/ Check-in [0a0a17d8]
Login

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

Overview
Comment:Remove unreachable branches from fkey.c. Add a few tests to cover the remaining branches.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:0a0a17d87c990a811a2755b9d9d4141b611b49db
User & Date: dan 2009-09-30 08:11:07
Context
2009-10-01
04:35
Add tests to check that FK support interacts with count-changes correctly. check-in: 5b836615 user: dan tags: trunk
2009-09-30
08:11
Remove unreachable branches from fkey.c. Add a few tests to cover the remaining branches. check-in: 0a0a17d8 user: dan tags: trunk
04:28
Change a couple of comments to use "SQLITE_MUTEX_OMIT" instead of OMIT_MUTEX. check-in: b733e939 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

189
190
191
192
193
194
195

196
197
198
199
200
201
202
...
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
...
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
  int *aiCol = 0;                     /* Value to return via *paiCol */
  int nCol = pFKey->nCol;             /* Number of columns in parent key */
  char *zKey = pFKey->aCol[0].zCol;   /* Name of left-most parent key column */

  /* The caller is responsible for zeroing output parameters. */
  assert( ppIdx && *ppIdx==0 );
  assert( !paiCol || *paiCol==0 );


  /* If this is a non-composite (single column) foreign key, check if it 
  ** maps to the INTEGER PRIMARY KEY of table pParent. If so, leave *ppIdx 
  ** and *paiCol set to zero and return early. 
  **
  ** Otherwise, for a composite foreign key (more than one column), allocate
  ** space for the aiCol array (returned via output parameter *paiCol).
................................................................................
          if( j==nCol ) break;
        }
        if( i==nCol ) break;      /* pIdx is usable */
      }
    }
  }

  if( pParse && !pIdx ){
    if( !pParse->disableTriggers ){
      sqlite3ErrorMsg(pParse, "foreign key mismatch");
    }
    sqlite3DbFree(pParse->db, aiCol);
    return 1;
  }

................................................................................
  sqlite3ResolveExprNames(&sNameContext, pWhere);

  /* Create VDBE to loop through the entries in pSrc that match the WHERE
  ** clause. If the constraint is not deferred, throw an exception for
  ** each row found. Otherwise, for deferred constraints, increment the
  ** deferred constraint counter by nIncr for each row selected.  */
  pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0);
  if( nIncr==0 ){
    /* Special case: A RESTRICT Action. Throw an error immediately if one
    ** of these is encountered.  */
    sqlite3HaltConstraint(
      pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
    );
  }else{
    if( nIncr>0 && pFKey->isDeferred==0 ){
      sqlite3ParseToplevel(pParse)->mayAbort = 1;
    }
    sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
  }
  if( pWInfo ){
    sqlite3WhereEnd(pWInfo);
  }

  /* Clean up the WHERE clause constructed above. */
  sqlite3ExprDelete(db, pWhere);
  if( iFkIfZero ){







>







 







|







 







<
<
<
<
<
<
<
|
|
|
|
<







189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
...
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
...
539
540
541
542
543
544
545







546
547
548
549

550
551
552
553
554
555
556
  int *aiCol = 0;                     /* Value to return via *paiCol */
  int nCol = pFKey->nCol;             /* Number of columns in parent key */
  char *zKey = pFKey->aCol[0].zCol;   /* Name of left-most parent key column */

  /* The caller is responsible for zeroing output parameters. */
  assert( ppIdx && *ppIdx==0 );
  assert( !paiCol || *paiCol==0 );
  assert( pParse );

  /* If this is a non-composite (single column) foreign key, check if it 
  ** maps to the INTEGER PRIMARY KEY of table pParent. If so, leave *ppIdx 
  ** and *paiCol set to zero and return early. 
  **
  ** Otherwise, for a composite foreign key (more than one column), allocate
  ** space for the aiCol array (returned via output parameter *paiCol).
................................................................................
          if( j==nCol ) break;
        }
        if( i==nCol ) break;      /* pIdx is usable */
      }
    }
  }

  if( !pIdx ){
    if( !pParse->disableTriggers ){
      sqlite3ErrorMsg(pParse, "foreign key mismatch");
    }
    sqlite3DbFree(pParse->db, aiCol);
    return 1;
  }

................................................................................
  sqlite3ResolveExprNames(&sNameContext, pWhere);

  /* Create VDBE to loop through the entries in pSrc that match the WHERE
  ** clause. If the constraint is not deferred, throw an exception for
  ** each row found. Otherwise, for deferred constraints, increment the
  ** deferred constraint counter by nIncr for each row selected.  */
  pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0);







  if( nIncr>0 && pFKey->isDeferred==0 ){
    sqlite3ParseToplevel(pParse)->mayAbort = 1;
  }
  sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);

  if( pWInfo ){
    sqlite3WhereEnd(pWInfo);
  }

  /* Clean up the WHERE clause constructed above. */
  sqlite3ExprDelete(db, pWhere);
  if( iFkIfZero ){

Changes to test/fkey2.test.

70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
...
807
808
809
810
811
812
813














































814
815
816
817
818
819
820
....
1008
1009
1010
1011
1012
1013
1014
































































1015
1016
1017
1018
1019
1020
1021
#
# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
#            command. Recycled to test the built-in implementation.
#


proc drop_all_tables {{db db}} {
  set tbls [execsql {SELECT name FROM sqlite_master WHERE type = 'table'}]
  execsql { PRAGMA foreign_keys = OFF }
  foreach t [execsql {
    SELECT name FROM sqlite_master 
    WHERE type = 'table' AND name NOT like 'sqlite_%'
  }] {
    execsql "DROP TABLE $t"
  }
  execsql { PRAGMA foreign_keys = ON }
}

execsql { PRAGMA foreign_keys = on }

set FkeySimpleSchema {
................................................................................
do_test fkey2-12.2.4 {
  execsql {
    SELECT * FROM t1;
    SELECT * FROM t2;
  }
} {A B a b}















































#-------------------------------------------------------------------------
# The following tests, fkey2-13.*, test that FK processing is performed
# when rows are REPLACEd.
#
drop_all_tables
do_test fkey2-13.1.1 {
  execsql {
................................................................................
    CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
  }
  catchsql { INSERT INTO cc VALUES(1, 2) }
} {1 {foreign key mismatch}}
do_test fkey-2.14.3.9 {
  execsql { DROP TABLE cc }
} {}

































































#-------------------------------------------------------------------------
# The following tests, fkey2-15.*, test that unnecessary FK related scans 
# and lookups are avoided when the constraint counters are zero.
#
drop_all_tables
proc execsqlS {zSql} {







<

|
|
|

|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







70
71
72
73
74
75
76

77
78
79
80
81
82
83
84
85
86
87
88
89
...
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
....
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
#
# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
#            command. Recycled to test the built-in implementation.
#


proc drop_all_tables {{db db}} {

  execsql { PRAGMA foreign_keys = OFF }
  foreach {t type} [execsql {
    SELECT name, type FROM sqlite_master 
    WHERE type IN('table', 'view') AND name NOT like 'sqlite_%'
  }] {
    execsql "DROP $type $t"
  }
  execsql { PRAGMA foreign_keys = ON }
}

execsql { PRAGMA foreign_keys = on }

set FkeySimpleSchema {
................................................................................
do_test fkey2-12.2.4 {
  execsql {
    SELECT * FROM t1;
    SELECT * FROM t2;
  }
} {A B a b}

drop_all_tables
do_test fkey2-12.3.1 {
  execsql {
    CREATE TABLE up(
      c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
      c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
      c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
      c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
      PRIMARY KEY(c34, c35)
    );
    CREATE TABLE down(
      c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
      c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
      c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
      c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
      FOREIGN KEY(c39, c38) REFERENCES up ON UPDATE CASCADE
    );
  }
} {}
do_test fkey2-12.3.2 {
  execsql {
    INSERT INTO up(c34, c35) VALUES('yes', 'no');
    INSERT INTO down(c39, c38) VALUES('yes', 'no');
    UPDATE up SET c34 = 'possibly';
    SELECT c38, c39 FROM down;
    DELETE FROM down;
  }
} {no possibly}
do_test fkey2-12.3.3 {
  catchsql { INSERT INTO down(c39, c38) VALUES('yes', 'no') }
} {1 {foreign key constraint failed}}
do_test fkey2-12.3.4 {
  execsql { 
    INSERT INTO up(c34, c35) VALUES('yes', 'no');
    INSERT INTO down(c39, c38) VALUES('yes', 'no');
  }
  catchsql { DELETE FROM up WHERE c34 = 'yes' }
} {1 {foreign key constraint failed}}
do_test fkey2-12.3.5 {
  execsql { 
    DELETE FROM up WHERE c34 = 'possibly';
    SELECT c34, c35 FROM up;
    SELECT c39, c38 FROM down;
  }
} {yes no yes no}

#-------------------------------------------------------------------------
# The following tests, fkey2-13.*, test that FK processing is performed
# when rows are REPLACEd.
#
drop_all_tables
do_test fkey2-13.1.1 {
  execsql {
................................................................................
    CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
  }
  catchsql { INSERT INTO cc VALUES(1, 2) }
} {1 {foreign key mismatch}}
do_test fkey-2.14.3.9 {
  execsql { DROP TABLE cc }
} {}
do_test fkey-2.14.3.10 {
  execsql {
    CREATE TABLE cc(a, b, 
      FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED
    );
  }
  execsql {
    INSERT INTO pp VALUES('a', 'b');
    INSERT INTO cc VALUES('a', 'b');
    BEGIN;
      DROP TABLE pp;
      CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
      INSERT INTO pp VALUES(1, 'a', 'b');
    COMMIT;
  }
} {}
do_test fkey-2.14.3.11 {
  execsql { 
    BEGIN;
      DROP TABLE cc;
      DROP TABLE pp;
    COMMIT;
  }
} {}
do_test fkey-2.14.3.12 {
  execsql {
    CREATE TABLE b1(a, b);
    CREATE TABLE b2(a, b REFERENCES b1);
    DROP TABLE b1;
  }
} {}
do_test fkey-2.14.3.13 {
  execsql {
    CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED);
    DROP TABLE b2;
  }
} {}

# Test that nothing goes wrong when dropping a table that refers to a view.
# Or dropping a view that an existing FK (incorrectly) refers to. Or either
# of the above scenarios with a virtual table.
drop_all_tables
do_test fkey-2.14.4.1 {
  execsql {
    CREATE TABLE t1(x REFERENCES v); 
    CREATE VIEW v AS SELECT * FROM t1;
  }
} {}
do_test fkey-2.14.4.2 {
  execsql {
    DROP VIEW v;
  }
} {}
ifcapable vtab {
  register_echo_module db
  do_test fkey-2.14.4.3 {
    execsql { CREATE VIRTUAL TABLE v USING echo(t1) }
  } {}
  do_test fkey-2.14.4.2 {
    execsql {
      DROP TABLE v;
    }
  } {}
}

#-------------------------------------------------------------------------
# The following tests, fkey2-15.*, test that unnecessary FK related scans 
# and lookups are avoided when the constraint counters are zero.
#
drop_all_tables
proc execsqlS {zSql} {

Changes to test/fkey_malloc.test.

96
97
98
99
100
101
102


























103
104
105
106
  CREATE TABLE t1(x, y, PRIMARY KEY(x, y));
  CREATE TABLE t2(a, b, FOREIGN KEY(a, b) REFERENCES t1 ON UPDATE CASCADE);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t2 VALUES(1, 2);
} -sqlbody {
  UPDATE t1 SET x = 5;
}



























finish_test









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
  CREATE TABLE t1(x, y, PRIMARY KEY(x, y));
  CREATE TABLE t2(a, b, FOREIGN KEY(a, b) REFERENCES t1 ON UPDATE CASCADE);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t2 VALUES(1, 2);
} -sqlbody {
  UPDATE t1 SET x = 5;
}

do_malloc_test fkey_malloc-6 -sqlprep {
  PRAGMA foreign_keys = 1;
  CREATE TABLE t1(
    x PRIMARY KEY, 
    y REFERENCES t1 ON DELETE RESTRICT ON UPDATE SET DEFAULT
  );
  INSERT INTO t1 VALUES('abc', 'abc');
  INSERT INTO t1 VALUES('def', 'def');
} -sqlbody {
  INSERT INTO t1 VALUES('ghi', 'ghi');
  DELETE FROM t1 WHERE rowid>1;
  UPDATE t1 SET x='jkl', y='jkl';
}

do_malloc_test fkey_malloc-7 -sqlprep {
  PRAGMA foreign_keys = 1;
  CREATE TABLE x(a, b, PRIMARY KEY(a, b));
  CREATE TABLE y(c, d,
    FOREIGN KEY(d, c) REFERENCES x DEFERRABLE INITIALLY DEFERRED
  );
  CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x);
} -sqlbody {
  DROP TABLE y;
  DROP TABLE x;
}

finish_test