Annotation of embedaddon/sqlite3/test/e_vacuum.test, revision 1.1
1.1 ! misho 1: # 2010 September 24
! 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: #
! 12: # This file implements tests to verify that the "testable statements" in
! 13: # the lang_vacuum.html document are correct.
! 14: #
! 15:
! 16: set testdir [file dirname $argv0]
! 17: source $testdir/tester.tcl
! 18:
! 19: sqlite3_test_control_pending_byte 0x1000000
! 20:
! 21: proc create_db {{sql ""}} {
! 22: catch { db close }
! 23: forcedelete test.db
! 24: sqlite3 db test.db
! 25:
! 26: db transaction {
! 27: execsql { PRAGMA page_size = 1024; }
! 28: execsql $sql
! 29: execsql {
! 30: CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
! 31: INSERT INTO t1 VALUES(1, randomblob(400));
! 32: INSERT INTO t1 SELECT a+1, randomblob(400) FROM t1;
! 33: INSERT INTO t1 SELECT a+2, randomblob(400) FROM t1;
! 34: INSERT INTO t1 SELECT a+4, randomblob(400) FROM t1;
! 35: INSERT INTO t1 SELECT a+8, randomblob(400) FROM t1;
! 36: INSERT INTO t1 SELECT a+16, randomblob(400) FROM t1;
! 37: INSERT INTO t1 SELECT a+32, randomblob(400) FROM t1;
! 38: INSERT INTO t1 SELECT a+64, randomblob(400) FROM t1;
! 39:
! 40: CREATE TABLE t2(a PRIMARY KEY, b UNIQUE);
! 41: INSERT INTO t2 SELECT * FROM t1;
! 42: }
! 43: }
! 44:
! 45: return [expr {[file size test.db] / 1024}]
! 46: }
! 47:
! 48: # This proc returns the number of contiguous blocks of pages that make up
! 49: # the table or index named by the only argument. For example, if the table
! 50: # occupies database pages 3, 4, 8 and 9, then this command returns 2 (there
! 51: # are 2 fragments - one consisting of pages 3 and 4, the other of fragments
! 52: # 8 and 9).
! 53: #
! 54: proc fragment_count {name} {
! 55: execsql { CREATE VIRTUAL TABLE temp.stat USING dbstat }
! 56: set nFrag 1
! 57: db eval {SELECT pageno FROM stat WHERE name = 't1' ORDER BY pageno} {
! 58: if {[info exists prevpageno] && $prevpageno != $pageno-1} {
! 59: incr nFrag
! 60: }
! 61: set prevpageno $pageno
! 62: }
! 63: execsql { DROP TABLE temp.stat }
! 64: set nFrag
! 65: }
! 66:
! 67:
! 68: # EVIDENCE-OF: R-45173-45977 -- syntax diagram vacuum-stmt
! 69: #
! 70: do_execsql_test e_vacuum-0.1 { VACUUM } {}
! 71:
! 72: # EVIDENCE-OF: R-51469-36013 Unless SQLite is running in
! 73: # "auto_vacuum=FULL" mode, when a large amount of data is deleted from
! 74: # the database file it leaves behind empty space, or "free" database
! 75: # pages.
! 76: #
! 77: # EVIDENCE-OF: R-60541-63059 Running VACUUM to rebuild the database
! 78: # reclaims this space and reduces the size of the database file.
! 79: #
! 80: foreach {tn avmode sz} {
! 81: 1 none 7
! 82: 2 full 8
! 83: 3 incremental 8
! 84: } {
! 85: set nPage [create_db "PRAGMA auto_vacuum = $avmode"]
! 86:
! 87: do_execsql_test e_vacuum-1.1.$tn.1 {
! 88: DELETE FROM t1;
! 89: DELETE FROM t2;
! 90: } {}
! 91:
! 92: if {$avmode == "full"} {
! 93: # This branch tests the "unless ... auto_vacuum=FULL" in the requirement
! 94: # above. If auto_vacuum is set to FULL, then no empty space is left in
! 95: # the database file.
! 96: do_execsql_test e_vacuum-1.1.$tn.2 {PRAGMA freelist_count} 0
! 97: } else {
! 98: set freelist [expr {$nPage - $sz}]
! 99: if {$avmode == "incremental"} {
! 100: # The page size is 1024 bytes. Therefore, assuming the database contains
! 101: # somewhere between 207 and 411 pages (it does), there are 2 pointer-map
! 102: # pages.
! 103: incr freelist -2
! 104: }
! 105: do_execsql_test e_vacuum-1.1.$tn.3 {PRAGMA freelist_count} $freelist
! 106: do_execsql_test e_vacuum-1.1.$tn.4 {VACUUM} {}
! 107: }
! 108:
! 109: do_test e_vacuum-1.1.$tn.5 { expr {[file size test.db] / 1024} } $sz
! 110: }
! 111:
! 112: # EVIDENCE-OF: R-50943-18433 Frequent inserts, updates, and deletes can
! 113: # cause the database file to become fragmented - where data for a single
! 114: # table or index is scattered around the database file.
! 115: #
! 116: # EVIDENCE-OF: R-05791-54928 Running VACUUM ensures that each table and
! 117: # index is largely stored contiguously within the database file.
! 118: #
! 119: # e_vacuum-1.2.1 - Perform many INSERT, UPDATE and DELETE ops on table t1.
! 120: # e_vacuum-1.2.2 - Verify that t1 and its indexes are now quite fragmented.
! 121: # e_vacuum-1.2.3 - Run VACUUM.
! 122: # e_vacuum-1.2.4 - Verify that t1 and its indexes are now much
! 123: # less fragmented.
! 124: #
! 125: ifcapable vtab {
! 126: create_db
! 127: register_dbstat_vtab db
! 128: do_execsql_test e_vacuum-1.2.1 {
! 129: DELETE FROM t1 WHERE a%2;
! 130: INSERT INTO t1 SELECT b, a FROM t2 WHERE a%2;
! 131: UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0;
! 132: } {}
! 133:
! 134: do_test e_vacuum-1.2.2.1 { expr [fragment_count t1]>100 } 1
! 135: do_test e_vacuum-1.2.2.2 { expr [fragment_count sqlite_autoindex_t1_1]>100 } 1
! 136: do_test e_vacuum-1.2.2.3 { expr [fragment_count sqlite_autoindex_t1_2]>100 } 1
! 137:
! 138: do_execsql_test e_vacuum-1.2.3 { VACUUM } {}
! 139:
! 140: # In practice, the tables and indexes each end up stored as two fragments -
! 141: # one containing the root page and another containing all other pages.
! 142: #
! 143: do_test e_vacuum-1.2.4.1 { fragment_count t1 } 2
! 144: do_test e_vacuum-1.2.4.2 { fragment_count sqlite_autoindex_t1_1 } 2
! 145: do_test e_vacuum-1.2.4.3 { fragment_count sqlite_autoindex_t1_2 } 2
! 146: }
! 147:
! 148: # EVIDENCE-OF: R-20474-44465 Normally, the database page_size and
! 149: # whether or not the database supports auto_vacuum must be configured
! 150: # before the database file is actually created.
! 151: #
! 152: do_test e_vacuum-1.3.1.1 {
! 153: create_db "PRAGMA page_size = 1024 ; PRAGMA auto_vacuum = FULL"
! 154: execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
! 155: } {1024 1}
! 156: do_test e_vacuum-1.3.1.2 {
! 157: execsql { PRAGMA page_size = 2048 }
! 158: execsql { PRAGMA auto_vacuum = NONE }
! 159: execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
! 160: } {1024 1}
! 161:
! 162: # EVIDENCE-OF: R-08570-19916 However, when not in write-ahead log mode,
! 163: # the page_size and/or auto_vacuum properties of an existing database
! 164: # may be changed by using the page_size and/or pragma auto_vacuum
! 165: # pragmas and then immediately VACUUMing the database.
! 166: #
! 167: do_test e_vacuum-1.3.2.1 {
! 168: execsql { PRAGMA journal_mode = delete }
! 169: execsql { PRAGMA page_size = 2048 }
! 170: execsql { PRAGMA auto_vacuum = NONE }
! 171: execsql VACUUM
! 172: execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
! 173: } {2048 0}
! 174:
! 175: # EVIDENCE-OF: R-48521-51450 When in write-ahead log mode, only the
! 176: # auto_vacuum support property can be changed using VACUUM.
! 177: #
! 178: ifcapable wal {
! 179: do_test e_vacuum-1.3.3.1 {
! 180: execsql { PRAGMA journal_mode = wal }
! 181: execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
! 182: } {2048 0}
! 183: do_test e_vacuum-1.3.3.2 {
! 184: execsql { PRAGMA page_size = 1024 }
! 185: execsql { PRAGMA auto_vacuum = FULL }
! 186: execsql VACUUM
! 187: execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
! 188: } {2048 1}
! 189: }
! 190:
! 191: # EVIDENCE-OF: R-38001-03952 VACUUM only works on the main database. It
! 192: # is not possible to VACUUM an attached database file.
! 193: forcedelete test.db2
! 194: create_db { PRAGMA auto_vacuum = NONE }
! 195: do_execsql_test e_vacuum-2.1.1 {
! 196: ATTACH 'test.db2' AS aux;
! 197: PRAGMA aux.page_size = 1024;
! 198: CREATE TABLE aux.t3 AS SELECT * FROM t1;
! 199: DELETE FROM t3;
! 200: } {}
! 201: set original_size [file size test.db2]
! 202:
! 203: # Try everything we can think of to get the aux database vacuumed:
! 204: do_execsql_test e_vacuum-2.1.3 { VACUUM } {}
! 205: do_execsql_test e_vacuum-2.1.4 { VACUUM aux } {}
! 206: do_execsql_test e_vacuum-2.1.5 { VACUUM 'test.db2' } {}
! 207:
! 208: # Despite our efforts, space in the aux database has not been reclaimed:
! 209: do_test e_vacuum-2.1.6 { expr {[file size test.db2]==$::original_size} } 1
! 210:
! 211: # EVIDENCE-OF: R-17495-17419 The VACUUM command may change the ROWIDs of
! 212: # entries in any tables that do not have an explicit INTEGER PRIMARY
! 213: # KEY.
! 214: #
! 215: # Tests e_vacuum-3.1.1 - 3.1.2 demonstrate that rowids can change when
! 216: # a database is VACUUMed. Tests e_vacuum-3.1.3 - 3.1.4 show that adding
! 217: # an INTEGER PRIMARY KEY column to a table stops this from happening.
! 218: #
! 219: do_execsql_test e_vacuum-3.1.1 {
! 220: CREATE TABLE t4(x);
! 221: INSERT INTO t4(x) VALUES('x');
! 222: INSERT INTO t4(x) VALUES('y');
! 223: INSERT INTO t4(x) VALUES('z');
! 224: DELETE FROM t4 WHERE x = 'y';
! 225: SELECT rowid, x FROM t4;
! 226: } {1 x 3 z}
! 227: do_execsql_test e_vacuum-3.1.2 {
! 228: VACUUM;
! 229: SELECT rowid, x FROM t4;
! 230: } {1 x 2 z}
! 231:
! 232: do_execsql_test e_vacuum-3.1.3 {
! 233: CREATE TABLE t5(x, y INTEGER PRIMARY KEY);
! 234: INSERT INTO t5(x) VALUES('x');
! 235: INSERT INTO t5(x) VALUES('y');
! 236: INSERT INTO t5(x) VALUES('z');
! 237: DELETE FROM t5 WHERE x = 'y';
! 238: SELECT rowid, x FROM t5;
! 239: } {1 x 3 z}
! 240: do_execsql_test e_vacuum-3.1.4 {
! 241: VACUUM;
! 242: SELECT rowid, x FROM t5;
! 243: } {1 x 3 z}
! 244:
! 245: # EVIDENCE-OF: R-49563-33883 A VACUUM will fail if there is an open
! 246: # transaction, or if there are one or more active SQL statements when it
! 247: # is run.
! 248: #
! 249: do_execsql_test e_vacuum-3.2.1.1 { BEGIN } {}
! 250: do_catchsql_test e_vacuum-3.2.1.2 {
! 251: VACUUM
! 252: } {1 {cannot VACUUM from within a transaction}}
! 253: do_execsql_test e_vacuum-3.2.1.3 { COMMIT } {}
! 254: do_execsql_test e_vacuum-3.2.1.4 { VACUUM } {}
! 255: do_execsql_test e_vacuum-3.2.1.5 { SAVEPOINT x } {}
! 256: do_catchsql_test e_vacuum-3.2.1.6 {
! 257: VACUUM
! 258: } {1 {cannot VACUUM from within a transaction}}
! 259: do_execsql_test e_vacuum-3.2.1.7 { COMMIT } {}
! 260: do_execsql_test e_vacuum-3.2.1.8 { VACUUM } {}
! 261:
! 262: create_db
! 263: do_test e_vacuum-3.2.2.1 {
! 264: set res ""
! 265: db eval { SELECT a FROM t1 } {
! 266: if {$a == 10} { set res [catchsql VACUUM] }
! 267: }
! 268: set res
! 269: } {1 {cannot VACUUM - SQL statements in progress}}
! 270:
! 271:
! 272: # EVIDENCE-OF: R-38735-12540 As of SQLite version 3.1, an alternative to
! 273: # using the VACUUM command to reclaim space after data has been deleted
! 274: # is auto-vacuum mode, enabled using the auto_vacuum pragma.
! 275: #
! 276: do_test e_vacuum-3.3.1 {
! 277: create_db { PRAGMA auto_vacuum = FULL }
! 278: execsql { PRAGMA auto_vacuum }
! 279: } {1}
! 280:
! 281: # EVIDENCE-OF: R-64844-34873 When auto_vacuum is enabled for a database
! 282: # free pages may be reclaimed after deleting data, causing the file to
! 283: # shrink, without rebuilding the entire database using VACUUM.
! 284: #
! 285: do_test e_vacuum-3.3.2.1 {
! 286: create_db { PRAGMA auto_vacuum = FULL }
! 287: execsql {
! 288: DELETE FROM t1;
! 289: DELETE FROM t2;
! 290: }
! 291: expr {[file size test.db] / 1024}
! 292: } {8}
! 293: do_test e_vacuum-3.3.2.2 {
! 294: create_db { PRAGMA auto_vacuum = INCREMENTAL }
! 295: execsql {
! 296: DELETE FROM t1;
! 297: DELETE FROM t2;
! 298: PRAGMA incremental_vacuum;
! 299: }
! 300: expr {[file size test.db] / 1024}
! 301: } {8}
! 302:
! 303: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to e_vacuum.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