util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own...
[prosody.git] / util / sql.lua
1
2 local setmetatable, getmetatable = setmetatable, getmetatable;
3 local ipairs, unpack, select = ipairs, table.unpack or unpack, select; --luacheck: ignore 113
4 local tonumber, tostring = tonumber, tostring;
5 local assert, xpcall, debug_traceback = assert, xpcall, debug.traceback;
6 local t_concat = table.concat;
7 local s_char = string.char;
8 local log = require "util.logger".init("sql");
9
10 local DBI = require "DBI";
11 -- This loads all available drivers while globals are unlocked
12 -- LuaDBI should be fixed to not set globals.
13 DBI.Drivers();
14 local build_url = require "socket.url".build;
15
16 local _ENV = nil;
17
18 local column_mt = {};
19 local table_mt = {};
20 local query_mt = {};
21 --local op_mt = {};
22 local index_mt = {};
23
24 local function is_column(x) return getmetatable(x)==column_mt; end
25 local function is_index(x) return getmetatable(x)==index_mt; end
26 local function is_table(x) return getmetatable(x)==table_mt; end
27 local function is_query(x) return getmetatable(x)==query_mt; end
28 local function Integer(n) return "Integer()" end
29 local function String(n) return "String()" end
30
31 local function Column(definition)
32         return setmetatable(definition, column_mt);
33 end
34 local function Table(definition)
35         local c = {}
36         for i,col in ipairs(definition) do
37                 if is_column(col) then
38                         c[i], c[col.name] = col, col;
39                 elseif is_index(col) then
40                         col.table = definition.name;
41                 end
42         end
43         return setmetatable({ __table__ = definition, c = c, name = definition.name }, table_mt);
44 end
45 local function Index(definition)
46         return setmetatable(definition, index_mt);
47 end
48
49 function table_mt:__tostring()
50         local s = { 'name="'..self.__table__.name..'"' }
51         for i,col in ipairs(self.__table__) do
52                 s[#s+1] = tostring(col);
53         end
54         return 'Table{ '..t_concat(s, ", ")..' }'
55 end
56 table_mt.__index = {};
57 function table_mt.__index:create(engine)
58         return engine:_create_table(self);
59 end
60 function table_mt:__call(...)
61         -- TODO
62 end
63 function column_mt:__tostring()
64         return 'Column{ name="'..self.name..'", type="'..self.type..'" }'
65 end
66 function index_mt:__tostring()
67         local s = 'Index{ name="'..self.name..'"';
68         for i=1,#self do s = s..', "'..self[i]:gsub("[\\\"]", "\\%1")..'"'; end
69         return s..' }';
70 --      return 'Index{ name="'..self.name..'", type="'..self.type..'" }'
71 end
72
73 local function urldecode(s) return s and (s:gsub("%%(%x%x)", function (c) return s_char(tonumber(c,16)); end)); end
74 local function parse_url(url)
75         local scheme, secondpart, database = url:match("^([%w%+]+)://([^/]*)/?(.*)");
76         assert(scheme, "Invalid URL format");
77         local username, password, host, port;
78         local authpart, hostpart = secondpart:match("([^@]+)@([^@+])");
79         if not authpart then hostpart = secondpart; end
80         if authpart then
81                 username, password = authpart:match("([^:]*):(.*)");
82                 username = username or authpart;
83                 password = password and urldecode(password);
84         end
85         if hostpart then
86                 host, port = hostpart:match("([^:]*):(.*)");
87                 host = host or hostpart;
88                 port = port and assert(tonumber(port), "Invalid URL format");
89         end
90         return {
91                 scheme = scheme:lower();
92                 username = username; password = password;
93                 host = host; port = port;
94                 database = #database > 0 and database or nil;
95         };
96 end
97
98 local engine = {};
99 function engine:connect()
100         if self.conn then return true; end
101
102         local params = self.params;
103         assert(params.driver, "no driver")
104         log("debug", "Connecting to [%s] %s...", params.driver, params.database);
105         local dbh, err = DBI.Connect(
106                 params.driver, params.database,
107                 params.username, params.password,
108                 params.host, params.port
109         );
110         if not dbh then return nil, err; end
111         dbh:autocommit(false); -- don't commit automatically
112         self.conn = dbh;
113         self.prepared = {};
114         local ok, err = self:set_encoding();
115         if not ok then
116                 return ok, err;
117         end
118         local ok, err = self:onconnect();
119         if ok == false then
120                 return ok, err;
121         end
122         return true;
123 end
124 function engine:onconnect()
125         -- Override from create_engine()
126 end
127
128 function engine:prepquery(sql)
129         if self.params.driver == "PostgreSQL" then
130                 sql = sql:gsub("`", "\"");
131         end
132         return sql;
133 end
134
135 function engine:execute(sql, ...)
136         local success, err = self:connect();
137         if not success then return success, err; end
138         local prepared = self.prepared;
139
140         local stmt = prepared[sql];
141         if not stmt then
142                 local err;
143                 stmt, err = self.conn:prepare(sql);
144                 if not stmt then return stmt, err; end
145                 prepared[sql] = stmt;
146         end
147
148         local success, err = stmt:execute(...);
149         if not success then return success, err; end
150         return stmt;
151 end
152
153 local result_mt = { __index = {
154         affected = function(self) return self.__stmt:affected(); end;
155         rowcount = function(self) return self.__stmt:rowcount(); end;
156 } };
157
158 local function debugquery(where, sql, ...)
159         local i = 0; local a = {...}
160         log("debug", "[%s] %s", where, sql:gsub("%?", function () i = i + 1; local v = a[i]; if type(v) == "string" then v = ("%q"):format(v); end return tostring(v); end));
161 end
162
163 function engine:execute_query(sql, ...)
164         sql = self:prepquery(sql);
165         local stmt = assert(self.conn:prepare(sql));
166         assert(stmt:execute(...));
167         return stmt:rows();
168 end
169 function engine:execute_update(sql, ...)
170         sql = self:prepquery(sql);
171         local prepared = self.prepared;
172         local stmt = prepared[sql];
173         if not stmt then
174                 stmt = assert(self.conn:prepare(sql));
175                 prepared[sql] = stmt;
176         end
177         assert(stmt:execute(...));
178         return setmetatable({ __stmt = stmt }, result_mt);
179 end
180 engine.insert = engine.execute_update;
181 engine.select = engine.execute_query;
182 engine.delete = engine.execute_update;
183 engine.update = engine.execute_update;
184 local function debugwrap(name, f)
185         return function (self, sql, ...)
186                 debugquery(name, sql, ...)
187                 return f(self, sql, ...)
188         end
189 end
190 function engine:debug(enable)
191         self._debug = enable;
192         if enable then
193                 engine.insert = debugwrap("insert", engine.execute_update);
194                 engine.select = debugwrap("select", engine.execute_query);
195                 engine.delete = debugwrap("delete", engine.execute_update);
196                 engine.update = debugwrap("update", engine.execute_update);
197         else
198                 engine.insert = engine.execute_update;
199                 engine.select = engine.execute_query;
200                 engine.delete = engine.execute_update;
201                 engine.update = engine.execute_update;
202         end
203 end
204 function engine:_transaction(func, ...)
205         if not self.conn then
206                 local ok, err = self:connect();
207                 if not ok then return ok, err; end
208         end
209         --assert(not self.__transaction, "Recursive transactions not allowed");
210         local args, n_args = {...}, select("#", ...);
211         local function f() return func(unpack(args, 1, n_args)); end
212         log("debug", "SQL transaction begin [%s]", tostring(func));
213         self.__transaction = true;
214         local success, a, b, c = xpcall(f, debug_traceback);
215         self.__transaction = nil;
216         if success then
217                 log("debug", "SQL transaction success [%s]", tostring(func));
218                 local ok, err = self.conn:commit();
219                 if not ok then return ok, err; end -- commit failed
220                 return success, a, b, c;
221         else
222                 log("debug", "SQL transaction failure [%s]: %s", tostring(func), a);
223                 if self.conn then self.conn:rollback(); end
224                 return success, a;
225         end
226 end
227 function engine:transaction(...)
228         local ok, ret = self:_transaction(...);
229         if not ok then
230                 local conn = self.conn;
231                 if not conn or not conn:ping() then
232                         self.conn = nil;
233                         ok, ret = self:_transaction(...);
234                 end
235         end
236         return ok, ret;
237 end
238 function engine:_create_index(index)
239         local sql = "CREATE INDEX `"..index.name.."` ON `"..index.table.."` (";
240         for i=1,#index do
241                 sql = sql.."`"..index[i].."`";
242                 if i ~= #index then sql = sql..", "; end
243         end
244         sql = sql..");"
245         if self.params.driver == "PostgreSQL" then
246                 sql = sql:gsub("`", "\"");
247         elseif self.params.driver == "MySQL" then
248                 sql = sql:gsub("`([,)])", "`(20)%1");
249         end
250         if index.unique then
251                 sql = sql:gsub("^CREATE", "CREATE UNIQUE");
252         end
253         if self._debug then
254                 debugquery("create", sql);
255         end
256         return self:execute(sql);
257 end
258 function engine:_create_table(table)
259         local sql = "CREATE TABLE `"..table.name.."` (";
260         for i,col in ipairs(table.c) do
261                 local col_type = col.type;
262                 if col_type == "MEDIUMTEXT" and self.params.driver ~= "MySQL" then
263                         col_type = "TEXT"; -- MEDIUMTEXT is MySQL-specific
264                 end
265                 if col.auto_increment == true and self.params.driver == "PostgreSQL" then
266                         col_type = "BIGSERIAL";
267                 end
268                 sql = sql.."`"..col.name.."` "..col_type;
269                 if col.nullable == false then sql = sql.." NOT NULL"; end
270                 if col.primary_key == true then sql = sql.." PRIMARY KEY"; end
271                 if col.auto_increment == true then
272                         if self.params.driver == "MySQL" then
273                                 sql = sql.." AUTO_INCREMENT";
274                         elseif self.params.driver == "SQLite3" then
275                                 sql = sql.." AUTOINCREMENT";
276                         end
277                 end
278                 if i ~= #table.c then sql = sql..", "; end
279         end
280         sql = sql.. ");"
281         if self.params.driver == "PostgreSQL" then
282                 sql = sql:gsub("`", "\"");
283         elseif self.params.driver == "MySQL" then
284                 sql = sql:gsub(";$", (" CHARACTER SET '%s' COLLATE '%s_bin';"):format(self.charset, self.charset));
285         end
286         if self._debug then
287                 debugquery("create", sql);
288         end
289         local success,err = self:execute(sql);
290         if not success then return success,err; end
291         for i,v in ipairs(table.__table__) do
292                 if is_index(v) then
293                         self:_create_index(v);
294                 end
295         end
296         return success;
297 end
298 function engine:set_encoding() -- to UTF-8
299         local driver = self.params.driver;
300         if driver == "SQLite3" then
301                 return self:transaction(function()
302                         if self:select"PRAGMA encoding;"()[1] == "UTF-8" then
303                                 self.charset = "utf8";
304                         end
305                 end);
306         end
307         local set_names_query = "SET NAMES '%s';"
308         local charset = "utf8";
309         if driver == "MySQL" then
310                 local ok, charsets = self:transaction(function()
311                         return self:select"SELECT `CHARACTER_SET_NAME` FROM `information_schema`.`CHARACTER_SETS` WHERE `CHARACTER_SET_NAME` LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1;";
312                 end);
313                 local row = ok and charsets();
314                 charset = row and row[1] or charset;
315                 set_names_query = set_names_query:gsub(";$", (" COLLATE '%s';"):format(charset.."_bin"));
316         end
317         self.charset = charset;
318         log("debug", "Using encoding '%s' for database connection", charset);
319         local ok, err = self:transaction(function() return self:execute(set_names_query:format(charset)); end);
320         if not ok then
321                 return ok, err;
322         end
323
324         if driver == "MySQL" then
325                 local ok, actual_charset = self:transaction(function ()
326                         return self:select"SHOW SESSION VARIABLES LIKE 'character_set_client'";
327                 end);
328                 for row in actual_charset do
329                         if row[2] ~= charset then
330                                 log("error", "MySQL %s is actually %q (expected %q)", row[1], row[2], charset);
331                                 return false, "Failed to set connection encoding";
332                         end
333                 end
334         end
335
336         return true;
337 end
338 local engine_mt = { __index = engine };
339
340 local function db2uri(params)
341         return build_url{
342                 scheme = params.driver,
343                 user = params.username,
344                 password = params.password,
345                 host = params.host,
346                 port = params.port,
347                 path = params.database,
348         };
349 end
350
351 local function create_engine(self, params, onconnect)
352         return setmetatable({ url = db2uri(params), params = params, onconnect = onconnect }, engine_mt);
353 end
354
355 return {
356         is_column = is_column;
357         is_index = is_index;
358         is_table = is_table;
359         is_query = is_query;
360         Integer = Integer;
361         String = String;
362         Column = Column;
363         Table = Table;
364         Index = Index;
365         create_engine = create_engine;
366         db2uri = db2uri;
367 };