SQLite

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.123 2004/05/19 20:41:03 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

char const *sqlite3AffinityString(char affinity){
  switch( affinity ){
    case SQLITE_AFF_INTEGER: return "i";

** to fit in a 32-bit integer, return 1 and put the value of the integer
** in *pValue.  If the expression is not an integer or if it is too big
** to fit in a signed 32-bit integer, return 0 and leave *pValue unchanged.
*/
int sqlite3ExprIsInteger(Expr *p, int *pValue){
  switch( p->op ){
    case TK_INTEGER: {

      if( sqlite3GetInt32(p->token.z, pValue) ){
        return 1;
      }
      break;
    }
    case TK_STRING: {
      const char *z = p->token.z;
      int n = p->token.n;
      if( n>0 && z[0]=='-' ){ z++; n--; }
      while( n>0 && *z && isdigit(*z) ){ z++; n--; }
      if( n==0 && sqlite3GetInt32(p->token.z, pValue) ){

        return 1;
      }
      break;
    }
    case TK_UPLUS: {
      return sqlite3ExprIsInteger(p->pLeft, pValue);
    }

    default:
      assert( p->op==TK_ABORT );  /* Can't Happen */
      break;
  }
  return SQLITE_SO_NUM;
}

/*
** Generate an instruction that will put the integer describe by
** text z[0..n-1] on the stack.
*/
static void codeInteger(Vdbe *v, const char *z, int n){
  int i;
  if( sqlite3GetInt32(z, &i) || (i=0, sqlite3FitsIn64Bits(z))!=0 ){
    sqlite3VdbeOp3(v, OP_Integer, i, 0, z, n);
  }else{
    sqlite3VdbeOp3(v, OP_Real, 0, 0, z, n);
  }
}

/*
** Generate code into the current Vdbe to evaluate the given
** expression and leave the result on the top of stack.
*/
void sqlite3ExprCode(Parse *pParse, Expr *pExpr){
  Vdbe *v = pParse->pVdbe;

    case TK_UMINUS:   op = OP_Negative; break;
    case TK_BITAND:   op = OP_BitAnd;   break;
    case TK_BITOR:    op = OP_BitOr;    break;
    case TK_BITNOT:   op = OP_BitNot;   break;
    case TK_LSHIFT:   op = OP_ShiftLeft;  break;
    case TK_RSHIFT:   op = OP_ShiftRight; break;
    case TK_REM:      op = OP_Remainder;  break;
    case TK_FLOAT:    op = OP_Real;       break;
    case TK_STRING:   op = OP_String;     break;
    default: break;
  }
  switch( pExpr->op ){
    case TK_COLUMN: {
      if( pParse->useAgg ){
        sqlite3VdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg);
      }else if( pExpr->iColumn>=0 ){
        sqlite3VdbeAddOp(v, OP_Column, pExpr->iTable, pExpr->iColumn);
      }else{
        sqlite3VdbeAddOp(v, OP_Recno, pExpr->iTable, 0);
      }
      break;
    }


    case TK_INTEGER: {

      codeInteger(v, pExpr->token.z, pExpr->token.n);


      break;

    }
    case TK_FLOAT:
    case TK_STRING: {
      sqlite3VdbeOp3(v, op, 0, 0, pExpr->token.z, pExpr->token.n);
      sqlite3VdbeDequoteP3(v, -1);
      break;
    }
    case TK_NULL: {
      sqlite3VdbeAddOp(v, OP_String, 0, 0);
      break;
    }

    case TK_CONCAT: {
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3ExprCode(pParse, pExpr->pRight);
      sqlite3VdbeAddOp(v, OP_Concat, 2, 0);
      break;
    }
    case TK_UMINUS: {
      Expr *pLeft = pExpr->pLeft;
      assert( pLeft );
      if( pLeft->op==TK_FLOAT || pLeft->op==TK_INTEGER ){
        Token *p = &pLeft->token;
        char *z = sqliteMalloc( p->n + 2 );
        sprintf(z, "-%.*s", p->n, p->z);
        if( pLeft->op==TK_FLOAT ){
          sqlite3VdbeOp3(v, OP_Real, 0, 0, z, p->n+1);
        }else{
          codeInteger(v, z, p->n+1);
        }

        sqliteFree(z);
        break;
      }
      /* Fall through into TK_NOT */
    }
    case TK_BITNOT:
    case TK_NOT: {

      getFunctionName(pExpr, &zId, &nId);
      pDef = sqlite3FindFunction(pParse->db, zId, nId, nExpr, 0);
      assert( pDef!=0 );
      nExpr = sqlite3ExprCodeExprList(pParse, pList, pDef->includeTypes);
      /* FIX ME: The following is a temporary hack. */
      if( 0==sqlite3StrNICmp(zId, "classof", nId) ){
        assert( nExpr==1 );
        sqlite3VdbeAddOp(v, OP_Class, nExpr, 0);
      }else{
        sqlite3VdbeOp3(v, OP_Function, nExpr, 0, (char*)pDef, P3_POINTER);
      }
      break;
    }
    case TK_SELECT: {
      sqlite3VdbeAddOp(v, OP_MemLoad, pExpr->iColumn, 0);

    p->nArg = nArg;
    p->pNext = pFirst;
    p->dataType = pFirst ? pFirst->dataType : SQLITE_NUMERIC;
    sqlite3HashInsert(&db->aFunc, zName, nName, (void*)p);
  }
  return p;
}

/*
** 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.238 2004/05/19 20:41:03 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include <stdio.h>
#include <stdlib.h>

**
** If CollSeq.xCmp is NULL, it means that the collating sequence is
** undefined.  Indices built on an undefined collating sequence may
** not be read or written.
*/
struct CollSeq {
  char *zName;         /* Name of the collating sequence */
  u8 reverseOrder;     /* Compare in reverse order.  Used by OP_Sort only */
  void *pUser;         /* First argument to xCmp() */
  int (*xCmp)(void*,int,const void*,int,const void*); /* Comparison function */
};

/*
** The allowed sort orders.
**

int sqlite3FixSrcList(DbFixer*, SrcList*);
int sqlite3FixSelect(DbFixer*, Select*);
int sqlite3FixExpr(DbFixer*, Expr*);
int sqlite3FixExprList(DbFixer*, ExprList*);
int sqlite3FixTriggerStep(DbFixer*, TriggerStep*);
double sqlite3AtoF(const char *z, const char **);
char *sqlite3_snprintf(int,char*,const char*,...);
int sqlite3GetInt32(const char *, int*);
int sqlite3GetInt64(const char *, i64*);
int sqlite3FitsIn64Bits(const char *);
unsigned char *sqlite3utf16to8(const void *pData, int N);
void *sqlite3utf8to16be(const unsigned char *pIn, int N);
void *sqlite3utf8to16le(const unsigned char *pIn, int N);
void sqlite3utf16to16le(void *pData, int N);
void sqlite3utf16to16be(void *pData, int N);
int sqlite3PutVarint(unsigned char *, u64);
int sqlite3GetVarint(const unsigned char *, u64 *);
int sqlite3GetVarint32(const unsigned char *, u32 *);
int sqlite3VarintLen(u64 v);
char sqlite3AffinityType(const char *, int);
void sqlite3IndexAffinityStr(Vdbe *, Index *);
void sqlite3TableAffinityStr(Vdbe *, Table *);
char sqlite3CompareAffinity(Expr *pExpr, char aff2);
char const *sqlite3AffinityString(char affinity);
int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity);
char sqlite3ExprAffinity(Expr *pExpr);
int sqlite3atoi64(const char*, i64*);

**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.86 2004/05/19 20:41:03 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.

    }else{
      v1 *= scale;
    }
  }
  if( pzEnd ) *pzEnd = z;
  return sign<0 ? -v1 : v1;
}

/*
** Return TRUE if zNum is a 64-bit signed integer and write
** the value of the integer into *pNum.  If zNum is not an integer
** or is an integer that is too large to be expressed with 64 bits,
** then return false.  If n>0 and the integer is string is not
** exactly n bytes long, return false.
**
** When this routine was originally written it dealt with only
** 32-bit numbers.  At that time, it was much faster than the
** atoi() library routine in RedHat 7.2.
*/
int sqlite3atoi64(const char *zNum, i64 *pNum){
  i64 v = 0;
  int neg;
  int i, c;
  if( *zNum=='-' ){
    neg = 1;
    zNum++;
  }else if( *zNum=='+' ){
    neg = 0;
    zNum++;
  }else{
    neg = 0;
  }
  for(i=0; (c=zNum[i])>='0' && c<='9'; i++){
    v = v*10 + c - '0';
  }
  *pNum = neg ? -v : v;
  return c==0 && i>0 && 
      (i<19 || (i==19 && memcmp(zNum,"9223372036854775807",19)<=0));
}

/*
** The string zNum represents an integer.  There might be some other
** information following the integer too, but that part is ignored.
** If the integer that the prefix of zNum represents will fit in a
** 32-bit signed integer, return TRUE.  Otherwise return FALSE.
**
** This routine returns FALSE for the string -2147483648 even that
** that number will, in theory fit in a 32-bit integer.  But positive
** 2147483648 will not fit in 32 bits.  So it seems safer to return
** false.
*/
static int sqlite3FitsIn32Bits(const char *zNum){
  int i, c;
  if( *zNum=='-' || *zNum=='+' ) zNum++;
  for(i=0; (c=zNum[i])>='0' && c<='9'; i++){}
  return i<10 || (i==10 && memcmp(zNum,"2147483647",10)<=0);
}

/*
** If zNum represents an integer that will fit in 32-bits, then set
** *pValue to that integer and return true.  Otherwise return false.
*/
int sqlite3GetInt32(const char *zNum, int *pValue){
  if( sqlite3FitsIn32Bits(zNum) ){
    *pValue = atoi(zNum);
    return 1;
  }
  return 0;
}

/*
** The string zNum represents an integer.  There might be some other
** information following the integer too, but that part is ignored.
** If the integer that the prefix of zNum represents will fit in a
** 64-bit signed integer, return TRUE.  Otherwise return FALSE.
**
** This routine returns FALSE for the string -9223372036854775808 even that
** that number will, in theory fit in a 64-bit integer.  Positive
** 9223373036854775808 will not fit in 64 bits.  So it seems safer to return
** false.
*/
int sqlite3FitsIn64Bits(const char *zNum){
  int i, c;
  if( *zNum=='-' || *zNum=='+' ) zNum++;
  for(i=0; (c=zNum[i])>='0' && c<='9'; i++){}
  return i<19 || (i==19 && memcmp(zNum,"9223372036854775807",19)<=0);
}

/*
** If zNum represents an integer that will fit in 64-bits, then set
** *pValue to that integer and return true.  Otherwise return false.
*/
int sqlite3GetInt64(const char *zNum, i64 *pValue){
  if( sqlite3FitsIn64Bits(zNum) ){
    sqlite3atoi64(zNum, pValue);
    return 1;
  }
  return 0;
}

/* This comparison routine is what we use for comparison operations
** between numeric values in an SQL expression.  "Numeric" is a little
** bit misleading here.  What we mean is that the strings have a
** type of "numeric" from the point of view of SQL.  The strings
** do not necessarily contain numbers.  They could contain text.
**

  int i = 0;
  do{
    i++;
    v >>= 7;
  }while( v!=0 && i<9 );
  return i;
}

**
** 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.305 2004/05/19 20:41:03 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*

    N--;
    Release(pTos);
    pTos--;
  }
  *ppTos = pTos;
}































/*
** Convert the given stack entity into a integer if it isn't one
** already.
**
** Any prior string or real representation is invalidated.  
** NULLs are converted into 0.
*/
#define Integerify(P) if(((P)->flags&MEM_Int)==0){ hardIntegerify(P); }
static void hardIntegerify(Mem *pStack){
  if( pStack->flags & MEM_Real ){
    pStack->i = (int)pStack->r;
    Release(pStack);
  }else if( pStack->flags & MEM_Str ){
    sqlite3atoi64(pStack->z, &pStack->i);
    Release(pStack);
  }else{
    pStack->i = 0;
  }
  pStack->flags = MEM_Int;
}

  }
}

/* Opcode: Integer P1 * P3
**
** The integer value P1 is pushed onto the stack.  If P3 is not zero
** then it is assumed to be a string representation of the same integer.
** If P1 is zero and P3 is not zero, then the value is derived from P3.
*/
case OP_Integer: {
  pTos++;
  pTos->i = pOp->p1;
  pTos->flags = MEM_Int;
  if( pOp->p3 ){
    pTos->z = pOp->p3;
    pTos->flags |= MEM_Str | MEM_Static;
    pTos->n = strlen(pOp->p3)+1;
    if( pTos->i==0 ){
      sqlite3GetInt64(pTos->z, &pTos->i);
    }
  }
  break;
}

/* Opcode: String * * P3
**
** The string value P3 is pushed onto the stack.  If P3==0 then a

    double r = (double)i;
    if( r!=pTos->r ){
      goto mismatch;
    }
    pTos->i = i;
  }else if( pTos->flags & MEM_Str ){
    i64 v;
    if( !sqlite3atoi64(pTos->z, &v) ){
      double r;
      if( !sqlite3IsNumber(pTos->z, 0) ){
        goto mismatch;
      }
      Realify(pTos);
      v = (int)pTos->r;
      r = (double)v;

**
** This opcode also instructs the cursor that the keys used will be
** serialized in the record format usually used for table data, not
** the usual index key format.
*/
case OP_KeyAsData: {
  int i = pOp->p1;
  Cursor *pC;
  assert( i>=0 && i<p->nCursor );
  pC = p->apCsr[i];
  pC->keyAsData = pOp->p2;
  sqlite3BtreeSetCompare(pC->pCursor, sqlite3VdbeRowCompare, pC);
  break;
}

/* Opcode: RowData P1 * *
**
** Push onto the stack the complete row data for cursor P1.
** There is no interpretation of the data.  It is just copied

  pTos++;
  pSet = &p->aSet[iSet];
  nRoot = sqliteHashCount(&pSet->hash);
  aRoot = sqliteMallocRaw( sizeof(int)*(nRoot+1) );
  if( aRoot==0 ) goto no_mem;
  for(j=0, i=sqliteHashFirst(&pSet->hash); i; i=sqliteHashNext(i), j++){
    i64 root64;
    sqlite3atoi64((char*)sqliteHashKey(i), &root64);
    aRoot[j] = root64;
  }
  aRoot[j] = 0;
  sqlite3HashClear(&pSet->hash);
  pSet->prev = 0;
  z = sqlite3BtreeIntegrityCheck(db->aDb[pOp->p2].pBt, aRoot, nRoot);
  if( z==0 || z[0]==0 ){

  }

  /* If one value is a number and the other is not, the number is less.
  ** If both are numbers, compare as reals if one is a real, or as integers
  ** if both values are integers.
  */
  if( combined_flags&(MEM_Int|MEM_Real) ){
    i64 diff;
    if( !(pMem1->flags&(MEM_Int|MEM_Real)) ){
      return 1;
    }
    if( !(pMem2->flags&(MEM_Int|MEM_Real)) ){
      return -1;
    }

    if( combined_flags&MEM_Real ){
      if( pMem1->flags&MEM_Int ){
        pMem1->r = pMem1->i;
      }
      if( pMem2->flags&MEM_Int ){
        pMem2->r = pMem2->i;
      }
      if( pMem1->r < pMem2->r ) return -1;
      if( pMem1->r > pMem2->r ) return 1;
      return 0;
    }

    diff = pMem1->i - pMem2->i;
    return diff<0 ? -1 : diff==0 ? 0 : +1;
  }

  rc = (pMem2->flags&MEM_Null) - (pMem1->flags&MEM_Null);
  if( rc ){
    return rc;
  }

      return rc;
    }
  }
 
  len = nCellKey-2;
  while( pCellKey[len] && --len );







  *res = sqlite3VdbeKeyCompare(pC, len, pCellKey, nKey, pKey);
  
  if( freeCellKey ){
    sqliteFree(pCellKey);
  }
  return SQLITE_OK;
}

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.
**
** $Id: where.c,v 1.99 2004/05/19 20:41:04 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.

      sqlite3VdbeAddOp(v, OP_NotExists, iCur, brk);
      pLevel->op = OP_Noop;
    }else if( pIdx!=0 && pLevel->score>0 && pLevel->score%4==0 ){
      /* Case 2:  There is an index and all terms of the WHERE clause that
      **          refer to the index use the "==" or "IN" operators.
      */
      int start;

      int nColumn = (pLevel->score+4)/8;
      brk = pLevel->brk = sqlite3VdbeMakeLabel(v);

      /* For each column of the index, find the term of the WHERE clause that
      ** constraints that column.  If the WHERE clause term is X=expr, then
      ** evaluation expr and leave the result on the stack */
      for(j=0; j<nColumn; j++){

        start = sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqlite3VdbeAddOp(v, OP_IdxLT, pLevel->iCur, brk);
        pLevel->op = OP_Prev;
      }else{
        /* Scan in the forward order */
        sqlite3VdbeAddOp(v, OP_MoveGe, pLevel->iCur, brk);
        start = sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);



        sqlite3VdbeOp3(v, OP_IdxGE, pLevel->iCur, brk, "+", P3_STATIC);

        pLevel->op = OP_Next;
      }
      sqlite3VdbeAddOp(v, OP_RowKey, pLevel->iCur, 0);
      sqlite3VdbeAddOp(v, OP_IdxIsNull, nColumn, cont);
      sqlite3VdbeAddOp(v, OP_IdxRecno, pLevel->iCur, 0);
      if( i==pTabList->nSrc-1 && pushKey ){
        haveKey = 1;

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.24 2004/05/19 20:41:04 drh Exp $

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

# Test the creation and use of tables that have a large number
# of columns.
#

execsql {
  DROP TABLE t1;
  DROP TABLE t2;
  DROP TABLE t3;
  DROP TABLE t4;
}

# 64-bit integers are represented exactly.

#
do_test misc1-9.1 {
  catchsql {
    CREATE TABLE t1(a unique not null, b unique not null);
    INSERT INTO t1 VALUES('a',1234567890123456789);
    INSERT INTO t1 VALUES('b',1234567891123456789);
    INSERT INTO t1 VALUES('c',1234567892123456789);
    SELECT * FROM t1;
  }
} {0 {a 1234567890123456789 b 1234567891123456789 c 1234567892123456789}}

# A WHERE clause is not allowed to contain more than 99 terms.  Check to
# make sure this limit is enforced.
#
do_test misc1-10.0 {
  execsql {SELECT count(*) FROM manycol}
} {9}

# Make sure string comparisons really do compare strings in format4+.
# Similar tests in the format3.test file show that for format3 and earlier
# all comparisions where numeric if either operand looked like a number.
#
do_test misc1-12.1 {
  execsql {SELECT '0'=='0.0'}
} {1}
do_test misc1-12.2 {
  execsql {SELECT '0'==0.0}
} {1}
do_test misc1-12.3 {
  execsql {SELECT '12345678901234567890'=='12345678901234567891'}
} {0}
do_test misc1-12.4 {

    CREATE TABLE t8(x TEXT COLLATE numeric, y INTEGER COLLATE text, z);
    INSERT INTO t8 VALUES(0,0,1);
    INSERT INTO t8 VALUES(0.0,0,2);
    INSERT INTO t8 VALUES(0,0.0,3);
    INSERT INTO t8 VALUES(0.0,0.0,4);
    SELECT DISTINCT x, y FROM t8 ORDER BY z;
  }
} {0 0 0.0 0}
do_test misc1-12.12 {
  execsql {
    SELECT min(z), max(z), count(z) FROM t8 GROUP BY x ORDER BY 1;
  }
} {1 3 2 2 4 2}
do_test misc1-12.13 {
  execsql {
    SELECT min(z), max(z), count(z) FROM t8 GROUP BY y ORDER BY 1;
  }
} {1 4 4}

# There was a problem with realloc() in the OP_MemStore operation of
# the VDBE.  A buffer was being reallocated but some pointers into 
# the old copy of the buffer were not being moved over to the new copy.
# The following code tests for the problem.
#
do_test misc1-13.1 {

# 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.
#
#***********************************************************************
# This file runs all tests.
#
# $Id: quick.test,v 1.14 2004/05/19 20:41:04 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl
rename finish_test really_finish_test
proc finish_test {} {}
set ISQUICK 1

lappend EXCLUDE printf.test       ;# sqlite3_XX vs sqlite_XX problem
lappend EXCLUDE auth.test         ;# Cannot attach empty databases.
lappend EXCLUDE tableapi.test     ;# sqlite3_XX vs sqlite_XX problem
lappend EXCLUDE version.test      ;# uses the btree_meta API (not updated)

# Some tests fail in these file as a result of the partial manifest types
# implementation.

lappend EXCLUDE capi2.test 


if {[sqlite -has-codec]} {
  lappend EXCLUDE \
    attach.test \
    attach2.test \