ocda db: the schema gains doc_id

each file still holds one language, here groundwork for one database per
document. The shape changes here and nothing merges yet: a database is
still written per language, so every doc_id is 1. Real and testable on
its own, where the writer and the reader together are not.

documents, a row per language, is what lets one file hold a document's
whole set. Everything that tells one language's rows from another's keys
on documents.id.

metadata is keyed on (doc_id, key) (no longer on key alone). Every
language has a title and a creator, and a key-only primary key refuses
the second one. schema.name and schema.version describe the file rather
than a document in it, so they are written with a null doc_id and are
the only rows that are.

objects gain doc_id and its uniqueness widens from (section, seq) to
(doc_id, section, seq). ('body', 0) exists once per language, so the
narrow constraint was the thing that would have refused a second
language outright. idx_objects_section leads with doc_id, or reading one
language scans them all.

objects.id stays a global INTEGER PRIMARY KEY, so object_images,
object_links, object_anchors and object_subtoc keep their schema and
their keys, and objects_fts keeps content_rowid='id'. The reader's four
sweeps filter through objects rather than gaining a column of their own.

outline and citable name the language and order by it first. A view over
a file that may hold several languages and does not say which reads as
one document and is several.

The DDL is IF NOT EXISTS throughout, since a second language will open a
file that already has its schema.

dbReadFile takes an optional language and means "the only document in
it" without one. Given none where there are several it reports the
languages and stops, rather than returning the first: the round trip
compares byte for byte, and a quietly wrong answer there would read as a
spine fault.

(assisted by Claude-Code)

1 files changed, 35 insertions, 8 deletions

diff --git a/org/in_abstraction_artefacts.org b/org/in_abstraction_artefacts.org
index 9da6d32..c2b4c7a 100644
--- a/org/in_abstraction_artefacts.org
+++ b/org/in_abstraction_artefacts.org
@@ -659,16 +659,41 @@ template spineAbstractionDbRead() {
     default: return "";
     }
   }
-  @trusted SSPdocument dbReadFile(string db_file) {
+  @trusted SSPdocument dbReadFile(string db_file, string _lang = "") {
     SSPdocument doc;
     if (!db_file.exists) {
       writeln("ERROR: no such file: ", db_file);
       return doc;
     }
     auto db = Database(db_file, SQLITE_OPEN_READONLY);
+    /+ ↓ which document in the file. A file holds one per language; with
+         no language named, take the only one, and say so if there is a
+         choice to be made rather than picking for the caller.
+    +/
+    long _doc_id = -1;
+    {
+      string[] _langs;
+      foreach (row; db.execute("SELECT id, lang FROM documents ORDER BY id")) {
+        string _l = row["lang"].as!string;
+        _langs ~= _l;
+        if (_lang.length > 0 && _l == _lang) { _doc_id = row["id"].as!long; }
+        else if (_lang.length == 0 && _doc_id < 0) { _doc_id = row["id"].as!long; }
+      }
+      if (_doc_id < 0) {
+        writeln("ERROR: ", db_file, " holds no document in '", _lang,
+          "'; it has: ", _langs.join(" "));
+        return doc;
+      }
+      if (_lang.length == 0 && _langs.length > 1) {
+        writeln("ERROR: ", db_file, " holds ", _langs.length,
+          " languages and none was named: ", _langs.join(" "));
+        return doc;
+      }
+    }
     /+ ↓ the four header blocks, from the metadata table. the key prefix
          says which block a row belongs to, and insertion order is kept +/
-    foreach (row; db.execute("SELECT key, value FROM metadata ORDER BY rowid")) {
+    foreach (row; db.execute("SELECT key, value FROM metadata WHERE doc_id = " ~ _doc_id.to!string
+      ~ " OR doc_id IS NULL ORDER BY rowid")) {
       string _k = row["key"].as!string;
       string _v = row["value"].as!string;
       if (_k.startsWith("make.")) {
@@ -717,7 +742,7 @@ template spineAbstractionDbRead() {
     string[][long]                  _subtoc_by_id;
     foreach (r; db.execute(
       "SELECT object_id, name, bytes, sha256, width, height, missing"
-      ~ " FROM object_images ORDER BY object_id, seq")
+      ~ " FROM object_images WHERE object_id IN (SELECT id FROM objects WHERE doc_id = " ~ _doc_id.to!string ~ ") ORDER BY object_id, seq")
     ) {
       ST_file_name_hash_size_ _img;
       _img.fileName        = r["name"].as!string;
@@ -729,18 +754,19 @@ template spineAbstractionDbRead() {
       _images_by_id[r["object_id"].as!long] ~= _img;
     }
     foreach (r; db.execute(
-      "SELECT object_id, url FROM object_links ORDER BY object_id, seq")
+      "SELECT object_id, url FROM object_links WHERE object_id IN (SELECT id FROM objects WHERE doc_id = " ~ _doc_id.to!string ~ ") ORDER BY object_id, seq")
     ) { _links_by_id[r["object_id"].as!long] ~= r["url"].as!string; }
     foreach (r; db.execute(
-      "SELECT object_id, anchor FROM object_anchors ORDER BY object_id, seq")
+      "SELECT object_id, anchor FROM object_anchors WHERE object_id IN (SELECT id FROM objects WHERE doc_id = " ~ _doc_id.to!string ~ ") ORDER BY object_id, seq")
     ) { _anchors_by_id[r["object_id"].as!long] ~= r["anchor"].as!string; }
     foreach (r; db.execute(
-      "SELECT object_id, entry FROM object_subtoc ORDER BY object_id, seq")
+      "SELECT object_id, entry FROM object_subtoc WHERE object_id IN (SELECT id FROM objects WHERE doc_id = " ~ _doc_id.to!string ~ ") ORDER BY object_id, seq")
     ) { _subtoc_by_id[r["object_id"].as!long] ~= r["entry"].as!string; }
     /+ ↓ the objects, section by section, in the order they were written +/
     string[] _sections;
     foreach (row; db.execute(
-      "SELECT section FROM objects GROUP BY section ORDER BY MIN(id)")
+      "SELECT section FROM objects WHERE doc_id = " ~ _doc_id.to!string
+      ~ " GROUP BY section ORDER BY MIN(id)")
     ) {
       _sections ~= row["section"].as!string;
     }
@@ -758,7 +784,8 @@ template spineAbstractionDbRead() {
       +/
       int[string] _col;
       foreach (row; db.execute(
-        "SELECT * FROM objects WHERE section = '" ~ section ~ "' ORDER BY seq")
+        "SELECT * FROM objects WHERE doc_id = " ~ _doc_id.to!string
+        ~ " AND section = '" ~ section ~ "' ORDER BY seq")
       ) {
         if (_col.length == 0) {
           foreach (_i; 0 .. row.length) { _col[row.columnName(_i)] = _i.to!int; }