"""Does the defence depend on the order the AND terms are written in?

probe.py found that a predicate excluding the bad row saves the query even on a
full scan. That is the optimizer choosing an evaluation order. This asks whether
writing the terms the other way round loses the protection.
"""
import json
import sqlite3

GOOD, BAD = '{"carried_by": "av1"}', "not json"


def build(rows=400, index=False):
    conn = sqlite3.connect(":memory:")
    conn.execute("CREATE TABLE things (id TEXT PRIMARY KEY, x INT, state_json TEXT)")
    conn.execute("INSERT INTO things VALUES ('av1', 100, ?)", (GOOD,))
    for i in range(rows):
        conn.execute("INSERT INTO things VALUES (?, ?, ?)", (f"t{i}", i % 50, GOOD))
    conn.execute("INSERT INTO things VALUES ('bad', 5, ?)", (BAD,))
    if index:
        conn.execute("CREATE INDEX ix ON things(x)")
    return conn


CASES = [
    ("filter first,  extract second", False,
     "SELECT id FROM things WHERE x = 100 AND json_extract(state_json,'$.carried_by')='av1'"),
    ("extract first, filter second", False,
     "SELECT id FROM things WHERE json_extract(state_json,'$.carried_by')='av1' AND x = 100"),
    ("extract first, filter second, index on x", True,
     "SELECT id FROM things WHERE json_extract(state_json,'$.carried_by')='av1' AND x = 100"),
    ("json_valid first,  extract second", False,
     "SELECT id FROM things WHERE json_valid(state_json) AND json_extract(state_json,'$.carried_by')='av1'"),
    ("extract first, json_valid second", False,
     "SELECT id FROM things WHERE json_extract(state_json,'$.carried_by')='av1' AND json_valid(state_json)"),
    ("OR, not AND", False,
     "SELECT id FROM things WHERE x = 100 OR json_extract(state_json,'$.carried_by')='av1'"),
    ("the extract in a subquery the outer query filters", False,
     "SELECT id FROM (SELECT id, json_extract(state_json,'$.carried_by') AS c "
     "FROM things WHERE x = 100) WHERE c = 'av1'"),
    ("UPDATE, filter first", False,
     "UPDATE things SET x = 7 WHERE x = 100 AND json_extract(state_json,'$.carried_by')='av1'"),
    ("UPDATE, extract first", False,
     "UPDATE things SET x = 7 WHERE json_extract(state_json,'$.carried_by')='av1' AND x = 100"),
]

out = {"sqlite_version": sqlite3.sqlite_version, "cases": []}
print("sqlite", sqlite3.sqlite_version)
for label, index, sql in CASES:
    conn = build(index=index)
    plan = " / ".join(r[3] for r in conn.execute("EXPLAIN QUERY PLAN " + sql))
    try:
        cur = conn.execute(sql)
        result = "ok: %d rows" % (len(cur.fetchall()) if sql.startswith("SELECT") else cur.rowcount)
    except sqlite3.Error as exc:
        result = "%s: %s" % (type(exc).__name__, exc)
    print(f"{'PASS' if result.startswith('ok') else 'FAIL'}  {label:44s} {result}")
    print(f"        plan: {plan}")
    out["cases"].append({"case": label, "sql": sql, "result": result, "plan": plan,
                         "survived": result.startswith("ok")})
with open("order.json", "w") as fh:
    json.dump(out, fh, indent=2, sort_keys=True)