1# 2016 April 15 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 contains tests for the RBU module. More specifically, it 13# contains tests to ensure that the sqlite3rbu_vacuum() API works as 14# expected. 15# 16 17source [file join [file dirname [info script]] rbu_common.tcl] 18set ::testprefix rbuvacuum 19 20foreach step {0 1} { 21 22 set ::testprefix rbuvacuum-step=$step 23 reset_db 24 25 # Simplest possible vacuum. 26 do_execsql_test 1.0 { 27 PRAGMA page_size = 1024; 28 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 29 INSERT INTO t1 VALUES(1, 2, 3); 30 INSERT INTO t1 VALUES(4, 5, 6); 31 INSERT INTO t1 VALUES(7, 8, 9); 32 PRAGMA integrity_check; 33 } {ok} 34 do_rbu_vacuum_test 1.1 $step 35 36 # A vacuum that actually reclaims space. 37 do_execsql_test 1.2.1 { 38 INSERT INTO t1 VALUES(8, randomblob(900), randomblob(900)); 39 INSERT INTO t1 VALUES(9, randomblob(900), randomblob(900)); 40 INSERT INTO t1 VALUES(10, randomblob(900), randomblob(900)); 41 INSERT INTO t1 VALUES(11, randomblob(900), randomblob(900)); 42 INSERT INTO t1 VALUES(12, randomblob(900), randomblob(900)); 43 PRAGMA page_count; 44 } {12} 45 do_execsql_test 1.2.2 { 46 DELETE FROM t1 WHERE rowid BETWEEN 8 AND 11; 47 PRAGMA page_count; 48 } {12} 49 do_rbu_vacuum_test 1.2.3 $step 50 do_execsql_test 1.2.4 { 51 PRAGMA page_count; 52 } {3} 53 54 # Add an index to the table. 55 do_execsql_test 1.3.1 { 56 CREATE INDEX t1b ON t1(b); 57 INSERT INTO t1 VALUES(13, randomblob(900), randomblob(900)); 58 INSERT INTO t1 VALUES(14, randomblob(900), randomblob(900)); 59 INSERT INTO t1 VALUES(15, randomblob(900), randomblob(900)); 60 INSERT INTO t1 VALUES(16, randomblob(900), randomblob(900)); 61 PRAGMA page_count; 62 } {18} 63 do_execsql_test 1.3.2 { 64 DELETE FROM t1 WHERE rowid BETWEEN 12 AND 15; 65 PRAGMA page_count; 66 } {18} 67 do_rbu_vacuum_test 1.3.3 $step 68 do_execsql_test 1.3.4 { 69 PRAGMA page_count; 70 } {5} 71 72 # WITHOUT ROWID table. 73 do_execsql_test 1.4.1 { 74 CREATE TABLE t2(a, b, c, PRIMARY KEY(a, b)) WITHOUT ROWID; 75 76 INSERT INTO t2 VALUES(randomblob(900), 1, randomblob(900)); 77 INSERT INTO t2 VALUES(randomblob(900), 2, randomblob(900)); 78 INSERT INTO t2 VALUES(randomblob(900), 3, randomblob(900)); 79 INSERT INTO t2 VALUES(randomblob(900), 4, randomblob(900)); 80 INSERT INTO t2 VALUES(randomblob(900), 6, randomblob(900)); 81 INSERT INTO t2 VALUES(randomblob(900), 7, randomblob(900)); 82 INSERT INTO t2 VALUES(randomblob(900), 8, randomblob(900)); 83 84 DELETE FROM t2 WHERE b BETWEEN 2 AND 7; 85 PRAGMA page_count; 86 } {20} 87 do_rbu_vacuum_test 1.4.2 $step 88 do_execsql_test 1.4.3 { 89 PRAGMA page_count; 90 } {10} 91 92 # WITHOUT ROWID table with an index. 93 do_execsql_test 1.4.1 { 94 CREATE INDEX t2c ON t2(c); 95 96 INSERT INTO t2 VALUES(randomblob(900), 9, randomblob(900)); 97 INSERT INTO t2 VALUES(randomblob(900), 10, randomblob(900)); 98 INSERT INTO t2 VALUES(randomblob(900), 11, randomblob(900)); 99 INSERT INTO t2 VALUES(randomblob(900), 12, randomblob(900)); 100 INSERT INTO t2 VALUES(randomblob(900), 13, randomblob(900)); 101 102 DELETE FROM t2 WHERE b BETWEEN 8 AND 12; 103 PRAGMA page_count; 104 } {35} 105 do_rbu_vacuum_test 1.4.2 $step 106 do_execsql_test 1.4.3 { 107 PRAGMA page_count; 108 } {15} 109 do_execsql_test 1.4.4 { 110 VACUUM; 111 PRAGMA page_count; 112 } {15} 113 114 do_execsql_test 1.5.1 { 115 CREATE TABLE t3(a, b, c); 116 INSERT INTO t3 VALUES('a', 'b', 'c'); 117 INSERT INTO t3 VALUES('d', 'e', 'f'); 118 INSERT INTO t3 VALUES('g', 'h', 'i'); 119 } 120 do_rbu_vacuum_test 1.5.2 $step 121 do_execsql_test 1.5.3 { 122 SELECT * FROM t3 123 } {a b c d e f g h i} 124 do_execsql_test 1.5.4 { 125 CREATE INDEX t3a ON t3(a); 126 CREATE INDEX t3b ON t3(b); 127 CREATE INDEX t3c ON t3(c); 128 INSERT INTO t3 VALUES('j', 'k', 'l'); 129 DELETE FROM t3 WHERE a = 'g'; 130 } 131 do_rbu_vacuum_test 1.5.5 $step 132 do_execsql_test 1.5.6 { 133 SELECT rowid, * FROM t3 ORDER BY b 134 } {1 a b c 2 d e f 4 j k l} 135 136 do_execsql_test 1.6.1 { 137 CREATE TABLE t4(a PRIMARY KEY, b, c); 138 INSERT INTO t4 VALUES('a', 'b', 'c'); 139 INSERT INTO t4 VALUES('d', 'e', 'f'); 140 INSERT INTO t4 VALUES('g', 'h', 'i'); 141 } 142 do_rbu_vacuum_test 1.6.2 $step 143 do_execsql_test 1.6.3 { 144 SELECT * FROM t4 145 } {a b c d e f g h i} 146 do_execsql_test 1.6.4 { 147 CREATE INDEX t4a ON t4(a); 148 CREATE INDEX t4b ON t4(b); 149 CREATE INDEX t4c ON t4(c); 150 151 INSERT INTO t4 VALUES('j', 'k', 'l'); 152 DELETE FROM t4 WHERE a='g'; 153 } 154 do_rbu_vacuum_test 1.6.5 $step 155 do_execsql_test 1.6.6 { 156 SELECT * FROM t4 ORDER BY b 157 } {a b c d e f j k l} 158 159 reset_db 160 do_execsql_test 1.7.0 { 161 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b); 162 INSERT INTO t1 VALUES(NULL, 'one'); 163 INSERT INTO t1 VALUES(NULL, 'two'); 164 DELETE FROM t1 WHERE a=2; 165 INSERT INTO t1 VALUES(NULL, 'three'); 166 INSERT INTO t1 VALUES(NULL, 'four'); 167 DELETE FROM t1 WHERE a=4; 168 INSERT INTO t1 VALUES(NULL, 'five'); 169 INSERT INTO t1 VALUES(NULL, 'six'); 170 DELETE FROM t1 WHERE a=6; 171 SELECT * FROM t1; 172 } {1 one 3 three 5 five} 173 do_rbu_vacuum_test 1.7.1 $step 174 do_execsql_test 1.7.2 { 175 INSERT INTO t1 VALUES(NULL, 'seven'); 176 SELECT * FROM t1; 177 } {1 one 3 three 5 five 7 seven} 178 179 reset_db 180 do_execsql_test 1.8.0 { 181 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b); 182 CREATE INDEX i1 ON t1(b); 183 INSERT INTO t1 VALUES(NULL, 'one'); 184 INSERT INTO t1 VALUES(NULL, 'two'); 185 INSERT INTO t1 VALUES(NULL, 'three'); 186 INSERT INTO t1 VALUES(NULL, 'four'); 187 INSERT INTO t1 VALUES(NULL, 'five'); 188 INSERT INTO t1 VALUES(NULL, 'six'); 189 ANALYZE; 190 SELECT * FROM sqlite_stat1; 191 } {t1 i1 {6 1}} 192 do_rbu_vacuum_test 1.8.1 $step 193 do_execsql_test 1.7.2 { 194 SELECT * FROM sqlite_stat1; 195 } {t1 i1 {6 1}} 196 197 reset_db 198 do_execsql_test 1.9.0 { 199 PRAGMA page_size = 8192; 200 PRAGMA auto_vacuum = 2; 201 PRAGMA user_version = 412; 202 PRAGMA application_id = 413; 203 204 CREATE TABLE t1(a INTEGER PRIMARY KEY AUTOINCREMENT, b); 205 CREATE INDEX i1 ON t1(b); 206 INSERT INTO t1 VALUES(NULL, 'one'); 207 INSERT INTO t1 VALUES(NULL, 'two'); 208 INSERT INTO t1 VALUES(NULL, 'three'); 209 INSERT INTO t1 VALUES(NULL, 'four'); 210 INSERT INTO t1 VALUES(NULL, 'five'); 211 INSERT INTO t1 VALUES(NULL, 'six'); 212 213 PRAGMA main.page_size; 214 PRAGMA main.auto_vacuum; 215 PRAGMA main.user_version; 216 PRAGMA main.application_id; 217 } {8192 2 412 413} 218 219 do_rbu_vacuum_test 1.9.1 $step 220 do_execsql_test 1.9.2 { 221 PRAGMA main.page_size; 222 PRAGMA main.auto_vacuum; 223 PRAGMA main.user_version; 224 PRAGMA main.application_id; 225 } {8192 2 412 413} 226 227 # Vacuum a database with a large sqlite_master table. 228 # 229 reset_db 230 do_test 1.10.1 { 231 for {set i 1} {$i < 50} {incr i} { 232 execsql "PRAGMA page_size = 1024" 233 execsql "CREATE TABLE t$i (a, b, c, PRIMARY KEY(a, b));" 234 execsql " 235 INSERT INTO t$i VALUES(1, 2, 3); 236 INSERT INTO t$i VALUES(4, 5, 6); 237 " 238 } 239 } {} 240 do_rbu_vacuum_test 1.10.2 $step 241 242 # Database with empty tables. 243 # 244 reset_db 245 do_execsql_test 1.11.1 { 246 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 247 CREATE TABLE t2(a INTEGER PRIMARY KEY, b); 248 CREATE TABLE t3(a INTEGER PRIMARY KEY, b); 249 CREATE TABLE t4(a INTEGER PRIMARY KEY, b); 250 INSERT INTO t4 VALUES(1, 2); 251 } 252 do_rbu_vacuum_test 1.11.2 $step 253 do_execsql_test 1.11.3 { 254 SELECT * FROM t1; 255 SELECT * FROM t2; 256 SELECT * FROM t3; 257 SELECT * FROM t4; 258 } {1 2} 259 reset_db 260 do_execsql_test 1.12.1 { 261 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 262 CREATE TABLE t2(a INTEGER PRIMARY KEY, b); 263 CREATE TABLE t3(a INTEGER PRIMARY KEY, b); 264 CREATE TABLE t4(a INTEGER PRIMARY KEY, b); 265 INSERT INTO t1 VALUES(1, 2); 266 } 267 do_rbu_vacuum_test 1.12.2 $step 268 do_execsql_test 1.12.3 { 269 SELECT * FROM t1; 270 SELECT * FROM t2; 271 SELECT * FROM t3; 272 SELECT * FROM t4; 273 } {1 2} 274} 275set ::testprefix rbuvacuum 276 277#------------------------------------------------------------------------- 278# Test some error cases: 279# 280# 2.1.* the db being vacuumed being in wal mode already. 281# 2.2.* database modified mid vacuum. 282# 283reset_db 284do_execsql_test 2.1.0 { 285 CREATE TABLE t1(a, b); 286 INSERT INTO t1 VALUES(1, 2); 287 INSERT INTO t1 VALUES(3, 4); 288 INSERT INTO t1 VALUES(5, 6); 289 INSERT INTO t1 VALUES(7, 8); 290 PRAGMA journal_mode = wal; 291 INSERT INTO t1 VALUES(9, 10); 292} wal 293do_test 2.1.1 { 294 sqlite3rbu_vacuum rbu test.db state.db 295 rbu step 296} {SQLITE_ERROR} 297do_test 2.1.2 { 298 list [catch { rbu close } msg] $msg 299} {1 {SQLITE_ERROR - cannot vacuum wal mode database}} 300 301do_test 2.1.3 { 302 sqlite3rbu_vacuum rbu test.db state.db 303 rbu step 304} {SQLITE_ERROR} 305do_test 2.1.4 { 306 list [catch { rbu close_no_error } msg] $msg 307} {1 SQLITE_ERROR} 308 309reset_db 310do_execsql_test 2.2.0 { 311 CREATE TABLE tx(a PRIMARY KEY, b BLOB); 312 INSERT INTO tx VALUES(1, randomblob(900)); 313 INSERT INTO tx SELECT a+1, randomblob(900) FROM tx; 314 INSERT INTO tx SELECT a+2, randomblob(900) FROM tx; 315 INSERT INTO tx SELECT a+4, randomblob(900) FROM tx; 316 INSERT INTO tx SELECT a+8, randomblob(900) FROM tx; 317} 318db_save_and_close 319for {set i 1} 1 {incr i} { 320 db_restore_and_reopen 321 322 sqlite3rbu_vacuum rbu test.db state.db 323 for {set step 0} {$step<$i} {incr step} { rbu step } 324 rbu close 325 if {[file exists test.db-wal]} break 326 327 execsql { INSERT INTO tx VALUES(20, 20) } 328 329 do_test 2.2.$i.1 { 330 sqlite3rbu_vacuum rbu test.db state.db 331 rbu step 332 } {SQLITE_BUSY} 333 do_test 2.2.$i.2 { 334 list [catch { rbu close } msg] $msg 335 } {1 {SQLITE_BUSY - database modified during rbu vacuum}} 336} 337 338#------------------------------------------------------------------------- 339# Test that a database that uses custom collation sequences can be RBU 340# vacuumed. 341# 342reset_db 343forcedelete state.db 344proc noop {args} {} 345proc length_cmp {x y} { 346 set n1 [string length $x] 347 set n2 [string length $y] 348 return [expr $n1 - $n2] 349} 350sqlite3_create_collation_v2 db length length_cmp noop 351 352do_execsql_test 3.0 { 353 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 354 INSERT INTO t1 VALUES(1, 'i'); 355 INSERT INTO t1 VALUES(2, 'iiii'); 356 INSERT INTO t1 VALUES(3, 'ii'); 357 INSERT INTO t1 VALUES(4, 'iii'); 358 SELECT a FROM t1 ORDER BY b COLLATE length; 359} {1 3 4 2} 360do_execsql_test 3.1 { 361 CREATE INDEX i1 ON t1(b COLLATE length); 362} 363 364do_test 3.2 { 365 sqlite3rbu_vacuum rbu test.db state.db 366 while {[rbu step]=="SQLITE_OK"} {} 367 list [catch { rbu close } msg] $msg 368} {1 {SQLITE_ERROR - no such collation sequence: length}} 369 370do_test 3.3 { 371 sqlite3rbu_vacuum rbu test.db state.db 372 set db1 [rbu db 0] 373 sqlite3_create_collation_v2 $db1 length length_cmp noop 374 while {[rbu step]=="SQLITE_OK"} {} 375 list [catch { rbu close } msg] $msg 376} {1 {SQLITE_ERROR - no such collation sequence: length}} 377 378do_test 3.4 { 379 sqlite3rbu_vacuum rbu test.db state.db 380 set db1 [rbu db 1] 381 sqlite3_create_collation_v2 $db1 length length_cmp noop 382 while {[rbu step]=="SQLITE_OK"} {} 383 list [catch { rbu close } msg] $msg 384} {1 {SQLITE_ERROR - no such collation sequence: length}} 385 386do_test 3.5 { 387 sqlite3rbu_vacuum rbu test.db state.db 388 set db1 [rbu db 0] 389 set db2 [rbu db 1] 390 391 sqlite3_create_collation_v2 $db1 length length_cmp noop 392 sqlite3_create_collation_v2 $db2 length length_cmp noop 393 394 while {[rbu step]=="SQLITE_OK"} {} 395 list [catch { rbu close } msg] $msg 396} {0 SQLITE_DONE} 397 398catch { db close } 399finish_test 400