sqlite3/alter.c

   1 /*
   2 ** 2005 February 15
   3 **
   4 ** The author disclaims copyright to this source code.  In place of
   5 ** a legal notice, here is a blessing:
   6 **
   7 **    May you do good and not evil.
   8 **    May you find forgiveness for yourself and forgive others.
   9 **    May you share freely, never taking more than you give.
  10 **
  11 *************************************************************************
  12 ** This file contains C code routines that used to generate VDBE code
  13 ** that implements the ALTER TABLE command.
  14 **
  15 ** $Id$
  16 */
  17 #include "sqliteInt.h"
  18 #include <ctype.h>
  19
  20 /*
  21 ** The code in this file only exists if we are not omitting the
  22 ** ALTER TABLE logic from the build.
  23 */
  24 #ifndef SQLITE_OMIT_ALTERTABLE
  25
  26
  27 /*
  28 ** This function is used by SQL generated to implement the
  29 ** ALTER TABLE command. The first argument is the text of a CREATE TABLE or
  30 ** CREATE INDEX command. The second is a table name. The table name in
  31 ** the CREATE TABLE or CREATE INDEX statement is replaced with the second
  32 ** argument and the result returned. Examples:
  33 **
  34 ** sqlite_rename_table('CREATE TABLE abc(a, b, c)', 'def')
  35 **     -> 'CREATE TABLE def(a, b, c)'
  36 **
  37 ** sqlite_rename_table('CREATE INDEX i ON abc(a)', 'def')
  38 **     -> 'CREATE INDEX i ON def(a, b, c)'
  39 */
  40 static void renameTableFunc(
  41                 sqlite3_context *context,
  42                 int argc,
  43                 sqlite3_value **argv
  44 ){
  45         unsigned char const *zSql = sqlite3_value_text(argv[0]);
  46         unsigned char const *zTableName = sqlite3_value_text(argv[1]);
  47
  48         int token;
  49         Token tname;
  50         char const *zCsr = zSql;
  51         int len = 0;
  52         char *zRet;
  53
  54         /* The principle used to locate the table name in the CREATE TABLE
  55          * statement is that the table name is the first token that is immediatedly
  56          * followed by a left parenthesis - TK_LP.
  57          * */
  58         if( zSql )
  59         {
  60                 do {
  61                         /* Store the token that zCsr points to in tname. */
  62                         tname.z = zCsr;
  63                         tname.n = len;
  64
  65                         /* Advance zCsr to the next token. Store that token type in
  66                          * 'token', and it's length in 'len' (to be used next iteration of
  67                          * this loop).
  68                          * */
  69                         do {
  70                                 zCsr += len;
  71                                 len = sqlite3GetToken(zCsr, &token);
  72                         } while( token==TK_SPACE );
  73
  74                         assert( len>0 );
  75                 } while( token!=TK_LP );
  76
  77                 zRet = sqlite3MPrintf("%.*s%Q%s", tname.z - zSql, zSql,
  78                                 zTableName, tname.z+tname.n);
  79                 sqlite3_result_text(context, zRet, -1, sqlite3FreeX);
  80         }
  81 }
  82
  83 #ifndef SQLITE_OMIT_TRIGGER
  84 /*
  85  * This function is used by SQL generated to implement the ALTER TABLE command.
  86  * The first argument is the text of a CREATE TRIGGER statement. The second is
  87  * a table name. The table name in the CREATE TRIGGER statement is replaced
  88  * with the second argument and the result returned. This is analagous to
  89  * renameTableFunc() above, except for CREATE TRIGGER, not CREATE INDEX and
  90  * CREATE TABLE.
  91  * */
  92 static void renameTriggerFunc(
  93                 sqlite3_context *context,
  94                 int argc,
  95                 sqlite3_value **argv
  96 ){
  97         unsigned char const *zSql = sqlite3_value_text(argv[0]);
  98         unsigned char const *zTableName = sqlite3_value_text(argv[1]);
  99
 100         int token;
 101         Token tname;
 102         int dist = 3;
 103         char const *zCsr = zSql;
 104         int len = 0;
 105         char *zRet;
 106
 107         /* The principle used to locate the table name in the CREATE TRIGGER
 108          * statement is that the table name is the first token that is immediatedly
 109          * preceded by either TK_ON or TK_DOT and immediatedly followed by one of
 110          * TK_WHEN, TK_BEGIN or TK_FOR.
 111          * */
 112         if( zSql )
 113         {
 114                 do {
 115                         /* Store the token that zCsr points to in tname. */
 116                         tname.z = zCsr;
 117                         tname.n = len;
 118
 119                         /* Advance zCsr to the next token. Store that token type in
 120                          * 'token', and it's length in 'len' (to be used next iteration of
 121                          * this loop).
 122                          * */
 123                         do {
 124                                 zCsr += len;
 125                                 len = sqlite3GetToken(zCsr, &token);
 126                         }while( token==TK_SPACE );
 127
 128                         assert( len>0 );
 129
 130                         /* Variable 'dist' stores the number of tokens read since the most
 131                          * recent TK_DOT or TK_ON. This means that when a WHEN, FOR or
 132                          * BEGIN token is read and 'dist' equals 2, the condition stated
 133                          * above to be met.
 134                          *
 135                          * Note that ON cannot be a database, table or column name, so
 136                          * there is no need to worry about syntax like CREATE TRIGGER ...
 137                          * ON ON.ON BEGIN ..." etc.
 138                          * */
 139                         dist++;
 140                         if( token==TK_DOT || token==TK_ON )
 141                         {
 142                                 dist = 0;
 143                         }
 144                 } while( dist!=2 || (
 145                                         token != TK_WHEN &&
 146                                         token != TK_FOR &&
 147                                         token != TK_BEGIN) );
 148
 149                 /* Variable tname now contains the token that is the old table-name in
 150                  * the CREATE TRIGGER statement.
 151                  * */
 152                 zRet = sqlite3MPrintf("%.*s%Q%s", tname.z - zSql, zSql,
 153                                 zTableName, tname.z+tname.n);
 154                 sqlite3_result_text(context, zRet, -1, sqlite3FreeX);
 155         }
 156 }
 157 #endif   /* !SQLITE_OMIT_TRIGGER */
 158
 159
 160 /**
 161  * Register built-in functions used to help implement ALTER TABLE
 162  *
 163  * */
 164 void sqlite3AlterFunctions(sqlite3 *db)
 165 {
 166         static const struct {
 167                 char *zName;
 168                 signed char nArg;
 169                 void (*xFunc)(sqlite3_context*,int,sqlite3_value **);
 170         } aFuncs[] = {
 171                 { "sqlite_rename_table",    2, renameTableFunc},
 172 #ifndef SQLITE_OMIT_TRIGGER
 173                 { "sqlite_rename_trigger",  2, renameTriggerFunc},
 174 #endif
 175         };
 176         int i;
 177
 178         for( i=0; i < sizeof(aFuncs)/sizeof(aFuncs[0]); i++)
 179         {
 180                 sqlite3_create_function(db, aFuncs[i].zName, aFuncs[i].nArg,
 181                                 SQLITE_UTF8, 0, aFuncs[i].xFunc, 0, 0);
 182         }
 183 }
 184
 185
 186 /*
 187  * Generate the text of a WHERE expression which can be used to select all
 188  * temporary triggers on table pTab from the sqlite_temp_master table. If
 189  * table pTab has no temporary triggers, or is itself stored in the temporary
 190  * database, NULL is returned.
 191  * */
 192 static char *whereTempTriggers(Parse *pParse, Table *pTab)
 193 {
 194         Trigger *pTrig;
 195         char *zWhere = 0;
 196         char *tmp = 0;
 197         if( 1 != pTab->iDb )
 198         {
 199                 for( pTrig=pTab->pTrigger; pTrig; pTrig=pTrig->pNext )
 200                 {
 201                         if( 1 == pTrig->iDb )
 202                         {
 203                                 if( !zWhere )
 204                                         zWhere = sqlite3MPrintf("name=%Q", pTrig->name);
 205                                 else {
 206                                         tmp = zWhere;
 207                                         zWhere = sqlite3MPrintf("%s OR name=%Q",
 208                                                         zWhere, pTrig->name);
 209                                         sqliteFree(tmp);
 210                                 }
 211                         }
 212                 }
 213         }
 214         return zWhere;
 215 }
 216
 217
 218 /*
 219  * Generate code to drop and reload the internal representation of table pTab
 220  * from the database, including triggers and temporary triggers. Argument
 221  * zName is the name of the table in the database schema at the time the
 222  * generated code is executed. This can be different from pTab->zName if this
 223  * function is being called to code part of an "ALTER TABLE RENAME TO"
 224  * statement.
 225  * */
 226 static void reloadTableSchema(Parse *pParse, Table *pTab, const char *zName)
 227 {
 228         Vdbe *v;
 229         char *zWhere;
 230         int iDb;
 231 #ifndef SQLITE_OMIT_TRIGGER
 232         Trigger *pTrig;
 233 #endif
 234
 235         if( ! (v = sqlite3GetVdbe(pParse))) return;
 236         iDb = pTab->iDb;
 237
 238 #ifndef SQLITE_OMIT_TRIGGER
 239         /* Drop any table triggers from the internal schema. */
 240         for( pTrig = pTab->pTrigger; pTrig; pTrig = pTrig->pNext )
 241         {
 242                 assert( pTrig->iDb==iDb || pTrig->iDb==1 );
 243                 sqlite3VdbeOp3(v, OP_DropTrigger, pTrig->iDb, 0, pTrig->name, 0);
 244         }
 245 #endif
 246
 247         /* Drop the table and index from the internal schema */
 248         sqlite3VdbeOp3(v, OP_DropTable, iDb, 0, pTab->zName, 0);
 249
 250         /* Reload the table, index and permanent trigger schemas. */
 251         if( ! (zWhere = sqlite3MPrintf("tbl_name=%Q", zName))) return;
 252         sqlite3VdbeOp3(v, OP_ParseSchema, iDb, 0, zWhere, P3_DYNAMIC);
 253
 254 #ifndef SQLITE_OMIT_TRIGGER
 255         /* Now, if the table is not stored in the temp database, reload any temp
 256          * triggers. Don't use IN(...) in case SQLITE_OMIT_SUBQUERY is defined.
 257          * */
 258         if( (zWhere=whereTempTriggers(pParse, pTab)) )
 259                 sqlite3VdbeOp3(v, OP_ParseSchema, 1, 0, zWhere, P3_DYNAMIC);
 260 #endif
 261 }
 262
 263
 264 /**
 265  * Generate code to implement the "ALTER TABLE xxx RENAME TO yyy" command.
 266  * */
 267 void sqlite3AlterRenameTable(
 268                 Parse *pParse,            /* Parser context. */
 269                 SrcList *pSrc,            /* The table to rename. */
 270                 Token *pName              /* The new table name. */
 271 ){
 272         int iDb;                  /* Database that contains the table */
 273         char *zDb;                /* Name of database iDb */
 274         Table *pTab;              /* Table being renamed */
 275         char *zName = 0;          /* NULL-terminated version of pName */
 276         sqlite3 *db = pParse->db; /* Database connection */
 277         Vdbe *v;
 278 #ifndef SQLITE_OMIT_TRIGGER
 279         char *zWhere = 0;         /* Where clause to locate temp triggers */
 280 #endif
 281
 282         assert( pSrc->nSrc==1 );
 283
 284         pTab = sqlite3LocateTable(pParse, pSrc->a[0].zName, pSrc->a[0].zDatabase);
 285         if( !pTab ) goto exit_rename_table;
 286         iDb = pTab->iDb;
 287         zDb = db->aDb[iDb].zName;
 288
 289         /* Get a NULL terminated version of the new table name. */
 290         zName = sqlite3NameFromToken(pName);
 291         if( !zName ) goto exit_rename_table;
 292
 293         /*
 294          * Check that a table or index named 'zName' does not already exist in
 295          * database iDb. If so, this is an error.
 296          * */
 297         if( sqlite3FindTable(db, zName, zDb) || sqlite3FindIndex(db, zName, zDb) )
 298         {
 299                 sqlite3ErrorMsg(pParse,
 300                                 "there is already another table or index with this name: %s",
 301                                 zName);
 302                 goto exit_rename_table;
 303         }
 304
 305         /*
 306          * Make sure it is not a system table being altered, or a reserved name
 307          * that the table is being renamed to.
 308          * */
 309         if( strlen(pTab->zName)>6 && 0==sqlite3StrNICmp(pTab->zName, "sqlite_", 7) )
 310         {
 311                 sqlite3ErrorMsg(pParse, "table %s may not be altered", pTab->zName);
 312                 goto exit_rename_table;
 313         }
 314         if( SQLITE_OK != sqlite3CheckObjectName(pParse, zName))
 315                 goto exit_rename_table;
 316
 317 #ifndef SQLITE_OMIT_AUTHORIZATION
 318         /* Invoke the authorization callback. */
 319         if( sqlite3AuthCheck(pParse, SQLITE_ALTER_TABLE, zDb, pTab->zName, 0))
 320                 goto exit_rename_table;
 321 #endif
 322
 323         /*
 324          * Begin a transaction and code the VerifyCookie for database iDb. Then
 325          * modify the schema cookie (since the ALTER TABLE modifies the schema).
 326          * */
 327         if( 0 == (v = sqlite3GetVdbe(pParse)))
 328                 goto exit_rename_table;
 329
 330         sqlite3BeginWriteOperation(pParse, 0, iDb);
 331         sqlite3ChangeCookie(db, v, iDb);
 332
 333         /* Modify the sqlite_master table to use the new table name. */
 334         sqlite3NestedParse(pParse,
 335                         "UPDATE %Q.%s SET "
 336 #ifdef SQLITE_OMIT_TRIGGER
 337                         "sql = sqlite_rename_table(sql, %Q), "
 338 #else
 339                         "sql = CASE "
 340                         "WHEN type = 'trigger' THEN sqlite_rename_trigger(sql, %Q)"
 341                         "ELSE sqlite_rename_table(sql, %Q) END, "
 342 #endif
 343                         "tbl_name = %Q, "
 344                         "name = CASE "
 345                         "WHEN type='table' THEN %Q "
 346                         "WHEN name LIKE 'sqlite_autoindex%%' AND type='index' THEN "
 347                         "'sqlite_autoindex_' || %Q || substr(name, %d+18,10) "
 348                         "ELSE name END "
 349                         "WHERE tbl_name=%Q AND "
 350                         "(type='table' OR type='index' OR type='trigger');",
 351                         zDb, SCHEMA_TABLE(iDb), zName, zName, zName,
 352 #ifndef SQLITE_OMIT_TRIGGER
 353                         zName,
 354 #endif
 355                         zName, strlen(pTab->zName), pTab->zName
 356                                 );
 357
 358 #ifndef SQLITE_OMIT_AUTOINCREMENT
 359         /*
 360          * If the sqlite_sequence table exists in this database, then update it
 361          * with the new table name.
 362          * */
 363         if( sqlite3FindTable(db, "sqlite_sequence", zDb))
 364                 sqlite3NestedParse(pParse,
 365                                 "UPDATE %Q.sqlite_sequence set name = %Q WHERE name = %Q",
 366                                 zDb, zName, pTab->zName);
 367 #endif
 368
 369 #ifndef SQLITE_OMIT_TRIGGER
 370         /*
 371          * If there are TEMP triggers on this table, modify the sqlite_temp_master
 372          * table. Don't do this if the table being ALTERed is itself located in
 373          * the temp database.
 374          * */
 375         if( (zWhere = whereTempTriggers( pParse, pTab )))
 376         {
 377                 sqlite3NestedParse( pParse,
 378                                 "UPDATE sqlite_temp_master SET "
 379                                 "sql = sqlite_rename_trigger(sql, %Q), "
 380                                 "tbl_name = %Q "
 381                                 "WHERE %s;", zName, zName, zWhere);
 382                 sqliteFree( zWhere );
 383         }
 384 #endif
 385
 386         /* Drop and reload the internal table schema. */
 387         reloadTableSchema( pParse, pTab, zName );
 388
 389 exit_rename_table:
 390         sqlite3SrcListDelete( pSrc );
 391         sqliteFree( zName );
 392 }
 393
 394
 395 /*
 396  * This function is called after an "ALTER TABLE ... ADD" statement has been
 397  * parsed. Argument pColDef contains the text of the new column definition.
 398  *
 399  * The Table structure pParse->pNewTable was extended to include the new
 400  * column during parsing.
 401  * */
 402 void sqlite3AlterFinishAddColumn(Parse *pParse, Token *pColDef)
 403 {
 404         Table *pNew;              /* Copy of pParse->pNewTable */
 405         Table *pTab;              /* Table being altered */
 406         int iDb;                  /* Database number */
 407         const char *zDb;          /* Database name */
 408         const char *zTab;         /* Table name */
 409         char *zCol;               /* Null-terminated column definition */
 410         Column *pCol;             /* The new column */
 411         Expr *pDflt;              /* Default value for the new column */
 412         Vdbe *v;
 413
 414         if( pParse->nErr ) return;
 415         pNew = pParse->pNewTable;
 416         assert( pNew );
 417
 418         iDb = pNew->iDb;
 419         zDb = pParse->db->aDb[iDb].zName;
 420         zTab = pNew->zName;
 421         pCol = &pNew->aCol[pNew->nCol-1];
 422         pDflt = pCol->pDflt;
 423         pTab = sqlite3FindTable( pParse->db, zTab, zDb );
 424         assert( pTab );
 425
 426         /*
 427          * If the default value for the new column was specified with a literal
 428          * NULL, then set pDflt to 0. This simplifies checking for an SQL NULL
 429          * default below.
 430          * */
 431         if( pDflt && pDflt->op==TK_NULL )
 432                 pDflt = 0;
 433
 434         /*
 435          * Check that the new column is not specified as PRIMARY KEY or UNIQUE.
 436          * If there is a NOT NULL constraint, then the default value for the
 437          * column must not be NULL.
 438          * */
 439         if( pCol->isPrimKey )
 440         {
 441                 sqlite3ErrorMsg( pParse, "Cannot add a PRIMARY KEY column");
 442                 return;
 443         }
 444         if( pNew->pIndex )
 445         {
 446                 sqlite3ErrorMsg( pParse, "Cannot add a UNIQUE column");
 447                 return;
 448         }
 449         if( pCol->notNull && !pDflt )
 450         {
 451                 sqlite3ErrorMsg( pParse,
 452                                 "Cannot add a NOT NULL column with default value NULL");
 453                 return;
 454         }
 455
 456         /*
 457          * Ensure the default expression is something that sqlite3ValueFromExpr()
 458          * can handle (i.e. not CURRENT_TIME etc.)
 459          * */
 460         if( pDflt )
 461         {
 462                 sqlite3_value *pVal;
 463                 if( sqlite3ValueFromExpr(pDflt, SQLITE_UTF8, SQLITE_AFF_NONE, &pVal))
 464                 {
 465                         /* malloc() has failed */
 466                         return;
 467                 }
 468                 if( !pVal )
 469                 {
 470                         sqlite3ErrorMsg( pParse,
 471                                         "Cannot add a column with non-constant default");
 472                         return;
 473                 }
 474                 sqlite3ValueFree(pVal);
 475         }
 476
 477         /* Modify the CREATE TABLE statement. */
 478         if( (zCol = sqliteStrNDup(pColDef->z, pColDef->n)))
 479         {
 480                 char *zEnd = &zCol[pColDef->n-1];
 481                 while( (zEnd>zCol && *zEnd==';') || isspace(*(unsigned char *)zEnd))
 482                         *zEnd-- = '\0';
 483
 484                 sqlite3NestedParse( pParse,
 485                                 "UPDATE %Q.%s SET "
 486                                 "sql = substr(sql,1,%d) || ', "
 487                                 "' || %Q || substr(sql,%d,length(sql)) "
 488                                 "WHERE type = 'table' AND name = %Q",
 489                                 zDb, SCHEMA_TABLE(iDb), pNew->addColOffset, zCol,
 490                                 pNew->addColOffset+1, zTab
 491                                 );
 492                 sqliteFree( zCol );
 493         }
 494
 495         /*
 496          * If the default value of the new column is NULL, then set the file
 497          * format to 2. If the default value of the new column is not NULL, the
 498          * file format becomes 3.
 499          * */
 500         if( (v = sqlite3GetVdbe(pParse)))
 501         {
 502                 int f = (pDflt?3:2);
 503
 504                 /* Only set the file format to $f if it is currently less than $f. */
 505                 sqlite3VdbeAddOp(v, OP_ReadCookie, iDb, 1);
 506                 sqlite3VdbeAddOp(v, OP_Integer, f, 0);
 507                 sqlite3VdbeAddOp(v, OP_Ge, 0, sqlite3VdbeCurrentAddr(v)+3);
 508                 sqlite3VdbeAddOp(v, OP_Integer, f, 0);
 509                 sqlite3VdbeAddOp(v, OP_SetCookie, iDb, 1);
 510         }
 511
 512         /* Reload the schema of the modified table. */
 513         reloadTableSchema(pParse, pTab, pTab->zName);
 514 }
 515
 516
 517 /*
 518  * This function is called by the parser after the table-name in an "ALTER
 519  * TABLE <table-name> ADD" statement is parsed. Argument pSrc is the full-name
 520  * of the table being altered.
 521  *
 522  * This routine makes a (partial) copy of the Table structure for the table
 523  * being altered and sets Parse.pNewTable to point to it. Routines called by
 524  * the parser as the column definition is parsed (i.e. sqlite3AddColumn()) add
 525  * the new Column data to the copy. The copy of the Table structure is deleted
 526  * by tokenize.c after parsing is finished.
 527  *
 528  * Routine sqlite3AlterFinishAddColumn() will be called to complete coding the
 529  * "ALTER TABLE ... ADD" statement.
 530  * */
 531 void sqlite3AlterBeginAddColumn(Parse *pParse, SrcList *pSrc)
 532 {
 533         Table *pNew;
 534         Table *pTab;
 535         Vdbe *v;
 536         int iDb;
 537         int i;
 538         int nAlloc;
 539
 540         /* Look up the table being altered. */
 541         assert( !pParse->pNewTable );
 542         pTab = sqlite3LocateTable(pParse, pSrc->a[0].zName, pSrc->a[0].zDatabase);
 543         if( !pTab ) goto exit_begin_add_column;
 544
 545         /* Make sure this is not an attempt to ALTER a view. */
 546         if( pTab->pSelect )
 547         {
 548                 sqlite3ErrorMsg(pParse, "Cannot add a column to a view");
 549                 goto exit_begin_add_column;
 550         }
 551
 552         assert( pTab->addColOffset>0 );
 553         iDb = pTab->iDb;
 554
 555         /*
 556          * Put a copy of the Table struct in Parse.pNewTable for the
 557          * sqlite3AddColumn() function and friends to modify.
 558          * */
 559         pNew = (Table *)sqliteMalloc(sizeof(Table));
 560         if( !pNew ) goto exit_begin_add_column;
 561         pParse->pNewTable = pNew;
 562         pNew->nCol = pTab->nCol;
 563         assert( pNew->nCol>0 );
 564         nAlloc = (((pNew->nCol-1)/8)*8)+8;
 565         assert( nAlloc>=pNew->nCol && nAlloc%8==0 && nAlloc-pNew->nCol<8 );
 566         pNew->aCol = (Column *)sqliteMalloc(sizeof(Column)*nAlloc);
 567         pNew->zName = sqliteStrDup(pTab->zName);
 568         if( !pNew->aCol || !pNew->zName )
 569                 goto exit_begin_add_column;
 570         memcpy(pNew->aCol, pTab->aCol, sizeof(Column)*pNew->nCol);
 571         for( i=0; i<pNew->nCol; i++ )
 572         {
 573                 Column *pCol = &pNew->aCol[i];
 574                 pCol->zName = sqliteStrDup(pCol->zName);
 575                 pCol->zType = 0;
 576                 pCol->pDflt = 0;
 577         }
 578         pNew->iDb = iDb;
 579         pNew->addColOffset = pTab->addColOffset;
 580         pNew->nRef = 1;
 581
 582         /* Begin a transaction and increment the schema cookie.  */
 583         sqlite3BeginWriteOperation(pParse, 0, iDb);
 584         v = sqlite3GetVdbe(pParse);
 585         if( !v ) goto exit_begin_add_column;
 586         sqlite3ChangeCookie(pParse->db, v, iDb);
 587
 588 exit_begin_add_column:
 589         sqlite3SrcListDelete(pSrc);
 590 }
 591 #endif  /* SQLITE_ALTER_TABLE */