ocda: reading .ocda.db now faster than parsing markup

reading a .ocda.db was slower than parsing markup source. Taking the
largest markup document sample War and peace, 12,135 objects, optimised
build, before this commit and after:

  parse markup    0.87 s   0.87 s
  load .ssp       0.047 s  0.047 s
  load .ocda.db   1.09 s   0.128 s

The database goes from being 1.25x slower than parsing the document to
6.8x faster.

Two things fixed in the reader were:

First, four queries per object. object_images, object_links,
object_anchors and object_subtoc were queried per object as the objects
were built, each statement compiled fresh from a concatenated string.
For war and peace that is 48,540 statement preparations to collect
138 rows, which is all those four tables hold between them. They are now
four ordered sweeps, kept by object id, so the cost is what the tables
hold rather than what the document holds.

Second, and even more consequentially: d2sqlite3's row["name"] is
indexForName, a linear scan over the statement's columns that calls
sqlite3_column_name and allocates a D string for every column it passes.
At some fifty named reads per object over forty columns that is around a
thousand of those per object, twelve million for the document. The
column name to index map is now resolved once per statement and the
reads are an integer index.

Also here, since it was measured while doing this: sspReadFile no longer
hashes the file it read unless asked (with_digest). Only the database
writer wants that digest and it has the lines already, so every other
read was paying for it.

Existing outputs unaffected.

(assisted by Claude-Code)

1 files changed, 111 insertions, 74 deletions

diff --git a/org/in_source_files.org b/org/in_source_files.org
index 6b645b6..e78970a 100644
--- a/org/in_source_files.org
+++ b/org/in_source_files.org
@@ -1131,7 +1131,11 @@ template spineAbstractionRead() {
     }
     return true;
   }
-  SSPdocument sspReadFile(string file_path) {
+  /+ ↓ with_digest is off by default: hashing the file is only wanted where
+       the digest is going to be recorded, and on the largest document in the
+       sample set it is a tenth of what reading the file costs
+  +/
+  SSPdocument sspReadFile(string file_path, bool with_digest = false) {
     string   _text;
     string[] _lines;
     try {
@@ -1143,7 +1147,7 @@ template spineAbstractionRead() {
     }
     auto _doc = sspRead(_lines);
     if (!sspFormatVersionOk(_doc.format, file_path)) { return SSPdocument(); }
-    _doc.ssp_digest = sspDigestOfText(_text);
+    if (with_digest) { _doc.ssp_digest = sspDigestOfText(_text); }
     return _doc;
   }
 }
@@ -1278,6 +1282,44 @@ template spineAbstractionDbRead() {
       }
     }
     if (!sspFormatVersionOk(doc.format, db_file)) { return SSPdocument(); }
+    /+ ↓ the per object lists, gathered first, one sweep of each table.
+
+         these four tables hold what a single object row cannot: the images
+         an object carries, the links stowed off it, its anchor tags and its
+         lev4 subtoc entries. They are read here in four ordered passes and
+         kept by object id, rather than queried per object as the objects are
+         built. The cost is then what the tables hold, not what the document
+         holds: war and peace has 12,135 objects and 138 rows across all
+         four, which as four statements per object was 48,540 statement
+         preparations and made reading a .ocda.db slower than parsing the
+         markup it came from.
+    +/
+    ST_file_name_hash_size_[][long] _images_by_id;
+    string[][long]                  _links_by_id;
+    string[][long]                  _anchors_by_id;
+    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")
+    ) {
+      ST_file_name_hash_size_ _img;
+      _img.fileName        = r["name"].as!string;
+      _img.fileSize        = r["bytes"].as!ulong;
+      _img.fileHash_sha256 = _hex32(r["sha256"].as!string);
+      _img.imageWidth      = r["width"].as!int;
+      _img.imageHeight     = r["height"].as!int;
+      _img.fileMissing     = (r["missing"].as!int == 1);
+      _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")
+    ) { _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")
+    ) { _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")
+    ) { _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(
@@ -1288,93 +1330,88 @@ template spineAbstractionDbRead() {
     foreach (section; _sections) {
       doc.section_order ~= section;
       doc.abstraction[section] = [];
+      /+ ↓ column name to index, resolved once for the statement.
+
+           d2sqlite3's row["name"] is indexForName, a linear scan over the
+           columns that calls sqlite3_column_name and allocates a D string
+           for each one it passes. With some fifty named reads per object
+           over forty columns that is around a thousand of those per object,
+           and it was the larger half of what made reading a database slow.
+           Resolved once here, the reads below are an integer index.
+      +/
+      int[string] _col;
       foreach (row; db.execute(
         "SELECT * FROM objects WHERE section = '" ~ section ~ "' ORDER BY seq")
       ) {
+        if (_col.length == 0) {
+          foreach (_i; 0 .. row.length) { _col[row.columnName(_i)] = _i.to!int; }
+        }
         ObjGenericComposite obj;
-        long _id                          = row["id"].as!long;
-        obj.metainfo.ocn                  = row["ocn"].as!int;
-        obj.metainfo.is_a                 = row["is_a"].as!string;
-        obj.metainfo.is_of_part           = row["is_of_part"].as!string;
-        obj.metainfo.is_of_section        = (row["is_of_section"].as!string.length > 0)
-          ? row["is_of_section"].as!string : section;
-        obj.metainfo.is_of_type           = row["is_of_type"].as!string;
-        obj.metainfo.identifier           = row["identifier"].as!string;
+        long _id                          = row[_col["id"]].as!long;
+        obj.metainfo.ocn                  = row[_col["ocn"]].as!int;
+        obj.metainfo.is_a                 = row[_col["is_a"]].as!string;
+        obj.metainfo.is_of_part           = row[_col["is_of_part"]].as!string;
+        obj.metainfo.is_of_section        = (row[_col["is_of_section"]].as!string.length > 0)
+          ? row[_col["is_of_section"]].as!string : section;
+        obj.metainfo.is_of_type           = row[_col["is_of_type"]].as!string;
+        obj.metainfo.identifier           = row[_col["identifier"]].as!string;
         obj.metainfo.heading_lev_markup   = (obj.metainfo.is_a == "heading")
-          ? row["heading_level"].as!int : 9;
-        obj.metainfo.heading_lev_collapsed = (row["heading_lev_collapsed"].as!string.length > 0)
-          ? row["heading_lev_collapsed"].as!int : 9;
-        obj.metainfo.parent_ocn           = row["parent_ocn"].as!int;
-        obj.metainfo.parent_lev_markup    = row["parent_lev"].as!int;
-        obj.metainfo.last_descendant_ocn  = row["last_descendant_ocn"].as!int;
-        obj.metainfo.children_headings    = _jsonInts(row["children"].as!string);
-        obj.metainfo.markedup_ancestors   = _jsonEight(row["ancestors"].as!string);
-        obj.metainfo.collapsed_ancestors  = _jsonEight(row["ancestors_collapsed"].as!string);
+          ? row[_col["heading_level"]].as!int : 9;
+        obj.metainfo.heading_lev_collapsed = (row[_col["heading_lev_collapsed"]].as!string.length > 0)
+          ? row[_col["heading_lev_collapsed"]].as!int : 9;
+        obj.metainfo.parent_ocn           = row[_col["parent_ocn"]].as!int;
+        obj.metainfo.parent_lev_markup    = row[_col["parent_lev"]].as!int;
+        obj.metainfo.last_descendant_ocn  = row[_col["last_descendant_ocn"]].as!int;
+        obj.metainfo.children_headings    = _jsonInts(row[_col["children"]].as!string);
+        obj.metainfo.markedup_ancestors   = _jsonEight(row[_col["ancestors"]].as!string);
+        obj.metainfo.collapsed_ancestors  = _jsonEight(row[_col["ancestors_collapsed"]].as!string);
         obj.metainfo.dom_structure_markedup_tags_status
-          = _jsonEight(row["dom_status"].as!string);
+          = _jsonEight(row[_col["dom_status"]].as!string);
         obj.metainfo.dom_structure_collapsed_tags_status
-          = _jsonEight(row["dom_status_collapsed"].as!string);
-        obj.metainfo.dummy_heading        = (row["dummy_heading"].as!int == 1);
-        obj.metainfo.object_number_off    = (row["object_number_off"].as!int == 1);
-        obj.metainfo.attrib               = row["attrib"].as!string;
-        obj.metainfo.lang                 = row["meta_lang"].as!string;
-        obj.metainfo.syntax               = row["meta_syntax"].as!string;
-        obj.metainfo.sha256.text          = _hex32(row["sha256"].as!string);
-        obj.attrib.indent_base            = row["indent_base"].as!int;
-        obj.attrib.indent_hang            = row["indent_hang"].as!int;
-        obj.attrib.bullet                 = (row["bullet"].as!int == 1);
-        obj.attrib.language               = row["lang"].as!string;
-        obj.has.inline_links              = (row["has_links"].as!int == 1);
-        obj.has.inline_notes_reg          = (row["has_notes_reg"].as!int == 1);
-        obj.has.inline_notes_star         = (row["has_notes_star"].as!int == 1);
-        obj.has.images                    = (row["has_images"].as!int == 1);
-        obj.has.image_without_dimensions  = (row["has_images_no_dim"].as!int == 1);
-        obj.tags.in_segment_html          = row["segment"].as!string;
-        obj.tags.segname_prev             = row["segment_prev"].as!string;
-        obj.tags.segname_next             = row["segment_next"].as!string;
-        obj.tags.segment_anchor_tag_epub  = row["segment_epub"].as!string;
-        obj.tags.html_segment_anchor_tag_is = row["segment_html_is"].as!string;
-        obj.tags.epub_segment_anchor_tag_is = row["segment_epub_is"].as!string;
-        obj.tags.anchor_tag_html          = row["anchor"].as!string;
-        obj.tags.heading_lev_anchor_tag   = row["heading_lev_anchor"].as!string;
+          = _jsonEight(row[_col["dom_status_collapsed"]].as!string);
+        obj.metainfo.dummy_heading        = (row[_col["dummy_heading"]].as!int == 1);
+        obj.metainfo.object_number_off    = (row[_col["object_number_off"]].as!int == 1);
+        obj.metainfo.attrib               = row[_col["attrib"]].as!string;
+        obj.metainfo.lang                 = row[_col["meta_lang"]].as!string;
+        obj.metainfo.syntax               = row[_col["meta_syntax"]].as!string;
+        obj.metainfo.sha256.text          = _hex32(row[_col["sha256"]].as!string);
+        obj.attrib.indent_base            = row[_col["indent_base"]].as!int;
+        obj.attrib.indent_hang            = row[_col["indent_hang"]].as!int;
+        obj.attrib.bullet                 = (row[_col["bullet"]].as!int == 1);
+        obj.attrib.language               = row[_col["lang"]].as!string;
+        obj.has.inline_links              = (row[_col["has_links"]].as!int == 1);
+        obj.has.inline_notes_reg          = (row[_col["has_notes_reg"]].as!int == 1);
+        obj.has.inline_notes_star         = (row[_col["has_notes_star"]].as!int == 1);
+        obj.has.images                    = (row[_col["has_images"]].as!int == 1);
+        obj.has.image_without_dimensions  = (row[_col["has_images_no_dim"]].as!int == 1);
+        obj.tags.in_segment_html          = row[_col["segment"]].as!string;
+        obj.tags.segname_prev             = row[_col["segment_prev"]].as!string;
+        obj.tags.segname_next             = row[_col["segment_next"]].as!string;
+        obj.tags.segment_anchor_tag_epub  = row[_col["segment_epub"]].as!string;
+        obj.tags.html_segment_anchor_tag_is = row[_col["segment_html_is"]].as!string;
+        obj.tags.epub_segment_anchor_tag_is = row[_col["segment_epub_is"]].as!string;
+        obj.tags.anchor_tag_html          = row[_col["anchor"]].as!string;
+        obj.tags.heading_lev_anchor_tag   = row[_col["heading_lev_anchor"]].as!string;
         {
           string[8] _h;
-          foreach (i, h; _jsonStrs(row["heading_ancestors_text"].as!string)) {
+          foreach (i, h; _jsonStrs(row[_col["heading_ancestors_text"]].as!string)) {
             if (i < _h.length) { _h[i] = h; }
           }
           obj.tags.heading_ancestors_text = _h;
         }
         if (obj.metainfo.is_a == "table") {
-          obj.table.number_of_columns     = row["table_cols"].as!int;
-          obj.table.column_widths         = _jsonDoubles(row["table_widths"].as!string);
-          obj.table.column_aligns         = _jsonStrs(row["table_aligns"].as!string);
-          obj.table.heading               = (row["table_header"].as!int == 1);
-        }
-        obj.code_block.linenumbers        = (row["code_linenumbers"].as!int == 1);
-        obj.text                          = row["text"].as!string;
-        /+ ↓ the per object lists +/
-        foreach (r; db.execute(
-          "SELECT name, bytes, sha256, width, height, missing FROM object_images"
-          ~ " WHERE object_id = " ~ _id.to!string ~ " ORDER BY seq")
-        ) {
-          ST_file_name_hash_size_ _img;
-          _img.fileName        = r["name"].as!string;
-          _img.fileSize        = r["bytes"].as!ulong;
-          _img.fileHash_sha256 = _hex32(r["sha256"].as!string);
-          _img.imageWidth      = r["width"].as!int;
-          _img.imageHeight     = r["height"].as!int;
-          _img.fileMissing     = (r["missing"].as!int == 1);
-          obj.metainfo.sha256.images ~= _img;
+          obj.table.number_of_columns     = row[_col["table_cols"]].as!int;
+          obj.table.column_widths         = _jsonDoubles(row[_col["table_widths"]].as!string);
+          obj.table.column_aligns         = _jsonStrs(row[_col["table_aligns"]].as!string);
+          obj.table.heading               = (row[_col["table_header"]].as!int == 1);
         }
-        foreach (r; db.execute(
-          "SELECT url FROM object_links WHERE object_id = " ~ _id.to!string ~ " ORDER BY seq")
-        ) { obj.stow.link ~= r["url"].as!string; }
-        foreach (r; db.execute(
-          "SELECT anchor FROM object_anchors WHERE object_id = " ~ _id.to!string ~ " ORDER BY seq")
-        ) { obj.tags.anchor_tags ~= r["anchor"].as!string; }
-        foreach (r; db.execute(
-          "SELECT entry FROM object_subtoc WHERE object_id = " ~ _id.to!string ~ " ORDER BY seq")
-        ) { obj.tags.lev4_subtoc ~= r["entry"].as!string; }
+        obj.code_block.linenumbers        = (row[_col["code_linenumbers"]].as!int == 1);
+        obj.text                          = row[_col["text"]].as!string;
+        /+ ↓ the per object lists, from the sweeps above +/
+        if (auto _v = _id in _images_by_id)  { obj.metainfo.sha256.images = *_v; }
+        if (auto _v = _id in _links_by_id)   { obj.stow.link           = *_v; }
+        if (auto _v = _id in _anchors_by_id) { obj.tags.anchor_tags    = *_v; }
+        if (auto _v = _id in _subtoc_by_id)  { obj.tags.lev4_subtoc    = *_v; }
         doc.abstraction[section] ~= obj;
       }
     }