Orderer/model.py

import re
import sqlite3 as db
import sys
import uuid

import db_setup
from game import CURRENCY_NAME
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
        else:
            print('Must match "[A-Za-z0-9.-]{0,50}"')


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()

    db_setup.setup(cursor)

    connection.commit()


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, game_key):
    connect()
    if username == '':
        return False
    if password == '':
        return False
    cursor.execute('''
                INSERT INTO users 
                (username, password)
                VALUES (? , ?)
                ''', (username, password))
    own(get_user_id_by_name(username), CURRENCY_NAME)
    if game_key != '':
        if valid_key(game_key):
            activate_key(game_key, get_user_id_by_name(username))
    return True


def own(user_id, ownable_name):
    cursor.execute('''
                WITH ownableid AS (SELECT rowid FROM ownables WHERE name = ?),
                     userid AS (SELECT ?)
                INSERT INTO ownership (user_id, ownable_id)
                SELECT *
                FROM userid, ownableid
                WHERE NOT EXISTS (
                    SELECT * FROM ownership
                    WHERE ownership.user_id IN userid
                    AND ownership.ownable_id IN ownableid
                )
                ''', (ownable_name, user_id,))


def send_ownable(from_user_id, to_user_id, ownable_name, amount):
    connect()

    if amount < 0:
        return False

    if from_user_id != bank_id():
        cursor.execute('''
                    UPDATE ownership
                    SET amount = amount - ?
                    WHERE user_id = ?
                    AND ownable_id = (SELECT rowid FROM ownables WHERE name = ?)
                    ''', (amount, from_user_id, ownable_name,))
        if not cursor.fetchone():
            return False

    cursor.execute('''
                UPDATE ownership
                SET amount = amount + ?
                WHERE user_id = ?
                AND ownable_id = (SELECT rowid FROM ownables WHERE name = ?)
                ''', (amount, to_user_id, ownable_name))
    if cursor.rowcount == 0:
        return False
    return True


def valid_key(key):
    connect()

    cursor.execute('''
                SELECT key
                FROM keys
                WHERE used_by_user_id IS NULL
                AND key = ?
                ''', (key,))

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


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 save_key(key):
    connect()

    cursor.execute('''
                INSERT INTO keys 
                (key)
                VALUES (?)
                ''', (key,))


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 unused_keys():
    connect()

    cursor.execute('''
        SELECT key
        FROM keys
        WHERE used_by_user_id IS NULL
        ''')

    return [str(key[0]).strip().upper() for key in cursor.fetchall()]


def get_user_id_by_session_id(session_id):
    connect()

    cursor.execute('''
        SELECT users.rowid
        FROM sessions, users
        WHERE sessions.session_id = ?
        AND users.rowid = sessions.user_id
        ''', (session_id,))

    ids = cursor.fetchone()
    if ids is None:
        return False
    return ids[0]


def get_user_id_by_name(username):
    connect()

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

    return cursor.fetchone()[0]


def get_user_ownership(user_id):
    connect()

    cursor.execute('''
        SELECT ownables.name, ownership.amount
        FROM ownership, ownables
        WHERE user_id = ?
        AND ownership.ownable_id = ownables.rowid
        ''', (user_id,))

    return cursor.fetchall()


def activate_key(key, user_id):
    connect()
    cursor.execute('''
                UPDATE keys
                SET used_by_user_id = ?
                WHERE used_by_user_id IS NULL
                AND key = ?
                ''', (user_id, key,))

    if cursor.rowcount == 0:
        raise AssertionError
    send_ownable(bank_id(), user_id, CURRENCY_NAME, 1000)


def bank_id():
    connect()

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

    return cursor.fetchone()[0]


def valid_session_id(session_id):
    connect()

    cursor.execute('''
                SELECT rowid
                FROM sessions
                WHERE session_id = ?
                ''', (session_id,))

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


def get_user_orders(user_id):
    connect()

    cursor.execute('''
        SELECT 
            orders.buy, 
            ownables.name, 
            orders.ordered_amount - orders.executed_amount, 
            orders."limit", 
            orders.stop_loss, 
            orders.ordered_amount
        FROM orders, ownables, ownership
        WHERE ownership.user_id = ?
        AND ownership.ownable_id = ownables.rowid
        AND orders.ownership_id = ownership.rowid
        ORDER BY orders.buy, ownables.name
        ''', (user_id,))

    return cursor.fetchall()


def sell_ordered_amount(user_id, ownable_id):
    connect()

    # if ownable_id == kollar_id():
    #     return 0

    cursor.execute('''
                SELECT SUM(orders.ordered_amount - orders.executed_amount)
                FROM orders, ownership
                WHERE ownership.rowid = orders.ownership_id
                AND ownership.user_id = ?
                AND ownership.ownable_id = ?
                AND NOT orders.buy
                ''', (user_id, ownable_id))

    return cursor.fetchone()[0]


def user_owns_at_least(amount, user_id, ownable_id):
    connect()

    cursor.execute('''
                SELECT users.rowid
                FROM ownership
                AND ownership.user_id = ?
                AND ownership.ownable_id = ?
                AND ownership.amount - ? >= ?
                ''', (user_id, ownable_id, sell_ordered_amount(user_id, ownable_id), amount))

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


def news():
    connect()

    cursor.execute('''
        SELECT *
        FROM news
        ORDER BY dt
        LIMIT 20
        ''')

    return cursor.fetchall()