From bded90b4873a5e4e6d513f36dd0edc181004dfaa Mon Sep 17 00:00:00 2001 From: dan Date: Thu, 4 Jul 2013 15:22:53 +0000 Subject: [PATCH] Fix a bug preventing some FK constraint checking from being deferred until the end of changeset application. FossilOrigin-Name: 1452defb8cfcc489230314dd1e0425feba46c49d --- ext/session/session9.test | 124 +++++++++++++++++++++++++++++++++++++- manifest | 14 ++--- manifest.uuid | 2 +- src/fkey.c | 7 ++- 4 files changed, 135 insertions(+), 12 deletions(-) diff --git a/ext/session/session9.test b/ext/session/session9.test index 35d8e876c9..b6ead68991 100644 --- a/ext/session/session9.test +++ b/ext/session/session9.test @@ -23,8 +23,8 @@ set testprefix session9 #-------------------------------------------------------------------- +# Basic tests. # - proc populate_db {} { drop_all_tables execsql { @@ -130,5 +130,127 @@ foreach {tn open noclose close} { do_execsql_test 2.$tn.4 { PRAGMA defer_foreign_keys } {0} } +#-------------------------------------------------------------------- +# Test that a cyclic relationship can be inserted and deleted. +# +# This situation does not come up in practice, but testing it serves to +# show that it does not matter which order parent and child keys +# are processed in internally when applying a changeset. +# +drop_all_tables + +do_execsql_test 3.1 { + CREATE TABLE t1(a PRIMARY KEY, b); + CREATE TABLE t2(x PRIMARY KEY, y); +} + +# Create changesets as follows: +# +# $cc1 - Insert a row into t1. +# $cc2 - Insert a row into t2. +# $cc - Combination of $cc1 and $cc2. +# +# $ccdel1 - Delete the row from t1. +# $ccdel2 - Delete the row from t2. +# $ccdel - Combination of $cc1 and $cc2. +# +do_test 3.2 { + set cc1 [capture_changeset { + INSERT INTO t1 VALUES('one', 'value one'); + }] + set ccdel1 [capture_changeset { DELETE FROM t1; }] + set cc2 [capture_changeset { + INSERT INTO t2 VALUES('value one', 'one'); + }] + set ccdel2 [capture_changeset { DELETE FROM t2; }] + set cc [capture_changeset { + INSERT INTO t1 VALUES('one', 'value one'); + INSERT INTO t2 VALUES('value one', 'one'); + }] + set ccdel [capture_changeset { + DELETE FROM t1; + DELETE FROM t2; + }] + set {} {} +} {} + +# Now modify the database schema to create a cyclic foreign key dependency +# between tables t1 and t2. This means that although changesets $cc and +# $ccdel can be applied, none of the others may without violating the +# foreign key constraints. +# +do_test 3.3 { + + drop_all_tables + execsql { + CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t2); + CREATE TABLE t2(x PRIMARY KEY, y REFERENCES t1); + } + + + proc conflict_handler {args} { return "ABORT" } + sqlite3changeset_apply db $cc conflict_handler + + execsql { + SELECT * FROM t1; + SELECT * FROM t2; + } +} {one {value one} {value one} one} + +do_test 3.3.1 { + list [catch {sqlite3changeset_apply db $::ccdel1 conflict_handler} msg] $msg +} {1 SQLITE_CONSTRAINT} +do_test 3.3.2 { + list [catch {sqlite3changeset_apply db $::ccdel2 conflict_handler} msg] $msg +} {1 SQLITE_CONSTRAINT} + +do_test 3.3.4.1 { + list [catch {sqlite3changeset_apply db $::ccdel conflict_handler} msg] $msg +} {0 {}} +do_execsql_test 3.3.4.2 { + SELECT * FROM t1; + SELECT * FROM t2; +} {} + +do_test 3.5.1 { + list [catch {sqlite3changeset_apply db $::cc1 conflict_handler} msg] $msg +} {1 SQLITE_CONSTRAINT} +do_test 3.5.2 { + list [catch {sqlite3changeset_apply db $::cc2 conflict_handler} msg] $msg +} {1 SQLITE_CONSTRAINT} + +#-------------------------------------------------------------------- +# Test that if a change that affects FK processing is not applied +# due to a separate constraint, SQLite does not get confused and +# increment FK counters anyway. +# +drop_all_tables +do_execsql_test 4.1 { + CREATE TABLE p1(x PRIMARY KEY, y); + CREATE TABLE c1(a PRIMARY KEY, b REFERENCES p1); + INSERT INTO p1 VALUES(1,1); +} + +do_execsql_test 4.2.1 { + BEGIN; + PRAGMA defer_foreign_keys = 1; + INSERT INTO c1 VALUES('x', 'x'); +} +do_catchsql_test 4.2.2 { COMMIT } {1 {foreign key constraint failed}} +do_catchsql_test 4.2.3 { ROLLBACK } {0 {}} + +do_execsql_test 4.3.1 { + BEGIN; + PRAGMA defer_foreign_keys = 1; + INSERT INTO c1 VALUES(1, 1); +} +do_catchsql_test 4.3.2 { + INSERT INTO c1 VALUES(1, 'x') +} {1 {column a is not unique}} + +do_catchsql_test 4.3.3 { COMMIT } {0 {}} +do_catchsql_test 4.3.4 { BEGIN ; COMMIT } {0 {}} + finish_test + diff --git a/manifest b/manifest index cbf96b3093..05a751b481 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Experimental\schange\sto\sthe\shandling\sof\sforeign\skey\sconstraint\sviolations\swhen\sapplying\sa\schangeset:\sall\sforeign\skeys,\simmediate\sand\sdeferred,\sare\sdeferred\suntil\sthe\send\sof\sthe\stransaction\s(or\ssub-transaction)\sopened\sby\sthe\ssqlite3changeset_apply().\sA\ssingle\scall\sto\sthe\sconflict-handler\s(if\sany)\sis\smade\sif\sany\sFK\sconstraint\sviolations\sare\sstill\spresent\sin\sthe\sdatabase\sat\sthis\spoint.\sThe\sconflict-handler\smay\schoose\sto\srollback\sthe\schangeset,\sor\sto\sapply\sit,\sconstraint\sviolations\sand\sall. -D 2013-07-03T19:53:05.693 +C Fix\sa\sbug\spreventing\ssome\sFK\sconstraint\schecking\sfrom\sbeing\sdeferred\suntil\sthe\send\sof\schangeset\sapplication. +D 2013-07-04T15:22:53.953 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in aff38bc64c582dd147f18739532198372587b0f0 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -142,7 +142,7 @@ F ext/session/session4.test a6ed685da7a5293c5d6f99855bcf41dbc352ca84 F ext/session/session5.test 8fdfaf9dba28a2f1c6b89b06168bdab1fef2d478 F ext/session/session6.test 443789bc2fca12e4f7075cf692c60b8a2bea1a26 F ext/session/session8.test 7d35947ad329b8966f095d34f9617a9eff52dc65 -F ext/session/session9.test 3378ceace4e291dda8512b83d256be51aac5344e +F ext/session/session9.test 43acfdc57647c2ce4b36dbd0769112520ea6af14 F ext/session/session_common.tcl 1539d8973b2aea0025c133eb0cc4c89fcef541a5 F ext/session/sessionfault.test 496291b287ba3c0b14ca2e074425e29cc92a64a6 F ext/session/sqlite3session.c 80903fe2c24c8a9e7ccacebca1855a31ebebbbc3 @@ -186,7 +186,7 @@ F src/date.c 067a81c9942c497aafd2c260e13add8a7d0c7dd4 F src/delete.c 39a770e9729b1acd2de347f8f614584841d0083e F src/expr.c 2b47ae9da6c9f34eff6736962ea2e102c6c4a755 F src/fault.c 160a0c015b6c2629d3899ed2daf63d75754a32bb -F src/fkey.c ddd160ce0b81f092165f72d84c356d49c903a3ad +F src/fkey.c 87619fc3a9b6ab6877388bbcc05cdb136503786e F src/func.c 5c50c1ea31fd864b0fe921fe1a8d4c55acd609ef F src/global.c 5caf4deab621abb45b4c607aad1bd21c20aac759 F src/hash.c ac3470bbf1ca4ae4e306a8ecb0fdf1731810ffe4 @@ -1111,7 +1111,7 @@ F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh fbc018d67fd7395f440c28f33ef0f94420226381 F tool/wherecosttest.c f407dc4c79786982a475261866a161cd007947ae F tool/win/sqlite.vsix 97894c2790eda7b5bce3cc79cb2a8ec2fde9b3ac -P c2972b6aed23f6c76a289534de9ea4732a48f40e -R 1e1bb18e922bc4e9bb6b2e837b9cd1c8 +P 1d44e5d3c2b1dc958442f9114a960b256e002ed3 +R 28f75ac347bd281672cb0a8d27d3f6b3 U dan -Z 8c44f70721e5b391cc002040c9d1eb51 +Z 01296a7a6fdc241a613ea24758428ad2 diff --git a/manifest.uuid b/manifest.uuid index f8240dd8be..44d88b34c0 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -1d44e5d3c2b1dc958442f9114a960b256e002ed3 \ No newline at end of file +1452defb8cfcc489230314dd1e0425feba46c49d \ No newline at end of file diff --git a/src/fkey.c b/src/fkey.c index 0369bbc6f0..3f3c5d8ad6 100644 --- a/src/fkey.c +++ b/src/fkey.c @@ -422,10 +422,9 @@ static void fkLookupParent( } } - if( !pFKey->isDeferred + if( !pFKey->isDeferred && !(pParse->db->flags & SQLITE_DeferForeignKeys) && !pParse->pToplevel && !pParse->isMultiWrite - && !(pParse->db->flags & SQLITE_DeferForeignKeys) ){ /* Special case: If this is an INSERT statement that will insert exactly ** one row into the table, raise a constraint immediately instead of @@ -817,7 +816,9 @@ void sqlite3FkCheck( SrcList *pSrc; int *aiCol = 0; - if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){ + if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferForeignKeys) + && !pParse->pToplevel && !pParse->isMultiWrite + ){ assert( regOld==0 && regNew!=0 ); /* Inserting a single row into a parent table cannot cause an immediate ** foreign key violation. So do nothing in this case. */ -- 2.47.3