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