AryaWu/sqlite
0
1# 2013-11-052#3# The author disclaims copyright to this source code. In place of4# 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 extension14# 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 20set testdir [file dirname $argv0]21source $testdir/tester.tcl22set testprefix conflict323 24ifcapable !conflict {25 finish_test26 return27}28 29do_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 FAIL34 );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#41do_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 go47# and and then there should be a constraint error.48#49do_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}}52do_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#58do_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 FAIL64 );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#71do_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 go77# and and then there should be a constraint error.78#79do_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}}82do_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#88do_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 FAIL94 ) 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#101do_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 go107# and and then there should be a constraint error.108#109do_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}}112do_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#118do_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 REPLACE124 ) 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#131do_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 go137# and and then there should be a constraint error.138#139do_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}}142do_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#148do_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 FAIL154 );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#161do_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 go167# and and then there should be a constraint error.168#169do_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}}172do_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#178do_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 IGNORE184 );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#191do_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 go197# and and then there should be a constraint error.198#199do_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}}202do_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 KEY207#208do_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 FAIL214 );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#221do_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 go227# and and then there should be a constraint error.228#229do_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}}232do_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 KEY237#238do_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 FAIL244 );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#251do_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 go257# and and then there should be a constraint error.258#259do_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}}262do_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 KEY267#268do_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 FAIL274 ) 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#281do_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 go287# and and then there should be a constraint error.288#289do_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}}292do_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 KEY297#298do_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 FAIL304 );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#311do_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 go317# and and then there should be a constraint error.318#319do_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}}322do_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 KEY327#328do_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 FAIL334 ) 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#341do_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 go347# and and then there should be a constraint error.348#349do_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}}352do_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#358do_execsql_test 12.1 {359 CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);360 INSERT INTO t2 VALUES(111, '111');361}362do_execsql_test 12.2 {363 REPLACE INTO t2 VALUES(NULL, '112'), (111, '111B');364}365do_execsql_test 12.3 {366 SELECT * FROM t2;367} {111 111B 112 112}368 369#-------------------------------------------------------------------------370ifcapable trigger {371 reset_db372 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.2388 389 do_execsql_test 13.1.3 {390 SELECT * FROM t0391 } {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.2408 409 do_execsql_test 13.2.3 {410 SELECT * FROM t2411 } {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 BEGIN420 DELETE FROM t1;421 END;422 CREATE TRIGGER ta AFTER UPDATE ON t1 BEGIN423 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 437finish_test438