Software `/` code `/` prosody

Annotate

util/sql.lua @ 13073:9e5802b45b9e

mod_tokenauth: Only check if expiry of expiring tokens Some tokens, e.g. OAuth2 refresh tokens, might not have their lifetime explicitly bounded here, but rather be bounded by the lifetime of something else, like the OAuth2 client. Open question: Would it be better to enforce a lifetime on all tokens?

author	Kim Alvefur <zash@zash.se>
date	Wed, 12 Apr 2023 10:21:32 +0200
parent	12975:d10957394a3c
child	13240:a378937103cb

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
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
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 10534 diff changeset	102 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	103 -- Override from create_engine()
a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 10534 diff changeset	104 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	105
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 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	107 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	108 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	109 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	110 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	111 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	112
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	113 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	114 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	115 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	116 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	117
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	118 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	119 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	120 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	121 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	122 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	123 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	124 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	125 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	126
8382 e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	127 -- 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	128 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	129 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	130 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	131 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	132
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 local result_mt = { __index = {
5744 253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount Kim Alvefur <zash@zash.se> parents: 5743 diff changeset	134 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	135 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	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
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	138 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	139 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	140 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	141 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	142 i = i + 1;
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	143 local v = a[i];
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	144 if type(v) == "string" then
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	145 v = ("'%s'"):format(v:gsub("'", "''"));
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	146 end
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages Kim Alvefur <zash@zash.se> parents: 8077 diff changeset	147 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	148 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	149 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	150
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	151 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	152 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	153 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	154 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	155 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	156 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	157 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	158 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	159 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	160 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	161 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	162 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	163 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	164 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	165 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	166 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	167 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	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 assert(stmt:execute(...));
5744 253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount Kim Alvefur <zash@zash.se> parents: 5743 diff changeset	170 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	171 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	172 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	173 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	174 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	175 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	176 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	177 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	178 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	179 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	180 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	181 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	182 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	183 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	184 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	185 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	186 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	187 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	188 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	189 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	190 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	191 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	192 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	193 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	194 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	195 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	196 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	197 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	198 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	199 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	200 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	201 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	202 if not self.conn then
6733 36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	203 local ok, err = self:connect();
36e2b35397b1 util.sql: Rename some variable to match conventions Matthew Wild <mwild1@gmail.com> parents: 6730 diff changeset	204 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	205 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	206 --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	207 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	208 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	209 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	210 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	211 if success then
10109 c59d384b0959 util.sql: Remove tostring call from logging Kim Alvefur <zash@zash.se> parents: 10038 diff changeset	212 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	213 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	214 -- 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	215 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	216 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	217 else
10109 c59d384b0959 util.sql: Remove tostring call from logging Kim Alvefur <zash@zash.se> parents: 10038 diff changeset	218 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	219 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	220 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	221 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	222 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	223 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	224 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	225 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	226 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	227 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	228 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	229 self.conn = nil;
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 10534 diff changeset	230 self:ondisconnect();
10109 c59d384b0959 util.sql: Remove tostring call from logging Kim Alvefur <zash@zash.se> parents: 10038 diff changeset	231 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	232 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	233 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	234 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	235 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	236 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	237 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	238 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	239 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	240 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	241 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	242 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	243 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	244 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	245 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	246 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	247 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	248 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	249 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	250 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	251 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	252 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	253 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	254 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	255 end
5885 cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	256 if index.unique then
cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	257 sql = sql:gsub("^CREATE", "CREATE UNIQUE");
cbc25ae1eea0 util.sql: Allow creating unique indices Kim Alvefur <zash@zash.se> parents: 5883 diff changeset	258 end
7174 d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	259 if self._debug then
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	260 debugquery("create", sql);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin) Kim Alvefur <zash@zash.se> parents: 6806 diff changeset	261 end
5494 9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	262 return self:execute(sql);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	263 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	264 function engine:_create_table(table)
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	265 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	266 do
7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064) Kim Alvefur <zash@zash.se> parents: 9616 diff changeset	267 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	268 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	269 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	270 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	271 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	272 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	273 end
5912 f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL Kim Alvefur <zash@zash.se> parents: 5910 diff changeset	274 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	275 col_type = "BIGSERIAL";
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL Kim Alvefur <zash@zash.se> parents: 5910 diff changeset	276 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	277 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	278 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	279 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	280 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	281 if self.params.driver == "MySQL" then
5887 1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	282 sql = sql.." AUTO_INCREMENT";
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	283 elseif self.params.driver == "SQLite3" then
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	284 sql = sql.." AUTOINCREMENT";
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	285 end
1f860279b2f8 util.sql: Support incrementing columns Kim Alvefur <zash@zash.se> parents: 5886 diff changeset	286 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	287 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	288 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	289 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	290 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	291 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	292 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	293 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	294 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	295 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	296 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	297 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	298 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	299 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	300 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	301 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress Matthew Wild <mwild1@gmail.com> parents: diff changeset	302 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	303 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	304 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	305 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	306 local driver = self.params.driver;
5889 ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	307 if driver == "SQLite3" then
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	308 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	309 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	310 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	311 self.charset = "utf8";
187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	312 end
5889 ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	313 end
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	314 end);
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses Kim Alvefur <zash@zash.se> parents: 5888 diff changeset	315 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	316 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	317 local charset = "utf8";
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	318 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	319 self:transaction(function()
8382 e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	320 for row in self:select[[
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	321 SELECT "CHARACTER_SET_NAME"
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	322 FROM "information_schema"."CHARACTER_SETS"
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	323 WHERE "CHARACTER_SET_NAME" LIKE 'utf8%'
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	324 ORDER BY MAXLEN DESC LIMIT 1;
e5d00bf4a4d5 util: Various minor changes to please [luacheck] Kim Alvefur <zash@zash.se> parents: 8380 diff changeset	325 ]] do
7275 187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	326 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	327 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	328 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	329 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	330 end
5888 f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that Kim Alvefur <zash@zash.se> parents: 5887 diff changeset	331 self.charset = charset;
6763 e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	332 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	333 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	334 if not ok then
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	335 return ok, err;
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	336 end
6771 60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace Kim Alvefur <zash@zash.se> parents: 6766 diff changeset	337
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	338 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	339 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	340 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	341 end);
10534 8a42fd6702e6 util.sql: Handle failure to detect connection encoding Kim Alvefur <zash@zash.se> parents: 10275 diff changeset	342 if not ok then
8a42fd6702e6 util.sql: Handle failure to detect connection encoding Kim Alvefur <zash@zash.se> parents: 10275 diff changeset	343 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	344 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	345 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	346 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	347 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	348 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	349 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	350 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	351 end
7275 187ba2e9c012 util.sql: Don't break out of result retreival loops Kim Alvefur <zash@zash.se> parents: 7272 diff changeset	352 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	353 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	354 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	355 end
6771 60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace Kim Alvefur <zash@zash.se> parents: 6766 diff changeset	356
6763 e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES Matthew Wild <mwild1@gmail.com> parents: 6762 diff changeset	357 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	358 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	359 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	360
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	361 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	362 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	363 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	364 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	365 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	366 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	367 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	368 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	369 };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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
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	371
12872 a20923f7d5fd mod_storage_sql: Record connection to database as module status Kim Alvefur <zash@zash.se> parents: 10534 diff changeset	372 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	373 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	374 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	375
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	376 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	377 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	378 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	379 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	380 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	381 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	382 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	383 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	384 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	385 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	386 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	387 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	388 };

prosody

9e5802b45b9e

util/sql.lua

Software / code / prosody

Annotate

util/sql.lua @ 13073:9e5802b45b9e

Software `/` code `/` prosody