test/incrblob.test

# 2007 May 1
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# $Id: incrblob.test,v 1.10 2007/05/07 09:32:45 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable {!autovacuum || !pragma || !incrblob} {
  finish_test
  return
}

do_test incrblob-1.1 {
  execsql {
    CREATE TABLE blobs(k PRIMARY KEY, v BLOB);
    INSERT INTO blobs VALUES('one', X'0102030405060708090A');
    INSERT INTO blobs VALUES('two', X'0A090807060504030201');
  }
} {}

do_test incrblob-1.2.1 {
  set ::blob [db incrblob blobs v 1]
} {incrblob_1}
do_test incrblob-1.2.2 {
  binary scan [read $::blob] c* data
  set data
} {1 2 3 4 5 6 7 8 9 10}
do_test incrblob-1.2.3 {
  seek $::blob 0
  puts -nonewline $::blob "1234567890"
  flush $::blob
} {}
do_test incrblob-1.2.4 {
  seek $::blob 0
  binary scan [read $::blob] c* data
  set data
} {49 50 51 52 53 54 55 56 57 48}
do_test incrblob-1.2.5 {
  close $::blob
} {}
do_test incrblob-1.2.6 {
  execsql {
    SELECT v FROM blobs WHERE rowid = 1;
  }
} {1234567890}

#--------------------------------------------------------------------
# Test cases incrblob-1.3.X check that it is possible to read and write
# regions of a blob that lie on overflow pages.
#
do_test incrblob-1.3.1 {
  set ::str "[string repeat . 10000]"
  execsql {
    INSERT INTO blobs(rowid, k, v) VALUES(3, 'three', $::str);
  }
} {}

do_test incrblob-1.3.2 {
  set ::blob [db incrblob blobs v 3]
  seek $::blob 8500
  read $::blob 10
} {..........}
do_test incrblob-1.3.3 {
  seek $::blob 8500
  puts -nonewline $::blob 1234567890
} {}
do_test incrblob-1.3.4 {
  seek $::blob 8496
  read $::blob 10
} {....123456}
do_test incrblob-1.3.10 {
  close $::blob
} {}


#------------------------------------------------------------------------
# incrblob-2.*: 
#
# Test that the following operations use ptrmap pages to reduce
# unnecessary reads:
#
#     * Reading near the end of a blob,
#     * Writing near the end of a blob, and
#     * SELECT a column value that is located on an overflow page.
#
proc nRead {db} {
  set bt [btree_from_db $db]
  array set stats [btree_pager_stats $bt]
  return $stats(read)
}
proc nWrite {db} {
  set bt [btree_from_db $db]
  array set stats [btree_pager_stats $bt]
  return $stats(write)
}

foreach AutoVacuumMode [list 0 1] {

  if {$AutoVacuumMode>0} {
    ifcapable !autovacuum {
      break
    }
  }

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

  sqlite3 db test.db
  execsql "PRAGMA auto_vacuum = $AutoVacuumMode"

  do_test incrblob-2.$AutoVacuumMode.1 {
    set ::str [string repeat abcdefghij 2900]
    execsql {
      BEGIN;
      CREATE TABLE blobs(k PRIMARY KEY, v BLOB, i INTEGER);
      DELETE FROM blobs;
      INSERT INTO blobs VALUES('one', $::str || randstr(500,500), 45);
      COMMIT;
    }
    expr [file size test.db]/1024
  } [expr 31 + $AutoVacuumMode]

  ifcapable autovacuum {
    do_test incrblob-2.$AutoVacuumMode.2 {
      execsql {
        PRAGMA auto_vacuum;
      }
    } $AutoVacuumMode
  }

  do_test incrblob-2.$AutoVacuumMode.3 {
    # Open and close the db to make sure the page cache is empty.
    db close
    sqlite3 db test.db
  
    # Read the last 20 bytes of the blob via a blob handle.
    set ::blob [db incrblob blobs v 1]
    seek $::blob -20 end
    set ::fragment [read $::blob]
    close $::blob
  
    # If the database is not in auto-vacuum mode, the whole of
    # the overflow-chain must be scanned. In auto-vacuum mode,
    # sqlite uses the ptrmap pages to avoid reading the other pages.
    #
    nRead db
  } [expr $AutoVacuumMode ? 4 : 30]

  do_test incrblob-2.$AutoVacuumMode.4 {
    string range [db one {SELECT v FROM blobs}] end-19 end
  } $::fragment

  do_test incrblob-2.$AutoVacuumMode.5 {
    # Open and close the db to make sure the page cache is empty.
    db close
    sqlite3 db test.db
  
    # Write the second-to-last 20 bytes of the blob via a blob handle.
    #
    set ::blob [db incrblob blobs v 1]
    seek $::blob -40 end
    puts -nonewline $::blob "1234567890abcdefghij"
    flush $::blob
  
    # If the database is not in auto-vacuum mode, the whole of
    # the overflow-chain must be scanned. In auto-vacuum mode,
    # sqlite uses the ptrmap pages to avoid reading the other pages.
    #
    nRead db
  } [expr $AutoVacuumMode ? 4 : 30]

  # Pages 1 (the write-counter) and 32 (the blob data) were written.
  do_test incrblob-2.$AutoVacuumMode.6 {
    close $::blob
    nWrite db
  } 2

  do_test incrblob-2.$AutoVacuumMode.7 {
    string range [db one {SELECT v FROM blobs}] end-39 end-20
  } "1234567890abcdefghij"

  do_test incrblob-2.$AutoVacuumMode.8 {
    # Open and close the db to make sure the page cache is empty.
    db close
    sqlite3 db test.db

    execsql { SELECT i FROM blobs } 
  } {45}

  do_test incrblob-2.$AutoVacuumMode.9 {
    nRead db
  } [expr $AutoVacuumMode ? 4 : 30]
}

#------------------------------------------------------------------------
# incrblob-3.*: 
#
# Test the outcome of trying to write to a read-only blob handle.
#
do_test incrblob-3.1 {
  set ::blob [db incrblob -readonly blobs v 1]
  seek $::blob -40 end
  read $::blob 20
} "1234567890abcdefghij"
do_test incrblob-3.2 {
  seek $::blob 0
  set rc [catch {
    puts -nonewline $::blob "helloworld"
  } msg]
  close $::blob
  list $rc $msg
} "1 {channel \"$::blob\" wasn't opened for writing}"

do_test incrblob-3.3 {
  set ::blob [db incrblob -readonly blobs v 1]
  seek $::blob -40 end
  read $::blob 20
} "1234567890abcdefghij"
do_test incrblob-3.4 {
  set rc [catch {
    sqlite3_blob_write $::blob 20 "qwertyuioplkjhgfds" 
  } msg]
  list $rc $msg
} {1 SQLITE_READONLY}
catch {close $::blob}

#------------------------------------------------------------------------
# incrblob-4.*: 
#
# Try a couple of error conditions:
#
#     4.1 - Attempt to open a row that does not exist.
#     4.2 - Attempt to open a column that does not exist.
#     4.3 - Attempt to open a table that does not exist.
#     4.4 - Attempt to open a database that does not exist.
#
#     4.5 - Attempt to open an integer
#     4.6 - Attempt to open a real value
#     4.7 - Attempt to open an SQL null
#
#     4.8 - Attempt to open an indexed column for writing
#     4.9 - Attempt to open an indexed column for reading (this works)
#
do_test incrblob-4.1 {
  set rc [catch {
    set ::blob [db incrblob blobs v 2]
  } msg ] 
  list $rc $msg
} {1 {no such rowid: 2}}
do_test incrblob-4.2 {
  set rc [catch {
    set ::blob [db incrblob blobs blue 1]
  } msg ] 
  list $rc $msg
} {1 {no such column: "blue"}}
do_test incrblob-4.3 {
  set rc [catch {
    set ::blob [db incrblob nosuchtable blue 1]
  } msg ]
  list $rc $msg
} {1 {no such table: main.nosuchtable}}
do_test incrblob-4.4 {
  set rc [catch {
    set ::blob [db incrblob nosuchdb blobs v 1]
  } msg ] 
  list $rc $msg
} {1 {no such table: nosuchdb.blobs}}

do_test incrblob-4.5 {
  set rc [catch {
    set ::blob [db incrblob blobs i 1]
  } msg ] 
  list $rc $msg
} {1 {cannot open value of type integer}}
do_test incrblob-4.6 {
  execsql {
    INSERT INTO blobs(k, v, i) VALUES(123, 567.765, NULL);
  }
  set rc [catch {
    set ::blob [db incrblob blobs v 2]
  } msg ] 
  list $rc $msg
} {1 {cannot open value of type real}}
do_test incrblob-4.7 {
  set rc [catch {
    set ::blob [db incrblob blobs i 2]
  } msg ] 
  list $rc $msg
} {1 {cannot open value of type null}}

do_test incrblob-4.8 {
  execsql {
    INSERT INTO blobs(k, v, i) VALUES(X'010203040506070809', 'hello', 'world');
  }
  set rc [catch {
    set ::blob [db incrblob blobs k 3]
  } msg ] 
  list $rc $msg
} {1 {cannot open indexed column for writing}}

do_test incrblob-4.9.1 {
  set rc [catch {
    set ::blob [db incrblob -readonly blobs k 3]
  } msg]
} {0}
do_test incrblob-4.9.2 {
  binary scan [read $::blob] c* c
  close $::blob
  set c
} {1 2 3 4 5 6 7 8 9}

#------------------------------------------------------------------------
# incrblob-5.*: 
#
#     Test that opening a blob in an attached database works.
#
do_test incrblob-5.1 {
  file delete -force test2.db test2.db-journal
  set ::size [expr [file size [info script]]]
  execsql {
    ATTACH 'test2.db' AS aux;
    CREATE TABLE aux.files(name, text);
    INSERT INTO aux.files VALUES('this one', zeroblob($::size));
  }
  set fd  [db incrblob aux files text 1]
  set fd2 [open [info script]]
  puts -nonewline $fd [read $fd2]
  close $fd
  close $fd2
  set ::text [db one {select text from aux.files}]
  string length $::text
} [file size [info script]]
do_test incrblob-5.2 {
  set fd2 [open [info script]]
  set ::data [read $fd2]
  close $fd2
  set ::data
} $::text

# free memory
unset ::data
unset ::text

#------------------------------------------------------------------------
# incrblob-6.*: 
#
#     Test that opening a blob for write-access is impossible if
#     another connection has the database RESERVED lock.
#
#     Then test that blob writes that take place inside of a
#     transaction are not visible to external connections until
#     after the transaction is commited and the blob channel 
#     closed.
#
do_test incrblob-6.1 {
  sqlite3 db2 test.db
  execsql {
    BEGIN;
    INSERT INTO blobs(k, v, i) VALUES('a', 'different', 'connection');
  } db2
} {}
do_test incrblob-6.2 {
  execsql {
    SELECT rowid FROM blobs
  }
} {1 2 3}
do_test incrblob-6.3 {
  set rc [catch {
    db incrblob blobs v 1
  } msg]
  list $rc $msg
} {1 {database is locked}}
do_test incrblob-6.4 {
  set rc [catch {
    db incrblob blobs v 3
  } msg]
  list $rc $msg
} {1 {database is locked}}
do_test incrblob-6.5 {
  set ::blob [db incrblob -readonly blobs v 3]
  read $::blob
} {hello}
do_test incrblob-6.6 {
  close $::blob
} {}

do_test incrblob-6.7 {
  set ::blob [db2 incrblob blobs i 4]
  gets $::blob
} {connection}
do_test incrblob-6.8 {
  tell $::blob
} {10}
do_test incrblob-6.9 {
  seek $::blob 0
  puts -nonewline $::blob "invocation"
  flush $::blob
} {}

# At this point rollback or commit should be illegal (because 
# there is an open blob channel).
do_test incrblob-6.10 {
  catchsql {
    ROLLBACK;
  } db2
} {1 {cannot rollback transaction - SQL statements in progress}}
do_test incrblob-6.11 {
  catchsql {
    COMMIT;
  } db2
} {1 {cannot commit transaction - SQL statements in progress}}

do_test incrblob-6.12 {
  execsql {
    SELECT * FROM blobs WHERE rowid = 4;
  }
} {}
do_test incrblob-6.13 {
  close $::blob
  execsql {
    COMMIT;
  } db2
} {}
do_test incrblob-6.14 {
  execsql {
    SELECT * FROM blobs WHERE rowid = 4;
  }
} {a different invocation}
db2 close

#-----------------------------------------------------------------------
# The following tests verify the behaviour of the incremental IO
# APIs in the following cases:
#
#     7.1 A row that containing an open blob is modified.
#
#     7.2 A CREATE TABLE requires that an overflow page that is part
#         of an open blob is moved.
#
#     7.3 An INCREMENTAL VACUUM moves an overflow page that is part
#         of an open blob.
#
# In the first case above, correct behaviour is for all subsequent
# read/write operations on the blob-handle to return SQLITE_ABORT.
# More accurately, blob-handles are invalidated whenever the table
# they belong to is written to.
#
# The second two cases have no external effect. They are testing
# that the internal cache of overflow page numbers is correctly
# invalidated.
#
do_test incrblob-7.1.0 {
  execsql {
    BEGIN;
    DROP TABLE blobs;
    CREATE TABLE t1 (a, b, c, d BLOB);
    INSERT INTO t1(a, b, c, d) VALUES(1, 2, 3, 4);
    COMMIT;
  }
} {}

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

  execsql {
    UPDATE t1 SET d = zeroblob(10000);
  }

  do_test incrblob-7.1.$tn.1 {
    set ::b [eval db incrblob $arg t1 d 1]
    binary scan [sqlite3_blob_read $::b 5000 5] c* c
    set c
  } {0 0 0 0 0}
  do_test incrblob-7.1.$tn.2 {
    execsql {
      UPDATE t1 SET d = 15;
    }
  } {}
  do_test incrblob-7.1.$tn.3 {
    set rc [catch { sqlite3_blob_read $::b 5000 5 } msg]
    list $rc $msg
  } {1 SQLITE_ABORT}
  do_test incrblob-7.1.$tn.4 {
    execsql {
      SELECT d FROM t1;
    }
  } {15}
  do_test incrblob-7.1.$tn.5 {
    set rc [catch { close $::b } msg]
    list $rc $msg
  } {0 {}}
  do_test incrblob-7.1.$tn.6 {
    execsql {
      SELECT d FROM t1;
    }
  } {15}

}

set fd [open [info script]]
set ::data [read $fd]
close $fd

db close
file delete -force test.db test.db-journal
sqlite3 db test.db

do_test incrblob-7.2.1 {
  execsql {
    PRAGMA auto_vacuum = "incremental";
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);        -- root@page3
    INSERT INTO t1 VALUES(123, $::data);
  }
  set ::b [db incrblob -readonly t1 b 123]
  read $::b
} $::data
do_test incrblob-7.2.2 {
  execsql {
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);        -- root@page4
  }
  seek $::b 0
  read $::b
} $::data
do_test incrblob-7.2.3 {
  close $::b
  execsql {
    SELECT rootpage FROM sqlite_master;
  }
} {3 4}

set ::otherdata "[string range $::data 0 1000][string range $::data 1001 end]"
do_test incrblob-7.3.1 {
  execsql {
    INSERT INTO t2 VALUES(456, $::otherdata);
  }
  set ::b [db incrblob -readonly t2 b 456]
  read $::b
} $::otherdata
do_test incrblob-7.3.2 {
  expr [file size test.db]/1024
} 30
do_test incrblob-7.3.3 {
  execsql {
    DELETE FROM t1 WHERE a = 123;
    PRAGMA INCREMENTAL_VACUUM(0);
  }
  seek $::b 0
  read $::b
} $::otherdata

finish_test