aboutsummaryrefslogtreecommitdiffhomepage
path: root/src/sdp/output/sqlite.d
diff options
context:
space:
mode:
Diffstat (limited to 'src/sdp/output/sqlite.d')
-rw-r--r--src/sdp/output/sqlite.d225
1 files changed, 124 insertions, 101 deletions
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
+ }
+ }
}
}
generated by cgit v1.2.3 (git 2.25.1) at 2024-07-04 13:54:34 +0000