#!/usr/bin/python3 # coding=utf-8 import sys, json, sqlite3, psycopg2 from utils import filenamefy 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 _no_conversion(row): return row, None def copy_table(src_qry, dst_qry, conversion_fn, cleanup_fn, sequence): conversion_fn = conversion_fn or _no_conversion 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] != 0, row[5] != 0, row[6], row[7]), 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 def convert_problem_group(row): return (row[0], row[1], filenamefy(row[1])), None def convert_problem(row): return (row[0], row[1], row[2], row[3] != 0, filenamefy(row[2])), None if __name__ == '__main__': sqc = sqlite3.connect('db.sqlite3') # pgc = psycopg2.connect(host='localhost', port=5432, database='codeq', user='codeq', password='c0d3q') pgc = psycopg2.connect(host='172.31.200.40', port=5432, database='codeq', user='codeq', password='c0d3q') pgc.autocommit = False try: log('Creating language prolog') cur = pgc.cursor() try: cur.execute("insert into language (name, identifier) values ('Prolog', 'prolog') returning id") _language_prolog_id = cur.fetchone()[0] finally: cur.close() log('Copying auth_group -> user_group') copy_table( 'select id, name from auth_group', 'insert into user_group (id, name) values (%s, %s)', None, None, 'user_group_id_seq') log('Copying auth_user -> codeq_user') copy_table( 'select id, username, password, email, is_superuser, is_active, date_joined, last_login from auth_user order by id', 'insert into codeq_user (id, username, password, email, is_admin, is_active, date_joined, last_login) values (%s, %s, %s, %s, %s, %s, %s, %s)', convert_auth_user, None, 'codeq_user_id_seq') log ('Copying auth_user_groups -> user_in_group') copy_table( 'select user_id, group_id from auth_user_groups', 'insert into user_in_group (codeq_user_id, user_group_id) values (%s, %s)', None, None, None) log('Copying tutor_group -> problem_group') copy_table( 'select id, name from tutor_group', 'insert into problem_group (id, name, identifier) values (%s, %s, %s)', convert_problem_group, None, 'problem_group_id_seq') log('Copying tutor_problem -> problem') copy_table( 'select id, group_id, name, visible from tutor_problem', 'insert into problem (id, language_id, problem_group_id, name, identifier) values (%s, ' + str(_language_prolog_id) + ', %s, %s, %s)', convert_problem, None, 'problem_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()