Drupal-SQLite

Introduction

This page describes Drupal-SQLite, a patch to make Drupal 6.x work with a SQLite database.

SQLite is a lightweight, fast, public domain, easily integrable, multiplatform database system.
It's used by many of the greatest open source projects; just to name one... Mozilla Foundation on Firefox & Thunderbird.

Why should you need this?
Well, most hosting providers gives you PHP 5.2+ for free, but they ask a few bucks for a MySQL database. If this could worth the case for mid-large sites, with hundreds or thousands users a day, it's a waste for mid-small sites (like mine).

With SQLite support you can setup a preconfigured Drupal site which works just out of the box.
Suppose you're a theme designer or a module developer: you can create a self-working Drupal installation with only 1,5 Mb overload.

My work started from here.
In this long post, some good guys put their efforts to have a SQLite database layer for Drupal 4.7, then 5 and finally 6. The thread is now closed because some of them moved to the forthcoming Drupal 7 series, which will have SQLite as first class citizen; while waiting for it, Drupal-SQLite gives you a full working SQLite database layer for Drupal 6.

Alternatives

There are other Drupal+SQLite bundles out there, but none fulfill my needs.

Speedtech.it

(link currently down)
Working (but old) Drupal 5.6 version, patched for SQLite.

Pros:

• works great (this site used it till Oct 2008)

Cons:

• it's a Drupal 5 version, no PDO then its SQLite support is limited to SQLite 2.x databases (which are not performing as 3.x ones)
• no more updates, last version was against Drupal 5.6

Siren

Deep (maybe too deep) patch against Drupal 6.x

Pros:

• PDO support, then 3.x SQLite databases support
• more databases other than SQLite

Cons:

• too deep changes to Drupal core files
  Read from it's official site: "According to the needs of PDO and Oracle drivers implementation, ALL core queries and some APIs are hacked for compatibility concern."
• even if it's based on Drupal (ver. 6.4) now it's a complete fork; this means you can't use Drupal updates.

Drupal-SQLite

Drupal-SQLite comes in two flavours:
- a .zip archive ready to be unpacked in your web server root
- a .patch file to apply to a default Drupal 6 tree

After installing the one you prefer, just open index.php in your Drupal site and you're ready to start.

These are Drupal-SQLite pros:

• built against Drupal 6.10 (and following, read next point)
• no core files modified (except one: includes/install.inc.php) (*)
• uses PDO layer (even if original Drupal 6 doesn't), so SQLite 3.x databases can be created
• supports a growing list of modules

(*) Drupal-SQLite patches this file to let the user choose SQLite as its database. After the first install, this file is not used anymore and can be safely overwritten. Other Drupal-SQLite related files are additional ones; saying this I mean you could install latest Drupal updates being sure you'll not broke Drupal-SQLite.

Requirements

Drupal-SQLite requires PHP5 with PDO-SQLite support enabled.
This is NOT the standard SQLite support (non PDO) and is provided through a different extension named php_pdo_sqlite.dll(win) or php_pdo_sqlite.so(linux).

To test if PDO_SQLite support is enabled, create a file named phpinfo.phpcontaining a single line:

<?php phpinfo(); ?>

Now open this file in your browser and check if a section named pdo_sqlite exists and is enabled.

NOTE to XAMPP users:
XAMPP 1.7.1 comes with pdo_sqlite disabled by default.

To enable it:

• Stop all XAMPP services
• open xampp\php\php.ini file
• find the rows containing the following strings:
  • extension=php_pdo_sqlite.dll
  • extension=php_sqlite.dll
• remove the leading ; to enable PDO-SQLite
• restart the XAMPP services

How to install

Depending on your hosting solution, choose the file to download:

• Full archive
  This should be the best solution for Windows based hosting, but it surely works on Linux too.
  Download the archive and extract it in your web server root folder.
• Patch file
  Since applying patches on Windows is not so easy, I suggest this to Linux users only.
  Download patch file of the original version you're going to patch and apply it to Drupal tree you already extracted to your web server root folder.
  If the patch applied successfully then go on.

Now open install.php file and follow the instructions; don't forget to:

• choose the Drupal-SQLite profile at the very first page to have an optimized configuration
• select sqlite as database type inside the "basic options" block (I can't find a way to force this selection through the profile)

Security notes

You should protect your database file, which is by default sites/default/*.s3db, from being downloaded.
Drupal-SQLite comes with a preconfigured .htaccess files that denies access to *.s3db files.
Please edit it if you changed proposed database file name, then test if it works.

Core modules

Drupal-SQLite comes with a profile which, by default, disables some core modules to make it run faster.
Time-consuming modules, like dblog and syslog, are disabled by default.
Enabled modules are: 'color', 'comment', 'help', 'menu' and 'taxonomy'.

"sqlitetools" module

Since your database is a flat-file, making a backup is now easy as copying the file itself.
I created a specific module, named sqlitetool (you can find it in downloads) to accomplish this task; backups are stored into/retrieved from ZIP files.
The same module allowsto cleanup the active database (VACUUM) to free unused space.

Adding external modules

Drupal-SQLite is a non-invasive patch against Drupal and should work with each module accessing the database through the Drupal DB subsystem.
Sometimes module developers need to use DB specific SQL queries, and they put a switch statement in their code, like this (from xmlsitemap module):

switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      $query .= "LEFT JOIN {url_alias} ua ON ua.src = ('node/' || '%d')";
      break;
    case 'pgsql':
      $query .= "LEFT JOIN {url_alias} ua ON ua.src = CONCAT('node/', CAST(%d AS VARCHAR))";
      break;
}

As you can see, the query is composed according to the active db and, worst of all, there's no 'default:' item at the end of switch.
Now we have: $GLOBALS['db_type'] == 'sqlite', then no code is executed and no query (or part of it) is composed.

In this case the fix is quite simple:

switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
    case 'sqlite':
      $query .= "LEFT JOIN {url_alias} ua ON ua.src = ('node/' || '%d')";
      break;
    case 'pgsql':
      $query .= "LEFT JOIN {url_alias} ua ON ua.src = CONCAT('node/', CAST(%d AS VARCHAR))";
      break;
}

Note that SQLite shares the same code as MySQL.

So my suggestion is: before installing a new module, do a find (grep for Linux friends) inside module folders and look for mysqlor pgsql.
If you find it, examine the code and try to find a way to accomodate; if you can't, drop a line to module developers and ask them to fix it for you.
Ok ok, I know that SQLite is not officially supported, but in most of the cases I saw the fix is just one line, like this.

Known working modules

I tried these modules (most of them are used here) and I can confirm they work untouched.
Please let me know if you used a not-listed module (and it worked).

Known not-working modules

These modules do not work with Drupal-SQLite out-of-the-box and a patch is required.
This patch could concern few lines (relevance=1) or require the whole module to be patched (relevance=5).

Acknowledgements

• Tutorial on installing Drupal-SQLite on Altervista (ITA) (by Roberto Capuzzo, aka robocap)
  He wrote a great italian tutorial on installing Drupal-SQLite on Altervista WebHosting.
• Dmitri Schamschurko
  Great feedback and help debugging SQLite table schema functions in database.sqlite.inc.
  This lead to CCK module support in Drupal-SQLite-6.13-1.2