Most database API's work like this:
The SQL Relay connection daemon class works this way too. Writing a connection daemon mainly consists of wrapping database API methods in corresponding connection class methods. More specifically, to write a new connection daemon, you create a class which inherits from the "connection" class and implement it's pure virtual methods.
Below is an example. On the left is pseudocode and on the right, the code for the MySQL connection.
The header file...
// Copyright (c) 2000 Your Name Here // See the file LICENSE.GPL for more information #ifndef MYCONNECTION_H #define MYCONNECTION_H #define NUM_CONNECT_STRING_VARS N (how many var=value pairs are there in the connectstring attribute of the sqlrelay:connection tag in sqlrelay.conf for your database) #include <connection.h> include whatever header files you need... include the header file for your database API... class myconnection : public connection { public: myconnection(); ~myconnection(); private: ... these are the implementations of the connection class's pure virtual methods ... int getNumberOfConnectStringVars(); void handleConnectString(); int logIn(); void logOut(); int executeQuery(char *query, long length); char *getErrorMessage(int *liveconnection); void returnHeader(); int noRowsToReturn(); int fetchRow(); void returnRow(); void cleanUpData(); int ping(); char *identify(); int isTransactional(); ... you'll need a connection descriptor, a result set descriptor, a row descriptor, an error message buffer and possibly some other variables ... ... you'll probably want to keep a row count and column count ... ... you'll also need variables to store the connectstring values in ... }; #endif |
mysqlconnection.h...
// Copyright (c) 2000 David Muse // See the file LICENSE.GPL for more information #ifndef MYSQLCONNECTION_H #define MYSQLCONNECTION_H #define NUM_CONNECT_STRING_VARS 6 #include <connection.h> #include <fstream.h> #include <strstream.h> #include <mysql.h> class mysqlconnection : public connection { public: mysqlconnection(); ~mysqlconnection(); private: int getNumberOfConnectStringVars(); void handleConnectString(); int logIn(); void logOut(); int executeQuery(char *query, long length); char *getErrorMessage(int *liveconnection); void returnHeader(); int noRowsToReturn(); int fetchRow(); void returnRow(); void cleanUpData(); int ping(); char *identify(); int isTransactional(); MYSQL mysql; MYSQL_RES *mysqlresult; MYSQL_FIELD *mysqlfield; MYSQL_ROW mysqlrow; int ncols; int nrows; int affectedrows; int connected; strstream *errmesg; char *user; char *password; char *db; char *host; char *port; char *socket; }; #endif |
After writing the header, you'll have to implement the connection class's pure virtual methods. Below is an example like the one above.
// Copyright (c) 1999-2000 Your Name Here // See the file COPYING for more information #include < your header file > #include <datatypes.h> ... you need to include this file too ... ... you may need to include some other headers ... |
// Copyright (c) 1999-2000 David Muse // See the file COPYING for more information #include <mysqlconnection.h> #include <datatypes.h> #include <stdlib.h> |
myconnection::myconnection() { ... initialize whatever you need to here ... } |
mysqlconnection::mysqlconnection() { connected=0; errmesg=NULL; } |
myconnection::~myconnection() { ... deallocate whatever you initialized ... } |
mysqlconnection::~mysqlconnection() { if (errmesg) { delete errmesg; } } |
int myconnection::getNumberOfConnectStringVars() { return NUM_CONNECT_STRING_VARS; } |
int mysqlconnection::getNumberOfConnectStringVars() { return NUM_CONNECT_STRING_VARS; } |
void myconnection::handleConnectString() { ... set your connect string variables using the connectStringValue() method ... } |
void mysqlconnection::handleConnectString() { user=connectStringValue("user"); password=connectStringValue("password"); db=connectStringValue("db"); host=connectStringValue("host"); port=connectStringValue("port"); socket=connectStringValue("socket"); } |
int myconnection::logIn() { ... set some default values for any missing connect string variables ... ... connect to the database ... ... return 1 for success, 0 for failure ... } |
int mysqlconnection::logIn() { // handle host char *hostval; if (host && strlen(host)) { hostval=host; } else { hostval=""; } // handle port int portval; if (port && strlen(port)) { portval=atoi(port); } else { portval=0; } // handle socket char *socketval; if (socket && strlen(socket)) { socketval=socket; } else { socketval=NULL; } // handle db char *dbval; if (db && strlen(db)) { dbval=db; } else { dbval=""; } // initialize database connection structure if (!mysql_init(&mysql)) { return 0; } // log in if (!mysql_real_connect(&mysql,hostval,user,password,dbval, portval,socketval,0)) { logOut(); return 0; } else { connected=1; return 1; } } |
void myconnection::logOut() { ... disconnect from the database ... } |
void mysqlconnection::logOut() { connected=0; mysql_close(&mysql); } |
int myconnection::executeQuery(char *query, long length) { ... set row and column counts to 0 ... ... execute the query, get the result set ... ... get information about the result set such as the row and column counts and the number of affected rows (affected rows are the number of rows affected by an insert, update or delete) ... ... return 1 for success, 0 for failure ... Note: Some databases have a 3 step query execution process involving bind variables. See the information at the end of this document for how to handle those databases. } |
int mysqlconnection::executeQuery(char *query, long length) { // initialize counts ncols=0; nrows=0; // execute the query if (mysql_real_query(&mysql,query,length)) { return 0; } // store the result set if ((mysqlresult=mysql_store_result(&mysql))==(MYSQL_RES *)NULL) { // if there was an error then return failure, otherwise // the query must have been some DML or DDL char *err=mysql_error(&mysql); if (err && strlen(err)) { return 0; } else { return 1; } } // get the column count ncols=mysql_num_fields(mysqlresult); // get the row count nrows=mysql_num_rows(mysqlresult); // get the row count affectedrows=mysql_affected_rows(&mysql); return 1; } |
char *myconnection::getErrorMessage(int *liveconnection) { ... call the database API to get an error message ... ... if that error is a down database, set the "liveconnection" parameter to 1 ... ... return the error message string ... } |
char *mysqlconnection::getErrorMessage(int *liveconnection) { // store the error message because mysql_ping will set it blank if (errmesg) { delete errmesg; } errmesg=new strstream(); *errmesg << mysql_error(&mysql) << ends; // only return an error if the database is up if (connected && !mysql_ping(&mysql)) { *liveconnection=1; return errmesg->str(); } else { *liveconnection=0; return ""; } } |
void myconnection::returnHeader() { ... First, send back the number of rows and affected rows using the sendRowCounts() method. For databases that don't return these values, return -1 instead. ... ... if the query isn't a select, return immediately ... ... Position yourself at the first column. This is important because returnHeader() may be called more than 1 time per result set (if the user suspends the result set for example) and you don't want to be off the end of the column list from the previous call. Some database API's provide methods for accessing the columns by index or require you to store the definitions in your own array. For those databases, this positioning is not necessary. ... ... Run through the columns of the result set, for each, use the sendColumnDefinition() method to return the name, type and size of each. The column type should be one of the types in the datatypes.h file, so you'll have to establish a mapping between one of them and the database API's data types. If your database has a type that's not in that file, add it and submit a patch! ... } |
void mysqlconnection::returnHeader() { // send row counts sendRowCounts((long)nrows,(long)affectedrows); // for DML or DDL queries, return no column info if (!mysqlresult) { return; } // a useful variable int type; // position ourselves on the first field mysql_field_seek(mysqlresult,0); // for each column... for (int i=0; i<ncols; i++) { // fetch the field mysqlfield=mysql_fetch_field(mysqlresult); // append column type to the header if (mysqlfield->type==FIELD_TYPE_STRING) { type=STRING_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_VAR_STRING) { type=VARSTRING_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_DECIMAL) { type=DECIMAL_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_TINY) { type=TINY_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_SHORT) { type=SHORT_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_LONG) { type=LONG_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_FLOAT) { type=FLOAT_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_DOUBLE) { type=DOUBLE_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_LONGLONG) { type=LONGLONG_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_INT24) { type=INT24_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_TIMESTAMP) { type=TIMESTAMP_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_DATE) { type=DATE_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_TIME) { type=TIME_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_DATETIME) { type=DATETIME_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_YEAR) { type=YEAR_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_NEWDATE) { type=NEWDATE_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_NULL) { type=NULL_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_ENUM) { type=ENUM_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_SET) { type=SET_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_TINY_BLOB) { type=TINY_BLOB_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_MEDIUM_BLOB) { type=MEDIUM_BLOB_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_LONG_BLOB) { type=LONG_BLOB_DATATYPE; } else if (mysqlfield->type==FIELD_TYPE_BLOB) { type=BLOB_DATATYPE; } else { type=UNKNOWN_DATATYPE; } // send column definition sendColumnDefinition(mysqlfield->name, strlen(mysqlfield->name), type,(int)mysqlfield->length); } } |
int myconnection::noRowsToReturn() { ... test to see if you're at the end of the result set, if so, return 1, otherwise return 0 ... ... if there is no good way to know this for your database, just return 0 ... } |
int mysqlconnection::noRowsToReturn() { // for DML or DDL queries, return no data if (!mysqlresult) { return 1; } return 0; } |
int myconnection::fetchRow() { ... fetch a row, return a 1 for success and 0 for failure ... } |
int mysqlconnection::fetchRow() { return ((mysqlrow=mysql_fetch_row(mysqlresult))!=NULL); } |
void myconnection::returnRow() { ... run through each column of the result set and return it using the sendField() method ... } |
void mysqlconnection::returnRow() { for (int col=0; col<ncols; col++) { if (mysqlrow[col]) { sendField(mysqlrow[col],strlen(mysqlrow[col])); } else { sendField("",0); } } } |
void myconnection::cleanUpData() { ... if you allocated any memory between getting the query and returning the result set, deallocate it here ... } |
void mysqlconnection::cleanUpData() { if (mysqlresult!=(MYSQL_RES *)NULL) { mysql_free_result(mysqlresult); } } |
int myconnection::ping() { ... test to see if the database connection is still good, return 1 if it is, and 0 if it's not ... } |
int mysqlconnection::ping() { if (!mysql_ping(&mysql)) { return 1; } return 0; } |
char *myconnection::identify() { ... return the name of your database ... } |
char *mysqlconnection::identify() { return "mysql"; } |
int myconnection::isTransactional() { ... you only need this method if your database is not transactional ... ... return a 0 ... } |
int mysqlconnection::isTransactional() { return 0; } |
At this point, you may elect to override some other virtual methods.
When a session is ended (either on purpose or accident) the connection daemon sends either a commit or rollback (depending on how it's configured) to the database if any DML or DDL queries were executed. The following methods are used to implement this functionality.
int prepareQuery()
int inputBindValue()
int oututBindValue()
Some databases have a 3 part process for executing queries or procedural code. In Part 1, the database figures out what steps it will take to run the query or code. In Part 2, local variables or values are "bound" to variables in those steps. In Part 3, the steps are executed. If your database has a process like this, you should override these methods. The prepareQuery() method should execute Part 1 functions. The inputBindValue() and outputBindValue() methods should handle functions for Part 2. And the executeQuery() method should handle Part 3.
int queryIsNotSelect()
int queryIsCommitOrRollback()
The queryIsNotSelect() method returns 0 if the query is a SELECT and 1 if it is not. Similarly, the queryIsCommitOrRollback() method returns 1 if the query is either a COMMIT or ROLLBACK and 0 if it is not. The connection class implements these methods by parsing the text of the query. Many database API's have their own means of identifying the query type. If yours does, then you should use it to override these methods.
void commit()
void rollback()
The commit() and rollback() methods send COMMIT and ROLLBACK queries. The connection class implements these methods by running a COMMIT or ROLLBACK query them through the executeQuery() method. Many database API's have their own commit and rollback functions. If yours does, then you should use them to override these methods.
There are a few other methods you may elect to override as well.
int skipRows(int rows)
The skipRows() method works by calling fetchRow() "rows" times. It returns a 0 if any call to fetchRow() failed, and 1 otherwise. Your database may provide a better way to skip down through the result set. If so, you should use it to override this method.
Some database API's work differently. Those API's may be difficult to work with. For example, the sqlite API requires that you define a callback function, then execute a query. For each row of the result set, the callback function is called. By default, the rows just come flooding in under no particular control. The simplest way to deal with this is to collect them all into memory, then step through them. This is not the most elegant solution and for large result sets, could prove quite costly.
A better solution is to "dam the flood". When implementing the sqlite connection, I forked off a thread to execute the query and callbacks. I then synchronized the callbacks with the main thread using private semaphores. I had to use actual semaphores rather than pthread_mutexes because I needed to initialize them to 0 and mutexes always initialize to 1. Fortunately, the rudiments library provides an easy-to-use semaphore set class.