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} 33 34do_malloc_test fkey_malloc-2 -sqlprep { 35 PRAGMA foreign_keys = 1; 36 CREATE TABLE t1(a, b, UNIQUE(a, b)); 37} -sqlbody { 38 CREATE TABLE t2(x, y, 39 FOREIGN KEY(x, y) REFERENCES t1(a, b) DEFERRABLE INITIALLY DEFERRED 40 ); 41 BEGIN; 42 INSERT INTO t2 VALUES('a', 'b'); 43 INSERT INTO t1 VALUES('a', 'b'); 44 UPDATE t1 SET a = 'c'; 45 DELETE FROM t2; 46 INSERT INTO t2 VALUES('d', 'b'); 47 UPDATE t2 SET x = 'c'; 48 COMMIT; 49} 50 51do_malloc_test fkey_malloc-3 -sqlprep { 52 PRAGMA foreign_keys = 1; 53 CREATE TABLE t1(x INTEGER PRIMARY KEY); 54 CREATE TABLE t2(y DEFAULT 14 REFERENCES t1(x) ON UPDATE SET DEFAULT); 55 CREATE TABLE t3(y REFERENCES t1 ON UPDATE SET NULL); 56 INSERT INTO t1 VALUES(13); 57 INSERT INTO t2 VALUES(13); 58 INSERT INTO t3 VALUES(13); 59} -sqlbody { 60 UPDATE t1 SET x = 14; 61} 62 63proc catch_fk_error {zSql} { 64 set rc [catch {db eval $zSql} msg] 65 if {$rc==0} { 66 return $msg 67 } 68 if {[string match {*foreign key*} $msg]} { 69 return "" 70 } 71 if {$msg eq "out of memory"} { 72 error 1 73 } 74 error $msg 75} 76 77do_malloc_test fkey_malloc-4 -sqlprep { 78 PRAGMA foreign_keys = 1; 79 CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE); 80 CREATE TABLE t2(z REFERENCES t1(x), a REFERENCES t1(y)); 81 CREATE TABLE t3(x); 82 CREATE TABLE t4(z REFERENCES t3); 83 CREATE TABLE t5(x, y); 84 CREATE TABLE t6(z REFERENCES t5(x)); 85 CREATE INDEX i51 ON t5(x); 86 CREATE INDEX i52 ON t5(y, x); 87 INSERT INTO t1 VALUES(1, 2); 88} -tclbody { 89 catch_fk_error {INSERT INTO t2 VALUES(1, 3)} 90 catch_fk_error {INSERT INTO t4 VALUES(2)} 91 catch_fk_error {INSERT INTO t6 VALUES(2)} 92} 93 94do_malloc_test fkey_malloc-5 -sqlprep { 95 PRAGMA foreign_keys = 1; 96 CREATE TABLE t1(x, y, PRIMARY KEY(x, y)); 97 CREATE TABLE t2(a, b, FOREIGN KEY(a, b) REFERENCES t1 ON UPDATE CASCADE); 98 INSERT INTO t1 VALUES(1, 2); 99 INSERT INTO t2 VALUES(1, 2); 100} -sqlbody { 101 UPDATE t1 SET x = 5; 102} 103 104do_malloc_test fkey_malloc-6 -sqlprep { 105 PRAGMA foreign_keys = 1; 106 CREATE TABLE t1( 107 x PRIMARY KEY, 108 y REFERENCES t1 ON DELETE RESTRICT ON UPDATE SET DEFAULT 109 ); 110 INSERT INTO t1 VALUES('abc', 'abc'); 111 INSERT INTO t1 VALUES('def', 'def'); 112} -sqlbody { 113 INSERT INTO t1 VALUES('ghi', 'ghi'); 114 DELETE FROM t1 WHERE rowid>1; 115 UPDATE t1 SET x='jkl', y='jkl'; 116} 117 118do_malloc_test fkey_malloc-7 -sqlprep { 119 PRAGMA foreign_keys = 1; 120 CREATE TABLE x(a, b, PRIMARY KEY(a, b)); 121 CREATE TABLE y(c, d, 122 FOREIGN KEY(d, c) REFERENCES x DEFERRABLE INITIALLY DEFERRED 123 ); 124 CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x); 125} -sqlbody { 126 DROP TABLE y; 127 DROP TABLE x; 128} 129 130finish_test 131 132 133