SQLite: Check-in [f09aa324]

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

Changes to src/alter.c.

  memset(p, 0, sizeof(Parse));
  p->eParseMode = (bTable ? PARSE_MODE_RENAME_TABLE : PARSE_MODE_RENAME_COLUMN);
  p->db = db;
  p->nQueryLoop = 1;
  rc = sqlite3RunParser(p, zSql, &zErr);
  assert( p->zErrMsg==0 );
  assert( rc!=SQLITE_OK || zErr==0 );
  assert( (0!=p->pNewTable) + (0!=p->pNewIndex) + (0!=p->pNewTrigger)<2 );
  p->zErrMsg = zErr;
  if( db->mallocFailed ) rc = SQLITE_NOMEM;
  if( rc==SQLITE_OK 
   && p->pNewTable==0 && p->pNewIndex==0 && p->pNewTrigger==0 
  ){
    rc = SQLITE_CORRUPT_BKPT;
  }
................................................................................

/*
** Free the contents of Parse object (*pParse). Do not free the memory
** occupied by the Parse object itself.
*/
static void renameParseCleanup(Parse *pParse){
  sqlite3 *db = pParse->db;

  if( pParse->pVdbe ){
    sqlite3VdbeFinalize(pParse->pVdbe);
  }
  sqlite3DeleteTable(db, pParse->pNewTable);


  if( pParse->pNewIndex ) sqlite3FreeIndex(db, pParse->pNewIndex);

  sqlite3DeleteTrigger(db, pParse->pNewTrigger);
  sqlite3DbFree(db, pParse->zErrMsg);
  renameTokenFree(db, pParse->pRename);
  sqlite3ParserReset(pParse);
}

/*
................................................................................
        );
        if( sCtx.iCol<0 ){
          renameTokenFind(&sParse, &sCtx, (void*)&sParse.pNewTable->iPKey);
        }
        sqlite3WalkExprList(&sWalker, sParse.pNewTable->pCheck);
        for(pIdx=sParse.pNewTable->pIndex; pIdx; pIdx=pIdx->pNext){
          sqlite3WalkExprList(&sWalker, pIdx->aColExpr);



        }
      }

      for(pFKey=sParse.pNewTable->pFKey; pFKey; pFKey=pFKey->pNextFrom){
        for(i=0; i<pFKey->nCol; i++){
          if( bFKOnly==0 && pFKey->aCol[i].iFrom==iCol ){
            renameTokenFind(&sParse, &sCtx, (void*)&pFKey->aCol[i]);

  memset(p, 0, sizeof(Parse));
  p->eParseMode = (bTable ? PARSE_MODE_RENAME_TABLE : PARSE_MODE_RENAME_COLUMN);
  p->db = db;
  p->nQueryLoop = 1;
  rc = sqlite3RunParser(p, zSql, &zErr);
  assert( p->zErrMsg==0 );
  assert( rc!=SQLITE_OK || zErr==0 );

  p->zErrMsg = zErr;
  if( db->mallocFailed ) rc = SQLITE_NOMEM;
  if( rc==SQLITE_OK 
   && p->pNewTable==0 && p->pNewIndex==0 && p->pNewTrigger==0 
  ){
    rc = SQLITE_CORRUPT_BKPT;
  }
................................................................................

/*
** Free the contents of Parse object (*pParse). Do not free the memory
** occupied by the Parse object itself.
*/
static void renameParseCleanup(Parse *pParse){
  sqlite3 *db = pParse->db;
  Index *pIdx;
  if( pParse->pVdbe ){
    sqlite3VdbeFinalize(pParse->pVdbe);
  }
  sqlite3DeleteTable(db, pParse->pNewTable);
  while( (pIdx = pParse->pNewIndex)!=0 ){
    pParse->pNewIndex = pIdx->pNext;
    sqlite3FreeIndex(db, pIdx);
  }
  sqlite3DeleteTrigger(db, pParse->pNewTrigger);
  sqlite3DbFree(db, pParse->zErrMsg);
  renameTokenFree(db, pParse->pRename);
  sqlite3ParserReset(pParse);
}

/*
................................................................................
        );
        if( sCtx.iCol<0 ){
          renameTokenFind(&sParse, &sCtx, (void*)&sParse.pNewTable->iPKey);
        }
        sqlite3WalkExprList(&sWalker, sParse.pNewTable->pCheck);
        for(pIdx=sParse.pNewTable->pIndex; pIdx; pIdx=pIdx->pNext){
          sqlite3WalkExprList(&sWalker, pIdx->aColExpr);
        }
        for(pIdx=sParse.pNewIndex; pIdx; pIdx=pIdx->pNext){
          sqlite3WalkExprList(&sWalker, pIdx->aColExpr);
        }
      }

      for(pFKey=sParse.pNewTable->pFKey; pFKey; pFKey=pFKey->pNextFrom){
        for(i=0; i<pFKey->nCol; i++){
          if( bFKOnly==0 && pFKey->aCol[i].iFrom==iCol ){
            renameTokenFind(&sParse, &sCtx, (void*)&pFKey->aCol[i]);

Changes to src/build.c.

                "conflicting ON CONFLICT clauses specified", 0);
          }
          if( pIdx->onError==OE_Default ){
            pIdx->onError = pIndex->onError;
          }
        }
        if( idxType==SQLITE_IDXTYPE_PRIMARYKEY ) pIdx->idxType = idxType;





        goto exit_create_index;
      }
    }
  }

  if( !IN_RENAME_OBJECT ){








>
>
>
>
>

                "conflicting ON CONFLICT clauses specified", 0);
          }
          if( pIdx->onError==OE_Default ){
            pIdx->onError = pIndex->onError;
          }
        }
        if( idxType==SQLITE_IDXTYPE_PRIMARYKEY ) pIdx->idxType = idxType;
        if( IN_RENAME_OBJECT ){
          pIndex->pNext = pParse->pNewIndex;
          pParse->pNewIndex = pIndex;
          pIndex = 0;
        }
        goto exit_create_index;
      }
    }
  }

  if( !IN_RENAME_OBJECT ){

Changes to src/sqliteInt.h.

#ifndef SQLITE_OMIT_EXPLAIN
  int addrExplain;          /* Address of current OP_Explain opcode */
#endif
  VList *pVList;            /* Mapping between variable names and numbers */
  Vdbe *pReprepare;         /* VM being reprepared (sqlite3Reprepare()) */
  const char *zTail;        /* All SQL text past the last semicolon parsed */
  Table *pNewTable;         /* A table being constructed by CREATE TABLE */
  Index *pNewIndex;         /* An index being constructed by CREATE INDEX */


  Trigger *pNewTrigger;     /* Trigger under construct by a CREATE TRIGGER */
  const char *zAuthContext; /* The 6th parameter to db->xAuth callbacks */
#ifndef SQLITE_OMIT_VIRTUALTABLE
  Token sArg;               /* Complete text of a module argument */
  Table **apVtabLock;       /* Pointer to virtual tables needing locking */
#endif
  Table *pZombieTab;        /* List of Table objects to delete after code gen */








|
>
>

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

Changes to test/altercol.test.

do_execsql_test 19.1 {
  ALTER TABLE t1 RENAME a TO f;
  SELECT sql FROM sqlite_master WHERE name = 'v2';
} {
  {CREATE VIEW v2(e) AS SELECT coalesce(t2.c,t1.f) FROM t1, t2 WHERE t1.b=t2.d}
}





























finish_test








>
|
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

do_execsql_test 19.1 {
  ALTER TABLE t1 RENAME a TO f;
  SELECT sql FROM sqlite_master WHERE name = 'v2';
} {
  {CREATE VIEW v2(e) AS SELECT coalesce(t2.c,t1.f) FROM t1, t2 WHERE t1.b=t2.d}
}

# 2019-01-08: https://www.sqlite.org/src/tktview/bc8d94f0fbd633fd9a051e3
#
# ALTER TABLE RENAME COLUMN does not work for tables that have redundant
# UNIQUE constraints.
#
sqlite3 db :memory:
do_execsql_test 20.100 {
  CREATE TABLE t1(aaa,b,c,UNIQUE(aaA),PRIMARY KEY(aAa),UNIQUE(aAA));
  ALTER TABLE t1 RENAME aaa TO bbb;
  SELECT sql FROM sqlite_master WHERE name='t1';
} {{CREATE TABLE t1(bbb,b,c,UNIQUE(bbb),PRIMARY KEY(bbb),UNIQUE(bbb))}}
do_execsql_test 20.105 {
  DROP TABLE t1;
  CREATE TABLE t1(aaa,b,c,UNIQUE(aaA),PRIMARY KEY(aAa),UNIQUE(aAA))WITHOUT ROWID;
  ALTER TABLE t1 RENAME aaa TO bbb;
  SELECT sql FROM sqlite_master WHERE name='t1';
} {{CREATE TABLE t1(bbb,b,c,UNIQUE(bbb),PRIMARY KEY(bbb),UNIQUE(bbb))WITHOUT ROWID}}
do_execsql_test 20.110 {
  DROP TABLE t1;
  CREATE TABLE t1(aa UNIQUE,bb UNIQUE,cc UNIQUE,UNIQUE(aA),PRIMARY KEY(bB),UNIQUE(cC));
  ALTER TABLE t1 RENAME aa TO xx;
  ALTER TABLE t1 RENAME bb TO yy;
  ALTER TABLE t1 RENAME cc TO zz;
  SELECT sql FROM sqlite_master WHERE name='t1';
} {{CREATE TABLE t1(xx UNIQUE,yy UNIQUE,zz UNIQUE,UNIQUE(xx),PRIMARY KEY(yy),UNIQUE(zz))}}



finish_test