-*- mode: org -*-
#+TITLE:       spine (doc_reform) output sqlite
#+DESCRIPTION: documents - structuring, publishing in multiple formats & search
#+FILETAGS:    :spine:output:db:sql:sqlite:
#+AUTHOR:      Ralph Amissah
#+EMAIL:       [[mailto:ralph.amissah@gmail.com][ralph.amissah@gmail.com]]
#+COPYRIGHT:   Copyright (C) 2015 - 2020 Ralph Amissah
#+LANGUAGE:    en
#+STARTUP:     content hideblocks hidestars noindent entitiespretty
#+OPTIONS:     H:3 num:nil toc:t \n:nil @:t ::t |:t ^:nil _:nil -:t f:t *:t <:t
#+PROPERTY:    header-args  :exports code
#+PROPERTY:    header-args+ :noweb yes
#+PROPERTY:    header-args+ :eval no
#+PROPERTY:    header-args+ :results no
#+PROPERTY:    header-args+ :cache no
#+PROPERTY:    header-args+ :padline no

- [[./spine.org][spine]]  [[./][org/]]
- [[./output_hub.org][output_hub]]

sudo chown ralph:ralph /var/www

- create db
  ~dr/bin/spine-ldc -v \
    --sqlite-db-create --sqlite-db-filename="spine.search.db" \
    --output=/var/www/html \
    ~grotto/repo/git.repo/code/project-spine/doc-reform-markup/markup_samples/markup/pod/*

- update db
  ~dr/bin/spine-ldc -v \
    --sqlite-update --sqlite-db-filename="spine.search.db" \
    --output=/var/www/html \
    ~grotto/repo/git.repo/code/project-spine/doc-reform-markup/markup_samples/markup/pod/*

- produce html (and some other) output files
  ~dr/bin/spine-ldc -v --html --epub --latex --odt \
    --output=/var/www \
    ~grotto/repo/git.repo/code/project-spine/doc-reform-markup/markup_samples/markup/pod/*

* sql
** _module, templates_                                                :module:
*** template

#+BEGIN_SRC d :tangle "../src/doc_reform/io_out/sqlite.d"
module doc_reform.io_out.sqlite;
<<sqlite_imports>>
<<sqlite_common>>
<<sqlite_collection>>
<<sqlite_discrete>>
<<sqlite_run>>
<<sqlite_munge>>
<<sqlite_create>>
<<sqlite_delete>>
<<sqlite_insert>>
<<sqlite_insert_doc_objects_loop>>
<<sqlite_tables_create>>
<<sqlite_tables_drop>>
#+END_SRC

*** hub
**** common

#+NAME: sqlite_common
#+BEGIN_SRC d
mixin spineRgxOut;
mixin InternalMarkup;
static auto rgx = RgxO();
static auto mkup = InlineMarkup();
long _metadata_tid_lastrowid;
#+END_SRC

**** collection

#+NAME: sqlite_collection
#+BEGIN_SRC d
template SQLiteHubBuildTablesAndPopulate() {
  void SQLiteHubBuildTablesAndPopulate(D,M)(
    const D  doc_abstraction,
          M  doc_matters,
  ) {
    auto pth_sqlite = spinePathsSQLite!()(doc_matters.sqlite.filename, doc_matters.output_path);
    pth_sqlite.base.mkdirRecurse;
    auto db = Database(pth_sqlite.sqlite_file);
    template SQLiteDbStatementComposite() {
      void SQLiteDbStatementComposite(Db,D,M)(
              Db   db,
        const D    doc_abstraction,
              M    doc_matters,
      ) {
        <<sqlite_db_statement_composite_collection>>
        if (!(doc_matters.opt.action.quiet)) {
          writeln(" ", pth_sqlite.sqlite_file);
        }
      }
    }
    SQLiteDbStatementComposite!()(db, doc_abstraction, doc_matters);
  }
}
#+END_SRC

**** discrete

#+NAME: sqlite_discrete
#+BEGIN_SRC d
template SQLiteHubDiscreteBuildTablesAndPopulate() {
  void SQLiteHubDiscreteBuildTablesAndPopulate(D,M)(
    const D    doc_abstraction,
          M    doc_matters,
  ) {
    auto url_html = spineUrlsHTML!()(doc_matters.conf_make_meta.conf.w_srv_data_root_url_html, doc_matters.src.language);
    auto pth_sqlite = spinePathsSQLiteDiscrete!()(doc_matters.output_path, doc_matters.src.language);
    pth_sqlite.base.mkdirRecurse;
    auto db = Database(pth_sqlite.sqlite_file(doc_matters.src.filename));
    template SQLiteDiscreteDbStatementComposite() {
      void SQLiteDiscreteDbStatementComposite(Db,D,M)(
              Db   db,
        const D    doc_abstraction,
              M    doc_matters,
      ) {
        <<sqlite_db_statement_composite_discrete>>
        if (!(doc_matters.opt.action.quiet)) {
          writeln(" ", pth_sqlite.sqlite_file(doc_matters.src.filename));
        }
      }
    }
    SQLiteDiscreteDbStatementComposite!()(db, doc_abstraction, doc_matters);
  }
}
#+END_SRC

*** db run

#+NAME: sqlite_run
#+BEGIN_SRC d
template SQLiteDbRun() {
  void SQLiteDbRun(Db,St,O)(
    Db       db,
    St       db_statement,
    O        opt_action,
    string   note,
  ) {
    debug(sql_statement) {
      writeln(db_statement);
    }
    try {
      db.run(
        "\nBEGIN;\n" ~
        db_statement ~
        "\nCOMMIT;\n"
      );
    } catch (ErrnoException ex) {
      writeln("ERROR SQLite : ", ex);
    } catch (Exception ex) {
      writeln("ERROR SQLite : ", ex);
    }
    { /+ debug +/
      if (opt_action.debug_do
      && opt_action.verbose) {
        writeln(note);
        if (opt_action.very_verbose) {
          writeln(db_statement);
        }
      }
    }
  }
}
#+END_SRC

*** munge

#+NAME: sqlite_munge
#+BEGIN_SRC d
template SQLinsertDelimiter() {
  string SQLinsertDelimiter(string _txt) {
    _txt = _txt
      .replaceAll(rgx.quotation_mark_sql_insert_delimiter, "$0$0");
    return _txt;
  }
}
template SQLiteFormatAndLoadObject() {
  auto SQLiteFormatAndLoadObject(M)(
    M    doc_matters,
  ) {
    mixin spineRgxOut;
    struct sqlite_format_and_load_objects {
      <<sanitize_text_for_search>>
      <<sanitize_and_munge_inline_html_0>>
      <<sanitize_and_munge_inline_html_1>>
      <<sanitize_and_munge_inline_html_2>>
      <<sanitize_and_munge_inline_html_3>>
      <<sanitize_and_munge_inline_html_4>>
      <<sanitize_and_munge_inline_html_5>>
      <<sanitize_and_munge_inline_html_6>>
      <<sanitize_and_munge_inline_html_7>>
      <<html_objects_0>>
      <<html_objects_1>>
      <<html_objects_2>>
      <<html_objects_3>>
      <<html_objects_4>>
      <<html_objects_5>>
      <<html_objects_6>>
      <<html_objects_7>>
      <<sqlite_load_object_0>>
      <<sqlite_load_object_1>>
      <<sqlite_load_object_2>>
      <<hub_format_and_sqlite_load_objects_0>>
      <<hub_format_and_sqlite_load_objects_1>>
      <<hub_format_and_sqlite_load_objects_2>>
      <<hub_format_and_sqlite_load_objects_3>>
      <<hub_format_and_sqlite_load_objects_4>>
      <<hub_format_and_sqlite_load_objects_5>>
      <<hub_format_and_sqlite_load_objects_6>>
      <<hub_format_and_sqlite_load_objects_7>>
    }
    return sqlite_format_and_load_objects();
  }
}
#+END_SRC

*** sqlite instructions
**** create

#+NAME: sqlite_create
#+BEGIN_SRC d
template SQLiteTablesReCreate() {
  string SQLiteTablesReCreate()() {
    string _sql_instruct;
    _sql_instruct = format(q"┃
      <<sqlite_statement_drop_existing_index>>
      <<sqlite_statement_drop_existing_tables>>
      <<sqlite_statement_create_table_metadata_and_src_txt>>
      <<sqlite_statement_create_table_topic_register>>
      <<sqlite_statement_create_table_objects>>
      <<sqlite_statement_create_index>>
    ┃",);
    return _sql_instruct;
  }
}
#+END_SRC

**** delete

#+NAME: sqlite_delete
#+BEGIN_SRC d
template SQLiteDeleteDocument() {
  string SQLiteDeleteDocument(M)(
    M doc_matters,
  ) {
    <<sqlite_formatted_delete_0>>
    <<sqlite_formatted_delete_1>>
    <<sqlite_formatted_delete_2>>
    return _delete_uid;
  }
}
#+END_SRC

**** insert

#+NAME: sqlite_insert
#+BEGIN_SRC d
template SQLiteInsertMetadata() {
  string SQLiteInsertMetadata(M)(
    M doc_matters,
  ) {
    <<sqlite_formatted_insertions_doc_matters_metadata_0>>
    <<sqlite_formatted_insertions_doc_matters_metadata_1>>
    <<sqlite_formatted_insertions_doc_matters_metadata_2>>
    <<sqlite_formatted_insertions_doc_matters_metadata_3>>
    <<sqlite_formatted_insertions_topic_register_0>>
    <<sqlite_formatted_insertions_topic_register_1>>
    <<sqlite_formatted_insertions_topic_register_2>>
    <<sqlite_formatted_insertions_topic_register_3>>
    <<sqlite_formatted_insertions_topic_register_4>>
    <<sqlite_formatted_insertions_topic_register_5>>
    return _insert_metadata;
  }
}
#+END_SRC

**** insert doc objects loop

#+NAME: sqlite_insert_doc_objects_loop
#+BEGIN_SRC d
template SQLiteInsertDocObjectsLoop() {
  string SQLiteInsertDocObjectsLoop(D,M)(
    const D    doc_abstraction,
          M    doc_matters,
  ) {
    string _uid = SQLinsertDelimiter!()(doc_matters.src.doc_uid);
    auto url_html = spineUrlsHTML!()(doc_matters.conf_make_meta.conf.w_srv_data_root_url_html, doc_matters.src.language);
    string insertDocObjectsRow(O)(O obj) {
      <<sqlite_formatted_insertions_doc_objects_0>>
      <<sqlite_formatted_insertions_doc_objects_1>>
      <<sqlite_formatted_insertions_doc_objects_2>>
      <<sqlite_formatted_insertions_doc_objects_3>>
      return _insert_doc_objects_row;
    }
    <<sqlite_objects_loop>>
  }
}
#+END_SRC

**** tables create

#+NAME: sqlite_tables_create
#+BEGIN_SRC d
template SQLiteTablesCreate() {
  void SQLiteTablesCreate(E,O,C)(E env, O opt_action, C config) {
    import d2sqlite3;
    template SQLiteTablesReCreate() {
      string SQLiteTablesReCreate()() {
        string _sql_instruct;
        _sql_instruct = format(q"┃
          <<sqlite_statement_drop_existing_index>>
          <<sqlite_statement_drop_existing_tables>>
          <<sqlite_statement_create_table_metadata_and_src_txt>>
          <<sqlite_statement_create_table_topic_register>>
          <<sqlite_statement_create_table_objects>>
          <<sqlite_statement_create_index>>
        ┃",);
        return _sql_instruct;
      }
    }
    if (opt_action.sqlite_db_create) {
      string _db_statement;
      string db_filename = (opt_action.sqlite_filename.length > 0)
      ? opt_action.sqlite_filename
      : (config.conf.w_srv_db_sqlite.length > 0)
        ? config.conf.w_srv_db_sqlite
        : "";
      string output_path = (opt_action.output_dir_set.length > 0)
      ? opt_action.output_dir_set
      : (config.conf.output_path.length > 0)
        ? config.conf.output_path
        : "";
      if (db_filename.length > 0 && output_path.length > 0) {
        if ((opt_action.verbose)) {
          writeln("db name & path: ", config.conf.output_path, "/sqlite/", db_filename);
        }
        auto pth_sqlite = spinePathsSQLite!()(db_filename, output_path);
        pth_sqlite.base.mkdirRecurse;
        auto db = Database(pth_sqlite.sqlite_file);
        {
          _db_statement ~= SQLiteTablesReCreate!()();
        }
        SQLiteDbRun!()(db, _db_statement, opt_action, "TABLE RE-CREATE");
      } else {
        writeln("must provide db name & output root path either on the command line or in configuration file");
        writeln("db name: ", db_filename);
        writeln("output root path (path less /sqlite which is added: ", config.conf.output_path);
      }
    }
  }
}
#+END_SRC

**** tables drop

#+NAME: sqlite_tables_drop
#+BEGIN_SRC d
template SQLiteDbDrop() {
  void SQLiteDbDrop(O,C)(O opt_action, C config) {
    writeln("db drop");
    if ((opt_action.sqlite_db_drop)) {
      string db_filename = (opt_action.sqlite_filename.length > 0)
      ? opt_action.sqlite_filename
      : (config.conf.w_srv_db_sqlite.length > 0)
        ? config.conf.w_srv_db_sqlite
        : "";
      string output_path = (opt_action.output_dir_set.length > 0)
      ? opt_action.output_dir_set
      : (config.conf.output_path.length > 0)
        ? config.conf.output_path
        : "";
      if (db_filename.length > 0 && output_path.length > 0) {
        auto pth_sqlite = spinePathsSQLite!()(db_filename, output_path);
        writeln("remove(", pth_sqlite.sqlite_file, ")");
        try {
          remove(pth_sqlite.sqlite_file);
        } catch (FileException ex) {
          // handle error
        }
      } else {
        writeln("must provide db name & output root path either on the command line or in configuration file");
        writeln("db name: ", db_filename);
        writeln("output root path (path less /sqlite which is added: ", config.conf.output_path);
      }
    }
  }
}
#+END_SRC

** 1. [#A] sqlite_db_statement                                   :statement:
*** collection

#+NAME: sqlite_db_statement_composite_collection
#+BEGIN_SRC d
{
  {
    string _db_statement;
    if ((doc_matters.opt.action.sqlite_db_create)) {
      auto pth_sqlite = spinePathsSQLite!()(doc_matters.sqlite.filename, doc_matters.output_path);
      pth_sqlite.base.mkdirRecurse;
      _db_statement ~= SQLiteTablesReCreate!()();
      SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "TABLE RE-CREATE");
    }
    if (doc_matters.opt.action.sqlite_delete) {
      _db_statement ~= SQLiteDeleteDocument!()(doc_matters);
      SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "DELETE Document");
    }
    if (doc_matters.opt.action.sqlite_update) {
      _db_statement ~= SQLiteDeleteDocument!()(doc_matters);
      SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "DELETE Document");
      _db_statement ~= SQLiteInsertMetadata!()(doc_matters);
      SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "INSERT MetaData");
      /+ get tid (lastrowid or max) for use in doc_objects table +/
      _db_statement ~= doc_abstraction.SQLiteInsertDocObjectsLoop!()(doc_matters);
      SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "INSERT DocObjects");
    }
  }
  db.close;
}
#+END_SRC

*** discrete

#+NAME: sqlite_db_statement_composite_discrete
#+BEGIN_SRC d
{
  {
    string _db_statement;
    _db_statement ~= SQLiteTablesReCreate!()();
    _db_statement ~= SQLiteInsertMetadata!()(doc_matters);
    _db_statement ~= doc_abstraction.SQLiteInsertDocObjectsLoop!()(doc_matters);
    SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "table CREATE Tables, INSERT DocObjects");
  }
  db.close;
}
#+END_SRC

** 2. imports

#+NAME: sqlite_imports
#+BEGIN_SRC d
import doc_reform.io_out;
import
  std.file,
  std.uri;
import std.conv : to;
import std.typecons : Nullable;
import d2sqlite3;
#+END_SRC

** 3. format and load template
*** 1. prepare objects (munge, sanitize, markup)
**** 1. _text_ generic munge (sanitize text for search)

- [3/4] (search text, applies to all but code blocks)
  - [ ] remove whitespace, paragraph on single line (formatting kept so far)
  - [X] remove font face attributes
  - [X] move embedded endnotes
    - [X] place after text object
    - [X] remove embedded endnote numbers (rely on html output to represent)
  - [X] urls
    - [X] clean url markers, leave plain link text
    - [X] place urls after text object and its endnotes

#+NAME: sanitize_text_for_search
#+BEGIN_SRC d
string generic_munge_sanitize_text_for_search(
  string _txt,
) {
  string _notes;
  string _urls;
  if (_txt.matchFirst(rgx.inline_notes_al_gen)) {
    foreach (m; _txt.matchAll(rgx.inline_notes_al_gen_text)) {
      _notes ~= "\n" ~ m["text"];
    }
    _txt = _txt.replaceAll(rgx.inline_notes_al_gen, "");
  }
  if (_txt.matchFirst(rgx.inline_link)) {
    foreach (m; _txt.matchAll(rgx.inline_link)) {
      if (m["link"].match(rgx.url)) {
        _urls ~= "\n" ~ m["link"];
      }
    }
    _txt = _txt.replaceAll(rgx.inline_link_clean, "");
  }
  if (_notes.length > 0) {
    _txt ~= _notes;
  }
  if (_urls.length > 0) {
    _txt ~= _urls;
  }
  { /+ debug +/
    if (doc_matters.opt.action.debug_do
    && doc_matters.opt.action.verbose) {
      writeln(_txt, "\n");
    }
  }
  debug(sql_text_clean) {
    writeln(_txt);
  }
  return _txt;
}
#+END_SRC

**** 2. _html_

light html objects
- place endnotes after text object
- light inline html markup

***** munge
****** general munge (special characters, inline markup, move notes)

#+NAME: sanitize_and_munge_inline_html_0
#+BEGIN_SRC d
string munge_html(M,O)(
        M    doc_matters,
  const O    obj,
) {
  string _html_special_characters(string _txt){
    _txt = _txt
      .replaceAll(rgx.xhtml_ampersand,    "&#38;")
      .replaceAll(rgx.xhtml_quotation,    "&#34;")
      .replaceAll(rgx.xhtml_less_than,    "&#60;")
      .replaceAll(rgx.xhtml_greater_than, "&#62;")
      .replaceAll(rgx.nbsp_char,          " ")
      .replaceAll(rgx.xhtml_line_break,   "<br />");
    return _txt;
  }
  string _html_font_face(string _txt){
    _txt = _txt
      .replaceAll(rgx.inline_emphasis,    "<em>$1</em>")
      .replaceAll(rgx.inline_bold,        "<b>$1</b>")
      .replaceAll(rgx.inline_underscore,  "<u>$1</u>")
      .replaceAll(rgx.inline_italics,     "<i>$1</i>")
      .replaceAll(rgx.inline_superscript, "<sup>$1</sup>")
      .replaceAll(rgx.inline_subscript,   "<sub>$1</sub>")
      .replaceAll(rgx.inline_strike,      "<del>$1</del>")
      .replaceAll(rgx.inline_insert,      "<ins>$1</ins>")
      .replaceAll(rgx.inline_mono,        "<tt>$1</tt>")
      .replaceAll(rgx.inline_cite,        "<cite>$1</cite>");
    return _txt;
  }
  string _notes;
  string _urls;
  string _txt = _html_font_face(_html_special_characters(obj.text));
  { /+ debug +/
    if (doc_matters.opt.action.debug_do
    && doc_matters.opt.action.verbose) {
      writeln(_txt, "\n");
    }
  }
  return _txt;
}
#+END_SRC

****** special characters

#+NAME: sanitize_and_munge_inline_html_1
#+BEGIN_SRC d
string html_special_characters(string _txt){
  _txt = _txt
    .replaceAll(rgx.xhtml_ampersand,    "&#38;")
    .replaceAll(rgx.xhtml_quotation,    "&#34;")
    .replaceAll(rgx.xhtml_less_than,    "&#60;")
    .replaceAll(rgx.xhtml_greater_than, "&#62;")
    .replaceAll(rgx.nbsp_char,          " ")
    .replaceAll(rgx.xhtml_line_break,   "<br />");
  return _txt;
}
#+END_SRC

****** special characters for code

#+NAME: sanitize_and_munge_inline_html_2
#+BEGIN_SRC d
string html_special_characters_code(string _txt){
  _txt = _txt
    .replaceAll(rgx.xhtml_ampersand,    "&#38;")
    .replaceAll(rgx.xhtml_quotation,    "&#34;")
    .replaceAll(rgx.xhtml_less_than,    "&#60;")
    .replaceAll(rgx.xhtml_greater_than, "&#62;")
    .replaceAll(rgx.nbsp_char,          " ");
  return _txt;
}
#+END_SRC

****** font_face

#+NAME: sanitize_and_munge_inline_html_3
#+BEGIN_SRC d
string html_font_face(string _txt){
  _txt = _txt
    .replaceAll(rgx.inline_emphasis,    "<em>$1</em>")
    .replaceAll(rgx.inline_bold,        "<b>$1</b>")
    .replaceAll(rgx.inline_underscore,  "<u>$1</u>")
    .replaceAll(rgx.inline_italics,     "<i>$1</i>")
    .replaceAll(rgx.inline_superscript, "<sup>$1</sup>")
    .replaceAll(rgx.inline_subscript,   "<sub>$1</sub>")
    .replaceAll(rgx.inline_strike,      "<del>$1</del>")
    .replaceAll(rgx.inline_insert,      "<ins>$1</ins>")
    .replaceAll(rgx.inline_mono,        "<tt>$1</tt>")
    .replaceAll(rgx.inline_cite,        "<cite>$1</cite>");
  return _txt;
}
#+END_SRC

****** inline markup
******* images

#+NAME: sanitize_and_munge_inline_html_4
#+BEGIN_SRC d
string inline_images(M,O)(
               M  doc_matters,
  const        O  obj,
  string          _txt,
  string          _suffix   = ".html",
  string          _xml_type = "seg",
) {
  string _img_pth;
  if (_xml_type == "epub") {
    _img_pth = "image/";
  } else if (_xml_type == "scroll") {
    _img_pth = "../../image/";
  } else if (_xml_type == "seg") {
    _img_pth = "../../../image/";
  }
  if (_txt.match(rgx.inline_image)) {
    _txt = _txt.replaceAll( // TODO bug where image dimensions (w or h) not given & consequently set to 0; should not be used (calculate earlier, abstraction)
        rgx.inline_image,
        ("$1<img src=\""
          ~ _img_pth
          ~ "$3\" width=\"$4\" height=\"$5\" naturalsizeflag=\"0\" align=\"bottom\" border=\"0\" /> $6"));
  }
  return _txt;
}
#+END_SRC

******* links
******** scroll, seg, epub

#+NAME: sanitize_and_munge_inline_html_5
#+BEGIN_SRC d
string inline_links(M,O)(
               M doc_matters,
  const        O obj,
  string         _txt,
  string         _xml_type = "seg",
) {
  if (obj.has.inline_links) {
    if  (obj.metainfo.is_a != "code") {
      _txt = replaceAll!(m =>
          m["linked_text"] ~ "┤" ~ to!string((obj.stow.link[m["num"].to!ulong])).encode ~ "├"
        )(_txt, rgx.inline_link_number_only);
    }
    if ((_txt.match(rgx.mark_internal_site_lnk))
    && (_xml_type == "scroll")) { // conditions reversed to avoid: gdc compiled program run segfault
      _txt = _txt.replaceAll(
        rgx.inline_seg_link,
        "$1");
    }
    auto pth_html = spinePathsHTML!()(doc_matters.output_path, doc_matters.src.language);
    if (_xml_type == "seg") {
      foreach (m; _txt.matchAll(rgx.inline_link_seg_and_hash)) {
        if (m.captures["hash"] in doc_matters.has.tag_associations) {
          if (m.captures["hash"] == doc_matters.has.tag_associations[(m.captures["hash"])]["seg_lv4"]) {
            _txt = _txt.replaceFirst(
              rgx.inline_link_seg_and_hash,
              "┥$1┝┤"
                ~ doc_matters.conf_make_meta.conf.w_srv_data_root_url_html
                ~ "/"
                ~ pth_html.tail_fn_seg(doc_matters.src.filename, "$2.html")
              ~ "├"
            );
          } else {
            _txt = _txt.replaceFirst(
              rgx.inline_link_seg_and_hash,
              "┥$1┝┤"
                ~ doc_matters.conf_make_meta.conf.w_srv_data_root_url_html
                ~ "/"
                ~ doc_matters.src.filename_base
                ~ "/"
                ~ doc_matters.has.tag_associations[(m.captures["hash"])]["seg_lv4"]
                ~ ".html"
                ~ "#" ~ m.captures["hash"]
              ~ "├"
            );
          }
        } else {
          if (!(doc_matters.opt.action.quiet)) {
            writeln(
              "WARNING on internal document links, anchor to link <<"
               ~ m.captures["hash"]
               ~ ">> not found in document, "
               ~ "anchor: " ~ m.captures["hash"]
               ~ " document: " ~ doc_matters.src.filename
            );
          }
        }
      }
    } else {
      if (auto m = _txt.match(rgx.inline_link_seg_and_hash)) {
        _txt = _txt.replaceFirst(
          rgx.inline_link_seg_and_hash,
          "┥$1┝┤"
            ~ doc_matters.conf_make_meta.conf.w_srv_data_root_url_html
            ~ "/"
            ~ pth_html.tail_fn_scroll(doc_matters.src.filename)
            ~ "#" ~ m.captures["hash"]
          ~ "├"
        );
      }
    }
    _txt = _txt
      .replaceAll(
        rgx.inline_link_fn_suffix,
        ("$1.html"))
      .replaceAll(
        rgx.inline_link,
        ("<a href=\"$2\">$1</a>"))
      .replaceAll(
        rgx.mark_internal_site_lnk,
        "");
  }
  debug(markup_links) {
    if (_txt.match(rgx.inline_link)) {
      writeln(__LINE__,
        " (missed) markup link identified (",
        obj.has.inline_links,
        "): ", obj.metainfo.is_a, ": ",
        obj.text
      );
    }
  }
  debug(markup) {
    if (_txt.match(rgx.inline_link)) {
      writeln(__LINE__,
        " (missed) markup link identified (",
        obj.has.inline_links,
        "): ", obj.metainfo.is_a, ": ",
        obj.text
      );
    }
  }
  return _txt;
}
#+END_SRC

******* notes
******** scroll

#+NAME: sanitize_and_munge_inline_html_6
#+BEGIN_SRC d
string inline_notes_scroll(M,O)(
               M   doc_matters,
  const        O   obj,
  string           _txt,
) {
  if (obj.has.inline_notes_reg) {
    string[] _endnotes;
    foreach(m; _txt.matchAll(rgx.inline_notes_al_regular_number_note)) {
      _endnotes ~= "<p class=\"endnote\">"
      ~ "<sup>" ~ m.captures["num"] ~ ".</sup>"
      ~ m.captures["note"]
      ~ "</p>";
    }
    _txt = replaceAll!(m =>
      ("&#160;" ~ "<sup>" ~ m["num"] ~ "</sup>"))
        (_txt, rgx.inline_notes_al_regular_number_note)
      ~ _endnotes.join("\n");
  }
  debug(markup_endnotes) {
    if (_txt.match(rgx.inline_notes_al_regular_number_note)) {
      writeln(__LINE__, " (missed) markup endnote: ", obj.metainfo.is_a, ": ", obj.text);
    }
  }
  debug(markup) {
    if (_txt.match(rgx.inline_notes_al_regular_number_note)) {
      writeln(__LINE__, " (missed) markup endnote: ", obj.metainfo.is_a, ": ", obj.text);
    }
  }
  return _txt;
}
#+END_SRC

******* inline markup

#+NAME: sanitize_and_munge_inline_html_7
#+BEGIN_SRC d
string xml_type="seg"; /+ set html document type to be linked to here (seg|scroll) +/
string inline_markup(M,O)(
               M  doc_matters,
  const        O  obj,
  string          _txt,
) {
  _txt = inline_images(doc_matters, obj, _txt, xml_type);
  _txt = inline_links(doc_matters, obj, _txt, xml_type);
  _txt = inline_notes_scroll(doc_matters, obj, _txt);
  return _txt;
}
#+END_SRC

***** objects
****** heading

#+NAME: html_objects_0
#+BEGIN_SRC d
string html_heading(M,O)(
        M   doc_matters,
  const O   obj,
) {
  assert(obj.metainfo.is_of_part    == "body" || "frontmatter" || "backmatter");
  assert(obj.metainfo.is_of_section == "body" || "toc" || "endnotes" || "glossary" || "bibliography" || "bookindex" || "blurb");
  assert(obj.metainfo.is_of_type    == "para");
  assert(obj.metainfo.is_a          == "heading");
  string _txt = munge_html(doc_matters, obj);
  _txt = inline_markup(doc_matters, obj, _txt);
  string o = format(q"┃<p class="%s"><b>
      %s
    </b></p>┃",
      obj.metainfo.is_a,
      _txt,
    );
  return o;
}
#+END_SRC

******* +fancy+

##+NAME: prepare_objects_html
#+BEGIN_SRC d
string html_heading(M,O)(
        M   doc_matters,
  const O   obj,
) {
  string o;
  string _txt = munge_html(doc_matters, obj);
  o = format(q"┃<h%s class="%s">
      %s
    </h%s>┃",
      obj.metainfo.heading_lev_markup,
      obj.metainfo.is_a,
      _txt,
      obj.metainfo.heading_lev_markup,
    );
  return o;
}
#+END_SRC

****** para

#+NAME: html_objects_1
#+BEGIN_SRC d
string html_para(M,O)(
        M   doc_matters,
  const O   obj,
) {
  assert(obj.metainfo.is_of_part    == "body" || "frontmatter" || "backmatter");
  assert(obj.metainfo.is_of_section == "body" || "toc" || "endnotes" || "glossary" || "bibliography" || "bookindex" || "blurb");
  assert(obj.metainfo.is_of_type    == "para");
  assert(obj.metainfo.is_a          == "para" || "toc" || "endnote" || "glossary" || "bibliography" || "bookindex" || "blurb");
  string _txt = munge_html(doc_matters, obj);
  _txt = (obj.attrib.bullet) ? ("●&#160;&#160;" ~ _txt) : _txt;
  _txt = inline_markup(doc_matters, obj, _txt);
  string o = format(q"┃<p class="%s" indent="h%si%s">
    %s
  </p>┃",
      obj.metainfo.is_a,
      obj.attrib.indent_hang,
      obj.attrib.indent_base,
      _txt
    );
  return o;
}
#+END_SRC

****** quote

#+NAME: html_objects_2
#+BEGIN_SRC d
string html_quote(M,O)(
        M   doc_matters,
  const O   obj,
) {
  assert(obj.metainfo.is_of_part    == "body");
  assert(obj.metainfo.is_of_section == "body" || "glossary" || "bibliography" || "bookindex" || "blurb");
  assert(obj.metainfo.is_of_type    == "block");
  assert(obj.metainfo.is_a          == "quote");
  string _txt = munge_html(doc_matters, obj);
  string o = format(q"┃<p class="%s">
    %s
  </p>┃",
      obj.metainfo.is_a,
      _txt
    );
  return o;
}
#+END_SRC

****** group

#+NAME: html_objects_3
#+BEGIN_SRC d
string html_group(M,O)(
        M   doc_matters,
  const O   obj,
) {
  assert(obj.metainfo.is_of_part    == "body");
  assert(obj.metainfo.is_of_section == "body" || "glossary" || "bibliography" || "bookindex" || "blurb");
  assert(obj.metainfo.is_of_type    == "block");
  assert(obj.metainfo.is_a          == "group");
  string _txt = munge_html(doc_matters, obj);
  string o = format(q"┃<p class="%s">
    %s
  </p>┃",
      obj.metainfo.is_a,
      _txt
    );
  return o;
}
#+END_SRC

****** block

#+NAME: html_objects_4
#+BEGIN_SRC d
string html_block(M,O)(
        M   doc_matters,
  const O   obj,
) {
  assert(obj.metainfo.is_of_part    == "body");
  assert(obj.metainfo.is_of_section == "body" || "glossary" || "bibliography" || "bookindex" || "blurb");
  assert(obj.metainfo.is_of_type    == "block");
  assert(obj.metainfo.is_a          == "block");
  string _txt = munge_html(doc_matters, obj);
  string o = format(q"┃
  <p class="%s">%s</p>┃",
      obj.metainfo.is_a,
      _txt.stripRight
    );
  return o;
}
#+END_SRC

****** verse

#+NAME: html_objects_5
#+BEGIN_SRC d
string html_verse(M,O)(
        M   doc_matters,
  const O   obj,
) {
  assert(obj.metainfo.is_of_part    == "body");
  assert(obj.metainfo.is_of_section == "body" || "glossary" || "bibliography" || "bookindex" || "blurb");
  assert(obj.metainfo.is_of_type    == "block");
  assert(obj.metainfo.is_a          == "verse");
  string _txt = munge_html(doc_matters, obj);
  string o = format(q"┃<p class="%s">%s</p>┃",
      obj.metainfo.is_a,
      _txt
    );
  return o;
}
#+END_SRC

****** code

#+NAME: html_objects_6
#+BEGIN_SRC d
string html_code(O)(
  const O   obj,
) {
  assert(obj.metainfo.is_of_part    == "body");
  assert(obj.metainfo.is_of_section == "body");
  assert(obj.metainfo.is_of_type    == "block");
  assert(obj.metainfo.is_a          == "code");
  string _txt = html_special_characters_code(obj.text);
  string o = format(q"┃<p class="%s">%s</p>┃",
      obj.metainfo.is_a,
      _txt
    );
  return o;
}
#+END_SRC

****** table

#+NAME: html_objects_7
#+BEGIN_SRC d
string html_table(M,O)(
        M   doc_matters,
  const O   obj,
) {
  assert(obj.metainfo.is_of_part    == "body");
  assert(obj.metainfo.is_of_section == "body");
  assert(obj.metainfo.is_of_type    == "block");
  assert(obj.metainfo.is_a          == "table");
  Tuple!(string, string) _tablarize(O)(
    const O         obj,
          string    _txt,
  ) {
    string[] _table_rows = _txt.split(rgx.table_delimiter_row);
    string[] _table_cols;
    string _table;
    string _tablenote;
    foreach(row_idx, row; _table_rows) {
      _table_cols = row.split(rgx.table_delimiter_col);
        _table ~= "<tr>";
        foreach(col_idx, cell; _table_cols) {
          if ((_table_cols.length == 1)
          && (_table_rows.length <= row_idx+2)) { // check row_idx+2 (rather than == ++row_idx)
            _tablenote ~= cell;
          } else {
            string _col_is = (row_idx == 0 && obj.table.heading) ? "th" : "td";
            string _align = ("style=\"text-align:"
            ~ ((obj.table.column_aligns[col_idx] == "l")
            ? "left\"" : "right\""));
            _table ~= "<"
              ~ _col_is
              ~ " width=\""
              ~ obj.table.column_widths[col_idx].to!string
              ~ "%\" "
              ~ _align
              ~ ">";
            _table ~= cell;
            _table ~= "</"
              ~ _col_is
              ~ ">";
          }
        }
        _table ~= "</tr>";
      }
    Tuple!(string, string) t = tuple(
      _table,
      _tablenote,
    );
    return t;
  }
  string _txt = munge_html(doc_matters, obj);
  Tuple!(string, string) t = _tablarize(obj, _txt);
  _txt = t[0];
  string _note = t[1];
  string o = format(q"┃<p class="%s">
    <table summary="normal text css" width="95%%" border="0" cellpadding="2" align="center">
      %s
    </table>
    %s
  </p>┃",
    obj.metainfo.is_a,
    _txt,
    _note
  );
  return o;
}
#+END_SRC

*** 2. hub (sqlite_format_and_load_objects)
**** sql related

#+NAME: sqlite_load_object_0
#+BEGIN_SRC d
string sqlite_load_string(M,O)(
        M   doc_matters,
  const O   obj,
) {
  string o;
  return o;
}
#+END_SRC

#+NAME: sqlite_load_object_1
#+BEGIN_SRC d
string postgresql_load_string(M,O)(
        M   doc_matters,
  const O   obj,
) {
  string o;
  return o;
}
#+END_SRC

#+NAME: sqlite_load_object_2
#+BEGIN_SRC d
string sqlite_statement(O)(
  const O          obj,
        string     _txt,
        string     _html,
) {
  void _sql_exe(O)(
    string _sql,
  ) {
    writeln(_html);
    writeln(_sql);
  }
  string _sql;
  return _sql;
}
#+END_SRC

**** heading

#+NAME: hub_format_and_sqlite_load_objects_0
#+BEGIN_SRC d
string[string] heading(M,O)(
        M   doc_matters,
  const O   obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_heading(doc_matters, obj)
  ];
  { /+ debug +/
    if (doc_matters.opt.action.debug_do
    && doc_matters.opt.action.verbose) {
      debug(sql_txt) {
        writeln(obj_txt["text"]);
      }
      debug(sql_html) {
        writeln(obj_txt["html"]);
      }
    } else {
      // load sql
    }
  }
  return obj_txt;
}
#+END_SRC

**** para

#+NAME: hub_format_and_sqlite_load_objects_1
#+BEGIN_SRC d
string[string] para(M,O)(
        M   doc_matters,
  const O   obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_para(doc_matters, obj)
  ];
  { /+ debug +/
    if (doc_matters.opt.action.debug_do
    && doc_matters.opt.action.verbose) {
      debug(sql_txt) {
        writeln(obj_txt["text"]);
      }
      debug(sql_html) {
        writeln(obj_txt["html"]);
      }
    } else {
      // load sql
    }
  }
  return obj_txt;
}
#+END_SRC

**** quote

#+NAME: hub_format_and_sqlite_load_objects_2
#+BEGIN_SRC d
string[string] quote(M,O)(
        M   doc_matters,
  const O   obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_quote(doc_matters, obj)
  ];
  { /+ debug +/
    if (doc_matters.opt.action.debug_do
    && doc_matters.opt.action.verbose) {
      debug(sql_txt) {
        writeln(obj_txt["text"]);
      }
      debug(sql_html) {
        writeln(obj_txt["html"]);
      }
    } else {
      // load sql
    }
  }
  return obj_txt;
}
#+END_SRC
**** group

#+NAME: hub_format_and_sqlite_load_objects_3
#+BEGIN_SRC d
string[string] group(M,O)(
        M   doc_matters,
  const O   obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_group(doc_matters, obj)
  ];
  { /+ debug +/
    if (doc_matters.opt.action.debug_do
    && doc_matters.opt.action.verbose) {
      debug(sql_txt) {
        writeln(obj_txt["text"]);
      }
      debug(sql_html) {
        writeln(obj_txt["html"]);
      }
    } else {
      // load sql
    }
  }
  return obj_txt;
}
#+END_SRC

**** block

#+NAME: hub_format_and_sqlite_load_objects_4
#+BEGIN_SRC d
string[string] block(M,O)(
        M   doc_matters,
  const O   obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_block(doc_matters, obj)
  ];
  { /+ debug +/
    if (doc_matters.opt.action.debug_do
    && doc_matters.opt.action.verbose) {
      debug(sql_txt) {
        writeln(obj_txt["text"]);
      }
      debug(sql_html) {
        writeln(obj_txt["html"]);
      }
    } else {
      // load sql
    }
  }
  return obj_txt;
}
#+END_SRC

**** verse

#+NAME: hub_format_and_sqlite_load_objects_5
#+BEGIN_SRC d
string[string] verse(M,O)(
        M   doc_matters,
  const O   obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_verse(doc_matters, obj)
  ];
  { /+ debug +/
    if (doc_matters.opt.action.debug_do
    && doc_matters.opt.action.verbose) {
      debug(sql_txt) {
        writeln(obj_txt["text"]);
      }
      debug(sql_html) {
        writeln(obj_txt["html"]);
      }
    } else {
      // load sql
    }
  }
  return obj_txt;
}
#+END_SRC

**** code

#+NAME: hub_format_and_sqlite_load_objects_6
#+BEGIN_SRC d
string[string] code(M,O)(
        M   doc_matters,
  const O   obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_code(obj)
  ];
  { /+ debug +/
    if (doc_matters.opt.action.debug_do
    && doc_matters.opt.action.verbose) {
      debug(sql_txt) {
        writeln(obj_txt["text"]);
      }
      debug(sql_html) {
        writeln(obj_txt["html"]);
      }
    } else {
      // load sql
    }
  }
  return obj_txt;
}
#+END_SRC

**** table

#+NAME: hub_format_and_sqlite_load_objects_7
#+BEGIN_SRC d
string[string] table(M,O)(
        M   doc_matters,
  const O   obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_table(doc_matters, obj)
  ];
  { /+ debug +/
    if (doc_matters.opt.action.debug_do
    && doc_matters.opt.action.verbose) {
      debug(sql_txt) {
        writeln(obj_txt["text"]);
      }
      debug(sql_html) {
        writeln(obj_txt["html"]);
      }
    } else {
      // load sql
    }
  }
  return obj_txt;
}
#+END_SRC

** 4. ↻ loop, identify, load - loop template

#+NAME: sqlite_objects_loop
#+BEGIN_SRC d
auto format_and_sqlite_load = SQLiteFormatAndLoadObject!()(doc_matters);
string[string] obj_txt;
string doc_text;
string[] _insert_doc_objects;
foreach (part; doc_matters.has.keys_seq.sql) {
  foreach (obj; doc_abstraction[part]) {
    switch (obj.metainfo.is_of_part) {
    case "frontmatter":              assert(part == "head", part);
      switch (obj.metainfo.is_of_type) {
      case "para":
        switch (obj.metainfo.is_a) {
        case "heading":
          obj_txt = format_and_sqlite_load.heading(doc_matters, obj);
          break;
        default:
          { /+ debug +/
            if (doc_matters.opt.action.debug_do
            && doc_matters.opt.action.verbose) {
              writeln(__FILE__, ":", __LINE__, ": ", obj.metainfo.is_a);
            }
          }
          break;
        }
        break;
      default:
        { /+ debug +/
          if (doc_matters.opt.action.debug_do
          && doc_matters.opt.action.verbose) {
            writeln(__FILE__, ":", __LINE__, ": ", obj.metainfo.is_of_type);
          }
        }
        break;
      }
      break;
    case "body": //                    assert(part == "body", part);
      switch (obj.metainfo.is_of_type) {
      case "para":
        switch (obj.metainfo.is_a) {
        case "heading":
          debug (asserts) {
            if (part != "body") {
              writeln(__LINE__, ": ", obj.text);
            }
          }
          obj_txt = format_and_sqlite_load.heading(doc_matters, obj);
          break;
        case "para":
          obj_txt = format_and_sqlite_load.para(doc_matters, obj);
          break;
        default:
          { /+ debug +/
            if (doc_matters.opt.action.debug_do
            && doc_matters.opt.action.verbose) {
              writeln(__FILE__, ":", __LINE__, ": ", obj.metainfo.is_a);
            }
          }
          break;
        }
        break;
      case "block":
        switch (obj.metainfo.is_a) {
        case "quote":
          obj_txt = format_and_sqlite_load.quote(doc_matters, obj);
          break;
        case "group":
          obj_txt = format_and_sqlite_load.group(doc_matters, obj);
          break;
        case "block":
          obj_txt = format_and_sqlite_load.block(doc_matters, obj);
          break;
        case "poem":                        // double check on keeping both poem & verse
          break;
        case "verse":
          obj_txt = format_and_sqlite_load.verse(doc_matters, obj);
          break;
        case "code":
          obj_txt = format_and_sqlite_load.code(doc_matters, obj);
          break;
        case "table":
          obj_txt = format_and_sqlite_load.table(doc_matters, obj);
          break;
        default:
          { /+ debug +/
            if (doc_matters.opt.action.debug_do
            && doc_matters.opt.action.verbose) {
              writeln(__FILE__, ":", __LINE__, ": ", obj.metainfo.is_a);
            }
          }
          break;
        }
        break;
      default:
        { /+ debug +/
          if (doc_matters.opt.action.debug_do
          && doc_matters.opt.action.verbose) {
            writeln(__FILE__, ":", __LINE__, ": ", obj.metainfo.is_of_type);
          }
        }
        break;
      }
      break;
    case "backmatter":
      assert(part == "glossary" || "bibliography" || "bookindex" || "blurb" || "tail", part);
      switch (obj.metainfo.is_of_type) {
      case "para":
        switch (obj.metainfo.is_a) {
        case "heading":
          obj_txt = format_and_sqlite_load.heading(doc_matters, obj);
          break;
        case "glossary":             assert(part == "glossary", part);
          obj_txt = format_and_sqlite_load.para(doc_matters, obj);
          break;
        case "bibliography":         assert(part == "bibliography", part);
          obj_txt = format_and_sqlite_load.para(doc_matters, obj);
          break;
        case "bookindex":            assert(part == "bookindex", part);
          obj_txt = format_and_sqlite_load.para(doc_matters, obj);
          break;
        case "blurb":                assert(part == "blurb", part);
          obj_txt = format_and_sqlite_load.para(doc_matters, obj);
          break;
        default:
          { /+ debug +/
            if (doc_matters.opt.action.debug_do
            && doc_matters.opt.action.verbose) {
              writeln(__FILE__, ":", __LINE__, ": ", obj.metainfo.is_a);
            }
          }
          break;
        }
        break;
      default:
        { /+ debug +/
          if (doc_matters.opt.action.debug_do
          && doc_matters.opt.action.verbose) {
            writeln(__FILE__, ":", __LINE__, ": ", obj.metainfo.is_of_type);
          }
        }
        break;
      }
      break;
    case "comment":
      break;
    default:
      { /+ debug +/
        if (doc_matters.opt.action.debug_do
        && doc_matters.opt.action.verbose) {
          writeln(__FILE__, ":", __LINE__, ": ", obj.metainfo.is_of_part); // check where empty value could come from
          writeln(__FILE__, ":", __LINE__, ": ", obj.metainfo.is_a);
          writeln(__FILE__, ":", __LINE__, ": ", obj.text); // check where empty value could come from
        }
      }
      break;
    }
    if (obj.metainfo.is_a == "heading") {
      if ((doc_matters.opt.action.very_verbose)) {
        writeln(
          "markup: ", obj.metainfo.heading_lev_markup,
          "> ", obj.metainfo.dom_structure_markedup_tags_status,
          "; collapsed: ", obj.metainfo.heading_lev_collapsed,
          "> ", obj.metainfo.dom_structure_collapsed_tags_status,
          "; ocn: ", obj.metainfo.ocn,
          " node: ", obj.metainfo.node,
          "; parent: ", obj.metainfo.parent_lev_markup,
          "; ocn: ", obj.metainfo.parent_ocn,
          "; ",
        );
      }
    }
    if (!(obj.metainfo.is_a == "comment")) {
      _insert_doc_objects ~= insertDocObjectsRow(obj);
    }
  } // loop closes
}
return _insert_doc_objects.join.to!(char[]).toUTF8;
#+END_SRC

** 5. SQL statements                                             :statement:
*** drop index and tables
**** DROP INDEX IF EXISTS

#+NAME: sqlite_statement_drop_existing_index
#+BEGIN_SRC sql
DROP INDEX IF EXISTS idx_ocn;
DROP INDEX IF EXISTS idx_uid;
DROP INDEX IF EXISTS idx_digest_clean;
DROP INDEX IF EXISTS idx_digest_all;
DROP INDEX IF EXISTS idx_clean;
DROP INDEX IF EXISTS idx_title;
DROP INDEX IF EXISTS idx_author;
DROP INDEX IF EXISTS src_filename_base;
DROP INDEX IF EXISTS idx_language_document_char;
DROP INDEX IF EXISTS idx_classify_topic_register;
#+END_SRC

**** DROP TABLE IF EXISTS

#+NAME: sqlite_statement_drop_existing_tables
#+BEGIN_SRC sql
DROP TABLE IF EXISTS metadata_and_text;
DROP TABLE IF EXISTS topic_register;
DROP TABLE IF EXISTS doc_objects;
DROP TABLE IF EXISTS urls;
#+END_SRC

*** create tables and index
**** CREATE TABLE metadata_and_text

#+NAME: sqlite_statement_create_table_metadata_and_src_txt
#+BEGIN_SRC sql
CREATE TABLE metadata_and_text (
  uid                              VARCHAR(256)      UNIQUE, -- filename, language char, pod/txt (decide on delimiter [,;:/])
  src_composite_id_per_txt         VARCHAR(256)  NOT NULL,   -- UNIQUE, z pod name if any + src filename + language code
  src_composite_id_per_pod         VARCHAR(256)  NOT NULL,   -- z pod name if any + src filename
  title                            VARCHAR(800)  NOT NULL,
  title_main                       VARCHAR(400)  NOT NULL,
  title_sub                        VARCHAR(400)      NULL,
  title_short                      VARCHAR(400)      NULL,
  title_edition                    VARCHAR(10)       NULL,
  title_language                   VARCHAR(100)      NULL,
  title_language_char              VARCHAR(6)        NULL,
  creator_author                   VARCHAR(600)  NOT NULL,
  creator_author_last_first        VARCHAR(600)  NOT NULL,
  creator_author_email             VARCHAR(100)      NULL,
  creator_author_hon               VARCHAR(100)      NULL,
  creator_author_nationality       VARCHAR(100)      NULL,
  creator_editor                   VARCHAR(600)      NULL,
  creator_contributor              VARCHAR(600)      NULL,
  creator_illustrator              VARCHAR(600)      NULL,
  creator_photographer             VARCHAR(600)      NULL,
  creator_translator               VARCHAR(600)      NULL,
  creator_prepared_by              VARCHAR(600)      NULL,
  creator_digitized_by             VARCHAR(600)      NULL,
  creator_audio                    VARCHAR(600)      NULL,
  creator_video                    VARCHAR(600)      NULL,
  language_document                VARCHAR(100)      NULL,
  language_document_char           VARCHAR(6)    NOT NULL,
  language_original                VARCHAR(100)      NULL,
  language_original_char           VARCHAR(6)        NULL,
  date_added_to_site               VARCHAR(10)       NULL,
  date_available                   VARCHAR(10)       NULL,
  date_created                     VARCHAR(10)       NULL,
  date_issued                      VARCHAR(10)       NULL,
  date_modified                    VARCHAR(10)       NULL,
  date_published                   VARCHAR(10)       NULL,
  date_valid                       VARCHAR(10)       NULL,
  date_translated                  VARCHAR(10)       NULL,
  date_original_publication        VARCHAR(10)       NULL,
  date_generated                   VARCHAR(10)       NULL,
  original_title                   VARCHAR(800)      NULL,
  original_publisher               VARCHAR(600)      NULL,
  original_language                VARCHAR(100)      NULL,
  original_language_char           VARCHAR(6)        NULL,
  original_source                  VARCHAR(600)      NULL,
  original_institution             VARCHAR(600)      NULL,
  original_nationality             VARCHAR(100)      NULL,
  rights_copyright                 VARCHAR(2500)     NULL,
  rights_copyright_audio           VARCHAR(2500)     NULL,
  rights_copyright_cover           VARCHAR(2500)     NULL,
  rights_copyright_illustrations   VARCHAR(2500)     NULL,
  rights_copyright_photographs     VARCHAR(2500)     NULL,
  rights_copyright_text            VARCHAR(2500)     NULL,
  rights_copyright_translation     VARCHAR(2500)     NULL,
  rights_copyright_video           VARCHAR(2500)     NULL,
  rights_license                   VARCHAR(2500)     NULL,
  identifier_oclc                  VARCHAR(30)       NULL,
  identifier_isbn                  VARCHAR(16)       NULL,
  classify_topic_register          VARCHAR(2500)     NULL,
  classify_subject                 VARCHAR(600)      NULL,
  classify_loc                     VARCHAR(30)       NULL,
  classify_dewey                   VARCHAR(30)       NULL,
  classify_keywords                VARCHAR(600)      NULL,
  notes_abstract                   TEXT              NULL,
  notes_description                TEXT              NULL,
  notes_comment                    TEXT              NULL,
  notes_coverage                   VARCHAR(200)      NULL,
  notes_relation                   VARCHAR(200)      NULL,
  notes_history                    VARCHAR(600)      NULL,
  notes_type                       VARCHAR(600)      NULL,
  notes_format                     VARCHAR(600)      NULL,
  notes_prefix                     TEXT              NULL,
  notes_prefix_a                   TEXT              NULL,
  notes_prefix_b                   TEXT              NULL,
  notes_suffix                     TEXT              NULL,
  publisher                        VARCHAR(600)      NULL,
  src_filename_base                VARCHAR(256)  NOT NULL,
  src_filename_suffix              VARCHAR(6)    NOT NULL,
  src_fingerprint                  VARCHAR(256)      NULL,
  src_filesize                     VARCHAR(10)       NULL,
  src_wordcount                    VARCHAR(10)       NULL,
  pod_name                         VARCHAR(256)      NULL, -- zipped pod, work to be done here
  pod_fingerprint                  VARCHAR(256)      NULL, -- zipped pod, work to be done here
  pod_size                         VARCHAR(10)       NULL, -- zipped pod, work to be done here
  site_url_doc_root                VARCHAR(256)      NULL, -- url path to doc root
  site_url_html_toc                VARCHAR(256)      NULL,
  site_url_html_scroll             VARCHAR(256)      NULL,
  site_url_epub                    VARCHAR(256)      NULL,
  links                            TEXT              NULL
);
#+END_SRC

**** CREATE TABLE topic_register

#+NAME: sqlite_statement_create_table_topic_register
#+BEGIN_SRC sql
CREATE TABLE topic_register (
  -- tid                              BIGINT            PRIMARY KEY,
  uid_metadata_and_text            VARCHAR(256)      REFERENCES metadata_and_text(uid) ON DELETE CASCADE,
  -- src_composite_id_per_txt         VARCHAR(256)  NOT NULL,  - UNIQUE, - z pod name if any + src filename + language code
  -- src_composite_id_per_pod         VARCHAR(256)  NOT NULL,  - z pod name if any + src filename
  topic_register_lv0               VARCHAR(250)  NOT NULL,
  topic_register_lv1               VARCHAR(250)      NULL,
  topic_register_lv2               VARCHAR(250)      NULL,
  topic_register_lv3               VARCHAR(250)      NULL,
  topic_register_lv4               VARCHAR(250)      NULL,
  site_url_doc_root                VARCHAR(256)      NULL, -- url path to doc root
  site_url_html_toc                VARCHAR(256)      NULL,
  site_url_html_scroll             VARCHAR(256)      NULL
);
#+END_SRC

**** CREATE TABLE site_urls ?

#+NAME: sqlite_statement_create_table_site_urls
#+BEGIN_SRC sql
CREATE TABLE site_urls (
  -- tid                              BIGINT            PRIMARY KEY,
  uid_metadata_and_text            VARCHAR(256)      REFERENCES metadata_and_text(uid) ON DELETE CASCADE,
  src_composite_id_per_txt         VARCHAR(256)  NOT NULL, -- UNIQUE, - z pod name if any + src filename + language code
  src_composite_id_per_pod         VARCHAR(256)  NOT NULL, -- z pod name if any + src filename
  site_url_doc_root                VARCHAR(256)      NULL, -- url path to doc root
  site_url_html_toc                VARCHAR(256)      NULL,
  site_url_html_scroll             VARCHAR(256)      NULL
);
#+END_SRC

**** CREATE TABLE doc_objects

#+NAME: sqlite_statement_create_table_objects
#+BEGIN_SRC sql
CREATE TABLE doc_objects (
  lid                              BIGINT            PRIMARY KEY,
  uid_metadata_and_text            VARCHAR(256)      REFERENCES metadata_and_text(uid) ON DELETE CASCADE,
  ocn                              SMALLINT,
  obj_id                           VARCHAR(6)        NULL,
  clean                            TEXT              NULL,
  body                             TEXT              NULL,
  seg                              VARCHAR(256)      NULL,
  lev_an                           VARCHAR(1),
  is_of_type                       VARCHAR(16),
  is_a                             VARCHAR(16),
  lev                              SMALLINT          NULL,
  node                             VARCHAR(16)       NULL,
  parent                           VARCHAR(16)       NULL,
  last_decendant                   VARCHAR(16)       NULL, -- headings only
  digest_clean                     CHAR(256),
  digest_all                       CHAR(256),
  seg_name                         CHAR(256),
  types                            CHAR(1)           NULL
);
#+END_SRC

**** CREATE INDEX

#+NAME: sqlite_statement_create_index
#+BEGIN_SRC sql
CREATE INDEX idx_ocn          ON doc_objects(ocn);
CREATE INDEX idx_digest_clean ON doc_objects(digest_clean);
CREATE INDEX idx_digest_all   ON doc_objects(digest_all);
CREATE INDEX idx_clean        ON doc_objects(clean);
CREATE INDEX idx_title        ON metadata_and_text(title);
CREATE INDEX idx_author       ON metadata_and_text(creator_author_last_first);
CREATE INDEX idx_uid          ON metadata_and_text(uid);
CREATE INDEX idx_filename     ON metadata_and_text(src_filename_base);
CREATE INDEX idx_language     ON metadata_and_text(language_document_char);
CREATE INDEX idx_topics       ON metadata_and_text(classify_topic_register);
#+END_SRC

*** TODO local site link & info

*** delete rows (delete document)
**** DELETE uid rows doc matters & metadata
***** sql statement: dlang format

#+NAME: sqlite_formatted_delete_0
#+BEGIN_SRC d
string _uid = doc_matters.src.doc_uid;
string _delete_uid = format(q"┃
#+END_SRC

***** DELETE FROM ... WHERE

#+NAME: sqlite_formatted_delete_1
#+BEGIN_SRC sql
DELETE FROM metadata_and_text
WHERE uid = '%s';
DELETE FROM doc_objects
WHERE uid_metadata_and_text = '%s';
#+END_SRC

***** VALUES

#+NAME: sqlite_formatted_delete_2
#+BEGIN_SRC d
┃",
  _uid,
  _uid,
);
#+END_SRC

*** inserts
**** INSERT doc matters & metadata
***** sql statement: dlang format

#+NAME: sqlite_formatted_insertions_doc_matters_metadata_0
#+BEGIN_SRC d
string _uid = SQLinsertDelimiter!()(doc_matters.src.doc_uid);
string _insert_metadata = format(q"┃
#+END_SRC

***** INSERT INTO

#+NAME: sqlite_formatted_insertions_doc_matters_metadata_1
#+BEGIN_SRC sql
  INSERT INTO metadata_and_text (
    uid,
    src_filename_base,
    src_filename_suffix,
    src_composite_id_per_txt,
    src_composite_id_per_pod,
    title,
    title_main,
    title_sub,
    title_short,
    title_edition,
    title_language,
    creator_author,
    creator_author_last_first,
    creator_author_email,
    creator_illustrator,
    creator_translator,
    language_document,
    language_document_char,
    date_added_to_site,
    date_available,
    date_created,
    date_issued,
    date_modified,
    date_published,
    date_valid,
    rights_copyright,
    rights_copyright_audio,
    rights_copyright_cover,
    rights_copyright_illustrations,
    rights_copyright_photographs,
    rights_copyright_text,
    rights_copyright_translation,
    rights_copyright_video,
    rights_license,
    identifier_oclc,
    identifier_isbn,
    classify_dewey,
    classify_keywords,
    classify_loc,
    classify_subject,
    classify_topic_register,
    original_title,
    original_publisher,
    original_language,
    original_language_char,
    original_source,
    notes_abstract,
    notes_description,
    publisher,
    site_url_doc_root
  )
#+END_SRC

***** VALUES

#+NAME: sqlite_formatted_insertions_doc_matters_metadata_2
#+BEGIN_SRC sql
  VALUES (
    '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'
  );
#+END_SRC

***** dlang values for formatting

#+NAME: sqlite_formatted_insertions_doc_matters_metadata_3
#+BEGIN_SRC d
┃",
  _uid,
  SQLinsertDelimiter!()(doc_matters.src.filename_base),
  SQLinsertDelimiter!()(doc_matters.src.filename_extension),
  SQLinsertDelimiter!()(doc_matters.src.docname_composite_unique_per_src_doc),
  SQLinsertDelimiter!()(doc_matters.src.docname_composite_unique_per_src_pod),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.title_full),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.title_main),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.title_subtitle),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.title_short),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.title_edition),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.title_language),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.creator_author),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.creator_author_surname_fn),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.creator_author_email),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.creator_illustrator),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.creator_translator),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.language_document),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.language_document_char),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_added_to_site),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_available),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_created),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_issued),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_modified),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_published),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_valid),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_audio),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_cover),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_illustrations),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_photographs),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_text),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_translation),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_video),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_license),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.identifier_oclc),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.identifier_isbn),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.classify_dewey),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.classify_keywords),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.classify_loc),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.classify_subject),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.classify_topic_register),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.notes_abstract),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.notes_description),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.original_title),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.original_publisher),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.original_language),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.original_language_char),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.original_source),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.publisher),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.conf.w_srv_data_root_url_html)
);
#+END_SRC

**** INSERT topic register

writeln(doc_matters.conf_make_meta.meta.classify_topic_register_arr);

***** { if topic register then loop topic register array

#+NAME: sqlite_formatted_insertions_topic_register_0
#+BEGIN_SRC d
if (doc_matters.conf_make_meta.meta.classify_topic_register_arr.length > 0) {
  string _insert_topics;
  foreach (topic; doc_matters.conf_make_meta.meta.classify_topic_register_arr) {
    string[] subject_tree = topic.split(mkup.sep);
#+END_SRC

***** sql statement: dlang format

#+NAME: sqlite_formatted_insertions_topic_register_1
#+BEGIN_SRC d
_insert_topics ~= format(q"┃
#+END_SRC

***** INSERT INTO

#+NAME: sqlite_formatted_insertions_topic_register_2
#+BEGIN_SRC sql
  INSERT INTO topic_register (
    uid_metadata_and_text,
    topic_register_lv0,
    topic_register_lv1,
    topic_register_lv2,
    topic_register_lv3,
    topic_register_lv4
  )
#+END_SRC

***** VALUES

#+NAME: sqlite_formatted_insertions_topic_register_3
#+BEGIN_SRC sql
  VALUES (
    '%s', '%s', '%s', '%s', '%s', '%s'
  );
#+END_SRC

***** dlang values for formatting

#+NAME: sqlite_formatted_insertions_topic_register_4
#+BEGIN_SRC d
┃",
  _uid,
  (subject_tree.length > 0) ? subject_tree[0] : "",
  (subject_tree.length > 1) ? subject_tree[1] : "",
  (subject_tree.length > 2) ? subject_tree[2] : "",
  (subject_tree.length > 3) ? subject_tree[3] : "",
  (subject_tree.length > 4) ? subject_tree[4] : ""
);
#+END_SRC

***** } close topic register & loop topic register array

#+NAME: sqlite_formatted_insertions_topic_register_5
#+BEGIN_SRC d
  }
}
#+END_SRC

**** INSERT doc objects

lid unique, increment by 1 per object, not ocn

metadata tid document number unique
either:
- increment by adding 1 for each document,
- make hash of document filename or url and use?

***** sql statement: dlang format

#+NAME: sqlite_formatted_insertions_doc_objects_0
#+BEGIN_SRC d
string _insert_doc_objects_row = format(q"┃
#+END_SRC

***** INSERT INTO

#+NAME: sqlite_formatted_insertions_doc_objects_1
#+BEGIN_SRC sql
  INSERT INTO doc_objects (
    uid_metadata_and_text,
    ocn,
    obj_id,
    clean,
    body,
    lev,
    is_of_type,
    is_a,
    seg_name
  )
#+END_SRC

***** VALUES

#+NAME: sqlite_formatted_insertions_doc_objects_2
#+BEGIN_SRC sql
  VALUES (
    '%s', %s, '%s', '%s', '%s', %s, '%s', '%s', '%s'
  );
#+END_SRC

***** dlang values for formatting

#+NAME: sqlite_formatted_insertions_doc_objects_3
#+BEGIN_SRC d
┃",
  _uid,
  obj.metainfo.ocn,
  obj.metainfo.identifier,
  SQLinsertDelimiter!()(obj_txt["text"]),
  SQLinsertDelimiter!()(obj_txt["html"]),
  obj.metainfo.heading_lev_markup,
  obj.metainfo.is_of_type,
  obj.metainfo.is_a,
  obj.tags.html_segment_anchor_tag_is
);
#+END_SRC

* __END__