1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/incrvacuum.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,737 @@
1.4 +# 2007 April 26
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing the incremental vacuum feature.
1.16 +#
1.17 +# Note: There are also some tests for incremental vacuum and IO
1.18 +# errors in incrvacuum_ioerr.test.
1.19 +#
1.20 +# $Id: incrvacuum.test,v 1.20 2008/09/10 10:57:28 danielk1977 Exp $
1.21 +
1.22 +set testdir [file dirname $argv0]
1.23 +source $testdir/tester.tcl
1.24 +
1.25 +# If this build of the library does not support auto-vacuum, omit this
1.26 +# whole file.
1.27 +ifcapable {!autovacuum || !pragma} {
1.28 + finish_test
1.29 + return
1.30 +}
1.31 +
1.32 +#---------------------------------------------------------------------
1.33 +# Test the pragma on an empty database.
1.34 +#
1.35 +do_test incrvacuum-1.1 {
1.36 + execsql {
1.37 + pragma auto_vacuum;
1.38 + }
1.39 +} $sqlite_options(default_autovacuum)
1.40 +do_test incrvacuum-1.2.0 {
1.41 + # File size is sometimes 1 instead of 0 due to the hack we put in
1.42 + # to work around ticket #3260. Search for comments on #3260 in
1.43 + # os_unix.c.
1.44 + expr {[file size test.db] > 1}
1.45 +} {0}
1.46 +do_test incrvacuum-1.2 {
1.47 + # This command will create the database.
1.48 + execsql {
1.49 + pragma auto_vacuum = 'full';
1.50 + pragma auto_vacuum;
1.51 + }
1.52 +} {1}
1.53 +do_test incrvacuum-1.2.1 {
1.54 + expr {[file size test.db] > 0}
1.55 +} {1}
1.56 +do_test incrvacuum-1.3 {
1.57 + execsql {
1.58 + pragma auto_vacuum = 'incremental';
1.59 + pragma auto_vacuum;
1.60 + }
1.61 +} {2}
1.62 +do_test incrvacuum-1.4 {
1.63 + # In this case the invalid value is ignored and the auto_vacuum
1.64 + # setting remains unchanged.
1.65 + execsql {
1.66 + pragma auto_vacuum = 'invalid';
1.67 + pragma auto_vacuum;
1.68 + }
1.69 +} {2}
1.70 +do_test incrvacuum-1.5 {
1.71 + execsql {
1.72 + pragma auto_vacuum = 1;
1.73 + pragma auto_vacuum;
1.74 + }
1.75 +} {1}
1.76 +do_test incrvacuum-1.6 {
1.77 + execsql {
1.78 + pragma auto_vacuum = '2';
1.79 + pragma auto_vacuum;
1.80 + }
1.81 +} {2}
1.82 +do_test incrvacuum-1.7 {
1.83 + # Invalid value. auto_vacuum setting remains unchanged.
1.84 + execsql {
1.85 + pragma auto_vacuum = 5;
1.86 + pragma auto_vacuum;
1.87 + }
1.88 +} {2}
1.89 +
1.90 +#---------------------------------------------------------------------
1.91 +# Test the pragma on a non-empty database. It is possible to toggle
1.92 +# the connection between "full" and "incremental" mode, but not to
1.93 +# change from either of these to "none", or from "none" to "full" or
1.94 +# "incremental".
1.95 +#
1.96 +do_test incrvacuum-2.1 {
1.97 + execsql {
1.98 + pragma auto_vacuum = 1;
1.99 + CREATE TABLE abc(a, b, c);
1.100 + }
1.101 +} {}
1.102 +do_test incrvacuum-2.2 {
1.103 + execsql {
1.104 + pragma auto_vacuum = 'none';
1.105 + pragma auto_vacuum;
1.106 + }
1.107 +} {1}
1.108 +do_test incrvacuum-2.2.1 {
1.109 + db close
1.110 + sqlite3 db test.db
1.111 + execsql {
1.112 + pragma auto_vacuum;
1.113 + }
1.114 +} {1}
1.115 +do_test incrvacuum-2.3 {
1.116 + execsql {
1.117 + pragma auto_vacuum = 'incremental';
1.118 + pragma auto_vacuum;
1.119 + }
1.120 +} {2}
1.121 +do_test incrvacuum-2.4 {
1.122 + execsql {
1.123 + pragma auto_vacuum = 'full';
1.124 + pragma auto_vacuum;
1.125 + }
1.126 +} {1}
1.127 +
1.128 +#---------------------------------------------------------------------
1.129 +# Test that when the auto_vacuum mode is "incremental", the database
1.130 +# does not shrink when pages are removed from it. But it does if
1.131 +# the mode is set to "full".
1.132 +#
1.133 +do_test incrvacuum-3.1 {
1.134 + execsql {
1.135 + pragma auto_vacuum;
1.136 + }
1.137 +} {1}
1.138 +do_test incrvacuum-3.2 {
1.139 + set ::str [string repeat 1234567890 110]
1.140 + execsql {
1.141 + PRAGMA auto_vacuum = 2;
1.142 + BEGIN;
1.143 + CREATE TABLE tbl2(str);
1.144 + INSERT INTO tbl2 VALUES($::str);
1.145 + COMMIT;
1.146 + }
1.147 + # 5 pages:
1.148 + #
1.149 + # 1 -> database header
1.150 + # 2 -> first back-pointer page
1.151 + # 3 -> table abc
1.152 + # 4 -> table tbl2
1.153 + # 5 -> table tbl2 overflow page.
1.154 + #
1.155 + expr {[file size test.db] / 1024}
1.156 +} {5}
1.157 +do_test incrvacuum-3.3 {
1.158 + execsql {
1.159 + DROP TABLE abc;
1.160 + DELETE FROM tbl2;
1.161 + }
1.162 + expr {[file size test.db] / 1024}
1.163 +} {5}
1.164 +do_test incrvacuum-3.4 {
1.165 + execsql {
1.166 + PRAGMA auto_vacuum = 1;
1.167 + INSERT INTO tbl2 VALUES('hello world');
1.168 + }
1.169 + expr {[file size test.db] / 1024}
1.170 +} {3}
1.171 +
1.172 +#---------------------------------------------------------------------
1.173 +# Try to run a very simple incremental vacuum. Also verify that
1.174 +# PRAGMA incremental_vacuum is a harmless no-op against a database that
1.175 +# does not support auto-vacuum.
1.176 +#
1.177 +do_test incrvacuum-4.1 {
1.178 + set ::str [string repeat 1234567890 110]
1.179 + execsql {
1.180 + PRAGMA auto_vacuum = 2;
1.181 + INSERT INTO tbl2 VALUES($::str);
1.182 + CREATE TABLE tbl1(a, b, c);
1.183 + }
1.184 + expr {[file size test.db] / 1024}
1.185 +} {5}
1.186 +do_test incrvacuum-4.2 {
1.187 + execsql {
1.188 + DELETE FROM tbl2;
1.189 + DROP TABLE tbl1;
1.190 + }
1.191 + expr {[file size test.db] / 1024}
1.192 +} {5}
1.193 +do_test incrvacuum-4.3 {
1.194 + set ::nStep 0
1.195 + db eval {pragma incremental_vacuum(10)} {
1.196 + incr ::nStep
1.197 + }
1.198 + list [expr {[file size test.db] / 1024}] $::nStep
1.199 +} {3 2}
1.200 +
1.201 +#---------------------------------------------------------------------
1.202 +# The following tests - incrvacuum-5.* - test incremental vacuum
1.203 +# from within a transaction.
1.204 +#
1.205 +do_test incrvacuum-5.1.1 {
1.206 + expr {[file size test.db] / 1024}
1.207 +} {3}
1.208 +do_test incrvacuum-5.1.2 {
1.209 + execsql {
1.210 + BEGIN;
1.211 + DROP TABLE tbl2;
1.212 + PRAGMA incremental_vacuum;
1.213 + COMMIT;
1.214 + }
1.215 + expr {[file size test.db] / 1024}
1.216 +} {1}
1.217 +
1.218 +do_test incrvacuum-5.2.1 {
1.219 + set ::str [string repeat abcdefghij 110]
1.220 + execsql {
1.221 + BEGIN;
1.222 + CREATE TABLE tbl1(a);
1.223 + INSERT INTO tbl1 VALUES($::str);
1.224 + PRAGMA incremental_vacuum; -- this is a no-op.
1.225 + COMMIT;
1.226 + }
1.227 + expr {[file size test.db] / 1024}
1.228 +} {4}
1.229 +do_test incrvacuum-5.2.2 {
1.230 + set ::str [string repeat abcdefghij 110]
1.231 + execsql {
1.232 + BEGIN;
1.233 + INSERT INTO tbl1 VALUES($::str);
1.234 + INSERT INTO tbl1 SELECT * FROM tbl1;
1.235 + DELETE FROM tbl1 WHERE oid%2; -- Put 2 overflow pages on free-list.
1.236 + COMMIT;
1.237 + }
1.238 + expr {[file size test.db] / 1024}
1.239 +} {7}
1.240 +do_test incrvacuum-5.2.3 {
1.241 + execsql {
1.242 + BEGIN;
1.243 + PRAGMA incremental_vacuum; -- Vacuum up the two pages.
1.244 + CREATE TABLE tbl2(b); -- Use one free page as a table root.
1.245 + INSERT INTO tbl2 VALUES('a nice string');
1.246 + COMMIT;
1.247 + }
1.248 + expr {[file size test.db] / 1024}
1.249 +} {6}
1.250 +do_test incrvacuum-5.2.4 {
1.251 + execsql {
1.252 + SELECT * FROM tbl2;
1.253 + }
1.254 +} {{a nice string}}
1.255 +do_test incrvacuum-5.2.5 {
1.256 + execsql {
1.257 + DROP TABLE tbl1;
1.258 + DROP TABLE tbl2;
1.259 + PRAGMA incremental_vacuum;
1.260 + }
1.261 + expr {[file size test.db] / 1024}
1.262 +} {1}
1.263 +
1.264 +
1.265 +# Test cases incrvacuum-5.3.* use the following list as input data.
1.266 +# Two new databases are opened, one with incremental vacuum enabled,
1.267 +# the other with no auto-vacuum completely disabled. After executing
1.268 +# each element of the following list on both databases, test that
1.269 +# the integrity-check passes and the contents of each are identical.
1.270 +#
1.271 +set TestScriptList [list {
1.272 + BEGIN;
1.273 + CREATE TABLE t1(a, b);
1.274 + CREATE TABLE t2(a, b);
1.275 + CREATE INDEX t1_i ON t1(a);
1.276 + CREATE INDEX t2_i ON t2(a);
1.277 +} {
1.278 + INSERT INTO t1 VALUES($::str1, $::str2);
1.279 + INSERT INTO t1 VALUES($::str1||$::str2, $::str2||$::str1);
1.280 + INSERT INTO t2 SELECT b, a FROM t1;
1.281 + INSERT INTO t2 SELECT a, b FROM t1;
1.282 + INSERT INTO t1 SELECT b, a FROM t2;
1.283 + UPDATE t2 SET b = '';
1.284 + PRAGMA incremental_vacuum;
1.285 +} {
1.286 + UPDATE t2 SET b = (SELECT b FROM t1 WHERE t1.oid = t2.oid);
1.287 + PRAGMA incremental_vacuum;
1.288 +} {
1.289 + CREATE TABLE t3(a, b);
1.290 + INSERT INTO t3 SELECT * FROM t2;
1.291 + DROP TABLE t2;
1.292 + PRAGMA incremental_vacuum;
1.293 +} {
1.294 + CREATE INDEX t3_i ON t3(a);
1.295 + COMMIT;
1.296 +} {
1.297 + BEGIN;
1.298 + DROP INDEX t3_i;
1.299 + PRAGMA incremental_vacuum;
1.300 + INSERT INTO t3 VALUES('hello', 'world');
1.301 + ROLLBACK;
1.302 +} {
1.303 + INSERT INTO t3 VALUES('hello', 'world');
1.304 +}
1.305 +]
1.306 +
1.307 +# If this build omits subqueries, step 2 in the above list will not
1.308 +# work. Replace it with "" in this case.
1.309 +#
1.310 +ifcapable !subquery { lset TestScriptList 2 "" }
1.311 +
1.312 +# Compare the contents of databases $A and $B.
1.313 +#
1.314 +proc compare_dbs {A B tname} {
1.315 + set tbl_list [execsql {
1.316 + SELECT tbl_name FROM sqlite_master WHERE type = 'table'
1.317 + } $A]
1.318 +
1.319 + do_test ${tname}.1 [subst {
1.320 + execsql {
1.321 + SELECT tbl_name FROM sqlite_master WHERE type = 'table'
1.322 + } $B
1.323 + }] $tbl_list
1.324 +
1.325 + set tn 1
1.326 + foreach tbl $tbl_list {
1.327 + set control [execsql "SELECT * FROM $tbl" $A]
1.328 + do_test ${tname}.[incr tn] [subst {
1.329 + execsql "SELECT * FROM $tbl" $B
1.330 + }] $control
1.331 + }
1.332 +}
1.333 +
1.334 +set ::str1 [string repeat abcdefghij 130]
1.335 +set ::str2 [string repeat 1234567890 105]
1.336 +
1.337 +file delete -force test1.db test1.db-journal test2.db test2.db-journal
1.338 +sqlite3 db1 test1.db
1.339 +sqlite3 db2 test2.db
1.340 +execsql { PRAGMA auto_vacuum = 'none' } db1
1.341 +execsql { PRAGMA auto_vacuum = 'incremental' } db2
1.342 +
1.343 +set tn 1
1.344 +foreach sql $::TestScriptList {
1.345 + execsql $sql db1
1.346 + execsql $sql db2
1.347 +
1.348 + compare_dbs db1 db2 incrvacuum-5.3.${tn}
1.349 + do_test incrvacuum-5.3.${tn}.integrity1 {
1.350 + execsql { PRAGMA integrity_check; } db1
1.351 + } {ok}
1.352 + do_test incrvacuum-5.3.${tn}.integrity2 {
1.353 + execsql { PRAGMA integrity_check; } db2
1.354 + } {ok}
1.355 + incr tn
1.356 +}
1.357 +db1 close
1.358 +db2 close
1.359 +#
1.360 +# End of test cases 5.3.*
1.361 +
1.362 +#---------------------------------------------------------------------
1.363 +# The following tests - incrvacuum-6.* - test running incremental
1.364 +# vacuum while another statement (a read) is being executed.
1.365 +#
1.366 +for {set jj 0} {$jj < 10} {incr jj} {
1.367 + # Build some test data. Two tables are created in an empty
1.368 + # database. tbl1 data is a contiguous block starting at page 5 (pages
1.369 + # 3 and 4 are the table roots). tbl2 is a contiguous block starting
1.370 + # right after tbl1.
1.371 + #
1.372 + # Then drop tbl1 so that when an incr vacuum is run the pages
1.373 + # of tbl2 have to be moved to fill the gap.
1.374 + #
1.375 + do_test incrvacuum-6.${jj}.1 {
1.376 + execsql {
1.377 + DROP TABLE IF EXISTS tbl1;
1.378 + DROP TABLE IF EXISTS tbl2;
1.379 + PRAGMA incremental_vacuum;
1.380 + CREATE TABLE tbl1(a, b);
1.381 + CREATE TABLE tbl2(a, b);
1.382 + BEGIN;
1.383 + }
1.384 + for {set ii 0} {$ii < 1000} {incr ii} {
1.385 + db eval {INSERT INTO tbl1 VALUES($ii, $ii || $ii)}
1.386 + }
1.387 + execsql {
1.388 + INSERT INTO tbl2 SELECT * FROM tbl1;
1.389 + COMMIT;
1.390 + DROP TABLE tbl1;
1.391 + }
1.392 + expr {[file size test.db] / 1024}
1.393 + } {36}
1.394 +
1.395 + # Run a linear scan query on tbl2. After reading ($jj*100) rows,
1.396 + # run the incremental vacuum to shrink the database.
1.397 + #
1.398 + do_test incrvacuum-6.${jj}.2 {
1.399 + set ::nRow 0
1.400 + db eval {SELECT a FROM tbl2} {} {
1.401 + if {$a == [expr $jj*100]} {
1.402 + db eval {PRAGMA incremental_vacuum}
1.403 + }
1.404 + incr ::nRow
1.405 + }
1.406 + list [expr {[file size test.db] / 1024}] $nRow
1.407 + } {19 1000}
1.408 +}
1.409 +
1.410 +#---------------------------------------------------------------------
1.411 +# This test - incrvacuum-7.* - is to check that the database can be
1.412 +# written in the middle of an incremental vacuum.
1.413 +#
1.414 +set ::iWrite 1
1.415 +while 1 {
1.416 + do_test incrvacuum-7.${::iWrite}.1 {
1.417 + execsql {
1.418 + DROP TABLE IF EXISTS tbl1;
1.419 + DROP TABLE IF EXISTS tbl2;
1.420 + PRAGMA incremental_vacuum;
1.421 + CREATE TABLE tbl1(a, b);
1.422 + CREATE TABLE tbl2(a, b);
1.423 + BEGIN;
1.424 + }
1.425 + for {set ii 0} {$ii < 1000} {incr ii} {
1.426 + db eval {INSERT INTO tbl1 VALUES($ii, $ii || $ii)}
1.427 + }
1.428 + execsql {
1.429 + INSERT INTO tbl2 SELECT * FROM tbl1;
1.430 + COMMIT;
1.431 + DROP TABLE tbl1;
1.432 + }
1.433 + expr {[file size test.db] / 1024}
1.434 + } {36}
1.435 +
1.436 + do_test incrvacuum-7.${::iWrite}.2 {
1.437 + set ::nRow 0
1.438 + db eval {PRAGMA incremental_vacuum} {
1.439 + incr ::nRow
1.440 + if {$::nRow == $::iWrite} {
1.441 + db eval {
1.442 + CREATE TABLE tbl1(a, b);
1.443 + INSERT INTO tbl1 VALUES('hello', 'world');
1.444 + }
1.445 + }
1.446 + }
1.447 + list [expr {[file size test.db] / 1024}]
1.448 + } {20}
1.449 +
1.450 + do_test incrvacuum-7.${::iWrite}.3 {
1.451 + execsql {
1.452 + SELECT * FROM tbl1;
1.453 + }
1.454 + } {hello world}
1.455 +
1.456 + if {$::nRow == $::iWrite} break
1.457 + incr ::iWrite
1.458 +}
1.459 +
1.460 +#---------------------------------------------------------------------
1.461 +# This test - incrvacuum-8.* - is to check that nothing goes wrong
1.462 +# with an incremental-vacuum if it is the first statement executed
1.463 +# after an existing database is opened.
1.464 +#
1.465 +# At one point, this would always return SQLITE_SCHEMA (which
1.466 +# causes an infinite loop in tclsqlite.c if using the Tcl interface).
1.467 +#
1.468 +do_test incrvacuum-8.1 {
1.469 + db close
1.470 + sqlite3 db test.db
1.471 + execsql {
1.472 + PRAGMA incremental_vacuum(50);
1.473 + }
1.474 +} {}
1.475 +
1.476 +#---------------------------------------------------------------------
1.477 +# At one point this test case was causing an assert() to fail.
1.478 +#
1.479 +do_test incrvacuum-9.1 {
1.480 + db close
1.481 + file delete -force test.db test.db-journal
1.482 + sqlite3 db test.db
1.483 +
1.484 + execsql {
1.485 + PRAGMA auto_vacuum = 'incremental';
1.486 + CREATE TABLE t1(a, b, c);
1.487 + CREATE TABLE t2(a, b, c);
1.488 + INSERT INTO t2 VALUES(randstr(500,500),randstr(500,500),randstr(500,500));
1.489 + INSERT INTO t1 VALUES(1, 2, 3);
1.490 + INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
1.491 + INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
1.492 + INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
1.493 + INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
1.494 + INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
1.495 + INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
1.496 + INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
1.497 + INSERT INTO t1 SELECT a||a, b||b, c||c FROM t1;
1.498 + }
1.499 +} {}
1.500 +
1.501 +do_test incrvacuum-9.2 {
1.502 + execsql {
1.503 + PRAGMA synchronous = 'OFF';
1.504 + BEGIN;
1.505 + UPDATE t1 SET a = a, b = b, c = c;
1.506 + DROP TABLE t2;
1.507 + PRAGMA incremental_vacuum(10);
1.508 + ROLLBACK;
1.509 + }
1.510 +} {}
1.511 +
1.512 +do_test incrvacuum-9.3 {
1.513 + execsql {
1.514 + PRAGMA cache_size = 10;
1.515 + BEGIN;
1.516 + UPDATE t1 SET a = a, b = b, c = c;
1.517 + DROP TABLE t2;
1.518 + PRAGMA incremental_vacuum(10);
1.519 + ROLLBACK;
1.520 + }
1.521 +} {}
1.522 +
1.523 +#---------------------------------------------------------------------
1.524 +# Test that the parameter to the incremental_vacuum pragma works. That
1.525 +# is, if the user executes "PRAGMA incremental_vacuum(N)", at most
1.526 +# N pages are vacuumed.
1.527 +#
1.528 +do_test incrvacuum-10.1 {
1.529 + execsql {
1.530 + DROP TABLE t1;
1.531 + DROP TABLE t2;
1.532 + }
1.533 + expr [file size test.db] / 1024
1.534 +} {29}
1.535 +
1.536 +do_test incrvacuum-10.2 {
1.537 + execsql {
1.538 + PRAGMA incremental_vacuum(1);
1.539 + }
1.540 + expr [file size test.db] / 1024
1.541 +} {28}
1.542 +
1.543 +do_test incrvacuum-10.3 {
1.544 + execsql {
1.545 + PRAGMA incremental_vacuum(5);
1.546 + }
1.547 + expr [file size test.db] / 1024
1.548 +} {23}
1.549 +
1.550 +do_test incrvacuum-10.4 {
1.551 + execsql {
1.552 + PRAGMA incremental_vacuum('1');
1.553 + }
1.554 + expr [file size test.db] / 1024
1.555 +} {22}
1.556 +
1.557 +do_test incrvacuum-10.5 {
1.558 + execsql {
1.559 + PRAGMA incremental_vacuum("+3");
1.560 + }
1.561 + expr [file size test.db] / 1024
1.562 +} {19}
1.563 +
1.564 +do_test incrvacuum-10.6 {
1.565 + execsql {
1.566 + PRAGMA incremental_vacuum = 1;
1.567 + }
1.568 + expr [file size test.db] / 1024
1.569 +} {18}
1.570 +
1.571 +do_test incrvacuum-10.7 {
1.572 + # Use a really big number as an argument to incremetal_vacuum. Should
1.573 + # be interpreted as "free all possible space".
1.574 + execsql {
1.575 + PRAGMA incremental_vacuum(2147483649);
1.576 + }
1.577 + expr [file size test.db] / 1024
1.578 +} {1}
1.579 +
1.580 +do_test incrvacuum-10.8 {
1.581 + execsql {
1.582 + CREATE TABLE t1(x);
1.583 + INSERT INTO t1 VALUES(hex(randomblob(1000)));
1.584 + DROP TABLE t1;
1.585 + }
1.586 + # A negative number means free all possible space.
1.587 + execsql {
1.588 + PRAGMA incremental_vacuum=-1;
1.589 + }
1.590 + expr [file size test.db] / 1024
1.591 +} {1}
1.592 +
1.593 +#----------------------------------------------------------------
1.594 +# Test that if we set the auto_vacuum mode to 'incremental', then
1.595 +# create a database, thereafter that database defaults to incremental
1.596 +# vacuum mode.
1.597 +#
1.598 +db close
1.599 +file delete -force test.db test.db-journal
1.600 +sqlite3 db test.db
1.601 +
1.602 +ifcapable default_autovacuum {
1.603 + do_test incrvacuum-11.1-av-dflt-on {
1.604 + execsql {
1.605 + PRAGMA auto_vacuum;
1.606 + }
1.607 + } $AUTOVACUUM
1.608 +} else {
1.609 + do_test incrvacuum-11.1-av-dflt-off {
1.610 + execsql {
1.611 + PRAGMA auto_vacuum;
1.612 + }
1.613 + } {0}
1.614 +}
1.615 +do_test incrvacuum-11.2 {
1.616 + execsql {
1.617 + PRAGMA auto_vacuum = incremental;
1.618 + }
1.619 +} {}
1.620 +do_test incrvacuum-11.3 {
1.621 + execsql {
1.622 + PRAGMA auto_vacuum;
1.623 + }
1.624 +} {2}
1.625 +do_test incrvacuum-11.4 {
1.626 + # The database has now been created.
1.627 + expr {[file size test.db]>0}
1.628 +} {1}
1.629 +do_test incrvacuum-11.5 {
1.630 + # Close and reopen the connection.
1.631 + db close
1.632 + sqlite3 db test.db
1.633 +
1.634 + # Test we are still in incremental vacuum mode.
1.635 + execsql { PRAGMA auto_vacuum; }
1.636 +} {2}
1.637 +do_test incrvacuum-11.6 {
1.638 + execsql {
1.639 + PRAGMA auto_vacuum = 'full';
1.640 + PRAGMA auto_vacuum;
1.641 + }
1.642 +} {1}
1.643 +do_test incrvacuum-11.7 {
1.644 + # Close and reopen the connection.
1.645 + db close
1.646 + sqlite3 db test.db
1.647 +
1.648 + # Test we are still in "full" auto-vacuum mode.
1.649 + execsql { PRAGMA auto_vacuum; }
1.650 +} {1}
1.651 +
1.652 +#----------------------------------------------------------------------
1.653 +# Special case: What happens if the database is locked when a "PRAGMA
1.654 +# auto_vacuum = XXX" statement is executed.
1.655 +#
1.656 +db close
1.657 +file delete -force test.db test.db-journal
1.658 +sqlite3 db test.db
1.659 +
1.660 +do_test incrvacuum-12.1 {
1.661 + execsql {
1.662 + PRAGMA auto_vacuum = 1;
1.663 + }
1.664 + expr {[file size test.db]>0}
1.665 +} {1}
1.666 +
1.667 +# Try to change the auto-vacuum from "full" to "incremental" while the
1.668 +# database is locked. Nothing should change.
1.669 +#
1.670 +do_test incrvacuum-12.2 {
1.671 + sqlite3 db2 test.db
1.672 + execsql { BEGIN EXCLUSIVE; } db2
1.673 + catchsql { PRAGMA auto_vacuum = 2; }
1.674 +} {1 {database is locked}}
1.675 +
1.676 +do_test incrvacuum-12.3 {
1.677 + execsql { ROLLBACK; } db2
1.678 + execsql { PRAGMA auto_vacuum }
1.679 +} {1}
1.680 +
1.681 +do_test incrvacuum-12.3 {
1.682 + execsql { SELECT * FROM sqlite_master }
1.683 + execsql { PRAGMA auto_vacuum }
1.684 +} {1}
1.685 +
1.686 +#----------------------------------------------------------------------
1.687 +# Special case #2: What if one process prepares a "PRAGMA auto_vacuum = XXX"
1.688 +# statement when the database is empty, but doesn't execute it until
1.689 +# after some other process has created the database.
1.690 +#
1.691 +db2 close
1.692 +db close
1.693 +file delete -force test.db test.db-journal
1.694 +sqlite3 db test.db ; set ::DB [sqlite3_connection_pointer db]
1.695 +sqlite3 db2 test.db
1.696 +
1.697 +do_test incrvacuum-13.1 {
1.698 + # File size is sometimes 1 instead of 0 due to the hack we put in
1.699 + # to work around ticket #3260. Search for comments on #3260 in
1.700 + # os_unix.c.
1.701 + expr {[file size test.db]>1}
1.702 +} {0}
1.703 +do_test incrvacuum-13.2 {
1.704 + set ::STMT [sqlite3_prepare $::DB {PRAGMA auto_vacuum = 2} -1 DUMMY]
1.705 + execsql {
1.706 + PRAGMA auto_vacuum = none;
1.707 + PRAGMA default_cache_size = 1024;
1.708 + PRAGMA auto_vacuum;
1.709 + } db2
1.710 +} {0}
1.711 +do_test incrvacuum-13.3 {
1.712 + expr {[file size test.db]>0}
1.713 +} {1}
1.714 +do_test incrvacuum-13.4 {
1.715 + set rc [sqlite3_step $::STMT]
1.716 + list $rc [sqlite3_finalize $::STMT]
1.717 +} {SQLITE_DONE SQLITE_OK}
1.718 +do_test incrvacuum-13.5 {
1.719 + execsql {
1.720 + PRAGMA auto_vacuum;
1.721 + }
1.722 +} {0}
1.723 +
1.724 +
1.725 +# Verify that the incremental_vacuum pragma fails gracefully if it
1.726 +# is used against an invalid database file.
1.727 +#
1.728 +do_test incrvacuum-14.1 {
1.729 + set out [open invalid.db w]
1.730 + puts $out "This is not an SQLite database file"
1.731 + close $out
1.732 + sqlite3 db3 invalid.db
1.733 + catchsql {
1.734 + PRAGMA incremental_vacuum(10);
1.735 + } db3
1.736 +} {1 {file is encrypted or is not a database}}
1.737 +
1.738 +db2 close
1.739 +db3 close
1.740 +finish_test