MODULE SQLiteHelpers;
(* Exercise SQLiteUtils and the typed handle aliases. *)

FROM SYSTEM IMPORT ADDRESS, ADR;
FROM SQLite IMPORT
  DbHandle, StmtHandle, ContextHandle, ValueHandle,
  SQLiteOk, SQLiteRow, SQLiteDone, SQLiteError, SQLiteText,
  SQLiteOpenReadWrite, SQLiteOpenCreate, SQLiteOpenMemory,
  SQLiteUtf8,
  sqlite3_open, sqlite3_open_v2, sqlite3_close,
  sqlite3_step, sqlite3_finalize,
  sqlite3_column_int, sqlite3_column_text, sqlite3_column_type,
  sqlite3_create_function_v2,
  sqlite3_value_type, SQLiteArrayAt, SQLiteTransient;
FROM SQLiteUtils IMPORT
  CStrToM2, ErrMsg, ErrStr, LibVersionStr, StaticDestr, TransientDestr,
  BindTextCopy, BindBlobCopy, PrepareOne, ValueText, ResultTextCopy,
  BackupToFile, ExecSimple, ExecWithErr;

VAR
  ExitCode : INTEGER;
  db, fileDb : DbHandle;
  stmt : StmtHandle;
  rc : INTEGER;
  ok : BOOLEAN;
  bin : ARRAY [0..7] OF CHAR;
  buf : ARRAY [0..127] OF CHAR;

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 Echo (ctx : ContextHandle; argc : INTEGER; argv : ADDRESS);
VAR
  v : ValueHandle;
  text : ARRAY [0..63] OF CHAR;
BEGIN
  IF argc = 1 THEN
    v := SQLiteArrayAt(argv, 0);
    IF sqlite3_value_type(v) = SQLiteText THEN
      ValueText(v, text);
      ResultTextCopy(ctx, text)
    END
  END
END Echo;

PROCEDURE QueryCount (h : DbHandle; VAR n : INTEGER) : BOOLEAN;
VAR
  s : StmtHandle;
  code : INTEGER;
  good : BOOLEAN;
BEGIN
  s := NIL; n := -1; good := FALSE;
  code := PrepareOne(h, "SELECT count(*) FROM t;", s);
  IF (code = SQLiteOk) AND (s # NIL) THEN
    IF sqlite3_step(s) = SQLiteRow THEN
      n := 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
END QueryCount;

BEGIN
  ExitCode := 1;
  db := NIL; fileDb := NIL; stmt := NIL; ok := FALSE;
  bin[0] := CHR(0); bin[1] := CHR(1); bin[2] := CHR(2);

  IF (sqlite3_open_v2(":memory:", db,
        SQLiteOpenReadWrite + SQLiteOpenCreate + SQLiteOpenMemory,
        NIL) = SQLiteOk) AND (db # NIL)
     AND (ExecSimple(db, "CREATE TABLE t(a TEXT, b BLOB);") = SQLiteOk)
     AND (PrepareOne(db, "INSERT INTO t(a,b) VALUES(?,?);", stmt) = SQLiteOk)
     AND (BindTextCopy(stmt, 1, "hello") = SQLiteOk)
     AND (BindBlobCopy(stmt, 2, ADR(bin[0]), 3) = SQLiteOk)
     AND (sqlite3_step(stmt) = SQLiteDone) THEN
    ok := sqlite3_finalize(stmt) = SQLiteOk;
    stmt := NIL
  END;

  IF ok THEN
    ok := (ExecWithErr(db, "INSERT INTO bogus(x) VALUES(1);", buf) # SQLiteOk)
          AND (buf[0] # CHR(0));
    IF ok THEN
      ErrMsg(db, buf);
      ok := buf[0] # CHR(0)
    END
  END;
  IF ok THEN
    ErrStr(SQLiteError, buf);
    ok := buf[0] # CHR(0)
  END;
  IF ok THEN
    LibVersionStr(buf);
    ok := buf[0] # CHR(0)
  END;
  IF ok THEN
    CStrToM2(NIL, buf);
    ok := buf[0] = CHR(0)
  END;
  IF ok THEN
    ok := (StaticDestr() = NIL) AND (TransientDestr() = SQLiteTransient())
  END;

  (* ResultTextCopy + ValueText through a UDF. *)
  IF ok THEN
    ok := sqlite3_create_function_v2(db, "m2_echo", 1, SQLiteUtf8,
      NIL, ADR(Echo), NIL, NIL, NIL) = SQLiteOk
  END;
  IF ok THEN
    stmt := NIL;
    ok := (PrepareOne(db, "SELECT m2_echo('hey');", stmt) = SQLiteOk)
          AND (stmt # NIL)
          AND (sqlite3_step(stmt) = SQLiteRow)
          AND (sqlite3_column_type(stmt, 0) = SQLiteText);
    IF ok THEN
      CStrToM2(sqlite3_column_text(stmt, 0), buf);
      ok := SameText(buf, "hey")
    END;
    IF stmt # NIL THEN
      IF sqlite3_finalize(stmt) # SQLiteOk THEN ok := FALSE END;
      stmt := NIL
    END
  END;

  (* BackupToFile and a file-backed verification query. *)
  IF ok THEN
    ok := BackupToFile(db, "/tmp/m2-sqlite-utils.db") = SQLiteOk
  END;
  IF ok THEN
    fileDb := NIL;
    ok := (sqlite3_open("/tmp/m2-sqlite-utils.db", fileDb) = SQLiteOk)
          AND (fileDb # NIL);
    IF ok THEN
      rc := 0;
      ok := QueryCount(fileDb, rc) AND (rc = 1)
    END;
    IF fileDb # NIL THEN
      IF sqlite3_close(fileDb) # SQLiteOk THEN ok := FALSE END;
      fileDb := NIL
    END
  END;

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