#![cfg(any(feature = "sqlite", feature = "postgres", feature = "mysql"))]
use crate::{
Column, Describe, EdgeShape, EdgeTable, FieldName, RecordAddress, Shadow, Table, TypeVisitor,
query::sql::Create,
};
struct Reel;
const TAGS: &[&FieldName] = &[FieldName::new("tags")];
const TAGS_SORT: &FieldName = FieldName::new("__ig_tags_sort__");
const REEL: RecordAddress = RecordAddress::new(FieldName::new("reel"), &[FieldName::new("id")]);
#[cfg(feature = "sqlite")]
impl Describe<sqlx::Sqlite> for Reel {
const BLUEPRINT: Table = Table::new(
REEL,
&[
Column::new(FieldName::new("id"), "INTEGER", false),
Column::new(FieldName::new("title"), "TEXT", false),
],
)
.with_edges(&[EdgeTable::new(TAGS, EdgeShape::Sequence, "TEXT")])
.with_shadows(&[Shadow::new(TAGS_SORT, TAGS)]);
}
#[cfg(feature = "postgres")]
impl Describe<sqlx::Postgres> for Reel {
const BLUEPRINT: Table = Table::new(
REEL,
&[
Column::new(FieldName::new("id"), "bigint", false),
Column::new(FieldName::new("title"), "text", false),
],
)
.with_edges(&[EdgeTable::new(TAGS, EdgeShape::Sequence, "text")])
.with_shadows(&[Shadow::new(TAGS_SORT, TAGS)]);
}
#[cfg(feature = "mysql")]
impl Describe<sqlx::MySql> for Reel {
const BLUEPRINT: Table = Table::new(
REEL,
&[
Column::new(FieldName::new("id"), "BIGINT", false),
Column::new(FieldName::new("title"), "VARCHAR(255)", false),
],
)
.with_edges(&[EdgeTable::new(TAGS, EdgeShape::Sequence, "VARCHAR(255)")])
.with_shadows(&[Shadow::new(TAGS_SORT, TAGS)]);
}
fn type_iter<V>(v: &mut V) -> Result<(), V::Error>
where
V: TypeVisitor,
Reel: Describe<V::Db>,
{
v.next_type::<Reel>()
}
fn schema<Db>() -> Vec<String>
where
Db: super::AsyncSchemaDialect,
Reel: Describe<Db>,
{
let mut create = Create::<Db>::new();
type_iter(&mut create).expect("this descriptor renders");
create.statements().to_vec()
}
#[cfg(feature = "sqlite")]
#[test]
fn sqlite_creates_a_blob_companion_and_indexes_it() {
assert_eq!(
schema::<sqlx::Sqlite>(),
[
r#"CREATE TABLE IF NOT EXISTS "reel" ("id" INTEGER NOT NULL, "title" TEXT NOT NULL, "__ig_tags_sort__" BLOB NOT NULL, PRIMARY KEY ("id"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_reel___ig_tags_sort__" ON "reel" ("__ig_tags_sort__")"#,
r#"CREATE TABLE IF NOT EXISTS "reel_tags" ("id" INTEGER NOT NULL, "tags_ordinal" INTEGER NOT NULL, "tags" TEXT NOT NULL, PRIMARY KEY ("id", "tags_ordinal"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_reel_tags_element" ON "reel_tags" ("id", "tags")"#,
]
);
}
#[cfg(feature = "postgres")]
#[test]
fn postgres_creates_a_bytea_companion_and_indexes_it() {
assert_eq!(
schema::<sqlx::Postgres>(),
[
r#"CREATE TABLE IF NOT EXISTS "reel" ("id" bigint NOT NULL, "title" text NOT NULL, "__ig_tags_sort__" bytea NOT NULL, PRIMARY KEY ("id"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_reel___ig_tags_sort__" ON "reel" ("__ig_tags_sort__")"#,
r#"CREATE TABLE IF NOT EXISTS "reel_tags" ("id" bigint NOT NULL, "tags_ordinal" bigint NOT NULL, "tags" text NOT NULL, PRIMARY KEY ("id", "tags_ordinal"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_reel_tags_element" ON "reel_tags" ("id", "tags")"#,
]
);
}
#[cfg(feature = "mysql")]
#[test]
fn mysql_creates_the_companion_and_declines_the_index() {
assert_eq!(
schema::<sqlx::MySql>(),
[
r"CREATE TABLE IF NOT EXISTS `reel` (`id` BIGINT NOT NULL, `title` VARCHAR(255) NOT NULL, `__ig_tags_sort__` BLOB NOT NULL, PRIMARY KEY (`id`))",
r"CREATE TABLE IF NOT EXISTS `reel_tags` (`id` BIGINT NOT NULL, `tags_ordinal` BIGINT NOT NULL, `tags` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`, `tags_ordinal`))",
r"CREATE INDEX IF NOT EXISTS `__ig_reel_tags_element` ON `reel_tags` (`id`, `tags`)",
]
);
}
#[cfg(feature = "sqlite")]
mod live {
use sqlx::{Connection as _, Executor as _, Row as _, sqlite::SqliteConnection};
use super::type_iter;
use crate::{
FieldName, OrderDirection, OrderTerm, TermName, Walk, collection_sort_key,
comparison::sql::{QueryBuilder, SelectClause, SqlOrder},
query::{
compare_collections,
sql::{Create, Validate},
},
};
fn corpus() -> Vec<(i64, Vec<String>)> {
let reel = |id: i64, tags: &[&str]| (id, tags.iter().map(|tag| (*tag).to_owned()).collect());
vec![
reel(1, &["b"]),
reel(2, &[]),
reel(3, &["a", "b"]),
reel(4, &["a"]),
reel(5, &["a", "a", "a"]),
reel(6, &["10"]),
reel(7, &["2"]),
reel(8, &["ab"]),
]
}
async fn written() -> SqliteConnection {
let mut db = SqliteConnection::connect("sqlite::memory:")
.await
.expect("an in-memory database");
let mut create = Create::<sqlx::Sqlite>::new();
type_iter(&mut create).expect("this descriptor renders");
create.run(&mut db).await.expect("the schema is created");
for (id, tags) in corpus() {
sqlx::query(r#"INSERT INTO "reel" VALUES (?, ?, ?)"#)
.bind(id)
.bind(format!("reel {id}"))
.bind(collection_sort_key(&tags))
.execute(&mut db)
.await
.expect("a reel");
for (ordinal, tag) in tags.iter().enumerate() {
sqlx::query(r#"INSERT INTO "reel_tags" VALUES (?, ?, ?)"#)
.bind(id)
.bind(i64::try_from(ordinal).expect("a small corpus"))
.bind(tag)
.execute(&mut db)
.await
.expect("a tag");
}
}
db
}
async fn ordered(db: &mut SqliteConnection, walk: Walk) -> Vec<i64> {
let terms = [
OrderTerm::shadow(
TermName::new("tags"),
FieldName::new("__ig_tags_sort__"),
OrderDirection::Asc,
),
OrderTerm::new(
TermName::new("id"),
FieldName::new("id"),
OrderDirection::Asc,
),
];
let order = SqlOrder::<sqlx::Sqlite>::new(&terms).expect("a companion column is a column");
let mut built = order
.push_order_by(
QueryBuilder::<SelectClause, sqlx::Sqlite>::select()
.push_field(r#""id""#)
.from(r#""reel""#),
walk,
)
.finalize();
built
.build()
.fetch_all(db)
.await
.expect("the engine answers")
.iter()
.map(|row| row.get::<i64, _>(0))
.collect()
}
fn pinned() -> Vec<i64> {
let mut rows = corpus();
rows.sort_by(|(left_id, left), (right_id, right)| {
compare_collections(left, right).then(left_id.cmp(right_id))
});
rows.into_iter().map(|(id, _)| id).collect()
}
#[tokio::test]
async fn a_live_engine_orders_the_companion_the_way_the_pin_does() {
let mut db = written().await;
assert_eq!(
ordered(&mut db, Walk::Forward).await,
pinned(),
"the engine's order over the companion column is the framework's collection order"
);
}
#[tokio::test]
async fn the_backward_walk_reverses_it() {
let mut db = written().await;
let mut backwards = pinned();
backwards.reverse();
assert_eq!(ordered(&mut db, Walk::Backward).await, backwards);
}
#[tokio::test]
async fn the_empty_collection_sorts_first_and_is_not_null() {
let mut db = written().await;
assert_eq!(
ordered(&mut db, Walk::Forward).await.first().copied(),
Some(2),
"the reel with no tags"
);
let nulls: i64 =
sqlx::query_scalar(r#"SELECT COUNT(*) FROM "reel" WHERE "__ig_tags_sort__" IS NULL"#)
.fetch_one(&mut db)
.await
.expect("the engine answers");
assert_eq!(
nulls, 0,
"a collection has no null state and neither has its order"
);
}
#[tokio::test]
async fn the_companion_is_created_indexed_and_audited() {
let mut db = written().await;
let indexes: Vec<String> = sqlx::query_scalar(
"SELECT name FROM sqlite_schema WHERE type = 'index' AND sql IS NOT NULL ORDER BY name",
)
.fetch_all(&mut db)
.await
.expect("the catalogue answers");
assert!(
indexes
.iter()
.any(|name| name == "__ig_reel___ig_tags_sort__"),
"the companion's index is there: {indexes:?}"
);
let mut validate = Validate::<sqlx::Sqlite>::new();
type_iter(&mut validate).expect("this descriptor renders");
validate
.audit(&mut db)
.await
.expect("the companion is one of the columns the audit reads");
}
#[tokio::test]
async fn a_missing_companion_is_reported_by_name() {
let mut db = SqliteConnection::connect("sqlite::memory:")
.await
.expect("an in-memory database");
db.execute(r#"CREATE TABLE "reel" ("id" INTEGER NOT NULL, "title" TEXT NOT NULL)"#)
.await
.expect("a table");
let mut validate = Validate::<sqlx::Sqlite>::new();
type_iter(&mut validate).expect("this descriptor renders");
let drift = validate
.audit(&mut db)
.await
.expect_err("the companion is missing");
assert!(
format!("{drift}").contains("__ig_tags_sort__"),
"the drift names the column: {drift}"
);
}
}