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 --- src/doc_reform/io_out/sqlite.d | 74 ++++++++++++++++++++++-------------------- 1 file changed, 39 insertions(+), 35 deletions(-) (limited to 'src/doc_reform/io_out') 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; } -- cgit v1.2.3