MODULE SQLiteParams;
(* Statement parameter introspection:
   count, names, indices, anonymous/named parameters, unbound NULL,
   reset/clear and a true 64-bit integer value. *)

FROM SYSTEM IMPORT ADDRESS;
FROM CStr IMPORT CString, Copy;
FROM SQLite IMPORT
  SQLiteOk, SQLiteRow, SQLiteDone, SQLiteInteger, SQLiteText, SQLiteNull,
  SQLiteOpenReadWrite, SQLiteOpenCreate, SQLiteOpenMemory,
  sqlite3_open_v2, sqlite3_exec, sqlite3_close_v2,
  sqlite3_prepare_v2, sqlite3_step, sqlite3_reset,
  sqlite3_clear_bindings, sqlite3_finalize,
  sqlite3_bind_int, sqlite3_bind_int64, sqlite3_bind_double,
  sqlite3_bind_null, sqlite3_bind_text,
  sqlite3_bind_parameter_count, sqlite3_bind_parameter_name,
  sqlite3_bind_parameter_index,
  sqlite3_column_count, sqlite3_column_type, sqlite3_column_int,
  sqlite3_column_int64, sqlite3_column_double, sqlite3_column_text,
  sqlite3_changes, sqlite3_free, SQLiteTransient;

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

PROCEDURE SameText (a, b : ARRAY OF CHAR) : BOOLEAN;
VAR
  i : INTEGER;
BEGIN
  i := 0;
  WHILE (a[i] # CHR(0)) AND (a[i] = b[i]) DO
    i := i + 1
  END;
  RETURN (a[i] = CHR(0)) AND (b[i] = CHR(0))
END SameText;

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 NameIs (s : ADDRESS; idx : INTEGER;
                  want : ARRAY OF CHAR) : BOOLEAN;
VAR
  n : CString;
  buf : ARRAY [0..63] OF CHAR;
BEGIN
  n := sqlite3_bind_parameter_name(s, idx);
  IF n = NIL THEN RETURN FALSE END;
  IF Copy(n, buf) = 0 THEN RETURN FALSE END;
  RETURN SameText(buf, want)
END NameIs;

PROCEDURE NameIsNil (s : ADDRESS; idx : INTEGER) : BOOLEAN;
BEGIN
  RETURN sqlite3_bind_parameter_name(s, idx) = NIL
END NameIsNil;

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 t(a INTEGER, b INTEGER, c REAL, d TEXT);") THEN
      ok := TRUE
    END;

    (* Four anonymous parameters, including a real 64-bit value. *)
    IF ok THEN
      stmt := NIL;
      rc := sqlite3_prepare_v2(db,
        "INSERT INTO t(a,b,c,d) VALUES(?,?,?,?);", -1, stmt, NIL);
      ok := (rc = SQLiteOk) AND (stmt # NIL)
            AND (sqlite3_bind_parameter_count(stmt) = 4)
            AND NameIsNil(stmt, 1)
            AND (sqlite3_bind_parameter_index(stmt, ":missing") = 0);
      IF ok THEN
        ok := (sqlite3_bind_int(stmt, 1, 42) = SQLiteOk)
              AND (sqlite3_bind_int64(stmt, 2,
                    VAL(LONGINT, 9000000001)) = SQLiteOk)
              AND (sqlite3_bind_double(stmt, 3, 2.5) = SQLiteOk)
              AND (sqlite3_bind_text(stmt, 4, "hello", -1,
                                     SQLiteTransient()) = SQLiteOk)
      END;
      IF ok THEN ok := sqlite3_step(stmt) = SQLiteDone END;
      IF ok THEN ok := sqlite3_changes(db) = 1 END;
      IF stmt # NIL THEN
        IF sqlite3_finalize(stmt) # SQLiteOk THEN ok := FALSE END;
        stmt := NIL
      END
    END;

    IF ok THEN
      stmt := NIL;
      rc := sqlite3_prepare_v2(db,
        "SELECT a,b,c,d FROM t WHERE a = 42;", -1, stmt, NIL);
      ok := (rc = SQLiteOk) AND (stmt # NIL)
            AND (sqlite3_column_count(stmt) = 4);
      IF ok THEN
        ok := sqlite3_step(stmt) = SQLiteRow;
        IF ok THEN
          ok := (sqlite3_column_type(stmt, 0) = SQLiteInteger)
                AND (sqlite3_column_int(stmt, 0) = 42)
                AND (sqlite3_column_int64(stmt, 1)
                     = VAL(LONGINT, 9000000001))
                AND (sqlite3_column_double(stmt, 2) = 2.5)
                AND (sqlite3_column_type(stmt, 3) = SQLiteText);
          IF sqlite3_step(stmt) # SQLiteDone THEN ok := FALSE END
        END
      END;
      IF stmt # NIL THEN
        IF sqlite3_finalize(stmt) # SQLiteOk THEN ok := FALSE END;
        stmt := NIL
      END
    END;

    (* Named parameters: :1, anonymous ?, and :abc. *)
    IF ok THEN
      stmt := NIL;
      rc := sqlite3_prepare_v2(db,
        "INSERT INTO t(a,b,d) VALUES(:1, ?, :abc);", -1, stmt, NIL);
      ok := (rc = SQLiteOk) AND (stmt # NIL)
            AND (sqlite3_bind_parameter_count(stmt) = 3)
            AND NameIs(stmt, 1, ":1")
            AND NameIsNil(stmt, 2)
            AND NameIs(stmt, 3, ":abc")
            AND (sqlite3_bind_parameter_index(stmt, ":1") = 1)
            AND (sqlite3_bind_parameter_index(stmt, ":abc") = 3)
            AND (sqlite3_bind_parameter_index(stmt, ":missing") = 0);
      IF ok THEN
        ok := (sqlite3_bind_int(stmt, 1, 100) = SQLiteOk)
              AND (sqlite3_bind_int(stmt, 2, 200) = SQLiteOk)
              AND (sqlite3_bind_text(stmt, 3, "named", -1,
                                     SQLiteTransient()) = SQLiteOk)
      END;
      IF ok THEN ok := sqlite3_step(stmt) = SQLiteDone END;
      IF ok THEN ok := sqlite3_reset(stmt) = SQLiteOk END;
      IF ok THEN ok := sqlite3_clear_bindings(stmt) = SQLiteOk END;

      (* After clear_bindings, bind only :1; other parameters are NULL. *)
      IF ok THEN ok := sqlite3_bind_int(stmt, 1, 7) = SQLiteOk END;
      IF ok THEN ok := sqlite3_step(stmt) = SQLiteDone END;
      IF stmt # NIL THEN
        IF sqlite3_finalize(stmt) # SQLiteOk THEN ok := FALSE END;
        stmt := NIL
      END
    END;

    IF ok THEN
      stmt := NIL;
      rc := sqlite3_prepare_v2(db,
        "SELECT b,d FROM t WHERE a = 7;", -1, stmt, NIL);
      ok := (rc = SQLiteOk) AND (stmt # NIL);
      IF ok THEN
        ok := sqlite3_step(stmt) = SQLiteRow;
        IF ok THEN
          ok := (sqlite3_column_type(stmt, 0) = SQLiteNull)
                AND (sqlite3_column_type(stmt, 1) = SQLiteNull);
          IF sqlite3_step(stmt) # SQLiteDone THEN ok := FALSE END
        END
      END;
      IF stmt # NIL THEN
        IF sqlite3_finalize(stmt) # SQLiteOk THEN ok := FALSE END;
        stmt := NIL
      END
    END
  END;

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