0.26.5 sqlite output, focus

1 files changed, 124 insertions, 101 deletions

diff --git a/src/sdp/output/sqlite.d b/src/sdp/output/sqlite.d
index faff29b..41d504e 100644
--- a/src/sdp/output/sqlite.d
+++ b/src/sdp/output/sqlite.d
@@ -10,38 +10,44 @@ 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,
       ) {
         {
           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;
         }
+        writeln(" ", pth_sqlite.sqlite_file);
       }
     }
     SQLiteDbStatementComposite!()(db, doc_abstraction, doc_matters);
@@ -49,28 +55,29 @@ template SQLiteHubBuildTablesAndPopulate() {
 }
 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,
       ) {
         {
           string _db_statement;
           {
             _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;
         }
+        writeln(" ", pth_sqlite.sqlite_file(doc_matters.src.filename));
       }
     }
     SQLiteDiscreteDbStatementComposite!()(db, doc_abstraction, doc_matters);
@@ -78,10 +85,10 @@ template SQLiteHubDiscreteBuildTablesAndPopulate() {
 }
 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);
@@ -93,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);
@@ -120,7 +125,7 @@ template SQLinsertDelimiter() {
 }
 template SQLiteFormatAndLoadObject() {
   auto SQLiteFormatAndLoadObject(I)(
-    auto ref       I    doc_matters,
+    I    doc_matters,
   ) {
     mixin SiSUoutputRgxInit;
     struct sqlite_format_and_load_objects {
@@ -569,29 +574,23 @@ template SQLiteTablesReCreate() {
   string SQLiteTablesReCreate()() {
     string _sql_instruct;
     _sql_instruct = format(q"¶
-      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;
       DROP TABLE IF EXISTS metadata_and_text;
       DROP TABLE IF EXISTS doc_objects;
       DROP TABLE IF EXISTS urls;
       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,
@@ -675,24 +674,24 @@ template SQLiteTablesReCreate() {
         links                            TEXT              NULL
       );
       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
       );
       CREATE INDEX idx_ocn ON doc_objects(ocn);
       CREATE INDEX idx_digest_clean ON doc_objects(digest_clean);
@@ -700,6 +699,7 @@ template SQLiteTablesReCreate() {
       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);
@@ -707,13 +707,31 @@ template SQLiteTablesReCreate() {
     return _sql_instruct;
   }
 }
+template SQLiteDeleteDocument() {
+  string SQLiteDeleteDocument(I)(
+    I doc_matters,
+  ) {
+    string _uid = doc_matters.src.doc_uid;
+    string _delete_uid = format(q"¶
+    DELETE FROM metadata_and_text
+    WHERE uid = '%s';
+    DELETE FROM doc_objects
+    WHERE uid_metadata_and_text = '%s';
+    ¶",
+      _uid,
+      _uid,
+    );
+    return _delete_uid;
+  }
+}
 template SQLiteInsertMetadata() {
   string SQLiteInsertMetadata(I)(
-    auto ref       I    doc_matters,
+    I doc_matters,
   ) {
-    string _insert_metadata;
-    _insert_metadata = format(q"¶
+    string _uid = SQLinsertDelimiter!()(doc_matters.src.doc_uid);
+    string _insert_metadata = format(q"¶
       INSERT INTO metadata_and_text (
+        uid,
         src_filename,
         src_composite_id_per_txt,
         src_composite_id_per_pod,
@@ -807,9 +825,11 @@ template SQLiteInsertMetadata() {
         '%s',
         '%s',
         '%s',
+        '%s',
         '%s'
       );
     ¶",
+      _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),
@@ -861,16 +881,15 @@ template SQLiteInsertMetadata() {
   }
 }
 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) {
-      string _insert_doc_objects_row;
-      _insert_doc_objects_row = format(q"¶
+      string _insert_doc_objects_row = format(q"¶
         INSERT INTO doc_objects (
-          metadata_tid,
+          uid_metadata_and_text,
           ocn,
           clean,
           body,
@@ -879,7 +898,7 @@ template SQLiteInsertDocObjectsLoop() {
           is_a
         )
         VALUES (
-          %d,
+          '%s',
           %s,
           '%s',
           '%s',
@@ -888,7 +907,7 @@ template SQLiteInsertDocObjectsLoop() {
           '%s'
         );
       ¶",
-        _metadata_tid,
+        _uid,
         obj.metainfo.ocn,
         SQLinsertDelimiter!()(obj_txt["text"]),
         SQLinsertDelimiter!()(obj_txt["html"]),
@@ -1058,29 +1077,23 @@ template SQLiteTablesCreate() {
       string SQLiteTablesReCreate()() {
         string _sql_instruct;
         _sql_instruct = format(q"¶
-          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;
           DROP TABLE IF EXISTS metadata_and_text;
           DROP TABLE IF EXISTS doc_objects;
           DROP TABLE IF EXISTS urls;
           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,
@@ -1164,24 +1177,24 @@ template SQLiteTablesCreate() {
             links                            TEXT              NULL
           );
           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
           );
           CREATE INDEX idx_ocn ON doc_objects(ocn);
           CREATE INDEX idx_digest_clean ON doc_objects(digest_clean);
@@ -1189,6 +1202,7 @@ template SQLiteTablesCreate() {
           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);
@@ -1196,9 +1210,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
       {
@@ -1208,8 +1222,17 @@ template SQLiteTablesCreate() {
     }
   }
 }
-template SQLiteTablesDrop() {
-  void SQLiteTablesDrop()() {
-    writeln("table drop");
+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
+      }
+    }
   }
 }