AryaWu/sqlite

1# 2009 September 222#3# The author disclaims copyright to this source code.  In place of4# 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#13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16 17ifcapable !foreignkey||!trigger {18  finish_test19  return20}21source $testdir/malloc_common.tcl22 23do_malloc_test fkey_malloc-1 -sqlprep {24  PRAGMA foreign_keys = 1;25  CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);26  CREATE TABLE t2(x REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE);27} -sqlbody {28  INSERT INTO t1 VALUES('aaa', 1);29  INSERT INTO t2 VALUES('aaa');30  UPDATE t1 SET a = 'bbb';31  DELETE FROM t1;32  PRAGMA foreign_key_check;33}34 35do_malloc_test fkey_malloc-2 -sqlprep {36  PRAGMA foreign_keys = 1;37  CREATE TABLE t1(a, b, UNIQUE(a, b));38} -sqlbody {39  CREATE TABLE t2(x, y, 40    FOREIGN KEY(x, y) REFERENCES t1(a, b) DEFERRABLE INITIALLY DEFERRED41  );42  BEGIN;43    INSERT INTO t2 VALUES('a', 'b');44    INSERT INTO t1 VALUES('a', 'b');45    UPDATE t1 SET a = 'c';46    DELETE FROM t2;47    INSERT INTO t2 VALUES('d', 'b');48    UPDATE t2 SET x = 'c';49  COMMIT;50}51 52do_malloc_test fkey_malloc-3 -sqlprep {53  PRAGMA foreign_keys = 1;54  CREATE TABLE t1(x INTEGER PRIMARY KEY);55  CREATE TABLE t2(y DEFAULT 14 REFERENCES t1(x) ON UPDATE SET DEFAULT);56  CREATE TABLE t3(y REFERENCES t1 ON UPDATE SET NULL);57  INSERT INTO t1 VALUES(13);58  INSERT INTO t2 VALUES(13);59  INSERT INTO t3 VALUES(13);60} -sqlbody {61  UPDATE t1 SET x = 14;62}63 64proc catch_fk_error {zSql} {65  set rc [catch {db eval $zSql} msg]66  if {$rc==0} {67    return $msg68  }69  if {[string match {*foreign key*} $msg]} {70    return ""71  }72  if {$msg eq "out of memory" 73   || $msg eq "FOREIGN KEY constraint failed"74   || $msg eq "constraint failed"75  } {76    error 177  }78  error $msg79}80 81do_malloc_test fkey_malloc-4 -sqlprep {82  PRAGMA foreign_keys = 1;83  CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE);84  CREATE TABLE t2(z REFERENCES t1(x), a REFERENCES t1(y));85  CREATE TABLE t3(x);86  CREATE TABLE t4(z REFERENCES t3);87  CREATE TABLE t5(x, y);88  CREATE TABLE t6(z REFERENCES t5(x));89  CREATE INDEX i51 ON t5(x);90  CREATE INDEX i52 ON t5(y, x);91  INSERT INTO t1 VALUES(1, 2);92} -tclbody {93  catch_fk_error {INSERT INTO t2 VALUES(1, 3)}94  catch_fk_error {INSERT INTO t4 VALUES(2)}95  catch_fk_error {INSERT INTO t6 VALUES(2)}96}97 98do_malloc_test fkey_malloc-5 -sqlprep {99  PRAGMA foreign_keys = 1;100  CREATE TABLE t1(x, y, PRIMARY KEY(x, y));101  CREATE TABLE t2(a, b, FOREIGN KEY(a, b) REFERENCES t1 ON UPDATE CASCADE);102  INSERT INTO t1 VALUES(1, 2);103  INSERT INTO t2 VALUES(1, 2);104} -sqlbody {105  UPDATE t1 SET x = 5;106}107 108do_malloc_test fkey_malloc-6 -sqlprep {109  PRAGMA foreign_keys = 1;110  CREATE TABLE t1(111    x PRIMARY KEY, 112    y REFERENCES t1 ON DELETE RESTRICT ON UPDATE SET DEFAULT113  );114  INSERT INTO t1 VALUES('abc', 'abc');115  INSERT INTO t1 VALUES('def', 'def');116} -sqlbody {117  INSERT INTO t1 VALUES('ghi', 'ghi');118  DELETE FROM t1 WHERE rowid>1;119  UPDATE t1 SET x='jkl', y='jkl';120}121 122do_malloc_test fkey_malloc-7 -sqlprep {123  PRAGMA foreign_keys = 1;124  CREATE TABLE x(a, b, PRIMARY KEY(a, b));125  CREATE TABLE y(c, d,126    FOREIGN KEY(d, c) REFERENCES x DEFERRABLE INITIALLY DEFERRED127  );128  CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x);129} -sqlbody {130  DROP TABLE y;131  DROP TABLE x;132}133 134finish_test135