0.26.5 sqlite output, focus

1 files changed, 152 insertions, 112 deletions

diff --git a/org/output_sqlite.org b/org/output_sqlite.org
index dc76da4..eeff4f1 100644
--- a/org/output_sqlite.org
+++ b/org/output_sqlite.org
@@ -33,19 +33,20 @@ 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,
+    D    doc_abstraction,
+    I    doc_matters,
   ) {
-    auto pth_sqlite = SiSUpathsSQLite!()(doc_matters.output_path);
+    auto pth_sqlite = SiSUpathsSQLite!()(doc_matters.sqlite.filename, doc_matters.output_path);
     pth_sqlite.base.mkdirRecurse;
-    auto db = Database(pth_sqlite.sqlite_file(doc_matters.env.pwd.baseName));
+    auto db = Database(pth_sqlite.sqlite_file);
     template SQLiteDbStatementComposite() {
       void SQLiteDbStatementComposite(Db,D,I)(
-        auto ref       Db   db,
-        auto ref const D    doc_abstraction,
-        auto ref       I    doc_matters,
+        Db   db,
+        D    doc_abstraction,
+        I    doc_matters,
       ) {
         <<sqlite_db_statement_composite_collection>>
+        writeln(" ", pth_sqlite.sqlite_file);
       }
     }
     SQLiteDbStatementComposite!()(db, doc_abstraction, doc_matters);
@@ -58,19 +59,20 @@ template SQLiteHubBuildTablesAndPopulate() {
 #+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,
+    D    doc_abstraction,
+    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,
+        Db   db,
+        D    doc_abstraction,
+        I    doc_matters,
       ) {
         <<sqlite_db_statement_composite_discrete>>
+        writeln(" ", pth_sqlite.sqlite_file(doc_matters.src.filename));
       }
     }
     SQLiteDiscreteDbStatementComposite!()(db, doc_abstraction, doc_matters);
@@ -83,10 +85,10 @@ template SQLiteHubDiscreteBuildTablesAndPopulate() {
 #+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d
 template SQLiteDbRun() {
   void SQLiteDbRun(Db,St,O)(
-    auto ref       Db   db,
-    auto ref       St   db_statement,
-    auto ref       O    opt_action,
-               string   note,
+    Db       db,
+    St       db_statement,
+    O        opt_action,
+    string   note,
   ) {
     debug(sql_statement) {
       writeln(db_statement);
@@ -98,10 +100,8 @@ template SQLiteDbRun() {
         "\nCOMMIT;\n"
       );
       if (!(opt_action.sqlite_discrete)
-        && !(opt_action.sqlite_create)
+        && !(opt_action.sqlite_db_create)
       ) {
-        _metadata_tid_lastrowid = db.lastInsertRowid();
-        writeln("last row id: ", _metadata_tid_lastrowid);
       }
     } catch (ErrnoException ex) {
       writeln("ERROR SQLite : ", ex);
@@ -130,7 +130,7 @@ template SQLinsertDelimiter() {
 }
 template SQLiteFormatAndLoadObject() {
   auto SQLiteFormatAndLoadObject(I)(
-    auto ref       I    doc_matters,
+    I    doc_matters,
   ) {
     mixin SiSUoutputRgxInit;
     struct sqlite_format_and_load_objects {
@@ -146,6 +146,7 @@ template SQLiteFormatAndLoadObject() {
 #+END_SRC
 
 *** sqlite instructions
+**** create
 
 #+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d
 template SQLiteTablesReCreate() {
@@ -161,20 +162,43 @@ template SQLiteTablesReCreate() {
     return _sql_instruct;
   }
 }
+#+END_SRC
+
+**** delete
+
+#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d
+template SQLiteDeleteDocument() {
+  string SQLiteDeleteDocument(I)(
+    I doc_matters,
+  ) {
+    <<sqlite_formatted_delete>>
+    return _delete_uid;
+  }
+}
+#+END_SRC
+
+**** insert
+
+#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d
 template SQLiteInsertMetadata() {
   string SQLiteInsertMetadata(I)(
-    auto ref       I    doc_matters,
+    I doc_matters,
   ) {
     <<sqlite_formatted_insertions_doc_matters_metadata>>
     return _insert_metadata;
   }
 }
+#+END_SRC
+
+**** insert doc objects loop
+
+#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d
 template SQLiteInsertDocObjectsLoop() {
-  string SQLiteInsertDocObjectsLoop(D,I,X)(
-    auto ref const D    doc_abstraction,
-    auto ref       I    doc_matters,
-    auto ref       X    _metadata_tid,
+  string SQLiteInsertDocObjectsLoop(D,I)(
+    D    doc_abstraction,
+    I    doc_matters,
   ) {
+    string _uid = SQLinsertDelimiter!()(doc_matters.src.doc_uid);
     string insertDocObjectsRow(O)(O obj) {
       <<sqlite_formatted_insertions_doc_objects>>
       return _insert_doc_objects_row;
@@ -182,6 +206,11 @@ template SQLiteInsertDocObjectsLoop() {
     <<sqlite_objects_loop>>
   }
 }
+#+END_SRC
+
+**** tables create
+
+#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d
 template SQLiteTablesCreate() {
   void SQLiteTablesCreate(E,O)(E env, O opt_action) {
     import d2sqlite3;
@@ -198,9 +227,9 @@ template SQLiteTablesCreate() {
         return _sql_instruct;
       }
     }
-    if (opt_action.sqlite_create) {
+    if (opt_action.sqlite_db_create) {
       string _db_statement;
-      auto pth_sqlite = SiSUpathsSQLite!()(opt_action.output_dir_set); // ISSUE
+      auto pth_sqlite = SiSUpathsSQLite!()(opt_action.sqlite_filename, opt_action.output_dir_set); // ISSUE
       pth_sqlite.base.mkdirRecurse;
       auto db = Database(pth_sqlite.sqlite_file); // ISSUE
       {
@@ -210,9 +239,23 @@ template SQLiteTablesCreate() {
     }
   }
 }
-template SQLiteTablesDrop() {
-  void SQLiteTablesDrop()() {
-    writeln("table drop");
+#+END_SRC
+
+**** tables drop
+
+#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d
+template SQLiteDbDrop() {
+  void SQLiteDbDrop(O)(O opt_action) {
+    writeln("db drop");
+    if ((opt_action.sqlite_db_drop)) {
+      auto pth_sqlite = SiSUpathsSQLite!()(opt_action.sqlite_filename, opt_action.output_dir_set); // ISSUE
+      writeln("remove(", pth_sqlite.sqlite_file, ")");
+      try {
+        remove(pth_sqlite.sqlite_file);
+      } catch (FileException ex) {
+        // handle error
+      }
+    }
   }
 }
 #+END_SRC
@@ -225,19 +268,24 @@ template SQLiteTablesDrop() {
 {
   string _db_statement;
   {
-    if ((doc_matters.opt.action.sqlite_create)) {
-      auto pth_sqlite = SiSUpathsSQLite!()(doc_matters.output_path);
+    if ((doc_matters.opt.action.sqlite_db_create)) {
+      auto pth_sqlite = SiSUpathsSQLite!()(doc_matters.sqlite.filename, 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
+    if (doc_matters.opt.action.sqlite_delete) {
+      _db_statement ~= SQLiteDeleteDocument!()(doc_matters);
+      SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "DELETE Document");
+    }
+    if (doc_matters.opt.action.sqlite_insert || doc_matters.opt.action.sqlite_update) { // sqlite_update is .opt.action sqlite_delete & sqlite_insert (set in sdp)
+      _db_statement ~= SQLiteDeleteDocument!()(doc_matters);
+      SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "DELETE Document");
       _db_statement ~= SQLiteInsertMetadata!()(doc_matters);
-      SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "table INSERT MetaData");
+      SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "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_statement ~= SQLiteInsertDocObjectsLoop!()(doc_abstraction, doc_matters);
+      SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "INSERT DocObjects");
     }
   }
   db.close;
@@ -253,7 +301,7 @@ template SQLiteTablesDrop() {
   {
     _db_statement ~= SQLiteTablesReCreate!()();
     _db_statement ~= SQLiteInsertMetadata!()(doc_matters);
-    _db_statement ~= SQLiteInsertDocObjectsLoop!()(doc_abstraction, doc_matters, 1); // FIX
+    _db_statement ~= SQLiteInsertDocObjectsLoop!()(doc_abstraction, doc_matters);
     SQLiteDbRun!()(db, _db_statement, doc_matters.opt.action, "table CREATE Tables, INSERT DocObjects");
   }
   db.close;
@@ -646,44 +694,7 @@ auto html_table(O)(
 }
 #+END_SRC
 
-*** 2. _sqlite_ (loop preparation & pre-loop action)
-**** prepare insert statements and do pre-loop inserts
-***** 1. _metadata & src_ text
-****** TODO sql remove selected
-******* d { sql statement
-
-##+name: sqlite_remove_selected
-#+BEGIN_SRC d
-Statement insert_metadata = db.prepare("
-#+END_SRC
-
-******** remove selected
-
-identify and remove, identify by
-- filename & language code
-- (in ruby get_first_value and remove by fn & ln)
-
-##+name: sqlite_remove_selected
-#+BEGIN_SRC sql
-   SELECT tid
-   FROM metadata_and_text
-   WHERE src_filename = '#{doc_matters.conf_make_meta.meta.src_filename}'
-   AND metadata_and_text.language_document_char = '#{doc_matters.conf_make_meta.meta.language_document_char}'
-#+END_SRC
-
-******* d }
-
-##+name: sqlite_remove_selected
-#+BEGIN_SRC d
-");
-#+END_SRC
-
-***** 2. _doc objects_ (used with doc_objects in document loop)
-******  TODO (within loop not here - insert doc objects
-
-work out
-
-*** 3. hub (sqlite_format_and_load_objects)
+*** 2. hub (sqlite_format_and_load_objects)
 **** sql related
 
 #+name: sqlite_load_object
@@ -1088,22 +1099,16 @@ return _insert_doc_objects.join.to!(char[]).toUTF8;
 
 #+name: sqlite_statement_drop_existing_index
 #+BEGIN_SRC sql
-DROP INDEX IF EXISTS ocn;
-DROP INDEX IF EXISTS digest_clean;
-DROP INDEX IF EXISTS digest_all;
-DROP INDEX IF EXISTS clean;
-DROP INDEX IF EXISTS lev0;
-DROP INDEX IF EXISTS lev1;
-DROP INDEX IF EXISTS lev2;
-DROP INDEX IF EXISTS lev3;
-DROP INDEX IF EXISTS lev4;
-DROP INDEX IF EXISTS lev5;
-DROP INDEX IF EXISTS lev6;
-DROP INDEX IF EXISTS title;
-DROP INDEX IF EXISTS creator_author;
+DROP INDEX IF EXISTS idx_ocn;
+DROP INDEX IF EXISTS idx_uid;
+DROP INDEX IF EXISTS idx_digest_clean;
+DROP INDEX IF EXISTS idx_digest_all;
+DROP INDEX IF EXISTS idx_clean;
+DROP INDEX IF EXISTS idx_title;
+DROP INDEX IF EXISTS idx_creator_author;
 DROP INDEX IF EXISTS src_filename;
-DROP INDEX IF EXISTS language_document_char;
-DROP INDEX IF EXISTS classify_topic_register;
+DROP INDEX IF EXISTS idx_language_document_char;
+DROP INDEX IF EXISTS idx_classify_topic_register;
 #+END_SRC
 
 **** DROP TABLE IF EXISTS
@@ -1121,9 +1126,9 @@ DROP TABLE IF EXISTS urls;
 #+name: sqlite_statement_create_table_metadata_and_src_txt
 #+BEGIN_SRC sql
 CREATE TABLE metadata_and_text (
-  tid                              INTEGER           PRIMARY KEY,
-  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 */
+  uid                              VARCHAR(256)      UNIQUE, /* filename, language char, pod/txt (decide on delimiter [,;:/]) */
+  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 */
   title                            VARCHAR(800)  NOT NULL,
   title_main                       VARCHAR(400)  NOT NULL,
   title_sub                        VARCHAR(400)      NULL,
@@ -1213,24 +1218,24 @@ CREATE TABLE metadata_and_text (
 #+name: sqlite_statement_create_table_objects
 #+BEGIN_SRC sql
 CREATE TABLE doc_objects (
-  lid                              INTEGER PRIMARY KEY,
-  metadata_tid                     INTEGER REFERENCES metadata_and_text,
+  lid                              BIGINT            PRIMARY KEY,
+  uid_metadata_and_text            VARCHAR(256)      REFERENCES metadata_and_text(uid) ON DELETE CASCADE,
   ocn                              SMALLINT,
   ocnd                             VARCHAR(6),
   ocns                             VARCHAR(6),
-  clean                            TEXT NULL,
-  body                             TEXT NULL,
-  seg                              VARCHAR(256) NULL,
+  clean                            TEXT              NULL,
+  body                             TEXT              NULL,
+  seg                              VARCHAR(256)      NULL,
   lev_an                           VARCHAR(1),
-  lev                              SMALLINT NULL,
+  lev                              SMALLINT          NULL,
   is_of_type                       VARCHAR(16),
   is_a                             VARCHAR(16),
-  node                             VARCHAR(16) NULL,
-  parent                           VARCHAR(16) NULL,
-  last_decendant                   VARCHAR(16) NULL, /* headings only */
+  node                             VARCHAR(16)       NULL,
+  parent                           VARCHAR(16)       NULL,
+  last_decendant                   VARCHAR(16)       NULL, /* headings only */
   digest_clean                     CHAR(256),
   digest_all                       CHAR(256),
-  types                            CHAR(1) NULL
+  types                            CHAR(1)           NULL
 );
 #+END_SRC
 
@@ -1244,11 +1249,44 @@ CREATE INDEX idx_digest_all ON doc_objects(digest_all);
 CREATE INDEX idx_clean ON doc_objects(clean);
 CREATE INDEX idx_title ON metadata_and_text(title);
 CREATE INDEX idx_author ON metadata_and_text(creator_author);
+CREATE INDEX idx_uid ON metadata_and_text(uid);
 CREATE INDEX idx_filename ON metadata_and_text(src_filename);
 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
+
+*** delete rows (delete document)
+**** DELETE uid rows doc matters & metadata
+***** sql statement: dlang format
+
+#+name: sqlite_formatted_delete
+#+BEGIN_SRC d
+string _uid = doc_matters.src.doc_uid;
+string _delete_uid = format(q"¶
+#+END_SRC
+
+***** DELETE FROM ... WHERE
+
+#+name: sqlite_formatted_delete
+#+BEGIN_SRC sql
+DELETE FROM metadata_and_text
+WHERE uid = '%s';
+DELETE FROM doc_objects
+WHERE uid_metadata_and_text = '%s';
+#+END_SRC
+
+***** VALUES
+
+#+name: sqlite_formatted_delete
+#+BEGIN_SRC d
+¶",
+  _uid,
+  _uid,
+);
+#+END_SRC
+
 *** inserts
 **** INSERT doc objects
 
@@ -1263,8 +1301,7 @@ either:
 
 #+name: sqlite_formatted_insertions_doc_objects
 #+BEGIN_SRC d
-string _insert_doc_objects_row;
-_insert_doc_objects_row = format(q"¶
+string _insert_doc_objects_row = format(q"¶
 #+END_SRC
 
 ***** INSERT INTO
@@ -1272,7 +1309,7 @@ _insert_doc_objects_row = format(q"¶
 #+name: sqlite_formatted_insertions_doc_objects
 #+BEGIN_SRC sql
   INSERT INTO doc_objects (
-    metadata_tid,
+    uid_metadata_and_text,
     ocn,
     clean,
     body,
@@ -1287,7 +1324,7 @@ _insert_doc_objects_row = format(q"¶
 #+name: sqlite_formatted_insertions_doc_objects
 #+BEGIN_SRC sql
   VALUES (
-    %d,
+    '%s',
     %s,
     '%s',
     '%s',
@@ -1302,7 +1339,7 @@ _insert_doc_objects_row = format(q"¶
 #+name: sqlite_formatted_insertions_doc_objects
 #+BEGIN_SRC d
 ¶",
-  _metadata_tid,
+  _uid,
   obj.metainfo.ocn,
   SQLinsertDelimiter!()(obj_txt["text"]),
   SQLinsertDelimiter!()(obj_txt["html"]),
@@ -1312,13 +1349,13 @@ _insert_doc_objects_row = format(q"¶
 );
 #+END_SRC
 
-**** INSERT doc matters metadata
+**** 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"¶
+string _uid = SQLinsertDelimiter!()(doc_matters.src.doc_uid);
+string _insert_metadata = format(q"¶
 #+END_SRC
 
 ***** INSERT INTO
@@ -1326,6 +1363,7 @@ _insert_metadata = format(q"¶
 #+name: sqlite_formatted_insertions_doc_matters_metadata
 #+BEGIN_SRC sql
   INSERT INTO metadata_and_text (
+    uid,
     src_filename,
     src_composite_id_per_txt,
     src_composite_id_per_pod,
@@ -1425,6 +1463,7 @@ _insert_metadata = format(q"¶
     '%s',
     '%s',
     '%s',
+    '%s',
     '%s'
   );
 #+END_SRC
@@ -1434,6 +1473,7 @@ _insert_metadata = format(q"¶
 #+name: sqlite_formatted_insertions_doc_matters_metadata
 #+BEGIN_SRC d
 ¶",
+  _uid,
   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),