Orderer/db_setup/create_triggers.py

import sqlite3
from typing import List

from game import MINIMUM_ORDER_AMOUNT, CURRENCY_NAME, BANK_NAME


def create_triggers(cursor: sqlite3.Cursor):
    print(' - Creating triggers...')
    # ensure that the internal rowids of any table are not updated after creation
    create_triggers_that_restrict_rowid_update(cursor)
    cursor.execute(f'''
                CREATE TRIGGER IF NOT EXISTS owned_amount_not_negative_after_insert
                AFTER INSERT ON ownership
                WHEN NEW.amount < 0 AND (SELECT name FROM ownables WHERE ownables.rowid = NEW.ownable_id) != '{CURRENCY_NAME}'
                BEGIN SELECT RAISE(ROLLBACK, 'Can not own an amount less than 0 (except {CURRENCY_NAME}).'); END
                ''')
    cursor.execute(f'''
                CREATE TRIGGER IF NOT EXISTS owned_amount_not_negative_after_update
                AFTER UPDATE ON ownership
                WHEN NEW.amount < 0 AND (SELECT name FROM ownables WHERE ownables.rowid = NEW.ownable_id) != '{CURRENCY_NAME}'
                BEGIN SELECT RAISE(ROLLBACK, 'Can not own an amount less than 0 (except {CURRENCY_NAME}).'); 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 stop_loss_order_not_ioc_after_insert
                AFTER INSERT ON orders
                WHEN NEW.stop_loss AND NEW.ioc
                BEGIN SELECT RAISE(ROLLBACK, 'Stop-loss orders can not be Immediate-or-cancel.'); END
                ''')
    cursor.execute('''
                CREATE TRIGGER IF NOT EXISTS stop_loss_order_not_ioc_after_update
                AFTER UPDATE ON orders
                WHEN NEW.stop_loss AND NEW.ioc
                BEGIN SELECT RAISE(ROLLBACK, 'Stop-loss orders can not be Immediate-or-cancel.'); 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(f'''
                CREATE TRIGGER IF NOT EXISTS not_more_selling_than_available_after_insert
                AFTER INSERT ON orders
                WHEN (SELECT 
                    NOT NEW.buy -- is a selling order
                    AND NOT EXISTS(
                        SELECT * FROM credits
                        WHERE credits.issuer_id = u.rowid
                        AND credits.ownable_id = o.ownable_id) -- not an self-issued bond
                    AND u.username != '{BANK_NAME}' -- bank may sell any amount
                    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)
                FROM ownership o 
                JOIN users u ON o.user_id = u.rowid
                WHERE o.rowid = NEW.ownership_id)
                BEGIN SELECT RAISE(ROLLBACK, 'Can not sell more than you own.'); END
                ''')
    cursor.execute(f'''
                CREATE TRIGGER IF NOT EXISTS not_more_selling_than_available_after_update
                AFTER UPDATE ON orders
                WHEN (SELECT 
                    NOT NEW.buy -- is a selling order
                    AND NOT EXISTS(
                        SELECT * FROM credits
                        WHERE credits.issuer_id = u.rowid
                        AND credits.ownable_id = o.ownable_id) -- not an self-issued bond
                    AND u.username != '{BANK_NAME}' -- bank may sell any amount
                    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)
                FROM ownership o 
                JOIN users u ON o.user_id = u.rowid
                WHERE o.rowid = NEW.ownership_id)
                BEGIN SELECT RAISE(ROLLBACK, 'Can not sell 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 <= CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)
                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 <= CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)
                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
                ''')


def create_combination_cluster_triggers(cursor: sqlite3.Cursor,
                                        table_name: str,
                                        foreign_key_column_name: str,
                                        referenced_tables: List[str],
                                        kind_column_name: str = 'kind',):
    valid_kind = '\n    OR '.join("(NEW.{0} = '{1}' AND EXISTS (SELECT * FROM {1} WHERE rowid = NEW.{2}))"
                                  .format(kind_column_name, table, foreign_key_column_name)
                                  for table in referenced_tables)
    cursor.execute('''-- noinspection SqlResolveForFile
        CREATE TRIGGER valid_{0}_{1}_after_insert
        AFTER INSERT ON {0}
        WHEN NOT ({2})
        BEGIN SELECT RAISE(ROLLBACK, '{0}.{1} is invalid or violating a foreign key constraint.'); END
    '''.format(table_name, kind_column_name, valid_kind))
    cursor.execute('''-- noinspection SqlResolveForFile
        CREATE TRIGGER valid_{0}_{1}_after_update
        AFTER UPDATE ON {0}
        WHEN NOT ({2})
        BEGIN SELECT RAISE(ROLLBACK, '{0}.{1} is invalid or violating a foreign key constraint.'); END
    '''.format(table_name, kind_column_name, valid_kind))
    for referenced_table in referenced_tables:
        cursor.execute('''-- noinspection SqlResolveForFile
            CREATE TRIGGER IF NOT EXISTS {0}_{1}_{3}_foreign_key_before_delete
            BEFORE DELETE ON {3}
            WHEN EXISTS (
                SELECT * FROM {0}
                WHERE {0}.{4} = OLD.rowid
                AND {0}.{1} = '{3}'
            )
            BEGIN SELECT RAISE(ROLLBACK, '{0}.{1} is violating a foreign key constraint.'); END
        '''.format(table_name, kind_column_name, valid_kind, referenced_table, foreign_key_column_name))


def create_triggers_that_restrict_rowid_update(cursor):
    cursor.execute('''
        SELECT name FROM sqlite_master WHERE type='table'
    ''')
    tables = [row[0] for row in cursor.fetchall()]
    for table_name in tables:
        if table_name.startswith('sqlite_'):
            continue
        cursor.execute('''-- noinspection SqlResolveForFile
                    CREATE TRIGGER IF NOT EXISTS restrict_rowid_update_on_{0}
                    AFTER UPDATE ON {0}
                    WHEN OLD.rowid <> NEW.rowid
                    BEGIN SELECT RAISE(ROLLBACK, 'The rowid can not be changed.'); END
                    '''.format(table_name))