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 --- src/doc_reform/io_out/sqlite.d | 56 +++++++++++++++++++++--------------------- 1 file changed, 28 insertions(+), 28 deletions(-) (limited to 'src/doc_reform/io_out/sqlite.d') 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; } -- cgit v1.2.3