NaviServer Database Driver Development Guide(n) 4.99.2 ""

NAME

NaviServer Database Driver Development Guide - NaviServer Database Driver Development Guide

    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

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.

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:

Name
Database type
Server initialization
Open a database connection
Close a database connection
Get row from table
Flush
Cancel a database connection
Get information about a table
Get list of tables
Best row identifier (identifies each row uniquely)
Execute SQL query
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 ns_DbDriverInit; 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 Postgres95 db driver:

static ns_DbProc PgProcs = { {DbFn_Name, (void *) ns_PgName}, {DbFn_DbType, (void *) ns_PgDbType}, {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}, {DbFn_ResetHandle, (void *) NULL}, {0, NULL} }; 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; }

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

char *ns_dbms-nameName(ns_DbHandle *handle);

This function returns the string which identifies the database driver.
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.
int ns_dbms-nameServerInit(char *hServer, char *hModule, char *hDriver);

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.

int 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, user name and password, name of the database pool and some other parameters. The structure is as follows:

typedef struct ns_DbHandle { char *driver; char *datasource; char *user; char *password; void *connection; char *poolname; /* poolname associated for this handle */ /* used by internal DbAPI */ int connected; int verbose; /* used for verbose error messages */ /* equivalent to [ns/db/pool/poolname] Verbose=On */ /* used by internal Db API */ 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 */ int fetchingRows; } ns_DbHandle;

The statement field is used as a pointer to your allocated statement handles (hstmt). This allows you 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.

int 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).
int ns_dbms-nameGetRow(ns_DbHandle *handle, 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.
int 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.
int ns_dbms-nameCancel(ns_DbHandle *handle);

A call to this function results in cancelling that particular database connection.
ns_DbTableInfo *ns_dbms-nameGetTableInfo(ns_DbHandle *handle, char *table, int fExtended);

This function returns table-specific information such as number of attributes in the table, their types etc. The third parameter is for internal use and can be ignored. ns_DbTableInfo is a array of ns_Set * and the sets contain something resembling the following:
set->name = "column_name"; set->field[0].name = "column_type"; set->field[0].value = "text"; set->field[1].name = "column_notnull"; set->field[1].value = "t";
A "t" or an "f" would represent whether the column is nullable or not.
char *ns_dbms-nameTableList(ns_DString *pds, ns_DbHandle *handle, int includesystem);

This function builds a database-specific SELECT statement which returns a list of all tables in the database. The function returns this list. In case of a NULL table name, a warning is logged by ns_Log. The list of tables should be returned in the pds parameter in the form "string\0string\0string\0", which can be implemented as follows:

ns_DStringNAppend(pds, table, strlen(table) +1);
char *ns_dbms-nameBestRowId(ns_DString *pds, ns_DbHandle *handle, char *table);

This function returns the unique identifier for a table. In most cases, it is usually the primary key for the table. In some cases, the system table containing table information contains a unique identifier for each table. In such cases, this identifier is returned. The pds parameter is an initialized ns_DString that gets the primary key (e.g., in Postgres, ns_DStringNAppend(pds, "oid", 4)).
int ns_dbms-nameExec(ns_DbHandle *handle, 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.
int 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 it's 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.

Functions to be Implemented

The set of functions that need to be implemented are:

void *ns_PdDbInit (void)

This function is called once from the 'nspdmain' when the database driver is initialized at process startup. It normally allocates and returns a dbms-specific structure to be passed to all other routines. It also calls any dbms-specific initialization routines. This function does not open a connection to the database.
void ns_PdDbCleanup(void *handle)

This function is called once from the 'nspdmain' when the database driver is initialized at process startup. It performs cleanup associated with process shutdown and frees the database-specific handle.
void ns_PdDbOpen(void *handle, char *openArgs)

This function opens a connection to the database based on the openArgs passed in. The handle passed in is the handle that was returned by ns_PdDbInit, and will usually be cast to some driver-specific data structure. If the open operation is successful, this function should call ns_PdSendString with OK_STATUS. On failure, the function should use ns_PdSendString to return an error string.
void ns_PdDbClose(void *handle)

This function closes the database. It should not free the database handle. If the close operation is successful, this function should call ns_PdSendString with OK_STATUS. On failure, the function should use ns_PdSendString to return an error string.
void ns_PdDbExec(void *handle, char *sql)

This function executes a SQL query. If the SQL is executed successfully, this function should call ns_PdSendString with OK_STATUS followed by either ns_PdSendString(EXEC_RET_ROWS) or ns_PdSendString(EXEC_RET_DML), depending on whether the SQL returned rows or was a DML statement. On failure, the function should use ns_PdSendException to return an error.
void ns_PdDbFlush(void *handle)

Flushes any pending data. Usually this function will call ns_PdDbCancel or something similiar along with any other database specific clean-up routines. If the flush operation is successful, this function should call ns_PdSendString with an OK_STATUS. On failure, the function should use ns_PdSendString to return an error string.
void ns_PdDbCancel(void *handle)

This function cancels the current database operation. If the cancel operation was successful, this function should call ns_PdSendString with OK_STATUS. On failure, the function should use ns_PdSendString to return an error string.
void ns_PdDbBindRow (void *handle)

This function should retrieve from the DBMS a list of column names of rows and return this data to the client. If the bind-row operation is successful, this function should call ns_PdSendString with an OK_STATUS. On failure this function should return an exception code with ns_PdSendException.
void ns_PdDbGetRow(void *handle, char *columnCount)

This function should retrieve from the DBMS the row associated with the columnCount column and send this data to the client. If the get-row operation was successful, this function should call ns_PdSendString with an OK_STATUS and then send the data with ns_PdDbSendData. On failure, the function should use ns_PdSendException to return an error.
void ns_PdDbTableList (void *handle, char *includeSystem)

This function should retrieve the list of table names from the database associated with the handle and send this data to the client. The includeSystem parameter indicates whether to include system tables with this list. The function should implement the following protocol:

If the initial SQL query returns a successful status of DB_ROWS when generating the table names, then this function should:
1. Call ns_PdSendString with an OK_STATUS.
2. Call ns_PdSendString with a size of the item (table name).
3. Call ns_PdSendData to send the actual data associated with the name and to signal the end of data.
4. If an exception occurs during the processing of the table data, then this function should send the partial data to the client and indicate an error with ns_PdDbLog.
If an exception is raised before successfully retrieving some of the table data, then this function should call ns_PdSendException to return an error.
void ns_PdDbGetTableInfo(void *handle, char *tableName)

This function should retrieve the system catalog information (columns, types, etc.) about a table and send this to the client.

If the initial SQL select query returns a successful status of DB_ROWS, then this function should:
1. Call ns_PdSendString with an OK_STATUS.
2. Call ns_PdSendRowInfo to send to the client the column info.
3. Call ns_PdSendRowInfo to send to the client subsequent row info.
4. Call ns_PdSendData to indicate to the client that END_DATA has been reached.
5. If an exception occurs during the processing of the row info, then this function should send the partial data to the client and indicate an error with ns_PdDbLog.
If an exception is raised before successfully retrieving some of the row info, then this function should call ns_PdSendException to return an error.
void ns_PdDbBestRowId(void *handle, char *tableName)

This function retrieves the primary key of a table from the database and sends this to the client. If a table has a primary key, NaviServer can perform row updates and deletes. If the best-row-id operation is successful, this function should call ns_PdSendString with an OK_STATUS. If a best-row-id is found then call ns_PdSendString with the column name; otherwise, call ns_PdSendString with NO_BESTROWID. On failure this function should return an exception code with ns_PdSendException.
void ns_PdDbIndentify(void *handle)

This function sends a string identifying the proxy daemon name and version with ns_PdSendString.
void ns_PdDbGetTypes(void *handle)

This function sends a string of the data types for the database with ns_PdSendString.
void ns_PdDbResultId(void *handle)

This function sends to the client with ns_PdSendString the id of the last object affected by an exec command or a null terminated string.
void ns_PdDbResultRows(void *handle)

This function sends to the client with ns_PdSendString the number of rows affected by last exec command.
void ns_PdDbSetMaxRows(void *handle, char *maxRows)

This function sets the max rows for the database. If this function is successful or the SQL command is undefined for your specific DBMS, it should call ns_PdSendString with an OK_STATUS. If set-max-rows is defined for your specific DBMS and there is an exception raised, then this function should send an exception code with ns_PdSendException.
void ns_PdDbSpExec(void *handle)

This function executes a stored procedure that has been initialized with ns_PdDbSpStart and ns_PdDbSpSetParam.
void ns_PdDbSpGetParams(void *handle)

This function returns output parameters from a previously executed stored procedure.
void ns_PdDbSpReturnCode (void *handle)

This function gets the return code from a previously executed stored procedure.
void ns_PdDbSpSetParam (void *handle, char *args)

This function sets parameters in a stored procedure before executing it.
void ns_PdDbSpStart(void *handle, char *procname)

This function begins the definition of a stored procedure.

Utility Functions

In implementing the above functions, you will need to call some of the various utility functions described below. When using these functions, do not pass a parameter of NULL for string values. If you do, an error message is printed to the syslog and the function returns.

void ns_PdLog(ns_PdLogMsgType errtype, char *fmt, ...)

The ns_PdLog function sends a formatted messages to the client. Allowable values for the log type are the following with a default type of Error: Notice, Trace, Error.
void ns_PdSendString(char *string)

This function sends a string to the client.
void ns_PdSendException(char *code, char *msg)

This sends an exception code and an exception message to the client.
ns_PdParseOpenArgs(char *openargs, char **datasource, char **user, char **password, char **param)

This function parses the datasource, user, password, and param parameters leaving missing elements as NULL. This function is normally called from within ns_PdDbOpen.
void ns_PdSendRowInfo(ns_PdRowInfo * rowInfo)

This function sends a row encapsulated in an ns_PdRowInfo structure to the client.
void ns_PdSendData(char *data, int len)

This function sends data of length len to the client. You indicate that you are finished sending data by calling this function with END_DATA.
void ns_PdFreeRowInfo(ns_PdRowInfo * rowInfo, int fFreeData)

This function frees a ns_PdRowInfo data structure. If fFreeData is a non-zero value, then this function frees the data associated with the ns_PdRowData structure (encapsulated in ns_PdRowInfo) as well.

Constants

The following constants are used in the example drivers:

#define EXCEPTION_CODE_MAX 32

#define EXCEPTION_MSG_MAX 4096

These constants are used for the buffer sizes for the database error/exception code and error/exception message, respectively. The given buffer size must include a terminating null byte. In other words, you can have an exception message of up to 4095 bytes, reserving the last byte for a null byte.