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)

2 files changed, 67 insertions, 4 deletions

diff --git a/src/sisudoc/outputs/io_out/sqlite.d b/src/sisudoc/outputs/io_out/sqlite.d
index f96846d..1e1970e 100644
--- a/src/sisudoc/outputs/io_out/sqlite.d
+++ b/src/sisudoc/outputs/io_out/sqlite.d
@@ -64,6 +64,49 @@ static auto rgx = RgxO();
 static auto rgx_xhtml = RgxXHTML();
 static auto mkup = InlineMarkup();
 long _metadata_tid_lastrowid;
+/+ ↓ 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);
+  }
+}
 template SQLiteHubBuildTablesAndPopulate() {
   void SQLiteHubBuildTablesAndPopulate(D)(D doc) {
     auto pth_sqlite = spinePathsSQLite!()(doc.matters.sqlite.filename, doc.matters.sqlite.path);
@@ -129,6 +172,7 @@ template SQLiteHubBuildTablesAndPopulate() {
     }
     try {
       auto db = Database(pth_sqlite.sqlite_file);
+      sqliteSchemaVersionCheck(db, pth_sqlite.sqlite_file);
       SQLiteDbStatementComposite!()(db, doc);
     }
     catch (FileException e) {
@@ -172,6 +216,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,
@@ -235,9 +280,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) {
@@ -1142,7 +1187,10 @@ template SQLiteTablesReCreate() {
       CREATE INDEX IF NOT EXISTS idx_language     ON metadata_and_text(language_document_char);
       CREATE INDEX IF NOT EXISTS idx_topics       ON metadata_and_text(classify_topic_register);
       CREATE INDEX IF NOT EXISTS idx_topic_list   ON topic_register(topic_register);
-    ┃",);
+      PRAGMA user_version = %s;
+    ┃",
+      sqlite_db_schema_version,
+    );
     return _sql_instruct;
   }
 }
@@ -1654,7 +1702,10 @@ template SQLiteTablesCreate() {
           CREATE INDEX IF NOT EXISTS idx_language     ON metadata_and_text(language_document_char);
           CREATE INDEX IF NOT EXISTS idx_topics       ON metadata_and_text(classify_topic_register);
           CREATE INDEX IF NOT EXISTS idx_topic_list   ON topic_register(topic_register);
-        ┃",);
+          PRAGMA user_version = %s;
+        ┃",
+          sqlite_db_schema_version,
+        );
         return _sql_instruct;
       }
     }
diff --git a/src/sisudoc/spine.d b/src/sisudoc/spine.d
index 568a102..2862303 100644
--- a/src/sisudoc/spine.d
+++ b/src/sisudoc/spine.d
@@ -1637,4 +1637,16 @@ string program_name = "spine";
   foreach (ref _dlr; _url_downloads) {
     cleanupDownload(_dlr);
   }
+  /+ ↓ sqlite statements that failed are fatal to the run, report and exit non-zero +/
+  {
+    import sisudoc.outputs.io_out.sqlite : sqliteFailureTally;
+    if (sqliteFailureTally() > 0) {
+      stderr.writefln(
+        "~ run FAILED ~ %s sqlite statement(s) failed, db not written as expected",
+        sqliteFailureTally(),
+      );
+      import core.stdc.stdlib : exit;
+      exit(1);
+    }
+  }
 }