jbe/bsw@0: #!/usr/bin/env lua jbe/bsw@0: jbe/bsw@0: jbe/bsw@0: --------------------------- jbe/bsw@0: -- module initialization -- jbe/bsw@0: --------------------------- jbe/bsw@0: jbe/bsw@0: local _G = _G jbe/bsw@0: local _VERSION = _VERSION jbe/bsw@0: local assert = assert jbe/bsw@0: local error = error jbe/bsw@0: local getmetatable = getmetatable jbe/bsw@0: local ipairs = ipairs jbe/bsw@0: local next = next jbe/bsw@0: local pairs = pairs jbe/bsw@0: local print = print jbe/bsw@0: local rawequal = rawequal jbe/bsw@0: local rawget = rawget jbe@64: local rawlen = rawlen jbe/bsw@0: local rawset = rawset jbe/bsw@0: local select = select jbe/bsw@0: local setmetatable = setmetatable jbe/bsw@0: local tonumber = tonumber jbe/bsw@0: local tostring = tostring jbe/bsw@0: local type = type jbe/bsw@0: jbe/bsw@0: local math = math jbe/bsw@0: local string = string jbe/bsw@0: local table = table jbe/bsw@0: jbe/bsw@0: local add = table.insert jbe/bsw@0: jbe@64: local _M = require("mondelefant_native") jbe@64: if _ENV then jbe@64: _ENV = _M jbe@64: else jbe@64: _G[...] = _M jbe@64: setfenv(1, _M) jbe@64: end jbe/bsw@0: jbe/bsw@0: jbe/bsw@0: jbe/bsw@0: --------------- jbe/bsw@0: -- selectors -- jbe/bsw@0: --------------- jbe/bsw@0: jbe/bsw@0: selector_metatable = {} jbe/bsw@0: selector_prototype = {} jbe/bsw@0: selector_metatable.__index = selector_prototype jbe/bsw@0: jbe/bsw@0: local function init_selector(self, db_conn) jbe/bsw@0: self._db_conn = db_conn jbe/bsw@0: self._mode = "list" jbe@39: self._with = { sep = ", " } jbe/bsw@0: self._fields = { sep = ", " } jbe/bsw@0: self._distinct = false jbe/bsw@0: self._distinct_on = {sep = ", ", expression} jbe/bsw@0: self._from = { sep = " " } jbe/bsw@4: self._where = { sep = ") AND (" } jbe/bsw@0: self._group_by = { sep = ", " } jbe/bsw@4: self._having = { sep = ") AND (" } jbe/bsw@0: self._combine = { sep = " " } jbe/bsw@0: self._order_by = { sep = ", " } jbe/bsw@0: self._limit = nil jbe/bsw@0: self._offset = nil jbe/bsw@4: self._read_lock = { sep = ", " } jbe/bsw@4: self._write_lock = { sep = ", " } jbe/bsw@0: self._class = nil jbe/bsw@0: self._attach = nil jbe/bsw@0: return self jbe/bsw@0: end jbe/bsw@0: jbe@23: --[[-- jbe@23: selector = -- new selector jbe@23: :new_selector() jbe@23: jbe@23: Creates a new selector to operate on the given database handle. jbe@23: --]]-- jbe/bsw@0: function connection_prototype:new_selector() jbe/bsw@0: return init_selector(setmetatable({}, selector_metatable), self) jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_handle = -- handle of database connection jbe@23: :get_db_conn() jbe@23: jbe@23: Returns the database connection handle used by a selector. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:get_db_conn() jbe/bsw@0: return self._db_conn jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe/bsw@0: -- TODO: selector clone? jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :single_object_mode() jbe@23: jbe@23: Sets selector to single object mode (mode "object" passed to "query" method of database handle). The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:single_object_mode() jbe/bsw@0: self._mode = "object" jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :optional_object_mode() jbe@23: jbe@23: Sets selector to single object mode (mode "opt_object" passed to "query" method of database handle). The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:optional_object_mode() jbe/bsw@0: self._mode = "opt_object" jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :empty_list_mode() jbe@23: jbe@23: Sets selector to empty list mode. The selector is modified and returned. When using the selector, no SQL query will be issued, but instead an empty database result list is returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:empty_list_mode() jbe/bsw@0: self._mode = "empty_list" jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@39: db_selector = jbe@39: :add_with( jbe@39: expression = expression, jbe@39: selector = selector jbe@39: ) jbe@39: jbe@39: Adds an WITH RECURSIVE expression to the selector. The selector is modified and returned. jbe@39: --]]-- jbe@39: function selector_prototype:add_with(expression, selector) jbe@39: add(self._with, {"$ AS ($)", {expression}, {selector}}) jbe@39: return self jbe@39: end jbe@39: --//-- jbe@39: jbe@39: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :add_distinct_on( jbe@23: expression -- expression as passed to "assemble_command" jbe@23: ) jbe@23: jbe@23: Adds an DISTINCT ON expression to the selector. The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:add_distinct_on(expression) jbe/bsw@0: if self._distinct then jbe/bsw@0: error("Can not combine DISTINCT with DISTINCT ON.") jbe/bsw@0: end jbe/bsw@0: add(self._distinct_on, expression) jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :set_distinct() jbe@23: jbe@23: Sets selector to perform a SELECT DISTINCT instead of SELECT (ALL). The selector is modified and returned. This mode can not be combined with DISTINCT ON. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:set_distinct() jbe/bsw@0: if #self._distinct_on > 0 then jbe/bsw@0: error("Can not combine DISTINCT with DISTINCT ON.") jbe/bsw@0: end jbe/bsw@0: self._distinct = true jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :add_from( jbe@23: expression, -- expression as passed to "assemble_command" jbe@23: alias, -- optional alias expression as passed to "assemble_command" jbe@23: condition -- optional condition expression as passed to "assemble_command" jbe@23: ) jbe@23: jbe@23: Adds expressions for FROM clause to the selector. The selector is modified and returned. If an additional condition is given, an INNER JOIN will be used, otherwise a CROSS JOIN. jbe@23: jbe@23: This method is identical to "join". jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:add_from(expression, alias, condition) jbe/bsw@0: local first = (#self._from == 0) jbe/bsw@0: if not first then jbe/bsw@0: if condition then jbe/bsw@0: add(self._from, "INNER JOIN") jbe/bsw@0: else jbe/bsw@0: add(self._from, "CROSS JOIN") jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: if getmetatable(expression) == selector_metatable then jbe/bsw@0: if alias then jbe/bsw@0: add(self._from, {'($) AS "$"', {expression}, {alias}}) jbe/bsw@0: else jbe/bsw@0: add(self._from, {'($) AS "subquery"', {expression}}) jbe/bsw@0: end jbe/bsw@0: else jbe/bsw@0: if alias then jbe/bsw@0: add(self._from, {'$ AS "$"', {expression}, {alias}}) jbe/bsw@0: else jbe/bsw@0: add(self._from, expression) jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: if condition then jbe/bsw@0: if first then jbe@55: self:add_where(condition) jbe/bsw@0: else jbe/bsw@0: add(self._from, "ON") jbe/bsw@0: add(self._from, condition) jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :add_where( jbe@23: expression -- expression as passed to "assemble_command" jbe@23: ) jbe@23: jbe@23: Adds expressions for WHERE clause to the selector. The selector is modified and returned. Multiple calls cause expressions to be AND-combined. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:add_where(expression) jbe/bsw@0: add(self._where, expression) jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :add_group_by( jbe@23: expression -- expression as passed to "assemble_command" jbe@23: ) jbe@23: jbe@23: Adds expressions for GROUP BY clause to the selector. The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:add_group_by(expression) jbe/bsw@0: add(self._group_by, expression) jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :add_having( jbe@23: expression -- expression as passed to "assemble_command" jbe@23: ) jbe@23: jbe@23: Adds expressions for HAVING clause to the selector. The selector is modified and returned. Multiple calls cause expressions to be AND-combined. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:add_having(expression) jbe/bsw@0: add(self._having, expression) jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :add_combine( jbe@23: expression -- expression as passed to "assemble_command" jbe@23: ) jbe@23: jbe@23: This function is used for UNION/INTERSECT/EXCEPT clauses. It does not need to be called directly. Use "union", "union_all", "intersect", "intersect_all", "except" and "except_all" instead. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:add_combine(expression) jbe/bsw@0: add(self._combine, expression) jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :add_order_by( jbe@23: expression -- expression as passed to "assemble_command" jbe@23: ) jbe@23: jbe@23: Adds expressions for ORDER BY clause to the selector. The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:add_order_by(expression) jbe/bsw@0: add(self._order_by, expression) jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :limit( jbe@23: count -- integer used as LIMIT jbe@23: ) jbe@23: jbe@23: Limits the number of rows to a given number, by using LIMIT. The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:limit(count) jbe/bsw@0: if type(count) ~= "number" or count % 1 ~= 0 then jbe/bsw@0: error("LIMIT must be an integer.") jbe/bsw@0: end jbe/bsw@0: self._limit = count jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :offset( jbe@23: count -- integer used as OFFSET jbe@23: ) jbe@23: jbe@23: Skips a given number of rows, by using OFFSET. The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:offset(count) jbe/bsw@0: if type(count) ~= "number" or count % 1 ~= 0 then jbe/bsw@0: error("OFFSET must be an integer.") jbe/bsw@0: end jbe/bsw@0: self._offset = count jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :for_share() jbe@23: jbe@23: Adds FOR SHARE to the statement, to share-lock all rows read. The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@4: function selector_prototype:for_share() jbe/bsw@4: self._read_lock.all = true jbe/bsw@4: return self jbe/bsw@4: end jbe@23: --//-- jbe/bsw@4: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :for_share_of( jbe@23: expression -- expression as passed to "assemble_command" jbe@23: ) jbe@23: jbe@23: Adds FOR SHARE OF to the statement, to share-lock all rows read by the named table(s). The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@4: function selector_prototype:for_share_of(expression) jbe/bsw@4: add(self._read_lock, expression) jbe/bsw@4: return self jbe/bsw@4: end jbe@23: --//-- jbe/bsw@4: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :for_update() jbe@23: jbe@23: Adds FOR UPDATE to the statement, to exclusivly lock all rows read. The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@4: function selector_prototype:for_update() jbe/bsw@4: self._write_lock.all = true jbe/bsw@4: return self jbe/bsw@4: end jbe@23: --//-- jbe/bsw@4: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :for_update_of( jbe@23: expression -- expression as passed to "assemble_command" jbe@23: ) jbe@23: jbe@23: Adds FOR SHARE OF to the statement, to exclusivly lock all rows read by the named table(s). The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@4: function selector_prototype:for_update_of(expression) jbe/bsw@4: add(self._write_lock, expression) jbe/bsw@4: return self jbe/bsw@4: end jbe@23: --//-- jbe/bsw@4: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :reset_fields() jbe@23: jbe@23: This method removes all fields added by method "add_field". The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:reset_fields() jbe/bsw@0: for idx in ipairs(self._fields) do jbe/bsw@0: self._fields[idx] = nil jbe/bsw@0: end jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :add_field( jbe@23: expression, -- expression as passed to "assemble_command" jbe@23: alias, -- optional alias expression as passed to "assemble_command" jbe@23: option_list -- optional list of options (may contain strings "distinct" or "grouped") jbe@23: ) jbe@23: jbe@23: Adds fields to the selector. The selector is modified and returned. The third argument can be a list of options. If option "distinct" is given, then "add_distinct_on" will be executed for the given field or alias. If option "grouped" is given, then "add_group_by" will be executed for the given field or alias. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:add_field(expression, alias, options) jbe/bsw@0: if alias then jbe/bsw@0: add(self._fields, {'$ AS "$"', {expression}, {alias}}) jbe/bsw@0: else jbe/bsw@0: add(self._fields, expression) jbe/bsw@0: end jbe/bsw@0: if options then jbe/bsw@0: for i, option in ipairs(options) do jbe/bsw@0: if option == "distinct" then jbe/bsw@0: if alias then jbe/bsw@0: self:add_distinct_on('"' .. alias .. '"') jbe/bsw@0: else jbe/bsw@0: self:add_distinct_on(expression) jbe/bsw@0: end jbe/bsw@0: elseif option == "grouped" then jbe/bsw@0: if alias then jbe/bsw@0: self:add_group_by('"' .. alias .. '"') jbe/bsw@0: else jbe/bsw@0: self:add_group_by(expression) jbe/bsw@0: end jbe/bsw@0: else jbe/bsw@0: error("Unknown option '" .. option .. "' to add_field method.") jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :join( jbe@23: expression, -- expression as passed to "assemble_command" jbe@23: alias, -- optional alias expression as passed to "assemble_command" jbe@23: condition -- optional condition expression as passed to "assemble_command" jbe@23: ) jbe@23: jbe@23: Adds expressions for FROM clause to the selector. The selector is modified and returned. If an additional condition is given, an INNER JOIN will be used, otherwise a CROSS JOIN. jbe@23: jbe@23: This method is identical to "add_from". jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:join(...) -- NOTE: alias for add_from jbe/bsw@0: return self:add_from(...) jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :from( jbe@23: expression, -- expression as passed to "assemble_command" jbe@23: alias, -- optional alias expression as passed to "assemble_command" jbe@23: condition -- optional condition expression as passed to "assemble_command" jbe@23: ) jbe@23: jbe@23: Adds the first expression for FROM clause to the selector. The selector is modified and returned. If an additional condition is given, an INNER JOIN will be used, otherwise a CROSS JOIN. jbe@23: jbe@23: This method is identical to "add_from" or "join", except that an error is thrown, if there is already any FROM expression existent. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:from(expression, alias, condition) jbe/bsw@0: if #self._from > 0 then jbe/bsw@0: error("From-clause already existing (hint: try join).") jbe/bsw@0: end jbe/bsw@0: return self:join(expression, alias, condition) jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :left_join( jbe@23: expression, -- expression as passed to "assemble_command" jbe@23: alias, -- optional alias expression as passed to "assemble_command" jbe@23: condition -- optional condition expression as passed to "assemble_command" jbe@23: ) jbe@23: jbe@23: Adds expressions for FROM clause to the selector using a LEFT OUTER JOIN. The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:left_join(expression, alias, condition) jbe/bsw@0: local first = (#self._from == 0) jbe/bsw@0: if not first then jbe/bsw@0: add(self._from, "LEFT OUTER JOIN") jbe/bsw@0: end jbe/bsw@0: if alias then jbe/bsw@0: add(self._from, {'$ AS "$"', {expression}, {alias}}) jbe/bsw@0: else jbe/bsw@0: add(self._from, expression) jbe/bsw@0: end jbe/bsw@0: if condition then jbe/bsw@0: if first then jbe@55: self:add_where(condition) jbe/bsw@0: else jbe/bsw@0: add(self._from, "ON") jbe/bsw@0: add(self._from, condition) jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :union( jbe@23: expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE jbe@23: ) jbe@23: jbe@348: 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. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:union(expression) jbe/bsw@0: self:add_combine{"UNION $", {expression}} jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :union_all( jbe@23: expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE jbe@23: ) jbe@23: jbe@348: 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. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:union_all(expression) jbe/bsw@0: self:add_combine{"UNION ALL $", {expression}} jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :intersect( jbe@23: expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE jbe@23: ) jbe@23: jbe@23: This method adds an INTERSECT clause to the given selector. The selector is modified and returned. The selector (or expression) passed as argument to this function shall not contain any ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clauses. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:intersect(expression) jbe/bsw@0: self:add_combine{"INTERSECT $", {expression}} jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :intersect_all( jbe@23: expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE jbe@23: ) jbe@23: jbe@23: This method adds an INTERSECT ALL clause to the given selector. The selector is modified and returned. The selector (or expression) passed as argument to this function shall not contain any ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clauses. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:intersect_all(expression) jbe/bsw@0: self:add_combine{"INTERSECT ALL $", {expression}} jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :except( jbe@23: expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE jbe@23: ) jbe@23: jbe@23: This method adds an EXCEPT clause to the given selector. The selector is modified and returned. The selector (or expression) passed as argument to this function shall not contain any ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clauses. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:except(expression) jbe/bsw@0: self:add_combine{"EXCEPT $", {expression}} jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :except_all( jbe@23: expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE jbe@23: ) jbe@23: jbe@23: This method adds an EXCEPT ALL clause to the given selector. The selector is modified and returned. The selector (or expression) passed as argument to this function shall not contain any ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clauses. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:except_all(expression) jbe/bsw@0: self:add_combine{"EXCEPT ALL $", {expression}} jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :set_class( jbe@23: class -- database class (model) jbe@23: ) jbe@23: jbe@23: This method makes the selector to return database result lists or objects of the given database class (model). The selector is modified and returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:set_class(class) jbe/bsw@0: self._class = class jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = -- same selector returned jbe@23: :attach( jbe@23: mode, -- attachment type: "11" one to one, "1m" one to many, "m1" many to one jbe@23: data2, -- other database result list or object, the results of this selector shall be attached with jbe@23: field1, -- field name(s) in result list or object of this selector used for attaching jbe@23: field2, -- field name(s) in "data2" used for attaching jbe@23: ref1, -- name of reference field in the results of this selector after attaching jbe@23: ref2 -- name of reference field in "data2" after attaching jbe@23: ) jbe@23: jbe@23: This method causes database result lists or objects of this selector to be attached with other database result lists after execution. This method does not need to be called directly. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:attach(mode, data2, field1, field2, ref1, ref2) jbe/bsw@0: self._attach = { jbe/bsw@0: mode = mode, jbe/bsw@0: data2 = data2, jbe/bsw@0: field1 = field1, jbe/bsw@0: field2 = field2, jbe/bsw@0: ref1 = ref1, jbe/bsw@0: ref2 = ref2 jbe/bsw@0: } jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe/bsw@0: function selector_metatable:__tostring() jbe/bsw@0: local parts = {sep = " "} jbe@39: if #self._with > 0 then jbe@39: add(parts, {"WITH RECURSIVE $", self._with}) jbe@39: end jbe/bsw@0: add(parts, "SELECT") jbe/bsw@0: if self._distinct then jbe/bsw@0: add(parts, "DISTINCT") jbe/bsw@0: elseif #self._distinct_on > 0 then jbe/bsw@0: add(parts, {"DISTINCT ON ($)", self._distinct_on}) jbe/bsw@0: end jbe/bsw@0: add(parts, {"$", self._fields}) jbe/bsw@0: if #self._from > 0 then jbe/bsw@0: add(parts, {"FROM $", self._from}) jbe/bsw@0: end jbe/bsw@0: if #self._mode == "empty_list" then jbe/bsw@0: add(parts, "WHERE FALSE") jbe/bsw@0: elseif #self._where > 0 then jbe/bsw@4: add(parts, {"WHERE ($)", self._where}) jbe/bsw@0: end jbe/bsw@0: if #self._group_by > 0 then jbe/bsw@0: add(parts, {"GROUP BY $", self._group_by}) jbe/bsw@0: end jbe/bsw@0: if #self._having > 0 then jbe/bsw@4: add(parts, {"HAVING ($)", self._having}) jbe/bsw@0: end jbe/bsw@0: for i, v in ipairs(self._combine) do jbe/bsw@0: add(parts, v) jbe/bsw@0: end jbe/bsw@0: if #self._order_by > 0 then jbe/bsw@0: add(parts, {"ORDER BY $", self._order_by}) jbe/bsw@0: end jbe/bsw@0: if self._mode == "empty_list" then jbe/bsw@0: add(parts, "LIMIT 0") jbe/bsw@0: elseif self._mode ~= "list" then jbe/bsw@0: add(parts, "LIMIT 1") jbe/bsw@0: elseif self._limit then jbe/bsw@0: add(parts, "LIMIT " .. self._limit) jbe/bsw@0: end jbe/bsw@0: if self._offset then jbe/bsw@0: add(parts, "OFFSET " .. self._offset) jbe/bsw@0: end jbe/bsw@4: if self._write_lock.all then jbe/bsw@4: add(parts, "FOR UPDATE") jbe/bsw@4: else jbe/bsw@4: if self._read_lock.all then jbe/bsw@4: add(parts, "FOR SHARE") jbe/bsw@4: elseif #self._read_lock > 0 then jbe/bsw@4: add(parts, {"FOR SHARE OF $", self._read_lock}) jbe/bsw@4: end jbe/bsw@4: if #self._write_lock > 0 then jbe/bsw@4: add(parts, {"FOR UPDATE OF $", self._write_lock}) jbe/bsw@4: end jbe/bsw@4: end jbe/bsw@0: return self._db_conn:assemble_command{"$", parts} jbe/bsw@0: end jbe/bsw@0: jbe@23: --[[-- jbe@23: db_error, -- database error object, or nil in case of success jbe@23: result = -- database result list or object jbe@23: :try_exec() jbe@23: jbe@23: This method executes the selector on its database. First return value is an error object or nil in case of success. Second return value is the result list or object. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:try_exec() jbe/bsw@0: if self._mode == "empty_list" then jbe/bsw@0: if self._class then jbe/bsw@0: return nil, self._class:create_list() jbe/bsw@0: else jbe/bsw@0: return nil, self._db_conn:create_list() jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: local db_error, db_result = self._db_conn:try_query(self, self._mode) jbe/bsw@0: if db_error then jbe/bsw@0: return db_error jbe/bsw@0: elseif db_result then jbe/bsw@0: if self._class then set_class(db_result, self._class) end jbe/bsw@0: if self._attach then jbe/bsw@0: attach( jbe/bsw@0: self._attach.mode, jbe/bsw@0: db_result, jbe/bsw@0: self._attach.data2, jbe/bsw@0: self._attach.field1, jbe/bsw@0: self._attach.field2, jbe/bsw@0: self._attach.ref1, jbe/bsw@0: self._attach.ref2 jbe/bsw@0: ) jbe/bsw@0: end jbe/bsw@0: return nil, db_result jbe/bsw@0: else jbe/bsw@0: return nil jbe/bsw@0: end jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: result = -- database result list or object jbe@23: :exec() jbe@23: jbe@23: This method executes the selector on its database. The result list or object is returned on success, otherwise an error is thrown. jbe@23: jbe@23: --]]-- jbe/bsw@0: function selector_prototype:exec() jbe/bsw@0: local db_error, result = self:try_exec() jbe/bsw@0: if db_error then jbe/bsw@0: db_error:escalate() jbe/bsw@0: else jbe/bsw@0: return result jbe/bsw@0: end jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: count = -- number of rows returned jbe@23: :count() jbe@23: jbe@23: This function wraps the given selector inside a subquery to count the number of rows returned by the database. NOTE: The result is cached inside the selector, thus the selector should NOT be modified afterwards. jbe@23: jbe@23: --]]-- jbe/bsw@4: function selector_prototype:count() jbe/bsw@4: if not self._count then jbe/bsw@4: local count_selector = self:get_db_conn():new_selector() jbe/bsw@4: count_selector:add_field('count(1)') jbe/bsw@4: count_selector:add_from(self) jbe/bsw@4: count_selector:single_object_mode() jbe/bsw@4: self._count = count_selector:exec().count jbe/bsw@4: end jbe/bsw@4: return self._count jbe/bsw@4: end jbe@23: --//-- jbe/bsw@4: jbe/bsw@0: jbe/bsw@0: jbe/bsw@0: ----------------- jbe/bsw@0: -- attachments -- jbe/bsw@0: ----------------- jbe/bsw@0: jbe/bsw@0: local function attach_key(row, fields) jbe/bsw@0: local t = type(fields) jbe/bsw@0: if t == "string" then jbe/bsw@0: return tostring(row[fields]) jbe/bsw@0: elseif t == "table" then jbe/bsw@0: local r = {} jbe/bsw@0: for idx, field in ipairs(fields) do jbe/bsw@0: r[idx] = string.format("%q", row[field]) jbe/bsw@0: end jbe/bsw@0: return table.concat(r) jbe/bsw@0: else jbe/bsw@0: error("Field information for 'mondelefant.attach' is neither a string nor a table.") jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: jbe@23: --[[-- jbe@23: mondelefant.attach( jbe@23: mode, -- attachment type: "11" one to one, "1m" one to many, "m1" many to one jbe@23: data1, -- first database result list or object jbe@23: data2, -- second database result list or object jbe@23: key1, -- field name(s) in first result list or object used for attaching jbe@23: key2, -- field name(s) in second result list or object used for attaching jbe@23: ref1, -- name of reference field to be set in first database result list or object jbe@23: ref2 -- name of reference field to be set in second database result list or object jbe@23: ) jbe@23: jbe@23: This function attaches database result lists/objects with each other. It does not need to be called directly. jbe@23: jbe@23: --]]-- jbe/bsw@0: function attach(mode, data1, data2, key1, key2, ref1, ref2) jbe/bsw@0: local many1, many2 jbe/bsw@0: if mode == "11" then jbe/bsw@0: many1 = false jbe/bsw@0: many2 = false jbe/bsw@0: elseif mode == "1m" then jbe/bsw@0: many1 = false jbe/bsw@0: many2 = true jbe/bsw@0: elseif mode == "m1" then jbe/bsw@0: many1 = true jbe/bsw@0: many2 = false jbe/bsw@0: elseif mode == "mm" then jbe/bsw@0: many1 = true jbe/bsw@0: many2 = true jbe/bsw@0: else jbe/bsw@0: error("Unknown mode specified for 'mondelefant.attach'.") jbe/bsw@0: end jbe/bsw@0: local list1, list2 jbe/bsw@0: if data1._type == "object" then jbe/bsw@0: list1 = { data1 } jbe/bsw@0: elseif data1._type == "list" then jbe/bsw@0: list1 = data1 jbe/bsw@0: else jbe/bsw@0: error("First result data given to 'mondelefant.attach' is invalid.") jbe/bsw@0: end jbe/bsw@0: if data2._type == "object" then jbe/bsw@0: list2 = { data2 } jbe/bsw@0: elseif data2._type == "list" then jbe/bsw@0: list2 = data2 jbe/bsw@0: else jbe/bsw@0: error("Second result data given to 'mondelefant.attach' is invalid.") jbe/bsw@0: end jbe/bsw@0: local hash1 = {} jbe/bsw@0: local hash2 = {} jbe/bsw@0: if ref2 then jbe/bsw@0: for i, row in ipairs(list1) do jbe/bsw@0: local key = attach_key(row, key1) jbe/bsw@0: local list = hash1[key] jbe/bsw@0: if not list then list = {}; hash1[key] = list end jbe/bsw@0: list[#list + 1] = row jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: if ref1 then jbe/bsw@0: for i, row in ipairs(list2) do jbe/bsw@0: local key = attach_key(row, key2) jbe/bsw@0: local list = hash2[key] jbe/bsw@0: if not list then list = {}; hash2[key] = list end jbe/bsw@0: list[#list + 1] = row jbe/bsw@0: end jbe/bsw@0: for i, row in ipairs(list1) do jbe/bsw@0: local key = attach_key(row, key1) jbe/bsw@0: local matching_rows = hash2[key] jbe/bsw@0: if many2 then jbe/bsw@0: local list = data2._connection:create_list(matching_rows) jbe/bsw@0: list._class = data2._class jbe/bsw@0: row._ref[ref1] = list jbe/bsw@0: elseif matching_rows and #matching_rows == 1 then jbe/bsw@0: row._ref[ref1] = matching_rows[1] jbe/bsw@0: else jbe/bsw@0: row._ref[ref1] = false jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: if ref2 then jbe/bsw@0: for i, row in ipairs(list2) do jbe/bsw@0: local key = attach_key(row, key2) jbe/bsw@0: local matching_rows = hash1[key] jbe/bsw@0: if many1 then jbe/bsw@0: local list = data1._connection:create_list(matching_rows) jbe/bsw@0: list._class = data1._class jbe/bsw@0: row._ref[ref2] = list jbe/bsw@0: elseif matching_rows and #matching_rows == 1 then jbe/bsw@0: row._ref[ref2] = matching_rows[1] jbe/bsw@0: else jbe/bsw@0: row._ref[ref2] = false jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe/bsw@0: jbe/bsw@0: jbe/bsw@0: ------------------ jbe/bsw@0: -- model system -- jbe/bsw@0: ------------------ jbe/bsw@0: jbe@23: --[[-- jbe@23: .primary_key jbe@23: jbe@23: Primary key of a database class (model). Defaults to "id". jbe@23: jbe@376: If the primary key is a tuple, then a sequence (table with integer keys mapped to the column names) must be used. If the primary key is contained in a JSON document within a table column, then a special object with the following fields is expected: {json_doc = "column_name", key = "field_name_within_json_object", type = "postgresql_type"}. jbe@376: jbe@23: --]]-- jbe/bsw@0: class_prototype.primary_key = "id" jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@376: .document_column jbe@376: jbe@376: Optional column name to redirect key lookups to. This can be used to allow for an easier access to fields of a JSON document. jbe@376: jbe@376: --]]-- jbe@376: class_prototype.document_column = nil jbe@376: --//-- jbe@376: jbe@376: --[[-- jbe@23: db_handle = -- database connection handle used by this class jbe@23: :get_db_conn() jbe@23: jbe@23: By implementing this method for a particular model or overwriting it in the default prototype "mondelefant.class_prototype", classes are connected with a particular database. This method needs to return a database connection handle. If it is not overwritten, an error is thrown, when invoking this method. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype:get_db_conn() jbe/bsw@0: error( jbe/bsw@0: "Method mondelefant class(_prototype):get_db_conn() " .. jbe/bsw@0: "has to be implemented." jbe/bsw@0: ) jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: string = -- string of form '"schemaname"."tablename"' or '"tablename"' jbe@23: :get_qualified_table() jbe@23: jbe@23: This method returns a string with the (double quoted) qualified table name used to store objects of this class. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype:get_qualified_table() jbe/bsw@0: if not self.table then error "Table unknown." end jbe/bsw@0: if self.schema then jbe/bsw@0: return '"' .. self.schema .. '"."' .. self.table .. '"' jbe/bsw@0: else jbe/bsw@0: return '"' .. self.table .. '"' jbe/bsw@0: end jbe/bsw@0: end jbe@23: --]]-- jbe/bsw@0: jbe@23: --[[-- jbe@23: string = -- single quoted string of form "'schemaname.tablename'" or "'tablename'" jbe@23: :get_qualified_table_literal() jbe@23: jbe@23: This method returns a string with an SQL literal representing the given table. It causes ambiguities when the table name contains a dot (".") character. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype:get_qualified_table_literal() jbe/bsw@0: if not self.table then error "Table unknown." end jbe/bsw@0: if self.schema then jbe/bsw@0: return self.schema .. '.' .. self.table jbe/bsw@0: else jbe/bsw@0: return self.table jbe/bsw@0: end jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: list = -- list of column names of primary key jbe@23: :get_primary_key_list() jbe@23: jbe@23: This method returns a list of column names of the primary key. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype:get_primary_key_list() jbe/bsw@0: local primary_key = self.primary_key jbe/bsw@0: if type(primary_key) == "string" then jbe/bsw@0: return {primary_key} jbe/bsw@0: else jbe/bsw@0: return primary_key jbe/bsw@0: end jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: columns = -- list of columns jbe@23: :get_columns() jbe@23: jbe@23: This method returns a list of column names of the table used for the class. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype:get_columns() jbe/bsw@0: if self._columns then jbe/bsw@0: return self._columns jbe/bsw@0: end jbe/bsw@0: local selector = self:get_db_conn():new_selector() jbe/bsw@0: selector:set_class(self) jbe/bsw@0: selector:from(self:get_qualified_table()) jbe/bsw@0: selector:add_field("*") jbe/bsw@0: selector:add_where("FALSE") jbe/bsw@0: local db_result = selector:exec() jbe/bsw@0: local connection = db_result._connection jbe/bsw@0: local columns = {} jbe/bsw@0: for idx, info in ipairs(db_result._column_info) do jbe/bsw@0: local key = info.field_name jbe/bsw@0: local value = { jbe/bsw@0: name = key, jbe/bsw@0: type = connection.type_mappings[info.type] jbe/bsw@0: } jbe/bsw@0: columns[key] = value jbe/bsw@0: table.insert(columns, value) jbe/bsw@0: end jbe/bsw@0: self._columns = columns jbe/bsw@0: return columns jbe/bsw@0: end jbe@25: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: selector = -- new selector for selecting objects of this class jbe@23: :new_selector( jbe@23: db_conn -- optional(!) database connection handle, defaults to result of :get_db_conn() jbe@23: ) jbe@23: jbe@23: This method creates a new selector for selecting objects of the class. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype:new_selector(db_conn) jbe/bsw@0: local selector = (db_conn or self:get_db_conn()):new_selector() jbe/bsw@0: selector:set_class(self) jbe/bsw@0: selector:from(self:get_qualified_table()) jbe/bsw@0: selector:add_field(self:get_qualified_table() .. ".*") jbe/bsw@0: return selector jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_list = -- database result being an empty list jbe@23: :create_list() jbe@23: jbe@23: Creates an empty database result representing a list of objects of the given class. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype:create_list() jbe/bsw@0: local list = self:get_db_conn():create_list() jbe/bsw@0: list._class = self jbe/bsw@0: return list jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_object = -- database object (instance of model) jbe@23: :new() jbe@23: jbe@23: Creates a new object of the given class. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype:new() jbe/bsw@0: local object = self:get_db_conn():create_object() jbe/bsw@0: object._class = self jbe/bsw@0: object._new = true jbe/bsw@0: return object jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_error = -- database error object, or nil in case of success jbe@23: :try_save() jbe@23: jbe@23: This method saves changes to an object in the database. Returns nil on success, otherwise an error object is returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype.object:try_save() jbe/bsw@0: if not self._class then jbe/bsw@0: error("Cannot save object: No class information available.") jbe/bsw@0: end jbe/bsw@0: local primary_key = self._class:get_primary_key_list() jbe/bsw@0: local primary_key_sql = { sep = ", " } jbe@373: if primary_key.json_doc then jbe@373: primary_key_sql[1] = { jbe@373: '("$"->>?)::$ AS "json_key"', jbe@373: {primary_key.json_doc}, primary_key.key, {primary_key.type} jbe@373: } jbe@373: else jbe@373: for idx, value in ipairs(primary_key) do jbe@373: primary_key_sql[idx] = '"' .. value .. '"' jbe@373: end jbe/bsw@0: end jbe/bsw@0: if self._new then jbe/bsw@0: local fields = {sep = ", "} jbe/bsw@0: local values = {sep = ", "} jbe@375: for key in pairs(self._dirty or {}) do jbe/bsw@0: add(fields, {'"$"', {key}}) jbe/bsw@0: add(values, {'?', self[key]}) jbe/bsw@0: end jbe@373: local db_error, db_result jbe@373: if #fields == 0 then jbe@373: db_error, db_result = self._connection:try_query( jbe/bsw@0: { jbe@373: 'INSERT INTO $ DEFAULT VALUES RETURNING $', jbe@373: {self._class:get_qualified_table()}, jbe@373: primary_key_sql jbe@373: }, jbe@373: "object" jbe@373: ) jbe@373: else jbe@373: db_error, db_result = self._connection:try_query( jbe@373: { jbe@373: 'INSERT INTO $ ($) VALUES ($) RETURNING $', jbe/bsw@0: {self._class:get_qualified_table()}, jbe/bsw@0: fields, jbe/bsw@0: values, jbe/bsw@0: primary_key_sql jbe/bsw@0: }, jbe/bsw@0: "object" jbe/bsw@0: ) jbe@373: end jbe@373: if db_error then jbe@373: return db_error jbe@373: end jbe@373: if primary_key.json_doc then jbe@373: self[primary_key.json_doc][primary_key.key] = db_result.json_key jbe/bsw@0: else jbe/bsw@0: for idx, value in ipairs(primary_key) do jbe/bsw@0: self[value] = db_result[value] jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: self._new = false jbe/bsw@0: else jbe/bsw@0: local command_sets = {sep = ", "} jbe@375: for key, mutability_state in pairs(self._dirty or {}) do jbe@375: if jbe@375: mutability_state == true or ( jbe@375: verify_mutability_state and jbe@375: verify_mutability_state(self[key], mutability_state) jbe@375: ) jbe@375: then jbe@375: add(command_sets, {'"$" = ?', {key}, self[key]}) jbe@375: self._dirty[key] = true -- always dirty in case of later error jbe@375: end jbe/bsw@0: end jbe/bsw@0: if #command_sets >= 1 then jbe/bsw@0: local primary_key_compare = {sep = " AND "} jbe@373: if primary_key.json_doc then jbe@373: primary_key_compare[1] = { jbe@373: '("$"->>?)::$ = ?', jbe@373: {primary_key.json_doc}, primary_key.key, {primary_key.type}, jbe@373: self[primary_key.json_doc][primary_key.key] jbe/bsw@0: } jbe@373: else jbe@373: for idx, value in ipairs(primary_key) do jbe@373: primary_key_compare[idx] = { jbe@373: "$ = ?", jbe@373: {'"' .. value .. '"'}, jbe@373: self[value] jbe@373: } jbe@373: end jbe/bsw@0: end jbe/bsw@0: local db_error = self._connection:try_query{ jbe/bsw@0: 'UPDATE $ SET $ WHERE $', jbe/bsw@0: {self._class:get_qualified_table()}, jbe/bsw@0: command_sets, jbe/bsw@0: primary_key_compare jbe/bsw@0: } jbe/bsw@0: if db_error then jbe/bsw@0: return db_error jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: end jbe@375: for key in pairs(self._dirty or {}) do jbe@375: if save_mutability_state then jbe@375: self._dirty[key] = jbe@375: save_mutability_state and save_mutability_state(self[key]) or nil jbe@375: end jbe@375: end jbe/bsw@0: return nil jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: :save() jbe@23: jbe@23: This method saves changes to an object in the database. Throws error, unless successful. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype.object:save() jbe/bsw@0: local db_error = self:try_save() jbe/bsw@0: if db_error then jbe/bsw@0: db_error:escalate() jbe/bsw@0: end jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_error = -- database error object, or nil in case of success jbe@23: :try_destroy() jbe@23: jbe@23: This method deletes an object in the database. Returns nil on success, otherwise an error object is returned. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype.object:try_destroy() jbe/bsw@0: if not self._class then jbe/bsw@0: error("Cannot destroy object: No class information available.") jbe/bsw@0: end jbe/bsw@0: local primary_key = self._class:get_primary_key_list() jbe/bsw@0: local primary_key_compare = {sep = " AND "} jbe@373: if primary_key.json_doc then jbe@373: primary_key_compare[1] = { jbe@373: '("$"->>?)::$ = ?', jbe@373: {primary_key.json_doc}, primary_key.key, {primary_key.type}, jbe@373: self[primary_key.json_doc][primary_key.key] jbe/bsw@0: } jbe@373: else jbe@373: for idx, value in ipairs(primary_key) do jbe@373: primary_key_compare[idx] = { jbe@373: "$ = ?", jbe@373: {'"' .. value .. '"'}, jbe@373: self[value] jbe@373: } jbe@373: end jbe/bsw@0: end jbe/bsw@0: return self._connection:try_query{ jbe/bsw@0: 'DELETE FROM $ WHERE $', jbe/bsw@0: {self._class:get_qualified_table()}, jbe/bsw@0: primary_key_compare jbe/bsw@0: } jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: :destroy() jbe@23: jbe@23: This method deletes an object in the database. Throws error, unless successful. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype.object:destroy() jbe/bsw@0: local db_error = self:try_destroy() jbe/bsw@0: if db_error then jbe/bsw@0: db_error:escalate() jbe/bsw@0: end jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_selector = jbe@23: :get_reference_selector( jbe@23: ref_name, -- name of reference (e.g. "children") jbe@23: options, -- table options passed to the reference loader (e.g. { order = ... }) jbe@23: ref_alias, -- optional alias for the reference (e.g. "ordered_children") jbe@23: back_ref_alias -- back reference name (e.g. "parent") jbe@23: ) jbe@23: jbe@23: This method returns a special selector for selecting referenced objects. It is prepared in a way, that on execution of the selector, all returned objects are attached with the objects of the existent list. The "ref" and "back_ref" arguments passed to "add_reference" are used for the attachment, unless aliases are given with "ref_alias" and "back_ref_alias". If "options" are set, these options are passed to the reference loader. The default reference loader supports only one option named "order". If "order" is set to nil, the default order is used, if "order" is set to false, no ORDER BY statment is included in the selector, otherwise the given expression is used for ordering. jbe@23: jbe@23: This method is not only available for database result lists but also for database result objects. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype.list:get_reference_selector( jbe/bsw@0: ref_name, options, ref_alias, back_ref_alias jbe/bsw@0: ) jbe/bsw@0: local ref_info = self._class.references[ref_name] jbe/bsw@0: if not ref_info then jbe/bsw@0: error('Reference with name "' .. ref_name .. '" not found.') jbe/bsw@0: end jbe/bsw@0: local selector = ref_info.selector_generator(self, options or {}) jbe/bsw@0: local mode = ref_info.mode jbe/bsw@0: if mode == "mm" or mode == "1m" then jbe/bsw@0: mode = "m1" jbe/bsw@0: elseif mode == "m1" then jbe/bsw@0: mode = "1m" jbe/bsw@0: end jbe/bsw@0: local ref_alias = ref_alias jbe/bsw@0: if ref_alias == false then jbe/bsw@0: ref_alias = nil jbe/bsw@0: elseif ref_alias == nil then jbe/bsw@0: ref_alias = ref_name jbe/bsw@0: end jbe/bsw@0: local back_ref_alias jbe/bsw@0: if back_ref_alias == false then jbe/bsw@0: back_ref_alias = nil jbe/bsw@0: elseif back_ref_alias == nil then jbe/bsw@0: back_ref_alias = ref_info.back_ref jbe/bsw@0: end jbe/bsw@0: selector:attach( jbe/bsw@0: mode, jbe/bsw@0: self, jbe/bsw@0: ref_info.that_key, ref_info.this_key, jbe/bsw@0: back_ref_alias or ref_info.back_ref, ref_alias or ref_name jbe/bsw@0: ) jbe/bsw@0: return selector jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_list_or_object = jbe@23: :load( jbe@23: ref_name, -- name of reference (e.g. "children") jbe@23: options, -- table options passed to the reference loader (e.g. { order = ... }) jbe@23: ref_alias, -- optional alias for the reference (e.g. "ordered_children") jbe@23: back_ref_alias -- back reference name (e.g. "parent") jbe@23: ) jbe@23: jbe@23: This method loads referenced objects and attaches them with the objects of the existent list. The "ref" and "back_ref" arguments passed to "add_reference" are used for the attachment, unless aliases are given with "ref_alias" and "back_ref_alias". If "options" are set, these options are passed to the reference loader. The default reference loader supports only one option named "order". If "order" is set to nil, the default order is used, if "order" is set to false, no ORDER BY statment is included in the selector, otherwise the given expression is used for ordering. jbe@23: jbe@23: This method is not only available for database result lists but also for database result objects. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype.list.load(...) jbe/bsw@0: return class_prototype.list.get_reference_selector(...):exec() jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_object = jbe@23: :get_reference_selector( jbe@23: ref_name, -- name of reference (e.g. "children") jbe@23: options, -- table options passed to the reference loader (e.g. { order = ... }) jbe@23: ref_alias, -- optional alias for the reference (e.g. "ordered_children") jbe@23: back_ref_alias -- back reference name (e.g. "parent") jbe@23: ) jbe@23: jbe@23: This method returns a special selector for selecting referenced objects. It is prepared in a way, that on execution of the selector, all returned objects are attached with the objects of the existent list. The "ref" and "back_ref" arguments passed to "add_reference" are used for the attachment, unless aliases are given with "ref_alias" and "back_ref_alias". If "options" are set, these options are passed to the reference loader. The default reference loader supports only one option named "order". If "order" is set to nil, the default order is used, if "order" is set to false, no ORDER BY statment is included in the selector, otherwise the given expression is used for ordering. jbe@23: jbe@23: This method is not only available for database result objects but also for database result lists. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype.object:get_reference_selector(...) jbe/bsw@0: local list = self._class:create_list() jbe/bsw@0: list[1] = self jbe/bsw@0: return list:get_reference_selector(...) jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_list_or_object = jbe@23: :load( jbe@23: ref_name, -- name of reference (e.g. "children") jbe@23: options, -- table options passed to the reference loader (e.g. { order = ... }) jbe@23: ref_alias, -- optional alias for the reference (e.g. "ordered_children") jbe@23: back_ref_alias -- back reference name (e.g. "parent") jbe@23: ) jbe@23: jbe@23: This method loads referenced objects and attaches them with the objects of the existent list. The "ref" and "back_ref" arguments passed to "add_reference" are used for the attachment, unless aliases are given with "ref_alias" and "back_ref_alias". If "options" are set, these options are passed to the reference loader. The default reference loader supports only one option named "order". If "order" is set to nil, the default order is used, if "order" is set to false, no ORDER BY statment is included in the selector, otherwise the given expression is used for ordering. jbe@23: jbe@23: This method is not only available for database result objects but also for database result lists. Calling this method for objects is unneccessary, unless additional options and/or an alias is used. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype.object.load(...) jbe/bsw@0: return class_prototype.object.get_reference_selector(...):exec() jbe/bsw@0: end jbe@23: --//-- jbe/bsw@0: jbe@23: --[[-- jbe@23: db_class = -- same class returned jbe@23: :add_reference{ jbe@23: mode = mode, -- "11", "1m", "m1", or "mm" (one/many to one/many) jbe@23: to = to, -- referenced class (model), optionally as string or function returning the value (avoids autoload) jbe@23: this_key = this_key, -- name of key in this class (model) jbe@23: that_key = that_key, -- name of key in the other class (model) ("to" argument) jbe@23: ref = ref, -- name of reference in this class, referring to the other class jbe@23: back_ref = back_ref, -- name of reference in other class, referring to this class jbe@23: default_order = default_order, -- expression as passed to "assemble_command" used for sorting jbe@23: selector_generator = selector_generator, -- alternative function used as selector generator (use only, when you know what you are doing) jbe@23: connected_by_table = connected_by_table, -- connecting table used for many to many relations jbe@23: connected_by_this_key = connected_by_this_key, -- key in connecting table referring to "this_key" of this class (model) jbe@23: connected_by_that_key = connected_by_that_key -- key in connecting table referring to "that_key" in other class (model) ("to" argument) jbe@23: } jbe/bsw@0: jbe@23: Denotes a reference from one database class to another database class (model to model relation). There are 4 possible types of references: one-to-one (mode = "11"), one-to-many (mode = "1m"), many-to-one ("m1"), and many-to-many ("mm"). References usually should be defined in both models, which are related to each other, with mirrored mode (i.e. "1m" in one model, and "m1" in the other). One-to-one and one-to-many references may have a "back_ref" setting, which causes that loaded objects of the referenced class, refer back to the originating object. One-to-many and many-to-many references may have a "default_order" setting, which selects the default order for selected objects. When adding a many-to-many reference, the argument "connected_by_table", "connected_by_this_key" and "connected_by_that_key" must be set additionally. jbe@23: jbe@23: --]]-- jbe/bsw@0: function class_prototype:add_reference(args) jbe/bsw@0: local selector_generator = args.selector_generator jbe/bsw@0: local mode = args.mode jbe/bsw@0: local to = args.to jbe/bsw@0: local this_key = args.this_key jbe/bsw@0: local that_key = args.that_key jbe/bsw@0: local connected_by_table = args.connected_by_table -- TODO: split to table and schema jbe/bsw@0: local connected_by_this_key = args.connected_by_this_key jbe/bsw@0: local connected_by_that_key = args.connected_by_that_key jbe/bsw@0: local ref = args.ref jbe/bsw@0: local back_ref = args.back_ref jbe/bsw@0: local default_order = args.default_order jbe/bsw@0: local model jbe/bsw@0: local function get_model() jbe/bsw@0: if not model then jbe/bsw@0: if type(to) == "string" then jbe/bsw@0: model = _G jbe/bsw@0: for path_element in string.gmatch(to, "[^.]+") do jbe/bsw@0: model = model[path_element] jbe/bsw@0: end jbe/bsw@0: elseif type(to) == "function" then jbe/bsw@0: model = to() jbe/bsw@0: else jbe/bsw@0: model = to jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: if not model or model == _G then jbe/bsw@0: error("Could not get model for reference.") jbe/bsw@0: end jbe/bsw@0: return model jbe/bsw@0: end jbe/bsw@0: self.references[ref] = { jbe/bsw@0: mode = mode, jbe/bsw@0: this_key = this_key, jbe/bsw@0: that_key = connected_by_table and "mm_ref_" or that_key, jbe/bsw@0: ref = ref, jbe/bsw@0: back_ref = back_ref, jbe/bsw@0: selector_generator = selector_generator or function(list, options) jbe/bsw@0: -- TODO: support tuple keys jbe/bsw@0: local options = options or {} jbe/bsw@0: local model = get_model() jbe/bsw@0: -- TODO: too many records cause PostgreSQL command stack overflow jbe/bsw@0: local ids = { sep = ", " } jbe/bsw@0: for i, object in ipairs(list) do jbe/bsw@0: local id = object[this_key] jbe/bsw@0: if id ~= nil then jbe/bsw@0: ids[#ids+1] = {"?", id} jbe/bsw@0: end jbe/bsw@0: end jbe/bsw@0: if #ids == 0 then jbe/bsw@0: return model:new_selector():empty_list_mode() jbe/bsw@0: end jbe/bsw@0: local selector = model:new_selector() jbe/bsw@0: if connected_by_table then jbe/bsw@0: selector:join( jbe/bsw@0: connected_by_table, jbe/bsw@0: nil, jbe/bsw@0: { jbe/bsw@0: '$."$" = $."$"', jbe/bsw@0: {connected_by_table}, jbe/bsw@0: {connected_by_that_key}, jbe/bsw@0: {model:get_qualified_table()}, jbe/bsw@0: {that_key} jbe/bsw@0: } jbe/bsw@0: ) jbe/bsw@0: selector:add_field( jbe/bsw@0: { jbe/bsw@0: '$."$"', jbe/bsw@0: {connected_by_table}, jbe/bsw@0: {connected_by_this_key} jbe/bsw@0: }, jbe/bsw@0: 'mm_ref_' jbe/bsw@0: ) jbe/bsw@0: selector:add_where{ jbe/bsw@0: '$."$" IN ($)', jbe/bsw@0: {connected_by_table}, jbe/bsw@0: {connected_by_this_key}, jbe/bsw@0: ids jbe/bsw@0: } jbe/bsw@0: else jbe/bsw@6: selector:add_where{'$."$" IN ($)', {model:get_qualified_table()}, {that_key}, ids} jbe/bsw@0: end jbe/bsw@0: if options.order == nil and default_order then jbe/bsw@0: selector:add_order_by(default_order) jbe/bsw@0: elseif options.order then jbe/bsw@0: selector:add_order_by(options.order) jbe/bsw@0: end jbe/bsw@0: return selector jbe/bsw@0: end jbe/bsw@0: } jbe/bsw@0: if mode == "m1" or mode == "11" then jbe/bsw@0: self.foreign_keys[this_key] = ref jbe/bsw@0: end jbe/bsw@0: return self jbe/bsw@0: end jbe@23: --//-- jbe@23: jbe@67: return _M jbe@67: