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, 245 insertions, 0 deletions

diff --git a/test/test-search-cgi.sh b/test/test-search-cgi.sh
new file mode 100755
index 0000000..7541a92
--- /dev/null
+++ b/test/test-search-cgi.sh
@@ -0,0 +1,245 @@
+#!/usr/bin/env sh
+# test-search-cgi.sh
+#
+# End to end check that the search cgi works against a database spine has
+# just built.
+#
+# The schema check (test-search-db-schema.sh) compares names. This one runs
+# the real binary against the real database, which is the only thing that
+# tests the query spine's schema actually has to satisfy: the joins, the
+# ordering, the types, and the fields the form fills in from a request.
+#
+# No web server is involved. The cgi binary is invoked as CGI, with the
+# request in the environment, which is what a web server would do.
+#
+# Usage:
+#   ./test/test-search-cgi.sh [/path/to/spine] [/path/to/spine_search]
+#
+# Environment:
+#   SpinePOD           pod directory to build the db from, relative to the
+#                      spine directory (default: data/pod, the bundled set)
+#   SpineSearchCGIbin  the built search cgi
+#                      (default: ../sisudoc-spine-search-cgi/result/cgi-bin/spine_search,
+#                      then .../cgi-bin/spine-search-ldc.cgi)
+#   SpineSearchDB      an existing search database to test against; when set,
+#                      nothing is built and this database is used as is
+#
+# The cgi is pointed at the database with SPINE_SEARCH_DB_PATH, so the binary
+# under test needs no rebuild and its compiled-in path is left alone.
+#
+# Exit codes:
+#   0  every request behaved
+#   1  a request did not (printed)
+#   2  set-up problem (no binary, no cgi, no samples)
+
+set -e
+
+SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
+SPINE_DIR="$(cd "$SCRIPT_DIR/.." && pwd)"
+
+SPINE_BIN="${1:-}"
+if [ -z "$SPINE_BIN" ]; then
+  if   [ -x "$SPINE_DIR/result/bin/spine" ]; then SPINE_BIN="$SPINE_DIR/result/bin/spine"
+  elif [ -x "$SPINE_DIR/bin/spine-ldc" ];    then SPINE_BIN="$SPINE_DIR/bin/spine-ldc"
+  elif [ -x "$SPINE_DIR/bin/spine" ];        then SPINE_BIN="$SPINE_DIR/bin/spine"
+  else
+    echo "ERROR: spine binary not found. Specify path as first argument." >&2
+    exit 2
+  fi
+fi
+
+CGI_BIN="${2:-${SpineSearchCGIbin:-}}"
+if [ -z "$CGI_BIN" ]; then
+  CGI_REPO="$SPINE_DIR/../sisudoc-spine-search-cgi"
+  for c in \
+    "$CGI_REPO/result/cgi-bin/spine_search" \
+    "$CGI_REPO/cgi-bin/spine-search-ldc.cgi" \
+    "$CGI_REPO/cgi-bin/spine_search"
+  do
+    [ -x "$c" ] && CGI_BIN="$c" && break
+  done
+fi
+if [ -z "$CGI_BIN" ] || [ ! -x "$CGI_BIN" ]; then
+  echo "ERROR: search cgi binary not found." >&2
+  echo "  build it: cd ../sisudoc-spine-search-cgi && nix build \".#spine-search-overlay-ldc\"" >&2
+  echo "  or set \$SpineSearchCGIbin / pass it as the second argument" >&2
+  exit 2
+fi
+
+OUT_DIR="$SCRIPT_DIR/current-search-cgi"
+DB_NAME="spine.search.db"
+
+if [ -n "$SpineSearchDB" ]; then
+  DB_DIR="$(cd "$(dirname "$SpineSearchDB")" && pwd)"
+  DB_NAME="$(basename "$SpineSearchDB")"
+  echo "spine binary: (not used, \$SpineSearchDB set)"
+else
+  SAMPLES_RAW="$SPINE_DIR/${SpinePOD:-data/pod}"
+  if [ -d "$SAMPLES_RAW" ]; then
+    SAMPLES_DIR="$(cd "$SAMPLES_RAW" && pwd)"
+  else
+    echo "ERROR: sample documents not found at $SAMPLES_RAW" >&2
+    exit 2
+  fi
+  DB_DIR="$OUT_DIR/sqlite-database"
+  echo "spine binary: $SPINE_BIN"
+  echo "samples:      $SAMPLES_DIR"
+  rm -rf "$OUT_DIR"
+  mkdir -p "$DB_DIR"
+  echo "Building search database ..."
+  "$SPINE_BIN" --sqlite-db-recreate \
+    --sqlite-db-path="$DB_DIR" --sqlite-db-filename="$DB_NAME" > /dev/null
+  if ! "$SPINE_BIN" --sqlite-update \
+    --sqlite-db-path="$DB_DIR" --sqlite-db-filename="$DB_NAME" \
+    --output="$OUT_DIR" "$SAMPLES_DIR"/* > "$OUT_DIR/build.log" 2>&1; then
+    echo "FAIL: spine --sqlite-update reported failure"
+    grep "ERROR SQLite\|run FAILED" "$OUT_DIR/build.log" | head -20
+    exit 1
+  fi
+fi
+echo "search cgi:   $CGI_BIN"
+echo "database:     $DB_DIR/$DB_NAME"
+
+mkdir -p "$OUT_DIR"
+RESP="$OUT_DIR/response.html"
+FAILURES=0
+
+# one GET request, as a web server would present it
+request() {
+  SPINE_SEARCH_DB_PATH="$DB_DIR" \
+  REQUEST_METHOD=GET \
+  SCRIPT_NAME=/spine_search \
+  SERVER_NAME=localhost \
+  SERVER_PORT=80 \
+  REQUEST_SCHEME=http \
+  GATEWAY_INTERFACE=CGI/1.1 \
+  SERVER_PROTOCOL=HTTP/1.1 \
+  QUERY_STRING="$1" \
+  "$CGI_BIN" > "$RESP" 2>"$OUT_DIR/response.err" || true
+}
+
+# a request that has to answer 200, name no sql error, and find documents
+expect_hits() {
+  _query="$1"
+  _what="$2"
+  request "$_query"
+  if ! head -1 "$RESP" | grep -q "200 OK"; then
+    echo "  FAIL $_what: no 200 status ($(head -1 "$RESP"))"
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  if grep -qi "SqliteException\|no such column\|no such table" "$RESP" "$OUT_DIR/response.err"; then
+    echo "  FAIL $_what: sql error"
+    grep -i "SqliteException\|no such column\|no such table" "$RESP" "$OUT_DIR/response.err" | head -3
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  _hits=$(grep -o 'class="publication"' "$RESP" | wc -l)
+  _hits=$((_hits / 2))
+  if [ "$_hits" -lt 1 ]; then
+    echo "  FAIL $_what: no results"
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  echo "  ok   $_what ($_hits document(s))"
+}
+
+# a request that has to answer 200 and say the named thing
+expect_says() {
+  _query="$1"
+  _text="$2"
+  _what="$3"
+  request "$_query"
+  if ! head -1 "$RESP" | grep -q "200 OK"; then
+    echo "  FAIL $_what: no 200 status ($(head -1 "$RESP"))"
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  if ! grep -q "$_text" "$RESP"; then
+    echo "  FAIL $_what: response does not say \"$_text\""
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  echo "  ok   $_what"
+}
+
+# the queries below name a different column of the schema each, so between
+# them they exercise the whole of what the search form selects on. The values
+# searched for are read out of the database itself where sqlite3 is present,
+# so that the test says something whichever corpus it is pointed at; the
+# fallbacks are words the bundled data/pod documents contain. Every field is
+# matched with LIKE '%value%', so a single safe word out of a longer value
+# still matches the row it came from.
+PROBE_TEXT="document"
+PROBE_AUTHOR="Amissah"
+PROBE_TITLE="markup"
+PROBE_FN="sisu-spine-markup"
+PROBE_UID="markup"
+PROBE_LNG="en"
+if command -v sqlite3 > /dev/null 2>&1; then
+  # first word of at least four letters or digits, nothing else: the values in
+  # the database carry punctuation, markup and newlines, none of which belong
+  # in a query string
+  _word() {
+    printf '%s' "$1" | tr -c 'A-Za-z0-9' ' ' \
+      | awk '{for (i = 1; i <= NF; i++) if (length($i) >= 4) { print $i; exit } }'
+  }
+  _ask() { sqlite3 "$DB_DIR/$DB_NAME" "$1" 2>/dev/null | head -1 || true; }
+  _v=$(_word "$(_ask "select clean from doc_objects where length(clean) > 60 limit 1;")")
+  [ -n "$_v" ] && PROBE_TEXT="$_v"
+  _v=$(_word "$(_ask "select creator_author from metadata_and_text where creator_author != '' limit 1;")")
+  [ -n "$_v" ] && PROBE_AUTHOR="$_v"
+  _v=$(_word "$(_ask "select title from metadata_and_text where title != '' limit 1;")")
+  [ -n "$_v" ] && PROBE_TITLE="$_v"
+  _v=$(_word "$(_ask "select src_filename_base from metadata_and_text limit 1;")")
+  [ -n "$_v" ] && PROBE_FN="$_v"
+  _v=$(_word "$(_ask "select uid from metadata_and_text limit 1;")")
+  [ -n "$_v" ] && PROBE_UID="$_v"
+  _v=$(_ask "select language_document_char from metadata_and_text limit 1;")
+  case "$_v" in [a-z][a-z]) PROBE_LNG="$_v" ;; esac
+fi
+echo "Requests (text \"$PROBE_TEXT\", author \"$PROBE_AUTHOR\", title \"$PROBE_TITLE\"):"
+expect_hits "sf=$PROBE_TEXT&rt=idx&sml=1000"          "text, index results"
+expect_hits "sf=$PROBE_TEXT&rt=txt&sml=1000"          "text, text results"
+expect_hits "sf=$PROBE_TEXT%20AND%20$PROBE_TEXT&rt=idx&sml=1000" "text, boolean AND"
+expect_hits "sf=$PROBE_TEXT%20OR%20zzzznotfound&rt=idx&sml=1000" "text, boolean OR"
+expect_hits "au=$PROBE_AUTHOR&sf=$PROBE_TEXT&rt=idx&sml=1000" "creator_author"
+expect_hits "ti=$PROBE_TITLE&sf=$PROBE_TEXT&rt=idx&sml=1000"  "title"
+expect_hits "lng=$PROBE_LNG&sf=$PROBE_TEXT&rt=idx&sml=1000"   "language_document_char"
+expect_hits "fn=$PROBE_FN&sf=$PROBE_TEXT&rt=idx&sml=1000"     "src_filename_base"
+expect_hits "uid=$PROBE_UID&sf=$PROBE_TEXT&rt=idx&sml=1000"   "uid"
+
+# the failure modes a database change would show up as, each of which used to
+# be a blank page, a 500, or a silently created empty file
+echo "Refusals:"
+expect_says 'sf=document&rt=idx&selected_db=no_such_file.db' \
+  "search database not found" "a database that is not there is named, not created"
+if [ -e "$DB_DIR/no_such_file.db" ]; then
+  echo "  FAIL a missing database was created by the request"
+  FAILURES=$((FAILURES + 1))
+fi
+expect_hits "sf=$PROBE_TEXT&rt=idx&sml=1000&selected_db=../../../../etc/passwd" \
+  "a path in selected_db falls back to the configured database"
+
+
+# the schema version gate, end to end: a database stamped with a version the
+# search form does not expect has to be named as such, not searched. Needs
+# sqlite3 to do the stamping, so it is skipped when sqlite3 is absent.
+if command -v sqlite3 > /dev/null 2>&1; then
+  echo "Schema version gate:"
+  cp "$DB_DIR/$DB_NAME" "$DB_DIR/wrong_version.db"
+  sqlite3 "$DB_DIR/wrong_version.db" "pragma user_version = 99;"
+  expect_says 'sf=document&rt=idx&selected_db=wrong_version.db' \
+    "schema version" "a database of another schema version is refused, with the version named"
+  rm -f "$DB_DIR/wrong_version.db"
+else
+  echo "Schema version gate: skipped, sqlite3 not on PATH"
+fi
+echo ""
+if [ "$FAILURES" -eq 0 ]; then
+  echo "PASS: the search cgi works against this database"
+  exit 0
+else
+  echo "FAIL: $FAILURES request(s) misbehaved"
+  exit 1
+fi