#![cfg(any(feature = "sqlite", feature = "postgres", feature = "mysql"))]
use crate::{
Column, Describe, EdgeShape, EdgeTable, FieldName, RecordAddress, Table, TypeVisitor,
query::sql::Create,
};
struct Clip;
const CLIP: RecordAddress = RecordAddress::new(FieldName::new("clip"), &[FieldName::new("id")]);
const TAGS: &[&FieldName] = &[FieldName::new("tags")];
const MARKS: &[&FieldName] = &[FieldName::new("marks")];
const NOTES: &[&FieldName] = &[FieldName::new("notes")];
const BEATS: &[&FieldName] = &[FieldName::new("beats")];
#[cfg(feature = "sqlite")]
const SQLITE_EDGES: &[EdgeTable] = &[
EdgeTable::new(TAGS, EdgeShape::Sequence, "TEXT").indexed(),
EdgeTable::new(MARKS, EdgeShape::Set, "TEXT").indexed(),
EdgeTable::new(NOTES, EdgeShape::Sequence, "TEXT"),
EdgeTable::new(BEATS, EdgeShape::Sequence, "INTEGER").indexed(),
];
#[cfg(feature = "postgres")]
const POSTGRES_EDGES: &[EdgeTable] = &[
EdgeTable::new(TAGS, EdgeShape::Sequence, "text").indexed(),
EdgeTable::new(MARKS, EdgeShape::Set, "text").indexed(),
EdgeTable::new(NOTES, EdgeShape::Sequence, "text"),
EdgeTable::new(BEATS, EdgeShape::Sequence, "bigint").indexed(),
];
#[cfg(feature = "mysql")]
const MYSQL_EDGES: &[EdgeTable] = &[
EdgeTable::new(TAGS, EdgeShape::Sequence, "VARCHAR(255)").indexed(),
EdgeTable::new(MARKS, EdgeShape::Set, "VARCHAR(255)").indexed(),
EdgeTable::new(NOTES, EdgeShape::Sequence, "VARCHAR(255)"),
EdgeTable::new(BEATS, EdgeShape::Sequence, "BIGINT").indexed(),
];
#[cfg(feature = "sqlite")]
impl Describe<sqlx::Sqlite> for Clip {
const BLUEPRINT: Table = Table::new(CLIP, &[Column::new(FieldName::new("id"), "INTEGER", false)])
.with_edges(SQLITE_EDGES);
}
#[cfg(feature = "postgres")]
impl Describe<sqlx::Postgres> for Clip {
const BLUEPRINT: Table = Table::new(CLIP, &[Column::new(FieldName::new("id"), "bigint", false)])
.with_edges(POSTGRES_EDGES);
}
#[cfg(feature = "mysql")]
impl Describe<sqlx::MySql> for Clip {
const BLUEPRINT: Table =
Table::new(CLIP, &[Column::new(FieldName::new("id"), "BIGINT", false)]).with_edges(MYSQL_EDGES);
}
fn type_iter<V>(v: &mut V) -> Result<(), V::Error>
where
V: TypeVisitor,
Clip: Describe<V::Db>,
{
v.next_type::<Clip>()
}
fn schema<Db>() -> Vec<String>
where
Db: super::AsyncSchemaDialect,
Clip: 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_folds_the_text_columns_that_asked_and_indexes_every_one_of_them_by_value() {
assert_eq!(
schema::<sqlx::Sqlite>(),
[
r#"CREATE TABLE IF NOT EXISTS "clip" ("id" INTEGER NOT NULL, PRIMARY KEY ("id"))"#,
r#"CREATE TABLE IF NOT EXISTS "clip_tags" ("id" INTEGER NOT NULL, "tags_ordinal" INTEGER NOT NULL, "tags" TEXT COLLATE NOCASE NOT NULL, PRIMARY KEY ("id", "tags_ordinal"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_tags_element" ON "clip_tags" ("id", "tags")"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_tags_value" ON "clip_tags" ("tags", "id")"#,
r#"CREATE TABLE IF NOT EXISTS "clip_marks" ("id" INTEGER NOT NULL, "marks" TEXT COLLATE NOCASE NOT NULL, PRIMARY KEY ("id", "marks"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_marks_value" ON "clip_marks" ("marks", "id")"#,
r#"CREATE TABLE IF NOT EXISTS "clip_notes" ("id" INTEGER NOT NULL, "notes_ordinal" INTEGER NOT NULL, "notes" TEXT NOT NULL, PRIMARY KEY ("id", "notes_ordinal"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_notes_element" ON "clip_notes" ("id", "notes")"#,
r#"CREATE TABLE IF NOT EXISTS "clip_beats" ("id" INTEGER NOT NULL, "beats_ordinal" INTEGER NOT NULL, "beats" INTEGER NOT NULL, PRIMARY KEY ("id", "beats_ordinal"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_beats_element" ON "clip_beats" ("id", "beats")"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_beats_value" ON "clip_beats" ("beats", "id")"#,
]
);
}
#[cfg(feature = "postgres")]
#[test]
fn postgres_indexes_by_value_and_writes_no_collation() {
assert_eq!(
schema::<sqlx::Postgres>(),
[
r#"CREATE TABLE IF NOT EXISTS "clip" ("id" bigint NOT NULL, PRIMARY KEY ("id"))"#,
r#"CREATE TABLE IF NOT EXISTS "clip_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_clip_tags_element" ON "clip_tags" ("id", "tags")"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_tags_value" ON "clip_tags" ("tags", "id")"#,
r#"CREATE TABLE IF NOT EXISTS "clip_marks" ("id" bigint NOT NULL, "marks" text NOT NULL, PRIMARY KEY ("id", "marks"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_marks_value" ON "clip_marks" ("marks", "id")"#,
r#"CREATE TABLE IF NOT EXISTS "clip_notes" ("id" bigint NOT NULL, "notes_ordinal" bigint NOT NULL, "notes" text NOT NULL, PRIMARY KEY ("id", "notes_ordinal"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_notes_element" ON "clip_notes" ("id", "notes")"#,
r#"CREATE TABLE IF NOT EXISTS "clip_beats" ("id" bigint NOT NULL, "beats_ordinal" bigint NOT NULL, "beats" bigint NOT NULL, PRIMARY KEY ("id", "beats_ordinal"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_beats_element" ON "clip_beats" ("id", "beats")"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_clip_beats_value" ON "clip_beats" ("beats", "id")"#,
]
);
}
#[cfg(feature = "mysql")]
#[test]
fn mysql_indexes_by_value_and_leans_on_its_own_case_insensitive_default() {
assert_eq!(
schema::<sqlx::MySql>(),
[
"CREATE TABLE IF NOT EXISTS `clip` (`id` BIGINT NOT NULL, PRIMARY KEY (`id`))",
"CREATE TABLE IF NOT EXISTS `clip_tags` (`id` BIGINT NOT NULL, `tags_ordinal` BIGINT NOT \
NULL, `tags` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`, `tags_ordinal`))",
"CREATE INDEX IF NOT EXISTS `__ig_clip_tags_element` ON `clip_tags` (`id`, `tags`)",
"CREATE INDEX IF NOT EXISTS `__ig_clip_tags_value` ON `clip_tags` (`tags`, `id`)",
"CREATE TABLE IF NOT EXISTS `clip_marks` (`id` BIGINT NOT NULL, `marks` VARCHAR(255) NOT \
NULL, PRIMARY KEY (`id`, `marks`))",
"CREATE INDEX IF NOT EXISTS `__ig_clip_marks_value` ON `clip_marks` (`marks`, `id`)",
"CREATE TABLE IF NOT EXISTS `clip_notes` (`id` BIGINT NOT NULL, `notes_ordinal` BIGINT NOT \
NULL, `notes` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`, `notes_ordinal`))",
"CREATE INDEX IF NOT EXISTS `__ig_clip_notes_element` ON `clip_notes` (`id`, `notes`)",
"CREATE TABLE IF NOT EXISTS `clip_beats` (`id` BIGINT NOT NULL, `beats_ordinal` BIGINT NOT \
NULL, `beats` BIGINT NOT NULL, PRIMARY KEY (`id`, `beats_ordinal`))",
"CREATE INDEX IF NOT EXISTS `__ig_clip_beats_element` ON `clip_beats` (`id`, `beats`)",
"CREATE INDEX IF NOT EXISTS `__ig_clip_beats_value` ON `clip_beats` (`beats`, `id`)",
]
);
}
#[cfg(feature = "sqlite")]
#[test]
fn a_collection_that_asked_for_nothing_is_byte_identical_to_what_it_rendered_before() {
const WORDLESS: &[EdgeTable] = &[EdgeTable::new(TAGS, EdgeShape::Sequence, "TEXT")];
const PLAIN: Table =
Table::new(CLIP, &[Column::new(FieldName::new("id"), "INTEGER", false)]).with_edges(WORDLESS);
assert!(!PLAIN.edges()[0].is_indexed());
assert_eq!(
PLAIN
.create::<sqlx::Sqlite>()
.expect("this descriptor renders"),
[
r#"CREATE TABLE IF NOT EXISTS "clip" ("id" INTEGER NOT NULL, PRIMARY KEY ("id"))"#,
r#"CREATE TABLE IF NOT EXISTS "clip_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_clip_tags_element" ON "clip_tags" ("id", "tags")"#,
]
);
}
#[cfg(feature = "sqlite")]
#[test]
fn the_value_led_index_is_named_for_the_access_and_not_for_a_column() {
let statements = schema::<sqlx::Sqlite>();
let names: Vec<&str> = statements
.iter()
.filter_map(|statement| {
let start = statement.strip_prefix(r#"CREATE INDEX IF NOT EXISTS ""#)?;
Some(&start[..start.find('"').expect("the quote closes")])
})
.collect();
assert_eq!(
names,
[
"__ig_clip_tags_element",
"__ig_clip_tags_value",
"__ig_clip_marks_value",
"__ig_clip_notes_element",
"__ig_clip_beats_element",
"__ig_clip_beats_value",
],
"`element` and `value` name the ACCESS — the two indexes are over the same columns in the two \
orders, so a name built from the columns could not tell them apart",
);
}
struct Album;
const ALBUM: RecordAddress = RecordAddress::new(FieldName::new("album"), &[FieldName::new("id")]);
const COVERS: &[&FieldName] = &[FieldName::new("covers")];
#[cfg(feature = "postgres")]
impl Describe<sqlx::Postgres> for Album {
const BLUEPRINT: Table = Table::new(
ALBUM,
&[
Column::new(FieldName::new("id"), "bigint", false),
Column::new(FieldName::new("covers"), "text[]", false),
],
)
.with_collection_indexes(COVERS);
}
#[cfg(feature = "sqlite")]
impl Describe<sqlx::Sqlite> for Album {
const BLUEPRINT: Table = Table::new(
ALBUM,
&[
Column::new(FieldName::new("id"), "INTEGER", false),
Column::new(FieldName::new("covers"), "TEXT", false),
],
)
.with_collection_indexes(COVERS);
}
#[cfg(feature = "postgres")]
#[test]
fn postgres_gives_an_array_column_a_gin_index_under_the_edge_tables_own_name() {
assert_eq!(
<Album as Describe<sqlx::Postgres>>::BLUEPRINT
.create::<sqlx::Postgres>()
.expect("this descriptor renders"),
[
r#"CREATE TABLE IF NOT EXISTS "album" ("id" bigint NOT NULL, "covers" text[] NOT NULL, PRIMARY KEY ("id"))"#,
r#"CREATE INDEX IF NOT EXISTS "__ig_album_covers_value" ON "album" USING GIN ("covers")"#,
]
);
}
#[cfg(feature = "sqlite")]
#[test]
fn a_dialect_with_no_access_method_renders_the_table_and_no_index() {
assert_eq!(
<Album as Describe<sqlx::Sqlite>>::BLUEPRINT
.create::<sqlx::Sqlite>()
.expect("this descriptor renders"),
[
r#"CREATE TABLE IF NOT EXISTS "album" ("id" INTEGER NOT NULL, "covers" TEXT NOT NULL, PRIMARY KEY ("id"))"#,
]
);
}
#[cfg(feature = "sqlite")]
mod live {
use sqlx::{Executor as _, Row as _, Sqlite};
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::<Sqlite>::new();
type_iter(&mut create)?;
create.run(db).await
}
async fn stocked() -> sqlx::sqlite::SqliteConnection {
let mut db = empty().await;
create(&mut db).await.expect("an empty database takes it");
for statement in [
r#"INSERT INTO "clip" ("id") VALUES (1)"#,
r#"INSERT INTO "clip_tags" ("id", "tags_ordinal", "tags") VALUES (1, 0, 'Dog')"#,
r#"INSERT INTO "clip_notes" ("id", "notes_ordinal", "notes") VALUES (1, 0, 'Dog')"#,
r#"INSERT INTO "clip_marks" ("id", "marks") VALUES (1, 'Dog')"#,
] {
db.execute(statement).await.expect("the row is written");
}
db
}
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")
}
async fn columns_of(db: &mut sqlx::sqlite::SqliteConnection, index: &str) -> Vec<String> {
sqlx::query_scalar::<_, String>("SELECT name FROM pragma_index_info(?) ORDER BY seqno")
.bind(index)
.fetch_all(db)
.await
.expect("the catalogue answers")
}
#[tokio::test]
async fn the_value_led_index_leads_on_the_value() {
let mut db = empty().await;
create(&mut db).await.expect("an empty database takes it");
assert_eq!(
indexes(&mut db).await,
[
"__ig_clip_beats_element",
"__ig_clip_beats_value",
"__ig_clip_marks_value",
"__ig_clip_notes_element",
"__ig_clip_tags_element",
"__ig_clip_tags_value",
]
);
assert_eq!(
columns_of(&mut db, "__ig_clip_tags_element").await,
["id", "tags"],
"the derived index leads on the KEY, as it always has",
);
assert_eq!(
columns_of(&mut db, "__ig_clip_tags_value").await,
["tags", "id"],
"and the word's index leads on the VALUE, which is what a seek from a value needs",
);
}
#[tokio::test]
async fn the_fold_does_not_touch_what_is_stored() {
let mut db = stocked().await;
let stored = sqlx::query_scalar::<_, String>(r#"SELECT "tags" FROM "clip_tags""#)
.fetch_one(&mut db)
.await
.expect("the row is there");
assert_eq!(stored, "Dog", "store verbatim is half of the law");
}
#[tokio::test]
async fn a_lowercase_seek_finds_the_capitalised_element() {
let mut db = stocked().await;
let found = sqlx::query_scalar::<_, i64>(r#"SELECT "id" FROM "clip_tags" WHERE "tags" = ?"#)
.bind("dog")
.fetch_all(&mut db)
.await
.expect("the query runs");
assert_eq!(
found,
[1],
"query case-tolerant is the other half of the law"
);
}
#[tokio::test]
async fn a_collection_without_the_word_keeps_its_binary_comparison() {
let mut db = stocked().await;
let found = sqlx::query_scalar::<_, i64>(r#"SELECT "id" FROM "clip_notes" WHERE "notes" = ?"#)
.bind("dog")
.fetch_all(&mut db)
.await
.expect("the query runs");
assert!(found.is_empty(), "the default is BINARY and stays BINARY");
}
#[tokio::test]
async fn the_planner_seeks_rather_than_scans() {
let mut db = stocked().await;
let plan: Vec<String> =
sqlx::query(r#"EXPLAIN QUERY PLAN SELECT "id" FROM "clip_tags" WHERE "tags" = 'dog'"#)
.fetch_all(&mut db)
.await
.expect("the planner answers")
.iter()
.map(|row| row.get::<String, _>("detail"))
.collect();
assert!(
plan
.iter()
.any(|detail| detail.contains("SEARCH") && detail.contains("__ig_clip_tags_value")),
"a seek by value enters the value-led index rather than reading it through: {plan:?}",
);
}
#[tokio::test]
async fn a_set_holds_one_element_however_it_is_capitalised() {
let mut db = stocked().await;
let second = db
.execute(r#"INSERT INTO "clip_marks" ("id", "marks") VALUES (1, 'dog')"#)
.await;
assert!(
second.is_err(),
"the set already holds this element, spelled the other way",
);
}
#[tokio::test]
async fn a_second_create_adds_no_second_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!(indexes(&mut db).await.len(), 6);
}
}