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).

Module name Version Note
admin_menu 6.x-1.3  
blockquote 6.x-1.0  
captcha 6.x-1.0  
cck 6.4-2.4  
comment_notify 6.x-1.2  
devel 6.x-1.17 (see this comment)
dhtml_menu 6.x-3.4  
fckeditor 6.x-2.0-alpha5  
geshifilter 6.x-1.2  
languageicons 6.x-1.1  
pathauto 6.x-2.x-dev  
permalink 6.x-1.1  
poormanscron 6.x-1.0  
potx (Translation Template Extractor) 6.x-3.0  
service_links 6.x-1.0  
taxonomy_dhtml 6.x-1.0-rc3  
token 6.x-1.11  
wysiwyg 6.x-2.0 + FCKeditor 2.6.4.1

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).

Module name Version Relevance
date 6.x-2.3 5

Acknowledgements

Version history 

Drupal-SQLite-6.25-1.5, 2012-02-29

Drupal-SQLite-6.24-1.5, 2012-02-03

Drupal-SQLite-6.22-1.5, 2011-06-02

Drupal-SQLite-6.20-1.5, 2010-12-16

  • Drupal official version 6.20

Drupal-SQLite-6.19-1.5, 2010-08-12

Drupal-SQLite-6.17-1.5, 2010-06-03

  • Drupal official version 6.17

Drupal-SQLite-6.16-1.5, 2010-03-05

  • Drupal official version 6.16
    This release fixes security vulnerabilities.
    Sites are urged to upgrade immediately after reading the official security announcement:
    http://drupal.org/drupal-6.16

Drupal-SQLite-6.15-1.5, 2010-01-07

  • Drupal official version 6.15
  • Optimized query rewriting by adding shortcut returns after a successful rewrite.
  • New core rewrite rule for cache and update modules ("TRUNCATE TABLE" SQL commands).
  • Fixed an error during setup that causes install.php not use the values of "Site Name" and "Administrator username" fields. The default values were used instead.

Drupal-SQLite-6.14-1.4, 2009-10-04

  • Drupal official version 6.14
  • New query rewriting system: it allows Drupal-SQLite to rewrite SQL queries just before their execution, without the needing to patch (core) modules.
    Rewrite rules are contained into two new files:
    • database.sqlite.core-patches.inc
      this file is mantained by CoolSoft and will include rewrite rules for core modules and for widely used ones ;) (like devel)
    • database.sqlite.user-patches.inc (optional)
      here you could add rewrite rules for all other modules
  • Drupal DB functions "db_add_unique_key" and "db_remove_unique_key" are now supported.
  • Workaround for SQLite not returning short column names for queries with JOIN or GROUP BY clauses.
  • Added support for SQL function STDDEV (thanks again Dmitri)

Drupal-SQLite-6.13-1.3, 2009-08-06

  • Fixed a bug in _db_query function which caused multiple/nested queries to fail.

Drupal-SQLite-6.13-1.2, 2009-07-15

  • Bug fixing and code cleanup in SQLite table schema management functions: db_column_exists, db_create_table_sql, _db_create_index_sql,  _db_alterTable, _db_introspectSchema.
    Thanks to Dmitri Schamschurko for his great help and feedback.
  • CCK module (cck-6.4-2.4) now works with Drupal-SQLite.

Drupal-SQLite-6.13-1.1, 2009-07-02

  • Drupal official version 6.13, see here for detailed changes

Drupal-SQLite-6.12-1.1, 2009-05-14

  • Drupal official version 6.12

Drupal-SQLite-6.11-1.1.1, 2009-05-09

This is only a repackage of the previous archive version, due to missing folders in drupal-sqlite-6.11-1.1.zip file.
Missing folders were:

  • /modules/color/images
  • /sites
  • /themes/garland/images

I'm sorry for that <:)

Drupal-SQLite-6.11-1.1, 2009-05-05

Drupal-SQLite-6.10-1.0, 2009-03-21

  • Drupal official version 6.10
  • First public release

Download

Drupal-SQLite is also on SourceForge here:
Get Drupal-SQLite at SourceForge.net. Fast, secure and Free Open Source software downloads http://sourceforge.net/projects/drupal-sqlite/

You can also download the latest development version (trunk) here (thanks to SourceForge):
svn checkout svn://svn.code.sf.net/p/drupal-sqlite/code/trunk drupal-sqlite-code

drupal-sqlite-6.25-1.5.tar.gz
Description Fully patched version (compressed TAR archive)
Release date 2012-Feb-29 Size 1,082,929 bytes
MD5 6bec33f490823b60e928e6a7900a5b16
SHA1 db7c79203c0ab277356863cbd551646ecefaf7fb
SHA256 63f4c6710cc3fd87458862a879520935c4087365cb181838943f876f28593caa
drupal-sqlite-6.25-1.5.zip
Description Fully patched version (compressed ZIP archive)
Release date 2012-Feb-29 Size 1,280,368 bytes
MD5 ab988f79fcc70682c111fedcd06c598d
SHA1 080b96b249ae57924feff73741b816c42a1bc07d
SHA256 1df6ccd3bb4fc69cc638ecdd1dffaf090dead3d48e0bb30359051db01d9939df
drupal-sqlite-6.25-1.5.diff
Description Patch file for Drupal sources
Release date 2012-Feb-29 Size 72,496 bytes
MD5 6fff775f01125f3a206a32b99e4b9447
SHA1 944fe43f9f9e0376d2c5714525bc9f41b8ec62da
SHA256 7d61d8c6aaa832b5fa19013216c62c0b0d45b59deda14b3cf174e577a3e545e1
sqlitetools-6.x-1.1.tar.gz
Description sqlitetools module (compressed TAR archive)
Release date 2009-May-05 Size 54,803 bytes
MD5 5f6ef9d960a8e3d8a7e39ea8034c64cb
SHA1 1f5244d040a8cad02d95cde7389b918ab6887640
SHA256 67fddb3e965fb62a887e7da697ae6f6e79d1d3668f8b4fb99eb48afb62d8e804
sqlitetools-6.x-1.1.zip
Description sqlitetools module (compressed ZIP archive)
Release date 2009-May-05 Size 56,937 bytes
MD5 92b61dad1c29e4700526435c90bcb04f
SHA1 7eb1867f2f8cc4692e20e6ef558f087fd2cfe459
SHA256 9dffc2fe97d18d63614dd6659866de471b1f9528a94d1c514ddd957373aa77c6
Installazione_Drupal-SQLite_su_Altervista.pdf
Description Istruzioni per linstallazione su www.altervista.org
Release date 2009-Jul-16 Size 173,765 bytes
MD5 fcad99e64318d1826917fe1f2c3ad779
SHA1 b3a0c82ca37399ee09d6d508963564beedfb1da4
SHA256 5d6d36cdd580a10957c17fa66108635f372dc4c77aeaa444a7426999f63279cd

Comments

Pages

Dmitri Schamschurko,

THANKS!!!!
Sorry that I took so long, but I just saw the new version and then tried to apply your patch with calendar and views and it worked smoothly!

Obs.: Just a note on your patch that I saw is that on function sql_offset there is a small error (trailling space).

case('sqlite'):
  $offset = ($offset >= 0) ? "+ $offset" : ("- " . -$offset);

by:

case('sqlite'):
  $offset = ($offset >= 0) ? "+ $offset" : ("-" . -$offset); 

Hi,
did anybody found a solution for this problem?
The patch (see below in this thread) doesn't fix the problem for me.
Regards,
Oliver

Certain changes made in the initital "Configure site" page after the Drupal-SQLite installation are not being saved, and I have to go back to the admin pages to make those changes again. The fields that apparently aren't saving include:

  • Site name
  • Administrator account: user name
  • Administrator account: email address

I had to log back in using the user name "Administrator" even though I changed that field during installation. The db is writeable, as I'm able to use the site after installation. I'm using the Entropy package of PHP, which uses PHP 5.2.9 and SQLite database 3.3.7 according to Drupal's status report.

Thanks for the detailed feedback, I'll give a look ASAP.

I can confirm this bug. Very frustrating till you figure out to try 'Administrator' name. Password recovery also doesn't work because of e-mail not set.

Ciao, volevo farti i miei più vivi complimenti, circa un'anno fa decisi di crearmi un sito con lo stesso hosting che hai usato per questo sito e provai come cms joomla ma purtroppo c'era il problema delle query allora decisi da abbandonare il tutto; poi circa un mese fa ho scoperto per caso Drupal ed ancora più per caso ho scoperto questa versione di Drupal patchata da te ed ora anche grazie a te sto creando il mio sito.

Una cosa è certa, mi sono innamorato di Drupal, inizialmente è stato ostico ma più passa il tempo e meno segreti ha per me.
Se hai tempo spero vorrai contattarmi in privato in quanto avrei delle domande da farti.

Comunque sia ti faccio di nuovo tanti complimenti e ti ringrazio per questo stupendo lavoro.

Grazie per i complimenti.

Le domande sono benvenute se riguardano nello specifico Drupal-SQLite, ossia la versione specifica per SQLite del CMS.
In questo caso postale pure qui in modo che siano utili a tutti, oppure (a brevissimo) sulla pagina del nuovo progetto su Sourceforge.

Se invece i tuoi dubbi riguardano Drupal, credo che un buon posto per ottenere aiuto sia, oltre al sito ufficiale drupal.org, anche l'ottimo forum di Drupal Italia.

Ciao, complimenti.
mi serviva proprio qualcosa così. Ora sto ancora modificando il sito, l'ho installato da venerdì.
Ma spero di riuscire a definire qualcosa di carino.

sito: supernovasystem.altervista.org/

Io sto facendo un DBMS per SQLite (2 e poi 3)(lo trovi sul sito, se vuoi provarlo e commentarlo anche tu! magari ti potrebbe tornare utile se devi modificare qualche database sqlite.) ma quando cerco di allegare la verione beta zippata, mi da questo errore, su un javascript alert :

Si è verificato un errore HTTP 0.
/?q=upload/js

se mi potessi aiutare te ne sarei grato.
Ciao

Ciao e grazie per i complimenti.

L'errore che segnali non è strettamente legato a Drupal-SQLite e si verifica anche su Drupal classico.
Prova a guardare qui: http://www.drupalitalia.org/node/6994

Comunque io ho avuto lo stesso problema su Altervista; dopo aver provato a modificare un'infinità di parametri ci ho rinunciato ed uso direttamente FTP con FileZilla.

Hello,

First of all, thanks for this project.  This is really a great job!

Recently I upgraded Views to v2.10 and hit a problem : I was not able to same my views anymore.  Any attempt to save the view would somehow "reset" the view to the default values.

After some hours bumping my head on the wall (it was just working fine with v2.8), I discovered an inconsistency in the way Views is saving and loading some options.  By chance, this bug does not appear for MySQL but is causing problem with SQLite (see bug report for details).

Conclusion is : if you are using Views with SQLite, do not upgrade until the bug is fixed, do not used v2.10 (but v2.8) or be ready to patch the code (see bug report).

Again, thanks for the great job with Durpal SQLite!

Thank you for reporting the bug both here and on Drupal project issues.
Luckily the bug is not strictly MySQL related (the fact MySQL is not affected is not important), so I hope they'll release quickly an update for it.

Please keep us updated...

I am using Drupal-sqlite with Views 2.16 and also have same problem, after.. two years? Bug is marked as wontfix...

Any suggestions?

Views developers decided to not investigate further on a bug that appears only on DBs other than MySQL.

I don't share the same opinion: the fact it works on MySQL could hide worst bugs.
Anyway I have no role on it and sadly I can only suggest you to migrate to D7 asap and use SQLite "natively".

You could try to install D7 with SQLite, than change the created SQLite db with a copy of your current one and (try to) update it.
I can't confirm it will work, because D6->D7 upgrade scripts were not tested on SQLite; anyway it worths a try.

Thank you for your concern. Main site uses D6 + MySQL. Drupal-Sqlite looked as good setup for no-extra-charge-for-additonal-mysql installation in shared hosting for experimenting prior to modifying production website.

It's sad that this awesome project becomes limited due to rather.. unacceptable Views developers decision...

Well, honestly their decision is comprehensible because SQLite was never officially supported on D6.

The worst thing is that this bug, shown by Views, could hide a worst one...

I'm getting a warning on the ../index.php?q=admin/reports/status/sql page. Is it a problem with my installation, or is it a bug?

warning: PDO::prepare(): SQLSTATE[HY000]: General error: 1 near "SHOW":
syntax error in /includes/database.sqlite.inc on line 218.
user warning: near "SHOW": syntax error
query: SHOW STATUS in /modules/system/system.admin.inc on line 1765.

No, this is not a problem on your side, it's just SQLite not supporting the "SHOW STATUS" command.

I solved this by preventing the access to that page, which is not giving any useful info for SQLite: the DB version reported by the system state page is not an hyperlink anymore. Since this is not a critical bug, you'll find the fix in the next Drupal-SQLite version.

If you want to fix it right now, you can download the latest trunk version from SourceForge:
http://drupal-sqlite.svn.sourceforge.net/viewvc/drupal-sqlite/

Hi guys,

I'm problems with the Views module, I can't create the new view because when save the settings the system respond: "The view recent_news has been saved."; but the settings are the default.

Any comments?

Check out the older version. Views 2.8 works nice.

Credo di aver fatto tutto bene. Del resto ad oggi avrò installato 1000 drupal (mysql).
Ho seguito alla lettera.
ho creato il file di testo vuoto sites/default/db/db.s3db
Scrivibili (777) sia la cartella db che il file db.s3db

Arrivato a -Database configuration-.
Inserisco username e password, clicco su -save and continue-
ma la pagina si ricarica come se avessi premuto il tasto reload del browser.
Ora, se non è il sistema a creare da solo le credenziali, sicuramente il problema è questo:
username e password oltre ad inserirli nella schermata -Database configuration-
dovrebbero essere presenti nel file di testo db.s3db
Ma qual è la sintasssi? non la conosco.
Con la versione mysql username e password le metto quando creo il database,
invece qui? come faccio?

help

grazie da ora
fab

Ciao, il file sites/default/db/db.s3db (che è il database vero e proprio) non devi crearlo manualmente, ma lasciarlo creare dal setup. L'unico file che devi creare (o meglio copiare) è sites/default/settings.php, come in tutte le installazioni standard di Drupal.

I campi Username e Password possono contenere qualsiasi valore, dato che non sono usati (SQLite non ha utenti per l'accesso ma sfrutta i diritti sul file); l'importante però è che siano valorizati altrimenti il setup non prosegue.

Tienici aggiornati ;)

Ciao,
è tutto il giorno che provo ma non ci riesco, probabilmente mi sfugge qualcosa di "ovvio". 
Ho provato sia su altervista che in locale e non riesco a superare la pagina "database configuration", ho scaricato anche una versione un po'più vecchia per timore che l'ultima avesse un bug.

Per altervista ho seguito la lista trovata in pdf su questo sito, per il locale ho seguito la tua.

Se non ho capito male in quella pagina non bisogna cambiare niente (basta semplicemente confermare dopo essersi accertati che sia selezionato sqlite come tipo di database). Ma anche facendo così non riesco (ho provato anche a modificare qualcosa ma non cambia niente). Cosa può essere?

Il file db viene regolarmente creato ma rimane a 0, ed io mi trovo sempre davanti la stessa pagina.

Grazie in anticpo per la risposta e per il tuo lavoro.

Ciao

Tiberio

Le "dimenticanze" più frequenti sono:

  • mancata attivazione del supporto ai file .htaccess
  • mancata attivazione del supporto a PHP5
  • problemi di permessi sulla cartella che contiene il DB (tutta la cartella, non solo il file del DB)

Verifica queste cose e, nel caso, verifica nell'output di phpinfo() (vedi la sezione "Requisiti") che siano abilitati sia PHP5 che pdo_sqlite. Infine mandami una mail... ;)

Ciao,
su altervista avevo letto php5 in cima al filemanager ed avevo pensato volesse dire che php5 era attivo.
Invece quello era il tasto che permetteva, tra le altre cose, di attivare php5. Me ne sono accorto interrogando phpinfo.
Ma perché non c'ho pensato prima?

Grazie del tuo suggerimento!

ho il tuo stesso problema....vedo che il post è vecchio chissà se sei gia riuscito a risolverlo

sono anche io nelle stesse condizioni;
l'installazione rimane ferma alla pagina di configurazione del database.
Non esiste una soluzione?

Grazie

Confermo che il file sites/default/db/db.s3db NON deve essere creato ma viene creato dalla procedura di setup.

L'unico file da creare (o meglio copiare da quello di default) prima di iniziare è sites/default/settings.php.
Inoltre bisogna verificare che la cartella sites/default/db/ sia scrivibile.

Su Altervista verificare inoltre di avere sia il PHP5 attivo che il supporto SQLite tramite la procedura phpinfo() spiegata nell'articolo.

Ciao, per caso è compatibile questa versione modificata di Drupal con il modulo Views?

Purtroppo il modulo Views è uno di quelli problematici, dato che in diverse occasioni fa ricorso a query "customizzate" per il database corrente.

Se guardi nella sezione inglese dei commenti troverai diversi utenti che ci hanno provato, con vari risultati.

Views è uno dei moduli più importanti di Drupal. Un vero peccato che non funzioni a dovere.

Ma la versione v2.8 funziona? Mi interesserebbe per creare una slideshow.

Pages

warning

Warning, JavaScript is disabled!

JavaScript is not available, maybe because you disabled it globally into your browser settings or you are using an addon like NoScript.

We do not have any dangerous JavaScript running here.
Please enable JavaScript; if you're using NoScript this image will help you adding CoolSoft to your whitelist.

Thanks for your comprehension and enjoy CoolSoft.