SQLITE-m2/showcases/showcase_all.mod

MODULE showcase_all ;

(*
   m2SQLITE showcase: exercises every procedure of the SQLite
   binding plus all SQLiteUtils helpers end to end.

   1. library info: version, source id, threadsafe, errstr, complete
   2. open, autocommit, busy timeout, extended codes, limit, open_v2
   3. exec with raw errmsg capture, ExecWithErr failure, errcode paths
   4. prepare_v2 tail capture, prepare_v3 flags, sql text, parameter ids
   5. all bind kinds, step, changes counters, rowid
   6. all column kinds, metadata, busy/reset/rebind, transactions
   7. expanded sql, zeroblob, static destructor, close_v2
   8. custom functions: scalar tax plus aggregate total
   9. blob read/write plus file backup
   10. extended codes plus result tables
   11. close
*)

FROM SYSTEM IMPORT ADDRESS, ADR;
FROM SQLite IMPORT DbHandle, StmtHandle, ContextHandle, ValueHandle,
   BlobHandle,
   SQLiteOk, SQLiteRow, SQLiteDone,
   SQLiteInteger, SQLiteFloat, SQLiteText, SQLiteBlob, SQLiteNull,
   SQLiteOpenReadWrite, SQLiteOpenCreate,
   SQLitePreparePersistent, SQLiteLimitVariableNumber,
   SQLiteUtf8, SQLiteDeterministic, SQLiteConstraintUnique,
   sqlite3_libversion, sqlite3_libversion_number, sqlite3_sourceid,
   sqlite3_threadsafe,
   sqlite3_open, sqlite3_open_v2, sqlite3_close, sqlite3_close_v2,
   sqlite3_exec,
   sqlite3_errcode, sqlite3_extended_errcode, sqlite3_errmsg,
   sqlite3_errstr, sqlite3_error_offset, sqlite3_free,
   sqlite3_complete, sqlite3_busy_timeout, sqlite3_get_autocommit,
   sqlite3_changes, sqlite3_total_changes, sqlite3_last_insert_rowid,
   sqlite3_extended_result_codes, sqlite3_limit,
   sqlite3_prepare_v2, sqlite3_prepare_v3,
   sqlite3_step, sqlite3_finalize, sqlite3_reset, sqlite3_clear_bindings,
   sqlite3_data_count, sqlite3_column_count,
   sqlite3_sql, sqlite3_expanded_sql,
   sqlite3_stmt_readonly, sqlite3_stmt_busy,
   sqlite3_bind_blob, sqlite3_bind_zeroblob,
   sqlite3_bind_double, sqlite3_bind_int, sqlite3_bind_int64,
   sqlite3_bind_null, sqlite3_bind_text,
   sqlite3_bind_parameter_count, sqlite3_bind_parameter_name,
   sqlite3_bind_parameter_index,
   sqlite3_column_blob, sqlite3_column_double, sqlite3_column_int,
   sqlite3_column_int64, sqlite3_column_text, sqlite3_column_bytes,
   sqlite3_column_type, sqlite3_column_name, sqlite3_column_decltype,
   sqlite3_create_function,
   sqlite3_value_int64, sqlite3_value_double,
   sqlite3_result_double, sqlite3_result_int64,
   sqlite3_aggregate_context,
   sqlite3_blob_open, sqlite3_blob_close,
   sqlite3_blob_bytes, sqlite3_blob_read, sqlite3_blob_write;
FROM SQLiteUtils IMPORT CStrToM2, ErrMsg, ErrStr, LibVersionStr,
   StaticDestr, TransientDestr,
   BindTextCopy, BindBlobCopy, ExecSimple, ExecWithErr, BackupToFile,
   GetTable, TableCell, FreeTable;
FROM libc IMPORT printf, strncpy, unlink;

TYPE
   ArgVec = POINTER TO ARRAY [0..255] OF ADDRESS;
   TotalPtr = POINTER TO LONGINT;

VAR
   db, db2, db3: DbHandle;
   stmt: StmtHandle;
   bh: BlobHandle;
   tbl: ADDRESS; trows, tcols: INTEGER;
   rc, i, ncols, ndata, busy, ro: INTEGER;
   li: LONGINT;
   d: REAL;
   tp: INTEGER;
   tail, msg, p, xsql: ADDRESS;
   buf, buf2: ARRAY [0..127] OF CHAR;
   blobIn: ARRAY [0..3] OF CHAR;
   staticBlob: ARRAY [0..3] OF CHAR;
   out: ARRAY [0..15] OF CHAR;
   wpat: ARRAY [0..15] OF CHAR;
   rback: ARRAY [0..63] OF CHAR;
   showInt, showFloat, showText, showBlob, showNull: INTEGER;

PROCEDURE RemoveFile (path: ARRAY OF CHAR);
VAR fbuf: ARRAY [0..255] OF CHAR; n: INTEGER; urc: INTEGER;
BEGIN
   n := 0;
   WHILE (VAL(CARDINAL, n) <= HIGH(fbuf))
         AND (VAL(CARDINAL, n) <= HIGH(path))
         AND (path[n] # 0C) DO
      fbuf[n] := path[n];
      INC(n)
   END;
   IF VAL(CARDINAL, n) <= HIGH(fbuf) THEN fbuf[n] := 0C END;
   urc := unlink(ADR(fbuf))
END RemoveFile;

PROCEDURE fail (what: ARRAY OF CHAR);
VAR e: ARRAY [0..255] OF CHAR;
BEGIN
   ErrMsg(db, e);
   printf("FAIL %s: %s\n", what, e);
   HALT(1)
END fail;

PROCEDURE check (rc: INTEGER; what: ARRAY OF CHAR);
BEGIN
   IF rc # SQLiteOk THEN fail(what) END
END check;

PROCEDURE section (n: INTEGER; title: ARRAY OF CHAR);
BEGIN
   printf("--- [%d] %s ---\n", n, title)
END section;

PROCEDURE ArgAt (argv: ADDRESS; i: INTEGER) : ValueHandle;
VAR vec: ArgVec;
BEGIN
   vec := VAL(ArgVec, argv);
   RETURN vec^[i]
END ArgAt;

PROCEDURE tax (ctx: ContextHandle; argc: INTEGER; argv: ADDRESS);
BEGIN
   sqlite3_result_double(ctx,
      sqlite3_value_double(ArgAt(argv, 0)) * 1.2)
END tax;

PROCEDURE totalStep (ctx: ContextHandle; argc: INTEGER; argv: ADDRESS);
VAR s: TotalPtr;
BEGIN
   s := VAL(TotalPtr, sqlite3_aggregate_context(ctx, 8));
   s^ := s^ + sqlite3_value_int64(ArgAt(argv, 0))
END totalStep;

PROCEDURE totalFinal (ctx: ContextHandle);
VAR s: TotalPtr;
BEGIN
   s := VAL(TotalPtr, sqlite3_aggregate_context(ctx, 0));
   sqlite3_result_int64(ctx, s^)
END totalFinal;

BEGIN
   staticBlob[0] := 'W'; staticBlob[1] := 'X';
   staticBlob[2] := 'Y'; staticBlob[3] := 'Z';
   blobIn[0] := 'A'; blobIn[1] := 'B';
   blobIn[2] := 'C'; blobIn[3] := 'D';

   (* 1. library info *)
   section(1, "library info");
   LibVersionStr(buf);
   printf("version %s number %d\n", buf, sqlite3_libversion_number());
   IF sqlite3_libversion_number() <= 0 THEN HALT(1) END;
   CStrToM2(sqlite3_libversion(), buf);
   CStrToM2(sqlite3_sourceid(), buf2);
   printf("sourceid %s\n", buf2);
   printf("threadsafe %d\n", sqlite3_threadsafe());
   ErrStr(SQLiteOk, buf);
   printf("errstr ok %s\n", buf);
   CStrToM2(sqlite3_errstr(SQLiteOk), buf);
   printf("raw errstr %s\n", buf);
   IF sqlite3_complete("SELECT 1;") = 0 THEN
      printf("FAIL complete full\n"); HALT(1)
   END;
   IF sqlite3_complete("SELECT") # 0 THEN
      printf("FAIL complete partial\n"); HALT(1)
   END;
   printf("complete ok\n");

   (* 2. open and connection settings *)
   section(2, "open and settings");
   check(sqlite3_open(":memory:", db), "open");
   printf("autocommit %d\n", sqlite3_get_autocommit(db));
   check(sqlite3_busy_timeout(db, 500), "busy timeout");
   check(sqlite3_extended_result_codes(db, 1), "ext codes");
   i := sqlite3_limit(db, SQLiteLimitVariableNumber, -1);
   printf("limit variables %d\n", i);
   IF i <= 0 THEN printf("FAIL limit\n"); HALT(1) END;
   check(sqlite3_open_v2(":memory:", db2,
                         SQLiteOpenReadWrite + SQLiteOpenCreate,
                         NIL), "open_v2");

   (* 3. exec, including raw errmsg capture and failure paths *)
   section(3, "exec and errors");
   check(ExecSimple(db, "CREATE TABLE demo(id INTEGER PRIMARY KEY,"
                        + " small INTEGER, big INTEGER,"
                        + " score REAL, label TEXT,"
                        + " payload BLOB, code TEXT);"),
         "create");
   check(ExecSimple(db, "INSERT INTO demo(small) VALUES(1);"), "seed");
   printf("changes %d total %d rowid %ld\n",
          sqlite3_changes(db), sqlite3_total_changes(db),
          sqlite3_last_insert_rowid(db));
   msg := NIL;
   rc := sqlite3_exec(db, "INSERT INTO demo(small) VALUES(2);",
                      NIL, NIL, ADR(msg));
   IF rc # SQLiteOk THEN fail("raw exec") END;
   IF msg # NIL THEN sqlite3_free(msg) END;
   rc := ExecWithErr(db, "INSERT INTO bogus(x) VALUES(1);", buf);
   printf("expected failure rc %d err %s\n", rc, buf);
   IF rc = SQLiteOk THEN printf("FAIL bad sql ok\n"); HALT(1) END;
   printf("errcode %d ext %d offset %d\n",
          sqlite3_errcode(db), sqlite3_extended_errcode(db),
          sqlite3_error_offset(db));
   CStrToM2(sqlite3_errmsg(db), buf);
   printf("raw errmsg %s\n", buf);

   (* 4. prepare variants, tail, sql text, parameters *)
   section(4, "prepare and parameters");
   tail := NIL;
   check(sqlite3_prepare_v2(db, "SELECT small FROM demo; SELECT 99;",
                            -1, stmt, ADR(tail)), "prep tail");
   IF tail = NIL THEN printf("FAIL tail nil\n"); HALT(1) END;
   CStrToM2(tail, buf);
   printf("tail %s\n", buf);
   CStrToM2(sqlite3_sql(stmt), buf);
   printf("sql %s\n", buf);
   check(sqlite3_finalize(stmt), "fin tail");
   check(sqlite3_prepare_v3(db, "SELECT :a, ?, :b;",
                            -1, SQLitePreparePersistent,
                            stmt, NIL), "prep v3");
   printf("param count %d\n", sqlite3_bind_parameter_count(stmt));
   IF sqlite3_bind_parameter_count(stmt) # 3 THEN HALT(1) END;
   CStrToM2(sqlite3_bind_parameter_name(stmt, 1), buf);
   printf("param1 %s\n", buf);
   IF sqlite3_bind_parameter_name(stmt, 2) # NIL THEN
      printf("FAIL anon param named\n"); HALT(1)
   END;
   i := sqlite3_bind_parameter_index(stmt, ":b");
   printf("param :b index %d\n", i);
   IF i # 3 THEN HALT(1) END;
   ro := sqlite3_stmt_readonly(stmt);
   printf("readonly select %d\n", ro);
   IF ro = 0 THEN HALT(1) END;
   check(sqlite3_finalize(stmt), "fin v3");
   check(sqlite3_prepare_v2(db, "INSERT INTO demo(small) VALUES(1);",
                            -1, stmt, NIL), "prep ins");
   IF sqlite3_stmt_readonly(stmt) # 0 THEN
      printf("FAIL readonly insert\n"); HALT(1)
   END;
   check(sqlite3_finalize(stmt), "fin ins");

   (* 5. all bind kinds *)
   section(5, "binds and step");
   check(sqlite3_prepare_v2(db, "DELETE FROM demo;", -1, stmt, NIL),
         "prep delete");
   IF sqlite3_step(stmt) # SQLiteDone THEN HALT(1) END;
   check(sqlite3_finalize(stmt), "fin delete");
   check(sqlite3_prepare_v2(db, "INSERT INTO demo"
                                + "(small,big,score,label,payload,code)"
                                + " VALUES(?,?,?,?,?,?);",
                            -1, stmt, NIL), "prep full insert");
   check(sqlite3_bind_int(stmt, 1, 42), "bind int");
   check(sqlite3_bind_int64(stmt, 2, VAL(LONGINT, 9000000001)),
         "bind int64");
   check(sqlite3_bind_double(stmt, 3, 2.5), "bind double");
   check(BindTextCopy(stmt, 4, "hello"), "bind text copy");
   check(BindBlobCopy(stmt, 5, ADR(blobIn), 4), "bind blob copy");
   check(sqlite3_bind_null(stmt, 6), "bind null");
   IF sqlite3_step(stmt) # SQLiteDone THEN fail("step insert") END;
   printf("insert changes %d rowid %ld\n",
          sqlite3_changes(db), sqlite3_last_insert_rowid(db));
   xsql := sqlite3_expanded_sql(stmt);
   CStrToM2(xsql, buf);
   printf("expanded %s\n", buf);
   IF xsql # NIL THEN sqlite3_free(xsql) END;
   check(sqlite3_finalize(stmt), "fin full insert");

   (* 6. all column kinds, metadata, reuse, transactions *)
   section(6, "columns and reuse");
   check(sqlite3_prepare_v2(db, "SELECT small,big,score,label,"
                                + "payload,code FROM demo;",
                            -1, stmt, NIL), "prep select");
   rc := sqlite3_step(stmt);
   IF rc # SQLiteRow THEN fail("step row") END;
   ncols := sqlite3_column_count(stmt);
   ndata := sqlite3_data_count(stmt);
   printf("cols %d data %d\n", ncols, ndata);
   IF (ncols # 6) OR (ndata # 6) THEN HALT(1) END;
   busy := sqlite3_stmt_busy(stmt);
   printf("busy %d\n", busy);
   IF busy = 0 THEN HALT(1) END;
   i := sqlite3_column_int(stmt, 0);
   li := sqlite3_column_int64(stmt, 1);
   d := sqlite3_column_double(stmt, 2);
   CStrToM2(sqlite3_column_text(stmt, 3), buf);
   ncols := sqlite3_column_bytes(stmt, 3);
   p := sqlite3_column_blob(stmt, 4);
   tp := sqlite3_column_type(stmt, 4);
   printf("row %d %ld %f %s bytes %d blobtype %d\n",
          i, li, d, buf, ncols, tp);
   IF (i # 42) OR (li # VAL(LONGINT, 9000000001)) THEN HALT(1) END;
   IF ncols # 5 THEN HALT(1) END;
   IF (p = NIL) OR (sqlite3_column_bytes(stmt, 4) # 4) THEN HALT(1) END;
   strncpy(ADR(out), p, 4);
   out[4] := 0C;
   printf("blob %s\n", out);
   IF sqlite3_column_type(stmt, 5) # SQLiteNull THEN HALT(1) END;
   CStrToM2(sqlite3_column_name(stmt, 0), buf);
   CStrToM2(sqlite3_column_decltype(stmt, 0), buf2);
   printf("col0 %s decl %s\n", buf, buf2);
   showInt := SQLiteInteger; showFloat := SQLiteFloat;
   showText := SQLiteText; showBlob := SQLiteBlob; showNull := SQLiteNull;
   printf("type ids int %d float %d text %d blob %d null %d\n",
          showInt, showFloat, showText, showBlob, showNull);
   check(sqlite3_reset(stmt), "reset");
   IF sqlite3_stmt_busy(stmt) # 0 THEN
      printf("FAIL busy after reset\n"); HALT(1)
   END;
   check(sqlite3_clear_bindings(stmt), "clear");
   check(sqlite3_finalize(stmt), "fin select");

   (* statement reuse with raw transient and static destructors *)
   check(sqlite3_prepare_v2(db, "INSERT INTO demo(label,payload)"
                                + " VALUES(?,?);",
                            -1, stmt, NIL), "prep reuse");
   check(sqlite3_bind_text(stmt, 1, "raw", -1, TransientDestr()),
         "bind raw transient");
   check(sqlite3_bind_blob(stmt, 2, ADR(staticBlob), 4, StaticDestr()),
         "bind static");
   IF sqlite3_step(stmt) # SQLiteDone THEN HALT(1) END;
   check(sqlite3_reset(stmt), "reset reuse");
   check(sqlite3_clear_bindings(stmt), "clear reuse");
   check(BindTextCopy(stmt, 1, "second"), "rebind text");
   check(sqlite3_bind_zeroblob(stmt, 2, 16), "bind zeroblob");
   IF sqlite3_step(stmt) # SQLiteDone THEN HALT(1) END;
   check(sqlite3_finalize(stmt), "fin reuse");
   check(sqlite3_prepare_v2(db, "SELECT COUNT(*),"
                                + " SUM(LENGTH(payload)) FROM demo;",
                            -1, stmt, NIL), "prep agg");
   IF sqlite3_step(stmt) # SQLiteRow THEN HALT(1) END;
   printf("rows %d blobs %d\n",
          sqlite3_column_int(stmt, 0), sqlite3_column_int(stmt, 1));
   IF sqlite3_column_int(stmt, 0) # 3 THEN HALT(1) END;
   check(sqlite3_finalize(stmt), "fin agg");

   check(ExecSimple(db, "BEGIN;"), "begin");
   IF sqlite3_get_autocommit(db) # 0 THEN HALT(1) END;
   check(ExecSimple(db, "INSERT INTO demo(small) VALUES(7);"), "txn ins");
   check(ExecSimple(db, "COMMIT;"), "commit");
   IF sqlite3_get_autocommit(db) = 0 THEN HALT(1) END;
   printf("total changes %d\n", sqlite3_total_changes(db));

   (* 8. custom functions *)
   section(8, "custom functions");
   check(sqlite3_create_function(db, "m2tax", 1,
                                 SQLiteUtf8 + SQLiteDeterministic,
                                 NIL, tax, NIL, NIL), "reg tax");
   check(sqlite3_create_function(db, "m2total", 1, SQLiteUtf8, NIL,
                                 NIL, totalStep, totalFinal),
         "reg total");
   check(ExecSimple(db, "CREATE TABLE fx(price REAL);"), "fx create");
   check(ExecSimple(db, "INSERT INTO fx VALUES(10.0),(20.0),(30.0);"),
         "fx fill");
   check(sqlite3_prepare_v2(db, "SELECT m2tax(100.0), m2total(price)"
                                + " FROM fx;",
                            -1, stmt, NIL), "prep fx");
   rc := sqlite3_step(stmt);
   IF rc # SQLiteRow THEN fail("fx step") END;
   d := sqlite3_column_double(stmt, 0);
   li := sqlite3_column_int64(stmt, 1);
   printf("taxed %f totalled %ld\n", d, li);
   IF (d < 119.9) OR (d > 120.1) THEN fail("fx tax") END;
   IF li # 60 THEN fail("fx total") END;
   check(sqlite3_finalize(stmt), "fin fx");

   (* 10. blob I/O and backup *)
   section(10, "blob and backup");
   check(ExecSimple(db, "CREATE TABLE raw(data BLOB);"), "raw create");
   check(ExecSimple(db, "INSERT INTO raw VALUES(zeroblob(64));"),
         "raw seed");
   check(sqlite3_blob_open(db, "main", "raw", "data",
                           sqlite3_last_insert_rowid(db), 1, bh),
         "raw open");
   printf("blob size %d\n", sqlite3_blob_bytes(bh));
   IF sqlite3_blob_bytes(bh) # 64 THEN fail("raw size") END;
   FOR i := 0 TO 15 DO
      wpat[i] := CHR(ORD('0') + VAL(CARDINAL, i MOD 10))
   END;
   check(sqlite3_blob_write(bh, ADR(wpat), 16, 0), "raw write");
   check(sqlite3_blob_read(bh, ADR(rback), 16, 0), "raw read");
   FOR i := 0 TO 15 DO
      IF rback[i] # wpat[i] THEN fail("raw roundtrip") END
   END;
   printf("blob ok\n");
   check(sqlite3_blob_close(bh), "raw close");
   check(BackupToFile(db, "/tmp/m2sqlite_showcase_backup.db"),
         "show backup");
   check(sqlite3_open("/tmp/m2sqlite_showcase_backup.db", db3),
         "open backup");
   check(sqlite3_prepare_v2(db3, "SELECT COUNT(*) FROM raw;",
                            -1, stmt, NIL), "prep backup");
   IF sqlite3_step(stmt) # SQLiteRow THEN fail("step backup") END;
   printf("backup rows %d\n", sqlite3_column_int(stmt, 0));
   IF sqlite3_column_int(stmt, 0) # 1 THEN fail("backup count") END;
   check(sqlite3_finalize(stmt), "fin backup");
   check(sqlite3_close(db3), "close backup");
   RemoveFile("/tmp/m2sqlite_showcase_backup.db");

   (* 11. codes and tables *)
   section(11, "codes and tables");
   check(ExecSimple(db, "CREATE TABLE uniq(v TEXT UNIQUE);"),
         "uniq create");
   check(ExecSimple(db, "INSERT INTO uniq VALUES('a');"), "uniq seed");
   rc := ExecSimple(db, "INSERT INTO uniq VALUES('a');");
   printf("unique rc %d\n", rc);
   IF rc # SQLiteConstraintUnique THEN fail("uniq code") END;
   check(GetTable(db, "SELECT price FROM fx ORDER BY price;",
                  tbl, trows, tcols, buf), "fx table");
   TableCell(tbl, tcols, -1, 0, buf);
   printf("table %dx%d head %s\n", trows, tcols, buf);
   FOR i := 0 TO trows - 1 DO
      TableCell(tbl, tcols, i, 0, buf2);
      printf("cell %s\n", buf2)
   END;
   FreeTable(tbl);

   (* 12. close paths *)
   section(12, "close");
   check(sqlite3_close_v2(db2), "close_v2");
   check(sqlite3_close(db), "close");
   printf("PASS showcase_all\n")
END showcase_all.