From MySQL to SQLiteAn 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
Bear in mind when reading this that...
motivation to use "industry standard" database technology is to make
data available not only to the original application (normally written
in Dyalog APL/W), but also to other applications which may be written
using other programming languages. Another consideration is that
this might ease migration from Windows/PC to other OS/hardware
- The motivation to move from one SQL-centred
database host to another varies between dissatisfaction (in the case of
moving from Access to MySQL) to curiosity (moving from MySQL to SQLite).
- The databases in question are relatively small and mostly used by a single user
author isn't particularly interested in dealing with the vagaries of
SQL for anything beyond simple data retrieval and updating, believing
that the algorithmics behind stuff like joins is better handled by an
algorithmic programming language like APL than adhoc rephrasings of
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...
"upgrade" policy was tiresome; especially when migrating to a new PC.
Quite why they can't arrange a "we'll collect your old CDs and
send you a complete new set" rather than requiring "install the old
version, then upgrade over it, then upgrade over it..." escapes me.
- I wanted to reduce my dependency on Microsoft anyway.
- Stuff seemed to be breaking (this was probably due to the upgrade nonsense).
MySQLMySQL 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.
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.
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...
- Create database folders, files and tables (the database viewer described below can write SQL Create Table commands to file, which SQLite can in turn read)
- Export from MySQL using the SQLyog
tool (I came to this late in my time with MySQL, if I'd known about it
earlier I might have felt less inclined to make my own viewer)
- Import into SQLite using the command-line; the only "fragile" issue being choosing an appropriate field separator)
(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...
- People who
install multiple versions of Dyalog APL can sometimes find that new
installs mangle SQAPL registry/INI settings (easily put right when
you're alert to the potential)
- SQAPL is at the mercy of what
particular ODBC drivers are capable of; in practice this only affects
more ambitious use - but there doesn't (yet?) seem to be good
documentation of which drivers won't do what.
Database Viewer UtilityThe Dogon Research Database Viewer is a framework for database GUI and manipulation, comprising five main sections...
for detailed table viewing and updating - these forms are created
manually (could be automated, but the need hasn't been acute) on a
database/table basis. This is where stuff like referential
integrity is handed off from the database software to custom code (if
only because you can't rely on SQL products actually including this
sort of feature - surprisingly). Creating form definitions and
retrieve/update code is simple (but repetitively tiresome).
- these are specific to the database. As with forms, writing the
code for a new report is mostly boilerplate tedium. Reports may
well combine data from several table, and here APL is a usefully terser
(and less arbitrary?) technology than bare SQL - so the logic is in the
APL, not in the SQL queries.
- Table views - an entirely generic piece of code which just shows what's in the table.
- Table properties - another entirely generic piece of code that displays field definitions
SQL - a place where SQL statements can be tried (and used for
particular patching-up jobs where database contents need a nudge.
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
- Write SQL statements used to create (all of
the) database tables to file. These files can then be read by
SQLite (and potentially other database products) to create new tables
ready for data loading.
- Export table data to CSV files; the usual options included to specify field separators and the like.
was a pretty smooth transfer, the only real surprise coming when trying
to delete ODBC data sources after removing MySQL and MyODBC - for
whatever reason known only to that sort of programmer, Windows insisted
that the sources couldn't be deleted if MyODBC wasn't there - temprary
reinstall of MyODBC gets it straightened out).
- SQLite seems to have a much smaller footprint the MySQL.
- Keeping the data within the SQL paradigm seems to be holding up - perhaps it'll move again one day...
Page created 24 November 2006; Copyright © Dogon Research 2006