1 # 2001 September 23

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     6 #    May you do good and not evil.

     7 #    May you find forgiveness for yourself and forgive others.

     8 #    May you share freely, never taking more than you give.

     9 #

    10 #***********************************************************************

    11 # This file implements regression tests for SQLite library.  The

    12 # focus of this file is stressing the library by putting large amounts

    13 # of data in a single row of a table.

    14 #

    15 # $Id: bigrow.test,v 1.5 2004/08/07 23:54:48 drh Exp $

    16 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 # Make a big string that we can use for test data

    21 #

    22 do_test bigrow-1.0 {

    23   set ::bigstr {}

    24   for {set i 1} {$i<=9999} {incr i} {

    25     set sep [string index "abcdefghijklmnopqrstuvwxyz" [expr {$i%26}]]

    26     append ::bigstr "$sep [format %04d $i] "

    27   }

    28   string length $::bigstr

    29 } {69993}

    30 

    31 # Make a table into which we can insert some but records.

    32 #

    33 do_test bigrow-1.1 {

    34   execsql {

    35     CREATE TABLE t1(a text, b text, c text);

    36     SELECT name FROM sqlite_master

    37       WHERE type='table' OR type='index'

    38       ORDER BY name

    39   }

    40 } {t1}

    41 

    42 do_test bigrow-1.2 {

    43   set ::big1 [string range $::bigstr 0 65519]

    44   set sql "INSERT INTO t1 VALUES('abc',"

    45   append sql "'$::big1', 'xyz');"

    46   execsql $sql

    47   execsql {SELECT a, c FROM t1}

    48 } {abc xyz}

    49 do_test bigrow-1.3 {

    50   execsql {SELECT b FROM t1}

    51 } [list $::big1]

    52 do_test bigrow-1.4 {

    53   set ::big2 [string range $::bigstr 0 65520]

    54   set sql "INSERT INTO t1 VALUES('abc2',"

    55   append sql "'$::big2', 'xyz2');"

    56   set r [catch {execsql $sql} msg]

    57   lappend r $msg

    58 } {0 {}}

    59 do_test bigrow-1.4.1 {

    60   execsql {SELECT b FROM t1 ORDER BY c}

    61 } [list $::big1 $::big2]

    62 do_test bigrow-1.4.2 {

    63   execsql {SELECT c FROM t1 ORDER BY c}

    64 } {xyz xyz2}

    65 do_test bigrow-1.4.3 {

    66   execsql {DELETE FROM t1 WHERE a='abc2'}

    67   execsql {SELECT c FROM t1}

    68 } {xyz}

    69 

    70 do_test bigrow-1.5 {

    71   execsql {

    72     UPDATE t1 SET a=b, b=a;

    73     SELECT b,c FROM t1

    74   }

    75 } {abc xyz}

    76 do_test bigrow-1.6 {

    77   execsql {

    78     SELECT * FROM t1

    79   }

    80 } [list $::big1 abc xyz]

    81 do_test bigrow-1.7 {

    82   execsql {

    83     INSERT INTO t1 VALUES('1','2','3');

    84     INSERT INTO t1 VALUES('A','B','C');

    85     SELECT b FROM t1 WHERE a=='1';

    86   }

    87 } {2}

    88 do_test bigrow-1.8 {

    89   execsql "SELECT b FROM t1 WHERE a=='$::big1'"

    90 } {abc}

    91 do_test bigrow-1.9 {

    92   execsql "SELECT b FROM t1 WHERE a!='$::big1' ORDER BY a"

    93 } {2 B}

    94 

    95 # Try doing some indexing on big columns

    96 #

    97 do_test bigrow-2.1 {

    98   execsql {

    99     CREATE INDEX i1 ON t1(a)

   100   }

   101   execsql "SELECT b FROM t1 WHERE a=='$::big1'"

   102 } {abc}

   103 do_test bigrow-2.2 {

   104   execsql {

   105     UPDATE t1 SET a=b, b=a

   106   }

   107   execsql "SELECT b FROM t1 WHERE a=='abc'"

   108 } [list $::big1]

   109 do_test bigrow-2.3 {

   110   execsql {

   111     UPDATE t1 SET a=b, b=a

   112   }

   113   execsql "SELECT b FROM t1 WHERE a=='$::big1'"

   114 } {abc}

   115 catch {unset ::bigstr}

   116 catch {unset ::big1}

   117 catch {unset ::big2}

   118 

   119 # Mosts of the tests above were created back when rows were limited in

   120 # size to 64K.  Now rows can be much bigger.  Test that logic.  Also

   121 # make sure things work correctly at the transition boundries between

   122 # row sizes of 256 to 257 bytes and from 65536 to 65537 bytes.

   123 #

   124 # We begin by testing the 256..257 transition.

   125 #

   126 do_test bigrow-3.1 {

   127   execsql {

   128     DELETE FROM t1;

   129     INSERT INTO t1(a,b,c) VALUES('one','abcdefghijklmnopqrstuvwxyz0123','hi');

   130   }

   131   execsql {SELECT a,length(b),c FROM t1}

   132 } {one 30 hi}

   133 do_test bigrow-3.2 {

   134   execsql {

   135     UPDATE t1 SET b=b||b;

   136     UPDATE t1 SET b=b||b;

   137     UPDATE t1 SET b=b||b;

   138   }

   139   execsql {SELECT a,length(b),c FROM t1}

   140 } {one 240 hi}

   141 for {set i 1} {$i<10} {incr i} {

   142   do_test bigrow-3.3.$i {

   143     execsql "UPDATE t1 SET b=b||'$i'"

   144     execsql {SELECT a,length(b),c FROM t1}

   145   } "one [expr {240+$i}] hi"

   146 }

   147 

   148 # Now test the 65536..65537 row-size transition.

   149 #

   150 do_test bigrow-4.1 {

   151   execsql {

   152     DELETE FROM t1;

   153     INSERT INTO t1(a,b,c) VALUES('one','abcdefghijklmnopqrstuvwxyz0123','hi');

   154   }

   155   execsql {SELECT a,length(b),c FROM t1}

   156 } {one 30 hi}

   157 do_test bigrow-4.2 {

   158   execsql {

   159     UPDATE t1 SET b=b||b;

   160     UPDATE t1 SET b=b||b;

   161     UPDATE t1 SET b=b||b;

   162     UPDATE t1 SET b=b||b;

   163     UPDATE t1 SET b=b||b;

   164     UPDATE t1 SET b=b||b;

   165     UPDATE t1 SET b=b||b;

   166     UPDATE t1 SET b=b||b;

   167     UPDATE t1 SET b=b||b;

   168     UPDATE t1 SET b=b||b;

   169     UPDATE t1 SET b=b||b;

   170     UPDATE t1 SET b=b||b;

   171   }

   172   execsql {SELECT a,length(b),c FROM t1}

   173 } {one 122880 hi}

   174 do_test bigrow-4.3 {

   175   execsql {

   176     UPDATE t1 SET b=substr(b,1,65515)

   177   }

   178   execsql {SELECT a,length(b),c FROM t1}

   179 } {one 65515 hi}

   180 for {set i 1} {$i<10} {incr i} {

   181   do_test bigrow-4.4.$i {

   182     execsql "UPDATE t1 SET b=b||'$i'"

   183     execsql {SELECT a,length(b),c FROM t1}

   184   } "one [expr {65515+$i}] hi"

   185 }

   186 

   187 # Check to make sure the library recovers safely if a row contains

   188 # too much data.

   189 #

   190 do_test bigrow-5.1 {

   191   execsql {

   192     DELETE FROM t1;

   193     INSERT INTO t1(a,b,c) VALUES('one','abcdefghijklmnopqrstuvwxyz0123','hi');

   194   }

   195   execsql {SELECT a,length(b),c FROM t1}

   196 } {one 30 hi}

   197 set i 1

   198 for {set sz 60} {$sz<1048560} {incr sz $sz} {

   199   do_test bigrow-5.2.$i {

   200     execsql {

   201       UPDATE t1 SET b=b||b;

   202       SELECT a,length(b),c FROM t1;

   203     }

   204   } "one $sz hi"

   205   incr i

   206 }

   207 do_test bigrow-5.3 {

   208   catchsql {UPDATE t1 SET b=b||b}

   209 } {0 {}}

   210 do_test bigrow-5.4 {

   211   execsql {SELECT length(b) FROM t1}

   212 } 1966080

   213 do_test bigrow-5.5 {

   214   catchsql {UPDATE t1 SET b=b||b}

   215 } {0 {}}

   216 do_test bigrow-5.6 {

   217   execsql {SELECT length(b) FROM t1}

   218 } 3932160

   219 do_test bigrow-5.99 {

   220   execsql {DROP TABLE t1}

   221 } {}

   222 

   223 finish_test