octofhir_sof/sql_generator/
mod.rs

1//! SQL generation from ViewDefinitions.
2//!
3//! Converts a [`ViewDefinition`] into a PostgreSQL query over FHIR resources
4//! stored as JSONB. FHIRPath selectors, `where` filters and `constant`
5//! references are parsed with the real `octofhir-fhirpath` parser and the AST is
6//! lowered to SQL with a collection-aware model: every FHIRPath sub-expression
7//! evaluates to a JSONB *array* (a FHIRPath collection), so a singleton and a
8//! one-element collection behave identically and array navigation flattens the
9//! way the spec requires.
10
11use octofhir_fhirpath::parse_ast;
12
13use crate::Error;
14use crate::column::ColumnType;
15use crate::view_definition::ViewDefinition;
16
17mod boundary;
18mod constants;
19mod ddl;
20mod dialect;
21mod lower;
22
23pub use ddl::{Dialect, create_table};
24
25use lower::Lower;
26// Re-exported for the in-memory evaluator (crate::eval), which shares the
27// constant-handling logic.
28pub(crate) use constants::{build_constants, substitute_constants};
29
30/// Generates SQL queries from ViewDefinitions.
31pub struct SqlGenerator {
32    /// Alias used for the base table (e.g. `base` in `FROM patient base`).
33    table_pattern: String,
34    /// Optional row-status predicate template; `{base}` is replaced with the
35    /// base alias. `None` disables row filtering entirely.
36    row_filter: Option<String>,
37    /// SQL dialect for the generated SELECT query.
38    dialect: Dialect,
39}
40
41impl Default for SqlGenerator {
42    fn default() -> Self {
43        Self::new()
44    }
45}
46
47impl SqlGenerator {
48    /// Create a new SQL generator with default settings.
49    pub fn new() -> Self {
50        Self {
51            table_pattern: "base".to_string(),
52            row_filter: Some("{base}.status <> 'deleted'".to_string()),
53            dialect: Dialect::Postgres,
54        }
55    }
56
57    /// Create a new SQL generator with a custom base-table alias.
58    pub fn with_table_pattern(table_pattern: impl Into<String>) -> Self {
59        Self {
60            table_pattern: table_pattern.into(),
61            ..Self::new()
62        }
63    }
64
65    /// Select the SQL dialect for the generated SELECT query. Defaults to
66    /// [`Dialect::Postgres`]. `Dialect::Ansi` is treated as `Postgres`.
67    pub fn with_dialect(mut self, dialect: Dialect) -> Self {
68        self.dialect = dialect;
69        self
70    }
71
72    /// Set the row-status predicate. `{base}` is replaced with the base alias.
73    /// Pass `None` to emit no row filter (useful for plain tables that have no
74    /// `status` column).
75    pub fn with_row_filter(mut self, filter: Option<String>) -> Self {
76        self.row_filter = filter;
77        self
78    }
79
80    /// Generate SQL from a ViewDefinition.
81    ///
82    /// # Errors
83    ///
84    /// Returns an error if a FHIRPath selector cannot be parsed or lowered, if a
85    /// referenced constant is undefined, or if the view's column shape is
86    /// inconsistent across `unionAll` branches.
87    pub fn generate(&self, view: &ViewDefinition) -> Result<GeneratedSql, Error> {
88        if view.resource.trim().is_empty() {
89            return Err(Error::InvalidViewDefinition(
90                "ViewDefinition is missing the required `resource`".to_string(),
91            ));
92        }
93
94        let constants = build_constants(view)?;
95        let table = view.resource.to_lowercase();
96        let ctx0 = format!("{}.resource", self.table_pattern);
97        let lower = Lower::new(view.resource.clone(), constants, ctx0.clone(), self.dialect);
98
99        // Top-level selects cross-join, exactly like nested selects.
100        let mut plans = vec![Plan::empty()];
101        for select in &view.select {
102            let mut next = Vec::new();
103            for p in &plans {
104                for child in lower.build_select(select, &p.joins, &ctx0)? {
105                    next.push(p.cross(&child));
106                }
107            }
108            plans = next;
109        }
110
111        if plans.is_empty() || plans.iter().all(|p| p.columns.is_empty()) {
112            return Err(Error::InvalidViewDefinition(
113                "ViewDefinition produces no columns".to_string(),
114            ));
115        }
116
117        // Every UNION ALL branch must expose the same ordered column shape.
118        let shape: Vec<&str> = plans[0].columns.iter().map(|c| c.name.as_str()).collect();
119        for p in &plans[1..] {
120            let other: Vec<&str> = p.columns.iter().map(|c| c.name.as_str()).collect();
121            if other != shape {
122                return Err(Error::InvalidViewDefinition(
123                    "unionAll branches have mismatched column shape".to_string(),
124                ));
125            }
126        }
127
128        // Lower top-level `where` filters to booleans over the base resource.
129        let mut where_sql = Vec::new();
130        for clause in &view.where_ {
131            let path = lower.substitute(&clause.path)?;
132            let ast = parse_ast(&path).map_err(|e| Error::FhirPath(e.to_string()))?;
133            where_sql.push(lower.bool(&ast, &ctx0)?);
134        }
135
136        let select_sqls: Vec<String> = plans
137            .iter()
138            .map(|p| self.render_plan(&table, p, &where_sql))
139            .collect();
140        let sql = select_sqls.join(" UNION ALL ");
141
142        let columns = plans[0]
143            .columns
144            .iter()
145            .map(|c| GeneratedColumn {
146                name: c.name.clone(),
147                expression: c.expr.clone(),
148                alias: c.name.clone(),
149                col_type: c.col_type,
150            })
151            .collect();
152
153        Ok(GeneratedSql {
154            sql,
155            columns,
156            ctes: Vec::new(),
157        })
158    }
159
160    fn render_plan(&self, table: &str, plan: &Plan, where_sql: &[String]) -> String {
161        let cols: Vec<String> = plan
162            .columns
163            .iter()
164            .map(|c| format!("{} AS \"{}\"", c.expr, c.name.replace('"', "\"\"")))
165            .collect();
166        let mut sql = format!(
167            "SELECT {} FROM {} {}",
168            cols.join(", "),
169            table,
170            self.table_pattern
171        );
172        for j in &plan.joins {
173            sql.push(' ');
174            sql.push_str(j);
175        }
176        let mut conds = Vec::new();
177        if let Some(f) = &self.row_filter {
178            conds.push(f.replace("{base}", &self.table_pattern));
179        }
180        for w in where_sql {
181            conds.push(format!("({w})"));
182        }
183        if !conds.is_empty() {
184            sql.push_str(" WHERE ");
185            sql.push_str(&conds.join(" AND "));
186        }
187        sql
188    }
189}
190
191/// A column produced by a plan, with the SQL expression that yields it.
192#[derive(Debug, Clone)]
193struct PlanColumn {
194    name: String,
195    expr: String,
196    col_type: ColumnType,
197}
198
199/// One UNION ALL branch: a chain of lateral joins plus its output columns.
200#[derive(Debug, Clone)]
201struct Plan {
202    joins: Vec<String>,
203    columns: Vec<PlanColumn>,
204}
205
206impl Plan {
207    fn empty() -> Self {
208        Self {
209            joins: Vec::new(),
210            columns: Vec::new(),
211        }
212    }
213
214    /// Cross join two plans: the child's joins already include this plan's joins
215    /// as a prefix, so its join list wins; columns concatenate.
216    fn cross(&self, child: &Plan) -> Plan {
217        let mut columns = self.columns.clone();
218        columns.extend(child.columns.iter().cloned());
219        Plan {
220            joins: child.joins.clone(),
221            columns,
222        }
223    }
224}
225
226/// Generated SQL with column metadata.
227#[derive(Debug, Clone)]
228pub struct GeneratedSql {
229    /// The generated SQL query.
230    pub sql: String,
231
232    /// Column information for the result set.
233    pub columns: Vec<GeneratedColumn>,
234
235    /// Common Table Expressions (CTEs) to prepend to the query.
236    pub ctes: Vec<String>,
237}
238
239/// A generated column with its SQL expression and metadata.
240#[derive(Debug, Clone)]
241pub struct GeneratedColumn {
242    /// Original column name from the ViewDefinition.
243    pub name: String,
244
245    /// SQL expression that produces this column's value.
246    pub expression: String,
247
248    /// Alias used in the SQL SELECT clause.
249    pub alias: String,
250
251    /// Data type of the column.
252    pub col_type: ColumnType,
253}
254
255#[cfg(test)]
256mod tests {
257    use super::*;
258    use serde_json::json;
259
260    fn build_sql(view: serde_json::Value) -> GeneratedSql {
261        let v = ViewDefinition::from_json(&view).unwrap();
262        SqlGenerator::new().generate(&v).unwrap()
263    }
264
265    #[test]
266    fn simple_columns() {
267        let g = build_sql(json!({
268            "resource": "Patient",
269            "select": [{ "column": [
270                { "name": "id", "path": "id", "type": "id" },
271                { "name": "gender", "path": "gender", "type": "code" }
272            ] }]
273        }));
274        assert!(g.sql.contains("FROM patient base"));
275        assert_eq!(g.columns.len(), 2);
276        assert_eq!(g.columns[0].name, "id");
277    }
278
279    #[test]
280    fn collection_column_is_json() {
281        let g = build_sql(json!({
282            "resource": "Patient",
283            "select": [{ "column": [
284                { "name": "fam", "path": "name.family", "type": "string", "collection": true }
285            ] }]
286        }));
287        assert_eq!(g.columns[0].col_type, ColumnType::Json);
288    }
289
290    #[test]
291    fn union_shape_mismatch_errors() {
292        let v = ViewDefinition::from_json(&json!({
293            "resource": "Patient",
294            "select": [{ "unionAll": [
295                { "column": [{ "name": "a", "path": "id" }, { "name": "b", "path": "id" }] },
296                { "column": [{ "name": "a", "path": "id" }, { "name": "c", "path": "id" }] }
297            ] }]
298        }))
299        .unwrap();
300        assert!(SqlGenerator::new().generate(&v).is_err());
301    }
302
303    #[test]
304    fn undefined_constant_errors() {
305        let v = ViewDefinition::from_json(&json!({
306            "resource": "Patient",
307            "select": [{ "forEach": "name.where(use = %missing)",
308                "column": [{ "name": "f", "path": "family" }] }]
309        }))
310        .unwrap();
311        assert!(SqlGenerator::new().generate(&v).is_err());
312    }
313
314    #[test]
315    fn missing_resource_errors() {
316        let v = ViewDefinition::from_json(&json!({
317            "resource": "",
318            "select": [{ "column": [{ "name": "id", "path": "id" }] }]
319        }))
320        .unwrap();
321        assert!(SqlGenerator::new().generate(&v).is_err());
322    }
323}
octofhir_sof/sql_generator/mod.rs

octofhir_sof/sql_generator/
mod.rs
