1
1

create_triggers.py 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293
  1. import sqlite3
  2. from typing import List
  3. from game import MINIMUM_ORDER_AMOUNT, CURRENCY_NAME, BANK_NAME
  4. def create_triggers(cursor: sqlite3.Cursor):
  5. print(' - Creating triggers...')
  6. # ensure that the internal rowids of any table are not updated after creation
  7. create_triggers_that_restrict_rowid_update(cursor)
  8. cursor.execute(f'''
  9. CREATE TRIGGER IF NOT EXISTS owned_amount_not_negative_after_insert
  10. AFTER INSERT ON ownership
  11. WHEN NEW.amount < 0 AND (SELECT name FROM ownables WHERE ownables.rowid = NEW.ownable_id) != '{CURRENCY_NAME}'
  12. BEGIN SELECT RAISE(ROLLBACK, 'Can not own an amount less than 0 (except {CURRENCY_NAME}).'); END
  13. ''')
  14. cursor.execute(f'''
  15. CREATE TRIGGER IF NOT EXISTS owned_amount_not_negative_after_update
  16. AFTER UPDATE ON ownership
  17. WHEN NEW.amount < 0 AND (SELECT name FROM ownables WHERE ownables.rowid = NEW.ownable_id) != '{CURRENCY_NAME}'
  18. BEGIN SELECT RAISE(ROLLBACK, 'Can not own an amount less than 0 (except {CURRENCY_NAME}).'); END
  19. ''')
  20. cursor.execute('''
  21. CREATE TRIGGER IF NOT EXISTS amount_positive_after_insert
  22. AFTER INSERT ON transactions
  23. WHEN NEW.amount <= 0
  24. BEGIN SELECT RAISE(ROLLBACK, 'Can not perform empty transactions.'); END
  25. ''')
  26. cursor.execute('''
  27. CREATE TRIGGER IF NOT EXISTS amount_positive_after_update
  28. AFTER UPDATE ON transactions
  29. WHEN NEW.amount <= 0
  30. BEGIN SELECT RAISE(ROLLBACK, 'Can not perform empty transactions.'); END
  31. ''')
  32. cursor.execute('''
  33. CREATE TRIGGER IF NOT EXISTS order_limit_not_negative_after_insert
  34. AFTER INSERT ON orders
  35. WHEN NEW."limit" IS NOT NULL AND NEW."limit" < 0
  36. BEGIN SELECT RAISE(ROLLBACK, 'Can not set a limit less than 0.'); END
  37. ''')
  38. cursor.execute('''
  39. CREATE TRIGGER IF NOT EXISTS order_limit_not_negative_after_update
  40. AFTER UPDATE ON orders
  41. WHEN NEW."limit" IS NOT NULL AND NEW."limit" < 0
  42. BEGIN SELECT RAISE(ROLLBACK, 'Can not set a limit less than 0.'); END
  43. ''')
  44. cursor.execute('''
  45. CREATE TRIGGER IF NOT EXISTS order_amount_positive_after_insert
  46. AFTER INSERT ON orders
  47. WHEN NEW.ordered_amount <= 0 OR NEW.executed_amount < 0
  48. BEGIN SELECT RAISE(ROLLBACK, 'Can not order 0 or less.'); END
  49. ''')
  50. cursor.execute('''
  51. CREATE TRIGGER IF NOT EXISTS order_amount_positive_after_update
  52. AFTER UPDATE ON orders
  53. WHEN NEW.ordered_amount <= 0 OR NEW.executed_amount < 0
  54. BEGIN SELECT RAISE(ROLLBACK, 'Can not order 0 or less.'); END
  55. ''')
  56. cursor.execute('''
  57. CREATE TRIGGER IF NOT EXISTS stop_loss_order_not_ioc_after_insert
  58. AFTER INSERT ON orders
  59. WHEN NEW.stop_loss AND NEW.ioc
  60. BEGIN SELECT RAISE(ROLLBACK, 'Stop-loss orders can not be Immediate-or-cancel.'); END
  61. ''')
  62. cursor.execute('''
  63. CREATE TRIGGER IF NOT EXISTS stop_loss_order_not_ioc_after_update
  64. AFTER UPDATE ON orders
  65. WHEN NEW.stop_loss AND NEW.ioc
  66. BEGIN SELECT RAISE(ROLLBACK, 'Stop-loss orders can not be Immediate-or-cancel.'); END
  67. ''')
  68. cursor.execute('''
  69. CREATE TRIGGER IF NOT EXISTS not_more_executed_than_ordered_after_insert
  70. AFTER INSERT ON orders
  71. WHEN NEW.ordered_amount < NEW.executed_amount
  72. BEGIN SELECT RAISE(ROLLBACK, 'Can not execute more than ordered.'); END
  73. ''')
  74. cursor.execute('''
  75. CREATE TRIGGER IF NOT EXISTS not_more_executed_than_ordered_after_update
  76. AFTER UPDATE ON orders
  77. WHEN NEW.ordered_amount < NEW.executed_amount
  78. BEGIN SELECT RAISE(ROLLBACK, 'Can not execute more than ordered.'); END
  79. ''')
  80. cursor.execute(f'''
  81. CREATE TRIGGER IF NOT EXISTS not_more_selling_than_available_after_insert
  82. AFTER INSERT ON orders
  83. WHEN (SELECT
  84. NOT NEW.buy -- is a selling order
  85. AND NOT EXISTS(
  86. SELECT * FROM credits
  87. WHERE credits.issuer_id = u.rowid
  88. AND credits.ownable_id = o.ownable_id) -- not an self-issued bond
  89. AND u.username != '{BANK_NAME}' -- bank may sell any amount
  90. AND 0 >
  91. -- owned_amount
  92. COALESCE (
  93. (SELECT amount
  94. FROM ownership
  95. WHERE ownership.rowid = NEW.ownership_id), 0)
  96. - -- sell_ordered_amount
  97. (SELECT COALESCE(SUM(orders.ordered_amount - orders.executed_amount),0)
  98. FROM orders, ownership
  99. WHERE ownership.rowid = orders.ownership_id
  100. AND ownership.rowid = NEW.ownership_id
  101. AND NOT orders.buy)
  102. FROM ownership o
  103. JOIN users u ON o.user_id = u.rowid
  104. WHERE o.rowid = NEW.ownership_id)
  105. BEGIN SELECT RAISE(ROLLBACK, 'Can not sell more than you own.'); END
  106. ''')
  107. cursor.execute(f'''
  108. CREATE TRIGGER IF NOT EXISTS not_more_selling_than_available_after_update
  109. AFTER UPDATE ON orders
  110. WHEN (SELECT
  111. NOT NEW.buy -- is a selling order
  112. AND NOT EXISTS(
  113. SELECT * FROM credits
  114. WHERE credits.issuer_id = u.rowid
  115. AND credits.ownable_id = o.ownable_id) -- not an self-issued bond
  116. AND u.username != '{BANK_NAME}' -- bank may sell any amount
  117. AND 0 >
  118. -- owned_amount
  119. COALESCE (
  120. (SELECT amount
  121. FROM ownership
  122. WHERE ownership.rowid = NEW.ownership_id), 0)
  123. - -- sell_ordered_amount
  124. (SELECT COALESCE(SUM(orders.ordered_amount - orders.executed_amount),0)
  125. FROM orders, ownership
  126. WHERE ownership.rowid = orders.ownership_id
  127. AND ownership.rowid = NEW.ownership_id
  128. AND NOT orders.buy)
  129. FROM ownership o
  130. JOIN users u ON o.user_id = u.rowid
  131. WHERE o.rowid = NEW.ownership_id)
  132. BEGIN SELECT RAISE(ROLLBACK, 'Can not sell more than you own.'); END
  133. ''')
  134. cursor.execute('''
  135. CREATE TRIGGER IF NOT EXISTS expiry_dt_in_future_after_insert
  136. AFTER INSERT ON orders
  137. WHEN NEW.expiry_dt <= CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)
  138. BEGIN SELECT RAISE(ROLLBACK, 'Order is already expired.'); END
  139. ''')
  140. cursor.execute('''
  141. CREATE TRIGGER IF NOT EXISTS expiry_dt_in_future_after_update
  142. AFTER UPDATE ON orders
  143. WHEN NEW.expiry_dt <= CAST(strftime('%s', CURRENT_TIMESTAMP) AS INTEGER)
  144. BEGIN SELECT RAISE(ROLLBACK, 'Order is already expired.'); END
  145. ''')
  146. cursor.execute('''
  147. CREATE TRIGGER IF NOT EXISTS stop_loss_requires_limit_after_insert
  148. AFTER INSERT ON orders
  149. WHEN NEW."limit" IS NULL AND NEW.stop_loss IS NOT NULL
  150. BEGIN SELECT RAISE(ROLLBACK, 'Can only set `stop_loss` `for limit orders.'); END
  151. ''')
  152. cursor.execute('''
  153. CREATE TRIGGER IF NOT EXISTS stop_loss_requires_limit_after_update
  154. AFTER UPDATE ON orders
  155. WHEN NEW."limit" IS NULL AND NEW.stop_loss IS NOT NULL
  156. BEGIN SELECT RAISE(ROLLBACK, 'Can only set `stop_loss` `for limit orders.'); END
  157. ''')
  158. cursor.execute('''
  159. CREATE TRIGGER IF NOT EXISTS limit_requires_stop_loss_after_insert
  160. AFTER INSERT ON orders
  161. WHEN NEW."limit" IS NOT NULL AND NEW.stop_loss IS NULL
  162. BEGIN SELECT RAISE(ROLLBACK, 'Need to set stop_loss to either True or False for limit orders.'); END
  163. ''')
  164. cursor.execute('''
  165. CREATE TRIGGER IF NOT EXISTS limit_requires_stop_loss_after_update
  166. AFTER UPDATE ON orders
  167. WHEN NEW."limit" IS NOT NULL AND NEW.stop_loss IS NULL
  168. BEGIN SELECT RAISE(ROLLBACK, 'Need to set stop_loss to either True or False for limit orders.'); END
  169. ''')
  170. cursor.execute('''
  171. CREATE TRIGGER IF NOT EXISTS minimum_order_amount_after_insert
  172. AFTER INSERT ON orders
  173. WHEN NEW.ordered_amount < ?
  174. BEGIN SELECT RAISE(ROLLBACK, 'There is a minimum amount for new orders.'); END
  175. '''.replace('?', str(MINIMUM_ORDER_AMOUNT)))
  176. cursor.execute('''
  177. CREATE TRIGGER IF NOT EXISTS integer_amount_after_insert
  178. AFTER INSERT ON orders
  179. WHEN NEW.ordered_amount <> ROUND(NEW.ordered_amount)
  180. BEGIN SELECT RAISE(ROLLBACK, 'Can only set integer amounts for new orders.'); END
  181. '''.replace('?', str(MINIMUM_ORDER_AMOUNT)))
  182. cursor.execute('''
  183. CREATE TRIGGER IF NOT EXISTS dt_monotonic_after_insert
  184. AFTER INSERT ON transactions
  185. WHEN NEW.dt < (SELECT MAX(dt) FROM transactions t2 WHERE t2.rowid < rowid)
  186. BEGIN SELECT RAISE(ROLLBACK, 'Transaction rowid programming bug, not your fault.'); END
  187. ''')
  188. cursor.execute('''
  189. CREATE TRIGGER IF NOT EXISTS dt_monotonic_after_update
  190. AFTER INSERT ON transactions
  191. WHEN NEW.dt < (SELECT MAX(dt) FROM transactions t2 WHERE t2.rowid < rowid)
  192. BEGIN SELECT RAISE(ROLLBACK, 'Transaction rowid programming bug, not your fault.'); END
  193. ''')
  194. cursor.execute('''
  195. CREATE TRIGGER IF NOT EXISTS news_dt_monotonic_after_insert
  196. AFTER INSERT ON news
  197. WHEN NEW.dt < (SELECT MAX(dt) FROM news t2 WHERE t2.rowid < rowid)
  198. BEGIN SELECT RAISE(ROLLBACK, 'Transaction rowid programming bug, not your fault.'); END
  199. ''')
  200. cursor.execute('''
  201. CREATE TRIGGER IF NOT EXISTS news_dt_monotonic_after_insert
  202. AFTER INSERT ON news
  203. WHEN NEW.dt < (SELECT MAX(dt) FROM news t2 WHERE t2.rowid < rowid)
  204. BEGIN SELECT RAISE(ROLLBACK, 'Transaction rowid programming bug, not your fault.'); END
  205. ''')
  206. cursor.execute('''
  207. CREATE TRIGGER IF NOT EXISTS orders_rowid_sorted_by_creation_time_after_insert
  208. AFTER INSERT ON orders
  209. WHEN NEW.rowid < (SELECT MAX(rowid) FROM orders o2)
  210. BEGIN SELECT RAISE(ROLLBACK, 'Order-rowid programming bug (insert), not your fault.'); END
  211. ''')
  212. cursor.execute('''
  213. CREATE TRIGGER IF NOT EXISTS news_dt_monotonic_after_update
  214. AFTER UPDATE ON orders
  215. WHEN NEW.rowid <> OLD.rowid
  216. BEGIN SELECT RAISE(ROLLBACK, 'Cannot change number of existing order.'); END
  217. ''')
  218. cursor.execute('''
  219. CREATE TRIGGER IF NOT EXISTS not_nullify_buyer_id_after_update
  220. AFTER UPDATE ON transactions
  221. WHEN NEW.buyer_id IS NULL AND OLD.buyer_id IS NOT NULL
  222. BEGIN SELECT RAISE(ROLLBACK, 'Cannot nullify buyer_id.'); END
  223. ''')
  224. cursor.execute('''
  225. CREATE TRIGGER IF NOT EXISTS buyer_id_not_null_after_insert
  226. AFTER INSERT ON transactions
  227. WHEN NEW.buyer_id IS NULL
  228. BEGIN SELECT RAISE(ROLLBACK, 'buyer_id must not be null for new transactions.'); END
  229. ''')
  230. cursor.execute('''
  231. CREATE TRIGGER IF NOT EXISTS not_nullify_seller_id_after_update
  232. AFTER UPDATE ON transactions
  233. WHEN NEW.seller_id IS NULL AND OLD.seller_id IS NOT NULL
  234. BEGIN SELECT RAISE(ROLLBACK, 'Cannot nullify seller_id.'); END
  235. ''')
  236. cursor.execute('''
  237. CREATE TRIGGER IF NOT EXISTS seller_id_not_null_after_insert
  238. AFTER INSERT ON transactions
  239. WHEN NEW.seller_id IS NULL
  240. BEGIN SELECT RAISE(ROLLBACK, 'seller_id must not be null for new transactions.'); END
  241. ''')
  242. def create_combination_cluster_triggers(cursor: sqlite3.Cursor,
  243. table_name: str,
  244. foreign_key_column_name: str,
  245. referenced_tables: List[str],
  246. kind_column_name: str = 'kind',):
  247. valid_kind = '\n OR '.join("(NEW.{0} = '{1}' AND EXISTS (SELECT * FROM {1} WHERE rowid = NEW.{2}))"
  248. .format(kind_column_name, table, foreign_key_column_name)
  249. for table in referenced_tables)
  250. cursor.execute('''-- noinspection SqlResolveForFile
  251. CREATE TRIGGER valid_{0}_{1}_after_insert
  252. AFTER INSERT ON {0}
  253. WHEN NOT ({2})
  254. BEGIN SELECT RAISE(ROLLBACK, '{0}.{1} is invalid or violating a foreign key constraint.'); END
  255. '''.format(table_name, kind_column_name, valid_kind))
  256. cursor.execute('''-- noinspection SqlResolveForFile
  257. CREATE TRIGGER valid_{0}_{1}_after_update
  258. AFTER UPDATE ON {0}
  259. WHEN NOT ({2})
  260. BEGIN SELECT RAISE(ROLLBACK, '{0}.{1} is invalid or violating a foreign key constraint.'); END
  261. '''.format(table_name, kind_column_name, valid_kind))
  262. for referenced_table in referenced_tables:
  263. cursor.execute('''-- noinspection SqlResolveForFile
  264. CREATE TRIGGER IF NOT EXISTS {0}_{1}_{3}_foreign_key_before_delete
  265. BEFORE DELETE ON {3}
  266. WHEN EXISTS (
  267. SELECT * FROM {0}
  268. WHERE {0}.{4} = OLD.rowid
  269. AND {0}.{1} = '{3}'
  270. )
  271. BEGIN SELECT RAISE(ROLLBACK, '{0}.{1} is violating a foreign key constraint.'); END
  272. '''.format(table_name, kind_column_name, valid_kind, referenced_table, foreign_key_column_name))
  273. def create_triggers_that_restrict_rowid_update(cursor):
  274. cursor.execute('''
  275. SELECT name FROM sqlite_master WHERE type='table'
  276. ''')
  277. tables = [row[0] for row in cursor.fetchall()]
  278. for table_name in tables:
  279. if table_name.startswith('sqlite_'):
  280. continue
  281. cursor.execute('''-- noinspection SqlResolveForFile
  282. CREATE TRIGGER IF NOT EXISTS restrict_rowid_update_on_{0}
  283. AFTER UPDATE ON {0}
  284. WHEN OLD.rowid <> NEW.rowid
  285. BEGIN SELECT RAISE(ROLLBACK, 'The rowid can not be changed.'); END
  286. '''.format(table_name))