From f589450dc9892a5cb486690cadbe2ba443eec4ef Mon Sep 17 00:00:00 2001 From: dan Date: Wed, 7 Oct 2009 18:41:19 +0000 Subject: [PATCH] Add some tests of statements in foreignkeys.html. FossilOrigin-Name: 8382867956caf20f62c46c15b456c1c16d0824fd --- manifest | 21 ++--- manifest.uuid | 2 +- src/sqliteLimit.h | 4 + src/test_config.c | 1 + src/vdbe.c | 2 +- test/e_fkey.test | 210 ++++++++++++++++++++++++++++++++++++++++++++++ test/fkey2.test | 11 --- test/tester.tcl | 14 ++++ 8 files changed, 242 insertions(+), 23 deletions(-) create mode 100644 test/e_fkey.test diff --git a/manifest b/manifest index a20991102d..5707cc2345 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Add\sa\smissing\sOP_Close\sopcode\sto\sVDBE\sprograms\sthat\scheck\sfor\sFK\sconstraint\sviolations. -D 2009-10-07T16:04:47 +C Add\ssome\stests\sof\sstatements\sin\sforeignkeys.html. +D 2009-10-07T18:41:20 F Makefile.arm-wince-mingw32ce-gcc fcd5e9cd67fe88836360bb4f9ef4cb7f8e2fb5a0 F Makefile.in 4ca3f1dd6efa2075bcb27f4dc43eef749877740d F Makefile.linux-gcc d53183f4aa6a9192d249731c90dbdffbd2c68654 @@ -165,7 +165,7 @@ F src/shell.c d6e64471aafb81f355262533393169a70529847a F src/sqlite.h.in 2c9fa83525b14fe3dda38146fb3005847d57a14e F src/sqlite3ext.h 1db7d63ab5de4b3e6b83dd03d1a4e64fef6d2a17 F src/sqliteInt.h 61c55f5f83c63813903f374e9b33173572f0559a -F src/sqliteLimit.h 504a3161886d2938cbd163054ad620b8356df758 +F src/sqliteLimit.h 38b2fffcd01faeaeaadea71b2b47695a81580c8b F src/status.c 237b193efae0cf6ac3f0817a208de6c6c6ef6d76 F src/table.c cc86ad3d6ad54df7c63a3e807b5783c90411a08d F src/tclsqlite.c 868d62910bc6b41c49554482bdcc1590efc01f3c @@ -182,7 +182,7 @@ F src/test_async.c 731d23f953ece5bf40ce87810cfb7607218953c5 F src/test_autoext.c f53b0cdf7bf5f08100009572a5d65cdb540bd0ad F src/test_backup.c 1384a18985a5a2d275c2662e48473bf1542ebd08 F src/test_btree.c 5adbba9b138988a3cf4d3b5424dbc7c85651da02 -F src/test_config.c 97d840717efcca8cad5e353debc69780ea8ad7ce +F src/test_config.c 4ac1e6257dcf926a71b7934410b71c5c326e68f2 F src/test_devsym.c 9f4bc2551e267ce7aeda195f3897d0f30c5228f4 F src/test_func.c 26ac62d8ed7a9f45a1e05baffb1c1e55fe2a06f2 F src/test_hexio.c 2f1122aa3f012fa0142ee3c36ce5c902a70cd12f @@ -206,7 +206,7 @@ F src/update.c 2c8a64237e4fae604468d14380b877d169545b63 F src/utf.c 99cf927eabb104621ba889ac0dd075fc1657ad30 F src/util.c 59d4e9456bf1fe581f415a783fa0cee6115c8f35 F src/vacuum.c 869d08eaab64e2a4eaf4ef9ea34b851892b65a75 -F src/vdbe.c cb9dae4c5c3706e5772756bb38710faa954f0bb9 +F src/vdbe.c df2824f4f8b38f313aea5f6ce651bff20eb29715 F src/vdbe.h 7d5075e3fa4e5587a9be8d5e503857c825490cef F src/vdbeInt.h 7afb76c0296f9a2310e565803fa66798ef47e9d5 F src/vdbeapi.c 524d79eb17bbcbe31c37c908b8e01edc5c684a90 @@ -319,6 +319,7 @@ F test/descidx2.test 1310ed1326cdfed4ea2c55169631579f082d174f F test/descidx3.test 3394ad4d089335cac743c36a14129d6d931c316f F test/diskfull.test 0cede7ef9d8f415d9d3944005c76be7589bb5ebb F test/distinctagg.test 1a6ef9c87a58669438fc771450d7a72577417376 +F test/e_fkey.test 0f520315537039d6e2c72285dca2a63ab26b5c48 F test/enc.test e54531cd6bf941ee6760be041dff19a104c7acea F test/enc2.test 6d91a5286f59add0cfcbb2d0da913b76f2242398 F test/enc3.test 5c550d59ff31dccdba5d1a02ae11c7047d77c041 @@ -330,7 +331,7 @@ F test/expr.test 9f521ae22f00e074959f72ce2e55d46b9ed23f68 F test/filectrl.test 8923a6dc7630f31c8a9dd3d3d740aa0922df7bf8 F test/filefmt.test 84e3d0fe9f12d0d2ac852465c6f8450aea0d6f43 F test/fkey1.test 01c7de578e11747e720c2d9aeef27f239853c4da -F test/fkey2.test 2220d7384624d792b692a94c65239f6359480312 +F test/fkey2.test add654160d1b066f7b866d63d4435954ccbcab7d F test/fkey3.test 42f88d6048d8dc079e2a8cf7baad1cc1483a7620 F test/fkey_malloc.test a5ede29bd2f6e56dea78c3d43fb86dd696c068c8 F test/format4.test 1f0cac8ff3895e9359ed87e41aaabee982a812eb @@ -574,7 +575,7 @@ F test/tclsqlite.test 8b1150d0486c4848c70d96422513a91c5342be0e F test/tempdb.test 1bf52da28a9c24e29717362a87722dff08feb72b F test/temptable.test f42121a0d29a62f00f93274464164177ab1cc24a F test/temptrigger.test b0273db072ce5f37cf19140ceb1f0d524bbe9f05 -F test/tester.tcl b22f925f3c6eea6283e29a81a7379091ca89ab40 +F test/tester.tcl 2caf7980d7dbb99dab9507ae0646802bc4d12c79 F test/thread001.test a3e6a7254d1cb057836cb3145b60c10bf5b7e60f F test/thread002.test afd20095e6e845b405df4f2c920cb93301ca69db F test/thread003.test b824d4f52b870ae39fc5bae4d8070eca73085dca @@ -755,7 +756,7 @@ F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e F tool/vdbe-compress.tcl d70ea6d8a19e3571d7ab8c9b75cba86d1173ff0f -P 2fd487fdb669df89a8eb62cd0b7dc27ea1016edf -R 9ddc4e2fa5a388e13b394f0c6d81b590 +P 5caa4a2b050d5743b1407485cf419aa36cf16ac6 +R 6e2bec7f14be2bc8a2ff167508eb9921 U dan -Z 7ec5b231b6f1f0bf46023d4eea263057 +Z 1105fea50b7c6c07e7cbb16d59bd372a diff --git a/manifest.uuid b/manifest.uuid index e010ae6354..f90eda0a20 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -5caa4a2b050d5743b1407485cf419aa36cf16ac6 \ No newline at end of file +8382867956caf20f62c46c15b456c1c16d0824fd \ No newline at end of file diff --git a/src/sqliteLimit.h b/src/sqliteLimit.h index c0244a348f..77d78589a9 100644 --- a/src/sqliteLimit.h +++ b/src/sqliteLimit.h @@ -191,6 +191,10 @@ /* ** 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) diff --git a/src/test_config.c b/src/test_config.c index f1d9413876..6340b5e269 100644 --- a/src/test_config.c +++ b/src/test_config.c @@ -537,6 +537,7 @@ Tcl_SetVar2(interp, "sqlite_options", "long_double", 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 ); diff --git a/src/vdbe.c b/src/vdbe.c index f5cd7ce77a..d0fef80fea 100644 --- a/src/vdbe.c +++ b/src/vdbe.c @@ -4822,7 +4822,7 @@ case OP_Program: { /* jump */ if( pFrame ) break; } - if( p->nFrame>db->aLimit[SQLITE_LIMIT_TRIGGER_DEPTH] ){ + if( p->nFrame>=db->aLimit[SQLITE_LIMIT_TRIGGER_DEPTH] ){ rc = SQLITE_ERROR; sqlite3SetString(&p->zErrMsg, db, "too many levels of trigger recursion"); break; diff --git a/test/e_fkey.test b/test/e_fkey.test new file mode 100644 index 0000000000..ba5d057ecf --- /dev/null +++ b/test/e_fkey.test @@ -0,0 +1,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 diff --git a/test/fkey2.test b/test/fkey2.test index 78ccddf697..dc6529be13 100644 --- a/test/fkey2.test +++ b/test/fkey2.test @@ -79,17 +79,6 @@ ifcapable {!foreignkey||!trigger} { # -proc drop_all_tables {{db db}} { - execsql { PRAGMA foreign_keys = OFF } - foreach {t type} [execsql { - SELECT name, type FROM sqlite_master - WHERE type IN('table', 'view') AND name NOT like 'sqlite_%' - }] { - execsql "DROP $type $t" - } - execsql { PRAGMA foreign_keys = ON } -} - execsql { PRAGMA foreign_keys = on } set FkeySimpleSchema { diff --git a/test/tester.tcl b/test/tester.tcl index d8047a5f86..7651017a25 100644 --- a/test/tester.tcl +++ b/test/tester.tcl @@ -958,6 +958,20 @@ proc copy_file {from to} { } } +# 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) -- 2.47.2