gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/pageropt.test (about) 1 # 2007 April 12 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. 12 # The focus of the tests in this file are to verify that the 13 # pager optimizations implemented in version 3.3.14 work. 14 # 15 # $Id: pageropt.test,v 1.5 2008/08/20 14:49:25 danielk1977 Exp $ 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 do_not_use_codec 20 21 ifcapable {!pager_pragmas||secure_delete||direct_read} { 22 finish_test 23 return 24 } 25 26 # A non-zero reserved_bytes value changes the number of pages in the 27 # database file, which messes up the results in this test. 28 if {[nonzero_reserved_bytes]} {finish_test; return;} 29 30 # Run the SQL statement supplied by the argument and return 31 # the results. Prepend four integers to the beginning of the 32 # result which are 33 # 34 # (1) The number of page reads from the database 35 # (2) The number of page writes to the database 36 # (3) The number of page writes to the journal 37 # (4) The number of cache pages freed 38 # 39 proc pagercount_sql {sql {db db}} { 40 global sqlite3_pager_readdb_count 41 global sqlite3_pager_writedb_count 42 global sqlite3_pager_writej_count 43 global sqlite3_pager_pgfree_count 44 set sqlite3_pager_readdb_count 0 45 set sqlite3_pager_writedb_count 0 46 set sqlite3_pager_writej_count 0 47 set r [$db eval $sql] 48 set cnt [list $sqlite3_pager_readdb_count \ 49 $sqlite3_pager_writedb_count \ 50 $sqlite3_pager_writej_count ] 51 return [concat $cnt $r] 52 } 53 54 # Setup the test database 55 # 56 do_test pageropt-1.1 { 57 sqlite3_soft_heap_limit 0 58 execsql { 59 PRAGMA auto_vacuum = OFF; 60 PRAGMA page_size = 1024; 61 } 62 pagercount_sql { 63 CREATE TABLE t1(x); 64 } 65 } {0 2 0} 66 do_test pageropt-1.2 { 67 pagercount_sql { 68 INSERT INTO t1 VALUES(randomblob(5000)); 69 } 70 } {0 6 2} 71 72 # Verify that values remain in cache on for subsequent reads. 73 # We should not have to go back to disk. 74 # 75 do_test pageropt-1.3 { 76 pagercount_sql { 77 SELECT length(x) FROM t1 78 } 79 } {0 0 0 5000} 80 81 # If another thread reads the database, the original cache 82 # remains valid. 83 # 84 sqlite3 db2 test.db 85 set blobcontent [db2 one {SELECT hex(x) FROM t1}] 86 do_test pageropt-1.4 { 87 pagercount_sql { 88 SELECT hex(x) FROM t1 89 } 90 } [list 0 0 0 $blobcontent] 91 92 # But if the other thread modifies the database, then the cache 93 # must refill. 94 # 95 ifcapable mmap { 96 set x [expr {[permutation]=="mmap" ? 1 : 6}] 97 } else { 98 set x 6 99 } 100 do_test pageropt-1.5 { 101 db2 eval {CREATE TABLE t2(y)} 102 pagercount_sql { 103 SELECT hex(x) FROM t1 104 } 105 } [list $x 0 0 $blobcontent] 106 do_test pageropt-1.6 { 107 pagercount_sql { 108 SELECT hex(x) FROM t1 109 } 110 } [list 0 0 0 $blobcontent] 111 112 # Verify that the last page of an overflow chain is not read from 113 # disk when deleting a row. The one row of t1(x) has four pages 114 # of overflow. So deleting that row from t1 should involve reading 115 # the sqlite_master table (1 page) the main page of t1 (1 page) and 116 # the three overflow pages of t1 for a total of 5 pages. 117 # 118 # Pages written are page 1 (for the freelist pointer), the root page 119 # of the table, and one of the overflow chain pointers because it 120 # becomes the trunk of the freelist. Total 3. 121 # 122 do_test pageropt-2.1 { 123 db close 124 sqlite3 db test.db 125 pagercount_sql { 126 DELETE FROM t1 WHERE rowid=1 127 } 128 } {5 3 3} 129 130 # When pulling pages off of the freelist, there is no reason 131 # to actually bring in the old content. 132 # 133 do_test pageropt-2.2 { 134 db close 135 sqlite3 db test.db 136 pagercount_sql { 137 INSERT INTO t1 VALUES(randomblob(1500)); 138 } 139 } {3 4 3} 140 do_test pageropt-2.3 { 141 pagercount_sql { 142 INSERT INTO t1 VALUES(randomblob(1500)); 143 } 144 } {0 4 3} 145 146 # Note the new optimization that when pulling the very last page off of the 147 # freelist we do not read the content of that page. 148 # 149 do_test pageropt-2.4 { 150 pagercount_sql { 151 INSERT INTO t1 VALUES(randomblob(1500)); 152 } 153 } {0 5 3} 154 155 # Appending a large quantity of data does not involve writing much 156 # to the journal file. 157 # 158 do_test pageropt-3.1 { 159 pagercount_sql { 160 INSERT INTO t2 SELECT * FROM t1; 161 } 162 } {1 7 2} 163 164 # Once again, we do not need to read the last page of an overflow chain 165 # while deleting. 166 # 167 do_test pageropt-3.2 { 168 pagercount_sql { 169 DROP TABLE t2; 170 } 171 } {0 2 3} 172 do_test pageropt-3.3 { 173 pagercount_sql { 174 DELETE FROM t1; 175 } 176 } {0 3 3} 177 178 # There are now 11 pages on the freelist. Move them all into an 179 # overflow chain by inserting a single large record. Starting from 180 # a cold cache, only page 1, the root page of table t1, and the trunk 181 # of the freelist need to be read (3 pages). And only those three 182 # pages need to be journalled. But 13 pages need to be written: 183 # page1, the root page of table t1, and an 11 page overflow chain. 184 # 185 do_test pageropt-4.1 { 186 db close 187 sqlite3 db test.db 188 pagercount_sql { 189 INSERT INTO t1 VALUES(randomblob(11300)) 190 } 191 } {3 13 3} 192 193 # Now we delete that big entries starting from a cold cache and an 194 # empty freelist. The first 10 of the 11 pages overflow chain have 195 # to be read, together with page1 and the root of the t1 table. 12 196 # reads total. But only page1, the t1 root, and the trunk of the 197 # freelist need to be journalled and written back. 198 # 199 do_test pageropt-4.2 { 200 db close 201 sqlite3 db test.db 202 pagercount_sql { 203 DELETE FROM t1 204 } 205 } {12 3 3} 206 207 sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit) 208 catch {db2 close} 209 finish_test