/ Check-in [ffa6207d]
Login

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

Overview
Comment:Extra test cases mapped to statements in foreignkeys.html.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:ffa6207dd72024f01ab6f2715fe628943f178ca5
User & Date: dan 2009-10-12 11:27:01
Context
2009-10-12
15:25
Update e_fkey.test to match the latest version of foreignkeys.html. check-in: a2ca9f1a user: dan tags: trunk
11:27
Extra test cases mapped to statements in foreignkeys.html. check-in: ffa6207d user: dan tags: trunk
08:41
Add further test cases to e_fkey.test. check-in: d61cc0e1 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_fkey.test.

     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   #
    12     12   # This file implements tests to verify the "testable statements" in the
    13     13   # foreignkeys.in document.
    14     14   #
           15  +# The tests in this file are arranged to mirror the structure of 
           16  +# foreignkey.in, with one exception: The statements in section 2, which 
           17  +# deals with enabling/disabling foreign key support, is tested first,
           18  +# before section 1. This is because some statements in section 2 deal
           19  +# with builds that do not include complete foreign key support (because
           20  +# either SQLITE_OMIT_TRIGGER or SQLITE_OMIT_FOREIGN_KEY was defined
           21  +# at build time).
           22  +#
    15     23   
    16     24   set testdir [file dirname $argv0]
    17     25   source $testdir/tester.tcl
    18         -ifcapable {!foreignkey} { finish_test ; return }
    19         -
    20         -execsql "PRAGMA foreign_keys = ON"
           26  +
           27  +###########################################################################
           28  +### SECTION 2: Enabling Foreign Key Support
           29  +###########################################################################
           30  +
           31  +#-------------------------------------------------------------------------
           32  +# /* EV: R-33710-56344 */
           33  +#
           34  +# Test builds neither OMIT_FOREIGN_KEY or OMIT_TRIGGER defined have 
           35  +# foreign key functionality.
           36  +#
           37  +ifcapable trigger&&foreignkey {
           38  +  do_test e_fkey-49 {
           39  +    execsql {
           40  +      PRAGMA foreign_keys = ON;
           41  +      CREATE TABLE p(i PRIMARY KEY);
           42  +      CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
           43  +      INSERT INTO p VALUES('hello');
           44  +      INSERT INTO c VALUES('hello');
           45  +      UPDATE p SET i = 'world';
           46  +      SELECT * FROM c;
           47  +    }
           48  +  } {world}
           49  +}
           50  +
           51  +#-------------------------------------------------------------------------
           52  +# /* EV: R-44697-61543 */
           53  +#
           54  +# Test the effects of defining OMIT_TRIGGER but not OMIT_FOREIGN_KEY.
           55  +#
           56  +# /* EV: R-22567-44039 */
           57  +# /* EV: R-60444-29168 */
           58  +#
           59  +# Specifically, test that "PRAGMA foreign_keys" is a no-op in this case.
           60  +# When using the pragma to query the current setting, 0 rows are returned.
           61  +#
           62  +reset_db
           63  +ifcapable !trigger&&foreignkey {
           64  +  do_test e_fkey-51.1 {
           65  +    execsql {
           66  +      PRAGMA foreign_keys = ON;
           67  +      CREATE TABLE p(i PRIMARY KEY);
           68  +      CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
           69  +      INSERT INTO p VALUES('hello');
           70  +      INSERT INTO c VALUES('hello');
           71  +      UPDATE p SET i = 'world';
           72  +      SELECT * FROM c;
           73  +    }
           74  +  } {hello}
           75  +  do_test e_fkey-51.2 {
           76  +    execsql { PRAGMA foreign_key_list(c) }
           77  +  } {0 0 p j {} CASCADE {NO ACTION} NONE}
           78  +  do_test e_fkey-51.3 {
           79  +    execsql { PRAGMA foreign_keys }
           80  +  } {}
           81  +}
           82  +
           83  +
           84  +#-------------------------------------------------------------------------
           85  +# /* EV: R-58428-36660 */
           86  +#
           87  +# Test the effects of defining OMIT_FOREIGN_KEY.
           88  +#
           89  +# /* EV: R-58428-36660 */
           90  +#
           91  +# Specifically, test that foreign key constraints cannot even be parsed 
           92  +# in such a build.
           93  +#
           94  +reset_db
           95  +ifcapable !foreignkey {
           96  +  do_test e_fkey-52.1 {
           97  +    execsql { CREATE TABLE p(i PRIMARY KEY) }
           98  +    catchsql { CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE) }
           99  +  } {1 {near "ON": syntax error}}
          100  +  do_test e_fkey-52.2 {
          101  +    # This is allowed, as in this build, "REFERENCES" is not a keyword.
          102  +    # The declared datatype of column j is "REFERENCES p".
          103  +    execsql { CREATE TABLE c(j REFERENCES p) }
          104  +  } {}
          105  +  do_test e_fkey-52.3 {
          106  +    execsql { PRAGMA table_info(c) }
          107  +  } {0 j {REFERENCES p} 0 {} 0}
          108  +  do_test e_fkey-52.4 {
          109  +    execsql { PRAGMA foreign_key_list(c) }
          110  +  } {}
          111  +  do_test e_fkey-52.5 {
          112  +    execsql { PRAGMA foreign_keys }
          113  +  } {}
          114  +}
          115  +
          116  +ifcapable !foreignkey||!trigger { finish_test ; return }
          117  +reset_db
          118  +
          119  +
          120  +#-------------------------------------------------------------------------
          121  +# /* EV: R-07280-60510 */
          122  +#
          123  +# Test that even if foreign keys are supported by the build, they must
          124  +# be enabled using "PRAGMA foreign_keys = ON" (or similar).
          125  +#
          126  +# /* EV: R-15831-45974 */
          127  +#
          128  +# This also tests that foreign key constraints are disabled by default.
          129  +#
          130  +drop_all_tables
          131  +do_test e_fkey-53.1 {
          132  +  execsql {
          133  +    CREATE TABLE p(i PRIMARY KEY);
          134  +    CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE);
          135  +    INSERT INTO p VALUES('hello');
          136  +    INSERT INTO c VALUES('hello');
          137  +    UPDATE p SET i = 'world';
          138  +    SELECT * FROM c;
          139  +  } 
          140  +} {hello}
          141  +do_test e_fkey-53.2 {
          142  +  execsql {
          143  +    DELETE FROM c;
          144  +    DELETE FROM p;
          145  +    PRAGMA foreign_keys = ON;
          146  +    INSERT INTO p VALUES('hello');
          147  +    INSERT INTO c VALUES('hello');
          148  +    UPDATE p SET i = 'world';
          149  +    SELECT * FROM c;
          150  +  } 
          151  +} {world}
          152  +
          153  +#-------------------------------------------------------------------------
          154  +# /* EV: R-15278-54456 */
          155  +# /* EV: R-11255-19907 */
          156  +#
          157  +# Test that the application can use "PRAGMA foreign_keys" to query for
          158  +# whether or not foreign keys are currently enabled. This also tests
          159  +# the example code in section 2 of foreignkeys.in.
          160  +#
          161  +reset_db
          162  +do_test e_fkey-54.1 {
          163  +  execsql { PRAGMA foreign_keys }
          164  +} {0}
          165  +do_test e_fkey-54.2 {
          166  +  execsql { 
          167  +    PRAGMA foreign_keys = ON;
          168  +    PRAGMA foreign_keys;
          169  +  }
          170  +} {1}
          171  +do_test e_fkey-54.3 {
          172  +  execsql { 
          173  +    PRAGMA foreign_keys = OFF;
          174  +    PRAGMA foreign_keys;
          175  +  }
          176  +} {0}
          177  +
          178  +#-------------------------------------------------------------------------
          179  +# /* EV: R-07050-54503 */
          180  +#
          181  +# Test that it is not possible to enable or disable foreign key support
          182  +# while not in auto-commit mode.
          183  +#
          184  +reset_db
          185  +do_test e_fkey-55.1 {
          186  +  execsql {
          187  +    PRAGMA foreign_keys = ON;
          188  +    CREATE TABLE t1(a UNIQUE, b);
          189  +    CREATE TABLE t2(c, d REFERENCES t1(a));
          190  +    INSERT INTO t1 VALUES(1, 2);
          191  +    INSERT INTO t2 VALUES(2, 1);
          192  +    BEGIN;
          193  +      PRAGMA foreign_keys = OFF;
          194  +  }
          195  +  catchsql {
          196  +      DELETE FROM t1
          197  +  }
          198  +} {1 {foreign key constraint failed}}
          199  +do_test e_fkey-55.2 {
          200  +  execsql { PRAGMA foreign_keys }
          201  +} {1}
          202  +do_test e_fkey-55.3 {
          203  +  execsql {
          204  +    COMMIT;
          205  +    PRAGMA foreign_keys = OFF;
          206  +    BEGIN;
          207  +      PRAGMA foreign_keys = ON;
          208  +      DELETE FROM t1;
          209  +      PRAGMA foreign_keys;
          210  +  }
          211  +} {0}
          212  +do_test e_fkey-55.4 {
          213  +  execsql COMMIT
          214  +} {}
    21    215   
    22    216   ###########################################################################
    23    217   ### SECTION 1: Introduction to Foreign Key Constraints
    24    218   ###########################################################################
          219  +execsql "PRAGMA foreign_keys = ON"
    25    220   
    26    221   #-------------------------------------------------------------------------
    27    222   # /* EV: R-04042-24825 */
    28    223   #
    29    224   # Verify that the syntax in the first example in section 1 is valid.
    30    225   #
    31    226   do_test e_fkey-38.1 {
................................................................................
   365    560   do_test e_fkey-47.3 {
   366    561     execsql { SELECT typeof(a) FROM t1 }
   367    562   } {integer integer text}
   368    563   do_test e_fkey-47.4 {
   369    564     catchsql { DELETE FROM t1 WHERE rowid = 2 }
   370    565   } {1 {foreign key constraint failed}}
   371    566   
   372         -###########################################################################
   373         -### SECTION 2: Enabling Foreign Key Support
   374         -###########################################################################
   375         -
   376    567   ###########################################################################
   377    568   ### SECTION 3: Required and Suggested Database Indexes
   378    569   ###########################################################################
   379    570   
   380    571   ###########################################################################
   381    572   ### SECTION 4.1: Composite Foreign Key Constraints
   382    573   ###########################################################################