sqlite document db discrete & collection, started

- discrete document , drop & recreate document
  --sqlite-disctrete
- document collection
  - create tables
    --sqlite-create
  - populate db with specified document
    --sqlite-update

1 files changed, 106 insertions, 81 deletions

diff --git a/org/output_sqlite.org b/org/output_sqlite.org
index bb679e2..4f5029e 100644
--- a/org/output_sqlite.org
+++ b/org/output_sqlite.org
@@ -1,4 +1,4 @@
-#+TITLE:       sdp output html
+#+TITLE:       sdp output sqlite
 #+AUTHOR:      Ralph Amissah
 #+EMAIL:       [[mailto:ralph.amissah@gmail.com][ralph.amissah@gmail.com]]
 #+DESCRIPTION: documents - structuring, publishing in multiple formats & search
@@ -27,7 +27,6 @@ template SQLiteBuildTablesAndPopulate() {
   ) {
     import d2sqlite3;
     import std.typecons : Nullable;
-    import sdp.output.sqlite;
     mixin SiSUoutputRgxInit;
     static auto rgx = Rgx();
     template SQLiteFormatAndLoadObject() {
@@ -50,13 +49,13 @@ template SQLiteBuildTablesAndPopulate() {
         auto ref              I    doc_matters,
       ) {
         <<sqlite_db_initialize>>
-        <<sqlite_table_initialize>>
-        <<sqlite_create_table_metadata_and_src_txt>>
-        <<sqlite_create_table_urls>>
-        <<sqlite_create_table_objects>>
+          <<sqlite_table_initialize>>
+          <<sqlite_create_table_metadata_and_src_txt>>
+          /* */<<sqlite_create_table_urls>>
+          <<sqlite_create_table_objects>>
         <<sqlite_close_initialize>>
         <<sqlite_insert_metadata_and_src_text>>
-        <<sqlite_insert_urls>>
+        //<<sqlite_insert_urls>>
         <<sqlite_insert_doc_objects>>
       }
     }
@@ -90,7 +89,6 @@ import sdp.output;
 import
   std.file,
   std.conv : to;
-import sdp.output.sqlite;
 #+END_SRC
 
 ** 2. format and load template
@@ -469,9 +467,11 @@ auto html_table(O)(
 #+name: sqlite_db_initialize
 #+BEGIN_SRC d
 auto pth_sqlite = SiSUpathsSQLite!()(doc_matters.src_path_info, doc_matters.language);
-auto db = Database(pth_sqlite.sqlite_file(doc_matters.source_filename));
+auto db = Database(pth_sqlite.sqlite_file(doc_matters.environment["pwd"].baseName));
 // auto db = Database(":memory:"); // open database in memory
-db.run("
+if (doc_matters.opt_action["sqlite-create"]) {
+  if ((doc_matters.opt_action["verbose"])) { writeln("sqlite create tables... "); }
+  db.run("
 #+END_SRC
 
 *******  0. drop table if exists
@@ -499,6 +499,7 @@ CREATE TABLE metadata_and_text (
   title_language                   VARCHAR(100)      NULL,
   title_language_char              VARCHAR(6)        NULL,
   creator_author                   VARCHAR(600)      NULL,
+  creator_author_email             VARCHAR(100)      NULL,
   creator_author_hon               VARCHAR(100)      NULL,
   creator_author_nationality       VARCHAR(100)      NULL,
   creator_editor                   VARCHAR(600)      NULL,
@@ -531,14 +532,14 @@ CREATE TABLE metadata_and_text (
   original_source                  VARCHAR(600)      NULL,
   original_institution             VARCHAR(600)      NULL,
   original_nationality             VARCHAR(100)      NULL,
-  rights                           VARCHAR(2500)     NULL,
-  rights_copyright_text            VARCHAR(2500)     NULL,
-  rights_copyright_translation     VARCHAR(2500)     NULL,
+  original_title                   VARCHAR(800)      NULL,
+  rights_copyright                 VARCHAR(2500)     NULL,
+  rights_copyright_audio           VARCHAR(2500)     NULL,
+  rights_copyright_cover           VARCHAR(2500)     NULL,
   rights_copyright_illustrations   VARCHAR(2500)     NULL,
   rights_copyright_photographs     VARCHAR(2500)     NULL,
-  rights_copyright_preparation     VARCHAR(2500)     NULL,
-  rights_copyright_digitization    VARCHAR(2500)     NULL,
-  rights_copyright_audio           VARCHAR(2500)     NULL,
+  rights_copyright_text            VARCHAR(2500)     NULL,
+  rights_copyright_translation     VARCHAR(2500)     NULL,
   rights_copyright_video           VARCHAR(2500)     NULL,
   rights_license                   VARCHAR(2500)     NULL,
   identifier_oclc                  VARCHAR(30)       NULL,
@@ -614,11 +615,41 @@ CREATE TABLE doc_objects (
 
 #+name: sqlite_close_initialize
 #+BEGIN_SRC d
-");
+  ");
+}
 #+END_SRC
 
 ***** 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
+
 ******* sql statement
 ******** d { sql statement
 
@@ -647,6 +678,7 @@ either:
     title_language,
     title_language_char,
     creator_author,
+    creator_author_email,
     creator_author_hon,
     creator_author_nationality,
     creator_editor,
@@ -679,14 +711,14 @@ either:
     original_source,
     original_institution,
     original_nationality,
-    rights,
-    rights_copyright_text,
-    rights_copyright_translation,
+    original_title,
+    rights_copyright,
+    rights_copyright_audio,
+    rights_copyright_cover,
     rights_copyright_illustrations,
     rights_copyright_photographs,
-    rights_copyright_preparation,
-    rights_copyright_digitization,
-    rights_copyright_audio,
+    rights_copyright_text,
+    rights_copyright_translation,
     rights_copyright_video,
     rights_license,
     identifier_oclc,
@@ -733,6 +765,7 @@ either:
     :title_language,
     :title_language_char,
     :creator_author,
+    :creator_author_email,
     :creator_author_hon,
     :creator_author_nationality,
     :creator_editor,
@@ -765,14 +798,14 @@ either:
     :original_source,
     :original_institution,
     :original_nationality,
-    :rights,
-    :rights_copyright_text,
-    :rights_copyright_translation,
+    :original_title,
+    :rights_copyright,
+    :rights_copyright_audio,
+    :rights_copyright_cover,
     :rights_copyright_illustrations,
     :rights_copyright_photographs,
-    :rights_copyright_preparation,
-    :rights_copyright_digitization,
-    :rights_copyright_audio,
+    :rights_copyright_text,
+    :rights_copyright_translation,
     :rights_copyright_video,
     :rights_license,
     :identifier_oclc,
@@ -815,74 +848,66 @@ either:
 
 #+name: sqlite_insert_metadata_and_src_text
 #+BEGIN_SRC d
+// insert_metadata.bind(":tid",                               ); // unique identifier you could try use sha of title author & language? or auto increment??
 insert_metadata.bind(":title",                             doc_matters.conf_make_meta.meta.title_full);
-// insert_metadata.bind(":title_main",                     doc_matters.conf_make_meta.meta.title_main);
-// insert_metadata.bind(":title_sub",                      doc_matters.conf_make_meta.meta.title_subtitle);
-// insert_metadata.bind(":title_short",                    doc_matters.conf_make_meta.meta.title_short); //
-// insert_metadata.bind(":title_edition",                  doc_matters.conf_make_meta.meta.title_edition);
-// insert_metadata.bind(":title_note",                     doc_matters.conf_make_meta.meta.title_note);
-// insert_metadata.bind(":title_language",                 doc_matters.conf_make_meta.meta.title_language);
+insert_metadata.bind(":title_main",                        doc_matters.conf_make_meta.meta.title_main);
+insert_metadata.bind(":title_sub",                         doc_matters.conf_make_meta.meta.title_subtitle);
+insert_metadata.bind(":title_short",                       doc_matters.conf_make_meta.meta.title_short); //
+insert_metadata.bind(":title_edition",                     doc_matters.conf_make_meta.meta.title_edition);
+insert_metadata.bind(":title_language",                    doc_matters.conf_make_meta.meta.title_language);
 // insert_metadata.bind(":title_language_char",            doc_matters.conf_make_meta.meta.title_language_char);
+insert_metadata.bind(":classify_dewey",                    doc_matters.conf_make_meta.meta.classify_dewey);
+insert_metadata.bind(":classify_keywords",                 doc_matters.conf_make_meta.meta.classify_keywords);
+insert_metadata.bind(":classify_loc",                      doc_matters.conf_make_meta.meta.classify_loc);
+insert_metadata.bind(":classify_subject",                  doc_matters.conf_make_meta.meta.classify_subject);
+insert_metadata.bind(":classify_topic_register",           doc_matters.conf_make_meta.meta.classify_topic_register);
 insert_metadata.bind(":creator_author",                    doc_matters.conf_make_meta.meta.creator_author);
-// insert_metadata.bind(":creator_author_hon",             doc_matters.conf_make_meta.meta.creator_author_hon);
-// insert_metadata.bind(":creator_author_nationality",     doc_matters.conf_make_meta.meta.creator_author_nationality);
+insert_metadata.bind(":creator_author_email",              doc_matters.conf_make_meta.meta.creator_author_email);
 // insert_metadata.bind(":creator_editor",                 doc_matters.conf_make_meta.meta.creator_editor);
 // insert_metadata.bind(":creator_contributor",            doc_matters.conf_make_meta.meta.creator_contributor);
-// insert_metadata.bind(":creator_illustrator",            doc_matters.conf_make_meta.meta.creator_illustrator);
+insert_metadata.bind(":creator_illustrator",               doc_matters.conf_make_meta.meta.creator_illustrator);
 // insert_metadata.bind(":creator_photographer",           doc_matters.conf_make_meta.meta.creator_photographer);
-// insert_metadata.bind(":creator_translator",             doc_matters.conf_make_meta.meta.creator_translator);
-// insert_metadata.bind(":creator_prepared_by",            doc_matters.conf_make_meta.meta.creator_prepared_by);
-// insert_metadata.bind(":creator_digitized_by",           doc_matters.conf_make_meta.meta.creator_digitized_by);
+insert_metadata.bind(":creator_translator",                doc_matters.conf_make_meta.meta.creator_translator);
 // insert_metadata.bind(":creator_audio",                  doc_matters.conf_make_meta.meta.creator_audio);
 // insert_metadata.bind(":creator_video",                  doc_matters.conf_make_meta.meta.creator_video);
-// insert_metadata.bind(":language_document",              doc_matters.conf_make_meta.meta.language_document);
-// insert_metadata.bind(":language_document_char",         doc_matters.conf_make_meta.meta.language_document_char);
-// insert_metadata.bind(":language_original",              doc_matters.conf_make_meta.meta.language_original);
-// insert_metadata.bind(":language_original_char",         doc_matters.conf_make_meta.meta.language_original_char);
-// insert_metadata.bind(":date_added_to_site",             doc_matters.conf_make_meta.meta.date_added_to_site);
-// insert_metadata.bind(":date_available",                 doc_matters.conf_make_meta.meta.date_available);
-// insert_metadata.bind(":date_created",                   doc_matters.conf_make_meta.meta.date_created);
-// insert_metadata.bind(":date_issued",                    doc_matters.conf_make_meta.meta.date_issued);
-// insert_metadata.bind(":date_modified",                  doc_matters.conf_make_meta.meta.date_modified);
-// insert_metadata.bind(":date_published",                 doc_matters.conf_make_meta.meta.date_published);
-// insert_metadata.bind(":date_valid",                     doc_matters.conf_make_meta.meta.date_valid);
+insert_metadata.bind(":date_added_to_site",                doc_matters.conf_make_meta.meta.date_added_to_site);
+insert_metadata.bind(":date_available",                    doc_matters.conf_make_meta.meta.date_available);
+insert_metadata.bind(":date_created",                      doc_matters.conf_make_meta.meta.date_created);
+insert_metadata.bind(":date_issued",                       doc_matters.conf_make_meta.meta.date_issued);
+insert_metadata.bind(":date_modified",                     doc_matters.conf_make_meta.meta.date_modified);
+insert_metadata.bind(":date_published",                    doc_matters.conf_make_meta.meta.date_published);
+insert_metadata.bind(":date_valid",                        doc_matters.conf_make_meta.meta.date_valid);
 // insert_metadata.bind(":date_translated",                doc_matters.conf_make_meta.meta.date_translated);
 // insert_metadata.bind(":date_original_publication",      doc_matters.conf_make_meta.meta.date_original_publication);
 // insert_metadata.bind(":date_generated",                 doc_matters.conf_make_meta.meta.date_generated);
-// insert_metadata.bind(":publisher",                      doc_matters.conf_make_meta.meta.publisher));
-// insert_metadata.bind(":original_publisher",             doc_matters.conf_make_meta.meta.original_publisher);
-// insert_metadata.bind(":original_language",              doc_matters.conf_make_meta.meta.original_language);
-// insert_metadata.bind(":original_language_char",         doc_matters.conf_make_meta.meta.original_language_char);
-// insert_metadata.bind(":original_source",                doc_matters.conf_make_meta.meta.original_source);
-// insert_metadata.bind(":original_institution",           doc_matters.conf_make_meta.meta.original_institution);
-// insert_metadata.bind(":original_nationality",           doc_matters.conf_make_meta.meta.original_nationality);
+insert_metadata.bind(":identifier_isbn",                   doc_matters.conf_make_meta.meta.identifier_isbn);
+insert_metadata.bind(":identifier_oclc",                   doc_matters.conf_make_meta.meta.identifier_oclc);
+insert_metadata.bind(":language_document",                 doc_matters.conf_make_meta.meta.language_document);
+insert_metadata.bind(":language_document_char",            doc_matters.conf_make_meta.meta.language_document_char);
+// insert_metadata.bind(":language_original",              doc_matters.conf_make_meta.meta.language_original);
+// insert_metadata.bind(":language_original_char",         doc_matters.conf_make_meta.meta.language_original_char);
+insert_metadata.bind(":notes_abstract",                    doc_matters.conf_make_meta.meta.notes_abstract);
+insert_metadata.bind(":notes_description",                 doc_matters.conf_make_meta.meta.notes_description);
+insert_metadata.bind(":original_publisher",                doc_matters.conf_make_meta.meta.original_publisher);
+insert_metadata.bind(":original_language",                 doc_matters.conf_make_meta.meta.original_language);
+insert_metadata.bind(":original_language_char",            doc_matters.conf_make_meta.meta.original_language_char);
+insert_metadata.bind(":original_source",                   doc_matters.conf_make_meta.meta.original_source);
+insert_metadata.bind(":original_title",                    doc_matters.conf_make_meta.meta.original_title);
+insert_metadata.bind(":publisher",                         doc_matters.conf_make_meta.meta.publisher);
 // insert_metadata.bind(":rights",                         doc_matters.conf_make_meta.meta.rights);
-// insert_metadata.bind(":rights_copyright_text",          doc_matters.conf_make_meta.meta.rights_copyright_text);
-// insert_metadata.bind(":rights_copyright_translation",   doc_matters.conf_make_meta.meta.rights_copyright_translation);
-// insert_metadata.bind(":rights_copyright_illustrations", doc_matters.conf_make_meta.meta.rights_illustrations);
-// insert_metadata.bind(":rights_copyright_photographs",   doc_matters.conf_make_meta.meta.rights_photographs);
+insert_metadata.bind(":rights_copyright",                  doc_matters.conf_make_meta.meta.rights_copyright);
+insert_metadata.bind(":rights_copyright_audio",            doc_matters.conf_make_meta.meta.rights_copyright_audio);
+insert_metadata.bind(":rights_copyright_cover",            doc_matters.conf_make_meta.meta.rights_copyright_cover);
+insert_metadata.bind(":rights_copyright_illustrations",    doc_matters.conf_make_meta.meta.rights_copyright_illustrations);
+insert_metadata.bind(":rights_copyright_photographs",      doc_matters.conf_make_meta.meta.rights_copyright_photographs);
 // insert_metadata.bind(":rights_copyright_preparation",   doc_matters.conf_make_meta.meta.rights_preparation);
+insert_metadata.bind(":rights_copyright_text",      doc_matters.conf_make_meta.meta.rights_copyright_text);
+insert_metadata.bind(":rights_copyright_translation",      doc_matters.conf_make_meta.meta.rights_copyright_translation);
+insert_metadata.bind(":rights_copyright_video",            doc_matters.conf_make_meta.meta.rights_copyright_video);
 // insert_metadata.bind(":rights_copyright_digitization",  doc_matters.conf_make_meta.meta.rights_digitization);
 // insert_metadata.bind(":rights_copyright_audio",         doc_matters.conf_make_meta.meta.rights_audio);
 // insert_metadata.bind(":rights_copyright_video",         doc_matters.conf_make_meta.meta.rights_video);
-// insert_metadata.bind(":rights_license",                 doc_matters.conf_make_meta.meta.rights_license);
-// insert_metadata.bind(":identifier_oclc",                doc_matters.conf_make_meta.meta.identifier_oclc);
-// insert_metadata.bind(":identifier_isbn",                doc_matters.conf_make_meta.meta.identifier_isbn);
-// insert_metadata.bind(":classify_topic_register",        doc_matters.conf_make_meta.meta.classify_topic_register);
-// insert_metadata.bind(":classify_subject",               doc_matters.conf_make_meta.meta.classify_subject);
-// insert_metadata.bind(":classify_loc",                   doc_matters.conf_make_meta.meta.classify_loc);
-// insert_metadata.bind(":notes_abstract",                 doc_matters.conf_make_meta.meta.notes_abstract);
-// insert_metadata.bind(":notes_description",              doc_matters.conf_make_meta.meta.notes_description);
-// insert_metadata.bind(":notes_comment",                  doc_matters.conf_make_meta.meta.notes_comment);
-// insert_metadata.bind(":notes_coverage",                 doc_matters.conf_make_meta.meta.notes_coverage);
-// insert_metadata.bind(":notes_relation",                 doc_matters.conf_make_meta.meta.notes_relation);
-// insert_metadata.bind(":notes_history",                  doc_matters.conf_make_meta.meta.notes_history);
-// insert_metadata.bind(":notes_type",                     doc_matters.conf_make_meta.meta.notes_type);
-// insert_metadata.bind(":notes_format",                   doc_matters.conf_make_meta.meta.notes_format);
-// insert_metadata.bind(":notes_prefix",                   doc_matters.conf_make_meta.meta.notes_prefix);
-// insert_metadata.bind(":notes_prefix_a",                 doc_matters.conf_make_meta.meta.notes_prefix_a);
-// insert_metadata.bind(":notes_prefix_b",                 doc_matters.conf_make_meta.meta.notes_prefix_b);
-// insert_metadata.bind(":notes_suffix",                   doc_matters.conf_make_meta.meta.notes_suffix);
+insert_metadata.bind(":rights_license",                    doc_matters.conf_make_meta.meta.rights_license);
 // insert_metadata.bind(":src_filename",                   doc_matters.conf_make_meta.meta.src_filename);
 // insert_metadata.bind(":src_fingerprint",                doc_matters.conf_make_meta.meta.src_fingerprint);
 // insert_metadata.bind(":src_filesize",                   doc_matters.conf_make_meta.meta.src_filesize);