# 2007 May 17

#

# 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 this script is testing that the overflow-page related

# enhancements added after version 3.3.17 speed things up.

#

# $Id: speed3.test,v 1.5 2007/10/09 08:29:33 danielk1977 Exp $

#

#---------------------------------------------------------------------

# Test plan:

#

# If auto-vacuum is enabled for the database, the following cases

# should show performance improvement with respect to 3.3.17.

#

#   + When deleting rows that span overflow pages. This is faster

#     because the overflow pages no longer need to be read before

#     they can be moved to the free list (test cases speed3-1.X). 

#

#   + When reading a column value stored on an overflow page that

#     is not the first overflow page for the row. The improvement

#     in this case is because the overflow pages between the tree

#     page and the overflow page containing the value do not have

#     to be read (test cases speed3-2.X).

#

set testdir [file dirname $argv0]

source $testdir/tester.tcl

ifcapable !tclvar||!attach {

  finish_test

  return

}

speed_trial_init speed1

# Set a uniform random seed

expr srand(0)

set ::NROW 1000

# The number_name procedure below converts its argment (an integer)

# into a string which is the English-language name for that number.

#

# Example:

#

#     puts [number_name 123]   ->  "one hundred twenty three"

#

set ones {zero one two three four five six seven eight nine

          ten eleven twelve thirteen fourteen fifteen sixteen seventeen

          eighteen nineteen}

set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety}

proc number_name {n} {

  if {$n>=1000} {

    set txt "[number_name [expr {$n/1000}]] thousand"

    set n [expr {$n%1000}]

  } else {

    set txt {}

  }

  if {$n>=100} {

    append txt " [lindex $::ones [expr {$n/100}]] hundred"

    set n [expr {$n%100}]

  }

  if {$n>=20} {

    append txt " [lindex $::tens [expr {$n/10}]]"

    set n [expr {$n%10}]

  }

  if {$n>0} {

    append txt " [lindex $::ones $n]"

  }

  set txt [string trim $txt]

  if {$txt==""} {set txt zero}

  return $txt

}

proc populate_t1 {db} {

  $db transaction {

    for {set ii 0} {$ii < $::NROW} {incr ii} {

      set N [number_name $ii]

      set repeats [expr {(10000/[string length $N])+1}]

      set text [string range [string repeat $N $repeats] 0 10000]

      $db eval {INSERT INTO main.t1 VALUES($ii, $text, $ii)}

    }

    $db eval {INSERT INTO aux.t1 SELECT * FROM main.t1}

  }

}

proc io_log {db} {

  db_enter db

  array set stats1 [btree_pager_stats [btree_from_db db]]

  array set stats2 [btree_pager_stats [btree_from_db db 2]]

  db_leave db

# puts "1: [array get stats1]"

# puts "2: [array get stats2]"

  puts "Incrvacuum: Read $stats1(read), wrote $stats1(write)"

  puts "Normal    : Read $stats2(read), wrote $stats2(write)"

}

proc overflow_report {db} {

  set bt [btree_from_db db]

  set csr [btree_cursor $bt 3 0]

  for {btree_first $csr} {![btree_eof $csr]} {btree_next $csr} {

    puts "[btree_ovfl_info $bt $csr]"

  }

  btree_close_cursor $csr

}

proc reset_db {} {

  db close

  sqlite3 db test.db

  db eval { 

    PRAGMA main.cache_size = 200000;

    PRAGMA main.auto_vacuum = 'incremental';

    ATTACH 'test2.db' AS 'aux'; 

    PRAGMA aux.auto_vacuum = 'none';

  }

}

file delete -force test2.db test2.db-journal

reset_db

# Set up a database in auto-vacuum mode and create a database schema.

#

do_test speed3-0.1 {

  execsql {

    CREATE TABLE main.t1(a INTEGER, b TEXT, c INTEGER);

  }

  execsql {

    SELECT name FROM sqlite_master ORDER BY 1;

  }

} {t1}

do_test speed3-0.2 {

  execsql {

    CREATE TABLE aux.t1(a INTEGER, b TEXT, c INTEGER);

  }

  execsql {

    SELECT name FROM aux.sqlite_master ORDER BY 1;

  }

} {t1}

do_test speed3-0.3 {

  populate_t1 db

  execsql {

    SELECT count(*) FROM main.t1;

    SELECT count(*) FROM aux.t1;

  }

} "$::NROW $::NROW"

do_test speed3-0.4 {

  execsql {

    PRAGMA main.auto_vacuum;

    PRAGMA aux.auto_vacuum;

  }

} {2 0}

# Delete all content in a table, one row at a time.

#

#io_log db

#overflow_report db

reset_db

speed_trial speed3-1.incrvacuum $::NROW row {DELETE FROM main.t1 WHERE 1}

speed_trial speed3-1.normal     $::NROW row {DELETE FROM aux.t1 WHERE 1}

io_log db

# Select the "C" column (located at the far end of the overflow 

# chain) from each table row.

#

#db eval {PRAGMA incremental_vacuum(500000)}

populate_t1 db

#overflow_report db

reset_db

speed_trial speed3-2.incrvacuum $::NROW row {SELECT c FROM main.t1}

speed_trial speed3-2.normal     $::NROW row {SELECT c FROM aux.t1}

io_log db

finish_test