third_party/sqlite/src/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 # 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