ADDED ext/session/session1.test Index: ext/session/session1.test ================================================================== --- /dev/null +++ ext/session/session1.test @@ -0,0 +1,448 @@ +# 2011 March 07 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +if {![info exists testdir]} { + set testdir [file join [file dirname [info script]] .. .. test] +} +source [file join [file dirname [info script]] session_common.tcl] +source $testdir/tester.tcl + +set testprefix session1 + +proc do_changeset_test {tn session res} { + set r [list] + foreach x $res {lappend r $x} + uplevel do_test $tn [list [subst -nocommands { + set x [list] + sqlite3session_foreach c [$session changeset] { lappend x [set c] } + set x + }]] [list $r] +} + +proc do_changeset_invert_test {tn session res} { + set r [list] + foreach x $res {lappend r $x} + uplevel do_test $tn [list [subst -nocommands { + set x [list] + set changeset [sqlite3changeset_invert [$session changeset]] + sqlite3session_foreach c [set changeset] { lappend x [set c] } + set x + }]] [list $r] +} + +do_execsql_test 1.0 { + CREATE TABLE t1(x PRIMARY KEY, y); + INSERT INTO t1 VALUES('abc', 'def'); +} + +#------------------------------------------------------------------------- +# Test creating, attaching tables to and deleting session objects. +# +do_test 1.1 { sqlite3session S db main } {S} +do_test 1.2 { S delete } {} +do_test 1.3 { sqlite3session S db main } {S} +do_test 1.4 { S attach t1 } {} +do_test 1.5 { S delete } {} +do_test 1.6 { sqlite3session S db main } {S} +do_test 1.7 { S attach t1 ; S attach t2 ; S attach t3 } {} +do_test 1.8 { S attach t1 ; S attach t2 ; S attach t3 } {} +do_test 1.9 { S delete } {} +do_test 1.10 { + sqlite3session S db main + S attach t1 + execsql { INSERT INTO t1 VALUES('ghi', 'jkl') } +} {} +do_test 1.11 { S delete } {} +do_test 1.12 { + sqlite3session S db main + S attach t1 + execsql { INSERT INTO t1 VALUES('mno', 'pqr') } + execsql { UPDATE t1 SET x = 111 WHERE rowid = 1 } + execsql { DELETE FROM t1 WHERE rowid = 2 } +} {} +do_test 1.13 { + S changeset + S delete +} {} + +#------------------------------------------------------------------------- +# Simple changeset tests. Also test the sqlite3changeset_invert() +# function. +# +do_test 2.1.1 { + execsql { DELETE FROM t1 } + sqlite3session S db main + S attach t1 + execsql { INSERT INTO t1 VALUES(1, 'Sukhothai') } + execsql { INSERT INTO t1 VALUES(2, 'Ayutthaya') } + execsql { INSERT INTO t1 VALUES(3, 'Thonburi') } +} {} +do_changeset_test 2.1.2 S { + {INSERT t1 {} {i 1 t Sukhothai}} + {INSERT t1 {} {i 2 t Ayutthaya}} + {INSERT t1 {} {i 3 t Thonburi}} +} +do_changeset_invert_test 2.1.3 S { + {DELETE t1 {i 1 t Sukhothai} {}} + {DELETE t1 {i 2 t Ayutthaya} {}} + {DELETE t1 {i 3 t Thonburi} {}} +} +do_test 2.1.4 { S delete } {} + +do_test 2.2.1 { + sqlite3session S db main + S attach t1 + execsql { DELETE FROM t1 WHERE 1 } +} {} +do_changeset_test 2.2.2 S { + {DELETE t1 {i 1 t Sukhothai} {}} + {DELETE t1 {i 2 t Ayutthaya} {}} + {DELETE t1 {i 3 t Thonburi} {}} +} +do_changeset_invert_test 2.2.3 S { + {INSERT t1 {} {i 1 t Sukhothai}} + {INSERT t1 {} {i 2 t Ayutthaya}} + {INSERT t1 {} {i 3 t Thonburi}} +} +do_test 2.2.4 { S delete } {} + +do_test 2.3.1 { + execsql { DELETE FROM t1 } + sqlite3session S db main + execsql { INSERT INTO t1 VALUES(1, 'Sukhothai') } + execsql { INSERT INTO t1 VALUES(2, 'Ayutthaya') } + execsql { INSERT INTO t1 VALUES(3, 'Thonburi') } + S attach t1 + execsql { + UPDATE t1 SET x = 10 WHERE x = 1; + UPDATE t1 SET y = 'Surin' WHERE x = 2; + UPDATE t1 SET x = 20, y = 'Thapae' WHERE x = 3; + } +} {} + +do_changeset_test 2.3.2 S { + {INSERT t1 {} {i 10 t Sukhothai}} + {DELETE t1 {i 1 t Sukhothai} {}} + {UPDATE t1 {i 2 t Ayutthaya} {{} {} t Surin}} + {DELETE t1 {i 3 t Thonburi} {}} + {INSERT t1 {} {i 20 t Thapae}} +} + +do_changeset_invert_test 2.3.3 S { + {DELETE t1 {i 10 t Sukhothai} {}} + {INSERT t1 {} {i 1 t Sukhothai}} + {UPDATE t1 {{} {} t Surin} {i 2 t Ayutthaya}} + {INSERT t1 {} {i 3 t Thonburi}} + {DELETE t1 {i 20 t Thapae} {}} +} +do_test 2.3.4 { S delete } {} + +do_test 2.4.1 { + sqlite3session S db main + S attach t1 + execsql { INSERT INTO t1 VALUES(100, 'Bangkok') } + execsql { DELETE FROM t1 WHERE x = 100 } +} {} +do_changeset_test 2.4.2 S {} +do_changeset_invert_test 2.4.3 S {} +do_test 2.4.4 { S delete } {} + +#------------------------------------------------------------------------- +# Test the application of simple changesets. These tests also test that +# the conflict callback is invoked correctly. For these tests, the +# conflict callback always returns OMIT. +# +db close +forcedelete test.db test.db2 +sqlite3 db test.db +sqlite3 db2 test.db2 + +proc xConflict {args} { + lappend ::xConflict $args + return "" +} + +proc bgerror {args} { set ::background_error $args } + +proc do_conflict_test {tn args} { + set O(-tables) [list] + set O(-sql) [list] + set O(-conflicts) [list] + + array set V $args + foreach key [array names V] { + if {![info exists O($key)]} {error "no such option: $key"} + } + array set O $args + + sqlite3session S db main + foreach t $O(-tables) { S attach $t } + execsql $O(-sql) + + set ::xConflict [list] + sqlite3changeset_apply db2 [S changeset] xConflict + + set conflicts [list] + foreach c $O(-conflicts) { + lappend conflicts $c + } + + after 1 {set go 1} + vwait go + + uplevel do_test $tn [list { set ::xConflict }] [list $conflicts] + S delete +} + +proc do_db2_test {testname sql {result {}}} { + uplevel do_test $testname [list "execsql {$sql} db2"] [list [list {*}$result]] +} + +# Test INSERT changesets. +# +do_test 3.1.0 { + execsql { CREATE TABLE t1(a PRIMARY KEY, b NOT NULL) } db2 + execsql { + CREATE TABLE t1(a PRIMARY KEY, b); + INSERT INTO t1 VALUES(1, 'one'); + INSERT INTO t1 VALUES(2, 'two'); + } db +} {} +do_db2_test 3.1.1 "INSERT INTO t1 VALUES(6, 'VI')" +do_conflict_test 3.1.2 -tables t1 -sql { + INSERT INTO t1 VALUES(3, 'three'); + INSERT INTO t1 VALUES(4, 'four'); + INSERT INTO t1 VALUES(5, 'five'); + INSERT INTO t1 VALUES(6, 'six'); + INSERT INTO t1 VALUES(7, 'seven'); + INSERT INTO t1 VALUES(8, NULL); +} -conflicts { + {INSERT t1 CONSTRAINT {i 8 n {}}} + {INSERT t1 CONFLICT {i 6 t six} {i 6 t VI}} +} + +do_db2_test 3.1.3 "SELECT * FROM t1" { + 6 VI 3 three 4 four 5 five 7 seven +} +do_execsql_test 3.1.4 "SELECT * FROM t1" { + 1 one 2 two 3 three 4 four 5 five 6 six 7 seven 8 {} +} + +# Test DELETE changesets. +# +do_execsql_test 3.2.1 { + PRAGMA foreign_keys = on; + CREATE TABLE t2(a PRIMARY KEY, b); + CREATE TABLE t3(c, d REFERENCES t2); + INSERT INTO t2 VALUES(1, 'one'); + INSERT INTO t2 VALUES(2, 'two'); + INSERT INTO t2 VALUES(3, 'three'); + INSERT INTO t2 VALUES(4, 'four'); +} +do_db2_test 3.2.2 { + PRAGMA foreign_keys = on; + CREATE TABLE t2(a PRIMARY KEY, b); + CREATE TABLE t3(c, d REFERENCES t2); + INSERT INTO t2 VALUES(1, 'one'); + INSERT INTO t2 VALUES(2, 'two'); + INSERT INTO t2 VALUES(4, 'five'); + INSERT INTO t3 VALUES('i', 1); +} +do_conflict_test 3.2.3 -tables t2 -sql { + DELETE FROM t2 WHERE a = 1; + DELETE FROM t2 WHERE a = 2; + DELETE FROM t2 WHERE a = 3; + DELETE FROM t2 WHERE a = 4; +} -conflicts { + {DELETE t2 CONSTRAINT {i 1 t one}} + {DELETE t2 NOTFOUND {i 3 t three}} + {DELETE t2 DATA {i 4 t four} {i 4 t five}} +} +do_execsql_test 3.2.4 "SELECT * FROM t2" {} +do_db2_test 3.2.5 "SELECT * FROM t2" {1 one 4 five} + +# Test UPDATE changesets. +# +do_execsql_test 3.3.1 { + CREATE TABLE t4(a, b, c, PRIMARY KEY(b, c)); + INSERT INTO t4 VALUES(1, 2, 3); + INSERT INTO t4 VALUES(4, 5, 6); + INSERT INTO t4 VALUES(7, 8, 9); + INSERT INTO t4 VALUES(10, 11, 12); +} +do_db2_test 3.3.2 { + CREATE TABLE t4(a NOT NULL, b, c, PRIMARY KEY(b, c)); + INSERT INTO t4 VALUES(0, 2, 3); + INSERT INTO t4 VALUES(4, 5, 7); + INSERT INTO t4 VALUES(7, 8, 9); + INSERT INTO t4 VALUES(10, 11, 12); +} +do_conflict_test 3.3.3 -tables t4 -sql { + UPDATE t4 SET a = -1 WHERE b = 2; + UPDATE t4 SET a = -1 WHERE b = 5; + UPDATE t4 SET a = NULL WHERE c = 9; + UPDATE t4 SET a = 'x' WHERE b = 11; +} -conflicts { + {UPDATE t4 CONSTRAINT {i 7 i 8 i 9} {n {} {} {} {} {}}} + {UPDATE t4 DATA {i 1 i 2 i 3} {i -1 {} {} {} {}} {i 0 i 2 i 3}} + {UPDATE t4 NOTFOUND {i 4 i 5 i 6} {i -1 {} {} {} {}}} +} +do_db2_test 3.3.4 { SELECT * FROM t4 } {0 2 3 4 5 7 7 8 9 x 11 12} +do_execsql_test 3.3.5 { SELECT * FROM t4 } {-1 2 3 -1 5 6 {} 8 9 x 11 12} + +#------------------------------------------------------------------------- +# This next block of tests verifies that values returned by the conflict +# handler are intepreted correctly. +# + +proc test_reset {} { + db close + db2 close + forcedelete test.db test.db2 + sqlite3 db test.db + sqlite3 db2 test.db2 +} + +proc xConflict {args} { + lappend ::xConflict $args + return $::conflict_return +} + +foreach {tn conflict_return after} { + 1 OMIT {1 2 value1 4 5 7 10 x x} + 2 REPLACE {1 2 value1 4 5 value2 10 8 9} +} { + test_reset + + do_test 4.$tn.1 { + foreach db {db db2} { + execsql { + CREATE TABLE t1(a, b, c, PRIMARY KEY(a)); + INSERT INTO t1 VALUES(1, 2, 3); + INSERT INTO t1 VALUES(4, 5, 6); + INSERT INTO t1 VALUES(7, 8, 9); + } $db + } + execsql { + REPLACE INTO t1 VALUES(4, 5, 7); + REPLACE INTO t1 VALUES(10, 'x', 'x'); + } db2 + } {} + + do_conflict_test 4.$tn.2 -tables t1 -sql { + UPDATE t1 SET c = 'value1' WHERE a = 1; -- no conflict + UPDATE t1 SET c = 'value2' WHERE a = 4; -- DATA conflict + UPDATE t1 SET a = 10 WHERE a = 7; -- CONFLICT conflict + } -conflicts { + {INSERT t1 CONFLICT {i 10 i 8 i 9} {i 10 t x t x}} + {UPDATE t1 DATA {i 4 {} {} i 6} {{} {} {} {} t value2} {i 4 i 5 i 7}} + } + + do_db2_test 4.$tn.3 "SELECT * FROM t1 ORDER BY a" $after +} + +foreach {tn conflict_return} { + 1 OMIT + 2 REPLACE +} { + test_reset + + do_test 5.$tn.1 { + # Create an identical schema in both databases. + set schema { + CREATE TABLE "'foolish name'"(x, y, z, PRIMARY KEY(x, y)); + } + execsql $schema db + execsql $schema db2 + + # Add some rows to [db2]. These rows will cause conflicts later + # on when the changeset from [db] is applied to it. + execsql { + INSERT INTO "'foolish name'" VALUES('one', 'one', 'ii'); + INSERT INTO "'foolish name'" VALUES('one', 'two', 'i'); + INSERT INTO "'foolish name'" VALUES('two', 'two', 'ii'); + } db2 + + } {} + + do_conflict_test 5.$tn.2 -tables {{'foolish name'}} -sql { + INSERT INTO "'foolish name'" VALUES('one', 'two', 2); + } -conflicts { + {INSERT {'foolish name'} CONFLICT {t one t two i 2} {t one t two t i}} + } + + set res(REPLACE) {one one ii one two 2 two two ii} + set res(OMIT) {one one ii one two i two two ii} + do_db2_test 5.$tn.3 { + SELECT * FROM "'foolish name'" ORDER BY x, y + } $res($conflict_return) + + + do_test 5.$tn.1 { + set schema { + CREATE TABLE d1("z""z" PRIMARY KEY, y); + INSERT INTO d1 VALUES(1, 'one'); + INSERT INTO d1 VALUES(2, 'two'); + } + execsql $schema db + execsql $schema db2 + + execsql { + UPDATE d1 SET y = 'TWO' WHERE "z""z" = 2; + } db2 + + } {} + + do_conflict_test 5.$tn.2 -tables d1 -sql { + DELETE FROM d1 WHERE "z""z" = 2; + } -conflicts { + {DELETE d1 DATA {i 2 t two} {i 2 t TWO}} + } + + set res(REPLACE) {1 one} + set res(OMIT) {1 one 2 TWO} + do_db2_test 5.$tn.3 "SELECT * FROM d1" $res($conflict_return) +} + +#------------------------------------------------------------------------- +# Test that two tables can be monitored by a single session object. +# +test_reset +set schema { + CREATE TABLE t1(a COLLATE nocase PRIMARY KEY, b); + CREATE TABLE t2(a, b PRIMARY KEY); +} +do_test 6.0 { + execsql $schema db + execsql $schema db2 + execsql { + INSERT INTO t1 VALUES('a', 'b'); + INSERT INTO t2 VALUES('a', 'b'); + } db2 +} {} + +set conflict_return "" +do_conflict_test 6.1 -tables {t1 t2} -sql { + INSERT INTO t1 VALUES('1', '2'); + INSERT INTO t1 VALUES('A', 'B'); + INSERT INTO t2 VALUES('A', 'B'); +} -conflicts { + {INSERT t1 CONFLICT {t A t B} {t a t b}} +} + +do_db2_test 6.2 "SELECT * FROM t1" {a b 1 2} +do_db2_test 6.3 "SELECT * FROM t2" {a b A B} + +catch { db2 close } +finish_test + DELETED test/session1.test Index: test/session1.test ================================================================== --- test/session1.test +++ /dev/null @@ -1,444 +0,0 @@ -# 2011 March 07 -# -# The author disclaims copyright to this source code. In place of -# a legal notice, here is a blessing: -# -# May you do good and not evil. -# May you find forgiveness for yourself and forgive others. -# May you share freely, never taking more than you give. -# -#*********************************************************************** -# This file implements regression tests for SQLite library. -# - -set testdir [file dirname $argv0] -source $testdir/tester.tcl -set ::testprefix session1 - -proc do_changeset_test {tn session res} { - set r [list] - foreach x $res {lappend r $x} - uplevel do_test $tn [list [subst -nocommands { - set x [list] - sqlite3session_foreach c [$session changeset] { lappend x [set c] } - set x - }]] [list $r] -} - -proc do_changeset_invert_test {tn session res} { - set r [list] - foreach x $res {lappend r $x} - uplevel do_test $tn [list [subst -nocommands { - set x [list] - set changeset [sqlite3changeset_invert [$session changeset]] - sqlite3session_foreach c [set changeset] { lappend x [set c] } - set x - }]] [list $r] -} - -do_execsql_test 1.0 { - CREATE TABLE t1(x PRIMARY KEY, y); - INSERT INTO t1 VALUES('abc', 'def'); -} - -#------------------------------------------------------------------------- -# Test creating, attaching tables to and deleting session objects. -# -do_test 1.1 { sqlite3session S db main } {S} -do_test 1.2 { S delete } {} -do_test 1.3 { sqlite3session S db main } {S} -do_test 1.4 { S attach t1 } {} -do_test 1.5 { S delete } {} -do_test 1.6 { sqlite3session S db main } {S} -do_test 1.7 { S attach t1 ; S attach t2 ; S attach t3 } {} -do_test 1.8 { S attach t1 ; S attach t2 ; S attach t3 } {} -do_test 1.9 { S delete } {} -do_test 1.10 { - sqlite3session S db main - S attach t1 - execsql { INSERT INTO t1 VALUES('ghi', 'jkl') } -} {} -do_test 1.11 { S delete } {} -do_test 1.12 { - sqlite3session S db main - S attach t1 - execsql { INSERT INTO t1 VALUES('mno', 'pqr') } - execsql { UPDATE t1 SET x = 111 WHERE rowid = 1 } - execsql { DELETE FROM t1 WHERE rowid = 2 } -} {} -do_test 1.13 { - S changeset - S delete -} {} - -#------------------------------------------------------------------------- -# Simple changeset tests. Also test the sqlite3changeset_invert() -# function. -# -do_test 2.1.1 { - execsql { DELETE FROM t1 } - sqlite3session S db main - S attach t1 - execsql { INSERT INTO t1 VALUES(1, 'Sukhothai') } - execsql { INSERT INTO t1 VALUES(2, 'Ayutthaya') } - execsql { INSERT INTO t1 VALUES(3, 'Thonburi') } -} {} -do_changeset_test 2.1.2 S { - {INSERT t1 {} {i 1 t Sukhothai}} - {INSERT t1 {} {i 2 t Ayutthaya}} - {INSERT t1 {} {i 3 t Thonburi}} -} -do_changeset_invert_test 2.1.3 S { - {DELETE t1 {i 1 t Sukhothai} {}} - {DELETE t1 {i 2 t Ayutthaya} {}} - {DELETE t1 {i 3 t Thonburi} {}} -} -do_test 2.1.4 { S delete } {} - -do_test 2.2.1 { - sqlite3session S db main - S attach t1 - execsql { DELETE FROM t1 WHERE 1 } -} {} -do_changeset_test 2.2.2 S { - {DELETE t1 {i 1 t Sukhothai} {}} - {DELETE t1 {i 2 t Ayutthaya} {}} - {DELETE t1 {i 3 t Thonburi} {}} -} -do_changeset_invert_test 2.2.3 S { - {INSERT t1 {} {i 1 t Sukhothai}} - {INSERT t1 {} {i 2 t Ayutthaya}} - {INSERT t1 {} {i 3 t Thonburi}} -} -do_test 2.2.4 { S delete } {} - -do_test 2.3.1 { - execsql { DELETE FROM t1 } - sqlite3session S db main - execsql { INSERT INTO t1 VALUES(1, 'Sukhothai') } - execsql { INSERT INTO t1 VALUES(2, 'Ayutthaya') } - execsql { INSERT INTO t1 VALUES(3, 'Thonburi') } - S attach t1 - execsql { - UPDATE t1 SET x = 10 WHERE x = 1; - UPDATE t1 SET y = 'Surin' WHERE x = 2; - UPDATE t1 SET x = 20, y = 'Thapae' WHERE x = 3; - } -} {} - -do_changeset_test 2.3.2 S { - {INSERT t1 {} {i 10 t Sukhothai}} - {DELETE t1 {i 1 t Sukhothai} {}} - {UPDATE t1 {i 2 t Ayutthaya} {{} {} t Surin}} - {DELETE t1 {i 3 t Thonburi} {}} - {INSERT t1 {} {i 20 t Thapae}} -} - -do_changeset_invert_test 2.3.3 S { - {DELETE t1 {i 10 t Sukhothai} {}} - {INSERT t1 {} {i 1 t Sukhothai}} - {UPDATE t1 {{} {} t Surin} {i 2 t Ayutthaya}} - {INSERT t1 {} {i 3 t Thonburi}} - {DELETE t1 {i 20 t Thapae} {}} -} -do_test 2.3.4 { S delete } {} - -do_test 2.4.1 { - sqlite3session S db main - S attach t1 - execsql { INSERT INTO t1 VALUES(100, 'Bangkok') } - execsql { DELETE FROM t1 WHERE x = 100 } -} {} -do_changeset_test 2.4.2 S {} -do_changeset_invert_test 2.4.3 S {} -do_test 2.4.4 { S delete } {} - -#------------------------------------------------------------------------- -# Test the application of simple changesets. These tests also test that -# the conflict callback is invoked correctly. For these tests, the -# conflict callback always returns OMIT. -# -db close -forcedelete test.db test.db2 -sqlite3 db test.db -sqlite3 db2 test.db2 - -proc xConflict {args} { - lappend ::xConflict $args - return "" -} - -proc bgerror {args} { set ::background_error $args } - -proc do_conflict_test {tn args} { - set O(-tables) [list] - set O(-sql) [list] - set O(-conflicts) [list] - - array set V $args - foreach key [array names V] { - if {![info exists O($key)]} {error "no such option: $key"} - } - array set O $args - - sqlite3session S db main - foreach t $O(-tables) { S attach $t } - execsql $O(-sql) - - set ::xConflict [list] - sqlite3changeset_apply db2 [S changeset] xConflict - - set conflicts [list] - foreach c $O(-conflicts) { - lappend conflicts $c - } - - after 1 {set go 1} - vwait go - - uplevel do_test $tn [list { set ::xConflict }] [list $conflicts] - S delete -} - -proc do_db2_test {testname sql {result {}}} { - uplevel do_test $testname [list "execsql {$sql} db2"] [list [list {*}$result]] -} - -# Test INSERT changesets. -# -do_test 3.1.0 { - execsql { CREATE TABLE t1(a PRIMARY KEY, b NOT NULL) } db2 - execsql { - CREATE TABLE t1(a PRIMARY KEY, b); - INSERT INTO t1 VALUES(1, 'one'); - INSERT INTO t1 VALUES(2, 'two'); - } db -} {} -do_db2_test 3.1.1 "INSERT INTO t1 VALUES(6, 'VI')" -do_conflict_test 3.1.2 -tables t1 -sql { - INSERT INTO t1 VALUES(3, 'three'); - INSERT INTO t1 VALUES(4, 'four'); - INSERT INTO t1 VALUES(5, 'five'); - INSERT INTO t1 VALUES(6, 'six'); - INSERT INTO t1 VALUES(7, 'seven'); - INSERT INTO t1 VALUES(8, NULL); -} -conflicts { - {INSERT t1 CONSTRAINT {i 8 n {}}} - {INSERT t1 CONFLICT {i 6 t six} {i 6 t VI}} -} - -do_db2_test 3.1.3 "SELECT * FROM t1" { - 6 VI 3 three 4 four 5 five 7 seven -} -do_execsql_test 3.1.4 "SELECT * FROM t1" { - 1 one 2 two 3 three 4 four 5 five 6 six 7 seven 8 {} -} - -# Test DELETE changesets. -# -do_execsql_test 3.2.1 { - PRAGMA foreign_keys = on; - CREATE TABLE t2(a PRIMARY KEY, b); - CREATE TABLE t3(c, d REFERENCES t2); - INSERT INTO t2 VALUES(1, 'one'); - INSERT INTO t2 VALUES(2, 'two'); - INSERT INTO t2 VALUES(3, 'three'); - INSERT INTO t2 VALUES(4, 'four'); -} -do_db2_test 3.2.2 { - PRAGMA foreign_keys = on; - CREATE TABLE t2(a PRIMARY KEY, b); - CREATE TABLE t3(c, d REFERENCES t2); - INSERT INTO t2 VALUES(1, 'one'); - INSERT INTO t2 VALUES(2, 'two'); - INSERT INTO t2 VALUES(4, 'five'); - INSERT INTO t3 VALUES('i', 1); -} -do_conflict_test 3.2.3 -tables t2 -sql { - DELETE FROM t2 WHERE a = 1; - DELETE FROM t2 WHERE a = 2; - DELETE FROM t2 WHERE a = 3; - DELETE FROM t2 WHERE a = 4; -} -conflicts { - {DELETE t2 CONSTRAINT {i 1 t one}} - {DELETE t2 NOTFOUND {i 3 t three}} - {DELETE t2 DATA {i 4 t four} {i 4 t five}} -} -do_execsql_test 3.2.4 "SELECT * FROM t2" {} -do_db2_test 3.2.5 "SELECT * FROM t2" {1 one 4 five} - -# Test UPDATE changesets. -# -do_execsql_test 3.3.1 { - CREATE TABLE t4(a, b, c, PRIMARY KEY(b, c)); - INSERT INTO t4 VALUES(1, 2, 3); - INSERT INTO t4 VALUES(4, 5, 6); - INSERT INTO t4 VALUES(7, 8, 9); - INSERT INTO t4 VALUES(10, 11, 12); -} -do_db2_test 3.3.2 { - CREATE TABLE t4(a NOT NULL, b, c, PRIMARY KEY(b, c)); - INSERT INTO t4 VALUES(0, 2, 3); - INSERT INTO t4 VALUES(4, 5, 7); - INSERT INTO t4 VALUES(7, 8, 9); - INSERT INTO t4 VALUES(10, 11, 12); -} -do_conflict_test 3.3.3 -tables t4 -sql { - UPDATE t4 SET a = -1 WHERE b = 2; - UPDATE t4 SET a = -1 WHERE b = 5; - UPDATE t4 SET a = NULL WHERE c = 9; - UPDATE t4 SET a = 'x' WHERE b = 11; -} -conflicts { - {UPDATE t4 CONSTRAINT {i 7 i 8 i 9} {n {} {} {} {} {}}} - {UPDATE t4 DATA {i 1 i 2 i 3} {i -1 {} {} {} {}} {i 0 i 2 i 3}} - {UPDATE t4 NOTFOUND {i 4 i 5 i 6} {i -1 {} {} {} {}}} -} -do_db2_test 3.3.4 { SELECT * FROM t4 } {0 2 3 4 5 7 7 8 9 x 11 12} -do_execsql_test 3.3.5 { SELECT * FROM t4 } {-1 2 3 -1 5 6 {} 8 9 x 11 12} - -#------------------------------------------------------------------------- -# This next block of tests verifies that values returned by the conflict -# handler are intepreted correctly. -# - -proc test_reset {} { - db close - db2 close - forcedelete test.db test.db2 - sqlite3 db test.db - sqlite3 db2 test.db2 -} - -proc xConflict {args} { - lappend ::xConflict $args - return $::conflict_return -} - -foreach {tn conflict_return after} { - 1 OMIT {1 2 value1 4 5 7 10 x x} - 2 REPLACE {1 2 value1 4 5 value2 10 8 9} -} { - test_reset - - do_test 4.$tn.1 { - foreach db {db db2} { - execsql { - CREATE TABLE t1(a, b, c, PRIMARY KEY(a)); - INSERT INTO t1 VALUES(1, 2, 3); - INSERT INTO t1 VALUES(4, 5, 6); - INSERT INTO t1 VALUES(7, 8, 9); - } $db - } - execsql { - REPLACE INTO t1 VALUES(4, 5, 7); - REPLACE INTO t1 VALUES(10, 'x', 'x'); - } db2 - } {} - - do_conflict_test 4.$tn.2 -tables t1 -sql { - UPDATE t1 SET c = 'value1' WHERE a = 1; -- no conflict - UPDATE t1 SET c = 'value2' WHERE a = 4; -- DATA conflict - UPDATE t1 SET a = 10 WHERE a = 7; -- CONFLICT conflict - } -conflicts { - {INSERT t1 CONFLICT {i 10 i 8 i 9} {i 10 t x t x}} - {UPDATE t1 DATA {i 4 {} {} i 6} {{} {} {} {} t value2} {i 4 i 5 i 7}} - } - - do_db2_test 4.$tn.3 "SELECT * FROM t1 ORDER BY a" $after -} - -foreach {tn conflict_return} { - 1 OMIT - 2 REPLACE -} { - test_reset - - do_test 5.$tn.1 { - # Create an identical schema in both databases. - set schema { - CREATE TABLE "'foolish name'"(x, y, z, PRIMARY KEY(x, y)); - } - execsql $schema db - execsql $schema db2 - - # Add some rows to [db2]. These rows will cause conflicts later - # on when the changeset from [db] is applied to it. - execsql { - INSERT INTO "'foolish name'" VALUES('one', 'one', 'ii'); - INSERT INTO "'foolish name'" VALUES('one', 'two', 'i'); - INSERT INTO "'foolish name'" VALUES('two', 'two', 'ii'); - } db2 - - } {} - - do_conflict_test 5.$tn.2 -tables {{'foolish name'}} -sql { - INSERT INTO "'foolish name'" VALUES('one', 'two', 2); - } -conflicts { - {INSERT {'foolish name'} CONFLICT {t one t two i 2} {t one t two t i}} - } - - set res(REPLACE) {one one ii one two 2 two two ii} - set res(OMIT) {one one ii one two i two two ii} - do_db2_test 5.$tn.3 { - SELECT * FROM "'foolish name'" ORDER BY x, y - } $res($conflict_return) - - - do_test 5.$tn.1 { - set schema { - CREATE TABLE d1("z""z" PRIMARY KEY, y); - INSERT INTO d1 VALUES(1, 'one'); - INSERT INTO d1 VALUES(2, 'two'); - } - execsql $schema db - execsql $schema db2 - - execsql { - UPDATE d1 SET y = 'TWO' WHERE "z""z" = 2; - } db2 - - } {} - - do_conflict_test 5.$tn.2 -tables d1 -sql { - DELETE FROM d1 WHERE "z""z" = 2; - } -conflicts { - {DELETE d1 DATA {i 2 t two} {i 2 t TWO}} - } - - set res(REPLACE) {1 one} - set res(OMIT) {1 one 2 TWO} - do_db2_test 5.$tn.3 "SELECT * FROM d1" $res($conflict_return) -} - -#------------------------------------------------------------------------- -# Test that two tables can be monitored by a single session object. -# -test_reset -set schema { - CREATE TABLE t1(a COLLATE nocase PRIMARY KEY, b); - CREATE TABLE t2(a, b PRIMARY KEY); -} -do_test 6.0 { - execsql $schema db - execsql $schema db2 - execsql { - INSERT INTO t1 VALUES('a', 'b'); - INSERT INTO t2 VALUES('a', 'b'); - } db2 -} {} - -set conflict_return "" -do_conflict_test 6.1 -tables {t1 t2} -sql { - INSERT INTO t1 VALUES('1', '2'); - INSERT INTO t1 VALUES('A', 'B'); - INSERT INTO t2 VALUES('A', 'B'); -} -conflicts { - {INSERT t1 CONFLICT {t A t B} {t a t b}} -} - -do_db2_test 6.2 "SELECT * FROM t1" {a b 1 2} -do_db2_test 6.3 "SELECT * FROM t2" {a b A B} - -catch { db2 close } -finish_test -