fsql.py

#!/usr/bin/env python

# [ https://gist.github.com/jcarbaugh/100651/download# ]

# Convert a mysql dump into a sqlite-compatible format.
# I wrote this for just one script... no guarantees that it will work with others...
# python fsql.py < mysqldump.sql > readyforsqlite.sql

import re
import sys

content = sys.stdin.read()

print >>sys.stderr, "..read;"

print >>sys.stderr, "..replacing unused commands/statements .."

# unused commands
# COMMAND_RE = re.compile(r'(^(SET|LOCK|UNLOCK).*?;$)|((,\s*?)?^\s*?(KEY).*?$)|(COLLATE utf8_unicode_ci)|(AUTO_INCREMENT)', re.I | re.M | re.S)
COMMAND_RE = re.compile(r''' (^(SET|LOCK|UNLOCK).*?;$)
                           | ((,\s*?)?^\s*?(UNIQUE\s+)?(KEY).*?$)
                           | (COLLATE\s+utf8_unicode_ci)
                           | (AUTO_INCREMENT) ''', re.M | re.S | re.X )

content = COMMAND_RE.sub('', content)

print >>sys.stderr, ".. patches and corrections .."

# unused commands
# COMMAND_RE = re.compile(r'^(SET).*?;\n$', re.I | re.M | re.S)
"""
COMMAND_RE = re.compile(r"\\'", re.I | re.M | re.S) # "\'" => "''"
content = COMMAND_RE.sub("''", content)
"""
content = content.replace( r"\'", "''" )

COMMAND_RE = re.compile(r"COMMENT\s+.*?(,?)$", re.M | re.S) # "\'" => "''"
content = COMMAND_RE.sub(r"\1", content)


print >>sys.stderr, "..removing unused constraints .."

# table constraints
TCONS_RE = re.compile(r'\)(\s*(CHARSET|DEFAULT|ENGINE)(=.*?)?\s*)+;', re.M | re.S)
content = TCONS_RE.sub(');', content)

print >>sys.stderr, "..editing multiple value replacement .."

# insert multiple values
# the most tricky / dangerous part (may fail; simple "state machine" parser will be the way to go)
INSERTVALS_RE = re.compile(r'^(INSERT INTO.*?VALUES)\s*\((.*?)\);$', re.M | re.S)
INSERTVALS_SPLIT_RE = re.compile(r'\)\s*,\s*\(', re.I | re.M | re.S)

def insertvals_replacer(match):
    insert, values = match.groups()
    replacement = []
    for vals in INSERTVALS_SPLIT_RE.split(values):
        replacement.append( '%s (%s);' % (insert, vals) )
    return '\n'.join( replacement )

content = INSERTVALS_RE.sub(insertvals_replacer, content)

print >>sys.stderr, "..writing output .."

# [ http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html ]
print 'BEGIN TRANSACTION;'
# write results to stdout
sys.stdout.write(content)
print 'END TRANSACTION;'