1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/autovacuum.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,665 @@
1.4 +# 2001 September 15
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 SELECT statement.
1.16 +#
1.17 +# $Id: autovacuum.test,v 1.28 2008/09/10 10:57:28 danielk1977 Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# If this build of the library does not support auto-vacuum, omit this
1.23 +# whole file.
1.24 +ifcapable {!autovacuum || !pragma} {
1.25 + finish_test
1.26 + return
1.27 +}
1.28 +
1.29 +# Return a string $len characters long. The returned string is $char repeated
1.30 +# over and over. For example, [make_str abc 8] returns "abcabcab".
1.31 +proc make_str {char len} {
1.32 + set str [string repeat $char. $len]
1.33 + return [string range $str 0 [expr $len-1]]
1.34 +}
1.35 +
1.36 +# Return the number of pages in the file test.db by looking at the file system.
1.37 +proc file_pages {} {
1.38 + return [expr [file size test.db] / 1024]
1.39 +}
1.40 +
1.41 +#-------------------------------------------------------------------------
1.42 +# Test cases autovacuum-1.* work as follows:
1.43 +#
1.44 +# 1. A table with a single indexed field is created.
1.45 +# 2. Approximately 20 rows are inserted into the table. Each row is long
1.46 +# enough such that it uses at least 2 overflow pages for both the table
1.47 +# and index entry.
1.48 +# 3. The rows are deleted in a psuedo-random order. Sometimes only one row
1.49 +# is deleted per transaction, sometimes more than one.
1.50 +# 4. After each transaction the table data is checked to ensure it is correct
1.51 +# and a "PRAGMA integrity_check" is executed.
1.52 +# 5. Once all the rows are deleted the file is checked to make sure it
1.53 +# consists of exactly 4 pages.
1.54 +#
1.55 +# Steps 2-5 are repeated for a few different psuedo-random delete patterns
1.56 +# (defined by the $delete_orders list).
1.57 +set delete_orders [list]
1.58 +lappend delete_orders {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20}
1.59 +lappend delete_orders {20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1}
1.60 +lappend delete_orders {8 18 2 4 14 11 13 3 10 7 9 5 12 17 19 15 20 6 16 1}
1.61 +lappend delete_orders {10 3 11 17 19 20 7 4 13 6 1 14 16 12 9 18 8 15 5 2}
1.62 +lappend delete_orders {{1 2 3 4 5 6 7 8 9 10} {11 12 13 14 15 16 17 18 19 20}}
1.63 +lappend delete_orders {{19 8 17 15} {16 11 9 14} {18 5 3 1} {13 20 7 2} {6 12}}
1.64 +
1.65 +# The length of each table entry.
1.66 +# set ENTRY_LEN 3500
1.67 +set ENTRY_LEN 3500
1.68 +
1.69 +do_test autovacuum-1.1 {
1.70 + execsql {
1.71 + PRAGMA auto_vacuum = 1;
1.72 + CREATE TABLE av1(a);
1.73 + CREATE INDEX av1_idx ON av1(a);
1.74 + }
1.75 +} {}
1.76 +
1.77 +set tn 0
1.78 +foreach delete_order $delete_orders {
1.79 + incr tn
1.80 +
1.81 + # Set up the table.
1.82 + set ::tbl_data [list]
1.83 + foreach i [lsort -integer [eval concat $delete_order]] {
1.84 + execsql "INSERT INTO av1 (oid, a) VALUES($i, '[make_str $i $ENTRY_LEN]')"
1.85 + lappend ::tbl_data [make_str $i $ENTRY_LEN]
1.86 + }
1.87 +
1.88 + # Make sure the integrity check passes with the initial data.
1.89 + ifcapable {integrityck} {
1.90 + do_test autovacuum-1.$tn.1 {
1.91 + execsql {
1.92 + pragma integrity_check
1.93 + }
1.94 + } {ok}
1.95 + }
1.96 +
1.97 + foreach delete $delete_order {
1.98 + # Delete one set of rows from the table.
1.99 + do_test autovacuum-1.$tn.($delete).1 {
1.100 + execsql "
1.101 + DELETE FROM av1 WHERE oid = [join $delete " OR oid = "]
1.102 + "
1.103 + } {}
1.104 +
1.105 + # Do the integrity check.
1.106 + ifcapable {integrityck} {
1.107 + do_test autovacuum-1.$tn.($delete).2 {
1.108 + execsql {
1.109 + pragma integrity_check
1.110 + }
1.111 + } {ok}
1.112 + }
1.113 + # Ensure the data remaining in the table is what was expected.
1.114 + foreach d $delete {
1.115 + set idx [lsearch $::tbl_data [make_str $d $ENTRY_LEN]]
1.116 + set ::tbl_data [lreplace $::tbl_data $idx $idx]
1.117 + }
1.118 + do_test autovacuum-1.$tn.($delete).3 {
1.119 + execsql {
1.120 + select a from av1
1.121 + }
1.122 + } $::tbl_data
1.123 + }
1.124 +
1.125 + # All rows have been deleted. Ensure the file has shrunk to 4 pages.
1.126 + do_test autovacuum-1.$tn.3 {
1.127 + file_pages
1.128 + } {4}
1.129 +}
1.130 +
1.131 +#---------------------------------------------------------------------------
1.132 +# Tests cases autovacuum-2.* test that root pages are allocated
1.133 +# and deallocated correctly at the start of the file. Operation is roughly as
1.134 +# follows:
1.135 +#
1.136 +# autovacuum-2.1.*: Drop the tables that currently exist in the database.
1.137 +# autovacuum-2.2.*: Create some tables. Ensure that data pages can be
1.138 +# moved correctly to make space for new root-pages.
1.139 +# autovacuum-2.3.*: Drop one of the tables just created (not the last one),
1.140 +# and check that one of the other tables is moved to
1.141 +# the free root-page location.
1.142 +# autovacuum-2.4.*: Check that a table can be created correctly when the
1.143 +# root-page it requires is on the free-list.
1.144 +# autovacuum-2.5.*: Check that a table with indices can be dropped. This
1.145 +# is slightly tricky because dropping one of the
1.146 +# indices/table btrees could move the root-page of another.
1.147 +# The code-generation layer of SQLite overcomes this problem
1.148 +# by dropping the btrees in descending order of root-pages.
1.149 +# This test ensures that this actually happens.
1.150 +#
1.151 +do_test autovacuum-2.1.1 {
1.152 + execsql {
1.153 + DROP TABLE av1;
1.154 + }
1.155 +} {}
1.156 +do_test autovacuum-2.1.2 {
1.157 + file_pages
1.158 +} {1}
1.159 +
1.160 +# Create a table and put some data in it.
1.161 +do_test autovacuum-2.2.1 {
1.162 + execsql {
1.163 + CREATE TABLE av1(x);
1.164 + SELECT rootpage FROM sqlite_master ORDER BY rootpage;
1.165 + }
1.166 +} {3}
1.167 +do_test autovacuum-2.2.2 {
1.168 + execsql "
1.169 + INSERT INTO av1 VALUES('[make_str abc 3000]');
1.170 + INSERT INTO av1 VALUES('[make_str def 3000]');
1.171 + INSERT INTO av1 VALUES('[make_str ghi 3000]');
1.172 + INSERT INTO av1 VALUES('[make_str jkl 3000]');
1.173 + "
1.174 + set ::av1_data [db eval {select * from av1}]
1.175 + file_pages
1.176 +} {15}
1.177 +
1.178 +# Create another table. Check it is located immediately after the first.
1.179 +# This test case moves the second page in an over-flow chain.
1.180 +do_test autovacuum-2.2.3 {
1.181 + execsql {
1.182 + CREATE TABLE av2(x);
1.183 + SELECT rootpage FROM sqlite_master ORDER BY rootpage;
1.184 + }
1.185 +} {3 4}
1.186 +do_test autovacuum-2.2.4 {
1.187 + file_pages
1.188 +} {16}
1.189 +
1.190 +# Create another table. Check it is located immediately after the second.
1.191 +# This test case moves the first page in an over-flow chain.
1.192 +do_test autovacuum-2.2.5 {
1.193 + execsql {
1.194 + CREATE TABLE av3(x);
1.195 + SELECT rootpage FROM sqlite_master ORDER BY rootpage;
1.196 + }
1.197 +} {3 4 5}
1.198 +do_test autovacuum-2.2.6 {
1.199 + file_pages
1.200 +} {17}
1.201 +
1.202 +# Create another table. Check it is located immediately after the second.
1.203 +# This test case moves a btree leaf page.
1.204 +do_test autovacuum-2.2.7 {
1.205 + execsql {
1.206 + CREATE TABLE av4(x);
1.207 + SELECT rootpage FROM sqlite_master ORDER BY rootpage;
1.208 + }
1.209 +} {3 4 5 6}
1.210 +do_test autovacuum-2.2.8 {
1.211 + file_pages
1.212 +} {18}
1.213 +do_test autovacuum-2.2.9 {
1.214 + execsql {
1.215 + select * from av1
1.216 + }
1.217 +} $av1_data
1.218 +
1.219 +do_test autovacuum-2.3.1 {
1.220 + execsql {
1.221 + INSERT INTO av2 SELECT 'av1' || x FROM av1;
1.222 + INSERT INTO av3 SELECT 'av2' || x FROM av1;
1.223 + INSERT INTO av4 SELECT 'av3' || x FROM av1;
1.224 + }
1.225 + set ::av2_data [execsql {select x from av2}]
1.226 + set ::av3_data [execsql {select x from av3}]
1.227 + set ::av4_data [execsql {select x from av4}]
1.228 + file_pages
1.229 +} {54}
1.230 +do_test autovacuum-2.3.2 {
1.231 + execsql {
1.232 + DROP TABLE av2;
1.233 + SELECT rootpage FROM sqlite_master ORDER BY rootpage;
1.234 + }
1.235 +} {3 4 5}
1.236 +do_test autovacuum-2.3.3 {
1.237 + file_pages
1.238 +} {41}
1.239 +do_test autovacuum-2.3.4 {
1.240 + execsql {
1.241 + SELECT x FROM av3;
1.242 + }
1.243 +} $::av3_data
1.244 +do_test autovacuum-2.3.5 {
1.245 + execsql {
1.246 + SELECT x FROM av4;
1.247 + }
1.248 +} $::av4_data
1.249 +
1.250 +# Drop all the tables in the file. This puts all pages except the first 2
1.251 +# (the sqlite_master root-page and the first pointer map page) on the
1.252 +# free-list.
1.253 +do_test autovacuum-2.4.1 {
1.254 + execsql {
1.255 + DROP TABLE av1;
1.256 + DROP TABLE av3;
1.257 + BEGIN;
1.258 + DROP TABLE av4;
1.259 + }
1.260 + file_pages
1.261 +} {15}
1.262 +do_test autovacuum-2.4.2 {
1.263 + for {set i 3} {$i<=10} {incr i} {
1.264 + execsql "CREATE TABLE av$i (x)"
1.265 + }
1.266 + file_pages
1.267 +} {15}
1.268 +do_test autovacuum-2.4.3 {
1.269 + execsql {
1.270 + SELECT rootpage FROM sqlite_master ORDER by rootpage
1.271 + }
1.272 +} {3 4 5 6 7 8 9 10}
1.273 +
1.274 +# Right now there are 5 free pages in the database. Consume and then free
1.275 +# a 520 pages. Then create 520 tables. This ensures that at least some of the
1.276 +# desired root-pages reside on the second free-list trunk page, and that the
1.277 +# trunk itself is required at some point.
1.278 +do_test autovacuum-2.4.4 {
1.279 + execsql "
1.280 + INSERT INTO av3 VALUES ('[make_str abcde [expr 1020*520 + 500]]');
1.281 + DELETE FROM av3;
1.282 + "
1.283 +} {}
1.284 +set root_page_list [list]
1.285 +set pending_byte_page [expr ($::sqlite_pending_byte / 1024) + 1]
1.286 +for {set i 3} {$i<=532} {incr i} {
1.287 + # 207 and 412 are pointer-map pages.
1.288 + if { $i!=207 && $i!=412 && $i != $pending_byte_page} {
1.289 + lappend root_page_list $i
1.290 + }
1.291 +}
1.292 +if {$i >= $pending_byte_page} {
1.293 + lappend root_page_list $i
1.294 +}
1.295 +do_test autovacuum-2.4.5 {
1.296 + for {set i 11} {$i<=530} {incr i} {
1.297 + execsql "CREATE TABLE av$i (x)"
1.298 + }
1.299 + execsql {
1.300 + SELECT rootpage FROM sqlite_master ORDER by rootpage
1.301 + }
1.302 +} $root_page_list
1.303 +
1.304 +# Just for fun, delete all those tables and see if the database is 1 page.
1.305 +do_test autovacuum-2.4.6 {
1.306 + execsql COMMIT;
1.307 + file_pages
1.308 +} [expr 561 + (($i >= $pending_byte_page)?1:0)]
1.309 +integrity_check autovacuum-2.4.6
1.310 +do_test autovacuum-2.4.7 {
1.311 + execsql BEGIN
1.312 + for {set i 3} {$i<=530} {incr i} {
1.313 + execsql "DROP TABLE av$i"
1.314 + }
1.315 + execsql COMMIT
1.316 + file_pages
1.317 +} 1
1.318 +
1.319 +# Create some tables with indices to drop.
1.320 +do_test autovacuum-2.5.1 {
1.321 + execsql {
1.322 + CREATE TABLE av1(a PRIMARY KEY, b, c);
1.323 + INSERT INTO av1 VALUES('av1 a', 'av1 b', 'av1 c');
1.324 +
1.325 + CREATE TABLE av2(a PRIMARY KEY, b, c);
1.326 + CREATE INDEX av2_i1 ON av2(b);
1.327 + CREATE INDEX av2_i2 ON av2(c);
1.328 + INSERT INTO av2 VALUES('av2 a', 'av2 b', 'av2 c');
1.329 +
1.330 + CREATE TABLE av3(a PRIMARY KEY, b, c);
1.331 + CREATE INDEX av3_i1 ON av3(b);
1.332 + INSERT INTO av3 VALUES('av3 a', 'av3 b', 'av3 c');
1.333 +
1.334 + CREATE TABLE av4(a, b, c);
1.335 + CREATE INDEX av4_i1 ON av4(a);
1.336 + CREATE INDEX av4_i2 ON av4(b);
1.337 + CREATE INDEX av4_i3 ON av4(c);
1.338 + CREATE INDEX av4_i4 ON av4(a, b, c);
1.339 + INSERT INTO av4 VALUES('av4 a', 'av4 b', 'av4 c');
1.340 + }
1.341 +} {}
1.342 +
1.343 +do_test autovacuum-2.5.2 {
1.344 + execsql {
1.345 + SELECT name, rootpage FROM sqlite_master;
1.346 + }
1.347 +} [list av1 3 sqlite_autoindex_av1_1 4 \
1.348 + av2 5 sqlite_autoindex_av2_1 6 av2_i1 7 av2_i2 8 \
1.349 + av3 9 sqlite_autoindex_av3_1 10 av3_i1 11 \
1.350 + av4 12 av4_i1 13 av4_i2 14 av4_i3 15 av4_i4 16 \
1.351 +]
1.352 +
1.353 +# The following 4 tests are SELECT queries that use the indices created.
1.354 +# If the root-pages in the internal schema are not updated correctly when
1.355 +# a table or indice is moved, these queries will fail. They are repeated
1.356 +# after each table is dropped (i.e. as test cases 2.5.*.[1..4]).
1.357 +do_test autovacuum-2.5.2.1 {
1.358 + execsql {
1.359 + SELECT * FROM av1 WHERE a = 'av1 a';
1.360 + }
1.361 +} {{av1 a} {av1 b} {av1 c}}
1.362 +do_test autovacuum-2.5.2.2 {
1.363 + execsql {
1.364 + SELECT * FROM av2 WHERE a = 'av2 a' AND b = 'av2 b' AND c = 'av2 c'
1.365 + }
1.366 +} {{av2 a} {av2 b} {av2 c}}
1.367 +do_test autovacuum-2.5.2.3 {
1.368 + execsql {
1.369 + SELECT * FROM av3 WHERE a = 'av3 a' AND b = 'av3 b';
1.370 + }
1.371 +} {{av3 a} {av3 b} {av3 c}}
1.372 +do_test autovacuum-2.5.2.4 {
1.373 + execsql {
1.374 + SELECT * FROM av4 WHERE a = 'av4 a' AND b = 'av4 b' AND c = 'av4 c';
1.375 + }
1.376 +} {{av4 a} {av4 b} {av4 c}}
1.377 +
1.378 +# Drop table av3. Indices av4_i2, av4_i3 and av4_i4 are moved to fill the two
1.379 +# root pages vacated. The operation proceeds as:
1.380 +# Step 1: Delete av3_i1 (root-page 11). Move root-page of av4_i4 to page 11.
1.381 +# Step 2: Delete av3 (root-page 10). Move root-page of av4_i3 to page 10.
1.382 +# Step 3: Delete sqlite_autoindex_av1_3 (root-page 9). Move av4_i2 to page 9.
1.383 +do_test autovacuum-2.5.3 {
1.384 + execsql {
1.385 + DROP TABLE av3;
1.386 + SELECT name, rootpage FROM sqlite_master;
1.387 + }
1.388 +} [list av1 3 sqlite_autoindex_av1_1 4 \
1.389 + av2 5 sqlite_autoindex_av2_1 6 av2_i1 7 av2_i2 8 \
1.390 + av4 12 av4_i1 13 av4_i2 9 av4_i3 10 av4_i4 11 \
1.391 +]
1.392 +do_test autovacuum-2.5.3.1 {
1.393 + execsql {
1.394 + SELECT * FROM av1 WHERE a = 'av1 a';
1.395 + }
1.396 +} {{av1 a} {av1 b} {av1 c}}
1.397 +do_test autovacuum-2.5.3.2 {
1.398 + execsql {
1.399 + SELECT * FROM av2 WHERE a = 'av2 a' AND b = 'av2 b' AND c = 'av2 c'
1.400 + }
1.401 +} {{av2 a} {av2 b} {av2 c}}
1.402 +do_test autovacuum-2.5.3.3 {
1.403 + execsql {
1.404 + SELECT * FROM av4 WHERE a = 'av4 a' AND b = 'av4 b' AND c = 'av4 c';
1.405 + }
1.406 +} {{av4 a} {av4 b} {av4 c}}
1.407 +
1.408 +# Drop table av1:
1.409 +# Step 1: Delete av1 (root page 4). Root-page of av4_i1 fills the gap.
1.410 +# Step 2: Delete sqlite_autoindex_av1_1 (root page 3). Move av4 to the gap.
1.411 +do_test autovacuum-2.5.4 {
1.412 + execsql {
1.413 + DROP TABLE av1;
1.414 + SELECT name, rootpage FROM sqlite_master;
1.415 + }
1.416 +} [list av2 5 sqlite_autoindex_av2_1 6 av2_i1 7 av2_i2 8 \
1.417 + av4 3 av4_i1 4 av4_i2 9 av4_i3 10 av4_i4 11 \
1.418 +]
1.419 +do_test autovacuum-2.5.4.2 {
1.420 + execsql {
1.421 + SELECT * FROM av2 WHERE a = 'av2 a' AND b = 'av2 b' AND c = 'av2 c'
1.422 + }
1.423 +} {{av2 a} {av2 b} {av2 c}}
1.424 +do_test autovacuum-2.5.4.4 {
1.425 + execsql {
1.426 + SELECT * FROM av4 WHERE a = 'av4 a' AND b = 'av4 b' AND c = 'av4 c';
1.427 + }
1.428 +} {{av4 a} {av4 b} {av4 c}}
1.429 +
1.430 +# Drop table av4:
1.431 +# Step 1: Delete av4_i4.
1.432 +# Step 2: Delete av4_i3.
1.433 +# Step 3: Delete av4_i2.
1.434 +# Step 4: Delete av4_i1. av2_i2 replaces it.
1.435 +# Step 5: Delete av4. av2_i1 replaces it.
1.436 +do_test autovacuum-2.5.5 {
1.437 + execsql {
1.438 + DROP TABLE av4;
1.439 + SELECT name, rootpage FROM sqlite_master;
1.440 + }
1.441 +} [list av2 5 sqlite_autoindex_av2_1 6 av2_i1 3 av2_i2 4]
1.442 +do_test autovacuum-2.5.5.2 {
1.443 + execsql {
1.444 + SELECT * FROM av2 WHERE a = 'av2 a' AND b = 'av2 b' AND c = 'av2 c'
1.445 + }
1.446 +} {{av2 a} {av2 b} {av2 c}}
1.447 +
1.448 +#--------------------------------------------------------------------------
1.449 +# Test cases autovacuum-3.* test the operation of the "PRAGMA auto_vacuum"
1.450 +# command.
1.451 +#
1.452 +do_test autovacuum-3.1 {
1.453 + execsql {
1.454 + PRAGMA auto_vacuum;
1.455 + }
1.456 +} {1}
1.457 +do_test autovacuum-3.2 {
1.458 + db close
1.459 + sqlite3 db test.db
1.460 + execsql {
1.461 + PRAGMA auto_vacuum;
1.462 + }
1.463 +} {1}
1.464 +do_test autovacuum-3.3 {
1.465 + execsql {
1.466 + PRAGMA auto_vacuum = 0;
1.467 + PRAGMA auto_vacuum;
1.468 + }
1.469 +} {1}
1.470 +
1.471 +do_test autovacuum-3.4 {
1.472 + db close
1.473 + file delete -force test.db
1.474 + sqlite3 db test.db
1.475 + execsql {
1.476 + PRAGMA auto_vacuum;
1.477 + }
1.478 +} $AUTOVACUUM
1.479 +do_test autovacuum-3.5 {
1.480 + execsql {
1.481 + CREATE TABLE av1(x);
1.482 + PRAGMA auto_vacuum;
1.483 + }
1.484 +} $AUTOVACUUM
1.485 +do_test autovacuum-3.6 {
1.486 + execsql {
1.487 + PRAGMA auto_vacuum = 1;
1.488 + PRAGMA auto_vacuum;
1.489 + }
1.490 +} [expr $AUTOVACUUM ? 1 : 0]
1.491 +do_test autovacuum-3.7 {
1.492 + execsql {
1.493 + DROP TABLE av1;
1.494 + }
1.495 + file_pages
1.496 +} [expr $AUTOVACUUM?1:2]
1.497 +
1.498 +
1.499 +#-----------------------------------------------------------------------
1.500 +# Test that if a statement transaction around a CREATE INDEX statement is
1.501 +# rolled back no corruption occurs.
1.502 +#
1.503 +do_test autovacuum-4.0 {
1.504 + # The last round of tests may have left the db in non-autovacuum mode.
1.505 + # Reset everything just in case.
1.506 + #
1.507 + db close
1.508 + file delete -force test.db test.db-journal
1.509 + sqlite3 db test.db
1.510 + execsql {
1.511 + PRAGMA auto_vacuum = 1;
1.512 + PRAGMA auto_vacuum;
1.513 + }
1.514 +} {1}
1.515 +do_test autovacuum-4.1 {
1.516 + execsql {
1.517 + CREATE TABLE av1(a, b);
1.518 + BEGIN;
1.519 + }
1.520 + for {set i 0} {$i<100} {incr i} {
1.521 + execsql "INSERT INTO av1 VALUES($i, '[string repeat X 200]');"
1.522 + }
1.523 + execsql "INSERT INTO av1 VALUES(99, '[string repeat X 200]');"
1.524 + execsql {
1.525 + SELECT sum(a) FROM av1;
1.526 + }
1.527 +} {5049}
1.528 +do_test autovacuum-4.2 {
1.529 + catchsql {
1.530 + CREATE UNIQUE INDEX av1_i ON av1(a);
1.531 + }
1.532 +} {1 {indexed columns are not unique}}
1.533 +do_test autovacuum-4.3 {
1.534 + execsql {
1.535 + SELECT sum(a) FROM av1;
1.536 + }
1.537 +} {5049}
1.538 +do_test autovacuum-4.4 {
1.539 + execsql {
1.540 + COMMIT;
1.541 + }
1.542 +} {}
1.543 +
1.544 +ifcapable integrityck {
1.545 +
1.546 +# Ticket #1727
1.547 +do_test autovacuum-5.1 {
1.548 + db close
1.549 + sqlite3 db :memory:
1.550 + db eval {
1.551 + PRAGMA auto_vacuum=1;
1.552 + CREATE TABLE t1(a);
1.553 + CREATE TABLE t2(a);
1.554 + DROP TABLE t1;
1.555 + PRAGMA integrity_check;
1.556 + }
1.557 +} ok
1.558 +
1.559 +}
1.560 +
1.561 +# Ticket #1728.
1.562 +#
1.563 +# In autovacuum mode, when tables or indices are deleted, the rootpage
1.564 +# values in the symbol table have to be updated. There was a bug in this
1.565 +# logic so that if an index/table was moved twice, the second move might
1.566 +# not occur. This would leave the internal symbol table in an inconsistent
1.567 +# state causing subsequent statements to fail.
1.568 +#
1.569 +# The problem is difficult to reproduce. The sequence of statements in
1.570 +# the following test are carefully designed make it occur and thus to
1.571 +# verify that this very obscure bug has been resolved.
1.572 +#
1.573 +ifcapable integrityck&&memorydb {
1.574 +
1.575 +do_test autovacuum-6.1 {
1.576 + db close
1.577 + sqlite3 db :memory:
1.578 + db eval {
1.579 + PRAGMA auto_vacuum=1;
1.580 + CREATE TABLE t1(a, b);
1.581 + CREATE INDEX i1 ON t1(a);
1.582 + CREATE TABLE t2(a);
1.583 + CREATE INDEX i2 ON t2(a);
1.584 + CREATE TABLE t3(a);
1.585 + CREATE INDEX i3 ON t2(a);
1.586 + CREATE INDEX x ON t1(b);
1.587 + DROP TABLE t3;
1.588 + PRAGMA integrity_check;
1.589 + DROP TABLE t2;
1.590 + PRAGMA integrity_check;
1.591 + DROP TABLE t1;
1.592 + PRAGMA integrity_check;
1.593 + }
1.594 +} {ok ok ok}
1.595 +
1.596 +}
1.597 +
1.598 +#---------------------------------------------------------------------
1.599 +# Test cases autovacuum-7.X test the case where a page must be moved
1.600 +# and the destination location collides with at least one other
1.601 +# entry in the page hash-table (internal to the pager.c module.
1.602 +#
1.603 +do_test autovacuum-7.1 {
1.604 + db close
1.605 + file delete -force test.db
1.606 + file delete -force test.db-journal
1.607 + sqlite3 db test.db
1.608 +
1.609 + execsql {
1.610 + PRAGMA auto_vacuum=1;
1.611 + CREATE TABLE t1(a, b, PRIMARY KEY(a, b));
1.612 + INSERT INTO t1 VALUES(randstr(400,400),randstr(400,400));
1.613 + INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2
1.614 + INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 4
1.615 + INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 8
1.616 + INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 16
1.617 + INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 32
1.618 + }
1.619 +
1.620 + expr {[file size test.db] / 1024}
1.621 +} {73}
1.622 +
1.623 +do_test autovacuum-7.2 {
1.624 + execsql {
1.625 + CREATE TABLE t2(a, b, PRIMARY KEY(a, b));
1.626 + INSERT INTO t2 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2
1.627 + CREATE TABLE t3(a, b, PRIMARY KEY(a, b));
1.628 + INSERT INTO t3 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2
1.629 + CREATE TABLE t4(a, b, PRIMARY KEY(a, b));
1.630 + INSERT INTO t4 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2
1.631 + CREATE TABLE t5(a, b, PRIMARY KEY(a, b));
1.632 + INSERT INTO t5 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2
1.633 + }
1.634 + expr {[file size test.db] / 1024}
1.635 +} {354}
1.636 +
1.637 +do_test autovacuum-7.3 {
1.638 + db close
1.639 + sqlite3 db test.db
1.640 + execsql {
1.641 + BEGIN;
1.642 + DELETE FROM t4;
1.643 + COMMIT;
1.644 + SELECT count(*) FROM t1;
1.645 + }
1.646 + expr {[file size test.db] / 1024}
1.647 +} {286}
1.648 +
1.649 +#------------------------------------------------------------------------
1.650 +# Additional tests.
1.651 +#
1.652 +# Try to determine the autovacuum setting for a database that is locked.
1.653 +#
1.654 +do_test autovacuum-8.1 {
1.655 + db close
1.656 + sqlite3 db test.db
1.657 + sqlite3 db2 test.db
1.658 + db eval {PRAGMA auto_vacuum}
1.659 +} {1}
1.660 +do_test autovacuum-8.2 {
1.661 + db eval {BEGIN EXCLUSIVE}
1.662 + catchsql {PRAGMA auto_vacuum} db2
1.663 +} {1 {database is locked}}
1.664 +catch {db2 close}
1.665 +catch {db eval {COMMIT}}
1.666 +
1.667 +
1.668 +finish_test