mysql_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.
**
**  mysql_backend.c -- the MySQL database backend
*/
#include <glib.h>
#include "plugins.h"
#include <mysql.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 */

  MYSQL                   mysql;        /* A copy of the MySQL structure */
}
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 */

  MYSQL_RES		   * result;	  /* MYSQL result structure */
  unsigned int               num_fields;  /* Total number of fields in row */
  MYSQL_FIELD              * fields;      /* Array of field keys */
  MYSQL_ROW                  row;         /* Array of field data */
  unsigned long            * lengths;     /* Array of field lengths */

}
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 MySQL engine
*/

static struct _G_sql_backend backend =
{
  "MySQL",                      /* 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
**
**  MySQL has no initialization function, so just return TRUE.
**
*******************************************************************************/

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

/*******************************************************************************
**
**  sql_terminate ()  -- Perform database engine-specific termination
**
**  MySQL 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 MySQL database server
**
**  Create and initialize a G_sql_connection structure, then open a 
**  connection to a MYSQL server.
**
**  Returns:  address of G_connection structure if successful
**            NULL                              if unsuccessful
**
**  This really should use mysql_init() and mysql_real_connect(), but
**  these functions appear to be in a state of flux and are documented
**  incorrectly.  Maybe later ...
**
**  Until then, the "port" configuration parameter cannot be used.  
**
*******************************************************************************/

static G_sql_connection *
sql_connect (G_sql * db)
{
  G_sql_connection *dbc;

  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);
  if (!mysql_connect (&dbc->mysql, db->host, db->user, db->password))
    {
      notice_dlg ("MySQL backend: Unable to connect to the MySQL server on\n"
                  "%s port %d as %s.\nThe error reported was:\n%s\n", 
                  db->host, db->port, db->user, mysql_error (&dbc->mysql));
      d_print (DEBUG_DUMP, "Unable to connect to MySQL server on "
	       "%s port %d as %s\n", db->host, db->port, db->user);
      d_print (DEBUG_DUMP, "Error was:%s\n", mysql_error (&dbc->mysql));
      g_free (dbc);
      D_FUNC_END;
      return NULL;
    }

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

/*******************************************************************************
**
**  sql_disconnect ()  --  Close a connection to a MySQL 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;
  mysql_close (&dbc->mysql);
  g_free (dbc);
  D_FUNC_END;
  return TRUE;
}

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

static GList * 
sql_enum_dbs (G_sql_connection * dbc)
{
  GList * dblist;
  MYSQL_RES * result;
  MYSQL_ROW row;

  D_FUNC_START;
  result = mysql_list_dbs (&dbc->mysql, NULL);  
  if (NULL == result)
    {
      g_print ("MySQL Backend: mysql_list_dbs failed\n");
      g_print ("MySQL Backend: %s\n", mysql_error (&dbc->mysql));
      return NULL;
    }

  dblist = NULL;
  while ((row = mysql_fetch_row (result)))
    {
      gchar * name;
      
      name = g_strdup (row[0]);
      dblist = g_list_append (dblist, name);
    }

  mysql_free_result (result);
  D_FUNC_END;
  return dblist;
}

/*******************************************************************************
**
**  db_select ()  --  Select a database on an open MySQL server 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) 
{
  D_FUNC_START;
  d_print (DEBUG_DUMP, "selecting database %s\n", database);
  if (mysql_select_db (&dbc->mysql, database))
    {
      g_print ("MySQL backend: Unable to select '%s' database.\n", database);
      g_print ("MySQL backend: %s\n", mysql_error(&dbc->mysql));
      D_FUNC_END;
      return FALSE;
    }
  D_FUNC_END;
  return TRUE;
}

/*******************************************************************************
**
**  sql_query ()  --  Issue a database query to an open MySQL 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;
  D_FUNC_START;
  if (!(dbq = (G_sql_query *) g_new0 (G_sql_query, 1)))
    {
      g_warning ("MySQL backend: out of memory\n");
      return NULL;
    }

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

  if (mysql_query (&dbc->mysql, query))
    {
      g_print ("MySQL Backend: Query '%s' failed\n", query);
      g_print ("MySQL Backend: %s\n", mysql_error (&dbc->mysql));
      g_free (dbq);
      return NULL;
    }

  dbq->result = mysql_store_result (&dbq->connection->mysql);
  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);
  mysql_free_result (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 = mysql_fetch_row (dbq->result);
  if (!dbq->row)
    return FALSE;

  dbq->num_fields = mysql_num_fields    (dbq->result);
  dbq->fields     = mysql_fetch_fields  (dbq->result);
  dbq->lengths    = mysql_fetch_lengths (dbq->result);
  d_print (DEBUG_DUMP, "number of fields %d\n", dbq->num_fields);
  D_FUNC_END;
  return TRUE;
}

/*******************************************************************************
**
**  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;
  d_print (DEBUG_DUMP, "field %s\n", field);
  if (!*accel)
      for (i = 0; i < dbq->num_fields; i++)
        if (!g_strcasecmp (field, dbq->fields[i].name))
          *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 = dbq->lengths[i];
  D_FUNC_END;
  return 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 ("MySQL 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.", "MySQL backend");
  g_sql_register_backend (&backend);
  D_FUNC_START;
}

/* EOF */