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.

21.9 KB
Raw Blame
use filter_ast::Expr;
use item_filters::ast::properties::{EntityRefId, PropertiesLiteral, PropertyMatchValue};

use super::*;
use crate::catalog::{PEOPLE_EMAIL, PEOPLE_ID, PEOPLE_NAME, PEOPLE_TABLE};
use crate::resolve::{
    AggregateFunction, ComparisonOperator, Direction, JoinKind, Order, OrderKey, Query, Relation,
    Value, column_key, compile, row_id_key,
};
use crate::test_support::{catalog, *};

fn select(sql: &str) -> SelectQuery {
    match compile(&catalog(), sql).unwrap() {
        Query::Select(select) => select,
        Query::Insert(_) | Query::Update(_) | Query::Delete(_) | Query::AlterColumnType(_) => {
            panic!("not a SELECT")
        }
    }
}

#[test]
fn pushable_and_residual_conjuncts_are_divided() {
    let plan = split(
        &catalog(),
        select(
            "SELECT name, amount FROM crm.deals WHERE stage = 'Won' AND amount > 5000 ORDER BY amount DESC",
        ),
    );

    assert_eq!(
        plan,
        Plan {
            relations: vec![RelationPlan {
                relation: Relation {
                    table: DEALS,
                    alias: "deals".into(),
                    source: TableSource::Database,
                },
                query: GqlQuery::Soup {
                    table: DEALS,
                    property_filter: Some(Expr::Literal(PropertiesLiteral {
                        property_definition_id: STAGE,
                        entity_type: None,
                        value: PropertyMatchValue::SelectOption(WON.into_uuid())
                    })),
                    key_hint: None,
                },
                needs: vec![NAME, AMOUNT],
            }],
            joins: vec![],
            residual: Some(Filter::Comparison {
                column: AMOUNT,
                operator: ComparisonOperator::Greater,
                value: Value::Number(5000.0),
            }),
            distinct: false,
            shape: Shape::Rows(vec![NAME, AMOUNT]),
            order_by: vec![Order {
                key: OrderKey::Column(AMOUNT),
                direction: Direction::Descending,
            }],
            limit: None,
            offset: None,
            bindings: plan.bindings.clone(),
        }
    );
}

#[test]
fn an_or_with_a_residual_side_pushes_nothing() {
    let plan = split(
        &catalog(),
        select("SELECT name FROM crm.deals WHERE stage = 'Won' OR amount > 5000"),
    );

    assert_eq!(
        plan,
        Plan {
            relations: vec![RelationPlan {
                relation: Relation {
                    table: DEALS,
                    alias: "deals".into(),
                    source: TableSource::Database,
                },
                query: GqlQuery::Soup {
                    table: DEALS,
                    property_filter: None,
                    key_hint: None,
                },
                needs: vec![NAME, STAGE, AMOUNT],
            }],
            joins: vec![],
            residual: Some(Filter::Or(vec![
                Filter::Comparison {
                    column: STAGE,
                    operator: ComparisonOperator::Equal,
                    value: Value::Option(WON),
                },
                Filter::Comparison {
                    column: AMOUNT,
                    operator: ComparisonOperator::Greater,
                    value: Value::Number(5000.0),
                },
            ])),
            distinct: false,
            shape: Shape::Rows(vec![NAME]),
            order_by: vec![],
            limit: None,
            offset: None,
            bindings: plan.bindings.clone(),
        }
    );
}

#[test]
fn in_lists_nested_ors_and_has_push_as_one_expression() {
    let plan = split(
        &catalog(),
        select(
            "SELECT name FROM crm.deals
             WHERE (stage IN ('Won', 'Lead') OR owner = 'macro|[email protected]')
               AND tags HAS 'vip'
               AND name LIKE 'A%'",
        ),
    );

    assert_eq!(
        plan,
        Plan {
            relations: vec![RelationPlan {
                relation: Relation {
                    table: DEALS,
                    alias: "deals".into(),
                    source: TableSource::Database,
                },
                query: GqlQuery::Soup {
                    table: DEALS,
                    property_filter: Some(Expr::and(
                        Expr::or(
                            Expr::or(
                                Expr::Literal(PropertiesLiteral {
                                    property_definition_id: STAGE,
                                    entity_type: None,
                                    value: PropertyMatchValue::SelectOption(WON.into_uuid())
                                }),
                                Expr::Literal(PropertiesLiteral {
                                    property_definition_id: STAGE,
                                    entity_type: None,
                                    value: PropertyMatchValue::SelectOption(LEAD.into_uuid())
                                })
                            ),
                            Expr::Literal(PropertiesLiteral {
                                property_definition_id: OWNER,
                                entity_type: None,
                                value: PropertyMatchValue::EntityRef(
                                    EntityRefId::new("macro|[email protected]".into()).unwrap()
                                )
                            }),
                        ),
                        Expr::Literal(PropertiesLiteral {
                            property_definition_id: TAGS,
                            entity_type: None,
                            value: PropertyMatchValue::SelectOption(VIP.into_uuid())
                        }),
                    )),
                    key_hint: None,
                },
                needs: vec![NAME],
            }],
            joins: vec![],
            residual: Some(Filter::Like {
                column: NAME,
                pattern: "A%".into(),
                escape: None,
                negated: false,
            }),
            distinct: false,
            shape: Shape::Rows(vec![NAME]),
            order_by: vec![],
            limit: None,
            offset: None,
            bindings: plan.bindings.clone(),
        }
    );
}

#[test]
fn negations_stay_residual_because_soup_not_keeps_empty_cells() {
    let plan = split(
        &catalog(),
        select(
            "SELECT name FROM crm.deals
             WHERE stage != 'Won' AND tags NOT HAS 'vip' AND owner NOT IN ('macro|[email protected]') AND done = TRUE",
        ),
    );

    assert_eq!(
        plan,
        Plan {
            relations: vec![RelationPlan {
                relation: Relation {
                    table: DEALS,
                    alias: "deals".into(),
                    source: TableSource::Database,
                },
                query: GqlQuery::Soup {
                    table: DEALS,
                    property_filter: None,
                    key_hint: None,
                },
                needs: vec![NAME, STAGE, TAGS, OWNER, DONE],
            }],
            joins: vec![],
            residual: Some(Filter::And(vec![
                Filter::Comparison {
                    column: STAGE,
                    operator: ComparisonOperator::NotEqual,
                    value: Value::Option(WON),
                },
                Filter::Has {
                    column: TAGS,
                    value: Value::Option(VIP),
                    negated: true,
                },
                Filter::In {
                    column: OWNER,
                    values: vec![Value::Entity("macro|[email protected]".into())],
                    negated: true,
                },
                Filter::Comparison {
                    column: DONE,
                    operator: ComparisonOperator::Equal,
                    value: Value::Bool(true),
                },
            ])),
            distinct: false,
            shape: Shape::Rows(vec![NAME]),
            order_by: vec![],
            limit: None,
            offset: None,
            bindings: plan.bindings.clone(),
        }
    );
}

#[test]
fn count_per_select_group_needs_no_rows() {
    let plan = split(
        &catalog(),
        select(
            "SELECT stage, COUNT(*) FROM crm.deals WHERE owner = 'macro|[email protected]' GROUP BY stage ORDER BY 2 DESC",
        ),
    );

    assert_eq!(
        plan,
        Plan {
            relations: vec![RelationPlan {
                relation: Relation {
                    table: DEALS,
                    alias: "deals".into(),
                    source: TableSource::Database,
                },
                query: GqlQuery::GroupSoup {
                    table: DEALS,
                    property_filter: Some(Expr::Literal(PropertiesLiteral {
                        property_definition_id: OWNER,
                        entity_type: None,
                        value: PropertyMatchValue::EntityRef(
                            EntityRefId::new("macro|[email protected]".into()).unwrap()
                        )
                    })),
                    group_by: STAGE,
                },
                needs: vec![],
            }],
            joins: vec![],
            residual: None,
            distinct: false,
            shape: Shape::Aggregate {
                group_by: Some(STAGE),
                items: vec![
                    SelectItem::Column(STAGE),
                    SelectItem::Aggregate {
                        function: AggregateFunction::Count,
                        column: None,
                    },
                ],
            },
            order_by: vec![Order {
                key: OrderKey::Item(1),
                direction: Direction::Descending,
            }],
            limit: None,
            offset: None,
            bindings: plan.bindings.clone(),
        }
    );
}

#[test]
fn any_other_aggregate_or_a_residual_filter_fetches_rows_and_folds() {
    let plan = split(
        &catalog(),
        select(
            "SELECT owner, SUM(amount), COUNT(*)
             FROM crm.deals
             WHERE stage IN ('Won', 'Lead') AND amount > 5000 AND \"closed at\" IS NOT NULL
             GROUP BY owner
             ORDER BY 2 DESC, owner ASC",
        ),
    );

    assert_eq!(
        plan,
        Plan {
            relations: vec![RelationPlan {
                relation: Relation {
                    table: DEALS,
                    alias: "deals".into(),
                    source: TableSource::Database,
                },
                query: GqlQuery::Soup {
                    table: DEALS,
                    property_filter: Some(Expr::or(
                        Expr::Literal(PropertiesLiteral {
                            property_definition_id: STAGE,
                            entity_type: None,
                            value: PropertyMatchValue::SelectOption(WON.into_uuid())
                        }),
                        Expr::Literal(PropertiesLiteral {
                            property_definition_id: STAGE,
                            entity_type: None,
                            value: PropertyMatchValue::SelectOption(LEAD.into_uuid())
                        })
                    )),
                    key_hint: None,
                },
                needs: vec![OWNER, AMOUNT, CLOSED_AT],
            }],
            joins: vec![],
            residual: Some(Filter::And(vec![
                Filter::Comparison {
                    column: AMOUNT,
                    operator: ComparisonOperator::Greater,
                    value: Value::Number(5000.0),
                },
                Filter::IsNull {
                    column: CLOSED_AT,
                    negated: true,
                },
            ])),
            distinct: false,
            shape: Shape::Aggregate {
                group_by: Some(OWNER),
                items: vec![
                    SelectItem::Column(OWNER),
                    SelectItem::Aggregate {
                        function: AggregateFunction::Sum,
                        column: Some(AMOUNT),
                    },
                    SelectItem::Aggregate {
                        function: AggregateFunction::Count,
                        column: None,
                    },
                ],
            },
            order_by: vec![
                Order {
                    key: OrderKey::Item(1),
                    direction: Direction::Descending,
                },
                Order {
                    key: OrderKey::Column(OWNER),
                    direction: Direction::Ascending,
                },
            ],
            limit: None,
            offset: None,
            bindings: plan.bindings.clone(),
        }
    );

    // The same COUNT-only shape over a residual filter must fetch rows too:
    // bins are counted before we could apply `amount > 5000`.
    let plan = split(
        &catalog(),
        select("SELECT stage, COUNT(*) FROM crm.deals WHERE amount > 5000 GROUP BY stage"),
    );
    assert_eq!(
        plan.relations[0].query,
        GqlQuery::Soup {
            table: DEALS,
            property_filter: None,
            key_hint: None,
        }
    );
    assert_eq!(plan.relations[0].needs, vec![STAGE, AMOUNT]);
}

#[test]
fn a_whole_table_read_pushes_nothing_and_needs_every_column() {
    let plan = split(&catalog(), select("SELECT * FROM crm.people"));

    assert_eq!(
        plan,
        Plan {
            relations: vec![RelationPlan {
                relation: Relation {
                    table: PEOPLE,
                    alias: "people".into(),
                    source: TableSource::Database,
                },
                query: GqlQuery::Soup {
                    table: PEOPLE,
                    property_filter: None,
                    key_hint: None,
                },
                needs: vec![NAME],
            }],
            joins: vec![],
            residual: None,
            distinct: false,
            shape: Shape::Rows(vec![NAME]),
            order_by: vec![],
            limit: None,
            offset: None,
            bindings: plan.bindings.clone(),
        }
    );
}

#[test]
fn each_relation_gets_its_own_pushdown_and_needs() {
    let plan = split(
        &catalog(),
        select(
            "SELECT DISTINCT p.email
             FROM macro.tasks t
             JOIN macro.people p ON t.assignees = p.id
             LEFT JOIN crm.deals d ON t.deal = d.row_id
             WHERE t.priority = 'High' AND d.stage = 'Won' AND d.amount > 100 AND p.name LIKE 'A%'",
        ),
    );
    let people_id = column_key(1, PEOPLE_ID);
    let people_email = column_key(1, PEOPLE_EMAIL);
    let people_name = column_key(1, PEOPLE_NAME);
    let deals_stage = column_key(2, STAGE);
    let deals_amount = column_key(2, AMOUNT);

    assert_eq!(
        plan.relations,
        vec![
            RelationPlan {
                relation: Relation {
                    table: TASKS,
                    alias: "t".into(),
                    source: TableSource::Database,
                },
                query: GqlQuery::Soup {
                    table: TASKS,
                    property_filter: Some(Expr::Literal(PropertiesLiteral {
                        property_definition_id: PRIORITY,
                        entity_type: None,
                        value: PropertyMatchValue::SelectOption(HIGH.into_uuid())
                    })),
                    key_hint: None,
                },
                needs: vec![ASSIGNEES, DEAL],
            },
            RelationPlan {
                relation: Relation {
                    table: PEOPLE_TABLE,
                    alias: "p".into(),
                    source: TableSource::People,
                },
                query: GqlQuery::People { ids: None },
                needs: vec![people_email, people_name, people_id],
            },
            RelationPlan {
                relation: Relation {
                    table: DEALS,
                    alias: "d".into(),
                    source: TableSource::Database,
                },
                // Left joined: its conditions apply after the join.
                query: GqlQuery::Soup {
                    table: DEALS,
                    property_filter: None,
                    key_hint: None,
                },
                needs: vec![deals_stage, deals_amount, row_id_key(DEALS)],
            },
        ]
    );
    assert_eq!(
        plan.joins,
        vec![
            JoinPlan {
                relation: 1,
                kind: JoinKind::Inner,
                on: vec![(ASSIGNEES, people_id)],
            },
            JoinPlan {
                relation: 2,
                kind: JoinKind::Left,
                on: vec![(DEAL, row_id_key(DEALS))],
            },
        ]
    );
    assert_eq!(
        plan.residual,
        Some(Filter::And(vec![
            Filter::Comparison {
                column: deals_stage,
                operator: ComparisonOperator::Equal,
                value: Value::Option(WON),
            },
            Filter::Comparison {
                column: deals_amount,
                operator: ComparisonOperator::Greater,
                value: Value::Number(100.0),
            },
            Filter::Like {
                column: people_name,
                pattern: "A%".into(),
                escape: None,
                negated: false,
            },
        ]))
    );
    assert!(plan.distinct);
    assert_eq!(plan.shape, Shape::Rows(vec![people_email]));
    assert_eq!(
        plan.column(&catalog(), deals_amount).map(|c| c.id),
        Some(AMOUNT)
    );
    assert_eq!(plan.column(&catalog(), row_id_key(DEALS)), None);
    assert_eq!(plan.table(), TASKS);
}

#[test]
fn a_left_joined_relation_takes_no_pushdown_and_an_inner_joined_one_does() {
    let plan = split(
        &catalog(),
        select(
            "SELECT p.name FROM crm.people p
             JOIN crm.deals d ON p.row_id = d.owner
             LEFT JOIN macro.tasks t ON d.row_id = t.deal
             WHERE d.stage = 'Won' AND t.priority = 'High'",
        ),
    );
    let tasks_priority = column_key(2, PRIORITY);

    assert_eq!(
        plan.relations[1].query,
        GqlQuery::Soup {
            table: DEALS,
            property_filter: Some(Expr::Literal(PropertiesLiteral {
                property_definition_id: STAGE,
                entity_type: None,
                value: PropertyMatchValue::SelectOption(WON.into_uuid())
            })),
            key_hint: None,
        }
    );
    assert_eq!(
        plan.relations[2].query,
        GqlQuery::Soup {
            table: TASKS,
            property_filter: None,
            key_hint: None,
        }
    );
    assert_eq!(
        plan.residual,
        Some(Filter::Comparison {
            column: tasks_priority,
            operator: ComparisonOperator::Equal,
            value: Value::Option(HIGH),
        })
    );
    assert_eq!(
        plan.relations[2].needs,
        vec![tasks_priority, column_key(2, DEAL)]
    );
}

#[test]
fn a_condition_spanning_relations_stays_residual_and_bins_need_one_relation() {
    let plan = split(
        &catalog(),
        select(
            "SELECT t.priority, COUNT(*) FROM macro.tasks t JOIN macro.people p ON t.assignees = p.id
             WHERE t.priority = 'High' OR p.name = 'Sam' GROUP BY t.priority",
        ),
    );
    // COUNT per select group would be bins over one table; over a join the
    // rows are needed.
    assert!(matches!(
        plan.relations[0].query,
        GqlQuery::Soup {
            property_filter: None,
            ..
        }
    ));
    assert!(plan.residual.is_some());
    assert_eq!(plan.relations[0].needs, vec![PRIORITY, ASSIGNEES]);

    let plan = split(
        &catalog(),
        select("SELECT DISTINCT stage, COUNT(*) FROM crm.deals GROUP BY stage"),
    );
    assert!(matches!(plan.relations[0].query, GqlQuery::Soup { .. }));
}

#[test]
fn counting_per_member_of_a_multi_valued_column_folds_rows() {
    // Soup bins a multi-select row once per option it holds; SQL groups by
    // the whole cell, so the bins cannot answer it.
    let plan = split(
        &catalog(),
        select("SELECT tags, COUNT(*) FROM crm.deals GROUP BY tags"),
    );
    assert_eq!(
        plan.relations[0].query,
        GqlQuery::Soup {
            table: DEALS,
            property_filter: None,
            key_hint: None,
        }
    );
    assert_eq!(plan.relations[0].needs, vec![TAGS]);
}

#[test]
fn a_row_id_condition_never_pushes_down() {
    let plan = split(
        &catalog(),
        select("SELECT name FROM crm.deals WHERE row_id = '00000000-0000-0000-0000-0000000000a1'"),
    );
    assert!(matches!(
        plan.relations[0].query,
        GqlQuery::Soup {
            property_filter: None,
            ..
        }
    ));
    assert_eq!(plan.relations[0].needs, vec![NAME, row_id_key(DEALS)]);
}

#[test]
fn propf_serializes_to_the_soup_wire_form() {
    let plan = split(
        &catalog(),
        select(
            "SELECT name FROM crm.deals WHERE stage = 'Won' AND owner = 'macro|[email protected]'",
        ),
    );
    let GqlQuery::Soup {
        property_filter, ..
    } = plan.relations.into_iter().next().unwrap().query
    else {
        panic!("expected a soup query");
    };

    assert_eq!(
        serde_json::to_value(property_filter.unwrap()).unwrap(),
        serde_json::json!({
            "&": [
                { "l": { "pd": "00000000-0000-0000-0000-000000000003", "v": { "so": "00000000-0000-0000-0000-000000000030" } } },
                { "l": { "pd": "00000000-0000-0000-0000-000000000005", "v": { "er": "macro|[email protected]" } } }
            ]
        })
    );
}

#[test]
fn a_row_position_condition_never_pushes_down() {
    let plan = split(
        &catalog(),
        select("SELECT name FROM crm.deals WHERE row_position = '000000000001'"),
    );
    assert!(matches!(
        plan.relations[0].query,
        GqlQuery::Soup {
            property_filter: None,
            ..
        }
    ));
    assert_eq!(
        plan.relations[0].needs,
        vec![NAME, crate::resolve::row_position_key(DEALS)]
    );
}