1# 2015-07-31 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 [sqldiff --rbu] command. 13# 14# 15if {![info exists testdir]} { 16 set testdir [file join [file dirname [info script]] .. .. test] 17} 18source $testdir/tester.tcl 19set testprefix rbudiff 20 21set PROG [test_find_sqldiff] 22db close 23 24proc get_rbudiff_sql {db1 db2} { 25 exec $::PROG --rbu $db1 $db2 26} 27 28proc get_vtab_rbudiff_sql {db1 db2} { 29 exec $::PROG --vtab --rbu $db1 $db2 30} 31 32proc step_rbu {target rbu} { 33 while 1 { 34 sqlite3rbu rbu $target $rbu 35 set rc [rbu step] 36 rbu close 37 if {$rc != "SQLITE_OK"} break 38 } 39 set rc 40} 41 42proc apply_rbudiff {sql target} { 43 test_rbucount $sql 44 forcedelete rbu.db 45 sqlite3 rbudb rbu.db 46 rbudb eval $sql 47 rbudb close 48 step_rbu $target rbu.db 49} 50 51proc sqlesc {id} { 52 set ret "'[string map {' ''} $id]'" 53 set ret 54} 55 56# The only argument is the output of an [sqldiff -rbu] run. This command 57# tests that the contents of the rbu_count table is correct. An exception 58# is thrown if it is not. 59# 60proc test_rbucount {sql} { 61 sqlite3 tmpdb "" 62 tmpdb eval $sql 63 tmpdb eval { 64 SELECT name FROM sqlite_master WHERE name LIKE 'data%' AND type='table' 65 } { 66 set a [tmpdb eval "SELECT count(*) FROM [sqlesc $name]"] 67 set b [tmpdb eval {SELECT cnt FROM rbu_count WHERE tbl = $name}] 68 if {$a != $b} { 69 tmpdb close 70 error "rbu_count error - tbl = $name" 71 } 72 } 73 tmpdb close 74 return "" 75} 76 77proc rbudiff_cksum {db1} { 78 set txt "" 79 80 sqlite3 dbtmp $db1 81 foreach tbl [dbtmp eval {SELECT name FROM sqlite_master WHERE type='table'}] { 82 set cols [list] 83 dbtmp eval "PRAGMA table_info = [sqlesc $tbl]" { 84 lappend cols "quote( $name )" 85 } 86 append txt [dbtmp eval \ 87 "SELECT [join $cols {||'.'||}] FROM [sqlesc $tbl] ORDER BY 1" 88 ] 89 } 90 dbtmp close 91 92 md5 $txt 93} 94 95foreach {tn init mod} { 96 1 { 97 CREATE TABLE t1(a PRIMARY KEY, b, c); 98 INSERT INTO t1 VALUES(1, 2, 3); 99 INSERT INTO t1 VALUES(4, 5, 6); 100 101 CREATE TABLE t2(a, b, c, PRIMARY KEY(b, c)); 102 INSERT INTO t2 VALUES(1, 2, 3); 103 INSERT INTO t2 VALUES(4, 5, 6); 104 } { 105 INSERT INTO t1 VALUES(7, 8, 9); 106 DELETE FROM t1 WHERE a=4; 107 UPDATE t1 SET c = 11 WHERE a = 1; 108 109 INSERT INTO t2 VALUES(7, 8, 9); 110 DELETE FROM t2 WHERE a=4; 111 UPDATE t2 SET c = 11 WHERE a = 1; 112 } 113 114 2 { 115 CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b, c)); 116 INSERT INTO t1 VALUES('u', 'v', 'w'); 117 INSERT INTO t1 VALUES('x', 'y', 'z'); 118 } { 119 DELETE FROM t1 WHERE a='u'; 120 INSERT INTO t1 VALUES('a', 'b', 'c'); 121 } 122 123 3 { 124 CREATE TABLE t1(i INTEGER PRIMARY KEY, x); 125 INSERT INTO t1 VALUES(1, 126 X'0000000000000000111111111111111122222222222222223333333333333333' 127 ); 128 CREATE TABLE t2(y INTEGER PRIMARY KEY, x); 129 INSERT INTO t2 VALUES(1, 130 X'0000000000000000111111111111111122222222222222223333333333333333' 131 ); 132 } { 133 DELETE FROM t1; 134 INSERT INTO t1 VALUES(1, 135 X'0000000000000000111111111111111122222555555552223333333333333333' 136 ); 137 DELETE FROM t2; 138 INSERT INTO t2 VALUES(1, 139 X'0000000000000000111111111111111122222222222222223333333FFF333333' 140 ); 141 } 142 143 4 { 144 CREATE TABLE x1(a, b, c, PRIMARY KEY(a, b, c)); 145 INSERT INTO x1 VALUES('u', 'v', NULL); 146 INSERT INTO x1 VALUES('x', 'y', 'z'); 147 INSERT INTO x1 VALUES('a', NULL, 'b'); 148 } { 149 INSERT INTO x1 VALUES('a', 'b', 'c'); 150 } 151 152 5 { 153 CREATE TABLE t1(a PRIMARY KEY, b); 154 INSERT INTO t1 VALUES(1, NULL); 155 INSERT INTO t1 VALUES(2, X''); 156 } { 157 UPDATE t1 SET b = X'' WHERE a=1; 158 UPDATE t1 SET b = NULL WHERE a=2; 159 } 160 161} { 162 catch { db close } 163 164 forcedelete test.db test.db2 165 sqlite3 db test.db 166 db eval "$init" 167 sqlite3 db test.db2 168 db eval "$init ; $mod" 169 db close 170 171 do_test 1.$tn.2 { 172 set sql [get_rbudiff_sql test.db test.db2] 173 apply_rbudiff $sql test.db 174 } {SQLITE_DONE} 175 do_test 1.$tn.3 { rbudiff_cksum test.db } [rbudiff_cksum test.db2] 176 177 forcedelete test.db test.db2 178 sqlite3 db test.db 179 db eval "$init ; $mod" 180 sqlite3 db test.db2 181 db eval "$init" 182 db close 183 184 do_test 1.$tn.4 { 185 set sql [get_rbudiff_sql test.db test.db2] 186 apply_rbudiff $sql test.db 187 } {SQLITE_DONE} 188 do_test 1.$tn.5 { rbudiff_cksum test.db } [rbudiff_cksum test.db2] 189} 190 191#------------------------------------------------------------------------- 192# Test that if the --vtab switch is present, [sqldiff] handles virtual 193# table types fts[345] and rtree correctly. 194# 195ifcapable fts3&&fts5&&rtree { 196 197foreach {tn init mod} { 198 1 { 199 CREATE VIRTUAL TABLE t1 USING fts5(c); 200 INSERT INTO t1 VALUES('a b c'); 201 INSERT INTO t1 VALUES('a b c'); 202 } { 203 DELETE FROM t1 WHERE rowid = 1; 204 INSERT INTO t1 VALUES('a b c'); 205 } 206 207 2 { 208 CREATE VIRTUAL TABLE "x y" USING 'rtree'(id, x1, x2); 209 INSERT INTO "x y" VALUES(1, 2, 3); 210 INSERT INTO "x y" VALUES(2, 4, 6); 211 } { 212 DELETE FROM "x y" WHERE rowid = 1; 213 INSERT INTO "x y" VALUES(3, 6, 9); 214 } 215 216 3 { 217 CREATE VIRTUAL TABLE 'x''y' USING fts3; 218 INSERT INTO 'x''y' VALUES('one two three'); 219 INSERT INTO 'x''y' VALUES('four five six'); 220 } { 221 DELETE FROM 'x''y' WHERE rowid = 1; 222 INSERT INTO 'x''y' VALUES('one two three'); 223 } 224} { 225 226 forcedelete test.db test.db2 227 sqlite3 db test.db 228 db eval "$init" 229 sqlite3 db test.db2 230 db eval "$init ; $mod" 231 db close 232 233 do_test 2.$tn.1 { 234 set sql [get_vtab_rbudiff_sql test.db test.db2] 235 apply_rbudiff $sql test.db 236 } {SQLITE_DONE} 237 do_test 2.$tn.2 { rbudiff_cksum test.db } [rbudiff_cksum test.db2] 238} 239 240} 241 242ifcapable fts5 { 243 foreach {tn init mod} { 244 1 { 245 CREATE VIRTUAL TABLE t1 USING fts5(c); 246 INSERT INTO t1 VALUES('a b c'); 247 INSERT INTO t1 VALUES('a b c'); 248 } { 249 DELETE FROM t1 WHERE rowid = 1; 250 INSERT INTO t1 VALUES('a b c'); 251 } 252 253 2 { 254 CREATE VIRTUAL TABLE t1 USING FTs5(c); 255 INSERT INTO t1 VALUES('a b c'); 256 INSERT INTO t1 VALUES('a b c'); 257 } { 258 DELETE FROM t1 WHERE rowid = 1; 259 INSERT INTO t1 VALUES('a b c'); 260 } 261 262 3 { 263 creAte virTUal 264tablE t1 USING FTs5(c); 265 INSERT INTO t1 VALUES('a b c'); 266 INSERT INTO t1 VALUES('a b c'); 267 } { 268 DELETE FROM t1 WHERE rowid = 1; 269 INSERT INTO t1 VALUES('a b c'); 270 } 271 4 { 272 creAte virTUal tablE t1 USING FTs5(c); 273 INSERT INTO t1 VALUES('a b c'); 274 INSERT INTO t1 VALUES('a b c'); 275 } { 276 DELETE FROM t1 WHERE rowid = 1; 277 INSERT INTO t1 VALUES('a b c'); 278 } 279 280 } { 281 forcedelete test.db test.db2 282 sqlite3 db test.db 283 db eval "$init" 284 sqlite3 db test.db2 285 db eval "$init ; $mod" 286 db eval { INSERT INTO t1(t1) VALUES('optimize') } 287 db close 288 289 do_test 3.$tn.1 { 290 set sql [get_vtab_rbudiff_sql test.db test.db2] 291 apply_rbudiff $sql test.db 292 } {SQLITE_DONE} 293 294 sqlite3 db test.db 295 sqlite3 db2 test.db2 296 do_test 3.$tn.2 { 297 db2 eval { SELECT * FROM t1 ORDER BY rowid } 298 } [db eval { SELECT * FROM t1 ORDER BY rowid }] 299 300 do_test 3.$tn.3 { 301 db2 eval { INSERT INTO t1(t1) VALUES('integrity-check') } 302 } {} 303 304 db close 305 db2 close 306 } 307} 308 309 310finish_test 311