Software `/` code `/` prosody

Annotate

util/sql.lua @ 8791:8da11142fabf

muc: Allow clients to change multiple affiliations or roles at once (#345) According to XEP-0045 sections 9.2, 9.5 and 9.8 affiliation lists and role lists should allow mass-modification. Prosody however would just use the first entry of the list and ignore the rest. This is fixed by introducing a `for` loop to `set` stanzas of the respective `muc#admin` namespace. In order for this loop to work, the error handling was changed a little. Prosody no longer returns after the first error. Instead, an error reply is sent for each malformed or otherwise wrong entry, but the loop keeps going over the other entries. This may lead to multiple error messages being sent for one client request. A notable exception from this is when the XML Schema for `muc#admin` requests is violated. In that case the loop is aborted with an error message to the client. The change is a bit bigger than that in order to have the loop only for `set` stanzas without changing the behaviour of the `get` stanzas. This is now more in line with trunk, where there are separate methods for each stanza type. References: #345

author	Lennart Sauerbeck <devel@lennart.sauerbeck.org>
date	Sat, 18 Mar 2017 18:47:28 +0100
parent	8378:6a098961bc00
child	8380:a597ff326758

rev	line source
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	1
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	2 local setmetatable, getmetatable = setmetatable, getmetatable;
7180 0d7f80263813 util.sql: Localize unpack() in Lua 5.2 compatible way Kim Alvefur <zash@zash.se> parents: 7174 diff changeset	3 local ipairs, unpack, select = ipairs, table.unpack or unpack, select; --luacheck: ignore 113
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	4 local tonumber, tostring = tonumber, tostring;
7431 0d991d5659f0 util.sql: Import type too (fix global access) Kim Alvefur <zash@zash.se> parents: 7428 diff changeset	5 local type = type;
7428 f791651ee334 util.sql: Import pcall (fixes #677) Matthew Wild <mwild1@gmail.com> parents: 7317 diff changeset	6 local assert, pcall, xpcall, debug_traceback = assert, pcall, xpcall, debug.traceback;
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	7 local t_concat = table.concat;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	8 local s_char = string.char;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	9 local log = require "util.logger".init("sql");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	10
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	11 local DBI = require "DBI";
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	12 -- This loads all available drivers while globals are unlocked
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	13 -- LuaDBI should be fixed to not set globals.
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	14 DBI.Drivers();
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	15 local build_url = require "socket.url".build;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	16
6777 5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	17 local _ENV = nil;
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	18
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	19 local column_mt = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	20 local table_mt = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	21 local query_mt = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	22 --local op_mt = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	23 local index_mt = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	24
6777 5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	25 local function is_column(x) return getmetatable(x)==column_mt; end
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	26 local function is_index(x) return getmetatable(x)==index_mt; end
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	27 local function is_table(x) return getmetatable(x)==table_mt; end
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	28 local function is_query(x) return getmetatable(x)==query_mt; end
7276 30dfaf36ea6d util.sql: Remove unused arguments [luacheck] Kim Alvefur <zash@zash.se> parents: 7275 diff changeset	29 local function Integer() return "Integer()" end
30dfaf36ea6d util.sql: Remove unused arguments [luacheck] Kim Alvefur <zash@zash.se> parents: 7275 diff changeset	30 local function String() return "String()" end
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	31
6777 5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	32 local function Column(definition)
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	33 return setmetatable(definition, column_mt);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	34 end
6777 5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	35 local function Table(definition)
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	36 local c = {}
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	37 for i,col in ipairs(definition) do
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	38 if is_column(col) then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	39 c[i], c[col.name] = col, col;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	40 elseif is_index(col) then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	41 col.table = definition.name;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	42 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	43 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	44 return setmetatable({ __table__ = definition, c = c, name = definition.name }, table_mt);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	45 end
6777 5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	46 local function Index(definition)
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	47 return setmetatable(definition, index_mt);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	48 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	49
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	50 function table_mt:__tostring()
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	51 local s = { 'name="'..self.__table__.name..'"' }
7513 8a6c7c4b15fb util.sql: remove unused one-letter loop variables [luacheck] Anton Shestakov <av6@dwimlabs.net> parents: 7431 diff changeset	52 for _, col in ipairs(self.__table__) do
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	53 s[#s+1] = tostring(col);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	54 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	55 return 'Table{ '..t_concat(s, ", ")..' }'
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	56 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	57 table_mt.__index = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	58 function table_mt.__index:create(engine)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	59 return engine:_create_table(self);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	60 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	61 function table_mt:__call(...)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	62 -- TODO
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	63 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	64 function column_mt:__tostring()
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	65 return 'Column{ name="'..self.name..'", type="'..self.type..'" }'
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	66 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	67 function index_mt:__tostring()
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	68 local s = 'Index{ name="'..self.name..'"';
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	69 for i=1,#self do s = s..', "'..self[i]:gsub("[\\\"]", "\\%1")..'"'; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	70 return s..' }';
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	71 -- return 'Index{ name="'..self.name..'", type="'..self.type..'" }'
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	72 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	73
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	74 local function urldecode(s) return s and (s:gsub("%%(%x%x)", function (c) return s_char(tonumber(c,16)); end)); end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	75 local function parse_url(url)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	76 local scheme, secondpart, database = url:match("^([%w%+]+)://([^/])/?(.)");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	77 assert(scheme, "Invalid URL format");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	78 local username, password, host, port;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	79 local authpart, hostpart = secondpart:match("([^@]+)@([^@+])");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	80 if not authpart then hostpart = secondpart; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	81 if authpart then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	82 username, password = authpart:match("([^:]):(.)");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	83 username = username or authpart;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	84 password = password and urldecode(password);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	85 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	86 if hostpart then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	87 host, port = hostpart:match("([^:]):(.)");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	88 host = host or hostpart;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	89 port = port and assert(tonumber(port), "Invalid URL format");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	90 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	91 return {
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	92 scheme = scheme:lower();
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	93 username = username; password = password;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	94 host = host; port = port;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	95 database = #database > 0 and database or nil;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	96 };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	97 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	98
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	99 local engine = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	100 function engine:connect()
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	101 if self.conn then return true; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	102
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	103 local params = self.params;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	104 assert(params.driver, "no driver")
6765 0cbb09afa5c3 util.sql: Fix log level of debug message Matthew Wild <mwild1@gmail.com> parents: 6764 diff changeset	105 log("debug", "Connecting to [%s] %s...", params.driver, params.database);
7306 98c4c3a2b536 util.sql: Catch errors from LuaDBI connect (Fixes #568) Kim Alvefur <zash@zash.se> parents: 7276 diff changeset	106 local ok, dbh, err = pcall(DBI.Connect,
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	107 params.driver, params.database,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	108 params.username, params.password,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	109 params.host, params.port
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	110 );
7306 98c4c3a2b536 util.sql: Catch errors from LuaDBI connect (Fixes #568) Kim Alvefur <zash@zash.se> parents: 7276 diff changeset	111 if not ok then return ok, dbh; end
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	112 if not dbh then return nil, err; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	113 dbh:autocommit(false); -- don't commit automatically
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	114 self.conn = dbh;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	115 self.prepared = {};
6762 ea43a5af31ca util.sql: Return failure if set_encoding() fails Matthew Wild <mwild1@gmail.com> parents: 6760 diff changeset	116 local ok, err = self:set_encoding();
ea43a5af31ca util.sql: Return failure if set_encoding() fails Matthew Wild <mwild1@gmail.com> parents: 6760 diff changeset	117 if not ok then
ea43a5af31ca util.sql: Return failure if set_encoding() fails Matthew Wild <mwild1@gmail.com> parents: 6760 diff changeset	118 return ok, err;
ea43a5af31ca util.sql: Return failure if set_encoding() fails Matthew Wild <mwild1@gmail.com> parents: 6760 diff changeset	119 end
6758 88b89facc3c9 util.sql: Allow onconnect callback to fail connection to the DB by returning false, err Matthew Wild <mwild1@gmail.com> parents: 6748 diff changeset	120 local ok, err = self:onconnect();
88b89facc3c9 util.sql: Allow onconnect callback to fail connection to the DB by returning false, err Matthew Wild <mwild1@gmail.com> parents: 6748 diff changeset	121 if ok == false then
88b89facc3c9 util.sql: Allow onconnect callback to fail connection to the DB by returning false, err Matthew Wild <mwild1@gmail.com> parents: 6748 diff changeset	122 return ok, err;
88b89facc3c9 util.sql: Allow onconnect callback to fail connection to the DB by returning false, err Matthew Wild <mwild1@gmail.com> parents: 6748 diff changeset	123 end
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	124 return true;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	125 end
6748 ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set Kim Alvefur <zash@zash.se> parents: 6735 diff changeset	126 function engine:onconnect()
ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set Kim Alvefur <zash@zash.se> parents: 6735 diff changeset	127 -- Override from create_engine()
ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set Kim Alvefur <zash@zash.se> parents: 6735 diff changeset	128 end
7272 a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method Kim Alvefur <zash@zash.se> parents: 7180 diff changeset	129
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method Kim Alvefur <zash@zash.se> parents: 7180 diff changeset	130 function engine:prepquery(sql)
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 7513 diff changeset	131 if self.params.driver == "MySQL" then
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 7513 diff changeset	132 sql = sql:gsub("\"", "`");
7272 a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method Kim Alvefur <zash@zash.se> parents: 7180 diff changeset	133 end
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method Kim Alvefur <zash@zash.se> parents: 7180 diff changeset	134 return sql;
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method Kim Alvefur <zash@zash.se> parents: 7180 diff changeset	135 end
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method Kim Alvefur <zash@zash.se> parents: 7180 diff changeset	136
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	137 function engine:execute(sql, ...)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	138 local success, err = self:connect();
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	139 if not success then return success, err; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	140 local prepared = self.prepared;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	141
8076 6e0defa19ab9 util.sql: Apply quote transform in engine:execute so it is applied to eg encoding checks in mod_storage_sql (thanks Martin) Kim Alvefur <zash@zash.se> parents: 8073 diff changeset	142 sql = self:prepquery(sql);
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	143 local stmt = prepared[sql];
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	144 if not stmt then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	145 local err;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	146 stmt, err = self.conn:prepare(sql);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	147 if not stmt then return stmt, err; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	148 prepared[sql] = stmt;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	149 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	150
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	151 local success, err = stmt:execute(...);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	152 if not success then return success, err; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	153 return stmt;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	154 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	155
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	156 local result_mt = { __index = {
5744 253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount Kim Alvefur <zash@zash.se> parents: 5743 diff changeset	157 affected = function(self) return self.__stmt:affected(); end;
253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount Kim Alvefur <zash@zash.se> parents: 5743 diff changeset	158 rowcount = function(self) return self.__stmt:rowcount(); end;
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	159 } };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	160
7174 d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	161 local function debugquery(where, sql, ...)
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	162 local i = 0; local a = {...}
8077 29b3957db212 util.sql: Strip indentation from queries for debug logging Kim Alvefur <zash@zash.se> parents: 8076 diff changeset	163 sql = sql:gsub("\n?\t+", " ");
8084 655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	164 log("debug", "[%s] %s", where, sql:gsub("%?", function ()
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	165 i = i + 1;
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	166 local v = a[i];
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	167 if type(v) == "string" then
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	168 v = ("'%s'"):format(v:gsub("'", "''"));
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	169 end
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	170 return tostring(v);
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	171 end));
7174 d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	172 end
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	173
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	174 function engine:execute_query(sql, ...)
7272 a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method Kim Alvefur <zash@zash.se> parents: 7180 diff changeset	175 sql = self:prepquery(sql);
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	176 local stmt = assert(self.conn:prepare(sql));
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	177 assert(stmt:execute(...));
8186 becb593ed86d util.sql: Greedily read all rows so we can close queries early (fixes #391) Matthew Wild <mwild1@gmail.com> parents: 8084 diff changeset	178 local result = {};
becb593ed86d util.sql: Greedily read all rows so we can close queries early (fixes #391) Matthew Wild <mwild1@gmail.com> parents: 8084 diff changeset	179 for row in stmt:rows() do result[#result + 1] = row; end
becb593ed86d util.sql: Greedily read all rows so we can close queries early (fixes #391) Matthew Wild <mwild1@gmail.com> parents: 8084 diff changeset	180 stmt:close();
becb593ed86d util.sql: Greedily read all rows so we can close queries early (fixes #391) Matthew Wild <mwild1@gmail.com> parents: 8084 diff changeset	181 local i = 0;
becb593ed86d util.sql: Greedily read all rows so we can close queries early (fixes #391) Matthew Wild <mwild1@gmail.com> parents: 8084 diff changeset	182 return function() i=i+1; return result[i]; end;
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	183 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	184 function engine:execute_update(sql, ...)
7272 a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method Kim Alvefur <zash@zash.se> parents: 7180 diff changeset	185 sql = self:prepquery(sql);
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	186 local prepared = self.prepared;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	187 local stmt = prepared[sql];
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	188 if not stmt then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	189 stmt = assert(self.conn:prepare(sql));
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	190 prepared[sql] = stmt;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	191 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	192 assert(stmt:execute(...));
5744 253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount Kim Alvefur <zash@zash.se> parents: 5743 diff changeset	193 return setmetatable({ __stmt = stmt }, result_mt);
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	194 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	195 engine.insert = engine.execute_update;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	196 engine.select = engine.execute_query;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	197 engine.delete = engine.execute_update;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	198 engine.update = engine.execute_update;
7174 d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	199 local function debugwrap(name, f)
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	200 return function (self, sql, ...)
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	201 debugquery(name, sql, ...)
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	202 return f(self, sql, ...)
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	203 end
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	204 end
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	205 function engine:debug(enable)
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	206 self._debug = enable;
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	207 if enable then
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	208 engine.insert = debugwrap("insert", engine.execute_update);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	209 engine.select = debugwrap("select", engine.execute_query);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	210 engine.delete = debugwrap("delete", engine.execute_update);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	211 engine.update = debugwrap("update", engine.execute_update);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	212 else
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	213 engine.insert = engine.execute_update;
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	214 engine.select = engine.execute_query;
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	215 engine.delete = engine.execute_update;
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	216 engine.update = engine.execute_update;
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	217 end
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	218 end
7317 a2dce746599b util.sql: Log errors in transaction to error level with traceback but return only error message (fixes #464) Kim Alvefur <zash@zash.se> parents: 7312 diff changeset	219 local function handleerr(err)
8288 e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	220 local trace = debug_traceback(err, 3);
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	221 log("debug", "Error in SQL transaction: %s", trace);
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	222 return { err = err, traceback = trace };
7317 a2dce746599b util.sql: Log errors in transaction to error level with traceback but return only error message (fixes #464) Kim Alvefur <zash@zash.se> parents: 7312 diff changeset	223 end
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	224 function engine:_transaction(func, ...)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	225 if not self.conn then
6733 36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	226 local ok, err = self:connect();
36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	227 if not ok then return ok, err; end
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	228 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	229 --assert(not self.__transaction, "Recursive transactions not allowed");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	230 local args, n_args = {...}, select("#", ...);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	231 local function f() return func(unpack(args, 1, n_args)); end
6805 c37633feaece util.sql: Log when transactions begin Kim Alvefur <zash@zash.se> parents: 6777 diff changeset	232 log("debug", "SQL transaction begin [%s]", tostring(func));
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	233 self.__transaction = true;
7317 a2dce746599b util.sql: Log errors in transaction to error level with traceback but return only error message (fixes #464) Kim Alvefur <zash@zash.se> parents: 7312 diff changeset	234 local success, a, b, c = xpcall(f, handleerr);
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	235 self.__transaction = nil;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	236 if success then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	237 log("debug", "SQL transaction success [%s]", tostring(func));
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	238 local ok, err = self.conn:commit();
8378 6a098961bc00 util.sql: Return an error message when a SQL commit fails (LuaDBI doesn't) (Thanks Ge0rG) Kim Alvefur <zash@zash.se> parents: 8288 diff changeset	239 -- LuaDBI doesn't actually return an error message here, just a boolean
6a098961bc00 util.sql: Return an error message when a SQL commit fails (LuaDBI doesn't) (Thanks Ge0rG) Kim Alvefur <zash@zash.se> parents: 8288 diff changeset	240 if not ok then return ok, err or "commit failed"; end
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	241 return success, a, b, c;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	242 else
8288 e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	243 log("debug", "SQL transaction failure [%s]: %s", tostring(func), a.err);
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	244 if self.conn then self.conn:rollback(); end
8288 e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	245 return success, a.err;
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	246 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	247 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	248 function engine:transaction(...)
6733 36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	249 local ok, ret = self:_transaction(...);
36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	250 if not ok then
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	251 local conn = self.conn;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	252 if not conn or not conn:ping() then
8288 e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	253 log("debug", "Database connection was closed. Will reconnect and retry.");
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	254 self.conn = nil;
8288 e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	255 log("debug", "Retrying SQL transaction [%s]", tostring((...)));
6733 36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	256 ok, ret = self:_transaction(...);
8288 e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	257 log("debug", "SQL transaction retry %s", ok and "succeeded" or "failed");
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	258 else
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	259 log("debug", "SQL connection is up, so not retrying");
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	260 end
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	261 if not ok then
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok Matthew Wild <mwild1@gmail.com> parents: 8186 diff changeset	262 log("error", "Error in SQL transaction: %s", ret);
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	263 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	264 end
6733 36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	265 return ok, ret;
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	266 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	267 function engine:_create_index(index)
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 7513 diff changeset	268 local sql = "CREATE INDEX \""..index.name.."\" ON \""..index.table.."\" (";
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	269 for i=1,#index do
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 7513 diff changeset	270 sql = sql.."\""..index[i].."\"";
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	271 if i ~= #index then sql = sql..", "; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	272 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	273 sql = sql..");"
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 7513 diff changeset	274 if self.params.driver == "MySQL" then
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 7513 diff changeset	275 sql = sql:gsub("\"([,)])", "\"(20)%1");
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	276 end
5885 cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	277 if index.unique then
cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	278 sql = sql:gsub("^CREATE", "CREATE UNIQUE");
cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	279 end
7174 d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	280 if self._debug then
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	281 debugquery("create", sql);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	282 end
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	283 return self:execute(sql);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	284 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	285 function engine:_create_table(table)
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 7513 diff changeset	286 local sql = "CREATE TABLE \""..table.name.."\" (";
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	287 for i,col in ipairs(table.c) do
5890 544ca3d94596 util.sql: Rewrite MEDIUMTEXT to TEXT for drivers other than MySQL Kim Alvefur <zash@zash.se> parents: 5889 diff changeset	288 local col_type = col.type;
544ca3d94596 util.sql: Rewrite MEDIUMTEXT to TEXT for drivers other than MySQL Kim Alvefur <zash@zash.se> parents: 5889 diff changeset	289 if col_type == "MEDIUMTEXT" and self.params.driver ~= "MySQL" then
544ca3d94596 util.sql: Rewrite MEDIUMTEXT to TEXT for drivers other than MySQL Kim Alvefur <zash@zash.se> parents: 5889 diff changeset	290 col_type = "TEXT"; -- MEDIUMTEXT is MySQL-specific
544ca3d94596 util.sql: Rewrite MEDIUMTEXT to TEXT for drivers other than MySQL Kim Alvefur <zash@zash.se> parents: 5889 diff changeset	291 end
5912 f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL Kim Alvefur <zash@zash.se> parents: 5910 diff changeset	292 if col.auto_increment == true and self.params.driver == "PostgreSQL" then
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL Kim Alvefur <zash@zash.se> parents: 5910 diff changeset	293 col_type = "BIGSERIAL";
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL Kim Alvefur <zash@zash.se> parents: 5910 diff changeset	294 end
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 7513 diff changeset	295 sql = sql.."\""..col.name.."\" "..col_type;
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	296 if col.nullable == false then sql = sql.." NOT NULL"; end
5886 1237f9cc3123 util.sql: Allow columns to be marked the primary key Kim Alvefur <zash@zash.se> parents: 5885 diff changeset	297 if col.primary_key == true then sql = sql.." PRIMARY KEY"; end
5887 1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	298 if col.auto_increment == true then
5912 f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL Kim Alvefur <zash@zash.se> parents: 5910 diff changeset	299 if self.params.driver == "MySQL" then
5887 1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	300 sql = sql.." AUTO_INCREMENT";
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	301 elseif self.params.driver == "SQLite3" then
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	302 sql = sql.." AUTOINCREMENT";
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	303 end
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	304 end
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	305 if i ~= #table.c then sql = sql..", "; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	306 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	307 sql = sql.. ");"
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 7513 diff changeset	308 if self.params.driver == "MySQL" then
6759 fb952032f83e util.sql: Create table with same charset as the charset we selected for our connection, also use corresponding _bin collation Matthew Wild <mwild1@gmail.com> parents: 6758 diff changeset	309 sql = sql:gsub(";$", (" CHARACTER SET '%s' COLLATE '%s_bin';"):format(self.charset, self.charset));
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	310 end
7174 d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	311 if self._debug then
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	312 debugquery("create", sql);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	313 end
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	314 local success,err = self:execute(sql);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	315 if not success then return success,err; end
7513 8a6c7c4b15fb util.sql: remove unused one-letter loop variables [luacheck] Anton Shestakov <av6@dwimlabs.net> parents: 7431 diff changeset	316 for _, v in ipairs(table.__table__) do
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	317 if is_index(v) then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	318 self:_create_index(v);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	319 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	320 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	321 return success;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	322 end
5883 39b187e7e892 mod_storage_sql2, util.sql: Move code for setting encoding to util.sql Kim Alvefur <zash@zash.se> parents: 5776 diff changeset	323 function engine:set_encoding() -- to UTF-8
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	324 local driver = self.params.driver;
5889 ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	325 if driver == "SQLite3" then
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	326 return self:transaction(function()
7275 187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	327 for encoding in self:select"PRAGMA encoding;" do
187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	328 if encoding[1] == "UTF-8" then
187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	329 self.charset = "utf8";
187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	330 end
5889 ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	331 end
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	332 end);
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	333 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	334 local set_names_query = "SET NAMES '%s';"
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	335 local charset = "utf8";
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	336 if driver == "MySQL" then
7275 187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	337 self:transaction(function()
8073 7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885) Kim Alvefur <zash@zash.se> parents: 7513 diff changeset	338 for row in self:select"SELECT \"CHARACTER_SET_NAME\" FROM \"information_schema\".\"CHARACTER_SETS\" WHERE \"CHARACTER_SET_NAME\" LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1;" do
7275 187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	339 charset = row and row[1] or charset;
187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	340 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	341 end);
6760 e45a58c72609 util.sql: Use appropriate collation for the chosen character set - fixes MySQL silently ignoring our SET NAMES command when we use utf8mb4 Matthew Wild <mwild1@gmail.com> parents: 6759 diff changeset	342 set_names_query = set_names_query:gsub(";$", (" COLLATE '%s';"):format(charset.."_bin"));
5883 39b187e7e892 mod_storage_sql2, util.sql: Move code for setting encoding to util.sql Kim Alvefur <zash@zash.se> parents: 5776 diff changeset	343 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	344 self.charset = charset;
6763 e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	345 log("debug", "Using encoding '%s' for database connection", charset);
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	346 local ok, err = self:transaction(function() return self:execute(set_names_query:format(charset)); end);
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	347 if not ok then
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	348 return ok, err;
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	349 end
6771 60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace Kim Alvefur <zash@zash.se> parents: 6766 diff changeset	350
6766 b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL) Matthew Wild <mwild1@gmail.com> parents: 6765 diff changeset	351 if driver == "MySQL" then
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL) Matthew Wild <mwild1@gmail.com> parents: 6765 diff changeset	352 local ok, actual_charset = self:transaction(function ()
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL) Matthew Wild <mwild1@gmail.com> parents: 6765 diff changeset	353 return self:select"SHOW SESSION VARIABLES LIKE 'character_set_client'";
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL) Matthew Wild <mwild1@gmail.com> parents: 6765 diff changeset	354 end);
7312 b4e99602ae75 util.sql: Charset should be innocent until proven guilty (initialize charset_ok to true), fixes bug introduced in 187ba2e9c012 Matthew Wild <mwild1@gmail.com> parents: 7306 diff changeset	355 local charset_ok = true;
6766 b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL) Matthew Wild <mwild1@gmail.com> parents: 6765 diff changeset	356 for row in actual_charset do
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL) Matthew Wild <mwild1@gmail.com> parents: 6765 diff changeset	357 if row[2] ~= charset then
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL) Matthew Wild <mwild1@gmail.com> parents: 6765 diff changeset	358 log("error", "MySQL %s is actually %q (expected %q)", row[1], row[2], charset);
7275 187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	359 charset_ok = false;
6766 b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL) Matthew Wild <mwild1@gmail.com> parents: 6765 diff changeset	360 end
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL) Matthew Wild <mwild1@gmail.com> parents: 6765 diff changeset	361 end
7275 187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	362 if not charset_ok then
187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	363 return false, "Failed to set connection encoding";
187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	364 end
6766 b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL) Matthew Wild <mwild1@gmail.com> parents: 6765 diff changeset	365 end
6771 60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace Kim Alvefur <zash@zash.se> parents: 6766 diff changeset	366
6763 e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	367 return true;
5883 39b187e7e892 mod_storage_sql2, util.sql: Move code for setting encoding to util.sql Kim Alvefur <zash@zash.se> parents: 5776 diff changeset	368 end
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	369 local engine_mt = { __index = engine };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	370
6777 5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	371 local function db2uri(params)
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	372 return build_url{
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	373 scheme = params.driver,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	374 user = params.username,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	375 password = params.password,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	376 host = params.host,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	377 port = params.port,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	378 path = params.database,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	379 };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	380 end
6735 b553a30620b2 util.sql: Remove built-in engine caching. This is the wrong layer to do this, and unintentionally sharing connections could cause problems (e.g. when interleaving multiple queries and result fetching) Matthew Wild <mwild1@gmail.com> parents: 6734 diff changeset	381
6777 5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	382 local function create_engine(self, params, onconnect)
6735 b553a30620b2 util.sql: Remove built-in engine caching. This is the wrong layer to do this, and unintentionally sharing connections could cause problems (e.g. when interleaving multiple queries and result fetching) Matthew Wild <mwild1@gmail.com> parents: 6734 diff changeset	383 return setmetatable({ url = db2uri(params), params = params, onconnect = onconnect }, engine_mt);
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	384 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	385
6777 5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	386 return {
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	387 is_column = is_column;
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	388 is_index = is_index;
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	389 is_table = is_table;
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	390 is_query = is_query;
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	391 Integer = Integer;
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	392 String = String;
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	393 Column = Column;
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	394 Table = Table;
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	395 Index = Index;
5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	396 create_engine = create_engine;
6806 f824057189ed util.sql: Export db2uri (mod_storage_sql2 command uses it) (thanks mike) Kim Alvefur <zash@zash.se> parents: 6805 diff changeset	397 db2uri = db2uri;
6777 5de6b93d0190 util.: Remove use of module() function, make all module functions local and return them in a table at the end Kim Alvefur <zash@zash.se>* parents: 6771 diff changeset	398 };

prosody

8da11142fabf

util/sql.lua

Software / code / prosody

Annotate

util/sql.lua @ 8791:8da11142fabf

Software `/` code `/` prosody