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