0.26.0 sqlite single statement insertion of objects

- d2sqlite3 db.run, begin commit used with insert statement
- can be used after upstream fix
  that should follow d2sqlite3 0.16.0

1 files changed, 67 insertions, 115 deletions

diff --git a/org/output_sqlite_discrete.org b/org/output_sqlite_discrete.org
index c659441..fced21d 100644
--- a/org/output_sqlite_discrete.org
+++ b/org/output_sqlite_discrete.org
@@ -45,7 +45,8 @@ template SQLiteDiscreteBuildTablesAndPopulate() {
       }
     }
     template SQLiteInstruct() {
-      Statement SQLiteInstruct(I)(
+      void SQLiteInstruct(Db,I)(
+                              Db   db,
         auto ref              I    doc_matters,
       ) {
         <<sqlite_db_initialize>>
@@ -56,13 +57,13 @@ template SQLiteDiscreteBuildTablesAndPopulate() {
         <<sqlite_close_initialize>>
         <<sqlite_insert_metadata_and_src_text>>
         //<<sqlite_insert_urls>>
-        <<sqlite_insert_doc_objects>>
       }
     }
     template SQLiteObjectsLoop() {
       void SQLiteObjectsLoop(P)(
         auto ref P          doc_parts,
       ) {
+        <<sqlite_insert_doc_objects>>
         <<sqlite_objects_loop>>
       }
     }
@@ -112,7 +113,6 @@ auto generic_munge_sanitize_text_for_search(
 ) {
   string _notes;
   string _urls;
-  _txt = _txt.replaceAll(rgx.inline_fontface_clean, "");
   if (_txt.matchFirst(rgx.inline_notes_al_gen)) {
     foreach (m; _txt.matchAll(rgx.inline_notes_al_gen_text)) {
       _notes ~= "\n" ~ m["text"];
@@ -210,7 +210,7 @@ auto munge_html(O)(
 #+name: sanitize_and_munge_inline_html
 #+BEGIN_SRC d
 string html_special_characters(string _txt){
-  _txt = (_txt)
+  _txt = _txt
     .replaceAll(rgx.xhtml_ampersand,    "&#38;")
     .replaceAll(rgx.xhtml_quotation,    "&#34;")
     .replaceAll(rgx.xhtml_less_than,    "&#60;")
@@ -226,7 +226,7 @@ string html_special_characters(string _txt){
 #+name: sanitize_and_munge_inline_html
 #+BEGIN_SRC d
 string html_special_characters_code(string _txt){
-  _txt = (_txt)
+  _txt = _txt
     .replaceAll(rgx.xhtml_ampersand,    "&#38;")
     .replaceAll(rgx.xhtml_quotation,    "&#34;")
     .replaceAll(rgx.xhtml_less_than,    "&#60;")
@@ -241,7 +241,7 @@ string html_special_characters_code(string _txt){
 #+name: sanitize_and_munge_inline_html
 #+BEGIN_SRC d
 string html_font_face(string _txt){
-  _txt = (_txt)
+  _txt = _txt
     .replaceAll(rgx.inline_emphasis,    "<em>$1</em>")
     .replaceAll(rgx.inline_bold,        "<b>$1</b>")
     .replaceAll(rgx.inline_underscore,  "<u>$1</u>")
@@ -474,10 +474,6 @@ auto html_table(O)(
 
 #+name: sqlite_db_initialize
 #+BEGIN_SRC d
-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));
-// auto db = Database(":memory:"); // open database in memory
 db.run("
 #+END_SRC
 
@@ -896,7 +892,6 @@ insert_metadata.bind(":rights_license",                    doc_matters.conf_make
 // insert_metadata.bind(":links",                          doc_matters.conf_make_meta.meta.links);
 insert_metadata.execute(); insert_metadata.reset();
 /+ watch +/
-writeln(" ", pth_sqlite.sqlite_file(doc_matters.src.filename));
 if ((doc_matters.opt.action.verbose)) {
   writeln("sql statement executed");
 }
@@ -920,15 +915,22 @@ insert_metadata.inject(
 #+END_SRC
 
 ****** 2. _doc objects_ (used with doc_objects in document loop)
-******* prepare sql statement
+******* prepare sql statement, sql utf8
 ******** d { sql statement
 
 #+name: sqlite_insert_doc_objects
 #+BEGIN_SRC d
-Statement _insert_doc_objects = db.prepare("
+string insertDocObjectsRow(O)(O obj) {
+  auto sql_insert_delimiter(string _txt) {
+    _txt = _txt
+      .replaceAll(rgx.quotation_mark_sql_insert_delimiter, "$0$0");
+    return _txt;
+  }
+  string _insert_doc_objects_row;
+ _insert_doc_objects_row = format(q"¶
 #+END_SRC
 
-********* sql insert into
+********* sql statement: formatted
 
 lid unique, increment by 1 per object, not ocn
 
@@ -937,90 +939,54 @@ either:
 - increment by adding 1 for each document,
 - make hash of document filename or url and use?
 
+********** INSERT INTO
+
 #+name: sqlite_insert_doc_objects
 #+BEGIN_SRC sql
-  INSERT INTO doc_objects (
-    lid,
-    metadata_tid,
-    ocn,
-    ocnd,
-    ocns,
-    clean,
-    body,
-    book_idx,
-    seg,
-    lev_an,
-    lev,
-    lev0,
-    lev1,
-    lev2,
-    lev3,
-    lev4,
-    lev5,
-    lev6,
-    lev7,
-    en_a,
-    en_z,
-    en_a_asterisk,
-    en_z_asterisk,
-    en_a_plus,
-    en_z_plus,
-    t_of,
-    t_is,
-    node,
-    parent,
-    digest_clean,
-    digest_all,
-    types
-  )
+    INSERT INTO doc_objects (
+      ocn,
+      clean,
+      body,
+      lev,
+      t_of,
+      t_is
+    )
 #+END_SRC
 
-********* sql values
+********** VALUES
 
 #+name: sqlite_insert_doc_objects
 #+BEGIN_SRC sql
-  VALUES (
-    :lid,
-    :metadata_tid,
-    :ocn,
-    :ocnd,
-    :ocns,
-    :clean,
-    :body,
-    :book_idx,
-    :seg,
-    :lev_an,
-    :lev,
-    :lev0,
-    :lev1,
-    :lev2,
-    :lev3,
-    :lev4,
-    :lev5,
-    :lev6,
-    :lev7,
-    :en_a,
-    :en_z,
-    :en_a_asterisk,
-    :en_z_asterisk,
-    :en_a_plus,
-    :en_z_plus,
-    :t_of,
-    :t_is,
-    :node,
-    :parent,
-    :digest_clean,
-    :digest_all,
-    :types
-  )
+    VALUES (
+      %s,
+      '%s',
+      '%s',
+      %s,
+      '%s',
+      '%s'
+    );
+#+END_SRC
+
+********* sql statement: values for formatting
+
+#+name: sqlite_insert_doc_objects
+#+BEGIN_SRC sql
+  ¶",
+    obj.ocn,
+    sql_insert_delimiter(obj_txt["text"]),
+    sql_insert_delimiter(obj_txt["html"]),
+    obj.heading_lev_markup,
+    obj.is_of,
+    obj.is_a,
+  );
 #+END_SRC
 
 ******** d }
 
 #+name: sqlite_insert_doc_objects
 #+BEGIN_SRC d
-");
-return _insert_doc_objects;
+  return _insert_doc_objects_row;
+}
 #+END_SRC
 
 *******  TODO (within loop not here - insert doc objects
@@ -1273,10 +1239,15 @@ auto table(O)(
 
 #+name: sqlite_objects_loop
 #+BEGIN_SRC d
-Statement insert_doc_objects = SQLiteInstruct!()(doc_matters);
+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));
+SQLiteInstruct!()(db, doc_matters); // consider best location, need to feed individual objects for sqlite table: doc_objects, possibly a separate template?
 auto format_and_sqlite_load = SQLiteFormatAndLoadObject!()(doc_matters);
 string[string] obj_txt;
 string doc_text;
+string[] _insert_doc_objects;
+_insert_doc_objects ~= "BEGIN;\n";
 foreach (part; doc_parts) {
   foreach (obj; doc_abstraction[part]) {
     switch (obj.of_part) {
@@ -1421,36 +1392,17 @@ foreach (part; doc_parts) {
         );
       }
     }
-    insert_doc_objects.bind(":t_of",        obj.is_of);
-    insert_doc_objects.bind(":t_is",        obj.is_a);
-    insert_doc_objects.bind(":ocn",         obj.ocn);
-    insert_doc_objects.bind(":clean",       obj_txt["text"]); // consider whether book index info should be made available within clear text for search
-    insert_doc_objects.bind(":body",        obj_txt["html"]);
-    // insert_doc_objects.bind(":book_idx",    ""); // not needed, but, consider whether should be made available within object for clear text search
-    insert_doc_objects.bind(":lev",         obj.heading_lev_markup);
-    // // insert_doc_objects.bind(":dom_markedup",    ""); // should make lev sequence below obsolete
-    // // insert_doc_objects.bind(":dom_collapsed", ""); // should add info
-    // insert_doc_objects.bind(":lev0",        "");
-    // insert_doc_objects.bind(":lev1",        "");
-    // insert_doc_objects.bind(":lev2",        "");
-    // insert_doc_objects.bind(":lev3",        "");
-    // insert_doc_objects.bind(":lev4",        "");
-    // insert_doc_objects.bind(":lev5",        "");
-    // insert_doc_objects.bind(":lev6",        "");
-    // insert_doc_objects.bind(":lev7",        "");
-    // insert_doc_objects.bind(":node",        "");
-    // insert_doc_objects.bind(":type",        "");
-    // insert_doc_objects.bind(":parent_ocn",                "");
-    // insert_doc_objects.bind(":ancestors",                 "");
-    // insert_doc_objects.bind(":heading_lev_markup",        "");
-    // insert_doc_objects.bind(":heading_lev_collapsed",     "");
-    // insert_doc_objects.bind(":parent_lev_markup",        "");
-    // insert_doc_objects.bind(":heading_ancestors",         "");
-    // insert_doc_objects.bind(":node",                      "");
-    insert_doc_objects.execute(); insert_doc_objects.reset();
-  }
+    if (!(obj.is_a == "comment")) {
+      _insert_doc_objects ~= insertDocObjectsRow(obj);
+    }
+  } // loop closes
+}
+_insert_doc_objects ~= "COMMIT";
+debug(sql_statement) {
+  writeln("#+BEGIN_SRC sql\n", _insert_doc_objects.join, "\n#+END_SRC");
 }
-insert_doc_objects.finalize();
+std.utf.validate(_insert_doc_objects.join); // TODO
+db.run(_insert_doc_objects.join.to!(char[]).toUTF8);
 #+END_SRC
 
 * __END__