# 2016 March 30 # # 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 the sessions module. # Specifically, it tests that UNIQUE constraints are dealt with correctly. # 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 ifcapable !session {finish_test; return} set testprefix sessionG forcedelete test.db2 sqlite3 db2 test.db2 do_test 1.0 { do_common_sql { CREATE TABLE t1(a PRIMARY KEY, b UNIQUE); INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two'); INSERT INTO t1 VALUES(3, 'three'); } do_then_apply_sql { DELETE FROM t1 WHERE a=1; INSERT INTO t1 VALUES(4, 'one'); } compare_db db db2 } {} do_test 1.1 { do_then_apply_sql { DELETE FROM t1 WHERE a=4; INSERT INTO t1 VALUES(1, 'one'); } compare_db db db2 } {} do_test 1.2 { execsql { INSERT INTO t1 VALUES(5, 'five') } db2 do_then_apply_sql { INSERT INTO t1 VALUES(11, 'eleven'); INSERT INTO t1 VALUES(12, 'five'); } execsql { SELECT * FROM t1 } db2 } {2 two 3 three 1 one 5 five 11 eleven} do_test 1.3 { execsql { SELECT * FROM t1 } } {2 two 3 three 1 one 11 eleven 12 five} #------------------------------------------------------------------------- # reset_db db2 close forcedelete test.db2 sqlite3 db2 test.db2 do_test 2.1 { do_common_sql { CREATE TABLE t1(a PRIMARY KEY, b UNIQUE, c UNIQUE); INSERT INTO t1 VALUES(1, 1, 1); INSERT INTO t1 VALUES(2, 2, 2); INSERT INTO t1 VALUES(3, 3, 3); } } {} do_test 2.2.1 { # It is not possible to apply the changeset generated by the following # SQL, as none of the three updated rows may be updated as part of the # first pass. do_then_apply_sql { UPDATE t1 SET b=0 WHERE a=1; UPDATE t1 SET b=1 WHERE a=2; UPDATE t1 SET b=2 WHERE a=3; UPDATE t1 SET b=3 WHERE a=1; } db2 eval { SELECT a, b FROM t1 } } {1 1 2 2 3 3} do_test 2.2.2 { db eval { SELECT a, b FROM t1 } } {1 3 2 1 3 2} #------------------------------------------------------------------------- # reset_db db2 close forcedelete test.db2 sqlite3 db2 test.db2 do_test 3.1 { do_common_sql { CREATE TABLE t1(a PRIMARY KEY, b UNIQUE, c UNIQUE); INSERT INTO t1 VALUES(1, 1, 1); INSERT INTO t1 VALUES(2, 2, 2); INSERT INTO t1 VALUES(3, 3, 3); } } {} do_test 3.3 { do_then_apply_sql { UPDATE t1 SET b=4 WHERE a=3; UPDATE t1 SET b=3 WHERE a=2; UPDATE t1 SET b=2 WHERE a=1; } compare_db db db2 } {} do_test 3.4 { do_then_apply_sql { UPDATE t1 SET b=1 WHERE a=1; UPDATE t1 SET b=2 WHERE a=2; UPDATE t1 SET b=3 WHERE a=3; } compare_db db db2 } {} #------------------------------------------------------------------------- # reset_db db2 close forcedelete test.db2 sqlite3 db2 test.db2 do_test 4.1 { do_common_sql { CREATE TABLE t1(a PRIMARY KEY, b UNIQUE); INSERT INTO t1 VALUES(1, 1); INSERT INTO t1 VALUES(2, 2); INSERT INTO t1 VALUES(3, 3); CREATE TABLE t2(a PRIMARY KEY, b UNIQUE); INSERT INTO t2 VALUES(1, 1); INSERT INTO t2 VALUES(2, 2); INSERT INTO t2 VALUES(3, 3); } } {} do_test 4.2 { do_then_apply_sql { UPDATE t1 SET b=4 WHERE a=3; UPDATE t1 SET b=3 WHERE a=2; UPDATE t1 SET b=2 WHERE a=1; UPDATE t2 SET b=0 WHERE a=1; UPDATE t2 SET b=1 WHERE a=2; UPDATE t2 SET b=2 WHERE a=3; } compare_db db db2 } {} do_test 4.3 { do_then_apply_sql { UPDATE t1 SET b=1 WHERE a=1; UPDATE t1 SET b=2 WHERE a=2; UPDATE t1 SET b=3 WHERE a=3; UPDATE t2 SET b=3 WHERE a=3; UPDATE t2 SET b=2 WHERE a=2; UPDATE t2 SET b=1 WHERE a=1; } compare_db db db2 } {} #------------------------------------------------------------------------- reset_db catch { db2 close } forcedelete test.db2 sqlite3 db2 test.db2 do_execsql_test 5.0.1 { CREATE TABLE t1(a PRIMARY KEY, b, c); CREATE TABLE t2(a, b, c PRIMARY KEY); CREATE TABLE t3(a, b PRIMARY KEY, c); } do_execsql_test -db db2 5.0.2 { CREATE TABLE t1(a PRIMARY KEY, b, c); CREATE TABLE t2(a, b, c); CREATE TABLE t3(a, b PRIMARY KEY, c); } do_test 5.1 { do_then_apply_sql { INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t2 VALUES(4, 5, 6); INSERT INTO t3 VALUES(7, 8, 9); } db2 eval { SELECT * FROM t1; SELECT * FROM t2; SELECT * FROM t3; } } {1 2 3 7 8 9} #------------------------------------------------------------------------- reset_db db func number_name number_name do_execsql_test 6.0 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b); CREATE UNIQUE INDEX t1b ON t1(b); WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000 ) INSERT INTO t1 SELECT i, number_name(i) FROM s; } do_test 6.1 { db eval BEGIN set ::C [changeset_from_sql { DELETE FROM t1; WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000 ) INSERT INTO t1 SELECT i, number_name(i+1) FROM s; }] db eval ROLLBACK execsql { SELECT count(*) FROM t1 WHERE number_name(a) IS NOT b } } {0} proc xConflict {args} { exit ; return "OMIT" } do_test 6.2 { sqlite3changeset_apply db $C xConflict } {} do_execsql_test 6.3 { SELECT count(*) FROM t1; } {1000} do_execsql_test 6.4 { SELECT count(*) FROM t1 WHERE number_name(a+1) IS NOT b; } {0} # db eval { SELECT * FROM t1 } { puts "$a || $b" } finish_test