1# 2009 January 30 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 SQLite library. The 12# focus of this file is testing the sqlite3_backup_XXX API. 13# 14# $Id: backup.test,v 1.11 2009/06/05 17:09:12 drh Exp $ 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19do_not_use_codec 20 21#--------------------------------------------------------------------- 22# Test organization: 23# 24# backup-1.*: Warm-body tests. 25# 26# backup-2.*: Test backup under various conditions. To and from in-memory 27# databases. To and from empty/populated databases. etc. 28# 29# backup-3.*: Verify that the locking-page (pending byte page) is handled. 30# 31# backup-4.*: Test various error conditions. 32# 33# backup-5.*: Test the source database being modified during a backup. 34# 35# backup-6.*: Test the backup_remaining() and backup_pagecount() APIs. 36# 37# backup-7.*: Test SQLITE_BUSY and SQLITE_LOCKED errors. 38# 39# backup-8.*: Test multiple simultaneous backup operations. 40# 41# backup-9.*: Test that passing a negative argument to backup_step() is 42# interpreted as "copy the whole file". 43# 44# backup-10.*: Test writing the source database mid backup. 45# 46 47proc data_checksum {db file} { $db one "SELECT md5sum(a, b) FROM ${file}.t1" } 48proc test_contents {name db1 file1 db2 file2} { 49 $db2 eval {select * from sqlite_master} 50 $db1 eval {select * from sqlite_master} 51 set checksum [data_checksum $db2 $file2] 52 uplevel [list do_test $name [list data_checksum $db1 $file1] $checksum] 53} 54 55do_test backup-1.1 { 56 execsql { 57 BEGIN; 58 CREATE TABLE t1(a, b); 59 CREATE INDEX i1 ON t1(a, b); 60 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 61 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 62 INSERT INTO t1 VALUES(3, randstr(1000,1000)); 63 INSERT INTO t1 VALUES(4, randstr(1000,1000)); 64 INSERT INTO t1 VALUES(5, randstr(1000,1000)); 65 COMMIT; 66 } 67} {} 68 69# Sanity check to verify that the [test_contents] proc works. 70# 71test_contents backup-1.2 db main db main 72 73# Check that it is possible to create and finish backup operations. 74# 75do_test backup-1.3.1 { 76 file delete test2.db 77 sqlite3 db2 test2.db 78 sqlite3_backup B db2 main db main 79} {B} 80do_test backup-1.3.2 { 81 B finish 82} {SQLITE_OK} 83do_test backup-1.3.3 { 84 info commands B 85} {} 86 87# Simplest backup operation. Backup test.db to test2.db. test2.db is 88# initially empty. test.db uses the default page size. 89# 90do_test backup-1.4.1 { 91 sqlite3_backup B db2 main db main 92} {B} 93do_test backup-1.4.2 { 94 B step 200 95} {SQLITE_DONE} 96do_test backup-1.4.3 { 97 B finish 98} {SQLITE_OK} 99do_test backup-1.4.4 { 100 info commands B 101} {} 102test_contents backup-1.4.5 db2 main db main 103db close 104db2 close 105# 106# End of backup-1.* tests. 107#--------------------------------------------------------------------- 108 109 110#--------------------------------------------------------------------- 111# The following tests, backup-2.*, are based on the following procedure: 112# 113# 1) Populate the source database. 114# 2) Populate the destination database. 115# 3) Run the backup to completion. (backup-2.*.1) 116# 4) Integrity check the destination db. (backup-2.*.2) 117# 5) Check that the contents of the destination db is the same as that 118# of the source db. (backup-2.*.3) 119# 120# The test is run with all possible combinations of the following 121# input parameters, except that if the destination is an in-memory 122# database, the only page size tested is 1024 bytes (the same as the 123# source page-size). 124# 125# * Source database is an in-memory database, OR 126# * Source database is a file-backed database. 127# 128# * Target database is an in-memory database, OR 129# * Target database is a file-backed database. 130# 131# * Destination database is a main file, OR 132# * Destination database is an attached file, OR 133# * Destination database is a temp database. 134# 135# * Target database is empty (zero bytes), OR 136# * Target database is larger than the source, OR 137# * Target database is smaller than the source. 138# 139# * Target database page-size is the same as the source, OR 140# * Target database page-size is larger than the source, OR 141# * Target database page-size is smaller than the source. 142# 143# * Each call to step copies a single page, OR 144# * A single call to step copies the entire source database. 145# 146set iTest 1 147foreach zSrcFile {test.db :memory:} { 148foreach zDestFile {test2.db :memory:} { 149foreach zOpenScript [list { 150 sqlite3 db $zSrcFile 151 sqlite3 db2 $zSrcFile 152 db2 eval "ATTACH '$zDestFile' AS bak" 153 set db_dest db2 154 set file_dest bak 155} { 156 sqlite3 db $zSrcFile 157 sqlite3 db2 $zDestFile 158 set db_dest db2 159 set file_dest main 160} { 161 sqlite3 db $zSrcFile 162 sqlite3 db2 $zDestFile 163 set db_dest db2 164 set file_dest temp 165}] { 166foreach rows_dest {0 3 10} { 167foreach pgsz_dest {512 1024 2048} { 168foreach nPagePerStep {1 200} { 169 170 # Open the databases. 171 catch { file delete test.db } 172 catch { file delete test2.db } 173 eval $zOpenScript 174 175 # Set to true if copying to an in-memory destination. Copying to an 176 # in-memory destination is only possible if the initial destination 177 # page size is the same as the source page size (in this case 1024 bytes). 178 # 179 set isMemDest [expr { 180 $zDestFile eq ":memory:" || $file_dest eq "temp" && $TEMP_STORE>=2 181 }] 182 183 if { $isMemDest==0 || $pgsz_dest == 1024 } { 184 if 0 { 185 puts -nonewline "Test $iTest: src=$zSrcFile dest=$zDestFile" 186 puts -nonewline " (as $db_dest.$file_dest)" 187 puts -nonewline " rows_dest=$rows_dest pgsz_dest=$pgsz_dest" 188 puts "" 189 } 190 191 # Set up the content of the source database. 192 execsql { 193 PRAGMA page_size = 1024; 194 BEGIN; 195 CREATE TABLE t1(a, b); 196 CREATE INDEX i1 ON t1(a, b); 197 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 198 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 199 INSERT INTO t1 VALUES(3, randstr(1000,1000)); 200 INSERT INTO t1 VALUES(4, randstr(1000,1000)); 201 INSERT INTO t1 VALUES(5, randstr(1000,1000)); 202 COMMIT; 203 } 204 205 206 207 # Set up the content of the target database. 208 execsql "PRAGMA ${file_dest}.page_size = ${pgsz_dest}" $db_dest 209 if {$rows_dest != 0} { 210 execsql " 211 BEGIN; 212 CREATE TABLE ${file_dest}.t1(a, b); 213 CREATE INDEX ${file_dest}.i1 ON t1(a, b); 214 " $db_dest 215 for {set ii 0} {$ii < $rows_dest} {incr ii} { 216 execsql " 217 INSERT INTO ${file_dest}.t1 VALUES(1, randstr(1000,1000)) 218 " $db_dest 219 } 220 } 221 222 # Backup the source database. 223 do_test backup-2.$iTest.1 { 224 sqlite3_backup B $db_dest $file_dest db main 225 while {[B step $nPagePerStep]=="SQLITE_OK"} {} 226 B finish 227 } {SQLITE_OK} 228 229 # Run integrity check on the backup. 230 do_test backup-2.$iTest.2 { 231 execsql "PRAGMA ${file_dest}.integrity_check" $db_dest 232 } {ok} 233 234 test_contents backup-2.$iTest.3 db main $db_dest $file_dest 235 236 } 237 238 db close 239 catch {db2 close} 240 incr iTest 241 242} } } } } } 243# 244# End of backup-2.* tests. 245#--------------------------------------------------------------------- 246 247#--------------------------------------------------------------------- 248# These tests, backup-3.*, ensure that nothing goes wrong if either 249# the source or destination database are large enough to include the 250# the locking-page (the page that contains the range of bytes that 251# the locks are applied to). These tests assume that the pending 252# byte is at offset 0x00010000 (64KB offset), as set by tester.tcl, 253# not at the 1GB offset as it usually is. 254# 255# The test procedure is as follows (same procedure as used for 256# the backup-2.* tests): 257# 258# 1) Populate the source database. 259# 2) Populate the destination database. 260# 3) Run the backup to completion. (backup-3.*.1) 261# 4) Integrity check the destination db. (backup-3.*.2) 262# 5) Check that the contents of the destination db is the same as that 263# of the source db. (backup-3.*.3) 264# 265# The test procedure is run with the following parameters varied: 266# 267# * Source database includes pending-byte page. 268# * Source database does not include pending-byte page. 269# 270# * Target database includes pending-byte page. 271# * Target database does not include pending-byte page. 272# 273# * Target database page-size is the same as the source, OR 274# * Target database page-size is larger than the source, OR 275# * Target database page-size is smaller than the source. 276# 277set iTest 1 278foreach nSrcPg {10 64 65 66 100} { 279foreach nDestRow {10 100} { 280foreach nDestPgsz {512 1024 2048 4096} { 281 282 catch { file delete test.db } 283 catch { file delete test2.db } 284 sqlite3 db test.db 285 sqlite3 db2 test2.db 286 287 # Set up the content of the two databases. 288 # 289 execsql { PRAGMA page_size = 1024 } 290 execsql "PRAGMA page_size = $nDestPgsz" db2 291 foreach db {db db2} { 292 execsql { 293 BEGIN; 294 CREATE TABLE t1(a, b); 295 CREATE INDEX i1 ON t1(a, b); 296 COMMIT; 297 } $db 298 } 299 while {[file size test.db]/1024 < $nSrcPg} { 300 execsql { INSERT INTO t1 VALUES($ii, randstr(200,200)) } 301 } 302 303 for {set ii 0} {$ii < $nDestRow} {incr ii} { 304 execsql { INSERT INTO t1 VALUES($ii, randstr(1000,1000)) } db2 305 } 306 307 # Backup the source database. 308 do_test backup-3.$iTest.1 { 309 sqlite3_backup B db main db2 main 310 while {[B step 10]=="SQLITE_OK"} {} 311 B finish 312 } {SQLITE_OK} 313 314 # Run integrity check on the backup. 315 do_test backup-3.$iTest.2 { 316 execsql "PRAGMA integrity_check" db2 317 } {ok} 318 319 test_contents backup-3.$iTest.3 db main db2 main 320 321 db close 322 db2 close 323 incr iTest 324} 325} 326} 327 328#-------------------------------------------------------------------- 329do_test backup-3.$iTest.1 { 330 catch { file delete -force test.db } 331 catch { file delete -force test2.db } 332 sqlite3 db test.db 333 set iTab 1 334 335 db eval { PRAGMA page_size = 512 } 336 while {[file size test.db] <= $::sqlite_pending_byte} { 337 db eval "CREATE TABLE t${iTab}(a, b, c)" 338 incr iTab 339 } 340 341 sqlite3 db2 test2.db 342 db2 eval { PRAGMA page_size = 4096 } 343 while {[file size test2.db] < $::sqlite_pending_byte} { 344 db2 eval "CREATE TABLE t${iTab}(a, b, c)" 345 incr iTab 346 } 347 348 sqlite3_backup B db2 main db main 349 B step -1 350} {SQLITE_DONE} 351 352do_test backup-3.$iTest.2 { 353 B finish 354} {SQLITE_OK} 355 356# 357# End of backup-3.* tests. 358#--------------------------------------------------------------------- 359 360 361#--------------------------------------------------------------------- 362# The following tests, backup-4.*, test various error conditions: 363# 364# backup-4.1.*: Test invalid database names. 365# 366# backup-4.2.*: Test that the source database cannot be detached while 367# a backup is in progress. 368# 369# backup-4.3.*: Test that the source database handle cannot be closed 370# while a backup is in progress. 371# 372# backup-4.4.*: Test an attempt to specify the same handle for the 373# source and destination databases. 374# 375# backup-4.5.*: Test that an in-memory destination with a different 376# page-size to the source database is an error. 377# 378sqlite3 db test.db 379sqlite3 db2 test2.db 380 381do_test backup-4.1.1 { 382 catch { sqlite3_backup B db aux db2 main } 383} {1} 384do_test backup-4.1.2 { 385 sqlite3_errmsg db 386} {unknown database aux} 387do_test backup-4.1.3 { 388 catch { sqlite3_backup B db main db2 aux } 389} {1} 390do_test backup-4.1.4 { 391 sqlite3_errmsg db 392} {unknown database aux} 393 394do_test backup-4.2.1 { 395 catch { file delete -force test3.db } 396 catch { file delete -force test4.db } 397 execsql { 398 ATTACH 'test3.db' AS aux1; 399 CREATE TABLE aux1.t1(a, b); 400 } 401 execsql { 402 ATTACH 'test4.db' AS aux2; 403 CREATE TABLE aux2.t2(a, b); 404 } db2 405 sqlite3_backup B db aux1 db2 aux2 406} {B} 407do_test backup-4.2.2 { 408 catchsql { DETACH aux2 } db2 409} {1 {database aux2 is locked}} 410do_test backup-4.2.3 { 411 B step 50 412} {SQLITE_DONE} 413do_test backup-4.2.4 { 414 B finish 415} {SQLITE_OK} 416 417do_test backup-4.3.1 { 418 sqlite3_backup B db aux1 db2 aux2 419} {B} 420do_test backup-4.3.2 { 421 db2 cache flush 422 sqlite3_close db2 423} {SQLITE_BUSY} 424do_test backup-4.3.3 { 425 sqlite3_errmsg db2 426} {unable to close due to unfinished backup operation} 427do_test backup-4.3.4 { 428 B step 50 429} {SQLITE_DONE} 430do_test backup-4.3.5 { 431 B finish 432} {SQLITE_OK} 433 434do_test backup-4.4.1 { 435 set rc [catch {sqlite3_backup B db main db aux1}] 436 list $rc [sqlite3_errcode db] [sqlite3_errmsg db] 437} {1 SQLITE_ERROR {source and destination must be distinct}} 438db close 439db2 close 440 441do_test backup-4.5.1 { 442 catch { file delete -force test.db } 443 sqlite3 db test.db 444 sqlite3 db2 :memory: 445 execsql { 446 CREATE TABLE t1(a, b); 447 INSERT INTO t1 VALUES(1, 2); 448 } 449 execsql { 450 PRAGMA page_size = 4096; 451 CREATE TABLE t2(a, b); 452 INSERT INTO t2 VALUES(3, 4); 453 } db2 454 sqlite3_backup B db2 main db main 455} {B} 456do_test backup-4.5.2 { 457 B step 5000 458} {SQLITE_READONLY} 459do_test backup-4.5.3 { 460 B finish 461} {SQLITE_READONLY} 462 463db close 464db2 close 465# 466# End of backup-5.* tests. 467#--------------------------------------------------------------------- 468 469#--------------------------------------------------------------------- 470# The following tests, backup-5.*, test that the backup works properly 471# when the source database is modified during the backup. Test cases 472# are organized as follows: 473# 474# backup-5.x.1.*: Nothing special. Modify the database mid-backup. 475# 476# backup-5.x.2.*: Modify the database mid-backup so that one or more 477# pages are written out due to cache stress. Then 478# rollback the transaction. 479# 480# backup-5.x.3.*: Database is vacuumed. 481# 482# backup-5.x.4.*: Database is vacuumed and the page-size modified. 483# 484# backup-5.x.5.*: Database is shrunk via incr-vacuum. 485# 486# Each test is run three times, in the following configurations: 487# 488# 1) Backing up file-to-file. The writer writes via an external pager. 489# 2) Backing up file-to-file. The writer writes via the same pager as 490# is used by the backup operation. 491# 3) Backing up memory-to-file. 492# 493set iTest 0 494file delete -force bak.db-wal 495foreach {writer file} {db test.db db3 test.db db :memory:} { 496 incr iTest 497 catch { file delete bak.db } 498 sqlite3 db2 bak.db 499 catch { file delete $file } 500 sqlite3 db $file 501 sqlite3 db3 $file 502 503 do_test backup-5.$iTest.1.1 { 504 execsql { 505 BEGIN; 506 CREATE TABLE t1(a, b); 507 CREATE INDEX i1 ON t1(a, b); 508 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 509 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 510 INSERT INTO t1 VALUES(3, randstr(1000,1000)); 511 INSERT INTO t1 VALUES(4, randstr(1000,1000)); 512 INSERT INTO t1 VALUES(5, randstr(1000,1000)); 513 COMMIT; 514 } 515 expr {[execsql {PRAGMA page_count}] > 10} 516 } {1} 517 do_test backup-5.$iTest.1.2 { 518 sqlite3_backup B db2 main db main 519 B step 5 520 } {SQLITE_OK} 521 do_test backup-5.$iTest.1.3 { 522 execsql { UPDATE t1 SET a = a + 1 } $writer 523 B step 50 524 } {SQLITE_DONE} 525 do_test backup-5.$iTest.1.4 { 526 B finish 527 } {SQLITE_OK} 528 integrity_check backup-5.$iTest.1.5 db2 529 test_contents backup-5.$iTest.1.6 db main db2 main 530 531 do_test backup-5.$iTest.2.1 { 532 execsql { 533 PRAGMA cache_size = 10; 534 BEGIN; 535 INSERT INTO t1 SELECT '', randstr(1000,1000) FROM t1; 536 INSERT INTO t1 SELECT '', randstr(1000,1000) FROM t1; 537 INSERT INTO t1 SELECT '', randstr(1000,1000) FROM t1; 538 INSERT INTO t1 SELECT '', randstr(1000,1000) FROM t1; 539 COMMIT; 540 } 541 } {} 542 do_test backup-5.$iTest.2.2 { 543 sqlite3_backup B db2 main db main 544 B step 50 545 } {SQLITE_OK} 546 do_test backup-5.$iTest.2.3 { 547 execsql { 548 BEGIN; 549 UPDATE t1 SET a = a + 1; 550 ROLLBACK; 551 } $writer 552 B step 5000 553 } {SQLITE_DONE} 554 do_test backup-5.$iTest.2.4 { 555 B finish 556 } {SQLITE_OK} 557 integrity_check backup-5.$iTest.2.5 db2 558 test_contents backup-5.$iTest.2.6 db main db2 main 559 560 do_test backup-5.$iTest.3.1 { 561 execsql { UPDATE t1 SET b = randstr(1000,1000) } 562 } {} 563 do_test backup-5.$iTest.3.2 { 564 sqlite3_backup B db2 main db main 565 B step 50 566 } {SQLITE_OK} 567 do_test backup-5.$iTest.3.3 { 568 execsql { VACUUM } $writer 569 B step 5000 570 } {SQLITE_DONE} 571 do_test backup-5.$iTest.3.4 { 572 B finish 573 } {SQLITE_OK} 574 integrity_check backup-5.$iTest.3.5 db2 575 test_contents backup-5.$iTest.3.6 db main db2 main 576 577 do_test backup-5.$iTest.4.1 { 578 execsql { UPDATE t1 SET b = randstr(1000,1000) } 579 } {} 580 do_test backup-5.$iTest.4.2 { 581 sqlite3_backup B db2 main db main 582 B step 50 583 } {SQLITE_OK} 584 do_test backup-5.$iTest.4.3 { 585 execsql { 586 PRAGMA page_size = 2048; 587 VACUUM; 588 } $writer 589 B step 5000 590 } {SQLITE_DONE} 591 do_test backup-5.$iTest.4.4 { 592 B finish 593 } {SQLITE_OK} 594 integrity_check backup-5.$iTest.4.5 db2 595 test_contents backup-5.$iTest.4.6 db main db2 main 596 597 catch {db close} 598 catch {db2 close} 599 catch {db3 close} 600 catch { file delete bak.db } 601 sqlite3 db2 bak.db 602 catch { file delete $file } 603 sqlite3 db $file 604 sqlite3 db3 $file 605 do_test backup-5.$iTest.5.1 { 606 execsql { 607 PRAGMA auto_vacuum = incremental; 608 BEGIN; 609 CREATE TABLE t1(a, b); 610 CREATE INDEX i1 ON t1(a, b); 611 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 612 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 613 INSERT INTO t1 VALUES(3, randstr(1000,1000)); 614 INSERT INTO t1 VALUES(4, randstr(1000,1000)); 615 INSERT INTO t1 VALUES(5, randstr(1000,1000)); 616 COMMIT; 617 } 618 } {} 619 do_test backup-5.$iTest.5.2 { 620 sqlite3_backup B db2 main db main 621 B step 8 622 } {SQLITE_OK} 623 do_test backup-5.$iTest.5.3 { 624 execsql { 625 DELETE FROM t1; 626 PRAGMA incremental_vacuum; 627 } $writer 628 B step 50 629 } {SQLITE_DONE} 630 do_test backup-5.$iTest.5.4 { 631 B finish 632 } {SQLITE_OK} 633 integrity_check backup-5.$iTest.5.5 db2 634 test_contents backup-5.$iTest.5.6 db main db2 main 635 catch {db close} 636 catch {db2 close} 637 catch {db3 close} 638} 639# 640# End of backup-5.* tests. 641#--------------------------------------------------------------------- 642 643#--------------------------------------------------------------------- 644# Test the sqlite3_backup_remaining() and backup_pagecount() APIs. 645# 646do_test backup-6.1 { 647 catch { file delete -force test.db } 648 catch { file delete -force test2.db } 649 sqlite3 db test.db 650 sqlite3 db2 test2.db 651 execsql { 652 BEGIN; 653 CREATE TABLE t1(a, b); 654 CREATE INDEX i1 ON t1(a, b); 655 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 656 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 657 INSERT INTO t1 VALUES(3, randstr(1000,1000)); 658 INSERT INTO t1 VALUES(4, randstr(1000,1000)); 659 INSERT INTO t1 VALUES(5, randstr(1000,1000)); 660 COMMIT; 661 } 662} {} 663do_test backup-6.2 { 664 set nTotal [expr {[file size test.db]/1024}] 665 sqlite3_backup B db2 main db main 666 B step 1 667} {SQLITE_OK} 668do_test backup-6.3 { 669 B pagecount 670} $nTotal 671do_test backup-6.4 { 672 B remaining 673} [expr $nTotal-1] 674do_test backup-6.5 { 675 B step 5 676 list [B remaining] [B pagecount] 677} [list [expr $nTotal-6] $nTotal] 678do_test backup-6.6 { 679 execsql { CREATE TABLE t2(a PRIMARY KEY, b) } 680 B step 1 681 list [B remaining] [B pagecount] 682} [list [expr $nTotal-5] [expr $nTotal+2]] 683 684do_test backup-6.X { 685 B finish 686} {SQLITE_OK} 687 688catch {db close} 689catch {db2 close} 690 691#--------------------------------------------------------------------- 692# Test cases backup-7.* test that SQLITE_BUSY and SQLITE_LOCKED errors 693# are returned correctly: 694# 695# backup-7.1.*: Source database is externally locked (return SQLITE_BUSY). 696# 697# backup-7.2.*: Attempt to step the backup process while a 698# write-transaction is underway on the source pager (return 699# SQLITE_LOCKED). 700# 701# backup-7.3.*: Destination database is externally locked (return SQLITE_BUSY). 702# 703do_test backup-7.0 { 704 catch { file delete -force test.db } 705 catch { file delete -force test2.db } 706 sqlite3 db2 test2.db 707 sqlite3 db test.db 708 execsql { 709 CREATE TABLE t1(a, b); 710 CREATE INDEX i1 ON t1(a, b); 711 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 712 INSERT INTO t1 SELECT a+ 1, randstr(1000,1000) FROM t1; 713 INSERT INTO t1 SELECT a+ 2, randstr(1000,1000) FROM t1; 714 INSERT INTO t1 SELECT a+ 4, randstr(1000,1000) FROM t1; 715 INSERT INTO t1 SELECT a+ 8, randstr(1000,1000) FROM t1; 716 INSERT INTO t1 SELECT a+16, randstr(1000,1000) FROM t1; 717 INSERT INTO t1 SELECT a+32, randstr(1000,1000) FROM t1; 718 INSERT INTO t1 SELECT a+64, randstr(1000,1000) FROM t1; 719 } 720} {} 721 722do_test backup-7.1.1 { 723 sqlite3_backup B db2 main db main 724 B step 5 725} {SQLITE_OK} 726do_test backup-7.1.2 { 727 sqlite3 db3 test.db 728 execsql { BEGIN EXCLUSIVE } db3 729 B step 5 730} {SQLITE_BUSY} 731do_test backup-7.1.3 { 732 execsql { ROLLBACK } db3 733 B step 5 734} {SQLITE_OK} 735do_test backup-7.2.1 { 736 execsql { 737 BEGIN; 738 INSERT INTO t1 VALUES(1, 4); 739 } 740} {} 741do_test backup-7.2.2 { 742 B step 5000 743} {SQLITE_BUSY} 744do_test backup-7.2.3 { 745 execsql { ROLLBACK } 746 B step 5000 747} {SQLITE_DONE} 748do_test backup-7.2.4 { 749 B finish 750} {SQLITE_OK} 751test_contents backup-7.2.5 db main db2 main 752integrity_check backup-7.3.6 db2 753 754do_test backup-7.3.1 { 755 db2 close 756 db3 close 757 file delete -force test2.db 758 sqlite3 db2 test2.db 759 sqlite3 db3 test2.db 760 761 sqlite3_backup B db2 main db main 762 execsql { BEGIN ; CREATE TABLE t2(a, b); } db3 763 764 B step 5 765} {SQLITE_BUSY} 766do_test backup-7.3.2 { 767 execsql { COMMIT } db3 768 B step 5000 769} {SQLITE_DONE} 770do_test backup-7.3.3 { 771 B finish 772} {SQLITE_OK} 773test_contents backup-7.3.4 db main db2 main 774integrity_check backup-7.3.5 db2 775catch { db2 close } 776catch { db3 close } 777 778#----------------------------------------------------------------------- 779# The following tests, backup-8.*, test attaching multiple backup 780# processes to the same source database. Also, reading from the source 781# database while a read transaction is active. 782# 783# These tests reuse the database "test.db" left over from backup-7.*. 784# 785do_test backup-8.1 { 786 catch { file delete -force test2.db } 787 catch { file delete -force test3.db } 788 sqlite3 db2 test2.db 789 sqlite3 db3 test3.db 790 791 sqlite3_backup B2 db2 main db main 792 sqlite3_backup B3 db3 main db main 793 list [B2 finish] [B3 finish] 794} {SQLITE_OK SQLITE_OK} 795do_test backup-8.2 { 796 sqlite3_backup B3 db3 main db main 797 sqlite3_backup B2 db2 main db main 798 list [B2 finish] [B3 finish] 799} {SQLITE_OK SQLITE_OK} 800do_test backup-8.3 { 801 sqlite3_backup B2 db2 main db main 802 sqlite3_backup B3 db3 main db main 803 B2 step 5 804} {SQLITE_OK} 805do_test backup-8.4 { 806 execsql { 807 BEGIN; 808 SELECT * FROM sqlite_master; 809 } 810 B3 step 5 811} {SQLITE_OK} 812do_test backup-8.5 { 813 list [B3 step 5000] [B3 finish] 814} {SQLITE_DONE SQLITE_OK} 815do_test backup-8.6 { 816 list [B2 step 5000] [B2 finish] 817} {SQLITE_DONE SQLITE_OK} 818test_contents backup-8.7 db main db2 main 819test_contents backup-8.8 db main db3 main 820do_test backup-8.9 { 821 execsql { PRAGMA lock_status } 822} {main shared temp closed} 823do_test backup-8.10 { 824 execsql COMMIT 825} {} 826catch { db2 close } 827catch { db3 close } 828 829#----------------------------------------------------------------------- 830# The following tests, backup-9.*, test that: 831# 832# * Passing 0 as an argument to sqlite3_backup_step() means no pages 833# are backed up (backup-9.1.*), and 834# * Passing a negative value as an argument to sqlite3_backup_step() means 835# all pages are backed up (backup-9.2.*). 836# 837# These tests reuse the database "test.db" left over from backup-7.*. 838# 839do_test backup-9.1.1 { 840 sqlite3 db2 test2.db 841 sqlite3_backup B db2 main db main 842 B step 1 843} {SQLITE_OK} 844do_test backup-9.1.2 { 845 set nRemaining [B remaining] 846 expr {$nRemaining>100} 847} {1} 848do_test backup-9.1.3 { 849 B step 0 850} {SQLITE_OK} 851do_test backup-9.1.4 { 852 B remaining 853} $nRemaining 854 855do_test backup-9.2.1 { 856 B step -1 857} {SQLITE_DONE} 858do_test backup-9.2.2 { 859 B remaining 860} {0} 861do_test backup-9.2.3 { 862 B finish 863} {SQLITE_OK} 864catch {db2 close} 865 866ifcapable memorymanage { 867 db close 868 file delete -force test.db 869 file delete -force bak.db 870 871 sqlite3 db test.db 872 sqlite3 db2 test.db 873 sqlite3 db3 bak.db 874 875 do_test backup-10.1.1 { 876 execsql { 877 BEGIN; 878 CREATE TABLE t1(a, b); 879 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 880 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 881 INSERT INTO t1 VALUES(3, randstr(1000,1000)); 882 INSERT INTO t1 VALUES(4, randstr(1000,1000)); 883 INSERT INTO t1 VALUES(5, randstr(1000,1000)); 884 CREATE INDEX i1 ON t1(a, b); 885 COMMIT; 886 } 887 } {} 888 do_test backup-10.1.2 { 889 sqlite3_backup B db3 main db2 main 890 B step 5 891 } {SQLITE_OK} 892 do_test backup-10.1.3 { 893 execsql { 894 UPDATE t1 SET b = randstr(500,500); 895 } 896 } {} 897 sqlite3_release_memory [expr 1024*1024] 898 do_test backup-10.1.3 { 899 B step 50 900 } {SQLITE_DONE} 901 do_test backup-10.1.4 { 902 B finish 903 } {SQLITE_OK} 904 do_test backup-10.1.5 { 905 execsql { PRAGMA integrity_check } db3 906 } {ok} 907 908 db2 close 909 db3 close 910} 911 912 913#----------------------------------------------------------------------- 914# Test that if the database is written to via the same database handle being 915# used as the source by a backup operation: 916# 917# 10.1.*: If the db is in-memory, the backup is restarted. 918# 10.2.*: If the db is a file, the backup is not restarted. 919# 920db close 921file delete -force test.db test.db-journal 922foreach {tn file rc} { 923 1 test.db SQLITE_DONE 924 2 :memory: SQLITE_OK 925} { 926 do_test backup-10.$tn.1 { 927 sqlite3 db $file 928 execsql { 929 CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB); 930 BEGIN; 931 INSERT INTO t1 VALUES(NULL, randomblob(200)); 932 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1; 933 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1; 934 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1; 935 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1; 936 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1; 937 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1; 938 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1; 939 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1; 940 COMMIT; 941 SELECT count(*) FROM t1; 942 } 943 } {256} 944 945 do_test backup-10.$tn.2 { 946 set pgs [execsql {pragma page_count}] 947 expr {$pgs > 50 && $pgs < 75} 948 } {1} 949 950 do_test backup-10.$tn.3 { 951 file delete -force bak.db bak.db-journal 952 sqlite3 db2 bak.db 953 sqlite3_backup B db2 main db main 954 B step 50 955 } {SQLITE_OK} 956 957 do_test backup-10.$tn.4 { 958 execsql { UPDATE t1 SET b = randomblob(200) WHERE a IN (1, 250) } 959 } {} 960 961 do_test backup-10.$tn.5 { 962 B step 50 963 } $rc 964 965 do_test backup-10.$tn.6 { 966 B finish 967 } {SQLITE_OK} 968 969 db2 close 970} 971 972finish_test 973