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
//! `ALTER TABLE … RENAME COLUMN` must rewrite references to the renamed column
//! everywhere a dependent trigger names it — including *inside* expression
//! subqueries in the trigger's `WHEN` guard and its body statements (a scalar
//! `(SELECT …)`, `EXISTS`, or `x IN (SELECT …)`). graphite previously bailed out
//! of the trigger rewrite the moment the body or `WHEN` held any subquery,
//! leaving stale references behind so the trigger broke when it next fired
//! (`no such column: a`).
//!
//! graphite now rewrites a trigger attached to the renamed table whose body and
//! `WHEN` target only that table — every body statement targets it and every
//! nested subquery references only it — at every nesting level, bare and
//! `<alias>.`/`NEW.`/`OLD.`-qualified references alike — and, via per-occurrence
//! source spans on `Expr::Column`, the genuinely *mixed* body where the same bare
//! name binds to the renamed table in one scope and another table in a different
//! scope (A-rn3-edge). It still conservatively leaves the trigger untouched (a
//! known gap, not a regression) when a token rewrite can't be proven safe: a
//! derived table in a subquery `FROM`. That bail case is asserted to leave the
//! stored trigger SQL byte-identical (no *wrong* rewrite).
//!
//! Verified against the sqlite3 3.50.4 CLI.
#![cfg(feature = "std")]
use std::process::Command;
fn sqlite3_available() -> bool {
Command::new("sqlite3").arg("--version").output().is_ok()
}
fn run(bin: &str, sql: &str) -> String {
let o = Command::new(bin).arg(":memory:").arg(sql).output().unwrap();
let mut s = String::from_utf8_lossy(&o.stdout).into_owned();
s.push_str(&String::from_utf8_lossy(&o.stderr));
let mut lines = Vec::new();
for line in s.lines() {
let mut t = line.trim_end();
if t.trim_start().starts_with('^') {
continue;
}
for prefix in [
"Error: ",
"in prepare, ",
"stepping, ",
"SQL error: ",
"error: ",
] {
t = t.strip_prefix(prefix).unwrap_or(t);
}
lines.push(t.to_string());
}
lines.join("\n")
}
#[test]
fn trigger_rename_column_subquery_matches_sqlite() {
if !sqlite3_available() {
eprintln!("sqlite3 CLI not found; skipping");
return;
}
let g = env!("CARGO_BIN_EXE_graphitesql");
// A trigger on the renamed table whose body/WHEN nest subqueries over only
// that table: the rename now rewrites every reference (bare, `NEW.`/`OLD.`,
// and `<alias>.`-qualified, nested too) so the stored schema stays byte-exact.
let matching = [
// Scalar subquery in an UPDATE body.
"CREATE TABLE t(a,b); CREATE TRIGGER tr AFTER INSERT ON t BEGIN \
UPDATE t SET b=b+1 WHERE a=(SELECT max(a) FROM t t2); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// Subquery in the WHEN guard, plus NEW.<col> in the body.
"CREATE TABLE t(a,b); CREATE TRIGGER tr AFTER UPDATE ON t \
WHEN NEW.a > (SELECT min(a) FROM t t2) BEGIN UPDATE t SET b=0 WHERE a=NEW.a; END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// Scalar subquery in a DELETE body.
"CREATE TABLE t(a,b); CREATE TRIGGER tr AFTER INSERT ON t BEGIN \
DELETE FROM t WHERE a < (SELECT avg(a) FROM t t2); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// INSERT ... SELECT into the same table with a nested IN(SELECT) subquery.
"CREATE TABLE t(a,b); CREATE TRIGGER tr AFTER UPDATE ON t BEGIN \
INSERT INTO t(a,b) SELECT a+100,b FROM t t2 WHERE a IN (SELECT a FROM t t3); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// EXISTS in the WHEN guard.
"CREATE TABLE t(a,b); CREATE TRIGGER tr AFTER INSERT ON t \
WHEN EXISTS(SELECT 1 FROM t t2 WHERE t2.a < NEW.a) BEGIN UPDATE t SET b=b+1; END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// Control: no subquery anywhere (was already rewritten correctly).
"CREATE TABLE t(a,b); CREATE TRIGGER tr AFTER INSERT ON t BEGIN \
UPDATE t SET b=b+1 WHERE a=NEW.a; END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// Renaming the *other* column must not disturb the subquery's `a` refs.
"CREATE TABLE t(a,b); CREATE TRIGGER tr AFTER INSERT ON t BEGIN \
UPDATE t SET b=b+1 WHERE a=(SELECT max(a) FROM t t2); END; \
ALTER TABLE t RENAME COLUMN b TO bb; SELECT sql FROM sqlite_schema WHERE name='tr'",
// The rewritten trigger still fires correctly: compare both the schema
// and the rows it produces over real data.
"CREATE TABLE t(a,b); INSERT INTO t VALUES(1,10),(2,20); \
CREATE TRIGGER tr AFTER UPDATE OF b ON t BEGIN \
UPDATE t SET b=b+1 WHERE a=(SELECT max(a) FROM t t2) AND a<>NEW.a; END; \
ALTER TABLE t RENAME COLUMN a TO aa; UPDATE t SET b=99 WHERE aa=1; \
SELECT sql FROM sqlite_schema WHERE name='tr'; SELECT aa,b FROM t ORDER BY aa",
// A body statement writes another table (`log`), but the renamed column
// name is globally unique across every source the trigger touches, so a
// bare `a` can only bind to `t` — every reference is rewritten (the
// global-uniqueness prover; see `tests/rename_column_trigger_subquery.rs`
// for the cross-object family). Previously a bail case.
"CREATE TABLE t(a,b); CREATE TABLE log(x); \
CREATE TRIGGER tr AFTER INSERT ON t BEGIN \
INSERT INTO log SELECT a FROM t WHERE a=(SELECT max(a) FROM t t2); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// Mixed scope (A-rn3-edge, now handled for triggers too): the trigger body
// `UPDATE t SET b=b+1 WHERE a IN (SELECT a FROM u)` has the outer `a` bound
// to the UPDATE target `t` and the inner `a` bound to `u`. Renaming `t.a`
// rewrites only the outer `a` (byte-matching SQLite); the subquery's `a`
// stays. Per-occurrence source spans on `Expr::Column` disambiguate them.
"CREATE TABLE t(a,b); CREATE TABLE u(a,c); \
CREATE TRIGGER tr AFTER INSERT ON t BEGIN \
UPDATE t SET b=b+1 WHERE a IN (SELECT a FROM u); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// Mixed, other direction: renaming `u.a` rewrites only the inner `a`.
"CREATE TABLE t(a,b); CREATE TABLE u(a,c); \
CREATE TRIGGER tr AFTER INSERT ON t BEGIN \
UPDATE t SET b=b+1 WHERE a IN (SELECT a FROM u); END; \
ALTER TABLE u RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// Mixed inside an INSERT … SELECT body over a log table.
"CREATE TABLE t(a,b); CREATE TABLE u(a,c); CREATE TABLE log(x); \
CREATE TRIGGER tr AFTER INSERT ON log BEGIN \
INSERT INTO log SELECT a FROM t WHERE a IN (SELECT a FROM u); END; \
ALTER TABLE u RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
];
for sql in matching {
assert_eq!(run("sqlite3", sql), run(g, sql), "for {sql}");
}
// Bail cases: a token rewrite can't be proven safe, so graphite leaves the
// stored trigger SQL byte-identical. (SQLite does more here — aborting on a
// derived table — so this stays a known gap rather than a differential
// equality. The invariant guards against a *wrong* rewrite creeping in.)
let bail = [
// Derived table in a nested subquery's FROM.
(
"CREATE TABLE t(a,b); CREATE TRIGGER tr AFTER INSERT ON t BEGIN \
UPDATE t SET b=b+1 WHERE a IN (SELECT a FROM (SELECT a FROM t)); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
"CREATE TRIGGER tr AFTER INSERT ON t BEGIN \
UPDATE t SET b=b+1 WHERE a IN (SELECT a FROM (SELECT a FROM t)); END",
),
];
for (sql, unchanged) in bail {
assert_eq!(run(g, sql), unchanged, "bail invariant for {sql}");
}
}