/* pgsql2latex.c

   Fanel Donea, fdonea@yahoo.com, 2002

   Prints the result of an SQL query in LaTeX format.
   Meant to be used from inside shell scripts.
   Passes the query directly to the SQL server, no checking is done on the query string.

   Usage:

   pgsql_to_latex database4 user44 "password" "SELECT * FROM ....;"

   Output goes to stdout.

   No memory allocation for input strings for now,
   so keep the query string below 1000 chars.
   SQL comments will confuse the converter.

   Compile like this (valid for the default tar.gz installation
   of postgresql):

   gcc -I/usr/local/pgsql/include -L/usr/local/pgsql/lib -lpq  pgsql2latex.c -o ~/bin/pgsql2latex



   TO DO:
      - deal with column names containing uppercase chars ---> keep the name as it is???
      - optional alignment parameters, like "cll", "crrrrll"
      - optional table width or column widths
      - align floats correctly at the decimal point
      - optional table title
      - optional field descriptions, instead of cryptical field names
      - the data values themselves might contain underscores
      - optional header colour
      - multipage tables ...
      - font size..automatic for large tables ?...
      - separate data from layout?? a function taking 'result' as argument??
        (use for HTML too..?)
*/

#include <stdlib.h>
#include <libpq-fe.h>

#include <string.h>

/* takes a string and puts a backslash in front of the underscores, if any */
/* needed because the underscore is a special character in LaTeX */
void escape_the_underscore (char input_string[], char output_string[])
{
   int i;  /* counts chars of input_string */
   int j;  /* counts chars of output_string */

   j=0;
   for(i=0; input_string[i] != '\0'; i++)
   {
      if (input_string[i] != '_')
      {
         output_string[j]= input_string[i];
         j=j+1;
      }
      else
      {
         output_string[j] = '\\';
         output_string[j+1] = '_';
         j=j+2;
      }
   }
   output_string[j]='\0';
}





int main(int argc, char **argv)
{
   char dbname[100];
   char user[100];
   char password[100];
   char sql_query[1000];

   char connection_string[100];    /* to be passed to PQconnectdb() */
   PGconn *myconn;
   PGresult *result;

   int r, nrows;       /* r=0,1,...,nrows-1   */
   int f, nfields;     /* f=0,1,...,nfields-1 */

   char clean_field_name[100];

   /* test and print the arguments */
   if (argc-1 != 4)
   {
      printf("Number of arguments supplied = %d\n", argc-1);
      printf("Usage:\n");
      printf("pgsql_to_latex database4 user44 \"password\" \"SELECT * FROM ....;\"\n");
      exit(1);
   }
   strcpy(dbname, argv[1]);
   strcpy(user, argv[2]);
   strcpy(password, argv[3]);
   strcpy(sql_query, argv[4]);

   /*   
   printf("dbname = %s\n", dbname);
   printf("user = %s\n", user);
   printf("password = %s\n", password);

   printf("SQL Query = %s\n", sql_query);
   */

   /* build the connection string */
   sprintf(connection_string,"dbname=%s user=%s password=\"%s\"", dbname,user,password);
   /*
   printf("connection_string=%s\n", connection_string);
   */

   myconn = PQconnectdb(connection_string);
   if( PQstatus(myconn) != CONNECTION_OK )
   {
      printf("connection failed\n");
      exit(1);
   }

   /* query the SQL server */
   result=PQexec(myconn, sql_query);

   nrows=PQntuples(result);
   nfields=PQnfields(result);
   /*
   printf("number of rows returned = %d\n", nrows);
   printf("number of fields returned = %d\n", nfields);
   */

   /* move all this to print_latex_table.c or maybe print_latex_table.cpp */
   printf("\\begin{tabular}{|");
   for(f=0;f<=nfields-1;f++)
      printf("l");
   printf("|} \\hline\n\\rowcolor[gray]{0.9}\n");

   for(f=0;f<=nfields-2;f++)
   {
      escape_the_underscore(PQfname(result,f), clean_field_name);
      printf("{\\bf %s} & ", clean_field_name);
   }
   escape_the_underscore(PQfname(result, nfields-1), clean_field_name);
   printf("{\\bf %s} \\\\ \\hline\n", clean_field_name);


   for(r=0;r<=nrows-1;r++)
   {
      for(f=0;f<=nfields-2;f++)
      {
         printf("%s & ", PQgetvalue(result, r, f));
      }
      printf("%s \\\\ \\hline\n", PQgetvalue(result, r, nfields-1));
   }
   printf("\\end{tabular}\n");


   PQclear(result);
   PQfinish(myconn);
   return 0;

}