OLD | NEW |
(Empty) | |
| 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 |
| 17 source [file join [file dirname [info script]] rbu_common.tcl] |
| 18 set ::testprefix rbuvacuum |
| 19 |
| 20 foreach 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 } |
| 275 set ::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 # |
| 283 reset_db |
| 284 do_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 |
| 293 do_test 2.1.1 { |
| 294 sqlite3rbu_vacuum rbu test.db state.db |
| 295 rbu step |
| 296 } {SQLITE_ERROR} |
| 297 do_test 2.1.2 { |
| 298 list [catch { rbu close } msg] $msg |
| 299 } {1 {SQLITE_ERROR - cannot vacuum wal mode database}} |
| 300 |
| 301 reset_db |
| 302 do_execsql_test 2.2.0 { |
| 303 CREATE TABLE tx(a PRIMARY KEY, b BLOB); |
| 304 INSERT INTO tx VALUES(1, randomblob(900)); |
| 305 INSERT INTO tx SELECT a+1, randomblob(900) FROM tx; |
| 306 INSERT INTO tx SELECT a+2, randomblob(900) FROM tx; |
| 307 INSERT INTO tx SELECT a+4, randomblob(900) FROM tx; |
| 308 INSERT INTO tx SELECT a+8, randomblob(900) FROM tx; |
| 309 } |
| 310 db_save_and_close |
| 311 for {set i 1} 1 {incr i} { |
| 312 db_restore_and_reopen |
| 313 |
| 314 sqlite3rbu_vacuum rbu test.db state.db |
| 315 for {set step 0} {$step<$i} {incr step} { rbu step } |
| 316 rbu close |
| 317 if {[file exists test.db-wal]} break |
| 318 |
| 319 execsql { INSERT INTO tx VALUES(20, 20) } |
| 320 |
| 321 do_test 2.2.$i.1 { |
| 322 sqlite3rbu_vacuum rbu test.db state.db |
| 323 rbu step |
| 324 } {SQLITE_BUSY} |
| 325 do_test 2.2.$i.2 { |
| 326 list [catch { rbu close } msg] $msg |
| 327 } {1 {SQLITE_BUSY - database modified during rbu vacuum}} |
| 328 } |
| 329 |
| 330 #------------------------------------------------------------------------- |
| 331 # Test that a database that uses custom collation sequences can be RBU |
| 332 # vacuumed. |
| 333 # |
| 334 reset_db |
| 335 forcedelete state.db |
| 336 proc noop {args} {} |
| 337 proc length_cmp {x y} { |
| 338 set n1 [string length $x] |
| 339 set n2 [string length $y] |
| 340 return [expr $n1 - $n2] |
| 341 } |
| 342 sqlite3_create_collation_v2 db length length_cmp noop |
| 343 |
| 344 do_execsql_test 3.0 { |
| 345 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); |
| 346 INSERT INTO t1 VALUES(1, 'i'); |
| 347 INSERT INTO t1 VALUES(2, 'iiii'); |
| 348 INSERT INTO t1 VALUES(3, 'ii'); |
| 349 INSERT INTO t1 VALUES(4, 'iii'); |
| 350 SELECT a FROM t1 ORDER BY b COLLATE length; |
| 351 } {1 3 4 2} |
| 352 do_execsql_test 3.1 { |
| 353 CREATE INDEX i1 ON t1(b COLLATE length); |
| 354 } |
| 355 |
| 356 do_test 3.2 { |
| 357 sqlite3rbu_vacuum rbu test.db state.db |
| 358 while {[rbu step]=="SQLITE_OK"} {} |
| 359 list [catch { rbu close } msg] $msg |
| 360 } {1 {SQLITE_ERROR - no such collation sequence: length}} |
| 361 |
| 362 do_test 3.3 { |
| 363 sqlite3rbu_vacuum rbu test.db state.db |
| 364 set db1 [rbu db 0] |
| 365 sqlite3_create_collation_v2 $db1 length length_cmp noop |
| 366 while {[rbu step]=="SQLITE_OK"} {} |
| 367 list [catch { rbu close } msg] $msg |
| 368 } {1 {SQLITE_ERROR - no such collation sequence: length}} |
| 369 |
| 370 do_test 3.4 { |
| 371 sqlite3rbu_vacuum rbu test.db state.db |
| 372 set db1 [rbu db 1] |
| 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 |
| 378 do_test 3.5 { |
| 379 sqlite3rbu_vacuum rbu test.db state.db |
| 380 set db1 [rbu db 0] |
| 381 set db2 [rbu db 1] |
| 382 |
| 383 sqlite3_create_collation_v2 $db1 length length_cmp noop |
| 384 sqlite3_create_collation_v2 $db2 length length_cmp noop |
| 385 |
| 386 while {[rbu step]=="SQLITE_OK"} {} |
| 387 list [catch { rbu close } msg] $msg |
| 388 } {0 SQLITE_DONE} |
| 389 |
| 390 catch { db close } |
| 391 finish_test |
| 392 |
OLD | NEW |