AryaWu/sqlite
0
1# 2011 September 202#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#12# Tests for the sqlite3_db_status() function13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 18set ::testprefix dbstatus219 20do_execsql_test 1.0 {21 PRAGMA page_size = 1024;22 PRAGMA auto_vacuum = 0;23 24 CREATE TABLE t1(a PRIMARY KEY, b);25 INSERT INTO t1 VALUES(1, randomblob(600));26 INSERT INTO t1 VALUES(2, randomblob(600));27 INSERT INTO t1 VALUES(3, randomblob(600));28}29 30proc db_hit_miss {db {reset 0}} {31 set nHit [sqlite3_db_status $db CACHE_HIT $reset]32 set nMiss [sqlite3_db_status $db CACHE_MISS $reset]33 list $nHit $nMiss34}35 36proc db_write {db {reset 0}} {37 sqlite3_db_status $db CACHE_WRITE $reset38}39 40proc db_spill {db {reset 0}} {41 sqlite3_db_status $db CACHE_SPILL $reset42}43 44proc db_temp_spill {db {reset 0}} {45 sqlite3_db_status $db TEMPBUF_SPILL $reset46}47 48do_test 1.1 {49 db close50 sqlite3 db test.db51 execsql { PRAGMA mmap_size = 0 }52 expr {[file size test.db] / 1024}53} 654 55do_test 1.2 {56 execsql { SELECT b FROM t1 WHERE a=2 }57 db_hit_miss db58} {{0 2 0} {0 4 0}}59 60do_test 1.3 { 61 execsql { SELECT b FROM t1 WHERE a=2 }62 db_hit_miss db63} {{0 6 0} {0 4 0}}64 65do_test 1.4 { 66 execsql { SELECT b FROM t1 WHERE a=2 }67 db_hit_miss db68} {{0 10 0} {0 4 0}}69 70do_test 1.5 { 71 db_hit_miss db 172} {{0 10 0} {0 4 0}}73 74do_test 1.6 { 75 db_hit_miss db 076} {{0 0 0} {0 0 0}}77 78do_test 1.7 {79 set fd [db incrblob main t1 b 1]80 fconfigure $fd -translation binary81 set len [string length [read $fd]]82 close $fd83 set len84} 60085do_test 1.8 { sqlite3_db_status db CACHE_HIT 0 } {0 2 0}86do_test 1.9 { sqlite3_db_status db CACHE_MISS 0 } {0 1 0}87 88do_test 2.1 { db_write db } {0 0 0}89do_test 2.2 { 90 execsql { INSERT INTO t1 VALUES(4, randomblob(600)) }91 db_write db92} {0 4 0}93do_test 2.3 { db_write db 1 } {0 4 0}94do_test 2.4 { db_write db 0 } {0 0 0}95do_test 2.5 { db_write db 1 } {0 0 0}96 97if {[wal_is_capable]} {98 do_test 2.6 { 99 execsql { PRAGMA journal_mode = WAL }100 db_write db 1101 } {0 1 0}102}103do_test 2.7 { 104 execsql { INSERT INTO t1 VALUES(5, randomblob(600)) }105 db_write db106} {0 4 0}107do_test 2.8 { db_write db 1 } {0 4 0}108do_test 2.9 { db_write db 0 } {0 0 0}109 110do_test 3.0 { db_spill db 1 } {0 0 0}111do_test 3.1 { db_spill db 0 } {0 0 0}112do_execsql_test 3.2 {113 PRAGMA journal_mode=DELETE;114 PRAGMA cache_size=3;115 UPDATE t1 SET b=randomblob(1000);116} {delete}117do_test 3.3 { db_spill db 0 } {0 8 0}118 119 120if {$::TEMP_STORE<3} {121 do_execsql_test 4.0 {122 PRAGMA temp_store = file;123 PRAGMA cache_size = -1024;124 } {}125 126 do_test 4.1 { db_temp_spill db 0 } {0 0 0}127 128 do_execsql_test 4.2 {129 CREATE TABLE data(a INTEGER, b BLOB);130 131 -- Insert 5-6 MB of data.132 WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<75000 )133 INSERT INTO data SELECT i, hex(randomblob(50)) FROM s;134 }135 136 do_test 4.3 { db_temp_spill db 0 } {0 0 0}137 138 do_test 4.4 {139 execsql { SELECT a, b FROM data ORDER BY a }140 set nTmpSpill [lindex [db_temp_spill db 1] 1]141 expr {($nTmpSpill>7*1000*1000) && ($nTmpSpill<10*1000*1000)?"ok":$nTmpSpill}142 } ok143 144 # The previous test case reset the status value.145 do_test 4.5 { db_temp_spill db 0 } {0 0 0}146 147 do_test 4.6 {148 execsql { CREATE INDEX i1 ON data(a) }149 set nTmpSpill [lindex [db_temp_spill db 1] 1]150 expr {($nTmpSpill>384*1000) && ($nTmpSpill<768*1000)?"ok":$nTmpSpill}151 } ok152 153 # The previous test case reset the status value.154 do_test 4.7 { db_temp_spill db 0 } {0 0 0}155 156 # Same query as in (4.4). Now does not require temp space.157 do_test 4.8 {158 execsql { SELECT a, b FROM data ORDER BY a }159 db_temp_spill db 0160 } {0 0 0}161}162 163 164finish_test165