Software `/` code `/` prosody

Annotate

util/sqlite3.lua @ 13262:9a86e7cbdd79

mod_storage_internal: Fix fast trimming of archive with exactly one item This method would previously never delete the first (and only) item since it works out which item should become the first item after the trim operation, which doesn't make sense when all should be removed. This also works as an optimization for when all the last item should be trimmed, thus items should be removed.

author	Kim Alvefur <zash@zash.se>
date	Sun, 24 Sep 2023 13:41:54 +0200
parent	13239:f2578a69ccf4

rev	line source
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	1
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	2 local setmetatable, getmetatable = setmetatable, getmetatable;
13239 f2578a69ccf4 util.sqlite3: Clean up unused variables Kim Alvefur <zash@zash.se> parents: 13147 diff changeset	3 local ipairs, select = ipairs, select;
f2578a69ccf4 util.sqlite3: Clean up unused variables Kim Alvefur <zash@zash.se> parents: 13147 diff changeset	4 local tostring = tostring;
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	5 local assert, xpcall, debug_traceback = assert, xpcall, debug.traceback;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	6 local error = error
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	7 local type = type
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	8 local t_concat = table.concat;
13146 771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	9 local array = require "prosody.util.array";
12975 d10957394a3c util: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12872 diff changeset	10 local log = require "prosody.util.logger".init("sql");
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	11
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	12 local lsqlite3 = require "lsqlite3";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	13 local build_url = require "socket.url".build;
12847 d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	14
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	15 -- from sqlite3.h, no copyright claimed
12975 d10957394a3c util: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12872 diff changeset	16 local sqlite_errors = require"prosody.util.error".init("util.sqlite3", {
12847 d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	17 -- FIXME xmpp error conditions?
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	18 [1] = { code = 1; type = "modify"; condition = "ERROR"; text = "Generic error" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	19 [2] = { code = 2; type = "cancel"; condition = "INTERNAL"; text = "Internal logic error in SQLite" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	20 [3] = { code = 3; type = "auth"; condition = "PERM"; text = "Access permission denied" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	21 [4] = { code = 4; type = "cancel"; condition = "ABORT"; text = "Callback routine requested an abort" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	22 [5] = { code = 5; type = "wait"; condition = "BUSY"; text = "The database file is locked" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	23 [6] = { code = 6; type = "wait"; condition = "LOCKED"; text = "A table in the database is locked" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	24 [7] = { code = 7; type = "wait"; condition = "NOMEM"; text = "A malloc() failed" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	25 [8] = { code = 8; type = "cancel"; condition = "READONLY"; text = "Attempt to write a readonly database" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	26 [9] = { code = 9; type = "cancel"; condition = "INTERRUPT"; text = "Operation terminated by sqlite3_interrupt()" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	27 [10] = { code = 10; type = "wait"; condition = "IOERR"; text = "Some kind of disk I/O error occurred" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	28 [11] = { code = 11; type = "cancel"; condition = "CORRUPT"; text = "The database disk image is malformed" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	29 [12] = { code = 12; type = "modify"; condition = "NOTFOUND"; text = "Unknown opcode in sqlite3_file_control()" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	30 [13] = { code = 13; type = "wait"; condition = "FULL"; text = "Insertion failed because database is full" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	31 [14] = { code = 14; type = "auth"; condition = "CANTOPEN"; text = "Unable to open the database file" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	32 [15] = { code = 15; type = "cancel"; condition = "PROTOCOL"; text = "Database lock protocol error" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	33 [16] = { code = 16; type = "continue"; condition = "EMPTY"; text = "Internal use only" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	34 [17] = { code = 17; type = "modify"; condition = "SCHEMA"; text = "The database schema changed" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	35 [18] = { code = 18; type = "modify"; condition = "TOOBIG"; text = "String or BLOB exceeds size limit" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	36 [19] = { code = 19; type = "modify"; condition = "CONSTRAINT"; text = "Abort due to constraint violation" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	37 [20] = { code = 20; type = "modify"; condition = "MISMATCH"; text = "Data type mismatch" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	38 [21] = { code = 21; type = "modify"; condition = "MISUSE"; text = "Library used incorrectly" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	39 [22] = { code = 22; type = "cancel"; condition = "NOLFS"; text = "Uses OS features not supported on host" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	40 [23] = { code = 23; type = "auth"; condition = "AUTH"; text = "Authorization denied" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	41 [24] = { code = 24; type = "modify"; condition = "FORMAT"; text = "Not used" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	42 [25] = { code = 25; type = "modify"; condition = "RANGE"; text = "2nd parameter to sqlite3_bind out of range" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	43 [26] = { code = 26; type = "cancel"; condition = "NOTADB"; text = "File opened that is not a database file" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	44 [27] = { code = 27; type = "continue"; condition = "NOTICE"; text = "Notifications from sqlite3_log()" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	45 [28] = { code = 28; type = "continue"; condition = "WARNING"; text = "Warnings from sqlite3_log()" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	46 [100] = { code = 100; type = "continue"; condition = "ROW"; text = "sqlite3_step() has another row ready" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	47 [101] = { code = 101; type = "continue"; condition = "DONE"; text = "sqlite3_step() has finished executing" };
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	48 });
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	49
13239 f2578a69ccf4 util.sqlite3: Clean up unused variables Kim Alvefur <zash@zash.se> parents: 13147 diff changeset	50 -- luacheck: ignore 411/assert
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	51 local assert = function(cond, errno, err)
12847 d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	52 return assert(sqlite_errors.coerce(cond, err or errno));
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	53 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	54 local _ENV = nil;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	55 -- luacheck: std none
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	56
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	57 local column_mt = {};
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	58 local table_mt = {};
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	59 local query_mt = {};
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	60 --local op_mt = {};
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	61 local index_mt = {};
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	62
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	63 local function is_column(x) return getmetatable(x)==column_mt; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	64 local function is_index(x) return getmetatable(x)==index_mt; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	65 local function is_table(x) return getmetatable(x)==table_mt; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	66 local function is_query(x) return getmetatable(x)==query_mt; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	67
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	68 local function Column(definition)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	69 return setmetatable(definition, column_mt);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	70 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	71 local function Table(definition)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	72 local c = {}
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	73 for i,col in ipairs(definition) do
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	74 if is_column(col) then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	75 c[i], c[col.name] = col, col;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	76 elseif is_index(col) then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	77 col.table = definition.name;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	78 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	79 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	80 return setmetatable({ __table__ = definition, c = c, name = definition.name }, table_mt);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	81 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	82 local function Index(definition)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	83 return setmetatable(definition, index_mt);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	84 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	85
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	86 function table_mt:__tostring()
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	87 local s = { 'name="'..self.__table__.name..'"' }
13239 f2578a69ccf4 util.sqlite3: Clean up unused variables Kim Alvefur <zash@zash.se> parents: 13147 diff changeset	88 for _, col in ipairs(self.__table__) do
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	89 s[#s+1] = tostring(col);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	90 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	91 return 'Table{ '..t_concat(s, ", ")..' }'
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	92 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	93 table_mt.__index = {};
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	94 function table_mt.__index:create(engine)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	95 return engine:_create_table(self);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	96 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	97 function column_mt:__tostring()
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	98 return 'Column{ name="'..self.name..'", type="'..self.type..'" }'
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	99 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	100 function index_mt:__tostring()
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	101 local s = 'Index{ name="'..self.name..'"';
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	102 for i=1,#self do s = s..', "'..self[i]:gsub("[\\\"]", "\\%1")..'"'; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	103 return s..' }';
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	104 -- return 'Index{ name="'..self.name..'", type="'..self.type..'" }'
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	105 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	106
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	107 local engine = {};
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	108 function engine:connect()
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	109 if self.conn then return true; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	110
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	111 local params = self.params;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	112 assert(params.driver == "SQLite3", "Only sqlite3 is supported");
12847 d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	113 local dbh, err = sqlite_errors.coerce(lsqlite3.open(params.database));
d6cdde74cd9b util.sqlite3: Create util.error registry from headers Kim Alvefur <zash@zash.se> parents: 12845 diff changeset	114 if not dbh then return nil, err; end
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	115 self.conn = dbh;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	116 self.prepared = {};
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	117 local ok, err = self:set_encoding();
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	118 if not ok then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	119 return ok, err;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	120 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	121 local ok, err = self:onconnect();
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	122 if ok == false then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	123 return ok, err;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	124 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	125 return true;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	126 end
13239 f2578a69ccf4 util.sqlite3: Clean up unused variables Kim Alvefur <zash@zash.se> parents: 13147 diff changeset	127 function engine:onconnect() -- luacheck: ignore 212/self
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	128 -- Override from create_engine()
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	129 end
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12848 diff changeset	130 function engine:ondisconnect() -- luacheck: ignore 212/self
a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12848 diff changeset	131 -- Override from create_engine()
a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12848 diff changeset	132 end
13147 e560f7c691ce util.sqlite3: Don't cache prepared statements for one-off queries Kim Alvefur <zash@zash.se> parents: 13146 diff changeset	133
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	134 function engine:execute(sql, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	135 local success, err = self:connect();
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	136 if not success then return success, err; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	137
12848 ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given Kim Alvefur <zash@zash.se> parents: 12847 diff changeset	138 if select('#', ...) == 0 then
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given Kim Alvefur <zash@zash.se> parents: 12847 diff changeset	139 local ret = self.conn:exec(sql);
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given Kim Alvefur <zash@zash.se> parents: 12847 diff changeset	140 if ret ~= lsqlite3.OK then
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given Kim Alvefur <zash@zash.se> parents: 12847 diff changeset	141 local err = sqlite_errors.new(err);
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given Kim Alvefur <zash@zash.se> parents: 12847 diff changeset	142 err.text = self.conn:errmsg();
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given Kim Alvefur <zash@zash.se> parents: 12847 diff changeset	143 return err;
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given Kim Alvefur <zash@zash.se> parents: 12847 diff changeset	144 end
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given Kim Alvefur <zash@zash.se> parents: 12847 diff changeset	145 return true;
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given Kim Alvefur <zash@zash.se> parents: 12847 diff changeset	146 end
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given Kim Alvefur <zash@zash.se> parents: 12847 diff changeset	147
13147 e560f7c691ce util.sqlite3: Don't cache prepared statements for one-off queries Kim Alvefur <zash@zash.se> parents: 13146 diff changeset	148 local stmt, err = self.conn:prepare(sql);
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	149 if not stmt then
13147 e560f7c691ce util.sqlite3: Don't cache prepared statements for one-off queries Kim Alvefur <zash@zash.se> parents: 13146 diff changeset	150 err = sqlite_errors.new(err);
e560f7c691ce util.sqlite3: Don't cache prepared statements for one-off queries Kim Alvefur <zash@zash.se> parents: 13146 diff changeset	151 err.text = self.conn:errmsg();
e560f7c691ce util.sqlite3: Don't cache prepared statements for one-off queries Kim Alvefur <zash@zash.se> parents: 13146 diff changeset	152 return stmt, err;
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	153 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	154
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	155 local ret = stmt:bind_values(...);
13147 e560f7c691ce util.sqlite3: Don't cache prepared statements for one-off queries Kim Alvefur <zash@zash.se> parents: 13146 diff changeset	156 if ret ~= lsqlite3.OK then
e560f7c691ce util.sqlite3: Don't cache prepared statements for one-off queries Kim Alvefur <zash@zash.se> parents: 13146 diff changeset	157 return nil, sqlite_errors.new(ret, { message = self.conn:errmsg() });
e560f7c691ce util.sqlite3: Don't cache prepared statements for one-off queries Kim Alvefur <zash@zash.se> parents: 13146 diff changeset	158 end
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	159 return stmt;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	160 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	161
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	162 local function iterator(table)
13145 af251471d5ae util.sqlite3: Fix indentation Kim Alvefur <zash@zash.se> parents: 12975 diff changeset	163 local i = 0;
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	164 return function()
13145 af251471d5ae util.sqlite3: Fix indentation Kim Alvefur <zash@zash.se> parents: 12975 diff changeset	165 i = i + 1;
af251471d5ae util.sqlite3: Fix indentation Kim Alvefur <zash@zash.se> parents: 12975 diff changeset	166 local item = table[i];
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	167 if item ~= nil then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	168 return item;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	169 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	170 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	171 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	172
13146 771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	173 local result_mt = {
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	174 __len = function(self)
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	175 return self.__rowcount;
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	176 end;
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	177 __index = {
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	178 affected = function(self)
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	179 return self.__affected;
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	180 end;
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	181 rowcount = function(self)
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	182 return self.__rowcount;
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	183 end;
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	184 };
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	185 __call = function(self)
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	186 return iterator(self.__data);
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	187 end;
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	188 };
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	189
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	190 local function debugquery(where, sql, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	191 local i = 0; local a = {...}
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	192 sql = sql:gsub("\n?\t+", " ");
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	193 log("debug", "[%s] %s", where, (sql:gsub("%?", function ()
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	194 i = i + 1;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	195 local v = a[i];
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	196 if type(v) == "string" then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	197 v = ("'%s'"):format(v:gsub("'", "''"));
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	198 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	199 return tostring(v);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	200 end)));
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	201 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	202
13146 771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	203 function engine:execute_update(sql, ...)
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	204 local prepared = self.prepared;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	205 local stmt = prepared[sql];
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	206 if stmt and stmt:isopen() then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	207 prepared[sql] = nil; -- Can't be used concurrently
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	208 else
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	209 stmt = assert(self.conn:prepare(sql));
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	210 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	211 local ret = stmt:bind_values(...);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	212 if ret ~= lsqlite3.OK then error(self.conn:errmsg()); end
13146 771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	213 local data = array();
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	214 for row in stmt:rows() do
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	215 data:push(array(row));
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	216 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	217 -- FIXME Error handling, BUSY, ERROR, MISUSE
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	218 if stmt:reset() == lsqlite3.OK then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	219 prepared[sql] = stmt;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	220 end
13146 771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	221 local affected = self.conn:changes();
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	222 return setmetatable({ __affected = affected; __rowcount = #data; __data = data }, result_mt);
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	223 end
13146 771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	224
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	225 function engine:execute_query(sql, ...)
771eb453e03a util.sqlite3: Deduplicate query methods Kim Alvefur <zash@zash.se> parents: 13145 diff changeset	226 return self:execute_update(sql, ...)()
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	227 end
13145 af251471d5ae util.sqlite3: Fix indentation Kim Alvefur <zash@zash.se> parents: 12975 diff changeset	228
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	229 engine.insert = engine.execute_update;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	230 engine.select = engine.execute_query;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	231 engine.delete = engine.execute_update;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	232 engine.update = engine.execute_update;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	233 local function debugwrap(name, f)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	234 return function (self, sql, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	235 debugquery(name, sql, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	236 return f(self, sql, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	237 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	238 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	239 function engine:debug(enable)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	240 self._debug = enable;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	241 if enable then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	242 engine.insert = debugwrap("insert", engine.execute_update);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	243 engine.select = debugwrap("select", engine.execute_query);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	244 engine.delete = debugwrap("delete", engine.execute_update);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	245 engine.update = debugwrap("update", engine.execute_update);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	246 else
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	247 engine.insert = engine.execute_update;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	248 engine.select = engine.execute_query;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	249 engine.delete = engine.execute_update;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	250 engine.update = engine.execute_update;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	251 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	252 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	253 function engine:_(word)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	254 local ret = self.conn:exec(word);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	255 if ret ~= lsqlite3.OK then return nil, self.conn:errmsg(); end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	256 return true;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	257 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	258 function engine:_transaction(func, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	259 if not self.conn then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	260 local a,b = self:connect();
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	261 if not a then return a,b; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	262 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	263 --assert(not self.__transaction, "Recursive transactions not allowed");
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	264 local ok, err = self:_"BEGIN";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	265 if not ok then return ok, err; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	266 self.__transaction = true;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	267 local success, a, b, c = xpcall(func, debug_traceback, ...);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	268 self.__transaction = nil;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	269 if success then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	270 log("debug", "SQL transaction success [%s]", tostring(func));
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	271 local ok, err = self:_"COMMIT";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	272 if not ok then return ok, err; end -- commit failed
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	273 return success, a, b, c;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	274 else
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	275 log("debug", "SQL transaction failure [%s]: %s", tostring(func), a);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	276 if self.conn then self:_"ROLLBACK"; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	277 return success, a;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	278 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	279 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	280 function engine:transaction(...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	281 local ok, ret = self:_transaction(...);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	282 if not ok then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	283 local conn = self.conn;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	284 if not conn or not conn:isopen() then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	285 self.conn = nil;
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12848 diff changeset	286 self:ondisconnect();
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	287 ok, ret = self:_transaction(...);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	288 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	289 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	290 return ok, ret;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	291 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	292 function engine:_create_index(index)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	293 local sql = "CREATE INDEX IF NOT EXISTS \""..index.name.."\" ON \""..index.table.."\" (";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	294 for i=1,#index do
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	295 sql = sql.."\""..index[i].."\"";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	296 if i ~= #index then sql = sql..", "; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	297 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	298 sql = sql..");"
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	299 if index.unique then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	300 sql = sql:gsub("^CREATE", "CREATE UNIQUE");
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	301 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	302 if self._debug then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	303 debugquery("create", sql);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	304 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	305 return self:execute(sql);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	306 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	307 function engine:_create_table(table)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	308 local sql = "CREATE TABLE IF NOT EXISTS \""..table.name.."\" (";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	309 for i,col in ipairs(table.c) do
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	310 local col_type = col.type;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	311 sql = sql.."\""..col.name.."\" "..col_type;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	312 if col.nullable == false then sql = sql.." NOT NULL"; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	313 if col.primary_key == true then sql = sql.." PRIMARY KEY"; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	314 if col.auto_increment == true then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	315 sql = sql.." AUTOINCREMENT";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	316 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	317 if i ~= #table.c then sql = sql..", "; end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	318 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	319 sql = sql.. ");"
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	320 if self._debug then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	321 debugquery("create", sql);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	322 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	323 local success,err = self:execute(sql);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	324 if not success then return success,err; end
13239 f2578a69ccf4 util.sqlite3: Clean up unused variables Kim Alvefur <zash@zash.se> parents: 13147 diff changeset	325 for _, v in ipairs(table.__table__) do
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	326 if is_index(v) then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	327 self:_create_index(v);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	328 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	329 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	330 return success;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	331 end
13145 af251471d5ae util.sqlite3: Fix indentation Kim Alvefur <zash@zash.se> parents: 12975 diff changeset	332
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	333 function engine:set_encoding() -- to UTF-8
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	334 return self:transaction(function()
13145 af251471d5ae util.sqlite3: Fix indentation Kim Alvefur <zash@zash.se> parents: 12975 diff changeset	335 for encoding in self:select "PRAGMA encoding;" do
af251471d5ae util.sqlite3: Fix indentation Kim Alvefur <zash@zash.se> parents: 12975 diff changeset	336 if encoding[1] == "UTF-8" then
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	337 self.charset = "utf8";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	338 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	339 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	340 end);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	341 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	342 local engine_mt = { __index = engine };
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	343
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	344 local function db2uri(params)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	345 return build_url{
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	346 scheme = params.driver,
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	347 user = params.username,
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	348 password = params.password,
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	349 host = params.host,
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	350 port = params.port,
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	351 path = params.database,
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	352 };
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	353 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	354
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12848 diff changeset	355 local function create_engine(_, params, onconnect, ondisconnect)
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	356 assert(params.driver == "SQLite3", "Only SQLite3 is supported without LuaDBI");
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 12848 diff changeset	357 return setmetatable({ url = db2uri(params); params = params; onconnect = onconnect; ondisconnect = ondisconnect }, engine_mt);
12845 f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	358 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	359
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	360 return {
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	361 is_column = is_column;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	362 is_index = is_index;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	363 is_table = is_table;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	364 is_query = is_query;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	365 Column = Column;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	366 Table = Table;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	367 Index = Index;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	368 create_engine = create_engine;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	369 db2uri = db2uri;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3 Kim Alvefur <zash@zash.se> parents: diff changeset	370 };

prosody

9a86e7cbdd79

util/sqlite3.lua

Software / code / prosody

Annotate

util/sqlite3.lua @ 13262:9a86e7cbdd79

Software `/` code `/` prosody