prosody
9916f0a2d178
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 @ 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
author Matthew Wild <mwild1@gmail.com>
date Thu, 18 Apr 2013 11:13:40 +0100
child 5739:2e9ad9cb206f
comparison
equal deleted inserted replaced
5492:5d0cc5de5c51 5494:9916f0a2d178
1
2 local setmetatable, getmetatable = setmetatable, getmetatable;
3 local ipairs, unpack, select = ipairs, unpack, select;
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 module("sql")
17
18 local column_mt = {};
19 local table_mt = {};
20 local query_mt = {};
21 --local op_mt = {};
22 local index_mt = {};
23
24 function is_column(x) return getmetatable(x)==column_mt; end
25 function is_index(x) return getmetatable(x)==index_mt; end
26 function is_table(x) return getmetatable(x)==table_mt; end
27 function is_query(x) return getmetatable(x)==query_mt; end
28 --function is_op(x) return getmetatable(x)==op_mt; end
29 --function expr(...) return setmetatable({...}, op_mt); end
30 function Integer(n) return "Integer()" end
31 function String(n) return "String()" end
32
33 --[[local ops = {
34 __add = function(a, b) return "("..a.."+"..b..")" end;
35 __sub = function(a, b) return "("..a.."-"..b..")" end;
36 __mul = function(a, b) return "("..a.."*"..b..")" end;
37 __div = function(a, b) return "("..a.."/"..b..")" end;
38 __mod = function(a, b) return "("..a.."%"..b..")" end;
39 __pow = function(a, b) return "POW("..a..","..b..")" end;
40 __unm = function(a) return "NOT("..a..")" end;
41 __len = function(a) return "COUNT("..a..")" end;
42 __eq = 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;
45 };
46
47 local functions = {
48
49 };
50
51 local cmap = {
52 [Integer] = Integer();
53 [String] = String();
54 };]]
55
56 function Column(definition)
57 return setmetatable(definition, column_mt);
58 end
59 function Table(definition)
60 local c = {}
61 for i,col in ipairs(definition) do
62 if is_column(col) then
63 c[i], c[col.name] = col, col;
64 elseif is_index(col) then
65 col.table = definition.name;
66 end
67 end
68 return setmetatable({ __table__ = definition, c = c, name = definition.name }, table_mt);
69 end
70 function Index(definition)
71 return setmetatable(definition, index_mt);
72 end
73
74 function table_mt:__tostring()
75 local s = { 'name="'..self.__table__.name..'"' }
76 for i,col in ipairs(self.__table__) do
77 s[#s+1] = tostring(col);
78 end
79 return 'Table{ '..t_concat(s, ", ")..' }'
80 end
81 table_mt.__index = {};
82 function table_mt.__index:create(engine)
83 return engine:_create_table(self);
84 end
85 function table_mt:__call(...)
86 -- TODO
87 end
88 function column_mt:__tostring()
89 return 'Column{ name="'..self.name..'", type="'..self.type..'" }'
90 end
91 function index_mt:__tostring()
92 local s = 'Index{ name="'..self.name..'"';
93 for i=1,#self do s = s..', "'..self[i]:gsub("[\\\"]", "\\%1")..'"'; end
94 return s..' }';
95 -- return 'Index{ name="'..self.name..'", type="'..self.type..'" }'
96 end
97 --
98
99 local function urldecode(s) return s and (s:gsub("%%(%x%x)", function (c) return s_char(tonumber(c,16)); end)); end
100 local function parse_url(url)
101 local scheme, secondpart, database = url:match("^([%w%+]+)://([^/]*)/?(.*)");
102 assert(scheme, "Invalid URL format");
103 local username, password, host, port;
104 local authpart, hostpart = secondpart:match("([^@]+)@([^@+])");
105 if not authpart then hostpart = secondpart; end
106 if authpart then
107 username, password = authpart:match("([^:]*):(.*)");
108 username = username or authpart;
109 password = password and urldecode(password);
110 end
111 if hostpart then
112 host, port = hostpart:match("([^:]*):(.*)");
113 host = host or hostpart;
114 port = port and assert(tonumber(port), "Invalid URL format");
115 end
116 return {
117 scheme = scheme:lower();
118 username = username; password = password;
119 host = host; port = port;
120 database = #database > 0 and database or nil;
121 };
122 end
123
124 --[[local session = {};
125
126 function session.query(...)
127 local rets = {...};
128 local query = setmetatable({ __rets = rets, __filters }, query_mt);
129 return query;
130 end
131 --
132
133 local function db2uri(params)
134 return build_url{
135 scheme = params.driver,
136 user = params.username,
137 password = params.password,
138 host = params.host,
139 port = params.port,
140 path = params.database,
141 };
142 end]]
143
144 local engine = {};
145 function engine:connect()
146 if self.conn then return true; end
147
148 local params = self.params;
149 assert(params.driver, "no driver")
150 local dbh, err = DBI.Connect(
151 params.driver, params.database,
152 params.username, params.password,
153 params.host, params.port
154 );
155 if not dbh then return nil, err; end
156 dbh:autocommit(false); -- don't commit automatically
157 self.conn = dbh;
158 self.prepared = {};
159 return true;
160 end
161 function engine:execute(sql, ...)
162 local success, err = self:connect();
163 if not success then return success, err; end
164 local prepared = self.prepared;
165
166 local stmt = prepared[sql];
167 if not stmt then
168 local err;
169 stmt, err = self.conn:prepare(sql);
170 if not stmt then return stmt, err; end
171 prepared[sql] = stmt;
172 end
173
174 local success, err = stmt:execute(...);
175 if not success then return success, err; end
176 return stmt;
177 end
178
179 local result_mt = { __index = {
180 affected = function(self) return self.__affected; end;
181 rowcount = function(self) return self.__rowcount; end;
182 } };
183
184 function engine:execute_query(sql, ...)
185 if self.params.driver == "PostgreSQL" then
186 sql = sql:gsub("`", "\"");
187 end
188 local stmt = assert(self.conn:prepare(sql));
189 assert(stmt:execute(...));
190 return stmt:rows();
191 end
192 function engine:execute_update(sql, ...)
193 if self.params.driver == "PostgreSQL" then
194 sql = sql:gsub("`", "\"");
195 end
196 local prepared = self.prepared;
197 local stmt = prepared[sql];
198 if not stmt then
199 stmt = assert(self.conn:prepare(sql));
200 prepared[sql] = stmt;
201 end
202 assert(stmt:execute(...));
203 return setmetatable({ __affected = stmt:affected(), __rowcount = stmt:rowcount() }, result_mt);
204 end
205 engine.insert = engine.execute_update;
206 engine.select = engine.execute_query;
207 engine.delete = engine.execute_update;
208 engine.update = engine.execute_update;
209 function engine:_transaction(func, ...)
210 if not self.conn then
211 local a,b = self:connect();
212 if not a then return a,b; end
213 end
214 --assert(not self.__transaction, "Recursive transactions not allowed");
215 local args, n_args = {...}, select("#", ...);
216 local function f() return func(unpack(args, 1, n_args)); end
217 self.__transaction = true;
218 local success, a, b, c = xpcall(f, debug_traceback);
219 self.__transaction = nil;
220 if success then
221 log("debug", "SQL transaction success [%s]", tostring(func));
222 local ok, err = self.conn:commit();
223 if not ok then return ok, err; end -- commit failed
224 return success, a, b, c;
225 else
226 log("debug", "SQL transaction failure [%s]: %s", tostring(func), a);
227 if self.conn then self.conn:rollback(); end
228 return success, a;
229 end
230 end
231 function engine:transaction(...)
232 local a,b = self:_transaction(...);
233 if not a then
234 local conn = self.conn;
235 if not conn or not conn:ping() then
236 self.conn = nil;
237 a,b = self:_transaction(...);
238 end
239 end
240 return a,b;
241 end
242 function engine:_create_index(index)
243 local sql = "CREATE INDEX `"..index.name.."` ON `"..index.table.."` (";
244 for i=1,#index do
245 sql = sql.."`"..index[i].."`";
246 if i ~= #index then sql = sql..", "; end
247 end
248 sql = sql..");"
249 if self.params.driver == "PostgreSQL" then
250 sql = sql:gsub("`", "\"");
251 elseif self.params.driver == "MySQL" then
252 sql = sql:gsub("`([,)])", "`(20)%1");
253 end
254 --print(sql);
255 return self:execute(sql);
256 end
257 function engine:_create_table(table)
258 local sql = "CREATE TABLE `"..table.name.."` (";
259 for i,col in ipairs(table.c) do
260 sql = sql.."`"..col.name.."` "..col.type;
261 if col.nullable == false then sql = sql.." NOT NULL"; end
262 if i ~= #table.c then sql = sql..", "; end
263 end
264 sql = sql.. ");"
265 if self.params.driver == "PostgreSQL" then
266 sql = sql:gsub("`", "\"");
267 end
268 local success,err = self:execute(sql);
269 if not success then return success,err; end
270 for i,v in ipairs(table.__table__) do
271 if is_index(v) then
272 self:_create_index(v);
273 end
274 end
275 return success;
276 end
277 local engine_mt = { __index = engine };
278
279 local function db2uri(params)
280 return build_url{
281 scheme = params.driver,
282 user = params.username,
283 password = params.password,
284 host = params.host,
285 port = params.port,
286 path = params.database,
287 };
288 end
289 local engine_cache = {}; -- TODO make weak valued
290 function create_engine(self, params)
291 local url = db2uri(params);
292 if not engine_cache[url] then
293 local engine = setmetatable({ url = url, params = params }, engine_mt);
294 engine_cache[url] = engine;
295 end
296 return engine_cache[url];
297 end
298
299
300 --[[Users = Table {
301 name="users";
302 Column { name="user_id", type=String(), primary_key=true };
303 };
304 print(Users)
305 print(Users.c.user_id)]]
306
307 --local engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase');
308 --[[local engine = create_engine{ driver = "SQLite3", database = "./alchemy.sqlite" };
309
310 local i = 0;
311 for row in assert(engine:execute("select * from sqlite_master")):rows(true) do
312 i = i+1;
313 print(i);
314 for k,v in pairs(row) do
315 print("",k,v);
316 end
317 end
318 print("---")
319
320 Prosody = Table {
321 name="prosody";
322 Column { name="host", type="TEXT", nullable=false };
323 Column { name="user", type="TEXT", nullable=false };
324 Column { name="store", type="TEXT", nullable=false };
325 Column { name="key", type="TEXT", nullable=false };
326 Column { name="type", type="TEXT", nullable=false };
327 Column { name="value", type="TEXT", nullable=false };
328 Index { name="prosody_index", "host", "user", "store", "key" };
329 };
330 --print(Prosody);
331 assert(engine:transaction(function()
332 assert(Prosody:create(engine));
333 end));
334
335 for row in assert(engine:execute("select user from prosody")):rows(true) do
336 print("username:", row['username'])
337 end
338 --result.close();]]
339
340 return _M;