wasCSharpSQLite – Blame information for rev 1

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