SQLite

**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.318 2005/03/29 03:10:59 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.

** See also sqlite3LocateTable().
*/
Table *sqlite3FindTable(sqlite3 *db, const char *zName, const char *zDatabase){
  Table *p = 0;
  int i;
  assert( zName!=0 );
  assert( (db->flags & SQLITE_Initialized) || db->init.busy );
  for(i=OMIT_TEMPDB; i<db->nDb; i++){
    int j = (i<2) ? i^1 : i;   /* Search TEMP before MAIN */
    if( zDatabase!=0 && sqlite3StrICmp(zDatabase, db->aDb[j].zName) ) continue;
    p = sqlite3HashFind(&db->aDb[j].tblHash, zName, strlen(zName)+1);
    if( p ) break;
  }
  return p;
}

** TEMP first, then MAIN, then any auxiliary databases added
** using the ATTACH command.
*/
Index *sqlite3FindIndex(sqlite3 *db, const char *zName, const char *zDb){
  Index *p = 0;
  int i;
  assert( (db->flags & SQLITE_Initialized) || db->init.busy );
  for(i=OMIT_TEMPDB; i<db->nDb; i++){
    int j = (i<2) ? i^1 : i;  /* Search TEMP before MAIN */
    if( zDb && sqlite3StrICmp(zDb, db->aDb[j].zName) ) continue;
    p = sqlite3HashFind(&db->aDb[j].idxHash, zName, strlen(zName)+1);
    if( p ) break;
  }
  return p;
}

  Db *pDb;       /* A database whose name space is being searched */
  char *zName;   /* Name we are searching for */

  zName = sqlite3NameFromToken(pName);
  if( zName ){
    n = strlen(zName);
    for(i=(db->nDb-1), pDb=&db->aDb[i]; i>=0; i--, pDb--){
      if( (!OMIT_TEMPDB || i!=1 ) && n==strlen(pDb->zName) && 
          0==sqlite3StrICmp(pDb->zName, zName) ){
        break;
      }
    }
    sqliteFree(zName);
  }
  return i;
}

  ** The call below sets the pName pointer to point at the token (pName1 or
  ** pName2) that stores the unqualified table name. The variable iDb is
  ** set to the index of the database that the table or view is to be
  ** created in.
  */
  iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pName);
  if( iDb<0 ) return;
  if( !OMIT_TEMPDB && isTemp && iDb>1 ){
    /* If creating a temp table, the name may not be qualified */
    sqlite3ErrorMsg(pParse, "temporary table name must be unqualified");
    return;
  }
  if( !OMIT_TEMPDB && isTemp ) iDb = 1;

  pParse->sNameToken = *pName;
  zName = sqlite3NameFromToken(pName);
  if( zName==0 ) return;
  if( SQLITE_OK!=sqlite3CheckObjectName(pParse, zName) ){
    goto begin_table_error;
  }
  if( db->init.iDb==1 ) isTemp = 1;
#ifndef SQLITE_OMIT_AUTHORIZATION
  assert( (isTemp & 1)==isTemp );
  {
    int code;
    char *zDb = db->aDb[iDb].zName;
    if( sqlite3AuthCheck(pParse, SQLITE_INSERT, SCHEMA_TABLE(isTemp), 0, zDb) ){
      goto begin_table_error;
    }
    if( isView ){
      if( !OMIT_TEMPDB && isTemp ){
        code = SQLITE_CREATE_TEMP_VIEW;
      }else{
        code = SQLITE_CREATE_VIEW;
      }
    }else{
      if( !OMIT_TEMPDB && isTemp ){
        code = SQLITE_CREATE_TEMP_TABLE;
      }else{
        code = SQLITE_CREATE_TABLE;
      }
    }
    if( sqlite3AuthCheck(pParse, code, zName, 0, zDb) ){
      goto begin_table_error;

    zSep = "\n  ";
    zSep2 = ",\n  ";
    zEnd = "\n)";
  }
  n += 35 + 6*p->nCol;
  zStmt = sqliteMallocRaw( n );
  if( zStmt==0 ) return 0;
  strcpy(zStmt, !OMIT_TEMPDB&&p->iDb==1 ? "CREATE TEMP TABLE ":"CREATE TABLE ");
  k = strlen(zStmt);
  identPut(zStmt, &k, p->zName);
  zStmt[k++] = '(';
  for(pCol=p->aCol, i=0; i<p->nCol; i++, pCol++){
    strcpy(&zStmt[k], zSep);
    k += strlen(&zStmt[k]);
    zSep = zSep2;

    int code;
    const char *zTab = SCHEMA_TABLE(pTab->iDb);
    const char *zDb = db->aDb[pTab->iDb].zName;
    if( sqlite3AuthCheck(pParse, SQLITE_DELETE, zTab, 0, zDb)){
      goto exit_drop_table;
    }
    if( isView ){
      if( !OMIT_TEMPDB && iDb==1 ){
        code = SQLITE_DROP_TEMP_VIEW;
      }else{
        code = SQLITE_DROP_VIEW;
      }
    }else{
      if( !OMIT_TEMPDB && iDb==1 ){
        code = SQLITE_DROP_TEMP_TABLE;
      }else{
        code = SQLITE_DROP_TABLE;
      }
    }
    if( sqlite3AuthCheck(pParse, code, pTab->zName, 0, zDb) ){
      goto exit_drop_table;

){
  Table *pTab = 0;   /* Table to be indexed */
  Index *pIndex = 0; /* The index to be created */
  char *zName = 0;
  int i, j;
  Token nullId;    /* Fake token for an empty ID list */
  DbFixer sFix;    /* For assigning database names to pTable */

  sqlite3 *db = pParse->db;

  int iDb;          /* Index of the database that is being written */
  Token *pName = 0; /* Unqualified name of the index to create */

  if( pParse->nErr || sqlite3_malloc_failed ) goto exit_create_index;

  /*
  ** Find the table that is to be indexed.  Return early if not found.
  */
  if( pTblName!=0 ){

    /* Use the two-part index name to determine the database 
    ** to search for the table. 'Fix' the table name to this db
    ** before looking up the table.
    */
    assert( pName1 && pName2 );
    iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pName);
    if( iDb<0 ) goto exit_create_index;

#ifndef SQLITE_OMIT_TEMPDB
    /* If the index name was unqualified, check if the the table
    ** is a temp table. If so, set the database to 1.
    */
    pTab = sqlite3SrcListLookup(pParse, pTblName);
    if( pName2 && pName2->n==0 && pTab && pTab->iDb==1 ){
      iDb = 1;
    }
#endif

    if( sqlite3FixInit(&sFix, pParse, iDb, "index", pName) &&
        sqlite3FixSrcList(&sFix, pTblName)
    ){
      goto exit_create_index;
    }
    pTab = sqlite3LocateTable(pParse, pTblName->a[0].zName, 

  }
#ifndef SQLITE_OMIT_VIEW
  if( pTab->pSelect ){
    sqlite3ErrorMsg(pParse, "views may not be indexed");
    goto exit_create_index;
  }
#endif


  /*
  ** Find the name of the index.  Make sure there is not already another
  ** index or table with the same name.  
  **
  ** Exception:  If we are reading the names of permanent indices from the
  ** sqlite_master table (because some other process changed the schema) and

    if( zName==0 ) goto exit_create_index;
  }

  /* Check for authorization to create an index.
  */
#ifndef SQLITE_OMIT_AUTHORIZATION
  {
    const char *zDb = db->aDb[iDb].zName;
    if( sqlite3AuthCheck(pParse, SQLITE_INSERT, SCHEMA_TABLE(iDb), 0, zDb) ){
      goto exit_create_index;
    }
    i = SQLITE_CREATE_INDEX;
    if( !OMIT_TEMPDB && iDb==1 ) i = SQLITE_CREATE_TEMP_INDEX;
    if( sqlite3AuthCheck(pParse, i, zName, pTab->zName, zDb) ){
      goto exit_create_index;
    }
  }
#endif

  /* If pList==0, it means this routine was called to make a primary

    int code = SQLITE_DROP_INDEX;
    Table *pTab = pIndex->pTable;
    const char *zDb = db->aDb[pIndex->iDb].zName;
    const char *zTab = SCHEMA_TABLE(pIndex->iDb);
    if( sqlite3AuthCheck(pParse, SQLITE_DELETE, zTab, 0, zDb) ){
      goto exit_drop_index;
    }
    if( !OMIT_TEMPDB && pIndex->iDb ) code = SQLITE_DROP_TEMP_INDEX;
    if( sqlite3AuthCheck(pParse, code, pIndex->zName, pTab->zName, zDb) ){
      goto exit_drop_index;
    }
  }
#endif

  /* Generate code to remove the index and from the master table */

    assert( iDb<db->nDb );
    assert( db->aDb[iDb].pBt!=0 || iDb==1 );
    assert( iDb<32 );
    mask = 1<<iDb;
    if( (pParse->cookieMask & mask)==0 ){
      pParse->cookieMask |= mask;
      pParse->cookieValue[iDb] = db->aDb[iDb].schema_cookie;
      if( !OMIT_TEMPDB && iDb==1 ){
        sqlite3OpenTempDatabase(pParse);
      }
    }
  }
}

/*

  Vdbe *v = sqlite3GetVdbe(pParse);
  if( v==0 ) return;
  sqlite3CodeVerifySchema(pParse, iDb);
  pParse->writeMask |= 1<<iDb;
  if( setStatement && pParse->nested==0 ){
    sqlite3VdbeAddOp(v, OP_Statement, iDb, 0);
  }
  if( (OMIT_TEMPDB || iDb!=1) && pParse->db->aDb[1].pBt!=0 ){
    sqlite3BeginWriteOperation(pParse, setStatement, 1);
  }
}

#ifndef SQLITE_OMIT_UTF16
/* 
** Return the transient sqlite3_value object used for encoding conversions

**
*************************************************************************
** 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.284 2005/03/29 03:10:59 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** The following constant value is used by the SQLITE_BIGENDIAN and

  int size;
  Table *pTab;
  char const *azArg[5];
  char zDbNum[30];
  int meta[10];
  InitData initData;
  char const *zMasterSchema;
  char const *zMasterName = SCHEMA_TABLE(iDb);

  /*
  ** The master database table has a structure like this
  */
  static const char master_schema[] = 
     "CREATE TABLE sqlite_master(\n"
     "  type text,\n"
     "  name text,\n"
     "  tbl_name text,\n"
     "  rootpage integer,\n"
     "  sql text\n"
     ")"
  ;
#ifndef SQLITE_OMIT_TEMPDB
  static const char temp_master_schema[] = 
     "CREATE TEMP TABLE sqlite_temp_master(\n"
     "  type text,\n"
     "  name text,\n"
     "  tbl_name text,\n"
     "  rootpage integer,\n"
     "  sql text\n"
     ")"
  ;
#else
  #define temp_master_schema 0
#endif

  assert( iDb>=0 && iDb<db->nDb );

  /* zMasterSchema and zInitScript are set to point at the master schema
  ** and initialisation script appropriate for the database being
  ** initialised. zMasterName is the name of the master table.
  */
  if( !OMIT_TEMPDB && iDb==1 ){
    zMasterSchema = temp_master_schema;

  }else{
    zMasterSchema = master_schema;

  }
  zMasterName = SCHEMA_TABLE(iDb);

  /* Construct the schema tables.  */
  sqlite3SafetyOff(db);
  azArg[0] = zMasterName;
  azArg[1] = "1";
  azArg[2] = zMasterSchema;
  sprintf(zDbNum, "%d", iDb);

    pTab->readOnly = 1;
  }
  sqlite3SafetyOn(db);

  /* Create a cursor to hold the database open
  */
  if( db->aDb[iDb].pBt==0 ){
    if( !OMIT_TEMPDB && iDb==1 ) DbSetProperty(db, 1, DB_SchemaLoaded);
    return SQLITE_OK;
  }
  rc = sqlite3BtreeCursor(db->aDb[iDb].pBt, MASTER_ROOT, 0, 0, 0, &curMain);
  if( rc!=SQLITE_OK && rc!=SQLITE_EMPTY ){
    sqlite3SetString(pzErrMsg, sqlite3ErrStr(rc), (char*)0);
    return rc;
  }

    }
  }

  /* Once all the other databases have been initialised, load the schema
  ** for the TEMP database. This is loaded last, as the TEMP database
  ** schema may contain references to objects in other databases.
  */
#ifndef SQLITE_OMIT_TEMPDB
  if( rc==SQLITE_OK && db->nDb>1 && !DbHasProperty(db, 1, DB_SchemaLoaded) ){
    rc = sqlite3InitOne(db, 1, pzErrMsg);
    if( rc ){
      sqlite3ResetInternalSchema(db, 1);
    }
  }
#endif

  db->init.busy = 0;
  if( rc==SQLITE_OK ){
    db->flags |= SQLITE_Initialized;
    sqlite3CommitInternalChanges(db);
  }

  /* Open the backend database driver */
  rc = sqlite3BtreeFactory(db, zFilename, 0, MAX_PAGES, &db->aDb[0].pBt);
  if( rc!=SQLITE_OK ){
    sqlite3Error(db, rc, 0);
    db->magic = SQLITE_MAGIC_CLOSED;
    goto opendb_out;
  }



  /* The default safety_level for the main database is 'full'; for the temp
  ** database it is 'NONE'. This matches the pager layer defaults.  
  */
  db->aDb[0].zName = "main";
  db->aDb[0].safety_level = 3;
#ifndef SQLITE_OMIT_TEMPDB
  db->aDb[1].zName = "temp";
  db->aDb[1].safety_level = 1;
#endif


  /* Register all built-in functions, but do not attempt to read the
  ** database schema yet. This is delayed until the first time the database
  ** is accessed.
  */
  sqlite3RegisterBuiltinFunctions(db);
  sqlite3Error(db, SQLITE_OK, 0);

**
*************************************************************************
** 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.170 2005/03/29 03:10:59 danielk1977 Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  if( pParse->zErrMsg==0 ){

///////////////////// The CREATE TABLE statement ////////////////////////////
//
cmd ::= create_table create_table_args.
create_table ::= CREATE(X) temp(T) TABLE nm(Y) dbnm(Z). {
   sqlite3StartTable(pParse,&X,&Y,&Z,T,0);
}
%type temp {int}
%ifndef SQLITE_OMIT_TEMPDB
temp(A) ::= TEMP.  {A = 1;}
%endif
temp(A) ::= .      {A = 0;}
create_table_args ::= LP columnlist conslist_opt(X) RP(Y). {
  sqlite3EndTable(pParse,&X,&Y,0);
}
create_table_args ::= AS select(S). {
  sqlite3EndTable(pParse,0,0,S);
  sqlite3SelectDelete(S);

/*
** 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 PRAGMA command.
**
** $Id: pragma.c,v 1.91 2005/03/29 03:10:59 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/* Ignore this whole file if pragmas are disabled
*/

    sqlite3VdbeSetColName(v, 0, "integrity_check", P3_STATIC);
    sqlite3VdbeAddOpList(v, ArraySize(initCode), initCode);

    /* Do an integrity check on each database file */
    for(i=0; i<db->nDb; i++){
      HashElem *x;
      int cnt = 0;

      if( OMIT_TEMPDB && i==1 ) continue;

      sqlite3CodeVerifySchema(pParse, i);

      /* Do an integrity check of the B-Tree
      */
      for(x=sqliteHashFirst(&db->aDb[i].tblHash); x; x=sqliteHashNext(x)){
        Table *pTab = sqliteHashData(x);

/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.375 2005/03/29 03:10:59 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** These #defines should enable >2GB file support on Posix if the
** underlying operating system supports it.  If the OS lacks

#endif
#ifdef SQLITE_DEFAULT_TEMP_CACHE_SIZE
# define TEMP_PAGES SQLITE_DEFAULT_TEMP_CACHE_SIZE
#else
# define TEMP_PAGES   500
#endif

/*
** OMIT_TEMPDB is set to 1 if SQLITE_OMIT_TEMPDB is defined, or 0
** afterward. Having this macro allows us to cause the C compiler 
** to omit code used by TEMP tables without messy #ifndef statements.
*/
#ifdef SQLITE_OMIT_TEMPDB
#define OMIT_TEMPDB 1
#else
#define OMIT_TEMPDB 0
#endif

/*
** If the following macro is set to 1, then NULL values are considered
** distinct for the SELECT DISTINCT statement and for UNION or EXCEPT
** compound queries.  No other SQL database engine (among those tested) 
** works this way except for OCELOT.  But the SQL92 spec implies that
** this is how things should work.
**

** The root-page of the master database table.
*/
#define MASTER_ROOT       1

/*
** The name of the schema table.
*/
#define SCHEMA_TABLE(x)  ((!OMIT_TEMPDB)&&(x==1)?TEMP_MASTER_NAME:MASTER_NAME)

/*
** A convenience macro that returns the number of elements in
** an array.
*/
#define ArraySize(X)    (sizeof(X)/sizeof(X[0]))

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

#endif

#ifdef SQLITE_OMIT_TRIGGER
  Tcl_SetVar2(interp, "sqlite_options", "trigger", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "trigger", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_TEMPDB
  Tcl_SetVar2(interp, "sqlite_options", "tempdb", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "tempdb", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_UTF16
  Tcl_SetVar2(interp, "sqlite_options", "utf16", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "utf16", "1", TCL_GLOBAL_ONLY);
#endif

  TriggerStep *pStepList, /* The triggered program */
  Token *pAll             /* Token that describes the complete CREATE TRIGGER */
){
  Trigger *pTrig = 0;     /* The trigger whose construction is finishing up */
  sqlite3 *db = pParse->db;  /* The database */
  DbFixer sFix;


  pTrig = pParse->pNewTrigger;
  pParse->pNewTrigger = 0;
  if( pParse->nErr || pTrig==0 ) goto triggerfinish_cleanup;
  pTrig->step_list = pStepList;
  while( pStepList ){
    pStepList->pTrig = pTrig;
    pStepList = pStepList->pNext;
  }
  if( sqlite3FixInit(&sFix, pParse, pTrig->iDb, "trigger", &pTrig->nameToken) 
          && sqlite3FixTriggerStep(&sFix, pTrig->step_list) ){

    goto drop_trigger_cleanup;
  }

  assert( pName->nSrc==1 );
  zDb = pName->a[0].zDatabase;
  zName = pName->a[0].zName;
  nName = strlen(zName);
  for(i=OMIT_TEMPDB; i<db->nDb; i++){
    int j = (i<2) ? i^1 : i;  /* Search TEMP before MAIN */
    if( zDb && sqlite3StrICmp(db->aDb[j].zName, zDb) ) continue;
    pTrigger = sqlite3HashFind(&(db->aDb[j].trigHash), zName, nName+1);
    if( pTrigger ) break;
  }
  if( !pTrigger ){
    sqlite3ErrorMsg(pParse, "no such trigger: %S", pName, 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.461 2005/03/29 03:11:00 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*

  char *zSql;
  int iDb = pOp->p1;
  const char *zMaster;
  InitData initData;

  assert( iDb>=0 && iDb<db->nDb );
  if( !DbHasProperty(db, iDb, DB_SchemaLoaded) ) break;
  zMaster = SCHEMA_TABLE(iDb);
  initData.db = db;
  initData.pzErrMsg = &p->zErrMsg;
  zSql = sqlite3MPrintf(
     "SELECT name, rootpage, sql, %d FROM '%q'.%s WHERE %s",
     pOp->p1, db->aDb[iDb].zName, zMaster, pOp->p3);
  if( zSql==0 ) goto no_mem;
  sqlite3SafetyOff(db);

# 2004 November 10
#
# 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 script is testing the ALTER TABLE statement.
#
# $Id: alter.test,v 1.11 2005/03/29 03:11:00 danielk1977 Exp $
#

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

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {

# alter-4.*: Test ALTER TABLE on tables that have AUTOINCREMENT fields.
#

# Create some tables to rename.  Be sure to include some TEMP tables
# and some tables with odd names.
#
do_test alter-1.1 {
  ifcapable tempdb {
    set ::temp TEMP
  } else {
    set ::temp {}
  }
  execsql [subst -nocommands {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,2);
    CREATE TABLE [t1'x1](c UNIQUE, b PRIMARY KEY);
    INSERT INTO [t1'x1] VALUES(3,4);
    CREATE INDEX t1i1 ON T1(B);
    CREATE INDEX t1i2 ON t1(a,b);
    CREATE INDEX i3 ON [t1'x1](b,c);
    CREATE $::temp TABLE "temp table"(e,f,g UNIQUE);
    CREATE INDEX i2 ON [temp table](f);
    INSERT INTO [temp table] VALUES(5,6,7);
  }]
  execsql {
    SELECT 't1', * FROM t1;
    SELECT 't1''x1', * FROM "t1'x1";
    SELECT * FROM [temp table];
  }
} {t1 1 2 t1'x1 3 4 5 6 7}
do_test alter-1.2 {
  execsql [subst {
    CREATE $::temp TABLE objlist(type, name, tbl_name);
    INSERT INTO objlist SELECT type, name, tbl_name 
        FROM sqlite_master WHERE NAME!='objlist';
  }]
  ifcapable tempdb {
    execsql {
      INSERT INTO objlist SELECT type, name, tbl_name 
          FROM sqlite_temp_master WHERE NAME!='objlist';
    }
  }

  execsql {
    SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;
  }
} [list \
     table t1                              t1             \
     index t1i1                            t1             \
     index t1i2                            t1             \
     table t1'x1                           t1'x1          \

    SELECT 't2', * FROM t2;
    SELECT * FROM temptab;
  }
} {t1 1 2 t2 3 4 5 6 7}
do_test alter-1.5 {
  execsql {
    DELETE FROM objlist;
    INSERT INTO objlist SELECT type, name, tbl_name
        FROM sqlite_master WHERE NAME!='objlist';
  }
  catchsql {
    INSERT INTO objlist SELECT type, name, tbl_name 
        FROM sqlite_temp_master WHERE NAME!='objlist';
  }
  execsql {
    SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;
  }
} [list \
     table -t1-                         -t1-        \
     index t1i1                         -t1-        \
     index t1i2                         -t1-        \
     table T2                           T2          \
     index i3                           T2          \
     index {sqlite_autoindex_T2_1}      T2          \
     index {sqlite_autoindex_T2_2}      T2          \
     table {TempTab}                    {TempTab}   \
     index i2                           {TempTab}   \
     index {sqlite_autoindex_TempTab_1} {TempTab}   \
  ]

# Make sure the changes persist after restarting the database.
# (The TEMP table will not persist, of course.)
#
ifcapable tempdb {
  do_test alter-1.6 {
    db close
    set DB [sqlite3 db test.db]
    execsql {
      CREATE TEMP TABLE objlist(type, name, tbl_name);
      INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master;
      INSERT INTO objlist 
          SELECT type, name, tbl_name FROM sqlite_temp_master 
          WHERE NAME!='objlist';
      SELECT type, name, tbl_name FROM objlist 
          ORDER BY tbl_name, type desc, name;
    }
  } [list \
       table -t1-                         -t1-           \
       index t1i1                         -t1-           \
       index t1i2                         -t1-           \
       table T2                           T2          \
       index i3                           T2          \
       index {sqlite_autoindex_T2_1}      T2          \
       index {sqlite_autoindex_T2_2}      T2          \
    ]
} else {
  execsql {
    DROP TABLE TempTab;
  }
}

# Make sure the ALTER TABLE statements work with the
# non-callback API
#
do_test alter-1.7 {
  stepsql $DB {
    ALTER TABLE [-t1-] RENAME to [*t1*];
    ALTER TABLE T2 RENAME TO [<t2>];
  }
  execsql {
    DELETE FROM objlist;
    INSERT INTO objlist SELECT type, name, tbl_name
        FROM sqlite_master WHERE NAME!='objlist';
  }
  catchsql {
    INSERT INTO objlist SELECT type, name, tbl_name 

        FROM sqlite_temp_master WHERE NAME!='objlist';
  }
  execsql {
    SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name;

  }
} [list \
     table *t1*                         *t1*           \
     index t1i1                         *t1*           \
     index t1i2                         *t1*           \
     table <t2>                         <t2>          \
     index i3                           <t2>          \

do_test alter-3.2.10 {
  execsql {
    DROP TABLE t10;
  }
} {}

do_test alter-3.3.1 {
  execsql [subst {
    CREATE TABLE tbl1(a, b, c);
    CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN
      SELECT trigfunc('trig1', new.a, new.b, new.c);
    END;
  }]
} {}
do_test alter-3.3.2 {
  execsql {
    INSERT INTO tbl1 VALUES('a', 'b', 'c');
  }
  set ::TRIGGER
} {trig1 a b c}
do_test alter-3.3.3 {
  execsql {
    ALTER TABLE tbl1 RENAME TO tbl2;
    INSERT INTO tbl2 VALUES('d', 'e', 'f');
  } 
  set ::TRIGGER
} {trig1 d e f}
do_test alter-3.3.4 {
  execsql [subst {
    CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN
      SELECT trigfunc('trig2', new.a, new.b, new.c);
    END;
  }] 
} {}
do_test alter-3.3.5 {
  execsql {
    ALTER TABLE tbl2 RENAME TO tbl3;
    INSERT INTO tbl3 VALUES('g', 'h', 'i');
  } 
  set ::TRIGGER
} {trig1 g h i}
do_test alter-3.3.6 {
  execsql {
    UPDATE tbl3 SET a = 'G' where a = 'g';
  } 
  set ::TRIGGER
} {trig2 G h i}
do_test alter-3.3.7 {
  execsql {
    DROP TABLE tbl3;
  }
} {}
ifcapable tempdb {
  do_test alter-3.3.8 {
    execsql {
      SELECT * FROM sqlite_temp_master WHERE type = 'trigger';
    }
  } {}
}

} ;# ifcapable trigger

# If the build does not include AUTOINCREMENT fields, omit alter-4.*.
ifcapable autoinc {

do_test alter-4.1 {

#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing that SQLite can handle a subtle 
# file format change that may be used in the future to implement
# "ALTER TABLE ... ADD COLUMN".
#
# $Id: alter3.test,v 1.6 2005/03/29 03:11:00 danielk1977 Exp $
#

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.

  }
} {}

#----------------------------------------------------------------
# Test that the table schema is correctly reloaded when a column
# is added to a table.
#
ifcapable trigger&&tempdb {
  do_test alter3-6.1 {
    execsql {
      CREATE TABLE t1(a, b);
      CREATE TABLE log(trig, a, b);

      CREATE TRIGGER t1_a AFTER INSERT ON t1 BEGIN
        INSERT INTO log VALUES('a', new.a, new.b);

#    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: attach.test,v 1.40 2005/03/29 03:11:00 danielk1977 Exp $
#

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

for {set i 2} {$i<=15} {incr i} {
  file delete -force test$i.db

  }
} {1 {database db9 is already in use}}
do_test attach-1.15 {
  catchsql {
    ATTACH 'test.db' as main;
  }
} {1 {database main is already in use}}
ifcapable tempdb {
  do_test attach-1.16 {
    catchsql {
      ATTACH 'test.db' as temp;
    }
  } {1 {database temp is already in use}}
}
do_test attach-1.17 {
  catchsql {
    ATTACH 'test.db' as MAIN;
  }
} {1 {database MAIN is already in use}}
do_test attach-1.18 {
  catchsql {

} {}
ifcapable schema_pragmas {
do_test attach-1.20.2 {
  db_list db
} {0 main 2 db2 3 db3 4 db4 5 db6 6 db7 7 db8 8 db9 9 db10 10 db11}
} ;# ifcapable schema_pragmas
integrity_check attach-1.20.3
ifcapable tempdb {
  execsql {select * from sqlite_temp_master}
}
do_test attach-1.21 {
  catchsql {
    ATTACH 'test.db' as db12;
  }
} {0 {}}
do_test attach-1.22 {
  catchsql {

  }
} {1 {no such database: db12}}
do_test attach-1.26 {
  catchsql {
    DETACH main;
  }
} {1 {cannot detach database main}}

ifcapable tempdb {
  do_test attach-1.27 {
    catchsql {
      DETACH Temp;
    }
  } {1 {cannot detach database Temp}}
} else {
  do_test attach-1.27 {
    catchsql {
      DETACH Temp;
    }
  } {1 {no such database: Temp}}
}

do_test attach-1.28 {
  catchsql {
    DETACH db11;
    DETACH db10;
    DETACH db9;
    DETACH db8;
    DETACH db7;
    DETACH db6;
    DETACH db4;
    DETACH db3;
    DETACH db2;
  }
} {0 {}}
ifcapable schema_pragmas {
  ifcapable tempdb {
    do_test attach-1.29 {
      db_list db
    } {0 main 1 temp}
  } else {
    do_test attach-1.29 {
      db_list db
    } {0 main}
  }
} ;# ifcapable schema_pragmas

ifcapable {trigger} {  # Only do the following tests if triggers are enabled
do_test attach-2.1 {
  execsql {
    CREATE TABLE tx(x1,x2,y1,y2);
    CREATE TRIGGER r1 AFTER UPDATE ON t2 FOR EACH ROW BEGIN

  }
} {}
do_test attach-2.7 {
  execsql {
    SELECT type, name, tbl_name FROM db2.sqlite_master;
  }
} {table t2 t2 table tx tx trigger r1 t2}

ifcapable schema_pragmas&&tempdb {
  do_test attach-2.8 {
    db_list db
  } {0 main 1 temp 2 db2}
} ;# ifcapable schema_pragmas&&tempdb
ifcapable schema_pragmas&&!tempdb {
  do_test attach-2.8 {
    db_list db
  } {0 main 2 db2}
} ;# ifcapable schema_pragmas&&!tempdb

do_test attach-2.9 {
  execsql {
    CREATE INDEX i2 ON t2(x);
    SELECT * FROM t2 WHERE x>5;
  } db2
} {21 x 22 y}
do_test attach-2.10 {
  execsql {
    SELECT type, name, tbl_name FROM sqlite_master;
  } db2
} {table t2 t2 table tx tx trigger r1 t2 index i2 t2}
#do_test attach-2.11 {
#  catchsql { 
#    SELECT * FROM t2 WHERE x>5;
#  }
#} {1 {database schema has changed}}
ifcapable schema_pragmas {
  ifcapable tempdb {
    do_test attach-2.12 {
      db_list db
    } {0 main 1 temp 2 db2}
  } else {
    do_test attach-2.12 {
      db_list db
    } {0 main 2 db2}
  }
} ;# ifcapable schema_pragmas
do_test attach-2.13 {
  catchsql {
    SELECT * FROM t2 WHERE x>5;
  }
} {0 {21 x 22 y}}
do_test attach-2.14 {

  catchsql {
    DROP TRIGGER r5;
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      SELECT 'no-op' FROM orig.t1;
    END;
  } db2
} {1 {trigger r5 cannot reference objects in database orig}}
ifcapable tempdb {
  do_test attach-5.4 {
    catchsql {
      CREATE TEMP TABLE t6(p,q,r);
      CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
        SELECT 'no-op' FROM temp.t6;
      END;
    } db2
  } {1 {trigger r5 cannot reference objects in database temp}}
}
ifcapable subquery {
  do_test attach-5.5 {
    catchsql {
      CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
        SELECT 'no-op' || (SELECT * FROM temp.t6);
      END;
    } db2

#    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: attach2.test,v 1.32 2005/03/29 03:11:00 danielk1977 Exp $
#

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


# Ticket #354

for {set i 2} {$i<=15} {incr i} {
  catch {db$i close}
}

# A procedure to verify the status of locks on a database.
#
proc lock_status {testnum db expected_result} {
  # If the database was compiled with OMIT_TEMPDB set, then 
  # the lock_status list will not contain an entry for the temp
  # db. But the test code doesn't know this, so it's easiest 
  # to filter it out here.
  ifcapable !tempdb {
    set expected_result [concat \
        [lrange $expected_result 0 1] \
        [lrange $expected_result 4 end] \
    ]
  }
  do_test attach2-$testnum [subst {
    $db cache flush  ;# The lock_status pragma should not be cached
    execsql {PRAGMA lock_status} $db
  }] $expected_result
}
set sqlite_os_trace 0

#    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 schema changes to attached databases.
#
# $Id: attach3.test,v 1.15 2005/03/29 03:11:00 danielk1977 Exp $
#


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

# Create tables t1 and t2 in the main database

} {}
do_test attach3-8.2 {
  execsql {
    SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
  }
} {}

ifcapable tempdb {
  # Try to trick SQLite into dropping the wrong temp trigger.
  do_test attach3-9.0 {
    execsql {
      CREATE TABLE main.t4(a, b, c);
      CREATE TABLE aux.t4(a, b, c);
      CREATE TEMP TRIGGER tst_trigger BEFORE INSERT ON aux.t4 BEGIN 
        SELECT 'hello world';
      END;
      SELECT count(*) FROM sqlite_temp_master;
    }
  } {1}
  do_test attach3-9.1 {
    execsql {
      DROP TABLE main.t4;
      SELECT count(*) FROM sqlite_temp_master;
    }
  } {1}
  do_test attach3-9.2 {
    execsql {
      DROP TABLE aux.t4;
      SELECT count(*) FROM sqlite_temp_master;
    }
  } {0}
}
} ;# endif trigger

# Make sure the aux.sqlite_master table is read-only
do_test attach3-10.0 {
  catchsql {
    INSERT INTO aux.sqlite_master VALUES(1, 2, 3, 4, 5);
  }

#    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.27 2005/03/29 03:11:00 danielk1977 Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.

do_test auth-1.3.3 {
  set ::authargs
} {t1 {} main {}}
do_test auth-1.4 {
  execsql {SELECT name FROM sqlite_master}
} {}

ifcapable tempdb {
  do_test auth-1.5 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {CREATE TEMP TABLE t1(a,b,c)}
  } {1 {not authorized}}
  do_test auth-1.6 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {}
  do_test auth-1.7.1 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_CREATE_TEMP_TABLE"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {CREATE TEMP TABLE t1(a,b,c)}
  } {1 {not authorized}}
  do_test auth-1.7.2 {
     set ::authargs
  } {t1 {} temp {}}
  do_test auth-1.8 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {}
}

do_test auth-1.9 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_INSERT" && $arg1=="sqlite_master"} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK

    return SQLITE_OK
  }
  catchsql {CREATE TABLE t1(a,b,c)}
} {0 {}}
do_test auth-1.12 {
  execsql {SELECT name FROM sqlite_master}
} {}

ifcapable tempdb {
  do_test auth-1.13 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {CREATE TEMP TABLE t1(a,b,c)}
  } {0 {}}
  do_test auth-1.14 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {}
  do_test auth-1.15 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_CREATE_TEMP_TABLE"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {CREATE TEMP TABLE t1(a,b,c)}
  } {0 {}}
  do_test auth-1.16 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {}
  
  do_test auth-1.17 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_CREATE_TABLE"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {CREATE TEMP TABLE t1(a,b,c)}
  } {0 {}}
  do_test auth-1.18 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
}

do_test auth-1.19.1 {
  set ::authargs {}
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_CREATE_TEMP_TABLE"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      return SQLITE_DENY
    }

do_test auth-1.23.2 {
  set ::authargs
} {t2 {} main {}}
do_test auth-1.24 {
  execsql {SELECT name FROM sqlite_master}
} {t2}

ifcapable tempdb {
  do_test auth-1.25 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DROP_TEMP_TABLE"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {DROP TABLE t1}
  } {1 {not authorized}}
  do_test auth-1.26 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
  do_test auth-1.27 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DROP_TEMP_TABLE"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {DROP TABLE t1}
  } {0 {}}
  do_test auth-1.28 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
}

do_test auth-1.29 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_INSERT" && $arg1=="t2"} {
      return SQLITE_DENY
    }
    return SQLITE_OK

    return SQLITE_OK
  }
  catchsql {DROP TABLE t2}
} {1 {not authorized}}
do_test auth-1.66 {
  execsql {SELECT name FROM sqlite_master}
} {t2}

ifcapable tempdb {
  do_test auth-1.67 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
         return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {DROP TABLE t1}
  } {1 {not authorized}}
  do_test auth-1.68 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
  do_test auth-1.69 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DELETE" && $arg1=="t1"} {
         return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {DROP TABLE t1}
  } {1 {not authorized}}
  do_test auth-1.70 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
}

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

    return SQLITE_OK
  }
  catchsql {DROP TABLE t2}
} {0 {}}
do_test auth-1.74 {
  execsql {SELECT name FROM sqlite_master}
} {t2}

ifcapable tempdb {
  do_test auth-1.75 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
         return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {DROP TABLE t1}
  } {0 {}}
  do_test auth-1.76 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
  do_test auth-1.77 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DELETE" && $arg1=="t1"} {
         return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {DROP TABLE t1}
  } {0 {}}
  do_test auth-1.78 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
}

# Test cases auth-1.79 to auth-1.124 test creating and dropping views.
# Omit these if the library was compiled with views omitted.
ifcapable view {
do_test auth-1.79 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_CREATE_VIEW"} {

do_test auth-1.83 {
  set ::authargs
} {v1 {} main {}}
do_test auth-1.84 {
  execsql {SELECT name FROM sqlite_master}
} {t2}

ifcapable tempdb {
  do_test auth-1.85 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_CREATE_TEMP_VIEW"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4] 
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
  } {1 {not authorized}}
  do_test auth-1.86 {
    set ::authargs
  } {v1 {} temp {}}
  do_test auth-1.87 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
  do_test auth-1.88 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_CREATE_TEMP_VIEW"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4] 
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
  } {0 {}}
  do_test auth-1.89 {
    set ::authargs
  } {v1 {} temp {}}
  do_test auth-1.90 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
}

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

  }
  catchsql {CREATE VIEW v1 AS SELECT a+1,b+1 FROM t2}
} {0 {}}
do_test auth-1.94 {
  execsql {SELECT name FROM sqlite_master}
} {t2}

ifcapable tempdb {
  do_test auth-1.95 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
  } {1 {not authorized}}
  do_test auth-1.96 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
  do_test auth-1.97 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {CREATE TEMPORARY VIEW v1 AS SELECT a+1,b+1 FROM t2}
  } {0 {}}
  do_test auth-1.98 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
}

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

  set ::authargs
} {v2 {} main {}}
do_test auth-1.111 {
  execsql {SELECT name FROM sqlite_master}
} {t2}


ifcapable tempdb {
  do_test auth-1.112 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {
      CREATE TEMP VIEW v1 AS SELECT a+1,b+1 FROM t1;
      DROP VIEW v1
    }
  } {1 {not authorized}}
  do_test auth-1.113 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1 v1}
  do_test auth-1.114 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DROP_TEMP_VIEW"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {DROP VIEW v1}
  } {1 {not authorized}}
  do_test auth-1.115 {
    set ::authargs
  } {v1 {} temp {}}
  do_test auth-1.116 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1 v1}
  do_test auth-1.117 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {DROP VIEW v1}
  } {0 {}}
  do_test auth-1.118 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1 v1}
  do_test auth-1.119 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DROP_TEMP_VIEW"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {DROP VIEW v1}
  } {0 {}}
  do_test auth-1.120 {
    set ::authargs
  } {v1 {} temp {}}
  do_test auth-1.121 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1 v1}
  do_test auth-1.122 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DROP_TEMP_VIEW"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_OK
      }
      return SQLITE_OK
    }
    catchsql {DROP VIEW v1}
  } {0 {}}
  do_test auth-1.123 {
    set ::authargs
  } {v1 {} temp {}}
  do_test auth-1.124 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
}
} ;# ifcapable view

# Test cases auth-1.125 to auth-1.176 test creating and dropping triggers.
# Omit these if the library was compiled with triggers omitted.
#
ifcapable trigger&&tempdb {
do_test auth-1.125 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_CREATE_TRIGGER"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      return SQLITE_DENY
    }
    return SQLITE_OK

do_test auth-1.188 {
  set ::authargs
} {i2 t2 main {}}
do_test auth-1.189 {
  execsql {SELECT name FROM sqlite_master}
} {t2 i2}

ifcapable tempdb {
  do_test auth-1.190 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_CREATE_TEMP_INDEX"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {CREATE INDEX i1 ON t1(a)}
  } {1 {not authorized}}
  do_test auth-1.191 {
    set ::authargs
  } {i1 t1 temp {}}
  do_test auth-1.192 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
  do_test auth-1.193 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {CREATE INDEX i1 ON t1(b)}
  } {1 {not authorized}}
  do_test auth-1.194 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
  do_test auth-1.195 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_CREATE_TEMP_INDEX"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {CREATE INDEX i1 ON t1(b)}
  } {0 {}}
  do_test auth-1.196 {
    set ::authargs
  } {i1 t1 temp {}}
  do_test auth-1.197 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
  do_test auth-1.198 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_INSERT" && $arg1=="sqlite_temp_master"} {
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {CREATE INDEX i1 ON t1(c)}
  } {0 {}}
  do_test auth-1.199 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
  do_test auth-1.200 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_CREATE_TEMP_INDEX"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_OK
      }
      return SQLITE_OK
    }
    catchsql {CREATE INDEX i1 ON t1(a)}
  } {0 {}}
  do_test auth-1.201 {
    set ::authargs
  } {i1 t1 temp {}}
  do_test auth-1.202 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1 i1}
}

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

do_test auth-1.214 {
  set ::authargs
} {i2 t2 main {}}
do_test auth-1.215 {
  execsql {SELECT name FROM sqlite_master}
} {t2}

ifcapable tempdb {
  do_test auth-1.216 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {DROP INDEX i1}
  } {1 {not authorized}}
  do_test auth-1.217 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1 i1}
  do_test auth-1.218 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DROP_TEMP_INDEX"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {DROP INDEX i1}
  } {1 {not authorized}}
  do_test auth-1.219 {
    set ::authargs
  } {i1 t1 temp {}}
  do_test auth-1.220 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1 i1}
  do_test auth-1.221 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DELETE" && $arg1=="sqlite_temp_master"} {
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {DROP INDEX i1}
  } {0 {}}
  do_test auth-1.222 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1 i1}
  do_test auth-1.223 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DROP_TEMP_INDEX"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_IGNORE
      }
      return SQLITE_OK
    }
    catchsql {DROP INDEX i1}
  } {0 {}}
  do_test auth-1.224 {
    set ::authargs
  } {i1 t1 temp {}}
  do_test auth-1.225 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1 i1}
  do_test auth-1.226 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DROP_TEMP_INDEX"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_OK
      }
      return SQLITE_OK
    }
    catchsql {DROP INDEX i1}
  } {0 {}}
  do_test auth-1.227 {
    set ::authargs
  } {i1 t1 temp {}}
  do_test auth-1.228 {
    execsql {SELECT name FROM sqlite_temp_master}
  } {t1}
}

do_test auth-1.229 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_PRAGMA"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]
      return SQLITE_DENY
    }

    }
    return SQLITE_OK
  }
  catchsql {
    DETACH DATABASE test1;
  }
} {0 {}}
ifcapable tempdb {
  ifcapable schema_pragmas {
  do_test auth-1.260 {
    lindex [execsql {PRAGMA database_list}] 7
  } {test1}
  } ;# ifcapable schema_pragmas
  do_test auth-1.261 {
    proc auth {code arg1 arg2 arg3 arg4} {
      if {$code=="SQLITE_DETACH"} {
        set ::authargs [list $arg1 $arg2 $arg3 $arg4]
        return SQLITE_DENY
      }
      return SQLITE_OK
    }
    catchsql {
      DETACH DATABASE test1;
    }
  } {1 {not authorized}}
  ifcapable schema_pragmas {
  do_test auth-1.262 {
    lindex [execsql {PRAGMA database_list}] 7
  } {test1}
  } ;# ifcapable schema_pragmas
  db authorizer {}
  execsql {DETACH DATABASE test1}
  db authorizer ::auth
  
  # Authorization for ALTER TABLE. These tests are omitted if the library
  # was built without ALTER TABLE support.
  ifcapable altertable {
  
    do_test auth-1.263 {
      proc auth {code arg1 arg2 arg3 arg4} {
        if {$code=="SQLITE_ALTER_TABLE"} {
          set ::authargs [list $arg1 $arg2 $arg3 $arg4]
          return SQLITE_OK
        }
        return SQLITE_OK
      }
      catchsql {
        ALTER TABLE t1 RENAME TO t1x
      }
    } {0 {}}
    do_test auth-1.264 {
      execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
    } {t1x}
    do_test auth-1.265 {
      set authargs
    } {temp t1 {} {}}
    do_test auth-1.266 {
      proc auth {code arg1 arg2 arg3 arg4} {
        if {$code=="SQLITE_ALTER_TABLE"} {
          set ::authargs [list $arg1 $arg2 $arg3 $arg4]
          return SQLITE_IGNORE
        }
        return SQLITE_OK
      }
      catchsql {
        ALTER TABLE t1x RENAME TO t1
      }
    } {0 {}}
    do_test auth-1.267 {
      execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
    } {t1x}
    do_test auth-1.268 {
      set authargs
    } {temp t1x {} {}}
    do_test auth-1.269 {
      proc auth {code arg1 arg2 arg3 arg4} {
        if {$code=="SQLITE_ALTER_TABLE"} {
          set ::authargs [list $arg1 $arg2 $arg3 $arg4]
          return SQLITE_DENY
        }
        return SQLITE_OK
      }
      catchsql {
        ALTER TABLE t1x RENAME TO t1
      }
    } {1 {not authorized}}
    do_test auth-1.270 {
      execsql {SELECT name FROM sqlite_temp_master WHERE type='table'}
    } {t1x}
  } ;# ifcapable altertable

  do_test auth-1.271 {
    set authargs
  } {temp t1x {} {}}
} else {
  db authorizer {}
  db eval {
    DETACH DATABASE test1;
  }
}

ifcapable  altertable {
db authorizer {}
catchsql {ALTER TABLE t1x RENAME TO t1}
db authorizer ::auth
do_test auth-1.272 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_ALTER_TABLE"} {
      set ::authargs [list $arg1 $arg2 $arg3 $arg4]

  set ::authargs
} {t3_idx2 {} main {} t3_idx1 {} main {} sqlite_autoindex_t3_1 {} main {}}
do_test auth-1.286 {
  execsql {
    DROP TABLE t3;
  }
} {}
ifcapable tempdb {
  do_test auth-1.287 {
    execsql {
      CREATE TEMP TABLE t3(a PRIMARY KEY, b, c);
      CREATE INDEX t3_idx1 ON t3(c COLLATE BINARY);
      CREATE INDEX t3_idx2 ON t3(b COLLATE NOCASE);
    }
  } {}
  do_test auth-1.288 {
    set ::authargs {}
    execsql {
      REINDEX temp.t3_idx1;
    }
    set ::authargs
  } {t3_idx1 {} temp {}}
  do_test auth-1.289 {
    set ::authargs {}
    execsql {
      REINDEX BINARY;
    }
    set ::authargs
  } {t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}}
  do_test auth-1.290 {
    set ::authargs {}
    execsql {
      REINDEX NOCASE;
    }
    set ::authargs
  } {t3_idx2 {} temp {}}
  do_test auth-1.291 {
    set ::authargs {}
    execsql {
      REINDEX temp.t3;
    }
    set ::authargs
  } {t3_idx2 {} temp {} t3_idx1 {} temp {} sqlite_autoindex_t3_1 {} temp {}}
  proc auth {code args} {
    if {$code=="SQLITE_REINDEX"} {
      set ::authargs [concat $::authargs $args]
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  do_test auth-1.292 {
    set ::authargs {}
    catchsql {
      REINDEX temp.t3;
    }
  } {1 {not authorized}}
  do_test auth-1.293 {
    execsql {
      DROP TABLE t3;
    }
  } {}
}


} ;# ifcapable reindex 

do_test auth-2.1 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {
      return SQLITE_DENY
    }
    return SQLITE_OK

# 2004 November 12
#
# 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 script is testing the AUTOINCREMENT features.
#
# $Id: autoinc.test,v 1.5 2005/03/29 03:11:00 danielk1977 Exp $
#

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

# If the library is not compiled with autoincrement support then
# skip all tests in this file.

  }
} {235 1 1235 2 1240 3 1241 4}
do_test autoinc-2.51 {
  execsql {
    SELECT * FROM sqlite_sequence
  }
} {t1 1241}

ifcapable tempdb {
  do_test autoinc-2.52 {
    execsql {
      CREATE TEMP TABLE t2 AS SELECT y FROM t1;
      INSERT INTO t1 SELECT NULL, y+4 FROM t2;
      SELECT * FROM t1;
    }
  } {235 1 1235 2 1240 3 1241 4 1242 5 1243 6 1244 7 1245 8}
  do_test autoinc-2.53 {
    execsql {
      SELECT * FROM sqlite_sequence
    }
  } {t1 1245}
  do_test autoinc-2.54 {
    execsql {
      DELETE FROM t1;
      INSERT INTO t1 SELECT NULL, y FROM t2;
      SELECT * FROM t1;
    }
  } {1246 1 1247 2 1248 3 1249 4}
  do_test autoinc-2.55 {
    execsql {
      SELECT * FROM sqlite_sequence
    }
  } {t1 1249}
}

# Create multiple AUTOINCREMENT tables.  Make sure all sequences are
# tracked separately and do not interfere with one another.
#
do_test autoinc-2.70 {
  catchsql {
    DROP TABLE t2;
  }
  execsql {
    CREATE TABLE t2(d, e INTEGER PRIMARY KEY AUTOINCREMENT, f);
    INSERT INTO t2(d) VALUES(1);
    SELECT * FROM sqlite_sequence;
  }
} [ifcapable tempdb {list t1 1249 t2 1} else {list t1 1241 t2 1}]
do_test autoinc-2.71 {
  execsql {
    INSERT INTO t2(d) VALUES(2);
    SELECT * FROM sqlite_sequence;
  }
} [ifcapable tempdb {list t1 1249 t2 2} else {list t1 1241 t2 2}]
do_test autoinc-2.72 {
  execsql {
    INSERT INTO t1(x) VALUES(10000);
    SELECT * FROM sqlite_sequence;
  }
} {t1 10000 t2 2}
do_test autoinc-2.73 {

    DROP TABLE t2;
    SELECT name FROM sqlite_sequence;
  }
} {}

# AUTOINCREMENT on TEMP tables.
#
ifcapable tempdb {
  do_test autoinc-4.1 {
    execsql {
      SELECT 1, name FROM sqlite_master WHERE type='table';
      SELECT 2, name FROM sqlite_temp_master WHERE type='table';
    }
  } {1 sqlite_sequence}
  do_test autoinc-4.2 {
    execsql {
      CREATE TABLE t1(x INTEGER PRIMARY KEY AUTOINCREMENT, y);
      CREATE TEMP TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
      SELECT 1, name FROM sqlite_master WHERE type='table';
      SELECT 2, name FROM sqlite_temp_master WHERE type='table';
    }
  } {1 sqlite_sequence 1 t1 2 t3 2 sqlite_sequence}
  do_test autoinc-4.3 {
    execsql {
      SELECT 1, * FROM main.sqlite_sequence;
      SELECT 2, * FROM temp.sqlite_sequence;
    }
  } {}
  do_test autoinc-4.4 {
    execsql {
      INSERT INTO t1 VALUES(10,1);
      INSERT INTO t3 VALUES(20,2);
      INSERT INTO t1 VALUES(NULL,3);
      INSERT INTO t3 VALUES(NULL,4);
    }
  } {}
  
  ifcapable compound {
  do_test autoinc-4.4.1 {
    execsql {
      SELECT * FROM t1 UNION ALL SELECT * FROM t3;
    }
  } {10 1 11 3 20 2 21 4}
  } ;# ifcapable compound
  
  do_test autoinc-4.5 {
    execsql {
      SELECT 1, * FROM main.sqlite_sequence;
      SELECT 2, * FROM temp.sqlite_sequence;
    }
  } {1 t1 11 2 t3 21}
  do_test autoinc-4.6 {
    execsql {
      INSERT INTO t1 SELECT * FROM t3;
      SELECT 1, * FROM main.sqlite_sequence;
      SELECT 2, * FROM temp.sqlite_sequence;
    }
  } {1 t1 21 2 t3 21}
  do_test autoinc-4.7 {
    execsql {
      INSERT INTO t3 SELECT x+100, y  FROM t1;
      SELECT 1, * FROM main.sqlite_sequence;
      SELECT 2, * FROM temp.sqlite_sequence;
    }
  } {1 t1 21 2 t3 121}
  do_test autoinc-4.8 {
    execsql {
      DROP TABLE t3;
      SELECT 1, * FROM main.sqlite_sequence;
      SELECT 2, * FROM temp.sqlite_sequence;
    }
  } {1 t1 21}
  do_test autoinc-4.9 {
    execsql {
      CREATE TEMP TABLE t2(p INTEGER PRIMARY KEY AUTOINCREMENT, q);
      INSERT INTO t2 SELECT * FROM t1;
      DROP TABLE t1;
      SELECT 1, * FROM main.sqlite_sequence;
      SELECT 2, * FROM temp.sqlite_sequence;
    }
  } {2 t2 21}
  do_test autoinc-4.10 {
    execsql {
      DROP TABLE t2;
      SELECT 1, * FROM main.sqlite_sequence;
      SELECT 2, * FROM temp.sqlite_sequence;
    }
  } {}
}

# Make sure AUTOINCREMENT works on ATTACH-ed tables.
#
ifcapable tempdb {
  do_test autoinc-5.1 {
    file delete -force test2.db
    file delete -force test2.db-journal
    sqlite3 db2 test2.db
    execsql {
      CREATE TABLE t4(m INTEGER PRIMARY KEY AUTOINCREMENT, n);
      CREATE TABLE t5(o, p INTEGER PRIMARY KEY AUTOINCREMENT);
    } db2;
    execsql {
      ATTACH 'test2.db' as aux;
      SELECT 1, * FROM main.sqlite_sequence;
      SELECT 2, * FROM temp.sqlite_sequence;
      SELECT 3, * FROM aux.sqlite_sequence;
    }
  } {}
  do_test autoinc-5.2 {
    execsql {
      INSERT INTO t4 VALUES(NULL,1);
      SELECT 1, * FROM main.sqlite_sequence;
      SELECT 2, * FROM temp.sqlite_sequence;
      SELECT 3, * FROM aux.sqlite_sequence;
    }
  } {3 t4 1}
  do_test autoinc-5.3 {
    execsql {
      INSERT INTO t5 VALUES(100,200);
      SELECT * FROM sqlite_sequence
    } db2
  } {t4 1 t5 200}
  do_test autoinc-5.4 {
    execsql {
      SELECT 1, * FROM main.sqlite_sequence;
      SELECT 2, * FROM temp.sqlite_sequence;
      SELECT 3, * FROM aux.sqlite_sequence;
    }
  } {3 t4 1 3 t5 200}
}

# Requirement REQ00310:  Make sure an insert fails if the sequence is
# already at its maximum value.
#
ifcapable {rowid32} {
  do_test autoinc-6.1 {
    execsql {

# 2003 January 29
#
# 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 script testing the callback-free C/C++ API.
#
# $Id: capi2.test,v 1.26 2005/03/29 03:11:00 danielk1977 Exp $
#

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

# Return the text values from the current row pointed at by STMT as a list.
proc get_row_values {STMT} {

# Check to make sure that the "tail" of a multi-statement SQL script
# is returned by sqlite3_prepare.
#
do_test capi2-2.1 {
  set SQL {
    SELECT name, rowid FROM sqlite_master;
    SELECT name, rowid FROM sqlite_master WHERE 0;
    -- A comment at the end
  }
  set VM [sqlite3_prepare $DB $SQL -1 SQL]
  set SQL
} {
    SELECT name, rowid FROM sqlite_master WHERE 0;
    -- A comment at the end
  }
do_test capi2-2.2 {
  set r [sqlite3_step $VM]
  lappend r [sqlite3_column_count $VM] \
            [get_row_values $VM] \
            [get_column_names $VM]

# 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 built-in functions.
#
# $Id: func.test,v 1.34 2005/03/29 03:11:00 danielk1977 Exp $

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

# Create a table to work with.
#
do_test func-0.0 {

  }
} {68236.0 3 22745.33 1 67890 5}
do_test func-8.2 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

ifcapable tempdb {
  do_test func-8.3 {
    execsql {
      CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
      SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
    }
  } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
} else {
  do_test func-8.3 {
    execsql {
      CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
      SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
    }
  } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
}
do_test func-8.4 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

# How do you test the random() function in a meaningful, deterministic way?

# 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 INSERT statement.
#
# $Id: insert.test,v 1.25 2005/03/29 03:11:00 danielk1977 Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {

    SELECT * FROM t3 WHERE c=99;
  }
} {1 3 99}

# Test the ability to insert from a temporary table into itself.
# Ticket #275.
#
ifcapable tempdb {
  do_test insert-5.1 {
    execsql {
      CREATE TEMP TABLE t4(x);
      INSERT INTO t4 VALUES(1);
      SELECT * FROM t4;
    }
  } {1}
  do_test insert-5.2 {
    execsql {
      INSERT INTO t4 SELECT x+1 FROM t4;
      SELECT * FROM t4;
    }
  } {1 2}
  ifcapable {explain} {
    do_test insert-5.3 {
      # verify that a temporary table is used to copy t4 to t4
      set x [execsql {
        EXPLAIN INSERT INTO t4 SELECT x+2 FROM t4;
      }]
      expr {[lsearch $x OpenTemp]>0}
    } {1}
  }
  
  do_test insert-5.4 {
    # Verify that table "test1" begins on page 3.  This should be the same
    # page number used by "t4" above.
    #
    # Update for v3 - the first table now begins on page 2 of each file, not 3.
    execsql {
      SELECT rootpage FROM sqlite_master WHERE name='test1';
    }
  } [expr $AUTOVACUUM?3:2]
  do_test insert-5.5 {
    # Verify that "t4" begins on page 3.
    #
    # Update for v3 - the first table now begins on page 2 of each file, not 3.
    execsql {
      SELECT rootpage FROM sqlite_temp_master WHERE name='t4';
    }
  } {2}
  do_test insert-5.6 {
    # This should not use an intermediate temporary table.
    execsql {
      INSERT INTO t4 SELECT one FROM test1 WHERE three=7;
      SELECT * FROM t4
    }
  } {1 2 8}
  ifcapable {explain} {
    do_test insert-5.7 {
      # verify that no temporary table is used to copy test1 to t4
      set x [execsql {
        EXPLAIN INSERT INTO t4 SELECT one FROM test1;
      }]
      expr {[lsearch $x OpenTemp]>0}
    } {0}
  }
}

# Ticket #334:  REPLACE statement corrupting indices.
#
do_test insert-6.1 {
  execsql {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement that takes is
# result from a SELECT.
#
# $Id: insert2.test,v 1.16 2005/03/29 03:11:00 danielk1977 Exp $

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

# Create some tables with data that we can select against
#
do_test insert2-1.0 {

do_test insert2-3.8 {
  db changes
} {159}
integrity_check insert2-3.9

# Ticket #901
#
ifcapable tempdb {
  do_test insert2-4.1 {
    execsql {
      CREATE TABLE Dependencies(depId integer primary key,
        class integer, name str, flag str);
      CREATE TEMPORARY TABLE DepCheck(troveId INT, depNum INT,
        flagCount INT, isProvides BOOL, class INTEGER, name STRING,
        flag STRING);
      INSERT INTO DepCheck 
         VALUES(-1, 0, 1, 0, 2, 'libc.so.6', 'GLIBC_2.0');
      INSERT INTO Dependencies 
         SELECT DISTINCT 
             NULL, 
             DepCheck.class, 
             DepCheck.name, 
             DepCheck.flag 
         FROM DepCheck LEFT OUTER JOIN Dependencies ON 
             DepCheck.class == Dependencies.class AND 
             DepCheck.name == Dependencies.name AND 
             DepCheck.flag == Dependencies.flag 
         WHERE 
             Dependencies.depId is NULL;
    };
  } {}
}

#--------------------------------------------------------------------
# Test that the INSERT works when the SELECT statement (a) references
# the table being inserted into and (b) is optimized to use an index
# only.
do_test insert2-5.1 {
  execsql {

# 2004 Feb 8
#
# 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 script is the sqlite_interrupt() API.
#
# $Id: interrupt.test,v 1.11 2005/03/29 03:11:00 danielk1977 Exp $


set testdir [file dirname $argv0]
source $testdir/tester.tcl
db close
set DB [sqlite3 db test.db]

}
integrity_check interrupt-2.6

# Ticket #594.  If an interrupt occurs in the middle of a transaction
# and that transaction is later rolled back, the internal schema tables do
# not reset.
#
ifcapable tempdb {
  for {set i 1} {$i<50} {incr i 5} {
    do_test interrupt-3.$i.1 {
      execsql {
        BEGIN;
        CREATE TEMP TABLE t2(x,y);
        SELECT name FROM sqlite_temp_master;
      }
    } {t2}
    do_test interrupt-3.$i.2 {
      set ::sqlite_interrupt_count $::i
      catchsql {
        INSERT INTO t2 SELECT * FROM t1;
      }
    } {1 interrupted}
    do_test interrupt-3.$i.3 {
      execsql {
        SELECT name FROM sqlite_temp_master;
      }
    } {t2}
    do_test interrupt-3.$i.4 {
      catchsql {
        ROLLBACK
      }
    } {0 {}}
    do_test interrupt-3.$i.5 {
      catchsql {SELECT name FROM sqlite_temp_master};
      execsql {
        SELECT name FROM sqlite_temp_master;
      }
    } {}
  }
}

# There are reports of a memory leak if an interrupt occurs during
# the beginning of a complex query - before the first callback.  We
# will try to reproduce it here:
#
execsql {

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for left outer joins containing WHERE
# clauses that restrict the scope of the left term of the join.
#
# $Id: join4.test,v 1.4 2005/03/29 03:11:00 danielk1977 Exp $

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

ifcapable tempdb {
  do_test join4-1.1 {
    execsql {
      create temp table t1(a integer, b varchar(10));
      insert into t1 values(1,'one');
      insert into t1 values(2,'two');
      insert into t1 values(3,'three');
      insert into t1 values(4,'four');
  
      create temp table t2(x integer, y varchar(10), z varchar(10));
      insert into t2 values(2,'niban','ok');
      insert into t2 values(4,'yonban','err');
    }
    execsql {
      select * from t1 left outer join t2 on t1.a=t2.x where t2.z='ok'
    }
  } {2 two 2 niban ok}
} else {
  do_test join4-1.1 {
    execsql {
      create table t1(a integer, b varchar(10));
      insert into t1 values(1,'one');
      insert into t1 values(2,'two');
      insert into t1 values(3,'three');
      insert into t1 values(4,'four');
  
      create table t2(x integer, y varchar(10), z varchar(10));
      insert into t2 values(2,'niban','ok');
      insert into t2 values(4,'yonban','err');
    }
    execsql {
      select * from t1 left outer join t2 on t1.a=t2.x where t2.z='ok'
    }
  } {2 two 2 niban ok}
}
do_test join4-1.2 {
  execsql {
    select * from t1 left outer join t2 on t1.a=t2.x and t2.z='ok'
  }
} {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}
do_test join4-1.3 {
  execsql {

} {0 1032}
} ;# ifcapable (view && trigger)

# ----------------------------------------------------------------------------
# 7.x - complex tests with temporary tables and nested instead of triggers
# These do not run if views or triggers are disabled.

ifcapable {trigger && view && tempdb} {
do_test lastinsert-7.1 {
    catchsql {
        drop table t1; drop table t2; drop trigger r1;
        create temp table t1 (k integer primary key);
        create temp table t2 (k integer primary key);
        create temp view v1 as select * from t1;
        create temp view v2 as select * from t2;

    }
} {0 1}

# ----------------------------------------------------------------------------
# 5.x - complex tests with temporary tables and nested instead of triggers
# These tests cannot run if the library does not have view support enabled.

ifcapable view&&tempdb {

do_test laststmtchanges-5.1 {
    catchsql {
        drop table t0; drop table t1; drop table t2;
        create temp table t0(x);
        create temp table t1 (k integer primary key);
        create temp table t2 (k integer primary key);

# 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 script is database locks.
#
# $Id: lock.test,v 1.32 2005/03/29 03:11:00 danielk1977 Exp $


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

# Create an alternative connection to the database
#

} {2 1}
do_test lock-5.2 {
  db function tx_exec tx_exec
  catchsql {
    INSERT INTO t1(a,b) SELECT 3, tx_exec('SELECT y FROM t2 LIMIT 1');
  }
} {0 {}}

ifcapable tempdb {
  do_test lock-5.3 {
    execsql {
      CREATE TEMP TABLE t3(x);
      SELECT * FROM t3;
    }
  } {}
  do_test lock-5.4 {
    catchsql {
      INSERT INTO t3 SELECT tx_exec('SELECT y FROM t2 LIMIT 1');
    }
  } {0 {}}
  do_test lock-5.5 {
    execsql {
      SELECT * FROM t3;
    }
  } {8}
  do_test lock-5.6 {
    catchsql {
      UPDATE t1 SET a=tx_exec('SELECT x FROM t2');
    }
  } {0 {}}
  do_test lock-5.7 {
    execsql {
      SELECT * FROM t1;
    }
  } {9 1 9 8}
  do_test lock-5.8 {
    catchsql {
      UPDATE t3 SET x=tx_exec('SELECT x FROM t2');
    }
  } {0 {}}
  do_test lock-5.9 {
    execsql {
      SELECT * FROM t3;
    }
  } {9}
}

do_test lock-999.1 {
  rename db2 {}
} {}

finish_test

# 2005 March 18
#
# 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 attempts to check that the library can recover from a malloc()
# failure when sqlite3_global_recover() is invoked.
#
# $Id: malloc2.test,v 1.2 2005/03/29 03:11:00 danielk1977 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]==""} {

# Generate a checksum based on the contents of the database. If the
# checksum of two databases is the same, and the integrity-check passes
# for both, the two databases are identical.
#
proc cksum {db} {
  set ret [list]
  ifcapable tempdb {
    set sql {
      SELECT name FROM sqlite_master WHERE type = 'table' UNION
      SELECT name FROM sqlite_temp_master WHERE type = 'table' UNION
      SELECT 'sqlite_master' UNION
      SELECT 'sqlite_temp_master'
    }
  } else {
    set sql {
      SELECT name FROM sqlite_master WHERE type = 'table' UNION
      SELECT 'sqlite_master'
    }
  }
  foreach tbl [$db eval $sql] {
    set cols [list]
    $db eval "PRAGMA table_info($tbl)" {
      lappend cols $name
    }
    set sql "SELECT md5sum([join $cols ,]) FROM $tbl"

  DROP TABLE def2;

  DELETE FROM def WHERE (oid%9)==2;
  INSERT INTO def SELECT * FROM def WHERE (oid%13)==2;
  COMMIT;
}

ifcapable tempdb {
  do_test malloc2.3.setup {
    execsql {
      CREATE TEMP TABLE ghi(a, b, c);
      BEGIN;
    }
    for {set i 0} {$i<20} {incr i} {
      execsql {
      INSERT INTO ghi VALUES(randstr(300,300),randstr(300,300),randstr(300,300));
      }
    }
    execsql {
      COMMIT;
    }
  } {}
  do_malloc2_test 3 -sql {
    BEGIN;
    CREATE INDEX ghi_i1 ON ghi(a);
    UPDATE def SET a = randstr(100,100) WHERE (oid%2)==0;
    UPDATE ghi SET a = randstr(100,100) WHERE (oid%2)==0;
    COMMIT;
  }
}

############################################################################
# The test cases below are to increase the code coverage in btree.c and 
# pager.c of this test file. The idea is that each malloc() that occurs in
# these two source files should be made to fail at least once.
#

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.34 2005/03/29 03:11:00 danielk1977 Exp $

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

# Mimic the SQLite 2 collation type NUMERIC.
db collate numeric numeric_collate
proc numeric_collate {lhs rhs} {

do_test misc1-16.6 {
  execsql {
    INSERT INTO test VALUES(NULL);
    SELECT rowid, a FROM test;
  }
} {1 1 5 5 6 6}

ifcapable trigger&&tempdb {
# Ticket #333: Temp triggers that modify persistent tables.
#
do_test misc1-17.1 {
  execsql {
    BEGIN;
    CREATE TABLE RealTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
    CREATE TEMP TABLE TempTable(TestID INTEGER PRIMARY KEY, TestString TEXT);

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc2.test,v 1.21 2005/03/29 03:11:00 danielk1977 Exp $

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

ifcapable {trigger} {
# Test for ticket #360
#

#
do_test misc2-8.1 {
  catchsql {-}
} {1 {near "-": syntax error}}

# Ticket #513.  Make sure the VDBE stack does not grow on a 3-way join.
#
ifcapable tempdb {
  do_test misc2-9.1 {
    execsql {
      BEGIN;
      CREATE TABLE counts(n INTEGER PRIMARY KEY);
      INSERT INTO counts VALUES(0);
      INSERT INTO counts VALUES(1);
      INSERT INTO counts SELECT n+2 FROM counts;
      INSERT INTO counts SELECT n+4 FROM counts;
      INSERT INTO counts SELECT n+8 FROM counts;
      COMMIT;
  
      CREATE TEMP TABLE x AS
      SELECT dim1.n, dim2.n, dim3.n
      FROM counts AS dim1, counts AS dim2, counts AS dim3
      WHERE dim1.n<10 AND dim2.n<10 AND dim3.n<10;
  
      SELECT count(*) FROM x;
    }
  } {1000}
  do_test misc2-9.2 {
    execsql {
      DROP TABLE x;
      CREATE TEMP TABLE x AS
      SELECT dim1.n, dim2.n, dim3.n
      FROM counts AS dim1, counts AS dim2, counts AS dim3
      WHERE dim1.n>=6 AND dim2.n>=6 AND dim3.n>=6;
  
      SELECT count(*) FROM x;
    }
  } {1000}
  do_test misc2-9.3 {
    execsql {
      DROP TABLE x;
      CREATE TEMP TABLE x AS
      SELECT dim1.n, dim2.n, dim3.n, dim4.n
      FROM counts AS dim1, counts AS dim2, counts AS dim3, counts AS dim4
      WHERE dim1.n<5 AND dim2.n<5 AND dim3.n<5 AND dim4.n<5;
  
      SELECT count(*) FROM x;
    }
  } [expr 5*5*5*5]
}

finish_test

#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.16 2005/03/29 03:11:00 danielk1977 Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#
do_test misc4-1.1 {
  db close
  set DB [sqlite3 db test.db]
  execsql {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(1);
  }
} {}

ifcapable tempdb {
  do_test misc4-1.2 {
    set sql {CREATE TEMP TABLE t2 AS SELECT * FROM t1}
    set stmt [sqlite3_prepare $DB $sql -1 TAIL]
    execsql {
      BEGIN;
      CREATE TABLE t3(a,b,c);
      INSERT INTO t1 SELECT * FROM t1;
      ROLLBACK;
    }
  } {}
  do_test misc4-1.3 {
    sqlite3_step $stmt
  } SQLITE_DONE
  do_test misc4-1.4 {
    execsql {
      SELECT * FROM temp.t2;
    }
  } {1}
  
  # Drop the temporary table, then rerun the prepared  statement to
  # recreate it again.  This recreates ticket #807.
  #
  do_test misc4-1.5 {
    execsql {DROP TABLE t2}
    sqlite3_reset $stmt
    sqlite3_step $stmt
  } {SQLITE_ERROR}
  do_test misc4-1.6 {
    sqlite3_finalize $stmt
  } {SQLITE_SCHEMA}
}

# Prepare but do not execute various CREATE statements.  Then before
# those statements are executed, try to use the tables, indices, views,
# are triggers that were created.
#
do_test misc4-2.1 {
  set stmt [sqlite3_prepare $DB {CREATE TABLE t3(x);} -1 TAIL]

# 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 script is page cache subsystem.
#
# $Id: pager3.test,v 1.3 2005/03/29 03:11:00 danielk1977 Exp $


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

# This test makes sure the database file is truncated back to the correct
# length on a rollback.

# written so the file is actually lengthened by this truncate.  Finally,
# the rollback at NOTE (3) is called to undo all the changes since the
# begin.  This rollback should truncate the database again.
# 
# This test was added because the second truncate at NOTE (3) was not
# occurring on early versions of SQLite 3.0.
#
ifcapable tempdb {
  do_test pager3-1.1 {
    execsql {
      create table t1(a unique, b);
      insert into t1 values(1, 'abcdefghijklmnopqrstuvwxyz');
      insert into t1 values(2, 'abcdefghijklmnopqrstuvwxyz');
      update t1 set b=b||a||b;
      update t1 set b=b||a||b;
      update t1 set b=b||a||b;
      update t1 set b=b||a||b;
      update t1 set b=b||a||b;
      update t1 set b=b||a||b;
      create temp table t2 as select * from t1;
      begin;                  ------- NOTE (1)
      create table t3(x);
    }
    catchsql {
      insert into t1 select 4-a, b from t2;  ----- NOTE (2)
    }
    execsql {
      rollback;  ------- NOTE (3)
    }
    db close
    sqlite3 db test.db
    set r ok
    ifcapable {integrityck} {
      set r [execsql {
        pragma integrity_check;
      }]
    }
    set r
  } ok
}

finish_test

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.35 2005/03/29 03:11:00 danielk1977 Exp $

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

# Test organization:
#
# pragma-1.*: Test cache_size, default_cache_size and synchronous on main db.

} {2}
catchsql {COMMIT;}
} ;# ifcapable pager_pragmas

# Test schema-query pragmas
#
ifcapable schema_pragmas {
ifcapable tempdb {
  do_test pragma-6.1 {
    set res {}
    execsql {SELECT * FROM sqlite_temp_master}
    foreach {idx name file} [execsql {pragma database_list}] {
      lappend res $idx $name
    }
    set res
  } {0 main 1 temp 2 aux}
}
do_test pragma-6.2 {
  execsql {
    pragma table_info(t2)
  }
} {0 a numeric 0 {} 0 1 b numeric 0 {} 0 2 c numeric 0 {} 0}
ifcapable {foreignkey} {
  do_test pragma-6.3 {

    db close
    sqlite3 db test.db
    catchsql {
      pragma encoding=bogus;
    }
  } {1 {unsupported encoding: bogus}}
}
ifcapable tempdb {
  do_test pragma-7.3 {
    db close
    sqlite3 db test.db
    execsql {
      pragma lock_status;
    }
  } {main unlocked temp closed}
} else {
  do_test pragma-7.3 {
    db close
    sqlite3 db test.db
    execsql {
      pragma lock_status;
    }
  } {main unlocked}
}


#----------------------------------------------------------------------
# Test cases pragma-8.* test the "PRAGMA schema_version" and "PRAGMA
# user_version" statements.
#
# pragma-8.1: PRAGMA schema_version

  }
} {1 {not a writable directory}}
do_test pragma-9.8 {
  execsql { 
    PRAGMA temp_store_directory='';
  }
} {}
ifcapable tempdb {
  do_test pragma-9.9 {
    execsql { 
      PRAGMA temp_store_directory;
      PRAGMA temp_store=FILE;
      CREATE TEMP TABLE temp_store_directory_test(a integer);
      INSERT INTO temp_store_directory_test values (2);
      SELECT * FROM temp_store_directory_test;
    }
  } {2}
}
do_test pragma-9.10 {
  catchsql "
    PRAGMA temp_store_directory='$pwd';
    SELECT * FROM temp_store_directory_test;
  "
} {1 {no such table: temp_store_directory_test}}
} ;# ifcapable pager_pragmas

# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file runs all tests.
#
# $Id: quick.test,v 1.35 2005/03/29 03:11:00 danielk1977 Exp $

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

set EXCLUDE {
  alter.test
  all.test
  btree2.test
  btree3.test
  btree4.test
  btree5.test
  btree6.test
  corrupt.test

# 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 compute SELECT statements and nested
# views.
#
# $Id: select7.test,v 1.7 2005/03/29 03:11:00 danielk1977 Exp $


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

ifcapable compound {

# A 3-way INTERSECT.  Ticket #875
ifcapable tempdb {
  do_test select7-1.1 {
    execsql {
      create temp table t1(x);
      insert into t1 values('amx');
      insert into t1 values('anx');
      insert into t1 values('amy');
      insert into t1 values('bmy');
      select * from t1 where x like 'a__'
        intersect select * from t1 where x like '_m_'
        intersect select * from t1 where x like '__x';
    }
  } {amx}
}


# Nested views do not handle * properly.  Ticket #826.
#
ifcapable view {
do_test select7-2.1 {
  execsql {

# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: table.test,v 1.39 2005/03/29 03:11:00 danielk1977 Exp $

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

# Create a basic table and verify it is added to sqlite_master
#
do_test table-1.1 {

# NULL pointer, so the created table has no column types. (Changed result
# from {{CREATE TABLE 't4"abc'(cnt NUMERIC,"max(b+c)" NUMERIC)}}).
do_test table-8.3.1 {
  execsql {
    SELECT sql FROM sqlite_master WHERE name='t4"abc'
  }
} {{CREATE TABLE "t4""abc"(cnt,"max(b+c)")}}

ifcapable tempdb {
  do_test table-8.4 {
    execsql2 {
      CREATE TEMPORARY TABLE t5 AS SELECT count(*) AS [y'all] FROM [t3"xyz];
      SELECT * FROM t5;
    }
  } {y'all 1}
}

do_test table-8.5 {
  db close
  sqlite3 db test.db
  execsql2 {
    SELECT * FROM [t4"abc];
  }
} {cnt 1 max(b+c) 5}

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for temporary tables and indices.
#
# $Id: temptable.test,v 1.15 2005/03/29 03:11:00 danielk1977 Exp $

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

ifcapable !tempdb {
  finish_test
  return
}

# Create an alternative connection to the database
#
do_test temptable-1.0 {
  sqlite3 db2 ./test.db
  set dummy {}
} {}

# 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 script is database locks.
#
# $Id: trans.test,v 1.26 2005/03/29 03:11:00 danielk1977 Exp $


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


# Create several tables to work with.

    ROLLBACK;
    SELECT md5sum(x,y,z) FROM t2;
  }
} $checksum
do_test trans-7.8 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
ifcapable tempdb {
  do_test trans-7.9 {
    execsql {
      BEGIN;
      CREATE TEMP TABLE t3 AS SELECT * FROM t2;
      INSERT INTO t2 SELECT * FROM t3;
      ROLLBACK;
      SELECT md5sum(x,y,z) FROM t2;
    }
  } $checksum
}
do_test trans-7.10 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
ifcapable tempdb {
  do_test trans-7.11 {
    execsql {
      BEGIN;
      CREATE TEMP TABLE t3 AS SELECT * FROM t2;
      INSERT INTO t2 SELECT * FROM t3;
      DROP INDEX i2x;
      DROP INDEX i2y;
      CREATE INDEX i3a ON t3(x);
      ROLLBACK;
      SELECT md5sum(x,y,z) FROM t2;
    }
  } $checksum
}
do_test trans-7.12 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
ifcapable tempdb {
  do_test trans-7.13 {
    execsql {
      BEGIN;
      DROP TABLE t2;
      ROLLBACK;
      SELECT md5sum(x,y,z) FROM t2;
    }
  } $checksum
}
do_test trans-7.14 {
  execsql {SELECT md5sum(type,name,tbl_name,rootpage,sql) FROM sqlite_master}
} $checksum2
integrity_check trans-7.15

# Arrange for another process to begin modifying the database but abort
# and die in the middle of the modification.  Then have this process read

do_test trigger1-1.1.1 {
   catchsql {
     CREATE TRIGGER trig UPDATE ON no_such_table BEGIN
       SELECT * from sqlite_master;
     END;
   } 
} {1 {no such table: main.no_such_table}}

ifcapable tempdb {
  do_test trigger1-1.1.2 {
     catchsql {
       CREATE TEMP TRIGGER trig UPDATE ON no_such_table BEGIN
         SELECT * from sqlite_master;
       END;
     } 
  } {1 {no such table: no_such_table}}
}

execsql {
    CREATE TABLE t1(a);
}
execsql {
	CREATE TRIGGER tr1 INSERT ON t1 BEGIN
	  INSERT INTO t1 values(1);

do_test trigger1-1.7 {
    catchsql {
	DROP TABLE t1;
	DROP TRIGGER tr1;
    }
} {1 {no such trigger: tr1}}

ifcapable tempdb {
  execsql {
    CREATE TEMP TABLE temp_table(a);
  }
  do_test trigger1-1.8 {
    execsql {
  	CREATE TRIGGER temp_trig UPDATE ON temp_table BEGIN
  	    SELECT * from sqlite_master;
  	END;
  	SELECT count(*) FROM sqlite_master WHERE name = 'temp_trig';
    } 
  } {0}
}

do_test trigger1-1.9 {
  catchsql {
    CREATE TRIGGER tr1 AFTER UPDATE ON sqlite_master BEGIN
       SELECT * FROM sqlite_master;
    END;
  }

      SELECT * FROM;  -- Syntax error
    END;
  }
} {1 {near ";": syntax error}}

# Create a trigger that refers to a table that might not exist.
#
ifcapable tempdb {
  do_test trigger1-3.1 {
    execsql {
      CREATE TEMP TABLE t2(x,y);
    }
    catchsql {
      CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
        INSERT INTO t2 VALUES(NEW.a,NEW.b);
      END;
    }
  } {0 {}}
  do_test trigger-3.2 {
    catchsql {
      INSERT INTO t1 VALUES(1,2);
      SELECT * FROM t2;
    }
  } {1 {no such table: main.t2}}
  do_test trigger-3.3 {
    db close
    set rc [catch {sqlite3 db test.db} err]
    if {$rc} {lappend rc $err}
    set rc
  } {0}
  do_test trigger-3.4 {
    catchsql {
      INSERT INTO t1 VALUES(1,2);
      SELECT * FROM t2;
    }
  } {1 {no such table: main.t2}}
  do_test trigger-3.5 {
    catchsql {
      CREATE TEMP TABLE t2(x,y);
      INSERT INTO t1 VALUES(1,2);
      SELECT * FROM t2;
    }
  } {1 {no such table: main.t2}}
  do_test trigger-3.6 {
    catchsql {
      DROP TRIGGER r1;
      CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN
        INSERT INTO t2 VALUES(NEW.a,NEW.b);
      END;
      INSERT INTO t1 VALUES(1,2);
      SELECT * FROM t2;
    }
  } {0 {1 2}}
  do_test trigger-3.7 {
    execsql {
      DROP TABLE t2;
      CREATE TABLE t2(x,y);
      SELECT * FROM t2;
    }
  } {}

  # There are two versions of trigger-3.8 and trigger-3.9. One that uses
  # compound SELECT statements, and another that does not.
  ifcapable compound {
  do_test trigger1-3.8 {
    execsql {
      INSERT INTO t1 VALUES(3,4);
      SELECT * FROM t1 UNION ALL SELECT * FROM t2;
    }
  } {1 2 3 4 3 4}
  do_test trigger1-3.9 {
    db close
    sqlite3 db test.db
    execsql {
      INSERT INTO t1 VALUES(5,6);
      SELECT * FROM t1 UNION ALL SELECT * FROM t2;
    }
  } {1 2 3 4 5 6 3 4}
  } ;# ifcapable compound
  ifcapable !compound {
  do_test trigger1-3.8 {
    execsql {
      INSERT INTO t1 VALUES(3,4);
      SELECT * FROM t1; 
      SELECT * FROM t2;
    }
  } {1 2 3 4 3 4}
  do_test trigger1-3.9 {
    db close
    sqlite3 db test.db
    execsql {
      INSERT INTO t1 VALUES(5,6);
      SELECT * FROM t1;
      SELECT * FROM t2;
    }
  } {1 2 3 4 5 6 3 4}
  } ;# ifcapable !compound

  do_test trigger1-4.1 {
    execsql {
      CREATE TEMP TRIGGER r1 BEFORE INSERT ON t1 BEGIN
        INSERT INTO t2 VALUES(NEW.a,NEW.b);
      END;
      INSERT INTO t1 VALUES(7,8);
      SELECT * FROM t2;
    }
  } {3 4 7 8}
  do_test trigger1-4.2 {
    sqlite3 db2 test.db
    execsql {
      INSERT INTO t1 VALUES(9,10);
    } db2;
    db2 close
    execsql {
      SELECT * FROM t2;
    }
  } {3 4 7 8}
  do_test trigger1-4.3 {
    execsql {
      DROP TABLE t1;
      SELECT * FROM t2;
    };
  } {3 4 7 8}
  do_test trigger1-4.4 {
    db close
    sqlite3 db test.db
    execsql {
      SELECT * FROM t2;
    };
  } {3 4 7 8}
} else {
  execsql {
    CREATE TABLE t2(x,y);
    DROP TABLE t1;
    INSERT INTO t2 VALUES(3, 4);

    INSERT INTO t2 VALUES(7, 8);
  }
}








integrity_check trigger1-5.1

# Create a trigger with the same name as a table.  Make sure the
# trigger works.  Then drop the trigger.  Make sure the table is
# still there.
#
set view_v1 {}
ifcapable view {
  set view_v1 {view v1}
}
do_test trigger1-6.1 {
  execsql {SELECT type, name FROM sqlite_master}
} [concat $view_v1 {table t2}]
do_test trigger1-6.2 {
  execsql {
    CREATE TRIGGER t2 BEFORE DELETE ON t2 BEGIN
      SELECT RAISE(ABORT,'deletes are not allows');
    END;
    SELECT type, name FROM sqlite_master;
  }
} [concat $view_v1 {table t2 trigger t2}]
do_test trigger1-6.3 {
  catchsql {DELETE FROM t2}
} {1 {deletes are not allows}}
do_test trigger1-6.4 {
  execsql {SELECT * FROM t2}
} {3 4 7 8}
do_test trigger1-6.5 {
  db close
  sqlite3 db test.db
  execsql {SELECT type, name FROM sqlite_master}
} [concat $view_v1 {table t2 trigger t2}]
do_test trigger1-6.6 {
  execsql {
    DROP TRIGGER t2;
    SELECT type, name FROM sqlite_master;
  }
} [concat $view_v1 {table t2}]
do_test trigger1-6.7 {
  execsql {SELECT * FROM t2}
} {3 4 7 8}
do_test trigger1-6.8 {
  db close
  sqlite3 db test.db
  execsql {SELECT * FROM t2}
} {3 4 7 8}

integrity_check trigger-7.1

# Check to make sure the name of a trigger can be quoted so that keywords
# can be used as trigger names.  Ticket #468
#
do_test trigger1-8.1 {
  execsql {
    CREATE TRIGGER 'trigger' AFTER INSERT ON t2 BEGIN SELECT 1; END;
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {trigger}
do_test trigger1-8.2 {
  execsql {
    DROP TRIGGER 'trigger';
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {}
do_test trigger1-8.3 {
  execsql {
    CREATE TRIGGER "trigger" AFTER INSERT ON t2 BEGIN SELECT 1; END;
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {trigger}
do_test trigger1-8.4 {
  execsql {
    DROP TRIGGER "trigger";
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {}
do_test trigger1-8.5 {
  execsql {
    CREATE TRIGGER [trigger] AFTER INSERT ON t2 BEGIN SELECT 1; END;
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {trigger}
do_test trigger1-8.6 {
  execsql {
    DROP TRIGGER [trigger];
    SELECT name FROM sqlite_master WHERE type='trigger';
  }
} {}

# Make sure REPLACE works inside of triggers.
#
# There are two versions of trigger-9.1 and trigger-9.2. One that uses
# compound SELECT statements, and another that does not.
ifcapable compound {
do_test trigger1-9.1 {
  execsql {
    CREATE TABLE t3(a,b);
    CREATE TABLE t4(x UNIQUE, b);
    CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
      REPLACE INTO t4 VALUES(new.a,new.b);
    END;
    INSERT INTO t3 VALUES(1,2);
    SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
  }
} {1 2 99 99 1 2}
do_test trigger1-9.2 {
  execsql {
    INSERT INTO t3 VALUES(1,3);
    SELECT * FROM t3 UNION ALL SELECT 99, 99 UNION ALL SELECT * FROM t4;
  }
} {1 2 1 3 99 99 1 3}
}
ifcapable !compound {
do_test trigger1-9.1 {
  execsql {
    CREATE TABLE t3(a,b);
    CREATE TABLE t4(x UNIQUE, b);
    CREATE TRIGGER r34 AFTER INSERT ON t3 BEGIN
      REPLACE INTO t4 VALUES(new.a,new.b);
    END;
    INSERT INTO t3 VALUES(1,2);
    SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;
  }
} {1 2 99 99 1 2}
do_test trigger1-9.2 {
  execsql {
    INSERT INTO t3 VALUES(1,3);
    SELECT * FROM t3; SELECT 99, 99; SELECT * FROM t4;
  }
} {1 2 1 3 99 99 1 3}
}

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

# Ticket #764. At one stage TEMP triggers would fail to re-install when the
# schema was reloaded. The following tests ensure that TEMP triggers are
# correctly re-installed.
#
# Also verify that references within trigger programs are resolved at
# statement compile time, not trigger installation time. This means, for
# example, that you can drop and re-create tables referenced by triggers. 
ifcapable tempdb {
  do_test trigger1-10.0 {
    file delete -force test2.db
    file delete -force test2.db-journal
    execsql {
      ATTACH 'test2.db' AS aux;
    }
  } {}
  do_test trigger1-10.1 {
    execsql {
      CREATE TABLE main.t4(a, b, c);
      CREATE TABLE temp.t4(a, b, c);
      CREATE TABLE aux.t4(a, b, c);
      CREATE TABLE insert_log(db, a, b, c);
    }
  } {}
  do_test trigger1-10.2 {
    execsql {
      CREATE TEMP TRIGGER trig1 AFTER INSERT ON main.t4 BEGIN 
        INSERT INTO insert_log VALUES('main', new.a, new.b, new.c);
      END;
      CREATE TEMP TRIGGER trig2 AFTER INSERT ON temp.t4 BEGIN 
        INSERT INTO insert_log VALUES('temp', new.a, new.b, new.c);
      END;
      CREATE TEMP TRIGGER trig3 AFTER INSERT ON aux.t4 BEGIN 
        INSERT INTO insert_log VALUES('aux', new.a, new.b, new.c);
      END;
    }
  } {}
  do_test trigger1-10.3 {
    execsql {
      INSERT INTO main.t4 VALUES(1, 2, 3);
      INSERT INTO temp.t4 VALUES(4, 5, 6);
      INSERT INTO aux.t4  VALUES(7, 8, 9);
    }
  } {}
  do_test trigger1-10.4 {
    execsql {
      SELECT * FROM insert_log;
    }
  } {main 1 2 3 temp 4 5 6 aux 7 8 9}
  do_test trigger1-10.5 {
    execsql {
      BEGIN;
      INSERT INTO main.t4 VALUES(1, 2, 3);
      INSERT INTO temp.t4 VALUES(4, 5, 6);
      INSERT INTO aux.t4  VALUES(7, 8, 9);
      ROLLBACK;
    }
  } {}
  do_test trigger1-10.6 {
    execsql {
      SELECT * FROM insert_log;
    }
  } {main 1 2 3 temp 4 5 6 aux 7 8 9}
  do_test trigger1-10.7 {
    execsql {
      DELETE FROM insert_log;
      INSERT INTO main.t4 VALUES(11, 12, 13);
      INSERT INTO temp.t4 VALUES(14, 15, 16);
      INSERT INTO aux.t4  VALUES(17, 18, 19);
    }
  } {}
  do_test trigger1-10.8 {
    execsql {
      SELECT * FROM insert_log;
    }
  } {main 11 12 13 temp 14 15 16 aux 17 18 19}
  do_test trigger1-10.8 {
  # Drop and re-create the insert_log table in a different database. Note
  # that we can change the column names because the trigger programs don't
  # use them explicitly.
    execsql {
      DROP TABLE insert_log;
      CREATE TABLE aux.insert_log(db, d, e, f);
    }
  } {}
  do_test trigger1-10.10 {
    execsql {
      INSERT INTO main.t4 VALUES(21, 22, 23);
      INSERT INTO temp.t4 VALUES(24, 25, 26);
      INSERT INTO aux.t4  VALUES(27, 28, 29);
    }
  } {}
  do_test trigger1-10.11 {
    execsql {
      SELECT * FROM insert_log;
    }
  } {main 21 22 23 temp 24 25 26 aux 27 28 29}
}

finish_test

  finish_test
  return
}

# 1.
ifcapable subquery {
  set ii 0
  set tbl_definitions [list \

  	{CREATE TABLE tbl (a, b);}                                      \
  	{CREATE TABLE tbl (a INTEGER PRIMARY KEY, b);}                  \

        {CREATE TABLE tbl (a, b PRIMARY KEY);}                          \
  	{CREATE TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);}      \
  ]
  ifcapable tempdb {
    lappend tbl_definitions \
        {CREATE TEMP TABLE tbl (a, b); CREATE INDEX tbl_idx ON tbl(b);} 

    lappend tbl_definitions {CREATE TEMP TABLE tbl (a, b);}
    lappend tbl_definitions \
        {CREATE TEMPORARY TABLE tbl (a INTEGER PRIMARY KEY, b);}
  }
  foreach tbl_defn $tbl_definitions {
    incr ii
    catchsql { DROP INDEX tbl_idx; }
    catchsql {
      DROP TABLE rlog;
      DROP TABLE clog;
      DROP TABLE tbl;
      DROP TABLE other_tbl;

# 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 VACUUM statement.
#
# $Id: vacuum.test,v 1.35 2005/03/29 03:11:00 danielk1977 Exp $

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

# If the VACUUM statement is disabled in the current build, skip all
# the tests in this file.
#

  sqlite3 db test.db
  catchsql {
    CREATE TABLE Test (TestID int primary key);
    INSERT INTO Test VALUES (NULL);
    CREATE VIEW viewTest AS SELECT * FROM Test;

    BEGIN;
    CREATE TABLE tempTest (TestID int primary key, Test2 int NULL);
    INSERT INTO tempTest SELECT TestID, 1 FROM Test;
    DROP TABLE Test;
    CREATE TABLE Test(TestID int primary key, Test2 int NULL);
    INSERT INTO Test SELECT * FROM tempTest;
    DROP TABLE tempTest;
    COMMIT;
    VACUUM;
  }
} {0 {}}
do_test vacuum-5.2 {
  catchsql {
    VACUUM;