SQLite

Check-in [04973fc2a6]
Login

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

Overview
Comment:Fix for ticket #104: Make triggers on views work properly even after closing and reopening the database. Also fixed an unrelated bug in the version 2.6.0 database format upgrade logic. The upgrade logic bug was found while testing the trigger fixes. (CVS 697)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 04973fc2a6a0c93877de7e564618e0e9fcffc06c
User & Date: drh 2002-07-31 00:32:50.000
Context
2002-07-31
00:37
Update version number and change log for the 2.6.2 release. (CVS 698) (check-in: 4f196de07d user: drh tags: trunk)
00:32
Fix for ticket #104: Make triggers on views work properly even after closing and reopening the database. Also fixed an unrelated bug in the version 2.6.0 database format upgrade logic. The upgrade logic bug was found while testing the trigger fixes. (CVS 697) (check-in: 04973fc2a6 user: drh tags: trunk)
2002-07-30
18:43
Fix for ticket #114: Correctly handle SQLITE_BUSY if it occurs during database initialization. (CVS 696) (check-in: 5b814b5df6 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/insert.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.64 2002/07/18 00:34:12 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.65 2002/07/31 00:32:50 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
86
87
88
89
90
91
92








93
94
95
96
97
98
99
    pParse->nErr++;
    goto insert_cleanup;
  }
  sqliteFree(zTab);
  zTab = 0;

  if( pTab==0 ) goto insert_cleanup;









  /* Allocate a VDBE
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto insert_cleanup;
  sqliteBeginWriteOperation(pParse, pSelect || row_triggers_exist);








>
>
>
>
>
>
>
>







86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
    pParse->nErr++;
    goto insert_cleanup;
  }
  sqliteFree(zTab);
  zTab = 0;

  if( pTab==0 ) goto insert_cleanup;

  /* If pTab is really a view, make sure it has been initialized.
  */
  if( pTab->pSelect ){
    if( sqliteViewGetColumnNames(pParse, pTab) ){
      goto insert_cleanup;
    }
  }

  /* Allocate a VDBE
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto insert_cleanup;
  sqliteBeginWriteOperation(pParse, pSelect || row_triggers_exist);

Changes to src/main.c.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.93 2002/07/30 18:43:41 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** A pointer to this structure is used to communicate information







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.94 2002/07/31 00:32:50 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>

/*
** A pointer to this structure is used to communicate information
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
  InitData *pData = (InitData*)pInit;
  int rc;
  Table *pTab;
  Trigger *pTrig;
  char *zErr = 0;

  pTab = sqliteFindTable(pData->db, argv[0]);


  if( pTab ){
    pTrig = pTab->pTrigger;
    pTab->pTrigger = 0;  /* Disable all triggers before rebuilding the table */
  }
  rc = sqlite_exec_printf(pData->db,
    "CREATE TEMP TABLE sqlite_x AS SELECT * FROM '%q'; "
    "DELETE FROM '%q'; "
    "INSERT INTO '%q' SELECT * FROM sqlite_x; "
    "DROP TABLE sqlite_x;",
    0, 0, &zErr, argv[0], argv[0], argv[0]);
  if( zErr ){
    sqliteSetString(pData->pzErrMsg, zErr, 0);
    sqlite_freemem(zErr);
  }









  if( pTab ) pTab->pTrigger = pTrig;  /* Re-enable triggers */

  return rc!=SQLITE_OK;
}



/*
** Attempt to read the database schema and initialize internal







>
>














>
>
>
>
>
>
>
>
>
|
>







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
  InitData *pData = (InitData*)pInit;
  int rc;
  Table *pTab;
  Trigger *pTrig;
  char *zErr = 0;

  pTab = sqliteFindTable(pData->db, argv[0]);
  assert( pTab!=0 );
  assert( sqliteStrICmp(pTab->zName, argv[0])==0 );
  if( pTab ){
    pTrig = pTab->pTrigger;
    pTab->pTrigger = 0;  /* Disable all triggers before rebuilding the table */
  }
  rc = sqlite_exec_printf(pData->db,
    "CREATE TEMP TABLE sqlite_x AS SELECT * FROM '%q'; "
    "DELETE FROM '%q'; "
    "INSERT INTO '%q' SELECT * FROM sqlite_x; "
    "DROP TABLE sqlite_x;",
    0, 0, &zErr, argv[0], argv[0], argv[0]);
  if( zErr ){
    sqliteSetString(pData->pzErrMsg, zErr, 0);
    sqlite_freemem(zErr);
  }

  /* If an error occurred in the SQL above, then the transaction will
  ** rollback which will delete the internal symbol tables.  This will
  ** cause the structure that pTab points to be deleted.  In case that
  ** happened, we need to refetch pTab.
  */
  pTab = sqliteFindTable(pData->db, argv[0]);
  if( pTab ){
    assert( sqliteStrICmp(pTab->zName, argv[0])==0 );
    pTab->pTrigger = pTrig;  /* Re-enable triggers */
  }
  return rc!=SQLITE_OK;
}



/*
** Attempt to read the database schema and initialize internal
Added test/trigger4.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
# 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 tests the triggers of views.
#

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

do_test trigger4-1.1 {
  execsql {
    create table test1(id integer primary key,a);
    create table test2(id integer,b);
    create view test as
      select test1.id as id,a as a,b as b
      from test1 join test2 on test2.id =  test1.id;
    create trigger I_test instead of insert on test
      begin
        insert into test1 (id,a) values (NEW.id,NEW.a);
        insert into test2 (id,b) values (NEW.id,NEW.b);
      end;
    insert into test values(1,2,3);
    select * from test1;
  }
} {1 2}
do_test trigger4-1.2 {
  execsql {
    select * from test2;
  }
} {1 3}
do_test trigger4-1.3 {
  db close
  sqlite db test.db
  execsql {
    insert into test values(4,5,6);
    select * from test1;
  }
} {1 2 4 5}
do_test trigger4-1.4 {
  execsql {
    select * from test2;
  }
} {1 3 4 6}

do_test trigger4-2.1 {
  execsql {
    create trigger U_test instead of update on test
      begin
        update test1 set a=NEW.a where id=NEW.id;
        update test2 set b=NEW.b where id=NEW.id;
      end;
    update test set a=22 where id=1;
    select * from test1;
  }
} {1 22 4 5}
do_test trigger4-2.2 {
  execsql {
    select * from test2;
  }
} {1 3 4 6}
do_test trigger4-2.3 {
  db close
  sqlite db test.db
  execsql {
    update test set b=66 where id=4;
    select * from test1;
  }
} {1 22 4 5}
do_test trigger4-2.4 {
  execsql {
    select * from test2;
  }
} {1 3 4 66}

do_test trigger4-3.1 {
  catchsql {
    drop table test2;
    insert into test values(7,8,9);
  }
} {1 {no such table: test2}}
do_test trigger4-3.2 {
  db close
  sqlite db test.db
  catchsql {
    insert into test values(7,8,9);
  }
} {1 {no such table: test2}}
do_test trigger4-3.3 {
  catchsql {
    update test set a=222 where id=1;
  }
} {1 {no such table: test2}}
do_test trigger4-3.4 {
  execsql {
    select * from test1;
  }
} {1 22 4 5}
do_test trigger4-3.5 {
  execsql {
    create table test2(id,b);
    insert into test values(7,8,9);
    select * from test1;
  }
} {1 22 4 5 7 8}
do_test trigger4-3.6 {
  execsql {
    select * from test2;
  }
} {7 9}
do_test trigger4-3.7 {
  db close
  sqlite db test.db
  execsql {
    update test set b=99 where id=7;
    select * from test2;
  }
} {7 99}

finish_test