Software `/` code `/` prosody

Annotate

util/sql.lua @ 11962:9a70a543c727

util.async: Add next-tick configuration Running woken runners in the next iteration of the event loop prevents unexpected recursion, unexpected tracebacks, and is generally more predictable. The pattern is borrowed from util.promise, where we're now doing the same.

author	Matthew Wild <mwild1@gmail.com>
date	Mon, 29 Nov 2021 14:14:30 +0000
parent	10534:8a42fd6702e6
child	12872:a20923f7d5fd

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;
9616 61376a3c0c1d util.sql: Switch from hacky multi-arg xpcall implementation to util.xpcall Kim Alvefur <zash@zash.se> parents: 8555 diff changeset	3 local ipairs = ipairs;
8382 e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	4 local tostring = tostring;
7431 0d991d5659f0 util.sql: Import type too (fix global access) Kim Alvefur <zash@zash.se> parents: 7428 diff changeset	5 local type = type;
9616 61376a3c0c1d util.sql: Switch from hacky multi-arg xpcall implementation to util.xpcall Kim Alvefur <zash@zash.se> parents: 8555 diff changeset	6 local assert, pcall, debug_traceback = assert, pcall, debug.traceback;
61376a3c0c1d util.sql: Switch from hacky multi-arg xpcall implementation to util.xpcall Kim Alvefur <zash@zash.se> parents: 8555 diff changeset	7 local xpcall = require "util.xpcall".xpcall;
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	8 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	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;
8555 4f0f5b49bb03 vairious: Add annotation when an empty environment is set [luacheck] Kim Alvefur <zash@zash.se> parents: 8389 diff changeset	18 -- luacheck: std none
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	19
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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	21 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	22 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	23 --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	24 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	25
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	26 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	27 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	28 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	29 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	30 local function Integer() return "Integer()" end
30dfaf36ea6d util.sql: Remove unused arguments [luacheck] Kim Alvefur <zash@zash.se> parents: 7275 diff changeset	31 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	32
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	33 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	34 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	35 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	36 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	37 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	38 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	39 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	40 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	41 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	42 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	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 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	45 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	46 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	47 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	48 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	49 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	50
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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	52 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	53 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	54 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	55 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	56 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	57 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	58 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	59 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	60 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	61 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	62 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	63 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	64 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	65 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	66 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	67 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	68 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	69 -- 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	70 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	71
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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	73 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	74 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	75
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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	77 assert(params.driver, "no driver")
6765 0cbb09afa5c3 util.sql: Fix log level of debug message Matthew Wild <mwild1@gmail.com> parents: 6764 diff changeset	78 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	79 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	80 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	81 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	82 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	83 );
7306 98c4c3a2b536 util.sql: Catch errors from LuaDBI connect (Fixes #568) Kim Alvefur <zash@zash.se> parents: 7276 diff changeset	84 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	85 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	86 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	87 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	88 self.prepared = {};
6762 ea43a5af31ca util.sql: Return failure if set_encoding() fails Matthew Wild <mwild1@gmail.com> parents: 6760 diff changeset	89 local ok, err = self:set_encoding();
ea43a5af31ca util.sql: Return failure if set_encoding() fails Matthew Wild <mwild1@gmail.com> parents: 6760 diff changeset	90 if not ok then
ea43a5af31ca util.sql: Return failure if set_encoding() fails Matthew Wild <mwild1@gmail.com> parents: 6760 diff changeset	91 return ok, err;
ea43a5af31ca util.sql: Return failure if set_encoding() fails Matthew Wild <mwild1@gmail.com> parents: 6760 diff changeset	92 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	93 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	94 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	95 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	96 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	97 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	98 end
8382 e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	99 function engine:onconnect() -- luacheck: ignore 212/self
6748 ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set Kim Alvefur <zash@zash.se> parents: 6735 diff changeset	100 -- Override from create_engine()
ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set Kim Alvefur <zash@zash.se> parents: 6735 diff changeset	101 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	102
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method Kim Alvefur <zash@zash.se> parents: 7180 diff changeset	103 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	104 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	105 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	106 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	107 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	108 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	109
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	110 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	111 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	112 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	113 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	114
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	115 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	116 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	117 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	118 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	119 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	120 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	121 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	122 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	123
8382 e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	124 -- luacheck: ignore 411/success
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	125 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	126 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	127 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	128 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	129
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	130 local result_mt = { __index = {
5744 253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount Kim Alvefur <zash@zash.se> parents: 5743 diff changeset	131 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	132 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	133 } };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	134
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	135 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	136 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	137 sql = sql:gsub("\n?\t+", " ");
8380 a597ff326758 util.sql: Discard useless substitution count from string.gsub in SQL debug logs Kim Alvefur <zash@zash.se> parents: 8378 diff changeset	138 log("debug", "[%s] %s", where, (sql:gsub("%?", function ()
8084 655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	139 i = i + 1;
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	140 local v = a[i];
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	141 if type(v) == "string" then
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	142 v = ("'%s'"):format(v:gsub("'", "''"));
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	143 end
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	144 return tostring(v);
8380 a597ff326758 util.sql: Discard useless substitution count from string.gsub in SQL debug logs Kim Alvefur <zash@zash.se> parents: 8378 diff changeset	145 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	146 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	147
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	148 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	149 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	150 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	151 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	152 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	153 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	154 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	155 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	156 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	157 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	158 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	159 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	160 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	161 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	162 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	163 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	164 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	165 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	166 assert(stmt:execute(...));
5744 253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount Kim Alvefur <zash@zash.se> parents: 5743 diff changeset	167 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	168 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	169 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	170 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	171 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	172 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	173 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	174 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	175 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	176 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	177 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	178 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	179 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	180 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	181 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	182 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	183 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	184 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	185 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	186 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	187 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	188 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	189 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	190 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	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 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	193 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	194 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	195 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	196 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	197 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	198 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	199 if not self.conn then
6733 36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	200 local ok, err = self:connect();
36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	201 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	202 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	203 --assert(not self.__transaction, "Recursive transactions not allowed");
10109 c59d384b0959 util.sql: Remove tostring call from logging Kim Alvefur <zash@zash.se> parents: 10038 diff changeset	204 log("debug", "SQL transaction begin [%s]", 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	205 self.__transaction = true;
9616 61376a3c0c1d util.sql: Switch from hacky multi-arg xpcall implementation to util.xpcall Kim Alvefur <zash@zash.se> parents: 8555 diff changeset	206 local success, a, b, c = xpcall(func, 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	207 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	208 if success then
10109 c59d384b0959 util.sql: Remove tostring call from logging Kim Alvefur <zash@zash.se> parents: 10038 diff changeset	209 log("debug", "SQL transaction success [%s]", 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	210 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	211 -- 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	212 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	213 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	214 else
10109 c59d384b0959 util.sql: Remove tostring call from logging Kim Alvefur <zash@zash.se> parents: 10038 diff changeset	215 log("debug", "SQL transaction failure [%s]: %s", 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	216 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	217 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	218 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	219 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	220 function engine:transaction(...)
10275 a247fa8df7df util.sql: Preserve 3rd and 4th return values from transaction (fixes #1434) (thanks mrdoctorwho) Kim Alvefur <zash@zash.se> parents: 10109 diff changeset	221 local ok, ret, b, c = self:_transaction(...);
6733 36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	222 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	223 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	224 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	225 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	226 self.conn = nil;
10109 c59d384b0959 util.sql: Remove tostring call from logging Kim Alvefur <zash@zash.se> parents: 10038 diff changeset	227 log("debug", "Retrying SQL transaction [%s]", (...));
10275 a247fa8df7df util.sql: Preserve 3rd and 4th return values from transaction (fixes #1434) (thanks mrdoctorwho) Kim Alvefur <zash@zash.se> parents: 10109 diff changeset	228 ok, ret, b, c = 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	229 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	230 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	231 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	232 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	233 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	234 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	235 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	236 end
10275 a247fa8df7df util.sql: Preserve 3rd and 4th return values from transaction (fixes #1434) (thanks mrdoctorwho) Kim Alvefur <zash@zash.se> parents: 10109 diff changeset	237 return ok, ret, b, c;
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	238 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	239 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	240 local sql = "CREATE INDEX \""..index.name.."\" ON \""..index.table.."\" (";
10038 7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064) Kim Alvefur <zash@zash.se> parents: 9616 diff changeset	241 if self.params.driver ~= "MySQL" then
7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064) Kim Alvefur <zash@zash.se> parents: 9616 diff changeset	242 sql = sql:gsub("^CREATE INDEX", "%1 IF NOT EXISTS");
7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064) Kim Alvefur <zash@zash.se> parents: 9616 diff changeset	243 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	244 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	245 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	246 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	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 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	249 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	250 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	251 end
5885 cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	252 if index.unique then
cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	253 sql = sql:gsub("^CREATE", "CREATE UNIQUE");
cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	254 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	255 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	256 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	257 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	258 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	259 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	260 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	261 local sql = "CREATE TABLE \""..table.name.."\" (";
10038 7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064) Kim Alvefur <zash@zash.se> parents: 9616 diff changeset	262 do
7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064) Kim Alvefur <zash@zash.se> parents: 9616 diff changeset	263 sql = sql:gsub("^CREATE TABLE", "%1 IF NOT EXISTS");
7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064) Kim Alvefur <zash@zash.se> parents: 9616 diff changeset	264 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	265 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	266 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	267 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	268 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	269 end
5912 f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL Kim Alvefur <zash@zash.se> parents: 5910 diff changeset	270 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	271 col_type = "BIGSERIAL";
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL Kim Alvefur <zash@zash.se> parents: 5910 diff changeset	272 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	273 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	274 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	275 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	276 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	277 if self.params.driver == "MySQL" then
5887 1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	278 sql = sql.." AUTO_INCREMENT";
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	279 elseif self.params.driver == "SQLite3" then
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	280 sql = sql.." AUTOINCREMENT";
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	281 end
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 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 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	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 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	286 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	287 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	288 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	289 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	290 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	291 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	292 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	293 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	294 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	295 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	296 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	297 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	298 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	299 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	300 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	301 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	302 local driver = self.params.driver;
5889 ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	303 if driver == "SQLite3" then
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	304 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	305 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	306 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	307 self.charset = "utf8";
187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	308 end
5889 ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	309 end
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	310 end);
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	311 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	312 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	313 local charset = "utf8";
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	314 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	315 self:transaction(function()
8382 e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	316 for row in self:select[[
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	317 SELECT "CHARACTER_SET_NAME"
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	318 FROM "information_schema"."CHARACTER_SETS"
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	319 WHERE "CHARACTER_SET_NAME" LIKE 'utf8%'
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	320 ORDER BY MAXLEN DESC LIMIT 1;
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	321 ]] do
7275 187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	322 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	323 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	324 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	325 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	326 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	327 self.charset = charset;
6763 e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	328 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	329 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	330 if not ok then
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	331 return ok, err;
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	332 end
6771 60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace Kim Alvefur <zash@zash.se> parents: 6766 diff changeset	333
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	334 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	335 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	336 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	337 end);
10534 8a42fd6702e6 util.sql: Handle failure to detect connection encoding Kim Alvefur <zash@zash.se> parents: 10275 diff changeset	338 if not ok then
8a42fd6702e6 util.sql: Handle failure to detect connection encoding Kim Alvefur <zash@zash.se> parents: 10275 diff changeset	339 return false, "Failed to detect connection encoding";
8a42fd6702e6 util.sql: Handle failure to detect connection encoding Kim Alvefur <zash@zash.se> parents: 10275 diff changeset	340 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	341 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	342 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	343 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	344 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	345 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	346 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	347 end
7275 187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	348 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	349 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	350 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	351 end
6771 60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace Kim Alvefur <zash@zash.se> parents: 6766 diff changeset	352
6763 e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	353 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	354 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	355 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	356
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	357 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	358 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	359 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	360 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	361 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	362 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	363 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	364 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	365 };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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	367
8382 e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	368 local function create_engine(_, 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	369 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	370 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	371
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	372 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	373 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	374 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	375 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	376 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	377 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	378 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	379 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	380 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	381 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	382 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	383 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	384 };

prosody

9a70a543c727

util/sql.lua

Software / code / prosody

Annotate

util/sql.lua @ 11962:9a70a543c727

Software `/` code `/` prosody