| blob | 91f5c8e1e00c7959079e191e3f0647c95f445668 |
1 # 2007 April 26
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 incremental vacuum feature.
13 #
14 # Note: There are also some tests for incremental vacuum and IO
15 # errors in incrvacuum_ioerr.test.
16 #
17 # $Id: incrvacuum.test,v 1.23 2009/02/18 20:31:18 drh Exp $
19 set testdir [file dirname $argv0]
20 source $testdir/tester.tcl
22 # If this build of the library does not support auto-vacuum, omit this
23 # whole file.
24 ifcapable {!autovacuum || !pragma} {
25 finish_test
26 return
27 }
29 #---------------------------------------------------------------------
30 # Test the pragma on an empty database.
31 #
32 do_test incrvacuum-1.1 {
33 execsql {
34 pragma auto_vacuum;
35 }
36 } $sqlite_options(default_autovacuum)
37 do_test incrvacuum-1.2.0 {
38 # File size is sometimes 1 instead of 0 due to the hack we put in
39 # to work around ticket #3260. Search for comments on #3260 in
40 # os_unix.c.
41 expr {[file size test.db] > 1}
42 } {0}
43 do_test incrvacuum-1.2 {
44 # This command will create the database.
45 execsql {
46 pragma auto_vacuum = 'full';
47 pragma auto_vacuum;
48 }
49 } {1}
50 do_test incrvacuum-1.2.1 {
51 expr {[file size test.db] > 0}
52 } {1}
53 do_test incrvacuum-1.3 {
54 execsql {
55 pragma auto_vacuum = 'incremental';
56 pragma auto_vacuum;
57 }
58 } {2}
59 do_test incrvacuum-1.4 {
60 # In this case the invalid value is ignored and the auto_vacuum
61 # setting remains unchanged.
62 execsql {
63 pragma auto_vacuum = 'invalid';
64 pragma auto_vacuum;
65 }
66 } {2}
67 do_test incrvacuum-1.5 {
68 execsql {
69 pragma auto_vacuum = 1;
70 pragma auto_vacuum;
71 }
72 } {1}
73 do_test incrvacuum-1.6 {
74 execsql {
75 pragma auto_vacuum = '2';
76 pragma auto_vacuum;
77 }
78 } {2}
79 do_test incrvacuum-1.7 {
80 # Invalid value. auto_vacuum setting remains unchanged.
81 execsql {
82 pragma auto_vacuum = 5;
83 pragma auto_vacuum;
84 }
85 } {2}
87 #---------------------------------------------------------------------
88 # Test the pragma on a non-empty database. It is possible to toggle
89 # the connection between "full" and "incremental" mode, but not to
90 # change from either of these to "none", or from "none" to "full" or
91 # "incremental".
92 #
93 do_test incrvacuum-2.1 {
94 execsql {
95 pragma auto_vacuum = 1;
96 CREATE TABLE abc(a, b, c);
97 }
98 } {}
99 do_test incrvacuum-2.2 {
100 execsql {
101 pragma auto_vacuum = 'none';
102 pragma auto_vacuum;
103 }
104 } {1}
105 do_test incrvacuum-2.2.1 {
106 db close
107 sqlite3 db test.db
108 execsql {
109 pragma auto_vacuum;
110 }
111 } {1}
112 do_test incrvacuum-2.3 {
113 execsql {
114 pragma auto_vacuum = 'incremental';
115 pragma auto_vacuum;
116 }
117 } {2}
118 do_test incrvacuum-2.4 {
119 execsql {
120 pragma auto_vacuum = 'full';
121 pragma auto_vacuum;
122 }
123 } {1}
125 #---------------------------------------------------------------------
126 # Test that when the auto_vacuum mode is "incremental", the database
127 # does not shrink when pages are removed from it. But it does if
128 # the mode is set to "full".
129 #
130 do_test incrvacuum-3.1 {
131 execsql {
132 pragma auto_vacuum;
133 }
134 } {1}
135 do_test incrvacuum-3.2 {
136 set ::str [string repeat 1234567890 110]
137 execsql {
138 PRAGMA auto_vacuum = 2;
139 BEGIN;
140 CREATE TABLE tbl2(str);
141 INSERT INTO tbl2 VALUES($::str);
142 COMMIT;
143 }
144 # 5 pages:
145 #
146 # 1 -> database header
147 # 2 -> first back-pointer page
148 # 3 -> table abc
149 # 4 -> table tbl2
150 # 5 -> table tbl2 overflow page.
151 #
152 expr {[file size test.db] / 1024}
153 } {5}
154 do_test incrvacuum-3.3 {
155 execsql {
156 DROP TABLE abc;
157 DELETE FROM tbl2;
158 }
159 expr {[file size test.db] / 1024}
160 } {5}
161 do_test incrvacuum-3.4 {
162 execsql {
163 PRAGMA auto_vacuum = 1;
164 INSERT INTO tbl2 VALUES('hello world');
165 }
166 expr {[file size test.db] / 1024}
167 } {3}
169 #---------------------------------------------------------------------
170 # Try to run a very simple incremental vacuum. Also verify that
171 # PRAGMA incremental_vacuum is a harmless no-op against a database that
172 # does not support auto-vacuum.
173 #
174 do_test incrvacuum-4.1 {
175 set ::str [string repeat 1234567890 110]
176 execsql {
177 PRAGMA auto_vacuum = 2;
178 INSERT INTO tbl2 VALUES($::str);
179 CREATE TABLE tbl1(a, b, c);
180 }
181 expr {[file size test.db] / 1024}
182 } {5}
183 do_test incrvacuum-4.2 {
184 execsql {
185 DELETE FROM tbl2;
186 DROP TABLE tbl1;
187 }
188 expr {[file size test.db] / 1024}
189 } {5}
190 do_test incrvacuum-4.3 {
191 set ::nStep 0
192 db eval {pragma incremental_vacuum(10)} {
193 incr ::nStep
194 }
195 list [expr {[file size test.db] / 1024}] $::nStep
196 } {3 2}
198 #---------------------------------------------------------------------
199 # The following tests - incrvacuum-5.* - test incremental vacuum
200 # from within a transaction.
201 #
202 do_test incrvacuum-5.1.1 {
203 expr {[file size test.db] / 1024}
204 } {3}
205 do_test incrvacuum-5.1.2 {
206 execsql {
207 BEGIN;
208 DROP TABLE tbl2;
209 PRAGMA incremental_vacuum;
210 COMMIT;
211 }
212 expr {[file size test.db] / 1024}
213 } {1}
215 do_test incrvacuum-5.2.1 {
216 set ::str [string repeat abcdefghij 110]
217 execsql {
218 BEGIN;
219 CREATE TABLE tbl1(a);
220 INSERT INTO tbl1 VALUES($::str);
221 PRAGMA incremental_vacuum; -- this is a no-op.
222 COMMIT;
223 }
224 expr {[file size test.db] / 1024}
225 } {4}
226 do_test incrvacuum-5.2.2 {
227 set ::str [string repeat abcdefghij 110]
228 execsql {
229 BEGIN;
230 INSERT INTO tbl1 VALUES($::str);
231 INSERT INTO tbl1 SELECT * FROM tbl1;
232 DELETE FROM tbl1 WHERE oid%2; -- Put 2 overflow pages on free-list.
233 COMMIT;
234 }
235 expr {[file size test.db] / 1024}
236 } {7}
237 do_test incrvacuum-5.2.3 {
238 execsql {
239 BEGIN;
240 PRAGMA incremental_vacuum; -- Vacuum up the two pages.
241 CREATE TABLE tbl2(b); -- Use one free page as a table root.
242 INSERT INTO tbl2 VALUES('a nice string');
243 COMMIT;
244 }
245 expr {[file size test.db] / 1024}
246 } {6}
247 do_test incrvacuum-5.2.4 {
248 execsql {
249 SELECT * FROM tbl2;
250 }
251 } {{a nice string}}
252 do_test incrvacuum-5.2.5 {
253 execsql {
254 DROP TABLE tbl1;
255 DROP TABLE tbl2;
256 PRAGMA incremental_vacuum;
257 }
258 expr {[file size test.db] / 1024}
259 } {1}
262 # Test cases incrvacuum-5.3.* use the following list as input data.
263 # Two new databases are opened, one with incremental vacuum enabled,
264 # the other with no auto-vacuum completely disabled. After executing
265 # each element of the following list on both databases, test that
266 # the integrity-check passes and the contents of each are identical.
267 #
268 set TestScriptList [list {
269 BEGIN;
270 CREATE TABLE t1(a, b);
271 CREATE TABLE t2(a, b);
272 CREATE INDEX t1_i ON t1(a);
273 CREATE INDEX t2_i ON t2(a);
274 } {
275 INSERT INTO t1 VALUES($::str1, $::str2);
276 INSERT INTO t1 VALUES($::str1||$::str2, $::str2||$::str1);
277 INSERT INTO t2 SELECT b, a FROM t1;
278 INSERT INTO t2 SELECT a, b FROM t1;
279 INSERT INTO t1 SELECT b, a FROM t2;
280 UPDATE t2 SET b = '';
281 PRAGMA incremental_vacuum;
282 } {
283 UPDATE t2 SET b = (SELECT b FROM t1 WHERE t1.oid = t2.oid);
284 PRAGMA incremental_vacuum;
285 } {
286 CREATE TABLE t3(a, b);
287 INSERT INTO t3 SELECT * FROM t2;
288 DROP TABLE t2;
289 PRAGMA incremental_vacuum;
290 } {
291 CREATE INDEX t3_i ON t3(a);
292 COMMIT;
293 } {
294 BEGIN;
295 DROP INDEX t3_i;
296 PRAGMA incremental_vacuum;
297 INSERT INTO t3 VALUES('hello', 'world');
298 ROLLBACK;
299 } {
300 INSERT INTO t3 VALUES('hello', 'world');
301 }
302 ]
304 # If this build omits subqueries, step 2 in the above list will not
305 # work. Replace it with "" in this case.
306 #
307 ifcapable !subquery { lset TestScriptList 2 "" }
309 # Compare the contents of databases $A and $B.
310 #
311 proc compare_dbs {A B tname} {
312 set tbl_list [execsql {
313 SELECT tbl_name FROM sqlite_master WHERE type = 'table'
314 } $A]
316 do_test ${tname}.1 [subst {
317 execsql {
318 SELECT tbl_name FROM sqlite_master WHERE type = 'table'
319 } $B
320 }] $tbl_list
322 set tn 1
323 foreach tbl $tbl_list {
324 set control [execsql "SELECT * FROM $tbl" $A]
325 do_test ${tname}.[incr tn] [subst {
326 execsql "SELECT * FROM $tbl" $B
327 }] $control
328 }
329 }
331 set ::str1 [string repeat abcdefghij 130]
332 set ::str2 [string repeat 1234567890 105]
334 forcedelete test1.db test1.db-journal test2.db test2.db-journal
335 sqlite3 db1 test1.db
336 sqlite3 db2 test2.db
337 execsql { PRAGMA auto_vacuum = 'none' } db1
338 execsql { PRAGMA auto_vacuum = 'incremental' } db2
340 set tn 1
341 foreach sql $::TestScriptList {
342 execsql $sql db1
343 execsql $sql db2
345 compare_dbs db1 db2 incrvacuum-5.3.${tn}
346 do_test incrvacuum-5.3.${tn}.integrity1 {
347 execsql { PRAGMA integrity_check; } db1
348 } {ok}
349 do_test incrvacuum-5.3.${tn}.integrity2 {
350 execsql { PRAGMA integrity_check; } db2
351 } {ok}
352 incr tn
353 }
354 db1 close
355 db2 close
356 #
357 # End of test cases 5.3.*
359 #---------------------------------------------------------------------
360 # The following tests - incrvacuum-6.* - test running incremental
361 # vacuum while another statement (a read) is being executed.
362 #
363 for {set jj 0} {$jj < 10} {incr jj} {
364 # Build some test data. Two tables are created in an empty
365 # database. tbl1 data is a contiguous block starting at page 5 (pages
366 # 3 and 4 are the table roots). tbl2 is a contiguous block starting
367 # right after tbl1.
368 #
369 # Then drop tbl1 so that when an incr vacuum is run the pages
370 # of tbl2 have to be moved to fill the gap.
371 #
372 do_test incrvacuum-6.${jj}.1 {
373 execsql {
374 DROP TABLE IF EXISTS tbl1;
375 DROP TABLE IF EXISTS tbl2;
376 PRAGMA incremental_vacuum;
377 CREATE TABLE tbl1(a, b);
378 CREATE TABLE tbl2(a, b);
379 BEGIN;
380 }
381 for {set ii 0} {$ii < 1000} {incr ii} {
382 db eval {INSERT INTO tbl1 VALUES($ii, $ii || $ii)}
383 }
384 execsql {
385 INSERT INTO tbl2 SELECT * FROM tbl1;
386 COMMIT;
387 DROP TABLE tbl1;
388 }
389 expr {[file size test.db] / 1024}
390 } {36}
392 # Run a linear scan query on tbl2. After reading ($jj*100) rows,
393 # run the incremental vacuum to shrink the database.
394 #
395 do_test incrvacuum-6.${jj}.2 {
396 set ::nRow 0
397 db eval {SELECT a FROM tbl2} {} {
398 if {$a == [expr $jj*100]} {
399 db eval {PRAGMA incremental_vacuum}
400 }
401 incr ::nRow
402 }
403 list [expr {[file size test.db] / 1024}] $nRow
404 } {19 1000}
405 }
407 #---------------------------------------------------------------------
408 # This test - incrvacuum-7.* - is to check that the database can be
409 # written in the middle of an incremental vacuum.
410 #
411 set ::iWrite 1
412 while 1 {
413 do_test incrvacuum-7.${::iWrite}.1 {
414 execsql {
415 DROP TABLE IF EXISTS tbl1;
416 DROP TABLE IF EXISTS tbl2;
417 PRAGMA incremental_vacuum;
418 CREATE TABLE tbl1(a, b);
419 CREATE TABLE tbl2(a, b);
420 BEGIN;
421 }
422 for {set ii 0} {$ii < 1000} {incr ii} {
423 db eval {INSERT INTO tbl1 VALUES($ii, $ii || $ii)}
424 }
425 execsql {
426 INSERT INTO tbl2 SELECT * FROM tbl1;
427 COMMIT;
428 DROP TABLE tbl1;
429 }
430 expr {[file size test.db] / 1024}
431 } {36}
433 do_test incrvacuum-7.${::iWrite}.2 {
434 set ::nRow 0
435 db eval {PRAGMA incremental_vacuum} {
436 incr ::nRow
437 if {$::nRow == $::iWrite} {
438 db eval {
439 CREATE TABLE tbl1(a, b);
440 INSERT INTO tbl1 VALUES('hello', 'world');
441 }
442 }
443 }
444 list [expr {[file size test.db] / 1024}]
445 } {20}
447 do_test incrvacuum-7.${::iWrite}.3 {
448 execsql {
449 SELECT * FROM tbl1;
450 }
451 } {hello world}
453 if {$::nRow == $::iWrite} break
454 incr ::iWrite
455 }
457 #---------------------------------------------------------------------
458 # This test - incrvacuum-8.* - is to check that nothing goes wrong
459 # with an incremental-vacuum if it is the first statement executed
460 # after an existing database is opened.
461 #
462 # At one point, this would always return SQLITE_SCHEMA (which
463 # causes an infinite loop in tclsqlite.c if using the Tcl interface).
464 #
465 do_test incrvacuum-8.1 {
466 db close
467 sqlite3 db test.db
468 execsql {
469 PRAGMA incremental_vacuum(50);
470 }
471 } {}
473 #---------------------------------------------------------------------
474 # At one point this test case was causing an assert() to fail.
475 #
476 do_test incrvacuum-9.1 {
477 db close
478 forcedelete test.db test.db-journal
479 sqlite3 db test.db
481 execsql {
482 PRAGMA auto_vacuum = 'incremental';
483 CREATE TABLE t1(a, b, c);
484 CREATE TABLE t2(a, b, c);
485 INSERT INTO t2 VALUES(randstr(500,500),randstr(500,500),randstr(500,500));
486 INSERT INTO t1 VALUES(1, 2, 3);
487 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
488 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
489 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
490 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
491 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
492 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
493 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
494 INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
495 }
496 } {}
498 do_test incrvacuum-9.2 {
499 execsql {
500 PRAGMA synchronous = 'OFF';
501 BEGIN;
502 UPDATE t1 SET a = a, b = b, c = c;
503 DROP TABLE t2;
504 PRAGMA incremental_vacuum(10);
505 ROLLBACK;
506 }
507 } {}
509 do_test incrvacuum-9.3 {
510 execsql {
511 PRAGMA cache_size = 10;
512 BEGIN;
513 UPDATE t1 SET a = a, b = b, c = c;
514 DROP TABLE t2;
515 PRAGMA incremental_vacuum(10);
516 ROLLBACK;
517 }
518 } {}
520 #---------------------------------------------------------------------
521 # Test that the parameter to the incremental_vacuum pragma works. That
522 # is, if the user executes "PRAGMA incremental_vacuum(N)", at most
523 # N pages are vacuumed.
524 #
525 do_test incrvacuum-10.1 {
526 execsql {
527 DROP TABLE t1;
528 DROP TABLE t2;
529 }
530 expr [file size test.db] / 1024
531 } {29}
533 do_test incrvacuum-10.2 {
534 execsql {
535 PRAGMA incremental_vacuum(1);
536 }
537 expr [file size test.db] / 1024
538 } {28}
540 do_test incrvacuum-10.3 {
541 execsql {
542 PRAGMA incremental_vacuum(5);
543 }
544 expr [file size test.db] / 1024
545 } {23}
547 do_test incrvacuum-10.4 {
548 execsql {
549 PRAGMA incremental_vacuum('1');
550 }
551 expr [file size test.db] / 1024
552 } {22}
554 do_test incrvacuum-10.5 {
555 execsql {
556 PRAGMA incremental_vacuum("+3");
557 }
558 expr [file size test.db] / 1024
559 } {19}
561 do_test incrvacuum-10.6 {
562 execsql {
563 PRAGMA incremental_vacuum = 1;
564 }
565 expr [file size test.db] / 1024
566 } {18}
568 do_test incrvacuum-10.7 {
569 # Use a really big number as an argument to incremetal_vacuum. Should
570 # be interpreted as "free all possible space".
571 execsql {
572 PRAGMA incremental_vacuum(2147483649);
573 }
574 expr [file size test.db] / 1024
575 } {1}
577 do_test incrvacuum-10.8 {
578 execsql {
579 CREATE TABLE t1(x);
580 INSERT INTO t1 VALUES(hex(randomblob(1000)));
581 DROP TABLE t1;
582 }
583 # A negative number means free all possible space.
584 execsql {
585 PRAGMA incremental_vacuum=-1;
586 }
587 expr [file size test.db] / 1024
588 } {1}
590 #----------------------------------------------------------------
591 # Test that if we set the auto_vacuum mode to 'incremental', then
592 # create a database, thereafter that database defaults to incremental
593 # vacuum mode.
594 #
595 db close
596 forcedelete test.db test.db-journal
597 sqlite3 db test.db
599 ifcapable default_autovacuum {
600 do_test incrvacuum-11.1-av-dflt-on {
601 execsql {
602 PRAGMA auto_vacuum;
603 }
604 } $AUTOVACUUM
605 } else {
606 do_test incrvacuum-11.1-av-dflt-off {
607 execsql {
608 PRAGMA auto_vacuum;
609 }
610 } {0}
611 }
612 do_test incrvacuum-11.2 {
613 execsql {
614 PRAGMA auto_vacuum = incremental;
615 }
616 } {}
617 do_test incrvacuum-11.3 {
618 execsql {
619 PRAGMA auto_vacuum;
620 }
621 } {2}
622 do_test incrvacuum-11.4 {
623 # The database has now been created.
624 expr {[file size test.db]>0}
625 } {1}
626 do_test incrvacuum-11.5 {
627 # Close and reopen the connection.
628 db close
629 sqlite3 db test.db
631 # Test we are still in incremental vacuum mode.
632 execsql { PRAGMA auto_vacuum; }
633 } {2}
634 do_test incrvacuum-11.6 {
635 execsql {
636 PRAGMA auto_vacuum = 'full';
637 PRAGMA auto_vacuum;
638 }
639 } {1}
640 do_test incrvacuum-11.7 {
641 # Close and reopen the connection.
642 db close
643 sqlite3 db test.db
645 # Test we are still in "full" auto-vacuum mode.
646 execsql { PRAGMA auto_vacuum; }
647 } {1}
649 #----------------------------------------------------------------------
650 # Special case: What happens if the database is locked when a "PRAGMA
651 # auto_vacuum = XXX" statement is executed.
652 #
653 db close
654 forcedelete test.db test.db-journal
655 sqlite3 db test.db
657 do_test incrvacuum-12.1 {
658 execsql {
659 PRAGMA auto_vacuum = 1;
660 }
661 expr {[file size test.db]>0}
662 } {1}
664 # Try to change the auto-vacuum from "full" to "incremental" while the
665 # database is locked. Nothing should change.
666 #
667 do_test incrvacuum-12.2 {
668 sqlite3 db2 test.db
669 execsql { BEGIN EXCLUSIVE; } db2
670 catchsql { PRAGMA auto_vacuum = 2; }
671 } {1 {database is locked}}
673 do_test incrvacuum-12.3 {
674 execsql { ROLLBACK; } db2
675 execsql { PRAGMA auto_vacuum }
676 } {2} ;# Still 2 because PRAGMA auto_vacuum setting held in case of vacuum
677 do_test incrvacuum-12.4 {
678 db close
679 sqlite3 db test.db
680 execsql { PRAGMA auto_vacuum }
681 } {1} ;# Revert to 1 because the database file did not change
683 do_test incrvacuum-12.5 {
684 execsql { SELECT * FROM sqlite_master }
685 execsql { PRAGMA auto_vacuum }
686 } {1}
688 #----------------------------------------------------------------------
689 # Special case #2: What if one process prepares a "PRAGMA auto_vacuum = XXX"
690 # statement when the database is empty, but doesn't execute it until
691 # after some other process has created the database.
692 #
693 db2 close
694 db close
695 forcedelete test.db test.db-journal
696 sqlite3 db test.db ; set ::DB [sqlite3_connection_pointer db]
697 sqlite3 db2 test.db
699 do_test incrvacuum-13.1 {
700 # File size is sometimes 1 instead of 0 due to the hack we put in
701 # to work around ticket #3260. Search for comments on #3260 in
702 # os_unix.c.
703 expr {[file size test.db]>1}
704 } {0}
705 do_test incrvacuum-13.2 {
706 set ::STMT [sqlite3_prepare $::DB {PRAGMA auto_vacuum = 2} -1 DUMMY]
707 execsql {
708 PRAGMA auto_vacuum = none;
709 PRAGMA default_cache_size = 1024;
710 PRAGMA auto_vacuum;
711 } db2
712 } {0}
713 do_test incrvacuum-13.3 {
714 expr {[file size test.db]>0}
715 } {1}
716 do_test incrvacuum-13.4 {
717 set rc [sqlite3_step $::STMT]
718 list $rc [sqlite3_finalize $::STMT]
719 } {SQLITE_DONE SQLITE_OK}
720 do_test incrvacuum-13.5 {
721 execsql {
722 PRAGMA auto_vacuum;
723 }
724 } {0}
727 # Verify that the incremental_vacuum pragma fails gracefully if it
728 # is used against an invalid database file.
729 #
730 if {[permutation] == ""} {
731 do_test incrvacuum-14.1 {
732 set out [open invalid.db w]
733 puts $out "This is not an SQLite database file"
734 close $out
735 sqlite3 db3 invalid.db
736 catchsql {
737 PRAGMA incremental_vacuum(10);
738 } db3
739 } {1 {file is encrypted or is not a database}}
740 db3 close
741 }
743 do_test incrvacuum-15.1 {
744 db close
745 db2 close
746 forcedelete test.db
747 sqlite3 db test.db
749 set str [string repeat "abcdefghij" 500]
751 execsql {
752 PRAGMA cache_size = 10;
753 PRAGMA auto_vacuum = incremental;
754 CREATE TABLE t1(x, y);
755 INSERT INTO t1 VALUES('a', $str);
756 INSERT INTO t1 VALUES('b', $str);
757 INSERT INTO t1 VALUES('c', $str);
758 INSERT INTO t1 VALUES('d', $str);
759 INSERT INTO t1 VALUES('e', $str);
760 INSERT INTO t1 VALUES('f', $str);
761 INSERT INTO t1 VALUES('g', $str);
762 INSERT INTO t1 VALUES('h', $str);
763 INSERT INTO t1 VALUES('i', $str);
764 INSERT INTO t1 VALUES('j', $str);
765 INSERT INTO t1 VALUES('j', $str);
767 CREATE TABLE t2(x PRIMARY KEY, y);
768 INSERT INTO t2 VALUES('a', $str);
769 INSERT INTO t2 VALUES('b', $str);
770 INSERT INTO t2 VALUES('c', $str);
771 INSERT INTO t2 VALUES('d', $str);
773 BEGIN;
774 DELETE FROM t2;
775 PRAGMA incremental_vacuum;
776 }
778 catchsql {INSERT INTO t2 SELECT * FROM t1}
780 execsql {
781 COMMIT;
782 PRAGMA integrity_check;
783 }
784 } {ok}
786 finish_test