diff options
| -rw-r--r-- | db/create.sql | 58 | ||||
| -rw-r--r-- | scripts/export_problems.py (renamed from export_problems.py) | 7 | ||||
| -rw-r--r-- | scripts/sqlite_to_pg.py (renamed from sqlite_to_pg.py) | 52 | ||||
| -rw-r--r-- | scripts/utils.py | 8 |
4 files changed, 110 insertions, 15 deletions
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/scripts/export_problems.py index 7191e74..fc2c2cf 100644 --- a/export_problems.py +++ b/scripts/export_problems.py @@ -1,7 +1,8 @@ #!/usr/bin/python3 # coding=utf-8 -import sys, sqlite3, os, os.path, re +import sys, sqlite3, os, os.path +from .utils import filenamefy base_dir = '/tmp' # where to create the directory structure @@ -18,10 +19,6 @@ if not os.path.isdir(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)) diff --git a/sqlite_to_pg.py b/scripts/sqlite_to_pg.py index 25059b2..a5889d6 100644 --- a/sqlite_to_pg.py +++ b/scripts/sqlite_to_pg.py @@ -2,6 +2,7 @@ # coding=utf-8 import sys, json, sqlite3, psycopg2 +from utils import filenamefy sqc = None # sqlite3 connection handle pgc = None # postgresql connection handle @@ -12,7 +13,11 @@ def log(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: @@ -36,7 +41,7 @@ def copy_table(src_qry, dst_qry, conversion_fn, cleanup_fn, sequence): 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 + 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): @@ -124,17 +129,56 @@ def convert_attempt(row): 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='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, 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)', + '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', 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('_') + |