Orderer/cool_query.py

import model
from client_controller import _my_tabulate

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

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