Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a bug preventing some FK constraint checking from being deferred until the end of changeset application. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | sessions |
Files: | files | file ages | folders |
SHA1: |
1452defb8cfcc489230314dd1e0425fe |
User & Date: | dan 2013-07-04 15:22:53.953 |
Context
2013-07-09
| ||
13:05 | Pull in all the latest changes from trunk. (check-in: af3ca4c6e5 user: drh tags: sessions) | |
2013-07-04
| ||
15:22 | Fix a bug preventing some FK constraint checking from being deferred until the end of changeset application. (check-in: 1452defb8c user: dan tags: sessions) | |
2013-07-03
| ||
19:53 | Experimental change to the handling of foreign key constraint violations when applying a changeset: all foreign keys, immediate and deferred, are deferred until the end of the transaction (or sub-transaction) opened by the sqlite3changeset_apply(). A single call to the conflict-handler (if any) is made if any FK constraint violations are still present in the database at this point. The conflict-handler may choose to rollback the changeset or to apply it, constraint violations and all. (check-in: 1d44e5d3c2 user: dan tags: sessions) | |
Changes
Changes to ext/session/session9.test.
︙ | ︙ | |||
19 20 21 22 23 24 25 | source [file join [file dirname [info script]] session_common.tcl] source $testdir/tester.tcl ifcapable !session {finish_test; return} set testprefix session9 #-------------------------------------------------------------------- | | | | 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | source [file join [file dirname [info script]] session_common.tcl] source $testdir/tester.tcl ifcapable !session {finish_test; return} set testprefix session9 #-------------------------------------------------------------------- # Basic tests. # proc populate_db {} { drop_all_tables execsql { PRAGMA foreign_keys = 1; CREATE TABLE p1(a PRIMARY KEY, b); CREATE TABLE c1(a PRIMARY KEY, b REFERENCES p1); CREATE TABLE c2(a PRIMARY KEY, |
︙ | ︙ | |||
126 127 128 129 130 131 132 133 134 | execsql $noclose do_execsql_test 2.$tn.3 { PRAGMA defer_foreign_keys } {1} execsql $close do_execsql_test 2.$tn.4 { PRAGMA defer_foreign_keys } {0} } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 | execsql $noclose do_execsql_test 2.$tn.3 { PRAGMA defer_foreign_keys } {1} execsql $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 |
Changes to src/fkey.c.
︙ | ︙ | |||
418 419 420 421 422 423 424 | sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, regRec, 0); sqlite3ReleaseTempReg(pParse, regRec); sqlite3ReleaseTempRange(pParse, regTemp, nCol); } } | | < | 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 | sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, regRec, 0); sqlite3ReleaseTempReg(pParse, regRec); sqlite3ReleaseTempRange(pParse, regTemp, nCol); } } if( !pFKey->isDeferred && !(pParse->db->flags & SQLITE_DeferForeignKeys) && !pParse->pToplevel && !pParse->isMultiWrite ){ /* Special case: If this is an INSERT statement that will insert exactly ** one row into the table, raise a constraint immediately instead of ** incrementing a counter. This is necessary as the VM code is being ** generated for will not open a statement transaction. */ assert( nIncr==1 ); sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY, |
︙ | ︙ | |||
813 814 815 816 817 818 819 | /* Loop through all the foreign key constraints that refer to this table */ for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ Index *pIdx = 0; /* Foreign key index for pFKey */ SrcList *pSrc; int *aiCol = 0; | > | > | 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 | /* Loop through all the foreign key constraints that refer to this table */ for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ Index *pIdx = 0; /* Foreign key index for pFKey */ SrcList *pSrc; int *aiCol = 0; 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. */ continue; } if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){ |
︙ | ︙ |