ocda db: rows for repeatable fields & images inside

Four things in the abstraction are open ended lists that a single
row cannot hold, and which the database therefore could not
express at all: a paragraph's extracted links, its anchor tags, a
heading's lev4 subtoc entries, and the image records. Each now has
a table keyed on the object, ordered by seq:

  object_images, object_links, object_anchors, object_subtoc

And the images themselves are carried in the file, in a files
table with role, name, bytes, sha256, pixel size and the blob. The
point is self-sufficiency: html copies every image into its output
tree, epub and odt place them in their zips, and xelatex reads
them from disk, so an abstraction without the bytes cannot produce
a document, only its text and structure. The table is deliberately
generic rather than an images feature, so that the same mechanism
can later carry the source .sst or the pod's configuration by
adding a row with another role, and so that a reader can ask
whether a database carries its images (select count(*) from files
where role='image') and fall back to a directory beside it when
the answer is zero.

Bytes are stored exactly as read and the digest is taken over them.

Only images the document references are carried: the sisu manual's
media/image holds 10 files and 5 are in its image list, so 5 are
stored.

(assisted by Claude-Code)

1 files changed, 186 insertions, 0 deletions

diff --git a/src/sisudoc/outputs/io_out/create_abstraction_db.d b/src/sisudoc/outputs/io_out/create_abstraction_db.d
index d953472..a0cbd57 100644
--- a/src/sisudoc/outputs/io_out/create_abstraction_db.d
+++ b/src/sisudoc/outputs/io_out/create_abstraction_db.d
@@ -178,6 +178,57 @@ template spineAbstractionDb() {
       CREATE INDEX idx_objects_is_a ON objects(is_a);
       CREATE INDEX 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
+      -- nothing beside it. role says what a file is; images are the ones the
+      -- 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 (
+        id      INTEGER PRIMARY KEY,
+        role    TEXT NOT NULL,
+        name    TEXT NOT NULL,
+        bytes   INTEGER NOT NULL,
+        sha256  TEXT,
+        width   INTEGER,
+        height  INTEGER,
+        data    BLOB,
+        UNIQUE(role, name)
+      );
+
+      -- the open ended per object lists, one row each, in order
+      CREATE TABLE object_images (
+        object_id INTEGER NOT NULL REFERENCES objects(id),
+        seq       INTEGER NOT NULL,
+        name      TEXT NOT NULL,
+        bytes     INTEGER,
+        sha256    TEXT,
+        width     INTEGER,
+        height    INTEGER,
+        missing   INTEGER DEFAULT 0,
+        PRIMARY KEY(object_id, seq)
+      );
+      CREATE TABLE 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 (
+        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 (
+        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);
     ");
 
     /+ ↓ populate metadata +/
@@ -287,6 +338,25 @@ template spineAbstractionDb() {
       ~ ")"
     );
 
+    /+ ↓ the per object lists, one row each +/
+    auto img_stmt = db.prepare(
+      "INSERT INTO object_images"
+      ~ " (object_id, seq, name, bytes, sha256, width, height, missing)"
+      ~ " VALUES (:object_id, :seq, :name, :bytes, :sha256, :width, :height, :missing)"
+    );
+    auto lnk_stmt = db.prepare(
+      "INSERT INTO object_links (object_id, seq, url)"
+      ~ " VALUES (:object_id, :seq, :url)"
+    );
+    auto anc_stmt = db.prepare(
+      "INSERT INTO object_anchors (object_id, seq, anchor)"
+      ~ " VALUES (:object_id, :seq, :anchor)"
+    );
+    auto sub_stmt = db.prepare(
+      "INSERT INTO object_subtoc (object_id, seq, entry)"
+      ~ " VALUES (:object_id, :seq, :entry)"
+    );
+
     string[] section_order = ["head", "toc", "body", "endnotes",
       "glossary", "bibliography", "bookindex", "blurb"];
 
@@ -445,10 +515,126 @@ template spineAbstractionDb() {
 
         obj_stmt.execute();
         obj_stmt.reset();
+
+        /+ ↓ the per object lists, keyed on the row just written +/
+        long _obj_id = db.lastInsertRowid;
+        {
+          int _n = 0;
+          foreach (i; obj.metainfo.sha256.images) {
+            img_stmt.bind(":object_id", _obj_id);
+            img_stmt.bind(":seq", _n);
+            img_stmt.bind(":name", i.fileName);
+            img_stmt.bind(":bytes", cast(long) i.fileSize);
+            img_stmt.bind(":sha256", i.fileHash_sha256.toHexString.to!string);
+            img_stmt.bind(":width", i.imageWidth);
+            img_stmt.bind(":height", i.imageHeight);
+            img_stmt.bind(":missing", i.fileMissing ? 1 : 0);
+            img_stmt.execute();
+            img_stmt.reset();
+            ++_n;
+          }
+        }
+        {
+          int _n = 0;
+          foreach (lnk; obj.stow.link) {
+            if (lnk.length == 0) { continue; }
+            lnk_stmt.bind(":object_id", _obj_id);
+            lnk_stmt.bind(":seq", _n);
+            lnk_stmt.bind(":url", lnk);
+            lnk_stmt.execute();
+            lnk_stmt.reset();
+            ++_n;
+          }
+        }
+        {
+          int _n = 0;
+          foreach (at; obj.tags.anchor_tags) {
+            if (at.length == 0) { continue; }
+            anc_stmt.bind(":object_id", _obj_id);
+            anc_stmt.bind(":seq", _n);
+            anc_stmt.bind(":anchor", at);
+            anc_stmt.execute();
+            anc_stmt.reset();
+            ++_n;
+          }
+        }
+        {
+          int _n = 0;
+          foreach (st; obj.tags.lev4_subtoc) {
+            if (st.length == 0) { continue; }
+            sub_stmt.bind(":object_id", _obj_id);
+            sub_stmt.bind(":seq", _n);
+            sub_stmt.bind(":entry", st);
+            sub_stmt.execute();
+            sub_stmt.reset();
+            ++_n;
+          }
+        }
       }
     }
 
     obj_stmt.finalize();
+    img_stmt.finalize();
+    lnk_stmt.finalize();
+    anc_stmt.finalize();
+    sub_stmt.finalize();
+
+    /+ ↓ the document's images, carried inside the file so that output can be
+         produced from this database alone. bytes exactly as read, and the
+         digest taken over them, so that it can be checked against the digest
+         the abstraction recorded +/
+    {
+      import std.digest.sha : sha256Of;
+      auto file_stmt = db.prepare(
+        "INSERT OR IGNORE INTO files"
+        ~ " (role, name, bytes, sha256, width, height, data)"
+        ~ " VALUES (:role, :name, :bytes, :sha256, :width, :height, :data)"
+      );
+      int[string] _w, _h;
+      foreach (section; section_order) {
+        if (section !in doc_abstraction) { continue; }
+        foreach (obj; doc_abstraction[section]) {
+          foreach (i; obj.metainfo.sha256.images) {
+            if (i.imageWidth > 0)  { _w[i.fileName] = i.imageWidth; }
+            if (i.imageHeight > 0) { _h[i.fileName] = i.imageHeight; }
+          }
+        }
+      }
+      foreach (image; doc_matters.srcs.image_list) {
+        string _fn = doc_matters.src.image_dir_path ~ "/" ~ image;
+        ubyte[] _bytes;
+        try {
+          if (_fn.exists) { _bytes = cast(ubyte[]) _fn.read; }
+        } catch (Exception ex) {
+          _bytes = [];
+        }
+        if (_bytes.length == 0) {
+          if (doc_matters.opt.action.vox_gt_1) {
+            writeln("  image not carried into abstraction db: ", _fn);
+          }
+          continue;
+        }
+        file_stmt.bind(":role", "image");
+        file_stmt.bind(":name", image);
+        file_stmt.bind(":bytes", cast(long) _bytes.length);
+        file_stmt.bind(":sha256", sha256Of(_bytes).toHexString.to!string);
+        if (image in _w) { file_stmt.bind(":width", _w[image]); }
+        else {
+          import std.typecons : Nullable;
+          file_stmt.bind(":width", Nullable!int());
+        }
+        if (image in _h) { file_stmt.bind(":height", _h[image]); }
+        else {
+          import std.typecons : Nullable;
+          file_stmt.bind(":height", Nullable!int());
+        }
+        file_stmt.bind(":data", _bytes);
+        file_stmt.execute();
+        file_stmt.reset();
+      }
+      file_stmt.finalize();
+    }
+
     db.run("COMMIT TRANSACTION");
   }
 }