New database tables and data imports: user_group, user_in_group, language, problem_group, problem.

3 files changed, 302 insertions, 0 deletions

diff --git a/scripts/export_problems.py b/scripts/export_problems.py
new file mode 100644
index 0000000..fc2c2cf
--- /dev/null
+++ b/scripts/export_problems.py
@@ -0,0 +1,105 @@
+#!/usr/bin/python3
+# coding=utf-8
+
+import sys, sqlite3, os, os.path
+from .utils import filenamefy
+
+base_dir = '/tmp' # where to create the directory structure
+
+if not os.path.isdir(base_dir):
+    os.mkdir(base_dir)
+
+prolog_dir = base_dir + '/prolog'
+prolog_facts_dir = prolog_dir + '/facts'
+prolog_groups_dir = prolog_dir + '/problems'
+if not os.path.isdir(prolog_dir):
+    os.mkdir(prolog_dir)
+if not os.path.isdir(prolog_facts_dir):
+    os.mkdir(prolog_facts_dir)
+if not os.path.isdir(prolog_groups_dir):
+    os.mkdir(prolog_groups_dir)
+
+def log(line):
+    sys.stdout.write('{0}\n'.format(line))
+
+fact_map = {}
+group_map = {}
+
+sqc = sqlite3.connect('db.sqlite3') # sqlite3 connection handle
+try:
+    cur = sqc.cursor()
+    try:
+        for row in cur.execute('select id, name, facts from tutor_library'):
+            facts_module = filenamefy(row[1])
+            facts_filename = prolog_facts_dir + '/' + facts_module + '.py'
+            fact_map[row[0]] = facts_module
+            f = open(facts_filename, 'w', encoding='UTF-8', errors='replace', newline='')
+            try:
+                f.write("""\
+# coding=utf-8
+
+id = {0}
+
+name = '{1}'
+
+facts = '''\\
+{2}'''
+""".format(row[0], row[1], '\n'.join(row[2].split('\r\n'))))
+            finally:
+                f.close()
+            log('facts module: {0}'.format(facts_filename))
+
+        for row in cur.execute('select id, name from tutor_group'):
+            group_dir = filenamefy(row[1])
+            group_dir_abs = prolog_groups_dir + '/' + group_dir
+            if not os.path.isdir(group_dir_abs):
+                os.mkdir(group_dir_abs)
+            group_map[row[0]] = group_dir
+            log('group dir: {0}'.format(group_dir_abs))
+
+        for row in cur.execute('select id, group_id, number, name, slug, details, solution, visible, library_id from tutor_problem'):
+            group_dir = group_map[row[1]]
+            name = row[3]
+            problem_dir = filenamefy(name)
+            problem_dir_abs = prolog_groups_dir + '/' + group_dir + '/' + problem_dir # contains all python modules for the problem
+            if not os.path.isdir(problem_dir_abs):
+                os.mkdir(problem_dir_abs)
+            facts_module = None if row[8] is None else fact_map.get(row[8])
+            visible = row[7]
+            f = open(problem_dir_abs + '/common.py', 'w', encoding='UTF-8', errors='replace', newline='')
+            try:
+                f.write("""\
+# coding=utf-8
+
+id = {0}
+group = '{1}'
+number = {2}
+visible = {3}
+facts = {4}
+
+solution = '''\\
+{5}'''
+""".format(row[0], group_dir, row[2], 'False' if visible == 0 else 'True', 'None' if facts_module is None else "'" + facts_module + "'", '\n'.join(row[6].split('\r\n'))))
+            finally:
+                f.close()
+            f = open(problem_dir_abs + '/en.py', 'w', encoding='UTF-8', errors='replace', newline='')
+            try:
+                f.write("""\
+# coding=utf-8
+
+id = {0}
+name = '{1}'
+slug = '{2}'
+
+description = '''\\
+{3}'''
+
+hint = {{}}
+""".format(row[0], name, row[4], '\n'.join(row[5].split('\r\n'))))
+            finally:
+                f.close()
+            log('problem: {0}'.format(problem_dir_abs))
+    finally:
+        cur.close()
+finally:
+    sqc.close()
\ No newline at end of file
diff --git a/scripts/sqlite_to_pg.py b/scripts/sqlite_to_pg.py
new file mode 100644
index 0000000..a5889d6
--- /dev/null
+++ b/scripts/sqlite_to_pg.py
@@ -0,0 +1,189 @@
+#!/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, is_visible, identifier) values (%s, ' + str(_language_prolog_id) + ', %s, %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()
diff --git a/scripts/utils.py b/scripts/utils.py
new file mode 100644
index 0000000..30bbde4
--- /dev/null
+++ b/scripts/utils.py
@@ -0,0 +1,8 @@
+# coding=utf-8
+
+import re
+
+_re_filenamify = re.compile(r''' |\.|;|,|:|"|'|\*|\(|\)|\/|\\|&|%|\$|\#|!|\n|\r|\t|\|''', re.MULTILINE)
+def filenamefy(name):
+    return '_'.join(_re_filenamify.split(name)).lower().strip('_')
+