# 2017 February 04 # # 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. # #*********************************************************************** # # Tests for the sessions module. Specifically, that a changeset can # be applied after ALTER TABLE ADD COLUMN has been used to add # columns to tables. # 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 sessionat db close sqlite3_shutdown test_sqlite3_log log proc log {code msg} { lappend ::log $code $msg } proc reset_test {} { catch { db close } catch { db2 close } forcedelete test.db test.db2 sqlite3 db test.db sqlite3 db2 test.db2 } # Run all tests in this file twice. Once with "WITHOUT ROWID", and once # with regular rowid tables. # # ?.1.*: Test that PK inconsistencies are detected if one or more of the PK # columns are not present in the changeset. # # ?.2.*: Test that it is not possible to apply a changeset with N columns # to a db with fewer than N columns. # # ?.3.*: Test some INSERT, UPDATE and DELETE operations that do not # require conflict handling. # # ?.4.*: Test some INSERT, UPDATE and DELETE operations that do require # conflict handling. # # ?.5.*: Test that attempting to concat two changesets with different # numbers of columns for the same table is an error. # foreach {tn trailing} { sessionat-ipk "" sessionat-wor " WITHOUT ROWID " } { eval [string map [list %WR% $trailing] { reset_test #----------------------------------------------------------------------- do_execsql_test $tn.1.0 { CREATE TABLE t1(a, b, PRIMARY KEY(a)) %WR%; } do_execsql_test -db db2 $tn.1.1 { CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)) %WR%; } do_test $tn.1.2 { set ::log {} do_then_apply_sql { INSERT INTO t1 VALUES('one', 'two') } set ::log } [list \ SQLITE_SCHEMA {sqlite3changeset_apply(): primary key mismatch for table t1} ] do_execsql_test $tn.1.3 { SELECT * FROM t1 } {one two} do_execsql_test -db db2 $tn.1.4 { SELECT * FROM t1 } {} #----------------------------------------------------------------------- do_execsql_test $tn.2.0 { CREATE TABLE t2(x, y, z, PRIMARY KEY(x)) %WR%; } do_execsql_test -db db2 $tn.2.1 { CREATE TABLE t2(x, y, PRIMARY KEY(x)) %WR%; } do_test $tn.2.2 { db cache flush set ::log {} do_then_apply_sql { INSERT INTO t2 VALUES(1, 2, 3) } set ::log } [list SQLITE_SCHEMA \ {sqlite3changeset_apply(): table t2 has 2 columns, expected 3 or more} ] do_execsql_test $tn.2.3 { SELECT * FROM t2 } {1 2 3} do_execsql_test -db db2 $tn.2.4 { SELECT * FROM t2 } {} #----------------------------------------------------------------------- do_execsql_test $tn.3.0 { CREATE TABLE t3(a, b, PRIMARY KEY(b)) %WR%; } do_execsql_test -db db2 $tn.3.1 { CREATE TABLE t3(a, b, c DEFAULT 'D', PRIMARY KEY(b)) %WR%; } do_test $tn.3.2 { do_then_apply_sql { INSERT INTO t3 VALUES(1, 2); INSERT INTO t3 VALUES(3, 4); INSERT INTO t3 VALUES(5, 6); }; db2 eval {SELECT * FROM t3} } {1 2 D 3 4 D 5 6 D} do_test $tn.3.3 { do_then_apply_sql { UPDATE t3 SET a=45 WHERE b=4; DELETE FROM t3 WHERE a=5; }; db2 eval {SELECT * FROM t3} } {1 2 D 45 4 D} #----------------------------------------------------------------------- # 4.1: INSERT statements # 4.2: DELETE statements # 4.3: UPDATE statements # do_execsql_test $tn.4.1.0 { CREATE TABLE t4(x INTEGER PRIMARY KEY, y) %WR%; } do_execsql_test -db db2 $tn.4.1.1 { CREATE TABLE t4(x INTEGER PRIMARY KEY, y, z) %WR%; INSERT INTO t4 VALUES(1, 2, 3); INSERT INTO t4 VALUES(4, 5, 6); } do_conflict_test $tn.4.1.2 -tables t4 -sql { INSERT INTO t4 VALUES(10, 20); INSERT INTO t4 VALUES(4, 11); } -conflicts { {INSERT t4 CONFLICT {i 4 i 11} {i 4 i 5}} } do_execsql_test -db db2 $tn.4.1.3 { SELECT * FROM t4 ORDER BY x } {1 2 3 4 5 6 10 20 {}} do_conflict_test $tn.4.1.4 -policy REPLACE -tables t4 -sql { INSERT INTO t4 VALUES(1, 11); } -conflicts { {INSERT t4 CONFLICT {i 1 i 11} {i 1 i 2}} } do_execsql_test -db db2 $tn.4.1.5 { SELECT * FROM t4 ORDER BY x } {1 11 {} 4 5 6 10 20 {}} do_execsql_test $tn.4.2.0 { DELETE FROM t4; INSERT INTO t4 VALUES(1, 'A'); INSERT INTO t4 VALUES(2, 'B'); INSERT INTO t4 VALUES(3, 'C'); INSERT INTO t4 VALUES(4, 'D'); } do_execsql_test -db db2 $tn.4.2.1 { DELETE FROM t4; INSERT INTO t4 VALUES(1, 'A', 'a'); INSERT INTO t4 VALUES(3, 'C', 'c'); INSERT INTO t4 VALUES(4, 'E', 'd'); } do_conflict_test $tn.4.2.2 -tables t4 -sql { DELETE FROM t4 WHERE x=2; DELETE FROM t4 WHERE x=4; } -conflicts { {DELETE t4 NOTFOUND {i 2 t B}} {DELETE t4 DATA {i 4 t D} {i 4 t E}} } do_execsql_test $tn.4.3.0 { CREATE TABLE t5(a, b, c PRIMARY KEY) %WR%; INSERT INTO t5 VALUES(1,1,1), (2,2,2), (3,3,3), (4,4,4); } do_execsql_test -db db2 $tn.4.3.1 { CREATE TABLE t5(a, b, c PRIMARY KEY, d CHECK(b!=10)) %WR%; INSERT INTO t5 VALUES (2,2,2,2), (3,8,3,3), (4,4,4,4); } do_conflict_test $tn.4.3.2 -tables t5 -sql { UPDATE t5 SET a=4 WHERE c=1; UPDATE t5 SET b=9 WHERE c=3; UPDATE t5 SET b=10 WHERE c=2; } -conflicts { {UPDATE t5 NOTFOUND {i 1 {} {} i 1} {i 4 {} {} {} {}}} {UPDATE t5 DATA {{} {} i 3 i 3} {{} {} i 9 {} {}} {i 3 i 8 i 3}} {UPDATE t5 CONSTRAINT {{} {} i 2 i 2} {{} {} i 10 {} {}}} } #----------------------------------------------------------------------- do_execsql_test $tn.5.0 { CREATE TABLE t6(a, b, c, PRIMARY KEY(a, b)) %WR%; } do_execsql_test -db db2 $tn.5.1 { CREATE TABLE t6(a, b, c, d, e, PRIMARY KEY(a, b)) %WR%; } do_test $tn.5.2 { set c1 [sql_exec_changeset db { INSERT INTO t6 VALUES(1, 1, 1); INSERT INTO t6 VALUES(2, 2, 2); }] set c2 [sql_exec_changeset db2 { INSERT INTO t6 VALUES(3, 3, 3, 3, 3); INSERT INTO t6 VALUES(4, 4, 4, 4, 4); }] list [catch { sqlite3changeset_concat $c1 $c2} msg] $msg } {1 SQLITE_SCHEMA} }] } finish_test