SQLite

/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.817 2009/01/02 17:33:46 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build

    Select *pSelect;  /* A SELECT statement used in place of a table name */
    u8 isPopulated;   /* Temporary table associated with SELECT is populated */
    u8 jointype;      /* Type of join between this able and the previous */
    u8 notIndexed;    /* True if there is a NOT INDEXED clause */
    int iCursor;      /* The VDBE cursor number used to access this table */
    Expr *pOn;        /* The ON clause of a join */
    IdList *pUsing;   /* The USING clause of a join */
    Bitmask colUsed;  /* Bit N (1<<N) set if column N of pTab is used */
    char *zIndex;     /* Identifier from "INDEXED BY <zIndex>" clause */
    Index *pIndex;    /* Index structure corresponding to zIndex, if any */
  } a[1];             /* One entry for each identifier on the list */
};

/*
** Permitted values of the SrcList.a.jointype field

**    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.233 2009/01/02 17:33:46 danielk1977 Exp $
*/
#include "tcl.h"
#include <errno.h>

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

  Tcl_Obj *pCollateNeeded;   /* Collation needed script */
  SqlPreparedStmt *stmtList; /* List of prepared statements*/
  SqlPreparedStmt *stmtLast; /* Last statement in the list */
  int maxStmt;               /* The next maximum number of stmtList */
  int nStmt;                 /* Number of statements in stmtList */
  IncrblobChannel *pIncrblob;/* Linked list of open incrblob channels */
  int nStep, nSort;          /* Statistics for most recent operation */
  int nTransaction;          /* Number of nested [transaction] methods */
};

struct IncrblobChannel {
  sqlite3_blob *pBlob;      /* sqlite3 blob handle */
  SqliteDb *pDb;            /* Associated database connection */
  int iSeek;                /* Current seek offset */
  Tcl_Channel channel;      /* Channel identifier */

  ** throws an exception.  Or if no new transation was started, do nothing.
  ** pass the exception on up the stack.
  **
  ** This command was inspired by Dave Thomas's talk on Ruby at the
  ** 2005 O'Reilly Open Source Convention (OSCON).
  */
  case DB_TRANSACTION: {

    Tcl_Obj *pScript;
    const char *zBegin = "SAVEPOINT _tcl_transaction";
    const char *zEnd;
    if( objc!=3 && objc!=4 ){
      Tcl_WrongNumArgs(interp, 2, objv, "[TYPE] SCRIPT");
      return TCL_ERROR;
    }

    if( pDb->nTransaction ){
      zBegin = "SAVEPOINT _tcl_transaction";
    }else if( pDb->nTransaction==0 && objc==4 ){
      static const char *TTYPE_strs[] = {
        "deferred",   "exclusive",  "immediate", 0
      };
      enum TTYPE_enum {
        TTYPE_DEFERRED, TTYPE_EXCLUSIVE, TTYPE_IMMEDIATE
      };
      int ttype;
      if( Tcl_GetIndexFromObj(interp, objv[2], TTYPE_strs, "transaction type",
                              0, &ttype) ){
        return TCL_ERROR;
      }
      switch( (enum TTYPE_enum)ttype ){
        case TTYPE_DEFERRED:    /* no-op */;                 break;
        case TTYPE_EXCLUSIVE:   zBegin = "BEGIN EXCLUSIVE";  break;
        case TTYPE_IMMEDIATE:   zBegin = "BEGIN IMMEDIATE";  break;
      }
    }
    pScript = objv[objc-1];



    pDb->disableAuth++;
    rc = sqlite3_exec(pDb->db, zBegin, 0, 0, 0);
    pDb->disableAuth--;
    if( rc!=SQLITE_OK ){
      Tcl_AppendResult(interp, sqlite3_errmsg(pDb->db), 0);
      return TCL_ERROR;
    }

    pDb->nTransaction++;
    rc = Tcl_EvalObjEx(interp, pScript, 0);
    pDb->nTransaction--;

    if( rc!=TCL_ERROR ){
      if( pDb->nTransaction ){
        zEnd = "RELEASE _tcl_transaction";
      }else{
        zEnd = "COMMIT";
      }
    }else{
      if( pDb->nTransaction ){
        zEnd = "ROLLBACK TO _tcl_transaction ; RELEASE _tcl_transaction";
      }else{
        zEnd = "ROLLBACK";
      }
    }

    pDb->disableAuth++;
    if( sqlite3_exec(pDb->db, zEnd, 0, 0, 0) ){
      /* This is a tricky scenario to handle. The most likely cause of an
      ** error is that the exec() above was an attempt to commit the 
      ** top-level transaction that returned SQLITE_BUSY. Or, less likely,
      ** that an IO-error has occured. In either case, throw a Tcl exception
      ** and try to rollback the transaction.
      **
      ** But it could also be that the user executed one or more BEGIN, 
      ** COMMIT, SAVEPOINT, RELEASE or ROLLBACK commands that are confusing
      ** this method's logic. Not clear how this would be best handled.
      */
      if( rc!=TCL_ERROR ){
        Tcl_AppendResult(interp, sqlite3_errmsg(pDb->db), 0);
        rc = TCL_ERROR;
      }
      sqlite3_exec(pDb->db, "ROLLBACK", 0, 0, 0);
    }
    pDb->disableAuth--;

    break;
  }

  /*
  **    $db update_hook ?script?
  **    $db rollback_hook ?script?
  */

# 2007 October 15
#
# 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: fts3near.test,v 1.3 2009/01/02 17:33:46 danielk1977 Exp $
#

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

# If SQLITE_ENABLE_FTS3 is defined, omit this file.
ifcapable !fts3 {

} {1}
do_test fts3near-1.12 {
  execsql {SELECT docid FROM t1 WHERE content MATCH 'five NEAR/1 "two three"'}
} {2 3} 
do_test fts3near-1.13 {
  execsql {SELECT docid FROM t1 WHERE content MATCH 'one NEAR five'}
} {1 3} 

do_test fts3near-1.14 {
  execsql {SELECT docid FROM t1 WHERE content MATCH 'four NEAR four'}
} {} 
do_test fts3near-1.15 {
  execsql {SELECT docid FROM t1 WHERE content MATCH 'one NEAR two NEAR one'}
} {3} 


# Output format of the offsets() function:
#
#     <column number> <term number> <starting offset> <number of bytes>
#
db eval {

# This file implements regression tests for TCL interface to the
# SQLite library. 
#
# Actually, all tests are based on the TCL interface, so the main
# interface is pretty well tested.  This file contains some addition
# tests for fringe issues that the main test suite does not cover.
#
# $Id: tclsqlite.test,v 1.71 2009/01/02 17:33:46 danielk1977 Exp $

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

# Check the error messages generated by tclsqlite
#
if {[sqlite3 -has-codec]} {

        db eval {INSERT INTO t4 VALUES(3)}
        db eval {INSERT INTO t4 VALUES(4)}
        error test-error
      }
    }
  }
  db eval {SELECT * FROM t4}
} {1 2}
do_test tcl-10.10 {
  for {set i 0} {$i<1} {incr i} {
    db transaction {
      db eval {INSERT INTO t4 VALUES(5)}
      continue
    }
    error "This line should not be run"
  }
  db eval {SELECT * FROM t4}
} {1 2 5}
do_test tcl-10.11 {
  for {set i 0} {$i<10} {incr i} {
    db transaction {
      db eval {INSERT INTO t4 VALUES(6)}
      break
    }
  }
  db eval {SELECT * FROM t4}
} {1 2 5 6}
do_test tcl-10.12 {
  set rc [catch {
    for {set i 0} {$i<10} {incr i} {
      db transaction {
        db eval {INSERT INTO t4 VALUES(7)}
        return
      }
    }
  }]
} {2}
do_test tcl-10.13 {
  db eval {SELECT * FROM t4}
} {1 2 5 6 7}

# Now test that [db transaction] commands may be nested with 
# the expected results.
#
do_test tcl-10.14 {
  db transaction {
    db eval {
      DELETE FROM t4;
      INSERT INTO t4 VALUES('one');
    }

    catch { 
      db transaction {
        db eval { INSERT INTO t4 VALUES('two') }
        db transaction {
          db eval { INSERT INTO t4 VALUES('three') }
          error "throw an error!"
        }
      }
    }
  }

  db eval {SELECT * FROM t4}
} {one}
do_test tcl-10.15 {
  # Make sure a transaction has not been left open.
  db eval {BEGIN ; COMMIT}
} {}
do_test tcl-10.16 {
  db transaction {
    db eval { INSERT INTO t4 VALUES('two'); }
    db transaction {
      db eval { INSERT INTO t4 VALUES('three') }
      db transaction {
        db eval { INSERT INTO t4 VALUES('four') }
      }
    }
  }
  db eval {SELECT * FROM t4}
} {one two three four}
do_test tcl-10.17 {
  catch {
    db transaction {
      db eval { INSERT INTO t4 VALUES('A'); }
      db transaction {
        db eval { INSERT INTO t4 VALUES('B') }
        db transaction {
          db eval { INSERT INTO t4 VALUES('C') }
          error "throw an error!"
        }
      }
    }
  }
  db eval {SELECT * FROM t4}
} {one two three four}
do_test tcl-10.18 {
  # Make sure a transaction has not been left open.
  db eval {BEGIN ; COMMIT}
} {}

# Mess up a [db transaction] command by locking the database using a
# second connection when it tries to commit. Make sure the transaction
# is not still open after the "database is locked" exception is thrown.
#
do_test tcl-10.18 {
  sqlite3 db2 test.db
  db2 eval {
    BEGIN;
    SELECT * FROM sqlite_master;
  }

  set rc [catch {
    db transaction {
      db eval {INSERT INTO t4 VALUES('five')}
    }
  } msg]
  list $rc $msg
} {1 {database is locked}}
do_test tcl-10.19 {
  db eval {BEGIN ; COMMIT}
} {}

# Thwart a [db transaction] command by locking the database using a
# second connection with "BEGIN EXCLUSIVE". Make sure no transaction is 
# open after the "database is locked" exception is thrown.
#
do_test tcl-10.20 {
  db2 eval {
    COMMIT;
    BEGIN EXCLUSIVE;
  }
  set rc [catch {
    db transaction {
      db eval {INSERT INTO t4 VALUES('five')}
    }
  } msg]
  list $rc $msg
} {1 {database is locked}}
do_test tcl-10.21 {
  db2 close
  db eval {BEGIN ; COMMIT}
} {}
do_test tcl-10.22 {
  sqlite3 db2 test.db
  db transaction exclusive {
    catch { db2 eval {SELECT * FROM sqlite_master} } msg
    set msg "db2: $msg"
  }
  set msg
} {db2: database is locked}
db2 close

do_test tcl-11.1 {
  db eval {INSERT INTO t4 VALUES(6)}
  db exists {SELECT x,x*2,x+x FROM t4 WHERE x==6}
} {1}
do_test tcl-11.2 {
  db exists {SELECT 0 FROM t4 WHERE x==6}
} {1}
do_test tcl-11.3 {
  db exists {SELECT 1 FROM t4 WHERE x==8}
} {0}

do_test tcl-12.1 {
  unset -nocomplain a b c version