blob: 9c9389eb4c4bd450907afd96818aeed2784ace48 [file] [log] [blame]
# 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.24 2009/06/19 22:23:42 drh 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]
string match incrblob_* $::blob
} {1}
unset -nocomplain data
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]
db_enter $db
array set stats [btree_pager_stats $bt]
db_leave $db
return $stats(read)
}
proc nWrite {db} {
set bt [btree_from_db $db]
db_enter $db
array set stats [btree_pager_stats $bt]
db_leave $db
return $stats(write)
}
sqlite3_soft_heap_limit 0
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]
}
sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit)
#------------------------------------------------------------------------
# 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)
#
# 4.11 - Attempt to open a column of a view.
# 4.12 - Attempt to open a column of a virtual table.
#
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.1 {
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.8.2 {
execsql {
CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
INSERT INTO t3 VALUES(1, 2);
}
set rc [catch {
set ::blob [db incrblob -readonly t3 a 1]
} msg ]
list $rc $msg
} {1 {cannot open value of type null}}
do_test incrblob-4.8.3 {
set rc [catch {
set ::blob [db incrblob -readonly t3 rowid 1]
} msg ]
list $rc $msg
} {1 {no such column: "rowid"}}
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}
do_test incrblob-4.10 {
set ::blob [db incrblob -readonly blobs k 3]
set rc [catch { sqlite3_blob_read $::blob 10 100 } msg]
list $rc $msg
} {1 SQLITE_ERROR}
do_test incrblob-4.10.2 {
close $::blob
} {}
ifcapable view {
do_test incrblob-4.11 {
execsql { CREATE VIEW blobs_view AS SELECT k, v, i FROM blobs }
set rc [catch { db incrblob blobs_view v 3 } msg]
list $rc $msg
} {1 {cannot open view: blobs_view}}
}
ifcapable vtab {
register_echo_module [sqlite3_connection_pointer db]
do_test incrblob-4.12 {
execsql { CREATE VIRTUAL TABLE blobs_echo USING echo(blobs) }
set rc [catch { db incrblob blobs_echo v 3 } msg]
list $rc $msg
} {1 {cannot open virtual table: blobs_echo}}
}
#------------------------------------------------------------------------
# incrblob-5.*:
#
# Test that opening a blob in an attached database works.
#
ifcapable attach {
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]
fconfigure $fd -translation binary
set fd2 [open [info script]]
fconfigure $fd2 -translation binary
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]]
fconfigure $fd2 -translation binary
set ::data [read $fd2]
close $fd2
set ::data
} $::text
}
# free memory
unset -nocomplain ::data
unset -nocomplain ::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.
#
# This test does not work with the "memsubsys1" configuration.
# Permutation memsubsys1 configures a very small static allocation
# for use as page-cache memory. This causes SQLite to upgrade
# to an exclusive lock when writing earlier than usual, which
# makes some of these tests fail.
#
sqlite3_soft_heap_limit 0
if {[permutation] != "memsubsys1"} {
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 should be illegal (because
# there is an open blob channel). But commit is also illegal because
# the open blob is read-write.
#
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
} {}
do_test incrblob-6.14 {
catchsql {
COMMIT;
} db2
} {0 {}}
do_test incrblob-6.15 {
execsql {
SELECT * FROM blobs WHERE rowid = 4;
}
} {a different invocation}
db2 close
}
sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit)
#-----------------------------------------------------------------------
# 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]]
fconfigure $fd -translation binary
set ::data [read $fd 14000]
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]
fconfigure $::b -translation binary
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]
fconfigure $::b -translation binary
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
# Attempt to write on a read-only blob. Make sure the error code
# gets set. Ticket #2464.
#
do_test incrblob-7.4 {
set rc [catch {sqlite3_blob_write $::b 10 HELLO} msg]
lappend rc $msg
} {1 SQLITE_READONLY}
do_test incrblob-7.5 {
sqlite3_errcode db
} {SQLITE_READONLY}
do_test incrblob-7.6 {
sqlite3_errmsg db
} {attempt to write a readonly database}
# Test that if either the "offset" or "amount" arguments to
# sqlite3_blob_write() are less than zero, SQLITE_ERROR is returned.
#
do_test incrblob-8.1 {
execsql { INSERT INTO t1 VALUES(314159, 'sqlite') }
set ::b [db incrblob t1 b 314159]
fconfigure $::b -translation binary
set rc [catch {sqlite3_blob_write $::b 10 HELLO -1} msg]
lappend rc $msg
} {1 SQLITE_ERROR}
do_test incrblob-8.2 {
sqlite3_errcode db
} {SQLITE_ERROR}
do_test incrblob-8.3 {
set rc [catch {sqlite3_blob_write $::b -1 HELLO 5} msg]
lappend rc $msg
} {1 SQLITE_ERROR}
do_test incrblob-8.4 {
sqlite3_errcode db
} {SQLITE_ERROR}
do_test incrblob-8.5 {
execsql {SELECT b FROM t1 WHERE a = 314159}
} {sqlite}
do_test incrblob-8.6 {
set rc [catch {sqlite3_blob_write $::b 0 etilqs 6} msg]
lappend rc $msg
} {0 {}}
do_test incrblob-8.7 {
execsql {SELECT b FROM t1 WHERE a = 314159}
} {etilqs}
# The following test case exposes an instance in the blob code where
# an error message was set using a call similar to sqlite3_mprintf(zErr),
# where zErr is an arbitrary string. This is no good if the string contains
# characters that can be mistaken for printf() formatting directives.
#
do_test incrblob-9.1 {
list [catch { db incrblob t1 "A tricky column name %s%s" 1 } msg] $msg
} {1 {no such column: "A tricky column name %s%s"}}
finish_test