SQLite support for Tinderbox

[Joe Marcus Clarke]

On 7/26/11 11:59 AM, Mark Johnston wrote:
> Hello all,
> 
> I've been working on adding support for SQLite 3 as a backend db to Tinderbox.
> At this point it looks like everything is working, both w.r.t. the tc script
> and the webui, so I thought I'd post my patches here and encourage people
> to give it a try.
> 
> ==
> 
> The one nagging issue is in the db access code for the webui. Tinderbox uses
> the PEAR MDB2 drivers to provide a unified interface to MySQL and Postgres;
> unfortunately, I found that no official MDB2 driver for SQLite 3 exists.
> The driver in the databases/pear-MDB2_Driver_sqlite port only provides SQLite
> 2 support, although this is not made particularly obvious in the docs. =(
> 
> However, someone has written an unofficial MDB2 driver for SQLite 3 that
> just uses PDO, and that's what I've been using for my testing. It seems
> to work well enough (modulo one important patch), but I'm wary of adding
> a FreeBSD port for it - the developer doesn't seem to be actively working on it
> and doesn't seem interested in making it an official MDB2 driver. I'm still
> waiting for the developer to get back to me, but I'm wondering if it might
> just be easier to rewrite the Tinderbox db access layer with PDO, dropping MDB2
> entirely. Does anyone have any objections to this?
> 
> ==
> 
> I've created an archive containing all of the necessary patches;
> it's currently posted here:
> http://www.student.cs.uwaterloo.ca/~m6johnst/patch/tb_sqlite.tbz
> 
> The tarball contains the following files:
> - pwd_fix.patch: A generic fix that's needed for dbs that are described
>   by a relative path in ds.ph.
> - MDB2_Driver_pdoSqlite.tar.bz2: The MDB2 driver for SQLite 3.
> - pdo_sqlite.patch: A required patch for the MDB2 driver. I sent it to
>   the developer but haven't heard anything from him.
> 
> The rest are patches to various parts of Tinderbox:
> 1. The "correct" way to escape single quotes in SQL is to use a second quote,
>    i.e. ''. SQLite doesn't recognize backslash-escaped quotes.
> 2. The SQLite schema. It's mostly identical to the Postgres schema.
> 3. Make the needed changes to get ./tc Setup to allow users to initialize
>    a SQLite database.
> 4. NOW() doesn't work with SQLite; this patch addresses that.
> 5. Webui DS changes. SQLite returns column names prefixed with the table name,
>    so _doQueryHashRef() doesn't work properly. Thus we give aliases to all the
>    returned columns.
> 
> All of the patches apply to a recent CVS checkout of Tinderbox. To test, extract the
> MDB2 driver to /usr/local/share/pear/MDB2/Driver/, apply the patch, make sure
> the files are world-readable and apply and rest of my patches.
> 
> Comments and review are most welcome. =)
> 
> If these patches are committed, I'm willing to continue maintaining SQLite support
> for Tinderbox, and I'll also submit a patch for the README with any SQLite-specific
> info.

This is good stuff.  I am certainly willing to commit this, but the
webui thing needs to be resolved first (I don't want to commit until it
is possible for run with SQLite and a current FreeBSD ports tree).  I
have no objection to a PDO rewrite if Beat is on board.  I assume you're
going to do the work?  I would rather have a consistent DB access scheme.

Thanks for your work, Mark.

Joe
-- 
PGP Key : http://www.marcuscom.com/pgp.asc