Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

16.9 KB
Raw Blame
//! Parameterized SQL for the engine's validated, single-table view plan.

use std::collections::BTreeSet;

use database_sql::{
    catalog::{Column, ColumnKind, Table, TableSource},
    resolve::{
        ComparisonOperator, Direction, Filter, OrderKey, SelectQuery, Value, row_position_key,
    },
};
use models_databases::RowId;
use sqlx::{Postgres, QueryBuilder};
use uuid::Uuid;

use crate::domain::view_rows::ViewRowsError;

type Sql = QueryBuilder<'static, Postgres>;

fn filter_columns(filter: &Filter, columns: &mut BTreeSet<Uuid>) {
    match filter {
        Filter::And(parts) | Filter::Or(parts) => {
            for part in parts {
                filter_columns(part, columns);
            }
        }
        Filter::Comparison { column, .. }
        | Filter::In { column, .. }
        | Filter::Has { column, .. }
        | Filter::IsNull { column, .. }
        | Filter::Like { column, .. } => {
            columns.insert(*column);
        }
    }
}

struct Columns<'a>(Vec<&'a Column>);

impl Columns<'_> {
    fn index(&self, id: Uuid) -> Result<usize, ViewRowsError> {
        self.0
            .iter()
            .position(|column| column.id == id)
            .ok_or(ViewRowsError::InvalidQuery)
    }

    fn kind(&self, id: Uuid) -> Result<&ColumnKind, ViewRowsError> {
        Ok(&self.0[self.index(id)?].kind)
    }

    fn reference(&self, sql: &mut Sql, id: Uuid, scalar: bool) -> Result<(), ViewRowsError> {
        let index = self.index(id)?;
        let array = matches!(
            self.0[index].kind,
            ColumnKind::Select { .. } | ColumnKind::Entity { .. }
        );
        if scalar && array {
            sql.push(format!(
                "(CASE WHEN cardinality(v{index}) = 1 THEN v{index}[1] END)"
            ));
        } else {
            sql.push(format!("v{index}"));
        }
        Ok(())
    }
}

/// Only IDs and compiler-generated aliases become SQL identifiers. Every
/// caller-supplied value, option ID and cursor is a bind parameter.
pub(super) fn page(
    table: &Table,
    query: &SelectQuery,
    after: Option<RowId>,
    limit: u16,
) -> Result<Sql, ViewRowsError> {
    if table.source != TableSource::Database
        || query.relations.len() != 1
        || query.relations[0].table != table.id
        || !query.joins.is_empty()
        || query.group_by.is_some()
        || query.distinct
        || !(1..=501).contains(&limit)
    {
        return Err(ViewRowsError::InvalidQuery);
    }
    let position = row_position_key(table.id);
    let mut needed = BTreeSet::new();
    if let Some(filter) = &query.where_ {
        filter_columns(filter, &mut needed);
    }
    for order in &query.order_by {
        let OrderKey::Column(column) = order.key else {
            return Err(ViewRowsError::InvalidQuery);
        };
        if column != position {
            needed.insert(column);
        }
    }
    let columns = Columns(
        needed
            .into_iter()
            .map(|id| {
                table
                    .columns
                    .iter()
                    // A formula's values are stored in its definition like any cell.
                    .find(|column| column.id == id)
                    .ok_or(ViewRowsError::InvalidQuery)
            })
            .collect::<Result<_, _>>()?,
    );
    let mut sql = Sql::new("WITH cells AS NOT MATERIALIZED (SELECT r.id, r.position");
    for (index, column) in columns.0.iter().enumerate() {
        sql.push(", ");
        cell(&mut sql, index, &column.kind);
        sql.push(format!(" AS v{index}, p{index}.values AS raw{index}"));
    }
    sql.push(" FROM database_rows r");
    for (index, column) in columns.0.iter().enumerate() {
        // Keep both keys indexable: filters may start from properties, whereas
        // an unfiltered ordered page starts from database_rows.
        sql.push(format!(" LEFT JOIN entity_properties p{index} ON p{index}.entity_id = r.id::text AND r.id = CASE WHEN p{index}.entity_type = 'DATABASE_ROW' THEN p{index}.entity_id::uuid END AND p{index}.entity_type = 'DATABASE_ROW' AND p{index}.property_definition_id = "))
            .push_bind(column.id);
    }
    sql.push(" WHERE r.table_id = ")
        .push_bind(table.id.into_uuid());
    sql.push("), candidates AS NOT MATERIALIZED (SELECT cells.*");
    for (index, order) in query.order_by.iter().enumerate() {
        sql.push(", ");
        let OrderKey::Column(column) = order.key else {
            return Err(ViewRowsError::InvalidQuery);
        };
        if column == position {
            sql.push("position");
        } else {
            sort_value(&mut sql, &columns, column)?;
        }
        sql.push(format!(" AS k{index}"));
    }
    sql.push(" FROM cells");
    if let Some(filter) = &query.where_ {
        sql.push(" WHERE ");
        predicate(&mut sql, &columns, filter)?;
    }
    sql.push(") SELECT c.id FROM candidates c");
    if let Some(after) = after {
        sql.push(" CROSS JOIN (SELECT * FROM candidates WHERE id = ")
            .push_bind(after.into_uuid())
            .push(") pivot WHERE ");
        if query.order_by.len() == 1
            && query.order_by[0].key == OrderKey::Column(position)
            && query.order_by[0].direction == Direction::Ascending
        {
            sql.push("(c.k0, c.id) > (pivot.k0, pivot.id)");
        } else {
            seek(&mut sql, query);
        }
    }
    sql.push(" ORDER BY ");
    for (index, order) in query.order_by.iter().enumerate() {
        sql.push(format!(
            "c.k{index} {} NULLS LAST, ",
            direction(order.direction)
        ));
    }
    sql.push("c.id ASC LIMIT ").push_bind(i64::from(limit));
    Ok(sql)
}

fn direction(direction: Direction) -> &'static str {
    match direction {
        Direction::Ascending => "ASC",
        Direction::Descending => "DESC",
    }
}

/// Lexicographic continuation with empty values last in either direction.
fn seek(sql: &mut Sql, query: &SelectQuery) {
    sql.push("(");
    for at in 0..=query.order_by.len() {
        if at > 0 {
            sql.push(" OR ");
        }
        sql.push("(");
        for prefix in 0..at {
            sql.push(format!(
                "c.k{prefix} IS NOT DISTINCT FROM pivot.k{prefix} AND "
            ));
        }
        if let Some(order) = query.order_by.get(at) {
            let operator = match order.direction {
                Direction::Ascending => ">",
                Direction::Descending => "<",
            };
            sql.push(format!(
                "(c.k{at} {operator} pivot.k{at} OR (c.k{at} IS NULL AND pivot.k{at} IS NOT NULL))"
            ));
        } else {
            sql.push("c.id > pivot.id");
        }
        sql.push(")");
    }
    sql.push(")");
}

fn cell(sql: &mut Sql, index: usize, kind: &ColumnKind) {
    let property = format!("p{index}.values");
    let (tag, value) = match kind {
        ColumnKind::Text => ("String", format!("({property}->>'value') COLLATE \"C\"")),
        ColumnKind::Number => (
            "Number",
            format!("({property}->>'value')::double precision"),
        ),
        ColumnKind::Boolean => ("Boolean", format!("({property}->>'value')::boolean")),
        ColumnKind::Date => ("Date", date_value(&format!("({property}->>'value')"))),
        // `[start, end]`: arrays compare element by element, so a sort by
        // start then end and the keyset seek need nothing more.
        ColumnKind::DateRange => (
            "DateRange",
            format!(
                "ARRAY[{}, {}]",
                date_value(&format!("({property}->'value'->>'start')")),
                date_value(&format!("({property}->'value'->>'end')"))
            ),
        ),
        ColumnKind::Link => (
            "Link",
            format!(
                "COALESCE((SELECT string_agg(value, ' ' ORDER BY ordinal) FROM jsonb_array_elements_text({property}->'value') WITH ORDINALITY AS links(value, ordinal)), '') COLLATE \"C\""
            ),
        ),
        ColumnKind::Select { .. } => (
            "SelectOption",
            format!(
                "NULLIF(ARRAY(SELECT value FROM jsonb_array_elements_text({property}->'value') WITH ORDINALITY AS options(value, ordinal) ORDER BY ordinal), '{{}}'::text[]) COLLATE \"C\""
            ),
        ),
        ColumnKind::Entity { .. } => (
            "EntityReference",
            format!(
                "NULLIF(ARRAY(SELECT value->>'entity_id' FROM jsonb_array_elements({property}->'value') WITH ORDINALITY AS refs(value, ordinal) ORDER BY ordinal), '{{}}'::text[]) COLLATE \"C\""
            ),
        ),
    };
    sql.push(format!(
        "CASE WHEN {property}->>'type' = '{tag}' THEN {value} END"
    ));
}

// Properties serialize DateTime<Utc> as RFC3339 UTC. Keep the fractional
// seconds separate: PostgreSQL timestamps round to microseconds, but the
// view engine compares every stored nanosecond.
fn date_value(text: &str) -> String {
    format!(
        "(extract(epoch FROM (split_part(rtrim({text}, 'Z'), '.', 1) || 'Z')::timestamptz) + COALESCE(('0.' || substring({text} FROM '[.]([0-9]+)'))::numeric, 0))"
    )
}

fn sort_value(sql: &mut Sql, columns: &Columns<'_>, id: Uuid) -> Result<(), ViewRowsError> {
    let index = columns.index(id)?;
    match &columns.0[index].kind {
        ColumnKind::Text | ColumnKind::Link => {
            sql.push(format!(
                "lower(v{index} COLLATE \"und-x-icu\") COLLATE \"C\""
            ));
        }
        ColumnKind::Select { options, .. } => {
            sql.push(format!(
                "CASE WHEN v{index} IS NOT NULL THEN ARRAY(SELECT COALESCE(array_position("
            ));
            sql.push_bind(
                options
                    .iter()
                    .map(|option| option.id.to_string())
                    .collect::<Vec<_>>(),
            );
            sql.push(format!("::text[], value), 2147483647) FROM unnest(v{index}) WITH ORDINALITY AS members(value, ordinal) ORDER BY ordinal) END"));
        }
        _ => {
            sql.push(format!("v{index}"));
        }
    }
    Ok(())
}

fn literal(sql: &mut Sql, value: &Value) -> Result<(), ViewRowsError> {
    match value {
        Value::Text(value) | Value::Entity(value) => {
            sql.push_bind(value.clone());
        }
        Value::Number(value) => {
            sql.push_bind(*value);
        }
        Value::Bool(value) => {
            sql.push_bind(*value);
        }
        Value::Date(value) => {
            sql.push("(")
                .push_bind(value.timestamp())
                .push("::numeric + ")
                .push_bind(i64::from(value.timestamp_subsec_nanos()))
                .push("::numeric / 1000000000)");
        }
        Value::Option(value) => {
            sql.push_bind(value.to_string());
        }
        Value::Options(_) | Value::Entities(_) | Value::DateRange(_) => {
            return Err(ViewRowsError::InvalidQuery);
        }
    }
    Ok(())
}

fn predicate(sql: &mut Sql, columns: &Columns<'_>, filter: &Filter) -> Result<(), ViewRowsError> {
    sql.push("(");
    // The typed expressions below retain scalar/list and empty-cell semantics.
    // Expose positive membership separately so the property GIN index can prune
    // candidates before PostgreSQL expands JSON arrays.
    match filter {
        Filter::Has {
            column,
            value,
            negated: false,
        } => {
            membership_index(sql, columns, *column, std::slice::from_ref(value))?;
        }
        Filter::In {
            column,
            values,
            negated: false,
        } => {
            membership_index(sql, columns, *column, values)?;
        }
        _ => {}
    }
    match filter {
        Filter::And(parts) | Filter::Or(parts) => {
            if parts.is_empty() {
                sql.push(if matches!(filter, Filter::And(_)) {
                    "TRUE"
                } else {
                    "FALSE"
                });
            }
            for (index, part) in parts.iter().enumerate() {
                if index > 0 {
                    sql.push(if matches!(filter, Filter::And(_)) {
                        " AND "
                    } else {
                        " OR "
                    });
                }
                predicate(sql, columns, part)?;
            }
        }
        Filter::Comparison {
            column,
            operator,
            value,
        } if columns.kind(*column)? == &ColumnKind::DateRange => {
            range_comparison(sql, columns.index(*column)?, *operator, value)?;
        }
        Filter::Comparison {
            column,
            operator,
            value,
        } => {
            columns.reference(sql, *column, true)?;
            sql.push(match operator {
                ComparisonOperator::Equal => " = ",
                ComparisonOperator::NotEqual => " <> ",
                ComparisonOperator::Less => " < ",
                ComparisonOperator::LessOrEqual => " <= ",
                ComparisonOperator::Greater => " > ",
                ComparisonOperator::GreaterOrEqual => " >= ",
            });
            literal(sql, value)?;
        }
        Filter::In {
            column,
            values,
            negated,
        } => {
            if values.is_empty() {
                return Err(ViewRowsError::InvalidQuery);
            }
            columns.reference(sql, *column, true)?;
            sql.push(if *negated { " NOT IN (" } else { " IN (" });
            for (index, value) in values.iter().enumerate() {
                if index > 0 {
                    sql.push(", ");
                }
                literal(sql, value)?;
            }
            sql.push(")");
        }
        Filter::Has {
            column,
            value,
            negated,
        } => {
            sql.push(if *negated {
                "NOT COALESCE("
            } else {
                "COALESCE("
            });
            literal(sql, value)?;
            sql.push(" = ANY(");
            columns.reference(sql, *column, false)?;
            sql.push("), FALSE)");
        }
        Filter::IsNull { column, negated } => {
            columns.reference(sql, *column, false)?;
            sql.push(if *negated { " IS NOT NULL" } else { " IS NULL" });
        }
        Filter::Like {
            column,
            pattern,
            escape,
            negated,
        } => {
            sql.push("lower(");
            columns.reference(sql, *column, true)?;
            sql.push(" COLLATE \"und-x-icu\") COLLATE \"C\"");
            sql.push(if *negated { " NOT LIKE " } else { " LIKE " });
            sql.push_bind(pattern.to_lowercase());
            sql.push(" ESCAPE ")
                .push_bind(escape.map(|value| value.to_string()).unwrap_or_default());
        }
    }
    sql.push(")");
    Ok(())
}

/// A range compared to a date the way the engine reads it
/// (`DateRange::compare_to`): `=` contains it, `<` ends before it, `<=`
/// starts on or before it, `>` starts after it and `>=` ends on or after it.
fn range_comparison(
    sql: &mut Sql,
    index: usize,
    operator: ComparisonOperator,
    value: &Value,
) -> Result<(), ViewRowsError> {
    let start = format!("v{index}[1]");
    let end = format!("v{index}[2]");
    let side = |sql: &mut Sql, bound: &str, symbol: &str| {
        sql.push(format!("{bound} {symbol} "));
        literal(sql, value)
    };
    match operator {
        ComparisonOperator::Equal => {
            sql.push("(");
            side(sql, &start, "<=")?;
            sql.push(" AND ");
            side(sql, &end, ">=")?;
            sql.push(")");
        }
        ComparisonOperator::NotEqual => {
            sql.push("(");
            side(sql, &end, "<")?;
            sql.push(" OR ");
            side(sql, &start, ">")?;
            sql.push(")");
        }
        ComparisonOperator::Less => side(sql, &end, "<")?,
        ComparisonOperator::LessOrEqual => side(sql, &start, "<=")?,
        ComparisonOperator::Greater => side(sql, &start, ">")?,
        ComparisonOperator::GreaterOrEqual => side(sql, &end, ">=")?,
    }
    Ok(())
}

fn membership_index(
    sql: &mut Sql,
    columns: &Columns<'_>,
    column: Uuid,
    values: &[Value],
) -> Result<(), ViewRowsError> {
    let index = columns.index(column)?;
    let needles = values
        .iter()
        .map(|value| match (&columns.0[index].kind, value) {
            (ColumnKind::Select { .. }, Value::Option(option)) => Some(serde_json::json!({
                "type": "SelectOption", "value": [option.to_string()]
            })),
            (ColumnKind::Entity { .. }, Value::Entity(entity)) => Some(serde_json::json!({
                "type": "EntityReference", "value": [{"entity_id": entity}]
            })),
            _ => None,
        })
        .collect::<Option<Vec<_>>>();
    if let Some(needles) = needles.filter(|values| !values.is_empty()) {
        sql.push("(");
        for (at, needle) in needles.into_iter().enumerate() {
            if at > 0 {
                sql.push(" OR ");
            }
            sql.push(format!("raw{index} @> ")).push_bind(needle);
        }
        sql.push(") AND ");
    }
    Ok(())
}