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 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15set 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 31foreach {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 134do_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 142do_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 155do_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 165do_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#------------------------------------------------------------------------- 180foreach {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#------------------------------------------------------------------------- 215reset_db 216foreach {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 261finish_test 262 263