/ Check-in [28aed847]
Login

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

Overview
Comment:Parser bug fix: Make sure the table constraints allowed by prior releases can still be parsed, even if they are technically not allowed by the syntax diagram. This is a cherry-pick of [a1c014d8a87c8940b3], [38bf90af1ede6ee64e], and [e536ac041815b118c4].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | apple-osx-ml
Files: files | file ages | folders
SHA1: 28aed847c6a9d5a3eae4627e1c23eb5cb9c3aabe
User & Date: drh 2012-08-07 01:37:15
Context
2012-08-07
01:37
Parser bug fix: Make sure the table constraints allowed by prior releases can still be parsed, even if they are technically not allowed by the syntax diagram. This is a cherry-pick of [a1c014d8a87c8940b3], [38bf90af1ede6ee64e], and [e536ac041815b118c4]. Leaf check-in: 28aed847 user: drh tags: apple-osx-ml
2012-04-03
19:43
Cherrypick commit [1d5e744cc6] from the trunk in order to avoid leaving a file-descriptor open in test scripts capi3.test and capi3c.test. This is the version of SQLite that ships by default on the original Mountain Lion release. check-in: 86b8481b user: dan tags: apple-osx, mountain-lion
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

   181    181   column(A) ::= columnid(X) type carglist. {
   182    182     A.z = X.z;
   183    183     A.n = (int)(pParse->sLastToken.z-X.z) + pParse->sLastToken.n;
   184    184   }
   185    185   columnid(A) ::= nm(X). {
   186    186     sqlite3AddColumn(pParse,&X);
   187    187     A = X;
          188  +  pParse->constraintName.n = 0;
   188    189   }
   189    190   
   190    191   
   191    192   // An IDENTIFIER can be a generic identifier, or one of several
   192    193   // keywords.  Any non-standard keyword can also be an identifier.
   193    194   //
   194    195   %type id {Token}
................................................................................
   269    270   typename(A) ::= typename(X) ids(Y). {A.z=X.z; A.n=Y.n+(int)(Y.z-X.z);}
   270    271   signed ::= plus_num.
   271    272   signed ::= minus_num.
   272    273   
   273    274   // "carglist" is a list of additional constraints that come after the
   274    275   // column name and column type in a CREATE TABLE statement.
   275    276   //
   276         -carglist ::= carglist cname ccons.
          277  +carglist ::= carglist ccons.
   277    278   carglist ::= .
   278         -cname ::= CONSTRAINT nm(X).           {pParse->constraintName = X;}
   279         -cname ::= .                           {pParse->constraintName.n = 0;}
          279  +ccons ::= CONSTRAINT nm(X).           {pParse->constraintName = X;}
   280    280   ccons ::= DEFAULT term(X).            {sqlite3AddDefaultValue(pParse,&X);}
   281    281   ccons ::= DEFAULT LP expr(X) RP.      {sqlite3AddDefaultValue(pParse,&X);}
   282    282   ccons ::= DEFAULT PLUS term(X).       {sqlite3AddDefaultValue(pParse,&X);}
   283    283   ccons ::= DEFAULT MINUS(A) term(X).      {
   284    284     ExprSpan v;
   285    285     v.pExpr = sqlite3PExpr(pParse, TK_UMINUS, X.pExpr, 0, 0);
   286    286     v.zStart = A.z;
................................................................................
   335    335   defer_subclause(A) ::= NOT DEFERRABLE init_deferred_pred_opt.     {A = 0;}
   336    336   defer_subclause(A) ::= DEFERRABLE init_deferred_pred_opt(X).      {A = X;}
   337    337   %type init_deferred_pred_opt {int}
   338    338   init_deferred_pred_opt(A) ::= .                       {A = 0;}
   339    339   init_deferred_pred_opt(A) ::= INITIALLY DEFERRED.     {A = 1;}
   340    340   init_deferred_pred_opt(A) ::= INITIALLY IMMEDIATE.    {A = 0;}
   341    341   
   342         -conslist_opt(A) ::= .                   {A.n = 0; A.z = 0;}
   343         -conslist_opt(A) ::= COMMA(X) conslist.  {A = X;}
   344         -conslist ::= conslist COMMA cname tcons.
   345         -conslist ::= cname tcons.
          342  +conslist_opt(A) ::= .                         {A.n = 0; A.z = 0;}
          343  +conslist_opt(A) ::= COMMA(X) conslist.        {A = X;}
          344  +conslist ::= conslist tconscomma tcons.
          345  +conslist ::= tcons.
          346  +tconscomma ::= COMMA.            {pParse->constraintName.n = 0;}
          347  +tconscomma ::= .
          348  +tcons ::= CONSTRAINT nm(X).      {pParse->constraintName = X;}
   346    349   tcons ::= PRIMARY KEY LP idxlist(X) autoinc(I) RP onconf(R).
   347    350                                    {sqlite3AddPrimaryKey(pParse,X,R,I,0);}
   348    351   tcons ::= UNIQUE LP idxlist(X) RP onconf(R).
   349    352                                    {sqlite3CreateIndex(pParse,0,0,0,X,R,0,0,0,0);}
   350    353   tcons ::= CHECK LP expr(E) RP onconf.
   351    354                                    {sqlite3AddCheckConstraint(pParse,E.pExpr);}
   352    355   tcons ::= FOREIGN KEY LP idxlist(FA) RP

Changes to test/check.test.

   148    148     }
   149    149   } {1 {constraint two failed}}
   150    150   do_test check-2.6 {
   151    151     catchsql {
   152    152       INSERT INTO t2 VALUES(NULL, NULL, 3.14159);
   153    153     }
   154    154   } {1 {constraint three failed}}
          155  +
          156  +# Undocumented behavior:  The CONSTRAINT name clause can follow a constraint.
          157  +# Such a clause is ignored.  But the parser must accept it for backwards
          158  +# compatibility.
          159  +#
          160  +do_test check-2.10 {
          161  +  execsql {
          162  +    CREATE TABLE t2b(
          163  +      x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one,
          164  +      y TEXT PRIMARY KEY constraint two,
          165  +      z INTEGER,
          166  +      UNIQUE(x,z) constraint three
          167  +    );
          168  +  }
          169  +} {}
          170  +do_test check-2.11 {
          171  +  catchsql {
          172  +    INSERT INTO t2b VALUES('xyzzy','hi',5);
          173  +  }
          174  +} {1 {constraint failed}}
          175  +do_test check-2.12 {
          176  +  execsql {
          177  +    CREATE TABLE t2c(
          178  +      x INTEGER CONSTRAINT x_one CONSTRAINT x_two
          179  +          CHECK( typeof(coalesce(x,0))=='integer' )
          180  +          CONSTRAINT x_two CONSTRAINT x_three,
          181  +      y INTEGER, z INTEGER,
          182  +      CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two
          183  +    );
          184  +  }
          185  +} {}
          186  +do_test check-2.13 {
          187  +  catchsql {
          188  +    INSERT INTO t2c VALUES('xyzzy',7,8);
          189  +  }
          190  +} {1 {constraint x_two failed}}
          191  +do_test check-2.cleanup {
          192  +  execsql {
          193  +    DROP TABLE IF EXISTS t2b;
          194  +    DROP TABLE IF EXISTS t2c;
          195  +  }
          196  +} {}
   155    197   
   156    198   ifcapable subquery {
   157    199     do_test check-3.1 {
   158    200       catchsql {
   159    201         CREATE TABLE t3(
   160    202           x, y, z,
   161    203           CHECK( x<(SELECT min(x) FROM t1) )

Added test/schema5.test.

            1  +# 2010 September 28
            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  +#
           12  +# This file checks corner cases in the CREATE TABLE syntax to make
           13  +# sure that legacy syntax (syntax that is disallowed according to the
           14  +# syntax diagrams) is still accepted, so that older databases that use
           15  +# that syntax can still be read.
           16  +#
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +
           21  +# Table constraints should be separated by commas, but they do not have
           22  +# to be.
           23  +#
           24  +do_test schema5-1.1 {
           25  +  db eval {
           26  +    CREATE TABLE t1(a,b,c, PRIMARY KEY(a) UNIQUE (a) CONSTRAINT one);
           27  +    INSERT INTO t1 VALUES(1,2,3);
           28  +    SELECT * FROM t1;
           29  +  }
           30  +} {1 2 3}
           31  +do_test schema5-1.2 {
           32  +  catchsql {INSERT INTO t1 VALUES(1,3,4);}
           33  +} {1 {column a is not unique}}
           34  +do_test schema5-1.3 {
           35  +  db eval {
           36  +    DROP TABLE t1;
           37  +    CREATE TABLE t1(a,b,c,
           38  +        CONSTRAINT one PRIMARY KEY(a) CONSTRAINT two CHECK(b<10) UNIQUE(b)
           39  +        CONSTRAINT three
           40  +    );
           41  +    INSERT INTO t1 VALUES(1,2,3);
           42  +    SELECT * FROM t1;
           43  +  }
           44  +} {1 2 3}
           45  +do_test schema5-1.4 {
           46  +  catchsql {INSERT INTO t1 VALUES(10,11,12);}
           47  +} {1 {constraint two failed}}
           48  +do_test schema5-1.5 {
           49  +  db eval {
           50  +    DROP TABLE t1;
           51  +    CREATE TABLE t1(a,b,c,
           52  +       UNIQUE(a) CONSTRAINT one,
           53  +       PRIMARY KEY(b,c) CONSTRAINT two
           54  +    );
           55  +    INSERT INTO t1 VALUES(1,2,3);
           56  +  }
           57  +} {}
           58  +do_test schema5-1.6 {
           59  +  catchsql {INSERT INTO t1 VALUES(1,3,4)}
           60  +} {1 {column a is not unique}}
           61  +do_test schema5-1.7 {
           62  +  catchsql {INSERT INTO t1 VALUES(10,2,3)}
           63  +} {1 {columns b, c are not unique}}
           64  +
           65  +
           66  +
           67  +    
           68  +
           69  +finish_test