1# 2011 Mar 16 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# The focus of this file is testing the session module. 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 20 21set testprefix session2 22 23proc test_reset {} { 24 catch { db close } 25 catch { db2 close } 26 forcedelete test.db test.db2 27 sqlite3 db test.db 28 sqlite3 db2 test.db2 29} 30 31proc do_common_sql {sql} { 32 execsql $sql db 33 execsql $sql db2 34} 35proc xConflict args { return "OMIT" } 36 37proc do_then_apply_sql {sql {dbname main}} { 38 39 sqlite3session S db $dbname 40 db eval "SELECT name FROM $dbname.sqlite_master WHERE type = 'table'" { 41 S attach $name 42 } 43 44 db eval $sql 45 sqlite3changeset_apply db2 [S changeset] xConflict 46 S delete 47} 48 49proc do_iterator_test {tn tbl_list sql res} { 50 sqlite3session S db main 51 foreach t $tbl_list {S attach $t} 52 execsql $sql 53 54 set r [list] 55 foreach v $res { lappend r $v } 56 57 set x [list] 58 sqlite3session_foreach c [S changeset] { lappend x $c } 59 uplevel do_test $tn [list [list set {} $x]] [list $r] 60 61 S delete 62} 63 64# Compare the contents of all tables in [db1] and [db2]. Throw an error if 65# they are not identical, or return an empty string if they are. 66# 67proc compare_db {db1 db2} { 68 69 set sql {SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name} 70 set lot1 [$db1 eval $sql] 71 set lot2 [$db2 eval $sql] 72 73 if {$lot1 != $lot2} { error "databases contain different tables" } 74 75 foreach tbl $lot1 { 76 set col1 [list] 77 set col2 [list] 78 79 $db1 eval "PRAGMA table_info = $tbl" { lappend col1 $name } 80 $db2 eval "PRAGMA table_info = $tbl" { lappend col2 $name } 81 if {$col1 != $col2} { error "table $tbl schema mismatch" } 82 83 set sql "SELECT * FROM $tbl ORDER BY [join $col1 ,]" 84 set data1 [$db1 eval $sql] 85 set data2 [$db2 eval $sql] 86 if {$data1 != $data2} { error "table $tbl data mismatch" } 87 } 88 89 return "" 90} 91 92########################################################################## 93# End of proc definitions. Start of tests. 94########################################################################## 95 96test_reset 97do_execsql_test 1.0 { 98 CREATE TABLE t1(a PRIMARY KEY, b); 99 INSERT INTO t1 VALUES('i', 'one'); 100} 101do_iterator_test 1.1 t1 { 102 DELETE FROM t1 WHERE a = 'i'; 103 INSERT INTO t1 VALUES('ii', 'two'); 104} { 105 {DELETE t1 {t i t one} {}} 106 {INSERT t1 {} {t ii t two}} 107} 108do_iterator_test 1.1 t1 { 109 INSERT INTO t1 VALUES(1.5, 99.9) 110} { 111 {INSERT t1 {} {f 1.5 f 99.9}} 112} 113 114test_reset 115do_common_sql { 116 CREATE TABLE t1(a PRIMARY KEY, b); 117 CREATE TABLE t2(a, b INTEGER PRIMARY KEY); 118 CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)); 119} 120 121# Execute each of the following blocks of SQL on database [db1]. Collect 122# changes using a session object. Apply the resulting changeset to 123# database [db2]. Then check that the contents of the two databases are 124# identical. 125# 126foreach {tn sql} { 127 1 { INSERT INTO t1 VALUES(1, 2) } 128 129 2 { 130 INSERT INTO t2 VALUES(1, NULL); 131 INSERT INTO t2 VALUES(2, NULL); 132 INSERT INTO t2 VALUES(3, NULL); 133 DELETE FROM t2 WHERE a = 2; 134 INSERT INTO t2 VALUES(4, NULL); 135 UPDATE t2 SET b=0 WHERE b=1; 136 } 137 138 3 { INSERT INTO t3 SELECT *, NULL FROM t2 } 139 140 4 { 141 INSERT INTO t3 SELECT a||a, b||b, NULL FROM t3; 142 DELETE FROM t3 WHERE rowid%2; 143 } 144 145 5 { UPDATE t3 SET c = a||b } 146 147 6 { UPDATE t1 SET a = 32 } 148 149 7 { 150 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 2 151 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 4 152 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 8 153 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 16 154 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 32 155 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 64 156 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 128 157 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 256 158 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 512 159 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 1024 160 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 2048 161 DELETE FROM t1 WHERE (rowid%3)==0; 162 } 163 164 8 { 165 BEGIN; 166 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; 167 ROLLBACK; 168 } 169 9 { 170 BEGIN; 171 UPDATE t1 SET b = 'xxx'; 172 ROLLBACK; 173 } 174 10 { 175 BEGIN; 176 DELETE FROM t1 WHERE 1; 177 ROLLBACK; 178 } 179} { 180 do_then_apply_sql $sql 181 do_test 1.$tn { compare_db db db2 } {} 182} 183 184# The following block of tests is similar to the last, except that the 185# session object is recording changes made to an attached database. The 186# main database contains a table of the same name as the table being 187# modified within the attached db. 188# 189test_reset 190forcedelete test.db3 191sqlite3 db3 test.db3 192do_test 2.0 { 193 execsql { 194 ATTACH 'test.db3' AS 'aux'; 195 CREATE TABLE t1(a, b PRIMARY KEY); 196 CREATE TABLE t2(x, y, z); 197 CREATE TABLE t3(a); 198 199 CREATE TABLE aux.t1(a PRIMARY KEY, b); 200 CREATE TABLE aux.t2(a, b INTEGER PRIMARY KEY); 201 CREATE TABLE aux.t3(a, b, c, PRIMARY KEY(a, b)); 202 } 203 execsql { 204 CREATE TABLE t1(a PRIMARY KEY, b); 205 CREATE TABLE t2(a, b INTEGER PRIMARY KEY); 206 CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)); 207 } db2 208} {} 209 210proc xTrace {args} { puts $args } 211 212foreach {tn sql} { 213 214 1 { INSERT INTO aux.t1 VALUES(1, 2) } 215 216 2 { 217 INSERT INTO aux.t2 VALUES(1, NULL); 218 INSERT INTO aux.t2 VALUES(2, NULL); 219 INSERT INTO aux.t2 VALUES(3, NULL); 220 DELETE FROM aux.t2 WHERE a = 2; 221 INSERT INTO aux.t2 VALUES(4, NULL); 222 UPDATE aux.t2 SET b=0 WHERE b=1; 223 } 224 225 3 { INSERT INTO aux.t3 SELECT *, NULL FROM aux.t2 } 226 227 4 { 228 INSERT INTO aux.t3 SELECT a||a, b||b, NULL FROM aux.t3; 229 DELETE FROM aux.t3 WHERE rowid%2; 230 } 231 232 5 { UPDATE aux.t3 SET c = a||b } 233 234 6 { UPDATE aux.t1 SET a = 32 } 235 236 7 { 237 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 238 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 239 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 240 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 241 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 242 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 243 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 244 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 245 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 246 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 247 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 248 DELETE FROM aux.t1 WHERE (rowid%3)==0; 249 } 250 251 8 { 252 BEGIN; 253 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 254 ROLLBACK; 255 } 256 9 { 257 BEGIN; 258 UPDATE aux.t1 SET b = 'xxx'; 259 ROLLBACK; 260 } 261 10 { 262 BEGIN; 263 DELETE FROM aux.t1 WHERE 1; 264 ROLLBACK; 265 } 266 11 { 267 INSERT INTO aux.t1 VALUES(randomblob(21000), randomblob(0)); 268 INSERT INTO aux.t1 VALUES(1.5, 1.5); 269 INSERT INTO aux.t1 VALUES(4.56, -99.999999999999999999999); 270 } 271 272} { 273 do_then_apply_sql $sql aux 274 do_test 2.$tn { compare_db db3 db2 } {} 275} 276 277 278catch {db3 close} 279 280finish_test 281 282