Debian Codegraph @

test/autovacuum.test

raw
1 # 2001 September 15
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 autovacuum feature.
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17
18 # If this build of the library does not support auto-vacuum, omit this
19 # whole file.
20 ifcapable {!autovacuum || !pragma} {
21 finish_test
22 return
23 }
24
25 # Return a string $len characters long. The returned string is $char repeated
26 # over and over. For example, [make_str abc 8] returns "abcabcab".
27 proc make_str {char len} {
28 set str [string repeat $char. $len]
29 return [string range $str 0 [expr $len-1]]
30 }
31
32 # Return the number of pages in the file test.db by looking at the file system.
33 proc file_pages {} {
34 return [expr [file size test.db] / 1024]
35 }
36
37 #-------------------------------------------------------------------------
38 # Test cases autovacuum-1.* work as follows:
39 #
40 # 1. A table with a single indexed field is created.
41 # 2. Approximately 20 rows are inserted into the table. Each row is long
42 # enough such that it uses at least 2 overflow pages for both the table
43 # and index entry.
44 # 3. The rows are deleted in a psuedo-random order. Sometimes only one row
45 # is deleted per transaction, sometimes more than one.
46 # 4. After each transaction the table data is checked to ensure it is correct
47 # and a "PRAGMA integrity_check" is executed.
48 # 5. Once all the rows are deleted the file is checked to make sure it
49 # consists of exactly 4 pages.
50 #
51 # Steps 2-5 are repeated for a few different psuedo-random delete patterns
52 # (defined by the $delete_orders list).
53 set delete_orders [list]
54 lappend delete_orders {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20}
55 lappend delete_orders {20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1}
56 lappend delete_orders {8 18 2 4 14 11 13 3 10 7 9 5 12 17 19 15 20 6 16 1}
57 lappend delete_orders {10 3 11 17 19 20 7 4 13 6 1 14 16 12 9 18 8 15 5 2}
58 lappend delete_orders {{1 2 3 4 5 6 7 8 9 10} {11 12 13 14 15 16 17 18 19 20}}
59 lappend delete_orders {{19 8 17 15} {16 11 9 14} {18 5 3 1} {13 20 7 2} {6 12}}
60
61 # The length of each table entry.
62 # set ENTRY_LEN 3500
63 set ENTRY_LEN 3500
64
65 do_test autovacuum-1.1 {
66 execsql {
67 PRAGMA auto_vacuum = 1;
68 CREATE TABLE av1(a);
69 CREATE INDEX av1_idx ON av1(a);
70 }
71 } {}
72
73 set tn 0
74 foreach delete_order $delete_orders {
75 incr tn
76
77 # Set up the table.
78 set ::tbl_data [list]
79 foreach i [lsort -integer [eval concat $delete_order]] {
80 execsql "INSERT INTO av1 (oid, a) VALUES($i, '[make_str $i $ENTRY_LEN]')"
81 lappend ::tbl_data [make_str $i $ENTRY_LEN]
82 }
83
84 # Make sure the integrity check passes with the initial data.
85 ifcapable {integrityck} {
86 do_test autovacuum-1.$tn.1 {
87 execsql {
88 pragma integrity_check
89 }
90 } {ok}
91 }
92
93 foreach delete $delete_order {
94 # Delete one set of rows from the table.
95 do_test autovacuum-1.$tn.($delete).1 {
96 execsql "
97 DELETE FROM av1 WHERE oid = [join $delete " OR oid = "]
98 "
99 } {}
100
101 # Do the integrity check.
102 ifcapable {integrityck} {
103 do_test autovacuum-1.$tn.($delete).2 {
104 execsql {
105 pragma integrity_check
106 }
107 } {ok}
108 }
109 # Ensure the data remaining in the table is what was expected.
110 foreach d $delete {
111 set idx [lsearch $::tbl_data [make_str $d $ENTRY_LEN]]
112 set ::tbl_data [lreplace $::tbl_data $idx $idx]
113 }
114 do_test autovacuum-1.$tn.($delete).3 {
115 execsql {
116 select a from av1 order by rowid
117 }
118 } $::tbl_data
119 }
120
121 # All rows have been deleted. Ensure the file has shrunk to 4 pages.
122 do_test autovacuum-1.$tn.3 {
123 file_pages
124 } {4}
125 }
126
127 #---------------------------------------------------------------------------
128 # Tests cases autovacuum-2.* test that root pages are allocated
129 # and deallocated correctly at the start of the file. Operation is roughly as
130 # follows:
131 #
132 # autovacuum-2.1.*: Drop the tables that currently exist in the database.
133 # autovacuum-2.2.*: Create some tables. Ensure that data pages can be
134 # moved correctly to make space for new root-pages.
135 # autovacuum-2.3.*: Drop one of the tables just created (not the last one),
136 # and check that one of the other tables is moved to
137 # the free root-page location.
138 # autovacuum-2.4.*: Check that a table can be created correctly when the
139 # root-page it requires is on the free-list.
140 # autovacuum-2.5.*: Check that a table with indices can be dropped. This
141 # is slightly tricky because dropping one of the
142 # indices/table btrees could move the root-page of another.
143 # The code-generation layer of SQLite overcomes this problem
144 # by dropping the btrees in descending order of root-pages.
145 # This test ensures that this actually happens.
146 #
147 do_test autovacuum-2.1.1 {
148 execsql {
149 DROP TABLE av1;
150 }
151 } {}
152 do_test autovacuum-2.1.2 {
153 file_pages
154 } {1}
155
156 # Create a table and put some data in it.
157 do_test autovacuum-2.2.1 {
158 execsql {
159 CREATE TABLE av1(x);
160 SELECT rootpage FROM sqlite_master ORDER BY rootpage;
161 }
162 } {3}
163 do_test autovacuum-2.2.2 {
164 execsql "
165 INSERT INTO av1 VALUES('[make_str abc 3000]');
166 INSERT INTO av1 VALUES('[make_str def 3000]');
167 INSERT INTO av1 VALUES('[make_str ghi 3000]');
168 INSERT INTO av1 VALUES('[make_str jkl 3000]');
169 "
170 set ::av1_data [db eval {select * from av1}]
171 file_pages
172 } {15}
173
174 # Create another table. Check it is located immediately after the first.
175 # This test case moves the second page in an over-flow chain.
176 do_test autovacuum-2.2.3 {
177 execsql {
178 CREATE TABLE av2(x);
179 SELECT rootpage FROM sqlite_master ORDER BY rootpage;
180 }
181 } {3 4}
182 do_test autovacuum-2.2.4 {
183 file_pages
184 } {16}
185
186 # Create another table. Check it is located immediately after the second.
187 # This test case moves the first page in an over-flow chain.
188 do_test autovacuum-2.2.5 {
189 execsql {
190 CREATE TABLE av3(x);
191 SELECT rootpage FROM sqlite_master ORDER BY rootpage;
192 }
193 } {3 4 5}
194 do_test autovacuum-2.2.6 {
195 file_pages
196 } {17}
197
198 # Create another table. Check it is located immediately after the second.
199 # This test case moves a btree leaf page.
200 do_test autovacuum-2.2.7 {
201 execsql {
202 CREATE TABLE av4(x);
203 SELECT rootpage FROM sqlite_master ORDER BY rootpage;
204 }
205 } {3 4 5 6}
206 do_test autovacuum-2.2.8 {
207 file_pages
208 } {18}
209 do_test autovacuum-2.2.9 {
210 execsql {
211 select * from av1
212 }
213 } $av1_data
214
215 do_test autovacuum-2.3.1 {
216 execsql {
217 INSERT INTO av2 SELECT 'av1' || x FROM av1;
218 INSERT INTO av3 SELECT 'av2' || x FROM av1;
219 INSERT INTO av4 SELECT 'av3' || x FROM av1;
220 }
221 set ::av2_data [execsql {select x from av2}]
222 set ::av3_data [execsql {select x from av3}]
223 set ::av4_data [execsql {select x from av4}]
224 file_pages
225 } {54}
226 do_test autovacuum-2.3.2 {
227 execsql {
228 DROP TABLE av2;
229 SELECT rootpage FROM sqlite_master ORDER BY rootpage;
230 }
231 } {3 4 5}
232 do_test autovacuum-2.3.3 {
233 file_pages
234 } {41}
235 do_test autovacuum-2.3.4 {
236 execsql {
237 SELECT x FROM av3;
238 }
239 } $::av3_data
240 do_test autovacuum-2.3.5 {
241 execsql {
242 SELECT x FROM av4;
243 }
244 } $::av4_data
245
246 # Drop all the tables in the file. This puts all pages except the first 2
247 # (the sqlite_master root-page and the first pointer map page) on the
248 # free-list.
249 do_test autovacuum-2.4.1 {
250 execsql {
251 DROP TABLE av1;
252 DROP TABLE av3;
253 BEGIN;
254 DROP TABLE av4;
255 }
256 file_pages
257 } {15}
258 do_test autovacuum-2.4.2 {
259 for {set i 3} {$i<=10} {incr i} {
260 execsql "CREATE TABLE av$i (x)"
261 }
262 file_pages
263 } {15}
264 do_test autovacuum-2.4.3 {
265 execsql {
266 SELECT rootpage FROM sqlite_master ORDER by rootpage
267 }
268 } {3 4 5 6 7 8 9 10}
269
270 # Right now there are 5 free pages in the database. Consume and then free
271 # all 520 pages. Then create 520 tables. This ensures that at least some of the
272 # desired root-pages reside on the second free-list trunk page, and that the
273 # trunk itself is required at some point.
274 do_test autovacuum-2.4.4 {
275 execsql "
276 INSERT INTO av3 VALUES ('[make_str abcde [expr 1020*520 + 500]]');
277 DELETE FROM av3;
278 "
279 } {}
280 set root_page_list [list]
281 set pending_byte_page [expr ($::sqlite_pending_byte / 1024) + 1]
282
283 # unusable_pages
284 # These are either the pending_byte page or the pointer map pages
285 #
286 unset -nocomplain unusable_page
287 if {[sqlite3 -has-codec]} {
288 array set unusable_page {205 1 408 1}
289 } else {
290 array set unusable_page {207 1 412 1}
291 }
292 set unusable_page($pending_byte_page) 1
293
294 for {set i 3} {$i<=532} {incr i} {
295 if {![info exists unusable_page($i)]} {
296 lappend root_page_list $i
297 }
298 }
299 if {$i >= $pending_byte_page} {
300 lappend root_page_list $i
301 }
302 do_test autovacuum-2.4.5 {
303 for {set i 11} {$i<=530} {incr i} {
304 execsql "CREATE TABLE av$i (x)"
305 }
306 execsql {
307 SELECT rootpage FROM sqlite_master ORDER by rootpage
308 }
309 } $root_page_list
310
311 # Just for fun, delete all those tables and see if the database is 1 page.
312 do_test autovacuum-2.4.6 {
313 execsql COMMIT;
314 file_pages
315 } [expr 561 + (($i >= $pending_byte_page)?1:0)]
316 integrity_check autovacuum-2.4.6
317 do_test autovacuum-2.4.7 {
318 execsql BEGIN
319 for {set i 3} {$i<=530} {incr i} {
320 execsql "DROP TABLE av$i"
321 }
322 execsql COMMIT
323 file_pages
324 } 1
325
326 # Create some tables with indices to drop.
327 do_test autovacuum-2.5.1 {
328 execsql {
329 CREATE TABLE av1(a PRIMARY KEY, b, c);
330 INSERT INTO av1 VALUES('av1 a', 'av1 b', 'av1 c');
331
332 CREATE TABLE av2(a PRIMARY KEY, b, c);
333 CREATE INDEX av2_i1 ON av2(b);
334 CREATE INDEX av2_i2 ON av2(c);
335 INSERT INTO av2 VALUES('av2 a', 'av2 b', 'av2 c');
336
337 CREATE TABLE av3(a PRIMARY KEY, b, c);
338 CREATE INDEX av3_i1 ON av3(b);
339 INSERT INTO av3 VALUES('av3 a', 'av3 b', 'av3 c');
340
341 CREATE TABLE av4(a, b, c);
342 CREATE INDEX av4_i1 ON av4(a);
343 CREATE INDEX av4_i2 ON av4(b);
344 CREATE INDEX av4_i3 ON av4(c);
345 CREATE INDEX av4_i4 ON av4(a, b, c);
346 INSERT INTO av4 VALUES('av4 a', 'av4 b', 'av4 c');
347 }
348 } {}
349
350 do_test autovacuum-2.5.2 {
351 execsql {
352 SELECT name, rootpage FROM sqlite_master;
353 }
354 } [list av1 3 sqlite_autoindex_av1_1 4 \
355 av2 5 sqlite_autoindex_av2_1 6 av2_i1 7 av2_i2 8 \
356 av3 9 sqlite_autoindex_av3_1 10 av3_i1 11 \
357 av4 12 av4_i1 13 av4_i2 14 av4_i3 15 av4_i4 16 \
358 ]
359
360 # The following 4 tests are SELECT queries that use the indices created.
361 # If the root-pages in the internal schema are not updated correctly when
362 # a table or indice is moved, these queries will fail. They are repeated
363 # after each table is dropped (i.e. as test cases 2.5.*.[1..4]).
364 do_test autovacuum-2.5.2.1 {
365 execsql {
366 SELECT * FROM av1 WHERE a = 'av1 a';
367 }
368 } {{av1 a} {av1 b} {av1 c}}
369 do_test autovacuum-2.5.2.2 {
370 execsql {
371 SELECT * FROM av2 WHERE a = 'av2 a' AND b = 'av2 b' AND c = 'av2 c'
372 }
373 } {{av2 a} {av2 b} {av2 c}}
374 do_test autovacuum-2.5.2.3 {
375 execsql {
376 SELECT * FROM av3 WHERE a = 'av3 a' AND b = 'av3 b';
377 }
378 } {{av3 a} {av3 b} {av3 c}}
379 do_test autovacuum-2.5.2.4 {
380 execsql {
381 SELECT * FROM av4 WHERE a = 'av4 a' AND b = 'av4 b' AND c = 'av4 c';
382 }
383 } {{av4 a} {av4 b} {av4 c}}
384
385 # Drop table av3. Indices av4_i2, av4_i3 and av4_i4 are moved to fill the two
386 # root pages vacated. The operation proceeds as:
387 # Step 1: Delete av3_i1 (root-page 11). Move root-page of av4_i4 to page 11.
388 # Step 2: Delete av3 (root-page 10). Move root-page of av4_i3 to page 10.
389 # Step 3: Delete sqlite_autoindex_av1_3 (root-page 9). Move av4_i2 to page 9.
390 do_test autovacuum-2.5.3 {
391 execsql {
392 DROP TABLE av3;
393 SELECT name, rootpage FROM sqlite_master;
394 }
395 } [list av1 3 sqlite_autoindex_av1_1 4 \
396 av2 5 sqlite_autoindex_av2_1 6 av2_i1 7 av2_i2 8 \
397 av4 12 av4_i1 13 av4_i2 9 av4_i3 10 av4_i4 11 \
398 ]
399 do_test autovacuum-2.5.3.1 {
400 execsql {
401 SELECT * FROM av1 WHERE a = 'av1 a';
402 }
403 } {{av1 a} {av1 b} {av1 c}}
404 do_test autovacuum-2.5.3.2 {
405 execsql {
406 SELECT * FROM av2 WHERE a = 'av2 a' AND b = 'av2 b' AND c = 'av2 c'
407 }
408 } {{av2 a} {av2 b} {av2 c}}
409 do_test autovacuum-2.5.3.3 {
410 execsql {
411 SELECT * FROM av4 WHERE a = 'av4 a' AND b = 'av4 b' AND c = 'av4 c';
412 }
413 } {{av4 a} {av4 b} {av4 c}}
414
415 # Drop table av1:
416 # Step 1: Delete av1 (root page 4). Root-page of av4_i1 fills the gap.
417 # Step 2: Delete sqlite_autoindex_av1_1 (root page 3). Move av4 to the gap.
418 do_test autovacuum-2.5.4 {
419 execsql {
420 DROP TABLE av1;
421 SELECT name, rootpage FROM sqlite_master;
422 }
423 } [list av2 5 sqlite_autoindex_av2_1 6 av2_i1 7 av2_i2 8 \
424 av4 3 av4_i1 4 av4_i2 9 av4_i3 10 av4_i4 11 \
425 ]
426 do_test autovacuum-2.5.4.2 {
427 execsql {
428 SELECT * FROM av2 WHERE a = 'av2 a' AND b = 'av2 b' AND c = 'av2 c'
429 }
430 } {{av2 a} {av2 b} {av2 c}}
431 do_test autovacuum-2.5.4.4 {
432 execsql {
433 SELECT * FROM av4 WHERE a = 'av4 a' AND b = 'av4 b' AND c = 'av4 c';
434 }
435 } {{av4 a} {av4 b} {av4 c}}
436
437 # Drop table av4:
438 # Step 1: Delete av4_i4.
439 # Step 2: Delete av4_i3.
440 # Step 3: Delete av4_i2.
441 # Step 4: Delete av4_i1. av2_i2 replaces it.
442 # Step 5: Delete av4. av2_i1 replaces it.
443 do_test autovacuum-2.5.5 {
444 execsql {
445 DROP TABLE av4;
446 SELECT name, rootpage FROM sqlite_master;
447 }
448 } [list av2 5 sqlite_autoindex_av2_1 6 av2_i1 3 av2_i2 4]
449 do_test autovacuum-2.5.5.2 {
450 execsql {
451 SELECT * FROM av2 WHERE a = 'av2 a' AND b = 'av2 b' AND c = 'av2 c'
452 }
453 } {{av2 a} {av2 b} {av2 c}}
454
455 #--------------------------------------------------------------------------
456 # Test cases autovacuum-3.* test the operation of the "PRAGMA auto_vacuum"
457 # command.
458 #
459 do_test autovacuum-3.1 {
460 execsql {
461 PRAGMA auto_vacuum;
462 }
463 } {1}
464 do_test autovacuum-3.2 {
465 db close
466 sqlite3 db test.db
467 execsql {
468 PRAGMA auto_vacuum;
469 }
470 } {1}
471 do_test autovacuum-3.3 {
472 execsql {
473 PRAGMA auto_vacuum = 0;
474 PRAGMA auto_vacuum;
475 }
476 } {1}
477
478 do_test autovacuum-3.4 {
479 db close
480 forcedelete test.db
481 sqlite3 db test.db
482 execsql {
483 PRAGMA auto_vacuum;
484 }
485 } $AUTOVACUUM
486 do_test autovacuum-3.5 {
487 execsql {
488 CREATE TABLE av1(x);
489 PRAGMA auto_vacuum;
490 }
491 } $AUTOVACUUM
492 do_test autovacuum-3.6 {
493 execsql {
494 PRAGMA auto_vacuum = 1;
495 PRAGMA auto_vacuum;
496 }
497 } [expr $AUTOVACUUM ? 1 : 0]
498 do_test autovacuum-3.7 {
499 execsql {
500 DROP TABLE av1;
501 }
502 file_pages
503 } [expr $AUTOVACUUM?1:2]
504
505
506 #-----------------------------------------------------------------------
507 # Test that if a statement transaction around a CREATE INDEX statement is
508 # rolled back no corruption occurs.
509 #
510 do_test autovacuum-4.0 {
511 # The last round of tests may have left the db in non-autovacuum mode.
512 # Reset everything just in case.
513 #
514 db close
515 forcedelete test.db test.db-journal
516 sqlite3 db test.db
517 execsql {
518 PRAGMA auto_vacuum = 1;
519 PRAGMA auto_vacuum;
520 }
521 } {1}
522 do_test autovacuum-4.1 {
523 execsql {
524 CREATE TABLE av1(a, b);
525 BEGIN;
526 }
527 for {set i 0} {$i<100} {incr i} {
528 execsql "INSERT INTO av1 VALUES($i, '[string repeat X 200]');"
529 }
530 execsql "INSERT INTO av1 VALUES(99, '[string repeat X 200]');"
531 execsql {
532 SELECT sum(a) FROM av1;
533 }
534 } {5049}
535 do_test autovacuum-4.2 {
536 catchsql {
537 CREATE UNIQUE INDEX av1_i ON av1(a);
538 }
539 } {1 {UNIQUE constraint failed: av1.a}}
540 do_test autovacuum-4.3 {
541 execsql {
542 SELECT sum(a) FROM av1;
543 }
544 } {5049}
545 do_test autovacuum-4.4 {
546 execsql {
547 COMMIT;
548 }
549 } {}
550
551 ifcapable integrityck {
552
553 # Ticket #1727
554 do_test autovacuum-5.1 {
555 db close
556 sqlite3 db :memory:
557 db eval {
558 PRAGMA auto_vacuum=1;
559 CREATE TABLE t1(a);
560 CREATE TABLE t2(a);
561 DROP TABLE t1;
562 PRAGMA integrity_check;
563 }
564 } ok
565
566 }
567
568 # Ticket #1728.
569 #
570 # In autovacuum mode, when tables or indices are deleted, the rootpage
571 # values in the symbol table have to be updated. There was a bug in this
572 # logic so that if an index/table was moved twice, the second move might
573 # not occur. This would leave the internal symbol table in an inconsistent
574 # state causing subsequent statements to fail.
575 #
576 # The problem is difficult to reproduce. The sequence of statements in
577 # the following test are carefully designed make it occur and thus to
578 # verify that this very obscure bug has been resolved.
579 #
580 ifcapable integrityck&&memorydb {
581
582 do_test autovacuum-6.1 {
583 db close
584 sqlite3 db :memory:
585 db eval {
586 PRAGMA auto_vacuum=1;
587 CREATE TABLE t1(a, b);
588 CREATE INDEX i1 ON t1(a);
589 CREATE TABLE t2(a);
590 CREATE INDEX i2 ON t2(a);
591 CREATE TABLE t3(a);
592 CREATE INDEX i3 ON t2(a);
593 CREATE INDEX x ON t1(b);
594 DROP TABLE t3;
595 PRAGMA integrity_check;
596 DROP TABLE t2;
597 PRAGMA integrity_check;
598 DROP TABLE t1;
599 PRAGMA integrity_check;
600 }
601 } {ok ok ok}
602
603 }
604
605 #---------------------------------------------------------------------
606 # Test cases autovacuum-7.X test the case where a page must be moved
607 # and the destination location collides with at least one other
608 # entry in the page hash-table (internal to the pager.c module.
609 #
610 do_test autovacuum-7.1 {
611 db close
612 forcedelete test.db
613 forcedelete test.db-journal
614 sqlite3 db test.db
615
616 execsql {
617 PRAGMA auto_vacuum=1;
618 CREATE TABLE t1(a, b, PRIMARY KEY(a, b));
619 INSERT INTO t1 VALUES(randstr(400,400),randstr(400,400));
620 INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2
621 INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 4
622 INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 8
623 INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 16
624 INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 32
625 }
626
627 expr {[file size test.db] / 1024}
628 } {73}
629
630 do_test autovacuum-7.2 {
631 execsql {
632 CREATE TABLE t2(a, b, PRIMARY KEY(a, b));
633 INSERT INTO t2 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2
634 CREATE TABLE t3(a, b, PRIMARY KEY(a, b));
635 INSERT INTO t3 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2
636 CREATE TABLE t4(a, b, PRIMARY KEY(a, b));
637 INSERT INTO t4 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2
638 CREATE TABLE t5(a, b, PRIMARY KEY(a, b));
639 INSERT INTO t5 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2
640 }
641 expr {[file size test.db] / 1024}
642 } {354}
643
644 do_test autovacuum-7.3 {
645 db close
646 sqlite3 db test.db
647 execsql {
648 BEGIN;
649 DELETE FROM t4;
650 COMMIT;
651 SELECT count(*) FROM t1;
652 }
653 expr {[file size test.db] / 1024}
654 } {286}
655
656 #------------------------------------------------------------------------
657 # Additional tests.
658 #
659 # Try to determine the autovacuum setting for a database that is locked.
660 #
661 do_test autovacuum-8.1 {
662 db close
663 sqlite3 db test.db
664 sqlite3 db2 test.db
665 db eval {PRAGMA auto_vacuum}
666 } {1}
667 if {[permutation] == ""} {
668 do_test autovacuum-8.2 {
669 db eval {BEGIN EXCLUSIVE}
670 catchsql {PRAGMA auto_vacuum} db2
671 } {1 {database is locked}}
672 catch {db2 close}
673 catch {db eval {COMMIT}}
674 }
675
676 do_test autovacuum-9.1 {
677 execsql {
678 DROP TABLE t1;
679 DROP TABLE t2;
680 DROP TABLE t3;
681 DROP TABLE t4;
682 DROP TABLE t5;
683 PRAGMA page_count;
684 }
685 } {1}
686 do_test autovacuum-9.2 {
687 file size test.db
688 } 1024
689 do_test autovacuum-9.3 {
690 execsql {
691 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
692 INSERT INTO t1 VALUES(NULL, randstr(50,50));
693 }
694 for {set ii 0} {$ii < 10} {incr ii} {
695 db eval { INSERT INTO t1 SELECT NULL, randstr(50,50) FROM t1 }
696 }
697 file size test.db
698 } $::sqlite_pending_byte
699 do_test autovacuum-9.4 {
700 execsql { INSERT INTO t1 SELECT NULL, randstr(50,50) FROM t1 }
701 } {}
702 do_test autovacuum-9.5 {
703 execsql { DELETE FROM t1 WHERE rowid > (SELECT max(a)/2 FROM t1) }
704 file size test.db
705 } $::sqlite_pending_byte
706
707 do_execsql_test autovacuum-10.1 {
708 DROP TABLE t1;
709 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
710 INSERT INTO t1 VALUES(25, randomblob(104));
711 REPLACE INTO t1 VALUES(25, randomblob(1117));
712 PRAGMA integrity_check;
713 } {ok}
714
715 finish_test