Software /
code /
prosody
Annotate
util/sql.lua @ 10066:216ae100c04a
mod_pep: Only log when creating new pubsub services
Once upon a time get_pep_service() would get called with random bare
JIDs and remote hostnames, which is why it was logged this way. This
seems to have been fixed, so it's not as useful anymore. It's still
useful to know when it creates a new service object.
author | Kim Alvefur <zash@zash.se> |
---|---|
date | Tue, 09 Jul 2019 15:12:32 +0200 |
parent | 10038:7dd0dddd8e02 |
child | 10109:c59d384b0959 |
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"); |
6805
c37633feaece
util.sql: Log when transactions begin
Kim Alvefur <zash@zash.se>
parents:
6777
diff
changeset
|
204 log("debug", "SQL transaction begin [%s]", tostring(func)); |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
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 |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
209 log("debug", "SQL transaction success [%s]", tostring(func)); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
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 |
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
|
215 log("debug", "SQL transaction failure [%s]: %s", tostring(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(...) |
6733
36e2b35397b1
util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents:
6730
diff
changeset
|
221 local ok, ret = self:_transaction(...); |
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; |
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
|
227 log("debug", "Retrying SQL transaction [%s]", tostring((...))); |
6733
36e2b35397b1
util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents:
6730
diff
changeset
|
228 ok, ret = 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 |
6733
36e2b35397b1
util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents:
6730
diff
changeset
|
237 return ok, ret; |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
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); |
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
|
338 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
|
339 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
|
340 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
|
341 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
|
342 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
|
343 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
|
344 end |
7275
187ba2e9c012
util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents:
7272
diff
changeset
|
345 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
|
346 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
|
347 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
|
348 end |
6771
60957dd5b41b
util.{interpolation,prosodyctl,sql}: Trim trailing whitespace
Kim Alvefur <zash@zash.se>
parents:
6766
diff
changeset
|
349 |
6763
e961ac4efbb3
util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents:
6762
diff
changeset
|
350 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
|
351 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
|
352 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
|
353 |
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
|
354 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
|
355 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
|
356 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
|
357 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
|
358 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
|
359 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
|
360 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
|
361 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
|
362 }; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.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 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
|
364 |
8382
e5d00bf4a4d5
util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents:
8380
diff
changeset
|
365 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
|
366 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
|
367 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
|
368 |
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
|
369 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
|
370 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
|
371 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
|
372 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
|
373 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
|
374 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
|
375 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
|
376 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
|
377 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
|
378 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
|
379 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
|
380 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
|
381 }; |