15821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 2002 January 29 25821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 35821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# The author disclaims copyright to this source code. In place of 45821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# a legal notice, here is a blessing: 55821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 65821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# May you do good and not evil. 75821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# May you find forgiveness for yourself and forgive others. 85821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# May you share freely, never taking more than you give. 95821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)#*********************************************************************** 115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# This file implements regression tests for SQLite library. 125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# This file implements tests for the conflict resolution extension 145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# to SQLite. 155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# $Id: conflict.test,v 1.32 2009/04/30 09:10:38 danielk1977 Exp $ 175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)set testdir [file dirname $argv0] 195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)source $testdir/tester.tcl 205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)ifcapable !conflict { 225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) finish_test 235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) return 245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Create tables for the first group of tests. 275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-1.0 { 295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t1(a, b, c, UNIQUE(a,b)); 315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t2(x); 325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT c FROM t1 ORDER BY c; 335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Six columns of configuration data as follows: 375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# i The reference number of the test 395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# cmd An INSERT or REPLACE command to execute against table t1 405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t0 True if there is an error from $cmd 415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t1 Content of "c" column of t1 assuming no error in $cmd 425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t2 Content of "x" column of t2 435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t3 Number of temporary files created by this test 445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)foreach {i cmd t0 t1 t2 t3} { 465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1 INSERT 1 {} 1 0 475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2 {INSERT OR IGNORE} 0 3 1 0 485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3 {INSERT OR REPLACE} 0 4 1 0 495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 4 REPLACE 0 4 1 0 505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 5 {INSERT OR FAIL} 1 {} 1 0 515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6 {INSERT OR ABORT} 1 {} 1 0 525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 7 {INSERT OR ROLLBACK} 1 {} {} 0 535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} { 545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) do_test conflict-1.$i { 555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set ::sqlite_opentemp_count 0 565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r0 [catch {execsql [subst { 575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM t1; 585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM t2; 595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t1 VALUES(1,2,3); 605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(1); 625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) $cmd INTO t1 VALUES(1,2,4); 635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) }]} r1] 645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catch {execsql {COMMIT}} 655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r2 [execsql {SELECT x FROM t2}] 675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r3 $::sqlite_opentemp_count 685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) list $r0 $r1 $r2 $r3 695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } [list $t0 $t1 $t2 $t3] 705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Create tables for the first group of tests. 735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-2.0 { 755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t1; 775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t2; 785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(a,b)); 795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t2(x); 805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT c FROM t1 ORDER BY c; 815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Six columns of configuration data as follows: 855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# i The reference number of the test 875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# cmd An INSERT or REPLACE command to execute against table t1 885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t0 True if there is an error from $cmd 895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t1 Content of "c" column of t1 assuming no error in $cmd 905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t2 Content of "x" column of t2 915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)foreach {i cmd t0 t1 t2} { 935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1 INSERT 1 {} 1 945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2 {INSERT OR IGNORE} 0 3 1 955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3 {INSERT OR REPLACE} 0 4 1 965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 4 REPLACE 0 4 1 975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 5 {INSERT OR FAIL} 1 {} 1 985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6 {INSERT OR ABORT} 1 {} 1 995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 7 {INSERT OR ROLLBACK} 1 {} {} 1005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} { 1015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) do_test conflict-2.$i { 1025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r0 [catch {execsql [subst { 1035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM t1; 1045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM t2; 1055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t1 VALUES(1,2,3); 1065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 1075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(1); 1085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) $cmd INTO t1 VALUES(1,2,4); 1095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) }]} r1] 1105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catch {execsql {COMMIT}} 1115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 1125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r2 [execsql {SELECT x FROM t2}] 1135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) list $r0 $r1 $r2 1145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } [list $t0 $t1 $t2] 1155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 1165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Create tables for the first group of tests. 1185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 1195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-3.0 { 1205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 1215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t1; 1225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t2; 1235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t1(a, b, c INTEGER, PRIMARY KEY(c), UNIQUE(a,b)); 1245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t2(x); 1255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT c FROM t1 ORDER BY c; 1265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 1285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Six columns of configuration data as follows: 1305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 1315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# i The reference number of the test 1325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# cmd An INSERT or REPLACE command to execute against table t1 1335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t0 True if there is an error from $cmd 1345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t1 Content of "c" column of t1 assuming no error in $cmd 1355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t2 Content of "x" column of t2 1365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 1375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)foreach {i cmd t0 t1 t2} { 1385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1 INSERT 1 {} 1 1395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2 {INSERT OR IGNORE} 0 3 1 1405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3 {INSERT OR REPLACE} 0 4 1 1415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 4 REPLACE 0 4 1 1425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 5 {INSERT OR FAIL} 1 {} 1 1435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6 {INSERT OR ABORT} 1 {} 1 1445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 7 {INSERT OR ROLLBACK} 1 {} {} 1455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} { 1465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) do_test conflict-3.$i { 1475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r0 [catch {execsql [subst { 1485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM t1; 1495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM t2; 1505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t1 VALUES(1,2,3); 1515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 1525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(1); 1535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) $cmd INTO t1 VALUES(1,2,4); 1545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) }]} r1] 1555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catch {execsql {COMMIT}} 1565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 1575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r2 [execsql {SELECT x FROM t2}] 1585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) list $r0 $r1 $r2 1595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } [list $t0 $t1 $t2] 1605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 1615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-4.0 { 1635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 1645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t2; 1655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t2(x); 1665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT x FROM t2; 1675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 1685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 1695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Six columns of configuration data as follows: 1715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 1725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# i The reference number of the test 1735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# conf1 The conflict resolution algorithm on the UNIQUE constraint 1745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# cmd An INSERT or REPLACE command to execute against table t1 1755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t0 True if there is an error from $cmd 1765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t1 Content of "c" column of t1 assuming no error in $cmd 1775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t2 Content of "x" column of t2 1785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 1795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)foreach {i conf1 cmd t0 t1 t2} { 1805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1 {} INSERT 1 {} 1 1815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2 REPLACE INSERT 0 4 1 1825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3 IGNORE INSERT 0 3 1 1835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 4 FAIL INSERT 1 {} 1 1845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 5 ABORT INSERT 1 {} 1 1855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6 ROLLBACK INSERT 1 {} {} 1865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 7 REPLACE {INSERT OR IGNORE} 0 3 1 1875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 8 IGNORE {INSERT OR REPLACE} 0 4 1 1885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 9 FAIL {INSERT OR IGNORE} 0 3 1 1895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 10 ABORT {INSERT OR REPLACE} 0 4 1 1905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 11 ROLLBACK {INSERT OR IGNORE } 0 3 1 1915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} { 1925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) do_test conflict-4.$i { 1935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} 1945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r0 [catch {execsql [subst { 1955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t1; 1965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1); 1975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM t2; 1985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t1 VALUES(1,2,3); 1995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 2005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(1); 2015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) $cmd INTO t1 VALUES(1,2,4); 2025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) }]} r1] 2035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catch {execsql {COMMIT}} 2045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 2055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r2 [execsql {SELECT x FROM t2}] 2065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) list $r0 $r1 $r2 2075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } [list $t0 $t1 $t2] 2085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 2095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-5.0 { 2115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 2125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t2; 2135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t2(x); 2145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT x FROM t2; 2155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 2165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 2175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Six columns of configuration data as follows: 2195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 2205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# i The reference number of the test 2215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# conf1 The conflict resolution algorithm on the NOT NULL constraint 2225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# cmd An INSERT or REPLACE command to execute against table t1 2235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t0 True if there is an error from $cmd 2245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t1 Content of "c" column of t1 assuming no error in $cmd 2255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t2 Content of "x" column of t2 2265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 2275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)foreach {i conf1 cmd t0 t1 t2} { 2285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1 {} INSERT 1 {} 1 2295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2 REPLACE INSERT 0 5 1 2305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3 IGNORE INSERT 0 {} 1 2315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 4 FAIL INSERT 1 {} 1 2325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 5 ABORT INSERT 1 {} 1 2335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6 ROLLBACK INSERT 1 {} {} 2345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 7 REPLACE {INSERT OR IGNORE} 0 {} 1 2355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 8 IGNORE {INSERT OR REPLACE} 0 5 1 2365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 9 FAIL {INSERT OR IGNORE} 0 {} 1 2375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 10 ABORT {INSERT OR REPLACE} 0 5 1 2385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 11 ROLLBACK {INSERT OR IGNORE} 0 {} 1 2395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 12 {} {INSERT OR IGNORE} 0 {} 1 2405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 13 {} {INSERT OR REPLACE} 0 5 1 2415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 14 {} {INSERT OR FAIL} 1 {} 1 2425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 15 {} {INSERT OR ABORT} 1 {} 1 2435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 16 {} {INSERT OR ROLLBACK} 1 {} {} 2445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} { 2455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$t0} {set t1 {t1.c may not be NULL}} 2465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) do_test conflict-5.$i { 2475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} 2485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r0 [catch {execsql [subst { 2495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t1; 2505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5); 2515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM t2; 2525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 2535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(1); 2545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) $cmd INTO t1 VALUES(1,2,NULL); 2555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) }]} r1] 2565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catch {execsql {COMMIT}} 2575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {!$r0} {set r1 [execsql {SELECT c FROM t1}]} 2585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r2 [execsql {SELECT x FROM t2}] 2595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) list $r0 $r1 $r2 2605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } [list $t0 $t1 $t2] 2615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 2625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-6.0 { 2645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 2655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t2; 2665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t2(a,b,c); 2675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(1,2,1); 2685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(2,3,2); 2695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(3,4,1); 2705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(4,5,4); 2715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT c FROM t2 ORDER BY b; 2725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t3(x); 2735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t3 VALUES(1); 2745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 2755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 2 1 4} 2765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Six columns of configuration data as follows: 2785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 2795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# i The reference number of the test 2805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# conf1 The conflict resolution algorithm on the UNIQUE constraint 2815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# cmd An UPDATE command to execute against table t1 2825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t0 True if there is an error from $cmd 2835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t1 Content of "b" column of t1 assuming no error in $cmd 2845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t2 Content of "x" column of t3 2855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t3 Number of temporary files for tables 2865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# t4 Number of temporary files for statement journals 2875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 2885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Update: Since temporary table files are now opened lazily, and none 2895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# of the following tests use large quantities of data, t3 is always 0. 2905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 2915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)foreach {i conf1 cmd t0 t1 t2 t3 t4} { 2925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 1 {} UPDATE 1 {6 7 8 9} 1 0 1 2935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 2 REPLACE UPDATE 0 {7 6 9} 1 0 0 2945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3 IGNORE UPDATE 0 {6 7 3 9} 1 0 0 2955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 4 FAIL UPDATE 1 {6 7 3 4} 1 0 0 2965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 5 ABORT UPDATE 1 {1 2 3 4} 1 0 1 2975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6 ROLLBACK UPDATE 1 {1 2 3 4} 0 0 0 2985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 7 REPLACE {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0 2995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 8 IGNORE {UPDATE OR REPLACE} 0 {7 6 9} 1 0 0 3005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 9 FAIL {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0 3015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 10 ABORT {UPDATE OR REPLACE} 0 {7 6 9} 1 0 0 3025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 11 ROLLBACK {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0 3035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 12 {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0 3045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 13 {} {UPDATE OR REPLACE} 0 {7 6 9} 1 0 0 3055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 14 {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 0 0 3065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 15 {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 0 1 3075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 16 {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 0 0 3085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} { 3095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$t0} {set t1 {column a is not unique}} 3105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {[info exists TEMP_STORE] && $TEMP_STORE==3} { 3115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set t3 0 3125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } else { 3135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set t3 [expr {$t3+$t4}] 3145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) do_test conflict-6.$i { 3165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db close 3175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) sqlite3 db test.db 3185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} 3195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {pragma temp_store=file} 3205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set ::sqlite_opentemp_count 0 3215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r0 [catch {execsql [subst { 3225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t1; 3235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1); 3245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t1 SELECT * FROM t2; 3255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t3 SET x=0; 3265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 3275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) $cmd t3 SET x=1; 3285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) $cmd t1 SET b=b*2; 3295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) $cmd t1 SET a=c+5; 3305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) }]} r1] 3315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catch {execsql {COMMIT}} 3325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]} 3335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) set r2 [execsql {SELECT x FROM t3}] 3345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) list $r0 $r1 $r2 $::sqlite_opentemp_count 3355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } [list $t0 $t1 $t2 $t3] 3365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} 3375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Test to make sure a lot of IGNOREs don't cause a stack overflow 3395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 3405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-7.1 { 3415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 3425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t1; 3435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t2; 3445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DROP TABLE t3; 3455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t1(a unique, b); 3465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) for {set i 1} {$i<=50} {incr i} { 3485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql "INSERT into t1 values($i,[expr {$i+1}]);" 3495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 3515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT count(*), min(a), max(b) FROM t1; 3525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {50 1 51} 3545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-7.2 { 3555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 3565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) PRAGMA count_changes=on; 3575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE OR IGNORE t1 SET a=1000; 3585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1} 3605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-7.2.1 { 3615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db changes 3625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1} 3635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-7.3 { 3645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 3655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT b FROM t1 WHERE a=1000; 3665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {2} 3685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-7.4 { 3695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 3705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT count(*) FROM t1; 3715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {50} 3735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-7.5 { 3745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 3755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) PRAGMA count_changes=on; 3765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE OR REPLACE t1 SET a=1001; 3775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {50} 3795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-7.5.1 { 3805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db changes 3815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {50} 3825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-7.6 { 3835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 3845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT b FROM t1 WHERE a=1001; 3855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {51} 3875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-7.7 { 3885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 3895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT count(*) FROM t1; 3905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 3915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1} 3925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Update for version 3: A SELECT statement no longer resets the change 3945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# counter (Test result changes from 0 to 50). 3955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-7.7.1 { 3965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db changes 3975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {50} 3985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 3995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Make sure the row count is right for rows that are ignored on 4005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# an insert. 4015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 4025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-8.1 { 4035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 4045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM t1; 4055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t1 VALUES(1,2); 4065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 4075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 4085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT OR IGNORE INTO t1 VALUES(2,3); 4095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 4105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1} 4115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-8.1.1 { 4125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db changes 4135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1} 4145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-8.2 { 4155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 4165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT OR IGNORE INTO t1 VALUES(2,4); 4175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 4185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {0} 4195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-8.2.1 { 4205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db changes 4215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {0} 4225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-8.3 { 4235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 4245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT OR REPLACE INTO t1 VALUES(2,4); 4255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 4265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1} 4275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-8.3.1 { 4285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db changes 4295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1} 4305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-8.4 { 4315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 4325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT OR IGNORE INTO t1 SELECT * FROM t1; 4335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 4345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {0} 4355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-8.4.1 { 4365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db changes 4375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {0} 4385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-8.5 { 4395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 4405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1; 4415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 4425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {2} 4435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-8.5.1 { 4445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db changes 4455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {2} 4465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-8.6 { 4475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 4485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1; 4495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 4505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {3} 4515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-8.6.1 { 4525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) db changes 4535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {3} 4545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 4555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)integrity_check conflict-8.99 4565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 4575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.1 { 4585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 4595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) PRAGMA count_changes=0; 4605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t2( 4615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) a INTEGER UNIQUE ON CONFLICT IGNORE, 4625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) b INTEGER UNIQUE ON CONFLICT FAIL, 4635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) c INTEGER UNIQUE ON CONFLICT REPLACE, 4645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) d INTEGER UNIQUE ON CONFLICT ABORT, 4655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) e INTEGER UNIQUE ON CONFLICT ROLLBACK 4665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ); 4675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t3(x); 4685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t3 VALUES(1); 4695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t3; 4705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 4715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1} 4725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.2 { 4735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 4745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(1,1,1,1,1); 4755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(2,2,2,2,2); 4765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 4775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 4785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {0 {1 1 1 1 1 2 2 2 2 2}} 4795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.3 { 4805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 4815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(1,3,3,3,3); 4825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 4835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 4845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {0 {1 1 1 1 1 2 2 2 2 2}} 4855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.4 { 4865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 4875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t2 SET a=a+1 WHERE a=1; 4885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 4895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 4905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {0 {1 1 1 1 1 2 2 2 2 2}} 4915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.5 { 4925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 4935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(3,1,3,3,3); 4945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 4955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 4965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {column b is not unique}} 4975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.6 { 4985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 4995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t2 SET b=b+1 WHERE b=1; 5005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 5015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 5025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {column b is not unique}} 5035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.7 { 5045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 5055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 5065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t3 SET x=x+1; 5075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(3,1,3,3,3); 5085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 5095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 5105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {column b is not unique}} 5115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.8 { 5125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {COMMIT} 5135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {SELECT * FROM t3} 5145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {2} 5155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.9 { 5165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 5175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 5185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t3 SET x=x+1; 5195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t2 SET b=b+1 WHERE b=1; 5205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 5215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 5225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {column b is not unique}} 5235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.10 { 5245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {COMMIT} 5255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {SELECT * FROM t3} 5265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {3} 5275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.11 { 5285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 5295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(3,3,3,1,3); 5305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 5315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 5325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {column d is not unique}} 5335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.12 { 5345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 5355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t2 SET d=d+1 WHERE d=1; 5365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 5375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 5385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {column d is not unique}} 5395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.13 { 5405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 5415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 5425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t3 SET x=x+1; 5435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(3,3,3,1,3); 5445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 5455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 5465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {column d is not unique}} 5475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.14 { 5485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {COMMIT} 5495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {SELECT * FROM t3} 5505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {4} 5515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.15 { 5525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 5535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 5545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t3 SET x=x+1; 5555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t2 SET d=d+1 WHERE d=1; 5565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 5575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 5585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {column d is not unique}} 5595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.16 { 5605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {COMMIT} 5615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {SELECT * FROM t3} 5625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {5} 5635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.17 { 5645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 5655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(3,3,3,3,1); 5665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 5675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 5685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {column e is not unique}} 5695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.18 { 5705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 5715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t2 SET e=e+1 WHERE e=1; 5725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 5735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 5745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {column e is not unique}} 5755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.19 { 5765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 5775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 5785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t3 SET x=x+1; 5795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(3,3,3,3,1); 5805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 5815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 5825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {column e is not unique}} 5835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.20 { 5845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catch {execsql {COMMIT}} 5855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {SELECT * FROM t3} 5865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {5} 5875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.21 { 5885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 5895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 5905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t3 SET x=x+1; 5915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t2 SET e=e+1 WHERE e=1; 5925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 5935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 5945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {column e is not unique}} 5955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.22 { 5965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catch {execsql {COMMIT}} 5975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {SELECT * FROM t3} 5985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {5} 5995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.23 { 6005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 6015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(3,3,1,3,3); 6025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 6035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 6045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {0 {2 2 2 2 2 3 3 1 3 3}} 6055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.24 { 6065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 6075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t2 SET c=c-1 WHERE c=2; 6085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 6095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 6105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {0 {2 2 1 2 2}} 6115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.25 { 6125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 6135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 6145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t3 SET x=x+1; 6155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t2 VALUES(3,3,1,3,3); 6165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t2; 6175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 6185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {0 {3 3 1 3 3}} 6195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-9.26 { 6205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catch {execsql {COMMIT}} 6215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {SELECT * FROM t3} 6225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {6} 6235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-10.1 { 6255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 6265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM t1; 6275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 6285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT OR ROLLBACK INTO t1 VALUES(1,2); 6295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT OR ROLLBACK INTO t1 VALUES(1,3); 6305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) COMMIT; 6315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 6325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {SELECT * FROM t1} 6335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 6345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-10.2 { 6355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 6365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t4(x); 6375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE UNIQUE INDEX t4x ON t4(x); 6385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 6395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT OR ROLLBACK INTO t4 VALUES(1); 6405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT OR ROLLBACK INTO t4 VALUES(1); 6415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) COMMIT; 6425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 6435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql {SELECT * FROM t4} 6445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {} 6455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Ticket #1171. Make sure statement rollbacks do not 6475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# damage the database. 6485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 6495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-11.1 { 6505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 6515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) -- Create a database object (pages 2, 3 of the file) 6525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 6535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE abc(a UNIQUE, b, c); 6545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO abc VALUES(1, 2, 3); 6555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO abc VALUES(4, 5, 6); 6565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO abc VALUES(7, 8, 9); 6575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) COMMIT; 6585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 6595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # Set a small cache size so that changes will spill into 6625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # the database file. 6635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 6645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) PRAGMA cache_size = 10; 6655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 6665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # Make lots of changes. Because of the small cache, some 6685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # (most?) of these changes will spill into the disk file. 6695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # In other words, some of the changes will not be held in 6705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # cache. 6715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # 6725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 6735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 6745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) -- Make sure the pager is in EXCLUSIVE state. 6755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE def(d, e, f); 6765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def VALUES 6775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz'); 6785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 6795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 6805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 6815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 6825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 6835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 6845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 6855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM abc WHERE a = 4; 6865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 6875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # Execute a statement that does a statement rollback due to 6895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # a constraint failure. 6905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # 6915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 6925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO abc SELECT 10, 20, 30 FROM def; 6935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 6945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 6955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # Rollback the database. Verify that the state of the ABC table 6965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # is unchanged from the beginning of the transaction. In other words, 6975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # make sure the DELETE on table ABC that occurred within the transaction 6985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # had no effect. 6995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) # 7005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 7015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ROLLBACK; 7025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM abc; 7035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 7045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 2 3 4 5 6 7 8 9} 7055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)integrity_check conflict-11.2 7065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 7075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Repeat test conflict-11.1 but this time commit. 7085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 7095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-11.3 { 7105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 7115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 7125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) -- Make sure the pager is in EXCLUSIVE state. 7135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE abc SET a=a+1; 7145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE def(d, e, f); 7155821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def VALUES 7165821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz'); 7175821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7185821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7195821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7205821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7215821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7225821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7235821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7245821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM abc WHERE a = 4; 7255821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 7265821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 7275821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO abc SELECT 10, 20, 30 FROM def; 7285821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 7295821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 7305821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ROLLBACK; 7315821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM abc; 7325821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 7335821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 2 3 4 5 6 7 8 9} 7345821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Repeat test conflict-11.1 but this time commit. 7355821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 7365821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-11.5 { 7375821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 7385821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 7395821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) -- Make sure the pager is in EXCLUSIVE state. 7405821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE def(d, e, f); 7415821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def VALUES 7425821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz'); 7435821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7445821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7455821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7465821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7475821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7485821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7495821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO def SELECT * FROM def; 7505821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) DELETE FROM abc WHERE a = 4; 7515821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 7525821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 7535821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO abc SELECT 10, 20, 30 FROM def; 7545821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 7555821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 7565821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) COMMIT; 7575821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM abc; 7585821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 7595821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 2 3 7 8 9} 7605821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)integrity_check conflict-11.6 7615821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 7625821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Make sure UPDATE OR REPLACE works on tables that have only 7635821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# an INTEGER PRIMARY KEY. 7645821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 7655821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-12.1 { 7665821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 7675821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t5(a INTEGER PRIMARY KEY, b text); 7685821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t5 VALUES(1,'one'); 7695821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) INSERT INTO t5 VALUES(2,'two'); 7705821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t5 7715821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 7725821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 one 2 two} 7735821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-12.2 { 7745821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 7755821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE OR IGNORE t5 SET a=a+1 WHERE a=1; 7765821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t5; 7775821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 7785821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 one 2 two} 7795821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-12.3 { 7805821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 7815821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE t5 SET a=a+1 WHERE a=1; 7825821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 7835821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {PRIMARY KEY must be unique}} 7845821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-12.4 { 7855821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 7865821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) UPDATE OR REPLACE t5 SET a=a+1 WHERE a=1; 7875821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t5; 7885821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 7895821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {2 one} 7905821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 7915821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 7925821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# Ticket [c38baa3d969eab7946dc50ba9d9b4f0057a19437] 7935821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# REPLACE works like ABORT on a CHECK constraint. 7945821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)# 7955821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-13.1 { 7965821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 7975821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) CREATE TABLE t13(a CHECK(a!=2)); 7985821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) BEGIN; 7995821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) REPLACE INTO t13 VALUES(1); 8005821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 8015821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) catchsql { 8025821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) REPLACE INTO t13 VALUES(2); 8035821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 8045821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 {constraint failed}} 8055821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)do_test conflict-13.2 { 8065821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) execsql { 8075821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) REPLACE INTO t13 VALUES(3); 8085821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) COMMIT; 8095821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) SELECT * FROM t13; 8105821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) } 8115821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)} {1 3} 8125821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 8135821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles) 8145821806d5e7f356e8fa4b058a389a808ea183019Torne (Richard Coles)finish_test 815