Orderer/db_setup/tables.py

from sqlite3 import OperationalError


def tables(cursor):
    print(' - Creating tables...')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS users(
                    rowid INTEGER PRIMARY KEY,
                    username VARCHAR(10) UNIQUE NOT NULL, 
                    password VARCHAR(200) NOT NULL)
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS ownables(
                    rowid INTEGER PRIMARY KEY,
                    name VARCHAR(10) UNIQUE NOT NULL)
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS ownership(
                    rowid INTEGER PRIMARY KEY,
                    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(
                    rowid INTEGER PRIMARY KEY,
                    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(
                    rowid INTEGER PRIMARY KEY,
                    ownership_id INTEGER NOT NULL,
                    buy BOOLEAN NOT NULL,
                    "limit" CURRENCY,
                    stop_loss BOOLEAN, -- TODO clarify what it means if this is NULL
                    ordered_amount CURRENCY NOT NULL,
                    executed_amount CURRENCY DEFAULT 0 NOT NULL,
                    expiry_dt TIMESTAMP NOT NULL,
                    ioc BOOLEAN NOT NULL,
                    FOREIGN KEY (ownership_id) REFERENCES ownership(rowid)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS order_history(
                    rowid INTEGER PRIMARY KEY,
                    ownership_id INTEGER NOT NULL,
                    buy BOOLEAN NOT NULL,
                    "limit" CURRENCY,
                    ordered_amount CURRENCY NOT NULL,
                    executed_amount CURRENCY NOT NULL,
                    expiry_dt TIMESTAMP 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 TIMESTAMP NOT NULL DEFAULT (CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)),
                    FOREIGN KEY (ownership_id) REFERENCES ownership(rowid)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS transactions(
                    rowid INTEGER PRIMARY KEY,
                    dt TIMESTAMP NOT NULL DEFAULT (CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)),
                    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 news(
                    rowid INTEGER PRIMARY KEY,
                    dt TIMESTAMP NOT NULL DEFAULT (CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)),
                    title VARCHAR(50) NOT NULL
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS banks(
                    rowid INTEGER PRIMARY KEY,
                    user_id INTEGER UNIQUE NOT NULL REFERENCES users(rowid),
                    last_deposit_facility_pay_dt TIMESTAMP NOT NULL DEFAULT (CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER))
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS global_control_values(
                    rowid INTEGER PRIMARY KEY,
                    dt TIMESTAMP NOT NULL DEFAULT (CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)),
                    value_name VARCHAR NOT NULL,
                    value FLOAT NOT NULL,
                    UNIQUE (value_name, dt)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS tender_calendar(
                    rowid INTEGER PRIMARY KEY,
                    dt TIMESTAMP UNIQUE NOT NULL,
                    mro_interest CURRENCY NOT NULL,
                    maturity_dt TIMESTAMP NOT NULL, 
                    executed BOOLEAN NOT NULL DEFAULT FALSE
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS loans(
                    rowid INTEGER PRIMARY KEY,
                    user_id INTEGER NOT NULL REFERENCES users(rowid),
                    total_amount CURRENCY NOT NULL CHECK(total_amount > 0), 
                    amount CURRENCY NOT NULL CHECK(amount > 0),
                    last_interest_pay_dt TIMESTAMP NOT NULL DEFAULT (CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)),
                    interest_rate CURRENCY NOT NULL -- determined from the global value 'personal_loan_interest_rate'
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS credits(
                    rowid INTEGER PRIMARY KEY,
                    issuer_id INTEGER NOT NULL REFERENCES users(rowid),
                    ownable_id INTEGER UNIQUE NOT NULL REFERENCES ownables(rowid),
                    last_interest_pay_dt TIMESTAMP NOT NULL DEFAULT (CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)),
                    maturity_dt TIMESTAMP NOT NULL DEFAULT (CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)),
                    coupon CURRENCY NOT NULL -- fancy word for interest rate
                )
                ''')
    _add_column_if_not_exists(cursor, '''
                -- there is a not null constraint for new values that is watched by triggers
                ALTER TABLE users ADD COLUMN salt BLOB NOT NULL DEFAULT 'orderer_is_a_cool_application_]{][{²$%WT§$%GV§$%SF$%&S$%FGGFHBDHJZIF254325'
                ''')


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
        else:
            pass