webmcp

view libraries/mondelefant/mondelefant.lua @ 547:75fd8869bc4e

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

Impressum / About Us