From 2a359a57ce021ba6ddf7232b3f3d79726d4738ba Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Tue, 17 Oct 2017 13:36:41 -0400 Subject: 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 --- org/output_sqlite.org | 187 ++++++++++++++++++++++++++++---------------------- 1 file changed, 106 insertions(+), 81 deletions(-) (limited to 'org/output_sqlite.org') 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, ) { <> - <> - <> - <> - <> + <> + <> + /* */<> + <> <> <> - <> + //<> <> } } @@ -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); -- cgit v1.2.3