Debian Codegraph @

test/upfrom3.test

raw
1 # 2020 July 14
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
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set testprefix upfrom3
16
17 # Test plan:
18 #
19 # 1.*: Test UPDATE ... FROM statements that modify IPK fields. And that
20 # modify "INTEGER PRIMARY KEY" fields on WITHOUT ROWID tables.
21 #
22 # 2.*: Test UPDATE ... FROM statements that modify PK fields of WITHOUT
23 # ROWID tables.
24 #
25 # 3.*: Test that UPDATE ... FROM statements are not confused if there
26 # are multiple tables of the same name in attached databases.
27 #
28 # 4.*: Tests for UPDATE ... FROM statements and foreign keys.
29 #
30
31 foreach {tn wo} {
32 1 ""
33 2 "WITHOUT ROWID"
34 } {
35 reset_db
36 eval [string map [list %WO% $wo %TN% $tn] {
37
38 do_execsql_test 1.%TN%.0 {
39 CREATE TABLE log(t TEXT);
40 CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z UNIQUE) %WO%;
41 CREATE INDEX t1y ON t1(y);
42
43 INSERT INTO t1 VALUES(1, 'i', 'one');
44 INSERT INTO t1 VALUES(2, 'ii', 'two');
45 INSERT INTO t1 VALUES(3, 'iii', 'three');
46 INSERT INTO t1 VALUES(4, 'iv', 'four');
47 }
48
49 do_execsql_test 1.%TN%.1 {
50 CREATE TABLE x1(o, n);
51 INSERT INTO x1 VALUES(1, 11);
52 INSERT INTO x1 VALUES(2, 12);
53 INSERT INTO x1 VALUES(3, 13);
54 INSERT INTO x1 VALUES(4, 14);
55 UPDATE t1 SET x=n FROM x1 WHERE x=o;
56 SELECT x, y, z FROM t1 ORDER BY 1;
57 } {
58 11 i one
59 12 ii two
60 13 iii three
61 14 iv four
62 }
63
64 do_test 1.%TN%.2 { db changes } 4
65
66 do_execsql_test 1.%TN%.3 {
67 INSERT INTO x1 VALUES(11, 21);
68 INSERT INTO x1 VALUES(12, 22);
69 INSERT INTO x1 VALUES(13, 23);
70 INSERT INTO x1 VALUES(14, 24);
71
72 INSERT INTO x1 VALUES(21, 31);
73 INSERT INTO x1 VALUES(22, 32);
74 INSERT INTO x1 VALUES(23, 33);
75 INSERT INTO x1 VALUES(24, 34);
76 UPDATE t1 SET x=n FROM x1 WHERE x=o;
77 SELECT x, y, z FROM t1 ORDER BY 1;
78 } {
79 21 i one
80 22 ii two
81 23 iii three
82 24 iv four
83 }
84
85 do_execsql_test 1.%TN%.4 {
86 UPDATE t1 SET x=n FROM x1 WHERE x=o;
87 SELECT x, y, z FROM t1 ORDER BY 1;
88 } {
89 31 i one
90 32 ii two
91 33 iii three
92 34 iv four
93 }
94
95 do_execsql_test 1.%TN%.5 {
96 INSERT INTO x1 VALUES(31, 32);
97 INSERT INTO x1 VALUES(33, 34);
98 UPDATE OR REPLACE t1 SET x=n FROM x1 WHERE x=o;
99 SELECT x, y, z FROM t1 ORDER BY 1;
100 } {
101 32 i one
102 34 iii three
103 }
104
105 do_execsql_test 1.%TN%.6 {
106 INSERT INTO t1 VALUES(33, 'ii', 'two');
107 INSERT INTO t1 VALUES(35, 'iv', 'four');
108 }
109
110 do_execsql_test 1.%TN%.7 {
111 CREATE TABLE x2(o, n, zz);
112 INSERT INTO x2 VALUES(32, 41, 'four');
113 INSERT INTO x2 VALUES(33, 42, 'three');
114 UPDATE OR IGNORE t1 SET x=n, z=zz FROM x2 WHERE x=o;
115 SELECT x, y, z FROM t1 ORDER BY 1;
116 } {
117 32 i one
118 33 ii two
119 34 iii three
120 35 iv four
121 }
122
123 do_execsql_test 1.%TN%.8 {
124 UPDATE OR REPLACE t1 SET x=n, z=zz FROM x2 WHERE x=o;
125 SELECT x, y, z FROM t1 ORDER BY 1;
126 } {
127 41 i four
128 42 ii three
129 }
130
131 }]
132 }
133
134 do_execsql_test 2.1.1 {
135 CREATE TABLE u1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;
136 INSERT INTO u1 VALUES(0, 0, 0);
137 INSERT INTO u1 VALUES(1, 0, 1);
138 INSERT INTO u1 VALUES(2, 1, 0);
139 INSERT INTO u1 VALUES(3, 1, 1);
140 }
141
142 do_execsql_test 2.1.2 {
143 CREATE TABLE map(f, t);
144 INSERT INTO map VALUES(0, 10);
145 INSERT INTO map VALUES(1, 11);
146 UPDATE u1 SET c=t FROM map WHERE c=f;
147 SELECT * FROM u1 ORDER BY a;
148 } {
149 0 0 10
150 1 0 11
151 2 1 10
152 3 1 11
153 }
154
155 do_execsql_test 2.1.3 {
156 UPDATE u1 SET b=t FROM map WHERE b=f;
157 SELECT * FROM u1 ORDER BY a;
158 } {
159 0 10 10
160 1 10 11
161 2 11 10
162 3 11 11
163 }
164
165 do_execsql_test 2.1.4 {
166 CREATE TABLE map2(o1, o2, n1, n2);
167 INSERT INTO map2 VALUES
168 (10, 10, 50, 50), (10, 11, 50, 60),
169 (11, 10, 60, 50), (11, 11, 60, 60);
170 UPDATE u1 SET b=n1, c=n2 FROM map2 WHERE b=o1 AND c=o2;
171 SELECT * FROM u1 ORDER BY a;
172 } {
173 0 50 50
174 1 50 60
175 2 60 50
176 3 60 60
177 }
178
179 #-------------------------------------------------------------------------
180 foreach {tn wo} {
181 1 ""
182 2 "WITHOUT ROWID"
183 } {
184 reset_db
185 forcedelete test.db2
186 eval [string map [list %WO% $wo %TN% $tn] {
187 do_execsql_test 3.$tn.1 {
188 CREATE TABLE g1(a, b, c, PRIMARY KEY(a, b)) %WO%;
189 INSERT INTO g1 VALUES(1, 1, 1);
190
191 ATTACH 'test.db2' AS aux;
192 CREATE TABLE aux.g1(a, b, c, PRIMARY KEY(a, b)) %WO%;
193 INSERT INTO aux.g1 VALUES(10, 1, 10);
194 INSERT INTO aux.g1 VALUES(20, 2, 20);
195 INSERT INTO aux.g1 VALUES(30, 3, 30);
196 }
197
198 do_execsql_test 3.$tn.2 {
199 UPDATE aux.g1 SET c=101 FROM main.g1;
200 }
201 do_execsql_test 3.$tn.3 {
202 SELECT * FROM aux.g1;
203 } {10 1 101 20 2 101 30 3 101}
204
205 do_execsql_test 3.$tn.4 {
206 UPDATE g1 SET c=101 FROM g1 AS g2;
207 }
208 do_execsql_test 3.$tn.5 {
209 SELECT * FROM g1;
210 } {1 1 101}
211 }]
212 }
213
214 #-------------------------------------------------------------------------
215 reset_db
216 foreach {tn wo} {
217 1 ""
218 2 "WITHOUT ROWID"
219 } {
220 reset_db
221 forcedelete test.db2
222 eval [string map [list %WO% $wo %TN% $tn] {
223
224 do_execsql_test 4.$tn.1 {
225 CREATE TABLE p1(a INTEGER PRIMARY KEY, b) %WO%;
226 CREATE TABLE c1(x PRIMARY KEY, y REFERENCES p1 ON UPDATE CASCADE) %WO%;
227 PRAGMA foreign_keys = 1;
228
229 INSERT INTO p1 VALUES(1, 'one');
230 INSERT INTO p1 VALUES(11, 'eleven');
231 INSERT INTO p1 VALUES(111, 'eleventyone');
232
233 INSERT INTO c1 VALUES('a', 1);
234 INSERT INTO c1 VALUES('b', 11);
235 INSERT INTO c1 VALUES('c', 111);
236 }
237
238 do_execsql_test 4.$tn.2 {
239 CREATE TABLE map(f, t);
240 INSERT INTO map VALUES('a', 111);
241 INSERT INTO map VALUES('c', 112);
242 }
243
244 do_catchsql_test 4.$tn.3 {
245 UPDATE c1 SET y=t FROM map WHERE x=f;
246 } {1 {FOREIGN KEY constraint failed}}
247
248 do_execsql_test 4.$tn.4 {
249 INSERT INTO map VALUES('eleven', 12);
250 INSERT INTO map VALUES('eleventyone', 112);
251 UPDATE p1 SET a=t FROM map WHERE b=f;
252 }
253
254 do_execsql_test 4.$tn.5 {
255 SELECT * FROM c1
256 } {a 1 b 12 c 112}
257
258 }]
259 }
260
261 finish_test