1 # 2008 June 9

     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 #

    12 # Test that it is possible to have two open blob handles on a single

    13 # blob object.

    14 #

    15 # $Id: incrblob2.test,v 1.8 2008/06/28 15:33:26 danielk1977 Exp $

    16 #

    17 

    18 set testdir [file dirname $argv0]

    19 source $testdir/tester.tcl

    20 

    21 ifcapable {!autovacuum || !pragma || !incrblob} {

    22   finish_test

    23   return

    24 }

    25 

    26 do_test incrblob2-1.0 {

    27   execsql {

    28     CREATE TABLE blobs(id INTEGER PRIMARY KEY, data BLOB);

    29     INSERT INTO blobs VALUES(NULL, zeroblob(5000));

    30     INSERT INTO blobs VALUES(NULL, zeroblob(5000));

    31     INSERT INTO blobs VALUES(NULL, zeroblob(5000));

    32     INSERT INTO blobs VALUES(NULL, zeroblob(5000));

    33   }

    34 } {}

    35 

    36 foreach iOffset [list 0 256 4094] {

    37   do_test incrblob2-1.$iOffset.1 {

    38     set fd [db incrblob blobs data 1]

    39     puts $fd "[string repeat x $iOffset]SQLite version 3.6.0"

    40     close $fd

    41   } {}

    42   

    43   do_test incrblob2-1.$iOffset.2 {

    44     set fd1 [db incrblob blobs data 1]

    45     set fd2 [db incrblob blobs data 1]

    46     fconfigure $fd1 -buffering none

    47     fconfigure $fd2 -buffering none

    48     if {$iOffset != 0} {

    49       seek $fd2 $iOffset start

    50       seek $fd1 $iOffset start

    51     }

    52     read $fd1 6

    53   } {SQLite}

    54   

    55   do_test incrblob2-1.$iOffset.3 {

    56     read $fd2 6

    57   } {SQLite}

    58   

    59   do_test incrblob2-1.$iOffset.4 {

    60     seek $fd2 $iOffset start

    61     seek $fd1 $iOffset start

    62     puts -nonewline $fd2 "etiLQS"

    63   } {}

    64 

    65   

    66   do_test incrblob2-1.$iOffset.5 {

    67     seek $fd1 $iOffset start

    68     read $fd1 6

    69   } {etiLQS}

    70   

    71   do_test incrblob2-1.$iOffset.6 {

    72     seek $fd2 $iOffset start

    73     read $fd2 6

    74   } {etiLQS}

    75   

    76   do_test incrblob2-1.$iOffset.7 {

    77     seek $fd1 $iOffset start

    78     read $fd1 6

    79   } {etiLQS}

    80   

    81   do_test incrblob2-1.$iOffset.8 {

    82     close $fd1

    83     close $fd2

    84   } {}

    85 }

    86 

    87 #--------------------------------------------------------------------------

    88 

    89 foreach iOffset [list 0 256 4094] {

    90 

    91   do_test incrblob2-2.$iOffset.1 {

    92     set fd1 [db incrblob blobs data 1]

    93     seek $fd1 [expr $iOffset - 5000] end

    94     fconfigure $fd1 -buffering none

    95 

    96     set fd2 [db incrblob blobs data 1]

    97     seek $fd2 [expr $iOffset - 5000] end

    98     fconfigure $fd2 -buffering none

    99 

   100     puts -nonewline $fd1 "123456"

   101   } {}

   102   

   103   do_test incrblob2-2.$iOffset.2 {

   104     read $fd2 6

   105   } {123456}

   106 

   107   do_test incrblob2-2.$iOffset.3 {

   108     close $fd1

   109     close $fd2

   110   } {}

   111 }

   112 

   113 do_test incrblob2-3.1 {

   114   set fd1 [db incrblob blobs data 1]

   115   fconfigure $fd1 -buffering none

   116 } {}

   117 do_test incrblob2-3.2 {

   118   execsql {

   119     INSERT INTO blobs VALUES(5, zeroblob(10240));

   120   }

   121 } {}

   122 do_test incrblob2-3.3 {

   123   set rc [catch { read $fd1 6 } msg]

   124   list $rc $msg

   125 } {0 123456}

   126 do_test incrblob2-3.4 {

   127   close $fd1

   128 } {}

   129 

   130 #--------------------------------------------------------------------------

   131 # The following tests - incrblob2-4.* - test that blob handles are 

   132 # invalidated at the correct times.

   133 #

   134 do_test incrblob2-4.1 {

   135   unset -nocomplain data

   136   db eval BEGIN

   137   db eval { CREATE TABLE t1(id INTEGER PRIMARY KEY, data BLOB); }

   138   for {set ii 1} {$ii < 100} {incr ii} {

   139     set data [string repeat "blob$ii" 500]

   140     db eval { INSERT INTO t1 VALUES($ii, $data) }

   141   }

   142   db eval COMMIT

   143 } {}

   144 

   145 proc aborted_handles {} {

   146   global handles

   147 

   148   set aborted {}

   149   for {set ii 1} {$ii < 100} {incr ii} {

   150     set str "blob$ii"

   151     set nByte [string length $str]

   152     set iOffset [expr $nByte * $ii * 2]

   153 

   154     set rc [catch {sqlite3_blob_read $handles($ii) $iOffset $nByte} msg]

   155     if {$rc && $msg eq "SQLITE_ABORT"} {

   156       lappend aborted $ii

   157     } else {

   158       if {$rc || $msg ne $str} {

   159         error "blob $ii: $msg"

   160       }

   161     }

   162   }

   163   set aborted

   164 }

   165 

   166 do_test incrblob2-4.2 {

   167   for {set ii 1} {$ii < 100} {incr ii} {

   168     set handles($ii) [db incrblob t1 data $ii]

   169   }

   170   aborted_handles

   171 } {}

   172 

   173 # Update row 3. This should abort handle 3 but leave all others untouched.

   174 #

   175 do_test incrblob2-4.3 {

   176   db eval {UPDATE t1 SET data = data || '' WHERE id = 3}

   177   aborted_handles

   178 } {3}

   179 

   180 # Test that a write to handle 3 also returns SQLITE_ABORT.

   181 #

   182 do_test incrblob2-4.3.1 {

   183   set rc [catch {sqlite3_blob_write $::handles(3) 10 HELLO} msg]

   184   list $rc $msg

   185 } {1 SQLITE_ABORT}

   186 

   187 # Delete row 14. This should abort handle 6 but leave all others untouched.

   188 #

   189 do_test incrblob2-4.4 {

   190   db eval {DELETE FROM t1 WHERE id = 14}

   191   aborted_handles

   192 } {3 14}

   193 

   194 # Change the rowid of row 15 to 102. Should abort handle 15.

   195 #

   196 do_test incrblob2-4.5 {

   197   db eval {UPDATE t1 SET id = 102 WHERE id = 15}

   198   aborted_handles

   199 } {3 14 15}

   200 

   201 # Clobber row 92 using INSERT OR REPLACE.

   202 #

   203 do_test incrblob2-4.6 {

   204   db eval {INSERT OR REPLACE INTO t1 VALUES(92, zeroblob(1000))}

   205   aborted_handles

   206 } {3 14 15 92}

   207 

   208 # Clobber row 65 using UPDATE OR REPLACE on row 35. This should abort 

   209 # handles 35 and 65.

   210 #

   211 do_test incrblob2-4.7 {

   212   db eval {UPDATE OR REPLACE t1 SET id = 65 WHERE id = 35}

   213   aborted_handles

   214 } {3 14 15 35 65 92}

   215 

   216 # Insert a couple of new rows. This should not invalidate any handles.

   217 #

   218 do_test incrblob2-4.9 {

   219   db eval {INSERT INTO t1 SELECT NULL, data FROM t1}

   220   aborted_handles

   221 } {3 14 15 35 65 92}

   222 

   223 # Delete all rows from 1 to 25. This should abort all handles up to 25.

   224 #

   225 do_test incrblob2-4.9 {

   226   db eval {DELETE FROM t1 WHERE id >=1 AND id <= 25}

   227   aborted_handles

   228 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 35 65 92}

   229 

   230 # Delete the whole table (this will use sqlite3BtreeClearTable()). All handles

   231 # should now be aborted.

   232 #

   233 do_test incrblob2-4.10 {

   234   db eval {DELETE FROM t1}

   235   aborted_handles

   236 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99}

   237 

   238 do_test incrblob2-4.1.X {

   239   for {set ii 1} {$ii < 100} {incr ii} {

   240     close $handles($ii) 

   241   }

   242 } {}

   243 

   244 #--------------------------------------------------------------------------

   245 # The following tests - incrblob2-5.* - test that in shared cache an open

   246 # blob handle counts as a read-lock on its table.

   247 #

   248 ifcapable shared_cache {

   249   db close

   250   set ::enable_shared_cache [sqlite3_enable_shared_cache 1]

   251 

   252   do_test incrblob2-5.1 {

   253     sqlite3 db test.db

   254     sqlite3 db2 test.db

   255 

   256     execsql {

   257       INSERT INTO t1 VALUES(1, 'abcde');

   258     }

   259   } {}

   260 

   261   do_test incrblob2-5.2 {

   262     catchsql { INSERT INTO t1 VALUES(2, 'fghij') } db2

   263   } {0 {}}

   264 

   265   do_test incrblob2-5.3 {

   266     set blob [db incrblob t1 data 1]

   267     catchsql { INSERT INTO t1 VALUES(3, 'klmno') } db2

   268   } {1 {database is locked}}

   269 

   270   do_test incrblob2-5.4 {

   271     close $blob

   272     execsql BEGIN db2

   273     catchsql { INSERT INTO t1 VALUES(4, 'pqrst') } db2

   274   } {0 {}}

   275 

   276   do_test incrblob2-5.5 {

   277     set blob [db incrblob -readonly t1 data 1]

   278     catchsql { INSERT INTO t1 VALUES(5, 'uvwxy') } db2

   279   } {1 {database table is locked}}

   280 

   281   do_test incrblob2-5.6 {

   282     close $blob

   283     catchsql { INSERT INTO t1 VALUES(3, 'klmno') } db2

   284   } {0 {}}

   285 

   286   db2 close

   287   db close

   288   sqlite3_enable_shared_cache $::enable_shared_cache

   289 }

   290 

   291 #--------------------------------------------------------------------------

   292 # The following tests - incrblob2-6.* - test a specific scenario that might

   293 # be causing an error.

   294 #

   295 sqlite3 db test.db

   296 do_test incrblob2-6.1 {

   297   execsql {

   298     DELETE FROM t1;

   299     INSERT INTO t1 VALUES(1, zeroblob(100));

   300   }

   301   

   302   set rdHandle [db incrblob -readonly t1 data 1]

   303   set wrHandle [db incrblob t1 data 1]

   304 

   305   sqlite3_blob_read $rdHandle 0 100

   306 

   307   sqlite3_blob_write $wrHandle 0 ABCDEF

   308 

   309   close $wrHandle

   310   close $rdHandle

   311 } {}

   312 

   313 do_test incrblob2-6.2 {

   314   set rdHandle [db incrblob -readonly t1 data 1]

   315   sqlite3_blob_read $rdHandle 0 2

   316 } {AB}

   317 

   318 do_test incrblob2-6.3 {

   319   set wrHandle [db incrblob t1 data 1]

   320   sqlite3_blob_write $wrHandle 0 ZZZZZZZZZZ

   321   sqlite3_blob_read $rdHandle 2 4

   322 } {ZZZZ}

   323 

   324 do_test incrblob2-6.4 {

   325   close $wrHandle

   326   close $rdHandle

   327 } {}

   328 

   329 sqlite3_memory_highwater 1

   330 do_test incrblob2-7.1 {

   331   db eval {

   332     CREATE TABLE t2(B BLOB);

   333     INSERT INTO t2 VALUES(zeroblob(10 * 1024 * 1024)); 

   334   }

   335   expr {[sqlite3_memory_highwater]<(5 * 1024 * 1024)}

   336 } {1}

   337 

   338 do_test incrblob2-7.2 {

   339   set h [db incrblob t2 B 1]

   340   expr {[sqlite3_memory_highwater]<(5 * 1024 * 1024)}

   341 } {1}

   342 

   343 do_test incrblob2-7.3 {

   344   seek $h 0 end

   345   tell $h

   346 } [expr 10 * 1024 * 1024]

   347 

   348 do_test incrblob2-7.4 {

   349   expr {[sqlite3_memory_highwater]<(5 * 1024 * 1024)}

   350 } {1}

   351 

   352 do_test incrblob2-7.5 {

   353   close $h

   354 } {}

   355 

   356 #---------------------------------------------------------------------------

   357 # The following tests, incrblob2-8.*, test that nothing terrible happens

   358 # when a statement transaction is rolled back while there are open 

   359 # incremental-blob handles. At one point an assert() was failing when

   360 # this was attempted.

   361 #

   362 do_test incrblob2-8.1 {

   363   execsql BEGIN

   364   set h [db incrblob t2 B 1]

   365   set rc [catch {

   366     db eval {SELECT ROWID FROM t2} { execsql "DROP TABLE t2" }

   367   } msg] 

   368   list $rc $msg

   369 } {1 {database table is locked}}

   370 do_test incrblob2-8.2 {

   371   close $h

   372   execsql COMMIT

   373 } {}

   374 do_test incrblob2-8.3 {

   375   execsql {

   376     CREATE TABLE t3(a INTEGER UNIQUE, b TEXT);

   377     INSERT INTO t3 VALUES(1, 'aaaaaaaaaaaaaaaaaaaa');

   378     INSERT INTO t3 VALUES(2, 'bbbbbbbbbbbbbbbbbbbb');

   379     INSERT INTO t3 VALUES(3, 'cccccccccccccccccccc');

   380     INSERT INTO t3 VALUES(4, 'dddddddddddddddddddd');

   381     INSERT INTO t3 VALUES(5, 'eeeeeeeeeeeeeeeeeeee');

   382   }

   383 } {}

   384 do_test incrblob2-8.4 {

   385   execsql BEGIN

   386   set h [db incrblob t3 b 3]

   387   sqlite3_blob_read $h 0 20

   388 } {cccccccccccccccccccc}

   389 do_test incrblob2-8.5 {

   390   catchsql {UPDATE t3 SET a = 6 WHERE a > 3}

   391 } {1 {column a is not unique}}

   392 do_test incrblob2-8.6 {

   393   catchsql {UPDATE t3 SET a = 6 WHERE a > 3}

   394 } {1 {column a is not unique}}

   395 do_test incrblob2-8.7 {

   396   sqlite3_blob_read $h 0 20

   397 } {cccccccccccccccccccc}

   398 do_test incrblob2-8.8 {

   399   catchsql {UPDATE t3 SET a = 6 WHERE a = 3 OR a = 5}

   400 } {1 {column a is not unique}}

   401 do_test incrblob2-8.9 {

   402   set rc [catch {sqlite3_blob_read $h 0 20} msg]

   403   list $rc $msg

   404 } {1 SQLITE_ABORT}

   405 do_test incrblob2-8.X {

   406   close $h

   407 } {}

   408 

   409 finish_test