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,
                    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(
                    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 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(
                    rowid INTEGER PRIMARY KEY,
                    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(
                    rowid INTEGER PRIMARY KEY,
                    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(
                    rowid INTEGER PRIMARY KEY,
                    dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    title VARCHAR(50) NOT NULL
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS banks(
                    rowid INTEGER PRIMARY KEY,
                    user_id NOT NULL REFERENCES users(rowid)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS global_control_values(
                    rowid INTEGER PRIMARY KEY,
                    dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    value_name VARCHAR NOT NULL,
                    value FLOAT NOT NULL,
                    UNIQUE (value_name, dt)
                )
                ''')
    _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