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