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)

2 files changed, 146 insertions, 40 deletions

diff --git a/src/sisudoc/ocda/abstraction/db_in.d b/src/sisudoc/ocda/abstraction/db_in.d
index 6e90820..a8a216c 100644
--- a/src/sisudoc/ocda/abstraction/db_in.d
+++ b/src/sisudoc/ocda/abstraction/db_in.d
@@ -129,16 +129,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.")) {
@@ -187,7 +212,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;
@@ -199,18 +224,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;
     }
@@ -228,7 +254,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; }
diff --git a/src/sisudoc/outputs/io_out/sqlite_ocda_db.d b/src/sisudoc/outputs/io_out/sqlite_ocda_db.d
index 79be05e..d94fa26 100644
--- a/src/sisudoc/outputs/io_out/sqlite_ocda_db.d
+++ b/src/sisudoc/outputs/io_out/sqlite_ocda_db.d
@@ -116,9 +116,29 @@ template spineAbstractionDb() {
     db.run("PRAGMA synchronous=OFF");
 
     db.run("
-      CREATE TABLE metadata (
-        key   TEXT PRIMARY KEY,
-        value TEXT NOT NULL
+      -- documents: a row per language of the document this database holds.
+      -- One today, since a database is written per language; the table is
+      -- what lets one hold a document's whole set, which is what it is for.
+      -- Everything a reader needs to tell one language's rows from another's
+      -- keys on documents.id, called doc_id wherever it is referred to.
+      CREATE TABLE IF NOT EXISTS documents (
+        id              INTEGER PRIMARY KEY,
+        lang            TEXT NOT NULL,
+        source_filename TEXT,
+        ssp_digest      TEXT,
+        UNIQUE(lang)
+      );
+      -- metadata: the document's own header, a row per property.
+      -- Keyed on (doc_id, key) and not 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 doc_id null and are the
+      -- only rows that are.
+      CREATE TABLE IF NOT EXISTS metadata (
+        doc_id INTEGER REFERENCES documents(id),
+        key    TEXT NOT NULL,
+        value  TEXT NOT NULL,
+        UNIQUE(doc_id, key)
       );
       -- objects: one row per abstraction object, in document order.
       -- the fixed width arrays (ancestors, dom status, children, heading
@@ -126,8 +146,9 @@ template spineAbstractionDb() {
       -- json_extract, json_each and json_array_length can reach inside them;
       -- the open ended lists (images, links, anchor tags, subtoc) are rows
       -- in their own tables, not columns
-      CREATE TABLE objects (
+      CREATE TABLE IF NOT EXISTS objects (
         id                    INTEGER PRIMARY KEY,
+        doc_id                INTEGER NOT NULL REFERENCES documents(id),
         section               TEXT NOT NULL,
         seq                   INTEGER NOT NULL,
         ocn                   INTEGER DEFAULT 0,
@@ -185,14 +206,14 @@ template spineAbstractionDb() {
         table_header          INTEGER,
         code_linenumbers      INTEGER DEFAULT 0,
         text                  TEXT,
-        UNIQUE(section, seq)
+        UNIQUE(doc_id, section, seq)
       );
 
-      CREATE INDEX idx_objects_section ON objects(section);
-      CREATE INDEX idx_objects_ocn ON objects(ocn);
-      CREATE INDEX idx_objects_parent ON objects(parent_ocn);
-      CREATE INDEX idx_objects_is_a ON objects(is_a);
-      CREATE INDEX idx_objects_heading ON objects(heading_level)
+      CREATE INDEX IF NOT EXISTS idx_objects_section ON objects(doc_id, section);
+      CREATE INDEX IF NOT EXISTS idx_objects_ocn ON objects(ocn);
+      CREATE INDEX IF NOT EXISTS idx_objects_parent ON objects(parent_ocn);
+      CREATE INDEX IF NOT EXISTS idx_objects_is_a ON objects(is_a);
+      CREATE INDEX IF NOT EXISTS idx_objects_heading ON objects(heading_level)
         WHERE heading_level IS NOT NULL;
 
       -- files: what this database carries with it, so that a reader needs
@@ -200,7 +221,7 @@ template spineAbstractionDb() {
       -- output writers must have in order to produce a document. bytes are
       -- stored exactly as read, so sha256 matches the digest the abstraction
       -- was built with
-      CREATE TABLE files (
+      CREATE TABLE IF NOT EXISTS files (
         id      INTEGER PRIMARY KEY,
         role    TEXT NOT NULL,
         name    TEXT NOT NULL,
@@ -213,7 +234,7 @@ template spineAbstractionDb() {
       );
 
       -- the open ended per object lists, one row each, in order
-      CREATE TABLE object_images (
+      CREATE TABLE IF NOT EXISTS object_images (
         object_id INTEGER NOT NULL REFERENCES objects(id),
         seq       INTEGER NOT NULL,
         name      TEXT NOT NULL,
@@ -224,43 +245,52 @@ template spineAbstractionDb() {
         missing   INTEGER DEFAULT 0,
         PRIMARY KEY(object_id, seq)
       );
-      CREATE TABLE object_links (
+      CREATE TABLE IF NOT EXISTS object_links (
         object_id INTEGER NOT NULL REFERENCES objects(id),
         seq       INTEGER NOT NULL,
         url       TEXT NOT NULL,
         PRIMARY KEY(object_id, seq)
       );
-      CREATE TABLE object_anchors (
+      CREATE TABLE IF NOT EXISTS object_anchors (
         object_id INTEGER NOT NULL REFERENCES objects(id),
         seq       INTEGER NOT NULL,
         anchor    TEXT NOT NULL,
         PRIMARY KEY(object_id, seq)
       );
-      CREATE TABLE object_subtoc (
+      CREATE TABLE IF NOT EXISTS object_subtoc (
         object_id INTEGER NOT NULL REFERENCES objects(id),
         seq       INTEGER NOT NULL,
         entry     TEXT NOT NULL,
         PRIMARY KEY(object_id, seq)
       );
 
-      CREATE INDEX idx_files_role ON files(role);
-      CREATE INDEX idx_object_images_name ON object_images(name);
+      CREATE INDEX IF NOT EXISTS idx_files_role ON files(role);
+      CREATE INDEX IF NOT EXISTS idx_object_images_name ON object_images(name);
 
       -- full text over the object text, external content so the text is not
       -- stored twice. per document: this is for exploring one abstraction,
       -- the collection wide search database is a different thing
-      CREATE VIRTUAL TABLE objects_fts USING fts5(
+      CREATE VIRTUAL TABLE IF NOT EXISTS objects_fts USING fts5(
         text, content='objects', content_rowid='id'
       );
 
       -- the shapes worth asking for, named
-      CREATE VIEW outline AS
-        SELECT section, seq, ocn, heading_level, heading_lev_collapsed,
-               last_descendant_ocn, identifier, text
-          FROM objects WHERE is_a = 'heading' ORDER BY id;
-      CREATE VIEW citable AS
-        SELECT * FROM objects WHERE ocn > 0 ORDER BY ocn;
-      CREATE VIEW document_files AS
+      -- both name the language, and order by it first. A view over a file
+      -- that may hold several languages and does not say which would read
+      -- as one document and be several: the language is in the projection
+      -- so that what comes back says what it is, and a reader wanting one
+      -- language adds WHERE lang = '..' rather than knowing to.
+      CREATE VIEW IF NOT EXISTS outline AS
+        SELECT d.lang AS lang, o.doc_id, o.section, o.seq, o.ocn,
+               o.heading_level, o.heading_lev_collapsed,
+               o.last_descendant_ocn, o.identifier, o.text
+          FROM objects o JOIN documents d ON d.id = o.doc_id
+         WHERE o.is_a = 'heading' ORDER BY o.doc_id, o.id;
+      CREATE VIEW IF NOT EXISTS citable AS
+        SELECT d.lang AS lang, o.*
+          FROM objects o JOIN documents d ON d.id = o.doc_id
+         WHERE o.ocn > 0 ORDER BY o.doc_id, o.ocn;
+      CREATE VIEW IF NOT EXISTS document_files AS
         SELECT role, name, bytes, sha256, width, height FROM files ORDER BY role, name;
     ");
 
@@ -273,18 +303,51 @@ template spineAbstractionDb() {
     +/
     db.run("BEGIN TRANSACTION");
 
+    /+ ↓ the document this call is writing: one row in documents, and its id
+         is the doc_id every row of this language then carries. Written with
+         OR REPLACE on the language so that re-writing a language replaces
+         its row rather than refusing it.
+    +/
+    long _doc_id;
+    {
+      auto doc_stmt = db.prepare(
+        "INSERT OR REPLACE INTO documents (lang, source_filename, ssp_digest)"
+        ~ " VALUES (:lang, :fn, :dig)"
+      );
+      doc_stmt.bind(":lang", doc_matters.src.language);
+      doc_stmt.bind(":fn",   ssp_doc.source);
+      doc_stmt.bind(":dig",  ssp_doc.ssp_digest);
+      doc_stmt.execute();
+      doc_stmt.finalize();
+      _doc_id = db.lastInsertRowid;
+    }
+
     auto meta_stmt = db.prepare(
-      "INSERT INTO metadata (key, value) VALUES (:key, :value)"
+      "INSERT INTO metadata (doc_id, key, value) VALUES (:doc_id, :key, :value)"
     );
 
     void insertMeta(string key, string value) {
       if (value.length > 0) {
+        meta_stmt.bind(":doc_id", _doc_id);
         meta_stmt.bind(":key", key);
         meta_stmt.bind(":value", value);
         meta_stmt.execute();
         meta_stmt.reset();
       }
     }
+    /+ ↓ what the file is rather than what a document in it is, so these two
+         carry no doc_id and are the only rows that do not
+    +/
+    void insertFileMeta(string key, string value) {
+      auto _s = db.prepare(
+        "INSERT OR REPLACE INTO metadata (doc_id, key, value)"
+        ~ " VALUES (NULL, :key, :value)"
+      );
+      _s.bind(":key", key);
+      _s.bind(":value", value);
+      _s.execute();
+      _s.finalize();
+    }
     void insertBlock(string prefix, string[] keys_in_order, string[string] kv) {
       foreach (k; keys_in_order) {
         if (k in kv) { insertMeta(prefix ~ k, kv[k]); }
@@ -295,8 +358,8 @@ template spineAbstractionDb() {
          version is the .ssp's own: the two serialisations are one format and
          move together
     +/
-    insertMeta("schema.name", "sisu-abstraction-db");
-    insertMeta("schema.version", ssp_format_version);
+    insertFileMeta("schema.name", "sisu-abstraction-db");
+    insertFileMeta("schema.version", ssp_format_version);
     insertMeta("source.filename", ssp_doc.source);
     insertBlock("source.", ssp_doc.source_info_order, ssp_doc.source_info);
     /+ ↓ the digest of the .ssp this database was built from. a file cannot
@@ -315,7 +378,7 @@ template spineAbstractionDb() {
     /+ ↓ populate objects +/
     auto obj_stmt = db.prepare(
       "INSERT INTO objects ("
-      ~ "section, seq, ocn, is_a, is_of_part, is_of_section, is_of_type,"
+      ~ "doc_id, section, seq, ocn, is_a, is_of_part, is_of_section, is_of_type,"
       ~ "heading_level, heading_lev_collapsed, identifier,"
       ~ "parent_ocn, parent_lev, last_descendant_ocn,"
       ~ "children, ancestors, ancestors_collapsed,"
@@ -330,7 +393,7 @@ template spineAbstractionDb() {
       ~ "table_cols, table_widths, table_aligns, table_header,"
       ~ "code_linenumbers, text"
       ~ ") VALUES ("
-      ~ ":section, :seq, :ocn, :is_a, :is_of_part, :is_of_section, :is_of_type,"
+      ~ ":doc_id, :section, :seq, :ocn, :is_a, :is_of_part, :is_of_section, :is_of_type,"
       ~ ":heading_level, :heading_lev_collapsed, :identifier,"
       ~ ":parent_ocn, :parent_lev, :last_descendant_ocn,"
       ~ ":children, :ancestors, :ancestors_collapsed,"
@@ -375,6 +438,7 @@ template spineAbstractionDb() {
       if (section_objs.length == 0) continue;
 
       foreach (seq, obj; section_objs) {
+        obj_stmt.bind(":doc_id", _doc_id);
         obj_stmt.bind(":section", section);
         obj_stmt.bind(":seq", cast(int) seq);
         obj_stmt.bind(":ocn", obj.metainfo.ocn);
@@ -648,8 +712,23 @@ template spineAbstractionDb() {
     }
 
     /+ ↓ build the full text index from the rows just written +/
-    db.run("INSERT INTO objects_fts(rowid, text)"
-      ~ " SELECT id, text FROM objects WHERE text IS NOT NULL");
+    /+ ↓ scoped to this document, not the whole table.
+         (objects_fts is external content over objects.id, so re-running this
+         for a second language would re-insert every row the first language
+         put in: duplicate rowids, which fts5 does not refuse and which give
+         duplicated and wrong results. Nothing in the test suite queries the
+         index, so this one would not have been caught.)
+    +/
+    {
+      auto _fts = db.prepare(
+        "INSERT INTO objects_fts(rowid, text)"
+        ~ " SELECT id, text FROM objects"
+        ~ " WHERE doc_id = :doc_id AND text IS NOT NULL"
+      );
+      _fts.bind(":doc_id", _doc_id);
+      _fts.execute();
+      _fts.finalize();
+    }
 
     db.run("COMMIT TRANSACTION");
   }