SQLite

/*
** 2004 April 6
**
** 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.
**
*************************************************************************
** $Id: btree.c,v 1.438 2008/01/31 14:54:44 drh Exp $
**
** This file implements a external (disk-based) database using BTrees.
** See the header comment on "btreeInt.h" for additional information.
** Including a description of file format and an overview of operation.
*/
#include "btreeInt.h"

      /* If *pPgno refers to a pointer-map page, allocate two new pages
      ** at the end of the file instead of one. The first allocated page
      ** becomes a new pointer-map page, the second is used by the caller.
      */
      TRACE(("ALLOCATE: %d from end of file (pointer-map page)\n", *pPgno));
      assert( *pPgno!=PENDING_BYTE_PAGE(pBt) );
      (*pPgno)++;
      if( *pPgno==PENDING_BYTE_PAGE(pBt) ){ (*pPgno)++; }
    }
    if( pBt->nTrunc ){
      pBt->nTrunc = *pPgno;
    }
#endif

    assert( *pPgno!=PENDING_BYTE_PAGE(pBt) );

      return rc;
    }
    pgnoRoot++;

    /* The new root-page may not be allocated on a pointer-map page, or the
    ** PENDING_BYTE page.
    */
    while( pgnoRoot==PTRMAP_PAGENO(pBt, pgnoRoot) ||
        pgnoRoot==PENDING_BYTE_PAGE(pBt) ){
      pgnoRoot++;
    }
    assert( pgnoRoot>=3 );

    /* Allocate a page. The page that currently resides at pgnoRoot will
    ** be moved to the allocated page (unless the allocated page happens

#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file contains tests to verify that the limits defined in
# sqlite source file limits.h are enforced.
#
# $Id: sqllimits1.test,v 1.24 2008/01/31 14:54:44 drh Exp $

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

# Test organization:
#
#     sqllimits1-1.*:  SQLITE_MAX_LENGTH
#     sqllimits1-2.*:  SQLITE_MAX_SQL_LENGTH
#     sqllimits1-3.*:  SQLITE_MAX_PAGE_COUNT (and the max_page_count pragma)
#     sqllimits1-4.*:  SQLITE_MAX_COLUMN
#
#
#     sqllimits1-7.*:   SQLITE_MAX_FUNCTION_ARG  
#     sqllimits1-8.*:   SQLITE_MAX_ATTACHED
#     sqllimits1-9.*:   SQLITE_MAX_VARIABLE_NUMBER
#     sqllimits1-10.*:  SQLITE_MAX_PAGE_SIZE
#     sqllimits1-11.*:  SQLITE_MAX_LIKE_PATTERN_LENGTH
#
# Todo:
#
#     sqllimits1-5.*:   SQLITE_MAX_EXPR_DEPTH            (sqlite todo)
#     sqllimits1-6.*:   SQLITE_MAX_VDBE_OP               (sqlite todo)
#     sqllimits1-12.*:  SQLITE_MAX_PAGE_COUNT            (sqlite todo)
#

unset -nocomplain saved
foreach var [info vars SQLITE_MAX_*] {
  set saved($var) [set $var]
}

set SQLITE_MAX_LENGTH                 100000
set SQLITE_MAX_COLUMN
set SQLITE_MAX_SQL_LENGTH              50000
set SQLITE_MAX_EXPR_DEPTH               1000
set SQLITE_MAX_COMPOUND_SELECT             5
set SQLITE_MAX_VDBE_OP
set SQLITE_MAX_FUNCTION_ARG
set SQLITE_MAX_VARIABLE_NUMBER
set SQLITE_MAX_PAGE_SIZE
set SQLITE_MAX_PAGE_COUNT
set SQLITE_MAX_LIKE_PATTERN_LENGTH      1000

#--------------------------------------------------------------------
# Test cases sqllimits1-1.* test that the SQLITE_MAX_LENGTH limit
# is enforced.
#
do_test sqllimits1-1.1.1 {
  catchsql { SELECT randomblob(2147483647) }
} {1 {string or blob too big}}
do_test sqllimits1-1.1.2 {
  catchsql { SELECT zeroblob(2147483647) }
} {1 {string or blob too big}}

# Large, but allowable, blob-size.
#
set ::LARGESIZE [expr $SQLITE_MAX_LENGTH - 1] 

do_test sqllimits1-1.2 {
  catchsql { SELECT LENGTH(randomblob($::LARGESIZE)) }
} "0 $::LARGESIZE"

do_test sqllimits1-1.3 {
  catchsql { SELECT quote(randomblob($::LARGESIZE)) }
} {1 {string or blob too big}}

do_test sqllimits1-1.4 {
  catchsql { SELECT LENGTH(zeroblob($::LARGESIZE)) }
} "0 $::LARGESIZE"

do_test sqllimits1-1.5 {
  catchsql { SELECT quote(zeroblob($::LARGESIZE)) }
} {1 {string or blob too big}}

do_test sqllimits1-1.6 {
  catchsql { SELECT zeroblob(-1) }
} {0 {{}}}

do_test sqllimits1-1.9 {
  set ::str [string repeat A 65537]
  set ::rep [string repeat B 65537]
  catchsql { SELECT replace($::str, 'A', $::rep) }
} {1 {string or blob too big}}

do_test sqllimits1-1.10 {
  set ::str [string repeat %J 2100]
  catchsql { SELECT strftime($::str, '2003-10-31') }
} {1 {string or blob too big}}

do_test sqllimits1-1.11 {
  set ::str1 [string repeat A [expr {$SQLITE_MAX_LENGTH - 10}]]
  set ::str2 [string repeat B [expr {$SQLITE_MAX_LENGTH - 10}]]
  catchsql { SELECT $::str1 || $::str2 }
} {1 {string or blob too big}}

do_test sqllimits1-1.12 {
  set ::str1 [string repeat ' [expr {$SQLITE_MAX_LENGTH - 10}]]
  catchsql { SELECT quote($::str1) }
} {1 {string or blob too big}}

do_test sqllimits1-1.13 {
  set ::str1 [string repeat ' [expr {$SQLITE_MAX_LENGTH - 10}]]
  catchsql { SELECT hex($::str1) }
} {1 {string or blob too big}}

do_test sqllimits1-1.14.1 {
  set ::STMT [sqlite3_prepare $::DB "SELECT ?" -1 TAIL]
  sqlite3_bind_zeroblob $::STMT 1 [expr {$SQLITE_MAX_LENGTH + 1}]
} {}
do_test sqllimits1-1.14.2 {
  sqlite3_step $::STMT 
} {SQLITE_ERROR}
do_test sqllimits1-1.14.3 {
  sqlite3_finalize $::STMT 
} {SQLITE_TOOBIG}

do_test sqllimits1-1.15 {
  execsql {
    CREATE TABLE t4(x);
    INSERT INTO t4 VALUES(1);
    INSERT INTO t4 VALUES(2);
    INSERT INTO t4 SELECT 2+x FROM t4;
  }
  catchsql {
    SELECT group_concat(hex(randomblob(20000))) FROM t4;
  }
} {1 {string or blob too big}}
db eval {DROP TABLE t4}

set ::SQLITE_MAX_SQL_LENGTH 0
set strvalue [string repeat A $::SQLITE_MAX_LENGTH]
do_test sqllimits1-1.16 {
  catchsql "SELECT '$strvalue'"
} [list 0 $strvalue]
do_test sqllimits1-1.17 {
  catchsql "SELECT 'A$strvalue'"
} [list 1 {string or blob too big}]
set blobvalue [string repeat 41 $::SQLITE_MAX_LENGTH]
do_test sqllimits1-1.18 {
  catchsql "SELECT x'$blobvalue'"
} [list 0 $strvalue]
do_test sqllimits1-1.19 {
  catchsql "SELECT '41$blobvalue'"
} [list 1 {string or blob too big}]
unset blobvalue

ifcapable datetime {
  set strvalue [string repeat D [expr {$SQLITE_MAX_LENGTH-12}]]
  do_test sqllimits1-1.20 {
    catchsql {SELECT strftime('%Y ' || $::strvalue, '2008-01-02')}
  } [list 0 [list "2008 $strvalue"]]
  do_test sqllimits1-1.21 {
    catchsql {SELECT strftime('%Y-%m-%d ' || $::strvalue, '2008-01-02')}
  } {1 {string or blob too big}}
}
unset strvalue

#--------------------------------------------------------------------
# Test cases sqllimits1-2.* test that the SQLITE_MAX_SQL_LENGTH limit
# is enforced.
#
do_test sqllimits1-2.1 {
  set ::SQLITE_MAX_SQL_LENGTH 50000
  set sql "SELECT 1 WHERE 1==1"
  set tail " /* A comment to take up space in order to make the string\
                longer without increasing the expression depth */\
                AND   1  ==  1"
  set N [expr {($::SQLITE_MAX_SQL_LENGTH / [string length $tail])+1}]
  append sql [string repeat $tail $N]
  catchsql $sql
} {1 {String or BLOB exceeded size limit}}
do_test sqllimits1-2.2 {
  set ::SQLITE_MAX_SQL_LENGTH 0
  catchsql $sql
} {0 1}
do_test sqllimits1-2.3 {
  set ::SQLITE_MAX_SQL_LENGTH 50000
  set sql "SELECT 1 WHERE 1==1"
  set tail " /* A comment to take up space in order to make the string\
                longer without increasing the expression depth */\
                AND   1  ==  1"
  set N [expr {($::SQLITE_MAX_SQL_LENGTH / [string length $tail])+1}]
  append sql [string repeat $tail $N]
  set nbytes [string length $sql]
  append sql { AND 0}
  set rc [catch {sqlite3_prepare $DB $sql $nbytes TAIL} STMT]
  lappend rc $STMT
} {1 {(18) statement too long}}
do_test sqllimits1-2.4 {
  sqlite3_errmsg $DB
} {statement too long}


#--------------------------------------------------------------------
# Test cases sqllimits1-3.* test that the limit set using the
# max_page_count pragma.
#
do_test sqllimits1-3.1 {
  execsql {
    PRAGMA max_page_count = 1000;
  }
} {1000}
do_test sqllimits1-3.2 {
  execsql { CREATE TABLE trig (a INTEGER, b INTEGER); }

  # Set up a tree of triggers to fire when a row is inserted
  # into table "trig".
  #
  # INSERT -> insert_b -> update_b -> insert_a -> update_a      (chain 1)
  #                    -> update_a -> insert_a -> update_b      (chain 2)

    SELECT COUNT(*) FROM trig;
  }
} {0 7}

# Now check the response of the library to opening a file larger than
# the current max_page_count value. The response is to change the
# internal max_page_count value to match the actual size of the file.
if {[db eval {PRAGMA auto_vacuum}]} {
   set fsize 1700
} else {
   set fsize 1691
}
do_test sqllimits1-3.7.1 {
  execsql {
    PRAGMA max_page_count = 1000000;
    CREATE TABLE abc(a, b, c);
    INSERT INTO abc VALUES(1, 2, 3);
    INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
    INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
    INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
    INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
    INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
    INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
    INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
    INSERT INTO abc SELECT a||b||c, b||c||a, c||a||b FROM abc;
    INSERT INTO abc SELECT a, b, c FROM abc;
    INSERT INTO abc SELECT b, a, c FROM abc;
    INSERT INTO abc SELECT c, b, a FROM abc;
  }
  expr [file size test.db] / 1024
} $fsize
do_test sqllimits1-3.7.2 {
  db close
  sqlite3 db test.db
  execsql {
    PRAGMA max_page_count = 1000;
  }
  execsql {
    SELECT count(*) FROM sqlite_master;
  }
} {6}
do_test sqllimits1-3.7.3 {
  execsql {
    PRAGMA max_page_count;
  }
} $fsize
do_test sqllimits1-3.7.4 {
  execsql {
    DROP TABLE abc;
  }
} {}

#--------------------------------------------------------------------
# Test cases sqllimits1-4.* test the SQLITE_MAX_COLUMN limit.
#
do_test sqllimits1-4.1 {
  # Columns in a table.
  set cols [list]
  for {set i 0} {$i <= $SQLITE_MAX_COLUMN} {incr i} {
    lappend cols "c$i"
  }
  catchsql "CREATE TABLE t([join $cols ,])" 
} {1 {too many columns on t}}

do_test sqllimits1-4.2 {
  # Columns in the result-set of a SELECT.
  set cols [list]
  for {set i 0} {$i <= $SQLITE_MAX_COLUMN} {incr i} {
    lappend cols "sql AS sql$i"
  }
  catchsql "SELECT [join $cols ,] FROM sqlite_master"
} {1 {too many columns in result set}}

do_test sqllimits1-4.3 {
  # Columns in the result-set of a sub-SELECT.
  set cols [list]
  for {set i 0} {$i <= $SQLITE_MAX_COLUMN} {incr i} {
    lappend cols "sql AS sql$i"
  }
  catchsql "SELECT sql4 FROM (SELECT [join $cols ,] FROM sqlite_master)"
} {1 {too many columns in result set}}

do_test sqllimits1-4.4 {
  # Columns in an index.
  set cols [list]
  for {set i 0} {$i <= $SQLITE_MAX_COLUMN} {incr i} {
    lappend cols c
  }
  set sql1 "CREATE TABLE t1(c);"
  set sql2 "CREATE INDEX i1 ON t1([join $cols ,]);"
  catchsql "$sql1 ; $sql2"
} {1 {too many columns in index}}

do_test sqllimits1-4.5 {
  # Columns in a GROUP BY clause.
  catchsql "SELECT * FROM t1 GROUP BY [join $cols ,]"
} {1 {too many terms in GROUP BY clause}}

do_test sqllimits1-4.6 {
  # Columns in an ORDER BY clause.
  catchsql "SELECT * FROM t1 ORDER BY [join $cols ,]"
} {1 {too many terms in ORDER BY clause}}

do_test sqllimits1-4.7 {
  # Assignments in an UPDATE statement.
  set cols [list]
  for {set i 0} {$i <= $SQLITE_MAX_COLUMN} {incr i} {
    lappend cols "c = 1"
  }
  catchsql "UPDATE t1 SET [join $cols ,];"
} {1 {too many columns in set list}}

do_test sqllimits1-4.8 {
  # Columns in a view definition:
  set cols [list]
  for {set i 0} {$i <= $SQLITE_MAX_COLUMN} {incr i} {
    lappend cols "c$i"
  }
  catchsql "CREATE VIEW v1 AS SELECT [join $cols ,] FROM t1;"
} {1 {too many columns in result set}}

do_test sqllimits1-4.9 {
  # Columns in a view definition (testing * expansion):
  set cols [list]
  for {set i 0} {$i < $SQLITE_MAX_COLUMN} {incr i} {
    lappend cols "c$i"
  }
  catchsql "CREATE TABLE t2([join $cols ,])"
  catchsql "CREATE VIEW v1 AS SELECT *, c1 AS o FROM t2;"
} {1 {too many columns in result set}}

#--------------------------------------------------------------------
# These tests - sqllimits1-5.* - test that the SQLITE_MAX_EXPR_DEPTH
# limit is enforced. The limit refers to the number of terms in 
# the expression.
#
if {$::SQLITE_MAX_EXPR_DEPTH != 1000} {
  puts -nonewline stderr "WARNING: Compile with -DSQLITE_MAX_EXPR_DEPTH to run "
  puts stderr "tests sqllimits1-5.X"
} else {
  do_test sqllimits1-5.1 {
    set max $::SQLITE_MAX_EXPR_DEPTH
    set expr "(1 [string repeat {AND 1 } $max])"
    catchsql [subst {
      SELECT $expr
    }]
  } "1 {Expression tree is too large (maximum depth $::SQLITE_MAX_EXPR_DEPTH)}"
  
  # Attempting to beat the expression depth limit using nested SELECT
  # queries causes a parser stack overflow. 
  do_test sqllimits1-5.2 {
    set max $::SQLITE_MAX_EXPR_DEPTH
    set expr "SELECT 1"
    for {set i 0} {$i <= $max} {incr i} {
      set expr "SELECT ($expr)"
    }
    catchsql [subst { $expr }]
  } "1 {parser stack overflow}"
  
  
  do_test sqllimits1-5.3 {
    execsql {
      PRAGMA max_page_count = 1000000;  -- 1 GB
      CREATE TABLE v0(a);
      INSERT INTO v0 VALUES(1);
    }
    db transaction {
      for {set i 1} {$i < 200} {incr i} {
        set expr "(a [string repeat {AND 1 } 50]) AS a"
        execsql [subst {
          CREATE VIEW v${i} AS SELECT $expr FROM v[expr {$i-1}]
        }]
      }
    }
  } {}
  
  do_test sqllimits1-5.4 {
    catchsql {
      SELECT a FROM v199
    }
  } "1 {Expression tree is too large (maximum depth $::SQLITE_MAX_EXPR_DEPTH)}"
}

#--------------------------------------------------------------------
# Test cases sqllimits1-6.* test that the SQLITE_MAX_VDBE_OP
# limit works as expected. The limit refers to the number of opcodes
# in a single VDBE program.
#
# TODO

#--------------------------------------------------------------------
# Test the SQLITE_MAX_FUNCTION_ARG limit works. Test case names
# match the pattern "sqllimits1-7.*".
#
do_test sqllimits1-7.1 {
  set max $::SQLITE_MAX_FUNCTION_ARG
  set vals [list]
  for {set i 0} {$i < $SQLITE_MAX_FUNCTION_ARG} {incr i} {
    lappend vals $i
  }
  catchsql "SELECT max([join $vals ,])"
} "0 [expr {$::SQLITE_MAX_FUNCTION_ARG - 1}]"
do_test sqllimits1-7.2 {
  set max $::SQLITE_MAX_FUNCTION_ARG
  set vals [list]
  for {set i 0} {$i <= $SQLITE_MAX_FUNCTION_ARG} {incr i} {
    lappend vals $i
  }
  catchsql "SELECT max([join $vals ,])"
} {1 {too many arguments on function max}}

# Test that it is SQLite, and not the implementation of the
# user function that is throwing the error.
proc myfunc {args} {error "I don't like to be called!"}
do_test sqllimits1-7.2 {
  db function myfunc myfunc
  set max $::SQLITE_MAX_FUNCTION_ARG
  set vals [list]
  for {set i 0} {$i <= $SQLITE_MAX_FUNCTION_ARG} {incr i} {
    lappend vals $i
  }
  catchsql "SELECT myfunc([join $vals ,])"
} {1 {too many arguments on function myfunc}}

#--------------------------------------------------------------------
# Test cases sqllimits1-8.*: Test the SQLITE_MAX_ATTACHED limit.
#
ifcapable attach {
  do_test sqllimits1-8.1 {
    set max $::SQLITE_MAX_ATTACHED
    for {set i 0} {$i < ($max)} {incr i} {
      file delete -force test${i}.db test${i}.db-journal
    }
    for {set i 0} {$i < ($max)} {incr i} {
      execsql "ATTACH 'test${i}.db' AS aux${i}"
    }
    catchsql "ATTACH 'test${i}.db' AS aux${i}"
  } "1 {too many attached databases - max $::SQLITE_MAX_ATTACHED}"
  do_test sqllimits1-8.2 {
    set max $::SQLITE_MAX_ATTACHED
    for {set i 0} {$i < ($max)} {incr i} {
      execsql "DETACH aux${i}"
    }
  } {}
}

#--------------------------------------------------------------------
# Test cases sqllimits1-9.*: Check that the SQLITE_MAX_VARIABLE_NUMBER 
# limit works.
#
do_test sqllimits1-9.1 {
  set max $::SQLITE_MAX_VARIABLE_NUMBER
  catchsql "SELECT ?[expr {$max+1}] FROM t1"
} "1 {variable number must be between ?1 and ?$::SQLITE_MAX_VARIABLE_NUMBER}"
do_test sqllimits1-9.2 {
  set max $::SQLITE_MAX_VARIABLE_NUMBER
  set vals [list]
  for {set i 0} {$i < ($max+3)} {incr i} {
    lappend vals ?
  }
  catchsql "SELECT [join $vals ,] FROM t1"
} "1 {too many SQL variables}"


#--------------------------------------------------------------------
# sqllimits1-10.*:  Test the SQLITE_MAX_PAGE_SIZE define is enforced. 
# This is probably tested elsewhere too (pagerX.test). Attempts
# to raise the page size above this limit are silently ignored.
#
do_test sqllimits1-10.1 {
  db close
  file delete -force test.db test.db-journal
  sqlite3 db test.db
  set max $::SQLITE_MAX_PAGE_SIZE
  catchsql "PRAGMA page_size = [expr {$max*2}]"
} {0 {}}
do_test sqllimits1-10.2 {
  catchsql "PRAGMA page_size"
} {0 1024}
do_test sqllimits1-10.3 {
  set max $::SQLITE_MAX_PAGE_SIZE
  catchsql "PRAGMA page_size = $max"
} {0 {}}
do_test sqllimits1-10.4 {
  execsql "pragma page_size"
} $::SQLITE_MAX_PAGE_SIZE
do_test sqllimits1-10.5 {
  set max $::SQLITE_MAX_PAGE_SIZE
  execsql "pragma page_size = [expr {$max - 5}]"
  execsql "pragma page_size"
} $::SQLITE_MAX_PAGE_SIZE

# Opening a database where the page size is too large should generate
# an error.
#
do_test sqllimits1-10.5 {
  db close
  file delete -force test.db
  set ::SQLITE_MAX_PAGE_SIZE 32768
  sqlite3 db test.db
  db eval {PRAGMA page_size=32768}
  db eval {CREATE TABLE t1(x);}
  db eval {PRAGMA page_size}
} {32768}
do_test sqllimits1-10.6 {
  db close
  set ::SQLITE_MAX_PAGE_SIZE 8192
  sqlite3 db test.db
  catchsql {SELECT name FROM sqlite_master}
} {1 {file is encrypted or is not a database}}
db close
file delete -force test.db
sqlite3 db test.db

#--------------------------------------------------------------------
# Test cases sqllimits1-11.* verify that the 
# SQLITE_MAX_LIKE_PATTERN_LENGTH limit is enforced. This limit only
# applies to the built-in LIKE operator, supplying an external 
# implementation by overriding the like() scalar function bypasses
# this limitation.
#
# These tests check that the limit is not incorrectly applied to
# the left-hand-side of the LIKE operator (the string being tested
# against the pattern).
#
do_test sqllimits1-11.1 {
  set max $::SQLITE_MAX_LIKE_PATTERN_LENGTH
  set ::pattern [string repeat "A%" [expr $max/2]]
  set ::string  [string repeat "A" [expr {$max*2}]]
  execsql {
    SELECT $::string LIKE $::pattern;
  }
} {1}
do_test sqllimits1-11.2 {
  set max $::SQLITE_MAX_LIKE_PATTERN_LENGTH
  set ::pattern [string repeat "A%" [expr {($max/2) + 1}]]
  set ::string  [string repeat "A" [expr {$max*2}]]
  catchsql {
    SELECT $::string LIKE $::pattern;
  }
} {1 {LIKE or GLOB pattern too complex}}

#--------------------------------------------------------------------
# This test case doesn't really belong with the other limits tests.
# It is in this file because it is taxing to run, like the limits tests.
#
do_test sqllimits1-12.1 {
  set ::N [expr int(([expr pow(2,32)]/50) + 1)]
  expr (($::N*50) & 0xffffffff)<55
} {1}
do_test sqllimits1-12.2 {
  set ::format "[string repeat A 60][string repeat "%J" $::N]"
  catchsql {
    SELECT strftime($::format, 1);
  }
} {1 {string or blob too big}}


foreach {key value} [array get saved] {
  catch {set $key $value}
}
finish_test