c-driverdb(n) 4.99.30 manual "NaviServer Manual"

Name

c-driverdb - NaviServer Database Driver Development Guide

Table Of Contents

• Table Of Contents
• Description
• Introduction
• Internal vs. External Database Drivers
• The Internal Driver API
• The External Driver API
  • Functions to be Implemented
  • Utility Functions
  • Constants

Description

Introduction

A database driver is a module that interfaces between the NaviServer database-independent nsdb API and the API of a particular DBMS. A database driver provides implementations of a standard set of functions for doing such things as opening connections to the database, sending SQL statements, returning results to a standard form, and closing connections. NaviServer takes care of managing collections of open database connections called database pools.

This manual page is kept of as a reference. Part of this is outdated.

Database driver modules look much like ordinary NaviServer modules but are loaded differently. Instead of being listed with other modules in the ns/server/server-name/modules section of the configuration file, a database driver is listed in the ns/db/drivers section -- a database driver is not tied to a particular virtual server. The database driver initialization function must call Ns_DbRegisterDriver() with an array of pointers to functions. These functions are then later used by NaviServer to perform database operations. The virtual server initialization function is called each time nsdb is loaded into a virtual server. The server initialization function (Ns_dbms-nameServerInit) adds the Tcl command Ns_dbms-name to the server's Tcl interpreters. The Ns_dbms-name command can then be used to fetch driver-specific information about an active connection.

NOTE: The above naming scheme is simply based on convention. It is not enforced, and can be changed at the programmer's discretion.

Internal vs. External Database Drivers

Before developing a database driver, you need to decide whether the driver will be internal or external:

• Internal: An internal driver is tightly coupled with the NaviServer; the database client libraries are linked directly into the server.

• External: An external driver is loosely coupled; the nsext driver sends messages to an external database proxy daemon instead of calling the database client libraries directly. This database proxy daemon can be a local or remote process.

While an internal driver can be faster and can use less resources than an external driver, there are several reasons to develop an external driver:

• Many database client libraries are not thread-safe -- you'll need to be very careful about locking the libraries to use them in an internal driver, and this can unnecessarily slow down simultaneous queries.

• Many database client libraries make assumptions regarding per-process resources such as signals.

• An external driver can be used to access a database from a platform that may not be supported by the database client library.

The Internal Driver API

The set of function points that a database driver implements and what NaviServer expects is as follows:

1. Name

2. Database type

3. Server initialization

4. Open a database connection

5. Close a database connection

6. Get row from table

7. Flush

8. Cancel a database connection

9. Get information about a table

10. Get list of tables

11. Best row identifier (identifies each row uniquely)

12. Execute SQL query

13. Optional: Reset a database handle when it gets checked back into the pool.

The database driver's job is to make the appropriate DBMS-specific function calls that will implement these functions. The driver must provide an implementation of the function NsDbDriverInit(); This function is executed once, when the server starts up. The most important thing it does is call Ns_DbRegisterDriver() with an array of all the functions implemented by the driver. Here's an example from the PostgreSQL driver (nsdbpg, simplified):

 static Ns_DbProcPgProcs = {
    {DbFn_DbType,       (ns_funcptr_t)DbType},
    {DbFn_Name,         (ns_funcptr_t)DbType},
    {DbFn_OpenDb,       (ns_funcptr_t)OpenDb},
    {DbFn_CloseDb,      (ns_funcptr_t)CloseDb},
    {DbFn_BindRow,      (ns_funcptr_t)BindRow},
    {DbFn_Exec,         (ns_funcptr_t)Exec},
    {DbFn_GetRow,       (ns_funcptr_t)GetRow},
    {DbFn_GetRowCount,  (ns_funcptr_t)GetRowCount},
    {DbFn_Flush,        (ns_funcptr_t)Flush},
    {DbFn_Cancel,       (ns_funcptr_t)Flush},
    {DbFn_ResetHandle,  (ns_funcptr_t)ResetHandle},
    {DbFn_ServerInit,   (ns_funcptr_t)Ns_PgServerInit},
    {DbFn_End, NULL}
 };
 
 NS_EXPORT Ns_ReturnCode
 Ns_DbDriverInit(const char *driver, const char *configPath)
 {
    Ns_ReturnCode status;
    /*
     * Register the PostgreSQL driver functions with nsdb.
     * Nsdb will later call the Ns_PgServerInit() function
     * for each virtual server which utilizes nsdb.
     */
    status = Ns_DbRegisterDriver(driver, &procs[0]);
    if (status) != NS_OK) {
        Ns_Log(Error, "Ns_DbDriverInit(%s):  Could not register the %s driver.",
            driver, pgDbName);
        return NS_ERROR;
    }
    Ns_Log(Notice, "%s loaded.", pgDbName);
    return status;
 }

In more detail, here's what each of these functions needs to do:

1. const char *Ns_dbms-nameName(Ns_DbHandle *handle);

   This function returns the string which identifies the database driver.

2. const char *Ns_dbms-nameDbType(Ns_DbHandle *handle);

   This function returns the string which identifies the database type. Usually, it is the same as the name of the driver.

3. Ns_ReturnCode Ns_dbms-nameServerInit(const char *hServer, const char *hModule, const char *driver);

   Ns_dbms-nameServerInit calls another function named Ns_dbms_nameInterpInit which is responsible for adding the command Ns_dbms-name to a single Tcl interpreter. Ns_dbms-nameServerInit calls Ns_dbms-nameInterpInit for each interpreter in the virtual server that is being initialized.

4. Ns_ReturnCode Ns_dbms-nameOpenDb(Ns_DbHandle *dbh);

   This function takes a pointer (typically known as the "handle") to the Ns_DbHandle structure as an argument. The handle contains information such as the driver name, name of the datasource, username and password, name of the database pool and some other parameters. The structure is as follows:

    typedef struct Ns_DbHandle {
       const char *driver;
       const char *datasource;
       const char *user;
       const char *password;
       void       *connection;
       const char *poolname; /* poolname associated for this handle */
       bool        connected;
       bool        verbose; /* Was previously used for general verbosity, then unused, now verboseError */
       Ns_Set     *row;     /* used for row data during binds, getrows, etc.*/
                            /* used by internal Db API */
       char        cExceptionCode[6];
       Ns_DString  dsExceptionMsg;
       void       *context;
       void       *statement; /* used by ODBC driver statements */
       bool        fetchingRows;
    } Ns_DbHandle;
   

   The statement field is used as a pointer to your allocated statement handles (hstmt). It can be used to pass a DbHandle with a pointer to the statement handle to other driver functions to invoke cancels (e.g., SQLCancel()) and error reporting (e.g., SQLError()).

   This function takes the information contained in the handle and opens a connection to the database named in the handle along with the username and password. If a connection is successfully established, then OpenDb performs some database specific functions, and returns NS_OK. If it is unable to establish a connection to the database server, or if it is unable to perform the required database specific functions, it returns NS_ERROR. The status is logged by using the Ns_Log function.

   Note: For more information about database-specific functions, the documentation about its API should be consulted.

5. Ns_ReturnCode Ns_dbms-nameCloseDb(Ns_DbHandle *handle);

   This function takes a handle as an argument and terminates the connection to the database server. It also cleans up the handle parameters. It returns with either NS_OK or NS_ERROR (in case CloseDb fails to close the connection).

6. int Ns_dbms-nameGetRow(const Ns_DbHandle *handle, const Ns_Set *row);

   The results of a SELECT query are usually a number of rows. Ns_dbms-nameGetRow is responsible to obtain these rows from the database and return them to NaviServer for display/manipulation. This function typically does the following:

   checks if it is being called from within a fetch row loop checks if the end of data is reached, and if so, stops returns the next row in the result set

   You can return the values something like this:

   Ns_SetPutValue(row, (int) i, colval);

   This function should return either NS_ERROR or NS_OK.

7. Ns_ReturnCode Ns_dbms-nameFlush(Ns_DbHandle *handle);

   This function flushes any rows which are waiting to be retrieved after a SELECT is executed. The rows are irretrievably lost. The fields for columns, tuples, and current tuple in the connection structure are reset to 0. The result field is reset to NULL. This function should return either NS_ERROR or NS_OK.

8. Ns_ReturnCode Ns_dbms-nameCancel(Ns_DbHandle *handle);

   A call to this function results in cancelling that particular database connection.

9. int Ns_dbms-nameExec(Ns_DbHandle *handle, const char *sql);

   This function takes an SQL command and sends it to the DBMS. If the command is returns rows, the function should return NS_ROWS. If the command was DDL or DML, then the function should return NS_DML. And of course if there is an error executing the SQL, the function should return NS_ERROR. It is recommended that you define one Ns_dbms-nameExec procedure that handles both queries that return rows and those that do not. When Ns_DbExec is invoked in NaviServer, it calls whatever Ns_DbExec function is defined. When Ns_DbSelect is invoked, it tries Ns_DbExec first and then Ns_DbSelect.

10. Ns_ReturnCode Ns_dbms-nameResetHandle(Ns_DbHandle *handle);

    This function will be called with the database handle every time one is returned to the database pool. You can use this to normalize its state for the next use; for example always setting the handle to autocommit mode and aborting any uncommitted transactions.

The External Driver API

To build an external driver, you need to provide implementations for several functions, and then link your code with the provided 'nspdmain' program, found in the nspd.a library. The resulting "database proxy daemon" is contacted by the server's "external" driver (the client in the following descriptions) for all database activities. The 'nspdmain' program handles all communication with the server's 'external' driver, calling your routines database-specific functions. Note that there is a single database proxy daemon associated with each database connection, so all of the state for each connection is encapsulated here. Database connections are managed efficiently by the server's database pool mechanism.

The next section summarizes the External Driver Proxy Daemon functions. The reference section at the end of this appendix describes each function in detail and gives a pseudo-code implementation. Each implementation of a function may be a different for each dbms-specification. Consult your database client library documentation and the freely distributed NaviServer examples of the Sybase Proxy daemons for more information and dbms-specific examples.