/ Check-in [5ab71c3a]
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:When materializing a view for an UPDATE or DELETE make use of the WHERE clause to limit the number of rows materialized. Ticket #2938. (CVS 4782)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 5ab71c3a79cac04cb2c576f83a62218d05571006
User & Date: drh 2008-02-12 16:52:14
Context
2008-02-13
18:25
Where possible, avoid freeing buffers allocated for vdbe memory cells in case they can be reused. (CVS 4783) check-in: 990237e2 user: danielk1977 tags: trunk
2008-02-12
16:52
When materializing a view for an UPDATE or DELETE make use of the WHERE clause to limit the number of rows materialized. Ticket #2938. (CVS 4782) check-in: 5ab71c3a user: drh tags: trunk
2008-02-09
14:30
ALTER TABLE uses double-quotes for quoting table names. (CVS 4781) check-in: 607247c2 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/delete.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
76
77
78
79
80
81
82

































83
84
85
86
87
88
89
...
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
...
217
218
219
220
221
222
223
224
225
226

227
228

229
230
231


232
233
234
235
236
237
238
**    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
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.160 2008/01/25 15:04:50 drh Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
................................................................................
  assert( opcode==OP_OpenWrite || opcode==OP_OpenRead );
  sqlite3TableLock(p, iDb, pTab->tnum, (opcode==OP_OpenWrite), pTab->zName);
  sqlite3VdbeAddOp3(v, opcode, iCur, pTab->tnum, iDb);
  VdbeComment((v, "%s", pTab->zName));
  sqlite3VdbeAddOp2(v, OP_SetNumColumns, iCur, pTab->nCol);
}



































/*
** Generate code for a DELETE FROM statement.
**
**     DELETE FROM table_wxyz WHERE a<5 AND b NOT NULL;
**                 \________/       \________________/
**                  pTabList              pWhere
................................................................................

  /* Allocate a cursor used to store the old.* data for a trigger.
  */
  if( triggers_exist ){ 
    oldIdx = pParse->nTab++;
  }

  /* Resolve the column names in the WHERE clause.
  */
  assert( pTabList->nSrc==1 );
  iCur = pTabList->a[0].iCursor = pParse->nTab++;
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    pParse->nTab++;
  }
  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;
  if( sqlite3ExprResolveNames(&sNC, pWhere) ){
    goto delete_from_cleanup;
  }

  /* Start the view context
  */
  if( isView ){
    sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
  }

................................................................................
    sqlite3VdbeJumpHere(v, iGoto);
  }

  /* If we are trying to delete from a view, realize that view into
  ** a ephemeral table.
  */
  if( isView ){
    SelectDest dest;
    Select *pView;


    pView = sqlite3SelectDup(db, pTab->pSelect);
    sqlite3SelectMask(pParse, pView, old_col_mask);

    sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
    sqlite3Select(pParse, pView, &dest, 0, 0, 0, 0);
    sqlite3SelectDelete(pView);


  }

  /* Initialize the counter of the number of rows deleted, if
  ** we are counting rows.
  */
  if( db->flags & SQLITE_CountRows ){
    memCnt = ++pParse->nMem;







|







 







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







 







|






<
<
<
<
<
<







 







|
<
|
>
|
<
>
|
|
|
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
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
...
199
200
201
202
203
204
205
206
207
208
209
210
211
212






213
214
215
216
217
218
219
...
244
245
246
247
248
249
250
251

252
253
254

255
256
257
258
259
260
261
262
263
264
265
266
267
**    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
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.161 2008/02/12 16:52:14 drh Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
................................................................................
  assert( opcode==OP_OpenWrite || opcode==OP_OpenRead );
  sqlite3TableLock(p, iDb, pTab->tnum, (opcode==OP_OpenWrite), pTab->zName);
  sqlite3VdbeAddOp3(v, opcode, iCur, pTab->tnum, iDb);
  VdbeComment((v, "%s", pTab->zName));
  sqlite3VdbeAddOp2(v, OP_SetNumColumns, iCur, pTab->nCol);
}


#if !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER)
/*
** Evaluate a view and store its result in an ephemeral table.  The
** pWhere argument is an optional WHERE clause that restricts the
** set of rows in the view that are to be added to the ephemeral table.
*/
void sqlite3MaterializeView(
  Parse *pParse,       /* Parsing context */
  Select *pView,       /* View definition */
  Expr *pWhere,        /* Optional WHERE clause to be added */
  u32 col_mask,        /* Render only the columns in this mask. */
  int iCur             /* Cursor number for ephemerial table */
){
  SelectDest dest;
  Select *pDup;
  sqlite3 *db = pParse->db;

  pDup = sqlite3SelectDup(db, pView);
  if( pWhere ){
    SrcList *pFrom;
    
    pWhere = sqlite3ExprDup(db, pWhere);
    pFrom = sqlite3SrcListAppendFromTerm(pParse, 0, 0, 0, 0, pDup, 0, 0);
    pDup = sqlite3SelectNew(pParse, 0, pFrom, pWhere, 0, 0, 0, 0, 0, 0);
  }
  sqlite3SelectMask(pParse, pDup, col_mask);
  sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
  sqlite3Select(pParse, pDup, &dest, 0, 0, 0, 0);
  sqlite3SelectDelete(pDup);
}
#endif /* !defined(SQLITE_OMIT_VIEW) && !defined(SQLITE_OMIT_TRIGGER) */


/*
** Generate code for a DELETE FROM statement.
**
**     DELETE FROM table_wxyz WHERE a<5 AND b NOT NULL;
**                 \________/       \________________/
**                  pTabList              pWhere
................................................................................

  /* Allocate a cursor used to store the old.* data for a trigger.
  */
  if( triggers_exist ){ 
    oldIdx = pParse->nTab++;
  }

  /* Assign  cursor number to the table and all its indices.
  */
  assert( pTabList->nSrc==1 );
  iCur = pTabList->a[0].iCursor = pParse->nTab++;
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    pParse->nTab++;
  }







  /* Start the view context
  */
  if( isView ){
    sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
  }

................................................................................
    sqlite3VdbeJumpHere(v, iGoto);
  }

  /* If we are trying to delete from a view, realize that view into
  ** a ephemeral table.
  */
  if( isView ){
    sqlite3MaterializeView(pParse, pTab->pSelect, pWhere, old_col_mask, iCur);

  }

  /* Resolve the column names in the WHERE clause.

  */
  memset(&sNC, 0, sizeof(sNC));
  sNC.pParse = pParse;
  sNC.pSrcList = pTabList;
  if( sqlite3ExprResolveNames(&sNC, pWhere) ){
    goto delete_from_cleanup;
  }

  /* Initialize the counter of the number of rows deleted, if
  ** we are counting rows.
  */
  if( db->flags & SQLITE_CountRows ){
    memCnt = ++pParse->nMem;

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1822
1823
1824
1825
1826
1827
1828

1829
1830
1831
1832
1833
1834
1835
**    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.659 2008/02/02 04:47:09 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds
................................................................................
#else
# define sqlite3SafetyOn(A) 0
# define sqlite3SafetyOff(A) 0
#endif
int sqlite3SafetyCheckOk(sqlite3*);
int sqlite3SafetyCheckSickOrOk(sqlite3*);
void sqlite3ChangeCookie(Parse*, int);


#ifndef SQLITE_OMIT_TRIGGER
  void sqlite3BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*,
                           Expr*,int, int);
  void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*);
  void sqlite3DropTrigger(Parse*, SrcList*, int);
  void sqlite3DropTriggerPtr(Parse*, Trigger*);







|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
**    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.660 2008/02/12 16:52:14 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds
................................................................................
#else
# define sqlite3SafetyOn(A) 0
# define sqlite3SafetyOff(A) 0
#endif
int sqlite3SafetyCheckOk(sqlite3*);
int sqlite3SafetyCheckSickOrOk(sqlite3*);
void sqlite3ChangeCookie(Parse*, int);
void sqlite3MaterializeView(Parse*, Select*, Expr*, u32, int);

#ifndef SQLITE_OMIT_TRIGGER
  void sqlite3BeginTrigger(Parse*, Token*,Token*,int,int,IdList*,SrcList*,
                           Expr*,int, int);
  void sqlite3FinishTrigger(Parse*, TriggerStep*, Token*);
  void sqlite3DropTrigger(Parse*, SrcList*, int);
  void sqlite3DropTriggerPtr(Parse*, Trigger*);

Changes to src/update.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
...
331
332
333
334
335
336
337
338
339
340
341

342
343
344

345

346
347
348
349
350
351
352
**    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 UPDATE statements.
**
** $Id: update.c,v 1.170 2008/01/19 03:35:59 drh Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
................................................................................
                       pWhere);
    pWhere = 0;
    pTabList = 0;
    goto update_cleanup;
  }
#endif

  /* Resolve the column names in all the expressions in the
  ** WHERE clause.
  */
  if( sqlite3ExprResolveNames(&sNC, pWhere) ){
    goto update_cleanup;
  }

  /* Start the view context
  */
  if( isView ){
    sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
  }

  /* Generate the code for triggers.
................................................................................
    sqlite3VdbeJumpHere(v, iGoto);
  }

  /* If we are trying to update a view, realize that view into
  ** a ephemeral table.
  */
  if( isView ){
    Select *pView;
    SelectDest dest;

    pView = sqlite3SelectDup(db, pTab->pSelect);

    sqlite3SelectMask(pParse, pView, old_col_mask|new_col_mask);
    sqlite3SelectDestInit(&dest, SRT_EphemTab, iCur);
    sqlite3Select(pParse, pView, &dest, 0, 0, 0, 0);

    sqlite3SelectDelete(pView);

  }

  /* Begin the database scan
  */
  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0);
  if( pWInfo==0 ) goto update_cleanup;








|







 







<
<
<
<
<
<
<







 







|
|
|
<
>
|
|
<
>
|
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
285
286
287
288
289
290
291







292
293
294
295
296
297
298
...
324
325
326
327
328
329
330
331
332
333

334
335
336

337
338
339
340
341
342
343
344
345
346
**    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 UPDATE statements.
**
** $Id: update.c,v 1.171 2008/02/12 16:52:14 drh Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
................................................................................
                       pWhere);
    pWhere = 0;
    pTabList = 0;
    goto update_cleanup;
  }
#endif








  /* Start the view context
  */
  if( isView ){
    sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
  }

  /* Generate the code for triggers.
................................................................................
    sqlite3VdbeJumpHere(v, iGoto);
  }

  /* If we are trying to update a view, realize that view into
  ** a ephemeral table.
  */
  if( isView ){
    sqlite3MaterializeView(pParse, pTab->pSelect, pWhere,
                           old_col_mask|new_col_mask, iCur);
  }


  /* Resolve the column names in all the expressions in the
  ** WHERE clause.

  */
  if( sqlite3ExprResolveNames(&sNC, pWhere) ){
    goto update_cleanup;
  }

  /* Begin the database scan
  */
  pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0);
  if( pWInfo==0 ) goto update_cleanup;

Changes to test/auth.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254



2255
2256
2257
2258
2259
2260
2261
....
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276



2277
2278
2279
2280
2281
2282
2283
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the sqlite3_set_authorizer() API
# and related functionality.
#
# $Id: auth.test,v 1.40 2008/01/01 19:02:09 danielk1977 Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.
................................................................................
  set authargs {}
  execsql {
    UPDATE v1 SET x=1 WHERE x=117
  }
  set authargs
} [list \
  SQLITE_UPDATE v1     x  main {} \
  SQLITE_READ   v1     x  main {} \
  SQLITE_INSERT v1chng {} main r2 \
  SQLITE_READ   v1     x  main r2 \
  SQLITE_READ   v1     x  main r2 \
  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \
  SQLITE_SELECT {}     {} {}   v1]



do_test auth-4.4 {
  execsql {
    CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN
      INSERT INTO v1chng VALUES(OLD.x,NULL);
    END;
    SELECT * FROM v1;
  }
................................................................................
  set authargs {}
  execsql {
    DELETE FROM v1 WHERE x=117
  }
  set authargs
} [list \
  SQLITE_DELETE v1     {} main {} \
  SQLITE_READ   v1     x  main {} \
  SQLITE_INSERT v1chng {} main r3 \
  SQLITE_READ   v1     x  main r3 \
  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \
  SQLITE_SELECT {}     {} {}   v1]




} ;# ifcapable view && trigger

# Ticket #1338:  Make sure authentication works in the presence of an AS
# clause.
#
do_test auth-5.1 {







|







 







<





|
>
>
>







 







<




|
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2241
2242
2243
2244
2245
2246
2247

2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
....
2266
2267
2268
2269
2270
2271
2272

2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the sqlite3_set_authorizer() API
# and related functionality.
#
# $Id: auth.test,v 1.41 2008/02/12 16:52:14 drh Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.
................................................................................
  set authargs {}
  execsql {
    UPDATE v1 SET x=1 WHERE x=117
  }
  set authargs
} [list \
  SQLITE_UPDATE v1     x  main {} \

  SQLITE_INSERT v1chng {} main r2 \
  SQLITE_READ   v1     x  main r2 \
  SQLITE_READ   v1     x  main r2 \
  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_READ   v1     x  main v1 \
]
do_test auth-4.4 {
  execsql {
    CREATE TRIGGER r3 INSTEAD OF DELETE ON v1 BEGIN
      INSERT INTO v1chng VALUES(OLD.x,NULL);
    END;
    SELECT * FROM v1;
  }
................................................................................
  set authargs {}
  execsql {
    DELETE FROM v1 WHERE x=117
  }
  set authargs
} [list \
  SQLITE_DELETE v1     {} main {} \

  SQLITE_INSERT v1chng {} main r3 \
  SQLITE_READ   v1     x  main r3 \
  SQLITE_READ   t2     a  main v1 \
  SQLITE_READ   t2     b  main v1 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_SELECT {}     {} {}   v1 \
  SQLITE_READ   v1     x  main v1 \
]

} ;# ifcapable view && trigger

# Ticket #1338:  Make sure authentication works in the presence of an AS
# clause.
#
do_test auth-5.1 {

Added test/triggerA.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
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
# 2008 February 12
#
# 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. Specifically,
# it tests issues relating to firing an INSTEAD OF trigger on a VIEW
# when one tries to UPDATE or DELETE from the view.  Does the WHERE
# clause of the UPDATE or DELETE statement get passed down correctly 
# into the query that manifests the view?
#
# Ticket #2938
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable {!trigger} {
  finish_test
  return
}

# Create two table containing some sample data
#
do_test triggerA-1.1 {
  db eval {
    CREATE TABLE t1(x INTEGER PRIMARY KEY, y TEXT UNIQUE);
    CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c TEXT);
  }
  set i 1
  foreach word {one two three four five six seven eight nine ten} {
    set j [expr {$i*100 + [string length $word]}]
    db eval {
       INSERT INTO t1 VALUES($i,$word);
       INSERT INTO t2 VALUES(20-$i,$j,$word);
    }
    incr i
  }
  db eval {
    SELECT count(*) FROM t1 UNION ALL SELECT count(*) FROM t2;
  }
} {10 10}

# Create views of various forms against one or both of the two tables.
#
do_test triggerA-1.2 {
  db eval {
     CREATE VIEW v1 AS SELECT y, x FROM t1;
     SELECT * FROM v1 ORDER BY 1;
  }
} {eight 8 five 5 four 4 nine 9 one 1 seven 7 six 6 ten 10 three 3 two 2}
do_test triggerA-1.3 {
  db eval {
     CREATE VIEW v2 AS SELECT x, y FROM t1 WHERE y GLOB '*e*';
     SELECT * FROM v2 ORDER BY 1;
  }
} {1 one 3 three 5 five 7 seven 8 eight 9 nine 10 ten}
do_test triggerA-1.4 {
  db eval {
     CREATE VIEW v3 AS
       SELECT CAST(x AS TEXT) AS c1 FROM t1 UNION SELECT y FROM t1;
     SELECT * FROM v3 ORDER BY c1;
  }
} {1 10 2 3 4 5 6 7 8 9 eight five four nine one seven six ten three two}
do_test triggerA-1.5 {
  db eval {
     CREATE VIEW v4 AS
        SELECT CAST(x AS TEXT) AS c1 FROM t1
        UNION SELECT y FROM t1 WHERE x BETWEEN 3 and 5;
     SELECT * FROM v4 ORDER BY 1;
  }
} {1 10 2 3 4 5 6 7 8 9 five four three}
do_test triggerA-1.6 {
  db eval {
     CREATE VIEW v5 AS SELECT x, b FROM t1, t2 WHERE y=c;
     SELECT * FROM v5;
  }
} {1 103 2 203 3 305 4 404 5 504 6 603 7 705 8 805 9 904 10 1003}

# Create INSTEAD OF triggers on the views.  Run UPDATE and DELETE statements
# using those triggers.  Verify correct operation.
#
do_test triggerA-2.1 {
  db eval {
     CREATE TABLE result2(a,b);
     CREATE TRIGGER r1d INSTEAD OF DELETE ON v1 BEGIN
       INSERT INTO result2(a,b) VALUES(old.y, old.x);
     END;
     DELETE FROM v1 WHERE x=5;
     SELECT * FROM result2;
  }
} {five 5}
do_test triggerA-2.2 {
  db eval {
     CREATE TABLE result4(a,b,c,d);
     CREATE TRIGGER r1u INSTEAD OF UPDATE ON v1 BEGIN
       INSERT INTO result4(a,b,c,d) VALUES(old.y, old.x, new.y, new.x);
     END;
     UPDATE v1 SET y=y||'-extra' WHERE x BETWEEN 3 AND 5;
     SELECT * FROM result4 ORDER BY a;
  }
} {five 5 five-extra 5 four 4 four-extra 4 three 3 three-extra 3}


do_test triggerA-2.3 {
  db eval {
     DELETE FROM result2;
     CREATE TRIGGER r2d INSTEAD OF DELETE ON v2 BEGIN
       INSERT INTO result2(a,b) VALUES(old.y, old.x);
     END;
     DELETE FROM v2 WHERE x=5;
     SELECT * FROM result2;
  }
} {five 5}
do_test triggerA-2.4 {
  db eval {
     DELETE FROM result4;
     CREATE TRIGGER r2u INSTEAD OF UPDATE ON v2 BEGIN
       INSERT INTO result4(a,b,c,d) VALUES(old.y, old.x, new.y, new.x);
     END;
     UPDATE v2 SET y=y||'-extra' WHERE x BETWEEN 3 AND 5;
     SELECT * FROM result4 ORDER BY a;
  }
} {five 5 five-extra 5 three 3 three-extra 3}


do_test triggerA-2.5 {
  db eval {
     CREATE TABLE result1(a);
     CREATE TRIGGER r3d INSTEAD OF DELETE ON v3 BEGIN
       INSERT INTO result1(a) VALUES(old.c1);
     END;
     DELETE FROM v3 WHERE c1 BETWEEN '8' AND 'eight';
     SELECT * FROM result1 ORDER BY a;
  }
} {8 9 eight}
do_test triggerA-2.6 {
  db eval {
     DELETE FROM result2;
     CREATE TRIGGER r3u INSTEAD OF UPDATE ON v3 BEGIN
       INSERT INTO result2(a,b) VALUES(old.c1, new.c1);
     END;
     UPDATE v3 SET c1 = c1 || '-extra' WHERE c1 BETWEEN '8' and 'eight';
     SELECT * FROM result2 ORDER BY a;
  }
} {8 8-extra 9 9-extra eight eight-extra}


do_test triggerA-2.7 {
  db eval {
     DELETE FROM result1;
     CREATE TRIGGER r4d INSTEAD OF DELETE ON v4 BEGIN
       INSERT INTO result1(a) VALUES(old.c1);
     END;
     DELETE FROM v4 WHERE c1 BETWEEN '8' AND 'eight';
     SELECT * FROM result1 ORDER BY a;
  }
} {8 9}
do_test triggerA-2.8 {
  db eval {
     DELETE FROM result2;
     CREATE TRIGGER r4u INSTEAD OF UPDATE ON v4 BEGIN
       INSERT INTO result2(a,b) VALUES(old.c1, new.c1);
     END;
     UPDATE v4 SET c1 = c1 || '-extra' WHERE c1 BETWEEN '8' and 'eight';
     SELECT * FROM result2 ORDER BY a;
  }
} {8 8-extra 9 9-extra}


do_test triggerA-2.9 {
  db eval {
     DELETE FROM result2;
     CREATE TRIGGER r5d INSTEAD OF DELETE ON v5 BEGIN
       INSERT INTO result2(a,b) VALUES(old.x, old.b);
     END;
     DELETE FROM v5 WHERE x=5;
     SELECT * FROM result2;
  }
} {5 504}
do_test triggerA-2.10 {
  db eval {
     DELETE FROM result4;
     CREATE TRIGGER r5u INSTEAD OF UPDATE ON v5 BEGIN
       INSERT INTO result4(a,b,c,d) VALUES(old.x, old.b, new.x, new.b);
     END;
     UPDATE v5 SET b = b+9900000 WHERE x BETWEEN 3 AND 5;
     SELECT * FROM result4 ORDER BY a;
  }
} {3 305 3 9900305 4 404 4 9900404 5 504 5 9900504}

# Only run the reamining tests if memory debugging is turned on.
#
ifcapable !memdebug {
   puts "Skipping triggerA malloc tests: not compiled with -DSQLITE_MEMDEBUG..."
   finish_test
   return
}
source $testdir/malloc_common.tcl

# Save a copy of the current database configuration.
#
db close
file delete -force test.db-triggerA
file copy test.db test.db-triggerA
sqlite3 db test.db

# Run malloc tests on the INSTEAD OF trigger firing.
#
do_malloc_test triggerA-3 -tclprep {
  db close
  file delete -force test.db test.db-journal
  file copy -force test.db-triggerA test.db
  sqlite3 db test.db
  sqlite3_extended_result_codes db 1  
  db eval {SELECT * FROM v5; -- warm up the cache}
} -sqlbody {
   DELETE FROM v5 WHERE x=5;
   UPDATE v5 SET b=b+9900000 WHERE x BETWEEN 3 AND 5;
}

# Clean up the saved database copy.
#
file delete -force test.db-triggerA

finish_test