SQLite

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.259 2005/09/01 12:16:29 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.

/*
** Insert code into "v" that will push the record on the top of the
** stack into the sorter.
*/
static void pushOntoSorter(Parse *pParse, Vdbe *v, ExprList *pOrderBy){
  sqlite3ExprCodeExprList(pParse, pOrderBy);
  sqlite3VdbeAddOp(v, OP_Sequence, pOrderBy->iTab, 0);
  sqlite3VdbeAddOp(v, OP_Pull, pOrderBy->nExpr + 1, 0);
  sqlite3VdbeAddOp(v, OP_MakeRecord, pOrderBy->nExpr + 2, 0);
  sqlite3VdbeAddOp(v, OP_IdxInsert, pOrderBy->iTab, 0);
}

/*
** Add code to implement the OFFSET and LIMIT
*/
static void codeLimiter(

  int iTab;
  ExprList *pOrderBy = p->pOrderBy;

  if( eDest==SRT_Sorter ) return;
  iTab = pOrderBy->iTab;
  addr = 1 + sqlite3VdbeAddOp(v, OP_Sort, iTab, brk);
  codeLimiter(v, p, cont, brk, 0);
  sqlite3VdbeAddOp(v, OP_Column, iTab, pOrderBy->nExpr + 1);
  switch( eDest ){
    case SRT_Table:
    case SRT_TempTable: {
      sqlite3VdbeAddOp(v, OP_NewRowid, iParm, 0);
      sqlite3VdbeAddOp(v, OP_Pull, 1, 0);
      sqlite3VdbeAddOp(v, OP_Insert, iParm, 0);
      break;

    p->iOffset = iMem;
  }
}

/*
** Allocate a virtual index to use for sorting.
*/
static void createSortingIndex(Parse *pParse, Select *p, ExprList *pOrderBy){
  if( pOrderBy ){
    int addr;
    assert( pOrderBy->iTab==0 );
    pOrderBy->iTab = pParse->nTab++;
    addr = sqlite3VdbeAddOp(pParse->pVdbe, OP_OpenVirtual,
                            pOrderBy->iTab, pOrderBy->nExpr+1);
    assert( p->addrOpenVirt[2] == -1 );

    int i;                        /* Loop counter */
    KeyInfo *pKeyInfo;            /* Collating sequence for the result set */
    Select *pLoop;                /* For looping through SELECT statements */
    CollSeq **apColl;
    CollSeq **aCopy;

    assert( p->pRightmost==p );
    pKeyInfo = sqliteMalloc(sizeof(*pKeyInfo)+nCol*2*sizeof(CollSeq*) + nCol);
    if( !pKeyInfo ){
      rc = SQLITE_NOMEM;
      goto multi_select_end;
    }

    pKeyInfo->enc = pParse->db->enc;
    pKeyInfo->nField = nCol;

      }
    }

    if( pOrderBy ){
      struct ExprList_item *pOTerm = pOrderBy->a;
      int nExpr = pOrderBy->nExpr;
      int addr;
      u8 *pSortOrder;

      aCopy = (CollSeq**)&pKeyInfo[1];
      pSortOrder = pKeyInfo->aSortOrder = (u8*)&aCopy[nExpr];
      memcpy(aCopy, pKeyInfo->aColl, nCol*sizeof(CollSeq*));
      apColl = pKeyInfo->aColl;
      for(i=0; i<pOrderBy->nExpr; i++, pOTerm++, apColl++, pSortOrder++){
        Expr *pExpr = pOTerm->pExpr;
        char *zName = pOTerm->zName;
        assert( pExpr->op==TK_COLUMN && pExpr->iColumn<nCol );
        if( zName ){
          *apColl = sqlite3LocateCollSeq(pParse, zName, -1);
        }else{
          *apColl = aCopy[pExpr->iColumn];
        }
        *pSortOrder = pOTerm->sortOrder;
      }
      assert( p->pRightmost==p );
      assert( p->addrOpenVirt[2]>=0 );
      addr = p->addrOpenVirt[2];
      sqlite3VdbeChangeP2(v, addr, p->pEList->nExpr+2);
      sqlite3VdbeChangeP3(v, addr, (char*)pKeyInfo, P3_KEYINFO_HANDOFF);
      pKeyInfo = 0;
      generateSortTail(pParse, p, v, p->pEList->nExpr, eDest, iParm);
    }

    sqliteFree(pKeyInfo);
  }

multi_select_end:

      }
    }
    if( pParse->nErr ){
      goto select_end;
    }
    pKeyInfo = keyInfoFromExprList(pParse, pOrderBy);
    pOrderBy->iTab = pParse->nTab++;
    addr = sqlite3VdbeOp3(v, OP_OpenVirtual, pOrderBy->iTab, pOrderBy->nExpr+2, 
                        (char*)pKeyInfo, P3_KEYINFO_HANDOFF);
    p->addrOpenVirt[2] = addr;
  }

  /* Set the limiter.
  */
  computeLimitRegisters(pParse, p);

**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing the printf() interface to SQLite.  This code
** is not included in the SQLite library.  It is used for automated
** testing of the SQLite library.
**
** $Id: test1.c,v 1.158 2005/09/01 12:16:29 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

  void *pCtx, 
  int nA, const void *zA,
  int nB, const void *zB
){
  Tcl_Interp *i = pTestCollateInterp;
  int encin = (int)pCtx;
  int res;
  int n;

  sqlite3_value *pVal;
  Tcl_Obj *pX;

  pX = Tcl_NewStringObj("test_collate", -1);
  Tcl_IncrRefCount(pX);

      break;
    default:
      assert(0);
  }

  pVal = sqlite3ValueNew();
  sqlite3ValueSetStr(pVal, nA, zA, encin, SQLITE_STATIC);
  n = sqlite3_value_bytes(pVal);
  Tcl_ListObjAppendElement(i,pX,Tcl_NewStringObj(sqlite3_value_text(pVal),n));
  sqlite3ValueSetStr(pVal, nB, zB, encin, SQLITE_STATIC);
  n = sqlite3_value_bytes(pVal);
  Tcl_ListObjAppendElement(i,pX,Tcl_NewStringObj(sqlite3_value_text(pVal),n));
  sqlite3ValueFree(pVal);

  Tcl_EvalObjEx(i, pX, 0);
  Tcl_DecrRefCount(pX);
  Tcl_GetIntFromObj(i, Tcl_GetObjResult(i), &res);
  return 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.480 2005/09/01 12:16:29 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*

      pC->rowidIsValid = 0;
    }
  }
  Release(pTos);
  pTos--;
  break;
}

/* Opcode: Sequence P1 * *
**
** Push an integer onto the stack which is the next available
** sequence number for cursor P1.  The sequence number on the
** cursor is incremented after the push.
*/
case OP_Sequence: {
  int i = pOp->p1;
  assert( pTos>=p->aStack );
  assert( i>=0 && i<p->nCursor );
  assert( p->apCsr[i]!=0 );
  pTos++;
  pTos->i = p->apCsr[i]->seqCount++;
  pTos->flags = MEM_Int;
  break;
}


/* Opcode: NewRowid P1 P2 *
**
** Get a new integer record number (a.k.a "rowid") used as the key to a table.
** The record number is not previously used as a key in the database
** table that cursor P1 points to.  The new record number is pushed 
** onto the stack.

** end.  We use the OP_Sort opcode instead of OP_Rewind to do the
** rewinding so that the global variable will be incremented and
** regression tests can determine whether or not the optimizer is
** correctly optimizing out sorts.
*/
case OP_Sort: {        /* no-push */
  sqlite3_sort_count++;
  sqlite3_search_count--;
  /* Fall through into OP_Rewind */
}
/* Opcode: Rewind P1 P2 *
**
** The next use of the Rowid or Column or Next instruction for P1 
** will refer to the first entry in the database table or index.
** If the table or index is empty and P2>0, then jump immediately to P2.

  Btree *pBt;           /* Separate file holding temporary table */
  int nData;            /* Number of bytes in pData */
  char *pData;          /* Data for a NEW or OLD pseudo-table */
  i64 iKey;             /* Key for the NEW or OLD pseudo-table row */
  u8 *pIncrKey;         /* Pointer to pKeyInfo->incrKey */
  KeyInfo *pKeyInfo;    /* Info about index keys needed by index cursors */
  int nField;           /* Number of fields in the header */
  i64 seqCount;         /* Sequence counter */

  /* Cached information about the header for the data record that the
  ** cursor is currently pointing to.  Only valid if cacheValid is true.
  ** zRow might point to (ephemeral) data for the current row, or it might
  ** be NULL. */
  Bool cacheValid;      /* True if the cache is valid */
  int payloadSize;      /* Total number of bytes in the record */

  }
  if( zP3==0 ){
    pOp->p3 = 0;
    pOp->p3type = P3_NOTUSED;
  }else if( n==P3_KEYINFO ){
    KeyInfo *pKeyInfo;
    int nField, nByte;

    /* KeyInfo structures that include an KeyInfo.aSortOrder are always
    ** sent in using P3_KEYINFO_HANDOFF.  The KeyInfo.aSortOrder array
    ** is not duplicated when P3_KEYINFO is used. */
    /* assert( pKeyInfo->aSortOrder==0 ); */
    nField = ((KeyInfo*)zP3)->nField;
    nByte = sizeof(*pKeyInfo) + (nField-1)*sizeof(pKeyInfo->aColl[0]);
    pKeyInfo = sqliteMallocRaw( nByte );
    pOp->p3 = (char*)pKeyInfo;
    if( pKeyInfo ){
      memcpy(pKeyInfo, zP3, nByte);
      pOp->p3type = P3_KEYINFO;

#
#***********************************************************************
# This file implements regression tests for SQLite library.  The focus of
# this file is testing the SQLite routines used for converting between the
# various suported unicode encodings (UTF-8, UTF-16, UTF-16le and
# UTF-16be).
#
# $Id: enc2.test,v 1.23 2005/09/01 12:16:29 drh Exp $

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

# If UTF16 support is disabled, ignore the tests in this file
#
ifcapable {!utf16} {

set ::values [list one two three four five]
set ::test_collate_enc INVALID
proc test_collate {enc lhs rhs} {
  set ::test_collate_enc $enc
  set l [lsearch -exact $::values $lhs]
  set r [lsearch -exact $::values $rhs]
  set res [expr $l - $r]
  # puts "enc=$enc lhs=$lhs/$l rhs=$rhs/$r res=$res"
  return $res
}

file delete -force test.db
set DB [sqlite3 db test.db]
do_test enc2-5.0 {
  execsql {

# 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 implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.34 2005/09/01 12:16:29 drh Exp $

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

# Build some test data
#
do_test where-1.0 {

# Omit these tests if the build is not capable of sub-queries.
#
ifcapable subquery {
  do_test where-5.1 {
    count {
      SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 4}
  do_test where-5.2 {
    count {
      SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 199}
  do_test where-5.3 {
    count {
      SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 14}
  do_test where-5.4 {
    count {
      SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1;
    }
  } {1 0 4 2 1 9 3 1 16 199}
  do_test where-5.5 {
    count {

      SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
    }
  } {2 1 9 3 1 16 7}
  do_test where-5.14 {
    count {
      SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
    }
  } {2 1 9 8}
  do_test where-5.15 {
    count {
      SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1;
    }
  } {2 1 9 3 1 16 11}
}