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} 108 109test_reset 110do_common_sql { 111 CREATE TABLE t1(a PRIMARY KEY, b); 112 CREATE TABLE t2(a, b INTEGER PRIMARY KEY); 113 CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)); 114} 115 116# Execute each of the following blocks of SQL on database [db1]. Collect 117# changes using a session object. Apply the resulting changeset to 118# database [db2]. Then check that the contents of the two databases are 119# identical. 120# 121foreach {tn sql} { 122 1 { INSERT INTO t1 VALUES(1, 2) } 123 124 2 { 125 INSERT INTO t2 VALUES(1, NULL); 126 INSERT INTO t2 VALUES(2, NULL); 127 INSERT INTO t2 VALUES(3, NULL); 128 DELETE FROM t2 WHERE a = 2; 129 INSERT INTO t2 VALUES(4, NULL); 130 UPDATE t2 SET b=0 WHERE b=1; 131 } 132 133 3 { INSERT INTO t3 SELECT *, NULL FROM t2 } 134 135 4 { 136 INSERT INTO t3 SELECT a||a, b||b, NULL FROM t3; 137 DELETE FROM t3 WHERE rowid%2; 138 } 139 140 5 { UPDATE t3 SET c = a||b } 141 142 6 { UPDATE t1 SET a = 32 } 143 144 7 { 145 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 2 146 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 4 147 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 8 148 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 16 149 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 32 150 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 64 151 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 128 152 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 256 153 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 512 154 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 1024 155 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; -- 2048 156 DELETE FROM t1 WHERE (rowid%3)==0; 157 } 158 159 8 { 160 BEGIN; 161 INSERT INTO t1 SELECT randomblob(32), randomblob(32) FROM t1; 162 ROLLBACK; 163 } 164 9 { 165 BEGIN; 166 UPDATE t1 SET b = 'xxx'; 167 ROLLBACK; 168 } 169 10 { 170 BEGIN; 171 DELETE FROM t1 WHERE 1; 172 ROLLBACK; 173 } 174} { 175 do_then_apply_sql $sql 176 do_test 1.$tn { compare_db db db2 } {} 177} 178 179# The following block of tests is similar to the last, except that the 180# session object is recording changes made to an attached database. The 181# main database contains a table of the same name as the table being 182# modified within the attached db. 183# 184test_reset 185forcedelete test.db3 186sqlite3 db3 test.db3 187do_test 2.0 { 188 execsql { 189 ATTACH 'test.db3' AS 'aux'; 190 CREATE TABLE t1(a, b PRIMARY KEY); 191 CREATE TABLE t2(x, y, z); 192 CREATE TABLE t3(a); 193 194 CREATE TABLE aux.t1(a PRIMARY KEY, b); 195 CREATE TABLE aux.t2(a, b INTEGER PRIMARY KEY); 196 CREATE TABLE aux.t3(a, b, c, PRIMARY KEY(a, b)); 197 } 198 execsql { 199 CREATE TABLE t1(a PRIMARY KEY, b); 200 CREATE TABLE t2(a, b INTEGER PRIMARY KEY); 201 CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b)); 202 } db2 203} {} 204 205foreach {tn sql} { 206 1 { INSERT INTO aux.t1 VALUES(1, 2) } 207 208 2 { 209 INSERT INTO aux.t2 VALUES(1, NULL); 210 INSERT INTO aux.t2 VALUES(2, NULL); 211 INSERT INTO aux.t2 VALUES(3, NULL); 212 DELETE FROM aux.t2 WHERE a = 2; 213 INSERT INTO aux.t2 VALUES(4, NULL); 214 UPDATE aux.t2 SET b=0 WHERE b=1; 215 } 216 217 3 { INSERT INTO aux.t3 SELECT *, NULL FROM aux.t2 } 218 219 4 { 220 INSERT INTO aux.t3 SELECT a||a, b||b, NULL FROM aux.t3; 221 DELETE FROM aux.t3 WHERE rowid%2; 222 } 223 224 5 { UPDATE aux.t3 SET c = a||b } 225 226 6 { UPDATE aux.t1 SET a = 32 } 227 228 7 { 229 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 230 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 231 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 232 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 233 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 234 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 235 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 236 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 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 DELETE FROM aux.t1 WHERE (rowid%3)==0; 241 } 242 243 8 { 244 BEGIN; 245 INSERT INTO aux.t1 SELECT randomblob(32), randomblob(32) FROM aux.t1; 246 ROLLBACK; 247 } 248 9 { 249 BEGIN; 250 UPDATE aux.t1 SET b = 'xxx'; 251 ROLLBACK; 252 } 253 10 { 254 BEGIN; 255 DELETE FROM aux.t1 WHERE 1; 256 ROLLBACK; 257 } 258} { 259 do_then_apply_sql $sql aux 260 do_test 2.$tn { compare_db db3 db2 } {} 261} 262 263 264catch {db3 close} 265 266finish_test 267 268