LIST adaptor: ODBC

This is list adaptor number two: it stores objects in ODBC tables on Windows, and it's a rampup for the MySQL adaptor, which is what I really need, and which will run on Unix as well. This index stuff will eventually replace the taskindex code of the old wftk.

#include <stdio.h>
#include <stdarg.h>
#include <string.h>
#include <time.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <io.h>
#include <errno.h>
#include <windows.h> /* Boy, *this* feels weird! */
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include "xmlapi.h"
#include "xmlobj.h"
#include "../wftk.h"
#include "../wftk_internals.h"

The adaptor_info structure is used to pass adaptor info (duh) back to the config module when it's building an adaptor instance. Here's what it contains:

static char *names[] = 
{
   "init",
   "free",
   "info",
   "create",
   "destroy",
   "add",
   "update",
   "delete",
   "get",
   "query",
   "first",
   "next",
   "rewind",
   "prev",
   "last",
   "attach_open",
   "attach_write",
   "attach_close",
   "attach_cancel",
   "retrieve_open",
   "retrieve_read",
   "retrieve_close"
};

XML * LIST_odbc_init (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_free (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_info (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_create (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_destroy (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_add (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_update (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_delete (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_get (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_query (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_first (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_next (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_rewind (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_prev (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_last (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_attach_open (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_attach_write (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_attach_close (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_attach_cancel (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_retrieve_open (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_retrieve_read (WFTK_ADAPTOR * ad, va_list args);
XML * LIST_odbc_retrieve_close (WFTK_ADAPTOR * ad, va_list args);

static WFTK_API_FUNC vtab[] = 
{
   LIST_odbc_init,
   LIST_odbc_free,
   LIST_odbc_info,
   LIST_odbc_create,
   LIST_odbc_destroy,
   LIST_odbc_add,
   LIST_odbc_update,
   LIST_odbc_delete,
   LIST_odbc_get,
   LIST_odbc_query,
   LIST_odbc_first,
   LIST_odbc_next,
   LIST_odbc_rewind,
   LIST_odbc_prev,
   LIST_odbc_last,
   LIST_odbc_attach_open,
   LIST_odbc_attach_write,
   LIST_odbc_attach_close,
   LIST_odbc_attach_cancel,
   LIST_odbc_retrieve_open,
   LIST_odbc_retrieve_read,
   LIST_odbc_retrieve_close
};

static struct wftk_adaptor_info _LIST_odbc_info =
{
   22,
   names,
   vtab
};

Cool. So here's the incredibly complex function which returns a pointer to that:

struct wftk_adaptor_info * LIST_odbc_get_info ()
{
   return & _LIST_odbc_info;
}

The adaptor can reuse an existing database connection, which is stashed in the binary handle of a "connection" element in the repository. This is getting a tad baroque, perhaps... At any rate, if such a connection element is present, and it matches the parameter, then we'll use it, otherwise we'll create one. Database connections are thus (probably) not going to close until the repository is closed. Which makes sense to me.

The connection element looks like this:
<connection storage="odbc:wftk" conn="DSN=wftk"/>
Then we can reference it like this:
<list id="..." storage="odbc:wftk"/>
Thus the connection starts to look a lot like a predefined alias, see? This makes a lot more sense than the wftk config file, I think.

struct _LIST_odbc_handles {
   SQLHENV henv;
   SQLHDBC hdbc;
   int ok;
};

void LIST_odbc_cleanup (struct _LIST_odbc_handles *handles)
{
   if (handles) {
      SQLDisconnect (handles->hdbc);
      SQLFreeHandle (SQL_HANDLE_DBC, handles->hdbc);
      SQLFreeHandle (SQL_HANDLE_ENV, handles->henv);
      free (handles);
   }
}

XML * LIST_odbc_init (WFTK_ADAPTOR * ad, va_list args)
{
   const char * parms;
   char * mark;
   XML * repository = (XML *) ad->session;
   XML * conn;
   struct _LIST_odbc_handles *handles;
   SQLRETURN rslt;
   SQLSMALLINT junk;
   char buf[1024];

   parms = xml_attrval (ad->parms, "parm");
   if (!*parms) {
      xml_set (ad->parms, "error", "No ODBC datasource specified");
      return NULL;
   }

   /* Find or create connection. */
   xml_setf (repository, "scratch", "odbc:%s", parms);
   conn = xml_search (repository, "connection", "storage", xml_attrval (repository, "scratch"));
   if (!conn) {
      conn = xml_create ("connection");
      xml_set (conn, "storage", xml_attrval (repository, "scratch"));
      xml_setf (conn, "conn", "DSN=%s", parms);
      xml_append (repository, conn);
   }

   /* Is connection already made?  If not, connect. */
   handles = (struct _LIST_odbc_handles *) xml_getbin (conn);
   if (!handles) {
      handles = (struct _LIST_odbc_handles *) malloc (sizeof (struct _LIST_odbc_handles));
      if (!handles) {
        xml_set (ad->parms, "error", "Unable to allocate binary stash.");
        return NULL;
      }
      handles->henv = SQL_NULL_HANDLE;
      handles->hdbc = SQL_NULL_HANDLE;
      handles->ok = 0;
   }
   ad->bindata = (void *) handles;
   if (!handles->ok) {
      rslt = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &(handles->henv));  /* My first C ODBC statement ever!  (Now somewhat transplanted.) */
      if (rslt == SQL_ERROR) {
         xml_set (ad->parms, "error", "Unable to allocate ODBC environment handle.");
         free ((void *) handles);
         ad->bindata = 0;
         return (XML *) 0;
      }
      SQLSetEnvAttr (handles->henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC2, 0);

      rslt = SQLAllocHandle (SQL_HANDLE_DBC, handles->henv, &(handles->hdbc));
      if (rslt == SQL_ERROR) {
         xml_set (ad->parms, "error", "Unable to allocate ODBC connection handle.");
         SQLFreeHandle (SQL_HANDLE_ENV, handles->henv);
         free ((void *) handles);
         ad->bindata = 0;
         return (XML *) 0;
      }

      rslt = SQLDriverConnect (handles->hdbc, NULL, (char *) xml_attrval (conn, "conn"), (SQLSMALLINT) strlen (xml_attrval (conn, "conn")), buf, (SQLSMALLINT) sizeof(buf), &junk, SQL_DRIVER_NOPROMPT);
      if (rslt == SQL_ERROR) {
         xml_set (ad->parms, "error", "Unable to connect to database specified.");
         SQLFreeHandle (SQL_HANDLE_DBC, handles->hdbc);
         SQLFreeHandle (SQL_HANDLE_ENV, handles->henv);
         free ((void *) handles);
         ad->bindata = 0;
         return (XML *) 0;
      }

      handles->ok = 1;
      xml_setbin (conn, handles, LIST_odbc_cleanup);
   }

   return (XML *) 0;
}
XML * LIST_odbc_free (WFTK_ADAPTOR * ad, va_list args) { return (XML *) 0; }

Next up is the info call, which builds and returns a little XML telling the caller about the adaptor. If the adaptor itself is NULL, then it just returns info about the installed adaptor handler; otherwise it's free to elaborate on the adaptor instance.

XML * LIST_odbc_info (WFTK_ADAPTOR * ad, va_list args) {
   XML * info;

   info = xml_create ("info");
   xml_set (info, "type", "list");
   xml_set (info, "name", "odbc");
   xml_set (info, "ver", "1.0.0");
   xml_set (info, "compiled", __TIME__ " " __DATE__);
   xml_set (info, "author", "Michael Roberts");
   xml_set (info, "contact", "wftk@vivtek.com");
   xml_set (info, "extra_functions", "0");

   return (info);
}

So. Down to business. I'm most interested in indexing at the moment, so I'm going to start with the add/mod/del functions, then do the create/destroy functions (the latter easy under SQL), and only then do querying and retrieval. So. Let's do it. First off let's define a couple of helper functions.

static void _LIST_odbc_builderrmsg (XML * ap, SQLHSTMT stmt)
{
   char buf[1024];
   char msg[128];
   SQLSMALLINT len;
   char code[16];
   SQLINTEGER native;

   SQLGetDiagRec (SQL_HANDLE_STMT, stmt, 1, code, &native, msg, sizeof(msg), &len);
   sprintf (buf, "%s[%s %d] %s", xml_attrval (ap, "error"), code, native, msg);
   xml_set (ap, "error", buf);
}

static void sql_escaped_append (XML * s, const char * q, const char * v)
{
   char * mark;
   if (!v) return;
   while (mark = strchr (v, '\'')) {
      xml_attrncat (s, q, v, mark - v);
      xml_attrcat (s, q, "'");
      v = mark + 1;
   }
   xml_attrcat (s, q, v);
}

So. Down to business. Adding is first up. I initially wanted to say it pretty straightforward, but really -- it isn't. Too much going on, what with checking whether we need to return a generated key, testing for the various data types we know about, and such. For the moment, we're ignoring links, but pretty soon we'll have to start working with them. Their entire point, after all, is for relational databases.

static XML * _LIST_odbc_add (WFTK_ADAPTOR * ad, XML * list, XML * object)
{
   struct _LIST_odbc_handles *handles = (struct _LIST_odbc_handles *) ad->bindata;
   const char * key;
   const char * value;
   XML * mark;
   XML * field;
   XML * scratch;


   int keygen = 0; /* Set if we need to generate a unique field. */

   SQLHSTMT stmt;
   SQLRETURN rslt;
   SQLSMALLINT junk;
   char state[6];


   if (!handles) {
      xml_set (ad->parms, "error", "Database connection not initialized.");
      return 0;
   }
   if (!handles->ok) {
      xml_set (ad->parms, "error", "Database connection not initialized.");
      return 0;
   }

   scratch = xml_create ("s");

   if (!*xml_attrval (list, "table")) xml_set (list, "table", xml_attrval (list, "id"));
   xml_set (scratch, "fields", "");
   xml_set (scratch, "values", "");

   keygen = 0;

   mark = xml_firstelem (list);
   while (mark) {
      if (xml_is (mark, "field")) {
         if (!keygen && !strcmp (xml_attrval (mark, "special"), "key")) {
            keygen = 1;
            xml_set (scratch, "keygen", xml_attrval (mark, "id"));
            field = xml_search (object, "field", "id", xml_attrval (mark, "id"));
            if (!field) {
               field = xml_create ("field");
               xml_set (field, "id", xml_attrval (mark, "id"));
               if (*xml_attrval (mark, "type")) xml_set (field, "type", xml_attrval (mark, "type"));
               xml_append_pretty (object, field);
            }
            xml_set_nodup (scratch, "keygeninit", xmlobj_get (object, list, xml_attrval (mark, "id")));
         } else {
            xml_attrcat (scratch, "fields", ", ");
            xml_attrcat (scratch, "fields", xml_attrval (mark, "id"));

            xml_attrcat (scratch, "values", ", ");
            if (strcmp (xml_attrval (mark, "special"), "constant")) {
               value = xmlobj_get (object, list, xml_attrval (mark, "id"));
               if (!value) value = strdup ("");
            } else {
               value = xml_stringcontenthtml (mark);
               field = xml_search (object, "field", "id", xml_attrval (mark, "id"));
               if (!field) {
                  field = xml_create ("field");
                  xml_set (field, "id", xml_attrval (mark, "id"));
                  if (*xml_attrval (mark, "type")) xml_set (field, "type", xml_attrval (mark, "type"));
                  xml_append_pretty (object, field);
               }
               xml_replacecontent (field, xml_createtext (value));
            }

            if (!strcmp (xml_attrval (mark, "special"), "now")) {
               xml_attrcat (scratch, "values", "{fn NOW()}");
            } else if (!strncmp (xml_attrval (mark, "type"), "numeric", 7)) {
               if (!value) {
                  xml_attrcat (scratch, "values", "0");
               } else if (!*value) {
                  xml_attrcat (scratch, "values", "0");
               } else {
                  xml_attrcat (scratch, "values", value);
               }
            } else if (!strncmp (xml_attrval (mark, "type"), "date", 4)) {
               xml_attrcat (scratch, "values", "#");
               xml_attrcat (scratch, "values", value);  /* TODO: be more resilient to non-ODBC-sanctioned values. */
               xml_attrcat (scratch, "values", "#");
            } else {
               xml_attrcat (scratch, "values", "'");
               sql_escaped_append (scratch, "values", value);
               xml_attrcat (scratch, "values", "'");
            }
            if (value) free ((void *)value);
         }
      }
      mark = xml_nextelem (mark);
   }

   if (keygen) {
      xml_setf (scratch, "unique", "%d%d", time(NULL) % 100000, rand() * 100 / RAND_MAX);
      xml_setf (scratch, "query", "insert into %s (%s %s) values ('%s%s' %s)",
                                  xml_attrval (list, "table"),
                                  xml_attrval (scratch, "keygen"),
                                  xml_attrval (scratch, "fields"),
                                  xml_attrval (scratch, "keygeninit"),
                                  xml_attrval (scratch, "unique"),
                                  xml_attrval (scratch, "values"));
   } else {
      xml_setf (scratch, "query", "insert into %s (%s) values (%s)", xml_attrval (list, "table"), xml_attrval (scratch, "fields") + 2, xml_attrval (scratch, "values") + 2);
   }

   /* Now execute the query we just built. */
   rslt = SQLAllocHandle (SQL_HANDLE_STMT, handles->hdbc, &stmt);
   if (rslt != SQL_ERROR) {
      rslt = SQL_ERROR;
      while (rslt == SQL_ERROR) {
         rslt = SQLExecDirect (stmt, (char *)xml_attrval (scratch, "query"), SQL_NTS);
         if (rslt == SQL_ERROR) {
            SQLError (SQL_NULL_HENV, SQL_NULL_HDBC, stmt, state, NULL, NULL, 0, 0);
            if (!keygen || strcmp (state, "23000")) {  /* Integrity constraint: try again. */
               xml_setf (ad->parms, "error", "SQL error from [%s] ", xml_attrval (scratch, "query"));
               _LIST_odbc_builderrmsg (ad->parms, stmt);
               break;
            }
            xml_setf (scratch, "unique", "%d%d", time(NULL), rand() * 100 / RAND_MAX);
            xml_setf (scratch, "query", "insert into %s (%s %s) values ('%s%s' %s)",
                                        xml_attrval (list, "table"),
                                        xml_attrval (scratch, "keygen"),
                                        xml_attrval (scratch, "fields"),
                                        xml_attrval (scratch, "keygeninit"),
                                        xml_attrval (scratch, "unique"),
                                        xml_attrval (scratch, "values"));

         }
      }
      SQLTransact (handles->henv, handles->hdbc, SQL_COMMIT);
      SQLFreeHandle (SQL_HANDLE_STMT, stmt);
   }
   if (keygen) {
      xml_attrcat (scratch, "keygeninit", xml_attrval (scratch, "unique"));
      xmlobj_set (object, NULL, xml_attrval (scratch, "keygen"), xml_attrval (scratch, "keygeninit"));
      xml_set (object, "id", xml_attrval (scratch, "keygeninit"));
   }

   /* TODO: handle links. */

   xml_free (scratch);
   return NULL;
}

XML * LIST_odbc_add (WFTK_ADAPTOR * ad, va_list args) {
   XML * list = NULL;
   XML * obj = NULL;

   if (args) list = va_arg (args, XML *);
   if (!list) {
      xml_set (ad->parms, "error", "No list descriptor given.");
      return (XML *) 0;
   }
   obj = va_arg (args, XML *);
   if (!obj) {
      xml_set (ad->parms, "error", "No object given.");
      return (XML *) 0;
   }

   return _LIST_odbc_add (ad, list, obj);
}

Updating is a little easier. We do have to worry about the key field; at the moment, we assume it's set as an attribute of the list definition, which is probably a facile assumption.

static XML * _LIST_odbc_update (WFTK_ADAPTOR * ad, XML * list, XML * object)
{
   struct _LIST_odbc_handles *handles = (struct _LIST_odbc_handles *) ad->bindata;
   const char * key;
   const char * value;
   XML * mark;
   XML * field;
   XML * scratch;

   SQLHSTMT stmt;
   SQLRETURN rslt;
   SQLSMALLINT junk;
   SDWORD rows;
   char state[6];

   if (!handles) {
      xml_set (ad->parms, "error", "Database connection not initialized.");
      return 0;
   }
   if (!handles->ok) {
      xml_set (ad->parms, "error", "Database connection not initialized.");
      return 0;
   }

   scratch = xml_create ("s");

   if (!*xml_attrval (list, "table")) xml_set (list, "table", xml_attrval (list, "id"));
   xml_set (scratch, "key", xml_attrval (object, "id"));
   if (!*xml_attrval (scratch, "key")) {
      xml_set_nodup (scratch, "key", xmlobj_getkey (object, list));
   }
   if (!*xml_attrval (list, "key")) {
      xml_free (scratch);
      xml_set (ad->parms, "error", "List definition does not specify key column.");
      return (XML *) 0;
   }
   if (!*xml_attrval (scratch, "key")) {
      xml_free (scratch);
      xml_set (ad->parms, "error", "Unable to determine key for update.");
      return (XML *) 0;
   }
   xml_set (scratch, "sets", "");

   mark = xml_firstelem (object);
   while (mark) {
      if (xml_is (mark, "field")) {
         xml_attrcat (scratch, "sets", ", ");
         xml_attrcat (scratch, "sets", xml_attrval (mark, "id"));
         xml_attrcat (scratch, "sets", "=");

         value = xmlobj_get (object, list, xml_attrval (mark, "id"));
         if (!strncmp (xml_attrval (mark, "type"), "numeric", 7)) {
            if (!*value) {
               xml_attrcat (scratch, "sets", "0");
            } else {
               xml_attrcat (scratch, "sets", value);
            }
         } else if (!strncmp (xml_attrval (mark, "type"), "date", 4)) {
            xml_attrcat (scratch, "sets", "#");
            xml_attrcat (scratch, "sets", value);  /* TODO: be more resilient to non-ODBC-sanctioned values. */
            xml_attrcat (scratch, "sets", "#");
         } else {
            xml_attrcat (scratch, "sets", "'");
            sql_escaped_append (scratch, "sets", value);
            xml_attrcat (scratch, "sets", "'");
         }
         free ((void *)value);
      }
      mark = xml_nextelem (mark);
   }

   xml_setf (scratch, "query", "update %s set %s where %s='%s'", xml_attrval (list, "table"), xml_attrval (scratch, "sets") + 2, xml_attrval (list, "key"), xml_attrval (scratch, "key"));

   /* Now execute the query we just built. */
   rslt = SQLAllocHandle (SQL_HANDLE_STMT, handles->hdbc, &stmt);
   if (rslt != SQL_ERROR) {
      rslt = SQLExecDirect (stmt, (char *)xml_attrval (scratch, "query"), SQL_NTS);
      if (rslt != SQL_ERROR) {
         if (!xml_is (object, "diff")) { /* Note: we only allow an insertion for a complete record, not a diff. */
            SQLRowCount (stmt, &rows);
            if (rows == 0) {
               xml_free (scratch);
               SQLFreeHandle (SQL_HANDLE_STMT, stmt);
               return _LIST_odbc_add (ad, list, object);
            }
         }
      }
      if (rslt == SQL_ERROR) {
         xml_setf (ad->parms, "error", "SQL error from [%s] ", xml_attrval (scratch, "query"));
         _LIST_odbc_builderrmsg (ad->parms, stmt);
      }
      SQLTransact (handles->henv, handles->hdbc, SQL_COMMIT);
      SQLFreeHandle (SQL_HANDLE_STMT, stmt);
   }
   xml_free (scratch);
   return 0;
}


XML * LIST_odbc_update (WFTK_ADAPTOR * ad, va_list args) {
   XML * list;
   XML * obj;

   if (args) list = va_arg (args, XML *);
   if (!list) {
      xml_set (ad->parms, "error", "No list descriptor given.");
      return (XML *) 0;
   }
   obj = va_arg (args, XML *);
   if (!obj) {
      xml_set (ad->parms, "error", "No object given.");
      return (XML *) 0;
   }

   return _LIST_odbc_update (ad, list, obj);
}

static XML * _LIST_odbc_delete (WFTK_ADAPTOR * ad, XML * list, char * key)
{
   struct _LIST_odbc_handles *handles = (struct _LIST_odbc_handles *) ad->bindata;
   const char * value;
   XML * mark;
   XML * field;
   XML * scratch;

   SQLHSTMT stmt;
   SQLRETURN rslt;
   SQLSMALLINT junk;
   char state[6];

   if (!handles) {
      xml_set (ad->parms, "error", "Database connection not initialized.");
      return 0;
   }
   if (!handles->ok) {
      xml_set (ad->parms, "error", "Database connection not initialized.");
      return 0;
   }

   scratch = xml_create ("s");

   if (!*xml_attrval (list, "table")) xml_set (list, "table", xml_attrval (list, "id"));
   if (!*xml_attrval (list, "key")) {
      xml_free (scratch);
      xml_set (ad->parms, "error", "List definition does not specify key column.");
      return (XML *) 0;
   }

   xml_setf (scratch, "query", "delete from %s where %s='%s'", xml_attrval (list, "table"), xml_attrval (list, "key"), key);

   /* Now execute the query we just built. */
   rslt = SQLAllocHandle (SQL_HANDLE_STMT, handles->hdbc, &stmt);
   if (rslt != SQL_ERROR) {
      rslt = SQL_ERROR;
      while (rslt == SQL_ERROR) {
         rslt = SQLExecDirect (stmt, (char *)xml_attrval (scratch, "query"), SQL_NTS);

         if (rslt == SQL_ERROR) {
            xml_setf (ad->parms, "error", "SQL error from [%s] ", xml_attrval (scratch, "query"));
            _LIST_odbc_builderrmsg (ad->parms, stmt);
         }
      }
      SQLTransact (handles->henv, handles->hdbc, SQL_COMMIT);
      SQLFreeHandle (SQL_HANDLE_STMT, stmt);
   }
   xml_free (scratch);
   return 0;
}



XML * LIST_odbc_delete (WFTK_ADAPTOR * ad, va_list args) {
   XML * list;
   char * key;

   if (args) list = va_arg (args, XML *);
   if (!list) {
      xml_set (ad->parms, "error", "No list descriptor given.");
      return (XML *) 0;
   }
   key = va_arg (args, char *);
   if (!key) {
      xml_set (ad->parms, "error", "No key given.");
      return (XML *) 0;
   }

   return _LIST_odbc_delete (ad, list, key);
}

Next stop: queries. Naturally, queries are where RDBMS-type list adaptors will excel. For now, I'm going to omit any support for reverse scanning of query results; iteration will simply have to work in the forward direction only. Frankly, I'm not even sure if there's any reason to care.

The query in this adaptor returns all known fields, as they're just as cheap to retrieve as not to retrieve (or close). A full get will also retrieve links, at least it will later. For now, that's not terribly important either. A common configuration for a repository is going to be a relatively restricted index which keys content stored in a local directory. Thus a query will ask the RDBMS index, but a get will retrieve the file directly.

Like the directory adaptor, we define a separate scanner function which is used by both query and iteration calls. If the latter, the context of the iteration must be stored. The scanner in this case is called "select" -- what it does is to define the query and execute it. The query function will read all results and return the resulting list; the iterator functions on the other hand will not do that, but rather will simply retrieve rows when asked.

static void _LIST_odbc_freestmt (void * stmt)
{
   SQLFreeHandle (SQL_HANDLE_STMT, (SQLHSTMT) stmt);
}

static void _LIST_odbc_select (WFTK_ADAPTOR * ad, XML * list, char * key) {
   struct _LIST_odbc_handles *handles = (struct _LIST_odbc_handles *) ad->bindata;
   SQLHSTMT stmt;
   SQLRETURN rslt;
   XML * scratch;
   XML * field;
   char * value;

   if (!handles) return;
   if (!handles->ok) return;

   scratch = xml_create ("s");

   if (!*xml_attrval (list, "table")) xml_set (list, "table", xml_attrval (list, "id"));

   /* Build the where clause for the query. */
   xml_set (scratch, "where", xml_attrval (list, "where"));
   field = xml_firstelem (list);
   while (field) {
      if (xml_is (field, "field")) {
         if (!strcmp (xml_attrval (field, "special"), "constant")) {
            value = xml_stringcontenthtml (field);
            if (*xml_attrval (scratch, "where")) { xml_attrcat (scratch, "where", " and "); }
            xml_attrcat (scratch, "where", xml_attrval (field, "id"));
            xml_attrcat (scratch, "where", "='");
            sql_escaped_append (scratch, "where", value);
            xml_attrcat (scratch, "where", "'");
            free ((void *) value);
         }
      }
      field = xml_nextelem (field);
   }
   if (key) {
      if (!*xml_attrval (list, "key")) {
         xml_free (scratch);
         xml_set (ad->parms, "error", "List definition does not specify key column.");
         return;
      }
      if (*xml_attrval (scratch, "where")) { xml_attrcat (scratch, "where", " and "); }
      xml_attrcat (scratch, "where", xml_attrval (list, "key"));
      xml_attrcat (scratch, "where", "='");
      sql_escaped_append (scratch, "where", key);
      xml_attrcat (scratch, "where", "'");
   }

   /* Build the query itself. */
   xml_setf (list, "_query", "select * from %s", xml_attrval (list, "table"));
   if (*xml_attrval (scratch, "where")) {
      xml_attrcat (list, "_query", " where ");
      xml_attrcat (list, "_query", xml_attrval (scratch, "where"));
   }
   if (*xml_attrval (list, "order")) {
      xml_attrcat (list, "_query", " order by ");
      xml_attrcat (list, "_query", xml_attrval (list, "order"));
   }
   xml_free (scratch);

   /* Allocate a statement handle and stash it in the list XML. */
   rslt = SQLAllocHandle (SQL_HANDLE_STMT, handles->hdbc, &stmt);
   if (rslt == SQL_ERROR) {
      xml_set (ad->parms, "error", "Unable to allocate ODBC statement handle.");
      xml_free (scratch);
      return;
   }
   xml_setbin (list, (void *) stmt, _LIST_odbc_freestmt);

   /* Execute the query. */
   rslt = SQLExecDirect (stmt, (char *)xml_attrval (list, "_query"), SQL_NTS);
   xml_set (list, "_status", "ok");
   if (rslt == SQL_ERROR) {
      xml_set (ad->parms, "error", "SQL error selecting data ");
      xml_set (list, "_status", "error");
      _LIST_odbc_builderrmsg (ad->parms, stmt);
      SQLFreeHandle (SQL_HANDLE_STMT, (SQLHSTMT) stmt);
      xml_setbin (list, NULL, NULL);
   }
}

Selection is only half the battle, though; we also need retrieval. Retrieval takes a currently active statement handle and attempts to retrieve a row from it; if there is no row, it fails (and sets a flag in the list.) Retrieval will be used for the "get" function as well.

Retrieval takes a flag "save_row" by which the caller indicates whether the retrieved row should be appended to the list or not.

static XML * _LIST_odbc_retrieve (WFTK_ADAPTOR * ad, XML * list, int save_row) {
   struct _LIST_odbc_handles *handles = (struct _LIST_odbc_handles *) ad->bindata;
   SQLHSTMT stmt;
   SQLRETURN rslt;
   SQLSMALLINT numcols;
   SQLSMALLINT col;
   SQLSMALLINT column_len;
   long value_len;
   XML * ret;
   char column[256];
   char value[1024];
   XML * field;

   if (!handles) return (XML *) 0;
   if (!handles->ok) return (XML *) 0;

   stmt = (SQLHSTMT) xml_getbin (list);
   if (!stmt) return (XML *) 0;

   /* Retrieve a row; if none, free the statement and return NULL. */
   SQLNumResultCols (stmt, &numcols);
   rslt = SQLFetch (stmt);
   if (rslt != SQL_SUCCESS) {
      SQLFreeHandle (SQL_HANDLE_STMT, (SQLHSTMT) stmt);
      xml_setbin (list, NULL, NULL);
      xml_set (list, "_status", "complete");
      return (XML *) 0;
   }

   ret = xml_create (*xml_attrval (list, "element") ? xml_attrval (list, "element") : "record");
   if (save_row) xml_append (list, ret);

   for (col=1; col <= numcols; col++) {
       SQLColAttribute (stmt, col, SQL_DESC_NAME, column, sizeof(column), &column_len, NULL);
       strlwr (column);
       field = xml_search (list, "field", "id", column);

       if (field) {
          value_len = sizeof(value);
          SQLGetData (stmt, col, SQL_C_CHAR, value, sizeof(value), &value_len);
          xmlobj_set (ret, list, xml_attrval (field, "id"), value);
       }
   }
   xml_set_nodup (ret, "id", xmlobj_getkey (ret, list));

   return ret;
}

Now that we know how to select and retrieve, we have all the building blocks we need to define the adaptor functionality (query, iteration, and get.) For this sort of usage, the list is assumed to be a copy of the repository's list definition, because it will be used to store cursor information.

XML * LIST_odbc_query (WFTK_ADAPTOR * ad, va_list args)
{
   XML * list;
   int count = 0;

   if (args) list = va_arg (args, XML *);
   if (!list) {
      xml_set (ad->parms, "error", "No list descriptor given.");
      return (XML *) 0;
   }

   _LIST_odbc_select (ad, list, NULL); /* NULL = no specific key. */

   /* Retrieve all results. */
   while (_LIST_odbc_retrieve (ad, list, 1)) { /* 1 = save row in list. */
      count ++;
   }

   xml_setnum (list, "count", count);
   return list;
}

Iteration is the same thing, really, except that we don't save anything while iterating.

XML * LIST_odbc_first (WFTK_ADAPTOR * ad, va_list args) {
   XML * list;

   if (args) list = va_arg (args, XML *);
   if (!list) {
      xml_set (ad->parms, "error", "No list descriptor given.");
      return (XML *) 0;
   }

   _LIST_odbc_select (ad, list, NULL);

   return _LIST_odbc_retrieve (ad, list, 0);
}
XML * LIST_odbc_next (WFTK_ADAPTOR * ad, va_list args) {
   XML * list;

   if (args) list = va_arg (args, XML *);
   if (!list) {
      xml_set (ad->parms, "error", "No list descriptor given.");
      return (XML *) 0;
   }

   return _LIST_odbc_retrieve (ad, list, 0);
}
XML * LIST_odbc_rewind (WFTK_ADAPTOR * ad, va_list args) {
   xml_set (ad->parms, "error", "'rewind' command not supported under ODBC");
}
XML * LIST_odbc_prev (WFTK_ADAPTOR * ad, va_list args)
{
   xml_set (ad->parms, "error", "'prev' command not supported under ODBC");
}
XML * LIST_odbc_last (WFTK_ADAPTOR * ad, va_list args)
{
   xml_set (ad->parms, "error", "'last' command not supported under ODBC");
}

Retrieval of a single keyed record is pretty easy -- we just make a copy of the list spec as cursor, perform the retrieval query with the key attached, save the result, free the list copy, and return the result. The result may be NULL, if the key doesn't exist, which is exactly what we want.

XML * LIST_odbc_get (WFTK_ADAPTOR * ad, va_list args)
{
   XML * list;
   char * key;
   XML * copy;
   XML * ret;

   if (args) list = va_arg (args, XML *);
   if (!list) {
      xml_set (ad->parms, "error", "No list descriptor given.");
      return (XML *) 0;
   }
   key = va_arg (args, char *);
   copy = xml_copy (list);

   _LIST_odbc_select (ad, copy, key);
   ret = _LIST_odbc_retrieve (ad, copy, 0);
   xml_free (copy);

   return ret;
}

Creation and destruction of lists (i.e. tables) will be fairly straightforward DML -- but I'm unsure whether I should be checking for table existence or not. There are a lot of potential traps as far as consistency is concerned, as a constant field may be used to allow lists to share a table -- but that means if one list drops/creates the table, the others are disrupted. Likewise, there needs to be some sort of logic allowing lists of different structure either to share the table or at least to note that they can't. So (since I don't really need this yet) I'm going to leave it for later.

XML * LIST_odbc_create (WFTK_ADAPTOR * ad, va_list args)
{
   return NULL;
}
XML * LIST_odbc_destroy (WFTK_ADAPTOR * ad, va_list args)
{
   return NULL;
}

So (January 12, 2002) the next thing to address is attachments. I think it's likely that handling attachments will be the last new thing that the list storage adaptor will have to handle itself. At any rate, an attachment is a regular field value, except it is generally stored separately from the object. As far as storage is concerned, attachments are pretty straightforward: they're files, or something like files. You open them, set their MIME types, read and write to streams, close them.

It's important to realize that actually telling the object where its attachment is stored is up to the repository manager library, not the adaptor. I waffled about this a lot, but essentially the list adaptor shouldn't be dependent on the repmgr, so that precludes any knowledge of the structure of record objects, and so (for instance) to open an attachment for retrieval, the repmgr will simply give the adaptor back whatever the adaptor told it earlier was the "location" of the attachment. In our case here, this is a filename local to the adaptor's controlled directory, but in a database it may be a unique key into a BLOB table or something.

XML * LIST_odbc_attach_open (WFTK_ADAPTOR * ad, va_list args) {
   XML * list;
   char * key;
   char * field;
   char * filename;
   struct stat statbuf;
   XML * mark;
   XML * ret;
   FILE * file;

   if (args) list = va_arg (args, XML *);
   if (!list) {
      xml_set (ad->parms, "error", "No list descriptor given.");
      return (XML *) 0;
   }
   key = va_arg (args, char *);
   field = va_arg (args, char *);
   filename = va_arg (args, char *);

   /* If we're not given a fieldname, then we'll just scan the list definition to find the first "document"-type field. */
   if (!field) {
      mark = xml_search (list, "field", "type", "document");
      if (!mark) {
         xml_set (ad->parms, "error", "No attachment field given and no default exists.");
         return NULL;
      }
      field = (char *) xml_attrval (mark, "id");
   }

   ret = xml_create ("attachment-handle");

   xml_set (ret, "dir", xml_attrval (ad->parms, "basedir"));
   if (!*xml_attrval (ad->parms, "subdir")) xml_set (ad->parms, "subdir", xml_attrval (list, "id"));
   if (strcmp (xml_attrval (ad->parms, "subdir"), ".")) {
      xml_attrcat (ret, "dir", xml_attrval (ad->parms, "subdir"));
      xml_attrcat (ret, "dir", "/");
   }
   xml_setf (ret, "adaptor", "odbc:%s", xml_attrval (ret, "dir"));

   /* If we're supplied with a filename, then that file can't already exist in our controlled
      directory. */
   if (filename && *filename) {
      xml_setf (ret, "location", filename);
      xml_setf (ret, "file", "%s%s", xml_attrval (ret, "dir"), filename);
      xml_set (ret, "tempfile", xml_attrval (ret, "file"));
      if (stat (xml_attrval (ret, "file"), &statbuf) != -1) { /* File exists already. */
         xml_setf (ad->parms, "error", "File %s is already present.", filename);
         xml_free (ret);
         return NULL;
      }
   } else {
      xml_setf (ret, "location", "_att_%s_%s.dat", key, field);
      xml_setf (ret, "file", "%s%s", xml_attrval (ret, "dir"), xml_attrval (ret, "location"));
      xml_setf (ret, "tempfile", "%s_newatt_%s_%s.dat", xml_attrval (ret, "dir"), key, field);
   }

   file = fopen (xml_attrval (ret, "file"), "w");
   if (!file) {
      xml_setf (ad->parms, "error", "Unable to open file %s for writing.", xml_attrval (ret, "file"));
      xml_free (ret);
      return NULL;
   }

   xml_setbin (ret, file, fclose);
   xml_set (ret, "content-type", "text/plain");

   return (ret);
}

Writing and closing are easy -- note that when closing the attachment, we do nothing else; the repository manager wrapped around this adaptor will take care of writing any location information into the object for the attachment, any version-control work, or whatever else. Otherwise we'd be duplicating that logic in every adaptor, which doesn't make a lot of sense.

XML * LIST_odbc_attach_write (WFTK_ADAPTOR * ad, va_list args) {
   void * buffer;
   int size, number;
   XML * handle;

   if (!args) {
      xml_set (ad->parms, "error", "No arguments given.");
      return NULL;
   }
   buffer = va_arg (args, void *);
   size = va_arg (args, int);
   number = va_arg (args, int);
   handle = va_arg (args, XML *);

   xml_setnum (handle, "last-write", fwrite (buffer, size, number, xml_getbin(handle)));
   return NULL;
}
XML * LIST_odbc_attach_cancel (WFTK_ADAPTOR * ad, va_list args) {
   XML * handle;

   if (!args) {
      xml_set (ad->parms, "error", "No arguments given.");
      return NULL;
   }
   handle = va_arg (args, XML *);

   fclose (xml_getbin (handle));
   unlink (xml_attrval (handle, "tempfile"));
   return NULL;
}
XML * LIST_odbc_attach_close (WFTK_ADAPTOR * ad, va_list args) {
   XML * handle;

   if (!args) {
      xml_set (ad->parms, "error", "No arguments given.");
      return NULL;
   }
   handle = va_arg (args, XML *);

   fclose (xml_getbin (handle));
   rename (xml_attrval (handle, "tempfile"), xml_attrval (handle, "file"));
   return NULL;
}

Retrieval is easier than attachment, because we already have a filename. The filename is stored in the object, using the attribute "filename" -- this reserves the content of the field element for version control or whatever else may be appropriate.

XML * LIST_odbc_retrieve_open (WFTK_ADAPTOR * ad, va_list args) {
   XML * list = NULL;
   XML * fld;
   char * key;
   char * field;
   XML * mark;
   XML * ret;
   FILE * file;
   WFTK_ADAPTOR * ad2;

   if (args) list = va_arg (args, XML *);
   if (!list) {
      xml_set (ad->parms, "error", "No list given.");
      return (XML *) 0;
   }
   key = va_arg (args, char *);
   fld = va_arg (args, XML *);

   ret = xml_create ("attachment-handle");

   xml_set (ret, "dir", xml_attrval (ad->parms, "basedir"));
   if (!*xml_attrval (ad->parms, "subdir")) xml_set (ad->parms, "subdir", xml_attrval (list, "id"));
   if (strcmp (xml_attrval (ad->parms, "subdir"), ".")) {
      xml_attrcat (ret, "dir", xml_attrval (ad->parms, "subdir"));
      xml_attrcat (ret, "dir", "/");
   }
   xml_setf (ret, "adaptor", "odbc:%s", xml_attrval (ret, "dir"));

   if (fld) {
      xml_set (ret, "location", xml_attrval (fld, "location"));
   }
   if (!*xml_attrval (ret, "location")) xml_setf (ret, "location", "_att_%s_%s.dat", key, xml_attrval (fld, "id"));
   xml_setf (ret, "file", "%s%s", xml_attrval (ret, "dir"), xml_attrval (ret, "location"));

   file = fopen (xml_attrval (ret, "file"), "r");
   if (!file) {
      xml_setf (ad->parms, "error", "Unable to open file %s for reading.", xml_attrval (ret, "location"));
      xml_free (ret);
      return NULL;
   }

   xml_setbin (ret, file, fclose);
   xml_set (ret, "content-type", "text/plain");

   return (ret);
}
XML * LIST_odbc_retrieve_read (WFTK_ADAPTOR * ad, va_list args) {
   void * buffer;
   int size, number;
   XML * handle;

   if (!args) {
      xml_set (ad->parms, "error", "No arguments given.");
      return NULL;
   }
   buffer = va_arg (args, void *);
   size = va_arg (args, int);
   number = va_arg (args, int);
   handle = va_arg (args, XML *);

   xml_setnum (handle, "last-read", fread (buffer, size, number, xml_getbin(handle)));
   return NULL;
}
XML * LIST_odbc_retrieve_close (WFTK_ADAPTOR * ad, va_list args) {
   XML * handle;

   if (!args) {
      xml_set (ad->parms, "error", "No arguments given.");
      return NULL;
   }
   handle = va_arg (args, XML *);

   fclose (xml_getbin (handle));
   return NULL;
}

This code and documentation are released under the terms of the GNU license. They are additionally copyright (c) 2002, Vivtek. All rights reserved except those explicitly granted under the terms of the GNU license. This presentation was prepared with LPML. Try literate programming. You'll like it.