I'm proud to announce the first release of Drupal-SQLite, my project that makes Drupal 6.x series work on a SQLite database. The website hosting the project (http://coolsoft.altervista.org/drupal-sqlite) is a Drupal-SQLite installation itself, thus you could test its speed and reliability by yourself.

Well, I know that Drupal 7 will have SQLite as an official DB, but I'm using Drupal 6.x and I'm not going to switch in the near future. Most of all, not all the modules I use are going to switch to 7, so... I'll stay with 6 for a long time.

My work started here http://drupal.org/node/67349.
In this long post, those 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.x.

Drupal-SQLite pros (versus other sqlite patches):
1) built against Drupal 6.10 (and newer, read next point)
2) no core files modified (except one: includes/install.inc.php, used during first setup only)
3) uses PDO layer (even if Drupal 6 doesn't), so SQLite 3.x databases can be created

Point 2) means that - once installed - you can update your Drupal-SQLite website with future (and official) Drupal releases, no worry to break anything.

Drupal-SQLite comes with a custom installation profile and a module - sqlitetools - that allows to backup/restore your database (after all is a flat files) in an easy way.

Both a patch file (against Drupal 6.xx) and a full archive are available here:
http://coolsoft.altervista.org/drupal-sqlite

Please let me know what you think about my work ;)

Comments

thePanz’s picture

Great work Claudio ;)

-thePanz-

nicorac’s picture

You're welcome.
Please post me your website URL, when it'll be online.

nicorac’s picture

A new version of Drupal-SQLite was released, including many of the fixes suggested by users.

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

* Drupal official version 6.11
* Increased length of database name field (which here is used as a path)
* Moved new 'sqlite' database type as first in install.inc, so it becomes the default selected during install
* The profile Drupal-SQLite now checks requirements during the install.
see: http://coolsoft.altervista.org/en/drupal-sqlite#comment-28
* Database file is now, by default, contained in a reserved folder.
SQLite needs RW access to both the database file and the containing folder.
see: http://coolsoft.altervista.org/en/drupal-sqlite#comment-40
* Install procedure will now create both the database and its folder, and test their permissions.
see: http://coolsoft.altervista.org/en/drupal-sqlite#comment-40
* Fixed usage of undeclared variables in includes/database.sqlite.inc
see: http://coolsoft.altervista.org/en/drupal-sqlite#comment-37

nicorac’s picture

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 previous release.

Missing folders were:
- /modules/color/images
- /sites
- /themes/garland/images

I'm sorry for that <:)

nicorac’s picture

Drupal-SQLite 6.12-1.1 is out.

It is an update to Drupal 6.12 official version.

Both a patch file (against Drupal 6.12) and a full archive are available here:
http://coolsoft.altervista.org/drupal-sqlite

aryanto’s picture

Hello Claudio

A part from subscribing to this post, I would like to say thank you again.

My site has been running using SQLite3 for almost 2 months now. I am still using your patch for Drupal 6.10 though, which I carried over to 6.12 with some little modifications. So I am not sure if that would be quite different from your patch for 6.12 as I have not checked that. I think I will just use your patch at some point. That is why I am subscribing to this post and your newsletter :)

And I needed to tweak my site a bit to avoid slow access, by using Cache Router module and Varnish HTTP accelerator. If you are interested to have a look, just click on my profile as I added my site there.

Kind regards,

Anto

nicorac’s picture

Thanks for using it.

Please note that upgrading to latest Drupal-SQLite version is easy as unpacking the newer version over the existing one, overwriting all the files. Your additional modules and database will be untouched.

You can also use an original Drupal release because Drupal-SQlite patches only additional files; the only original file patched is /install.php, but it's used only during first installation then you can re-overwrite it safely (or even trash it).

Before upgrading, I suggest you to make a copy of your DB: it's a single file in (by default) /sites/default/db/*.s3db.
My sqlitetools module (http://coolsoft.altervista.org/drupal-sqlite#sqlitetools) is an easy and safe way to backup and compress the DB, even on sites with no FTP access; you could then download the compressed backup through your browser.

Regards
Claudio

nicorac’s picture

Drupal-SQLite 6.13-1.1 is out.

Updated to match the new Drupal 6.13 official version.

Both a patch file (against Drupal 6.13) and a full archive (.zip and .tar.gz) are available here:
http://coolsoft.altervista.org/drupal-sqlite

westbywest’s picture

Subscribing. This is actually just what I need.

I read of plans to incorporate SQLite support in Drupal 7. Any plans to backport this to D6?

aryanto’s picture

It says somewhere that "the drop is always moving". That means, there is no intention of backporting any features. I disagreed with that, but I have no choice than to accept it.

I am not sure why you are still looking for the backport of the Drupal 7 SQLite interface, while the patch mentioned on this thread is perfectly working on Drupal 6. Or at least I have never experienced any issue using this patch so far.

nicorac’s picture

Honestly speaking, the greatest drawback I see on using my D6 version with SQLite is that I can't assure that each module will work correctly.
This is baceuse module developers are not required to test their work against SQLite...

The most of them will work without changes, but sometimes you'll find sources like this:

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;
}

Here you can only patch it adding the 'sqlite' case and, better, post a feedback to module team.
The worst side is that this code will not generate an error, simply it won't be executed and the composed query is incomplete or malformed.
Best programmming will be to include a default: case and throw an error, but this is another thread...

Backporting D7 SQLite support to D6 will require all of the modules to be tested on SQLite too which, I think, is a lot of work.

drupalista’s picture

This is a wonderful peace of work - and a great example that shows, how week and chaotic tzhe drupal code is. In 2009 there is absolutely no way to release software without a database abstracion layer - but drupal not only has nothing like this, no, it gets worse - in many modules sql queries are hardcoded and authors use switch statements for different databses - this is really like living in a cage! Total primitive code constructs and this drupal sqlite shows all the weeknesses of these primitive and prehistoric code concept.
Only solution will be to built drupal completely new on top of a modern framwork like zend, doophp, cake or yii.
If you still believe in using drupal - you will loose your customers in two years. You will totally be behind technical relevance and everybody will look at you like an apeman coming from the jungle. Drupal is dead, but many still dont know. The elite is elsewhere.

WorldFallz’s picture

You might want to research a little before trolling your fud:

aryanto’s picture

@WorldFallz

We are talking about a usable Drupal with SQLite here. Your links are for Drupal 7 which can not practically be used up to now. Don't think that I am trying to force anybody to make Drupal 7 usable, by saying that. And you better do some research about listening to anybody else's opinions, before judging the opinions as troll.

flexgrip’s picture

I just wanted to mention that another plus to this that you forgot to blast as a key "pro" is that this will now allow people to use version control while developing drupal sites since the DB is stored in a file. The plus being no more exporting and importing SQL files or just developing on the public server.

This is by far the best and only reason i am using it.

nicorac’s picture

I use SVN and this "plus" comes for free... but I've never thought about it!
I'll add to features ASAP, thanks ;)

nicorac’s picture

Drupal-SQLite-6.14-1.4 was released.

As we all know, the greatest problem of Drupal-SQLite is to work with the thousands modules available.
I can't try them all (unless someone will pay me ;) ) and adding patches to them is a short-term fix because this patch should be reapplied at each subsequent update.

To partially solve this issue, version 6.14-1.4 has a new interesting feature: the ability to rewrite queries just before they are executed.
The rewrite rules are defined in two separate files, unrelated with modules.
The first is maintained by me (CoolSoft) and will contain rules against core queries and largely used modules (like devel and cck).
The other file is optional, will be maintained by the user and will contain rules for his/her own modules.
This should decrease the need to patch modules (and keep them patched after updates).

  • 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
  • Please let me know what you think about...

    ChaosD’s picture

    hi,
    iam hosting on dreamhost and they are known for slow mysql connections (host/database are seperate servers) so your project gave me a huge performance boost.
    iam planning to move some of my standard drupal projects to sqlite drupal but iam still struggling with exporting the mysql database to sqlite. any hints for this?

    //edit
    conversation tools didnt work for me.

    nicorac’s picture

    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 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.
    nicorac’s picture

    nicorac’s picture

    Aligned code to Drupal 6.19:
    - Includes SECURITY FIXES from Drupal 6.18 (see http://drupal.org/node/880476)
    - Includes bug fixes from Drupal 6.19 (see http://drupal.org/drupal-6.19)

    You can find the new version here:
    http://coolsoft.altervista.org/drupal-sqlite

    Drupal-SQLite is now on SourceForge:
    Project page: http://sourceforge.net/projects/drupal-sqlite/
    Source code: http://drupal-sqlite.svn.sourceforge.net/viewvc/drupal-sqlite/

    nicorac’s picture

    Drupal-SQLite-6.20-1.5 is out
    - Includes bug fixes from Drupal 6.20 (see http://drupal.org/drupal-6.20)

    You can find the new version here:
    http://coolsoft.altervista.org/drupal-sqlite

    Drupal-SQLite is now on SourceForge:
    Project page: http://sourceforge.net/projects/drupal-sqlite/
    Source code: http://drupal-sqlite.svn.sourceforge.net/viewvc/drupal-sqlite/