Annotation of embedaddon/sqlite3/test/autovacuum.test, revision 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