aboutsummaryrefslogtreecommitdiffhomepage
path: root/org/output_sqlite.org
diff options
context:
space:
mode:
authorRalph Amissah <ralph@amissah.com>2018-04-23 12:07:22 -0400
committerRalph Amissah <ralph@amissah.com>2019-04-10 15:14:14 -0400
commitdb92f9af3293f8009543448a100b08b7a2f2ffbe (patch)
tree026d884db374b4ab6c575b2b56edc2526de4adc4 /org/output_sqlite.org
parentsqlite3 some stuff & rearrange files, dlang, org (diff)
0.26.1 single sqlite file, premature?
Diffstat (limited to 'org/output_sqlite.org')
-rw-r--r--org/output_sqlite.org280
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),