test/e_vacuum.test

   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 # -- 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&&compound {
 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 if {![nonzero_reserved_bytes]} {
 163   # EVIDENCE-OF: R-08570-19916 However, when not in write-ahead log mode,
 164   # the page_size and/or auto_vacuum properties of an existing database
 165   # may be changed by using the page_size and/or pragma auto_vacuum
 166   # pragmas and then immediately VACUUMing the database.
 167   #
 168   do_test e_vacuum-1.3.2.1 {
 169     execsql { PRAGMA journal_mode = delete }
 170     execsql { PRAGMA page_size = 2048 }
 171     execsql { PRAGMA auto_vacuum = NONE }
 172     execsql VACUUM
 173     execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
 174   } {2048 0}
 175
 176   # EVIDENCE-OF: R-48521-51450 When in write-ahead log mode, only the
 177   # auto_vacuum support property can be changed using VACUUM.
 178   #
 179   if {[wal_is_capable]} {
 180     do_test e_vacuum-1.3.3.1 {
 181       execsql { PRAGMA journal_mode = wal }
 182       execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
 183     } {2048 0}
 184     do_test e_vacuum-1.3.3.2 {
 185       execsql { PRAGMA page_size = 1024 }
 186       execsql { PRAGMA auto_vacuum = FULL }
 187       execsql VACUUM
 188       execsql { PRAGMA page_size ; PRAGMA auto_vacuum }
 189     } {2048 1}
 190   }
 191 }
 192
 193 # EVIDENCE-OF: R-55119-57913 By default, VACUUM only works only on the
 194 # main database.
 195 forcedelete test.db2
 196 create_db { PRAGMA auto_vacuum = NONE }
 197 do_execsql_test e_vacuum-2.1.1 {
 198   ATTACH 'test.db2' AS aux;
 199   PRAGMA aux.page_size = 1024;
 200   CREATE TABLE aux.t3 AS SELECT * FROM t1;
 201   DELETE FROM t3;
 202 } {}
 203 set original_size [file size test.db2]
 204
 205 # Vacuuming the main database does not affect aux
 206 do_execsql_test e_vacuum-2.1.3 { VACUUM } {}
 207 do_test e_vacuum-2.1.6 { expr {[file size test.db2]==$::original_size} } 1
 208
 209 # EVIDENCE-OF: R-36598-60500 Attached databases can be vacuumed by
 210 # appending the appropriate schema-name to the VACUUM statement.
 211 do_execsql_test e_vacuum-2.1.7 { VACUUM aux; } {}
 212 do_test e_vacuum-2.1.8 { expr {[file size test.db2]<$::original_size} } 1
 213
 214 # EVIDENCE-OF: R-17495-17419 The VACUUM command may change the ROWIDs of
 215 # entries in any tables that do not have an explicit INTEGER PRIMARY
 216 # KEY.
 217 #
 218 #   Tests e_vacuum-3.1.1 - 3.1.2 demonstrate that rowids can change when
 219 #   a database is VACUUMed. Tests e_vacuum-3.1.3 - 3.1.4 show that adding
 220 #   an INTEGER PRIMARY KEY column to a table stops this from happening.
 221 #
 222 do_execsql_test e_vacuum-3.1.1 {
 223   CREATE TABLE t4(x);
 224   INSERT INTO t4(x) VALUES('x');
 225   INSERT INTO t4(x) VALUES('y');
 226   INSERT INTO t4(x) VALUES('z');
 227   DELETE FROM t4 WHERE x = 'y';
 228   SELECT rowid, x FROM t4;
 229 } {1 x 3 z}
 230 do_execsql_test e_vacuum-3.1.2 {
 231   VACUUM;
 232   SELECT rowid, x FROM t4;
 233 } {1 x 2 z}
 234
 235 do_execsql_test e_vacuum-3.1.3 {
 236   CREATE TABLE t5(x, y INTEGER PRIMARY KEY);
 237   INSERT INTO t5(x) VALUES('x');
 238   INSERT INTO t5(x) VALUES('y');
 239   INSERT INTO t5(x) VALUES('z');
 240   DELETE FROM t5 WHERE x = 'y';
 241   SELECT rowid, x FROM t5;
 242 } {1 x 3 z}
 243 do_execsql_test e_vacuum-3.1.4 {
 244   VACUUM;
 245   SELECT rowid, x FROM t5;
 246 } {1 x 3 z}
 247
 248 # EVIDENCE-OF: R-49563-33883 A VACUUM will fail if there is an open
 249 # transaction, or if there are one or more active SQL statements when it
 250 # is run.
 251 #
 252 do_execsql_test  e_vacuum-3.2.1.1 { BEGIN } {}
 253 do_catchsql_test e_vacuum-3.2.1.2 {
 254   VACUUM
 255 } {1 {cannot VACUUM from within a transaction}}
 256 do_execsql_test  e_vacuum-3.2.1.3 { COMMIT } {}
 257 do_execsql_test  e_vacuum-3.2.1.4 { VACUUM } {}
 258 do_execsql_test  e_vacuum-3.2.1.5 { SAVEPOINT x } {}
 259 do_catchsql_test e_vacuum-3.2.1.6 {
 260   VACUUM
 261 } {1 {cannot VACUUM from within a transaction}}
 262 do_execsql_test  e_vacuum-3.2.1.7 { COMMIT } {}
 263 do_execsql_test  e_vacuum-3.2.1.8 { VACUUM } {}
 264
 265 create_db
 266 do_test e_vacuum-3.2.2.1 {
 267   set res ""
 268   db eval { SELECT a FROM t1 } {
 269     if {$a == 10} { set res [catchsql VACUUM] }
 270   }
 271   set res
 272 } {1 {cannot VACUUM - SQL statements in progress}}
 273
 274
 275 # EVIDENCE-OF: R-55138-13241 An alternative to using the VACUUM command
 276 # to reclaim space after data has been deleted is auto-vacuum mode,
 277 # enabled using the auto_vacuum pragma.
 278 #
 279 do_test e_vacuum-3.3.1 {
 280   create_db { PRAGMA auto_vacuum = FULL }
 281   execsql { PRAGMA auto_vacuum }
 282 } {1}
 283
 284 # EVIDENCE-OF: R-64844-34873 When auto_vacuum is enabled for a database
 285 # free pages may be reclaimed after deleting data, causing the file to
 286 # shrink, without rebuilding the entire database using VACUUM.
 287 #
 288 do_test e_vacuum-3.3.2.1 {
 289   create_db { PRAGMA auto_vacuum = FULL }
 290   execsql {
 291     DELETE FROM t1;
 292     DELETE FROM t2;
 293   }
 294   expr {[file size test.db] / 1024}
 295 } {8}
 296 do_test e_vacuum-3.3.2.2 {
 297   create_db { PRAGMA auto_vacuum = INCREMENTAL }
 298   execsql {
 299     DELETE FROM t1;
 300     DELETE FROM t2;
 301     PRAGMA incremental_vacuum;
 302   }
 303   expr {[file size test.db] / 1024}
 304 } {8}
 305
 306 finish_test