1# 2011 April 13 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# This file implements regression tests for the session module. 12# Specifically, for the sqlite3changeset_concat() command. 13# 14 15if {![info exists testdir]} { 16 set testdir [file join [file dirname [info script]] .. .. test] 17} 18source [file join [file dirname [info script]] session_common.tcl] 19source $testdir/tester.tcl 20ifcapable !session {finish_test; return} 21 22set testprefix session5 23 24# Organization of tests: 25# 26# session5-1.*: Simple tests to check the concat() function produces 27# correct results. 28# 29# session5-2.*: More complicated tests. 30# 31# session5-3.*: Schema mismatch errors. 32# 33 34proc do_concat_test {tn args} { 35 36 set subtest 0 37 foreach sql $args { 38 incr subtest 39 sqlite3session S db main ; S attach * 40 execsql $sql 41 42 set c [S changeset] 43 if {[info commands s_prev] != ""} { 44 set c_concat [sqlite3changeset_concat $c_prev $c] 45 set c_two [s_prev changeset] 46 s_prev delete 47 48 set h_concat [changeset_to_list $c_concat] 49 set h_two [changeset_to_list $c_two] 50 51 do_test $tn.$subtest [list set {} $h_concat] $h_two 52 } 53 set c_prev $c 54 rename S s_prev 55 } 56 57 catch { s_prev delete } 58} 59 60#------------------------------------------------------------------------- 61# Test cases session5-1.* - simple tests. 62# 63do_execsql_test 1.0 { 64 CREATE TABLE t1(a PRIMARY KEY, b); 65} 66 67do_concat_test 1.1.1 { 68 INSERT INTO t1 VALUES(1, 'one'); 69} { 70 INSERT INTO t1 VALUES(2, 'two'); 71} 72 73do_concat_test 1.1.2 { 74 UPDATE t1 SET b = 'five' WHERE a = 1; 75} { 76 UPDATE t1 SET b = 'six' WHERE a = 2; 77} 78 79do_concat_test 1.1.3 { 80 DELETE FROM t1 WHERE a = 1; 81} { 82 DELETE FROM t1 WHERE a = 2; 83} 84 85 86# 1.2.1: INSERT + DELETE -> (none) 87# 1.2.2: INSERT + UPDATE -> INSERT 88# 89# 1.2.3: DELETE + INSERT (matching data) -> (none) 90# 1.2.4: DELETE + INSERT (non-matching data) -> UPDATE 91# 92# 1.2.5: UPDATE + UPDATE (matching data) -> (none) 93# 1.2.6: UPDATE + UPDATE (non-matching data) -> UPDATE 94# 1.2.7: UPDATE + DELETE -> DELETE 95# 96do_concat_test 1.2.1 { 97 INSERT INTO t1 VALUES('x', 'y'); 98} { 99 DELETE FROM t1 WHERE a = 'x'; 100} 101do_concat_test 1.2.2 { 102 INSERT INTO t1 VALUES(5.0, 'five'); 103} { 104 UPDATE t1 SET b = 'six' WHERE a = 5.0; 105} 106 107do_execsql_test 1.2.3.1 "INSERT INTO t1 VALUES('I', 'one')" 108do_concat_test 1.2.3.2 { 109 DELETE FROM t1 WHERE a = 'I'; 110} { 111 INSERT INTO t1 VALUES('I', 'one'); 112} 113do_concat_test 1.2.4 { 114 DELETE FROM t1 WHERE a = 'I'; 115} { 116 INSERT INTO t1 VALUES('I', 'two'); 117} 118do_concat_test 1.2.5 { 119 UPDATE t1 SET b = 'five' WHERE a = 'I'; 120} { 121 UPDATE t1 SET b = 'two' WHERE a = 'I'; 122} 123do_concat_test 1.2.6 { 124 UPDATE t1 SET b = 'six' WHERE a = 'I'; 125} { 126 UPDATE t1 SET b = 'seven' WHERE a = 'I'; 127} 128do_concat_test 1.2.7 { 129 UPDATE t1 SET b = 'eight' WHERE a = 'I'; 130} { 131 DELETE FROM t1 WHERE a = 'I'; 132} 133 134 135#------------------------------------------------------------------------- 136# Test cases session5-2.* - more complex tests. 137# 138db function indirect indirect 139proc indirect {{x -1}} { 140 S indirect $x 141 s_prev indirect $x 142} 143do_concat_test 2.1 { 144 CREATE TABLE abc(a, b, c PRIMARY KEY); 145 INSERT INTO abc VALUES(NULL, NULL, 1); 146 INSERT INTO abc VALUES('abcdefghijkl', NULL, 2); 147} { 148 DELETE FROM abc WHERE c = 1; 149 UPDATE abc SET c = 1 WHERE c = 2; 150} { 151 INSERT INTO abc VALUES('abcdefghijkl', NULL, 2); 152 INSERT INTO abc VALUES(1.0, 2.0, 3); 153} { 154 UPDATE abc SET a = a-1; 155} { 156 CREATE TABLE def(d, e, f, PRIMARY KEY(e, f)); 157 INSERT INTO def VALUES('x', randomblob(11000), 67); 158 INSERT INTO def SELECT d, e, f+1 FROM def; 159 INSERT INTO def SELECT d, e, f+2 FROM def; 160 INSERT INTO def SELECT d, e, f+4 FROM def; 161} { 162 DELETE FROM def WHERE rowid>4; 163} { 164 INSERT INTO def SELECT d, e, f+4 FROM def; 165} { 166 INSERT INTO abc VALUES(22, 44, -1); 167} { 168 UPDATE abc SET c=-2 WHERE c=-1; 169 UPDATE abc SET c=-3 WHERE c=-2; 170} { 171 UPDATE abc SET c=-4 WHERE c=-3; 172} { 173 UPDATE abc SET a=a+1 WHERE c=-3; 174 UPDATE abc SET a=a+1 WHERE c=-3; 175} { 176 UPDATE abc SET a=a+1 WHERE c=-3; 177 UPDATE abc SET a=a+1 WHERE c=-3; 178} { 179 INSERT INTO abc VALUES('one', 'two', 'three'); 180} { 181 SELECT indirect(1); 182 UPDATE abc SET a='one point five' WHERE c = 'three'; 183} { 184 SELECT indirect(0); 185 UPDATE abc SET a='one point six' WHERE c = 'three'; 186} { 187 CREATE TABLE x1(a, b, PRIMARY KEY(a)); 188 SELECT indirect(1); 189 INSERT INTO x1 VALUES(1, 2); 190} { 191 SELECT indirect(1); 192 UPDATE x1 SET b = 3 WHERE a = 1; 193} 194 195catch {db close} 196forcedelete test.db 197sqlite3 db test.db 198do_concat_test 2.2 { 199 CREATE TABLE t1(a, b, PRIMARY KEY(b)); 200 CREATE TABLE t2(a PRIMARY KEY, b); 201 INSERT INTO t1 VALUES('string', 1); 202 INSERT INTO t1 VALUES(4, 2); 203 INSERT INTO t1 VALUES(X'FFAAFFAAFFAA', 3); 204} { 205 INSERT INTO t2 VALUES('one', 'two'); 206 INSERT INTO t2 VALUES(1, NULL); 207 UPDATE t1 SET a = 5 WHERE a = 2; 208} { 209 DELETE FROM t2 WHERE a = 1; 210 UPDATE t1 SET a = 4 WHERE a = 2; 211 INSERT INTO t2 VALUES('x', 'y'); 212} 213 214do_test 2.3.0 { 215 catch {db close} 216 forcedelete test.db 217 sqlite3 db test.db 218 219 set sql1 "" 220 set sql2 "" 221 for {set i 1} {$i < 120} {incr i} { 222 append sql1 "INSERT INTO x1 VALUES($i*4, $i);" 223 } 224 for {set i 1} {$i < 120} {incr i} { 225 append sql2 "DELETE FROM x1 WHERE a = $i*4;" 226 } 227 228 set {} {} 229} {} 230do_concat_test 2.3 { 231 CREATE TABLE x1(a PRIMARY KEY, b) 232} $sql1 $sql2 $sql1 $sql2 233 234do_concat_test 2.4 { 235 CREATE TABLE x2(a PRIMARY KEY, b); 236 CREATE TABLE x3(a PRIMARY KEY, b); 237 238 INSERT INTO x2 VALUES('a', 'b'); 239 INSERT INTO x2 VALUES('x', 'y'); 240 INSERT INTO x3 VALUES('a', 'b'); 241} { 242 INSERT INTO x2 VALUES('c', 'd'); 243 INSERT INTO x3 VALUES('e', 'f'); 244 INSERT INTO x3 VALUES('x', 'y'); 245} 246 247do_concat_test 2.5 { 248 UPDATE x3 SET b = 'Y' WHERE a = 'x' 249} { 250 DELETE FROM x3 WHERE a = 'x' 251} { 252 DELETE FROM x2 WHERE a = 'a' 253} { 254 INSERT INTO x2 VALUES('a', 'B'); 255} 256 257 258#------------------------------------------------------------------------- 259# Test that schema incompatibilities are detected correctly. 260# 261# session5-3.1: Incompatible number of columns. 262# session5-3.2: Incompatible PK definition. 263# 264 265do_test 3.1 { 266 db close 267 forcedelete test.db 268 sqlite3 db test.db 269 270 execsql { CREATE TABLE t1(a PRIMARY KEY, b) } 271 set c1 [changeset_from_sql { INSERT INTO t1 VALUES(1, 2) }] 272 execsql { 273 DROP TABLE t1; 274 CREATE TABLE t1(a PRIMARY KEY, b, c); 275 } 276 set c2 [changeset_from_sql { INSERT INTO t1 VALUES(2, 3, 4) }] 277 278 list [catch { sqlite3changeset_concat $c1 $c2 } msg] $msg 279} {1 SQLITE_SCHEMA} 280 281do_test 3.2 { 282 db close 283 forcedelete test.db 284 sqlite3 db test.db 285 286 execsql { CREATE TABLE t1(a PRIMARY KEY, b) } 287 set c1 [changeset_from_sql { INSERT INTO t1 VALUES(1, 2) }] 288 execsql { 289 DROP TABLE t1; 290 CREATE TABLE t1(a, b PRIMARY KEY); 291 } 292 set c2 [changeset_from_sql { INSERT INTO t1 VALUES(2, 3) }] 293 294 list [catch { sqlite3changeset_concat $c1 $c2 } msg] $msg 295} {1 SQLITE_SCHEMA} 296 297 298finish_test 299