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
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
//! The SCHEMA lane: the statements a [`Table`] renders, the two visitors a graph's walk drives,
//! and the drift audit one of them runs against a live database.
//!
//! ```text
//! create     CREATE TABLE IF NOT EXISTS <table> (<columns…>[, <companions…>],
//!                                                 PRIMARY KEY (<key…>)[, UNIQUE (<id>)])
//!            CREATE UNIQUE INDEX IF NOT EXISTS __ig_<table>_<field> ON <table> (<field>)  per unique field
//!            CREATE INDEX IF NOT EXISTS __ig_<table>_<field> ON <table> (<field>)     per indexed field
//!                                            that is not ALSO unique — see `create`'s own note
//!            CREATE INDEX IF NOT EXISTS __ig_<table>_<companion> ON <table> (<companion>)
//!                                                          per companion, where the dialect can
//!            CREATE TABLE IF NOT EXISTS <side>  (<id>, <ancestors…>, PRIMARY KEY (<id>),
//!                                                FOREIGN KEY (<id>) REFERENCES <table> (<id>))
//!            CREATE INDEX IF NOT EXISTS __ig_<table>_<field>_value ON <table>
//!                                            USING <method> (<field>)   per indexed collection
//!                                                          COLUMN, where the dialect keeps one
//!            CREATE TABLE IF NOT EXISTS <edge>  (<key…>, <ordinal>, <value…>, PRIMARY KEY (…))
//!            CREATE INDEX IF NOT EXISTS __ig_<edge>_element ON <edge> (<key…>, <value…>)  per sequence
//!            CREATE INDEX IF NOT EXISTS __ig_<edge>_value   ON <edge> (<value…>, <key…>)
//!                                                          per collection that wrote `indexed`
//!
//! validate   SELECT <name>, <type> FROM <this dialect's own catalogue> WHERE <table>
//! ```
//!
//! # Every name here is READ, and that closes a gap this crate had written down
//!
//! [`comparison::sql`](crate::comparison::sql)'s edge module lists four parties that spell an edge
//! table's names, records that three of them DERIVE, and names the fourth — a hand-written migration
//! — as the one that can therefore be wrong. This module is the fifth party and it derives: the
//! table and the key columns are [`RecordAddress`], the edge table, its value column and its ordinal
//! are [`edge`]'s, and the type each column is stored at is the descriptor's. Nothing in a
//! `CREATE TABLE` is joined here.
//!
//! # A create is a WALK and then a RUN, and the order is the write lane's own
//!
//! [`Create`] accumulates the whole graph's statements while the walk is running and executes
//! none of them; [`Create::run`] sends them afterwards. So a descriptor the dialect cannot render
//! leaves nothing half-created, and the statement sequence IS readable from a test — which is what
//! the goldens beside this module assert.
//!
//! # The foreign key is EMITTED, and enforcing it is the CONNECTION's posture
//!
//! A provenance table's rows point back at the common row by its id, and the constraint saying so
//! is rendered here — so an orphan provenance row is the engine's own refusal rather than a rule
//! this crate re-checks on every read. PostgreSQL and MySQL/InnoDB enforce one as declared.
//!
//! **SQLite parses a foreign key and ignores it unless the connection asks for it.** `PRAGMA
//! foreign_keys = ON` is per connection and defaults OFF, for backwards compatibility, so a caller
//! that wants the constraint it declared sends the pragma on every connection it opens — which is
//! the posture, and it is the caller's because this crate opens no connection. Nothing here can set
//! it for them: a handle is a parameter everywhere in this lane, which is the injection seam's own
//! law. The pragma is also refused inside a transaction, so it belongs where the connection is made
//! rather than where a statement is run. `ingraph`'s own corpus sends it in the one place it opens a
//! connection, and the FK's teeth are measured there.
//!
//! # No transaction, and that is a dialect fact rather than an omission
//!
//! SQLite and PostgreSQL run DDL inside a transaction; MySQL commits implicitly at every
//! `CREATE TABLE` and cannot roll one back. A bracket around this sequence would therefore be a
//! promise one shipped dialect cannot keep, and a half-created schema is what `IF NOT EXISTS` makes
//! survivable: the same run repeated finishes what the first one started, which is the property the
//! clause is there for.
//!
//! # What was MEASURED, and what was only RENDERED
//!
//! | engine | what was put to it |
//! |---|---|
//! | **SQLite**, live, in-memory | the whole lane: a three-node graph created — a root, a child and a fan-in under the split layout — the tables and indexes read back out of the catalogue, a second create over the same connection, the audit passing on what the create made and naming the column an `ALTER TABLE` moved from a common table and from a provenance one, a foreign key REFUSING an orphan provenance row and taking that same row once its common row exists, and the WRITE and READ lanes run against a schema no test hand-wrote |
//! | **PostgreSQL**, **MySQL** | the rendered TEXT of every statement, and nothing else — this repository provisions no server, so the goldens beside this module record what each dialect is SENT rather than what an engine answered |

use core::{convert::Infallible, marker::PhantomData};

use super::{SqlDialect, hydrate::edge};
use crate::{
  Column, CompositeUnique, Describe, EdgeTable, FieldName, Path, SideTable, Table, TableColumns,
  comparison::sql::{
    ALIAS_PREFIX, QuoteIdent, SqlLoweringError, column_name, fold_shapes, fold_shapes_under,
    quote_ident, quote_path,
  },
};

/// Every column of the row's OWN table, in the order the DDL declares them: its ordinal, the
/// [`Path`] naming it, the word this database stores it at, and whether it holds `NULL`.
///
/// # ONE walk for both roads a descriptor is written
///
/// A FLAT descriptor's column is a path of ONE segment — its own name, which it already carries —
/// and a SHAPED one's is the field's segments descended through, which is
/// [`fold_shapes`](crate::comparison::sql) and therefore the same descent a `SELECT` list makes. So
/// the two roads reach `push_segments` identically and a shaped blueprint cannot declare a column
/// its flat twin would not, nor one the read lane would not find.
///
/// A shaped row's nullability is the composition of TWO grains — the field's `optional` marker and
/// the leaf's own [`ColumnShape::Nullable`](crate::ColumnShape::Nullable) — which is why this walk
/// asks for the shape and not only the path. A flat descriptor states the composed answer directly,
/// one [`Column`] per column, so it has nothing to compose.
///
/// The path is handed over UNRENDERED because a column is spelled twice here: bracketed into a
/// `CREATE TABLE`, and plain against a catalogue that answers a column's real name. Rendering it
/// inside this walk would have meant one of the two callers un-doing the other's quoting.
///
/// COMPANION columns are not among these — see [`Shadow`](crate::Shadow), where the partition is —
/// and both callers append them themselves, being columns of the table and none of the record's.
fn declared_columns(table: &Table, each: &mut impl FnMut(usize, &Path<'_>, &'static str, bool)) {
  match table.columns() {
    TableColumns::Flat(columns) => {
      for (at, column) in columns.iter().enumerate() {
        each(
          at,
          &Path::under(column.name(), None),
          column.ty(),
          column.nullable(),
        );
      }
    }
    TableColumns::Shaped {
      shapes,
      words,
      nulls,
    } => {
      let mut at = 0;
      // Per FIELD, because that is the grain the words and the DECLARATION's nullability are stated
      // at. So the walk is run one field at a time and the LEAF index inside it is what indexes that
      // field's own word list — which `Table::shaped` has already asserted is as long as the field
      // is wide.
      for (which, shape) in shapes.iter().enumerate() {
        let (words, optional) = (words[which], nulls[which]);
        let leaves = fold_shapes(core::slice::from_ref(shape), 0, &mut |leaf, path, shape| {
          // THE TWO GRAINS, COMPOSED. `optional` is the declaration's — the `optional` marker, which
          // says the whole field may be absent — and `self_nullable` is the TYPE's, which says this
          // one column holds `NULL` whatever the field is. A column admits `NULL` when either says
          // so. This is the ONE reader that asks the leaf about nullability rather than about the
          // value, which is why a `ColumnShape::Derived` leaf under a required field takes
          // `NOT NULL` here while every structural walk still passes it over.
          each(
            at + leaf,
            path,
            words[leaf],
            optional || shape.self_nullable(),
          );
          leaf + 1
        });
        at += leaves;
      }
    }
  }
}

/// The word an index over an edge table is named after — the column such a table holds.
///
/// One index exists per sequence's edge table and it is over `(key…, value)`, so a name built from
/// the value COLUMN would have to re-join the collection's path, which is
/// [`Backend::write_column`](crate::Backend::write_column)'s operation and not an identifier this
/// layer may rebuild. The edge table's own name already carries that path, so the index is named
/// after the TABLE and this word says which of its columns the index is for.
const ELEMENT: &str = "element";

/// The word the VALUE-LED index over a collection wears — [`ELEMENT`]'s sibling, naming the ACCESS
/// rather than a second column.
///
/// The two indexes are over the same columns in the two orders, so a name built from the columns
/// could not tell them apart. What differs is which end a seek enters from: `element` leads on the
/// KEY and answers *does THIS row's collection hold it*, `value` leads on the VALUE and answers
/// *WHICH rows' collections hold it*.
///
/// It is spelled once for BOTH storage forms. An edge table's name is the row's table run together
/// with the collection's path, so `__ig_<edge>_value` and the array column's
/// `__ig_<table>_<field>_value` are the same identifier for the same collection — see
/// [`Table::with_collection_indexes`], where that is the property rather than the coincidence.
const VALUE: &str = "value";

/// What a schema needs of a SQL dialect ON TOP of what a query needs.
///
/// Two facts and a statement, each of them the whole of what the three shipped dialects differ in
/// here — the split [`QuoteIdent`] and [`BindCeiling`](crate::BindCeiling) already make: a dialect
/// states its answer and the party that spends it is written once.
///
/// # It is a SEPARATE trait from [`SqlDialect`]'s bundle
///
/// Only this lane spends it. Obliging every caller of every read lane to prove a dialect can name
/// its ordinal type would be the cost that bundle's own note weighs and declines for its three
/// missing predicates, and [`Upsert`](super::Upsert) is already the precedent one lane over.
///
/// # The two I/O members are here so that NO CALLER carries a driver predicate
///
/// Running a statement needs `&mut Db::Connection: Executor`, and decoding a catalogue row needs
/// `String: Decode + Type` and `usize: ColumnIndex` — four predicates whose subject is not the
/// dialect, so none can be a supertrait, and stating them in this trait's `where` clause would
/// oblige every use of the bound to prove all four and hand none of them back. That is the
/// measurement [`SqlDialect`]'s own note records.
///
/// Written as METHODS instead, they are discharged once per dialect at a CONCRETE type, and a party
/// that drives a walk — a graph root's emitted `init`, a hand-written boot sequence — writes
/// `Db: AsyncSchemaDialect` and nothing else. The bodies are one line each: the work is
/// [`send`] and [`read`], written once and generic, which is the arrangement
/// [`push_excluded`](super::write) already has one lane over.
///
/// Neither returns a `Send` future. Nothing in this lane spawns — a schema is created on the
/// connection the caller acquired — so the bound would be asked for and never spent.
///
/// The trait is public and unsealed, and this is the shape a fourth dialect joins by: it names two
/// type words, writes the statement its own catalogue answers columns with, and hands the two I/O
/// members to the helpers below.
///
/// # [`Habitat<Blueprint = Table>`](crate::Habitat) is a SUPERTRAIT, and it is a statement about the
/// family
///
/// Every SQL dialect's home for a node is a table — that is what makes them one family and what this
/// whole module renders — so the fact is stated once here rather than restated as a `where` row at
/// every walk site. What it buys is that a party writing `Db: AsyncSchemaDialect` still writes
/// nothing else, which is the promise the paragraph above makes; what it costs a fourth dialect is
/// the one line its three siblings write beside their own impls.
///
/// It is NOT a blanket. `impl<D: AsyncSchemaDialect> Habitat for D` would give a target that answers
/// two seams two impls and `E0119` — see [`Habitat`](crate::Habitat), where the measurement is.
pub trait AsyncSchemaDialect: SqlDialect + crate::Habitat<Blueprint = Table> {
  /// The type an edge table's ORDINAL column is stored at.
  ///
  /// A position is bound as an `i64` — see
  /// [`push_ordinal`](crate::comparison::sql::SqlEdgeComparison), which the write lane and the
  /// positional predicates share — so this is that width in this dialect's own words.
  const ORDINAL: &'static str;

  /// The type a DISCRIMINANT column is stored at.
  ///
  /// The discriminant is THE WORD — one vocabulary across every kind a node can be tagged by — so
  /// the column is text in every dialect and only the spelling differs.
  ///
  /// A DECLARATION reads it, not this module: the discriminant is one of the common table's own
  /// columns, so it is declared beside them and rendered like them — see
  /// [`Table::with_provenance`], which refuses a descriptor whose column list does not carry it.
  /// What this const is for is that the declaration does not have to know each dialect's word for
  /// text.
  const DISCRIMINANT: &'static str;

  /// The type an ORDER-PRESERVING COMPANION COLUMN is stored at — the binary column a `sortable`
  /// collection is mirrored into.
  ///
  /// It has to be the dialect's BINARY type and not its text one, and the difference is a wrong
  /// order rather than a wrong bind: a text column compares under a COLLATION, so the same bytes
  /// rank one way on a `C` database and another on a `en_US.UTF-8` one, where
  /// [`collection_sort_key`](crate::collection_sort_key)'s frame is byte order and nothing else.
  /// [`ShadowOrdering`](crate::query::ShadowOrdering) is where a dialect states that its binary
  /// ordering IS byte order; this is the column that ordering is over.
  const SORT_KEY: &'static str;

  /// Whether this dialect can create an index over [`SORT_KEY`](Self::SORT_KEY) with NO key length.
  ///
  /// The one row that is `false` is MySQL, and it is measured rather than assumed:
  /// `CREATE INDEX … ON t (blob_col)` is `ERROR 1170 (42000): BLOB/TEXT column used in key
  /// specification without a key length`. A PREFIX index is what MySQL offers instead and it is not
  /// taken — a prefix cannot decide the order past its own width, so an `ORDER BY` over one still
  /// sorts and the index would buy a scan it cannot skip while promising otherwise.
  ///
  /// A dialect answering `false` LOSES THE INDEX AND KEEPS THE ORDER. That is the whole consequence:
  /// the companion column is created, written, and ordered by; the engine sorts rather than reads a
  /// sorted structure. What the word buys — the order living in the statement, where it was a
  /// remainder — is unaffected.
  const SORT_KEY_INDEX: bool;

  /// The ACCESS METHOD a containment index over a collection COLUMN is created with, and [`None`]
  /// where this dialect keeps no collection in a column.
  ///
  /// ```sql
  /// CREATE INDEX … ON "clip" USING GIN ("tags")
  /// ```
  ///
  /// PostgreSQL is the one row that answers, and `GIN` is the method its own documentation names for
  /// an array: a GIN entry per ELEMENT, which is what `@>` and `&&` seek by, where a B-tree over an
  /// array column indexes the whole array as one value and can answer only equality of the entire
  /// list. SQLite and MySQL answer [`None`] because they have no array type at all — every
  /// collection they store is a second table, so the containment index there is
  /// [`EdgeTable::indexed`](crate::EdgeTable::indexed)'s and a descriptor for them carries no such
  /// column to name.
  ///
  /// The word is rendered VERBATIM into the statement, so a dialect states it in its own spelling.
  /// It is never a caller's string — the only party that writes one is a dialect impl, which is a
  /// `const` in this crate or in a fourth dialect's own.
  const COLLECTION_INDEX: Option<&'static str>;

  /// The COLLATION CLAUSE an `indexed` collection's element column takes here, for an element stored
  /// at `ty` — the empty string where this dialect needs none.
  ///
  /// # The law is *store verbatim, seek case-tolerant*, and only one dialect has to write a word for
  /// it
  ///
  /// | dialect | what it answers, and what was measured |
  /// |---|---|
  /// | **SQLite** | `" COLLATE NOCASE"` for a column of TEXT affinity. Its default is `BINARY`, and `BINARY` is the only default among the three that does not fold |
  /// | **MySQL** | `""`. The default collation for `utf8mb4` is `utf8mb4_0900_ai_ci` on 8.0 and `utf8mb4_general_ci` on 5.7 — both `_ci`, both already folding |
  /// | **PostgreSQL** | `""`. No stock collation folds, and the two that would (`citext`, a nondeterministic `CREATE COLLATION`) are an extension and a database-wide install — not a clause a declaration's word may write |
  ///
  /// # It goes on the COLUMN, and that is a MEASUREMENT rather than a preference
  ///
  /// SQLite assigns a collating sequence to a binary comparison by four ordered rules: an explicit
  /// `COLLATE` operator on either operand, else the LEFT operand's column collation, else the
  /// RIGHT's, else `BINARY`. And an index serves a `=` seek only when its collating sequence is the
  /// comparison's. So `CREATE INDEX … (v COLLATE NOCASE)` over a plain `TEXT` column builds an index
  /// that a plain `WHERE v = ?` — `BINARY`, by the fourth rule — can never use, and `'Dog'` still
  /// does not match `'dog'`; making the query write the operator instead would be a change to the
  /// comparison lane, which this word is not. Declared on the COLUMN, the second rule fires, the
  /// unchanged `WHERE v = ?` folds, and the index inherits the column's collation because the
  /// `CREATE INDEX` names none.
  ///
  /// The second reason is a SET's, and it is about correctness rather than about a plan: a set's
  /// `PRIMARY KEY` covers the element column. With the fold on the column, `{"Dog", "dog"}` is one
  /// element and the value-led seek returns the parent row once. With the fold on the index alone
  /// the set would admit both rows and the seek would return that parent twice.
  ///
  /// STORAGE IS UNTOUCHED. A collation decides comparison and ordering; the bytes SQLite writes are
  /// the bytes it was given, which is the *store verbatim* half of the law.
  ///
  /// Its LIMIT is written down rather than discovered: SQLite's built-in `NOCASE` folds ASCII `A`–`Z`
  /// and nothing else, so `'Straße'` and `'STRASSE'` remain two elements.
  ///
  /// # `ty` is the DECLARED word, and the affinity rule reads it
  ///
  /// A collation applies to text, so the clause is answered per column: a composite element's TEXT
  /// segments take it and its numeric ones do not. SQLite decides that by AFFINITY rather than by an
  /// exact word — a declaration may spell `type = "VARCHAR(40)"` — which is its own documented rule,
  /// read here in the two arms that can produce TEXT: a word containing `INT` is INTEGER affinity
  /// (rule 1, which runs first), and otherwise a word containing `CHAR`, `CLOB` or `TEXT` is TEXT
  /// affinity (rule 2).
  fn case_fold(ty: &str) -> &'static str;

  /// The statement answering which columns this dialect stores for `table`, and at what types.
  ///
  /// TWO columns in the select list, in this order: the column's name and the type the catalogue
  /// reports it at. The audit reads them positionally, which is what lets three catalogues with
  /// three sets of column names answer one question.
  ///
  /// The table name is BOUND rather than written into the text. It is a `&'static FieldName` off a
  /// descriptor and could not carry a caller's string, so the bind is not closing an injection —
  /// what it closes is a second escaping rule, since a catalogue compares the name as a VALUE and a
  /// quoted identifier is not the same string.
  fn probe(table: &'static FieldName) -> sqlx::QueryBuilder<Self>;

  /// Send one rendered DDL statement.
  ///
  /// # Errors
  ///
  /// The driver's own refusal.
  fn execute(
    statement: &str,
    conn: &mut Self::Connection,
  ) -> impl Future<Output = Result<(), sqlx::Error>>;

  /// Run [`probe`](Self::probe) and read its two columns out — each stored column's name, and the
  /// type this database reports it at.
  ///
  /// # Errors
  ///
  /// The driver's own refusal, and whatever it refuses the decode with.
  fn stored(
    table: &'static FieldName,
    conn: &mut Self::Connection,
  ) -> impl Future<Output = Result<Vec<(String, String)>, sqlx::Error>>;
}

/// Send one statement on `conn` — the body every dialect's [`AsyncSchemaDialect::execute`] is.
///
/// Written once and generic, so the driver predicates are discharged at the concrete dialect that
/// calls it rather than at every party driving a walk. A [`QueryBuilder`](sqlx::QueryBuilder) rather
/// than `sqlx::query`, because DDL carries no binds and this is the one way the family already
/// reaches an executor.
///
/// Compiled only where a DIALECT is, its only callers being their impls — the `sqlx` feature is the
/// family ROOT and carries none, so in that build there is nothing to call this and it is not part
/// of it. The same gate [`push_excluded`](super::write) carries one lane over.
#[cfg(any(feature = "sqlite", feature = "postgres", feature = "mysql"))]
async fn send<Db>(statement: &str, conn: &mut Db::Connection) -> Result<(), sqlx::Error>
where
  Db: AsyncSchemaDialect,
  for<'e> &'e mut Db::Connection: sqlx::Executor<'e, Database = Db>,
  Db::Arguments: sqlx::IntoArguments<Db>,
{
  let mut qb = sqlx::QueryBuilder::<Db>::new(statement);
  sqlx::Executor::execute(&mut *conn, qb.build()).await?;
  Ok(())
}

/// Run one dialect's catalogue probe and read the two columns out — the body every dialect's
/// [`AsyncSchemaDialect::stored`] is.
///
/// The columns are read POSITIONALLY, which is what lets three catalogues with three sets of column
/// names answer one question: `pragma_table_info`'s `name`/`type`, `information_schema`'s
/// `column_name`/`data_type`, and MySQL's `COLUMN_NAME`/`COLUMN_TYPE` are one select list as far as
/// this is concerned.
///
/// Gated for [`send`]'s reason, and with it.
#[cfg(any(feature = "sqlite", feature = "postgres", feature = "mysql"))]
async fn read<Db>(
  table: &'static FieldName,
  conn: &mut Db::Connection,
) -> Result<Vec<(String, String)>, sqlx::Error>
where
  Db: AsyncSchemaDialect,
  for<'e> &'e mut Db::Connection: sqlx::Executor<'e, Database = Db>,
  Db::Arguments: sqlx::IntoArguments<Db>,
  for<'r> String: sqlx::Decode<'r, Db> + sqlx::Type<Db>,
  usize: sqlx::ColumnIndex<Db::Row>,
{
  use sqlx::Row as _;

  sqlx::Executor::fetch_all(&mut *conn, Db::probe(table).build())
    .await?
    .iter()
    .map(|row| Ok((row.try_get::<String, _>(0)?, row.try_get::<String, _>(1)?)))
    .collect()
}

/// A SQL dialect's HOME for a node is a [`Table`], and it is stated per DIALECT rather than by a
/// blanket over [`AsyncSchemaDialect`].
///
/// The blanket is spellable and is refused for [`Habitat`](crate::Habitat)'s own measured reason: a
/// target may answer two seams at once — one connection-manager with both a relational face and a
/// vector extension is one type — so a per-kind blanket would give such a target two impls and
/// `E0119`. Three dialects, three lines, and a fourth dialect's author writes the fourth.
#[cfg(feature = "sqlite")]
impl crate::Habitat for sqlx::Sqlite {
  type Blueprint = Table;
}

/// SQLite answers out of `pragma_table_info`, which is a table-valued function since 3.16 and
/// reports the DECLARED type verbatim — so a drift audit against this dialect compares the very word
/// the `CREATE TABLE` used.
#[cfg(feature = "sqlite")]
impl AsyncSchemaDialect for sqlx::Sqlite {
  const ORDINAL: &'static str = "INTEGER";
  const DISCRIMINANT: &'static str = "TEXT";
  const SORT_KEY: &'static str = "BLOB";
  // SQLite compares two `BLOB`s with `memcmp` and takes the shorter to be less where one is a prefix
  // of the other, which IS the companion frame's rule, and it indexes one with no key length.
  const SORT_KEY_INDEX: bool = true;
  // NO ARRAY TYPE, so no collection rides a column of the row here and there is nothing for an
  // access method to be over — every collection SQLite stores is already a second table.
  const COLLECTION_INDEX: Option<&'static str> = None;

  /// SQLite is the ONE dialect that writes a collation for the word, its default being the only one
  /// of the three that does not fold — see [`AsyncSchemaDialect::case_fold`], where the four
  /// collating-sequence rules and the set's-identity argument are.
  ///
  /// The affinity test is SQLite's own, in its two TEXT-producing arms and in its own order: `INT`
  /// anywhere makes the column INTEGER (rule 1), and otherwise `CHAR`, `CLOB` or `TEXT` anywhere
  /// makes it TEXT (rule 2). Case-insensitively, because a declared type name is compared that way.
  fn case_fold(ty: &str) -> &'static str {
    let has = |needle: &str| {
      ty.as_bytes()
        .windows(needle.len())
        .any(|window| window.eq_ignore_ascii_case(needle.as_bytes()))
    };
    if !has("INT") && (has("CHAR") || has("CLOB") || has("TEXT")) {
      " COLLATE NOCASE"
    } else {
      ""
    }
  }

  fn probe(table: &'static FieldName) -> sqlx::QueryBuilder<Self> {
    let mut qb = sqlx::QueryBuilder::new(r#"SELECT "name", "type" FROM pragma_table_info("#);
    qb.push_bind(table.as_str());
    qb.push(")");
    qb
  }
  fn execute(
    statement: &str,
    conn: &mut Self::Connection,
  ) -> impl Future<Output = Result<(), sqlx::Error>> {
    send::<Self>(statement, conn)
  }

  fn stored(
    table: &'static FieldName,
    conn: &mut Self::Connection,
  ) -> impl Future<Output = Result<Vec<(String, String)>, sqlx::Error>> {
    read::<Self>(table, conn)
  }
}

/// PostgreSQL's home for a node, per the dialect above.
#[cfg(feature = "postgres")]
impl crate::Habitat for sqlx::Postgres {
  type Blueprint = Table;
}

/// PostgreSQL answers out of `information_schema`, whose `data_type` is the SQL standard's NAME for
/// the type rather than the word the declaration used — `integer` for an `INTEGER`, `text` for a
/// `TEXT`. So a descriptor for this dialect states the standard's spelling, which is what the
/// goldens beside this module carry.
#[cfg(feature = "postgres")]
impl AsyncSchemaDialect for sqlx::Postgres {
  const ORDINAL: &'static str = "bigint";
  const DISCRIMINANT: &'static str = "text";
  const SORT_KEY: &'static str = "bytea";
  // `bytea` compares bytewise under every collation — a collation is a text property and this column
  // is not text — and takes an index with no key length.
  const SORT_KEY_INDEX: bool = true;
  // THE ONE DIALECT WITH AN ARRAY COLUMN, and `GIN` is the method that indexes its ELEMENTS: a B-tree
  // over an array indexes the whole list as one value and answers only equality of the entire list,
  // where a GIN entry per element is what `@>` and `&&` seek by.
  const COLLECTION_INDEX: Option<&'static str> = Some("GIN");

  /// PostgreSQL writes no collation for the word, and the reason is that neither answer it could
  /// give is one a `CREATE TABLE` may make: every stock collation here is deterministic and
  /// case-SENSITIVE, and the two forms that fold — the `citext` extension, and a nondeterministic
  /// `CREATE COLLATION (deterministic = false)`, which also forecloses pattern matching on the
  /// column — are a database-wide install rather than a clause behind a declaration's word.
  ///
  /// So the value-led path is EXACT-MATCH on this dialect. See
  /// [`AsyncSchemaDialect::case_fold`], where the three answers are tabled together.
  fn case_fold(_: &str) -> &'static str {
    ""
  }

  fn probe(table: &'static FieldName) -> sqlx::QueryBuilder<Self> {
    let mut qb = sqlx::QueryBuilder::new(
      "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = ",
    );
    qb.push_bind(table.as_str());
    qb
  }
  fn execute(
    statement: &str,
    conn: &mut Self::Connection,
  ) -> impl Future<Output = Result<(), sqlx::Error>> {
    send::<Self>(statement, conn)
  }

  fn stored(
    table: &'static FieldName,
    conn: &mut Self::Connection,
  ) -> impl Future<Output = Result<Vec<(String, String)>, sqlx::Error>> {
    read::<Self>(table, conn)
  }
}

/// MySQL's home for a node, per the two dialects above.
#[cfg(feature = "mysql")]
impl crate::Habitat for sqlx::MySql {
  type Blueprint = Table;
}

/// MySQL answers out of the same catalogue, scoped to the connection's own schema — `information_schema`
/// spans every database on the server, so an unscoped query would report a table of the same name in
/// somebody else's.
///
/// `COLUMN_TYPE` rather than `DATA_TYPE`, because the two answer different questions: `DATA_TYPE` is
/// `varchar` where the declaration wrote `VARCHAR(255)`, and a descriptor states the width it
/// declared.
#[cfg(feature = "mysql")]
impl AsyncSchemaDialect for sqlx::MySql {
  const ORDINAL: &'static str = "BIGINT";
  const DISCRIMINANT: &'static str = "VARCHAR(255)";
  const SORT_KEY: &'static str = "BLOB";
  // MySQL's `BLOB` carries the binary collation, so the ORDER is the frame's — and the INDEX is the
  // one this dialect will not give: a blob key needs a length (`ERROR 1170`), and a prefix index
  // cannot decide the order past its own width. See `SORT_KEY_INDEX`, where the trade is stated.
  const SORT_KEY_INDEX: bool = false;
  // NO ARRAY TYPE — SQLite's row, for SQLite's reason. MySQL's `JSON` is not one: no index seeks
  // into it without a generated column, which is a second declared column rather than an access
  // method over the one that is there.
  const COLLECTION_INDEX: Option<&'static str> = None;

  /// MySQL writes no collation for the word because its OWN DEFAULT already folds: `utf8mb4`'s
  /// default collation is `utf8mb4_0900_ai_ci` on 8.0 and `utf8mb4_general_ci` on 5.7, and both are
  /// `_ci` — case-insensitive — so `WHERE v = 'dog'` matches `'Dog'` and uses the value-led index
  /// with nothing added here.
  ///
  /// Writing one anyway would pin a VERSION-SPECIFIC identifier into DDL this repository provisions
  /// no server to check — `utf8mb4_0900_ai_ci` does not exist before 8.0 — so the honest answer is
  /// the empty one. A deployment that chose a `_bin` or `_as_cs` collation gets a case-SENSITIVE
  /// seek, which is that deployment's own choice, made where the schema's charset is.
  fn case_fold(_: &str) -> &'static str {
    ""
  }

  fn probe(table: &'static FieldName) -> sqlx::QueryBuilder<Self> {
    let mut qb = sqlx::QueryBuilder::new(
      "SELECT COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = \
       DATABASE() AND TABLE_NAME = ",
    );
    qb.push_bind(table.as_str());
    qb
  }
  fn execute(
    statement: &str,
    conn: &mut Self::Connection,
  ) -> impl Future<Output = Result<(), sqlx::Error>> {
    send::<Self>(statement, conn)
  }

  fn stored(
    table: &'static FieldName,
    conn: &mut Self::Connection,
  ) -> impl Future<Output = Result<Vec<(String, String)>, sqlx::Error>> {
    read::<Self>(table, conn)
  }
}

impl Table {
  /// Every statement that creates this node's storage, in the order it has to run.
  ///
  /// The parent table, then one statement per UNIQUE field, then one per COMPOSITE unique, then one
  /// per remaining indexed field, then one per COLLECTION COLUMN that asked for the value-led path
  /// where this dialect keeps a collection in one, then each upstream's provenance table, then each
  /// collection's edge table with its own containment index where the collection is a SEQUENCE and
  /// the VALUE-LED index beside it where the collection wrote `indexed`. Nothing here executes: the
  /// list IS the schema, which is what makes a rendering assertion see exactly what an engine would.
  ///
  /// The two UNIQUE loops are adjacent because they render the same KIND of statement from two
  /// declarations — one column apiece and several — and neither is deduplicated against the other:
  /// a pair that would collide is refused where the descriptor is written, which is
  /// [`Table::with_composite_uniques`]'s own check and the reason nothing here has to look.
  ///
  /// The parent table leads because the provenance tables' foreign keys name it, and a dialect that
  /// checks a constraint as it creates it would have nothing to point at.
  ///
  /// # A field named in BOTH `indexes` and `uniques` renders the unique statement ALONE
  ///
  /// A unique index already answers what a plain one over the same column exists for — a seek by
  /// that value — so a second, plain `CREATE INDEX` beside it would name the identical column for
  /// the identical purpose and cost every future write a second structure to maintain: a redundant
  /// write amplifier, not a second capability. [`Table::with_indexes`] and
  /// [`Table::with_uniques`] do not refuse the overlap — an author who wrote `indexed` beside
  /// `unique` on one field stated two intents about it, a seek and a constraint, and the spelling
  /// stays legal — it is THIS method, reading both lists together, that satisfies both with the one
  /// statement the unique index already is. A field named only in `indexes` is unaffected: it
  /// renders exactly the plain statement it always has.
  ///
  /// # No column type is refused here, and a dialect that cannot index one says so ITSELF
  ///
  /// Neither loop below asks what a field's column is stored AS before naming it in a `CREATE
  /// INDEX`/`CREATE UNIQUE INDEX` — the check [`AsyncSchemaDialect::SORT_KEY_INDEX`] runs for the
  /// companion loop beneath this one does not apply here, because that constant answers for ONE
  /// column type this dialect controls and a declared field's column may be typed at anything the
  /// declaration named. So a `TEXT`/`BLOB` field marked `indexed` or `unique` under MySQL renders a
  /// statement this dialect refuses at `CREATE INDEX … ERROR 1170 (42000): BLOB/TEXT column used in
  /// key specification without a key length` — the same honest failure an over-wide plain index
  /// already reached before `unique` existed, at the engine, on a connection the caller had already
  /// opened, rather than a silent narrowing this crate would have to guess a prefix width for.
  ///
  /// # Errors
  ///
  /// [`SqlLoweringError::EdgeWithoutField`], which is [`edge`]'s own refusal for a collection path
  /// naming no field. It is unreachable from a descriptor — [`EdgeTable::new`] refuses the empty
  /// path in a `const` — and it is still carried rather than unwrapped, because [`edge`] is the ONE
  /// derivation every party spelling an edge table's names goes through and a second, infallible
  /// copy of it would be the second spelling this whole module exists to avoid.
  pub fn create<Db: AsyncSchemaDialect>(&self) -> Result<Vec<String>, SqlLoweringError> {
    let mut statements = vec![self.create_table::<Db>()];
    let table = self.address().table();
    for field in self.uniques() {
      statements.push(create_index::<Db>(table, field, &[field], true));
    }
    for composite in self.composite_uniques() {
      statements.push(create_composite_unique::<Db>(
        table,
        self.columns(),
        composite,
      ));
    }
    for field in self.indexes() {
      if self.uniques().contains(field) {
        continue;
      }
      statements.push(create_index::<Db>(table, field, &[field], false));
    }
    // The companion columns take the SAME index rule as a declared field's — `__ig_<table>_<column>`,
    // derived from the table it is on — and they take it only where the dialect can index a binary
    // column with no key length. See `AsyncSchemaDialect::SORT_KEY_INDEX`, where MySQL's `ERROR 1170` is.
    if Db::SORT_KEY_INDEX {
      for shadow in self.shadows() {
        let column = shadow.column();
        statements.push(create_index::<Db>(table, column, &[column], false));
      }
    }
    // The COLLECTION COLUMNS that asked for the value-led path, on the dialect that keeps a
    // collection in one. A dialect answering `None` has no array type, so its descriptor carries no
    // such column and this list is empty there by construction — see
    // `AsyncSchemaDialect::COLLECTION_INDEX`, and `SORT_KEY_INDEX` one loop up, which is the
    // precedent for a dialect rendering nothing for an index it cannot make.
    if let Some(method) = Db::COLLECTION_INDEX {
      for collection in self.collection_indexes() {
        statements.push(self.create_collection_index::<Db>(collection, method)?);
      }
    }
    for side in self.sides() {
      statements.push(self.create_side::<Db>(side));
    }
    for collection in self.edges() {
      statements.extend(self.create_edge::<Db>(collection)?);
    }
    Ok(statements)
  }

  /// One COLLECTION COLUMN's containment index — `indexed`'s array-storage half.
  ///
  /// ```sql
  /// CREATE INDEX IF NOT EXISTS "__ig_clip_tags_value" ON "clip" USING GIN ("tags")
  /// ```
  ///
  /// # The NAME is [`edge`]'s, and asking it is what keeps ONE derivation
  ///
  /// The identifier is `__ig_<table>_<collection>_value`, and `<table>_<collection>` is exactly the
  /// name an edge table for the same collection wears — so the two storage forms derive one string
  /// and this method does not join it a second time. [`edge`] is the party that owns the join, so it
  /// is the party that is asked; its SHAPE argument is immaterial to a name, and [`EdgeShape::Set`]
  /// is passed because a name has no ordinal in it.
  ///
  /// # Errors
  ///
  /// As [`create`](Self::create).
  fn create_collection_index<Db: AsyncSchemaDialect>(
    &self,
    collection: &'static FieldName,
    method: &str,
  ) -> Result<String, SqlLoweringError> {
    let path = core::slice::from_ref(&collection);
    let edge = edge::<Db>(&self.address(), path, crate::EdgeShape::Set, &[])?;
    let mut columns = String::new();
    push_names::<Db>(&mut columns, path);
    Ok(index_over::<Db>(
      edge.name.as_str(),
      VALUE,
      quote_ident::<Db>(self.address().table()).as_str(),
      Some(method),
      &columns,
      false,
    ))
  }

  /// `CREATE TABLE IF NOT EXISTS <table> (<columns…>, PRIMARY KEY (<key…>)[, UNIQUE (<id>)])`.
  ///
  /// ```sql
  /// CREATE TABLE IF NOT EXISTS "clip" ("media_id" INTEGER NOT NULL, "id" INTEGER NOT NULL,
  ///   "title" TEXT, "__ig_tags_sort__" BLOB NOT NULL,
  ///   PRIMARY KEY ("media_id", "id"), UNIQUE ("id"))
  /// ```
  ///
  /// # The COMPANION columns come last, and they are not among `columns`
  ///
  /// A `sortable` collection's order-preserving companion is a column of this table and NOT one of
  /// the record's — see [`Shadow`](crate::Shadow), where the partition is stated — so it is declared
  /// from [`Table::shadows`] after the declared list rather than inside it. [`describes`] is
  /// therefore untouched by the word: what it audits is the record's columns, and a companion is
  /// none of them.
  ///
  /// # The `UNIQUE` is the ADDRESS LAW written down
  ///
  /// [`SqlQuery::addresses`](super::SqlQuery::addresses) names a row by its id alone, and its note
  /// states what that rests on: *every table this engine addresses declares its id `UNIQUE`*. A
  /// lineage-bearing record's primary key is the whole chain, so the id is unique only if the schema
  /// says so — and this is the schema. A ROOT needs no such clause: its key IS the id, so the
  /// primary key already carries the uniqueness and a second constraint over the same one column
  /// would be a second index for nothing.
  ///
  /// It earns a second keep under the split layout: a provenance table's foreign key names this
  /// table's id, and a foreign key points at a column its parent table constrains uniquely.
  ///
  /// # The DISCRIMINANT is one of `columns`, so nothing is appended for it
  ///
  /// [`Table::with_provenance`] refuses a discriminant the table's own column list does not carry,
  /// so the word a row's upstream is read out of is declared, selected, bound and created exactly
  /// like every other column — and [`AsyncSchemaDialect::DISCRIMINANT`] is what a DECLARATION states its
  /// type as rather than what this renders behind one.
  ///
  /// # A SHAPED descriptor renders the SAME TEXT, and the join is the reason it can
  ///
  /// [`Table::shaped`] carries the row's fields rather than its column names, so this asks
  /// [`declared_columns`] for each column's name instead of reading one — and that walk is
  /// `fold_paths`, which is what a `SELECT` list and a filter's own column already descend through.
  /// A shaped blueprint and the flat one naming the same columns therefore render byte-identical
  /// statements, which is the property the goldens beside this module assert directly.
  fn create_table<Db: AsyncSchemaDialect>(&self) -> String {
    let address = self.address();
    let mut out = String::from("CREATE TABLE IF NOT EXISTS ");
    out.push_str(quote_ident::<Db>(address.table()).as_str());
    out.push_str(" (");
    declared_columns(self, &mut |at, path, ty, nullable| {
      if at > 0 {
        out.push_str(", ");
      }
      push_typed(&mut out, quote_path::<Db>(path).as_str(), ty, nullable);
    });
    // The companion columns come AFTER every declared one, and they are `NOT NULL` for the reason an
    // edge row's columns are: a collection has no null state, so the empty collection is a key of one
    // byte rather than an absent value.
    for shadow in self.shadows() {
      out.push_str(", ");
      push_column::<Db>(&mut out, shadow.column(), Db::SORT_KEY, false);
    }
    out.push_str(", ");
    push_primary_key::<Db>(&mut out, address.keys());
    // The id is the key's LAST column — `key = lineage ⊕ id`, root-first — and a key of one column
    // is already unique under the primary key above.
    if let [_, .., id] = address.keys() {
      out.push_str(", UNIQUE (");
      out.push_str(quote_ident::<Db>(id).as_str());
      out.push(')');
    }
    out.push(')');
    out
  }

  /// One upstream's PROVENANCE table: the common row's id, that variant's ancestors, and the
  /// constraint that ties the two.
  ///
  /// ```sql
  /// 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"))
  /// ```
  ///
  /// The id LEADS and is the whole primary key, which is what makes the relationship one-to-one: a
  /// row descends from ONE upstream, so it has one provenance row in one of these tables and none in
  /// any other. Every column is `NOT NULL` — see [`SideTable`], where that is the layout's own
  /// argument rather than a rendering choice.
  ///
  /// The id's TYPE is the common table's, read off it rather than restated: a provenance row's key
  /// is the common row's key, and two spellings of one column's type is the drift a descriptor's
  /// whole shape exists to prevent.
  fn create_side<Db: AsyncSchemaDialect>(&self, side: &SideTable) -> String {
    let id = self.id();
    let mut out = String::from("CREATE TABLE IF NOT EXISTS ");
    out.push_str(quote_ident::<Db>(side.table()).as_str());
    out.push_str(" (");
    push_column::<Db>(&mut out, id, self.stored(id), false);
    for column in side.columns() {
      out.push_str(", ");
      push_column::<Db>(&mut out, column.name(), column.ty(), column.nullable());
    }
    out.push_str(", ");
    push_primary_key::<Db>(&mut out, &[id]);
    out.push_str(", FOREIGN KEY (");
    out.push_str(quote_ident::<Db>(id).as_str());
    out.push_str(") REFERENCES ");
    out.push_str(quote_ident::<Db>(self.address().table()).as_str());
    out.push_str(" (");
    out.push_str(quote_ident::<Db>(id).as_str());
    out.push_str("))");
    out
  }

  /// One collection's second table, the containment index a sequence's needs, and the VALUE-LED one
  /// the `indexed` word asks for.
  ///
  /// ```sql
  /// 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"))
  /// CREATE INDEX IF NOT EXISTS "__ig_clip_tags_element" ON "clip_tags" ("media_id", "id", "tags")
  /// ```
  ///
  /// The same collection having written [`EdgeTable::indexed`](crate::EdgeTable::indexed), a second
  /// index joins it with the columns in the other order — and, on a dialect whose default comparison
  /// does not fold case, the element column takes that dialect's collation:
  ///
  /// ```sql
  /// CREATE TABLE IF NOT EXISTS "clip_tags" (…, "tags" TEXT COLLATE NOCASE NOT NULL, …)
  /// CREATE INDEX IF NOT EXISTS "__ig_clip_tags_element" ON "clip_tags" ("media_id", "id", "tags")
  /// CREATE INDEX IF NOT EXISTS "__ig_clip_tags_value" ON "clip_tags" ("tags", "media_id", "id")
  /// ```
  ///
  /// A collection that wrote no word renders the first block BYTE FOR BYTE, which is what makes the
  /// second structure and the fold a cost only a declaration that asked for them pays. See
  /// [`AsyncSchemaDialect::case_fold`], where the collation's placement is measured.
  ///
  /// A COMPOSITE element occupies one column per SEGMENT, and every clause that named the one
  /// element column names all of them:
  ///
  /// ```sql
  /// CREATE TABLE IF NOT EXISTS "clip_stamps" ("id" BIGINT NOT NULL,
  ///   "stamps_ordinal" INTEGER NOT NULL, "stamps_secs" BIGINT NOT NULL,
  ///   "stamps_nanos" INTEGER NOT NULL, PRIMARY KEY ("id", "stamps_ordinal"))
  /// CREATE INDEX IF NOT EXISTS "__ig_clip_stamps_element" ON "clip_stamps"
  ///   ("id", "stamps_secs", "stamps_nanos")
  /// ```
  ///
  /// The two shapes are the ones
  /// [`SqlEdgeComparison`](crate::comparison::sql::SqlEdgeComparison)'s own note writes down: a
  /// SEQUENCE is keyed on `(key…, ordinal)` and indexed on `(key…, value…)` for the containment
  /// seeks, and a SET is keyed on `(key…, value…)`, which is what makes its membership unique and
  /// leaves it nothing further to index KEY-FIRST. A composite element widens both lists and moves
  /// neither rule: the identity is still the ordinal on one shape and the whole element on the
  /// other.
  ///
  /// The word's index is over BOTH shapes, and that is the same note read from the other end: every
  /// structure above leads on the KEY, so neither shape can be entered from a VALUE the caller has
  /// and a row it has not.
  ///
  /// Every column is `NOT NULL` except a SEQUENCE's optional SEGMENT. An edge row exists because an
  /// element does, so there is no state for a `NULL` to stand for — where a parent's column may
  /// legitimately be absent, an element that is absent is a row that was never written. A composite
  /// element's PART is the one thing that can be absent while the element is there, and it may be so
  /// only under a sequence: a set's rows ARE its key, and that is
  /// [`EdgeTable::composite`](crate::EdgeTable::composite)'s own refusal.
  ///
  /// # Errors
  ///
  /// As [`create`](Self::create).
  fn create_edge<Db: AsyncSchemaDialect>(
    &self,
    collection: &EdgeTable,
  ) -> Result<Vec<String>, SqlLoweringError> {
    let address = self.address();
    let segments = collection.element().segments();
    let names: Vec<&FieldName> = segments.iter().map(Column::name).collect();
    let edge = edge::<Db>(&address, collection.path(), collection.shape(), &names)?;
    // The columns come back in the BACKEND's own buffer, which this family bounds by [`Display`]
    // alone — pinning it is a cost only the correlated comparison kinds pay, and a schema is
    // rendered once per boot, so each is read out here rather than restricting the dialect.
    let values: Vec<String> = edge.values.iter().map(ToString::to_string).collect();
    let ordinal = edge.ordinal.as_ref().map(ToString::to_string);
    // A SCALAR element carries the ONE type the descriptor states; a composite carries one per
    // segment, positionally against the columns above — which is the same order
    // [`ColumnKind::SEGMENTS`](crate::ColumnKind::SEGMENTS) states them in, since
    // [`EdgeTable::composite`] is what put them there.
    let types: Vec<&str> = match collection.element() {
      crate::ElementColumns::Plain(ty) => vec![ty],
      crate::ElementColumns::Segments(segments) => segments.iter().map(Column::ty).collect(),
    };

    let mut out = String::from("CREATE TABLE IF NOT EXISTS ");
    out.push_str(edge.table.as_str());
    out.push_str(" (");
    for key in address.keys() {
      push_column::<Db>(&mut out, key, self.stored(key), false);
      out.push_str(", ");
    }
    if let Some(ordinal) = &ordinal {
      push_typed(&mut out, ordinal, Db::ORDINAL, false);
      out.push_str(", ");
    }
    for (at, (value, ty)) in values.iter().zip(&types).enumerate() {
      if at > 0 {
        out.push_str(", ");
      }
      // A SEQUENCE's segment may hold `NULL` — its identity is the ordinal, so an optional part is
      // an ordinary nullable column. A SET's may not, and that is refused where the descriptor is
      // written rather than rendered here: see [`EdgeTable::composite`].
      //
      // THE COLLATION rides the COLUMN and only where the word was written, which is what keeps a
      // collection that asked for nothing byte-identical to what it rendered before the word
      // existed. See `AsyncSchemaDialect::case_fold`, where the placement is measured.
      push_element(
        &mut out,
        value,
        ty,
        collation::<Db>(collection, ty),
        nullable(segments, at),
      );
    }
    out.push_str(", PRIMARY KEY (");
    for key in address.keys() {
      out.push_str(quote_ident::<Db>(key).as_str());
      out.push_str(", ");
    }
    // A sequence is keyed on its POSITION and a set on its VALUE — every column of it — which is the
    // whole physical difference between the two shapes, and the reason a repeated element is a
    // refusal in one and impossible in the other.
    match &ordinal {
      Some(ordinal) => out.push_str(ordinal),
      None => push_joined(&mut out, &values),
    }
    out.push_str("))");

    let mut statements = vec![out];
    let mut keys = String::new();
    push_names::<Db>(&mut keys, address.keys());
    let mut elements = String::new();
    push_joined(&mut elements, &values);
    if ordinal.is_some() {
      statements.push(index_over::<Db>(
        edge.name.as_str(),
        ELEMENT,
        edge.table.as_str(),
        None,
        &format!("{keys}, {elements}"),
        false,
      ));
    }
    // THE VALUE-LED INDEX, and it is the SAME columns in the other order — which is the whole of what
    // the word buys. It is rendered for BOTH shapes: a sequence's derived index leads on the key and
    // a set's primary key does too, so neither answers a seek that starts from a value the caller
    // has and a row it has not.
    if collection.is_indexed() {
      statements.push(index_over::<Db>(
        edge.name.as_str(),
        VALUE,
        edge.table.as_str(),
        None,
        &format!("{elements}, {keys}"),
        false,
      ));
    }
    Ok(statements)
  }
}

/// The collation clause ONE element column takes — this dialect's answer where the collection wrote
/// `indexed`, and the empty string where it did not.
///
/// The word is what gates it. A collection that asked for no value-led path keeps the column it
/// always declared, byte for byte, which is what makes the word's cost — a second structure, and a
/// set's identity folding case — the choice its author made rather than one every collection pays.
fn collation<Db: AsyncSchemaDialect>(collection: &EdgeTable, ty: &str) -> &'static str {
  if collection.is_indexed() {
    Db::case_fold(ty)
  } else {
    ""
  }
}

/// Whether the `at`th ELEMENT column holds `NULL` — the descriptor's own answer for a composite,
/// and `false` for a scalar element, whose row exists because an element does.
///
/// EMPTY is the scalar answer, exactly as it is one type over: an edge row that is there at all
/// carries a value, so a `NULL` in its one element column would stand for nothing.
fn nullable(segments: &[Column], at: usize) -> bool {
  segments.get(at).is_some_and(Column::nullable)
}

/// The element's columns, comma-joined — what a set's `PRIMARY KEY` ends on and what a sequence's
/// containment index ends on, which are the same list read by two clauses.
///
/// Each is ALREADY bracketed, being what [`edge`] hands over, so this joins and brackets nothing.
fn push_joined(out: &mut String, values: &[String]) {
  for (at, value) in values.iter().enumerate() {
    if at > 0 {
      out.push_str(", ");
    }
    out.push_str(value);
  }
}

/// `"<name>" <type>[ NOT NULL]` — one column of a `CREATE TABLE`, with the name bracketed for this
/// dialect.
fn push_column<Db: QuoteIdent>(out: &mut String, name: &FieldName, ty: &str, nullable: bool) {
  push_typed(out, quote_ident::<Db>(name).as_str(), ty, nullable);
}

/// The same, where the name is ALREADY bracketed — an edge table's value and ordinal columns arrive
/// that way, being what [`edge`] hands over.
fn push_typed(out: &mut String, name: &str, ty: &str, nullable: bool) {
  push_element(out, name, ty, "", nullable);
}

/// `"<name>" <type>[<collation>][ NOT NULL]` — the general form, and the one an edge table's ELEMENT
/// column is written through.
///
/// `collation` is rendered VERBATIM and comes from a dialect's own
/// [`case_fold`](AsyncSchemaDialect::case_fold), so it carries its own leading space or is empty.
/// Every other column reaches this through [`push_typed`], which passes the empty string — a
/// collation is a property of the one column the word is about, and the columns around it are
/// untouched by it.
fn push_element(out: &mut String, name: &str, ty: &str, collation: &str, nullable: bool) {
  out.push_str(name);
  out.push(' ');
  out.push_str(ty);
  out.push_str(collation);
  if !nullable {
    out.push_str(" NOT NULL");
  }
}

/// A column list, bracketed for this dialect and comma-joined — what the parentheses of a
/// `CREATE INDEX` hold.
fn push_names<Db: QuoteIdent>(out: &mut String, columns: &[&FieldName]) {
  for (at, column) in columns.iter().enumerate() {
    if at > 0 {
      out.push_str(", ");
    }
    out.push_str(quote_ident::<Db>(column).as_str());
  }
}

/// `PRIMARY KEY ("a", "b")`.
fn push_primary_key<Db: QuoteIdent>(out: &mut String, keys: &[&FieldName]) {
  out.push_str("PRIMARY KEY (");
  for (at, key) in keys.iter().enumerate() {
    if at > 0 {
      out.push_str(", ");
    }
    out.push_str(quote_ident::<Db>(key).as_str());
  }
  out.push(')');
}

/// The domain mark every framework-generated PERSISTENT DDL object name wears — the same `__ig_`
/// this crate already stamps on a query-time alias (see [`interface::KIND`](crate::interface::KIND)
/// and [`interface::SOURCE`](crate::interface::SOURCE)), spent here on the catalog's own objects
/// instead: an index and a composite constraint both derive their name from the table they are on,
/// and this is what tells the two apart from anything a caller declared in the same catalog.
///
/// UNIFORM across both roads is the whole reason this is one constant and not two spellings of one
/// prefix: [`index_over`] and [`create_composite_unique`] derive the SAME name for the SAME columns
/// on the two roads that can name one — that is [`Table::with_composite_uniques`]'s own duplicate
/// detector, and it only fires because the two derivations agree on every byte, this one included.
///
/// # It is PINNED to the reserved namespace, at compile time
///
/// This was a FOURTH hand-written copy of `__ig`. The other three — this crate's
/// [`ALIAS_PREFIX`](crate::comparison::sql::ALIAS_PREFIX), `ingraph-codegen`'s, and
/// `ingraph-derive`'s fence — are held level by tests that can see two crates at once; this one,
/// being private and carrying a trailing `_` the others do not, was reachable by none of them, so
/// moving the namespace would have left the CATALOG's object names behind without a red build
/// anywhere. `concat!` cannot join a `const`, so the agreement is asserted instead of derived: the
/// value stays a literal a reader can see whole, and a namespace that moved without this moving is
/// a compile error rather than a schema that disagrees with itself.
const DDL_NAME_PREFIX: &str = "__ig_";

const _: () = assert!(
  leads_with(DDL_NAME_PREFIX, ALIAS_PREFIX) && DDL_NAME_PREFIX.len() == ALIAS_PREFIX.len() + 1,
  "the catalog's object-name prefix is the reserved namespace plus one joining underscore"
);

/// Whether `name` begins with `lead`, answerable in a `const` context.
///
/// `str::starts_with` is not `const`, and the pin above is worth more as a compile error than as a
/// test: a test states the agreement for whoever runs it, and this states it for whoever builds.
///
/// `pub(crate)` rather than private, and the widening is this function's own note honoured rather
/// than bent: the retirement ledger mints `__ig_retired_<family>` and needs the SAME pin, and a
/// second copy of the loop would be the copy that drifts — which is exactly the failure the pin
/// above exists to prevent. One checker, spent at every literal that claims the namespace.
pub(crate) const fn leads_with(name: &str, lead: &str) -> bool {
  let (name, lead) = (name.as_bytes(), lead.as_bytes());
  if name.len() < lead.len() {
    return false;
  }
  let mut at = 0;
  while at < lead.len() {
    if name[at] != lead[at] {
      return false;
    }
    at += 1;
  }
  true
}

/// `CREATE [UNIQUE ]INDEX IF NOT EXISTS "__ig_<table>_<suffix>" ON "<table>" (<columns…>)`.
///
/// ONE naming rule, spent twice: an index over a parent's field is `__ig_<table>_<field>`, and the
/// containment index over a sequence's edge table is `__ig_<edge>_element`. A name derived from the
/// table it is on is as unique as that table is, which is what makes the rule enough without a
/// registry — and [`DDL_NAME_PREFIX`] is what keeps it out of the same namespace a caller's own
/// catalog objects are declared in.
///
/// `unique` is the FIELD's own claim — [`Table::uniques`] names which calls pass it — and never the
/// containment index's: a sequence's element is expected to repeat across rows, so the one caller
/// that reaches [`index_over`] directly always passes `false` for it.
fn create_index<Db: QuoteIdent>(
  table: &FieldName,
  suffix: &FieldName,
  columns: &[&FieldName],
  unique: bool,
) -> String {
  let mut rendered = String::new();
  push_names::<Db>(&mut rendered, columns);
  index_over::<Db>(
    table.as_str(),
    suffix.as_str(),
    quote_ident::<Db>(table).as_str(),
    None,
    &rendered,
    unique,
  )
}

/// `CREATE UNIQUE INDEX IF NOT EXISTS "__ig_<table>_<entry>_<entry>…" ON "<table>" (<leaf…>)`.
///
/// # The NAME follows [`create_index`]'s rule, spent over several entries
///
/// One index over a parent's field is `__ig_<table>_<field>`; this is that rule with the entries
/// the declaration named run together the same way, so a ONE-ENTRY composite derives exactly the
/// name the field marker over that same column derives. That is not a coincidence to be worked
/// around — it is the TELL [`Table::with_composite_uniques`] refuses the pair by, and building this
/// name any other way (a `uq_` prefix, a hash, the leaves instead of the entries) would have made
/// the two spellings of one constraint derive two names and collide silently in the engine instead.
/// [`DDL_NAME_PREFIX`] is the ONE spelling both roads read, for the same reason.
///
/// The ENTRIES spell the name and the LEAVES fill the parentheses, which is the one place the two
/// differ: a citizen contributes one segment to the name and several columns to the statement.
/// Naming it by its leaves instead would spell `scenes_span_start_pts_span_timebase_num_…` — past
/// PostgreSQL's 63-byte identifier limit on any realistic citizen, where the server TRUNCATES
/// rather than refusing, and two constraints sharing a prefix would silently become one. The domain
/// mark spends five of those 63 bytes; an entry-spelled name is nowhere near the cliff to begin
/// with, so the budget this note already worried about is the one that pays for it.
///
/// # The COLUMN ORDER is the authored order, and the fan is stable inside it
///
/// Entries are rendered in the order the declaration wrote them, and a citizen's own leaves in
/// [`ColumnKind::SEGMENTS`](crate::ColumnKind::SEGMENTS) order — the same order the `CREATE TABLE`
/// declared them and a `SELECT` reads them, since both descend through the same walk.
fn create_composite_unique<Db: QuoteIdent>(
  table: &FieldName,
  columns: TableColumns,
  composite: &CompositeUnique,
) -> String {
  let mut name = String::from(DDL_NAME_PREFIX);
  name.push_str(table.as_str());
  for entry in composite.entries() {
    name.push('_');
    name.push_str(entry.as_str());
  }

  let mut out = String::from("CREATE UNIQUE INDEX IF NOT EXISTS ");
  out.push_str(quote_ident::<Db>(FieldName::new(&name)).as_str());
  out.push_str(" ON ");
  out.push_str(quote_ident::<Db>(table).as_str());
  out.push_str(" (");
  let mut at = 0;
  for entry in composite.entries() {
    constrained_leaves(columns, entry, &mut |path| {
      if at > 0 {
        out.push_str(", ");
      }
      out.push_str(quote_path::<Db>(path).as_str());
      at += 1;
    });
  }
  out.push(')');
  out
}

/// The columns ONE entry of a composite unique constrains — a leaf as itself, a citizen as its VALUE
/// leaves.
///
/// This is the RENDERING half of `constrained_width` beside [`Table::with_composite_uniques`], which
/// is the `const` one: the same descent, asking the composite before its leaves, so the count that
/// admitted a descriptor and the columns this emits cannot disagree. A `const fn` takes no closure,
/// which is why the walk is written twice rather than shared — the standing this module's
/// `declared_columns` note records for `leaf_named`, and the agreement is pinned by a test.
fn constrained_leaves(columns: TableColumns, name: &FieldName, each: &mut impl FnMut(&Path<'_>)) {
  match columns {
    TableColumns::Flat(flat) => {
      for column in flat {
        if column.name().is(name.as_str()) {
          each(&Path::under(column.name(), None));
          return;
        }
      }
    }
    TableColumns::Shaped { shapes, .. } => {
      named_leaves(shapes, None, name, each);
    }
  }
}

/// [`constrained_leaves`]'s recursion — `true` once the entry has been found, so the walk stops
/// where the `const` twin returns.
fn named_leaves(
  shapes: &[crate::ColumnShape],
  under: Option<&Path<'_>>,
  name: &FieldName,
  each: &mut impl FnMut(&Path<'_>),
) -> bool {
  for shape in shapes {
    match *shape {
      crate::ColumnShape::Plain(field)
      | crate::ColumnShape::Nullable(field)
      | crate::ColumnShape::Derived(field) => {
        let path = Path::under(field.stored(), under);
        if crate::joins(&path, name) {
          each(&path);
          return true;
        }
      }
      crate::ColumnShape::Composite {
        field: Some(field),
        segments,
      } => {
        let path = Path::under(field.stored(), under);
        // THE CITIZEN IS ASKED BEFORE ITS LEAVES ARE — `constrained_width`'s own order, and the
        // reason the two walks agree about what an entry names.
        if crate::joins(&path, name) {
          value_leaves(segments, Some(&path), each);
          return true;
        }
        if named_leaves(segments, Some(&path), name, each) {
          return true;
        }
      }
      crate::ColumnShape::Composite {
        field: None,
        segments,
      } => {
        if named_leaves(segments, under, name, each) {
          return true;
        }
      }
    }
  }
  false
}

/// Every VALUE leaf beneath `shapes`, in order — [`fold_shapes`]'s descent with the DERIVED leaves
/// dropped, which is [`ColumnShape::holds_value`](crate::ColumnShape::holds_value)'s own answer.
///
/// What differs from the walk every other spelling of a column makes is that the two DERIVED words
/// are dropped, and that is the whole of what a composite unique over a citizen means. See
/// [`CompositeUnique`], where the law and the `NULL`-does-not-collide argument are.
fn value_leaves(
  shapes: &[crate::ColumnShape],
  under: Option<&Path<'_>>,
  each: &mut impl FnMut(&Path<'_>),
) {
  fold_shapes_under(shapes, under, (), &mut |(), path, shape| {
    if shape.holds_value() {
      each(path);
    }
  });
}

/// The same, where the table's two forms are handed in separately — an edge table's name is derived
/// and its bracketed form is [`edge`]'s, so neither is rebuilt from the other.
///
/// `columns` arrives ALREADY RENDERED — bracketed for this dialect and comma-joined — because the
/// three callers hold three different lists: a parent's declared fields, which [`push_names`]
/// brackets; an edge table's keys and its element columns in either order, the second of which
/// [`edge`] has already bracketed; and one collection column. A parameter per list would have made
/// the ORDER of two of them a positional convention rather than a string a caller can read.
///
/// `method` is the dialect's own access method, rendered `USING <method>` before the parentheses and
/// absent for the B-tree every dialect defaults to — see
/// [`AsyncSchemaDialect::COLLECTION_INDEX`].
fn index_over<Db: QuoteIdent>(
  table: &str,
  suffix: &str,
  bracketed: &str,
  method: Option<&str>,
  columns: &str,
  unique: bool,
) -> String {
  let mut name = String::with_capacity(DDL_NAME_PREFIX.len() + table.len() + suffix.len() + 1);
  name.push_str(DDL_NAME_PREFIX);
  name.push_str(table);
  name.push('_');
  name.push_str(suffix);

  let mut out = String::from(if unique {
    "CREATE UNIQUE INDEX IF NOT EXISTS "
  } else {
    "CREATE INDEX IF NOT EXISTS "
  });
  out.push_str(quote_ident::<Db>(FieldName::new(&name)).as_str());
  out.push_str(" ON ");
  out.push_str(bracketed);
  if let Some(method) = method {
    out.push_str(" USING ");
    out.push_str(method);
  }
  out.push_str(" (");
  out.push_str(columns);
  out.push(')');
  out
}

/// Why a schema was not created, or why a live one is not the declared one.
///
/// # The drift variants carry the NAMES and not a rendered sentence
///
/// A caller that finds a drift wants to say which table and which column, and an audit that answered
/// with one string would have thrown that away. Each variant carries the table it is about, so a
/// caller matching on one can name it in its own words.
///
/// # The COLUMN is owned where the table is borrowed, and a shaped blueprint is why
///
/// A table's name is DECLARED — `&'static FieldName` in every descriptor — but a column's may not
/// be: [`Table::shaped`] states the row's FIELDS and lets the renderer join their leaves, so
/// `tb_num` exists nowhere as data and there is no borrow to hand back. The audit builds that name
/// to ask the catalogue about it, so the drift carries the string it already had. A flat descriptor
/// pays one copy on a path that is about to stop.
#[derive(Debug)]
pub enum SqlSchemaError {
  /// The driver refused, or a statement did.
  Sql(sqlx::Error),
  /// A descriptor will not render for this dialect — carried as the dialect stated it.
  Lowering(SqlLoweringError),
  /// A declared table is not in the live database at all.
  Missing(&'static FieldName),
  /// A declared column is not in the live table.
  Column {
    /// The table the column was declared on.
    table: &'static FieldName,
    /// The column the live table does not carry, as the catalogue would report it.
    column: String,
  },
  /// A declared column is stored at a type the declaration does not name.
  Type {
    /// The table the column is on.
    table: &'static FieldName,
    /// The column whose type has drifted, as the catalogue reports it.
    column: String,
    /// The type the descriptor declares.
    declared: &'static str,
    /// The type the live database reports.
    stored: String,
  },
}

impl From<sqlx::Error> for SqlSchemaError {
  #[inline]
  fn from(error: sqlx::Error) -> Self {
    Self::Sql(error)
  }
}

impl From<SqlLoweringError> for SqlSchemaError {
  #[inline]
  fn from(error: SqlLoweringError) -> Self {
    Self::Lowering(error)
  }
}

impl From<Infallible> for SqlSchemaError {
  #[inline]
  fn from(error: Infallible) -> Self {
    match error {}
  }
}

impl core::fmt::Display for SqlSchemaError {
  fn fmt(&self, f: &mut core::fmt::Formatter<'_>) -> core::fmt::Result {
    match self {
      Self::Sql(error) => write!(f, "this schema statement did not run: {error}"),
      Self::Lowering(error) => write!(f, "this descriptor does not render here: {error}"),
      Self::Missing(table) => write!(
        f,
        "the table `{}` is declared and this database has none",
        table.as_str()
      ),
      Self::Column { table, column } => write!(
        f,
        "`{}` declares the column `{column}` and this database's table has none",
        table.as_str()
      ),
      Self::Type {
        table,
        column,
        declared,
        stored,
      } => write!(
        f,
        "`{}`.`{column}` is declared `{declared}` and this database stores it as `{stored}`",
        table.as_str()
      ),
    }
  }
}

impl core::error::Error for SqlSchemaError {
  fn source(&self) -> Option<&(dyn core::error::Error + 'static)> {
    match self {
      Self::Sql(error) => Some(error),
      Self::Lowering(error) => Some(error),
      Self::Missing(_) | Self::Column { .. } | Self::Type { .. } => None,
    }
  }
}

/// The CREATE visitor: every statement the graph's storage takes, accumulated in walk order.
///
/// # It collects, and [`run`](Self::run) sends
///
/// The walk is where a descriptor might refuse to render, and it happens before any statement is
/// executed — so a graph with one unrenderable node creates nothing rather than half of itself.
/// That is the ordering the write lane's own `statements` keeps, one lane over, and for the same
/// reason: the list IS what an engine is sent, so a test can read it.
pub struct Create<Db> {
  statements: Vec<String>,
  db: PhantomData<Db>,
}

impl<Db> Create<Db> {
  /// A visitor with nothing accumulated yet.
  #[inline]
  #[must_use]
  pub const fn new() -> Self {
    Self {
      statements: Vec::new(),
      db: PhantomData,
    }
  }

  /// Every statement the walk has accumulated, in the order it will run them.
  ///
  /// What the goldens read. It is also what a caller with a migration tool of its own takes instead
  /// of [`run`](Self::run) — the statements are the deliverable, and executing them is one thing to
  /// do with them.
  #[inline]
  #[must_use]
  pub fn statements(&self) -> &[String] {
    &self.statements
  }
}

impl<Db> Default for Create<Db> {
  #[inline]
  fn default() -> Self {
    Self::new()
  }
}

impl<Db: AsyncSchemaDialect> crate::TypeVisitor for Create<Db> {
  type Db = Db;
  type Error = SqlLoweringError;

  fn next_type<E: Describe<Db>>(&mut self) -> Result<(), SqlLoweringError> {
    self.statements.extend(E::BLUEPRINT.create::<Db>()?);
    Ok(())
  }
}

impl<Db: AsyncSchemaDialect> Create<Db> {
  /// Send every accumulated statement, in order, stopping at the first one the engine refuses.
  ///
  /// The connection is a PARAMETER, as every handle in this lane is: a graph root owns no
  /// context, so what a schema is created against is whatever the caller acquired.
  ///
  /// # Errors
  ///
  /// The driver's own refusal.
  pub async fn run(&self, conn: &mut Db::Connection) -> Result<(), SqlSchemaError> {
    for statement in &self.statements {
      Db::execute(statement, conn).await?;
    }
    Ok(())
  }
}

/// The VALIDATE visitor: the descriptors the graph declares, collected for an audit against a
/// live database.
///
/// # It reads the catalogue rather than the statements
///
/// A create renders what a schema SHOULD be; this asks the database what it IS, through
/// [`AsyncSchemaDialect::columns`], and compares the answer against the declaration. That is the one
/// check a re-run of the create cannot make: `CREATE TABLE IF NOT EXISTS` over a table whose column
/// has since been altered succeeds and changes nothing.
pub struct Validate<Db> {
  tables: Vec<Table>,
  db: PhantomData<Db>,
}

impl<Db> Validate<Db> {
  /// A visitor with nothing collected yet.
  #[inline]
  #[must_use]
  pub const fn new() -> Self {
    Self {
      tables: Vec::new(),
      db: PhantomData,
    }
  }

  /// The descriptors the walk collected, in walk order.
  #[inline]
  #[must_use]
  pub fn tables(&self) -> &[Table] {
    &self.tables
  }
}

impl<Db> Default for Validate<Db> {
  #[inline]
  fn default() -> Self {
    Self::new()
  }
}

impl<Db: AsyncSchemaDialect> crate::TypeVisitor for Validate<Db> {
  type Db = Db;
  /// The walk cannot fail: collecting a `const` refuses nothing. What can fail is the audit, which
  /// is where the database is actually asked.
  type Error = Infallible;

  fn next_type<E: Describe<Db>>(&mut self) -> Result<(), Infallible> {
    self.tables.push(E::BLUEPRINT);
    Ok(())
  }
}

impl<Db: AsyncSchemaDialect> Validate<Db> {
  /// Compare every collected descriptor against what this database actually stores, stopping at the
  /// first drift.
  ///
  /// # What it checks, and what it deliberately does not
  ///
  /// Every declared table exists, carries every declared COLUMN, and stores each at the declared
  /// type. Three things are outside it and each is named rather than left to be discovered:
  ///
  /// - an EXTRA live column is not drift. A column the declaration does not know about is what an
  ///   additive migration leaves behind, and reporting it would make every forward-compatible
  ///   deployment fail its own audit.
  /// - NULLABILITY is not read. The three catalogues report it three ways and only one of them is
  ///   reachable from this repository, so a check written against the one would be a claim about the
  ///   other two that nothing here measured.
  /// - EDGE tables are not audited. Their column names are derived rather than declared — they exist
  ///   BRACKETED, as [`edge`] hands them over — so comparing one against a catalogue's plain text
  ///   would mean a second, unbracketed derivation, which is the duplication this module is built to
  ///   avoid. What covers them instead is the write and read lanes running against a
  ///   created-by-visitor schema.
  ///
  /// PROVENANCE tables ARE audited, and the difference from an edge table is who names them: a side
  /// table's name and every ancestor column it carries are DECLARED, so the audit compares the
  /// declaration against the catalogue with nothing derived in between. The ORDER-PRESERVING
  /// COMPANION COLUMNS are audited for the same reason one step in — a companion's name is carried
  /// on the descriptor as plain text rather than derived at each spelling — and they are audited
  /// beside the row's own columns, being columns of the row. What it does not read is the
  /// foreign key, for the nullability reason one line up — three catalogues report a constraint
  /// three ways, and only one of them is reachable from this repository.
  ///
  /// The comparison of a type is ASCII-case-insensitive. A catalogue answers in its own case —
  /// SQLite hands back the declaration's word verbatim, `information_schema` normalises — and a
  /// difference of case is not a difference of type in any dialect here.
  ///
  /// # Errors
  ///
  /// [`SqlSchemaError::Missing`], [`Column`](SqlSchemaError::Column) or
  /// [`Type`](SqlSchemaError::Type) for the first drift, and the driver's own refusal for a probe
  /// that would not run.
  pub async fn audit(&self, conn: &mut Db::Connection) -> Result<(), SqlSchemaError> {
    for table in &self.tables {
      audit_table::<Db>(conn, table).await?;
    }
    Ok(())
  }
}

/// One table's own audit — the probe, and the comparison against what was declared. The common
/// table first, then each provenance table beside it.
///
/// The row's own columns come through [`declared_columns`], so a SHAPED descriptor is audited at the
/// grain it was written at: the leaf names are joined by the same walk the `CREATE TABLE` joined
/// them with, and the catalogue is asked about the column that statement actually made.
async fn audit_table<Db: AsyncSchemaDialect>(
  conn: &mut Db::Connection,
  table: &Table,
) -> Result<(), SqlSchemaError> {
  let name = table.address().table();
  let stored = probe::<Db>(conn, name).await?;
  // A WALK rather than an iterator, so the first drift is kept rather than broken out of: a row's
  // columns are few and this path is about to stop either way, so threading a short circuit through
  // the closure would buy an audit nothing it can spend.
  let mut drift = None;
  declared_columns(table, &mut |_, path, ty, _| {
    if drift.is_none() {
      drift = compare(&stored, name, &column_name(path), ty);
    }
  });
  // The companion columns ARE audited, where an edge table's are not, and the difference is who
  // spells the name: a companion's is carried on the descriptor as plain text — minted once, where
  // the declaration is read — so the comparison against a catalogue derives nothing in between.
  let drift = drift.or_else(|| {
    table
      .shadows()
      .iter()
      .find_map(|shadow| compare(&stored, name, shadow.column().as_str(), Db::SORT_KEY))
  });
  if let Some(drift) = drift {
    return Err(drift);
  }

  for side in table.sides() {
    let stored = probe::<Db>(conn, side.table()).await?;
    // The id LEADS a provenance row and is the common table's own column, so the audit reads its
    // declared type off the common table rather than from a second statement of it — which is the
    // arrangement the DDL that created the table already has.
    let id = crate::Column::new(table.id(), table.stored(table.id()), false);
    for column in core::iter::once(&id).chain(side.columns()) {
      if let Some(drift) = compare(&stored, side.table(), column.name().as_str(), column.ty()) {
        return Err(drift);
      }
    }
  }
  Ok(())
}

/// What this database stores for ONE table, or the refusal that it has no such table.
async fn probe<Db: AsyncSchemaDialect>(
  conn: &mut Db::Connection,
  name: &'static FieldName,
) -> Result<Vec<(String, String)>, SqlSchemaError> {
  let stored = Db::stored(name, conn).await?;
  // A table this database does not have answers NO ROWS rather than refusing, in all three
  // catalogues — so the absent table and the table with no columns are one answer, and a table with
  // no columns is not a shape any dialect here can be made to hold.
  if stored.is_empty() {
    return Err(SqlSchemaError::Missing(name));
  }
  Ok(stored)
}

/// ONE declared column against a probe's answer — the drift it is, or [`None`] where there is none.
///
/// `column` is the name as the CATALOGUE reports one: unbracketed, and joined where a shaped
/// descriptor's field spans several. The drift carries it OWNED for that reason — see
/// [`SqlSchemaError`], where the two roads' names are.
fn compare(
  stored: &[(String, String)],
  table: &'static FieldName,
  column: &str,
  declared: &'static str,
) -> Option<SqlSchemaError> {
  let Some((_, ty)) = stored.iter().find(|(name, _)| name == column) else {
    return Some(SqlSchemaError::Column {
      table,
      column: column.to_owned(),
    });
  };
  (!ty.eq_ignore_ascii_case(declared)).then(|| SqlSchemaError::Type {
    table,
    column: column.to_owned(),
    declared,
    stored: ty.clone(),
  })
}

#[cfg(test)]
mod composite_tests;
#[cfg(test)]
mod index_tests;
#[cfg(test)]
mod indexed_tests;
#[cfg(test)]
mod shadow_tests;
#[cfg(test)]
mod shaped_tests;
#[cfg(test)]
mod tests;
#[cfg(test)]
mod unique_tests;