[ Previous ] [ Contents ] [ Index ] [ Next ]

Example 7: postgres

The following example shows how to create a database driver. This `postgres' module is a database services driver which allows the AOLserver to use the Postgres95 database.

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);
    }
    

Top of Page

[ Previous ] [ Contents ] [ Index ] [ Next ]
Copyright © 1996 America Online, Inc.