Drupal-SQLite

Please let our ADS show!

This sites offers only FREE software and it's supported by a few advertisement boxes (no intrusive popups).
Please:

  • disable your AdBlocker by adding CoolSoft website to whitelist
  • give the proper cookie consent
  • enable JavaScript for this website

This seconds wait is to let you update your browser configuration...

Ok, I've done the required changes... now show me your content!
!!! Please enable JavaScript !!!

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
Open virus check report
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
Open virus check report
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
Open virus check report
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
Open virus check report
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
Open virus check report
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
Open virus check report

Comments

Pages

EDIT: the discussion continued privately. I reported here only relevant parts.

Hi pm530,
I need further info about your issue...

- does the user appear in users table inside database file?
  You can use a SQLite database manager to open it, like this plugin for Firefox: sqlite-manager (http://code.google.com/p/sqlite-manager)
- can you login with created users? If yes, it could be a theme issue: what's yours?
- which modules (core and additional) are enabled?

As a last chance, create a new and clean site, with no additional modules enabled.
Add a new user (after the admin) and retry.
If the bug persists, make a compressed archive of this site DB and send it to me; I'll give it a try ASAP.

Waiting for your feedback.

Hi Claudio
1. You are cool.
2. The records are written into the db file.
3. Newly created users can login.
4. I can see only one user (Anonymous) in admin/user/user.
5. This happens in a new and clean installation.

The linux box is:
lighttpd/1.4.20
SQLite Library 3.6.2
php 5.29

Hi pm530,
sorry for the late reply but today and tomorrow is holiday here in Italy, so I read email less frequently.

Your DB loaded perfectly in my setup and all the users I create appear in the users list.
There's a thing I first noticed in the screenshot you sent me: the Administrator user was created 39 years ago!!!
This could be due to a problem with date on your Lighttpd/PHP/SQLite environment.

Is this the first PHP application you installed or are there any other working correctly?
Sorry for not having better ideas...

Sorry for the late reply.
Thank you again for you kindness. The user listed in the screenshot is actually anonymous, not administrator.
I think I've nailed down the problem: after upgrading the sqlite library from 3.6.2 to 3.6.14, the problem is gone :) .

Glad you sorted it out and thanks for sharing your knowledge with other users ;)

Let me know your new website URL when done.

Hello Claudio,
Thanks a lot for your efforts developing the Drupal sqlite.

I have been trying to install your Drupal-sqlite, but as I get the second installation page in which I have to: "select sqlite as database type inside the "basic options" block (I can't find a way to force this selection through the profile)" this "sqlite" option doesn't appear.

I would appreciate if you may help me to continue.
Thanks Carlos

It seems you are missing PDO-SQlite support in your PHP configuration.

Maybe my documentation should be clearer about the requirements:
Drupal-SQLite works with PDO database layer, then you need to enable PDO-SQLite support in your PHP.

Try to create a phpinfo page, then look for a section named pdo_sqlite.

Post me your results through the Contact form and I'll be glad to help you.

SOLVED: The problem was XAMPP, which comes with PDO-SQLite disabled by default.
I added system requirements to this page, helping XAMPP users to enable PDO-SQLite support.

I'm tryng to setup SEND module with SEND FRIEND module and MIME.

In the page http://...../node/x/send nothing work and this is the error:

  • warning: PDO::prepare() [pdo.prepare]: SQLSTATE[HY000]: General error: 1 near ".": syntax error in /Applications/MAMP/htdocs/druzip/includes/database.sqlite.inc on line 218.
  • user warning: near ".": syntax error
    query: SELECT COUNT(*) FROM (SELECT send.sid AS sid FROM send send WHERE .nid = 4 ) count_alias in /Applications/MAMP/htdocs/druzip/sites/all/modules/views/includes/view.inc on line 739.
  • warning: PDO::prepare() [pdo.prepare]: SQLSTATE[HY000]: General error: 1 near ".": syntax error in /Applications/MAMP/htdocs/druzip/includes/database.sqlite.inc on line 218.
  • user warning: near ".": syntax error
    query: SELECT send.sid AS sid, send.timestamp AS send_timestamp, name, uid, send.subject AS send_subject, send.message AS send_message FROM send send WHERE .nid = 4 LIMIT 20 OFFSET 0 in /Applications/MAMP/htdocs/druzip/sites/all/modules/views/includes/view.inc on line 765.

How i can correct?

Thanks!

Could you please post the relevant parts of phpinfo() output?

tnx for quick reply.

how can i do that? (one time i force drupal to see all php errors but white page again..)

the problem is when database file is about 4mb then another file appear in same folder with .journal extension. site is all white pages then. is any filesize limit in sqlite?

the sites is from my lab in my university in expirimental state.. im student here and we try to not invole mysql...

i must figure out if is host site issue or sqlite issue (shall i try Xammp?)

tnx so much for your time and for you answers!!

See in the "Requirements" section above, where I describe the procedure to create a phpinfo.php file.

SQLite is not limited, and 4Mb is too low to be an acceptable limit! This site database is more than 30Mb, so that's not the culprit.
What's the host config (I mean OS, PHP, ...)?
Have you double checked the database folder permissions? Web Server must have RW on both the database file AND the containing folder.
Is the temporary folder set and available, with sufficient disk space? (you can retrieve all these info through the phpinfo file).

As for XAMPP, yes you can test it and it works perfectly; please ensure that the XAMPP user has RW permissions on Drupal folders.

Send me the phpinfo() file once you have it, using the contact form.

Thank you for Drupal-SQLite! Very handy!
But there are a few erros in Backend, after successful installation.
It starts with:
warning: PDOStatement::execute() [function.PDOStatement-execute]: SQLSTATE[HY000]:
General error: 14 unable to open database file in /var/www/drupal-sqlite/includes/database.sqlite.inc on line 214.
# user warning: query: UPDATE variable SET value = 'b:0;' WHERE name = 'drupal_http_request_fails' in /var/www/drupal-sqlite/includes/bootstrap.inc on line 519.
# warning: PDOStatement::execute() [function.PDOStatement-execute]: SQLSTATE[HY000]: General error: 14 unable to open database file in /var/www/drupal-sqlite/includes/database.sqlite.inc on line 214.

sqlite-file ist read- and writeable by webserver.
Webserver lighttp PHP Version 5.2.4-2ubuntu5.5
Any Ideas?
Regards Jehu

Really don't know.
It seems it can't write (I saw the error comes from an UPDATE SQL command) but you said you checked read/write permissions of *.s3db file...

I'll write you an email so we could solve it privately, then I'll post the final solution here.
Stay tuned.

Tnx for your mail and hints, coolsoft.
I've found this (http://www.mail-archive.com/[email protected]/msg28190.html) and now I also know that the directory need write permissions too...

Thanks again.
You've done a good Job with Drupal-SQLite!

I'm glad we sorted it out.
I'll add a note in setup instructions for the upcoming next version and, better, will perform a test while creating the database file.

Let me know when your website will be on-line and I'll post here.

Hi, To further on the permission problems, --- putting the db.s3db in a seperarte folder, (I created a folder called db in the main directory) stopped the many errors I got after installing. It seems drupal keeps setting permission of the ./sites/default/ directory to non writable, but in my case it needs to be writable to write to the sqlite db file (on linux running apache). During the last step of the install and afterwards it kept producing errors on writing to the database.

This is my first time on drupal after a few years away, and first time testing it with sqlite, so please let me know if you can think of any issues with creating a db folder with owner write permissions like I have.

--Vince

Hi Vince, let me explain this issue a little bit for all the audience ;)

SQLite needs RW on the folder containing the DB because, during its life, it sometimes needs to create additional temporary and journal files.
That said, RW on the *.s3db is mandatory to be able to change it with INSERT, DELETE and UPDATE queries; but when transactions get into the game (like bunch of INSERTS and/or UPDATES), SQLite needs temp files support. Because of this, RW on the *.s3db file itself is not enough and the file creation permission is needed (RW on the folder).

What you suggest (a separate folder for the db) is what I'm trying to do in next Drupal-SQLite version.
It'll be safer to require RW on a folder which contains only the database and not (actually) other relevant ones like settings.php.
This will also make easier to fix the security issue regarding the db file being downloaded: an .htaccess file which denies access to all will suffice.

As for your question, I can't see any issue having a folder with RW permissions (many PHP applications have it), except creating it in website root.
Drupal suggested folders are under sites/ folder (see api.drupal.org/api/file/sites/default/settings.php/5), so I'll suggest to choose one of these.

Stay tuned and subscribe to the newsletter to be informed about new releases.

Thanks for your comment
Claudio

Hello Claudio,

Thanks a lot for the sqlite3 patch for Drupal 6, which I have been waiting for quite some time.
I have just finally managed to use SQLite for my test web site, but I always get the following error messages on my lighttpd error log:

2009-04-23 19:43:10: (mod_fastcgi.c.2610)
FastCGI-stderr:
PHP Notice: Undefined variable:
username in /home/www/drupal-6.10_SQLite/includes/database.sqlite.inc on line 82
PHP Notice: Undefined variable:
password in /home/www/drupal-6.10_SQLite/includes/database.sqlite.inc on line 82

So what I changed that line 82 on database.sqlite.inc from:

$connection = new PDO($dsn, $username, $password, $driver_options);

to

$connection = new PDO($dsn, '', '', $driver_options);

There is now no such error as before.

Thanks again.
Anto

Thanks for your fix, Anto.

I missed this; I'll be included it in the upcoming version.
And I will set my PHP to show warnings ASAP ;).

I commenti nella versione inglese di questa pagina sono piu' numerosi e riguardano problemi (e relative soluzioni) che si possono verificare durante l'installazione e l'uso di Drupal-SQLite.
Li potete trovare a questo indirizzo.

I noticed that comments on this page were accidentally closed for a few days.
Sorry for that...

I've installed the latest version. Everything looks fine. But I cannot add users ( Users can be added, but doesn't show on the user list).
Please give me a clue.

I've just set up this version of Drupal SQLite v6.12 on very low-memory (360MB) machine running nginx+fastcgi.  Very noticeable increase in page loads.

Is there still a performance benefit from page caching with the SQLite database layer?  Since cached content is stored in the database, caching would presumably cause the database flat file to grow in size substantially, and possiblity negate the desired effect of the caching.

IMHO page caching is mandatory for each website, even those hosted on a super quad-core machine.
That's because I hate redoing the same thing I've already done 10 seconds ago :)

Think of a Drupal page life cycle: request parsing, user auth, modules activation and invocation, content retrieval for node and all of the modules|menus|links|comments, layout composition & rendering, compression.
Moreover most of these steps require a DB query.

Content caching requires only a DB query to retrieve the already compressed result, if still valid.
My suggestion is to enable caching, since it always worth.

As for the DB space,I don't know what kind of site you're building; just to give a reference, the database of this site is near 20Mb uncompressed.

 

Hi!

I am really glad that you've done this port of drupal. It really works well, even with modules other than listed here. So far I've only had one problem with the devel module. It fails with an SQL error (on the devel_query table) upon install.

I have tried devel-6.x-1.17 and it will probably be the same with later versions as well. It is only the devel / devel module that's affected.
The fix is simple though: just open up the devel.install file from the module's directory, and change in the devel_schema() function this:

'primary key' => array('qid'), 
'indexes' => array('hash' => array('hash'))

Easy, not? ;)

When you uninstall the devel module - it might still say errors about database table being locked. Do not worry, if all else fails, and you really want to get rid of the devel tables, just open up the database from your favorite editor and droip the devel_* tables.

Thanks for your tip and effort providing a workaround...

Hi, I'm trying to set up a calendar style view on drupal-sqlite but always get an error related to a wrong query. Once I setup the view, the list shows the following error:

  • warning: PDO::prepare() [pdo.prepare]: SQLSTATE[HY000]: General error: 1 near "<=": syntax error in C:optApache2htdocsdrupal-sqliteincludesdatabase.sqlite.inc on line 195.

  • user warning: near "<=": syntax error
    query: SELECT node.nid AS nid, node.title AS node_title, node_data_field_date.field_date_value AS node_data_field_date_field_date_value, node_data_field_date.field_date_value2 AS node_data_field_date_field_date_value2, node_data_field_date.field_date_rrule AS node_data_field_date_field_date_rrule, node_data_field_date.delta AS node_data_field_date_delta, node.type AS node_type, node.vid AS node_vid FROM node node LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid WHERE ( <= '2009-08' AND >= '2009-08') in C:optApache2htdocsdrupal-sqlitemodulesviews-6.x.3.x-devviewspluginsviews_plugin_query_default.inc on line 1094.

As you can see, the column that should have been passed on as argument is ommitted! WHERE ( <= '2009-08' AND >= '2009-08'). As I've have not found this issue in any other drupal related websites I supposed it is related to your patch. Am I right? I'm using the following versions:
- Windows XP
- SQLite3
- Drupal-SQLite 6.13-1.3
  -> views-6.x.3.x-dev
  -> calendar-6.x.2.2
  -> cck-6.x.2.5
  -> date-6.x.2.3

Hi Davyd, I have bad news for you.
As I said, not all of Drupal modules will work, and Date is one of them.

I setup an environment like yours and I see that Date module is not SQLite aware at all.
A quich search for 'mysql' string returned a bunch of results from the Date module.
File date/date_api_sql.inc contains a lot of switch constructs due to the fact it is strictly DB-related.

Example, at line 10:

function date_sql_concat($array) {
  global $db_type;
  switch ($db_type) {
    case('mysql'):
    case('mysqli'):
      return "CONCAT(". implode(",", $array) .")";
    case('pgsql'):
      return implode(" || ", $array);
  }
}

This code is easily patchable to make it work with SQLite:

function date_sql_concat($array) {
  global $db_type;
  switch ($db_type) {
    case('mysql'):
    case('mysqli'):
      return "CONCAT(". implode(",", $array) .")";
    case('sqlite'):
    case('pgsql'):
      return implode(" || ", $array);
  }
}

This is the easiest one, other functions are harder.
See sql_field() at line 163, which requires deep changes and, maybe, missing SQLite SQL functions to be added.

I think that making Date module work with Drupal-SQLite is not feasible.
Sorry.

Hi Davyd,

I had the same problem two months ago on my site.
As Claudio already said, the Date module needs a lot of patches. In almost every function you have a
switch ($db_type) {

Here my patch file for date/date_api_sql.inc.
It is by far not perfect, but it works for me, may be also for you.

--- date_api_sql.orig.inc	So Mai  3 17:37:16 2009
+++ date_api_sql.inc	Di Jul 14 20:58:37 2009
@@ -14,6 +14,7 @@
     case('mysqli'):
       return "CONCAT(". implode(",", $array) .")";
     case('pgsql'):
+    case('sqlite'):
       return implode(" || ", $array);
   }
 }
@@ -30,6 +31,7 @@
     case('mysql'):
     case('mysqli'):
     case('pgsql'):
+    case('sqlite'):
       return "COALESCE(". implode(',', $array) .")";
   }  
 }
@@ -97,7 +99,10 @@
           if ($test == '2008-02-15 06:00:00') {
             $has_support = TRUE;
           }
-        break;
+          break;
+        case('sqlite'):
+          // no support
+          break;
       }
       variable_set('date_db_tz_support', $has_support);
     }
@@ -130,6 +135,9 @@
       elseif ($type == 'pgsql') {
         db_query("SET TIME ZONE INTERVAL '$offset' HOUR TO MINUTE");
       }
+      elseif ($type == 'sqlite') {
+        // no support
+      }
       $already_set = true;
     }
   }
@@ -185,6 +193,18 @@
             break;
         }
         break;
+      case('sqlite'):
+        switch ($this->date_type) {
+          case DATE_UNIX:
+            $field = "FROM_UNIXTIME($field)";
+            break;
+          case DATE_ISO:
+            $field = "REPLACE($field, 'T', ' ')";
+            break;
+          case DATE_DATETIME:
+            break;
+        }
+        break;
       case 'pgsql':
         switch ($this->date_type) {
           case DATE_UNIX:
@@ -216,6 +236,17 @@
           else {
             return "DATE_ADD($field, INTERVAL $offset SECOND)";
           }
+        case('sqlite'):
+          $offset = ($offset >= 0) ? "+ $offset" : ("- " . -$offset);
+          switch ($this->date_type) {
+            case DATE_UNIX:
+              return "($field $offset)";
+            case DATE_ISO:
+              return "STRFTIME('%Y-%m-%%dT%H:%M:%S', $field, '$offset SECONDS')";
+            case DATE_DATETIME:
+              return "DATETIME($field, '$offset SECONDS')";
+          }
+          break;
         case 'pgsql':
           return "($field + INTERVAL '$offset SECONDS')";;
       }
@@ -247,6 +278,16 @@
           case 'SUB':
             return "DATE_SUB($field, INTERVAL $count $granularity)";
           }
+      case('sqlite'):
+        $direction = ($direction == 'ADD') ? '+' : '-';
+        switch ($this->date_type) {
+          case DATE_UNIX:
+            return "STRFTIME('s', $field, '$direction $count $granularity')";
+          case DATE_ISO:
+            return "STRFTIME('%Y-%m-%%dT%H:%M:%S', $field, '$direction $count $granularity')";
+          case DATE_DATETIME:
+            return "DATETIME($field, '$direction $count $granularity')";
+        }
 
       case 'pgsql':
         $granularity .= 'S';
@@ -307,6 +348,8 @@
           // WITH TIME ZONE assumes the date is using the system
           // timezone, which should have been set to UTC.
           return "TIMESTAMP WITH TIME ZONE $field AT TIME ZONE $localzone";
+        case 'sqlite':
+          // no support
       }
     }
   }
@@ -336,6 +379,18 @@
           );
         $format = strtr($format, $replace);
         return "DATE_FORMAT($field, '$format')";
+      case('sqlite'):
+        $replace = array(
+          'Y' => '%Y', 'y' => '%y',
+          'm' => '%m', 'n' => '%c',
+          'd' => '%%d', 'j' => '%j',
+          'H' => '%H',
+          'i' => '%M',
+          's' => '%S',
+          'WW' => '%W',
+          );
+        $format = strtr($format, $replace);
+        return "STRFTIME('$format', $field)";
       case 'pgsql':
         $replace = array(
           'Y' => 'YYYY', 'y' => 'Y',
@@ -387,6 +442,7 @@
           // WEEK using arg 3 in mysql should return the same value as postgres EXTRACT
           return "WEEK($field, 3)";
         case('pgsql'):
+        case('sqlite'):
           return "EXTRACT(WEEK FROM($field))";
       }
     case('DOW'):
@@ -397,6 +453,7 @@
           // php date functions and postgres use 0 for Sunday and 6 for Saturday
           return "INTEGER(DAYOFWEEK($field) - 1)";
         case('pgsql'):
+        case('sqlite'):
           return "EXTRACT(DOW FROM($field))";
       }
     case('DOY'):
@@ -405,6 +462,7 @@
         case('mysqli'):
           return "DAYOFYEAR($field)";
         case('pgsql'):
+        case('sqlite'):
           return "EXTRACT(DOY FROM($field))";
       }
     }

Thanks Dmitri for your patch and for all the other valuable ones you sent me before (see changelog).
A new forum will start soon: this way searching for issues and patches will be easier.

Pages