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
//! `ALTER TABLE … RENAME COLUMN` must rewrite the renamed column everywhere a
//! dependent trigger reaches it *across objects* — including references buried in
//! a `WHEN` guard's subquery or a body statement that targets / reads a different
//! table, when the renamed column name is globally unique across every base-table
//! source the trigger touches.
//!
//! graphite's column-rename propagation rewrites a trigger's stored schema text
//! token-by-token, guarded by provers. The single-source prover bails when the
//! body touches more than the renamed table; the `NEW`/`OLD`-only prover (for a
//! trigger attached to the renamed table) rewrites just the pseudo-column refs,
//! leaving bare refs stale; the cross-object single-source prover bails on a
//! subquery over another table. So a trigger like
//! `CREATE TRIGGER tr AFTER INSERT ON u BEGIN UPDATE u SET c=1 WHERE c IN
//! (SELECT a FROM t); END` was left untouched after renaming `t.a`, leaving a
//! stale `SELECT a FROM t` that broke the trigger the next time it fired
//! (`no such column: a`).
//!
//! The global-uniqueness prover closes this: it collects every base-table source
//! at every nesting level (the trigger's own target tables, its `WHEN`/body
//! subquery `FROM`s) and, when the renamed column name is unique across all of
//! them, a bare `old` can only bind to the renamed table, so every reference
//! (bare, `<table>.old`/`<alias>.old`, and — when the trigger is on the renamed
//! table — `NEW.old`/`OLD.old`) is rewritten. When the name is *not* globally
//! unique a scope-aware pass resolves each bare `old` innermost-scope-first; and
//! a genuinely *mixed* body — the same bare name binding to the renamed table in
//! one scope and another table in a different scope — is handled via
//! per-occurrence source spans on `Expr::Column`, rewriting exactly the bound
//! occurrences (A-rn3-edge). The multi-source body statements are handled too: an
//! `UPDATE … SET … FROM <sources>` collects its joined `FROM` tables as base
//! sources; a row-assignment `SET (…) = (SELECT … FROM other)` and an
//! `ON CONFLICT … DO UPDATE SET … = (SELECT … FROM other)` upsert collect their
//! nested subqueries' sources. So a `<src>.old` or globally-unique bare `old`
//! reached through any of them rewrites (and the matching DROP COLUMN break is
//! rejected). A `NATURAL`/`USING` join, table-valued-function or derived source in
//! an `UPDATE … FROM`, or a row-assignment target-list that names the renamed
//! column, still bails the trigger untouched — the remaining residual.
//!
//! 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 out(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));
s.trim_end().to_string()
}
#[test]
fn rename_column_rewrites_cross_object_trigger_refs() {
if !sqlite3_available() {
eprintln!("sqlite3 CLI not found; skipping");
return;
}
let g = env!("CARGO_BIN_EXE_graphitesql");
let cases = [
// The found case: a `WHEN` guard's subquery over the renamed table, on a
// trigger attached to an unrelated table.
"CREATE TABLE t(a,b); CREATE TABLE u(c); \
CREATE TRIGGER tr BEFORE INSERT ON u WHEN (SELECT a FROM t LIMIT 1)>0 \
BEGIN SELECT 1; END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// A body statement on another table whose subquery reads the renamed one.
"CREATE TABLE t(a,b); CREATE TABLE u(c); \
CREATE TRIGGER tr AFTER INSERT ON u BEGIN \
UPDATE u SET c=1 WHERE c IN (SELECT a FROM t); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// Trigger ON the renamed table with a multi-source body: the bare `a`
// binds to the renamed table (globally unique), so it is rewritten — the
// `NEW`/`OLD`-only branch would have left it stale.
"CREATE TABLE t(a,b); CREATE TABLE u(c); \
CREATE TRIGGER tr AFTER INSERT ON t BEGIN \
UPDATE t SET b=1 WHERE a IN (SELECT c FROM u); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// `NEW.a` in a `WHEN` guard plus a bare `a` over a multi-source body.
"CREATE TABLE t(a,b); CREATE TABLE u(c); \
CREATE TRIGGER tr AFTER UPDATE ON t WHEN NEW.a>0 BEGIN \
UPDATE t SET b=1 WHERE a IN (SELECT c FROM u); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// `OLD.a` in a body assignment, trigger on the renamed table, subquery
// over another table.
"CREATE TABLE t(a,b); CREATE TABLE u(c); \
CREATE TRIGGER tr AFTER DELETE ON t BEGIN \
UPDATE t SET b=OLD.a WHERE a IN (SELECT c FROM u); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// `INSERT … VALUES` whose value expression is a subquery over the renamed
// table, on a trigger attached to another table.
"CREATE TABLE t(a,b); CREATE TABLE u(c); \
CREATE TRIGGER tr AFTER INSERT ON u BEGIN \
INSERT INTO u VALUES((SELECT a FROM t LIMIT 1)); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// `INSERT … SELECT` into another table, reading the renamed one at two
// nesting levels (a self-join alias inside the subquery).
"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'",
// Renaming a column the trigger never references leaves it byte-unchanged.
"CREATE TABLE t(a,b); CREATE TABLE u(c); \
CREATE TRIGGER tr AFTER INSERT ON u BEGIN \
UPDATE u SET c=1 WHERE c IN (SELECT a FROM t); END; \
ALTER TABLE t RENAME COLUMN b TO bb; SELECT sql FROM sqlite_schema WHERE name='tr'",
// The rewritten trigger still fires correctly: compare schema *and* rows.
"CREATE TABLE t(a,b); CREATE TABLE u(c); \
INSERT INTO u VALUES(5); INSERT INTO t VALUES(5,0); \
CREATE TRIGGER tr AFTER UPDATE OF b ON t BEGIN \
UPDATE t SET b=99 WHERE a IN (SELECT c FROM u) AND a<>NEW.a; END; \
ALTER TABLE t RENAME COLUMN a TO aa; \
INSERT INTO t VALUES(7,1); UPDATE t SET b=2 WHERE aa=7; \
SELECT sql FROM sqlite_schema WHERE name='tr'; SELECT aa,b FROM t ORDER BY aa",
// Scope-aware (A-rn3-edge): the body's `UPDATE u` target *also* owns an
// `a`, so the name is not globally unique — yet the only bare `a` reached
// is the subquery's, binding to `t`. The scope pass rewrites just that one;
// the `UPDATE u`'s own columns are untouched. Matches SQLite's per-scope
// resolution.
"CREATE TABLE t(a,b); CREATE TABLE u(a,c); \
CREATE TRIGGER tr AFTER INSERT ON u BEGIN \
UPDATE u SET c=1 WHERE c IN (SELECT a FROM t); 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): the same bare `a`
// binds to the `UPDATE u` target in the `WHERE` and to `t` in the subquery
// of one statement. Renaming `t.a` rewrites only the inner `a`; the outer
// `a` (bound to `u`) stays — per-occurrence source spans disambiguate them,
// byte-matching SQLite.
"CREATE TABLE t(a,b); CREATE TABLE u(a,c); \
CREATE TRIGGER tr AFTER INSERT ON u BEGIN \
UPDATE u SET c=1 WHERE a IN (SELECT a FROM t); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// A CTE inside a body subquery: the CTE body is rewritten like any scope
// (its `a` binds to `t`), the outer `count(*)` is unaffected — byte-matching
// SQLite. (The CTE body's output column isn't referenced by the outer under
// its old name, so the rewrite is safe.)
"CREATE TABLE t(a,b); CREATE TABLE u(c); \
CREATE TRIGGER tr AFTER INSERT ON u BEGIN \
UPDATE u SET c=(WITH x AS (SELECT a FROM t) SELECT count(*) FROM x); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// `UPDATE … SET … FROM <table>` in the body: the joined `FROM t` is a base
// source, so the renamed `t.a` — read via a qualifier — is rewritten. The
// old code bailed on any `UPDATE … FROM`, leaving the ref stale.
"CREATE TABLE t(a,b); CREATE TABLE u(y,z); \
CREATE TRIGGER tr AFTER INSERT ON u BEGIN \
UPDATE u SET z=t.a FROM t WHERE u.y=t.b; END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// Same, reached by a globally-unique *bare* `a` in the `SET` value (only
// `t` has an `a`, so it binds there and is rewritten).
"CREATE TABLE t(a,b); CREATE TABLE u(y,z); \
CREATE TRIGGER tr AFTER INSERT ON u BEGIN \
UPDATE u SET z=a FROM t WHERE u.y=t.b; END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// The `FROM` source is aliased: `x.a` (x = t) rewrites via the alias qual.
"CREATE TABLE t(a,b); CREATE TABLE u(y,z); \
CREATE TRIGGER tr AFTER INSERT ON u BEGIN \
UPDATE u SET z=x.a FROM t AS x WHERE u.y=x.b; END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// `UPDATE … FROM` where the renamed name also exists in the target table:
// not globally unique, so only the qualified `t.a` rewrites; the target's
// own `a` (in the `WHERE`) is left — scope-aware per-occurrence, matching
// SQLite.
"CREATE TABLE t(a,b); CREATE TABLE u(a,z); \
CREATE TRIGGER tr AFTER INSERT ON u BEGIN \
UPDATE u SET z=t.a FROM t WHERE u.a=t.b; END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// A row-assignment subquery `SET (…) = (SELECT … FROM t)`: the subquery is
// a base source, so its `a` (bound to `t`) is rewritten while the target
// column-list `(z)` is left. The old code bailed on any row-assignment.
"CREATE TABLE t(a,b); CREATE TABLE u(y,z); \
CREATE TRIGGER tr AFTER INSERT ON u BEGIN \
UPDATE u SET (z)=(SELECT a FROM t LIMIT 1); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
// An `ON CONFLICT … DO UPDATE SET … = (SELECT … FROM t)` upsert reached by
// a body `INSERT`: both the inserted `VALUES` subquery and the DO-UPDATE
// subquery read `t.a` and are rewritten. The old code bailed on any upsert.
"CREATE TABLE t(a,b); CREATE TABLE u(y PRIMARY KEY,z); \
CREATE TRIGGER tr AFTER INSERT ON t BEGIN \
INSERT INTO u VALUES(1,(SELECT a FROM t LIMIT 1)) \
ON CONFLICT(y) DO UPDATE SET z=(SELECT a FROM t LIMIT 1); END; \
ALTER TABLE t RENAME COLUMN a TO aa; SELECT sql FROM sqlite_schema WHERE name='tr'",
];
for sql in cases {
assert_eq!(out("sqlite3", sql), out(g, sql), "for {sql}");
}
}