model.py 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727
  1. import datetime
  2. import random
  3. import re
  4. import sqlite3 as db
  5. import sys
  6. import uuid
  7. from math import floor
  8. import db_setup
  9. from game import CURRENCY_NAME
  10. from util import debug, random_chars
  11. connection: db.Connection = None
  12. cursor: db.Cursor = None
  13. db_name = None
  14. def query_save_name():
  15. global db_name
  16. if debug:
  17. db_name = 'test.db'
  18. return
  19. while True:
  20. save_name = input('Name of the savegame: ')
  21. if re.match(r"[A-Za-z0-9.-]{0,50}", save_name):
  22. db_name = save_name + '.db'
  23. return
  24. else:
  25. print('Must match "[A-Za-z0-9.-]{0,50}"')
  26. def connect(reconnect=False):
  27. global connection
  28. global cursor
  29. global db_name
  30. if reconnect:
  31. connection.commit()
  32. connection.close()
  33. cursor = None
  34. connection = None
  35. db_name = None
  36. if connection is None or cursor is None:
  37. query_save_name()
  38. try:
  39. connection = db.connect(db_name)
  40. cursor = connection.cursor()
  41. except db.Error as e:
  42. print("Database error %s:" % e.args[0])
  43. sys.exit(1)
  44. # finally:
  45. # if con is not None:
  46. # con.close()
  47. def setup():
  48. connect()
  49. db_setup.setup(cursor)
  50. connection.commit()
  51. def login(username, password):
  52. connect()
  53. # do not allow login as bank
  54. if password == '':
  55. return None
  56. cursor.execute('''
  57. SELECT rowid
  58. FROM users
  59. WHERE username = ?
  60. AND password = ?
  61. ''', (username, password))
  62. user_id = cursor.fetchone()
  63. if user_id:
  64. return new_session(user_id)
  65. else:
  66. return None
  67. def register(username, password, game_key):
  68. connect()
  69. if username == '':
  70. return False
  71. if password == '':
  72. return False
  73. cursor.execute('''
  74. INSERT INTO users
  75. (username, password)
  76. VALUES (? , ?)
  77. ''', (username, password))
  78. own(get_user_id_by_name(username), CURRENCY_NAME)
  79. if game_key != '':
  80. if valid_key(game_key):
  81. activate_key(game_key, get_user_id_by_name(username))
  82. return True
  83. def own(user_id, ownable_name):
  84. if not isinstance(ownable_name, str):
  85. return AssertionError('A name must be a string.')
  86. cursor.execute('''
  87. WITH one_ownable_id AS (SELECT rowid FROM ownables WHERE name = ?),
  88. one_user_id AS (SELECT ?)
  89. INSERT INTO ownership (user_id, ownable_id)
  90. SELECT *
  91. FROM one_user_id, one_ownable_id
  92. WHERE NOT EXISTS (
  93. SELECT * FROM ownership
  94. WHERE ownership.user_id IN one_user_id
  95. AND ownership.ownable_id IN one_ownable_id
  96. )
  97. ''', (ownable_name, user_id,))
  98. def send_ownable(from_user_id, to_user_id, ownable_name, amount):
  99. connect()
  100. if amount < 0:
  101. return False
  102. if from_user_id != bank_id():
  103. cursor.execute('''
  104. UPDATE ownership
  105. SET amount = amount - ?
  106. WHERE user_id = ?
  107. AND ownable_id = (SELECT rowid FROM ownables WHERE name = ?)
  108. ''', (amount, from_user_id, ownable_name,))
  109. cursor.execute('''
  110. UPDATE ownership
  111. SET amount = amount + ?
  112. WHERE user_id = ?
  113. AND ownable_id = (SELECT rowid FROM ownables WHERE name = ?)
  114. ''', (amount, to_user_id, ownable_name))
  115. return True
  116. def valid_key(key):
  117. connect()
  118. cursor.execute('''
  119. SELECT key
  120. FROM keys
  121. WHERE used_by_user_id IS NULL
  122. AND key = ?
  123. ''', (key,))
  124. if cursor.fetchone():
  125. return True
  126. else:
  127. return False
  128. def new_session(user_id):
  129. connect()
  130. session_id = str(uuid.uuid4())
  131. cursor.execute('''
  132. INSERT INTO SESSIONS
  133. (user_id, session_id)
  134. VALUES (? , ?)
  135. ''', (user_id[0], session_id))
  136. return session_id
  137. def save_key(key):
  138. connect()
  139. cursor.execute('''
  140. INSERT INTO keys
  141. (key)
  142. VALUES (?)
  143. ''', (key,))
  144. def drop_old_sessions():
  145. connect()
  146. cursor.execute('''
  147. DELETE FROM sessions s1
  148. WHERE
  149. (SELECT COUNT(*) as newer
  150. FROM sessions s2
  151. WHERE s1.user_id = s2.user_id
  152. AND s1.rowid < s2.rowid) >= 10
  153. ''')
  154. def user_exists(username):
  155. connect()
  156. cursor.execute('''
  157. SELECT rowid
  158. FROM users
  159. WHERE username = ?
  160. ''', (username,))
  161. if cursor.fetchone():
  162. return True
  163. else:
  164. return False
  165. def unused_keys():
  166. connect()
  167. cursor.execute('''
  168. SELECT key
  169. FROM keys
  170. WHERE used_by_user_id IS NULL
  171. ''')
  172. return [str(key[0]).strip().upper() for key in cursor.fetchall()]
  173. def get_user_id_by_session_id(session_id):
  174. connect()
  175. cursor.execute('''
  176. SELECT users.rowid
  177. FROM sessions, users
  178. WHERE sessions.session_id = ?
  179. AND users.rowid = sessions.user_id
  180. ''', (session_id,))
  181. ids = cursor.fetchone()
  182. if not ids:
  183. return False
  184. return ids[0]
  185. def get_user_id_by_name(username):
  186. connect()
  187. cursor.execute('''
  188. SELECT users.rowid
  189. FROM users
  190. WHERE username = ?
  191. ''', (username,))
  192. return cursor.fetchone()[0]
  193. def get_user_ownership(user_id):
  194. connect()
  195. cursor.execute('''
  196. SELECT ownables.name, ownership.amount
  197. FROM ownership, ownables
  198. WHERE user_id = ?
  199. AND ownership.ownable_id = ownables.rowid
  200. ''', (user_id,))
  201. return cursor.fetchall()
  202. def activate_key(key, user_id):
  203. connect()
  204. cursor.execute('''
  205. UPDATE keys
  206. SET used_by_user_id = ?
  207. WHERE used_by_user_id IS NULL
  208. AND key = ?
  209. ''', (user_id, key,))
  210. send_ownable(bank_id(), user_id, CURRENCY_NAME, 1000)
  211. def bank_id():
  212. connect()
  213. cursor.execute('''
  214. SELECT users.rowid
  215. FROM users
  216. WHERE username = 'bank'
  217. ''')
  218. return cursor.fetchone()[0]
  219. def valid_session_id(session_id):
  220. connect()
  221. cursor.execute('''
  222. SELECT rowid
  223. FROM sessions
  224. WHERE session_id = ?
  225. ''', (session_id,))
  226. if cursor.fetchone():
  227. return True
  228. else:
  229. return False
  230. def get_user_orders(user_id):
  231. connect()
  232. cursor.execute('''
  233. SELECT
  234. CASE
  235. WHEN orders.buy THEN 'Buy'
  236. ELSE 'Sell'
  237. END,
  238. ownables.name,
  239. orders.ordered_amount - orders.executed_amount,
  240. orders."limit",
  241. CASE
  242. WHEN orders."limit" IS NULL THEN NULL
  243. WHEN orders.stop_loss THEN 'Yes'
  244. ELSE 'No'
  245. END,
  246. orders.ordered_amount,
  247. datetime(orders.expiry_dt),
  248. orders.rowid
  249. FROM orders, ownables, ownership
  250. WHERE ownership.user_id = ?
  251. AND ownership.ownable_id = ownables.rowid
  252. AND orders.ownership_id = ownership.rowid
  253. ORDER BY ownables.name ASC, orders.stop_loss ASC, orders.buy DESC, orders."limit" ASC
  254. ''', (user_id,))
  255. return cursor.fetchall()
  256. def get_ownable_orders(ownable_id):
  257. connect()
  258. cursor.execute('''
  259. SELECT
  260. CASE
  261. WHEN orders.buy THEN 'Buy'
  262. ELSE 'Sell'
  263. END,
  264. ownables.name,
  265. orders.ordered_amount - orders.executed_amount,
  266. orders."limit",
  267. CASE
  268. WHEN orders."limit" IS NULL THEN NULL
  269. WHEN orders.stop_loss THEN 'Yes'
  270. ELSE 'No'
  271. END,
  272. datetime(orders.expiry_dt),
  273. orders.rowid
  274. FROM orders, ownables, ownership
  275. WHERE ownership.ownable_id = ?
  276. AND ownership.ownable_id = ownables.rowid
  277. AND orders.ownership_id = ownership.rowid
  278. ORDER BY ownables.name ASC, orders.stop_loss ASC, orders.buy DESC, orders."limit" ASC
  279. ''', (ownable_id,))
  280. return cursor.fetchall()
  281. def sell_ordered_amount(user_id, ownable_id):
  282. connect()
  283. # if ownable_id == currency_id():
  284. # return 0
  285. cursor.execute('''
  286. SELECT SUM(orders.ordered_amount - orders.executed_amount)
  287. FROM orders, ownership
  288. WHERE ownership.rowid = orders.ownership_id
  289. AND ownership.user_id = ?
  290. AND ownership.ownable_id = ?
  291. AND NOT orders.buy
  292. ''', (user_id, ownable_id))
  293. return cursor.fetchone()[0]
  294. def user_owns_at_least(amount, user_id, ownable_id):
  295. connect()
  296. cursor.execute('''
  297. SELECT rowid
  298. FROM ownership
  299. WHERE user_id = ?
  300. AND ownable_id = ?
  301. AND amount - ? >= ?
  302. ''', (user_id, ownable_id, sell_ordered_amount(user_id, ownable_id), amount))
  303. if cursor.fetchone():
  304. return True
  305. else:
  306. return False
  307. def news():
  308. connect()
  309. cursor.execute('''
  310. SELECT *
  311. FROM news
  312. ORDER BY dt DESC
  313. LIMIT 20
  314. ''')
  315. return cursor.fetchall()
  316. def ownable_name_exists(name):
  317. connect()
  318. cursor.execute('''
  319. SELECT rowid
  320. FROM ownables
  321. WHERE name = ?
  322. ''', (name,))
  323. if cursor.fetchone():
  324. return True
  325. else:
  326. return False
  327. def new_stock(name=None):
  328. connect()
  329. while name is None:
  330. name = random_chars(6)
  331. if ownable_name_exists(name):
  332. name = None
  333. cursor.execute('''
  334. INSERT INTO ownables(name)
  335. VALUES (?)
  336. ''', (name,))
  337. cursor.execute('''
  338. INSERT INTO news(title)
  339. VALUES (?)
  340. ''', ('A new stock can now be bought: ' + name,))
  341. if random.getrandbits(1):
  342. cursor.execute('''
  343. INSERT INTO news(title)
  344. VALUES (?)
  345. ''', ('Experts expect the price of ' + name + ' to fall',))
  346. else:
  347. cursor.execute('''
  348. INSERT INTO news(title)
  349. VALUES (?)
  350. ''', ('Experts expect the price of ' + name + ' to rise',))
  351. amount = random.randrange(100, 10000)
  352. price = random.randrange(10000, 20000) / amount
  353. ownable_id = ownable_id_by_name(name)
  354. own(bank_id(), name)
  355. bank_order(False,
  356. ownable_id,
  357. price,
  358. amount,
  359. 60)
  360. return name
  361. def new_stocks(count=1):
  362. return [new_stock() for _ in range(count)]
  363. def ownable_id_by_name(ownable_name):
  364. connect()
  365. cursor.execute('''
  366. SELECT rowid
  367. FROM ownables
  368. WHERE name = ?
  369. ''', (ownable_name,))
  370. return cursor.fetchone()[0]
  371. def get_ownership_id(ownable_id, user_id):
  372. connect()
  373. cursor.execute('''
  374. SELECT rowid
  375. FROM ownership
  376. WHERE ownable_id = ?
  377. AND user_id = ?
  378. ''', (ownable_id, user_id,))
  379. return cursor.fetchone()[0]
  380. def currency_id():
  381. connect()
  382. cursor.execute('''
  383. SELECT rowid
  384. FROM ownables
  385. WHERE name = ?
  386. ''', (CURRENCY_NAME,))
  387. return cursor.fetchone()[0]
  388. def user_money(user_id):
  389. connect()
  390. cursor.execute('''
  391. SELECT amount
  392. FROM ownership
  393. WHERE user_id = ?
  394. AND ownable_id = ?
  395. ''', (user_id, currency_id()))
  396. return cursor.fetchone()[0]
  397. def delete_order(order_id):
  398. connect()
  399. cursor.execute('''
  400. DELETE FROM orders
  401. WHERE rowid = ?
  402. ''', (order_id,))
  403. def execute_orders(ownable_id):
  404. connect()
  405. while True:
  406. # find order to execute
  407. cursor.execute('''
  408. SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
  409. FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
  410. WHERE buy_order.buy AND NOT sell_order.buy
  411. AND buyer.rowid = buy_order.ownership_id
  412. AND seller.rowid = sell_order.ownership_id
  413. AND buyer.ownable_id = ?
  414. AND seller.ownable_id = ?
  415. AND (buy_order."limit" IS NULL
  416. OR sell_order."limit" IS NULL
  417. OR (sell_order."limit" < buy_order."limit"
  418. AND sell_order.stop_loss IS NULL
  419. AND buy_order.stop_loss IS NULL))
  420. ORDER BY COALESCE(sell_order."limit", 0) ASC,
  421. -COALESCE(buy_order."limit", 0) ASC
  422. LIMIT 1
  423. ''', (ownable_id, ownable_id,))
  424. matching_orders = cursor.fetchone()
  425. # return type: (ownership_id,buy,limit,stop_loss,ordered_amount,executed_amount,expiry_dt,
  426. # ownership_id,buy,limit,stop_loss,ordered_amount,executed_amount,expiry_dt,
  427. # user_id,user_id,rowid,rowid)
  428. if not matching_orders:
  429. break
  430. # TODO continue and delete order if buyer has not enough money
  431. buy_ownership_id, _, buy_limit, _, buy_order_amount, buy_executed_amount, buy_expiry_dt, sell_ownership_id, _, sell_limit, _, sell_order_amount, sell_executed_amount, sell_expiry_dt, buyer_id, seller_id, buy_order_id, sell_order_id = matching_orders
  432. if not isinstance(buy_limit, str) and not isinstance(buy_limit, float):
  433. return AssertionError()
  434. if not isinstance(sell_limit, str) and not isinstance(sell_limit, float):
  435. return AssertionError()
  436. if buy_limit == '' and sell_limit == '':
  437. raise AssertionError()
  438. elif buy_limit == '':
  439. price = sell_limit
  440. elif sell_limit == '':
  441. price = buy_limit
  442. else: # both not NULL
  443. price = (float(sell_limit) + float(buy_limit)) / 2
  444. buyer_money = user_money(buyer_id)
  445. amount = min(buy_order_amount - buy_executed_amount,
  446. sell_order_amount - sell_executed_amount,
  447. floor(buyer_money / price))
  448. buy_order_finished = (buy_order_amount - buy_executed_amount - amount <= 0) or (
  449. buyer_money - amount * price < price)
  450. sell_order_finished = (sell_order_amount - sell_executed_amount - amount <= 0)
  451. if price < 0 or amount <= 0:
  452. return AssertionError()
  453. # actually execute the order, but the bank does not send or recieve anything
  454. if buyer_id != bank_id(): # buyer pays
  455. cursor.execute('''
  456. UPDATE ownership
  457. SET amount = amount - ?
  458. WHERE user_id = ?
  459. AND ownable_id = ?
  460. ''', (price * amount, buyer_id, currency_id()))
  461. if seller_id != bank_id(): # seller pays
  462. cursor.execute('''
  463. UPDATE ownership
  464. SET amount = amount - ?
  465. WHERE rowid = ?
  466. ''', (amount, sell_ownership_id))
  467. if buyer_id != bank_id(): # buyer receives
  468. cursor.execute('''
  469. UPDATE ownership
  470. SET amount = amount + ?
  471. WHERE rowid = ?
  472. ''', (amount, buy_ownership_id))
  473. if seller_id != bank_id(): # seller receives
  474. cursor.execute('''
  475. UPDATE ownership
  476. SET amount = amount + ?
  477. WHERE user_id = ?
  478. AND ownable_id = ?
  479. ''', (amount, seller_id, currency_id()))
  480. cursor.execute('''
  481. UPDATE orders
  482. SET executed_amount = executed_amount + ?
  483. WHERE rowid = ?
  484. OR rowid = ?
  485. ''', (amount, buy_order_id, sell_order_id))
  486. if buy_order_finished:
  487. delete_order(buy_order_id)
  488. if sell_order_finished:
  489. delete_order(sell_order_id)
  490. if seller_id != buyer_id: # prevent showing self-transactions
  491. cursor.execute('''
  492. INSERT INTO transactions
  493. (price, ownable_id, amount)
  494. VALUES(?, ?, ?)
  495. ''', (price, ownable_id, amount,))
  496. # trigger stop loss orders
  497. if buyer_id != seller_id:
  498. cursor.execute('''
  499. UPDATE orders
  500. SET stop_loss = NULL
  501. "limit" = NULL
  502. WHERE stop_loss
  503. AND (buy AND "limit" > ?)
  504. OR (NOT buy AND "limit" < ?)
  505. ''', (price, price,))
  506. def ownable_id_by_ownership_id(ownership_id):
  507. connect()
  508. cursor.execute('''
  509. SELECT ownable_id
  510. FROM ownership
  511. WHERE rowid = ?
  512. ''', (ownership_id,))
  513. return cursor.fetchone()[0]
  514. def ownable_name_by_id(ownable_id):
  515. connect()
  516. cursor.execute('''
  517. SELECT name
  518. FROM ownables
  519. WHERE rowid = ?
  520. ''', (ownable_id,))
  521. return cursor.fetchone()[0]
  522. def bank_order(buy, ownable_id, limit, amount, time_until_expiration):
  523. if not limit:
  524. raise AssertionError('The bank does not give away anything.')
  525. place_order(buy,
  526. get_ownership_id(ownable_id, bank_id()),
  527. limit,
  528. False,
  529. amount,
  530. time_until_expiration)
  531. ownable_name = ownable_name_by_id(ownable_id)
  532. cursor.execute('''
  533. INSERT INTO news(title)
  534. VALUES (?)
  535. ''', ('External investors are selling ' + ownable_name,))
  536. def place_order(buy, ownership_id, limit, stop_loss, amount, time_until_expiration):
  537. connect()
  538. expiry = datetime.datetime.now() + datetime.timedelta(minutes=time_until_expiration)
  539. cursor.execute('''
  540. INSERT INTO orders
  541. (buy, ownership_id, "limit", stop_loss, ordered_amount, expiry_dt)
  542. VALUES (?, ?, ?, ?, ?, ?)
  543. ''', (buy, ownership_id, limit, stop_loss, amount, expiry))
  544. execute_orders(ownable_id_by_ownership_id(ownership_id))
  545. return True
  546. def transactions(ownable_id):
  547. connect()
  548. cursor.execute('''
  549. SELECT dt, amount, price
  550. FROM transactions
  551. WHERE ownable_id = ?
  552. ORDER BY dt DESC
  553. ''', (ownable_id,))
  554. return cursor.fetchall()
  555. def drop_expired_orders():
  556. connect()
  557. cursor.execute('''
  558. DELETE FROM orders
  559. WHERE expiry_dt < DATETIME('now')
  560. ''')
  561. return cursor.fetchall()