Software `/` code `/` prosody

Annotate

util/sql.lua @ 7567:495de404a8ae

ejabberdsql2prosody: rename variable 'host' to prevent shadowing upvalue [luacheck] Functions roster(), roster_pending(), roster_group(), private_storage() and offline_msg() have argument named "host", which used to shadow upvalue of this variable before this change. Instead of renaming this argument, let's rename the variable to match what the script says in usage: Usage: ejabberdsql2prosody.lua filename.txt hostname

author	Anton Shestakov <av6@dwimlabs.net>
date	Fri, 12 Aug 2016 13:44:47 +0800
parent	7513:8a6c7c4b15fb
child	8073:7361412a9664

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)
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method Kim Alvefur <zash@zash.se> parents: 7180 diff changeset	131 if self.params.driver == "PostgreSQL" then
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method Kim Alvefur <zash@zash.se> parents: 7180 diff changeset	132 sql = sql:gsub("`", "\"");
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
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	142 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	143 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	144 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	145 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	146 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	147 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	148 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	149
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 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	151 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	152 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	153 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	154
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 local result_mt = { __index = {
5744 253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount Kim Alvefur <zash@zash.se> parents: 5743 diff changeset	156 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	157 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	158 } };
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
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	160 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	161 local i = 0; local a = {...}
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 log("debug", "[%s] %s", where, sql:gsub("%?", function () i = i + 1; local v = a[i]; if type(v) == "string" then v = ("%q"):format(v); end return tostring(v); 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	163 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	164
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	165 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	166 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	167 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	168 assert(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	169 return stmt:rows();
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	170 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	171 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	172 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	173 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	174 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	175 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	176 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 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	178 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	179 assert(stmt:execute(...));
5744 253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount Kim Alvefur <zash@zash.se> parents: 5743 diff changeset	180 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	181 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	182 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	183 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	184 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	185 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	186 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	187 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	188 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	189 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	190 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	191 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	192 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	193 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	194 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	195 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	196 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	197 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	198 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	199 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	200 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	201 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	202 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	203 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	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 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	206 local function handleerr(err)
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	207 log("error", "Error in SQL transaction: %s", debug_traceback(err, 3));
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	208 return err;
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	209 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	210 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	211 if not self.conn then
6733 36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	212 local ok, err = self:connect();
36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	213 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	214 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	215 --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	216 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	217 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	218 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	219 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	220 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	221 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	222 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	223 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	224 local ok, err = self.conn:commit();
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 ok then return ok, err; end -- commit failed
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	226 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	227 else
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 log("debug", "SQL transaction failure [%s]: %s", tostring(func), a);
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 if self.conn then self.conn:rollback(); 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	230 return success, a;
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 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	232 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	233 function engine:transaction(...)
6733 36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	234 local ok, ret = self:_transaction(...);
36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	235 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	236 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	237 if not conn or not conn:ping() 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	238 self.conn = nil;
6733 36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	239 ok, ret = self:_transaction(...);
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	240 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	241 end
6733 36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	242 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	243 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	244 function engine:_create_index(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	245 local sql = "CREATE INDEX `"..index.name.."` ON `"..index.table.."` (";
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 for i=1,#index 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	247 sql = sql.."`"..index[i].."`";
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 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	249 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	250 sql = 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	251 if self.params.driver == "PostgreSQL" 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	252 sql = sql:gsub("`", "\"");
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	253 elseif self.params.driver == "MySQL" 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	254 sql = sql:gsub("`([,)])", "`(20)%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	255 end
5885 cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	256 if index.unique then
cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	257 sql = sql:gsub("^CREATE", "CREATE UNIQUE");
cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	258 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	259 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	260 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	261 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	262 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	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 function engine:_create_table(table)
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	265 local sql = "CREATE TABLE `"..table.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	266 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	267 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	268 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	269 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	270 end
5912 f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL Kim Alvefur <zash@zash.se> parents: 5910 diff changeset	271 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	272 col_type = "BIGSERIAL";
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL Kim Alvefur <zash@zash.se> parents: 5910 diff changeset	273 end
5890 544ca3d94596 util.sql: Rewrite MEDIUMTEXT to TEXT for drivers other than MySQL Kim Alvefur <zash@zash.se> parents: 5889 diff changeset	274 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	275 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	276 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	277 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	278 if self.params.driver == "MySQL" then
5887 1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	279 sql = sql.." AUTO_INCREMENT";
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	280 elseif self.params.driver == "SQLite3" then
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	281 sql = sql.." AUTOINCREMENT";
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	282 end
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	283 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	284 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	285 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	286 sql = 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	287 if self.params.driver == "PostgreSQL" 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	288 sql = sql:gsub("`", "\"");
5739 2e9ad9cb206f util.sql: Set charset and collation for MySQL when creating tables Kim Alvefur <zash@zash.se> parents: 5494 diff changeset	289 elseif 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	290 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	291 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	292 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	293 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	294 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	295 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	296 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	297 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	298 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	299 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	300 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	301 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	302 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	303 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	304 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	305 local driver = self.params.driver;
5889 ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	306 if driver == "SQLite3" then
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	307 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	308 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	309 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	310 self.charset = "utf8";
187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	311 end
5889 ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	312 end
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	313 end);
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	314 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	315 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	316 local charset = "utf8";
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	317 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	318 self:transaction(function()
187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	319 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
187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	320 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	321 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	322 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	323 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	324 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	325 self.charset = charset;
6763 e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	326 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	327 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	328 if not ok then
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	329 return ok, err;
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	330 end
6771 60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace Kim Alvefur <zash@zash.se> parents: 6766 diff changeset	331
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	332 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	333 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	334 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	335 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	336 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	337 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	338 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	339 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	340 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	341 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	342 end
7275 187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	343 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	344 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	345 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	346 end
6771 60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace Kim Alvefur <zash@zash.se> parents: 6766 diff changeset	347
6763 e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	348 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	349 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	350 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	351
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	352 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	353 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	354 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	355 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	356 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	357 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	358 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	359 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	360 };
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	361 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	362
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	363 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	364 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	365 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	366
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	367 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	368 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	369 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	370 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	371 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	372 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	373 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	374 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	375 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	376 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	377 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	378 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	379 };

prosody

495de404a8ae

util/sql.lua

Software / code / prosody

Annotate

util/sql.lua @ 7567:495de404a8ae

Software `/` code `/` prosody