1# 2017 February 04 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# 12# Tests for the sessions module. Specifically, that a changeset can 13# be applied after ALTER TABLE ADD COLUMN has been used to add 14# columns to tables. 15# 16 17if {![info exists testdir]} { 18 set testdir [file join [file dirname [info script]] .. .. test] 19} 20source [file join [file dirname [info script]] session_common.tcl] 21source $testdir/tester.tcl 22ifcapable !session {finish_test; return} 23 24set testprefix sessionat 25 26db close 27sqlite3_shutdown 28test_sqlite3_log log 29proc log {code msg} { lappend ::log $code $msg } 30 31proc reset_test {} { 32 catch { db close } 33 catch { db2 close } 34 forcedelete test.db test.db2 35 sqlite3 db test.db 36 sqlite3 db2 test.db2 37} 38 39 40# Run all tests in this file twice. Once with "WITHOUT ROWID", and once 41# with regular rowid tables. 42# 43# ?.1.*: Test that PK inconsistencies are detected if one or more of the PK 44# columns are not present in the changeset. 45# 46# ?.2.*: Test that it is not possible to apply a changeset with N columns 47# to a db with fewer than N columns. 48# 49# ?.3.*: Test some INSERT, UPDATE and DELETE operations that do not 50# require conflict handling. 51# 52# ?.4.*: Test some INSERT, UPDATE and DELETE operations that do require 53# conflict handling. 54# 55# ?.5.*: Test that attempting to concat two changesets with different 56# numbers of columns for the same table is an error. 57# 58foreach {tn trailing} { 59 sessionat-ipk "" 60 sessionat-wor " WITHOUT ROWID " 61} { 62eval [string map [list %WR% $trailing] { 63 reset_test 64 65 #----------------------------------------------------------------------- 66 do_execsql_test $tn.1.0 { 67 CREATE TABLE t1(a, b, PRIMARY KEY(a)) %WR%; 68 } 69 do_execsql_test -db db2 $tn.1.1 { 70 CREATE TABLE t1(a, b, c, PRIMARY KEY(a, c)) %WR%; 71 } 72 do_test $tn.1.2 { 73 set ::log {} 74 do_then_apply_sql { INSERT INTO t1 VALUES('one', 'two') } 75 set ::log 76 } [list \ 77 SQLITE_SCHEMA {sqlite3changeset_apply(): primary key mismatch for table t1} 78 ] 79 do_execsql_test $tn.1.3 { SELECT * FROM t1 } {one two} 80 do_execsql_test -db db2 $tn.1.4 { SELECT * FROM t1 } {} 81 82 #----------------------------------------------------------------------- 83 do_execsql_test $tn.2.0 { 84 CREATE TABLE t2(x, y, z, PRIMARY KEY(x)) %WR%; 85 } 86 do_execsql_test -db db2 $tn.2.1 { 87 CREATE TABLE t2(x, y, PRIMARY KEY(x)) %WR%; 88 } 89 do_test $tn.2.2 { 90 db cache flush 91 set ::log {} 92 do_then_apply_sql { INSERT INTO t2 VALUES(1, 2, 3) } 93 set ::log 94 } [list SQLITE_SCHEMA \ 95 {sqlite3changeset_apply(): table t2 has 2 columns, expected 3 or more} 96 ] 97 do_execsql_test $tn.2.3 { SELECT * FROM t2 } {1 2 3} 98 do_execsql_test -db db2 $tn.2.4 { SELECT * FROM t2 } {} 99 100 #----------------------------------------------------------------------- 101 do_execsql_test $tn.3.0 { 102 CREATE TABLE t3(a, b, PRIMARY KEY(b)) %WR%; 103 } 104 do_execsql_test -db db2 $tn.3.1 { 105 CREATE TABLE t3(a, b, c DEFAULT 'D', PRIMARY KEY(b)) %WR%; 106 } 107 do_test $tn.3.2 { 108 do_then_apply_sql { 109 INSERT INTO t3 VALUES(1, 2); 110 INSERT INTO t3 VALUES(3, 4); 111 INSERT INTO t3 VALUES(5, 6); 112 }; 113 db2 eval {SELECT * FROM t3} 114 } {1 2 D 3 4 D 5 6 D} 115 do_test $tn.3.3 { 116 do_then_apply_sql { 117 UPDATE t3 SET a=45 WHERE b=4; 118 DELETE FROM t3 WHERE a=5; 119 }; 120 db2 eval {SELECT * FROM t3} 121 } {1 2 D 45 4 D} 122 123 #----------------------------------------------------------------------- 124 # 4.1: INSERT statements 125 # 4.2: DELETE statements 126 # 4.3: UPDATE statements 127 # 128 do_execsql_test $tn.4.1.0 { 129 CREATE TABLE t4(x INTEGER PRIMARY KEY, y) %WR%; 130 } 131 do_execsql_test -db db2 $tn.4.1.1 { 132 CREATE TABLE t4(x INTEGER PRIMARY KEY, y, z) %WR%; 133 INSERT INTO t4 VALUES(1, 2, 3); 134 INSERT INTO t4 VALUES(4, 5, 6); 135 } 136 do_conflict_test $tn.4.1.2 -tables t4 -sql { 137 INSERT INTO t4 VALUES(10, 20); 138 INSERT INTO t4 VALUES(4, 11); 139 } -conflicts { 140 {INSERT t4 CONFLICT {i 4 i 11} {i 4 i 5}} 141 } 142 do_execsql_test -db db2 $tn.4.1.3 { 143 SELECT * FROM t4 ORDER BY x 144 } {1 2 3 4 5 6 10 20 {}} 145 do_conflict_test $tn.4.1.4 -policy REPLACE -tables t4 -sql { 146 INSERT INTO t4 VALUES(1, 11); 147 } -conflicts { 148 {INSERT t4 CONFLICT {i 1 i 11} {i 1 i 2}} 149 } 150 do_execsql_test -db db2 $tn.4.1.5 { 151 SELECT * FROM t4 ORDER BY x 152 } {1 11 {} 4 5 6 10 20 {}} 153 154 do_execsql_test $tn.4.2.0 { 155 DELETE FROM t4; 156 INSERT INTO t4 VALUES(1, 'A'); 157 INSERT INTO t4 VALUES(2, 'B'); 158 INSERT INTO t4 VALUES(3, 'C'); 159 INSERT INTO t4 VALUES(4, 'D'); 160 } 161 do_execsql_test -db db2 $tn.4.2.1 { 162 DELETE FROM t4; 163 INSERT INTO t4 VALUES(1, 'A', 'a'); 164 INSERT INTO t4 VALUES(3, 'C', 'c'); 165 INSERT INTO t4 VALUES(4, 'E', 'd'); 166 } 167 do_conflict_test $tn.4.2.2 -tables t4 -sql { 168 DELETE FROM t4 WHERE x=2; 169 DELETE FROM t4 WHERE x=4; 170 } -conflicts { 171 {DELETE t4 NOTFOUND {i 2 t B}} 172 {DELETE t4 DATA {i 4 t D} {i 4 t E}} 173 } 174 175 do_execsql_test $tn.4.3.0 { 176 CREATE TABLE t5(a, b, c PRIMARY KEY) %WR%; 177 INSERT INTO t5 VALUES(1,1,1), (2,2,2), (3,3,3), (4,4,4); 178 } 179 do_execsql_test -db db2 $tn.4.3.1 { 180 CREATE TABLE t5(a, b, c PRIMARY KEY, d CHECK(b!=10)) %WR%; 181 INSERT INTO t5 VALUES (2,2,2,2), (3,8,3,3), (4,4,4,4); 182 } 183 do_conflict_test $tn.4.3.2 -tables t5 -sql { 184 UPDATE t5 SET a=4 WHERE c=1; 185 UPDATE t5 SET b=9 WHERE c=3; 186 UPDATE t5 SET b=10 WHERE c=2; 187 } -conflicts { 188 {UPDATE t5 NOTFOUND {i 1 {} {} i 1} {i 4 {} {} {} {}}} 189 {UPDATE t5 DATA {{} {} i 3 i 3} {{} {} i 9 {} {}} {i 3 i 8 i 3}} 190 {UPDATE t5 CONSTRAINT {{} {} i 2 i 2} {{} {} i 10 {} {}}} 191 } 192 193 #----------------------------------------------------------------------- 194 do_execsql_test $tn.5.0 { 195 CREATE TABLE t6(a, b, c, PRIMARY KEY(a, b)) %WR%; 196 } 197 do_execsql_test -db db2 $tn.5.1 { 198 CREATE TABLE t6(a, b, c, d, e, PRIMARY KEY(a, b)) %WR%; 199 } 200 do_test $tn.5.2 { 201 set c1 [sql_exec_changeset db { 202 INSERT INTO t6 VALUES(1, 1, 1); 203 INSERT INTO t6 VALUES(2, 2, 2); 204 }] 205 set c2 [sql_exec_changeset db2 { 206 INSERT INTO t6 VALUES(3, 3, 3, 3, 3); 207 INSERT INTO t6 VALUES(4, 4, 4, 4, 4); 208 }] 209 list [catch { sqlite3changeset_concat $c1 $c2} msg] $msg 210 } {1 SQLITE_SCHEMA} 211 212 #----------------------------------------------------------------------- 213 db2 close 214 sqlite3 db2 test.db 215 do_execsql_test $tn.6.0 { 216 CREATE TABLE t7(a INTEGER PRIMARY KEY, b) %WR%; 217 INSERT INTO t7 VALUES(1, 1); 218 INSERT INTO t7 VALUES(2, 2); 219 INSERT INTO t7 VALUES(3, 3); 220 } 221 222 do_test $tn.6.1 { 223 set c1 [sql_exec_changeset db { 224 INSERT INTO t7 VALUES(4, 4); 225 DELETE FROM t7 WHERE a=1; 226 UPDATE t7 SET b=222 WHERE a=2; 227 }] 228 set cinv [sqlite3changeset_invert $c1] 229 execsql { SELECT * FROM t7 } 230 } {2 222 3 3 4 4} 231 232 do_execsql_test -db db2 $tn.6.2 { 233 ALTER TABLE t7 ADD COLUMN c DEFAULT 'ccc' 234 } 235 236 proc xConfict {args} { return "OMIT" } 237 do_test $tn.6.3 { 238 sqlite3changeset_apply db $cinv xConflict 239 execsql { SELECT * FROM t7 } 240 } {1 1 ccc 2 2 ccc 3 3 ccc} 241}] 242} 243 244catch { db close } 245catch { db2 close } 246sqlite3_shutdown 247test_sqlite3_log 248 249finish_test 250