/ Check-in [f09aa324]
Login

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

Overview
Comment:Enhance the ALTER TABLE RENAME COLUMN feature so that it works on tables that have redundant UNIQUE and/or PRIMARY KEY constraints. Fix for ticket [bc8d94f0fbd633fd9a051e3]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: f09aa3248e2b4a32ff5b5d37084cb2a27445322cb06a3354cef723f4f0b2cd34
User & Date: drh 2019-01-09 02:02:24
Context
2019-01-09
11:06
Small simplification to the dbstat virtual table. check-in: 9d4c156f user: drh tags: trunk
02:02
Enhance the ALTER TABLE RENAME COLUMN feature so that it works on tables that have redundant UNIQUE and/or PRIMARY KEY constraints. Fix for ticket [bc8d94f0fbd633fd9a051e3] check-in: f09aa324 user: drh tags: trunk
2019-01-08
20:02
Use 64-bit math to compute the sizes of memory allocations in extensions. check-in: ca67f2ec user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

   957    957     memset(p, 0, sizeof(Parse));
   958    958     p->eParseMode = (bTable ? PARSE_MODE_RENAME_TABLE : PARSE_MODE_RENAME_COLUMN);
   959    959     p->db = db;
   960    960     p->nQueryLoop = 1;
   961    961     rc = sqlite3RunParser(p, zSql, &zErr);
   962    962     assert( p->zErrMsg==0 );
   963    963     assert( rc!=SQLITE_OK || zErr==0 );
   964         -  assert( (0!=p->pNewTable) + (0!=p->pNewIndex) + (0!=p->pNewTrigger)<2 );
   965    964     p->zErrMsg = zErr;
   966    965     if( db->mallocFailed ) rc = SQLITE_NOMEM;
   967    966     if( rc==SQLITE_OK 
   968    967      && p->pNewTable==0 && p->pNewIndex==0 && p->pNewTrigger==0 
   969    968     ){
   970    969       rc = SQLITE_CORRUPT_BKPT;
   971    970     }
................................................................................
  1177   1176   
  1178   1177   /*
  1179   1178   ** Free the contents of Parse object (*pParse). Do not free the memory
  1180   1179   ** occupied by the Parse object itself.
  1181   1180   */
  1182   1181   static void renameParseCleanup(Parse *pParse){
  1183   1182     sqlite3 *db = pParse->db;
         1183  +  Index *pIdx;
  1184   1184     if( pParse->pVdbe ){
  1185   1185       sqlite3VdbeFinalize(pParse->pVdbe);
  1186   1186     }
  1187   1187     sqlite3DeleteTable(db, pParse->pNewTable);
  1188         -  if( pParse->pNewIndex ) sqlite3FreeIndex(db, pParse->pNewIndex);
         1188  +  while( (pIdx = pParse->pNewIndex)!=0 ){
         1189  +    pParse->pNewIndex = pIdx->pNext;
         1190  +    sqlite3FreeIndex(db, pIdx);
         1191  +  }
  1189   1192     sqlite3DeleteTrigger(db, pParse->pNewTrigger);
  1190   1193     sqlite3DbFree(db, pParse->zErrMsg);
  1191   1194     renameTokenFree(db, pParse->pRename);
  1192   1195     sqlite3ParserReset(pParse);
  1193   1196   }
  1194   1197   
  1195   1198   /*
................................................................................
  1291   1294           );
  1292   1295           if( sCtx.iCol<0 ){
  1293   1296             renameTokenFind(&sParse, &sCtx, (void*)&sParse.pNewTable->iPKey);
  1294   1297           }
  1295   1298           sqlite3WalkExprList(&sWalker, sParse.pNewTable->pCheck);
  1296   1299           for(pIdx=sParse.pNewTable->pIndex; pIdx; pIdx=pIdx->pNext){
  1297   1300             sqlite3WalkExprList(&sWalker, pIdx->aColExpr);
         1301  +        }
         1302  +        for(pIdx=sParse.pNewIndex; pIdx; pIdx=pIdx->pNext){
         1303  +          sqlite3WalkExprList(&sWalker, pIdx->aColExpr);
  1298   1304           }
  1299   1305         }
  1300   1306   
  1301   1307         for(pFKey=sParse.pNewTable->pFKey; pFKey; pFKey=pFKey->pNextFrom){
  1302   1308           for(i=0; i<pFKey->nCol; i++){
  1303   1309             if( bFKOnly==0 && pFKey->aCol[i].iFrom==iCol ){
  1304   1310               renameTokenFind(&sParse, &sCtx, (void*)&pFKey->aCol[i]);

Changes to src/build.c.

  3463   3463                   "conflicting ON CONFLICT clauses specified", 0);
  3464   3464             }
  3465   3465             if( pIdx->onError==OE_Default ){
  3466   3466               pIdx->onError = pIndex->onError;
  3467   3467             }
  3468   3468           }
  3469   3469           if( idxType==SQLITE_IDXTYPE_PRIMARYKEY ) pIdx->idxType = idxType;
         3470  +        if( IN_RENAME_OBJECT ){
         3471  +          pIndex->pNext = pParse->pNewIndex;
         3472  +          pParse->pNewIndex = pIndex;
         3473  +          pIndex = 0;
         3474  +        }
  3470   3475           goto exit_create_index;
  3471   3476         }
  3472   3477       }
  3473   3478     }
  3474   3479   
  3475   3480     if( !IN_RENAME_OBJECT ){
  3476   3481   

Changes to src/sqliteInt.h.

  3130   3130   #ifndef SQLITE_OMIT_EXPLAIN
  3131   3131     int addrExplain;          /* Address of current OP_Explain opcode */
  3132   3132   #endif
  3133   3133     VList *pVList;            /* Mapping between variable names and numbers */
  3134   3134     Vdbe *pReprepare;         /* VM being reprepared (sqlite3Reprepare()) */
  3135   3135     const char *zTail;        /* All SQL text past the last semicolon parsed */
  3136   3136     Table *pNewTable;         /* A table being constructed by CREATE TABLE */
  3137         -  Index *pNewIndex;         /* An index being constructed by CREATE INDEX */
         3137  +  Index *pNewIndex;         /* An index being constructed by CREATE INDEX.
         3138  +                            ** Also used to hold redundant UNIQUE constraints
         3139  +                            ** during a RENAME COLUMN */
  3138   3140     Trigger *pNewTrigger;     /* Trigger under construct by a CREATE TRIGGER */
  3139   3141     const char *zAuthContext; /* The 6th parameter to db->xAuth callbacks */
  3140   3142   #ifndef SQLITE_OMIT_VIRTUALTABLE
  3141   3143     Token sArg;               /* Complete text of a module argument */
  3142   3144     Table **apVtabLock;       /* Pointer to virtual tables needing locking */
  3143   3145   #endif
  3144   3146     Table *pZombieTab;        /* List of Table objects to delete after code gen */

Changes to test/altercol.test.

   774    774   do_execsql_test 19.1 {
   775    775     ALTER TABLE t1 RENAME a TO f;
   776    776     SELECT sql FROM sqlite_master WHERE name = 'v2';
   777    777   } {
   778    778     {CREATE VIEW v2(e) AS SELECT coalesce(t2.c,t1.f) FROM t1, t2 WHERE t1.b=t2.d}
   779    779   }
   780    780   
          781  +# 2019-01-08: https://www.sqlite.org/src/tktview/bc8d94f0fbd633fd9a051e3
          782  +#
          783  +# ALTER TABLE RENAME COLUMN does not work for tables that have redundant
          784  +# UNIQUE constraints.
          785  +#
          786  +sqlite3 db :memory:
          787  +do_execsql_test 20.100 {
          788  +  CREATE TABLE t1(aaa,b,c,UNIQUE(aaA),PRIMARY KEY(aAa),UNIQUE(aAA));
          789  +  ALTER TABLE t1 RENAME aaa TO bbb;
          790  +  SELECT sql FROM sqlite_master WHERE name='t1';
          791  +} {{CREATE TABLE t1(bbb,b,c,UNIQUE(bbb),PRIMARY KEY(bbb),UNIQUE(bbb))}}
          792  +do_execsql_test 20.105 {
          793  +  DROP TABLE t1;
          794  +  CREATE TABLE t1(aaa,b,c,UNIQUE(aaA),PRIMARY KEY(aAa),UNIQUE(aAA))WITHOUT ROWID;
          795  +  ALTER TABLE t1 RENAME aaa TO bbb;
          796  +  SELECT sql FROM sqlite_master WHERE name='t1';
          797  +} {{CREATE TABLE t1(bbb,b,c,UNIQUE(bbb),PRIMARY KEY(bbb),UNIQUE(bbb))WITHOUT ROWID}}
          798  +do_execsql_test 20.110 {
          799  +  DROP TABLE t1;
          800  +  CREATE TABLE t1(aa UNIQUE,bb UNIQUE,cc UNIQUE,UNIQUE(aA),PRIMARY KEY(bB),UNIQUE(cC));
          801  +  ALTER TABLE t1 RENAME aa TO xx;
          802  +  ALTER TABLE t1 RENAME bb TO yy;
          803  +  ALTER TABLE t1 RENAME cc TO zz;
          804  +  SELECT sql FROM sqlite_master WHERE name='t1';
          805  +} {{CREATE TABLE t1(xx UNIQUE,yy UNIQUE,zz UNIQUE,UNIQUE(xx),PRIMARY KEY(yy),UNIQUE(zz))}}
          806  +
   781    807   
   782    808   
   783    809   finish_test