From 1e687b1821d25da40fdedf042c1cf60606a0c7db Mon Sep 17 00:00:00 2001 From: ketmar Date: Wed, 13 Oct 2021 14:45:29 +0000 Subject: [PATCH] sqlite: adapted to new sq3 API FossilOrigin-Name: 2add2d08cb76ae7f56c01c1139a4fac0b2745a29c6e1048dab4259994358ef73 --- sqbase_experiment/chiro_sqbase.d | 118 ++++++++++++++++----------------------- 1 file changed, 48 insertions(+), 70 deletions(-) diff --git a/sqbase_experiment/chiro_sqbase.d b/sqbase_experiment/chiro_sqbase.d index ef86b80..a2b60e4 100644 --- a/sqbase_experiment/chiro_sqbase.d +++ b/sqbase_experiment/chiro_sqbase.d @@ -23,22 +23,29 @@ non-compressed data to this function. // ////////////////////////////////////////////////////////////////////////// // -static immutable schema = ` +private enum CommonPragmas = ` PRAGMA application_id = 1128810834; /*CHIR*/ /*PRAGMA page_size = 1024;*/ PRAGMA auto_vacuum = NONE; - PRAGMA journal_mode = OFF; - PRAGMA synchronous = OFF; PRAGMA encoding = "UTF-8"; PRAGMA case_sensitive_like = OFF; PRAGMA foreign_keys = OFF; - PRAGMA locking_mode = EXCLUSIVE; /*NORMAL*/ + PRAGMA locking_mode = NORMAL; /*EXCLUSIVE;*/ PRAGMA secure_delete = OFF; PRAGMA temp_store = MEMORY; /*DEFAULT*/ /*FILE*/ PRAGMA threads = 3; PRAGMA trusted_schema = OFF; PRAGMA writable_schema = OFF; +`; + +static immutable dbpragmasRO = CommonPragmas; + +static immutable dbpragmas = CommonPragmas~` + PRAGMA journal_mode = WAL; /*OFF;*/ + PRAGMA synchronous = NORMAL; /*OFF;*/ +`; +static immutable schema = ` -- deleted messages have empty headers and body -- this is so uids will remain unique on inserting CREATE TABLE IF NOT EXISTS messages ( @@ -65,6 +72,14 @@ static immutable schema = ` , read INTEGER DEFAULT 0 /* is this message read? */ ); + CREATE INDEX IF NOT EXISTS trd_by_time ON threads(time); + CREATE INDEX IF NOT EXISTS trd_by_ruid_time ON threads(parent, time); + -- this is for fast "maximum nntp index" queries + CREATE INDEX IF NOT EXISTS trd_by_nntpidx ON threads(nntpidx); + -- this is for test if we have any unread articles (we don't mind the exact numbers, tho) + CREATE INDEX IF NOT EXISTS trd_by_read ON threads(read); + + CREATE TABLE IF NOT EXISTS info ( uid INTEGER PRIMARY KEY /* rowid, corresponds to "id" in "messages", never zero */ , from_name TEXT /* can be empty */ @@ -74,6 +89,7 @@ static immutable schema = ` , to_mail TEXT /* can be empty */ ); + -- this holds msgid -- moved to separate table, because this info is used only when inserting new messages -- "type" has no gaps (except the 0, which can be absent) @@ -82,6 +98,9 @@ static immutable schema = ` , msgid TEXT /* message id */ ); + CREATE INDEX IF NOT EXISTS msgid_by_msgid ON msgids(msgid); + + -- this holds in-reply-to, and references -- moved to separate table, because this info is used only when inserting new messages CREATE TABLE IF NOT EXISTS refids ( @@ -90,6 +109,9 @@ static immutable schema = ` , msgid TEXT /* message id */ ); + CREATE INDEX IF NOT EXISTS refid_by_uid_idx ON refids(uid, idx); + + CREATE TABLE IF NOT EXISTS content ( uid INTEGER /* owner message uid */ , mime TEXT /* always lowercased */ @@ -97,82 +119,32 @@ static immutable schema = ` , content TEXT /* properly decoded */ ); + CREATE INDEX IF NOT EXISTS content_by_uid ON content(uid); + CREATE INDEX IF NOT EXISTS content_by_uid_mime ON content(uid, mime); + + CREATE TABLE IF NOT EXISTS attaches ( uid INTEGER /* owner message uid */ , mime TEXT /* always lowercased */ , name TEXT /* attachment name */ , content BLOB /* properly decoded */ ); -`; - - -// ////////////////////////////////////////////////////////////////////////// // -static immutable indicies = ` - CREATE INDEX IF NOT EXISTS trd_by_time ON threads(time); - CREATE INDEX IF NOT EXISTS trd_by_ruid_time ON threads(parent, time); - -- this is for fast "maximum nntp index" queries - CREATE INDEX IF NOT EXISTS trd_by_nntpidx ON threads(nntpidx); - -- this is for test if we have any unread articles (we don't mind the exact numbers, tho) - CREATE INDEX IF NOT EXISTS trd_by_read ON threads(read); - - CREATE INDEX IF NOT EXISTS msgid_by_msgid ON msgids(msgid); - - CREATE INDEX IF NOT EXISTS refid_by_uid_idx ON refids(uid, idx); - - CREATE INDEX IF NOT EXISTS content_by_uid ON content(uid); - CREATE INDEX IF NOT EXISTS content_by_uid_mime ON content(uid, mime); CREATE INDEX IF NOT EXISTS attaches_by_uid ON attaches(uid); CREATE INDEX IF NOT EXISTS attaches_by_uid_mime ON attaches(uid, mime); - - PRAGMA journal_mode=WAL; - PRAGMA synchronous=NORMAL; `; // ////////////////////////////////////////////////////////////////////////// // static immutable dropsupport = ` - DROP INDEX IF EXISTS trd_by_time; - DROP INDEX IF EXISTS trd_by_ruid_time; - DROP INDEX IF EXISTS trd_by_msgid; - DROP TABLE IF EXISTS threads; - - DROP INDEX IF EXISTS msgid_by_msgid; - DROP TABLE IF EXISTS msgids; - - DROP INDEX IF EXISTS refid_by_uid_idx; - DROP TABLE IF EXISTS refids; - - DROP TABLE IF EXISTS info; - - DROP INDEX IF EXISTS content_by_msguid; - DROP INDEX IF EXISTS content_by_msguid_mime; - DROP TABLE IF EXISTS content; - - DROP INDEX IF EXISTS attaches_by_msguid; - DROP INDEX IF EXISTS attaches_by_msguid_mime; - DROP TABLE IF EXISTS attaches; -`; - - -// ////////////////////////////////////////////////////////////////////////// // -static immutable dbpragmas = ` - PRAGMA application_id = 1128810834; /*CHIR*/ - PRAGMA auto_vacuum = NONE; - PRAGMA journal_mode = WAL; - PRAGMA synchronous = NORMAL; - PRAGMA encoding = "UTF-8"; - PRAGMA case_sensitive_like = OFF; - PRAGMA foreign_keys = OFF; - PRAGMA locking_mode = NORMAL; - PRAGMA secure_delete = OFF; - PRAGMA temp_store = DEFAULT; - PRAGMA threads = 3; - PRAGMA trusted_schema = OFF; - PRAGMA writable_schema = OFF; + DROP TABLE IF EXISTS threads; + DROP TABLE IF EXISTS msgids; + DROP TABLE IF EXISTS refids; + DROP TABLE IF EXISTS info; + DROP TABLE IF EXISTS content; + DROP TABLE IF EXISTS attaches; `; - // ////////////////////////////////////////////////////////////////////////// // private bool isGoodText (const(void)[] buf) pure nothrow @safe @nogc { foreach (immutable ubyte ch; cast(const(ubyte)[])buf) { @@ -582,8 +554,7 @@ private void registerFunctions (ref Database db) { public void chiroRecreateSupportTables (ref Database db) { db.execute(dropsupport); db.execute(schema); - db.execute(dbpragmas); - db.setOnClose(indicies); + db.setOnClose("VACUUM;"); } @@ -593,18 +564,25 @@ public Database chiroRecreateDB (const(char)[] dbname="messages.db") { import std.file : remove; remove(dbname); } catch (Exception) {} - auto db = Database(dbname, schema); + auto db = Database(dbname, Database.Mode.ReadWriteCreate, dbpragmas, schema); registerFunctions(db); - db.setOnClose(indicies); + db.setOnClose("PRAGMA optimize;"); return db; } //////////////////////////////////////////////////////////////////////////////// public Database chiroOpenDB (const(char)[] dbname="messages.db") { - auto db = Database(dbname, ""); + auto db = Database(dbname, Database.Mode.ReadWrite, dbpragmas); registerFunctions(db); - db.execute(dbpragmas); db.setOnClose("PRAGMA optimize;"); return db; } + + +//////////////////////////////////////////////////////////////////////////////// +public Database chiroOpenRODB (const(char)[] dbname="messages.db") { + auto db = Database(dbname, Database.Mode.ReadOnly, dbpragmasRO); + registerFunctions(db); + return db; +} -- 2.11.4.GIT