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" || $msg eq "constraint failed"} { 73 error 1 74 } 75 error $msg 76} 77 78do_malloc_test fkey_malloc-4 -sqlprep { 79 PRAGMA foreign_keys = 1; 80 CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE); 81 CREATE TABLE t2(z REFERENCES t1(x), a REFERENCES t1(y)); 82 CREATE TABLE t3(x); 83 CREATE TABLE t4(z REFERENCES t3); 84 CREATE TABLE t5(x, y); 85 CREATE TABLE t6(z REFERENCES t5(x)); 86 CREATE INDEX i51 ON t5(x); 87 CREATE INDEX i52 ON t5(y, x); 88 INSERT INTO t1 VALUES(1, 2); 89} -tclbody { 90 catch_fk_error {INSERT INTO t2 VALUES(1, 3)} 91 catch_fk_error {INSERT INTO t4 VALUES(2)} 92 catch_fk_error {INSERT INTO t6 VALUES(2)} 93} 94 95do_malloc_test fkey_malloc-5 -sqlprep { 96 PRAGMA foreign_keys = 1; 97 CREATE TABLE t1(x, y, PRIMARY KEY(x, y)); 98 CREATE TABLE t2(a, b, FOREIGN KEY(a, b) REFERENCES t1 ON UPDATE CASCADE); 99 INSERT INTO t1 VALUES(1, 2); 100 INSERT INTO t2 VALUES(1, 2); 101} -sqlbody { 102 UPDATE t1 SET x = 5; 103} 104 105do_malloc_test fkey_malloc-6 -sqlprep { 106 PRAGMA foreign_keys = 1; 107 CREATE TABLE t1( 108 x PRIMARY KEY, 109 y REFERENCES t1 ON DELETE RESTRICT ON UPDATE SET DEFAULT 110 ); 111 INSERT INTO t1 VALUES('abc', 'abc'); 112 INSERT INTO t1 VALUES('def', 'def'); 113} -sqlbody { 114 INSERT INTO t1 VALUES('ghi', 'ghi'); 115 DELETE FROM t1 WHERE rowid>1; 116 UPDATE t1 SET x='jkl', y='jkl'; 117} 118 119do_malloc_test fkey_malloc-7 -sqlprep { 120 PRAGMA foreign_keys = 1; 121 CREATE TABLE x(a, b, PRIMARY KEY(a, b)); 122 CREATE TABLE y(c, d, 123 FOREIGN KEY(d, c) REFERENCES x DEFERRABLE INITIALLY DEFERRED 124 ); 125 CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x); 126} -sqlbody { 127 DROP TABLE y; 128 DROP TABLE x; 129} 130 131finish_test 132