path: root/src/sisudoc/outputs/io_out/sqlite_ocda_db.d

/+
- 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.outputs.io_out.sqlite_ocda_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;
  import std.string;
  import std.array;
  import d2sqlite3;
  import sisudoc.outputs.io_out.paths_output;
  import sisudoc.ocda.io_in.paths_source;
  import sisudoc.ocda.abstraction.ssp : ssp_format_version;
  /+ ↓ the document is passed in rather than taken from doc, because the
       caller hands over one that has been through the .ssp: written as text
       and read back. so the database is a function of the .ssp and cannot
       carry anything the .ssp does not, header blocks included: the metadata
       table below is filled from what came back, not from a second list read
       off doc_matters, which is how the two drifted apart before
  +/
  void spineAbstractionDb(D,S)(D doc, S ssp_doc) {
    auto doc_matters     = doc.matters;
    auto doc_abstraction = ssp_doc.abstraction;
    /+ ↓ output path: beside the pod's zip and digests, not under
         <lang>/abstraction/. the database carries its own images, so it is a
         distributable artefact in its own right and belongs with the others:
           pod/<doc>/            the source tree
           pod/<doc>.zip         the same, zipped
           pod/<doc>.digests.txt what is in them
           pod/<uid>.ocda.db     the abstraction, one per language
    +/
    auto pths_pod = spinePathsPods!()(doc_matters);
    string base_pth = pths_pod.pod_dir_();
    try {
      if (!exists(base_pth)) {
        base_pth.mkdirRecurse;
      }
    } catch (Exception ex) {
    }
    string db_file = ((base_pth.chainPath(
      doc_matters.src.doc_uid_out ~ ".ocda.db")).asNormalizedPath).array;

    /+ ↓ remove existing file to start fresh +/
    try {
      if (exists(db_file)) {
        remove(db_file);
      }
    } catch (Exception ex) {
    }

    if (doc_matters.opt.action.vox_gt_1) {
      writeln(" ", db_file);
    }

    /+ ↓ open database and create schema +/
    auto db = Database(db_file);
    /+ ↓ write once, read many: no WAL.
         a WAL stamped file cannot be opened by a reader that cannot write
         beside it (SQLITE_READONLY on a read-only directory, and inside a
         zip or on read-only media), and this database has one writer and no
         concurrency, so WAL buys nothing here. MEMORY journal keeps the bulk
         load fast without leaving that mark on the file.
    +/
    db.run("PRAGMA journal_mode=MEMORY");
    db.run("PRAGMA synchronous=OFF");

    db.run("
      CREATE TABLE metadata (
        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,
        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,
        segment_html_is       TEXT,
        segment_epub_is       TEXT,
        segment_lv4_is        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);
      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)
        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);

      -- 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(
        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
        SELECT role, name, bytes, sha256, width, height FROM files ORDER BY role, name;
    ");

    /+ ↓ populate metadata, from the header blocks the .ssp gave back.
         one list, in ssp.d, not two: a property added to the .ssp header
         arrives here without anything being changed, and one that is not in
         the .ssp cannot be here at all. the keys are the .ssp's own
         (title.main, make.doc_type, doc_has.images), prefixed by their block
         where the block name is not already part of the key.
    +/
    db.run("BEGIN TRANSACTION");

    auto meta_stmt = db.prepare(
      "INSERT INTO metadata (key, value) VALUES (:key, :value)"
    );

    void insertMeta(string key, string value) {
      if (value.length > 0) {
        meta_stmt.bind(":key", key);
        meta_stmt.bind(":value", value);
        meta_stmt.execute();
        meta_stmt.reset();
      }
    }
    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]); }
      }
    }

    /+ ↓ what this file is, so a reader can tell before it starts. the
         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);
    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
         hold its own hash, so this one is not in the .ssp and is taken here:
         source.digest says which markup the abstraction came from,
         source.ssp_digest says which abstraction this database came from,
         and the chain .sst -> .ssp -> .ocda.db is checkable end to end
    +/
    insertMeta("source.ssp_digest", ssp_doc.ssp_digest);

    insertBlock("",         ssp_doc.meta_order,    ssp_doc.meta);
    insertBlock("make.",    ssp_doc.make_order,    ssp_doc.make);
    insertBlock("doc_has.", ssp_doc.doc_has_order, ssp_doc.doc_has);
    meta_stmt.finalize();

    /+ ↓ populate objects +/
    auto obj_stmt = db.prepare(
      "INSERT INTO objects ("
      ~ "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, has_images_no_dim,"
      ~ "segment, segment_prev, segment_next, segment_epub,"
      ~ "segment_html_is, segment_epub_is, segment_lv4_is,"
      ~ "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_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, :has_images_no_dim,"
      ~ ":segment, :segment_prev, :segment_next, :segment_epub,"
      ~ ":segment_html_is, :segment_epub_is, :segment_lv4_is,"
      ~ ":anchor, :heading_lev_anchor, :heading_ancestors_text,"
      ~ ":table_cols, :table_widths, :table_aligns, :table_header,"
      ~ ":code_linenumbers, :text"
      ~ ")"
    );

    /+ ↓ 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", "tail"];

    foreach (section; section_order) {
      if (section !in doc_abstraction) continue;
      auto section_objs = doc_abstraction[section];
      if (section_objs.length == 0) continue;

      foreach (seq, obj; section_objs) {
        obj_stmt.bind(":section", section);
        obj_stmt.bind(":seq", cast(int) seq);
        obj_stmt.bind(":ocn", obj.metainfo.ocn);
        obj_stmt.bind(":is_a", obj.metainfo.is_a);

        /+ ↓ nullable string fields +/
        void bindStr(string param, string val) {
          import std.typecons : Nullable;
          if (val.length > 0) {
            obj_stmt.bind(param, val);
          } else {
            obj_stmt.bind(param, Nullable!string());
          }
        }
        /+ ↓ nullable int fields +/
        void bindInt(string param, int val, bool is_set) {
          import std.typecons : Nullable;
          if (is_set) {
            obj_stmt.bind(param, val);
          } else {
            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);

        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 +/
        {
          import std.typecons : Nullable;
          bool has_sha = false;
          foreach (b; obj.metainfo.sha256.text) {
            if (b != 0) { has_sha = true; break; }
          }
          if (has_sha) {
            obj_stmt.bind(":sha256", obj.metainfo.sha256.text.toHexString.to!string);
          } else {
            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(":indent_base", obj.attrib.indent_base);
        obj_stmt.bind(":indent_hang", obj.attrib.indent_hang);
        obj_stmt.bind(":bullet", obj.attrib.bullet ? 1 : 0);
        bindStr(":lang", obj.attrib.language);
        obj_stmt.bind(":has_links", obj.has.inline_links ? 1 : 0);
        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(":segment_html_is", obj.tags.html_segment_anchor_tag_is);
        bindStr(":segment_epub_is", obj.tags.epub_segment_anchor_tag_is);
        bindStr(":segment_lv4_is", obj.tags.segment_lv4_is);
        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 +/
        {
          import std.typecons : Nullable;
          if (obj.metainfo.is_a == "table" && obj.table.number_of_columns > 0) {
            obj_stmt.bind(":table_cols", obj.table.number_of_columns);
            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(",") ~ "]");
            } 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 +/
        obj_stmt.bind(":code_linenumbers",
          ((obj.metainfo.is_a == "code") && obj.code_block.linenumbers) ? 1 : 0);

        /+ ↓ text content +/
        bindStr(":text", obj.text);

        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();
    }

    /+ ↓ 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");

    db.run("COMMIT TRANSACTION");
  }
}