1 # 2007 May 1

     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 # $Id: incrblob.test,v 1.21 2008/09/11 11:28:00 danielk1977 Exp $

    13 #

    14 

    15 set testdir [file dirname $argv0]

    16 source $testdir/tester.tcl

    17 

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

    19   finish_test

    20   return

    21 }

    22 

    23 do_test incrblob-1.1 {

    24   execsql {

    25     CREATE TABLE blobs(k PRIMARY KEY, v BLOB);

    26     INSERT INTO blobs VALUES('one', X'0102030405060708090A');

    27     INSERT INTO blobs VALUES('two', X'0A090807060504030201');

    28   }

    29 } {}

    30 

    31 do_test incrblob-1.2.1 {

    32   set ::blob [db incrblob blobs v 1]

    33   string match incrblob_* $::blob

    34 } {1}

    35 unset -nocomplain data

    36 do_test incrblob-1.2.2 {

    37   binary scan [read $::blob] c* data

    38   set data

    39 } {1 2 3 4 5 6 7 8 9 10}

    40 do_test incrblob-1.2.3 {

    41   seek $::blob 0

    42   puts -nonewline $::blob "1234567890"

    43   flush $::blob

    44 } {}

    45 do_test incrblob-1.2.4 {

    46   seek $::blob 0

    47   binary scan [read $::blob] c* data

    48   set data

    49 } {49 50 51 52 53 54 55 56 57 48}

    50 do_test incrblob-1.2.5 {

    51   close $::blob

    52 } {}

    53 do_test incrblob-1.2.6 {

    54   execsql {

    55     SELECT v FROM blobs WHERE rowid = 1;

    56   }

    57 } {1234567890}

    58 

    59 #--------------------------------------------------------------------

    60 # Test cases incrblob-1.3.X check that it is possible to read and write

    61 # regions of a blob that lie on overflow pages.

    62 #

    63 do_test incrblob-1.3.1 {

    64   set ::str "[string repeat . 10000]"

    65   execsql {

    66     INSERT INTO blobs(rowid, k, v) VALUES(3, 'three', $::str);

    67   }

    68 } {}

    69 

    70 do_test incrblob-1.3.2 {

    71   set ::blob [db incrblob blobs v 3]

    72   seek $::blob 8500

    73   read $::blob 10

    74 } {..........}

    75 do_test incrblob-1.3.3 {

    76   seek $::blob 8500

    77   puts -nonewline $::blob 1234567890

    78 } {}

    79 do_test incrblob-1.3.4 {

    80   seek $::blob 8496

    81   read $::blob 10

    82 } {....123456}

    83 do_test incrblob-1.3.10 {

    84   close $::blob

    85 } {}

    86 

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

    88 # incrblob-2.*: 

    89 #

    90 # Test that the following operations use ptrmap pages to reduce

    91 # unnecessary reads:

    92 #

    93 #     * Reading near the end of a blob,

    94 #     * Writing near the end of a blob, and

    95 #     * SELECT a column value that is located on an overflow page.

    96 #

    97 proc nRead {db} {

    98   set bt [btree_from_db $db]

    99   db_enter $db

   100   array set stats [btree_pager_stats $bt]

   101   db_leave $db

   102   return $stats(read)

   103 }

   104 proc nWrite {db} {

   105   set bt [btree_from_db $db]

   106   db_enter $db

   107   array set stats [btree_pager_stats $bt]

   108   db_leave $db

   109   return $stats(write)

   110 }

   111 

   112 sqlite3_soft_heap_limit 0

   113 

   114 foreach AutoVacuumMode [list 0 1] {

   115 

   116   if {$AutoVacuumMode>0} {

   117     ifcapable !autovacuum {

   118       break

   119     }

   120   }

   121 

   122   db close

   123   file delete -force test.db test.db-journal

   124 

   125   sqlite3 db test.db

   126   execsql "PRAGMA auto_vacuum = $AutoVacuumMode"

   127 

   128   do_test incrblob-2.$AutoVacuumMode.1 {

   129     set ::str [string repeat abcdefghij 2900]

   130     execsql {

   131       BEGIN;

   132       CREATE TABLE blobs(k PRIMARY KEY, v BLOB, i INTEGER);

   133       DELETE FROM blobs;

   134       INSERT INTO blobs VALUES('one', $::str || randstr(500,500), 45);

   135       COMMIT;

   136     }

   137     expr [file size test.db]/1024

   138   } [expr 31 + $AutoVacuumMode]

   139 

   140   ifcapable autovacuum {

   141     do_test incrblob-2.$AutoVacuumMode.2 {

   142       execsql {

   143         PRAGMA auto_vacuum;

   144       }

   145     } $AutoVacuumMode

   146   }

   147 

   148   do_test incrblob-2.$AutoVacuumMode.3 {

   149     # Open and close the db to make sure the page cache is empty.

   150     db close

   151     sqlite3 db test.db

   152   

   153     # Read the last 20 bytes of the blob via a blob handle.

   154     set ::blob [db incrblob blobs v 1]

   155     seek $::blob -20 end

   156     set ::fragment [read $::blob]

   157     close $::blob

   158   

   159     # If the database is not in auto-vacuum mode, the whole of

   160     # the overflow-chain must be scanned. In auto-vacuum mode,

   161     # sqlite uses the ptrmap pages to avoid reading the other pages.

   162     #

   163     nRead db

   164   } [expr $AutoVacuumMode ? 4 : 30]

   165 

   166   do_test incrblob-2.$AutoVacuumMode.4 {

   167     string range [db one {SELECT v FROM blobs}] end-19 end

   168   } $::fragment

   169 

   170   do_test incrblob-2.$AutoVacuumMode.5 {

   171     # Open and close the db to make sure the page cache is empty.

   172     db close

   173     sqlite3 db test.db

   174   

   175     # Write the second-to-last 20 bytes of the blob via a blob handle.

   176     #

   177     set ::blob [db incrblob blobs v 1]

   178     seek $::blob -40 end

   179     puts -nonewline $::blob "1234567890abcdefghij"

   180     flush $::blob

   181   

   182     # If the database is not in auto-vacuum mode, the whole of

   183     # the overflow-chain must be scanned. In auto-vacuum mode,

   184     # sqlite uses the ptrmap pages to avoid reading the other pages.

   185     #

   186     nRead db

   187   } [expr $AutoVacuumMode ? 4 : 30]

   188 

   189   # Pages 1 (the write-counter) and 32 (the blob data) were written.

   190   do_test incrblob-2.$AutoVacuumMode.6 {

   191     close $::blob

   192     nWrite db

   193   } 2

   194 

   195   do_test incrblob-2.$AutoVacuumMode.7 {

   196     string range [db one {SELECT v FROM blobs}] end-39 end-20

   197   } "1234567890abcdefghij"

   198 

   199   do_test incrblob-2.$AutoVacuumMode.8 {

   200     # Open and close the db to make sure the page cache is empty.

   201     db close

   202     sqlite3 db test.db

   203 

   204     execsql { SELECT i FROM blobs } 

   205   } {45}

   206 

   207   do_test incrblob-2.$AutoVacuumMode.9 {

   208     nRead db

   209   } [expr $AutoVacuumMode ? 4 : 30]

   210 }

   211 sqlite3_soft_heap_limit $soft_limit

   212 

   213 #------------------------------------------------------------------------

   214 # incrblob-3.*: 

   215 #

   216 # Test the outcome of trying to write to a read-only blob handle.

   217 #

   218 do_test incrblob-3.1 {

   219   set ::blob [db incrblob -readonly blobs v 1]

   220   seek $::blob -40 end

   221   read $::blob 20

   222 } "1234567890abcdefghij"

   223 do_test incrblob-3.2 {

   224   seek $::blob 0

   225   set rc [catch {

   226     puts -nonewline $::blob "helloworld"

   227   } msg]

   228   close $::blob

   229   list $rc $msg

   230 } "1 {channel \"$::blob\" wasn't opened for writing}"

   231 

   232 do_test incrblob-3.3 {

   233   set ::blob [db incrblob -readonly blobs v 1]

   234   seek $::blob -40 end

   235   read $::blob 20

   236 } "1234567890abcdefghij"

   237 do_test incrblob-3.4 {

   238   set rc [catch {

   239     sqlite3_blob_write $::blob 20 "qwertyuioplkjhgfds" 

   240   } msg]

   241   list $rc $msg

   242 } {1 SQLITE_READONLY}

   243 catch {close $::blob}

   244 

   245 #------------------------------------------------------------------------

   246 # incrblob-4.*: 

   247 #

   248 # Try a couple of error conditions:

   249 #

   250 #     4.1 - Attempt to open a row that does not exist.

   251 #     4.2 - Attempt to open a column that does not exist.

   252 #     4.3 - Attempt to open a table that does not exist.

   253 #     4.4 - Attempt to open a database that does not exist.

   254 #

   255 #     4.5 - Attempt to open an integer

   256 #     4.6 - Attempt to open a real value

   257 #     4.7 - Attempt to open an SQL null

   258 #

   259 #     4.8 - Attempt to open an indexed column for writing

   260 #     4.9 - Attempt to open an indexed column for reading (this works)

   261 #

   262 #     4.11 - Attempt to open a column of a view.

   263 #     4.12 - Attempt to open a column of a virtual table.

   264 #

   265 do_test incrblob-4.1 {

   266   set rc [catch {

   267     set ::blob [db incrblob blobs v 2]

   268   } msg ] 

   269   list $rc $msg

   270 } {1 {no such rowid: 2}}

   271 do_test incrblob-4.2 {

   272   set rc [catch {

   273     set ::blob [db incrblob blobs blue 1]

   274   } msg ] 

   275   list $rc $msg

   276 } {1 {no such column: "blue"}}

   277 do_test incrblob-4.3 {

   278   set rc [catch {

   279     set ::blob [db incrblob nosuchtable blue 1]

   280   } msg ]

   281   list $rc $msg

   282 } {1 {no such table: main.nosuchtable}}

   283 do_test incrblob-4.4 {

   284   set rc [catch {

   285     set ::blob [db incrblob nosuchdb blobs v 1]

   286   } msg ] 

   287   list $rc $msg

   288 } {1 {no such table: nosuchdb.blobs}}

   289 

   290 do_test incrblob-4.5 {

   291   set rc [catch {

   292     set ::blob [db incrblob blobs i 1]

   293   } msg ] 

   294   list $rc $msg

   295 } {1 {cannot open value of type integer}}

   296 do_test incrblob-4.6 {

   297   execsql {

   298     INSERT INTO blobs(k, v, i) VALUES(123, 567.765, NULL);

   299   }

   300   set rc [catch {

   301     set ::blob [db incrblob blobs v 2]

   302   } msg ] 

   303   list $rc $msg

   304 } {1 {cannot open value of type real}}

   305 do_test incrblob-4.7 {

   306   set rc [catch {

   307     set ::blob [db incrblob blobs i 2]

   308   } msg ] 

   309   list $rc $msg

   310 } {1 {cannot open value of type null}}

   311 

   312 do_test incrblob-4.8 {

   313   execsql {

   314     INSERT INTO blobs(k, v, i) VALUES(X'010203040506070809', 'hello', 'world');

   315   }

   316   set rc [catch {

   317     set ::blob [db incrblob blobs k 3]

   318   } msg ] 

   319   list $rc $msg

   320 } {1 {cannot open indexed column for writing}}

   321 

   322 do_test incrblob-4.9.1 {

   323   set rc [catch {

   324     set ::blob [db incrblob -readonly blobs k 3]

   325   } msg]

   326 } {0}

   327 do_test incrblob-4.9.2 {

   328   binary scan [read $::blob] c* c

   329   close $::blob

   330   set c

   331 } {1 2 3 4 5 6 7 8 9}

   332 

   333 do_test incrblob-4.10 {

   334   set ::blob [db incrblob -readonly blobs k 3]

   335   set rc [catch { sqlite3_blob_read $::blob 10 100 } msg]

   336   list $rc $msg

   337 } {1 SQLITE_ERROR}

   338 do_test incrblob-4.10.2 {

   339   close $::blob

   340 } {}

   341 

   342 ifcapable view {

   343   do_test incrblob-4.11 {

   344     execsql { CREATE VIEW blobs_view AS SELECT k, v, i FROM blobs }

   345     set rc [catch { db incrblob blobs_view v 3 } msg]

   346     list $rc $msg

   347   } {1 {cannot open view: blobs_view}}

   348 }

   349 ifcapable vtab {

   350   register_echo_module [sqlite3_connection_pointer db]

   351   do_test incrblob-4.12 {

   352     execsql { CREATE VIRTUAL TABLE blobs_echo USING echo(blobs) }

   353     set rc [catch { db incrblob blobs_echo v 3 } msg]

   354     list $rc $msg

   355   } {1 {cannot open virtual table: blobs_echo}}

   356 }

   357 

   358 

   359 #------------------------------------------------------------------------

   360 # incrblob-5.*: 

   361 #

   362 #     Test that opening a blob in an attached database works.

   363 #

   364 ifcapable attach {

   365   do_test incrblob-5.1 {

   366     file delete -force test2.db test2.db-journal

   367     set ::size [expr [file size [info script]]]

   368     execsql {

   369       ATTACH 'test2.db' AS aux;

   370       CREATE TABLE aux.files(name, text);

   371       INSERT INTO aux.files VALUES('this one', zeroblob($::size));

   372     }

   373     set fd  [db incrblob aux files text 1]

   374     fconfigure $fd -translation binary

   375     set fd2 [open [info script]]

   376     fconfigure $fd2 -translation binary

   377     puts -nonewline $fd [read $fd2]

   378     close $fd

   379     close $fd2

   380     set ::text [db one {select text from aux.files}]

   381     string length $::text

   382   } [file size [info script]]

   383   do_test incrblob-5.2 {

   384     set fd2 [open [info script]]

   385     fconfigure $fd2 -translation binary

   386     set ::data [read $fd2]

   387     close $fd2

   388     set ::data

   389   } $::text

   390 }

   391 

   392 # free memory

   393 unset -nocomplain ::data

   394 unset -nocomplain ::text

   395 

   396 #------------------------------------------------------------------------

   397 # incrblob-6.*: 

   398 #

   399 #     Test that opening a blob for write-access is impossible if

   400 #     another connection has the database RESERVED lock.

   401 #

   402 #     Then test that blob writes that take place inside of a

   403 #     transaction are not visible to external connections until

   404 #     after the transaction is commited and the blob channel 

   405 #     closed.

   406 #

   407 sqlite3_soft_heap_limit 0

   408 do_test incrblob-6.1 {

   409   sqlite3 db2 test.db

   410   execsql {

   411     BEGIN;

   412     INSERT INTO blobs(k, v, i) VALUES('a', 'different', 'connection');

   413   } db2

   414 } {}

   415 do_test incrblob-6.2 {

   416   execsql {

   417     SELECT rowid FROM blobs

   418   }

   419 } {1 2 3}

   420 do_test incrblob-6.3 {

   421   set rc [catch {

   422     db incrblob blobs v 1

   423   } msg]

   424   list $rc $msg

   425 } {1 {database is locked}}

   426 do_test incrblob-6.4 {

   427   set rc [catch {

   428     db incrblob blobs v 3

   429   } msg]

   430   list $rc $msg

   431 } {1 {database is locked}}

   432 do_test incrblob-6.5 {

   433   set ::blob [db incrblob -readonly blobs v 3]

   434   read $::blob

   435 } {hello}

   436 do_test incrblob-6.6 {

   437   close $::blob

   438 } {}

   439 

   440 do_test incrblob-6.7 {

   441   set ::blob [db2 incrblob blobs i 4]

   442   gets $::blob

   443 } {connection}

   444 do_test incrblob-6.8 {

   445   tell $::blob

   446 } {10}

   447 do_test incrblob-6.9 {

   448   seek $::blob 0

   449   puts -nonewline $::blob "invocation"

   450   flush $::blob

   451 } {}

   452 

   453 # At this point rollback or commit should be illegal (because 

   454 # there is an open blob channel).

   455 do_test incrblob-6.10 {

   456   catchsql {

   457     ROLLBACK;

   458   } db2

   459 } {1 {cannot rollback transaction - SQL statements in progress}}

   460 do_test incrblob-6.11 {

   461   catchsql {

   462     COMMIT;

   463   } db2

   464 } {1 {cannot commit transaction - SQL statements in progress}}

   465 

   466 do_test incrblob-6.12 {

   467   execsql {

   468     SELECT * FROM blobs WHERE rowid = 4;

   469   }

   470 } {}

   471 do_test incrblob-6.13 {

   472   close $::blob

   473   execsql {

   474     COMMIT;

   475   } db2

   476 } {}

   477 do_test incrblob-6.14 {

   478   execsql {

   479     SELECT * FROM blobs WHERE rowid = 4;

   480   }

   481 } {a different invocation}

   482 db2 close

   483 sqlite3_soft_heap_limit $soft_limit

   484 

   485 #-----------------------------------------------------------------------

   486 # The following tests verify the behaviour of the incremental IO

   487 # APIs in the following cases:

   488 #

   489 #     7.1 A row that containing an open blob is modified.

   490 #

   491 #     7.2 A CREATE TABLE requires that an overflow page that is part

   492 #         of an open blob is moved.

   493 #

   494 #     7.3 An INCREMENTAL VACUUM moves an overflow page that is part

   495 #         of an open blob.

   496 #

   497 # In the first case above, correct behaviour is for all subsequent

   498 # read/write operations on the blob-handle to return SQLITE_ABORT.

   499 # More accurately, blob-handles are invalidated whenever the table

   500 # they belong to is written to.

   501 #

   502 # The second two cases have no external effect. They are testing

   503 # that the internal cache of overflow page numbers is correctly

   504 # invalidated.

   505 #

   506 do_test incrblob-7.1.0 {

   507   execsql {

   508     BEGIN;

   509     DROP TABLE blobs;

   510     CREATE TABLE t1 (a, b, c, d BLOB);

   511     INSERT INTO t1(a, b, c, d) VALUES(1, 2, 3, 4);

   512     COMMIT;

   513   }

   514 } {}

   515 

   516 foreach {tn arg} {1 "" 2 -readonly} {

   517 

   518   execsql {

   519     UPDATE t1 SET d = zeroblob(10000);

   520   }

   521 

   522   do_test incrblob-7.1.$tn.1 {

   523     set ::b [eval db incrblob $arg t1 d 1]

   524     binary scan [sqlite3_blob_read $::b 5000 5] c* c

   525     set c

   526   } {0 0 0 0 0}

   527   do_test incrblob-7.1.$tn.2 {

   528     execsql {

   529       UPDATE t1 SET d = 15;

   530     }

   531   } {}

   532   do_test incrblob-7.1.$tn.3 {

   533     set rc [catch { sqlite3_blob_read $::b 5000 5 } msg]

   534     list $rc $msg

   535   } {1 SQLITE_ABORT}

   536   do_test incrblob-7.1.$tn.4 {

   537     execsql {

   538       SELECT d FROM t1;

   539     }

   540   } {15}

   541   do_test incrblob-7.1.$tn.5 {

   542     set rc [catch { close $::b } msg]

   543     list $rc $msg

   544   } {0 {}}

   545   do_test incrblob-7.1.$tn.6 {

   546     execsql {

   547       SELECT d FROM t1;

   548     }

   549   } {15}

   550 

   551 }

   552 

   553 set fd [open [info script]]

   554 fconfigure $fd -translation binary

   555 set ::data [read $fd 14000]

   556 close $fd

   557 

   558 db close

   559 file delete -force test.db test.db-journal

   560 sqlite3 db test.db

   561 

   562 do_test incrblob-7.2.1 {

   563   execsql {

   564     PRAGMA auto_vacuum = "incremental";

   565     CREATE TABLE t1(a INTEGER PRIMARY KEY, b);        -- root@page3

   566     INSERT INTO t1 VALUES(123, $::data);

   567   }

   568   set ::b [db incrblob -readonly t1 b 123]

   569   fconfigure $::b -translation binary

   570   read $::b

   571 } $::data

   572 do_test incrblob-7.2.2 {

   573   execsql {

   574     CREATE TABLE t2(a INTEGER PRIMARY KEY, b);        -- root@page4

   575   }

   576   seek $::b 0

   577   read $::b

   578 } $::data

   579 do_test incrblob-7.2.3 {

   580   close $::b

   581   execsql {

   582     SELECT rootpage FROM sqlite_master;

   583   }

   584 } {3 4}

   585 

   586 set ::otherdata "[string range $::data 0 1000][string range $::data 1001 end]"

   587 do_test incrblob-7.3.1 {

   588   execsql {

   589     INSERT INTO t2 VALUES(456, $::otherdata);

   590   }

   591   set ::b [db incrblob -readonly t2 b 456]

   592   fconfigure $::b -translation binary

   593   read $::b

   594 } $::otherdata

   595 do_test incrblob-7.3.2 {

   596   expr [file size test.db]/1024

   597 } 30

   598 do_test incrblob-7.3.3 {

   599   execsql {

   600     DELETE FROM t1 WHERE a = 123;

   601     PRAGMA INCREMENTAL_VACUUM(0);

   602   }

   603   seek $::b 0

   604   read $::b

   605 } $::otherdata

   606 

   607 # Attempt to write on a read-only blob.  Make sure the error code

   608 # gets set.  Ticket #2464.

   609 #

   610 do_test incrblob-7.4 {

   611   set rc [catch {sqlite3_blob_write $::b 10 HELLO} msg]

   612   lappend rc $msg

   613 } {1 SQLITE_READONLY}

   614 do_test incrblob-7.5 {

   615   sqlite3_errcode db

   616 } {SQLITE_READONLY}

   617 do_test incrblob-7.6 {

   618   sqlite3_errmsg db

   619 } {attempt to write a readonly database}

   620 

   621 finish_test