Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add some tests of statements in foreignkeys.html. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
8382867956caf20f62c46c15b456c1c1 |
| User & Date: | dan 2009-10-07 18:41:20 |
Context
|
2009-10-07
| ||
| 23:42 | Use memcpy() rather than structure assignment so that memcmp() can later be used for comparison. Ticket [8550ecca70] check-in: 56f609da user: drh tags: trunk | |
| 18:41 | Add some tests of statements in foreignkeys.html. check-in: 83828679 user: dan tags: trunk | |
| 16:04 | Add a missing OP_Close opcode to VDBE programs that check for FK constraint violations. check-in: 5caa4a2b user: dan tags: trunk | |
Changes
Changes to src/sqliteLimit.h.
187 187 */ 188 188 #ifndef SQLITE_MAX_LIKE_PATTERN_LENGTH 189 189 # define SQLITE_MAX_LIKE_PATTERN_LENGTH 50000 190 190 #endif 191 191 192 192 /* 193 193 ** Maximum depth of recursion for triggers. 194 +** 195 +** A value of 1 means that a trigger program will not be able to itself 196 +** fire any triggers. A value of 0 means that no trigger programs at all 197 +** may be executed. 194 198 */ 195 199 #ifndef SQLITE_MAX_TRIGGER_DEPTH 196 200 #if defined(SQLITE_SMALL_STACK) 197 201 # define SQLITE_MAX_TRIGGER_DEPTH 10 198 202 #else 199 203 # define SQLITE_MAX_TRIGGER_DEPTH 1000 200 204 #endif 201 205 #endif
Changes to src/test_config.c.
533 533 LINKVAR( MAX_COMPOUND_SELECT ); 534 534 LINKVAR( MAX_VDBE_OP ); 535 535 LINKVAR( MAX_FUNCTION_ARG ); 536 536 LINKVAR( MAX_VARIABLE_NUMBER ); 537 537 LINKVAR( MAX_PAGE_SIZE ); 538 538 LINKVAR( MAX_PAGE_COUNT ); 539 539 LINKVAR( MAX_LIKE_PATTERN_LENGTH ); 540 + LINKVAR( MAX_TRIGGER_DEPTH ); 540 541 LINKVAR( DEFAULT_TEMP_CACHE_SIZE ); 541 542 LINKVAR( DEFAULT_CACHE_SIZE ); 542 543 LINKVAR( DEFAULT_PAGE_SIZE ); 543 544 LINKVAR( DEFAULT_FILE_FORMAT ); 544 545 LINKVAR( MAX_ATTACHED ); 545 546 546 547 {
Changes to src/vdbe.c.
4818 4818 ** variable. */ 4819 4819 if( pOp->p5 ){ 4820 4820 t = pProgram->token; 4821 4821 for(pFrame=p->pFrame; pFrame && pFrame->token!=t; pFrame=pFrame->pParent); 4822 4822 if( pFrame ) break; 4823 4823 } 4824 4824 4825 - if( p->nFrame>db->aLimit[SQLITE_LIMIT_TRIGGER_DEPTH] ){ 4825 + if( p->nFrame>=db->aLimit[SQLITE_LIMIT_TRIGGER_DEPTH] ){ 4826 4826 rc = SQLITE_ERROR; 4827 4827 sqlite3SetString(&p->zErrMsg, db, "too many levels of trigger recursion"); 4828 4828 break; 4829 4829 } 4830 4830 4831 4831 /* Register pRt is used to store the memory required to save the state 4832 4832 ** of the current program, and the memory required at runtime to execute
Added test/e_fkey.test.
1 +# 2009 October 7 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 +# This file implements tests to verify the "testable statements" in the 13 +# foreignkeys.in document. 14 +# 15 + 16 +set testdir [file dirname $argv0] 17 +source $testdir/tester.tcl 18 +ifcapable {!foreignkey} { finish_test ; return } 19 + 20 +execsql "PRAGMA foreign_keys = ON" 21 + 22 +#------------------------------------------------------------------------- 23 +# /* EV: R-24728-13230 */ 24 +# /* EV: R-24450-46174 */ 25 +# 26 +# Test that MATCH clauses are parsed, but SQLite treats every foreign key 27 +# constraint as if it were "MATCH SIMPLE". 28 +# 29 +foreach zMatch [list SIMPLE PARTIAL FULL Simple parTIAL FuLL ] { 30 + drop_all_tables 31 + do_test e_fkey-1.$zMatch.1 { 32 + execsql " 33 + CREATE TABLE p(a, b, c, PRIMARY KEY(b, c)); 34 + CREATE TABLE c(d, e, f, FOREIGN KEY(e, f) REFERENCES p MATCH $zMatch); 35 + " 36 + } {} 37 + do_test e_fkey-1.$zMatch.2 { 38 + execsql { INSERT INTO p VALUES(1, 2, 3) } 39 + 40 + # MATCH SIMPLE behaviour: Allow any child key that contains one or more 41 + # NULL value to be inserted. Non-NULL values do not have to map to any 42 + # parent key values, so long as at least one field of the child key is 43 + # NULL. 44 + execsql { INSERT INTO c VALUES('w', 2, 3) } 45 + execsql { INSERT INTO c VALUES('x', 'x', NULL) } 46 + execsql { INSERT INTO c VALUES('y', NULL, 'x') } 47 + execsql { INSERT INTO c VALUES('z', NULL, NULL) } 48 + 49 + # Check that the FK is enforced properly if there are no NULL values 50 + # in the child key columns. 51 + catchsql { INSERT INTO c VALUES('a', 2, 4) } 52 + } {1 {foreign key constraint failed}} 53 +} 54 + 55 +#------------------------------------------------------------------------- 56 +# /* EV: R-21599-16038 */ 57 +# 58 +# Test that SQLite does not support the SET CONSTRAINT statement. And 59 +# that it is possible to create both immediate and deferred constraints. 60 +# 61 +drop_all_tables 62 +do_test e_fkey-2.1 { 63 + catchsql { SET CONSTRAINTS ALL IMMEDIATE } 64 +} {1 {near "SET": syntax error}} 65 +do_test e_fkey-2.2 { 66 + catchsql { SET CONSTRAINTS ALL DEFERRED } 67 +} {1 {near "SET": syntax error}} 68 + 69 +do_test e_fkey-2.3 { 70 + execsql { 71 + CREATE TABLE p(a, b, PRIMARY KEY(a, b)); 72 + CREATE TABLE cd(c, d, 73 + FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY DEFERRED); 74 + CREATE TABLE ci(c, d, 75 + FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY IMMEDIATE); 76 + BEGIN; 77 + } 78 +} {} 79 +do_test e_fkey-2.4 { 80 + catchsql { INSERT INTO ci VALUES('x', 'y') } 81 +} {1 {foreign key constraint failed}} 82 +do_test e_fkey-2.5 { 83 + catchsql { INSERT INTO cd VALUES('x', 'y') } 84 +} {0 {}} 85 +do_test e_fkey-2.6 { 86 + catchsql { COMMIT } 87 +} {1 {foreign key constraint failed}} 88 +do_test e_fkey-2.7 { 89 + execsql { 90 + DELETE FROM cd; 91 + COMMIT; 92 + } 93 +} {} 94 + 95 +#------------------------------------------------------------------------- 96 +# /* EV: R-42264-30503 */ 97 +# 98 +# Test that the maximum recursion depth of foreign key action programs is 99 +# governed by the SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH 100 +# settings. 101 +# 102 +proc test_on_delete_recursion {limit} { 103 + drop_all_tables 104 + execsql { 105 + BEGIN; 106 + CREATE TABLE t0(a PRIMARY KEY, b); 107 + INSERT INTO t0 VALUES('x0', NULL); 108 + } 109 + for {set i 1} {$i <= $limit} {incr i} { 110 + execsql " 111 + CREATE TABLE t$i ( 112 + a PRIMARY KEY, b REFERENCES t[expr $i-1] ON DELETE CASCADE 113 + ); 114 + INSERT INTO t$i VALUES('x$i', 'x[expr $i-1]'); 115 + " 116 + } 117 + execsql COMMIT 118 + catchsql " 119 + DELETE FROM t0; 120 + SELECT count(*) FROM t$limit; 121 + " 122 +} 123 +proc test_on_update_recursion {limit} { 124 + drop_all_tables 125 + execsql { 126 + BEGIN; 127 + CREATE TABLE t0(a PRIMARY KEY); 128 + INSERT INTO t0 VALUES('xxx'); 129 + } 130 + for {set i 1} {$i <= $limit} {incr i} { 131 + set j [expr $i-1] 132 + 133 + execsql " 134 + CREATE TABLE t$i (a PRIMARY KEY REFERENCES t$j ON UPDATE CASCADE); 135 + INSERT INTO t$i VALUES('xxx'); 136 + " 137 + } 138 + execsql COMMIT 139 + catchsql " 140 + UPDATE t0 SET a = 'yyy'; 141 + SELECT NOT (a='yyy') FROM t$limit; 142 + " 143 +} 144 + 145 +do_test e_fkey-3.1.1 { 146 + test_on_delete_recursion $SQLITE_MAX_TRIGGER_DEPTH 147 +} {0 0} 148 +do_test e_fkey-3.1.2 { 149 + test_on_delete_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1] 150 +} {1 {too many levels of trigger recursion}} 151 +do_test e_fkey-3.1.3 { 152 + sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5 153 + test_on_delete_recursion 5 154 +} {0 0} 155 +do_test e_fkey-3.1.4 { 156 + test_on_delete_recursion 6 157 +} {1 {too many levels of trigger recursion}} 158 +do_test e_fkey-3.1.5 { 159 + sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000 160 +} {5} 161 +do_test e_fkey-3.2.1 { 162 + test_on_update_recursion $SQLITE_MAX_TRIGGER_DEPTH 163 +} {0 0} 164 +do_test e_fkey-3.2.2 { 165 + test_on_update_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1] 166 +} {1 {too many levels of trigger recursion}} 167 +do_test e_fkey-3.2.3 { 168 + sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5 169 + test_on_update_recursion 5 170 +} {0 0} 171 +do_test e_fkey-3.2.4 { 172 + test_on_update_recursion 6 173 +} {1 {too many levels of trigger recursion}} 174 +do_test e_fkey-3.2.5 { 175 + sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000 176 +} {5} 177 + 178 +#------------------------------------------------------------------------- 179 +# /* EV: R-51769-32730 */ 180 +# 181 +# The setting of the recursive_triggers pragma does not affect foreign 182 +# key actions. 183 +# 184 +foreach recursive_triggers_setting [list 0 1 ON OFF] { 185 + drop_all_tables 186 + execsql "PRAGMA recursive_triggers = $recursive_triggers_setting" 187 + 188 + do_test e_fkey-4.$recursive_triggers_setting.1 { 189 + execsql { 190 + CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1 ON DELETE CASCADE); 191 + INSERT INTO t1 VALUES(1, NULL); 192 + INSERT INTO t1 VALUES(2, 1); 193 + INSERT INTO t1 VALUES(3, 2); 194 + INSERT INTO t1 VALUES(4, 3); 195 + INSERT INTO t1 VALUES(5, 4); 196 + SELECT count(*) FROM t1; 197 + } 198 + } {5} 199 + do_test e_fkey-4.$recursive_triggers_setting.2 { 200 + execsql { SELECT count(*) FROM t1 WHERE a = 1 } 201 + } {1} 202 + do_test e_fkey-4.$recursive_triggers_setting.3 { 203 + execsql { 204 + DELETE FROM t1 WHERE a = 1; 205 + SELECT count(*) FROM t1; 206 + } 207 + } {0} 208 +} 209 + 210 +finish_test
Changes to test/fkey2.test.
75 75 # FK constraints. 76 76 # 77 77 # fkey2-genfkey.*: Tests that were used with the shell tool .genfkey 78 78 # command. Recycled to test the built-in implementation. 79 79 # 80 80 81 81 82 -proc drop_all_tables {{db db}} { 83 - execsql { PRAGMA foreign_keys = OFF } 84 - foreach {t type} [execsql { 85 - SELECT name, type FROM sqlite_master 86 - WHERE type IN('table', 'view') AND name NOT like 'sqlite_%' 87 - }] { 88 - execsql "DROP $type $t" 89 - } 90 - execsql { PRAGMA foreign_keys = ON } 91 -} 92 - 93 82 execsql { PRAGMA foreign_keys = on } 94 83 95 84 set FkeySimpleSchema { 96 85 PRAGMA foreign_keys = on; 97 86 CREATE TABLE t1(a PRIMARY KEY, b); 98 87 CREATE TABLE t2(c REFERENCES t1(a) /D/ , d); 99 88
Changes to test/tester.tcl.
953 953 set t [open $to w] 954 954 fconfigure $t -translation binary 955 955 puts -nonewline $t [read $f [file size $from]] 956 956 close $t 957 957 close $f 958 958 } 959 959 } 960 + 961 +# Drop all tables in database [db] 962 +proc drop_all_tables {{db db}} { 963 + set pk [$db one "PRAGMA foreign_keys"] 964 + $db eval "PRAGMA foreign_keys = OFF" 965 + foreach {t type} [$db eval { 966 + SELECT name, type FROM sqlite_master 967 + WHERE type IN('table', 'view') AND name NOT like 'sqlite_%' 968 + }] { 969 + $db eval "DROP $type $t" 970 + } 971 + $db eval " PRAGMA foreign_keys = $pk " 972 +} 973 + 960 974 961 975 # If the library is compiled with the SQLITE_DEFAULT_AUTOVACUUM macro set 962 976 # to non-zero, then set the global variable $AUTOVACUUM to 1. 963 977 set AUTOVACUUM $sqlite_options(default_autovacuum) 964 978 965 979 source $testdir/thread_common.tcl