/ Check-in [7edd26ed]
Login

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

Overview
Comment:Add the "PRAGMA legacy_alter_table=ON" command to enable the pre-3.25.0 behavior of ALTER TABLE that does not modify the bodies of triggers or views or the WHERE clause of a partial index. Enable the legacy behavior by default when running the xRename method of virtual tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 7edd26ed27ed1e7eab603058f7d55f2eac45e7bd1908bfa5f32293611883b157
User & Date: drh 2018-09-20 20:15:02
Context
2018-09-24
20:48
Add the "PRAGMA legacy_alter_table=ON" command to enable the pre-3.25.0 behavior of ALTER TABLE that does not modify the bodies of triggers or views or the WHERE clause of a partial index. Enable the legacy behavior by default when running the xRename method of virtual tables. check-in: 31dc8b19 user: drh tags: branch-3.25
2018-09-20
20:43
Minor code simplification. check-in: 22ae8a52 user: drh tags: trunk
20:15
Add the "PRAGMA legacy_alter_table=ON" command to enable the pre-3.25.0 behavior of ALTER TABLE that does not modify the bodies of triggers or views or the WHERE clause of a partial index. Enable the legacy behavior by default when running the xRename method of virtual tables. check-in: 7edd26ed user: drh tags: trunk
19:02
Combine the Expr.pTab and Expr.pWin fields into a union named "y". Add a new EP_WinFunc property that is only true if Expr.y.pWin is a valid pointer. This reduces the size of the Expr object by 8 bytes, reduces the overall amount of code, and shaves over 1 million cycles off of the speed test. check-in: ad130bb8 user: drh tags: trunk
17:21
By default, make all "ALTER TABLE RENAME" statements executed within a virtual table xRename() method exhibit the legacy behaviour. Closed-Leaf check-in: 71947337 user: dan tags: legacy-alter-table
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/alter.c.

   165    165     ** table.
   166    166     */
   167    167     v = sqlite3GetVdbe(pParse);
   168    168     if( v==0 ){
   169    169       goto exit_rename_table;
   170    170     }
   171    171   
   172         -  /* If this is a virtual table, invoke the xRename() function if
   173         -  ** one is defined. The xRename() callback will modify the names
   174         -  ** of any resources used by the v-table implementation (including other
   175         -  ** SQLite tables) that are identified by the name of the virtual table.
   176         -  */
   177         -#ifndef SQLITE_OMIT_VIRTUALTABLE
   178         -  if( pVTab ){
   179         -    int i = ++pParse->nMem;
   180         -    sqlite3VdbeLoadString(v, i, zName);
   181         -    sqlite3VdbeAddOp4(v, OP_VRename, i, 0, 0,(const char*)pVTab, P4_VTAB);
   182         -    sqlite3MayAbort(pParse);
   183         -  }
   184         -#endif
   185         -
   186    172     /* figure out how many UTF-8 characters are in zName */
   187    173     zTabName = pTab->zName;
   188    174     nTabName = sqlite3Utf8CharLen(zTabName, -1);
   189    175   
   190    176     /* Rewrite all CREATE TABLE, INDEX, TRIGGER or VIEW statements in
   191    177     ** the schema to use the new table name.  */
   192    178     sqlite3NestedParse(pParse, 
................................................................................
   235    221               "tbl_name = "
   236    222                 "CASE WHEN tbl_name=%Q COLLATE nocase AND "
   237    223                 "          sqlite_rename_test(%Q, sql, type, name, 1) "
   238    224                 "THEN %Q ELSE tbl_name END "
   239    225               "WHERE type IN ('view', 'trigger')"
   240    226           , zDb, zTabName, zName, zTabName, zDb, zName);
   241    227     }
          228  +
          229  +  /* If this is a virtual table, invoke the xRename() function if
          230  +  ** one is defined. The xRename() callback will modify the names
          231  +  ** of any resources used by the v-table implementation (including other
          232  +  ** SQLite tables) that are identified by the name of the virtual table.
          233  +  */
          234  +#ifndef SQLITE_OMIT_VIRTUALTABLE
          235  +  if( pVTab ){
          236  +    int i = ++pParse->nMem;
          237  +    sqlite3VdbeLoadString(v, i, zName);
          238  +    sqlite3VdbeAddOp4(v, OP_VRename, i, 0, 0,(const char*)pVTab, P4_VTAB);
          239  +    sqlite3MayAbort(pParse);
          240  +  }
          241  +#endif
   242    242   
   243    243     renameReloadSchema(pParse, iDb);
   244    244     renameTestSchema(pParse, zDb, iDb==1);
   245    245   
   246    246   exit_rename_table:
   247    247     sqlite3SrcListDelete(db, pSrc);
   248    248     sqlite3DbFree(db, zName);
................................................................................
  1418   1418       sWalker.xExprCallback = renameTableExprCb;
  1419   1419       sWalker.xSelectCallback = renameTableSelectCb;
  1420   1420       sWalker.u.pRename = &sCtx;
  1421   1421   
  1422   1422       rc = renameParseSql(&sParse, zDb, 1, db, zInput, bTemp);
  1423   1423   
  1424   1424       if( rc==SQLITE_OK ){
         1425  +      int isLegacy = (db->flags & SQLITE_LegacyAlter);
  1425   1426         if( sParse.pNewTable ){
  1426   1427           Table *pTab = sParse.pNewTable;
  1427   1428   
  1428   1429           if( pTab->pSelect ){
  1429         -          NameContext sNC;
  1430         -          memset(&sNC, 0, sizeof(sNC));
  1431         -          sNC.pParse = &sParse;
         1430  +          if( isLegacy==0 ){
         1431  +            NameContext sNC;
         1432  +            memset(&sNC, 0, sizeof(sNC));
         1433  +            sNC.pParse = &sParse;
  1432   1434   
  1433         -          sqlite3SelectPrep(&sParse, pTab->pSelect, &sNC);
  1434         -          if( sParse.nErr ) rc = sParse.rc;
  1435         -          sqlite3WalkSelect(&sWalker, pTab->pSelect);
         1435  +            sqlite3SelectPrep(&sParse, pTab->pSelect, &sNC);
         1436  +            if( sParse.nErr ) rc = sParse.rc;
         1437  +            sqlite3WalkSelect(&sWalker, pTab->pSelect);
         1438  +          }
  1436   1439           }else{
  1437   1440             /* Modify any FK definitions to point to the new table. */
  1438   1441   #ifndef SQLITE_OMIT_FOREIGN_KEY
  1439   1442             if( db->flags & SQLITE_ForeignKeys ){
  1440   1443               FKey *pFKey;
  1441   1444               for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
  1442   1445                 if( sqlite3_stricmp(pFKey->zTo, zOld)==0 ){
................................................................................
  1447   1450   #endif
  1448   1451   
  1449   1452             /* If this is the table being altered, fix any table refs in CHECK
  1450   1453             ** expressions. Also update the name that appears right after the
  1451   1454             ** "CREATE [VIRTUAL] TABLE" bit. */
  1452   1455             if( sqlite3_stricmp(zOld, pTab->zName)==0 ){
  1453   1456               sCtx.pTab = pTab;
  1454         -            sqlite3WalkExprList(&sWalker, pTab->pCheck);
         1457  +            if( isLegacy==0 ){
         1458  +              sqlite3WalkExprList(&sWalker, pTab->pCheck);
         1459  +            }
  1455   1460               renameTokenFind(&sParse, &sCtx, pTab->zName);
  1456   1461             }
  1457   1462           }
  1458   1463         }
  1459   1464   
  1460   1465         else if( sParse.pNewIndex ){
  1461   1466           renameTokenFind(&sParse, &sCtx, sParse.pNewIndex->zName);
  1462         -        sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
         1467  +        if( isLegacy==0 ){
         1468  +          sqlite3WalkExpr(&sWalker, sParse.pNewIndex->pPartIdxWhere);
         1469  +        }
  1463   1470         }
  1464   1471   
  1465   1472   #ifndef SQLITE_OMIT_TRIGGER
  1466   1473         else{
  1467   1474           Trigger *pTrigger = sParse.pNewTrigger;
  1468   1475           TriggerStep *pStep;
  1469   1476           if( 0==sqlite3_stricmp(sParse.pNewTrigger->table, zOld) 
  1470   1477               && sCtx.pTab->pSchema==pTrigger->pTabSchema
  1471   1478             ){
  1472   1479             renameTokenFind(&sParse, &sCtx, sParse.pNewTrigger->table);
  1473   1480           }
  1474   1481   
  1475         -        rc = renameResolveTrigger(&sParse, bTemp ? 0 : zDb);
  1476         -        if( rc==SQLITE_OK ){
  1477         -          renameWalkTrigger(&sWalker, pTrigger);
  1478         -          for(pStep=pTrigger->step_list; pStep; pStep=pStep->pNext){
  1479         -            if( pStep->zTarget && 0==sqlite3_stricmp(pStep->zTarget, zOld) ){
  1480         -              renameTokenFind(&sParse, &sCtx, pStep->zTarget);
         1482  +        if( isLegacy==0 ){
         1483  +          rc = renameResolveTrigger(&sParse, bTemp ? 0 : zDb);
         1484  +          if( rc==SQLITE_OK ){
         1485  +            renameWalkTrigger(&sWalker, pTrigger);
         1486  +            for(pStep=pTrigger->step_list; pStep; pStep=pStep->pNext){
         1487  +              if( pStep->zTarget && 0==sqlite3_stricmp(pStep->zTarget, zOld) ){
         1488  +                renameTokenFind(&sParse, &sCtx, pStep->zTarget);
         1489  +              }
  1481   1490               }
  1482   1491             }
  1483   1492           }
  1484   1493         }
  1485   1494   #endif
  1486   1495       }
  1487   1496   
................................................................................
  1531   1540     int NotUsed,
  1532   1541     sqlite3_value **argv
  1533   1542   ){
  1534   1543     sqlite3 *db = sqlite3_context_db_handle(context);
  1535   1544     char const *zDb = (const char*)sqlite3_value_text(argv[0]);
  1536   1545     char const *zInput = (const char*)sqlite3_value_text(argv[1]);
  1537   1546     int bTemp = sqlite3_value_int(argv[4]);
         1547  +  int isLegacy = (db->flags & SQLITE_LegacyAlter);
  1538   1548   
  1539   1549   #ifndef SQLITE_OMIT_AUTHORIZATION
  1540   1550     sqlite3_xauth xAuth = db->xAuth;
  1541   1551     db->xAuth = 0;
  1542   1552   #endif
  1543   1553   
  1544   1554     UNUSED_PARAMETER(NotUsed);
  1545   1555     if( zDb && zInput ){
  1546   1556       int rc;
  1547   1557       Parse sParse;
  1548   1558       rc = renameParseSql(&sParse, zDb, 1, db, zInput, bTemp);
  1549   1559       if( rc==SQLITE_OK ){
  1550         -      if( sParse.pNewTable && sParse.pNewTable->pSelect ){
         1560  +      if( isLegacy==0 && sParse.pNewTable && sParse.pNewTable->pSelect ){
  1551   1561           NameContext sNC;
  1552   1562           memset(&sNC, 0, sizeof(sNC));
  1553   1563           sNC.pParse = &sParse;
  1554   1564           sqlite3SelectPrep(&sParse, sParse.pNewTable->pSelect, &sNC);
  1555   1565           if( sParse.nErr ) rc = sParse.rc;
  1556   1566         }
  1557   1567   
  1558   1568         else if( sParse.pNewTrigger ){
  1559         -        rc = renameResolveTrigger(&sParse, bTemp ? 0 : zDb);
         1569  +        if( isLegacy==0 ){
         1570  +          rc = renameResolveTrigger(&sParse, bTemp ? 0 : zDb);
         1571  +        }
  1560   1572           if( rc==SQLITE_OK ){
  1561   1573             int i1 = sqlite3SchemaToIndex(db, sParse.pNewTrigger->pTabSchema);
  1562   1574             int i2 = sqlite3FindDbName(db, zDb);
  1563   1575             if( i1==i2 ) sqlite3_result_int(context, 1);
  1564   1576           }
  1565   1577         }
  1566   1578       }

Changes to src/pragma.h.

   389    389    {/* zName:     */ "key",
   390    390     /* ePragTyp:  */ PragTyp_KEY,
   391    391     /* ePragFlg:  */ 0,
   392    392     /* ColNames:  */ 0, 0,
   393    393     /* iArg:      */ 0 },
   394    394   #endif
   395    395   #if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
          396  + {/* zName:     */ "legacy_alter_table",
          397  +  /* ePragTyp:  */ PragTyp_FLAG,
          398  +  /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
          399  +  /* ColNames:  */ 0, 0,
          400  +  /* iArg:      */ SQLITE_LegacyAlter },
   396    401    {/* zName:     */ "legacy_file_format",
   397    402     /* ePragTyp:  */ PragTyp_FLAG,
   398    403     /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
   399    404     /* ColNames:  */ 0, 0,
   400    405     /* iArg:      */ SQLITE_LegacyFileFmt },
   401    406   #endif
   402    407   #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) && SQLITE_ENABLE_LOCKING_STYLE
................................................................................
   642    647    {/* zName:     */ "writable_schema",
   643    648     /* ePragTyp:  */ PragTyp_FLAG,
   644    649     /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
   645    650     /* ColNames:  */ 0, 0,
   646    651     /* iArg:      */ SQLITE_WriteSchema },
   647    652   #endif
   648    653   };
   649         -/* Number of pragmas: 60 on by default, 77 total. */
          654  +/* Number of pragmas: 61 on by default, 78 total. */

Changes to src/sqliteInt.h.

  1532   1532   #define SQLITE_DeferFKs       0x00080000  /* Defer all FK constraints */
  1533   1533   #define SQLITE_QueryOnly      0x00100000  /* Disable database changes */
  1534   1534   #define SQLITE_CellSizeCk     0x00200000  /* Check btree cell sizes on load */
  1535   1535   #define SQLITE_Fts3Tokenizer  0x00400000  /* Enable fts3_tokenizer(2) */
  1536   1536   #define SQLITE_EnableQPSG     0x00800000  /* Query Planner Stability Guarantee*/
  1537   1537   #define SQLITE_TriggerEQP     0x01000000  /* Show trigger EXPLAIN QUERY PLAN */
  1538   1538   #define SQLITE_ResetDatabase  0x02000000  /* Reset the database */
         1539  +#define SQLITE_LegacyAlter    0x04000000  /* Legacy ALTER TABLE behaviour */
  1539   1540   
  1540   1541   /* Flags used only if debugging */
  1541   1542   #ifdef SQLITE_DEBUG
  1542   1543   #define SQLITE_SqlTrace       0x08000000  /* Debug print SQL as it executes */
  1543   1544   #define SQLITE_VdbeListing    0x10000000  /* Debug listings of VDBE programs */
  1544   1545   #define SQLITE_VdbeTrace      0x20000000  /* True to trace VDBE execution */
  1545   1546   #define SQLITE_VdbeAddopTrace 0x40000000  /* Trace sqlite3VdbeAddOp() calls */

Changes to src/vdbe.c.

  7055   7055   ** P4 is a pointer to a virtual table object, an sqlite3_vtab structure.
  7056   7056   ** This opcode invokes the corresponding xRename method. The value
  7057   7057   ** in register P1 is passed as the zName argument to the xRename method.
  7058   7058   */
  7059   7059   case OP_VRename: {
  7060   7060     sqlite3_vtab *pVtab;
  7061   7061     Mem *pName;
  7062         -
         7062  +  int isLegacy;
         7063  +  
         7064  +  isLegacy = (db->flags & SQLITE_LegacyAlter);
         7065  +  db->flags |= SQLITE_LegacyAlter;
  7063   7066     pVtab = pOp->p4.pVtab->pVtab;
  7064   7067     pName = &aMem[pOp->p1];
  7065   7068     assert( pVtab->pModule->xRename );
  7066   7069     assert( memIsValid(pName) );
  7067   7070     assert( p->readOnly==0 );
  7068   7071     REGISTER_TRACE(pOp->p1, pName);
  7069   7072     assert( pName->flags & MEM_Str );
  7070   7073     testcase( pName->enc==SQLITE_UTF8 );
  7071   7074     testcase( pName->enc==SQLITE_UTF16BE );
  7072   7075     testcase( pName->enc==SQLITE_UTF16LE );
  7073   7076     rc = sqlite3VdbeChangeEncoding(pName, SQLITE_UTF8);
  7074   7077     if( rc ) goto abort_due_to_error;
  7075   7078     rc = pVtab->pModule->xRename(pVtab, pName->z);
         7079  +  if( isLegacy==0 ) db->flags &= ~SQLITE_LegacyAlter;
  7076   7080     sqlite3VtabImportErrmsg(p, pVtab);
  7077   7081     p->expired = 0;
  7078   7082     if( rc ) goto abort_due_to_error;
  7079   7083     break;
  7080   7084   }
  7081   7085   #endif
  7082   7086   

Added test/alterlegacy.test.

            1  +# 2018 September 20
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set testprefix alterlegacy
           16  +
           17  +# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
           18  +ifcapable !altertable {
           19  +  finish_test
           20  +  return
           21  +}
           22  +
           23  +do_execsql_test 1.0 {
           24  +  PRAGMA legacy_alter_table = 1;
           25  +  CREATE TABLE t1(a, b, CHECK(t1.a != t1.b));
           26  +  CREATE TABLE t2(a, b);
           27  +  CREATE INDEX t2expr ON t2(a) WHERE t2.b>0;
           28  +}
           29  +
           30  +do_execsql_test 1.1 {
           31  +  SELECT sql FROM sqlite_master
           32  +} {
           33  +  {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))}
           34  +  {CREATE TABLE t2(a, b)}
           35  +  {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0}
           36  +}
           37  +
           38  +# Legacy behavior is to corrupt the schema in this case, as the table name in
           39  +# the CHECK constraint is incorrect after "t1" is renamed. This version is
           40  +# slightly different - it rejects the change and rolls back the transaction.
           41  +do_catchsql_test 1.2 {
           42  +  ALTER TABLE t1 RENAME TO t1new;
           43  +} {1 {no such column: t1.a}}
           44  +
           45  +do_execsql_test 1.3 {
           46  +  CREATE TABLE t3(c, d);
           47  +  ALTER TABLE t3 RENAME TO t3new;
           48  +  DROP TABLE t3new;
           49  +}
           50  +
           51  +do_execsql_test 1.4 {
           52  +  SELECT sql FROM sqlite_master
           53  +} {
           54  +  {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))}
           55  +  {CREATE TABLE t2(a, b)}
           56  +  {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0}
           57  +}
           58  +
           59  +
           60  +do_catchsql_test 1.3 {
           61  +  ALTER TABLE t2 RENAME TO t2new;
           62  +} {1 {no such column: t2.b}}
           63  +do_execsql_test 1.4 {
           64  +  SELECT sql FROM sqlite_master
           65  +} {
           66  +  {CREATE TABLE t1(a, b, CHECK(t1.a != t1.b))}
           67  +  {CREATE TABLE t2(a, b)}
           68  +  {CREATE INDEX t2expr ON t2(a) WHERE t2.b>0}
           69  +}
           70  +
           71  +
           72  +#-------------------------------------------------------------------------
           73  +reset_db
           74  +ifcapable vtab {
           75  +  register_echo_module db
           76  +
           77  +  do_execsql_test 2.0 {
           78  +    PRAGMA legacy_alter_table = 1;
           79  +    CREATE TABLE abc(a, b, c);
           80  +    INSERT INTO abc VALUES(1, 2, 3);
           81  +    CREATE VIRTUAL TABLE eee USING echo('abc');
           82  +    SELECT * FROM eee;
           83  +  } {1 2 3}
           84  +
           85  +  do_execsql_test 2.1 {
           86  +    ALTER TABLE eee RENAME TO fff;
           87  +    SELECT * FROM fff;
           88  +  } {1 2 3}
           89  +
           90  +  db close
           91  +  sqlite3 db test.db
           92  +
           93  +  do_catchsql_test 2.2 {
           94  +    ALTER TABLE fff RENAME TO ggg;
           95  +  } {1 {no such module: echo}}
           96  +}
           97  +
           98  +#-------------------------------------------------------------------------
           99  +reset_db
          100  +
          101  +do_execsql_test 3.0 {
          102  +  PRAGMA legacy_alter_table = 1;
          103  +  CREATE TABLE txx(a, b, c);
          104  +  INSERT INTO txx VALUES(1, 2, 3);
          105  +  CREATE VIEW vvv AS SELECT main.txx.a, txx.b, c FROM txx;
          106  +  CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM txx AS one;
          107  +  CREATE VIEW temp.ttt AS SELECT main.txx.a, txx.b, one.b, main.one.a FROM txx AS one, txx;
          108  +}
          109  +
          110  +do_execsql_test 3.1.1 {
          111  +  SELECT * FROM vvv;
          112  +} {1 2 3}
          113  +do_execsql_test 3.1.2a {
          114  +  ALTER TABLE txx RENAME TO "t xx";
          115  +}
          116  +do_catchsql_test 3.1.2b {
          117  +  SELECT * FROM vvv;
          118  +} {1 {no such table: main.txx}}
          119  +do_execsql_test 3.1.3 {
          120  +  SELECT sql FROM sqlite_master WHERE name='vvv';
          121  +} {{CREATE VIEW vvv AS SELECT main.txx.a, txx.b, c FROM txx}}
          122  +
          123  +
          124  +do_catchsql_test 3.2.1 {
          125  +  SELECT * FROM uuu;
          126  +} {1 {no such table: main.txx}}
          127  +do_execsql_test 3.2.2 {
          128  +  SELECT sql FROM sqlite_master WHERE name='uuu';;
          129  +} {{CREATE VIEW uuu AS SELECT main.one.a, one.b, c FROM txx AS one}}
          130  +
          131  +do_catchsql_test 3.3.1 {
          132  +  SELECT * FROM ttt;
          133  +} {1 {no such table: txx}}
          134  +do_execsql_test 3.3.2 {
          135  +  SELECT sql FROM sqlite_temp_master WHERE name='ttt';
          136  +} {{CREATE VIEW ttt AS SELECT main.txx.a, txx.b, one.b, main.one.a FROM txx AS one, txx}}
          137  +
          138  +#-------------------------------------------------------------------------
          139  +reset_db
          140  +do_execsql_test 4.0 {
          141  +  PRAGMA legacy_alter_table = 1;
          142  +  CREATE table t1(x, y);
          143  +  CREATE table t2(a, b);
          144  +
          145  +  CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
          146  +    SELECT t1.x, * FROM t1, t2;
          147  +    INSERT INTO t2 VALUES(new.x, new.y);
          148  +  END;
          149  +}
          150  +
          151  +do_execsql_test 4.1 {
          152  +  INSERT INTO t1 VALUES(1, 1);
          153  +  ALTER TABLE t1 RENAME TO t11;
          154  +} 
          155  +do_catchsql_test 4.1a {
          156  +  INSERT INTO t11 VALUES(2, 2);
          157  +} {1 {no such table: main.t1}}
          158  +do_execsql_test 4.1b {
          159  +  ALTER TABLE t11 RENAME TO t1;
          160  +  ALTER TABLE t2 RENAME TO t22;
          161  +}
          162  +do_catchsql_test 4.1c {
          163  +  INSERT INTO t1 VALUES(3, 3);
          164  +} {1 {no such table: main.t2}}
          165  +
          166  +proc squish {a} {
          167  +  string trim [regsub -all {[[:space:]][[:space:]]*} $a { }]
          168  +}
          169  +db func squish squish
          170  +do_test 4.2 {
          171  +  execsql { SELECT squish(sql) FROM sqlite_master WHERE name = 'tr1' }
          172  +} [list [squish {
          173  +  CREATE TRIGGER tr1 AFTER INSERT ON "t1" BEGIN
          174  +    SELECT t1.x, * FROM t1, t2;
          175  +    INSERT INTO t2 VALUES(new.x, new.y);
          176  +  END
          177  +}]]
          178  +
          179  +#-------------------------------------------------------------------------
          180  +reset_db
          181  +do_execsql_test 5.0 {
          182  +  PRAGMA legacy_alter_table = 1;
          183  +  CREATE TABLE t9(a, b, c);
          184  +  CREATE TABLE t10(a, b, c);
          185  +  CREATE TEMP TABLE t9(a, b, c);
          186  +
          187  +  CREATE TRIGGER temp.t9t AFTER INSERT ON temp.t9 BEGIN
          188  +    INSERT INTO t10 VALUES(new.a, new.b, new.c);
          189  +  END;
          190  +
          191  +  INSERT INTO temp.t9 VALUES(1, 2, 3);
          192  +  SELECT * FROM t10;
          193  +} {1 2 3}
          194  +
          195  +do_execsql_test 5.1 {
          196  +  ALTER TABLE temp.t9 RENAME TO 't1234567890'
          197  +}
          198  +
          199  +do_execsql_test 5.2 {
          200  +  CREATE TABLE t1(a, b);
          201  +  CREATE TABLE t2(a, b);
          202  +  INSERT INTO t1 VALUES(1, 2);
          203  +  INSERT INTO t2 VALUES(3, 4);
          204  +  CREATE VIEW v AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2;
          205  +  SELECT * FROM v;
          206  +} {1 2 3 4}
          207  +
          208  +do_execsql_test 5.3 {
          209  +  ALTER TABLE t2 RENAME TO one;
          210  +} {}
          211  +
          212  +do_catchsql_test 5.4 {
          213  +  SELECT  *  FROM v
          214  +} {1 {no such table: main.t2}}
          215  +
          216  +do_execsql_test 5.5 {
          217  +  ALTER TABLE one RENAME TO t2;
          218  +  DROP VIEW v;
          219  +  CREATE VIEW temp.vv AS SELECT one.a, one.b, t2.a, t2.b FROM t1 AS one, t2;
          220  +  SELECT * FROM vv;
          221  +} {1 2 3 4}
          222  +
          223  +do_execsql_test 5.6 {
          224  +  ALTER TABLE t2 RENAME TO one;
          225  +} {}
          226  +do_catchsql_test 5.7 {
          227  +  SELECT  *  FROM vv
          228  +} {1 {no such table: t2}}
          229  +
          230  +#-------------------------------------------------------------------------
          231  +
          232  +ifcapable vtab {
          233  +  register_tcl_module db
          234  +  proc tcl_command {method args} {
          235  +    switch -- $method {
          236  +      xConnect {
          237  +        return "CREATE TABLE t1(a, b, c)"
          238  +      }
          239  +    }
          240  +    return {}
          241  +  }
          242  +  
          243  +  do_execsql_test 6.0 {
          244  +    CREATE VIRTUAL TABLE x1 USING tcl(tcl_command);
          245  +  }
          246  +  
          247  +  do_execsql_test 6.1 {
          248  +    ALTER TABLE x1 RENAME TO x2;
          249  +    SELECT sql FROM sqlite_master WHERE name = 'x2'
          250  +  } {{CREATE VIRTUAL TABLE "x2" USING tcl(tcl_command)}}
          251  +  
          252  +  do_execsql_test 7.1 {
          253  +    CREATE TABLE ddd(db, sql, zOld, zNew, bTemp);
          254  +    INSERT INTO ddd VALUES(
          255  +        'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', 'ddd', NULL, 0
          256  +    ), (
          257  +        'main', 'CREATE TABLE x1(i INTEGER, t TEXT)', NULL, 'eee', 0
          258  +    ), (
          259  +        'main', NULL, 'ddd', 'eee', 0
          260  +    );
          261  +  } {}
          262  +}
          263  +
          264  +#-------------------------------------------------------------------------
          265  +#
          266  +reset_db
          267  +forcedelete test.db2
          268  +do_execsql_test 8.1 {
          269  +  PRAGMA legacy_alter_table = 1;
          270  +  ATTACH 'test.db2' AS aux;
          271  +  PRAGMA foreign_keys = on;
          272  +  CREATE TABLE aux.p1(a INTEGER PRIMARY KEY, b);
          273  +  CREATE TABLE aux.c1(x INTEGER PRIMARY KEY, y REFERENCES p1(a));
          274  +  INSERT INTO aux.p1 VALUES(1, 1);
          275  +  INSERT INTO aux.p1 VALUES(2, 2);
          276  +  INSERT INTO aux.c1 VALUES(NULL, 2);
          277  +  CREATE TABLE aux.c2(x INTEGER PRIMARY KEY, y REFERENCES c1(a));
          278  +}
          279  +
          280  +do_execsql_test 8.2 {
          281  +  ALTER TABLE aux.p1 RENAME TO ppp;
          282  +}
          283  +
          284  +do_execsql_test 8.2 {
          285  +  INSERT INTO aux.c1 VALUES(NULL, 1);
          286  +  SELECT sql FROM aux.sqlite_master WHERE name = 'c1';
          287  +} {{CREATE TABLE c1(x INTEGER PRIMARY KEY, y REFERENCES "ppp"(a))}}
          288  +
          289  +reset_db
          290  +do_execsql_test 9.0 {
          291  +  PRAGMA legacy_alter_table = 1;
          292  +  CREATE TABLE t1(a, b, c);
          293  +  CREATE VIEW v1 AS SELECT * FROM t2;
          294  +}
          295  +do_execsql_test 9.1 {
          296  +  ALTER TABLE t1 RENAME TO t3;
          297  +} {}
          298  +do_execsql_test 9.1b {
          299  +  ALTER TABLE t3 RENAME TO t1;
          300  +} {}
          301  +do_execsql_test 9.2 {
          302  +  DROP VIEW v1;
          303  +  CREATE TRIGGER tr AFTER INSERT ON t1 BEGIN
          304  +    INSERT INTO t2 VALUES(new.a);
          305  +  END;
          306  +}
          307  +do_execsql_test 9.3 {
          308  +  ALTER TABLE t1 RENAME TO t3;
          309  +} {}
          310  +
          311  +forcedelete test.db2
          312  +do_execsql_test 9.4 {
          313  +  ALTER TABLE t3 RENAME TO t1;
          314  +  DROP TRIGGER tr;
          315  +
          316  +  ATTACH 'test.db2' AS aux;
          317  +  CREATE TRIGGER tr AFTER INSERT ON t1 WHEN new.a IS NULL BEGIN SELECT 1, 2, 3; END;
          318  +
          319  +  CREATE TABLE aux.t1(x);
          320  +  CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END;
          321  +}
          322  +do_execsql_test 9.5 {
          323  +  ALTER TABLE main.t1 RENAME TO t3;
          324  +}
          325  +do_execsql_test 9.6 {
          326  +  SELECT sql FROM sqlite_temp_master;
          327  +  SELECT sql FROM sqlite_master WHERE type='trigger';
          328  +} {
          329  +  {CREATE TRIGGER tr AFTER INSERT ON aux.t1 BEGIN SELECT 1, 2, 3; END}
          330  +  {CREATE TRIGGER tr AFTER INSERT ON "t3" WHEN new.a IS NULL BEGIN SELECT 1, 2, 3; END}
          331  +}
          332  +
          333  +#-------------------------------------------------------------------------
          334  +reset_db
          335  +ifcapable fts5 {
          336  +  do_execsql_test 10.0 {
          337  +    PRAGMA legacy_alter_table = 1;
          338  +    CREATE VIRTUAL TABLE fff USING fts5(x, y, z);
          339  +  }
          340  +
          341  +  do_execsql_test 10.1 {
          342  +    BEGIN;
          343  +      INSERT INTO fff VALUES('a', 'b', 'c');
          344  +      ALTER TABLE fff RENAME TO ggg;
          345  +    COMMIT;
          346  +  }
          347  +
          348  +  do_execsql_test 10.2 {
          349  +    SELECT * FROM ggg;
          350  +  } {a b c}
          351  +}
          352  +
          353  +#-------------------------------------------------------------------------
          354  +reset_db
          355  +forcedelete test.db2
          356  +db func trigger trigger
          357  +set ::trigger [list]
          358  +proc trigger {args} {
          359  +  lappend ::trigger $args
          360  +}
          361  +do_execsql_test 11.0 {
          362  +  PRAGMA legacy_alter_table = 1;
          363  +  ATTACH 'test.db2' AS aux;
          364  +  CREATE TABLE aux.t1(a, b, c);
          365  +  CREATE TABLE main.t1(a, b, c);
          366  +  CREATE TEMP TRIGGER tr AFTER INSERT ON aux.t1 BEGIN
          367  +    SELECT trigger(new.a, new.b, new.c);
          368  +  END;
          369  +}
          370  +
          371  +do_execsql_test 11.1 {
          372  +  INSERT INTO main.t1 VALUES(1, 2, 3);
          373  +  INSERT INTO aux.t1 VALUES(4, 5, 6);
          374  +}
          375  +do_test 11.2 { set ::trigger } {{4 5 6}}
          376  +
          377  +do_execsql_test 11.3 {
          378  +  SELECT name, tbl_name FROM sqlite_temp_master;
          379  +} {tr t1}
          380  +
          381  +do_execsql_test 11.4 {
          382  +  ALTER TABLE main.t1 RENAME TO t2;
          383  +  SELECT name, tbl_name FROM sqlite_temp_master;
          384  +} {tr t1}
          385  +
          386  +do_execsql_test 11.5 {
          387  +  ALTER TABLE aux.t1 RENAME TO t2;
          388  +  SELECT name, tbl_name FROM sqlite_temp_master;
          389  +} {tr t2}
          390  +
          391  +do_execsql_test 11.6 {
          392  +  INSERT INTO aux.t2 VALUES(7, 8, 9);
          393  +}
          394  +do_test 11.7 { set ::trigger } {{4 5 6} {7 8 9}}
          395  +
          396  +#-------------------------------------------------------------------------
          397  +reset_db
          398  +do_execsql_test 12.0 {
          399  +  PRAGMA legacy_alter_table = 1;
          400  +  CREATE TABLE t1(a);
          401  +  CREATE TABLE t2(w);
          402  +  CREATE TRIGGER temp.r1 AFTER INSERT ON main.t2 BEGIN
          403  +    INSERT INTO t1(a) VALUES(new.w);
          404  +  END;
          405  +  CREATE TEMP TABLE t2(x);
          406  +}
          407  +
          408  +do_execsql_test 12.1 {
          409  +  ALTER TABLE main.t2 RENAME TO t3;
          410  +}
          411  +
          412  +do_execsql_test 12.2 {
          413  +  INSERT INTO t3 VALUES('WWW');
          414  +  SELECT * FROM t1;
          415  +} {WWW}
          416  +
          417  +
          418  +#-------------------------------------------------------------------------
          419  +reset_db
          420  +
          421  +ifcapable rtree {
          422  +  do_execsql_test 14.0 {
          423  +    PRAGMA legacy_alter_table = 1;
          424  +    CREATE VIRTUAL TABLE rt USING rtree(id, minx, maxx, miny, maxy);
          425  +
          426  +    CREATE TABLE "mytable" ( "fid" INTEGER PRIMARY KEY, "geom" BLOB);
          427  +
          428  +    CREATE TRIGGER tr1 AFTER UPDATE OF "geom" ON "mytable" 
          429  +          WHEN OLD."fid" = NEW."fid" AND NEW."geom" IS NULL BEGIN 
          430  +      DELETE FROM rt WHERE id = OLD."fid"; 
          431  +    END;
          432  +
          433  +    INSERT INTO mytable VALUES(1, X'abcd');
          434  +  }
          435  +
          436  +  do_execsql_test 14.1 {
          437  +    UPDATE mytable SET geom = X'1234'
          438  +  }
          439  +
          440  +  do_execsql_test 14.2 {
          441  +    ALTER TABLE mytable RENAME TO mytable_renamed;
          442  +  }
          443  +
          444  +  do_execsql_test 14.3 {
          445  +    CREATE TRIGGER tr2 AFTER INSERT ON mytable_renamed BEGIN
          446  +      DELETE FROM rt WHERE id=(SELECT min(id) FROM rt);
          447  +    END;
          448  +  }
          449  +
          450  +  do_execsql_test 14.4 {
          451  +    ALTER TABLE mytable_renamed RENAME TO mytable2;
          452  +  }
          453  +}
          454  +
          455  +reset_db
          456  +do_execsql_test 14.5 {
          457  +  PRAGMA legacy_alter_table = 1;
          458  +  CREATE TABLE t1(a, b, c);
          459  +  CREATE VIEW v1 AS SELECT * FROM t1;
          460  +  CREATE TRIGGER xyz AFTER INSERT ON t1 BEGIN
          461  +    SELECT a, b FROM v1;
          462  +  END;
          463  +}
          464  +do_execsql_test 14.6 {
          465  +  ALTER TABLE t1 RENAME TO tt1;
          466  +}
          467  +
          468  +
          469  +finish_test
          470  +

Changes to test/altermalloc2.test.

    68     68   } -body {
    69     69     execsql {
    70     70       ALTER TABLE t1 RENAME abcd TO dcba
    71     71     }
    72     72   } -test {
    73     73     faultsim_test_result {0 {}}
    74     74   }
           75  +
           76  +reset_db
           77  +do_execsql_test 4.0 {
           78  +  CREATE TABLE rr(a, b);
           79  +  CREATE VIEW vv AS SELECT * FROM rr;
           80  +
           81  +  CREATE TRIGGER vv1 INSTEAD OF INSERT ON vv BEGIN
           82  +    SELECT 1, 2, 3;
           83  +  END;
           84  +  CREATE TRIGGER tr1 AFTER INSERT ON rr BEGIN
           85  +    INSERT INTO vv VALUES(new.a, new.b);
           86  +  END;
           87  +} {}
           88  +
           89  +faultsim_save_and_close
           90  +do_faultsim_test 4 -faults oom-* -prep {
           91  +  faultsim_restore_and_reopen
           92  +  execsql { SELECT * FROM sqlite_master }
           93  +} -body {
           94  +  execsql {
           95  +    ALTER TABLE rr RENAME a TO c;
           96  +  }
           97  +} -test {
           98  +  faultsim_test_result {0 {}} 
           99  +}
          100  +
    75    101   finish_test

Added test/altertab2.test.

            1  +# 2018 September 30
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set testprefix altertab
           16  +
           17  +# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
           18  +ifcapable !altertable {
           19  +  finish_test
           20  +  return
           21  +}
           22  +
           23  +ifcapable fts5 {
           24  +  do_execsql_test 1.0 {
           25  +    CREATE TABLE rr(a, b);
           26  +    CREATE VIRTUAL TABLE ff USING fts5(a, b);
           27  +    CREATE TRIGGER tr1 AFTER INSERT ON rr BEGIN
           28  +      INSERT INTO ff VALUES(new.a, new.b);
           29  +    END;
           30  +    INSERT INTO rr VALUES('hello', 'world');
           31  +    SELECT * FROM ff;
           32  +  } {hello world}
           33  +
           34  +  do_execsql_test 1.1 {
           35  +    ALTER TABLE ff RENAME TO ffff;
           36  +  }
           37  +
           38  +  do_execsql_test 1.2 {
           39  +    INSERT INTO rr VALUES('in', 'tcl');
           40  +    SELECT * FROM ffff;
           41  +  } {hello world in tcl}
           42  +}
           43  +
           44  +
           45  +finish_test
           46  +

Changes to tool/mkpragmatab.tcl.

   378    378     FLAG: Result0
   379    379   
   380    380     NAME: threads
   381    381     FLAG: Result0
   382    382   
   383    383     NAME: optimize
   384    384     FLAG: Result1 NeedSchema
          385  +
          386  +  NAME: legacy_alter_table
          387  +  TYPE: FLAG
          388  +  ARG:  SQLITE_LegacyAlter
          389  +  IF:   !defined(SQLITE_OMIT_FLAG_PRAGMAS)
   385    390   }
   386    391   
   387    392   # Open the output file
   388    393   #
   389    394   set destfile "[file dir [file dir [file normal $argv0]]]/src/pragma.h"
   390    395   puts "Overwriting $destfile with new pragma table..."
   391    396   set fd [open $destfile wb]