/ Check-in [b9dbd993]
Login
Home Timeline Branches Tags Tickets Wiki More...

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

Overview
Comment: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).
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:b9dbd9936191666c3fc9889e938344a82cc03aeb
User & Date: dan 2011-04-09 15:39:02
Context
2011-04-09
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
03:20
If the keyword "unordered" appears at the end of the SQLITE_STAT1.STAT column for an index, then use that index for equality lookups only, never for range queries or sorting. This feature is currently undocumented and my change or be removed in a future release. check-in: 8a42e236 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c. 

809
810
811
812
813
814
815



816
817
818
819
820
821
822
....
2514
2515
2516
2517
2518
2519
2520



2521
2522
2523
2524
2525
2526
2527

    if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){
      goto begin_table_error;
    }
    pTable = sqlite3FindTable(db, zName, zDb);
    if( pTable ){
      if( !noErr ){
        sqlite3ErrorMsg(pParse, "table %T already exists", pName);



      }
      goto begin_table_error;
    }
    if( sqlite3FindIndex(db, zName, zDb)!=0 ){
      sqlite3ErrorMsg(pParse, "there is already an index named %s", zName);
      goto begin_table_error;
    }
................................................................................
        sqlite3ErrorMsg(pParse, "there is already a table named %s", zName);
        goto exit_create_index;
      }
    }
    if( sqlite3FindIndex(db, zName, pDb->zName)!=0 ){
      if( !ifNotExist ){
        sqlite3ErrorMsg(pParse, "index %s already exists", zName);



      }
      goto exit_create_index;
    }
  }else{
    int n;
    Index *pLoop;
    for(pLoop=pTab->pIndex, n=1; pLoop; pLoop=pLoop->pNext, n++){}








>
>
>







 







>
>
>








809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
....
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533

    if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){
      goto begin_table_error;
    }
    pTable = sqlite3FindTable(db, zName, zDb);
    if( pTable ){
      if( !noErr ){
        sqlite3ErrorMsg(pParse, "table %T already exists", pName);
      }else{
        assert( !db->init.busy );
        sqlite3CodeVerifySchema(pParse, iDb);
      }
      goto begin_table_error;
    }
    if( sqlite3FindIndex(db, zName, zDb)!=0 ){
      sqlite3ErrorMsg(pParse, "there is already an index named %s", zName);
      goto begin_table_error;
    }
................................................................................
        sqlite3ErrorMsg(pParse, "there is already a table named %s", zName);
        goto exit_create_index;
      }
    }
    if( sqlite3FindIndex(db, zName, pDb->zName)!=0 ){
      if( !ifNotExist ){
        sqlite3ErrorMsg(pParse, "index %s already exists", zName);
      }else{
        assert( !db->init.busy );
        sqlite3CodeVerifySchema(pParse, iDb);
      }
      goto exit_create_index;
    }
  }else{
    int n;
    Index *pLoop;
    for(pLoop=pTab->pIndex, n=1; pLoop; pLoop=pLoop->pNext, n++){}

Changes to src/trigger.c. 

167
168
169
170
171
172
173



174
175
176
177
178
179
180

    goto trigger_cleanup;
  }
  assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
  if( sqlite3HashFind(&(db->aDb[iDb].pSchema->trigHash),
                      zName, sqlite3Strlen30(zName)) ){
    if( !noErr ){
      sqlite3ErrorMsg(pParse, "trigger %T already exists", pName);



    }
    goto trigger_cleanup;
  }

  /* Do not create a trigger on a system table */
  if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 ){
    sqlite3ErrorMsg(pParse, "cannot create trigger on system table");








>
>
>








167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183

    goto trigger_cleanup;
  }
  assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
  if( sqlite3HashFind(&(db->aDb[iDb].pSchema->trigHash),
                      zName, sqlite3Strlen30(zName)) ){
    if( !noErr ){
      sqlite3ErrorMsg(pParse, "trigger %T already exists", pName);
    }else{
      assert( !db->init.busy );
      sqlite3CodeVerifySchema(pParse, iDb);
    }
    goto trigger_cleanup;
  }

  /* Do not create a trigger on a system table */
  if( sqlite3StrNICmp(pTab->zName, "sqlite_", 7)==0 ){
    sqlite3ErrorMsg(pParse, "cannot create trigger on system table");

Added test/exists.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
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
65
66
67
68
69
70
71
72
73
74
75
76

# 2011 April 9
#
# 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 various schema modification statements
# that feature "IF EXISTS" or "IF NOT EXISTS" clauses.
#

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}

  # TRIGGER objects.
  #
  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

This page was generated in about 0.016s by Fossil 2.9 [356c0d017e] 2019-05-23 17:18:56