summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorAleš Smodiš <aless@guru.si>2015-08-13 16:55:10 +0200
committerAleš Smodiš <aless@guru.si>2015-08-13 16:55:10 +0200
commitb1388e4f6660c11e78498a530620b06e70ed7371 (patch)
treec1eb50c2f35820a59f54311f1b530c535daa9573
parentb07ff0ad629c93cbe8f22605e859471eeff6b0e7 (diff)
The conversion script SQLite -> PostgreSQL:
- auth_user -> codeq_user, - tutor_attempt -> solution.
-rw-r--r--sqlite_to_pg.py145
1 files changed, 145 insertions, 0 deletions
diff --git a/sqlite_to_pg.py b/sqlite_to_pg.py
new file mode 100644
index 0000000..25059b2
--- /dev/null
+++ b/sqlite_to_pg.py
@@ -0,0 +1,145 @@
+#!/usr/bin/python3
+# coding=utf-8
+
+import sys, json, sqlite3, psycopg2
+
+sqc = None # sqlite3 connection handle
+pgc = None # postgresql connection handle
+
+def log(line):
+ sys.stdout.write('{0}\n'.format(line))
+
+def _no_cleanup(row, data):
+ pass
+
+def copy_table(src_qry, dst_qry, conversion_fn, cleanup_fn, sequence):
+ cleanup_fn = cleanup_fn or _no_cleanup
+ src_cur = sqc.cursor()
+ try:
+ dst_cur = pgc.cursor()
+ try:
+ row = None
+ for row in src_cur.execute(src_qry):
+ new_row, cleanup_data = conversion_fn(row)
+ dst_cur.execute(dst_qry, new_row)
+ cleanup_fn(new_row, cleanup_data)
+ if sequence and row:
+ dst_cur.execute("select setval('{0}', {1})".format(sequence, row[0]))
+ pgc.commit()
+ except:
+ pgc.rollback()
+ raise
+ finally:
+ dst_cur.close()
+ finally:
+ src_cur.close()
+
+def convert_auth_user(row):
+ log('Converting auth_user id={0}'.format(row[0]))
+ return (row[0], row[1], row[2], row[3], row[4], row[5], row[6] != 0, row[7] != 0, row[8] != 0, row[9], row[10]), None
+
+
+def _handle_trace_insert(trace, i):
+ length = (trace[i+2] << 8) + trace[i+3]
+ js = {
+ 'typ': 'ins',
+ 'off': (trace[i] << 8) + trace[i+1],
+ 'txt': trace[i+4:i+4+length].decode()
+ }
+ return js, i + 4 + length
+
+def _handle_trace_remove(trace, i):
+ js = {
+ 'typ': 'rm',
+ 'off': (trace[i] << 8) + trace[i+1],
+ 'len': (trace[i+2] << 8) + trace[i+3]
+ }
+ return js, i + 4
+
+def _handle_trace_solve(trace, i):
+ length = (trace[i] << 8) + trace[i+1]
+ js = {
+ 'typ': 'slv',
+ 'qry': trace[i+2:i+2+length].decode()
+ }
+ return js, i + 2 + length
+
+def _handle_trace_solve_all(trace, i):
+ length = (trace[i] << 8) + trace[i+1]
+ js = {
+ 'typ': 'slva',
+ 'qry': trace[i+2:i+2+length].decode()
+ }
+ return js, i + 2 + length
+
+def _handle_trace_test(trace, i):
+ js = {
+ 'typ': 'tst',
+ 'tot': trace[i],
+ 'pas': trace[i+1]
+ }
+ return js, i + 2
+
+def _handle_trace_next(trace, i):
+ return {'typ': 'nxt'}, i
+
+def _handle_trace_stop(trace, i):
+ return {'typ': 'stp'}, i
+
+def _handle_trace_hint(trace, i):
+ return {'typ': 'hnt'}, i
+
+_trace_handler = {
+ 1: _handle_trace_insert, # insert
+ 2: _handle_trace_remove, # remove
+ 3: _handle_trace_solve, # solve
+ 4: _handle_trace_solve_all, # solve all
+ 5: _handle_trace_next, # next solution
+ 7: _handle_trace_stop, # stop/end
+ 8: _handle_trace_test, # test
+ 9: _handle_trace_hint # hint
+}
+
+def convert_trace(trace, i):
+ type = trace[i]
+ i += 1
+ dt = int(((trace[i] << 8) + (trace[i+1])) * 100.0)
+ i += 2
+ handler = _trace_handler.get(type)
+ if handler:
+ js, i = handler(trace, i)
+ js['dt'] = dt
+ return json.dumps(js), i
+ return None, i
+
+def convert_attempt(row):
+ log('Converting tutor_attempt id={0}'.format(row[0]))
+ old_trace = bytes(row[5])
+ new_trace = []
+ n = len(old_trace)
+ i = 0
+ while i < n:
+ trace, i = convert_trace(old_trace, i)
+ if trace:
+ new_trace.append(trace)
+ return (row[0], row[1] != 0, row[2], row[3], row[4], '[' + ','.join(new_trace) + ']'), None
+
+if __name__ == '__main__':
+ sqc = sqlite3.connect('db.sqlite3')
+ pgc = psycopg2.connect(host='localhost', port=5432, database='codeq', user='codeq', password='c0d3q')
+ pgc.autocommit = False
+ try:
+ log('Copying auth_user -> codeq_user')
+ copy_table(
+ 'select id, username, password, first_name, last_name, email, is_superuser, is_staff, is_active, date_joined, last_login from auth_user order by id',
+ 'insert into codeq_user (id, username, password, first_name, last_name, email, is_superuser, is_staff, is_active, date_joined, last_login) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
+ convert_auth_user, None, 'codeq_user_id_seq')
+
+ log('Copying tutor_attempt -> solution')
+ copy_table(
+ 'select id, done, content, problem_id, user_id, trace from tutor_attempt order by id',
+ 'insert into solution (id, done, content, problem_id, codeq_user_id, trace) values (%s, %s, %s, %s, %s, %s)',
+ convert_attempt, None, 'solution_id_seq')
+ finally:
+ sqc.close()
+ pgc.close()