Orderer/model.py

import re
import sqlite3 as db
import sys
import uuid

from game import money_amount
from util import debug

connection = None
cursor = None
db_name = None


def query_save_name():
    global db_name
    if debug:
        db_name = 'test.db'
        return
    while True:
        save_name = input('Name of the savegame: ')
        if re.match(r"[A-Za-z0-9.-]{0,50}", save_name):
            db_name = save_name + '.db'
            return


def connect(reconnect=False):
    global connection
    global cursor
    global db_name
    if reconnect:
        connection.commit()
        connection.close()
        cursor = None
        connection = None
        db_name = None

    if connection is None or cursor is None:
        query_save_name()

        try:
            connection = db.connect(db_name)

            cursor = connection.cursor()

        except db.Error as e:
            print("Database error %s:" % e.args[0])
            sys.exit(1)

        # finally:
        #     if con is not None:
        #         con.close()


def setup():
    connect()

    print('Database setup...')

    replace = False

    if replace:
        print(' - Dropping old tables...')
        cursor.execute("DROP TABLE IF EXISTS users")
        cursor.execute("DROP TABLE IF EXISTS ownables")
        cursor.execute("DROP TABLE IF EXISTS ownership")
        cursor.execute("DROP TABLE IF EXISTS sessions")
        cursor.execute("DROP TABLE IF EXISTS orders")
        cursor.execute("DROP TABLE IF EXISTS transactions")

    print(' - Creating tables...')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS users(
                    username VARCHAR(10) UNIQUE NOT NULL, 
                    password VARCHAR(6) NOT NULL)
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS ownables(
                    name VARCHAR(10) UNIQUE NOT NULL, 
                    total_amount CURRENCY NOT NULL)
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS ownership(
                    user_id INTEGER NOT NULL,
                    ownable_id INTEGER NOT NULL,
                    amount CURRENCY NOT NULL DEFAULT 0,
                    FOREIGN KEY (user_id) REFERENCES users(rowid),
                    FOREIGN KEY (ownable_id) REFERENCES ownables(rowid),
                    UNIQUE (user_id, stock_id)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS sessions(
                    user_id INTEGER NOT NULL,
                    session_id STRING NOT NULL,
                    FOREIGN KEY (user_id) REFERENCES users(rowid)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS orders(
                    ownership_id INTEGER NOT NULL,
                    buy BOOLEAN NOT NULL,
                    limit CURRENCY,
                    stop_loss BOOLEAN,
                    ordered_amount CURRENCY,
                    executed_amount CURRENCY,
                    FOREIGN KEY (ownership_id) REFERENCES ownership(rowid)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS transactions(
                    dt DATETIME NOT NULL,
                    price CURRENCY NOT NULL,
                    ownable_id INTEGER NOT NULL,
                    FOREIGN KEY (ownable_id) REFERENCES ownable(rowid)
                )
                ''')

    if replace:  # TODO also seed new databases
        print(' - Seeding initial data...')
        cursor.execute('''
                    INSERT INTO users
                    (username, password)
                    VALUES ("bank", "")
                    ''')
        cursor.execute('''
                    SELECT rowid 
                    FROM users
                    WHERE username = "bank"
                    ''')
        bank_id = cursor.fetchone()[0]
        cursor.execute('''
                    INSERT INTO ownables
                    (name, total_amount)
                    VALUES ("Kollar", ?)
                    ''', (money_amount,))
        cursor.execute('''
                    SELECT rowid 
                    FROM users
                    WHERE username = "bank"
                    ''')
        kollar_id = cursor.fetchone()[0]
        cursor.execute('''
                    INSERT INTO ownership
                    (user_id, ownable_id, amount)
                    VALUES (?, ?, ?)
                    ''', (bank_id, kollar_id, money_amount))


def login(username, password):
    connect()

    # do not allow login as bank
    if password == '':
        return None

    cursor.execute('''
                SELECT rowid
                FROM users
                WHERE username = ?
                AND password = ?
                ''', (username, password))
    user_id = cursor.fetchone()
    if user_id:
        return new_session(user_id)
    else:
        return None


def register(username, password):
    connect()
    if username == '':
        return False
    if password == '':
        return False
    cursor.execute('''
                INSERT INTO users 
                (username, password)
                VALUES (? , ?)
                ''', (username, password))
    return True


def new_session(user_id):
    connect()

    session_id = str(uuid.uuid4())

    cursor.execute('''
                INSERT INTO SESSIONS 
                (user_id, session_id)
                VALUES (? , ?)
                ''', (user_id[0], session_id))

    return session_id


def drop_old_sessions():
    connect()
    # TODO: test
    cursor.execute('''
                DELETE FROM sessions s1
                WHERE 
                    (SELECT COUNT(*) as newer
                     FROM sessions s2
                     WHERE s1.user_id = s2.user_id
                     AND s1.rowid < s2.rowid) >= 10
                ''')


def user_exists(username):
    connect()

    cursor.execute('''
                SELECT rowid
                FROM users
                WHERE username = ?
                ''', (username,))

    if cursor.fetchone():
        return True
    else:
        return False


def move_money(username):
    connect()

    cursor.execute()