Orderer/cool_query.py

import model
from client_controller import _my_tabulate

model.connect()
model.cursor.execute('''
        EXPLAIN QUERY PLAN
        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
        '''.replace('?', '1'))

# model.cursor.execute('''
#             SELECT 2
#             UNION ALL
#             SELECT * FROM (
#                 WITH RECURSIVE ones(x) AS (VALUES(1) UNION ALL SELECT * FROM ones)
#                 SELECT x FROM ones)
#             LIMIT 1
#          '''.replace('?','1'))

# model.cursor.execute('''SELECT CASE CAST(1+(0.5 - RANDOM() / CAST(-9223372036854775808 AS REAL) / 2)*3 AS INTEGER)
#   WHEN 1 THEN 'one'
#   WHEN 2 THEN 'two'
#   WHEN 3 THEN 'three'
# END
#   FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) v
#          '''.replace('?','1'))

# model.cursor.execute('''
# SELECT *
#   FROM (SELECT RANDOM() / CAST(-9223372036854775808 AS REAL) / 2) r, (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) v
#          '''.replace('?','1'))

print(_my_tabulate(model.cursor.fetchall(), tablefmt='pipe'))