ocda db: structural fields (arrays as JSON)

Added the structure ocda was extended to compute as JSON arrays
(a column each).

JSON arrays, allows SQL can reach inside them
(with CHECK(json_valid()) on each)

  select ocn from objects where json_extract(ancestors,'$[4]') = 9;
  select ocn from objects where json_array_length(children) > 3;

ancestors and table_widths change format for this reason; nothing
outside spine reads the file yet, and metadata now records what it is:
schema.name, schema.version, source.filename, source.language.

INTEGER PRIMARY KEY in place of AUTOINCREMENT, which was paying
for a guarantee not needed here and creating sqlite_sequence, and
UNIQUE(section, seq), which is the table's actual key.

Note for anyone editing this file: the schema is a D string handed to
sqlite, so comments inside it must be SQL comments. A D /+ +/ comment
there aborts the statement and leaves the table missing, silently.

(assisted by Claude-Code)

1 files changed, 174 insertions, 82 deletions

diff --git a/src/sisudoc/outputs/io_out/create_abstraction_db.d b/src/sisudoc/outputs/io_out/create_abstraction_db.d
index d1f3f53..d953472 100644
--- a/src/sisudoc/outputs/io_out/create_abstraction_db.d
+++ b/src/sisudoc/outputs/io_out/create_abstraction_db.d
@@ -52,6 +52,7 @@ module sisudoc.outputs.io_out.create_abstraction_db;
 /+ ↓ write document abstraction as per-document sqlite3 database +/
 template spineAbstractionDb() {
   import std.conv : to;
+  import std.digest : toHexString;
   import std.file;
   import std.path;
   import std.stdio;
@@ -106,40 +107,69 @@ template spineAbstractionDb() {
         key   TEXT PRIMARY KEY,
         value TEXT NOT NULL
       );
-
+      -- objects: one row per abstraction object, in document order.
+      -- the fixed width arrays (ancestors, dom status, children, heading
+      -- ancestors text, table widths and aligns) are JSON arrays so that
+      -- 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 (
-        id                   INTEGER PRIMARY KEY AUTOINCREMENT,
-        section              TEXT NOT NULL,
-        seq                  INTEGER NOT NULL,
-        ocn                  INTEGER DEFAULT 0,
-        is_a                 TEXT NOT NULL,
-        is_of_part           TEXT,
-        is_of_type           TEXT,
-        heading_level        INTEGER,
-        identifier           TEXT,
-        parent_ocn           INTEGER DEFAULT 0,
-        last_descendant_ocn  INTEGER DEFAULT 0,
-        ancestors            TEXT,
-        dummy_heading        INTEGER DEFAULT 0,
-        object_number_off    INTEGER DEFAULT 0,
-        indent_base          INTEGER DEFAULT 0,
-        indent_hang          INTEGER DEFAULT 0,
-        bullet               INTEGER DEFAULT 0,
-        lang                 TEXT,
-        has_links            INTEGER DEFAULT 0,
-        has_notes_reg        INTEGER DEFAULT 0,
-        has_notes_star       INTEGER DEFAULT 0,
-        has_images           INTEGER DEFAULT 0,
-        segment              TEXT,
-        segment_prev         TEXT,
-        segment_next         TEXT,
-        anchor               TEXT,
-        table_cols           INTEGER,
-        table_widths         TEXT,
-        table_header         INTEGER,
-        code_syntax          TEXT,
-        code_linenumbers     INTEGER DEFAULT 0,
-        text                 TEXT
+        id                    INTEGER PRIMARY KEY,
+        section               TEXT NOT NULL,
+        seq                   INTEGER NOT NULL,
+        ocn                   INTEGER DEFAULT 0,
+        is_a                  TEXT NOT NULL,
+        is_of_part            TEXT,
+        is_of_section         TEXT,
+        is_of_type            TEXT,
+        heading_level         INTEGER,
+        heading_lev_collapsed INTEGER,
+        identifier            TEXT,
+        parent_ocn            INTEGER DEFAULT 0,
+        parent_lev            INTEGER DEFAULT 0,
+        last_descendant_ocn   INTEGER DEFAULT 0,
+        children              TEXT
+          CHECK(children IS NULL OR json_valid(children)),
+        ancestors             TEXT
+          CHECK(ancestors IS NULL OR json_valid(ancestors)),
+        ancestors_collapsed   TEXT
+          CHECK(ancestors_collapsed IS NULL OR json_valid(ancestors_collapsed)),
+        dom_status            TEXT
+          CHECK(dom_status IS NULL OR json_valid(dom_status)),
+        dom_status_collapsed  TEXT
+          CHECK(dom_status_collapsed IS NULL OR json_valid(dom_status_collapsed)),
+        dummy_heading         INTEGER DEFAULT 0,
+        object_number_off     INTEGER DEFAULT 0,
+        attrib                TEXT,
+        meta_lang             TEXT,
+        meta_syntax           TEXT,
+        sha256                TEXT,
+        indent_base           INTEGER DEFAULT 0,
+        indent_hang           INTEGER DEFAULT 0,
+        bullet                INTEGER DEFAULT 0,
+        lang                  TEXT,
+        has_links             INTEGER DEFAULT 0,
+        has_notes_reg         INTEGER DEFAULT 0,
+        has_notes_star        INTEGER DEFAULT 0,
+        has_images            INTEGER DEFAULT 0,
+        has_images_no_dim     INTEGER DEFAULT 0,
+        segment               TEXT,
+        segment_prev          TEXT,
+        segment_next          TEXT,
+        segment_epub          TEXT,
+        anchor                TEXT,
+        heading_lev_anchor    TEXT,
+        heading_ancestors_text TEXT
+          CHECK(heading_ancestors_text IS NULL OR json_valid(heading_ancestors_text)),
+        table_cols            INTEGER,
+        table_widths          TEXT
+          CHECK(table_widths IS NULL OR json_valid(table_widths)),
+        table_aligns          TEXT
+          CHECK(table_aligns IS NULL OR json_valid(table_aligns)),
+        table_header          INTEGER,
+        code_linenumbers      INTEGER DEFAULT 0,
+        text                  TEXT,
+        UNIQUE(section, seq)
       );
 
       CREATE INDEX idx_objects_section ON objects(section);
@@ -167,6 +197,12 @@ template spineAbstractionDb() {
       }
     }
 
+    /+ ↓ what this file is, so a reader can tell before it starts +/
+    insertMeta("schema.name", "sisu-abstraction-db");
+    insertMeta("schema.version", "1");
+    insertMeta("source.filename", doc_matters.src.filename);
+    insertMeta("source.language", doc_matters.src.language);
+
     insertMeta("title.main", meta.title_main);
     insertMeta("title.subtitle", meta.title_subtitle);
     insertMeta("title.full", meta.title_full);
@@ -221,23 +257,33 @@ template spineAbstractionDb() {
     /+ ↓ populate objects +/
     auto obj_stmt = db.prepare(
       "INSERT INTO objects ("
-      ~ "section, seq, ocn, is_a, is_of_part, is_of_type,"
-      ~ "heading_level, identifier, parent_ocn, last_descendant_ocn,"
-      ~ "ancestors, dummy_heading, object_number_off,"
+      ~ "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,"
+      ~ "dom_status, dom_status_collapsed,"
+      ~ "dummy_heading, object_number_off,"
+      ~ "attrib, meta_lang, meta_syntax, sha256,"
       ~ "indent_base, indent_hang, bullet, lang,"
-      ~ "has_links, has_notes_reg, has_notes_star, has_images,"
-      ~ "segment, segment_prev, segment_next, anchor,"
-      ~ "table_cols, table_widths, table_header,"
-      ~ "code_syntax, code_linenumbers, text"
+      ~ "has_links, has_notes_reg, has_notes_star, has_images, has_images_no_dim,"
+      ~ "segment, segment_prev, segment_next, segment_epub,"
+      ~ "anchor, heading_lev_anchor, heading_ancestors_text,"
+      ~ "table_cols, table_widths, table_aligns, table_header,"
+      ~ "code_linenumbers, text"
       ~ ") VALUES ("
-      ~ ":section, :seq, :ocn, :is_a, :is_of_part, :is_of_type,"
-      ~ ":heading_level, :identifier, :parent_ocn, :last_descendant_ocn,"
-      ~ ":ancestors, :dummy_heading, :object_number_off,"
+      ~ ":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,"
+      ~ ":dom_status, :dom_status_collapsed,"
+      ~ ":dummy_heading, :object_number_off,"
+      ~ ":attrib, :meta_lang, :meta_syntax, :sha256,"
       ~ ":indent_base, :indent_hang, :bullet, :lang,"
-      ~ ":has_links, :has_notes_reg, :has_notes_star, :has_images,"
-      ~ ":segment, :segment_prev, :segment_next, :anchor,"
-      ~ ":table_cols, :table_widths, :table_header,"
-      ~ ":code_syntax, :code_linenumbers, :text"
+      ~ ":has_links, :has_notes_reg, :has_notes_star, :has_images, :has_images_no_dim,"
+      ~ ":segment, :segment_prev, :segment_next, :segment_epub,"
+      ~ ":anchor, :heading_lev_anchor, :heading_ancestors_text,"
+      ~ ":table_cols, :table_widths, :table_aligns, :table_header,"
+      ~ ":code_linenumbers, :text"
       ~ ")"
     );
 
@@ -264,45 +310,86 @@ template spineAbstractionDb() {
             obj_stmt.bind(param, Nullable!string());
           }
         }
-
-        bindStr(":is_of_part", obj.metainfo.is_of_part);
-        bindStr(":is_of_type", obj.metainfo.is_of_type);
-
-        /+ ↓ heading level +/
-        {
+        /+ ↓ nullable int fields +/
+        void bindInt(string param, int val, bool is_set) {
           import std.typecons : Nullable;
-          if (obj.metainfo.is_a == "heading" && obj.metainfo.heading_lev_markup < 9) {
-            obj_stmt.bind(":heading_level", obj.metainfo.heading_lev_markup);
+          if (is_set) {
+            obj_stmt.bind(param, val);
           } else {
-            obj_stmt.bind(":heading_level", Nullable!int());
+            obj_stmt.bind(param, Nullable!int());
+          }
+        }
+        /+ ↓ fixed width int array as a JSON array, NULL when all zero +/
+        void bindJsonInts(T)(string param, T arr) {
+          import std.typecons : Nullable;
+          bool any = false;
+          foreach (a; arr) { if (a != 0) { any = true; break; } }
+          if (any) {
+            string[] _j;
+            foreach (a; arr) { _j ~= a.to!string; }
+            obj_stmt.bind(param, "[" ~ _j.join(",") ~ "]");
+          } else {
+            obj_stmt.bind(param, Nullable!string());
+          }
+        }
+        /+ ↓ string array as a JSON array, NULL when every slot is empty +/
+        void bindJsonStrs(T)(string param, T arr) {
+          import std.json : JSONValue;
+          import std.typecons : Nullable;
+          bool any = false;
+          foreach (s; arr) { if (s.length > 0) { any = true; break; } }
+          if (any) {
+            string[] _s;
+            foreach (s; arr) { _s ~= s.to!string; }
+            obj_stmt.bind(param, JSONValue(_s).toString);
+          } else {
+            obj_stmt.bind(param, Nullable!string());
           }
         }
 
+        bindStr(":is_of_part", obj.metainfo.is_of_part);
+        bindStr(":is_of_section", obj.metainfo.is_of_section);
+        bindStr(":is_of_type", obj.metainfo.is_of_type);
+
+        /+ ↓ heading levels, marked up and collapsed, 9 meaning unset +/
+        bindInt(":heading_level", obj.metainfo.heading_lev_markup,
+          (obj.metainfo.is_a == "heading" && obj.metainfo.heading_lev_markup < 9));
+        bindInt(":heading_lev_collapsed", obj.metainfo.heading_lev_collapsed,
+          (obj.metainfo.heading_lev_collapsed < 9));
+
         bindStr(":identifier", obj.metainfo.identifier);
         obj_stmt.bind(":parent_ocn", obj.metainfo.parent_ocn);
+        obj_stmt.bind(":parent_lev", obj.metainfo.parent_lev_markup);
         obj_stmt.bind(":last_descendant_ocn", obj.metainfo.last_descendant_ocn);
 
-        /+ ↓ ancestors as space-separated integers +/
+        bindJsonInts(":children", obj.metainfo.children_headings);
+        bindJsonInts(":ancestors", obj.metainfo.markedup_ancestors);
+        bindJsonInts(":ancestors_collapsed", obj.metainfo.collapsed_ancestors);
+        bindJsonInts(":dom_status", obj.metainfo.dom_structure_markedup_tags_status);
+        bindJsonInts(":dom_status_collapsed", obj.metainfo.dom_structure_collapsed_tags_status);
+
+        obj_stmt.bind(":dummy_heading", obj.metainfo.dummy_heading ? 1 : 0);
+        obj_stmt.bind(":object_number_off", obj.metainfo.object_number_off ? 1 : 0);
+        bindStr(":attrib", obj.metainfo.attrib);
+        bindStr(":meta_lang", obj.metainfo.lang);
+        bindStr(":meta_syntax", obj.metainfo.syntax);
+
+        /+ ↓ object digest, hex, absent when never taken +/
         {
-          bool has_ancestors = false;
-          foreach (a; obj.metainfo.markedup_ancestors) {
-            if (a != 0) { has_ancestors = true; break; }
+          import std.typecons : Nullable;
+          bool has_sha = false;
+          foreach (b; obj.metainfo.sha256.text) {
+            if (b != 0) { has_sha = true; break; }
           }
-          if (has_ancestors) {
-            string anc;
-            foreach (i, a; obj.metainfo.markedup_ancestors) {
-              if (i > 0) anc ~= " ";
-              anc ~= a.to!string;
-            }
-            obj_stmt.bind(":ancestors", anc);
+          if (has_sha) {
+            obj_stmt.bind(":sha256", obj.metainfo.sha256.text.toHexString.to!string);
           } else {
-            import std.typecons : Nullable;
-            obj_stmt.bind(":ancestors", Nullable!string());
+            obj_stmt.bind(":sha256", Nullable!string());
           }
         }
 
-        obj_stmt.bind(":dummy_heading", obj.metainfo.dummy_heading ? 1 : 0);
-        obj_stmt.bind(":object_number_off", obj.metainfo.object_number_off ? 1 : 0);
+        // obj_stmt.bind(":dummy_heading", obj.metainfo.dummy_heading ? 1 : 0);
+        // obj_stmt.bind(":object_number_off", obj.metainfo.object_number_off ? 1 : 0);
         obj_stmt.bind(":indent_base", obj.attrib.indent_base);
         obj_stmt.bind(":indent_hang", obj.attrib.indent_hang);
         obj_stmt.bind(":bullet", obj.attrib.bullet ? 1 : 0);
@@ -311,10 +398,14 @@ template spineAbstractionDb() {
         obj_stmt.bind(":has_notes_reg", obj.has.inline_notes_reg ? 1 : 0);
         obj_stmt.bind(":has_notes_star", obj.has.inline_notes_star ? 1 : 0);
         obj_stmt.bind(":has_images", obj.has.images ? 1 : 0);
+        obj_stmt.bind(":has_images_no_dim", obj.has.image_without_dimensions ? 1 : 0);
         bindStr(":segment", obj.tags.in_segment_html);
         bindStr(":segment_prev", obj.tags.segname_prev);
         bindStr(":segment_next", obj.tags.segname_next);
+        bindStr(":segment_epub", obj.tags.segment_anchor_tag_epub);
         bindStr(":anchor", obj.tags.anchor_tag_html);
+        bindStr(":heading_lev_anchor", obj.tags.heading_lev_anchor_tag);
+        bindJsonStrs(":heading_ancestors_text", obj.tags.heading_ancestors_text);
 
         /+ ↓ table properties +/
         {
@@ -324,29 +415,30 @@ template spineAbstractionDb() {
             if (obj.table.column_widths.length > 0) {
               string[] ws;
               foreach (w; obj.table.column_widths) ws ~= w.to!string;
-              obj_stmt.bind(":table_widths", ws.join(" "));
+              obj_stmt.bind(":table_widths", "[" ~ ws.join(",") ~ "]");
             } else {
               obj_stmt.bind(":table_widths", Nullable!string());
             }
+            if (obj.table.column_aligns.length > 0) {
+              import std.json : JSONValue;
+              string[] _al;
+              foreach (a; obj.table.column_aligns) { _al ~= a.to!string; }
+              obj_stmt.bind(":table_aligns", JSONValue(_al).toString);
+            } else {
+              obj_stmt.bind(":table_aligns", Nullable!string());
+            }
             obj_stmt.bind(":table_header", obj.table.heading ? 1 : 0);
           } else {
             obj_stmt.bind(":table_cols", Nullable!int());
             obj_stmt.bind(":table_widths", Nullable!string());
+            obj_stmt.bind(":table_aligns", Nullable!string());
             obj_stmt.bind(":table_header", Nullable!int());
           }
         }
 
         /+ ↓ code block properties +/
-        {
-          import std.typecons : Nullable;
-          if (obj.metainfo.is_a == "code") {
-            bindStr(":code_syntax", obj.metainfo.syntax);
-            obj_stmt.bind(":code_linenumbers", obj.code_block.linenumbers ? 1 : 0);
-          } else {
-            obj_stmt.bind(":code_syntax", Nullable!string());
-            obj_stmt.bind(":code_linenumbers", 0);
-          }
-        }
+        obj_stmt.bind(":code_linenumbers",
+          ((obj.metainfo.is_a == "code") && obj.code_block.linenumbers) ? 1 : 0);
 
         /+ ↓ text content +/
         bindStr(":text", obj.text);