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

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

2 

3`Builder` only dispatches on node types, `SqlBuilder` generates SQLAlchemy 

4expressions for postgis. See the package documentation for details. 

5""" 

6 

7from typing import Any, cast 

8 

9import operator 

10 

11import gws 

12import gws.lib.crs 

13import gws.lib.datetimex as dtx 

14import gws.lib.sa as sa 

15 

16from .parser import Node, C 

17 

18 

19class BuildError(Exception): 

20 pass 

21 

22 

23class Builder: 

24 def get_method(self, name): 

25 """Return a handler method, or `None` if the subclass doesn't implement it.""" 

26 

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

28 

29 def build(self, e): 

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

31 

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

33 if fn: 

34 return fn(e[1:]) 

35 

36 if e[0] in C.OPERATORS: 

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

38 

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

40 

41 def build_operator(self, op, args): 

42 """Build a binary operator expression.""" 

43 

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

45 

46 def build_function(self, args): 

47 """Build a standard function call, dispatching on the function name.""" 

48 

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

50 

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

52 if fn: 

53 return fn(args[1:]) 

54 

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

56 

57 def build_user_function(self, args): 

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

59 

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

61 

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

63 

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

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

66 

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

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

69 if e[0] in C.LITERALS: 

70 return e[1] 

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

72 

73 

74class SqlBuilder(Builder): 

75 _binary_ops = { 

76 '>': operator.gt, 

77 '<': operator.lt, 

78 '>=': operator.ge, 

79 '<=': operator.le, 

80 '=': operator.eq, 

81 '!=': operator.ne, 

82 '<>': operator.ne, 

83 '*': operator.mul, 

84 '/': operator.truediv, 

85 '+': operator.add, 

86 '-': operator.sub, 

87 '%': operator.mod, 

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

89 '^': sa.func.power, 

90 } 

91 

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

93 self.table = table 

94 

95 def build_operator(self, op, args): 

96 fn = self._binary_ops.get(op) 

97 if fn: 

98 a, b = args 

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

100 

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

102 

103 def build_name(self, args): 

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

105 if col is None: 

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

107 return col 

108 

109 def build_array(self, args): 

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

111 

112 def build_bool(self, args): 

113 return self.literal(args[0]) 

114 

115 def build_float(self, args): 

116 return self.literal(args[0]) 

117 

118 def build_int(self, args): 

119 return self.literal(args[0]) 

120 

121 def build_string(self, args): 

122 return self.literal(args[0]) 

123 

124 def build_date(self, args): 

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

126 

127 def build_timestamp(self, args): 

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

129 

130 def build_wkt(self, args): 

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

132 

133 def build_bbox(self, args): 

134 minx, miny, maxx, maxy = args 

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

136 

137 ## 

138 

139 def build_and(self, args): 

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

141 

142 def build_or(self, args): 

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

144 

145 def build_not(self, args): 

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

147 

148 def build_between(self, args): 

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

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

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

152 return col.between(a, b) 

153 

154 def build_not_between(self, args): 

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

156 

157 def build_in(self, args): 

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

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

160 return col.in_(ls) 

161 

162 def build_not_in(self, args): 

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

164 

165 def build_like(self, args): 

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

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

168 

169 def build_not_like(self, args): 

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

171 

172 def build_is_null(self, args): 

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

174 return col.is_(None) 

175 

176 def build_not_null(self, args): 

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

178 return col.isnot(None) 

179 

180 ## 

181 

182 def func_s_intersects(self, args): 

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

184 

185 def func_s_contains(self, args): 

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

187 

188 def func_s_crosses(self, args): 

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

190 

191 def func_s_disjoint(self, args): 

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

193 

194 def func_s_equals(self, args): 

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

196 

197 def func_s_overlaps(self, args): 

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

199 

200 def func_s_touches(self, args): 

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

202 

203 def func_s_within(self, args): 

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

205 

206 ## 

207 

208 def func_casei(self, args): 

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

210 

211 def func_accenti(self, args): 

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

213 

214 ## 

215 

216 def func_bbox(self, args): 

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

218 

219 def func_timestamp(self, args): 

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

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

222 

223 def func_date(self, args): 

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

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

226 

227 def func_interval(self, args): 

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

229 

230 ## 

231 

232 def func_t_equals(self, args): 

233 a, b = self.temporal_pair(args) 

234 return a == b 

235 

236 def func_t_after(self, args): 

237 a, b = self.temporal_pair(args) 

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

239 

240 def func_t_before(self, args): 

241 a, b = self.temporal_pair(args) 

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

243 

244 def func_t_meets(self, args): 

245 a, b = self.temporal_pair(args) 

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

247 

248 def func_t_metby(self, args): 

249 a, b = self.temporal_pair(args) 

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

251 

252 def func_t_during(self, args): 

253 a, b = self.temporal_pair(args) 

254 return sa.and_( 

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

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

257 ) 

258 

259 def func_t_contains(self, args): 

260 a, b = self.temporal_pair(args) 

261 return sa.and_( 

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

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

264 ) 

265 

266 def func_t_overlaps(self, args): 

267 a, b = self.temporal_pair(args) 

268 return sa.and_( 

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

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

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

272 ) 

273 

274 def func_t_overlappedby(self, args): 

275 a, b = self.temporal_pair(args) 

276 return sa.and_( 

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

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

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

280 ) 

281 

282 def func_t_starts(self, args): 

283 a, b = self.temporal_pair(args) 

284 return sa.and_( 

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

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

287 ) 

288 

289 def func_t_startedby(self, args): 

290 a, b = self.temporal_pair(args) 

291 return sa.and_( 

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

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

294 ) 

295 

296 def func_t_finishes(self, args): 

297 a, b = self.temporal_pair(args) 

298 return sa.and_( 

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

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

301 ) 

302 

303 def func_t_finishedby(self, args): 

304 a, b = self.temporal_pair(args) 

305 return sa.and_( 

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

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

308 ) 

309 

310 def func_t_intersects(self, args): 

311 a, b = self.temporal_pair(args) 

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

313 

314 def func_t_disjoint(self, args): 

315 a, b = self.temporal_pair(args) 

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

317 

318 ## 

319 

320 def func_a_equals(self, args): 

321 a, b = self.array_pair(args) 

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

323 

324 def func_a_contains(self, args): 

325 a, b = self.array_pair(args) 

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

327 

328 def func_a_containedby(self, args): 

329 a, b = self.array_pair(args) 

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

331 

332 def func_a_overlaps(self, args): 

333 a, b = self.array_pair(args) 

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

335 

336 ## 

337 

338 def temporal_pair(self, args): 

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

340 

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

342 

343 def temporal_range(self, e): 

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

345 

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

347 otherwise a null column would match everything. 

348 """ 

349 

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

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

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

353 else: 

354 lo = hi = self.timestamp_value(e) 

355 

356 return sa.case( 

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

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

359 ) 

360 

361 def temporal_bound(self, e, unbounded): 

362 """Build an interval bound, the string `'..'` meaning open.""" 

363 

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

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

366 return self.timestamp_value(e) 

367 

368 def timestamp_value(self, e): 

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

370 

371 x = self.build(e) 

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

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

374 return x 

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

376 

377 ## 

378 

379 def literal(self, val): 

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

381 

382 return sa.literal(val) 

383 

384 ## 

385 

386 def array_pair(self, args): 

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

388 

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

390 

391 def array_operand(self, e): 

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

393 

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

395 return self.build(e) 

396 vals = self.value(e) 

397 return sa.literal(vals, sa.ARRAY(self.array_element_type(vals))) 

398 

399 def array_element_type(self, vals): 

400 """Infer the element type of an array literal from its first element.""" 

401 

402 if not vals: 

403 return sa.Text() 

404 v = vals[0] 

405 if isinstance(v, bool): 

406 return sa.Boolean() 

407 if isinstance(v, int): 

408 return sa.Integer() 

409 if isinstance(v, float): 

410 return sa.Float() 

411 return sa.Text()