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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 do_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 47 proc data_checksum {db file} { $db one "SELECT md5sum(a, b) FROM ${file}.t1" } 48 proc 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 55 do_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 # 71 test_contents backup-1.2 db main db main 72 73 # Check that it is possible to create and finish backup operations. 74 # 75 do_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} 80 do_test backup-1.3.2 { 81 B finish 82 } {SQLITE_OK} 83 do_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 # 90 do_test backup-1.4.1 { 91 sqlite3_backup B db2 main db main 92 } {B} 93 do_test backup-1.4.2 { 94 B step 200 95 } {SQLITE_DONE} 96 do_test backup-1.4.3 { 97 B finish 98 } {SQLITE_OK} 99 do_test backup-1.4.4 { 100 info commands B 101 } {} 102 test_contents backup-1.4.5 db2 main db main 103 db close 104 db2 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 # 146 set iTest 1 147 foreach zSrcFile {test.db :memory:} { 148 foreach zDestFile {test2.db :memory:} { 149 foreach 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 }] { 166 foreach rows_dest {0 3 10} { 167 foreach pgsz_dest {512 1024 2048} { 168 foreach 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 # 277 set iTest 1 278 foreach nSrcPg {10 64 65 66 100} { 279 foreach nDestRow {10 100} { 280 foreach 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 #-------------------------------------------------------------------- 329 do_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 352 do_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 # 378 sqlite3 db test.db 379 sqlite3 db2 test2.db 380 381 do_test backup-4.1.1 { 382 catch { sqlite3_backup B db aux db2 main } 383 } {1} 384 do_test backup-4.1.2 { 385 sqlite3_errmsg db 386 } {unknown database aux} 387 do_test backup-4.1.3 { 388 catch { sqlite3_backup B db main db2 aux } 389 } {1} 390 do_test backup-4.1.4 { 391 sqlite3_errmsg db 392 } {unknown database aux} 393 394 do_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} 407 do_test backup-4.2.2 { 408 catchsql { DETACH aux2 } db2 409 } {1 {database aux2 is locked}} 410 do_test backup-4.2.3 { 411 B step 50 412 } {SQLITE_DONE} 413 do_test backup-4.2.4 { 414 B finish 415 } {SQLITE_OK} 416 417 do_test backup-4.3.1 { 418 sqlite3_backup B db aux1 db2 aux2 419 } {B} 420 do_test backup-4.3.2 { 421 db2 cache flush 422 sqlite3_close db2 423 } {SQLITE_BUSY} 424 do_test backup-4.3.3 { 425 sqlite3_errmsg db2 426 } {unable to close due to unfinished backup operation} 427 do_test backup-4.3.4 { 428 B step 50 429 } {SQLITE_DONE} 430 do_test backup-4.3.5 { 431 B finish 432 } {SQLITE_OK} 433 434 do_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}} 438 db close 439 db2 close 440 441 do_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} 456 do_test backup-4.5.2 { 457 B step 5000 458 } {SQLITE_READONLY} 459 do_test backup-4.5.3 { 460 B finish 461 } {SQLITE_READONLY} 462 463 db close 464 db2 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 # 493 set iTest 0 494 file delete -force bak.db-wal 495 foreach {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 # 646 do_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 } {} 663 do_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} 668 do_test backup-6.3 { 669 B pagecount 670 } $nTotal 671 do_test backup-6.4 { 672 B remaining 673 } [expr $nTotal-1] 674 do_test backup-6.5 { 675 B step 5 676 list [B remaining] [B pagecount] 677 } [list [expr $nTotal-6] $nTotal] 678 do_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 684 do_test backup-6.X { 685 B finish 686 } {SQLITE_OK} 687 688 catch {db close} 689 catch {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 # 703 do_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 722 do_test backup-7.1.1 { 723 sqlite3_backup B db2 main db main 724 B step 5 725 } {SQLITE_OK} 726 do_test backup-7.1.2 { 727 sqlite3 db3 test.db 728 execsql { BEGIN EXCLUSIVE } db3 729 B step 5 730 } {SQLITE_BUSY} 731 do_test backup-7.1.3 { 732 execsql { ROLLBACK } db3 733 B step 5 734 } {SQLITE_OK} 735 do_test backup-7.2.1 { 736 execsql { 737 BEGIN; 738 INSERT INTO t1 VALUES(1, 4); 739 } 740 } {} 741 do_test backup-7.2.2 { 742 B step 5000 743 } {SQLITE_BUSY} 744 do_test backup-7.2.3 { 745 execsql { ROLLBACK } 746 B step 5000 747 } {SQLITE_DONE} 748 do_test backup-7.2.4 { 749 B finish 750 } {SQLITE_OK} 751 test_contents backup-7.2.5 db main db2 main 752 integrity_check backup-7.3.6 db2 753 754 do_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} 766 do_test backup-7.3.2 { 767 execsql { COMMIT } db3 768 B step 5000 769 } {SQLITE_DONE} 770 do_test backup-7.3.3 { 771 B finish 772 } {SQLITE_OK} 773 test_contents backup-7.3.4 db main db2 main 774 integrity_check backup-7.3.5 db2 775 catch { db2 close } 776 catch { 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 # 785 do_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} 795 do_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} 800 do_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} 805 do_test backup-8.4 { 806 execsql { 807 BEGIN; 808 SELECT * FROM sqlite_master; 809 } 810 B3 step 5 811 } {SQLITE_OK} 812 do_test backup-8.5 { 813 list [B3 step 5000] [B3 finish] 814 } {SQLITE_DONE SQLITE_OK} 815 do_test backup-8.6 { 816 list [B2 step 5000] [B2 finish] 817 } {SQLITE_DONE SQLITE_OK} 818 test_contents backup-8.7 db main db2 main 819 test_contents backup-8.8 db main db3 main 820 do_test backup-8.9 { 821 execsql { PRAGMA lock_status } 822 } {main shared temp closed} 823 do_test backup-8.10 { 824 execsql COMMIT 825 } {} 826 catch { db2 close } 827 catch { 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 # 839 do_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} 844 do_test backup-9.1.2 { 845 set nRemaining [B remaining] 846 expr {$nRemaining>100} 847 } {1} 848 do_test backup-9.1.3 { 849 B step 0 850 } {SQLITE_OK} 851 do_test backup-9.1.4 { 852 B remaining 853 } $nRemaining 854 855 do_test backup-9.2.1 { 856 B step -1 857 } {SQLITE_DONE} 858 do_test backup-9.2.2 { 859 B remaining 860 } {0} 861 do_test backup-9.2.3 { 862 B finish 863 } {SQLITE_OK} 864 catch {db2 close} 865 866 ifcapable 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 # 920 db close 921 file delete -force test.db test.db-journal 922 foreach {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 972 finish_test 973
