import random import re import sqlite3 as db import sys import uuid from math import floor from passlib.handlers.sha2_crypt import sha256_crypt import db_setup import trading_bot from debug import debug from game import CURRENCY_NAME from util import random_chars, salt # connection: db.Connection = None # cursor: db.Cursor = None connection = None # no type annotations in python 3.5 cursor = None # no type annotations in python 3.5 db_name = None def query_save_name(): global db_name if debug: db_name = 'test.db' return while True: save_name = input('Name of the savegame: ') if re.match(r"[A-Za-z0-9.-]{0,50}", save_name): db_name = save_name + '.db' return else: print('Must match "[A-Za-z0-9.-]{0,50}"') def connect(reconnect=False): global connection global cursor global db_name if reconnect: connection.commit() connection.close() cursor = None connection = None db_name = None if connection is None or cursor is None: query_save_name() try: connection = db.connect(db_name) # connection.text_factory = lambda x: unicode(x, 'utf-8', 'ignore') cursor = connection.cursor() except db.Error as e: print("Database error %s:" % e.args[0]) sys.exit(1) # finally: # if con is not None: # con.close() def setup(): connect() db_setup.setup(cursor) connection.commit() def used_key_count(): connect() cursor.execute(''' SELECT COUNT(*) -- rarely executed, no index needed, O(n) query FROM keys WHERE used_by_user_id IS NOT NULL ''') return cursor.fetchone()[0] def login(username, password): connect() # do not allow login as bank or with empty password if username == 'bank' and not debug: return None if password == '' and not debug: return None cursor.execute(''' SELECT rowid, password FROM users WHERE username = ? ''', (username,)) data = cursor.fetchone() if not data: return None hashed_password = data[1] user_id = data[0] # if a ValueError occurs here, then most likely a password that was stored as plain text if sha256_crypt.verify(password + salt, hashed_password): return new_session(user_id) else: return None def register(username, password, game_key): connect() if username == '': return False if password == '': return False cursor.execute(''' INSERT INTO users (username, password) VALUES (? , ?) ''', (username, password)) own(get_user_id_by_name(username), CURRENCY_NAME) if game_key != '': if valid_key(game_key): activate_key(game_key, get_user_id_by_name(username)) return True def own(user_id, ownable_name, amount=0): if not isinstance(ownable_name, str): return AssertionError('A name must be a string.') cursor.execute(''' INSERT OR IGNORE INTO ownership (user_id, ownable_id, amount) SELECT ?, (SELECT rowid FROM ownables WHERE name = ?), ? ''', (user_id, ownable_name, amount)) def send_ownable(from_user_id, to_user_id, ownable_id, amount): connect() if amount < 0: raise AssertionError('Can not send negative amount') cursor.execute(''' UPDATE ownership SET amount = amount - ? WHERE user_id = ? AND ownable_id = ? ''', (amount, from_user_id, ownable_id,)) cursor.execute(''' UPDATE ownership SET amount = amount + ? WHERE user_id = ? AND ownable_id = ? ''', (amount, to_user_id, ownable_id,)) return True def valid_key(key): connect() cursor.execute(''' SELECT key FROM keys WHERE used_by_user_id IS NULL AND key = ? ''', (key,)) if cursor.fetchone(): return True else: return False def new_session(user_id): connect() session_id = str(uuid.uuid4()) cursor.execute(''' INSERT INTO SESSIONS (user_id, session_id) VALUES (? , ?) ''', (user_id, session_id)) return session_id def save_key(key): connect() cursor.execute(''' INSERT INTO keys (key) VALUES (?) ''', (key,)) def drop_old_sessions(): connect() cursor.execute(''' -- no need to optimize this very well DELETE FROM sessions WHERE (SELECT COUNT(*) as newer FROM sessions s2 WHERE user_id = s2.user_id AND rowid < s2.rowid) >= 10 ''') def user_exists(username): connect() cursor.execute(''' SELECT rowid FROM users WHERE username = ? ''', (username,)) if cursor.fetchone(): return True else: return False def get_user_id_by_session_id(session_id): connect() cursor.execute(''' SELECT users.rowid FROM sessions, users WHERE sessions.session_id = ? AND users.rowid = sessions.user_id ''', (session_id,)) ids = cursor.fetchone() if not ids: return False return ids[0] def get_user_id_by_name(username): connect() cursor.execute(''' SELECT users.rowid FROM users WHERE username = ? ''', (username,)) return cursor.fetchone()[0] def get_user_ownership(user_id): connect() cursor.execute(''' SELECT ownables.name, ownership.amount, COALESCE ( CASE -- sum score for each of the users ownables WHEN ownership.ownable_id = ? THEN 1 ELSE (SELECT price FROM transactions WHERE ownable_id = ownership.ownable_id ORDER BY rowid DESC -- equivalent to ordering by dt LIMIT 1) END, 0) AS price, (SELECT MAX("limit") FROM orders, ownership o2 WHERE o2.rowid = orders.ownership_id AND o2.ownable_id = ownership.ownable_id AND buy AND NOT stop_loss) AS bid, (SELECT MIN("limit") FROM orders, ownership o2 WHERE o2.rowid = orders.ownership_id AND o2.ownable_id = ownership.ownable_id AND NOT buy AND NOT stop_loss) AS ask FROM ownership, ownables WHERE user_id = ? AND (ownership.amount > 0 OR ownership.ownable_id = ?) AND ownership.ownable_id = ownables.rowid ORDER BY ownables.rowid ASC ''', (currency_id(), user_id, currency_id(),)) return cursor.fetchall() def activate_key(key, user_id): connect() cursor.execute(''' UPDATE keys SET used_by_user_id = ? WHERE used_by_user_id IS NULL AND key = ? ''', (user_id, key,)) send_ownable(bank_id(), user_id, currency_id(), 1000) def bank_id(): connect() cursor.execute(''' SELECT users.rowid FROM users WHERE username = 'bank' ''') return cursor.fetchone()[0] def valid_session_id(session_id): connect() cursor.execute(''' SELECT rowid FROM sessions WHERE session_id = ? ''', (session_id,)) if cursor.fetchone(): return True else: return False def get_user_orders(user_id): connect() cursor.execute(''' SELECT CASE WHEN orders.buy THEN 'Buy' ELSE 'Sell' END, ownables.name, (orders.ordered_amount - orders.executed_amount) || '/' || orders.ordered_amount, orders."limit", CASE WHEN orders."limit" IS NULL THEN NULL WHEN orders.stop_loss THEN 'Yes' ELSE 'No' END, datetime(orders.expiry_dt, 'localtime'), orders.rowid FROM orders, ownables, ownership WHERE ownership.user_id = ? AND ownership.ownable_id = ownables.rowid AND orders.ownership_id = ownership.rowid ORDER BY ownables.name ASC, orders.stop_loss ASC, orders.buy DESC, orders."limit" ASC ''', (user_id,)) return cursor.fetchall() def get_ownable_orders(user_id, ownable_id): connect() cursor.execute(''' SELECT CASE WHEN ownership.user_id = ? THEN 'X' ELSE NULL END, CASE WHEN orders.buy THEN 'Buy' ELSE 'Sell' END, ownables.name, orders.ordered_amount - orders.executed_amount, orders."limit", datetime(orders.expiry_dt, 'localtime'), orders.rowid FROM orders, ownables, ownership WHERE ownership.ownable_id = ? AND ownership.ownable_id = ownables.rowid AND orders.ownership_id = ownership.rowid AND (orders.stop_loss IS NULL OR NOT orders.stop_loss) ORDER BY ownables.name ASC, orders.stop_loss ASC, orders.buy DESC, orders."limit" ASC ''', (user_id, ownable_id,)) return cursor.fetchall() def sell_ordered_amount(user_id, ownable_id): connect() cursor.execute(''' SELECT COALESCE(SUM(orders.ordered_amount - orders.executed_amount),0) FROM orders, ownership WHERE ownership.rowid = orders.ownership_id AND ownership.user_id = ? AND ownership.ownable_id = ? AND NOT orders.buy ''', (user_id, ownable_id)) return cursor.fetchone()[0] def available_amount(user_id, ownable_id): connect() cursor.execute(''' SELECT amount FROM ownership WHERE user_id = ? AND ownable_id = ? ''', (user_id, ownable_id)) return cursor.fetchone()[0] - sell_ordered_amount(user_id, ownable_id) def user_owns_at_least(amount, user_id, ownable_id): connect() if not isinstance(amount, float) and not isinstance(amount, int): # comparison of float with strings does not work so well in sql raise AssertionError() cursor.execute(''' SELECT rowid FROM ownership WHERE user_id = ? AND ownable_id = ? AND amount - ? >= ? ''', (user_id, ownable_id, sell_ordered_amount(user_id, ownable_id), amount)) if cursor.fetchone(): return True else: return False def news(): connect() cursor.execute(''' SELECT dt, title FROM (SELECT *, rowid FROM news ORDER BY rowid DESC -- equivalent to order by dt LIMIT 20) n ORDER BY rowid ASC -- equivalent to order by dt ''') return cursor.fetchall() def ownable_name_exists(name): connect() cursor.execute(''' SELECT rowid FROM ownables WHERE name = ? ''', (name,)) if cursor.fetchone(): return True else: return False def new_stock(expiry, name=None): connect() while name is None: name = random_chars(6) if ownable_name_exists(name): name = None cursor.execute(''' INSERT INTO ownables(name) VALUES (?) ''', (name,)) new_news('A new stock can now be bought: ' + name) if random.getrandbits(1): new_news('Experts expect the price of ' + name + ' to fall') else: new_news('Experts expect the price of ' + name + ' to rise') amount = random.randrange(100, 10000) price = random.randrange(10000, 20000) / amount ownable_id = ownable_id_by_name(name) own(bank_id(), name, amount) bank_order(False, ownable_id, price, amount, expiry) return name def ownable_id_by_name(ownable_name): connect() cursor.execute(''' SELECT rowid FROM ownables WHERE name = ? ''', (ownable_name,)) return cursor.fetchone()[0] def get_ownership_id(ownable_id, user_id): connect() cursor.execute(''' SELECT rowid FROM ownership WHERE ownable_id = ? AND user_id = ? ''', (ownable_id, user_id,)) return cursor.fetchone()[0] def currency_id(): connect() cursor.execute(''' SELECT rowid FROM ownables WHERE name = ? ''', (CURRENCY_NAME,)) return cursor.fetchone()[0] def user_money(user_id): connect() cursor.execute(''' SELECT amount FROM ownership WHERE user_id = ? AND ownable_id = ? ''', (user_id, currency_id())) return cursor.fetchone()[0] def delete_order(order_id, new_order_status): connect() cursor.execute(''' INSERT INTO order_history (ownership_id, buy, "limit", ordered_amount, executed_amount, expiry_dt, status, order_id) SELECT ownership_id, buy, "limit", ordered_amount, executed_amount, expiry_dt, ?, rowid FROM orders WHERE rowid = ? ''', (new_order_status, order_id,)) cursor.execute(''' DELETE FROM orders WHERE rowid = ? ''', (order_id,)) def current_value(ownable_id): connect() if ownable_id == currency_id(): return 1 cursor.execute('''SELECT price FROM transactions WHERE ownable_id = ? ORDER BY rowid DESC -- equivalent to order by dt LIMIT 1 ''', (ownable_id,)) return cursor.fetchone()[0] def execute_orders(ownable_id): connect() orders_traded = False while True: # find order to execute cursor.execute(''' -- two best orders SELECT * FROM ( SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid FROM orders buy_order, orders sell_order, ownership buyer, ownership seller WHERE buy_order.buy AND NOT sell_order.buy AND buyer.rowid = buy_order.ownership_id AND seller.rowid = sell_order.ownership_id AND buyer.ownable_id = ? AND seller.ownable_id = ? AND buy_order."limit" IS NULL AND sell_order."limit" IS NULL ORDER BY buy_order.rowid ASC, sell_order.rowid ASC LIMIT 1) UNION ALL -- best buy orders SELECT * FROM ( SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid FROM orders buy_order, orders sell_order, ownership buyer, ownership seller WHERE buy_order.buy AND NOT sell_order.buy AND buyer.rowid = buy_order.ownership_id AND seller.rowid = sell_order.ownership_id AND buyer.ownable_id = ? AND seller.ownable_id = ? AND buy_order."limit" IS NULL AND sell_order."limit" IS NOT NULL AND NOT sell_order.stop_loss ORDER BY sell_order."limit" ASC, buy_order.rowid ASC, sell_order.rowid ASC LIMIT 1) UNION ALL -- best sell orders SELECT * FROM ( SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid FROM orders buy_order, orders sell_order, ownership buyer, ownership seller WHERE buy_order.buy AND NOT sell_order.buy AND buyer.rowid = buy_order.ownership_id AND seller.rowid = sell_order.ownership_id AND buyer.ownable_id = ? AND seller.ownable_id = ? AND buy_order."limit" IS NOT NULL AND NOT buy_order.stop_loss AND sell_order."limit" IS NULL ORDER BY buy_order."limit" DESC, buy_order.rowid ASC, sell_order.rowid ASC LIMIT 1) UNION ALL -- both limit orders SELECT * FROM ( SELECT buy_order.*, sell_order.*, buyer.user_id, seller.user_id, buy_order.rowid, sell_order.rowid FROM orders buy_order, orders sell_order, ownership buyer, ownership seller WHERE buy_order.buy AND NOT sell_order.buy AND buyer.rowid = buy_order.ownership_id AND seller.rowid = sell_order.ownership_id AND buyer.ownable_id = ? AND seller.ownable_id = ? AND buy_order."limit" IS NOT NULL AND sell_order."limit" IS NOT NULL AND sell_order."limit" <= buy_order."limit" AND NOT sell_order.stop_loss AND NOT buy_order.stop_loss ORDER BY buy_order."limit" DESC, sell_order."limit" ASC, buy_order.rowid ASC, sell_order.rowid ASC LIMIT 1) LIMIT 1 ''', tuple(ownable_id for _ in range(8))) matching_orders = cursor.fetchone() # return type: (ownership_id,buy,limit,stop_loss,ordered_amount,executed_amount,expiry_dt, # ownership_id,buy,limit,stop_loss,ordered_amount,executed_amount,expiry_dt, # user_id,user_id,rowid,rowid) if not matching_orders: if not orders_traded: break # check if the trading bot has any new offers to make new_order_was_placed = trading_bot.notify_order_traded(ownable_id) if new_order_was_placed: orders_traded = False continue else: break buy_ownership_id, _, buy_limit, _, buy_order_amount, buy_executed_amount, buy_expiry_dt, \ sell_ownership_id, _, sell_limit, _, sell_order_amount, sell_executed_amount, sell_expiry_dt, \ buyer_id, seller_id, buy_order_id, sell_order_id \ = matching_orders if buy_limit is None and sell_limit is None: price = current_value(ownable_id) elif buy_limit is None: price = sell_limit elif sell_limit is None: price = buy_limit else: # both not NULL # the price of the older order is used, just like in the real exchange if buy_order_id < sell_order_id: price = buy_limit else: price = sell_limit buyer_money = user_money(buyer_id) def _my_division(x, y): try: return floor(x / y) except ZeroDivisionError: return float('Inf') amount = min(buy_order_amount - buy_executed_amount, sell_order_amount - sell_executed_amount, _my_division(buyer_money, price)) if amount == 0: # probable because buyer has not enough money delete_order(buy_order_id, 'Unable to pay') continue buy_order_finished = (buy_order_amount - buy_executed_amount - amount <= 0) or ( buyer_money - amount * price < price) sell_order_finished = (sell_order_amount - sell_executed_amount - amount <= 0) if price < 0 or amount <= 0: # price of 0 is possible though unlikely return AssertionError() # actually execute the order, but the bank does not send or receive anything send_ownable(buyer_id, seller_id, currency_id(), price * amount) send_ownable(seller_id, buyer_id, ownable_id, amount) # update order execution state cursor.execute(''' UPDATE orders SET executed_amount = executed_amount + ? WHERE rowid = ? OR rowid = ? ''', (amount, buy_order_id, sell_order_id)) if buy_order_finished: delete_order(buy_order_id, 'Executed') orders_traded = True if sell_order_finished: delete_order(sell_order_id, 'Executed') orders_traded = True if seller_id != buyer_id: # prevent showing self-transactions cursor.execute(''' INSERT INTO transactions (price, ownable_id, amount, buyer_id, seller_id) VALUES(?, ?, ?, ?, ?) ''', (price, ownable_id, amount, buyer_id, seller_id)) # trigger stop-loss orders if buyer_id != seller_id: cursor.execute(''' UPDATE orders SET stop_loss = NULL, "limit" = NULL WHERE stop_loss IS NOT NULL AND stop_loss AND ? IN (SELECT ownable_id FROM ownership WHERE rowid = ownership_id) AND ((buy AND "limit" < ?) OR (NOT buy AND "limit" > ?)) ''', (ownable_id, price, price,)) def ownable_id_by_ownership_id(ownership_id): connect() cursor.execute(''' SELECT ownable_id FROM ownership WHERE rowid = ? ''', (ownership_id,)) return cursor.fetchone()[0] def ownable_name_by_id(ownable_id): connect() cursor.execute(''' SELECT name FROM ownables WHERE rowid = ? ''', (ownable_id,)) return cursor.fetchone()[0] def bank_order(buy, ownable_id, limit, amount, expiry): if not limit: raise AssertionError('The bank does not give away anything.') place_order(buy, get_ownership_id(ownable_id, bank_id()), limit, False, amount, expiry) ownable_name = ownable_name_by_id(ownable_id) new_news('External investors are selling ' + ownable_name + ' atm') def current_db_time(): # might differ from datetime.datetime.now() for time zone reasons connect() cursor.execute(''' SELECT datetime('now') ''') return cursor.fetchone()[0] def place_order(buy, ownership_id, limit, stop_loss, amount, expiry): connect() cursor.execute(''' INSERT INTO orders (buy, ownership_id, "limit", stop_loss, ordered_amount, expiry_dt) VALUES (?, ?, ?, ?, ?, ?) ''', (buy, ownership_id, limit, stop_loss, amount, expiry)) execute_orders(ownable_id_by_ownership_id(ownership_id)) return True def trades_on(ownable_id, limit): connect() cursor.execute(''' SELECT datetime(dt,'localtime'), amount, price FROM transactions WHERE ownable_id = ? ORDER BY rowid DESC -- equivalent to order by dt LIMIT ? ''', (ownable_id, limit,)) return cursor.fetchall() def trades(user_id, limit): connect() cursor.execute(''' SELECT (CASE WHEN seller_id = ? THEN 'Sell' ELSE 'Buy' END), (SELECT name FROM ownables WHERE rowid = transactions.ownable_id), amount, price, datetime(dt,'localtime') FROM transactions WHERE seller_id = ? OR buyer_id = ? ORDER BY rowid DESC -- equivalent to order by dt LIMIT ? ''', (user_id, user_id, user_id, limit,)) return cursor.fetchall() def drop_expired_orders(): connect() cursor.execute(''' SELECT rowid, * FROM orders WHERE expiry_dt < DATETIME('now') ''') data = cursor.fetchall() for order in data: order_id = order[0] delete_order(order_id, 'Expired') return data def generate_keys(count=1): # source https://stackoverflow.com/questions/17049308/python-3-3-serial-key-generator-list-problems for i in range(count): key = '-'.join(random_chars(5) for _ in range(5)) save_key(key) print(key) def user_has_order_with_id(session_id, order_id): connect() cursor.execute(''' SELECT orders.rowid FROM orders, ownership, sessions WHERE orders.rowid = ? AND sessions.session_id = ? AND sessions.user_id = ownership.user_id AND ownership.rowid = orders.ownership_id ''', (order_id, session_id,)) if cursor.fetchone(): return True else: return False def leaderboard(): connect() cursor.execute(''' SELECT * FROM ( -- one score for each user SELECT username, SUM(CASE -- sum score for each of the users ownables WHEN ownership.ownable_id = ? THEN ownership.amount ELSE ownership.amount * (SELECT price FROM transactions WHERE ownable_id = ownership.ownable_id ORDER BY rowid DESC -- equivalent to ordering by dt LIMIT 1) END ) score FROM users, ownership WHERE ownership.user_id = users.rowid AND users.username != 'bank' GROUP BY users.rowid ) AS scores ORDER BY score DESC LIMIT 50 ''', (currency_id(),)) return cursor.fetchall() def user_wealth(user_id): connect() cursor.execute(''' SELECT SUM( CASE -- sum score for each of the users ownables WHEN ownership.ownable_id = ? THEN ownership.amount ELSE ownership.amount * (SELECT price FROM transactions WHERE ownable_id = ownership.ownable_id ORDER BY rowid DESC -- equivalent to ordering by dt LIMIT 1) END ) score FROM ownership WHERE ownership.user_id = ? ''', (currency_id(), user_id,)) return cursor.fetchone()[0] def change_password(session_id, password): connect() cursor.execute(''' UPDATE users SET password = ? WHERE rowid = (SELECT user_id FROM sessions WHERE sessions.session_id = ?) ''', (password, session_id,)) def sign_out_user(session_id): connect() cursor.execute(''' DELETE FROM sessions WHERE user_id = (SELECT user_id FROM sessions s2 WHERE s2.session_id = ?) ''', (session_id,)) def delete_user(user_id): connect() cursor.execute(''' DELETE FROM sessions WHERE user_id = ? ''', (user_id,)) cursor.execute(''' DELETE FROM orders WHERE ownership_id IN ( SELECT rowid FROM ownership WHERE user_id = ?) ''', (user_id,)) cursor.execute(''' DELETE FROM ownership WHERE user_id = ? ''', (user_id,)) cursor.execute(''' DELETE FROM keys WHERE used_by_user_id = ? ''', (user_id,)) cursor.execute(''' INSERT INTO news(title) VALUES ((SELECT username FROM users WHERE rowid = ?) || ' retired.') ''', (user_id,)) cursor.execute(''' DELETE FROM users WHERE rowid = ? ''', (user_id,)) def delete_ownable(ownable_id): connect() cursor.execute(''' DELETE FROM transactions WHERE ownable_id = ? ''', (ownable_id,)) cursor.execute(''' DELETE FROM orders WHERE ownership_id IN ( SELECT rowid FROM ownership WHERE ownable_id = ?) ''', (ownable_id,)) cursor.execute(''' DELETE FROM orders_history WHERE ownership_id IN ( SELECT rowid FROM ownership WHERE ownable_id = ?) ''', (ownable_id,)) # only delete empty ownerships cursor.execute(''' DELETE FROM ownership WHERE ownable_id = ? AND amount = 0 ''', (ownable_id,)) cursor.execute(''' INSERT INTO news(title) VALUES ((SELECT name FROM ownables WHERE rowid = ?) || ' can not be traded any more.') ''', (ownable_id,)) cursor.execute(''' DELETE FROM ownables WHERE rowid = ? ''', (ownable_id,)) def hash_all_users_passwords(): connect() cursor.execute(''' SELECT rowid, password FROM users ''') users = cursor.fetchall() for user in users: user_id = user[0] pw = user[1] valid_hash = True try: sha256_crypt.verify('password' + salt, pw) except ValueError: valid_hash = False if valid_hash: raise AssertionError('There is already a hashed password in the database! Be careful what you are doing!') pw = sha256_crypt.encrypt(pw + salt) cursor.execute(''' UPDATE users SET password = ? WHERE rowid = ? ''', (pw, user_id,)) def new_news(message): connect() cursor.execute(''' INSERT INTO news(title) VALUES (?) ''', (message,)) def abs_spread(ownable_id): connect() cursor.execute(''' SELECT (SELECT MAX("limit") FROM orders, ownership WHERE ownership.rowid = orders.ownership_id AND ownership.ownable_id = ? AND buy AND NOT stop_loss) AS bid, (SELECT MIN("limit") FROM orders, ownership WHERE ownership.rowid = orders.ownership_id AND ownership.ownable_id = ? AND NOT buy AND NOT stop_loss) AS ask ''', (ownable_id, ownable_id,)) return cursor.fetchone() def ownables(): connect() cursor.execute(''' SELECT name, course, (SELECT SUM(amount) FROM ownership WHERE ownership.ownable_id = ownables_with_course.rowid) market_size FROM (SELECT name, ownables.rowid, CASE WHEN ownables.rowid = ? THEN 1 ELSE (SELECT price FROM transactions WHERE ownable_id = ownables.rowid ORDER BY rowid DESC -- equivalent to ordering by dt LIMIT 1) END course FROM ownables) ownables_with_course ''', (currency_id(),)) data = cursor.fetchall() for idx in range(len(data)): # compute market cap row = data[idx] if row[1] is None: market_cap = None elif row[2] is None: market_cap = None else: market_cap = row[1] * row[2] data[idx] = (row[0], row[1], market_cap) return data def reset_bank(): connect() cursor.execute(''' DELETE FROM ownership WHERE user_id = ? ''', (bank_id(),)) def cleanup(): if connection is not None: connection.commit() connection.close() def ownable_ids(): connect() cursor.execute(''' SELECT rowid FROM ownables ''') return [ownable_id[0] for ownable_id in cursor.fetchall()] def get_old_orders(user_id, include_executed, include_canceled, limit): connect() cursor.execute(''' SELECT (CASE WHEN order_history.buy THEN 'Buy' ELSE 'Sell' END), ownables.name, (order_history.ordered_amount - order_history.executed_amount) || '/' || order_history.ordered_amount, order_history."limit", order_history.expiry_dt, order_history.order_id, order_history.status FROM order_history, ownership, ownables WHERE ownership.user_id = ? AND ownership.rowid = order_history.ownership_id AND ownables.rowid = ownership.ownable_id AND ( (order_history.status = 'Executed' AND ?) OR ((order_history.status = 'Expired' OR order_history.status = 'Canceled') AND ?) ) ORDER BY order_history.rowid DESC -- equivalent to ordering by creation time LIMIT ? ''', (user_id, include_executed, include_canceled, limit)) return cursor.fetchall()