SQLite

**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains the implementation of the sqlite3_prepare()
** interface, and routines that contribute to loading the database schema
** from disk.
**
** $Id: prepare.c,v 1.112 2009/03/23 17:11:27 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Fill the InitData structure with an error message that indicates
** that the database is corrupt.
*/

    db->init.iDb = 0;
    db->lookaside.bEnabled = lookasideEnabled;
    assert( rc!=SQLITE_OK || zErr==0 );
    if( SQLITE_OK!=rc ){
      pData->rc = rc;
      if( rc==SQLITE_NOMEM ){
        db->mallocFailed = 1;
      }else if( rc!=SQLITE_INTERRUPT && (rc&0xff)!=SQLITE_LOCKED ){
        corruptSchema(pData, argv[0], zErr);
      }
      sqlite3DbFree(db, zErr);
    }
  }else if( argv[0]==0 ){
    corruptSchema(pData, 0, 0);
  }else{

**
*************************************************************************
**
** This file contains the implementation of some Tcl commands used to
** test that sqlite3 database handles may be concurrently accessed by 
** multiple threads. Right now this only works on unix.
**
** $Id: test_thread.c,v 1.13 2009/03/23 17:11:27 danielk1977 Exp $
*/

#include "sqliteInt.h"
#include <tcl.h>

#if SQLITE_THREADSAFE

  extern int Sqlitetest_mutex_Init(Tcl_Interp*);

  interp = Tcl_CreateInterp();
  Tcl_CreateObjCommand(interp, "clock_seconds", clock_seconds_proc, 0, 0);
  Tcl_CreateObjCommand(interp, "sqlthread", sqlthread_proc, pSqlThread, 0);
#if defined(OS_UNIX) && defined(SQLITE_ENABLE_UNLOCK_NOTIFY)
  Tcl_CreateObjCommand(interp, "sqlite3_blocking_step", blocking_step_proc,0,0);
  Tcl_CreateObjCommand(interp, 
      "sqlite3_blocking_prepare_v2", blocking_prepare_v2_proc, (void *)1, 0);
  Tcl_CreateObjCommand(interp, 
      "sqlite3_nonblocking_prepare_v2", blocking_prepare_v2_proc, 0, 0);
#endif
  Sqlitetest1_Init(interp);
  Sqlitetest_mutex_Init(interp);

  rc = Tcl_Eval(interp, p->zScript);
  pRes = Tcl_GetObjResult(interp);
  pList = Tcl_NewObj();

  Tcl_SetResult(interp, (char *)sqlite3TestErrorName(rc), 0);
  return TCL_OK;
}

/*
** Usage: sqlite3_blocking_prepare_v2 DB sql bytes ?tailvar?
** Usage: sqlite3_nonblocking_prepare_v2 DB sql bytes ?tailvar?
*/
static int blocking_prepare_v2_proc(
  void * clientData,
  Tcl_Interp *interp,
  int objc,
  Tcl_Obj *CONST objv[]
){
  sqlite3 *db;
  const char *zSql;
  int bytes;
  const char *zTail = 0;
  sqlite3_stmt *pStmt = 0;
  char zBuf[50];
  int rc;
  int isBlocking = !(clientData==0);

  if( objc!=5 && objc!=4 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", 
       Tcl_GetString(objv[0]), " DB sql bytes tailvar", 0);
    return TCL_ERROR;
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  zSql = Tcl_GetString(objv[2]);
  if( Tcl_GetIntFromObj(interp, objv[3], &bytes) ) return TCL_ERROR;

  if( isBlocking ){
    rc = sqlite3_blocking_prepare_v2(db, zSql, bytes, &pStmt, &zTail);
  }else{
    rc = sqlite3_prepare_v2(db, zSql, bytes, &pStmt, &zTail);
  }

  assert(rc==SQLITE_OK || pStmt==0);
  if( zTail && objc>=5 ){
    if( bytes>=0 ){
      bytes = bytes - (zTail-zSql);
    }
    Tcl_ObjSetVar2(interp, objv[4], 0, Tcl_NewStringObj(zTail, bytes), 0);
  }

** Register commands with the TCL interpreter.
*/
int SqlitetestThread_Init(Tcl_Interp *interp){
  Tcl_CreateObjCommand(interp, "sqlthread", sqlthread_proc, 0, 0);
  Tcl_CreateObjCommand(interp, "clock_seconds", clock_seconds_proc, 0, 0);
#if defined(OS_UNIX) && defined(SQLITE_ENABLE_UNLOCK_NOTIFY)
  Tcl_CreateObjCommand(interp, "sqlite3_blocking_step", blocking_step_proc,0,0);
  Tcl_CreateObjCommand(interp, 
      "sqlite3_blocking_prepare_v2", blocking_prepare_v2_proc, (void *)1, 0);
  Tcl_CreateObjCommand(interp, 
      "sqlite3_nonblocking_prepare_v2", blocking_prepare_v2_proc, 0, 0);
#endif
  return TCL_OK;
}
#else
int SqlitetestThread_Init(Tcl_Interp *interp){
  return TCL_OK;
}
#endif

**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.828 2009/03/23 17:11:27 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "vdbeInt.h"

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_SeekXX, OP_Next, or OP_Prev opcodes.  The test

** is false, the SQLITE_MASTER table is only parsed if the rest of the
** schema is already loaded into the symbol table.
**
** This opcode invokes the parser to create a new virtual machine,
** then runs the new virtual machine.  It is thus a re-entrant opcode.
*/
case OP_ParseSchema: {

  int iDb = pOp->p1;



  assert( iDb>=0 && iDb<db->nDb );



  /* If pOp->p2 is 0, then this opcode is being executed to read a
  ** single row, for example the row corresponding to a new index
  ** created by this VDBE, from the sqlite_master table. It only
  ** does this if the corresponding in-memory schema is currently
  ** loaded. Otherwise, the new index definition can be loaded along
  ** with the rest of the schema when it is required.
  **
  ** Although the mutex on the BtShared object that corresponds to
  ** database iDb (the database containing the sqlite_master table
  ** read by this instruction) is currently held, it is necessary to
  ** obtain the mutexes on all attached databases before checking if
  ** the schema of iDb is loaded. This is because, at the start of
  ** the sqlite3_exec() call below, SQLite will invoke 
  ** sqlite3BtreeEnterAll(). If all mutexes are not already held, the
  ** iDb mutex may be temporarily released to avoid deadlock. If 
  ** this happens, then some other thread may delete the in-memory 
  ** schema of database iDb before the SQL statement runs. The schema
  ** will not be reloaded becuase the db->init.busy flag is set. This
  ** can result in a "no such table: sqlite_master" or "malformed
  ** database schema" error being returned to the user.
  */
  assert( sqlite3BtreeHoldsMutex(db->aDb[iDb].pBt) );
  sqlite3BtreeEnterAll(db);
  if( pOp->p2 || DbHasProperty(db, iDb, DB_SchemaLoaded) ){
    const char *zMaster = SCHEMA_TABLE(iDb);
    char *zSql;
    InitData initData;
    initData.db = db;
    initData.iDb = pOp->p1;
    initData.pzErrMsg = &p->zErrMsg;
    zSql = sqlite3MPrintf(db,
       "SELECT name, rootpage, sql FROM '%q'.%s WHERE %s",
       db->aDb[iDb].zName, zMaster, pOp->p4.z);
    if( zSql==0 ){
      rc = SQLITE_NOMEM;
    }else{
      (void)sqlite3SafetyOff(db);
      assert( db->init.busy==0 );
      db->init.busy = 1;
      initData.rc = SQLITE_OK;
      assert( !db->mallocFailed );
      rc = sqlite3_exec(db, zSql, sqlite3InitCallback, &initData, 0);
      if( rc==SQLITE_OK ) rc = initData.rc;
      sqlite3DbFree(db, zSql);
      db->init.busy = 0;
      (void)sqlite3SafetyOn(db);
    }
  }
  sqlite3BtreeLeaveAll(db);
  if( rc==SQLITE_NOMEM ){
    goto no_mem;
  }
  break;  
}

#if !defined(SQLITE_OMIT_ANALYZE) && !defined(SQLITE_OMIT_PARSER)

# 2009 March 04
#
# 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: notify2.test,v 1.3 2009/03/23 17:11:27 danielk1977 Exp $

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

# The tests in this file test the sqlite3_blocking_step() function in
# test_thread.c. sqlite3_blocking_step() is not an SQLite API function,
# it is just a demonstration of how the sqlite3_unlock_notify() function

set ThreadProgram {

  # Proc used by threads to execute SQL.
  #
  proc execsql_blocking {db zSql} {
    set lRes [list]
    set rc SQLITE_OK

set sql $zSql

    while {$rc=="SQLITE_OK" && $zSql ne ""} {
      set STMT [$::xPrepare $db $zSql -1 zSql]
      while {[set rc [$::xStep $STMT]] eq "SQLITE_ROW"} {
        for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
          lappend lRes [sqlite3_column_text $STMT 0]
        }
      }
      set rc [sqlite3_finalize $STMT]
    }

    if {$rc != "SQLITE_OK"} { error "$rc $sql [sqlite3_errmsg $db]" }
    return $lRes
  }

  proc execsql_retry {db sql} { 
    set msg "SQLITE_LOCKED blah..."
    while { [string match SQLITE_LOCKED* $msg] } {
      catch { execsql_blocking $db $sql } msg
    }
  }

  proc select_one {args} {
    set n [llength $args]
    lindex $args [expr int($n*rand())]
  }

  proc opendb {} {
    # Open a database connection. Attach the two auxillary databases.
    set ::DB [sqlite3_open test.db]

    execsql_retry $::DB { ATTACH 'test2.db' AS aux2; }
    execsql_retry $::DB { ATTACH 'test3.db' AS aux3; }
  }

  opendb

  #after 2000

  # This loop runs for ~20 seconds.
  #
  set iStart [clock_seconds]
  while { ([clock_seconds]-$iStart) < $nSecond } {

    # Each transaction does 3 operations. Each operation is either a read
    # or write of a randomly selected table (t1, t2 or t3). Set the variables
    # $SQL(1), $SQL(2) and $SQL(3) to the SQL commands used to implement
    # each operation.
    #
    for {set ii 1} {$ii <= 3} {incr ii} {
      foreach {tbl database} [select_one {t1 main} {t2 aux2} {t3 aux3}] {}

      set SQL($ii) [string map [list xxx $tbl yyy $database] [select_one {
            SELECT 
              (SELECT b FROM xxx WHERE a=(SELECT max(a) FROM xxx))==total(a) 
              FROM xxx WHERE a!=(SELECT max(a) FROM xxx);
      } {
            DELETE FROM xxx WHERE a<(SELECT max(a)-100 FROM xxx);
            INSERT INTO xxx SELECT NULL, total(a) FROM xxx;
      } {

            CREATE INDEX IF NOT EXISTS yyy.xxx_i ON xxx(b);
      } {
            DROP INDEX IF EXISTS yyy.xxx_i;

      }
      ]]
    }

    # Execute the SQL transaction.
    #
    set rc [catch { execsql_blocking $::DB "
        BEGIN;
          $SQL(1);
          $SQL(2);
          $SQL(3);
        COMMIT;
      "
    } msg]

    if {$rc && [string match "SQLITE_LOCKED*" $msg]
            || [string match "SQLITE_SCHEMA*" $msg]
    } {
      # Hit an SQLITE_LOCKED error. Rollback the current transaction.
      set rc [catch { execsql_blocking $::DB ROLLBACK } msg]
      if {$rc && [string match "SQLITE_LOCKED*" $msg]} {
        sqlite3_close $::DB
        opendb
      } 
    } elseif {$rc} {
      # Hit some other kind of error. This is a malfunction.
      error $msg
    } else {
      # No error occured. Check that any SELECT statements in the transaction
      # returned "1". Otherwise, the invariant was false, indicating that
      # some malfunction has occured.
      foreach r $msg { if {$r != 1} { puts "Invariant check failed: $msg" } }
    }
  }

  # Close the database connection and return 0.
  #
  sqlite3_close $::DB
  expr 0
}

foreach {iTest xStep xPrepare} {
  1 sqlite3_blocking_step sqlite3_blocking_prepare_v2
  2 sqlite3_step          sqlite3_nonblocking_prepare_v2
} {
  file delete -force test.db test2.db test3.db

  set ThreadSetup "set xStep $xStep;set xPrepare $xPrepare;set nSecond $nSecond"

  # Set up the database schema used by this test. Each thread opens file
  # test.db as the main database, then attaches files test2.db and test3.db