aboutsummaryrefslogtreecommitdiffhomepage
path: root/org/out_sqlite.org
diff options
context:
space:
mode:
authorRalph Amissah <ralph.amissah@gmail.com>2022-01-24 15:40:24 -0500
committerRalph Amissah <ralph.amissah@gmail.com>2022-01-25 11:18:00 -0500
commitf167586539641f92136ea6ffda73631973c20c43 (patch)
treeb69219a14abf055848413805646c3256288e8a7c /org/out_sqlite.org
parentxmls, html group text output (bullets & indents) (diff)
sqlite: table, topic register
Diffstat (limited to 'org/out_sqlite.org')
-rw-r--r--org/out_sqlite.org140
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,