webmcp

view libraries/mondelefant/mondelefant.lua @ 375:fb98b17056e5

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

Impressum / About Us