#![cfg(any(feature = "sqlite", feature = "postgres", feature = "mysql"))]
use crate::{
Column, ColumnName, ColumnShape, Describe, EdgeShape, EdgeTable, FieldName, RecordAddress,
SideTable, Table, TypeVisitor,
query::sql::{AsyncSchemaDialect, Create},
};
struct Media;
struct Clip;
struct Caption;
const CLIP_COLUMNS: &[ColumnShape] = &[
ColumnShape::Plain(ColumnName::new("media_id")),
ColumnShape::Plain(ColumnName::new("id")),
ColumnShape::Plain(ColumnName::new("title")),
ColumnShape::Plain(ColumnName::new("bit_rate")),
];
const CLIP: RecordAddress = RecordAddress::new(
FieldName::new("clip"),
&[FieldName::new("media_id"), FieldName::new("id")],
);
const MEDIA: RecordAddress = RecordAddress::new(FieldName::new("media"), &[FieldName::new("id")]);
const CAPTION: RecordAddress =
RecordAddress::new(FieldName::new("caption"), &[FieldName::new("id")]);
const SOURCE: &FieldName = FieldName::new("__ig_source__");
const FROM_FRAME: &FieldName = FieldName::new("caption_frame");
const FROM_TURN: &FieldName = FieldName::new("caption_turn");
const TAGS: &[&FieldName] = &[FieldName::new("tags")];
const NOTES: &[&FieldName] = &[FieldName::new("notes")];
#[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("title"), "TEXT", true),
],
)
.with_indexes(&[FieldName::new("title")]);
}
#[cfg(feature = "sqlite")]
impl Describe<sqlx::Sqlite> for Clip {
const BLUEPRINT: Table = Table::new(
CLIP,
&[
Column::new(FieldName::new("media_id"), "INTEGER", false),
Column::new(FieldName::new("id"), "INTEGER", false),
Column::new(FieldName::new("title"), "TEXT", true),
Column::new(FieldName::new("bit_rate"), "INTEGER", false),
],
)
.with_edges(&[
EdgeTable::new(TAGS, EdgeShape::Sequence, "TEXT"),
EdgeTable::new(NOTES, EdgeShape::Set, "TEXT"),
]);
}
#[cfg(feature = "sqlite")]
impl Describe<sqlx::Sqlite> for Caption {
const BLUEPRINT: Table = Table::new(
CAPTION,
&[
Column::new(FieldName::new("id"), "INTEGER", false),
Column::new(
SOURCE,
<sqlx::Sqlite as AsyncSchemaDialect>::DISCRIMINANT,
false,
),
Column::new(FieldName::new("text"), "TEXT", false),
],
)
.with_provenance(
SOURCE,
&[
SideTable::new(
FROM_FRAME,
&[
Column::new(FieldName::new("media_id"), "INTEGER", false),
Column::new(FieldName::new("frame"), "INTEGER", false),
],
),
SideTable::new(
FROM_TURN,
&[Column::new(FieldName::new("turn"), "INTEGER", false)],
),
],
);
}
#[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("title"), "text", true),
],
)
.with_indexes(&[FieldName::new("title")]);
}
#[cfg(feature = "postgres")]
impl Describe<sqlx::Postgres> for Clip {
const BLUEPRINT: Table = Table::new(
CLIP,
&[
Column::new(FieldName::new("media_id"), "bigint", false),
Column::new(FieldName::new("id"), "bigint", false),
Column::new(FieldName::new("title"), "text", true),
Column::new(FieldName::new("bit_rate"), "bigint", false),
],
)
.with_edges(&[
EdgeTable::new(TAGS, EdgeShape::Sequence, "text"),
EdgeTable::new(NOTES, EdgeShape::Set, "text"),
]);
}
#[cfg(feature = "postgres")]
impl Describe<sqlx::Postgres> for Caption {
const BLUEPRINT: Table = Table::new(
CAPTION,
&[
Column::new(FieldName::new("id"), "bigint", false),
Column::new(
SOURCE,
<sqlx::Postgres as AsyncSchemaDialect>::DISCRIMINANT,
false,
),
Column::new(FieldName::new("text"), "text", false),
],
)
.with_provenance(
SOURCE,
&[
SideTable::new(
FROM_FRAME,
&[
Column::new(FieldName::new("media_id"), "bigint", false),
Column::new(FieldName::new("frame"), "bigint", false),
],
),
SideTable::new(
FROM_TURN,
&[Column::new(FieldName::new("turn"), "bigint", false)],
),
],
);
}
#[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("title"), "VARCHAR(255)", true),
],
)
.with_indexes(&[FieldName::new("title")]);
}
#[cfg(feature = "mysql")]
impl Describe<sqlx::MySql> for Clip {
const BLUEPRINT: Table = Table::new(
CLIP,
&[
Column::new(FieldName::new("media_id"), "BIGINT", false),
Column::new(FieldName::new("id"), "BIGINT", false),
Column::new(FieldName::new("title"), "VARCHAR(255)", true),
Column::new(FieldName::new("bit_rate"), "BIGINT", false),
],
)
.with_edges(&[
EdgeTable::new(TAGS, EdgeShape::Sequence, "VARCHAR(255)"),
EdgeTable::new(NOTES, EdgeShape::Set, "VARCHAR(255)"),
]);
}
#[cfg(feature = "mysql")]
impl Describe<sqlx::MySql> for Caption {
const BLUEPRINT: Table = Table::new(
CAPTION,
&[
Column::new(FieldName::new("id"), "BIGINT", false),
Column::new(
SOURCE,
<sqlx::MySql as AsyncSchemaDialect>::DISCRIMINANT,
false,
),
Column::new(FieldName::new("text"), "VARCHAR(255)", false),
],
)
.with_provenance(
SOURCE,
&[
SideTable::new(
FROM_FRAME,
&[
Column::new(FieldName::new("media_id"), "BIGINT", false),
Column::new(FieldName::new("frame"), "BIGINT", false),
],
),
SideTable::new(
FROM_TURN,
&[Column::new(FieldName::new("turn"), "BIGINT", false)],
),
],
);
}
fn type_iter<V>(v: &mut V) -> Result<(), V::Error>
where
V: TypeVisitor,
Media: Describe<V::Db>,
Clip: Describe<V::Db>,
Caption: Describe<V::Db>,
{
v.next_type::<Media>()?;
v.next_type::<Clip>()?;
v.next_type::<Caption>()
}
fn schema<Db>() -> Vec<String>
where
Db: super::AsyncSchemaDialect,
Media: Describe<Db>,
Clip: Describe<Db>,
Caption: Describe<Db>,
{
let mut create = Create::<Db>::new();
type_iter(&mut create).expect("every descriptor in this graph renders");
create.statements().to_vec()
}
#[cfg(feature = "sqlite")]
const _: () = assert!(crate::describes(
&<Clip as Describe<sqlx::Sqlite>>::BLUEPRINT,
CLIP_COLUMNS
));
#[cfg(feature = "postgres")]
const _: () = assert!(crate::describes(
&<Clip as Describe<sqlx::Postgres>>::BLUEPRINT,
CLIP_COLUMNS
));
#[cfg(feature = "mysql")]
const _: () = assert!(crate::describes(
&<Clip as Describe<sqlx::MySql>>::BLUEPRINT,
CLIP_COLUMNS
));
fn probe<Db: super::AsyncSchemaDialect>() -> String {
Db::probe(FieldName::new("clip"))
.into_sql()
.as_str()
.to_owned()
}
#[cfg(feature = "sqlite")]
mod sqlite {
use super::{probe, schema};
#[test]
fn the_audit_probes_this_dialects_own_catalogue() {
assert_eq!(
probe::<sqlx::Sqlite>(),
r#"SELECT "name", "type" FROM pragma_table_info(?)"#
);
}
#[test]
fn the_graph_renders_its_whole_schema() {
assert_eq!(
schema::<sqlx::Sqlite>(),
[
r#"CREATE TABLE IF NOT EXISTS "media" ("id" INTEGER NOT NULL, "title" TEXT, PRIMARY KEY ("id"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_media_title" ON "media" ("title")"#,
r#"CREATE TABLE IF NOT EXISTS "clip" ("media_id" INTEGER NOT NULL, "id" INTEGER NOT NULL, "title" TEXT, "bit_rate" INTEGER NOT NULL, PRIMARY KEY ("media_id", "id"), UNIQUE ("id"))"#,
r#"CREATE TABLE IF NOT EXISTS "clip_tags" ("media_id" INTEGER NOT NULL, "id" INTEGER NOT NULL, "tags_ordinal" INTEGER NOT NULL, "tags" TEXT NOT NULL, PRIMARY KEY ("media_id", "id", "tags_ordinal"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_tags_element" ON "clip_tags" ("media_id", "id", "tags")"#,
r#"CREATE TABLE IF NOT EXISTS "clip_notes" ("media_id" INTEGER NOT NULL, "id" INTEGER NOT NULL, "notes" TEXT NOT NULL, PRIMARY KEY ("media_id", "id", "notes"))"#,
r#"CREATE TABLE IF NOT EXISTS "caption" ("id" INTEGER NOT NULL, "__ig_source__" TEXT NOT NULL, "text" TEXT NOT NULL, PRIMARY KEY ("id"))"#,
r#"CREATE TABLE IF NOT EXISTS "caption_frame" ("id" INTEGER NOT NULL, "media_id" INTEGER NOT NULL, "frame" INTEGER NOT NULL, PRIMARY KEY ("id"), FOREIGN KEY ("id") REFERENCES "caption" ("id"))"#,
r#"CREATE TABLE IF NOT EXISTS "caption_turn" ("id" INTEGER NOT NULL, "turn" INTEGER NOT NULL, PRIMARY KEY ("id"), FOREIGN KEY ("id") REFERENCES "caption" ("id"))"#,
]
);
}
}
#[cfg(feature = "postgres")]
mod postgres {
use super::{probe, schema};
#[test]
fn the_audit_probes_this_dialects_own_catalogue() {
assert_eq!(
probe::<sqlx::Postgres>(),
"SELECT column_name, data_type FROM information_schema.columns WHERE table_name = $1"
);
}
#[test]
fn the_graph_renders_its_whole_schema() {
assert_eq!(
schema::<sqlx::Postgres>(),
[
r#"CREATE TABLE IF NOT EXISTS "media" ("id" bigint NOT NULL, "title" text, PRIMARY KEY ("id"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_media_title" ON "media" ("title")"#,
r#"CREATE TABLE IF NOT EXISTS "clip" ("media_id" bigint NOT NULL, "id" bigint NOT NULL, "title" text, "bit_rate" bigint NOT NULL, PRIMARY KEY ("media_id", "id"), UNIQUE ("id"))"#,
r#"CREATE TABLE IF NOT EXISTS "clip_tags" ("media_id" bigint NOT NULL, "id" bigint NOT NULL, "tags_ordinal" bigint NOT NULL, "tags" text NOT NULL, PRIMARY KEY ("media_id", "id", "tags_ordinal"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_tags_element" ON "clip_tags" ("media_id", "id", "tags")"#,
r#"CREATE TABLE IF NOT EXISTS "clip_notes" ("media_id" bigint NOT NULL, "id" bigint NOT NULL, "notes" text NOT NULL, PRIMARY KEY ("media_id", "id", "notes"))"#,
r#"CREATE TABLE IF NOT EXISTS "caption" ("id" bigint NOT NULL, "__ig_source__" text NOT NULL, "text" text NOT NULL, PRIMARY KEY ("id"))"#,
r#"CREATE TABLE IF NOT EXISTS "caption_frame" ("id" bigint NOT NULL, "media_id" bigint NOT NULL, "frame" bigint NOT NULL, PRIMARY KEY ("id"), FOREIGN KEY ("id") REFERENCES "caption" ("id"))"#,
r#"CREATE TABLE IF NOT EXISTS "caption_turn" ("id" bigint NOT NULL, "turn" bigint NOT NULL, PRIMARY KEY ("id"), FOREIGN KEY ("id") REFERENCES "caption" ("id"))"#,
]
);
}
}
#[cfg(feature = "mysql")]
mod mysql {
use super::{probe, schema};
#[test]
fn the_audit_probes_this_dialects_own_catalogue() {
assert_eq!(
probe::<sqlx::MySql>(),
"SELECT COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = \
DATABASE() AND TABLE_NAME = ?"
);
}
#[test]
fn the_graph_renders_its_whole_schema() {
assert_eq!(
schema::<sqlx::MySql>(),
[
"CREATE TABLE IF NOT EXISTS `media` (`id` BIGINT NOT NULL, `title` VARCHAR(255), PRIMARY KEY (`id`))",
"CREATE INDEX IF NOT EXISTS `__ig_media_title` ON `media` (`title`)",
"CREATE TABLE IF NOT EXISTS `clip` (`media_id` BIGINT NOT NULL, `id` BIGINT NOT NULL, `title` VARCHAR(255), `bit_rate` BIGINT NOT NULL, PRIMARY KEY (`media_id`, `id`), UNIQUE (`id`))",
"CREATE TABLE IF NOT EXISTS `clip_tags` (`media_id` BIGINT NOT NULL, `id` BIGINT NOT NULL, `tags_ordinal` BIGINT NOT NULL, `tags` VARCHAR(255) NOT NULL, PRIMARY KEY (`media_id`, `id`, `tags_ordinal`))",
"CREATE INDEX IF NOT EXISTS `__ig_clip_tags_element` ON `clip_tags` (`media_id`, `id`, `tags`)",
"CREATE TABLE IF NOT EXISTS `clip_notes` (`media_id` BIGINT NOT NULL, `id` BIGINT NOT NULL, `notes` VARCHAR(255) NOT NULL, PRIMARY KEY (`media_id`, `id`, `notes`))",
"CREATE TABLE IF NOT EXISTS `caption` (`id` BIGINT NOT NULL, `__ig_source__` VARCHAR(255) NOT NULL, `text` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`))",
"CREATE TABLE IF NOT EXISTS `caption_frame` (`id` BIGINT NOT NULL, `media_id` BIGINT NOT NULL, `frame` BIGINT NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`id`) REFERENCES `caption` (`id`))",
"CREATE TABLE IF NOT EXISTS `caption_turn` (`id` BIGINT NOT NULL, `turn` BIGINT NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`id`) REFERENCES `caption` (`id`))",
]
);
}
}
#[cfg(feature = "sqlite")]
#[test]
fn a_lineage_bearing_fan_in_points_its_provenance_at_the_unique_id() {
const CUE: Table = Table::new(
RecordAddress::new(
FieldName::new("cue"),
&[FieldName::new("media_id"), FieldName::new("id")],
),
&[
Column::new(FieldName::new("media_id"), "INTEGER", false),
Column::new(FieldName::new("id"), "INTEGER", false),
Column::new(
SOURCE,
<sqlx::Sqlite as AsyncSchemaDialect>::DISCRIMINANT,
false,
),
],
)
.with_provenance(
SOURCE,
&[SideTable::new(
FieldName::new("cue_frame"),
&[Column::new(FieldName::new("frame"), "INTEGER", false)],
)],
);
assert_eq!(
CUE
.create::<sqlx::Sqlite>()
.expect("this descriptor renders"),
[
r#"CREATE TABLE IF NOT EXISTS "cue" ("media_id" INTEGER NOT NULL, "id" INTEGER NOT NULL, "__ig_source__" TEXT NOT NULL, PRIMARY KEY ("media_id", "id"), UNIQUE ("id"))"#,
r#"CREATE TABLE IF NOT EXISTS "cue_frame" ("id" INTEGER NOT NULL, "frame" INTEGER NOT NULL, PRIMARY KEY ("id"), FOREIGN KEY ("id") REFERENCES "cue" ("id"))"#,
]
);
}
#[cfg(feature = "sqlite")]
mod live {
use sqlx::Sqlite;
use super::{Caption, Clip, Describe, Media, type_iter};
use crate::{
FieldName,
query::sql::{Create, SqlSchemaError, Validate},
};
async fn empty() -> sqlx::sqlite::SqliteConnection {
use sqlx::Connection as _;
let mut db = sqlx::sqlite::SqliteConnection::connect("sqlite::memory:")
.await
.expect("an in-memory database");
sqlx::query("PRAGMA foreign_keys = ON")
.execute(&mut db)
.await
.expect("the connection enforces what the schema declares");
db
}
async fn create(db: &mut sqlx::sqlite::SqliteConnection) -> Result<(), SqlSchemaError> {
let mut create = Create::<Sqlite>::new();
type_iter(&mut create)?;
create.run(db).await
}
async fn validate(db: &mut sqlx::sqlite::SqliteConnection) -> Result<(), SqlSchemaError> {
let mut validate = Validate::<Sqlite>::new();
type_iter(&mut validate)?;
validate.audit(db).await
}
async fn tables(db: &mut sqlx::sqlite::SqliteConnection) -> Vec<String> {
sqlx::query_scalar::<_, String>(
"SELECT name FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY \
name",
)
.fetch_all(db)
.await
.expect("the catalogue answers")
}
async fn indexes(db: &mut sqlx::sqlite::SqliteConnection) -> Vec<String> {
sqlx::query_scalar::<_, String>(
"SELECT name FROM sqlite_schema WHERE type = 'index' AND sql IS NOT NULL ORDER BY name",
)
.fetch_all(db)
.await
.expect("the catalogue answers")
}
const DECLARED: [&str; 7] = [
"caption",
"caption_frame",
"caption_turn",
"clip",
"clip_notes",
"clip_tags",
"media",
];
#[tokio::test]
async fn the_walk_creates_every_table_and_index_the_graph_declares() {
let mut db = empty().await;
create(&mut db).await.expect("an empty database takes it");
assert_eq!(tables(&mut db).await, DECLARED);
assert_eq!(
indexes(&mut db).await,
["__ig_clip_tags_element", "__ig_media_title"]
);
}
#[tokio::test]
async fn an_orphan_provenance_row_is_refused_by_the_engine() {
let mut db = empty().await;
create(&mut db).await.expect("the schema is created");
let orphan = sqlx::query(r#"INSERT INTO "caption_turn" ("id", "turn") VALUES (1, 9)"#)
.execute(&mut db)
.await;
let Err(why) = orphan else {
panic!("a provenance row with no common row to point at is an orphan");
};
assert!(
why.to_string().contains("FOREIGN KEY constraint failed"),
"the engine's own sentence, rather than a rule re-checked here: {why}"
);
sqlx::query(
r#"INSERT INTO "caption" ("id", "__ig_source__", "text") VALUES (1, 'Turn', 'hi')"#,
)
.execute(&mut db)
.await
.expect("the common row lands");
sqlx::query(r#"INSERT INTO "caption_turn" ("id", "turn") VALUES (1, 9)"#)
.execute(&mut db)
.await
.expect("and the same provenance row is taken once it has one to point at");
}
#[tokio::test]
async fn a_second_create_over_the_same_database_changes_nothing() {
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!(tables(&mut db).await, DECLARED);
assert_eq!(
indexes(&mut db).await,
["__ig_clip_tags_element", "__ig_media_title"]
);
}
#[tokio::test]
async fn the_audit_passes_the_schema_the_create_made() {
let mut db = empty().await;
create(&mut db).await.expect("the schema is created");
validate(&mut db).await.expect("and it is the declared one");
}
#[tokio::test]
async fn the_audit_names_a_table_the_database_does_not_have() {
let mut db = empty().await;
let Err(SqlSchemaError::Missing(table)) = validate(&mut db).await else {
panic!("an empty database has none of the declared tables");
};
assert_eq!(table, FieldName::new("media"));
}
#[tokio::test]
async fn the_audit_names_the_column_whose_stored_type_drifted() {
let mut db = empty().await;
create(&mut db).await.expect("the schema is created");
for statement in [
r#"ALTER TABLE "media" RENAME TO "media_old""#,
r#"CREATE TABLE "media" ("id" INTEGER NOT NULL, "title" BLOB, PRIMARY KEY ("id"))"#,
r#"DROP TABLE "media_old""#,
] {
sqlx::query(statement)
.execute(&mut db)
.await
.expect("the drift is applied");
}
let Err(SqlSchemaError::Type {
table,
column,
declared,
stored,
}) = validate(&mut db).await
else {
panic!("a column stored at another type is drift");
};
assert_eq!(table, FieldName::new("media"));
assert_eq!(column, "title");
assert_eq!(declared, "TEXT");
assert_eq!(stored, "BLOB");
}
#[tokio::test]
async fn the_audit_names_a_column_the_live_table_lost() {
let mut db = empty().await;
create(&mut db).await.expect("the schema is created");
sqlx::query(r#"ALTER TABLE "clip" DROP COLUMN "bit_rate""#)
.execute(&mut db)
.await
.expect("the drift is applied");
let Err(SqlSchemaError::Column { table, column }) = validate(&mut db).await else {
panic!("a declared column that is gone is drift");
};
assert_eq!(table, FieldName::new("clip"));
assert_eq!(column, "bit_rate");
}
#[tokio::test]
async fn a_column_the_declaration_does_not_know_about_is_not_drift() {
let mut db = empty().await;
create(&mut db).await.expect("the schema is created");
sqlx::query(r#"ALTER TABLE "clip" ADD COLUMN "extra" TEXT"#)
.execute(&mut db)
.await
.expect("the column is added");
validate(&mut db)
.await
.expect("every declared column is still there, at its declared type");
}
#[tokio::test]
async fn both_visitors_are_driven_by_one_list() {
let mut create = Create::<Sqlite>::new();
type_iter(&mut create).expect("every descriptor renders");
let mut validate = Validate::<Sqlite>::new();
type_iter(&mut validate).expect("collecting refuses nothing");
let walked: Vec<&str> = validate
.tables()
.iter()
.map(|table| table.address().table().as_str())
.collect();
assert_eq!(walked, ["media", "clip", "caption"]);
assert_eq!(
create.statements().len(),
<Media as Describe<Sqlite>>::BLUEPRINT
.create::<Sqlite>()
.expect("it renders")
.len()
+ <Clip as Describe<Sqlite>>::BLUEPRINT
.create::<Sqlite>()
.expect("it renders")
.len()
+ <Caption as Describe<Sqlite>>::BLUEPRINT
.create::<Sqlite>()
.expect("it renders")
.len()
);
}
#[tokio::test]
async fn the_audit_names_a_column_a_provenance_table_lost() {
let mut db = empty().await;
create(&mut db).await.expect("the schema is created");
sqlx::query(r#"ALTER TABLE "caption_frame" DROP COLUMN "frame""#)
.execute(&mut db)
.await
.expect("the drift is applied");
let Err(SqlSchemaError::Column { table, column }) = validate(&mut db).await else {
panic!("a declared ancestor column that is gone is drift");
};
assert_eq!(table, FieldName::new("caption_frame"));
assert_eq!(column, "frame");
}
}