model.py 40 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378
  1. import json
  2. import os
  3. import random
  4. import re
  5. import sqlite3 as db
  6. import uuid
  7. from logging import INFO
  8. from math import floor
  9. from shutil import copyfile
  10. from typing import Optional, Dict
  11. from passlib.handlers.sha2_crypt import sha256_crypt
  12. import db_setup
  13. import trading_bot
  14. from game import CURRENCY_NAME, logger, DB_NAME, MIN_INTEREST_INTERVAL
  15. from util import random_chars
  16. DBName = str
  17. connections: Dict[DBName, db.Connection] = {}
  18. current_connection: Optional[db.Connection] = None
  19. current_cursor: Optional[db.Cursor] = None
  20. current_db_name: Optional[DBName] = None
  21. current_user_id: Optional[int] = None
  22. def execute(sql, parameters=()):
  23. if not re.search(r"(?i)\s*SELECT", sql):
  24. logger.info(sql, 'sql_query', data=json.dumps(parameters))
  25. return current_cursor.execute(sql, parameters)
  26. def executemany(sql, parameters=()):
  27. if not re.search(r"(?i)\s*SELECT", sql):
  28. logger.info(sql, 'sql_query_many', data=json.dumps(parameters))
  29. return current_cursor.executemany(sql, parameters)
  30. def valid_db_name(name):
  31. return re.match(r"[a-z0-9.-]{0,20}", name)
  32. def query_save_name():
  33. while True:
  34. # save_name = input('Name of the database (You can also enter a new filename here): ')
  35. save_name = DB_NAME
  36. if valid_db_name(save_name):
  37. return save_name
  38. else:
  39. print('Must match "[a-z0-9.-]{0,20}"')
  40. def connect(db_name=None, create_if_not_exists=False):
  41. """
  42. connects to the database with the given name, if it exists
  43. if the database does not exist an exception is raised
  44. (unless create_if_not_exists is true, then the database is created)
  45. if there is already a connection to this database, that connection is used
  46. :return: the connection and the connections' cursor
  47. """
  48. if db_name is None:
  49. db_name = query_save_name()
  50. if not db_name.endswith('.db'):
  51. db_name += '.db'
  52. db_name = db_name.lower()
  53. if not os.path.isfile(db_name) and not create_if_not_exists:
  54. raise FileNotFoundError('There is no database with this name.')
  55. creating_new_db = not os.path.isfile(db_name)
  56. if db_name not in connections:
  57. try:
  58. db_connection = db.connect(db_name, check_same_thread=False)
  59. db_setup.create_functions(db_connection)
  60. db_setup.set_pragmas(db_connection.cursor())
  61. # connection.text_factory = lambda x: x.encode('latin-1')
  62. except db.Error as e:
  63. print("Database error %s:" % e.args[0])
  64. raise
  65. connections[db_name] = db_connection
  66. global current_connection
  67. global current_db_name
  68. global current_cursor
  69. current_connection = connections[db_name]
  70. current_cursor = connections[db_name].cursor()
  71. current_db_name = db_name
  72. if creating_new_db:
  73. try:
  74. if os.path.isfile('/test-db/' + db_name):
  75. print('Using test database containing fake data')
  76. copyfile('/test-db/' + db_name, db_name)
  77. else:
  78. logger.log('Creating database', INFO, 'database_creation')
  79. logger.commit()
  80. setup()
  81. except Exception:
  82. if current_connection is not None:
  83. current_connection.rollback()
  84. if db_name in connections:
  85. disconnect(db_name, rollback=True)
  86. os.remove(db_name)
  87. current_connection = None
  88. current_cursor = None
  89. current_db_name = None
  90. raise
  91. def disconnect(connection_name, rollback=True):
  92. global connections
  93. if connection_name not in connections:
  94. raise ValueError('Invalid connection')
  95. if rollback:
  96. connections[connection_name].rollback()
  97. else:
  98. connections[connection_name].commit()
  99. connections[connection_name].close()
  100. del connections[connection_name]
  101. def setup():
  102. db_setup.setup(current_cursor)
  103. def used_key_count():
  104. connect()
  105. execute('''
  106. SELECT COUNT(*) -- rarely executed, no index needed, O(n) query
  107. FROM keys
  108. WHERE used_by_user_id IS NOT NULL
  109. ''')
  110. return current_cursor.fetchone()[0]
  111. def login(username, password):
  112. execute('''
  113. SELECT rowid, password, salt
  114. FROM users
  115. WHERE username = ?
  116. ''', (username,))
  117. data = current_cursor.fetchone()
  118. if not data:
  119. return None
  120. user_id, hashed_password, salt = data
  121. # if a ValueError occurs here, then most likely a password that was stored as plain text
  122. if sha256_crypt.verify(password + salt, hashed_password):
  123. return new_session(user_id)
  124. else:
  125. return None
  126. def register(username, password, game_key):
  127. salt = str(uuid.uuid4())
  128. hashed_password = sha256_crypt.using(rounds=100000).encrypt(str(password) + salt)
  129. connect()
  130. if username == '':
  131. return False
  132. if password == '':
  133. return False
  134. execute('''
  135. INSERT INTO users
  136. (username, password, salt)
  137. VALUES (? , ?, ?)
  138. ''', (username, hashed_password, salt))
  139. if game_key != '':
  140. if valid_key(game_key):
  141. activate_key(game_key, get_user_id_by_name(username))
  142. own(get_user_id_by_name(username), CURRENCY_NAME)
  143. return True
  144. def own(user_id, ownable_name, amount=0):
  145. if not isinstance(ownable_name, str):
  146. return AssertionError('A name must be a string.')
  147. execute('''
  148. INSERT OR IGNORE INTO ownership (user_id, ownable_id, amount)
  149. SELECT ?, (SELECT rowid FROM ownables WHERE name = ?), ?
  150. ''', (user_id, ownable_name, amount))
  151. def send_ownable(from_user_id, to_user_id, ownable_id, amount):
  152. connect()
  153. if amount < 0:
  154. raise AssertionError('Can not send negative amount')
  155. bank_id_ = bank_id()
  156. if from_user_id != bank_id_:
  157. execute('''
  158. UPDATE ownership
  159. SET amount = amount - ?
  160. WHERE user_id = ?
  161. AND ownable_id = ?
  162. ''', (amount, from_user_id, ownable_id,))
  163. own(to_user_id, ownable_name_by_id(ownable_id))
  164. if from_user_id != bank_id_ or ownable_id != currency_id():
  165. execute('''
  166. UPDATE ownership
  167. SET amount = amount + ?
  168. WHERE user_id = ?
  169. AND ownable_id = ?
  170. ''', (amount, to_user_id, ownable_id,))
  171. return True
  172. def valid_key(key):
  173. connect()
  174. execute('''
  175. SELECT key
  176. FROM keys
  177. WHERE used_by_user_id IS NULL
  178. AND key = ?
  179. ''', (key,))
  180. if current_cursor.fetchone():
  181. return True
  182. else:
  183. return False
  184. def new_session(user_id):
  185. connect()
  186. session_id = str(uuid.uuid4())
  187. execute('''
  188. INSERT INTO SESSIONS
  189. (user_id, session_id)
  190. VALUES (? , ?)
  191. ''', (user_id, session_id))
  192. return session_id
  193. def save_key(key):
  194. connect()
  195. execute('''
  196. INSERT INTO keys
  197. (key)
  198. VALUES (?)
  199. ''', (key,))
  200. def drop_old_sessions():
  201. connect()
  202. execute(''' -- no need to optimize this very well
  203. DELETE FROM sessions
  204. WHERE
  205. (SELECT COUNT(*) as newer
  206. FROM sessions s2
  207. WHERE user_id = s2.user_id
  208. AND rowid < s2.rowid) >= 10
  209. ''')
  210. def user_exists(username):
  211. connect()
  212. execute('''
  213. SELECT rowid
  214. FROM users
  215. WHERE username = ?
  216. ''', (username,))
  217. if current_cursor.fetchone():
  218. return True
  219. else:
  220. return False
  221. def get_user_id_by_session_id(session_id):
  222. connect()
  223. execute('''
  224. SELECT users.rowid
  225. FROM sessions, users
  226. WHERE sessions.session_id = ?
  227. AND users.rowid = sessions.user_id
  228. ''', (session_id,))
  229. ids = current_cursor.fetchone()
  230. if not ids:
  231. return False
  232. return ids[0]
  233. def get_user_id_by_name(username):
  234. connect()
  235. execute('''
  236. SELECT users.rowid
  237. FROM users
  238. WHERE username = ?
  239. ''', (username,))
  240. return current_cursor.fetchone()[0]
  241. def get_user_ownership(user_id):
  242. connect()
  243. execute('''
  244. SELECT
  245. ownables.name,
  246. ownership.amount,
  247. COALESCE (
  248. CASE -- sum score for each of the users ownables
  249. WHEN ownership.ownable_id = ? THEN 1
  250. ELSE (SELECT price
  251. FROM transactions
  252. WHERE ownable_id = ownership.ownable_id
  253. ORDER BY rowid DESC -- equivalent to ordering by dt
  254. LIMIT 1)
  255. END, 0) AS price,
  256. (SELECT MAX("limit")
  257. FROM orders, ownership o2
  258. WHERE o2.rowid = orders.ownership_id
  259. AND o2.ownable_id = ownership.ownable_id
  260. AND buy
  261. AND NOT stop_loss) AS bid,
  262. (SELECT MIN("limit")
  263. FROM orders, ownership o2
  264. WHERE o2.rowid = orders.ownership_id
  265. AND o2.ownable_id = ownership.ownable_id
  266. AND NOT buy
  267. AND NOT stop_loss) AS ask
  268. FROM ownership, ownables
  269. WHERE user_id = ?
  270. AND (ownership.amount >= 0.01 OR ownership.ownable_id = ?)
  271. AND ownership.ownable_id = ownables.rowid
  272. ORDER BY ownables.rowid ASC
  273. ''', (currency_id(), user_id, currency_id(),))
  274. return current_cursor.fetchall()
  275. def activate_key(key, user_id):
  276. connect()
  277. execute('''
  278. UPDATE keys
  279. SET used_by_user_id = ?
  280. WHERE used_by_user_id IS NULL
  281. AND key = ?
  282. ''', (user_id, key,))
  283. send_ownable(bank_id(), user_id, currency_id(), 1000)
  284. def bank_id():
  285. connect()
  286. execute('''
  287. SELECT users.rowid
  288. FROM users
  289. WHERE username = 'bank'
  290. ''')
  291. return current_cursor.fetchone()[0]
  292. def valid_session_id(session_id):
  293. connect()
  294. execute('''
  295. SELECT rowid
  296. FROM sessions
  297. WHERE session_id = ?
  298. ''', (session_id,))
  299. if current_cursor.fetchone():
  300. return True
  301. else:
  302. return False
  303. def get_user_orders(user_id):
  304. connect()
  305. execute('''
  306. SELECT
  307. CASE
  308. WHEN orders.buy THEN 'Buy'
  309. ELSE 'Sell'
  310. END,
  311. ownables.name,
  312. (orders.ordered_amount - orders.executed_amount) || '/' || orders.ordered_amount,
  313. orders."limit",
  314. CASE
  315. WHEN orders."limit" IS NULL THEN NULL
  316. WHEN orders.stop_loss THEN 'Yes'
  317. ELSE 'No'
  318. END,
  319. datetime(orders.expiry_dt, 'localtime'),
  320. orders.rowid
  321. FROM orders, ownables, ownership
  322. WHERE ownership.user_id = ?
  323. AND ownership.ownable_id = ownables.rowid
  324. AND orders.ownership_id = ownership.rowid
  325. ORDER BY ownables.name ASC, orders.stop_loss ASC, orders.buy DESC, orders."limit" ASC
  326. ''', (user_id,))
  327. return current_cursor.fetchall()
  328. def get_user_loans(user_id):
  329. connect()
  330. execute('''
  331. SELECT
  332. rowid,
  333. total_amount,
  334. amount,
  335. interest_rate
  336. FROM loans
  337. WHERE user_id is ?
  338. ORDER BY rowid ASC
  339. ''', (user_id,))
  340. return current_cursor.fetchall()
  341. def get_ownable_orders(user_id, ownable_id):
  342. connect()
  343. execute('''
  344. SELECT
  345. CASE
  346. WHEN ownership.user_id = ? THEN 'X'
  347. ELSE NULL
  348. END,
  349. CASE
  350. WHEN orders.buy THEN 'Buy'
  351. ELSE 'Sell'
  352. END,
  353. ownables.name,
  354. orders.ordered_amount - orders.executed_amount,
  355. orders."limit",
  356. datetime(orders.expiry_dt, 'localtime'),
  357. orders.rowid
  358. FROM orders, ownables, ownership
  359. WHERE ownership.ownable_id = ?
  360. AND ownership.ownable_id = ownables.rowid
  361. AND orders.ownership_id = ownership.rowid
  362. AND (orders.stop_loss IS NULL OR NOT orders.stop_loss)
  363. ORDER BY ownables.name ASC, orders.stop_loss ASC, orders.buy DESC, orders."limit" ASC
  364. ''', (user_id, ownable_id,))
  365. return current_cursor.fetchall()
  366. def sell_ordered_amount(user_id, ownable_id):
  367. connect()
  368. execute('''
  369. SELECT COALESCE(SUM(orders.ordered_amount - orders.executed_amount),0)
  370. FROM orders, ownership
  371. WHERE ownership.rowid = orders.ownership_id
  372. AND ownership.user_id = ?
  373. AND ownership.ownable_id = ?
  374. AND NOT orders.buy
  375. ''', (user_id, ownable_id))
  376. return current_cursor.fetchone()[0]
  377. def available_amount(user_id, ownable_id):
  378. connect()
  379. execute('''
  380. SELECT amount
  381. FROM ownership
  382. WHERE user_id = ?
  383. AND ownable_id = ?
  384. ''', (user_id, ownable_id))
  385. return current_cursor.fetchone()[0] - sell_ordered_amount(user_id, ownable_id)
  386. def user_has_at_least_available(amount, user_id, ownable_id):
  387. connect()
  388. if not isinstance(amount, float) and not isinstance(amount, int):
  389. # comparison of float with strings does not work so well in sql
  390. raise AssertionError()
  391. execute('''
  392. SELECT rowid
  393. FROM ownership
  394. WHERE user_id = ?
  395. AND ownable_id = ?
  396. AND amount - ? >= ?
  397. ''', (user_id, ownable_id, sell_ordered_amount(user_id, ownable_id), amount))
  398. if current_cursor.fetchone():
  399. return True
  400. else:
  401. return False
  402. def news():
  403. connect()
  404. execute('''
  405. SELECT dt, title FROM
  406. (SELECT *, rowid
  407. FROM news
  408. ORDER BY rowid DESC -- equivalent to order by dt
  409. LIMIT 20) n
  410. ORDER BY rowid ASC -- equivalent to order by dt
  411. ''')
  412. return current_cursor.fetchall()
  413. def ownable_name_exists(name):
  414. connect()
  415. execute('''
  416. SELECT rowid
  417. FROM ownables
  418. WHERE name = ?
  419. ''', (name,))
  420. if current_cursor.fetchone():
  421. return True
  422. else:
  423. return False
  424. def new_stock(expiry, name=None):
  425. connect()
  426. while name is None:
  427. name = random_chars(6)
  428. if ownable_name_exists(name):
  429. name = None
  430. execute('''
  431. INSERT INTO ownables(name)
  432. VALUES (?)
  433. ''', (name,))
  434. new_news('A new stock can now be bought: ' + name)
  435. if random.getrandbits(1):
  436. new_news('Experts expect the price of ' + name + ' to fall')
  437. else:
  438. new_news('Experts expect the price of ' + name + ' to rise')
  439. amount = random.randrange(100, 10000)
  440. price = random.randrange(10000, 20000) / amount
  441. ownable_id = ownable_id_by_name(name)
  442. own(bank_id(), name, amount)
  443. bank_order(False,
  444. ownable_id,
  445. price,
  446. amount,
  447. expiry)
  448. return name
  449. def ownable_id_by_name(ownable_name):
  450. connect()
  451. execute('''
  452. SELECT rowid
  453. FROM ownables
  454. WHERE name = ?
  455. ''', (ownable_name,))
  456. return current_cursor.fetchone()[0]
  457. def get_ownership_id(ownable_id, user_id):
  458. connect()
  459. execute('''
  460. SELECT rowid
  461. FROM ownership
  462. WHERE ownable_id = ?
  463. AND user_id = ?
  464. ''', (ownable_id, user_id,))
  465. return current_cursor.fetchone()[0]
  466. def currency_id():
  467. connect()
  468. execute('''
  469. SELECT rowid
  470. FROM ownables
  471. WHERE name = ?
  472. ''', (CURRENCY_NAME,))
  473. return current_cursor.fetchone()[0]
  474. def user_money(user_id):
  475. connect()
  476. execute('''
  477. SELECT amount
  478. FROM ownership
  479. WHERE user_id = ?
  480. AND ownable_id = ?
  481. ''', (user_id, currency_id()))
  482. return current_cursor.fetchone()[0]
  483. def delete_order(order_id, new_order_status):
  484. connect()
  485. execute('''
  486. INSERT INTO order_history
  487. (ownership_id, buy, "limit", ordered_amount, executed_amount, expiry_dt, status, order_id)
  488. SELECT
  489. ownership_id,
  490. buy,
  491. "limit",
  492. ordered_amount,
  493. executed_amount,
  494. expiry_dt,
  495. ?,
  496. rowid
  497. FROM orders
  498. WHERE rowid = ?
  499. ''', (new_order_status, order_id,))
  500. execute('''
  501. DELETE FROM orders
  502. WHERE rowid = ?
  503. ''', (order_id,))
  504. def current_value(ownable_id):
  505. connect()
  506. if ownable_id == currency_id():
  507. return 1
  508. execute('''SELECT price
  509. FROM transactions
  510. WHERE ownable_id = ?
  511. ORDER BY rowid DESC -- equivalent to order by dt
  512. LIMIT 1
  513. ''', (ownable_id,))
  514. return current_cursor.fetchone()[0]
  515. def execute_orders(ownable_id):
  516. connect()
  517. orders_traded = False
  518. while True:
  519. # find order to execute
  520. execute('''
  521. -- two best orders
  522. SELECT * FROM (
  523. SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
  524. FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
  525. WHERE buy_order.buy AND NOT sell_order.buy
  526. AND buyer.rowid = buy_order.ownership_id
  527. AND seller.rowid = sell_order.ownership_id
  528. AND buyer.ownable_id = ?
  529. AND seller.ownable_id = ?
  530. AND buy_order."limit" IS NULL
  531. AND sell_order."limit" IS NULL
  532. ORDER BY buy_order.rowid ASC,
  533. sell_order.rowid ASC
  534. LIMIT 1)
  535. UNION ALL -- best buy orders
  536. SELECT * FROM (
  537. SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
  538. FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
  539. WHERE buy_order.buy AND NOT sell_order.buy
  540. AND buyer.rowid = buy_order.ownership_id
  541. AND seller.rowid = sell_order.ownership_id
  542. AND buyer.ownable_id = ?
  543. AND seller.ownable_id = ?
  544. AND buy_order."limit" IS NULL
  545. AND sell_order."limit" IS NOT NULL
  546. AND NOT sell_order.stop_loss
  547. ORDER BY sell_order."limit" ASC,
  548. buy_order.rowid ASC,
  549. sell_order.rowid ASC
  550. LIMIT 1)
  551. UNION ALL -- best sell orders
  552. SELECT * FROM (
  553. SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
  554. FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
  555. WHERE buy_order.buy AND NOT sell_order.buy
  556. AND buyer.rowid = buy_order.ownership_id
  557. AND seller.rowid = sell_order.ownership_id
  558. AND buyer.ownable_id = ?
  559. AND seller.ownable_id = ?
  560. AND buy_order."limit" IS NOT NULL
  561. AND NOT buy_order.stop_loss
  562. AND sell_order."limit" IS NULL
  563. ORDER BY buy_order."limit" DESC,
  564. buy_order.rowid ASC,
  565. sell_order.rowid ASC
  566. LIMIT 1)
  567. UNION ALL -- both limit orders
  568. SELECT * FROM (
  569. SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
  570. FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
  571. WHERE buy_order.buy AND NOT sell_order.buy
  572. AND buyer.rowid = buy_order.ownership_id
  573. AND seller.rowid = sell_order.ownership_id
  574. AND buyer.ownable_id = ?
  575. AND seller.ownable_id = ?
  576. AND buy_order."limit" IS NOT NULL
  577. AND sell_order."limit" IS NOT NULL
  578. AND sell_order."limit" <= buy_order."limit"
  579. AND NOT sell_order.stop_loss
  580. AND NOT buy_order.stop_loss
  581. ORDER BY buy_order."limit" DESC,
  582. sell_order."limit" ASC,
  583. buy_order.rowid ASC,
  584. sell_order.rowid ASC
  585. LIMIT 1)
  586. LIMIT 1
  587. ''', tuple(ownable_id for _ in range(8)))
  588. matching_orders = current_cursor.fetchone()
  589. # return type: (ownership_id,buy,limit,stop_loss,ordered_amount,executed_amount,expiry_dt,
  590. # ownership_id,buy,limit,stop_loss,ordered_amount,executed_amount,expiry_dt,
  591. # user_id,user_id,rowid,rowid)
  592. if not matching_orders:
  593. if not orders_traded:
  594. break
  595. # check if the trading bot has any new offers to make
  596. new_order_was_placed = trading_bot.notify_order_traded(ownable_id)
  597. if new_order_was_placed:
  598. orders_traded = False
  599. continue
  600. else:
  601. break
  602. buy_ownership_id, _, buy_limit, _, buy_order_amount, buy_executed_amount, buy_expiry_dt, \
  603. sell_ownership_id, _, sell_limit, _, sell_order_amount, sell_executed_amount, sell_expiry_dt, \
  604. buyer_id, seller_id, buy_order_id, sell_order_id \
  605. = matching_orders
  606. if buy_limit is None and sell_limit is None:
  607. price = current_value(ownable_id)
  608. elif buy_limit is None:
  609. price = sell_limit
  610. elif sell_limit is None:
  611. price = buy_limit
  612. else: # both not NULL
  613. # the price of the older order is used, just like in the real exchange
  614. if buy_order_id < sell_order_id:
  615. price = buy_limit
  616. else:
  617. price = sell_limit
  618. buyer_money = user_money(buyer_id)
  619. def _my_division(x, y):
  620. try:
  621. return floor(x / y)
  622. except ZeroDivisionError:
  623. return float('Inf')
  624. amount = min(buy_order_amount - buy_executed_amount,
  625. sell_order_amount - sell_executed_amount,
  626. _my_division(buyer_money, price))
  627. if amount == 0: # probable because buyer has not enough money
  628. delete_order(buy_order_id, 'Unable to pay')
  629. continue
  630. buy_order_finished = (buy_order_amount - buy_executed_amount - amount <= 0) or (
  631. buyer_money - amount * price < price)
  632. sell_order_finished = (sell_order_amount - sell_executed_amount - amount <= 0)
  633. if price < 0 or amount <= 0: # price of 0 is possible though unlikely
  634. return AssertionError()
  635. # actually execute the order, but the bank does not send or receive anything
  636. send_ownable(buyer_id, seller_id, currency_id(), price * amount)
  637. send_ownable(seller_id, buyer_id, ownable_id, amount)
  638. # update order execution state
  639. execute('''
  640. UPDATE orders
  641. SET executed_amount = executed_amount + ?
  642. WHERE rowid = ?
  643. OR rowid = ?
  644. ''', (amount, buy_order_id, sell_order_id))
  645. if buy_order_finished:
  646. delete_order(buy_order_id, 'Executed')
  647. orders_traded = True
  648. if sell_order_finished:
  649. delete_order(sell_order_id, 'Executed')
  650. orders_traded = True
  651. if seller_id != buyer_id: # prevent showing self-transactions
  652. execute('''
  653. INSERT INTO transactions
  654. (price, ownable_id, amount, buyer_id, seller_id)
  655. VALUES(?, ?, ?, ?, ?)
  656. ''', (price, ownable_id, amount, buyer_id, seller_id))
  657. # trigger stop-loss orders
  658. if buyer_id != seller_id:
  659. execute('''
  660. UPDATE orders
  661. SET stop_loss = NULL,
  662. "limit" = NULL
  663. WHERE stop_loss IS NOT NULL
  664. AND stop_loss
  665. AND ? IN (SELECT ownable_id FROM ownership WHERE rowid = ownership_id)
  666. AND ((buy AND "limit" < ?) OR (NOT buy AND "limit" > ?))
  667. ''', (ownable_id, price, price,))
  668. def ownable_id_by_ownership_id(ownership_id):
  669. connect()
  670. execute('''
  671. SELECT ownable_id
  672. FROM ownership
  673. WHERE rowid = ?
  674. ''', (ownership_id,))
  675. return current_cursor.fetchone()[0]
  676. def ownable_name_by_id(ownable_id):
  677. connect()
  678. execute('''
  679. SELECT name
  680. FROM ownables
  681. WHERE rowid = ?
  682. ''', (ownable_id,))
  683. return current_cursor.fetchone()[0]
  684. def bank_order(buy, ownable_id, limit, amount, expiry):
  685. if not limit:
  686. raise AssertionError('The bank does not give away anything.')
  687. place_order(buy,
  688. get_ownership_id(ownable_id, bank_id()),
  689. limit,
  690. False,
  691. amount,
  692. expiry)
  693. ownable_name = ownable_name_by_id(ownable_id)
  694. new_news('External investors are selling ' + ownable_name + ' atm')
  695. def current_db_time(): # might differ from datetime.datetime.now() for time zone reasons
  696. connect()
  697. execute('''
  698. SELECT datetime('now')
  699. ''')
  700. return current_cursor.fetchone()[0]
  701. def place_order(buy, ownership_id, limit, stop_loss, amount, expiry):
  702. connect()
  703. execute('''
  704. INSERT INTO orders
  705. (buy, ownership_id, "limit", stop_loss, ordered_amount, expiry_dt)
  706. VALUES (?, ?, ?, ?, ?, ?)
  707. ''', (buy, ownership_id, limit, stop_loss, amount, expiry))
  708. execute_orders(ownable_id_by_ownership_id(ownership_id))
  709. return True
  710. def trades_on(ownable_id, limit):
  711. connect()
  712. execute('''
  713. SELECT datetime(dt,'localtime'), amount, price
  714. FROM transactions
  715. WHERE ownable_id = ?
  716. ORDER BY rowid DESC -- equivalent to order by dt
  717. LIMIT ?
  718. ''', (ownable_id, limit,))
  719. return current_cursor.fetchall()
  720. def trades(user_id, limit):
  721. connect()
  722. execute('''
  723. SELECT
  724. (CASE WHEN seller_id = ? THEN 'Sell' ELSE 'Buy' END),
  725. (SELECT name FROM ownables WHERE rowid = transactions.ownable_id),
  726. amount,
  727. price,
  728. datetime(dt,'localtime')
  729. FROM transactions
  730. WHERE seller_id = ? OR buyer_id = ?
  731. ORDER BY rowid DESC -- equivalent to order by dt
  732. LIMIT ?
  733. ''', (user_id, user_id, user_id, limit,))
  734. return current_cursor.fetchall()
  735. def drop_expired_orders():
  736. connect()
  737. execute('''
  738. SELECT rowid, ownership_id, * FROM orders
  739. WHERE expiry_dt < DATETIME('now')
  740. ''')
  741. data = current_cursor.fetchall()
  742. for order in data:
  743. order_id = order[0]
  744. delete_order(order_id, 'Expired')
  745. return data
  746. def generate_keys(count=1):
  747. # source https://stackoverflow.com/questions/17049308/python-3-3-serial-key-generator-list-problems
  748. for i in range(count):
  749. key = '-'.join(random_chars(5) for _ in range(5))
  750. save_key(key)
  751. print(key)
  752. def user_has_order_with_id(session_id, order_id):
  753. connect()
  754. execute('''
  755. SELECT orders.rowid
  756. FROM orders, ownership, sessions
  757. WHERE orders.rowid = ?
  758. AND sessions.session_id = ?
  759. AND sessions.user_id = ownership.user_id
  760. AND ownership.rowid = orders.ownership_id
  761. ''', (order_id, session_id,))
  762. if current_cursor.fetchone():
  763. return True
  764. else:
  765. return False
  766. def leaderboard():
  767. connect()
  768. execute('''
  769. SELECT *
  770. FROM ( -- one score for each user
  771. SELECT
  772. username,
  773. SUM(CASE -- sum score for each of the users ownables
  774. WHEN ownership.ownable_id = ? THEN ownership.amount
  775. ELSE ownership.amount * (SELECT price
  776. FROM transactions
  777. WHERE ownable_id = ownership.ownable_id
  778. ORDER BY rowid DESC -- equivalent to ordering by dt
  779. LIMIT 1)
  780. END
  781. ) score
  782. FROM users, ownership
  783. WHERE ownership.user_id = users.rowid
  784. AND users.username != 'bank'
  785. GROUP BY users.rowid
  786. ) AS scores
  787. ORDER BY score DESC
  788. LIMIT 50
  789. ''', (currency_id(),))
  790. return current_cursor.fetchall()
  791. def user_wealth(user_id):
  792. connect()
  793. execute('''
  794. SELECT (
  795. SELECT COALESCE(SUM(
  796. CASE -- sum score for each of the users ownables
  797. WHEN ownership.ownable_id = ? THEN ownership.amount
  798. ELSE ownership.amount * (SELECT price
  799. FROM transactions
  800. WHERE ownable_id = ownership.ownable_id
  801. ORDER BY rowid DESC -- equivalent to ordering by dt
  802. LIMIT 1)
  803. END
  804. ), 0)
  805. FROM ownership
  806. WHERE ownership.user_id = ?)
  807. -
  808. ( SELECT COALESCE(SUM(
  809. amount
  810. ), 0)
  811. FROM loans
  812. WHERE loans.user_id = ?)
  813. ''', (currency_id(), user_id, user_id,))
  814. return current_cursor.fetchone()[0]
  815. def change_password(session_id, password, salt):
  816. connect()
  817. execute('''
  818. UPDATE users
  819. SET password = ?, salt= ?
  820. WHERE rowid = (SELECT user_id FROM sessions WHERE sessions.session_id = ?)
  821. ''', (password, salt, session_id,))
  822. def sign_out_user(session_id):
  823. connect()
  824. execute('''
  825. DELETE FROM sessions
  826. WHERE user_id = (SELECT user_id FROM sessions s2 WHERE s2.session_id = ?)
  827. ''', (session_id,))
  828. def delete_user(user_id):
  829. connect()
  830. execute('''
  831. DELETE FROM sessions
  832. WHERE user_id = ?
  833. ''', (user_id,))
  834. execute('''
  835. DELETE FROM orders
  836. WHERE ownership_id IN (
  837. SELECT rowid FROM ownership WHERE user_id = ?)
  838. ''', (user_id,))
  839. execute('''
  840. DELETE FROM ownership
  841. WHERE user_id = ?
  842. ''', (user_id,))
  843. execute('''
  844. DELETE FROM keys
  845. WHERE used_by_user_id = ?
  846. ''', (user_id,))
  847. execute('''
  848. INSERT INTO news(title)
  849. VALUES ((SELECT username FROM users WHERE rowid = ?) || ' retired.')
  850. ''', (user_id,))
  851. execute('''
  852. DELETE FROM users
  853. WHERE rowid = ?
  854. ''', (user_id,))
  855. def delete_ownable(ownable_id):
  856. connect()
  857. execute('''
  858. DELETE FROM transactions
  859. WHERE ownable_id = ?
  860. ''', (ownable_id,))
  861. execute('''
  862. DELETE FROM orders
  863. WHERE ownership_id IN (
  864. SELECT rowid FROM ownership WHERE ownable_id = ?)
  865. ''', (ownable_id,))
  866. execute('''
  867. DELETE FROM order_history
  868. WHERE ownership_id IN (
  869. SELECT rowid FROM ownership WHERE ownable_id = ?)
  870. ''', (ownable_id,))
  871. # only delete empty ownerships
  872. execute('''
  873. DELETE FROM ownership
  874. WHERE ownable_id = ?
  875. AND amount = 0
  876. ''', (ownable_id,))
  877. execute('''
  878. INSERT INTO news(title)
  879. VALUES ((SELECT name FROM ownables WHERE rowid = ?) || ' can not be traded any more.')
  880. ''', (ownable_id,))
  881. execute('''
  882. DELETE FROM ownables
  883. WHERE rowid = ?
  884. ''', (ownable_id,))
  885. def hash_all_users_passwords():
  886. connect()
  887. execute('''
  888. SELECT rowid, password, salt
  889. FROM users
  890. ''')
  891. users = current_cursor.fetchall()
  892. for user_id, pw, salt in users:
  893. valid_hash = True
  894. try:
  895. sha256_crypt.verify('password' + salt, pw)
  896. except ValueError:
  897. valid_hash = False
  898. if valid_hash:
  899. raise AssertionError('There is already a hashed password in the database! Be careful what you are doing!')
  900. pw = sha256_crypt.encrypt(pw + salt)
  901. execute('''
  902. UPDATE users
  903. SET password = ?
  904. WHERE rowid = ?
  905. ''', (pw, user_id,))
  906. def new_news(message):
  907. connect()
  908. execute('''
  909. INSERT INTO news(title)
  910. VALUES (?)
  911. ''', (message,))
  912. def abs_spread(ownable_id):
  913. connect()
  914. execute('''
  915. SELECT
  916. (SELECT MAX("limit")
  917. FROM orders, ownership
  918. WHERE ownership.rowid = orders.ownership_id
  919. AND ownership.ownable_id = ?
  920. AND buy
  921. AND NOT stop_loss) AS bid,
  922. (SELECT MIN("limit")
  923. FROM orders, ownership
  924. WHERE ownership.rowid = orders.ownership_id
  925. AND ownership.ownable_id = ?
  926. AND NOT buy
  927. AND NOT stop_loss) AS ask
  928. ''', (ownable_id, ownable_id,))
  929. return current_cursor.fetchone()
  930. def ownables():
  931. connect()
  932. execute('''
  933. SELECT name, course,
  934. (SELECT SUM(amount)
  935. FROM ownership
  936. WHERE ownership.ownable_id = ownables_with_course.rowid) market_size
  937. FROM (SELECT
  938. name, ownables.rowid,
  939. CASE WHEN ownables.rowid = ?
  940. THEN 1
  941. ELSE (SELECT price
  942. FROM transactions
  943. WHERE ownable_id = ownables.rowid
  944. ORDER BY rowid DESC -- equivalent to ordering by dt
  945. LIMIT 1) END course
  946. FROM ownables) ownables_with_course
  947. ''', (currency_id(),))
  948. data = current_cursor.fetchall()
  949. for idx in range(len(data)):
  950. # compute market cap
  951. row = data[idx]
  952. if row[1] is None:
  953. market_cap = None
  954. elif row[2] is None:
  955. market_cap = None
  956. else:
  957. market_cap = row[1] * row[2]
  958. data[idx] = (row[0], row[1], market_cap)
  959. return data
  960. def reset_bank():
  961. connect()
  962. execute('''
  963. DELETE FROM ownership
  964. WHERE user_id = ?
  965. ''', (bank_id(),))
  966. def cleanup():
  967. global connections
  968. global current_connection
  969. global current_cursor
  970. global current_db_name
  971. global current_user_id
  972. for name in connections:
  973. connections[name].rollback()
  974. connections[name].close()
  975. connections = []
  976. current_connection = None
  977. current_cursor = None
  978. current_db_name = None
  979. current_user_id = None
  980. def ownable_ids():
  981. execute('''
  982. SELECT rowid FROM ownables
  983. ''')
  984. return [ownable_id[0] for ownable_id in current_cursor.fetchall()]
  985. def get_old_orders(user_id, include_executed, include_canceled, limit):
  986. connect()
  987. execute('''
  988. SELECT
  989. (CASE WHEN order_history.buy THEN 'Buy' ELSE 'Sell' END),
  990. ownables.name,
  991. (order_history.ordered_amount - order_history.executed_amount) || '/' || order_history.ordered_amount,
  992. order_history."limit",
  993. order_history.expiry_dt,
  994. order_history.order_id,
  995. order_history.status
  996. FROM order_history, ownership, ownables
  997. WHERE ownership.user_id = ?
  998. AND ownership.rowid = order_history.ownership_id
  999. AND ownables.rowid = ownership.ownable_id
  1000. AND (
  1001. (order_history.status = 'Executed' AND ?)
  1002. OR
  1003. ((order_history.status = 'Expired' OR order_history.status = 'Canceled') AND ?)
  1004. )
  1005. ORDER BY order_history.rowid DESC -- equivalent to ordering by creation time
  1006. LIMIT ?
  1007. ''', (user_id, include_executed, include_canceled, limit))
  1008. return current_cursor.fetchall()
  1009. def user_has_banking_license(user_id):
  1010. execute('''
  1011. SELECT EXISTS (SELECT * FROM banks WHERE user_id = ?)
  1012. ''', (user_id,))
  1013. return current_cursor.fetchone()[0]
  1014. def global_control_value(value_name):
  1015. execute('''
  1016. SELECT value
  1017. FROM global_control_values
  1018. WHERE value_name = ?
  1019. AND dt = (SELECT MAX(dt) FROM global_control_values WHERE value_name = ?)
  1020. ''', (value_name, value_name,))
  1021. return current_cursor.fetchone()[0]
  1022. def global_control_values():
  1023. execute('''
  1024. SELECT value_name, value
  1025. FROM global_control_values v1
  1026. WHERE dt IN (SELECT MAX(dt) FROM global_control_values v2 GROUP BY v2.value_name)
  1027. ''')
  1028. return {
  1029. row[0]: row[1] for row in current_cursor.fetchall()
  1030. }
  1031. def assign_banking_licence(user_id):
  1032. execute('''
  1033. INSERT INTO banks(user_id)
  1034. VALUES (?)
  1035. ''', (user_id,))
  1036. def pay_loan_interest():
  1037. current_dt = execute("SELECT strftime('%s', CURRENT_TIMESTAMP)").fetchone()[0]
  1038. sec_per_year = 3600 * 24 * 365
  1039. interests = execute('''
  1040. SELECT
  1041. SUM(amount * (POWER(1 + interest_rate,
  1042. (CAST(? AS FLOAT) - last_interest_pay_dt) / ?) - 1)
  1043. ) AS interest_since_last_pay,
  1044. user_id
  1045. FROM loans
  1046. WHERE ? - last_interest_pay_dt > ?
  1047. GROUP BY user_id
  1048. ''', (current_dt, sec_per_year, current_dt, MIN_INTEREST_INTERVAL)).fetchall()
  1049. executemany(f'''
  1050. UPDATE ownership
  1051. SET amount = amount - ?
  1052. WHERE ownable_id = {currency_id()}
  1053. AND user_id = ?
  1054. ''', interests)
  1055. # noinspection SqlWithoutWhere
  1056. execute('''
  1057. UPDATE loans
  1058. SET last_interest_pay_dt = ?
  1059. WHERE ? - last_interest_pay_dt > ?
  1060. ''', (current_dt, current_dt, MIN_INTEREST_INTERVAL,))
  1061. def loan_recipient_id(loan_id):
  1062. execute('''
  1063. SELECT user_id
  1064. FROM loans
  1065. WHERE rowid = ?
  1066. ''', (loan_id,))
  1067. return current_cursor.fetchone()[0]
  1068. def loan_remaining_amount(loan_id):
  1069. execute('''
  1070. SELECT amount
  1071. FROM loans
  1072. WHERE rowid = ?
  1073. ''', (loan_id,))
  1074. return current_cursor.fetchone()[0]
  1075. def repay_loan(loan_id, amount, known_user_id=None):
  1076. if known_user_id is None:
  1077. user_id = loan_recipient_id(loan_id)
  1078. else:
  1079. user_id = known_user_id
  1080. send_ownable(user_id, bank_id(), currency_id(), amount)
  1081. execute('''
  1082. UPDATE loans
  1083. SET amount = amount - ?
  1084. WHERE rowid = ?
  1085. ''', (amount, loan_id,))
  1086. if loan_remaining_amount(loan_id) == 0:
  1087. execute('''
  1088. DELETE FROM loans
  1089. WHERE rowid = ?
  1090. ''', (loan_id,))
  1091. def take_out_personal_loan(user_id, amount):
  1092. execute('''
  1093. INSERT INTO loans(user_id, total_amount, amount, interest_rate)
  1094. VALUES (?, ?, ?, ?)
  1095. ''', (user_id, amount, amount, global_control_value('personal_loan_interest_rate')))
  1096. send_ownable(bank_id(), user_id, currency_id(), amount)
  1097. def loan_id_exists(loan_id):
  1098. execute('''
  1099. SELECT EXISTS (SELECT * FROM loans WHERE rowid = ?)
  1100. ''', (loan_id,))
  1101. return current_cursor.fetchone()[0]