--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/persistentstorage/sqlite3api/TEST/TclScript/pageropt.test Fri Jan 22 11:06:30 2010 +0200
@@ -0,0 +1,199 @@
+# 2007 April 12
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.
+# The focus of the tests in this file are to verify that the
+# pager optimizations implemented in version 3.3.14 work.
+#
+# $Id: pageropt.test,v 1.5 2008/08/20 14:49:25 danielk1977 Exp $
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+ifcapable {!pager_pragmas||secure_delete} {
+ finish_test
+ return
+}
+
+# Run the SQL statement supplied by the argument and return
+# the results. Prepend four integers to the beginning of the
+# result which are
+#
+# (1) The number of page reads from the database
+# (2) The number of page writes to the database
+# (3) The number of page writes to the journal
+# (4) The number of cache pages freed
+#
+proc pagercount_sql {sql {db db}} {
+ global sqlite3_pager_readdb_count
+ global sqlite3_pager_writedb_count
+ global sqlite3_pager_writej_count
+ global sqlite3_pager_pgfree_count
+ set sqlite3_pager_readdb_count 0
+ set sqlite3_pager_writedb_count 0
+ set sqlite3_pager_writej_count 0
+ set r [$db eval $sql]
+ set cnt [list $sqlite3_pager_readdb_count \
+ $sqlite3_pager_writedb_count \
+ $sqlite3_pager_writej_count ]
+ return [concat $cnt $r]
+}
+
+# Setup the test database
+#
+do_test pageropt-1.1 {
+ sqlite3_soft_heap_limit 0
+ execsql {
+ PRAGMA auto_vacuum = OFF;
+ PRAGMA page_size = 1024;
+ }
+ pagercount_sql {
+ CREATE TABLE t1(x);
+ }
+} {0 2 0}
+do_test pageropt-1.2 {
+ pagercount_sql {
+ INSERT INTO t1 VALUES(randomblob(5000));
+ }
+} {0 6 2}
+
+# Verify that values remain in cache on for subsequent reads.
+# We should not have to go back to disk.
+#
+do_test pageropt-1.3 {
+ pagercount_sql {
+ SELECT length(x) FROM t1
+ }
+} {0 0 0 5000}
+
+# If another thread reads the database, the original cache
+# remains valid.
+#
+sqlite3 db2 test.db
+set blobcontent [db2 one {SELECT hex(x) FROM t1}]
+do_test pageropt-1.4 {
+ pagercount_sql {
+ SELECT hex(x) FROM t1
+ }
+} [list 0 0 0 $blobcontent]
+
+# But if the other thread modifies the database, then the cache
+# must refill.
+#
+do_test pageropt-1.5 {
+ db2 eval {CREATE TABLE t2(y)}
+ pagercount_sql {
+ SELECT hex(x) FROM t1
+ }
+} [list 6 0 0 $blobcontent]
+do_test pageropt-1.6 {
+ pagercount_sql {
+ SELECT hex(x) FROM t1
+ }
+} [list 0 0 0 $blobcontent]
+
+# Verify that the last page of an overflow chain is not read from
+# disk when deleting a row. The one row of t1(x) has four pages
+# of overflow. So deleting that row from t1 should involve reading
+# the sqlite_master table (1 page) the main page of t1 (1 page) and
+# the three overflow pages of t1 for a total of 5 pages.
+#
+# Pages written are page 1 (for the freelist pointer), the root page
+# of the table, and one of the overflow chain pointers because it
+# becomes the trunk of the freelist. Total 3.
+#
+do_test pageropt-2.1 {
+ db close
+ sqlite3 db test.db
+ pagercount_sql {
+ DELETE FROM t1 WHERE rowid=1
+ }
+} {5 3 3}
+
+# When pulling pages off of the freelist, there is no reason
+# to actually bring in the old content.
+#
+do_test pageropt-2.2 {
+ db close
+ sqlite3 db test.db
+ pagercount_sql {
+ INSERT INTO t1 VALUES(randomblob(1500));
+ }
+} {3 4 3}
+do_test pageropt-2.3 {
+ pagercount_sql {
+ INSERT INTO t1 VALUES(randomblob(1500));
+ }
+} {0 4 3}
+
+# Note the new optimization that when pulling the very last page off of the
+# freelist we do not read the content of that page.
+#
+do_test pageropt-2.4 {
+ pagercount_sql {
+ INSERT INTO t1 VALUES(randomblob(1500));
+ }
+} {0 5 3}
+
+# Appending a large quantity of data does not involve writing much
+# to the journal file.
+#
+do_test pageropt-3.1 {
+ pagercount_sql {
+ INSERT INTO t2 SELECT * FROM t1;
+ }
+} {1 7 2}
+
+# Once again, we do not need to read the last page of an overflow chain
+# while deleting.
+#
+do_test pageropt-3.2 {
+ pagercount_sql {
+ DROP TABLE t2;
+ }
+} {0 2 3}
+do_test pageropt-3.3 {
+ pagercount_sql {
+ DELETE FROM t1;
+ }
+} {0 3 3}
+
+# There are now 11 pages on the freelist. Move them all into an
+# overflow chain by inserting a single large record. Starting from
+# a cold cache, only page 1, the root page of table t1, and the trunk
+# of the freelist need to be read (3 pages). And only those three
+# pages need to be journalled. But 13 pages need to be written:
+# page1, the root page of table t1, and an 11 page overflow chain.
+#
+do_test pageropt-4.1 {
+ db close
+ sqlite3 db test.db
+ pagercount_sql {
+ INSERT INTO t1 VALUES(randomblob(11300))
+ }
+} {3 13 3}
+
+# Now we delete that big entries starting from a cold cache and an
+# empty freelist. The first 10 of the 11 pages overflow chain have
+# to be read, together with page1 and the root of the t1 table. 12
+# reads total. But only page1, the t1 root, and the trunk of the
+# freelist need to be journalled and written back.
+#
+do_test pageropt-4.2 {
+ db close
+ sqlite3 db test.db
+ pagercount_sql {
+ DELETE FROM t1
+ }
+} {12 3 3}
+
+sqlite3_soft_heap_limit $soft_limit
+catch {db2 close}
+finish_test