SQLite

**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.353 2005/11/03 00:41:17 drh 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.

  /* Delete the Table structure itself.
  */
  sqliteResetColumnNames(pTable);
  sqliteFree(pTable->zName);
  sqliteFree(pTable->zColAff);
  sqlite3SelectDelete(pTable->pSelect);
#ifndef SQLITE_OMIT_CHECK
  sqlite3ExprDelete(pTable->pCheck);
#endif
  sqliteFree(pTable);
}

/*
** Unlink the given table from the hash tables and the delete the
** table structure with all its indices and foreign keys.
*/

    pList = 0;
  }

primary_key_exit:
  sqlite3ExprListDelete(pList);
  return;
}

/*
** Add a new CHECK constraint to the table currently under construction.
*/
void sqlite3AddCheckConstraint(
  Parse *pParse,    /* Parsing context */
  Expr *pCheckExpr  /* The check expression */
){
#ifndef SQLITE_OMIT_CHECK
  Table *pTab = pParse->pNewTable;
  if( pTab ){
    /* The CHECK expression must be duplicated so that tokens refer
    ** to malloced space and not the (ephemeral) text of the CREATE TABLE
    ** statement */
    pTab->pCheck = sqlite3ExprAnd(pTab->pCheck, sqlite3ExprDup(pCheckExpr));
  }
#endif
  sqlite3ExprDelete(pCheckExpr);
}

/*
** Set the collation function of the most recently parsed table column
** to the CollSeq given.
*/
void sqlite3AddCollateType(Parse *pParse, const char *zType, int nType){
  Table *p;

  if( (pEnd==0 && pSelect==0) || pParse->nErr || sqlite3_malloc_failed ) return;
  p = pParse->pNewTable;
  if( p==0 ) return;

  assert( !db->init.busy || !pSelect );

#ifndef SQLITE_OMIT_CHECK
  /* Resolve names in all CHECK constraint expressions.
  */
  if( p->pCheck ){
    SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
    NameContext sNC;                /* Name context for pParse->pNewTable */

    memset(&sNC, 0, sizeof(sNC));
    memset(&sSrc, 0, sizeof(sSrc));
    sSrc.nSrc = 1;
    sSrc.a[0].zName = p->zName;
    sSrc.a[0].pTab = p;
    sSrc.a[0].iCursor = -1;
    sNC.pParse = pParse;
    sNC.pSrcList = &sSrc;
    if( sqlite3ExprResolveNames(&sNC, p->pCheck) ){
      return;
    }
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* If the db->init.busy is 1 it means we are reading the SQL off the
  ** "sqlite_master" or "sqlite_temp_master" table on the disk.
  ** So do not write to the disk again.  Extract the root page number
  ** for the table from the db->init.newTnum field.  (The page number
  ** should have been put there by the sqliteOpenCb routine.)
  */
  if( db->init.busy ){

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.233 2005/11/03 00:41:17 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**

** means that the form of the name is Z and that columns from any table
** can be used.
**
** If the name cannot be resolved unambiguously, leave an error message
** in pParse and return non-zero.  Return zero on success.
*/
static int lookupName(
  Parse *pParse,       /* The parsing context */
  Token *pDbToken,     /* Name of the database containing table, or NULL */
  Token *pTableToken,  /* Name of table containing column, or NULL */
  Token *pColumnToken, /* Name of the column. */
  NameContext *pNC,    /* The name context used to resolve the name */
  Expr *pExpr          /* Make this EXPR node point to the selected column */
){
  char *zDb = 0;       /* Name of the database.  The "X" in X.Y.Z */

  zCol = sqlite3NameFromToken(pColumnToken);
  if( sqlite3_malloc_failed ){
    goto lookupname_end;
  }

  pExpr->iTable = -1;
  while( pNC && cnt==0 ){
    ExprList *pEList;
    SrcList *pSrcList = pNC->pSrcList;



    if( pSrcList ){
      for(i=0, pItem=pSrcList->a; i<pSrcList->nSrc; i++, pItem++){
        Table *pTab = pItem->pTab;
        Column *pCol;
  
        if( pTab==0 ) continue;
        assert( pTab->nCol>0 );

    **     SELECT a+b AS x FROM table WHERE x<10;
    **
    ** In cases like this, replace pExpr with a copy of the expression that
    ** forms the result set entry ("a+b" in the example) and return immediately.
    ** Note that the expression in the result set should have already been
    ** resolved by the time the WHERE clause is resolved.
    */
    if( cnt==0 && (pEList = pNC->pEList)!=0 && zTab==0 ){
      for(j=0; j<pEList->nExpr; j++){
        char *zAs = pEList->a[j].zName;
        if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
          assert( pExpr->pLeft==0 && pExpr->pRight==0 );
          pExpr->op = TK_AS;
          pExpr->iColumn = j;
          pExpr->pLeft = sqlite3ExprDup(pEList->a[j].pExpr);

  assert( pNC!=0 );
  pSrcList = pNC->pSrcList;
  pParse = pNC->pParse;

  if( ExprHasAnyProperty(pExpr, EP_Resolved) ) return 1;
  ExprSetProperty(pExpr, EP_Resolved);
#ifndef NDEBUG
  if( pSrcList && pSrcList->nAlloc>0 ){
    int i;
    for(i=0; i<pSrcList->nSrc; i++){
      assert( pSrcList->a[i].iCursor>=0 && pSrcList->a[i].iCursor<pParse->nTab);
    }
  }
#endif
  switch( pExpr->op ){

** operation.  Special comments in vdbe.c and the mkopcodeh.awk script in
** the make process cause these values to align.  Assert()s in the code
** below verify that the numbers are aligned correctly.
*/
void sqlite3ExprCode(Parse *pParse, Expr *pExpr){
  Vdbe *v = pParse->pVdbe;
  int op;
  int stackChng = 1;    /* Amount of change to stack depth */

  if( v==0 ) return;
  if( pExpr==0 ){
    sqlite3VdbeAddOp(v, OP_Null, 0, 0);
    return;
  }
  op = pExpr->op;
  switch( op ){

        sqlite3VdbeAddOp(v, OP_Column, pAggInfo->sortingIdx,
                              pCol->iSorterColumn);
        break;
      }
      /* Otherwise, fall thru into the TK_COLUMN case */
    }
    case TK_COLUMN: {
      if( pExpr->iTable<0 ){
        /* This only happens when coding check constraints */
        assert( pParse->ckOffset>0 );
        sqlite3VdbeAddOp(v, OP_Dup, pParse->ckOffset-pExpr->iColumn-1, 1);
      }else if( pExpr->iColumn>=0 ){
        sqlite3VdbeAddOp(v, OP_Column, pExpr->iTable, pExpr->iColumn);
        sqlite3ColumnDefault(v, pExpr->pTab, pExpr->iColumn);
      }else{
        sqlite3VdbeAddOp(v, OP_Rowid, pExpr->iTable, 0);
      }
      break;
    }

      switch( aff ){
        case SQLITE_AFF_INTEGER:   op = OP_ToInt;      break;
        case SQLITE_AFF_NUMERIC:   op = OP_ToNumeric;  break;
        case SQLITE_AFF_TEXT:      op = OP_ToText;     break;
        case SQLITE_AFF_NONE:      op = OP_ToBlob;     break;
      }
      sqlite3VdbeAddOp(v, op, 0, 0);
      stackChng = 0;
      break;
    }
#endif /* SQLITE_OMIT_CAST */
    case TK_LT:
    case TK_LE:
    case TK_GT:
    case TK_GE:
    case TK_NE:
    case TK_EQ: {
      assert( TK_LT==OP_Lt );
      assert( TK_LE==OP_Le );
      assert( TK_GT==OP_Gt );
      assert( TK_GE==OP_Ge );
      assert( TK_EQ==OP_Eq );
      assert( TK_NE==OP_Ne );
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3ExprCode(pParse, pExpr->pRight);
      codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, 0, 0);
      stackChng = -1;
      break;
    }
    case TK_AND:
    case TK_OR:
    case TK_PLUS:
    case TK_STAR:
    case TK_MINUS:

      assert( TK_SLASH==OP_Divide );
      assert( TK_LSHIFT==OP_ShiftLeft );
      assert( TK_RSHIFT==OP_ShiftRight );
      assert( TK_CONCAT==OP_Concat );
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3ExprCode(pParse, pExpr->pRight);
      sqlite3VdbeAddOp(v, op, 0, 0);
      stackChng = -1;
      break;
    }
    case TK_UMINUS: {
      Expr *pLeft = pExpr->pLeft;
      assert( pLeft );
      if( pLeft->op==TK_FLOAT || pLeft->op==TK_INTEGER ){
        Token *p = &pLeft->token;

    }
    case TK_BITNOT:
    case TK_NOT: {
      assert( TK_BITNOT==OP_BitNot );
      assert( TK_NOT==OP_Not );
      sqlite3ExprCode(pParse, pExpr->pLeft);
      sqlite3VdbeAddOp(v, op, 0, 0);
      stackChng = 0;
      break;
    }
    case TK_ISNULL:
    case TK_NOTNULL: {
      int dest;
      assert( TK_ISNULL==OP_IsNull );
      assert( TK_NOTNULL==OP_NotNull );
      sqlite3VdbeAddOp(v, OP_Integer, 1, 0);
      sqlite3ExprCode(pParse, pExpr->pLeft);
      dest = sqlite3VdbeCurrentAddr(v) + 2;
      sqlite3VdbeAddOp(v, op, 1, dest);
      sqlite3VdbeAddOp(v, OP_AddImm, -1, 0);
      stackChng = 0;
      break;
    }
    case TK_AGG_FUNCTION: {
      AggInfo *pInfo = pExpr->pAggInfo;
      sqlite3VdbeAddOp(v, OP_MemLoad, pInfo->aFunc[pExpr->iAgg].iMem, 0);
      break;
    }

        }
      }
      if( pDef->needCollSeq ){
        if( !pColl ) pColl = pParse->db->pDfltColl; 
        sqlite3VdbeOp3(v, OP_CollSeq, 0, 0, (char *)pColl, P3_COLLSEQ);
      }
      sqlite3VdbeOp3(v, OP_Function, constMask, nExpr, (char*)pDef, P3_FUNCDEF);
      stackChng = 1-nExpr;
      break;
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case TK_EXISTS:
    case TK_SELECT: {
      sqlite3CodeSubselect(pParse, pExpr);
      sqlite3VdbeAddOp(v, OP_MemLoad, pExpr->iColumn, 0);

      codeCompare(pParse, pLeft, pRight, OP_Le, 0, 0);
      sqlite3VdbeAddOp(v, OP_And, 0, 0);
      break;
    }
    case TK_UPLUS:
    case TK_AS: {
      sqlite3ExprCode(pParse, pExpr->pLeft);
      stackChng = 0;
      break;
    }
    case TK_CASE: {
      int expr_end_label;
      int jumpInst;
      int nExpr;
      int i;

                        pExpr->token.z, pExpr->token.n);
      } else {
         assert( pExpr->iColumn == OE_Ignore );
         sqlite3VdbeAddOp(v, OP_ContextPop, 0, 0);
         sqlite3VdbeAddOp(v, OP_Goto, 0, pParse->trigStack->ignoreJump);
         VdbeComment((v, "# raise(IGNORE)"));
      }
      stackChng = 0;
      break;
    }
#endif
  }

  if( pParse->ckOffset ){
    pParse->ckOffset += stackChng;
    assert( pParse->ckOffset );
  }
}

#ifndef SQLITE_OMIT_TRIGGER
/*
** Generate code that evalutes the given expression and leaves the result
** on the stack.  See also sqlite3ExprCode().

** operation.  Special comments in vdbe.c and the mkopcodeh.awk script in
** the make process cause these values to align.  Assert()s in the code
** below verify that the numbers are aligned correctly.
*/
void sqlite3ExprIfTrue(Parse *pParse, Expr *pExpr, int dest, int jumpIfNull){
  Vdbe *v = pParse->pVdbe;
  int op = 0;
  int ckOffset = pParse->ckOffset;
  if( v==0 || pExpr==0 ) return;
  op = pExpr->op;
  switch( op ){
    case TK_AND: {
      int d2 = sqlite3VdbeMakeLabel(v);
      sqlite3ExprIfFalse(pParse, pExpr->pLeft, d2, !jumpIfNull);
      sqlite3ExprIfTrue(pParse, pExpr->pRight, dest, jumpIfNull);

    }
    default: {
      sqlite3ExprCode(pParse, pExpr);
      sqlite3VdbeAddOp(v, OP_If, jumpIfNull, dest);
      break;
    }
  }
  pParse->ckOffset = ckOffset;
}

/*
** Generate code for a boolean expression such that a jump is made
** to the label "dest" if the expression is false but execution
** continues straight thru if the expression is true.
**
** If the expression evaluates to NULL (neither true nor false) then
** jump if jumpIfNull is true or fall through if jumpIfNull is false.
*/
void sqlite3ExprIfFalse(Parse *pParse, Expr *pExpr, int dest, int jumpIfNull){
  Vdbe *v = pParse->pVdbe;
  int op = 0;
  int ckOffset = pParse->ckOffset;
  if( v==0 || pExpr==0 ) return;

  /* The value of pExpr->op and op are related as follows:
  **
  **       pExpr->op            op
  **       ---------          ----------
  **       TK_ISNULL          OP_NotNull

    }
    default: {
      sqlite3ExprCode(pParse, pExpr);
      sqlite3VdbeAddOp(v, OP_IfNot, jumpIfNull, dest);
      break;
    }
  }
  pParse->ckOffset = ckOffset;
}

/*
** Do a deep comparison of two expression trees.  Return TRUE (non-zero)
** if they are identical and return FALSE if they differ in any way.
*/
int sqlite3ExprCompare(Expr *pA, Expr *pB){

**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.145 2005/11/03 00:41:17 drh Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:

      }
    }
    sqlite3VdbeJumpHere(v, addr);
  }

  /* Test all CHECK constraints
  */
#ifndef SQLITE_OMIT_CHECK
  if( pTab->pCheck ){
    int allOk = sqlite3VdbeMakeLabel(v);
    assert( pParse->ckOffset==0 );
    pParse->ckOffset = nCol;
    sqlite3ExprIfTrue(pParse, pTab->pCheck, allOk, 0);
    assert( pParse->ckOffset==nCol );
    pParse->ckOffset = 0;
    sqlite3VdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, OE_Abort);
    sqlite3VdbeResolveLabel(v, allOk);
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* If we have an INTEGER PRIMARY KEY, make sure the primary key
  ** of the new record does not previously exist.  Except, if this
  ** is an UPDATE and the primary key is not changing, that is OK.
  */
  if( rowidChng ){
    onError = pTab->keyConf;

**
*************************************************************************
** 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.181 2005/11/03 00:41:17 drh Exp $
*/

// All token codes are small integers with #defines that begin with "TK_"
%token_prefix TK_

// The type of the data attached to each token is Token.  This is also the
// default type for non-terminals.

// UNIQUE constraints.
//
ccons ::= NULL onconf.
ccons ::= NOT NULL onconf(R).               {sqlite3AddNotNull(pParse, R);}
ccons ::= PRIMARY KEY sortorder onconf(R) autoinc(I).
                                     {sqlite3AddPrimaryKey(pParse,0,R,I);}
ccons ::= UNIQUE onconf(R).          {sqlite3CreateIndex(pParse,0,0,0,0,R,0,0);}
ccons ::= CHECK LP expr(X) RP.       {sqlite3AddCheckConstraint(pParse, X);}
ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
                                {sqlite3CreateForeignKey(pParse,0,&T,TA,R);}
ccons ::= defer_subclause(D).   {sqlite3DeferForeignKey(pParse,D);}
ccons ::= COLLATE id(C).  {sqlite3AddCollateType(pParse, C.z, C.n);}

// The optional AUTOINCREMENT keyword
%type autoinc {int}

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


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

  if( prepSelectStmt(pParse, p) ){
    return SQLITE_ERROR;
  }

  /* Resolve the expressions in the LIMIT and OFFSET clauses. These
  ** are not allowed to refer to any names, so pass an empty NameContext.
  */
  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;







  if( sqlite3ExprResolveNames(&sNC, p->pLimit) ||
      sqlite3ExprResolveNames(&sNC, p->pOffset) ){
    return SQLITE_ERROR;
  }

  /* Set up the local name-context to pass to ExprResolveNames() to
  ** resolve the expression-list.

/*
** 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.425 2005/11/03 00:41:17 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Many people are failing to set -DNDEBUG=1 when compiling SQLite.
** Setting NDEBUG makes the code smaller and run faster.  So the following

  u8 hasPrimKey;   /* True if there exists a primary key */
  u8 keyConf;      /* What to do in case of uniqueness conflict on iPKey */
  u8 autoInc;      /* True if the integer primary key is autoincrement */
  int nRef;          /* Number of pointers to this Table */
  Trigger *pTrigger; /* List of SQL triggers on this table */
  FKey *pFKey;       /* Linked list of all foreign keys in this table */
  char *zColAff;     /* String defining the affinity of each column */
#ifndef SQLITE_OMIT_CHECK
  Expr *pCheck;      /* The AND of all CHECK constraints */
#endif
#ifndef SQLITE_OMIT_ALTERTABLE
  int addColOffset;  /* Offset in CREATE TABLE statement to add a new column */
#endif
};

/*
** Each foreign key constraint is an instance of the following structure.

  u8 nameClash;        /* A permanent table name clashes with temp table name */
  u8 checkSchema;      /* Causes schema cookie check after an error */
  u8 nested;           /* Number of nested calls to the parser/code generator */
  int nErr;            /* Number of errors seen */
  int nTab;            /* Number of previously allocated VDBE cursors */
  int nMem;            /* Number of memory cells used so far */
  int nSet;            /* Number of sets used so far */
  int ckOffset;        /* Stack offset to data used by CHECK constraints */
  u32 writeMask;       /* Start a write transaction on these databases */
  u32 cookieMask;      /* Bitmask of schema verified databases */
  int cookieGoto;      /* Address of OP_Goto to cookie verifier subroutine */
  int cookieValue[MAX_ATTACHED+2];  /* Values of cookies to verify */

  /* Above is constant between recursions.  Below is reset before and after
  ** each recursion */

void sqlite3CommitInternalChanges(sqlite3*);
Table *sqlite3ResultSetOfSelect(Parse*,char*,Select*);
void sqlite3OpenMasterTable(Vdbe *v, int);
void sqlite3StartTable(Parse*,Token*,Token*,Token*,int,int);
void sqlite3AddColumn(Parse*,Token*);
void sqlite3AddNotNull(Parse*, int);
void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int);
void sqlite3AddCheckConstraint(Parse*, Expr*);
void sqlite3AddColumnType(Parse*,Token*);
void sqlite3AddDefaultValue(Parse*,Expr*);
void sqlite3AddCollateType(Parse*, const char*, int);
void sqlite3EndTable(Parse*,Token*,Token*,Select*);

#ifndef SQLITE_OMIT_VIEW
  void sqlite3CreateView(Parse*,Token*,Token*,Token*,Select*,int);

**    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.163 2005/11/03 00:41:17 drh Exp $
*/
#include "sqliteInt.h"
#include "tcl.h"
#include "os.h"
#include <stdlib.h>
#include <string.h>

#endif

#ifdef SQLITE_OMIT_CAST
  Tcl_SetVar2(interp, "sqlite_options", "cast", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "cast", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_CHECK
  Tcl_SetVar2(interp, "sqlite_options", "check", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "check", "1", TCL_GLOBAL_ONLY);
#endif

#ifdef SQLITE_OMIT_COMPLETE
  Tcl_SetVar2(interp, "sqlite_options", "complete", "0", TCL_GLOBAL_ONLY);
#else
  Tcl_SetVar2(interp, "sqlite_options", "complete", "1", TCL_GLOBAL_ONLY);
#endif

# 2005 November 2
#
# 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 CHECK constraints
#
# $Id: check.test,v 1.1 2005/11/03 00:41:18 drh Exp $

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

# Only run these tests if the build includes support for CHECK constraints
ifcapable !check {
  finish_test
  return
}

do_test check-1.1 {
  execsql {
    CREATE TABLE t1(
      x INTEGER CHECK( x<5 ),
      y REAL CHECK( y>x )
    );
  }
} {}
do_test check-1.2 {
  execsql {
    INSERT INTO t1 VALUES(3,4);
    SELECT * FROM t1;
  }  
} {3 4}
do_test check-1.3 {
  catchsql {
    INSERT INTO t1 VALUES(6,7);
  }
} {1 {constraint failed}}
do_test check-1.4 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4}
do_test check-1.5 {
  catchsql {
    INSERT INTO t1 VALUES(4,3);
  }
} {1 {constraint failed}}
do_test check-1.6 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4}
do_test check-1.7 {
  catchsql {
    INSERT INTO t1 VALUES(NULL,6);
  }
} {1 {constraint failed}}
do_test check-1.8 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4}
do_test check-1.9 {
  catchsql {
    INSERT INTO t1 VALUES(2,NULL);
  }
} {1 {constraint failed}}
do_test check-1.10 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4}
do_test check-1.11 {
  execsql {
    UPDATE t1 SET x=2 WHERE x==3;
    SELECT * FROM t1;
  }
} {2 4}
do_test check-1.12 {
  catchsql {
    UPDATE t1 SET x=7 WHERE x==2
  }
} {1 {constraint failed}}
do_test check-1.13 {
  execsql {
    SELECT * FROM t1;
  }
} {2 4}
do_test check-1.14 {
  catchsql {
    UPDATE t1 SET x=5 WHERE x==2
  }
} {1 {constraint failed}}
do_test check-1.15 {
  execsql {
    SELECT * FROM t1;
  }
} {2 4}
do_test check-1.16 {
  catchsql {
    UPDATE t1 SET x=4, y=11 WHERE x==2
  }
} {0 {}}
do_test check-1.17 {
  execsql {
    SELECT * FROM t1;
  }
} {4 11}

do_test check-2.1 {
  execsql {
    CREATE TABLE t2(
      x INTEGER CHECK( typeof(coalesce(x,0))=="integer" ),
      y REAL CHECK( typeof(coalesce(y,0.1))=="real" ),
      z TEXT CHECK( typeof(coalesce(z,''))=="text" )
    );
  }
} {}
do_test check-2.2 {
  execsql {
    INSERT INTO t2 VALUES(1,2.2,'three');
    SELECT * FROM t2;
  }
} {1 2.2 three}
do_test check-2.3 {
  execsql {
    INSERT INTO t2 VALUES(NULL, NULL, NULL);
    SELECT * FROM t2;
  }
} {1 2.2 three {} {} {}}
do_test check-2.4 {
  catchsql {
    INSERT INTO t2 VALUES(1.1, NULL, NULL);
  }
} {1 {constraint failed}}
do_test check-2.5 {
  catchsql {
    INSERT INTO t2 VALUES(NULL, 5, NULL);
  }
} {1 {constraint failed}}
do_test check-2.6 {
  catchsql {
    INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
  }
} {1 {constraint failed}}

finish_test

#
# Run this Tcl script to generate the lang-*.html files.
#
set rcsid {$Id: lang.tcl,v 1.101 2005/11/03 00:41:18 drh Exp $}
source common.tcl

if {[llength $argv]>0} {
  set outputdir [lindex $argv 0]
} else {
  set outputdir ""
}

<typename> |
<typename> ( <number> ) |
<typename> ( <number> , <number> )
} {column-constraint} {
NOT NULL [ <conflict-clause> ] |
PRIMARY KEY [<sort-order>] [ <conflict-clause> ] [AUTOINCREMENT] |
UNIQUE [ <conflict-clause> ] |
CHECK ( <expr> ) |
DEFAULT <value> |
COLLATE <collation-name>
} {constraint} {
PRIMARY KEY ( <column-list> ) [ <conflict-clause> ] |
UNIQUE ( <column-list> ) [ <conflict-clause> ] |
CHECK ( <expr> )
} {conflict-clause} {
ON CONFLICT <conflict-algorithm>
}

puts {
<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
followed by the name of a new table and a parenthesized list of column

table may have different default conflict resolution algorithms.
If an COPY, INSERT, or UPDATE command specifies a different conflict
resolution algorithm, then that algorithm is used in place of the
default algorithm specified in the CREATE TABLE statement.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.</p>

<p>CHECK constraints are supported as of version 3.3.0.  Prior
to version 3.3.0, CHECK constraints were parsed but not enforced.</p>



<p>There are no arbitrary limits on the number
of columns or on the number of constraints in a table.
The total amount of data in a single row is limited to about
1 megabytes in version 2.8.  In version 3.0 there is no arbitrary
limit on the amount of data in a row.</p>

#
# Run this script to generated a omitted.html output file
#
set rcsid {$Id: omitted.tcl,v 1.10 2005/11/03 00:41:18 drh Exp $}
source common.tcl
header {SQL Features That SQLite Does Not Implement}
puts {
<h2>SQL Features That SQLite Does Not Implement</h2>

<p>
Rather than try to list all the features of SQL92 that SQLite does

proc feature {name desc} {
  puts "<tr><td valign=\"top\"><b><nobr>$name</nobr></b></td>"
  puts "<td width=\"10\">&nbsp;</th>"
  puts "<td valign=\"top\">$desc</td></tr>"
}






feature {FOREIGN KEY constraints} {
  FOREIGN KEY constraints are parsed but are not enforced.
}

feature {Complete trigger support} {
  There is some support for triggers but it is not complete.  Missing
  subfeatures include FOR EACH STATEMENT triggers (currently all triggers