Orderer/model.py

import sqlite3 as db
import sys
import uuid

from game import money_amount

connection = None
cursor = None


def connect():
    global connection
    global cursor
    if connection is None or cursor is None:
        try:
            connection = db.connect('boerse.db')

            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 = True

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

    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 INTEGER NOT NULL)
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS ownership(
                    user_id INTEGER NOT NULL,
                    stock_id INTEGER NOT NULL,
                    amount INTEGER NOT NULL DEFAULT 0,
                    FOREIGN KEY (user_id) REFERENCES users(rowid),
                    FOREIGN KEY (stock_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)
                )
                ''')

    print('  Adding 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, stock_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()