rust-rel8 0.1.4

use rust_rel8::{
    helper_tables::{One, Two},
    helper_utilities::ShortenLifetime,
    *,
};
use sqlx::PgPool;

#[derive(Debug, PartialEq, rust_rel8_derive::TableStruct)]
struct User<'scope, Mode: TableMode = ExprMode> {
    id: Mode::T<'scope, i32>,
    name: Mode::T<'scope, String>,
    age: Mode::T<'scope, i32>,
}

impl<'scope> User<'scope, NameMode> {
    const SCHEMA: TableSchema<Self> = TableSchema {
        name: "users",
        columns: User {
            id: "id",
            name: "name",
            age: "age",
        },
    };
}

#[derive(Debug, PartialEq, rust_rel8_derive::TableStruct)]
struct Post<'scope, Mode: TableMode = ExprMode> {
    id: Mode::T<'scope, i32>,
    user_id: Mode::T<'scope, i32>,
    contents: Mode::T<'scope, String>,
}

impl<'scope> Post<'scope, NameMode> {
    const SCHEMA: TableSchema<Self> = TableSchema {
        name: "posts",
        columns: Post {
            id: "id",
            user_id: "user_id",
            contents: "contents",
        },
    };
}

#[derive(rust_rel8_derive::TableStruct)]
#[perfect_derive::perfect_derive(Debug, PartialEq, Clone)]
struct Common<'scope, Mode: TableMode = ExprMode> {
    id: Mode::T<'scope, i32>,
    created: Mode::T<'scope, i32>,
}

#[derive(rust_rel8_derive::TableStruct)]
#[perfect_derive::perfect_derive(Debug, PartialEq, Clone)]
struct UsesCommon<'scope, Mode: TableMode = ExprMode> {
    name: Mode::T<'scope, String>,
    #[table(nested)]
    common: Common<'scope, Mode>,
}

#[test]
fn does_it_work() {
    let q = query::<(Expr<i32>, Expr<i32>)>(|q| {
        let a = q.q(Query::values([1, 2, 3].map(|a| One { a })));
        let b = q.q(Query::values([4, 5, 6].map(|a| One { a })));
        return (a.a, b.a);
    });
    drop(q);
}

#[async_std::test]
async fn no_queries() {
    let q = query::<Expr<i32>>(|_q| {
        return Expr::lit(1i32);
    });

    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(vec![1i32], rows)
}

fn with_number_matching<'a>(number: Expr<'a, i32>) -> Query<Expr<'a, i32>> {
    query::<Expr<i32>>(|q| {
        let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
        q.where_(x.clone().equals(number));
        return x.add(Expr::lit(1i32));
    })
}

#[async_std::test]
async fn test_each_with_inc() {
    let q = query::<(Expr<i32>, Expr<i32>)>(|q| {
        let a = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
        let b = q.q(with_number_matching(a.clone()));
        (a, b)
    });

    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(vec![(1, 2), (2, 3), (3, 4)], rows)
}

#[async_std::test]
async fn and_filters() {
    let q = query::<(Expr<i32>, Expr<i32>)>(|q| {
        let a = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
        let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
        q.where_(a.clone().equals(b.clone()));
        return (a, b);
    });

    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(vec![(2, 2), (3, 3)], rows)
}

#[async_std::test]
async fn double_nested() {
    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    let q = query::<((Expr<i32>, Expr<i32>), (Expr<i32>, Expr<i32>))>(|q| {
        let inner_q = query::<(Expr<i32>, Expr<i32>)>(|q| {
            let a = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
            let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
            q.where_(a.clone().equals(b.clone()));
            return (a, b);
        });

        let x = q.q(inner_q.clone());
        let y = q.q(inner_q);

        return (x, y);
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(
        vec![
            ((2, 2), (2, 2)),
            ((2, 2), (3, 3)),
            ((3, 3), (2, 2)),
            ((3, 3), (3, 3))
        ],
        rows
    )
}

#[async_std::test]
async fn optionals() {
    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    let q = query::<(MaybeTable<Expr<i32>>, Expr<i32>)>(|q| {
        let empty_query = query::<Expr<i32>>(|q| {
            let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
            q.where_(Expr::lit(false));
            x
        });
        let a = q.q(empty_query.optional());
        let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
        return (a, b);
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(vec![(None::<i32>, 2), (None, 3), (None, 4)], rows);

    let q = query::<(MaybeTable<Expr<i32>>, Expr<i32>)>(|q| {
        let empty_query = query::<Expr<i32>>(|q| {
            let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
            q.where_(Expr::lit(true));
            x
        });
        let a = q.q(empty_query.optional());
        let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
        return (a, b);
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(
        vec![
            (Some::<i32>(1), 2),
            (Some(1), 3),
            (Some(1), 4),
            (Some(2), 2),
            (Some(2), 3),
            (Some(2), 4),
            (Some(3), 2),
            (Some(3), 3),
            (Some(3), 4)
        ],
        rows
    );

    let q = query::<(Expr<Option<i32>>, Expr<i32>)>(|q| {
        let empty_query = query::<(Expr<i32>, Expr<i32>)>(|q| {
            let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
            q.where_(Expr::lit(false));
            (x.clone(), x)
        });
        let a = q.q(empty_query.optional()).project(|v| v.0.clone());
        let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
        return (a, b);
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(vec![(None::<i32>, 2), (None, 3), (None, 4)], rows);

    let q = query::<(Expr<Option<i32>>, Expr<i32>)>(|q| {
        let empty_query = query::<(Expr<i32>, Expr<i32>)>(|q| {
            let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
            q.where_(Expr::lit(true));
            (x.clone(), x)
        });
        let a = q.q(empty_query.optional()).project(|v| v.0.clone());
        let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
        return (a, b);
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(
        vec![
            (Some::<i32>(1), 2),
            (Some(1), 3),
            (Some(1), 4),
            (Some(2), 2),
            (Some(2), 3),
            (Some(2), 4),
            (Some(3), 2),
            (Some(3), 3),
            (Some(3), 4)
        ],
        rows
    );
}

#[async_std::test]
async fn optionals_maybe() {
    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    let q = query::<(Expr<i32>, Expr<i32>)>(|q| {
        let empty_query = query::<Expr<i32>>(|q| {
            let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
            q.where_(Expr::lit(false));
            x
        });
        let a = q.q(empty_query.optional()).maybe(Expr::lit(99), |t| t);
        let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
        return (a, b);
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(vec![(99, 2), (99, 3), (99, 4)], rows);

    let q = query::<(Expr<i32>, Expr<i32>)>(|q| {
        let empty_query = query::<Expr<i32>>(|q| {
            let x = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
            q.where_(Expr::lit(true));
            x
        });
        let a = q.q(empty_query.optional()).maybe(Expr::lit(99), |t| t);
        let b = q.q(Query::values([2, 3, 4].map(|a| One { a }))).a;
        return (a, b);
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(
        vec![
            (1, 2),
            (1, 3),
            (1, 4),
            (2, 2),
            (2, 3),
            (2, 4),
            (3, 2),
            (3, 3),
            (3, 4)
        ],
        rows
    );
}

#[async_std::test]
async fn my_table() {
    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    sqlx::query("drop table if exists users")
        .execute(&mut *pool)
        .await
        .unwrap();

    sqlx::query(
        "create table users (id INTEGER GENERATED BY DEFAULT AS IDENTITY, name TEXT, age INTEGER)",
    )
    .execute(&mut *pool)
    .await
    .unwrap();

    sqlx::query("insert into users values (DEFAULT, 'ben', 0), (DEFAULT, 'foo', 1)")
        .execute(&mut *pool)
        .await
        .unwrap();

    let q = query::<User<ExprMode>>(|q| {
        let user = q.q(Query::each(&User::SCHEMA));
        q.where_(user.name.clone().equals(Expr::lit("ben".to_string())));
        user
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(
        vec![User {
            id: 1i32,
            name: "ben".to_string(),
            age: 0
        }],
        rows
    )
}

#[async_std::test]
async fn nextval_stuff() {
    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    sqlx::query("drop table if exists nextval_test")
        .execute(&mut *pool)
        .await
        .unwrap();

    sqlx::query("create table nextval_test (id SERIAL PRIMARY KEY)")
        .execute(&mut *pool)
        .await
        .unwrap();

    let q = query::<(Expr<i32>, Expr<i32>, Expr<i32>)>(|q| {
        let a = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
        let b = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
        let c = q.q(query::<Expr<i32>>(|q| {
            q.q(Query::evaluate(Expr::nextval("nextval_test_id_seq")))
        }));
        (a, b, c)
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(
        vec![
            (1, 1, 1),
            (1, 2, 2),
            (1, 3, 3),
            (2, 1, 4),
            (2, 2, 5),
            (2, 3, 6),
            (3, 1, 7),
            (3, 2, 8),
            (3, 3, 9)
        ],
        rows
    );

    // check that it fails if we forget eval
    sqlx::query("drop table if exists nextval_test")
        .execute(&mut *pool)
        .await
        .unwrap();

    sqlx::query("create table nextval_test (id SERIAL PRIMARY KEY)")
        .execute(&mut *pool)
        .await
        .unwrap();

    let q = query::<(Expr<i32>, Expr<i32>, Expr<i32>)>(|q| {
        let a = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
        let b = q.q(Query::values([1, 2, 3].map(|a| One { a }))).a;
        let c = q.q(query::<Expr<i32>>(|_| Expr::nextval("nextval_test_id_seq")));
        (a, b, c)
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(
        vec![
            (1, 1, 1),
            (1, 2, 1),
            (1, 3, 1),
            (2, 1, 1),
            (2, 2, 1),
            (2, 3, 1),
            (3, 1, 1),
            (3, 2, 1),
            (3, 3, 1)
        ],
        rows
    );
}

#[async_std::test]
async fn join_example() {
    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    let demo_users = vec![
        User::<ValueMode> {
            id: 0,
            name: "Undine".to_owned(),
            age: 3,
        },
        User {
            id: 1,
            name: "Leschy".to_owned(),
            age: 3,
        },
        User {
            id: 2,
            name: "Huldra".to_owned(),
            age: 2,
        },
    ];

    fn posts_of_user(user_id: Expr<i32>) -> Query<Post> {
        let demo_posts = vec![
            Post::<ValueMode> {
                id: 0,
                user_id: 0,
                contents: "Croak".to_owned(),
            },
            Post {
                id: 1,
                user_id: 1,
                contents: "Quak!".to_owned(),
            },
            Post {
                id: 2,
                user_id: 1,
                contents: "Hello".to_owned(),
            },
        ];

        query::<Post<ExprMode>>(|q| {
            let post = q.q(Query::values(demo_posts.shorten_lifetime()));
            q.where_(user_id.equals(post.user_id.clone()));
            post
        })
    }

    let q = query::<(Expr<String>, Expr<String>)>(|q| {
        let user = q.q(Query::values(demo_users.shorten_lifetime()));
        let post = q.q(posts_of_user(user.id.clone()));

        (user.name, post.contents)
    })
    .order_by(|x| (x.clone(), sea_query::Order::Asc));

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(
        vec![
            ("Leschy".to_owned(), "Hello".to_owned()),
            ("Leschy".to_owned(), "Quak!".to_owned()),
            ("Undine".to_owned(), "Croak".to_owned())
        ],
        rows
    )
}

#[async_std::test]
async fn nested() {
    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    let demo_things = vec![
        UsesCommon::<ValueMode> {
            name: "Undine".to_owned(),
            common: Common { id: 1, created: 0 },
        },
        UsesCommon::<ValueMode> {
            name: "Leschy".to_owned(),
            common: Common { id: 2, created: 0 },
        },
        UsesCommon::<ValueMode> {
            name: "Huldra".to_owned(),
            common: Common { id: 3, created: 0 },
        },
    ];

    let q = query::<(UsesCommon, Common)>(|q| {
        let thing = q.q(Query::values(demo_things.shorten_lifetime()));
        let common = thing.common.clone();
        (thing, common)
    })
    .order_by(|(_, common)| (common.id.clone(), sea_query::Order::Asc));

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(
        vec![
            (
                UsesCommon {
                    name: "Undine".to_owned(),
                    common: Common { id: 1, created: 0 }
                },
                Common { id: 1, created: 0 }
            ),
            (
                UsesCommon {
                    name: "Leschy".to_owned(),
                    common: Common { id: 2, created: 0 }
                },
                Common { id: 2, created: 0 }
            ),
            (
                UsesCommon {
                    name: "Huldra".to_owned(),
                    common: Common { id: 3, created: 0 }
                },
                Common { id: 3, created: 0 }
            )
        ],
        rows
    )
}

#[async_std::test]
async fn aggregations_simple() {
    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    let q = query::<Expr<i32>>(|q| {
        let a = q.q(Query::values([1, 1, 2, 2, 2, 3].map(|a| One { a }))).a;
        a
    })
    .aggregate::<ListTable<Expr<i32>>>(|a, e| {
        let _e = a.group_by(e.clone());
        let as_array = a.array_agg(e);
        as_array
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(vec![vec![1, 1], vec![3], vec![2, 2, 2]], rows);

    let q = query::<Two<_, i32, i32>>(|q| {
        let a = q.q(Query::values([
            Two { a: 1, b: 1 },
            Two { a: 1, b: 2 },
            Two { a: 1, b: 3 },
            Two { a: 1, b: 4 },
            Two { a: 2, b: 1 },
            Two { a: 2, b: 2 },
            Two { a: 3, b: 1 },
        ]));
        a
    })
    .aggregate::<(Expr<i32>, ListTable<Expr<i32>>, ListTable<Two<_, i32, i32>>)>(|a, e| {
        let x = a.group_by(e.a.clone());
        let y = a.array_agg(e.a.clone().add(Expr::lit(1i32)));
        let as_array = a.array_agg(e);
        (x, y, as_array)
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(
        vec![
            (
                1,
                vec![2, 2, 2, 2],
                vec![
                    Two { a: 1, b: 4 },
                    Two { a: 1, b: 3 },
                    Two { a: 1, b: 2 },
                    Two { a: 1, b: 1 }
                ]
            ),
            (3, vec![4], vec![Two { a: 3, b: 1 }]),
            (2, vec![3, 3], vec![Two { a: 2, b: 2 }, Two { a: 2, b: 1 }])
        ],
        rows
    );
}

#[async_std::test]
async fn windows_simple() {
    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    let q = query::<Two<_, i32, i32>>(|q| {
        let a = q.q(Query::values([
            Two { a: 1, b: 1 },
            Two { a: 1, b: 2 },
            Two { a: 1, b: 3 },
            Two { a: 1, b: 4 },
            Two { a: 2, b: 1 },
            Two { a: 2, b: 2 },
            Two { a: 3, b: 1 },
        ]));
        a
    })
    .window::<(Expr<i64>, Two<_, i32, i32>, Two<_, i32, i32>)>(|a, e| {
        let over = Over::new().partition_by(e.a.clone());
        let row_num = a.row_number(over.clone());
        let id_e = a.id(e.clone());
        let lagged_e = a.lag(e, None, Some(Two { a: 0, b: 0 }.lit()), over);

        (row_num, id_e, lagged_e)
    });

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(
        vec![
            (1, Two { a: 1, b: 1 }, Two { a: 0, b: 0 }),
            (2, Two { a: 1, b: 2 }, Two { a: 1, b: 1 }),
            (3, Two { a: 1, b: 3 }, Two { a: 1, b: 2 }),
            (4, Two { a: 1, b: 4 }, Two { a: 1, b: 3 }),
            (1, Two { a: 2, b: 1 }, Two { a: 0, b: 0 }),
            (2, Two { a: 2, b: 2 }, Two { a: 2, b: 1 }),
            (1, Two { a: 3, b: 1 }, Two { a: 0, b: 0 })
        ],
        rows
    );
}

#[async_std::test]
async fn join_example_many() {
    let db_string = format!(
        "postgresql://{}@postgres?host={}",
        std::env::var("USER").unwrap(),
        std::env::var("PGHOST").unwrap(),
    );

    let connection = PgPool::connect(&db_string).await.unwrap();
    let mut pool = connection.try_acquire().unwrap();

    let demo_users = vec![
        User::<ValueMode> {
            id: 0,
            name: "Undine".to_owned(),
            age: 3,
        },
        User {
            id: 1,
            name: "Leschy".to_owned(),
            age: 3,
        },
        User {
            id: 2,
            name: "Huldra".to_owned(),
            age: 2,
        },
    ];

    fn posts_of_user(user_id: Expr<i32>) -> Query<Expr<String>> {
        let demo_posts = vec![
            Post::<ValueMode> {
                id: 0,
                user_id: 0,
                contents: "Croak".to_owned(),
            },
            Post {
                id: 1,
                user_id: 1,
                contents: "Quak!".to_owned(),
            },
            Post {
                id: 2,
                user_id: 1,
                contents: "Hello".to_owned(),
            },
        ];

        query::<Expr<String>>(|q| {
            let post = q.q(Query::values(demo_posts.shorten_lifetime()));
            q.where_(user_id.equals(post.user_id.clone()));
            post.contents
        })
    }

    let q = query::<(Expr<String>, ListTable<Expr<String>>)>(|q| {
        let user = q.q(Query::values(demo_users.shorten_lifetime()));
        let posts = q.q(posts_of_user(user.id.clone()).many());

        (user.name, posts)
    })
    .order_by(|x| (x.0.clone(), sea_query::Order::Asc));

    let rows = q.all(&mut *pool).await.unwrap();

    assert_eq!(
        vec![
            ("Huldra".to_owned(), vec![]),
            (
                "Leschy".to_owned(),
                vec!["Quak!".to_owned(), "Hello".to_owned()]
            ),
            ("Undine".to_owned(), vec!["Croak".to_owned()])
        ],
        rows
    )
}