MODULE SQLiteHooks;
(* Connection hooks:
   busy handler/timeout, progress, trace_v2, update/commit/rollback,
   authorizer and sqlite3_db_handle. *)

FROM SYSTEM IMPORT ADDRESS, ADR;
FROM CStr IMPORT CString, Copy;
FROM SQLite IMPORT
  SQLiteOk, SQLiteRow, SQLiteDone, SQLiteAuth,
  SQLiteOpenReadWrite, SQLiteOpenCreate, SQLiteOpenMemory,
  SQLiteOpInsert, SQLiteOpUpdate, SQLiteOpDelete,
  SQLiteAuthOk, SQLiteAuthDeny, SQLiteAuthDelete,
  SQLiteTraceStmt,
  sqlite3_open_v2, sqlite3_exec, sqlite3_close_v2,
  sqlite3_prepare_v2, sqlite3_step, sqlite3_column_int,
  sqlite3_finalize, sqlite3_free,
  sqlite3_busy_timeout, sqlite3_busy_handler, sqlite3_interrupt,
  sqlite3_progress_handler, sqlite3_trace_v2,
  sqlite3_update_hook, sqlite3_commit_hook, sqlite3_rollback_hook,
  sqlite3_set_authorizer, sqlite3_db_handle;

VAR
  ExitCode : INTEGER;
  db, stmt, prev : ADDRESS;
  rc : INTEGER;
  ok : BOOLEAN;
  progressCalls, traceCalls, updateCalls : INTEGER;
  commitCalls, rollbackCalls, authCalls : INTEGER;
  lastOp : INTEGER;
  lastRowid : LONGINT;
  hookBad : BOOLEAN;
  tableBuf : ARRAY [0..31] OF CHAR;

PROCEDURE ExecSQL (sql : ARRAY OF CHAR) : BOOLEAN;
VAR
  errmsg : ADDRESS;
  good : BOOLEAN;
BEGIN
  errmsg := NIL;
  good := sqlite3_exec(db, sql, NIL, NIL, errmsg) = SQLiteOk;
  IF errmsg # NIL THEN
    sqlite3_free(errmsg)
  END;
  RETURN good
END ExecSQL;

PROCEDURE QueryInt (sql : ARRAY OF CHAR; want : INTEGER) : BOOLEAN;
VAR
  s : ADDRESS;
  code, got : INTEGER;
  good : BOOLEAN;
BEGIN
  s := NIL; got := -777; good := FALSE;
  code := sqlite3_prepare_v2(db, sql, -1, s, NIL);
  IF (code = SQLiteOk) AND (s # NIL) THEN
    IF sqlite3_step(s) = SQLiteRow THEN
      got := sqlite3_column_int(s, 0);
      IF sqlite3_step(s) = SQLiteDone THEN good := TRUE END
    END;
    IF sqlite3_finalize(s) # SQLiteOk THEN good := FALSE END
  END;
  RETURN good AND (got = want)
END QueryInt;

PROCEDURE BusyFn (arg : ADDRESS; count : INTEGER) : INTEGER;
BEGIN
  RETURN 0
END BusyFn;

PROCEDURE ProgressFn (arg : ADDRESS) : INTEGER;
BEGIN
  progressCalls := progressCalls + 1;
  RETURN 0
END ProgressFn;

PROCEDURE TraceFn (mask : CARDINAL; ctx, sql, expanded : ADDRESS) : INTEGER;
BEGIN
  traceCalls := traceCalls + 1;
  RETURN 0
END TraceFn;

PROCEDURE UpdateFn (arg : ADDRESS; op : INTEGER;
                    dbName, tableName : ADDRESS; rowid : LONGINT);
VAR
  s : CString;
BEGIN
  updateCalls := updateCalls + 1;
  lastOp := op;
  lastRowid := rowid;
  s := tableName;
  IF (s = NIL) OR (Copy(s, tableBuf) = 0) OR (tableBuf[0] # 'h') THEN
    hookBad := TRUE
  END
END UpdateFn;

PROCEDURE CommitFn (arg : ADDRESS) : INTEGER;
BEGIN
  commitCalls := commitCalls + 1;
  RETURN 0
END CommitFn;

PROCEDURE RollbackFn (arg : ADDRESS);
BEGIN
  rollbackCalls := rollbackCalls + 1
END RollbackFn;

PROCEDURE AuthFn (arg : ADDRESS; action : INTEGER;
                  dbName, tableName, colName, triggerName : ADDRESS) : INTEGER;
BEGIN
  authCalls := authCalls + 1;
  IF action = SQLiteAuthDelete THEN
    RETURN SQLiteAuthDeny
  ELSE
    RETURN SQLiteAuthOk
  END
END AuthFn;

BEGIN
  ExitCode := 1;
  db := NIL; stmt := NIL; prev := NIL;
  progressCalls := 0; traceCalls := 0; updateCalls := 0;
  commitCalls := 0; rollbackCalls := 0; authCalls := 0;
  lastOp := 0; lastRowid := 0; hookBad := FALSE;
  ok := FALSE;

  IF (sqlite3_open_v2(":memory:", db,
        SQLiteOpenReadWrite + SQLiteOpenCreate + SQLiteOpenMemory,
        NIL) = SQLiteOk) AND (db # NIL) THEN
    IF ExecSQL("CREATE TABLE h(id INTEGER PRIMARY KEY, v TEXT);") THEN
      ok := TRUE
    END;

    IF ok THEN
      ok := (sqlite3_busy_timeout(db, 100) = SQLiteOk)
            AND (sqlite3_busy_handler(db, ADR(BusyFn), NIL) = SQLiteOk)
            AND (sqlite3_busy_handler(db, NIL, NIL) = SQLiteOk)
    END;

    (* sqlite3_db_handle returns the owning connection. *)
    IF ok THEN
      stmt := NIL;
      rc := sqlite3_prepare_v2(db, "SELECT 1;", -1, stmt, NIL);
      ok := (rc = SQLiteOk) AND (stmt # NIL)
            AND (sqlite3_db_handle(stmt) = db);
      IF stmt # NIL THEN
        IF sqlite3_finalize(stmt) # SQLiteOk THEN ok := FALSE END;
        stmt := NIL
      END
    END;

    (* Progress handler runs during a recursive query. *)
    IF ok THEN
      progressCalls := 0;
      sqlite3_progress_handler(db, 1, ADR(ProgressFn), NIL);
      ok := QueryInt("SELECT 1;", 1) AND (progressCalls > 0);
      sqlite3_progress_handler(db, 0, NIL, NIL)
    END;

    (* Trace callback sees prepared statements. *)
    IF ok THEN
      traceCalls := 0;
      ok := sqlite3_trace_v2(db, SQLiteTraceStmt,
                             ADR(TraceFn), NIL) = SQLiteOk
    END;
    IF ok THEN ok := QueryInt("SELECT 1;", 1) AND (traceCalls > 0) END;
    IF ok THEN
      ok := sqlite3_trace_v2(db, 0, NIL, NIL) = SQLiteOk
    END;

    (* Update hook fires for INSERT, UPDATE and DELETE. *)
    IF ok THEN
      updateCalls := 0; hookBad := FALSE;
      prev := sqlite3_update_hook(db, ADR(UpdateFn), NIL)
    END;
    IF ok THEN
      ok := ExecSQL("INSERT INTO h(v) VALUES ('a');")
            AND (updateCalls = 1) AND (lastOp = SQLiteOpInsert)
    END;
    IF ok THEN
      ok := ExecSQL("UPDATE h SET v = 'b' WHERE id = 1;")
            AND (updateCalls = 2) AND (lastOp = SQLiteOpUpdate)
    END;
    IF ok THEN
      ok := ExecSQL("DELETE FROM h WHERE id = 1;")
            AND (updateCalls = 3) AND (lastOp = SQLiteOpDelete)
            AND (NOT hookBad)
    END;
    IF ok THEN prev := sqlite3_update_hook(db, NIL, NIL) END;

    (* Commit and rollback hooks. *)
    IF ok THEN
      commitCalls := 0; rollbackCalls := 0;
      prev := sqlite3_commit_hook(db, ADR(CommitFn), NIL);
      sqlite3_rollback_hook(db, ADR(RollbackFn), NIL)
    END;
    IF ok THEN
      ok := ExecSQL("BEGIN; INSERT INTO h(v) VALUES ('c'); COMMIT;")
            AND (commitCalls = 1)
    END;
    IF ok THEN
      ok := ExecSQL("BEGIN; INSERT INTO h(v) VALUES ('d'); ROLLBACK;")
            AND (rollbackCalls = 1)
    END;
    IF ok THEN
      prev := sqlite3_commit_hook(db, NIL, NIL);
      sqlite3_rollback_hook(db, NIL, NIL)
    END;

    (* Authorizer: allow SELECT, deny DELETE. *)
    IF ok THEN
      authCalls := 0;
      ok := sqlite3_set_authorizer(db, ADR(AuthFn), NIL) = SQLiteOk
    END;
    IF ok THEN
      stmt := NIL;
      rc := sqlite3_prepare_v2(db, "DELETE FROM h;", -1, stmt, NIL);
      ok := (rc = SQLiteAuth) AND (stmt = NIL) AND (authCalls > 0);
      IF stmt # NIL THEN
        IF sqlite3_finalize(stmt) # SQLiteOk THEN ok := FALSE END;
        stmt := NIL
      END
    END;
    IF ok THEN ok := QueryInt("SELECT count(*) FROM h;", 1) END;
    IF ok THEN
      ok := sqlite3_set_authorizer(db, NIL, NIL) = SQLiteOk
    END;
    IF ok THEN ok := ExecSQL("DELETE FROM h;") END;

    IF ok THEN sqlite3_interrupt(db) END;
    IF stmt # NIL THEN
      IF sqlite3_finalize(stmt) # SQLiteOk THEN ok := FALSE END;
      stmt := NIL
    END
  END;

  IF db # NIL THEN
    IF sqlite3_close_v2(db) # SQLiteOk THEN ok := FALSE END
  END;
  IF ok THEN ExitCode := 42 END
END SQLiteHooks.