Orderer/db_setup.py

from game import CURRENCY_NAME


def setup(cursor):
    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(200) NOT NULL)
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS ownables(
                    name VARCHAR(10) UNIQUE 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 DEFAULT 0,
                    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,
                    FOREIGN KEY (used_by_user_id) REFERENCES user(rowid)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS news(
                    dt DATETIME NOT NULL,
                    title VARCHAR(50) NOT NULL
                )
                ''')

    print(' - Integrity checks...')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS owned_amount_not_negative_after_insert
                AFTER INSERT
                ON ownership
                WHEN NEW.amount < 0
                BEGIN
                    SELECT RAISE(ROLLBACK, 'Can not own an amount less than 0.');
                END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS owned_amount_not_negative_after_update
                AFTER UPDATE
                ON ownership
                WHEN NEW.amount < 0
                BEGIN
                    SELECT RAISE(ROLLBACK, 'Can not own an amount less than 0.');
                END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS order_limit_not_negative_after_insert
                AFTER INSERT
                ON orders
                WHEN "limit" IS NOT NULL AND "limit" < 0
                BEGIN
                    SELECT RAISE(ROLLBACK, 'Can not set a limit less than 0.');
                END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS order_limit_not_negative_after_update
                AFTER UPDATE
                ON orders
                WHEN "limit" IS NOT NULL AND "limit" < 0
                BEGIN
                    SELECT RAISE(ROLLBACK, 'Can not set a limit less than 0.');
                END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS order_amount_not_negative_after_insert
                AFTER INSERT
                ON orders
                WHEN ordered_amount <= 0 OR executed_amount < 0
                BEGIN
                    SELECT RAISE(ROLLBACK, 'Can not order 0 or less.');
                END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS order_amount_not_negative_after_update
                AFTER UPDATE
                ON orders
                WHEN ordered_amount <= 0 OR executed_amount < 0
                BEGIN
                    SELECT RAISE(ROLLBACK, 'Can not order 0 or less.');
                END
                ''')

    if replace:  # TODO also seed new databases
        print(' - Seeding initial data...')
        cursor.execute('''
                    INSERT INTO ownables
                    (name)
                    VALUES (?)
                    ''', (CURRENCY_NAME,))
        cursor.execute('''
                    INSERT INTO users
                    (username,password)
                    VALUES ('bank','')
                    ''')
        cursor.execute('''
                    INSERT INTO ownership
                    (user_id, ownable_id)
                    VALUES ((SELECT rowid FROM users WHERE username = 'bank'), 
                            (SELECT rowid FROM ownables WHERE name = ?))
                    ''', (CURRENCY_NAME,))