/ Check-in [72cfb1be]
Login

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

Overview
Comment:Fix an ALTER TABLE problem with processing temp schema views and triggers.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | alter-table-rename-table
Files: files | file ages | folders
SHA3-256: 72cfb1be29971d91a164f1d4f20cb054de68960a0bd547630bcd1160565971c5
User & Date: dan 2018-08-30 16:26:48
Context
2018-08-30
20:03
Test the schema after renaming a table. Ensure that temp database triggers and views are updated when renaming a column. check-in: f3c27d91 user: dan tags: alter-table-rename-table
16:26
Fix an ALTER TABLE problem with processing temp schema views and triggers. check-in: 72cfb1be user: dan tags: alter-table-rename-table
2018-08-29
21:00
Extend RENAME TABLE to edit triggers and views. Still buggy. check-in: 01308bae user: dan tags: alter-table-rename-table
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

   271    271     zTabName = pTab->zName;
   272    272     nTabName = sqlite3Utf8CharLen(zTabName, -1);
   273    273   
   274    274     /* Rewrite all CREATE TABLE, INDEX, TRIGGER or VIEW statements in
   275    275     ** the schema to use the new table name.  */
   276    276     sqlite3NestedParse(pParse, 
   277    277         "UPDATE \"%w\".%s SET "
   278         -      "sql = sqlite_rename_table(%Q, sql, %Q, %Q, 0) "
          278  +      "sql = sqlite_rename_table(%Q, sql, %Q, %Q, %d) "
   279    279         "WHERE (type!='index' OR tbl_name=%Q COLLATE nocase)"
   280    280         "AND   name NOT LIKE 'sqlite_%%'"
   281         -      , zDb, MASTER_NAME, zDb, zTabName, zName, zTabName
          281  +      , zDb, MASTER_NAME, zDb, zTabName, zName, (iDb==1), zTabName
   282    282     );
   283    283   
   284    284     /* Update the tbl_name and name columns of the sqlite_master table
   285    285     ** as required.  */
   286    286     sqlite3NestedParse(pParse,
   287    287         "UPDATE %Q.%s SET "
   288    288             "tbl_name = %Q, "
................................................................................
  1267   1267       }
  1268   1268     }else if( sParse.pNewIndex ){
  1269   1269       sqlite3WalkExprList(&sWalker, sParse.pNewIndex->aColExpr);
  1270   1270       sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  1271   1271     }else{
  1272   1272       /* A trigger */
  1273   1273       TriggerStep *pStep;
  1274         -    rc = renameResolveTrigger(&sParse, zDb);
         1274  +    rc = renameResolveTrigger(&sParse, (bTemp ? 0 : zDb));
  1275   1275       if( rc!=SQLITE_OK ) goto renameColumnFunc_done;
  1276   1276   
  1277   1277       for(pStep=sParse.pNewTrigger->step_list; pStep; pStep=pStep->pNext){
  1278   1278         if( pStep->zTarget ){ 
  1279   1279           Table *pTarget = sqlite3LocateTable(&sParse, 0, pStep->zTarget, zDb);
  1280   1280           if( pTarget==pTab ){
  1281   1281             if( pStep->pUpsert ){
................................................................................
  1447   1447         TriggerStep *pStep;
  1448   1448         if( 0==sqlite3_stricmp(sParse.pNewTrigger->table, zOld) 
  1449   1449          && sCtx.pTab->pSchema==pTrigger->pTabSchema
  1450   1450         ){
  1451   1451           renameTokenFind(&sParse, &sCtx, sParse.pNewTrigger->table);
  1452   1452         }
  1453   1453   
  1454         -      rc = renameResolveTrigger(&sParse, zDb);
         1454  +      rc = renameResolveTrigger(&sParse, bTemp ? 0 : zDb);
  1455   1455         if( rc==SQLITE_OK ){
  1456   1456           renameWalkTrigger(&sWalker, pTrigger);
  1457         -      }
  1458         -
  1459         -      for(pStep=pTrigger->step_list; pStep; pStep=pStep->pNext){
  1460         -        if( pStep->zTarget && 0==sqlite3_stricmp(pStep->zTarget, zOld) ){
  1461         -          renameTokenFind(&sParse, &sCtx, pStep->zTarget);
         1457  +        for(pStep=pTrigger->step_list; pStep; pStep=pStep->pNext){
         1458  +          if( pStep->zTarget && 0==sqlite3_stricmp(pStep->zTarget, zOld) ){
         1459  +            renameTokenFind(&sParse, &sCtx, pStep->zTarget);
         1460  +          }
  1462   1461           }
  1463   1462         }
  1464   1463       }
  1465   1464   #endif
  1466   1465     }
  1467   1466   
  1468   1467     if( rc==SQLITE_OK ){

Changes to test/altertab.test.

   155    155   } [list [squish {
   156    156     CREATE TRIGGER tr1 AFTER INSERT ON "t11" BEGIN
   157    157       SELECT "t11".x, * FROM "t11", "t22";
   158    158       INSERT INTO "t22" VALUES(new.x, new.y);
   159    159     END
   160    160   }]]
   161    161   
          162  +#-------------------------------------------------------------------------
          163  +reset_db
          164  +do_execsql_test 5.0 {
          165  +  CREATE TABLE t9(a, b, c);
          166  +  CREATE TABLE t10(a, b, c);
          167  +  CREATE TEMP TABLE t9(a, b, c);
          168  +
          169  +  CREATE TRIGGER temp.t9t AFTER INSERT ON temp.t9 BEGIN
          170  +    INSERT INTO t10 VALUES(new.a, new.b, new.c);
          171  +  END;
          172  +
          173  +  INSERT INTO temp.t9 VALUES(1, 2, 3);
          174  +  SELECT * FROM t10;
          175  +} {1 2 3}
          176  +
          177  +do_execsql_test 5.1 {
          178  +  ALTER TABLE temp.t9 RENAME TO 't1234567890'
          179  +}
   162    180   
   163    181   finish_test
   164    182   
   165    183