gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/conflict3.test (about) 1 # 2013-11-05 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 # This file implements regression tests for SQLite library. 12 # 13 # This file implements tests for the conflict resolution extension 14 # to SQLite. 15 # 16 # This file focuses on making sure that combinations of REPLACE, 17 # IGNORE, and FAIL conflict resolution play well together. 18 # 19 20 set testdir [file dirname $argv0] 21 source $testdir/tester.tcl 22 set testprefix conflict3 23 24 ifcapable !conflict { 25 finish_test 26 return 27 } 28 29 do_execsql_test 1.1 { 30 CREATE TABLE t1( 31 a INTEGER PRIMARY KEY ON CONFLICT REPLACE, 32 b UNIQUE ON CONFLICT IGNORE, 33 c UNIQUE ON CONFLICT FAIL 34 ); 35 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 36 SELECT a,b,c FROM t1 ORDER BY a; 37 } {1 2 3 2 3 4} 38 39 # Insert a row that conflicts on column B. The insert should be ignored. 40 # 41 do_execsql_test 1.2 { 42 INSERT INTO t1(a,b,c) VALUES(3,2,5); 43 SELECT a,b,c FROM t1 ORDER BY a; 44 } {1 2 3 2 3 4} 45 46 # Insert two rows where the second conflicts on C. The first row show go 47 # and and then there should be a constraint error. 48 # 49 do_test 1.3 { 50 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 51 } {1 {UNIQUE constraint failed: t1.c}} 52 do_execsql_test 1.4 { 53 SELECT a,b,c FROM t1 ORDER BY a; 54 } {1 2 3 2 3 4 4 5 6} 55 56 # Replete the tests above, but this time on a table non-INTEGER primary key. 57 # 58 do_execsql_test 2.1 { 59 DROP TABLE t1; 60 CREATE TABLE t1( 61 a INT PRIMARY KEY ON CONFLICT REPLACE, 62 b UNIQUE ON CONFLICT IGNORE, 63 c UNIQUE ON CONFLICT FAIL 64 ); 65 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 66 SELECT a,b,c FROM t1 ORDER BY a; 67 } {1 2 3 2 3 4} 68 69 # Insert a row that conflicts on column B. The insert should be ignored. 70 # 71 do_execsql_test 2.2 { 72 INSERT INTO t1(a,b,c) VALUES(3,2,5); 73 SELECT a,b,c FROM t1 ORDER BY a; 74 } {1 2 3 2 3 4} 75 76 # Insert two rows where the second conflicts on C. The first row show go 77 # and and then there should be a constraint error. 78 # 79 do_test 2.3 { 80 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 81 } {1 {UNIQUE constraint failed: t1.c}} 82 do_execsql_test 2.4 { 83 SELECT a,b,c FROM t1 ORDER BY a; 84 } {1 2 3 2 3 4 4 5 6} 85 86 # Replete again on a WITHOUT ROWID table. 87 # 88 do_execsql_test 3.1 { 89 DROP TABLE t1; 90 CREATE TABLE t1( 91 a INT PRIMARY KEY ON CONFLICT REPLACE, 92 b UNIQUE ON CONFLICT IGNORE, 93 c UNIQUE ON CONFLICT FAIL 94 ) WITHOUT ROWID; 95 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 96 SELECT a,b,c FROM t1 ORDER BY a; 97 } {1 2 3 2 3 4} 98 99 # Insert a row that conflicts on column B. The insert should be ignored. 100 # 101 do_execsql_test 3.2 { 102 INSERT INTO t1(a,b,c) VALUES(3,2,5); 103 SELECT a,b,c FROM t1 ORDER BY a; 104 } {1 2 3 2 3 4} 105 106 # Insert two rows where the second conflicts on C. The first row show go 107 # and and then there should be a constraint error. 108 # 109 do_test 3.3 { 110 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 111 } {1 {UNIQUE constraint failed: t1.c}} 112 do_execsql_test 3.4 { 113 SELECT a,b,c FROM t1 ORDER BY a; 114 } {1 2 3 2 3 4 4 5 6} 115 116 # Arrange the table rows in a different order and repeat. 117 # 118 do_execsql_test 4.1 { 119 DROP TABLE t1; 120 CREATE TABLE t1( 121 b UNIQUE ON CONFLICT IGNORE, 122 c UNIQUE ON CONFLICT FAIL, 123 a INT PRIMARY KEY ON CONFLICT REPLACE 124 ) WITHOUT ROWID; 125 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 126 SELECT a,b,c FROM t1 ORDER BY a; 127 } {1 2 3 2 3 4} 128 129 # Insert a row that conflicts on column B. The insert should be ignored. 130 # 131 do_execsql_test 4.2 { 132 INSERT INTO t1(a,b,c) VALUES(3,2,5); 133 SELECT a,b,c FROM t1 ORDER BY a; 134 } {1 2 3 2 3 4} 135 136 # Insert two rows where the second conflicts on C. The first row show go 137 # and and then there should be a constraint error. 138 # 139 do_test 4.3 { 140 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 141 } {1 {UNIQUE constraint failed: t1.c}} 142 do_execsql_test 4.4 { 143 SELECT a,b,c FROM t1 ORDER BY a; 144 } {1 2 3 2 3 4 4 5 6} 145 146 # Arrange the table rows in a different order and repeat. 147 # 148 do_execsql_test 5.1 { 149 DROP TABLE t1; 150 CREATE TABLE t1( 151 b UNIQUE ON CONFLICT IGNORE, 152 a INT PRIMARY KEY ON CONFLICT REPLACE, 153 c UNIQUE ON CONFLICT FAIL 154 ); 155 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 156 SELECT a,b,c FROM t1 ORDER BY a; 157 } {1 2 3 2 3 4} 158 159 # Insert a row that conflicts on column B. The insert should be ignored. 160 # 161 do_execsql_test 5.2 { 162 INSERT INTO t1(a,b,c) VALUES(3,2,5); 163 SELECT a,b,c FROM t1 ORDER BY a; 164 } {1 2 3 2 3 4} 165 166 # Insert two rows where the second conflicts on C. The first row show go 167 # and and then there should be a constraint error. 168 # 169 do_test 5.3 { 170 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 171 } {1 {UNIQUE constraint failed: t1.c}} 172 do_execsql_test 5.4 { 173 SELECT a,b,c FROM t1 ORDER BY a; 174 } {1 2 3 2 3 4 4 5 6} 175 176 # Arrange the table rows in a different order and repeat. 177 # 178 do_execsql_test 6.1 { 179 DROP TABLE t1; 180 CREATE TABLE t1( 181 c UNIQUE ON CONFLICT FAIL, 182 a INT PRIMARY KEY ON CONFLICT REPLACE, 183 b UNIQUE ON CONFLICT IGNORE 184 ); 185 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 186 SELECT a,b,c FROM t1 ORDER BY a; 187 } {1 2 3 2 3 4} 188 189 # Insert a row that conflicts on column B. The insert should be ignored. 190 # 191 do_execsql_test 6.2 { 192 INSERT INTO t1(a,b,c) VALUES(3,2,5); 193 SELECT a,b,c FROM t1 ORDER BY a; 194 } {1 2 3 2 3 4} 195 196 # Insert two rows where the second conflicts on C. The first row show go 197 # and and then there should be a constraint error. 198 # 199 do_test 6.3 { 200 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 201 } {1 {UNIQUE constraint failed: t1.c}} 202 do_execsql_test 6.4 { 203 SELECT a,b,c FROM t1 ORDER BY a; 204 } {1 2 3 2 3 4 4 5 6} 205 206 # Change which column is the PRIMARY KEY 207 # 208 do_execsql_test 7.1 { 209 DROP TABLE t1; 210 CREATE TABLE t1( 211 a UNIQUE ON CONFLICT REPLACE, 212 b INTEGER PRIMARY KEY ON CONFLICT IGNORE, 213 c UNIQUE ON CONFLICT FAIL 214 ); 215 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 216 SELECT a,b,c FROM t1 ORDER BY a; 217 } {1 2 3 2 3 4} 218 219 # Insert a row that conflicts on column B. The insert should be ignored. 220 # 221 do_execsql_test 7.2 { 222 INSERT INTO t1(a,b,c) VALUES(3,2,5); 223 SELECT a,b,c FROM t1 ORDER BY a; 224 } {1 2 3 2 3 4} 225 226 # Insert two rows where the second conflicts on C. The first row show go 227 # and and then there should be a constraint error. 228 # 229 do_test 7.3 { 230 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 231 } {1 {UNIQUE constraint failed: t1.c}} 232 do_execsql_test 7.4 { 233 SELECT a,b,c FROM t1 ORDER BY a; 234 } {1 2 3 2 3 4 4 5 6} 235 236 # Change which column is the PRIMARY KEY 237 # 238 do_execsql_test 8.1 { 239 DROP TABLE t1; 240 CREATE TABLE t1( 241 a UNIQUE ON CONFLICT REPLACE, 242 b INT PRIMARY KEY ON CONFLICT IGNORE, 243 c UNIQUE ON CONFLICT FAIL 244 ); 245 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 246 SELECT a,b,c FROM t1 ORDER BY a; 247 } {1 2 3 2 3 4} 248 249 # Insert a row that conflicts on column B. The insert should be ignored. 250 # 251 do_execsql_test 8.2 { 252 INSERT INTO t1(a,b,c) VALUES(3,2,5); 253 SELECT a,b,c FROM t1 ORDER BY a; 254 } {1 2 3 2 3 4} 255 256 # Insert two rows where the second conflicts on C. The first row show go 257 # and and then there should be a constraint error. 258 # 259 do_test 8.3 { 260 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 261 } {1 {UNIQUE constraint failed: t1.c}} 262 do_execsql_test 8.4 { 263 SELECT a,b,c FROM t1 ORDER BY a; 264 } {1 2 3 2 3 4 4 5 6} 265 266 # Change which column is the PRIMARY KEY 267 # 268 do_execsql_test 9.1 { 269 DROP TABLE t1; 270 CREATE TABLE t1( 271 a UNIQUE ON CONFLICT REPLACE, 272 b INT PRIMARY KEY ON CONFLICT IGNORE, 273 c UNIQUE ON CONFLICT FAIL 274 ) WITHOUT ROWID; 275 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 276 SELECT a,b,c FROM t1 ORDER BY a; 277 } {1 2 3 2 3 4} 278 279 # Insert a row that conflicts on column B. The insert should be ignored. 280 # 281 do_execsql_test 9.2 { 282 INSERT INTO t1(a,b,c) VALUES(3,2,5); 283 SELECT a,b,c FROM t1 ORDER BY a; 284 } {1 2 3 2 3 4} 285 286 # Insert two rows where the second conflicts on C. The first row show go 287 # and and then there should be a constraint error. 288 # 289 do_test 9.3 { 290 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 291 } {1 {UNIQUE constraint failed: t1.c}} 292 do_execsql_test 9.4 { 293 SELECT a,b,c FROM t1 ORDER BY a; 294 } {1 2 3 2 3 4 4 5 6} 295 296 # Change which column is the PRIMARY KEY 297 # 298 do_execsql_test 10.1 { 299 DROP TABLE t1; 300 CREATE TABLE t1( 301 a UNIQUE ON CONFLICT REPLACE, 302 b UNIQUE ON CONFLICT IGNORE, 303 c INTEGER PRIMARY KEY ON CONFLICT FAIL 304 ); 305 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 306 SELECT a,b,c FROM t1 ORDER BY a; 307 } {1 2 3 2 3 4} 308 309 # Insert a row that conflicts on column B. The insert should be ignored. 310 # 311 do_execsql_test 10.2 { 312 INSERT INTO t1(a,b,c) VALUES(3,2,5); 313 SELECT a,b,c FROM t1 ORDER BY a; 314 } {1 2 3 2 3 4} 315 316 # Insert two rows where the second conflicts on C. The first row show go 317 # and and then there should be a constraint error. 318 # 319 do_test 10.3 { 320 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 321 } {1 {UNIQUE constraint failed: t1.c}} 322 do_execsql_test 10.4 { 323 SELECT a,b,c FROM t1 ORDER BY a; 324 } {1 2 3 2 3 4 4 5 6} 325 326 # Change which column is the PRIMARY KEY 327 # 328 do_execsql_test 11.1 { 329 DROP TABLE t1; 330 CREATE TABLE t1( 331 a UNIQUE ON CONFLICT REPLACE, 332 b UNIQUE ON CONFLICT IGNORE, 333 c PRIMARY KEY ON CONFLICT FAIL 334 ) WITHOUT ROWID; 335 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); 336 SELECT a,b,c FROM t1 ORDER BY a; 337 } {1 2 3 2 3 4} 338 339 # Insert a row that conflicts on column B. The insert should be ignored. 340 # 341 do_execsql_test 11.2 { 342 INSERT INTO t1(a,b,c) VALUES(3,2,5); 343 SELECT a,b,c FROM t1 ORDER BY a; 344 } {1 2 3 2 3 4} 345 346 # Insert two rows where the second conflicts on C. The first row show go 347 # and and then there should be a constraint error. 348 # 349 do_test 11.3 { 350 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} 351 } {1 {UNIQUE constraint failed: t1.c}} 352 do_execsql_test 11.4 { 353 SELECT a,b,c FROM t1 ORDER BY a; 354 } {1 2 3 2 3 4 4 5 6} 355 356 # Check that ticket [f68dc596c4] has been fixed. 357 # 358 do_execsql_test 12.1 { 359 CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT); 360 INSERT INTO t2 VALUES(111, '111'); 361 } 362 do_execsql_test 12.2 { 363 REPLACE INTO t2 VALUES(NULL, '112'), (111, '111B'); 364 } 365 do_execsql_test 12.3 { 366 SELECT * FROM t2; 367 } {111 111B 112 112} 368 369 #------------------------------------------------------------------------- 370 ifcapable trigger { 371 reset_db 372 do_execsql_test 13.1.0 { 373 PRAGMA recursive_triggers = true; 374 CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE); 375 CREATE TRIGGER tr0 AFTER DELETE ON t0 BEGIN 376 DELETE FROM t0; 377 END; 378 379 INSERT INTO t0 VALUES(1, NULL); 380 INSERT INTO t0 VALUES(0, NULL); 381 } 382 383 do_catchsql_test 13.1.1 { 384 UPDATE OR REPLACE t0 SET c1 = 1; 385 } {1 {constraint failed}} 386 387 integrity_check 13.1.2 388 389 do_execsql_test 13.1.3 { 390 SELECT * FROM t0 391 } {1 {} 0 {}} 392 393 do_execsql_test 13.2.0 { 394 CREATE TABLE t2 (a PRIMARY KEY, b UNIQUE, c UNIQUE) WITHOUT ROWID; 395 CREATE TRIGGER tr3 AFTER DELETE ON t2 BEGIN 396 DELETE FROM t2; 397 END; 398 399 INSERT INTO t2 VALUES(1, 1, 1); 400 INSERT INTO t2 VALUES(2, 2, 2); 401 } 402 403 do_catchsql_test 13.2.1 { 404 UPDATE OR REPLACE t2 SET c = 0; 405 } {1 {constraint failed}} 406 407 integrity_check 13.2.2 408 409 do_execsql_test 13.2.3 { 410 SELECT * FROM t2 411 } {1 1 1 2 2 2} 412 413 do_execsql_test 13.3.0 { 414 CREATE TABLE t1(a, b); 415 CREATE TABLE log(x); 416 CREATE INDEX i1 ON t1(a); 417 INSERT INTO t1 VALUES(1, 2); 418 419 CREATE TRIGGER tb BEFORE UPDATE ON t1 BEGIN 420 DELETE FROM t1; 421 END; 422 CREATE TRIGGER ta AFTER UPDATE ON t1 BEGIN 423 INSERT INTO log VALUES('fired!'); 424 END; 425 426 UPDATE t1 SET b=3; 427 } 428 429 do_execsql_test 13.3.1 { 430 SELECT * FROM t1; 431 } {} 432 do_execsql_test 13.3.2 { 433 SELECT * FROM log; 434 } {} 435 } 436 437 finish_test