Script for adding problems to DB, update readme

3 files changed, 138 insertions, 48 deletions

diff --git a/readme.md b/readme.md
new file mode 100644
index 0000000..6f891f2
--- /dev/null
+++ b/readme.md
@@ -0,0 +1,53 @@
+Deployment
+==========
+
+Install the following packages:
+
+  - apache2
+  - python3-falcon
+  - python3-ply
+  - python3-psycopg2
+  - python3-termcolor
+  - python3-waitress
+  - swi-prolog-nox
+
+Settings:
+
+  - point webroot to codeq-web
+  - set up reverse proxy for /svc/ to wsgi server:
+      ProxyPass /svc/ http://localhost:8082/
+      ProxyPassReverse /svc/ http://localhost:8082/
+  - set _path_prefix in server.problems
+  - set DB settings in db
+
+Running:
+
+  - run prolog/runner/main.pl
+  - run python/runner/main.py
+  - run daemon.py
+  - run wsgi_server.py
+
+Misc.
+=====
+
+Add a new problem to the database by running
+
+    python -m scripts.add_problem
+
+in the toplevel directory. This will ask for language and problem group (groups
+can be shared between languages, e.g. Introduction can be used for both Prolog
+and Python). The script will print the ID and path for the new problem.
+
+You can test stuff in a python interpreter:
+
+    >>> import server
+    >>> session = server.user_session.authenticate_and_create_session(<user>, <pass>)
+    >>> session.get_problem_data('python', 'introduction', 'fahrenheit_to_celsius')
+
+    >>> python = session.get_python()
+    >>> python.test(231, 180, '''print('-17.7')''')
+    >>> python.hint(231, 180, '''print('-17.7')''')
+
+    >>> prolog = session.get_prolog()
+    >>> prolog.test(231, 96, 'sister(X,Y):- parent(P,X), parent(P,Y), female(X).')
+    >>> prolog.hint(231, 96, 'sister(X,Y):- parent(P,X), parent(P,Y), female(X).')
diff --git a/readme.txt b/readme.txt
deleted file mode 100644
index c901a5a..0000000
--- a/readme.txt
+++ /dev/null
@@ -1,48 +0,0 @@
-Deploy
-======
-
-Requirements
-------------
-
-- apache2
-- python3-falcon
-- python3-ply
-- python3-psycopg2
-- python3-termcolor
-- python3-waitress
-- swi-prolog-nox
-
-Settings
---------
-
-- copy files from codeq-web to webroot
-- set up reverse proxy for /svc/ to wsgi server
-    ProxyPass /svc/ http://localhost:8082/
-    ProxyPassReverse /svc/ http://localhost:8082/
-- set _path_prefix in server.problems
-- set DB settings in db
-
-Run
----
-
-- start prolog/runner/main.pl
-- start daemon.py
-- start wsgi_server.py
-
-
-Debugging
-=========
-
-Some examples to run in a python interpreter:
-
->>> import server
->>> session = server.user_session.authenticate_and_create_session(<user>, <pass>)
->>> session.get_problem_data('python', 'introduction', 'fahrenheit_to_celsius')
-
->>> python = session.get_python()
->>> python.test(231, 180, '''print('-17.7')''')
->>> python.hint(231, 180, '''print('-17.7')''')
-
->>> prolog = session.get_prolog()
->>> prolog.test(231, 96, 'sister(X,Y):- parent(P,X), parent(P,Y), female(X).')
->>> prolog.hint(231, 96, 'sister(X,Y):- parent(P,X), parent(P,Y), female(X).')
diff --git a/scripts/add_problem.py b/scripts/add_problem.py
new file mode 100755
index 0000000..d8462cf
--- /dev/null
+++ b/scripts/add_problem.py
@@ -0,0 +1,85 @@
+#!/usr/bin/python3
+# coding=utf-8
+
+import db
+from .utils import filenamefy
+
+conn = db.get_connection()
+try:
+    cur = conn.cursor()
+    try:
+        new_lidentifier = None
+        # Get or add language.
+        cur.execute('select id, name, identifier from language order by id asc')
+        languages = cur.fetchall()
+        print('Languages:')
+        for lid, lname, lidentifier in languages:
+            print('  {}: {}'.format(lid, lname))
+
+        new_lid = input("Enter language ID or 'n' for new): ")
+        if new_lid == 'n':
+            new_lname = input('Enter name of the new language: ')
+            new_lidentifier = filenamefy(new_lname)
+            cur.execute('insert into language (name, identifier) values (%s, %s) returning id',
+                        (new_lname, new_lidentifier))
+            new_lid = cur.fetchone()[0]
+            print('Added new language "{}" with ID {} and identifier {}'.format(
+                new_lname, new_lid, new_lidentifier))
+        else:
+            new_lid = int(new_lid)
+            for lid, lname, lidentifier in languages:
+                print(lid, lname, lidentifier)
+                if lid == new_lid:
+                    new_lidentifier = lidentifier
+                    break
+        if new_lidentifier is None:
+            raise Exception('Language with ID {} does not exist'.format(new_lid))
+        print('Selected langauge {}'.format(new_lid))
+        print()
+
+        # Get or add problem group.
+        new_gidentifier = None
+        cur.execute('select id, name, identifier from problem_group order by id asc')
+        groups = cur.fetchall()
+        print('Problem groups:')
+        for gid, gname, gidentifier in groups:
+            print('  {}: {}'.format(gid, gname))
+        new_gid = input("Enter problem group ID or 'n' for new): ")
+        if new_gid == 'n':
+            new_gname = input('Enter name of the new problem group: ')
+            new_gidentifier = filenamefy(new_gname)
+            cur.execute('insert into problem_group (name, identifier) values (%s, %s) returning id',
+                        (new_gname, new_gidentifier))
+            new_gid = cur.fetchone()[0]
+            print('Added new problem group "{}" with ID {} and identifier {}'.format(
+                new_gname, new_gid, new_gidentifier))
+        else:
+            new_gid = int(new_gid)
+            for gid, gname, gidentifier in groups:
+                if gid == new_gid:
+                    new_gidentifier = gidentifier
+                    break
+        if new_gidentifier is None:
+            raise Exception('Group with ID {} does not exist'.format(new_gid))
+        print('Selected problem group {}'.format(new_gid))
+        print()
+
+        # Add problem.
+        new_pname = input('Enter name of the new problem: ')
+        new_pidentifier = filenamefy(new_pname)
+        cur.execute('insert into problem (language_id, problem_group_id, name, identifier, is_visible) values (%s, %s, %s, %s, %s) returning id',
+                    (new_lid, new_gid, new_pname, new_pidentifier, True))
+        new_pid = cur.fetchone()[0]
+        print('Added new problem "{}" with ID {} and identifier {}'.format(
+            new_pname, new_pid, new_pidentifier))
+        print('Data files should be placed in "{}/problems/{}/{}"'.format(
+            new_lidentifier, new_gidentifier, new_pidentifier))
+
+    finally:
+        cur.close()
+    conn.commit()
+except:
+    conn.rollback()
+    raise
+finally:
+    db.return_connection(conn)