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 delete_file 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 { delete_file test.db } 172 catch { delete_file 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 execsql COMMIT $db_dest 221 } 222 223 # Backup the source database. 224 do_test backup-2.$iTest.1 { 225 sqlite3_backup B $db_dest $file_dest db main 226 while {[B step $nPagePerStep]=="SQLITE_OK"} {} 227 B finish 228 } {SQLITE_OK} 229 230 # Run integrity check on the backup. 231 do_test backup-2.$iTest.2 { 232 execsql "PRAGMA ${file_dest}.integrity_check" $db_dest 233 } {ok} 234 235 test_contents backup-2.$iTest.3 db main $db_dest $file_dest 236 237 } 238 239 db close 240 catch {db2 close} 241 incr iTest 242 243} } } } } } 244# 245# End of backup-2.* tests. 246#--------------------------------------------------------------------- 247 248#--------------------------------------------------------------------- 249# These tests, backup-3.*, ensure that nothing goes wrong if either 250# the source or destination database are large enough to include the 251# the locking-page (the page that contains the range of bytes that 252# the locks are applied to). These tests assume that the pending 253# byte is at offset 0x00010000 (64KB offset), as set by tester.tcl, 254# not at the 1GB offset as it usually is. 255# 256# The test procedure is as follows (same procedure as used for 257# the backup-2.* tests): 258# 259# 1) Populate the source database. 260# 2) Populate the destination database. 261# 3) Run the backup to completion. (backup-3.*.1) 262# 4) Integrity check the destination db. (backup-3.*.2) 263# 5) Check that the contents of the destination db is the same as that 264# of the source db. (backup-3.*.3) 265# 266# The test procedure is run with the following parameters varied: 267# 268# * Source database includes pending-byte page. 269# * Source database does not include pending-byte page. 270# 271# * Target database includes pending-byte page. 272# * Target database does not include pending-byte page. 273# 274# * Target database page-size is the same as the source, OR 275# * Target database page-size is larger than the source, OR 276# * Target database page-size is smaller than the source. 277# 278set iTest 1 279foreach nSrcPg {10 64 65 66 100} { 280foreach nDestRow {10 100} { 281foreach nDestPgsz {512 1024 2048 4096} { 282 283 catch { delete_file test.db } 284 catch { delete_file test2.db } 285 sqlite3 db test.db 286 sqlite3 db2 test2.db 287 288 # Set up the content of the two databases. 289 # 290 execsql { PRAGMA page_size = 1024 } 291 execsql "PRAGMA page_size = $nDestPgsz" db2 292 foreach db {db db2} { 293 execsql { 294 BEGIN; 295 CREATE TABLE t1(a, b); 296 CREATE INDEX i1 ON t1(a, b); 297 COMMIT; 298 } $db 299 } 300 while {[file size test.db]/1024 < $nSrcPg} { 301 execsql { INSERT INTO t1 VALUES($ii, randstr(200,200)) } 302 } 303 304 for {set ii 0} {$ii < $nDestRow} {incr ii} { 305 execsql { INSERT INTO t1 VALUES($ii, randstr(1000,1000)) } db2 306 } 307 308 # Backup the source database. 309 do_test backup-3.$iTest.1 { 310 sqlite3_backup B db main db2 main 311 while {[B step 10]=="SQLITE_OK"} {} 312 B finish 313 } {SQLITE_OK} 314 315 # Run integrity check on the backup. 316 do_test backup-3.$iTest.2 { 317 execsql "PRAGMA integrity_check" db2 318 } {ok} 319 320 test_contents backup-3.$iTest.3 db main db2 main 321 322 db close 323 db2 close 324 incr iTest 325} 326} 327} 328 329#-------------------------------------------------------------------- 330do_test backup-3.$iTest.1 { 331 catch { forcedelete test.db } 332 catch { forcedelete test2.db } 333 sqlite3 db test.db 334 set iTab 1 335 336 db eval { PRAGMA page_size = 512 } 337 while {[file size test.db] <= $::sqlite_pending_byte} { 338 db eval "CREATE TABLE t${iTab}(a, b, c)" 339 incr iTab 340 } 341 342 sqlite3 db2 test2.db 343 db2 eval { PRAGMA page_size = 4096 } 344 while {[file size test2.db] < $::sqlite_pending_byte} { 345 db2 eval "CREATE TABLE t${iTab}(a, b, c)" 346 incr iTab 347 } 348 349 sqlite3_backup B db2 main db main 350 B step -1 351} {SQLITE_DONE} 352 353do_test backup-3.$iTest.2 { 354 B finish 355} {SQLITE_OK} 356 357# 358# End of backup-3.* tests. 359#--------------------------------------------------------------------- 360 361 362#--------------------------------------------------------------------- 363# The following tests, backup-4.*, test various error conditions: 364# 365# backup-4.1.*: Test invalid database names. 366# 367# backup-4.2.*: Test that the source database cannot be detached while 368# a backup is in progress. 369# 370# backup-4.3.*: Test that the source database handle cannot be closed 371# while a backup is in progress. 372# 373# backup-4.4.*: Test an attempt to specify the same handle for the 374# source and destination databases. 375# 376# backup-4.5.*: Test that an in-memory destination with a different 377# page-size to the source database is an error. 378# 379sqlite3 db test.db 380sqlite3 db2 test2.db 381 382do_test backup-4.1.1 { 383 catch { sqlite3_backup B db aux db2 main } 384} {1} 385do_test backup-4.1.2 { 386 sqlite3_errmsg db 387} {unknown database aux} 388do_test backup-4.1.3 { 389 catch { sqlite3_backup B db main db2 aux } 390} {1} 391do_test backup-4.1.4 { 392 sqlite3_errmsg db 393} {unknown database aux} 394 395do_test backup-4.2.1 { 396 catch { forcedelete test3.db } 397 catch { forcedelete test4.db } 398 execsql { 399 ATTACH 'test3.db' AS aux1; 400 CREATE TABLE aux1.t1(a, b); 401 } 402 execsql { 403 ATTACH 'test4.db' AS aux2; 404 CREATE TABLE aux2.t2(a, b); 405 } db2 406 sqlite3_backup B db aux1 db2 aux2 407} {B} 408do_test backup-4.2.2 { 409 catchsql { DETACH aux2 } db2 410} {1 {database aux2 is locked}} 411do_test backup-4.2.3 { 412 B step 50 413} {SQLITE_DONE} 414do_test backup-4.2.4 { 415 B finish 416} {SQLITE_OK} 417 418do_test backup-4.3.1 { 419 sqlite3_backup B db aux1 db2 aux2 420} {B} 421do_test backup-4.3.2 { 422 db2 cache flush 423 sqlite3_close db2 424} {SQLITE_BUSY} 425do_test backup-4.3.3 { 426 sqlite3_errmsg db2 427} {unable to close due to unfinalized statements or unfinished backups} 428do_test backup-4.3.4 { 429 B step 50 430} {SQLITE_DONE} 431do_test backup-4.3.5 { 432 B finish 433} {SQLITE_OK} 434 435do_test backup-4.4.1 { 436 set rc [catch {sqlite3_backup B db main db aux1}] 437 list $rc [sqlite3_errcode db] [sqlite3_errmsg db] 438} {1 SQLITE_ERROR {source and destination must be distinct}} 439db close 440db2 close 441 442do_test backup-4.5.1 { 443 catch { forcedelete test.db } 444 sqlite3 db test.db 445 sqlite3 db2 :memory: 446 execsql { 447 CREATE TABLE t1(a, b); 448 INSERT INTO t1 VALUES(1, 2); 449 } 450 execsql { 451 PRAGMA page_size = 4096; 452 CREATE TABLE t2(a, b); 453 INSERT INTO t2 VALUES(3, 4); 454 } db2 455 sqlite3_backup B db2 main db main 456} {B} 457do_test backup-4.5.2 { 458 B step 5000 459} {SQLITE_READONLY} 460do_test backup-4.5.3 { 461 B finish 462} {SQLITE_READONLY} 463 464db close 465db2 close 466# 467# End of backup-4.* tests. 468#--------------------------------------------------------------------- 469 470#--------------------------------------------------------------------- 471# The following tests, backup-5.*, test that the backup works properly 472# when the source database is modified during the backup. Test cases 473# are organized as follows: 474# 475# backup-5.x.1.*: Nothing special. Modify the database mid-backup. 476# 477# backup-5.x.2.*: Modify the database mid-backup so that one or more 478# pages are written out due to cache stress. Then 479# rollback the transaction. 480# 481# backup-5.x.3.*: Database is vacuumed. 482# 483# backup-5.x.4.*: Database is vacuumed and the page-size modified. 484# 485# backup-5.x.5.*: Database is shrunk via incr-vacuum. 486# 487# Each test is run three times, in the following configurations: 488# 489# 1) Backing up file-to-file. The writer writes via an external pager. 490# 2) Backing up file-to-file. The writer writes via the same pager as 491# is used by the backup operation. 492# 3) Backing up memory-to-file. 493# 494set iTest 0 495forcedelete bak.db-wal 496foreach {writer file} {db test.db db3 test.db db :memory:} { 497 incr iTest 498 catch { delete_file bak.db } 499 sqlite3 db2 bak.db 500 catch { delete_file $file } 501 sqlite3 db $file 502 sqlite3 db3 $file 503 504 do_test backup-5.$iTest.1.1 { 505 execsql { 506 BEGIN; 507 CREATE TABLE t1(a, b); 508 CREATE INDEX i1 ON t1(a, b); 509 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 510 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 511 INSERT INTO t1 VALUES(3, randstr(1000,1000)); 512 INSERT INTO t1 VALUES(4, randstr(1000,1000)); 513 INSERT INTO t1 VALUES(5, randstr(1000,1000)); 514 COMMIT; 515 } 516 expr {[execsql {PRAGMA page_count}] > 10} 517 } {1} 518 do_test backup-5.$iTest.1.2 { 519 sqlite3_backup B db2 main db main 520 B step 5 521 } {SQLITE_OK} 522 do_test backup-5.$iTest.1.3 { 523 execsql { UPDATE t1 SET a = a + 1 } $writer 524 B step 50 525 } {SQLITE_DONE} 526 do_test backup-5.$iTest.1.4 { 527 B finish 528 } {SQLITE_OK} 529 integrity_check backup-5.$iTest.1.5 db2 530 test_contents backup-5.$iTest.1.6 db main db2 main 531 532 do_test backup-5.$iTest.2.1 { 533 execsql { 534 PRAGMA cache_size = 10; 535 BEGIN; 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 INSERT INTO t1 SELECT '', randstr(1000,1000) FROM t1; 540 COMMIT; 541 } 542 } {} 543 do_test backup-5.$iTest.2.2 { 544 sqlite3_backup B db2 main db main 545 B step 50 546 } {SQLITE_OK} 547 do_test backup-5.$iTest.2.3 { 548 execsql { 549 BEGIN; 550 UPDATE t1 SET a = a + 1; 551 ROLLBACK; 552 } $writer 553 B step 5000 554 } {SQLITE_DONE} 555 do_test backup-5.$iTest.2.4 { 556 B finish 557 } {SQLITE_OK} 558 integrity_check backup-5.$iTest.2.5 db2 559 test_contents backup-5.$iTest.2.6 db main db2 main 560 561 do_test backup-5.$iTest.3.1 { 562 execsql { UPDATE t1 SET b = randstr(1000,1000) } 563 } {} 564 do_test backup-5.$iTest.3.2 { 565 sqlite3_backup B db2 main db main 566 B step 50 567 } {SQLITE_OK} 568 do_test backup-5.$iTest.3.3 { 569 execsql { VACUUM } $writer 570 B step 5000 571 } {SQLITE_DONE} 572 do_test backup-5.$iTest.3.4 { 573 B finish 574 } {SQLITE_OK} 575 integrity_check backup-5.$iTest.3.5 db2 576 test_contents backup-5.$iTest.3.6 db main db2 main 577 578 do_test backup-5.$iTest.4.1 { 579 execsql { UPDATE t1 SET b = randstr(1000,1000) } 580 } {} 581 do_test backup-5.$iTest.4.2 { 582 sqlite3_backup B db2 main db main 583 B step 50 584 } {SQLITE_OK} 585 do_test backup-5.$iTest.4.3 { 586 execsql { 587 PRAGMA page_size = 2048; 588 VACUUM; 589 } $writer 590 B step 5000 591 } {SQLITE_DONE} 592 do_test backup-5.$iTest.4.4 { 593 B finish 594 } {SQLITE_OK} 595 integrity_check backup-5.$iTest.4.5 db2 596 test_contents backup-5.$iTest.4.6 db main db2 main 597 598 catch {db close} 599 catch {db2 close} 600 catch {db3 close} 601 catch { delete_file bak.db } 602 sqlite3 db2 bak.db 603 catch { delete_file $file } 604 sqlite3 db $file 605 sqlite3 db3 $file 606 do_test backup-5.$iTest.5.1 { 607 execsql { 608 PRAGMA auto_vacuum = incremental; 609 BEGIN; 610 CREATE TABLE t1(a, b); 611 CREATE INDEX i1 ON t1(a, b); 612 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 613 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 614 INSERT INTO t1 VALUES(3, randstr(1000,1000)); 615 INSERT INTO t1 VALUES(4, randstr(1000,1000)); 616 INSERT INTO t1 VALUES(5, randstr(1000,1000)); 617 COMMIT; 618 } 619 } {} 620 do_test backup-5.$iTest.5.2 { 621 sqlite3_backup B db2 main db main 622 B step 8 623 } {SQLITE_OK} 624 do_test backup-5.$iTest.5.3 { 625 execsql { 626 DELETE FROM t1; 627 PRAGMA incremental_vacuum; 628 } $writer 629 B step 50 630 } {SQLITE_DONE} 631 do_test backup-5.$iTest.5.4 { 632 B finish 633 } {SQLITE_OK} 634 integrity_check backup-5.$iTest.5.5 db2 635 test_contents backup-5.$iTest.5.6 db main db2 main 636 catch {db close} 637 catch {db2 close} 638 catch {db3 close} 639} 640# 641# End of backup-5.* tests. 642#--------------------------------------------------------------------- 643 644#--------------------------------------------------------------------- 645# Test the sqlite3_backup_remaining() and backup_pagecount() APIs. 646# 647do_test backup-6.1 { 648 catch { forcedelete test.db } 649 catch { forcedelete test2.db } 650 sqlite3 db test.db 651 sqlite3 db2 test2.db 652 execsql { 653 BEGIN; 654 CREATE TABLE t1(a, b); 655 CREATE INDEX i1 ON t1(a, b); 656 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 657 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 658 INSERT INTO t1 VALUES(3, randstr(1000,1000)); 659 INSERT INTO t1 VALUES(4, randstr(1000,1000)); 660 INSERT INTO t1 VALUES(5, randstr(1000,1000)); 661 COMMIT; 662 } 663} {} 664do_test backup-6.2 { 665 set nTotal [expr {[file size test.db]/1024}] 666 sqlite3_backup B db2 main db main 667 B step 1 668} {SQLITE_OK} 669do_test backup-6.3 { 670 B pagecount 671} $nTotal 672do_test backup-6.4 { 673 B remaining 674} [expr $nTotal-1] 675do_test backup-6.5 { 676 B step 5 677 list [B remaining] [B pagecount] 678} [list [expr $nTotal-6] $nTotal] 679do_test backup-6.6 { 680 execsql { CREATE TABLE t2(a PRIMARY KEY, b) } 681 B step 1 682 list [B remaining] [B pagecount] 683} [list [expr $nTotal-5] [expr $nTotal+2]] 684 685do_test backup-6.X { 686 B finish 687} {SQLITE_OK} 688 689catch {db close} 690catch {db2 close} 691 692#--------------------------------------------------------------------- 693# Test cases backup-7.* test that SQLITE_BUSY and SQLITE_LOCKED errors 694# are returned correctly: 695# 696# backup-7.1.*: Source database is externally locked (return SQLITE_BUSY). 697# 698# backup-7.2.*: Attempt to step the backup process while a 699# write-transaction is underway on the source pager (return 700# SQLITE_LOCKED). 701# 702# backup-7.3.*: Destination database is externally locked (return SQLITE_BUSY). 703# 704do_test backup-7.0 { 705 catch { forcedelete test.db } 706 catch { forcedelete test2.db } 707 sqlite3 db2 test2.db 708 sqlite3 db test.db 709 execsql { 710 CREATE TABLE t1(a, b); 711 CREATE INDEX i1 ON t1(a, b); 712 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 713 INSERT INTO t1 SELECT a+ 1, randstr(1000,1000) FROM t1; 714 INSERT INTO t1 SELECT a+ 2, randstr(1000,1000) FROM t1; 715 INSERT INTO t1 SELECT a+ 4, randstr(1000,1000) FROM t1; 716 INSERT INTO t1 SELECT a+ 8, randstr(1000,1000) FROM t1; 717 INSERT INTO t1 SELECT a+16, randstr(1000,1000) FROM t1; 718 INSERT INTO t1 SELECT a+32, randstr(1000,1000) FROM t1; 719 INSERT INTO t1 SELECT a+64, randstr(1000,1000) FROM t1; 720 } 721} {} 722 723do_test backup-7.1.1 { 724 sqlite3_backup B db2 main db main 725 B step 5 726} {SQLITE_OK} 727do_test backup-7.1.2 { 728 sqlite3 db3 test.db 729 execsql { BEGIN EXCLUSIVE } db3 730 B step 5 731} {SQLITE_BUSY} 732do_test backup-7.1.3 { 733 execsql { ROLLBACK } db3 734 B step 5 735} {SQLITE_OK} 736do_test backup-7.2.1 { 737 execsql { 738 BEGIN; 739 INSERT INTO t1 VALUES(1, 4); 740 } 741} {} 742do_test backup-7.2.2 { 743 B step 5000 744} {SQLITE_BUSY} 745do_test backup-7.2.3 { 746 execsql { ROLLBACK } 747 B step 5000 748} {SQLITE_DONE} 749do_test backup-7.2.4 { 750 B finish 751} {SQLITE_OK} 752test_contents backup-7.2.5 db main db2 main 753integrity_check backup-7.3.6 db2 754 755do_test backup-7.3.1 { 756 db2 close 757 db3 close 758 forcedelete test2.db 759 sqlite3 db2 test2.db 760 sqlite3 db3 test2.db 761 762 sqlite3_backup B db2 main db main 763 execsql { BEGIN ; CREATE TABLE t2(a, b); } db3 764 765 B step 5 766} {SQLITE_BUSY} 767do_test backup-7.3.2 { 768 execsql { COMMIT } db3 769 B step 5000 770} {SQLITE_DONE} 771do_test backup-7.3.3 { 772 B finish 773} {SQLITE_OK} 774test_contents backup-7.3.4 db main db2 main 775integrity_check backup-7.3.5 db2 776catch { db2 close } 777catch { db3 close } 778 779#----------------------------------------------------------------------- 780# The following tests, backup-8.*, test attaching multiple backup 781# processes to the same source database. Also, reading from the source 782# database while a read transaction is active. 783# 784# These tests reuse the database "test.db" left over from backup-7.*. 785# 786do_test backup-8.1 { 787 catch { forcedelete test2.db } 788 catch { forcedelete test3.db } 789 sqlite3 db2 test2.db 790 sqlite3 db3 test3.db 791 792 sqlite3_backup B2 db2 main db main 793 sqlite3_backup B3 db3 main db main 794 list [B2 finish] [B3 finish] 795} {SQLITE_OK SQLITE_OK} 796do_test backup-8.2 { 797 sqlite3_backup B3 db3 main db main 798 sqlite3_backup B2 db2 main db main 799 list [B2 finish] [B3 finish] 800} {SQLITE_OK SQLITE_OK} 801do_test backup-8.3 { 802 sqlite3_backup B2 db2 main db main 803 sqlite3_backup B3 db3 main db main 804 B2 step 5 805} {SQLITE_OK} 806do_test backup-8.4 { 807 execsql { 808 BEGIN; 809 SELECT * FROM sqlite_master; 810 } 811 B3 step 5 812} {SQLITE_OK} 813do_test backup-8.5 { 814 list [B3 step 5000] [B3 finish] 815} {SQLITE_DONE SQLITE_OK} 816do_test backup-8.6 { 817 list [B2 step 5000] [B2 finish] 818} {SQLITE_DONE SQLITE_OK} 819test_contents backup-8.7 db main db2 main 820test_contents backup-8.8 db main db3 main 821do_test backup-8.9 { 822 execsql { PRAGMA lock_status } 823} {main shared temp closed} 824do_test backup-8.10 { 825 execsql COMMIT 826} {} 827catch { db2 close } 828catch { db3 close } 829 830#----------------------------------------------------------------------- 831# The following tests, backup-9.*, test that: 832# 833# * Passing 0 as an argument to sqlite3_backup_step() means no pages 834# are backed up (backup-9.1.*), and 835# * Passing a negative value as an argument to sqlite3_backup_step() means 836# all pages are backed up (backup-9.2.*). 837# 838# These tests reuse the database "test.db" left over from backup-7.*. 839# 840do_test backup-9.1.1 { 841 sqlite3 db2 test2.db 842 sqlite3_backup B db2 main db main 843 B step 1 844} {SQLITE_OK} 845do_test backup-9.1.2 { 846 set nRemaining [B remaining] 847 expr {$nRemaining>100} 848} {1} 849do_test backup-9.1.3 { 850 B step 0 851} {SQLITE_OK} 852do_test backup-9.1.4 { 853 B remaining 854} $nRemaining 855 856do_test backup-9.2.1 { 857 B step -1 858} {SQLITE_DONE} 859do_test backup-9.2.2 { 860 B remaining 861} {0} 862do_test backup-9.2.3 { 863 B finish 864} {SQLITE_OK} 865catch {db2 close} 866 867ifcapable memorymanage { 868 db close 869 forcedelete test.db 870 forcedelete bak.db 871 872 sqlite3 db test.db 873 sqlite3 db2 test.db 874 sqlite3 db3 bak.db 875 876 do_test backup-10.1.1 { 877 execsql { 878 BEGIN; 879 CREATE TABLE t1(a, b); 880 INSERT INTO t1 VALUES(1, randstr(1000,1000)); 881 INSERT INTO t1 VALUES(2, randstr(1000,1000)); 882 INSERT INTO t1 VALUES(3, randstr(1000,1000)); 883 INSERT INTO t1 VALUES(4, randstr(1000,1000)); 884 INSERT INTO t1 VALUES(5, randstr(1000,1000)); 885 CREATE INDEX i1 ON t1(a, b); 886 COMMIT; 887 } 888 } {} 889 do_test backup-10.1.2 { 890 sqlite3_backup B db3 main db2 main 891 B step 5 892 } {SQLITE_OK} 893 do_test backup-10.1.3 { 894 execsql { 895 UPDATE t1 SET b = randstr(500,500); 896 } 897 } {} 898 sqlite3_release_memory [expr 1024*1024] 899 do_test backup-10.1.3 { 900 B step 50 901 } {SQLITE_DONE} 902 do_test backup-10.1.4 { 903 B finish 904 } {SQLITE_OK} 905 do_test backup-10.1.5 { 906 execsql { PRAGMA integrity_check } db3 907 } {ok} 908 909 db2 close 910 db3 close 911} 912 913 914#----------------------------------------------------------------------- 915# Test that if the database is written to via the same database handle being 916# used as the source by a backup operation: 917# 918# 10.1.*: If the db is in-memory, the backup is restarted. 919# 10.2.*: If the db is a file, the backup is not restarted. 920# 921db close 922forcedelete test.db test.db-journal 923foreach {tn file rc} { 924 1 test.db SQLITE_DONE 925 2 :memory: SQLITE_OK 926} { 927 do_test backup-10.$tn.1 { 928 sqlite3 db $file 929 execsql { 930 CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB); 931 BEGIN; 932 INSERT INTO t1 VALUES(NULL, randomblob(200)); 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 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1; 941 COMMIT; 942 SELECT count(*) FROM t1; 943 } 944 } {256} 945 946 do_test backup-10.$tn.2 { 947 set pgs [execsql {pragma page_count}] 948 expr {$pgs > 50 && $pgs < 75} 949 } {1} 950 951 do_test backup-10.$tn.3 { 952 forcedelete bak.db bak.db-journal 953 sqlite3 db2 bak.db 954 sqlite3_backup B db2 main db main 955 B step 50 956 } {SQLITE_OK} 957 958 do_test backup-10.$tn.4 { 959 execsql { UPDATE t1 SET b = randomblob(200) WHERE a IN (1, 250) } 960 } {} 961 962 do_test backup-10.$tn.5 { 963 B step 50 964 } $rc 965 966 do_test backup-10.$tn.6 { 967 B finish 968 } {SQLITE_OK} 969 970 db2 close 971} 972 973finish_test 974