# 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