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
|
# 2022 May 27
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#*************************************************************************
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix altertrig
# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {
finish_test
return
}
proc collapse_whitespace {in} {
regsub -all {[ \t\n]+} [string trim $in] { }
}
proc do_whitespace_sql_test {tn sql res} {
set got [execsql $sql]
set wgot [list]
set wres [list]
foreach g $got { lappend wgot [collapse_whitespace $g] }
foreach r $res { lappend wres [collapse_whitespace $r] }
uplevel [list do_test $tn [list set {} $wgot] $wres]
}
do_execsql_test 1.0 {
CREATE TABLE t1(x);
CREATE TABLE t2(y);
CREATE TABLE t3(z);
CREATE TABLE t4(a);
CREATE TRIGGER r1 INSERT ON t1 BEGIN
UPDATE t1 SET d='xyz' FROM t2, t3;
END;
}
do_whitespace_sql_test 1.1 {
ALTER TABLE t3 RENAME TO t5;
SELECT sql FROM sqlite_schema WHERE type='trigger';
} {{
CREATE TRIGGER r1 INSERT ON t1 BEGIN
UPDATE t1 SET d='xyz' FROM t2, "t5";
END
}}
do_execsql_test 1.2 {
DROP TRIGGER r1;
CREATE TRIGGER r1 INSERT ON t1 BEGIN
UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM t5);
END;
}
do_whitespace_sql_test 1.3 {
ALTER TABLE t5 RENAME TO t3;
SELECT sql FROM sqlite_schema WHERE type='trigger';
} {{
CREATE TRIGGER r1 INSERT ON t1 BEGIN
UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM "t3");
END
}}
foreach {tn alter update final} {
1 {
ALTER TABLE t3 RENAME TO t10
} {
UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM t3)
} {
UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM "t10")
}
2 {
ALTER TABLE t3 RENAME TO t10
} {
UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT e FROM t3))
} {
UPDATE t1 SET a='xyz' FROM "t10", (SELECT * FROM (SELECT e FROM "t10"))
}
3 {
ALTER TABLE t3 RENAME e TO abc
} {
UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT e FROM t3))
} {
UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT abc FROM t3))
}
4 {
ALTER TABLE t2 RENAME c TO abc
} {
UPDATE t1 SET a='xyz' FROM t3, (SELECT 1 FROM t2 WHERE c)
} {
UPDATE t1 SET a='xyz' FROM t3, (SELECT 1 FROM t2 WHERE abc)
}
5 {
ALTER TABLE t2 RENAME c TO abc
} {
UPDATE t1 SET a=t2.c FROM t2
} {
UPDATE t1 SET a=t2.abc FROM t2
}
6 {
ALTER TABLE t2 RENAME c TO abc
} {
UPDATE t1 SET a=t2.c FROM t2, t3
} {
UPDATE t1 SET a=t2.abc FROM t2, t3
}
7 {
ALTER TABLE t4 RENAME e TO abc
} {
UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.e=a
} {
UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.abc=a
}
8 {
ALTER TABLE t4 RENAME TO abc
} {
UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.e=a
} {
UPDATE t1 SET a=1 FROM t3 NATURAL JOIN "abc" WHERE "abc".e=a
}
} {
reset_db
do_execsql_test 2.$tn.1 {
CREATE TABLE t1(a,b);
CREATE TABLE t2(c,d);
CREATE TABLE t3(e,f);
CREATE TABLE t4(e,f);
}
do_execsql_test 2.$tn.2 "
CREATE TRIGGER r1 INSERT ON t1 BEGIN
$update;
END
"
do_execsql_test 2.$tn.3 $alter
do_whitespace_sql_test 2.$tn.4 {
SELECT sqL FROM sqlite_schema WHERE type='trigger'
} "{
CREATE TRIGGER r1 INSERT ON t1 BEGIN
$final;
END
}"
}
finish_test
|
