model.py 33 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181
  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. own(get_user_id_by_name(username), CURRENCY_NAME)
  98. if game_key != '':
  99. if valid_key(game_key):
  100. activate_key(game_key, get_user_id_by_name(username))
  101. return True
  102. def own(user_id, ownable_name, amount=0):
  103. if not isinstance(ownable_name, str):
  104. return AssertionError('A name must be a string.')
  105. cursor.execute('''
  106. INSERT OR IGNORE INTO ownership (user_id, ownable_id, amount)
  107. SELECT ?, (SELECT rowid FROM ownables WHERE name = ?), ?
  108. ''', (user_id, ownable_name, amount))
  109. def send_ownable(from_user_id, to_user_id, ownable_id, amount):
  110. connect()
  111. if amount < 0:
  112. raise AssertionError('Can not send negative amount')
  113. cursor.execute('''
  114. UPDATE ownership
  115. SET amount = amount - ?
  116. WHERE user_id = ?
  117. AND ownable_id = ?
  118. ''', (amount, from_user_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_owns_at_least(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, * 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()