# This is how we compile
#
TCCX = $(TCC) $(OPTS) $(THREADSAFE) $(USLEEP) -I. -I$(TOP)/src

# Object files for the SQLite library.
#
LIBOBJ = attach.o auth.o btree.o build.o copy.o date.o delete.o \
         expr.o func.o hash.o insert.o \
         main.o opcodes.o os_mac.o os_unix.o os_win.o \
         pager.o parse.o pragma.o printf.o random.o \
         select.o table.o tokenize.o trigger.o update.o util.o vacuum.o \
         vdbe.o vdbeapi.o vdbeaux.o vdbemem.o \
         where.o tclsqlite.o utf.o legacy.o

................................................................................
#
SRC = \
  $(TOP)/src/attach.c \
  $(TOP)/src/auth.c \
  $(TOP)/src/btree.c \
  $(TOP)/src/btree.h \
  $(TOP)/src/build.c \
  $(TOP)/src/copy.c \
  $(TOP)/src/date.c \
  $(TOP)/src/delete.c \
  $(TOP)/src/encode.c \
  $(TOP)/src/expr.c \
  $(TOP)/src/func.c \
  $(TOP)/src/hash.c \
  $(TOP)/src/hash.h \
................................................................................
	echo '"#define SQLITE_PTR_SZ %d",sizeof(char*));' >>temp.c
	echo 'exit(0);}' >>temp.c
	$(BCC) -o temp temp.c
	./temp >config.h
	echo >>config.h
	rm -f temp.c temp

copy.o:	$(TOP)/src/copy.c $(HDR)
	$(TCCX) -c $(TOP)/src/copy.c

date.o:	$(TOP)/src/date.c $(HDR)
	$(TCCX) -c $(TOP)/src/date.c

delete.o:	$(TOP)/src/delete.c $(HDR)
	$(TCCX) -c $(TOP)/src/delete.c

encode.o:	$(TOP)/src/encode.c

# This is how we compile
#
TCCX = $(TCC) $(OPTS) $(THREADSAFE) $(USLEEP) -I. -I$(TOP)/src

# Object files for the SQLite library.
#
LIBOBJ = attach.o auth.o btree.o build.o date.o delete.o \
         expr.o func.o hash.o insert.o \
         main.o opcodes.o os_mac.o os_unix.o os_win.o \
         pager.o parse.o pragma.o printf.o random.o \
         select.o table.o tokenize.o trigger.o update.o util.o vacuum.o \
         vdbe.o vdbeapi.o vdbeaux.o vdbemem.o \
         where.o tclsqlite.o utf.o legacy.o

................................................................................
#
SRC = \
  $(TOP)/src/attach.c \
  $(TOP)/src/auth.c \
  $(TOP)/src/btree.c \
  $(TOP)/src/btree.h \
  $(TOP)/src/build.c \

  $(TOP)/src/date.c \
  $(TOP)/src/delete.c \
  $(TOP)/src/encode.c \
  $(TOP)/src/expr.c \
  $(TOP)/src/func.c \
  $(TOP)/src/hash.c \
  $(TOP)/src/hash.h \
................................................................................
	echo '"#define SQLITE_PTR_SZ %d",sizeof(char*));' >>temp.c
	echo 'exit(0);}' >>temp.c
	$(BCC) -o temp temp.c
	./temp >config.h
	echo >>config.h
	rm -f temp.c temp




date.o:	$(TOP)/src/date.c $(HDR)
	$(TCCX) -c $(TOP)/src/date.c

delete.o:	$(TOP)/src/delete.c $(HDR)
	$(TCCX) -c $(TOP)/src/delete.c

encode.o:	$(TOP)/src/encode.c

/*
** 2003 April 6
**
** 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 contains code used to implement the COPY command.
**
** $Id: copy.c,v 1.13 2004/05/26 10:11:05 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The COPY command is for compatibility with PostgreSQL and specificially
** for the ability to read the output of pg_dump.  The format is as
** follows:
**
**    COPY table FROM file [USING DELIMITERS string]
**
** "table" is an existing table name.  We will read lines of code from
** file to fill this table with data.  File might be "stdin".  The optional
** delimiter string identifies the field separators.  The default is a tab.
*/
void sqlite3Copy(
  Parse *pParse,       /* The parser context */
  SrcList *pTableName, /* The name of the table into which we will insert */
  Token *pFilename,    /* The file from which to obtain information */
  Token *pDelimiter,   /* Use this as the field delimiter */
  int onError          /* What to do if a constraint fails */
){
  Table *pTab;
  int i;
  Vdbe *v;
  int addr, end;
  char *zFile = 0;
  const char *zDb;
  sqlite *db = pParse->db;


  if( sqlite3_malloc_failed  ) goto copy_cleanup;
  assert( pTableName->nSrc==1 );
  pTab = sqlite3SrcListLookup(pParse, pTableName);
  if( pTab==0 || sqlite3IsReadOnly(pParse, pTab, 0) ) goto copy_cleanup;
  zFile = sqliteStrNDup(pFilename->z, pFilename->n);
  sqlite3Dequote(zFile);
  assert( pTab->iDb<db->nDb );
  zDb = db->aDb[pTab->iDb].zName;
  if( sqlite3AuthCheck(pParse, SQLITE_INSERT, pTab->zName, 0, zDb)
      || sqlite3AuthCheck(pParse, SQLITE_COPY, pTab->zName, zFile, zDb) ){
    goto copy_cleanup;
  }
  v = sqlite3GetVdbe(pParse);
  if( v ){
    sqlite3BeginWriteOperation(pParse, 1, pTab->iDb);
    addr = sqlite3VdbeOp3(v, OP_FileOpen, 0, 0, pFilename->z, pFilename->n);
    sqlite3VdbeDequoteP3(v, addr);
    sqlite3OpenTableAndIndices(pParse, pTab, 0);
    if( db->flags & SQLITE_CountRows ){
      sqlite3VdbeAddOp(v, OP_Integer, 0, 0);  /* Initialize the row count */
    }
    end = sqlite3VdbeMakeLabel(v);
    addr = sqlite3VdbeAddOp(v, OP_FileRead, pTab->nCol, end);
    if( pDelimiter ){
      sqlite3VdbeChangeP3(v, addr, pDelimiter->z, pDelimiter->n);
      sqlite3VdbeDequoteP3(v, addr);
    }else{
      sqlite3VdbeChangeP3(v, addr, "\t", 1);
    }
    if( pTab->iPKey>=0 ){
      sqlite3VdbeAddOp(v, OP_FileColumn, pTab->iPKey, 0);
      sqlite3VdbeAddOp(v, OP_MustBeInt, 0, 0);
    }else{
      sqlite3VdbeAddOp(v, OP_NewRecno, 0, 0);
    }
    for(i=0; i<pTab->nCol; i++){
      if( i==pTab->iPKey ){
        /* The integer primary key column is filled with NULL since its
        ** value is always pulled from the record number */
        sqlite3VdbeAddOp(v, OP_String, 0, 0);
      }else{
        sqlite3VdbeAddOp(v, OP_FileColumn, i, 0);
      }
    }
    sqlite3GenerateConstraintChecks(pParse, pTab, 0, 0, pTab->iPKey>=0, 
                                   0, onError, addr);
    sqlite3CompleteInsertion(pParse, pTab, 0, 0, 0, 0, -1);
    if( (db->flags & SQLITE_CountRows)!=0 ){
      sqlite3VdbeAddOp(v, OP_AddImm, 1, 0);  /* Increment row count */
    }
    sqlite3VdbeAddOp(v, OP_Goto, 0, addr);
    sqlite3VdbeResolveLabel(v, end);
    sqlite3VdbeAddOp(v, OP_Noop, 0, 0);
    sqlite3EndWriteOperation(pParse);
    if( db->flags & SQLITE_CountRows ){
      sqlite3VdbeAddOp(v, OP_Callback, 1, 0);
      sqlite3VdbeSetNumCols(v, 1);
      sqlite3VdbeSetColName(v, 0, "rows inserted", P3_STATIC);
    }
  }
  
copy_cleanup:
  sqlite3SrcListDelete(pTableName);
  sqliteFree(zFile);
  return;
}

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.118 2004/05/27 09:28:43 danielk1977 Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
................................................................................

// The following directive causes tokens ABORT, AFTER, ASC, etc. to
// fallback to ID if they will not parse as their original value.
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT AFTER ASC ATTACH BEFORE BEGIN CASCADE CLUSTER CONFLICT
  COPY DATABASE DEFERRED DELIMITERS DESC DETACH EACH END EXPLAIN FAIL FOR
  GLOB IGNORE IMMEDIATE INITIALLY INSTEAD LIKE MATCH KEY
  OF OFFSET PRAGMA RAISE REPLACE RESTRICT ROW STATEMENT
  TEMP TRIGGER VACUUM VIEW.

// Define operator precedence early so that this is the first occurance
// of the operator tokens in the grammer.  Keeping the operators together
// causes them to be assigned integer values that are close together,
................................................................................
///////////////////////////// The DROP INDEX command /////////////////////////
//

cmd ::= DROP INDEX nm(X) dbnm(Y).   {
  sqlite3DropIndex(pParse, sqlite3SrcListAppend(0,&X,&Y));
}


///////////////////////////// The COPY command ///////////////////////////////
//
cmd ::= COPY orconf(R) nm(X) dbnm(D) FROM nm(Y) USING DELIMITERS STRING(Z).
    {sqlite3Copy(pParse,sqlite3SrcListAppend(0,&X,&D),&Y,&Z,R);}
cmd ::= COPY orconf(R) nm(X) dbnm(D) FROM nm(Y).
    {sqlite3Copy(pParse,sqlite3SrcListAppend(0,&X,&D),&Y,0,R);}

///////////////////////////// The VACUUM command /////////////////////////////
//
cmd ::= VACUUM.                {sqlite3Vacuum(pParse,0);}
cmd ::= VACUUM nm(X).         {sqlite3Vacuum(pParse,&X);}

///////////////////////////// The PRAGMA command /////////////////////////////
//

**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.119 2004/05/27 17:22:55 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){
................................................................................

// The following directive causes tokens ABORT, AFTER, ASC, etc. to
// fallback to ID if they will not parse as their original value.
// This obviates the need for the "id" nonterminal.
//
%fallback ID
  ABORT AFTER ASC ATTACH BEFORE BEGIN CASCADE CLUSTER CONFLICT
  DATABASE DEFERRED DESC DETACH EACH END EXPLAIN FAIL FOR
  GLOB IGNORE IMMEDIATE INITIALLY INSTEAD LIKE MATCH KEY
  OF OFFSET PRAGMA RAISE REPLACE RESTRICT ROW STATEMENT
  TEMP TRIGGER VACUUM VIEW.

// Define operator precedence early so that this is the first occurance
// of the operator tokens in the grammer.  Keeping the operators together
// causes them to be assigned integer values that are close together,
................................................................................
///////////////////////////// The DROP INDEX command /////////////////////////
//

cmd ::= DROP INDEX nm(X) dbnm(Y).   {
  sqlite3DropIndex(pParse, sqlite3SrcListAppend(0,&X,&Y));
}









///////////////////////////// The VACUUM command /////////////////////////////
//
cmd ::= VACUUM.                {sqlite3Vacuum(pParse,0);}
cmd ::= VACUUM nm(X).         {sqlite3Vacuum(pParse,&X);}

///////////////////////////// The PRAGMA command /////////////////////////////
//

**    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.255 2004/05/27 13:35:20 danielk1977 Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include <stdio.h>
#include <stdlib.h>
................................................................................
int sqlite3ExprCodeExprList(Parse*, ExprList*);
void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
void sqlite3ExprIfFalse(Parse*, Expr*, int, int);
Table *sqlite3FindTable(sqlite*,const char*, const char*);
Table *sqlite3LocateTable(Parse*,const char*, const char*);
Index *sqlite3FindIndex(sqlite*,const char*, const char*);
void sqlite3UnlinkAndDeleteIndex(sqlite*,Index*);
void sqlite3Copy(Parse*, SrcList*, Token*, Token*, int);
void sqlite3Vacuum(Parse*, Token*);
int sqlite3RunVacuum(char**, sqlite*);
int sqlite3GlobCompare(const unsigned char*,const unsigned char*);
int sqlite3LikeCompare(const unsigned char*,const unsigned char*);
char *sqlite3TableNameFromToken(Token*);
int sqlite3ExprCheck(Parse*, Expr*, int, int*);
int sqlite3ExprType(Expr*);

**    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.256 2004/05/27 17:22:56 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "parse.h"
#include <stdio.h>
#include <stdlib.h>
................................................................................
int sqlite3ExprCodeExprList(Parse*, ExprList*);
void sqlite3ExprIfTrue(Parse*, Expr*, int, int);
void sqlite3ExprIfFalse(Parse*, Expr*, int, int);
Table *sqlite3FindTable(sqlite*,const char*, const char*);
Table *sqlite3LocateTable(Parse*,const char*, const char*);
Index *sqlite3FindIndex(sqlite*,const char*, const char*);
void sqlite3UnlinkAndDeleteIndex(sqlite*,Index*);

void sqlite3Vacuum(Parse*, Token*);
int sqlite3RunVacuum(char**, sqlite*);
int sqlite3GlobCompare(const unsigned char*,const unsigned char*);
int sqlite3LikeCompare(const unsigned char*,const unsigned char*);
char *sqlite3TableNameFromToken(Token*);
int sqlite3ExprCheck(Parse*, Expr*, int, int*);
int sqlite3ExprType(Expr*);

*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.72 2004/05/27 13:35:20 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include <stdlib.h>

/*
................................................................................
  { "CASE",              TK_CASE,         },
  { "CHECK",             TK_CHECK,        },
  { "CLUSTER",           TK_CLUSTER,      },
  { "COLLATE",           TK_COLLATE,      },
  { "COMMIT",            TK_COMMIT,       },
  { "CONFLICT",          TK_CONFLICT,     },
  { "CONSTRAINT",        TK_CONSTRAINT,   },
  { "COPY",              TK_COPY,         },
  { "CREATE",            TK_CREATE,       },
  { "CROSS",             TK_JOIN_KW,      },
  { "DATABASE",          TK_DATABASE,     },
  { "DEFAULT",           TK_DEFAULT,      },
  { "DEFERRED",          TK_DEFERRED,     },
  { "DEFERRABLE",        TK_DEFERRABLE,   },
  { "DELETE",            TK_DELETE,       },
  { "DELIMITERS",        TK_DELIMITERS,   },
  { "DESC",              TK_DESC,         },
  { "DETACH",            TK_DETACH,       },
  { "DISTINCT",          TK_DISTINCT,     },
  { "DROP",              TK_DROP,         },
  { "END",               TK_END,          },
  { "EACH",              TK_EACH,         },
  { "ELSE",              TK_ELSE,         },
................................................................................
      }
    }
    state = trans[state][token];
    zSql++;
  }
  return state==0;
}

*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.73 2004/05/27 17:22:56 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include <stdlib.h>

/*
................................................................................
  { "CASE",              TK_CASE,         },
  { "CHECK",             TK_CHECK,        },
  { "CLUSTER",           TK_CLUSTER,      },
  { "COLLATE",           TK_COLLATE,      },
  { "COMMIT",            TK_COMMIT,       },
  { "CONFLICT",          TK_CONFLICT,     },
  { "CONSTRAINT",        TK_CONSTRAINT,   },

  { "CREATE",            TK_CREATE,       },
  { "CROSS",             TK_JOIN_KW,      },
  { "DATABASE",          TK_DATABASE,     },
  { "DEFAULT",           TK_DEFAULT,      },
  { "DEFERRED",          TK_DEFERRED,     },
  { "DEFERRABLE",        TK_DEFERRABLE,   },
  { "DELETE",            TK_DELETE,       },

  { "DESC",              TK_DESC,         },
  { "DETACH",            TK_DETACH,       },
  { "DISTINCT",          TK_DISTINCT,     },
  { "DROP",              TK_DROP,         },
  { "END",               TK_END,          },
  { "EACH",              TK_EACH,         },
  { "ELSE",              TK_ELSE,         },
................................................................................
      }
    }
    state = trans[state][token];
    zSql++;
  }
  return state==0;
}

**
** 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.339 2004/05/27 09:28:43 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................

/* Opcode: SortReset * * *
**
** Remove any elements that remain on the sorter.
*/
case OP_SortReset: {
  sqlite3VdbeSorterReset(p);
  break;
}

/* Opcode: FileOpen * * P3
**
** Open the file named by P3 for reading using the FileRead opcode.
** If P3 is "stdin" then open standard input for reading.
*/
case OP_FileOpen: {
  assert( pOp->p3!=0 );
  if( p->pFile ){
    if( p->pFile!=stdin ) fclose(p->pFile);
    p->pFile = 0;
  }
  if( sqlite3StrICmp(pOp->p3,"stdin")==0 ){
    p->pFile = stdin;
  }else{
    p->pFile = fopen(pOp->p3, "r");
  }
  if( p->pFile==0 ){
    sqlite3SetString(&p->zErrMsg,"unable to open file: ", pOp->p3, (char*)0);
    rc = SQLITE_ERROR;
  }
  break;
}

/* Opcode: FileRead P1 P2 P3
**
** Read a single line of input from the open file (the file opened using
** FileOpen).  If we reach end-of-file, jump immediately to P2.  If
** we are able to get another line, split the line apart using P3 as
** a delimiter.  There should be P1 fields.  If the input line contains
** more than P1 fields, ignore the excess.  If the input line contains
** fewer than P1 fields, assume the remaining fields contain NULLs.
**
** Input ends if a line consists of just "\.".  A field containing only
** "\N" is a null field.  The backslash \ character can be used be used
** to escape newlines or the delimiter.
*/
case OP_FileRead: {
  int n, eol, nField, i, c, nDelim;
  char *zDelim, *z;
  CHECK_FOR_INTERRUPT;
  if( p->pFile==0 ) goto fileread_jump;
  nField = pOp->p1;
  if( nField<=0 ) goto fileread_jump;
  if( nField!=p->nField || p->azField==0 ){
    char **azField = sqliteRealloc(p->azField, sizeof(char*)*nField+1);
    if( azField==0 ){ goto no_mem; }
    p->azField = azField;
    p->nField = nField;
  }
  n = 0;
  eol = 0;
  while( eol==0 ){
    if( p->zLine==0 || n+200>p->nLineAlloc ){
      char *zLine;
      p->nLineAlloc = p->nLineAlloc*2 + 300;
      zLine = sqliteRealloc(p->zLine, p->nLineAlloc);
      if( zLine==0 ){
        p->nLineAlloc = 0;
        sqliteFree(p->zLine);
        p->zLine = 0;
        goto no_mem;
      }
      p->zLine = zLine;
    }
    if( vdbe_fgets(&p->zLine[n], p->nLineAlloc-n, p->pFile)==0 ){
      eol = 1;
      p->zLine[n] = 0;
    }else{
      int c;
      while( (c = p->zLine[n])!=0 ){
        if( c=='\\' ){
          if( p->zLine[n+1]==0 ) break;
          n += 2;
        }else if( c=='\n' ){
          p->zLine[n] = 0;
          eol = 1;
          break;
        }else{
          n++;
        }
      }
    }
  }
  if( n==0 ) goto fileread_jump;
  z = p->zLine;
  if( z[0]=='\\' && z[1]=='.' && z[2]==0 ){
    goto fileread_jump;
  }
  zDelim = pOp->p3;
  if( zDelim==0 ) zDelim = "\t";
  c = zDelim[0];
  nDelim = strlen(zDelim);
  p->azField[0] = z;
  for(i=1; *z!=0 && i<=nField; i++){
    int from, to;
    from = to = 0;
    if( z[0]=='\\' && z[1]=='N' 
       && (z[2]==0 || strncmp(&z[2],zDelim,nDelim)==0) ){
      if( i<=nField ) p->azField[i-1] = 0;
      z += 2 + nDelim;
      if( i<nField ) p->azField[i] = z;
      continue;
    }
    while( z[from] ){
      if( z[from]=='\\' && z[from+1]!=0 ){
        int tx = z[from+1];
        switch( tx ){
          case 'b':  tx = '\b'; break;
          case 'f':  tx = '\f'; break;
          case 'n':  tx = '\n'; break;
          case 'r':  tx = '\r'; break;
          case 't':  tx = '\t'; break;
          case 'v':  tx = '\v'; break;
          default:   break;
        }
        z[to++] = tx;
        from += 2;
        continue;
      }
      if( z[from]==c && strncmp(&z[from],zDelim,nDelim)==0 ) break;
      z[to++] = z[from++];
    }
    if( z[from] ){
      z[to] = 0;
      z += from + nDelim;
      if( i<nField ) p->azField[i] = z;
    }else{
      z[to] = 0;
      z = "";
    }
  }
  while( i<nField ){
    p->azField[i++] = 0;
  }
  break;

  /* If we reach end-of-file, or if anything goes wrong, jump here.
  ** This code will cause a jump to P2 */
fileread_jump:
  pc = pOp->p2 - 1;
  break;
}

/* Opcode: FileColumn P1 * *
**
** Push onto the stack the P1-th column of the most recently read line
** from the input file.
*/
case OP_FileColumn: {
/*
** FIX ME: This will be deleted, but loads of test case files have
** to be updated first...
*/
  int i = pOp->p1;
  char *z;
  assert( i>=0 && i<p->nField );
  if( p->azField ){
    z = p->azField[i];
  }else{
    z = 0;
  }
  pTos++;
  if( z ){
    pTos->n = strlen(z);
    pTos->z = z;
    pTos->flags = MEM_Str | MEM_Ephem | MEM_Term;
    pTos->enc = TEXT_Utf8;
    sqlite3VdbeChangeEncoding(pTos, db->enc);
  }else{
    pTos->flags = MEM_Null;
  }
  break;
}

/* Opcode: MemStore P1 P2 *
**
** Write the top of the stack into memory location P1.
** P1 should be a small integer since space is allocated

**
** 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.340 2004/05/27 17:22:56 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
................................................................................

/* Opcode: SortReset * * *
**
** Remove any elements that remain on the sorter.
*/
case OP_SortReset: {
  sqlite3VdbeSorterReset(p);














































































































































































  break;
}

/* Opcode: MemStore P1 P2 *
**
** Write the top of the stack into memory location P1.
** P1 should be a small integer since space is allocated

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: auth.test,v 1.12 2003/12/07 00:24:35 drh Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.
................................................................................
      return SQLITE_OK
    }
    return SQLITE_OK
  }
  catchsql {SELECT * FROM t2}
} {0 {11 2 33}}

set f [open data1.txt w]
puts $f "7:8:9"
close $f
do_test auth-1.54 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_COPY"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  catchsql {COPY t2 FROM 'data1.txt' USING DELIMITERS ':'}
} {1 {not authorized}}
do_test auth-1.55 {
  set ::authargs
} {t2 data1.txt main {}}
do_test auth-1.56 {
  execsql {SELECT * FROM t2}
} {11 2 33}
do_test auth-1.57 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_COPY"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {COPY t2 FROM 'data1.txt' USING DELIMITERS ':'}
} {0 {}}
do_test auth-1.58 {
  set ::authargs
} {t2 data1.txt main {}}
do_test auth-1.59 {
  execsql {SELECT * FROM t2}
} {11 2 33}
do_test auth-1.60 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_COPY"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      return SQLITE_OK
    }
    return SQLITE_OK
  }
  catchsql {COPY t2 FROM 'data1.txt' USING DELIMITERS ':'}
} {0 {}}
do_test auth-1.61 {
  set ::authargs
} {t2 data1.txt main {}}
do_test auth-1.62 {
  execsql {SELECT * FROM t2}
} {11 2 33 7 8 9}

do_test auth-1.63 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
       return SQLITE_DENY
    }
    return SQLITE_OK

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ATTACH and DETACH commands
# and related functionality.
#
# $Id: auth.test,v 1.13 2004/05/27 17:22:56 drh Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.
................................................................................
      return SQLITE_OK
    }
    return SQLITE_OK
  }
  catchsql {SELECT * FROM t2}
} {0 {11 2 33}}





















































do_test auth-1.63 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_DELETE" && $arg1=="sqlite_master"} {
       return SQLITE_DENY
    }
    return SQLITE_OK

# 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 COPY statement.
#
# $Id: copy.test,v 1.17 2004/02/17 18:26:57 dougcurrie Exp $

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

# Create a file of data from which to copy.
#
set f [open data1.txt w]
puts $f "11\t22\t33"
puts $f "22\t33\t11"
close $f
set f [open data2.txt w]
puts $f "11\t22\t33"
puts $f "\\."
puts $f "22\t33\t11"
close $f
set f [open data3.txt w]
puts $f "11\t22\t33\t44"
puts $f "22\t33\t11"
close $f
set f [open data4.txt w]
puts $f "11 | 22 | 33"
puts $f "22 | 33 | 11"
close $f
set f [open data5.txt w]
puts $f "11|22|33"
puts $f "22|33|11"
close $f
set f [open dataX.txt w]
fconfigure $f -translation binary 
puts -nonewline $f "11|22|33\r"
puts -nonewline $f "22|33|44\r\n"
puts -nonewline $f "33|44|55\n"
puts -nonewline $f "44|55|66\r"
puts -nonewline $f "55|66|77\r\n"
puts -nonewline $f "66|77|88\n"
close $f

# Try to COPY into a non-existant table.
#
do_test copy-1.1 {
  set v [catch {execsql {COPY test1 FROM 'data1.txt'}} msg]
  lappend v $msg
} {1 {no such table: test1}}

# Try to insert into sqlite_master
#
do_test copy-1.2 {
  set v [catch {execsql {COPY sqlite_master FROM 'data2.txt'}} msg]
  lappend v $msg
} {1 {table sqlite_master may not be modified}}

# Do some actual inserts
#
do_test copy-1.3 {
  execsql {CREATE TABLE test1(one int, two int, three int)}
  execsql {COPY test1 FROM 'data1.txt'}
  execsql {SELECT * FROM test1 ORDER BY one}
} {11 22 33 22 33 11}

# Make sure input terminates at \.
#
do_test copy-1.4 {
  execsql {DELETE FROM test1}
  execsql {COPY test1 FROM 'data2.txt'}
  execsql {SELECT * FROM test1 ORDER BY one}
} {11 22 33}

# Test out the USING DELIMITERS clause
#
do_test copy-1.5 {
  execsql {DELETE FROM test1}
  execsql {COPY test1 FROM 'data4.txt' USING DELIMITERS ' | '}
  execsql {SELECT * FROM test1 ORDER BY one}
} {11 22 33 22 33 11}
do_test copy-1.6 {
  execsql {DELETE FROM test1}
  execsql {COPY test1 FROM 'data5.txt' USING DELIMITERS '|'}
  execsql {SELECT * FROM test1 ORDER BY one}
} {11 22 33 22 33 11}
do_test copy-1.7 {
  execsql {DELETE FROM test1}
  execsql {COPY test1 FROM 'data4.txt' USING DELIMITERS '|'}
  execsql {SELECT * FROM test1 ORDER BY one}
} {{11 } { 22 } { 33} {22 } { 33 } { 11}}

# Try copying into a table that has one or more indices.
#
do_test copy-1.8 {
  execsql {DELETE FROM test1}
  execsql {CREATE INDEX index1 ON test1(one)}
  execsql {CREATE INDEX index2 ON test1(two)}
  execsql {CREATE INDEX index3 ON test1(three)}
  execsql {COPY test1 from 'data1.txt'}
  execsql {SELECT * FROM test1 WHERE one=11}
} {11 22 33}
do_test copy-1.8b {
  execsql {SELECT * FROM test1 WHERE one=22}
} {22 33 11}
do_test copy-1.8c {
  execsql {SELECT * FROM test1 WHERE two=22}
} {11 22 33}
do_test copy-1.8d {
  execsql {SELECT * FROM test1 WHERE three=11}
} {22 33 11}


# Try inserting really long data
#
set x {}
for {set i 0} {$i<100} {incr i} {
  append x "($i)-abcdefghijklmnopqrstyvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-"
}
do_test copy-2.1 {
  execsql {CREATE TABLE test2(a int, x text)}
  set f [open data21.txt w]
  puts $f "123\t$x"
  close $f
  execsql {COPY test2 FROM 'data21.txt'}
  execsql {SELECT x from test2}
} $x
file delete -force data21.txt

# Test the escape character mechanism
#
do_test copy-3.1 {
  set fd [open data6.txt w]
  puts $fd "hello\\\tworld\t1"
  puts $fd "hello\tworld\\\t2"
  close $fd
  execsql {
    CREATE TABLE t1(a text, b text);
    COPY t1 FROM 'data6.txt';
    SELECT * FROM t1 ORDER BY a;
  }
} {hello {world	2} {hello	world} 1}
do_test copy-3.2 {
  set fd [open data6.txt w]
  puts $fd "1\thello\\\nworld"
  puts $fd "2\thello world"
  close $fd
  execsql {
    DELETE FROM t1;
    COPY t1 FROM 'data6.txt';
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {hello
world} 2 {hello world}}
do_test copy-3.3 {
  set fd [open data6.txt w]
  puts $fd "1:hello\\b\\f\\n\\r\\t\\vworld"
  puts $fd "2:hello world"
  close $fd
  execsql {
    DELETE FROM t1;
    COPY t1 FROM 'data6.txt' USING DELIMITERS ':';
    SELECT * FROM t1 ORDER BY a;
  }
} [list 1 "hello\b\f\n\r\t\vworld" 2 "hello world"]

# Test the embedded NULL logic.
#
do_test copy-4.1 {
  set fd [open data6.txt w]
  puts $fd "1\t\\N"
  puts $fd "\\N\thello world"
  close $fd
  execsql {
    DELETE FROM t1;
    COPY t1 FROM 'data6.txt';
    SELECT * FROM t1 WHERE a IS NULL;
  }
} {{} {hello world}}
do_test copy-4.2 {
  execsql {
    SELECT * FROM t1 WHERE b IS NULL;
  }
} {1 {}}

# Test the conflict resolution logic for COPY
#
do_test copy-5.1 {
  execsql {
    DROP TABLE t1;
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
    COPY t1 FROM 'data5.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {11 22 33 22 33 11}
do_test copy-5.2 {
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {1 {column b is not unique}}
do_test copy-5.3 {
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY OR IGNORE t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {0 {11 22 33 22 33 11}}
do_test copy-5.4 {
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY OR REPLACE t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {0 {22 33 11 33 22 44}}

do_test copy-5.5 {
  execsql {
    DELETE FROM t1;
    PRAGMA count_changes=on;
    COPY t1 FROM 'data5.txt' USING DELIMITERS '|';
  }
} {2}
do_test copy-5.6 {
  execsql {
    COPY OR REPLACE t1 FROM 'data5.txt' USING DELIMITERS '|';
  }
} {2}
do_test copy-5.7 {
  execsql {
    COPY OR IGNORE t1 FROM 'data5.txt' USING DELIMITERS '|';
  }
} {0}

do_test copy-6.1 {
  execsql {
    PRAGMA count_changes=off;
    CREATE TABLE t2(a,b,c);
    COPY t2 FROM 'dataX.txt' USING DELIMITERS '|';
    SELECT * FROM t2;
  }
} {11 22 33 22 33 44 33 44 55 44 55 66 55 66 77 66 77 88}

integrity_check copy-7.1

# Cleanup 
#
#file delete -force data1.txt data2.txt data3.txt data4.txt data5.txt \
                   data6.txt dataX.txt

finish_test

#    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 DELETE FROM statement.
#
# $Id: delete.test,v 1.13 2003/06/15 23:42:25 drh Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-1.1 {
................................................................................
integrity_check delete-5.8


# Delete large quantities of data.  We want to test the List overflow
# mechanism in the vdbe.
#
do_test delete-6.1 {
  set fd [open data1.txt w]
  for {set i 1} {$i<=3000} {incr i} {
    puts $fd "[expr {$i}]\t[expr {$i*$i}]"
  }
  close $fd
  execsql {DELETE FROM table1}

  execsql {COPY table1 FROM 'data1.txt'}

  execsql {DELETE FROM table2}
  execsql {COPY table2 FROM 'data1.txt'}
  file delete data1.txt
  execsql {SELECT count(*) FROM table1}
} {3000}
do_test delete-6.2 {
  execsql {SELECT count(*) FROM table2}
} {3000}
do_test delete-6.3 {
  execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}

#    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 DELETE FROM statement.
#
# $Id: delete.test,v 1.14 2004/05/27 17:22:56 drh Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-1.1 {
................................................................................
integrity_check delete-5.8


# Delete large quantities of data.  We want to test the List overflow
# mechanism in the vdbe.
#
do_test delete-6.1 {
  execsql {BEGIN; DELETE FROM table1}
  for {set i 1} {$i<=3000} {incr i} {
    execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
  }

  execsql {DELETE FROM table2}
  for {set i 1} {$i<=3000} {incr i} {
    execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])"
  }
  execsql {COMMIT}


  execsql {SELECT count(*) FROM table1}
} {3000}
do_test delete-6.2 {
  execsql {SELECT count(*) FROM table2}
} {3000}
do_test delete-6.3 {
  execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}

#
#***********************************************************************
# 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.3 2004/05/27 01:53:56 drh Exp $

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

db close

# Return the UTF-8 representation of the supplied UTF-16 string $str. 
................................................................................

# Insert some data
do_test $t.2 {
  execsql {INSERT INTO t1 VALUES('two', 'II', 2);}
  execsql {SELECT * FROM t1}
} {one I 1 two II 2}

# Insert some data using the COPY command.
do_test $t.3 {
  set f [open data.txt w]
  puts $f "three\tIII\t3"
  puts $f "four\tIV\t4"
  puts $f "five\tV\t5"
  close $f
  execsql {COPY t1 FROM 'data.txt'}

  execsql {SELECT * FROM t1}
} {one I 1 two II 2 three III 3 four IV 4 five V 5}

# Use the index
do_test $t.4 {
  execsql {
    SELECT * FROM t1 WHERE a = 'one';

#
#***********************************************************************
# 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.4 2004/05/27 17:22:56 drh Exp $

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

db close

# Return the UTF-8 representation of the supplied UTF-16 string $str. 
................................................................................

# Insert some data
do_test $t.2 {
  execsql {INSERT INTO t1 VALUES('two', 'II', 2);}
  execsql {SELECT * FROM t1}
} {one I 1 two II 2}

# Insert some data 
do_test $t.3 {
  execsql {
    INSERT INTO t1 VALUES('three','III',3);
    INSERT INTO t1 VALUES('four','IV',4);
    INSERT INTO t1 VALUES('five','V',5);


  }
  execsql {SELECT * FROM t1}
} {one I 1 two II 2 three III 3 four IV 4 five V 5}

# Use the index
do_test $t.4 {
  execsql {
    SELECT * FROM t1 WHERE a = 'one';

# 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 the library is able to correctly
# handle file-format 3 (version 2.6.x) databases.
#
# $Id: format3.test,v 1.4 2003/12/23 02:17:35 drh Exp $

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

# Create a bunch of data to sort against
#
do_test format3-1.0 {
  set fd [open data.txt w]
  puts $fd "1\tone\t0\tI\t3.141592653"
  puts $fd "2\ttwo\t1\tII\t2.15"
  puts $fd "3\tthree\t1\tIII\t4221.0"
  puts $fd "4\tfour\t2\tIV\t-0.0013442"
  puts $fd "5\tfive\t2\tV\t-11"
  puts $fd "6\tsix\t2\tVI\t0.123"
  puts $fd "7\tseven\t2\tVII\t123.0"
  puts $fd "8\teight\t3\tVIII\t-1.6"
  close $fd
  execsql {
    CREATE TABLE t1(
       n int,
       v varchar(10),
       log int,
       roman varchar(10),
       flt real
    );
    COPY t1 FROM 'data.txt'
  }
  file delete data.txt
  db close
  set ::bt [btree_open test.db]
  btree_begin_transaction $::bt
  set m [btree_get_meta $::bt]
  set m [lreplace $m 2 2 3]
  eval btree_update_meta $::bt $m
  btree_commit $::bt
  btree_close $::bt
  sqlite db test.db
  execsql {SELECT count(*) FROM t1}
} {8}

do_test format3-1.1 {
  execsql {SELECT n FROM t1 ORDER BY n}
} {1 2 3 4 5 6 7 8}
do_test format3-1.1.1 {
  execsql {SELECT n FROM t1 ORDER BY n ASC}
} {1 2 3 4 5 6 7 8}
do_test format3-1.1.1 {
  execsql {SELECT ALL n FROM t1 ORDER BY n ASC}
} {1 2 3 4 5 6 7 8}
do_test format3-1.2 {
  execsql {SELECT n FROM t1 ORDER BY n DESC}
} {8 7 6 5 4 3 2 1}
do_test format3-1.3a {
  execsql {SELECT v FROM t1 ORDER BY v}
} {eight five four one seven six three two}
do_test format3-1.3b {
  execsql {SELECT n FROM t1 ORDER BY v}
} {8 5 4 1 7 6 3 2}
do_test format3-1.4 {
  execsql {SELECT n FROM t1 ORDER BY v DESC}
} {2 3 6 7 1 4 5 8}
do_test format3-1.5 {
  execsql {SELECT flt FROM t1 ORDER BY flt}
} {-11 -1.6 -0.0013442 0.123 2.15 3.141592653 123.0 4221.0}
do_test format3-1.6 {
  execsql {SELECT flt FROM t1 ORDER BY flt DESC}
} {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11}
do_test format3-1.7 {
  execsql {SELECT roman FROM t1 ORDER BY roman}
} {I II III IV V VI VII VIII}
do_test format3-1.8 {
  execsql {SELECT n FROM t1 ORDER BY log, flt}
} {1 2 3 5 4 6 7 8}
do_test format3-1.8.1 {
  execsql {SELECT n FROM t1 ORDER BY log asc, flt}
} {1 2 3 5 4 6 7 8}
do_test format3-1.8.2 {
  execsql {SELECT n FROM t1 ORDER BY log, flt ASC}
} {1 2 3 5 4 6 7 8}
do_test format3-1.8.3 {
  execsql {SELECT n FROM t1 ORDER BY log ASC, flt asc}
} {1 2 3 5 4 6 7 8}
do_test format3-1.9 {
  execsql {SELECT n FROM t1 ORDER BY log, flt DESC}
} {1 3 2 7 6 4 5 8}
do_test format3-1.9.1 {
  execsql {SELECT n FROM t1 ORDER BY log ASC, flt DESC}
} {1 3 2 7 6 4 5 8}
do_test format3-1.10 {
  execsql {SELECT n FROM t1 ORDER BY log DESC, flt}
} {8 5 4 6 7 2 3 1}
do_test format3-1.11 {
  execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC}
} {8 7 6 4 5 3 2 1}

# These tests are designed to reach some hard-to-reach places
# inside the string comparison routines.
#
# (Later) The sorting behavior changed in 2.7.0.  But we will
# keep these tests.  You can never have too many test cases!
#
do_test format3-2.1.1 {
  execsql {
    UPDATE t1 SET v='x' || -flt;
    UPDATE t1 SET v='x-2b' where v=='x-0.123';
    SELECT v FROM t1 ORDER BY v;
  }
} {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11}
do_test format3-2.1.2 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999);
  }
} {x-4221 x-123 x-3.141592653 x-2.15 x0.0013442 x1.6 x11 x-2b}
do_test format3-2.1.3 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0;
  }
} {x-4221 x-123 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11}
do_test format3-2.1.4 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999) DESC;
  }
} {x-2b x11 x1.6 x0.0013442 x-2.15 x-3.141592653 x-123 x-4221}
do_test format3-2.1.5 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0 DESC;
  }
} {x11 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123 x-4221}

# This is a bug fix for 2.2.4.
# Strings are normally mapped to upper-case for a caseless comparison.
# But this can cause problems for characters in between 'Z' and 'a'.
#
do_test format3-3.1 {
  execsql {
    CREATE TABLE t2(a,b);
    INSERT INTO t2 VALUES('AGLIENTU',1);
    INSERT INTO t2 VALUES('AGLIE`',2);
    INSERT INTO t2 VALUES('AGNA',3);
    SELECT a, b FROM t2 ORDER BY a;
  }
} {AGLIENTU 1 AGLIE` 2 AGNA 3}
do_test format3-3.2 {
  execsql {
    SELECT a, b FROM t2 ORDER BY a DESC;
  }
} {AGNA 3 AGLIE` 2 AGLIENTU 1}
do_test format3-3.3 {
  execsql {
    DELETE FROM t2;
    INSERT INTO t2 VALUES('aglientu',1);
    INSERT INTO t2 VALUES('aglie`',2);
    INSERT INTO t2 VALUES('agna',3);
    SELECT a, b FROM t2 ORDER BY a;
  }
} {aglie` 2 aglientu 1 agna 3}
do_test format3-3.4 {
  execsql {
    SELECT a, b FROM t2 ORDER BY a DESC;
  }
} {agna 3 aglientu 1 aglie` 2}

# Version 2.7.0 testing.
#
do_test format3-4.1 {
  execsql {
    INSERT INTO t1 VALUES(9,'x2.7',3,'IX',4.0e5);
    INSERT INTO t1 VALUES(10,'x5.0e10',3,'X',-4.0e5);
    INSERT INTO t1 VALUES(11,'x-4.0e9',3,'XI',4.1e4);
    INSERT INTO t1 VALUES(12,'x01234567890123456789',3,'XII',-4.2e3);
    SELECT n FROM t1 ORDER BY n;
  }
} {1 2 3 4 5 6 7 8 9 10 11 12}
do_test format3-4.2 {
  execsql {
    SELECT n||'' FROM t1 ORDER BY 1;
  }
} {1 2 3 4 5 6 7 8 9 10 11 12}
do_test format3-4.3 {
  execsql {
    SELECT n+0 FROM t1 ORDER BY 1;
  }
} {1 2 3 4 5 6 7 8 9 10 11 12}
do_test format3-4.4 {
  execsql {
    SELECT n||'' FROM t1 ORDER BY 1 DESC;
  }
} {12 11 10 9 8 7 6 5 4 3 2 1}
do_test format3-4.5 {
  execsql {
    SELECT n+0 FROM t1 ORDER BY 1 DESC;
  }
} {12 11 10 9 8 7 6 5 4 3 2 1}
do_test format3-4.6 {
  execsql {
    SELECT v FROM t1 ORDER BY 1;
  }
} {x-123 x-2.15 x-2b x-3.141592653 x-4.0e9 x-4221 x0.0013442 x01234567890123456789 x1.6 x11 x2.7 x5.0e10}
do_test format3-4.7 {
  execsql {
    SELECT v FROM t1 ORDER BY 1 DESC;
  }
} {x5.0e10 x2.7 x11 x1.6 x01234567890123456789 x0.0013442 x-4221 x-4.0e9 x-3.141592653 x-2b x-2.15 x-123}
do_test format3-4.8 {
  execsql {
    SELECT substr(v,2,99) FROM t1 ORDER BY 1;
  }
} {-4.0e9 -4221 -123 -3.141592653 -2.15 0.0013442 1.6 2.7 11 5.0e10 01234567890123456789 -2b}

# Build some new test data, this time with indices.
#
do_test format3-5.0 {
  execsql {
    DROP TABLE t1;
    CREATE TABLE t1(w int, x text, y blob);
    DROP TABLE t2;
    CREATE TABLE t2(p varchar(1), q clob, r real, s numeric(8));
  }
  for {set i 1} {$i<=100} {incr i} {
    set w $i
    set x [expr {int(log($i)/log(2))}]
    set y [expr {$i*$i + 2*$i + 1}]
    execsql "INSERT INTO t1 VALUES($w,$x,$y)"
  }
  execsql {
    INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1;
    CREATE INDEX i1w ON t1(w);
    CREATE INDEX i1xy ON t1(x,y);
    CREATE INDEX i2p ON t2(p);
    CREATE INDEX i2r ON t2(r);
    CREATE INDEX i2qs ON t2(q, s);
  }
} {}

# Do an SQL statement.  Append the search count to the end of the result.
#
proc count sql {
  set ::sqlite_search_count 0
  return [concat [execsql $sql] $::sqlite_search_count]
}

# Verify that queries use an index.  We are using the special variable
# "sqlite_search_count" which tallys the number of executions of MoveTo
# and Next operators in the VDBE.  By verifing that the search count is
# small we can be assured that indices are being used properly.
#
do_test format3-5.1 {
  db close
  sqlite db test.db
  count {SELECT x, y FROM t1 WHERE w=10}
} {3 121 3}
do_test format3-5.2 {
  count {SELECT x, y FROM t1 WHERE w=11}
} {3 144 3}
do_test format3-5.3 {
  count {SELECT x, y FROM t1 WHERE 11=w}
} {3 144 3}
do_test format3-5.4 {
  count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
} {3 144 3}
do_test format3-5.5 {
  count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 3}
do_test format3-5.6 {
  count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 3}
do_test format3-5.7 {
  count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 3}
do_test format3-5.8 {
  count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 3}
do_test format3-5.9 {
  count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3}
} {3 144 3}
do_test format3-5.10 {
  count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 3}
do_test format3-5.11 {
  count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 3}

# New for SQLite version 2.1: Verify that that inequality constraints
# are used correctly.
#
do_test format3-5.12 {
  count {SELECT w FROM t1 WHERE x=3 AND y<100}
} {8 3}
do_test format3-5.13 {
  count {SELECT w FROM t1 WHERE x=3 AND 100>y}
} {8 3}
do_test format3-5.14 {
  count {SELECT w FROM t1 WHERE 3=x AND y<100}
} {8 3}
do_test format3-5.15 {
  count {SELECT w FROM t1 WHERE 3=x AND 100>y}
} {8 3}
do_test format3-5.16 {
  count {SELECT w FROM t1 WHERE x=3 AND y<=100}
} {8 9 5}
do_test format3-5.17 {
  count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
} {8 9 5}
do_test format3-5.18 {
  count {SELECT w FROM t1 WHERE x=3 AND y>225}
} {15 3}
do_test format3-5.19 {
  count {SELECT w FROM t1 WHERE x=3 AND 225<y}
} {15 3}
do_test format3-5.20 {
  count {SELECT w FROM t1 WHERE x=3 AND y>=225}
} {14 15 5}
do_test format3-5.21 {
  count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
} {14 15 5}
do_test format3-5.22 {
  count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
} {11 12 5}
do_test format3-5.23 {
  count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
} {10 11 12 13 9}
do_test format3-5.24 {
  count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
} {11 12 5}
do_test format3-5.25 {
  count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y}
} {10 11 12 13 9}

# Need to work on optimizing the BETWEEN operator.  
#
# do_test format3-5.26 {
#   count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196}
# } {10 11 12 13 9}

do_test format3-5.27 {
  count {SELECT w FROM t1 WHERE x=3 AND y+1==122}
} {10 17}
do_test format3-5.28 {
  count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122}
} {10 99}
do_test format3-5.29 {
  count {SELECT w FROM t1 WHERE y==121}
} {10 99}


do_test format3-5.30 {
  count {SELECT w FROM t1 WHERE w>97}
} {98 99 100 6}
do_test format3-5.31 {
  count {SELECT w FROM t1 WHERE w>=97}
} {97 98 99 100 8}
do_test format3-5.33 {
  count {SELECT w FROM t1 WHERE w==97}
} {97 3}
do_test format3-5.34 {
  count {SELECT w FROM t1 WHERE w+1==98}
} {97 99}
do_test format3-5.35 {
  count {SELECT w FROM t1 WHERE w<3}
} {1 2 4}
do_test format3-5.36 {
  count {SELECT w FROM t1 WHERE w<=3}
} {1 2 3 6}
do_test format3-5.37 {
  count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w}
} {1 2 3 199}


# Do the same kind of thing except use a join as the data source.
#
do_test format3-6.1 {
  db close
  sqlite db test.db
  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND y=s AND r=8977
  }
} {34 67 6}
do_test format3-6.2 {
  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND s=y AND r=8977
  }
} {34 67 6}
do_test format3-6.3 {
  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 6}
do_test format3-6.4 {
  count {
    SELECT w, p FROM t2, t1
    WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 6}
do_test format3-6.5 {
  count {
    SELECT w, p FROM t2, t1
    WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
  }
} {34 67 6}
do_test format3-6.6 {
  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND p=77 AND s=y AND w>5
  }
} {24 77 6}
do_test format3-6.7 {
  count {
    SELECT w, p FROM t1, t2
    WHERE x=q AND p>77 AND s=y AND w=5
  }
} {5 96 6}

# Lets do a 3-way join.
#
do_test format3-7.1 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
  }
} {11 90 11 9}
do_test format3-7.2 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
  }
} {12 89 12 9}
do_test format3-7.3 {
  count {
    SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C
    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
  }
} {15 86 86 9}

# Test to see that the special case of a constant WHERE clause is
# handled.
#
do_test format3-8.1 {
  count {
    SELECT * FROM t1 WHERE 0
  }
} {0}
do_test format3-8.2 {
  count {
    SELECT * FROM t1 WHERE 1 LIMIT 1
  }
} {1 0 4 1}
do_test format3-8.3 {
  execsql {
    SELECT 99 WHERE 0
  }
} {}
do_test format3-8.4 {
  execsql {
    SELECT 99 WHERE 1
  }
} {99}

# Verify that IN operators in a WHERE clause are handled correctly.
#
do_test format3-9.1 {
  count {
    SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 0}
do_test format3-9.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 format3-9.3 {
  count {
    SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1;
  }
} {1 0 4 2 1 9 3 1 16 10}
do_test format3-9.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 format3-9.5 {
  count {
    SELECT * FROM t1 WHERE rowid IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 1}
do_test format3-9.6 {
  count {
    SELECT * FROM t1 WHERE rowid+0 IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 199}
do_test format3-9.7 {
  count {
    SELECT * FROM t1 WHERE w IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 7}
do_test format3-9.8 {
  count {
    SELECT * FROM t1 WHERE w+0 IN 
       (select rowid from t1 where rowid IN (-1,2,4))
    ORDER BY 1;
  }
} {2 1 9 4 2 25 199}
do_test format3-9.9 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1;
  }
} {2 1 9 3 1 16 6}
do_test format3-9.10 {
  count {
    SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1;
  }
} {2 1 9 3 1 16 199}
do_test format3-9.11 {
  count {
    SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1;
  }
} {79 6 6400 89 6 8100 199}
do_test format3-9.12 {
  count {
    SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1;
  }
} {79 6 6400 89 6 8100 74}
do_test format3-9.13 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1;
  }
} {2 1 9 3 1 16 6}
do_test format3-9.14 {
  count {
    SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1;
  }
} {2 1 9 6}

# This procedure executes the SQL.  Then it checks the generated program
# for the SQL and appends a "nosort" to the result if the program contains the
# SortCallback opcode.  If the program does not contain the SortCallback
# opcode it appends "sort"
#
proc cksort {sql} {
  set data [execsql $sql]
  set prog [execsql "EXPLAIN $sql"]
  if {[regexp SortCallback $prog]} {set x sort} {set x nosort}
  lappend data $x
  return $data
}
# Check out the logic that attempts to implement the ORDER BY clause
# using an index rather than by sorting.
#
do_test format3-10.1 {
  execsql {
    CREATE TABLE t3(a,b,c);
    CREATE INDEX t3a ON t3(a);
    CREATE INDEX t3bc ON t3(b,c);
    CREATE INDEX t3acb ON t3(a,c,b);
    INSERT INTO t3 SELECT w, 101-w, y FROM t1;
    SELECT count(*), sum(a), sum(b), sum(c) FROM t3;
  }
} {100 5050 5050 348550}
do_test format3-10.2 {
  cksort {
    SELECT * FROM t3 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}
do_test format3-10.3 {
  cksort {
    SELECT * FROM t3 ORDER BY a+1 LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 sort}
do_test format3-10.4 {
  cksort {
    SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}
do_test format3-10.5 {
  cksort {
    SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}
do_test format3-10.6 {
  cksort {
    SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 nosort}
do_test format3-10.7 {
  cksort {
    SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 sort}
do_test format3-10.8 {
  cksort {
    SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3
  }
} {1 100 4 2 99 9 3 98 16 sort}
do_test format3-10.9 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
  }
} {1 100 4 nosort}
do_test format3-10.10 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3
  }
} {1 100 4 nosort}
do_test format3-10.11 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3
  }
} {1 100 4 nosort}
do_test format3-10.12 {
  cksort {
    SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3
  }
} {1 100 4 nosort}
do_test format3-10.13 {
  cksort {
    SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3
  }
} {100 1 10201 99 2 10000 98 3 9801 nosort}
do_test format3-10.13.1 {
  cksort {
    SELECT * FROM t3 WHERE a>0 ORDER BY a+1 DESC LIMIT 3
  }
} {100 1 10201 99 2 10000 98 3 9801 sort}
do_test format3-10.14 {
  cksort {
    SELECT * FROM t3 ORDER BY b LIMIT 3
  }
} {100 1 10201 99 2 10000 98 3 9801 nosort}
do_test format3-10.15 {
  cksort {
    SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3
  }
} {1 0 2 1 3 1 nosort}
do_test format3-10.16 {
  cksort {
    SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3
  }
} {1 0 2 1 3 1 sort}
do_test format3-10.17 {
  cksort {
    SELECT y FROM t1 ORDER BY w COLLATE text LIMIT 3;
  }
} {4 121 10201 sort}
do_test format3-10.18 {
  cksort {
    SELECT y FROM t1 ORDER BY w COLLATE numeric LIMIT 3;
  }
} {4 9 16 sort}
do_test format3-10.19 {
  cksort {
    SELECT y FROM t1 ORDER BY w LIMIT 3;
  }
} {4 9 16 nosort}

# Check that all comparisons are numeric.  Similar tests in misc1.test
# check the same comparisons on a format4+ database and find that some
# are numeric and some are text.
#
do_test format3-11.1 {
  execsql {SELECT '0'=='0.0'}
} {1}
do_test format3-11.2 {
  execsql {SELECT '0'==0.0}
} {1}
do_test format3-11.3 {
  execsql {SELECT '123456789012345678901'=='123456789012345678900'}
} {1}
do_test format3-11.4 {
  execsql {
    CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE);
    INSERT INTO t6 VALUES('0','0.0');
    SELECT * FROM t6;
  }
} {0 0.0}
do_test format3-11.5 {
  execsql {
    INSERT OR IGNORE INTO t6 VALUES(0.0,'x');
    SELECT * FROM t6;
  }
} {0 0.0}
do_test format3-11.6 {
  execsql {
    INSERT OR IGNORE INTO t6 VALUES('y',0);
    SELECT * FROM t6;
  }
} {0 0.0}
do_test format3-11.7 {
  execsql {
    CREATE TABLE t7(x INTEGER, y TEXT, z);
    INSERT INTO t7 VALUES(0,0,1);
    INSERT INTO t7 VALUES(0.0,0,2);
    INSERT INTO t7 VALUES(0,0.0,3);
    INSERT INTO t7 VALUES(0.0,0.0,4);
    SELECT DISTINCT x, y FROM t7 ORDER BY z;
  }
} {0 0}

# Make sure attempts to attach a format 3 database fail.
#
do_test format3-12.1 {
  file delete -force test2.db
  sqlite db2 test2.db
  catchsql {
    CREATE TABLE t8(x,y);
    ATTACH DATABASE 'test.db' AS format3;
  } db2;
} {1 {incompatible file format in auxiliary database: format3}}
do_test format3-12.2 {
  catchsql {
    ATTACH DATABASE 'test2.db' AS test2;
  }
} {1 {cannot attach auxiliary databases to an older format master database}}
db2 close

finish_test

#    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 IN and BETWEEN operator.
#
# $Id: in.test,v 1.11 2004/01/15 03:30:25 drh Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
  set fd [open data1.txt w]



  for {set i 1} {$i<=10} {incr i} {
    puts $fd "$i\t[expr {int(pow(2,$i))}]"
  }
  close $fd
  execsql {
    CREATE TABLE t1(a int, b int);
    COPY t1 FROM 'data1.txt';


  }
  file delete -force data1.txt
  execsql {SELECT count(*) FROM t1}
} {10}

# Do basic testing of BETWEEN.
#
do_test in-1.1 {
  execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}
} {4 5}

#    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 IN and BETWEEN operator.
#
# $Id: in.test,v 1.12 2004/05/27 17:22:56 drh Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
  execsql {
    BEGIN;
    CREATE TABLE t1(a int, b int);
  }
  for {set i 1} {$i<=10} {incr i} {
    execsql "INSERT INTO t1 VALUES($i,[expr {int(pow(2,$i))}])"
  }

  execsql {


    COMMIT;
    SELECT count(*) FROM t1;
  }


} {10}

# Do basic testing of BETWEEN.
#
do_test in-1.1 {
  execsql {SELECT a FROM t1 WHERE b BETWEEN 10 AND 50 ORDER BY a}
} {4 5}

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the special processing associated
# with INTEGER PRIMARY KEY columns.
#
# $Id: intpkey.test,v 1.14 2003/06/15 23:42:25 drh Exp $

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

# Create a table with a primary key and a datatype other than
# integer
#
................................................................................
    SELECT rowid, a FROM t1
  }
} {-4 -4 0 0 5 5 6 6 11 11}

# Test the ability of the COPY command to put data into a
# table that contains an integer primary key.
#



do_test intpkey-6.1 {
  set f [open ./data1.txt w]
  puts $f "20\tb-20\tc-20"
  puts $f "21\tb-21\tc-21"
  puts $f "22\tb-22\tc-22"
  close $f
  execsql {


    COPY t1 FROM 'data1.txt';


    SELECT * FROM t1 WHERE a>=20;
  }
} {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}
do_test intpkey-6.2 {
  execsql {
    SELECT * FROM t1 WHERE b=='hello'
  }

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the special processing associated
# with INTEGER PRIMARY KEY columns.
#
# $Id: intpkey.test,v 1.15 2004/05/27 17:22:56 drh Exp $

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

# Create a table with a primary key and a datatype other than
# integer
#
................................................................................
    SELECT rowid, a FROM t1
  }
} {-4 -4 0 0 5 5 6 6 11 11}

# Test the ability of the COPY command to put data into a
# table that contains an integer primary key.
#
# COPY command has been removed.  But we retain these tests so
# that the tables will contain the right data for tests that follow.
#
do_test intpkey-6.1 {





  execsql {
    BEGIN;
    INSERT INTO t1 VALUES(20,'b-20','c-20');
    INSERT INTO t1 VALUES(21,'b-21','c-21');
    INSERT INTO t1 VALUES(22,'b-22','c-22');
    COMMIT;
    SELECT * FROM t1 WHERE a>=20;
  }
} {20 b-20 c-20 21 b-21 c-21 22 b-22 c-22}
do_test intpkey-6.2 {
  execsql {
    SELECT * FROM t1 WHERE b=='hello'
  }

#    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 LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.12 2004/05/14 21:12:24 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]



for {set i 1} {$i<=32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}
  puts $fd "[expr {32-$i}]\t[expr {10-$j}]"

}
close $fd
execsql {
  CREATE TABLE t1(x int, y int);
  COPY t1 FROM 'data1.txt'

}
file delete data1.txt

do_test limit-1.0 {
  execsql {SELECT count(*) FROM t1}
} {32}
do_test limit-1.1 {
  execsql {SELECT count(*) FROM t1 LIMIT  5}
} {32}

#    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 LIMIT ... OFFSET ... clause
#  of SELECT statements.
#
# $Id: limit.test,v 1.13 2004/05/27 17:22:56 drh Exp $

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

# Build some test data
#
execsql {
  CREATE TABLE t1(x int, y int);
  BEGIN;
}
for {set i 1} {$i<=32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}

  execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
}

execsql {


  COMMIT;
}


do_test limit-1.0 {
  execsql {SELECT count(*) FROM t1}
} {32}
do_test limit-1.1 {
  execsql {SELECT count(*) FROM t1 LIMIT  5}
} {32}

#***********************************************************************
# This file attempts to check the library in an out-of-memory situation.
# When compiled with -DMEMORY_DEBUG=1, the SQLite library accepts a special
# command (sqlite_malloc_fail N) which causes the N-th malloc to fail.  This
# special feature is used to see what happens in the library if a malloc
# were to really fail due to an out-of-memory situation.
#
# $Id: malloc.test,v 1.6 2004/02/14 01:39:50 drh Exp $

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

# Only run these tests if memory debugging is turned on.
#
if {[info command sqlite_malloc_stat]==""} {
................................................................................
       lappend v $v2
     }
  } {1 1}
}

set fd [open ./data.tmp w]
for {set i 1} {$i<=20} {incr i} {
  puts $fd "$i\t[expr {$i*$i}]\t[expr {100-$i}] abcdefghijklmnopqrstuvwxyz"
}
close $fd

for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-2.$i {
     sqlite_malloc_fail 0
     catch {db close}
................................................................................
     set v [catch {sqlite db test.db} msg]
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         COPY t1 FROM 'data.tmp';





         SELECT 'stuff', count(*) as 'other stuff', max(a+10) FROM t1;
         UPDATE t1 SET b=b||b||b||b;
         UPDATE t1 SET b=a WHERE a in (10,12,22);
         INSERT INTO t1(c,b,a) VALUES(20,10,5);
         INSERT INTO t1 SELECT * FROM t1
             WHERE a IN (SELECT a FROM t1 WHERE a<10);
         DELETE FROM t1 WHERE a>=10;
................................................................................
       set v2 [expr {$msg=="" || $msg=="out of memory"}]
       if {!$v2} {puts "\nError message returned: $msg"}
       lappend v $v2
     }
  } {1 1}
}

set fd [open ./data.tmp w]
for {set i 1} {$i<=10} {incr i} {
  puts $fd "$i\t[expr {$i*$i}]\t[expr {100-$i}]"
}
close $fd

for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-3.$i {
     sqlite_malloc_fail 0
     catch {db close}
     catch {file delete -force test.db}
     catch {file delete -force test.db-journal}
     sqlite_malloc_fail $i
................................................................................
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         BEGIN TRANSACTION;
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         COPY t1 FROM 'data.tmp';





         INSERT INTO t1(c,b,a) VALUES(20,10,5);
         DELETE FROM t1 WHERE a>=10;
         DROP INDEX i1;
         DELETE FROM t1;
         ROLLBACK;
       }} msg]
     }
................................................................................
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         BEGIN TRANSACTION;
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         COPY t1 FROM 'data.tmp';





         UPDATE t1 SET b=a WHERE a in (10,12,22);
         INSERT INTO t1 SELECT * FROM t1
             WHERE a IN (SELECT a FROM t1 WHERE a<10);
         DROP INDEX i1;
         DELETE FROM t1;
         COMMIT;
       }} msg]

#***********************************************************************
# This file attempts to check the library in an out-of-memory situation.
# When compiled with -DMEMORY_DEBUG=1, the SQLite library accepts a special
# command (sqlite_malloc_fail N) which causes the N-th malloc to fail.  This
# special feature is used to see what happens in the library if a malloc
# were to really fail due to an out-of-memory situation.
#
# $Id: malloc.test,v 1.7 2004/05/27 17:22:56 drh Exp $

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

# Only run these tests if memory debugging is turned on.
#
if {[info command sqlite_malloc_stat]==""} {
................................................................................
       lappend v $v2
     }
  } {1 1}
}

set fd [open ./data.tmp w]
for {set i 1} {$i<=20} {incr i} {
  puts $fd "$i\t[expr {$i*$i}]\t[expr {100-$i}]  abcdefghijklmnopqrstuvwxyz"
}
close $fd

for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-2.$i {
     sqlite_malloc_fail 0
     catch {db close}
................................................................................
     set v [catch {sqlite db test.db} msg]
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         INSERT INTO t1 VALUES(1,1,'99 abcdefghijklmnopqrstuvwxyz');
         INSERT INTO t1 VALUES(2,4,'98 abcdefghijklmnopqrstuvwxyz');
         INSERT INTO t1 VALUES(3,9,'97 abcdefghijklmnopqrstuvwxyz');
         INSERT INTO t1 VALUES(4,16,'96 abcdefghijklmnopqrstuvwxyz');
         INSERT INTO t1 VALUES(5,25,'95 abcdefghijklmnopqrstuvwxyz');
         INSERT INTO t1 VALUES(6,36,'94 abcdefghijklmnopqrstuvwxyz');
         SELECT 'stuff', count(*) as 'other stuff', max(a+10) FROM t1;
         UPDATE t1 SET b=b||b||b||b;
         UPDATE t1 SET b=a WHERE a in (10,12,22);
         INSERT INTO t1(c,b,a) VALUES(20,10,5);
         INSERT INTO t1 SELECT * FROM t1
             WHERE a IN (SELECT a FROM t1 WHERE a<10);
         DELETE FROM t1 WHERE a>=10;
................................................................................
       set v2 [expr {$msg=="" || $msg=="out of memory"}]
       if {!$v2} {puts "\nError message returned: $msg"}
       lappend v $v2
     }
  } {1 1}
}







for {set go 1; set i 1} {$go} {incr i} {
  do_test malloc-3.$i {
     sqlite_malloc_fail 0
     catch {db close}
     catch {file delete -force test.db}
     catch {file delete -force test.db-journal}
     sqlite_malloc_fail $i
................................................................................
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         BEGIN TRANSACTION;
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         INSERT INTO t1 VALUES(1,1,99);
         INSERT INTO t1 VALUES(2,4,98);
         INSERT INTO t1 VALUES(3,9,97);
         INSERT INTO t1 VALUES(4,16,96);
         INSERT INTO t1 VALUES(5,25,95);
         INSERT INTO t1 VALUES(6,36,94);
         INSERT INTO t1(c,b,a) VALUES(20,10,5);
         DELETE FROM t1 WHERE a>=10;
         DROP INDEX i1;
         DELETE FROM t1;
         ROLLBACK;
       }} msg]
     }
................................................................................
     if {$v} {
       set msg ""
     } else {
       set v [catch {execsql {
         BEGIN TRANSACTION;
         CREATE TABLE t1(a int, b int, c int);
         CREATE INDEX i1 ON t1(a,b);
         INSERT INTO t1 VALUES(1,1,99);
         INSERT INTO t1 VALUES(2,4,98);
         INSERT INTO t1 VALUES(3,9,97);
         INSERT INTO t1 VALUES(4,16,96);
         INSERT INTO t1 VALUES(5,25,95);
         INSERT INTO t1 VALUES(6,36,94);
         UPDATE t1 SET b=a WHERE a in (10,12,22);
         INSERT INTO t1 SELECT * FROM t1
             WHERE a IN (SELECT a FROM t1 WHERE a<10);
         DROP INDEX i1;
         DELETE FROM t1;
         COMMIT;
       }} msg]

#    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.20 2004/05/14 11:00:53 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)}
set f [open ./testdata1.txt w]
for {set i 0} {$i<=30} {incr i} {
  puts $f "[expr {$i%9}]\t[expr {$i%10}]"
}
close $f
execsql {COPY tbl1 FROM './testdata1.txt'}
file delete -force ./testdata1.txt
catch {unset data}

# Do a second query inside a first.
#
do_test select2-1.1 {
  set sql {SELECT DISTINCT f1 FROM tbl1 ORDER BY f1}
  set r {}
  db eval $sql data {
................................................................................
  }
  set r
} {4: 2 3 4}

# Create a largish table
#
do_test select2-2.0 {
  execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int)}
  set f [open ./testdata1.txt w]
  for {set i 1} {$i<=30000} {incr i} {
    puts $f "$i\t[expr {$i*2}]\t[expr {$i*3}]"

  }
  close $f
  # execsql {--vdbe-trace-on--}
  execsql {COPY tbl2 FROM './testdata1.txt'}
  file delete -force ./testdata1.txt
} {}

do_test select2-2.1 {
  execsql {SELECT count(*) FROM tbl2}
} {30000}
do_test select2-2.2 {
  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}

#    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.21 2004/05/27 17:22:56 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 {BEGIN}
for {set i 0} {$i<=30} {incr i} {
  execsql "INSERT INTO tbl1 VALUES([expr {$i%9}],[expr {$i%10}])"
}

execsql {COMMIT}



# Do a second query inside a first.
#
do_test select2-1.1 {
  set sql {SELECT DISTINCT f1 FROM tbl1 ORDER BY f1}
  set r {}
  db eval $sql data {
................................................................................
  }
  set r
} {4: 2 3 4}

# Create a largish table
#
do_test select2-2.0 {
  execsql {CREATE TABLE tbl2(f1 int, f2 int, f3 int); BEGIN;}

  for {set i 1} {$i<=30000} {incr i} {

    execsql "INSERT INTO tbl2 VALUES($i,[expr {$i*2}],[expr {$i*3}])"
  }

  execsql {COMMIT}


} {}

do_test select2-2.1 {
  execsql {SELECT count(*) FROM tbl2}
} {30000}
do_test select2-2.2 {
  execsql {SELECT count(*) FROM tbl2 WHERE f2>1000}

#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.9 2004/05/21 02:14:25 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
  set fd [open data1.txt w]



  for {set i 1} {$i<32} {incr i} {
    for {set j 0} {pow(2,$j)<$i} {incr j} {}
    puts $fd "$i\t$j"

  }
  close $fd
  execsql {
    CREATE TABLE t1(n int, log int);
    COPY t1 FROM 'data1.txt'

  }
  file delete data1.txt
  execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
} {0 1 2 3 4 5}

# Basic aggregate functions.
#
do_test select3-1.1 {
  execsql {SELECT count(*) FROM t1}

#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.10 2004/05/27 17:22:56 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
  execsql {
    CREATE TABLE t1(n int, log int);
    BEGIN;
  }
  for {set i 1} {$i<32} {incr i} {
    for {set j 0} {pow(2,$j)<$i} {incr j} {}

    execsql "INSERT INTO t1 VALUES($i,$j)"
  }

  execsql {


    COMMIT
  }

  execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
} {0 1 2 3 4 5}

# Basic aggregate functions.
#
do_test select3-1.1 {
  execsql {SELECT count(*) FROM t1}

#    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.15 2004/05/14 11:00:53 danielk1977 Exp $

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

# Build some test data
#
set fd [open data1.txt w]



for {set i 1} {$i<32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}
  puts $fd "$i\t$j"

}
close $fd
execsql {
  CREATE TABLE t1(n int, log int);
  COPY t1 FROM 'data1.txt'

}
file delete data1.txt

do_test select4-1.0 {
  execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
} {0 1 2 3 4 5}

# Union All operator
#

#    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.16 2004/05/27 17:22:56 drh Exp $

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

# Build some test data
#
execsql {
  CREATE TABLE t1(n int, log int);
  BEGIN;
}
for {set i 1} {$i<32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}

  execsql "INSERT INTO t1 VALUES($i,$j)"
}

execsql {


  COMMIT;
}


do_test select4-1.0 {
  execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
} {0 1 2 3 4 5}

# Union All operator
#

#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select5.test,v 1.6 2001/10/15 00:44:36 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]



for {set i 1} {$i<32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}
  puts $fd "[expr {32-$i}]\t[expr {10-$j}]"

}
close $fd
execsql {
  CREATE TABLE t1(x int, y int);
  COPY t1 FROM 'data1.txt'

}
file delete data1.txt

do_test select5-1.0 {
  execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
} {5 6 7 8 9 10}

# Sort by an aggregate function.
#

#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select5.test,v 1.7 2004/05/27 17:22:56 drh Exp $

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

# Build some test data
#
execsql {
  CREATE TABLE t1(x int, y int);
  BEGIN;
}
for {set i 1} {$i<32} {incr i} {
  for {set j 0} {pow(2,$j)<$i} {incr j} {}

  execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
}

execsql {


  COMMIT
}


do_test select5-1.0 {
  execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
} {5 6 7 8 9 10}

# Sort by an aggregate function.
#

#    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 CREATE TABLE statement.
#
# $Id: sort.test,v 1.11 2004/05/21 02:14:25 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
  set fd [open data.txt w]
  puts $fd "1\tone\t0\tI\t3.141592653"
  puts $fd "2\ttwo\t1\tII\t2.15"
  puts $fd "3\tthree\t1\tIII\t4221.0"
  puts $fd "4\tfour\t2\tIV\t-0.0013442"
  puts $fd "5\tfive\t2\tV\t-11"
  puts $fd "6\tsix\t2\tVI\t0.123"
  puts $fd "7\tseven\t2\tVII\t123.0"
  puts $fd "8\teight\t3\tVIII\t-1.6"
  close $fd
  execsql {
    CREATE TABLE t1(
       n int,
       v varchar(10),
       log int,
       roman varchar(10),
       flt real
    );
    COPY t1 FROM 'data.txt'







  }
  file delete data.txt
  execsql {SELECT count(*) FROM t1}
} {8}

do_test sort-1.1 {
  execsql {SELECT n FROM t1 ORDER BY n}
} {1 2 3 4 5 6 7 8}
do_test sort-1.1.1 {

#    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 CREATE TABLE statement.
#
# $Id: sort.test,v 1.12 2004/05/27 17:22:56 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {










  execsql {
    CREATE TABLE t1(
       n int,
       v varchar(10),
       log int,
       roman varchar(10),
       flt real
    );
    INSERT INTO t1 VALUES(1,'one',0,'I',3.141592653);
    INSERT INTO t1 VALUES(2,'two',1,'II',2.15);
    INSERT INTO t1 VALUES(3,'three',1,'III',4221.0);
    INSERT INTO t1 VALUES(4,'four',2,'IV',-0.0013442);
    INSERT INTO t1 VALUES(5,'five',2,'V',-11);
    INSERT INTO t1 VALUES(6,'six',2,'VI',0.123);
    INSERT INTO t1 VALUES(7,'seven',2,'VII',123.0);
    INSERT INTO t1 VALUES(8,'eight',3,'VIII',-1.6);
  }

  execsql {SELECT count(*) FROM t1}
} {8}

do_test sort-1.1 {
  execsql {SELECT n FROM t1 ORDER BY n}
} {1 2 3 4 5 6 7 8}
do_test sort-1.1.1 {