1 files changed, 44 insertions, 0 deletions
diff --git a/monkey/db.py b/monkey/db.py
new file mode 100644
index 0000000..0634098
--- /dev/null
+++ b/monkey/db.py
@@ -0,0 +1,44 @@
+#!/usr/bin/python3
+
+import sqlite3
+
+db = sqlite3.connect('misc/solutions.db')
+db.row_factory = sqlite3.Row
+db.text_factory = bytes
+cursor = db.cursor()
+
+def b_to_utf8(bytestring):
+    return str(bytestring, encoding='utf-8')
+
+def get_problem_ids():
+    cursor.execute('SELECT id FROM problems ORDER BY id ASC')
+    return [row['id'] for row in cursor]
+
+def get_problem(pid):
+    cursor.execute('SELECT name, solution, library FROM problems WHERE id=?', (pid,))
+    row = cursor.fetchone()
+    name = b_to_utf8(row['name'])
+    solution = b_to_utf8(row['solution']).replace('\r', '')
+    lib_id = row['library'] if row['library'] else None
+    return name, solution, lib_id
+
+def get_depends(pid):
+    cursor.execute('SELECT dependency FROM depends WHERE problem=?', (pid,))
+    return [r['dependency'] for r in cursor.fetchall()]
+
+def get_library(lid):
+    cursor.execute('SELECT facts FROM libraries WHERE id=?', (lid,))
+    row = cursor.fetchone()
+    return b_to_utf8(row['facts']).replace('\r', '') if row else None
+
+def get_tests(pid):
+    cursor.execute('SELECT query FROM tests WHERE problem=?', (pid,))
+    return [b_to_utf8(row['query']) for row in cursor]
+
+def get_traces(pid):
+    cursor.execute('SELECT * FROM attempts WHERE problem=? AND done=1 ORDER BY id ASC', (pid,))
+    return {(pid, attempt['user']): attempt['log'] for attempt in cursor}
+
+def get_solved(uid):
+    cursor.execute('SELECT problem FROM attempts WHERE user=? AND done=1 ORDER BY problem ASC', (uid,))
+    return [row['problem'] for row in cursor.fetchall()]