#![cfg(any(feature = "sqlite", feature = "postgres", feature = "mysql"))]
use crate::{
Column, CompositeUnique, Describe, FieldName, RecordAddress, Table, TypeVisitor,
query::sql::Create,
};
struct Scene;
const SCENE: RecordAddress = RecordAddress::new(FieldName::new("scene"), &[FieldName::new("id")]);
const COMPOSITES: &[CompositeUnique] = &[
CompositeUnique::new(&[
FieldName::new("media"),
FieldName::new("track"),
FieldName::new("index"),
]),
CompositeUnique::new(&[FieldName::new("run"), FieldName::new("ordinal")]),
CompositeUnique::new(&[FieldName::new("checksum")]),
];
#[cfg(feature = "sqlite")]
impl Describe<sqlx::Sqlite> for Scene {
const BLUEPRINT: Table = Table::new(
SCENE,
&[
Column::new(FieldName::new("id"), "INTEGER", false),
Column::new(FieldName::new("media"), "INTEGER", false),
Column::new(FieldName::new("track"), "INTEGER", false),
Column::new(FieldName::new("index"), "INTEGER", false),
Column::new(FieldName::new("run"), "INTEGER", false),
Column::new(FieldName::new("ordinal"), "INTEGER", false),
Column::new(FieldName::new("checksum"), "TEXT", false),
],
)
.with_composite_uniques(COMPOSITES);
}
#[cfg(feature = "postgres")]
impl Describe<sqlx::Postgres> for Scene {
const BLUEPRINT: Table = Table::new(
SCENE,
&[
Column::new(FieldName::new("id"), "bigint", false),
Column::new(FieldName::new("media"), "bigint", false),
Column::new(FieldName::new("track"), "bigint", false),
Column::new(FieldName::new("index"), "bigint", false),
Column::new(FieldName::new("run"), "bigint", false),
Column::new(FieldName::new("ordinal"), "bigint", false),
Column::new(FieldName::new("checksum"), "text", false),
],
)
.with_composite_uniques(COMPOSITES);
}
#[cfg(feature = "mysql")]
impl Describe<sqlx::MySql> for Scene {
const BLUEPRINT: Table = Table::new(
SCENE,
&[
Column::new(FieldName::new("id"), "BIGINT", false),
Column::new(FieldName::new("media"), "BIGINT", false),
Column::new(FieldName::new("track"), "BIGINT", false),
Column::new(FieldName::new("index"), "BIGINT", false),
Column::new(FieldName::new("run"), "BIGINT", false),
Column::new(FieldName::new("ordinal"), "BIGINT", false),
Column::new(FieldName::new("checksum"), "VARCHAR(255)", false),
],
)
.with_composite_uniques(COMPOSITES);
}
fn type_iter<V>(v: &mut V) -> Result<(), V::Error>
where
V: TypeVisitor,
Scene: Describe<V::Db>,
{
v.next_type::<Scene>()
}
fn schema<Db>() -> Vec<String>
where
Db: super::AsyncSchemaDialect,
Scene: 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_renders_one_statement_per_composite_in_the_authored_order() {
assert_eq!(
schema::<sqlx::Sqlite>(),
[
r#"CREATE TABLE IF NOT EXISTS "scene" ("id" INTEGER NOT NULL, "media" INTEGER NOT NULL, "track" INTEGER NOT NULL, "index" INTEGER NOT NULL, "run" INTEGER NOT NULL, "ordinal" INTEGER NOT NULL, "checksum" TEXT NOT NULL, PRIMARY KEY ("id"))"#,
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_scene_media_track_index" ON "scene" ("media", "track", "index")"#,
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_scene_run_ordinal" ON "scene" ("run", "ordinal")"#,
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_scene_checksum" ON "scene" ("checksum")"#,
]
);
}
#[cfg(feature = "postgres")]
#[test]
fn postgres_renders_one_statement_per_composite_in_the_authored_order() {
assert_eq!(
schema::<sqlx::Postgres>(),
[
r#"CREATE TABLE IF NOT EXISTS "scene" ("id" bigint NOT NULL, "media" bigint NOT NULL, "track" bigint NOT NULL, "index" bigint NOT NULL, "run" bigint NOT NULL, "ordinal" bigint NOT NULL, "checksum" text NOT NULL, PRIMARY KEY ("id"))"#,
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_scene_media_track_index" ON "scene" ("media", "track", "index")"#,
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_scene_run_ordinal" ON "scene" ("run", "ordinal")"#,
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_scene_checksum" ON "scene" ("checksum")"#,
]
);
}
#[cfg(feature = "mysql")]
#[test]
fn mysql_renders_one_statement_per_composite_in_the_authored_order() {
assert_eq!(
schema::<sqlx::MySql>(),
[
"CREATE TABLE IF NOT EXISTS `scene` (`id` BIGINT NOT NULL, `media` BIGINT NOT NULL, \
`track` BIGINT NOT NULL, `index` BIGINT NOT NULL, `run` BIGINT NOT NULL, \
`ordinal` BIGINT NOT NULL, `checksum` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`))",
"CREATE UNIQUE INDEX IF NOT EXISTS `__ig_scene_media_track_index` ON `scene` \
(`media`, `track`, `index`)",
"CREATE UNIQUE INDEX IF NOT EXISTS `__ig_scene_run_ordinal` ON `scene` (`run`, `ordinal`)",
"CREATE UNIQUE INDEX IF NOT EXISTS `__ig_scene_checksum` ON `scene` (`checksum`)",
]
);
}
#[cfg(feature = "sqlite")]
#[test]
fn the_same_entries_in_another_order_are_another_constraint() {
const REVERSED: Table = Table::new(
SCENE,
&[
Column::new(FieldName::new("id"), "INTEGER", false),
Column::new(FieldName::new("media"), "INTEGER", false),
Column::new(FieldName::new("track"), "INTEGER", false),
Column::new(FieldName::new("index"), "INTEGER", false),
],
)
.with_composite_uniques(&[
CompositeUnique::new(&[
FieldName::new("media"),
FieldName::new("track"),
FieldName::new("index"),
]),
CompositeUnique::new(&[
FieldName::new("index"),
FieldName::new("track"),
FieldName::new("media"),
]),
]);
assert_eq!(
REVERSED
.create::<sqlx::Sqlite>()
.expect("this descriptor renders")[1..],
[
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_scene_media_track_index" ON "scene" ("media", "track", "index")"#,
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_scene_index_track_media" ON "scene" ("index", "track", "media")"#,
]
);
}
#[cfg(feature = "sqlite")]
#[test]
fn a_table_with_no_composite_renders_no_composite_statement() {
const BARE: Table = Table::new(
SCENE,
&[Column::new(FieldName::new("id"), "INTEGER", false)],
);
assert!(BARE.composite_uniques().is_empty());
assert_eq!(
BARE
.create::<sqlx::Sqlite>()
.expect("this descriptor renders"),
[r#"CREATE TABLE IF NOT EXISTS "scene" ("id" INTEGER NOT NULL, PRIMARY KEY ("id"))"#]
);
}
#[cfg(all(feature = "mediatime", feature = "sqlite"))]
mod citizen {
use crate::{
ColumnName, ColumnShape, CompositeUnique, Describe, FieldName, RecordAddress, StoredAs, Table,
};
struct Clip;
const CLIP: RecordAddress = RecordAddress::new(FieldName::new("clip"), &[FieldName::new("id")]);
const CLIP_COLUMNS: &[ColumnShape] = &[
ColumnShape::Plain(ColumnName::new("id")),
ColumnShape::Plain(ColumnName::new("media")),
crate::shape::<crate::MediaTimeRange>(ColumnName::new("span")),
];
impl Describe<sqlx::Sqlite> for Clip {
const BLUEPRINT: Table = Table::shaped(
CLIP,
CLIP_COLUMNS,
&[
<i64 as StoredAs<sqlx::Sqlite>>::WORDS,
<i64 as StoredAs<sqlx::Sqlite>>::WORDS,
<crate::MediaTimeRange as StoredAs<sqlx::Sqlite>>::WORDS,
],
&[false, false, false],
)
.with_composite_uniques(&[CompositeUnique::new(&[
FieldName::new("media"),
FieldName::new("span"),
])]);
}
#[test]
fn a_citizen_in_a_composite_constrains_its_value_leaves_alone() {
let statements = <Clip as Describe<sqlx::Sqlite>>::BLUEPRINT
.create::<sqlx::Sqlite>()
.expect("this descriptor renders");
assert_eq!(
statements[1],
r#"CREATE UNIQUE INDEX IF NOT EXISTS "__ig_clip_media_span" ON "clip" ("media", "span_start_pts", "span_end_pts", "span_timebase_num", "span_timebase_den")"#
);
assert!(
!statements[1].contains("nanos"),
"the derived leaves are named by the CREATE TABLE and by no constraint: {}",
statements[1]
);
assert!(
statements[0].contains(r#""span_start_nanos""#),
"and they are still DECLARED, which is what makes their absence above a choice: {}",
statements[0]
);
}
}
#[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_widths(db: &mut sqlx::sqlite::SqliteConnection) -> Vec<(String, bool, i64)> {
let listed = sqlx::query_as::<_, (String, i64)>(
r#"SELECT name, "unique" FROM pragma_index_list('scene') ORDER BY name"#,
)
.fetch_all(&mut *db)
.await
.expect("the catalogue answers");
let mut out = Vec::new();
for (name, unique) in listed {
let width = sqlx::query_scalar::<_, i64>("SELECT count(*) FROM pragma_index_info(?)")
.bind(&name)
.fetch_one(&mut *db)
.await
.expect("the catalogue answers");
out.push((name, unique != 0, width));
}
out
}
async fn insert_row(
db: &mut sqlx::sqlite::SqliteConnection,
id: i64,
scene: (i64, i64, i64),
run: (i64, i64),
checksum: &str,
) -> Result<sqlx::sqlite::SqliteQueryResult, sqlx::Error> {
sqlx::query(
r#"INSERT INTO "scene" ("id", "media", "track", "index", "run", "ordinal", "checksum")
VALUES (?, ?, ?, ?, ?, ?, ?)"#,
)
.bind(id)
.bind(scene.0)
.bind(scene.1)
.bind(scene.2)
.bind(run.0)
.bind(run.1)
.bind(checksum)
.execute(db)
.await
}
#[tokio::test]
async fn the_engine_holds_each_composite_over_every_column_it_names() {
let mut db = empty().await;
create(&mut db).await.expect("an empty database takes it");
assert_eq!(
index_widths(&mut db).await,
[
("__ig_scene_checksum".to_owned(), true, 1),
("__ig_scene_media_track_index".to_owned(), true, 3),
("__ig_scene_run_ordinal".to_owned(), true, 2),
]
);
}
#[tokio::test]
async fn a_second_create_adds_no_second_composite_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_widths(&mut db).await,
[
("__ig_scene_checksum".to_owned(), true, 1),
("__ig_scene_media_track_index".to_owned(), true, 3),
("__ig_scene_run_ordinal".to_owned(), true, 2),
]
);
}
#[tokio::test]
async fn a_repeated_tuple_refuses_and_a_row_differing_in_one_column_does_not() {
let mut db = empty().await;
create(&mut db).await.expect("an empty database takes it");
insert_row(&mut db, 1, (7, 2, 0), (100, 0), "sha256:aaa")
.await
.expect("the first row lands");
insert_row(&mut db, 2, (7, 2, 1), (100, 1), "sha256:bbb")
.await
.expect("a row differing in one column of the tuple is a different scene");
let refused = insert_row(&mut db, 3, (7, 2, 0), (101, 0), "sha256:ccc").await;
let Err(sqlx::Error::Database(why)) = refused else {
panic!("a repeated tuple is the engine's own refusal, not a panic: {refused:?}");
};
assert!(
why.is_unique_violation(),
"the driver names it as one: {why}"
);
for column in ["scene.media", "scene.track", "scene.index"] {
assert!(
why.message().contains(column),
"the engine's own sentence names every column of the constraint, and not this one: {why}"
);
}
assert_eq!(
sqlx::query_scalar::<_, i64>(r#"SELECT count(*) FROM "scene""#)
.fetch_one(&mut db)
.await
.expect("the table is there"),
2,
"and the refused row was not written"
);
}
#[tokio::test]
async fn the_second_composite_refuses_on_its_own_columns() {
let mut db = empty().await;
create(&mut db).await.expect("an empty database takes it");
insert_row(&mut db, 1, (7, 2, 0), (100, 0), "sha256:aaa")
.await
.expect("the first row lands");
let refused = insert_row(&mut db, 2, (8, 3, 1), (100, 0), "sha256:bbb").await;
let Err(sqlx::Error::Database(why)) = refused else {
panic!("a repeated tuple 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("scene.run") && why.message().contains("scene.ordinal"),
"the SECOND constraint's own columns are what the engine names: {why}"
);
}
}