wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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