aboutsummaryrefslogtreecommitdiffhomepage
path: root/org/output_sqlite.org
diff options
context:
space:
mode:
Diffstat (limited to 'org/output_sqlite.org')
-rw-r--r--org/output_sqlite.org264
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),