sqlite: table, topic register

3 files changed, 94 insertions, 36 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;
       }
diff --git a/src/doc_reform/meta/conf_make_meta_structs.d b/src/doc_reform/meta/conf_make_meta_structs.d
index 7b5fe1d..878e87b 100644
--- a/src/doc_reform/meta/conf_make_meta_structs.d
+++ b/src/doc_reform/meta/conf_make_meta_structs.d
@@ -249,7 +249,10 @@ struct MetaComposite {
   string   classify_loc;
   string   classify_subject;
   string   classify_topic_register;
+  string   classify_topic_register_json; // experimental use in sqlite topics table
   string[] classify_topic_register_arr;
+  string[] classify_topic_register_json_arr; // experimental use in sqlite topics table
+  string[] classify_topic_register_expanded_arr; // experimental use in sqlite topics table
   string[] creator_author_arr;
   string   creator_author;
   string   creator_author_surname_fn;
diff --git a/src/doc_reform/meta/conf_make_meta_yaml.d b/src/doc_reform/meta/conf_make_meta_yaml.d
index f1959d0..71befdc 100644
--- a/src/doc_reform/meta/conf_make_meta_yaml.d
+++ b/src/doc_reform/meta/conf_make_meta_yaml.d
@@ -956,6 +956,42 @@ template contentYAMLtoSpineStruct() {
             && _yaml["classify"]["topic_register"].tag.match(rgx.yaml_tag_is_str)
           ) {
             _struct_composite.meta.classify_topic_register = _yaml["classify"]["topic_register"].get!string;
+            if (_struct_composite.meta.classify_topic_register.length > 0) {
+              auto wrds = ctRegex!(`([\wa-zA-Z(). -]+)`); // ctRegex!(`([(]?\w+[a-zA-Z(). -]*)+`);
+              auto mkp_delim = ctRegex!(`:([^:]+?)(;|$)`);
+              string _topic_register = _struct_composite.meta.classify_topic_register;
+              _topic_register = _topic_register
+                .replaceAll(wrds, "\"$1\"")
+                .replaceAll(mkp_delim, ":$1$2");
+              string[] _topics_json_arr;
+              //+
+              foreach (_topic; _topic_register.split(";")) {
+                while (_topic.match(":")) {
+                  foreach (m; _topic.match(":")) {
+                    _topic = m.pre ~ " ☼ [ " ~ m.post ~ " ] ";
+                  }
+                }
+                _topics_json_arr ~= ("  ") ~ (_topic
+                  .split("☼").join(":")
+                  .split("|").join(", "));
+              }
+              // +/
+              /+
+              foreach (_topic; _topic_register.split(";")) {
+                while (_topic.match(":")) {
+                  foreach (m; _topic.match(":")) {
+                    _topic = m.pre ~ " ☼ { " ~ m.post ~ " } ";
+                  }
+                }
+                _topics_json_arr ~= (" { ") ~ (_topic
+                  .split("☼").join(":")
+                  .split("|").join(", "))
+                  ~ " }";
+              }
+              +/
+              _struct_composite.meta.classify_topic_register_json_arr = _topics_json_arr;
+              _struct_composite.meta.classify_topic_register_json = "{\n" ~ _topics_json_arr.join(",\n") ~ "\n}";
+            }
             string[] main_topics_ = _struct_composite.meta.classify_topic_register.strip.split(rgx.topic_register_main_terms_split);
             string[] topics;
             string   topics_tmp;
@@ -971,8 +1007,23 @@ template contentYAMLtoSpineStruct() {
                 topics ~= topics_tmp;
               }
             }
-            // writeln("--> ", topics);
             _struct_composite.meta.classify_topic_register_arr = topics;
+            string[] topics_expanded;
+            if (_struct_composite.meta.classify_topic_register_arr.length > 0) {
+              foreach (i, topic; _struct_composite.meta.classify_topic_register_arr) {
+                string[] subject_tree = topic.split(mkup.sep);
+                if (topic.length > 0) {
+                  topics_expanded ~= subject_tree.join(", ");
+                }
+              }
+            }
+            _struct_composite.meta.classify_topic_register_expanded_arr = topics_expanded;
+            // writeln("\n------\n", _struct_composite.meta.title_full);
+            // writeln(_struct_composite.meta.classify_topic_register);
+            // writeln(_struct_composite.meta.classify_topic_register_json_arr);
+            // writeln(_struct_composite.meta.classify_topic_register_json);
+            // writeln(_struct_composite.meta.classify_topic_register_expanded_arr.sort.join("\n"));
+            // writeln(_struct_composite.meta.classify_topic_register_arr);
           }
         }
       }