SQLite

# fkey2-15.*: Test that if there are no (known) outstanding foreign key 
#             constraint violations in the database, inserting into a parent
#             table or deleting from a child table does not cause SQLite
#             to check if this has repaired an outstanding violation.
#
# fkey2-16.*: Test that rows that refer to themselves may be inserted, 
#             updated and deleted.
#
# fkey2-17.*: Test that the "count_changes" pragma does not interfere with
#             FK constraint processing.
#
# 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 { DELETE FROM self }
  } {}
  do_test fkey2-16.1.$tn.8 {
    catchsql { INSERT INTO self VALUES(20, 21) }
  } {1 {foreign key constraint failed}}
}

#-------------------------------------------------------------------------
# This next block of tests, fkey2-17.*, tests that if "PRAGMA count_changes"
# is turned on statements that violate immediate FK constraints return
# SQLITE_CONSTRAINT immediately, not after returning a number of rows.
# Whereas statements that violate deferred FK constraints return the number
# of rows before failing.
#
# Also test that rows modified by FK actions are not counted in either the
# returned row count or the values returned by sqlite3_changes(). Like
# trigger related changes, they are included in sqlite3_total_changes() though.
#
drop_all_tables
do_test fkey2-17.1.1 {
  execsql { PRAGMA count_changes = 1 }
  execsql { 
    CREATE TABLE one(a, b, c, UNIQUE(b, c));
    CREATE TABLE two(d, e, f, FOREIGN KEY(e, f) REFERENCES one(b, c));
    INSERT INTO one VALUES(1, 2, 3);
  }
} {1}
do_test fkey2-17.1.2 {
  set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy]
  sqlite3_step $STMT
} {SQLITE_CONSTRAINT}
do_test fkey2-17.1.3 {
  sqlite3_step $STMT
} {SQLITE_MISUSE}
do_test fkey2-17.1.4 {
  sqlite3_finalize $STMT
} {SQLITE_CONSTRAINT}
do_test fkey2-17.1.5 {
  execsql {
    INSERT INTO one VALUES(2, 3, 4);
    INSERT INTO one VALUES(3, 4, 5);
    INSERT INTO two VALUES(1, 2, 3);
    INSERT INTO two VALUES(2, 3, 4);
    INSERT INTO two VALUES(3, 4, 5);
  }
} {1 1 1 1 1}
do_test fkey2-17.1.6 {
  catchsql {
    BEGIN;
      INSERT INTO one VALUES(0, 0, 0);
      UPDATE two SET e=e+1, f=f+1;
  }
} {1 {foreign key constraint failed}}
do_test fkey2-17.1.7 {
  execsql { SELECT * FROM one }
} {1 2 3 2 3 4 3 4 5 0 0 0}
do_test fkey2-17.1.8 {
  execsql { SELECT * FROM two }
} {1 2 3 2 3 4 3 4 5}
do_test fkey2-17.1.9 {
  execsql COMMIT
} {}
do_test fkey2-17.1.10 {
  execsql {
    CREATE TABLE three(
      g, h, i, 
      FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED
    );
  }
} {}
do_test fkey2-17.1.11 {
  set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy]
  sqlite3_step $STMT
} {SQLITE_ROW}
do_test fkey2-17.1.12 {
  sqlite3_column_text $STMT 0
} {1}
do_test fkey2-17.1.13 {
  sqlite3_step $STMT
} {SQLITE_CONSTRAINT}
do_test fkey2-17.1.14 {
  sqlite3_finalize $STMT
} {SQLITE_CONSTRAINT}

drop_all_tables
do_test fkey2-17.2.1 {
  execsql {
    CREATE TABLE high("a'b!" PRIMARY KEY, b);
    CREATE TABLE low(
      c, 
      "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE
    );
  }
} {}
do_test fkey2-17.2.2 {
  execsql {
    INSERT INTO high VALUES('a', 'b');
    INSERT INTO low VALUES('b', 'a');
  }
  db changes
} {1}
set nTotal [db total_changes]
do_test fkey2-17.2.3 {
  execsql { UPDATE high SET "a'b!" = 'c' }
} {1}
do_test fkey2-17.2.4 {
  db changes
} {1}
do_test fkey2-17.2.5 {
  expr [db total_changes] - $nTotal
} {2}
do_test fkey2-17.2.6 {
  execsql { SELECT * FROM high ; SELECT * FROM low }
} {c b b c}
do_test fkey2-17.2.7 {
  execsql { DELETE FROM high }
} {1}
do_test fkey2-17.2.8 {
  db changes
} {1}
do_test fkey2-17.2.9 {
  expr [db total_changes] - $nTotal
} {4}
do_test fkey2-17.2.10 {
  execsql { SELECT * FROM high ; SELECT * FROM low }
} {}


execsql { PRAGMA count_changes = 0 }
#-------------------------------------------------------------------------
# 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.
#