Orderer/db_setup.py

from sqlite3 import OperationalError

from game import CURRENCY_NAME, MINIMUM_ORDER_AMOUNT


def setup(cursor):
    print('Database setup...')

    drop_triggers(cursor)

    tables(cursor)

    create_triggers(cursor)

    create_indices(cursor)

    seed(cursor)


def drop_triggers(cursor):
    print(' - Dropping all triggers...')
    cursor.execute("DROP TRIGGER IF EXISTS owned_amount_not_negative_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS owned_amount_not_negative_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS amount_positive_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS amount_positive_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS order_limit_not_negative_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS order_limit_not_negative_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS order_amount_positive_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS order_amount_positive_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS not_more_executed_than_ordered_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS not_more_executed_than_ordered_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS not_more_ordered_than_available_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS not_more_ordered_than_available_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS expiry_dt_in_future_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS expiry_dt_in_future_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS stop_loss_requires_limit_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS stop_loss_requires_limit_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS limit_requires_stop_loss_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS limit_requires_stop_loss_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS minimum_order_amount_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS integer_amount_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS dt_monotonic_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS dt_monotonic_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS orders_rowid_sorted_by_creation_time_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS news_dt_monotonic_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS not_nullify_buyer_id_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS buyer_id_not_null_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS not_nullify_seller_id_after_update")
    cursor.execute("DROP TRIGGER IF EXISTS seller_id_not_null_after_insert")
    cursor.execute("DROP TRIGGER IF EXISTS order_history_no_update")


def seed(cursor):
    print(' - Seeding initial data...')
    # ₭ollar
    cursor.execute('''
                    INSERT OR IGNORE INTO ownables
                    (name)
                    VALUES (?)
                    ''', (CURRENCY_NAME,))
    # The bank/external investors
    cursor.execute('''
                    INSERT OR IGNORE INTO users
                    (username,password)
                    VALUES ('bank','')
                    ''')

    # bank owns all the money that is not owned by players, 1000 * num_used_key - player_money
    cursor.execute('''
                    INSERT OR IGNORE INTO ownership
                    (user_id, ownable_id, amount)
                    VALUES ((SELECT rowid FROM users WHERE username = 'bank'), 
                            (SELECT rowid FROM ownables WHERE name = ?),
                            1000 * (SELECT COUNT(used_by_user_id) FROM keys) - (SELECT SUM(amount) 
                             FROM ownership 
                             WHERE ownable_id = (SELECT rowid FROM ownables WHERE name = ?)))
                    ''', (CURRENCY_NAME, CURRENCY_NAME,))

    # bank owns some stuff (₭ollar is be dealt with separately)
    cursor.execute('''
    INSERT OR IGNORE INTO ownership
    (user_id, ownable_id, amount)
    SELECT (SELECT rowid FROM users WHERE username = 'bank'), 
            ownables.rowid, 
            (SELECT COALESCE(SUM(amount),0) FROM ownership WHERE ownable_id = ownables.rowid)
    FROM ownables WHERE
    name <> ?
    ''', (CURRENCY_NAME,))
    cursor.executemany('''
    INSERT INTO global_control_values (value_name, value)
    WITH new_value AS (SELECT ? AS name, ? AS value)
    SELECT new_value.name, new_value.value
    FROM new_value
    WHERE NOT EXISTS(SELECT * -- tODO test if this works
                     FROM global_control_values v2
                     WHERE v2.value_name = new_value.name
                       AND v2.value = new_value.value
                       AND v2.dt = (SELECT MAX(v3.dt)
                                    FROM global_control_values v3
                                    WHERE v3.value_name = new_value.name
                                      AND v3.value = new_value.value))
    ''', [('banking_license_price', 5e6),
          ('personal_loan_interest_rate', 0.1),  # may seem a lot but actually this is a credit that you get without any assessment involved
          ('deposit_facility', -0.05),  # ECB 2020
          ('marginal_lending_facility', 0.25),  # ECB 2020
          ('cash_reserve_ratio', 0.01),  # Eurozone 2020
          ('cash_reserve_free_amount', 1e5),  # Eurozone 2020
          ])


def create_triggers(cursor):
    print(' - Creating triggers...')
    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 amount_positive_after_insert
                AFTER INSERT ON transactions
                WHEN NEW.amount <= 0
                BEGIN SELECT RAISE(ROLLBACK, 'Can not perform empty transactions.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS amount_positive_after_update
                AFTER UPDATE ON transactions
                WHEN NEW.amount <= 0
                BEGIN SELECT RAISE(ROLLBACK, 'Can not perform empty transactions.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS order_limit_not_negative_after_insert
                AFTER INSERT ON orders
                WHEN NEW."limit" IS NOT NULL AND NEW."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 NEW."limit" IS NOT NULL AND NEW."limit" < 0
                BEGIN SELECT RAISE(ROLLBACK, 'Can not set a limit less than 0.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS order_amount_positive_after_insert
                AFTER INSERT ON orders
                WHEN NEW.ordered_amount <= 0 OR NEW.executed_amount < 0
                BEGIN SELECT RAISE(ROLLBACK, 'Can not order 0 or less.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS order_amount_positive_after_update
                AFTER UPDATE ON orders
                WHEN NEW.ordered_amount <= 0 OR NEW.executed_amount < 0
                BEGIN SELECT RAISE(ROLLBACK, 'Can not order 0 or less.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS not_more_executed_than_ordered_after_insert
                AFTER INSERT ON orders
                WHEN NEW.ordered_amount < NEW.executed_amount
                BEGIN SELECT RAISE(ROLLBACK, 'Can not execute more than ordered.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS not_more_executed_than_ordered_after_update
                AFTER UPDATE ON orders
                WHEN NEW.ordered_amount < NEW.executed_amount
                BEGIN SELECT RAISE(ROLLBACK, 'Can not execute more than ordered.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS not_more_ordered_than_available_after_insert
                AFTER INSERT ON orders
                WHEN NOT NEW.buy AND 0 >
                    -- owned_amount
                    COALESCE (
                        (SELECT amount
                         FROM ownership
                         WHERE ownership.rowid = NEW.ownership_id), 0)
                    - -- sell_ordered_amount
                    (SELECT COALESCE(SUM(orders.ordered_amount - orders.executed_amount),0)
                     FROM orders, ownership
                     WHERE ownership.rowid = orders.ownership_id
                     AND ownership.rowid = NEW.ownership_id
                     AND NOT orders.buy) 
                BEGIN SELECT RAISE(ROLLBACK, 'Can not order more than you own.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS not_more_ordered_than_available_after_update
                AFTER UPDATE ON orders
                WHEN NOT NEW.buy AND 0 >
                    -- owned_amount
                    COALESCE (
                        (SELECT amount
                         FROM ownership
                         WHERE ownership.rowid = NEW.ownership_id), 0)
                    - -- sell_ordered_amount
                    (SELECT COALESCE(SUM(orders.ordered_amount - orders.executed_amount),0)
                     FROM orders, ownership
                     WHERE ownership.rowid = orders.ownership_id
                     AND ownership.rowid = NEW.ownership_id
                     AND NOT orders.buy) 
                BEGIN SELECT RAISE(ROLLBACK, 'Can not order more than you own.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS expiry_dt_in_future_after_insert
                AFTER INSERT ON orders
                WHEN NEW.expiry_dt <= datetime('now')
                BEGIN SELECT RAISE(ROLLBACK, 'Order is already expired.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS expiry_dt_in_future_after_update
                AFTER UPDATE ON orders
                WHEN NEW.expiry_dt <= datetime('now')
                BEGIN SELECT RAISE(ROLLBACK, 'Order is already expired.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS stop_loss_requires_limit_after_insert
                AFTER INSERT ON orders
                WHEN NEW."limit" IS NULL AND NEW.stop_loss IS NOT NULL
                BEGIN SELECT RAISE(ROLLBACK, 'Can only set `stop_loss` `for limit orders.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS stop_loss_requires_limit_after_update
                AFTER UPDATE ON orders
                WHEN NEW."limit" IS NULL AND NEW.stop_loss IS NOT NULL
                BEGIN SELECT RAISE(ROLLBACK, 'Can only set `stop_loss` `for limit orders.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS limit_requires_stop_loss_after_insert
                AFTER INSERT ON orders
                WHEN NEW."limit" IS NOT NULL AND NEW.stop_loss IS NULL
                BEGIN SELECT RAISE(ROLLBACK, 'Need to set stop_loss to either True or False for limit orders.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS limit_requires_stop_loss_after_update
                AFTER UPDATE ON orders
                WHEN NEW."limit" IS NOT NULL AND NEW.stop_loss IS NULL
                BEGIN SELECT RAISE(ROLLBACK, 'Need to set stop_loss to either True or False for limit orders.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS minimum_order_amount_after_insert
                AFTER INSERT ON orders
                WHEN NEW.ordered_amount < ?
                BEGIN SELECT RAISE(ROLLBACK, 'There is a minimum amount for new orders.'); END
                '''.replace('?', str(MINIMUM_ORDER_AMOUNT)))
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS integer_amount_after_insert
                AFTER INSERT ON orders
                WHEN NEW.ordered_amount <> ROUND(NEW.ordered_amount)
                BEGIN SELECT RAISE(ROLLBACK, 'Can only set integer amounts for new orders.'); END
                '''.replace('?', str(MINIMUM_ORDER_AMOUNT)))
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS dt_monotonic_after_insert
                AFTER INSERT ON transactions
                WHEN NEW.dt < (SELECT MAX(dt) FROM transactions t2 WHERE t2.rowid < rowid)
                BEGIN SELECT RAISE(ROLLBACK, 'Transaction rowid programming bug, not your fault.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS dt_monotonic_after_update
                AFTER INSERT ON transactions
                WHEN NEW.dt < (SELECT MAX(dt) FROM transactions t2 WHERE t2.rowid < rowid)
                BEGIN SELECT RAISE(ROLLBACK, 'Transaction rowid programming bug, not your fault.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS news_dt_monotonic_after_insert
                AFTER INSERT ON news
                WHEN NEW.dt < (SELECT MAX(dt) FROM news t2 WHERE t2.rowid < rowid)
                BEGIN SELECT RAISE(ROLLBACK, 'Transaction rowid programming bug, not your fault.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS news_dt_monotonic_after_insert
                AFTER INSERT ON news
                WHEN NEW.dt < (SELECT MAX(dt) FROM news t2 WHERE t2.rowid < rowid)
                BEGIN SELECT RAISE(ROLLBACK, 'Transaction rowid programming bug, not your fault.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS orders_rowid_sorted_by_creation_time_after_insert
                AFTER INSERT ON orders
                WHEN NEW.rowid < (SELECT MAX(rowid) FROM orders o2)
                BEGIN SELECT RAISE(ROLLBACK, 'Order-rowid programming bug (insert), not your fault.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS news_dt_monotonic_after_update
                AFTER UPDATE ON orders
                WHEN NEW.rowid <> OLD.rowid
                BEGIN SELECT RAISE(ROLLBACK, 'Cannot change number of existing order.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS not_nullify_buyer_id_after_update
                AFTER UPDATE ON transactions
                WHEN NEW.buyer_id IS NULL AND OLD.buyer_id IS NOT NULL
                BEGIN SELECT RAISE(ROLLBACK, 'Cannot nullify buyer_id.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS buyer_id_not_null_after_insert
                AFTER INSERT ON transactions
                WHEN NEW.buyer_id IS NULL
                BEGIN SELECT RAISE(ROLLBACK, 'buyer_id must not be null for new transactions.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS not_nullify_seller_id_after_update
                AFTER UPDATE ON transactions
                WHEN NEW.seller_id IS NULL AND OLD.seller_id IS NOT NULL
                BEGIN SELECT RAISE(ROLLBACK, 'Cannot nullify seller_id.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS seller_id_not_null_after_insert
                AFTER INSERT ON transactions
                WHEN NEW.seller_id IS NULL
                BEGIN SELECT RAISE(ROLLBACK, 'seller_id must not be null for new transactions.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS order_history_no_update
                BEFORE UPDATE ON order_history
                BEGIN SELECT RAISE(ROLLBACK, 'Can not change order history.'); END
                ''')


def create_indices(cursor):
    print(' - Creating indices...')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS ownership_ownable
                ON ownership (ownable_id)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS transactions_ownable
                ON transactions (ownable_id)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS orders_expiry
                ON orders (expiry_dt)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS orders_ownership
                ON orders (ownership_id)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS orders_limit
                ON orders ("limit")
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS transactions_dt
                ON transactions (dt)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS news_dt
                ON news (dt)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS ownables_name
                ON ownables (name)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS users_name
                ON users (username)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS sessions_id
                ON sessions (session_id)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS sessions_user
                ON sessions (user_id)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS transactions_seller
                ON transactions (seller_id)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS transactions_buyer
                ON transactions (buyer_id)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS order_history_id
                ON order_history (order_id)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS order_canceled
                ON order_history (archived_dt)
                ''')
    cursor.execute('''
                CREATE INDEX IF NOT EXISTS order_history_ownership
                ON order_history (ownership_id)
                ''')


def tables(cursor):
    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 NOT NULL,
                    executed_amount CURRENCY DEFAULT 0 NOT NULL,
                    expiry_dt DATETIME NOT NULL,
                    FOREIGN KEY (ownership_id) REFERENCES ownership(rowid)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS order_history(
                    ownership_id INTEGER NOT NULL,
                    buy BOOLEAN NOT NULL,
                    "limit" CURRENCY,
                    ordered_amount CURRENCY NOT NULL,
                    executed_amount CURRENCY NOT NULL,
                    expiry_dt DATETIME NOT NULL,
                    status VARCHAR(20) NOT NULL,
                    order_id INTEGER NOT NULL, -- order_id is not a FOREIGN KEY since orders are deleted from order table afterwards
                    archived_dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (ownership_id) REFERENCES ownership(rowid)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS transactions(
                    dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    price CURRENCY NOT NULL,
                    ownable_id INTEGER NOT NULL,
                    amount CURRENCY NOT NULL,
                    FOREIGN KEY (ownable_id) REFERENCES ownables(rowid)
                )
                ''')
    _add_column_if_not_exists(cursor, '''
                -- there is a not null constraint for new values that is watched by triggers
                ALTER TABLE transactions ADD COLUMN buyer_id INTEGER REFERENCES users(rowid)
                ''')
    _add_column_if_not_exists(cursor, '''
                -- there is a not null constraint for new values that is watched by triggers
                ALTER TABLE transactions ADD COLUMN seller_id INTEGER REFERENCES users(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 users(rowid)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS news(
                    dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    title VARCHAR(50) NOT NULL
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS banks(
                    user_id NOT NULL REFERENCES users(rowid)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS global_control_values(
                    dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    value_name VARCHAR NOT NULL,
                    value FLOAT NOT NULL,
                    UNIQUE (value_name, dt)
                )
                ''')


def _add_column_if_not_exists(cursor, query):
    if 'ALTER TABLE' not in query.upper():
        raise ValueError('Only alter table queries allowed.')
    if 'ADD COLUMN' not in query.upper():
        raise ValueError('Only add column queries allowed.')
    try:
        cursor.execute(query)
    except OperationalError as e:  # if the column already exists this will happen
        if 'duplicate column name' not in e.args[0]:
            raise