Index: src/alter.c ================================================================== --- src/alter.c +++ src/alter.c @@ -83,10 +83,71 @@ zTableName, tname.z+tname.n); sqlite3_result_text(context, zRet, -1, SQLITE_DYNAMIC); } } +/* +** 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 ** statement. The second is a table name. The table name in the CREATE ** TRIGGER statement is replaced with the third argument and the result @@ -170,10 +231,56 @@ renameTableFunc, 0, 0); #ifndef SQLITE_OMIT_TRIGGER 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; } /* ** Generate the text of a WHERE expression which can be used to select all ** temporary triggers on table pTab from the sqlite_temp_master table. If @@ -181,11 +288,10 @@ ** temporary database, NULL is returned. */ 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 ** returned, loop through the tables list of triggers. For each trigger ** that is not part of the temp-db schema, add a clause to the WHERE @@ -193,17 +299,11 @@ */ if( pTab->pSchema!=pTempSchema ){ 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); } } } return zWhere; } @@ -237,11 +337,11 @@ assert( iTrigDb==iDb || iTrigDb==1 ); sqlite3VdbeAddOp4(v, OP_DropTrigger, iTrigDb, 0, 0, pTrig->zName, 0); } #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. */ zWhere = sqlite3MPrintf(pParse->db, "tbl_name=%Q", zName); if( !zWhere ) return; @@ -367,10 +467,25 @@ #endif /* figure out how many UTF-8 characters are in zName */ 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 " #ifdef SQLITE_OMIT_TRIGGER @@ -416,10 +531,22 @@ "UPDATE sqlite_temp_master SET " "sql = sqlite_rename_trigger(sql, %Q), " "tbl_name = %Q " "WHERE %s;", zName, zName, zWhere); sqlite3DbFree(db, zWhere); + } +#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); Index: src/fkey.c ================================================================== --- src/fkey.c +++ src/fkey.c @@ -515,11 +515,11 @@ ** to the FKey structure representing the foreign key constraint on table ** "t2". Calling this function with "t2" as the argument would return a ** 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); } /* @@ -643,11 +643,11 @@ sqlite3DbFree(db, aiFree); } /* 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; int *aiCol = 0; @@ -738,11 +738,11 @@ FKey *p; int i; 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 ){ for(i=0; inColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]); } @@ -765,11 +765,11 @@ Parse *pParse, /* Parse context */ Table *pTab, /* Table being modified */ 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; } /* @@ -966,11 +966,11 @@ ** refer to table pTab. If there is an action associated with the FK ** for this operation (either update or delete), invoke the associated ** 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); } } Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -2950,10 +2950,11 @@ #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) void sqlite3FkCheck(Parse*, Table*, ExprList*, int, int); 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) #define sqlite3FkRequired(a,b,c) 0 #define sqlite3FkOldmask(a,b,c) 0 Index: test/fkey2.test ================================================================== --- test/fkey2.test +++ test/fkey2.test @@ -802,10 +802,65 @@ PRAGMA foreign_keys = on; SELECT sql FROM sqlite_master WHERE name='t2'; } } {{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. # drop_all_tables