Coverage for gws-app/gws/lib/cql/builder.py: 97%

1"""Build database expressions from CQL2 parse trees.""" 

2 

3from typing import Any, cast 

4 

5import operator 

6 

7import gws 

8import gws.lib.crs 

9import gws.lib.datetimex as dtx 

10import gws.lib.sa as sa 

11 

12from .parser import Node, C 

13 

14 

15class BuildError(Exception): 

16 """A parse tree node or function is not supported by the builder, or its arguments are invalid.""" 

17 

18 pass 

19 

20 

21class Builder: 

22 """Walks a CQL2 parse tree and dispatches nodes to handler methods. 

23 

24 Node types go to ``build_<type>`` methods, standard functions to ``func_<name>`` methods 

25 (names are lowercased). This class implements no node types; subclasses add handlers. 

26 """ 

27 

28 def get_method(self, name): 

29 """Find a handler method. 

30 

31 Args: 

32 name: Method name, case-insensitive. 

33 

34 Returns: 

35 The method, or ``None`` if the builder does not implement it. 

36 """ 

37 

38 return getattr(self, name.lower(), None) 

39 

40 def build(self, e): 

41 """Build an expression from a parse tree node. 

42 

43 Calls ``build_<type>`` with the node arguments, or ``build_operator`` for operators without one. 

44 

45 Args: 

46 e: Parse tree node. 

47 

48 Returns: 

49 The expression returned by the handler. 

50 

51 Raises: 

52 BuildError: If the node type is not implemented. 

53 """ 

54 

55 fn = self.get_method('build_' + e[0]) 

56 if fn: 

57 return fn(e[1:]) 

58 

59 if e[0] in C.OPERATORS: 

60 return self.build_operator(e[0], e[1:]) 

61 

62 raise BuildError(f'CQL: node {e[0]!r} not implemented') 

63 

64 def build_operator(self, op, args): 

65 """Build an operator expression. 

66 

67 Args: 

68 op: Operator, e.g. ``>`` or ``+``. 

69 args: Operand nodes. 

70 

71 Returns: 

72 The expression. Not implemented in this class. 

73 

74 Raises: 

75 BuildError: Always, in this class. 

76 """ 

77 

78 raise BuildError(f'CQL: operator {op!r} not implemented') 

79 

80 def build_function(self, args): 

81 """Build a standard function call by calling ``func_<name>`` with the argument nodes. 

82 

83 Args: 

84 args: Function name, followed by the argument nodes. 

85 

86 Returns: 

87 The expression returned by the handler. 

88 

89 Raises: 

90 BuildError: If the function is not implemented. 

91 """ 

92 

93 # [FUNCTION, name, arg1, arg2, ...] 

94 

95 fn = self.get_method('func_' + args[0]) 

96 if fn: 

97 return fn(args[1:]) 

98 

99 raise BuildError(f'CQL: function {args[0]!r} not implemented') 

100 

101 def build_user_function(self, args): 

102 """Build a non-standard function call. Subclasses handle their own functions here. 

103 

104 Args: 

105 args: Function name as written, followed by the argument nodes. 

106 

107 Returns: 

108 The expression. Not implemented in this class. 

109 

110 Raises: 

111 BuildError: Always, in this class. 

112 """ 

113 

114 # [USER_FUNCTION, name, arg1, arg2, ...] 

115 

116 raise BuildError(f'CQL: function {args[0]!r} not implemented') 

117 

118 def value(self, e) -> Any: 

119 """Unwrap a literal node into a plain python value. 

120 

121 Args: 

122 e: Literal node. Array nodes are unwrapped recursively. 

123 

124 Returns: 

125 The value, a list for arrays. 

126 

127 Raises: 

128 BuildError: If the node is not a literal. 

129 """ 

130 

131 if e[0] == Node.ARRAY: 

132 return [self.value(a) for a in e[1:]] 

133 if e[0] in C.LITERALS: 

134 return e[1] 

135 raise BuildError(f'CQL: expected a literal, got {e[0]!r}') 

136 

137 

138class SqlBuilder(Builder): 

139 """Builds SQLAlchemy expressions for a postgis table. 

140 

141 Names refer to table columns, literals become bound parameters, geometries are in WGS84. 

142 All standard CQL2 functions are implemented, user functions are not. 

143 """ 

144 

145 _binary_ops = { 

146 '>': operator.gt, 

147 '<': operator.lt, 

148 '>=': operator.ge, 

149 '<=': operator.le, 

150 '=': operator.eq, 

151 '!=': operator.ne, 

152 '<>': operator.ne, 

153 '*': operator.mul, 

154 '/': operator.truediv, 

155 '+': operator.add, 

156 '-': operator.sub, 

157 '%': operator.mod, 

158 # sqlalchemy columns don't support '**' 

159 '^': sa.func.power, 

160 } 

161 

162 def __init__(self, table: sa.Table): 

163 """Create a builder. 

164 

165 Args: 

166 table: Table whose columns are referenced by names. 

167 """ 

168 self.table = table 

169 

170 def build_operator(self, op, args): 

171 fn = self._binary_ops.get(op) 

172 if fn: 

173 a, b = args 

174 return fn(self.build(a), self.build(b)) 

175 

176 return super().build_operator(op, args) 

177 

178 def build_name(self, args): 

179 """Build a column reference. 

180 

181 Args: 

182 args: Name parts, only the first is used as the column name. 

183 

184 Returns: 

185 The table column. 

186 

187 Raises: 

188 BuildError: If the table has no such column. 

189 """ 

190 col = self.table.c.get(args[0]) 

191 if col is None: 

192 raise BuildError(f'CQL: unknown column {args[0]!r}') 

193 return col 

194 

195 def build_array(self, args): 

196 """Build an array. 

197 

198 Args: 

199 args: Element nodes. 

200 

201 Returns: 

202 A list of expressions. 

203 """ 

204 return [self.build(a) for a in args] 

205 

206 def build_bool(self, args): 

207 """Build a boolean literal. 

208 

209 Args: 

210 args: A list with the value. 

211 

212 Returns: 

213 A bound parameter. 

214 """ 

215 return self.literal(args[0]) 

216 

217 def build_float(self, args): 

218 """Build a float literal. 

219 

220 Args: 

221 args: A list with the value. 

222 

223 Returns: 

224 A bound parameter. 

225 """ 

226 return self.literal(args[0]) 

227 

228 def build_int(self, args): 

229 """Build a integer literal. 

230 

231 Args: 

232 args: A list with the value. 

233 

234 Returns: 

235 A bound parameter. 

236 """ 

237 return self.literal(args[0]) 

238 

239 def build_string(self, args): 

240 """Build a string literal. 

241 

242 Args: 

243 args: A list with the value. 

244 

245 Returns: 

246 A bound parameter. 

247 """ 

248 return self.literal(args[0]) 

249 

250 def build_date(self, args): 

251 """Build a date literal. 

252 

253 Args: 

254 args: A list with the date value. 

255 

256 Returns: 

257 A ``DATE`` expression. 

258 """ 

259 return sa.cast(args[0], sa.DATE()) 

260 

261 def build_timestamp(self, args): 

262 """Build a timestamp literal. 

263 

264 Args: 

265 args: A list with the datetime value. 

266 

267 Returns: 

268 A ``timestamptz`` expression. 

269 """ 

270 return sa.cast(args[0], sa.TIMESTAMP(timezone=True)) 

271 

272 def build_wkt(self, args): 

273 """Build a geometry literal. 

274 

275 Args: 

276 args: A list with the WKT string. 

277 

278 Returns: 

279 A geometry in WGS84. 

280 """ 

281 return sa.func.ST_GeomFromText(args[0], gws.lib.crs.WGS84.srid) 

282 

283 def build_bbox(self, args): 

284 """Build a bounding box geometry. 

285 

286 Args: 

287 args: Four numbers: min x, min y, max x, max y. 

288 

289 Returns: 

290 A rectangle geometry in WGS84. 

291 """ 

292 minx, miny, maxx, maxy = args 

293 return sa.func.ST_MakeEnvelope(minx, miny, maxx, maxy, gws.lib.crs.WGS84.srid) 

294 

295 ## 

296 

297 def build_and(self, args): 

298 """Build a conjunction. 

299 

300 Args: 

301 args: Operand nodes. 

302 

303 Returns: 

304 An ``AND`` expression. 

305 """ 

306 return sa.and_(*[cast(sa.BinaryExpression, self.build(a)) for a in args]) 

307 

308 def build_or(self, args): 

309 """Build a disjunction. 

310 

311 Args: 

312 args: Operand nodes. 

313 

314 Returns: 

315 An ``OR`` expression. 

316 """ 

317 return sa.or_(*[cast(sa.BinaryExpression, self.build(a)) for a in args]) 

318 

319 def build_not(self, args): 

320 """Build a negation. 

321 

322 Args: 

323 args: A list with the operand node. 

324 

325 Returns: 

326 A ``NOT`` expression. 

327 """ 

328 return sa.not_(cast(sa.BinaryExpression, self.build(args[0]))) 

329 

330 def build_between(self, args): 

331 """Build a ``BETWEEN`` predicate. 

332 

333 Args: 

334 args: The value, lower bound and upper bound nodes. 

335 

336 Returns: 

337 A ``BETWEEN`` expression. 

338 """ 

339 col = self.build(args[0]) 

340 a = self.build(args[1]) 

341 b = self.build(args[2]) 

342 return col.between(a, b) 

343 

344 def build_not_between(self, args): 

345 """Build a ``NOT BETWEEN`` predicate. 

346 

347 Args: 

348 args: The value, lower bound and upper bound nodes. 

349 

350 Returns: 

351 A negated ``BETWEEN`` expression. 

352 """ 

353 return sa.not_(self.build_between(args)) 

354 

355 def build_in(self, args): 

356 """Build an ``IN`` predicate. 

357 

358 Args: 

359 args: The value node, followed by the list item nodes. 

360 

361 Returns: 

362 An ``IN`` expression. 

363 """ 

364 col = self.build(args[0]) 

365 ls = [self.build(a) for a in args[1:]] 

366 return col.in_(ls) 

367 

368 def build_not_in(self, args): 

369 """Build a ``NOT IN`` predicate. 

370 

371 Args: 

372 args: The value node, followed by the list item nodes. 

373 

374 Returns: 

375 A negated ``IN`` expression. 

376 """ 

377 return sa.not_(self.build_in(args)) 

378 

379 def build_like(self, args): 

380 """Build a ``LIKE`` predicate. 

381 

382 Args: 

383 args: The value and pattern nodes. 

384 

385 Returns: 

386 A ``LIKE`` expression. 

387 """ 

388 col = self.build(args[0]) 

389 return col.like(self.build(args[1])) 

390 

391 def build_not_like(self, args): 

392 """Build a ``NOT LIKE`` predicate. 

393 

394 Args: 

395 args: The value and pattern nodes. 

396 

397 Returns: 

398 A negated ``LIKE`` expression. 

399 """ 

400 return sa.not_(self.build_like(args)) 

401 

402 def build_is_null(self, args): 

403 """Build an ``IS NULL`` predicate. 

404 

405 Args: 

406 args: A list with the value node. 

407 

408 Returns: 

409 An ``IS NULL`` expression. 

410 """ 

411 col = self.build(args[0]) 

412 return col.is_(None) 

413 

414 def build_not_null(self, args): 

415 """Build an ``IS NOT NULL`` predicate. 

416 

417 Args: 

418 args: A list with the value node. 

419 

420 Returns: 

421 An ``IS NOT NULL`` expression. 

422 """ 

423 col = self.build(args[0]) 

424 return col.isnot(None) 

425 

426 ## 

427 

428 def func_s_intersects(self, args): 

429 """Build the ``S_INTERSECTS`` spatial predicate. 

430 

431 Args: 

432 args: Two geometry nodes. 

433 

434 Returns: 

435 An ``ST_Intersects`` expression. 

436 """ 

437 return sa.func.ST_Intersects(self.build(args[0]), self.build(args[1])) 

438 

439 def func_s_contains(self, args): 

440 """Build the ``S_CONTAINS`` spatial predicate. 

441 

442 Args: 

443 args: Two geometry nodes. 

444 

445 Returns: 

446 An ``ST_Contains`` expression. 

447 """ 

448 return sa.func.ST_Contains(self.build(args[0]), self.build(args[1])) 

449 

450 def func_s_crosses(self, args): 

451 """Build the ``S_CROSSES`` spatial predicate. 

452 

453 Args: 

454 args: Two geometry nodes. 

455 

456 Returns: 

457 An ``ST_Crosses`` expression. 

458 """ 

459 return sa.func.ST_Crosses(self.build(args[0]), self.build(args[1])) 

460 

461 def func_s_disjoint(self, args): 

462 """Build the ``S_DISJOINT`` spatial predicate. 

463 

464 Args: 

465 args: Two geometry nodes. 

466 

467 Returns: 

468 An ``ST_Disjoint`` expression. 

469 """ 

470 return sa.func.ST_Disjoint(self.build(args[0]), self.build(args[1])) 

471 

472 def func_s_equals(self, args): 

473 """Build the ``S_EQUALS`` spatial predicate. 

474 

475 Args: 

476 args: Two geometry nodes. 

477 

478 Returns: 

479 An ``ST_Equals`` expression. 

480 """ 

481 return sa.func.ST_Equals(self.build(args[0]), self.build(args[1])) 

482 

483 def func_s_overlaps(self, args): 

484 """Build the ``S_OVERLAPS`` spatial predicate. 

485 

486 Args: 

487 args: Two geometry nodes. 

488 

489 Returns: 

490 An ``ST_Overlaps`` expression. 

491 """ 

492 return sa.func.ST_Overlaps(self.build(args[0]), self.build(args[1])) 

493 

494 def func_s_touches(self, args): 

495 """Build the ``S_TOUCHES`` spatial predicate. 

496 

497 Args: 

498 args: Two geometry nodes. 

499 

500 Returns: 

501 An ``ST_Touches`` expression. 

502 """ 

503 return sa.func.ST_Touches(self.build(args[0]), self.build(args[1])) 

504 

505 def func_s_within(self, args): 

506 """Build the ``S_WITHIN`` spatial predicate. 

507 

508 Args: 

509 args: Two geometry nodes. 

510 

511 Returns: 

512 An ``ST_Within`` expression. 

513 """ 

514 return sa.func.ST_Within(self.build(args[0]), self.build(args[1])) 

515 

516 ## 

517 

518 def func_casei(self, args): 

519 """Build the ``CASEI`` function. 

520 

521 Args: 

522 args: A string node. 

523 

524 Returns: 

525 The lowercased value. 

526 """ 

527 return sa.func.lower(self.build(args[0])) 

528 

529 def func_accenti(self, args): 

530 """Build the ``ACCENTI`` function. 

531 

532 Args: 

533 args: A string node. 

534 

535 Returns: 

536 The value without accents, using the postgres ``unaccent`` extension. 

537 """ 

538 return sa.func.unaccent(self.build(args[0])) 

539 

540 ## 

541 

542 def func_bbox(self, args): 

543 """Build the ``BBOX`` function. 

544 

545 Args: 

546 args: Four number literal nodes: min x, min y, max x, max y. 

547 

548 Returns: 

549 A rectangle geometry in WGS84. 

550 

551 Raises: 

552 BuildError: If an argument is not a literal. 

553 """ 

554 return self.build_bbox([self.value(a) for a in args]) 

555 

556 def func_timestamp(self, args): 

557 """Build the ``TIMESTAMP`` function. 

558 

559 Args: 

560 args: An ISO date-time string literal node. 

561 

562 Returns: 

563 A ``timestamptz`` expression. 

564 

565 Raises: 

566 BuildError: If the argument is not a literal. 

567 """ 

568 dt = dtx.from_iso_string(self.value(args[0]), 'UTC') 

569 return sa.cast(dt, sa.TIMESTAMP(timezone=True)) 

570 

571 def func_date(self, args): 

572 """Build the ``DATE`` function. 

573 

574 Args: 

575 args: An ISO date string literal node. 

576 

577 Returns: 

578 A ``DATE`` expression. 

579 

580 Raises: 

581 BuildError: If the argument is not a literal. 

582 """ 

583 dt = dtx.from_iso_string(self.value(args[0]), 'UTC') 

584 return sa.cast(dt, sa.DATE()) 

585 

586 def func_interval(self, args): 

587 """Reject ``INTERVAL`` outside of temporal predicates. 

588 

589 Args: 

590 args: Interval bound nodes. 

591 

592 Raises: 

593 BuildError: Always. 

594 """ 

595 raise BuildError('CQL: INTERVAL is only allowed in temporal predicates') 

596 

597 ## 

598 

599 def func_t_equals(self, args): 

600 """Build the ``T_EQUALS`` temporal predicate. 

601 

602 Args: 

603 args: Two temporal nodes, instants or intervals. 

604 

605 Returns: 

606 A condition that is true if both ranges are equal. 

607 """ 

608 a, b = self.temporal_pair(args) 

609 return a == b 

610 

611 def func_t_after(self, args): 

612 """Build the ``T_AFTER`` temporal predicate. 

613 

614 Args: 

615 args: Two temporal nodes, instants or intervals. 

616 

617 Returns: 

618 A condition that is true if the first range starts after the second ends. 

619 """ 

620 a, b = self.temporal_pair(args) 

621 return sa.func.lower(a) > sa.func.upper(b) 

622 

623 def func_t_before(self, args): 

624 """Build the ``T_BEFORE`` temporal predicate. 

625 

626 Args: 

627 args: Two temporal nodes, instants or intervals. 

628 

629 Returns: 

630 A condition that is true if the first range ends before the second starts. 

631 """ 

632 a, b = self.temporal_pair(args) 

633 return sa.func.upper(a) < sa.func.lower(b) 

634 

635 def func_t_meets(self, args): 

636 """Build the ``T_MEETS`` temporal predicate. 

637 

638 Args: 

639 args: Two temporal nodes, instants or intervals. 

640 

641 Returns: 

642 A condition that is true if the first range ends where the second starts. 

643 """ 

644 a, b = self.temporal_pair(args) 

645 return sa.func.upper(a) == sa.func.lower(b) 

646 

647 def func_t_metby(self, args): 

648 """Build the ``T_METBY`` temporal predicate. 

649 

650 Args: 

651 args: Two temporal nodes, instants or intervals. 

652 

653 Returns: 

654 A condition that is true if the first range starts where the second ends. 

655 """ 

656 a, b = self.temporal_pair(args) 

657 return sa.func.lower(a) == sa.func.upper(b) 

658 

659 def func_t_during(self, args): 

660 """Build the ``T_DURING`` temporal predicate. 

661 

662 Args: 

663 args: Two temporal nodes, instants or intervals. 

664 

665 Returns: 

666 A condition that is true if the first range lies strictly inside the second. 

667 """ 

668 a, b = self.temporal_pair(args) 

669 return sa.and_( 

670 sa.func.lower(a) > sa.func.lower(b), 

671 sa.func.upper(a) < sa.func.upper(b), 

672 ) 

673 

674 def func_t_contains(self, args): 

675 """Build the ``T_CONTAINS`` temporal predicate. 

676 

677 Args: 

678 args: Two temporal nodes, instants or intervals. 

679 

680 Returns: 

681 A condition that is true if the second range lies strictly inside the first. 

682 """ 

683 a, b = self.temporal_pair(args) 

684 return sa.and_( 

685 sa.func.lower(a) < sa.func.lower(b), 

686 sa.func.upper(a) > sa.func.upper(b), 

687 ) 

688 

689 def func_t_overlaps(self, args): 

690 """Build the ``T_OVERLAPS`` temporal predicate. 

691 

692 Args: 

693 args: Two temporal nodes, instants or intervals. 

694 

695 Returns: 

696 A condition that is true if the first range starts before the second and ends inside it. 

697 """ 

698 a, b = self.temporal_pair(args) 

699 return sa.and_( 

700 sa.func.lower(a) < sa.func.lower(b), 

701 sa.func.upper(a) > sa.func.lower(b), 

702 sa.func.upper(a) < sa.func.upper(b), 

703 ) 

704 

705 def func_t_overlappedby(self, args): 

706 """Build the ``T_OVERLAPPEDBY`` temporal predicate. 

707 

708 Args: 

709 args: Two temporal nodes, instants or intervals. 

710 

711 Returns: 

712 A condition that is true if the first range starts inside the second and ends after it. 

713 """ 

714 a, b = self.temporal_pair(args) 

715 return sa.and_( 

716 sa.func.lower(a) > sa.func.lower(b), 

717 sa.func.lower(a) < sa.func.upper(b), 

718 sa.func.upper(a) > sa.func.upper(b), 

719 ) 

720 

721 def func_t_starts(self, args): 

722 """Build the ``T_STARTS`` temporal predicate. 

723 

724 Args: 

725 args: Two temporal nodes, instants or intervals. 

726 

727 Returns: 

728 A condition that is true if both ranges start together and the first ends earlier. 

729 """ 

730 a, b = self.temporal_pair(args) 

731 return sa.and_( 

732 sa.func.lower(a) == sa.func.lower(b), 

733 sa.func.upper(a) < sa.func.upper(b), 

734 ) 

735 

736 def func_t_startedby(self, args): 

737 """Build the ``T_STARTEDBY`` temporal predicate. 

738 

739 Args: 

740 args: Two temporal nodes, instants or intervals. 

741 

742 Returns: 

743 A condition that is true if both ranges start together and the first ends later. 

744 """ 

745 a, b = self.temporal_pair(args) 

746 return sa.and_( 

747 sa.func.lower(a) == sa.func.lower(b), 

748 sa.func.upper(a) > sa.func.upper(b), 

749 ) 

750 

751 def func_t_finishes(self, args): 

752 """Build the ``T_FINISHES`` temporal predicate. 

753 

754 Args: 

755 args: Two temporal nodes, instants or intervals. 

756 

757 Returns: 

758 A condition that is true if both ranges end together and the first starts later. 

759 """ 

760 a, b = self.temporal_pair(args) 

761 return sa.and_( 

762 sa.func.upper(a) == sa.func.upper(b), 

763 sa.func.lower(a) > sa.func.lower(b), 

764 ) 

765 

766 def func_t_finishedby(self, args): 

767 """Build the ``T_FINISHEDBY`` temporal predicate. 

768 

769 Args: 

770 args: Two temporal nodes, instants or intervals. 

771 

772 Returns: 

773 A condition that is true if both ranges end together and the first starts earlier. 

774 """ 

775 a, b = self.temporal_pair(args) 

776 return sa.and_( 

777 sa.func.upper(a) == sa.func.upper(b), 

778 sa.func.lower(a) < sa.func.lower(b), 

779 ) 

780 

781 def func_t_intersects(self, args): 

782 """Build the ``T_INTERSECTS`` temporal predicate. 

783 

784 Args: 

785 args: Two temporal nodes, instants or intervals. 

786 

787 Returns: 

788 A condition that is true if the ranges have a common point. 

789 """ 

790 a, b = self.temporal_pair(args) 

791 return a.op('&&')(b) 

792 

793 def func_t_disjoint(self, args): 

794 """Build the ``T_DISJOINT`` temporal predicate. 

795 

796 Args: 

797 args: Two temporal nodes, instants or intervals. 

798 

799 Returns: 

800 A condition that is true if the ranges have no common point. 

801 """ 

802 a, b = self.temporal_pair(args) 

803 return sa.not_(a.op('&&')(b)) 

804 

805 ## 

806 

807 def func_a_equals(self, args): 

808 """Build the ``A_EQUALS`` array predicate. 

809 

810 Args: 

811 args: Two array nodes. 

812 

813 Returns: 

814 A condition that is true if both arrays contain the same elements, ignoring order and duplicates. 

815 """ 

816 a, b = self.array_pair(args) 

817 return sa.and_(a.op('@>')(b), a.op('<@')(b)) 

818 

819 def func_a_contains(self, args): 

820 """Build the ``A_CONTAINS`` array predicate. 

821 

822 Args: 

823 args: Two array nodes. 

824 

825 Returns: 

826 A condition that is true if the first array contains all elements of the second. 

827 """ 

828 a, b = self.array_pair(args) 

829 return a.op('@>')(b) 

830 

831 def func_a_containedby(self, args): 

832 """Build the ``A_CONTAINEDBY`` array predicate. 

833 

834 Args: 

835 args: Two array nodes. 

836 

837 Returns: 

838 A condition that is true if all elements of the first array are in the second. 

839 """ 

840 a, b = self.array_pair(args) 

841 return a.op('<@')(b) 

842 

843 def func_a_overlaps(self, args): 

844 """Build the ``A_OVERLAPS`` array predicate. 

845 

846 Args: 

847 args: Two array nodes. 

848 

849 Returns: 

850 A condition that is true if the arrays have a common element. 

851 """ 

852 a, b = self.array_pair(args) 

853 return a.op('&&')(b) 

854 

855 ## 

856 

857 def temporal_pair(self, args): 

858 """Coerce both operands of a temporal predicate to ranges. 

859 

860 Args: 

861 args: Two temporal nodes. 

862 

863 Returns: 

864 A tuple of two ``tstzrange`` expressions. 

865 """ 

866 

867 return self.temporal_range(args[0]), self.temporal_range(args[1]) 

868 

869 def temporal_range(self, e): 

870 """Coerce a temporal expression to a ``tstzrange``, an instant becomes a degenerate range. 

871 

872 A null bound is unbounded in postgres, therefore null inputs must yield a null range, 

873 otherwise a null column would match everything. 

874 

875 Args: 

876 e: An ``INTERVAL`` function node, or a node that builds a timestamp. 

877 

878 Returns: 

879 A ``tstzrange`` expression with inclusive bounds, null if a bound is null. 

880 """ 

881 

882 if e[0] == Node.FUNCTION and e[1] == 'interval': 

883 lo = self.temporal_bound(e[2], '-infinity') 

884 hi = self.temporal_bound(e[3], 'infinity') 

885 else: 

886 lo = hi = self.timestamp_value(e) 

887 

888 return sa.case( 

889 (sa.or_(lo.is_(None), hi.is_(None)), sa.null()), 

890 else_=sa.func.tstzrange(lo, hi, '[]'), 

891 ) 

892 

893 def temporal_bound(self, e, unbounded): 

894 """Build an interval bound. 

895 

896 Args: 

897 e: Bound node. The string ``'..'`` means an open bound. 

898 unbounded: Value for an open bound, ``-infinity`` or ``infinity``. 

899 

900 Returns: 

901 A ``timestamptz`` expression. 

902 """ 

903 

904 if e[0] == Node.STRING and e[1] == '..': 

905 return sa.cast(sa.literal(unbounded), sa.TIMESTAMP(timezone=True)) 

906 return self.timestamp_value(e) 

907 

908 def timestamp_value(self, e): 

909 """Coerce an expression to a ``timestamptz``, naive values are assumed to be UTC. 

910 

911 Args: 

912 e: Node. 

913 

914 Returns: 

915 A ``timestamptz`` expression. 

916 """ 

917 

918 x = self.build(e) 

919 typ = getattr(x, 'type', None) 

920 if isinstance(typ, sa.TIMESTAMP) and typ.timezone: 

921 return x 

922 return sa.cast(sa.func.timezone('UTC', sa.cast(x, sa.TIMESTAMP())), sa.TIMESTAMP(timezone=True)) 

923 

924 ## 

925 

926 def literal(self, val): 

927 """Wrap a python value as a bound parameter. 

928 

929 Args: 

930 val: Value. 

931 

932 Returns: 

933 A literal expression. 

934 """ 

935 

936 return sa.literal(val) 

937 

938 ## 

939 

940 def array_pair(self, args): 

941 """Coerce both operands of an array predicate to arrays. 

942 

943 Args: 

944 args: Two array nodes. 

945 

946 Returns: 

947 A tuple of two array expressions. 

948 """ 

949 

950 return self.array_operand(args[0]), self.array_operand(args[1]) 

951 

952 def array_operand(self, e): 

953 """Build an array expression, an array literal becoming a typed parameter. 

954 

955 Args: 

956 e: Array literal or another node. 

957 

958 Returns: 

959 An array expression. 

960 """ 

961 

962 if e[0] != Node.ARRAY: 

963 return self.build(e) 

964 vals = self.value(e)