Skip to main content

uqa_sql/semantics/
view_rewrite.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! `PostgreSQL` 18 automatically updatable view analysis and DML rewriting.
8
9use std::collections::BTreeSet;
10
11use crate::ast::{ReturningAliases, TriggerEvent, TriggerTiming};
12use crate::plan::{
13    ComputePlan, ConflictActionPlan, ConflictPlan, DeletePlan, InsertPlan, MergePlan,
14    MergeWhenPlan, ProjectionPlan, QueryPlan, RelationalPlan, SourcePlan, UpdatePlan,
15};
16use crate::SQLError;
17use crate::{RowSchema, ScalarExpr};
18
19use crate::{
20    catalog::view::{StoredViewKind, ViewRewriteDefinition as StoredView},
21    RelationIdentity,
22};
23
24use crate::binding::snapshot::BindingSnapshot as CteScope;
25pub mod context;
26use context::{stored_view_schema, ViewRewriteContext};
27
28mod bindings;
29mod captured_projection;
30pub use bindings::ViewRewriteBindings;
31mod columns;
32pub use columns::relation_columns_with_control;
33mod commands;
34mod correlation;
35pub use captured_projection::{capture_view_projection, CapturedViewProjection};
36pub use captured_projection::{capture_view_projection_owned, OwnedCapturedViewProjection};
37pub use commands::{
38    merge_view_target_path_with_control, rewrite_insert_owned, rewrite_merge_owned,
39    rewrite_rule_inputs_owned, validate_public_merge_targets_with_control,
40};
41pub use validation::production::validate_public_merge_contract as validate_public_merge_contract_with_control;
42mod deferred;
43pub(crate) use deferred::has_resolvable_outer_rewrites;
44pub use deferred::{
45    bind_outer_rewrite_scope, bind_outer_rewrite_scope_with_control,
46    resolve_outer_expression_rewrites, resolve_outer_expression_rewrites_owned,
47    resolve_outer_rewrites, resolve_outer_rewrites_owned, resolve_outer_source_rewrites,
48    resolve_outer_sources, resolve_outer_sources_owned, DeferredOuterRewrite,
49};
50mod layer_privileges;
51pub mod production;
52mod returning;
53mod rewrite_insert;
54mod rewrite_merge;
55mod rewrite_update_delete;
56pub mod rule_inputs;
57mod updatability;
58mod validation;
59
60use correlation::schema_public_columns;
61use returning::instead_of_trigger_definition;
62pub use rewrite_insert::rewrite_insert_to_base;
63pub use rewrite_merge::rewrite_merge_to_base;
64pub use rewrite_update_delete::{
65    rewrite_delete_rule_inputs, rewrite_delete_to_base, rewrite_update_rule_inputs,
66    rewrite_update_to_base,
67};
68pub use rule_inputs::{rule_input_requirements, validate_grouped_rule_inputs};
69pub use updatability::NotUpdatableReason;
70use updatability::{view_not_updatable, ColumnRestriction, ColumnWrite, ViewCommand};
71use validation::{duplicate_assignment, duplicate_insert_column, layer_column, ExpressionScope};
72pub use validation::{merge_view_target_path, MergeViewTargetPath};
73pub use validation::{validate_public_merge_contract, validate_public_merge_targets};
74
75#[derive(Debug, Clone, Copy, PartialEq, Eq)]
76pub enum ViewCheckOption {
77    None,
78    Local,
79    Cascaded,
80}
81
82impl ViewCheckOption {
83    fn from_options(options: &[(String, String)]) -> Self {
84        options
85            .iter()
86            .rev()
87            .find(|(name, _)| name == "check_option")
88            .map_or(Self::None, |(_, value)| match value.as_str() {
89                "local" => Self::Local,
90                "cascaded" => Self::Cascaded,
91                _ => Self::None,
92            })
93    }
94
95    const fn catalog_value(self) -> &'static str {
96        match self {
97            Self::None => "NONE",
98            Self::Local => "LOCAL",
99            Self::Cascaded => "CASCADED",
100        }
101    }
102}
103
104#[derive(Debug, Clone)]
105pub struct ViewColumn {
106    pub name: String,
107    pub collation_oid: Option<u32>,
108    pub expression: ScalarExpr,
109    pub writable_source_column: Option<String>,
110}
111
112#[derive(Debug, Clone)]
113pub struct AutomaticViewLayer {
114    pub canonical_name: String,
115    pub source_name: String,
116    pub source_qualifier: String,
117    pub source_column_map: Vec<(String, String)>,
118    pub source_include_descendants: bool,
119    pub source_schema: RowSchema,
120    pub columns: Vec<ViewColumn>,
121    pub predicate: Option<ScalarExpr>,
122    pub subqueries: Vec<QueryPlan>,
123    pub check_option: ViewCheckOption,
124}
125
126impl AutomaticViewLayer {
127    pub fn physical_source_column<'a>(&'a self, column: &'a str) -> &'a str {
128        self.source_column_map
129            .binary_search_by(|(name, _)| name.as_str().cmp(column))
130            .map_or(column, |index| self.source_column_map[index].1.as_str())
131    }
132
133    /// Whether a column of the view is a column of its base relation, which INSERT and UPDATE need and DELETE does not.
134    pub fn has_writable_column(&self) -> bool {
135        self.columns
136            .iter()
137            .any(|column| column.writable_source_column.is_some())
138    }
139
140    /// Why `column`, which is not a column of the base relation, cannot be written, as `view_col_is_auto_updatable` reports it.
141    fn column_restriction(&self, column: &ViewColumn) -> ColumnRestriction {
142        let name = match &column.expression {
143            ScalarExpr::Column(name) => Some(name),
144            ScalarExpr::QualifiedColumn { qualifier, column }
145                if source_qualifier_matches(
146                    qualifier,
147                    &self.source_qualifier,
148                    &self.source_name,
149                ) =>
150            {
151                Some(column)
152            }
153            _ => None,
154        };
155        if name.is_some_and(|name| {
156            crate::schema::columns::POSTGRES_SYSTEM_COLUMNS.contains(&name.as_str())
157        }) {
158            ColumnRestriction::SystemColumn
159        } else {
160            ColumnRestriction::Computed
161        }
162    }
163}
164
165/// What `view_query_is_auto_updatable` finds in a view's own query, short of its test for an updatable column, which depends on the command.
166#[derive(Debug)]
167pub enum ViewQueryShape {
168    Updatable(Box<AutomaticViewLayer>),
169    NotUpdatable(NotUpdatableReason),
170}
171
172pub use crate::catalog::view::ViewMutationCapabilities;
173
174#[derive(Debug, Clone)]
175pub struct ViewUpdatability {
176    pub automatic: ViewMutationCapabilities,
177    runtime: ViewMutationCapabilities,
178    runtime_insert_columns: Vec<bool>,
179    runtime_columns: Vec<bool>,
180    pub catalog: ViewMutationCapabilities,
181    catalog_insert_columns: Vec<bool>,
182    pub catalog_columns: Vec<bool>,
183    pub check_option: String,
184}
185
186fn display_relation(name: &str) -> String {
187    RelationIdentity::from_legacy_name(name)
188        .map_or_else(|_| name.to_string(), |relation| relation.name)
189}
190
191pub fn relation_columns(
192    services: ViewRewriteContext<'_>,
193    relation: &str,
194) -> Result<Vec<String>, SQLError> {
195    Ok(relation_columns_with_control(
196        services,
197        relation,
198        &uqa_core::memory::ProductionControl::uncontrolled(),
199    )?
200    .into_uncontrolled()
201    .expect("ordinary view source columns"))
202}
203
204fn source_qualifier_matches(qualifier: &str, source_qualifier: &str, source_name: &str) -> bool {
205    qualifier == source_qualifier
206        || qualifier == source_name
207        || RelationIdentity::borrowed_reference(source_name)
208            .is_some_and(|identity| identity.name == uqa_core::RelationName::decoded(qualifier))
209}
210
211pub fn automatic_view_layer(
212    services: ViewRewriteContext<'_>,
213    name: &str,
214) -> Result<Option<AutomaticViewLayer>, SQLError> {
215    Ok(match view_query_shape(services, name)? {
216        Some(ViewQueryShape::Updatable(layer)) => Some(*layer),
217        Some(ViewQueryShape::NotUpdatable(_)) | None => None,
218    })
219}
220
221/// The shape of the query of view `name`, or `None` when `name` names no regular view.
222pub fn view_query_shape(
223    services: ViewRewriteContext<'_>,
224    name: &str,
225) -> Result<Option<ViewQueryShape>, SQLError> {
226    let Some(canonical_name) = services
227        .catalog
228        .try_resolve_view_name(name)
229        .map_err(|error| SQLError::Internal(format!("resolve DML view `{name}`: {error}")))?
230    else {
231        return Ok(None);
232    };
233    let control = uqa_core::memory::ProductionControl::uncontrolled();
234    let mut shape = None;
235    services.catalog.with_view_definition_with_control(
236        &canonical_name,
237        &control,
238        &mut |definition| {
239            let definition = definition.ok_or_else(|| SQLError::UnknownTable(name.to_string()))?;
240            if definition.kind == StoredViewKind::View {
241                shape = Some(
242                    production::shape_from_definition(
243                        services,
244                        &canonical_name,
245                        definition,
246                        &control,
247                    )?
248                    .finish(&control)?
249                    .into_uncontrolled()
250                    .expect("ordinary view shape"),
251                );
252            }
253            Ok(())
254        },
255    )?;
256    Ok(shape)
257}
258
259fn view_query_shape_from_definition(
260    services: ViewRewriteContext<'_>,
261    canonical_name: &str,
262    definition: &StoredView,
263) -> Result<ViewQueryShape, SQLError> {
264    let control = uqa_core::memory::ProductionControl::uncontrolled();
265    Ok(
266        production::shape_from_definition(services, canonical_name, definition.into(), &control)?
267            .finish(&control)?
268            .into_uncontrolled()
269            .expect("ordinary view shape"),
270    )
271}
272
273mod layer_rewrite;
274
275pub fn has_instead_of_trigger(
276    services: ViewRewriteContext<'_>,
277    view: &str,
278    event: TriggerEvent,
279) -> Result<bool, SQLError> {
280    let Some(canonical) = services
281        .catalog
282        .try_resolve_view_name(view)
283        .map_err(|error| SQLError::Internal(format!("resolve DML view `{view}`: {error}")))?
284    else {
285        return Ok(false);
286    };
287    instead_of_trigger_definition(services, &canonical, event)
288}
289
290#[expect(
291    clippy::too_many_lines,
292    reason = "preserves view qualifier and row identity"
293)]
294fn view_updatability_inner(
295    services: ViewRewriteContext<'_>,
296    name: &str,
297    visited: &mut BTreeSet<String>,
298) -> Result<ViewUpdatability, SQLError> {
299    let definition = services
300        .catalog
301        .view_definition(name)?
302        .ok_or_else(|| SQLError::UnknownTable(name.to_string()))?;
303    let schema = stored_view_schema(services, &definition)?;
304    let width = schema.len();
305    let check_option = ViewCheckOption::from_options(&definition.options)
306        .catalog_value()
307        .to_string();
308    let mut automatic = ViewMutationCapabilities::default();
309    let mut automatic_insert_columns = vec![false; width];
310    let mut automatic_columns = vec![false; width];
311    let mut projected_catalog = ViewMutationCapabilities::default();
312    let mut projected_catalog_insert_columns = vec![false; width];
313    let mut projected_catalog_columns = vec![false; width];
314    if let Some(layer) = automatic_view_layer(services, name)? {
315        if visited.insert(layer.canonical_name.clone()) {
316            if services
317                .catalog
318                .view_definition(&layer.source_name)?
319                .is_some()
320            {
321                let source = view_updatability_inner(services, &layer.source_name, visited)?;
322                let source_columns = relation_columns(services, &layer.source_name)?;
323                let mapped = |source_capabilities: &[bool]| {
324                    layer
325                        .columns
326                        .iter()
327                        .map(|column| {
328                            column.writable_source_column.as_ref().is_some_and(|name| {
329                                source_columns
330                                    .iter()
331                                    .position(|candidate| candidate == name)
332                                    .is_some_and(|position| {
333                                        source_capabilities.get(position) == Some(&true)
334                                    })
335                            })
336                        })
337                        .collect::<Vec<_>>()
338                };
339                automatic_insert_columns = mapped(&source.runtime_insert_columns);
340                automatic_columns = mapped(&source.runtime_columns);
341                automatic = ViewMutationCapabilities {
342                    insertable: source.runtime.insertable
343                        && automatic_insert_columns.iter().any(|value| *value),
344                    updatable: source.runtime.updatable
345                        && automatic_columns.iter().any(|value| *value),
346                    deletable: source.runtime.deletable,
347                };
348                projected_catalog_insert_columns = mapped(&source.catalog_insert_columns);
349                projected_catalog_columns = mapped(&source.catalog_columns);
350                projected_catalog = ViewMutationCapabilities {
351                    insertable: source.catalog.insertable
352                        && projected_catalog_insert_columns.iter().any(|value| *value),
353                    updatable: source.catalog.updatable
354                        && projected_catalog_columns.iter().any(|value| *value),
355                    deletable: source.catalog.deletable,
356                };
357            } else {
358                automatic_insert_columns = layer
359                    .columns
360                    .iter()
361                    .map(|column| column.writable_source_column.is_some())
362                    .collect();
363                automatic_columns.clone_from(&automatic_insert_columns);
364                automatic = ViewMutationCapabilities {
365                    insertable: automatic_insert_columns.iter().any(|value| *value),
366                    updatable: automatic_columns.iter().any(|value| *value),
367                    deletable: true,
368                };
369                projected_catalog = automatic;
370                projected_catalog_insert_columns.clone_from(&automatic_insert_columns);
371                projected_catalog_columns.clone_from(&automatic_columns);
372            }
373            visited.remove(&layer.canonical_name);
374        }
375    }
376    let active_insert =
377        active_unconditional_instead_rule(services, name, crate::ast::RuleEvent::Insert)?;
378    let active_update =
379        active_unconditional_instead_rule(services, name, crate::ast::RuleEvent::Update)?;
380    let active_delete =
381        active_unconditional_instead_rule(services, name, crate::ast::RuleEvent::Delete)?;
382    let runtime = ViewMutationCapabilities {
383        insertable: automatic.insertable || active_insert,
384        updatable: automatic.updatable || active_update,
385        deletable: automatic.deletable || active_delete,
386    };
387    let runtime_insert_columns = automatic_insert_columns
388        .iter()
389        .map(|column| *column || active_insert)
390        .collect();
391    let runtime_columns = automatic_columns
392        .iter()
393        .map(|column| *column || active_update)
394        .collect();
395    let rule_insertable =
396        has_unconditional_instead_rule(services, name, crate::ast::RuleEvent::Insert)?;
397    let rule_updatable =
398        has_unconditional_instead_rule(services, name, crate::ast::RuleEvent::Update)?;
399    let rule_deletable =
400        has_unconditional_instead_rule(services, name, crate::ast::RuleEvent::Delete)?;
401    let catalog = ViewMutationCapabilities {
402        insertable: projected_catalog.insertable || rule_insertable,
403        updatable: projected_catalog.updatable || rule_updatable,
404        deletable: projected_catalog.deletable || rule_deletable,
405    };
406    let catalog_insert_columns = projected_catalog_insert_columns
407        .iter()
408        .map(|column| *column || rule_insertable)
409        .collect();
410    let catalog_columns = if catalog.fully_updatable() {
411        projected_catalog_columns
412            .iter()
413            .map(|column| *column || rule_updatable)
414            .collect()
415    } else {
416        vec![false; width]
417    };
418    Ok(ViewUpdatability {
419        automatic,
420        runtime,
421        runtime_insert_columns,
422        runtime_columns,
423        catalog,
424        catalog_insert_columns,
425        catalog_columns,
426        check_option,
427    })
428}
429
430pub fn view_updatability(
431    services: ViewRewriteContext<'_>,
432    name: &str,
433) -> Result<ViewUpdatability, SQLError> {
434    view_updatability_inner(services, name, &mut BTreeSet::new())
435}
436
437fn active_unconditional_instead_rule(
438    services: ViewRewriteContext<'_>,
439    relation: &str,
440    event: crate::ast::RuleEvent,
441) -> Result<bool, SQLError> {
442    services
443        .catalog
444        .capture_rules(relation, event)?
445        .any(|rule| rule.definition.instead && rule.definition.condition.is_none())
446}
447
448fn has_unconditional_instead_rule(
449    services: ViewRewriteContext<'_>,
450    relation: &str,
451    event: crate::ast::RuleEvent,
452) -> Result<bool, SQLError> {
453    Ok(services
454        .catalog
455        .rule_definitions_for(relation, event)?
456        .iter()
457        .any(|rule| rule.definition.instead && rule.definition.condition.is_none()))
458}
459
460pub fn validate_view_definition_check_option(
461    services: ViewRewriteContext<'_>,
462    name: &str,
463    definition: &StoredView,
464) -> Result<(), SQLError> {
465    if ViewCheckOption::from_options(&definition.options) == ViewCheckOption::None {
466        return Ok(());
467    }
468    // `DefineView` reports why the view is not automatically updatable as the hint.
469    let reason = match view_query_shape_from_definition(services, name, definition)? {
470        ViewQueryShape::Updatable(layer) if layer.has_writable_column() => return Ok(()),
471        ViewQueryShape::Updatable(_) => NotUpdatableReason::NoUpdatableColumns,
472        ViewQueryShape::NotUpdatable(reason) => reason,
473    };
474    Err(SQLError::Diagnostic {
475        sqlstate: "0A000".into(),
476        message: "WITH CHECK OPTION is supported only on automatically updatable views".into(),
477        detail: None,
478        hint: Some(reason.detail().into()),
479    })
480}