Software `/` code `/` prosody

Annotate

util/sql.lua @ 13758:fc97319ef48e 13.0

util.sasl: Preserve 'userdata' field between clones The :clean_clone() method is designed to provide a new cloned SASL handler, to be used when starting a fresh SASL negotiation on an existing connection. The userdata field is currently populated by mod_saslauth with the "read-only" information that the channel binding methods need to do their stuff. When :clean_clone() does not preserve this, it causes tracebacks in the cb profile handlers due to the property being nil. This does mean that SASL handlers should now not be reused (even when cloned) across different connections, if they ever could.

author	Matthew Wild <mwild1@gmail.com>
date	Thu, 06 Mar 2025 13:34:37 +0000
parent	13715:edd006093533

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;
12975 d10957394a3c util: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12872 diff changeset	7 local xpcall = require "prosody.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;
12975 d10957394a3c util: Prefix module imports with prosody namespace Kim Alvefur <zash@zash.se> parents: 12872 diff changeset	9 local log = require "prosody.util.logger".init("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	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
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	30
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	31 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	32 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	33 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	34 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	35 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	36 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	37 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	38 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	39 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	40 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	41 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	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 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	44 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	45 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	46 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	47 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	48
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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	50 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	51 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	52 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	53 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	54 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	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 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	57 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	58 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	59 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	60 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	61 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	62 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	63 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	64 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	65 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	66 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	67 -- 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	68 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	69
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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	71 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	72 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	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 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	75 assert(params.driver, "no driver")
6765 0cbb09afa5c3 util.sql: Fix log level of debug message Matthew Wild <mwild1@gmail.com> parents: 6764 diff changeset	76 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	77 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	78 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	79 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	80 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	81 );
7306 98c4c3a2b536 util.sql: Catch errors from LuaDBI connect (Fixes #568) Kim Alvefur <zash@zash.se> parents: 7276 diff changeset	82 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	83 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	84 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	85 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	86 self.prepared = {};
13715 edd006093533 util.sql: Don't send SQLCipher key to Postgres or MySQL (thanks gllmhyt) Kim Alvefur <zash@zash.se> parents: 13632 diff changeset	87 if params.driver == "SQLite3" and params.password then
13632 844e7bf7b48a util.sql: SQLCipher support Kim Alvefur <zash@zash.se> parents: 13240 diff changeset	88 local ok, err = self:execute(("PRAGMA key='%s'"):format(dbh:quote(params.password)));
844e7bf7b48a util.sql: SQLCipher support Kim Alvefur <zash@zash.se> parents: 13240 diff changeset	89 if not ok then
844e7bf7b48a util.sql: SQLCipher support Kim Alvefur <zash@zash.se> parents: 13240 diff changeset	90 return ok, err;
844e7bf7b48a util.sql: SQLCipher support Kim Alvefur <zash@zash.se> parents: 13240 diff changeset	91 end
844e7bf7b48a util.sql: SQLCipher support Kim Alvefur <zash@zash.se> parents: 13240 diff changeset	92 end
6762 ea43a5af31ca util.sql: Return failure if set_encoding() fails Matthew Wild <mwild1@gmail.com> parents: 6760 diff changeset	93 local ok, err = self:set_encoding();
ea43a5af31ca util.sql: Return failure if set_encoding() fails Matthew Wild <mwild1@gmail.com> parents: 6760 diff changeset	94 if not ok then
ea43a5af31ca util.sql: Return failure if set_encoding() fails Matthew Wild <mwild1@gmail.com> parents: 6760 diff changeset	95 return ok, err;
ea43a5af31ca util.sql: Return failure if set_encoding() fails Matthew Wild <mwild1@gmail.com> parents: 6760 diff changeset	96 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	97 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	98 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	99 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	100 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	101 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	102 end
8382 e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	103 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	104 -- Override from create_engine()
ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set Kim Alvefur <zash@zash.se> parents: 6735 diff changeset	105 end
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 10534 diff changeset	106 function engine:ondisconnect() -- luacheck: ignore 212/self
a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 10534 diff changeset	107 -- Override from create_engine()
a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 10534 diff changeset	108 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	109
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method Kim Alvefur <zash@zash.se> parents: 7180 diff changeset	110 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	111 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	112 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	113 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	114 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	115 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	116
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	117 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	118 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	119 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	120 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	121
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	122 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	123 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	124 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	125 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	126 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	127 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	128 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	129 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	130
8382 e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	131 -- 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	132 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	133 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	134 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	135 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	136
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 local result_mt = { __index = {
5744 253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount Kim Alvefur <zash@zash.se> parents: 5743 diff changeset	138 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	139 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	140 } };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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
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	142 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	143 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	144 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	145 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	146 i = i + 1;
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	147 local v = a[i];
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	148 if type(v) == "string" then
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	149 v = ("'%s'"):format(v:gsub("'", "''"));
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	150 end
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	151 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	152 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	153 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	154
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	155 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	156 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	157 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	158 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	159 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	160 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	161 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	162 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	163 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	164 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	165 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	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 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	168 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	169 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	170 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	171 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	172 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	173 assert(stmt:execute(...));
5744 253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount Kim Alvefur <zash@zash.se> parents: 5743 diff changeset	174 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	175 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	176 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	177 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	178 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	179 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	180 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	181 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	182 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	183 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	184 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	185 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	186 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	187 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	188 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	189 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	190 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	191 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	192 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	193 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	194 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	195 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	196 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	197 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	198 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	199 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	200 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	201 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	202 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	203 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	204 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	205 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	206 if not self.conn then
6733 36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	207 local ok, err = self:connect();
36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	208 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	209 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	210 --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	211 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	212 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	213 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	214 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	215 if success then
10109 c59d384b0959 util.sql: Remove tostring call from logging Kim Alvefur <zash@zash.se> parents: 10038 diff changeset	216 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	217 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	218 -- 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	219 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	220 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	221 else
10109 c59d384b0959 util.sql: Remove tostring call from logging Kim Alvefur <zash@zash.se> parents: 10038 diff changeset	222 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	223 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	224 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	225 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	226 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	227 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	228 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	229 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	230 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	231 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	232 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	233 self.conn = nil;
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 10534 diff changeset	234 self:ondisconnect();
10109 c59d384b0959 util.sql: Remove tostring call from logging Kim Alvefur <zash@zash.se> parents: 10038 diff changeset	235 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	236 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	237 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	238 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	239 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	240 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	241 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	242 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	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 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	245 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	246 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	247 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	248 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	249 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	250 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	251 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	252 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	253 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	254 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	255 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	256 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	257 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	258 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	259 end
5885 cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	260 if index.unique then
cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	261 sql = sql:gsub("^CREATE", "CREATE UNIQUE");
cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	262 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	263 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	264 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	265 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	266 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	267 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	268 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	269 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	270 do
7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064) Kim Alvefur <zash@zash.se> parents: 9616 diff changeset	271 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	272 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	273 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	274 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	275 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	276 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	277 end
5912 f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL Kim Alvefur <zash@zash.se> parents: 5910 diff changeset	278 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	279 col_type = "BIGSERIAL";
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL Kim Alvefur <zash@zash.se> parents: 5910 diff changeset	280 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	281 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	282 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	283 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	284 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	285 if self.params.driver == "MySQL" then
5887 1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	286 sql = sql.." AUTO_INCREMENT";
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	287 elseif self.params.driver == "SQLite3" then
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	288 sql = sql.." AUTOINCREMENT";
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	289 end
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	290 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	291 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	292 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	293 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	294 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	295 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	296 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	297 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	298 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	299 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	300 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	301 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	302 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	303 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	304 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	305 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	306 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	307 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	308 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	309 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	310 local driver = self.params.driver;
5889 ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	311 if driver == "SQLite3" then
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	312 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	313 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	314 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	315 self.charset = "utf8";
187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	316 end
5889 ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	317 end
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	318 end);
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	319 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	320 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	321 local charset = "utf8";
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	322 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	323 self:transaction(function()
8382 e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	324 for row in self:select[[
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	325 SELECT "CHARACTER_SET_NAME"
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	326 FROM "information_schema"."CHARACTER_SETS"
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	327 WHERE "CHARACTER_SET_NAME" LIKE 'utf8%'
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	328 ORDER BY MAXLEN DESC LIMIT 1;
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	329 ]] do
7275 187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	330 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	331 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	332 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	333 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	334 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	335 self.charset = charset;
6763 e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	336 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	337 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	338 if not ok then
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	339 return ok, err;
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	340 end
6771 60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace Kim Alvefur <zash@zash.se> parents: 6766 diff changeset	341
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 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	343 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	344 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	345 end);
10534 8a42fd6702e6 util.sql: Handle failure to detect connection encoding Kim Alvefur <zash@zash.se> parents: 10275 diff changeset	346 if not ok then
8a42fd6702e6 util.sql: Handle failure to detect connection encoding Kim Alvefur <zash@zash.se> parents: 10275 diff changeset	347 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	348 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	349 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	350 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	351 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	352 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	353 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	354 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	355 end
7275 187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	356 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	357 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	358 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	359 end
6771 60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace Kim Alvefur <zash@zash.se> parents: 6766 diff changeset	360
6763 e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	361 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	362 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	363 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	364
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	365 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	366 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	367 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	368 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	369 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	370 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	371 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	372 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	373 };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	374 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	375
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 10534 diff changeset	376 local function create_engine(_, params, onconnect, ondisconnect)
a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 10534 diff changeset	377 return setmetatable({ url = db2uri(params); params = params; onconnect = onconnect; ondisconnect = ondisconnect }, 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	378 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	379
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	380 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	381 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	382 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	383 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	384 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	385 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	386 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	387 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	388 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	389 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	390 };

prosody

fc97319ef48e

util/sql.lua

Software / code / prosody

Annotate

util/sql.lua @ 13758:fc97319ef48e 13.0

Software `/` code `/` prosody