sqlite: table, topic register

1 files changed, 39 insertions, 35 deletions

diff --git a/src/doc_reform/io_out/sqlite.d b/src/doc_reform/io_out/sqlite.d
index c1b55de..60f6569 100644
--- a/src/doc_reform/io_out/sqlite.d
+++ b/src/doc_reform/io_out/sqlite.d
@@ -83,19 +83,27 @@ template SQLiteHubBuildTablesAndPopulate() {
               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;
@@ -128,6 +136,7 @@ template SQLiteHubDiscreteBuildTablesAndPopulate() {
             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");
           }
@@ -924,6 +933,7 @@ template SQLiteTablesReCreate() {
       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;
       DROP TABLE IF EXISTS metadata_and_text;
       DROP TABLE IF EXISTS topic_register;
       DROP TABLE IF EXISTS doc_objects;
@@ -1022,11 +1032,7 @@ template SQLiteTablesReCreate() {
         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
@@ -1061,6 +1067,7 @@ template SQLiteTablesReCreate() {
       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);
     ┃",);
     return _sql_instruct;
   }
@@ -1195,33 +1202,32 @@ template SQLiteInsertMetadata() {
       SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.publisher),
       SQLinsertDelimiter!()(doc_matters.conf_make_meta.conf.w_srv_data_root_url_html)
     );
-    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);
-    _insert_topics ~= format(q"┃
-      INSERT INTO topic_register (
-        uid_metadata_and_text,
-        topic_register_lv0,
-        topic_register_lv1,
-        topic_register_lv2,
-        topic_register_lv3,
-        topic_register_lv4
-      )
-      VALUES (
-        '%s', '%s', '%s', '%s', '%s', '%s'
+    return _insert_metadata;
+  }
+}
+template SQLiteInsertMetadataTopics() {
+  string SQLiteInsertMetadataTopics(M)(
+    M doc_matters,
+  ) {
+    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);
+      _insert_topics ~= format(q"┃
+        INSERT INTO topic_register (
+          uid_metadata_and_text,
+          topic_register
+        )
+        VALUES (
+          '%s',
+          '%s'
+        );
+      ┃",
+        _uid,
+        SQLinsertDelimiter!()(topic_line)
       );
-    ┃",
-      _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] : ""
-    );
-      }
     }
-    return _insert_metadata;
+    return _insert_topics.join.to!(char[]).toUTF8;
   }
 }
 template SQLiteInsertDocObjectsLoop() {
@@ -1454,6 +1460,7 @@ template SQLiteTablesCreate() {
           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;
           DROP TABLE IF EXISTS metadata_and_text;
           DROP TABLE IF EXISTS topic_register;
           DROP TABLE IF EXISTS doc_objects;
@@ -1552,11 +1559,7 @@ template SQLiteTablesCreate() {
             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
@@ -1591,6 +1594,7 @@ template SQLiteTablesCreate() {
           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);
         ┃",);
         return _sql_instruct;
       }