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-40347-36128 By default, VACUUM operates on the main
 194 # 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 #   Update 2019-01-07:  Rowids are now preserved by VACUUM.
 223 #
 224 do_execsql_test e_vacuum-3.1.1 {
 225   CREATE TABLE t4(x);
 226   INSERT INTO t4(x) VALUES('x');
 227   INSERT INTO t4(x) VALUES('y');
 228   INSERT INTO t4(x) VALUES('z');
 229   DELETE FROM t4 WHERE x = 'y';
 230   SELECT rowid, x FROM t4;
 231 } {1 x 3 z}
 232 do_execsql_test e_vacuum-3.1.2 {
 233   VACUUM;
 234   SELECT rowid, x FROM t4;
 235 } {1 x 2 z}
 236
 237 # Rowids are preserved if an INTEGER PRIMARY KEY is used
 238 do_execsql_test e_vacuum-3.1.3 {
 239   CREATE TABLE t5(x, y INTEGER PRIMARY KEY);
 240   INSERT INTO t5(x) VALUES('x');
 241   INSERT INTO t5(x) VALUES('y');
 242   INSERT INTO t5(x) VALUES('z');
 243   DELETE FROM t5 WHERE x = 'y';
 244   SELECT rowid, x FROM t5;
 245 } {1 x 3 z}
 246 do_execsql_test e_vacuum-3.1.4 {
 247   VACUUM;
 248   SELECT rowid, x FROM t5;
 249 } {1 x 3 z}
 250
 251 # Rowid is preserved for VACUUM INTO
 252 do_execsql_test e_vacuum-3.1.5 {
 253   DROP TABLE t5;
 254   CREATE TABLE t5(x);
 255   INSERT INTO t5(x) VALUES('x');
 256   INSERT INTO t5(x) VALUES('y');
 257   INSERT INTO t5(x) VALUES('z');
 258   DELETE FROM t5 WHERE x = 'y';
 259   SELECT rowid, x FROM t5;
 260 } {1 x 3 z}
 261 forcedelete test2.db
 262 do_execsql_test e_vacuum-3.1.6 {
 263   VACUUM INTO 'test2.db';
 264   ATTACH 'test2.db' AS aux1;
 265   SELECT rowid, x FROM aux1.t5;
 266   DETACH aux1;
 267 } {1 x 3 z}
 268
 269 # Rowids are not renumbered if the table being vacuumed
 270 # has indexes.
 271 do_execsql_test e_vacuum-3.1.7 {
 272   DROP TABLE t5;
 273   CREATE TABLE t5(x,y,z);
 274   INSERT INTO t5(x) VALUES('x');
 275   INSERT INTO t5(x) VALUES('y');
 276   INSERT INTO t5(x) VALUES('z');
 277   UPDATE t5 SET y=x, z=random();
 278   DELETE FROM t5 WHERE x = 'y';
 279   CREATE INDEX t5x ON t5(x);
 280   CREATE UNIQUE INDEX t5y ON t5(y);
 281   CREATE INDEX t5zxy ON t5(z,x,y);
 282   SELECT rowid, x FROM t5;
 283 } {1 x 3 z}
 284 do_execsql_test e_vacuum-3.1.8 {
 285   VACUUM;
 286   SELECT rowid, x FROM t5;
 287 } {1 x 3 z}
 288
 289 # EVIDENCE-OF: R-12218-18073 A VACUUM will fail if there is an open
 290 # transaction on the database connection that is attempting to run the
 291 # VACUUM.
 292 #
 293 do_execsql_test  e_vacuum-3.2.1.1 { BEGIN } {}
 294 do_catchsql_test e_vacuum-3.2.1.2 {
 295   VACUUM
 296 } {1 {cannot VACUUM from within a transaction}}
 297 do_execsql_test  e_vacuum-3.2.1.3 { COMMIT } {}
 298 do_execsql_test  e_vacuum-3.2.1.4 { VACUUM } {}
 299 do_execsql_test  e_vacuum-3.2.1.5 { SAVEPOINT x } {}
 300 do_catchsql_test e_vacuum-3.2.1.6 {
 301   VACUUM
 302 } {1 {cannot VACUUM from within a transaction}}
 303 do_execsql_test  e_vacuum-3.2.1.7 { COMMIT } {}
 304 do_execsql_test  e_vacuum-3.2.1.8 { VACUUM } {}
 305
 306 create_db
 307 do_test e_vacuum-3.2.2.1 {
 308   set res ""
 309   db eval { SELECT a FROM t1 } {
 310     if {$a == 10} { set res [catchsql VACUUM] }
 311   }
 312   set res
 313 } {1 {cannot VACUUM - SQL statements in progress}}
 314
 315
 316 # EVIDENCE-OF: R-55138-13241 An alternative to using the VACUUM command
 317 # to reclaim space after data has been deleted is auto-vacuum mode,
 318 # enabled using the auto_vacuum pragma.
 319 #
 320 do_test e_vacuum-3.3.1 {
 321   create_db { PRAGMA auto_vacuum = FULL }
 322   execsql { PRAGMA auto_vacuum }
 323 } {1}
 324
 325 # EVIDENCE-OF: R-64844-34873 When auto_vacuum is enabled for a database
 326 # free pages may be reclaimed after deleting data, causing the file to
 327 # shrink, without rebuilding the entire database using VACUUM.
 328 #
 329 do_test e_vacuum-3.3.2.1 {
 330   create_db { PRAGMA auto_vacuum = FULL }
 331   execsql {
 332     DELETE FROM t1;
 333     DELETE FROM t2;
 334   }
 335   expr {[file size test.db] / 1024}
 336 } {8}
 337 do_test e_vacuum-3.3.2.2 {
 338   create_db { PRAGMA auto_vacuum = INCREMENTAL }
 339   execsql {
 340     DELETE FROM t1;
 341     DELETE FROM t2;
 342     PRAGMA incremental_vacuum;
 343   }
 344   expr {[file size test.db] / 1024}
 345 } {8}
 346
 347 finish_test