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
//! v7.9.27 → v7.16.2 — DO $$ ... $$ acceptance.
//!
//! Pre-v7.16.2 the body was silently no-op'd; mailrs round-10
//! flagged that as SEV-1 (idempotent migrations were invisible).
//! v7.16.2 actually runs the body through the trigger
//! executor's PlPgSqlBlock walker. Tests below cover both the
//! new positive cases (DO actually applies its effect) and the
//! shapes that should remain accept-as-no-op.
use spg_engine::{Engine, QueryResult};
fn ok(eng: &mut Engine, sql: &str) {
let r = eng
.execute(sql)
.unwrap_or_else(|e| panic!("{sql:?}: {e:?}"));
assert!(
matches!(r, QueryResult::CommandOk { .. }),
"{sql:?} → {r:?}"
);
}
#[test]
fn empty_do_block_is_accepted() {
let mut eng = Engine::new();
// Minimum body that parses as a PlPgSqlBlock — `BEGIN END`
// with no statements (equivalent to v7.9.27's silent no-op
// but now goes through the actual executor).
ok(&mut eng, "DO $$ BEGIN END $$");
}
#[test]
fn do_block_with_language_plpgsql() {
let mut eng = Engine::new();
ok(
&mut eng,
"DO $$ BEGIN RAISE NOTICE 'spg logs this'; END $$ LANGUAGE plpgsql",
);
}
#[test]
fn tagged_dollar_quoted_do_block() {
let mut eng = Engine::new();
// Tagged $body$ quoting + real plpgsql block (must include
// BEGIN/END now). Uses pg_class which v7.16.2 synthesises.
ok(
&mut eng,
"DO $body$ \
BEGIN \
IF EXISTS (SELECT 1 FROM pg_catalog.pg_class WHERE relname = 'foo') THEN \
ALTER TABLE foo ADD COLUMN x INT; \
END IF; \
END \
$body$ LANGUAGE plpgsql",
);
}
#[test]
fn do_block_executes_its_body() {
// v7.16.2 — the dogfood guarantee mailrs round-10 wanted:
// DO blocks ACTUALLY alter the catalog. Pre-v7.16.2 this
// was a silent no-op and the assertion below would have
// failed at the SELECT.
let mut eng = Engine::new();
eng.execute("CREATE TABLE t (a INT NOT NULL)").unwrap();
eng.execute("DO $$ BEGIN ALTER TABLE t ADD COLUMN b INT; UPDATE t SET a = a; END $$")
.unwrap();
let r = eng.execute("SELECT b FROM t WHERE a = 0").unwrap();
assert!(matches!(r, QueryResult::Rows { .. }), "{r:?}");
}
#[test]
fn pg_dump_idempotent_do_block_pattern() {
// The shape mailrs migrate-038 / -040 / -042 use — IF
// EXISTS over information_schema.columns followed by an
// ALTER. The condition resolves against the v7.16.2 virtual
// view; the THEN branch fires only if the column genuinely
// exists. Idempotent on re-run.
let mut eng = Engine::new();
eng.execute("CREATE TABLE accounts (id INT NOT NULL)")
.unwrap();
ok(
&mut eng,
"DO $$ \
BEGIN \
IF NOT EXISTS ( \
SELECT 1 FROM information_schema.columns \
WHERE table_name = 'accounts' AND column_name = 'user_id' \
) THEN \
ALTER TABLE accounts ADD COLUMN user_id INT; \
END IF; \
END $$",
);
// Second run is a no-op — column already exists.
ok(
&mut eng,
"DO $$ \
BEGIN \
IF NOT EXISTS ( \
SELECT 1 FROM information_schema.columns \
WHERE table_name = 'accounts' AND column_name = 'user_id' \
) THEN \
ALTER TABLE accounts ADD COLUMN user_id INT; \
END IF; \
END $$",
);
}