#![cfg(any(feature = "sqlite", feature = "postgres", feature = "mysql"))]
use crate::{Column, Describe, FieldName, RecordAddress, Table, TypeVisitor, query::sql::Create};
struct Media;
const MEDIA: RecordAddress = RecordAddress::new(FieldName::new("media"), &[FieldName::new("id")]);
const INDEXED: &[&FieldName] = &[FieldName::new("label"), FieldName::new("slug")];
const UNIQUE: &[&FieldName] = &[FieldName::new("checksum"), FieldName::new("slug")];
#[cfg(feature = "sqlite")]
impl Describe<sqlx::Sqlite> for Media {
const BLUEPRINT: Table = Table::new(
MEDIA,
&[
Column::new(FieldName::new("id"), "INTEGER", false),
Column::new(FieldName::new("label"), "TEXT", false),
Column::new(FieldName::new("checksum"), "TEXT", false),
Column::new(FieldName::new("slug"), "TEXT", false),
Column::new(FieldName::new("notes"), "TEXT", true),
],
)
.with_indexes(INDEXED)
.with_uniques(UNIQUE);
}
#[cfg(feature = "postgres")]
impl Describe<sqlx::Postgres> for Media {
const BLUEPRINT: Table = Table::new(
MEDIA,
&[
Column::new(FieldName::new("id"), "bigint", false),
Column::new(FieldName::new("label"), "text", false),
Column::new(FieldName::new("checksum"), "text", false),
Column::new(FieldName::new("slug"), "text", false),
Column::new(FieldName::new("notes"), "text", true),
],
)
.with_indexes(INDEXED)
.with_uniques(UNIQUE);
}
#[cfg(feature = "mysql")]
impl Describe<sqlx::MySql> for Media {
const BLUEPRINT: Table = Table::new(
MEDIA,
&[
Column::new(FieldName::new("id"), "BIGINT", false),
Column::new(FieldName::new("label"), "VARCHAR(255)", false),
Column::new(FieldName::new("checksum"), "VARCHAR(255)", false),
Column::new(FieldName::new("slug"), "VARCHAR(255)", false),
Column::new(FieldName::new("notes"), "VARCHAR(255)", true),
],
)
.with_indexes(INDEXED)
.with_uniques(UNIQUE);
}
fn type_iter<V>(v: &mut V) -> Result<(), V::Error>
where
V: TypeVisitor,
Media: Describe<V::Db>,
{
v.next_type::<Media>()
}
fn schema<Db>() -> Vec<String>
where
Db: super::AsyncSchemaDialect,
Media: 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_dedups_the_field_wearing_both_words_to_one_unique_statement() {
assert_eq!(
schema::<sqlx::Sqlite>(),
[
r#"CREATE TABLE IF NOT EXISTS "media" ("id" INTEGER NOT NULL, "label" TEXT NOT NULL, "checksum" TEXT NOT NULL, "slug" TEXT NOT NULL, "notes" TEXT, PRIMARY KEY ("id"))"#,
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_media_checksum" ON "media" ("checksum")"#,
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_media_slug" ON "media" ("slug")"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_media_label" ON "media" ("label")"#,
]
);
}
#[cfg(feature = "postgres")]
#[test]
fn postgres_dedups_the_field_wearing_both_words_to_one_unique_statement() {
assert_eq!(
schema::<sqlx::Postgres>(),
[
r#"CREATE TABLE IF NOT EXISTS "media" ("id" bigint NOT NULL, "label" text NOT NULL, "checksum" text NOT NULL, "slug" text NOT NULL, "notes" text, PRIMARY KEY ("id"))"#,
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_media_checksum" ON "media" ("checksum")"#,
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_media_slug" ON "media" ("slug")"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_media_label" ON "media" ("label")"#,
]
);
}
#[cfg(feature = "mysql")]
#[test]
fn mysql_dedups_the_field_wearing_both_words_to_one_unique_statement() {
assert_eq!(
schema::<sqlx::MySql>(),
[
"CREATE TABLE IF NOT EXISTS `media` (`id` BIGINT NOT NULL, `label` VARCHAR(255) NOT NULL, \
`checksum` VARCHAR(255) NOT NULL, `slug` VARCHAR(255) NOT NULL, `notes` VARCHAR(255), \
PRIMARY KEY (`id`))",
"CREATE UNIQUE INDEX IF NOT EXISTS `__ig_media_checksum` ON `media` (`checksum`)",
"CREATE UNIQUE INDEX IF NOT EXISTS `__ig_media_slug` ON `media` (`slug`)",
"CREATE INDEX IF NOT EXISTS `__ig_media_label` ON `media` (`label`)",
]
);
}
#[cfg(feature = "sqlite")]
#[test]
fn a_table_with_no_unique_field_renders_no_unique_index() {
const BARE: Table = Table::new(
MEDIA,
&[Column::new(FieldName::new("id"), "INTEGER", false)],
);
assert!(BARE.uniques().is_empty());
assert_eq!(
BARE
.create::<sqlx::Sqlite>()
.expect("this descriptor renders"),
[r#"CREATE TABLE IF NOT EXISTS "media" ("id" INTEGER NOT NULL, PRIMARY KEY ("id"))"#]
);
}
#[cfg(feature = "sqlite")]
mod live {
use super::type_iter;
use crate::query::sql::{Create, SqlSchemaError};
async fn empty() -> sqlx::sqlite::SqliteConnection {
use sqlx::Connection as _;
sqlx::sqlite::SqliteConnection::connect("sqlite::memory:")
.await
.expect("an in-memory database")
}
async fn create(db: &mut sqlx::sqlite::SqliteConnection) -> Result<(), SqlSchemaError> {
let mut create = Create::<sqlx::Sqlite>::new();
type_iter(&mut create)?;
create.run(db).await
}
async fn index_flags(
db: &mut sqlx::sqlite::SqliteConnection,
table: &str,
) -> Vec<(String, bool)> {
sqlx::query_as::<_, (String, i64)>(
r#"SELECT name, "unique" FROM pragma_index_list(?) ORDER BY name"#,
)
.bind(table)
.fetch_all(db)
.await
.expect("the catalogue answers")
.into_iter()
.map(|(name, unique)| (name, unique != 0))
.collect()
}
#[tokio::test]
async fn the_engine_marks_each_index_unique_exactly_where_the_word_was_written() {
let mut db = empty().await;
create(&mut db).await.expect("an empty database takes it");
assert_eq!(
index_flags(&mut db, "media").await,
[
("__ig_media_checksum".to_owned(), true),
("__ig_media_label".to_owned(), false),
("__ig_media_slug".to_owned(), true),
]
);
}
#[tokio::test]
async fn a_second_create_adds_no_second_unique_index() {
let mut db = empty().await;
create(&mut db).await.expect("the first create lands");
create(&mut db).await.expect("and so does the second");
assert_eq!(
index_flags(&mut db, "media").await,
[
("__ig_media_checksum".to_owned(), true),
("__ig_media_label".to_owned(), false),
("__ig_media_slug".to_owned(), true),
]
);
}
async fn insert_row(
db: &mut sqlx::sqlite::SqliteConnection,
id: i64,
label: &str,
checksum: &str,
slug: &str,
) -> Result<sqlx::sqlite::SqliteQueryResult, sqlx::Error> {
sqlx::query(r#"INSERT INTO "media" ("id", "label", "checksum", "slug") VALUES (?, ?, ?, ?)"#)
.bind(id)
.bind(label)
.bind(checksum)
.bind(slug)
.execute(db)
.await
}
async fn upsert_row(
db: &mut sqlx::sqlite::SqliteConnection,
id: i64,
label: &str,
checksum: &str,
slug: &str,
) -> Result<sqlx::sqlite::SqliteQueryResult, sqlx::Error> {
sqlx::query(
r#"INSERT INTO "media" ("id", "label", "checksum", "slug") VALUES (?, ?, ?, ?)
ON CONFLICT ("id") DO UPDATE SET "label" = excluded."label",
"checksum" = excluded."checksum", "slug" = excluded."slug""#,
)
.bind(id)
.bind(label)
.bind(checksum)
.bind(slug)
.execute(db)
.await
}
#[tokio::test]
async fn a_duplicate_insert_into_a_unique_column_refuses_by_name() {
let mut db = empty().await;
create(&mut db).await.expect("an empty database takes it");
insert_row(&mut db, 1, "a", "sha256:aaa", "slug-a")
.await
.expect("the first row lands");
let refused = insert_row(&mut db, 2, "b", "sha256:aaa", "slug-b").await;
let Err(sqlx::Error::Database(why)) = refused else {
panic!("a colliding unique column is the engine's own refusal, not a panic: {refused:?}");
};
assert!(
why.is_unique_violation(),
"the driver names it as one: {why}"
);
assert!(
why.message().contains("media.checksum"),
"the engine's own sentence names the TABLE and the COLUMN: {why}"
);
assert_eq!(
sqlx::query_scalar::<_, i64>(r#"SELECT count(*) FROM "media""#)
.fetch_one(&mut db)
.await
.expect("the parent table is there"),
1,
"and nothing was written twice"
);
}
#[tokio::test]
async fn an_upsert_by_id_updating_a_unique_column_to_a_colliding_value_refuses() {
let mut db = empty().await;
create(&mut db).await.expect("an empty database takes it");
insert_row(&mut db, 1, "a", "sha256:aaa", "slug-a")
.await
.expect("the first row lands");
upsert_row(&mut db, 2, "b", "sha256:bbb", "slug-b")
.await
.expect("a fresh address is a plain insert");
let refused = upsert_row(&mut db, 2, "b2", "sha256:aaa", "slug-b2").await;
let Err(sqlx::Error::Database(why)) = refused else {
panic!("a colliding unique column is the engine's own refusal, not a panic: {refused:?}");
};
assert!(
why.is_unique_violation(),
"the driver names it as one: {why}"
);
assert!(
why.message().contains("checksum"),
"the engine's own sentence names the COLUMN: {why}"
);
let (label, checksum, slug): (String, String, String) =
sqlx::query_as(r#"SELECT "label", "checksum", "slug" FROM "media" WHERE "id" = 2"#)
.fetch_one(&mut db)
.await
.expect("row 2 is still there");
assert_eq!(
(label.as_str(), checksum.as_str(), slug.as_str()),
("b", "sha256:bbb", "slug-b"),
"the failed keyed replace touched nothing"
);
}
}