# HG changeset patch
# User Kim Alvefur <zash@zash.se>
# Date 1686495851 -7200
# Node ID 0aaf67f700150174d10c2335d3cb15ed196078ef
# Parent  c6f46f33cecf4c361be7d73a7679c5b6c4bfd0a0
mod_storage_sql: Add setting to tune SQLite3 performance vs safety

Notably the default journal_mode of DELETE is somewhat slow, some users
might want to catch up to the amazing performance of internal storage.

diff -r c6f46f33cecf -r 0aaf67f70015 plugins/mod_storage_sql.lua
--- a/plugins/mod_storage_sql.lua	Sat Jun 10 14:01:56 2023 +0200
+++ b/plugins/mod_storage_sql.lua	Sun Jun 11 17:04:11 2023 +0200
@@ -910,6 +910,44 @@
 					compile_options[opt or option] = tonumber(val) or val or true;
 				end
 				engine.sqlite_compile_options = compile_options;
+
+				local journal_mode = "delete";
+				for row in engine:select[[PRAGMA journal_mode;]] do
+					journal_mode = row[1];
+				end
+
+				-- Note: These things can't be changed with in a transaction. LuaDBI
+				-- opens a transaction automatically for every statement(?), so this
+				-- will not work there.
+				local tune = module:get_option_string("sqlite_tune", "default");
+				if tune == "normal" then
+					if journal_mode ~= "wal" then
+						engine:execute("PRAGMA journal_mode=WAL;");
+					end
+					engine:execute("PRAGMA auto_vacuum=FULL;");
+					engine:execute("PRAGMA synchronous=NORMAL;")
+				elseif tune == "fast" then
+					if journal_mode ~= "wal" then
+						engine:execute("PRAGMA journal_mode=WAL;");
+					end
+					if compile_options.secure_delete then
+						engine:execute("PRAGMA secure_delete=FAST;");
+					end
+					engine:execute("PRAGMA synchronous=OFF;")
+					engine:execute("PRAGMA fullfsync=0;")
+				elseif tune == "safe" then
+					if journal_mode ~= "delete" then
+						engine:execute("PRAGMA journal_mode=DELETE;");
+					end
+					engine:execute("PRAGMA synchronous=EXTRA;")
+					engine:execute("PRAGMA fullfsync=1;")
+				end
+
+				for row in engine:select[[PRAGMA journal_mode;]] do
+					journal_mode = row[1];
+				end
+
+				module:log("debug", "SQLite3 database %q operating with journal_mode=%s", engine.params.database, journal_mode);
 			end
 			module:set_status("info", "Connected to " .. engine.params.driver);
 		end, function (engine) -- luacheck: ignore 431/engine