From f167586539641f92136ea6ffda73631973c20c43 Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Mon, 24 Jan 2022 15:40:24 -0500 Subject: sqlite: table, topic register --- org/meta_conf_make_meta.org | 56 +++++++++++++++++- org/out_sqlite.org | 140 +++++++++++++++++++++++--------------------- 2 files changed, 128 insertions(+), 68 deletions(-) (limited to 'org') diff --git a/org/meta_conf_make_meta.org b/org/meta_conf_make_meta.org index 1ac99e2..5dd581e 100644 --- a/org/meta_conf_make_meta.org +++ b/org/meta_conf_make_meta.org @@ -462,7 +462,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; @@ -1473,6 +1476,42 @@ if ("classify" in _yaml && _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; @@ -1488,8 +1527,23 @@ if ("classify" in _yaml 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); } } } 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; <> <> <> -<> +<> +<> <> <> <> @@ -187,7 +188,7 @@ template SQLiteFormatAndLoadObject() { mixin spineRgxOut; struct sqlite_format_and_load_objects { <> - <> + <> <> <> <> @@ -249,37 +250,50 @@ template SQLiteDeleteDocument() { string SQLiteDeleteDocument(M)( M doc_matters, ) { - <> - <> - <> + <> + <> + <> 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, ) { - <> - <> - <> - <> - <> - <> - <> - <> - <> - <> + <> + <> + <> + <> return _insert_metadata; } } #+END_SRC +**** insert metadata topics + +#+NAME: sqlite_insert_metadata_topics +#+BEGIN_SRC d +template SQLiteInsertMetadataTopics() { + string SQLiteInsertMetadataTopics(M)( + M doc_matters, + ) { + <> + <> + <> + <> + <> + } + 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) { - <> - <> - <> - <> + <> + <> + <> + <> return _insert_doc_objects_row; } <> @@ -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, -- cgit v1.2.3