jraine/munkiserver — pull request #111 by treydock (2011-12-05 00:23:50+00:00)
Title: - Add mysql support

This commit enables MySQL support, related to issue #96. As for converting from SQLite to MySQL, I modified the python script from here, http://stackoverflow.com/a/1067151/395946.

These are the changes I made which may be specific to Munkiserver

```diff
# diff -u sqlite_to_mysql.py.orig sqlite_to_mysql.py
--- sqlite_to_mysql.py.orig 2011-12-04 18:19:53.091423341 -0600
+++ sqlite_to_mysql.py 2011-12-04 18:06:01.037416878 -0600
@@ -3,7 +3,7 @@
def main():
for line in fileinput.input():
process = False
- for nope in ('BEGIN TRANSACTION','COMMIT',
+ for nope in ('BEGIN TRANSACTION','COMMIT','PRAGMA foreign_keys=OFF',
'sqlite_sequence','CREATE UNIQUE INDEX'):
if nope in line: break
else:
@@ -16,6 +16,7 @@
CREATE TABLE IF NOT EXISTS %(name)s%(sub)s
'''
line = line % dict(name=name, sub=sub)
+ line = line.replace('"', '`')
else:
m = re.search('INSERT INTO "([a-z_]*)"(.*)', line)
if m:
@@ -27,6 +28,8 @@
line = re.sub(r"([^'])'f'(.)", r"\1THIS_IS_FALSE\2", line)
line = line.replace('THIS_IS_FALSE', '0')
line = line.replace('AUTOINCREMENT', 'AUTO_INCREMENT')
+ line = re.sub(r"text\ DEFAULT '(.*)',", r"text,", line)
+ line = re.sub(r"text\ DEFAULT [0-9],", r"text,", line)
print line,

main()

```

This is the full script I used to convert from SQlite to MySQL

```python
import re, fileinput

def main():
for line in fileinput.input():
process = False
for nope in ('BEGIN TRANSACTION','COMMIT','PRAGMA foreign_keys=OFF',
'sqlite_sequence','CREATE UNIQUE INDEX'):
if nope in line: break
else:
process = True
if not process: continue
m = re.search('CREATE TABLE "([a-z_]*)"(.*)', line)
if m:
name, sub = m.groups()
line = '''DROP TABLE IF EXISTS %(name)s;
CREATE TABLE IF NOT EXISTS %(name)s%(sub)s
'''
line = line % dict(name=name, sub=sub)
line = line.replace('"', '`')
else:
m = re.search('INSERT INTO "([a-z_]*)"(.*)', line)
if m:
line = 'INSERT INTO %s%s\n' % m.groups()
line = line.replace('"', r'\"')
line = line.replace('"', "'")
line = re.sub(r"([^'])'t'(.)", r"\1THIS_IS_TRUE\2", line)
line = line.replace('THIS_IS_TRUE', '1')
line = re.sub(r"([^'])'f'(.)", r"\1THIS_IS_FALSE\2", line)
line = line.replace('THIS_IS_FALSE', '0')
line = line.replace('AUTOINCREMENT', 'AUTO_INCREMENT')
line = re.sub(r"text\ DEFAULT '(.*)',", r"text,", line)
line = re.sub(r"text\ DEFAULT [0-9],", r"text,", line)
print line,

main()

```

Using that script I did the export conversion as follows (After creating necessary MySQL DB)

```bash
sqlite3 db/production.sqlite3 .dump > production.sqlite

python sqlite_to_mysql.py production.sqlite > production.mysql

mysql -u root munkiserver < production.mysql
```

My database is very small as my installation of Munkiserver is new, could use more testing on data sets that are much larger.