From 096d12cb15e191dbd83f3399ba9bfef57bc9d826 Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Wed, 11 Apr 2018 21:37:45 -0400 Subject: 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 --- org/output_sqlite_discrete.org | 182 +++++++++++++++-------------------------- 1 file changed, 67 insertions(+), 115 deletions(-) (limited to 'org/output_sqlite_discrete.org') 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, ) { <> @@ -56,13 +57,13 @@ template SQLiteDiscreteBuildTablesAndPopulate() { <> <> //<> - <> } } template SQLiteObjectsLoop() { void SQLiteObjectsLoop(P)( auto ref P doc_parts, ) { + <> <> } } @@ -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, "&") .replaceAll(rgx.xhtml_quotation, """) .replaceAll(rgx.xhtml_less_than, "<") @@ -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, "&") .replaceAll(rgx.xhtml_quotation, """) .replaceAll(rgx.xhtml_less_than, "<") @@ -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, "$1") .replaceAll(rgx.inline_bold, "$1") .replaceAll(rgx.inline_underscore, "$1") @@ -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__ -- cgit v1.2.3