webmcp: ff39d4a310b9 libraries/mondelefant/mondelefant.lua

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

129

130 --]]--

131 function selector_prototype:empty_list_mode()

132 self._mode = "empty_list"

133 return self

134 end

135 --//--

136

137 --[[--

138 db_selector =

139 <db_selector>:add_with(

140 expression = expression,

141 selector = selector

142 )

143

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

145 --]]--

146 function selector_prototype:add_with(expression, selector)

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

148 return self

149 end

150 --//--

151

152 --[[--

153 db_selector = -- same selector returned

154 <db_selector>:add_distinct_on(

155 expression -- expression as passed to "assemble_command"

156 )

157

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

159

160 --]]--

161 function selector_prototype:add_distinct_on(expression)

162 if self._distinct then

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

164 end

165 add(self._distinct_on, expression)

166 return self

167 end

168 --//--

169

170 --[[--

171 db_selector = -- same selector returned

172 <db_selector>:set_distinct()

173

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

175

176 --]]--

177 function selector_prototype:set_distinct()

178 if #self._distinct_on > 0 then

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

180 end

181 self._distinct = true

182 return self

183 end

184 --//--

185

186 --[[--

187 db_selector = -- same selector returned

188 <db_selector>:add_from(

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

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

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

192 )

193

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

195

196 This method is identical to "join".

197

198 --]]--

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

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

201 if not first then

202 if condition then

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

204 else

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

206 end

207 end

208 if getmetatable(expression) == selector_metatable then

209 if alias then

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

211 else

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

213 end

214 else

215 if alias then

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

217 else

218 add(self._from, expression)

219 end

220 end

221 if condition then

222 if first then

223 self:add_where(condition)

224 else

225 add(self._from, "ON")

226 add(self._from, condition)

227 end

228 end

229 return self

230 end

231 --//--

232

233 --[[--

234 db_selector = -- same selector returned

235 <db_selector>:add_where(

236 expression -- expression as passed to "assemble_command"

237 )

238

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

240

241 --]]--

242 function selector_prototype:add_where(expression)

243 add(self._where, expression)

244 return self

245 end

246 --//--

247

248 --[[--

249 db_selector = -- same selector returned

250 <db_selector>:add_group_by(

251 expression -- expression as passed to "assemble_command"

252 )

253

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

255

256 --]]--

257 function selector_prototype:add_group_by(expression)

258 add(self._group_by, expression)

259 return self

260 end

261 --//--

262

263 --[[--

264 db_selector = -- same selector returned

265 <db_selector>:add_having(

266 expression -- expression as passed to "assemble_command"

267 )

268

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

270

271 --]]--

272 function selector_prototype:add_having(expression)

273 add(self._having, expression)

274 return self

275 end

276 --//--

277

278 --[[--

279 db_selector = -- same selector returned

280 <db_selector>:add_combine(

281 expression -- expression as passed to "assemble_command"

282 )

283

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

285

286 --]]--

287 function selector_prototype:add_combine(expression)

288 add(self._combine, expression)

289 return self

290 end

291 --//--

292

293 --[[--

294 db_selector = -- same selector returned

295 <db_selector>:add_order_by(

296 expression -- expression as passed to "assemble_command"

297 )

298

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

300

301 --]]--

302 function selector_prototype:add_order_by(expression)

303 add(self._order_by, expression)

304 return self

305 end

306 --//--

307

308 --[[--

309 db_selector = -- same selector returned

310 <db_selector>:limit(

311 count -- integer used as LIMIT

312 )

313

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

315

316 --]]--

317 function selector_prototype:limit(count)

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

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

320 end

321 self._limit = count

322 return self

323 end

324 --//--

325

326 --[[--

327 db_selector = -- same selector returned

328 <db_selector>:offset(

329 count -- integer used as OFFSET

330 )

331

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

333

334 --]]--

335 function selector_prototype:offset(count)

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

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

338 end

339 self._offset = count

340 return self

341 end

342 --//--

343

344 --[[--

345 db_selector = -- same selector returned

346 <db_selector>:for_share()

347

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

349

350 --]]--

351 function selector_prototype:for_share()

352 self._read_lock.all = true

353 return self

354 end

355 --//--

356

357 --[[--

358 db_selector = -- same selector returned

359 <db_selector>:for_share_of(

360 expression -- expression as passed to "assemble_command"

361 )

362

363 Adds FOR SHARE OF to the statement, to share-lock all rows read by the named table(s). The selector is modified and returned.

364

365 --]]--

366 function selector_prototype:for_share_of(expression)

367 add(self._read_lock, expression)

368 return self

369 end

370 --//--

371

372 --[[--

373 db_selector = -- same selector returned

374 <db_selector>:for_update()

375

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

377

378 --]]--

379 function selector_prototype:for_update()

380 self._write_lock.all = true

381 return self

382 end

383 --//--

384

385 --[[--

386 db_selector = -- same selector returned

387 <db_selector>:for_update_of(

388 expression -- expression as passed to "assemble_command"

389 )

390

391 Adds FOR SHARE OF to the statement, to exclusivly lock all rows read by the named table(s). The selector is modified and returned.

392

393 --]]--

394 function selector_prototype:for_update_of(expression)

395 add(self._write_lock, expression)

396 return self

397 end

398 --//--

399

400 --[[--

401 db_selector = -- same selector returned

402 <db_selector>:reset_fields()

403

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

405

406 --]]--

407 function selector_prototype:reset_fields()

408 for idx in ipairs(self._fields) do

409 self._fields[idx] = nil

410 end

411 return self

412 end

413 --//--

414

415 --[[--

416 db_selector = -- same selector returned

417 <db_selector>:add_field(

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

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

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

421 )

422

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

424

425 --]]--

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

427 if alias then

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

429 else

430 add(self._fields, expression)

431 end

432 if options then

433 for i, option in ipairs(options) do

434 if option == "distinct" then

435 if alias then

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

437 else

438 self:add_distinct_on(expression)

439 end

440 elseif option == "grouped" then

441 if alias then

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

443 else

444 self:add_group_by(expression)

445 end

446 else

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

448 end

449 end

450 end

451 return self

452 end

453 --//--

454

455 --[[--

456 db_selector = -- same selector returned

457 <db_selector>:join(

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

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

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

461 )

462

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

464

465 This method is identical to "add_from".

466

467 --]]--

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

469 return self:add_from(...)

470 end

471 --//--

472

473 --[[--

474 db_selector = -- same selector returned

475 <db_selector>:from(

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

482

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

484

485 --]]--

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

487 if #self._from > 0 then

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

489 end

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

491 end

492 --//--

493

494 --[[--

495 db_selector = -- same selector returned

496 <db_selector>:left_join(

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

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

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

500 )

501

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

503

504 --]]--

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

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

507 if not first then

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

509 end

510 if alias then

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

512 else

513 add(self._from, expression)

514 end

515 if condition then

516 if first then

517 self:add_where(condition)

518 else

519 add(self._from, "ON")

520 add(self._from, condition)

521 end

522 end

523 return self

524 end

525 --//--

526

527 --[[--

528 db_selector = -- same selector returned

529 <db_selector>:union(

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

531 )

532

533 This method adds an UNION clause to the given selector. The selector is modified and returned. The selector (or expression) passed as argument to this function shall not contain any ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clauses.

534

535 --]]--

536 function selector_prototype:union(expression)

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

538 return self

539 end

540 --//--

541

542 --[[--

543 db_selector = -- same selector returned

544 <db_selector>:union_all(

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

546 )

547

548 This method adds an UNION ALL clause to the given selector. The selector is modified and returned. The selector (or expression) passed as argument to this function shall not contain any ORDER BY, LIMIT, FOR UPDATE or FOR SHARE clauses.

549

550 --]]--

551 function selector_prototype:union_all(expression)

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

553 return self

554 end

555 --//--

556

557 --[[--

558 db_selector = -- same selector returned

559 <db_selector>:intersect(

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

561 )

562

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

564

565 --]]--

566 function selector_prototype:intersect(expression)

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

568 return self

569 end

570 --//--

571

572 --[[--

573 db_selector = -- same selector returned

574 <db_selector>:intersect_all(

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

576 )

577

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

579

580 --]]--

581 function selector_prototype:intersect_all(expression)

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

583 return self

584 end

585 --//--

586

587 --[[--

588 db_selector = -- same selector returned

589 <db_selector>:except(

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

591 )

592

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

594

595 --]]--

596 function selector_prototype:except(expression)

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

598 return self

599 end

600 --//--

601

602 --[[--

603 db_selector = -- same selector returned

604 <db_selector>:except_all(

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

606 )

607

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

609

610 --]]--

611 function selector_prototype:except_all(expression)

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

613 return self

614 end

615 --//--

616

617 --[[--

618 db_selector = -- same selector returned

619 <db_selector>:set_class(

620 class -- database class (model)

621 )

622

623 This method makes the selector to return database result lists or objects of the given database class (model). The selector is modified and returned.

624

625 --]]--

626 function selector_prototype:set_class(class)

627 self._class = class

628 return self

629 end

630 --//--

631

632 --[[--

633 db_selector = -- same selector returned

634 <db_selector>:attach(

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

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

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

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

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

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

641 )

642

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

644

645 --]]--

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

647 self._attach = {

648 mode = mode,

649 data2 = data2,

650 field1 = field1,

651 field2 = field2,

652 ref1 = ref1,

653 ref2 = ref2

654 }

655 return self

656 end

657 --//--

658

659 function selector_metatable:__tostring()

660 local parts = {sep = " "}

661 if #self._with > 0 then

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

663 end

664 add(parts, "SELECT")

665 if self._distinct then

666 add(parts, "DISTINCT")

667 elseif #self._distinct_on > 0 then

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

669 end

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

671 if #self._from > 0 then

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

673 end

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

675 add(parts, "WHERE FALSE")

676 elseif #self._where > 0 then

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

678 end

679 if #self._group_by > 0 then

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

681 end

682 if #self._having > 0 then

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

684 end

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

686 add(parts, v)

687 end

688 if #self._order_by > 0 then

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

690 end

691 if self._mode == "empty_list" then

692 add(parts, "LIMIT 0")

693 elseif self._mode ~= "list" then

694 add(parts, "LIMIT 1")

695 elseif self._limit then

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

697 end

698 if self._offset then

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

700 end

701 if self._write_lock.all then

702 add(parts, "FOR UPDATE")

703 else

704 if self._read_lock.all then

705 add(parts, "FOR SHARE")

706 elseif #self._read_lock > 0 then

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

708 end

709 if #self._write_lock > 0 then

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

711 end

712 end

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

714 end

715

716 --[[--

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

718 result = -- database result list or object

719 <db_selector>:try_exec()

720

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

722

723 --]]--

724 function selector_prototype:try_exec()

725 if self._mode == "empty_list" then

726 if self._class then

727 return nil, self._class:create_list()

728 else

729 return nil, self._db_conn:create_list()

730 end

731 end

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

733 if db_error then

734 return db_error

735 elseif db_result then

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

737 if self._attach then

738 attach(

739 self._attach.mode,

740 db_result,

741 self._attach.data2,

742 self._attach.field1,

743 self._attach.field2,

744 self._attach.ref1,

745 self._attach.ref2

746 )

747 end

748 return nil, db_result

749 else

750 return nil

751 end

752 end

753 --//--

754

755 --[[--

756 result = -- database result list or object

757 <db_selector>:exec()

758

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

760

761 --]]--

762 function selector_prototype:exec()

763 local db_error, result = self:try_exec()

764 if db_error then

765 db_error:escalate()

766 else

767 return result

768 end

769 end

770 --//--

771

772 --[[--

773 count = -- number of rows returned

774 <db_selector>:count()

775

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

777

778 --]]--

779 function selector_prototype:count()

780 if not self._count then

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

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

783 count_selector:add_from(self)

784 count_selector:single_object_mode()

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

786 end

787 return self._count

788 end

789 --//--

790

791

792

793 -----------------

794 -- attachments --

795 -----------------

796

797 local function attach_key(row, fields)

798 local t = type(fields)

799 if t == "string" then

800 return tostring(row[fields])

801 elseif t == "table" then

802 local r = {}

803 for idx, field in ipairs(fields) do

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

805 end

806 return table.concat(r)

807 else

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

809 end

810 end

811

812 --[[--

813 mondelefant.attach(

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

815 data1, -- first database result list or object

816 data2, -- second database result list or object

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

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

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

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

821 )

822

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

824

825 --]]--

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

827 local many1, many2

828 if mode == "11" then

829 many1 = false

830 many2 = false

831 elseif mode == "1m" then

832 many1 = false

833 many2 = true

834 elseif mode == "m1" then

835 many1 = true

836 many2 = false

837 elseif mode == "mm" then

838 many1 = true

839 many2 = true

840 else

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

842 end

843 local list1, list2

844 if data1._type == "object" then

845 list1 = { data1 }

846 elseif data1._type == "list" then

847 list1 = data1

848 else

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

850 end

851 if data2._type == "object" then

852 list2 = { data2 }

853 elseif data2._type == "list" then

854 list2 = data2

855 else

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

857 end

858 local hash1 = {}

859 local hash2 = {}

860 if ref2 then

861 for i, row in ipairs(list1) do

862 local key = attach_key(row, key1)

863 local list = hash1[key]

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

865 list[#list + 1] = row

866 end

867 end

868 if ref1 then

869 for i, row in ipairs(list2) do

870 local key = attach_key(row, key2)

871 local list = hash2[key]

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

873 list[#list + 1] = row

874 end

875 for i, row in ipairs(list1) do

876 local key = attach_key(row, key1)

877 local matching_rows = hash2[key]

878 if many2 then

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

880 list._class = data2._class

881 row._ref[ref1] = list

882 elseif matching_rows and #matching_rows == 1 then

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

884 else

885 row._ref[ref1] = false

886 end

887 end

888 end

889 if ref2 then

890 for i, row in ipairs(list2) do

891 local key = attach_key(row, key2)

892 local matching_rows = hash1[key]

893 if many1 then

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

895 list._class = data1._class

896 row._ref[ref2] = list

897 elseif matching_rows and #matching_rows == 1 then

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

899 else

900 row._ref[ref2] = false

901 end

902 end

903 end

904 end

905 --//--

906

907

908

909 ------------------

910 -- model system --

911 ------------------

912

913 --[[--

914 <db_class>.primary_key

915

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

917

918 --]]--

919 class_prototype.primary_key = "id"

920 --//--

921

922 --[[--

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

924 <db_class>:get_db_conn()

925

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

927

928 --]]--

929 function class_prototype:get_db_conn()

930 error(

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

932 "has to be implemented."

933 )

934 end

935 --//--

936

937 --[[--

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

939 <db_class>:get_qualified_table()

940

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

942

943 --]]--

944 function class_prototype:get_qualified_table()

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

946 if self.schema then

947 return '"' .. self.schema .. '"."' .. self.table .. '"'

948 else

949 return '"' .. self.table .. '"'

950 end

951 end

952 --]]--

953

954 --[[--

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

956 <db_class>:get_qualified_table_literal()

957

958 This method returns a string with an SQL literal representing the given table. It causes ambiguities when the table name contains a dot (".") character.

959

960 --]]--

961 function class_prototype:get_qualified_table_literal()

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

963 if self.schema then

964 return self.schema .. '.' .. self.table

965 else

966 return self.table

967 end

968 end

969 --//--

970

971 --[[--

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

973 <db_class>:get_primary_key_list()

974

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

976

977 --]]--

978 function class_prototype:get_primary_key_list()

979 local primary_key = self.primary_key

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

981 return {primary_key}

982 else

983 return primary_key

984 end

985 end

986 --//--

987

988 --[[--

989 columns = -- list of columns

990 <db_class>:get_columns()

991

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

993

994 --]]--

995 function class_prototype:get_columns()

996 if self._columns then

997 return self._columns

998 end

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

1000 selector:set_class(self)

1001 selector:from(self:get_qualified_table())

1002 selector:add_field("*")

1003 selector:add_where("FALSE")

1004 local db_result = selector:exec()

1005 local connection = db_result._connection

1006 local columns = {}

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

1008 local key = info.field_name

1009 local value = {

1010 name = key,

1011 type = connection.type_mappings[info.type]

1012 }

1013 columns[key] = value

1014 table.insert(columns, value)

1015 end

1016 self._columns = columns

1017 return columns

1018 end

1019 --//--

1020

1021 --[[--

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

1023 <db_class>:new_selector(

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

1025 )

1026

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

1028

1029 --]]--

1030 function class_prototype:new_selector(db_conn)

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

1032 selector:set_class(self)

1033 selector:from(self:get_qualified_table())

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

1035 return selector

1036 end

1037 --//--

1038

1039 --[[--

1040 db_list = -- database result being an empty list

1041 <db_class>:create_list()

1042

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

1044

1045 --]]--

1046 function class_prototype:create_list()

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

1048 list._class = self

1049 return list

1050 end

1051 --//--

1052

1053 --[[--

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

1055 <db_class>:new()

1056

1057 Creates a new object of the given class.

1058

1059 --]]--

1060 function class_prototype:new()

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

1062 object._class = self

1063 object._new = true

1064 return object

1065 end

1066 --//--

1067

1068 --[[--

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

1070 <db_object>:try_save()

1071

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

1073

1074 --]]--

1075 function class_prototype.object:try_save()

1076 if not self._class then

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

1078 end

1079 local primary_key = self._class:get_primary_key_list()

1080 local primary_key_sql = { sep = ", " }

1081 for idx, value in ipairs(primary_key) do

1082 primary_key_sql[idx] = '"' .. value .. '"'

1083 end

1084 if self._new then

1085 local fields = {sep = ", "}

1086 local values = {sep = ", "}

1087 for key, dummy in pairs(self._dirty or {}) do

1088 add(fields, {'"$"', {key}})

1089 add(values, {'?', self[key]})

1090 end

1091 if compat_returning then -- compatibility for PostgreSQL 8.1

1092 local db_error, db_result1, db_result2 = self._connection:try_query(

1093 {

1094 'INSERT INTO $ ($) VALUES ($)',

1095 {self._class:get_qualified_table()},

1096 fields,

1097 values,

1098 primary_key_sql

1099 },

1100 "list",

1101 {

1102 'SELECT currval(?)',

1103 self._class.table .. '_id_seq'

1104 },

1105 "object"

1106 )

1107 if db_error then

1108 return db_error

1109 end

1110 self.id = db_result2.id

1111 else

1112 local db_error, db_result

1113 if #fields == 0 then

1114 db_error, db_result = self._connection:try_query(

1115 {

1116 'INSERT INTO $ DEFAULT VALUES RETURNING ($)',

1117 {self._class:get_qualified_table()},

1118 primary_key_sql

1119 },

1120 "object"

1121 )

1122 else

1123 db_error, db_result = self._connection:try_query(

1124 {

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

1126 {self._class:get_qualified_table()},

1127 fields,

1128 values,

1129 primary_key_sql

1130 },

1131 "object"

1132 )

1133 end

1134 if db_error then

1135 return db_error

1136 end

1137 for idx, value in ipairs(primary_key) do

1138 self[value] = db_result[value]

1139 end

1140 end

1141 self._new = false

1142 else

1143 local command_sets = {sep = ", "}

1144 for key, dummy in pairs(self._dirty or {}) do

1145 add(command_sets, {'"$" = ?', {key}, self[key]})

1146 end

1147 if #command_sets >= 1 then

1148 local primary_key_compare = {sep = " AND "}

1149 for idx, value in ipairs(primary_key) do

1150 primary_key_compare[idx] = {

1151 "$ = ?",

1152 {'"' .. value .. '"'},

1153 self[value]

1154 }

1155 end

1156 local db_error = self._connection:try_query{

1157 'UPDATE $ SET $ WHERE $',

1158 {self._class:get_qualified_table()},

1159 command_sets,

1160 primary_key_compare

1161 }

1162 if db_error then

1163 return db_error

1164 end

1165 end

1166 end

1167 return nil

1168 end

1169 --//--

1170

1171 --[[--

1172 <db_object>:save()

1173

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

1175

1176 --]]--

1177 function class_prototype.object:save()

1178 local db_error = self:try_save()

1179 if db_error then

1180 db_error:escalate()

1181 end

1182 return self

1183 end

1184 --//--

1185

1186 --[[--

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

1188 <db_object>:try_destroy()

1189

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

1191

1192 --]]--

1193 function class_prototype.object:try_destroy()

1194 if not self._class then

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

1196 end

1197 local primary_key = self._class:get_primary_key_list()

1198 local primary_key_compare = {sep = " AND "}

1199 for idx, value in ipairs(primary_key) do

1200 primary_key_compare[idx] = {

1201 "$ = ?",

1202 {'"' .. value .. '"'},

1203 self[value]

1204 }

1205 end

1206 return self._connection:try_query{

1207 'DELETE FROM $ WHERE $',

1208 {self._class:get_qualified_table()},

1209 primary_key_compare

1210 }

1211 end

1212 --//--

1213

1214 --[[--

1215 <db_object>:destroy()

1216

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

1218

1219 --]]--

1220 function class_prototype.object:destroy()

1221 local db_error = self:try_destroy()

1222 if db_error then

1223 db_error:escalate()

1224 end

1225 return self

1226 end

1227 --//--

1228

1229 --[[--

1230 db_selector =

1231 <db_list>:get_reference_selector(

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

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

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

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

1236 )

1237

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

1239

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

1241

1242 --]]--

1243 function class_prototype.list:get_reference_selector(

1244 ref_name, options, ref_alias, back_ref_alias

1245 )

1246 local ref_info = self._class.references[ref_name]

1247 if not ref_info then

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

1249 end

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

1251 local mode = ref_info.mode

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

1253 mode = "m1"

1254 elseif mode == "m1" then

1255 mode = "1m"

1256 end

1257 local ref_alias = ref_alias

1258 if ref_alias == false then

1259 ref_alias = nil

1260 elseif ref_alias == nil then

1261 ref_alias = ref_name

1262 end

1263 local back_ref_alias

1264 if back_ref_alias == false then

1265 back_ref_alias = nil

1266 elseif back_ref_alias == nil then

1267 back_ref_alias = ref_info.back_ref

1268 end

1269 selector:attach(

1270 mode,

1271 self,

1272 ref_info.that_key, ref_info.this_key,

1273 back_ref_alias or ref_info.back_ref, ref_alias or ref_name

1274 )

1275 return selector

1276 end

1277 --//--

1278

1279 --[[--

1280 db_list_or_object =

1281 <db_list>:load(

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

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

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

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

1286 )

1287

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

1289

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

1291

1292 --]]--

1293 function class_prototype.list.load(...)

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

1295 end

1296 --//--

1297

1298 --[[--

1299 db_object =

1300 <db_object>:get_reference_selector(

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

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

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

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

1305 )

1306

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

1308

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

1310

1311 --]]--

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

1313 local list = self._class:create_list()

1314 list[1] = self

1315 return list:get_reference_selector(...)

1316 end

1317 --//--

1318

1319 --[[--

1320 db_list_or_object =

1321 <db_object>:load(

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

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

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

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

1326 )

1327

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

1329

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

1331

1332 --]]--

1333 function class_prototype.object.load(...)

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

1335 end

1336 --//--

1337

1338 --[[--

1339 db_class = -- same class returned

1340 <db_class>:add_reference{

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

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

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

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

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

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

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

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

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

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

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

1352 }

1353

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

1355

1356 --]]--

1357 function class_prototype:add_reference(args)

1358 local selector_generator = args.selector_generator

1359 local mode = args.mode

1360 local to = args.to

1361 local this_key = args.this_key

1362 local that_key = args.that_key

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

1364 local connected_by_this_key = args.connected_by_this_key

1365 local connected_by_that_key = args.connected_by_that_key

1366 local ref = args.ref

1367 local back_ref = args.back_ref

1368 local default_order = args.default_order

1369 local model

1370 local function get_model()

1371 if not model then

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

1373 model = _G

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

1375 model = model[path_element]

1376 end

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

1378 model = to()

1379 else

1380 model = to

1381 end

1382 end

1383 if not model or model == _G then

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

1385 end

1386 return model

1387 end

1388 self.references[ref] = {

1389 mode = mode,

1390 this_key = this_key,

1391 that_key = connected_by_table and "mm_ref_" or that_key,

1392 ref = ref,

1393 back_ref = back_ref,

1394 selector_generator = selector_generator or function(list, options)

1395 -- TODO: support tuple keys

1396 local options = options or {}

1397 local model = get_model()

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

1399 local ids = { sep = ", " }

1400 for i, object in ipairs(list) do

1401 local id = object[this_key]

1402 if id ~= nil then

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

1404 end

1405 end

1406 if #ids == 0 then

1407 return model:new_selector():empty_list_mode()

1408 end

1409 local selector = model:new_selector()

1410 if connected_by_table then

1411 selector:join(

1412 connected_by_table,

1413 nil,

1414 {

1415 '$."$" = $."$"',

1416 {connected_by_table},

1417 {connected_by_that_key},

1418 {model:get_qualified_table()},

1419 {that_key}

1420 }

1421 )

1422 selector:add_field(

1423 {

1424 '$."$"',

1425 {connected_by_table},

1426 {connected_by_this_key}

1427 },

1428 'mm_ref_'

1429 )

1430 selector:add_where{

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

1432 {connected_by_table},

1433 {connected_by_this_key},

1434 ids

1435 }

1436 else

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

1438 end

1439 if options.order == nil and default_order then

1440 selector:add_order_by(default_order)

1441 elseif options.order then

1442 selector:add_order_by(options.order)

1443 end

1444 return selector

1445 end

1446 }

1447 if mode == "m1" or mode == "11" then

1448 self.foreign_keys[this_key] = ref

1449 end

1450 return self

1451 end

1452 --//--

1453

1454 return _M

1455