drh | 8d1b82e | 2013-11-05 19:41:32 +0000 | [diff] [blame] | 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 |