1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/conflict.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,792 @@
1.4 +# 2002 January 29
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +#
1.16 +# This file implements tests for the conflict resolution extension
1.17 +# to SQLite.
1.18 +#
1.19 +# $Id: conflict.test,v 1.31 2008/01/21 16:22:46 drh Exp $
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +ifcapable !conflict {
1.25 + finish_test
1.26 + return
1.27 +}
1.28 +
1.29 +# Create tables for the first group of tests.
1.30 +#
1.31 +do_test conflict-1.0 {
1.32 + execsql {
1.33 + CREATE TABLE t1(a, b, c, UNIQUE(a,b));
1.34 + CREATE TABLE t2(x);
1.35 + SELECT c FROM t1 ORDER BY c;
1.36 + }
1.37 +} {}
1.38 +
1.39 +# Six columns of configuration data as follows:
1.40 +#
1.41 +# i The reference number of the test
1.42 +# cmd An INSERT or REPLACE command to execute against table t1
1.43 +# t0 True if there is an error from $cmd
1.44 +# t1 Content of "c" column of t1 assuming no error in $cmd
1.45 +# t2 Content of "x" column of t2
1.46 +# t3 Number of temporary files created by this test
1.47 +#
1.48 +foreach {i cmd t0 t1 t2 t3} {
1.49 + 1 INSERT 1 {} 1 0
1.50 + 2 {INSERT OR IGNORE} 0 3 1 0
1.51 + 3 {INSERT OR REPLACE} 0 4 1 0
1.52 + 4 REPLACE 0 4 1 0
1.53 + 5 {INSERT OR FAIL} 1 {} 1 0
1.54 + 6 {INSERT OR ABORT} 1 {} 1 0
1.55 + 7 {INSERT OR ROLLBACK} 1 {} {} 0
1.56 +} {
1.57 + do_test conflict-1.$i {
1.58 + set ::sqlite_opentemp_count 0
1.59 + set r0 [catch {execsql [subst {
1.60 + DELETE FROM t1;
1.61 + DELETE FROM t2;
1.62 + INSERT INTO t1 VALUES(1,2,3);
1.63 + BEGIN;
1.64 + INSERT INTO t2 VALUES(1);
1.65 + $cmd INTO t1 VALUES(1,2,4);
1.66 + }]} r1]
1.67 + catch {execsql {COMMIT}}
1.68 + if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
1.69 + set r2 [execsql {SELECT x FROM t2}]
1.70 + set r3 $::sqlite_opentemp_count
1.71 + list $r0 $r1 $r2 $r3
1.72 + } [list $t0 $t1 $t2 $t3]
1.73 +}
1.74 +
1.75 +# Create tables for the first group of tests.
1.76 +#
1.77 +do_test conflict-2.0 {
1.78 + execsql {
1.79 + DROP TABLE t1;
1.80 + DROP TABLE t2;
1.81 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(a,b));
1.82 + CREATE TABLE t2(x);
1.83 + SELECT c FROM t1 ORDER BY c;
1.84 + }
1.85 +} {}
1.86 +
1.87 +# Six columns of configuration data as follows:
1.88 +#
1.89 +# i The reference number of the test
1.90 +# cmd An INSERT or REPLACE command to execute against table t1
1.91 +# t0 True if there is an error from $cmd
1.92 +# t1 Content of "c" column of t1 assuming no error in $cmd
1.93 +# t2 Content of "x" column of t2
1.94 +#
1.95 +foreach {i cmd t0 t1 t2} {
1.96 + 1 INSERT 1 {} 1
1.97 + 2 {INSERT OR IGNORE} 0 3 1
1.98 + 3 {INSERT OR REPLACE} 0 4 1
1.99 + 4 REPLACE 0 4 1
1.100 + 5 {INSERT OR FAIL} 1 {} 1
1.101 + 6 {INSERT OR ABORT} 1 {} 1
1.102 + 7 {INSERT OR ROLLBACK} 1 {} {}
1.103 +} {
1.104 + do_test conflict-2.$i {
1.105 + set r0 [catch {execsql [subst {
1.106 + DELETE FROM t1;
1.107 + DELETE FROM t2;
1.108 + INSERT INTO t1 VALUES(1,2,3);
1.109 + BEGIN;
1.110 + INSERT INTO t2 VALUES(1);
1.111 + $cmd INTO t1 VALUES(1,2,4);
1.112 + }]} r1]
1.113 + catch {execsql {COMMIT}}
1.114 + if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
1.115 + set r2 [execsql {SELECT x FROM t2}]
1.116 + list $r0 $r1 $r2
1.117 + } [list $t0 $t1 $t2]
1.118 +}
1.119 +
1.120 +# Create tables for the first group of tests.
1.121 +#
1.122 +do_test conflict-3.0 {
1.123 + execsql {
1.124 + DROP TABLE t1;
1.125 + DROP TABLE t2;
1.126 + CREATE TABLE t1(a, b, c INTEGER, PRIMARY KEY(c), UNIQUE(a,b));
1.127 + CREATE TABLE t2(x);
1.128 + SELECT c FROM t1 ORDER BY c;
1.129 + }
1.130 +} {}
1.131 +
1.132 +# Six columns of configuration data as follows:
1.133 +#
1.134 +# i The reference number of the test
1.135 +# cmd An INSERT or REPLACE command to execute against table t1
1.136 +# t0 True if there is an error from $cmd
1.137 +# t1 Content of "c" column of t1 assuming no error in $cmd
1.138 +# t2 Content of "x" column of t2
1.139 +#
1.140 +foreach {i cmd t0 t1 t2} {
1.141 + 1 INSERT 1 {} 1
1.142 + 2 {INSERT OR IGNORE} 0 3 1
1.143 + 3 {INSERT OR REPLACE} 0 4 1
1.144 + 4 REPLACE 0 4 1
1.145 + 5 {INSERT OR FAIL} 1 {} 1
1.146 + 6 {INSERT OR ABORT} 1 {} 1
1.147 + 7 {INSERT OR ROLLBACK} 1 {} {}
1.148 +} {
1.149 + do_test conflict-3.$i {
1.150 + set r0 [catch {execsql [subst {
1.151 + DELETE FROM t1;
1.152 + DELETE FROM t2;
1.153 + INSERT INTO t1 VALUES(1,2,3);
1.154 + BEGIN;
1.155 + INSERT INTO t2 VALUES(1);
1.156 + $cmd INTO t1 VALUES(1,2,4);
1.157 + }]} r1]
1.158 + catch {execsql {COMMIT}}
1.159 + if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
1.160 + set r2 [execsql {SELECT x FROM t2}]
1.161 + list $r0 $r1 $r2
1.162 + } [list $t0 $t1 $t2]
1.163 +}
1.164 +
1.165 +do_test conflict-4.0 {
1.166 + execsql {
1.167 + DROP TABLE t2;
1.168 + CREATE TABLE t2(x);
1.169 + SELECT x FROM t2;
1.170 + }
1.171 +} {}
1.172 +
1.173 +# Six columns of configuration data as follows:
1.174 +#
1.175 +# i The reference number of the test
1.176 +# conf1 The conflict resolution algorithm on the UNIQUE constraint
1.177 +# cmd An INSERT or REPLACE command to execute against table t1
1.178 +# t0 True if there is an error from $cmd
1.179 +# t1 Content of "c" column of t1 assuming no error in $cmd
1.180 +# t2 Content of "x" column of t2
1.181 +#
1.182 +foreach {i conf1 cmd t0 t1 t2} {
1.183 + 1 {} INSERT 1 {} 1
1.184 + 2 REPLACE INSERT 0 4 1
1.185 + 3 IGNORE INSERT 0 3 1
1.186 + 4 FAIL INSERT 1 {} 1
1.187 + 5 ABORT INSERT 1 {} 1
1.188 + 6 ROLLBACK INSERT 1 {} {}
1.189 + 7 REPLACE {INSERT OR IGNORE} 0 3 1
1.190 + 8 IGNORE {INSERT OR REPLACE} 0 4 1
1.191 + 9 FAIL {INSERT OR IGNORE} 0 3 1
1.192 + 10 ABORT {INSERT OR REPLACE} 0 4 1
1.193 + 11 ROLLBACK {INSERT OR IGNORE } 0 3 1
1.194 +} {
1.195 + do_test conflict-4.$i {
1.196 + if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
1.197 + set r0 [catch {execsql [subst {
1.198 + DROP TABLE t1;
1.199 + CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1);
1.200 + DELETE FROM t2;
1.201 + INSERT INTO t1 VALUES(1,2,3);
1.202 + BEGIN;
1.203 + INSERT INTO t2 VALUES(1);
1.204 + $cmd INTO t1 VALUES(1,2,4);
1.205 + }]} r1]
1.206 + catch {execsql {COMMIT}}
1.207 + if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
1.208 + set r2 [execsql {SELECT x FROM t2}]
1.209 + list $r0 $r1 $r2
1.210 + } [list $t0 $t1 $t2]
1.211 +}
1.212 +
1.213 +do_test conflict-5.0 {
1.214 + execsql {
1.215 + DROP TABLE t2;
1.216 + CREATE TABLE t2(x);
1.217 + SELECT x FROM t2;
1.218 + }
1.219 +} {}
1.220 +
1.221 +# Six columns of configuration data as follows:
1.222 +#
1.223 +# i The reference number of the test
1.224 +# conf1 The conflict resolution algorithm on the NOT NULL constraint
1.225 +# cmd An INSERT or REPLACE command to execute against table t1
1.226 +# t0 True if there is an error from $cmd
1.227 +# t1 Content of "c" column of t1 assuming no error in $cmd
1.228 +# t2 Content of "x" column of t2
1.229 +#
1.230 +foreach {i conf1 cmd t0 t1 t2} {
1.231 + 1 {} INSERT 1 {} 1
1.232 + 2 REPLACE INSERT 0 5 1
1.233 + 3 IGNORE INSERT 0 {} 1
1.234 + 4 FAIL INSERT 1 {} 1
1.235 + 5 ABORT INSERT 1 {} 1
1.236 + 6 ROLLBACK INSERT 1 {} {}
1.237 + 7 REPLACE {INSERT OR IGNORE} 0 {} 1
1.238 + 8 IGNORE {INSERT OR REPLACE} 0 5 1
1.239 + 9 FAIL {INSERT OR IGNORE} 0 {} 1
1.240 + 10 ABORT {INSERT OR REPLACE} 0 5 1
1.241 + 11 ROLLBACK {INSERT OR IGNORE} 0 {} 1
1.242 + 12 {} {INSERT OR IGNORE} 0 {} 1
1.243 + 13 {} {INSERT OR REPLACE} 0 5 1
1.244 + 14 {} {INSERT OR FAIL} 1 {} 1
1.245 + 15 {} {INSERT OR ABORT} 1 {} 1
1.246 + 16 {} {INSERT OR ROLLBACK} 1 {} {}
1.247 +} {
1.248 + if {$t0} {set t1 {t1.c may not be NULL}}
1.249 + do_test conflict-5.$i {
1.250 + if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
1.251 + set r0 [catch {execsql [subst {
1.252 + DROP TABLE t1;
1.253 + CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5);
1.254 + DELETE FROM t2;
1.255 + BEGIN;
1.256 + INSERT INTO t2 VALUES(1);
1.257 + $cmd INTO t1 VALUES(1,2,NULL);
1.258 + }]} r1]
1.259 + catch {execsql {COMMIT}}
1.260 + if {!$r0} {set r1 [execsql {SELECT c FROM t1}]}
1.261 + set r2 [execsql {SELECT x FROM t2}]
1.262 + list $r0 $r1 $r2
1.263 + } [list $t0 $t1 $t2]
1.264 +}
1.265 +
1.266 +do_test conflict-6.0 {
1.267 + execsql {
1.268 + DROP TABLE t2;
1.269 + CREATE TABLE t2(a,b,c);
1.270 + INSERT INTO t2 VALUES(1,2,1);
1.271 + INSERT INTO t2 VALUES(2,3,2);
1.272 + INSERT INTO t2 VALUES(3,4,1);
1.273 + INSERT INTO t2 VALUES(4,5,4);
1.274 + SELECT c FROM t2 ORDER BY b;
1.275 + CREATE TABLE t3(x);
1.276 + INSERT INTO t3 VALUES(1);
1.277 + }
1.278 +} {1 2 1 4}
1.279 +
1.280 +# Six columns of configuration data as follows:
1.281 +#
1.282 +# i The reference number of the test
1.283 +# conf1 The conflict resolution algorithm on the UNIQUE constraint
1.284 +# cmd An UPDATE command to execute against table t1
1.285 +# t0 True if there is an error from $cmd
1.286 +# t1 Content of "b" column of t1 assuming no error in $cmd
1.287 +# t2 Content of "x" column of t3
1.288 +# t3 Number of temporary files for tables
1.289 +# t4 Number of temporary files for statement journals
1.290 +#
1.291 +# Update: Since temporary table files are now opened lazily, and none
1.292 +# of the following tests use large quantities of data, t3 is always 0.
1.293 +#
1.294 +foreach {i conf1 cmd t0 t1 t2 t3 t4} {
1.295 + 1 {} UPDATE 1 {6 7 8 9} 1 0 1
1.296 + 2 REPLACE UPDATE 0 {7 6 9} 1 0 0
1.297 + 3 IGNORE UPDATE 0 {6 7 3 9} 1 0 0
1.298 + 4 FAIL UPDATE 1 {6 7 3 4} 1 0 0
1.299 + 5 ABORT UPDATE 1 {1 2 3 4} 1 0 1
1.300 + 6 ROLLBACK UPDATE 1 {1 2 3 4} 0 0 0
1.301 + 7 REPLACE {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0
1.302 + 8 IGNORE {UPDATE OR REPLACE} 0 {7 6 9} 1 0 0
1.303 + 9 FAIL {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0
1.304 + 10 ABORT {UPDATE OR REPLACE} 0 {7 6 9} 1 0 0
1.305 + 11 ROLLBACK {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0
1.306 + 12 {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0
1.307 + 13 {} {UPDATE OR REPLACE} 0 {7 6 9} 1 0 0
1.308 + 14 {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 0 0
1.309 + 15 {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 0 1
1.310 + 16 {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 0 0
1.311 +} {
1.312 + if {$t0} {set t1 {column a is not unique}}
1.313 + if {[info exists TEMP_STORE] && $TEMP_STORE>=2} {
1.314 + set t3 $t4
1.315 + } else {
1.316 + set t3 [expr {$t3+$t4}]
1.317 + }
1.318 + do_test conflict-6.$i {
1.319 + db close
1.320 + sqlite3 db test.db
1.321 + if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
1.322 + execsql {pragma temp_store=file}
1.323 + set ::sqlite_opentemp_count 0
1.324 + set r0 [catch {execsql [subst {
1.325 + DROP TABLE t1;
1.326 + CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);
1.327 + INSERT INTO t1 SELECT * FROM t2;
1.328 + UPDATE t3 SET x=0;
1.329 + BEGIN;
1.330 + $cmd t3 SET x=1;
1.331 + $cmd t1 SET b=b*2;
1.332 + $cmd t1 SET a=c+5;
1.333 + }]} r1]
1.334 + catch {execsql {COMMIT}}
1.335 + if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]}
1.336 + set r2 [execsql {SELECT x FROM t3}]
1.337 + list $r0 $r1 $r2 $::sqlite_opentemp_count
1.338 + } [list $t0 $t1 $t2 $t3]
1.339 +}
1.340 +
1.341 +# Test to make sure a lot of IGNOREs don't cause a stack overflow
1.342 +#
1.343 +do_test conflict-7.1 {
1.344 + execsql {
1.345 + DROP TABLE t1;
1.346 + DROP TABLE t2;
1.347 + DROP TABLE t3;
1.348 + CREATE TABLE t1(a unique, b);
1.349 + }
1.350 + for {set i 1} {$i<=50} {incr i} {
1.351 + execsql "INSERT into t1 values($i,[expr {$i+1}]);"
1.352 + }
1.353 + execsql {
1.354 + SELECT count(*), min(a), max(b) FROM t1;
1.355 + }
1.356 +} {50 1 51}
1.357 +do_test conflict-7.2 {
1.358 + execsql {
1.359 + PRAGMA count_changes=on;
1.360 + UPDATE OR IGNORE t1 SET a=1000;
1.361 + }
1.362 +} {1}
1.363 +do_test conflict-7.2.1 {
1.364 + db changes
1.365 +} {1}
1.366 +do_test conflict-7.3 {
1.367 + execsql {
1.368 + SELECT b FROM t1 WHERE a=1000;
1.369 + }
1.370 +} {2}
1.371 +do_test conflict-7.4 {
1.372 + execsql {
1.373 + SELECT count(*) FROM t1;
1.374 + }
1.375 +} {50}
1.376 +do_test conflict-7.5 {
1.377 + execsql {
1.378 + PRAGMA count_changes=on;
1.379 + UPDATE OR REPLACE t1 SET a=1001;
1.380 + }
1.381 +} {50}
1.382 +do_test conflict-7.5.1 {
1.383 + db changes
1.384 +} {50}
1.385 +do_test conflict-7.6 {
1.386 + execsql {
1.387 + SELECT b FROM t1 WHERE a=1001;
1.388 + }
1.389 +} {51}
1.390 +do_test conflict-7.7 {
1.391 + execsql {
1.392 + SELECT count(*) FROM t1;
1.393 + }
1.394 +} {1}
1.395 +
1.396 +# Update for version 3: A SELECT statement no longer resets the change
1.397 +# counter (Test result changes from 0 to 50).
1.398 +do_test conflict-7.7.1 {
1.399 + db changes
1.400 +} {50}
1.401 +
1.402 +# Make sure the row count is right for rows that are ignored on
1.403 +# an insert.
1.404 +#
1.405 +do_test conflict-8.1 {
1.406 + execsql {
1.407 + DELETE FROM t1;
1.408 + INSERT INTO t1 VALUES(1,2);
1.409 + }
1.410 + execsql {
1.411 + INSERT OR IGNORE INTO t1 VALUES(2,3);
1.412 + }
1.413 +} {1}
1.414 +do_test conflict-8.1.1 {
1.415 + db changes
1.416 +} {1}
1.417 +do_test conflict-8.2 {
1.418 + execsql {
1.419 + INSERT OR IGNORE INTO t1 VALUES(2,4);
1.420 + }
1.421 +} {0}
1.422 +do_test conflict-8.2.1 {
1.423 + db changes
1.424 +} {0}
1.425 +do_test conflict-8.3 {
1.426 + execsql {
1.427 + INSERT OR REPLACE INTO t1 VALUES(2,4);
1.428 + }
1.429 +} {1}
1.430 +do_test conflict-8.3.1 {
1.431 + db changes
1.432 +} {1}
1.433 +do_test conflict-8.4 {
1.434 + execsql {
1.435 + INSERT OR IGNORE INTO t1 SELECT * FROM t1;
1.436 + }
1.437 +} {0}
1.438 +do_test conflict-8.4.1 {
1.439 + db changes
1.440 +} {0}
1.441 +do_test conflict-8.5 {
1.442 + execsql {
1.443 + INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1;
1.444 + }
1.445 +} {2}
1.446 +do_test conflict-8.5.1 {
1.447 + db changes
1.448 +} {2}
1.449 +do_test conflict-8.6 {
1.450 + execsql {
1.451 + INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1;
1.452 + }
1.453 +} {3}
1.454 +do_test conflict-8.6.1 {
1.455 + db changes
1.456 +} {3}
1.457 +
1.458 +integrity_check conflict-8.99
1.459 +
1.460 +do_test conflict-9.1 {
1.461 + execsql {
1.462 + PRAGMA count_changes=0;
1.463 + CREATE TABLE t2(
1.464 + a INTEGER UNIQUE ON CONFLICT IGNORE,
1.465 + b INTEGER UNIQUE ON CONFLICT FAIL,
1.466 + c INTEGER UNIQUE ON CONFLICT REPLACE,
1.467 + d INTEGER UNIQUE ON CONFLICT ABORT,
1.468 + e INTEGER UNIQUE ON CONFLICT ROLLBACK
1.469 + );
1.470 + CREATE TABLE t3(x);
1.471 + INSERT INTO t3 VALUES(1);
1.472 + SELECT * FROM t3;
1.473 + }
1.474 +} {1}
1.475 +do_test conflict-9.2 {
1.476 + catchsql {
1.477 + INSERT INTO t2 VALUES(1,1,1,1,1);
1.478 + INSERT INTO t2 VALUES(2,2,2,2,2);
1.479 + SELECT * FROM t2;
1.480 + }
1.481 +} {0 {1 1 1 1 1 2 2 2 2 2}}
1.482 +do_test conflict-9.3 {
1.483 + catchsql {
1.484 + INSERT INTO t2 VALUES(1,3,3,3,3);
1.485 + SELECT * FROM t2;
1.486 + }
1.487 +} {0 {1 1 1 1 1 2 2 2 2 2}}
1.488 +do_test conflict-9.4 {
1.489 + catchsql {
1.490 + UPDATE t2 SET a=a+1 WHERE a=1;
1.491 + SELECT * FROM t2;
1.492 + }
1.493 +} {0 {1 1 1 1 1 2 2 2 2 2}}
1.494 +do_test conflict-9.5 {
1.495 + catchsql {
1.496 + INSERT INTO t2 VALUES(3,1,3,3,3);
1.497 + SELECT * FROM t2;
1.498 + }
1.499 +} {1 {column b is not unique}}
1.500 +do_test conflict-9.6 {
1.501 + catchsql {
1.502 + UPDATE t2 SET b=b+1 WHERE b=1;
1.503 + SELECT * FROM t2;
1.504 + }
1.505 +} {1 {column b is not unique}}
1.506 +do_test conflict-9.7 {
1.507 + catchsql {
1.508 + BEGIN;
1.509 + UPDATE t3 SET x=x+1;
1.510 + INSERT INTO t2 VALUES(3,1,3,3,3);
1.511 + SELECT * FROM t2;
1.512 + }
1.513 +} {1 {column b is not unique}}
1.514 +do_test conflict-9.8 {
1.515 + execsql {COMMIT}
1.516 + execsql {SELECT * FROM t3}
1.517 +} {2}
1.518 +do_test conflict-9.9 {
1.519 + catchsql {
1.520 + BEGIN;
1.521 + UPDATE t3 SET x=x+1;
1.522 + UPDATE t2 SET b=b+1 WHERE b=1;
1.523 + SELECT * FROM t2;
1.524 + }
1.525 +} {1 {column b is not unique}}
1.526 +do_test conflict-9.10 {
1.527 + execsql {COMMIT}
1.528 + execsql {SELECT * FROM t3}
1.529 +} {3}
1.530 +do_test conflict-9.11 {
1.531 + catchsql {
1.532 + INSERT INTO t2 VALUES(3,3,3,1,3);
1.533 + SELECT * FROM t2;
1.534 + }
1.535 +} {1 {column d is not unique}}
1.536 +do_test conflict-9.12 {
1.537 + catchsql {
1.538 + UPDATE t2 SET d=d+1 WHERE d=1;
1.539 + SELECT * FROM t2;
1.540 + }
1.541 +} {1 {column d is not unique}}
1.542 +do_test conflict-9.13 {
1.543 + catchsql {
1.544 + BEGIN;
1.545 + UPDATE t3 SET x=x+1;
1.546 + INSERT INTO t2 VALUES(3,3,3,1,3);
1.547 + SELECT * FROM t2;
1.548 + }
1.549 +} {1 {column d is not unique}}
1.550 +do_test conflict-9.14 {
1.551 + execsql {COMMIT}
1.552 + execsql {SELECT * FROM t3}
1.553 +} {4}
1.554 +do_test conflict-9.15 {
1.555 + catchsql {
1.556 + BEGIN;
1.557 + UPDATE t3 SET x=x+1;
1.558 + UPDATE t2 SET d=d+1 WHERE d=1;
1.559 + SELECT * FROM t2;
1.560 + }
1.561 +} {1 {column d is not unique}}
1.562 +do_test conflict-9.16 {
1.563 + execsql {COMMIT}
1.564 + execsql {SELECT * FROM t3}
1.565 +} {5}
1.566 +do_test conflict-9.17 {
1.567 + catchsql {
1.568 + INSERT INTO t2 VALUES(3,3,3,3,1);
1.569 + SELECT * FROM t2;
1.570 + }
1.571 +} {1 {column e is not unique}}
1.572 +do_test conflict-9.18 {
1.573 + catchsql {
1.574 + UPDATE t2 SET e=e+1 WHERE e=1;
1.575 + SELECT * FROM t2;
1.576 + }
1.577 +} {1 {column e is not unique}}
1.578 +do_test conflict-9.19 {
1.579 + catchsql {
1.580 + BEGIN;
1.581 + UPDATE t3 SET x=x+1;
1.582 + INSERT INTO t2 VALUES(3,3,3,3,1);
1.583 + SELECT * FROM t2;
1.584 + }
1.585 +} {1 {column e is not unique}}
1.586 +do_test conflict-9.20 {
1.587 + catch {execsql {COMMIT}}
1.588 + execsql {SELECT * FROM t3}
1.589 +} {5}
1.590 +do_test conflict-9.21 {
1.591 + catchsql {
1.592 + BEGIN;
1.593 + UPDATE t3 SET x=x+1;
1.594 + UPDATE t2 SET e=e+1 WHERE e=1;
1.595 + SELECT * FROM t2;
1.596 + }
1.597 +} {1 {column e is not unique}}
1.598 +do_test conflict-9.22 {
1.599 + catch {execsql {COMMIT}}
1.600 + execsql {SELECT * FROM t3}
1.601 +} {5}
1.602 +do_test conflict-9.23 {
1.603 + catchsql {
1.604 + INSERT INTO t2 VALUES(3,3,1,3,3);
1.605 + SELECT * FROM t2;
1.606 + }
1.607 +} {0 {2 2 2 2 2 3 3 1 3 3}}
1.608 +do_test conflict-9.24 {
1.609 + catchsql {
1.610 + UPDATE t2 SET c=c-1 WHERE c=2;
1.611 + SELECT * FROM t2;
1.612 + }
1.613 +} {0 {2 2 1 2 2}}
1.614 +do_test conflict-9.25 {
1.615 + catchsql {
1.616 + BEGIN;
1.617 + UPDATE t3 SET x=x+1;
1.618 + INSERT INTO t2 VALUES(3,3,1,3,3);
1.619 + SELECT * FROM t2;
1.620 + }
1.621 +} {0 {3 3 1 3 3}}
1.622 +do_test conflict-9.26 {
1.623 + catch {execsql {COMMIT}}
1.624 + execsql {SELECT * FROM t3}
1.625 +} {6}
1.626 +
1.627 +do_test conflict-10.1 {
1.628 + catchsql {
1.629 + DELETE FROM t1;
1.630 + BEGIN;
1.631 + INSERT OR ROLLBACK INTO t1 VALUES(1,2);
1.632 + INSERT OR ROLLBACK INTO t1 VALUES(1,3);
1.633 + COMMIT;
1.634 + }
1.635 + execsql {SELECT * FROM t1}
1.636 +} {}
1.637 +do_test conflict-10.2 {
1.638 + catchsql {
1.639 + CREATE TABLE t4(x);
1.640 + CREATE UNIQUE INDEX t4x ON t4(x);
1.641 + BEGIN;
1.642 + INSERT OR ROLLBACK INTO t4 VALUES(1);
1.643 + INSERT OR ROLLBACK INTO t4 VALUES(1);
1.644 + COMMIT;
1.645 + }
1.646 + execsql {SELECT * FROM t4}
1.647 +} {}
1.648 +
1.649 +# Ticket #1171. Make sure statement rollbacks do not
1.650 +# damage the database.
1.651 +#
1.652 +do_test conflict-11.1 {
1.653 + execsql {
1.654 + -- Create a database object (pages 2, 3 of the file)
1.655 + BEGIN;
1.656 + CREATE TABLE abc(a UNIQUE, b, c);
1.657 + INSERT INTO abc VALUES(1, 2, 3);
1.658 + INSERT INTO abc VALUES(4, 5, 6);
1.659 + INSERT INTO abc VALUES(7, 8, 9);
1.660 + COMMIT;
1.661 + }
1.662 +
1.663 +
1.664 + # Set a small cache size so that changes will spill into
1.665 + # the database file.
1.666 + execsql {
1.667 + PRAGMA cache_size = 10;
1.668 + }
1.669 +
1.670 + # Make lots of changes. Because of the small cache, some
1.671 + # (most?) of these changes will spill into the disk file.
1.672 + # In other words, some of the changes will not be held in
1.673 + # cache.
1.674 + #
1.675 + execsql {
1.676 + BEGIN;
1.677 + -- Make sure the pager is in EXCLUSIVE state.
1.678 + CREATE TABLE def(d, e, f);
1.679 + INSERT INTO def VALUES
1.680 + ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
1.681 + INSERT INTO def SELECT * FROM def;
1.682 + INSERT INTO def SELECT * FROM def;
1.683 + INSERT INTO def SELECT * FROM def;
1.684 + INSERT INTO def SELECT * FROM def;
1.685 + INSERT INTO def SELECT * FROM def;
1.686 + INSERT INTO def SELECT * FROM def;
1.687 + INSERT INTO def SELECT * FROM def;
1.688 + DELETE FROM abc WHERE a = 4;
1.689 + }
1.690 +
1.691 + # Execute a statement that does a statement rollback due to
1.692 + # a constraint failure.
1.693 + #
1.694 + catchsql {
1.695 + INSERT INTO abc SELECT 10, 20, 30 FROM def;
1.696 + }
1.697 +
1.698 + # Rollback the database. Verify that the state of the ABC table
1.699 + # is unchanged from the beginning of the transaction. In other words,
1.700 + # make sure the DELETE on table ABC that occurred within the transaction
1.701 + # had no effect.
1.702 + #
1.703 + execsql {
1.704 + ROLLBACK;
1.705 + SELECT * FROM abc;
1.706 + }
1.707 +} {1 2 3 4 5 6 7 8 9}
1.708 +integrity_check conflict-11.2
1.709 +
1.710 +# Repeat test conflict-11.1 but this time commit.
1.711 +#
1.712 +do_test conflict-11.3 {
1.713 + execsql {
1.714 + BEGIN;
1.715 + -- Make sure the pager is in EXCLUSIVE state.
1.716 + UPDATE abc SET a=a+1;
1.717 + CREATE TABLE def(d, e, f);
1.718 + INSERT INTO def VALUES
1.719 + ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
1.720 + INSERT INTO def SELECT * FROM def;
1.721 + INSERT INTO def SELECT * FROM def;
1.722 + INSERT INTO def SELECT * FROM def;
1.723 + INSERT INTO def SELECT * FROM def;
1.724 + INSERT INTO def SELECT * FROM def;
1.725 + INSERT INTO def SELECT * FROM def;
1.726 + INSERT INTO def SELECT * FROM def;
1.727 + DELETE FROM abc WHERE a = 4;
1.728 + }
1.729 + catchsql {
1.730 + INSERT INTO abc SELECT 10, 20, 30 FROM def;
1.731 + }
1.732 + execsql {
1.733 + ROLLBACK;
1.734 + SELECT * FROM abc;
1.735 + }
1.736 +} {1 2 3 4 5 6 7 8 9}
1.737 +# Repeat test conflict-11.1 but this time commit.
1.738 +#
1.739 +do_test conflict-11.5 {
1.740 + execsql {
1.741 + BEGIN;
1.742 + -- Make sure the pager is in EXCLUSIVE state.
1.743 + CREATE TABLE def(d, e, f);
1.744 + INSERT INTO def VALUES
1.745 + ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
1.746 + INSERT INTO def SELECT * FROM def;
1.747 + INSERT INTO def SELECT * FROM def;
1.748 + INSERT INTO def SELECT * FROM def;
1.749 + INSERT INTO def SELECT * FROM def;
1.750 + INSERT INTO def SELECT * FROM def;
1.751 + INSERT INTO def SELECT * FROM def;
1.752 + INSERT INTO def SELECT * FROM def;
1.753 + DELETE FROM abc WHERE a = 4;
1.754 + }
1.755 + catchsql {
1.756 + INSERT INTO abc SELECT 10, 20, 30 FROM def;
1.757 + }
1.758 + execsql {
1.759 + COMMIT;
1.760 + SELECT * FROM abc;
1.761 + }
1.762 +} {1 2 3 7 8 9}
1.763 +integrity_check conflict-11.6
1.764 +
1.765 +# Make sure UPDATE OR REPLACE works on tables that have only
1.766 +# an INTEGER PRIMARY KEY.
1.767 +#
1.768 +do_test conflict-12.1 {
1.769 + execsql {
1.770 + CREATE TABLE t5(a INTEGER PRIMARY KEY, b text);
1.771 + INSERT INTO t5 VALUES(1,'one');
1.772 + INSERT INTO t5 VALUES(2,'two');
1.773 + SELECT * FROM t5
1.774 + }
1.775 +} {1 one 2 two}
1.776 +do_test conflict-12.2 {
1.777 + execsql {
1.778 + UPDATE OR IGNORE t5 SET a=a+1 WHERE a=1;
1.779 + SELECT * FROM t5;
1.780 + }
1.781 +} {1 one 2 two}
1.782 +do_test conflict-12.3 {
1.783 + catchsql {
1.784 + UPDATE t5 SET a=a+1 WHERE a=1;
1.785 + }
1.786 +} {1 {PRIMARY KEY must be unique}}
1.787 +do_test conflict-12.4 {
1.788 + execsql {
1.789 + UPDATE OR REPLACE t5 SET a=a+1 WHERE a=1;
1.790 + SELECT * FROM t5;
1.791 + }
1.792 +} {2 one}
1.793 +
1.794 +
1.795 +finish_test