/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/alter.c.

271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
....
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
....
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461

1462
1463
1464
1465
1466
1467
1468
  zTabName = pTab->zName;
  nTabName = sqlite3Utf8CharLen(zTabName, -1);

  /* Rewrite all CREATE TABLE, INDEX, TRIGGER or VIEW statements in
  ** the schema to use the new table name.  */
  sqlite3NestedParse(pParse, 
      "UPDATE \"%w\".%s SET "
      "sql = sqlite_rename_table(%Q, sql, %Q, %Q, 0) "
      "WHERE (type!='index' OR tbl_name=%Q COLLATE nocase)"
      "AND   name NOT LIKE 'sqlite_%%'"
      , zDb, MASTER_NAME, zDb, zTabName, zName, zTabName
  );

  /* Update the tbl_name and name columns of the sqlite_master table
  ** as required.  */
  sqlite3NestedParse(pParse,
      "UPDATE %Q.%s SET "
          "tbl_name = %Q, "
................................................................................
    }
  }else if( sParse.pNewIndex ){
    sqlite3WalkExprList(&sWalker, sParse.pNewIndex->aColExpr);
    sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  }else{
    /* A trigger */
    TriggerStep *pStep;
    rc = renameResolveTrigger(&sParse, zDb);
    if( rc!=SQLITE_OK ) goto renameColumnFunc_done;

    for(pStep=sParse.pNewTrigger->step_list; pStep; pStep=pStep->pNext){
      if( pStep->zTarget ){ 
        Table *pTarget = sqlite3LocateTable(&sParse, 0, pStep->zTarget, zDb);
        if( pTarget==pTab ){
          if( pStep->pUpsert ){
................................................................................
      TriggerStep *pStep;
      if( 0==sqlite3_stricmp(sParse.pNewTrigger->table, zOld) 
       && sCtx.pTab->pSchema==pTrigger->pTabSchema
      ){
        renameTokenFind(&sParse, &sCtx, sParse.pNewTrigger->table);
      }

      rc = renameResolveTrigger(&sParse, zDb);
      if( rc==SQLITE_OK ){
        renameWalkTrigger(&sWalker, pTrigger);
      }

      for(pStep=pTrigger->step_list; pStep; pStep=pStep->pNext){
        if( pStep->zTarget && 0==sqlite3_stricmp(pStep->zTarget, zOld) ){
          renameTokenFind(&sParse, &sCtx, pStep->zTarget);

        }
      }
    }
#endif
  }

  if( rc==SQLITE_OK ){







|


|







 







|







 







|


<
<
|
|
|
>







271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
....
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
....
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456


1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
  zTabName = pTab->zName;
  nTabName = sqlite3Utf8CharLen(zTabName, -1);

  /* Rewrite all CREATE TABLE, INDEX, TRIGGER or VIEW statements in
  ** the schema to use the new table name.  */
  sqlite3NestedParse(pParse, 
      "UPDATE \"%w\".%s SET "
      "sql = sqlite_rename_table(%Q, sql, %Q, %Q, %d) "
      "WHERE (type!='index' OR tbl_name=%Q COLLATE nocase)"
      "AND   name NOT LIKE 'sqlite_%%'"
      , zDb, MASTER_NAME, zDb, zTabName, zName, (iDb==1), zTabName
  );

  /* Update the tbl_name and name columns of the sqlite_master table
  ** as required.  */
  sqlite3NestedParse(pParse,
      "UPDATE %Q.%s SET "
          "tbl_name = %Q, "
................................................................................
    }
  }else if( sParse.pNewIndex ){
    sqlite3WalkExprList(&sWalker, sParse.pNewIndex->aColExpr);
    sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  }else{
    /* A trigger */
    TriggerStep *pStep;
    rc = renameResolveTrigger(&sParse, (bTemp ? 0 : zDb));
    if( rc!=SQLITE_OK ) goto renameColumnFunc_done;

    for(pStep=sParse.pNewTrigger->step_list; pStep; pStep=pStep->pNext){
      if( pStep->zTarget ){ 
        Table *pTarget = sqlite3LocateTable(&sParse, 0, pStep->zTarget, zDb);
        if( pTarget==pTab ){
          if( pStep->pUpsert ){
................................................................................
      TriggerStep *pStep;
      if( 0==sqlite3_stricmp(sParse.pNewTrigger->table, zOld) 
       && sCtx.pTab->pSchema==pTrigger->pTabSchema
      ){
        renameTokenFind(&sParse, &sCtx, sParse.pNewTrigger->table);
      }

      rc = renameResolveTrigger(&sParse, bTemp ? 0 : zDb);
      if( rc==SQLITE_OK ){
        renameWalkTrigger(&sWalker, pTrigger);


        for(pStep=pTrigger->step_list; pStep; pStep=pStep->pNext){
          if( pStep->zTarget && 0==sqlite3_stricmp(pStep->zTarget, zOld) ){
            renameTokenFind(&sParse, &sCtx, pStep->zTarget);
          }
        }
      }
    }
#endif
  }

  if( rc==SQLITE_OK ){

Changes to test/altertab.test.

155
156
157
158
159
160
161


















162
163
164
165
} [list [squish {
  CREATE TRIGGER tr1 AFTER INSERT ON "t11" BEGIN
    SELECT "t11".x, * FROM "t11", "t22";
    INSERT INTO "t22" VALUES(new.x, new.y);
  END
}]]




















finish_test









>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




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
} [list [squish {
  CREATE TRIGGER tr1 AFTER INSERT ON "t11" BEGIN
    SELECT "t11".x, * FROM "t11", "t22";
    INSERT INTO "t22" VALUES(new.x, new.y);
  END
}]]

#-------------------------------------------------------------------------
reset_db
do_execsql_test 5.0 {
  CREATE TABLE t9(a, b, c);
  CREATE TABLE t10(a, b, c);
  CREATE TEMP TABLE t9(a, b, c);

  CREATE TRIGGER temp.t9t AFTER INSERT ON temp.t9 BEGIN
    INSERT INTO t10 VALUES(new.a, new.b, new.c);
  END;

  INSERT INTO temp.t9 VALUES(1, 2, 3);
  SELECT * FROM t10;
} {1 2 3}

do_execsql_test 5.1 {
  ALTER TABLE temp.t9 RENAME TO 't1234567890'
}

finish_test