rev |
line source |
jbe/bsw@0
|
1 #!/usr/bin/env lua
|
jbe/bsw@0
|
2
|
jbe/bsw@0
|
3
|
jbe/bsw@0
|
4 ---------------------------
|
jbe/bsw@0
|
5 -- module initialization --
|
jbe/bsw@0
|
6 ---------------------------
|
jbe/bsw@0
|
7
|
jbe/bsw@0
|
8 local _G = _G
|
jbe/bsw@0
|
9 local _VERSION = _VERSION
|
jbe/bsw@0
|
10 local assert = assert
|
jbe/bsw@0
|
11 local error = error
|
jbe/bsw@0
|
12 local getmetatable = getmetatable
|
jbe/bsw@0
|
13 local ipairs = ipairs
|
jbe/bsw@0
|
14 local next = next
|
jbe/bsw@0
|
15 local pairs = pairs
|
jbe/bsw@0
|
16 local print = print
|
jbe/bsw@0
|
17 local rawequal = rawequal
|
jbe/bsw@0
|
18 local rawget = rawget
|
jbe@64
|
19 local rawlen = rawlen
|
jbe/bsw@0
|
20 local rawset = rawset
|
jbe/bsw@0
|
21 local select = select
|
jbe/bsw@0
|
22 local setmetatable = setmetatable
|
jbe/bsw@0
|
23 local tonumber = tonumber
|
jbe/bsw@0
|
24 local tostring = tostring
|
jbe/bsw@0
|
25 local type = type
|
jbe/bsw@0
|
26
|
jbe/bsw@0
|
27 local math = math
|
jbe/bsw@0
|
28 local string = string
|
jbe/bsw@0
|
29 local table = table
|
jbe/bsw@0
|
30
|
jbe/bsw@0
|
31 local add = table.insert
|
jbe/bsw@0
|
32
|
jbe@64
|
33 local _M = require("mondelefant_native")
|
jbe@64
|
34 if _ENV then
|
jbe@64
|
35 _ENV = _M
|
jbe@64
|
36 else
|
jbe@64
|
37 _G[...] = _M
|
jbe@64
|
38 setfenv(1, _M)
|
jbe@64
|
39 end
|
jbe/bsw@0
|
40
|
jbe@421
|
41 -- TODO: remove following downward-compatibility code
|
jbe@421
|
42 -- for mondelefant.connect{...} function:
|
jbe@421
|
43
|
jbe@421
|
44 do
|
jbe@421
|
45 local original_connect_function = connect
|
jbe@421
|
46 function connect(args)
|
jbe@421
|
47 if args.engine == "postgresql" then
|
jbe@421
|
48 local newargs = {}
|
jbe@421
|
49 for k, v in pairs(args) do
|
jbe@421
|
50 if k ~= "engine" then newargs[k] = v end
|
jbe@421
|
51 end
|
jbe@421
|
52 return original_connect_function(newargs)
|
jbe@421
|
53 else
|
jbe@421
|
54 return original_connect_function(args)
|
jbe@421
|
55 end
|
jbe@421
|
56 end
|
jbe@421
|
57 end
|
jbe@421
|
58
|
jbe/bsw@0
|
59
|
jbe/bsw@0
|
60
|
jbe/bsw@0
|
61 ---------------
|
jbe/bsw@0
|
62 -- selectors --
|
jbe/bsw@0
|
63 ---------------
|
jbe/bsw@0
|
64
|
jbe/bsw@0
|
65 selector_metatable = {}
|
jbe/bsw@0
|
66 selector_prototype = {}
|
jbe/bsw@0
|
67 selector_metatable.__index = selector_prototype
|
jbe/bsw@0
|
68
|
jbe/bsw@0
|
69 local function init_selector(self, db_conn)
|
jbe/bsw@0
|
70 self._db_conn = db_conn
|
jbe/bsw@0
|
71 self._mode = "list"
|
jbe@39
|
72 self._with = { sep = ", " }
|
jbe/bsw@0
|
73 self._fields = { sep = ", " }
|
jbe/bsw@0
|
74 self._distinct = false
|
jbe/bsw@0
|
75 self._distinct_on = {sep = ", ", expression}
|
jbe/bsw@0
|
76 self._from = { sep = " " }
|
jbe/bsw@4
|
77 self._where = { sep = ") AND (" }
|
jbe/bsw@0
|
78 self._group_by = { sep = ", " }
|
jbe/bsw@4
|
79 self._having = { sep = ") AND (" }
|
jbe/bsw@0
|
80 self._combine = { sep = " " }
|
jbe/bsw@0
|
81 self._order_by = { sep = ", " }
|
jbe/bsw@0
|
82 self._limit = nil
|
jbe/bsw@0
|
83 self._offset = nil
|
jbe/bsw@4
|
84 self._read_lock = { sep = ", " }
|
jbe/bsw@4
|
85 self._write_lock = { sep = ", " }
|
jbe/bsw@0
|
86 self._class = nil
|
jbe/bsw@0
|
87 self._attach = nil
|
jbe/bsw@0
|
88 return self
|
jbe/bsw@0
|
89 end
|
jbe/bsw@0
|
90
|
jbe@23
|
91 --[[--
|
jbe@23
|
92 selector = -- new selector
|
jbe@23
|
93 <db_handle>:new_selector()
|
jbe@23
|
94
|
jbe@23
|
95 Creates a new selector to operate on the given database handle.
|
jbe@23
|
96 --]]--
|
jbe/bsw@0
|
97 function connection_prototype:new_selector()
|
jbe/bsw@0
|
98 return init_selector(setmetatable({}, selector_metatable), self)
|
jbe/bsw@0
|
99 end
|
jbe@23
|
100 --//--
|
jbe/bsw@0
|
101
|
jbe@23
|
102 --[[--
|
jbe@23
|
103 db_handle = -- handle of database connection
|
jbe@23
|
104 <db_selector>:get_db_conn()
|
jbe@23
|
105
|
jbe@23
|
106 Returns the database connection handle used by a selector.
|
jbe@23
|
107
|
jbe@23
|
108 --]]--
|
jbe/bsw@0
|
109 function selector_prototype:get_db_conn()
|
jbe/bsw@0
|
110 return self._db_conn
|
jbe/bsw@0
|
111 end
|
jbe@23
|
112 --//--
|
jbe/bsw@0
|
113
|
jbe/bsw@0
|
114 -- TODO: selector clone?
|
jbe/bsw@0
|
115
|
jbe@23
|
116 --[[--
|
jbe@23
|
117 db_selector = -- same selector returned
|
jbe@23
|
118 <db_selector>:single_object_mode()
|
jbe@23
|
119
|
jbe@23
|
120 Sets selector to single object mode (mode "object" passed to "query" method of database handle). The selector is modified and returned.
|
jbe@23
|
121
|
jbe@23
|
122 --]]--
|
jbe/bsw@0
|
123 function selector_prototype:single_object_mode()
|
jbe/bsw@0
|
124 self._mode = "object"
|
jbe/bsw@0
|
125 return self
|
jbe/bsw@0
|
126 end
|
jbe@23
|
127 --//--
|
jbe/bsw@0
|
128
|
jbe@23
|
129 --[[--
|
jbe@23
|
130 db_selector = -- same selector returned
|
jbe@23
|
131 <db_selector>:optional_object_mode()
|
jbe@23
|
132
|
jbe@23
|
133 Sets selector to single object mode (mode "opt_object" passed to "query" method of database handle). The selector is modified and returned.
|
jbe@23
|
134
|
jbe@23
|
135 --]]--
|
jbe/bsw@0
|
136 function selector_prototype:optional_object_mode()
|
jbe/bsw@0
|
137 self._mode = "opt_object"
|
jbe/bsw@0
|
138 return self
|
jbe/bsw@0
|
139 end
|
jbe@23
|
140 --//--
|
jbe/bsw@0
|
141
|
jbe@23
|
142 --[[--
|
jbe@23
|
143 db_selector = -- same selector returned
|
jbe@23
|
144 <db_selector>:empty_list_mode()
|
jbe@23
|
145
|
jbe@23
|
146 Sets selector to empty list mode. The selector is modified and returned. When using the selector, no SQL query will be issued, but instead an empty database result list is returned.
|
jbe@23
|
147
|
jbe@23
|
148 --]]--
|
jbe/bsw@0
|
149 function selector_prototype:empty_list_mode()
|
jbe/bsw@0
|
150 self._mode = "empty_list"
|
jbe/bsw@0
|
151 return self
|
jbe/bsw@0
|
152 end
|
jbe@23
|
153 --//--
|
jbe/bsw@0
|
154
|
jbe@23
|
155 --[[--
|
jbe@39
|
156 db_selector =
|
jbe@39
|
157 <db_selector>:add_with(
|
jbe@39
|
158 expression = expression,
|
jbe@39
|
159 selector = selector
|
jbe@39
|
160 )
|
jbe@39
|
161
|
jbe@39
|
162 Adds an WITH RECURSIVE expression to the selector. The selector is modified and returned.
|
jbe@39
|
163 --]]--
|
jbe@39
|
164 function selector_prototype:add_with(expression, selector)
|
jbe@39
|
165 add(self._with, {"$ AS ($)", {expression}, {selector}})
|
jbe@39
|
166 return self
|
jbe@39
|
167 end
|
jbe@39
|
168 --//--
|
jbe@39
|
169
|
jbe@39
|
170 --[[--
|
jbe@23
|
171 db_selector = -- same selector returned
|
jbe@23
|
172 <db_selector>:add_distinct_on(
|
jbe@23
|
173 expression -- expression as passed to "assemble_command"
|
jbe@23
|
174 )
|
jbe@23
|
175
|
jbe@23
|
176 Adds an DISTINCT ON expression to the selector. The selector is modified and returned.
|
jbe@23
|
177
|
jbe@23
|
178 --]]--
|
jbe/bsw@0
|
179 function selector_prototype:add_distinct_on(expression)
|
jbe/bsw@0
|
180 if self._distinct then
|
jbe/bsw@0
|
181 error("Can not combine DISTINCT with DISTINCT ON.")
|
jbe/bsw@0
|
182 end
|
jbe/bsw@0
|
183 add(self._distinct_on, expression)
|
jbe/bsw@0
|
184 return self
|
jbe/bsw@0
|
185 end
|
jbe@23
|
186 --//--
|
jbe/bsw@0
|
187
|
jbe@23
|
188 --[[--
|
jbe@23
|
189 db_selector = -- same selector returned
|
jbe@23
|
190 <db_selector>:set_distinct()
|
jbe@23
|
191
|
jbe@23
|
192 Sets selector to perform a SELECT DISTINCT instead of SELECT (ALL). The selector is modified and returned. This mode can not be combined with DISTINCT ON.
|
jbe@23
|
193
|
jbe@23
|
194 --]]--
|
jbe/bsw@0
|
195 function selector_prototype:set_distinct()
|
jbe/bsw@0
|
196 if #self._distinct_on > 0 then
|
jbe/bsw@0
|
197 error("Can not combine DISTINCT with DISTINCT ON.")
|
jbe/bsw@0
|
198 end
|
jbe/bsw@0
|
199 self._distinct = true
|
jbe/bsw@0
|
200 return self
|
jbe/bsw@0
|
201 end
|
jbe@23
|
202 --//--
|
jbe/bsw@0
|
203
|
jbe@23
|
204 --[[--
|
jbe@23
|
205 db_selector = -- same selector returned
|
jbe@23
|
206 <db_selector>:add_from(
|
jbe@23
|
207 expression, -- expression as passed to "assemble_command"
|
jbe@23
|
208 alias, -- optional alias expression as passed to "assemble_command"
|
jbe@23
|
209 condition -- optional condition expression as passed to "assemble_command"
|
jbe@23
|
210 )
|
jbe@23
|
211
|
jbe@23
|
212 Adds expressions for FROM clause to the selector. The selector is modified and returned. If an additional condition is given, an INNER JOIN will be used, otherwise a CROSS JOIN.
|
jbe@23
|
213
|
jbe@23
|
214 This method is identical to "join".
|
jbe@23
|
215
|
jbe@23
|
216 --]]--
|
jbe/bsw@0
|
217 function selector_prototype:add_from(expression, alias, condition)
|
jbe/bsw@0
|
218 local first = (#self._from == 0)
|
jbe/bsw@0
|
219 if not first then
|
jbe/bsw@0
|
220 if condition then
|
jbe/bsw@0
|
221 add(self._from, "INNER JOIN")
|
jbe/bsw@0
|
222 else
|
jbe/bsw@0
|
223 add(self._from, "CROSS JOIN")
|
jbe/bsw@0
|
224 end
|
jbe/bsw@0
|
225 end
|
jbe/bsw@0
|
226 if getmetatable(expression) == selector_metatable then
|
jbe/bsw@0
|
227 if alias then
|
jbe/bsw@0
|
228 add(self._from, {'($) AS "$"', {expression}, {alias}})
|
jbe/bsw@0
|
229 else
|
jbe/bsw@0
|
230 add(self._from, {'($) AS "subquery"', {expression}})
|
jbe/bsw@0
|
231 end
|
jbe/bsw@0
|
232 else
|
jbe/bsw@0
|
233 if alias then
|
jbe/bsw@0
|
234 add(self._from, {'$ AS "$"', {expression}, {alias}})
|
jbe/bsw@0
|
235 else
|
jbe/bsw@0
|
236 add(self._from, expression)
|
jbe/bsw@0
|
237 end
|
jbe/bsw@0
|
238 end
|
jbe/bsw@0
|
239 if condition then
|
jbe/bsw@0
|
240 if first then
|
jbe@55
|
241 self:add_where(condition)
|
jbe/bsw@0
|
242 else
|
jbe/bsw@0
|
243 add(self._from, "ON")
|
jbe/bsw@0
|
244 add(self._from, condition)
|
jbe/bsw@0
|
245 end
|
jbe/bsw@0
|
246 end
|
jbe/bsw@0
|
247 return self
|
jbe/bsw@0
|
248 end
|
jbe@23
|
249 --//--
|
jbe/bsw@0
|
250
|
jbe@23
|
251 --[[--
|
jbe@23
|
252 db_selector = -- same selector returned
|
jbe@23
|
253 <db_selector>:add_where(
|
jbe@23
|
254 expression -- expression as passed to "assemble_command"
|
jbe@23
|
255 )
|
jbe@23
|
256
|
jbe@23
|
257 Adds expressions for WHERE clause to the selector. The selector is modified and returned. Multiple calls cause expressions to be AND-combined.
|
jbe@23
|
258
|
jbe@23
|
259 --]]--
|
jbe/bsw@0
|
260 function selector_prototype:add_where(expression)
|
jbe/bsw@0
|
261 add(self._where, expression)
|
jbe/bsw@0
|
262 return self
|
jbe/bsw@0
|
263 end
|
jbe@23
|
264 --//--
|
jbe/bsw@0
|
265
|
jbe@23
|
266 --[[--
|
jbe@23
|
267 db_selector = -- same selector returned
|
jbe@23
|
268 <db_selector>:add_group_by(
|
jbe@23
|
269 expression -- expression as passed to "assemble_command"
|
jbe@23
|
270 )
|
jbe@23
|
271
|
jbe@23
|
272 Adds expressions for GROUP BY clause to the selector. The selector is modified and returned.
|
jbe@23
|
273
|
jbe@23
|
274 --]]--
|
jbe/bsw@0
|
275 function selector_prototype:add_group_by(expression)
|
jbe/bsw@0
|
276 add(self._group_by, expression)
|
jbe/bsw@0
|
277 return self
|
jbe/bsw@0
|
278 end
|
jbe@23
|
279 --//--
|
jbe/bsw@0
|
280
|
jbe@23
|
281 --[[--
|
jbe@23
|
282 db_selector = -- same selector returned
|
jbe@23
|
283 <db_selector>:add_having(
|
jbe@23
|
284 expression -- expression as passed to "assemble_command"
|
jbe@23
|
285 )
|
jbe@23
|
286
|
jbe@23
|
287 Adds expressions for HAVING clause to the selector. The selector is modified and returned. Multiple calls cause expressions to be AND-combined.
|
jbe@23
|
288
|
jbe@23
|
289 --]]--
|
jbe/bsw@0
|
290 function selector_prototype:add_having(expression)
|
jbe/bsw@0
|
291 add(self._having, expression)
|
jbe/bsw@0
|
292 return self
|
jbe/bsw@0
|
293 end
|
jbe@23
|
294 --//--
|
jbe/bsw@0
|
295
|
jbe@23
|
296 --[[--
|
jbe@23
|
297 db_selector = -- same selector returned
|
jbe@23
|
298 <db_selector>:add_combine(
|
jbe@23
|
299 expression -- expression as passed to "assemble_command"
|
jbe@23
|
300 )
|
jbe@23
|
301
|
jbe@23
|
302 This function is used for UNION/INTERSECT/EXCEPT clauses. It does not need to be called directly. Use "union", "union_all", "intersect", "intersect_all", "except" and "except_all" instead.
|
jbe@23
|
303
|
jbe@23
|
304 --]]--
|
jbe/bsw@0
|
305 function selector_prototype:add_combine(expression)
|
jbe/bsw@0
|
306 add(self._combine, expression)
|
jbe/bsw@0
|
307 return self
|
jbe/bsw@0
|
308 end
|
jbe@23
|
309 --//--
|
jbe/bsw@0
|
310
|
jbe@23
|
311 --[[--
|
jbe@23
|
312 db_selector = -- same selector returned
|
jbe@23
|
313 <db_selector>:add_order_by(
|
jbe@23
|
314 expression -- expression as passed to "assemble_command"
|
jbe@23
|
315 )
|
jbe@23
|
316
|
jbe@23
|
317 Adds expressions for ORDER BY clause to the selector. The selector is modified and returned.
|
jbe@23
|
318
|
jbe@23
|
319 --]]--
|
jbe/bsw@0
|
320 function selector_prototype:add_order_by(expression)
|
jbe/bsw@0
|
321 add(self._order_by, expression)
|
jbe/bsw@0
|
322 return self
|
jbe/bsw@0
|
323 end
|
jbe@23
|
324 --//--
|
jbe/bsw@0
|
325
|
jbe@23
|
326 --[[--
|
jbe@23
|
327 db_selector = -- same selector returned
|
jbe@23
|
328 <db_selector>:limit(
|
jbe@23
|
329 count -- integer used as LIMIT
|
jbe@23
|
330 )
|
jbe@23
|
331
|
jbe@23
|
332 Limits the number of rows to a given number, by using LIMIT. The selector is modified and returned.
|
jbe@23
|
333
|
jbe@23
|
334 --]]--
|
jbe/bsw@0
|
335 function selector_prototype:limit(count)
|
jbe/bsw@0
|
336 if type(count) ~= "number" or count % 1 ~= 0 then
|
jbe/bsw@0
|
337 error("LIMIT must be an integer.")
|
jbe/bsw@0
|
338 end
|
jbe/bsw@0
|
339 self._limit = count
|
jbe/bsw@0
|
340 return self
|
jbe/bsw@0
|
341 end
|
jbe@23
|
342 --//--
|
jbe/bsw@0
|
343
|
jbe@23
|
344 --[[--
|
jbe@23
|
345 db_selector = -- same selector returned
|
jbe@23
|
346 <db_selector>:offset(
|
jbe@23
|
347 count -- integer used as OFFSET
|
jbe@23
|
348 )
|
jbe@23
|
349
|
jbe@23
|
350 Skips a given number of rows, by using OFFSET. The selector is modified and returned.
|
jbe@23
|
351
|
jbe@23
|
352 --]]--
|
jbe/bsw@0
|
353 function selector_prototype:offset(count)
|
jbe/bsw@0
|
354 if type(count) ~= "number" or count % 1 ~= 0 then
|
jbe/bsw@0
|
355 error("OFFSET must be an integer.")
|
jbe/bsw@0
|
356 end
|
jbe/bsw@0
|
357 self._offset = count
|
jbe/bsw@0
|
358 return self
|
jbe/bsw@0
|
359 end
|
jbe@23
|
360 --//--
|
jbe/bsw@0
|
361
|
jbe@23
|
362 --[[--
|
jbe@23
|
363 db_selector = -- same selector returned
|
jbe@23
|
364 <db_selector>:for_share()
|
jbe@23
|
365
|
jbe@23
|
366 Adds FOR SHARE to the statement, to share-lock all rows read. The selector is modified and returned.
|
jbe@23
|
367
|
jbe@23
|
368 --]]--
|
jbe/bsw@4
|
369 function selector_prototype:for_share()
|
jbe/bsw@4
|
370 self._read_lock.all = true
|
jbe/bsw@4
|
371 return self
|
jbe/bsw@4
|
372 end
|
jbe@23
|
373 --//--
|
jbe/bsw@4
|
374
|
jbe@23
|
375 --[[--
|
jbe@23
|
376 db_selector = -- same selector returned
|
jbe@23
|
377 <db_selector>:for_share_of(
|
jbe@23
|
378 expression -- expression as passed to "assemble_command"
|
jbe@23
|
379 )
|
jbe@23
|
380
|
jbe@23
|
381 Adds FOR SHARE OF to the statement, to share-lock all rows read by the named table(s). The selector is modified and returned.
|
jbe@23
|
382
|
jbe@23
|
383 --]]--
|
jbe/bsw@4
|
384 function selector_prototype:for_share_of(expression)
|
jbe/bsw@4
|
385 add(self._read_lock, expression)
|
jbe/bsw@4
|
386 return self
|
jbe/bsw@4
|
387 end
|
jbe@23
|
388 --//--
|
jbe/bsw@4
|
389
|
jbe@23
|
390 --[[--
|
jbe@23
|
391 db_selector = -- same selector returned
|
jbe@23
|
392 <db_selector>:for_update()
|
jbe@23
|
393
|
jbe@23
|
394 Adds FOR UPDATE to the statement, to exclusivly lock all rows read. The selector is modified and returned.
|
jbe@23
|
395
|
jbe@23
|
396 --]]--
|
jbe/bsw@4
|
397 function selector_prototype:for_update()
|
jbe/bsw@4
|
398 self._write_lock.all = true
|
jbe/bsw@4
|
399 return self
|
jbe/bsw@4
|
400 end
|
jbe@23
|
401 --//--
|
jbe/bsw@4
|
402
|
jbe@23
|
403 --[[--
|
jbe@23
|
404 db_selector = -- same selector returned
|
jbe@23
|
405 <db_selector>:for_update_of(
|
jbe@23
|
406 expression -- expression as passed to "assemble_command"
|
jbe@23
|
407 )
|
jbe@23
|
408
|
jbe@23
|
409 Adds FOR SHARE OF to the statement, to exclusivly lock all rows read by the named table(s). The selector is modified and returned.
|
jbe@23
|
410
|
jbe@23
|
411 --]]--
|
jbe/bsw@4
|
412 function selector_prototype:for_update_of(expression)
|
jbe/bsw@4
|
413 add(self._write_lock, expression)
|
jbe/bsw@4
|
414 return self
|
jbe/bsw@4
|
415 end
|
jbe@23
|
416 --//--
|
jbe/bsw@4
|
417
|
jbe@23
|
418 --[[--
|
jbe@23
|
419 db_selector = -- same selector returned
|
jbe@23
|
420 <db_selector>:reset_fields()
|
jbe@23
|
421
|
jbe@23
|
422 This method removes all fields added by method "add_field". The selector is modified and returned.
|
jbe@23
|
423
|
jbe@23
|
424 --]]--
|
jbe/bsw@0
|
425 function selector_prototype:reset_fields()
|
jbe/bsw@0
|
426 for idx in ipairs(self._fields) do
|
jbe/bsw@0
|
427 self._fields[idx] = nil
|
jbe/bsw@0
|
428 end
|
jbe/bsw@0
|
429 return self
|
jbe/bsw@0
|
430 end
|
jbe@23
|
431 --//--
|
jbe/bsw@0
|
432
|
jbe@23
|
433 --[[--
|
jbe@23
|
434 db_selector = -- same selector returned
|
jbe@23
|
435 <db_selector>:add_field(
|
jbe@23
|
436 expression, -- expression as passed to "assemble_command"
|
jbe@23
|
437 alias, -- optional alias expression as passed to "assemble_command"
|
jbe@23
|
438 option_list -- optional list of options (may contain strings "distinct" or "grouped")
|
jbe@23
|
439 )
|
jbe@23
|
440
|
jbe@23
|
441 Adds fields to the selector. The selector is modified and returned. The third argument can be a list of options. If option "distinct" is given, then "add_distinct_on" will be executed for the given field or alias. If option "grouped" is given, then "add_group_by" will be executed for the given field or alias.
|
jbe@23
|
442
|
jbe@23
|
443 --]]--
|
jbe/bsw@0
|
444 function selector_prototype:add_field(expression, alias, options)
|
jbe/bsw@0
|
445 if alias then
|
jbe/bsw@0
|
446 add(self._fields, {'$ AS "$"', {expression}, {alias}})
|
jbe/bsw@0
|
447 else
|
jbe/bsw@0
|
448 add(self._fields, expression)
|
jbe/bsw@0
|
449 end
|
jbe/bsw@0
|
450 if options then
|
jbe/bsw@0
|
451 for i, option in ipairs(options) do
|
jbe/bsw@0
|
452 if option == "distinct" then
|
jbe/bsw@0
|
453 if alias then
|
jbe/bsw@0
|
454 self:add_distinct_on('"' .. alias .. '"')
|
jbe/bsw@0
|
455 else
|
jbe/bsw@0
|
456 self:add_distinct_on(expression)
|
jbe/bsw@0
|
457 end
|
jbe/bsw@0
|
458 elseif option == "grouped" then
|
jbe/bsw@0
|
459 if alias then
|
jbe/bsw@0
|
460 self:add_group_by('"' .. alias .. '"')
|
jbe/bsw@0
|
461 else
|
jbe/bsw@0
|
462 self:add_group_by(expression)
|
jbe/bsw@0
|
463 end
|
jbe/bsw@0
|
464 else
|
jbe/bsw@0
|
465 error("Unknown option '" .. option .. "' to add_field method.")
|
jbe/bsw@0
|
466 end
|
jbe/bsw@0
|
467 end
|
jbe/bsw@0
|
468 end
|
jbe/bsw@0
|
469 return self
|
jbe/bsw@0
|
470 end
|
jbe@23
|
471 --//--
|
jbe/bsw@0
|
472
|
jbe@23
|
473 --[[--
|
jbe@23
|
474 db_selector = -- same selector returned
|
jbe@23
|
475 <db_selector>:join(
|
jbe@23
|
476 expression, -- expression as passed to "assemble_command"
|
jbe@23
|
477 alias, -- optional alias expression as passed to "assemble_command"
|
jbe@23
|
478 condition -- optional condition expression as passed to "assemble_command"
|
jbe@23
|
479 )
|
jbe@23
|
480
|
jbe@23
|
481 Adds expressions for FROM clause to the selector. The selector is modified and returned. If an additional condition is given, an INNER JOIN will be used, otherwise a CROSS JOIN.
|
jbe@23
|
482
|
jbe@23
|
483 This method is identical to "add_from".
|
jbe@23
|
484
|
jbe@23
|
485 --]]--
|
jbe/bsw@0
|
486 function selector_prototype:join(...) -- NOTE: alias for add_from
|
jbe/bsw@0
|
487 return self:add_from(...)
|
jbe/bsw@0
|
488 end
|
jbe@23
|
489 --//--
|
jbe/bsw@0
|
490
|
jbe@23
|
491 --[[--
|
jbe@23
|
492 db_selector = -- same selector returned
|
jbe@23
|
493 <db_selector>:from(
|
jbe@23
|
494 expression, -- expression as passed to "assemble_command"
|
jbe@23
|
495 alias, -- optional alias expression as passed to "assemble_command"
|
jbe@23
|
496 condition -- optional condition expression as passed to "assemble_command"
|
jbe@23
|
497 )
|
jbe@23
|
498
|
jbe@23
|
499 Adds the first expression for FROM clause to the selector. The selector is modified and returned. If an additional condition is given, an INNER JOIN will be used, otherwise a CROSS JOIN.
|
jbe@23
|
500
|
jbe@23
|
501 This method is identical to "add_from" or "join", except that an error is thrown, if there is already any FROM expression existent.
|
jbe@23
|
502
|
jbe@23
|
503 --]]--
|
jbe/bsw@0
|
504 function selector_prototype:from(expression, alias, condition)
|
jbe/bsw@0
|
505 if #self._from > 0 then
|
jbe/bsw@0
|
506 error("From-clause already existing (hint: try join).")
|
jbe/bsw@0
|
507 end
|
jbe/bsw@0
|
508 return self:join(expression, alias, condition)
|
jbe/bsw@0
|
509 end
|
jbe@23
|
510 --//--
|
jbe/bsw@0
|
511
|
jbe@23
|
512 --[[--
|
jbe@23
|
513 db_selector = -- same selector returned
|
jbe@23
|
514 <db_selector>:left_join(
|
jbe@23
|
515 expression, -- expression as passed to "assemble_command"
|
jbe@23
|
516 alias, -- optional alias expression as passed to "assemble_command"
|
jbe@23
|
517 condition -- optional condition expression as passed to "assemble_command"
|
jbe@23
|
518 )
|
jbe@23
|
519
|
jbe@23
|
520 Adds expressions for FROM clause to the selector using a LEFT OUTER JOIN. The selector is modified and returned.
|
jbe@23
|
521
|
jbe@23
|
522 --]]--
|
jbe/bsw@0
|
523 function selector_prototype:left_join(expression, alias, condition)
|
jbe/bsw@0
|
524 local first = (#self._from == 0)
|
jbe/bsw@0
|
525 if not first then
|
jbe/bsw@0
|
526 add(self._from, "LEFT OUTER JOIN")
|
jbe/bsw@0
|
527 end
|
jbe/bsw@0
|
528 if alias then
|
jbe/bsw@0
|
529 add(self._from, {'$ AS "$"', {expression}, {alias}})
|
jbe/bsw@0
|
530 else
|
jbe/bsw@0
|
531 add(self._from, expression)
|
jbe/bsw@0
|
532 end
|
jbe/bsw@0
|
533 if condition then
|
jbe/bsw@0
|
534 if first then
|
jbe@55
|
535 self:add_where(condition)
|
jbe/bsw@0
|
536 else
|
jbe/bsw@0
|
537 add(self._from, "ON")
|
jbe/bsw@0
|
538 add(self._from, condition)
|
jbe/bsw@0
|
539 end
|
jbe/bsw@0
|
540 end
|
jbe/bsw@0
|
541 return self
|
jbe/bsw@0
|
542 end
|
jbe@23
|
543 --//--
|
jbe/bsw@0
|
544
|
jbe@23
|
545 --[[--
|
jbe@23
|
546 db_selector = -- same selector returned
|
jbe@23
|
547 <db_selector>:union(
|
jbe@23
|
548 expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE
|
jbe@23
|
549 )
|
jbe@23
|
550
|
jbe@348
|
551 This method adds a UNION clause to the given selector. The selector is modified and returned. The selector (or expression) passed as argument to this function shall not contain any ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clauses.
|
jbe@23
|
552
|
jbe@23
|
553 --]]--
|
jbe/bsw@0
|
554 function selector_prototype:union(expression)
|
jbe/bsw@0
|
555 self:add_combine{"UNION $", {expression}}
|
jbe/bsw@0
|
556 return self
|
jbe/bsw@0
|
557 end
|
jbe@23
|
558 --//--
|
jbe/bsw@0
|
559
|
jbe@23
|
560 --[[--
|
jbe@23
|
561 db_selector = -- same selector returned
|
jbe@23
|
562 <db_selector>:union_all(
|
jbe@23
|
563 expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE
|
jbe@23
|
564 )
|
jbe@23
|
565
|
jbe@348
|
566 This method adds a UNION ALL clause to the given selector. The selector is modified and returned. The selector (or expression) passed as argument to this function shall not contain any ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clauses.
|
jbe@23
|
567
|
jbe@23
|
568 --]]--
|
jbe/bsw@0
|
569 function selector_prototype:union_all(expression)
|
jbe/bsw@0
|
570 self:add_combine{"UNION ALL $", {expression}}
|
jbe/bsw@0
|
571 return self
|
jbe/bsw@0
|
572 end
|
jbe@23
|
573 --//--
|
jbe/bsw@0
|
574
|
jbe@23
|
575 --[[--
|
jbe@23
|
576 db_selector = -- same selector returned
|
jbe@23
|
577 <db_selector>:intersect(
|
jbe@23
|
578 expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE
|
jbe@23
|
579 )
|
jbe@23
|
580
|
jbe@23
|
581 This method adds an INTERSECT clause to the given selector. The selector is modified and returned. The selector (or expression) passed as argument to this function shall not contain any ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clauses.
|
jbe@23
|
582
|
jbe@23
|
583 --]]--
|
jbe/bsw@0
|
584 function selector_prototype:intersect(expression)
|
jbe/bsw@0
|
585 self:add_combine{"INTERSECT $", {expression}}
|
jbe/bsw@0
|
586 return self
|
jbe/bsw@0
|
587 end
|
jbe@23
|
588 --//--
|
jbe/bsw@0
|
589
|
jbe@23
|
590 --[[--
|
jbe@23
|
591 db_selector = -- same selector returned
|
jbe@23
|
592 <db_selector>:intersect_all(
|
jbe@23
|
593 expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE
|
jbe@23
|
594 )
|
jbe@23
|
595
|
jbe@23
|
596 This method adds an INTERSECT ALL clause to the given selector. The selector is modified and returned. The selector (or expression) passed as argument to this function shall not contain any ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clauses.
|
jbe@23
|
597
|
jbe@23
|
598 --]]--
|
jbe/bsw@0
|
599 function selector_prototype:intersect_all(expression)
|
jbe/bsw@0
|
600 self:add_combine{"INTERSECT ALL $", {expression}}
|
jbe/bsw@0
|
601 return self
|
jbe/bsw@0
|
602 end
|
jbe@23
|
603 --//--
|
jbe/bsw@0
|
604
|
jbe@23
|
605 --[[--
|
jbe@23
|
606 db_selector = -- same selector returned
|
jbe@23
|
607 <db_selector>:except(
|
jbe@23
|
608 expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE
|
jbe@23
|
609 )
|
jbe@23
|
610
|
jbe@23
|
611 This method adds an EXCEPT clause to the given selector. The selector is modified and returned. The selector (or expression) passed as argument to this function shall not contain any ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clauses.
|
jbe@23
|
612
|
jbe@23
|
613 --]]--
|
jbe/bsw@0
|
614 function selector_prototype:except(expression)
|
jbe/bsw@0
|
615 self:add_combine{"EXCEPT $", {expression}}
|
jbe/bsw@0
|
616 return self
|
jbe/bsw@0
|
617 end
|
jbe@23
|
618 --//--
|
jbe/bsw@0
|
619
|
jbe@23
|
620 --[[--
|
jbe@23
|
621 db_selector = -- same selector returned
|
jbe@23
|
622 <db_selector>:except_all(
|
jbe@23
|
623 expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE
|
jbe@23
|
624 )
|
jbe@23
|
625
|
jbe@23
|
626 This method adds an EXCEPT ALL clause to the given selector. The selector is modified and returned. The selector (or expression) passed as argument to this function shall not contain any ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clauses.
|
jbe@23
|
627
|
jbe@23
|
628 --]]--
|
jbe/bsw@0
|
629 function selector_prototype:except_all(expression)
|
jbe/bsw@0
|
630 self:add_combine{"EXCEPT ALL $", {expression}}
|
jbe/bsw@0
|
631 return self
|
jbe/bsw@0
|
632 end
|
jbe@23
|
633 --//--
|
jbe/bsw@0
|
634
|
jbe@23
|
635 --[[--
|
jbe@23
|
636 db_selector = -- same selector returned
|
jbe@23
|
637 <db_selector>:set_class(
|
jbe@23
|
638 class -- database class (model)
|
jbe@23
|
639 )
|
jbe@23
|
640
|
jbe@23
|
641 This method makes the selector to return database result lists or objects of the given database class (model). The selector is modified and returned.
|
jbe@23
|
642
|
jbe@23
|
643 --]]--
|
jbe/bsw@0
|
644 function selector_prototype:set_class(class)
|
jbe/bsw@0
|
645 self._class = class
|
jbe/bsw@0
|
646 return self
|
jbe/bsw@0
|
647 end
|
jbe@23
|
648 --//--
|
jbe/bsw@0
|
649
|
jbe@23
|
650 --[[--
|
jbe@23
|
651 db_selector = -- same selector returned
|
jbe@23
|
652 <db_selector>:attach(
|
jbe@23
|
653 mode, -- attachment type: "11" one to one, "1m" one to many, "m1" many to one
|
jbe@23
|
654 data2, -- other database result list or object, the results of this selector shall be attached with
|
jbe@23
|
655 field1, -- field name(s) in result list or object of this selector used for attaching
|
jbe@23
|
656 field2, -- field name(s) in "data2" used for attaching
|
jbe@23
|
657 ref1, -- name of reference field in the results of this selector after attaching
|
jbe@23
|
658 ref2 -- name of reference field in "data2" after attaching
|
jbe@23
|
659 )
|
jbe@23
|
660
|
jbe@23
|
661 This method causes database result lists or objects of this selector to be attached with other database result lists after execution. This method does not need to be called directly.
|
jbe@23
|
662
|
jbe@23
|
663 --]]--
|
jbe/bsw@0
|
664 function selector_prototype:attach(mode, data2, field1, field2, ref1, ref2)
|
jbe/bsw@0
|
665 self._attach = {
|
jbe/bsw@0
|
666 mode = mode,
|
jbe/bsw@0
|
667 data2 = data2,
|
jbe/bsw@0
|
668 field1 = field1,
|
jbe/bsw@0
|
669 field2 = field2,
|
jbe/bsw@0
|
670 ref1 = ref1,
|
jbe/bsw@0
|
671 ref2 = ref2
|
jbe/bsw@0
|
672 }
|
jbe/bsw@0
|
673 return self
|
jbe/bsw@0
|
674 end
|
jbe@23
|
675 --//--
|
jbe/bsw@0
|
676
|
jbe/bsw@0
|
677 function selector_metatable:__tostring()
|
jbe/bsw@0
|
678 local parts = {sep = " "}
|
jbe@39
|
679 if #self._with > 0 then
|
jbe@39
|
680 add(parts, {"WITH RECURSIVE $", self._with})
|
jbe@39
|
681 end
|
jbe/bsw@0
|
682 add(parts, "SELECT")
|
jbe/bsw@0
|
683 if self._distinct then
|
jbe/bsw@0
|
684 add(parts, "DISTINCT")
|
jbe/bsw@0
|
685 elseif #self._distinct_on > 0 then
|
jbe/bsw@0
|
686 add(parts, {"DISTINCT ON ($)", self._distinct_on})
|
jbe/bsw@0
|
687 end
|
jbe/bsw@0
|
688 add(parts, {"$", self._fields})
|
jbe/bsw@0
|
689 if #self._from > 0 then
|
jbe/bsw@0
|
690 add(parts, {"FROM $", self._from})
|
jbe/bsw@0
|
691 end
|
jbe/bsw@0
|
692 if #self._mode == "empty_list" then
|
jbe/bsw@0
|
693 add(parts, "WHERE FALSE")
|
jbe/bsw@0
|
694 elseif #self._where > 0 then
|
jbe/bsw@4
|
695 add(parts, {"WHERE ($)", self._where})
|
jbe/bsw@0
|
696 end
|
jbe/bsw@0
|
697 if #self._group_by > 0 then
|
jbe/bsw@0
|
698 add(parts, {"GROUP BY $", self._group_by})
|
jbe/bsw@0
|
699 end
|
jbe/bsw@0
|
700 if #self._having > 0 then
|
jbe/bsw@4
|
701 add(parts, {"HAVING ($)", self._having})
|
jbe/bsw@0
|
702 end
|
jbe/bsw@0
|
703 for i, v in ipairs(self._combine) do
|
jbe/bsw@0
|
704 add(parts, v)
|
jbe/bsw@0
|
705 end
|
jbe/bsw@0
|
706 if #self._order_by > 0 then
|
jbe/bsw@0
|
707 add(parts, {"ORDER BY $", self._order_by})
|
jbe/bsw@0
|
708 end
|
jbe/bsw@0
|
709 if self._mode == "empty_list" then
|
jbe/bsw@0
|
710 add(parts, "LIMIT 0")
|
jbe/bsw@0
|
711 elseif self._mode ~= "list" then
|
jbe/bsw@0
|
712 add(parts, "LIMIT 1")
|
jbe/bsw@0
|
713 elseif self._limit then
|
jbe/bsw@0
|
714 add(parts, "LIMIT " .. self._limit)
|
jbe/bsw@0
|
715 end
|
jbe/bsw@0
|
716 if self._offset then
|
jbe/bsw@0
|
717 add(parts, "OFFSET " .. self._offset)
|
jbe/bsw@0
|
718 end
|
jbe/bsw@4
|
719 if self._write_lock.all then
|
jbe/bsw@4
|
720 add(parts, "FOR UPDATE")
|
jbe/bsw@4
|
721 else
|
jbe/bsw@4
|
722 if self._read_lock.all then
|
jbe/bsw@4
|
723 add(parts, "FOR SHARE")
|
jbe/bsw@4
|
724 elseif #self._read_lock > 0 then
|
jbe/bsw@4
|
725 add(parts, {"FOR SHARE OF $", self._read_lock})
|
jbe/bsw@4
|
726 end
|
jbe/bsw@4
|
727 if #self._write_lock > 0 then
|
jbe/bsw@4
|
728 add(parts, {"FOR UPDATE OF $", self._write_lock})
|
jbe/bsw@4
|
729 end
|
jbe/bsw@4
|
730 end
|
jbe/bsw@0
|
731 return self._db_conn:assemble_command{"$", parts}
|
jbe/bsw@0
|
732 end
|
jbe/bsw@0
|
733
|
jbe@23
|
734 --[[--
|
jbe@23
|
735 db_error, -- database error object, or nil in case of success
|
jbe@23
|
736 result = -- database result list or object
|
jbe@23
|
737 <db_selector>:try_exec()
|
jbe@23
|
738
|
jbe@23
|
739 This method executes the selector on its database. First return value is an error object or nil in case of success. Second return value is the result list or object.
|
jbe@23
|
740
|
jbe@23
|
741 --]]--
|
jbe/bsw@0
|
742 function selector_prototype:try_exec()
|
jbe/bsw@0
|
743 if self._mode == "empty_list" then
|
jbe/bsw@0
|
744 if self._class then
|
jbe/bsw@0
|
745 return nil, self._class:create_list()
|
jbe/bsw@0
|
746 else
|
jbe/bsw@0
|
747 return nil, self._db_conn:create_list()
|
jbe/bsw@0
|
748 end
|
jbe/bsw@0
|
749 end
|
jbe/bsw@0
|
750 local db_error, db_result = self._db_conn:try_query(self, self._mode)
|
jbe/bsw@0
|
751 if db_error then
|
jbe/bsw@0
|
752 return db_error
|
jbe/bsw@0
|
753 elseif db_result then
|
jbe/bsw@0
|
754 if self._class then set_class(db_result, self._class) end
|
jbe/bsw@0
|
755 if self._attach then
|
jbe/bsw@0
|
756 attach(
|
jbe/bsw@0
|
757 self._attach.mode,
|
jbe/bsw@0
|
758 db_result,
|
jbe/bsw@0
|
759 self._attach.data2,
|
jbe/bsw@0
|
760 self._attach.field1,
|
jbe/bsw@0
|
761 self._attach.field2,
|
jbe/bsw@0
|
762 self._attach.ref1,
|
jbe/bsw@0
|
763 self._attach.ref2
|
jbe/bsw@0
|
764 )
|
jbe/bsw@0
|
765 end
|
jbe/bsw@0
|
766 return nil, db_result
|
jbe/bsw@0
|
767 else
|
jbe/bsw@0
|
768 return nil
|
jbe/bsw@0
|
769 end
|
jbe/bsw@0
|
770 end
|
jbe@23
|
771 --//--
|
jbe/bsw@0
|
772
|
jbe@23
|
773 --[[--
|
jbe@23
|
774 result = -- database result list or object
|
jbe@23
|
775 <db_selector>:exec()
|
jbe@23
|
776
|
jbe@23
|
777 This method executes the selector on its database. The result list or object is returned on success, otherwise an error is thrown.
|
jbe@23
|
778
|
jbe@23
|
779 --]]--
|
jbe/bsw@0
|
780 function selector_prototype:exec()
|
jbe/bsw@0
|
781 local db_error, result = self:try_exec()
|
jbe/bsw@0
|
782 if db_error then
|
jbe/bsw@0
|
783 db_error:escalate()
|
jbe/bsw@0
|
784 else
|
jbe/bsw@0
|
785 return result
|
jbe/bsw@0
|
786 end
|
jbe/bsw@0
|
787 end
|
jbe@23
|
788 --//--
|
jbe/bsw@0
|
789
|
jbe@23
|
790 --[[--
|
jbe@23
|
791 count = -- number of rows returned
|
jbe@23
|
792 <db_selector>:count()
|
jbe@23
|
793
|
jbe@23
|
794 This function wraps the given selector inside a subquery to count the number of rows returned by the database. NOTE: The result is cached inside the selector, thus the selector should NOT be modified afterwards.
|
jbe@23
|
795
|
jbe@23
|
796 --]]--
|
jbe/bsw@4
|
797 function selector_prototype:count()
|
jbe/bsw@4
|
798 if not self._count then
|
jbe/bsw@4
|
799 local count_selector = self:get_db_conn():new_selector()
|
jbe/bsw@4
|
800 count_selector:add_field('count(1)')
|
jbe/bsw@4
|
801 count_selector:add_from(self)
|
jbe/bsw@4
|
802 count_selector:single_object_mode()
|
jbe/bsw@4
|
803 self._count = count_selector:exec().count
|
jbe/bsw@4
|
804 end
|
jbe/bsw@4
|
805 return self._count
|
jbe/bsw@4
|
806 end
|
jbe@23
|
807 --//--
|
jbe/bsw@4
|
808
|
jbe/bsw@0
|
809
|
jbe/bsw@0
|
810
|
jbe/bsw@0
|
811 -----------------
|
jbe/bsw@0
|
812 -- attachments --
|
jbe/bsw@0
|
813 -----------------
|
jbe/bsw@0
|
814
|
jbe/bsw@0
|
815 local function attach_key(row, fields)
|
jbe/bsw@0
|
816 local t = type(fields)
|
jbe/bsw@0
|
817 if t == "string" then
|
jbe/bsw@0
|
818 return tostring(row[fields])
|
jbe/bsw@0
|
819 elseif t == "table" then
|
jbe/bsw@0
|
820 local r = {}
|
jbe/bsw@0
|
821 for idx, field in ipairs(fields) do
|
jbe/bsw@0
|
822 r[idx] = string.format("%q", row[field])
|
jbe/bsw@0
|
823 end
|
jbe/bsw@0
|
824 return table.concat(r)
|
jbe/bsw@0
|
825 else
|
jbe/bsw@0
|
826 error("Field information for 'mondelefant.attach' is neither a string nor a table.")
|
jbe/bsw@0
|
827 end
|
jbe/bsw@0
|
828 end
|
jbe/bsw@0
|
829
|
jbe@23
|
830 --[[--
|
jbe@23
|
831 mondelefant.attach(
|
jbe@23
|
832 mode, -- attachment type: "11" one to one, "1m" one to many, "m1" many to one
|
jbe@23
|
833 data1, -- first database result list or object
|
jbe@23
|
834 data2, -- second database result list or object
|
jbe@23
|
835 key1, -- field name(s) in first result list or object used for attaching
|
jbe@23
|
836 key2, -- field name(s) in second result list or object used for attaching
|
jbe@23
|
837 ref1, -- name of reference field to be set in first database result list or object
|
jbe@23
|
838 ref2 -- name of reference field to be set in second database result list or object
|
jbe@23
|
839 )
|
jbe@23
|
840
|
jbe@23
|
841 This function attaches database result lists/objects with each other. It does not need to be called directly.
|
jbe@23
|
842
|
jbe@23
|
843 --]]--
|
jbe/bsw@0
|
844 function attach(mode, data1, data2, key1, key2, ref1, ref2)
|
jbe/bsw@0
|
845 local many1, many2
|
jbe/bsw@0
|
846 if mode == "11" then
|
jbe/bsw@0
|
847 many1 = false
|
jbe/bsw@0
|
848 many2 = false
|
jbe/bsw@0
|
849 elseif mode == "1m" then
|
jbe/bsw@0
|
850 many1 = false
|
jbe/bsw@0
|
851 many2 = true
|
jbe/bsw@0
|
852 elseif mode == "m1" then
|
jbe/bsw@0
|
853 many1 = true
|
jbe/bsw@0
|
854 many2 = false
|
jbe/bsw@0
|
855 elseif mode == "mm" then
|
jbe/bsw@0
|
856 many1 = true
|
jbe/bsw@0
|
857 many2 = true
|
jbe/bsw@0
|
858 else
|
jbe/bsw@0
|
859 error("Unknown mode specified for 'mondelefant.attach'.")
|
jbe/bsw@0
|
860 end
|
jbe/bsw@0
|
861 local list1, list2
|
jbe/bsw@0
|
862 if data1._type == "object" then
|
jbe/bsw@0
|
863 list1 = { data1 }
|
jbe/bsw@0
|
864 elseif data1._type == "list" then
|
jbe/bsw@0
|
865 list1 = data1
|
jbe/bsw@0
|
866 else
|
jbe/bsw@0
|
867 error("First result data given to 'mondelefant.attach' is invalid.")
|
jbe/bsw@0
|
868 end
|
jbe/bsw@0
|
869 if data2._type == "object" then
|
jbe/bsw@0
|
870 list2 = { data2 }
|
jbe/bsw@0
|
871 elseif data2._type == "list" then
|
jbe/bsw@0
|
872 list2 = data2
|
jbe/bsw@0
|
873 else
|
jbe/bsw@0
|
874 error("Second result data given to 'mondelefant.attach' is invalid.")
|
jbe/bsw@0
|
875 end
|
jbe/bsw@0
|
876 local hash1 = {}
|
jbe/bsw@0
|
877 local hash2 = {}
|
jbe/bsw@0
|
878 if ref2 then
|
jbe/bsw@0
|
879 for i, row in ipairs(list1) do
|
jbe/bsw@0
|
880 local key = attach_key(row, key1)
|
jbe/bsw@0
|
881 local list = hash1[key]
|
jbe/bsw@0
|
882 if not list then list = {}; hash1[key] = list end
|
jbe/bsw@0
|
883 list[#list + 1] = row
|
jbe/bsw@0
|
884 end
|
jbe/bsw@0
|
885 end
|
jbe/bsw@0
|
886 if ref1 then
|
jbe/bsw@0
|
887 for i, row in ipairs(list2) do
|
jbe/bsw@0
|
888 local key = attach_key(row, key2)
|
jbe/bsw@0
|
889 local list = hash2[key]
|
jbe/bsw@0
|
890 if not list then list = {}; hash2[key] = list end
|
jbe/bsw@0
|
891 list[#list + 1] = row
|
jbe/bsw@0
|
892 end
|
jbe/bsw@0
|
893 for i, row in ipairs(list1) do
|
jbe/bsw@0
|
894 local key = attach_key(row, key1)
|
jbe/bsw@0
|
895 local matching_rows = hash2[key]
|
jbe/bsw@0
|
896 if many2 then
|
jbe/bsw@0
|
897 local list = data2._connection:create_list(matching_rows)
|
jbe/bsw@0
|
898 list._class = data2._class
|
jbe/bsw@0
|
899 row._ref[ref1] = list
|
jbe/bsw@0
|
900 elseif matching_rows and #matching_rows == 1 then
|
jbe/bsw@0
|
901 row._ref[ref1] = matching_rows[1]
|
jbe/bsw@0
|
902 else
|
jbe/bsw@0
|
903 row._ref[ref1] = false
|
jbe/bsw@0
|
904 end
|
jbe/bsw@0
|
905 end
|
jbe/bsw@0
|
906 end
|
jbe/bsw@0
|
907 if ref2 then
|
jbe/bsw@0
|
908 for i, row in ipairs(list2) do
|
jbe/bsw@0
|
909 local key = attach_key(row, key2)
|
jbe/bsw@0
|
910 local matching_rows = hash1[key]
|
jbe/bsw@0
|
911 if many1 then
|
jbe/bsw@0
|
912 local list = data1._connection:create_list(matching_rows)
|
jbe/bsw@0
|
913 list._class = data1._class
|
jbe/bsw@0
|
914 row._ref[ref2] = list
|
jbe/bsw@0
|
915 elseif matching_rows and #matching_rows == 1 then
|
jbe/bsw@0
|
916 row._ref[ref2] = matching_rows[1]
|
jbe/bsw@0
|
917 else
|
jbe/bsw@0
|
918 row._ref[ref2] = false
|
jbe/bsw@0
|
919 end
|
jbe/bsw@0
|
920 end
|
jbe/bsw@0
|
921 end
|
jbe/bsw@0
|
922 end
|
jbe@23
|
923 --//--
|
jbe/bsw@0
|
924
|
jbe/bsw@0
|
925
|
jbe/bsw@0
|
926
|
jbe/bsw@0
|
927 ------------------
|
jbe/bsw@0
|
928 -- model system --
|
jbe/bsw@0
|
929 ------------------
|
jbe/bsw@0
|
930
|
jbe@23
|
931 --[[--
|
jbe@509
|
932 <db_class>.schema
|
jbe@509
|
933
|
jbe@509
|
934 Can be set if the underlying table is not in the default "public" schema of the PostgreSQL database. Leave untouched (i.e. nil) otherwise.
|
jbe@509
|
935
|
jbe@509
|
936 --]]--
|
jbe@509
|
937 class_prototype.schema = nil
|
jbe@509
|
938 --//--
|
jbe@509
|
939
|
jbe@509
|
940 --[[--
|
jbe@509
|
941 <db_class>.table
|
jbe@509
|
942
|
jbe@509
|
943 Name of the table in the PostgreSQL database. Should be set in the model file of each model.
|
jbe@509
|
944
|
jbe@509
|
945 --]]--
|
jbe@509
|
946 class_prototype.table = nil
|
jbe@509
|
947 --//--
|
jbe@509
|
948
|
jbe@509
|
949 --[[--
|
jbe@23
|
950 <db_class>.primary_key
|
jbe@23
|
951
|
jbe@23
|
952 Primary key of a database class (model). Defaults to "id".
|
jbe@23
|
953
|
jbe@376
|
954 If the primary key is a tuple, then a sequence (table with integer keys mapped to the column names) must be used. If the primary key is contained in a JSON document within a table column, then a special object with the following fields is expected: {json_doc = "column_name", key = "field_name_within_json_object", type = "postgresql_type"}.
|
jbe@376
|
955
|
jbe@23
|
956 --]]--
|
jbe/bsw@0
|
957 class_prototype.primary_key = "id"
|
jbe@23
|
958 --//--
|
jbe/bsw@0
|
959
|
jbe@23
|
960 --[[--
|
jbe@376
|
961 <db_class>.document_column
|
jbe@376
|
962
|
jbe@376
|
963 Optional column name to redirect key lookups to. This can be used to allow for an easier access to fields of a JSON document.
|
jbe@376
|
964
|
jbe@376
|
965 --]]--
|
jbe@376
|
966 class_prototype.document_column = nil
|
jbe@376
|
967 --//--
|
jbe@376
|
968
|
jbe@376
|
969 --[[--
|
jbe@553
|
970 <db_class>.binary_columns
|
jbe@553
|
971
|
jbe@553
|
972 If binary data (PostgreSQL type BYTEA) is stored, must be set to a table mapping all column names used with binary data to true, e.g. File.binary_columns = { data = true }.
|
jbe@553
|
973
|
jbe@553
|
974 --]]--
|
jbe@553
|
975 class_prototype.binary_columns = nil
|
jbe@553
|
976 --//
|
jbe@553
|
977
|
jbe@553
|
978 --[[--
|
jbe@23
|
979 db_handle = -- database connection handle used by this class
|
jbe@23
|
980 <db_class>:get_db_conn()
|
jbe@23
|
981
|
jbe@23
|
982 By implementing this method for a particular model or overwriting it in the default prototype "mondelefant.class_prototype", classes are connected with a particular database. This method needs to return a database connection handle. If it is not overwritten, an error is thrown, when invoking this method.
|
jbe@23
|
983
|
jbe@23
|
984 --]]--
|
jbe/bsw@0
|
985 function class_prototype:get_db_conn()
|
jbe/bsw@0
|
986 error(
|
jbe/bsw@0
|
987 "Method mondelefant class(_prototype):get_db_conn() " ..
|
jbe/bsw@0
|
988 "has to be implemented."
|
jbe/bsw@0
|
989 )
|
jbe/bsw@0
|
990 end
|
jbe@23
|
991 --//--
|
jbe/bsw@0
|
992
|
jbe@23
|
993 --[[--
|
jbe@23
|
994 string = -- string of form '"schemaname"."tablename"' or '"tablename"'
|
jbe@23
|
995 <db_class>:get_qualified_table()
|
jbe@23
|
996
|
jbe@23
|
997 This method returns a string with the (double quoted) qualified table name used to store objects of this class.
|
jbe@23
|
998
|
jbe@23
|
999 --]]--
|
jbe/bsw@0
|
1000 function class_prototype:get_qualified_table()
|
jbe/bsw@0
|
1001 if not self.table then error "Table unknown." end
|
jbe/bsw@0
|
1002 if self.schema then
|
jbe/bsw@0
|
1003 return '"' .. self.schema .. '"."' .. self.table .. '"'
|
jbe/bsw@0
|
1004 else
|
jbe/bsw@0
|
1005 return '"' .. self.table .. '"'
|
jbe/bsw@0
|
1006 end
|
jbe/bsw@0
|
1007 end
|
jbe@23
|
1008 --]]--
|
jbe/bsw@0
|
1009
|
jbe@23
|
1010 --[[--
|
jbe@23
|
1011 string = -- single quoted string of form "'schemaname.tablename'" or "'tablename'"
|
jbe@23
|
1012 <db_class>:get_qualified_table_literal()
|
jbe@23
|
1013
|
jbe@23
|
1014 This method returns a string with an SQL literal representing the given table. It causes ambiguities when the table name contains a dot (".") character.
|
jbe@23
|
1015
|
jbe@23
|
1016 --]]--
|
jbe/bsw@0
|
1017 function class_prototype:get_qualified_table_literal()
|
jbe/bsw@0
|
1018 if not self.table then error "Table unknown." end
|
jbe/bsw@0
|
1019 if self.schema then
|
jbe/bsw@0
|
1020 return self.schema .. '.' .. self.table
|
jbe/bsw@0
|
1021 else
|
jbe/bsw@0
|
1022 return self.table
|
jbe/bsw@0
|
1023 end
|
jbe/bsw@0
|
1024 end
|
jbe@23
|
1025 --//--
|
jbe/bsw@0
|
1026
|
jbe@23
|
1027 --[[--
|
jbe@23
|
1028 list = -- list of column names of primary key
|
jbe@23
|
1029 <db_class>:get_primary_key_list()
|
jbe@23
|
1030
|
jbe@23
|
1031 This method returns a list of column names of the primary key.
|
jbe@23
|
1032
|
jbe@23
|
1033 --]]--
|
jbe/bsw@0
|
1034 function class_prototype:get_primary_key_list()
|
jbe/bsw@0
|
1035 local primary_key = self.primary_key
|
jbe/bsw@0
|
1036 if type(primary_key) == "string" then
|
jbe/bsw@0
|
1037 return {primary_key}
|
jbe/bsw@0
|
1038 else
|
jbe/bsw@0
|
1039 return primary_key
|
jbe/bsw@0
|
1040 end
|
jbe/bsw@0
|
1041 end
|
jbe@23
|
1042 --//--
|
jbe/bsw@0
|
1043
|
jbe@23
|
1044 --[[--
|
jbe@23
|
1045 columns = -- list of columns
|
jbe@23
|
1046 <db_class>:get_columns()
|
jbe@23
|
1047
|
jbe@23
|
1048 This method returns a list of column names of the table used for the class.
|
jbe@23
|
1049
|
jbe@23
|
1050 --]]--
|
jbe/bsw@0
|
1051 function class_prototype:get_columns()
|
jbe/bsw@0
|
1052 if self._columns then
|
jbe/bsw@0
|
1053 return self._columns
|
jbe/bsw@0
|
1054 end
|
jbe/bsw@0
|
1055 local selector = self:get_db_conn():new_selector()
|
jbe/bsw@0
|
1056 selector:set_class(self)
|
jbe/bsw@0
|
1057 selector:from(self:get_qualified_table())
|
jbe/bsw@0
|
1058 selector:add_field("*")
|
jbe/bsw@0
|
1059 selector:add_where("FALSE")
|
jbe/bsw@0
|
1060 local db_result = selector:exec()
|
jbe/bsw@0
|
1061 local connection = db_result._connection
|
jbe/bsw@0
|
1062 local columns = {}
|
jbe/bsw@0
|
1063 for idx, info in ipairs(db_result._column_info) do
|
jbe/bsw@0
|
1064 local key = info.field_name
|
jbe/bsw@0
|
1065 local value = {
|
jbe/bsw@0
|
1066 name = key,
|
jbe/bsw@0
|
1067 type = connection.type_mappings[info.type]
|
jbe/bsw@0
|
1068 }
|
jbe/bsw@0
|
1069 columns[key] = value
|
jbe/bsw@0
|
1070 table.insert(columns, value)
|
jbe/bsw@0
|
1071 end
|
jbe/bsw@0
|
1072 self._columns = columns
|
jbe/bsw@0
|
1073 return columns
|
jbe/bsw@0
|
1074 end
|
jbe@25
|
1075 --//--
|
jbe/bsw@0
|
1076
|
jbe@23
|
1077 --[[--
|
jbe@23
|
1078 selector = -- new selector for selecting objects of this class
|
jbe@23
|
1079 <db_class>:new_selector(
|
jbe@23
|
1080 db_conn -- optional(!) database connection handle, defaults to result of :get_db_conn()
|
jbe@23
|
1081 )
|
jbe@23
|
1082
|
jbe@23
|
1083 This method creates a new selector for selecting objects of the class.
|
jbe@23
|
1084
|
jbe@23
|
1085 --]]--
|
jbe/bsw@0
|
1086 function class_prototype:new_selector(db_conn)
|
jbe/bsw@0
|
1087 local selector = (db_conn or self:get_db_conn()):new_selector()
|
jbe/bsw@0
|
1088 selector:set_class(self)
|
jbe/bsw@0
|
1089 selector:from(self:get_qualified_table())
|
jbe/bsw@0
|
1090 selector:add_field(self:get_qualified_table() .. ".*")
|
jbe/bsw@0
|
1091 return selector
|
jbe/bsw@0
|
1092 end
|
jbe@23
|
1093 --//--
|
jbe/bsw@0
|
1094
|
jbe@23
|
1095 --[[--
|
jbe@23
|
1096 db_list = -- database result being an empty list
|
jbe@23
|
1097 <db_class>:create_list()
|
jbe@23
|
1098
|
jbe@23
|
1099 Creates an empty database result representing a list of objects of the given class.
|
jbe@23
|
1100
|
jbe@23
|
1101 --]]--
|
jbe/bsw@0
|
1102 function class_prototype:create_list()
|
jbe/bsw@0
|
1103 local list = self:get_db_conn():create_list()
|
jbe/bsw@0
|
1104 list._class = self
|
jbe/bsw@0
|
1105 return list
|
jbe/bsw@0
|
1106 end
|
jbe@23
|
1107 --//--
|
jbe/bsw@0
|
1108
|
jbe@23
|
1109 --[[--
|
jbe@23
|
1110 db_object = -- database object (instance of model)
|
jbe@23
|
1111 <db_class>:new()
|
jbe@23
|
1112
|
jbe@23
|
1113 Creates a new object of the given class.
|
jbe@23
|
1114
|
jbe@23
|
1115 --]]--
|
jbe/bsw@0
|
1116 function class_prototype:new()
|
jbe/bsw@0
|
1117 local object = self:get_db_conn():create_object()
|
jbe/bsw@0
|
1118 object._class = self
|
jbe/bsw@0
|
1119 object._new = true
|
jbe/bsw@0
|
1120 return object
|
jbe/bsw@0
|
1121 end
|
jbe@23
|
1122 --//--
|
jbe/bsw@0
|
1123
|
jbe@23
|
1124 --[[--
|
jbe@464
|
1125 <db_object>:upsert_mode()
|
jbe@464
|
1126
|
jbe@464
|
1127 Enables UPSERT mode for an existing (new) database object. Note that only new objects can use the UPSERT mode, i.e. it is not possible to call this method on objects returned from a database query.
|
jbe@464
|
1128
|
jbe@464
|
1129 --]]--
|
jbe@464
|
1130 function class_prototype.object:upsert_mode()
|
jbe@464
|
1131 if not self._new then
|
jbe@464
|
1132 error("Upsert mode requires a new object and cannot be used on objects returned from a database query.")
|
jbe@464
|
1133 end
|
jbe@464
|
1134 self._upsert = true
|
jbe@464
|
1135 return self
|
jbe@464
|
1136 end
|
jbe@464
|
1137 --//--
|
jbe@464
|
1138
|
jbe@464
|
1139 --[[--
|
jbe@23
|
1140 db_error = -- database error object, or nil in case of success
|
jbe@23
|
1141 <db_object>:try_save()
|
jbe@23
|
1142
|
jbe@23
|
1143 This method saves changes to an object in the database. Returns nil on success, otherwise an error object is returned.
|
jbe@23
|
1144
|
jbe@23
|
1145 --]]--
|
jbe/bsw@0
|
1146 function class_prototype.object:try_save()
|
jbe/bsw@0
|
1147 if not self._class then
|
jbe/bsw@0
|
1148 error("Cannot save object: No class information available.")
|
jbe/bsw@0
|
1149 end
|
jbe/bsw@0
|
1150 local primary_key = self._class:get_primary_key_list()
|
jbe/bsw@0
|
1151 if self._new then
|
jbe/bsw@0
|
1152 local fields = {sep = ", "}
|
jbe/bsw@0
|
1153 local values = {sep = ", "}
|
jbe@375
|
1154 for key in pairs(self._dirty or {}) do
|
jbe@466
|
1155 add(fields, '"' .. key .. '"')
|
jbe@553
|
1156 if self._class.binary_columns and self._class.binary_columns[key] then
|
jbe@553
|
1157 add(values, {'$', {self._connection:quote_binary(self._col[key])}})
|
jbe@553
|
1158 else
|
jbe@553
|
1159 add(values, {'?', self._col[key]})
|
jbe@553
|
1160 end
|
jbe/bsw@0
|
1161 end
|
jbe@465
|
1162 local returning = { sep = ", " }
|
jbe@465
|
1163 if primary_key.json_doc then
|
jbe@465
|
1164 returning[1] = {
|
jbe@465
|
1165 '("$"->>?)::$ AS "json_key"',
|
jbe@465
|
1166 {primary_key.json_doc}, primary_key.key, {primary_key.type}
|
jbe@465
|
1167 }
|
jbe@465
|
1168 else
|
jbe@465
|
1169 for idx, value in ipairs(primary_key) do
|
jbe@465
|
1170 returning[idx] = '"' .. value .. '"'
|
jbe@465
|
1171 end
|
jbe@465
|
1172 end
|
jbe@465
|
1173 local db_error, db_result
|
jbe@464
|
1174 if self._upsert then
|
jbe@464
|
1175 local upsert_keys = {sep = ", "}
|
jbe@464
|
1176 if primary_key.json_doc then
|
jbe@464
|
1177 upsert_keys[1] = {
|
jbe@464
|
1178 '("$"->>?)::$',
|
jbe@464
|
1179 {primary_key.json_doc}, primary_key.key, {primary_key.type}
|
jbe@464
|
1180 }
|
jbe@464
|
1181 else
|
jbe@464
|
1182 for idx, value in ipairs(primary_key) do
|
jbe@466
|
1183 upsert_keys[idx] = '"' .. value .. '"'
|
jbe@464
|
1184 end
|
jbe@464
|
1185 end
|
jbe@465
|
1186 if #fields == 0 then
|
jbe@465
|
1187 db_error, db_result = self._connection:try_query(
|
jbe@465
|
1188 {
|
jbe@465
|
1189 'INSERT INTO $ DEFAULT VALUES ON CONFLICT ($) DO NOTHING $',
|
jbe@465
|
1190 {self._class:get_qualified_table()},
|
jbe@465
|
1191 upsert_keys,
|
jbe@465
|
1192 returning
|
jbe@465
|
1193 },
|
jbe@465
|
1194 "object"
|
jbe@465
|
1195 )
|
jbe@465
|
1196 else
|
jbe@465
|
1197 local upsert_sets = {sep = ", "}
|
jbe@465
|
1198 for key in pairs(self._dirty) do
|
jbe@465
|
1199 add(upsert_sets, {'"$" = ?', {key}, self._col[key]})
|
jbe@465
|
1200 end
|
jbe@465
|
1201 db_error, db_result = self._connection:try_query(
|
jbe@465
|
1202 {
|
jbe@465
|
1203 'INSERT INTO $ ($) VALUES ($) ON CONFLICT ($) DO UPDATE SET $ RETURNING $',
|
jbe@465
|
1204 {self._class:get_qualified_table()},
|
jbe@465
|
1205 fields,
|
jbe@465
|
1206 values,
|
jbe@465
|
1207 upsert_keys,
|
jbe@465
|
1208 upsert_sets,
|
jbe@465
|
1209 returning
|
jbe@465
|
1210 },
|
jbe@465
|
1211 "object"
|
jbe@465
|
1212 )
|
jbe@464
|
1213 end
|
jbe@373
|
1214 else
|
jbe@465
|
1215 if #fields == 0 then
|
jbe@465
|
1216 db_error, db_result = self._connection:try_query(
|
jbe@465
|
1217 {
|
jbe@465
|
1218 'INSERT INTO $ DEFAULT VALUES RETURNING $',
|
jbe@465
|
1219 {self._class:get_qualified_table()},
|
jbe@465
|
1220 returning
|
jbe@465
|
1221 },
|
jbe@465
|
1222 "object"
|
jbe@465
|
1223 )
|
jbe@465
|
1224 else
|
jbe@465
|
1225 db_error, db_result = self._connection:try_query(
|
jbe@465
|
1226 {
|
jbe@465
|
1227 'INSERT INTO $ ($) VALUES ($) RETURNING $',
|
jbe@465
|
1228 {self._class:get_qualified_table()},
|
jbe@465
|
1229 fields,
|
jbe@465
|
1230 values,
|
jbe@465
|
1231 returning
|
jbe@465
|
1232 },
|
jbe@465
|
1233 "object"
|
jbe@465
|
1234 )
|
jbe@465
|
1235 end
|
jbe@373
|
1236 end
|
jbe@373
|
1237 if db_error then
|
jbe@373
|
1238 return db_error
|
jbe@373
|
1239 end
|
jbe@373
|
1240 if primary_key.json_doc then
|
jbe@438
|
1241 self._col[primary_key.json_doc][primary_key.key] = db_result.json_key
|
jbe/bsw@0
|
1242 else
|
jbe/bsw@0
|
1243 for idx, value in ipairs(primary_key) do
|
jbe/bsw@0
|
1244 self[value] = db_result[value]
|
jbe/bsw@0
|
1245 end
|
jbe/bsw@0
|
1246 end
|
jbe@467
|
1247 if not self._upsert then
|
jbe@467
|
1248 self._new = false
|
jbe@467
|
1249 end
|
jbe/bsw@0
|
1250 else
|
jbe@464
|
1251 local update_sets = {sep = ", "}
|
jbe@375
|
1252 for key, mutability_state in pairs(self._dirty or {}) do
|
jbe@375
|
1253 if
|
jbe@375
|
1254 mutability_state == true or (
|
jbe@375
|
1255 verify_mutability_state and
|
jbe@437
|
1256 verify_mutability_state(self._col[key], mutability_state)
|
jbe@375
|
1257 )
|
jbe@375
|
1258 then
|
jbe@553
|
1259 if self._class.binary_columns and self._class.binary_columns[key] then
|
jbe@553
|
1260 add(update_sets, {'"$" = $', {key}, {self._connection:quote_binary(self._col[key])}})
|
jbe@553
|
1261 else
|
jbe@553
|
1262 add(update_sets, {'"$" = ?', {key}, self._col[key]})
|
jbe@553
|
1263 end
|
jbe@375
|
1264 self._dirty[key] = true -- always dirty in case of later error
|
jbe@375
|
1265 end
|
jbe/bsw@0
|
1266 end
|
jbe@464
|
1267 if #update_sets >= 1 then
|
jbe/bsw@0
|
1268 local primary_key_compare = {sep = " AND "}
|
jbe@373
|
1269 if primary_key.json_doc then
|
jbe@373
|
1270 primary_key_compare[1] = {
|
jbe@373
|
1271 '("$"->>?)::$ = ?',
|
jbe@373
|
1272 {primary_key.json_doc}, primary_key.key, {primary_key.type},
|
jbe@438
|
1273 self._col[primary_key.json_doc][primary_key.key]
|
jbe/bsw@0
|
1274 }
|
jbe@373
|
1275 else
|
jbe@373
|
1276 for idx, value in ipairs(primary_key) do
|
jbe@373
|
1277 primary_key_compare[idx] = {
|
jbe@373
|
1278 "$ = ?",
|
jbe@373
|
1279 {'"' .. value .. '"'},
|
jbe@373
|
1280 self[value]
|
jbe@373
|
1281 }
|
jbe@373
|
1282 end
|
jbe/bsw@0
|
1283 end
|
jbe/bsw@0
|
1284 local db_error = self._connection:try_query{
|
jbe/bsw@0
|
1285 'UPDATE $ SET $ WHERE $',
|
jbe/bsw@0
|
1286 {self._class:get_qualified_table()},
|
jbe@464
|
1287 update_sets,
|
jbe/bsw@0
|
1288 primary_key_compare
|
jbe/bsw@0
|
1289 }
|
jbe/bsw@0
|
1290 if db_error then
|
jbe/bsw@0
|
1291 return db_error
|
jbe/bsw@0
|
1292 end
|
jbe/bsw@0
|
1293 end
|
jbe/bsw@0
|
1294 end
|
jbe@375
|
1295 for key in pairs(self._dirty or {}) do
|
jbe@375
|
1296 if save_mutability_state then
|
jbe@375
|
1297 self._dirty[key] =
|
jbe@437
|
1298 save_mutability_state and save_mutability_state(self._col[key]) or nil
|
jbe@375
|
1299 end
|
jbe@375
|
1300 end
|
jbe/bsw@0
|
1301 return nil
|
jbe/bsw@0
|
1302 end
|
jbe@23
|
1303 --//--
|
jbe/bsw@0
|
1304
|
jbe@23
|
1305 --[[--
|
jbe@23
|
1306 <db_object>:save()
|
jbe@23
|
1307
|
jbe@23
|
1308 This method saves changes to an object in the database. Throws error, unless successful.
|
jbe@23
|
1309
|
jbe@23
|
1310 --]]--
|
jbe/bsw@0
|
1311 function class_prototype.object:save()
|
jbe/bsw@0
|
1312 local db_error = self:try_save()
|
jbe/bsw@0
|
1313 if db_error then
|
jbe/bsw@0
|
1314 db_error:escalate()
|
jbe/bsw@0
|
1315 end
|
jbe/bsw@0
|
1316 return self
|
jbe/bsw@0
|
1317 end
|
jbe@23
|
1318 --//--
|
jbe/bsw@0
|
1319
|
jbe@23
|
1320 --[[--
|
jbe@23
|
1321 db_error = -- database error object, or nil in case of success
|
jbe@23
|
1322 <db_object>:try_destroy()
|
jbe@23
|
1323
|
jbe@23
|
1324 This method deletes an object in the database. Returns nil on success, otherwise an error object is returned.
|
jbe@23
|
1325
|
jbe@23
|
1326 --]]--
|
jbe/bsw@0
|
1327 function class_prototype.object:try_destroy()
|
jbe/bsw@0
|
1328 if not self._class then
|
jbe/bsw@0
|
1329 error("Cannot destroy object: No class information available.")
|
jbe/bsw@0
|
1330 end
|
jbe/bsw@0
|
1331 local primary_key = self._class:get_primary_key_list()
|
jbe/bsw@0
|
1332 local primary_key_compare = {sep = " AND "}
|
jbe@373
|
1333 if primary_key.json_doc then
|
jbe@373
|
1334 primary_key_compare[1] = {
|
jbe@373
|
1335 '("$"->>?)::$ = ?',
|
jbe@373
|
1336 {primary_key.json_doc}, primary_key.key, {primary_key.type},
|
bsw@462
|
1337 self._col[primary_key.json_doc][primary_key.key]
|
jbe/bsw@0
|
1338 }
|
jbe@373
|
1339 else
|
jbe@373
|
1340 for idx, value in ipairs(primary_key) do
|
jbe@373
|
1341 primary_key_compare[idx] = {
|
jbe@373
|
1342 "$ = ?",
|
jbe@373
|
1343 {'"' .. value .. '"'},
|
jbe@373
|
1344 self[value]
|
jbe@373
|
1345 }
|
jbe@373
|
1346 end
|
jbe/bsw@0
|
1347 end
|
jbe/bsw@0
|
1348 return self._connection:try_query{
|
jbe/bsw@0
|
1349 'DELETE FROM $ WHERE $',
|
jbe/bsw@0
|
1350 {self._class:get_qualified_table()},
|
jbe/bsw@0
|
1351 primary_key_compare
|
jbe/bsw@0
|
1352 }
|
jbe/bsw@0
|
1353 end
|
jbe@23
|
1354 --//--
|
jbe/bsw@0
|
1355
|
jbe@23
|
1356 --[[--
|
jbe@23
|
1357 <db_object>:destroy()
|
jbe@23
|
1358
|
jbe@23
|
1359 This method deletes an object in the database. Throws error, unless successful.
|
jbe@23
|
1360
|
jbe@23
|
1361 --]]--
|
jbe/bsw@0
|
1362 function class_prototype.object:destroy()
|
jbe/bsw@0
|
1363 local db_error = self:try_destroy()
|
jbe/bsw@0
|
1364 if db_error then
|
jbe/bsw@0
|
1365 db_error:escalate()
|
jbe/bsw@0
|
1366 end
|
jbe/bsw@0
|
1367 return self
|
jbe/bsw@0
|
1368 end
|
jbe@23
|
1369 --//--
|
jbe/bsw@0
|
1370
|
jbe@23
|
1371 --[[--
|
jbe@23
|
1372 db_selector =
|
jbe@23
|
1373 <db_list>:get_reference_selector(
|
jbe@23
|
1374 ref_name, -- name of reference (e.g. "children")
|
jbe@23
|
1375 options, -- table options passed to the reference loader (e.g. { order = ... })
|
jbe@23
|
1376 ref_alias, -- optional alias for the reference (e.g. "ordered_children")
|
jbe@23
|
1377 back_ref_alias -- back reference name (e.g. "parent")
|
jbe@23
|
1378 )
|
jbe@23
|
1379
|
jbe@23
|
1380 This method returns a special selector for selecting referenced objects. It is prepared in a way, that on execution of the selector, all returned objects are attached with the objects of the existent list. The "ref" and "back_ref" arguments passed to "add_reference" are used for the attachment, unless aliases are given with "ref_alias" and "back_ref_alias". If "options" are set, these options are passed to the reference loader. The default reference loader supports only one option named "order". If "order" is set to nil, the default order is used, if "order" is set to false, no ORDER BY statment is included in the selector, otherwise the given expression is used for ordering.
|
jbe@23
|
1381
|
jbe@23
|
1382 This method is not only available for database result lists but also for database result objects.
|
jbe@23
|
1383
|
jbe@23
|
1384 --]]--
|
jbe/bsw@0
|
1385 function class_prototype.list:get_reference_selector(
|
jbe/bsw@0
|
1386 ref_name, options, ref_alias, back_ref_alias
|
jbe/bsw@0
|
1387 )
|
jbe@542
|
1388 local ref_info = self._class:get_reference(ref_name)
|
jbe/bsw@0
|
1389 if not ref_info then
|
jbe/bsw@0
|
1390 error('Reference with name "' .. ref_name .. '" not found.')
|
jbe/bsw@0
|
1391 end
|
jbe/bsw@0
|
1392 local selector = ref_info.selector_generator(self, options or {})
|
jbe/bsw@0
|
1393 local mode = ref_info.mode
|
jbe/bsw@0
|
1394 if mode == "mm" or mode == "1m" then
|
jbe/bsw@0
|
1395 mode = "m1"
|
jbe/bsw@0
|
1396 elseif mode == "m1" then
|
jbe/bsw@0
|
1397 mode = "1m"
|
jbe/bsw@0
|
1398 end
|
jbe/bsw@0
|
1399 local ref_alias = ref_alias
|
jbe/bsw@0
|
1400 if ref_alias == false then
|
jbe/bsw@0
|
1401 ref_alias = nil
|
jbe/bsw@0
|
1402 elseif ref_alias == nil then
|
jbe/bsw@0
|
1403 ref_alias = ref_name
|
jbe/bsw@0
|
1404 end
|
jbe/bsw@0
|
1405 local back_ref_alias
|
jbe/bsw@0
|
1406 if back_ref_alias == false then
|
jbe/bsw@0
|
1407 back_ref_alias = nil
|
jbe/bsw@0
|
1408 elseif back_ref_alias == nil then
|
jbe/bsw@0
|
1409 back_ref_alias = ref_info.back_ref
|
jbe/bsw@0
|
1410 end
|
jbe/bsw@0
|
1411 selector:attach(
|
jbe/bsw@0
|
1412 mode,
|
jbe/bsw@0
|
1413 self,
|
jbe/bsw@0
|
1414 ref_info.that_key, ref_info.this_key,
|
jbe/bsw@0
|
1415 back_ref_alias or ref_info.back_ref, ref_alias or ref_name
|
jbe/bsw@0
|
1416 )
|
jbe/bsw@0
|
1417 return selector
|
jbe/bsw@0
|
1418 end
|
jbe@23
|
1419 --//--
|
jbe/bsw@0
|
1420
|
jbe@23
|
1421 --[[--
|
jbe@23
|
1422 db_list_or_object =
|
jbe@23
|
1423 <db_list>:load(
|
jbe@23
|
1424 ref_name, -- name of reference (e.g. "children")
|
jbe@23
|
1425 options, -- table options passed to the reference loader (e.g. { order = ... })
|
jbe@23
|
1426 ref_alias, -- optional alias for the reference (e.g. "ordered_children")
|
jbe@23
|
1427 back_ref_alias -- back reference name (e.g. "parent")
|
jbe@23
|
1428 )
|
jbe@23
|
1429
|
jbe@23
|
1430 This method loads referenced objects and attaches them with the objects of the existent list. The "ref" and "back_ref" arguments passed to "add_reference" are used for the attachment, unless aliases are given with "ref_alias" and "back_ref_alias". If "options" are set, these options are passed to the reference loader. The default reference loader supports only one option named "order". If "order" is set to nil, the default order is used, if "order" is set to false, no ORDER BY statment is included in the selector, otherwise the given expression is used for ordering.
|
jbe@23
|
1431
|
jbe@23
|
1432 This method is not only available for database result lists but also for database result objects.
|
jbe@23
|
1433
|
jbe@23
|
1434 --]]--
|
jbe/bsw@0
|
1435 function class_prototype.list.load(...)
|
jbe/bsw@0
|
1436 return class_prototype.list.get_reference_selector(...):exec()
|
jbe/bsw@0
|
1437 end
|
jbe@23
|
1438 --//--
|
jbe/bsw@0
|
1439
|
jbe@23
|
1440 --[[--
|
jbe@23
|
1441 db_object =
|
jbe@23
|
1442 <db_object>:get_reference_selector(
|
jbe@23
|
1443 ref_name, -- name of reference (e.g. "children")
|
jbe@23
|
1444 options, -- table options passed to the reference loader (e.g. { order = ... })
|
jbe@23
|
1445 ref_alias, -- optional alias for the reference (e.g. "ordered_children")
|
jbe@23
|
1446 back_ref_alias -- back reference name (e.g. "parent")
|
jbe@23
|
1447 )
|
jbe@23
|
1448
|
jbe@23
|
1449 This method returns a special selector for selecting referenced objects. It is prepared in a way, that on execution of the selector, all returned objects are attached with the objects of the existent list. The "ref" and "back_ref" arguments passed to "add_reference" are used for the attachment, unless aliases are given with "ref_alias" and "back_ref_alias". If "options" are set, these options are passed to the reference loader. The default reference loader supports only one option named "order". If "order" is set to nil, the default order is used, if "order" is set to false, no ORDER BY statment is included in the selector, otherwise the given expression is used for ordering.
|
jbe@23
|
1450
|
jbe@23
|
1451 This method is not only available for database result objects but also for database result lists.
|
jbe@23
|
1452
|
jbe@23
|
1453 --]]--
|
jbe/bsw@0
|
1454 function class_prototype.object:get_reference_selector(...)
|
jbe/bsw@0
|
1455 local list = self._class:create_list()
|
jbe/bsw@0
|
1456 list[1] = self
|
jbe/bsw@0
|
1457 return list:get_reference_selector(...)
|
jbe/bsw@0
|
1458 end
|
jbe@23
|
1459 --//--
|
jbe/bsw@0
|
1460
|
jbe@23
|
1461 --[[--
|
jbe@23
|
1462 db_list_or_object =
|
jbe@23
|
1463 <db_object>:load(
|
jbe@23
|
1464 ref_name, -- name of reference (e.g. "children")
|
jbe@23
|
1465 options, -- table options passed to the reference loader (e.g. { order = ... })
|
jbe@23
|
1466 ref_alias, -- optional alias for the reference (e.g. "ordered_children")
|
jbe@23
|
1467 back_ref_alias -- back reference name (e.g. "parent")
|
jbe@23
|
1468 )
|
jbe@23
|
1469
|
jbe@23
|
1470 This method loads referenced objects and attaches them with the objects of the existent list. The "ref" and "back_ref" arguments passed to "add_reference" are used for the attachment, unless aliases are given with "ref_alias" and "back_ref_alias". If "options" are set, these options are passed to the reference loader. The default reference loader supports only one option named "order". If "order" is set to nil, the default order is used, if "order" is set to false, no ORDER BY statment is included in the selector, otherwise the given expression is used for ordering.
|
jbe@23
|
1471
|
jbe@23
|
1472 This method is not only available for database result objects but also for database result lists. Calling this method for objects is unneccessary, unless additional options and/or an alias is used.
|
jbe@23
|
1473
|
jbe@23
|
1474 --]]--
|
jbe/bsw@0
|
1475 function class_prototype.object.load(...)
|
jbe/bsw@0
|
1476 return class_prototype.object.get_reference_selector(...):exec()
|
jbe/bsw@0
|
1477 end
|
jbe@23
|
1478 --//--
|
jbe/bsw@0
|
1479
|
jbe@23
|
1480 --[[--
|
jbe@23
|
1481 db_class = -- same class returned
|
jbe@23
|
1482 <db_class>:add_reference{
|
jbe@23
|
1483 mode = mode, -- "11", "1m", "m1", or "mm" (one/many to one/many)
|
jbe@543
|
1484 primary = primary, -- only needed if mode=="11": set to true only in the referenced (not in the referencing) class
|
jbe@23
|
1485 to = to, -- referenced class (model), optionally as string or function returning the value (avoids autoload)
|
jbe@23
|
1486 this_key = this_key, -- name of key in this class (model)
|
jbe@23
|
1487 that_key = that_key, -- name of key in the other class (model) ("to" argument)
|
jbe@23
|
1488 ref = ref, -- name of reference in this class, referring to the other class
|
jbe@23
|
1489 back_ref = back_ref, -- name of reference in other class, referring to this class
|
jbe@23
|
1490 default_order = default_order, -- expression as passed to "assemble_command" used for sorting
|
jbe@23
|
1491 selector_generator = selector_generator, -- alternative function used as selector generator (use only, when you know what you are doing)
|
jbe@23
|
1492 connected_by_table = connected_by_table, -- connecting table used for many to many relations
|
jbe@23
|
1493 connected_by_this_key = connected_by_this_key, -- key in connecting table referring to "this_key" of this class (model)
|
jbe@23
|
1494 connected_by_that_key = connected_by_that_key -- key in connecting table referring to "that_key" in other class (model) ("to" argument)
|
jbe@23
|
1495 }
|
jbe/bsw@0
|
1496
|
jbe@23
|
1497 Denotes a reference from one database class to another database class (model to model relation). There are 4 possible types of references: one-to-one (mode = "11"), one-to-many (mode = "1m"), many-to-one ("m1"), and many-to-many ("mm"). References usually should be defined in both models, which are related to each other, with mirrored mode (i.e. "1m" in one model, and "m1" in the other). One-to-one and one-to-many references may have a "back_ref" setting, which causes that loaded objects of the referenced class, refer back to the originating object. One-to-many and many-to-many references may have a "default_order" setting, which selects the default order for selected objects. When adding a many-to-many reference, the argument "connected_by_table", "connected_by_this_key" and "connected_by_that_key" must be set additionally.
|
jbe@23
|
1498
|
jbe@23
|
1499 --]]--
|
jbe/bsw@0
|
1500 function class_prototype:add_reference(args)
|
jbe/bsw@0
|
1501 local selector_generator = args.selector_generator
|
jbe/bsw@0
|
1502 local mode = args.mode
|
jbe@543
|
1503 local primary = args.primary
|
jbe/bsw@0
|
1504 local to = args.to
|
jbe/bsw@0
|
1505 local this_key = args.this_key
|
jbe/bsw@0
|
1506 local that_key = args.that_key
|
jbe/bsw@0
|
1507 local connected_by_table = args.connected_by_table -- TODO: split to table and schema
|
jbe/bsw@0
|
1508 local connected_by_this_key = args.connected_by_this_key
|
jbe/bsw@0
|
1509 local connected_by_that_key = args.connected_by_that_key
|
jbe/bsw@0
|
1510 local ref = args.ref
|
jbe/bsw@0
|
1511 local back_ref = args.back_ref
|
jbe/bsw@0
|
1512 local default_order = args.default_order
|
jbe/bsw@0
|
1513 local model
|
jbe/bsw@0
|
1514 local function get_model()
|
jbe/bsw@0
|
1515 if not model then
|
jbe/bsw@0
|
1516 if type(to) == "string" then
|
jbe/bsw@0
|
1517 model = _G
|
jbe/bsw@0
|
1518 for path_element in string.gmatch(to, "[^.]+") do
|
jbe/bsw@0
|
1519 model = model[path_element]
|
jbe/bsw@0
|
1520 end
|
jbe/bsw@0
|
1521 elseif type(to) == "function" then
|
jbe/bsw@0
|
1522 model = to()
|
jbe/bsw@0
|
1523 else
|
jbe/bsw@0
|
1524 model = to
|
jbe/bsw@0
|
1525 end
|
jbe/bsw@0
|
1526 end
|
jbe/bsw@0
|
1527 if not model or model == _G then
|
jbe/bsw@0
|
1528 error("Could not get model for reference.")
|
jbe/bsw@0
|
1529 end
|
jbe/bsw@0
|
1530 return model
|
jbe/bsw@0
|
1531 end
|
jbe/bsw@0
|
1532 self.references[ref] = {
|
jbe/bsw@0
|
1533 mode = mode,
|
jbe@547
|
1534 primary = primary,
|
jbe/bsw@0
|
1535 this_key = this_key,
|
jbe/bsw@0
|
1536 that_key = connected_by_table and "mm_ref_" or that_key,
|
jbe/bsw@0
|
1537 ref = ref,
|
jbe/bsw@0
|
1538 back_ref = back_ref,
|
jbe/bsw@0
|
1539 selector_generator = selector_generator or function(list, options)
|
jbe/bsw@0
|
1540 -- TODO: support tuple keys
|
jbe/bsw@0
|
1541 local options = options or {}
|
jbe/bsw@0
|
1542 local model = get_model()
|
jbe/bsw@0
|
1543 -- TODO: too many records cause PostgreSQL command stack overflow
|
jbe@532
|
1544 local ids_used = {}
|
jbe/bsw@0
|
1545 local ids = { sep = ", " }
|
jbe/bsw@0
|
1546 for i, object in ipairs(list) do
|
jbe/bsw@0
|
1547 local id = object[this_key]
|
jbe/bsw@0
|
1548 if id ~= nil then
|
jbe@532
|
1549 if not ids_used[id] then
|
jbe@532
|
1550 ids[#ids+1] = {"?", id}
|
jbe@532
|
1551 ids_used[id] = true
|
jbe@532
|
1552 end
|
jbe/bsw@0
|
1553 end
|
jbe/bsw@0
|
1554 end
|
jbe/bsw@0
|
1555 if #ids == 0 then
|
jbe/bsw@0
|
1556 return model:new_selector():empty_list_mode()
|
jbe/bsw@0
|
1557 end
|
jbe/bsw@0
|
1558 local selector = model:new_selector()
|
jbe/bsw@0
|
1559 if connected_by_table then
|
jbe/bsw@0
|
1560 selector:join(
|
jbe/bsw@0
|
1561 connected_by_table,
|
jbe/bsw@0
|
1562 nil,
|
jbe/bsw@0
|
1563 {
|
jbe/bsw@0
|
1564 '$."$" = $."$"',
|
jbe/bsw@0
|
1565 {connected_by_table},
|
jbe/bsw@0
|
1566 {connected_by_that_key},
|
jbe/bsw@0
|
1567 {model:get_qualified_table()},
|
jbe/bsw@0
|
1568 {that_key}
|
jbe/bsw@0
|
1569 }
|
jbe/bsw@0
|
1570 )
|
jbe/bsw@0
|
1571 selector:add_field(
|
jbe/bsw@0
|
1572 {
|
jbe/bsw@0
|
1573 '$."$"',
|
jbe/bsw@0
|
1574 {connected_by_table},
|
jbe/bsw@0
|
1575 {connected_by_this_key}
|
jbe/bsw@0
|
1576 },
|
jbe/bsw@0
|
1577 'mm_ref_'
|
jbe/bsw@0
|
1578 )
|
jbe/bsw@0
|
1579 selector:add_where{
|
jbe/bsw@0
|
1580 '$."$" IN ($)',
|
jbe/bsw@0
|
1581 {connected_by_table},
|
jbe/bsw@0
|
1582 {connected_by_this_key},
|
jbe/bsw@0
|
1583 ids
|
jbe/bsw@0
|
1584 }
|
jbe/bsw@0
|
1585 else
|
jbe/bsw@6
|
1586 selector:add_where{'$."$" IN ($)', {model:get_qualified_table()}, {that_key}, ids}
|
jbe/bsw@0
|
1587 end
|
jbe/bsw@0
|
1588 if options.order == nil and default_order then
|
jbe/bsw@0
|
1589 selector:add_order_by(default_order)
|
jbe/bsw@0
|
1590 elseif options.order then
|
jbe/bsw@0
|
1591 selector:add_order_by(options.order)
|
jbe/bsw@0
|
1592 end
|
jbe/bsw@0
|
1593 return selector
|
jbe/bsw@0
|
1594 end
|
jbe/bsw@0
|
1595 }
|
jbe@543
|
1596 if mode == "m1" or (mode == "11" and not primary) then
|
jbe/bsw@0
|
1597 self.foreign_keys[this_key] = ref
|
jbe/bsw@0
|
1598 end
|
jbe/bsw@0
|
1599 return self
|
jbe/bsw@0
|
1600 end
|
jbe@23
|
1601 --//--
|
jbe@23
|
1602
|
jbe@67
|
1603 return _M
|
jbe@67
|
1604
|