SQLite support in Glom

Over the last few months I implemented SQLite support in Glom for Openismus. When creating a new document, Glom now allows to save the data in a SQLite database instead of a postgresql database. This is more light-weight, and there are generally fewer things that can go wrong as everything is performed within the same process. However, there are some obvious limitations: As SQLite does not support authentication, Glom documents using an SQLite database can be opened without access restriction either. Also, the Glom session is not announced on the network since the SQLite database can not be accessed from remote anyway.

When implementing this, I tried to get rid of most of the postgresql-specific code which was in quite a few places throughout Glom, and putting it into a single place, and using libgda instead of hand-written SQL when possible. This should make it relatively easy to add more database backends in the future, if people need any. There is only some code dealing with users and privileges that may be postgres-specific and which I didn't touch, simply because it is not used with SQLite anyway.

One of the interesting parts was to map the different glom data types to SQLite data types. The switch to libgda-4 helped with this, since libgda-4 allows storing dates and timestamps in SQLite databases, by converting them to standardized strings. However, SQLite does not support a "numeric" type, but only integer and real. Currently, Glom maps its numeric type always to an SQLite real, which worked surprisingly well so far, though I'm still not 100% happy with it, mostly due to possible floating point inaccuracies.

Another problem was changing a table, meaning adding, modifying or removing columns, or changing the primary key column. As SQLite only supports adding non-primary key columns, but not changing existing columns or removing columns, Glom recreates the whole table in that case and moves the data from the old table into the new one. The tricky part of this was to get all possible type conversions right. For example, when changing the type from Image to something else, then the conversion failed with a "Database table is locked" error only if at least one row contained an actual (non-NULL) image. It took me some time to find out that SQLite does not allow dropping a table when a recordset from a SELECT query is still in use, and that Glom was indeed still holding such a recordset to display records from the current table. The problem went away when I released that recordset before changing the field type, though I wonder why there was no problem when there were no images in that column, or for other field type changes not involving images.

This work also yielded some bugs in the upcoming libgda-4, but Vivien has been very responsive so that they have been fixed very quickly. Kudos!

The SQLite version feels much faster and more responsive than the postgresql one when creating a new database or populating the list view. I wonder whether this can be explained with the overhead that comes with postgresql (talking to another process via TCP/IP) or there is a bottleneck elsewhere, which, if fixed, would make (postgresql) glom more enjoyable to use.

The current tarball version, 1.9.1, already supports SQLite, but still has some problems with changing field types which have been fixed in SVN. A 1.9.2 will hopefully be released soon. The next stable version of Glom, 1.10, will also contain SQLite support.

Sorry, no screenshots this time, since there wouldn't be anything to see that would be different from a postgresql-based Glom, except maybe an option to create a SQLite database when creating a new Glom document.