[rt-users] Migrating from SQLite to MySQL

[David X. Glover]

On 27 Nov 2009, at 10:32, David X. Glover wrote:

> One (hopefully minor) problem left over. When attempting to create a
> new ticket in the migrated database, I'm getting the SQL error
> "Duplicate entry '0' for key 1" when trying to create the new record
> in the Tickets table.

Apologies for solving my own problem again, but the issue lay with the  
conversion script which I posted yesterday. We've solved it now, so  
here, for anyone interested, is the fixed version:

#!/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,  re.IGNORECASE ):
                line = re.sub("(?i)PRIMARY KEY", "PRIMARY KEY  
AUTO_INCREMENT", line)

        # 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,


-- 
David X. Glover - Macintosh IT Support
Physics @ University of Oxford
http://www-astro.physics.ox.ac.uk/~Glover/
Jabber/GTalk: davidcwg at jabber.ox.ac.uk