a reader for the ocda db, and its round trip

sisudoc.ocda.abstraction.db_in reads a <doc>.abstraction.db back
into ObjGenericComposite[][string], the same value ssp_in returns
from a .ssp, so a consumer need not know which artefact it was
handed. It mixes in the .ssp reader for that shared document
struct rather than declaring a second one.

--db-round-trip=<file.abstraction.db> reads a database and emits
it as .ssp on stdout, through sspObjectRecord as the other round
trip does. Held against the .ssp written from the same document,
this says whether the two artefacts really carry the same thing:
not a count of fields, as test-abstraction-db.sh does, but the
whole document reconstructed from the database and compared to the
text.

  SpinePOD=... ./test/test-abstraction-db-roundtrip.sh ./bin/spine-ldc
  PASS: all 35 databases re-emit their document's .ssp exactly

It passed on the first run over the whole sample set, which is evidence
that the database is now field-complete against the .ssp rather than
merely counting the same.

Four tests with different checks:
  test-abstraction-ssp.sh            the abstraction has not changed
  test-abstraction-db.sh             the two serialisations agree, field by field
  test-abstraction-ssp-roundtrip.sh  the .ssp can be read back whole
  test-abstraction-db-roundtrip.sh   the .db can be read back whole

(assisted by Claude-Code)

3 files changed, 307 insertions, 0 deletions

diff --git a/src/sisudoc/ocda/abstraction/db_in.d b/src/sisudoc/ocda/abstraction/db_in.d
new file mode 100644
index 0000000..02007b8
--- /dev/null
+++ b/src/sisudoc/ocda/abstraction/db_in.d
@@ -0,0 +1,265 @@
+/+
+- Name: SisuDoc Spine, Doc Reform [a part of]
+  - Description: documents, structuring, processing, publishing, search
+    - static content generator
+
+  - Author: Ralph Amissah
+    [ralph.amissah@gmail.com]
+
+  - Copyright: (C) 2015 (continuously updated, current 2026) Ralph Amissah, All Rights Reserved.
+
+  - License: AGPL 3 or later:
+
+    Spine (SiSU), a framework for document structuring, publishing and
+    search
+
+    Copyright (C) Ralph Amissah
+
+    This program is free software: you can redistribute it and/or modify it
+    under the terms of the GNU AFERO General Public License as published by the
+    Free Software Foundation, either version 3 of the License, or (at your
+    option) any later version.
+
+    This program is distributed in the hope that it will be useful, but WITHOUT
+    ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
+    FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
+    more details.
+
+    You should have received a copy of the GNU General Public License along with
+    this program. If not, see [https://www.gnu.org/licenses/].
+
+    If you have Internet connection, the latest version of the AGPL should be
+    available at these locations:
+    [https://www.fsf.org/licensing/licenses/agpl.html]
+    [https://www.gnu.org/licenses/agpl.html]
+
+  - Spine (by Doc Reform, related to SiSU) uses standard:
+    - docReform markup syntax
+      - standard SiSU markup syntax with modified headers and minor modifications
+    - docReform object numbering
+      - standard SiSU object citation numbering & system
+
+  - Homepages:
+    [https://www.sisudoc.org]
+    [https://www.doc-reform.org]
+
+  - Git
+    [https://git.sisudoc.org/]
+
++/
+module sisudoc.ocda.abstraction.db_in;
+@safe:
+/+ ↓ read a <doc>.abstraction.db back into the document abstraction
+
+     the counterpart of sisudoc.outputs.io_out.create_abstraction_db, and the
+     sibling of sisudoc.ocda.abstraction.ssp_in: it returns the same value,
+     so a consumer does not have to know which of the two artefacts it was
+     handed.
+
+     the check is the same one: read a database, emit it as .ssp with the
+     writer's own record definition, and require the result to equal the .ssp
+     written from the same document.
++/
+template spineAbstractionDbRead() {
+  import std.conv : to;
+  import std.file;
+  import std.stdio;
+  import std.string;
+  import std.array;
+  import std.json;
+  import d2sqlite3;
+  import sisudoc.ocda.abstraction.ssp_in : spineAbstractionRead;
+  mixin spineAbstractionRead;   // for SSPdocument, and the shared unescape
+  private int[8] _jsonEight(string _s) {
+    int[8] _out;
+    if (_s.length == 0) { return _out; }
+    try {
+      auto _j = parseJSON(_s);
+      foreach (i, v; _j.array) {
+        if (i < _out.length) { _out[i] = v.integer.to!int; }
+      }
+    } catch (Exception ex) {}
+    return _out;
+  }
+  private int[] _jsonInts(string _s) {
+    int[] _out;
+    if (_s.length == 0) { return _out; }
+    try {
+      auto _j = parseJSON(_s);
+      foreach (v; _j.array) { _out ~= v.integer.to!int; }
+    } catch (Exception ex) {}
+    return _out;
+  }
+  private string[] _jsonStrs(string _s) {
+    string[] _out;
+    if (_s.length == 0) { return _out; }
+    try {
+      auto _j = parseJSON(_s);
+      foreach (v; _j.array) { _out ~= v.str; }
+    } catch (Exception ex) {}
+    return _out;
+  }
+  private double[] _jsonDoubles(string _s) {
+    double[] _out;
+    if (_s.length == 0) { return _out; }
+    try {
+      auto _j = parseJSON(_s);
+      foreach (v; _j.array) {
+        _out ~= (v.type == JSONType.integer) ? v.integer.to!double : v.floating;
+      }
+    } catch (Exception ex) {}
+    return _out;
+  }
+  private ubyte[32] _hex32(string hex) {
+    ubyte[32] _out;
+    if (hex.length < 64) { return _out; }
+    foreach (i; 0..32) { _out[i] = hex[i*2 .. i*2+2].to!ubyte(16); }
+    return _out;
+  }
+  private string _levMarkedUp(int lev) {
+    switch (lev) {
+    case 0:  return "A";
+    case 1:  return "B";
+    case 2:  return "C";
+    case 3:  return "D";
+    case 4:  return "1";
+    case 5:  return "2";
+    case 6:  return "3";
+    case 7:  return "4";
+    default: return "";
+    }
+  }
+  @trusted SSPdocument dbReadFile(string db_file) {
+    SSPdocument doc;
+    if (!db_file.exists) {
+      writeln("ERROR: no such file: ", db_file);
+      return doc;
+    }
+    auto db = Database(db_file, SQLITE_OPEN_READONLY);
+    /+ ↓ the three 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")) {
+      string _k = row["key"].as!string;
+      string _v = row["value"].as!string;
+      if (_k.startsWith("make.")) {
+        string _kk = _k["make.".length .. $];
+        doc.make[_kk] = _v;
+        doc.make_order ~= _kk;
+      } else if (_k.startsWith("doc_has.")) {
+        string _kk = _k["doc_has.".length .. $];
+        doc.doc_has[_kk] = _v;
+        doc.doc_has_order ~= _kk;
+      } else if (_k.startsWith("schema.")) {
+        if (_k == "schema.version") { doc.format = "% SiSU Document Abstraction v0.1"; }
+      } else if (_k == "source.filename") {
+        doc.source = _v;
+      } else if (_k == "source.language") {
+        // carried in the path, not in the .ssp header
+      } else {
+        doc.meta[_k] = _v;
+        doc.meta_order ~= _k;
+      }
+    }
+    /+ ↓ 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)")
+    ) {
+      _sections ~= row["section"].as!string;
+    }
+    foreach (section; _sections) {
+      doc.section_order ~= section;
+      doc.abstraction[section] = [];
+      foreach (row; db.execute(
+        "SELECT * FROM objects WHERE section = '" ~ section ~ "' ORDER BY seq")
+      ) {
+        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;
+        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);
+        obj.metainfo.dom_structure_markedup_tags_status
+          = _jsonEight(row["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;
+        {
+          string[8] _h;
+          foreach (i, h; _jsonStrs(row["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;
+        }
+        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; }
+        doc.abstraction[section] ~= obj;
+      }
+    }
+    return doc;
+  }
+}
diff --git a/src/sisudoc/ocda/abstraction/package.d b/src/sisudoc/ocda/abstraction/package.d
index 6281f6e..3a58b2e 100644
--- a/src/sisudoc/ocda/abstraction/package.d
+++ b/src/sisudoc/ocda/abstraction/package.d
@@ -85,3 +85,4 @@ public import sisudoc.ocda.meta.metadoc;          // spineAbstraction (A-layer)
 public import sisudoc.ocda.meta.metadoc_from_src; // docAbstraction   (B-layer)
 public import sisudoc.ocda.abstraction.ssp;       // spineAbstractionTxt (.ssp)
 public import sisudoc.ocda.abstraction.ssp_in;    // spineAbstractionRead (.ssp back in)
+public import sisudoc.ocda.abstraction.db_in;     // spineAbstractionDbRead (.db back in)
diff --git a/src/sisudoc/spine.d b/src/sisudoc/spine.d
index 9ceb069..4bf714d 100644
--- a/src/sisudoc/spine.d
+++ b/src/sisudoc/spine.d
@@ -214,6 +214,7 @@ string program_name = "spine";
   string[string] settings = [
     "output"                      : "",
     "ssp-round-trip"              : "",
+    "db-round-trip"               : "",
     "www-http"                    : "",
     "www-host"                    : "",
     "www-host-doc-root"           : "",
@@ -275,6 +276,7 @@ string program_name = "spine";
     "odt",                        "open document format text",                                      &opts["odt"],
     "output",                     "=/path/to/output/dir specify where to place output",             &settings["output"],
     "ssp-round-trip",             "=/path/to/file.ssp read a .ssp back and re-emit it on stdout",   &settings["ssp-round-trip"],
+    "db-round-trip",              "=/path/to/file.abstraction.db read it back and emit .ssp on stdout", &settings["db-round-trip"],
     "parallel",                   "parallelisation",                                                &opts["parallel"],
     "parallel-subprocesses",      "nested parallelisation",                                         &opts["parallel-subprocesses"],
     "pdf",                        "latex output for pdfs",                                          &opts["pdf"],
@@ -397,6 +399,45 @@ string program_name = "spine";
     stdout.flush;
     exit(0);
   }
+  /+ ↓ read a <doc>.abstraction.db back into the abstraction and emit it as
+       .ssp on stdout. compared against the .ssp for the same document, this
+       says whether the two artefacts really do carry the same thing
+  +/
+  if (settings["db-round-trip"].length > 0) {
+    import sisudoc.ocda.abstraction.ssp;
+    import sisudoc.ocda.abstraction.db_in;
+    mixin spineAbstractionDbRead;
+    mixin sspObjectRecord;
+    auto _doc = dbReadFile(settings["db-round-trip"]);
+    string[] _out;
+    if (_doc.format.length > 0) { _out ~= _doc.format; }
+    if (_doc.source.length > 0) { _out ~= "% Source: " ~ _doc.source; }
+    _out ~= "";
+    void _blockdb(string _name, string[] _keys, string[string] _kv) {
+      _out ~= "@" ~ _name ~ " {";
+      foreach (k; _keys) {
+        if (k in _kv) { _out ~= "  " ~ k ~ ": " ~ _kv[k]; }
+      }
+      _out ~= "}";
+      _out ~= "";
+    }
+    _blockdb("meta", _doc.meta_order, _doc.meta);
+    _blockdb("make", _doc.make_order, _doc.make);
+    _blockdb("doc_has", _doc.doc_has_order, _doc.doc_has);
+    foreach (section; _doc.section_order) {
+      _out ~= "@" ~ section ~ " {";
+      _out ~= "";
+      foreach (obj; _doc.abstraction[section]) {
+        _out ~= sspObjectRecord(obj, section);
+      }
+      _out ~= "}";
+      _out ~= "";
+    }
+    foreach (line; _out) { writeln(line); }
+    import core.stdc.stdlib : exit;
+    stdout.flush;
+    exit(0);
+  }
   enum outTask { source_or_pod, sqlite, sqlite_multi, latex, odt, epub, html_scroll, html_seg, html_stuff, text, skel }
   struct OptActions {
     @trusted bool allow_downloads() {