MODULE SQLiteConnection;
(* Connection flags, close_v2, changes and last_insert_rowid. *)

FROM SYSTEM IMPORT ADDRESS;
FROM SQLite IMPORT
  SQLiteOk,
  SQLiteOpenReadOnly, SQLiteOpenReadWrite, SQLiteOpenCreate,
  SQLiteOpenMemory, SQLiteReadOnly,
  sqlite3_open_v2, sqlite3_exec, sqlite3_close_v2,
  sqlite3_prepare_v2, sqlite3_finalize,
  sqlite3_changes, sqlite3_total_changes, sqlite3_last_insert_rowid,
  sqlite3_free;

VAR
  ExitCode : INTEGER;
  db, stmt, errmsg : ADDRESS;
  rc : INTEGER;
  ok : BOOLEAN;

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;

BEGIN
  ExitCode := 1;
  db := NIL; stmt := NIL;
  ok := FALSE;

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

    IF ok THEN
      ok := ExecSQL("INSERT INTO items(v) VALUES ('a');")
            AND (sqlite3_changes(db) = 1)
            AND (sqlite3_total_changes(db) = 1)
            AND (sqlite3_last_insert_rowid(db) = 1)
    END;

    IF ok THEN
      ok := ExecSQL("INSERT INTO items(v) VALUES ('b');")
            AND (sqlite3_changes(db) = 1)
            AND (sqlite3_total_changes(db) = 2)
            AND (sqlite3_last_insert_rowid(db) = 2)
    END;

    IF ok THEN
      ok := ExecSQL("UPDATE items SET v = v || '!';")
            AND (sqlite3_changes(db) = 2)
            AND (sqlite3_total_changes(db) = 4)
            AND (sqlite3_last_insert_rowid(db) = 2)
    END;

    IF ok THEN
      ok := ExecSQL("DELETE FROM items WHERE id = 1;")
            AND (sqlite3_changes(db) = 1)
            AND (sqlite3_total_changes(db) = 5)
    END;

    (* Leave a statement open across close_v2 to exercise deferred close. *)
    IF ok THEN
      rc := sqlite3_prepare_v2(db, "SELECT 1;", -1, stmt, NIL);
      ok := (rc = SQLiteOk) AND (stmt # NIL)
    END;
    IF ok THEN
      ok := sqlite3_close_v2(db) = SQLiteOk
    END;
    IF ok THEN
      ok := sqlite3_finalize(stmt) = SQLiteOk
    END;
    stmt := NIL;
    db := NIL
  END;

  (* A read-only connection must reject writes. *)
  IF ok THEN
    IF sqlite3_open_v2(":memory:", db, SQLiteOpenReadOnly,
                       NIL) = SQLiteOk THEN
      errmsg := NIL;
      ok := sqlite3_exec(db, "CREATE TABLE ro(x INTEGER);",
                         NIL, NIL, errmsg) = SQLiteReadOnly;
      IF errmsg # NIL THEN
        sqlite3_free(errmsg);
        errmsg := NIL
      END;
      IF sqlite3_close_v2(db) # SQLiteOk THEN ok := FALSE END;
      db := NIL
    ELSE
      ok := FALSE
    END
  END;

  IF ok THEN ExitCode := 42 END
END SQLiteConnection.