From 432cc5b9f37f12bcf5dbdaba238f33717b21e682 Mon Sep 17 00:00:00 2001 From: dan Date: Sat, 26 Sep 2009 17:51:48 +0000 Subject: [PATCH] When ALTER TABLE RENAME TO is used to change the name of a table that is the parent table of a foreign key constraint, modify that foreign key constraint to use the new table name. FossilOrigin-Name: b4a10c39e726dc190e9597e382baddc034294114 --- manifest | 18 +++--- manifest.uuid | 2 +- src/alter.c | 145 +++++++++++++++++++++++++++++++++++++++++++++--- src/fkey.c | 10 ++-- src/sqliteInt.h | 1 + test/fkey2.test | 55 ++++++++++++++++++ 6 files changed, 207 insertions(+), 24 deletions(-) diff --git a/manifest b/manifest index 5a1ee5aca2..741394e6dc 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Avoid\schecking\sif\san\sinsert\sor\sdelete\shas\s"fixed"\san\soutstanding\sFK\sconstraint\sviolation\sif\sthe\sconstraint\scounter\sindicates\sthat\sthe\sdatabase\scontains\sno\ssuch\sviolations. -D 2009-09-25T17:03:14 +C When\sALTER\sTABLE\sRENAME\sTO\sis\sused\sto\schange\sthe\sname\sof\sa\stable\sthat\sis\sthe\sparent\stable\sof\sa\sforeign\skey\sconstraint,\smodify\sthat\sforeign\skey\sconstraint\sto\suse\sthe\snew\stable\sname. +D 2009-09-26T17:51:48 F Makefile.arm-wince-mingw32ce-gcc fcd5e9cd67fe88836360bb4f9ef4cb7f8e2fb5a0 F Makefile.in 4ca3f1dd6efa2075bcb27f4dc43eef749877740d F Makefile.linux-gcc d53183f4aa6a9192d249731c90dbdffbd2c68654 @@ -99,7 +99,7 @@ F spec.template 86a4a43b99ebb3e75e6b9a735d5fd293a24e90ca F sqlite.pc.in 42b7bf0d02e08b9e77734a47798d1a55a9e0716b F sqlite3.1 6be1ad09113570e1fc8dcaff84c9b0b337db5ffc F sqlite3.pc.in ae6f59a76e862f5c561eb32a380228a02afc3cad -F src/alter.c e771528da07315e0416bf9788399fb3dc2fecb5e +F src/alter.c 082657ce3a584a512ed472e3163c09ed399e17f4 F src/analyze.c 5a8b8aa3d170eac5e71af45458cec61f83c623ee F src/attach.c 13995348fc5a26cdd136a50806faf292aabc173f F src/auth.c d6a88bf8e81e6a079ccb8881002b327e42ec46b9 @@ -116,7 +116,7 @@ F src/date.c 657ff12ca0f1195b531561afacbb38b772d16638 F src/delete.c 2a3d6fc0861b2f8dbd9feb7847b390267b281c60 F src/expr.c c7f3f718bd5c392344ec8694a41c1824f30cf375 F src/fault.c dc88c821842157460750d2d61a8a8b4197d047ff -F src/fkey.c 542de974e3ed3a507f00b68d1486a4cbeaf4d8bb +F src/fkey.c ee4e0d2465c2c092b2b6ab4be7e33ae33d6c6dac F src/func.c e536218d193b8d326aab91120bc4c6f28aa2b606 F src/global.c 271952d199a8cc59d4ce840b3bbbfd2f30c8ba32 F src/hash.c ebcaa921ffd9d86f7ea5ae16a0a29d1c871130a7 @@ -164,7 +164,7 @@ F src/select.c 1d0a13137532321b4364f964e46f057d271691e3 F src/shell.c d6e64471aafb81f355262533393169a70529847a F src/sqlite.h.in 5af8181f815831a8672c3834c60e6b4418448bcc F src/sqlite3ext.h 1db7d63ab5de4b3e6b83dd03d1a4e64fef6d2a17 -F src/sqliteInt.h 7dcffff0e91ff07e3f7752c4d8e87f9582443a20 +F src/sqliteInt.h 21510d10a6794c633e044e8396acb7899396a9e8 F src/sqliteLimit.h 504a3161886d2938cbd163054ad620b8356df758 F src/status.c 237b193efae0cf6ac3f0817a208de6c6c6ef6d76 F src/table.c cc86ad3d6ad54df7c63a3e807b5783c90411a08d @@ -330,7 +330,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 4b22c954c3923ffe366feb664b33b1b438abb5b1 +F test/fkey2.test 13e99ef0cafe6947638f748aea7b1a7ee8aecb1a F test/fkey3.test 2183cac9075f3aae4875106eb9255bb73618444e F test/fkey_malloc.test da912d000bb6ceb1cd11b655de1989762fa71ceb F test/format4.test 1f0cac8ff3895e9359ed87e41aaabee982a812eb @@ -755,7 +755,7 @@ F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e F tool/vdbe-compress.tcl d70ea6d8a19e3571d7ab8c9b75cba86d1173ff0f -P 9fd54b0aa73ed74c65f7db53cb666752f13263f9 -R 5e90fcf7b135a7c55fc18c54261d65ee +P 519144ac437b5842e4213f0e81e05c709939c2ab +R 67b182d615cf1918bbb959f1c1561217 U dan -Z 1aaee7b75a94b33f2eb5b3e61baf845b +Z 1c73bf12a1b7498f5704501d82682485 diff --git a/manifest.uuid b/manifest.uuid index 39275a4071..eeb2dc1946 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -519144ac437b5842e4213f0e81e05c709939c2ab \ No newline at end of file +b4a10c39e726dc190e9597e382baddc034294114 \ No newline at end of file diff --git a/src/alter.c b/src/alter.c index 60c90fc2b6..875a448c95 100644 --- a/src/alter.c +++ b/src/alter.c @@ -85,6 +85,67 @@ static void renameTableFunc( } } +/* +** This C function implements an SQL user function that is used by SQL code +** generated by the ALTER TABLE ... RENAME command to modify the definition +** of any foreign key constraints that use the table being renamed as the +** parent table. It is passed three arguments: +** +** 1) The complete text of the CREATE TABLE statement being modified, +** 2) The old name of the table being renamed, and +** 3) The new name of the table being renamed. +** +** It returns the new CREATE TABLE statement. For example: +** +** sqlite_rename_parent('CREATE TABLE t1(a REFERENCES t2)', 't2', 't3') +** -> 'CREATE TABLE t1(a REFERENCES t3)' +*/ +#ifndef SQLITE_OMIT_FOREIGN_KEY +static void renameParentFunc( + sqlite3_context *context, + int NotUsed, + sqlite3_value **argv +){ + sqlite3 *db = sqlite3_context_db_handle(context); + char *zOutput = 0; + char *zResult; + unsigned char const *zInput = sqlite3_value_text(argv[0]); + unsigned char const *zOld = sqlite3_value_text(argv[1]); + unsigned char const *zNew = sqlite3_value_text(argv[2]); + + unsigned const char *z; /* Pointer to token */ + int n; /* Length of token z */ + int token; /* Type of token */ + + for(z=zInput; *z; z=z+n){ + n = sqlite3GetToken(z, &token); + if( token==TK_REFERENCES ){ + char *zParent; + do { + z += n; + n = sqlite3GetToken(z, &token); + }while( token==TK_SPACE ); + + zParent = sqlite3DbStrNDup(db, (const char *)z, n); + sqlite3Dequote(zParent); + if( 0==sqlite3StrICmp((const char *)zOld, zParent) ){ + char *zOut = sqlite3MPrintf(db, "%s%.*s\"%w\"", + (zOutput?zOutput:""), z-zInput, zInput, (const char *)zNew + ); + sqlite3DbFree(db, zOutput); + zOutput = zOut; + zInput = &z[n]; + } + sqlite3DbFree(db, zParent); + } + } + + zResult = sqlite3MPrintf(db, "%s%s", (zOutput?zOutput:""), zInput), + sqlite3_result_text(context, zResult, -1, SQLITE_DYNAMIC); + sqlite3DbFree(db, zOutput); +} +#endif + #ifndef SQLITE_OMIT_TRIGGER /* This function is used by SQL generated to implement the ** ALTER TABLE command. The first argument is the text of a CREATE TRIGGER @@ -172,6 +233,52 @@ void sqlite3AlterFunctions(sqlite3 *db){ sqlite3CreateFunc(db, "sqlite_rename_trigger", 2, SQLITE_UTF8, 0, renameTriggerFunc, 0, 0); #endif +#ifndef SQLITE_OMIT_FOREIGN_KEY + sqlite3CreateFunc(db, "sqlite_rename_parent", 3, SQLITE_UTF8, 0, + renameParentFunc, 0, 0); +#endif +} + +/* +** This function is used to create the text of expressions of the form: +** +** name= OR name= OR ... +** +** If argument zWhere is NULL, then a pointer string containing the text +** "name=" is returned, where is the quoted version +** of the string passed as argument zConstant. The returned buffer is +** allocated using sqlite3DbMalloc(). It is the responsibility of the +** caller to ensure that it is eventually freed. +** +** If argument zWhere is not NULL, then the string returned is +** " OR name=", where is the contents of zWhere. +** In this case zWhere is passed to sqlite3DbFree() before returning. +** +*/ +static char *whereOrName(sqlite3 *db, char *zWhere, char *zConstant){ + char *zNew; + if( !zWhere ){ + zNew = sqlite3MPrintf(db, "name=%Q", zConstant); + }else{ + zNew = sqlite3MPrintf(db, "%s OR name=%Q", zWhere, zConstant); + sqlite3DbFree(db, zWhere); + } + return zNew; +} + +/* +** Generate the text of a WHERE expression which can be used to select all +** tables that have foreign key constraints that refer to table pTab (i.e. +** constraints for which pTab is the parent table) from the sqlite_master +** table. +*/ +static char *whereForeignKeys(Parse *pParse, Table *pTab){ + FKey *p; + char *zWhere = 0; + for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){ + zWhere = whereOrName(pParse->db, zWhere, p->pFrom->zName); + } + return zWhere; } /* @@ -183,7 +290,6 @@ void sqlite3AlterFunctions(sqlite3 *db){ static char *whereTempTriggers(Parse *pParse, Table *pTab){ Trigger *pTrig; char *zWhere = 0; - char *tmp = 0; const Schema *pTempSchema = pParse->db->aDb[1].pSchema; /* Temp db schema */ /* If the table is not located in the temp-db (in which case NULL is @@ -195,13 +301,7 @@ static char *whereTempTriggers(Parse *pParse, Table *pTab){ sqlite3 *db = pParse->db; for(pTrig=sqlite3TriggerList(pParse, pTab); pTrig; pTrig=pTrig->pNext){ if( pTrig->pSchema==pTempSchema ){ - if( !zWhere ){ - zWhere = sqlite3MPrintf(db, "name=%Q", pTrig->zName); - }else{ - tmp = zWhere; - zWhere = sqlite3MPrintf(db, "%s OR name=%Q", zWhere, pTrig->zName); - sqlite3DbFree(db, tmp); - } + zWhere = whereOrName(db, zWhere, pTrig->zName); } } } @@ -239,7 +339,7 @@ static void reloadTableSchema(Parse *pParse, Table *pTab, const char *zName){ } #endif - /* Drop the table and index from the internal schema */ + /* Drop the table and index from the internal schema. */ sqlite3VdbeAddOp4(v, OP_DropTable, iDb, 0, 0, pTab->zName, 0); /* Reload the table, index and permanent trigger schemas. */ @@ -370,6 +470,21 @@ void sqlite3AlterRenameTable( zTabName = pTab->zName; nTabName = sqlite3Utf8CharLen(zTabName, -1); +#ifndef SQLITE_OMIT_FOREIGN_KEY + if( db->flags&SQLITE_ForeignKeys ){ + /* If foreign-key support is enabled, rewrite the CREATE TABLE + ** statements corresponding to all child tables of foreign key constraints + ** for which the renamed table is the parent table. */ + if( (zWhere=whereForeignKeys(pParse, pTab))!=0 ){ + sqlite3NestedParse(pParse, + "UPDATE sqlite_master SET " + "sql = sqlite_rename_parent(sql, %Q, %Q) " + "WHERE %s;", zTabName, zName, zWhere); + sqlite3DbFree(db, zWhere); + } + } +#endif + /* Modify the sqlite_master table to use the new table name. */ sqlite3NestedParse(pParse, "UPDATE %Q.%s SET " @@ -421,6 +536,18 @@ void sqlite3AlterRenameTable( } #endif +#ifndef SQLITE_OMIT_FOREIGN_KEY + if( db->flags&SQLITE_ForeignKeys ){ + FKey *p; + for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){ + Table *pFrom = p->pFrom; + if( pFrom!=pTab ){ + reloadTableSchema(pParse, p->pFrom, pFrom->zName); + } + } + } +#endif + /* Drop and reload the internal table schema. */ reloadTableSchema(pParse, pTab, zName); diff --git a/src/fkey.c b/src/fkey.c index e7250d9a43..dfd158bfc5 100644 --- a/src/fkey.c +++ b/src/fkey.c @@ -517,7 +517,7 @@ static void fkScanChildren( ** NULL pointer (as there are no FK constraints for which t2 is the parent ** table). */ -static FKey *fkRefering(Table *pTab){ +FKey *sqlite3FkReferences(Table *pTab){ int nName = sqlite3Strlen30(pTab->zName); return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName, nName); } @@ -645,7 +645,7 @@ void sqlite3FkCheck( } /* Loop through all the foreign key constraints that refer to this table */ - for(pFKey = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){ + for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ int iGoto; /* Address of OP_Goto instruction */ Index *pIdx = 0; /* Foreign key index for pFKey */ SrcList *pSrc; @@ -740,7 +740,7 @@ u32 sqlite3FkOldmask( for(p=pTab->pFKey; p; p=p->pNextFrom){ for(i=0; inCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom); } - for(p=fkRefering(pTab); p; p=p->pNextTo){ + for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){ Index *pIdx = 0; locateFkeyIndex(0, pTab, p, &pIdx, 0); if( pIdx ){ @@ -767,7 +767,7 @@ int sqlite3FkRequired( ExprList *pChanges /* Non-NULL for UPDATE operations */ ){ if( pParse->db->flags&SQLITE_ForeignKeys ){ - if( fkRefering(pTab) || pTab->pFKey ) return 1; + if( sqlite3FkReferences(pTab) || pTab->pFKey ) return 1; } return 0; } @@ -968,7 +968,7 @@ void sqlite3FkActions( ** trigger sub-program. */ if( pParse->db->flags&SQLITE_ForeignKeys ){ FKey *pFKey; /* Iterator variable */ - for(pFKey = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){ + for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges); if( pAction ){ sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0); diff --git a/src/sqliteInt.h b/src/sqliteInt.h index cbf7cd5820..60414f7629 100644 --- a/src/sqliteInt.h +++ b/src/sqliteInt.h @@ -2952,6 +2952,7 @@ VTable *sqlite3GetVTable(sqlite3*, Table*); void sqlite3FkActions(Parse*, Table*, ExprList*, int); int sqlite3FkRequired(Parse*, Table*, ExprList*); u32 sqlite3FkOldmask(Parse*, Table*, ExprList*); + FKey *sqlite3FkReferences(Table *); #else #define sqlite3FkCheck(a,b,c,d,e) #define sqlite3FkActions(a,b,c,d) diff --git a/test/fkey2.test b/test/fkey2.test index 6508d37c5e..0fb4218f6b 100644 --- a/test/fkey2.test +++ b/test/fkey2.test @@ -804,6 +804,61 @@ do_test fkey2-14.1.6 { } } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} + +# Test the sqlite_rename_parent() function directly. +# +proc test_rename_parent {zCreate zOld zNew} { + db eval {SELECT sqlite_rename_parent($zCreate, $zOld, $zNew)} +} +do_test fkey2-14.2.1.1 { + test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 +} {{CREATE TABLE t1(a REFERENCES "t3")}} +do_test fkey2-14.2.1.2 { + test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 +} {{CREATE TABLE t1(a REFERENCES t2)}} +do_test fkey2-14.2.1.3 { + test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 +} {{CREATE TABLE t1(a REFERENCES "t3")}} + +# Test ALTER TABLE RENAME TABLE a bit. +# +do_test fkey2-14.2.2.1 { + drop_all_tables + execsql { + CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1); + CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); + CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); + } + execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} +} [list \ + {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ + {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ + {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ +] +do_test fkey2-14.2.2.2 { + execsql { ALTER TABLE t1 RENAME TO t4 } + execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} +} [list \ + {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ + {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ + {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ +] +do_test fkey2-14.2.2.3 { + catchsql { INSERT INTO t3 VALUES(1, 2, 3) } +} {1 {foreign key constraint failed}} +do_test fkey2-14.2.2.4 { + execsql { INSERT INTO t4 VALUES(1, NULL) } +} {} +do_test fkey2-14.2.2.5 { + catchsql { UPDATE t4 SET b = 5 } +} {1 {foreign key constraint failed}} +do_test fkey2-14.2.2.6 { + catchsql { UPDATE t4 SET b = 1 } +} {0 {}} +do_test fkey2-14.2.2.7 { + execsql { INSERT INTO t3 VALUES(1, NULL, 1) } +} {} + #------------------------------------------------------------------------- # The following tests, fkey2-15.*, test that unnecessary FK related scans # and lookups are avoided when the constraint counters are zero. -- 2.47.2