sqlite: schema version, & fail run on writes fail

Spine now declares sqlite_db_schema_version and stamps it into the
database as PRAGMA user_version when the tables are created, in
both the shared and the discrete DDL blocks. On opening an
existing database it compares, and says once per run which version
it found and which it writes.

Failures are now tallied (shared, the output can run in parallel),
reported one line each on stderr naming the operation, and main
exits 1 without printing "run complete, ok".

Two tests under test/, both taking the spine binary as their first
argument and building their own database from data/pod unless
$SpinePOD says otherwise:
  test-search-db-schema.sh  names: every column the search form
    uses exists, and spine's declaration, the database's stamp
    and the search form's expectation all agree
  test-search-cgi.sh  behaviour: the real search binary answers
    real requests against a fresh database, no web server
    involved, the probe values read out of whichever database it
    is given

(assisted by Claude-Code)

1 files changed, 82 insertions, 39 deletions

diff --git a/org/out_sqlite.org b/org/out_sqlite.org
index e4499ce..275931b 100644
--- a/org/out_sqlite.org
+++ b/org/out_sqlite.org
@@ -48,17 +48,18 @@ sudo chown ralph:ralph /var/www
 module sisudoc.outputs.io_out.sqlite;
 <<sqlite_imports>>
 <<sqlite_common>>
-<<sqlite_collection>>
-<<sqlite_discrete>>
-<<sqlite_run>>
-<<sqlite_munge>>
-<<sqlite_create>>
-<<sqlite_delete>>
-<<sqlite_insert_metadata>>
-<<sqlite_insert_metadata_topics>>
-<<sqlite_insert_doc_objects_loop>>
-<<sqlite_tables_create>>
-<<sqlite_tables_drop>>
+<<sqlite_schema>>
+<<template_sqlite_collection>>
+<<template_sqlite_discrete>>
+<<template_sqlite_run>>
+<<templates_sqlite_munge>>
+<<template_sqlite_create>>
+<<template_sqlite_delete>>
+<<template_sqlite_insert_metadata>>
+<<template_sqlite_insert_metadata_topics>>
+<<template_sqlite_insert_doc_objects_loop>>
+<<template_sqlite_tables_create>>
+<<template_sqlite_tables_drop>>
 #+END_SRC
 
 *** hub
@@ -75,9 +76,59 @@ static auto mkup = InlineMarkup();
 long _metadata_tid_lastrowid;
 #+END_SRC
 
+**** sqlite schema
+
+#+NAME: sqlite_schema
+#+HEADER: :noweb yes
+#+BEGIN_SRC d
+/+ ↓ sqlite db schema version, stamped into the db as PRAGMA user_version +/
+/+   bump whenever the table or index definitions in this module change, and +/
+/+   bump the matching expectation in sisudoc-spine-search-cgi +/
+enum sqlite_db_schema_version = 1;
+/+ ↓ tally of failed sqlite statements, shared: sqlite output may run in parallel +/
+private shared int _sqlite_failure_tally = 0;
+/+ ↓ schema version mismatch is reported once per run, not once per document +/
+private shared int _sqlite_schema_mismatch_reported = 0;
+/+ ↓ record a failed sqlite statement, reported on stderr, tallied for exit status +/
+void sqliteFailureRecord(string note, string msg) {
+  import core.atomic : atomicOp;
+  atomicOp!"+="(_sqlite_failure_tally, 1);
+  stderr.writeln("ERROR SQLite (", note, "): ", msg);
+}
+/+ ↓ count of failed sqlite statements this run, zero when all went well +/
+int sqliteFailureTally() {
+  import core.atomic : atomicLoad;
+  return atomicLoad(_sqlite_failure_tally);
+}
+/+ ↓ compare the schema version stamped in an existing db with this spine's +/
+void sqliteSchemaVersionCheck(Db)(Db db, string sqlite_file) {
+  import core.atomic : atomicOp;
+  int _found;
+  try {
+    _found = db.execute("PRAGMA user_version;").oneValue!int;
+  } catch (Exception) {
+    return;
+  }
+  if (_found == sqlite_db_schema_version
+  || _found == 0) { /+ ↓ zero: db predates the stamp, or has no tables yet +/
+    return;
+  }
+  if (atomicOp!"+="(_sqlite_schema_mismatch_reported, 1) == 1) {
+    stderr.writeln("ERROR SQLite : db schema version mismatch: ", sqlite_file);
+    stderr.writeln("       db holds schema version ", _found,
+      ", this spine writes schema version ", sqlite_db_schema_version);
+    stderr.writeln("       recreate the db, e.g.: spine --sqlite-db-recreate",
+      " --sqlite-db-path=... --sqlite-db-filename=...");
+    sqliteFailureRecord("schema version",
+      "db schema version " ~ _found.to!string
+      ~ " != spine schema version " ~ sqlite_db_schema_version.to!string);
+  }
+}
+#+END_SRC
+
 **** collection
 
-#+NAME: sqlite_collection
+#+NAME: template_sqlite_collection
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteHubBuildTablesAndPopulate() {
@@ -107,6 +158,7 @@ template SQLiteHubBuildTablesAndPopulate() {
     }
     try {
       auto db = Database(pth_sqlite.sqlite_file);
+      sqliteSchemaVersionCheck(db, pth_sqlite.sqlite_file);
       SQLiteDbStatementComposite!()(db, doc);
     }
     catch (FileException e) {
@@ -138,7 +190,7 @@ template SQLiteHubBuildTablesAndPopulate() {
 
 **** discrete
 
-#+NAME: sqlite_discrete
+#+NAME: template_sqlite_discrete
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteHubDiscreteBuildTablesAndPopulate() {
@@ -157,6 +209,7 @@ template SQLiteHubDiscreteBuildTablesAndPopulate() {
       }
     }
     auto db = Database(pth_sqlite.sqlite_file(doc.matters.src.filename));
+    sqliteSchemaVersionCheck(db, pth_sqlite.sqlite_file(doc.matters.src.filename));
     template SQLiteDiscreteDbStatementComposite() {
       void SQLiteDiscreteDbStatementComposite(Db,D)(
         Db   db,
@@ -199,7 +252,7 @@ template SQLiteHubDiscreteBuildTablesAndPopulate() {
 
 *** db run
 
-#+NAME: sqlite_run
+#+NAME: template_sqlite_run
 #+BEGIN_SRC d
 template SQLiteDbRun() {
   void SQLiteDbRun(Db,St,O)(
@@ -218,9 +271,9 @@ template SQLiteDbRun() {
         "\nCOMMIT TRANSACTION;\n"
       );
     } catch (ErrnoException ex) {
-      writeln("ERROR SQLite : ", ex);
+      sqliteFailureRecord(note, ex.msg);
     } catch (Exception ex) {
-      writeln("ERROR SQLite : ", ex);
+      sqliteFailureRecord(note, ex.msg);
     }
     { /+ debug +/
       if (opt_action.debug_do_sqlite) {
@@ -236,7 +289,7 @@ template SQLiteDbRun() {
 
 *** munge
 
-#+NAME: sqlite_munge
+#+NAME: templates_sqlite_munge
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLinsertDelimiter() {
@@ -288,7 +341,7 @@ template SQLiteFormatAndLoadObject() {
 *** sqlite instructions
 **** create
 
-#+NAME: sqlite_create
+#+NAME: template_sqlite_create
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteTablesReCreate() {
@@ -301,7 +354,10 @@ template SQLiteTablesReCreate() {
       <<sqlite_statement_create_table_topic_register>>
       <<sqlite_statement_create_table_objects>>
       <<sqlite_statement_create_index>>
-    ┃",);
+      PRAGMA user_version = %s;
+    ┃",
+      sqlite_db_schema_version,
+    );
     return _sql_instruct;
   }
 }
@@ -309,7 +365,7 @@ template SQLiteTablesReCreate() {
 
 **** delete
 
-#+NAME: sqlite_delete
+#+NAME: template_sqlite_delete
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteDeleteDocument() {
@@ -324,7 +380,7 @@ template SQLiteDeleteDocument() {
 
 **** insert metadata
 
-#+NAME: sqlite_insert_metadata
+#+NAME: template_sqlite_insert_metadata
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteInsertMetadata() {
@@ -340,7 +396,7 @@ template SQLiteInsertMetadata() {
 
 **** insert metadata topics
 
-#+NAME: sqlite_insert_metadata_topics
+#+NAME: template_sqlite_insert_metadata_topics
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteInsertMetadataTopics() {
@@ -358,7 +414,7 @@ template SQLiteInsertMetadataTopics() {
 
 **** insert doc objects loop
 
-#+NAME: sqlite_insert_doc_objects_loop
+#+NAME: template_sqlite_insert_doc_objects_loop
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteInsertDocObjectsLoop() {
@@ -379,26 +435,13 @@ template SQLiteInsertDocObjectsLoop() {
 
 **** tables create
 
-#+NAME: sqlite_tables_create
+#+NAME: template_sqlite_tables_create
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteTablesCreate() {
   void SQLiteTablesCreate(E,O,C)(E env, O opt_action, C config) {
     import d2sqlite3;
-    template SQLiteTablesReCreate() {
-      string SQLiteTablesReCreate()() {
-        string _sql_instruct;
-        _sql_instruct = format(q"┃
-          <<sqlite_statement_drop_existing_index>>
-          <<sqlite_statement_drop_existing_tables>>
-          <<sqlite_statement_create_table_metadata_and_src_txt>>
-          <<sqlite_statement_create_table_topic_register>>
-          <<sqlite_statement_create_table_objects>>
-          <<sqlite_statement_create_index>>
-        ┃",);
-        return _sql_instruct;
-      }
-    }
+    <<template_sqlite_create>>
     try {
       <<sqlite_db_create>>
     }
@@ -481,7 +524,7 @@ if (opt_action.sqlite_db_create) {
 
 **** tables drop
 
-#+NAME: sqlite_tables_drop
+#+NAME: template_sqlite_tables_drop
 #+BEGIN_SRC d
 template SQLiteDbDrop() {
   void SQLiteDbDrop(O,C)(O opt_action, C config) {