/ Check-in [1fd8e835]
Login

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

Overview
Comment:Fix bugs in ALTER TABLE related to (a) whitespace in table defn, (b) temp triggers. (CVS 2112)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1fd8e835a3656799c23f4ef6ea1311fecf5a15cb
User & Date: danielk1977 2004-11-19 05:14:55
Context
2004-11-19
07:07
When dropping a table, update the sqlite_sequence table first, as auto-vacuum mode may need to move sqlite_sequence when the btree table is dropped. (CVS 2113) check-in: 0514107b user: danielk1977 tags: trunk
05:14
Fix bugs in ALTER TABLE related to (a) whitespace in table defn, (b) temp triggers. (CVS 2112) check-in: 1fd8e835 user: danielk1977 tags: trunk
2004-11-18
15:44
Handle triggers correctly in ALTER TABLE. (CVS 2111) check-in: c61b7de1 user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

    18     18   **     CREATE INDEX
    19     19   **     DROP INDEX
    20     20   **     creating ID lists
    21     21   **     BEGIN TRANSACTION
    22     22   **     COMMIT
    23     23   **     ROLLBACK
    24     24   **
    25         -** $Id: build.c,v 1.280 2004/11/18 15:44:29 danielk1977 Exp $
           25  +** $Id: build.c,v 1.281 2004/11/19 05:14:55 danielk1977 Exp $
    26     26   */
    27     27   #include "sqliteInt.h"
    28     28   #include <ctype.h>
    29     29   
    30     30   /*
    31     31   ** This routine is called when a new SQL statement is beginning to
    32     32   ** be parsed.  Check to see if the schema for the database needs
................................................................................
  2927   2927     int iDb;                  /* Database that contains the table */
  2928   2928     char *zDb;                /* Name of database iDb */
  2929   2929     Table *pTab;              /* Table being renamed */
  2930   2930     char *zName = 0;          /* NULL-terminated version of pName */ 
  2931   2931     char *zWhere = 0;         /* Where clause of schema elements to reparse */
  2932   2932     sqlite3 *db = pParse->db; /* Database connection */
  2933   2933     Vdbe *v;
         2934  +#ifndef SQLITE_OMIT_TRIGGER
         2935  +  char *zTempTrig = 0;      /* Where clause to locate temp triggers */
         2936  +#endif
  2934   2937     
  2935   2938     assert( pSrc->nSrc==1 );
  2936   2939   
  2937   2940     pTab = sqlite3LocateTable(pParse, pSrc->a[0].zName, pSrc->a[0].zDatabase);
  2938   2941     if( !pTab ) return;
  2939   2942     iDb = pTab->iDb;
  2940   2943     zDb = db->aDb[iDb].zName;
................................................................................
  2989   2992               "WHEN name LIKE 'sqlite_autoindex%%' AND type='index' THEN "
  2990   2993                 "'sqlite_autoindex_' || %Q || substr(name, %d+18,10) "
  2991   2994               "ELSE name END "
  2992   2995         "WHERE tbl_name=%Q AND type IN ('table', 'index', 'trigger');", 
  2993   2996         db->aDb[iDb].zName, SCHEMA_TABLE(iDb), zName, zName, zName, zName, 
  2994   2997         zName, strlen(pTab->zName), pTab->zName
  2995   2998     );
         2999  +
         3000  +#ifndef SQLITE_OMIT_TRIGGER
         3001  +  /* If there are TEMP triggers on this table, modify the sqlite_temp_master
         3002  +  ** table. Don't do this if the table being ALTERed is itself located in
         3003  +  ** the temp database.
         3004  +  */
         3005  +  if( iDb!=1 ){
         3006  +    Trigger *pTrig;
         3007  +    char *tmp = 0;
         3008  +    for( pTrig=pTab->pTrigger; pTrig; pTrig=pTrig->pNext ){
         3009  +      if( pTrig->iDb==1 ){
         3010  +        if( !zTempTrig ){
         3011  +          zTempTrig = 
         3012  +              sqlite3MPrintf("type = 'trigger' AND name IN(%Q", pTrig->name);
         3013  +        }else{
         3014  +          tmp = zTempTrig;
         3015  +          zTempTrig = sqlite3MPrintf("%s, %Q", zTempTrig, pTrig->name);
         3016  +          sqliteFree(tmp);
         3017  +        }
         3018  +      }
         3019  +    }
         3020  +    if( zTempTrig ){
         3021  +      tmp = zTempTrig;
         3022  +      zTempTrig = sqlite3MPrintf("%s)", zTempTrig);
         3023  +      sqliteFree(tmp);
         3024  +      sqlite3NestedParse(pParse, 
         3025  +          "UPDATE sqlite_temp_master SET "
         3026  +              "sql = sqlite_alter_trigger(sql, %Q), "
         3027  +              "tbl_name = %Q "
         3028  +              "WHERE %s;", zName, zName, zTempTrig);
         3029  +    }
         3030  +  }
         3031  +#endif
  2996   3032   
  2997   3033     /* Drop the elements of the in-memory schema that refered to the table
  2998   3034     ** renamed and load the new versions from the database.
  2999   3035     */
  3000   3036     if( pParse->nErr==0 ){
  3001   3037   #ifndef SQLITE_OMIT_TRIGGER
  3002   3038       Trigger *pTrig;
  3003   3039       for( pTrig=pTab->pTrigger; pTrig; pTrig=pTrig->pNext ){
  3004         -      sqlite3VdbeOp3(v, OP_DropTrigger, iDb, 0, pTrig->name, 0);
         3040  +      assert( pTrig->iDb==iDb || pTrig->iDb==1 );
         3041  +      sqlite3VdbeOp3(v, OP_DropTrigger, pTrig->iDb, 0, pTrig->name, 0);
  3005   3042       }
  3006   3043   #endif
  3007   3044       sqlite3VdbeOp3(v, OP_DropTable, iDb, 0, pTab->zName, 0);
  3008   3045       zWhere = sqlite3MPrintf("tbl_name=%Q", zName);
  3009   3046       sqlite3VdbeOp3(v, OP_ParseSchema, iDb, 0, zWhere, P3_DYNAMIC);
         3047  +#ifndef SQLITE_OMIT_TRIGGER
         3048  +    if( zTempTrig ){
         3049  +      sqlite3VdbeOp3(v, OP_ParseSchema, 1, 0, zTempTrig, P3_DYNAMIC);
         3050  +    }
         3051  +  }else{
         3052  +    sqliteFree(zTempTrig);
         3053  +#endif
  3010   3054     }
  3011   3055   
  3012   3056     sqliteFree(zName);
  3013   3057   }
  3014   3058   #endif

Changes to src/func.c.

    12     12   ** This file contains the C functions that implement various SQL
    13     13   ** functions of SQLite.  
    14     14   **
    15     15   ** There is only one exported symbol in this file - the function
    16     16   ** sqliteRegisterBuildinFunctions() found at the bottom of the file.
    17     17   ** All other code has file scope.
    18     18   **
    19         -** $Id: func.c,v 1.90 2004/11/18 15:44:29 danielk1977 Exp $
           19  +** $Id: func.c,v 1.91 2004/11/19 05:14:55 danielk1977 Exp $
    20     20   */
    21     21   #include <ctype.h>
    22     22   #include <math.h>
    23     23   #include <stdlib.h>
    24     24   #include <assert.h>
    25     25   #include "sqliteInt.h"
    26     26   #include "vdbeInt.h"
................................................................................
   548    548   **     -> 'CREATE INDEX i ON def(a, b, c)'
   549    549   */
   550    550   static void altertableFunc(
   551    551     sqlite3_context *context,
   552    552     int argc,
   553    553     sqlite3_value **argv
   554    554   ){
   555         -  char const *zSql = sqlite3_value_text(argv[0]);
   556         -  char const *zTableName = sqlite3_value_text(argv[1]);
          555  +  unsigned char const *zSql = sqlite3_value_text(argv[0]);
          556  +  unsigned char const *zTableName = sqlite3_value_text(argv[1]);
   557    557   
          558  +  int token;
          559  +  Token tname;
   558    560     char const *zCsr = zSql;
   559         -  char const *zPrev;
   560         -  char *zRet = 0;
   561         -  int tokenType = 0;
   562         -  int len;
          561  +  int len = 0;
          562  +  char *zRet;
   563    563   
   564         -  assert( argc==2 );
          564  +  /* The principle used to locate the table name in the CREATE TABLE 
          565  +  ** statement is that the table name is the first token that is immediatedly
          566  +  ** followed by a left parenthesis - TK_LP.
          567  +  */
   565    568     if( zSql ){
   566         -    while( tokenType!=TK_LP ){
   567         -      zPrev = zCsr-len;
   568         -      len = sqlite3GetToken(zCsr, &tokenType);
   569         -      zCsr += len;
   570         -    }
          569  +    do {
          570  +      /* Store the token that zCsr points to in tname. */
          571  +      tname.z = zCsr;
          572  +      tname.n = len;
   571    573   
   572         -    zRet = sqlite3MPrintf("%.*s%Q(%s", zPrev-zSql, zSql, zTableName, zCsr);
   573         -    sqlite3_result_text(context, zRet, -1, SQLITE_TRANSIENT);
   574         -    sqliteFree(zRet);
          574  +      /* Advance zCsr to the next token. Store that token type in 'token',
          575  +      ** and it's length in 'len' (to be used next iteration of this loop).
          576  +      */
          577  +      do {
          578  +        zCsr += len;
          579  +        len = sqlite3GetToken(zCsr, &token);
          580  +      } while( token==TK_SPACE );
          581  +      assert( len>0 );
          582  +    } while( token!=TK_LP );
          583  +
          584  +    zRet = sqlite3MPrintf("%.*s%Q%s", tname.z - zSql, zSql, 
          585  +       zTableName, tname.z+tname.n);
          586  +    sqlite3_result_text(context, zRet, -1, sqlite3FreeX);
   575    587     }
   576    588   }
   577    589   #endif
   578    590   
   579    591   #ifndef SQLITE_OMIT_ALTERTABLE
   580    592   #ifndef SQLITE_OMIT_TRIGGER
   581    593   /* This function is used by SQL generated to implement the ALTER TABLE
................................................................................
   601    613     char *zRet;
   602    614   
   603    615     /* The principle used to locate the table name in the CREATE TRIGGER 
   604    616     ** statement is that the table name is the first token that is immediatedly
   605    617     ** preceded by either TK_ON or TK_DOT and immediatedly followed by one
   606    618     ** of TK_WHEN, TK_BEGIN or TK_FOR.
   607    619     */
   608         -  assert( argc==2 );
   609    620     if( zSql ){
   610    621       do {
   611    622         /* Store the token that zCsr points to in tname. */
   612    623         tname.z = zCsr;
   613    624         tname.n = len;
   614    625   
   615    626         /* Advance zCsr to the next token. Store that token type in 'token',
................................................................................
   637    648       } while( dist!=2 || (token!=TK_WHEN && token!=TK_FOR && token!=TK_BEGIN) );
   638    649   
   639    650       /* Variable tname now contains the token that is the old table-name
   640    651       ** in the CREATE TRIGGER statement.
   641    652       */
   642    653       zRet = sqlite3MPrintf("%.*s%Q%s", tname.z - zSql, zSql, 
   643    654          zTableName, tname.z+tname.n);
   644         -    sqlite3_result_text(context, zRet, -1, SQLITE_TRANSIENT);
   645         -    sqliteFree(zRet);
          655  +    sqlite3_result_text(context, zRet, -1, sqlite3FreeX);
   646    656     }
   647    657   }
   648    658   #endif   /* !SQLITE_OMIT_TRIGGER */
   649    659   #endif   /* !SQLITE_OMIT_ALTERTABLE */
   650    660   
   651    661   /*
   652    662   ** EXPERIMENTAL - This is not an official function.  The interface may

Changes to test/alter.test.

     4      4   # intellectual property to make, have made, use, have used, reproduce,
     5      5   # prepare derivative works, distribute, perform and display the work.  
     6      6   #
     7      7   #*************************************************************************
     8      8   # This file implements regression tests for SQLite library.  The
     9      9   # focus of this script is testing the ALTER TABLE statement.
    10     10   #
    11         -# $Id: alter.test,v 1.3 2004/11/18 15:44:30 danielk1977 Exp $
           11  +# $Id: alter.test,v 1.4 2004/11/19 05:14:56 danielk1977 Exp $
    12     12   #
    13     13   
    14     14   set testdir [file dirname $argv0]
    15     15   source $testdir/tester.tcl
    16     16   
    17     17   # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
    18     18   ifcapable !altertable {
    19     19     finish_test
    20     20     return
    21     21   }
           22  +
           23  +#----------------------------------------------------------------------
           24  +# Test organization:
           25  +#
           26  +# alter-1.1.* - alter-1.7.*: Basic tests of ALTER TABLE, including tables
           27  +#     with implicit and explicit indices. These tests came from an earlier
           28  +#     fork of SQLite that also supported ALTER TABLE.
           29  +# alter-1.8.*: Tests for ALTER TABLE when the table resides in an 
           30  +#     attached database.
           31  +# alter-1.9.*: Tests for ALTER TABLE when their is whitespace between the
           32  +#     table name and left parenthesis token. i.e: 
           33  +#     "CREATE TABLE abc       (a, b, c);"
           34  +# alter-2.*: Test error conditions and messages.
           35  +# alter-3.*: Test ALTER TABLE on tables that have TRIGGERs attached to them.
           36  +#
    22     37   
    23     38   # Create some tables to rename.  Be sure to include some TEMP tables
    24     39   # and some tables with odd names.
    25     40   #
    26     41   do_test alter-1.1 {
    27     42     execsql {
    28     43       CREATE TABLE t1(a,b);
................................................................................
   191    206   } {main main main}
   192    207   do_test alter-1.8.7 {
   193    208     execsql {
   194    209       ALTER TABLE aux.t4 RENAME TO t5;
   195    210       SELECT * FROM aux.t5 WHERE b = 'aux';
   196    211     }
   197    212   } {aux aux aux}
          213  +
          214  +do_test alter-1.9.1 {
          215  +  execsql {
          216  +    CREATE TABLE tbl1   (a, b, c);
          217  +    INSERT INTO tbl1 VALUES(1, 2, 3);
          218  +  }
          219  +} {}
          220  +do_test alter-1.9.2 {
          221  +  execsql {
          222  +    SELECT * FROM tbl1;
          223  +  }
          224  +} {1 2 3}
          225  +do_test alter-1.9.3 {
          226  +  execsql {
          227  +    ALTER TABLE tbl1 RENAME TO tbl2;
          228  +    SELECT * FROM tbl2;
          229  +  }
          230  +} {1 2 3}
          231  +do_test alter-1.9.4 {
          232  +  execsql {
          233  +    DROP TABLE tbl2;
          234  +  }
          235  +} {}
   198    236   
   199    237   # Test error messages
   200    238   #
   201    239   do_test alter-2.1 {
   202    240     catchsql {
   203    241       ALTER TABLE none RENAME TO hi;
   204    242     }
................................................................................
   219    257   
   220    258   # If this compilation does not include triggers, omit the remainder
   221    259   # of this file.
   222    260   ifcapable !trigger {
   223    261     finish_test
   224    262     return
   225    263   }
          264  +
          265  +#-----------------------------------------------------------------------
          266  +# Tests alter-3.* test ALTER TABLE on tables that have triggers.
          267  +#
          268  +# alter-3.1.*: ALTER TABLE with triggers.
          269  +# alter-3.2.*: Test that the ON keyword cannot be used as a database,
          270  +#     table or column name unquoted. This is done because part of the
          271  +#     ALTER TABLE code (specifically the implementation of SQL function
          272  +#     "sqlite_alter_trigger") will break in this case.
          273  +# alter-3.3.*: ALTER TABLE with TEMP triggers (todo).
          274  +#
   226    275   
   227    276   # An SQL user-function for triggers to fire, so that we know they
   228    277   # are working.
   229    278   proc trigfunc {args} {
   230    279     set ::TRIGGER $args
   231    280   }
   232    281   db func trigfunc trigfunc
................................................................................
   345    394     }
   346    395   } {1 {near "ON": syntax error}}
   347    396   do_test alter-3.2.9 {
   348    397     catchsql {
   349    398       CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;
   350    399     }
   351    400   } {0 {}}
   352         -finish_test
          401  +do_test alter-3.2.10 {
          402  +  execsql {
          403  +    DROP TABLE t10;
          404  +  }
          405  +} {}
          406  +
          407  +do_test alter-3.3.1 {
          408  +  execsql {
          409  +    CREATE TABLE tbl1(a, b, c);
          410  +    CREATE TEMP TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN
          411  +      SELECT trigfunc('trig1', new.a, new.b, new.c);
          412  +    END;
          413  +  }
          414  +} {}
          415  +do_test alter-3.3.2 {
          416  +  execsql {
          417  +    INSERT INTO tbl1 VALUES('a', 'b', 'c');
          418  +  }
          419  +  set ::TRIGGER
          420  +} {trig1 a b c}
          421  +do_test alter-3.3.3 {
          422  +  execsql {
          423  +    ALTER TABLE tbl1 RENAME TO tbl2;
          424  +    INSERT INTO tbl2 VALUES('d', 'e', 'f');
          425  +  } 
          426  +  set ::TRIGGER
          427  +} {trig1 d e f}
          428  +do_test alter-3.3.4 {
          429  +  execsql {
          430  +    CREATE TEMP TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN
          431  +      SELECT trigfunc('trig2', new.a, new.b, new.c);
          432  +    END;
          433  +  } 
          434  +} {}
          435  +do_test alter-3.3.5 {
          436  +  execsql {
          437  +    ALTER TABLE tbl2 RENAME TO tbl3;
          438  +    INSERT INTO tbl3 VALUES('g', 'h', 'i');
          439  +  } 
          440  +  set ::TRIGGER
          441  +} {trig1 g h i}
          442  +do_test alter-3.3.6 {
          443  +  execsql {
          444  +    UPDATE tbl3 SET a = 'G' where a = 'g';
          445  +  } 
          446  +  set ::TRIGGER
          447  +} {trig2 G h i}
          448  +do_test alter-3.3.7 {
          449  +  execsql {
          450  +    DROP TABLE tbl3;
          451  +    SELECT * FROM sqlite_temp_master WHERE type = 'trigger';
          452  +  }
          453  +} {}
   353    454   
          455  +finish_test