From MySQL to SQLite

An earlier paper described the experience of migrating a number of relatively small databases from Microsoft Access to MySQL; this paper describes a similar migration (of essentially the same databases) from MySQL to SQLite.

Motivation

Bear in mind when reading this that...

Microsoft Access

When first encountered Microsoft Access was something of a revelation - the author had previous experience of APL2 and was rather overwhelmed to find a consumer-priced database package that not ony took care of data storage and retrieval, but also provided the tools for constructing user interfaces.  Using Access for all the general-purpose stuff (and APL to do the interesting applications) served quite well for a number of years.  But eventually the cracks started to appear...

MySQL

MySQL is popular, and available at the right price.  Available for all sorts of platforms, with an ODBC driver.

Migrating the database tables to MySQL was straightforward (described in "From MS Access to MySQL").  The only thing that really hurt was that MySQL was (still is?) missing any sort of user interface (although there are some third-party tools that are quite useful and useable) - building a general-purpose database viewer/manipulator was easy enough, if a bit tedious.

After three year's satisfactory usage there was a bit of "itchy feet", and a vague feeling that MySQL was overkill for the particular context - that something more "lightweight" would suffice.

SQLite

At first release 2 of OpenOffice.org looked as though it might hold the answer; it had a useable database built in.  But sadly the pigheadedness of Java programmers know few bounds and there isn't an ODBC driver (and no sign that one will ever become freely available).

Some casual research led me to SQLite, which seemed absurdly small and self-contained for one used to the bloat of Microsoft products.  Download is less than 200kB, ODBC driver equally compact - install routine a model of simplicity.

Driven (in the first instance) by a command-line interface, easy enough to set up a test database and check out the basics.

Getting ahead of myself briefly...

SQAPL

SQAPL (included in the Dyalog APL/W product) is the tool that makes ODBC happen for APL.  It's been through a number of iterations (currently at Version 5) and "just works".  Just replace the ODBC connection name used for your MySQL (or whatever) data source with your new SQLite name and that's the only change within the APL.  The only things to be wary of with SQAPL are...

Database Viewer Utility

The Dogon Research Database Viewer is a framework for database GUI and manipulation, comprising five main sections...


This viewer has been running for several years - mostly unchanging except when new forms and reports are needed for the various databases.  For this exercise a new Tools menu was added, which offers two options...

And Finally...


Page created 24 November 2006; Copyright Dogon Research 2006