From df70e61e29b14dac8a0c03c5b4c9fc6321fa7e48 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Ale=C5=A1=20Smodi=C5=A1?= Date: Wed, 19 Aug 2015 19:05:14 +0200 Subject: New database tables and data imports: user_group, user_in_group, language, problem_group, problem. --- db/create.sql | 58 ++++++++++++-- export_problems.py | 108 -------------------------- scripts/export_problems.py | 105 +++++++++++++++++++++++++ scripts/sqlite_to_pg.py | 189 +++++++++++++++++++++++++++++++++++++++++++++ scripts/utils.py | 8 ++ sqlite_to_pg.py | 145 ---------------------------------- 6 files changed, 354 insertions(+), 259 deletions(-) delete mode 100644 export_problems.py create mode 100644 scripts/export_problems.py create mode 100644 scripts/sqlite_to_pg.py create mode 100644 scripts/utils.py delete mode 100644 sqlite_to_pg.py diff --git a/db/create.sql b/db/create.sql index 5b3fc8c..39eab20 100644 --- a/db/create.sql +++ b/db/create.sql @@ -6,22 +6,30 @@ \c codeq \i create.sql grant all privileges on all tables in schema public to codeq; - grant all privileges on codeq_user_id_seq to codeq; - grant all privileges on solution_id_seq to codeq; + grant all privileges on all sequences in schema public to codeq; */ +create sequence user_group_id_seq; create sequence codeq_user_id_seq; +create sequence language_id_seq; +create sequence problem_group_id_seq; +create sequence problem_id_seq; create sequence solution_id_seq; +create table user_group ( + id integer not null default nextval('user_group_id_seq'), + name varchar(80) not null, + constraint user_group_pk primary key (id), + constraint user_group_uq1 unique (name) +); + create table codeq_user ( id integer not null default nextval('codeq_user_id_seq'), username varchar(30) not null, password varchar(128) not null, - first_name varchar(30) not null, - last_name varchar(30) not null, + name varchar(200), email varchar(254) not null, - is_superuser bool not null, - is_staff bool not null, + is_admin bool not null, is_active bool not null, date_joined timestamp not null, last_login timestamp not null, @@ -29,6 +37,44 @@ create table codeq_user ( constraint codeq_user_uq1 unique (username) ); +create table user_in_group ( + user_group_id integer not null, + codeq_user_id integer not null, + constraint user_in_group_pk primary key (user_group_id, codeq_user_id) +); + +create table language ( + id integer not null default nextval('language_id_seq'), + name varchar(30) not null, + identifier varchar(30) not null, + constraint language_pk primary key (id), + constraint language_uq1 unique (name), + constraint language_uq2 unique (identifier) +); + +create table problem_group ( + id integer not null default nextval('problem_group_id_seq'), + name varchar(100) not null, + identifier varchar(100) not null, + constraint group_pk primary key (id), + constraint group_uq1 unique (name), + constraint group_uq2 unique (identifier) +); + +create table problem ( + id integer not null default nextval('problem_id_seq'), + language_id integer not null, + problem_group_id integer not null, + name varchar(100) not null, + identifier varchar(100) not null, + is_visible bool not null default true, + constraint problem_pk primary key (id), + constraint problem_fk1 foreign key (language_id) references language (id) on delete no action on update cascade, + constraint problem_fk2 foreign key (problem_group_id) references problem_group (id) on delete no action on update cascade, + constraint problem_uq1 unique (language_id, problem_group_id, name), + constraint problem_uq2 unique (identifier) +); + create table solution ( id integer not null default nextval('solution_id_seq'), done bool not null, diff --git a/export_problems.py b/export_problems.py deleted file mode 100644 index 7191e74..0000000 --- a/export_problems.py +++ /dev/null @@ -1,108 +0,0 @@ -#!/usr/bin/python3 -# coding=utf-8 - -import sys, sqlite3, os, os.path, re - -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) - -re_filenamify = re.compile(r''' |\.|;|,|:|"|'|\*|\(|\)|\/|\\|&|%|\$|\#|!|\n|\r|\t|\|''', re.MULTILINE) -def filenamefy(name): - return '_'.join(re_filenamify.split(name)).lower().strip('_') - -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/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('_') + diff --git a/sqlite_to_pg.py b/sqlite_to_pg.py deleted file mode 100644 index 25059b2..0000000 --- a/sqlite_to_pg.py +++ /dev/null @@ -1,145 +0,0 @@ -#!/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() -- cgit v1.2.1