"""Compare foreign-key enforcement in a disposable in-memory database."""
import argparse
import json
import sqlite3
import sys


def open_db(enforce=True):
    # No implicit BEGIN: all write transactions below are explicit.
    con = sqlite3.connect(":memory:", isolation_level=None)
    try:
        con.execute("PRAGMA foreign_keys = " + ("ON" if enforce else "OFF"))
        expected = (1 if enforce else 0,)
        if con.execute("PRAGMA foreign_keys").fetchone() != expected:
            raise RuntimeError("foreign-key setting was not applied")
        con.execute("CREATE TABLE jobs (id INTEGER PRIMARY KEY)")
        con.execute("""CREATE TABLE results (
            id INTEGER PRIMARY KEY,
            job_id INTEGER NOT NULL REFERENCES jobs(id)
        )""")
        con.execute("CREATE INDEX results_job_id ON results(job_id)")
        con.execute("INSERT INTO jobs VALUES (1)")
        return con
    except Exception:
        con.close()
        raise


def run_demo(enforce=True):
    con = open_db(enforce)
    error = None
    try:
        try:
            con.execute("BEGIN")
            con.execute("INSERT INTO results VALUES (?, ?)", (1, 1))
            con.execute("INSERT INTO results VALUES (?, ?)", (2, 999))
            con.execute("COMMIT")
        except sqlite3.IntegrityError as exc:
            con.execute("ROLLBACK")
            error = str(exc)
        report = {
            "accepted": error is None,
            "foreign_keys": con.execute("PRAGMA foreign_keys").fetchone()[0],
            "result_rows": con.execute("SELECT COUNT(*) FROM results").fetchone()[0],
            "violations": con.execute("PRAGMA foreign_key_check").fetchall(),
        }
        return report, error
    finally:
        con.close()


def main():
    parser = argparse.ArgumentParser(description=__doc__, allow_abbrev=False)
    parser.add_argument("--enforce", choices=("on", "off"), default="on")
    args = parser.parse_args()
    report, error = run_demo(args.enforce == "on")
    print(json.dumps(report, sort_keys=True))
    if error:
        print("ERROR: " + error, file=sys.stderr)
        return 1
    return 0


if __name__ == "__main__":
    raise SystemExit(main())