SQLite

Check-in [72cfb1be29]
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
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.167
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: f3c27d916d user: dan tags: alter-table-rename-table)
16:26
Fix an ALTER TABLE problem with processing temp schema views and triggers. (check-in: 72cfb1be29 user: dan tags: alter-table-rename-table)
2018-08-29
21:00
Extend RENAME TABLE to edit triggers and views. Still buggy. (check-in: 01308bae3a user: dan tags: alter-table-rename-table)
Changes
Side-by-Side Diff 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

271
272
273
274
275
276
277

278
279
280

281
282
283
284
285
286
287
288








-
+


-
+








  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) "
      "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, zTabName
      , 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, "
︙
1267
1268
1269
1270
1271
1272
1273
1274

1275
1276
1277
1278
1279
1280
1281

1267
1268
1269
1270
1271
1272
1273

1274
1275
1276
1277
1278
1279
1280
1281








-
+








    }
  }else if( sParse.pNewIndex ){
    sqlite3WalkExprList(&sWalker, sParse.pNewIndex->aColExpr);
    sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
  }else{
    /* A trigger */
    TriggerStep *pStep;
    rc = renameResolveTrigger(&sParse, zDb);
    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 ){
︙
1447
1448
1449
1450
1451
1452
1453
1454

1455
1456
1457
1458
1459
1460
1461




1462
1463
1464
1465
1466
1467
1468

1447
1448
1449
1450
1451
1452
1453

1454
1455
1456





1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467








-
+


-
-
-
-
-
+
+
+
+








      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);
      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);
        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

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