Orderer/db_setup.py

from game import CURRENCY_NAME
from util import debug

replace = False and debug


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

    if replace:
        drop_database(cursor)

    tables(cursor)

    integrity_checks(cursor)

    if replace:  # TODO also seed new databases
        seed(cursor)


def drop_database(cursor):
    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")
    cursor.execute("DROP TABLE IF EXISTS keys")
    cursor.execute("DROP TABLE IF EXISTS news")
    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 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")


def seed(cursor):
    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,))


def integrity_checks(cursor):
    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 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 expiry_dt_in_future_after_insert
                AFTER INSERT
                ON orders
                WHEN NEW.expiry_dt < NEW.dt
                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 < NEW.dt
                BEGIN
                    SELECT RAISE(ROLLBACK, 'Order is already expired.');
                END
                ''')


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,
                    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,
                    expiry_dt DATETIME 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 DEFAULT CURRENT_TIMESTAMP,
                    title VARCHAR(50) NOT NULL
                )
                ''')