/ Check-in [8d89ddc1a6]
Login

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

Overview
Comment:Have "ALTER TABLE ADD COLUMN" reload the entire db schema, as "RENAME COLUMN" and "RENAME TABLE" do.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | alter-table-rename-table
Files: files | file ages | folders
SHA3-256: 8d89ddc1a628e983b0fbd929c9c9daac86ee23d18f8dd2709c971012389395c6
User & Date: dan 2018-09-01 16:05:50
Context
2018-09-01
16:13
Merge alter-table-rename-table back into this branch. check-in: ad704a7c86 user: dan tags: alter-table-rename-column
16:05
Have "ALTER TABLE ADD COLUMN" reload the entire db schema, as "RENAME COLUMN" and "RENAME TABLE" do. Closed-Leaf check-in: 8d89ddc1a6 user: dan tags: alter-table-rename-table
15:55
Merge fixes from the alter-table-rename-column branch that occurred after this branch separated from that one. check-in: 22e785aa2b user: drh tags: alter-table-rename-table
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

    16     16   
    17     17   /*
    18     18   ** The code in this file only exists if we are not omitting the
    19     19   ** ALTER TABLE logic from the build.
    20     20   */
    21     21   #ifndef SQLITE_OMIT_ALTERTABLE
    22     22   
    23         -/*
    24         -** This function is used to create the text of expressions of the form:
    25         -**
    26         -**   name=<constant1> OR name=<constant2> OR ...
    27         -**
    28         -** If argument zWhere is NULL, then a pointer string containing the text 
    29         -** "name=<constant>" is returned, where <constant> is the quoted version
    30         -** of the string passed as argument zConstant. The returned buffer is
    31         -** allocated using sqlite3DbMalloc(). It is the responsibility of the
    32         -** caller to ensure that it is eventually freed.
    33         -**
    34         -** If argument zWhere is not NULL, then the string returned is 
    35         -** "<where> OR name=<constant>", where <where> is the contents of zWhere.
    36         -** In this case zWhere is passed to sqlite3DbFree() before returning.
    37         -** 
    38         -*/
    39         -static char *whereOrName(sqlite3 *db, char *zWhere, char *zConstant){
    40         -  char *zNew;
    41         -  if( !zWhere ){
    42         -    zNew = sqlite3MPrintf(db, "name=%Q", zConstant);
    43         -  }else{
    44         -    zNew = sqlite3MPrintf(db, "%s OR name=%Q", zWhere, zConstant);
    45         -    sqlite3DbFree(db, zWhere);
    46         -  }
    47         -  return zNew;
    48         -}
    49         -
    50         -#if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
    51         -/*
    52         -** Generate the text of a WHERE expression which can be used to select all
    53         -** tables that have foreign key constraints that refer to table pTab (i.e.
    54         -** constraints for which pTab is the parent table) from the sqlite_master
    55         -** table.
    56         -*/
    57         -static char *whereForeignKeys(Parse *pParse, Table *pTab){
    58         -  FKey *p;
    59         -  char *zWhere = 0;
    60         -  for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
    61         -    zWhere = whereOrName(pParse->db, zWhere, p->pFrom->zName);
    62         -  }
    63         -  return zWhere;
    64         -}
    65         -#endif
    66         -
    67         -/*
    68         -** Generate the text of a WHERE expression which can be used to select all
    69         -** temporary triggers on table pTab from the sqlite_temp_master table. If
    70         -** table pTab has no temporary triggers, or is itself stored in the 
    71         -** temporary database, NULL is returned.
    72         -*/
    73         -static char *whereTempTriggers(Parse *pParse, Table *pTab){
    74         -  Trigger *pTrig;
    75         -  char *zWhere = 0;
    76         -  const Schema *pTempSchema = pParse->db->aDb[1].pSchema; /* Temp db schema */
    77         -
    78         -  /* If the table is not located in the temp-db (in which case NULL is 
    79         -  ** returned, loop through the tables list of triggers. For each trigger
    80         -  ** that is not part of the temp-db schema, add a clause to the WHERE 
    81         -  ** expression being built up in zWhere.
    82         -  */
    83         -  if( pTab->pSchema!=pTempSchema ){
    84         -    sqlite3 *db = pParse->db;
    85         -    for(pTrig=sqlite3TriggerList(pParse, pTab); pTrig; pTrig=pTrig->pNext){
    86         -      if( pTrig->pSchema==pTempSchema ){
    87         -        zWhere = whereOrName(db, zWhere, pTrig->zName);
    88         -      }
    89         -    }
    90         -  }
    91         -  if( zWhere ){
    92         -    char *zNew = sqlite3MPrintf(pParse->db, "type='trigger' AND (%s)", zWhere);
    93         -    sqlite3DbFree(pParse->db, zWhere);
    94         -    zWhere = zNew;
    95         -  }
    96         -  return zWhere;
    97         -}
    98         -
    99         -/*
   100         -** Generate code to drop and reload the internal representation of table
   101         -** pTab from the database, including triggers and temporary triggers.
   102         -** Argument zName is the name of the table in the database schema at
   103         -** the time the generated code is executed. This can be different from
   104         -** pTab->zName if this function is being called to code part of an 
   105         -** "ALTER TABLE RENAME TO" statement.
   106         -*/
   107         -static void reloadTableSchema(Parse *pParse, Table *pTab, const char *zName){
   108         -  Vdbe *v;
   109         -  char *zWhere;
   110         -  int iDb;                   /* Index of database containing pTab */
   111         -#ifndef SQLITE_OMIT_TRIGGER
   112         -  Trigger *pTrig;
   113         -#endif
   114         -
   115         -  v = sqlite3GetVdbe(pParse);
   116         -  if( NEVER(v==0) ) return;
   117         -  assert( sqlite3BtreeHoldsAllMutexes(pParse->db) );
   118         -  iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);
   119         -  assert( iDb>=0 );
   120         -
   121         -#ifndef SQLITE_OMIT_TRIGGER
   122         -  /* Drop any table triggers from the internal schema. */
   123         -  for(pTrig=sqlite3TriggerList(pParse, pTab); pTrig; pTrig=pTrig->pNext){
   124         -    int iTrigDb = sqlite3SchemaToIndex(pParse->db, pTrig->pSchema);
   125         -    assert( iTrigDb==iDb || iTrigDb==1 );
   126         -    sqlite3VdbeAddOp4(v, OP_DropTrigger, iTrigDb, 0, 0, pTrig->zName, 0);
   127         -  }
   128         -#endif
   129         -
   130         -  /* Drop the table and index from the internal schema.  */
   131         -  sqlite3VdbeAddOp4(v, OP_DropTable, iDb, 0, 0, pTab->zName, 0);
   132         -
   133         -  /* Reload the table, index and permanent trigger schemas. */
   134         -  zWhere = sqlite3MPrintf(pParse->db, "tbl_name=%Q", zName);
   135         -  if( !zWhere ) return;
   136         -  sqlite3VdbeAddParseSchemaOp(v, iDb, zWhere);
   137         -
   138         -#ifndef SQLITE_OMIT_TRIGGER
   139         -  /* Now, if the table is not stored in the temp database, reload any temp 
   140         -  ** triggers. Don't use IN(...) in case SQLITE_OMIT_SUBQUERY is defined. 
   141         -  */
   142         -  if( (zWhere=whereTempTriggers(pParse, pTab))!=0 ){
   143         -    sqlite3VdbeAddParseSchemaOp(v, 1, zWhere);
   144         -  }
   145         -#endif
   146         -}
   147         -
   148     23   /*
   149     24   ** Parameter zName is the name of a table that is about to be altered
   150     25   ** (either with ALTER TABLE ... RENAME TO or ALTER TABLE ... ADD COLUMN).
   151     26   ** If the table is a system table, this function leaves an error message
   152     27   ** in pParse->zErr (system tables may not be altered) and returns non-zero.
   153     28   **
   154     29   ** Or, if zName is not a system table, zero is returned.
................................................................................
   157     32     if( 0==sqlite3StrNICmp(zName, "sqlite_", 7) ){
   158     33       sqlite3ErrorMsg(pParse, "table %s may not be altered", zName);
   159     34       return 1;
   160     35     }
   161     36     return 0;
   162     37   }
   163     38   
           39  +/*
           40  +** Generate code to verify that the schemas of database zDb and, if
           41  +** bTemp is not true, database "temp", can still be parsed. This is
           42  +** called at the end of the generation of an ALTER TABLE ... RENAME ...
           43  +** statement to ensure that the operation has not rendered any schema
           44  +** objects unusable.
           45  +*/
   164     46   void renameTestSchema(Parse *pParse, const char *zDb, int bTemp){
   165     47     sqlite3NestedParse(pParse, 
   166     48         "SELECT 1 "
   167     49         "FROM \"%w\".%s "
   168     50         "WHERE name NOT LIKE 'sqlite_%%'"
   169     51         " AND sql NOT LIKE 'create virtual%%'"
   170     52         " AND sqlite_rename_test(%Q, sql, type, name, %d)=0 ",
................................................................................
   179     61           "WHERE name NOT LIKE 'sqlite_%%'"
   180     62           " AND sql NOT LIKE 'create virtual%%'"
   181     63           " AND sqlite_rename_test(%Q, sql, type, name, 1)=0 ",
   182     64           MASTER_NAME, zDb 
   183     65       );
   184     66     }
   185     67   }
           68  +
           69  +/*
           70  +** Generate code to reload the schema for database iDb. And, if iDb!=1, for
           71  +** the temp database as well.
           72  +*/
           73  +void renameReloadSchema(Parse *pParse, int iDb){
           74  +  Vdbe *v = pParse->pVdbe;
           75  +  if( v ){
           76  +    sqlite3ChangeCookie(pParse, iDb);
           77  +    sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, iDb, 0);
           78  +    if( iDb!=1 ) sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, 1, 0);
           79  +  }
           80  +}
   186     81   
   187     82   /*
   188     83   ** Generate code to implement the "ALTER TABLE xxx RENAME TO yyy" 
   189     84   ** command. 
   190     85   */
   191     86   void sqlite3AlterRenameTable(
   192     87     Parse *pParse,            /* Parser context. */
................................................................................
   269    164     ** schema). Open a statement transaction if the table is a virtual
   270    165     ** table.
   271    166     */
   272    167     v = sqlite3GetVdbe(pParse);
   273    168     if( v==0 ){
   274    169       goto exit_rename_table;
   275    170     }
   276         -  sqlite3BeginWriteOperation(pParse, pVTab!=0, iDb);
   277         -  sqlite3ChangeCookie(pParse, iDb);
   278    171   
   279    172     /* If this is a virtual table, invoke the xRename() function if
   280    173     ** one is defined. The xRename() callback will modify the names
   281    174     ** of any resources used by the v-table implementation (including other
   282    175     ** SQLite tables) that are identified by the name of the virtual table.
   283    176     */
   284    177   #ifndef SQLITE_OMIT_VIRTUALTABLE
................................................................................
   341    234               "sql = sqlite_rename_table(%Q, sql, %Q, %Q, 1), "
   342    235               "tbl_name = "
   343    236                 "CASE WHEN tbl_name=%Q COLLATE nocase THEN %Q ELSE tbl_name END "
   344    237               "WHERE type IN ('view', 'trigger')"
   345    238           , zDb, zTabName, zName, zTabName, zTabName, zName);
   346    239     }
   347    240   
   348         -  sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, iDb, 0);
   349         -  if( iDb!=1 ) sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, 1, 0);
   350         -
          241  +  renameReloadSchema(pParse, iDb);
   351    242     renameTestSchema(pParse, zDb, iDb==1);
   352    243   
   353    244   exit_rename_table:
   354    245     sqlite3SrcListDelete(db, pSrc);
   355    246     sqlite3DbFree(db, zName);
   356    247     db->mDbFlags = savedDbFlags;
   357    248   }
................................................................................
   370    261     int iDb;                  /* Database number */
   371    262     const char *zDb;          /* Database name */
   372    263     const char *zTab;         /* Table name */
   373    264     char *zCol;               /* Null-terminated column definition */
   374    265     Column *pCol;             /* The new column */
   375    266     Expr *pDflt;              /* Default value for the new column */
   376    267     sqlite3 *db;              /* The database connection; */
   377         -  Vdbe *v = pParse->pVdbe;  /* The prepared statement under construction */
          268  +  Vdbe *v;                  /* The prepared statement under construction */
   378    269     int r1;                   /* Temporary registers */
          270  +  char *zWhere;             /* WHERE clause for reloading schema */
   379    271   
   380    272     db = pParse->db;
   381    273     if( pParse->nErr || db->mallocFailed ) return;
   382         -  assert( v!=0 );
   383    274     pNew = pParse->pNewTable;
   384    275     assert( pNew );
   385    276   
   386    277     assert( sqlite3BtreeHoldsAllMutexes(db) );
   387    278     iDb = sqlite3SchemaToIndex(db, pNew->pSchema);
   388    279     zDb = db->aDb[iDb].zDbSName;
   389    280     zTab = &pNew->zName[16];  /* Skip the "sqlite_altertab_" prefix on the name */
................................................................................
   470    361       db->mDbFlags = savedDbFlags;
   471    362     }
   472    363   
   473    364     /* Make sure the schema version is at least 3.  But do not upgrade
   474    365     ** from less than 3 to 4, as that will corrupt any preexisting DESC
   475    366     ** index.
   476    367     */
   477         -  r1 = sqlite3GetTempReg(pParse);
   478         -  sqlite3VdbeAddOp3(v, OP_ReadCookie, iDb, r1, BTREE_FILE_FORMAT);
   479         -  sqlite3VdbeUsesBtree(v, iDb);
   480         -  sqlite3VdbeAddOp2(v, OP_AddImm, r1, -2);
   481         -  sqlite3VdbeAddOp2(v, OP_IfPos, r1, sqlite3VdbeCurrentAddr(v)+2);
   482         -  VdbeCoverage(v);
   483         -  sqlite3VdbeAddOp3(v, OP_SetCookie, iDb, BTREE_FILE_FORMAT, 3);
   484         -  sqlite3ReleaseTempReg(pParse, r1);
          368  +  v = sqlite3GetVdbe(pParse);
          369  +  if( v ){
          370  +    r1 = sqlite3GetTempReg(pParse);
          371  +    sqlite3VdbeAddOp3(v, OP_ReadCookie, iDb, r1, BTREE_FILE_FORMAT);
          372  +    sqlite3VdbeUsesBtree(v, iDb);
          373  +    sqlite3VdbeAddOp2(v, OP_AddImm, r1, -2);
          374  +    sqlite3VdbeAddOp2(v, OP_IfPos, r1, sqlite3VdbeCurrentAddr(v)+2);
          375  +    VdbeCoverage(v);
          376  +    sqlite3VdbeAddOp3(v, OP_SetCookie, iDb, BTREE_FILE_FORMAT, 3);
          377  +    sqlite3ReleaseTempReg(pParse, r1);
          378  +  }
   485    379   
   486         -  /* Reload the schema of the modified table. */
   487         -  reloadTableSchema(pParse, pTab, pTab->zName);
          380  +  /* Reload the table definition */
          381  +  renameReloadSchema(pParse, iDb);
   488    382   }
   489    383   
   490    384   /*
   491    385   ** This function is called by the parser after the table-name in
   492    386   ** an "ALTER TABLE <table-name> ADD" statement is parsed. Argument 
   493    387   ** pSrc is the full-name of the table being altered.
   494    388   **
................................................................................
   565    459       pCol->zColl = 0;
   566    460       pCol->pDflt = 0;
   567    461     }
   568    462     pNew->pSchema = db->aDb[iDb].pSchema;
   569    463     pNew->addColOffset = pTab->addColOffset;
   570    464     pNew->nTabRef = 1;
   571    465   
   572         -  /* Begin a transaction and increment the schema cookie.  */
   573         -  sqlite3BeginWriteOperation(pParse, 0, iDb);
   574         -  v = sqlite3GetVdbe(pParse);
   575         -  if( !v ) goto exit_begin_add_column;
   576         -  sqlite3ChangeCookie(pParse, iDb);
   577         -
   578    466   exit_begin_add_column:
   579    467     sqlite3SrcListDelete(db, pSrc);
   580    468     return;
   581    469   }
   582    470   
   583    471   /*
   584    472   ** Parameter pTab is the subject of an ALTER TABLE ... RENAME COLUMN
................................................................................
   688    576         "sql = sqlite_rename_column(sql, type, name, %Q, %Q, %d, %Q, %d) "
   689    577         "WHERE type IN ('trigger', 'view')",
   690    578         MASTER_NAME, 
   691    579         zDb, pTab->zName, iCol, zNew, bQuote
   692    580     );
   693    581   
   694    582     /* Drop and reload the database schema. */
   695         -  if( pParse->pVdbe ){
   696         -    sqlite3ChangeCookie(pParse, iSchema);
   697         -    sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, iSchema, 0);
   698         -    if( iSchema!=1 ) sqlite3VdbeAddParseSchemaOp(pParse->pVdbe, 1, 0);
   699         -  }
   700         -
          583  +  renameReloadSchema(pParse, iSchema);
   701    584     renameTestSchema(pParse, zDb, iSchema==1);
   702    585   
   703    586    exit_rename_column:
   704    587     sqlite3SrcListDelete(db, pSrc);
   705    588     sqlite3DbFree(db, zOld);
   706    589     sqlite3DbFree(db, zNew);
   707    590     return;
................................................................................
   741    624     int iCol;                       /* Index of column being renamed */
   742    625     Table *pTab;                    /* Table being ALTERed */ 
   743    626     const char *zOld;               /* Old column name */
   744    627   };
   745    628   
   746    629   void renameTokenClear(Parse *pParse, void *pPtr){
   747    630     RenameToken *p;
   748         -  assert( pPtr );
          631  +  assert( pPtr || pParse->db->mallocFailed );
   749    632     for(p=pParse->pRename; p; p=p->pNext){
   750    633       if( p->p==pPtr ){
   751    634         p->p = 0;
   752    635       }
   753    636     }
   754    637   }
   755    638   
................................................................................
  1534   1417     int NotUsed,
  1535   1418     sqlite3_value **argv
  1536   1419   ){
  1537   1420     sqlite3 *db = sqlite3_context_db_handle(context);
  1538   1421     unsigned char const *zDb = sqlite3_value_text(argv[0]);
  1539   1422     unsigned char const *zInput = sqlite3_value_text(argv[1]);
  1540   1423     int bTemp = sqlite3_value_int(argv[4]);
  1541         -  int rc;
  1542         -  Parse sParse;
  1543   1424   
  1544   1425   #ifndef SQLITE_OMIT_AUTHORIZATION
  1545   1426     sqlite3_xauth xAuth = db->xAuth;
  1546   1427     db->xAuth = 0;
  1547   1428   #endif
  1548   1429   
  1549         -  rc = renameParseSql(&sParse, zDb, 1, db, zInput, bTemp);
  1550         -  if( rc==SQLITE_OK ){
  1551         -    if( sParse.pNewTable && sParse.pNewTable->pSelect ){
  1552         -      NameContext sNC;
  1553         -      memset(&sNC, 0, sizeof(sNC));
  1554         -      sNC.pParse = &sParse;
  1555         -      sqlite3SelectPrep(&sParse, sParse.pNewTable->pSelect, &sNC);
  1556         -      if( sParse.nErr ) rc = sParse.rc;
         1430  +  if( zDb && zInput ){
         1431  +    int rc;
         1432  +    Parse sParse;
         1433  +    rc = renameParseSql(&sParse, zDb, 1, db, zInput, bTemp);
         1434  +    if( rc==SQLITE_OK ){
         1435  +      if( sParse.pNewTable && sParse.pNewTable->pSelect ){
         1436  +        NameContext sNC;
         1437  +        memset(&sNC, 0, sizeof(sNC));
         1438  +        sNC.pParse = &sParse;
         1439  +        sqlite3SelectPrep(&sParse, sParse.pNewTable->pSelect, &sNC);
         1440  +        if( sParse.nErr ) rc = sParse.rc;
         1441  +      }
         1442  +
         1443  +      else if( sParse.pNewTrigger ){
         1444  +        rc = renameResolveTrigger(&sParse, bTemp ? 0 : zDb);
         1445  +      }
  1557   1446       }
  1558   1447   
  1559         -    else if( sParse.pNewTrigger ){
  1560         -      rc = renameResolveTrigger(&sParse, bTemp ? 0 : zDb);
         1448  +    if( rc!=SQLITE_OK ){
         1449  +      renameColumnParseError(context, 1, argv[2], argv[3], &sParse);
  1561   1450       }
         1451  +    renameParseCleanup(&sParse);
  1562   1452     }
  1563   1453   
  1564         -  if( rc!=SQLITE_OK ){
  1565         -    renameColumnParseError(context, 1, argv[2], argv[3], &sParse);
  1566         -  }
  1567         -  renameParseCleanup(&sParse);
  1568         -
  1569   1454   #ifndef SQLITE_OMIT_AUTHORIZATION
  1570   1455     db->xAuth = xAuth;
  1571   1456   #endif
  1572   1457   }
  1573   1458   
  1574   1459   /*
  1575   1460   ** Register built-in functions used to help implement ALTER TABLE