/*
**  gsql -- A simplified, unified interface to various SQL packages.
**  Copyright (C) 1999 John Schulien
**
**  This program is free software; you can redistribute it and/or modify
**  it under the terms of the GNU General Public License as published by
**  the Free Software Foundation; either version 2 of the License, or
**  (at your option) any later version.
**
**  This program is distributed in the hope that it will be useful,
**  but WITHOUT ANY WARRANTY; without even the implied warranty of
**  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
**  GNU General Public License for more details.
**
**  You should have received a copy of the GNU General Public License
**  along with this program; if not, write to the Free Software
**  Foundation, Inc.,  59 Temple Place - Suite 330, Cambridge, MA 02139, USA.
**
**  postgresql_backend.c -- the PostgreSQL database backend
**  Written by Kai Nacke <kai@redstar.de>
*/
#include <config.h>
#include <glib.h>
#include "plugins.h"
#include <libpq-fe.h>

#include "debug.h"

/*******************************************************************************
**
**  Private structure to represent an open database
**
**  The first two elements of this structure are mandatory.
**
*******************************************************************************/

typedef struct _G_sql_connection
{
  struct _G_sql_backend * backend;	/* Pointer to the backend block */
  gint			  errno;	/* Last error reported on connection */

  PGconn                * pgconn;       /* A pointer to the PGconn object */
}
G_sql_connection;

/*******************************************************************************
**
**  Private structure to represent an open query
**
**  The first two elements of this structure are mandatory
**
*******************************************************************************/

typedef struct _G_sql_query
{
  struct _G_sql_connection * connection;  /* Pointer to the connection block */
  gint			     errno;	  /* Last error reported on query */

  PGresult                 * result;      /* PostgreSQL result structure */
  gint                       tuples;      /* Number of tuples in result */
  gint                       fields;      /* Number of fields of a tuple */
  gint                       row;         /* Current row */
}
G_sql_query;

/*
**  Next comes the public portion of the database interface definition.
**  G_sql_backend and G_sql_query must be defined before including g_sql.h.
**  with G_SQL_BACKEND defined.
*/

#define G_SQL_BACKEND
#include "g_sql.h"

/*
**  Forward references
*/

static gboolean           sql_initialize  (G_sql_backend    * dbb);
static gboolean           sql_terminate   (G_sql_backend    * dbb);
static G_sql_connection * sql_connect     (G_sql            * db);
static gboolean           sql_disconnect  (G_sql_connection * dbc);
static GList            * sql_enum_dbs    (G_sql_connection * dbc);
static gboolean	          sql_select      (G_sql_connection * dbc,
				           gchar 	    * database);
static G_sql_query      * sql_query       (G_sql_connection * dbc,
				      	   gchar            * query,
					   gint		      querylen);
static gboolean           sql_free_query  (G_sql_query      * dbq);
static gboolean           sql_next_row    (G_sql_query      * dbq);
static guint              sql_num_rows    (G_sql_query      * dbq);
static gchar            * sql_field_pos   (G_sql_query      *, gint);
static gchar            * sql_field       (G_sql_query      * dbq, 
					   void		   ** accel,
					   gchar            * field, 
				           gint             * length);

/*
**  The database backend control block for the PostgreSQL engine
*/

static struct _G_sql_backend backend =
{
  "PostgreSQL",                 /* Name of the database engine */
  NULL,				/* Private data pointer */
  sql_initialize,		/* "Initialize" handler */
  sql_terminate,		/* "Terminate" handler */
  sql_connect,                  /* "Connect" handler */
  sql_disconnect,		/* "Disconnect" handler */
  sql_enum_dbs,      		/* "Enumerate databases" handler */
  sql_select,			/* "Select" handler */
  sql_query,			/* "Query" handler */
  sql_free_query,		/* "Free-Query" handler */ 
  sql_next_row,			/* "Next Row" handler */
  sql_num_rows,                 /* "Num Rows" handler */
  sql_field_pos,                /* "Field pos" handler */
  sql_field			/* "Get Field" handler */
};

/*******************************************************************************
**
**  sql_initialize ()  -- Perform database engine-specific initialization
**
**  PostgreSQL has no initialization function, so just return TRUE.
**
*******************************************************************************/

gboolean
sql_initialize (G_sql_backend * dbb)
{
  D_FUNC_START;
  app_update_init_status ("Starting plugins.", 
			  "PostgreSQL backend initilized.");
  D_FUNC_END;
  return TRUE;
}

/*******************************************************************************
**
**  sql_terminate ()  -- Perform database engine-specific termination
**
**  PostgreSQL has no termination function, so just return TRUE.
**
*******************************************************************************/

gboolean
sql_terminate (G_sql_backend * dbb)
{
  d_print (DEBUG_TRACE, "\n");
  return TRUE;
}

/*******************************************************************************
**
**  sql_connect ()  --  Connect to a PostgreSQL database server
**
**  Create and initialize a G_sql_connection structure, then open a 
**  connection to a PostgreSQL server.
**  This is a little bit tricky, since we have no database name. 
**  Therefore the template database 'template1' is choosen.
**
**  Returns:  address of G_connection structure if successful
**            NULL                              if unsuccessful
**
*******************************************************************************/

static G_sql_connection *
sql_connect (G_sql * db)
{
  G_sql_connection * dbc;
  gchar port[32];

  D_FUNC_START;
  dbc = (G_sql_connection *) g_new0 (G_sql_connection, 1);
  if (!dbc)
    return NULL;

  dbc->backend = &backend;
  d_print (DEBUG_DUMP, "db->host %s, db->user %s\n", db->host, db->user);

  snprintf (port, sizeof (port), "%d", db->port);
  dbc->pgconn = PQsetdbLogin (db->host, port, NULL, NULL,
                              "template1", db->user, db->password);
  if (NULL == dbc->pgconn ||
      CONNECTION_BAD == PQstatus (dbc->pgconn))
    {
      notice_dlg ("PostgreSQL backend: Unable to connect to the PostgreSQL\n"
                  "server on %s port %d as %s.\n"
		  "The error reported was:\n%s\n", 
                  db->host, db->port, db->user, PQerrorMessage (dbc->pgconn));
      d_print (DEBUG_DUMP, "Unable to connect to PostgreSQL server on "
	       "%s port %d as %s\n", db->host, db->port, db->user);
      d_print (DEBUG_DUMP, "Error was:%s\n", PQerrorMessage (dbc->pgconn));
      g_free (dbc);
      D_FUNC_END;
      return NULL;
    }

  dbc->errno = 0;
  D_FUNC_END;
  return dbc;
}

/*******************************************************************************
**
**  sql_disconnect ()  --  Close a connection to a PostgreSQL database server
**
**  Close the open connection, then free the control block.
**
**  Returns:  TRUE  -- if the operation was successful
**            FALSE -- if the operation failed
**
*******************************************************************************/

static gboolean
sql_disconnect (G_sql_connection * dbc)
{
  D_FUNC_START;
  g_return_val_if_fail (NULL != dbc, FALSE);
  PQfinish (dbc->pgconn);
  g_free (dbc);
  D_FUNC_END;
  return TRUE;
}

/*******************************************************************************
**
**  sql_enum_dbs ()  --  Enumerate the available databases
**
**  Use the system tables to list the available databases on the
**  connection.
**
**  Returns:  GList of databases if the operation succeeded
**            NULL if the operation failed.       
**
*******************************************************************************/

static gchar * show_databases = "select datname from pg_database";

static GList * 
sql_enum_dbs (G_sql_connection * dbc)
{
  PGresult * result;
  ExecStatusType status;
  GList * dblist;
  int i;

  D_FUNC_START;
  result = PQexec (dbc->pgconn, show_databases);
  if (NULL == result ||
      PGRES_BAD_RESPONSE == (status = PQresultStatus (result)) ||
      PGRES_NONFATAL_ERROR == status ||
      PGRES_FATAL_ERROR == status)
    {
      g_print ("PostgreSQL Backend: Query '%s' failed\n", show_databases);
      g_print ("PostgreSQL Backend: %s\n", PQresultErrorMessage (result));
      PQclear (result);
      return NULL;
    }

  dblist = NULL;
  for (i = 0; i < PQntuples (result); i++)
    {
      gchar * name;

      name = g_strdup (PQgetvalue (result, i, 0));
      dblist = g_list_append (dblist, name);
    }
  PQclear (result);
  D_FUNC_END;
  return dblist;
}

/*******************************************************************************
**
**  db_select ()  --  Select a database on an open PostgreSQL server connection
**
**  For PostgreSQL, we have to close the current connection and open a new
**  connection.
**
**  Returns:  TRUE  -- if the select operation was successful
**            FALSE -- if the select operation failed
**
*******************************************************************************/

static gboolean
sql_select (G_sql_connection * dbc, gchar * database) 
{
  PGconn * oldpgconn;

  D_FUNC_START;
  g_return_val_if_fail (NULL != dbc, FALSE);
  g_return_val_if_fail (NULL != database, FALSE);
  d_print (DEBUG_DUMP, "selecting database %s\n", database);

  /* Check if the same database is selected */
  if (0 == strcmp (database, PQdb (dbc->pgconn)))
    {
      D_FUNC_END;
      return TRUE;
    }

  oldpgconn = dbc->pgconn;
  dbc->pgconn = PQsetdbLogin (PQhost (oldpgconn), PQport (oldpgconn),
                              NULL, NULL, database, PQuser (oldpgconn), 
			      PQpass (oldpgconn));
  if (NULL == dbc->pgconn ||
      CONNECTION_BAD == PQstatus (dbc->pgconn))
    {
      g_print ("PostgreSQL backend: Unable to select '%s' database.\n",
	       database);
      g_print ("PostgreSQL backend: %s\n", PQerrorMessage (dbc->pgconn));
      PQfinish (dbc->pgconn);
      dbc->pgconn = oldpgconn;
      D_FUNC_END;
      return FALSE;
    }
  else
    PQfinish (oldpgconn);

  D_FUNC_END;
  return TRUE;
}

/*******************************************************************************
**
**  sql_query ()  --  Issue a database query to an open PostgreSQL server 
**                    connection
**
**  Returns Values:
**
**  The return value is an open database query structure, or NULL if the
**  operation failed.
**
*******************************************************************************/

static G_sql_query  * 
sql_query (G_sql_connection * dbc, gchar * query, gint querylen)
{
  G_sql_query *dbq;
  ExecStatusType status;

  D_FUNC_START;
  if (!(dbq = (G_sql_query *) g_new0 (G_sql_query, 1)))
    {
      g_warning ("PostgreSQL backend: out of memory\n");
      return NULL;
    }

  dbq->connection = dbc;
  d_print (DEBUG_QUERIES, "query: %s\n", query);

  dbq->result = PQexec (dbc->pgconn, query);
  if (NULL == dbq->result ||
      PGRES_BAD_RESPONSE == (status = PQresultStatus (dbq->result)) ||
      PGRES_NONFATAL_ERROR == status ||
      PGRES_FATAL_ERROR == status)
    {
      g_print ("PostgreSQL Backend: Query '%s' failed\n", query);
      g_print ("PostgreSQL Backend: %s\n", PQresultErrorMessage (dbq->result));
      PQclear (dbq->result);
      g_free (dbq);
      return NULL;
    }

  dbq->tuples = PQntuples (dbq->result);
  dbq->fields = PQnfields (dbq->result);
  dbq->row = -1;

  D_FUNC_END;
  return dbq;
}


/*******************************************************************************
**
**  sql_free_query ()  --  Free up a query structure
**
*******************************************************************************/

static gboolean
sql_free_query (G_sql_query * dbq)
{
  D_FUNC_START;
  g_return_val_if_fail (dbq != NULL, FALSE);
  PQclear (dbq->result);
  g_free (dbq);
  D_FUNC_END;
  return TRUE;
}

/*******************************************************************************
**
**  sql_next_row ()  --  Select the next row in a query result
**
**  This subroutine returns the private handle of the next available row
**  in a query result.
**
**  Return values:
**
**  TRUE  --  If the operation succeeded
**  FALSE --  If the operation failed
**
*******************************************************************************/

static gboolean 
sql_next_row (G_sql_query * dbq)
{
  D_FUNC_START;
  g_return_val_if_fail (dbq != NULL, FALSE);
  ++dbq->row;
  if (dbq->row >= dbq->tuples)
    return FALSE;
  D_FUNC_END;
  return TRUE;
}

/*******************************************************************************
**
**  sql_num_rows ()  --  get the number of rows
**
**  This subroutine returns the private handle of the next available row
**  in a query result.
**
**  Return values:
**
**  TRUE  --  If the operation succeeded
**  FALSE --  If the operation failed
**
*******************************************************************************/

static guint
sql_num_rows (G_sql_query * dbq)
{
  guint num;
  g_return_val_if_fail (dbq != NULL, FALSE);
  num = PQnfields(dbq->result);
  d_print (DEBUG_DUMP, "number of rows %u\n", num);
  return num;
}

/*******************************************************************************
**
**  sql_field_pos ()  --  Select a specified row
**
**  Return values:
**
**  Pointer to a ASCIIZ representing the data found in row[fieldpos];
**  NULL --  If the operation failed ( NOT IMPLEMENTED )
**
*******************************************************************************/
  
static gchar *sql_field_pos(G_sql_query *dbq, gint fieldpos)
{ 
  return dbq->row[fieldpos];
} 


/*******************************************************************************
**
**  sql_field ()  --  Read a named field from a database row,
**                          and return a pointer to the data, and the length
**			    of the returned data.
**
**  This subroutine returns the named field from a selected database row.
**  This subroutine may read binary data that might contain zeroes.   
**
**  Return values:
**
**  The return value is a pointer to the raw database field data, or NULL if
**  the operation failed.  The length of the result is stored in the "length"
**  parameter.
**
**  The caller is responsible for g_free()'ing the allocated storage.
**
**  Since searching through the field table each time we are called is 
**  expensive, accelerators are used to speed up this process for subsequent
**  row lookups on the same query.  The accelerator field, provided by the
**  caller, will be unique for each field name.  We simply store the found
**  row index plus one in the caller's accelerator field.  Then, on
**  subsequent calls, we can skip the search by using the accelerator
**  value minus one for the field index.
**
*******************************************************************************/

static gchar * 
sql_field (G_sql_query * dbq, G_sql_accelerator * accel,
	   gchar * field, gint * length)
{
  int i; 
  
  D_FUNC_START;
  g_return_val_if_fail (dbq != NULL, FALSE);
  g_return_val_if_fail (dbq->row >= 0 && dbq->row < dbq->tuples, FALSE);
  d_print (DEBUG_DUMP, "field %s\n", field);
  if (!*accel)
      for (i = 0; i < dbq->fields; i++)
        if (!g_strcasecmp (field, PQfname (dbq->result, i)))
          *accel = (G_sql_accelerator) (i + 1);

  if (!*accel)
    {
      d_print (DEBUG_DUMP, "Field %s not found in selected row\n", field);
      D_FUNC_END;
      return NULL;
    }

  i = (int)(*accel) - 1;
  *length = (gint) PQgetlength (dbq->result, dbq->row, i);
  D_FUNC_END;
  return PQgetvalue(dbq->result, dbq->row, i);
}

/******************************************************************************
**
**  Subroutine to load the plugin.  Set the plugin type to PLUGIN_DATABASE.
**
******************************************************************************/

int 
load_plugin (PluginData * pd)
{
  D_FUNC_START;
  pd->type = PLUGIN_DATABASE;
  pd->name = g_strdup ("PostgreSQL DB backend");
  D_FUNC_END;
  return 0;
}

/******************************************************************************
**
** Subroutine to unload the plugin
**
******************************************************************************/

void
unload_plugin (PluginData * pd)
{
  D_FUNC_START;
  g_sql_unregister_backend (&backend);
  D_FUNC_END;
}

/******************************************************************************
**
**  Subroutine to start the plugin
**
******************************************************************************/

void
start_plugin (PluginData * pd)
{
  D_FUNC_START;
  app_update_init_status ("Starting plugins.", "PostgreSQL backend");
  g_sql_register_backend (&backend);
  D_FUNC_START;
}

/* EOF */