diff options
Diffstat (limited to 'org/output_sqlite.org')
| -rw-r--r-- | org/output_sqlite.org | 280 |
1 files changed, 174 insertions, 106 deletions
diff --git a/org/output_sqlite.org b/org/output_sqlite.org index 7fb843d..566775b 100644 --- a/org/output_sqlite.org +++ b/org/output_sqlite.org @@ -17,10 +17,67 @@ * sql ** 0. module, templates +*** hub +**** collection + #+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d module sdp.output.sqlite; <<output_imports>> +import d2sqlite3; +import std.typecons : Nullable; +mixin SiSUoutputRgxInit; +static auto rgx = Rgx(); long _metadata_tid_lastrowid; +template SQLiteHubBuildTablesAndPopulate() { + void SQLiteHubBuildTablesAndPopulate(D,I)( + auto ref const D doc_abstraction, + auto ref I doc_matters, + ) { + auto pth_sqlite = SiSUpathsSQLite!()(doc_matters.output_path); + pth_sqlite.base.mkdirRecurse; + auto db = Database(pth_sqlite.sqlite_file(doc_matters.env.pwd.baseName)); + template SQLiteDbStatementComposite() { + void SQLiteDbStatementComposite(Db,D,I)( + auto ref Db db, + auto ref const D doc_abstraction, + auto ref I doc_matters, + ) { + <<sqlite_db_statement_composite_collection>> + } + } + SQLiteDbStatementComposite!()(db, doc_abstraction, doc_matters); + } +} +#+END_SRC + +**** discrete + +#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d +template SQLiteHubDiscreteBuildTablesAndPopulate() { + void SQLiteHubDiscreteBuildTablesAndPopulate(D,I)( + auto ref const D doc_abstraction, + auto ref I doc_matters, + ) { + auto pth_sqlite = SiSUpathsSQLiteDiscrete!()(doc_matters.output_path, doc_matters.src.language); + pth_sqlite.base.mkdirRecurse; + auto db = Database(pth_sqlite.sqlite_file(doc_matters.src.filename)); + template SQLiteDiscreteDbStatementComposite() { + void SQLiteDiscreteDbStatementComposite(Db,D,I)( + auto ref Db db, + auto ref const D doc_abstraction, + auto ref I doc_matters, + ) { + <<sqlite_db_statement_composite_discrete>> + } + } + SQLiteDiscreteDbStatementComposite!()(db, doc_abstraction, doc_matters); + } +} +#+END_SRC + +*** db run + +#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d template SQLiteDbRun() { void SQLiteDbRun(Db,St,O)( auto ref Db db, @@ -37,7 +94,9 @@ template SQLiteDbRun() { db_statement ~ "\nCOMMIT;\n" ); - if (!(opt_action.sqlite_create)) { + if (!(opt_action.sqlite_discrete) + && !(opt_action.sqlite_create) + ) { _metadata_tid_lastrowid = db.lastInsertRowid(); writeln("last row id: ", _metadata_tid_lastrowid); } @@ -54,83 +113,70 @@ template SQLiteDbRun() { } } } -template SQLiteBuildTablesAndPopulate() { - void SQLiteBuildTablesAndPopulate(D,I)( - auto ref const D doc_abstraction, +#+END_SRC + +*** munge + +#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d +template SQLinsertDelimiter() { + auto SQLinsertDelimiter(string _txt) { + _txt = _txt + .replaceAll(rgx.quotation_mark_sql_insert_delimiter, "$0$0"); + return _txt; + } +} +template SQLiteFormatAndLoadObject() { + auto SQLiteFormatAndLoadObject(I)( auto ref I doc_matters, ) { - import d2sqlite3; - import std.typecons : Nullable; mixin SiSUoutputRgxInit; - static auto rgx = Rgx(); - auto pth_sqlite = SiSUpathsSQLite!()(doc_matters.output_path); - pth_sqlite.base.mkdirRecurse; - auto db = Database(pth_sqlite.sqlite_file(doc_matters.env.pwd.baseName)); - template SQLiteDbStatementComposite() { - void SQLiteDbStatementComposite(Db,D,I)( - auto ref Db db, - auto ref const D doc_abstraction, - auto ref I doc_matters, - ) { - <<sqlite_db_statement_composite>> - } - } - template SQLinsertDelimiter() { - auto SQLinsertDelimiter(string _txt) { - _txt = _txt - .replaceAll(rgx.quotation_mark_sql_insert_delimiter, "$0$0"); - return _txt; - } + struct sqlite_format_and_load_objects { + <<sanitize_text_for_search>> + <<sanitize_and_munge_inline_html>> + <<html_objects>> + <<sqlite_load_object>> + <<hub_format_and_sqlite_load_objects>> } - template SQLiteFormatAndLoadObject() { - auto SQLiteFormatAndLoadObject(I)( - auto ref I doc_matters, - ) { - mixin SiSUoutputRgxInit; - struct sqlite_format_and_load_objects { - <<sanitize_text_for_search>> - <<sanitize_and_munge_inline_html>> - <<html_objects>> - <<sqlite_load_object>> - <<hub_format_and_sqlite_load_objects>> - } - return sqlite_format_and_load_objects(); - } - } - template SQLiteTablesReCreate() { - string SQLiteTablesReCreate()() { - string _sql_instruct; - _sql_instruct = format(q"¶ - <<sqlite_statement_drop_existing_index>> - <<sqlite_statement_drop_existing_tables>> - <<sqlite_statement_create_table_metadata_and_src_txt>> - <<sqlite_statement_create_table_objects>> - <<sqlite_statement_create_index>> - ¶",); - return _sql_instruct; - } - } - template SQLiteInsertMetadata() { - string SQLiteInsertMetadata(I)( - auto ref I doc_matters, - ) { - <<sqlite_formatted_insertions_doc_matters_metadata>> - return _insert_metadata; - } - } - template SQLiteInsertDocObjectsLoop() { - string SQLiteInsertDocObjectsLoop(P,I)( - auto ref P doc_parts, - auto ref I _metadata_tid, - ) { - string insertDocObjectsRow(O)(O obj) { - <<sqlite_formatted_insertions_doc_objects>> - return _insert_doc_objects_row; - } - <<sqlite_objects_loop>> - } + return sqlite_format_and_load_objects(); + } +} +#+END_SRC + +*** sqlite instructions + +#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d +template SQLiteTablesReCreate() { + string SQLiteTablesReCreate()() { + string _sql_instruct; + _sql_instruct = format(q"¶ + <<sqlite_statement_drop_existing_index>> + <<sqlite_statement_drop_existing_tables>> + <<sqlite_statement_create_table_metadata_and_src_txt>> + <<sqlite_statement_create_table_objects>> + <<sqlite_statement_create_index>> + ¶",); + return _sql_instruct; + } +} +template SQLiteInsertMetadata() { + string SQLiteInsertMetadata(I)( + auto ref I doc_matters, + ) { + <<sqlite_formatted_insertions_doc_matters_metadata>> + return _insert_metadata; + } +} +template SQLiteInsertDocObjectsLoop() { + string SQLiteInsertDocObjectsLoop(D,I,X)( + auto ref const D doc_abstraction, + auto ref I doc_matters, + auto ref X _metadata_tid, + ) { + string insertDocObjectsRow(O)(O obj) { + <<sqlite_formatted_insertions_doc_objects>> + return _insert_doc_objects_row; } - SQLiteDbStatementComposite!()(db, doc_abstraction, doc_matters); + <<sqlite_objects_loop>> } } template SQLiteTablesCreate() { @@ -169,30 +215,45 @@ template SQLiteTablesDrop() { #+END_SRC ** 1. [#A] sqlite_db_statement +*** collection -#+name: sqlite_db_statement_composite +#+name: sqlite_db_statement_composite_collection #+BEGIN_SRC d { + string _db_statement; { - string _db_statement; - { - if ((doc_matters.opt.action.sqlite_create)) { - auto pth_sqlite = SiSUpathsSQLiteDiscrete!()(doc_matters.output_path, doc_matters.src.language); - pth_sqlite.base.mkdirRecurse; - _db_statement ~= SQLiteTablesReCreate!()(); - SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "TABLE RE-CREATE"); - } - if ((doc_matters.opt.action.sqlite_update)) { // TODO - _db_statement ~= SQLiteInsertMetadata!()(doc_matters); - SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "table INSERT MetaData"); - /+ get tid (lastrowid or max) for use in doc_objects table +/ - _metadata_tid_lastrowid = db.lastInsertRowid(); - _db_statement ~= SQLiteInsertDocObjectsLoop!()(doc_matters.xml.keys_seq.sql, _metadata_tid_lastrowid); // FIX - SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "table INSERT DocObjects"); - } + if ((doc_matters.opt.action.sqlite_create)) { + auto pth_sqlite = SiSUpathsSQLite!()(doc_matters.output_path); + pth_sqlite.base.mkdirRecurse; + _db_statement ~= SQLiteTablesReCreate!()(); + SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "TABLE RE-CREATE"); + } + if ((doc_matters.opt.action.sqlite_update)) { // TODO + _db_statement ~= SQLiteInsertMetadata!()(doc_matters); + SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "table INSERT MetaData"); + /+ get tid (lastrowid or max) for use in doc_objects table +/ + _metadata_tid_lastrowid = db.lastInsertRowid(); + _db_statement ~= SQLiteInsertDocObjectsLoop!()(doc_abstraction, doc_matters, _metadata_tid_lastrowid); // FIX + SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "table INSERT DocObjects"); } - db.close; } + db.close; +} +#+END_SRC + +*** discrete + +#+name: sqlite_db_statement_composite_discrete +#+BEGIN_SRC d +{ + string _db_statement; + { + _db_statement ~= SQLiteTablesReCreate!()(); + _db_statement ~= SQLiteInsertMetadata!()(doc_matters); + _db_statement ~= SQLiteInsertDocObjectsLoop!()(doc_abstraction, doc_matters, 1); // FIX + SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "table CREATE Tables, INSERT DocObjects"); + } + db.close; } #+END_SRC @@ -869,7 +930,7 @@ auto format_and_sqlite_load = SQLiteFormatAndLoadObject!()(doc_matters); string[string] obj_txt; string doc_text; string[] _insert_doc_objects; -foreach (part; doc_parts) { +foreach (part; doc_matters.xml.keys_seq.sql) { foreach (obj; doc_abstraction[part]) { switch (obj.of_part) { case "frontmatter": assert(part == "head", part); @@ -1023,7 +1084,6 @@ return _insert_doc_objects.join.to!(char[]).toUTF8; ** 5. SQL statements *** create tables - **** DROP INDEX IF EXISTS #+name: sqlite_statement_drop_existing_index @@ -1061,7 +1121,8 @@ DROP TABLE IF EXISTS urls; #+BEGIN_SRC sql CREATE TABLE metadata_and_text ( tid INTEGER PRIMARY KEY, -/* src_filename_composite VARCHAR(256) NOT NULL UNIQUE, /* z pod name if any + src filename + language code */ + 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 UNIQUE, /* z pod name if any + src filename + language code */ title VARCHAR(800) NOT NULL, title_main VARCHAR(400) NOT NULL, title_sub VARCHAR(400) NULL, @@ -1208,9 +1269,8 @@ CREATE INDEX idx_language ON metadata_and_text(language_document_char); CREATE INDEX idx_topics ON metadata_and_text(classify_topic_register); #+END_SRC -**** TODO local site link & info - -*** sql insert statement formatted doc objects +*** inserts +**** INSERT doc objects lid unique, increment by 1 per object, not ocn @@ -1219,14 +1279,15 @@ either: - increment by adding 1 for each document, - make hash of document filename or url and use? -**** sql statement: dlang format +***** sql statement: dlang format + #+name: sqlite_formatted_insertions_doc_objects #+BEGIN_SRC d string _insert_doc_objects_row; _insert_doc_objects_row = format(q"¶ #+END_SRC -**** INSERT INTO +***** INSERT INTO #+name: sqlite_formatted_insertions_doc_objects #+BEGIN_SRC sql @@ -1241,7 +1302,7 @@ _insert_doc_objects_row = format(q"¶ ) #+END_SRC -**** VALUES +***** VALUES #+name: sqlite_formatted_insertions_doc_objects #+BEGIN_SRC sql @@ -1256,7 +1317,7 @@ _insert_doc_objects_row = format(q"¶ ); #+END_SRC -**** sql statement: dlang values for formatting +***** dlang values for formatting #+name: sqlite_formatted_insertions_doc_objects #+BEGIN_SRC d @@ -1271,20 +1332,23 @@ _insert_doc_objects_row = format(q"¶ ); #+END_SRC -*** sqlite insert statement formatted doc matters metadata -**** sql statement: dlang format +**** INSERT doc matters metadata +***** sql statement: dlang format + #+name: sqlite_formatted_insertions_doc_matters_metadata #+BEGIN_SRC d string _insert_metadata; _insert_metadata = format(q"¶ #+END_SRC -**** INSERT INTO +***** INSERT INTO #+name: sqlite_formatted_insertions_doc_matters_metadata #+BEGIN_SRC sql INSERT INTO metadata_and_text ( src_filename, + src_composite_id_per_txt, + src_composite_id_per_pod, title, title_main, title_sub, @@ -1331,7 +1395,7 @@ _insert_metadata = format(q"¶ ) #+END_SRC -**** VALUES +***** VALUES #+name: sqlite_formatted_insertions_doc_matters_metadata #+BEGIN_SRC sql @@ -1379,16 +1443,20 @@ _insert_metadata = format(q"¶ '%s', '%s', '%s', + '%s', + '%s', '%s' ); #+END_SRC -**** sql statement: values for formatting +***** dlang values for formatting #+name: sqlite_formatted_insertions_doc_matters_metadata #+BEGIN_SRC d ¶", SQLinsertDelimiter!()(doc_matters.src.filename), + SQLinsertDelimiter!()(doc_matters.src.docname_composite_unique_per_src_doc), + SQLinsertDelimiter!()(doc_matters.src.docname_composite_unique_per_src_pod), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.title_full), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.title_main), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.title_subtitle), |