webmcp

view libraries/mondelefant/mondelefant.lua @ 51:791849c58105

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

Impressum / About Us