import random
import re
import sqlite3 as db
import sys
import uuid
from datetime import timedelta, datetime
from math import floor
from passlib.handlers.sha2_crypt import sha256_crypt
import db_setup
from game import CURRENCY_NAME
from util import random_chars, salt
from debug import debug
# 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):
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)
SELECT ?, (SELECT rowid FROM ownables WHERE name = ?)
''', (user_id, ownable_name,))
def send_ownable(from_user_id, to_user_id, ownable_name, amount):
connect()
if amount < 0:
return False
if from_user_id != bank_id():
cursor.execute('''
UPDATE ownership
SET amount = amount - ?
WHERE user_id = ?
AND ownable_id = (SELECT rowid FROM ownables WHERE name = ?)
''', (amount, from_user_id, ownable_name,))
cursor.execute('''
UPDATE ownership
SET amount = amount + ?
WHERE user_id = ?
AND ownable_id = (SELECT rowid FROM ownables WHERE name = ?)
''', (amount, to_user_id, ownable_name))
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 unused_keys():
connect()
cursor.execute('''
SELECT key
FROM keys
WHERE used_by_user_id IS NULL
''')
return [str(key[0]).strip().upper() for key in cursor.fetchall()]
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_NAME, 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)
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):
connect()
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()
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:
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)
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
if buyer_id != bank_id(): # buyer pays
cursor.execute('''
UPDATE ownership
SET amount = amount - ?
WHERE user_id = ?
AND ownable_id = ?
''', (price * amount, buyer_id, currency_id()))
if seller_id != bank_id(): # seller pays
cursor.execute('''
UPDATE ownership
SET amount = amount - ?
WHERE rowid = ?
''', (amount, sell_ownership_id))
if buyer_id != bank_id(): # buyer receives
cursor.execute('''
UPDATE ownership
SET amount = amount + ?
WHERE rowid = ?
''', (amount, buy_ownership_id))
if seller_id != bank_id(): # seller receives
cursor.execute('''
UPDATE ownership
SET amount = amount + ?
WHERE user_id = ?
AND ownable_id = ?
''', (price * amount, seller_id, currency_id()))
# 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)
if sell_order_finished:
delete_order(sell_order_id)
if seller_id != buyer_id: # prevent showing self-transactions
cursor.execute('''
INSERT INTO transactions
(price, ownable_id, amount)
VALUES(?, ?, ?)
''', (price, ownable_id, amount,))
# 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 transactions(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 drop_expired_orders():
connect()
cursor.execute('''
DELETE FROM orders
WHERE expiry_dt < DATETIME('now')
''')
return cursor.fetchall()
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,))
# 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 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