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
//! The WRITE lane: the verbs a record is stored and REMOVED with, the statement each renders, and
//! the ONE transaction a multi-table write runs inside.
//!
//! ```text
//! insert   INSERT INTO <table> (<columns…>[, <companions…>]) VALUES (?, …)
//!          INSERT INTO <side> (<id>, <ancestors…>) VALUES (?, …)                  the row's own
//!          INSERT INTO <edge> (<keys…>, <ordinal>, <value>) VALUES (?, ?, ?), …   per collection
//!
//! upsert   INSERT INTO <table> (<columns…>[, <companions…>]) VALUES (?, …) <the keyed replace>
//!          DELETE FROM <side> WHERE <id> = ?                                      per upstream
//!          INSERT INTO <side> (<id>, <ancestors…>) VALUES (?, …)                  the row's own
//!          DELETE FROM <edge> WHERE <key> = ? AND …                               per collection
//!          INSERT INTO <edge> (<keys…>, <ordinal>, <value>) VALUES (?, ?, ?), …   per collection
//!
//! delete   DELETE FROM <edge> WHERE <id> IN (SELECT <id> FROM <table> WHERE …)    per collection
//!          DELETE FROM <side> WHERE <id> IN (SELECT <id> FROM <table> WHERE …)    per upstream
//!          DELETE FROM <table> WHERE <the predicate>                              the row's own
//! ```
//!
//! # Two verbs, and the difference is what a taken address MEANS
//!
//! [`insert`] is the plain statement: an address that is already stored is the dialect's own
//! constraint violation, surfaced as [`SqlWriteError::Sql`] and never swallowed. [`upsert`] is the
//! KEYED REPLACE on that same address — `ON CONFLICT (…) DO UPDATE` on SQLite and PostgreSQL, `ON
//! DUPLICATE KEY UPDATE` on MySQL — so a caller that means *store this, whatever was there* says so
//! rather than reading a driver's error code to find out which case it was in.
//!
//! # The crossing is the DECLARED one, and there is no way past it
//!
//! A record reaches this lane through [`SqlWrite::row`] and through nothing else — the dual of
//! [`SqlQuery::record`], the same columns and the same declared conversions read the other way. So
//! every bind below is rendered off the ROW, which is the projection a declaration's `from` and
//! `try_from` legs BUILD, and a value that never crossed has no way onto a statement: the engine
//! holds a `Row` and cannot name a record's field.
//!
//! # A COMPANION column needs no bracket at all, and that is the shape rather than a shortcut
//!
//! A `sortable` collection's order-preserving companion is a column of the ROW, so the `INSERT` that
//! writes the row writes it: one statement, one set of binds, and the dual write is inside the
//! single-transaction law by construction — there being one statement to put the two halves in. The
//! keyed replace assigns it beside every other column, which is the row that matters: an `upsert`
//! REPLACES the edge set, so a companion left out would hold the previous collection's order beside
//! the new collection and every ordered scan would answer from it silently.
//!
//! # The multi-table write is ONE transaction, and the SHAPE decides the bracket
//!
//! A record whose field is a collection stores edge rows, and a record several independent upstreams
//! feed stores a provenance row, so storing one is several statements. They run inside the dialect's
//! write transaction unless the surface the caller passed is already inside one, where the lane is
//! PASSTHROUGH — never nested, never a savepoint, which is [`BeginWrite`]'s ruling and
//! [`BeginRead`](super::BeginRead)'s one direction over.
//!
//! What decides is [`SqlWrite::EDGES`] and [`SqlWrite::SIDES`] — the record's SHAPE — and not what
//! one instance happens to hold. A bracket that appeared only when a collection was non-empty would
//! be a transaction a caller could not predict, and the cost of the rule is one `BEGIN`/`COMMIT`
//! around a record whose collections are all empty. A record with NO edge-stored collection and no
//! provenance table is one statement, which is atomic alone, so it opens nothing at all.
//!
//! # The PROVENANCE row rides the same bracket, and `upsert` clears EVERY upstream's
//!
//! A node several independent upstreams feed stores its ancestry in one provenance table per
//! upstream — the split layout, [`SideTable`](crate::SideTable) — and a row descends from exactly
//! one of them. So a write is the common row and the ONE matching provenance row, together or
//! neither: a common row whose provenance is missing is a row whose upstream cannot be read back,
//! and a provenance row whose common row is missing is what the foreign key refuses outright.
//!
//! An `upsert` deletes from EVERY provenance table before writing the one, and the extra statements
//! are the point rather than a cost: a replacement whose upstream differs from the stored row's
//! would otherwise leave the old provenance row beside the new one, and a row with two ancestries
//! is exactly the state the split layout exists to make unrepresentable. The delete is uniform, so
//! the matching table is cleared by the same statement that clears the others and the insert after
//! it needs no keyed replace of its own.
//!
//! # `upsert` REPLACES the edge set, and the delete is why it has to be transactional
//!
//! A collection is a set of rows rather than a value, so replacing one is *remove what was there,
//! write what is there now* — two statements whose gap is a record with NO elements. Inside the
//! bracket that gap is unobservable and a failure between them stores neither; outside one it would
//! be a window in which a reader sees a record that lost its collection.
//!
//! # What was MEASURED, and what was only RENDERED
//!
//! | engine | what was put to it |
//! |---|---|
//! | **SQLite**, live, in-memory — the same corpus shape [`find`](super::find)'s own walk runs on | every verb: an insert read back through `get`, an upsert replacing a payload at one address, an edge set replaced with the old rows gone and the new ordinals right, a second insert at a taken address refused, a fan-in's common row landing with the ONE provenance row its ancestry belongs in, a replacement that CHANGES upstream leaving one ancestry rather than two, and the torn-write pair below — twice over, once for a collection and once for a provenance row |
//! | **PostgreSQL**, **MySQL** | the rendered TEXT and the bind count, and nothing else — this repository provisions no server, so what the goldens beside this module record is the statement each dialect is SENT rather than what an engine answered |
//!
//! The torn-write pair is the load-bearing one, and it is a pair because one half of it proves
//! nothing alone: the SAME sequence is run twice, once whole and once with the second statement made
//! to fail, and what the second run leaves behind is NO parent row. A lane that wrote without the
//! bracket passes the first run exactly.
//!
//! Three mutations were put to the same corpus:
//!
//! | mutation | what came back |
//! |---|---|
//! | `upsert` does not delete the parent's edge rows first | it depends on the SHAPE, and only one of the two shapes says so. A non-empty SEQUENCE collides — its edge table is keyed on `(key…, ordinal)`, so a second element at ordinal zero is `UNIQUE constraint failed: clip_tags.media_id, clip_tags.id, clip_tags.tags_ordinal` — where an EMPTIED sequence writes no row to collide with and a SET is keyed on its values. So `["a", "b"]` / `["one", "two"]` replaced by `[]` / `["three"]` reads back **`["a", "b"]` / `["one", "three", "two"]`**, with every scalar column right and no error anywhere |
//! | the ordinal is written as `0` for every element rather than as the element's position | every sequence of two elements or more fails at the WRITE, with the same collision — so the mutation is loud, and it is loud only because the edge table's own primary key is what the ordinal is part of. Every rendering test stayed GREEN: an ordinal is a bind, and a bind says nothing in a statement's text |
//! | the bracket is opened AFTER the parent statement rather than before it | exactly ONE test red, and it is the second half of the pair — the parent row is there afterwards where the whole point is that it is not. The first half stayed green, which is the whole reason the two are a pair |
//!
//! The first row is the finding worth reading twice: the shape that catches the missing delete is
//! the one whose STORAGE happens to make it a collision, and the shape that does not is silent. So
//! the emptied collection is a test of its own rather than a case of the replacement's, and the
//! second row is why the ordinal is the plan's own counter rather than anything read off a row.
//!
//! # The REMOVAL half, and why it is the write's own walk read backwards
//!
//! A record is several tables, so removing one is several statements — the same fact the two store
//! verbs already live by, and the same bracket. What differs is the DIRECTION: a write goes parent
//! first because a provenance row's foreign key points at one, so a delete goes parent LAST for
//! exactly that reason. The auxiliary rows lead and the row they belong to follows, which is the
//! insert sequence reversed rather than a sequence of its own.
//!
//! **Nothing here depends on `ON DELETE CASCADE`, and that is deliberate rather than a gap.** The
//! schema lane declares one foreign key — a provenance row's id, pointing at the common row — with
//! no cascade action, and an EDGE table declares no foreign key at all; SQLite then ignores even
//! the one that is declared unless a connection asked for enforcement. So a cascade left to the
//! engine would be a cascade that runs on some deployments and not others, which is the worst of
//! the three available states. The statements say what they mean instead, exactly as an `upsert`'s
//! own edge delete already does.
//!
//! # The cascade addresses its parents by ID, through a SUBQUERY rather than a fetched key set
//!
//! Every auxiliary table carries the common row's id — an edge table among its key columns, a
//! provenance table as its whole primary key — and the id is `UNIQUE` on the parent, so `id` alone
//! names the parent a child row belongs to. That makes one shape serve both:
//! `WHERE <id> IN (SELECT <id> FROM <table> WHERE <the predicate>)`.
//!
//! The alternative was to run the predicate ONCE, decode the matched ids and bind them into each
//! child's statement — which is what the read lane's own gather does one module over. It was
//! declined for a reason a page does not have: a gather's membership is bounded by
//! [`MAX_PAGE_SIZE`](crate::query::MAX_PAGE_SIZE) and a PURGE is bounded by nothing, so binding the
//! set would put the whole verb under the dialect's parameter ceiling — a re-index dropping forty
//! thousand rows would be refused for being large, which is not a property a delete should have.
//!
//! **What the subquery costs is one hazard, and it is MySQL's alone.** MySQL refuses a statement
//! whose subquery reads the table being deleted from (error 1093, *you can't specify target table
//! … for update in FROM clause*). The parent's predicate normally names only the parent's own
//! columns, so the subquery reads the parent while the delete targets a child and the two are
//! different tables — but a filter that asks a question ABOUT A COLLECTION lowers to a correlated
//! subquery over that collection's edge table, and when the statement carrying it is that same edge
//! table's own delete, MySQL rejects it. It is LOUD — a driver error carried as
//! [`SqlWriteError::Sql`], never a wrong answer — and it is confined to one dialect and to filters
//! that name a collection. Closing it would mean wrapping the subquery in a derived table on every
//! dialect to materialize it, which is a cost every correct statement would pay for one dialect's
//! restriction on a subset of filters. MySQL is a RENDERED dialect here rather than a measured one
//! (see the table above), so the honest thing is to write the hazard down.
//!
//! # The filter is REQUIRED, and the empty one is still refused
//!
//! A read's filter is an [`Option`] because *every row* is a legitimate question to ask. A delete's
//! is not optional, so *delete everything* has no spelling — the same enforcement the query
//! builder's typestate makes of a cursor over an unordered set. That closes the case a caller wrote
//! nothing, and it does NOT close the case a caller built a comparison and set no operator on it:
//! that value lowers to zero conjuncts, and the statement it produces is `DELETE FROM <table>` with
//! no `WHERE`. So there is a second, RUNTIME refusal —
//! [`SqlWriteError::Unscoped`](super::SqlWriteError::Unscoped) — asked before anything renders,
//! through [`ComplexComparison::is_empty`](crate::ComplexComparison), which is the one place the
//! fact is visible.

use super::{
  CrossingError, EdgeShape, SqlDialect, SqlQuery, SqlWriteError,
  hydrate::edge,
  snapshot::{BeginWrite, WriteTransaction, close_write, open_write},
};
use crate::{
  FieldName, Identifiable, Record,
  comparison::{
    At, Backend, ComplexComparison, RecordAddress,
    sql::{
      BindCeilingError, Carrier, QuoteIdent, SqlConjunction, SqlLoweringError, fold_columns,
      push_ordinal, quote_ident,
    },
  },
};

/// The pseudo-table a conflicting `INSERT`'s own row is read back under, in the dialects that spell
/// the keyed replace as a conflict clause.
///
/// A [`FieldName`] rather than a literal so the name goes through the family's own bracketing: the
/// word is the same in both dialects and the quote character is not.
#[cfg(any(feature = "sqlite", feature = "postgres"))]
const EXCLUDED: &FieldName = FieldName::new("excluded");

/// How ONE dialect spells a KEYED REPLACE: the clause that turns an `INSERT` into *store this,
/// whatever was at this address*.
///
/// # A method rather than a const, because the two shapes are not one string apart
///
/// SQLite and PostgreSQL name the conflict TARGET and read the incoming row back through a
/// pseudo-table; MySQL names no target — it fires on whatever unique constraint was violated — and
/// reads the incoming row back through a function. So there is no place in one template for a
/// dialect to fill, and a set of consts that covered both would be five of them with the assembly
/// spread across every one.
///
/// The trait is public and unsealed, and this is the shape a fourth dialect joins by: it is handed
/// the address it may name and the columns it must assign, and writes its own clause. Nothing here
/// is a closed vocabulary — a dialect whose keyed replace is `MERGE` writes `MERGE` and no variant
/// has to be added anywhere.
///
/// # It is NOT in [`SqlDialect`]'s bundle
///
/// Only the write lane spends it, and only one of that lane's two verbs. A plain [`insert`] renders
/// no conflict clause at all, so obliging every caller of every read lane to prove a dialect can
/// write one would be the cost the bundle's own note weighs and declines for its three missing
/// predicates.
///
/// # What MySQL's spelling costs, and why the other one was not taken
///
/// `VALUES(<column>)` is deprecated as of MySQL 8.0.20 in favour of an `INSERT … AS <alias>` row
/// alias, and it is what is written here anyway: the alias form needs 8.0.19 or later and is
/// MySQL's alone, where `VALUES()` is understood by every server this driver reaches — MySQL 5.7
/// through 8.4, and MariaDB, which has no row-alias form at all. A deprecation warning on a server
/// that still answers is a cheaper failure than a statement a supported deployment cannot parse.
pub trait Upsert: sqlx::Database + QuoteIdent {
  /// Push the clause that makes this statement a keyed replace.
  ///
  /// `address` is what names the row being replaced — [`SqlQuery::ADDRESS`]'s key columns, which the
  /// schema constrains uniquely — and `columns` is every column the statement WRITES, which is the
  /// same list the `INSERT` names and in the same order: the row's own fields, and after them the
  /// order-preserving companion of each `sortable` collection. A companion left out here would hold
  /// the previous collection's order beside the new collection, so the two halves read ONE list.
  ///
  /// The two arrive in different types because they are different lists. An address names columns
  /// directly — every key column is one column, which is what makes it an address — where a field
  /// may span several and its names are joined by the walk. An impl spends
  /// [`fold_columns`](crate::comparison::sql) for the second and iterates the first.
  ///
  /// The buffer arrives with the `VALUES (…)` list closed, so an impl opens with its own leading
  /// space.
  fn push_upsert(
    address: &[&'static FieldName],
    columns: &[crate::ColumnShape],
    qb: &mut sqlx::QueryBuilder<Self>,
  );
}

/// `ON CONFLICT (<address…>) DO UPDATE SET <c> = <excluded>.<c>, …` — the standard spelling, which
/// SQLite and PostgreSQL share exactly.
///
/// Written once and named by both impls rather than twice: the two dialects differ in their quote
/// character and in nothing else here, and the bracketing is already a per-dialect operation.
#[cfg(any(feature = "sqlite", feature = "postgres"))]
fn push_excluded<Db>(
  address: &[&'static FieldName],
  columns: &[crate::ColumnShape],
  qb: &mut sqlx::QueryBuilder<Db>,
) where
  Db: sqlx::Database + QuoteIdent,
{
  qb.push(" ON CONFLICT (");
  for (at, column) in address.iter().enumerate() {
    if at > 0 {
      qb.push(", ");
    }
    qb.push(quote_ident::<Db>(column).as_str());
  }
  qb.push(") DO UPDATE SET ");
  let source = quote_ident::<Db>(EXCLUDED);
  fold_columns::<Db, usize>(columns, 0, &mut |at, column| {
    if at > 0 {
      qb.push(", ");
    }
    qb.push(column.as_str());
    qb.push(" = ");
    qb.push(source.as_str());
    qb.push(".");
    qb.push(column.as_str());
    at + 1
  });
}

/// SQLite's `ON CONFLICT` is the standard one, and it takes the conflict TARGET — which is what
/// makes the replace keyed rather than *whichever constraint fired*.
#[cfg(feature = "sqlite")]
impl Upsert for sqlx::Sqlite {
  fn push_upsert(
    address: &[&'static FieldName],
    columns: &[crate::ColumnShape],
    qb: &mut sqlx::QueryBuilder<Self>,
  ) {
    push_excluded::<Self>(address, columns, qb);
  }
}

/// PostgreSQL's, spelled identically — see [`push_excluded`], which both of them are.
#[cfg(feature = "postgres")]
impl Upsert for sqlx::Postgres {
  fn push_upsert(
    address: &[&'static FieldName],
    columns: &[crate::ColumnShape],
    qb: &mut sqlx::QueryBuilder<Self>,
  ) {
    push_excluded::<Self>(address, columns, qb);
  }
}

/// MySQL names NO target, and that is a real difference rather than a spelling one: the clause fires
/// on whatever unique constraint the row violated, so a table carrying a second unique column is
/// replaced on THAT collision here where the other two dialects would raise it.
///
/// The address is therefore unread, and it is still a parameter: it is what the trait's contract
/// hands every dialect, and a signature that varied with what an impl happened to use would be one
/// no fourth dialect could write against.
#[cfg(feature = "mysql")]
impl Upsert for sqlx::MySql {
  fn push_upsert(
    _address: &[&'static FieldName],
    columns: &[crate::ColumnShape],
    qb: &mut sqlx::QueryBuilder<Self>,
  ) {
    qb.push(" ON DUPLICATE KEY UPDATE ");
    fold_columns::<Self, usize>(columns, 0, &mut |at, column| {
      if at > 0 {
        qb.push(", ");
      }
      qb.push(column.as_str());
      qb.push(" = VALUES(");
      qb.push(column.as_str());
      qb.push(")");
      at + 1
    });
  }
}

/// How many elements one collection holds on this row.
type EdgeLen<Row> = fn(&Row) -> usize;

/// Bind one element of one collection, at the position the row holds it.
type EdgeElement<Row, Db> = fn(&Row, usize, &mut sqlx::QueryBuilder<Db>);

/// One collection column of a row, as a WRITE stores it.
///
/// # It is a second plan beside [`EdgeCollection`](super::EdgeCollection), and the two are one
/// derivation
///
/// A read's plan says how a collection is MATERIALIZED, which differs per dialect — a native
/// aggregate on the page statement, or a gather of its own. A write has no such fork: both are edge
/// tables, and both are stored by the same two statements. So the two plans answer different
/// questions and share the naming: the table, the value column and the ordinal all come out of
/// [`edge`], which the read lane already spends, so the party that writes an element and the party
/// that reads it back cannot name two tables.
///
/// Keeping them apart is also what lets each stay minimal. A read entry holds the crossings that
/// DECODE, and a write entry holds the two that BIND; folding them would make every row state four
/// function pointers where it spends two.
///
/// # The typed halves are function pointers, for the read plan's reason
///
/// An element's type is the declaration's, so nothing generic over the dialect can name it. What the
/// engine owns is the STATEMENT — the table, the columns, the ceiling, the transaction — and what a
/// declaration owns is the value at each end.
pub struct EdgeWrite<Row, Db: sqlx::Database> {
  path: &'static [&'static FieldName],
  shape: EdgeShape,
  /// What ONE element's type spans — EMPTY for a scalar element, which is
  /// [`ColumnKind::SEGMENTS`](crate::ColumnKind::SEGMENTS)' own default arriving here.
  segments: &'static [&'static FieldName],
  len: EdgeLen<Row>,
  element: EdgeElement<Row, Db>,
}

impl<Row, Db: sqlx::Database> EdgeWrite<Row, Db> {
  /// A collection stored in the edge table `path` names, whose rows this row's `len` and `element`
  /// supply.
  ///
  /// `path` names the column from the ROW down — one segment for a column of the row itself, and one
  /// per level for a column of a composite inside it — which is the same path
  /// [`EdgeCollection`](super::EdgeCollection) states and the same one
  /// [`edge_table`](crate::comparison::sql::edge_table) derives the second table's name from.
  #[inline]
  #[must_use]
  pub const fn new(
    path: &'static [&'static FieldName],
    shape: EdgeShape,
    len: EdgeLen<Row>,
    element: EdgeElement<Row, Db>,
  ) -> Self {
    Self {
      path,
      shape,
      segments: &[],
      len,
      element,
    }
  }

  /// A collection of COMPOSITE elements — `segments` names what one element's type spans, in the
  /// order the columns are declared and bound.
  ///
  /// # The BIND is unchanged, and that is [`Carrier`]'s own arrangement
  ///
  /// `element` still writes ONE element at one position, and a composite one writes several
  /// placeholders with its own separators between them — *a type spanning two pushes two
  /// placeholders*, which is what [`Carrier::push_bind`] already says and what makes a declaration's
  /// write leg identical for both. What the plan needs from a composite is the COLUMN LIST and the
  /// bind ARITHMETIC, and both are read off `segments`.
  #[inline]
  #[must_use]
  pub const fn composite(
    path: &'static [&'static FieldName],
    shape: EdgeShape,
    segments: &'static [&'static FieldName],
    len: EdgeLen<Row>,
    element: EdgeElement<Row, Db>,
  ) -> Self {
    assert!(
      !segments.is_empty(),
      "a composite element states its parts, and an element of no columns is the scalar answer — \
       `EdgeWrite::new` is the form that spells one"
    );
    Self {
      path,
      shape,
      segments,
      len,
      element,
    }
  }
}

impl<Row, Db: sqlx::Database> Clone for EdgeWrite<Row, Db> {
  #[inline]
  fn clone(&self) -> Self {
    *self
  }
}

impl<Row, Db: sqlx::Database> Copy for EdgeWrite<Row, Db> {}

/// Compute and BIND one row's order-preserving companion value.
///
/// One function pointer where [`EdgeWrite`] spends two, and the asymmetry is the shape: an edge
/// table's rows are a LENGTH and an element per position, where a companion is ONE value — the whole
/// collection, through [`collection_sort_key`](crate::collection_sort_key).
type ShadowKey<Row, Db> = fn(&Row, &mut sqlx::QueryBuilder<Db>);

/// One collection's ORDER-PRESERVING COMPANION COLUMN, as a WRITE fills it.
///
/// # It rides the PARENT statement, which is what makes the dual write free
///
/// The companion is a column of the row, so it is written by the `INSERT` that writes the row: one
/// statement, the same binds, and no second round trip. The single-transaction law is therefore
/// satisfied by CONSTRUCTION rather than by a bracket somebody has to remember — a row and its
/// companion cannot be written apart, there being one statement to write them with.
///
/// The keyed replace assigns it too, and that is a correctness row rather than a completeness one:
/// an `upsert` REPLACES the edge set, so a companion left out of the assignment list would hold the
/// PREVIOUS collection's order beside the new collection, and every ordered scan would answer from
/// it with no error anywhere.
///
/// # The bind is a function pointer, for [`EdgeWrite`]'s reason
///
/// The element's type is the declaration's, so nothing generic over the dialect can name it. What
/// the engine owns is the STATEMENT — the column list, the placeholders, the transaction — and what
/// a declaration owns is the value.
pub struct ShadowWrite<Row, Db: sqlx::Database> {
  column: &'static FieldName,
  key: ShadowKey<Row, Db>,
}

impl<Row, Db: sqlx::Database> ShadowWrite<Row, Db> {
  /// The companion column `column` names, whose value this row's `key` supplies.
  ///
  /// `column` is the name the descriptor's [`Shadow`](crate::Shadow) carries and the order term
  /// renders — minted once, where the declaration is read, so no party spells a second column.
  #[inline]
  #[must_use]
  pub const fn new(column: &'static FieldName, key: ShadowKey<Row, Db>) -> Self {
    Self { column, key }
  }

  /// The companion column's own name.
  #[inline]
  #[must_use]
  pub const fn column(&self) -> &'static FieldName {
    self.column
  }
}

impl<Row, Db: sqlx::Database> Clone for ShadowWrite<Row, Db> {
  #[inline]
  fn clone(&self) -> Self {
    *self
  }
}

impl<Row, Db: sqlx::Database> Copy for ShadowWrite<Row, Db> {}

/// Bind one ancestor column of one provenance row, at the position its table names it.
type SideAncestor<Row, Db> = fn(&Row, usize, &mut sqlx::QueryBuilder<Db>);

/// ONE upstream's provenance table, as a WRITE stores a row's ancestry in it.
///
/// # It is the descriptor's twin, and neither reads the other
///
/// [`SideTable`](crate::SideTable) says what the table IS — its name and each ancestor column's
/// stored type — and this says how a row's ancestry gets INTO it. The two lists of column names
/// agree because the same declaration writes both, exactly as [`SqlQuery::COLUMNS`] and a
/// descriptor's own column list do, and [`describes`](crate::describes) is the audit that holds one
/// against the other.
///
/// The common row's id is not among [`columns`](Self::columns) here either: it is bound through
/// [`SqlWrite::bind_address`], which is the same crossing an edge table's rows lead with, so a
/// provenance row and the common row it points at cannot be keyed on two different values.
///
/// # The bind is a function pointer, for [`EdgeWrite`]'s reason
///
/// An ancestor's type is the UPSTREAM declaration's, so nothing generic over the dialect can name
/// it. What the engine owns is the statement — the table, the columns, the transaction — and what a
/// declaration owns is the value at each end.
pub struct SideWrite<Row, Db: sqlx::Database> {
  table: &'static FieldName,
  columns: &'static [&'static FieldName],
  ancestor: SideAncestor<Row, Db>,
}

impl<Row, Db: sqlx::Database> SideWrite<Row, Db> {
  /// The provenance table `table` names, holding the ancestors `columns` names, bound by
  /// `ancestor`.
  #[inline]
  #[must_use]
  pub const fn new(
    table: &'static FieldName,
    columns: &'static [&'static FieldName],
    ancestor: SideAncestor<Row, Db>,
  ) -> Self {
    Self {
      table,
      columns,
      ancestor,
    }
  }

  /// The table this variant's ancestors are stored in.
  #[inline]
  #[must_use]
  pub const fn table(&self) -> &'static FieldName {
    self.table
  }

  /// That variant's ancestor columns, in the order they are bound — the common row's id leads the
  /// statement and is not among them.
  #[inline]
  #[must_use]
  pub const fn columns(&self) -> &'static [&'static FieldName] {
    self.columns
  }
}

impl<Row, Db: sqlx::Database> Clone for SideWrite<Row, Db> {
  #[inline]
  fn clone(&self) -> Self {
    *self
  }
}

impl<Row, Db: sqlx::Database> Copy for SideWrite<Row, Db> {}

/// One database's stored row, as the engine WRITES it.
///
/// # A second trait over [`SqlQuery`], which is the split [`SqlOrdering`] already makes
///
/// Everything a write shares with a query — the table, the columns, the address — is stated once, on
/// the trait that already holds them, so a lane that stores a row and a lane that reads one cannot
/// disagree about which table or which columns. What is here is the three things only a write needs:
/// the crossing INTO the row, the binds off it, and the edge plan its collections are stored by.
///
/// The supertrait also fixes what a write-only row would be, and the answer today is that there is
/// none: a row that cannot be raised back into its record has no [`SqlQuery`] impl, so it has no
/// write lane either. That is the emission's own gate read one layer down rather than a new one —
/// nothing is refused here that was not already unqueryable — and a declaration that wants the
/// asymmetry is what would make this trait stand alone.
///
/// # Every bind goes through the DECLARED crossing
///
/// [`row`](Self::row) is the only way in and [`bind`](Self::bind) reads what it produced, so a
/// declaration's write legs are on the path of every value this lane sends. There is no ad-hoc
/// conversion beside them, and there is nowhere to put one: the engine holds a `Row` and nothing
/// generic over the dialect can name a record's field.
pub trait SqlWrite<Db: Backend + sqlx::Database>: SqlQuery<Db> {
  /// Every collection column this database stores in an EDGE TABLE, and how each is bound.
  ///
  /// It DEFAULTS to none, for the read plan's reason: a record with no collection, and a record
  /// whose collections this database keeps in a column of the row, both state nothing and both write
  /// exactly one statement.
  ///
  /// A PostgreSQL row that keeps a collection in a native array states nothing here either, and that
  /// is the same partition [`COLUMNS`](SqlQuery::COLUMNS) already draws: an array column is a value
  /// of the row, so it is bound by [`bind`](Self::bind) like any other.
  const EDGES: &'static [EdgeWrite<Self, Db>] = &[];

  /// Every PROVENANCE table this node's rows can descend through — one per upstream, in the order
  /// the declaration named them.
  ///
  /// It DEFAULTS to none, which is a node whose ancestry is columns of its own row: a root, a child
  /// of one upstream, and a fusion all state nothing here and all write exactly one row.
  ///
  /// The list is the SHAPE, and the shape is what decides the bracket — [`EDGES`](Self::EDGES)'s own
  /// law, for its own reason. Which of these tables THIS row goes in is [`side`](Self::side), and
  /// what makes the pair honest is that a node with no provenance table has no side to name.
  const SIDES: &'static [SideWrite<Self, Db>] = &[];

  /// Every ORDER-PRESERVING COMPANION COLUMN this row carries, and how each is filled — the
  /// `sortable` word's write half.
  ///
  /// It DEFAULTS to none, for [`EDGES`](Self::EDGES)'s reason: a record with no sortable collection
  /// writes exactly the statement it wrote before the word existed, down to the column list.
  ///
  /// Every entry names a collection this database keeps in an EDGE TABLE, so a non-empty list here
  /// implies a non-empty [`EDGES`](Self::EDGES) — which is what makes the transaction bracket
  /// automatic: the shape already opened one. A PostgreSQL row keeping its collection in a native
  /// array states nothing here, the array ordering itself.
  const SHADOWS: &'static [ShadowWrite<Self, Db>] = &[];

  /// The provenance table THIS row's ancestry goes in — one of [`SIDES`](Self::SIDES), or [`None`]
  /// where there are none.
  ///
  /// A row descends from ONE upstream, so it answers with one entry rather than a list, and it
  /// answers with a BORROW of the plan it belongs to rather than an index into it — an index is a
  /// number that can be out of range, and a reference into the const cannot name a table this node
  /// does not have.
  ///
  /// It is per INSTANCE where [`SIDES`](Self::SIDES) is per type, because which upstream a row came
  /// from is what varies between two rows of one node. That is also the whole of what varies: the
  /// payload is uniform, which is why the common table can hold every row of them.
  #[inline]
  fn side(&self) -> Option<&'static SideWrite<Self, Db>> {
    None
  }

  /// The row this record becomes — the DUAL of [`SqlQuery::record`], the same columns and the same
  /// declared conversions read the other way.
  ///
  /// # Errors
  ///
  /// Whatever the declaration's own write conversions refuse with: `<db>(try_from(…))` is
  /// on this path, and the cell beside it is the type its refusal is named by.
  fn row(record: Self::Record) -> Result<Self, CrossingError>;

  /// Bind this row's `at`th column, in the order [`COLUMNS`](SqlQuery::COLUMNS) names them.
  ///
  /// The engine writes every bracket, every comma and the whole `VALUES` list around it, so an
  /// implementation states the VALUES and nothing about the statement.
  fn bind(&self, at: usize, qb: &mut sqlx::QueryBuilder<Db>);

  /// Bind this row's `at`th ADDRESS column, in the order [`ADDRESS`](SqlQuery::ADDRESS) names them.
  ///
  /// Its own method rather than a position in [`bind`](Self::bind), because the two lists are
  /// indexed by different things: an edge table correlates on the address, whose columns are a
  /// SUBSET of the row's in an order of their own, and a row that had to map one index onto the
  /// other would be a place the mapping could be got wrong.
  fn bind_address(&self, at: usize, qb: &mut sqlx::QueryBuilder<Db>);
}

/// `INSERT INTO <table> (<columns…>) VALUES ` — a RECORD table's opening, up to its first row of
/// binds.
///
/// `columns` is what [`stored_columns`] answers: the row's own fields, and after them the
/// order-preserving companion of each `sortable` collection.
///
/// `table` arrives BRACKETED, because its caller holds a row's table as a [`FieldName`] and quotes
/// it. The key-only auxiliary tables do not come through here: an edge table's and a provenance
/// table's column lists are FIELD NAMES rather than shapes, so each renders its own opening.
fn open_insert<Db>(table: &str, columns: &[crate::ColumnShape]) -> sqlx::QueryBuilder<Db>
where
  Db: SqlDialect,
{
  let mut qb = sqlx::QueryBuilder::new("INSERT INTO ");
  qb.push(table);
  qb.push(" (");
  fold_columns::<Db, usize>(columns, 0, &mut |at, column| {
    if at > 0 {
      qb.push(", ");
    }
    qb.push(column.as_str());
    at + 1
  });
  qb.push(") VALUES ");
  qb
}

/// The parent row's statement: one `INSERT`, binding every column the row carries.
///
/// ```sql
/// INSERT INTO "clip" ("media_id", "id", "title", "score") VALUES (?, ?, ?, ?)
/// ```
fn insert_statement<Row, Db>(row: &Row) -> sqlx::QueryBuilder<Db>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect,
{
  let written = stored_columns::<Row, Db>();
  let mut qb = open_insert::<Db>(quote_ident::<Db>(Row::ADDRESS.table()).as_str(), &written);
  qb.push("(");
  // Indexed by FIELD, where the list above is indexed by column: a field whose type spans two
  // columns is one `bind` here and pushes both placeholders itself, which is what keeps the two
  // lists the same width without either side being told the number.
  for at in 0..Row::COLUMNS.len() {
    if at > 0 {
      qb.push(", ");
    }
    row.bind(at, &mut qb);
  }
  // The companions come last, in the order the list above named them — one value apiece, and each
  // computed from the collection this row holds rather than read off anything stored.
  for (at, shadow) in Row::SHADOWS.iter().enumerate() {
    if at > 0 || !Row::COLUMNS.is_empty() {
      qb.push(", ");
    }
    (shadow.key)(row, &mut qb);
  }
  qb.push(")");
  qb
}

/// Every column the parent statement WRITES: the record's own, then its companions.
///
/// One list, read by both halves of the statement — the `INSERT`'s column list and the keyed
/// replace's assignment list — for the reason the upsert's own note gives: two lists that agree
/// today are two lists that can drift, and here the drift is a companion holding the previous
/// collection's order.
///
/// # It BORROWS where nothing was declared, and that is the invariant rather than a saving
///
/// [`ColumnShape`](crate::ColumnShape)'s own note states it: a row's column list is declared with
/// the schema, once, and *nothing builds one per query*. A row with no companion therefore gets
/// [`Row::COLUMNS`](SqlQuery::COLUMNS) itself and this function is a borrow — the statement it
/// renders is the statement it rendered before the word existed, down to what was allocated.
///
/// A row WITH one pays the join, and the pair is what makes that honest: the const holds the
/// record's columns and the plan holds the framework's, so there is nothing to concatenate at
/// declaration time and the join is the shortest place it can be made. Such a write is already
/// encoding a collection per companion, which is the larger of the two costs by far.
///
/// A companion is [`Plain`](crate::ColumnShape::Plain) by construction: it is one binary column
/// whatever the collection holds, so nothing here can be composite.
fn stored_columns<Row, Db>() -> std::borrow::Cow<'static, [crate::ColumnShape]>
where
  Row: SqlWrite<Db>,
  Db: Backend + sqlx::Database,
{
  if Row::SHADOWS.is_empty() {
    return std::borrow::Cow::Borrowed(Row::COLUMNS);
  }
  let mut written = Row::COLUMNS.to_vec();
  written.extend(
    Row::SHADOWS
      .iter()
      .map(|shadow| crate::ColumnShape::Plain(crate::ColumnName::of(shadow.column()))),
  );
  std::borrow::Cow::Owned(written)
}

/// The same statement with the dialect's keyed replace after it.
///
/// ```sql
/// INSERT INTO "clip" ("media_id", "id", "title", "score") VALUES (?, ?, ?, ?)
///   ON CONFLICT ("media_id", "id") DO UPDATE SET "media_id" = "excluded"."media_id", …
/// ```
///
/// # The assignment list is EVERY column, address included
///
/// It is the same list the `INSERT` names, read a second time — one list rather than two that agree
/// today, which is the discipline the ordering's four readings of one term list already keep. An
/// address column assigns itself under the conflict predicate, where the two are equal by
/// construction, so what the uniform list costs is an assignment that is the identity and what it
/// buys is that no list can be empty: a record whose only stored column is its id would otherwise
/// render `DO UPDATE SET ` with nothing after it, which parses nowhere.
fn upsert_statement<Row, Db>(row: &Row) -> sqlx::QueryBuilder<Db>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect + Upsert,
{
  let mut qb = insert_statement::<Row, Db>(row);
  Db::push_upsert(Row::ADDRESS.keys(), &stored_columns::<Row, Db>(), &mut qb);
  qb
}

/// The statement that stores one row's ancestry: the common row's id, then that variant's ancestor
/// columns.
///
/// ```sql
/// INSERT INTO "caption_frame" ("id", "media_id", "frame") VALUES (?, ?, ?)
/// ```
///
/// The id is bound through [`SqlWrite::bind_address`] at the address's LAST column — `key = lineage
/// ⊕ id`, root-first — which is the same column the foreign key names and the same one the schema
/// declares `UNIQUE`. So the value a provenance row points with and the value the common row is
/// addressed by are one bind rendered twice, and neither is read off anything else.
fn side_insert<Row, Db>(
  row: &Row,
  address: &RecordAddress,
  plan: &SideWrite<Row, Db>,
) -> sqlx::QueryBuilder<Db>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect,
{
  let keys = address.keys();
  let id = keys.len() - 1;
  let mut qb = sqlx::QueryBuilder::new("INSERT INTO ");
  qb.push(quote_ident::<Db>(plan.table).as_str());
  qb.push(" (");
  qb.push(quote_ident::<Db>(keys[id]).as_str());
  for column in plan.columns {
    qb.push(", ");
    qb.push(quote_ident::<Db>(column).as_str());
  }
  qb.push(") VALUES ");
  qb.push("(");
  row.bind_address(id, &mut qb);
  for at in 0..plan.columns.len() {
    qb.push(", ");
    (plan.ancestor)(row, at, &mut qb);
  }
  qb.push(")");
  qb
}

/// The statement that removes one row's ancestry from ONE provenance table, so the insert after it
/// is a REPLACEMENT.
///
/// ```sql
/// DELETE FROM "caption_frame" WHERE "id" = ?
/// ```
///
/// It is rendered for every upstream rather than for the row's own, and the reason is in the
/// module's own note: a replacement whose upstream differs from the stored row's would leave the old
/// provenance row beside the new one.
fn side_delete<Row, Db>(
  row: &Row,
  address: &RecordAddress,
  plan: &SideWrite<Row, Db>,
) -> sqlx::QueryBuilder<Db>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect,
{
  let keys = address.keys();
  let id = keys.len() - 1;
  let mut qb = sqlx::QueryBuilder::new("DELETE FROM ");
  qb.push(quote_ident::<Db>(plan.table).as_str());
  qb.push(" WHERE ");
  qb.push(quote_ident::<Db>(keys[id]).as_str());
  qb.push(" = ");
  row.bind_address(id, &mut qb);
  qb
}

/// The statement that removes one parent's edge rows, so the insert after it is a REPLACEMENT.
///
/// ```sql
/// DELETE FROM "clip_tags" WHERE "media_id" = ? AND "id" = ?
/// ```
///
/// It is an equality per address column rather than the row-value membership the gather renders,
/// because the two are asking different questions: a gather names a PAGE's worth of parents and this
/// names one. The columns are the same and in the same order either way.
///
/// # Errors
///
/// [`SqlLoweringError::EdgeWithoutField`] for a plan entry naming no field, which is [`edge`]'s own
/// refusal carried verbatim.
fn edge_delete<Row, Db>(
  row: &Row,
  address: &RecordAddress,
  plan: &EdgeWrite<Row, Db>,
) -> Result<sqlx::QueryBuilder<Db>, SqlWriteError>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect,
{
  let edge = edge::<Db>(address, plan.path, plan.shape, plan.segments)?;
  let mut qb = sqlx::QueryBuilder::new("DELETE FROM ");
  qb.push(edge.table.as_str());
  qb.push(" WHERE ");
  for (at, column) in address.keys().iter().enumerate() {
    if at > 0 {
      qb.push(" AND ");
    }
    qb.push(quote_ident::<Db>(column).as_str());
    qb.push(" = ");
    row.bind_address(at, &mut qb);
  }
  Ok(qb)
}

/// The statement that writes one collection's elements, all of them in ONE `INSERT`.
///
/// ```sql
/// INSERT INTO "clip_tags" ("media_id", "id", "tags_ordinal", "tags")
///   VALUES (?, ?, ?, ?), (?, ?, ?, ?)
/// ```
///
/// The ORDINAL is the plan's own counter — the element's position in the collection the row holds —
/// rather than anything read off a row or off the engine. It is what a materialization orders by, so
/// a write that did not supply it would store a sequence whose order is the physical one, which is
/// the same defect the gather's own `ORDER BY` exists to close from the other side. A SET's edge
/// table carries no ordinal column and none is bound.
///
/// [`None`] for an EMPTY collection, which is the whole of what *this parent has no elements* is: no
/// rows, which is what a materialization reads back as the empty collection. Rendering `VALUES `
/// with nothing after it would be a statement that parses nowhere, and the empty case is answered
/// here — where the statements are made — rather than by the runner alone, which is the arrangement
/// [`gathers`](super::hydrate) already makes for an empty page.
///
/// # Errors
///
/// [`SqlLoweringError::BindCeiling`] where the elements cost more parameters than the dialect binds
/// in one statement, and [`edge`]'s own refusal for a plan entry naming no field.
fn edge_insert<Row, Db>(
  row: &Row,
  address: &RecordAddress,
  plan: &EdgeWrite<Row, Db>,
) -> Result<Option<sqlx::QueryBuilder<Db>>, SqlWriteError>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect,
  i64: Carrier<Db>,
{
  let len = (plan.len)(row);
  if len == 0 {
    return Ok(None);
  }
  let keys = address.keys();
  // The element's own width, which for a COMPOSITE is its segment count: one bind per column, so the
  // ceiling a caller is refused by counts what the statement actually sends. A scalar's `1` is the
  // EMPTY segment list's answer rather than a case beside it.
  let element = plan.segments.len().max(1);
  let width = keys.len() + usize::from(plan.shape.ordered()) + element;
  // `checked_mul` rather than a product, and the same three numbers `SqlKeyIn` refuses with: a batch
  // whose bind cost does not fit in a `usize` is over every ceiling there is.
  let binds = len.checked_mul(width);
  if binds.is_none_or(|binds| binds > Db::MAX_BIND_PARAMETERS) {
    return Err(
      SqlLoweringError::BindCeiling(BindCeilingError::new(len, width, Db::MAX_BIND_PARAMETERS))
        .into(),
    );
  }

  let edge = edge::<Db>(address, plan.path, plan.shape, plan.segments)?;
  let mut qb = sqlx::QueryBuilder::new("INSERT INTO ");
  qb.push(edge.table.as_str());
  qb.push(" (");
  for column in keys {
    qb.push(quote_ident::<Db>(column).as_str());
    qb.push(", ");
  }
  if let Some(ordinal) = &edge.ordinal {
    qb.push(ordinal);
    qb.push(", ");
  }
  for (at, value) in edge.values.iter().enumerate() {
    if at > 0 {
      qb.push(", ");
    }
    qb.push(value);
  }
  qb.push(") VALUES ");
  for at in 0..len {
    if at > 0 {
      qb.push(", ");
    }
    qb.push("(");
    for key in 0..keys.len() {
      row.bind_address(key, &mut qb);
      qb.push(", ");
    }
    if edge.ordinal.is_some() {
      // The ordinal is 0-based and DENSE, which is what the edge kinds one layer down read it as —
      // and it is bound through the same `push_ordinal` those kinds spend, so a position stored and
      // a position compared against are one operand at one type.
      push_ordinal(&mut qb, at);
      qb.push(", ");
    }
    (plan.element)(row, at, &mut qb);
    qb.push(")");
  }
  Ok(Some(qb))
}

/// Every statement one write runs, in the order it runs them.
///
/// # The refusals come FIRST, and that ordering is the transaction's cheapest half
///
/// Nothing is executed until every statement has rendered, so a plan whose elements pass the bind
/// ceiling is refused with no write attempted at all — where a lane that rendered as it went would
/// have stored the parent row and then rolled it back. It is also what makes the whole write
/// readable from a test: the list IS the statement sequence, so a rendering assertion sees what an
/// engine would.
///
/// `replace` is the verb: an [`upsert`] removes each collection's rows and every provenance row at
/// this address before writing them, an [`insert`] writes them onto a parent that had none.
///
/// # Errors
///
/// As [`edge_delete`] and [`edge_insert`].
fn statements<Row, Db>(
  row: &Row,
  parent: sqlx::QueryBuilder<Db>,
  replace: bool,
) -> Result<Vec<sqlx::QueryBuilder<Db>>, SqlWriteError>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect,
  i64: Carrier<Db>,
{
  let address = Row::ADDRESS;
  let mut statements = vec![parent];
  // The PROVENANCE row before the collections, because it is the row's own ancestry rather than one
  // of its fields — and after the common row, because the foreign key points at one.
  if replace {
    for plan in Row::SIDES {
      statements.push(side_delete::<Row, Db>(row, &address, plan));
    }
  }
  if let Some(plan) = row.side() {
    statements.push(side_insert::<Row, Db>(row, &address, plan));
  }
  for plan in Row::EDGES {
    if replace {
      statements.push(edge_delete::<Row, Db>(row, &address, plan)?);
    }
    if let Some(insert) = edge_insert::<Row, Db>(row, &address, plan)? {
      statements.push(insert);
    }
  }
  Ok(statements)
}

/// Acquire ONE connection, bracket what is about to run on it, and run it.
///
/// The connection is the one every statement of the write uses, exactly as a query's is: the
/// transaction is opened on it and the edge statements are handed it rather than an executor, so
/// there is no second connection for a statement to reach and no way for one of them to land outside
/// the bracket.
async fn run<'c, Row, Db, A>(
  executor: A,
  row: Row,
  parent: sqlx::QueryBuilder<Db>,
  replace: bool,
) -> Result<(), SqlWriteError>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect + WriteTransaction,
  i64: Carrier<Db>,
  for<'e> &'e mut Db::Connection: sqlx::Executor<'e, Database = Db>,
  Db::Arguments: sqlx::IntoArguments<Db>,
  A: sqlx::Acquire<'c, Database = Db> + BeginWrite,
{
  let rendered = statements::<Row, Db>(&row, parent, replace)?;
  send::<Row, Db, A>(executor, rendered).await.map(|_| ())
}

/// The half of [`run`] that has nothing to do with a ROW: acquire, bracket, send, close.
///
/// Split out because the DELETE lane renders no row and still runs the same sequence under the same
/// rule — the shape decides the bracket, and the shape is the two plan lists rather than anything an
/// instance holds. A second copy of the bracket would be a second place the passthrough rule could
/// be got wrong.
///
/// The answer is the LAST statement's [`QueryResult`](sqlx::Database::QueryResult), because that is
/// the one the delete lane needs and the only one it needs: its parent statement runs last — the
/// auxiliary rows lead, per the module note — so the last result is the count of RECORDS removed.
/// The store verbs discard it.
async fn send<'c, Row, Db, A>(
  executor: A,
  mut rendered: Vec<sqlx::QueryBuilder<Db>>,
) -> Result<Db::QueryResult, SqlWriteError>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect + WriteTransaction,
  for<'e> &'e mut Db::Connection: sqlx::Executor<'e, Database = Db>,
  Db::Arguments: sqlx::IntoArguments<Db>,
  A: sqlx::Acquire<'c, Database = Db> + BeginWrite,
{
  let mut held = executor.acquire().await?;
  let conn = &mut *held;
  // The SHAPE decides the bracket, and both lists are shape: a node storing a collection in a second
  // table and a node storing its ancestry in one are the same multi-statement write.
  let multi = !Row::EDGES.is_empty() || !Row::SIDES.is_empty();
  let opened = open_write::<Db, A>(conn, multi).await?;
  let landed = execute::<Db>(conn, &mut rendered).await;
  close_write::<Db>(conn, opened, landed.is_ok()).await?;
  landed
}

/// Send every statement, in order, stopping at the first one the engine refuses, and hand back what
/// the LAST one answered.
///
/// The sequence is never empty — every verb here renders at least the row's own statement — so the
/// default is unreachable rather than a meaning. It is spelled anyway because `Default` is what
/// [`Database::QueryResult`](sqlx::Database::QueryResult) gives and a panic here would be a panic
/// over a case the type system already excludes at every call site.
async fn execute<Db>(
  conn: &mut Db::Connection,
  statements: &mut [sqlx::QueryBuilder<Db>],
) -> Result<Db::QueryResult, SqlWriteError>
where
  Db: SqlDialect,
  for<'e> &'e mut Db::Connection: sqlx::Executor<'e, Database = Db>,
  Db::Arguments: sqlx::IntoArguments<Db>,
{
  let mut last = Db::QueryResult::default();
  for statement in statements {
    last = sqlx::Executor::execute(&mut *conn, statement.build()).await?;
  }
  Ok(last)
}

/// How many rows ONE dialect's query result says a statement changed.
///
/// # A trait of its own rather than an item of [`SqlDialect`]'s bundle
///
/// [`Upsert`]'s own argument, one verb over: only the DELETE lane spends this, so obliging every
/// caller of every read lane to prove a dialect can count rows would be a bound paid everywhere for
/// a fact asked in one place.
///
/// # It exists because sqlx has no such item, which is a fact worth stating rather than working
/// around
///
/// `sqlx::Database::QueryResult` is bounded by `Default + Extend<Self::QueryResult>` and nothing
/// else — every dialect's result type carries a `rows_affected`, and the trait names none of them,
/// so a generic lane cannot read one. Three inherent methods with one signature is exactly what a
/// trait is for, and this is the trait.
///
/// It is public and unsealed for [`Upsert`]'s reason: a fourth dialect joins the write lane by
/// writing this alongside its keyed replace, and neither is a closed vocabulary.
pub trait Affected: sqlx::Database {
  /// How many rows the statement that produced `result` changed.
  fn rows_affected(result: &Self::QueryResult) -> u64;
}

#[cfg(feature = "sqlite")]
impl Affected for sqlx::Sqlite {
  #[inline]
  fn rows_affected(result: &Self::QueryResult) -> u64 {
    result.rows_affected()
  }
}

#[cfg(feature = "postgres")]
impl Affected for sqlx::Postgres {
  #[inline]
  fn rows_affected(result: &Self::QueryResult) -> u64 {
    result.rows_affected()
  }
}

#[cfg(feature = "mysql")]
impl Affected for sqlx::MySql {
  #[inline]
  fn rows_affected(result: &Self::QueryResult) -> u64 {
    result.rows_affected()
  }
}

/// Push ` WHERE <the predicate>` for `filter`, and say how many conjuncts came out.
///
/// The accumulator is the one every other lane's predicates are threaded through, so a filter
/// renders here exactly as it renders on a `SELECT` — same lowering, same bracketing, same
/// refusals — and a `DELETE` cannot answer about a different set of rows than the `find` a caller
/// checked it with.
///
/// # Errors
///
/// A lowering refusal, carried verbatim.
fn push_filter<Db, P>(
  qb: &mut sqlx::QueryBuilder<Db>,
  address: &RecordAddress,
  filter: &P,
) -> Result<usize, SqlWriteError>
where
  Db: SqlDialect,
  P: ComplexComparison<Db>,
{
  let conjunction = filter.apply(At::new(address), SqlConjunction::new(qb))?;
  Ok(conjunction.written())
}

/// The statement that removes the ROWS THEMSELVES.
///
/// ```sql
/// DELETE FROM "clip" WHERE "title" = ?
/// ```
///
/// # The refusal here asks about the RENDERING, which is NOT the question [`delete_statements`]
/// asked
///
/// That one refuses a filter whose [`conjuncts`](ComplexComparison::conjuncts) is zero, which is
/// what an unfilled comparison answers before anything is written. This one refuses a filter that
/// rendered nothing, and the gap between the two is a comparison whose COUNT and whose TEXT
/// disagree — a number an implementor supplies, checked against text an implementor writes. An
/// over-reporting one passes the first refusal and is caught only here, and what it would otherwise
/// produce is `DELETE FROM "clip"` with no `WHERE`: the exact statement both refusals exist to keep
/// out. So the second is a check on the trait's own contract rather than the first one repeated.
///
/// # Errors
///
/// A lowering refusal carried verbatim, and [`SqlWriteError::Unscoped`] as above.
fn parent_delete<Row, Db, P>(filter: &P) -> Result<sqlx::QueryBuilder<Db>, SqlWriteError>
where
  Row: SqlQuery<Db>,
  Db: SqlDialect,
  P: ComplexComparison<Db>,
{
  let address = Row::ADDRESS;
  let mut qb = sqlx::QueryBuilder::new("DELETE FROM ");
  qb.push(quote_ident::<Db>(address.table()).as_str());
  if push_filter::<Db, P>(&mut qb, &address, filter)? == 0 {
    return Err(SqlWriteError::Unscoped(address.table()));
  }
  Ok(qb)
}

/// The statement that removes one AUXILIARY table's rows for every parent the filter names.
///
/// ```sql
/// DELETE FROM "clip_tags" WHERE "id" IN (SELECT "id" FROM "clip" WHERE "title" = ?)
/// ```
///
/// `table` arrives BRACKETED, because its two callers hold their table differently — an edge's name
/// is derived and already quoted, a provenance table's is a [`FieldName`] — and quoting is the same
/// operation either way.
///
/// The correlating column is the parent's ID, which is the key's LAST column (`key = lineage ⊕ id`,
/// root-first) and the one the schema declares `UNIQUE`. Both auxiliary tables carry it: a
/// provenance table AS its primary key, an edge table among the key columns it leads with. So one
/// statement shape serves both, and it is the shape [`side_delete`] already writes at one address.
///
/// # Errors
///
/// A lowering refusal carried verbatim.
fn cascade_delete<Row, Db, P>(
  table: &str,
  filter: &P,
) -> Result<sqlx::QueryBuilder<Db>, SqlWriteError>
where
  Row: SqlQuery<Db>,
  Db: SqlDialect,
  P: ComplexComparison<Db>,
{
  let address = Row::ADDRESS;
  let keys = address.keys();
  // The same position [`side_insert`] and [`side_delete`] reach the id by, indexed the same way:
  // the key's LAST column, `key = lineage ⊕ id` being root-first.
  let id = quote_ident::<Db>(keys[keys.len() - 1]);
  let mut qb = sqlx::QueryBuilder::new("DELETE FROM ");
  qb.push(table);
  qb.push(" WHERE ");
  qb.push(id.as_str());
  qb.push(" IN (SELECT ");
  qb.push(id.as_str());
  qb.push(" FROM ");
  qb.push(quote_ident::<Db>(address.table()).as_str());
  // The count is DISCARDED here and read at the parent's own statement, which is the one place the
  // predicate stands alone: an empty filter would render `… IN (SELECT "id" FROM "clip")` — every
  // parent, which is exactly what the parent's refusal is about — so asking twice would report the
  // same finding from a statement whose sentence is harder to write.
  push_filter::<Db, P>(&mut qb, &address, filter)?;
  qb.push(")");
  Ok(qb)
}

/// Every statement one delete runs, in the order it runs them: the collections, the ancestry, then
/// the rows.
///
/// # The refusals come FIRST, exactly as the store verbs' do
///
/// Nothing is executed until every statement has rendered, so an unscoped filter or a lowering a
/// dialect will not take costs no write at all — where a lane that rendered as it went would have
/// dropped a collection and then refused the row it belonged to.
///
/// # The PARENT is last, and two separate facts say so
///
/// A provenance row's foreign key points at the common row, so removing the common row first is the
/// orphan the constraint exists to refuse. And each auxiliary statement names its parents by reading
/// the parent table, so a parent already gone names nothing — the rows would survive as exactly the
/// orphans this verb is for.
///
/// # Errors
///
/// As [`parent_delete`] and [`cascade_delete`], and [`edge`]'s own refusal for a plan entry naming
/// no field.
fn delete_statements<Row, Db, P>(filter: &P) -> Result<Vec<sqlx::QueryBuilder<Db>>, SqlWriteError>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect,
  P: ComplexComparison<Db>,
{
  let address = Row::ADDRESS;
  // Ahead of every rendering, so the cheapest refusal is also the first: an unscoped filter is a
  // property of the VALUE, and asking it once here is what keeps the sentence about the table
  // rather than about whichever statement noticed.
  if filter.is_empty() {
    return Err(SqlWriteError::Unscoped(address.table()));
  }
  let mut statements = Vec::with_capacity(Row::EDGES.len() + Row::SIDES.len() + 1);
  for plan in Row::EDGES {
    let edge = edge::<Db>(&address, plan.path, plan.shape, plan.segments)?;
    statements.push(cascade_delete::<Row, Db, P>(edge.table.as_str(), filter)?);
  }
  // EVERY provenance table rather than the row's own, which is the same reasoning an `upsert`'s
  // clearing walk states: nothing here holds a row, so which upstream each matched record descended
  // from is not a question this lane can ask — and it does not need to, a table holding no row for
  // an id being a delete that removes none.
  for plan in Row::SIDES {
    statements.push(cascade_delete::<Row, Db, P>(
      quote_ident::<Db>(plan.table).as_str(),
      filter,
    )?);
  }
  statements.push(parent_delete::<Row, Db, P>(filter)?);
  Ok(statements)
}

/// Store `record` as a NEW row, refusing an address that is already taken.
///
/// # The refusal is the DIALECT's, surfaced rather than read
///
/// An address that already holds a row is a constraint violation, and it arrives as
/// [`SqlWriteError::Sql`] carrying whatever the engine said. It is not translated into a *this
/// already exists* of the framework's own, because the framework cannot know which constraint fired:
/// a table may carry uniqueness the address does not name — a declared `unique` field is exactly
/// that — and a lane that reported all of them as one thing would tell a caller the wrong story
/// about half of them. A caller that means *store this whatever was there* has [`upsert`] and does
/// not have to read an error to get it, for the address; a `unique` field elsewhere in the table is
/// a DIFFERENT row's constraint and [`upsert`] does not read it either, so a value colliding with
/// another row's still answers here, unresolved, exactly as a first `insert` would.
///
/// # Errors
///
/// A record that will not cross into this database's row, a statement this dialect will not render,
/// or the driver's own refusal.
pub async fn insert<'c, Row, Db, A>(executor: A, record: Row::Record) -> Result<(), SqlWriteError>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect + WriteTransaction,
  i64: Carrier<Db>,
  for<'e> &'e mut Db::Connection: sqlx::Executor<'e, Database = Db>,
  Db::Arguments: sqlx::IntoArguments<Db>,
  A: sqlx::Acquire<'c, Database = Db> + BeginWrite,
{
  let row = Row::row(record)?;
  let parent = insert_statement::<Row, Db>(&row);
  run::<Row, Db, A>(executor, row, parent, false).await
}

/// Store `record` at its address, REPLACING whatever was there.
///
/// The parent row is replaced by the dialect's own keyed replace — see [`Upsert`] — and every
/// edge-stored collection is replaced as a SET: the parent's rows are removed and the new ones
/// written, in the same transaction as the parent, with the ordinals the new collection has rather
/// than the ones the old one had.
///
/// # What it does NOT do is merge
///
/// A collection that arrives shorter comes back shorter, and an element that was stored and is not
/// in this record is gone. That is what *replace* means and it is the honest reading of a record: the
/// value handed over is the whole collection, so a lane that kept what it did not see would be
/// storing a state no caller ever held.
///
/// # Errors
///
/// As [`insert`].
pub async fn upsert<'c, Row, Db, A>(executor: A, record: Row::Record) -> Result<(), SqlWriteError>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect + WriteTransaction + Upsert,
  i64: Carrier<Db>,
  for<'e> &'e mut Db::Connection: sqlx::Executor<'e, Database = Db>,
  Db::Arguments: sqlx::IntoArguments<Db>,
  A: sqlx::Acquire<'c, Database = Db> + BeginWrite,
{
  let row = Row::row(record)?;
  let parent = upsert_statement::<Row, Db>(&row);
  run::<Row, Db, A>(executor, row, parent, true).await
}

/// Remove the record at `id`, and everything stored FOR it — `true` where a row was there.
///
/// The id is the whole address, exactly as it is for [`get`](super::get): it is `UNIQUE`, so it
/// names at most one row, and the answer is therefore a yes-or-no rather than a count. That is the
/// same narrowing `get` makes when it answers with an [`Option`] where a scan answers with a
/// [`Vec`] — one law, read on the write side.
///
/// # It is the FILTER road at width one, and shares every statement with it
///
/// The predicate is [`SqlQuery::addresses`] over a slice of one — the family's compound-key
/// membership, which is what the read lane's own id road spends — so a by-id delete and a by-filter
/// delete are the same sequence asked with a different predicate. The collections and the ancestry
/// go with the row either way; see the module note for the walk and for what it does NOT ask the
/// engine to cascade.
///
/// # A miss is `Ok(false)` and never an error
///
/// [`get`](super::get)'s uniform-miss rule, one verb over: an address that holds nothing is a fact
/// about the database rather than a failure of the call, and a caller purging a root wants to say
/// *and it was already gone* rather than to sort one driver error from another.
///
/// # Errors
///
/// An id that will not cross into this database's stored type, a statement this dialect will not
/// render, or the driver's own refusal.
pub async fn delete<'c, Row, Db, A>(
  executor: A,
  id: &<Row::Record as Identifiable>::Id,
) -> Result<bool, SqlWriteError>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect + WriteTransaction + Affected,
  for<'e> &'e mut Db::Connection: sqlx::Executor<'e, Database = Db>,
  Db::Arguments: sqlx::IntoArguments<Db>,
  A: sqlx::Acquire<'c, Database = Db> + BeginWrite,
{
  let address = Row::addresses(core::slice::from_ref(id))?;
  let rendered = delete_statements::<Row, Db, Row::Address>(&address)?;
  let landed = send::<Row, Db, A>(executor, rendered).await?;
  Ok(Db::rows_affected(&landed) != 0)
}

/// Remove every record `filter` names, and everything stored FOR each of them — answering with how
/// many RECORDS went.
///
/// This is the re-index road. A source that produced fourteen rows and now produces nine leaves five
/// behind unless something removes them, and *what the source used to produce* is not a set anybody
/// holds: what a caller has is the predicate those rows are under. So the verb takes the predicate.
///
/// # The count is PARENT rows, not statements and not rows touched
///
/// The auxiliary tables lose their rows too, and they are not counted: what a caller asked to remove
/// is records, and a collection's element rows are one record's own storage rather than records of
/// their own. A number that included them would vary with a schema detail rather than with the data.
///
/// # There is no filterless form
///
/// The filter is a required parameter, and a filter that lowers to NO predicate is refused with
/// [`SqlWriteError::Unscoped`] before anything runs. Both halves are the module note's; the second
/// exists because the first cannot close it.
///
/// # Errors
///
/// [`SqlWriteError::Unscoped`] for a filter naming no row, a lowering refusal carried verbatim, or
/// the driver's own refusal.
pub async fn delete_where<'c, Row, Db, A>(
  executor: A,
  filter: &<Row::Record as Record<Db>>::Comparison,
) -> Result<u64, SqlWriteError>
where
  Row: SqlWrite<Db>,
  Db: SqlDialect + WriteTransaction + Affected,
  for<'e> &'e mut Db::Connection: sqlx::Executor<'e, Database = Db>,
  Db::Arguments: sqlx::IntoArguments<Db>,
  A: sqlx::Acquire<'c, Database = Db> + BeginWrite,
{
  let rendered = delete_statements::<Row, Db, _>(filter)?;
  let landed = send::<Row, Db, A>(executor, rendered).await?;
  Ok(Db::rows_affected(&landed))
}

#[cfg(test)]
mod shadow_tests;
#[cfg(test)]
mod tests;