SQLite

Check-in [0603eb74e6]
Login

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

Overview
Comment:Fix for ticket #95: Do not allow automatically created indices (associated with UNIQUE or PRIMARY KEY constraints) to be dropped. (CVS 671)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0603eb74e6aca48f62bd95cb6c236b9e559af850
User & Date: drh 2002-07-13 03:11:53.000
Context
2002-07-13
14:41
Fix for ticket #54: Export additional APIs to the Win32 DLL. (CVS 672) (check-in: 072fd2ad58 user: drh tags: trunk)
03:11
Fix for ticket #95: Do not allow automatically created indices (associated with UNIQUE or PRIMARY KEY constraints) to be dropped. (CVS 671) (check-in: 0603eb74e6 user: drh tags: trunk)
2002-07-11
12:18
Turn of the reporting of datatypes in the 4th callback argument unless the SHOW_DATATYPES pragma is ON. Eliminate the NULL pointer that used to separate the beginning of datatypes from the end of column names so that the callback can test to see whether or not datatypes are provided. This is an incompatible changes, but since the prior behavior was never documented, we will let it in. (CVS 670) (check-in: b98727246d user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.103 2002/07/11 12:18:16 drh 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







|







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.104 2002/07/13 03:11:53 drh 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
1270
1271
1272
1273
1274
1275
1276

1277
1278
1279
1280
1281
1282
1283
  if( pIndex==0 ) goto exit_create_index;
  pIndex->aiColumn = (int*)&pIndex[1];
  pIndex->zName = (char*)&pIndex->aiColumn[pList->nId];
  strcpy(pIndex->zName, zName);
  pIndex->pTable = pTab;
  pIndex->nColumn = pList->nId;
  pIndex->onError = pIndex->isUnique = onError;


  /* Scan the names of the columns of the table to be indexed and
  ** load the column indices into the Index structure.  Report an error
  ** if any column is not found.
  */
  for(i=0; i<pList->nId; i++){
    for(j=0; j<pTab->nCol; j++){







>







1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
  if( pIndex==0 ) goto exit_create_index;
  pIndex->aiColumn = (int*)&pIndex[1];
  pIndex->zName = (char*)&pIndex->aiColumn[pList->nId];
  strcpy(pIndex->zName, zName);
  pIndex->pTable = pTab;
  pIndex->nColumn = pList->nId;
  pIndex->onError = pIndex->isUnique = onError;
  pIndex->autoIndex = pName==0;

  /* Scan the names of the columns of the table to be indexed and
  ** load the column indices into the Index structure.  Report an error
  ** if any column is not found.
  */
  for(i=0; i<pList->nId; i++){
    for(j=0; j<pTab->nCol; j++){
1436
1437
1438
1439
1440
1441
1442






1443
1444
1445
1446
1447
1448
1449
  pIndex = sqliteFindIndex(db, zName);
  sqliteFree(zName);
  if( pIndex==0 ){
    sqliteSetNString(&pParse->zErrMsg, "no such index: ", 0, 
        pName->z, pName->n, 0);
    pParse->nErr++;
    return;






  }

  /* Generate code to remove the index and from the master table */
  v = sqliteGetVdbe(pParse);
  if( v ){
    static VdbeOp dropIndex[] = {
      { OP_Rewind,     0, ADDR(9), 0}, 







>
>
>
>
>
>







1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
  pIndex = sqliteFindIndex(db, zName);
  sqliteFree(zName);
  if( pIndex==0 ){
    sqliteSetNString(&pParse->zErrMsg, "no such index: ", 0, 
        pName->z, pName->n, 0);
    pParse->nErr++;
    return;
  }
  if( pIndex->autoIndex ){
    sqliteSetString(&pParse->zErrMsg, "index associated with UNIQUE "
      "or PRIMARY KEY constraint cannot be dropped", 0);
    pParse->nErr++;
    return;
  }

  /* Generate code to remove the index and from the master table */
  v = sqliteGetVdbe(pParse);
  if( v ){
    static VdbeOp dropIndex[] = {
      { OP_Rewind,     0, ADDR(9), 0}, 
Changes to src/sqliteInt.h.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.136 2002/07/11 12:18:17 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.137 2002/07/13 03:11:54 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
380
381
382
383
384
385
386

387
388
389
390
391
392
393
  char *zName;     /* Name of this index */
  int nColumn;     /* Number of columns in the table used by this index */
  int *aiColumn;   /* Which columns are used by this index.  1st is 0 */
  Table *pTable;   /* The SQL table being indexed */
  int tnum;        /* Page containing root of this index in database file */
  u8 isUnique;     /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  u8 onError;      /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */

  Index *pNext;    /* The next index associated with the same table */
};

/*
** Each token coming out of the lexer is an instance of
** this structure.
*/







>







380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
  char *zName;     /* Name of this index */
  int nColumn;     /* Number of columns in the table used by this index */
  int *aiColumn;   /* Which columns are used by this index.  1st is 0 */
  Table *pTable;   /* The SQL table being indexed */
  int tnum;        /* Page containing root of this index in database file */
  u8 isUnique;     /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  u8 onError;      /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  u8 autoIndex;    /* True if is automatically created (ex: by UNIQUE) */
  Index *pNext;    /* The next index associated with the same table */
};

/*
** Each token coming out of the lexer is an instance of
** this structure.
*/
Changes to test/index.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.17 2001/11/24 00:31:47 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.18 2002/07/13 03:11:54 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
376
377
378
379
380
381
382
383


































384
  }
} {0.0 0.00 -1.0 0 00000}
do_test index-12.7 {
  execsql {
    SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
  }
} {0.0 0.00 abc +1.0 0 00000}



































finish_test








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

376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
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
  }
} {0.0 0.00 -1.0 0 00000}
do_test index-12.7 {
  execsql {
    SELECT a FROM t4 WHERE a>-0.5 ORDER BY b
  }
} {0.0 0.00 abc +1.0 0 00000}

# Make sure we cannot drop an automatically created index.
#
do_test index-13.1 {
  execsql {
   CREATE TABLE t5(
      a int UNIQUE,
      b float PRIMARY KEY,
      c varchar(10),
      UNIQUE(a,c)
   );
   INSERT INTO t5 VALUES(1,2,3);
   SELECT * FROM t5;
  }
} {1 2 3}
do_test index-13.2 {
  set ::idxlist [execsql {
    SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
  }]
  llength $::idxlist
} {3}
for {set i 0} {$i<[llength $::idxlist]} {incr i} {
  do_test index-13.3.$i {
    catchsql "
      DROP INDEX '[lindex $::idxlist $i]';
    "
  } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
}
do_test index-13.4 {
  execsql {
    INSERT INTO t5 VALUES('a','b','c');
    SELECT * FROM t5;
  }
} {1 2 3 a b c}

finish_test