![[ Previous ]](navbprev.gif)
![[ Contents ]](navbhome.gif)
![[ Index ]](navbhelp.gif)
![[ Next ]](navbnext.gif)
|
|
|
This example can be found in the examples/c/postgres directory.
#include "ns.h"
#include "nsdb.h"
#include "nstcl.h"
#include "libpq-fe.h"
#include <string.h>
#include <assert.h>
/*-
What is this?
------------
This module implements a simple AOLserver database services driver. A
database driver is a module which interfaces between the AOLserver
database-independent nsdb module and the API of a particular DBMS. A
database driver's job is to open connections, send SQL statements, and
translate the results into the form used by nsdb. In this case, the
driver is for the Postgres95 DBMS from UC Berekely. Postgres95 can be
downloaded and installed on most Unix systems. To use this driver, you
must have Postgres95 installed on your system. For more information on
Postgres95 or to download the code, open:
http://s2k-ftp.cs.berkeley.edu:8000/postgres95
How does it work?
----------------
Driver modules look much like ordinary AOLserver modules but are
loaded differently. Instead of being listed with other modules in the
[ns\server\<server-name>\modules] configuration section, a database
driver is listed in the [ns\module\nsdb\drivers] section and nsdb does
the loading. The database driver initialization function normally does
little more than call the nsdb Ns_DbRegisterDriver() function with an
array of pointers to functions. The functions are then later used by
nsdb to open database connections and send and process queries. This
architecture is much like ODBC on Windows. In addition to open,
select, and getrow functions, the driver also provides system catalog
functions and a function for initializing a virtual server. The
virtual server initialization function is called each time nsdb is
loaded into a virtual server. In this case, the server initialization
function, Ns_PgServerInit, adds the "ns_pg" Tcl command to the server's
Tcl interpreters which can be used to fetch information about an active
Postgres95 connection in a Tcl script.
*/
#define DRIVER_NAME "Postgres95"
static char *Ns_PgName(Ns_DbHandle *handle);
static int Ns_PgOpenDb(Ns_DbHandle *dbhandle);
static int Ns_PgCloseDb(Ns_DbHandle *dbhandle);
static int Ns_PgCmd(Ns_DbHandle *handle, char *sql);
static int Ns_PgReconnect(Ns_DbHandle *handle);
static Ns_Set *Ns_PgSelect(Ns_DbHandle *handle, char *sql);
static int Ns_PgGetRow(Ns_DbHandle *handle, Ns_Set *row);
static int Ns_PgFlush(Ns_DbHandle *handle);
static Ns_DbTableInfo *Ns_PgGetTableInfo(Ns_DbHandle *handle, char *table);
static char *Ns_PgTableList(Ns_DString *pds, Ns_DbHandle *handle,
int includesystem);
static char *Ns_PgBestRowId(Ns_DString *pds, Ns_DbHandle *handle, char *table);
static int Ns_PgServerInit(char *hServer, char *hModule, char *hDriver);
static char *pgName = DRIVER_NAME;
static unsigned int pgCNum = 0;
/*-
*
* The NULL-terminated PgProcs[] array of Ns_DbProc structures is the
* method by which the function pointers are passed to the nsdb module
* through the Ns_DbRegisterDriver() function. Each Ns_DbProc includes
* the function id (i.e., DbFn_OpenDb, DbFn_CloseDb, etc.) and the
* cooresponding driver function pointer (i.e., Ns_PgOpendb, Ns_PgCloseDb,
* etc.). See nsdb.h for a complete list of function ids.
*/
static Ns_DbProc PgProcs[] = {
{DbFn_Name, (void *) Ns_PgName},
{DbFn_OpenDb, (void *) Ns_PgOpenDb},
{DbFn_CloseDb, (void *) Ns_PgCloseDb},
{DbFn_DML, (void *) Ns_PgCmd},
{DbFn_Select, (void *) Ns_PgSelect},
{DbFn_GetRow, (void *) Ns_PgGetRow},
{DbFn_Flush, (void *) Ns_PgFlush},
{DbFn_Cancel, (void *) Ns_PgFlush},
{DbFn_GetTableInfo, (void *) Ns_PgGetTableInfo},
{DbFn_TableList, (void *) Ns_PgTableList},
{DbFn_BestRowId, (void *) Ns_PgBestRowId},
{DbFn_ServerInit, (void *) Ns_PgServerInit},
{0, NULL}
};
/*
* The NsPgConn structure is connection data specific
* to Postgres95.
*/
typedef struct NsPgConn {
PGconn *conn;
unsigned int cNum;
PGresult *res;
int nCols;
int nTuples;
int curTuple;
} NsPgConn;
DllExport int Ns_ModuleVersion = 1;
DllExport int
Ns_DbDriverInit(char *hDriver, char *configPath)
{
/*
* Register the Postgres95 driver functions with nsdb.
* Nsdb will later call the Ns_PgServerInit() function
* for each virtual server which utilizes nsdb.
*/
if (Ns_DbRegisterDriver(hDriver, &(PgProcs[0])) != NS_OK) {
Ns_Log(Error, "Ns_DbDriverInit(%s): Could not register the %s driver.",
hDriver, pgName);
return NS_ERROR;
}
Ns_Log(Notice, "%s loaded.", pgName);
return NS_OK;
}
/*
* Ns_PgName - Return the string name which identifies the Postgres95 driver.
*/
static char *
Ns_PgName(Ns_DbHandle *ignored)
{
return pgName;
}
/*
* Ns_PgOpenDb - Open an Postgres95 connection on an nsdb handle. The
* datasource for Postgres95 is in the form "host:port:database".
*/
static int
Ns_PgOpenDb(Ns_DbHandle *handle)
{
NsPgConn *nsConn;
PGconn *pgConn;
char *host;
char *port;
char *db;
int status;
assert(handle != NULL);
status = NS_ERROR;
host = handle->datasource;
port = strchr(handle->datasource, `:');
if (port == NULL || ((db = strchr(port + 1, `:')) == NULL)) {
Ns_Log(Error, "Ns_PgOpenDb(%s): Malformed datasource: %s",
handle->driver, handle->datasource);
} else {
*port++ = `\0';
*db++ = `\0';
Ns_Log(Notice, "Opening %s on %s, %s", db, host, port);
pgConn = PQsetdb(host, port, NULL, NULL, db);
*--db = `:';
*--port = `:';
if (PQstatus(pgConn) == CONNECTION_OK) {
Ns_Log(Notice, "Ns_PgOpenDb(%s): Openned connection to %s.",
handle->driver, handle->datasource);
nsConn = ns_malloc(sizeof(NsPgConn));
nsConn->cNum = pgCNum++;
nsConn->conn = pgConn;
nsConn->res = NULL;
nsConn->nCols = nsConn->nTuples = nsConn->curTuple = 0;
handle->connection = nsConn;
status = NS_OK;
} else {
Ns_Log(Error, "Ns_PgOpenDb(%s): Could not connect to %s: %s",
handle->driver, handle->datasource, PQerrorMessage(pgConn));
PQfinish(pgConn);
return NS_ERROR;
}
}
return NS_OK;
}
/*
* Ns_PgCloseDb - Close an Postgres95 connection on an nsdb handle.
*/
static int
Ns_PgCloseDb(Ns_DbHandle *handle)
{
NsPgConn *nsConn;
assert(handle != NULL);
nsConn = handle->connection;
if (handle->verbose) {
Ns_Log(Notice, "Ns_PgCloseDb(%d): Closing connection: %s",
nsConn->cNum, handle->datasource);
}
PQfinish(nsConn->conn);
nsConn->conn = NULL;
nsConn->nCols = nsConn->nTuples = nsConn->curTuple = 0;
ns_free(nsConn);
handle->connection = NULL;
return NS_OK;
}
/*
* Ns_PgExec - Send a Postgres95 query. This function does not
* implement an nsdb function but is used internally by the Postgres95
* driver.
*/
static int
Ns_PgExec(Ns_DbHandle *handle, char *sql)
{
NsPgConn *nsConn;
Ns_DString dsSql;
int status;
assert(handle != NULL);
assert(sql != NULL);
status = NS_ERROR;
nsConn = handle->connection;
Ns_DStringInit(&dsSql);
Ns_DStringAppend(&dsSql, sql);
while (dsSql.length > 0 && isspace(dsSql.string[dsSql.length - 1])) {
dsSql.string[--dsSql.length] = `\0';
}
if (dsSql.length > 0 && dsSql.string[dsSql.length - 1] != `;') {
Ns_DStringNAppend(&dsSql, ";", 1);
}
nsConn->res = PQexec(nsConn->conn, dsSql.string);
Ns_DStringFree(&dsSql);
if (nsConn->res == NULL) {
Ns_Log(Error, "Ns_PgExec(%s): Could not send query `%s': %s",
handle->datasource, sql, PQerrorMessage(nsConn->conn));
return NS_ERROR;
}
return NS_OK;
}
/*
* Ns_PgCmd - Send a query which should return a command result.
*/
static int
Ns_PgCmd(Ns_DbHandle *handle, char *sql)
{
int status;
NsPgConn *nsConn;
assert(handle != NULL);
assert(sql != NULL);
nsConn = handle->connection;
status = Ns_PgExec(handle, sql);
if (status == NS_OK) {
if (PQresultStatus(nsConn->res) != PGRES_COMMAND_OK) {
Ns_Log(Error,
"Ns_PgCmd(%s): Query `%s' did not return PGRES_COMMAND_OK status.",
handle->datasource, sql);
status = NS_ERROR;
}
nsConn->nCols = 0;
PQclear(nsConn->res);
nsConn->res = NULL;
}
return status;
}
/*
* Ns_PgSelect - Send a query which should return rows.
*/
static Ns_Set *
Ns_PgSelect(Ns_DbHandle *handle, char *sql)
{
Ns_Set *row;
NsPgConn *nsConn;
int i;
assert(handle != NULL);
assert(sql != NULL);
row = NULL;
nsConn = handle->connection;
if (Ns_PgExec(handle, sql) == NS_OK) {
if (PQresultStatus(nsConn->res) == PGRES_TUPLES_OK) {
nsConn->curTuple = 0;
nsConn->nCols = PQnfields(nsConn->res);
nsConn->nTuples = PQntuples(nsConn->res);
row = handle->row;
for (i = 0; i < nsConn->nCols; ++i) {
Ns_SetPut(row, PQfname(nsConn->res, i), NULL);
}
} else {
Ns_Log(Error, "Ns_PgSelect(%s): Query did not return rows: %s",
handle->datasource, sql);
}
}
return row;
}
/*
* Ns_PgGetRow - Fetch rows after an Ns_PgSelect.
*/
static int
Ns_PgGetRow(Ns_DbHandle *handle, Ns_Set *row)
{
NsPgConn *nsConn;
int status;
int i;
assert(handle != NULL);
assert(row != NULL);
assert(handle->connection != NULL);
nsConn = handle->connection;
if (nsConn->nCols == 0) {
Ns_Log(Error, "Ns_PgGetRow(%s): Get row called outside a fetch row loop.",
handle->datasource);
status = NS_ERROR;
} else if (nsConn->curTuple == nsConn->nTuples) {
PQclear(nsConn->res);
nsConn->res = NULL;
nsConn->nCols = nsConn->nTuples = nsConn->curTuple = 0;
status = NS_END_DATA;
} else {
for (i = 0; i < nsConn->nCols; i++) {
Ns_SetPutValue(row, (int) i, PQgetvalue(nsConn->res,
nsConn->curTuple, i));
}
++nsConn->curTuple;
return NS_OK;
}
return status;
}
/*
* Ns_PgFlush - Flush any waiting rows not needed after an Ns_DbSelect().
*/
static int
Ns_PgFlush(Ns_DbHandle *handle)
{
NsPgConn *nsConn;
assert(handle != NULL);
assert(handle->connection != NULL);
nsConn = handle->connection;
if (nsConn->nCols > 0) {
PQclear(nsConn->res);
nsConn->res = NULL;
nsConn->nCols = nsConn->nTuples = nsConn->curTuple = 0;
}
return NS_OK;
}
/*
* Ns_DbTableInfo - Return system catalog information (columns, types, etc.)
* about a table.
*/
static Ns_DbTableInfo *
Ns_PgGetTableInfo(Ns_DbHandle *handle, char *table)
{
Ns_Set *row;
Ns_Set *col;
Ns_DString ds;
Ns_DbTableInfo *tinfo;
int status;
char *name;
char *type;
Ns_DStringInit(&ds);
Ns_DStringVarAppend(&ds, "SELECT a.attname, t.typname "
"FROM pg_class c, pg_attribute a, pg_type t "
"WHERE c.relname = `", table, "` "
"and a.attnum > 0 and a.attrelid = c.oid "
"and a.atttypid = t.oid ORDER BY attname", NULL);
row = Ns_DbSelect(handle, ds.string);
Ns_DStringFree(&ds);
tinfo = NULL;
if (row != NULL) {
while ((status = Ns_PgGetRow(handle, row)) == NS_OK) {
name = row->fields[0].value;
type = row->fields[1].value;
if (name == NULL || type == NULL) {
Ns_Log(Error,
"Ns_PgGetTableInfo(%s): Invalid `pg_attribute' entry for table: %s",
handle->datasource, table);
break;
}
/*
* NB: Move the fields directly from the row
* Ns_Set to the col Ns_Set to avoid a data copy.
*/
col = Ns_SetCreate(NULL);
col->name = name;
Ns_SetPut(col, "type", NULL);
col->fields[0].value = type;
row->fields[0].value = NULL;
row->fields[1].value = NULL;
if (tinfo == NULL) {
tinfo = Ns_DbNewTableInfo(table);
}
Ns_DbAddColumnInfo(tinfo, col);
}
if (status != NS_END_DATA && tinfo != NULL) {
Ns_DbFreeTableInfo(tinfo);
tinfo = NULL;
}
}
return tinfo;
}
/*
* Ns_PgTableList - Return a list of tables in the database.
*/
static char *
Ns_PgTableList(Ns_DString *pds, Ns_DbHandle *handle, int fSystemTables)
{
Ns_Set *row;
Ns_DString ds;
char *table;
int status;
Ns_DStringInit(&ds);
Ns_DStringAppend(&ds, "SELECT relname FROM pg_class "
"WHERE relkind = `r' and relname !~ `^Inv' ");
if (!fSystemTables) {
Ns_DStringAppend(&ds, "and relname !~ `^pg_' ");
}
Ns_DStringAppend(&ds, "ORDER BY relname");
row = Ns_DbSelect(handle, ds.string);
Ns_DStringFree(&ds);
status = NS_ERROR;
if (row != NULL) {
while ((status = Ns_DbGetRow(handle, row)) == NS_OK) {
table = row->fields[0].value;
if (table == NULL) {
Ns_Log(Warning,
"Ns_PgTableList(%s): NULL relname in `pg_class' table.",
handle->datasource);
} else {
Ns_DStringNAppend(pds, table, strlen(table) + 1);
}
}
}
if (status == NS_END_DATA) {
return pds->string;
}
return NULL;
}
/*
* Ns_PgBestRowId - Return the primary key of a table. If a table
* has a primary key, the AOLserver can perform row updates and
* deletes. In the case of Postgres95, the "oid" system column is alwasy
* unique so we just return it instead of looking for an actual
* primary key.
*/
static char *
Ns_PgBestRowId(Ns_DString *pds, Ns_DbHandle *handle, char *table)
{
Ns_DStringNAppend(pds, "oid", 4);
return pds->string;
}
/*
* PgCmd - This function implements the "ns_pg" Tcl command installed into
* each interpreter of each virtual server. It provides access to features
* specific to the Postgres95 driver.
*/
static int
PgCmd(ClientData dummy, Tcl_Interp *interp, int argc, char **argv)
{
Ns_DbHandle *handle;
NsPgConn *pgconn;
if (argc != 3) {
Tcl_AppendResult(interp, "wrong # args: should be \"",
argv[0], " command dbId\"", NULL);
return TCL_ERROR;
}
if (Ns_TclDbGetHandle(interp, argv[2], &handle) != TCL_OK) {
return TCL_ERROR;
}
/*
* Make sure this is a Postgres95 handle before accessing
* handle->connection as an NsPgConn.
*/
if (Ns_DbDriverName(handle) != pgName) {
Tcl_AppendResult(interp, "handle \"", argv[1], "\" is not of type \"",
pgName, "\"", NULL);
return TCL_ERROR;
}
pgconn = (NsPgConn *) handle->connection;
if (!strcmp(argv[1], "db")) {
Tcl_SetResult(interp, PQdb(pgconn->conn), TCL_STATIC);
} else if (!strcmp(argv[1], "host")) {
Tcl_SetResult(interp, PQhost(pgconn->conn), TCL_STATIC);
} else if (!strcmp(argv[1], "options")) {
Tcl_SetResult(interp, PQoptions(pgconn->conn), TCL_STATIC);
} else if (!strcmp(argv[1], "port")) {
Tcl_SetResult(interp, PQport(pgconn->conn), TCL_STATIC);
} else if (!strcmp(argv[1], "number")) {
sprintf(interp->result, "%u", pgconn->cNum);
} else if (!strcmp(argv[1], "error")) {
Tcl_SetResult(interp, PQerrorMessage(pgconn->conn), TCL_STATIC);
} else if (!strcmp(argv[1], "status")) {
if (PQstatus(pgconn->conn) == CONNECTION_OK) {
interp->result = "ok";
} else {
interp->result = "bad";
}
} else {
Tcl_AppendResult(interp, "unknown command \"", argv[2],
"\": should be db, host, options, port, error or status.", NULL);
return TCL_ERROR;
}
return TCL_OK;
}
/*
* Ns_PgInterpInit - Add the "ns_pg" command to a single Tcl interpreter.
*/
static int
Ns_PgInterpInit(Tcl_Interp *interp, void *ignored)
{
Tcl_CreateCommand(interp, "ns_pg", PgCmd, NULL, NULL);
return NS_OK;
}
/*
* Ns_PgServerInit - Have Ns_PgInterpInit called for each interpreter in
* the virtual server which is being intialized.
*/
static int
Ns_PgServerInit(char *hServer, char *hModule, char *hDriver)
{
return Ns_TclInitInterps(hServer, Ns_PgInterpInit, NULL);
}
|
