/ Check-in [a46f3290]
Login
Home Timeline Branches Tags Tickets Wiki More...
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Add VerifyCookie instructions to "DROP XXX IF EXISTS" statements if the specified database object does not exist when the statement is prepared.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a46f32900a013aa6bb2dad2a9ed3ce00ab2493fd
User & Date: dan 2011-04-09 17:32:58
Context
2011-04-09
17:53
Remove an always-true conditional. Replace it with an assert(). check-in: 1c2f0f84 user: drh tags: trunk
17:32
Add VerifyCookie instructions to "DROP XXX IF EXISTS" statements if the specified database object does not exist when the statement is prepared. check-in: a46f3290 user: dan tags: trunk
15:39
Add an OP_VerifyCookie instruction to "CREATE XXX IF NOT EXISTS" commands. This way, if the specified database object existed when the statement was compiled but removed from the database before sqlite3_step() was called, the statement still works as expected (and creates the object). check-in: b9dbd993 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c. 

1999
2000
2001
2002
2003
2004
2005

2006
2007
2008
2009
2010
2011
2012
....
2913
2914
2915
2916
2917
2918
2919


2920
2921
2922
2923
2924
2925
2926
....
3501
3502
3503
3504
3505
3506
3507















3508
3509
3510
3511
3512
3513
3514

  assert( pName->nSrc==1 );
  if( noErr ) db->suppressErr++;
  pTab = sqlite3LocateTable(pParse, isView, 
                            pName->a[0].zName, pName->a[0].zDatabase);
  if( noErr ) db->suppressErr--;

  if( pTab==0 ){

    goto exit_drop_table;
  }
  iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  assert( iDb>=0 && iDb<db->nDb );

  /* If pTab is a virtual table, call ViewGetColumnNames() to ensure
  ** it is initialized.
................................................................................
  if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){
    goto exit_drop_index;
  }
  pIndex = sqlite3FindIndex(db, pName->a[0].zName, pName->a[0].zDatabase);
  if( pIndex==0 ){
    if( !ifExists ){
      sqlite3ErrorMsg(pParse, "no such index: %S", pName, 0);


    }
    pParse->checkSchema = 1;
    goto exit_drop_index;
  }
  if( pIndex->autoIndex ){
    sqlite3ErrorMsg(pParse, "index associated with UNIQUE "
      "or PRIMARY KEY constraint cannot be dropped", 0);
................................................................................
      pToplevel->cookieValue[iDb] = db->aDb[iDb].pSchema->schema_cookie;
      if( !OMIT_TEMPDB && iDb==1 ){
        sqlite3OpenTempDatabase(pToplevel);
      }
    }
  }
}
















/*
** Generate VDBE code that prepares for doing an operation that
** might change the database.
**
** This routine starts a new transaction if we are not already within
** a transaction.  If we are already within a transaction, then a checkpoint








>







 







>
>







 







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








1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
....
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926
2927
2928
2929
....
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
3517
3518
3519
3520
3521
3522
3523
3524
3525
3526
3527
3528
3529
3530
3531
3532

  assert( pName->nSrc==1 );
  if( noErr ) db->suppressErr++;
  pTab = sqlite3LocateTable(pParse, isView, 
                            pName->a[0].zName, pName->a[0].zDatabase);
  if( noErr ) db->suppressErr--;

  if( pTab==0 ){
    if( noErr ) sqlite3CodeVerifyNamedSchema(pParse, pName->a[0].zDatabase);
    goto exit_drop_table;
  }
  iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  assert( iDb>=0 && iDb<db->nDb );

  /* If pTab is a virtual table, call ViewGetColumnNames() to ensure
  ** it is initialized.
................................................................................
  if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){
    goto exit_drop_index;
  }
  pIndex = sqlite3FindIndex(db, pName->a[0].zName, pName->a[0].zDatabase);
  if( pIndex==0 ){
    if( !ifExists ){
      sqlite3ErrorMsg(pParse, "no such index: %S", pName, 0);
    }else{
      sqlite3CodeVerifyNamedSchema(pParse, pName->a[0].zDatabase);
    }
    pParse->checkSchema = 1;
    goto exit_drop_index;
  }
  if( pIndex->autoIndex ){
    sqlite3ErrorMsg(pParse, "index associated with UNIQUE "
      "or PRIMARY KEY constraint cannot be dropped", 0);
................................................................................
      pToplevel->cookieValue[iDb] = db->aDb[iDb].pSchema->schema_cookie;
      if( !OMIT_TEMPDB && iDb==1 ){
        sqlite3OpenTempDatabase(pToplevel);
      }
    }
  }
}

/*
** If argument zDb is NULL, then call sqlite3CodeVerifySchema() for each 
** attached database. Otherwise, invoke it for the database named zDb only.
*/
void sqlite3CodeVerifyNamedSchema(Parse *pParse, const char *zDb){
  sqlite3 *db = pParse->db;
  int i;
  for(i=0; i<db->nDb; i++){
    Db *pDb = &db->aDb[i];
    if( pDb->pBt && (!zDb || 0==sqlite3StrICmp(zDb, pDb->zName)) ){
      sqlite3CodeVerifySchema(pParse, i);
    }
  }
}

/*
** Generate VDBE code that prepares for doing an operation that
** might change the database.
**
** This routine starts a new transaction if we are not already within
** a transaction.  If we are already within a transaction, then a checkpoint

Changes to src/sqliteInt.h. 

2764
2765
2766
2767
2768
2769
2770

2771
2772
2773
2774
2775
2776
2777

void sqlite3ExprAnalyzeAggList(NameContext*,ExprList*);
Vdbe *sqlite3GetVdbe(Parse*);
void sqlite3PrngSaveState(void);
void sqlite3PrngRestoreState(void);
void sqlite3PrngResetState(void);
void sqlite3RollbackAll(sqlite3*);
void sqlite3CodeVerifySchema(Parse*, int);

void sqlite3BeginTransaction(Parse*, int);
void sqlite3CommitTransaction(Parse*);
void sqlite3RollbackTransaction(Parse*);
void sqlite3Savepoint(Parse*, int, Token*);
void sqlite3CloseSavepoints(sqlite3 *);
int sqlite3ExprIsConstant(Expr*);
int sqlite3ExprIsConstantNotJoin(Expr*);








>








2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778

void sqlite3ExprAnalyzeAggList(NameContext*,ExprList*);
Vdbe *sqlite3GetVdbe(Parse*);
void sqlite3PrngSaveState(void);
void sqlite3PrngRestoreState(void);
void sqlite3PrngResetState(void);
void sqlite3RollbackAll(sqlite3*);
void sqlite3CodeVerifySchema(Parse*, int);
void sqlite3CodeVerifyNamedSchema(Parse*, const char *zDb);
void sqlite3BeginTransaction(Parse*, int);
void sqlite3CommitTransaction(Parse*);
void sqlite3RollbackTransaction(Parse*);
void sqlite3Savepoint(Parse*, int, Token*);
void sqlite3CloseSavepoints(sqlite3 *);
int sqlite3ExprIsConstant(Expr*);
int sqlite3ExprIsConstantNotJoin(Expr*);

Changes to src/trigger.c. 

498
499
500
501
502
503
504


505
506
507
508
509
510
511

    assert( sqlite3SchemaMutexHeld(db, j, 0) );
    pTrigger = sqlite3HashFind(&(db->aDb[j].pSchema->trigHash), zName, nName);
    if( pTrigger ) break;
  }
  if( !pTrigger ){
    if( !noErr ){
      sqlite3ErrorMsg(pParse, "no such trigger: %S", pName, 0);


    }
    pParse->checkSchema = 1;
    goto drop_trigger_cleanup;
  }
  sqlite3DropTriggerPtr(pParse, pTrigger);

drop_trigger_cleanup:








>
>








498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513

    assert( sqlite3SchemaMutexHeld(db, j, 0) );
    pTrigger = sqlite3HashFind(&(db->aDb[j].pSchema->trigHash), zName, nName);
    if( pTrigger ) break;
  }
  if( !pTrigger ){
    if( !noErr ){
      sqlite3ErrorMsg(pParse, "no such trigger: %S", pName, 0);
    }else{
      sqlite3CodeVerifyNamedSchema(pParse, zDb);
    }
    pParse->checkSchema = 1;
    goto drop_trigger_cleanup;
  }
  sqlite3DropTriggerPtr(pParse, pTrigger);

drop_trigger_cleanup:

Changes to test/exists.test. 

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
..
65
66
67
68
69
70
71
72


















































































































73
74
75
76


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

set testprefix exists



do_multiclient_test tn {

  # TABLE objects.
  #
  do_test $tn.1.1 {
    sql2 { CREATE TABLE t1(x) }
    sql1 { CREATE TABLE IF NOT EXISTS t1(a, b) }
    sql2 { DROP TABLE t1 }
    sql1 { CREATE TABLE IF NOT EXISTS t1(a, b) }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
  } {t1}

  do_test $tn.1.2 {
    sql2 { CREATE TABLE t2(x) }
    sql1 { CREATE TABLE IF NOT EXISTS t2 AS SELECT * FROM t1 }
    sql2 { DROP TABLE t2 }
    sql1 { CREATE TABLE IF NOT EXISTS t2 AS SELECT * FROM t1 }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
  } {t1 t2}


  # INDEX objects.
  #
  do_test $tn.2 {
    sql2 { CREATE INDEX i1 ON t1(a) }
    sql1 { CREATE INDEX IF NOT EXISTS i1 ON t1(a, b) }
    sql2 { DROP INDEX i1 }
    sql1 { CREATE INDEX IF NOT EXISTS i1 ON t1(a, b) }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'index' }
  } {i1}

  # VIEW objects.
  #
  do_test $tn.3 {
    sql2 { CREATE VIEW v1 AS SELECT * FROM t1 }
    sql1 { CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1 }
    sql2 { DROP VIEW v1 }
    sql1 { CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1 }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'view' }
  } {v1}

................................................................................
  do_test $tn.4 {
    sql2 { CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END }
sql1 { CREATE TRIGGER IF NOT EXISTS tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END }
    sql2 { DROP TRIGGER tr1 }
sql1 { CREATE TRIGGER IF NOT EXISTS tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'trigger' }
  } {tr1}



















































































































}


finish_test








>
>




|







|










|









|







 







|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>





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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
..
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192


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

set testprefix exists

# This block of tests is targeted at CREATE XXX IF NOT EXISTS statements.
#
do_multiclient_test tn {

  # TABLE objects.
  #
  do_test 1.$tn.1.1 {
    sql2 { CREATE TABLE t1(x) }
    sql1 { CREATE TABLE IF NOT EXISTS t1(a, b) }
    sql2 { DROP TABLE t1 }
    sql1 { CREATE TABLE IF NOT EXISTS t1(a, b) }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
  } {t1}

  do_test 1.$tn.1.2 {
    sql2 { CREATE TABLE t2(x) }
    sql1 { CREATE TABLE IF NOT EXISTS t2 AS SELECT * FROM t1 }
    sql2 { DROP TABLE t2 }
    sql1 { CREATE TABLE IF NOT EXISTS t2 AS SELECT * FROM t1 }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
  } {t1 t2}


  # INDEX objects.
  #
  do_test 1.$tn.2 {
    sql2 { CREATE INDEX i1 ON t1(a) }
    sql1 { CREATE INDEX IF NOT EXISTS i1 ON t1(a, b) }
    sql2 { DROP INDEX i1 }
    sql1 { CREATE INDEX IF NOT EXISTS i1 ON t1(a, b) }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'index' }
  } {i1}

  # VIEW objects.
  #
  do_test 1.$tn.3 {
    sql2 { CREATE VIEW v1 AS SELECT * FROM t1 }
    sql1 { CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1 }
    sql2 { DROP VIEW v1 }
    sql1 { CREATE VIEW IF NOT EXISTS v1 AS SELECT * FROM t1 }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'view' }
  } {v1}

................................................................................
  do_test $tn.4 {
    sql2 { CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END }
sql1 { CREATE TRIGGER IF NOT EXISTS tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END }
    sql2 { DROP TRIGGER tr1 }
sql1 { CREATE TRIGGER IF NOT EXISTS tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'trigger' }
  } {tr1}
}

# This block of tests is targeted at DROP XXX IF EXISTS statements.
#
do_multiclient_test tn {

  # TABLE objects.
  #
  do_test 2.$tn.1 {
    sql1 { DROP TABLE IF EXISTS t1 }
    sql2 { CREATE TABLE t1(x) }
    sql1 { DROP TABLE IF EXISTS t1 }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
  } {}

  # INDEX objects.
  #
  do_test 2.$tn.2 {
    sql1 { CREATE TABLE t2(x) }
    sql1 { DROP INDEX IF EXISTS i2 }
    sql2 { CREATE INDEX i2 ON t2(x) }
    sql1 { DROP INDEX IF EXISTS i2 }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'index' }
  } {}

  # VIEW objects.
  #
  do_test 2.$tn.3 {
    sql1 { DROP VIEW IF EXISTS v1 }
    sql2 { CREATE VIEW v1 AS SELECT * FROM t2 }
    sql1 { DROP VIEW IF EXISTS v1 }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'view' }
  } {}
  
  # TRIGGER objects.
  #
  do_test 2.$tn.4 {
    sql1 { DROP TRIGGER IF EXISTS tr1 }
    sql2 { CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN SELECT 1; END }
    sql1 { DROP TRIGGER IF EXISTS tr1 }
    sql2 { SELECT name FROM sqlite_master WHERE type = 'trigger' }
  } {}
}

# This block of tests is targeted at DROP XXX IF EXISTS statements with
# attached databases.
#
do_multiclient_test tn {

  forcedelete test.db2
  do_test 3.$tn.0 {
    sql1 { ATTACH 'test.db2' AS aux }
    sql2 { ATTACH 'test.db2' AS aux }
  } {}

  # TABLE objects.
  #
  do_test 3.$tn.1.1 {
    sql1 { DROP TABLE IF EXISTS aux.t1 }
    sql2 { CREATE TABLE aux.t1(x) }
    sql1 { DROP TABLE IF EXISTS aux.t1 }
    sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'table' }
  } {}
  do_test 3.$tn.1.2 {
    sql1 { DROP TABLE IF EXISTS t1 }
    sql2 { CREATE TABLE aux.t1(x) }
    sql1 { DROP TABLE IF EXISTS t1 }
    sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'table' }
  } {}

  # INDEX objects.
  #
  do_test 3.$tn.2.1 {
    sql1 { CREATE TABLE aux.t2(x) }
    sql1 { DROP INDEX IF EXISTS aux.i2 }
    sql2 { CREATE INDEX aux.i2 ON t2(x) }
    sql1 { DROP INDEX IF EXISTS aux.i2 }
    sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'index' }
  } {}
  do_test 3.$tn.2.2 {
    sql1 { DROP INDEX IF EXISTS i2 }
    sql2 { CREATE INDEX aux.i2 ON t2(x) }
    sql1 { DROP INDEX IF EXISTS i2 }
    sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'index' }
  } {}

  # VIEW objects.
  #
  do_test 3.$tn.3.1 {
    sql1 { DROP VIEW IF EXISTS aux.v1 }
    sql2 { CREATE VIEW aux.v1 AS SELECT * FROM t2 }
    sql1 { DROP VIEW IF EXISTS aux.v1 }
    sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'view' }
  } {}
  do_test 3.$tn.3.2 {
    sql1 { DROP VIEW IF EXISTS v1 }
    sql2 { CREATE VIEW aux.v1 AS SELECT * FROM t2 }
    sql1 { DROP VIEW IF EXISTS v1 }
    sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'view' }
  } {}
  
  # TRIGGER objects.
  #
  do_test 3.$tn.4.1 {
    sql1 { DROP TRIGGER IF EXISTS aux.tr1 }
    sql2 { CREATE TRIGGER aux.tr1 AFTER INSERT ON t2 BEGIN SELECT 1; END }
    sql1 { DROP TRIGGER IF EXISTS aux.tr1 }
    sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'trigger' }
  } {}
  do_test 3.$tn.4.2 {
    sql1 { DROP TRIGGER IF EXISTS tr1 }
    sql2 { CREATE TRIGGER aux.tr1 AFTER INSERT ON t2 BEGIN SELECT 1; END }
    sql1 { DROP TRIGGER IF EXISTS tr1 }
    sql2 { SELECT name FROM aux.sqlite_master WHERE type = 'trigger' }
  } {}
}


finish_test

This page was generated in about 0.035s by Fossil 2.10 [9d9ef82234] 2019-10-04 21:41:13