webmcp

view libraries/mondelefant/mondelefant.lua @ 23:3a6fe8663b26

Code cleanup and documentation added; Year in copyright notice changed to 2009-2010

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

Impressum / About Us