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
//! `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. It still conservatively
//! leaves the trigger untouched (a known gap, not a regression) when a token
//! rewrite can't be proven safe: a body statement writing another table, a
//! subquery touching another table, or a derived table in a `FROM`. Those bail
//! cases are 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'",
];
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 — rewriting
// through the other table — so these stay known gaps rather than differential
// equalities. The invariant guards against a *wrong* rewrite creeping in.)
let bail = [
// Nested subquery references another table that *also* owns an `a`, so a
// bare `a` is ambiguous (it could bind to either) — the global-uniqueness
// prover declines rather than guess.
(
"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'",
"CREATE TRIGGER tr AFTER INSERT ON t BEGIN \
UPDATE t SET b=b+1 WHERE a IN (SELECT a FROM u); END",
),
// 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}");
}
}