Orderer/db_setup.py

from game import money_amount
from model import cursor


def setup():

    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")
        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, ownable_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)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS keys(
                    key STRING UNIQUE NOT NULL,
                    used_by_user_id INTEGER UNIQUE,
                    FOREIGN KEY (used_by_user_id) REFERENCES user(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))