[rt-users] Migrating from SQLite to MySQL

[David X. Glover]

On 26 Nov 2009, at 11:22, Dominic Hargreaves wrote:

> or just write a little filter to tweak things yourself (eg you could
> start by removing the "BEGIN TRANSACTION" and corresponding "COMMIT.."
> lines from the dump), then see what else breaks.

In the end, that's what we did. So, for anyone else who wants to do this:

1. Dump the SQLite database with the ".dump" command.
2. Run the script through the Python script will I'll paste below.
3. Run "rt-setup-database --action init" on the new server. (To create things like the Sessions table, which don't exist when you're using SQLite.)
4. Run the processed script through mysql.
5. Done.

Note that our script may not work with databases other than RT databases, because we took a couple of shortcuts.

Script follows:

#!/usr/bin/env python

import re
import fileinput
import sys

def this_line_is_useless(line):
    useless_es = [
        'BEGIN TRANSACTION',
        'COMMIT',
        'sqlite_sequence',
        'CREATE UNIQUE INDEX',            
        ]
    for useless in useless_es:
        if re.search(useless, line):
                        return True

def has_primary_key(line):
    return bool(re.search(r'PRIMARY KEY', line))

searching_for_end = False
for line in fileinput.input():
    if this_line_is_useless(line): continue

    # this line was necessary because ''); was getting
    # converted (inappropriately) to \');
    if re.match(r".*, ''\);", line):
        line = re.sub(r"''\);", r'``);', line)

    if re.match(r'^CREATE TABLE.*', line):
        searching_for_end = True

    m = re.search('CREATE TABLE ([a-zA-Z_]+) ', line)
    if m:
        (name,) = m.groups()
        sys.stderr.write('creating table %s\n'%name)
        line = "DROP TABLE IF EXISTS %(name)s;\nCREATE TABLE IF NOT EXISTS %(name)s(\n"
        line = line % dict(name=name)
    else:
        m = re.search('INSERT INTO "([a-zA-Z_]*)"(.*)', line)
        if m:
                        line = 'INSERT INTO %s%s\n' % m.groups()
                        line = line.replace('"', r'\"')
                        line = line.replace('"', "'")
    line = re.sub(r"([^'])'t'(.)", "\1THIS_IS_TRUE\2", line)
    line = line.replace('THIS_IS_TRUE', '1')
    line = re.sub(r"([^'])'f'(.)", "\1THIS_IS_FALSE\2", line)
    line = line.replace('THIS_IS_FALSE', '0')

    # Add auto_increment if it's not there since sqlite auto_increments ALL
    # primary keys
    if searching_for_end:
        if re.search(r"integer(?:\s+\w+)*\s*PRIMARY KEY(?:\s+\w+)*\s*,", line):
                line = line.replace("PRIMARY KEY", "PRIMARY KEY AUTO_INCREMENT")
        # replace " and ' with ` because mysql doesn't like quotes in CREATE commands
        if re.search(r'varchar.+DEFAULT', line):
            sys.stderr.write('Not changing ` for DEFAULT string: %s' % line)
        else:
            line = line.replace('"', '`').replace("'", '`')

    # And now we convert it back (see above)
    if re.match(r".*, ``\);", line):
        line = re.sub(r'``\);', r"'');", line)

    if searching_for_end and re.match(r'.*\);', line):
        searching_for_end = False

    if re.match(r"CREATE INDEX", line):
        line = re.sub('"', '`', line)


    print line,