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, 55 insertions, 111 deletions

diff --git a/src/sdp/output/sqlite_discrete.d b/src/sdp/output/sqlite_discrete.d
index 541c4d8..443fb3b 100644
--- a/src/sdp/output/sqlite_discrete.d
+++ b/src/sdp/output/sqlite_discrete.d
@@ -23,7 +23,6 @@ template SQLiteDiscreteBuildTablesAndPopulate() {
           ) {
             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"];
@@ -102,7 +101,7 @@ template SQLiteDiscreteBuildTablesAndPopulate() {
             return _txt;
           }
           string html_special_characters(string _txt){
-            _txt = (_txt)
+            _txt = _txt
               .replaceAll(rgx.xhtml_ampersand,    "&")
               .replaceAll(rgx.xhtml_quotation,    """)
               .replaceAll(rgx.xhtml_less_than,    "<")
@@ -112,7 +111,7 @@ template SQLiteDiscreteBuildTablesAndPopulate() {
             return _txt;
           }
           string html_special_characters_code(string _txt){
-            _txt = (_txt)
+            _txt = _txt
               .replaceAll(rgx.xhtml_ampersand,    "&")
               .replaceAll(rgx.xhtml_quotation,    """)
               .replaceAll(rgx.xhtml_less_than,    "<")
@@ -121,7 +120,7 @@ template SQLiteDiscreteBuildTablesAndPopulate() {
             return _txt;
           }
           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>")
@@ -461,13 +460,10 @@ template SQLiteDiscreteBuildTablesAndPopulate() {
       }
     }
     template SQLiteInstruct() {
-      Statement SQLiteInstruct(I)(
+      void SQLiteInstruct(Db,I)(
+                              Db   db,
         auto ref              I    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));
-        // auto db = Database(":memory:"); // open database in memory
         db.run("
           DROP TABLE IF EXISTS metadata_and_text;
           DROP TABLE IF EXISTS doc_objects;
@@ -824,93 +820,60 @@ template SQLiteDiscreteBuildTablesAndPopulate() {
         // 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");
         }
         assert(db.totalChanges == 1);
         //
-        Statement _insert_doc_objects = db.prepare("
-          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
-          )
-          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
-          )
-        ");
-        return _insert_doc_objects;
       }
     }
     template SQLiteObjectsLoop() {
       void SQLiteObjectsLoop(P)(
         auto ref P          doc_parts,
       ) {
-        Statement insert_doc_objects = SQLiteInstruct!()(doc_matters);
+        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"¶
+            INSERT INTO doc_objects (
+              ocn,
+              clean,
+              body,
+              lev,
+              t_of,
+              t_is
+            )
+            VALUES (
+              %s,
+              '%s',
+              '%s',
+              %s,
+              '%s',
+              '%s'
+            );
+          ¶",
+            obj.ocn,
+            sql_insert_delimiter(obj_txt["text"]),
+            sql_insert_delimiter(obj_txt["html"]),
+            obj.heading_lev_markup,
+            obj.is_of,
+            obj.is_a,
+          );
+          return _insert_doc_objects_row;
+        }
+        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) {
@@ -1055,36 +1018,17 @@ template SQLiteDiscreteBuildTablesAndPopulate() {
                 );
               }
             }
-            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);
       }
     }
     SQLiteObjectsLoop!()(doc_matters.xml.keys_seq.sql);