summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorAleš Smodiš <aless@guru.si>2015-08-13 16:51:14 +0200
committerAleš Smodiš <aless@guru.si>2015-08-13 16:51:14 +0200
commitc7820c61e376774abd65bb1c13ea12239ce87be4 (patch)
tree845c6da971844c7b5344318ecaefdbf899ee4866
parent7c60e736e514ffb81b12f5ccd45e36c8fcdcaffa (diff)
Initial PostgreSQL data model: codeq_user and solution tables.
Converted the action.py to use the new model.
-rw-r--r--db/create.sql41
-rw-r--r--db/models.py121
-rwxr-xr-xmonkey/action.py135
3 files changed, 240 insertions, 57 deletions
diff --git a/db/create.sql b/db/create.sql
new file mode 100644
index 0000000..5b3fc8c
--- /dev/null
+++ b/db/create.sql
@@ -0,0 +1,41 @@
+/* initial (server preparatory) statements:
+ create user codeq with password 'c0d3q';
+ create user vladar with password 'vl4d4r' superuser createdb createrole;
+ * to create the database (logged in as vladar):
+ create database codeq with encoding 'UTF8' template template0;
+ \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;
+ */
+
+create sequence codeq_user_id_seq;
+create sequence solution_id_seq;
+
+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,
+ email varchar(254) not null,
+ is_superuser bool not null,
+ is_staff bool not null,
+ is_active bool not null,
+ date_joined timestamp not null,
+ last_login timestamp not null,
+ constraint codeq_user_pk primary key (id),
+ constraint codeq_user_uq1 unique (username)
+);
+
+create table solution (
+ id integer not null default nextval('solution_id_seq'),
+ done bool not null,
+ content text not null,
+ problem_id integer not null,
+ codeq_user_id integer not null,
+ trace jsonb not null default '[]'::jsonb,
+ constraint solution_pk primary key (id),
+ constraint solution_fk1 foreign key (codeq_user_id) references codeq_user (id) on delete cascade on update cascade
+);
diff --git a/db/models.py b/db/models.py
new file mode 100644
index 0000000..994e819
--- /dev/null
+++ b/db/models.py
@@ -0,0 +1,121 @@
+import os, collections, psycopg2, json
+
+_conn = None # the database connection
+_host = None # the database hostname/IP
+_port = None # the database port number
+_database = None # the name of the database
+_username = None # the username to access the database
+_password = None # the password to access the database
+
+class CodeqUser(collections.namedtuple('CodeqUser', ['id', 'username', 'password', 'first_name', 'last_name', 'email', 'is_superuser', 'is_staff', 'is_active', 'date_joined', 'last_login'])):
+ __sql_prefix = 'select id, username, password, first_name, last_name, email, is_superuser, is_staff, is_active, date_joined, last_login from codeq_user'
+
+ @staticmethod
+ def get(**kwargs):
+ return _general_get(kwargs, CodeqUser, CodeqUser.__sql_prefix)
+
+ @staticmethod
+ def list():
+ return _general_list(CodeqUser, CodeqUser.__sql_prefix)
+
+ @staticmethod
+ def filter(**kwargs):
+ return _general_filter(kwargs, CodeqUser, CodeqUser.__sql_prefix)
+
+# known as Attempt in the original code
+class Solution(collections.namedtuple('Solution', ['id', 'done', 'content', 'problem_id', 'codeq_user_id', 'trace'])):
+ __sql_prefix = 'select id, done, content, problem_id, codeq_user_id, trace::text from solution'
+ __row_conversion = lambda row: (row[0], row[1], row[2], row[3], row[4], json.loads(row[5]))
+
+ @staticmethod
+ def get(**kwargs):
+ return _general_get(kwargs, Solution, Solution.__sql_prefix, Solution.__row_conversion)
+
+ @staticmethod
+ def list():
+ return _general_list(Solution, Solution.__sql_prefix, Solution.__row_conversion)
+
+ @staticmethod
+ def filter(**kwargs):
+ return _general_filter(kwargs, Solution, Solution.__sql_prefix, Solution.__row_conversion)
+
+
+def _get_connection():
+ global _conn, _host, _port, _database, _username, _password
+ if _conn == None:
+ _conn = psycopg2.connect(host=_host, port=_port, database=_database, user=_username, password=_password)
+ return _conn
+
+def _no_row_conversion(row):
+ return row
+
+def _general_get(kwargs_dict, clazz, sql_select, row_conversion_fn=_no_row_conversion):
+ conditions = []
+ parameters = []
+ for field_name, field_value in kwargs_dict.items():
+ conditions.append(field_name + ' = %s')
+ parameters.append(field_value)
+ if len(conditions) == 0:
+ return None
+ conn = _get_connection()
+ cur = conn.cursor('crsr1') # a named cursor: scrolling is done on the server
+ cur.arraysize = 1 # scroll unit in the number of rows
+ try:
+ cur.execute(sql_select + ' where ' + ' and '.join(conditions), parameters)
+ row = cur.fetchone()
+ if row:
+ return clazz(*row_conversion_fn(row))
+ return None
+ finally:
+ cur.close()
+
+def _general_filter(kwargs_dict, clazz, sql_select, row_conversion_fn=_no_row_conversion):
+ conditions = []
+ parameters = []
+ for field_name, field_value in kwargs_dict.items():
+ conditions.append(field_name + ' = %s')
+ parameters.append(field_value)
+ if len(conditions) == 0:
+ return _general_list(clazz, sql_select)
+ conn = _get_connection()
+ cur = conn.cursor('crsr2') # a named cursor: scrolling is done on the server
+ cur.arraysize = 10000 # scroll unit in the number of rows
+ try:
+ cur.execute(sql_select + ' where ' + ' and '.join(conditions) + ' order by id', parameters)
+ result = []
+ row = cur.fetchone()
+ while row:
+ result.append(clazz(*row_conversion_fn(row)))
+ row = cur.fetchone()
+ return result
+ finally:
+ cur.close()
+
+def _general_list(clazz, sql_select, row_conversion_fn=_no_row_conversion):
+ conn = _get_connection()
+ cur = conn.cursor('crsr3') # a named cursor: scrolling is done on the server
+ cur.arraysize = 10000 # scroll unit in the number of rows
+ try:
+ cur.execute(sql_select + ' order by id')
+ result = []
+ row = cur.fetchone()
+ while row:
+ result.append(clazz(*row_conversion_fn(row)))
+ row = cur.fetchone()
+ return result
+ finally:
+ cur.close()
+
+def init():
+ global _host, _port, _database, _username, _password
+ _host = os.environ.get('CODEQ_DB_HOST') or 'localhost'
+ try:
+ _port = int(os.environ.get('CODEQ_DB_PORT')) or 5432
+ except:
+ _port = 5432
+ _database = os.environ.get('CODEQ_DB_DATABASE') or 'codeq'
+ _username = os.environ.get('CODEQ_DB_USER') or 'codeq'
+ _password = os.environ.get('CODEQ_DB_PASS') or 'c0d3q'
+
+if __name__ == '__main__':
+ init()
diff --git a/monkey/action.py b/monkey/action.py
index f47cbb8..0a6a7cb 100755
--- a/monkey/action.py
+++ b/monkey/action.py
@@ -47,6 +47,16 @@ class Action:
else:
return text
+_packet_action_map = {
+ 'ins': lambda packet, time, code: Action('insert', time, offset=packet['off'], text=packet['txt']),
+ 'rm': lambda packet, time, code: Action('remove', time, offset=packet['off'], text=code[packet['off']:packet['off']+packet['len']]),
+ 'slv': lambda packet, time, code: Action('solve', time, text=packet['qry']),
+ 'slva': lambda packet, time, code: Action('solve_all', time, text=packet['qry']),
+ 'nxt': lambda packet, time, code: Action('next', time),
+ 'stp': lambda packet, time, code: Action('stop', time),
+ 'tst': lambda packet, time, code: Action('test', time, total=packet['tot'], passed=packet['pas']),
+ 'hnt': lambda packet, time, code: Action('hint', time)
+}
# parse log from database into a list of actions, cleaning up some fluff.
# ignore non-text actions (queries and tests)
def parse(data):
@@ -58,50 +68,58 @@ def parse(data):
time = 0
code = ''
- i = 0
- while i < len(data):
- # parse one action
- type = data[i]
- i += 1
- dt = int(((data[i] << 8) + (data[i+1])) * 100.0)
- time += dt
- i += 2
- if type == 1: # insert
- offset = (data[i] << 8) + data[i+1]
- i += 2
- length = (data[i] << 8) + data[i+1]
- i += 2
- text = data[i:i+length].decode()
- i += length
- action = Action('insert', time, offset=offset, text=text)
- elif type == 2: # remove
- offset = (data[i] << 8) + data[i+1]
- i += 2
- length = (data[i] << 8) + data[i+1]
- i += 2
- text = code[offset:offset+length]
- action = Action('remove', time, offset=offset, text=text)
- elif type == 3 or type == 4: # solve / solve all
- length = (data[i] << 8) + data[i+1]
- i += 2
- query = data[i:i+length].decode()
- i += length
- act_type = 'solve' + ('_all' if type == 4 else '')
- action = Action(act_type, time, text=query)
- elif type == 5: # next solution
- action = Action('next', time)
- elif type == 7: # stop/end
- action = Action('stop', time)
- elif type == 8: # test
- total = data[i]
- i += 1
- passed = data[i]
- i += 1
- action = Action('test', time, total=total, passed=passed)
- elif type == 9: # hint
- action = Action('hint', time)
- else:
- # unsupported action type
+# i = 0
+# while i < len(data):
+# # parse one action
+# type = data[i]
+# i += 1
+# dt = int(((data[i] << 8) + (data[i+1])) * 100.0)
+# time += dt
+# i += 2
+# if type == 1: # insert
+# offset = (data[i] << 8) + data[i+1]
+# i += 2
+# length = (data[i] << 8) + data[i+1]
+# i += 2
+# text = data[i:i+length].decode()
+# i += length
+# action = Action('insert', time, offset=offset, text=text)
+# elif type == 2: # remove
+# offset = (data[i] << 8) + data[i+1]
+# i += 2
+# length = (data[i] << 8) + data[i+1]
+# i += 2
+# text = code[offset:offset+length]
+# action = Action('remove', time, offset=offset, text=text)
+# elif type == 3 or type == 4: # solve / solve all
+# length = (data[i] << 8) + data[i+1]
+# i += 2
+# query = data[i:i+length].decode()
+# i += length
+# act_type = 'solve' + ('_all' if type == 4 else '')
+# action = Action(act_type, time, text=query)
+# elif type == 5: # next solution
+# action = Action('next', time)
+# elif type == 7: # stop/end
+# action = Action('stop', time)
+# elif type == 8: # test
+# total = data[i]
+# i += 1
+# passed = data[i]
+# i += 1
+# action = Action('test', time, total=total, passed=passed)
+# elif type == 9: # hint
+# action = Action('hint', time)
+# else:
+# # unsupported action type
+# continue
+
+ for packet in data:
+ try:
+ time += packet['dt']
+ action = _packet_action_map[packet['typ']](packet, time, code)
+ except:
+ # ignore any errors while decoding a packet
continue
# skip normalization if this is the first action
@@ -232,30 +250,33 @@ def compress(actions):
# some sample code
if __name__ == '__main__':
- import os
- import django
- os.environ['DJANGO_SETTINGS_MODULE'] = 'webmonkey.settings'
- django.setup()
+ import sys, os.path
+ sys.path.append(os.path.dirname(os.path.realpath(__file__)) + '/..') # the parent directory is the app directory
+# import django
+# os.environ['DJANGO_SETTINGS_MODULE'] = 'webmonkey.settings'
+# django.setup()
- from tutor.models import Problem, Attempt
+ import db.models
+ db.models.init()
+ from db.models import Solution
# print all problem ids
- print('problems:')
- for problem in Problem.objects.all():
- print(' {}\t{}'.format(problem.pk, problem.name))
- print()
+# print('problems:')
+# for problem in Problem.objects.all():
+# print(' {}\t{}'.format(problem.pk, problem.name))
+# print()
pid = input('enter problem id: ')
- problem = Problem.objects.get(pk=pid)
+# problem = Problem.objects.get(pk=pid)
# print all attempt ids for the selected problem
print('users solving problem ' + str(pid) + ':')
- attempts = Attempt.objects.filter(problem=problem)
- print(', '.join([str(attempt.user_id) for attempt in attempts]))
+ attempts = Solution.filter(problem_id=pid)
+ print(', '.join([str(attempt.codeq_user_id) for attempt in attempts]))
print()
uid = input('enter user id: ')
- attempt = Attempt.objects.get(problem_id=pid, user_id=uid)
+ attempt = Solution.get(problem_id=pid, codeq_user_id=uid)
try:
actions = parse(attempt.trace)