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() return "Integer()" end
  29 local function String() 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 ok, dbh, err = pcall(DBI.Connect,
 106                 params.driver, params.database,
 107                 params.username, params.password,
 108                 params.host, params.port
 109         );
 110         if not ok then return ok, dbh; end
 111         if not dbh then return nil, err; end
 112         dbh:autocommit(false); -- don't commit automatically
 113         self.conn = dbh;
 114         self.prepared = {};
 115         local ok, err = self:set_encoding();
 116         if not ok then
 117                 return ok, err;
 118         end
 119         local ok, err = self:onconnect();
 120         if ok == false then
 121                 return ok, err;
 122         end
 123         return true;
 124 end
 125 function engine:onconnect()
 126         -- Override from create_engine()
 127 end
 128
 129 function engine:prepquery(sql)
 130         if self.params.driver == "PostgreSQL" then
 131                 sql = sql:gsub("`", "\"");
 132         end
 133         return sql;
 134 end
 135
 136 function engine:execute(sql, ...)
 137         local success, err = self:connect();
 138         if not success then return success, err; end
 139         local prepared = self.prepared;
 140
 141         local stmt = prepared[sql];
 142         if not stmt then
 143                 local err;
 144                 stmt, err = self.conn:prepare(sql);
 145                 if not stmt then return stmt, err; end
 146                 prepared[sql] = stmt;
 147         end
 148
 149         local success, err = stmt:execute(...);
 150         if not success then return success, err; end
 151         return stmt;
 152 end
 153
 154 local result_mt = { __index = {
 155         affected = function(self) return self.__stmt:affected(); end;
 156         rowcount = function(self) return self.__stmt:rowcount(); end;
 157 } };
 158
 159 local function debugquery(where, sql, ...)
 160         local i = 0; local a = {...}
 161         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));
 162 end
 163
 164 function engine:execute_query(sql, ...)
 165         sql = self:prepquery(sql);
 166         local stmt = assert(self.conn:prepare(sql));
 167         assert(stmt:execute(...));
 168         return stmt:rows();
 169 end
 170 function engine:execute_update(sql, ...)
 171         sql = self:prepquery(sql);
 172         local prepared = self.prepared;
 173         local stmt = prepared[sql];
 174         if not stmt then
 175                 stmt = assert(self.conn:prepare(sql));
 176                 prepared[sql] = stmt;
 177         end
 178         assert(stmt:execute(...));
 179         return setmetatable({ __stmt = stmt }, result_mt);
 180 end
 181 engine.insert = engine.execute_update;
 182 engine.select = engine.execute_query;
 183 engine.delete = engine.execute_update;
 184 engine.update = engine.execute_update;
 185 local function debugwrap(name, f)
 186         return function (self, sql, ...)
 187                 debugquery(name, sql, ...)
 188                 return f(self, sql, ...)
 189         end
 190 end
 191 function engine:debug(enable)
 192         self._debug = enable;
 193         if enable then
 194                 engine.insert = debugwrap("insert", engine.execute_update);
 195                 engine.select = debugwrap("select", engine.execute_query);
 196                 engine.delete = debugwrap("delete", engine.execute_update);
 197                 engine.update = debugwrap("update", engine.execute_update);
 198         else
 199                 engine.insert = engine.execute_update;
 200                 engine.select = engine.execute_query;
 201                 engine.delete = engine.execute_update;
 202                 engine.update = engine.execute_update;
 203         end
 204 end
 205 function engine:_transaction(func, ...)
 206         if not self.conn then
 207                 local ok, err = self:connect();
 208                 if not ok then return ok, err; end
 209         end
 210         --assert(not self.__transaction, "Recursive transactions not allowed");
 211         local args, n_args = {...}, select("#", ...);
 212         local function f() return func(unpack(args, 1, n_args)); end
 213         log("debug", "SQL transaction begin [%s]", tostring(func));
 214         self.__transaction = true;
 215         local success, a, b, c = xpcall(f, debug_traceback);
 216         self.__transaction = nil;
 217         if success then
 218                 log("debug", "SQL transaction success [%s]", tostring(func));
 219                 local ok, err = self.conn:commit();
 220                 if not ok then return ok, err; end -- commit failed
 221                 return success, a, b, c;
 222         else
 223                 log("debug", "SQL transaction failure [%s]: %s", tostring(func), a);
 224                 if self.conn then self.conn:rollback(); end
 225                 return success, a;
 226         end
 227 end
 228 function engine:transaction(...)
 229         local ok, ret = self:_transaction(...);
 230         if not ok then
 231                 local conn = self.conn;
 232                 if not conn or not conn:ping() then
 233                         self.conn = nil;
 234                         ok, ret = self:_transaction(...);
 235                 end
 236         end
 237         return ok, ret;
 238 end
 239 function engine:_create_index(index)
 240         local sql = "CREATE INDEX `"..index.name.."` ON `"..index.table.."` (";
 241         for i=1,#index do
 242                 sql = sql.."`"..index[i].."`";
 243                 if i ~= #index then sql = sql..", "; end
 244         end
 245         sql = sql..");"
 246         if self.params.driver == "PostgreSQL" then
 247                 sql = sql:gsub("`", "\"");
 248         elseif self.params.driver == "MySQL" then
 249                 sql = sql:gsub("`([,)])", "`(20)%1");
 250         end
 251         if index.unique then
 252                 sql = sql:gsub("^CREATE", "CREATE UNIQUE");
 253         end
 254         if self._debug then
 255                 debugquery("create", sql);
 256         end
 257         return self:execute(sql);
 258 end
 259 function engine:_create_table(table)
 260         local sql = "CREATE TABLE `"..table.name.."` (";
 261         for i,col in ipairs(table.c) do
 262                 local col_type = col.type;
 263                 if col_type == "MEDIUMTEXT" and self.params.driver ~= "MySQL" then
 264                         col_type = "TEXT"; -- MEDIUMTEXT is MySQL-specific
 265                 end
 266                 if col.auto_increment == true and self.params.driver == "PostgreSQL" then
 267                         col_type = "BIGSERIAL";
 268                 end
 269                 sql = sql.."`"..col.name.."` "..col_type;
 270                 if col.nullable == false then sql = sql.." NOT NULL"; end
 271                 if col.primary_key == true then sql = sql.." PRIMARY KEY"; end
 272                 if col.auto_increment == true then
 273                         if self.params.driver == "MySQL" then
 274                                 sql = sql.." AUTO_INCREMENT";
 275                         elseif self.params.driver == "SQLite3" then
 276                                 sql = sql.." AUTOINCREMENT";
 277                         end
 278                 end
 279                 if i ~= #table.c then sql = sql..", "; end
 280         end
 281         sql = sql.. ");"
 282         if self.params.driver == "PostgreSQL" then
 283                 sql = sql:gsub("`", "\"");
 284         elseif self.params.driver == "MySQL" then
 285                 sql = sql:gsub(";$", (" CHARACTER SET '%s' COLLATE '%s_bin';"):format(self.charset, self.charset));
 286         end
 287         if self._debug then
 288                 debugquery("create", sql);
 289         end
 290         local success,err = self:execute(sql);
 291         if not success then return success,err; end
 292         for i,v in ipairs(table.__table__) do
 293                 if is_index(v) then
 294                         self:_create_index(v);
 295                 end
 296         end
 297         return success;
 298 end
 299 function engine:set_encoding() -- to UTF-8
 300         local driver = self.params.driver;
 301         if driver == "SQLite3" then
 302                 return self:transaction(function()
 303                         for encoding in self:select"PRAGMA encoding;" do
 304                                 if encoding[1] == "UTF-8" then
 305                                         self.charset = "utf8";
 306                                 end
 307                         end
 308                 end);
 309         end
 310         local set_names_query = "SET NAMES '%s';"
 311         local charset = "utf8";
 312         if driver == "MySQL" then
 313                 self:transaction(function()
 314                         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
 315                                 charset = row and row[1] or charset;
 316                         end
 317                 end);
 318                 set_names_query = set_names_query:gsub(";$", (" COLLATE '%s';"):format(charset.."_bin"));
 319         end
 320         self.charset = charset;
 321         log("debug", "Using encoding '%s' for database connection", charset);
 322         local ok, err = self:transaction(function() return self:execute(set_names_query:format(charset)); end);
 323         if not ok then
 324                 return ok, err;
 325         end
 326
 327         if driver == "MySQL" then
 328                 local ok, actual_charset = self:transaction(function ()
 329                         return self:select"SHOW SESSION VARIABLES LIKE 'character_set_client'";
 330                 end);
 331                 local charset_ok;
 332                 for row in actual_charset do
 333                         if row[2] ~= charset then
 334                                 log("error", "MySQL %s is actually %q (expected %q)", row[1], row[2], charset);
 335                                 charset_ok = false;
 336                         end
 337                 end
 338                 if not charset_ok then
 339                         return false, "Failed to set connection encoding";
 340                 end
 341         end
 342
 343         return true;
 344 end
 345 local engine_mt = { __index = engine };
 346
 347 local function db2uri(params)
 348         return build_url{
 349                 scheme = params.driver,
 350                 user = params.username,
 351                 password = params.password,
 352                 host = params.host,
 353                 port = params.port,
 354                 path = params.database,
 355         };
 356 end
 357
 358 local function create_engine(self, params, onconnect)
 359         return setmetatable({ url = db2uri(params), params = params, onconnect = onconnect }, engine_mt);
 360 end
 361
 362 return {
 363         is_column = is_column;
 364         is_index = is_index;
 365         is_table = is_table;
 366         is_query = is_query;
 367         Integer = Integer;
 368         String = String;
 369         Column = Column;
 370         Table = Table;
 371         Index = Index;
 372         create_engine = create_engine;
 373         db2uri = db2uri;
 374 };