**
** 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.284 2004/05/12 11:24:03 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
** P3 is a string that is P1 characters long.  Each character is either
** an 'n' or a 't' to indicates if the argument should be numeric or
** text.  The first character corresponds to the lowest element on the
** stack.  If P3 is null then all arguments are assumed to be numeric.
**
** See also:  MakeKey, SortMakeKey
*/
case OP_MakeIdxKey:
case OP_MakeKey: {
  char *zNewKey;
  int nByte;
  int nField;
  int addRowid;
  int i, j;
  int containsNull = 0;
  Mem *pRec;
................................................................................
  }else{
    pTos->z = zNewKey;
    pTos->flags = MEM_Str | MEM_Dyn;
  }
  break;
}

/* Opcode: MakeIdxKey3 P1 P2 P3
**
** Convert the top P1 entries of the stack into a single entry suitable
** for use as the key in an index.  In addition, take one additional integer
** off of the stack, treat that integer as an eight-byte record number, and
** append the integer to the key as a varint.  Thus a total of P1+1 entries
** are popped from the stack for this instruction and a single entry is
** pushed back.  The first P1 entries that are popped are strings and the
** last entry (the lowest on the stack) is an integer record number.
*/
case OP_MakeKey3:
case OP_MakeIdxKey3: {
  Mem *pRec;
  Mem *pData0;
  int nField;
  u64 rowid;
  int nByte = 0;
  int addRowid;

  char *zKey;      /* The new key */

 
  nField = pOp->p1;
  pData0 = &pTos[1-nField];
  assert( pData0>=p->aStack );

  addRowid = (pOp->opcode==OP_MakeIdxKey?1:0);

  /* Calculate the number of bytes required for the new index key and
  ** store that number in nByte. Also set rowid to the record number to
  ** append to the index key.
  */
  for(pRec=pData0; pRec<=pTos; pRec++){
    u64 serial_type = sqlite3VdbeSerialType(pRec);



    nByte += sqlite3VarintLen(serial_type);
    nByte += sqlite3VdbeSerialTypeLen(serial_type);
  }
  if( addRowid ){
    pRec = &pData0[-nField];
    assert( pRec>=p->aStack );
    Integerify(pRec);
    rowid = pRec->i;
    nByte += sqlite3VarintLen(rowid);

  }

  /* Allocate space for the new key */
  zKey = sqliteMalloc(nByte);
  if( !zKey ){
    rc = SQLITE_NOMEM;
    goto abort_due_to_error;
  }
  
  /* Build the key in the buffer pointed to by zKey. */
  for(pRec=pData0; pRec<=pTos; pRec++){
    zKey += sqlite3PutVarint(zKey, sqlite3VdbeSerialType(pRec));
    zKey += sqlite3VdbeSerialPut(zKey, pRec);
  }
  if( addRowid ){

    sqlite3PutVarint(zKey, rowid);
  }


  /* Pop the consumed values off the stack and push on the new key. */

  popStack(&pTos, nField+addRowid);

  pTos++;
  pTos->flags = MEM_Blob|MEM_Dyn;

  pTos->z = zKey;
  pTos->n = nByte;




  break;
}

/* Opcode: IncrKey * * *
**
** The top of the stack should contain an index key generated by
** The MakeKey opcode.  This routine increases the least significant
................................................................................
  if( expandCursorArraySize(p, i) ) goto no_mem;
  pCur = &p->aCsr[i];
  sqlite3VdbeCleanupCursor(pCur);
  memset(pCur, 0, sizeof(Cursor));
  pCur->nullRow = 1;
  if( pX==0 ) break;
  do{




    rc = sqlite3BtreeCursor(pX, p2, wrFlag, 0, 0, &pCur->pCursor);

    switch( rc ){
      case SQLITE_BUSY: {
        if( db->xBusyCallback==0 ){
          p->pc = pc;
          p->rc = SQLITE_BUSY;
          p->pTos = &pTos[1 + (pOp->p2<=0)]; /* Operands must remain on stack */
          return SQLITE_BUSY;
................................................................................
** See also: Distinct, NotFound, NotExists, Found
*/
case OP_IsUnique: {
  int i = pOp->p1;
  Mem *pNos = &pTos[-1];
  Cursor *pCx;
  BtCursor *pCrsr;
  int R;

  /* Pop the value R off the top of the stack
  */
  assert( pNos>=p->aStack );
  Integerify(pTos);
  R = pTos->i;
  pTos--;
  assert( i>=0 && i<=p->nCursor );
  pCx = &p->aCsr[i];
  pCrsr = pCx->pCursor;
  if( pCrsr!=0 ){
    int res, rc;
    int v;         /* The record number on the P1 entry that matches K */
    char *zKey;    /* The value of K */
    int nKey;      /* Number of bytes in K */


    /* Make sure K is a string and make zKey point to K
    */
    Stringify(pNos);
    zKey = pNos->z;
    nKey = pNos->n;

    assert( nKey >= 4 );



    /* Search for an entry in P1 where all but the last four bytes match K.
    ** If there is no such entry, jump immediately to P2.
    */
    assert( p->aCsr[i].deferredMoveto==0 );
    rc = sqlite3BtreeMoveto(pCrsr, zKey, nKey-4, &res);
    if( rc!=SQLITE_OK ) goto abort_due_to_error;
    if( res<0 ){
      rc = sqlite3BtreeNext(pCrsr, &res);
      if( res ){
        pc = pOp->p2 - 1;
        break;
      }
    }
    /* FIX ME - the sqlite2BtreeKeyCompare() function is a temporary hack */
    rc = sqlite2BtreeKeyCompare(pCrsr, zKey, nKey-4, 4, &res); 
    if( rc!=SQLITE_OK ) goto abort_due_to_error;
    if( res>0 ){
      pc = pOp->p2 - 1;
      break;
    }

    /* At this point, pCrsr is pointing to an entry in P1 where all but
    ** the last for bytes of the key match K.  Check to see if the last
    ** four bytes of the key are different from R.  If the last four
    ** bytes equal R then jump immediately to P2.
    */
    sqlite3BtreeKey(pCrsr, nKey - 4, 4, (char*)&v);
    v = keyToInt(v);


    if( v==R ){
      pc = pOp->p2 - 1;
      break;
    }

    /* The last four bytes of the key are different from R.  Convert the
    ** last four bytes of the key into an integer and push it onto the
    ** stack.  (These bytes are the record number of an entry that
    ** violates a UNIQUE constraint.)
    */
    pTos++;
    pTos->i = v;
    pTos->flags = MEM_Int;
  }
  break;
}
................................................................................
  assert( i>=0 && i<p->nCursor );
  assert( pTos->flags & MEM_Str );
  if( (pCrsr = p->aCsr[i].pCursor)!=0 ){
    int nKey = pTos->n;
    const char *zKey = pTos->z;
    if( pOp->p2 ){
      int res;

      u64 n;
      assert( nKey >= 4 );





      rc = sqlite3BtreeMoveto(pCrsr, zKey, nKey-4, &res);
      if( rc!=SQLITE_OK ) goto abort_due_to_error;
      while( res!=0 ){
        int c;
        sqlite3BtreeKeySize(pCrsr, &n);
        if( n==nKey
     /* FIX ME - the sqlite2BtreeKeyCompare() function is a temporary hack */
            && sqlite2BtreeKeyCompare(pCrsr, zKey, nKey-4, 4, &c)==SQLITE_OK
           && c==0
        ){
          rc = SQLITE_CONSTRAINT;
          if( pOp->p3 && pOp->p3[0] ){
            sqlite3SetString(&p->zErrMsg, pOp->p3, (char*)0);
          }
          goto abort_due_to_error;
        }
................................................................................
          sqlite3BtreeNext(pCrsr, &res);
          res = +1;
        }else{
          break;
        }
      }
    }

    rc = sqlite3BtreeInsert(pCrsr, zKey, nKey, "", 0);
    assert( p->aCsr[i].deferredMoveto==0 );
  }
  Release(pTos);
  pTos--;
  break;
}
................................................................................
  Release(pTos);
  pTos--;
  break;
}

/* Opcode: IdxRecno P1 * *
**
** Push onto the stack an integer which is the last 4 bytes of the
** the key to the current entry in index P1.  These 4 bytes should
** be the record number of the table entry to which this index entry
** points.
**
** See also: Recno, MakeIdxKey.
*/
case OP_IdxRecno: {
  int i = pOp->p1;
  BtCursor *pCrsr;

  assert( i>=0 && i<p->nCursor );
  pTos++;
  if( (pCrsr = p->aCsr[i].pCursor)!=0 ){
    i64 v;
    u64 sz;



    assert( p->aCsr[i].deferredMoveto==0 );
    assert( p->aCsr[i].intKey==0 );





    sqlite3BtreeKeySize(pCrsr, &sz);
    if( sz<sizeof(i64) ){











      pTos->flags = MEM_Null;
    }else{
      sqlite3BtreeKey(pCrsr, sz - sizeof(i64), sizeof(i64), (char*)&v);
      v = keyToInt(v);
      pTos->i = v;








      pTos->flags = MEM_Int;

    }
  }else{
    pTos->flags = MEM_Null;
  }
  break;
}

................................................................................
  assert( i>=0 && i<p->nCursor );
  assert( pTos>=p->aStack );
  if( (pCrsr = p->aCsr[i].pCursor)!=0 ){
    int res, rc;
 
    Stringify(pTos);
    assert( p->aCsr[i].deferredMoveto==0 );
    /* FIX ME - the sqlite2BtreeKeyCompare() function is a temporary hack */
    rc = sqlite2BtreeKeyCompare(pCrsr, pTos->z, pTos->n, 4, &res);
    if( rc!=SQLITE_OK ){
      break;
    }
    if( pOp->opcode==OP_IdxLT ){
      res = -res;
    }else if( pOp->opcode==OP_IdxGE ){
      res++;

**
** 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.285 2004/05/13 05:16:16 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................
** P3 is a string that is P1 characters long.  Each character is either
** an 'n' or a 't' to indicates if the argument should be numeric or
** text.  The first character corresponds to the lowest element on the
** stack.  If P3 is null then all arguments are assumed to be numeric.
**
** See also:  MakeKey, SortMakeKey
*/
case OP_MakeIdxKey2:
case OP_MakeKey2: {
  char *zNewKey;
  int nByte;
  int nField;
  int addRowid;
  int i, j;
  int containsNull = 0;
  Mem *pRec;
................................................................................
  }else{
    pTos->z = zNewKey;
    pTos->flags = MEM_Str | MEM_Dyn;
  }
  break;
}

/* Opcode: MakeIdxKey3 P1 P2 *
**
** Convert the top P1 entries of the stack into a single entry suitable
** for use as the key in an index.  In addition, take one additional integer
** off of the stack, treat that integer as an eight-byte record number, and
** append the integer to the key as a varint.  Thus a total of P1+1 entries
** are popped from the stack for this instruction and a single entry is
** pushed back.  The first P1 entries that are popped are strings and the
** last entry (the lowest on the stack) is an integer record number.
*/
case OP_MakeKey:
case OP_MakeIdxKey: {
  Mem *pRec;
  Mem *pData0;
  int nField;
  u64 rowid;
  int nByte = 0;
  int addRowid;
  int containsNull = 0;
  char *zKey;      /* The new key */
  int offset = 0;
 
  nField = pOp->p1;
  pData0 = &pTos[1-nField];
  assert( pData0>=p->aStack );

  addRowid = ((pOp->opcode==OP_MakeIdxKey)?1:0);

  /* Calculate the number of bytes required for the new index key and
  ** store that number in nByte. Also set rowid to the record number to
  ** append to the index key.
  */
  for(pRec=pData0; pRec<=pTos; pRec++){
    u64 serial_type = sqlite3VdbeSerialType(pRec);
    if( serial_type==0 ){
      containsNull = 1;
    }
    nByte += sqlite3VarintLen(serial_type);
    nByte += sqlite3VdbeSerialTypeLen(serial_type);
  }
  if( addRowid ){
    pRec = &pTos[0-nField];
    assert( pRec>=p->aStack );
    Integerify(pRec);
    rowid = pRec->i;
    nByte += sqlite3VarintLen(rowid);
    nByte++;
  }

  /* Allocate space for the new key */
  zKey = (char *)sqliteMalloc(nByte);
  if( !zKey ){
    rc = SQLITE_NOMEM;
    goto abort_due_to_error;
  }
  
  /* Build the key in the buffer pointed to by zKey. */
  for(pRec=pData0; pRec<=pTos; pRec++){
    offset += sqlite3PutVarint(&zKey[offset], sqlite3VdbeSerialType(pRec));
    offset += sqlite3VdbeSerialPut(&zKey[offset], pRec);
  }
  if( addRowid ){
    zKey[offset++] = '\0';
    offset += sqlite3PutVarint(&zKey[offset], rowid);
  }
  assert( offset==nByte );

  /* Pop the consumed values off the stack and push on the new key. */
  if( addRowid||(pOp->p2==0) ){
    popStack(&pTos, nField+addRowid);
  }
  pTos++;

  pTos->flags = MEM_Str|MEM_Dyn; /* TODO: should eventually be MEM_Blob */
  pTos->z = zKey;
  pTos->n = nByte;

  if( pOp->p2 && containsNull ){
    pc = pOp->p2 - 1;
  }
  break;
}

/* Opcode: IncrKey * * *
**
** The top of the stack should contain an index key generated by
** The MakeKey opcode.  This routine increases the least significant
................................................................................
  if( expandCursorArraySize(p, i) ) goto no_mem;
  pCur = &p->aCsr[i];
  sqlite3VdbeCleanupCursor(pCur);
  memset(pCur, 0, sizeof(Cursor));
  pCur->nullRow = 1;
  if( pX==0 ) break;
  do{
    /* When opening cursors, always supply the comparison function
    ** sqlite3VdbeKeyCompare(). If the table being opened is of type
    ** INTKEY, the btree layer won't call the comparison function anyway.
    */
    rc = sqlite3BtreeCursor(pX, p2, wrFlag, sqlite3VdbeKeyCompare, 0,
        &pCur->pCursor);
    switch( rc ){
      case SQLITE_BUSY: {
        if( db->xBusyCallback==0 ){
          p->pc = pc;
          p->rc = SQLITE_BUSY;
          p->pTos = &pTos[1 + (pOp->p2<=0)]; /* Operands must remain on stack */
          return SQLITE_BUSY;
................................................................................
** See also: Distinct, NotFound, NotExists, Found
*/
case OP_IsUnique: {
  int i = pOp->p1;
  Mem *pNos = &pTos[-1];
  Cursor *pCx;
  BtCursor *pCrsr;
  i64 R;

  /* Pop the value R off the top of the stack
  */
  assert( pNos>=p->aStack );
  Integerify(pTos);
  R = pTos->i;
  pTos--;
  assert( i>=0 && i<=p->nCursor );
  pCx = &p->aCsr[i];
  pCrsr = pCx->pCursor;
  if( pCrsr!=0 ){
    int res, rc;
    i64 v;         /* The record number on the P1 entry that matches K */
    char *zKey;    /* The value of K */
    int nKey;      /* Number of bytes in K */
    int len;       /* Number of bytes in K without the rowid at the end */

    /* Make sure K is a string and make zKey point to K
    */
    Stringify(pNos);
    zKey = pNos->z;
    nKey = pNos->n;

    assert( nKey >= 2 );
    len = nKey-2;
    while( zKey[len] && --len );

    /* Search for an entry in P1 where all but the last four bytes match K.
    ** If there is no such entry, jump immediately to P2.
    */
    assert( p->aCsr[i].deferredMoveto==0 );
    rc = sqlite3BtreeMoveto(pCrsr, zKey, len, &res);
    if( rc!=SQLITE_OK ) goto abort_due_to_error;
    if( res<0 ){
      rc = sqlite3BtreeNext(pCrsr, &res);
      if( res ){
        pc = pOp->p2 - 1;
        break;
      }
    }
    rc = sqlite3VdbeIdxKeyCompare(pCrsr, len, zKey, 0, &res); 

    if( rc!=SQLITE_OK ) goto abort_due_to_error;
    if( res>0 ){
      pc = pOp->p2 - 1;
      break;
    }

    /* At this point, pCrsr is pointing to an entry in P1 where all but
    ** the final varint (the rowid) matches K.  Check to see if the
    ** final varint is different from R.  If it equals R then jump
    ** immediately to P2.
    */
    rc = sqlite3VdbeIdxRowid(pCrsr, &v);
    if( rc!=SQLITE_OK ){
      goto abort_due_to_error;
    }
    if( v==R ){
      pc = pOp->p2 - 1;
      break;
    }

    /* The final varint of the key is different from R.  Push it onto

    ** the stack.  (The record number of an entry that violates a UNIQUE
    ** constraint.)
    */
    pTos++;
    pTos->i = v;
    pTos->flags = MEM_Int;
  }
  break;
}
................................................................................
  assert( i>=0 && i<p->nCursor );
  assert( pTos->flags & MEM_Str );
  if( (pCrsr = p->aCsr[i].pCursor)!=0 ){
    int nKey = pTos->n;
    const char *zKey = pTos->z;
    if( pOp->p2 ){
      int res;
      int len;
      u64 n;

   
      /* 'len' is the length of the key minus the rowid at the end */
      len = nKey-2;
      while( zKey[len] && --len );

      rc = sqlite3BtreeMoveto(pCrsr, zKey, len, &res);
      if( rc!=SQLITE_OK ) goto abort_due_to_error;
      while( res!=0 ){
        int c;
        sqlite3BtreeKeySize(pCrsr, &n);
        if( n==nKey 
            && sqlite3VdbeIdxKeyCompare(pCrsr, len, zKey, 0, &c)==SQLITE_OK

            && c==0
        ){
          rc = SQLITE_CONSTRAINT;
          if( pOp->p3 && pOp->p3[0] ){
            sqlite3SetString(&p->zErrMsg, pOp->p3, (char*)0);
          }
          goto abort_due_to_error;
        }
................................................................................
          sqlite3BtreeNext(pCrsr, &res);
          res = +1;
        }else{
          break;
        }
      }
    }
    assert( p->aCsr[i].intKey==0 );
    rc = sqlite3BtreeInsert(pCrsr, zKey, nKey, "", 0);
    assert( p->aCsr[i].deferredMoveto==0 );
  }
  Release(pTos);
  pTos--;
  break;
}
................................................................................
  Release(pTos);
  pTos--;
  break;
}

/* Opcode: IdxRecno P1 * *
**
** Push onto the stack an integer which is the varint located at the
** end of the index key pointed to by cursor P1.  These integer should be
** the record number of the table entry to which this index entry points.

**
** See also: Recno, MakeIdxKey.
*/
case OP_IdxRecno: {
  int i = pOp->p1;
  BtCursor *pCrsr;

  assert( i>=0 && i<p->nCursor );
  pTos++;
  if( (pCrsr = p->aCsr[i].pCursor)!=0 ){

    u64 sz;
    int len;
    char buf[9];

    assert( p->aCsr[i].deferredMoveto==0 );
    assert( p->aCsr[i].intKey==0 );

    /* Read the final 9 bytes of the key into buf[]. If the whole key is
    ** less than 9 bytes then just load the whole thing. Set len to the 
    ** number of bytes read.
    */
    sqlite3BtreeKeySize(pCrsr, &sz);

    len = ((sz>9)?9:sz);
    rc = sqlite3BtreeKey(pCrsr, sz-len, len, buf);
    if( rc!=SQLITE_OK ){
      goto abort_due_to_error;
    }

    len--;
    if( buf[len]&0x80 ){
      /* If the last byte read has the 0x80 bit set, then the key does
      ** not end with a varint. Push a NULL onto the stack instead.
      */
      pTos->flags = MEM_Null;
    }else{



      /* Find the start of the varint by searching backwards for a 0x00
      ** byte. If one does not exists, then intepret the whole 9 bytes as a
      ** varint.
      */
      while( len && buf[len-1] ){
        len--;
      }
      sqlite3GetVarint(&buf[len], &sz);
      pTos->flags = MEM_Int;
      pTos->i = sz;
    }
  }else{
    pTos->flags = MEM_Null;
  }
  break;
}

................................................................................
  assert( i>=0 && i<p->nCursor );
  assert( pTos>=p->aStack );
  if( (pCrsr = p->aCsr[i].pCursor)!=0 ){
    int res, rc;
 
    Stringify(pTos);
    assert( p->aCsr[i].deferredMoveto==0 );

    rc = sqlite3VdbeIdxKeyCompare(pCrsr, pTos->n, pTos->z, 0, &res);
    if( rc!=SQLITE_OK ){
      break;
    }
    if( pOp->opcode==OP_IdxLT ){
      res = -res;
    }else if( pOp->opcode==OP_IdxGE ){
      res++;

*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.74 2004/05/12 11:24:03 danielk1977 Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines
................................................................................
int sqlite3VdbeFinalize(Vdbe*,char**);
void sqlite3VdbeResolveLabel(Vdbe*, int);
int sqlite3VdbeCurrentAddr(Vdbe*);
void sqlite3VdbeTrace(Vdbe*,FILE*);
void sqlite3VdbeCompressSpace(Vdbe*,int);
int sqlite3VdbeReset(Vdbe*,char **);
int sqliteVdbeSetVariables(Vdbe*,int,const char**);
int sqlite3VdbeKeyCompare(void*,int,const unsigned char*,int,
    const unsigned char*);

#endif

*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.75 2004/05/13 05:16:17 danielk1977 Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines
................................................................................
int sqlite3VdbeFinalize(Vdbe*,char**);
void sqlite3VdbeResolveLabel(Vdbe*, int);
int sqlite3VdbeCurrentAddr(Vdbe*);
void sqlite3VdbeTrace(Vdbe*,FILE*);
void sqlite3VdbeCompressSpace(Vdbe*,int);
int sqlite3VdbeReset(Vdbe*,char **);
int sqliteVdbeSetVariables(Vdbe*,int,const char**);
int sqlite3VdbeKeyCompare(void*,int,const void*,int, const void*);


#endif

#endif
int sqlite3VdbeSerialTypeLen(u64);
u64 sqlite3VdbeSerialType(const Mem *);
int sqlite3VdbeSerialPut(unsigned char *, const Mem *);
int sqlite3VdbeSerialGet(const unsigned char *, u64, Mem *);

int sqlite2BtreeKeyCompare(BtCursor *, const void *, int, int, int *);

#endif
int sqlite3VdbeSerialTypeLen(u64);
u64 sqlite3VdbeSerialType(const Mem *);
int sqlite3VdbeSerialPut(unsigned char *, const Mem *);
int sqlite3VdbeSerialGet(const unsigned char *, u64, Mem *);

int sqlite2BtreeKeyCompare(BtCursor *, const void *, int, int, int *);
int sqlite3VdbeIdxKeyCompare(BtCursor*, int , const unsigned char*, int, int*);
int sqlite3VdbeIdxRowid(BtCursor *, i64 *);

#    May you find forgiveness for yourself and forgive others.
#    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 INSERT statement.
#
# $Id: insert.test,v 1.16 2004/05/11 09:50:02 danielk1977 Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
................................................................................
  execsql {
    DELETE FROM test2;
    CREATE INDEX index9 ON test2(f1,f2);
    CREATE INDEX indext ON test2(f4,f5);
    SELECT * from test2;
  }
} {}




do_test insert-3.2 {
  execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')}
  execsql {SELECT * FROM test2 WHERE f1=111 AND f2=-3.33}
} {111 -3.33 hi hum {}}
do_test insert-3.3 {
  execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')}
  execsql {SELECT * FROM test2 WHERE f1=111 AND f2=-3.33}
} {111 -3.33 hi hum {}}
do_test insert-3.4 {
  execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44}
} {22 -4.44 hi abc-123 wham}
integrity_check insert-3.5

# Test of expressions in the VALUES clause

#    May you find forgiveness for yourself and forgive others.
#    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 INSERT statement.
#
# $Id: insert.test,v 1.17 2004/05/13 05:16:17 danielk1977 Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
................................................................................
  execsql {
    DELETE FROM test2;
    CREATE INDEX index9 ON test2(f1,f2);
    CREATE INDEX indext ON test2(f4,f5);
    SELECT * from test2;
  }
} {}

# Update for sqlite v3:
# Change the 111 to '111' in the following two test cases, because
# the default value is being inserted as a string. TODO: It shouldn't be.
do_test insert-3.2 {
  execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')}
  execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33}
} {111 -3.33 hi hum {}}
do_test insert-3.3 {
  execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')}
  execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33}
} {111 -3.33 hi hum {}}
do_test insert-3.4 {
  execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44}
} {22 -4.44 hi abc-123 wham}
integrity_check insert-3.5

# Test of expressions in the VALUES clause

#    May you find forgiveness for yourself and forgive others.
#    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 SELECT statement.
#
# $Id: select2.test,v 1.18 2002/04/02 13:26:11 drh Exp $

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

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}
................................................................................
  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
} {29500}

do_test select2-3.1 {
  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
} {500}




do_test select2-3.2a {
  execsql {CREATE INDEX idx1 ON tbl2(f2)}
} {}

do_test select2-3.2b {
  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
} {500}
do_test select2-3.2c {
  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
} {500}
do_test select2-3.2d {
  set sqlite_search_count 0
  execsql {SELECT * FROM tbl2 WHERE 1000=f2}
  set sqlite_search_count
} {3}
do_test select2-3.2e {
  set sqlite_search_count 0
  execsql {SELECT * FROM tbl2 WHERE f2=1000}
  set sqlite_search_count
} {3}

# Make sure queries run faster with an index than without
#
do_test select2-3.3 {
  execsql {DROP INDEX idx1}

#    May you find forgiveness for yourself and forgive others.
#    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 SELECT statement.
#
# $Id: select2.test,v 1.19 2004/05/13 05:16:17 danielk1977 Exp $

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

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}
................................................................................
  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}
} {29500}

do_test select2-3.1 {
  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
} {500}

# SQLite v3: Change the expressions in the following four test cases
# from 1000=f2 to '1000'=f2. This is because fields read in using
# the COPY command have manifest type TEXT.
do_test select2-3.2a {
  execsql {CREATE INDEX idx1 ON tbl2(f2)}
} {}

do_test select2-3.2b {
  execsql {SELECT f1 FROM tbl2 WHERE '1000'=f2}
} {500}
do_test select2-3.2c {
  execsql {SELECT f1 FROM tbl2 WHERE f2='1000'}
} {500}
do_test select2-3.2d {
  set sqlite_search_count 0
  execsql {SELECT * FROM tbl2 WHERE '1000'=f2}
  set sqlite_search_count
} {3}
do_test select2-3.2e {
  set sqlite_search_count 0
  execsql {SELECT * FROM tbl2 WHERE f2='1000'}
  set sqlite_search_count
} {3}

# Make sure queries run faster with an index than without
#
do_test select2-3.3 {
  execsql {DROP INDEX idx1}

#    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 UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.13 2003/02/02 12:41:27 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]
................................................................................
  execsql {
    SELECT DISTINCT log FROM t1
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {5}





do_test select4-4.1.2 {
  execsql {
    SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {5 6}
do_test select4-4.1.3 {
  execsql {
    CREATE TABLE t2 AS
      SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
      INTERSECT
      SELECT n FROM t1 WHERE log=3
      ORDER BY log;
    SELECT * FROM t2;
  }
} {5 6}
execsql {DROP TABLE t2}
do_test select4-4.1.4 {
  execsql {
    CREATE TABLE t2 AS
      SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
      INTERSECT
      SELECT n FROM t1 WHERE log=3
      ORDER BY log DESC;
    SELECT * FROM t2;
  }
} {6 5}
execsql {DROP TABLE t2}
................................................................................
    SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
    ORDER BY n LIMIT 2
  }
} {n 1 log 0 n 2 log 1}

# Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
#


do_test select4-8.1 {
  execsql {
    BEGIN;
    CREATE TABLE t3(a text, b float, c text);
    INSERT INTO t3 VALUES(1, 1.1, '1.1');
    INSERT INTO t3 VALUES(2, 1.10, '1.10');
    INSERT INTO t3 VALUES(3, 1.10, '1.1');
    INSERT INTO t3 VALUES(4, 1.1, '1.10');
    INSERT INTO t3 VALUES(5, 1.2, '1.2');
    INSERT INTO t3 VALUES(6, 1.3, '1.3');
    COMMIT;
  }
  execsql {
    SELECT DISTINCT b FROM t3 ORDER BY c;
  }
} {1.1 1.2 1.3}
do_test select4-8.2 {

#    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 UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.14 2004/05/13 05:16:17 danielk1977 Exp $

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

# Build some test data
#
set fd [open data1.txt w]
................................................................................
  execsql {
    SELECT DISTINCT log FROM t1
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {5}

# Update for sqlite 3:
# Change the "UNION ALL SELECT 6" in each of the select statements
# for the next three test cases to "UNION ALL SELECT '6'". This is
# to accomadate manifest typing.
do_test select4-4.1.2 {
  execsql {
    SELECT DISTINCT log FROM t1 UNION ALL SELECT '6'
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {5 6}
do_test select4-4.1.3 {
  execsql {
    CREATE TABLE t2 AS
      SELECT DISTINCT log FROM t1 UNION ALL SELECT '6'
      INTERSECT
      SELECT n FROM t1 WHERE log=3
      ORDER BY log;
    SELECT * FROM t2;
  }
} {5 6}
execsql {DROP TABLE t2}
do_test select4-4.1.4 {
  execsql {
    CREATE TABLE t2 AS
      SELECT DISTINCT log FROM t1 UNION ALL SELECT '6'
      INTERSECT
      SELECT n FROM t1 WHERE log=3
      ORDER BY log DESC;
    SELECT * FROM t2;
  }
} {6 5}
execsql {DROP TABLE t2}
................................................................................
    SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
    ORDER BY n LIMIT 2
  }
} {n 1 log 0 n 2 log 1}

# Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
#
# Update for sqlite v3:
# Insert X+0.0 instead of X to make sure X has manifest type NUMERIC.
do_test select4-8.1 {
  execsql {
    BEGIN;
    CREATE TABLE t3(a text, b float, c text);
    INSERT INTO t3 VALUES(1, 1.1 + 0.0, '1.1');
    INSERT INTO t3 VALUES(2, 1.10 + 0.0, '1.10');
    INSERT INTO t3 VALUES(3, 1.10 + 0.0, '1.1');
    INSERT INTO t3 VALUES(4, 1.1 + 0.0, '1.10');
    INSERT INTO t3 VALUES(5, 1.2 + 0.0, '1.2');
    INSERT INTO t3 VALUES(6, 1.3 + 0.0, '1.3');
    COMMIT;
  }
  execsql {
    SELECT DISTINCT b FROM t3 ORDER BY c;
  }
} {1.1 1.2 1.3}
do_test select4-8.2 {