diff options
| -rw-r--r-- | db/create.sql | 41 | ||||
| -rw-r--r-- | db/models.py | 121 | ||||
| -rwxr-xr-x | monkey/action.py | 135 |
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) |