SQL Relay Database Connection Daemons

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.

There are a few other methods you may elect to override as well.

Different API's

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.