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_neg(self, args): 

196 """Build a negation. 

197 

198 Args: 

199 args: A list with the operand node. 

200 

201 Returns: 

202 The negated expression. 

203 """ 

204 return -self.build(args[0]) 

205 

206 def build_array(self, args): 

207 """Build an array. 

208 

209 Args: 

210 args: Element nodes. 

211 

212 Returns: 

213 A list of expressions. 

214 """ 

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

216 

217 def build_bool(self, args): 

218 """Build a boolean 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_float(self, args): 

229 """Build a float 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_int(self, args): 

240 """Build a integer 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_string(self, args): 

251 """Build a string literal. 

252 

253 Args: 

254 args: A list with the value. 

255 

256 Returns: 

257 A bound parameter. 

258 """ 

259 return self.literal(args[0]) 

260 

261 def build_date(self, args): 

262 """Build a date literal. 

263 

264 Args: 

265 args: A list with the date value. 

266 

267 Returns: 

268 A ``DATE`` expression. 

269 """ 

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

271 

272 def build_timestamp(self, args): 

273 """Build a timestamp literal. 

274 

275 Args: 

276 args: A list with the datetime value. 

277 

278 Returns: 

279 A ``timestamptz`` expression. 

280 """ 

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

282 

283 def build_wkt(self, args): 

284 """Build a geometry literal. 

285 

286 Args: 

287 args: A list with the WKT string. 

288 

289 Returns: 

290 A geometry in WGS84. 

291 """ 

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

293 

294 def build_bbox(self, args): 

295 """Build a bounding box geometry. 

296 

297 Args: 

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

299 

300 Returns: 

301 A rectangle geometry in WGS84. 

302 """ 

303 minx, miny, maxx, maxy = args 

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

305 

306 ## 

307 

308 def build_and(self, args): 

309 """Build a conjunction. 

310 

311 Args: 

312 args: Operand nodes. 

313 

314 Returns: 

315 An ``AND`` expression. 

316 """ 

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

318 

319 def build_or(self, args): 

320 """Build a disjunction. 

321 

322 Args: 

323 args: Operand nodes. 

324 

325 Returns: 

326 An ``OR`` expression. 

327 """ 

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

329 

330 def build_not(self, args): 

331 """Build a negation. 

332 

333 Args: 

334 args: A list with the operand node. 

335 

336 Returns: 

337 A ``NOT`` expression. 

338 """ 

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

340 

341 def build_between(self, args): 

342 """Build a ``BETWEEN`` predicate. 

343 

344 Args: 

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

346 

347 Returns: 

348 A ``BETWEEN`` expression. 

349 """ 

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

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

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

353 return col.between(a, b) 

354 

355 def build_not_between(self, args): 

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

357 

358 Args: 

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

360 

361 Returns: 

362 A negated ``BETWEEN`` expression. 

363 """ 

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

365 

366 def build_in(self, args): 

367 """Build an ``IN`` predicate. 

368 

369 Args: 

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

371 

372 Returns: 

373 An ``IN`` expression. 

374 """ 

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

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

377 return col.in_(ls) 

378 

379 def build_not_in(self, args): 

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

381 

382 Args: 

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

384 

385 Returns: 

386 A negated ``IN`` expression. 

387 """ 

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

389 

390 def build_like(self, args): 

391 """Build a ``LIKE`` predicate. 

392 

393 Args: 

394 args: The value and pattern nodes. 

395 

396 Returns: 

397 A ``LIKE`` expression. 

398 """ 

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

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

401 

402 def build_not_like(self, args): 

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

404 

405 Args: 

406 args: The value and pattern nodes. 

407 

408 Returns: 

409 A negated ``LIKE`` expression. 

410 """ 

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

412 

413 def build_is_null(self, args): 

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

415 

416 Args: 

417 args: A list with the value node. 

418 

419 Returns: 

420 An ``IS NULL`` expression. 

421 """ 

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

423 return col.is_(None) 

424 

425 def build_not_null(self, args): 

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

427 

428 Args: 

429 args: A list with the value node. 

430 

431 Returns: 

432 An ``IS NOT NULL`` expression. 

433 """ 

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

435 return col.isnot(None) 

436 

437 ## 

438 

439 def func_s_intersects(self, args): 

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

441 

442 Args: 

443 args: Two geometry nodes. 

444 

445 Returns: 

446 An ``ST_Intersects`` expression. 

447 """ 

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

449 

450 def func_s_contains(self, args): 

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

452 

453 Args: 

454 args: Two geometry nodes. 

455 

456 Returns: 

457 An ``ST_Contains`` expression. 

458 """ 

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

460 

461 def func_s_crosses(self, args): 

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

463 

464 Args: 

465 args: Two geometry nodes. 

466 

467 Returns: 

468 An ``ST_Crosses`` expression. 

469 """ 

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

471 

472 def func_s_disjoint(self, args): 

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

474 

475 Args: 

476 args: Two geometry nodes. 

477 

478 Returns: 

479 An ``ST_Disjoint`` expression. 

480 """ 

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

482 

483 def func_s_equals(self, args): 

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

485 

486 Args: 

487 args: Two geometry nodes. 

488 

489 Returns: 

490 An ``ST_Equals`` expression. 

491 """ 

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

493 

494 def func_s_overlaps(self, args): 

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

496 

497 Args: 

498 args: Two geometry nodes. 

499 

500 Returns: 

501 An ``ST_Overlaps`` expression. 

502 """ 

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

504 

505 def func_s_touches(self, args): 

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

507 

508 Args: 

509 args: Two geometry nodes. 

510 

511 Returns: 

512 An ``ST_Touches`` expression. 

513 """ 

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

515 

516 def func_s_within(self, args): 

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

518 

519 Args: 

520 args: Two geometry nodes. 

521 

522 Returns: 

523 An ``ST_Within`` expression. 

524 """ 

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

526 

527 ## 

528 

529 def func_casei(self, args): 

530 """Build the ``CASEI`` function. 

531 

532 Args: 

533 args: A string node. 

534 

535 Returns: 

536 The lowercased value. 

537 """ 

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

539 

540 def func_accenti(self, args): 

541 """Build the ``ACCENTI`` function. 

542 

543 Args: 

544 args: A string node. 

545 

546 Returns: 

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

548 """ 

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

550 

551 ## 

552 

553 def func_bbox(self, args): 

554 """Build the ``BBOX`` function. 

555 

556 Args: 

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

558 

559 Returns: 

560 A rectangle geometry in WGS84. 

561 

562 Raises: 

563 BuildError: If an argument is not a literal. 

564 """ 

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

566 

567 def func_timestamp(self, args): 

568 """Build the ``TIMESTAMP`` function. 

569 

570 Args: 

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

572 

573 Returns: 

574 A ``timestamptz`` expression. 

575 

576 Raises: 

577 BuildError: If the argument is not a literal. 

578 """ 

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

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

581 

582 def func_date(self, args): 

583 """Build the ``DATE`` function. 

584 

585 Args: 

586 args: An ISO date string literal node. 

587 

588 Returns: 

589 A ``DATE`` expression. 

590 

591 Raises: 

592 BuildError: If the argument is not a literal. 

593 """ 

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

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

596 

597 def func_interval(self, args): 

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

599 

600 Args: 

601 args: Interval bound nodes. 

602 

603 Raises: 

604 BuildError: Always. 

605 """ 

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

607 

608 ## 

609 

610 def func_t_equals(self, args): 

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

612 

613 Args: 

614 args: Two temporal nodes, instants or intervals. 

615 

616 Returns: 

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

618 """ 

619 a, b = self.temporal_pair(args) 

620 return a == b 

621 

622 def func_t_after(self, args): 

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

624 

625 Args: 

626 args: Two temporal nodes, instants or intervals. 

627 

628 Returns: 

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

630 """ 

631 a, b = self.temporal_pair(args) 

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

633 

634 def func_t_before(self, args): 

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

636 

637 Args: 

638 args: Two temporal nodes, instants or intervals. 

639 

640 Returns: 

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

642 """ 

643 a, b = self.temporal_pair(args) 

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

645 

646 def func_t_meets(self, args): 

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

648 

649 Args: 

650 args: Two temporal nodes, instants or intervals. 

651 

652 Returns: 

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

654 """ 

655 a, b = self.temporal_pair(args) 

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

657 

658 def func_t_metby(self, args): 

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

660 

661 Args: 

662 args: Two temporal nodes, instants or intervals. 

663 

664 Returns: 

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

666 """ 

667 a, b = self.temporal_pair(args) 

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

669 

670 def func_t_during(self, args): 

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

672 

673 Args: 

674 args: Two temporal nodes, instants or intervals. 

675 

676 Returns: 

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

678 """ 

679 a, b = self.temporal_pair(args) 

680 return sa.and_( 

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

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

683 ) 

684 

685 def func_t_contains(self, args): 

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

687 

688 Args: 

689 args: Two temporal nodes, instants or intervals. 

690 

691 Returns: 

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

693 """ 

694 a, b = self.temporal_pair(args) 

695 return sa.and_( 

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

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

698 ) 

699 

700 def func_t_overlaps(self, args): 

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

702 

703 Args: 

704 args: Two temporal nodes, instants or intervals. 

705 

706 Returns: 

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

708 """ 

709 a, b = self.temporal_pair(args) 

710 return sa.and_( 

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

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

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

714 ) 

715 

716 def func_t_overlappedby(self, args): 

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

718 

719 Args: 

720 args: Two temporal nodes, instants or intervals. 

721 

722 Returns: 

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

724 """ 

725 a, b = self.temporal_pair(args) 

726 return sa.and_( 

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

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

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

730 ) 

731 

732 def func_t_starts(self, args): 

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

734 

735 Args: 

736 args: Two temporal nodes, instants or intervals. 

737 

738 Returns: 

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

740 """ 

741 a, b = self.temporal_pair(args) 

742 return sa.and_( 

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

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

745 ) 

746 

747 def func_t_startedby(self, args): 

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

749 

750 Args: 

751 args: Two temporal nodes, instants or intervals. 

752 

753 Returns: 

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

755 """ 

756 a, b = self.temporal_pair(args) 

757 return sa.and_( 

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

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

760 ) 

761 

762 def func_t_finishes(self, args): 

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

764 

765 Args: 

766 args: Two temporal nodes, instants or intervals. 

767 

768 Returns: 

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

770 """ 

771 a, b = self.temporal_pair(args) 

772 return sa.and_( 

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

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

775 ) 

776 

777 def func_t_finishedby(self, args): 

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

779 

780 Args: 

781 args: Two temporal nodes, instants or intervals. 

782 

783 Returns: 

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

785 """ 

786 a, b = self.temporal_pair(args) 

787 return sa.and_( 

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

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

790 ) 

791 

792 def func_t_intersects(self, args): 

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

794 

795 Args: 

796 args: Two temporal nodes, instants or intervals. 

797 

798 Returns: 

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

800 """ 

801 a, b = self.temporal_pair(args) 

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

803 

804 def func_t_disjoint(self, args): 

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

806 

807 Args: 

808 args: Two temporal nodes, instants or intervals. 

809 

810 Returns: 

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

812 """ 

813 a, b = self.temporal_pair(args) 

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

815 

816 ## 

817 

818 def func_a_equals(self, args): 

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

820 

821 Args: 

822 args: Two array nodes. 

823 

824 Returns: 

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

826 """ 

827 a, b = self.array_pair(args) 

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

829 

830 def func_a_contains(self, args): 

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

832 

833 Args: 

834 args: Two array nodes. 

835 

836 Returns: 

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

838 """ 

839 a, b = self.array_pair(args) 

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

841 

842 def func_a_containedby(self, args): 

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

844 

845 Args: 

846 args: Two array nodes. 

847 

848 Returns: 

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

850 """ 

851 a, b = self.array_pair(args) 

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

853 

854 def func_a_overlaps(self, args): 

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

856 

857 Args: 

858 args: Two array nodes. 

859 

860 Returns: 

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

862 """ 

863 a, b = self.array_pair(args) 

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

865 

866 ## 

867 

868 def temporal_pair(self, args): 

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

870 

871 Args: 

872 args: Two temporal nodes. 

873 

874 Returns: 

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

876 """ 

877 

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

879 

880 def temporal_range(self, e): 

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

882 

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

884 otherwise a null column would match everything. 

885 

886 Args: 

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

888 

889 Returns: 

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

891 """ 

892 

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

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

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

896 else: 

897 lo = hi = self.timestamp_value(e) 

898 

899 return sa.case( 

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

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

902 ) 

903 

904 def temporal_bound(self, e, unbounded): 

905 """Build an interval bound. 

906 

907 Args: 

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

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

910 

911 Returns: 

912 A ``timestamptz`` expression. 

913 """ 

914 

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

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

917 return self.timestamp_value(e) 

918 

919 def timestamp_value(self, e): 

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

921 

922 Args: 

923 e: Node. 

924 

925 Returns: 

926 A ``timestamptz`` expression. 

927 """ 

928 

929 x = self.build(e) 

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

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

932 return x 

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

934 

935 ## 

936 

937 def literal(self, val): 

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

939 

940 Args: 

941 val: Value. 

942 

943 Returns: 

944 A literal expression. 

945 """ 

946 

947 return sa.literal(val) 

948 

949 ## 

950 

951 def array_pair(self, args): 

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

953 

954 Args: 

955 args: Two array nodes. 

956 

957 Returns: 

958 A tuple of two array expressions. 

959 """ 

960 

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

962 

963 def array_operand(self, e): 

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