webmcp

annotate libraries/mondelefant/mondelefant.lua @ 553:a0c49529ab8b

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

Impressum / About Us