path: root/org/output_sqlite_discrete.org

#+TITLE:       sdp output sqlite discrete
#+AUTHOR:      Ralph Amissah
#+EMAIL:       [[mailto:ralph.amissah@gmail.com][ralph.amissah@gmail.com]]
#+DESCRIPTION: documents - structuring, publishing in multiple formats & search
#+KEYWORDS
#+LANGUAGE:    en
#+STARTUP:     indent content
#+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 :noweb yes
#+EXPORT_SELECT_TAGS:  export
#+EXPORT_EXCLUDE_TAGS: noexport
#+FILETAGS:            :sdp:niu:output:
#+TAGS: assert(a) class(c) debug(d) mixin(m) sdp(s) tangle(T) template(t) WEB(W) noexport(n)

* sql
** 0. module, templates

#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite_discrete.d
module sdp.output.sqlite_discrete;
<<output_imports>>
template SQLiteDiscreteDbRun() {
  void SQLiteDiscreteDbRun(Db,St,O)(
    auto ref       Db   db,
    auto ref       St   db_statement,
    auto ref       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);
    }
    if (opt_action.debug_do) {
      writeln(note);
      if (opt_action.verbose) {
        writeln(db_statement);
      }
    }
  }
}
template SQLiteDiscreteBuildTablesAndPopulate() {
  void SQLiteDiscreteBuildTablesAndPopulate(D,I)(
    auto ref const D    doc_abstraction,
    auto ref       I    doc_matters,
  ) {
    import d2sqlite3;
    import std.typecons : Nullable;
    mixin SiSUoutputRgxInit;
    static auto rgx = Rgx();
    auto pth_sqlite = SiSUpathsSQLiteDiscrete!()(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,I)(
        auto ref       Db   db,
        auto ref const D    doc_abstraction,
        auto ref       I    doc_matters,
      ) {
        <<sqlite_db_statement_composite>>
      }
    }
    template SQLinsertDelimiter() {
      auto SQLinsertDelimiter(string _txt) {
        _txt = _txt
          .replaceAll(rgx.quotation_mark_sql_insert_delimiter, "$0$0");
        return _txt;
      }
    }
    template SQLiteFormatAndLoadObject() {
      auto SQLiteFormatAndLoadObject(I)(
        auto ref       I    doc_matters,
      ) {
        mixin SiSUoutputRgxInit;
        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();
      }
    }
    template SQLiteDiscreteTablesReCreate() {
      string SQLiteDiscreteTablesReCreate()() {
        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;
      }
    }
    template SQLiteDiscreteInsertMetadata() {
      string SQLiteDiscreteInsertMetadata(I)(
        auto ref       I    doc_matters,
      ) {
        <<sqlite_formatted_insertions_doc_matters_metadata>>
        return _insert_metadata;
      }
    }
    template SQLiteDiscreteInsertDocObjectsLoop() {
      string SQLiteDiscreteInsertDocObjectsLoop(P,I)(
        auto ref       P    doc_parts,
        auto ref       I    tid,
      ) {
        string insertDocObjectsRow(O)(O obj) {
          <<sqlite_formatted_insertions_doc_objects>>
          return _insert_doc_objects_row;
        }
        <<sqlite_objects_loop>>
      }
    }
    SQLiteDiscreteDbStatementComposite!()(db, doc_abstraction, doc_matters);
  }
}
#+END_SRC

** 1. sqlite_db_statement [#A]

#+name: sqlite_db_statement_composite
#+BEGIN_SRC d
{
  string _db_statement;
  {
    _db_statement ~= SQLiteDiscreteTablesReCreate!()();
    _db_statement ~= SQLiteDiscreteInsertMetadata!()(doc_matters);
    _db_statement ~= SQLiteDiscreteInsertDocObjectsLoop!()(doc_matters.xml.keys_seq.sql, 1); // FIX
    SQLiteDiscreteDbRun!()(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 sdp.output;
import
  std.file,
  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
auto 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;
  }
  if (doc_matters.opt.action.debug_do) {
    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
auto munge_html(O)(
  auto return ref 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 (_txt.matchFirst(rgx.inline_link)) {
    foreach (m; _txt.matchAll(rgx.inline_link)) {
    }
    _txt = _txt.replaceAll(rgx.inline_link_clean, "");
  }
  if (_notes.length > 0) {
    _txt ~= _notes;
  }
  if (doc_matters.opt.action.debug_do) {
    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

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

#+name: html_objects
#+BEGIN_SRC d
auto html_heading(O)(
  auto return ref const O    obj,
) {
  string _txt = munge_html(obj);
  string o = format(q"¶<p class="%s"><b>
      %s
    </b></p>¶",
      obj.is_a,
      _txt,
    );
  return o;
}
#+END_SRC

******* +fancy+

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

****** para

#+name: html_objects
#+BEGIN_SRC d
auto html_para(O)(
  auto return ref const O    obj,
) {
  string _txt = munge_html(obj);
  _txt = (obj.bullet) ? ("●&#160;&#160;" ~ _txt) : _txt;
  string o = format(q"¶<p class="%s" indent="h%si%s">
    %s
  </p>¶",
      obj.is_a,
      obj.indent_hang,
      obj.indent_base,
      _txt
    );
  return o;
}
#+END_SRC

****** quote

#+name: html_objects
#+BEGIN_SRC d
auto html_quote(O)(
  auto return ref const O    obj,
) {
  string _txt = munge_html(obj);
  string o = format(q"¶<p class="%s">
    %s
  </p>¶",
      obj.is_a,
      _txt
    );
  return o;
}
#+END_SRC

****** group

#+name: html_objects
#+BEGIN_SRC d
auto html_group(O)(
  auto return ref const O    obj,
) {
  string _txt = munge_html(obj);
  string o = format(q"¶<p class="%s">
    %s
  </p>¶",
      obj.is_a,
      _txt
    );
  return o;
}
#+END_SRC

****** block

#+name: html_objects
#+BEGIN_SRC d
auto html_block(O)(
  auto return ref const O    obj,
) {
  string _txt = munge_html(obj);
  string o = format(q"¶
  <p class="%s">%s</p>¶",
      obj.is_a,
      _txt.stripRight
    );
  return o;
}
#+END_SRC

****** verse

#+name: html_objects
#+BEGIN_SRC d
auto html_verse(O)(
  auto return ref const O    obj,
) {
  string _txt = munge_html(obj);
  string o = format(q"¶<p class="%s">%s</p>¶",
      obj.is_a,
      _txt
    );
  return o;
}
#+END_SRC

****** code

#+name: html_objects
#+BEGIN_SRC d
auto html_code(O)(
  auto return ref const O    obj,
) {
  string _txt = html_special_characters_code(obj.text);
  string o = format(q"¶<p class="%s">%s</p>¶",
      obj.is_a,
      _txt
    );
  return o;
}
#+END_SRC

****** table

#+name: html_objects
#+BEGIN_SRC d
auto html_table(O)(
  auto return ref const O     obj,
) {
  auto _tablarize(O)(
    auto return ref 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(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.is_a,
    _txt,
    _note
  );
  return o;
}
#+END_SRC

*** 2. _sqlite_ (loop preparation & pre-loop action)
**** prepare insert statements and do pre-loop inserts
***** 1. _metadata & src_ text
***** 2. _doc objects_ (used with doc_objects in document loop)
******  TODO (within loop not here - insert doc objects

work out

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

#+name: sqlite_load_object
#+BEGIN_SRC d
auto sqlite_load_string(O,Dm)(
  auto return ref const O     obj,
  auto return ref       Dm    doc_matters,
) {
  string o;
  return o;
}
#+END_SRC

#+name: sqlite_load_object
#+BEGIN_SRC d
auto postgresql_load_string(O,Dm)(
  auto return ref const O     obj,
  auto return ref       Dm    doc_matters,
) {
  string o;
  return o;
}
#+END_SRC

#+name: sqlite_load_object
#+BEGIN_SRC d
string sqlite_statement(O)(
  auto return ref 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
auto heading(O)(
  auto return ref const O      obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_heading(obj)
  ];
  if (doc_matters.opt.action.debug_do) {
    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
auto para(O)(
  auto return ref const O     obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_para(obj)
  ];
  if (doc_matters.opt.action.debug_do) {
    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
auto quote(O)(
  auto return ref const O     obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_quote(obj)
  ];
  if (doc_matters.opt.action.debug_do) {
    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
auto group(O)(
  auto return ref const O     obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_group(obj)
  ];
  if (doc_matters.opt.action.debug_do) {
    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
auto block(O)(
  auto return ref const O     obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_block(obj)
  ];
  if (doc_matters.opt.action.debug_do) {
    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
auto verse(O)(
  auto return ref const O     obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_verse(obj)
  ];
  if (doc_matters.opt.action.debug_do) {
    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
auto code(O)(
  auto return ref const O     obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_code(obj)
  ];
  if (doc_matters.opt.action.debug_do) {
    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
auto table(O)(
  auto return ref const O     obj,
) {
  string[string] obj_txt = [
    "text": generic_munge_sanitize_text_for_search(obj.text),
    "html": html_table(obj)
  ];
  if (doc_matters.opt.action.debug_do) {
    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_parts) {
  foreach (obj; doc_abstraction[part]) {
    switch (obj.of_part) {
    case "frontmatter":              assert(part == "head", part);
      switch (obj.is_of) {
      case "para":
        switch (obj.is_a) {
        case "heading":
          obj_txt = format_and_sqlite_load.heading(obj);
          break;
        default:
          if ((doc_matters.opt.action.debug_do)) {
            writeln(__FILE__, ":", __LINE__, ": ", obj.is_a);
          }
          break;
        }
        break;
      default:
        if ((doc_matters.opt.action.debug_do)) {
          writeln(__FILE__, ":", __LINE__, ": ", obj.is_of);
        }
        break;
      }
      break;
    case "body": //                    assert(part == "body", part); // TODO broken
      switch (obj.is_of) {
      case "para":
        switch (obj.is_a) {
        case "heading":
          debug (asserts) { // TODO consider and fix or remove
            if (part != "body") {
              writeln(__LINE__, ": ", obj.text);
            }
          }
          obj_txt = format_and_sqlite_load.heading(obj);
          break;
        case "para":
          obj_txt = format_and_sqlite_load.para(obj);
          break;
        default:
          if ((doc_matters.opt.action.debug_do)) {
            writeln(__FILE__, ":", __LINE__, ": ", obj.is_a);
          }
          break;
        }
        break;
      case "block":
        switch (obj.is_a) {
        case "quote":
          obj_txt = format_and_sqlite_load.quote(obj);
          break;
        case "group":
          obj_txt = format_and_sqlite_load.group(obj);
          break;
        case "block":
          obj_txt = format_and_sqlite_load.block(obj);
          break;
        case "poem":                        // double check on keeping both poem & verse
          break;
        case "verse":
          obj_txt = format_and_sqlite_load.verse(obj);
          break;
        case "code":
          obj_txt = format_and_sqlite_load.code(obj);
          break;
        case "table":
          obj_txt = format_and_sqlite_load.table(obj);
          break;
        default:
          if ((doc_matters.opt.action.debug_do)) {
            writeln(__FILE__, ":", __LINE__, ": ", obj.is_a);
          }
          break;
        }
        break;
      default:
        if ((doc_matters.opt.action.debug_do)) {
          writeln(__FILE__, ":", __LINE__, ": ", obj.is_of);
        }
        break;
      }
      break;
    case "backmatter":
      assert(part == "endnotes" || "glossary" || "bibliography" || "bookindex_seg" || "blurb" || "tail", part);
      switch (obj.is_of) {
      case "para":
        switch (obj.is_a) {
        case "heading":
          obj_txt = format_and_sqlite_load.heading(obj);
          break;
        case "endnote":              assert(part == "endnotes", part);
          obj_txt = format_and_sqlite_load.para(obj);
          break;
        case "glossary":             assert(part == "glossary", part);
          obj_txt = format_and_sqlite_load.para(obj);
          break;
        case "bibliography":         assert(part == "bibliography", part);
          obj_txt = format_and_sqlite_load.para(obj);
          break;
        case "bookindex":            assert(part == "bookindex_seg", part);
          obj_txt = format_and_sqlite_load.para(obj);
          break;
        case "blurb":                assert(part == "blurb", part);
          obj_txt = format_and_sqlite_load.para(obj);
          break;
        default:
          if ((doc_matters.opt.action.debug_do)) {
            writeln(__FILE__, ":", __LINE__, ": ", obj.is_a);
          }
          break;
        }
        break;
      default:
        if ((doc_matters.opt.action.debug_do)) {
          writeln(__FILE__, ":", __LINE__, ": ", obj.is_of);
        }
        break;
      }
      break;
    case "comment":
      break;
    default:
      if ((doc_matters.opt.action.debug_do)) {
        writeln(__FILE__, ":", __LINE__, ": ", obj.of_part); // check where empty value could come from
        writeln(__FILE__, ":", __LINE__, ": ", obj.is_a);
        writeln(__FILE__, ":", __LINE__, ": ", obj.text); // check where empty value could come from
      }
      break;
    }
    if (obj.is_a == "heading") {
      if ((doc_matters.opt.action.verbose)) {
        writeln(
          "markup: ", obj.heading_lev_markup,
          "> ", obj.dom_markedup,
          "; collapsed: ", obj.heading_lev_collapsed,
          "> ", obj.dom_collapsed,
          "; ocn: ", obj.ocn,
          " node: ", obj.node,
          "; parent: ", obj.parent_lev_markup,
          "; ocn: ", obj.parent_ocn,
          "; ",
        );
      }
    }
    if (!(obj.is_a == "comment")) {
      _insert_doc_objects ~= insertDocObjectsRow(obj);
    }
  } // loop closes
}
return _insert_doc_objects.join.to!(char[]).toUTF8;
#+END_SRC

** 5. SQL statements
*** create tables

**** DROP INDEX IF EXISTS

#+name: sqlite_statement_drop_existing_index
#+BEGIN_SRC sql
DROP INDEX IF EXISTS ocn;
DROP INDEX IF EXISTS digest_clean;
DROP INDEX IF EXISTS digest_all;
DROP INDEX IF EXISTS clean;
DROP INDEX IF EXISTS lev0;
DROP INDEX IF EXISTS lev1;
DROP INDEX IF EXISTS lev2;
DROP INDEX IF EXISTS lev3;
DROP INDEX IF EXISTS lev4;
DROP INDEX IF EXISTS lev5;
DROP INDEX IF EXISTS lev6;
DROP INDEX IF EXISTS title;
DROP INDEX IF EXISTS creator_author;
DROP INDEX IF EXISTS src_filename;
DROP INDEX IF EXISTS language_document_char;
DROP INDEX IF EXISTS 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 TABLE metadata_and_text

#+name: sqlite_statement_create_table_metadata_and_src_txt
#+BEGIN_SRC sql
CREATE TABLE metadata_and_text (
  tid                              INTEGER           PRIMARY KEY,
/* src_filename_composite           VARCHAR(256)  NOT NULL UNIQUE, /* z pod name if any + src filename + language code */
  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 */
  src_text                         TEXT              NULL,
  fulltext                         TEXT              NULL,
  links                            TEXT              NULL
);
#+END_SRC

**** CREATE TABLE doc_objects

#+name: sqlite_statement_create_table_objects
#+BEGIN_SRC sql
CREATE TABLE doc_objects (
  lid                              INTEGER PRIMARY KEY,
  metadata_tid                     INTEGER REFERENCES metadata_and_text,
  ocn                              SMALLINT,
  ocnd                             VARCHAR(6),
  ocns                             VARCHAR(6),
  clean                            TEXT NULL,
  body                             TEXT NULL,
  book_idx                         TEXT NULL,
  seg                              VARCHAR(256) NULL,
  lev_an                           VARCHAR(1),
  lev                              SMALLINT NULL,
  lev0                             SMALLINT,
  lev1                             SMALLINT,
  lev2                             SMALLINT,
  lev3                             SMALLINT,
  lev4                             SMALLINT,
  lev5                             SMALLINT,
  lev6                             SMALLINT,
  lev7                             SMALLINT,
  en_a                             SMALLINT NULL,
  en_z                             SMALLINT NULL,
  en_a_asterisk                    SMALLINT NULL,
  en_z_asterisk                    SMALLINT NULL,
  en_a_plus                        SMALLINT NULL,
  en_z_plus                        SMALLINT NULL,
  t_of                             VARCHAR(16),
  t_is                             VARCHAR(16),
  node                             VARCHAR(16) NULL,
  parent                           VARCHAR(16) NULL,
  digest_clean                     CHAR(256),
  digest_all                       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_lev0 ON doc_objects(lev0);
CREATE INDEX idx_lev1 ON doc_objects(lev1);
CREATE INDEX idx_lev2 ON doc_objects(lev2);
CREATE INDEX idx_lev3 ON doc_objects(lev3);
CREATE INDEX idx_lev4 ON doc_objects(lev4);
CREATE INDEX idx_lev5 ON doc_objects(lev5);
CREATE INDEX idx_lev6 ON doc_objects(lev6);
CREATE INDEX idx_title ON metadata_and_text(title);
CREATE INDEX idx_author ON metadata_and_text(creator_author);
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

*** sql insert statement formatted 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;
_insert_doc_objects_row = format(q"¶
#+END_SRC

**** INSERT INTO

#+name: sqlite_formatted_insertions_doc_objects
#+BEGIN_SRC sql
  INSERT INTO doc_objects (
    metadata_tid,
    ocn,
    clean,
    body,
    lev,
    t_of,
    t_is
  )
#+END_SRC

**** VALUES

#+name: sqlite_formatted_insertions_doc_objects
#+BEGIN_SRC sql
  VALUES (
    %d,
    %s,
    '%s',
    '%s',
    %s,
    '%s',
    '%s'
  );
#+END_SRC

**** sql statement: dlang values for formatting

#+name: sqlite_formatted_insertions_doc_objects
#+BEGIN_SRC d
¶",
  1,
  obj.ocn,
  SQLinsertDelimiter!()(obj_txt["text"]),
  SQLinsertDelimiter!()(obj_txt["html"]),
  obj.heading_lev_markup,
  obj.is_of,
  obj.is_a,
);
#+END_SRC

*** sqlite insert statement formatted doc matters metadata
**** sql statement: dlang format
#+name: sqlite_formatted_insertions_doc_matters_metadata
#+BEGIN_SRC d
string _insert_metadata;
_insert_metadata = format(q"¶
#+END_SRC

**** INSERT INTO

#+name: sqlite_formatted_insertions_doc_matters_metadata
#+BEGIN_SRC sql
  INSERT INTO metadata_and_text (
    src_filename,
    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
  )
#+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'
  );
#+END_SRC

**** sql statement: values for formatting

#+name: sqlite_formatted_insertions_doc_matters_metadata
#+BEGIN_SRC d
¶",
  SQLinsertDelimiter!()(doc_matters.src.filename),
  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)
);
#+END_SRC

* __END__