Line data Source code
1 : // SPDX-License-Identifier: EUPL-1.2
2 : //! Temporal Query Language (CIM 009 clause 4.11) compiled to a
3 : //! per-instance SQL predicate over a jsonb instance object.
4 : //!
5 : //! Exactness by construction: `TemporalQ::instance_matches` (antares-api)
6 : //! compares CANONICAL keys — the trailing `Z` dropped and the 4.6.3 seconds
7 : //! fraction (`.` or `,`) zero-padded — so that equal instants written in
8 : //! different fraction forms hit the bounds exactly. The SQL builds the same
9 : //! key with `dt_key_sql` and compares it with `COLLATE "C"` (byte order,
10 : //! locale-proof) instead of casting to timestamptz (which would re-order
11 : //! mixed-offset forms and can raise on malformed values). The member must be
12 : //! string-typed, exactly as `Value::as_str` demands.
13 : //!
14 : //! The store PRUNES on this predicate, so it may never be stricter than the
15 : //! arbiter: a raw byte compare made `…00.000Z` sort after `…00Z` ('.' is
16 : //! 0x2E, 'Z' is 0x5A) and silently dropped instances 4.11 requires to be
17 : //! returned.
18 :
19 : /// The 4.11 window, as the API layer parsed it. `timerel` ∈
20 : /// before|after|between|any ("any" = bare timeproperty: presence filter).
21 : pub use antares_store::filter::InstanceRange;
22 :
23 : // The compiled range is the shared fragment shape; its bind 0 is always the
24 : // timeproperty name.
25 : use antares_ql::sql::CompiledSql;
26 :
27 : /// 4.6.3 DateTime → canonical lexicographic key, the SQL twin of the
28 : /// arbiter's `dt_key`: for a `Z`-terminated stamp of at least 19 characters,
29 : /// the `Z` is dropped and the optional seconds fraction (`.` or `,`
30 : /// separator) is zero-padded to six digits; anything else is compared as it
31 : /// stands. String order over the key is temporal order across spellings, so
32 : /// `…00Z`, `…00.000Z` and `…00,0Z` are one instant on both sides.
33 : ///
34 : /// Total by construction — no cast, so a malformed stored stamp can never
35 : /// raise; the nested `CASE` is only reached for stamps long enough to slice.
36 4125 : pub fn dt_key_sql(e: &str) -> String {
37 : // the arbiter takes the fraction only after a '.'/',' and otherwise
38 : // treats it as absent — junk between the seconds and the 'Z' is dropped
39 4125 : let frac = format!(
40 : "(CASE WHEN substr({e},20,1) IN ('.', ',') \
41 : THEN substr({e},21,length({e})-21) ELSE '' END)"
42 : );
43 : // zero-pad to six WITHOUT truncating: rpad would shorten a nanosecond
44 : // fraction the arbiter keeps in full, which turns a near-tie into a tie
45 4125 : format!(
46 : "(CASE WHEN right({e},1) = 'Z' AND length({e}) >= 20 \
47 : THEN substr({e},1,19) || '.' || {frac} || repeat('0', greatest(0, 6 - length{frac})) \
48 : ELSE {e} END) COLLATE \"C\""
49 : )
50 4125 : }
51 :
52 : /// `None` = a shape this compiler does not reproduce (unknown timerel, or
53 : /// between without an end) — the caller prunes nothing and the in-memory
54 : /// window stays the arbiter.
55 1422 : pub fn compile_instance_range(
56 1422 : r: &InstanceRange<'_>,
57 1422 : el: &'static str,
58 1422 : first_bind: usize,
59 1422 : ) -> Option<CompiledSql> {
60 1422 : let tp = format!("${first_bind}");
61 1422 : let present = format!("jsonb_typeof({el} -> {tp}) = 'string'");
62 1422 : let ts = dt_key_sql(&format!("({el} ->> {tp})"));
63 : // the bound is keyed too — keying one side only is what made the
64 : // pushdown stricter than the arbiter
65 2210 : let at = |n: usize| dt_key_sql(&format!("${n}::text"));
66 1422 : let mut binds = vec![r.timeproperty.to_owned()];
67 1422 : let sql = match r.timerel {
68 1422 : "any" => present,
69 1404 : "before" => {
70 130 : binds.push(antares_store::filter::canonical_datetime(r.time_at).into_owned());
71 130 : format!("{present} AND {ts} < {}", at(first_bind.saturating_add(1)))
72 : }
73 1274 : "after" => {
74 456 : binds.push(antares_store::filter::canonical_datetime(r.time_at).into_owned());
75 456 : format!("{present} AND {ts} >= {}", at(first_bind.saturating_add(1)))
76 : }
77 818 : "between" => {
78 814 : let end = r.end_time_at?;
79 812 : binds.push(antares_store::filter::canonical_datetime(r.time_at).into_owned());
80 812 : binds.push(antares_store::filter::canonical_datetime(end).into_owned());
81 812 : format!(
82 : "{present} AND {ts} >= {} AND {ts} < {}",
83 812 : at(first_bind.saturating_add(1)),
84 812 : at(first_bind.saturating_add(2))
85 : )
86 : }
87 4 : _ => return None,
88 : };
89 1416 : Some(CompiledSql { sql, binds })
90 1422 : }
91 :
92 : /// Widened, index-serving COLUMN bound for the 4.11 window. Returns SQL only
93 : /// — it references `$time_bind` (and `$time_bind+1` for between), the SAME
94 : /// binds the byte-exact text predicate uses, cast to timestamptz in place.
95 : ///
96 : /// A SUPERSET by construction: the parsed column and the raw stamp diverge by
97 : /// at most the two RFC 3339 offsets (±14 h each), so 48 h of slack admits
98 : /// every row the text window keeps; the extra rows it admits are dropped by
99 : /// the text predicate (or the API arbiter) right after. Purpose: the btree on
100 : /// (tenant_id, entity_id, attr_id, observed_at) can serve this range — the
101 : /// jsonb text extraction the exact predicate runs on never uses an index.
102 : /// Only timeproperties with a parsed column compile; others prune by text
103 : /// alone (`None`).
104 1331 : pub fn column_range_bound(
105 1331 : r: &InstanceRange<'_>,
106 1331 : alias: &'static str,
107 1331 : time_bind: usize,
108 1331 : ) -> Option<String> {
109 : // `deleted_at` is the one nullable column of the four (0001_init.sql):
110 : // its bound must let NULL rows through to the text predicate, which
111 : // decides membership either way. `observed_at`, `created_at` and
112 : // `modified_at` are NOT NULL, so their bounds stay bare.
113 1331 : let (col, nullable) = match r.timeproperty {
114 1331 : "observedAt" => ("observed_at", false),
115 102 : "createdAt" => ("created_at", false),
116 45 : "modifiedAt" => ("modified_at", false),
117 22 : "deletedAt" => ("deleted_at", true),
118 2 : _ => return None,
119 : };
120 1329 : let bound = match r.timerel {
121 1329 : "before" => format!("{alias}.{col} < ${time_bind}::timestamptz + interval '48 hours'"),
122 1206 : "after" => format!("{alias}.{col} >= ${time_bind}::timestamptz - interval '48 hours'"),
123 719 : "between" => {
124 699 : r.end_time_at?;
125 697 : format!(
126 : "{alias}.{col} >= ${time_bind}::timestamptz - interval '48 hours' \
127 : AND {alias}.{col} < ${}::timestamptz + interval '48 hours'",
128 697 : time_bind.saturating_add(1)
129 : )
130 : }
131 20 : _ => return None,
132 : };
133 1307 : Some(if nullable {
134 4 : format!("({alias}.{col} IS NULL OR ({bound}))")
135 : } else {
136 1303 : bound
137 : })
138 1331 : }
139 :
140 : #[cfg(test)]
141 : mod tests {
142 : use super::*;
143 :
144 32 : fn range<'a>(rel: &'a str, at: &'a str, end: Option<&'a str>) -> InstanceRange<'a> {
145 32 : InstanceRange {
146 32 : timerel: rel,
147 32 : time_at: at,
148 32 : end_time_at: end,
149 32 : timeproperty: "observedAt",
150 32 : }
151 32 : }
152 :
153 : #[test]
154 2 : fn operators_mirror_instance_matches() {
155 : // before: strict < after: >= between: [at, end)
156 2 : let c = compile_instance_range(&range("before", "2026-01-01T00:00:00Z", None), "el", 4)
157 2 : .expect("compiles");
158 2 : assert!(c.sql.contains(" < (CASE"), "sql: {}", c.sql);
159 2 : assert!(c.sql.contains("$5::text"), "sql: {}", c.sql);
160 2 : assert_eq!(c.binds, vec!["observedAt", "2026-01-01T00:00:00Z"]);
161 :
162 2 : let c = compile_instance_range(&range("after", "t0", None), "el", 1).expect("compiles");
163 2 : assert!(c.sql.contains(" >= (CASE"), "sql: {}", c.sql);
164 2 : assert!(c.sql.contains("$2::text"), "sql: {}", c.sql);
165 :
166 2 : let c = compile_instance_range(&range("between", "t0", Some("t1")), "el", 1).expect("c");
167 2 : assert!(
168 2 : c.sql.contains(" >= (CASE") && c.sql.contains(" < (CASE"),
169 : "{}",
170 : c.sql
171 : );
172 2 : assert!(c.sql.contains("$2::text") && c.sql.contains("$3::text"));
173 2 : assert_eq!(c.binds.len(), 3);
174 2 : }
175 :
176 : #[test]
177 2 : fn byte_order_and_string_type_guard() {
178 2 : let c = compile_instance_range(&range("any", "", None), "el", 1).expect("compiles");
179 : // presence = string-typed member, exactly Value::as_str
180 2 : assert_eq!(c.sql, "jsonb_typeof(el -> $1) = 'string'");
181 : // ranged forms compare bytes, never timestamptz casts
182 2 : let c = compile_instance_range(&range("after", "t0", None), "el", 1).expect("c");
183 2 : assert!(c.sql.contains("COLLATE \"C\""), "{}", c.sql);
184 2 : assert!(!c.sql.contains("timestamptz"), "{}", c.sql);
185 2 : }
186 :
187 : /// 4.11 bounds are inclusive/exclusive on the INSTANT, and 4.6.3 spells
188 : /// one instant several ways (`…00Z`, `…00.000Z`, `…00,0Z`). The store
189 : /// prunes on this predicate, so it must key BOTH operands the way the
190 : /// arbiter's `dt_key` does — keying only the stored stamp made
191 : /// `"…00.000Z" >= "…00Z"` false in bytes and dropped an instance that
192 : /// `?timerel=after&timeAt=…00Z` must return.
193 : #[test]
194 2 : fn both_operands_are_canonically_keyed() {
195 2 : let c = compile_instance_range(&range("after", "2017-12-13T14:20:00Z", None), "el", 1)
196 2 : .expect("compiles");
197 : // one key expression per operand, and the raw jsonb text is never
198 : // compared directly against the bind
199 2 : assert_eq!(c.sql.matches("repeat('0', greatest(0, 6 -").count(), 2);
200 2 : assert!(
201 2 : !c.sql.contains("(el ->> $1) COLLATE \"C\" >= $2"),
202 : "raw byte compare survived: {}",
203 : c.sql
204 : );
205 : // the fraction separator the arbiter accepts is accepted here too
206 2 : let key = dt_key_sql("x");
207 2 : assert!(key.contains("IN ('.', ',')"), "{key}");
208 : // no cast: a malformed stored stamp must not be able to raise
209 2 : assert!(!key.contains("::timestamp"), "{key}");
210 2 : }
211 :
212 : #[test]
213 2 : fn unknown_shapes_refuse() {
214 2 : assert!(compile_instance_range(&range("since", "t0", None), "el", 1).is_none());
215 2 : assert!(compile_instance_range(&range("between", "t0", None), "el", 1).is_none());
216 2 : }
217 :
218 : /// `timeproperty` and the stamps are client strings: the first is a bind,
219 : /// the stamps are binds, and the only identifiers in the statement are the
220 : /// caller's own alias and this module's fixed column table.
221 : #[test]
222 2 : fn client_text_never_reaches_the_statement() {
223 2 : let hostile = "observedAt' OR 1=1 --";
224 2 : let r = InstanceRange {
225 2 : timerel: "between",
226 2 : time_at: "2026-01-01T00:00:00Z'; DROP TABLE attr_instances; --",
227 2 : end_time_at: Some("2026-02-01T00:00:00Z"),
228 2 : timeproperty: hostile,
229 2 : };
230 2 : let c = compile_instance_range(&r, "el", 1).expect("compiles");
231 10 : for needle in ["observedAt", "DROP", "TABLE", "--", "OR 1=1"] {
232 10 : assert!(!c.sql.contains(needle), "{needle:?} leaked: {}", c.sql);
233 : }
234 2 : assert!(c
235 2 : .sql
236 2 : .starts_with("jsonb_typeof(el -> $1) = 'string' AND (CASE"));
237 : // every identifier in the statement is this module's own, and the
238 : // only slots are $1..$3
239 6 : for n in ["$1", "$2", "$3"] {
240 6 : assert!(c.sql.contains(n), "{n} missing: {}", c.sql);
241 : }
242 2 : assert!(!c.sql.contains("$4"), "overshoot: {}", c.sql);
243 2 : assert_eq!(c.binds[0], hostile);
244 : // an unknown timeproperty has no column, so no identifier is ever
245 : // derived from client text
246 2 : assert!(column_range_bound(&r, "ai", 1).is_none());
247 2 : }
248 :
249 : #[test]
250 2 : fn column_bound_reuses_binds_and_widens_outward() {
251 : // after: lower bound moves DOWN, before: upper bound moves UP —
252 : // widening must always ADMIT more than the text window, never less
253 2 : let s = column_range_bound(&range("after", "t0", None), "ai", 5).expect("bound");
254 2 : assert_eq!(s, "ai.observed_at >= $5::timestamptz - interval '48 hours'");
255 2 : let s = column_range_bound(&range("before", "t0", None), "ai", 2).expect("bound");
256 2 : assert!(s.contains("< $2::timestamptz + interval '48 hours'"), "{s}");
257 2 : let s = column_range_bound(&range("between", "t0", Some("t1")), "ai", 2).expect("bound");
258 2 : assert!(
259 2 : s.contains(">= $2::timestamptz - interval")
260 2 : && s.contains("< $3::timestamptz + interval"),
261 : "{s}"
262 : );
263 2 : }
264 :
265 : #[test]
266 2 : fn column_bound_only_for_parsed_columns_and_known_relations() {
267 2 : let created = InstanceRange {
268 2 : timeproperty: "createdAt",
269 2 : ..range("after", "t0", None)
270 2 : };
271 2 : assert!(column_range_bound(&created, "ai", 1)
272 2 : .expect("bound")
273 2 : .contains("ai.created_at"));
274 : // deleted_at is nullable and historically unfilled — its bound must
275 : // carry the IS NULL escape so old rows reach the text predicate
276 2 : let deleted = InstanceRange {
277 2 : timeproperty: "deletedAt",
278 2 : ..range("after", "t0", None)
279 2 : };
280 2 : let s = column_range_bound(&deleted, "ai", 1).expect("bound");
281 2 : assert!(s.starts_with("(ai.deleted_at IS NULL OR ("), "{s}");
282 2 : assert!(column_range_bound(&range("any", "", None), "ai", 1).is_none());
283 2 : assert!(column_range_bound(&range("since", "t0", None), "ai", 1).is_none());
284 2 : assert!(column_range_bound(&range("between", "t0", None), "ai", 1).is_none());
285 2 : }
286 : }
|
