/ Check-in [e9e5b100]
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:Add tests to fkey2.test to check that ON CONFLICT clauses do not affect SQLite's behaviour when an FK constraint is violated.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e9e5b1001986348ef0f88c19de87b94559a5451e
User & Date: dan 2010-05-29 08:40:38
Context
2010-05-31
06:38
Changes to the way one of the WAL/OOM tests works. check-in: 15abbc34 user: dan tags: trunk
2010-05-30
19:55
Initial code for incremental checkpoint in WAL mode. This check-in compiles on unix and runs as long as you do not engage WAL mode. WAL mode crashes and burns. Consider this check-in a baseline implementation for getting the new capability up and running. check-in: ef3ba7a1 user: drh tags: wal-incr-ckpt
2010-05-29
08:40
Add tests to fkey2.test to check that ON CONFLICT clauses do not affect SQLite's behaviour when an FK constraint is violated. check-in: e9e5b100 user: dan tags: trunk
06:18
Minor changes to checksum related test cases. check-in: 60c22bde user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/fkey2.test.

69
70
71
72
73
74
75



76
77
78
79
80
81
82
....
1622
1623
1624
1625
1626
1627
1628

























































































1629
1630
1631
1632
1633
1634
1635
#             updated and deleted.
#
# fkey2-17.*: Test that the "count_changes" pragma does not interfere with
#             FK constraint processing.
# 
# fkey2-18.*: Test that the authorization callback is invoked when processing
#             FK constraints.



#
# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
#            command. Recycled to test the built-in implementation.
#
# fkey2-dd08e5.*:  Tests to verify that ticket dd08e5a988d00decc4a543daa8d
#                  has been fixed.
#
................................................................................
do_test fkey2-19.4 {
  sqlite3_bind_int $S 1 1
  sqlite3_step $S
} {SQLITE_DONE}
do_test fkey2-19.4 {
  sqlite3_finalize $S
} {SQLITE_OK}


























































































#-------------------------------------------------------------------------
# The following block of tests, those prefixed with "fkey2-genfkey.", are 
# the same tests that were used to test the ".genfkey" command provided 
# by the shell tool. So these tests show that the built-in foreign key 
# implementation is more or less compatible with the triggers generated 
# by genfkey.







>
>
>







 







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







69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
....
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
#             updated and deleted.
#
# fkey2-17.*: Test that the "count_changes" pragma does not interfere with
#             FK constraint processing.
# 
# fkey2-18.*: Test that the authorization callback is invoked when processing
#             FK constraints.
#
# fkey2-20.*: Test that ON CONFLICT clauses specified as part of statements
#             do not affect the operation of FK constraints.
#
# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
#            command. Recycled to test the built-in implementation.
#
# fkey2-dd08e5.*:  Tests to verify that ticket dd08e5a988d00decc4a543daa8d
#                  has been fixed.
#
................................................................................
do_test fkey2-19.4 {
  sqlite3_bind_int $S 1 1
  sqlite3_step $S
} {SQLITE_DONE}
do_test fkey2-19.4 {
  sqlite3_finalize $S
} {SQLITE_OK}

drop_all_tables
do_test fkey2-20.1 {
  execsql {
    CREATE TABLE pp(a PRIMARY KEY, b);
    CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp);
  }
} {}

foreach {tn insert} {
  1 "INSERT"
  2 "INSERT OR IGNORE"
  3 "INSERT OR ABORT"
  4 "INSERT OR ROLLBACK"
  5 "INSERT OR REPLACE"
  6 "INSERT OR FAIL"
} {
  do_test fkey2-20.2.$tn.1 {
    catchsql "$insert INTO cc VALUES(1, 2)"
  } {1 {foreign key constraint failed}}
  do_test fkey2-20.2.$tn.2 {
    execsql { SELECT * FROM cc }
  } {}
  do_test fkey2-20.2.$tn.3 {
    execsql {
      BEGIN;
        INSERT INTO pp VALUES(2, 'two');
        INSERT INTO cc VALUES(1, 2);
    }
    catchsql "$insert INTO cc VALUES(3, 4)"
  } {1 {foreign key constraint failed}}
  do_test fkey2-20.2.$tn.4 {
    execsql { COMMIT ; SELECT * FROM cc }
  } {1 2}
  do_test fkey2-20.2.$tn.5 {
    execsql { DELETE FROM cc ; DELETE FROM pp }
  } {}
}

foreach {tn update} {
  1 "UPDATE"
  2 "UPDATE OR IGNORE"
  3 "UPDATE OR ABORT"
  4 "UPDATE OR ROLLBACK"
  5 "UPDATE OR REPLACE"
  6 "UPDATE OR FAIL"
} {
  do_test fkey2-20.3.$tn.1 {
    execsql {
      INSERT INTO pp VALUES(2, 'two');
      INSERT INTO cc VALUES(1, 2);
    }
  } {}
  do_test fkey2-20.3.$tn.2 {
    catchsql "$update pp SET a = 1"
  } {1 {foreign key constraint failed}}
  do_test fkey2-20.3.$tn.3 {
    execsql { SELECT * FROM pp }
  } {2 two}
  do_test fkey2-20.3.$tn.4 {
    catchsql "$update cc SET d = 1"
  } {1 {foreign key constraint failed}}
  do_test fkey2-20.3.$tn.5 {
    execsql { SELECT * FROM cc }
  } {1 2}
  do_test fkey2-20.3.$tn.6 {
    execsql {
      BEGIN;
        INSERT INTO pp VALUES(3, 'three');
    }
    catchsql "$update pp SET a = 1 WHERE a = 2"
  } {1 {foreign key constraint failed}}
  do_test fkey2-20.3.$tn.7 {
    execsql { COMMIT ; SELECT * FROM pp }
  } {2 two 3 three}
  do_test fkey2-20.3.$tn.8 {
    execsql {
      BEGIN;
        INSERT INTO cc VALUES(2, 2);
    }
    catchsql "$update cc SET d = 1 WHERE c = 1"
  } {1 {foreign key constraint failed}}
  do_test fkey2-20.3.$tn.9 {
    execsql { COMMIT ; SELECT * FROM cc }
  } {1 2 2 2}
  do_test fkey2-20.3.$tn.10 {
    execsql { DELETE FROM cc ; DELETE FROM pp }
  } {}
}

#-------------------------------------------------------------------------
# The following block of tests, those prefixed with "fkey2-genfkey.", are 
# the same tests that were used to test the ".genfkey" command provided 
# by the shell tool. So these tests show that the built-in foreign key 
# implementation is more or less compatible with the triggers generated 
# by genfkey.