diff options
Diffstat (limited to 'org/output_sqlite.org')
| -rw-r--r-- | org/output_sqlite.org | 264 |
1 files changed, 152 insertions, 112 deletions
diff --git a/org/output_sqlite.org b/org/output_sqlite.org index dc76da4..eeff4f1 100644 --- a/org/output_sqlite.org +++ b/org/output_sqlite.org @@ -33,19 +33,20 @@ 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, ) { <<sqlite_db_statement_composite_collection>> + writeln(" ", pth_sqlite.sqlite_file); } } SQLiteDbStatementComposite!()(db, doc_abstraction, doc_matters); @@ -58,19 +59,20 @@ template SQLiteHubBuildTablesAndPopulate() { #+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d 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, ) { <<sqlite_db_statement_composite_discrete>> + writeln(" ", pth_sqlite.sqlite_file(doc_matters.src.filename)); } } SQLiteDiscreteDbStatementComposite!()(db, doc_abstraction, doc_matters); @@ -83,10 +85,10 @@ template SQLiteHubDiscreteBuildTablesAndPopulate() { #+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d 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); @@ -98,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); @@ -130,7 +130,7 @@ template SQLinsertDelimiter() { } template SQLiteFormatAndLoadObject() { auto SQLiteFormatAndLoadObject(I)( - auto ref I doc_matters, + I doc_matters, ) { mixin SiSUoutputRgxInit; struct sqlite_format_and_load_objects { @@ -146,6 +146,7 @@ template SQLiteFormatAndLoadObject() { #+END_SRC *** sqlite instructions +**** create #+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d template SQLiteTablesReCreate() { @@ -161,20 +162,43 @@ template SQLiteTablesReCreate() { return _sql_instruct; } } +#+END_SRC + +**** delete + +#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d +template SQLiteDeleteDocument() { + string SQLiteDeleteDocument(I)( + I doc_matters, + ) { + <<sqlite_formatted_delete>> + return _delete_uid; + } +} +#+END_SRC + +**** insert + +#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d template SQLiteInsertMetadata() { string SQLiteInsertMetadata(I)( - auto ref I doc_matters, + I doc_matters, ) { <<sqlite_formatted_insertions_doc_matters_metadata>> return _insert_metadata; } } +#+END_SRC + +**** insert doc objects loop + +#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d 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) { <<sqlite_formatted_insertions_doc_objects>> return _insert_doc_objects_row; @@ -182,6 +206,11 @@ template SQLiteInsertDocObjectsLoop() { <<sqlite_objects_loop>> } } +#+END_SRC + +**** tables create + +#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d template SQLiteTablesCreate() { void SQLiteTablesCreate(E,O)(E env, O opt_action) { import d2sqlite3; @@ -198,9 +227,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 { @@ -210,9 +239,23 @@ template SQLiteTablesCreate() { } } } -template SQLiteTablesDrop() { - void SQLiteTablesDrop()() { - writeln("table drop"); +#+END_SRC + +**** tables drop + +#+BEGIN_SRC d :tangle ../src/sdp/output/sqlite.d +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 + } + } } } #+END_SRC @@ -225,19 +268,24 @@ template SQLiteTablesDrop() { { 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; @@ -253,7 +301,7 @@ template SQLiteTablesDrop() { { _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; @@ -646,44 +694,7 @@ auto html_table(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) +*** 2. hub (sqlite_format_and_load_objects) **** sql related #+name: sqlite_load_object @@ -1088,22 +1099,16 @@ return _insert_doc_objects.join.to!(char[]).toUTF8; #+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 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; #+END_SRC **** DROP TABLE IF EXISTS @@ -1121,9 +1126,9 @@ DROP TABLE IF EXISTS urls; #+name: sqlite_statement_create_table_metadata_and_src_txt #+BEGIN_SRC sql 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, @@ -1213,24 +1218,24 @@ CREATE TABLE metadata_and_text ( #+name: sqlite_statement_create_table_objects #+BEGIN_SRC sql 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 ); #+END_SRC @@ -1244,11 +1249,44 @@ CREATE INDEX idx_digest_all ON doc_objects(digest_all); 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); #+END_SRC +*** TODO local site link & info + +*** delete rows (delete document) +**** DELETE uid rows doc matters & metadata +***** sql statement: dlang format + +#+name: sqlite_formatted_delete +#+BEGIN_SRC d +string _uid = doc_matters.src.doc_uid; +string _delete_uid = format(q"¶ +#+END_SRC + +***** DELETE FROM ... WHERE + +#+name: sqlite_formatted_delete +#+BEGIN_SRC sql +DELETE FROM metadata_and_text +WHERE uid = '%s'; +DELETE FROM doc_objects +WHERE uid_metadata_and_text = '%s'; +#+END_SRC + +***** VALUES + +#+name: sqlite_formatted_delete +#+BEGIN_SRC d +¶", + _uid, + _uid, +); +#+END_SRC + *** inserts **** INSERT doc objects @@ -1263,8 +1301,7 @@ either: #+name: sqlite_formatted_insertions_doc_objects #+BEGIN_SRC d -string _insert_doc_objects_row; -_insert_doc_objects_row = format(q"¶ +string _insert_doc_objects_row = format(q"¶ #+END_SRC ***** INSERT INTO @@ -1272,7 +1309,7 @@ _insert_doc_objects_row = format(q"¶ #+name: sqlite_formatted_insertions_doc_objects #+BEGIN_SRC sql INSERT INTO doc_objects ( - metadata_tid, + uid_metadata_and_text, ocn, clean, body, @@ -1287,7 +1324,7 @@ _insert_doc_objects_row = format(q"¶ #+name: sqlite_formatted_insertions_doc_objects #+BEGIN_SRC sql VALUES ( - %d, + '%s', %s, '%s', '%s', @@ -1302,7 +1339,7 @@ _insert_doc_objects_row = format(q"¶ #+name: sqlite_formatted_insertions_doc_objects #+BEGIN_SRC d ¶", - _metadata_tid, + _uid, obj.metainfo.ocn, SQLinsertDelimiter!()(obj_txt["text"]), SQLinsertDelimiter!()(obj_txt["html"]), @@ -1312,13 +1349,13 @@ _insert_doc_objects_row = format(q"¶ ); #+END_SRC -**** INSERT doc matters metadata +**** INSERT doc matters & metadata ***** sql statement: dlang format #+name: sqlite_formatted_insertions_doc_matters_metadata #+BEGIN_SRC d -string _insert_metadata; -_insert_metadata = format(q"¶ +string _uid = SQLinsertDelimiter!()(doc_matters.src.doc_uid); +string _insert_metadata = format(q"¶ #+END_SRC ***** INSERT INTO @@ -1326,6 +1363,7 @@ _insert_metadata = format(q"¶ #+name: sqlite_formatted_insertions_doc_matters_metadata #+BEGIN_SRC sql INSERT INTO metadata_and_text ( + uid, src_filename, src_composite_id_per_txt, src_composite_id_per_pod, @@ -1425,6 +1463,7 @@ _insert_metadata = format(q"¶ '%s', '%s', '%s', + '%s', '%s' ); #+END_SRC @@ -1434,6 +1473,7 @@ _insert_metadata = format(q"¶ #+name: sqlite_formatted_insertions_doc_matters_metadata #+BEGIN_SRC d ¶", + _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), |