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