postgresql_backend.c

gxsnmp SNMP MANAGER 的实现

/*
**  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 <[e-mail]>
*/
#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 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_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++)