SQLite Support in Joomla

2014-10-29 5 min read Joomla marco

It’s been a while now that I’ve been eyeing the SQLite implementation of Joomla. There have been lots of teasers, so far, but never could I ever click on the “Select Database Type” field and enter, sqlite. Finally, I got sick of waiting on a release and decided to go solo. I would find out who was working on SQLite for Joomla, track them down, offer them encouragement and help, if needed cajole or threaten, and finally get a SQLite implementation that “just works.”

Why Joomla and SQLite?

At first, I was hesitant to want that combination. After all, installing Postgres or MySQL is not really a big deal, especially considering that installation of Apache is still orders of magnitude worse. But then I found that I can start a Joomla instance just by invoking the php server process in the Joomla main directory – suddenly life on development servers was much easier.

If you don’t know what I mean, here the skinny: you can just unzip the Joomla download into any directory, change into it, and type: php -S localhost:XXXX. XXXX has to be a number above 1024, something like 8000. Then you can simply go to your browser, type in localhost:XXXX (again, same number as before) and you can start installing Joomla. Once you are done installing, you can simply go there again and test and test.

That means that if you had a way to store your database locally, with your Joomla files, you could simply copy the whole tree and have a different instance running. You would go to the copy, change the paths in the configuration file, type in php -S localhost:YYYY and off you go with your new instance.

Can’t run MySQL on your web host? Problem solved. Don’t want to install a database server just for the occasional glimpse at your files? No problem. Want to see how a change to your site is reflected on your underlying files? There you go!

Status

As of Joomla 3.3.6, SQLite support is abortive. There is a driver, sqlite.php, but that’s pretty much it. You cannot call it, you cannot install the database on it, and it isn’t even listed as an available driver on the installation page.

A quick search for “joomla sqlite” turned up almost nothing. There was support scheduled in the 2.5 series, but nothing seems to have come of it. The closest we get is a guy that created a MySQL to SQLite engine that presumably only works to take an existing MySQL site and convert it to SQLite. In particular, it doesn’t seem to support any change to the menus, articles, and other content.

Fortunately, you can clone the git repository (dang, it’s time to switch my own svn repo!). There we are at 3.4, but the status of sqlite is frozen in time. There has been no visible change in support since 2.5. On the other hand, there are a few tests that might do something. Admittedly, once I started working with the sqlite driver, it was clear it wasn’t working at all, so that I doubt the tests actually do anything useful.

The commits on the sqlite driver show a similar pattern. None of them reference sqlite in particular, only one of them talks about error messages coming from the file. Otherwise, we are looking at generic changes like a copyright change, or a general change to the driver API.

What to do?

Enabling the Sqlite Driver

First things first: we need to tell Joomla that we are interested in using the SQLite driver. To be able to do so, we should really invest the time in installing the php5 driver:

sudo apt-get install php5-sqlite sqlite

Once that is done, we’ll find that a single file in the installation folder is responsible for the list of available drivers. The file is installation/model/forms/database.xml. On line 8 (in my version, 2013-03-05) there is a line that starts with supported=”. This line is followed by a list of acceptable drivers. Add sqlite to it:

supported="mysql,mysqli,postgresql,sqlsrv,sqlazure,sqlite"

Now when you go into the installer (just run php -S like above and go to your browser as mentioned), on the second screen you will have the ability to select SQLite from the drop-down menu.

Adding the SQLite Installer SQL

Turns out each database driver comes with a set of SQL files to initialize the database. Unfortunately, those files are not separated for schema and content. Also unfortunately, SQLite does not support many features that other database systems do. The SQLite folks call it “syntactic sugar,” meaning there are ways around it. Also, their concern is mainly targeted towards the size of the library (after all, it is the main data access layer in Android and Firefox).

For us, it means we have to tediously create an set of installer files. I copied those from PostgreSQL for no good reason and started making modifications. It’s been a long process.

To get that going, you copy the directory installation/sql/postgresql to installation/sql/sqlite. The name is significant and has to match the driver name.

On the database front, I found that starting with postgres had advantages, since the languages spoken are not all too different:

COMMENT statements don’t work and need to be removed. Luckily, they can be safely removed.
SQLite has no compound INSERT statement (yet). Current versions of SQLite do not understand the syntax INSERT INTO table (id, name) VALUES (0, “zero”), (1, “one); Instead, you have to use separate statements. This gets incredibly tedious, since the files contain tons of such compound INSERT statements. (Notice that the latest release of SQLite has that feature.)
The ID selectors from PostgreSQL, nextval() etc. don’t work. Fortunately, SQLIte is pretty good with integer primary keys.
The ‘serial’ keyword doesn’t work. It needs to be replaced by INTEGER PRIMARY KEY. Then the PRIMARY KEY at the end of the table definition needs to be removed.
The SOUNDEX function at the bottom of the file needs to be removed. Thankfully, that doesn’t seem to be a problem.

Adding Database Updates

For that, I simply copied the PostgreSQL files from administrator/components/com_admin/sql/updates/postgresql to administrator/components/com_admin/sql/updates/sqlite. That was pretty simple and worked without a hitch.

That’s it – with these changes, you can get your installation running! But nothing really works at first…