/ Check-in [b4a10c39]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment: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.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b4a10c39e726dc190e9597e382baddc034294114
User & Date: dan 2009-09-26 17:51:48
References
2014-04-14
19:46 New ticket [264b970c] Crash when calling sqlite_rename_parent() with NULL parameters.. artifact: 9c40070d user: drh
Context
2009-09-28
11:54
Fix some foreign key constraint related problems that occur when a row refers to itself. check-in: 9e503e2d user: dan tags: trunk
2009-09-26
17:51
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. check-in: b4a10c39 user: dan tags: trunk
2009-09-25
17:03
Avoid checking if an insert or delete has "fixed" an outstanding FK constraint violation if the constraint counter indicates that the database contains no such violations. check-in: 519144ac user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

    81     81   
    82     82       zRet = sqlite3MPrintf(db, "%.*s\"%w\"%s", ((u8*)tname.z) - zSql, zSql, 
    83     83          zTableName, tname.z+tname.n);
    84     84       sqlite3_result_text(context, zRet, -1, SQLITE_DYNAMIC);
    85     85     }
    86     86   }
    87     87   
           88  +/*
           89  +** This C function implements an SQL user function that is used by SQL code
           90  +** generated by the ALTER TABLE ... RENAME command to modify the definition
           91  +** of any foreign key constraints that use the table being renamed as the 
           92  +** parent table. It is passed three arguments:
           93  +**
           94  +**   1) The complete text of the CREATE TABLE statement being modified,
           95  +**   2) The old name of the table being renamed, and
           96  +**   3) The new name of the table being renamed.
           97  +**
           98  +** It returns the new CREATE TABLE statement. For example:
           99  +**
          100  +**   sqlite_rename_parent('CREATE TABLE t1(a REFERENCES t2)', 't2', 't3')
          101  +**       -> 'CREATE TABLE t1(a REFERENCES t3)'
          102  +*/
          103  +#ifndef SQLITE_OMIT_FOREIGN_KEY
          104  +static void renameParentFunc(
          105  +  sqlite3_context *context,
          106  +  int NotUsed,
          107  +  sqlite3_value **argv
          108  +){
          109  +  sqlite3 *db = sqlite3_context_db_handle(context);
          110  +  char *zOutput = 0;
          111  +  char *zResult;
          112  +  unsigned char const *zInput = sqlite3_value_text(argv[0]);
          113  +  unsigned char const *zOld = sqlite3_value_text(argv[1]);
          114  +  unsigned char const *zNew = sqlite3_value_text(argv[2]);
          115  +
          116  +  unsigned const char *z;         /* Pointer to token */
          117  +  int n;                          /* Length of token z */
          118  +  int token;                      /* Type of token */
          119  +
          120  +  for(z=zInput; *z; z=z+n){
          121  +    n = sqlite3GetToken(z, &token);
          122  +    if( token==TK_REFERENCES ){
          123  +      char *zParent;
          124  +      do {
          125  +        z += n;
          126  +        n = sqlite3GetToken(z, &token);
          127  +      }while( token==TK_SPACE );
          128  +
          129  +      zParent = sqlite3DbStrNDup(db, (const char *)z, n);
          130  +      sqlite3Dequote(zParent);
          131  +      if( 0==sqlite3StrICmp((const char *)zOld, zParent) ){
          132  +        char *zOut = sqlite3MPrintf(db, "%s%.*s\"%w\"", 
          133  +            (zOutput?zOutput:""), z-zInput, zInput, (const char *)zNew
          134  +        );
          135  +        sqlite3DbFree(db, zOutput);
          136  +        zOutput = zOut;
          137  +        zInput = &z[n];
          138  +      }
          139  +      sqlite3DbFree(db, zParent);
          140  +    }
          141  +  }
          142  +
          143  +  zResult = sqlite3MPrintf(db, "%s%s", (zOutput?zOutput:""), zInput), 
          144  +  sqlite3_result_text(context, zResult, -1, SQLITE_DYNAMIC);
          145  +  sqlite3DbFree(db, zOutput);
          146  +}
          147  +#endif
          148  +
    88    149   #ifndef SQLITE_OMIT_TRIGGER
    89    150   /* This function is used by SQL generated to implement the
    90    151   ** ALTER TABLE command. The first argument is the text of a CREATE TRIGGER 
    91    152   ** statement. The second is a table name. The table name in the CREATE 
    92    153   ** TRIGGER statement is replaced with the third argument and the result 
    93    154   ** returned. This is analagous to renameTableFunc() above, except for CREATE
    94    155   ** TRIGGER, not CREATE INDEX and CREATE TABLE.
................................................................................
   168    229   void sqlite3AlterFunctions(sqlite3 *db){
   169    230     sqlite3CreateFunc(db, "sqlite_rename_table", 2, SQLITE_UTF8, 0,
   170    231                            renameTableFunc, 0, 0);
   171    232   #ifndef SQLITE_OMIT_TRIGGER
   172    233     sqlite3CreateFunc(db, "sqlite_rename_trigger", 2, SQLITE_UTF8, 0,
   173    234                            renameTriggerFunc, 0, 0);
   174    235   #endif
          236  +#ifndef SQLITE_OMIT_FOREIGN_KEY
          237  +  sqlite3CreateFunc(db, "sqlite_rename_parent", 3, SQLITE_UTF8, 0,
          238  +                         renameParentFunc, 0, 0);
          239  +#endif
          240  +}
          241  +
          242  +/*
          243  +** This function is used to create the text of expressions of the form:
          244  +**
          245  +**   name=<constant1> OR name=<constant2> OR ...
          246  +**
          247  +** If argument zWhere is NULL, then a pointer string containing the text 
          248  +** "name=<constant>" is returned, where <constant> is the quoted version
          249  +** of the string passed as argument zConstant. The returned buffer is
          250  +** allocated using sqlite3DbMalloc(). It is the responsibility of the
          251  +** caller to ensure that it is eventually freed.
          252  +**
          253  +** If argument zWhere is not NULL, then the string returned is 
          254  +** "<where> OR name=<constant>", where <where> is the contents of zWhere.
          255  +** In this case zWhere is passed to sqlite3DbFree() before returning.
          256  +** 
          257  +*/
          258  +static char *whereOrName(sqlite3 *db, char *zWhere, char *zConstant){
          259  +  char *zNew;
          260  +  if( !zWhere ){
          261  +    zNew = sqlite3MPrintf(db, "name=%Q", zConstant);
          262  +  }else{
          263  +    zNew = sqlite3MPrintf(db, "%s OR name=%Q", zWhere, zConstant);
          264  +    sqlite3DbFree(db, zWhere);
          265  +  }
          266  +  return zNew;
          267  +}
          268  +
          269  +/*
          270  +** Generate the text of a WHERE expression which can be used to select all
          271  +** tables that have foreign key constraints that refer to table pTab (i.e.
          272  +** constraints for which pTab is the parent table) from the sqlite_master
          273  +** table.
          274  +*/
          275  +static char *whereForeignKeys(Parse *pParse, Table *pTab){
          276  +  FKey *p;
          277  +  char *zWhere = 0;
          278  +  for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
          279  +    zWhere = whereOrName(pParse->db, zWhere, p->pFrom->zName);
          280  +  }
          281  +  return zWhere;
   175    282   }
   176    283   
   177    284   /*
   178    285   ** Generate the text of a WHERE expression which can be used to select all
   179    286   ** temporary triggers on table pTab from the sqlite_temp_master table. If
   180    287   ** table pTab has no temporary triggers, or is itself stored in the 
   181    288   ** temporary database, NULL is returned.
   182    289   */
   183    290   static char *whereTempTriggers(Parse *pParse, Table *pTab){
   184    291     Trigger *pTrig;
   185    292     char *zWhere = 0;
   186         -  char *tmp = 0;
   187    293     const Schema *pTempSchema = pParse->db->aDb[1].pSchema; /* Temp db schema */
   188    294   
   189    295     /* If the table is not located in the temp-db (in which case NULL is 
   190    296     ** returned, loop through the tables list of triggers. For each trigger
   191    297     ** that is not part of the temp-db schema, add a clause to the WHERE 
   192    298     ** expression being built up in zWhere.
   193    299     */
   194    300     if( pTab->pSchema!=pTempSchema ){
   195    301       sqlite3 *db = pParse->db;
   196    302       for(pTrig=sqlite3TriggerList(pParse, pTab); pTrig; pTrig=pTrig->pNext){
   197    303         if( pTrig->pSchema==pTempSchema ){
   198         -        if( !zWhere ){
   199         -          zWhere = sqlite3MPrintf(db, "name=%Q", pTrig->zName);
   200         -        }else{
   201         -          tmp = zWhere;
   202         -          zWhere = sqlite3MPrintf(db, "%s OR name=%Q", zWhere, pTrig->zName);
   203         -          sqlite3DbFree(db, tmp);
   204         -        }
          304  +        zWhere = whereOrName(db, zWhere, pTrig->zName);
   205    305         }
   206    306       }
   207    307     }
   208    308     return zWhere;
   209    309   }
   210    310   
   211    311   /*
................................................................................
   235    335     for(pTrig=sqlite3TriggerList(pParse, pTab); pTrig; pTrig=pTrig->pNext){
   236    336       int iTrigDb = sqlite3SchemaToIndex(pParse->db, pTrig->pSchema);
   237    337       assert( iTrigDb==iDb || iTrigDb==1 );
   238    338       sqlite3VdbeAddOp4(v, OP_DropTrigger, iTrigDb, 0, 0, pTrig->zName, 0);
   239    339     }
   240    340   #endif
   241    341   
   242         -  /* Drop the table and index from the internal schema */
          342  +  /* Drop the table and index from the internal schema.  */
   243    343     sqlite3VdbeAddOp4(v, OP_DropTable, iDb, 0, 0, pTab->zName, 0);
   244    344   
   245    345     /* Reload the table, index and permanent trigger schemas. */
   246    346     zWhere = sqlite3MPrintf(pParse->db, "tbl_name=%Q", zName);
   247    347     if( !zWhere ) return;
   248    348     sqlite3VdbeAddOp4(v, OP_ParseSchema, iDb, 0, 0, zWhere, P4_DYNAMIC);
   249    349   
................................................................................
   365    465       sqlite3MayAbort(pParse);
   366    466     }
   367    467   #endif
   368    468   
   369    469     /* figure out how many UTF-8 characters are in zName */
   370    470     zTabName = pTab->zName;
   371    471     nTabName = sqlite3Utf8CharLen(zTabName, -1);
          472  +
          473  +#ifndef SQLITE_OMIT_FOREIGN_KEY
          474  +  if( db->flags&SQLITE_ForeignKeys ){
          475  +    /* If foreign-key support is enabled, rewrite the CREATE TABLE 
          476  +    ** statements corresponding to all child tables of foreign key constraints
          477  +    ** for which the renamed table is the parent table.  */
          478  +    if( (zWhere=whereForeignKeys(pParse, pTab))!=0 ){
          479  +      sqlite3NestedParse(pParse, 
          480  +          "UPDATE sqlite_master SET "
          481  +              "sql = sqlite_rename_parent(sql, %Q, %Q) "
          482  +              "WHERE %s;", zTabName, zName, zWhere);
          483  +      sqlite3DbFree(db, zWhere);
          484  +    }
          485  +  }
          486  +#endif
   372    487   
   373    488     /* Modify the sqlite_master table to use the new table name. */
   374    489     sqlite3NestedParse(pParse,
   375    490         "UPDATE %Q.%s SET "
   376    491   #ifdef SQLITE_OMIT_TRIGGER
   377    492             "sql = sqlite_rename_table(sql, %Q), "
   378    493   #else
................................................................................
   414    529     if( (zWhere=whereTempTriggers(pParse, pTab))!=0 ){
   415    530       sqlite3NestedParse(pParse, 
   416    531           "UPDATE sqlite_temp_master SET "
   417    532               "sql = sqlite_rename_trigger(sql, %Q), "
   418    533               "tbl_name = %Q "
   419    534               "WHERE %s;", zName, zName, zWhere);
   420    535       sqlite3DbFree(db, zWhere);
          536  +  }
          537  +#endif
          538  +
          539  +#ifndef SQLITE_OMIT_FOREIGN_KEY
          540  +  if( db->flags&SQLITE_ForeignKeys ){
          541  +    FKey *p;
          542  +    for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
          543  +      Table *pFrom = p->pFrom;
          544  +      if( pFrom!=pTab ){
          545  +        reloadTableSchema(pParse, p->pFrom, pFrom->zName);
          546  +      }
          547  +    }
   421    548     }
   422    549   #endif
   423    550   
   424    551     /* Drop and reload the internal table schema. */
   425    552     reloadTableSchema(pParse, pTab, zName);
   426    553   
   427    554   exit_rename_table:

Changes to src/fkey.c.

   513    513   **
   514    514   ** Calling this function with table "t1" as an argument returns a pointer
   515    515   ** to the FKey structure representing the foreign key constraint on table
   516    516   ** "t2". Calling this function with "t2" as the argument would return a
   517    517   ** NULL pointer (as there are no FK constraints for which t2 is the parent
   518    518   ** table).
   519    519   */
   520         -static FKey *fkRefering(Table *pTab){
          520  +FKey *sqlite3FkReferences(Table *pTab){
   521    521     int nName = sqlite3Strlen30(pTab->zName);
   522    522     return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName, nName);
   523    523   }
   524    524   
   525    525   /*
   526    526   ** The second argument is a Trigger structure allocated by the 
   527    527   ** fkActionTrigger() routine. This function deletes the Trigger structure
................................................................................
   641    641         fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1);
   642    642       }
   643    643   
   644    644       sqlite3DbFree(db, aiFree);
   645    645     }
   646    646   
   647    647     /* Loop through all the foreign key constraints that refer to this table */
   648         -  for(pFKey = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){
          648  +  for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
   649    649       int iGoto;                    /* Address of OP_Goto instruction */
   650    650       Index *pIdx = 0;              /* Foreign key index for pFKey */
   651    651       SrcList *pSrc;
   652    652       int *aiCol = 0;
   653    653   
   654    654       if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){
   655    655         assert( regOld==0 && regNew!=0 );
................................................................................
   736    736     u32 mask = 0;
   737    737     if( pParse->db->flags&SQLITE_ForeignKeys ){
   738    738       FKey *p;
   739    739       int i;
   740    740       for(p=pTab->pFKey; p; p=p->pNextFrom){
   741    741         for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom);
   742    742       }
   743         -    for(p=fkRefering(pTab); p; p=p->pNextTo){
          743  +    for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
   744    744         Index *pIdx = 0;
   745    745         locateFkeyIndex(0, pTab, p, &pIdx, 0);
   746    746         if( pIdx ){
   747    747           for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
   748    748         }
   749    749       }
   750    750     }
................................................................................
   763    763   */
   764    764   int sqlite3FkRequired(
   765    765     Parse *pParse,                  /* Parse context */
   766    766     Table *pTab,                    /* Table being modified */
   767    767     ExprList *pChanges              /* Non-NULL for UPDATE operations */
   768    768   ){
   769    769     if( pParse->db->flags&SQLITE_ForeignKeys ){
   770         -    if( fkRefering(pTab) || pTab->pFKey ) return 1;
          770  +    if( sqlite3FkReferences(pTab) || pTab->pFKey ) return 1;
   771    771     }
   772    772     return 0;
   773    773   }
   774    774   
   775    775   /*
   776    776   ** This function is called when an UPDATE or DELETE operation is being 
   777    777   ** compiled on table pTab, which is the parent table of foreign-key pFKey.
................................................................................
   964    964   ){
   965    965     /* If foreign-key support is enabled, iterate through all FKs that 
   966    966     ** refer to table pTab. If there is an action associated with the FK 
   967    967     ** for this operation (either update or delete), invoke the associated 
   968    968     ** trigger sub-program.  */
   969    969     if( pParse->db->flags&SQLITE_ForeignKeys ){
   970    970       FKey *pFKey;                  /* Iterator variable */
   971         -    for(pFKey = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){
          971  +    for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
   972    972         Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges);
   973    973         if( pAction ){
   974    974           sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0);
   975    975         }
   976    976       }
   977    977     }
   978    978   }

Changes to src/sqliteInt.h.

  2948   2948   ** provided (enforcement of FK constraints requires the triggers sub-system).
  2949   2949   */
  2950   2950   #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
  2951   2951     void sqlite3FkCheck(Parse*, Table*, ExprList*, int, int);
  2952   2952     void sqlite3FkActions(Parse*, Table*, ExprList*, int);
  2953   2953     int sqlite3FkRequired(Parse*, Table*, ExprList*);
  2954   2954     u32 sqlite3FkOldmask(Parse*, Table*, ExprList*);
         2955  +  FKey *sqlite3FkReferences(Table *);
  2955   2956   #else
  2956   2957     #define sqlite3FkCheck(a,b,c,d,e)
  2957   2958     #define sqlite3FkActions(a,b,c,d)
  2958   2959     #define sqlite3FkRequired(a,b,c) 0
  2959   2960     #define sqlite3FkOldmask(a,b,c)  0
  2960   2961   #endif
  2961   2962   #ifndef SQLITE_OMIT_FOREIGN_KEY

Changes to test/fkey2.test.

   800    800       PRAGMA foreign_keys = off;
   801    801       ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
   802    802       PRAGMA foreign_keys = on;
   803    803       SELECT sql FROM sqlite_master WHERE name='t2';
   804    804     }
   805    805   } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
   806    806   
          807  +
          808  +# Test the sqlite_rename_parent() function directly.
          809  +#
          810  +proc test_rename_parent {zCreate zOld zNew} {
          811  +  db eval {SELECT sqlite_rename_parent($zCreate, $zOld, $zNew)}
          812  +}
          813  +do_test fkey2-14.2.1.1 {
          814  +  test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
          815  +} {{CREATE TABLE t1(a REFERENCES "t3")}}
          816  +do_test fkey2-14.2.1.2 {
          817  +  test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
          818  +} {{CREATE TABLE t1(a REFERENCES t2)}}
          819  +do_test fkey2-14.2.1.3 {
          820  +  test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
          821  +} {{CREATE TABLE t1(a REFERENCES "t3")}}
          822  +
          823  +# Test ALTER TABLE RENAME TABLE a bit.
          824  +#
          825  +do_test fkey2-14.2.2.1 {
          826  +  drop_all_tables
          827  +  execsql {
          828  +    CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1);
          829  +    CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
          830  +    CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
          831  +  }
          832  +  execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
          833  +} [list \
          834  +  {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)}                     \
          835  +  {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)}    \
          836  +  {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
          837  +]
          838  +do_test fkey2-14.2.2.2 {
          839  +  execsql { ALTER TABLE t1 RENAME TO t4 }
          840  +  execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
          841  +} [list \
          842  +  {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                     \
          843  +  {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}    \
          844  +  {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")}  \
          845  +]
          846  +do_test fkey2-14.2.2.3 {
          847  +  catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
          848  +} {1 {foreign key constraint failed}}
          849  +do_test fkey2-14.2.2.4 {
          850  +  execsql { INSERT INTO t4 VALUES(1, NULL) }
          851  +} {}
          852  +do_test fkey2-14.2.2.5 {
          853  +  catchsql { UPDATE t4 SET b = 5 }
          854  +} {1 {foreign key constraint failed}}
          855  +do_test fkey2-14.2.2.6 {
          856  +  catchsql { UPDATE t4 SET b = 1 }
          857  +} {0 {}}
          858  +do_test fkey2-14.2.2.7 {
          859  +  execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
          860  +} {}
          861  +
   807    862   #-------------------------------------------------------------------------
   808    863   # The following tests, fkey2-15.*, test that unnecessary FK related scans 
   809    864   # and lookups are avoided when the constraint counters are zero.
   810    865   #
   811    866   drop_all_tables
   812    867   proc execsqlS {zSql} {
   813    868     set ::sqlite_search_count 0