sqllimits1.test

最新的sqlite3.6.2源代码

  sqlite3_errmsg db
} {statement too long}

#--------------------------------------------------------------------
# Test cases sqllimits1-7.* test that the limit set using the
# max_page_count pragma.
#
do_test sqllimits1-7.1 {
  execsql {
    PRAGMA max_page_count = 1000;
  }
} {1000}
do_test sqllimits1-7.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)
  #        -> insert_a -> update_b -> insert_b -> update_a      (chain 3)
  #                    -> update_a -> insert_b -> update_b      (chain 4)
  #
  # Table starts with N rows.
  #
  #   Chain 1: insert_b (update N rows)
  #              -> update_b (insert 1 rows)
  #                -> insert_a (update N rows)
  #                  -> update_a (insert 1 rows)
  #
  # chains 2, 3 and 4 are similar. Each inserts more than N^2 rows, where
  # N is the number of rows at the conclusion of the previous chain.
  #
  # Therefore, a single insert adds (N^16 plus some) rows to the database.
  # A really long loop...
  #     
  execsql {
    CREATE TRIGGER update_b BEFORE UPDATE ON trig
      FOR EACH ROW BEGIN
        INSERT INTO trig VALUES (65, 'update_b');
      END;

    CREATE TRIGGER update_a AFTER UPDATE ON trig
      FOR EACH ROW BEGIN
        INSERT INTO trig VALUES (65, 'update_a');
      END;

    CREATE TRIGGER insert_b BEFORE INSERT ON trig
      FOR EACH ROW BEGIN
        UPDATE trig SET a = 1;
      END;

    CREATE TRIGGER insert_a AFTER INSERT ON trig
      FOR EACH ROW BEGIN
        UPDATE trig SET a = 1;
      END;
  }
} {}

do_test sqllimits1-7.3 {
  execsql {
    INSERT INTO trig VALUES (1,1); 
  }
} {}

do_test sqllimits1-7.4 {
  execsql {
    SELECT COUNT(*) FROM trig;
  }
} {7}

# This tries to insert so many rows it fills up the database (limited
# to 1MB, so not that noteworthy an achievement).
#
do_test sqllimits1-7.5 {
  catchsql {
    INSERT INTO trig VALUES (1,10);
  }
} {1 {database or disk is full}}

do_test sqllimits1-7.6 {
  catchsql {
    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-7.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-7.7.2 {
  db close
  sqlite3 db test.db
  execsql {
    PRAGMA max_page_count = 1000;
  }
  execsql {
    SELECT count(*) FROM sqlite_master;
  }
} {6}
do_test sqllimits1-7.7.3 {
  execsql {
    PRAGMA max_page_count;
  }
} $fsize
do_test sqllimits1-7.7.4 {
  execsql {
    DROP TABLE abc;
  }
} {}

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

do_test sqllimits1-8.2 {
  # Columns in the result-set of a SELECT.
  set cols [list]
  for {set i 0} {$i <= $SQLITE_LIMIT_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-8.3 {
  # Columns in the result-set of a sub-SELECT.
  set cols [list]
  for {set i 0} {$i <= $SQLITE_LIMIT_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-8.4 {
  # Columns in an index.
  set cols [list]
  for {set i 0} {$i <= $SQLITE_LIMIT_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-8.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-8.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-8.7 {
  # Assignments in an UPDATE statement.
  set cols [list]
  for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
    lappend cols "c = 1"
  }
  catchsql "UPDATE t1 SET [join $cols ,];"
} {1 {too many columns in set list}}

do_test sqllimits1-8.8 {
  # Columns in a view definition:
  set cols [list]
  for {set i 0} {$i <= $SQLITE_LIMIT_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-8.9 {
  # Columns in a view definition (testing * expansion):
  set cols [list]
  for {set i 0} {$i < $SQLITE_LIMIT_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}}
do_test sqllimits1-8.10 {
  # ORDER BY columns
  set cols [list]
  for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
    lappend cols c
  }
  set sql "SELECT c FROM t1 ORDER BY [join $cols ,]"
  catchsql $sql
} {1 {too many terms in ORDER BY clause}}
do_test sqllimits1-8.11 {
  # ORDER BY columns
  set cols [list]
  for {set i 0} {$i <= $SQLITE_LIMIT_COLUMN} {incr i} {
    lappend cols [expr {$i%3 + 1}]
  }
  set sql "SELECT c, c+1, c+2 FROM t1 UNION SELECT c-1, c-2, c-3 FROM t1"
  append sql " ORDER BY [join $cols ,]"
  catchsql $sql
} {1 {too many terms in ORDER BY clause}}


#--------------------------------------------------------------------
# These tests - sqllimits1-9.* - test that the SQLITE_LIMIT_EXPR_DEPTH
# limit is enforced. The limit refers to the number of terms in 
# the expression.
#
if {$SQLITE_MAX_EXPR_DEPTH==0} {
  puts -nonewline stderr "WARNING: Compile with -DSQLITE_MAX_EXPR_DEPTH to run "
  puts stderr "tests sqllimits1-9.X"
} else {
  do_test sqllimits1-9.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-9.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}"
  
if 0 {  
  do_test sqllimits1-9.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-9.4 {
    catchsql {
      SELECT a FROM v199
    }
  } "1 {Expression tree is too large (maximum depth $::SQLITE_MAX_EXPR_DEPTH)}"
}
}

#--------------------------------------------------------------------
# Test cases sqllimits1-10.* 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_LIMIT_FUNCTION_ARG limit works. Test case names
# match the pattern "sqllimits1-11.*".
#
do_test sqllimits1-11.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-11.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-11.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-12.*: Test the SQLITE_MAX_ATTACHED limit.
#
ifcapable attach {
  do_test sqllimits1-12.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-12.2 {
    set max $::SQLITE_MAX_ATTACHED
    for {set i 0} {$i < ($max)} {incr i} {
      execsql "DETACH aux${i}"
    }
  } {}
}

#--------------------------------------------------------------------
# Test cases sqllimits1-13.*: Check that the SQLITE_MAX_VARIABLE_NUMBER 
# limit works.
#
do_test sqllimits1-13.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-13.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}"


#--------------------------------------------------------------------
# Test cases sqllimits1-15.* 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).
#
set SQLITE_LIMIT_LIKE_PATTERN 1000
sqlite3_limit db SQLITE_LIMIT_LIKE_PATTERN_LENGTH $SQLITE_LIMIT_LIKE_PATTERN
do_test sqllimits1-15.1 {
  set max $::SQLITE_LIMIT_LIKE_PATTERN
  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-15.2 {
  set max $::SQLITE_LIMIT_LIKE_PATTERN
  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-16.1 {
  set ::N [expr int(([expr pow(2,32)]/50) + 1)]
  expr (($::N*50) & 0xffffffff)<55
} {1}
do_test sqllimits1-16.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