roundcube

[aqua]

Hello:

Sorry for the double post, but this was buried in the features section. Hopefully more people will see this that can respond :)

I want to update roundcube to the latest pkg package. Many people complained that the sender field was empty, and many pointed to the roundcube's FAQ, which states that this table must be altered by:

ALTER TABLE identities ADD `html_signature` tinyint( 1 ) NOT NULL default '0';


Falko pointed out that Roundcube uses ispconfig's db, which is the default one for me, db_ispconfig.

I looked inside db_ispconfig, but i cannot find a table called identities.

I also looked at the current installation of roundcube, in:

/home/admispconfig/ispconfig/web/roundcubemail/config/db.inc.php

and found the lines:

$rcmail_config['db_dsnw'] = 'sqlite://./sqlite.db?mode=0646';
// postgres example: 'pgsql://roundcubepass@localhost/roundcubemail';
// sqlite example: 'sqlite://./sqlite.db?mode=0646';

which means roundcube is using the sqlite database. I am confused now. Which database should I update? and what does this db store anyway?

Thanks,
aqua

[till]

You will have to update the sqlite database.

[aqua]

I tried updating the sqlite database in order for the new roundcube version to work work with the current release of roundcube. The original packager of this package used sqlite, not sqlite3. So excuting the above SQL statement didn't work.

I didn't want to use sqlite or sqlite3, because I am much more comfortable with mysql. I will document here what i did to convert my sqlite database to mysql, so that myself and my customer don't lose our contacts, etc. This has another advantage also, which is that you don't accidently over wright you database when you upgrade, and you could use tools like phpmyadmin to administer your database.

In theory an SQL dump from one database should be easily imported into another, but that is not the case.

1) Find your sqlite database, which lives in /home/admispconfig/ispconfig/web/roundcubemail
2) Install sqlite. sudo apt-get install sqlite
3) Dump the SQL, but only the insert-statements: sqlite sqlite.db .dump | grep 'INSERT INTO' > sql.dump That should give you a file with bunch of insert statement. Now you have all your data.
4) Look in the current roundcube directory. This should be a folder call SQL. In it should be a file called mysql.initial.sql. This file contains the information schema for mysql.
5) Create a new database from the ispconfig panel. Using phpmyadmin, upload mysql.initial.sql. This should setup your database.
6) Also using phpmyadmin, upload your sql.dump file. Now your database should contain all your data. I did have some difficulty with this step though, some lines from INSERT INTO identities lines would refuse to be inserted, because they would be missing a ') from the end of the line. If phpmyadmin throws and error, find the line and fix the mistake. There is no harm from reuploading the file again, ie your data won't be duplicated.

7) Until now, we haven't made any drastic changes to your roundcube installation. But we will, so make backups, especially your sqlite.db file.


tbc...

[till]

That looks fine so far. Be aware that apt-get install sqlite will work only on debian and ubuntu.