# 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.21 2008/09/11 11:28:00 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]

  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 $soft_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 {

  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}

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.

#

sqlite3_soft_heap_limit 0

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

sqlite3_soft_heap_limit $soft_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}

finish_test