MODULE SQLiteShowcase;
(* Prepared-statement walkthrough:
   open/exec/close, prepare, bind int/text, step, column int/text,
   finalize.  Prints the two stored rows. *)

FROM SYSTEM IMPORT ADDRESS;
FROM CStr IMPORT CString, Copy;
FROM SysIO IMPORT Write, WriteLn, WriteInt, WriteChar;
FROM SQLite IMPORT
  SQLiteOk, SQLiteRow, SQLiteDone,
  sqlite3_open, sqlite3_exec, sqlite3_close,
  sqlite3_prepare_v2, sqlite3_bind_int, sqlite3_bind_text,
  sqlite3_step, sqlite3_column_int, sqlite3_column_text,
  sqlite3_finalize, sqlite3_free, SQLiteTransient;

VAR
  ExitCode : INTEGER;
  db : ADDRESS;

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

PROCEDURE InsertPerson (id : INTEGER; name : ARRAY OF CHAR) : BOOLEAN;
VAR
  stmt : ADDRESS;
  rc : INTEGER;
  ok : BOOLEAN;
BEGIN
  stmt := NIL;
  rc := sqlite3_prepare_v2(db, "INSERT INTO people VALUES (?, ?);",
                           -1, stmt, NIL);
  ok := (rc = SQLiteOk) AND (stmt # NIL);
  IF ok THEN
    ok := (sqlite3_bind_int(stmt, 1, id) = SQLiteOk)
          AND (sqlite3_bind_text(stmt, 2, name, -1,
                                 SQLiteTransient()) = SQLiteOk)
  END;
  IF ok THEN
    ok := sqlite3_step(stmt) = SQLiteDone
  END;
  IF stmt # NIL THEN
    IF sqlite3_finalize(stmt) # SQLiteOk THEN ok := FALSE END
  END;
  RETURN ok
END InsertPerson;

PROCEDURE ShowPeople () : BOOLEAN;
VAR
  stmt : ADDRESS;
  rc : INTEGER;
  id : INTEGER;
  name : ARRAY [0..63] OF CHAR;
  ctext : CString;
  n : CARDINAL;
  ok : BOOLEAN;
BEGIN
  stmt := NIL;
  rc := sqlite3_prepare_v2(db, "SELECT id, name FROM people ORDER BY id;",
                           -1, stmt, NIL);
  ok := (rc = SQLiteOk) AND (stmt # NIL);
  IF ok THEN
    LOOP
      rc := sqlite3_step(stmt);
      IF rc = SQLiteRow THEN
        id := sqlite3_column_int(stmt, 0);
        ctext := sqlite3_column_text(stmt, 1);
        IF ctext = NIL THEN
          ok := FALSE; EXIT
        END;
        n := Copy(ctext, name);
        WriteInt(id);
        WriteChar(':');
        WriteChar(' ');
        Write(name);
        WriteLn
      ELSIF rc = SQLiteDone THEN
        EXIT
      ELSE
        ok := FALSE; EXIT
      END
    END
  END;
  IF stmt # NIL THEN
    IF sqlite3_finalize(stmt) # SQLiteOk THEN ok := FALSE END
  END;
  RETURN ok
END ShowPeople;

BEGIN
  db := NIL;
  IF sqlite3_open(":memory:", db) = SQLiteOk THEN
    IF ExecSQL("CREATE TABLE people(id INTEGER, name TEXT);")
       AND InsertPerson(1, "Ada")
       AND InsertPerson(2, "Grace")
       AND ShowPeople() THEN
      ExitCode := 42
    ELSE
      ExitCode := 1
    END
  ELSE
    ExitCode := 1
  END;
  IF db # NIL THEN
    IF sqlite3_close(db) # SQLiteOk THEN ExitCode := 1 END
  END
END SQLiteShowcase.