Author Topic: Making sqlite 3.x work  (Read 20216 times)

Offline mamash

  • Newbie
  • *
  • Posts: 9
Making sqlite 3.x work
« on: June 29, 2009, 06:13:49 AM »
I have Roundcube working with an sqlite 3.x database. I'm posting here for lack of a better idea - the approach was confirmed working for both the stable 0.2.x version and yesterday's SVN checkout.

  • Download the experimental pdoSqlite MDB2 driver from Marin Ivanov, as linked in the PEAR forums.
  • Extract the driver contents under 'Driver' of your existing MDB2 directory.
  • Patch rcube_mdb2.php using
  • Change your database driver from 'sqlite' to 'pdoSqlite' (db_dsnw in db.inc.php)


I cannot comment on the stability of the experimental driver - I guess it's not available from any standard distribution channel for a reason. It's working for me so far though.

Offline jpiedel

  • Newbie
  • *
  • Posts: 2
On 0.3-stable it doesn't work :(
« Reply #1 on: September 14, 2009, 09:18:58 AM »
Mamash, do You know if a patch for version "0.3-stable" is available?

In errorlog I find this:

[14-wrz-2009 15:14:28] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: DELETE FROM session WHERE sess_id='p76079uk0csekjqml8sv15b7e1']
[Native code: HY000]
[Native message: unable to open database file]

[14-Sep-2009 15:14:28 +0200]: DB Error: MDB2 Error: unknown error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: DELETE FROM session WHERE sess_id='p76079uk0csekjqml8sv15b7e1'] [Native code: HY000] [Native message: unable to open database file]  in /var/www/roundcubemail-0.3-stable/program/include/rcube_mdb2.php on line 630 (POST /poczta2/?_task=&_action=login)
[14-wrz-2009 15:14:28] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: INSERT INTO users
        (created, last_login, username, mail_host, alias, language)
       VALUES (now(), now(), 'user', 'localhost', '', 'pl_PL')]
[Native code: HY000]
[Native message: unable to open database file]

[14-Sep-2009 15:14:28 +0200]: DB Error: MDB2 Error: unknown error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: INSERT INTO users (created, last_login, username, mail_host, alias, language) VALUES (now(), now(), 'user', 'localhost', '', 'pl_PL')] [Native code: HY000] [Native message: unable to open database file]  in /var/www/roundcubemail-0.3-stable/program/include/rcube_mdb2.php on line 630 (POST /poczta2/?_task=&_action=login)
[14-wrz-2009 15:14:28] MDB2 Error: unknown error (-1): lastInsertID: [Error message: Could not get last insert ID]
[Last executed query: INSERT INTO users
        (created, last_login, username, mail_host, alias, language)
       VALUES (now(), now(), 'user', 'localhost', '', 'pl_PL')]
[Native code: 00000]
[Native message: unable to open database file]

[14-Sep-2009 15:14:28 +0200]: PHP Error: Failed to create new user in /var/www/roundcubemail-0.3-stable/program/include/rcube_user.php on line 452 (POST /poczta2/?_task=&_action=login)
[14-Sep-2009 15:14:28 +0200]: PHP Error: Failed to create a user record. Maybe aborted by a plugin? (POST /poczta2/?_task=&_action=login)
[14-wrz-2009 15:14:28] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: DELETE FROM session WHERE sess_id='p76079uk0csekjqml8sv15b7e1']
[Native code: HY000]
[Native message: unable to open database file]

[14-Sep-2009 15:14:28 +0200]: DB Error: MDB2 Error: unknown error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: DELETE FROM session WHERE sess_id='p76079uk0csekjqml8sv15b7e1'] [Native code: HY000] [Native message: unable to open database file]  in /var/www/roundcubemail-0.3-stable/program/include/rcube_mdb2.php on line 630 (POST /poczta2/?_task=&_action=login

Offline mamash

  • Newbie
  • *
  • Posts: 9
Making sqlite 3.x work
« Reply #2 on: September 15, 2009, 03:49:18 AM »
That's weird, I have the same modification running on my system under a trunk version from svn (i.e. even newer than 0.3-stable). Did the patch apply cleanly on your system?

I'll revisit and try on a vanilla 0.3-stable instance on my system.

Offline mamash

  • Newbie
  • *
  • Posts: 9
Making sqlite 3.x work
« Reply #3 on: September 15, 2009, 10:30:11 AM »
There is no problem with the patch. How did you init the database? Sounds like you might have used 'sqlite -init' whereas you should have used 'sqlite3 -init' instead (for SQLite 3.x).

Anyway, this is an improved patch for 0.3-stable that adds SQLite 3.x support to the installer too. To install a clean RoundCube 0.3 using SQLite 3.x:

  • Download the experimental pdoSqlite MDB2 driver from Marin Ivanov, as linked in the PEAR forums.
  • Extract the driver contents under 'Driver' of your existing MDB2 directory.
  • In the Roundcube root directory, apply the aggregate , e.g.:
    Code: [Select]
    patch -p0 < /path/to/rcube-0.3-stable-sqlite3.patch
  • Initialize the database, e.g.:
    Code: [Select]
    sqlite3 -init SQL/sqlite.initial.sql /path/to/dbfile
  • Run the installer and use the new 'SQLite 3.x' option for database storage

Offline jpiedel

  • Newbie
  • *
  • Posts: 2
On 0.3-stable it still doesn't work :(
« Reply #4 on: September 16, 2009, 12:10:35 PM »
Of course I've used sqlite3, and I've used tested configuration from version 0.2.2:
$rcmail_config['db_dsnw'] = 'pdoSqlite://./sqlite.db';
File sqlite.db is located in root roundcube directory, is owned by apache user and has 0640 permissions.
I've also tried '?mode=0646' and '?mode=0640' (with the same or different filesystem permission mask).

Now error appears before login screen, not after entering login and password like last time.

In log I find this:
[16-Sep-2009 17:59:02] MDB2 Error: not found (-4): unable to find package 'MDB2_Datatype' file 'MDB2/Datatype.php'
[16-Sep-2009 17:59:02 +0200]: DB Error: MDB2 Error: not found Query: unable to find package 'MDB2_Datatype' file 'MDB2/D
atatype.php' in /var/www/roundcubemail-0.3-stable/program/include/rcube_mdb2.php on line 630 (GET /poczta3/)

My MDB2 pdoSqlite driver: pdoSqlite.php,v 1 2009/03/07 00:38:39

Offline WaxWeazle

  • Newbie
  • *
  • Posts: 2
Making sqlite 3.x work
« Reply #5 on: January 30, 2010, 08:30:10 AM »
Quote from: jpiedel;21360
Of course I've used sqlite3, and I've used tested configuration from version 0.2.2:
$rcmail_config['db_dsnw'] = 'pdoSqlite://./sqlite.db';
File sqlite.db is located in root roundcube directory, is owned by apache user and has 0640 permissions.
I've also tried '?mode=0646' and '?mode=0640' (with the same or different filesystem permission mask).

Now error appears before login screen, not after entering login and password like last time.

In log I find this:
[16-Sep-2009 17:59:02] MDB2 Error: not found (-4): unable to find package 'MDB2_Datatype' file 'MDB2/Datatype.php'
[16-Sep-2009 17:59:02 +0200]: DB Error: MDB2 Error: not found Query: unable to find package 'MDB2_Datatype' file 'MDB2/D
atatype.php' in /var/www/roundcubemail-0.3-stable/program/include/rcube_mdb2.php on line 630 (GET /poczta3/)

My MDB2 pdoSqlite driver: pdoSqlite.php,v 1 2009/03/07 00:38:39


Same Problem...

Offline WaxWeazle

  • Newbie
  • *
  • Posts: 2
I Solve this Problem
« Reply #6 on: January 30, 2010, 08:42:24 AM »
yeah it works.

check the permissions on file:

ROUNDCUBE-Folder/program/lib/MDB2/Driver/Datatype/pdoSqlite.php

if not changed there on 770. this doesn't work.

change it to 666 and RoundCube runs on SQlite 3

thx

Offline mamash

  • Newbie
  • *
  • Posts: 9
Making sqlite 3.x work
« Reply #7 on: January 30, 2010, 06:11:32 PM »
Ah, yes, the most obvious trivial problem - the permissions. Depends on how you execute your scripts etc. Glad it's working for other people too.

Offline Seppel

  • Newbie
  • *
  • Posts: 3
Making sqlite 3.x work
« Reply #8 on: April 15, 2010, 03:48:11 AM »
Hi everybody,

I tried to apply the sqlite3 patches to my RC installation (since I had some problems with sqlite2). No matter which RC version I tried (0.3.1 and trunk-r3437) the configuration stops with this result
Code: [Select]
Check configured database settings
DSN (write):  OK
DB Schema:  NOT OK(Database not initialized)
But my database file is initialized (using the sqlite3 binary)!?
I double checked all file permissions but they seem ok to me.

System specs:
Windows Home Server
Apache 2.2.15
PHP 5.3.0
hMailServer 5.3.2

Just don't know where to check or what to change :(
Any help very much appreciated :)

Cheers,
  Seppel

Offline mamash

  • Newbie
  • *
  • Posts: 9
Making sqlite 3.x work
« Reply #9 on: April 16, 2010, 03:02:27 AM »
Do you have the experimental pdoSqlite.php driver installed? Also, I haven't tried this patch under PHP 5.3 yet, so can't confirm on that (will try soon).

Offline Seppel

  • Newbie
  • *
  • Posts: 3
Making sqlite 3.x work
« Reply #10 on: April 16, 2010, 03:24:59 AM »
Quote from: mamash;26826
Do you have the experimental pdoSqlite.php driver installed? Also, I haven't tried this patch under PHP 5.3 yet, so can't confirm on that (will try soon).
The one you've described in your step-by-step instruction? Yes - I have.

For the trunk version I had to change this line by hand:
Code: [Select]
+    'PgSQL' => 'pgsql', 'SQLite' => 'sqlite', 'SQLite 3.x' => 'pdoSqlite');
Everything else worked like a charme ;)

I just enabled the log messages. When trying to login, I get this:
Code: [Select]
Warning: PDO::query(): SQLSTATE[HY000]:
General error: mode must be an integer in \program\lib\MDB2\Driver\pdoSqlite.php on line 572
DB Error in \program\include\rcube_mdb2.php (639): MDB2 Error: unknown error Query: _doQuery:
[Error message: Could not execute statement] [Last executed query: SELECT vars, ip,
UNIX_TIMESTAMP(changed) AS changed FROM session WHERE sess_id = 'rh276ns6vts0ipcbrch3vulde4']
[Native code: 00000]
Do you have any idea whats causing this?

Thanx for your help :)
« Last Edit: April 16, 2010, 03:27:30 AM by Seppel »

Offline dirtbag

  • Newbie
  • *
  • Posts: 1
trying to get sqlite3 to work with roundcube..
« Reply #11 on: May 05, 2010, 11:30:59 AM »
I did the patching and installed the drivers and whatnot.. but on the installer page, i see the following:

Check configured database settings
DSN (write):  OK
DB Schema:  NOT OK(Cannot read the schema file: ../SQL/pdoSqlite.initial.sql)

I cannot find this pdoSqlite.initial.sql

and it does not seem to like the database i made with

sqlite -init SQL/sqlite.initial.sql sqlite.db

-db

Offline dirtbag

  • Newbie
  • *
  • Posts: 1
still cant get sqlite3 to work with roundcube
« Reply #12 on: May 05, 2010, 11:34:02 AM »
I did the patching and installed the drivers and whatnot.. but on the installer page, i see the following:

Check configured database settings
DSN (write):  OK
DB Schema:  NOT OK(Cannot read the schema file: ../SQL/pdoSqlite.initial.sql)

I cannot find this pdoSqlite.initial.sql

and it does not seem to like the database i made with

sqlite -init SQL/sqlite.initial.sql sqlite.db

Im getting this error too:

Code: [Select]
[05-May-2010 19:42:16 +0200]: DB Error: MDB2 Error: unknown error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: SELECT vars, ip, UNIX_TIMESTAMP(changed) AS changed FROM session WHERE  sess_id='ldnu9ca02d5mtrj1ngtp341e03'] [Native code: 00000]  in /usr/local/apache2/htdocs/roundcube/program/include/rcube_mdb2.php on line 630 (GET /)
[05-May-2010 19:42:37] PHP Warning:  PDO::query(): SQLSTATE[HY000]: General error: mode must be an integer in /usr/local/apache2/htdocs/roundcube/program/lib/MDB2/Driver/pdoSqlite.php on line 572
[05-May-2010 19:42:37] MDB2 Error: unknown error (-1): _doQuery: [Error message: Could not execute statement]
[Last executed query: SELECT vars, ip, UNIX_TIMESTAMP(changed) AS changed
     FROM session
     WHERE  sess_id='ldnu9ca02d5mtrj1ngtp341e03']
[Native code: 00000]

[05-May-2010 19:42:37 +0200]: DB Error: MDB2 Error: unknown error Query: _doQuery: [Error message: Could not execute statement] [Last executed query: SELECT vars, ip, UNIX_TIMESTAMP(changed) AS changed FROM session WHERE  sess_id='ldnu9ca02d5mtrj1ngtp341e03'] [Native code: 00000]  in /usr/local/apache2/htdocs/roundcube/program/include/rcube_mdb2.php on line 630 (GET /)


-db
« Last Edit: May 05, 2010, 01:51:58 PM by dirtbag »

Offline rkarpuzov

  • Newbie
  • *
  • Posts: 1
Making sqlite 3.x work
« Reply #13 on: June 28, 2011, 07:31:35 AM »
patch doesn't work with new versions of roundcube

Offline mamash

  • Newbie
  • *
  • Posts: 9
Making sqlite 3.x work
« Reply #14 on: June 29, 2011, 05:15:24 AM »
Try removing the patch part where fromunixtime() is being patched - the two lines that start with "case 'pdoSqlite':". I don't think that's needed on the latest versions anymore. I have been running this setup ever since, right now on trunk from ~ March this year, just didn't have time to try to upgrade to the latest yet.