use renox::db::relations::{count_many, sum_many};
use renox::prelude::*;
use renox::testing::TestApp;
#[derive(Model, serde::Serialize, Default, Clone, Debug)]
#[model(table = "customers")]
struct Customer {
id: i64,
name: String,
}
#[derive(Model, serde::Serialize, Default, Clone, Debug)]
#[model(table = "orders")]
struct Order {
id: i64,
customer_id: i64,
total: i64,
status: String,
}
async fn shop() -> TestApp {
let app = TestApp::new(App::new()).await;
let id = match app.db().dialect() {
renox::db::Dialect::Postgres => "BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY",
_ => "INTEGER PRIMARY KEY",
};
for statement in [
format!("CREATE TABLE customers (id {id}, name TEXT NOT NULL)"),
format!(
"CREATE TABLE orders (id {id}, customer_id BIGINT NOT NULL, total BIGINT NOT NULL, \
status TEXT NOT NULL)"
),
"INSERT INTO customers (name) VALUES ('Ana'), ('Bo'), ('Cy')".to_owned(),
"INSERT INTO orders (customer_id, total, status) VALUES \
(1, 100, 'paid'), (1, 250, 'paid'), (1, 50, 'cancelled'), (2, 900, 'paid')"
.to_owned(),
] {
renox::db::sql(statement).execute(app.db()).await.unwrap();
}
app
}
#[renox::test]
async fn raw_fragments_grouping_and_to_sql() {
let app = shop().await;
let db = app.db();
let big: Vec<i64> = Order::query()
.where_raw("total * 2 > ?", [300])
.order_by_raw("total DESC")
.pluck(db, "total")
.await
.unwrap();
assert_eq!(big, [900, 250]);
let per_customer: Vec<(i64, i64)> = Order::where_eq("status", "paid")
.group_by("customer_id")
.having_raw("SUM(total) > ?", [300])
.order_by_raw("1")
.select_as(db, "customer_id, CAST(SUM(total) AS BIGINT)")
.await
.unwrap();
assert_eq!(per_customer, [(1, 350), (2, 900)]);
let groups = Order::query()
.group_by("customer_id")
.count(db)
.await
.unwrap();
assert_eq!(groups, 2);
let (sql, binds) = Order::where_eq("status", "paid")
.where_raw("total > ?", [10])
.to_sql(db.dialect())
.unwrap();
assert!(
sql.contains(r#"WHERE "status" = ? AND (total > ?)"#),
"{sql}"
);
assert_eq!(binds.len(), 2);
assert!(
Order::query()
.group_by("nope")
.to_sql(db.dialect())
.is_err()
);
}
#[renox::test]
async fn exists_and_not_in_subqueries() {
let app = shop().await;
let db = app.db();
let names = |q: renox::db::Query<Customer>| {
let db = db.clone();
async move {
q.order_by("name")
.pluck::<String, _>(&db, "name")
.await
.unwrap()
}
};
assert_eq!(
names(Customer::query().where_has(Order::query(), "customer_id")).await,
["Ana", "Bo"]
);
assert_eq!(
names(Customer::query().where_has(Order::where_eq("status", "cancelled"), "customer_id"))
.await,
["Ana"]
);
assert_eq!(
names(Customer::query().where_doesnt_have(Order::query(), "customer_id")).await,
["Cy"]
);
assert_eq!(
names(Customer::query().where_not_in_query(
"id",
Order::query().where_op("total", ">", 500),
"customer_id"
))
.await,
["Ana", "Cy"]
);
}
#[renox::test]
async fn counts_and_sums_per_parent() {
let app = shop().await;
let db = app.db();
let customers = Customer::query().order_by("id").get(db).await.unwrap();
let orders = count_many(db, &customers, Order::query(), "customer_id")
.await
.unwrap();
assert_eq!((orders[&1], orders[&2], orders[&3]), (3, 1, 0));
let paid = sum_many::<i64, _, _>(
db,
&customers,
Order::where_eq("status", "paid"),
"customer_id",
"total",
)
.await
.unwrap();
assert_eq!((paid[&1], paid[&2], paid[&3]), (350, 900, 0));
assert!(
sum_many::<i64, _, _>(db, &customers, Order::query(), "customer_id", "nope")
.await
.is_err()
);
}
#[renox::test]
async fn simple_and_cursor_pages() {
let app = shop().await;
let db = app.db();
let first = Order::query()
.order_by("id")
.simple_paginate(db, 1, 3)
.await
.unwrap();
assert_eq!(
(first.items.len(), first.has_prev, first.has_next),
(3, false, true)
);
let last = Order::query()
.order_by("id")
.simple_paginate(db, 2, 3)
.await
.unwrap();
assert_eq!(
(last.items.len(), last.has_prev, last.has_next),
(1, true, false)
);
let page = Order::query().cursor_paginate(db, None, 3).await.unwrap();
let ids: Vec<i64> = page.items.iter().map(|o| o.id).collect();
assert_eq!(ids, [4, 3, 2]);
Order::create(
db,
Order {
customer_id: 3,
total: 1,
status: "paid".into(),
..Default::default()
},
)
.await
.unwrap();
let next = Order::query()
.cursor_paginate(db, page.next_cursor.as_deref(), 3)
.await
.unwrap();
let ids: Vec<i64> = next.items.iter().map(|o| o.id).collect();
assert_eq!((ids, next.next_cursor), (vec![1], None));
assert!(
Order::query()
.cursor_paginate(db, Some("x"), 3)
.await
.is_err()
);
}
#[renox::test]
async fn first_or_new_update_or_create_and_refresh() {
let app = shop().await;
let db = app.db();
let dy = Customer::where_eq("name", "Dy")
.first_or_new(db, || Customer {
name: "Dy".into(),
..Default::default()
})
.await
.unwrap();
assert_eq!(dy.id, 0, "not saved");
let ana = Customer::where_eq("name", "Ana")
.first_or_new(db, Customer::default)
.await
.unwrap();
assert_eq!(ana.id, 1);
let order = Order::where_eq("customer_id", 3)
.update_or_create(
db,
|| Order {
customer_id: 3,
status: "new".into(),
..Default::default()
},
|o| o.total = 70,
)
.await
.unwrap();
assert!(order.id > 0);
let again = Order::where_eq("customer_id", 3)
.update_or_create(db, Order::default, |o| o.total = 80)
.await
.unwrap();
assert_eq!((again.id, again.total), (order.id, 80));
let mut stale = Order::find_or_404(db, order.id).await.unwrap();
Order::where_eq("id", order.id)
.increment(db, "total", 5)
.await
.unwrap();
stale.refresh(db).await.unwrap();
assert_eq!(stale.total, 85);
}
#[renox::test]
async fn transactions_commit_or_roll_back() {
let app = shop().await;
let db = app.db();
let moved: i64 = db
.transaction(|tx| {
Box::pin(async move {
renox::db::sql("UPDATE orders SET total = total - 50 WHERE id = 2")
.execute(&mut *tx)
.await?;
renox::db::sql("UPDATE orders SET total = total + 50 WHERE id = 3")
.execute(&mut *tx)
.await?;
Ok(50)
})
})
.await
.unwrap();
assert_eq!(moved, 50);
assert_eq!(Order::find_or_404(db, 3).await.unwrap().total, 100);
let mut attempts = 0;
let failed: Result<()> = db
.transaction_retrying(3, |tx| {
attempts += 1;
Box::pin(async move {
renox::db::sql("UPDATE orders SET total = 0")
.execute(&mut *tx)
.await?;
Err(abort(StatusCode::CONFLICT, "not enough stock"))
})
})
.await;
assert!(failed.is_err());
assert_eq!(attempts, 1, "a non-conflict error isn't retried");
assert_eq!(
Order::find_or_404(db, 3).await.unwrap().total,
100,
"rolled back"
);
let mut tx = db.begin_immediate().await.unwrap();
let locked = Order::where_eq("id", 1)
.lock_for_update()
.first(&mut tx)
.await
.unwrap();
assert_eq!(locked.map(|o| o.total), Some(100));
let (sql, _) = Order::query().shared_lock().to_sql(db.dialect()).unwrap();
assert_eq!(
sql.ends_with("FOR SHARE"),
db.dialect() == renox::db::Dialect::Postgres
);
tx.commit().await.unwrap();
}