sl@0: # 2001 September 23
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this file is stressing the library by putting large amounts
sl@0: # of data in a single row of a table.
sl@0: #
sl@0: # $Id: bigrow.test,v 1.5 2004/08/07 23:54:48 drh Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: # Make a big string that we can use for test data
sl@0: #
sl@0: do_test bigrow-1.0 {
sl@0:   set ::bigstr {}
sl@0:   for {set i 1} {$i<=9999} {incr i} {
sl@0:     set sep [string index "abcdefghijklmnopqrstuvwxyz" [expr {$i%26}]]
sl@0:     append ::bigstr "$sep [format %04d $i] "
sl@0:   }
sl@0:   string length $::bigstr
sl@0: } {69993}
sl@0: 
sl@0: # Make a table into which we can insert some but records.
sl@0: #
sl@0: do_test bigrow-1.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t1(a text, b text, c text);
sl@0:     SELECT name FROM sqlite_master
sl@0:       WHERE type='table' OR type='index'
sl@0:       ORDER BY name
sl@0:   }
sl@0: } {t1}
sl@0: 
sl@0: do_test bigrow-1.2 {
sl@0:   set ::big1 [string range $::bigstr 0 65519]
sl@0:   set sql "INSERT INTO t1 VALUES('abc',"
sl@0:   append sql "'$::big1', 'xyz');"
sl@0:   execsql $sql
sl@0:   execsql {SELECT a, c FROM t1}
sl@0: } {abc xyz}
sl@0: do_test bigrow-1.3 {
sl@0:   execsql {SELECT b FROM t1}
sl@0: } [list $::big1]
sl@0: do_test bigrow-1.4 {
sl@0:   set ::big2 [string range $::bigstr 0 65520]
sl@0:   set sql "INSERT INTO t1 VALUES('abc2',"
sl@0:   append sql "'$::big2', 'xyz2');"
sl@0:   set r [catch {execsql $sql} msg]
sl@0:   lappend r $msg
sl@0: } {0 {}}
sl@0: do_test bigrow-1.4.1 {
sl@0:   execsql {SELECT b FROM t1 ORDER BY c}
sl@0: } [list $::big1 $::big2]
sl@0: do_test bigrow-1.4.2 {
sl@0:   execsql {SELECT c FROM t1 ORDER BY c}
sl@0: } {xyz xyz2}
sl@0: do_test bigrow-1.4.3 {
sl@0:   execsql {DELETE FROM t1 WHERE a='abc2'}
sl@0:   execsql {SELECT c FROM t1}
sl@0: } {xyz}
sl@0: 
sl@0: do_test bigrow-1.5 {
sl@0:   execsql {
sl@0:     UPDATE t1 SET a=b, b=a;
sl@0:     SELECT b,c FROM t1
sl@0:   }
sl@0: } {abc xyz}
sl@0: do_test bigrow-1.6 {
sl@0:   execsql {
sl@0:     SELECT * FROM t1
sl@0:   }
sl@0: } [list $::big1 abc xyz]
sl@0: do_test bigrow-1.7 {
sl@0:   execsql {
sl@0:     INSERT INTO t1 VALUES('1','2','3');
sl@0:     INSERT INTO t1 VALUES('A','B','C');
sl@0:     SELECT b FROM t1 WHERE a=='1';
sl@0:   }
sl@0: } {2}
sl@0: do_test bigrow-1.8 {
sl@0:   execsql "SELECT b FROM t1 WHERE a=='$::big1'"
sl@0: } {abc}
sl@0: do_test bigrow-1.9 {
sl@0:   execsql "SELECT b FROM t1 WHERE a!='$::big1' ORDER BY a"
sl@0: } {2 B}
sl@0: 
sl@0: # Try doing some indexing on big columns
sl@0: #
sl@0: do_test bigrow-2.1 {
sl@0:   execsql {
sl@0:     CREATE INDEX i1 ON t1(a)
sl@0:   }
sl@0:   execsql "SELECT b FROM t1 WHERE a=='$::big1'"
sl@0: } {abc}
sl@0: do_test bigrow-2.2 {
sl@0:   execsql {
sl@0:     UPDATE t1 SET a=b, b=a
sl@0:   }
sl@0:   execsql "SELECT b FROM t1 WHERE a=='abc'"
sl@0: } [list $::big1]
sl@0: do_test bigrow-2.3 {
sl@0:   execsql {
sl@0:     UPDATE t1 SET a=b, b=a
sl@0:   }
sl@0:   execsql "SELECT b FROM t1 WHERE a=='$::big1'"
sl@0: } {abc}
sl@0: catch {unset ::bigstr}
sl@0: catch {unset ::big1}
sl@0: catch {unset ::big2}
sl@0: 
sl@0: # Mosts of the tests above were created back when rows were limited in
sl@0: # size to 64K.  Now rows can be much bigger.  Test that logic.  Also
sl@0: # make sure things work correctly at the transition boundries between
sl@0: # row sizes of 256 to 257 bytes and from 65536 to 65537 bytes.
sl@0: #
sl@0: # We begin by testing the 256..257 transition.
sl@0: #
sl@0: do_test bigrow-3.1 {
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:     INSERT INTO t1(a,b,c) VALUES('one','abcdefghijklmnopqrstuvwxyz0123','hi');
sl@0:   }
sl@0:   execsql {SELECT a,length(b),c FROM t1}
sl@0: } {one 30 hi}
sl@0: do_test bigrow-3.2 {
sl@0:   execsql {
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:   }
sl@0:   execsql {SELECT a,length(b),c FROM t1}
sl@0: } {one 240 hi}
sl@0: for {set i 1} {$i<10} {incr i} {
sl@0:   do_test bigrow-3.3.$i {
sl@0:     execsql "UPDATE t1 SET b=b||'$i'"
sl@0:     execsql {SELECT a,length(b),c FROM t1}
sl@0:   } "one [expr {240+$i}] hi"
sl@0: }
sl@0: 
sl@0: # Now test the 65536..65537 row-size transition.
sl@0: #
sl@0: do_test bigrow-4.1 {
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:     INSERT INTO t1(a,b,c) VALUES('one','abcdefghijklmnopqrstuvwxyz0123','hi');
sl@0:   }
sl@0:   execsql {SELECT a,length(b),c FROM t1}
sl@0: } {one 30 hi}
sl@0: do_test bigrow-4.2 {
sl@0:   execsql {
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:     UPDATE t1 SET b=b||b;
sl@0:   }
sl@0:   execsql {SELECT a,length(b),c FROM t1}
sl@0: } {one 122880 hi}
sl@0: do_test bigrow-4.3 {
sl@0:   execsql {
sl@0:     UPDATE t1 SET b=substr(b,1,65515)
sl@0:   }
sl@0:   execsql {SELECT a,length(b),c FROM t1}
sl@0: } {one 65515 hi}
sl@0: for {set i 1} {$i<10} {incr i} {
sl@0:   do_test bigrow-4.4.$i {
sl@0:     execsql "UPDATE t1 SET b=b||'$i'"
sl@0:     execsql {SELECT a,length(b),c FROM t1}
sl@0:   } "one [expr {65515+$i}] hi"
sl@0: }
sl@0: 
sl@0: # Check to make sure the library recovers safely if a row contains
sl@0: # too much data.
sl@0: #
sl@0: do_test bigrow-5.1 {
sl@0:   execsql {
sl@0:     DELETE FROM t1;
sl@0:     INSERT INTO t1(a,b,c) VALUES('one','abcdefghijklmnopqrstuvwxyz0123','hi');
sl@0:   }
sl@0:   execsql {SELECT a,length(b),c FROM t1}
sl@0: } {one 30 hi}
sl@0: set i 1
sl@0: for {set sz 60} {$sz<1048560} {incr sz $sz} {
sl@0:   do_test bigrow-5.2.$i {
sl@0:     execsql {
sl@0:       UPDATE t1 SET b=b||b;
sl@0:       SELECT a,length(b),c FROM t1;
sl@0:     }
sl@0:   } "one $sz hi"
sl@0:   incr i
sl@0: }
sl@0: do_test bigrow-5.3 {
sl@0:   catchsql {UPDATE t1 SET b=b||b}
sl@0: } {0 {}}
sl@0: do_test bigrow-5.4 {
sl@0:   execsql {SELECT length(b) FROM t1}
sl@0: } 1966080
sl@0: do_test bigrow-5.5 {
sl@0:   catchsql {UPDATE t1 SET b=b||b}
sl@0: } {0 {}}
sl@0: do_test bigrow-5.6 {
sl@0:   execsql {SELECT length(b) FROM t1}
sl@0: } 3932160
sl@0: do_test bigrow-5.99 {
sl@0:   execsql {DROP TABLE t1}
sl@0: } {}
sl@0: 
sl@0: finish_test