SQLite

/*
** 2013-05-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.
**
******************************************************************************
**
** This SQLite extension implements a rot13() function and a rot13
** collating sequence.
*/
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#include <assert.h>
#include <string.h>

/*
** Perform rot13 encoding on a single ASCII character.
*/
static unsigned char rot13(unsigned char c){
  if( c>='a' && c<='z' ){
    c += 13;
    if( c>'z' ) c -= 26;
  }else if( c>='A' && c<='Z' ){
    c += 13;
    if( c>'Z' ) c -= 26;
  }
  return c;
}

/*
** Implementation of the rot13() function.
**
** Rotate ASCII alphabetic characters by 13 character positions.  
** Non-ASCII characters are unchanged.  rot13(rot13(X)) should always
** equal X.
*/
static void rot13func(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const unsigned char *zIn;
  int nIn;
  unsigned char *zOut;
  char *zToFree = 0;
  int i;
  char zTemp[100];
  assert( argc==1 );
  if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  zIn = (const unsigned char*)sqlite3_value_text(argv[0]);
  nIn = sqlite3_value_bytes(argv[0]);
  if( nIn<sizeof(zTemp)-1 ){
    zOut = zTemp;
  }else{
    zOut = zToFree = sqlite3_malloc( nIn+1 );
    if( zOut==0 ){
      sqlite3_result_error_nomem(context);
      return;
    }
  }
  for(i=0; i<nIn; i++) zOut[i] = rot13(zIn[i]);
  zOut[i] = 0;
  sqlite3_result_text(context, (char*)zOut, i, SQLITE_TRANSIENT);
  sqlite3_free(zToFree);
}

/*
** Implement the rot13 collating sequence so that if
**
**      x=y COLLATE rot13
**
** Then 
**
**      rot13(x)=rot13(y) COLLATE binary
*/
static int rot13CollFunc(
  void *notUsed,
  int nKey1, const void *pKey1,
  int nKey2, const void *pKey2
){
  const char *zA = (const char*)pKey1;
  const char *zB = (const char*)pKey2;
  int i, x;
  for(i=0; i<nKey1 && i<nKey2; i++){
    x = (int)rot13(zA[i]) - (int)rot13(zB[i]);
    if( x!=0 ) return x;
  }
  return nKey1 - nKey2;
}


#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_rot_init(
  sqlite3 *db, 
  char **pzErrMsg, 
  const sqlite3_api_routines *pApi
){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  (void)pzErrMsg;  /* Unused parameter */
  rc = sqlite3_create_function(db, "rot13", 1, SQLITE_UTF8, 0,
                               rot13func, 0, 0);
  if( rc==SQLITE_OK ){
    rc = sqlite3_create_collation(db, "rot13", SQLITE_UTF8, 0, rot13CollFunc);
  }
  return rc;
}

  $(TOP)/src/btree.c \
  $(TOP)/src/build.c \
  $(TOP)/src/date.c \
  $(TOP)/src/expr.c \
  $(TOP)/src/func.c \
  $(TOP)/src/insert.c \
  $(TOP)/src/wal.c \
  $(TOP)/src/main.c \
  $(TOP)/src/mem5.c \
  $(TOP)/src/os.c \
  $(TOP)/src/os_unix.c \
  $(TOP)/src/os_win.c \
  $(TOP)/src/pager.c \
  $(TOP)/src/pragma.c \
  $(TOP)/src/prepare.c \

page1_init_failed:
  releasePage(pPage1);
  pBt->pPage1 = 0;
  return rc;
}

#ifndef NDEBUG
/*
** Return the number of cursors open on pBt. This is for use
** in assert() expressions, so it is only compiled if NDEBUG is not
** defined.
**
** Only write cursors are counted if wrOnly is true.  If wrOnly is
** false then all cursors are counted.
**
** For the purposes of this routine, a cursor is any cursor that
** is capable of reading or writing to the databse.  Cursors that
** have been tripped into the CURSOR_FAULT state are not counted.
*/
static int countValidCursors(BtShared *pBt, int wrOnly){
  BtCursor *pCur;
  int r = 0;
  for(pCur=pBt->pCursor; pCur; pCur=pCur->pNext){
    if( (wrOnly==0 || pCur->wrFlag) && pCur->eState!=CURSOR_FAULT ) r++; 
  }
  return r;
}
#endif

/*
** If there are no outstanding cursors and we are not in the middle
** of a transaction but there is a read lock on the database, then
** this routine unrefs the first page of the database file which 
** has the effect of releasing the read lock.
**
** If there is a transaction in progress, this routine is a no-op.
*/
static void unlockBtreeIfUnused(BtShared *pBt){
  assert( sqlite3_mutex_held(pBt->mutex) );
  assert( countValidCursors(pBt,0)==0 || pBt->inTransaction>TRANS_NONE );
  if( pBt->inTransaction==TRANS_NONE && pBt->pPage1!=0 ){
    assert( pBt->pPage1->aData );
    assert( sqlite3PagerRefcount(pBt->pPager)==1 );
    assert( pBt->pPage1->aData );
    releasePage(pBt->pPage1);
    pBt->pPage1 = 0;
  }

static void btreeEndTransaction(Btree *p){
  BtShared *pBt = p->pBt;
  assert( sqlite3BtreeHoldsMutex(p) );

#ifndef SQLITE_OMIT_AUTOVACUUM
  pBt->bDoTruncate = 0;
#endif

  if( p->inTrans>TRANS_NONE && p->db->activeVdbeCnt>1 ){
    /* If there are other active statements that belong to this database
    ** handle, downgrade to a read-only transaction. The other statements
    ** may still be reading from the database.  */
    downgradeAllSharedCacheTableLocks(p);
    p->inTrans = TRANS_READ;
  }else{

    assert( pBt->nTransaction>0 );
    rc = sqlite3PagerCommitPhaseTwo(pBt->pPager);
    if( rc!=SQLITE_OK && bCleanup==0 ){
      sqlite3BtreeLeave(p);
      return rc;
    }
    pBt->inTransaction = TRANS_READ;
    btreeClearHasContent(pBt);
  }

  btreeEndTransaction(p);
  sqlite3BtreeLeave(p);
  return SQLITE_OK;
}

  if( rc==SQLITE_OK ){
    rc = sqlite3BtreeCommitPhaseTwo(p, 0);
  }
  sqlite3BtreeLeave(p);
  return rc;
}






















/*
** This routine sets the state to CURSOR_FAULT and the error
** code to errCode for every cursor on BtShared that pBtree
** references.
**
** Every cursor is tripped, including cursors that belong
** to other database connections that happen to be sharing

      int nPage = get4byte(28+(u8*)pPage1->aData);
      testcase( nPage==0 );
      if( nPage==0 ) sqlite3PagerPagecount(pBt->pPager, &nPage);
      testcase( pBt->nPage!=nPage );
      pBt->nPage = nPage;
      releasePage(pPage1);
    }
    assert( countValidCursors(pBt, 1)==0 );
    pBt->inTransaction = TRANS_READ;
    btreeClearHasContent(pBt);
  }

  btreeEndTransaction(p);
  sqlite3BtreeLeave(p);
  return rc;
}

  /* Figure out how many bytes of space are required to store explicitly
  ** specified collation sequence names.
  */
  for(i=0; i<pList->nExpr; i++){
    Expr *pExpr = pList->a[i].pExpr;
    if( pExpr ){
      assert( pExpr->op==TK_COLLATE );

      nExtra += (1 + sqlite3Strlen30(pExpr->u.zToken));

    }
  }

  /* 
  ** Allocate the index structure. 
  */
  nName = sqlite3Strlen30(zName);

  ** same column more than once cannot be an error because that would 
  ** break backwards compatibility - it needs to be a warning.
  */
  for(i=0, pListItem=pList->a; i<pList->nExpr; i++, pListItem++){
    const char *zColName = pListItem->zName;
    Column *pTabCol;
    int requestedSortOrder;

    char *zColl;                   /* Collation sequence name */

    for(j=0, pTabCol=pTab->aCol; j<pTab->nCol; j++, pTabCol++){
      if( sqlite3StrICmp(zColName, pTabCol->zName)==0 ) break;
    }
    if( j>=pTab->nCol ){
      sqlite3ErrorMsg(pParse, "table %s has no column named %s",
        pTab->zName, zColName);
      pParse->checkSchema = 1;
      goto exit_create_index;
    }
    pIndex->aiColumn[i] = j;
    if( pListItem->pExpr ){


      int nColl;
      assert( pListItem->pExpr->op==TK_COLLATE );
      zColl = pListItem->pExpr->u.zToken;
      nColl = sqlite3Strlen30(zColl) + 1;
      assert( nExtra>=nColl );
      memcpy(zExtra, zColl, nColl);
      zColl = zExtra;
      zExtra += nColl;
      nExtra -= nColl;
    }else{
      zColl = pTab->aCol[j].zColl;
      if( !zColl ) zColl = "BINARY";


    }
    if( !db->init.busy && !sqlite3LocateCollSeq(pParse, zColl) ){
      goto exit_create_index;
    }
    pIndex->azColl[i] = zColl;
    requestedSortOrder = pListItem->sortOrder & sortOrderMask;
    pIndex->aSortOrder[i] = (u8)requestedSortOrder;

    int op = p->op;
    if( op==TK_CAST || op==TK_UPLUS ){
      p = p->pLeft;
      continue;
    }
    assert( op!=TK_REGISTER || p->op2!=TK_COLLATE );
    if( op==TK_COLLATE ){




      pColl = sqlite3GetCollSeq(pParse, ENC(db), 0, p->u.zToken);

      break;
    }
    if( p->pTab!=0
     && (op==TK_AGG_COLUMN || op==TK_COLUMN
          || op==TK_REGISTER || op==TK_TRIGGER)
    ){
      /* op==TK_REGISTER && p->pTab!=0 happens when pExpr was originally

  if( !forceZombie && connectionIsBusy(db) ){
    sqlite3Error(db, SQLITE_BUSY, "unable to close due to unfinalized "
       "statements or unfinished backups");
    sqlite3_mutex_leave(db->mutex);
    return SQLITE_BUSY;
  }

  /* If a transaction is open, roll it back. This also ensures that if
  ** any database schemas have been modified by the current transaction
  ** they are reset. And that the required b-tree mutex is held to make
  ** the the pager rollback and schema reset an atomic operation. */
  sqlite3RollbackAll(db, SQLITE_OK);

#ifdef SQLITE_ENABLE_SQLLOG
  if( sqlite3GlobalConfig.xSqllog ){
    /* Closing the handle. Fourth parameter is passed the value 2. */
    sqlite3GlobalConfig.xSqllog(sqlite3GlobalConfig.pSqllogArg, db, 0, 2);
  }
#endif

** attempts to use that cursor.
*/
void sqlite3RollbackAll(sqlite3 *db, int tripCode){
  int i;
  int inTrans = 0;
  assert( sqlite3_mutex_held(db->mutex) );
  sqlite3BeginBenignMalloc();
  sqlite3BtreeEnterAll(db);
  for(i=0; i<db->nDb; i++){
    Btree *p = db->aDb[i].pBt;
    if( p ){
      if( sqlite3BtreeIsInTrans(p) ){
        inTrans = 1;
      }
      sqlite3BtreeRollback(p, tripCode);
      db->aDb[i].inTrans = 0;
    }
  }
  sqlite3VtabRollback(db);
  sqlite3EndBenignMalloc();

  if( (db->flags&SQLITE_InternChanges)!=0 && db->init.busy==0 ){
    sqlite3ExpirePreparedStatements(db);
    sqlite3ResetAllSchemasOfConnection(db);
  }
  sqlite3BtreeLeaveAll(db);

  /* Any deferred constraint violations have now been resolved. */
  db->nDeferredCons = 0;

  /* If one has been configured, invoke the rollback-hook callback */
  if( db->xRollbackCallback && (inTrans || !db->autoCommit) ){
    db->xRollbackCallback(db->pRollbackArg);

    CREATE INDEX collate3i1 ON collate3t1(c1 COLLATE garbage);
  }
} {1 {no such collation sequence: garbage}}

execsql {
  DROP TABLE collate3t1;
}

proc caseless {a b} { string compare -nocase $a $b }
do_test collate3-1.4 {
  db collate caseless caseless
  execsql { 
    CREATE TABLE t1(a COLLATE caseless); 
    INSERT INTO t1 VALUES('Abc2');
    INSERT INTO t1 VALUES('abc1');
    INSERT INTO t1 VALUES('aBc3');
  }
  execsql { SELECT * FROM t1 ORDER BY a }
} {abc1 Abc2 aBc3}

do_test collate3-1.5 {
  db close
  sqlite3 db test.db
  catchsql { SELECT * FROM t1 ORDER BY a }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.6.1 {
  db collate caseless caseless
  execsql { CREATE INDEX i1 ON t1(a) }
  execsql { SELECT * FROM t1 ORDER BY a }
} {abc1 Abc2 aBc3}

do_test collate3-1.6.2 {
  db close
  sqlite3 db test.db
  catchsql { SELECT * FROM t1 ORDER BY a }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.6.3 {
  db close
  sqlite3 db test.db
  catchsql { PRAGMA integrity_check }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.6.4 {
  db close
  sqlite3 db test.db
  catchsql { REINDEX }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.7.1 {
  db collate caseless caseless
  execsql {
    DROP TABLE t1;
    CREATE TABLE t1(a);
    CREATE INDEX i1 ON t1(a COLLATE caseless);
    INSERT INTO t1 VALUES('Abc2');
    INSERT INTO t1 VALUES('abc1');
    INSERT INTO t1 VALUES('aBc3');
    SELECT * FROM t1 ORDER BY a COLLATE caseless;
  }
} {abc1 Abc2 aBc3}

do_test collate3-1.7.2 {
  db close
  sqlite3 db test.db
  catchsql { SELECT * FROM t1 ORDER BY a COLLATE caseless}
} {1 {no such collation sequence: caseless}}

do_test collate3-1.7.4 {
  db close
  sqlite3 db test.db
  catchsql { REINDEX }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.7.3 {
  db close
  sqlite3 db test.db
  catchsql { PRAGMA integrity_check }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.7.4 {
  db close
  sqlite3 db test.db
  catchsql { REINDEX }
} {1 {no such collation sequence: caseless}}

do_test collate3-1.7.5 {
  db close
  sqlite3 db test.db
  db collate caseless caseless
  catchsql { PRAGMA integrity_check }
} {0 ok}

proc needed {nm} { db collate caseless caseless }
do_test collate3-1.7.6 {
  db close
  sqlite3 db test.db
  db collation_needed needed
  catchsql { PRAGMA integrity_check }
} {0 ok}

do_test collate3-1.8 {
  execsql { DROP TABLE t1 }
} {}

#
# Create a table with a default collation sequence, then close
# and re-open the database without re-registering the collation
# sequence. Then make sure the library stops us from using
# the collation sequence in:
# * an explicitly collated ORDER BY

# 2013 May 14
#
# 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.
#
#***********************************************************************
#
# Test some specific circumstances to do with shared cache mode.
#


set testdir [file dirname $argv0]
source $testdir/tester.tcl
if {[run_thread_tests]==0} { finish_test ; return }
db close
set ::testprefix sharedA

set ::enable_shared_cache [sqlite3_enable_shared_cache 1]

#-------------------------------------------------------------------------
#
do_test 0.1 {
  sqlite3 db1 test.db
  sqlite3 db2 test.db

  db1 eval {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(randomblob(100));
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    CREATE INDEX i1 ON t1(x);
  }

  db1 eval {
    BEGIN;
    DROP INDEX i1;
  }

  db2 close

  db1 eval {
    INSERT INTO t1 SELECT randomblob(100) FROM t1;
    ROLLBACK;
    PRAGMA integrity_check;
  }
} {ok}

db1 close
forcedelete test.db


#-------------------------------------------------------------------------
#
do_test 1.1 {
  sqlite3 db1 test.db
  sqlite3 db2 test.db
  db2 eval {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(123);
  }
  db1 eval { 
    SELECT * FROM t1;
    CREATE INDEX i1 ON t1(x);
  }
} {123}

do_test 1.2 {
  db2 eval { SELECT * FROM t1 ORDER BY x; }

  db1 eval {
    BEGIN; DROP INDEX i1;
  }
  db1 close

  db2 eval { SELECT * FROM t1 ORDER BY x; }
} {123}

do_test 1.3 {
  db2 close
} {}

#-------------------------------------------------------------------------
#
# sqlite3RollbackAll() loops through all attached b-trees and rolls
# back each one separately.  Then if the SQLITE_InternChanges flag is
# set, it resets the schema.  Both of the above steps must be done
# while holding a mutex, otherwise another thread might slip in and
# try to use the new schema with the old data.
#
# The following sequence of tests attempt to verify that the actions
# taken by sqlite3RollbackAll() are thread-atomic (that they cannot be
# interrupted by a separate thread.)  
#
# Note that a TCL interpreter can only be used within the thread in which
# it was originally created (because it uses thread-local-storage).  
# The tvfs callbacks must therefore only run on the main thread.  
# There is some trickery in the read_callback procedure to ensure that
# this is the case.
#
testvfs tvfs

# Set up two databases and two database connections.
#
#   db1:  main(test.db), two(test2.db)
#   db2:  main(test.db)
#
# The cache for test.db is shared between db1 and db2.
#
do_test 2.1 {
  forcedelete test.db test.db2
  sqlite3 db1 test.db -vfs tvfs
  db1 eval { ATTACH 'test.db2' AS two }

  db1 eval {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(1);
    INSERT INTO t1 VALUES(2);
    INSERT INTO t1 VALUES(3);
    CREATE TABLE two.t2(x);
    INSERT INTO t2 SELECT * FROM t1;
  }

  sqlite3 db2 test.db -vfs tvfs
  db2 eval { SELECT * FROM t1 }
} {1 2 3}

# Create a prepared statement on db2 that will attempt a schema change
# in test.db.  Meanwhile, start a transaction on db1 that changes
# the schema of test.db and that creates a rollback journal on test2.db
#
do_test 2.2 {
  set ::STMT [sqlite3_prepare db2 "CREATE INDEX i1 ON t1(x)" -1 tail]
  db1 eval {
    BEGIN;
      CREATE INDEX i1 ON t1(x);
      INSERT INTO t2 VALUES('value!');
  }
} {}

# Set up a callback that will cause db2 to try to execute its
# schema change when db1 accesses the journal file of test2.db.
#
# This callback will be invoked after the content of test.db has
# be rolled back but before the schema has been reset.  If the
# sqlite3RollbackAll() operation is not thread-atomic, then the
# db2 statement in the callback will see old content with the newer
# schema, which is wrong.
#
tvfs filter xRead
tvfs script read_callback
unset -nocomplain ::some_time_laster
unset -nocomplain ::thread_result
proc read_callback {call file args} { 
  if {[string match *test.db2-journal $file]} {
    tvfs filter {}   ;# Ensure that tvfs callbacks to do run on the
                      # child thread
    sqlthread spawn ::thread_result [subst -nocommands {
      sqlite3_step $::STMT
      set rc [sqlite3_finalize $::STMT]
    }]
    after 1000 { set ::some_time_later 1 }
    vwait ::some_time_later
  }
}
do_test 2.3 { db1 eval ROLLBACK } {}

# Verify that the db2 statement invoked by the callback detected the
# schema change.
#
if {[info exists ::thread_result]==0} { vwait ::thread_result }
do_test 2.4 { 
  list $::thread_result [sqlite3_errmsg db2] 
} {SQLITE_SCHEMA {database schema has changed}}

db1 close
db2 close
tvfs delete

sqlite3_enable_shared_cache $::enable_shared_cache
finish_test