AryaWu/sqlite

1# 2007 April 122#3# The author disclaims copyright to this source code.  In place of4# 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 the13# 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 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19do_not_use_codec20 21ifcapable {!pager_pragmas||secure_delete||direct_read} {22  finish_test23  return24}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.28if {[nonzero_reserved_bytes]} {finish_test; return;}29 30# Run the SQL statement supplied by the argument and return31# the results.  Prepend four integers to the beginning of the32# result which are33#34#     (1)  The number of page reads from the database35#     (2)  The number of page writes to the database36#     (3)  The number of page writes to the journal37#     (4)  The number of cache pages freed38#39proc pagercount_sql {sql {db db}} {40  global sqlite3_pager_readdb_count41  global sqlite3_pager_writedb_count42  global sqlite3_pager_writej_count43  global sqlite3_pager_pgfree_count44  set sqlite3_pager_readdb_count 045  set sqlite3_pager_writedb_count 046  set sqlite3_pager_writej_count 047  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 database55#56do_test pageropt-1.1 {57  sqlite3_soft_heap_limit 058  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}66do_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#75do_test pageropt-1.3 {76  pagercount_sql {77    SELECT length(x) FROM t178  }79} {0 0 0 5000}80 81# If another thread reads the database, the original cache82# remains valid.83#84sqlite3 db2 test.db85set blobcontent [db2 one {SELECT hex(x) FROM t1}]86do_test pageropt-1.4 {87  pagercount_sql {88    SELECT hex(x) FROM t189  }90} [list 0 0 0 $blobcontent]91 92# But if the other thread modifies the database, then the cache93# must refill.94#95ifcapable mmap {96  set x [expr {[permutation]=="mmap" ? 1 : 6}]97} else {98  set x 699}100do_test pageropt-1.5 {101  db2 eval {CREATE TABLE t2(y)}102  pagercount_sql {103    SELECT hex(x) FROM t1104  }105} [list $x 0 0 $blobcontent]106do_test pageropt-1.6 {107  pagercount_sql {108    SELECT hex(x) FROM t1109  }110} [list 0 0 0 $blobcontent]111 112# Verify that the last page of an overflow chain is not read from113# disk when deleting a row.  The one row of t1(x) has four pages114# of overflow.  So deleting that row from t1 should involve reading115# the sqlite_master table (1 page) the main page of t1 (1 page) and116# 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 page119# of the table, and one of the overflow chain pointers because it120# becomes the trunk of the freelist.  Total 3.121#122do_test pageropt-2.1 {123  db close124  sqlite3 db test.db125  pagercount_sql {126    DELETE FROM t1 WHERE rowid=1127  }128} {5 3 3}129 130# When pulling pages off of the freelist, there is no reason131# to actually bring in the old content.132#133do_test pageropt-2.2 {134  db close135  sqlite3 db test.db136  pagercount_sql {137    INSERT INTO t1 VALUES(randomblob(1500));138  }139} {3 4 3}140do_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 the147# freelist we do not read the content of that page.148#149do_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 much156# to the journal file.157#158do_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 chain165# while deleting.166#167do_test pageropt-3.2 {168  pagercount_sql {169    DROP TABLE t2;170  }171} {0 2 3}172do_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 an179# overflow chain by inserting a single large record.  Starting from180# a cold cache, only page 1, the root page of table t1, and the trunk181# of the freelist need to be read (3 pages).  And only those three182# 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#185do_test pageropt-4.1 {186  db close187  sqlite3 db test.db188  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 an194# empty freelist.  The first 10 of the 11 pages overflow chain have195# to be read, together with page1 and the root of the t1 table.  12196# reads total.  But only page1, the t1 root, and the trunk of the197# freelist need to be journalled and written back.198#199do_test pageropt-4.2 {200  db close201  sqlite3 db test.db202  pagercount_sql {203    DELETE FROM t1204  }205} {12 3 3}206 207sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit)208catch {db2 close}209finish_test210