sqlite, minor, cosmetic in use case, if not exist

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;
       }