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 (2013-04-18) |
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; |