1
1

model.py 33 KB

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