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!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.php
containing 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 mysql
or 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
- 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
Drupal-SQLite-6.25-1.5, 2012-02-29
- Drupal official version 6.25 (see http://drupal.org/node/1461656)
Drupal-SQLite-6.24-1.5, 2012-02-03
- Drupal official version 6.24 (see http://drupal.org/node/1425094)
Drupal-SQLite-6.22-1.5, 2011-06-02
- Drupal official version 6.24 (see http://drupal.org/node/1168950)
Drupal-SQLite-6.20-1.5, 2010-12-16
- Drupal official version 6.20
Drupal-SQLite-6.19-1.5, 2010-08-12
- Drupal official version 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)
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
- database.sqlite.core-patches.inc
- 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 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
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:
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 |
Navigation
Login
Support me
Click here if you want to support CoolSoft using PayPal
Comments
Pages
Lo so, capisco. Purtroppo
Lo so, capisco.
Purtroppo però, da parte mia non posso fare un gran chè: lo strato database di Drupal-SQLite per SQLite è pressochè completo. Il problema è che alcuni moduli (tra cui View) fanno ampio uso di query customizzate per il database correntemente in uso.
Gli sviluppatori di questi moduli ovviamente testano il loro codice solo sui DB ufficialmente supportati, quindi MySQL e PostgreSQL (come spiego nella scheda).
Le soluzioni possibili sono:
La situazione cambierà in futuro con Drupal 7, dove SQLite sarà supportato ufficialmente.
I'm getting errors with
I'm getting errors with RealName module (http://drupal.org/project/realname). When I go to 'Recalculate names' tab and click 'Rebuild names', I'm getting this:
query: TRUNCATE realname in /var/www/minuteware.net/htdocs/sites/all/modules/realname/realname.admin.inc on line 482.
Seems to be just a warning, the recalculation seems to work fine. Could you please shed some more light onto this warning message.
That's because TRUNCATE is
That's because TRUNCATE is not a supported keyword in SQLite (see here).
If you look file database.sqlite.core-patches.inc, there's a core patch (applied to all queries) that changes TRUNCATE TABLE xxx to DELETE FROM xxx.
That said, your problem is that RealName use the short (and worst) TRUNCATE xxx command form instead of TRUNCATE TABLE xxx, so the patch will not happen.
Now you can:
no such table: interwiki
query: SELECT iw_prefix, iw_url, iw_rel, iw_local from interwiki in /modules/interwiki/interwiki.module on line 476.
I like what you have done, and thank you for putting effort into this. I am not much of a developer and I have never dealt with sqlite much. when installing the interwiki module (and I am aware the 6.x version is a development module) the download, extracting, and enabling seems to work, but when i go to admin/settings/interwiki iget the error listed above. I did some investigation and found line 218 in the database.sqlite.inc and it looks like this:
$last_result = $stmt = $active_db->prepare($query);
and line 476 in interwiki.module looks like this:
$result = db_query("SELECT iw_prefix, iw_url, iw_rel, iw_local from {interwiki}");
I am not entirely sure what I am looking at or what can be done (if anything) to get this to function with drupal-sqlite. I would appreciate any sugestions you might have.
Well, it seems a simple
Well, it seems a simple "case" issue: look at interwiki.install file, line 5.
You'll find a switch statement, (badly) written to consider only MYSQL and not other DBs.
That's why the required interwiki table won't get created.
The bad thing of that code is no errors are thrown at all (Postgree DB users will get into troubles as well).
Change "mysql" to "sqlite" (or add a new "sqlite" case) and redo module installation.
problemi installazione
Ho installato la versione d6.19 su uno spazio altervista aperto di recente.
Tale spazio prevede automaticamente il php5.
Quando faccio l'update dopo aver carcato un modulo vengono segnalati una serie di errori.
Tale problema non esiste su un vecchio spazio sempre su altervista che richiede la scelta del php5
cosa devo fare?
grazie
dettagli
> vengono segnalati una serie di errori
Posta gli errori che ottieni, così gli diamo un'occhiata...
ecco parte dei messaggi di
ecco parte dei messaggi di errore
warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY000]: General error: 6 database table is locked in /membri/gtechallengercup/includes/database.sqlite.inc on line 241.
user warning:
query: UPDATE system SET info = 'a:10:{s:4:"name";s:10:"Aggregator";s:11:"description";s:57:"Aggregates syndicated content (RSS, RDF, and Atom feeds).";s:7:"package";s:15:"Core - optional";s:7:"version";s:4:"6.19";s:4:"core";s:3:"6.x";s:7:"project";s:6:"drupal";s:9:"datestamp";s:10:"1281559292";s:12:"dependencies";a:0:{}s:10:"dependents";a:0:{}s:3:"php";s:5:"4.3.5";}', name = 'aggregator', filename = 'modules/aggregator/aggregator.module', bootstrap = 0 WHERE filename = 'modules/aggregator/aggregator.module' in /membri/gtechallengercup/includes/module.inc on line 141.
ho reperito il dato da una segnalazione fatta al forum di altervista (ora ho cancellato il sito per provare una nuova installazione)
cmq seguendo le istruzioni sul manuale e scenado il fine phpinfo.php risulta il sqlite library 3.3.6 (e non 3.3.7 come indicato nella guida)
grazie
Drupal 7
Vedo che sul tuo sito stai provando Drupal7, che ha il supporto a SQLite integrato.
So che il destino di Drupal-SQLite è segnato (lo sapevo già dal principio) e quindi ti chiedo se puoi farmi sapere i tuoi risultati di installazione di D7, sia positivi che negativi.
Grazie
l'installazione di drupal con
l'installazione di drupal con sqlite si blocca: richiede la versione del db 3.3.7. non so come fare. ho chiesto aiuto sul forum di drupalitalia ma non sono ancora riuscito a risolvere il problema.
cosa intendevi sul destino segnato di sqlite?
hai idea di come risolvere i problemi riscontrati dnell'installazione di d6.19 sqlite? (non ho avuto problemi con l'installazione in un vecchio spazio di altervista ove di defoult c'era php=4)
grAZIE
Io vedo che Altervista
Io vedo che Altervista (almeno sul mio sito) fornisce SQLite v.3.3.7, quindi i requisiti dovrebbero essere soddisfatti.
Prova a segare tutti file, .htaccess compreso, magari accedendo via FTP.
Poi riabilita il PHP5. Se ancora non funziona prova a mandarmi via mail l'output di phpinfo (vedi nell'articolo le istruzioni per farlo).
niente da fare. PECL Module
niente da fare.
PECL Module version: (bundled) 1.0.1 $Id: pdo_sqlite.3.x: (bundled) 1.0.1 $Id: pdo_sqlite.c 293036 2010-01-03 09:23:27Z sebastian $
SQLite Library: 3.3.6
ho riportato in sintesi in php.info perché non riesco a fare il copia e incolla
ciao
Aggiornamento Drupal 6.20
Salve, ho installato l'attuale versione di DrupalSqlite sul server http:// www .cremespalmabilialcioccolato .it , da ieri è disponibile la versione 6.20 di Drupal.
Quando uscirà l'aggiornamento anche per DrupalSqlite ?
Grazie.
Aggiornamento
L'aggiornamento è appena uscito... :)
Migrating data from MYSQL to SQLite
Hi all,
I'd migrate my site data db from mysql to sqlite db.
But I'm not able to migrate DB.
Do you know a good way to do it loseless (losing no data)?
Thank you
Sorry, I'm not aware of any
Sorry, I'm not aware of any opensource tool to do such conversion.
You could try to export your MySQL to test (ie. with phpMyAdmin export function), edit it with some sort of search/replace with a text editor (like emacs), then reimport in SQLite db.
You could even try one of the available Drupal plugins that provide DB backup functionality; choose one (if any) that will export your data in a DB-independent form: export from Drupal-MySql site then reimport into Drupal-SQLite... just a suggestion ;)
error in search module
Hi,
I am trying Drupal with sqlite (6.20-1.5 Drupal 6.20) on windows XP based XAMPP installation. Everything is working soothly, but I found a few errors in core search module -
------------------------------------------------------------------------------------------------------------
1. While Re-Indexing the site and selecting manual :
* warning: PDO::prepare() [pdo.prepare]: SQLSTATE[HY000]: General error: 1 no such function: CHAR_LENGTH in /includes/database.sqlite.inc on line 218.
* user warning: no such function: CHAR_LENGTH
query: SELECT DISTINCT cart_id FROM uc_cart_products WHERE changed <= 1297478616 AND CHAR_LENGTH(cart_id) > 8 in /profiles/uberdrupal/modules/ubercart/uc_cart/uc_cart.module on line 279.
* warning: PDO::prepare() [pdo.prepare]: SQLSTATE[HY000]: General error: 1 no such function: CHAR_LENGTH in /includes/database.sqlite.inc on line 218.
* user warning: no such function: CHAR_LENGTH
query: SELECT DISTINCT cart_id FROM uc_cart_products WHERE changed <= 1265957016 AND CHAR_LENGTH(cart_id) <= 8 in /profiles/uberdrupal/modules/ubercart/uc_cart/uc_cart.module on line 286.
------------------------------------------------------------------------------------------------------------
2. While performing actual search :
warning: PDO::prepare() [pdo.prepare]: SQLSTATE[HY000]: General error: 1 misuse of aggregate function MAX() in /includes/database.sqlite.inc on line 218.
user warning: misuse of aggregate function MAX()
query: SELECT COUNT(*) FROM (SELECT i.type, i.sid, 5 * (3.71410944608 * SUM(i.score * t.count)) + 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), COALESCE(MAX(c.last_comment_timestamp), 0)) - 1297338101) * 6.43e-8) + 5 * (2.0 - 2.0 / (1.0 + MAX(c.comment_count) * 1)) AS score FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN node n ON n.nid = i.sid LEFT JOIN node_comment_statistics c ON c.nid = i.sid WHERE n.status = 1 AND (i.word = 'this') AND i.type = 'node' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1) n1 in /modules/search/search.module on line 958.
warning: PDO::prepare() [pdo.prepare]: SQLSTATE[HY000]: General error: 1 misuse of aggregate function MAX() in /includes/database.sqlite.inc on line 218.
user warning: misuse of aggregate function MAX()
query: SELECT i.type, i.sid, 5 * (3.71410944608 * SUM(i.score * t.count)) + 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), COALESCE(MAX(c.last_comment_timestamp), 0)) - 1297338101) * 6.43e-8) + 5 * (2.0 - 2.0 / (1.0 + MAX(c.comment_count) * 1)) AS score FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN node n ON n.nid = i.sid LEFT JOIN node_comment_statistics c ON c.nid = i.sid WHERE n.status = 1 AND (i.word = 'this') AND i.type = 'node' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 ORDER BY score DESC LIMIT 10 OFFSET 0 in /modules/search/search.module on line 958.
------------------------------------------------------------------------------------------------------------
Can anyone suggest if this is a bug, or am I doing something wrong? If this works, I would like to use this package (Drupal with SQLITE) on my live site.
Thanks for your detailed
Thanks for your detailed feedback.
I'll take a look ASAP.
Claudio
Fixed in SVN
Both errors were fixed in latest SVN version.
If you are used to SVN, then update to the latest trunk version here:
https://drupal-sqlite.svn.sourceforge.net/svnroot/drupal-sqlite/trunk
Otherwise you can download the full archive here:
http://drupal-sqlite.svn.sourceforge.net/viewvc/drupal-sqlite/trunk/?view=tar
(it's a .tar.gz compressed archive, if you're on Windows open it with 7-Zip)
Additional Errors
Hi Coolsoft,
Thanks for fixing the errors - that was real fast :-).
I tried the new version in my local setup (XAMPP under Windows - sqlite version 3.6.20, php version 5.2.8). The status is as follows:
1. The installation was successful except the following two warnings (at the end of this list).
2. If I ignore the warnings and continue with configuring the site, no more errors are reported.
3. The search is also working correctly
However, I do not know if these warnings are serious for production sites.
-------------------------------------------------------------------------------------
The Installation Warnings are:
# warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[23000]: Integrity constraint violation: 19 blocks.pages may not be NULL in C:\xampplite\htdocs\includes\database.sqlite.inc on line 243.
# user warning:
query: INSERT INTO blocks (module, delta, theme, status, weight, region, custom, throttle, visibility, title, cache) VALUES ('menu', 'primary-links', 'acquia_prosper', 1, -5, 'footer', 0, 0, 0, '', 1) in C:\xampplite\htdocs\includes\common.inc on line 3538.
-------------------------------------------------------------------------------------
Well, this warning is not
Well, this warning is not related to Drupal-SQLite but to an additional module/theme trying to run a malformed query.
In fact it tries to insert a record into the "blocks" table without providing a value for the "pages" column, which hasn't a default value:
The guilty should be the "acquia-prosper" theme, as I can read from the reported SQL command.
You should report them the error.
Anyway, as for SQLite database engine, this should not be a problem: it will add the record using '' (empty string) as default value.
You could also test "blocks" table content with a SQLite database manager (I suggest SQLite Manager firefox plugin for this).
Thanks ...
Thanks .. I will investigate it further (with my rather limited PHP skills :-) ) and post my findings here if they are worth mentionable.
As I said earlier, I am planning to use this on a live site if everything works out well - and now I feel confident that it will.
You have done a great service to Drupal community (although Drupal 7 has built-in support for sqlite, I still would like to stay with 6.x for quite some time)
Errore nella ricerca
Anche a me da errore quando cerco qualcosa nel sito (su altervista con PDO v3.3.7)
e mi dice "La ricerca non ha prodotto risultati"
query: SELECT COUNT(*) FROM (SELECT i.type, i.sid, 5 * (3.6712070738161 * SUM(i.score * t.count)) + 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), COALESCE(MAX(c.last_comment_timestamp), 0)) - 1356031083) * 6.43e-8) + 5 * (2.0 - 2.0 / (1.0 + MAX(c.comment_count) * 0.33333333333333)) + 5 * (2.0 - 2.0 / (1.0 + MAX(nc.totalcount) * 1)) AS score FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN node n ON n.nid = i.sid LEFT JOIN node_comment_statistics c ON c.nid = i.sid LEFT JOIN node_counter nc ON nc.nid = i.sid WHERE n.status = 1 AND (i.word = 'sicurezza') AND i.type = 'node' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1) n1 in /membri/niko2f/sga/modules/search/search.module on line 958.
query: SELECT i.type, i.sid, 5 * (3.6712070738161 * SUM(i.score * t.count)) + 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), COALESCE(MAX(c.last_comment_timestamp), 0)) - 1356031083) * 6.43e-8) + 5 * (2.0 - 2.0 / (1.0 + MAX(c.comment_count) * 0.33333333333333)) + 5 * (2.0 - 2.0 / (1.0 + MAX(nc.totalcount) * 1)) AS score FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN node n ON n.nid = i.sid LEFT JOIN node_comment_statistics c ON c.nid = i.sid LEFT JOIN node_counter nc ON nc.nid = i.sid WHERE n.status = 1 AND (i.word = 'sicurezza') AND i.type = 'node' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 ORDER BY score DESC LIMIT 10 OFFSET 0 in /membri/niko2f/sga/modules/search/search.module on line 958.
Upgrade to Drupal7
Ciao, devo dire che ha proprio una gran compatibilità con i moduli che ho provato ! :)
Ti volevo chiedere su come fare l'update da drupal-sqlite a drupal7, che vorrei effettivamente fare, quando Altervista aggiornerà SQLite al 3.3.7 dal 3.3.6 che mi da!
Grazie!
Upgrade to Drupal7
Non ho ancora affrontato l'argomento, ma c'è ancora tempo per pensarci.
Drupal 7 è ancora troppo giovane e non offre la vastità di temi e moduli disponibili per Drupal 6, che rimarrà attivo per diverso tempo.
Drupal dovrebbe avere una procedura di upgrade, che però considera solo MySQL e PostgreeSQL (che erano supportati nella 6).
La mia intenzione è quella di modificare lo script di upgrade originale per renderlo compatibile con Drupal-SQLite.
Stay tuned...
PS: chiamando il PHPInfo() sul mio sito mi da la versione 3.3.7 di SQLite.
Ciao, complimenti per
Ciao, complimenti per l'articolo, davvero utile e interessante.
Volevo chiedere:
ma su altervista è possibile installare drupal 7 in modalità sqlite?
se si, esiste una guida da scaricare?
grazie
fab
Personalmente non ho ancora
Personalmente non ho ancora provato, ma lo farò presto e riporterò i risultati.
Grazie da ora :)
Grazie da ora
:)
Funziona anche su Altervista!
Confermo che Drupal SQLite funziona correttamente anche su Altervista.
can anyone help me with views module?
Hi guys, I installed the latest version of Drupal Sqlite and I was working to create my website. Now I meet a problem: to create some pages I need to use the "views" module and in particular the view called "blog archive". When I enable this view from the administration panel everything seems to be OK, but if I open the page containing the view I receive the error messages listed below: please, can anyone help me to solve them?
warning: PDO::prepare() [pdo.prepare]: SQLSTATE[HY000]: General error: 1 near "AS": syntax error in /web/htdocs/www.titengodocchio.it/home/includes/database.sqlite.inc on line 218. user warning: near "AS": syntax error query: SELECT COUNT(*) FROM (SELECT AS created_year_month, COUNT(node.nid) AS num_records FROM node node WHERE node.status <> 0 GROUP BY created_year_month ORDER BY created_year_month DESC ) count_alias in /web/htdocs/www.titengodocchio.it/home/sites/all/modules/views/includes/view.inc on line 745.
warning: PDO::prepare() [pdo.prepare]: SQLSTATE[HY000]: General error: 1 near "AS": syntax error in /web/htdocs/www.titengodocchio.it/home/includes/database.sqlite.inc on line 218. user warning: near "AS": syntax error query: SELECT AS created_year_month, COUNT(node.nid) AS num_records FROM node node WHERE node.status <> 0 GROUP BY created_year_month ORDER BY created_year_month DESC LIMIT 30 OFFSET 0 in /web/htdocs/www.titengodocchio.it/home/sites/all/modules/views/includes/view.inc on line 771.
PS: Sorry for the mystakes I did writing this comments, I am Italian.
I hope you could understand my English!
Pages