sqlite: table, topic register

1 files changed, 73 insertions, 67 deletions

diff --git a/org/out_sqlite.org b/org/out_sqlite.org
index ecbfbe9..772604b 100644
--- a/org/out_sqlite.org
+++ b/org/out_sqlite.org
@@ -54,7 +54,8 @@ module doc_reform.io_out.sqlite;
 <<sqlite_munge>>
 <<sqlite_create>>
 <<sqlite_delete>>
-<<sqlite_insert>>
+<<sqlite_insert_metadata>>
+<<sqlite_insert_metadata_topics>>
 <<sqlite_insert_doc_objects_loop>>
 <<sqlite_tables_create>>
 <<sqlite_tables_drop>>
@@ -187,7 +188,7 @@ template SQLiteFormatAndLoadObject() {
     mixin spineRgxOut;
     struct sqlite_format_and_load_objects {
       <<sanitize_text_for_search>>
-      <<sanitize_and_munge_inline_html_0>>
+      <<sanitize_and_munge_inline_html_munge>>
       <<sanitize_and_munge_inline_html_special_characters>>
       <<sanitize_and_munge_inline_html_special_characters_code>>
       <<sanitize_and_munge_inline_html_font_face>>
@@ -249,37 +250,50 @@ template SQLiteDeleteDocument() {
   string SQLiteDeleteDocument(M)(
     M doc_matters,
   ) {
-    <<sqlite_formatted_delete_0>>
-    <<sqlite_formatted_delete_1>>
-    <<sqlite_formatted_delete_2>>
+    <<sqlite_formatted_delete_format>>
+    <<sqlite_formatted_delete_sql>>
+    <<sqlite_formatted_delete_values>>
     return _delete_uid;
   }
 }
 #+END_SRC
 
-**** insert
+**** insert metadata
 
-#+NAME: sqlite_insert
+#+NAME: sqlite_insert_metadata
 #+BEGIN_SRC d
 template SQLiteInsertMetadata() {
   string SQLiteInsertMetadata(M)(
     M doc_matters,
   ) {
-    <<sqlite_formatted_insertions_doc_matters_metadata_0>>
-      <<sqlite_formatted_insertions_doc_matters_metadata_1>>
-      <<sqlite_formatted_insertions_doc_matters_metadata_2>>
-    <<sqlite_formatted_insertions_doc_matters_metadata_3>>
-    <<sqlite_formatted_insertions_topic_register_0>>
-    <<sqlite_formatted_insertions_topic_register_1>>
-      <<sqlite_formatted_insertions_topic_register_2>>
-      <<sqlite_formatted_insertions_topic_register_3>>
-    <<sqlite_formatted_insertions_topic_register_4>>
-    <<sqlite_formatted_insertions_topic_register_5>>
+    <<sqlite_formatted_insertions_doc_matters_metadata_format>>
+      <<sqlite_formatted_insertions_doc_matters_metadata_sql>>
+      <<sqlite_formatted_insertions_doc_matters_metadata_sql_values>>
+    <<sqlite_formatted_insertions_doc_matters_metadata_values>>
     return _insert_metadata;
   }
 }
 #+END_SRC
 
+**** insert metadata topics
+
+#+NAME: sqlite_insert_metadata_topics
+#+BEGIN_SRC d
+template SQLiteInsertMetadataTopics() {
+  string SQLiteInsertMetadataTopics(M)(
+    M doc_matters,
+  ) {
+    <<sqlite_formatted_insertions_topic_register_loop>>
+      <<sqlite_formatted_insertions_topic_register_format>>
+        <<sqlite_formatted_insertions_topic_register_sql>>
+        <<sqlite_formatted_insertions_topic_register_sql_values>>
+      <<sqlite_formatted_insertions_topic_register_values>>
+    }
+    return _insert_topics.join.to!(char[]).toUTF8;
+  }
+}
+#+END_SRC
+
 **** insert doc objects loop
 
 #+NAME: sqlite_insert_doc_objects_loop
@@ -292,10 +306,10 @@ template SQLiteInsertDocObjectsLoop() {
     string _uid = SQLinsertDelimiter!()(doc_matters.src.doc_uid);
     auto url_html = spineUrlsHTML!()(doc_matters.conf_make_meta.conf.w_srv_data_root_url_html, doc_matters.src.language);
     string insertDocObjectsRow(O)(O obj) {
-      <<sqlite_formatted_insertions_doc_objects_0>>
-        <<sqlite_formatted_insertions_doc_objects_1>>
-        <<sqlite_formatted_insertions_doc_objects_2>>
-      <<sqlite_formatted_insertions_doc_objects_3>>
+      <<sqlite_formatted_insertions_doc_objects_format>>
+        <<sqlite_formatted_insertions_doc_objects_sql>>
+        <<sqlite_formatted_insertions_doc_objects_sql_values>>
+      <<sqlite_formatted_insertions_doc_objects_values>>
       return _insert_doc_objects_row;
     }
     <<sqlite_objects_loop>>
@@ -406,19 +420,27 @@ template SQLiteDbDrop() {
       pth_sqlite.base.mkdirRecurse;
       _db_statement ~= SQLiteTablesReCreate!()();
       SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "TABLE RE-CREATE");
+      _db_statement = [];
     }
     if (doc_matters.opt.action.sqlite_delete) {
       _db_statement ~= SQLiteDeleteDocument!()(doc_matters);
       SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "DELETE Document");
+      _db_statement = [];
     }
     if (doc_matters.opt.action.sqlite_update) {
       _db_statement ~= SQLiteDeleteDocument!()(doc_matters);
       SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "DELETE Document");
+      _db_statement = [];
       _db_statement ~= SQLiteInsertMetadata!()(doc_matters);
       SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "INSERT MetaData");
+      _db_statement = [];
       /+ 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_statement = [];
+     _db_statement ~= SQLiteInsertMetadataTopics!()(doc_matters);
+      SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "INSERT MetaDataTopics");
+      _db_statement = [];
     }
   }
   db.close;
@@ -434,6 +456,7 @@ template SQLiteDbDrop() {
     string _db_statement;
     _db_statement ~= SQLiteTablesReCreate!()();
     _db_statement ~= SQLiteInsertMetadata!()(doc_matters);
+    _db_statement ~= SQLiteInsertMetadataTopics!()(doc_matters);
     _db_statement ~= doc_abstraction.SQLiteInsertDocObjectsLoop!()(doc_matters);
     SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "table CREATE Tables, INSERT DocObjects");
   }
@@ -517,7 +540,7 @@ light html objects
 ***** munge
 ****** general munge (special characters, inline markup, move notes)
 
-#+NAME: sanitize_and_munge_inline_html_0
+#+NAME: sanitize_and_munge_inline_html_munge
 #+BEGIN_SRC d
 string munge_html(M,O)(
         M    doc_matters,
@@ -1582,6 +1605,7 @@ DROP INDEX IF EXISTS idx_author;
 DROP INDEX IF EXISTS src_filename_base;
 DROP INDEX IF EXISTS idx_language_document_char;
 DROP INDEX IF EXISTS idx_classify_topic_register;
+DROP INDEX IF EXISTS idx_topic_list;
 #+END_SRC
 
 **** DROP TABLE IF EXISTS
@@ -1699,11 +1723,7 @@ CREATE TABLE IF NOT EXISTS topic_register (
   uid_metadata_and_text            VARCHAR(256)      REFERENCES metadata_and_text(uid) ON DELETE CASCADE,
   -- 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
-  topic_register_lv0               VARCHAR(250)  NOT NULL,
-  topic_register_lv1               VARCHAR(250)      NULL,
-  topic_register_lv2               VARCHAR(250)      NULL,
-  topic_register_lv3               VARCHAR(250)      NULL,
-  topic_register_lv4               VARCHAR(250)      NULL,
+  topic_register                   VARCHAR(250)  NOT NULL,
   site_url_doc_root                VARCHAR(256)      NULL, -- url path to doc root
   site_url_html_toc                VARCHAR(256)      NULL,
   site_url_html_scroll             VARCHAR(256)      NULL
@@ -1765,6 +1785,7 @@ CREATE INDEX IF NOT EXISTS idx_uid          ON metadata_and_text(uid);
 CREATE INDEX IF NOT EXISTS idx_filename     ON metadata_and_text(src_filename_base);
 CREATE INDEX IF NOT EXISTS idx_language     ON metadata_and_text(language_document_char);
 CREATE INDEX IF NOT EXISTS idx_topics       ON metadata_and_text(classify_topic_register);
+CREATE INDEX IF NOT EXISTS idx_topic_list   ON topic_register(topic_register);
 #+END_SRC
 
 *** TODO local site link & info
@@ -1773,7 +1794,7 @@ CREATE INDEX IF NOT EXISTS idx_topics       ON metadata_and_text(classify_topic_
 **** DELETE uid rows doc matters & metadata
 ***** sql statement: dlang format
 
-#+NAME: sqlite_formatted_delete_0
+#+NAME: sqlite_formatted_delete_format
 #+BEGIN_SRC d
 string _uid = doc_matters.src.doc_uid;
 string _delete_uid = format(q"┃
@@ -1781,7 +1802,7 @@ string _delete_uid = format(q"┃
 
 ***** DELETE FROM ... WHERE
 
-#+NAME: sqlite_formatted_delete_1
+#+NAME: sqlite_formatted_delete_sql
 #+BEGIN_SRC sql
 DELETE FROM metadata_and_text
 WHERE uid = '%s';
@@ -1791,7 +1812,7 @@ WHERE uid_metadata_and_text = '%s';
 
 ***** VALUES
 
-#+NAME: sqlite_formatted_delete_2
+#+NAME: sqlite_formatted_delete_values
 #+BEGIN_SRC d
 ┃",
   _uid,
@@ -1803,7 +1824,7 @@ WHERE uid_metadata_and_text = '%s';
 **** INSERT doc matters & metadata
 ***** sql statement: dlang format
 
-#+NAME: sqlite_formatted_insertions_doc_matters_metadata_0
+#+NAME: sqlite_formatted_insertions_doc_matters_metadata_format
 #+BEGIN_SRC d
 string _uid = SQLinsertDelimiter!()(doc_matters.src.doc_uid);
 string _insert_metadata = format(q"┃
@@ -1811,7 +1832,7 @@ string _insert_metadata = format(q"┃
 
 ***** INSERT INTO
 
-#+NAME: sqlite_formatted_insertions_doc_matters_metadata_1
+#+NAME: sqlite_formatted_insertions_doc_matters_metadata_sql
 #+BEGIN_SRC sql
 INSERT INTO metadata_and_text (
   uid,
@@ -1869,7 +1890,7 @@ INSERT INTO metadata_and_text (
 
 ***** VALUES
 
-#+NAME: sqlite_formatted_insertions_doc_matters_metadata_2
+#+NAME: sqlite_formatted_insertions_doc_matters_metadata_sql_values
 #+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', '%s', '%s'
@@ -1878,7 +1899,7 @@ VALUES (
 
 ***** dlang values for formatting
 
-#+NAME: sqlite_formatted_insertions_doc_matters_metadata_3
+#+NAME: sqlite_formatted_insertions_doc_matters_metadata_values
 #+BEGIN_SRC d
 ┃",
   _uid,
@@ -1938,68 +1959,53 @@ VALUES (
 
 writeln(doc_matters.conf_make_meta.meta.classify_topic_register_arr);
 
-***** { if topic register then loop topic register array
+***** loop sql statement: dlang format
 
-#+NAME: sqlite_formatted_insertions_topic_register_0
+#+NAME: sqlite_formatted_insertions_topic_register_loop
 #+BEGIN_SRC d
-if (doc_matters.conf_make_meta.meta.classify_topic_register_arr.length > 0) {
-  string _insert_topics;
-  foreach (topic; doc_matters.conf_make_meta.meta.classify_topic_register_arr) {
-    string[] subject_tree = topic.split(mkup.sep);
+string _uid = SQLinsertDelimiter!()(doc_matters.src.doc_uid);
+string[] _insert_topics;
+foreach (topic_line; doc_matters.conf_make_meta.meta.classify_topic_register_expanded_arr) {
+  // writeln(topic_line);
 #+END_SRC
 
 ***** sql statement: dlang format
 
-#+NAME: sqlite_formatted_insertions_topic_register_1
+#+NAME: sqlite_formatted_insertions_topic_register_format
 #+BEGIN_SRC d
 _insert_topics ~= format(q"┃
 #+END_SRC
 
 ***** INSERT INTO
 
-#+NAME: sqlite_formatted_insertions_topic_register_2
+#+NAME: sqlite_formatted_insertions_topic_register_sql
 #+BEGIN_SRC sql
 INSERT INTO topic_register (
   uid_metadata_and_text,
-  topic_register_lv0,
-  topic_register_lv1,
-  topic_register_lv2,
-  topic_register_lv3,
-  topic_register_lv4
+  topic_register
 )
 #+END_SRC
 
 ***** VALUES
 
-#+NAME: sqlite_formatted_insertions_topic_register_3
+#+NAME: sqlite_formatted_insertions_topic_register_sql_values
 #+BEGIN_SRC sql
 VALUES (
-  '%s', '%s', '%s', '%s', '%s', '%s'
+  '%s',
+  '%s'
 );
 #+END_SRC
 
 ***** dlang values for formatting
 
-#+NAME: sqlite_formatted_insertions_topic_register_4
+#+NAME: sqlite_formatted_insertions_topic_register_values
 #+BEGIN_SRC d
 ┃",
   _uid,
-  (subject_tree.length > 0) ? subject_tree[0] : "",
-  (subject_tree.length > 1) ? subject_tree[1] : "",
-  (subject_tree.length > 2) ? subject_tree[2] : "",
-  (subject_tree.length > 3) ? subject_tree[3] : "",
-  (subject_tree.length > 4) ? subject_tree[4] : ""
+  SQLinsertDelimiter!()(topic_line)
 );
 #+END_SRC
 
-***** } close topic register & loop topic register array
-
-#+NAME: sqlite_formatted_insertions_topic_register_5
-#+BEGIN_SRC d
-  }
-}
-#+END_SRC
-
 **** INSERT doc objects
 
 lid unique, increment by 1 per object, not ocn
@@ -2011,14 +2017,14 @@ either:
 
 ***** sql statement: dlang format
 
-#+NAME: sqlite_formatted_insertions_doc_objects_0
+#+NAME: sqlite_formatted_insertions_doc_objects_format
 #+BEGIN_SRC d
 string _insert_doc_objects_row = format(q"┃
 #+END_SRC
 
 ***** INSERT INTO
 
-#+NAME: sqlite_formatted_insertions_doc_objects_1
+#+NAME: sqlite_formatted_insertions_doc_objects_sql
 #+BEGIN_SRC sql
 INSERT INTO doc_objects (
   uid_metadata_and_text,
@@ -2035,7 +2041,7 @@ INSERT INTO doc_objects (
 
 ***** VALUES
 
-#+NAME: sqlite_formatted_insertions_doc_objects_2
+#+NAME: sqlite_formatted_insertions_doc_objects_sql_values
 #+BEGIN_SRC sql
 VALUES (
   '%s', %s, '%s', '%s', '%s', %s, '%s', '%s', '%s'
@@ -2044,7 +2050,7 @@ VALUES (
 
 ***** dlang values for formatting
 
-#+NAME: sqlite_formatted_insertions_doc_objects_3
+#+NAME: sqlite_formatted_insertions_doc_objects_values
 #+BEGIN_SRC d
 ┃",
   _uid,