model.py 40 KB

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