use renox::db::Migration;
use renox::db::relations::{Pivot, belongs_to, has_many};
use renox::prelude::*;
use renox::testing::TestApp;
const SCHEMA: Migration = Migration::new("20300101000000_shop", "", None).sqlite("CREATE TABLE categories (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, active INTEGER NOT NULL DEFAULT 1);
CREATE TABLE products (id INTEGER PRIMARY KEY AUTOINCREMENT, category_id INTEGER REFERENCES categories (id),
sku TEXT NOT NULL UNIQUE, name TEXT NOT NULL, price INTEGER NOT NULL, weight REAL NOT NULL DEFAULT 0,
stock INTEGER NOT NULL DEFAULT 0, created_at TEXT, updated_at TEXT);
CREATE TABLE reviews (id INTEGER PRIMARY KEY AUTOINCREMENT, product_id INTEGER NOT NULL REFERENCES products (id), stars INTEGER NOT NULL);
CREATE TABLE tags (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL);
CREATE TABLE product_tags (product_id INTEGER NOT NULL, tag_id INTEGER NOT NULL, UNIQUE (product_id, tag_id));", None).postgres("CREATE TABLE categories (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name TEXT NOT NULL, active BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE products (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, category_id BIGINT REFERENCES categories (id),
sku TEXT NOT NULL UNIQUE, name TEXT NOT NULL, price BIGINT NOT NULL, weight DOUBLE PRECISION NOT NULL DEFAULT 0,
stock BIGINT NOT NULL DEFAULT 0, created_at TIMESTAMPTZ, updated_at TIMESTAMPTZ);
CREATE TABLE reviews (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, product_id BIGINT NOT NULL REFERENCES products (id), stars BIGINT NOT NULL);
CREATE TABLE tags (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE product_tags (product_id BIGINT NOT NULL, tag_id BIGINT NOT NULL, UNIQUE (product_id, tag_id));", None);
#[derive(Model, serde::Serialize, Default, Debug, Clone, PartialEq)]
#[model(table = "categories")]
struct Category {
id: i64,
name: String,
active: bool,
}
#[derive(Model, serde::Serialize, Default, Debug, Clone, PartialEq)]
#[model(table = "products")]
struct Product {
id: i64,
category_id: Option<i64>,
sku: String,
name: String,
price: i64,
weight: f64,
stock: i64,
created_at: Option<DateTime>,
updated_at: Option<DateTime>,
}
#[derive(Model, serde::Serialize, Default, Debug, Clone)]
#[model(table = "reviews")]
struct Review {
id: i64,
product_id: i64,
stars: i64,
}
#[derive(Model, serde::Serialize, Default, Debug, Clone)]
#[model(table = "tags")]
struct Tag {
id: i64,
name: String,
}
const PRODUCT_TAGS: Pivot = Pivot::new("product_tags", "product_id", "tag_id");
fn product(sku: &str, name: &str, price: i64, category_id: Option<i64>) -> Product {
Product {
sku: sku.into(),
name: name.into(),
price,
category_id,
weight: price as f64 / 1000.0,
..Default::default()
}
}
async fn shop() -> (TestApp, Category, Category) {
let app = TestApp::new(App::new().migrations(&[SCHEMA])).await;
let db = app.db();
let coffee = Category::create(
db,
Category {
name: "Coffee".into(),
active: true,
..Default::default()
},
)
.await
.unwrap();
let tea = Category::create(
db,
Category {
name: "Tea".into(),
active: false,
..Default::default()
},
)
.await
.unwrap();
for p in [
product("K1", "Milk Coffee", 18_000, Some(coffee.id)),
product("K2", "Black Coffee", 15_000, Some(coffee.id)),
product("T1", "Pulled Tea", 12_000, Some(tea.id)),
product("A1", "Mineral Water", 5_000, None),
] {
Product::create(db, p).await.unwrap();
}
(app, coffee, tea)
}
fn names(products: &[Product]) -> Vec<&str> {
products.iter().map(|p| p.name.as_str()).collect()
}
#[renox::test]
async fn grouped_conditions_and_optional_filters() {
let (app, coffee, _) = shop().await;
let db = app.db();
let found = Product::query()
.where_eq("category_id", coffee.id)
.where_any(|q| {
q.where_op("price", "<", 16_000)
.where_like("name", "%MILK%")
})
.order_by("name")
.get(db)
.await
.unwrap();
assert_eq!(names(&found), ["Black Coffee", "Milk Coffee"]);
let found = Product::query()
.where_any(|q| {
q.where_all(|q| {
q.where_op("price", ">=", 15_000)
.where_op("price", "<=", 16_000)
})
.where_null("category_id")
})
.order_by("name")
.get(db)
.await
.unwrap();
assert_eq!(names(&found), ["Black Coffee", "Mineral Water"]);
for (search, expected) in [("", 4), ("coffee", 2)] {
let found = Product::query()
.when(!search.is_empty(), |q| {
q.where_like("name", format!("%{search}%"))
})
.count(db)
.await
.unwrap();
assert_eq!(found, expected, "search {search:?}");
}
assert_eq!(
Product::query()
.where_between("price", 12_000, 15_000)
.count(db)
.await
.unwrap(),
2
);
assert_eq!(
Product::query()
.where_not_in("sku", ["K1", "K2"])
.count(db)
.await
.unwrap(),
2
);
assert_eq!(
Product::query()
.where_not_in("sku", Vec::<String>::new())
.count(db)
.await
.unwrap(),
4,
"NOT IN an empty list keeps everything"
);
assert_eq!(
Product::query().where_any(|q| q).count(db).await.unwrap(),
0
);
assert!(
Product::query()
.where_any(|q| q.where_eq("nope", 1))
.get(db)
.await
.is_err(),
"unknown columns inside groups are errors too"
);
let active = Product::query()
.where_in_query("category_id", Category::where_eq("active", true), "id")
.order_by("name")
.get(db)
.await
.unwrap();
assert_eq!(names(&active), ["Black Coffee", "Milk Coffee"]);
}
#[renox::test]
async fn aggregates_and_pluck() {
let (app, coffee, _) = shop().await;
let db = app.db();
assert_eq!(
Product::query().sum::<i64, _>(db, "price").await.unwrap(),
50_000
);
let weight = Product::query().sum::<f64, _>(db, "weight").await.unwrap();
assert!((weight - 50.0).abs() < 1e-9, "{weight}");
let avg = Product::where_eq("category_id", coffee.id)
.avg(db, "price")
.await
.unwrap();
assert_eq!(avg, Some(16_500.0));
assert_eq!(
Product::query().min::<i64, _>(db, "price").await.unwrap(),
Some(5_000)
);
assert_eq!(
Product::query().max::<String, _>(db, "name").await.unwrap(),
Some("Pulled Tea".into())
);
let none = Product::where_eq("sku", "nope");
assert_eq!(none.clone().sum::<i64, _>(db, "price").await.unwrap(), 0);
assert_eq!(none.clone().max::<i64, _>(db, "price").await.unwrap(), None);
assert_eq!(none.avg(db, "price").await.unwrap(), None);
let skus: Vec<String> = Product::query()
.order_by_desc("price")
.limit(2)
.pluck(db, "sku")
.await
.unwrap();
assert_eq!(skus, ["K1", "K2"]);
assert!(
Product::query()
.pluck::<String, _>(db, "nope")
.await
.is_err()
);
}
#[renox::test]
async fn bulk_updates_and_counters() {
let (app, coffee, _) = shop().await;
let db = app.db();
let before = Product::where_eq("sku", "K1")
.first_or_404(db)
.await
.unwrap();
let changed = Product::where_eq("category_id", coffee.id)
.update(db, &[("stock", &10_i64), ("name", &"Coffee")])
.await
.unwrap();
assert_eq!(changed, 2);
let after = Product::where_eq("sku", "K1")
.first_or_404(db)
.await
.unwrap();
assert_eq!((after.stock, after.name.as_str()), (10, "Coffee"));
assert!(after.updated_at >= before.updated_at);
Product::where_eq("sku", "K1")
.increment(db, "stock", -3)
.await
.unwrap();
Product::where_eq("sku", "K1")
.increment(db, "stock", 1)
.await
.unwrap();
assert_eq!(
Product::where_eq("sku", "K1")
.first_or_404(db)
.await
.unwrap()
.stock,
8
);
assert!(
Product::query()
.update(db, &[("id", &1_i64)])
.await
.is_err()
);
assert!(
Product::query()
.update(db, &[("nope", &1_i64)])
.await
.is_err()
);
let missing = Product::where_eq("sku", "nope")
.first_or_404(db)
.await
.unwrap_err();
assert_eq!(missing.status(), StatusCode::NOT_FOUND);
}
#[renox::test]
async fn first_or_create_chunk_insert_many_and_upsert() {
let (app, _, _) = shop().await;
let db = app.db();
let made = Product::where_eq("sku", "N1")
.first_or_create(db, || product("N1", "Rice", 20_000, None))
.await
.unwrap();
let again = Product::where_eq("sku", "N1")
.first_or_create(db, || product("N1", "Other", 1, None))
.await
.unwrap();
assert_eq!((made.id, again.name.as_str()), (again.id, "Rice"));
let many: Vec<Product> = (0..2500)
.map(|i| product(&format!("B{i:04}"), &format!("Bulk {i}"), 1_000 + i, None))
.collect();
assert_eq!(Product::insert_many(db, many).await.unwrap(), 2500);
assert_eq!(Product::query().count(db).await.unwrap(), 2505);
assert!(
Product::where_eq("sku", "B0001")
.first_or_404(db)
.await
.unwrap()
.created_at
.is_some()
);
let mut batches = Vec::new();
let seen = Product::query()
.where_like("sku", "B%")
.chunk(db, 1000, |rows| {
batches.push(rows.len());
async { Ok(()) }
})
.await
.unwrap();
assert_eq!((seen, batches), (2500, vec![1000, 1000, 500]));
let feed = vec![
product("K1", "Large Milk Coffee", 22_000, None),
product("Z9", "New", 9_000, None),
];
Product::upsert(db, feed, &["sku"], &["name", "price"])
.await
.unwrap();
let k1 = Product::where_eq("sku", "K1")
.first_or_404(db)
.await
.unwrap();
assert_eq!((k1.name.as_str(), k1.price), ("Large Milk Coffee", 22_000));
assert!(k1.category_id.is_some(), "columns not in `update` are kept");
assert!(Product::where_eq("sku", "Z9").exists(db).await.unwrap());
assert!(
Product::upsert(db, Vec::new(), &["nope"], &[])
.await
.is_err()
);
}
#[derive(renox::FromRow, Debug, PartialEq)]
struct CategorySales {
category: String,
products: i64,
#[row(rename = "revenue")]
total: i64,
#[row(skip)]
note: String,
}
#[renox::test]
async fn fetch_as_reads_joins_into_structs_and_tuples() {
let (app, _, _) = shop().await;
let db = app.db();
let sales: Vec<CategorySales> = renox::db::sql(
"SELECT c.name AS category, COUNT(p.id) AS products, CAST(SUM(p.price) AS BIGINT) AS revenue \
FROM categories c JOIN products p ON p.category_id = c.id GROUP BY c.name ORDER BY c.name",
)
.fetch_as(db)
.await
.unwrap();
assert_eq!(
sales,
[
CategorySales {
category: "Coffee".into(),
products: 2,
total: 33_000,
note: String::new()
},
CategorySales {
category: "Tea".into(),
products: 1,
total: 12_000,
note: String::new()
},
]
);
let pairs: Vec<(String, i64)> = renox::db::sql("SELECT sku, price FROM products ORDER BY sku")
.fetch_as(db)
.await
.unwrap();
assert_eq!(pairs[0], ("A1".to_owned(), 5_000));
let model: Option<Product> = renox::db::sql("SELECT * FROM products WHERE sku = ?")
.bind("K2")
.fetch_optional_as(db)
.await
.unwrap();
assert_eq!(model.unwrap().name, "Black Coffee");
}
#[renox::test]
async fn relations_load_a_page_without_n_plus_one() {
let (app, coffee, tea) = shop().await;
let db = app.db();
let products = Product::query().order_by("sku").get(db).await.unwrap();
let categories = belongs_to::<Category, _, _>(db, &products, |p| p.category_id)
.await
.unwrap();
assert_eq!(categories.len(), 2);
assert_eq!(categories[&coffee.id].name, "Coffee");
assert_eq!(categories[&tea.id].name, "Tea");
let k1 = products.iter().find(|p| p.sku == "K1").unwrap().id;
for stars in [5, 3, 4] {
Review::create(
db,
Review {
product_id: k1,
stars,
..Default::default()
},
)
.await
.unwrap();
}
let reviews = has_many(
db,
&products,
Review::query().order_by_desc("stars"),
"product_id",
|r| r.product_id,
)
.await
.unwrap();
let stars: Vec<i64> = reviews[&k1].iter().map(|r| r.stars).collect();
assert_eq!(stars, [5, 4, 3]);
assert!(!reviews.contains_key(&products[0].id));
let tags: Vec<Tag> = {
let mut out = Vec::new();
for name in ["hot", "sweet", "iced"] {
out.push(
Tag::create(
db,
Tag {
name: name.into(),
..Default::default()
},
)
.await
.unwrap(),
);
}
out
};
let (hot, sweet, iced) = (tags[0].id, tags[1].id, tags[2].id);
assert_eq!(
PRODUCT_TAGS
.attach(db, k1, [hot, sweet, hot])
.await
.unwrap(),
2
);
assert_eq!(PRODUCT_TAGS.ids(db, k1).await.unwrap(), [hot, sweet]);
PRODUCT_TAGS.sync(db, k1, [sweet, iced]).await.unwrap();
assert_eq!(PRODUCT_TAGS.ids(db, k1).await.unwrap(), [sweet, iced]);
assert_eq!(PRODUCT_TAGS.detach(db, k1, [iced]).await.unwrap(), 1);
PRODUCT_TAGS
.attach(db, products[0].id, [sweet])
.await
.unwrap();
let loaded = PRODUCT_TAGS
.load_for::<Tag, _>(db, &products)
.await
.unwrap();
assert_eq!(
loaded[&k1]
.iter()
.map(|t| t.name.as_str())
.collect::<Vec<_>>(),
["sweet"]
);
let tagged = PRODUCT_TAGS
.inverse()
.load::<Product>(db, [sweet])
.await
.unwrap();
assert_eq!(tagged[&sweet].len(), 2);
}