1# 2009 September 22 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# 13 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16 17ifcapable !foreignkey||!trigger { 18 finish_test 19 return 20} 21source $testdir/malloc_common.tcl 22 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 DEFERRED 41 ); 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 $msg 68 } 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 1 77 } 78 error $msg 79} 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 DEFAULT 113 ); 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 DEFERRED 127 ); 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_test 135