gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/autovacuum.test (about) 1 # 2001 September 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 # This file implements regression tests for SQLite library. The 12 # focus of this file is testing the autovacuum feature. 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 # If this build of the library does not support auto-vacuum, omit this 19 # whole file. 20 ifcapable {!autovacuum || !pragma} { 21 finish_test 22 return 23 } 24 25 # Return a string $len characters long. The returned string is $char repeated 26 # over and over. For example, [make_str abc 8] returns "abcabcab". 27 proc make_str {char len} { 28 set str [string repeat $char. $len] 29 return [string range $str 0 [expr $len-1]] 30 } 31 32 # Return the number of pages in the file test.db by looking at the file system. 33 proc file_pages {} { 34 return [expr [file size test.db] / 1024] 35 } 36 37 #------------------------------------------------------------------------- 38 # Test cases autovacuum-1.* work as follows: 39 # 40 # 1. A table with a single indexed field is created. 41 # 2. Approximately 20 rows are inserted into the table. Each row is long 42 # enough such that it uses at least 2 overflow pages for both the table 43 # and index entry. 44 # 3. The rows are deleted in a psuedo-random order. Sometimes only one row 45 # is deleted per transaction, sometimes more than one. 46 # 4. After each transaction the table data is checked to ensure it is correct 47 # and a "PRAGMA integrity_check" is executed. 48 # 5. Once all the rows are deleted the file is checked to make sure it 49 # consists of exactly 4 pages. 50 # 51 # Steps 2-5 are repeated for a few different psuedo-random delete patterns 52 # (defined by the $delete_orders list). 53 set delete_orders [list] 54 lappend delete_orders {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20} 55 lappend delete_orders {20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1} 56 lappend delete_orders {8 18 2 4 14 11 13 3 10 7 9 5 12 17 19 15 20 6 16 1} 57 lappend delete_orders {10 3 11 17 19 20 7 4 13 6 1 14 16 12 9 18 8 15 5 2} 58 lappend delete_orders {{1 2 3 4 5 6 7 8 9 10} {11 12 13 14 15 16 17 18 19 20}} 59 lappend delete_orders {{19 8 17 15} {16 11 9 14} {18 5 3 1} {13 20 7 2} {6 12}} 60 61 # The length of each table entry. 62 # set ENTRY_LEN 3500 63 set ENTRY_LEN 3500 64 65 do_test autovacuum-1.1 { 66 execsql { 67 PRAGMA auto_vacuum = 1; 68 CREATE TABLE av1(a); 69 CREATE INDEX av1_idx ON av1(a); 70 } 71 } {} 72 73 set tn 0 74 foreach delete_order $delete_orders { 75 incr tn 76 77 # Set up the table. 78 set ::tbl_data [list] 79 foreach i [lsort -integer [eval concat $delete_order]] { 80 execsql "INSERT INTO av1 (oid, a) VALUES($i, '[make_str $i $ENTRY_LEN]')" 81 lappend ::tbl_data [make_str $i $ENTRY_LEN] 82 } 83 84 # Make sure the integrity check passes with the initial data. 85 ifcapable {integrityck} { 86 do_test autovacuum-1.$tn.1 { 87 execsql { 88 pragma integrity_check 89 } 90 } {ok} 91 } 92 93 foreach delete $delete_order { 94 # Delete one set of rows from the table. 95 do_test autovacuum-1.$tn.($delete).1 { 96 execsql " 97 DELETE FROM av1 WHERE oid = [join $delete " OR oid = "] 98 " 99 } {} 100 101 # Do the integrity check. 102 ifcapable {integrityck} { 103 do_test autovacuum-1.$tn.($delete).2 { 104 execsql { 105 pragma integrity_check 106 } 107 } {ok} 108 } 109 # Ensure the data remaining in the table is what was expected. 110 foreach d $delete { 111 set idx [lsearch $::tbl_data [make_str $d $ENTRY_LEN]] 112 set ::tbl_data [lreplace $::tbl_data $idx $idx] 113 } 114 do_test autovacuum-1.$tn.($delete).3 { 115 execsql { 116 select a from av1 order by rowid 117 } 118 } $::tbl_data 119 } 120 121 # All rows have been deleted. Ensure the file has shrunk to 4 pages. 122 do_test autovacuum-1.$tn.3 { 123 file_pages 124 } {4} 125 } 126 127 #--------------------------------------------------------------------------- 128 # Tests cases autovacuum-2.* test that root pages are allocated 129 # and deallocated correctly at the start of the file. Operation is roughly as 130 # follows: 131 # 132 # autovacuum-2.1.*: Drop the tables that currently exist in the database. 133 # autovacuum-2.2.*: Create some tables. Ensure that data pages can be 134 # moved correctly to make space for new root-pages. 135 # autovacuum-2.3.*: Drop one of the tables just created (not the last one), 136 # and check that one of the other tables is moved to 137 # the free root-page location. 138 # autovacuum-2.4.*: Check that a table can be created correctly when the 139 # root-page it requires is on the free-list. 140 # autovacuum-2.5.*: Check that a table with indices can be dropped. This 141 # is slightly tricky because dropping one of the 142 # indices/table btrees could move the root-page of another. 143 # The code-generation layer of SQLite overcomes this problem 144 # by dropping the btrees in descending order of root-pages. 145 # This test ensures that this actually happens. 146 # 147 do_test autovacuum-2.1.1 { 148 execsql { 149 DROP TABLE av1; 150 } 151 } {} 152 do_test autovacuum-2.1.2 { 153 file_pages 154 } {1} 155 156 # Create a table and put some data in it. 157 do_test autovacuum-2.2.1 { 158 execsql { 159 CREATE TABLE av1(x); 160 SELECT rootpage FROM sqlite_master ORDER BY rootpage; 161 } 162 } {3} 163 do_test autovacuum-2.2.2 { 164 execsql " 165 INSERT INTO av1 VALUES('[make_str abc 3000]'); 166 INSERT INTO av1 VALUES('[make_str def 3000]'); 167 INSERT INTO av1 VALUES('[make_str ghi 3000]'); 168 INSERT INTO av1 VALUES('[make_str jkl 3000]'); 169 " 170 set ::av1_data [db eval {select * from av1}] 171 file_pages 172 } {15} 173 174 # Create another table. Check it is located immediately after the first. 175 # This test case moves the second page in an over-flow chain. 176 do_test autovacuum-2.2.3 { 177 execsql { 178 CREATE TABLE av2(x); 179 SELECT rootpage FROM sqlite_master ORDER BY rootpage; 180 } 181 } {3 4} 182 do_test autovacuum-2.2.4 { 183 file_pages 184 } {16} 185 186 # Create another table. Check it is located immediately after the second. 187 # This test case moves the first page in an over-flow chain. 188 do_test autovacuum-2.2.5 { 189 execsql { 190 CREATE TABLE av3(x); 191 SELECT rootpage FROM sqlite_master ORDER BY rootpage; 192 } 193 } {3 4 5} 194 do_test autovacuum-2.2.6 { 195 file_pages 196 } {17} 197 198 # Create another table. Check it is located immediately after the second. 199 # This test case moves a btree leaf page. 200 do_test autovacuum-2.2.7 { 201 execsql { 202 CREATE TABLE av4(x); 203 SELECT rootpage FROM sqlite_master ORDER BY rootpage; 204 } 205 } {3 4 5 6} 206 do_test autovacuum-2.2.8 { 207 file_pages 208 } {18} 209 do_test autovacuum-2.2.9 { 210 execsql { 211 select * from av1 212 } 213 } $av1_data 214 215 do_test autovacuum-2.3.1 { 216 execsql { 217 INSERT INTO av2 SELECT 'av1' || x FROM av1; 218 INSERT INTO av3 SELECT 'av2' || x FROM av1; 219 INSERT INTO av4 SELECT 'av3' || x FROM av1; 220 } 221 set ::av2_data [execsql {select x from av2}] 222 set ::av3_data [execsql {select x from av3}] 223 set ::av4_data [execsql {select x from av4}] 224 file_pages 225 } {54} 226 do_test autovacuum-2.3.2 { 227 execsql { 228 DROP TABLE av2; 229 SELECT rootpage FROM sqlite_master ORDER BY rootpage; 230 } 231 } {3 4 5} 232 do_test autovacuum-2.3.3 { 233 file_pages 234 } {41} 235 do_test autovacuum-2.3.4 { 236 execsql { 237 SELECT x FROM av3; 238 } 239 } $::av3_data 240 do_test autovacuum-2.3.5 { 241 execsql { 242 SELECT x FROM av4; 243 } 244 } $::av4_data 245 246 # Drop all the tables in the file. This puts all pages except the first 2 247 # (the sqlite_master root-page and the first pointer map page) on the 248 # free-list. 249 do_test autovacuum-2.4.1 { 250 execsql { 251 DROP TABLE av1; 252 DROP TABLE av3; 253 BEGIN; 254 DROP TABLE av4; 255 } 256 file_pages 257 } {15} 258 do_test autovacuum-2.4.2 { 259 for {set i 3} {$i<=10} {incr i} { 260 execsql "CREATE TABLE av$i (x)" 261 } 262 file_pages 263 } {15} 264 do_test autovacuum-2.4.3 { 265 execsql { 266 SELECT rootpage FROM sqlite_master ORDER by rootpage 267 } 268 } {3 4 5 6 7 8 9 10} 269 270 # Right now there are 5 free pages in the database. Consume and then free 271 # all 520 pages. Then create 520 tables. This ensures that at least some of the 272 # desired root-pages reside on the second free-list trunk page, and that the 273 # trunk itself is required at some point. 274 do_test autovacuum-2.4.4 { 275 execsql " 276 INSERT INTO av3 VALUES ('[make_str abcde [expr 1020*520 + 500]]'); 277 DELETE FROM av3; 278 " 279 } {} 280 set root_page_list [list] 281 set pending_byte_page [expr ($::sqlite_pending_byte / 1024) + 1] 282 283 # unusable_pages 284 # These are either the pending_byte page or the pointer map pages 285 # 286 unset -nocomplain unusable_page 287 if {[sqlite3 -has-codec]} { 288 array set unusable_page {205 1 408 1} 289 } else { 290 array set unusable_page {207 1 412 1} 291 } 292 set unusable_page($pending_byte_page) 1 293 294 for {set i 3} {$i<=532} {incr i} { 295 if {![info exists unusable_page($i)]} { 296 lappend root_page_list $i 297 } 298 } 299 if {$i >= $pending_byte_page} { 300 lappend root_page_list $i 301 } 302 do_test autovacuum-2.4.5 { 303 for {set i 11} {$i<=530} {incr i} { 304 execsql "CREATE TABLE av$i (x)" 305 } 306 execsql { 307 SELECT rootpage FROM sqlite_master ORDER by rootpage 308 } 309 } $root_page_list 310 311 # Just for fun, delete all those tables and see if the database is 1 page. 312 do_test autovacuum-2.4.6 { 313 execsql COMMIT; 314 file_pages 315 } [expr 561 + (($i >= $pending_byte_page)?1:0)] 316 integrity_check autovacuum-2.4.6 317 do_test autovacuum-2.4.7 { 318 execsql BEGIN 319 for {set i 3} {$i<=530} {incr i} { 320 execsql "DROP TABLE av$i" 321 } 322 execsql COMMIT 323 file_pages 324 } 1 325 326 # Create some tables with indices to drop. 327 do_test autovacuum-2.5.1 { 328 execsql { 329 CREATE TABLE av1(a PRIMARY KEY, b, c); 330 INSERT INTO av1 VALUES('av1 a', 'av1 b', 'av1 c'); 331 332 CREATE TABLE av2(a PRIMARY KEY, b, c); 333 CREATE INDEX av2_i1 ON av2(b); 334 CREATE INDEX av2_i2 ON av2(c); 335 INSERT INTO av2 VALUES('av2 a', 'av2 b', 'av2 c'); 336 337 CREATE TABLE av3(a PRIMARY KEY, b, c); 338 CREATE INDEX av3_i1 ON av3(b); 339 INSERT INTO av3 VALUES('av3 a', 'av3 b', 'av3 c'); 340 341 CREATE TABLE av4(a, b, c); 342 CREATE INDEX av4_i1 ON av4(a); 343 CREATE INDEX av4_i2 ON av4(b); 344 CREATE INDEX av4_i3 ON av4(c); 345 CREATE INDEX av4_i4 ON av4(a, b, c); 346 INSERT INTO av4 VALUES('av4 a', 'av4 b', 'av4 c'); 347 } 348 } {} 349 350 do_test autovacuum-2.5.2 { 351 execsql { 352 SELECT name, rootpage FROM sqlite_master; 353 } 354 } [list av1 3 sqlite_autoindex_av1_1 4 \ 355 av2 5 sqlite_autoindex_av2_1 6 av2_i1 7 av2_i2 8 \ 356 av3 9 sqlite_autoindex_av3_1 10 av3_i1 11 \ 357 av4 12 av4_i1 13 av4_i2 14 av4_i3 15 av4_i4 16 \ 358 ] 359 360 # The following 4 tests are SELECT queries that use the indices created. 361 # If the root-pages in the internal schema are not updated correctly when 362 # a table or indice is moved, these queries will fail. They are repeated 363 # after each table is dropped (i.e. as test cases 2.5.*.[1..4]). 364 do_test autovacuum-2.5.2.1 { 365 execsql { 366 SELECT * FROM av1 WHERE a = 'av1 a'; 367 } 368 } {{av1 a} {av1 b} {av1 c}} 369 do_test autovacuum-2.5.2.2 { 370 execsql { 371 SELECT * FROM av2 WHERE a = 'av2 a' AND b = 'av2 b' AND c = 'av2 c' 372 } 373 } {{av2 a} {av2 b} {av2 c}} 374 do_test autovacuum-2.5.2.3 { 375 execsql { 376 SELECT * FROM av3 WHERE a = 'av3 a' AND b = 'av3 b'; 377 } 378 } {{av3 a} {av3 b} {av3 c}} 379 do_test autovacuum-2.5.2.4 { 380 execsql { 381 SELECT * FROM av4 WHERE a = 'av4 a' AND b = 'av4 b' AND c = 'av4 c'; 382 } 383 } {{av4 a} {av4 b} {av4 c}} 384 385 # Drop table av3. Indices av4_i2, av4_i3 and av4_i4 are moved to fill the two 386 # root pages vacated. The operation proceeds as: 387 # Step 1: Delete av3_i1 (root-page 11). Move root-page of av4_i4 to page 11. 388 # Step 2: Delete av3 (root-page 10). Move root-page of av4_i3 to page 10. 389 # Step 3: Delete sqlite_autoindex_av1_3 (root-page 9). Move av4_i2 to page 9. 390 do_test autovacuum-2.5.3 { 391 execsql { 392 DROP TABLE av3; 393 SELECT name, rootpage FROM sqlite_master; 394 } 395 } [list av1 3 sqlite_autoindex_av1_1 4 \ 396 av2 5 sqlite_autoindex_av2_1 6 av2_i1 7 av2_i2 8 \ 397 av4 12 av4_i1 13 av4_i2 9 av4_i3 10 av4_i4 11 \ 398 ] 399 do_test autovacuum-2.5.3.1 { 400 execsql { 401 SELECT * FROM av1 WHERE a = 'av1 a'; 402 } 403 } {{av1 a} {av1 b} {av1 c}} 404 do_test autovacuum-2.5.3.2 { 405 execsql { 406 SELECT * FROM av2 WHERE a = 'av2 a' AND b = 'av2 b' AND c = 'av2 c' 407 } 408 } {{av2 a} {av2 b} {av2 c}} 409 do_test autovacuum-2.5.3.3 { 410 execsql { 411 SELECT * FROM av4 WHERE a = 'av4 a' AND b = 'av4 b' AND c = 'av4 c'; 412 } 413 } {{av4 a} {av4 b} {av4 c}} 414 415 # Drop table av1: 416 # Step 1: Delete av1 (root page 4). Root-page of av4_i1 fills the gap. 417 # Step 2: Delete sqlite_autoindex_av1_1 (root page 3). Move av4 to the gap. 418 do_test autovacuum-2.5.4 { 419 execsql { 420 DROP TABLE av1; 421 SELECT name, rootpage FROM sqlite_master; 422 } 423 } [list av2 5 sqlite_autoindex_av2_1 6 av2_i1 7 av2_i2 8 \ 424 av4 3 av4_i1 4 av4_i2 9 av4_i3 10 av4_i4 11 \ 425 ] 426 do_test autovacuum-2.5.4.2 { 427 execsql { 428 SELECT * FROM av2 WHERE a = 'av2 a' AND b = 'av2 b' AND c = 'av2 c' 429 } 430 } {{av2 a} {av2 b} {av2 c}} 431 do_test autovacuum-2.5.4.4 { 432 execsql { 433 SELECT * FROM av4 WHERE a = 'av4 a' AND b = 'av4 b' AND c = 'av4 c'; 434 } 435 } {{av4 a} {av4 b} {av4 c}} 436 437 # Drop table av4: 438 # Step 1: Delete av4_i4. 439 # Step 2: Delete av4_i3. 440 # Step 3: Delete av4_i2. 441 # Step 4: Delete av4_i1. av2_i2 replaces it. 442 # Step 5: Delete av4. av2_i1 replaces it. 443 do_test autovacuum-2.5.5 { 444 execsql { 445 DROP TABLE av4; 446 SELECT name, rootpage FROM sqlite_master; 447 } 448 } [list av2 5 sqlite_autoindex_av2_1 6 av2_i1 3 av2_i2 4] 449 do_test autovacuum-2.5.5.2 { 450 execsql { 451 SELECT * FROM av2 WHERE a = 'av2 a' AND b = 'av2 b' AND c = 'av2 c' 452 } 453 } {{av2 a} {av2 b} {av2 c}} 454 455 #-------------------------------------------------------------------------- 456 # Test cases autovacuum-3.* test the operation of the "PRAGMA auto_vacuum" 457 # command. 458 # 459 do_test autovacuum-3.1 { 460 execsql { 461 PRAGMA auto_vacuum; 462 } 463 } {1} 464 do_test autovacuum-3.2 { 465 db close 466 sqlite3 db test.db 467 execsql { 468 PRAGMA auto_vacuum; 469 } 470 } {1} 471 do_test autovacuum-3.3 { 472 execsql { 473 PRAGMA auto_vacuum = 0; 474 PRAGMA auto_vacuum; 475 } 476 } {1} 477 478 do_test autovacuum-3.4 { 479 db close 480 forcedelete test.db 481 sqlite3 db test.db 482 execsql { 483 PRAGMA auto_vacuum; 484 } 485 } $AUTOVACUUM 486 do_test autovacuum-3.5 { 487 execsql { 488 CREATE TABLE av1(x); 489 PRAGMA auto_vacuum; 490 } 491 } $AUTOVACUUM 492 do_test autovacuum-3.6 { 493 execsql { 494 PRAGMA auto_vacuum = 1; 495 PRAGMA auto_vacuum; 496 } 497 } [expr $AUTOVACUUM ? 1 : 0] 498 do_test autovacuum-3.7 { 499 execsql { 500 DROP TABLE av1; 501 } 502 file_pages 503 } [expr $AUTOVACUUM?1:2] 504 505 506 #----------------------------------------------------------------------- 507 # Test that if a statement transaction around a CREATE INDEX statement is 508 # rolled back no corruption occurs. 509 # 510 do_test autovacuum-4.0 { 511 # The last round of tests may have left the db in non-autovacuum mode. 512 # Reset everything just in case. 513 # 514 db close 515 forcedelete test.db test.db-journal 516 sqlite3 db test.db 517 execsql { 518 PRAGMA auto_vacuum = 1; 519 PRAGMA auto_vacuum; 520 } 521 } {1} 522 do_test autovacuum-4.1 { 523 execsql { 524 CREATE TABLE av1(a, b); 525 BEGIN; 526 } 527 for {set i 0} {$i<100} {incr i} { 528 execsql "INSERT INTO av1 VALUES($i, '[string repeat X 200]');" 529 } 530 execsql "INSERT INTO av1 VALUES(99, '[string repeat X 200]');" 531 execsql { 532 SELECT sum(a) FROM av1; 533 } 534 } {5049} 535 do_test autovacuum-4.2 { 536 catchsql { 537 CREATE UNIQUE INDEX av1_i ON av1(a); 538 } 539 } {1 {UNIQUE constraint failed: av1.a}} 540 do_test autovacuum-4.3 { 541 execsql { 542 SELECT sum(a) FROM av1; 543 } 544 } {5049} 545 do_test autovacuum-4.4 { 546 execsql { 547 COMMIT; 548 } 549 } {} 550 551 ifcapable integrityck { 552 553 # Ticket #1727 554 do_test autovacuum-5.1 { 555 db close 556 sqlite3 db :memory: 557 db eval { 558 PRAGMA auto_vacuum=1; 559 CREATE TABLE t1(a); 560 CREATE TABLE t2(a); 561 DROP TABLE t1; 562 PRAGMA integrity_check; 563 } 564 } ok 565 566 } 567 568 # Ticket #1728. 569 # 570 # In autovacuum mode, when tables or indices are deleted, the rootpage 571 # values in the symbol table have to be updated. There was a bug in this 572 # logic so that if an index/table was moved twice, the second move might 573 # not occur. This would leave the internal symbol table in an inconsistent 574 # state causing subsequent statements to fail. 575 # 576 # The problem is difficult to reproduce. The sequence of statements in 577 # the following test are carefully designed make it occur and thus to 578 # verify that this very obscure bug has been resolved. 579 # 580 ifcapable integrityck&&memorydb { 581 582 do_test autovacuum-6.1 { 583 db close 584 sqlite3 db :memory: 585 db eval { 586 PRAGMA auto_vacuum=1; 587 CREATE TABLE t1(a, b); 588 CREATE INDEX i1 ON t1(a); 589 CREATE TABLE t2(a); 590 CREATE INDEX i2 ON t2(a); 591 CREATE TABLE t3(a); 592 CREATE INDEX i3 ON t2(a); 593 CREATE INDEX x ON t1(b); 594 DROP TABLE t3; 595 PRAGMA integrity_check; 596 DROP TABLE t2; 597 PRAGMA integrity_check; 598 DROP TABLE t1; 599 PRAGMA integrity_check; 600 } 601 } {ok ok ok} 602 603 } 604 605 #--------------------------------------------------------------------- 606 # Test cases autovacuum-7.X test the case where a page must be moved 607 # and the destination location collides with at least one other 608 # entry in the page hash-table (internal to the pager.c module. 609 # 610 do_test autovacuum-7.1 { 611 db close 612 forcedelete test.db 613 forcedelete test.db-journal 614 sqlite3 db test.db 615 616 execsql { 617 PRAGMA auto_vacuum=1; 618 CREATE TABLE t1(a, b, PRIMARY KEY(a, b)); 619 INSERT INTO t1 VALUES(randstr(400,400),randstr(400,400)); 620 INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2 621 INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 4 622 INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 8 623 INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 16 624 INSERT INTO t1 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 32 625 } 626 627 expr {[file size test.db] / 1024} 628 } {73} 629 630 do_test autovacuum-7.2 { 631 execsql { 632 CREATE TABLE t2(a, b, PRIMARY KEY(a, b)); 633 INSERT INTO t2 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2 634 CREATE TABLE t3(a, b, PRIMARY KEY(a, b)); 635 INSERT INTO t3 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2 636 CREATE TABLE t4(a, b, PRIMARY KEY(a, b)); 637 INSERT INTO t4 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2 638 CREATE TABLE t5(a, b, PRIMARY KEY(a, b)); 639 INSERT INTO t5 SELECT randstr(400,400), randstr(400,400) FROM t1; -- 2 640 } 641 expr {[file size test.db] / 1024} 642 } {354} 643 644 do_test autovacuum-7.3 { 645 db close 646 sqlite3 db test.db 647 execsql { 648 BEGIN; 649 DELETE FROM t4; 650 COMMIT; 651 SELECT count(*) FROM t1; 652 } 653 expr {[file size test.db] / 1024} 654 } {286} 655 656 #------------------------------------------------------------------------ 657 # Additional tests. 658 # 659 # Try to determine the autovacuum setting for a database that is locked. 660 # 661 do_test autovacuum-8.1 { 662 db close 663 sqlite3 db test.db 664 sqlite3 db2 test.db 665 db eval {PRAGMA auto_vacuum} 666 } {1} 667 if {[permutation] == ""} { 668 do_test autovacuum-8.2 { 669 db eval {BEGIN EXCLUSIVE} 670 catchsql {PRAGMA auto_vacuum} db2 671 } {1 {database is locked}} 672 catch {db2 close} 673 catch {db eval {COMMIT}} 674 } 675 676 do_test autovacuum-9.1 { 677 execsql { 678 DROP TABLE t1; 679 DROP TABLE t2; 680 DROP TABLE t3; 681 DROP TABLE t4; 682 DROP TABLE t5; 683 PRAGMA page_count; 684 } 685 } {1} 686 do_test autovacuum-9.2 { 687 file size test.db 688 } 1024 689 do_test autovacuum-9.3 { 690 execsql { 691 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 692 INSERT INTO t1 VALUES(NULL, randstr(50,50)); 693 } 694 for {set ii 0} {$ii < 10} {incr ii} { 695 db eval { INSERT INTO t1 SELECT NULL, randstr(50,50) FROM t1 } 696 } 697 file size test.db 698 } $::sqlite_pending_byte 699 do_test autovacuum-9.4 { 700 execsql { INSERT INTO t1 SELECT NULL, randstr(50,50) FROM t1 } 701 } {} 702 do_test autovacuum-9.5 { 703 execsql { DELETE FROM t1 WHERE rowid > (SELECT max(a)/2 FROM t1) } 704 file size test.db 705 } $::sqlite_pending_byte 706 707 do_execsql_test autovacuum-10.1 { 708 DROP TABLE t1; 709 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 710 INSERT INTO t1 VALUES(25, randomblob(104)); 711 REPLACE INTO t1 VALUES(25, randomblob(1117)); 712 PRAGMA integrity_check; 713 } {ok} 714 715 finish_test