/ Check-in [83828679]
Login

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

Overview
Comment:Add some tests of statements in foreignkeys.html.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8382867956caf20f62c46c15b456c1c16d0824fd
User & Date: dan 2009-10-07 18:41:20
Context
2009-10-07
23:42
Use memcpy() rather than structure assignment so that memcmp() can later be used for comparison. Ticket [8550ecca70] check-in: 56f609da user: drh tags: trunk
18:41
Add some tests of statements in foreignkeys.html. check-in: 83828679 user: dan tags: trunk
16:04
Add a missing OP_Close opcode to VDBE programs that check for FK constraint violations. check-in: 5caa4a2b user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqliteLimit.h.

   187    187   */
   188    188   #ifndef SQLITE_MAX_LIKE_PATTERN_LENGTH
   189    189   # define SQLITE_MAX_LIKE_PATTERN_LENGTH 50000
   190    190   #endif
   191    191   
   192    192   /*
   193    193   ** Maximum depth of recursion for triggers.
          194  +**
          195  +** A value of 1 means that a trigger program will not be able to itself
          196  +** fire any triggers. A value of 0 means that no trigger programs at all 
          197  +** may be executed.
   194    198   */
   195    199   #ifndef SQLITE_MAX_TRIGGER_DEPTH
   196    200   #if defined(SQLITE_SMALL_STACK)
   197    201   # define SQLITE_MAX_TRIGGER_DEPTH 10
   198    202   #else
   199    203   # define SQLITE_MAX_TRIGGER_DEPTH 1000
   200    204   #endif
   201    205   #endif

Changes to src/test_config.c.

   533    533     LINKVAR( MAX_COMPOUND_SELECT );
   534    534     LINKVAR( MAX_VDBE_OP );
   535    535     LINKVAR( MAX_FUNCTION_ARG );
   536    536     LINKVAR( MAX_VARIABLE_NUMBER );
   537    537     LINKVAR( MAX_PAGE_SIZE );
   538    538     LINKVAR( MAX_PAGE_COUNT );
   539    539     LINKVAR( MAX_LIKE_PATTERN_LENGTH );
          540  +  LINKVAR( MAX_TRIGGER_DEPTH );
   540    541     LINKVAR( DEFAULT_TEMP_CACHE_SIZE );
   541    542     LINKVAR( DEFAULT_CACHE_SIZE );
   542    543     LINKVAR( DEFAULT_PAGE_SIZE );
   543    544     LINKVAR( DEFAULT_FILE_FORMAT );
   544    545     LINKVAR( MAX_ATTACHED );
   545    546   
   546    547     {

Changes to src/vdbe.c.

  4818   4818     ** variable.  */
  4819   4819     if( pOp->p5 ){
  4820   4820       t = pProgram->token;
  4821   4821       for(pFrame=p->pFrame; pFrame && pFrame->token!=t; pFrame=pFrame->pParent);
  4822   4822       if( pFrame ) break;
  4823   4823     }
  4824   4824   
  4825         -  if( p->nFrame>db->aLimit[SQLITE_LIMIT_TRIGGER_DEPTH] ){
         4825  +  if( p->nFrame>=db->aLimit[SQLITE_LIMIT_TRIGGER_DEPTH] ){
  4826   4826       rc = SQLITE_ERROR;
  4827   4827       sqlite3SetString(&p->zErrMsg, db, "too many levels of trigger recursion");
  4828   4828       break;
  4829   4829     }
  4830   4830   
  4831   4831     /* Register pRt is used to store the memory required to save the state
  4832   4832     ** of the current program, and the memory required at runtime to execute

Added test/e_fkey.test.

            1  +# 2009 October 7
            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 implements tests to verify the "testable statements" in the
           13  +# foreignkeys.in document.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +ifcapable {!foreignkey} { finish_test ; return }
           19  +
           20  +execsql "PRAGMA foreign_keys = ON"
           21  +
           22  +#-------------------------------------------------------------------------
           23  +# /* EV: R-24728-13230 */
           24  +# /* EV: R-24450-46174 */
           25  +#
           26  +# Test that MATCH clauses are parsed, but SQLite treats every foreign key
           27  +# constraint as if it were "MATCH SIMPLE".
           28  +#
           29  +foreach zMatch [list SIMPLE PARTIAL FULL Simple parTIAL FuLL ] {
           30  +  drop_all_tables
           31  +  do_test e_fkey-1.$zMatch.1 {
           32  +    execsql "
           33  +      CREATE TABLE p(a, b, c, PRIMARY KEY(b, c));
           34  +      CREATE TABLE c(d, e, f, FOREIGN KEY(e, f) REFERENCES p MATCH $zMatch);
           35  +    "
           36  +  } {}
           37  +  do_test e_fkey-1.$zMatch.2 {
           38  +    execsql { INSERT INTO p VALUES(1, 2, 3)         }
           39  +
           40  +    # MATCH SIMPLE behaviour: Allow any child key that contains one or more
           41  +    # NULL value to be inserted. Non-NULL values do not have to map to any
           42  +    # parent key values, so long as at least one field of the child key is
           43  +    # NULL.
           44  +    execsql { INSERT INTO c VALUES('w', 2, 3)       }
           45  +    execsql { INSERT INTO c VALUES('x', 'x', NULL)  }
           46  +    execsql { INSERT INTO c VALUES('y', NULL, 'x')  }
           47  +    execsql { INSERT INTO c VALUES('z', NULL, NULL) }
           48  +
           49  +    # Check that the FK is enforced properly if there are no NULL values 
           50  +    # in the child key columns.
           51  +    catchsql { INSERT INTO c VALUES('a', 2, 4) }
           52  +  } {1 {foreign key constraint failed}}
           53  +}
           54  +
           55  +#-------------------------------------------------------------------------
           56  +# /* EV: R-21599-16038 */
           57  +#
           58  +# Test that SQLite does not support the SET CONSTRAINT statement. And
           59  +# that it is possible to create both immediate and deferred constraints.
           60  +#
           61  +drop_all_tables
           62  +do_test e_fkey-2.1 {
           63  +  catchsql { SET CONSTRAINTS ALL IMMEDIATE }
           64  +} {1 {near "SET": syntax error}}
           65  +do_test e_fkey-2.2 {
           66  +  catchsql { SET CONSTRAINTS ALL DEFERRED }
           67  +} {1 {near "SET": syntax error}}
           68  +
           69  +do_test e_fkey-2.3 {
           70  +  execsql {
           71  +    CREATE TABLE p(a, b, PRIMARY KEY(a, b));
           72  +    CREATE TABLE cd(c, d, 
           73  +      FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY DEFERRED);
           74  +    CREATE TABLE ci(c, d, 
           75  +      FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY IMMEDIATE);
           76  +    BEGIN;
           77  +  }
           78  +} {}
           79  +do_test e_fkey-2.4 {
           80  +  catchsql { INSERT INTO ci VALUES('x', 'y') }
           81  +} {1 {foreign key constraint failed}}
           82  +do_test e_fkey-2.5 {
           83  +  catchsql { INSERT INTO cd VALUES('x', 'y') }
           84  +} {0 {}}
           85  +do_test e_fkey-2.6 {
           86  +  catchsql { COMMIT }
           87  +} {1 {foreign key constraint failed}}
           88  +do_test e_fkey-2.7 {
           89  +  execsql { 
           90  +    DELETE FROM cd;
           91  +    COMMIT;
           92  +  }
           93  +} {}
           94  +
           95  +#-------------------------------------------------------------------------
           96  +# /* EV: R-42264-30503 */
           97  +#
           98  +# Test that the maximum recursion depth of foreign key action programs is
           99  +# governed by the SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH
          100  +# settings.
          101  +#
          102  +proc test_on_delete_recursion {limit} {
          103  +  drop_all_tables
          104  +  execsql { 
          105  +    BEGIN;
          106  +    CREATE TABLE t0(a PRIMARY KEY, b);
          107  +    INSERT INTO t0 VALUES('x0', NULL);
          108  +  }
          109  +  for {set i 1} {$i <= $limit} {incr i} {
          110  +    execsql "
          111  +      CREATE TABLE t$i (
          112  +        a PRIMARY KEY, b REFERENCES t[expr $i-1] ON DELETE CASCADE
          113  +      );
          114  +      INSERT INTO t$i VALUES('x$i', 'x[expr $i-1]');
          115  +    "
          116  +  }
          117  +  execsql COMMIT
          118  +  catchsql "
          119  +    DELETE FROM t0;
          120  +    SELECT count(*) FROM t$limit;
          121  +  "
          122  +}
          123  +proc test_on_update_recursion {limit} {
          124  +  drop_all_tables
          125  +  execsql { 
          126  +    BEGIN;
          127  +    CREATE TABLE t0(a PRIMARY KEY);
          128  +    INSERT INTO t0 VALUES('xxx');
          129  +  }
          130  +  for {set i 1} {$i <= $limit} {incr i} {
          131  +    set j [expr $i-1]
          132  +
          133  +    execsql "
          134  +      CREATE TABLE t$i (a PRIMARY KEY REFERENCES t$j ON UPDATE CASCADE);
          135  +      INSERT INTO t$i VALUES('xxx');
          136  +    "
          137  +  }
          138  +  execsql COMMIT
          139  +  catchsql "
          140  +    UPDATE t0 SET a = 'yyy';
          141  +    SELECT NOT (a='yyy') FROM t$limit;
          142  +  "
          143  +}
          144  +
          145  +do_test e_fkey-3.1.1 {
          146  +  test_on_delete_recursion $SQLITE_MAX_TRIGGER_DEPTH
          147  +} {0 0}
          148  +do_test e_fkey-3.1.2 {
          149  +  test_on_delete_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1]
          150  +} {1 {too many levels of trigger recursion}}
          151  +do_test e_fkey-3.1.3 {
          152  +  sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5
          153  +  test_on_delete_recursion 5
          154  +} {0 0}
          155  +do_test e_fkey-3.1.4 {
          156  +  test_on_delete_recursion 6
          157  +} {1 {too many levels of trigger recursion}}
          158  +do_test e_fkey-3.1.5 {
          159  +  sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000
          160  +} {5}
          161  +do_test e_fkey-3.2.1 {
          162  +  test_on_update_recursion $SQLITE_MAX_TRIGGER_DEPTH
          163  +} {0 0}
          164  +do_test e_fkey-3.2.2 {
          165  +  test_on_update_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1]
          166  +} {1 {too many levels of trigger recursion}}
          167  +do_test e_fkey-3.2.3 {
          168  +  sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5
          169  +  test_on_update_recursion 5
          170  +} {0 0}
          171  +do_test e_fkey-3.2.4 {
          172  +  test_on_update_recursion 6
          173  +} {1 {too many levels of trigger recursion}}
          174  +do_test e_fkey-3.2.5 {
          175  +  sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000
          176  +} {5}
          177  +
          178  +#-------------------------------------------------------------------------
          179  +# /* EV: R-51769-32730 */
          180  +#
          181  +# The setting of the recursive_triggers pragma does not affect foreign
          182  +# key actions.
          183  +#
          184  +foreach recursive_triggers_setting [list 0 1 ON OFF] {
          185  +  drop_all_tables
          186  +  execsql "PRAGMA recursive_triggers = $recursive_triggers_setting"
          187  +
          188  +  do_test e_fkey-4.$recursive_triggers_setting.1 {
          189  +    execsql {
          190  +      CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1 ON DELETE CASCADE);
          191  +      INSERT INTO t1 VALUES(1, NULL);
          192  +      INSERT INTO t1 VALUES(2, 1);
          193  +      INSERT INTO t1 VALUES(3, 2);
          194  +      INSERT INTO t1 VALUES(4, 3);
          195  +      INSERT INTO t1 VALUES(5, 4);
          196  +      SELECT count(*) FROM t1;
          197  +    }
          198  +  } {5}
          199  +  do_test e_fkey-4.$recursive_triggers_setting.2 {
          200  +    execsql { SELECT count(*) FROM t1 WHERE a = 1 }
          201  +  } {1}
          202  +  do_test e_fkey-4.$recursive_triggers_setting.3 {
          203  +    execsql { 
          204  +      DELETE FROM t1 WHERE a = 1;
          205  +      SELECT count(*) FROM t1;
          206  +    }
          207  +  } {0}
          208  +}
          209  +
          210  +finish_test

Changes to test/fkey2.test.

    75     75   #             FK constraints.
    76     76   #
    77     77   # fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
    78     78   #            command. Recycled to test the built-in implementation.
    79     79   #
    80     80   
    81     81   
    82         -proc drop_all_tables {{db db}} {
    83         -  execsql { PRAGMA foreign_keys = OFF }
    84         -  foreach {t type} [execsql {
    85         -    SELECT name, type FROM sqlite_master 
    86         -    WHERE type IN('table', 'view') AND name NOT like 'sqlite_%'
    87         -  }] {
    88         -    execsql "DROP $type $t"
    89         -  }
    90         -  execsql { PRAGMA foreign_keys = ON }
    91         -}
    92         -
    93     82   execsql { PRAGMA foreign_keys = on }
    94     83   
    95     84   set FkeySimpleSchema {
    96     85     PRAGMA foreign_keys = on;
    97     86     CREATE TABLE t1(a PRIMARY KEY, b);
    98     87     CREATE TABLE t2(c REFERENCES t1(a) /D/ , d);
    99     88   

Changes to test/tester.tcl.

   953    953       set t [open $to w]
   954    954       fconfigure $t -translation binary
   955    955       puts -nonewline $t [read $f [file size $from]]
   956    956       close $t
   957    957       close $f
   958    958     }
   959    959   }
          960  +
          961  +# Drop all tables in database [db]
          962  +proc drop_all_tables {{db db}} {
          963  +  set pk [$db one "PRAGMA foreign_keys"]
          964  +  $db eval "PRAGMA foreign_keys = OFF"
          965  +  foreach {t type} [$db eval {
          966  +    SELECT name, type FROM sqlite_master 
          967  +    WHERE type IN('table', 'view') AND name NOT like 'sqlite_%'
          968  +  }] {
          969  +    $db eval "DROP $type $t"
          970  +  }
          971  +  $db eval " PRAGMA foreign_keys = $pk "
          972  +}
          973  +
   960    974   
   961    975   # If the library is compiled with the SQLITE_DEFAULT_AUTOVACUUM macro set
   962    976   # to non-zero, then set the global variable $AUTOVACUUM to 1.
   963    977   set AUTOVACUUM $sqlite_options(default_autovacuum)
   964    978   
   965    979   source $testdir/thread_common.tcl