From 33a5e142313b2c79fd136f79a954cd3c196c2fc9 Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Sun, 16 Jan 2022 20:29:35 -0500 Subject: sqlite, minor, cosmetic in use case, if not exist --- org/out_sqlite.org | 36 ++++++++++++++++++------------------ 1 file changed, 18 insertions(+), 18 deletions(-) (limited to 'org') 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 -- cgit v1.2.3