aboutsummaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
authorRalph Amissah <ralph.amissah@gmail.com>2022-01-16 20:29:35 -0500
committerRalph Amissah <ralph.amissah@gmail.com>2022-01-16 20:30:33 -0500
commit33a5e142313b2c79fd136f79a954cd3c196c2fc9 (patch)
tree7b79adad9f6890bd41ee21fe945be70619d6675a
parenta new year (diff)
sqlite, minor, cosmetic in use case, if not exist
-rw-r--r--org/out_sqlite.org36
-rw-r--r--src/doc_reform/io_out/sqlite.d56
2 files changed, 46 insertions, 46 deletions
diff --git a/org/out_sqlite.org b/org/out_sqlite.org
index 69723f7..fd7ddbd 100644
--- a/org/out_sqlite.org
+++ b/org/out_sqlite.org
@@ -147,9 +147,9 @@ template SQLiteDbRun() {
}
try {
db.run(
- "\nBEGIN;\n" ~
+ "\nBEGIN TRANSACTION;\n" ~
db_statement ~
- "\nCOMMIT;\n"
+ "\nCOMMIT TRANSACTION;\n"
);
} catch (ErrnoException ex) {
writeln("ERROR SQLite : ", ex);
@@ -1544,11 +1544,11 @@ DROP TABLE IF EXISTS urls;
#+END_SRC
*** create tables and index
-**** CREATE TABLE metadata_and_text
+**** CREATE TABLE IF NOT EXISTS metadata_and_text
#+NAME: sqlite_statement_create_table_metadata_and_src_txt
#+BEGIN_SRC sql
-CREATE TABLE metadata_and_text (
+CREATE TABLE IF NOT EXISTS metadata_and_text (
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
@@ -1639,11 +1639,11 @@ CREATE TABLE metadata_and_text (
);
#+END_SRC
-**** CREATE TABLE topic_register
+**** CREATE TABLE IF NOT EXISTS topic_register
#+NAME: sqlite_statement_create_table_topic_register
#+BEGIN_SRC sql
-CREATE TABLE topic_register (
+CREATE TABLE IF NOT EXISTS topic_register (
-- tid BIGINT PRIMARY KEY,
uid_metadata_and_text VARCHAR(256) REFERENCES metadata_and_text(uid) ON DELETE CASCADE,
-- src_composite_id_per_txt VARCHAR(256) NOT NULL, - UNIQUE, - z pod name if any + src filename + language code
@@ -1663,7 +1663,7 @@ CREATE TABLE topic_register (
#+NAME: sqlite_statement_create_table_site_urls
#+BEGIN_SRC sql
-CREATE TABLE site_urls (
+CREATE TABLE IF NOT EXISTS site_urls (
-- tid BIGINT PRIMARY KEY,
uid_metadata_and_text VARCHAR(256) REFERENCES metadata_and_text(uid) ON DELETE CASCADE,
src_composite_id_per_txt VARCHAR(256) NOT NULL, -- UNIQUE, - z pod name if any + src filename + language code
@@ -1678,7 +1678,7 @@ CREATE TABLE site_urls (
#+NAME: sqlite_statement_create_table_objects
#+BEGIN_SRC sql
-CREATE TABLE doc_objects (
+CREATE TABLE IF NOT EXISTS doc_objects (
lid BIGINT PRIMARY KEY,
uid_metadata_and_text VARCHAR(256) REFERENCES metadata_and_text(uid) ON DELETE CASCADE,
ocn SMALLINT,
@@ -1704,16 +1704,16 @@ CREATE TABLE doc_objects (
#+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_title ON metadata_and_text(title);
-CREATE INDEX idx_author ON metadata_and_text(creator_author_last_first);
-CREATE INDEX idx_uid ON metadata_and_text(uid);
-CREATE INDEX idx_filename ON metadata_and_text(src_filename_base);
-CREATE INDEX idx_language ON metadata_and_text(language_document_char);
-CREATE INDEX idx_topics ON metadata_and_text(classify_topic_register);
+CREATE INDEX IF NOT EXISTS idx_ocn ON doc_objects(ocn);
+CREATE INDEX IF NOT EXISTS idx_digest_clean ON doc_objects(digest_clean);
+CREATE INDEX IF NOT EXISTS idx_digest_all ON doc_objects(digest_all);
+CREATE INDEX IF NOT EXISTS idx_clean ON doc_objects(clean);
+CREATE INDEX IF NOT EXISTS idx_title ON metadata_and_text(title);
+CREATE INDEX IF NOT EXISTS idx_author ON metadata_and_text(creator_author_last_first);
+CREATE INDEX IF NOT EXISTS idx_uid ON metadata_and_text(uid);
+CREATE INDEX IF NOT EXISTS idx_filename ON metadata_and_text(src_filename_base);
+CREATE INDEX IF NOT EXISTS idx_language ON metadata_and_text(language_document_char);
+CREATE INDEX IF NOT EXISTS idx_topics ON metadata_and_text(classify_topic_register);
#+END_SRC
*** TODO local site link & info
diff --git a/src/doc_reform/io_out/sqlite.d b/src/doc_reform/io_out/sqlite.d
index 8dc0f62..3a0b67c 100644
--- a/src/doc_reform/io_out/sqlite.d
+++ b/src/doc_reform/io_out/sqlite.d
@@ -153,9 +153,9 @@ template SQLiteDbRun() {
}
try {
db.run(
- "\nBEGIN;\n" ~
+ "\nBEGIN TRANSACTION;\n" ~
db_statement ~
- "\nCOMMIT;\n"
+ "\nCOMMIT TRANSACTION;\n"
);
} catch (ErrnoException ex) {
writeln("ERROR SQLite : ", ex);
@@ -879,7 +879,7 @@ template SQLiteTablesReCreate() {
DROP TABLE IF EXISTS topic_register;
DROP TABLE IF EXISTS doc_objects;
DROP TABLE IF EXISTS urls;
- CREATE TABLE metadata_and_text (
+ CREATE TABLE IF NOT EXISTS metadata_and_text (
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
@@ -968,7 +968,7 @@ template SQLiteTablesReCreate() {
site_url_epub VARCHAR(256) NULL,
links TEXT NULL
);
- CREATE TABLE topic_register (
+ CREATE TABLE IF NOT EXISTS topic_register (
-- tid BIGINT PRIMARY KEY,
uid_metadata_and_text VARCHAR(256) REFERENCES metadata_and_text(uid) ON DELETE CASCADE,
-- src_composite_id_per_txt VARCHAR(256) NOT NULL, - UNIQUE, - z pod name if any + src filename + language code
@@ -982,7 +982,7 @@ template SQLiteTablesReCreate() {
site_url_html_toc VARCHAR(256) NULL,
site_url_html_scroll VARCHAR(256) NULL
);
- CREATE TABLE doc_objects (
+ CREATE TABLE IF NOT EXISTS doc_objects (
lid BIGINT PRIMARY KEY,
uid_metadata_and_text VARCHAR(256) REFERENCES metadata_and_text(uid) ON DELETE CASCADE,
ocn SMALLINT,
@@ -1002,16 +1002,16 @@ template SQLiteTablesReCreate() {
seg_name CHAR(256),
types CHAR(1) NULL
);
- 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_title ON metadata_and_text(title);
- CREATE INDEX idx_author ON metadata_and_text(creator_author_last_first);
- CREATE INDEX idx_uid ON metadata_and_text(uid);
- CREATE INDEX idx_filename ON metadata_and_text(src_filename_base);
- CREATE INDEX idx_language ON metadata_and_text(language_document_char);
- CREATE INDEX idx_topics ON metadata_and_text(classify_topic_register);
+ CREATE INDEX IF NOT EXISTS idx_ocn ON doc_objects(ocn);
+ CREATE INDEX IF NOT EXISTS idx_digest_clean ON doc_objects(digest_clean);
+ CREATE INDEX IF NOT EXISTS idx_digest_all ON doc_objects(digest_all);
+ CREATE INDEX IF NOT EXISTS idx_clean ON doc_objects(clean);
+ CREATE INDEX IF NOT EXISTS idx_title ON metadata_and_text(title);
+ CREATE INDEX IF NOT EXISTS idx_author ON metadata_and_text(creator_author_last_first);
+ CREATE INDEX IF NOT EXISTS idx_uid ON metadata_and_text(uid);
+ CREATE INDEX IF NOT EXISTS idx_filename ON metadata_and_text(src_filename_base);
+ CREATE INDEX IF NOT EXISTS idx_language ON metadata_and_text(language_document_char);
+ CREATE INDEX IF NOT EXISTS idx_topics ON metadata_and_text(classify_topic_register);
┃",);
return _sql_instruct;
}
@@ -1409,7 +1409,7 @@ template SQLiteTablesCreate() {
DROP TABLE IF EXISTS topic_register;
DROP TABLE IF EXISTS doc_objects;
DROP TABLE IF EXISTS urls;
- CREATE TABLE metadata_and_text (
+ CREATE TABLE IF NOT EXISTS metadata_and_text (
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
@@ -1498,7 +1498,7 @@ template SQLiteTablesCreate() {
site_url_epub VARCHAR(256) NULL,
links TEXT NULL
);
- CREATE TABLE topic_register (
+ CREATE TABLE IF NOT EXISTS topic_register (
-- tid BIGINT PRIMARY KEY,
uid_metadata_and_text VARCHAR(256) REFERENCES metadata_and_text(uid) ON DELETE CASCADE,
-- src_composite_id_per_txt VARCHAR(256) NOT NULL, - UNIQUE, - z pod name if any + src filename + language code
@@ -1512,7 +1512,7 @@ template SQLiteTablesCreate() {
site_url_html_toc VARCHAR(256) NULL,
site_url_html_scroll VARCHAR(256) NULL
);
- CREATE TABLE doc_objects (
+ CREATE TABLE IF NOT EXISTS doc_objects (
lid BIGINT PRIMARY KEY,
uid_metadata_and_text VARCHAR(256) REFERENCES metadata_and_text(uid) ON DELETE CASCADE,
ocn SMALLINT,
@@ -1532,16 +1532,16 @@ template SQLiteTablesCreate() {
seg_name CHAR(256),
types CHAR(1) NULL
);
- 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_title ON metadata_and_text(title);
- CREATE INDEX idx_author ON metadata_and_text(creator_author_last_first);
- CREATE INDEX idx_uid ON metadata_and_text(uid);
- CREATE INDEX idx_filename ON metadata_and_text(src_filename_base);
- CREATE INDEX idx_language ON metadata_and_text(language_document_char);
- CREATE INDEX idx_topics ON metadata_and_text(classify_topic_register);
+ CREATE INDEX IF NOT EXISTS idx_ocn ON doc_objects(ocn);
+ CREATE INDEX IF NOT EXISTS idx_digest_clean ON doc_objects(digest_clean);
+ CREATE INDEX IF NOT EXISTS idx_digest_all ON doc_objects(digest_all);
+ CREATE INDEX IF NOT EXISTS idx_clean ON doc_objects(clean);
+ CREATE INDEX IF NOT EXISTS idx_title ON metadata_and_text(title);
+ CREATE INDEX IF NOT EXISTS idx_author ON metadata_and_text(creator_author_last_first);
+ CREATE INDEX IF NOT EXISTS idx_uid ON metadata_and_text(uid);
+ CREATE INDEX IF NOT EXISTS idx_filename ON metadata_and_text(src_filename_base);
+ CREATE INDEX IF NOT EXISTS idx_language ON metadata_and_text(language_document_char);
+ CREATE INDEX IF NOT EXISTS idx_topics ON metadata_and_text(classify_topic_register);
┃",);
return _sql_instruct;
}