SQLite

**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.354 2005/11/03 02:03:13 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.

    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;
    sNC.isCheck = 1;
    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

**    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.234 2005/11/03 02:03:13 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

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

    }
#ifndef SQLITE_OMIT_SUBQUERY
    case TK_SELECT:
    case TK_EXISTS:
#endif
    case TK_IN: {
      if( pExpr->pSelect ){
#ifndef SQLITE_OMIT_CHECK
        if( pNC->isCheck ){
          sqlite3ErrorMsg(pParse,"subqueries prohibited in CHECK constraints");
        }
#endif
        int nRef = pNC->nRef;
        sqlite3SelectResolve(pParse, pExpr->pSelect, pNC);
        assert( pNC->nRef>=nRef );
        if( nRef!=pNC->nRef ){
          ExprSetProperty(pExpr, EP_VarSelect);
        }
      }

**
*************************************************************************
** 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.182 2005/11/03 02:03:13 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}

conslist ::= conslist tcons.
conslist ::= tcons.
tcons ::= CONSTRAINT nm.
tcons ::= PRIMARY KEY LP idxlist(X) autoinc(I) RP onconf(R).
                                         {sqlite3AddPrimaryKey(pParse,X,R,I);}
tcons ::= UNIQUE LP idxlist(X) RP onconf(R).
                                       {sqlite3CreateIndex(pParse,0,0,0,X,R,0,0);}
tcons ::= CHECK LP expr(E) RP onconf. {sqlite3AddCheckConstraint(pParse,E);}
tcons ::= FOREIGN KEY LP idxlist(FA) RP
          REFERENCES nm(T) idxlist_opt(TA) refargs(R) defer_subclause_opt(D). {
    sqlite3CreateForeignKey(pParse, FA, &T, TA, R);
    sqlite3DeferForeignKey(pParse, D);
}
%type defer_subclause_opt {int}
defer_subclause_opt(A) ::= .                    {A = 0;}

/*
** 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.426 2005/11/03 02:03:13 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

  Parse *pParse;       /* The parser */
  SrcList *pSrcList;   /* One or more tables used to resolve names */
  ExprList *pEList;    /* Optional list of named expressions */
  int nRef;            /* Number of names resolved by this context */
  int nErr;            /* Number of errors encountered while resolving names */
  u8 allowAgg;         /* Aggregate functions allowed here */
  u8 hasAgg;           /* True if aggregates are seen */
  u8 isCheck;          /* True if resolving names in a CHECK constraint */
  int nDepth;          /* Depth of subquery recursion. 1 for no recursion */
  AggInfo *pAggInfo;   /* Information about aggregates at this level */
  NameContext *pNext;  /* Next outer name context.  NULL for outermost */
};

/*
** An instance of the following structure contains all information

# 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.3 2005/11/03 02:03:13 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

  }
} {1 {constraint failed}}
do_test check-2.6 {
  catchsql {
    INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
  }
} {1 {constraint failed}}

do_test check-3.1 {
  catchsql {
    CREATE TABLE t3(
      x, y, z,
      CHECK( x<(SELECT min(x) FROM t1) )
    );
  }
} {1 {subqueries prohibited in CHECK constraints}}
do_test check-3.2 {
  execsql {
    SELECT name FROM sqlite_master ORDER BY name
  }
} {t1 t2}
do_test check-3.3 {
  catchsql {
    CREATE TABLE t3(
      x, y, z,
      CHECK( q<x )
    );
  }
} {1 {no such column: q}}
do_test check-3.4 {
  execsql {
    SELECT name FROM sqlite_master ORDER BY name
  }
} {t1 t2}
do_test check-3.5 {
  catchsql {
    CREATE TABLE t3(
      x, y, z,
      CHECK( t2.x<x )
    );
  }
} {1 {no such column: t2.x}}
do_test check-3.6 {
  execsql {
    SELECT name FROM sqlite_master ORDER BY name
  }
} {t1 t2}
do_test check-3.7 {
  catchsql {
    CREATE TABLE t3(
      x, y, z,
      CHECK( t3.x<25 )
    );
  }
} {0 {}}
do_test check-3.8 {
  execsql {
    INSERT INTO t3 VALUES(1,2,3);
    SELECT * FROM t3;
  }
} {1 2 3}
do_test check-3.9 {
  catchsql {
    INSERT INTO t3 VALUES(111,222,333);
  }
} {1 {constraint failed}}

do_test check-4.1 {
  execsql {
    CREATE TABLE t4(x, y,
      CHECK (
           x+y==11
        OR x*y==12
        OR x/y BETWEEN 5 AND 8
        OR -x==y+10
      )
    );
  }
} {}
do_test check-4.2 {
  execsql {
    INSERT INTO t4 VALUES(1,10);
    SELECT * FROM t4
  }
} {1 10}
do_test check-4.3 {
  execsql {
    UPDATE t4 SET x=4, y=3;
    SELECT * FROM t4
  }
} {4 3}
do_test check-4.3 {
  execsql {
    UPDATE t4 SET x=12, y=2;
    SELECT * FROM t4
  }
} {12 2}
do_test check-4.4 {
  execsql {
    UPDATE t4 SET x=12, y=-22;
    SELECT * FROM t4
  }
} {12 -22}
do_test check-4.5 {
  catchsql {
    UPDATE t4 SET x=0, y=1;
  }
} {1 {constraint failed}}
do_test check-4.6 {
  execsql {
    SELECT * FROM t4;
  }
} {12 -22}

finish_test