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

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('_')
+