[Pkg-roundcube-maintainers] Bug#688634: roundcube sqlite -> mysql transition hint

[Chris Knadle]

I'm also using roundcube-sqlite, so I'm going to have to deal with the 
transition to either roundcube-mysql or roundcube-pgsql.  I found some hints 
from someone that did the transition from sqlite -> mysql back in 2007:

   http://www.howtoforge.com/forums/showpost.php?p=70929&postcount=3

Below I'm going to make some notes in []'s on indented lines to update a 
couple of the commands based on what I see.

------------------------------------------------------------------------

7th May 2007, 07:46
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

  [For Debian Squeeze the roundcube sqlite database is in:
   /var/lib/dbconfig-common/sqlite/roundcube/roundcube]

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.

   [sqlite roundcube .dump | fgrep 'INSERT INTO' > sql.dump]

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.

   [s/sqlite.db/roundcube/]

tbc... 

------------------------------------------------------------------------

  -- Chris

--
Chris Knadle
Chris.Knadle at coredump.us