prosody
7a5ddbaf758d
util/sql.lua

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

Software / code / prosody

Comparison

util/sql.lua @ 6054:7a5ddbaf758d

Merge 0.9->0.10
author Matthew Wild <mwild1@gmail.com>
date Wed, 02 Apr 2014 17:41:38 +0100
parent 5919:d1cc67ed0767
child 6532:a966efeb6cb3
comparison
equal deleted inserted replaced
6053:2f93a04564b2 6054:7a5ddbaf758d
43 __lt = function(a, b) return "("..a.."<"..b..")" end; 43 __lt = function(a, b) return "("..a.."<"..b..")" end;
44 __le = function(a, b) return "("..a.."<="..b..")" end; 44 __le = function(a, b) return "("..a.."<="..b..")" end;
45 }; 45 };
46 46
47 local functions = { 47 local functions = {
48 48
49 }; 49 };
50 50
51 local cmap = { 51 local cmap = {
52 [Integer] = Integer(); 52 [Integer] = Integer();
53 [String] = String(); 53 [String] = String();
175 if not success then return success, err; end 175 if not success then return success, err; end
176 return stmt; 176 return stmt;
177 end 177 end
178 178
179 local result_mt = { __index = { 179 local result_mt = { __index = {
180 affected = function(self) return self.__affected; end; 180 affected = function(self) return self.__stmt:affected(); end;
181 rowcount = function(self) return self.__rowcount; end; 181 rowcount = function(self) return self.__stmt:rowcount(); end;
182 } }; 182 } };
183 183
184 function engine:execute_query(sql, ...) 184 function engine:execute_query(sql, ...)
185 if self.params.driver == "PostgreSQL" then 185 if self.params.driver == "PostgreSQL" then
186 sql = sql:gsub("`", "\""); 186 sql = sql:gsub("`", "\"");
198 if not stmt then 198 if not stmt then
199 stmt = assert(self.conn:prepare(sql)); 199 stmt = assert(self.conn:prepare(sql));
200 prepared[sql] = stmt; 200 prepared[sql] = stmt;
201 end 201 end
202 assert(stmt:execute(...)); 202 assert(stmt:execute(...));
203 return setmetatable({ __affected = stmt:affected(), __rowcount = stmt:rowcount() }, result_mt); 203 return setmetatable({ __stmt = stmt }, result_mt);
204 end 204 end
205 engine.insert = engine.execute_update; 205 engine.insert = engine.execute_update;
206 engine.select = engine.execute_query; 206 engine.select = engine.execute_query;
207 engine.delete = engine.execute_update; 207 engine.delete = engine.execute_update;
208 engine.update = engine.execute_update; 208 engine.update = engine.execute_update;
249 if self.params.driver == "PostgreSQL" then 249 if self.params.driver == "PostgreSQL" then
250 sql = sql:gsub("`", "\""); 250 sql = sql:gsub("`", "\"");
251 elseif self.params.driver == "MySQL" then 251 elseif self.params.driver == "MySQL" then
252 sql = sql:gsub("`([,)])", "`(20)%1"); 252 sql = sql:gsub("`([,)])", "`(20)%1");
253 end 253 end
254 if index.unique then
255 sql = sql:gsub("^CREATE", "CREATE UNIQUE");
256 end
254 --print(sql); 257 --print(sql);
255 return self:execute(sql); 258 return self:execute(sql);
256 end 259 end
257 function engine:_create_table(table) 260 function engine:_create_table(table)
258 local sql = "CREATE TABLE `"..table.name.."` ("; 261 local sql = "CREATE TABLE `"..table.name.."` (";
259 for i,col in ipairs(table.c) do 262 for i,col in ipairs(table.c) do
260 sql = sql.."`"..col.name.."` "..col.type; 263 local col_type = col.type;
264 if col_type == "MEDIUMTEXT" and self.params.driver ~= "MySQL" then
265 col_type = "TEXT"; -- MEDIUMTEXT is MySQL-specific
266 end
267 if col.auto_increment == true and self.params.driver == "PostgreSQL" then
268 col_type = "BIGSERIAL";
269 end
270 sql = sql.."`"..col.name.."` "..col_type;
261 if col.nullable == false then sql = sql.." NOT NULL"; end 271 if col.nullable == false then sql = sql.." NOT NULL"; end
272 if col.primary_key == true then sql = sql.." PRIMARY KEY"; end
273 if col.auto_increment == true then
274 if self.params.driver == "MySQL" then
275 sql = sql.." AUTO_INCREMENT";
276 elseif self.params.driver == "SQLite3" then
277 sql = sql.." AUTOINCREMENT";
278 end
279 end
262 if i ~= #table.c then sql = sql..", "; end 280 if i ~= #table.c then sql = sql..", "; end
263 end 281 end
264 sql = sql.. ");" 282 sql = sql.. ");"
265 if self.params.driver == "PostgreSQL" then 283 if self.params.driver == "PostgreSQL" then
266 sql = sql:gsub("`", "\""); 284 sql = sql:gsub("`", "\"");
285 elseif self.params.driver == "MySQL" then
286 sql = sql:gsub(";$", " CHARACTER SET 'utf8' COLLATE 'utf8_bin';");
267 end 287 end
268 local success,err = self:execute(sql); 288 local success,err = self:execute(sql);
269 if not success then return success,err; end 289 if not success then return success,err; end
270 for i,v in ipairs(table.__table__) do 290 for i,v in ipairs(table.__table__) do
271 if is_index(v) then 291 if is_index(v) then
272 self:_create_index(v); 292 self:_create_index(v);
273 end 293 end
274 end 294 end
275 return success; 295 return success;
296 end
297 function engine:set_encoding() -- to UTF-8
298 local driver = self.params.driver;
299 if driver == "SQLite3" then
300 return self:transaction(function()
301 if self:select"PRAGMA encoding;"()[1] == "UTF-8" then
302 self.charset = "utf8";
303 end
304 end);
305 end
306 local set_names_query = "SET NAMES '%s';"
307 local charset = "utf8";
308 if driver == "MySQL" then
309 set_names_query = set_names_query:gsub(";$", " COLLATE 'utf8_bin';");
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 end
316 self.charset = charset;
317 return self:transaction(function() return self:execute(set_names_query:format(charset)); end);
276 end 318 end
277 local engine_mt = { __index = engine }; 319 local engine_mt = { __index = engine };
278 320
279 local function db2uri(params) 321 local function db2uri(params)
280 return build_url{ 322 return build_url{