From fc2471c91966186fc7bc0a5ac2217496c9e43eea Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Mon, 16 Jul 2018 22:39:09 -0400 Subject: 0.26.5 sqlite output, focus --- src/sdp/output/epub3.d | 6 +- src/sdp/output/html.d | 9 +- src/sdp/output/hub.d | 21 ++-- src/sdp/output/paths_output.d | 9 +- src/sdp/output/sqlite.d | 225 +++++++++++++++++++++++------------------- 5 files changed, 148 insertions(+), 122 deletions(-) (limited to 'src/sdp/output') diff --git a/src/sdp/output/epub3.d b/src/sdp/output/epub3.d index cde42d1..d10b7f2 100644 --- a/src/sdp/output/epub3.d +++ b/src/sdp/output/epub3.d @@ -758,8 +758,7 @@ template outputEPub3() { zip.addMember(zip_arc_member_file); createZipFile!()(fn_epub, zip.build()); } - } - catch (ErrnoException ex) { + } catch (ErrnoException ex) { // Handle error } writeln(" ", fn_epub); @@ -771,8 +770,7 @@ template outputEPub3() { auto data = zipped.expand(member); writeln(filename, " length ", data.length); } - } - catch (ZipException ex) { + } catch (ZipException ex) { // Handle errors } } diff --git a/src/sdp/output/html.d b/src/sdp/output/html.d index 7024b0f..ba53eef 100644 --- a/src/sdp/output/html.d +++ b/src/sdp/output/html.d @@ -174,8 +174,7 @@ template outputHTML() { foreach (o; doc) { f.writeln(o); } - } - catch (ErrnoException ex) { + } catch (ErrnoException ex) { // Handle error } writeln(" ", pth_html.fn_scroll(doc_matters.src.filename)); @@ -431,8 +430,7 @@ template outputHTML() { } f.writeln(xhtml_format.tail); } - } - catch (ErrnoException ex) { + } catch (ErrnoException ex) { // handle error } writeln(" ", pth_html.fn_seg(doc_matters.src.filename, "toc")); @@ -450,8 +448,7 @@ template outputHTML() { f.writeln(css.html_seg_css); f = File(pth_html.fn_scroll_css, "w"); f.writeln(css.html_scroll_css); - } - catch (ErrnoException ex) { + } catch (ErrnoException ex) { // Handle error } } diff --git a/src/sdp/output/hub.d b/src/sdp/output/hub.d index b6caca9..47713f5 100644 --- a/src/sdp/output/hub.d +++ b/src/sdp/output/hub.d @@ -124,7 +124,14 @@ template outputHub() { } SQLiteHubDiscreteBuildTablesAndPopulate!()(doc_abstraction, doc_matters); } - if (doc_matters.opt.action.sqlite_update) { + if (doc_matters.opt.action.sqlite_insert + || doc_matters.opt.action.sqlite_update + ) { + if ((doc_matters.opt.action.verbose)) { + writeln("sqlite processing... "); + } + SQLiteHubBuildTablesAndPopulate!()(doc_abstraction, doc_matters); + } else if (doc_matters.opt.action.sqlite_delete) { if ((doc_matters.opt.action.verbose)) { writeln("sqlite processing... "); } @@ -148,17 +155,17 @@ template outputHubOp() { void outputHubOp(E,O)(E env, O opt_action) { mixin SiSUoutputRgxInit; static auto rgx = Rgx(); - if ((opt_action.sqlite_create)) { + if ((opt_action.sqlite_db_drop)) { if ((opt_action.verbose)) { - writeln("sqlite create table... "); + writeln("sqlite drop db... "); } - SQLiteTablesCreate!()(env, opt_action); + SQLiteDbDrop!()(opt_action); } - if ((opt_action.sqlite_drop)) { + if ((opt_action.sqlite_db_create)) { if ((opt_action.verbose)) { - writeln("sqlite drop table... "); + writeln("sqlite create table... "); } - SQLiteTablesDrop!()(); + SQLiteTablesCreate!()(env, opt_action); } } } diff --git a/src/sdp/output/paths_output.d b/src/sdp/output/paths_output.d index 8a4d623..3b18c75 100644 --- a/src/sdp/output/paths_output.d +++ b/src/sdp/output/paths_output.d @@ -243,7 +243,7 @@ template SiSUpathsSQLiteDiscrete() { return asNormalizedPath(base.chainPath(base_filename(fn_src))).array; } string sqlite_file(string fn_src) { - return asNormalizedPath(base.chainPath(base_filename(fn_src) ~ ".sqlite")).array; + return asNormalizedPath(base.chainPath(base_filename(fn_src) ~ ".sql.db")).array; } } return _PathsStruct(); @@ -252,7 +252,8 @@ template SiSUpathsSQLiteDiscrete() { template SiSUpathsSQLite() { mixin SiSUrgxInit; static auto rgx = Rgx(); - auto SiSUpathsSQLite(Po)( + auto SiSUpathsSQLite(DbN, Po)( + DbN db_name, Po output_pth_root, ) { struct _PathsStruct { @@ -264,8 +265,8 @@ template SiSUpathsSQLite() { string base_dir = "sqlite"; return asNormalizedPath((out_pth.output_root).chainPath(base_dir)).array; } - string sqlite_file(string fn_src = "sdp-niu") { - return asNormalizedPath(base.chainPath(base_filename(fn_src) ~ ".sqlite")).array; + string sqlite_file() { + return asNormalizedPath(base.chainPath(base_filename(db_name) ~ ".sql.db")).array; } } return _PathsStruct(); 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 + } + } } } -- cgit v1.2.3