prosody
a219001b449d
util/sql.lua

Find changesets by keywords by author, files, the commit message, revision number or hash, or revset expression.

Software / code / prosody

Annotate

util/sql.lua @ 11349:a219001b449d

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