3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the sqlite3_backup_XXX API.
14 # $Id: backup.test,v 1.11 2009/06/05 17:09:12 drh Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
21 #---------------------------------------------------------------------
24 # backup-1.*: Warm-body tests.
26 # backup-2.*: Test backup under various conditions. To and from in-memory
27 # databases. To and from empty/populated databases. etc.
29 # backup-3.*: Verify that the locking-page (pending byte page) is handled.
31 # backup-4.*: Test various error conditions.
33 # backup-5.*: Test the source database being modified during a backup.
35 # backup-6.*: Test the backup_remaining() and backup_pagecount() APIs.
37 # backup-7.*: Test SQLITE_BUSY and SQLITE_LOCKED errors.
39 # backup-8.*: Test multiple simultaneous backup operations.
41 # backup-9.*: Test that passing a negative argument to backup_step() is
42 # interpreted as "copy the whole file".
44 # backup-10.*: Test writing the source database mid backup.
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]
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));
69 # Sanity check to verify that the [test_contents] proc works.
71 test_contents backup-1.2 db main db main
73 # Check that it is possible to create and finish backup operations.
75 do_test backup-1.3.1 {
78 sqlite3_backup B db2 main db main
80 do_test backup-1.3.2 {
83 do_test backup-1.3.3 {
87 # Simplest backup operation. Backup test.db to test2.db. test2.db is
88 # initially empty. test.db uses the default page size.
90 do_test backup-1.4.1 {
91 sqlite3_backup B db2 main db main
93 do_test backup-1.4.2 {
96 do_test backup-1.4.3 {
99 do_test backup-1.4.4 {
102 test_contents backup-1.4.5 db2 main db main
106 # End of backup-1.* tests.
107 #---------------------------------------------------------------------
110 #---------------------------------------------------------------------
111 # The following tests, backup-2.*, are based on the following procedure:
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)
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
125 # * Source database is an in-memory database, OR
126 # * Source database is a file-backed database.
128 # * Target database is an in-memory database, OR
129 # * Target database is a file-backed database.
131 # * Destination database is a main file, OR
132 # * Destination database is an attached file, OR
133 # * Destination database is a temp database.
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.
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.
143 # * Each call to step copies a single page, OR
144 # * A single call to step copies the entire source database.
147 foreach zSrcFile {test.db :memory:} {
148 foreach zDestFile {test2.db :memory:} {
149 foreach zOpenScript [list {
151 sqlite3 db2 $zSrcFile
152 db2 eval "ATTACH '$zDestFile' AS bak"
157 sqlite3 db2 $zDestFile
162 sqlite3 db2 $zDestFile
166 foreach rows_dest {0 3 10} {
167 foreach pgsz_dest {1024} {
168 foreach nPagePerStep {1 200} {
170 # Open the databases.
171 catch { file delete -force -- test.db }
172 catch { file delete -force -- test2.db }
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).
179 set isMemDest [expr {
180 $zDestFile eq ":memory:" || $file_dest eq "temp" && $TEMP_STORE>=2
183 if { $isMemDest==0 || $pgsz_dest == 1024 } {
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"
191 # Set up the content of the source database.
193 PRAGMA page_size = 1024;
194 PRAGMA cache_size = 4000;
196 CREATE TABLE t1(a, b);
197 CREATE INDEX i1 ON t1(a, b);
198 INSERT INTO t1 VALUES(1, randstr(1000,1000));
199 INSERT INTO t1 VALUES(2, randstr(1000,1000));
200 INSERT INTO t1 VALUES(3, randstr(1000,1000));
201 INSERT INTO t1 VALUES(4, randstr(1000,1000));
202 INSERT INTO t1 VALUES(5, randstr(1000,1000));
208 # Set up the content of the target database.
209 execsql "PRAGMA ${file_dest}.page_size = ${pgsz_dest}" $db_dest
210 execsql "PRAGMA ${file_dest}.cache_size = 4000" $db_dest
211 if {$rows_dest != 0} {
213 CREATE TABLE ${file_dest}.t1(a, b);
214 CREATE INDEX ${file_dest}.i1 ON t1(a, b);
216 for {set ii 0} {$ii < $rows_dest} {incr ii} {
218 INSERT INTO ${file_dest}.t1 VALUES(1, randstr(1000,1000))
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"} {}
230 # Run integrity check on the backup.
231 do_test backup-2.$iTest.2 {
232 execsql "PRAGMA ${file_dest}.integrity_check" $db_dest
235 test_contents backup-2.$iTest.3 db main $db_dest $file_dest
245 # End of backup-2.* tests.
246 #---------------------------------------------------------------------
249 #---------------------------------------------------------------------
250 # The following tests, backup-4.*, test various error conditions:
252 # backup-4.1.*: Test invalid database names.
254 # backup-4.2.*: Test that the source database cannot be detached while
255 # a backup is in progress.
257 # backup-4.3.*: Test that the source database handle cannot be closed
258 # while a backup is in progress.
260 # backup-4.4.*: Test an attempt to specify the same handle for the
261 # source and destination databases.
263 # backup-4.5.*: Test that an in-memory destination with a different
264 # page-size to the source database is an error.
269 do_test backup-4.1.1 {
270 catch { sqlite3_backup B db aux db2 main }
272 do_test backup-4.1.2 {
274 } {unknown database aux}
275 do_test backup-4.1.3 {
276 catch { sqlite3_backup B db main db2 aux }
278 do_test backup-4.1.4 {
280 } {unknown database aux}
282 do_test backup-4.2.1 {
283 catch { file delete -force test3.db }
284 catch { file delete -force test4.db }
286 ATTACH 'test3.db' AS aux1;
287 CREATE TABLE aux1.t1(a, b);
290 ATTACH 'test4.db' AS aux2;
291 CREATE TABLE aux2.t2(a, b);
293 sqlite3_backup B db aux1 db2 aux2
295 do_test backup-4.2.2 {
296 catchsql { DETACH aux2 } db2
297 } {1 {database aux2 is locked}}
298 do_test backup-4.2.3 {
301 do_test backup-4.2.4 {
305 do_test backup-4.3.1 {
306 sqlite3_backup B db aux1 db2 aux2
308 do_test backup-4.3.2 {
312 do_test backup-4.3.3 {
314 } {unable to close due to unfinished backup operation}
315 do_test backup-4.3.4 {
318 do_test backup-4.3.5 {
322 do_test backup-4.4.1 {
323 set rc [catch {sqlite3_backup B db main db aux1}]
324 list $rc [sqlite3_errcode db] [sqlite3_errmsg db]
325 } {1 SQLITE_ERROR {source and destination must be distinct}}
329 do_test backup-4.5.1 {
330 catch { file delete -force -- test.db }
334 CREATE TABLE t1(a, b);
335 INSERT INTO t1 VALUES(1, 2);
338 PRAGMA page_size = 4096;
339 CREATE TABLE t2(a, b);
340 INSERT INTO t2 VALUES(3, 4);
342 sqlite3_backup B db2 main db main
344 do_test backup-4.5.2 {
347 do_test backup-4.5.3 {
354 # End of backup-5.* tests.
355 #---------------------------------------------------------------------
357 #---------------------------------------------------------------------
358 # The following tests, backup-5.*, test that the backup works properly
359 # when the source database is modified during the backup. Test cases
360 # are organized as follows:
362 # backup-5.x.1.*: Nothing special. Modify the database mid-backup.
364 # backup-5.x.2.*: Modify the database mid-backup so that one or more
365 # pages are written out due to cache stress. Then
366 # rollback the transaction.
368 # backup-5.x.3.*: Database is vacuumed.
370 # backup-5.x.4.*: Database is vacuumed and the page-size modified.
372 # backup-5.x.5.*: Database is shrunk via incr-vacuum.
374 # Each test is run three times, in the following configurations:
376 # 1) Backing up file-to-file. The writer writes via an external pager.
377 # 2) Backing up file-to-file. The writer writes via the same pager as
378 # is used by the backup operation.
379 # 3) Backing up memory-to-file.
382 file delete -force bak.db-wal
383 foreach {writer file} {db test.db db :memory:} {
385 catch { file delete -force bak.db }
387 catch { file delete -force $file }
391 do_test backup-5.$iTest.1.1 {
394 CREATE TABLE t1(a, b);
395 CREATE INDEX i1 ON t1(a, b);
396 INSERT INTO t1 VALUES(1, randstr(1000,1000));
397 INSERT INTO t1 VALUES(2, randstr(1000,1000));
398 INSERT INTO t1 VALUES(3, randstr(1000,1000));
399 INSERT INTO t1 VALUES(4, randstr(1000,1000));
400 INSERT INTO t1 VALUES(5, randstr(1000,1000));
403 expr {[execsql {PRAGMA page_count}] > 10}
405 do_test backup-5.$iTest.1.2 {
406 sqlite3_backup B db2 main db main
409 do_test backup-5.$iTest.1.3 {
410 execsql { UPDATE t1 SET a = a + 1 } $writer
413 do_test backup-5.$iTest.1.4 {
416 integrity_check backup-5.$iTest.1.5 db2
417 test_contents backup-5.$iTest.1.6 db main db2 main
419 do_test backup-5.$iTest.2.1 {
421 PRAGMA cache_size = 10;
423 INSERT INTO t1 SELECT '', randstr(1000,1000) FROM t1;
424 INSERT INTO t1 SELECT '', randstr(1000,1000) FROM t1;
425 INSERT INTO t1 SELECT '', randstr(1000,1000) FROM t1;
426 INSERT INTO t1 SELECT '', randstr(1000,1000) FROM t1;
430 do_test backup-5.$iTest.2.2 {
431 sqlite3_backup B db2 main db main
434 do_test backup-5.$iTest.2.3 {
437 UPDATE t1 SET a = a + 1;
442 do_test backup-5.$iTest.2.4 {
445 integrity_check backup-5.$iTest.2.5 db2
446 test_contents backup-5.$iTest.2.6 db main db2 main
448 do_test backup-5.$iTest.3.1 {
449 execsql { UPDATE t1 SET b = randstr(1000,1000) }
451 do_test backup-5.$iTest.3.2 {
452 sqlite3_backup B db2 main db main
455 do_test backup-5.$iTest.3.3 {
456 execsql { VACUUM } $writer
459 do_test backup-5.$iTest.3.4 {
462 integrity_check backup-5.$iTest.3.5 db2
463 test_contents backup-5.$iTest.3.6 db main db2 main
465 do_test backup-5.$iTest.4.1 {
466 execsql { UPDATE t1 SET b = randstr(1000,1000) }
468 do_test backup-5.$iTest.4.2 {
469 sqlite3_backup B db2 main db main
472 do_test backup-5.$iTest.4.3 {
474 PRAGMA page_size = 2048;
479 do_test backup-5.$iTest.4.4 {
482 integrity_check backup-5.$iTest.4.5 db2
483 test_contents backup-5.$iTest.4.6 db main db2 main
488 catch { file delete -force -- bak.db }
490 catch { file delete -force -- $file }
493 do_test backup-5.$iTest.5.1 {
495 PRAGMA auto_vacuum = incremental;
497 CREATE TABLE t1(a, b);
498 CREATE INDEX i1 ON t1(a, b);
499 INSERT INTO t1 VALUES(1, randstr(1000,1000));
500 INSERT INTO t1 VALUES(2, randstr(1000,1000));
501 INSERT INTO t1 VALUES(3, randstr(1000,1000));
502 INSERT INTO t1 VALUES(4, randstr(1000,1000));
503 INSERT INTO t1 VALUES(5, randstr(1000,1000));
507 do_test backup-5.$iTest.5.2 {
508 sqlite3_backup B db2 main db main
511 do_test backup-5.$iTest.5.3 {
514 PRAGMA incremental_vacuum;
518 do_test backup-5.$iTest.5.4 {
521 integrity_check backup-5.$iTest.5.5 db2
527 # End of backup-5.* tests.
528 #---------------------------------------------------------------------
530 #---------------------------------------------------------------------
531 # Test the sqlite3_backup_remaining() and backup_pagecount() APIs.
534 catch { file delete -force -- test.db }
535 catch { file delete -force -- test2.db }
540 CREATE TABLE t1(a, b);
541 CREATE INDEX i1 ON t1(a, b);
542 INSERT INTO t1 VALUES(1, randstr(1000,1000));
543 INSERT INTO t1 VALUES(2, randstr(1000,1000));
544 INSERT INTO t1 VALUES(3, randstr(1000,1000));
545 INSERT INTO t1 VALUES(4, randstr(1000,1000));
546 INSERT INTO t1 VALUES(5, randstr(1000,1000));
551 set nTotal [expr {[file size test.db]/1024}]
552 sqlite3_backup B db2 main db main
563 list [B remaining] [B pagecount]
564 } [list [expr $nTotal-6] $nTotal]
566 execsql { CREATE TABLE t2(a PRIMARY KEY, b) }
568 list [B remaining] [B pagecount]
569 } [list [expr $nTotal-5] [expr $nTotal+2]]
578 #---------------------------------------------------------------------
579 # Test cases backup-7.* test that SQLITE_BUSY and SQLITE_LOCKED errors
580 # are returned correctly:
582 # backup-7.1.*: Source database is externally locked (return SQLITE_BUSY).
584 # backup-7.2.*: Attempt to step the backup process while a
585 # write-transaction is underway on the source pager (return
588 # backup-7.3.*: Destination database is externally locked (return SQLITE_BUSY).
591 catch { file delete -force -- test.db }
592 catch { file delete -force -- test2.db }
597 CREATE TABLE t1(a, b);
598 CREATE INDEX i1 ON t1(a, b);
599 INSERT INTO t1 VALUES(1, randstr(1000,1000));
600 INSERT INTO t1 SELECT a+ 1, randstr(1000,1000) FROM t1;
601 INSERT INTO t1 SELECT a+ 2, randstr(1000,1000) FROM t1;
602 INSERT INTO t1 SELECT a+ 4, randstr(1000,1000) FROM t1;
603 INSERT INTO t1 SELECT a+ 8, randstr(1000,1000) FROM t1;
604 INSERT INTO t1 SELECT a+16, randstr(1000,1000) FROM t1;
605 INSERT INTO t1 SELECT a+32, randstr(1000,1000) FROM t1;
606 INSERT INTO t1 SELECT a+64, randstr(1000,1000) FROM t1;
610 do_test backup-7.1.1 {
611 sqlite3_backup B db2 main db main
614 do_test backup-7.2.1 {
617 INSERT INTO t1 VALUES(1, 4);
620 do_test backup-7.2.3 {
624 do_test backup-7.2.4 {
627 test_contents backup-7.2.5 db main db2 main
628 integrity_check backup-7.3.6 db2
630 do_test backup-7.3.1 {
633 file delete -force -- test2.db
637 sqlite3_backup B db2 main db main
638 execsql { BEGIN ; CREATE TABLE t2(a, b); COMMIT; } db3
642 do_test backup-7.3.2 {
646 do_test backup-7.3.3 {
649 test_contents backup-7.3.4 db main db2 main
650 integrity_check backup-7.3.5 db2
653 #-----------------------------------------------------------------------
654 # The following tests, backup-8.*, test attaching multiple backup
655 # processes to the same source database. Also, reading from the source
656 # database while a read transaction is active.
658 # These tests reuse the database "test.db" left over from backup-7.*.
661 catch { file delete -force -- test2.db }
662 catch { file delete -force -- test3.db }
666 sqlite3_backup B2 db2 main db main
667 sqlite3_backup B3 db3 main db main
668 list [B2 finish] [B3 finish]
669 } {SQLITE_OK SQLITE_OK}
671 sqlite3_backup B3 db3 main db main
672 sqlite3_backup B2 db2 main db main
673 list [B2 finish] [B3 finish]
674 } {SQLITE_OK SQLITE_OK}
676 sqlite3_backup B2 db2 main db main
677 sqlite3_backup B3 db3 main db main
683 SELECT * FROM sqlite_master;
688 list [B3 step 5000] [B3 finish]
689 } {SQLITE_DONE SQLITE_OK}
691 list [B2 step 5000] [B2 finish]
692 } {SQLITE_DONE SQLITE_OK}
693 test_contents backup-8.7 db main db2 main
694 test_contents backup-8.8 db main db3 main
696 execsql { PRAGMA lock_status }
697 } {main shared temp closed}
698 do_test backup-8.10 {
704 #-----------------------------------------------------------------------
705 # The following tests, backup-9.*, test that:
707 # * Passing 0 as an argument to sqlite3_backup_step() means no pages
708 # are backed up (backup-9.1.*), and
709 # * Passing a negative value as an argument to sqlite3_backup_step() means
710 # all pages are backed up (backup-9.2.*).
712 # These tests reuse the database "test.db" left over from backup-7.*.
714 do_test backup-9.1.1 {
716 sqlite3_backup B db2 main db main
719 do_test backup-9.1.2 {
720 set nRemaining [B remaining]
721 expr {$nRemaining>100}
723 do_test backup-9.1.3 {
726 do_test backup-9.1.4 {
730 do_test backup-9.2.1 {
733 do_test backup-9.2.2 {
736 do_test backup-9.2.3 {
741 ifcapable memorymanage {
743 file delete -force -- test.db
744 file delete -force -- bak.db
750 do_test backup-10.1.1 {
753 CREATE TABLE t1(a, b);
754 INSERT INTO t1 VALUES(1, randstr(1000,1000));
755 INSERT INTO t1 VALUES(2, randstr(1000,1000));
756 INSERT INTO t1 VALUES(3, randstr(1000,1000));
757 INSERT INTO t1 VALUES(4, randstr(1000,1000));
758 INSERT INTO t1 VALUES(5, randstr(1000,1000));
759 CREATE INDEX i1 ON t1(a, b);
763 do_test backup-10.1.2 {
764 sqlite3_backup B db3 main db2 main
767 do_test backup-10.1.3 {
769 UPDATE t1 SET b = randstr(500,500);
772 sqlite3_release_memory [expr 1024*1024]
773 do_test backup-10.1.3 {
776 do_test backup-10.1.4 {
779 do_test backup-10.1.5 {
780 execsql { PRAGMA integrity_check } db3
788 #-----------------------------------------------------------------------
789 # Test that if the database is written to via the same database handle being
790 # used as the source by a backup operation:
792 # 10.1.*: If the db is in-memory, the backup is restarted.
793 # 10.2.*: If the db is a file, the backup is restarted.
794 # 10.3.*: If the db is in-memory, and not updated, the backup is not
796 # 10.4.*: If the db is a file,and not updated, the backup is not
800 file delete -force -- test.db test.db-journal
801 foreach {tn file update rc} {
802 1 test.db 1 SQLITE_OK
803 2 :memory: 1 SQLITE_OK
804 1 test.db 0 SQLITE_DONE
805 2 :memory: 0 SQLITE_DONE
807 do_test backup-10.$tn.1 {
810 DROP TABLE IF EXISTS t1;
811 CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB);
813 INSERT INTO t1 VALUES(NULL, randomblob(200));
814 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1;
815 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1;
816 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1;
817 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1;
818 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1;
819 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1;
820 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1;
821 INSERT INTO t1 SELECT NULL, randomblob(200) FROM t1;
823 SELECT count(*) FROM t1;
827 do_test backup-10.$tn.3 {
828 file delete -force -- bak.db bak.db-journal
830 sqlite3_backup B db2 main db main
834 if { $update == 1 } {
835 do_test backup-10.$tn.4 {
836 execsql { UPDATE t1 SET b = randomblob(200) WHERE a IN (1, 250) }
840 do_test backup-10.$tn.5 {
844 do_test backup-10.$tn.6 {