ingraph 0.1.0

A declaration-driven indexing framework: records and nodes are written as Rust types, and the storage rows, the dataflow between them and the GraphQL query surface are emitted from the declaration.
Documentation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
//! WHAT THE `indexed` WORD CREATES — the VALUE-LED index over a collection, the collation the law
//! rests on, and the byte-for-byte silence of every collection that did not write it.
//!
//! A suite of its own beside [`super::index_tests`], for the reason the `sortable` suites give one
//! crate over: what is asserted here is ONE word's effect on the schema, and holding that apart from
//! the graph's own statements keeps a change to either visible.
//!
//! # The four collections, and why it takes four
//!
//! | collection | shape | element | word | what it is here to show |
//! |---|---|---|---|---|
//! | `tags` | sequence | text | `indexed` | both indexes, the derived one and the word's, in that order |
//! | `marks` | set | text | `indexed` | a SET takes the word — its primary key leads on the key too |
//! | `notes` | sequence | text | — | the OPT-IN half: byte-identical to what it rendered before the word existed |
//! | `beats` | sequence | integer | `indexed` | the collation is per COLUMN affinity: a numeric element takes none |
//!
//! # What was MEASURED, and what was only RENDERED
//!
//! SQLite is put to an engine, and it is the dialect that has to be: it is the only one of the three
//! whose default collation does not fold case, so it is the only one this crate writes a collation
//! for — and a claim about what a collation DOES is not one a rendered string can make. The live
//! module below inserts `Dog`, reads it back verbatim, finds it by `dog`, watches the query planner
//! name the value-led index, and watches a set refuse the second spelling of one element.
//!
//! PostgreSQL and MySQL are RENDERED — this repository provisions no server. What their goldens
//! record is that neither is sent a collation, which is the census both dialects' own
//! [`case_fold`](super::AsyncSchemaDialect::case_fold) rows write down: MySQL's default `utf8mb4`
//! collation already folds, and PostgreSQL has no stock collation that does and no clause a
//! `CREATE TABLE` may write to get one.

#![cfg(any(feature = "sqlite", feature = "postgres", feature = "mysql"))]

use crate::{
  Column, Describe, EdgeShape, EdgeTable, FieldName, RecordAddress, Table, TypeVisitor,
  query::sql::Create,
};

/// A record whose whole content is collections — the row carries its key and nothing else, so every
/// statement below is about an edge table.
struct Clip;

const CLIP: RecordAddress = RecordAddress::new(FieldName::new("clip"), &[FieldName::new("id")]);

/// The four collections' paths, spelled once and shared by the three dialects' edge lists — a
/// `&[…]` written inside a call is `E0716`, which is [`Table`]'s own reason for being `const`.
const TAGS: &[&FieldName] = &[FieldName::new("tags")];
const MARKS: &[&FieldName] = &[FieldName::new("marks")];
const NOTES: &[&FieldName] = &[FieldName::new("notes")];
const BEATS: &[&FieldName] = &[FieldName::new("beats")];

/// The four collections in the order the descriptor names them — the order the walk sends them in,
/// which is what makes the goldens readable as a sequence rather than as a set.
///
/// Written once per dialect because the ELEMENT's stored word is the dialect's; nothing else about
/// the four differs, so the three lists sit beside each other and the words are the diff.
#[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);
}

/// The walk a graph root writes, over the one node this suite declares.
fn type_iter<V>(v: &mut V) -> Result<(), V::Error>
where
  V: TypeVisitor,
  Clip: Describe<V::Db>,
{
  v.next_type::<Clip>()
}

/// Every statement the node renders for one dialect, in walk order.
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()
}

/// **SQLITE, WHOLE** — the collation on the two TEXT collections that wrote the word, the value-led
/// index beside each, and neither on the two that did not ask for one apiece.
///
/// Read it as four blocks. `tags` takes `COLLATE NOCASE` and BOTH indexes; `marks` takes the
/// collation and the ONE index a set can have, its key already being `(id, marks)`; `notes` is the
/// control and carries the exact text a collection rendered before this word existed; `beats` takes
/// the index and NO collation, its column being INTEGER affinity.
#[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")"#,
    ]
  );
}

/// **POSTGRESQL SENDS NO COLLATION**, and that is the census rather than an omission: no stock
/// collation here folds case, and the two forms that would — the `citext` extension and a
/// nondeterministic `CREATE COLLATION` — are a database-wide install rather than a clause a
/// declaration's word may write. So the value-led path is exact-match on this dialect, and the
/// statements differ from SQLite's in the TYPE words alone.
#[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")"#,
    ]
  );
}

/// **MYSQL SENDS NO COLLATION EITHER, and for the opposite reason**: its own default already folds.
/// `utf8mb4`'s default collation is `utf8mb4_0900_ai_ci` on 8.0 and `utf8mb4_general_ci` on 5.7 —
/// both case-insensitive — so `WHERE v = 'dog'` matches `'Dog'` and uses the index below with
/// nothing added here. Writing one anyway would pin a version-specific identifier into DDL no test
/// in this repository can put to a server.
///
/// The backtick is this dialect's identifier bracket, and it brackets the derived index names
/// exactly as it brackets the tables.
#[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`)",
    ]
  );
}

/// **A COLLECTION THAT WROTE NO WORD RENDERS WHAT IT ALWAYS RENDERED**, asserted against a LITERAL
/// rather than against its indexed neighbour.
///
/// The goldens above already carry `notes`, so this adds one thing they cannot: it builds the same
/// collection in a descriptor of its own and compares the whole statement list to text copied from
/// what the word-free renderer emitted. A regression that widened the collation or the index to
/// every collection would turn this red on its own, in a test whose subject is exactly the promise
/// the word makes — *write amplification is a choice the declaration makes* — rather than as a
/// difference a reader has to notice inside a longer list.
#[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")"#,
    ]
  );
}

/// **THE DERIVED NAME WEARS THE DOMAIN PREFIX AND THE ACCESS WORD**, pinned as its own value for the
/// reason [`super::index_tests`]' prefix test states: a name checked only as a substring of the
/// statement carrying it would survive a de-prefixing.
///
/// `__ig_<edge>_value` is the whole rule, and `clip_tags` is this suite's own table, so the derived
/// string is written out here rather than inferred.
#[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",
  );
}

/// The ARRAY-STORAGE half of the same word: a collection this database keeps in a COLUMN of the row,
/// and the access method its own catalogue names for one.
///
/// # Why all three dialects carry the descriptor
///
/// The list is [`Table::with_collection_indexes`] and the method is
/// [`AsyncSchemaDialect::COLLECTION_INDEX`](super::AsyncSchemaDialect::COLLECTION_INDEX), which is
/// `None` on the two dialects with no array type. Declaring it on all three is what turns *SQLite
/// renders nothing for a method it has not got* from an assumption into an assertion — the shape
/// [`SORT_KEY_INDEX`](super::AsyncSchemaDialect::SORT_KEY_INDEX) already has one suite over, where
/// MySQL loses the companion index and keeps the order.
///
/// A `text[]` column is a fiction on SQLite and MySQL, and it is a HARMLESS one here: what this
/// fixture puts to them is the access method and never the type word, which no assertion below
/// reads.
///
/// It is a HAND-WRITTEN descriptor because no emission can reach this shape today: a record whose
/// Postgres collection rides an array column gets no blueprint at all — `Vec<T>` has no `StoredAs`
/// row, the `CREATE TABLE` spelling and the catalogue spelling of an array being two different
/// strings — which `emit::query`'s roster note measures one crate over. The framework's side is
/// written and the day that wall comes down is the day the derive reaches it.
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);
}

/// **POSTGRESQL RENDERS THE ACCESS METHOD**, and the NAME is the string an edge table for the same
/// collection would have worn: `__ig_album_covers_value`, whether `album.covers` is a second table
/// or an array column. One collection, one containment index, one name in whichever catalogue is
/// holding it.
#[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")"#,
    ]
  );
}

/// **A DIALECT WITH NO ACCESS METHOD RENDERS NOTHING FOR THE LIST**, and keeps the table it declared.
///
/// SQLite has no array type, so no collection rides a column of its rows and the list is empty there
/// by construction — this fixture writes one anyway, which is the only way to assert that a
/// descriptor carrying one still produces the `CREATE TABLE` alone rather than a statement naming a
/// method this engine would refuse.
#[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"))"#,
    ]
  );
}

/// The half no statement's text can show: what the collation DOES, put to a real engine.
#[cfg(feature = "sqlite")]
mod live {
  use sqlx::{Executor as _, Row as _, Sqlite};

  use super::type_iter;
  use crate::query::sql::{Create, SqlSchemaError};

  /// An EMPTY in-memory database, on ONE connection.
  ///
  /// A bare connection and never a `Pool`, for the reason the suites beside this one state: this
  /// crate enables no sqlx runtime, so a pool would panic spawning its reaper. No foreign-key
  /// pragma — this suite declares no provenance table.
  async fn empty() -> sqlx::sqlite::SqliteConnection {
    use sqlx::Connection as _;

    sqlx::sqlite::SqliteConnection::connect("sqlite::memory:")
      .await
      .expect("an in-memory database")
  }

  /// Create this suite's schema on `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
  }

  /// A database carrying the schema and one row, with `Dog` written into every text collection —
  /// VERBATIM, which is the half of the law the fold must not touch.
  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
  }

  /// Every index SQLite holds that a `CREATE INDEX` made, in name order. The implicit ones a
  /// `UNIQUE` or a primary key builds carry no `sql`, which is what tells them apart.
  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")
  }

  /// Which columns SQLite says one index is over, IN INDEX ORDER — the only party that can say the
  /// value-led index leads on the value, which no name can.
  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")
  }

  /// **THE ENGINE BUILDS BOTH INDEXES AND EACH LEADS WHERE THE DECLARATION SAID.** The names alone
  /// prove nothing about the order, and the order is the entire content of the word.
  #[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",
    );
  }

  /// **STORED VERBATIM.** The collation decides comparison and ordering; the bytes SQLite writes are
  /// the bytes it was given. A fold that had reached the STORAGE would have made the word lossy, and
  /// this is the assertion that says it did not.
  #[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");
  }

  /// **`dog` FINDS `Dog`, AND THE PLAIN COMPARISON IS WHAT FINDS IT.** No `COLLATE` operator is
  /// written into the query: the column carries the collation, so SQLite's second
  /// collating-sequence rule assigns it to the comparison and an unchanged `WHERE v = ?` folds.
  ///
  /// That is the whole argument for putting the clause on the COLUMN rather than on the index — see
  /// [`case_fold`](crate::query::sql::AsyncSchemaDialect::case_fold), where the four rules are — and
  /// this test is what would go red if it moved.
  #[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"
    );
  }

  /// **THE COLLECTION THAT ASKED FOR NOTHING DOES NOT FOLD**, which is what makes the assertion above
  /// about the WORD and not about SQLite.
  ///
  /// `notes` holds the same `Dog` in the same shape of table, one word apart. A regression that put
  /// the collation on every collection would turn this red rather than passing silently.
  #[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");
  }

  /// **THE PLANNER SEARCHES RATHER THAN SCANS**, which is the claim the word is bought for: without
  /// the index the same query reads every element of every row.
  ///
  /// `EXPLAIN QUERY PLAN` is asked rather than trusted from the schema, and the assertion is
  /// `SEARCH` rather than the index's NAME — the difference is the whole test. An index whose
  /// collating sequence does not match the comparison's is one the planner will not SEEK with, and
  /// it may still SCAN it as a covering index: the name appears in the plan either way. MEASURED —
  /// a collation moved from the column onto the index turns this line from `SEARCH … (tags=?)` into
  /// `SCAN … USING COVERING INDEX`, and only the stricter assertion sees it.
  #[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:?}",
    );
  }

  /// **A SET FOLDS AT ITS IDENTITY**, and this is the second, independent reason the collation
  /// belongs on the column.
  ///
  /// A set's rows ARE its primary key, so the collation reaches the key. `Dog` and `dog` are
  /// therefore ONE element of one set and the second write is refused — which is what keeps the
  /// value-led seek from returning the same parent row twice. A collation written on the index alone
  /// would have admitted both rows and left that duplicate for every caller to deduplicate.
  #[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",
    );
  }

  /// **A SECOND CREATE IS THE SAME CREATE.** `IF NOT EXISTS` is what makes a boot sequence safe to
  /// re-run, and the word adds statements to that sequence rather than a new kind of one.
  #[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);
  }
}