webmcp: 5b19007574de libraries/mondelefant/mondelefant.lua

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.

147

148 --]]--

149 function selector_prototype:empty_list_mode()

150 self._mode = "empty_list"

151 return self

152 end

153 --//--

154

155 --[[--

156 db_selector =

157 <db_selector>:add_with(

158 expression = expression,

159 selector = selector

160 )

161

162 Adds an WITH RECURSIVE expression to the selector. The selector is modified and returned.

163 --]]--

164 function selector_prototype:add_with(expression, selector)

165 add(self._with, {"$ AS ($)", {expression}, {selector}})

166 return self

167 end

168 --//--

169

170 --[[--

171 db_selector = -- same selector returned

172 <db_selector>:add_distinct_on(

173 expression -- expression as passed to "assemble_command"

174 )

175

176 Adds an DISTINCT ON expression to the selector. The selector is modified and returned.

177

178 --]]--

179 function selector_prototype:add_distinct_on(expression)

180 if self._distinct then

181 error("Can not combine DISTINCT with DISTINCT ON.")

182 end

183 add(self._distinct_on, expression)

184 return self

185 end

186 --//--

187

188 --[[--

189 db_selector = -- same selector returned

190 <db_selector>:set_distinct()

191

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.

193

194 --]]--

195 function selector_prototype:set_distinct()

196 if #self._distinct_on > 0 then

197 error("Can not combine DISTINCT with DISTINCT ON.")

198 end

199 self._distinct = true

200 return self

201 end

202 --//--

203

204 --[[--

205 db_selector = -- same selector returned

206 <db_selector>:add_from(

207 expression, -- expression as passed to "assemble_command"

208 alias, -- optional alias expression as passed to "assemble_command"

209 condition -- optional condition expression as passed to "assemble_command"

210 )

211

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.

213

214 This method is identical to "join".

215

216 --]]--

217 function selector_prototype:add_from(expression, alias, condition)

218 local first = (#self._from == 0)

219 if not first then

220 if condition then

221 add(self._from, "INNER JOIN")

222 else

223 add(self._from, "CROSS JOIN")

224 end

225 end

226 if getmetatable(expression) == selector_metatable then

227 if alias then

228 add(self._from, {'($) AS "$"', {expression}, {alias}})

229 else

230 add(self._from, {'($) AS "subquery"', {expression}})

231 end

232 else

233 if alias then

234 add(self._from, {'$ AS "$"', {expression}, {alias}})

235 else

236 add(self._from, expression)

237 end

238 end

239 if condition then

240 if first then

241 self:add_where(condition)

242 else

243 add(self._from, "ON")

244 add(self._from, condition)

245 end

246 end

247 return self

248 end

249 --//--

250

251 --[[--

252 db_selector = -- same selector returned

253 <db_selector>:add_where(

254 expression -- expression as passed to "assemble_command"

255 )

256

257 Adds expressions for WHERE clause to the selector. The selector is modified and returned. Multiple calls cause expressions to be AND-combined.

258

259 --]]--

260 function selector_prototype:add_where(expression)

261 add(self._where, expression)

262 return self

263 end

264 --//--

265

266 --[[--

267 db_selector = -- same selector returned

268 <db_selector>:add_group_by(

269 expression -- expression as passed to "assemble_command"

270 )

271

272 Adds expressions for GROUP BY clause to the selector. The selector is modified and returned.

273

274 --]]--

275 function selector_prototype:add_group_by(expression)

276 add(self._group_by, expression)

277 return self

278 end

279 --//--

280

281 --[[--

282 db_selector = -- same selector returned

283 <db_selector>:add_having(

284 expression -- expression as passed to "assemble_command"

285 )

286

287 Adds expressions for HAVING clause to the selector. The selector is modified and returned. Multiple calls cause expressions to be AND-combined.

288

289 --]]--

290 function selector_prototype:add_having(expression)

291 add(self._having, expression)

292 return self

293 end

294 --//--

295

296 --[[--

297 db_selector = -- same selector returned

298 <db_selector>:add_combine(

299 expression -- expression as passed to "assemble_command"

300 )

301

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.

303

304 --]]--

305 function selector_prototype:add_combine(expression)

306 add(self._combine, expression)

307 return self

308 end

309 --//--

310

311 --[[--

312 db_selector = -- same selector returned

313 <db_selector>:add_order_by(

314 expression -- expression as passed to "assemble_command"

315 )

316

317 Adds expressions for ORDER BY clause to the selector. The selector is modified and returned.

318

319 --]]--

320 function selector_prototype:add_order_by(expression)

321 add(self._order_by, expression)

322 return self

323 end

324 --//--

325

326 --[[--

327 db_selector = -- same selector returned

328 <db_selector>:limit(

329 count -- integer used as LIMIT

330 )

331

332 Limits the number of rows to a given number, by using LIMIT. The selector is modified and returned.

333

334 --]]--

335 function selector_prototype:limit(count)

336 if type(count) ~= "number" or count % 1 ~= 0 then

337 error("LIMIT must be an integer.")

338 end

339 self._limit = count

340 return self

341 end

342 --//--

343

344 --[[--

345 db_selector = -- same selector returned

346 <db_selector>:offset(

347 count -- integer used as OFFSET

348 )

349

350 Skips a given number of rows, by using OFFSET. The selector is modified and returned.

351

352 --]]--

353 function selector_prototype:offset(count)

354 if type(count) ~= "number" or count % 1 ~= 0 then

355 error("OFFSET must be an integer.")

356 end

357 self._offset = count

358 return self

359 end

360 --//--

361

362 --[[--

363 db_selector = -- same selector returned

364 <db_selector>:for_share()

365

366 Adds FOR SHARE to the statement, to share-lock all rows read. The selector is modified and returned.

367

368 --]]--

369 function selector_prototype:for_share()

370 self._read_lock.all = true

371 return self

372 end

373 --//--

374

375 --[[--

376 db_selector = -- same selector returned

377 <db_selector>:for_share_of(

378 expression -- expression as passed to "assemble_command"

379 )

380

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.

382

383 --]]--

384 function selector_prototype:for_share_of(expression)

385 add(self._read_lock, expression)

386 return self

387 end

388 --//--

389

390 --[[--

391 db_selector = -- same selector returned

392 <db_selector>:for_update()

393

394 Adds FOR UPDATE to the statement, to exclusivly lock all rows read. The selector is modified and returned.

395

396 --]]--

397 function selector_prototype:for_update()

398 self._write_lock.all = true

399 return self

400 end

401 --//--

402

403 --[[--

404 db_selector = -- same selector returned

405 <db_selector>:for_update_of(

406 expression -- expression as passed to "assemble_command"

407 )

408

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.

410

411 --]]--

412 function selector_prototype:for_update_of(expression)

413 add(self._write_lock, expression)

414 return self

415 end

416 --//--

417

418 --[[--

419 db_selector = -- same selector returned

420 <db_selector>:reset_fields()

421

422 This method removes all fields added by method "add_field". The selector is modified and returned.

423

424 --]]--

425 function selector_prototype:reset_fields()

426 for idx in ipairs(self._fields) do

427 self._fields[idx] = nil

428 end

429 return self

430 end

431 --//--

432

433 --[[--

434 db_selector = -- same selector returned

435 <db_selector>:add_field(

436 expression, -- expression as passed to "assemble_command"

437 alias, -- optional alias expression as passed to "assemble_command"

438 option_list -- optional list of options (may contain strings "distinct" or "grouped")

439 )

440

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.

442

443 --]]--

444 function selector_prototype:add_field(expression, alias, options)

445 if alias then

446 add(self._fields, {'$ AS "$"', {expression}, {alias}})

447 else

448 add(self._fields, expression)

449 end

450 if options then

451 for i, option in ipairs(options) do

452 if option == "distinct" then

453 if alias then

454 self:add_distinct_on('"' .. alias .. '"')

455 else

456 self:add_distinct_on(expression)

457 end

458 elseif option == "grouped" then

459 if alias then

460 self:add_group_by('"' .. alias .. '"')

461 else

462 self:add_group_by(expression)

463 end

464 else

465 error("Unknown option '" .. option .. "' to add_field method.")

466 end

467 end

468 end

469 return self

470 end

471 --//--

472

473 --[[--

474 db_selector = -- same selector returned

475 <db_selector>:join(

476 expression, -- expression as passed to "assemble_command"

477 alias, -- optional alias expression as passed to "assemble_command"

478 condition -- optional condition expression as passed to "assemble_command"

479 )

480

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.

482

483 This method is identical to "add_from".

484

485 --]]--

486 function selector_prototype:join(...) -- NOTE: alias for add_from

487 return self:add_from(...)

488 end

489 --//--

490

491 --[[--

492 db_selector = -- same selector returned

493 <db_selector>:from(

494 expression, -- expression as passed to "assemble_command"

495 alias, -- optional alias expression as passed to "assemble_command"

496 condition -- optional condition expression as passed to "assemble_command"

497 )

498

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.

500

501 This method is identical to "add_from" or "join", except that an error is thrown, if there is already any FROM expression existent.

502

503 --]]--

504 function selector_prototype:from(expression, alias, condition)

505 if #self._from > 0 then

506 error("From-clause already existing (hint: try join).")

507 end

508 return self:join(expression, alias, condition)

509 end

510 --//--

511

512 --[[--

513 db_selector = -- same selector returned

514 <db_selector>:left_join(

515 expression, -- expression as passed to "assemble_command"

516 alias, -- optional alias expression as passed to "assemble_command"

517 condition -- optional condition expression as passed to "assemble_command"

518 )

519

520 Adds expressions for FROM clause to the selector using a LEFT OUTER JOIN. The selector is modified and returned.

521

522 --]]--

523 function selector_prototype:left_join(expression, alias, condition)

524 local first = (#self._from == 0)

525 if not first then

526 add(self._from, "LEFT OUTER JOIN")

527 end

528 if alias then

529 add(self._from, {'$ AS "$"', {expression}, {alias}})

530 else

531 add(self._from, expression)

532 end

533 if condition then

534 if first then

535 self:add_where(condition)

536 else

537 add(self._from, "ON")

538 add(self._from, condition)

539 end

540 end

541 return self

542 end

543 --//--

544

545 --[[--

546 db_selector = -- same selector returned

547 <db_selector>:union(

548 expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE

549 )

550

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.

552

553 --]]--

554 function selector_prototype:union(expression)

555 self:add_combine{"UNION $", {expression}}

556 return self

557 end

558 --//--

559

560 --[[--

561 db_selector = -- same selector returned

562 <db_selector>:union_all(

563 expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE

564 )

565

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.

567

568 --]]--

569 function selector_prototype:union_all(expression)

570 self:add_combine{"UNION ALL $", {expression}}

571 return self

572 end

573 --//--

574

575 --[[--

576 db_selector = -- same selector returned

577 <db_selector>:intersect(

578 expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE

579 )

580

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.

582

583 --]]--

584 function selector_prototype:intersect(expression)

585 self:add_combine{"INTERSECT $", {expression}}

586 return self

587 end

588 --//--

589

590 --[[--

591 db_selector = -- same selector returned

592 <db_selector>:intersect_all(

593 expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE

594 )

595

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.

597

598 --]]--

599 function selector_prototype:intersect_all(expression)

600 self:add_combine{"INTERSECT ALL $", {expression}}

601 return self

602 end

603 --//--

604

605 --[[--

606 db_selector = -- same selector returned

607 <db_selector>:except(

608 expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE

609 )

610

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.

612

613 --]]--

614 function selector_prototype:except(expression)

615 self:add_combine{"EXCEPT $", {expression}}

616 return self

617 end

618 --//--

619

620 --[[--

621 db_selector = -- same selector returned

622 <db_selector>:except_all(

623 expression -- expression or selector without ORDER BY, LIMIT, FOR UPDATE or FOR SHARE

624 )

625

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.

627

628 --]]--

629 function selector_prototype:except_all(expression)

630 self:add_combine{"EXCEPT ALL $", {expression}}

631 return self

632 end

633 --//--

634

635 --[[--

636 db_selector = -- same selector returned

637 <db_selector>:set_class(

638 class -- database class (model)

639 )

640

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.

642

643 --]]--

644 function selector_prototype:set_class(class)

645 self._class = class

646 return self

647 end

648 --//--

649

650 --[[--

651 db_selector = -- same selector returned

652 <db_selector>:attach(

653 mode, -- attachment type: "11" one to one, "1m" one to many, "m1" many to one

654 data2, -- other database result list or object, the results of this selector shall be attached with

655 field1, -- field name(s) in result list or object of this selector used for attaching

656 field2, -- field name(s) in "data2" used for attaching

657 ref1, -- name of reference field in the results of this selector after attaching

658 ref2 -- name of reference field in "data2" after attaching

659 )

660

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.

662

663 --]]--

664 function selector_prototype:attach(mode, data2, field1, field2, ref1, ref2)

665 self._attach = {

666 mode = mode,

667 data2 = data2,

668 field1 = field1,

669 field2 = field2,

670 ref1 = ref1,

671 ref2 = ref2

672 }

673 return self

674 end

675 --//--

676

677 function selector_metatable:__tostring()

678 local parts = {sep = " "}

679 if #self._with > 0 then

680 add(parts, {"WITH RECURSIVE $", self._with})

681 end

682 add(parts, "SELECT")

683 if self._distinct then

684 add(parts, "DISTINCT")

685 elseif #self._distinct_on > 0 then

686 add(parts, {"DISTINCT ON ($)", self._distinct_on})

687 end

688 add(parts, {"$", self._fields})

689 if #self._from > 0 then

690 add(parts, {"FROM $", self._from})

691 end

692 if #self._mode == "empty_list" then

693 add(parts, "WHERE FALSE")

694 elseif #self._where > 0 then

695 add(parts, {"WHERE ($)", self._where})

696 end

697 if #self._group_by > 0 then

698 add(parts, {"GROUP BY $", self._group_by})

699 end

700 if #self._having > 0 then

701 add(parts, {"HAVING ($)", self._having})

702 end

703 for i, v in ipairs(self._combine) do

704 add(parts, v)

705 end

706 if #self._order_by > 0 then

707 add(parts, {"ORDER BY $", self._order_by})

708 end

709 if self._mode == "empty_list" then

710 add(parts, "LIMIT 0")

711 elseif self._mode ~= "list" then

712 add(parts, "LIMIT 1")

713 elseif self._limit then

714 add(parts, "LIMIT " .. self._limit)

715 end

716 if self._offset then

717 add(parts, "OFFSET " .. self._offset)

718 end

719 if self._write_lock.all then

720 add(parts, "FOR UPDATE")

721 else

722 if self._read_lock.all then

723 add(parts, "FOR SHARE")

724 elseif #self._read_lock > 0 then

725 add(parts, {"FOR SHARE OF $", self._read_lock})

726 end

727 if #self._write_lock > 0 then

728 add(parts, {"FOR UPDATE OF $", self._write_lock})

729 end

730 end

731 return self._db_conn:assemble_command{"$", parts}

732 end

733

734 --[[--

735 db_error, -- database error object, or nil in case of success

736 result = -- database result list or object

737 <db_selector>:try_exec()

738

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.

740

741 --]]--

742 function selector_prototype:try_exec()

743 if self._mode == "empty_list" then

744 if self._class then

745 return nil, self._class:create_list()

746 else

747 return nil, self._db_conn:create_list()

748 end

749 end

750 local db_error, db_result = self._db_conn:try_query(self, self._mode)

751 if db_error then

752 return db_error

753 elseif db_result then

754 if self._class then set_class(db_result, self._class) end

755 if self._attach then

756 attach(

757 self._attach.mode,

758 db_result,

759 self._attach.data2,

760 self._attach.field1,

761 self._attach.field2,

762 self._attach.ref1,

763 self._attach.ref2

764 )

765 end

766 return nil, db_result

767 else

768 return nil

769 end

770 end

771 --//--

772

773 --[[--

774 result = -- database result list or object

775 <db_selector>:exec()

776

777 This method executes the selector on its database. The result list or object is returned on success, otherwise an error is thrown.

778

779 --]]--

780 function selector_prototype:exec()

781 local db_error, result = self:try_exec()

782 if db_error then

783 db_error:escalate()

784 else

785 return result

786 end

787 end

788 --//--

789

790 --[[--

791 count = -- number of rows returned

792 <db_selector>:count()

793

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.

795

796 --]]--

797 function selector_prototype:count()

798 if not self._count then

799 local count_selector = self:get_db_conn():new_selector()

800 count_selector:add_field('count(1)')

801 count_selector:add_from(self)

802 count_selector:single_object_mode()

803 self._count = count_selector:exec().count

804 end

805 return self._count

806 end

807 --//--

808

809

810

811 -----------------

812 -- attachments --

813 -----------------

814

815 local function attach_key(row, fields)

816 local t = type(fields)

817 if t == "string" then

818 return tostring(row[fields])

819 elseif t == "table" then

820 local r = {}

821 for idx, field in ipairs(fields) do

822 r[idx] = string.format("%q", row[field])

823 end

824 return table.concat(r)

825 else

826 error("Field information for 'mondelefant.attach' is neither a string nor a table.")

827 end

828 end

829

830 --[[--

831 mondelefant.attach(

832 mode, -- attachment type: "11" one to one, "1m" one to many, "m1" many to one

833 data1, -- first database result list or object

834 data2, -- second database result list or object

835 key1, -- field name(s) in first result list or object used for attaching

836 key2, -- field name(s) in second result list or object used for attaching

837 ref1, -- name of reference field to be set in first database result list or object

838 ref2 -- name of reference field to be set in second database result list or object

839 )

840

841 This function attaches database result lists/objects with each other. It does not need to be called directly.

842

843 --]]--

844 function attach(mode, data1, data2, key1, key2, ref1, ref2)

845 local many1, many2

846 if mode == "11" then

847 many1 = false

848 many2 = false

849 elseif mode == "1m" then

850 many1 = false

851 many2 = true

852 elseif mode == "m1" then

853 many1 = true

854 many2 = false

855 elseif mode == "mm" then

856 many1 = true

857 many2 = true

858 else

859 error("Unknown mode specified for 'mondelefant.attach'.")

860 end

861 local list1, list2

862 if data1._type == "object" then

863 list1 = { data1 }

864 elseif data1._type == "list" then

865 list1 = data1

866 else

867 error("First result data given to 'mondelefant.attach' is invalid.")

868 end

869 if data2._type == "object" then

870 list2 = { data2 }

871 elseif data2._type == "list" then

872 list2 = data2

873 else

874 error("Second result data given to 'mondelefant.attach' is invalid.")

875 end

876 local hash1 = {}

877 local hash2 = {}

878 if ref2 then

879 for i, row in ipairs(list1) do

880 local key = attach_key(row, key1)

881 local list = hash1[key]

882 if not list then list = {}; hash1[key] = list end

883 list[#list + 1] = row

884 end

885 end

886 if ref1 then

887 for i, row in ipairs(list2) do

888 local key = attach_key(row, key2)

889 local list = hash2[key]

890 if not list then list = {}; hash2[key] = list end

891 list[#list + 1] = row

892 end

893 for i, row in ipairs(list1) do

894 local key = attach_key(row, key1)

895 local matching_rows = hash2[key]

896 if many2 then

897 local list = data2._connection:create_list(matching_rows)

898 list._class = data2._class

899 row._ref[ref1] = list

900 elseif matching_rows and #matching_rows == 1 then

901 row._ref[ref1] = matching_rows[1]

902 else

903 row._ref[ref1] = false

904 end

905 end

906 end

907 if ref2 then

908 for i, row in ipairs(list2) do

909 local key = attach_key(row, key2)

910 local matching_rows = hash1[key]

911 if many1 then

912 local list = data1._connection:create_list(matching_rows)

913 list._class = data1._class

914 row._ref[ref2] = list

915 elseif matching_rows and #matching_rows == 1 then

916 row._ref[ref2] = matching_rows[1]

917 else

918 row._ref[ref2] = false

919 end

920 end

921 end

922 end

923 --//--

924

925

926

927 ------------------

928 -- model system --

929 ------------------

930

931 --[[--

932 <db_class>.schema

933

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.

935

936 --]]--

937 class_prototype.schema = nil

938 --//--

939

940 --[[--

941 <db_class>.table

942

943 Name of the table in the PostgreSQL database. Should be set in the model file of each model.

944

945 --]]--

946 class_prototype.table = nil

947 --//--

948

949 --[[--

950 <db_class>.primary_key

951

952 Primary key of a database class (model). Defaults to "id".

953

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"}.

955

956 --]]--

957 class_prototype.primary_key = "id"

958 --//--

959

960 --[[--

961 <db_class>.document_column

962

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.

964

965 --]]--

966 class_prototype.document_column = nil

967 --//--

968

969 --[[--

970 <db_class>.binary_columns

971

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 }.

973

974 --]]--

975 class_prototype.binary_columns = nil

976 --//

977

978 --[[--

979 db_handle = -- database connection handle used by this class

980 <db_class>:get_db_conn()

981

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.

983

984 --]]--

985 function class_prototype:get_db_conn()

986 error(

987 "Method mondelefant class(_prototype):get_db_conn() " ..

988 "has to be implemented."

989 )

990 end

991 --//--

992

993 --[[--

994 string = -- string of form '"schemaname"."tablename"' or '"tablename"'

995 <db_class>:get_qualified_table()

996

997 This method returns a string with the (double quoted) qualified table name used to store objects of this class.

998

999 --]]--

1000 function class_prototype:get_qualified_table()

1001 if not self.table then error "Table unknown." end

1002 if self.schema then

1003 return '"' .. self.schema .. '"."' .. self.table .. '"'

1004 else

1005 return '"' .. self.table .. '"'

1006 end

1007 end

1008 --]]--

1009

1010 --[[--

1011 string = -- single quoted string of form "'schemaname.tablename'" or "'tablename'"

1012 <db_class>:get_qualified_table_literal()

1013

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.

1015

1016 --]]--

1017 function class_prototype:get_qualified_table_literal()

1018 if not self.table then error "Table unknown." end

1019 if self.schema then

1020 return self.schema .. '.' .. self.table

1021 else

1022 return self.table

1023 end

1024 end

1025 --//--

1026

1027 --[[--

1028 list = -- list of column names of primary key

1029 <db_class>:get_primary_key_list()

1030

1031 This method returns a list of column names of the primary key.

1032

1033 --]]--

1034 function class_prototype:get_primary_key_list()

1035 local primary_key = self.primary_key

1036 if type(primary_key) == "string" then

1037 return {primary_key}

1038 else

1039 return primary_key

1040 end

1041 end

1042 --//--

1043

1044 --[[--

1045 columns = -- list of columns

1046 <db_class>:get_columns()

1047

1048 This method returns a list of column names of the table used for the class.

1049

1050 --]]--

1051 function class_prototype:get_columns()

1052 if self._columns then

1053 return self._columns

1054 end

1055 local selector = self:get_db_conn():new_selector()

1056 selector:set_class(self)

1057 selector:from(self:get_qualified_table())

1058 selector:add_field("*")

1059 selector:add_where("FALSE")

1060 local db_result = selector:exec()

1061 local connection = db_result._connection

1062 local columns = {}

1063 for idx, info in ipairs(db_result._column_info) do

1064 local key = info.field_name

1065 local value = {

1066 name = key,

1067 type = connection.type_mappings[info.type]

1068 }

1069 columns[key] = value

1070 table.insert(columns, value)

1071 end

1072 self._columns = columns

1073 return columns

1074 end

1075 --//--

1076

1077 --[[--

1078 selector = -- new selector for selecting objects of this class

1079 <db_class>:new_selector(

1080 db_conn -- optional(!) database connection handle, defaults to result of :get_db_conn()

1081 )

1082

1083 This method creates a new selector for selecting objects of the class.

1084

1085 --]]--

1086 function class_prototype:new_selector(db_conn)

1087 local selector = (db_conn or self:get_db_conn()):new_selector()

1088 selector:set_class(self)

1089 selector:from(self:get_qualified_table())

1090 selector:add_field(self:get_qualified_table() .. ".*")

1091 return selector

1092 end

1093 --//--

1094

1095 --[[--

1096 db_list = -- database result being an empty list

1097 <db_class>:create_list()

1098

1099 Creates an empty database result representing a list of objects of the given class.

1100

1101 --]]--

1102 function class_prototype:create_list()

1103 local list = self:get_db_conn():create_list()

1104 list._class = self

1105 return list

1106 end

1107 --//--

1108

1109 --[[--

1110 db_object = -- database object (instance of model)

1111 <db_class>:new()

1112

1113 Creates a new object of the given class.

1114

1115 --]]--

1116 function class_prototype:new()

1117 local object = self:get_db_conn():create_object()

1118 object._class = self

1119 object._new = true

1120 return object

1121 end

1122 --//--

1123

1124 --[[--

1125 <db_object>:upsert_mode()

1126

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.

1128

1129 --]]--

1130 function class_prototype.object:upsert_mode()

1131 if not self._new then

1132 error("Upsert mode requires a new object and cannot be used on objects returned from a database query.")

1133 end

1134 self._upsert = true

1135 return self

1136 end

1137 --//--

1138

1139 --[[--

1140 db_error = -- database error object, or nil in case of success

1141 <db_object>:try_save()

1142

1143 This method saves changes to an object in the database. Returns nil on success, otherwise an error object is returned.

1144

1145 --]]--

1146 function class_prototype.object:try_save()

1147 if not self._class then

1148 error("Cannot save object: No class information available.")

1149 end

1150 local primary_key = self._class:get_primary_key_list()

1151 if self._new then

1152 local fields = {sep = ", "}

1153 local values = {sep = ", "}

1154 for key in pairs(self._dirty or {}) do

1155 add(fields, '"' .. key .. '"')

1156 if self._class.binary_columns and self._class.binary_columns[key] then

1157 add(values, {'$', {self._connection:quote_binary(self._col[key])}})

1158 else

1159 add(values, {'?', self._col[key]})

1160 end

1161 end

1162 local returning = { sep = ", " }

1163 if primary_key.json_doc then

1164 returning[1] = {

1165 '("$"->>?)::$ AS "json_key"',

1166 {primary_key.json_doc}, primary_key.key, {primary_key.type}

1167 }

1168 else

1169 for idx, value in ipairs(primary_key) do

1170 returning[idx] = '"' .. value .. '"'

1171 end

1172 end

1173 local db_error, db_result

1174 if self._upsert then

1175 local upsert_keys = {sep = ", "}

1176 if primary_key.json_doc then

1177 upsert_keys[1] = {

1178 '("$"->>?)::$',

1179 {primary_key.json_doc}, primary_key.key, {primary_key.type}

1180 }

1181 else

1182 for idx, value in ipairs(primary_key) do

1183 upsert_keys[idx] = '"' .. value .. '"'

1184 end

1185 end

1186 if #fields == 0 then

1187 db_error, db_result = self._connection:try_query(

1188 {

1189 'INSERT INTO $ DEFAULT VALUES ON CONFLICT ($) DO NOTHING $',

1190 {self._class:get_qualified_table()},

1191 upsert_keys,

1192 returning

1193 },

1194 "object"

1195 )

1196 else

1197 local upsert_sets = {sep = ", "}

1198 for key in pairs(self._dirty) do

1199 add(upsert_sets, {'"$" = ?', {key}, self._col[key]})

1200 end

1201 db_error, db_result = self._connection:try_query(

1202 {

1203 'INSERT INTO $ ($) VALUES ($) ON CONFLICT ($) DO UPDATE SET $ RETURNING $',

1204 {self._class:get_qualified_table()},

1205 fields,

1206 values,

1207 upsert_keys,

1208 upsert_sets,

1209 returning

1210 },

1211 "object"

1212 )

1213 end

1214 else

1215 if #fields == 0 then

1216 db_error, db_result = self._connection:try_query(

1217 {

1218 'INSERT INTO $ DEFAULT VALUES RETURNING $',

1219 {self._class:get_qualified_table()},

1220 returning

1221 },

1222 "object"

1223 )

1224 else

1225 db_error, db_result = self._connection:try_query(

1226 {

1227 'INSERT INTO $ ($) VALUES ($) RETURNING $',

1228 {self._class:get_qualified_table()},

1229 fields,

1230 values,

1231 returning

1232 },

1233 "object"

1234 )

1235 end

1236 end

1237 if db_error then

1238 return db_error

1239 end

1240 if primary_key.json_doc then

1241 self._col[primary_key.json_doc][primary_key.key] = db_result.json_key

1242 else

1243 for idx, value in ipairs(primary_key) do

1244 self[value] = db_result[value]

1245 end

1246 end

1247 if not self._upsert then

1248 self._new = false

1249 end

1250 else

1251 local update_sets = {sep = ", "}

1252 for key, mutability_state in pairs(self._dirty or {}) do

1253 if

1254 mutability_state == true or (

1255 verify_mutability_state and

1256 verify_mutability_state(self._col[key], mutability_state)

1257 )

1258 then

1259 if self._class.binary_columns and self._class.binary_columns[key] then

1260 add(update_sets, {'"$" = $', {key}, {self._connection:quote_binary(self._col[key])}})

1261 else

1262 add(update_sets, {'"$" = ?', {key}, self._col[key]})

1263 end

1264 self._dirty[key] = true -- always dirty in case of later error

1265 end

1266 end

1267 if #update_sets >= 1 then

1268 local primary_key_compare = {sep = " AND "}

1269 if primary_key.json_doc then

1270 primary_key_compare[1] = {

1271 '("$"->>?)::$ = ?',

1272 {primary_key.json_doc}, primary_key.key, {primary_key.type},

1273 self._col[primary_key.json_doc][primary_key.key]

1274 }

1275 else

1276 for idx, value in ipairs(primary_key) do

1277 primary_key_compare[idx] = {

1278 "$ = ?",

1279 {'"' .. value .. '"'},

1280 self[value]

1281 }

1282 end

1283 end

1284 local db_error = self._connection:try_query{

1285 'UPDATE $ SET $ WHERE $',

1286 {self._class:get_qualified_table()},

1287 update_sets,

1288 primary_key_compare

1289 }

1290 if db_error then

1291 return db_error

1292 end

1293 end

1294 end

1295 for key in pairs(self._dirty or {}) do

1296 if save_mutability_state then

1297 self._dirty[key] =

1298 save_mutability_state and save_mutability_state(self._col[key]) or nil

1299 end

1300 end

1301 return nil

1302 end

1303 --//--

1304

1305 --[[--

1306 <db_object>:save()

1307

1308 This method saves changes to an object in the database. Throws error, unless successful.

1309

1310 --]]--

1311 function class_prototype.object:save()

1312 local db_error = self:try_save()

1313 if db_error then

1314 db_error:escalate()

1315 end

1316 return self

1317 end

1318 --//--

1319

1320 --[[--

1321 db_error = -- database error object, or nil in case of success

1322 <db_object>:try_destroy()

1323

1324 This method deletes an object in the database. Returns nil on success, otherwise an error object is returned.

1325

1326 --]]--

1327 function class_prototype.object:try_destroy()

1328 if not self._class then

1329 error("Cannot destroy object: No class information available.")

1330 end

1331 local primary_key = self._class:get_primary_key_list()

1332 local primary_key_compare = {sep = " AND "}

1333 if primary_key.json_doc then

1334 primary_key_compare[1] = {

1335 '("$"->>?)::$ = ?',

1336 {primary_key.json_doc}, primary_key.key, {primary_key.type},

1337 self._col[primary_key.json_doc][primary_key.key]

1338 }

1339 else

1340 for idx, value in ipairs(primary_key) do

1341 primary_key_compare[idx] = {

1342 "$ = ?",

1343 {'"' .. value .. '"'},

1344 self[value]

1345 }

1346 end

1347 end

1348 return self._connection:try_query{

1349 'DELETE FROM $ WHERE $',

1350 {self._class:get_qualified_table()},

1351 primary_key_compare

1352 }

1353 end

1354 --//--

1355

1356 --[[--

1357 <db_object>:destroy()

1358

1359 This method deletes an object in the database. Throws error, unless successful.

1360

1361 --]]--

1362 function class_prototype.object:destroy()

1363 local db_error = self:try_destroy()

1364 if db_error then

1365 db_error:escalate()

1366 end

1367 return self

1368 end

1369 --//--

1370

1371 --[[--

1372 db_selector =

1373 <db_list>:get_reference_selector(

1374 ref_name, -- name of reference (e.g. "children")

1375 options, -- table options passed to the reference loader (e.g. { order = ... })

1376 ref_alias, -- optional alias for the reference (e.g. "ordered_children")

1377 back_ref_alias -- back reference name (e.g. "parent")

1378 )

1379

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.

1381

1382 This method is not only available for database result lists but also for database result objects.

1383

1384 --]]--

1385 function class_prototype.list:get_reference_selector(

1386 ref_name, options, ref_alias, back_ref_alias

1387 )

1388 local ref_info = self._class:get_reference(ref_name)

1389 if not ref_info then

1390 error('Reference with name "' .. ref_name .. '" not found.')

1391 end

1392 local selector = ref_info.selector_generator(self, options or {})

1393 local mode = ref_info.mode

1394 if mode == "mm" or mode == "1m" then

1395 mode = "m1"

1396 elseif mode == "m1" then

1397 mode = "1m"

1398 end

1399 local ref_alias = ref_alias

1400 if ref_alias == false then

1401 ref_alias = nil

1402 elseif ref_alias == nil then

1403 ref_alias = ref_name

1404 end

1405 local back_ref_alias

1406 if back_ref_alias == false then

1407 back_ref_alias = nil

1408 elseif back_ref_alias == nil then

1409 back_ref_alias = ref_info.back_ref

1410 end

1411 selector:attach(

1412 mode,

1413 self,

1414 ref_info.that_key, ref_info.this_key,

1415 back_ref_alias or ref_info.back_ref, ref_alias or ref_name

1416 )

1417 return selector

1418 end

1419 --//--

1420

1421 --[[--

1422 db_list_or_object =

1423 <db_list>:load(

1424 ref_name, -- name of reference (e.g. "children")

1425 options, -- table options passed to the reference loader (e.g. { order = ... })

1426 ref_alias, -- optional alias for the reference (e.g. "ordered_children")

1427 back_ref_alias -- back reference name (e.g. "parent")

1428 )

1429

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.

1431

1432 This method is not only available for database result lists but also for database result objects.

1433

1434 --]]--

1435 function class_prototype.list.load(...)

1436 return class_prototype.list.get_reference_selector(...):exec()

1437 end

1438 --//--

1439

1440 --[[--

1441 db_object =

1442 <db_object>:get_reference_selector(

1443 ref_name, -- name of reference (e.g. "children")

1444 options, -- table options passed to the reference loader (e.g. { order = ... })

1445 ref_alias, -- optional alias for the reference (e.g. "ordered_children")

1446 back_ref_alias -- back reference name (e.g. "parent")

1447 )

1448

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.

1450

1451 This method is not only available for database result objects but also for database result lists.

1452

1453 --]]--

1454 function class_prototype.object:get_reference_selector(...)

1455 local list = self._class:create_list()

1456 list[1] = self

1457 return list:get_reference_selector(...)

1458 end

1459 --//--

1460

1461 --[[--

1462 db_list_or_object =

1463 <db_object>:load(

1464 ref_name, -- name of reference (e.g. "children")

1465 options, -- table options passed to the reference loader (e.g. { order = ... })

1466 ref_alias, -- optional alias for the reference (e.g. "ordered_children")

1467 back_ref_alias -- back reference name (e.g. "parent")

1468 )

1469

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.

1471

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.

1473

1474 --]]--

1475 function class_prototype.object.load(...)

1476 return class_prototype.object.get_reference_selector(...):exec()

1477 end

1478 --//--

1479

1480 --[[--

1481 db_class = -- same class returned

1482 <db_class>:add_reference{

1483 mode = mode, -- "11", "1m", "m1", or "mm" (one/many to one/many)

1484 primary = primary, -- only needed if mode=="11": set to true only in the referenced (not in the referencing) class

1485 to = to, -- referenced class (model), optionally as string or function returning the value (avoids autoload)

1486 this_key = this_key, -- name of key in this class (model)

1487 that_key = that_key, -- name of key in the other class (model) ("to" argument)

1488 ref = ref, -- name of reference in this class, referring to the other class

1489 back_ref = back_ref, -- name of reference in other class, referring to this class

1490 default_order = default_order, -- expression as passed to "assemble_command" used for sorting

1491 selector_generator = selector_generator, -- alternative function used as selector generator (use only, when you know what you are doing)

1492 connected_by_table = connected_by_table, -- connecting table used for many to many relations

1493 connected_by_this_key = connected_by_this_key, -- key in connecting table referring to "this_key" of this class (model)

1494 connected_by_that_key = connected_by_that_key -- key in connecting table referring to "that_key" in other class (model) ("to" argument)

1495 }

1496

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.

1498

1499 --]]--

1500 function class_prototype:add_reference(args)

1501 local selector_generator = args.selector_generator

1502 local mode = args.mode

1503 local primary = args.primary

1504 local to = args.to

1505 local this_key = args.this_key

1506 local that_key = args.that_key

1507 local connected_by_table = args.connected_by_table -- TODO: split to table and schema

1508 local connected_by_this_key = args.connected_by_this_key

1509 local connected_by_that_key = args.connected_by_that_key

1510 local ref = args.ref

1511 local back_ref = args.back_ref

1512 local default_order = args.default_order

1513 local model

1514 local function get_model()

1515 if not model then

1516 if type(to) == "string" then

1517 model = _G

1518 for path_element in string.gmatch(to, "[^.]+") do

1519 model = model[path_element]

1520 end

1521 elseif type(to) == "function" then

1522 model = to()

1523 else

1524 model = to

1525 end

1526 end

1527 if not model or model == _G then

1528 error("Could not get model for reference.")

1529 end

1530 return model

1531 end

1532 self.references[ref] = {

1533 mode = mode,

1534 primary = primary,

1535 this_key = this_key,

1536 that_key = connected_by_table and "mm_ref_" or that_key,

1537 ref = ref,

1538 back_ref = back_ref,

1539 selector_generator = selector_generator or function(list, options)

1540 -- TODO: support tuple keys

1541 local options = options or {}

1542 local model = get_model()

1543 -- TODO: too many records cause PostgreSQL command stack overflow

1544 local ids_used = {}

1545 local ids = { sep = ", " }

1546 for i, object in ipairs(list) do

1547 local id = object[this_key]

1548 if id ~= nil then

1549 if not ids_used[id] then

1550 ids[#ids+1] = {"?", id}

1551 ids_used[id] = true

1552 end

1553 end

1554 end

1555 if #ids == 0 then

1556 return model:new_selector():empty_list_mode()

1557 end

1558 local selector = model:new_selector()

1559 if connected_by_table then

1560 selector:join(

1561 connected_by_table,

1562 nil,

1563 {

1564 '$."$" = $."$"',

1565 {connected_by_table},

1566 {connected_by_that_key},

1567 {model:get_qualified_table()},

1568 {that_key}

1569 }

1570 )

1571 selector:add_field(

1572 {

1573 '$."$"',

1574 {connected_by_table},

1575 {connected_by_this_key}

1576 },

1577 'mm_ref_'

1578 )

1579 selector:add_where{

1580 '$."$" IN ($)',

1581 {connected_by_table},

1582 {connected_by_this_key},

1583 ids

1584 }

1585 else

1586 selector:add_where{'$."$" IN ($)', {model:get_qualified_table()}, {that_key}, ids}

1587 end

1588 if options.order == nil and default_order then

1589 selector:add_order_by(default_order)

1590 elseif options.order then

1591 selector:add_order_by(options.order)

1592 end

1593 return selector

1594 end

1595 }

1596 if mode == "m1" or (mode == "11" and not primary) then

1597 self.foreign_keys[this_key] = ref

1598 end

1599 return self

1600 end

1601 --//--

1602

1603 return _M

1604