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