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.

17.8 KB
Raw Blame
use models_databases::RowId;
use std::collections::HashMap;

use chrono::{TimeZone, Utc};

use super::*;
use crate::resolve::{Filter, Query, compile};
use crate::split::split;
use crate::test_support::{catalog, *};

const ACME: RowId = RowId::from_uuid(Uuid::from_u128(0xa1));
const GLOBEX: RowId = RowId::from_uuid(Uuid::from_u128(0xa2));
const HOOLI: RowId = RowId::from_uuid(Uuid::from_u128(0xa3));
const INITECH: RowId = RowId::from_uuid(Uuid::from_u128(0xa4));

/// Four deals as the server would return them for a `SELECT *`:
/// Acme (Won, 12000, Sam, vip, done), Globex (Lead, 3000, Sam), Hooli (Won,
/// no amount, Ana, closed), Initech (no stage, 7000, no owner, not done).
fn deals() -> Vec<Row> {
    vec![
        Row {
            id: ACME,
            position: None,
            cells: HashMap::from([
                (NAME, Cell::Text("Acme".into())),
                (AMOUNT, Cell::Number(12000.0)),
                (STAGE, Cell::Options(vec![WON])),
                (OWNER, Cell::Entities(vec!["macro|[email protected]".into()])),
                (TAGS, Cell::Options(vec![VIP])),
                (DONE, Cell::Bool(true)),
            ]),
        },
        Row {
            id: GLOBEX,
            position: None,
            cells: HashMap::from([
                (NAME, Cell::Text("Globex".into())),
                (AMOUNT, Cell::Number(3000.0)),
                (STAGE, Cell::Options(vec![LEAD])),
                (OWNER, Cell::Entities(vec!["macro|[email protected]".into()])),
                (TAGS, Cell::Options(vec![])),
            ]),
        },
        Row {
            id: HOOLI,
            position: None,
            cells: HashMap::from([
                (NAME, Cell::Text("hooli".into())),
                (STAGE, Cell::Options(vec![WON])),
                (
                    CLOSED_AT,
                    Cell::Date(Utc.with_ymd_and_hms(2026, 9, 15, 0, 0, 0).unwrap()),
                ),
                (OWNER, Cell::Entities(vec!["macro|[email protected]".into()])),
            ]),
        },
        Row {
            id: INITECH,
            position: None,
            cells: HashMap::from([
                (NAME, Cell::Text("Initech".into())),
                (AMOUNT, Cell::Number(7000.0)),
                (DONE, Cell::Bool(false)),
            ]),
        },
    ]
}

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

#[test]
fn rows_are_filtered_projected_and_sorted() {
    let plan = plan(
        "SELECT name, amount, \"closed at\" FROM crm.deals
         WHERE amount > 5000 OR \"closed at\" IS NOT NULL
         ORDER BY amount DESC",
    );

    assert_eq!(
        fold_rows(&catalog(), &plan, deals()),
        vec![
            vec![
                Some(Cell::Text("Acme".into())),
                Some(Cell::Number(12000.0)),
                None,
            ],
            vec![
                Some(Cell::Text("Initech".into())),
                Some(Cell::Number(7000.0)),
                None,
            ],
            // No amount: sorts last even though the order is descending.
            vec![
                Some(Cell::Text("hooli".into())),
                None,
                Some(Cell::Date(
                    Utc.with_ymd_and_hms(2026, 9, 15, 0, 0, 0).unwrap()
                )),
            ],
        ]
    );
}

#[test]
fn aggregates_per_group_with_a_residual_filter() {
    let plan = plan(
        "SELECT owner, SUM(amount), COUNT(*), COUNT(amount), AVG(amount)
         FROM crm.deals
         WHERE stage IN ('Won', 'Lead')
         GROUP BY owner
         ORDER BY 2 DESC",
    );
    // The server would have applied `stage IN (...)`; hand the fold only
    // what it would have returned.
    let won_or_lead: Vec<Row> = deals().into_iter().take(3).collect();

    assert_eq!(
        fold_rows(&catalog(), &plan, won_or_lead),
        vec![
            vec![
                Some(Cell::Entities(vec!["macro|[email protected]".into()])),
                Some(Cell::Number(15000.0)),
                Some(Cell::Number(2.0)),
                Some(Cell::Number(2.0)),
                Some(Cell::Number(7500.0)),
            ],
            // Hooli has no amount: SUM and AVG of nothing are NULL, COUNT(*)
            // still counts the row, COUNT(amount) does not.
            vec![
                Some(Cell::Entities(vec!["macro|[email protected]".into()])),
                None,
                Some(Cell::Number(1.0)),
                Some(Cell::Number(0.0)),
                None,
            ],
        ]
    );
}

#[test]
fn aggregates_without_group_by_are_one_row_even_over_nothing() {
    let plan = plan("SELECT COUNT(*), SUM(amount), MIN(amount), MAX(\"closed at\") FROM crm.deals");

    assert_eq!(
        fold_rows(&catalog(), &plan, deals()),
        vec![vec![
            Some(Cell::Number(4.0)),
            Some(Cell::Number(22000.0)),
            Some(Cell::Number(3000.0)),
            Some(Cell::Date(
                Utc.with_ymd_and_hms(2026, 9, 15, 0, 0, 0).unwrap()
            )),
        ]]
    );
    assert_eq!(
        fold_rows(&catalog(), &plan, vec![]),
        vec![vec![Some(Cell::Number(0.0)), None, None, None]]
    );
}

#[test]
fn groups_sort_by_option_order_and_the_empty_group_last() {
    // Option order in the catalog is Lead, Won; a plain sort would put Won
    // first by id.
    let plan = plan(
        "SELECT stage, COUNT(*) FROM crm.deals WHERE amount > 0 GROUP BY stage ORDER BY stage",
    );

    assert_eq!(
        fold_rows(&catalog(), &plan, deals()),
        vec![
            vec![Some(Cell::Options(vec![LEAD])), Some(Cell::Number(1.0))],
            vec![Some(Cell::Options(vec![WON])), Some(Cell::Number(1.0))],
            vec![None, Some(Cell::Number(1.0))],
        ]
    );
}

#[test]
fn bins_answer_a_count_only_group() {
    let plan = plan("SELECT stage, COUNT(*) FROM crm.deals GROUP BY stage ORDER BY 2 DESC, stage");
    let bins = vec![
        Bin {
            key: Some(Cell::Options(vec![LEAD])),
            count: 4,
        },
        Bin {
            key: None,
            count: 4,
        },
        Bin {
            key: Some(Cell::Options(vec![WON])),
            count: 9,
        },
    ];

    assert_eq!(
        fold_bins(&catalog(), &plan, bins),
        Ok(vec![
            vec![Some(Cell::Options(vec![WON])), Some(Cell::Number(9.0))],
            vec![Some(Cell::Options(vec![LEAD])), Some(Cell::Number(4.0))],
            vec![None, Some(Cell::Number(4.0))],
        ])
    );
}

#[test]
fn grouped_bins_apply_limit_and_offset_after_sorting() {
    use crate::engine::{Engine, Step};

    for (window, expected) in [
        (
            "LIMIT 1",
            vec![vec![
                Some(Cell::Options(vec![WON])),
                Some(Cell::Number(9.0)),
            ]],
        ),
        (
            "LIMIT 1 OFFSET 1",
            vec![vec![
                Some(Cell::Options(vec![LEAD])),
                Some(Cell::Number(4.0)),
            ]],
        ),
        ("LIMIT 0", vec![]),
        ("LIMIT 1 OFFSET 3", vec![]),
    ] {
        let sql = format!(
            "SELECT stage, COUNT(*) FROM crm.deals \
             GROUP BY stage ORDER BY 2 DESC, stage {window}"
        );
        let (mut engine, step) = Engine::start(&catalog(), &sql).unwrap();
        let Step::Bins(request) = step else {
            panic!("expected grouped bins for {sql}");
        };
        let bins = vec![
            Bin {
                key: Some(Cell::Options(vec![LEAD])),
                count: 4,
            },
            Bin {
                key: None,
                count: 4,
            },
            Bin {
                key: Some(Cell::Options(vec![WON])),
                count: 9,
            },
        ];
        let Step::Done(outcome) = engine.feed_bins(request.id, bins).unwrap() else {
            panic!("expected a completed read for {sql}");
        };
        assert_eq!(outcome.rows, expected, "{sql}");
        assert!(outcome.row_ids.is_empty());
    }
}

#[test]
fn limit_and_offset_apply_after_ordering() {
    let windowed = plan("SELECT name FROM crm.deals ORDER BY name LIMIT 2 OFFSET 1");
    assert_eq!(
        fold_rows(&catalog(), &windowed, deals()),
        vec![
            vec![Some(Cell::Text("Globex".into()))],
            vec![Some(Cell::Text("hooli".into()))],
        ]
    );

    let top_group =
        plan("SELECT stage, COUNT(*) FROM crm.deals GROUP BY stage ORDER BY 2 DESC LIMIT 1");
    assert_eq!(
        fold_rows(&catalog(), &top_group, deals()),
        vec![vec![
            Some(Cell::Options(vec![WON])),
            Some(Cell::Number(2.0))
        ]]
    );
}

#[test]
fn residual_predicates_follow_sql_null_rules_and_macro_matching() {
    let cases: &[(&str, &[RowId])] = &[
        // number comparisons; an empty cell never compares true
        ("amount > 5000", &[ACME, INITECH]),
        ("amount <= 7000", &[GLOBEX, INITECH]),
        ("amount != 7000", &[ACME, GLOBEX]),
        // != and NOT IN drop empty cells, as in SQL
        ("stage != 'Won'", &[GLOBEX]),
        ("stage NOT IN ('Won', 'Lead')", &[]),
        ("owner NOT IN ('macro|[email protected]')", &[HOOLI]),
        // IS NULL is how you ask for empty cells
        ("stage IS NULL", &[INITECH]),
        ("stage IS NOT NULL", &[ACME, GLOBEX, HOOLI]),
        // multi-valued: an absent cell is the empty set
        ("tags HAS 'vip'", &[ACME]),
        ("tags NOT HAS 'vip'", &[GLOBEX, HOOLI, INITECH]),
        // text: LIKE ignores case, = does not
        ("name LIKE 'h%'", &[HOOLI]),
        ("name LIKE '%o%'", &[GLOBEX, HOOLI]),
        ("name LIKE '_cme'", &[ACME]),
        ("name NOT LIKE '%e%'", &[HOOLI]),
        ("name = 'Hooli'", &[]),
        // ESCAPE makes the next pattern character literal
        ("name LIKE 'acm_'", &[ACME]),
        (r"name LIKE 'acm\_' ESCAPE '\'", &[]),
        (r"name LIKE 'acm\e' ESCAPE '\'", &[ACME]),
        (r"name LIKE '%\%%' ESCAPE '\'", &[]),
        (
            r"name NOT LIKE '%\%%' ESCAPE '\'",
            &[ACME, GLOBEX, HOOLI, INITECH],
        ),
        ("name LIKE 'H%' ESCAPE '!'", &[HOOLI]),
        ("name LIKE 'H!%' ESCAPE '!'", &[]),
        ("name < 'H'", &[ACME, GLOBEX]),
        // checkbox: an unset checkbox is not FALSE
        ("done = FALSE", &[INITECH]),
        ("done != TRUE", &[INITECH]),
        // dates
        ("\"closed at\" >= '2026-09-15'", &[HOOLI]),
        ("\"closed at\" < '2026-09-15T00:00:01Z'", &[HOOLI]),
        // combinations
        ("amount > 5000 AND done = TRUE", &[ACME]),
        ("amount > 5000 OR stage = 'Lead'", &[ACME, GLOBEX, INITECH]),
        (
            "(amount > 5000 OR stage = 'Lead') AND owner = 'macro|[email protected]'",
            &[ACME, GLOBEX],
        ),
    ];

    // Split would push some of these to the server; test the evaluator on the
    // whole resolved WHERE so every form is covered as the fold would apply
    // it when it is residual.
    for (where_, kept) in cases {
        let Query::Select(select) = compile(
            &catalog(),
            &format!("SELECT name FROM crm.deals WHERE {where_}"),
        )
        .unwrap() else {
            panic!("not a SELECT");
        };
        let filter = select.where_.unwrap();
        let held: Vec<RowId> = deals()
            .iter()
            .filter(|row| super::predicate::holds(&filter, row))
            .map(|row| row.id)
            .collect();
        assert_eq!(held, *kept, "\nWHERE {where_}");
    }
}

#[test]
fn distinct_keeps_the_first_of_equal_rows_after_sorting() {
    let plan = plan("SELECT DISTINCT owner FROM crm.deals ORDER BY owner");
    let (rows, ids) = fold_relations(&catalog(), &plan, vec![deals()]);
    assert_eq!(
        rows,
        vec![
            vec![Some(Cell::Entities(vec!["macro|[email protected]".into()]))],
            vec![Some(Cell::Entities(vec!["macro|[email protected]".into()]))],
            vec![None],
        ]
    );
    // Hooli is Ana's; Acme is the first of Sam's two; Initech has no owner.
    assert_eq!(ids, vec![HOOLI, ACME, INITECH]);
}

#[test]
fn distinct_keeps_different_group_keys() {
    let plan =
        plan("SELECT DISTINCT stage, COUNT(*) FROM crm.deals GROUP BY stage ORDER BY 2 DESC");
    let (rows, ids) = fold_relations(&catalog(), &plan, vec![deals()]);
    assert_eq!(rows.len(), 3);
    assert_eq!(ids, Vec::<RowId>::new());
}

#[test]
fn distinct_aggregate_results_are_deduplicated_before_limit_and_offset() {
    use crate::engine::{Engine, Step};
    use crate::run::Page;

    for (window, expected) in [
        (
            "",
            vec![vec![Some(Cell::Number(1.0))], vec![Some(Cell::Number(2.0))]],
        ),
        ("LIMIT 1 OFFSET 1", vec![vec![Some(Cell::Number(2.0))]]),
        ("LIMIT 1 OFFSET 2", vec![]),
    ] {
        let sql = format!(
            "SELECT DISTINCT COUNT(*) AS total FROM crm.deals \
             GROUP BY stage ORDER BY total {window}"
        );
        let (mut engine, step) = Engine::start(&catalog(), &sql).unwrap();
        let Step::Fetch(request) = step else {
            panic!("expected rows for distinct groups");
        };
        let Step::Done(outcome) = engine
            .feed_page(
                request.id,
                Page {
                    rows: deals(),
                    next: None,
                },
            )
            .unwrap()
        else {
            panic!("expected a completed read");
        };
        assert_eq!(outcome.rows, expected, "{sql}");
        assert!(outcome.row_ids.is_empty());
    }
}

#[test]
fn join_matches_by_membership_and_leaves_empty_cells_unmatched() {
    use crate::catalog::PEOPLE_ID;
    use crate::resolve::column_key;

    let plan = plan(
        "SELECT d.name, p.name FROM crm.deals d LEFT JOIN macro.people p ON d.owner = p.id ORDER BY d.name",
    );
    let people = vec![
        Row {
            id: RowId::from_uuid(Uuid::from_u128(0x71)),
            position: None,
            cells: HashMap::from([
                (
                    column_key(1, PEOPLE_ID),
                    Cell::Entities(vec!["macro|[email protected]".into()]),
                ),
                (
                    column_key(1, crate::catalog::PEOPLE_NAME),
                    Cell::Text("Sam".into()),
                ),
            ]),
        },
        Row {
            id: RowId::from_uuid(Uuid::from_u128(0x72)),
            position: None,
            cells: HashMap::from([
                (
                    column_key(1, PEOPLE_ID),
                    Cell::Entities(vec!["macro|[email protected]".into()]),
                ),
                (
                    column_key(1, crate::catalog::PEOPLE_NAME),
                    Cell::Text("Ana".into()),
                ),
            ]),
        },
    ];
    let joined = join::join(&plan, vec![deals(), people.clone()]);
    assert_eq!(joined.len(), 4);
    let (rows, ids) = fold_relations(&catalog(), &plan, vec![deals(), people]);
    assert_eq!(
        rows,
        vec![
            vec![
                Some(Cell::Text("Acme".into())),
                Some(Cell::Text("Sam".into()))
            ],
            vec![
                Some(Cell::Text("Globex".into())),
                Some(Cell::Text("Sam".into()))
            ],
            vec![
                Some(Cell::Text("hooli".into())),
                Some(Cell::Text("Ana".into()))
            ],
            vec![Some(Cell::Text("Initech".into())), None],
        ]
    );
    assert_eq!(ids, vec![ACME, GLOBEX, HOOLI, INITECH]);
}

/// Many `%`s over a long text that almost matches: backtracking would try
/// every way to split the text between them.
#[test]
fn like_with_many_wildcards_matches_in_linear_passes() {
    let row = Row {
        id: ACME,
        position: None,
        cells: HashMap::from([(NAME, Cell::Text(format!("{}b", "a".repeat(5_000))))]),
    };
    let like = |pattern: &str| Filter::Like {
        column: NAME,
        pattern: pattern.into(),
        escape: None,
        negated: false,
    };

    assert!(!predicate::holds(&like("%a%a%a%a%a%a%a%a%a%a%a%a%c"), &row));
    assert!(predicate::holds(&like("%a%a%a%a%a%a%a%a%a%a%a%a%b"), &row));
    assert!(predicate::holds(&like("a%_b"), &row));
    assert!(!predicate::holds(&like("%ab_"), &row));
}

/// `0` and `-0` are equal in SQL, so they are one group and one distinct row.
#[test]
fn zero_and_negative_zero_are_one_value() {
    let rows = vec![
        Row {
            id: ACME,
            position: None,
            cells: HashMap::from([(AMOUNT, Cell::Number(0.0))]),
        },
        Row {
            id: GLOBEX,
            position: None,
            cells: HashMap::from([(AMOUNT, Cell::Number(-0.0))]),
        },
    ];

    assert_eq!(
        fold_rows(
            &catalog(),
            &plan("SELECT DISTINCT amount FROM crm.deals"),
            rows.clone()
        ),
        vec![vec![Some(Cell::Number(0.0))]]
    );
    assert_eq!(
        fold_rows(
            &catalog(),
            &plan("SELECT amount, COUNT(*) FROM crm.deals GROUP BY amount"),
            rows
        ),
        vec![vec![Some(Cell::Number(0.0)), Some(Cell::Number(2.0))]]
    );
}

#[test]
fn bins_do_not_answer_a_row_shaped_plan() {
    assert_eq!(
        fold_bins(&catalog(), &plan("SELECT name FROM crm.deals"), vec![]),
        Err(RunError::NotAnsweredByBins)
    );
}

#[test]
fn row_queries_order_by_position_and_alias() {
    let expected = vec![
        vec![Some(Cell::Text("Acme".into()))],
        vec![Some(Cell::Text("Initech".into()))],
        vec![Some(Cell::Text("Globex".into()))],
        vec![Some(Cell::Text("hooli".into()))],
    ];
    for sql in [
        "SELECT name, amount FROM crm.deals ORDER BY 2 DESC",
        "SELECT name, amount AS total FROM crm.deals ORDER BY total DESC",
    ] {
        let names: Vec<_> = fold_rows(&catalog(), &plan(sql), deals())
            .into_iter()
            .map(|row| vec![row[0].clone()])
            .collect();
        assert_eq!(names, expected, "{sql}");
    }
}