Annotation of embedaddon/sqlite3/test/autovacuum.test, revision 1.1.1.1
1.1 misho 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: forcedelete 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: forcedelete 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: forcedelete test.db
603: forcedelete 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to autovacuum.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test