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_name, amount):
  110. connect()
  111. if amount < 0:
  112. return False
  113. if from_user_id != bank_id():
  114. cursor.execute('''
  115. UPDATE ownership
  116. SET amount = amount - ?
  117. WHERE user_id = ?
  118. AND ownable_id = (SELECT rowid FROM ownables WHERE name = ?)
  119. ''', (amount, from_user_id, ownable_name,))
  120. cursor.execute('''
  121. UPDATE ownership
  122. SET amount = amount + ?
  123. WHERE user_id = ?
  124. AND ownable_id = (SELECT rowid FROM ownables WHERE name = ?)
  125. ''', (amount, to_user_id, ownable_name))
  126. return True
  127. def valid_key(key):
  128. connect()
  129. cursor.execute('''
  130. SELECT key
  131. FROM keys
  132. WHERE used_by_user_id IS NULL
  133. AND key = ?
  134. ''', (key,))
  135. if cursor.fetchone():
  136. return True
  137. else:
  138. return False
  139. def new_session(user_id):
  140. connect()
  141. session_id = str(uuid.uuid4())
  142. cursor.execute('''
  143. INSERT INTO SESSIONS
  144. (user_id, session_id)
  145. VALUES (? , ?)
  146. ''', (user_id, session_id))
  147. return session_id
  148. def save_key(key):
  149. connect()
  150. cursor.execute('''
  151. INSERT INTO keys
  152. (key)
  153. VALUES (?)
  154. ''', (key,))
  155. def drop_old_sessions():
  156. connect()
  157. cursor.execute(''' -- no need to optimize this very well
  158. DELETE FROM sessions
  159. WHERE
  160. (SELECT COUNT(*) as newer
  161. FROM sessions s2
  162. WHERE user_id = s2.user_id
  163. AND rowid < s2.rowid) >= 10
  164. ''')
  165. def user_exists(username):
  166. connect()
  167. cursor.execute('''
  168. SELECT rowid
  169. FROM users
  170. WHERE username = ?
  171. ''', (username,))
  172. if cursor.fetchone():
  173. return True
  174. else:
  175. return False
  176. def get_user_id_by_session_id(session_id):
  177. connect()
  178. cursor.execute('''
  179. SELECT users.rowid
  180. FROM sessions, users
  181. WHERE sessions.session_id = ?
  182. AND users.rowid = sessions.user_id
  183. ''', (session_id,))
  184. ids = cursor.fetchone()
  185. if not ids:
  186. return False
  187. return ids[0]
  188. def get_user_id_by_name(username):
  189. connect()
  190. cursor.execute('''
  191. SELECT users.rowid
  192. FROM users
  193. WHERE username = ?
  194. ''', (username,))
  195. return cursor.fetchone()[0]
  196. def get_user_ownership(user_id):
  197. connect()
  198. cursor.execute('''
  199. SELECT
  200. ownables.name,
  201. ownership.amount,
  202. COALESCE (
  203. CASE -- sum score for each of the users ownables
  204. WHEN ownership.ownable_id = ? THEN 1
  205. ELSE (SELECT price
  206. FROM transactions
  207. WHERE ownable_id = ownership.ownable_id
  208. ORDER BY rowid DESC -- equivalent to ordering by dt
  209. LIMIT 1)
  210. END, 0) AS price,
  211. (SELECT MAX("limit")
  212. FROM orders, ownership o2
  213. WHERE o2.rowid = orders.ownership_id
  214. AND o2.ownable_id = ownership.ownable_id
  215. AND buy
  216. AND NOT stop_loss) AS bid,
  217. (SELECT MIN("limit")
  218. FROM orders, ownership o2
  219. WHERE o2.rowid = orders.ownership_id
  220. AND o2.ownable_id = ownership.ownable_id
  221. AND NOT buy
  222. AND NOT stop_loss) AS ask
  223. FROM ownership, ownables
  224. WHERE user_id = ?
  225. AND (ownership.amount > 0 OR ownership.ownable_id = ?)
  226. AND ownership.ownable_id = ownables.rowid
  227. ORDER BY ownables.rowid ASC
  228. ''', (currency_id(), user_id, currency_id(),))
  229. return cursor.fetchall()
  230. def activate_key(key, user_id):
  231. connect()
  232. cursor.execute('''
  233. UPDATE keys
  234. SET used_by_user_id = ?
  235. WHERE used_by_user_id IS NULL
  236. AND key = ?
  237. ''', (user_id, key,))
  238. send_ownable(bank_id(), user_id, currency_id(), 1000)
  239. def bank_id():
  240. connect()
  241. cursor.execute('''
  242. SELECT users.rowid
  243. FROM users
  244. WHERE username = 'bank'
  245. ''')
  246. return cursor.fetchone()[0]
  247. def valid_session_id(session_id):
  248. connect()
  249. cursor.execute('''
  250. SELECT rowid
  251. FROM sessions
  252. WHERE session_id = ?
  253. ''', (session_id,))
  254. if cursor.fetchone():
  255. return True
  256. else:
  257. return False
  258. def get_user_orders(user_id):
  259. connect()
  260. cursor.execute('''
  261. SELECT
  262. CASE
  263. WHEN orders.buy THEN 'Buy'
  264. ELSE 'Sell'
  265. END,
  266. ownables.name,
  267. (orders.ordered_amount - orders.executed_amount) || '/' || orders.ordered_amount,
  268. orders."limit",
  269. CASE
  270. WHEN orders."limit" IS NULL THEN NULL
  271. WHEN orders.stop_loss THEN 'Yes'
  272. ELSE 'No'
  273. END,
  274. datetime(orders.expiry_dt, 'localtime'),
  275. orders.rowid
  276. FROM orders, ownables, ownership
  277. WHERE ownership.user_id = ?
  278. AND ownership.ownable_id = ownables.rowid
  279. AND orders.ownership_id = ownership.rowid
  280. ORDER BY ownables.name ASC, orders.stop_loss ASC, orders.buy DESC, orders."limit" ASC
  281. ''', (user_id,))
  282. return cursor.fetchall()
  283. def get_ownable_orders(user_id, ownable_id):
  284. connect()
  285. cursor.execute('''
  286. SELECT
  287. CASE
  288. WHEN ownership.user_id = ? THEN 'X'
  289. ELSE NULL
  290. END,
  291. CASE
  292. WHEN orders.buy THEN 'Buy'
  293. ELSE 'Sell'
  294. END,
  295. ownables.name,
  296. orders.ordered_amount - orders.executed_amount,
  297. orders."limit",
  298. datetime(orders.expiry_dt, 'localtime'),
  299. orders.rowid
  300. FROM orders, ownables, ownership
  301. WHERE ownership.ownable_id = ?
  302. AND ownership.ownable_id = ownables.rowid
  303. AND orders.ownership_id = ownership.rowid
  304. AND (orders.stop_loss IS NULL OR NOT orders.stop_loss)
  305. ORDER BY ownables.name ASC, orders.stop_loss ASC, orders.buy DESC, orders."limit" ASC
  306. ''', (user_id, ownable_id,))
  307. return cursor.fetchall()
  308. def sell_ordered_amount(user_id, ownable_id):
  309. connect()
  310. cursor.execute('''
  311. SELECT COALESCE(SUM(orders.ordered_amount - orders.executed_amount),0)
  312. FROM orders, ownership
  313. WHERE ownership.rowid = orders.ownership_id
  314. AND ownership.user_id = ?
  315. AND ownership.ownable_id = ?
  316. AND NOT orders.buy
  317. ''', (user_id, ownable_id))
  318. return cursor.fetchone()[0]
  319. def available_amount(user_id, ownable_id):
  320. connect()
  321. cursor.execute('''
  322. SELECT amount
  323. FROM ownership
  324. WHERE user_id = ?
  325. AND ownable_id = ?
  326. ''', (user_id, ownable_id))
  327. return cursor.fetchone()[0] - sell_ordered_amount(user_id, ownable_id)
  328. def user_owns_at_least(amount, user_id, ownable_id):
  329. connect()
  330. if not isinstance(amount, float) and not isinstance(amount, int):
  331. # comparison of float with strings does not work so well in sql
  332. raise AssertionError()
  333. cursor.execute('''
  334. SELECT rowid
  335. FROM ownership
  336. WHERE user_id = ?
  337. AND ownable_id = ?
  338. AND amount - ? >= ?
  339. ''', (user_id, ownable_id, sell_ordered_amount(user_id, ownable_id), amount))
  340. if cursor.fetchone():
  341. return True
  342. else:
  343. return False
  344. def news():
  345. connect()
  346. cursor.execute('''
  347. SELECT dt, title FROM
  348. (SELECT *, rowid
  349. FROM news
  350. ORDER BY rowid DESC -- equivalent to order by dt
  351. LIMIT 20) n
  352. ORDER BY rowid ASC -- equivalent to order by dt
  353. ''')
  354. return cursor.fetchall()
  355. def ownable_name_exists(name):
  356. connect()
  357. cursor.execute('''
  358. SELECT rowid
  359. FROM ownables
  360. WHERE name = ?
  361. ''', (name,))
  362. if cursor.fetchone():
  363. return True
  364. else:
  365. return False
  366. def new_stock(expiry, name=None):
  367. connect()
  368. while name is None:
  369. name = random_chars(6)
  370. if ownable_name_exists(name):
  371. name = None
  372. cursor.execute('''
  373. INSERT INTO ownables(name)
  374. VALUES (?)
  375. ''', (name,))
  376. new_news('A new stock can now be bought: ' + name)
  377. if random.getrandbits(1):
  378. new_news('Experts expect the price of ' + name + ' to fall')
  379. else:
  380. new_news('Experts expect the price of ' + name + ' to rise')
  381. amount = random.randrange(100, 10000)
  382. price = random.randrange(10000, 20000) / amount
  383. ownable_id = ownable_id_by_name(name)
  384. own(bank_id(), name, amount)
  385. bank_order(False,
  386. ownable_id,
  387. price,
  388. amount,
  389. expiry)
  390. return name
  391. def ownable_id_by_name(ownable_name):
  392. connect()
  393. cursor.execute('''
  394. SELECT rowid
  395. FROM ownables
  396. WHERE name = ?
  397. ''', (ownable_name,))
  398. return cursor.fetchone()[0]
  399. def get_ownership_id(ownable_id, user_id):
  400. connect()
  401. cursor.execute('''
  402. SELECT rowid
  403. FROM ownership
  404. WHERE ownable_id = ?
  405. AND user_id = ?
  406. ''', (ownable_id, user_id,))
  407. return cursor.fetchone()[0]
  408. def currency_id():
  409. connect()
  410. cursor.execute('''
  411. SELECT rowid
  412. FROM ownables
  413. WHERE name = ?
  414. ''', (CURRENCY_NAME,))
  415. return cursor.fetchone()[0]
  416. def user_money(user_id):
  417. connect()
  418. cursor.execute('''
  419. SELECT amount
  420. FROM ownership
  421. WHERE user_id = ?
  422. AND ownable_id = ?
  423. ''', (user_id, currency_id()))
  424. return cursor.fetchone()[0]
  425. def delete_order(order_id, new_order_status):
  426. connect()
  427. cursor.execute('''
  428. INSERT INTO order_history
  429. (ownership_id, buy, "limit", ordered_amount, executed_amount, expiry_dt, status, order_id)
  430. SELECT
  431. ownership_id,
  432. buy,
  433. "limit",
  434. ordered_amount,
  435. executed_amount,
  436. expiry_dt,
  437. ?,
  438. rowid
  439. FROM orders
  440. WHERE rowid = ?
  441. ''', (new_order_status, order_id,))
  442. cursor.execute('''
  443. DELETE FROM orders
  444. WHERE rowid = ?
  445. ''', (order_id,))
  446. def current_value(ownable_id):
  447. connect()
  448. if ownable_id == currency_id():
  449. return 1
  450. cursor.execute('''SELECT price
  451. FROM transactions
  452. WHERE ownable_id = ?
  453. ORDER BY rowid DESC -- equivalent to order by dt
  454. LIMIT 1
  455. ''', (ownable_id,))
  456. return cursor.fetchone()[0]
  457. def execute_orders(ownable_id):
  458. connect()
  459. orders_changed = False
  460. while True:
  461. # find order to execute
  462. cursor.execute('''
  463. -- two best orders
  464. SELECT * FROM (
  465. SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
  466. FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
  467. WHERE buy_order.buy AND NOT sell_order.buy
  468. AND buyer.rowid = buy_order.ownership_id
  469. AND seller.rowid = sell_order.ownership_id
  470. AND buyer.ownable_id = ?
  471. AND seller.ownable_id = ?
  472. AND buy_order."limit" IS NULL
  473. AND sell_order."limit" IS NULL
  474. ORDER BY buy_order.rowid ASC,
  475. sell_order.rowid ASC
  476. LIMIT 1)
  477. UNION ALL -- best buy orders
  478. SELECT * FROM (
  479. SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
  480. FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
  481. WHERE buy_order.buy AND NOT sell_order.buy
  482. AND buyer.rowid = buy_order.ownership_id
  483. AND seller.rowid = sell_order.ownership_id
  484. AND buyer.ownable_id = ?
  485. AND seller.ownable_id = ?
  486. AND buy_order."limit" IS NULL
  487. AND sell_order."limit" IS NOT NULL
  488. AND NOT sell_order.stop_loss
  489. ORDER BY sell_order."limit" ASC,
  490. buy_order.rowid ASC,
  491. sell_order.rowid ASC
  492. LIMIT 1)
  493. UNION ALL -- best sell orders
  494. SELECT * FROM (
  495. SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
  496. FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
  497. WHERE buy_order.buy AND NOT sell_order.buy
  498. AND buyer.rowid = buy_order.ownership_id
  499. AND seller.rowid = sell_order.ownership_id
  500. AND buyer.ownable_id = ?
  501. AND seller.ownable_id = ?
  502. AND buy_order."limit" IS NOT NULL
  503. AND NOT buy_order.stop_loss
  504. AND sell_order."limit" IS NULL
  505. ORDER BY buy_order."limit" DESC,
  506. buy_order.rowid ASC,
  507. sell_order.rowid ASC
  508. LIMIT 1)
  509. UNION ALL -- both limit orders
  510. SELECT * FROM (
  511. SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid
  512. FROM orders buy_order, orders sell_order, ownership buyer, ownership seller
  513. WHERE buy_order.buy AND NOT sell_order.buy
  514. AND buyer.rowid = buy_order.ownership_id
  515. AND seller.rowid = sell_order.ownership_id
  516. AND buyer.ownable_id = ?
  517. AND seller.ownable_id = ?
  518. AND buy_order."limit" IS NOT NULL
  519. AND sell_order."limit" IS NOT NULL
  520. AND sell_order."limit" <= buy_order."limit"
  521. AND NOT sell_order.stop_loss
  522. AND NOT buy_order.stop_loss
  523. ORDER BY buy_order."limit" DESC,
  524. sell_order."limit" ASC,
  525. buy_order.rowid ASC,
  526. sell_order.rowid ASC
  527. LIMIT 1)
  528. LIMIT 1
  529. ''', tuple(ownable_id for _ in range(8)))
  530. matching_orders = cursor.fetchone()
  531. # return type: (ownership_id,buy,limit,stop_loss,ordered_amount,executed_amount,expiry_dt,
  532. # ownership_id,buy,limit,stop_loss,ordered_amount,executed_amount,expiry_dt,
  533. # user_id,user_id,rowid,rowid)
  534. if not matching_orders:
  535. if not orders_changed:
  536. break
  537. # check if the trading bot has any new offers to make
  538. new_order_was_placed = trading_bot.notify_order_traded(ownable_id)
  539. if new_order_was_placed:
  540. orders_changed = False
  541. continue
  542. else:
  543. break
  544. buy_ownership_id, _, buy_limit, _, buy_order_amount, buy_executed_amount, buy_expiry_dt, \
  545. sell_ownership_id, _, sell_limit, _, sell_order_amount, sell_executed_amount, sell_expiry_dt, \
  546. buyer_id, seller_id, buy_order_id, sell_order_id \
  547. = matching_orders
  548. if buy_limit is None and sell_limit is None:
  549. price = current_value(ownable_id)
  550. elif buy_limit is None:
  551. price = sell_limit
  552. elif sell_limit is None:
  553. price = buy_limit
  554. else: # both not NULL
  555. # the price of the older order is used, just like in the real exchange
  556. if buy_order_id < sell_order_id:
  557. price = buy_limit
  558. else:
  559. price = sell_limit
  560. buyer_money = user_money(buyer_id)
  561. def _my_division(x, y):
  562. try:
  563. return floor(x / y)
  564. except ZeroDivisionError:
  565. return float('Inf')
  566. amount = min(buy_order_amount - buy_executed_amount,
  567. sell_order_amount - sell_executed_amount,
  568. _my_division(buyer_money, price))
  569. if amount == 0: # probable because buyer has not enough money
  570. delete_order(buy_order_id, 'Unable to pay')
  571. continue
  572. buy_order_finished = (buy_order_amount - buy_executed_amount - amount <= 0) or (
  573. buyer_money - amount * price < price)
  574. sell_order_finished = (sell_order_amount - sell_executed_amount - amount <= 0)
  575. if price < 0 or amount <= 0: # price of 0 is possible though unlikely
  576. return AssertionError()
  577. # actually execute the order, but the bank does not send or receive anything
  578. send_ownable(buyer_id, seller_id, ownable_id, price * amount)
  579. send_ownable(seller_id, buyer_id, ownable_id, amount)
  580. # update order execution state
  581. cursor.execute('''
  582. UPDATE orders
  583. SET executed_amount = executed_amount + ?
  584. WHERE rowid = ?
  585. OR rowid = ?
  586. ''', (amount, buy_order_id, sell_order_id))
  587. if buy_order_finished:
  588. delete_order(buy_order_id, 'Executed')
  589. orders_changed = True
  590. if sell_order_finished:
  591. delete_order(sell_order_id, 'Executed')
  592. orders_changed = True
  593. if seller_id != buyer_id: # prevent showing self-transactions
  594. cursor.execute('''
  595. INSERT INTO transactions
  596. (price, ownable_id, amount, buyer_id, seller_id)
  597. VALUES(?, ?, ?, ?, ?)
  598. ''', (price, ownable_id, amount, buyer_id, seller_id))
  599. # trigger stop-loss orders
  600. if buyer_id != seller_id:
  601. cursor.execute('''
  602. UPDATE orders
  603. SET stop_loss = NULL,
  604. "limit" = NULL
  605. WHERE stop_loss IS NOT NULL
  606. AND stop_loss
  607. AND ? IN (SELECT ownable_id FROM ownership WHERE rowid = ownership_id)
  608. AND ((buy AND "limit" < ?) OR (NOT buy AND "limit" > ?))
  609. ''', (ownable_id, price, price,))
  610. def ownable_id_by_ownership_id(ownership_id):
  611. connect()
  612. cursor.execute('''
  613. SELECT ownable_id
  614. FROM ownership
  615. WHERE rowid = ?
  616. ''', (ownership_id,))
  617. return cursor.fetchone()[0]
  618. def ownable_name_by_id(ownable_id):
  619. connect()
  620. cursor.execute('''
  621. SELECT name
  622. FROM ownables
  623. WHERE rowid = ?
  624. ''', (ownable_id,))
  625. return cursor.fetchone()[0]
  626. def bank_order(buy, ownable_id, limit, amount, expiry):
  627. if not limit:
  628. raise AssertionError('The bank does not give away anything.')
  629. place_order(buy,
  630. get_ownership_id(ownable_id, bank_id()),
  631. limit,
  632. False,
  633. amount,
  634. expiry)
  635. ownable_name = ownable_name_by_id(ownable_id)
  636. new_news('External investors are selling ' + ownable_name + ' atm')
  637. def current_db_time(): # might differ from datetime.datetime.now() for time zone reasons
  638. connect()
  639. cursor.execute('''
  640. SELECT datetime('now')
  641. ''')
  642. return cursor.fetchone()[0]
  643. def place_order(buy, ownership_id, limit, stop_loss, amount, expiry):
  644. connect()
  645. cursor.execute('''
  646. INSERT INTO orders
  647. (buy, ownership_id, "limit", stop_loss, ordered_amount, expiry_dt)
  648. VALUES (?, ?, ?, ?, ?, ?)
  649. ''', (buy, ownership_id, limit, stop_loss, amount, expiry))
  650. execute_orders(ownable_id_by_ownership_id(ownership_id))
  651. return True
  652. def trades_on(ownable_id, limit):
  653. connect()
  654. cursor.execute('''
  655. SELECT datetime(dt,'localtime'), amount, price
  656. FROM transactions
  657. WHERE ownable_id = ?
  658. ORDER BY rowid DESC -- equivalent to order by dt
  659. LIMIT ?
  660. ''', (ownable_id, limit,))
  661. return cursor.fetchall()
  662. def trades(user_id, limit):
  663. connect()
  664. cursor.execute('''
  665. SELECT
  666. (CASE WHEN seller_id = ? THEN 'Sell' ELSE 'Buy' END),
  667. (SELECT name FROM ownables WHERE rowid = transactions.ownable_id),
  668. amount,
  669. price,
  670. datetime(dt,'localtime')
  671. FROM transactions
  672. WHERE seller_id = ? OR buyer_id = ?
  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() # TODO testen
  914. # TODO indices prüfen
  915. cursor.execute('''
  916. SELECT
  917. (CASE WHEN order_history.buy THEN 'Buy' ELSE 'Sell' END),
  918. ownables.name,
  919. (order_history.ordered_amount - order_history.executed_amount) || '/' || order_history.ordered_amount,
  920. order_history."limit",
  921. order_history.expiry_dt,
  922. order_history.order_id,
  923. order_history.status
  924. FROM order_history, ownership, ownables
  925. WHERE ownership.user_id = ?
  926. AND ownership.rowid = order_history.ownership_id
  927. AND ownables.rowid = ownership.ownable_id
  928. AND (
  929. (order_history.status = 'Executed' AND ?)
  930. OR
  931. ((order_history.status = 'Expired' OR order_history.status = 'Canceled') AND ?)
  932. )
  933. LIMIT ?
  934. ''', (user_id, include_executed, include_canceled, limit))
  935. return cursor.fetchall()