path: root/org/output_sqlite.org

#+TITLE:       doc_reform output sqlite
#+DESCRIPTION: documents - structuring, publishing in multiple formats & search
#+FILETAGS:    :doc_reform:output:db:sql:sqlite:
#+AUTHOR:      Ralph Amissah
#+EMAIL:       [[mailto:ralph.amissah@gmail.com][ralph.amissah@gmail.com]]
#+COPYRIGHT:   Copyright (C) 2015 - 2019 Ralph Amissah
#+LANGUAGE:    en
#+STARTUP:     indent content hideblocks hidestars
#+OPTIONS:     H:3 num:nil toc:t \n:nil @:t ::t |:t ^:nil _:nil -:t f:t *:t <:t
#+OPTIONS:     TeX:t LaTeX:t skip:nil d:nil todo:t pri:nil tags:not-in-toc
#+OPTIONS:     author:nil email:nil creator:nil timestamp:nil
#+PROPERTY:    header-args :padline no :exports code :cache no :noweb yes
#+EXPORT_SELECT_TAGS:  export
#+EXPORT_EXCLUDE_TAGS: noexport
#+TAGS: assert(a) class(c) debug(d) mixin(m) doc_reform(s) tangle(T) template(t) WEB(W) noexport(n)

- [[./doc_reform.org][doc_reform]]  [[./][org/]]
- [[./output_hub.org][output_hub]]

* sql
** 0. module, templates                                             :module:

*** hub
**** collection

#+BEGIN_SRC d :tangle "../src/doc_reform/output/sqlite.d"
module doc_reform.output.sqlite;
<<output_imports>>
import d2sqlite3;
import std.typecons : Nullable;
mixin DocReformOutputRgxInit;
static auto rgx = Rgx();
long _metadata_tid_lastrowid;
template SQLiteHubBuildTablesAndPopulate() {
  void SQLiteHubBuildTablesAndPopulate(D,M)(
    const D  doc_abstraction,
          M  doc_matters,
  ) {
    auto pth_sqlite = DocReformPathsSQLite!()(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

#+BEGIN_SRC d :tangle "../src/doc_reform/output/sqlite.d"
template SQLiteHubDiscreteBuildTablesAndPopulate() {
  void SQLiteHubDiscreteBuildTablesAndPopulate(D,M)(
    const D    doc_abstraction,
          M    doc_matters,
  ) {
    auto pth_sqlite = DocReformPathsSQLiteDiscrete!()(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

#+BEGIN_SRC d :tangle "../src/doc_reform/output/sqlite.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"
      );
      if (!(opt_action.sqlite_discrete)
        && !(opt_action.sqlite_db_create)
      ) {
      }
    } 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

#+BEGIN_SRC d :tangle "../src/doc_reform/output/sqlite.d"
template SQLinsertDelimiter() {
  auto 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 DocReformOutputRgxInit;
    struct sqlite_format_and_load_objects {
      <<sanitize_text_for_search>>
      <<sanitize_and_munge_inline_html>>
      <<html_objects>>
      <<sqlite_load_object>>
      <<hub_format_and_sqlite_load_objects>>
    }
    return sqlite_format_and_load_objects();
  }
}
#+END_SRC

*** sqlite instructions
**** create

#+BEGIN_SRC d :tangle "../src/doc_reform/output/sqlite.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_objects>>
      <<sqlite_statement_create_index>>
    ┃",);
    return _sql_instruct;
  }
}
#+END_SRC

**** delete

#+BEGIN_SRC d :tangle "../src/doc_reform/output/sqlite.d"
template SQLiteDeleteDocument() {
  string SQLiteDeleteDocument(M)(
    M doc_matters,
  ) {
    <<sqlite_formatted_delete>>
    return _delete_uid;
  }
}
#+END_SRC

**** insert

#+BEGIN_SRC d :tangle "../src/doc_reform/output/sqlite.d"
template SQLiteInsertMetadata() {
  string SQLiteInsertMetadata(M)(
    M doc_matters,
  ) {
    <<sqlite_formatted_insertions_doc_matters_metadata>>
    return _insert_metadata;
  }
}
#+END_SRC

**** insert doc objects loop

#+BEGIN_SRC d :tangle "../src/doc_reform/output/sqlite.d"
template SQLiteInsertDocObjectsLoop() {
  string SQLiteInsertDocObjectsLoop(D,M)(
    const D    doc_abstraction,
          M    doc_matters,
  ) {
    string _uid = SQLinsertDelimiter!()(doc_matters.src.doc_uid);
    auto site_url = DocReformPathsUrl!()(doc_matters);
    string insertDocObjectsRow(O)(O obj) {
      <<sqlite_formatted_insertions_doc_objects>>
      return _insert_doc_objects_row;
    }
    <<sqlite_objects_loop>>
  }
}
#+END_SRC

**** tables create

#+BEGIN_SRC d :tangle "../src/doc_reform/output/sqlite.d"
template SQLiteTablesCreate() {
  void SQLiteTablesCreate(E,O)(E env, O opt_action) {
    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_objects>>
          <<sqlite_statement_create_index>>
        ┃",);
        return _sql_instruct;
      }
    }
    if (opt_action.sqlite_db_create) {
      string _db_statement;
      auto pth_sqlite = DocReformPathsSQLite!()(opt_action.sqlite_filename, opt_action.output_dir_set);
      pth_sqlite.base.mkdirRecurse;
      auto db = Database(pth_sqlite.sqlite_file);
      {
        _db_statement ~= SQLiteTablesReCreate!()();
      }
      SQLiteDbRun!()(db, _db_statement, opt_action, "TABLE RE-CREATE");
    }
  }
}
#+END_SRC

**** tables drop

#+BEGIN_SRC d :tangle "../src/doc_reform/output/sqlite.d"
template SQLiteDbDrop() {
  void SQLiteDbDrop(O)(O opt_action) {
    writeln("db drop");
    if ((opt_action.sqlite_db_drop)) {
      auto pth_sqlite = DocReformPathsSQLite!()(opt_action.sqlite_filename, opt_action.output_dir_set);
      writeln("remove(", pth_sqlite.sqlite_file, ")");
      try {
        remove(pth_sqlite.sqlite_file);
      } catch (FileException ex) {
        // handle error
      }
    }
  }
}
#+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 = DocReformPathsSQLite!()(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: output_imports
#+BEGIN_SRC d
import doc_reform.output;
import
  std.file,
  std.uri,
  std.conv : to;
#+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
#+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));
  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_ref, "<sup>$1</sup> ");
  }
  if (_notes.length > 0) {
    _txt ~= _notes;
  }
  { /+ 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
#+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
#+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
#+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
#+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
#+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[1] ~ "┤" ~ to!string((obj.stow.link[m[2].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 = DocReformPathsHTML!()(doc_matters.output_path, doc_matters.src.language);
    if (_xml_type == "seg") {
      foreach (m; _txt.match(rgx.inline_link_hash)) {
        if (m.captures[3] in doc_matters.has.tag_associations) {
          if (m.captures[3] == doc_matters.has.tag_associations[(m.captures[3])]["seg_lv4"]) {
            _txt = _txt.replaceFirst(
              rgx.inline_link_hash,
              "┥$1┝┤"
                ~ doc_matters.conf_make_meta.conf.webserv_url_doc_root
                ~ "/"
                ~ pth_html.tail_fn_seg(doc_matters.src.filename, "$3.html")
              ~ "├"
            );
          } else {
            _txt = _txt.replaceFirst(
              rgx.inline_link_hash,
              "┥$1┝┤"
                ~ doc_matters.conf_make_meta.conf.webserv_url_doc_root
                ~ "/"
                ~ doc_matters.has.tag_associations[(m.captures[3])]["seg_lv4"]
                ~ ".html"
                ~ "#" ~ "$3"
              ~ "├"
            );
          }
        } else {
          if (!(doc_matters.opt.action.quiet)) {
            writeln(
              "WARNING on internal document links, anchor to link <<"
               ~ m.captures[3]
               ~ ">> not found in document, "
               ~ "anchor: " ~ m.captures[3]
               ~ " document: " ~ doc_matters.src.filename
            );
          }
        }
      }
    } else {
      if (auto m = _txt.match(rgx.inline_link_hash)) {
        _txt = _txt.replaceFirst(
          rgx.inline_link_hash,
          "┥$1┝┤"
            ~ doc_matters.conf_make_meta.conf.webserv_url_doc_root
            ~ "/"
            ~ pth_html.tail_fn_scroll(doc_matters.src.filename)
            ~ "#" ~ "$3"
          ~ "├"
        );
      }
    }
    _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
#+BEGIN_SRC d
string inline_notes_scroll(M,O)(
               M   doc_matters,
  const        O   obj,
  string           _txt,
) {
  if (obj.has.inline_notes_reg) {
    // _txt = font_face(_txt);
    _txt = _txt.replaceAll(
      rgx.inline_notes_al_regular_number_note,
      ("<a href=\"#note_$1\"><note id=\"noteref_$1\">&#160;<sup>$1</sup> </note></a>")
    );
  }
  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

******** seg

#+name: sanitize_and_munge_inline_html
#+BEGIN_SRC d
auto inline_notes_seg(M,O)(
               M     doc_matters,
  const        O     obj,
  string             _txt,
) {
  string[] _endnotes;
  if (obj.has.inline_notes_reg) {
    /+ need markup for text, and separated footnote +/
    foreach(m; _txt.matchAll(rgx.inline_notes_al_regular_number_note)) {
      _endnotes ~= format(
        "%s%s%s%s\n  %s%s%s%s%s\n  %s\n%s",
        "<p class=\"endnote\">",
        "<a href=\"#noteref_",
        m.captures[1],
        "\">",
        "<note id=\"note_",
        m.captures[1],
        "\">&#160;<sup>",
        m.captures[1],
        ".</sup></note></a>",
        m.captures[2],
        "</p>"
      );
    }
    _txt = _txt.replaceAll(
      rgx.inline_notes_al_regular_number_note,
      ("<a href=\"#note_$1\"><note id=\"noteref_$1\">&#160;<sup>$1</sup> </note></a>")
    );
  } else if (_txt.match(rgx.inline_notes_al_regular_number_note)) {
    debug(markup) {
      writeln(__LINE__, " endnote: ", obj.metainfo.is_a, ": ", obj.text);
    }
  }
  auto t = tuple(
    _txt,
    _endnotes,
  );
  return t;
}
#+END_SRC

******* inline markup

#+name: sanitize_and_munge_inline_html
#+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
#+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
#+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" || "endnotes" || "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
#+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
#+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
#+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
#+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
#+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
#+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");
  auto _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>";
      }
    auto t = tuple(
      _table,
      _tablenote,
    );
    return t;
  }
  string _txt = munge_html(doc_matters, obj);
  auto 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" bgcolor="white" 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
#+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
#+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
#+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
#+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
#+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
#+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
#+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
#+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
#+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
#+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
#+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_creator_author;
DROP INDEX IF EXISTS src_filename;
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 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_note                       VARCHAR(2500)     NULL,
  title_language                   VARCHAR(100)      NULL,
  title_language_char              VARCHAR(6)        NULL,
  creator_author                   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,
  publisher                        VARCHAR(600)      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,
  original_title                   VARCHAR(800)      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,
  src_filename                     VARCHAR(256)  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 */
  links                            TEXT              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),
  html_seg_url                     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);
CREATE INDEX idx_uid ON metadata_and_text(uid);
CREATE INDEX idx_filename ON metadata_and_text(src_filename);
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
#+BEGIN_SRC d
string _uid = doc_matters.src.doc_uid;
string _delete_uid = format(q"┃
#+END_SRC

***** DELETE FROM ... WHERE

#+name: sqlite_formatted_delete
#+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
#+BEGIN_SRC d
┃",
  _uid,
  _uid,
);
#+END_SRC

*** inserts
**** 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
#+BEGIN_SRC d
string _insert_doc_objects_row = format(q"┃
#+END_SRC

***** INSERT INTO

#+name: sqlite_formatted_insertions_doc_objects
#+BEGIN_SRC sql
  INSERT INTO doc_objects (
    uid_metadata_and_text,
    ocn,
    obj_id,
    clean,
    body,
    lev,
    is_of_type,
    is_a,
    html_seg_url
  )
#+END_SRC

***** VALUES

#+name: sqlite_formatted_insertions_doc_objects
#+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
#+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,
  site_url.html_seg(obj.tags.html_segment_anchor_tag_is, obj.metainfo.identifier),
);
#+END_SRC

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

#+name: sqlite_formatted_insertions_doc_matters_metadata
#+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
#+BEGIN_SRC sql
  INSERT INTO metadata_and_text (
    uid,
    src_filename,
    src_composite_id_per_txt,
    src_composite_id_per_pod,
    title,
    title_main,
    title_sub,
    title_short,
    title_edition,
    title_language,
    classify_dewey,
    classify_keywords,
    classify_loc,
    classify_subject,
    classify_topic_register,
    creator_author,
    creator_author_email,
    creator_illustrator,
    creator_translator,
    date_added_to_site,
    date_available,
    date_created,
    date_issued,
    date_modified,
    date_published,
    date_valid,
    identifier_isbn,
    identifier_oclc,
    language_document,
    language_document_char,
    notes_abstract,
    notes_description,
    original_publisher,
    original_language,
    original_language_char,
    original_source,
    original_title,
    publisher,
    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,
    site_url_doc_root
  )
#+END_SRC

***** VALUES

#+name: sqlite_formatted_insertions_doc_matters_metadata
#+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'
  );
#+END_SRC

***** dlang values for formatting

#+name: sqlite_formatted_insertions_doc_matters_metadata
#+BEGIN_SRC d
┃",
  _uid,
  SQLinsertDelimiter!()(doc_matters.src.filename),
  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.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.creator_author),
  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.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.identifier_isbn),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.identifier_oclc),
  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.notes_abstract),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.notes_description),
  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.original_title),
  SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.publisher),
  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.conf.webserv_url_doc_root)
);
#+END_SRC

* __END__