#+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 #+KEYWORDS #+LANGUAGE: en #+STARTUP: indent content #+OPTIONS: H:3 num:nil toc:t \n:nil @:t ::t |:t ^:nil _:nil -:t f:t *:t <:t #+OPTIONS: TeX:t LaTeX:t skip:nil d:nil todo:t pri:nil tags:not-in-toc #+OPTIONS: author:nil email:nil creator:nil timestamp:nil #+PROPERTY: header-args :padline no :exports code :noweb yes #+EXPORT_SELECT_TAGS: export #+EXPORT_EXCLUDE_TAGS: noexport #+FILETAGS: :sdp:niu:output: #+TAGS: assert(a) class(c) debug(d) mixin(m) sdp(s) tangle(T) template(t) WEB(W) noexport(n) * sql ** 0. module, templates #+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d module sdp.output.sqlite; <> long _metadata_tid_lastrowid; template SQLiteDbRun() { void SQLiteDbRun(Db,St,O)( auto ref Db db, auto ref St db_statement, auto ref O opt_action, string note, ) { debug(sql_statement) { writeln(db_statement); } try { db.run( "\nBEGIN;\n" ~ db_statement ~ "\nCOMMIT;\n" ); if (!(opt_action.sqlite_create)) { _metadata_tid_lastrowid = db.lastInsertRowid(); writeln("last row id: ", _metadata_tid_lastrowid); } } catch (ErrnoException ex) { writeln("ERROR SQLite : ", ex); } catch (Exception ex) { writeln("ERROR SQLite : ", ex); } if (opt_action.debug_do) { writeln(note); if (opt_action.verbose) { writeln(db_statement); } } } } template SQLiteBuildTablesAndPopulate() { void SQLiteBuildTablesAndPopulate(D,I)( auto ref const D doc_abstraction, 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, ) { <> } } 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, ) { mixin SiSUoutputRgxInit; struct sqlite_format_and_load_objects { <> <> <> <> <> } return sqlite_format_and_load_objects(); } } template SQLiteTablesReCreate() { string SQLiteTablesReCreate()() { string _sql_instruct; _sql_instruct = format(q"¶ <> <> <> <> <> ¶",); return _sql_instruct; } } template SQLiteInsertMetadata() { string SQLiteInsertMetadata(I)( auto ref I doc_matters, ) { <> return _insert_metadata; } } template SQLiteInsertDocObjectsLoop() { string SQLiteInsertDocObjectsLoop(P,I)( auto ref P doc_parts, auto ref I _metadata_tid, ) { string insertDocObjectsRow(O)(O obj) { <> return _insert_doc_objects_row; } <> } } SQLiteDbStatementComposite!()(db, doc_abstraction, doc_matters); } } template SQLiteTablesCreate() { void SQLiteTablesCreate(E,O)(E env, O opt_action) { import d2sqlite3; template SQLiteTablesReCreate() { string SQLiteTablesReCreate()() { string _sql_instruct; _sql_instruct = format(q"¶ <> <> <> <> <> ¶",); return _sql_instruct; } } if (opt_action.sqlite_create) { string _db_statement; auto pth_sqlite = SiSUpathsSQLite!()(opt_action.output_dir_set); // ISSUE pth_sqlite.base.mkdirRecurse; auto db = Database(pth_sqlite.sqlite_file); // ISSUE { _db_statement ~= SQLiteTablesReCreate!()(); } SQLiteDbRun!()(db, _db_statement, opt_action, "TABLE RE-CREATE"); } } } template SQLiteTablesDrop() { void SQLiteTablesDrop()() { writeln("table drop"); } } #+END_SRC ** 1. [#A] sqlite_db_statement #+name: sqlite_db_statement_composite #+BEGIN_SRC d { { 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"); } } db.close; } } #+END_SRC ** 2. imports #+name: output_imports #+BEGIN_SRC d import sdp.output; import std.file, std.conv : to; #+END_SRC ** 3. format and load template *** 1. prepare objects (munge, sanitize, markup) **** 1. _text_ generic munge (sanitize text for search) - [3/4] (search text, applies to all but code blocks) - [ ] remove whitespace, paragraph on single line (formatting kept so far) - [X] remove font face attributes - [X] move embedded endnotes - [X] place after text object - [X] remove embedded endnote numbers (rely on html output to represent) - [X] urls - [X] clean url markers, leave plain link text - [X] place urls after text object and its endnotes #+name: sanitize_text_for_search #+BEGIN_SRC d auto generic_munge_sanitize_text_for_search( string _txt, ) { string _notes; string _urls; if (_txt.matchFirst(rgx.inline_notes_al_gen)) { foreach (m; _txt.matchAll(rgx.inline_notes_al_gen_text)) { _notes ~= "\n" ~ m["text"]; } _txt = _txt.replaceAll(rgx.inline_notes_al_gen, ""); } if (_txt.matchFirst(rgx.inline_link)) { foreach (m; _txt.matchAll(rgx.inline_link)) { if (m["link"].match(rgx.url)) { _urls ~= "\n" ~ m["link"]; } } _txt = _txt.replaceAll(rgx.inline_link_clean, ""); } if (_notes.length > 0) { _txt ~= _notes; } if (_urls.length > 0) { _txt ~= _urls; } if (doc_matters.opt.action.debug_do) { writeln(_txt, "\n"); } debug(sql_text_clean) { writeln(_txt); } return _txt; } #+END_SRC **** 2. _html_ light html objects - place endnotes after text object - light inline html markup ***** munge ****** general munge (special characters, inline markup, move notes) #+name: sanitize_and_munge_inline_html #+BEGIN_SRC d auto munge_html(O)( auto return ref const O obj, ) { string _html_special_characters(string _txt){ _txt = _txt .replaceAll(rgx.xhtml_ampersand, "&") .replaceAll(rgx.xhtml_quotation, """) .replaceAll(rgx.xhtml_less_than, "<") .replaceAll(rgx.xhtml_greater_than, ">") .replaceAll(rgx.nbsp_char, " ") .replaceAll(rgx.xhtml_line_break, "
"); return _txt; } string _html_font_face(string _txt){ _txt = _txt .replaceAll(rgx.inline_emphasis, "$1") .replaceAll(rgx.inline_bold, "$1") .replaceAll(rgx.inline_underscore, "$1") .replaceAll(rgx.inline_italics, "$1") .replaceAll(rgx.inline_superscript, "$1") .replaceAll(rgx.inline_subscript, "$1") .replaceAll(rgx.inline_strike, "$1") .replaceAll(rgx.inline_insert, "$1") .replaceAll(rgx.inline_mono, "$1") .replaceAll(rgx.inline_cite, "$1"); return _txt; } string _notes; string _urls; string _txt = _html_font_face(_html_special_characters(obj.text)); if (_txt.matchFirst(rgx.inline_notes_al_gen)) { foreach (m; _txt.matchAll(rgx.inline_notes_al_gen_text)) { _notes ~= "\n" ~ m["text"]; } _txt = _txt.replaceAll(rgx.inline_notes_al_gen_ref, "$1 "); } if (_txt.matchFirst(rgx.inline_link)) { foreach (m; _txt.matchAll(rgx.inline_link)) { } _txt = _txt.replaceAll(rgx.inline_link_clean, ""); } if (_notes.length > 0) { _txt ~= _notes; } if (doc_matters.opt.action.debug_do) { writeln(_txt, "\n"); } return _txt; } #+END_SRC ****** special characters #+name: sanitize_and_munge_inline_html #+BEGIN_SRC d string html_special_characters(string _txt){ _txt = _txt .replaceAll(rgx.xhtml_ampersand, "&") .replaceAll(rgx.xhtml_quotation, """) .replaceAll(rgx.xhtml_less_than, "<") .replaceAll(rgx.xhtml_greater_than, ">") .replaceAll(rgx.nbsp_char, " ") .replaceAll(rgx.xhtml_line_break, "
"); return _txt; } #+END_SRC ****** special characters for code #+name: sanitize_and_munge_inline_html #+BEGIN_SRC d string html_special_characters_code(string _txt){ _txt = _txt .replaceAll(rgx.xhtml_ampersand, "&") .replaceAll(rgx.xhtml_quotation, """) .replaceAll(rgx.xhtml_less_than, "<") .replaceAll(rgx.xhtml_greater_than, ">") .replaceAll(rgx.nbsp_char, " "); return _txt; } #+END_SRC ****** font_face #+name: sanitize_and_munge_inline_html #+BEGIN_SRC d string html_font_face(string _txt){ _txt = _txt .replaceAll(rgx.inline_emphasis, "$1") .replaceAll(rgx.inline_bold, "$1") .replaceAll(rgx.inline_underscore, "$1") .replaceAll(rgx.inline_italics, "$1") .replaceAll(rgx.inline_superscript, "$1") .replaceAll(rgx.inline_subscript, "$1") .replaceAll(rgx.inline_strike, "$1") .replaceAll(rgx.inline_insert, "$1") .replaceAll(rgx.inline_mono, "$1") .replaceAll(rgx.inline_cite, "$1"); return _txt; } #+END_SRC ***** objects ****** heading #+name: html_objects #+BEGIN_SRC d auto html_heading(O)( auto return ref const O obj, ) { string _txt = munge_html(obj); string o = format(q"¶

%s

¶", obj.is_a, _txt, ); return o; } #+END_SRC ******* +fancy+ ##+name: prepare_objects_html #+BEGIN_SRC d auto html_heading(O)( auto return ref const O obj, ) { string o; string _txt = munge_html(obj); o = format(q"¶ %s ¶", obj.heading_lev_markup, obj.is_a, _txt, obj.heading_lev_markup, ); return o; } #+END_SRC ****** para #+name: html_objects #+BEGIN_SRC d auto html_para(O)( auto return ref const O obj, ) { string _txt = munge_html(obj); _txt = (obj.bullet) ? ("●  " ~ _txt) : _txt; string o = format(q"¶

%s

¶", obj.is_a, obj.indent_hang, obj.indent_base, _txt ); return o; } #+END_SRC ****** quote #+name: html_objects #+BEGIN_SRC d auto html_quote(O)( auto return ref const O obj, ) { string _txt = munge_html(obj); string o = format(q"¶

%s

¶", obj.is_a, _txt ); return o; } #+END_SRC ****** group #+name: html_objects #+BEGIN_SRC d auto html_group(O)( auto return ref const O obj, ) { string _txt = munge_html(obj); string o = format(q"¶

%s

¶", obj.is_a, _txt ); return o; } #+END_SRC ****** block #+name: html_objects #+BEGIN_SRC d auto html_block(O)( auto return ref const O obj, ) { string _txt = munge_html(obj); string o = format(q"¶

%s

¶", obj.is_a, _txt.stripRight ); return o; } #+END_SRC ****** verse #+name: html_objects #+BEGIN_SRC d auto html_verse(O)( auto return ref const O obj, ) { string _txt = munge_html(obj); string o = format(q"¶

%s

¶", obj.is_a, _txt ); return o; } #+END_SRC ****** code #+name: html_objects #+BEGIN_SRC d auto html_code(O)( auto return ref const O obj, ) { string _txt = html_special_characters_code(obj.text); string o = format(q"¶

%s

¶", obj.is_a, _txt ); return o; } #+END_SRC ****** table #+name: html_objects #+BEGIN_SRC d auto html_table(O)( auto return ref const O obj, ) { auto _tablarize(O)( auto return ref const O obj, string _txt, ) { string[] _table_rows = _txt.split(rgx.table_delimiter_row); string[] _table_cols; string _table; string _tablenote; foreach(row_idx, row; _table_rows) { _table_cols = row.split(rgx.table_delimiter_col); _table ~= ""; foreach(col_idx, cell; _table_cols) { if ((_table_cols.length == 1) && (_table_rows.length <= row_idx+2)) { // check row_idx+2 (rather than == ++row_idx) _tablenote ~= cell; } else { string _col_is = (row_idx == 0 && obj.table_heading) ? "th" : "td"; string _align = ("style=\"text-align:" ~ ((obj.table_column_aligns[col_idx] == "l") ? "left\"" : "right\"")); _table ~= "<" ~ _col_is ~ " width=\"" ~ obj.table_column_widths[col_idx].to!string ~ "%\" " ~ _align ~ ">"; _table ~= cell; _table ~= ""; } } _table ~= ""; } auto t = tuple( _table, _tablenote, ); return t; } string _txt = munge_html(obj); auto t = _tablarize(obj, _txt); _txt = t[0]; string _note = t[1]; string o = format(q"¶

%s
%s

¶", obj.is_a, _txt, _note ); return 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) **** sql related #+name: sqlite_load_object #+BEGIN_SRC d auto sqlite_load_string(O,Dm)( auto return ref const O obj, auto return ref Dm doc_matters, ) { string o; return o; } #+END_SRC #+name: sqlite_load_object #+BEGIN_SRC d auto postgresql_load_string(O,Dm)( auto return ref const O obj, auto return ref Dm doc_matters, ) { string o; return o; } #+END_SRC #+name: sqlite_load_object #+BEGIN_SRC d string sqlite_statement(O)( auto return ref const O obj, string _txt, string _html, ) { void _sql_exe(O)( string _sql, ) { writeln(_html); writeln(_sql); } string _sql; return _sql; } #+END_SRC **** heading #+name: hub_format_and_sqlite_load_objects #+BEGIN_SRC d auto heading(O)( auto return ref const O obj, ) { string[string] obj_txt = [ "text": generic_munge_sanitize_text_for_search(obj.text), "html": html_heading(obj) ]; if (doc_matters.opt.action.debug_do) { debug(sql_txt) { writeln(obj_txt["text"]); } debug(sql_html) { writeln(obj_txt["html"]); } } else { // load sql } return obj_txt; } #+END_SRC **** para #+name: hub_format_and_sqlite_load_objects #+BEGIN_SRC d auto para(O)( auto return ref const O obj, ) { string[string] obj_txt = [ "text": generic_munge_sanitize_text_for_search(obj.text), "html": html_para(obj) ]; if (doc_matters.opt.action.debug_do) { debug(sql_txt) { writeln(obj_txt["text"]); } debug(sql_html) { writeln(obj_txt["html"]); } } else { // load sql } return obj_txt; } #+END_SRC **** quote #+name: hub_format_and_sqlite_load_objects #+BEGIN_SRC d auto quote(O)( auto return ref const O obj, ) { string[string] obj_txt = [ "text": generic_munge_sanitize_text_for_search(obj.text), "html": html_quote(obj) ]; if (doc_matters.opt.action.debug_do) { debug(sql_txt) { writeln(obj_txt["text"]); } debug(sql_html) { writeln(obj_txt["html"]); } } else { // load sql } return obj_txt; } #+END_SRC **** group #+name: hub_format_and_sqlite_load_objects #+BEGIN_SRC d auto group(O)( auto return ref const O obj, ) { string[string] obj_txt = [ "text": generic_munge_sanitize_text_for_search(obj.text), "html": html_group(obj) ]; if (doc_matters.opt.action.debug_do) { debug(sql_txt) { writeln(obj_txt["text"]); } debug(sql_html) { writeln(obj_txt["html"]); } } else { // load sql } return obj_txt; } #+END_SRC **** block #+name: hub_format_and_sqlite_load_objects #+BEGIN_SRC d auto block(O)( auto return ref const O obj, ) { string[string] obj_txt = [ "text": generic_munge_sanitize_text_for_search(obj.text), "html": html_block(obj) ]; if (doc_matters.opt.action.debug_do) { debug(sql_txt) { writeln(obj_txt["text"]); } debug(sql_html) { writeln(obj_txt["html"]); } } else { // load sql } return obj_txt; } #+END_SRC **** verse #+name: hub_format_and_sqlite_load_objects #+BEGIN_SRC d auto verse(O)( auto return ref const O obj, ) { string[string] obj_txt = [ "text": generic_munge_sanitize_text_for_search(obj.text), "html": html_verse(obj) ]; if (doc_matters.opt.action.debug_do) { debug(sql_txt) { writeln(obj_txt["text"]); } debug(sql_html) { writeln(obj_txt["html"]); } } else { // load sql } return obj_txt; } #+END_SRC **** code #+name: hub_format_and_sqlite_load_objects #+BEGIN_SRC d auto code(O)( auto return ref const O obj, ) { string[string] obj_txt = [ "text": generic_munge_sanitize_text_for_search(obj.text), "html": html_code(obj) ]; if (doc_matters.opt.action.debug_do) { debug(sql_txt) { writeln(obj_txt["text"]); } debug(sql_html) { writeln(obj_txt["html"]); } } else { // load sql } return obj_txt; } #+END_SRC **** table #+name: hub_format_and_sqlite_load_objects #+BEGIN_SRC d auto table(O)( auto return ref const O obj, ) { string[string] obj_txt = [ "text": generic_munge_sanitize_text_for_search(obj.text), "html": html_table(obj) ]; if (doc_matters.opt.action.debug_do) { debug(sql_txt) { writeln(obj_txt["text"]); } debug(sql_html) { writeln(obj_txt["html"]); } } else { // load sql } return obj_txt; } #+END_SRC ** 4. loop, identify, load - loop template #+name: sqlite_objects_loop #+BEGIN_SRC d auto format_and_sqlite_load = SQLiteFormatAndLoadObject!()(doc_matters); string[string] obj_txt; string doc_text; string[] _insert_doc_objects; foreach (part; doc_parts) { foreach (obj; doc_abstraction[part]) { switch (obj.of_part) { case "frontmatter": assert(part == "head", part); switch (obj.is_of) { case "para": switch (obj.is_a) { case "heading": obj_txt = format_and_sqlite_load.heading(obj); break; default: if ((doc_matters.opt.action.debug_do)) { writeln(__FILE__, ":", __LINE__, ": ", obj.is_a); } break; } break; default: if ((doc_matters.opt.action.debug_do)) { writeln(__FILE__, ":", __LINE__, ": ", obj.is_of); } break; } break; case "body": // assert(part == "body", part); // TODO broken switch (obj.is_of) { case "para": switch (obj.is_a) { case "heading": debug (asserts) { // TODO consider and fix or remove if (part != "body") { writeln(__LINE__, ": ", obj.text); } } obj_txt = format_and_sqlite_load.heading(obj); break; case "para": obj_txt = format_and_sqlite_load.para(obj); break; default: if ((doc_matters.opt.action.debug_do)) { writeln(__FILE__, ":", __LINE__, ": ", obj.is_a); } break; } break; case "block": switch (obj.is_a) { case "quote": obj_txt = format_and_sqlite_load.quote(obj); break; case "group": obj_txt = format_and_sqlite_load.group(obj); break; case "block": obj_txt = format_and_sqlite_load.block(obj); break; case "poem": // double check on keeping both poem & verse break; case "verse": obj_txt = format_and_sqlite_load.verse(obj); break; case "code": obj_txt = format_and_sqlite_load.code(obj); break; case "table": obj_txt = format_and_sqlite_load.table(obj); break; default: if ((doc_matters.opt.action.debug_do)) { writeln(__FILE__, ":", __LINE__, ": ", obj.is_a); } break; } break; default: if ((doc_matters.opt.action.debug_do)) { writeln(__FILE__, ":", __LINE__, ": ", obj.is_of); } break; } break; case "backmatter": assert(part == "endnotes" || "glossary" || "bibliography" || "bookindex_seg" || "blurb" || "tail", part); switch (obj.is_of) { case "para": switch (obj.is_a) { case "heading": obj_txt = format_and_sqlite_load.heading(obj); break; case "endnote": assert(part == "endnotes", part); obj_txt = format_and_sqlite_load.para(obj); break; case "glossary": assert(part == "glossary", part); obj_txt = format_and_sqlite_load.para(obj); break; case "bibliography": assert(part == "bibliography", part); obj_txt = format_and_sqlite_load.para(obj); break; case "bookindex": assert(part == "bookindex_seg", part); obj_txt = format_and_sqlite_load.para(obj); break; case "blurb": assert(part == "blurb", part); obj_txt = format_and_sqlite_load.para(obj); break; default: if ((doc_matters.opt.action.debug_do)) { writeln(__FILE__, ":", __LINE__, ": ", obj.is_a); } break; } break; default: if ((doc_matters.opt.action.debug_do)) { writeln(__FILE__, ":", __LINE__, ": ", obj.is_of); } break; } break; case "comment": break; default: if ((doc_matters.opt.action.debug_do)) { writeln(__FILE__, ":", __LINE__, ": ", obj.of_part); // check where empty value could come from writeln(__FILE__, ":", __LINE__, ": ", obj.is_a); writeln(__FILE__, ":", __LINE__, ": ", obj.text); // check where empty value could come from } break; } if (obj.is_a == "heading") { if ((doc_matters.opt.action.verbose)) { writeln( "markup: ", obj.heading_lev_markup, "> ", obj.dom_markedup, "; collapsed: ", obj.heading_lev_collapsed, "> ", obj.dom_collapsed, "; ocn: ", obj.ocn, " node: ", obj.node, "; parent: ", obj.parent_lev_markup, "; ocn: ", obj.parent_ocn, "; ", ); } } if (!(obj.is_a == "comment")) { _insert_doc_objects ~= insertDocObjectsRow(obj); } } // loop closes } return _insert_doc_objects.join.to!(char[]).toUTF8; #+END_SRC ** 5. SQL statements *** create tables **** DROP INDEX IF EXISTS #+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 src_filename; DROP INDEX IF EXISTS language_document_char; DROP INDEX IF EXISTS classify_topic_register; #+END_SRC **** DROP TABLE IF EXISTS #+name: sqlite_statement_drop_existing_tables #+BEGIN_SRC sql DROP TABLE IF EXISTS metadata_and_text; DROP TABLE IF EXISTS doc_objects; DROP TABLE IF EXISTS urls; #+END_SRC **** CREATE TABLE metadata_and_text #+name: sqlite_statement_create_table_metadata_and_src_txt #+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 */ title VARCHAR(800) NOT NULL, title_main VARCHAR(400) NOT NULL, title_sub VARCHAR(400) NULL, title_short VARCHAR(400) NULL, title_edition VARCHAR(10) NULL, title_note VARCHAR(2500) NULL, title_language VARCHAR(100) NULL, title_language_char VARCHAR(6) NULL, creator_author VARCHAR(600) NOT NULL, creator_author_email VARCHAR(100) NULL, creator_author_hon VARCHAR(100) NULL, creator_author_nationality VARCHAR(100) NULL, creator_editor VARCHAR(600) NULL, creator_contributor VARCHAR(600) NULL, creator_illustrator VARCHAR(600) NULL, creator_photographer VARCHAR(600) NULL, creator_translator VARCHAR(600) NULL, creator_prepared_by VARCHAR(600) NULL, creator_digitized_by VARCHAR(600) NULL, creator_audio VARCHAR(600) NULL, creator_video VARCHAR(600) NULL, language_document VARCHAR(100) NULL, language_document_char VARCHAR(6) NOT NULL, language_original VARCHAR(100) NULL, language_original_char VARCHAR(6) NULL, date_added_to_site VARCHAR(10) NULL, date_available VARCHAR(10) NULL, date_created VARCHAR(10) NULL, date_issued VARCHAR(10) NULL, date_modified VARCHAR(10) NULL, date_published VARCHAR(10) NULL, date_valid VARCHAR(10) NULL, date_translated VARCHAR(10) NULL, date_original_publication VARCHAR(10) NULL, date_generated VARCHAR(10) NULL, publisher VARCHAR(600) NULL, original_publisher VARCHAR(600) NULL, original_language VARCHAR(100) NULL, original_language_char VARCHAR(6) NULL, original_source VARCHAR(600) NULL, original_institution VARCHAR(600) NULL, original_nationality VARCHAR(100) 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_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, identifier_isbn VARCHAR(16) NULL, classify_topic_register VARCHAR(2500) NULL, classify_subject VARCHAR(600) NULL, classify_loc VARCHAR(30) NULL, classify_dewey VARCHAR(30) NULL, classify_keywords VARCHAR(600) NULL, notes_abstract TEXT NULL, notes_description TEXT NULL, notes_comment TEXT NULL, notes_coverage VARCHAR(200) NULL, notes_relation VARCHAR(200) NULL, notes_history VARCHAR(600) NULL, notes_type VARCHAR(600) NULL, notes_format VARCHAR(600) NULL, notes_prefix TEXT NULL, notes_prefix_a TEXT NULL, notes_prefix_b TEXT NULL, notes_suffix TEXT NULL, src_filename VARCHAR(256) NOT NULL, src_fingerprint VARCHAR(256) NULL, src_filesize VARCHAR(10) NULL, src_wordcount VARCHAR(10) NULL, pod_name VARCHAR(256) NULL, /* zipped pod, work to be done here */ pod_fingerprint VARCHAR(256) NULL, /* zipped pod, work to be done here */ pod_size VARCHAR(10) NULL, /* zipped pod, work to be done here */ src_text TEXT NULL, fulltext TEXT NULL, links TEXT NULL ); #+END_SRC **** CREATE TABLE doc_objects #+name: sqlite_statement_create_table_objects #+BEGIN_SRC sql CREATE TABLE doc_objects ( lid INTEGER PRIMARY KEY, metadata_tid INTEGER REFERENCES metadata_and_text, ocn SMALLINT, ocnd VARCHAR(6), ocns VARCHAR(6), clean TEXT NULL, body TEXT NULL, book_idx TEXT NULL, seg VARCHAR(256) NULL, lev_an VARCHAR(1), lev SMALLINT NULL, lev0 SMALLINT, lev1 SMALLINT, lev2 SMALLINT, lev3 SMALLINT, lev4 SMALLINT, lev5 SMALLINT, lev6 SMALLINT, lev7 SMALLINT, en_a SMALLINT NULL, en_z SMALLINT NULL, en_a_asterisk SMALLINT NULL, en_z_asterisk SMALLINT NULL, en_a_plus SMALLINT NULL, en_z_plus SMALLINT NULL, t_of VARCHAR(16), t_is VARCHAR(16), node VARCHAR(16) NULL, parent VARCHAR(16) NULL, digest_clean CHAR(256), digest_all CHAR(256), types CHAR(1) NULL ); #+END_SRC **** CREATE INDEX #+name: sqlite_statement_create_index #+BEGIN_SRC sql CREATE INDEX idx_ocn ON doc_objects(ocn); CREATE INDEX idx_digest_clean ON doc_objects(digest_clean); CREATE INDEX idx_digest_all ON doc_objects(digest_all); CREATE INDEX idx_clean ON doc_objects(clean); CREATE INDEX idx_lev0 ON doc_objects(lev0); CREATE INDEX idx_lev1 ON doc_objects(lev1); CREATE INDEX idx_lev2 ON doc_objects(lev2); CREATE INDEX idx_lev3 ON doc_objects(lev3); CREATE INDEX idx_lev4 ON doc_objects(lev4); CREATE INDEX idx_lev5 ON doc_objects(lev5); CREATE INDEX idx_lev6 ON doc_objects(lev6); CREATE INDEX idx_title ON metadata_and_text(title); CREATE INDEX idx_author ON metadata_and_text(creator_author); 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 *** sql insert statement formatted doc objects lid unique, increment by 1 per object, not ocn metadata tid document number unique either: - increment by adding 1 for each document, - make hash of document filename or url and use? **** 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 #+name: sqlite_formatted_insertions_doc_objects #+BEGIN_SRC sql INSERT INTO doc_objects ( metadata_tid, ocn, clean, body, lev, t_of, t_is ) #+END_SRC **** VALUES #+name: sqlite_formatted_insertions_doc_objects #+BEGIN_SRC sql VALUES ( %d, %s, '%s', '%s', %s, '%s', '%s' ); #+END_SRC **** sql statement: dlang values for formatting #+name: sqlite_formatted_insertions_doc_objects #+BEGIN_SRC d ¶", _metadata_tid, obj.ocn, SQLinsertDelimiter!()(obj_txt["text"]), SQLinsertDelimiter!()(obj_txt["html"]), obj.heading_lev_markup, obj.is_of, obj.is_a, ); #+END_SRC *** sqlite insert statement formatted 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 #+name: sqlite_formatted_insertions_doc_matters_metadata #+BEGIN_SRC sql INSERT INTO metadata_and_text ( src_filename, title, title_main, title_sub, title_short, title_edition, title_language, classify_dewey, classify_keywords, classify_loc, classify_subject, classify_topic_register, creator_author, creator_author_email, creator_illustrator, creator_translator, date_added_to_site, date_available, date_created, date_issued, date_modified, date_published, date_valid, identifier_isbn, identifier_oclc, language_document, language_document_char, notes_abstract, notes_description, original_publisher, original_language, original_language_char, original_source, original_title, publisher, rights_copyright, rights_copyright_audio, rights_copyright_cover, rights_copyright_illustrations, rights_copyright_photographs, rights_copyright_text, rights_copyright_translation, rights_copyright_video, rights_license ) #+END_SRC **** VALUES #+name: sqlite_formatted_insertions_doc_matters_metadata #+BEGIN_SRC sql VALUES ( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ); #+END_SRC **** sql statement: values for formatting #+name: sqlite_formatted_insertions_doc_matters_metadata #+BEGIN_SRC d ¶", SQLinsertDelimiter!()(doc_matters.src.filename), 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), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.title_short), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.title_edition), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.title_language), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.classify_dewey), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.classify_keywords), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.classify_loc), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.classify_subject), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.classify_topic_register), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.creator_author), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.creator_author_email), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.creator_illustrator), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.creator_translator), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_added_to_site), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_available), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_created), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_issued), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_modified), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_published), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.date_valid), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.identifier_isbn), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.identifier_oclc), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.language_document), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.language_document_char), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.notes_abstract), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.notes_description), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.original_publisher), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.original_language), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.original_language_char), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.original_source), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.original_title), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.publisher), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_audio), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_cover), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_illustrations), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_photographs), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_text), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_translation), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_copyright_video), SQLinsertDelimiter!()(doc_matters.conf_make_meta.meta.rights_license) ); #+END_SRC * __END__