| OLD | NEW |
|---|
| (Empty) |
| 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 | |
| 23 ifcapable !conflict { | |
| 24 finish_test | |
| 25 return | |
| 26 } | |
| 27 | |
| 28 do_execsql_test conflict-1.1 { | |
| 29 CREATE TABLE t1( | |
| 30 a INTEGER PRIMARY KEY ON CONFLICT REPLACE, | |
| 31 b UNIQUE ON CONFLICT IGNORE, | |
| 32 c UNIQUE ON CONFLICT FAIL | |
| 33 ); | |
| 34 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); | |
| 35 SELECT a,b,c FROM t1 ORDER BY a; | |
| 36 } {1 2 3 2 3 4} | |
| 37 | |
| 38 # Insert a row that conflicts on column B. The insert should be ignored. | |
| 39 # | |
| 40 do_execsql_test conflict-1.2 { | |
| 41 INSERT INTO t1(a,b,c) VALUES(3,2,5); | |
| 42 SELECT a,b,c FROM t1 ORDER BY a; | |
| 43 } {1 2 3 2 3 4} | |
| 44 | |
| 45 # Insert two rows where the second conflicts on C. The first row show go | |
| 46 # and and then there should be a constraint error. | |
| 47 # | |
| 48 do_test conflict-1.3 { | |
| 49 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} | |
| 50 } {1 {UNIQUE constraint failed: t1.c}} | |
| 51 do_execsql_test conflict-1.4 { | |
| 52 SELECT a,b,c FROM t1 ORDER BY a; | |
| 53 } {1 2 3 2 3 4 4 5 6} | |
| 54 | |
| 55 # Replete the tests above, but this time on a table non-INTEGER primary key. | |
| 56 # | |
| 57 do_execsql_test conflict-2.1 { | |
| 58 DROP TABLE t1; | |
| 59 CREATE TABLE t1( | |
| 60 a INT PRIMARY KEY ON CONFLICT REPLACE, | |
| 61 b UNIQUE ON CONFLICT IGNORE, | |
| 62 c UNIQUE ON CONFLICT FAIL | |
| 63 ); | |
| 64 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); | |
| 65 SELECT a,b,c FROM t1 ORDER BY a; | |
| 66 } {1 2 3 2 3 4} | |
| 67 | |
| 68 # Insert a row that conflicts on column B. The insert should be ignored. | |
| 69 # | |
| 70 do_execsql_test conflict-2.2 { | |
| 71 INSERT INTO t1(a,b,c) VALUES(3,2,5); | |
| 72 SELECT a,b,c FROM t1 ORDER BY a; | |
| 73 } {1 2 3 2 3 4} | |
| 74 | |
| 75 # Insert two rows where the second conflicts on C. The first row show go | |
| 76 # and and then there should be a constraint error. | |
| 77 # | |
| 78 do_test conflict-2.3 { | |
| 79 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} | |
| 80 } {1 {UNIQUE constraint failed: t1.c}} | |
| 81 do_execsql_test conflict-2.4 { | |
| 82 SELECT a,b,c FROM t1 ORDER BY a; | |
| 83 } {1 2 3 2 3 4 4 5 6} | |
| 84 | |
| 85 # Replete again on a WITHOUT ROWID table. | |
| 86 # | |
| 87 do_execsql_test conflict-3.1 { | |
| 88 DROP TABLE t1; | |
| 89 CREATE TABLE t1( | |
| 90 a INT PRIMARY KEY ON CONFLICT REPLACE, | |
| 91 b UNIQUE ON CONFLICT IGNORE, | |
| 92 c UNIQUE ON CONFLICT FAIL | |
| 93 ) WITHOUT ROWID; | |
| 94 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); | |
| 95 SELECT a,b,c FROM t1 ORDER BY a; | |
| 96 } {1 2 3 2 3 4} | |
| 97 | |
| 98 # Insert a row that conflicts on column B. The insert should be ignored. | |
| 99 # | |
| 100 do_execsql_test conflict-3.2 { | |
| 101 INSERT INTO t1(a,b,c) VALUES(3,2,5); | |
| 102 SELECT a,b,c FROM t1 ORDER BY a; | |
| 103 } {1 2 3 2 3 4} | |
| 104 | |
| 105 # Insert two rows where the second conflicts on C. The first row show go | |
| 106 # and and then there should be a constraint error. | |
| 107 # | |
| 108 do_test conflict-3.3 { | |
| 109 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} | |
| 110 } {1 {UNIQUE constraint failed: t1.c}} | |
| 111 do_execsql_test conflict-3.4 { | |
| 112 SELECT a,b,c FROM t1 ORDER BY a; | |
| 113 } {1 2 3 2 3 4 4 5 6} | |
| 114 | |
| 115 # Arrange the table rows in a different order and repeat. | |
| 116 # | |
| 117 do_execsql_test conflict-4.1 { | |
| 118 DROP TABLE t1; | |
| 119 CREATE TABLE t1( | |
| 120 b UNIQUE ON CONFLICT IGNORE, | |
| 121 c UNIQUE ON CONFLICT FAIL, | |
| 122 a INT PRIMARY KEY ON CONFLICT REPLACE | |
| 123 ) WITHOUT ROWID; | |
| 124 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); | |
| 125 SELECT a,b,c FROM t1 ORDER BY a; | |
| 126 } {1 2 3 2 3 4} | |
| 127 | |
| 128 # Insert a row that conflicts on column B. The insert should be ignored. | |
| 129 # | |
| 130 do_execsql_test conflict-4.2 { | |
| 131 INSERT INTO t1(a,b,c) VALUES(3,2,5); | |
| 132 SELECT a,b,c FROM t1 ORDER BY a; | |
| 133 } {1 2 3 2 3 4} | |
| 134 | |
| 135 # Insert two rows where the second conflicts on C. The first row show go | |
| 136 # and and then there should be a constraint error. | |
| 137 # | |
| 138 do_test conflict-4.3 { | |
| 139 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} | |
| 140 } {1 {UNIQUE constraint failed: t1.c}} | |
| 141 do_execsql_test conflict-4.4 { | |
| 142 SELECT a,b,c FROM t1 ORDER BY a; | |
| 143 } {1 2 3 2 3 4 4 5 6} | |
| 144 | |
| 145 # Arrange the table rows in a different order and repeat. | |
| 146 # | |
| 147 do_execsql_test conflict-5.1 { | |
| 148 DROP TABLE t1; | |
| 149 CREATE TABLE t1( | |
| 150 b UNIQUE ON CONFLICT IGNORE, | |
| 151 a INT PRIMARY KEY ON CONFLICT REPLACE, | |
| 152 c UNIQUE ON CONFLICT FAIL | |
| 153 ); | |
| 154 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); | |
| 155 SELECT a,b,c FROM t1 ORDER BY a; | |
| 156 } {1 2 3 2 3 4} | |
| 157 | |
| 158 # Insert a row that conflicts on column B. The insert should be ignored. | |
| 159 # | |
| 160 do_execsql_test conflict-5.2 { | |
| 161 INSERT INTO t1(a,b,c) VALUES(3,2,5); | |
| 162 SELECT a,b,c FROM t1 ORDER BY a; | |
| 163 } {1 2 3 2 3 4} | |
| 164 | |
| 165 # Insert two rows where the second conflicts on C. The first row show go | |
| 166 # and and then there should be a constraint error. | |
| 167 # | |
| 168 do_test conflict-5.3 { | |
| 169 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} | |
| 170 } {1 {UNIQUE constraint failed: t1.c}} | |
| 171 do_execsql_test conflict-5.4 { | |
| 172 SELECT a,b,c FROM t1 ORDER BY a; | |
| 173 } {1 2 3 2 3 4 4 5 6} | |
| 174 | |
| 175 # Arrange the table rows in a different order and repeat. | |
| 176 # | |
| 177 do_execsql_test conflict-6.1 { | |
| 178 DROP TABLE t1; | |
| 179 CREATE TABLE t1( | |
| 180 c UNIQUE ON CONFLICT FAIL, | |
| 181 a INT PRIMARY KEY ON CONFLICT REPLACE, | |
| 182 b UNIQUE ON CONFLICT IGNORE | |
| 183 ); | |
| 184 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); | |
| 185 SELECT a,b,c FROM t1 ORDER BY a; | |
| 186 } {1 2 3 2 3 4} | |
| 187 | |
| 188 # Insert a row that conflicts on column B. The insert should be ignored. | |
| 189 # | |
| 190 do_execsql_test conflict-6.2 { | |
| 191 INSERT INTO t1(a,b,c) VALUES(3,2,5); | |
| 192 SELECT a,b,c FROM t1 ORDER BY a; | |
| 193 } {1 2 3 2 3 4} | |
| 194 | |
| 195 # Insert two rows where the second conflicts on C. The first row show go | |
| 196 # and and then there should be a constraint error. | |
| 197 # | |
| 198 do_test conflict-6.3 { | |
| 199 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} | |
| 200 } {1 {UNIQUE constraint failed: t1.c}} | |
| 201 do_execsql_test conflict-6.4 { | |
| 202 SELECT a,b,c FROM t1 ORDER BY a; | |
| 203 } {1 2 3 2 3 4 4 5 6} | |
| 204 | |
| 205 # Change which column is the PRIMARY KEY | |
| 206 # | |
| 207 do_execsql_test conflict-7.1 { | |
| 208 DROP TABLE t1; | |
| 209 CREATE TABLE t1( | |
| 210 a UNIQUE ON CONFLICT REPLACE, | |
| 211 b INTEGER PRIMARY KEY ON CONFLICT IGNORE, | |
| 212 c UNIQUE ON CONFLICT FAIL | |
| 213 ); | |
| 214 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); | |
| 215 SELECT a,b,c FROM t1 ORDER BY a; | |
| 216 } {1 2 3 2 3 4} | |
| 217 | |
| 218 # Insert a row that conflicts on column B. The insert should be ignored. | |
| 219 # | |
| 220 do_execsql_test conflict-7.2 { | |
| 221 INSERT INTO t1(a,b,c) VALUES(3,2,5); | |
| 222 SELECT a,b,c FROM t1 ORDER BY a; | |
| 223 } {1 2 3 2 3 4} | |
| 224 | |
| 225 # Insert two rows where the second conflicts on C. The first row show go | |
| 226 # and and then there should be a constraint error. | |
| 227 # | |
| 228 do_test conflict-7.3 { | |
| 229 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} | |
| 230 } {1 {UNIQUE constraint failed: t1.c}} | |
| 231 do_execsql_test conflict-7.4 { | |
| 232 SELECT a,b,c FROM t1 ORDER BY a; | |
| 233 } {1 2 3 2 3 4 4 5 6} | |
| 234 | |
| 235 # Change which column is the PRIMARY KEY | |
| 236 # | |
| 237 do_execsql_test conflict-8.1 { | |
| 238 DROP TABLE t1; | |
| 239 CREATE TABLE t1( | |
| 240 a UNIQUE ON CONFLICT REPLACE, | |
| 241 b INT PRIMARY KEY ON CONFLICT IGNORE, | |
| 242 c UNIQUE ON CONFLICT FAIL | |
| 243 ); | |
| 244 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); | |
| 245 SELECT a,b,c FROM t1 ORDER BY a; | |
| 246 } {1 2 3 2 3 4} | |
| 247 | |
| 248 # Insert a row that conflicts on column B. The insert should be ignored. | |
| 249 # | |
| 250 do_execsql_test conflict-8.2 { | |
| 251 INSERT INTO t1(a,b,c) VALUES(3,2,5); | |
| 252 SELECT a,b,c FROM t1 ORDER BY a; | |
| 253 } {1 2 3 2 3 4} | |
| 254 | |
| 255 # Insert two rows where the second conflicts on C. The first row show go | |
| 256 # and and then there should be a constraint error. | |
| 257 # | |
| 258 do_test conflict-8.3 { | |
| 259 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} | |
| 260 } {1 {UNIQUE constraint failed: t1.c}} | |
| 261 do_execsql_test conflict-8.4 { | |
| 262 SELECT a,b,c FROM t1 ORDER BY a; | |
| 263 } {1 2 3 2 3 4 4 5 6} | |
| 264 | |
| 265 # Change which column is the PRIMARY KEY | |
| 266 # | |
| 267 do_execsql_test conflict-9.1 { | |
| 268 DROP TABLE t1; | |
| 269 CREATE TABLE t1( | |
| 270 a UNIQUE ON CONFLICT REPLACE, | |
| 271 b INT PRIMARY KEY ON CONFLICT IGNORE, | |
| 272 c UNIQUE ON CONFLICT FAIL | |
| 273 ) WITHOUT ROWID; | |
| 274 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); | |
| 275 SELECT a,b,c FROM t1 ORDER BY a; | |
| 276 } {1 2 3 2 3 4} | |
| 277 | |
| 278 # Insert a row that conflicts on column B. The insert should be ignored. | |
| 279 # | |
| 280 do_execsql_test conflict-9.2 { | |
| 281 INSERT INTO t1(a,b,c) VALUES(3,2,5); | |
| 282 SELECT a,b,c FROM t1 ORDER BY a; | |
| 283 } {1 2 3 2 3 4} | |
| 284 | |
| 285 # Insert two rows where the second conflicts on C. The first row show go | |
| 286 # and and then there should be a constraint error. | |
| 287 # | |
| 288 do_test conflict-9.3 { | |
| 289 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} | |
| 290 } {1 {UNIQUE constraint failed: t1.c}} | |
| 291 do_execsql_test conflict-9.4 { | |
| 292 SELECT a,b,c FROM t1 ORDER BY a; | |
| 293 } {1 2 3 2 3 4 4 5 6} | |
| 294 | |
| 295 # Change which column is the PRIMARY KEY | |
| 296 # | |
| 297 do_execsql_test conflict-10.1 { | |
| 298 DROP TABLE t1; | |
| 299 CREATE TABLE t1( | |
| 300 a UNIQUE ON CONFLICT REPLACE, | |
| 301 b UNIQUE ON CONFLICT IGNORE, | |
| 302 c INTEGER PRIMARY KEY ON CONFLICT FAIL | |
| 303 ); | |
| 304 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); | |
| 305 SELECT a,b,c FROM t1 ORDER BY a; | |
| 306 } {1 2 3 2 3 4} | |
| 307 | |
| 308 # Insert a row that conflicts on column B. The insert should be ignored. | |
| 309 # | |
| 310 do_execsql_test conflict-10.2 { | |
| 311 INSERT INTO t1(a,b,c) VALUES(3,2,5); | |
| 312 SELECT a,b,c FROM t1 ORDER BY a; | |
| 313 } {1 2 3 2 3 4} | |
| 314 | |
| 315 # Insert two rows where the second conflicts on C. The first row show go | |
| 316 # and and then there should be a constraint error. | |
| 317 # | |
| 318 do_test conflict-10.3 { | |
| 319 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} | |
| 320 } {1 {UNIQUE constraint failed: t1.c}} | |
| 321 do_execsql_test conflict-10.4 { | |
| 322 SELECT a,b,c FROM t1 ORDER BY a; | |
| 323 } {1 2 3 2 3 4 4 5 6} | |
| 324 | |
| 325 # Change which column is the PRIMARY KEY | |
| 326 # | |
| 327 do_execsql_test conflict-11.1 { | |
| 328 DROP TABLE t1; | |
| 329 CREATE TABLE t1( | |
| 330 a UNIQUE ON CONFLICT REPLACE, | |
| 331 b UNIQUE ON CONFLICT IGNORE, | |
| 332 c PRIMARY KEY ON CONFLICT FAIL | |
| 333 ) WITHOUT ROWID; | |
| 334 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); | |
| 335 SELECT a,b,c FROM t1 ORDER BY a; | |
| 336 } {1 2 3 2 3 4} | |
| 337 | |
| 338 # Insert a row that conflicts on column B. The insert should be ignored. | |
| 339 # | |
| 340 do_execsql_test conflict-11.2 { | |
| 341 INSERT INTO t1(a,b,c) VALUES(3,2,5); | |
| 342 SELECT a,b,c FROM t1 ORDER BY a; | |
| 343 } {1 2 3 2 3 4} | |
| 344 | |
| 345 # Insert two rows where the second conflicts on C. The first row show go | |
| 346 # and and then there should be a constraint error. | |
| 347 # | |
| 348 do_test conflict-11.3 { | |
| 349 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} | |
| 350 } {1 {UNIQUE constraint failed: t1.c}} | |
| 351 do_execsql_test conflict-11.4 { | |
| 352 SELECT a,b,c FROM t1 ORDER BY a; | |
| 353 } {1 2 3 2 3 4 4 5 6} | |
| 354 | |
| 355 | |
| 356 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
