Convert Trac DB, from SQLite to MySQL

This is a copy from the mailing list …

% echo ".dump" | sqlite trac.db > trac.sql
% mysql tracdb < trac.sql

There could be syntax errors that would have to be manually fixed in the file trac.sql.

1. Use trac-admin initenv to create the database structure
2. Use mysqldump —no-data to dump the database structure into trac.mysql.sql
3. Use sqlite .dump to dump the structure + data from sqlite into trac.sqlite.sql (unfortunately there's no option to only dump the data).
4. Remove all the structure ('create table' and 'create index') from trac.sqlite.sql You could use this simple perl script:

   $ cat cleansql
   #!/usr/bin/env perl
   
   while (<>) {
     $i = $i . $_;
   }
   
   $i =~ s/^CREATE.*?\);$//smgi;
   
   print $i;
   
   # NEOF
   
   $ ./cleansql trac.sqlite.sql >  trac.sqlite.sql.dataonly
   

5. Concatenate trac.mysql.sql and trac.sqlite.sql.dataonly into trac.sql

   cat trac.mysql.sql trac.sqlite.sql.dataonly > trac.sql  
   

---

An alternatice to the cleansql above (that works a bit better):

$ cat cleansql.py
#!/usr/bin/env python

import sys
import re

file = sys.stdin.read()
file = re.sub(r'(CREATE (TABLE|INDEX)[^;]*|COMMIT|BEGIN TRANSACTION);', '', file)
file = re.sub(r'INSERT INTO "([^"]+)"', lambda m: 'INSERT INTO `%s`' % m.groups(1), file)
sys.stdout.write(file)

$ cat trac.sqlite.sql | ./cleansql.py > trac.sqlite.sql.dataonly