diff options
Diffstat (limited to 'test/e_vacuum.test')
| -rw-r--r-- | test/e_vacuum.test | 347 |
1 files changed, 347 insertions, 0 deletions
diff --git a/test/e_vacuum.test b/test/e_vacuum.test new file mode 100644 index 0000000..63bf7c1 --- /dev/null +++ b/test/e_vacuum.test @@ -0,0 +1,347 @@ +# 2010 September 24 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# This file implements tests to verify that the "testable statements" in +# the lang_vacuum.html document are correct. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +sqlite3_test_control_pending_byte 0x1000000 + +proc create_db {{sql ""}} { + catch { db close } + forcedelete test.db + sqlite3 db test.db + + db transaction { + execsql { PRAGMA page_size = 1024; } + execsql $sql + execsql { + CREATE TABLE t1(a PRIMARY KEY, b UNIQUE); + INSERT INTO t1 VALUES(1, randomblob(400)); + INSERT INTO t1 SELECT a+1, randomblob(400) FROM t1; + INSERT INTO t1 SELECT a+2, randomblob(400) FROM t1; + INSERT INTO t1 SELECT a+4, randomblob(400) FROM t1; + INSERT INTO t1 SELECT a+8, randomblob(400) FROM t1; + INSERT INTO t1 SELECT a+16, randomblob(400) FROM t1; + INSERT INTO t1 SELECT a+32, randomblob(400) FROM t1; + INSERT INTO t1 SELECT a+64, randomblob(400) FROM t1; + + CREATE TABLE t2(a PRIMARY KEY, b UNIQUE); + INSERT INTO t2 SELECT * FROM t1; + } + } + + return [expr {[file size test.db] / 1024}] +} + +# This proc returns the number of contiguous blocks of pages that make up +# the table or index named by the only argument. For example, if the table +# occupies database pages 3, 4, 8 and 9, then this command returns 2 (there +# are 2 fragments - one consisting of pages 3 and 4, the other of fragments +# 8 and 9). +# +proc fragment_count {name} { + execsql { CREATE VIRTUAL TABLE temp.stat USING dbstat } + set nFrag 1 + db eval {SELECT pageno FROM stat WHERE name = 't1' ORDER BY pageno} { + if {[info exists prevpageno] && $prevpageno != $pageno-1} { + incr nFrag + } + set prevpageno $pageno + } + execsql { DROP TABLE temp.stat } + set nFrag +} + + +# -- syntax diagram vacuum-stmt +# +do_execsql_test e_vacuum-0.1 { VACUUM } {} + +# EVIDENCE-OF: R-51469-36013 Unless SQLite is running in +# "auto_vacuum=FULL" mode, when a large amount of data is deleted from +# the database file it leaves behind empty space, or "free" database +# pages. +# +# EVIDENCE-OF: R-60541-63059 Running VACUUM to rebuild the database +# reclaims this space and reduces the size of the database file. +# +foreach {tn avmode sz} { + 1 none 7 + 2 full 8 + 3 incremental 8 +} { + set nPage [create_db "PRAGMA auto_vacuum = $avmode"] + + do_execsql_test e_vacuum-1.1.$tn.1 { + DELETE FROM t1; + DELETE FROM t2; + } {} + + if {$avmode == "full"} { + # This branch tests the "unless ... auto_vacuum=FULL" in the requirement + # above. If auto_vacuum is set to FULL, then no empty space is left in + # the database file. + do_execsql_test e_vacuum-1.1.$tn.2 {PRAGMA freelist_count} 0 + } else { + set freelist [expr {$nPage - $sz}] + if {$avmode == "incremental"} { + # The page size is 1024 bytes. Therefore, assuming the database contains + # somewhere between 207 and 411 pages (it does), there are 2 pointer-map + # pages. + incr freelist -2 + } + do_execsql_test e_vacuum-1.1.$tn.3 {PRAGMA freelist_count} $freelist + do_execsql_test e_vacuum-1.1.$tn.4 {VACUUM} {} + } + + do_test e_vacuum-1.1.$tn.5 { expr {[file size test.db] / 1024} } $sz +} + +# EVIDENCE-OF: R-50943-18433 Frequent inserts, updates, and deletes can +# cause the database file to become fragmented - where data for a single +# table or index is scattered around the database file. +# +# EVIDENCE-OF: R-05791-54928 Running VACUUM ensures that each table and +# index is largely stored contiguously within the database file. +# +# e_vacuum-1.2.1 - Perform many INSERT, UPDATE and DELETE ops on table t1. +# e_vacuum-1.2.2 - Verify that t1 and its indexes are now quite fragmented. +# e_vacuum-1.2.3 - Run VACUUM. +# e_vacuum-1.2.4 - Verify that t1 and its indexes are now much +# less fragmented. +# +ifcapable vtab&&compound { + create_db + register_dbstat_vtab db + do_execsql_test e_vacuum-1.2.1 { + DELETE FROM t1 WHERE a%2; + INSERT INTO t1 SELECT b, a FROM t2 WHERE a%2; + UPDATE t1 SET b=randomblob(600) WHERE (a%2)==0; + } {} + + do_test e_vacuum-1.2.2.1 { expr [fragment_count t1]>100 } 1 + do_test e_vacuum-1.2.2.2 { expr [fragment_count sqlite_autoindex_t1_1]>100 } 1 + do_test e_vacuum-1.2.2.3 { expr [fragment_count sqlite_autoindex_t1_2]>100 } 1 + + do_execsql_test e_vacuum-1.2.3 { VACUUM } {} + + # In practice, the tables and indexes each end up stored as two fragments - + # one containing the root page and another containing all other pages. + # + do_test e_vacuum-1.2.4.1 { fragment_count t1 } 2 + do_test e_vacuum-1.2.4.2 { fragment_count sqlite_autoindex_t1_1 } 2 + do_test e_vacuum-1.2.4.3 { fragment_count sqlite_autoindex_t1_2 } 2 +} + +# EVIDENCE-OF: R-20474-44465 Normally, the database page_size and +# whether or not the database supports auto_vacuum must be configured +# before the database file is actually created. +# +do_test e_vacuum-1.3.1.1 { + create_db "PRAGMA page_size = 1024 ; PRAGMA auto_vacuum = FULL" + execsql { PRAGMA page_size ; PRAGMA auto_vacuum } +} {1024 1} +do_test e_vacuum-1.3.1.2 { + execsql { PRAGMA page_size = 2048 } + execsql { PRAGMA auto_vacuum = NONE } + execsql { PRAGMA page_size ; PRAGMA auto_vacuum } +} {1024 1} + +if {![nonzero_reserved_bytes]} { + # EVIDENCE-OF: R-08570-19916 However, when not in write-ahead log mode, + # the page_size and/or auto_vacuum properties of an existing database + # may be changed by using the page_size and/or pragma auto_vacuum + # pragmas and then immediately VACUUMing the database. + # + do_test e_vacuum-1.3.2.1 { + execsql { PRAGMA journal_mode = delete } + execsql { PRAGMA page_size = 2048 } + execsql { PRAGMA auto_vacuum = NONE } + execsql VACUUM + execsql { PRAGMA page_size ; PRAGMA auto_vacuum } + } {2048 0} + + # EVIDENCE-OF: R-48521-51450 When in write-ahead log mode, only the + # auto_vacuum support property can be changed using VACUUM. + # + if {[wal_is_capable]} { + do_test e_vacuum-1.3.3.1 { + execsql { PRAGMA journal_mode = wal } + execsql { PRAGMA page_size ; PRAGMA auto_vacuum } + } {2048 0} + do_test e_vacuum-1.3.3.2 { + execsql { PRAGMA page_size = 1024 } + execsql { PRAGMA auto_vacuum = FULL } + execsql VACUUM + execsql { PRAGMA page_size ; PRAGMA auto_vacuum } + } {2048 1} + } +} + +# EVIDENCE-OF: R-40347-36128 By default, VACUUM operates on the main +# database. +forcedelete test.db2 +create_db { PRAGMA auto_vacuum = NONE } +do_execsql_test e_vacuum-2.1.1 { + ATTACH 'test.db2' AS aux; + PRAGMA aux.page_size = 1024; + CREATE TABLE aux.t3 AS SELECT * FROM t1; + DELETE FROM t3; +} {} +set original_size [file size test.db2] + +# Vacuuming the main database does not affect aux +do_execsql_test e_vacuum-2.1.3 { VACUUM } {} +do_test e_vacuum-2.1.6 { expr {[file size test.db2]==$::original_size} } 1 + +# EVIDENCE-OF: R-36598-60500 Attached databases can be vacuumed by +# appending the appropriate schema-name to the VACUUM statement. +do_execsql_test e_vacuum-2.1.7 { VACUUM aux; } {} +do_test e_vacuum-2.1.8 { expr {[file size test.db2]<$::original_size} } 1 + +# EVIDENCE-OF: R-17495-17419 The VACUUM command may change the ROWIDs of +# entries in any tables that do not have an explicit INTEGER PRIMARY +# KEY. +# +# Tests e_vacuum-3.1.1 - 3.1.2 demonstrate that rowids can change when +# a database is VACUUMed. Tests e_vacuum-3.1.3 - 3.1.4 show that adding +# an INTEGER PRIMARY KEY column to a table stops this from happening. +# +# Update 2019-01-07: Rowids are now preserved by VACUUM. +# +do_execsql_test e_vacuum-3.1.1 { + CREATE TABLE t4(x); + INSERT INTO t4(x) VALUES('x'); + INSERT INTO t4(x) VALUES('y'); + INSERT INTO t4(x) VALUES('z'); + DELETE FROM t4 WHERE x = 'y'; + SELECT rowid, x FROM t4; +} {1 x 3 z} +do_execsql_test e_vacuum-3.1.2 { + VACUUM; + SELECT rowid, x FROM t4; +} {1 x 2 z} + +# Rowids are preserved if an INTEGER PRIMARY KEY is used +do_execsql_test e_vacuum-3.1.3 { + CREATE TABLE t5(x, y INTEGER PRIMARY KEY); + INSERT INTO t5(x) VALUES('x'); + INSERT INTO t5(x) VALUES('y'); + INSERT INTO t5(x) VALUES('z'); + DELETE FROM t5 WHERE x = 'y'; + SELECT rowid, x FROM t5; +} {1 x 3 z} +do_execsql_test e_vacuum-3.1.4 { + VACUUM; + SELECT rowid, x FROM t5; +} {1 x 3 z} + +# Rowid is preserved for VACUUM INTO +do_execsql_test e_vacuum-3.1.5 { + DROP TABLE t5; + CREATE TABLE t5(x); + INSERT INTO t5(x) VALUES('x'); + INSERT INTO t5(x) VALUES('y'); + INSERT INTO t5(x) VALUES('z'); + DELETE FROM t5 WHERE x = 'y'; + SELECT rowid, x FROM t5; +} {1 x 3 z} +forcedelete test2.db +do_execsql_test e_vacuum-3.1.6 { + VACUUM INTO 'test2.db'; + ATTACH 'test2.db' AS aux1; + SELECT rowid, x FROM aux1.t5; + DETACH aux1; +} {1 x 3 z} + +# Rowids are not renumbered if the table being vacuumed +# has indexes. +do_execsql_test e_vacuum-3.1.7 { + DROP TABLE t5; + CREATE TABLE t5(x,y,z); + INSERT INTO t5(x) VALUES('x'); + INSERT INTO t5(x) VALUES('y'); + INSERT INTO t5(x) VALUES('z'); + UPDATE t5 SET y=x, z=random(); + DELETE FROM t5 WHERE x = 'y'; + CREATE INDEX t5x ON t5(x); + CREATE UNIQUE INDEX t5y ON t5(y); + CREATE INDEX t5zxy ON t5(z,x,y); + SELECT rowid, x FROM t5; +} {1 x 3 z} +do_execsql_test e_vacuum-3.1.8 { + VACUUM; + SELECT rowid, x FROM t5; +} {1 x 3 z} + +# EVIDENCE-OF: R-12218-18073 A VACUUM will fail if there is an open +# transaction on the database connection that is attempting to run the +# VACUUM. +# +do_execsql_test e_vacuum-3.2.1.1 { BEGIN } {} +do_catchsql_test e_vacuum-3.2.1.2 { + VACUUM +} {1 {cannot VACUUM from within a transaction}} +do_execsql_test e_vacuum-3.2.1.3 { COMMIT } {} +do_execsql_test e_vacuum-3.2.1.4 { VACUUM } {} +do_execsql_test e_vacuum-3.2.1.5 { SAVEPOINT x } {} +do_catchsql_test e_vacuum-3.2.1.6 { + VACUUM +} {1 {cannot VACUUM from within a transaction}} +do_execsql_test e_vacuum-3.2.1.7 { COMMIT } {} +do_execsql_test e_vacuum-3.2.1.8 { VACUUM } {} + +create_db +do_test e_vacuum-3.2.2.1 { + set res "" + db eval { SELECT a FROM t1 } { + if {$a == 10} { set res [catchsql VACUUM] } + } + set res +} {1 {cannot VACUUM - SQL statements in progress}} + + +# EVIDENCE-OF: R-55138-13241 An alternative to using the VACUUM command +# to reclaim space after data has been deleted is auto-vacuum mode, +# enabled using the auto_vacuum pragma. +# +do_test e_vacuum-3.3.1 { + create_db { PRAGMA auto_vacuum = FULL } + execsql { PRAGMA auto_vacuum } +} {1} + +# EVIDENCE-OF: R-64844-34873 When auto_vacuum is enabled for a database +# free pages may be reclaimed after deleting data, causing the file to +# shrink, without rebuilding the entire database using VACUUM. +# +do_test e_vacuum-3.3.2.1 { + create_db { PRAGMA auto_vacuum = FULL } + execsql { + DELETE FROM t1; + DELETE FROM t2; + } + expr {[file size test.db] / 1024} +} {8} +do_test e_vacuum-3.3.2.2 { + create_db { PRAGMA auto_vacuum = INCREMENTAL } + execsql { + DELETE FROM t1; + DELETE FROM t2; + PRAGMA incremental_vacuum; + } + expr {[file size test.db] / 1024} +} {8} + +finish_test |