from re import M from markupsafe import escape import sqlite3 from datetime import datetime import click from flask import current_app, g def log(statement, user_id=None, before=None, after=None, change=None): db = get_db() c = db.cursor() c.execute("INSERT INTO transaction_log (type, user_id, before, after, change) VALUES (?, ?, ?, ?, ?)", [ statement, user_id, before, after, change]) db.commit() def add_user(after): db = get_db() c = db.cursor() c.execute("INSERT or IGNORE INTO users (username, balance) VALUES (?, 0)", [after]) user_id = c.lastrowid log("add_user", user_id=user_id, after=after) db.commit() def remove_user(user_id): db = get_db() c = db.cursor() c.execute("SELECT * FROM users WHERE id = ?", [user_id]) user_name = c.fetchone()[1] c.execute("SELECT * FROM tags WHERE userid = ?", [user_id]) for tag in c.fetchall(): remove_tag(tag[0]) c.execute("DELETE FROM users WHERE id = ?", [user_id]) log("remove_user", user_id=user_id, before=user_name) db.commit() def add_tag(user_id, tag_id): db = get_db() c = db.cursor() c.execute("INSERT OR IGNORE INTO tags (tagid, userid) VALUES (?, ?)", [tag_id, user_id]) db.commit() log("add_tag", after=tag_id, user_id=user_id) def remove_tag(tag_id): db = get_db() c = db.cursor() c.execute("SELECT * FROM tags WHERE tagid = ?", [tag_id]) user_id = c.fetchone()[1] c.execute("DELETE FROM tags WHERE tagid = ?", [tag_id]) log("remove_tag", before=tag_id, user_id=user_id) db.commit() def change_balance(user_id, change): db = get_db() c = db.cursor() c.execute("UPDATE users SET balance = balance + ? WHERE id=?", [change, user_id]) log("balance", user_id=user_id, change=change) db.commit() def get_db(): if 'db' not in g: g.db = sqlite3.connect( current_app.config['DATABASE'], detect_types=sqlite3.PARSE_DECLTYPES ) g.db.row_factory = sqlite3.Row init_db() return g.db def close_db(e=None): db = g.pop('db', None) if db is not None: db.close() def init_db(): db = get_db() with current_app.open_resource('schema.sql') as f: db.executescript(f.read().decode('utf8')) @click.command('init-db') def init_db_command(): """Clear the existing data and create new tables.""" init_db() click.echo('Initialized the database.')