prosody
6a098961bc00
util/sql.lua

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

Software / code / prosody

Annotate

util/sql.lua @ 8378:6a098961bc00

util.sql: Return an error message when a SQL commit fails (LuaDBI doesn't) (Thanks Ge0rG)
author Kim Alvefur <zash@zash.se>
date Tue, 07 Nov 2017 09:26:48 +0100
parent 8288:e9ac2d93de18
child 8380:a597ff326758
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
1
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
2 local setmetatable, getmetatable = setmetatable, getmetatable;
7180
0d7f80263813 util.sql: Localize unpack() in Lua 5.2 compatible way
Kim Alvefur <zash@zash.se>
parents: 7174
diff changeset
3 local ipairs, unpack, select = ipairs, table.unpack or unpack, select; --luacheck: ignore 113
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
4 local tonumber, tostring = tonumber, tostring;
7431
0d991d5659f0 util.sql: Import type too (fix global access)
Kim Alvefur <zash@zash.se>
parents: 7428
diff changeset
5 local type = type;
7428
f791651ee334 util.sql: Import pcall (fixes #677)
Matthew Wild <mwild1@gmail.com>
parents: 7317
diff changeset
6 local assert, pcall, xpcall, debug_traceback = assert, pcall, xpcall, debug.traceback;
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
7 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
8 local s_char = string.char;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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;
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
18
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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
20 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
21 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
22 --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
23 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
24
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
25 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
26 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
27 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
28 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
29 local function Integer() return "Integer()" end
30dfaf36ea6d util.sql: Remove unused arguments [luacheck]
Kim Alvefur <zash@zash.se>
parents: 7275
diff changeset
30 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
31
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
32 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
33 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
34 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
35 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
36 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
37 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
38 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
39 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
40 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
41 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
42 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
43 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 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
45 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
46 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
47 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
48 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
49
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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
51 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
52 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
53 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
54 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
55 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
56 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
57 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
58 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
59 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
60 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
61 function table_mt:__call(...)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 -- TODO
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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
64 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
65 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
66 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
67 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
68 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
69 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
70 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
71 -- 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
72 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
73
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
74 local function urldecode(s) return s and (s:gsub("%%(%x%x)", function (c) return s_char(tonumber(c,16)); end)); 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 local function parse_url(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
76 local scheme, secondpart, database = url:match("^([%w%+]+)://([^/]*)/?(.*)");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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(scheme, "Invalid URL format");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
78 local username, password, host, 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
79 local authpart, hostpart = secondpart:match("([^@]+)@([^@+])");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 if not authpart then hostpart = secondpart; 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
81 if authpart 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
82 username, password = authpart:match("([^:]*):(.*)");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 username = username or authpart;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
84 password = password and urldecode(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
85 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 if hostpart 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
87 host, port = hostpart:match("([^:]*):(.*)");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 host = host or hostpart;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
89 port = port and assert(tonumber(port), "Invalid URL format");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
90 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
91 return {
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
92 scheme = scheme:lower();
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
93 username = username; password = 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
94 host = host; port = 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
95 database = #database > 0 and database or 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
96 };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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
98
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
99 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
100 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
101 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
102
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
103 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
104 assert(params.driver, "no driver")
6765
0cbb09afa5c3 util.sql: Fix log level of debug message
Matthew Wild <mwild1@gmail.com>
parents: 6764
diff changeset
105 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
106 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
107 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
108 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
109 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
110 );
7306
98c4c3a2b536 util.sql: Catch errors from LuaDBI connect (Fixes #568)
Kim Alvefur <zash@zash.se>
parents: 7276
diff changeset
111 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
112 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
113 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
114 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
115 self.prepared = {};
6762
ea43a5af31ca util.sql: Return failure if set_encoding() fails
Matthew Wild <mwild1@gmail.com>
parents: 6760
diff changeset
116 local ok, err = self:set_encoding();
ea43a5af31ca util.sql: Return failure if set_encoding() fails
Matthew Wild <mwild1@gmail.com>
parents: 6760
diff changeset
117 if not ok then
ea43a5af31ca util.sql: Return failure if set_encoding() fails
Matthew Wild <mwild1@gmail.com>
parents: 6760
diff changeset
118 return ok, err;
ea43a5af31ca util.sql: Return failure if set_encoding() fails
Matthew Wild <mwild1@gmail.com>
parents: 6760
diff changeset
119 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
120 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
121 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
122 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
123 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
124 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
125 end
6748
ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set
Kim Alvefur <zash@zash.se>
parents: 6735
diff changeset
126 function engine:onconnect()
ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set
Kim Alvefur <zash@zash.se>
parents: 6735
diff changeset
127 -- Override from create_engine()
ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set
Kim Alvefur <zash@zash.se>
parents: 6735
diff changeset
128 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
129
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method
Kim Alvefur <zash@zash.se>
parents: 7180
diff changeset
130 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
131 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
132 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
133 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
134 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
135 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
136
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
137 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
138 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
139 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
140 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
141
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
142 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
143 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
144 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
145 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
146 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
147 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
148 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
149 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
150
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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
152 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
153 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
154 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
155
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
156 local result_mt = { __index = {
5744
253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount
Kim Alvefur <zash@zash.se>
parents: 5743
diff changeset
157 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
158 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
159 } };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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
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
161 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
162 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
163 sql = sql:gsub("\n?\t+", " ");
8084
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
164 log("debug", "[%s] %s", where, sql:gsub("%?", function ()
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
165 i = i + 1;
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
166 local v = a[i];
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
167 if type(v) == "string" then
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
168 v = ("'%s'"):format(v:gsub("'", "''"));
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
169 end
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
170 return tostring(v);
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
171 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
172 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
173
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
174 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
175 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
176 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
177 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
178 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
179 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
180 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
181 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
182 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
183 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
184 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
185 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
186 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
187 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
188 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
189 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
190 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
191 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
192 assert(stmt:execute(...));
5744
253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount
Kim Alvefur <zash@zash.se>
parents: 5743
diff changeset
193 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
194 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
195 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
196 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
197 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
198 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
199 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
200 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
201 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
202 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
203 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
204 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
205 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
206 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
207 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
208 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
209 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
210 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
211 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
212 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
213 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
214 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
215 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
216 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
217 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
218 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
219 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
220 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
221 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
222 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
223 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
224 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
225 if not self.conn then
6733
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
226 local ok, err = self:connect();
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
227 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
228 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
229 --assert(not self.__transaction, "Recursive transactions not allowed");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
230 local args, n_args = {...}, select("#", ...);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
231 local function f() return func(unpack(args, 1, n_args)); end
6805
c37633feaece util.sql: Log when transactions begin
Kim Alvefur <zash@zash.se>
parents: 6777
diff changeset
232 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
233 self.__transaction = true;
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
234 local success, a, b, c = xpcall(f, 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
235 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
236 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
237 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
238 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
239 -- 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
240 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
241 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
242 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
243 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
244 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
245 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
246 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
247 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 function engine:transaction(...)
6733
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
249 local ok, ret = self:_transaction(...);
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
250 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
251 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
252 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
253 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
254 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
255 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
256 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
257 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
258 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
259 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
260 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
261 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
262 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
263 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
264 end
6733
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
265 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
266 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
267 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
268 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
269 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
270 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
271 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
272 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
273 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
274 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
275 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
276 end
5885
cbc25ae1eea0 util.sql: Allow creating unique indices
Kim Alvefur <zash@zash.se>
parents: 5883
diff changeset
277 if index.unique then
cbc25ae1eea0 util.sql: Allow creating unique indices
Kim Alvefur <zash@zash.se>
parents: 5883
diff changeset
278 sql = sql:gsub("^CREATE", "CREATE UNIQUE");
cbc25ae1eea0 util.sql: Allow creating unique indices
Kim Alvefur <zash@zash.se>
parents: 5883
diff changeset
279 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
280 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
281 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
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 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
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 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
286 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
287 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
288 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
289 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
290 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
291 end
5912
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL
Kim Alvefur <zash@zash.se>
parents: 5910
diff changeset
292 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
293 col_type = "BIGSERIAL";
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL
Kim Alvefur <zash@zash.se>
parents: 5910
diff changeset
294 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
295 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
296 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
297 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
298 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
299 if self.params.driver == "MySQL" then
5887
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
300 sql = sql.." AUTO_INCREMENT";
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
301 elseif self.params.driver == "SQLite3" then
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
302 sql = sql.." AUTOINCREMENT";
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
303 end
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
304 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
305 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
306 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
307 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
308 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
309 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
310 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
311 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
312 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
313 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
314 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
315 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
316 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
317 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
318 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
319 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
320 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
321 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
322 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
323 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
324 local driver = self.params.driver;
5889
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
325 if driver == "SQLite3" then
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
326 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
327 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
328 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
329 self.charset = "utf8";
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
330 end
5889
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
331 end
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
332 end);
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
333 end
5888
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
334 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
335 local charset = "utf8";
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
336 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
337 self:transaction(function()
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
338 for row in self:select"SELECT \"CHARACTER_SET_NAME\" FROM \"information_schema\".\"CHARACTER_SETS\" WHERE \"CHARACTER_SET_NAME\" LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1;" do
7275
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
339 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
340 end
5888
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
341 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
342 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
343 end
5888
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
344 self.charset = charset;
6763
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
345 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
346 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
347 if not ok then
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
348 return ok, err;
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
349 end
6771
60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace
Kim Alvefur <zash@zash.se>
parents: 6766
diff changeset
350
6766
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL)
Matthew Wild <mwild1@gmail.com>
parents: 6765
diff changeset
351 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
352 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
353 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
354 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
355 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
356 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
357 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
358 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
359 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
360 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
361 end
7275
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
362 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
363 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
364 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
365 end
6771
60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace
Kim Alvefur <zash@zash.se>
parents: 6766
diff changeset
366
6763
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
367 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
368 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
369 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
370
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
371 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
372 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
373 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
374 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
375 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
376 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
377 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
378 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
379 };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
380 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
381
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
382 local function create_engine(self, 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
383 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
384 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
385
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
386 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
387 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
388 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
389 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
390 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
391 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
392 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
393 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
394 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
395 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
396 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
397 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
398 };