/ Check-in [2313d912]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:First cut at supporting CHECK constraints. Everything appears to work, but much more testing is needed as well as documentation. (CVS 2754)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2313d912baeca0fd516d524f16708953de483729
User & Date: drh 2005-11-03 00:41:17
Context
2005-11-03
01:22
CHECK constraints that evaluate to NULL pass. (CVS 2755) check-in: 55b314a2 user: drh tags: trunk
00:41
First cut at supporting CHECK constraints. Everything appears to work, but much more testing is needed as well as documentation. (CVS 2754) check-in: 2313d912 user: drh tags: trunk
2005-11-01
15:48
Omit the SQLITE_AFF_INTEGER type affinity. All numeric values are now of type real, though an integer representation is still sometimes used internally for efficiency. (CVS 2753) check-in: e0d6f61c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

    18     18   **     CREATE INDEX
    19     19   **     DROP INDEX
    20     20   **     creating ID lists
    21     21   **     BEGIN TRANSACTION
    22     22   **     COMMIT
    23     23   **     ROLLBACK
    24     24   **
    25         -** $Id: build.c,v 1.352 2005/11/01 15:48:24 drh Exp $
           25  +** $Id: build.c,v 1.353 2005/11/03 00:41:17 drh Exp $
    26     26   */
    27     27   #include "sqliteInt.h"
    28     28   #include <ctype.h>
    29     29   
    30     30   /*
    31     31   ** This routine is called when a new SQL statement is beginning to
    32     32   ** be parsed.  Initialize the pParse structure as needed.
................................................................................
   452    452   
   453    453     /* Delete the Table structure itself.
   454    454     */
   455    455     sqliteResetColumnNames(pTable);
   456    456     sqliteFree(pTable->zName);
   457    457     sqliteFree(pTable->zColAff);
   458    458     sqlite3SelectDelete(pTable->pSelect);
          459  +#ifndef SQLITE_OMIT_CHECK
          460  +  sqlite3ExprDelete(pTable->pCheck);
          461  +#endif
   459    462     sqliteFree(pTable);
   460    463   }
   461    464   
   462    465   /*
   463    466   ** Unlink the given table from the hash tables and the delete the
   464    467   ** table structure with all its indices and foreign keys.
   465    468   */
................................................................................
  1042   1045       pList = 0;
  1043   1046     }
  1044   1047   
  1045   1048   primary_key_exit:
  1046   1049     sqlite3ExprListDelete(pList);
  1047   1050     return;
  1048   1051   }
         1052  +
         1053  +/*
         1054  +** Add a new CHECK constraint to the table currently under construction.
         1055  +*/
         1056  +void sqlite3AddCheckConstraint(
         1057  +  Parse *pParse,    /* Parsing context */
         1058  +  Expr *pCheckExpr  /* The check expression */
         1059  +){
         1060  +#ifndef SQLITE_OMIT_CHECK
         1061  +  Table *pTab = pParse->pNewTable;
         1062  +  if( pTab ){
         1063  +    /* The CHECK expression must be duplicated so that tokens refer
         1064  +    ** to malloced space and not the (ephemeral) text of the CREATE TABLE
         1065  +    ** statement */
         1066  +    pTab->pCheck = sqlite3ExprAnd(pTab->pCheck, sqlite3ExprDup(pCheckExpr));
         1067  +  }
         1068  +#endif
         1069  +  sqlite3ExprDelete(pCheckExpr);
         1070  +}
  1049   1071   
  1050   1072   /*
  1051   1073   ** Set the collation function of the most recently parsed table column
  1052   1074   ** to the CollSeq given.
  1053   1075   */
  1054   1076   void sqlite3AddCollateType(Parse *pParse, const char *zType, int nType){
  1055   1077     Table *p;
................................................................................
  1266   1288   
  1267   1289     if( (pEnd==0 && pSelect==0) || pParse->nErr || sqlite3_malloc_failed ) return;
  1268   1290     p = pParse->pNewTable;
  1269   1291     if( p==0 ) return;
  1270   1292   
  1271   1293     assert( !db->init.busy || !pSelect );
  1272   1294   
         1295  +#ifndef SQLITE_OMIT_CHECK
         1296  +  /* Resolve names in all CHECK constraint expressions.
         1297  +  */
         1298  +  if( p->pCheck ){
         1299  +    SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
         1300  +    NameContext sNC;                /* Name context for pParse->pNewTable */
         1301  +
         1302  +    memset(&sNC, 0, sizeof(sNC));
         1303  +    memset(&sSrc, 0, sizeof(sSrc));
         1304  +    sSrc.nSrc = 1;
         1305  +    sSrc.a[0].zName = p->zName;
         1306  +    sSrc.a[0].pTab = p;
         1307  +    sSrc.a[0].iCursor = -1;
         1308  +    sNC.pParse = pParse;
         1309  +    sNC.pSrcList = &sSrc;
         1310  +    if( sqlite3ExprResolveNames(&sNC, p->pCheck) ){
         1311  +      return;
         1312  +    }
         1313  +  }
         1314  +#endif /* !defined(SQLITE_OMIT_CHECK) */
         1315  +
  1273   1316     /* If the db->init.busy is 1 it means we are reading the SQL off the
  1274   1317     ** "sqlite_master" or "sqlite_temp_master" table on the disk.
  1275   1318     ** So do not write to the disk again.  Extract the root page number
  1276   1319     ** for the table from the db->init.newTnum field.  (The page number
  1277   1320     ** should have been put there by the sqliteOpenCb routine.)
  1278   1321     */
  1279   1322     if( db->init.busy ){

Changes to src/expr.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains routines used for analyzing expressions and
    13     13   ** for generating VDBE code that evaluates expressions in SQLite.
    14     14   **
    15         -** $Id: expr.c,v 1.232 2005/11/01 15:48:24 drh Exp $
           15  +** $Id: expr.c,v 1.233 2005/11/03 00:41:17 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   #include <ctype.h>
    19     19   
    20     20   /*
    21     21   ** Return the 'affinity' of the expression pExpr if any.
    22     22   **
................................................................................
   798    798   ** means that the form of the name is Z and that columns from any table
   799    799   ** can be used.
   800    800   **
   801    801   ** If the name cannot be resolved unambiguously, leave an error message
   802    802   ** in pParse and return non-zero.  Return zero on success.
   803    803   */
   804    804   static int lookupName(
   805         -  Parse *pParse,      /* The parsing context */
          805  +  Parse *pParse,       /* The parsing context */
   806    806     Token *pDbToken,     /* Name of the database containing table, or NULL */
   807    807     Token *pTableToken,  /* Name of table containing column, or NULL */
   808    808     Token *pColumnToken, /* Name of the column. */
   809    809     NameContext *pNC,    /* The name context used to resolve the name */
   810    810     Expr *pExpr          /* Make this EXPR node point to the selected column */
   811    811   ){
   812    812     char *zDb = 0;       /* Name of the database.  The "X" in X.Y.Z */
................................................................................
   826    826     zCol = sqlite3NameFromToken(pColumnToken);
   827    827     if( sqlite3_malloc_failed ){
   828    828       goto lookupname_end;
   829    829     }
   830    830   
   831    831     pExpr->iTable = -1;
   832    832     while( pNC && cnt==0 ){
          833  +    ExprList *pEList;
   833    834       SrcList *pSrcList = pNC->pSrcList;
   834         -    ExprList *pEList = pNC->pEList;
   835    835   
   836         -    /* assert( zTab==0 || pEList==0 ); */
   837    836       if( pSrcList ){
   838    837         for(i=0, pItem=pSrcList->a; i<pSrcList->nSrc; i++, pItem++){
   839    838           Table *pTab = pItem->pTab;
   840    839           Column *pCol;
   841    840     
   842    841           if( pTab==0 ) continue;
   843    842           assert( pTab->nCol>0 );
................................................................................
   948    947       **     SELECT a+b AS x FROM table WHERE x<10;
   949    948       **
   950    949       ** In cases like this, replace pExpr with a copy of the expression that
   951    950       ** forms the result set entry ("a+b" in the example) and return immediately.
   952    951       ** Note that the expression in the result set should have already been
   953    952       ** resolved by the time the WHERE clause is resolved.
   954    953       */
   955         -    if( cnt==0 && pEList!=0 && zTab==0 ){
          954  +    if( cnt==0 && (pEList = pNC->pEList)!=0 && zTab==0 ){
   956    955         for(j=0; j<pEList->nExpr; j++){
   957    956           char *zAs = pEList->a[j].zName;
   958    957           if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
   959    958             assert( pExpr->pLeft==0 && pExpr->pRight==0 );
   960    959             pExpr->op = TK_AS;
   961    960             pExpr->iColumn = j;
   962    961             pExpr->pLeft = sqlite3ExprDup(pEList->a[j].pExpr);
................................................................................
  1077   1076     assert( pNC!=0 );
  1078   1077     pSrcList = pNC->pSrcList;
  1079   1078     pParse = pNC->pParse;
  1080   1079   
  1081   1080     if( ExprHasAnyProperty(pExpr, EP_Resolved) ) return 1;
  1082   1081     ExprSetProperty(pExpr, EP_Resolved);
  1083   1082   #ifndef NDEBUG
  1084         -  if( pSrcList ){
         1083  +  if( pSrcList && pSrcList->nAlloc>0 ){
  1085   1084       int i;
  1086   1085       for(i=0; i<pSrcList->nSrc; i++){
  1087   1086         assert( pSrcList->a[i].iCursor>=0 && pSrcList->a[i].iCursor<pParse->nTab);
  1088   1087       }
  1089   1088     }
  1090   1089   #endif
  1091   1090     switch( pExpr->op ){
................................................................................
  1437   1436   ** operation.  Special comments in vdbe.c and the mkopcodeh.awk script in
  1438   1437   ** the make process cause these values to align.  Assert()s in the code
  1439   1438   ** below verify that the numbers are aligned correctly.
  1440   1439   */
  1441   1440   void sqlite3ExprCode(Parse *pParse, Expr *pExpr){
  1442   1441     Vdbe *v = pParse->pVdbe;
  1443   1442     int op;
         1443  +  int stackChng = 1;    /* Amount of change to stack depth */
         1444  +
  1444   1445     if( v==0 ) return;
  1445   1446     if( pExpr==0 ){
  1446   1447       sqlite3VdbeAddOp(v, OP_Null, 0, 0);
  1447   1448       return;
  1448   1449     }
  1449   1450     op = pExpr->op;
  1450   1451     switch( op ){
................................................................................
  1458   1459           sqlite3VdbeAddOp(v, OP_Column, pAggInfo->sortingIdx,
  1459   1460                                 pCol->iSorterColumn);
  1460   1461           break;
  1461   1462         }
  1462   1463         /* Otherwise, fall thru into the TK_COLUMN case */
  1463   1464       }
  1464   1465       case TK_COLUMN: {
  1465         -      if( pExpr->iColumn>=0 ){
         1466  +      if( pExpr->iTable<0 ){
         1467  +        /* This only happens when coding check constraints */
         1468  +        assert( pParse->ckOffset>0 );
         1469  +        sqlite3VdbeAddOp(v, OP_Dup, pParse->ckOffset-pExpr->iColumn-1, 1);
         1470  +      }else if( pExpr->iColumn>=0 ){
  1466   1471           sqlite3VdbeAddOp(v, OP_Column, pExpr->iTable, pExpr->iColumn);
  1467   1472           sqlite3ColumnDefault(v, pExpr->pTab, pExpr->iColumn);
  1468   1473         }else{
  1469   1474           sqlite3VdbeAddOp(v, OP_Rowid, pExpr->iTable, 0);
  1470   1475         }
  1471   1476         break;
  1472   1477       }
................................................................................
  1521   1526         switch( aff ){
  1522   1527           case SQLITE_AFF_INTEGER:   op = OP_ToInt;      break;
  1523   1528           case SQLITE_AFF_NUMERIC:   op = OP_ToNumeric;  break;
  1524   1529           case SQLITE_AFF_TEXT:      op = OP_ToText;     break;
  1525   1530           case SQLITE_AFF_NONE:      op = OP_ToBlob;     break;
  1526   1531         }
  1527   1532         sqlite3VdbeAddOp(v, op, 0, 0);
         1533  +      stackChng = 0;
  1528   1534         break;
  1529   1535       }
  1530   1536   #endif /* SQLITE_OMIT_CAST */
  1531   1537       case TK_LT:
  1532   1538       case TK_LE:
  1533   1539       case TK_GT:
  1534   1540       case TK_GE:
................................................................................
  1539   1545         assert( TK_GT==OP_Gt );
  1540   1546         assert( TK_GE==OP_Ge );
  1541   1547         assert( TK_EQ==OP_Eq );
  1542   1548         assert( TK_NE==OP_Ne );
  1543   1549         sqlite3ExprCode(pParse, pExpr->pLeft);
  1544   1550         sqlite3ExprCode(pParse, pExpr->pRight);
  1545   1551         codeCompare(pParse, pExpr->pLeft, pExpr->pRight, op, 0, 0);
         1552  +      stackChng = -1;
  1546   1553         break;
  1547   1554       }
  1548   1555       case TK_AND:
  1549   1556       case TK_OR:
  1550   1557       case TK_PLUS:
  1551   1558       case TK_STAR:
  1552   1559       case TK_MINUS:
................................................................................
  1567   1574         assert( TK_SLASH==OP_Divide );
  1568   1575         assert( TK_LSHIFT==OP_ShiftLeft );
  1569   1576         assert( TK_RSHIFT==OP_ShiftRight );
  1570   1577         assert( TK_CONCAT==OP_Concat );
  1571   1578         sqlite3ExprCode(pParse, pExpr->pLeft);
  1572   1579         sqlite3ExprCode(pParse, pExpr->pRight);
  1573   1580         sqlite3VdbeAddOp(v, op, 0, 0);
         1581  +      stackChng = -1;
  1574   1582         break;
  1575   1583       }
  1576   1584       case TK_UMINUS: {
  1577   1585         Expr *pLeft = pExpr->pLeft;
  1578   1586         assert( pLeft );
  1579   1587         if( pLeft->op==TK_FLOAT || pLeft->op==TK_INTEGER ){
  1580   1588           Token *p = &pLeft->token;
................................................................................
  1592   1600       }
  1593   1601       case TK_BITNOT:
  1594   1602       case TK_NOT: {
  1595   1603         assert( TK_BITNOT==OP_BitNot );
  1596   1604         assert( TK_NOT==OP_Not );
  1597   1605         sqlite3ExprCode(pParse, pExpr->pLeft);
  1598   1606         sqlite3VdbeAddOp(v, op, 0, 0);
         1607  +      stackChng = 0;
  1599   1608         break;
  1600   1609       }
  1601   1610       case TK_ISNULL:
  1602   1611       case TK_NOTNULL: {
  1603   1612         int dest;
  1604   1613         assert( TK_ISNULL==OP_IsNull );
  1605   1614         assert( TK_NOTNULL==OP_NotNull );
  1606   1615         sqlite3VdbeAddOp(v, OP_Integer, 1, 0);
  1607   1616         sqlite3ExprCode(pParse, pExpr->pLeft);
  1608   1617         dest = sqlite3VdbeCurrentAddr(v) + 2;
  1609   1618         sqlite3VdbeAddOp(v, op, 1, dest);
  1610   1619         sqlite3VdbeAddOp(v, OP_AddImm, -1, 0);
         1620  +      stackChng = 0;
  1611   1621         break;
  1612   1622       }
  1613   1623       case TK_AGG_FUNCTION: {
  1614   1624         AggInfo *pInfo = pExpr->pAggInfo;
  1615   1625         sqlite3VdbeAddOp(v, OP_MemLoad, pInfo->aFunc[pExpr->iAgg].iMem, 0);
  1616   1626         break;
  1617   1627       }
................................................................................
  1640   1650           }
  1641   1651         }
  1642   1652         if( pDef->needCollSeq ){
  1643   1653           if( !pColl ) pColl = pParse->db->pDfltColl; 
  1644   1654           sqlite3VdbeOp3(v, OP_CollSeq, 0, 0, (char *)pColl, P3_COLLSEQ);
  1645   1655         }
  1646   1656         sqlite3VdbeOp3(v, OP_Function, constMask, nExpr, (char*)pDef, P3_FUNCDEF);
         1657  +      stackChng = 1-nExpr;
  1647   1658         break;
  1648   1659       }
  1649   1660   #ifndef SQLITE_OMIT_SUBQUERY
  1650   1661       case TK_EXISTS:
  1651   1662       case TK_SELECT: {
  1652   1663         sqlite3CodeSubselect(pParse, pExpr);
  1653   1664         sqlite3VdbeAddOp(v, OP_MemLoad, pExpr->iColumn, 0);
................................................................................
  1698   1709         codeCompare(pParse, pLeft, pRight, OP_Le, 0, 0);
  1699   1710         sqlite3VdbeAddOp(v, OP_And, 0, 0);
  1700   1711         break;
  1701   1712       }
  1702   1713       case TK_UPLUS:
  1703   1714       case TK_AS: {
  1704   1715         sqlite3ExprCode(pParse, pExpr->pLeft);
         1716  +      stackChng = 0;
  1705   1717         break;
  1706   1718       }
  1707   1719       case TK_CASE: {
  1708   1720         int expr_end_label;
  1709   1721         int jumpInst;
  1710   1722         int nExpr;
  1711   1723         int i;
................................................................................
  1763   1775                           pExpr->token.z, pExpr->token.n);
  1764   1776         } else {
  1765   1777            assert( pExpr->iColumn == OE_Ignore );
  1766   1778            sqlite3VdbeAddOp(v, OP_ContextPop, 0, 0);
  1767   1779            sqlite3VdbeAddOp(v, OP_Goto, 0, pParse->trigStack->ignoreJump);
  1768   1780            VdbeComment((v, "# raise(IGNORE)"));
  1769   1781         }
         1782  +      stackChng = 0;
         1783  +      break;
  1770   1784       }
  1771   1785   #endif
  1772         -    break;
         1786  +  }
         1787  +
         1788  +  if( pParse->ckOffset ){
         1789  +    pParse->ckOffset += stackChng;
         1790  +    assert( pParse->ckOffset );
  1773   1791     }
  1774   1792   }
  1775   1793   
  1776   1794   #ifndef SQLITE_OMIT_TRIGGER
  1777   1795   /*
  1778   1796   ** Generate code that evalutes the given expression and leaves the result
  1779   1797   ** on the stack.  See also sqlite3ExprCode().
................................................................................
  1833   1851   ** operation.  Special comments in vdbe.c and the mkopcodeh.awk script in
  1834   1852   ** the make process cause these values to align.  Assert()s in the code
  1835   1853   ** below verify that the numbers are aligned correctly.
  1836   1854   */
  1837   1855   void sqlite3ExprIfTrue(Parse *pParse, Expr *pExpr, int dest, int jumpIfNull){
  1838   1856     Vdbe *v = pParse->pVdbe;
  1839   1857     int op = 0;
         1858  +  int ckOffset = pParse->ckOffset;
  1840   1859     if( v==0 || pExpr==0 ) return;
  1841   1860     op = pExpr->op;
  1842   1861     switch( op ){
  1843   1862       case TK_AND: {
  1844   1863         int d2 = sqlite3VdbeMakeLabel(v);
  1845   1864         sqlite3ExprIfFalse(pParse, pExpr->pLeft, d2, !jumpIfNull);
  1846   1865         sqlite3ExprIfTrue(pParse, pExpr->pRight, dest, jumpIfNull);
................................................................................
  1907   1926       }
  1908   1927       default: {
  1909   1928         sqlite3ExprCode(pParse, pExpr);
  1910   1929         sqlite3VdbeAddOp(v, OP_If, jumpIfNull, dest);
  1911   1930         break;
  1912   1931       }
  1913   1932     }
         1933  +  pParse->ckOffset = ckOffset;
  1914   1934   }
  1915   1935   
  1916   1936   /*
  1917   1937   ** Generate code for a boolean expression such that a jump is made
  1918   1938   ** to the label "dest" if the expression is false but execution
  1919   1939   ** continues straight thru if the expression is true.
  1920   1940   **
  1921   1941   ** If the expression evaluates to NULL (neither true nor false) then
  1922   1942   ** jump if jumpIfNull is true or fall through if jumpIfNull is false.
  1923   1943   */
  1924   1944   void sqlite3ExprIfFalse(Parse *pParse, Expr *pExpr, int dest, int jumpIfNull){
  1925   1945     Vdbe *v = pParse->pVdbe;
  1926   1946     int op = 0;
         1947  +  int ckOffset = pParse->ckOffset;
  1927   1948     if( v==0 || pExpr==0 ) return;
  1928   1949   
  1929   1950     /* The value of pExpr->op and op are related as follows:
  1930   1951     **
  1931   1952     **       pExpr->op            op
  1932   1953     **       ---------          ----------
  1933   1954     **       TK_ISNULL          OP_NotNull
................................................................................
  2016   2037       }
  2017   2038       default: {
  2018   2039         sqlite3ExprCode(pParse, pExpr);
  2019   2040         sqlite3VdbeAddOp(v, OP_IfNot, jumpIfNull, dest);
  2020   2041         break;
  2021   2042       }
  2022   2043     }
         2044  +  pParse->ckOffset = ckOffset;
  2023   2045   }
  2024   2046   
  2025   2047   /*
  2026   2048   ** Do a deep comparison of two expression trees.  Return TRUE (non-zero)
  2027   2049   ** if they are identical and return FALSE if they differ in any way.
  2028   2050   */
  2029   2051   int sqlite3ExprCompare(Expr *pA, Expr *pB){

Changes to src/insert.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle INSERT statements in SQLite.
    14     14   **
    15         -** $Id: insert.c,v 1.144 2005/11/01 15:48:24 drh Exp $
           15  +** $Id: insert.c,v 1.145 2005/11/03 00:41:17 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** Set P3 of the most recently inserted opcode to a column affinity
    21     21   ** string for index pIdx. A column affinity string has one character
    22     22   ** for each column in the table, according to the affinity of the column:
................................................................................
   864    864         }
   865    865       }
   866    866       sqlite3VdbeJumpHere(v, addr);
   867    867     }
   868    868   
   869    869     /* Test all CHECK constraints
   870    870     */
   871         -  /**** TBD ****/
          871  +#ifndef SQLITE_OMIT_CHECK
          872  +  if( pTab->pCheck ){
          873  +    int allOk = sqlite3VdbeMakeLabel(v);
          874  +    assert( pParse->ckOffset==0 );
          875  +    pParse->ckOffset = nCol;
          876  +    sqlite3ExprIfTrue(pParse, pTab->pCheck, allOk, 0);
          877  +    assert( pParse->ckOffset==nCol );
          878  +    pParse->ckOffset = 0;
          879  +    sqlite3VdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, OE_Abort);
          880  +    sqlite3VdbeResolveLabel(v, allOk);
          881  +  }
          882  +#endif /* !defined(SQLITE_OMIT_CHECK) */
   872    883   
   873    884     /* If we have an INTEGER PRIMARY KEY, make sure the primary key
   874    885     ** of the new record does not previously exist.  Except, if this
   875    886     ** is an UPDATE and the primary key is not changing, that is OK.
   876    887     */
   877    888     if( rowidChng ){
   878    889       onError = pTab->keyConf;

Changes to src/parse.y.

    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains SQLite's grammar for SQL.  Process this file
    13     13   ** using the lemon parser generator to generate C code that runs
    14     14   ** the parser.  Lemon will also generate a header file containing
    15     15   ** numeric codes for all of the tokens.
    16     16   **
    17         -** @(#) $Id: parse.y,v 1.180 2005/09/16 02:38:10 drh Exp $
           17  +** @(#) $Id: parse.y,v 1.181 2005/11/03 00:41:17 drh Exp $
    18     18   */
    19     19   
    20     20   // All token codes are small integers with #defines that begin with "TK_"
    21     21   %token_prefix TK_
    22     22   
    23     23   // The type of the data attached to each token is Token.  This is also the
    24     24   // default type for non-terminals.
................................................................................
   264    264   // UNIQUE constraints.
   265    265   //
   266    266   ccons ::= NULL onconf.
   267    267   ccons ::= NOT NULL onconf(R).               {sqlite3AddNotNull(pParse, R);}
   268    268   ccons ::= PRIMARY KEY sortorder onconf(R) autoinc(I).
   269    269                                        {sqlite3AddPrimaryKey(pParse,0,R,I);}
   270    270   ccons ::= UNIQUE onconf(R).          {sqlite3CreateIndex(pParse,0,0,0,0,R,0,0);}
   271         -ccons ::= CHECK LP expr(X) RP onconf. {sqlite3ExprDelete(X);}
          271  +ccons ::= CHECK LP expr(X) RP.       {sqlite3AddCheckConstraint(pParse, X);}
   272    272   ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
   273    273                                   {sqlite3CreateForeignKey(pParse,0,&T,TA,R);}
   274    274   ccons ::= defer_subclause(D).   {sqlite3DeferForeignKey(pParse,D);}
   275    275   ccons ::= COLLATE id(C).  {sqlite3AddCollateType(pParse, C.z, C.n);}
   276    276   
   277    277   // The optional AUTOINCREMENT keyword
   278    278   %type autoinc {int}

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.277 2005/10/06 16:53:15 drh Exp $
           15  +** $Id: select.c,v 1.278 2005/11/03 00:41:17 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Allocate a new Select structure and return a pointer to that
    22     22   ** structure.
................................................................................
  2380   2380     if( prepSelectStmt(pParse, p) ){
  2381   2381       return SQLITE_ERROR;
  2382   2382     }
  2383   2383   
  2384   2384     /* Resolve the expressions in the LIMIT and OFFSET clauses. These
  2385   2385     ** are not allowed to refer to any names, so pass an empty NameContext.
  2386   2386     */
         2387  +  memset(&sNC, 0, sizeof(sNC));
  2387   2388     sNC.pParse = pParse;
  2388         -  sNC.hasAgg = 0;
  2389         -  sNC.nErr = 0;
  2390         -  sNC.nRef = 0;
  2391         -  sNC.pEList = 0;
  2392         -  sNC.allowAgg = 0;
  2393         -  sNC.pSrcList = 0;
  2394         -  sNC.pNext = 0;
  2395   2389     if( sqlite3ExprResolveNames(&sNC, p->pLimit) ||
  2396   2390         sqlite3ExprResolveNames(&sNC, p->pOffset) ){
  2397   2391       return SQLITE_ERROR;
  2398   2392     }
  2399   2393   
  2400   2394     /* Set up the local name-context to pass to ExprResolveNames() to
  2401   2395     ** resolve the expression-list.

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.424 2005/11/01 15:48:24 drh Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.425 2005/11/03 00:41:17 drh Exp $
    15     15   */
    16     16   #ifndef _SQLITEINT_H_
    17     17   #define _SQLITEINT_H_
    18     18   
    19     19   /*
    20     20   ** Many people are failing to set -DNDEBUG=1 when compiling SQLite.
    21     21   ** Setting NDEBUG makes the code smaller and run faster.  So the following
................................................................................
   635    635     u8 hasPrimKey;   /* True if there exists a primary key */
   636    636     u8 keyConf;      /* What to do in case of uniqueness conflict on iPKey */
   637    637     u8 autoInc;      /* True if the integer primary key is autoincrement */
   638    638     int nRef;          /* Number of pointers to this Table */
   639    639     Trigger *pTrigger; /* List of SQL triggers on this table */
   640    640     FKey *pFKey;       /* Linked list of all foreign keys in this table */
   641    641     char *zColAff;     /* String defining the affinity of each column */
          642  +#ifndef SQLITE_OMIT_CHECK
          643  +  Expr *pCheck;      /* The AND of all CHECK constraints */
          644  +#endif
   642    645   #ifndef SQLITE_OMIT_ALTERTABLE
   643    646     int addColOffset;  /* Offset in CREATE TABLE statement to add a new column */
   644    647   #endif
   645    648   };
   646    649   
   647    650   /*
   648    651   ** Each foreign key constraint is an instance of the following structure.
................................................................................
  1168   1171     u8 nameClash;        /* A permanent table name clashes with temp table name */
  1169   1172     u8 checkSchema;      /* Causes schema cookie check after an error */
  1170   1173     u8 nested;           /* Number of nested calls to the parser/code generator */
  1171   1174     int nErr;            /* Number of errors seen */
  1172   1175     int nTab;            /* Number of previously allocated VDBE cursors */
  1173   1176     int nMem;            /* Number of memory cells used so far */
  1174   1177     int nSet;            /* Number of sets used so far */
         1178  +  int ckOffset;        /* Stack offset to data used by CHECK constraints */
  1175   1179     u32 writeMask;       /* Start a write transaction on these databases */
  1176   1180     u32 cookieMask;      /* Bitmask of schema verified databases */
  1177   1181     int cookieGoto;      /* Address of OP_Goto to cookie verifier subroutine */
  1178   1182     int cookieValue[MAX_ATTACHED+2];  /* Values of cookies to verify */
  1179   1183   
  1180   1184     /* Above is constant between recursions.  Below is reset before and after
  1181   1185     ** each recursion */
................................................................................
  1445   1449   void sqlite3CommitInternalChanges(sqlite3*);
  1446   1450   Table *sqlite3ResultSetOfSelect(Parse*,char*,Select*);
  1447   1451   void sqlite3OpenMasterTable(Vdbe *v, int);
  1448   1452   void sqlite3StartTable(Parse*,Token*,Token*,Token*,int,int);
  1449   1453   void sqlite3AddColumn(Parse*,Token*);
  1450   1454   void sqlite3AddNotNull(Parse*, int);
  1451   1455   void sqlite3AddPrimaryKey(Parse*, ExprList*, int, int);
         1456  +void sqlite3AddCheckConstraint(Parse*, Expr*);
  1452   1457   void sqlite3AddColumnType(Parse*,Token*);
  1453   1458   void sqlite3AddDefaultValue(Parse*,Expr*);
  1454   1459   void sqlite3AddCollateType(Parse*, const char*, int);
  1455   1460   void sqlite3EndTable(Parse*,Token*,Token*,Select*);
  1456   1461   
  1457   1462   #ifndef SQLITE_OMIT_VIEW
  1458   1463     void sqlite3CreateView(Parse*,Token*,Token*,Token*,Select*,int);

Changes to src/test1.c.

     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Code for testing the printf() interface to SQLite.  This code
    13     13   ** is not included in the SQLite library.  It is used for automated
    14     14   ** testing of the SQLite library.
    15     15   **
    16         -** $Id: test1.c,v 1.162 2005/09/19 13:15:23 drh Exp $
           16  +** $Id: test1.c,v 1.163 2005/11/03 00:41:17 drh Exp $
    17     17   */
    18     18   #include "sqliteInt.h"
    19     19   #include "tcl.h"
    20     20   #include "os.h"
    21     21   #include <stdlib.h>
    22     22   #include <string.h>
    23     23   
................................................................................
  2871   2871   #endif
  2872   2872   
  2873   2873   #ifdef SQLITE_OMIT_CAST
  2874   2874     Tcl_SetVar2(interp, "sqlite_options", "cast", "0", TCL_GLOBAL_ONLY);
  2875   2875   #else
  2876   2876     Tcl_SetVar2(interp, "sqlite_options", "cast", "1", TCL_GLOBAL_ONLY);
  2877   2877   #endif
         2878  +
         2879  +#ifdef SQLITE_OMIT_CHECK
         2880  +  Tcl_SetVar2(interp, "sqlite_options", "check", "0", TCL_GLOBAL_ONLY);
         2881  +#else
         2882  +  Tcl_SetVar2(interp, "sqlite_options", "check", "1", TCL_GLOBAL_ONLY);
         2883  +#endif
  2878   2884   
  2879   2885   #ifdef SQLITE_OMIT_COMPLETE
  2880   2886     Tcl_SetVar2(interp, "sqlite_options", "complete", "0", TCL_GLOBAL_ONLY);
  2881   2887   #else
  2882   2888     Tcl_SetVar2(interp, "sqlite_options", "complete", "1", TCL_GLOBAL_ONLY);
  2883   2889   #endif
  2884   2890   

Added test/check.test.

            1  +# 2005 November 2
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing CHECK constraints
           13  +#
           14  +# $Id: check.test,v 1.1 2005/11/03 00:41:18 drh Exp $
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +
           19  +# Only run these tests if the build includes support for CHECK constraints
           20  +ifcapable !check {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +do_test check-1.1 {
           26  +  execsql {
           27  +    CREATE TABLE t1(
           28  +      x INTEGER CHECK( x<5 ),
           29  +      y REAL CHECK( y>x )
           30  +    );
           31  +  }
           32  +} {}
           33  +do_test check-1.2 {
           34  +  execsql {
           35  +    INSERT INTO t1 VALUES(3,4);
           36  +    SELECT * FROM t1;
           37  +  }  
           38  +} {3 4}
           39  +do_test check-1.3 {
           40  +  catchsql {
           41  +    INSERT INTO t1 VALUES(6,7);
           42  +  }
           43  +} {1 {constraint failed}}
           44  +do_test check-1.4 {
           45  +  execsql {
           46  +    SELECT * FROM t1;
           47  +  }  
           48  +} {3 4}
           49  +do_test check-1.5 {
           50  +  catchsql {
           51  +    INSERT INTO t1 VALUES(4,3);
           52  +  }
           53  +} {1 {constraint failed}}
           54  +do_test check-1.6 {
           55  +  execsql {
           56  +    SELECT * FROM t1;
           57  +  }  
           58  +} {3 4}
           59  +do_test check-1.7 {
           60  +  catchsql {
           61  +    INSERT INTO t1 VALUES(NULL,6);
           62  +  }
           63  +} {1 {constraint failed}}
           64  +do_test check-1.8 {
           65  +  execsql {
           66  +    SELECT * FROM t1;
           67  +  }  
           68  +} {3 4}
           69  +do_test check-1.9 {
           70  +  catchsql {
           71  +    INSERT INTO t1 VALUES(2,NULL);
           72  +  }
           73  +} {1 {constraint failed}}
           74  +do_test check-1.10 {
           75  +  execsql {
           76  +    SELECT * FROM t1;
           77  +  }  
           78  +} {3 4}
           79  +do_test check-1.11 {
           80  +  execsql {
           81  +    UPDATE t1 SET x=2 WHERE x==3;
           82  +    SELECT * FROM t1;
           83  +  }
           84  +} {2 4}
           85  +do_test check-1.12 {
           86  +  catchsql {
           87  +    UPDATE t1 SET x=7 WHERE x==2
           88  +  }
           89  +} {1 {constraint failed}}
           90  +do_test check-1.13 {
           91  +  execsql {
           92  +    SELECT * FROM t1;
           93  +  }
           94  +} {2 4}
           95  +do_test check-1.14 {
           96  +  catchsql {
           97  +    UPDATE t1 SET x=5 WHERE x==2
           98  +  }
           99  +} {1 {constraint failed}}
          100  +do_test check-1.15 {
          101  +  execsql {
          102  +    SELECT * FROM t1;
          103  +  }
          104  +} {2 4}
          105  +do_test check-1.16 {
          106  +  catchsql {
          107  +    UPDATE t1 SET x=4, y=11 WHERE x==2
          108  +  }
          109  +} {0 {}}
          110  +do_test check-1.17 {
          111  +  execsql {
          112  +    SELECT * FROM t1;
          113  +  }
          114  +} {4 11}
          115  +
          116  +do_test check-2.1 {
          117  +  execsql {
          118  +    CREATE TABLE t2(
          119  +      x INTEGER CHECK( typeof(coalesce(x,0))=="integer" ),
          120  +      y REAL CHECK( typeof(coalesce(y,0.1))=="real" ),
          121  +      z TEXT CHECK( typeof(coalesce(z,''))=="text" )
          122  +    );
          123  +  }
          124  +} {}
          125  +do_test check-2.2 {
          126  +  execsql {
          127  +    INSERT INTO t2 VALUES(1,2.2,'three');
          128  +    SELECT * FROM t2;
          129  +  }
          130  +} {1 2.2 three}
          131  +do_test check-2.3 {
          132  +  execsql {
          133  +    INSERT INTO t2 VALUES(NULL, NULL, NULL);
          134  +    SELECT * FROM t2;
          135  +  }
          136  +} {1 2.2 three {} {} {}}
          137  +do_test check-2.4 {
          138  +  catchsql {
          139  +    INSERT INTO t2 VALUES(1.1, NULL, NULL);
          140  +  }
          141  +} {1 {constraint failed}}
          142  +do_test check-2.5 {
          143  +  catchsql {
          144  +    INSERT INTO t2 VALUES(NULL, 5, NULL);
          145  +  }
          146  +} {1 {constraint failed}}
          147  +do_test check-2.6 {
          148  +  catchsql {
          149  +    INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
          150  +  }
          151  +} {1 {constraint failed}}
          152  +
          153  +finish_test

Changes to www/lang.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the lang-*.html files.
     3      3   #
     4         -set rcsid {$Id: lang.tcl,v 1.100 2005/09/11 11:56:28 drh Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.101 2005/11/03 00:41:18 drh Exp $}
     5      5   source common.tcl
     6      6   
     7      7   if {[llength $argv]>0} {
     8      8     set outputdir [lindex $argv 0]
     9      9   } else {
    10     10     set outputdir ""
    11     11   }
................................................................................
   507    507   <typename> |
   508    508   <typename> ( <number> ) |
   509    509   <typename> ( <number> , <number> )
   510    510   } {column-constraint} {
   511    511   NOT NULL [ <conflict-clause> ] |
   512    512   PRIMARY KEY [<sort-order>] [ <conflict-clause> ] [AUTOINCREMENT] |
   513    513   UNIQUE [ <conflict-clause> ] |
   514         -CHECK ( <expr> ) [ <conflict-clause> ] |
          514  +CHECK ( <expr> ) |
   515    515   DEFAULT <value> |
   516    516   COLLATE <collation-name>
   517    517   } {constraint} {
   518    518   PRIMARY KEY ( <column-list> ) [ <conflict-clause> ] |
   519    519   UNIQUE ( <column-list> ) [ <conflict-clause> ] |
   520         -CHECK ( <expr> ) [ <conflict-clause> ]
          520  +CHECK ( <expr> )
   521    521   } {conflict-clause} {
   522    522   ON CONFLICT <conflict-algorithm>
   523    523   }
   524    524   
   525    525   puts {
   526    526   <p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
   527    527   followed by the name of a new table and a parenthesized list of column
................................................................................
   592    592   table may have different default conflict resolution algorithms.
   593    593   If an COPY, INSERT, or UPDATE command specifies a different conflict
   594    594   resolution algorithm, then that algorithm is used in place of the
   595    595   default algorithm specified in the CREATE TABLE statement.
   596    596   See the section titled
   597    597   <a href="#conflict">ON CONFLICT</a> for additional information.</p>
   598    598   
   599         -<p>CHECK constraints are ignored in the current implementation.
   600         -Support for CHECK constraints may be added in the future.  As of
   601         -version 2.3.0, NOT NULL, PRIMARY KEY, and UNIQUE constraints all
   602         -work.</p>
          599  +<p>CHECK constraints are supported as of version 3.3.0.  Prior
          600  +to version 3.3.0, CHECK constraints were parsed but not enforced.</p>
   603    601   
   604    602   <p>There are no arbitrary limits on the number
   605    603   of columns or on the number of constraints in a table.
   606    604   The total amount of data in a single row is limited to about
   607    605   1 megabytes in version 2.8.  In version 3.0 there is no arbitrary
   608    606   limit on the amount of data in a row.</p>
   609    607   

Changes to www/omitted.tcl.

     1      1   #
     2      2   # Run this script to generated a omitted.html output file
     3      3   #
     4         -set rcsid {$Id: omitted.tcl,v 1.9 2005/09/11 11:56:28 drh Exp $}
            4  +set rcsid {$Id: omitted.tcl,v 1.10 2005/11/03 00:41:18 drh Exp $}
     5      5   source common.tcl
     6      6   header {SQL Features That SQLite Does Not Implement}
     7      7   puts {
     8      8   <h2>SQL Features That SQLite Does Not Implement</h2>
     9      9   
    10     10   <p>
    11     11   Rather than try to list all the features of SQL92 that SQLite does
................................................................................
    24     24   
    25     25   proc feature {name desc} {
    26     26     puts "<tr><td valign=\"top\"><b><nobr>$name</nobr></b></td>"
    27     27     puts "<td width=\"10\">&nbsp;</th>"
    28     28     puts "<td valign=\"top\">$desc</td></tr>"
    29     29   }
    30     30   
    31         -feature {CHECK constraints} {
    32         -  CHECK constraints are parsed but they are not enforced.
    33         -  NOT NULL and UNIQUE constraints are enforced, however.
    34         -}
    35         -
    36     31   feature {FOREIGN KEY constraints} {
    37     32     FOREIGN KEY constraints are parsed but are not enforced.
    38     33   }
    39     34   
    40     35   feature {Complete trigger support} {
    41     36     There is some support for triggers but it is not complete.  Missing
    42     37     subfeatures include FOR EACH STATEMENT triggers (currently all triggers