xref: /sqlite-3.40.0/test/altertrig.test (revision ca29bbce) 
1# 2022 May 27
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
6#    May you do good and not evil.
7#    May you find forgiveness for yourself and forgive others.
8#    May you share freely, never taking more than you give.
9#
10#*************************************************************************
11#
12
13set testdir [file dirname $argv0]
14source $testdir/tester.tcl
15set testprefix altertrig
16
17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
18ifcapable !altertable {
19  finish_test
20  return
21}
22
23proc collapse_whitespace {in} {
24  regsub -all {[ \t\n]+} [string trim $in] { }
25}
26
27proc do_whitespace_sql_test {tn sql res} {
28  set got [execsql $sql]
29  set wgot [list]
30  set wres [list]
31  foreach g $got { lappend wgot [collapse_whitespace $g] }
32  foreach r $res { lappend wres [collapse_whitespace $r] }
33
34  uplevel [list do_test $tn [list set {} $wgot] $wres]
35}
36
37do_execsql_test 1.0 {
38  CREATE TABLE t1(x);
39  CREATE TABLE t2(y);
40  CREATE TABLE t3(z);
41  CREATE TABLE t4(a);
42
43  CREATE TRIGGER r1 INSERT ON t1 BEGIN
44    UPDATE t1 SET d='xyz' FROM t2, t3;
45  END;
46}
47
48do_whitespace_sql_test 1.1 {
49  ALTER TABLE t3 RENAME TO t5;
50  SELECT sql FROM sqlite_schema WHERE type='trigger';
51} {{
52  CREATE TRIGGER r1 INSERT ON t1 BEGIN
53    UPDATE t1 SET d='xyz' FROM t2, "t5";
54  END
55}}
56
57do_execsql_test 1.2 {
58  DROP TRIGGER r1;
59  CREATE TRIGGER r1 INSERT ON t1 BEGIN
60    UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM t5);
61  END;
62}
63
64do_whitespace_sql_test 1.3 {
65  ALTER TABLE t5 RENAME TO t3;
66  SELECT sql FROM sqlite_schema WHERE type='trigger';
67} {{
68  CREATE TRIGGER r1 INSERT ON t1 BEGIN
69    UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM "t3");
70  END
71}}
72
73foreach {tn alter update final} {
74  1 {
75    ALTER TABLE t3 RENAME TO t10
76  } {
77    UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM t3)
78  } {
79    UPDATE t1 SET d='xyz' FROM t2, (SELECT * FROM "t10")
80  }
81
82  2 {
83    ALTER TABLE t3 RENAME TO t10
84  } {
85    UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT e FROM t3))
86  } {
87    UPDATE t1 SET a='xyz' FROM "t10", (SELECT * FROM (SELECT e FROM "t10"))
88  }
89
90  3 {
91    ALTER TABLE t3 RENAME e TO abc
92  } {
93    UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT e FROM t3))
94  } {
95    UPDATE t1 SET a='xyz' FROM t3, (SELECT * FROM (SELECT abc FROM t3))
96  }
97
98  4 {
99    ALTER TABLE t2 RENAME c TO abc
100  } {
101    UPDATE t1 SET a='xyz' FROM t3, (SELECT 1 FROM t2 WHERE c)
102  } {
103    UPDATE t1 SET a='xyz' FROM t3, (SELECT 1 FROM t2 WHERE abc)
104  }
105
106  5 {
107    ALTER TABLE t2 RENAME c TO abc
108  } {
109    UPDATE t1 SET a=t2.c FROM t2
110  } {
111    UPDATE t1 SET a=t2.abc FROM t2
112  }
113
114  6 {
115    ALTER TABLE t2 RENAME c TO abc
116  } {
117    UPDATE t1 SET a=t2.c FROM t2, t3
118  } {
119    UPDATE t1 SET a=t2.abc FROM t2, t3
120  }
121
122  7 {
123    ALTER TABLE t4 RENAME e TO abc
124  } {
125    UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.e=a
126  } {
127    UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.abc=a
128  }
129
130  8 {
131    ALTER TABLE t4 RENAME TO abc
132  } {
133    UPDATE t1 SET a=1 FROM t3 NATURAL JOIN t4 WHERE t4.e=a
134  } {
135    UPDATE t1 SET a=1 FROM t3 NATURAL JOIN "abc" WHERE "abc".e=a
136  }
137
138} {
139  reset_db
140  do_execsql_test 2.$tn.1 {
141    CREATE TABLE t1(a,b);
142    CREATE TABLE t2(c,d);
143    CREATE TABLE t3(e,f);
144    CREATE TABLE t4(e,f);
145  }
146  do_execsql_test 2.$tn.2 "
147    CREATE TRIGGER r1 INSERT ON t1 BEGIN
148      $update;
149    END
150  "
151  do_execsql_test 2.$tn.3 $alter
152
153  do_whitespace_sql_test 2.$tn.4 {
154    SELECT sqL FROM sqlite_schema WHERE type='trigger'
155  } "{
156    CREATE TRIGGER r1 INSERT ON t1 BEGIN
157      $final;
158    END
159  }"
160}
161
162finish_test
163
164