**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.59 2002/02/18 18:30:33 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

/*
** This is the callback routine for the code that initializes the
** database.  See sqliteInit() below for additional information.
................................................................................
  db = sqliteMalloc( sizeof(sqlite) );
  if( pzErrMsg ) *pzErrMsg = 0;
  if( db==0 ) goto no_mem_on_open;
  sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0);
  sqliteHashInit(&db->idxHash, SQLITE_HASH_STRING, 0);
  sqliteHashInit(&db->tblDrop, SQLITE_HASH_POINTER, 0);
  sqliteHashInit(&db->idxDrop, SQLITE_HASH_POINTER, 0);
  db->nextRowid = sqliteRandomInteger();
  db->onError = OE_Default;

  
  /* Open the backend database driver */
  rc = sqliteBtreeOpen(zFilename, mode, MAX_PAGES, &db->pBe);
  if( rc!=SQLITE_OK ){
    switch( rc ){
      default: {
        sqliteSetString(pzErrMsg, "unable to open database: ", zFilename, 0);

**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.60 2002/02/19 22:42:05 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"

/*
** This is the callback routine for the code that initializes the
** database.  See sqliteInit() below for additional information.
................................................................................
  db = sqliteMalloc( sizeof(sqlite) );
  if( pzErrMsg ) *pzErrMsg = 0;
  if( db==0 ) goto no_mem_on_open;
  sqliteHashInit(&db->tblHash, SQLITE_HASH_STRING, 0);
  sqliteHashInit(&db->idxHash, SQLITE_HASH_STRING, 0);
  sqliteHashInit(&db->tblDrop, SQLITE_HASH_POINTER, 0);
  sqliteHashInit(&db->idxDrop, SQLITE_HASH_POINTER, 0);

  db->onError = OE_Default;
  db->priorNewRowid = 0;
  
  /* Open the backend database driver */
  rc = sqliteBtreeOpen(zFilename, mode, MAX_PAGES, &db->pBe);
  if( rc!=SQLITE_OK ){
    switch( rc ){
      default: {
        sqliteSetString(pzErrMsg, "unable to open database: ", zFilename, 0);

**    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.62 2002/02/19 15:00:08 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
  if( v==0 ) return 0;
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, p->pSrc, p->pEList);
  }

  /* Begin generating code
  */
  base = pParse->nTab;
  eList.nExpr = 1;
  memset(&eListItem, 0, sizeof(eListItem));
  eList.a = &eListItem;
  eList.a[0].pExpr = pExpr;

  openOp = pTab->isTemp ? OP_OpenAux : OP_Open;

  sqliteVdbeAddOp(v, openOp, base, pTab->tnum);

  if( pIdx==0 ){
    sqliteVdbeAddOp(v, seekOp, base, 0);
  }else{
    sqliteVdbeAddOp(v, openOp, base+1, pIdx->tnum);

    sqliteVdbeAddOp(v, seekOp, base+1, 0);
    sqliteVdbeAddOp(v, OP_IdxRecno, base+1, 0);
    sqliteVdbeAddOp(v, OP_Close, base+1, 0);
    sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
  }




  cont = sqliteVdbeMakeLabel(v);
  selectInnerLoop(pParse, &eList, base, 1, 0, -1, eDest, iParm, cont, cont);
  sqliteVdbeResolveLabel(v, cont);
  sqliteVdbeAddOp(v, OP_Close, base, 0);
  return 1;
}

................................................................................
    }
  }

  /* Check for the special case of a min() or max() function by itself
  ** in the result set.
  */
  if( simpleMinMaxQuery(pParse, p, eDest, iParm) ){

    goto select_end;
  }

  /* Begin generating code.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto select_end;

**    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.63 2002/02/19 22:42:05 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
  if( v==0 ) return 0;
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, p->pSrc, p->pEList);
  }

  /* Begin generating code
  */
  if( !pParse->schemaVerified && (pParse->db->flags & SQLITE_InTrans)==0 ){
    sqliteVdbeAddOp(v, OP_VerifyCookie, pParse->db->schema_cookie, 0);
    pParse->schemaVerified = 1;


  }
  openOp = pTab->isTemp ? OP_OpenAux : OP_Open;
  base = pParse->nTab;
  sqliteVdbeAddOp(v, openOp, base, pTab->tnum);
  sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
  if( pIdx==0 ){
    sqliteVdbeAddOp(v, seekOp, base, 0);
  }else{
    sqliteVdbeAddOp(v, openOp, base+1, pIdx->tnum);
    sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
    sqliteVdbeAddOp(v, seekOp, base+1, 0);
    sqliteVdbeAddOp(v, OP_IdxRecno, base+1, 0);
    sqliteVdbeAddOp(v, OP_Close, base+1, 0);
    sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
  }
  eList.nExpr = 1;
  memset(&eListItem, 0, sizeof(eListItem));
  eList.a = &eListItem;
  eList.a[0].pExpr = pExpr;
  cont = sqliteVdbeMakeLabel(v);
  selectInnerLoop(pParse, &eList, base, 1, 0, -1, eDest, iParm, cont, cont);
  sqliteVdbeResolveLabel(v, cont);
  sqliteVdbeAddOp(v, OP_Close, base, 0);
  return 1;
}

................................................................................
    }
  }

  /* Check for the special case of a min() or max() function by itself
  ** in the result set.
  */
  if( simpleMinMaxQuery(pParse, p, eDest, iParm) ){
    rc = 0;
    goto select_end;
  }

  /* Begin generating code.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto select_end;

**    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.87 2002/02/18 18:30:33 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
  void *pBusyArg;               /* 1st Argument to the busy callback */
  int (*xBusyCallback)(void *,const char*,int);  /* The busy callback */
  Hash tblHash;                 /* All tables indexed by name */
  Hash idxHash;                 /* All (named) indices indexed by name */
  Hash tblDrop;                 /* Uncommitted DROP TABLEs */
  Hash idxDrop;                 /* Uncommitted DROP INDEXs */
  int lastRowid;                /* ROWID of most recent insert */
  int nextRowid;                /* Next generated rowID */
  int onError;                  /* Default conflict algorithm */
};

/*
** Possible values for the sqlite.flags.
*/
#define SQLITE_VdbeTrace      0x00000001  /* True to trace VDBE execution */

**    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.88 2002/02/19 22:42:05 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
  void *pBusyArg;               /* 1st Argument to the busy callback */
  int (*xBusyCallback)(void *,const char*,int);  /* The busy callback */
  Hash tblHash;                 /* All tables indexed by name */
  Hash idxHash;                 /* All (named) indices indexed by name */
  Hash tblDrop;                 /* Uncommitted DROP TABLEs */
  Hash idxDrop;                 /* Uncommitted DROP INDEXs */
  int lastRowid;                /* ROWID of most recent insert */
  int priorNewRowid;            /* Last randomly generated ROWID */
  int onError;                  /* Default conflict algorithm */
};

/*
** Possible values for the sqlite.flags.
*/
#define SQLITE_VdbeTrace      0x00000001  /* True to trace VDBE execution */

** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.119 2002/02/19 15:00:08 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
*/
struct Cursor {
  BtCursor *pCursor;    /* The cursor structure of the backend */
  int lastRecno;        /* Last recno from a Next or NextIdx operation */
  Bool recnoIsValid;    /* True if lastRecno is valid */
  Bool keyAsData;       /* The OP_Column command works on key instead of data */
  Bool atFirst;         /* True if pointing to first entry */

  Btree *pBt;           /* Separate file holding temporary table */
};
typedef struct Cursor Cursor;

/*
** A sorter builds a list of elements to be sorted.  Each element of
** the list is an instance of the following structure.
................................................................................
case OP_NewRecno: {
  int i = pOp->p1;
  int v = 0;
  Cursor *pC;
  if( VERIFY( i<0 || i>=p->nCursor || ) (pC = &p->aCsr[i])->pCursor==0 ){
    v = 0;
  }else{






    /* A probablistic algorithm is used to locate an unused rowid.

    ** We select a rowid at random and see if it exists in the table.

    ** If it does not exist, we have succeeded.  If the random rowid
    ** does exist, we select a new one and try again, up to 1000 times.
    **
    ** For a table with less than 2 billion entries, the probability
    ** of not finding a unused rowid is about 1.0e-300.  This is a 
    ** non-zero probability, but it is still vanishingly small and should
    ** never cause a problem.  You are much, much more likely to have a
    ** hardware failure than for this algorithm to fail.
    **
................................................................................
    ** source of random numbers.  Is a library function like lrand48()
    ** good enough?  Maybe. Maybe not. It's hard to know whether there
    ** might be subtle bugs is some implementations of lrand48() that
    ** could cause problems. To avoid uncertainty, SQLite uses its own 
    ** random number generator based on the RC4 algorithm.
    **
    ** To promote locality of reference for repetitive inserts, the
    ** first few attempts at chosing a rowid pick values just a little
    ** larger than the previous rowid.  This has been shown experimentally
    ** to double the speed of the COPY operation.
    */
    int res, rx, cnt, x;
    cnt = 0;















    v = db->nextRowid;

    do{
      if( cnt>5 ){

        v = sqliteRandomInteger();

      }else{
        v += sqliteRandomByte() + 1;
      }
      if( v==0 ) continue;
      x = intToKey(v);
      rx = sqliteBtreeMoveto(pC->pCursor, &x, sizeof(int), &res);
      cnt++;
    }while( cnt<1000 && rx==SQLITE_OK && res==0 );
    db->nextRowid = v;
    if( rx==SQLITE_OK && res==0 ){
      rc = SQLITE_FULL;
      goto abort_due_to_error;

    }
  }
  VERIFY( NeedStack(p, p->tos+1); )
  p->tos++;
  aStack[p->tos].i = v;
  aStack[p->tos].flags = STK_Int;
  break;

** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.120 2002/02/19 22:42:05 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
*/
struct Cursor {
  BtCursor *pCursor;    /* The cursor structure of the backend */
  int lastRecno;        /* Last recno from a Next or NextIdx operation */
  Bool recnoIsValid;    /* True if lastRecno is valid */
  Bool keyAsData;       /* The OP_Column command works on key instead of data */
  Bool atFirst;         /* True if pointing to first entry */
  Bool useRandomRowid;  /* Generate new record numbers semi-randomly */
  Btree *pBt;           /* Separate file holding temporary table */
};
typedef struct Cursor Cursor;

/*
** A sorter builds a list of elements to be sorted.  Each element of
** the list is an instance of the following structure.
................................................................................
case OP_NewRecno: {
  int i = pOp->p1;
  int v = 0;
  Cursor *pC;
  if( VERIFY( i<0 || i>=p->nCursor || ) (pC = &p->aCsr[i])->pCursor==0 ){
    v = 0;
  }else{
    /* The next rowid or record number (different terms for the same
    ** thing) is obtained in a two-step algorithm.
    **
    ** First we attempt to find the largest existing rowid and add one
    ** to that.  But if the largest existing rowid is already the maximum
    ** positive integer, we have to fall through to the second
    ** probabilistic algorithm
    **
    ** The second algorithm is to select a rowid at random and see if
    ** it already exists in the table.  If it does not exist, we have
    ** succeeded.  If the random rowid does exist, we select a new one
    ** and try again, up to 1000 times.
    **
    ** For a table with less than 2 billion entries, the probability
    ** of not finding a unused rowid is about 1.0e-300.  This is a 
    ** non-zero probability, but it is still vanishingly small and should
    ** never cause a problem.  You are much, much more likely to have a
    ** hardware failure than for this algorithm to fail.
    **
................................................................................
    ** source of random numbers.  Is a library function like lrand48()
    ** good enough?  Maybe. Maybe not. It's hard to know whether there
    ** might be subtle bugs is some implementations of lrand48() that
    ** could cause problems. To avoid uncertainty, SQLite uses its own 
    ** random number generator based on the RC4 algorithm.
    **
    ** To promote locality of reference for repetitive inserts, the
    ** first few attempts at chosing a random rowid pick values just a little
    ** larger than the previous rowid.  This has been shown experimentally
    ** to double the speed of the COPY operation.
    */
    int res, rx, cnt, x;
    cnt = 0;
    if( !pC->useRandomRowid ){
      rx = sqliteBtreeLast(pC->pCursor, &res);
      if( res ){
        v = 1;
      }else{
        sqliteBtreeKey(pC->pCursor, 0, sizeof(v), (void*)&v);
        v = keyToInt(v);
        if( v==0x7fffffff ){
          pC->useRandomRowid = 1;
        }else{
          v++;
        }
      }
    }
    if( pC->useRandomRowid ){
      v = db->priorNewRowid;
      cnt = 0;
      do{

        if( v==0 || cnt>2 ){
          v = sqliteRandomInteger();
          if( cnt<5 ) v &= 0xffffff;
        }else{
          v += sqliteRandomByte() + 1;
        }
        if( v==0 ) continue;
        x = intToKey(v);
        rx = sqliteBtreeMoveto(pC->pCursor, &x, sizeof(int), &res);
        cnt++;
      }while( cnt<1000 && rx==SQLITE_OK && res==0 );
      db->priorNewRowid = v;
      if( rx==SQLITE_OK && res==0 ){
        rc = SQLITE_FULL;
        goto abort_due_to_error;
      }
    }
  }
  VERIFY( NeedStack(p, p->tos+1); )
  p->tos++;
  aStack[p->tos].i = v;
  aStack[p->tos].flags = STK_Int;
  break;

#
#***********************************************************************
# 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.7 2002/01/29 23:07:02 drh Exp $

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

# Create a table with a primary key and a datatype other than
# integer
#
................................................................................
  }
} {hello one second}

# Try to change the ROWID for the new entry.
#
do_test intpkey-1.11 {
  execsql {
    UPDATE t1 SET a=7 WHERE b='one';
    SELECT * FROM t1;
  }
} {5 hello world 6 second entry 7 one two}

# Make sure SELECT statements are able to use the primary key column
# as an index.
#
do_test intpkey-1.12 {
  execsql {
    SELECT * FROM t1 WHERE a==7;
  }
} {7 one two}

# Try to insert a non-integer value into the primary key field.  This
# should result in a data type mismatch.
#
do_test intpkey-1.13 {
  set r [catch {execsql {
    INSERT INTO t1 VALUES('x','y','z');
................................................................................
  set r [catch {execsql {
    INSERT INTO t1 VALUES(-3,'y','z');
  }} msg]
  lappend r $msg
} {0 {}}
do_test intpkey-1.16 {
  execsql {SELECT * FROM t1}
} {-3 y z 5 hello world 6 second entry 7 one two}

#### INDICES
# Check to make sure indices work correctly with integer primary keys
#
do_test intpkey-2.1 {
  execsql {
    CREATE INDEX i1 ON t1(b);
................................................................................
    SELECT * FROM t1 WHERE b=='y';
  }
} {8 y z}
do_test intpkey-2.3 {
  execsql {
    SELECT rowid, * FROM t1;
  }
} {5 5 hello world 6 6 second entry 7 7 one two 8 8 y z}
do_test intpkey-2.4 {
  execsql {
    SELECT rowid, * FROM t1 WHERE b<'second'
  }
} {5 5 hello world 7 7 one two}
do_test intpkey-2.4.1 {
  execsql {
    SELECT rowid, * FROM t1 WHERE 'second'>b
  }
} {5 5 hello world 7 7 one two}
do_test intpkey-2.4.2 {
  execsql {
    SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b
  }
} {5 5 hello world 7 7 one two}
do_test intpkey-2.4.3 {
  execsql {
    SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid
  }
} {5 5 hello world 7 7 one two}
do_test intpkey-2.5 {
  execsql {
    SELECT rowid, * FROM t1 WHERE b>'a'
  }
} {5 5 hello world 7 7 one two 6 6 second entry 8 8 y z}
do_test intpkey-2.6 {
  execsql {
    DELETE FROM t1 WHERE rowid=7;
    SELECT * FROM t1 WHERE b>'a';
  }
} {5 hello world 6 second entry 8 y z}
do_test intpkey-2.7 {
  execsql {
    UPDATE t1 SET a=-4 WHERE rowid=8;
    SELECT * FROM t1 WHERE b>'a';

#
#***********************************************************************
# 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.8 2002/02/19 22:42:06 drh Exp $

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

# Create a table with a primary key and a datatype other than
# integer
#
................................................................................
  }
} {hello one second}

# Try to change the ROWID for the new entry.
#
do_test intpkey-1.11 {
  execsql {
    UPDATE t1 SET a=4 WHERE b='one';
    SELECT * FROM t1;
  }
} {4 one two 5 hello world 6 second entry}

# Make sure SELECT statements are able to use the primary key column
# as an index.
#
do_test intpkey-1.12 {
  execsql {
    SELECT * FROM t1 WHERE a==4;
  }
} {4 one two}

# Try to insert a non-integer value into the primary key field.  This
# should result in a data type mismatch.
#
do_test intpkey-1.13 {
  set r [catch {execsql {
    INSERT INTO t1 VALUES('x','y','z');
................................................................................
  set r [catch {execsql {
    INSERT INTO t1 VALUES(-3,'y','z');
  }} msg]
  lappend r $msg
} {0 {}}
do_test intpkey-1.16 {
  execsql {SELECT * FROM t1}
} {-3 y z 4 one two 5 hello world 6 second entry}

#### INDICES
# Check to make sure indices work correctly with integer primary keys
#
do_test intpkey-2.1 {
  execsql {
    CREATE INDEX i1 ON t1(b);
................................................................................
    SELECT * FROM t1 WHERE b=='y';
  }
} {8 y z}
do_test intpkey-2.3 {
  execsql {
    SELECT rowid, * FROM t1;
  }
} {4 4 one two 5 5 hello world 6 6 second entry 8 8 y z}
do_test intpkey-2.4 {
  execsql {
    SELECT rowid, * FROM t1 WHERE b<'second'
  }
} {5 5 hello world 4 4 one two}
do_test intpkey-2.4.1 {
  execsql {
    SELECT rowid, * FROM t1 WHERE 'second'>b
  }
} {5 5 hello world 4 4 one two}
do_test intpkey-2.4.2 {
  execsql {
    SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b
  }
} {4 4 one two 5 5 hello world}
do_test intpkey-2.4.3 {
  execsql {
    SELECT rowid, * FROM t1 WHERE 8>rowid AND 'second'>b AND 0<rowid
  }
} {4 4 one two 5 5 hello world}
do_test intpkey-2.5 {
  execsql {
    SELECT rowid, * FROM t1 WHERE b>'a'
  }
} {5 5 hello world 4 4 one two 6 6 second entry 8 8 y z}
do_test intpkey-2.6 {
  execsql {
    DELETE FROM t1 WHERE rowid=4;
    SELECT * FROM t1 WHERE b>'a';
  }
} {5 hello world 6 second entry 8 y z}
do_test intpkey-2.7 {
  execsql {
    UPDATE t1 SET a=-4 WHERE rowid=8;
    SELECT * FROM t1 WHERE b>'a';

# 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 SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.1 2002/02/19 22:42:06 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
    CREATE TABLE t1(x, y);
    INSERT INTO t1 VALUES(1,1);
    INSERT INTO t1 VALUES(2,2);
    INSERT INTO t1 VALUES(3,2);
    INSERT INTO t1 VALUES(4,3);
    INSERT INTO t1 VALUES(5,3);
    INSERT INTO t1 VALUES(6,3);
    INSERT INTO t1 VALUES(7,3);
    INSERT INTO t1 VALUES(8,4);
    INSERT INTO t1 VALUES(9,4);
    INSERT INTO t1 VALUES(10,4);
    INSERT INTO t1 VALUES(11,4);
    INSERT INTO t1 VALUES(12,4);
    INSERT INTO t1 VALUES(13,4);
    INSERT INTO t1 VALUES(14,4);
    INSERT INTO t1 VALUES(15,4);
    INSERT INTO t1 VALUES(16,5);
    INSERT INTO t1 VALUES(17,5);
    INSERT INTO t1 VALUES(18,5);
    INSERT INTO t1 VALUES(19,5);
    INSERT INTO t1 VALUES(20,5);
    COMMIT;
    SELECT DISTINCT y FROM t1 ORDER BY y;
  }
} {1 2 3 4 5}

do_test minmax-1.1 {
  set sqlite_search_count 0
  execsql {SELECT min(x) FROM t1}
} {1}
do_test minmax-1.2 {
  set sqlite_search_count
} {19}
do_test minmax-1.3 {
  set sqlite_search_count 0
  execsql {SELECT max(x) FROM t1}
} {20}
do_test minmax-1.4 {
  set sqlite_search_count
} {19}
do_test minmax-1.5 {
  execsql {CREATE INDEX t1i1 ON t1(x)}
  set sqlite_search_count 0
  execsql {SELECT min(x) FROM t1}
} {1}
do_test minmax-1.6 {
  set sqlite_search_count
} {1}
do_test minmax-1.7 {
  set sqlite_search_count 0
  execsql {SELECT max(x) FROM t1}
} {20}
do_test minmax-1.8 {
  set sqlite_search_count
} {1}
do_test minmax-1.9 {
  set sqlite_search_count 0
  execsql {SELECT max(y) FROM t1}
} {5}
do_test minmax-1.10 {
  set sqlite_search_count
} {19}

do_test minmax-2.0 {
  execsql {
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
    INSERT INTO t2 SELECT * FROM t1;
  }
  set sqlite_search_count 0
  execsql {SELECT min(a) FROM t2}
} {1}
do_test minmax-2.1 {
  set sqlite_search_count
} {0}
do_test minmax-2.2 {
  set sqlite_search_count 0
  execsql {SELECT max(a) FROM t2}
} {20}
do_test minmax-2.3 {
  set sqlite_search_count
} {0}

do_test minmax-3.0 {
  execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
  set sqlite_search_count 0
  execsql {SELECT max(a) FROM t2}
} {21}
do_test minmax-3.1 {
  set sqlite_search_count
} {0}
do_test minmax-3.2 {
  execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
  set sqlite_search_count 0
  execsql {
    SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2)
  }
} {999}
do_test minmax-3.3 {
  set sqlite_search_count
} {0}


finish_test

#    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 magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.7 2002/01/04 03:09:30 drh Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {
................................................................................

# Columns defined in the CREATE statement override the buildin ROWID
# column names.
#
do_test rowid-3.1 {
  execsql {
    CREATE TABLE t2(rowid int, x int, y int);
    INSERT INTO t2 VALUES(1,2,3);
    INSERT INTO t2 VALUES(4,5,6);
    INSERT INTO t2 VALUES(7,8,9);
    SELECT * FROM t2 ORDER BY x;
  }
} {1 2 3 4 5 6 7 8 9}
do_test rowid-3.2 {
  execsql {SELECT * FROM t2 ORDER BY rowid}
} {1 2 3 4 5 6 7 8 9}
do_test rowid-3.3 {
  execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}
} {1 2 3 4 5 6 7 8 9}
do_test rowid-3.4 {
  set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
  foreach {a b c d e f} $r1 {}
  set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]
  foreach {u v w x y z} $r2 {}
  expr {$u==$e && $w==$c && $y==$a}
} {1}
................................................................................
#
do_test rowid-6.1 {
  execsql {
    SELECT x FROM t1
  }
} {1 2 3 4 5 6 7 8}
do_test rowid-6.2 {
  for {set ::norow 1} {[execsql {SELECT x FROM t1 WHERE rowid=10}]!=""} \
    {incr ::norow} {}

  execsql [subst {
    DELETE FROM t1 WHERE rowid=$::norow
  }]
} {}
do_test rowid-6.3 {
  execsql {
    SELECT x FROM t1
  }
} {1 2 3 4 5 6 7 8}






































































finish_test

#    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 magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.8 2002/02/19 22:42:06 drh Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {
................................................................................

# Columns defined in the CREATE statement override the buildin ROWID
# column names.
#
do_test rowid-3.1 {
  execsql {
    CREATE TABLE t2(rowid int, x int, y int);
    INSERT INTO t2 VALUES(0,2,3);
    INSERT INTO t2 VALUES(4,5,6);
    INSERT INTO t2 VALUES(7,8,9);
    SELECT * FROM t2 ORDER BY x;
  }
} {0 2 3 4 5 6 7 8 9}
do_test rowid-3.2 {
  execsql {SELECT * FROM t2 ORDER BY rowid}
} {0 2 3 4 5 6 7 8 9}
do_test rowid-3.3 {
  execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}
} {0 2 3 4 5 6 7 8 9}
do_test rowid-3.4 {
  set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
  foreach {a b c d e f} $r1 {}
  set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]
  foreach {u v w x y z} $r2 {}
  expr {$u==$e && $w==$c && $y==$a}
} {1}
................................................................................
#
do_test rowid-6.1 {
  execsql {
    SELECT x FROM t1
  }
} {1 2 3 4 5 6 7 8}
do_test rowid-6.2 {
  for {set ::norow 1} {1} {incr ::norow} {
    if {[execsql "SELECT x FROM t1 WHERE rowid=$::norow"]==""}  break
  }
  execsql [subst {
    DELETE FROM t1 WHERE rowid=$::norow
  }]
} {}
do_test rowid-6.3 {
  execsql {
    SELECT x FROM t1
  }
} {1 2 3 4 5 6 7 8}

# Beginning with version 2.3.4, SQLite computes rowids of new rows by
# finding the maximum current rowid and adding one.  It falls back to
# the old random algorithm if the maximum rowid is the largest integer.
# The following tests are for this new behavior.
#
do_test rowid-7.0 {
  execsql {
    DELETE FROM t1;
    DROP TABLE t2;
    DROP INDEX idxt1;
    INSERT INTO t1 VALUES(1,2);
    SELECT rowid, * FROM t1;
  }
} {1 1 2}
do_test rowid-7.1 {
  execsql {
    INSERT INTO t1 VALUES(99,100);
    SELECT rowid,* FROM t1
  }
} {1 1 2 2 99 100}
do_test rowid-7.2 {
  execsql {
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
    INSERT INTO t2(b) VALUES(55);
    SELECT * FROM t2;
  }
} {1 55}
do_test rowid-7.3 {
  execsql {
    INSERT INTO t2(b) VALUES(66);
    SELECT * FROM t2;
  }
} {1 55 2 66}
do_test rowid-7.4 {
  execsql {
    INSERT INTO t2(a,b) VALUES(1000000,77);
    INSERT INTO t2(b) VALUES(88);
    SELECT * FROM t2;
  }
} {1 55 2 66 1000000 77 1000001 88}
do_test rowid-7.5 {
  execsql {
    INSERT INTO t2(a,b) VALUES(2147483647,99);
    INSERT INTO t2(b) VALUES(11);
    SELECT b FROM t2 ORDER BY b;
  }
} {11 55 66 77 88 99}
do_test rowid-7.6 {
  execsql {
    SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647);
  }
} {11}
do_test rowid-7.7 {
  execsql {
    INSERT INTO t2(b) VALUES(22);
    INSERT INTO t2(b) VALUES(33);
    INSERT INTO t2(b) VALUES(44);
    INSERT INTO t2(b) VALUES(55);
    SELECT b FROM t2 WHERE a NOT IN(1,2,1000000,1000001,2147483647) ORDER BY b;
  }
} {11 22 33 44 55}
do_test rowid-7.8 {
  execsql {
    DELETE FROM t2 WHERE a!=2;
    INSERT INTO t2(b) VALUES(111);
    SELECT * FROM t2;
  }
} {2 66 3 111}

finish_test

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.23 2002/01/16 21:00:28 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
header file that comes in the source tree.</p>

<h2>The ROWID of the most recent insert</h2>

<p>Every row of an SQLite table has a unique integer key.  If the
table has a column labeled INTEGER PRIMARY KEY, then that column
servers as the key.  If there is no INTEGER PRIMARY KEY column then
the key is a random integer.  The key for a row can be accessed in
a SELECT statement or used in a WHERE or ORDER BY clause using any
of the names "ROWID", "OID", or "_ROWID_".</p>

<p>When you do an insert into a table that does not have an INTEGER PRIMARY
KEY column, or if the table does have an INTEGER PRIMARY KEY but the value
for that column is not specified in the VALUES clause of the insert, then
the key is automatically generated.  You can find the value of the key

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.24 2002/02/19 22:42:06 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
header file that comes in the source tree.</p>

<h2>The ROWID of the most recent insert</h2>

<p>Every row of an SQLite table has a unique integer key.  If the
table has a column labeled INTEGER PRIMARY KEY, then that column
servers as the key.  If there is no INTEGER PRIMARY KEY column then
the key is a unique integer.  The key for a row can be accessed in
a SELECT statement or used in a WHERE or ORDER BY clause using any
of the names "ROWID", "OID", or "_ROWID_".</p>

<p>When you do an insert into a table that does not have an INTEGER PRIMARY
KEY column, or if the table does have an INTEGER PRIMARY KEY but the value
for that column is not specified in the VALUES clause of the insert, then
the key is automatically generated.  You can find the value of the key

  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Feb * (2.3.4)} {
<li>Change the name of the sanity_check PRAGMA to <b>integrity_check</b>
    and make it available in all compiles.</li>



}

chng {2002 Feb 18 (2.3.3)} {
<li>Allow identifiers to be quoted in square brackets, for compatibility
    with MS-Access.</li>
<li>Added support for sub-queries in the FROM clause of a SELECT.</li>
<li>More efficient implementation of sqliteFileExists() under Windows.

  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Feb * (2.3.4)} {
<li>Change the name of the sanity_check PRAGMA to <b>integrity_check</b>
    and make it available in all compiles.</li>
<li>SELECT min() or max() of an indexed column with no WHERE or GROUP BY
    clause is handled as a special case which avoids a complete table scan.</li>
<li>Automatically generated ROWIDs are now sequential.</li>
}

chng {2002 Feb 18 (2.3.3)} {
<li>Allow identifiers to be quoted in square brackets, for compatibility
    with MS-Access.</li>
<li>Added support for sub-queries in the FROM clause of a SELECT.</li>
<li>More efficient implementation of sqliteFileExists() under Windows.

#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.7 2002/01/22 03:13:43 drh Exp $}

puts {<html>
<head>
  <title>SQLite Frequently Asked Questions</title>
</head>
<body bgcolor="white">
<h1 align="center">Frequently Asked Questions</h1>
................................................................................

#############
# Enter questions and answers here.

faq {
  How do I create an AUTOINCREMENT field.
} {
  SQLite does not support AUTOINCREMENT.  If you need a unique key for
  a new entry in a table, you can create an auxiliary table
  with a single entry that holds the next available value for that key.
  Like this:
<blockquote><pre>
CREATE TABLE counter(cnt);
INSERT INTO counter VALUES(1);
</pre></blockquote>
  Once you have a counter set up, you can generate a unique key as follows:
<blockquote><pre>
BEGIN TRANSACTION;
SELECT cnt FROM counter;
UPDATE counter SET cnt=cnt+1;
COMMIT;
</pre></blockquote>
  There are other ways of simulating the effect of AUTOINCREMENT but
  this approach seems to be the easiest and most efficient.

  <p><i>New in SQLite version 2.2.0:</i>
  If one of the columns in a table has type INTEGER PRIMARY KEY and
  you do an INSERT on that table that does not specify a value for
  the primary key, then a unique random number is inserted automatically
  in that column.  This automatically generated key is random, not 
  sequential, but you can still use it as a unique identifier.</p>

  <p>Here is an example of how the INTEGER PRIMARY KEY feature can be
  used:</p>








  <blockquote><pre>
CREATE TABLE ex2(
  cnum INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT

);
INSERT INTO ex2(name,email) VALUES('drh','drh@hwaci.com');
INSERT INTO ex2(name,email) VALUES('alle','alle@hwaci.com');
SELECT * FROM ex1;
</pre></blockquote>

  <p>Notice that the primary key column <b>cnum</b> is not specified on
  the INSERT statements.  The output of the SELECT on the last line will
  be something like this:</p>


  <blockquote>
     1597027670|drh|drh@hwaci.com<br>
     1597027853|alle|alle@hwaci.com

  </blockquote>

  <p>The randomly generated keys in this case are 1597027670 and
  1597027853.  You will probably get different keys every time you
  try this.  The keys will often be ascending, but this is not always
  the case and you cannot count on that behavior.  The keys will never
  be sequential.  If you need sequential keys, use the counter implemention
  described first.</p>














  <p>Beginning with version 2.2.3, there is a new API function named
  <b>sqlite_last_insert_rowid()</b> which will return the integer key
  for the most recent insert operation.  See the API documentation for
  details.</p>
}

#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.8 2002/02/19 22:42:06 drh Exp $}

puts {<html>
<head>
  <title>SQLite Frequently Asked Questions</title>
</head>
<body bgcolor="white">
<h1 align="center">Frequently Asked Questions</h1>
................................................................................

#############
# Enter questions and answers here.

faq {
  How do I create an AUTOINCREMENT field.
} {
  <p>Short answer: A column declared INTEGER PRIMARY KEY will
  autoincrement.</p>


























  <p>Here is the long answer:
  Beginning with version SQLite 2.3.4, If you declare a column of
  a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL
  into that column of the table, the NULL is automatically converted
  into an integer which is one greater than the largest value of that
  column over all other rows in the table, or 1 if the table is empty.
  For example, suppose you have a table like this:
<blockquote><pre>
CREATE TABLE t1(
  a INTEGER PRIMARY KEY,


  b INTEGER
);



</pre></blockquote>





  <p>With this table, the statement</p>
<blockquote><pre>


INSERT INTO t1 VALUES(NULL,123);
</pre></blockquote>







  <p>is logically equivalent to saying:</p>
<blockquote><pre>
INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);
</pre></blockquote>
  <p>For SQLite version 2.2.0 through 2.3.3, if you insert a NULL into
  an INTEGER PRIMARY KEY column, the NULL will be changed to a unique
  integer, but it will a semi-random integer.  Unique keys generated this
  way will not be sequential.  For SQLite version 2.3.4 and beyond, the
  unique keys will be sequential until the largest key reaches a value
  of 2147483647.  That is the largest 32-bit signed integer and cannot
  be incremented, so subsequent insert attempts will revert to the
  semi-random key generation algorithm of SQLite version 2.3.3 and
  earlier.</p>

  <p>Beginning with version 2.2.3, there is a new API function named
  <b>sqlite_last_insert_rowid()</b> which will return the integer key
  for the most recent insert operation.  See the API documentation for
  details.</p>
}

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.25 2002/02/19 13:39:23 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
on the primary key.  However, if primary key is on a single column
that has datatype INTEGER, then that column is used internally
as the actual key of the B-Tree for the table.  This means that the column
may only hold unique integer values.  (Except for this one case,
SQLite ignores the datatype specification of columns and allows
any kind of data to be put in a column regardless of its declared
datatype.)  If a table does not have an INTEGER PRIMARY KEY column,
then the B-Tree key will be a randomly generated integer.  The
B-Tree key for a row can always be accessed using one of the
special names "<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>".
This is true regardless of whether or not there is an INTEGER
PRIMARY KEY.</p>

<p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "TABLE" then the table that is created is only visible to the

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.26 2002/02/19 22:42:06 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
on the primary key.  However, if primary key is on a single column
that has datatype INTEGER, then that column is used internally
as the actual key of the B-Tree for the table.  This means that the column
may only hold unique integer values.  (Except for this one case,
SQLite ignores the datatype specification of columns and allows
any kind of data to be put in a column regardless of its declared
datatype.)  If a table does not have an INTEGER PRIMARY KEY column,
then the B-Tree key will be a automatically generated integer.  The
B-Tree key for a row can always be accessed using one of the
special names "<b>ROWID</b>", "<b>OID</b>", or "<b>_ROWID_</b>".
This is true regardless of whether or not there is an INTEGER
PRIMARY KEY.</p>

<p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "TABLE" then the table that is created is only visible to the