SQLite

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This header file defines the interface that the SQLite library
** presents to client programs.
**
** @(#) $Id: sqlite.h.in,v 1.204 2007/05/03 16:31:26 danielk1977 Exp $
*/
#ifndef _SQLITE3_H_
#define _SQLITE3_H_
#include <stdarg.h>     /* Needed for the definition of va_list */

/*
** Make sure we can call this stuff from C++.

**
** When the virtual-table mechanism stablizes, we will declare the
** interface fixed, support it indefinitely, and remove this comment.
**
****** EXPERIMENTAL - subject to change without notice **************
*/

/*
** An instance of the following opaque structure is used to 
** represent an open blob handle.
*/
typedef struct sqlite3_blob sqlite3_blob;

/*
** Open a handle to the blob located in row iRow,, column zColumn, 
** table zTable in database zDb. i.e. the same blob that would
** be selected by:
**
**     "SELECT zColumn FROM zDb.zTable WHERE rowid = iRow;
**
** If the flags parameter is non-zero, the blob is opened for 
** read and write access. If it is zero, the blob is opened for read 
** access.
**
** On success, SQLITE_OK is returned and the new blob-handle is
** written to *ppBlob. Otherwise an error code is returned and 
** any value written to *ppBlob should not be used by the caller.
** This function sets the database-handle error code and message
** accessible via sqlite3_errcode() and sqlite3_errmsg().
*/
int sqlite3_blob_open(
  sqlite3*,
  const char *zDb,
  const char *zTable,
  const char *zColumn,
  sqlite_int64 iRow,
  int flags,
  sqlite3_blob **ppBlob
);

/*
** Close an open blob handle.
*/
int sqlite3_blob_close(sqlite3_blob *);

/*
** Return the size in bytes of the blob accessible via the open 
** blob-handle passed as an argument.
*/
int sqlite3_blob_bytes(sqlite3_blob *);

/*
** This function is used to read data from an open blob-handle into
** a caller supplied buffer. n bytes of data are copied into buffer
** z from the open blob, starting at offset iOffset.
**
** On success, SQLITE_OK is returned. Otherwise, an SQLite error 
** code.
*/
int sqlite3_blob_read(sqlite3_blob *, void *z, int n, int iOffset);

/*
** This function is used to write data from an open blob-handle into
** a user supplied buffer. n bytes of data are copied from the buffer
** pointed to by z into the open blob, starting at offset iOffset.
**
** If the blob-handle passed as the first argument was not opened for
** writing (the flags parameter to sqlite3_blob_open was zero), this
** function returns SQLITE_READONLY.
**
** This function may only modify the contents of the blob, it is
** not possible to increase the size of a blob using this API. If
** offset iOffset is less than n bytes from the end of the blob, 
** SQLITE_ERROR is returned and no data is written.
**
** On success, SQLITE_OK is returned. Otherwise, an SQLite error 
** code. If an error occurs, this function sets the 
*/
int sqlite3_blob_write(sqlite3_blob *, const void *z, int n, int iOffset);


/*
** Undo the hack that converts floating point types to integer for
** builds on processors without floating point support.
*/
#ifdef SQLITE_OMIT_FLOATING_POINT
# undef double
#endif

#ifdef __cplusplus
}  /* End of the 'extern "C"' block */
#endif
#endif

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** A TCL Interface to SQLite.  Append this file to sqlite3.c and
** compile the whole thing to build a TCL-enabled version of SQLite.
**
** $Id: tclsqlite.c,v 1.182 2007/05/03 16:31:26 danielk1977 Exp $
*/
#include "tcl.h"
#include <errno.h>

/*
** Some additional include files are needed if this file is not
** appended to the amalgamation.

*/
static int createIncrblobChannel(
  Tcl_Interp *interp, 
  SqliteDb *pDb, 
  const char *zDb,
  const char *zTable, 
  const char *zColumn, 
  sqlite_int64 iRow,
  int isReadonly
){
  IncrblobChannel *p;
  sqlite3 *db = pDb->db;
  sqlite3_blob *pBlob;
  int rc;
  int flags = TCL_READABLE|(isReadonly ? 0 : TCL_WRITABLE);

  /* This variable is used to name the channels: "incrblob_[incr count]" */
  static int count = 0;
  char zChannel[64];

  rc = sqlite3_blob_open(db, zDb, zTable, zColumn, iRow, !isReadonly, &pBlob);
  if( rc!=SQLITE_OK ){
    Tcl_SetResult(interp, (char *)sqlite3_errmsg(pDb->db), TCL_VOLATILE);
    return TCL_ERROR;
  }

  p = (IncrblobChannel *)Tcl_Alloc(sizeof(IncrblobChannel));
  p->iSeek = 0;

      /* Must flush any cached statements */
      flushStmtCache( pDb );
    }
    break;
  }

  /*
  **     $db incrblob ?-readonly? ?DB? TABLE COLUMN ROWID
  */
  case DB_INCRBLOB: {
    int isReadonly = 0;
    const char *zDb = "main";
    const char *zTable;
    const char *zColumn;
    sqlite_int64 iRow;

    /* Check for the -readonly option */
    if( objc>3 && strcmp(Tcl_GetString(objv[2]), "-readonly")==0 ){
      isReadonly = 1;
    }

    if( objc!=(5+isReadonly) && objc!=(6+isReadonly) ){
      Tcl_WrongNumArgs(interp, 2, objv, "?-readonly? ?DB? TABLE COLUMN ROWID");
      return TCL_ERROR;
    }

    if( objc==(6+isReadonly) ){
      zDb = Tcl_GetString(objv[2]);
    }
    zTable = Tcl_GetString(objv[objc-3]);
    zColumn = Tcl_GetString(objv[objc-2]);
    rc = Tcl_GetWideIntFromObj(interp, objv[objc-1], &iRow);

    if( rc==TCL_OK ){
      rc = createIncrblobChannel(
          interp, pDb, zDb, zTable, zColumn, iRow, isReadonly
      );
    }
    break;
  }

  /*
  **     $db interrupt
  **

/*
** 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: vdbeblob.c,v 1.4 2007/05/03 16:31:26 danielk1977 Exp $
*/

#include "sqliteInt.h"
#include "vdbeInt.h"

#ifndef SQLITE_OMIT_INCRBLOB

  const char *zDb,
  const char *zTable,
  const char *zColumn,
  sqlite_int64 iRow,
  int flags,              /* True -> read/write access, false -> read-only */
  sqlite3_blob **ppBlob
){

  int nAttempt = 0;
  int iCol;               /* Index of zColumn in row-record */

  /* This VDBE program seeks a btree cursor to the identified 
  ** db/table/row entry. The reason for using a vdbe program instead
  ** of writing code to use the b-tree layer directly is that the
  ** vdbe program will take advantage of the various transaction,

    {OP_Column, 0, 0, 0},          /* 8  */
    {OP_Callback, 0, 0, 0},        /* 9  */
    {OP_Close, 0, 0, 0},           /* 10 */
    {OP_Halt, 0, 0, 0},            /* 11 */
  };

  Vdbe *v = 0;
  int rc = SQLITE_OK;
  char zErr[128] = {0};

  do {
    Parse sParse;
    Table *pTab;

    memset(&sParse, 0, sizeof(Parse));
    sParse.db = db;

    rc = sqlite3SafetyOn(db);
    if( rc!=SQLITE_OK ){
      return rc;
    }

    pTab = sqlite3LocateTable(&sParse, zTable, zDb);
    if( !pTab ){
      if( sParse.zErrMsg ){
        sqlite3_snprintf(sizeof(zErr), zErr, "%s", sParse.zErrMsg);
        zErr[sizeof(zErr)-1] = '\0';
      }
      sqliteFree(sParse.zErrMsg);
      rc = SQLITE_ERROR;
      sqlite3SafetyOff(db);
      goto blob_open_out;
    }

    /* Now search pTab for the exact column. */
    for(iCol=0; iCol < pTab->nCol; iCol++) {
      if( sqlite3StrICmp(pTab->aCol[iCol].zName, zColumn)==0 ){
        break;
      }
    }
    if( iCol==pTab->nCol ){
      sprintf(zErr, "no such column: \"%s\"", zColumn);

      rc = SQLITE_ERROR;
      sqlite3SafetyOff(db);
      goto blob_open_out;
    }

    v = sqlite3VdbeCreate(db);
    if( v ){

    }

    sqlite3_bind_int64((sqlite3_stmt *)v, 1, iRow);
    rc = sqlite3_step((sqlite3_stmt *)v);
    if( rc!=SQLITE_ROW ){
      nAttempt++;
      rc = sqlite3_finalize((sqlite3_stmt *)v);
      sprintf(zErr, "no such rowid: %lld", iRow);
      v = 0;
    }
  } while( nAttempt<5 && rc==SQLITE_SCHEMA );

  if( rc==SQLITE_ROW ){
    /* The row-record has been opened successfully. Check that the
    ** column in question contains text or a blob. If it contains

    pBlob->pCsr =  v->apCsr[0]->pCursor;
    sqlite3BtreeCacheOverflow(pBlob->pCsr);
    pBlob->pStmt = (sqlite3_stmt *)v;
    pBlob->iOffset = v->apCsr[0]->aOffset[iCol];
    pBlob->nByte = sqlite3VdbeSerialTypeLen(type);
    *ppBlob = (sqlite3_blob *)pBlob;
    rc = SQLITE_OK;

  }else if( rc==SQLITE_OK ){
    rc = SQLITE_ERROR;

  }

blob_open_out:
  if( rc!=SQLITE_OK || sqlite3MallocFailed() ){
    sqlite3_finalize((sqlite3_stmt *)v);
  }
  sqlite3Error(db, rc, zErr);
  return sqlite3ApiExit(db, rc);
}

/*
** Close a blob handle.
*/
int sqlite3_blob_close(sqlite3_blob *pBlob){

# 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.5 2007/05/03 16:31:26 danielk1977 Exp $
#

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

do_test incrblob-1.1 {
  execsql {

} {....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] {

  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]

  do_test incrblob-2.$AutoVacuumMode.2 {
    execsql {

    # 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.
#
# TODO: The following test only tests the tcl interface, not the
# underlying sqlite3 interface. Need to find some other method
# to call sqlite3_blob_write() on a readonly 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]
  list $rc $msg
} "1 {channel \"$::blob\" wasn't opened for writing}"

#------------------------------------------------------------------------
# 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.
#
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}}

finish_test

# 2007 May 3
#
# 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: tkt2332.test,v 1.2 2007/05/03 16:31:26 danielk1977 Exp $
#

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

do_test tkt2332.1 {
  execsql {

  do_test tkt2332.$Len.5 {
    lindex [execsql {SELECT v FROM blobs WHERE k = $::iKey}] 0
  } $::blobstr

  incr ::iKey
}

# Free memory:
unset ::blobstr

finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The focus
# of this file is testing the interaction of SQLite manifest types
# with Tcl dual-representations.
#
# $Id: types3.test,v 1.6 2007/05/03 16:31:26 danielk1977 Exp $
#

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

# A variable with only a string representation comes in as TEXT
do_test types3-1.1 {
  set V {}
  append V {}
  concat [tcl_variable_type V] [execsql {SELECT typeof(:V)}]
} {string text}

# A variable with an integer representation comes in as INTEGER
do_test types3-1.2 {
  set V [expr {int(1+2)}]
  concat [tcl_variable_type V] [execsql {SELECT typeof(:V)}]
} {int integer}
do_test types3-1.3 {
  set V [expr {1+123456789012345}]
  concat [tcl_variable_type V] [execsql {SELECT typeof(:V)}]
} {wideInt integer}