1# 2016 March 18 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 13source [file join [file dirname [info script]] rbu_common.tcl] 14set ::testprefix rbuprogress 15 16 17proc create_db_file {filename sql} { 18 forcedelete $filename 19 sqlite3 tmpdb $filename 20 tmpdb eval $sql 21 tmpdb close 22} 23 24# Create a simple RBU database. That expects to write to a table: 25# 26# CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 27# 28proc create_rbu1 {filename} { 29 create_db_file $filename { 30 CREATE TABLE data_t1(a, b, c, rbu_control); 31 INSERT INTO data_t1 VALUES(1, 2, 3, 0); 32 INSERT INTO data_t1 VALUES(2, 'two', 'three', 0); 33 INSERT INTO data_t1 VALUES(3, NULL, 8.2, 0); 34 35 CREATE TABLE rbu_count(tbl, cnt); 36 INSERT INTO rbu_count VALUES('data_t1', 3); 37 } 38 return $filename 39} 40 41 42do_execsql_test 1.0 { 43 PRAGMA page_size = 4096; 44 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 45} 46 47do_test 1.1 { 48 create_rbu1 rbu.db 49 sqlite3rbu rbu test.db rbu.db 50 rbu bp_progress 51} {0 0} 52do_test 1.2 { rbu step ; rbu bp_progress } {3333 0} 53do_test 1.3 { rbu step ; rbu bp_progress } {6666 0} 54do_test 1.4 { rbu step ; rbu bp_progress } {10000 0} 55do_test 1.5 { rbu step ; rbu bp_progress } {10000 0} 56do_test 1.6 { rbu step ; rbu bp_progress } {10000 0} 57do_test 1.7 { rbu step ; rbu bp_progress } {10000 5000} 58do_test 1.8 { rbu step ; rbu bp_progress } {10000 10000} 59do_test 1.9 { rbu step ; rbu bp_progress } {10000 10000} 60 61do_test 1.10 { 62 rbu close 63} {SQLITE_DONE} 64 65#------------------------------------------------------------------------- 66# 67proc do_sp_test {tn bReopen target rbu reslist} { 68 uplevel [list do_test $tn [subst -nocommands { 69 if {$bReopen==0} { sqlite3rbu rbu $target $rbu } 70 set res [list] 71 while 1 { 72 if {$bReopen} { sqlite3rbu rbu $target $rbu } 73 set rc [rbu step] 74 if {[set rc] != "SQLITE_OK"} { rbu close ; error "error 1" } 75 lappend res [lindex [rbu bp_progress] 0] 76 if {[lindex [set res] end]==10000} break 77 if {$bReopen} { rbu close } 78 } 79 if {[set res] != [list $reslist]} { 80 rbu close 81 error "1. reslist incorrect (expect=$reslist got=[set res])" 82 } 83 84 # One step to clean up the temporary tables used to update the only 85 # target table in the rbu database. And one more to move the *-oal 86 # file to *-wal. After each of these steps, the progress remains 87 # at "10000 0". 88 # 89 if {[lindex [list $reslist] 0]!=-1} { 90 rbu step 91 set res [rbu bp_progress] 92 if {[set res] != [list 10000 0]} { 93 rbu close 94 error "2. reslist incorrect (expect=10000 0 got=[set res])" 95 } 96 } 97 98 rbu step 99 set res [rbu bp_progress] 100 if {[set res] != [list 10000 0]} { 101 rbu close 102 error "3. reslist incorrect (expect=10000 0 got=[set res])" 103 } 104 105 # Do the checkpoint. 106 while {[rbu step]=="SQLITE_OK"} { 107 foreach {a b} [rbu bp_progress] {} 108 if {[set a]!=10000 || [set b]<=0 || [set b]>10000} { 109 rbu close 110 error "4. reslist incorrect (expect=10000 1..10000 got=[set a] [set b])" 111 } 112 } 113 114 set res [rbu bp_progress] 115 if {[set res] != [list 10000 10000]} { 116 rbu close 117 error "5. reslist is incorrect (expect=10000 10000 got=[set res])" 118 } 119 120 rbu close 121 }] {SQLITE_DONE}] 122} 123 124foreach {bReopen} { 0 1 } { 125 reset_db 126 do_test 2.$bReopen.1.0 { 127 execsql { 128 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 129 } 130 create_db_file rbu.db { 131 CREATE TABLE data_t1(a, b, c, rbu_control); 132 INSERT INTO data_t1 VALUES(4, 4, 4, 0); 133 INSERT INTO data_t1 VALUES(5, 5, 5, 0); 134 135 CREATE TABLE rbu_count(tbl, cnt); 136 INSERT INTO rbu_count VALUES('data_t1', 2); 137 } 138 } {} 139 do_sp_test 2.$bReopen.1.1 $bReopen test.db rbu.db {5000 10000} 140 141 reset_db 142 do_test 2.$bReopen.2.0 { 143 execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) } 144 create_rbu1 rbu.db 145 } {rbu.db} 146 do_sp_test 2.$bReopen.2.1 $bReopen test.db rbu.db {3333 6666 10000} 147 148 reset_db 149 do_test 2.$bReopen.3.0 { 150 execsql { 151 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 152 CREATE INDEX i1 ON t1(b); 153 INSERT INTO t1 VALUES(1, 1, 1); 154 INSERT INTO t1 VALUES(2, 2, 2); 155 INSERT INTO t1 VALUES(3, 3, 3); 156 } 157 create_db_file rbu.db { 158 CREATE TABLE data_t1(a, b, c, rbu_control); 159 INSERT INTO data_t1 VALUES(4, 4, 4, 0); 160 INSERT INTO data_t1 VALUES(2, NULL, NULL, 1); 161 INSERT INTO data_t1 VALUES(5, NULL, NULL, 1); 162 163 CREATE TABLE rbu_count(tbl, cnt); 164 INSERT INTO rbu_count VALUES('data_t1', 3); 165 } 166 } {} 167 do_sp_test 2.$bReopen.3.1 $bReopen test.db rbu.db {1666 3333 6000 8000 10000} 168 169 reset_db 170 do_test 2.$bReopen.4.0 { 171 execsql { 172 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 173 CREATE INDEX i1 ON t1(b); 174 INSERT INTO t1 VALUES(1, 1, 1); 175 INSERT INTO t1 VALUES(2, 2, 2); 176 INSERT INTO t1 VALUES(3, 3, 3); 177 } 178 create_db_file rbu.db { 179 CREATE TABLE data_t1(a, b, c, rbu_control); 180 INSERT INTO data_t1 VALUES(2, 4, 4, '.xx'); 181 182 CREATE TABLE rbu_count(tbl, cnt); 183 INSERT INTO rbu_count VALUES('data_t1', 1); 184 } 185 } {} 186 do_sp_test 2.$bReopen.4.1 $bReopen test.db rbu.db {3333 6666 10000} 187 188 reset_db 189 do_test 2.$bReopen.5.0 { 190 execsql { 191 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 192 CREATE INDEX i1 ON t1(b); 193 INSERT INTO t1 VALUES(1, 1, 1); 194 INSERT INTO t1 VALUES(2, 2, 2); 195 INSERT INTO t1 VALUES(3, 3, 3); 196 } 197 create_db_file rbu.db { 198 CREATE TABLE data_t1(a, b, c, rbu_control); 199 INSERT INTO data_t1 VALUES(4, NULL, 4, '.xx'); 200 201 CREATE TABLE rbu_count(tbl, cnt); 202 INSERT INTO rbu_count VALUES('data_t1', 1); 203 } 204 } {} 205 do_sp_test 2.$bReopen.5.1 $bReopen test.db rbu.db {10000} 206 207 reset_db 208 do_test 2.$bReopen.6.0 { 209 execsql { 210 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 211 CREATE INDEX i1 ON t1(b); 212 INSERT INTO t1 VALUES(1, 1, 1); 213 INSERT INTO t1 VALUES(2, 2, 2); 214 INSERT INTO t1 VALUES(3, 3, 3); 215 } 216 create_db_file rbu.db { 217 CREATE TABLE data_t1(a, b, c, rbu_control); 218 INSERT INTO data_t1 VALUES(4, 4, 4, 0); 219 INSERT INTO data_t1 VALUES(2, NULL, NULL, 1); 220 INSERT INTO data_t1 VALUES(5, NULL, NULL, 1); 221 } 222 } {} 223 do_sp_test 2.$bReopen.6.1 $bReopen test.db rbu.db {-1 -1 -1 -1 -1 10000} 224} 225 226#------------------------------------------------------------------------- 227# The following tests verify that the API works when resuming an update 228# during the incremental checkpoint stage. 229# 230proc do_phase2_test {tn bReopen target rbu nStep} { 231 uplevel [list do_test $tn [subst -nocommands { 232 233 # Build the OAL/WAL file: 234 sqlite3rbu rbu $target $rbu 235 while {[lindex [rbu bp_progress] 0]<10000} { 236 set rc [rbu step] 237 if {"SQLITE_OK" != [set rc]} { rbu close } 238 } 239 240 # Clean up the temp tables and move the *-oal file to *-wal. 241 rbu step 242 rbu step 243 244 for {set i 0} {[set i] < $nStep} {incr i} { 245 if {$bReopen} { 246 rbu close 247 sqlite3rbu rbu $target $rbu 248 } 249 rbu step 250 set res [rbu bp_progress] 251 set expect [expr (1 + [set i]) * 10000 / $nStep] 252 if {[lindex [set res] 1] != [set expect]} { 253 error "Have [set res], expected 10000 [set expect]" 254 } 255 } 256 257 set rc [rbu step] 258 if {[set rc] != "SQLITE_DONE"} { 259 error "Have [set rc], expected SQLITE_DONE" 260 } 261 262 rbu close 263 }] {SQLITE_DONE}] 264} 265 266foreach bReopen {0 1} { 267 do_test 3.$bReopen.1.0 { 268 reset_db 269 execsql { 270 PRAGMA page_size = 4096; 271 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 272 CREATE TABLE t2(a INTEGER PRIMARY KEY, b); 273 CREATE TABLE t3(a INTEGER PRIMARY KEY, b); 274 CREATE TABLE t4(a INTEGER PRIMARY KEY, b); 275 } 276 create_db_file rbu.db { 277 CREATE TABLE data_t1(a, b, rbu_control); 278 CREATE TABLE data_t2(a, b, rbu_control); 279 CREATE TABLE data_t3(a, b, rbu_control); 280 CREATE TABLE data_t4(a, b, rbu_control); 281 INSERT INTO data_t1 VALUES(1, 2, 0); 282 INSERT INTO data_t2 VALUES(1, 2, 0); 283 INSERT INTO data_t3 VALUES(1, 2, 0); 284 INSERT INTO data_t4 VALUES(1, 2, 0); 285 286 CREATE TABLE rbu_count(tbl, cnt); 287 INSERT INTO rbu_count VALUES('data_t1', 1); 288 INSERT INTO rbu_count VALUES('data_t2', 1); 289 INSERT INTO rbu_count VALUES('data_t3', 1); 290 INSERT INTO rbu_count VALUES('data_t4', 1); 291 } 292 } {} 293 do_phase2_test 3.$bReopen.1.1 $bReopen test.db rbu.db 5 294} 295 296 297foreach {bReopen} { 0 1 } { 298 foreach {tn tbl} { 299 ipk { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) } 300 wr { CREATE TABLE t1(a INT PRIMARY KEY, b, c) WITHOUT ROWID } 301 pk { CREATE TABLE t1(a INT PRIMARY KEY, b, c) } 302 } { 303 304 foreach {tn2 rbusql r1 r3} { 305 1 { 306 CREATE TABLE data0_t1(a, b, c, rbu_control); 307 INSERT INTO data0_t1 VALUES(15, 15, 15, 0); 308 INSERT INTO data0_t1 VALUES(20, 20, 20, 0); 309 CREATE TABLE rbu_count(tbl, cnt); 310 INSERT INTO rbu_count VALUES('data0_t1', 2); 311 } 312 {2500 5000 7500 10000} 313 {1666 3333 5000 6666 8333 10000} 314 315 2 { 316 CREATE TABLE data0_t1(a, b, c, rbu_control); 317 INSERT INTO data0_t1 VALUES(10, 10, 10, 2); 318 CREATE TABLE rbu_count(tbl, cnt); 319 INSERT INTO rbu_count VALUES('data0_t1', 1); 320 } 321 {3333 6666 10000} 322 {2000 4000 6000 8000 10000} 323 324 3 { 325 CREATE TABLE data0_t1(a, b, c, rbu_control); 326 INSERT INTO data0_t1 VALUES(7, 7, 7, 2); 327 INSERT INTO data0_t1 VALUES(10, 10, 10, 2); 328 CREATE TABLE rbu_count(tbl, cnt); 329 INSERT INTO rbu_count VALUES('data0_t1', 2); 330 } 331 {2500 4000 6000 8000 10000} 332 {1666 2500 3750 5000 6250 7500 8750 10000} 333 334 } { 335 336 reset_db ; execsql $tbl 337 do_test 4.$tn.$bReopen.$tn2.0 { 338 execsql { 339 CREATE INDEX t1c ON t1(c); 340 INSERT INTO t1 VALUES(1, 1, 1); 341 INSERT INTO t1 VALUES(5, 5, 5); 342 INSERT INTO t1 VALUES(10, 10, 10); 343 } 344 create_db_file rbu.db $rbusql 345 } {} 346 347 set R(ipk) $r1 348 set R(wr) $r1 349 set R(pk) $r3 350 do_sp_test 4.$tn.$bReopen.$tn2.1 $bReopen test.db rbu.db $R($tn) 351 } 352 } 353} 354 355foreach {bReopen} { 0 1 } { 356 foreach {tn tbl} { 357 nopk { 358 CREATE TABLE t1(a, b, c); 359 CREATE INDEX t1c ON t1(c); 360 } 361 vtab { 362 CREATE VIRTUAL TABLE t1 USING fts5(a, b, c); 363 } 364 } { 365 366 if {$tn=="vtab"} { ifcapable !fts5 break } 367 368 foreach {tn2 rbusql r1 r2} { 369 1 { 370 CREATE TABLE data0_t1(a, b, c, rbu_rowid, rbu_control); 371 INSERT INTO data0_t1 VALUES(15, 15, 15, 4, 0); 372 INSERT INTO data0_t1 VALUES(20, 20, 20, 5, 0); 373 CREATE TABLE rbu_count(tbl, cnt); 374 INSERT INTO rbu_count VALUES('data0_t1', 2); 375 } 376 {2500 5000 7500 10000} 377 {5000 10000} 378 379 2 { 380 CREATE TABLE data0_t1(rbu_rowid, a, b, c, rbu_control); 381 INSERT INTO data0_t1 VALUES(0, 7, 7, 7, 2); 382 INSERT INTO data0_t1 VALUES(2, 10, 10, 10, 2); 383 CREATE TABLE rbu_count(tbl, cnt); 384 INSERT INTO rbu_count VALUES('data0_t1', 2); 385 } 386 {2500 4000 6000 8000 10000} 387 {5000 10000} 388 389 3 { 390 CREATE TABLE data0_t1(rbu_rowid, a, b, c, rbu_control); 391 INSERT INTO data0_t1 VALUES(1, NULL, NULL, NULL, 1); 392 INSERT INTO data0_t1 VALUES(2, NULL, NULL, 7, '..x'); 393 CREATE TABLE rbu_count(tbl, cnt); 394 INSERT INTO rbu_count VALUES('data0_t1', 2); 395 } 396 {2500 4000 6000 8000 10000} 397 {5000 10000} 398 } { 399 400 reset_db ; execsql $tbl 401 do_test 5.$tn.$bReopen.$tn2.0 { 402 execsql { 403 INSERT INTO t1 VALUES(1, 1, 1); 404 INSERT INTO t1 VALUES(5, 5, 5); 405 INSERT INTO t1 VALUES(10, 10, 10); 406 } 407 create_db_file rbu.db $rbusql 408 } {} 409 410 set R(nopk) $r1 411 set R(vtab) $r2 412 do_sp_test 5.$tn.$bReopen.$tn2.1 $bReopen test.db rbu.db $R($tn) 413 } 414 } 415} 416 417#------------------------------------------------------------------------- 418# Test that sqlite3_bp_progress() works with an RBU vacuum if there 419# is an rbu_count table in the db being vacuumed. 420# 421reset_db 422do_execsql_test 6.0 { 423 CREATE TABLE t1(a, b, c); 424 CREATE INDEX i1 ON t1(a); 425 CREATE INDEX i2 ON t1(b); 426 WITH s(i) AS ( 427 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100 428 ) 429 INSERT INTO t1 SELECT i, i, i FROM s; 430 CREATE TABLE rbu_count(tbl TEXT PRIMARY KEY, cnt INTEGER) WITHOUT ROWID; 431 INSERT INTO rbu_count VALUES('t1', (SELECT count(*) FROM t1)); 432 INSERT INTO rbu_count VALUES('rbu_count', 2); 433} 434 435forcedelete state.db 436do_test 6.1 { 437 set maxA 0 438 set maxB 0 439 sqlite3rbu_vacuum rbu test.db state.db 440 while {[rbu step]=="SQLITE_OK"} { 441 foreach {a b} [rbu bp_progress] { 442 if {$a > $maxA} { set maxA $a } 443 if {$b > $maxB} { set maxB $b } 444 } 445 } 446 list [rbu close] $maxA $maxB 447} {SQLITE_DONE 10000 10000} 448 449 450finish_test 451