/ Check-in [655991ec]
Login
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:Fix some problems that can occur if a trigger has the same name as another database object.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 655991ec8a781d67d69fc353853403b9a811acf2
User & Date: dan 2010-09-28 19:16:47
Original User & Date: dan 2010-09-29 07:16:47
Context
2010-09-28
20:26
Simplify the test that determines if the name of a new table collides with a prior index name. check-in: 3f30f00a user: drh tags: trunk
19:16
Fix some problems that can occur if a trigger has the same name as another database object. check-in: 655991ec user: dan tags: trunk
17:37
Merge accidental fork. check-in: 33c8b9c7 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/alter.c.

309
310
311
312
313
314
315





316
317
318
319
320
321
322
    sqlite3 *db = pParse->db;
    for(pTrig=sqlite3TriggerList(pParse, pTab); pTrig; pTrig=pTrig->pNext){
      if( pTrig->pSchema==pTempSchema ){
        zWhere = whereOrName(db, zWhere, pTrig->zName);
      }
    }
  }





  return zWhere;
}

/*
** Generate code to drop and reload the internal representation of table
** pTab from the database, including triggers and temporary triggers.
** Argument zName is the name of the table in the database schema at







>
>
>
>
>







309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
    sqlite3 *db = pParse->db;
    for(pTrig=sqlite3TriggerList(pParse, pTab); pTrig; pTrig=pTrig->pNext){
      if( pTrig->pSchema==pTempSchema ){
        zWhere = whereOrName(db, zWhere, pTrig->zName);
      }
    }
  }
  if( zWhere ){
    char *zNew = sqlite3MPrintf(pParse->db, "type='trigger' AND (%s)", zWhere);
    sqlite3DbFree(pParse->db, zWhere);
    zWhere = zNew;
  }
  return zWhere;
}

/*
** Generate code to drop and reload the internal representation of table
** pTab from the database, including triggers and temporary triggers.
** Argument zName is the name of the table in the database schema at

Changes to src/build.c.

2768
2769
2770
2771
2772
2773
2774
2775

2776
2777
2778
2779
2780
2781
2782
....
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
    /* Fill the index with data and reparse the schema. Code an OP_Expire
    ** to invalidate all pre-compiled statements.
    */
    if( pTblName ){
      sqlite3RefillIndex(pParse, pIndex, iMem);
      sqlite3ChangeCookie(pParse, iDb);
      sqlite3VdbeAddOp4(v, OP_ParseSchema, iDb, 0, 0,
         sqlite3MPrintf(db, "name='%q'", pIndex->zName), P4_DYNAMIC);

      sqlite3VdbeAddOp1(v, OP_Expire, 0);
    }
  }

  /* When adding an index to the list of indices for a table, make
  ** sure all indices labeled OE_Replace come after all those labeled
  ** OE_Ignore.  This is necessary for the correct constraint check
................................................................................
#endif

  /* Generate code to remove the index and from the master table */
  v = sqlite3GetVdbe(pParse);
  if( v ){
    sqlite3BeginWriteOperation(pParse, 1, iDb);
    sqlite3NestedParse(pParse,
       "DELETE FROM %Q.%s WHERE name=%Q",
       db->aDb[iDb].zName, SCHEMA_TABLE(iDb),
       pIndex->zName
    );
    if( sqlite3FindTable(db, "sqlite_stat1", db->aDb[iDb].zName) ){
      sqlite3NestedParse(pParse,
        "DELETE FROM %Q.sqlite_stat1 WHERE idx=%Q",
        db->aDb[iDb].zName, pIndex->zName







|
>







 







|







2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
....
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
    /* Fill the index with data and reparse the schema. Code an OP_Expire
    ** to invalidate all pre-compiled statements.
    */
    if( pTblName ){
      sqlite3RefillIndex(pParse, pIndex, iMem);
      sqlite3ChangeCookie(pParse, iDb);
      sqlite3VdbeAddOp4(v, OP_ParseSchema, iDb, 0, 0,
         sqlite3MPrintf(db, "name='%q' AND type='index'", pIndex->zName), 
         P4_DYNAMIC);
      sqlite3VdbeAddOp1(v, OP_Expire, 0);
    }
  }

  /* When adding an index to the list of indices for a table, make
  ** sure all indices labeled OE_Replace come after all those labeled
  ** OE_Ignore.  This is necessary for the correct constraint check
................................................................................
#endif

  /* Generate code to remove the index and from the master table */
  v = sqlite3GetVdbe(pParse);
  if( v ){
    sqlite3BeginWriteOperation(pParse, 1, iDb);
    sqlite3NestedParse(pParse,
       "DELETE FROM %Q.%s WHERE name=%Q AND type='index'",
       db->aDb[iDb].zName, SCHEMA_TABLE(iDb),
       pIndex->zName
    );
    if( sqlite3FindTable(db, "sqlite_stat1", db->aDb[iDb].zName) ){
      sqlite3NestedParse(pParse,
        "DELETE FROM %Q.sqlite_stat1 WHERE idx=%Q",
        db->aDb[iDb].zName, pIndex->zName

Changes to src/vtab.c.

367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
      pParse->regRowid
    );
    sqlite3DbFree(db, zStmt);
    v = sqlite3GetVdbe(pParse);
    sqlite3ChangeCookie(pParse, iDb);

    sqlite3VdbeAddOp2(v, OP_Expire, 0, 0);
    zWhere = sqlite3MPrintf(db, "name='%q'", pTab->zName);
    sqlite3VdbeAddOp4(v, OP_ParseSchema, iDb, 1, 0, zWhere, P4_DYNAMIC);
    sqlite3VdbeAddOp4(v, OP_VCreate, iDb, 0, 0, 
                         pTab->zName, sqlite3Strlen30(pTab->zName) + 1);
  }

  /* If we are rereading the sqlite_master table create the in-memory
  ** record of the table. The xConnect() method is not called until







|







367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
      pParse->regRowid
    );
    sqlite3DbFree(db, zStmt);
    v = sqlite3GetVdbe(pParse);
    sqlite3ChangeCookie(pParse, iDb);

    sqlite3VdbeAddOp2(v, OP_Expire, 0, 0);
    zWhere = sqlite3MPrintf(db, "name='%q' AND type='table'", pTab->zName);
    sqlite3VdbeAddOp4(v, OP_ParseSchema, iDb, 1, 0, zWhere, P4_DYNAMIC);
    sqlite3VdbeAddOp4(v, OP_VCreate, iDb, 0, 0, 
                         pTab->zName, sqlite3Strlen30(pTab->zName) + 1);
  }

  /* If we are rereading the sqlite_master table create the in-memory
  ** record of the table. The xConnect() method is not called until

Added test/schema4.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
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
# 2010 September 28
#
# 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 that a trigger may have the same
# name as an index, view or table in the same database.
#

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

#--------------------------------------------------------------------------
# Test organization:
#
#   schema4-1.*: Dropping and creating triggers and other objects where
#     triggers and at least on other object share a name.
#
#   schema4-2.*: Renaming tables where there is a trigger that shares the
#     name of the table or one of its indices.
#

do_execsql_test schema4-1.1 {
  CREATE TABLE log(x, a, b);
  CREATE TABLE tbl(a, b);

  CREATE TABLE t1(a, b);
  CREATE VIEW v1 AS SELECT * FROM tbl;
  CREATE INDEX i1 ON tbl(a);
} {}

do_execsql_test schema4-1.2 {
  CREATE TRIGGER t1 AFTER INSERT ON tbl BEGIN
    INSERT INTO log VALUES('after insert', new.a, new.b);
  END;
  CREATE TRIGGER v1 AFTER UPDATE ON tbl BEGIN
    INSERT INTO log VALUES('after update', new.a, new.b);
  END;
  CREATE TRIGGER i1 AFTER DELETE ON tbl BEGIN
    INSERT INTO log VALUES('after delete', old.a, old.b);
  END;
} {}

do_execsql_test schema4-1.3 {
  INSERT INTO tbl VALUES(1, 2);
  UPDATE tbl SET b=a+b, a=a+1;
  DELETE FROM tbl;

  SELECT x, a, b FROM log;
} {{after insert} 1 2 {after update} 2 3 {after delete} 2 3}

do_execsql_test schema4-1.4 {
  DELETE FROM log;

  DROP INDEX i1;
  DROP TABLE t1;
  DROP VIEW v1;

  INSERT INTO tbl VALUES(1, 2);
  UPDATE tbl SET b=a+b, a=a+1;
  DELETE FROM tbl;

  SELECT x, a, b FROM log;
} {{after insert} 1 2 {after update} 2 3 {after delete} 2 3}

db close
sqlite3 db test.db

do_execsql_test schema4-1.5 {
  DELETE FROM log;
  INSERT INTO tbl VALUES(1, 2);
  UPDATE tbl SET b=a+b, a=a+1;
  DELETE FROM tbl;
  SELECT x, a, b FROM log;
} {{after insert} 1 2 {after update} 2 3 {after delete} 2 3}

do_execsql_test schema4-1.6 {
  CREATE TABLE t1(a, b);
  CREATE VIEW v1 AS SELECT * FROM tbl;
  CREATE INDEX i1 ON tbl(a);
} {}

ifcapable fts3 {
  do_execsql_test schema4-1.7 {
    DROP TABLE t1;
    CREATE VIRTUAL TABLE t1 USING fts3;
  } {}

  do_execsql_test schema4-1.8 {
    DELETE FROM log;
    DROP TABLE t1;
    INSERT INTO tbl VALUES(1, 2);
    UPDATE tbl SET b=a+b, a=a+1;
    DELETE FROM tbl;
    SELECT x, a, b FROM log;
  } {{after insert} 1 2 {after update} 2 3 {after delete} 2 3}
}

ifcapable altertable {
  drop_all_tables
  do_execsql_test schema4-2.1 {
    CREATE TABLE log(x, a, b);
    CREATE TABLE tbl(a, b);
  
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(a, b);
  } {}
  
  do_execsql_test schema4-2.2 {
    CREATE TRIGGER t1 AFTER INSERT ON tbl BEGIN
      INSERT INTO log VALUES('after insert', new.a, new.b);
    END;
    CREATE TRIGGER i1 AFTER DELETE ON tbl BEGIN
      INSERT INTO log VALUES('after delete', old.a, old.b);
    END;
  } {}

  do_execsql_test schema4-2.3 { ALTER TABLE t1 RENAME TO t2 } {}

  do_execsql_test schema4-2.4 { 
    INSERT INTO tbl VALUES('a', 'b');
    DELETE FROM tbl;
    SELECT * FROM log;
  } {{after insert} a b {after delete} a b}

  db close
  sqlite3 db test.db

  do_execsql_test schema4-2.5 { 
    DELETE FROM log;
    INSERT INTO tbl VALUES('c', 'd');
    DELETE FROM tbl;
    SELECT * FROM log;
  } {{after insert} c d {after delete} c d}

  do_execsql_test schema4-2.6 {
    CREATE TEMP TRIGGER x1 AFTER UPDATE ON tbl BEGIN
      INSERT INTO log VALUES('after update', new.a, new.b);
    END;

    CREATE TEMP TABLE x1(x);
    INSERT INTO x1 VALUES(123);
  } {}

  do_execsql_test schema4-2.8 {
    select sql from sqlite_temp_master WHERE type='table';
  } {{CREATE TABLE x1(x)}}

  do_execsql_test schema4-2.7 { ALTER TABLE tbl RENAME TO tbl2 } {}

  do_execsql_test schema4-2.9 {
    select sql from sqlite_temp_master WHERE type='table';
  } {{CREATE TABLE x1(x)}}

  do_execsql_test schema4-2.10 { 
    DELETE FROM log;
    INSERT INTO tbl2 VALUES('e', 'f');
    UPDATE tbl2 SET a='g', b='h';
    DELETE FROM tbl2;
    SELECT * FROM log;
  } {{after insert} e f {after update} g h {after delete} g h}

  do_execsql_test schema4-2.11 {
    INSERT INTO x1 VALUES(456);
    SELECT * FROM x1
  } {123 456}
}

finish_test