diff options
| -rw-r--r-- | org/out_sqlite.org | 121 | ||||
| -rw-r--r-- | org/spine.org | 12 | ||||
| -rw-r--r-- | org/tests_for_document_abstraction_shell_scripts.org | 628 | ||||
| -rw-r--r-- | src/sisudoc/outputs/io_out/sqlite.d | 59 | ||||
| -rw-r--r-- | src/sisudoc/spine.d | 12 | ||||
| -rwxr-xr-x | test/test-search-cgi.sh | 245 | ||||
| -rwxr-xr-x | test/test-search-db-schema.sh | 178 |
7 files changed, 1118 insertions, 137 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) { diff --git a/org/spine.org b/org/spine.org index 51926d5..86003b9 100644 --- a/org/spine.org +++ b/org/spine.org @@ -124,6 +124,18 @@ 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); + } + } } #+END_SRC diff --git a/org/tests_for_document_abstraction_shell_scripts.org b/org/tests_for_document_abstraction_shell_scripts.org index 1c93eed..30771be 100644 --- a/org/tests_for_document_abstraction_shell_scripts.org +++ b/org/tests_for_document_abstraction_shell_scripts.org @@ -15,7 +15,7 @@ - magic single double-quote → " ← FIX changes hilighting behavior (occuring after it) in org document. INVESTIGATE (org-mode CONFIG?) FIND & FIX -* test shell scripts document abstraction +* document abstraction - ocda ** run tests #+HEADER: :tangle ../test/run-tests.sh @@ -120,7 +120,8 @@ else fi #+END_SRC -** ssp document abstraction test/test-abstraction-ssp.sh +** ssp ocda abstraction +*** document.ssp abstraction test/test-abstraction-ssp.sh #+HEADER: :tangle ../test/test-abstraction-ssp.sh #+HEADER: :tangle-mode (identity #o755) @@ -306,7 +307,98 @@ else fi #+END_SRC -** db document abstraction test/test-abstraction-db.sh +*** document.ssp abstraction test/test-abstraction-ssp-roundtrip.sh + +#+HEADER: :tangle ../test/test-abstraction-ssp-roundtrip.sh +#+HEADER: :tangle-mode (identity #o755) +#+HEADER: :shebang "#!/usr/bin/env sh" +#+BEGIN_SRC shell +# test-abstraction-ssp-roundtrip.sh +# +# Reads each reference .ssp back into the abstraction and emits it again, +# and requires the result to be byte identical to the file it read. +# +# What that proves: the reader (sisudoc.ocda.abstraction.ssp_in) restores +# every field the writer emits. The check is made against the writer itself, +# since both go through template sspObjectRecord, so there is no second +# description of the format that could drift from the first. +# +# It needs no sample documents and no output directory: the committed +# reference set is the input. Run test-abstraction-ssp.sh first if you want +# to know that the reference set is itself current. +# +# Usage: +# ./test/test-abstraction-ssp-roundtrip.sh # uses result/bin/spine +# ./test/test-abstraction-ssp-roundtrip.sh ./bin/spine-ldc # specify binary +# +# Exit codes: +# 0 every reference .ssp round trips byte identically +# 1 one or more do not (the first differences are printed) +# 2 set-up problem (no binary, no reference set) + +set -e + +SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)" +SPINE_DIR="$(cd "$SCRIPT_DIR/.." && pwd)" +REF_DIR="$SCRIPT_DIR/reference/abstraction" + +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 argument." >&2 + exit 2 + fi +fi + +if [ ! -d "$REF_DIR" ] || [ -z "$(ls "$REF_DIR"/*.ssp 2>/dev/null)" ]; then + echo "ERROR: no reference files found at $REF_DIR" >&2 + echo "Run ./test/test-abstraction-ssp.sh --generate first." >&2 + exit 2 +fi + +TMP="$SCRIPT_DIR/current-roundtrip" +rm -rf "$TMP" +mkdir -p "$TMP" + +echo "spine binary: $SPINE_BIN" +echo "reference: $REF_DIR" +echo "Reading each .ssp back and emitting it again ..." + +COUNT=0 +FAILURES=0 +for ref in "$REF_DIR"/*.ssp; do + base=$(basename "$ref") + COUNT=$((COUNT + 1)) + if ! $SPINE_BIN --ssp-round-trip="$ref" > "$TMP/$base" 2> "$TMP/$base.err"; then + echo "ERROR: $base (spine exited non-zero)" + head -3 "$TMP/$base.err" + FAILURES=$((FAILURES + 1)) + continue + fi + if ! cmp -s "$ref" "$TMP/$base"; then + echo "DIFFERS: $base ($(diff "$ref" "$TMP/$base" | grep -c '^[<>]') lines)" + diff --unified=1 "$ref" "$TMP/$base" | head -12 + echo " ..." + FAILURES=$((FAILURES + 1)) + fi +done + +rm -rf "$TMP" + +if [ "$FAILURES" -eq 0 ]; then + echo "PASS: all $COUNT .ssp files read back and re-emit byte identically" + exit 0 +else + echo "FAIL: $FAILURES of $COUNT do not round trip" + exit 1 +fi +#+END_SRC + +** db ocda abstraction +*** document db ocda test/test-abstraction-db.sh #+HEADER: :tangle ../test/test-abstraction-db.sh #+HEADER: :tangle-mode (identity #o755) @@ -545,97 +637,7 @@ else fi #+END_SRC * -** ssp document abstraction test/test-abstraction-ssp-roundtrip.sh - -#+HEADER: :tangle ../test/test-abstraction-ssp-roundtrip.sh -#+HEADER: :tangle-mode (identity #o755) -#+HEADER: :shebang "#!/usr/bin/env sh" -#+BEGIN_SRC shell -# test-abstraction-ssp-roundtrip.sh -# -# Reads each reference .ssp back into the abstraction and emits it again, -# and requires the result to be byte identical to the file it read. -# -# What that proves: the reader (sisudoc.ocda.abstraction.ssp_in) restores -# every field the writer emits. The check is made against the writer itself, -# since both go through template sspObjectRecord, so there is no second -# description of the format that could drift from the first. -# -# It needs no sample documents and no output directory: the committed -# reference set is the input. Run test-abstraction-ssp.sh first if you want -# to know that the reference set is itself current. -# -# Usage: -# ./test/test-abstraction-ssp-roundtrip.sh # uses result/bin/spine -# ./test/test-abstraction-ssp-roundtrip.sh ./bin/spine-ldc # specify binary -# -# Exit codes: -# 0 every reference .ssp round trips byte identically -# 1 one or more do not (the first differences are printed) -# 2 set-up problem (no binary, no reference set) - -set -e - -SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)" -SPINE_DIR="$(cd "$SCRIPT_DIR/.." && pwd)" -REF_DIR="$SCRIPT_DIR/reference/abstraction" - -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 argument." >&2 - exit 2 - fi -fi - -if [ ! -d "$REF_DIR" ] || [ -z "$(ls "$REF_DIR"/*.ssp 2>/dev/null)" ]; then - echo "ERROR: no reference files found at $REF_DIR" >&2 - echo "Run ./test/test-abstraction-ssp.sh --generate first." >&2 - exit 2 -fi - -TMP="$SCRIPT_DIR/current-roundtrip" -rm -rf "$TMP" -mkdir -p "$TMP" - -echo "spine binary: $SPINE_BIN" -echo "reference: $REF_DIR" -echo "Reading each .ssp back and emitting it again ..." - -COUNT=0 -FAILURES=0 -for ref in "$REF_DIR"/*.ssp; do - base=$(basename "$ref") - COUNT=$((COUNT + 1)) - if ! $SPINE_BIN --ssp-round-trip="$ref" > "$TMP/$base" 2> "$TMP/$base.err"; then - echo "ERROR: $base (spine exited non-zero)" - head -3 "$TMP/$base.err" - FAILURES=$((FAILURES + 1)) - continue - fi - if ! cmp -s "$ref" "$TMP/$base"; then - echo "DIFFERS: $base ($(diff "$ref" "$TMP/$base" | grep -c '^[<>]') lines)" - diff --unified=1 "$ref" "$TMP/$base" | head -12 - echo " ..." - FAILURES=$((FAILURES + 1)) - fi -done - -rm -rf "$TMP" - -if [ "$FAILURES" -eq 0 ]; then - echo "PASS: all $COUNT .ssp files read back and re-emit byte identically" - exit 0 -else - echo "FAIL: $FAILURES of $COUNT do not round trip" - exit 1 -fi -#+END_SRC - -** ssp document abstraction test-abstraction-db-roundtrip.sh +*** document db ocda test-abstraction-db-roundtrip.sh #+HEADER: :tangle ../test/test-abstraction-db-roundtrip.sh #+HEADER: :tangle-mode (identity #o755) @@ -740,3 +742,441 @@ else exit 1 fi #+END_SRC + +* collection search db + cgi form +** test search db schema + +#+HEADER: :tangle ../test/test-search-db-schema.sh +#+HEADER: :tangle-mode (identity #o755) +#+HEADER: :shebang "#!/usr/bin/env sh" +#+BEGIN_SRC shell +# test-search-db-schema.sh +# +# Schema contract between spine's --sqlite-update output and the search form +# in sisudoc-spine-search-cgi. +# +# Spine holds the only copy of the DDL; the search form holds no DDL at all, +# only a small set of column references. Nothing connects the two, so a +# column renamed in spine is found by whoever runs a search next. This is the +# check that finds it earlier: build a search database, then read the column +# references out of the search form's source and require every one of them to +# exist in what was built. +# +# It also requires the three-way agreement on schema version: the version +# spine declares, the version stamped into the database it built, and the +# version the search form expects. +# +# Usage: +# ./test/test-search-db-schema.sh [/path/to/spine] +# +# Environment: +# SpinePOD pod directory to build the db from, relative to the +# spine directory (default: data/pod, the bundled set) +# SpineSearchCGIsrc path to the search cgi source file or its repository +# (default: ../sisudoc-spine-search-cgi) +# +# sqlite3 is needed. If it is not on PATH the script re-runs itself inside a +# nix shell that has it. +# +# Exit codes: +# 0 spine's database satisfies every expectation the search form has +# 1 a mismatch was found (printed) +# 2 set-up problem (no binary, no cgi source, no sqlite3, no samples) + +set -e + +SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)" +SPINE_DIR="$(cd "$SCRIPT_DIR/.." && pwd)" + +if ! command -v sqlite3 > /dev/null 2>&1; then + if command -v nix > /dev/null 2>&1; then + echo "sqlite3 not on PATH, re-running inside nix shell \"nixpkgs#sqlite\"" + exec nix shell "nixpkgs#sqlite" -c "$0" "$@" + fi + echo "ERROR: sqlite3 not on PATH and nix not available." >&2 + exit 2 +fi + +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 argument." >&2 + exit 2 + fi +fi + +# the search form's source, the other half of the contract +CGI_SRC="${SpineSearchCGIsrc:-$SPINE_DIR/../sisudoc-spine-search-cgi}" +if [ -d "$CGI_SRC" ]; then + CGI_SRC="$CGI_SRC/src/sisudoc/spine_search.d" +fi +if [ ! -f "$CGI_SRC" ]; then + echo "ERROR: search cgi source not found at $CGI_SRC" >&2 + echo " set \$SpineSearchCGIsrc to the sisudoc-spine-search-cgi checkout" >&2 + exit 2 +fi + +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 + +OUT_DIR="$SCRIPT_DIR/current-search-db" +DB_DIR="$OUT_DIR/sqlite-database" +DB_NAME="spine.search.db" +DB="$DB_DIR/$DB_NAME" + +echo "spine binary: $SPINE_BIN" +echo "search cgi: $CGI_SRC" +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 +# the update has to be able to fail the test: spine exits non-zero when any +# sqlite statement failed, and that is exactly the case this test is here for +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 + +FAILURES=0 +fail() { echo " MISMATCH $*"; FAILURES=$((FAILURES + 1)); } + +# ── the database was built at all +if [ ! -f "$DB" ]; then + echo "FAIL: no database at $DB" + exit 1 +fi + +# ── the tables the search form joins +echo "Checking tables ..." +for t in metadata_and_text doc_objects topic_register; do + found=$(sqlite3 "$DB" \ + "select count(*) from sqlite_master where type='table' and name='$t';") + [ "$found" = "1" ] || fail "table $t missing" +done + +# ── the tables are populated, an empty db satisfies a column check trivially +docs=$(sqlite3 "$DB" "select count(*) from metadata_and_text;" 2>/dev/null || echo 0) +objs=$(sqlite3 "$DB" "select count(*) from doc_objects;" 2>/dev/null || echo 0) +echo " documents: $docs, objects: $objs" +[ "$docs" -gt 0 ] || fail "metadata_and_text is empty" +[ "$objs" -gt 0 ] || fail "doc_objects is empty" + +# ── schema version: spine's declaration, the stamp in the db, the search +# form's expectation; all three have to agree +echo "Checking schema version ..." +SPINE_SQL_SRC="$SPINE_DIR/src/sisudoc/outputs/io_out/sqlite.d" +declared=$(sed -n 's/^enum sqlite_db_schema_version *= *\([0-9][0-9]*\);.*/\1/p' \ + "$SPINE_SQL_SRC" | head -1) +stamped=$(sqlite3 "$DB" "pragma user_version;") +expected=$(sed -n 's/^enum sqlite_db_schema_version_expected *= *\([0-9][0-9]*\);.*/\1/p' \ + "$CGI_SRC" | head -1) +echo " spine declares: ${declared:-unset}, db stamped: ${stamped:-unset}, cgi expects: ${expected:-unset}" +if [ -z "$declared" ]; then + fail "spine declares no sqlite_db_schema_version in $SPINE_SQL_SRC" +elif [ "$stamped" != "$declared" ]; then + fail "db stamped user_version $stamped, spine declares $declared" +fi +if [ -z "$expected" ]; then + fail "search form declares no sqlite_db_schema_version_expected" +elif [ -n "$declared" ] && [ "$expected" != "$declared" ]; then + fail "search form expects schema version $expected, spine writes $declared" +fi + +# ── every column the search form names has to exist +# the references are qualified, table.column, which is what makes them +# findable without parsing D +echo "Checking columns named by the search form ..." +COLS=$(grep -o \ + 'metadata_and_text\.[a-z_]*\|doc_objects\.[a-z_]*\|topic_register\.[a-z_]*' \ + "$CGI_SRC" | sort -u) +if [ -z "$COLS" ]; then + fail "no column references found in $CGI_SRC (has the source changed shape?)" +fi +count=0 +for ref in $COLS; do + table=${ref%%.*} + column=${ref#*.} + [ -n "$column" ] || continue + count=$((count + 1)) + found=$(sqlite3 "$DB" \ + "select count(*) from pragma_table_info('$table') where name='$column';") + [ "$found" = "1" ] || fail "$table.$column named by the search form, absent from the db" +done +echo " column references checked: $count" + +echo "" +if [ "$FAILURES" -eq 0 ]; then + echo "PASS: spine's search database satisfies the search form's expectations" + exit 0 +else + echo "FAIL: $FAILURES mismatch(es)" + exit 1 +fi +#+END_SRC + +** test search cgi + +#+HEADER: :tangle ../test/test-search-cgi.sh +#+HEADER: :tangle-mode (identity #o755) +#+HEADER: :shebang "#!/usr/bin/env sh" +#+BEGIN_SRC shell +# 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 +#+END_SRC 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); + } + } } 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 diff --git a/test/test-search-db-schema.sh b/test/test-search-db-schema.sh new file mode 100755 index 0000000..8d28b76 --- /dev/null +++ b/test/test-search-db-schema.sh @@ -0,0 +1,178 @@ +#!/usr/bin/env sh +# test-search-db-schema.sh +# +# Schema contract between spine's --sqlite-update output and the search form +# in sisudoc-spine-search-cgi. +# +# Spine holds the only copy of the DDL; the search form holds no DDL at all, +# only a small set of column references. Nothing connects the two, so a +# column renamed in spine is found by whoever runs a search next. This is the +# check that finds it earlier: build a search database, then read the column +# references out of the search form's source and require every one of them to +# exist in what was built. +# +# It also requires the three-way agreement on schema version: the version +# spine declares, the version stamped into the database it built, and the +# version the search form expects. +# +# Usage: +# ./test/test-search-db-schema.sh [/path/to/spine] +# +# Environment: +# SpinePOD pod directory to build the db from, relative to the +# spine directory (default: data/pod, the bundled set) +# SpineSearchCGIsrc path to the search cgi source file or its repository +# (default: ../sisudoc-spine-search-cgi) +# +# sqlite3 is needed. If it is not on PATH the script re-runs itself inside a +# nix shell that has it. +# +# Exit codes: +# 0 spine's database satisfies every expectation the search form has +# 1 a mismatch was found (printed) +# 2 set-up problem (no binary, no cgi source, no sqlite3, no samples) + +set -e + +SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)" +SPINE_DIR="$(cd "$SCRIPT_DIR/.." && pwd)" + +if ! command -v sqlite3 > /dev/null 2>&1; then + if command -v nix > /dev/null 2>&1; then + echo "sqlite3 not on PATH, re-running inside nix shell \"nixpkgs#sqlite\"" + exec nix shell "nixpkgs#sqlite" -c "$0" "$@" + fi + echo "ERROR: sqlite3 not on PATH and nix not available." >&2 + exit 2 +fi + +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 argument." >&2 + exit 2 + fi +fi + +# the search form's source, the other half of the contract +CGI_SRC="${SpineSearchCGIsrc:-$SPINE_DIR/../sisudoc-spine-search-cgi}" +if [ -d "$CGI_SRC" ]; then + CGI_SRC="$CGI_SRC/src/sisudoc/spine_search.d" +fi +if [ ! -f "$CGI_SRC" ]; then + echo "ERROR: search cgi source not found at $CGI_SRC" >&2 + echo " set \$SpineSearchCGIsrc to the sisudoc-spine-search-cgi checkout" >&2 + exit 2 +fi + +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 + +OUT_DIR="$SCRIPT_DIR/current-search-db" +DB_DIR="$OUT_DIR/sqlite-database" +DB_NAME="spine.search.db" +DB="$DB_DIR/$DB_NAME" + +echo "spine binary: $SPINE_BIN" +echo "search cgi: $CGI_SRC" +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 +# the update has to be able to fail the test: spine exits non-zero when any +# sqlite statement failed, and that is exactly the case this test is here for +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 + +FAILURES=0 +fail() { echo " MISMATCH $*"; FAILURES=$((FAILURES + 1)); } + +# ── the database was built at all +if [ ! -f "$DB" ]; then + echo "FAIL: no database at $DB" + exit 1 +fi + +# ── the tables the search form joins +echo "Checking tables ..." +for t in metadata_and_text doc_objects topic_register; do + found=$(sqlite3 "$DB" \ + "select count(*) from sqlite_master where type='table' and name='$t';") + [ "$found" = "1" ] || fail "table $t missing" +done + +# ── the tables are populated, an empty db satisfies a column check trivially +docs=$(sqlite3 "$DB" "select count(*) from metadata_and_text;" 2>/dev/null || echo 0) +objs=$(sqlite3 "$DB" "select count(*) from doc_objects;" 2>/dev/null || echo 0) +echo " documents: $docs, objects: $objs" +[ "$docs" -gt 0 ] || fail "metadata_and_text is empty" +[ "$objs" -gt 0 ] || fail "doc_objects is empty" + +# ── schema version: spine's declaration, the stamp in the db, the search +# form's expectation; all three have to agree +echo "Checking schema version ..." +SPINE_SQL_SRC="$SPINE_DIR/src/sisudoc/outputs/io_out/sqlite.d" +declared=$(sed -n 's/^enum sqlite_db_schema_version *= *\([0-9][0-9]*\);.*/\1/p' \ + "$SPINE_SQL_SRC" | head -1) +stamped=$(sqlite3 "$DB" "pragma user_version;") +expected=$(sed -n 's/^enum sqlite_db_schema_version_expected *= *\([0-9][0-9]*\);.*/\1/p' \ + "$CGI_SRC" | head -1) +echo " spine declares: ${declared:-unset}, db stamped: ${stamped:-unset}, cgi expects: ${expected:-unset}" +if [ -z "$declared" ]; then + fail "spine declares no sqlite_db_schema_version in $SPINE_SQL_SRC" +elif [ "$stamped" != "$declared" ]; then + fail "db stamped user_version $stamped, spine declares $declared" +fi +if [ -z "$expected" ]; then + fail "search form declares no sqlite_db_schema_version_expected" +elif [ -n "$declared" ] && [ "$expected" != "$declared" ]; then + fail "search form expects schema version $expected, spine writes $declared" +fi + +# ── every column the search form names has to exist +# the references are qualified, table.column, which is what makes them +# findable without parsing D +echo "Checking columns named by the search form ..." +COLS=$(grep -o \ + 'metadata_and_text\.[a-z_]*\|doc_objects\.[a-z_]*\|topic_register\.[a-z_]*' \ + "$CGI_SRC" | sort -u) +if [ -z "$COLS" ]; then + fail "no column references found in $CGI_SRC (has the source changed shape?)" +fi +count=0 +for ref in $COLS; do + table=${ref%%.*} + column=${ref#*.} + [ -n "$column" ] || continue + count=$((count + 1)) + found=$(sqlite3 "$DB" \ + "select count(*) from pragma_table_info('$table') where name='$column';") + [ "$found" = "1" ] || fail "$table.$column named by the search form, absent from the db" +done +echo " column references checked: $count" + +echo "" +if [ "$FAILURES" -eq 0 ]; then + echo "PASS: spine's search database satisfies the search form's expectations" + exit 0 +else + echo "FAIL: $FAILURES mismatch(es)" + exit 1 +fi |