helios_persistence/backends/postgres/search/
query_builder.rs

1//! PostgreSQL search query builder.
2//!
3//! Builds SQL queries for FHIR search operations using PostgreSQL syntax
4//! with $N parameter placeholders, ILIKE for case-insensitive matching,
5//! and native TIMESTAMPTZ comparisons.
6
7use chrono::{DateTime, Utc};
8
9use crate::types::{
10    SearchModifier, SearchParamType, SearchParameter, SearchPrefix, SearchQuery, SearchValue,
11};
12
13/// A SQL fragment with associated parameters.
14#[derive(Debug, Clone)]
15pub struct SqlFragment {
16    /// The SQL string with $N placeholders.
17    pub sql: String,
18    /// The parameter values.
19    pub params: Vec<SqlParam>,
20}
21
22/// A SQL parameter value.
23#[derive(Debug, Clone)]
24pub enum SqlParam {
25    /// Text parameter.
26    Text(String),
27    /// Floating point parameter.
28    Float(f64),
29    /// Integer parameter.
30    Integer(i64),
31    /// Boolean parameter.
32    Bool(bool),
33    /// Timestamp parameter.
34    Timestamp(DateTime<Utc>),
35    /// Null parameter.
36    Null,
37}
38
39impl SqlParam {
40    /// Creates a text parameter.
41    pub fn text(s: &str) -> Self {
42        SqlParam::Text(s.to_string())
43    }
44}
45
46impl SqlFragment {
47    /// Creates a new fragment with no parameters.
48    pub fn new(sql: impl Into<String>) -> Self {
49        Self {
50            sql: sql.into(),
51            params: Vec::new(),
52        }
53    }
54
55    /// Creates a fragment with parameters.
56    pub fn with_params(sql: impl Into<String>, params: Vec<SqlParam>) -> Self {
57        Self {
58            sql: sql.into(),
59            params,
60        }
61    }
62
63    /// Combines two fragments with AND.
64    pub fn and(self, other: SqlFragment) -> SqlFragment {
65        SqlFragment {
66            sql: format!("({}) AND ({})", self.sql, other.sql),
67            params: [self.params, other.params].concat(),
68        }
69    }
70
71    /// Combines two fragments with OR.
72    pub fn or(self, other: SqlFragment) -> SqlFragment {
73        SqlFragment {
74            sql: format!("({}) OR ({})", self.sql, other.sql),
75            params: [self.params, other.params].concat(),
76        }
77    }
78}
79
80/// PostgreSQL search query builder.
81pub struct PostgresQueryBuilder;
82
83impl PostgresQueryBuilder {
84    /// Builds a search query for finding matching resource IDs.
85    ///
86    /// Returns a SQL fragment that selects DISTINCT resource_ids from search_index
87    /// matching the given search parameters.
88    pub fn build_search_query(query: &SearchQuery, param_offset: usize) -> Option<SqlFragment> {
89        let mut conditions = Vec::new();
90        let mut current_offset = param_offset;
91
92        for param in &query.parameters {
93            if let Some(condition) = Self::build_parameter_condition(param, current_offset) {
94                current_offset += condition.params.len();
95                conditions.push(condition);
96            }
97        }
98
99        if conditions.is_empty() {
100            return None;
101        }
102
103        // AND all conditions together
104        let mut combined = conditions.remove(0);
105        for cond in conditions {
106            combined = combined.and(cond);
107        }
108
109        Some(combined)
110    }
111
112    /// Builds a condition for a single search parameter.
113    fn build_parameter_condition(
114        param: &SearchParameter,
115        param_offset: usize,
116    ) -> Option<SqlFragment> {
117        if param.values.is_empty() {
118            return None;
119        }
120
121        // Handle special parameters
122        match param.name.as_str() {
123            "_id" => return Self::build_id_condition(&param.values, param_offset),
124            "_lastUpdated" => {
125                return Self::build_last_updated_condition(&param.values, param_offset);
126            }
127            _ => {}
128        }
129
130        // Build conditions based on parameter type
131        match param.param_type {
132            SearchParamType::String => Self::build_string_condition(param, param_offset),
133            SearchParamType::Token => Self::build_token_condition(param, param_offset),
134            SearchParamType::Date => Self::build_date_condition(param, param_offset),
135            SearchParamType::Number => Self::build_number_condition(param, param_offset),
136            SearchParamType::Quantity => Self::build_quantity_condition(param, param_offset),
137            SearchParamType::Reference => Self::build_reference_condition(param, param_offset),
138            SearchParamType::Uri => Self::build_uri_condition(param, param_offset),
139            SearchParamType::Composite => None,
140            SearchParamType::Special => None,
141        }
142    }
143
144    fn build_id_condition(values: &[SearchValue], offset: usize) -> Option<SqlFragment> {
145        let mut conditions = Vec::new();
146        for (i, value) in values.iter().enumerate() {
147            let param_num = offset + i + 1;
148            conditions.push(SqlFragment::with_params(
149                format!("id = ${}", param_num),
150                vec![SqlParam::text(&value.value)],
151            ));
152        }
153        if conditions.is_empty() {
154            return None;
155        }
156        let mut combined = conditions.remove(0);
157        for cond in conditions {
158            combined = combined.or(cond);
159        }
160        Some(combined)
161    }
162
163    fn build_last_updated_condition(values: &[SearchValue], offset: usize) -> Option<SqlFragment> {
164        let mut conditions = Vec::new();
165        for (i, value) in values.iter().enumerate() {
166            let param_num = offset + i + 1;
167            let op = Self::prefix_to_operator(&value.prefix);
168            conditions.push(SqlFragment::with_params(
169                format!("last_updated {} ${}", op, param_num),
170                vec![SqlParam::text(&value.value)],
171            ));
172        }
173        if conditions.is_empty() {
174            return None;
175        }
176        let mut combined = conditions.remove(0);
177        for cond in conditions {
178            combined = combined.and(cond);
179        }
180        Some(combined)
181    }
182
183    fn build_string_condition(param: &SearchParameter, offset: usize) -> Option<SqlFragment> {
184        let modifier = param.modifier.as_ref();
185        let mut conditions = Vec::new();
186
187        for (i, value) in param.values.iter().enumerate() {
188            let param_num = offset + i + 1;
189            let condition = match modifier {
190                Some(SearchModifier::Exact) => SqlFragment::with_params(
191                    format!(
192                        "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_string = ${})",
193                        param.name, param_num
194                    ),
195                    vec![SqlParam::text(&value.value)],
196                ),
197                Some(SearchModifier::Contains) => SqlFragment::with_params(
198                    format!(
199                        "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_string ILIKE ${})",
200                        param.name, param_num
201                    ),
202                    vec![SqlParam::text(&format!("%{}%", value.value))],
203                ),
204                _ => {
205                    // Default: starts-with (case-insensitive)
206                    SqlFragment::with_params(
207                        format!(
208                            "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_string ILIKE ${})",
209                            param.name, param_num
210                        ),
211                        vec![SqlParam::text(&format!("{}%", value.value))],
212                    )
213                }
214            };
215            conditions.push(condition);
216        }
217
218        if conditions.is_empty() {
219            return None;
220        }
221        let mut combined = conditions.remove(0);
222        for cond in conditions {
223            combined = combined.or(cond);
224        }
225        Some(combined)
226    }
227
228    fn build_token_condition(param: &SearchParameter, offset: usize) -> Option<SqlFragment> {
229        let mut conditions = Vec::new();
230
231        for (i, value) in param.values.iter().enumerate() {
232            let base_offset = offset + i * 2;
233            let condition = if let Some((system, code)) = value.value.split_once('|') {
234                if system.is_empty() {
235                    // |code - match any system
236                    SqlFragment::with_params(
237                        format!(
238                            "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_token_code = ${})",
239                            param.name,
240                            base_offset + 1
241                        ),
242                        vec![SqlParam::text(code)],
243                    )
244                } else if code.is_empty() {
245                    // system| - match any code in system
246                    SqlFragment::with_params(
247                        format!(
248                            "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_token_system = ${})",
249                            param.name,
250                            base_offset + 1
251                        ),
252                        vec![SqlParam::text(system)],
253                    )
254                } else {
255                    // system|code - exact match
256                    SqlFragment::with_params(
257                        format!(
258                            "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_token_system = ${} AND value_token_code = ${})",
259                            param.name,
260                            base_offset + 1,
261                            base_offset + 2
262                        ),
263                        vec![SqlParam::text(system), SqlParam::text(code)],
264                    )
265                }
266            } else {
267                // code only - match any system
268                SqlFragment::with_params(
269                    format!(
270                        "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_token_code = ${})",
271                        param.name,
272                        base_offset + 1
273                    ),
274                    vec![SqlParam::text(&value.value)],
275                )
276            };
277            conditions.push(condition);
278        }
279
280        if conditions.is_empty() {
281            return None;
282        }
283        let mut combined = conditions.remove(0);
284        for cond in conditions {
285            combined = combined.or(cond);
286        }
287        Some(combined)
288    }
289
290    fn build_date_condition(param: &SearchParameter, offset: usize) -> Option<SqlFragment> {
291        let mut conditions = Vec::new();
292
293        for (i, value) in param.values.iter().enumerate() {
294            let param_num = offset + i + 1;
295            let op = Self::prefix_to_operator(&value.prefix);
296            let timestamp = Self::parse_date_value(&value.value);
297            conditions.push(SqlFragment::with_params(
298                format!(
299                    "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_date {} ${})",
300                    param.name, op, param_num
301                ),
302                vec![SqlParam::Timestamp(timestamp)],
303            ));
304        }
305
306        if conditions.is_empty() {
307            return None;
308        }
309        let mut combined = conditions.remove(0);
310        for cond in conditions {
311            combined = combined.and(cond);
312        }
313        Some(combined)
314    }
315
316    fn build_number_condition(param: &SearchParameter, offset: usize) -> Option<SqlFragment> {
317        let mut conditions = Vec::new();
318
319        for (i, value) in param.values.iter().enumerate() {
320            let param_num = offset + i + 1;
321            let op = Self::prefix_to_operator(&value.prefix);
322            if let Ok(num) = value.value.parse::<f64>() {
323                conditions.push(SqlFragment::with_params(
324                    format!(
325                        "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_number {} ${})",
326                        param.name, op, param_num
327                    ),
328                    vec![SqlParam::Float(num)],
329                ));
330            }
331        }
332
333        if conditions.is_empty() {
334            return None;
335        }
336        let mut combined = conditions.remove(0);
337        for cond in conditions {
338            combined = combined.and(cond);
339        }
340        Some(combined)
341    }
342
343    fn build_quantity_condition(param: &SearchParameter, offset: usize) -> Option<SqlFragment> {
344        let mut conditions = Vec::new();
345
346        for (i, value) in param.values.iter().enumerate() {
347            let base_offset = offset + i * 2;
348            // Parse quantity: [prefix]number|system|code
349            let parts: Vec<&str> = value.value.splitn(3, '|').collect();
350            if let Some(num_str) = parts.first() {
351                if let Ok(num) = num_str.parse::<f64>() {
352                    let op = Self::prefix_to_operator(&value.prefix);
353                    if parts.len() >= 3 {
354                        conditions.push(SqlFragment::with_params(
355                            format!(
356                                "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_quantity_value {} ${} AND value_quantity_unit = ${})",
357                                param.name, op, base_offset + 1, base_offset + 2
358                            ),
359                            vec![SqlParam::Float(num), SqlParam::text(parts[2])],
360                        ));
361                    } else {
362                        conditions.push(SqlFragment::with_params(
363                            format!(
364                                "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_quantity_value {} ${})",
365                                param.name, op, base_offset + 1
366                            ),
367                            vec![SqlParam::Float(num)],
368                        ));
369                    }
370                }
371            }
372        }
373
374        if conditions.is_empty() {
375            return None;
376        }
377        let mut combined = conditions.remove(0);
378        for cond in conditions {
379            combined = combined.and(cond);
380        }
381        Some(combined)
382    }
383
384    fn build_reference_condition(param: &SearchParameter, offset: usize) -> Option<SqlFragment> {
385        let mut conditions = Vec::new();
386
387        for (i, value) in param.values.iter().enumerate() {
388            let param_num = offset + i + 1;
389            conditions.push(SqlFragment::with_params(
390                format!(
391                    "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_reference = ${})",
392                    param.name, param_num
393                ),
394                vec![SqlParam::text(&value.value)],
395            ));
396        }
397
398        if conditions.is_empty() {
399            return None;
400        }
401        let mut combined = conditions.remove(0);
402        for cond in conditions {
403            combined = combined.or(cond);
404        }
405        Some(combined)
406    }
407
408    fn build_uri_condition(param: &SearchParameter, offset: usize) -> Option<SqlFragment> {
409        let modifier = param.modifier.as_ref();
410        let mut conditions = Vec::new();
411
412        for (i, value) in param.values.iter().enumerate() {
413            let param_num = offset + i + 1;
414            let condition = match modifier {
415                Some(SearchModifier::Below) => SqlFragment::with_params(
416                    format!(
417                        "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_uri LIKE ${} || '%')",
418                        param.name, param_num
419                    ),
420                    vec![SqlParam::text(&value.value)],
421                ),
422                Some(SearchModifier::Above) => SqlFragment::with_params(
423                    format!(
424                        "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND ${} LIKE value_uri || '%')",
425                        param.name, param_num
426                    ),
427                    vec![SqlParam::text(&value.value)],
428                ),
429                _ => SqlFragment::with_params(
430                    format!(
431                        "id IN (SELECT resource_id FROM search_index WHERE tenant_id = $1 AND resource_type = $2 AND param_name = '{}' AND value_uri = ${})",
432                        param.name, param_num
433                    ),
434                    vec![SqlParam::text(&value.value)],
435                ),
436            };
437            conditions.push(condition);
438        }
439
440        if conditions.is_empty() {
441            return None;
442        }
443        let mut combined = conditions.remove(0);
444        for cond in conditions {
445            combined = combined.or(cond);
446        }
447        Some(combined)
448    }
449
450    /// Converts a FHIR search prefix to a SQL comparison operator.
451    fn prefix_to_operator(prefix: &SearchPrefix) -> &'static str {
452        match prefix {
453            SearchPrefix::Eq => "=",
454            SearchPrefix::Ne => "!=",
455            SearchPrefix::Gt => ">",
456            SearchPrefix::Lt => "<",
457            SearchPrefix::Ge => ">=",
458            SearchPrefix::Le => "<=",
459            SearchPrefix::Sa => ">", // starts after
460            SearchPrefix::Eb => "<", // ends before
461            SearchPrefix::Ap => "=", // approximately (simplified)
462        }
463    }
464
465    /// Parses a FHIR date search value into a `DateTime<Utc>`.
466    ///
467    /// Handles partial dates (year, year-month, date) and full date-times.
468    fn parse_date_value(value: &str) -> DateTime<Utc> {
469        let normalized = if value.contains('T') {
470            if value.contains('+') || value.contains('Z') || value.ends_with("-00:00") {
471                value.to_string()
472            } else {
473                format!("{}+00:00", value)
474            }
475        } else if value.len() == 10 {
476            format!("{}T00:00:00+00:00", value)
477        } else if value.len() == 7 {
478            format!("{}-01T00:00:00+00:00", value)
479        } else if value.len() == 4 {
480            format!("{}-01-01T00:00:00+00:00", value)
481        } else {
482            value.to_string()
483        };
484
485        DateTime::parse_from_rfc3339(&normalized)
486            .map(|dt| dt.with_timezone(&Utc))
487            .or_else(|_| normalized.parse::<DateTime<Utc>>())
488            .unwrap_or_else(|_| Utc::now())
489    }
490}
helios_persistence/backends/postgres/search/query_builder.rs

helios_persistence/backends/postgres/search/
query_builder.rs
