From 178da7b42b34b7d985d79432f31bf32810eadec2 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20Pu=C5=A1nik?= Date: Mon, 12 Oct 2015 18:22:10 +0200 Subject: get basic user statistics --- server/handlers.py | 10 +++++- server/user_session.py | 98 +++++++++++++++++++++++++++++++++++++------------- 2 files changed, 83 insertions(+), 25 deletions(-) (limited to 'server') diff --git a/server/handlers.py b/server/handlers.py index 0b3dd46..5439d19 100644 --- a/server/handlers.py +++ b/server/handlers.py @@ -276,6 +276,13 @@ class EndProblem(CodeqService): request.end() + +class GetUserStat(CodeqService): + def process(self, request): + stat = request.session.get_stat() + request.reply({'code': 0, 'stat': stat}) + + # maps actions to their handlers incoming_handlers = { 'create_session': CreateSession(), @@ -293,7 +300,8 @@ incoming_handlers = { 'update_settings': UpdateSettings(), 'test': Test(), 'load_problem': LoadProblem(), - 'end_problem': EndProblem() + 'end_problem': EndProblem(), + 'user_stat': GetUserStat() } diff --git a/server/user_session.py b/server/user_session.py index 6a994f3..b678cea 100644 --- a/server/user_session.py +++ b/server/user_session.py @@ -210,51 +210,101 @@ class UserSession(object): def update_solution(self, problem_id, trace, solution): if (trace is None) and (solution is None): return + with self._access_lock: + uid = self.uid + conn = db.get_connection() + try: + cur = conn.cursor() + try: + # TODO: convert to upsert with postgresql 9.5 to eliminate the small window where it's possible for more than one concurrent insert to execute + cur.execute('select id, trace, content from solution where codeq_user_id = %s and problem_id = %s for update', (uid, problem_id)) + row = cur.fetchone() + if row: + if row[1]: + new_trace = row[1] + if trace: + new_trace.extend(trace) + else: + new_trace = trace + new_solution = row[2] if solution is None else solution + cur.execute('update solution set content = %s, trace = %s where id = %s', (new_solution, psycopg2.extras.Json(new_trace), row[0])) + else: + # this is the first entry + cur.execute('insert into solution (done, content, problem_id, codeq_user_id, trace) values (%s, %s, %s, %s, %s)', (False, solution, problem_id, uid, psycopg2.extras.Json(trace))) + finally: + cur.close() + conn.commit() + except: + conn.rollback() + raise + finally: + db.return_connection(conn) + + def change_password(self, password): uid = self.get_uid() conn = db.get_connection() try: cur = conn.cursor() try: - # TODO: convert to upsert with postgresql 9.5 to eliminate the small window where it's possible for more than one concurrent insert to execute - cur.execute('select id, trace, content from solution where codeq_user_id = %s and problem_id = %s for update', (uid, problem_id)) - row = cur.fetchone() - if row: - if row[1]: - new_trace = row[1] - if trace: - new_trace.extend(trace) - else: - new_trace = trace - new_solution = row[2] if solution is None else solution - cur.execute('update solution set content = %s, trace = %s where id = %s', (new_solution, psycopg2.extras.Json(new_trace), row[0])) - else: - # this is the first entry - cur.execute('insert into solution (done, content, problem_id, codeq_user_id, trace) values (%s, %s, %s, %s, %s)', (False, solution, problem_id, uid, psycopg2.extras.Json(trace))) + cur.execute('update codeq_user set password = %s where id = %s', (encrypt_password(password), uid)) + affected = cur.rowcount + if affected is None: + raise PasswordChangeFailed('Password change failed') finally: cur.close() - conn.commit() - except: - conn.rollback() - raise finally: + conn.commit() db.return_connection(conn) - def change_password(self, password): + + def get_stat(self): uid = self.get_uid() conn = db.get_connection() try: cur = conn.cursor() try: - cur.execute('update codeq_user set password = %s where id = %s', (encrypt_password(password), uid)) - affected = cur.rowcount - if affected is None: - raise PasswordChangeFailed('Password change failed') + cur.execute("""select * from( + (SELECT + distinct + l.name as language, + pg.id as problem_group_id, + pg.name as problem_group, + count(distinct p.id) as all, + count(CASE s.done WHEN true THEN true END) as done, + count(CASE s.done WHEN false THEN true END) as in_progress + FROM problem p + INNER JOIN language l ON p.language_id = l.id + INNER JOIN problem_group pg ON p.problem_group_id = pg.id + LEFT JOIN solution s ON s.problem_id = p.id AND s.codeq_user_id = %s + GROUP BY l.name, pg.id, pg.name) + UNION ALL + (SELECT + distinct + l.name as language, + 0 as problem_group_id, + null as problem_group, + count(distinct p.id) as all, + count(CASE s.done WHEN true THEN true END) as done, + count(CASE s.done WHEN false THEN true END) as in_progress + FROM problem p + INNER JOIN language l ON p.language_id = l.id + LEFT JOIN solution s ON s.problem_id = p.id AND s.codeq_user_id = %s + GROUP BY l.name) + ) as u + ORDER BY u.language, problem_group_id""", (uid, uid,)) + #return cur.fetchall() + columns = ('language', 'problem_group_id', 'problem_group', 'problems_count', 'done', 'in_progress') + results = [] + for row in cur.fetchall(): + results.append(dict(zip(columns, row))) + return results finally: cur.close() finally: conn.commit() db.return_connection(conn) + def send(self, json_obj): """Sends a message to the user. -- cgit v1.2.1