CDXLPlay added with permission from Fredik Wikstrom.

[AROS-Contrib.git] / sqlite3 / test / btree.test

# 2001 September 15
#
# 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 btree database backend
#
# $Id: btree.test,v 1.35 2005/01/11 10:25:07 danielk1977 Exp $


set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable default_autovacuum {
  finish_test
  return
}

# Basic functionality.  Open and close a database.
#
do_test btree-1.1 {
  file delete -force test1.bt
  file delete -force test1.bt-journal
  set rc [catch {btree_open test1.bt 2000 0} ::b1]
} {0}

# The second element of the list returned by btree_pager_stats is the
# number of pages currently checked out.  We'll be checking this value
# frequently during this test script, to make sure the btree library
# is properly releasing the pages it checks out, and thus avoiding
# page leaks.
#
do_test btree-1.1.1 {
  lindex [btree_pager_stats $::b1] 1
} {0}
do_test btree-1.2 {
  set rc [catch {btree_open test1.bt 2000 0} ::b2]
} {0}
do_test btree-1.3 {
  set rc [catch {btree_close $::b2} msg]
  lappend rc $msg
} {0 {}}

# Do an insert and verify that the database file grows in size.
#
do_test btree-1.4 {
  set rc [catch {btree_begin_transaction $::b1} msg]
  lappend rc $msg
} {0 {}}
do_test btree-1.4.1 {
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-1.5 {
  set rc [catch {btree_cursor $::b1 1 1} ::c1]
  if {$rc} {lappend rc $::c1}
  set rc
} {0}
do_test btree-1.6 {
  set rc [catch {btree_insert $::c1 100 1.00} msg]
  lappend rc $msg
} {0 {}}
do_test btree-1.7 {
  btree_move_to $::c1 100
  btree_key $::c1
} {100}
do_test btree-1.8 {
  btree_data $::c1
} {1.00}
do_test btree-1.9 {
  set rc [catch {btree_close_cursor $::c1} msg]
  lappend rc $msg
} {0 {}}
do_test btree-1.10 {
  set rc [catch {btree_commit $::b1} msg]
  lappend rc $msg
} {0 {}}
do_test btree-1.11 {
  file size test1.bt
} {1024}
do_test btree-1.12 {
  lindex [btree_pager_stats $::b1] 1
} {0}

# Reopen the database and attempt to read the record that we wrote.
#
do_test btree-2.1 {
  set rc [catch {btree_cursor $::b1 1 1} ::c1]
  if {$rc} {lappend rc $::c1}
  set rc
} {0}
do_test btree-2.2 {
  btree_move_to $::c1 99
} {1}
do_test btree-2.3 {
  btree_move_to $::c1 101
} {-1}
do_test btree-2.4 {
  btree_move_to $::c1 100
} {0}
do_test btree-2.5 {
  btree_key $::c1
} {100}
do_test btree-2.6 {
  btree_data $::c1
} {1.00}
do_test btree-2.7 {
  lindex [btree_pager_stats $::b1] 1
} {1}

# Do some additional inserts
#
do_test btree-3.1 {
  btree_begin_transaction $::b1
  btree_insert $::c1 200 2.00
  btree_move_to $::c1 200
  btree_key $::c1
} {200}
do_test btree-3.1.1 {
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-3.2 {
  btree_insert $::c1 300 3.00
  btree_move_to $::c1 300
  btree_key $::c1
} {300}
do_test btree-3.4 {
  btree_insert $::c1 400 4.00
  btree_move_to $::c1 400
  btree_key $::c1
} {400}
do_test btree-3.5 {
  btree_insert $::c1 500 5.00
  btree_move_to $::c1 500
  btree_key $::c1
} {500}
do_test btree-3.6 {
  btree_insert $::c1 600 6.00
  btree_move_to $::c1 600
  btree_key $::c1
} {600}
#btree_page_dump $::b1 2
do_test btree-3.7 {
  set rc [btree_move_to $::c1 0]
  expr {$rc>0}
} {1}
do_test btree-3.8 {
  btree_key $::c1
} {100}
do_test btree-3.9 {
  btree_data $::c1
} {1.00}
do_test btree-3.10 {
  btree_next $::c1
  btree_key $::c1
} {200}
do_test btree-3.11 {
  btree_data $::c1
} {2.00}
do_test btree-3.12 {
  btree_next $::c1
  btree_key $::c1
} {300}
do_test btree-3.13 {
  btree_data $::c1
} {3.00}
do_test btree-3.14 {
  btree_next $::c1
  btree_key $::c1
} {400}
do_test btree-3.15 {
  btree_data $::c1
} {4.00}
do_test btree-3.16 {
  btree_next $::c1
  btree_key $::c1
} {500}
do_test btree-3.17 {
  btree_data $::c1
} {5.00}
do_test btree-3.18 {
  btree_next $::c1
  btree_key $::c1
} {600}
do_test btree-3.19 {
  btree_data $::c1
} {6.00}
do_test btree-3.20.1 {
  btree_next $::c1
  btree_key $::c1
} {0}
do_test btree-3.20.2 {
  btree_eof $::c1
} {1}
# This test case used to test that one couldn't request data from an 
# invalid cursor. That is now an assert()ed condition.
#
# do_test btree-3.21 {
#   set rc [catch {btree_data $::c1} res]
#   lappend rc $res
# } {1 SQLITE_INTERNAL}

# Commit the changes, reopen and reread the data
#
do_test btree-3.22 {
  set rc [catch {btree_close_cursor $::c1} msg]
  lappend rc $msg
} {0 {}}
do_test btree-3.22.1 {
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-3.23 {
  set rc [catch {btree_commit $::b1} msg]
  lappend rc $msg
} {0 {}}
do_test btree-3.23.1 {
  lindex [btree_pager_stats $::b1] 1
} {0}
do_test btree-3.24 {
  file size test1.bt
} {1024}
do_test btree-3.25 {
  set rc [catch {btree_cursor $::b1 1 1} ::c1]
  if {$rc} {lappend rc $::c1}
  set rc
} {0}
do_test btree-3.25.1 {
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-3.26 {
  set rc [btree_move_to $::c1 0]
  expr {$rc>0}
} {1}
do_test btree-3.27 {
  btree_key $::c1
} {100}
do_test btree-3.28 {
  btree_data $::c1
} {1.00}
do_test btree-3.29 {
  btree_next $::c1
  btree_key $::c1
} {200}
do_test btree-3.30 {
  btree_data $::c1
} {2.00}
do_test btree-3.31 {
  btree_next $::c1
  btree_key $::c1
} {300}
do_test btree-3.32 {
  btree_data $::c1
} {3.00}
do_test btree-3.33 {
  btree_next $::c1
  btree_key $::c1
} {400}
do_test btree-3.34 {
  btree_data $::c1
} {4.00}
do_test btree-3.35 {
  btree_next $::c1
  btree_key $::c1
} {500}
do_test btree-3.36 {
  btree_data $::c1
} {5.00}
do_test btree-3.37 {
  btree_next $::c1
  btree_key $::c1
} {600}
do_test btree-3.38 {
  btree_data $::c1
} {6.00}
do_test btree-3.39 {
  btree_next $::c1
  btree_key $::c1
} {0}
# This test case used to test that requesting data from an invalid cursor
# returned SQLITE_INTERNAL. That is now an assert()ed condition.
#
# do_test btree-3.40 {
#   set rc [catch {btree_data $::c1} res]
#   lappend rc $res
# } {1 SQLITE_INTERNAL}
do_test btree-3.41 {
  lindex [btree_pager_stats $::b1] 1
} {1}


# Now try a delete
#
do_test btree-4.1 {
  btree_begin_transaction $::b1
  btree_move_to $::c1 100
  btree_key $::c1
} {100}
do_test btree-4.1.1 {
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-4.2 {
  btree_delete $::c1
} {}
do_test btree-4.3 {
  btree_move_to $::c1 100
  btree_key $::c1
} {200}
do_test btree-4.4 {
  btree_next $::c1
  btree_key $::c1
} {300}
do_test btree-4.5 {
  btree_next $::c1
  btree_key $::c1
} {400}
do_test btree-4.4 {
  btree_move_to $::c1 0
  set r {}
  while 1 {
    set key [btree_key $::c1]
    if {[btree_eof $::c1]} break
    lappend r $key
    lappend r [btree_data $::c1]
    btree_next $::c1
  }
  set r   
} {200 2.00 300 3.00 400 4.00 500 5.00 600 6.00}

# Commit and make sure the delete is still there.
#
do_test btree-4.5 {
  btree_commit $::b1
  btree_move_to $::c1 0
  set r {}
  while 1 {
    set key [btree_key $::c1]
    if {[btree_eof $::c1]} break
    lappend r $key
    lappend r [btree_data $::c1]
    btree_next $::c1
  }
  set r   
} {200 2.00 300 3.00 400 4.00 500 5.00 600 6.00}

# Completely close the database and reopen it.  Then check
# the data again.
#
do_test btree-4.6 {
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-4.7 {
  btree_close_cursor $::c1
  lindex [btree_pager_stats $::b1] 1
} {0}
do_test btree-4.8 {
  btree_close $::b1
  set ::b1 [btree_open test1.bt 2000 0]
  set ::c1 [btree_cursor $::b1 1 1]
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-4.9 {
  set r {}
  btree_first $::c1
  while 1 {
    set key [btree_key $::c1]
    if {[btree_eof $::c1]} break
    lappend r $key
    lappend r [btree_data $::c1]
    btree_next $::c1
  }
  set r   
} {200 2.00 300 3.00 400 4.00 500 5.00 600 6.00}

# Try to read and write meta data
#
do_test btree-5.1 {
  btree_get_meta $::b1
} {0 0 0 0 0 0 0 0 0 0}
do_test btree-5.2 {
  set rc [catch {
    btree_update_meta $::b1 0 1 2 3 4 5 6 7 8 9
  } msg]
  lappend rc $msg
} {1 SQLITE_ERROR}
do_test btree-5.3 {
  btree_begin_transaction $::b1
  set rc [catch {
    btree_update_meta $::b1 0 1 2 3 0 5 6 7 8 9
  } msg]
  lappend rc $msg
} {0 {}}
do_test btree-5.4 {
  btree_get_meta $::b1
} {0 1 2 3 0 5 6 7 8 9}
do_test btree-5.5 {
  btree_close_cursor $::c1
  btree_rollback $::b1
  btree_get_meta $::b1
} {0 0 0 0 0 0 0 0 0 0}
do_test btree-5.6 {
  btree_begin_transaction $::b1
  btree_update_meta $::b1 0 10 20 30 0 50 60 70 80 90
  btree_commit $::b1
  btree_get_meta $::b1
} {0 10 20 30 0 50 60 70 80 90}

proc select_all {cursor} {
  set r {}
  btree_first $cursor
  while {![btree_eof $cursor]} {
    set key [btree_key $cursor]
    lappend r $key
    lappend r [btree_data $cursor]
    btree_next $cursor
  }
  return $r
}
proc select_keys {cursor} {
  set r {}
  btree_first $cursor
  while {![btree_eof $cursor]} {
    set key [btree_key $cursor]
    lappend r $key
    btree_next $cursor
  }
  return $r
}

# Try to create a new table in the database file
#
do_test btree-6.1 {
  set rc [catch {btree_create_table $::b1 0} msg]
  lappend rc $msg
} {1 SQLITE_ERROR}
do_test btree-6.2 {
  btree_begin_transaction $::b1
  set ::t2 [btree_create_table $::b1 0]
} {2}
do_test btree-6.2.1 {
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-6.2.2 {
  set ::c2 [btree_cursor $::b1 $::t2 1]
  lindex [btree_pager_stats $::b1] 1
} {2}
do_test btree-6.2.3 {
  btree_insert $::c2 ten 10
  btree_move_to $::c2 ten
  btree_key $::c2
} {ten}
do_test btree-6.3 {
  btree_commit $::b1
  set ::c1 [btree_cursor $::b1 1 1]
  lindex [btree_pager_stats $::b1] 1
} {2}
do_test btree-6.3.1 {
  select_all $::c1
} {200 2.00 300 3.00 400 4.00 500 5.00 600 6.00}
#btree_page_dump $::b1 3
do_test btree-6.4 {
  select_all $::c2
} {ten 10}

# Drop the new table, then create it again anew.
#
do_test btree-6.5 {
  btree_begin_transaction $::b1
} {}
do_test btree-6.6 {
  btree_close_cursor $::c2
} {}
do_test btree-6.6.1 {
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-6.7 {
  btree_close_cursor $::c1
  btree_drop_table $::b1 $::t2
} {}
do_test btree-6.7.1 {
  lindex [btree_get_meta $::b1] 0
} {1}
do_test btree-6.8 {
  set ::t2 [btree_create_table $::b1 0]
} {2}
do_test btree-6.8.1 {
  lindex [btree_get_meta $::b1] 0
} {0}
do_test btree-6.9 {
  set ::c2 [btree_cursor $::b1 $::t2 1]
  lindex [btree_pager_stats $::b1] 1
} {2}

# This test case used to test that requesting the key from an invalid cursor
# returned an empty string.  But that is now an assert()ed condition.
#
# do_test btree-6.9.1 {
#   btree_move_to $::c2 {}
#   btree_key $::c2
# } {}

# If we drop table 1 it just clears the table.  Table 1 always exists.
#
do_test btree-6.10 {
  btree_close_cursor $::c2
  btree_drop_table $::b1 1
  set ::c2 [btree_cursor $::b1 $::t2 1]
  set ::c1 [btree_cursor $::b1 1 1]
  btree_first $::c1
  btree_eof $::c1
} {1}
do_test btree-6.11 {
  btree_commit $::b1
  select_all $::c1
} {}
do_test btree-6.12 {
  select_all $::c2
} {}
do_test btree-6.13 {
  btree_close_cursor $::c2
  lindex [btree_pager_stats $::b1] 1
} {1}

# Check to see that pages defragment properly.  To do this test we will
# 
#   1.  Fill the first page of table 1 with data.
#   2.  Delete every other entry of table 1.
#   3.  Insert a single entry that requires more contiguous
#       space than is available.
#
do_test btree-7.1 {
  btree_begin_transaction $::b1
} {}
catch {unset key}
catch {unset data}
if 0 {
do_test btree-7.2 {
  # Each record will be 10 bytes in size.
  #   + 100 bytes of database header
  #   + 8 bytes of table header
  #   + 91*10=910 bytes of cells
  # Totals 1018 bytes.  6 bytes left over
  # Keys are 1000 through 1090.
  for {set i 1000} {$i<1091} {incr i} {
    set key $i
    set data [format %5d $i]
    btree_insert $::c1 $key $data
  }
  lrange [btree_cursor_info $::c1] 4 5
} {6 0}
#btree_tree_dump $::b1 1
do_test btree-7.3 {
  for {set i 1001} {$i<1091} {incr i 2} {
    btree_move_to $::c1 $i
    btree_delete $::c1
  }
  # Freed 45 blocks.  Total freespace is 456
  # Keys remaining are even numbers between 1000 and 1090, inclusive
  lrange [btree_cursor_info $::c1] 4 5
} {456 45}
#btree_tree_dump $::b1 1
do_test btree-7.4 {
  # The largest free block is 8 bytes long.  But there is also a
  # huge hole between the cell pointer array and the cellcontent.
  # But if we insert a large enough record, it should force a defrag.
  set data 123456789_
  append data $data
  append data $data
  append data $data
  append data $data
  append data $data
  btree_insert $::c1 2000 $data
  btree_move_to $::c1 2000
  btree_key $::c1
} {2000}
do_test btree-7.5 {
  lrange [btree_cursor_info $::c1] 4 5
} {343 0}
#btree_tree_dump $::b1 1

# Delete an entry to make a hole of a known size, then immediately recreate
# that entry.  This tests the path into allocateSpace where the hole exactly
# matches the size of the desired space.
#
# Keys are even numbers between 1000 and 1090 and one record of 2000.
# There are 47 keys total.
#
do_test btree-7.6 {
  btree_move_to $::c1 1006
  btree_delete $::c1
  btree_move_to $::c1 1010
  btree_delete $::c1
} {}
do_test btree-7.7 {
  lrange [btree_cursor_info $::c1] 4 5
} {363 2}   ;# Create two new holes of 10 bytes each
#btree_page_dump $::b1 1
do_test btree-7.8 {
  btree_insert $::c1 1006 { 1006}
  lrange [btree_cursor_info $::c1] 4 5
} {353 1}   ;# Filled in the first hole
btree_page_dump $::b1 1

# Make sure the freeSpace() routine properly coaleses adjacent memory blocks
#
do_test btree-7.9 {
  btree_move_to $::c1 1012
  btree_delete $::c1
  lrange [btree_cursor_info $::c1] 4 5
} {363 2}  ;# Coalesce with the hole before
btree_page_dump $::b1 1
exit
do_test btree-7.10 {
  btree_move_to $::c1 1008
  btree_delete $::c1
  lrange [btree_cursor_info $::c1] 4 5
} {468 2}  ;# Coalesce with whole after
do_test btree-7.11 {
  btree_move_to $::c1 1030
  btree_delete $::c1
  lrange [btree_cursor_info $::c1] 4 5
} {478 3}   ;# Make a new hole
do_test btree-7.13 {
  btree_move_to $::c1 1034
  btree_delete $::c1
  lrange [btree_cursor_info $::c1] 4 5
} {488 4}   ;# Make another hole
do_test btree-7.14 {
  btree_move_to $::c1 1032
  btree_delete $::c1
  lrange [btree_cursor_info $::c1] 4 5
} {498 3}   ;# The freed space should coalesce on both ends
#btree_page_dump $::b1 2
do_test btree-7.15 {
  lindex [btree_pager_stats $::b1] 1
} {1}
} ;# endif

# Check to see that data on overflow pages work correctly.
#
do_test btree-8.1 {
  set data "*** This is a very long key "
  while {[string length $data]<1234} {append data $data}
  set ::data $data
  btree_insert $::c1 2020 $data
} {}
#btree_page_dump $::b1 1
do_test btree-8.1.1 {
  lindex [btree_pager_stats $::b1] 1
} {1}
#btree_pager_ref_dump $::b1
do_test btree-8.2 {
  btree_move_to $::c1 2020
  string length [btree_data $::c1]
} [string length $::data]
do_test btree-8.3 {
  btree_data $::c1
} $::data
do_test btree-8.4 {
  btree_delete $::c1
} {}
do_test btree-8.4.1 {
  lindex [btree_get_meta $::b1] 0
} [expr {int(([string length $::data]-238+1019)/1020)}]
do_test btree-8.4.2 {
  btree_integrity_check $::b1 1 2
} {}
do_test btree-8.5 {
  set data "*** This is an even longer key "
  while {[string length $data]<2000} {append data $data}
  append data END
  set ::data $data
  btree_insert $::c1 2030 $data
} {}
do_test btree-8.6 {
  btree_move_to $::c1 2030
  string length [btree_data $::c1]
} [string length $::data]
do_test btree-8.7 {
  btree_data $::c1
} $::data
do_test btree-8.8 {
  btree_commit $::b1
  btree_data $::c1
} $::data
do_test btree-8.9.1 {
  btree_close_cursor $::c1
  btree_close $::b1
  set ::b1 [btree_open test1.bt 2000 0]
  set ::c1 [btree_cursor $::b1 1 1]
  btree_move_to $::c1 2030
  btree_data $::c1
} $::data
do_test btree-8.9.2 {
  btree_integrity_check $::b1 1 2
} {}
do_test btree-8.10 {
  btree_begin_transaction $::b1
  btree_delete $::c1
} {}
do_test btree-8.11 {
  lindex [btree_get_meta $::b1] 0
} {4}

# Now check out keys on overflow pages.
#
do_test btree-8.12.1 {
  set ::keyprefix "This is a long prefix to a key "
  while {[string length $::keyprefix]<256} {append ::keyprefix $::keyprefix}
  btree_close_cursor $::c1
  btree_clear_table $::b1 2
  lindex [btree_get_meta $::b1] 0
} {4}
do_test btree-8.12.2 {
  btree_integrity_check $::b1 1 2
} {}
do_test btree-8.12.3 {
  set ::c1 [btree_cursor $::b1 2 1]
  btree_insert $::c1 ${::keyprefix}1 1
  btree_first $::c1
  btree_data $::c1
} {1}
do_test btree-8.13 {
  btree_key $::c1
} ${keyprefix}1
do_test btree-8.14 {
  btree_insert $::c1 ${::keyprefix}2 2
  btree_insert $::c1 ${::keyprefix}3 3
  btree_last $::c1
  btree_key $::c1
} ${keyprefix}3
do_test btree-8.15 {
  btree_move_to $::c1 ${::keyprefix}2
  btree_data $::c1
} {2}
do_test btree-8.16 {
  btree_move_to $::c1 ${::keyprefix}1
  btree_data $::c1
} {1}
do_test btree-8.17 {
  btree_move_to $::c1 ${::keyprefix}3
  btree_data $::c1
} {3}
do_test btree-8.18 {
  lindex [btree_get_meta $::b1] 0
} {1}
do_test btree-8.19 {
  btree_move_to $::c1 ${::keyprefix}2
  btree_key $::c1
} ${::keyprefix}2
#btree_page_dump $::b1 2
do_test btree-8.20 {
  btree_delete $::c1
  btree_next $::c1
  btree_key $::c1
} ${::keyprefix}3
#btree_page_dump $::b1 2
do_test btree-8.21 {
  lindex [btree_get_meta $::b1] 0
} {2}
do_test btree-8.22 {
  lindex [btree_pager_stats $::b1] 1
} {2}
do_test btree-8.23.1 {
  btree_close_cursor $::c1
  btree_drop_table $::b1 2
  btree_integrity_check $::b1 1
} {}
do_test btree-8.23.2 {
  btree_create_table $::b1 0
} {2}
do_test btree-8.23.3 {
  set ::c1 [btree_cursor $::b1 2 1]
  lindex [btree_get_meta $::b1] 0
} {4}
do_test btree-8.24 {
  lindex [btree_pager_stats $::b1] 1
} {2}
#btree_pager_ref_dump $::b1
do_test btree-8.25 {
  btree_integrity_check $::b1 1 2
} {}

# Check page splitting logic
#
do_test btree-9.1 {
  for {set i 1} {$i<=19} {incr i} {
    set key [format %03d $i]
    set data "*** $key *** $key *** $key *** $key ***"
    btree_insert $::c1 $key $data
  }
} {}
#btree_tree_dump $::b1 2
#btree_pager_ref_dump $::b1
#set pager_refinfo_enable 1
do_test btree-9.2 {
  btree_insert $::c1 020 {*** 020 *** 020 *** 020 *** 020 ***}
  select_keys $::c1
} {001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020}
#btree_page_dump $::b1 2
#btree_pager_ref_dump $::b1
#set pager_refinfo_enable 0

# The previous "select_keys" command left the cursor pointing at the root
# page.  So there should only be two pages checked out.  2 (the root) and
# page 1.
do_test btree-9.2.1 {
  lindex [btree_pager_stats $::b1] 1
} {2}
for {set i 1} {$i<=20} {incr i} {
  do_test btree-9.3.$i.1 [subst {
    btree_move_to $::c1 [format %03d $i]
    btree_key $::c1
  }] [format %03d $i]
  do_test btree-9.3.$i.2 [subst {
    btree_move_to $::c1 [format %03d $i]
    string range \[btree_data $::c1\] 0 10
  }] "*** [format %03d $i] ***"
}
do_test btree-9.4.1 {
  lindex [btree_pager_stats $::b1] 1
} {2}

# Check the page joining logic.
#
#btree_page_dump $::b1 2
#btree_pager_ref_dump $::b1
do_test btree-9.4.2 {
  btree_move_to $::c1 005
  btree_delete $::c1
} {}
#btree_page_dump $::b1 2
for {set i 1} {$i<=19} {incr i} {
  if {$i==5} continue
  do_test btree-9.5.$i.1 [subst {
    btree_move_to $::c1 [format %03d $i]
    btree_key $::c1
  }] [format %03d $i]
  do_test btree-9.5.$i.2 [subst {
    btree_move_to $::c1 [format %03d $i]
    string range \[btree_data $::c1\] 0 10
  }] "*** [format %03d $i] ***"
}
#btree_pager_ref_dump $::b1
do_test btree-9.6 {
  btree_close_cursor $::c1
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-9.7 {
  btree_integrity_check $::b1 1 2
} {}
do_test btree-9.8 {
  btree_rollback $::b1
  lindex [btree_pager_stats $::b1] 1
} {0}
do_test btree-9.9 {
  btree_integrity_check $::b1 1 2
} {}
do_test btree-9.10 {
  btree_close $::b1
  set ::b1 [btree_open test1.bt 2000 0]
  btree_integrity_check $::b1 1 2
} {}

# Create a tree of depth two.  That is, there is a single divider entry
# on the root pages and two leaf pages.  Then delete the divider entry
# see what happens.
#
do_test btree-10.1 {
  btree_begin_transaction $::b1
  btree_clear_table $::b1 2
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-10.2 {
  set ::c1 [btree_cursor $::b1 2 1]
  lindex [btree_pager_stats $::b1] 1
} {2}
do_test btree-10.3 {
  for {set i 1} {$i<=30} {incr i} {
    set key [format %03d $i]
    set data "*** $key *** $key *** $key *** $key ***"
    btree_insert $::c1 $key $data
  }
  select_keys $::c1
} {001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030}
#btree_tree_dump $::b1 2
do_test btree-10.4 {
  # The divider entry is 012.  This is found by uncommenting the 
  # btree_tree_dump call above and looking at the tree.  If the page size
  # changes, this test will no longer work.
  btree_move_to $::c1 012
  btree_delete $::c1
  select_keys $::c1
} {001 002 003 004 005 006 007 008 009 010 011 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030}
#btree_pager_ref_dump $::b1
#btree_tree_dump $::b1 2
for {set i 1} {$i<=30} {incr i} {
  # Check the number of unreference pages.  This should be 3 in most cases,
  # but 2 when the cursor is pointing to the divider entry which is now 013.
  do_test btree-10.5.$i {
    btree_move_to $::c1 [format %03d $i]
    lindex [btree_pager_stats $::b1] 1
  } [expr {$i==13?2:3}]
  #btree_pager_ref_dump $::b1
  #btree_tree_dump $::b1 2
}

# Create a tree with lots more pages
#
catch {unset ::data}
catch {unset ::key}
for {set i 31} {$i<=2000} {incr i} {
  do_test btree-11.1.$i.1 {
    set key [format %03d $i]
    set ::data "*** $key *** $key *** $key *** $key ***"
    btree_insert $::c1 $key $data
    btree_move_to $::c1 $key
    btree_key $::c1
  } [format %03d $i]
  do_test btree-11.1.$i.2 {
    btree_data $::c1
  } $::data
  set ::key [format %03d [expr {$i/2}]]
  if {$::key=="012"} {set ::key 013}
  do_test btree-11.1.$i.3 {
    btree_move_to $::c1 $::key
    btree_key $::c1
  } $::key
}
catch {unset ::data}
catch {unset ::key}

# Make sure our reference count is still correct.
#
do_test btree-11.2 {
  btree_close_cursor $::c1
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-11.3 {
  set ::c1 [btree_cursor $::b1 2 1]
  lindex [btree_pager_stats $::b1] 1
} {2}

# Delete the dividers on the root page
#
#btree_page_dump $::b1 2
do_test btree-11.4 {
  btree_move_to $::c1 1667
  btree_delete $::c1
  btree_move_to $::c1 1667
  set k [btree_key $::c1]
  if {$k==1666} {
    set k [btree_next $::c1]
  }
  btree_key $::c1
} {1668}
#btree_page_dump $::b1 2

# Change the data on an intermediate node such that the node becomes overfull
# and has to split.  We happen to know that intermediate nodes exist on
# 337, 401 and 465 by the btree_page_dumps above
#
catch {unset ::data}
set ::data {This is going to be a very long data segment}
append ::data $::data
append ::data $::data
do_test btree-12.1 {
  btree_insert $::c1 337 $::data
  btree_move_to $::c1 337
  btree_data $::c1
} $::data
do_test btree-12.2 {
  btree_insert $::c1 401 $::data
  btree_move_to $::c1 401
  btree_data $::c1
} $::data
do_test btree-12.3 {
  btree_insert $::c1 465 $::data
  btree_move_to $::c1 465
  btree_data $::c1
} $::data
do_test btree-12.4 {
  btree_move_to $::c1 337
  btree_key $::c1
} {337}
do_test btree-12.5 {
  btree_data $::c1
} $::data
do_test btree-12.6 {
  btree_next $::c1
  btree_key $::c1
} {338}
do_test btree-12.7 {
  btree_move_to $::c1 464
  btree_key $::c1
} {464}
do_test btree-12.8 {
  btree_next $::c1
  btree_data $::c1
} $::data
do_test btree-12.9 {
  btree_next $::c1
  btree_key $::c1
} {466}
do_test btree-12.10 {
  btree_move_to $::c1 400
  btree_key $::c1
} {400}
do_test btree-12.11 {
  btree_next $::c1
  btree_data $::c1
} $::data
do_test btree-12.12 {
  btree_next $::c1
  btree_key $::c1
} {402}
# btree_commit $::b1
# btree_tree_dump $::b1 1
do_test btree-13.1 {
  btree_integrity_check $::b1 1 2
} {}

# To Do:
#
#   1.  Do some deletes from the 3-layer tree
#   2.  Commit and reopen the database
#   3.  Read every 15th entry and make sure it works
#   4.  Implement btree_sanity and put it throughout this script
#

do_test btree-15.98 {
  btree_close_cursor $::c1
  lindex [btree_pager_stats $::b1] 1
} {1}
do_test btree-15.99 {
  btree_rollback $::b1
  lindex [btree_pager_stats $::b1] 1
} {0}
btree_pager_ref_dump $::b1

# Miscellaneous tests.
#
# btree-16.1 - Check that a statement cannot be started if a transaction 
#              is not active.
# btree-16.2 - Check that it is an error to request more payload from a 
#              btree entry than the entry contains.
do_test btree-16.1 {
  catch {btree_begin_statement $::b1} msg
  set msg
} SQLITE_ERROR

do_test btree-16.2 {
  btree_begin_transaction $::b1
  set ::c1 [btree_cursor $::b1 2 1]
  btree_insert $::c1 1 helloworld
  btree_close_cursor $::c1
  btree_commit $::b1
} {}
do_test btree-16.3 {
  set ::c1 [btree_cursor $::b1 2 1]
  btree_first $::c1
} 0
do_test btree-16.4 {
  catch {btree_data $::c1 [expr [btree_payload_size $::c1] + 10]} msg
  set msg
} SQLITE_ERROR

if {$tcl_platform(platform)=="unix"} {
  do_test btree-16.5 {
    btree_close $::b1
    set ::origperm [file attributes test1.bt -permissions]
    file attributes test1.bt -permissions o-w,g-w,a-w
    set ::b1 [btree_open test1.bt 2000 0]
    catch {btree_cursor $::b1 2 1} msg
    file attributes test1.bt -permissions $::origperm
    btree_close $::b1
    set ::b1 [btree_open test1.bt 2000 0]
    set msg
  } {SQLITE_READONLY}
}

do_test btree-16.6 {
  set ::c1 [btree_cursor $::b1 2 1]
  set ::c2 [btree_cursor $::b1 2 1]
  btree_begin_transaction $::b1
  for {set i 0} {$i<100} {incr i} {
    btree_insert $::c1 $i [string repeat helloworld 10]
  }
  btree_last $::c2
  btree_insert $::c1 100 [string repeat helloworld 10]
} {}

do_test btree-16.7 {
  btree_close_cursor $::c1
  btree_close_cursor $::c2
  btree_commit $::b1
  set ::c1 [btree_cursor $::b1 2 1]
  catch {btree_insert $::c1 101 helloworld} msg
  set msg
} {SQLITE_ERROR}
do_test btree-16.8 {
  btree_first $::c1
  catch {btree_delete $::c1} msg
  set msg
} {SQLITE_ERROR}
do_test btree-16.9 {
  btree_close_cursor $::c1
  btree_begin_transaction $::b1
  set ::c1 [btree_cursor $::b1 2 0]
  catch {btree_insert $::c1 101 helloworld} msg
  set msg
} {SQLITE_PERM}
do_test btree-16.10 {
  catch {btree_delete $::c1} msg
  set msg
} {SQLITE_PERM}
do_test btree-16.11 {
  btree_close_cursor $::c1
  set ::c2 [btree_cursor $::b1 2 1]
  set ::c1 [btree_cursor $::b1 2 0]
  catch {btree_insert $::c2 101 helloworld} msg
  set msg
} {SQLITE_LOCKED}
do_test btree-16.12 {
  btree_first $::c2
  catch {btree_delete $::c2} msg
  set msg
} {SQLITE_LOCKED}
do_test btree-16.13 {
  catch {btree_clear_table $::b1 2} msg
  set msg
} {SQLITE_LOCKED}
do_test btree-16.14 {
  btree_close_cursor $::c1
  btree_close_cursor $::c2
  btree_commit $::b1
  catch {btree_clear_table $::b1 2} msg
  set msg
} {SQLITE_ERROR}
do_test btree-16.15 {
  catch {btree_drop_table $::b1 2} msg
  set msg
} {SQLITE_ERROR}
do_test btree-16.16 {
  btree_begin_transaction $::b1
  set ::c1 [btree_cursor $::b1 2 0]
  catch {btree_drop_table $::b1 2} msg
  set msg
} {SQLITE_LOCKED}

do_test btree-99.1 {
  btree_close $::b1
} {}
catch {unset data}
catch {unset key}

finish_test