SQLite

Check-in [1fd8e835a3]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1fd8e835a3656799c23f4ef6ea1311fecf5a15cb
User & Date: danielk1977 2004-11-19 05:14:55.000
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: 0514107bff 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: 1fd8e835a3 user: danielk1977 tags: trunk)
2004-11-18
15:44
Handle triggers correctly in ALTER TABLE. (CVS 2111) (check-in: c61b7de107 user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.280 2004/11/18 15:44:29 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs







|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.281 2004/11/19 05:14:55 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
2927
2928
2929
2930
2931
2932
2933



2934
2935
2936
2937
2938
2939
2940
  int iDb;                  /* Database that contains the table */
  char *zDb;                /* Name of database iDb */
  Table *pTab;              /* Table being renamed */
  char *zName = 0;          /* NULL-terminated version of pName */ 
  char *zWhere = 0;         /* Where clause of schema elements to reparse */
  sqlite3 *db = pParse->db; /* Database connection */
  Vdbe *v;



  
  assert( pSrc->nSrc==1 );

  pTab = sqlite3LocateTable(pParse, pSrc->a[0].zName, pSrc->a[0].zDatabase);
  if( !pTab ) return;
  iDb = pTab->iDb;
  zDb = db->aDb[iDb].zName;







>
>
>







2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
  int iDb;                  /* Database that contains the table */
  char *zDb;                /* Name of database iDb */
  Table *pTab;              /* Table being renamed */
  char *zName = 0;          /* NULL-terminated version of pName */ 
  char *zWhere = 0;         /* Where clause of schema elements to reparse */
  sqlite3 *db = pParse->db; /* Database connection */
  Vdbe *v;
#ifndef SQLITE_OMIT_TRIGGER
  char *zTempTrig = 0;      /* Where clause to locate temp triggers */
#endif
  
  assert( pSrc->nSrc==1 );

  pTab = sqlite3LocateTable(pParse, pSrc->a[0].zName, pSrc->a[0].zDatabase);
  if( !pTab ) return;
  iDb = pTab->iDb;
  zDb = db->aDb[iDb].zName;
2989
2990
2991
2992
2993
2994
2995

































2996
2997
2998
2999
3000
3001
3002
3003

3004
3005
3006
3007
3008
3009







3010
3011
3012
3013
3014
            "WHEN name LIKE 'sqlite_autoindex%%' AND type='index' THEN "
              "'sqlite_autoindex_' || %Q || substr(name, %d+18,10) "
            "ELSE name END "
      "WHERE tbl_name=%Q AND type IN ('table', 'index', 'trigger');", 
      db->aDb[iDb].zName, SCHEMA_TABLE(iDb), zName, zName, zName, zName, 
      zName, strlen(pTab->zName), pTab->zName
  );


































  /* Drop the elements of the in-memory schema that refered to the table
  ** renamed and load the new versions from the database.
  */
  if( pParse->nErr==0 ){
#ifndef SQLITE_OMIT_TRIGGER
    Trigger *pTrig;
    for( pTrig=pTab->pTrigger; pTrig; pTrig=pTrig->pNext ){

      sqlite3VdbeOp3(v, OP_DropTrigger, iDb, 0, pTrig->name, 0);
    }
#endif
    sqlite3VdbeOp3(v, OP_DropTable, iDb, 0, pTab->zName, 0);
    zWhere = sqlite3MPrintf("tbl_name=%Q", zName);
    sqlite3VdbeOp3(v, OP_ParseSchema, iDb, 0, zWhere, P3_DYNAMIC);







  }

  sqliteFree(zName);
}
#endif







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>








>
|





>
>
>
>
>
>
>





2992
2993
2994
2995
2996
2997
2998
2999
3000
3001
3002
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
3056
3057
3058
            "WHEN name LIKE 'sqlite_autoindex%%' AND type='index' THEN "
              "'sqlite_autoindex_' || %Q || substr(name, %d+18,10) "
            "ELSE name END "
      "WHERE tbl_name=%Q AND type IN ('table', 'index', 'trigger');", 
      db->aDb[iDb].zName, SCHEMA_TABLE(iDb), zName, zName, zName, zName, 
      zName, strlen(pTab->zName), pTab->zName
  );

#ifndef SQLITE_OMIT_TRIGGER
  /* If there are TEMP triggers on this table, modify the sqlite_temp_master
  ** table. Don't do this if the table being ALTERed is itself located in
  ** the temp database.
  */
  if( iDb!=1 ){
    Trigger *pTrig;
    char *tmp = 0;
    for( pTrig=pTab->pTrigger; pTrig; pTrig=pTrig->pNext ){
      if( pTrig->iDb==1 ){
        if( !zTempTrig ){
          zTempTrig = 
              sqlite3MPrintf("type = 'trigger' AND name IN(%Q", pTrig->name);
        }else{
          tmp = zTempTrig;
          zTempTrig = sqlite3MPrintf("%s, %Q", zTempTrig, pTrig->name);
          sqliteFree(tmp);
        }
      }
    }
    if( zTempTrig ){
      tmp = zTempTrig;
      zTempTrig = sqlite3MPrintf("%s)", zTempTrig);
      sqliteFree(tmp);
      sqlite3NestedParse(pParse, 
          "UPDATE sqlite_temp_master SET "
              "sql = sqlite_alter_trigger(sql, %Q), "
              "tbl_name = %Q "
              "WHERE %s;", zName, zName, zTempTrig);
    }
  }
#endif

  /* Drop the elements of the in-memory schema that refered to the table
  ** renamed and load the new versions from the database.
  */
  if( pParse->nErr==0 ){
#ifndef SQLITE_OMIT_TRIGGER
    Trigger *pTrig;
    for( pTrig=pTab->pTrigger; pTrig; pTrig=pTrig->pNext ){
      assert( pTrig->iDb==iDb || pTrig->iDb==1 );
      sqlite3VdbeOp3(v, OP_DropTrigger, pTrig->iDb, 0, pTrig->name, 0);
    }
#endif
    sqlite3VdbeOp3(v, OP_DropTable, iDb, 0, pTab->zName, 0);
    zWhere = sqlite3MPrintf("tbl_name=%Q", zName);
    sqlite3VdbeOp3(v, OP_ParseSchema, iDb, 0, zWhere, P3_DYNAMIC);
#ifndef SQLITE_OMIT_TRIGGER
    if( zTempTrig ){
      sqlite3VdbeOp3(v, OP_ParseSchema, 1, 0, zTempTrig, P3_DYNAMIC);
    }
  }else{
    sqliteFree(zTempTrig);
#endif
  }

  sqliteFree(zName);
}
#endif
Changes to src/func.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.90 2004/11/18 15:44:29 danielk1977 Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "vdbeInt.h"







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.91 2004/11/19 05:14:55 danielk1977 Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "vdbeInt.h"
548
549
550
551
552
553
554
555
556
557


558
559
560
561
562
563
564



565

566
567
568
569
570









571
572

573
574
575
576
577
578
579
580
581
**     -> 'CREATE INDEX i ON def(a, b, c)'
*/
static void altertableFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  char const *zSql = sqlite3_value_text(argv[0]);
  char const *zTableName = sqlite3_value_text(argv[1]);



  char const *zCsr = zSql;
  char const *zPrev;
  char *zRet = 0;
  int tokenType = 0;
  int len;

  assert( argc==2 );



  if( zSql ){

    while( tokenType!=TK_LP ){
      zPrev = zCsr-len;
      len = sqlite3GetToken(zCsr, &tokenType);
      zCsr += len;
    }










    zRet = sqlite3MPrintf("%.*s%Q(%s", zPrev-zSql, zSql, zTableName, zCsr);

    sqlite3_result_text(context, zRet, -1, SQLITE_TRANSIENT);
    sqliteFree(zRet);
  }
}
#endif

#ifndef SQLITE_OMIT_ALTERTABLE
#ifndef SQLITE_OMIT_TRIGGER
/* This function is used by SQL generated to implement the ALTER TABLE







|
|

>
>

|
|
<
<

|
>
>
>

>
|
|
<
|
|
>
>
>
>
>
>
>
>
>

|
>
|
<







548
549
550
551
552
553
554
555
556
557
558
559
560
561
562


563
564
565
566
567
568
569
570
571

572
573
574
575
576
577
578
579
580
581
582
583
584
585
586

587
588
589
590
591
592
593
**     -> 'CREATE INDEX i ON def(a, b, c)'
*/
static void altertableFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  unsigned char const *zSql = sqlite3_value_text(argv[0]);
  unsigned char const *zTableName = sqlite3_value_text(argv[1]);

  int token;
  Token tname;
  char const *zCsr = zSql;
  int len = 0;
  char *zRet;



  /* The principle used to locate the table name in the CREATE TABLE 
  ** statement is that the table name is the first token that is immediatedly
  ** followed by a left parenthesis - TK_LP.
  */
  if( zSql ){
    do {
      /* Store the token that zCsr points to in tname. */
      tname.z = zCsr;

      tname.n = len;

      /* Advance zCsr to the next token. Store that token type in 'token',
      ** and it's length in 'len' (to be used next iteration of this loop).
      */
      do {
        zCsr += len;
        len = sqlite3GetToken(zCsr, &token);
      } while( token==TK_SPACE );
      assert( len>0 );
    } while( token!=TK_LP );

    zRet = sqlite3MPrintf("%.*s%Q%s", tname.z - zSql, zSql, 
       zTableName, tname.z+tname.n);
    sqlite3_result_text(context, zRet, -1, sqlite3FreeX);

  }
}
#endif

#ifndef SQLITE_OMIT_ALTERTABLE
#ifndef SQLITE_OMIT_TRIGGER
/* This function is used by SQL generated to implement the ALTER TABLE
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
  char *zRet;

  /* The principle used to locate the table name in the CREATE TRIGGER 
  ** statement is that the table name is the first token that is immediatedly
  ** preceded by either TK_ON or TK_DOT and immediatedly followed by one
  ** of TK_WHEN, TK_BEGIN or TK_FOR.
  */
  assert( argc==2 );
  if( zSql ){
    do {
      /* Store the token that zCsr points to in tname. */
      tname.z = zCsr;
      tname.n = len;

      /* Advance zCsr to the next token. Store that token type in 'token',







<







613
614
615
616
617
618
619

620
621
622
623
624
625
626
  char *zRet;

  /* The principle used to locate the table name in the CREATE TRIGGER 
  ** statement is that the table name is the first token that is immediatedly
  ** preceded by either TK_ON or TK_DOT and immediatedly followed by one
  ** of TK_WHEN, TK_BEGIN or TK_FOR.
  */

  if( zSql ){
    do {
      /* Store the token that zCsr points to in tname. */
      tname.z = zCsr;
      tname.n = len;

      /* Advance zCsr to the next token. Store that token type in 'token',
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
    } while( dist!=2 || (token!=TK_WHEN && token!=TK_FOR && token!=TK_BEGIN) );

    /* Variable tname now contains the token that is the old table-name
    ** in the CREATE TRIGGER statement.
    */
    zRet = sqlite3MPrintf("%.*s%Q%s", tname.z - zSql, zSql, 
       zTableName, tname.z+tname.n);
    sqlite3_result_text(context, zRet, -1, SQLITE_TRANSIENT);
    sqliteFree(zRet);
  }
}
#endif   /* !SQLITE_OMIT_TRIGGER */
#endif   /* !SQLITE_OMIT_ALTERTABLE */

/*
** EXPERIMENTAL - This is not an official function.  The interface may







|
<







648
649
650
651
652
653
654
655

656
657
658
659
660
661
662
    } while( dist!=2 || (token!=TK_WHEN && token!=TK_FOR && token!=TK_BEGIN) );

    /* Variable tname now contains the token that is the old table-name
    ** in the CREATE TRIGGER statement.
    */
    zRet = sqlite3MPrintf("%.*s%Q%s", tname.z - zSql, zSql, 
       zTableName, tname.z+tname.n);
    sqlite3_result_text(context, zRet, -1, sqlite3FreeX);

  }
}
#endif   /* !SQLITE_OMIT_TRIGGER */
#endif   /* !SQLITE_OMIT_ALTERTABLE */

/*
** EXPERIMENTAL - This is not an official function.  The interface may
Changes to test/alter.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21















22
23
24
25
26
27
28
#
# The author or author's hereby grant to the public domain a non-exclusive,
# fully paid-up, perpetual, license in the software and all related
# intellectual property to make, have made, use, have used, reproduce,
# prepare derivative works, distribute, perform and display the work.  
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ALTER TABLE statement.
#
# $Id: alter.test,v 1.3 2004/11/18 15:44:30 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {
  finish_test
  return
}
















# Create some tables to rename.  Be sure to include some TEMP tables
# and some tables with odd names.
#
do_test alter-1.1 {
  execsql {
    CREATE TABLE t1(a,b);










|










>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
#
# The author or author's hereby grant to the public domain a non-exclusive,
# fully paid-up, perpetual, license in the software and all related
# intellectual property to make, have made, use, have used, reproduce,
# prepare derivative works, distribute, perform and display the work.  
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the ALTER TABLE statement.
#
# $Id: alter.test,v 1.4 2004/11/19 05:14:56 danielk1977 Exp $
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {
  finish_test
  return
}

#----------------------------------------------------------------------
# Test organization:
#
# alter-1.1.* - alter-1.7.*: Basic tests of ALTER TABLE, including tables
#     with implicit and explicit indices. These tests came from an earlier
#     fork of SQLite that also supported ALTER TABLE.
# alter-1.8.*: Tests for ALTER TABLE when the table resides in an 
#     attached database.
# alter-1.9.*: Tests for ALTER TABLE when their is whitespace between the
#     table name and left parenthesis token. i.e: 
#     "CREATE TABLE abc       (a, b, c);"
# alter-2.*: Test error conditions and messages.
# alter-3.*: Test ALTER TABLE on tables that have TRIGGERs attached to them.
#

# Create some tables to rename.  Be sure to include some TEMP tables
# and some tables with odd names.
#
do_test alter-1.1 {
  execsql {
    CREATE TABLE t1(a,b);
191
192
193
194
195
196
197























198
199
200
201
202
203
204
} {main main main}
do_test alter-1.8.7 {
  execsql {
    ALTER TABLE aux.t4 RENAME TO t5;
    SELECT * FROM aux.t5 WHERE b = 'aux';
  }
} {aux aux aux}
























# Test error messages
#
do_test alter-2.1 {
  catchsql {
    ALTER TABLE none RENAME TO hi;
  }







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
} {main main main}
do_test alter-1.8.7 {
  execsql {
    ALTER TABLE aux.t4 RENAME TO t5;
    SELECT * FROM aux.t5 WHERE b = 'aux';
  }
} {aux aux aux}

do_test alter-1.9.1 {
  execsql {
    CREATE TABLE tbl1   (a, b, c);
    INSERT INTO tbl1 VALUES(1, 2, 3);
  }
} {}
do_test alter-1.9.2 {
  execsql {
    SELECT * FROM tbl1;
  }
} {1 2 3}
do_test alter-1.9.3 {
  execsql {
    ALTER TABLE tbl1 RENAME TO tbl2;
    SELECT * FROM tbl2;
  }
} {1 2 3}
do_test alter-1.9.4 {
  execsql {
    DROP TABLE tbl2;
  }
} {}

# Test error messages
#
do_test alter-2.1 {
  catchsql {
    ALTER TABLE none RENAME TO hi;
  }
219
220
221
222
223
224
225











226
227
228
229
230
231
232

# If this compilation does not include triggers, omit the remainder
# of this file.
ifcapable !trigger {
  finish_test
  return
}












# An SQL user-function for triggers to fire, so that we know they
# are working.
proc trigfunc {args} {
  set ::TRIGGER $args
}
db func trigfunc trigfunc







>
>
>
>
>
>
>
>
>
>
>







257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281

# If this compilation does not include triggers, omit the remainder
# of this file.
ifcapable !trigger {
  finish_test
  return
}

#-----------------------------------------------------------------------
# Tests alter-3.* test ALTER TABLE on tables that have triggers.
#
# alter-3.1.*: ALTER TABLE with triggers.
# alter-3.2.*: Test that the ON keyword cannot be used as a database,
#     table or column name unquoted. This is done because part of the
#     ALTER TABLE code (specifically the implementation of SQL function
#     "sqlite_alter_trigger") will break in this case.
# alter-3.3.*: ALTER TABLE with TEMP triggers (todo).
#

# An SQL user-function for triggers to fire, so that we know they
# are working.
proc trigfunc {args} {
  set ::TRIGGER $args
}
db func trigfunc trigfunc
345
346
347
348
349
350
351





352






353










































  }
} {1 {near "ON": syntax error}}
do_test alter-3.2.9 {
  catchsql {
    CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;
  }
} {0 {}}





finish_test
























































>
>
>
>
>
|
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
  }
} {1 {near "ON": syntax error}}
do_test alter-3.2.9 {
  catchsql {
    CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END;
  }
} {0 {}}
do_test alter-3.2.10 {
  execsql {
    DROP TABLE t10;
  }
} {}

do_test alter-3.3.1 {
  execsql {
    CREATE TABLE tbl1(a, b, c);
    CREATE TEMP TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN
      SELECT trigfunc('trig1', new.a, new.b, new.c);
    END;
  }
} {}
do_test alter-3.3.2 {
  execsql {
    INSERT INTO tbl1 VALUES('a', 'b', 'c');
  }
  set ::TRIGGER
} {trig1 a b c}
do_test alter-3.3.3 {
  execsql {
    ALTER TABLE tbl1 RENAME TO tbl2;
    INSERT INTO tbl2 VALUES('d', 'e', 'f');
  } 
  set ::TRIGGER
} {trig1 d e f}
do_test alter-3.3.4 {
  execsql {
    CREATE TEMP TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN
      SELECT trigfunc('trig2', new.a, new.b, new.c);
    END;
  } 
} {}
do_test alter-3.3.5 {
  execsql {
    ALTER TABLE tbl2 RENAME TO tbl3;
    INSERT INTO tbl3 VALUES('g', 'h', 'i');
  } 
  set ::TRIGGER
} {trig1 g h i}
do_test alter-3.3.6 {
  execsql {
    UPDATE tbl3 SET a = 'G' where a = 'g';
  } 
  set ::TRIGGER
} {trig2 G h i}
do_test alter-3.3.7 {
  execsql {
    DROP TABLE tbl3;
    SELECT * FROM sqlite_temp_master WHERE type = 'trigger';
  }
} {}

finish_test