Roundcube Community Forum

News and Announcements => General Discussion => Topic started by: mamash on June 29, 2009, 06:13:49 AM

Title: Making sqlite 3.x work
Post by: mamash 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.



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.
Title: On 0.3-stable it doesn't work :(
Post by: jpiedel 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
Title: Making sqlite 3.x work
Post by: mamash 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.
Title: Making sqlite 3.x work
Post by: mamash 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:

Title: On 0.3-stable it still doesn't work :(
Post by: jpiedel 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
Title: Making sqlite 3.x work
Post by: WaxWeazle 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...
Title: I Solve this Problem
Post by: WaxWeazle 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
Title: Making sqlite 3.x work
Post by: mamash 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.
Title: Making sqlite 3.x work
Post by: Seppel 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
Title: Making sqlite 3.x work
Post by: mamash 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).
Title: Making sqlite 3.x work
Post by: Seppel 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 :)
Title: trying to get sqlite3 to work with roundcube..
Post by: dirtbag 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
Title: still cant get sqlite3 to work with roundcube
Post by: dirtbag 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
Title: Making sqlite 3.x work
Post by: rkarpuzov on June 28, 2011, 07:31:35 AM
patch doesn't work with new versions of roundcube
Title: Making sqlite 3.x work
Post by: mamash 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.
Title: Updated instructions and patches for roundcube-0.7
Post by: andrew.fyfe on December 22, 2011, 06:10:58 PM
Updated instructions and patches for roundcube-0.7:

Required files:
Instructions:
Title: Making sqlite 3.x work
Post by: SKaero on December 22, 2011, 07:01:08 PM
Have you submitted a ticket for this problem?
Title: Making sqlite 3.x work
Post by: andrew.fyfe on December 23, 2011, 12:20:43 AM
Quote from: skaero;37751
Have you submitted a ticket for this problem?


It's already been submitted and fixed 1488255 (http://trac.roundcube.net/ticket/1488255)
Title: Does not work for me
Post by: double on March 05, 2012, 09:06:58 AM
Applied this patches against 0.7.1.
No chance, won't work.

Error:
DATABASE ERROR: CONNECTION FAILED!
Unable to connect to the database!
Please contact your server-administrator.

sqlite2 is not in the CentOS6 repositiory, is it?

Thanks a lot
Markus
Title: Making sqlite 3.x work
Post by: double on March 05, 2012, 10:27:48 AM
Errormessage:
MDB2 Error: not found Query: unable to find package 'MDB2_Datatype' file 'MDB2/Datatype.php' in /home/www/roundcubemail-0.7.1/program/include/rcube_mdb2.php on line 717 (GET /roundcubemail-0.7.1/)
Title: Re: Making sqlite 3.x work
Post by: mamash on April 15, 2012, 03:26:32 PM
Thanks to andrew.fyfe for the corrections. I'm migrating my mail server, and starting with 0.7.2 from scratch, and still works for me just fine.
Title: Re: Making sqlite 3.x work
Post by: mgillespie on May 08, 2012, 06:17:24 PM
Any plans to get this patch into the SVN mainstream?  lack of sqllite is a showstopper for me.  I can't run full SQL on a NAS..  Sqllite2 is being removed from many distros now.

I tried to apply these patches, but it didn't work for me...  It's not really clear what's been fixed already, what's part of the patch etc.
Title: Re: Making sqlite 3.x work
Post by: SKaero on May 08, 2012, 09:22:57 PM
This problem was already fixed (see ticket #1488255 http://trac.roundcube.net/ticket/1488255) if your having problems with the latest version you should open up a new ticket.
Title: Re: Making sqlite 3.x work
Post by: mgillespie on May 09, 2012, 02:31:25 PM
This problem was already fixed (see ticket #1488255 http://trac.roundcube.net/ticket/1488255) if your having problems with the latest version you should open up a new ticket.

Are you saying Sqlite3 support is in the latest SVN? So what bits of this do I need and no longer need to do?

I have switched to Debian Wheezy from Squeeze, and all I know, is roundcube no longer works, and it's being a right old faff to get it going...
Title: Re: Making sqlite 3.x work
Post by: SKaero on May 09, 2012, 04:03:43 PM
Sqlite is supposedly supported, I'm not sure what version is supported (I'm not a RoundCube developer) but the changes in this thread have already been applied to the RoundCube core.
Title: Re: Making sqlite 3.x work
Post by: mamash on May 09, 2012, 04:07:45 PM
SQlite has been supported for a long time, but only in the 2.x version (which is quite old and may not be supported at several places). I'm pretty sure nothing has happened on this front at RC, and since it depends on the MDB2 Pear modules for its database drivers, and there is no official MDB2 SQLite 3 driver yet (as far as I know…).
Title: Re: Making sqlite 3.x work
Post by: SKaero on May 09, 2012, 06:59:30 PM
I see what you mean, I found this ticket http://trac.roundcube.net/ticket/1488255 with the version 3 problem. My guess is you could patch RC with the bottom 3 files listed in the first post but I think some of the code has changed.
Title: Re: Making sqlite 3.x work
Post by: mamash on May 10, 2012, 03:42:34 AM
mgillespie: i think you're looking for this: http://trac.roundcube.net/ticket/1488332

The bad news is that it doesn't look like the RC devs have any plans to write their own SQLite3 support (as there doesn't seem to be an official MDB2 driver for SQLite3 coming, and using the PHP sqlite3 extension or the PDO driver would require adding some substantial code to RC). So the current SQLite support in RC doesn't have much sense, as SQLite2 is being phased out everywhere.

The good news is that the unofficial driver (from http://pear.php.net/bugs/bug.php?id=6907) and my patches still work fine on the latest RC release. I'm using it on my live system even.