Software /
code /
prosody
Annotate
util/sql.lua @ 9855:6c2370f17027
net.server_epoll: Optimize timer handling
author | Kim Alvefur <zash@zash.se> |
---|---|
date | Tue, 12 Mar 2019 23:13:51 +0100 |
parent | 9616:61376a3c0c1d |
child | 10038:7dd0dddd8e02 |
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.."\" ("; |
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
|
241 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
|
242 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
|
243 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
|
244 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
|
245 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
|
246 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
|
247 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
|
248 end |
5885
cbc25ae1eea0
util.sql: Allow creating unique indices
Kim Alvefur <zash@zash.se>
parents:
5883
diff
changeset
|
249 if index.unique then |
cbc25ae1eea0
util.sql: Allow creating unique indices
Kim Alvefur <zash@zash.se>
parents:
5883
diff
changeset
|
250 sql = sql:gsub("^CREATE", "CREATE UNIQUE"); |
cbc25ae1eea0
util.sql: Allow creating unique indices
Kim Alvefur <zash@zash.se>
parents:
5883
diff
changeset
|
251 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
|
252 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
|
253 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
|
254 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
|
255 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
|
256 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
|
257 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
|
258 local sql = "CREATE TABLE \""..table.name.."\" ("; |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
259 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
|
260 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
|
261 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
|
262 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
|
263 end |
5912
f6145e894569
util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL
Kim Alvefur <zash@zash.se>
parents:
5910
diff
changeset
|
264 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
|
265 col_type = "BIGSERIAL"; |
f6145e894569
util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL
Kim Alvefur <zash@zash.se>
parents:
5910
diff
changeset
|
266 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
|
267 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
|
268 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
|
269 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
|
270 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
|
271 if self.params.driver == "MySQL" then |
5887
1f860279b2f8
util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents:
5886
diff
changeset
|
272 sql = sql.." AUTO_INCREMENT"; |
1f860279b2f8
util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents:
5886
diff
changeset
|
273 elseif self.params.driver == "SQLite3" then |
1f860279b2f8
util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents:
5886
diff
changeset
|
274 sql = sql.." AUTOINCREMENT"; |
1f860279b2f8
util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents:
5886
diff
changeset
|
275 end |
1f860279b2f8
util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents:
5886
diff
changeset
|
276 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
|
277 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
|
278 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
|
279 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
|
280 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
|
281 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
|
282 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
|
283 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
|
284 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
|
285 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
|
286 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
|
287 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
|
288 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
|
289 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
|
290 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
|
291 end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
292 end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
293 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
|
294 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
|
295 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
|
296 local driver = self.params.driver; |
5889
ea6a3adb6a69
util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents:
5888
diff
changeset
|
297 if driver == "SQLite3" then |
ea6a3adb6a69
util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents:
5888
diff
changeset
|
298 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
|
299 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
|
300 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
|
301 self.charset = "utf8"; |
187ba2e9c012
util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents:
7272
diff
changeset
|
302 end |
5889
ea6a3adb6a69
util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents:
5888
diff
changeset
|
303 end |
ea6a3adb6a69
util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents:
5888
diff
changeset
|
304 end); |
ea6a3adb6a69
util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents:
5888
diff
changeset
|
305 end |
5888
f3e408ae59a6
util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents:
5887
diff
changeset
|
306 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
|
307 local charset = "utf8"; |
f3e408ae59a6
util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents:
5887
diff
changeset
|
308 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
|
309 self:transaction(function() |
8382
e5d00bf4a4d5
util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents:
8380
diff
changeset
|
310 for row in self:select[[ |
e5d00bf4a4d5
util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents:
8380
diff
changeset
|
311 SELECT "CHARACTER_SET_NAME" |
e5d00bf4a4d5
util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents:
8380
diff
changeset
|
312 FROM "information_schema"."CHARACTER_SETS" |
e5d00bf4a4d5
util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents:
8380
diff
changeset
|
313 WHERE "CHARACTER_SET_NAME" LIKE 'utf8%' |
e5d00bf4a4d5
util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents:
8380
diff
changeset
|
314 ORDER BY MAXLEN DESC LIMIT 1; |
e5d00bf4a4d5
util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents:
8380
diff
changeset
|
315 ]] do |
7275
187ba2e9c012
util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents:
7272
diff
changeset
|
316 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
|
317 end |
5888
f3e408ae59a6
util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents:
5887
diff
changeset
|
318 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
|
319 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
|
320 end |
5888
f3e408ae59a6
util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents:
5887
diff
changeset
|
321 self.charset = charset; |
6763
e961ac4efbb3
util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents:
6762
diff
changeset
|
322 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
|
323 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
|
324 if not ok then |
e961ac4efbb3
util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents:
6762
diff
changeset
|
325 return ok, err; |
e961ac4efbb3
util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents:
6762
diff
changeset
|
326 end |
6771
60957dd5b41b
util.{interpolation,prosodyctl,sql}: Trim trailing whitespace
Kim Alvefur <zash@zash.se>
parents:
6766
diff
changeset
|
327 |
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
|
328 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
|
329 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
|
330 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
|
331 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
|
332 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
|
333 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
|
334 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
|
335 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
|
336 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
|
337 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
|
338 end |
7275
187ba2e9c012
util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents:
7272
diff
changeset
|
339 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
|
340 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
|
341 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
|
342 end |
6771
60957dd5b41b
util.{interpolation,prosodyctl,sql}: Trim trailing whitespace
Kim Alvefur <zash@zash.se>
parents:
6766
diff
changeset
|
343 |
6763
e961ac4efbb3
util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents:
6762
diff
changeset
|
344 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
|
345 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
|
346 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
|
347 |
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
|
348 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
|
349 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
|
350 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
|
351 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
|
352 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
|
353 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
|
354 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
|
355 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
|
356 }; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.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 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
|
358 |
8382
e5d00bf4a4d5
util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents:
8380
diff
changeset
|
359 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
|
360 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
|
361 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
|
362 |
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
|
363 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
|
364 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
|
365 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
|
366 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
|
367 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
|
368 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
|
369 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
|
370 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
|
371 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
|
372 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
|
373 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
|
374 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
|
375 }; |