/ Check-in [1330993d]
Login

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

Overview
Comment:Enhance autoincrement so that it works with triggers that also do autoincrement inserts, even multiple inserts into the same table. Ticket #3928 (CVS 6807)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 1330993de8eae7baeec24100216158063c9bdc19
User & Date: drh 2009-06-23 20:28:54
References
2016-10-03
02:01 New ticket [7b332808] AUTOINCREMENT problem with the INSERT xfer optimization. artifact: 5a826871 user: drh
Context
2009-06-24
05:40
Simplify things by rolling the functionality of balance_shallower() into balance_nonroot(). (CVS 6808) check-in: 11750c6a user: danielk1977 tags: trunk
2009-06-23
20:28
Enhance autoincrement so that it works with triggers that also do autoincrement inserts, even multiple inserts into the same table. Ticket #3928 (CVS 6807) check-in: 1330993d user: drh tags: trunk
16:40
Remove a condition from balance_nonroot() that is always true. (CVS 6806) check-in: c5dc80e6 user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
...
170
171
172
173
174
175
176






177
178
179
180
181
182
183
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.552 2009/06/18 17:22:39 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.
*/
................................................................................
#endif

      /* Once all the cookies have been verified and transactions opened, 
      ** obtain the required table-locks. This is a no-op unless the 
      ** shared-cache feature is enabled.
      */
      codeTableLocks(pParse);






      sqlite3VdbeAddOp2(v, OP_Goto, 0, pParse->cookieGoto);
    }
  }


  /* Get the VDBE program ready for execution
  */







|







 







>
>
>
>
>
>







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
...
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.553 2009/06/23 20:28:54 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.
*/
................................................................................
#endif

      /* Once all the cookies have been verified and transactions opened, 
      ** obtain the required table-locks. This is a no-op unless the 
      ** shared-cache feature is enabled.
      */
      codeTableLocks(pParse);

      /* Initialize any AUTOINCREMENT data structures required.
      */
      sqlite3AutoincrementBegin(pParse);

      /* Finally, jump back to the beginning of the executable code. */
      sqlite3VdbeAddOp2(v, OP_Goto, 0, pParse->cookieGoto);
    }
  }


  /* Get the VDBE program ready for execution
  */

Changes to src/delete.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
470
471
472
473
474
475
476








477
478
479
480
481
482
483
**    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.203 2009/05/28 01:00:55 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.
................................................................................
    if( !isView  && !IsVirtual(pTab) ){
      for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
        sqlite3VdbeAddOp2(v, OP_Close, iCur + i, pIdx->tnum);
      }
      sqlite3VdbeAddOp1(v, OP_Close, iCur);
    }
  }









  /*
  ** Return the number of rows that were deleted. If this routine is 
  ** generating code because of a call to sqlite3NestedParse(), do not
  ** invoke the callback function.
  */
  if( db->flags & SQLITE_CountRows && pParse->nested==0 && !pParse->trigStack ){







|







 







>
>
>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
**    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.204 2009/06/23 20:28:54 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.
................................................................................
    if( !isView  && !IsVirtual(pTab) ){
      for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
        sqlite3VdbeAddOp2(v, OP_Close, iCur + i, pIdx->tnum);
      }
      sqlite3VdbeAddOp1(v, OP_Close, iCur);
    }
  }

  /* Update the sqlite_sequence table by storing the content of the
  ** maximum rowid counter values recorded while inserting into
  ** autoincrement tables.
  */
  if( pParse->nested==0 && pParse->trigStack==0 ){
    sqlite3AutoincrementEnd(pParse);
  }

  /*
  ** Return the number of rows that were deleted. If this routine is 
  ** generating code because of a call to sqlite3NestedParse(), do not
  ** invoke the callback function.
  */
  if( db->flags & SQLITE_CountRows && pParse->nested==0 && !pParse->trigStack ){

Changes to src/insert.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
...
222
223
224
225
226
227
228
229
230
231



232
233
234
235
236
237
238
239
240
241




242
243
244

245
246

247
248





249



250

251
252
253
254

255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
...
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
...
985
986
987
988
989
990
991

992
993
994

995
996



997
998
999
1000
1001
1002
1003
....
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
**    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.268 2009/05/29 19:00:13 drh Exp $
*/
#include "sqliteInt.h"

/*
** Generate code that will open a table for reading.
*/
void sqlite3OpenTable(
................................................................................
#endif
  }
  return 0;
}

#ifndef SQLITE_OMIT_AUTOINCREMENT
/*
** Write out code to initialize the autoincrement logic.  This code
** looks up the current autoincrement value in the sqlite_sequence
** table and stores that value in a register.  Code generated by
** autoIncStep() will keep that register holding the largest
** rowid value.  Code generated by autoIncEnd() will write the new
** largest value of the counter back into the sqlite_sequence table.
**
** This routine returns the index of the mem[] cell that contains
** the maximum rowid counter.
**
** Three consecutive registers are allocated by this routine.  The
** first two hold the name of the target table and the maximum rowid 
** inserted into the target table, respectively.
** The third holds the rowid in sqlite_sequence where we will
** write back the revised maximum rowid.  This routine returns the
** index of the second of these three registers.


*/
static int autoIncBegin(
  Parse *pParse,      /* Parsing context */
  int iDb,            /* Index of the database holding pTab */
  Table *pTab         /* The table we are writing to */
){
  int memId = 0;      /* Register holding maximum rowid */
  if( pTab->tabFlags & TF_Autoincrement ){


    Vdbe *v = pParse->pVdbe;
    Db *pDb = &pParse->db->aDb[iDb];




    int iCur = pParse->nTab++;
    int addr;               /* Address of the top of the loop */
    assert( v );



    pParse->nMem++;         /* Holds name of table */
    memId = ++pParse->nMem;
    pParse->nMem++;






















    sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenRead);
    addr = sqlite3VdbeCurrentAddr(v);
    sqlite3VdbeAddOp4(v, OP_String8, 0, memId-1, 0, pTab->zName, 0);
    sqlite3VdbeAddOp2(v, OP_Rewind, iCur, addr+9);
    sqlite3VdbeAddOp3(v, OP_Column, iCur, 0, memId);
    sqlite3VdbeAddOp3(v, OP_Ne, memId-1, addr+7, memId);
    sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);
    sqlite3VdbeAddOp2(v, OP_Rowid, iCur, memId+1);
    sqlite3VdbeAddOp3(v, OP_Column, iCur, 1, memId);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addr+9);
    sqlite3VdbeAddOp2(v, OP_Next, iCur, addr+2);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, memId);
    sqlite3VdbeAddOp2(v, OP_Close, iCur, 0);
  }
  return memId;
}

/*
** Update the maximum rowid for an autoincrement calculation.
**
** This routine should be called when the top of the stack holds a
** new rowid that is about to be inserted.  If that new rowid is
** larger than the maximum rowid in the memId memory cell, then the
................................................................................
static void autoIncStep(Parse *pParse, int memId, int regRowid){
  if( memId>0 ){
    sqlite3VdbeAddOp2(pParse->pVdbe, OP_MemMax, memId, regRowid);
  }
}

/*
** After doing one or more inserts, the maximum rowid is stored
** in reg[memId].  Generate code to write this value back into the
** the sqlite_sequence table.



*/
static void autoIncEnd(
  Parse *pParse,     /* The parsing context */
  int iDb,           /* Index of the database holding pTab */
  Table *pTab,       /* Table we are inserting into */
  int memId          /* Memory cell holding the maximum rowid */
){
  if( pTab->tabFlags & TF_Autoincrement ){
    int iCur = pParse->nTab++;
    Vdbe *v = pParse->pVdbe;




    Db *pDb = &pParse->db->aDb[iDb];
    int j1;
    int iRec = ++pParse->nMem;    /* Memory cell used for record */


    assert( v );

    sqlite3OpenTable(pParse, iCur, iDb, pDb->pSchema->pSeqTab, OP_OpenWrite);
    j1 = sqlite3VdbeAddOp1(v, OP_NotNull, memId+1);





    sqlite3VdbeAddOp2(v, OP_NewRowid, iCur, memId+1);



    sqlite3VdbeJumpHere(v, j1);

    sqlite3VdbeAddOp3(v, OP_MakeRecord, memId-1, 2, iRec);
    sqlite3VdbeAddOp3(v, OP_Insert, iCur, iRec, memId+1);
    sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
    sqlite3VdbeAddOp1(v, OP_Close, iCur);

  }
}
#else
/*
** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
** above are all no-ops
*/
# define autoIncBegin(A,B,C) (0)
# define autoIncStep(A,B,C)
# define autoIncEnd(A,B,C,D)
#endif /* SQLITE_OMIT_AUTOINCREMENT */


/* Forward declaration */
static int xferOptimization(
  Parse *pParse,        /* Parser context */
  Table *pDest,         /* The table we are inserting into */
................................................................................
  ** very fast and which reduce fragmentation of indices.
  **
  ** This is the 2nd template.
  */
  if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
    assert( !pTrigger );
    assert( pList==0 );
    goto insert_cleanup;
  }
#endif /* SQLITE_OMIT_XFER_OPT */

  /* If this is an AUTOINCREMENT table, look up the sequence number in the
  ** sqlite_sequence table and store it in memory cell regAutoinc.
  */
  regAutoinc = autoIncBegin(pParse, iDb, pTab);
................................................................................
    /* Close all tables opened */
    sqlite3VdbeAddOp1(v, OP_Close, baseCur);
    for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
      sqlite3VdbeAddOp1(v, OP_Close, idx+baseCur);
    }
  }


  /* Update the sqlite_sequence table by storing the content of the
  ** counter value in memory regAutoinc back into the sqlite_sequence
  ** table.

  */
  autoIncEnd(pParse, iDb, pTab, regAutoinc);




  /*
  ** Return the number of rows inserted. If this routine is 
  ** generating code because of a call to sqlite3NestedParse(), do not
  ** invoke the callback function.
  */
  if( db->flags & SQLITE_CountRows && pParse->nested==0 && !pParse->trigStack ){
................................................................................
    assert( (pDest->tabFlags & TF_Autoincrement)==0 );
  }
  sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);
  sqlite3VdbeAddOp3(v, OP_Insert, iDest, regData, regRowid);
  sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND);
  sqlite3VdbeChangeP4(v, -1, pDest->zName, 0);
  sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1);
  autoIncEnd(pParse, iDbDest, pDest, regAutoinc);
  for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
    for(pSrcIdx=pSrc->pIndex; ALWAYS(pSrcIdx); pSrcIdx=pSrcIdx->pNext){
      if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
    }
    assert( pSrcIdx );
    sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
    sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);







|







 







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








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

|
|
|


|
|

|

|

<








 







|
<
|
>
>
>

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

<
>
|

>
>
>
>
>
|
>
>
>

>

|

|
>









<







 







|







 







>

<
|
>

<
>
>
>







 







<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
231
232
233
234
235
236
237
238
239
240

241
242
243
244
245
246
247
248
...
251
252
253
254
255
256
257
258

259
260
261
262
263


264
265




266
267
268
269
270
271
272
273
274
275

276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303

304
305
306
307
308
309
310
...
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
....
1024
1025
1026
1027
1028
1029
1030
1031
1032

1033
1034
1035

1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
....
1754
1755
1756
1757
1758
1759
1760

1761
1762
1763
1764
1765
1766
1767
**    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.269 2009/06/23 20:28:54 drh Exp $
*/
#include "sqliteInt.h"

/*
** Generate code that will open a table for reading.
*/
void sqlite3OpenTable(
................................................................................
#endif
  }
  return 0;
}

#ifndef SQLITE_OMIT_AUTOINCREMENT
/*
** Locate or create an AutoincInfo structure associated with table pTab
** which is in database iDb.  Return the register number for the register
** that holds the maximum rowid.
**
** There is at most one AutoincInfo structure per table even if the
** same table is autoincremented multiple times due to inserts within
** triggers.  A new AutoincInfo structure is created if this is the
** first use of table pTab.  On 2nd and subsequent uses, the original
** AutoincInfo structure is used.
**
** Three memory locations are allocated:
**
**   (1)  Register to hold the name of the pTab table.
**   (2)  Register to hold the maximum ROWID of pTab.
**   (3)  Register to hold the rowid in sqlite_sequence of pTab
**
** The 2nd register is the one that is returned.  That is all the
** insert routine needs to know about.
*/
static int autoIncBegin(
  Parse *pParse,      /* Parsing context */
  int iDb,            /* Index of the database holding pTab */
  Table *pTab         /* The table we are writing to */
){
  int memId = 0;      /* Register holding maximum rowid */
  if( pTab->tabFlags & TF_Autoincrement ){
    AutoincInfo *pInfo;

    pInfo = pParse->pAinc;

    while( pInfo && pInfo->pTab!=pTab ){ pInfo = pInfo->pNext; }
    if( pInfo==0 ){
      pInfo = sqlite3DbMallocRaw(pParse->db, sizeof(*pInfo));
      if( pInfo==0 ) return 0;
      pInfo->pNext = pParse->pAinc;


      pParse->pAinc = pInfo;
      pInfo->pTab = pTab;
      pInfo->iDb = iDb;
      pParse->nMem++;                  /* Register to hold name of table */
      pInfo->regCtr = ++pParse->nMem;  /* Max rowid register */
      pParse->nMem++;                  /* Rowid in sqlite_sequence */
    }
    memId = pInfo->regCtr;
  }
  return memId;
}

/*
** This routine generates code that will initialize all of the
** register used by the autoincrement tracker.  
*/
void sqlite3AutoincrementBegin(Parse *pParse){
  AutoincInfo *p;            /* Information about an AUTOINCREMENT */
  sqlite3 *db = pParse->db;  /* The database connection */
  Db *pDb;                   /* Database only autoinc table */
  int memId;                 /* Register holding max rowid */
  int addr;                  /* A VDBE address */
  Vdbe *v = pParse->pVdbe;   /* VDBE under construction */

  assert( v );   /* We failed long ago if this is not so */
  for(p = pParse->pAinc; p; p = p->pNext){
    pDb = &db->aDb[p->iDb];
    memId = p->regCtr;
    sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenRead);
    addr = sqlite3VdbeCurrentAddr(v);
    sqlite3VdbeAddOp4(v, OP_String8, 0, memId-1, 0, p->pTab->zName, 0);
    sqlite3VdbeAddOp2(v, OP_Rewind, 0, addr+9);
    sqlite3VdbeAddOp3(v, OP_Column, 0, 0, memId);
    sqlite3VdbeAddOp3(v, OP_Ne, memId-1, addr+7, memId);
    sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);
    sqlite3VdbeAddOp2(v, OP_Rowid, 0, memId+1);
    sqlite3VdbeAddOp3(v, OP_Column, 0, 1, memId);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, addr+9);
    sqlite3VdbeAddOp2(v, OP_Next, 0, addr+2);
    sqlite3VdbeAddOp2(v, OP_Integer, 0, memId);
    sqlite3VdbeAddOp0(v, OP_Close);
  }

}

/*
** Update the maximum rowid for an autoincrement calculation.
**
** This routine should be called when the top of the stack holds a
** new rowid that is about to be inserted.  If that new rowid is
** larger than the maximum rowid in the memId memory cell, then the
................................................................................
static void autoIncStep(Parse *pParse, int memId, int regRowid){
  if( memId>0 ){
    sqlite3VdbeAddOp2(pParse->pVdbe, OP_MemMax, memId, regRowid);
  }
}

/*
** This routine generates the code needed to write autoincrement

** maximum rowid values back into the sqlite_sequence register.
** Every statement that might do an INSERT into an autoincrement
** table (either directly or through triggers) needs to call this
** routine just before the "exit" code.
*/


void sqlite3AutoincrementEnd(Parse *pParse){
  AutoincInfo *p;




  Vdbe *v = pParse->pVdbe;
  sqlite3 *db = pParse->db;

  assert( v );
  for(p = pParse->pAinc; p; p = p->pNext){
    Db *pDb = &db->aDb[p->iDb];
    int j1, j2, j3, j4, j5;
    int iRec;
    int memId = p->regCtr;


    iRec = sqlite3GetTempReg(pParse);
    sqlite3OpenTable(pParse, 0, p->iDb, pDb->pSchema->pSeqTab, OP_OpenWrite);
    j1 = sqlite3VdbeAddOp1(v, OP_NotNull, memId+1);
    j2 = sqlite3VdbeAddOp0(v, OP_Rewind);
    j3 = sqlite3VdbeAddOp3(v, OP_Column, 0, 0, iRec);
    j4 = sqlite3VdbeAddOp3(v, OP_Eq, memId-1, 0, iRec);
    sqlite3VdbeAddOp2(v, OP_Next, 0, j3);
    sqlite3VdbeJumpHere(v, j2);
    sqlite3VdbeAddOp2(v, OP_NewRowid, 0, memId+1);
    j5 = sqlite3VdbeAddOp0(v, OP_Goto);
    sqlite3VdbeJumpHere(v, j4);
    sqlite3VdbeAddOp2(v, OP_Rowid, 0, memId+1);
    sqlite3VdbeJumpHere(v, j1);
    sqlite3VdbeJumpHere(v, j5);
    sqlite3VdbeAddOp3(v, OP_MakeRecord, memId-1, 2, iRec);
    sqlite3VdbeAddOp3(v, OP_Insert, 0, iRec, memId+1);
    sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
    sqlite3VdbeAddOp0(v, OP_Close);
    sqlite3ReleaseTempReg(pParse, iRec);
  }
}
#else
/*
** If SQLITE_OMIT_AUTOINCREMENT is defined, then the three routines
** above are all no-ops
*/
# define autoIncBegin(A,B,C) (0)
# define autoIncStep(A,B,C)

#endif /* SQLITE_OMIT_AUTOINCREMENT */


/* Forward declaration */
static int xferOptimization(
  Parse *pParse,        /* Parser context */
  Table *pDest,         /* The table we are inserting into */
................................................................................
  ** very fast and which reduce fragmentation of indices.
  **
  ** This is the 2nd template.
  */
  if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
    assert( !pTrigger );
    assert( pList==0 );
    goto insert_end;
  }
#endif /* SQLITE_OMIT_XFER_OPT */

  /* If this is an AUTOINCREMENT table, look up the sequence number in the
  ** sqlite_sequence table and store it in memory cell regAutoinc.
  */
  regAutoinc = autoIncBegin(pParse, iDb, pTab);
................................................................................
    /* Close all tables opened */
    sqlite3VdbeAddOp1(v, OP_Close, baseCur);
    for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
      sqlite3VdbeAddOp1(v, OP_Close, idx+baseCur);
    }
  }

insert_end:
  /* Update the sqlite_sequence table by storing the content of the

  ** maximum rowid counter values recorded while inserting into
  ** autoincrement tables.
  */

  if( pParse->nested==0 && pParse->trigStack==0 ){
    sqlite3AutoincrementEnd(pParse);
  }

  /*
  ** Return the number of rows inserted. If this routine is 
  ** generating code because of a call to sqlite3NestedParse(), do not
  ** invoke the callback function.
  */
  if( db->flags & SQLITE_CountRows && pParse->nested==0 && !pParse->trigStack ){
................................................................................
    assert( (pDest->tabFlags & TF_Autoincrement)==0 );
  }
  sqlite3VdbeAddOp2(v, OP_RowData, iSrc, regData);
  sqlite3VdbeAddOp3(v, OP_Insert, iDest, regData, regRowid);
  sqlite3VdbeChangeP5(v, OPFLAG_NCHANGE|OPFLAG_LASTROWID|OPFLAG_APPEND);
  sqlite3VdbeChangeP4(v, -1, pDest->zName, 0);
  sqlite3VdbeAddOp2(v, OP_Next, iSrc, addr1);

  for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
    for(pSrcIdx=pSrc->pIndex; ALWAYS(pSrcIdx); pSrcIdx=pSrcIdx->pNext){
      if( xferCompatibleIndex(pDestIdx, pSrcIdx) ) break;
    }
    assert( pSrcIdx );
    sqlite3VdbeAddOp2(v, OP_Close, iSrc, 0);
    sqlite3VdbeAddOp2(v, OP_Close, iDest, 0);

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
577
578
579
580
581
582
583

584
585
586
587
588
589
590
....
1922
1923
1924
1925
1926
1927
1928
















1929
1930
1931
1932
1933
1934
1935
....
1988
1989
1990
1991
1992
1993
1994

1995
1996
1997
1998
1999
2000
2001
....
2478
2479
2480
2481
2482
2483
2484







2485
2486
2487
2488
2489
2490
2491
**    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.886 2009/06/19 14:06:03 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
................................................................................
#define UNUSED_PARAMETER2(x,y) UNUSED_PARAMETER(x),UNUSED_PARAMETER(y)

/*
** Forward references to structures
*/
typedef struct AggInfo AggInfo;
typedef struct AuthContext AuthContext;

typedef struct Bitvec Bitvec;
typedef struct RowSet RowSet;
typedef struct CollSeq CollSeq;
typedef struct Column Column;
typedef struct Db Db;
typedef struct Schema Schema;
typedef struct Expr Expr;
................................................................................
  u8 eDest;         /* How to dispose of the results */
  u8 affinity;      /* Affinity used when eDest==SRT_Set */
  int iParm;        /* A parameter used by the eDest disposal method */
  int iMem;         /* Base register where results are written */
  int nMem;         /* Number of registers allocated */
};

















/*
** Size of the column cache
*/
#ifndef SQLITE_N_COLCACHE
# define SQLITE_N_COLCACHE 10
#endif

................................................................................
  int cookieValue[SQLITE_MAX_ATTACHED+2];  /* Values of cookies to verify */
#ifndef SQLITE_OMIT_SHARED_CACHE
  int nTableLock;        /* Number of locks in aTableLock */
  TableLock *aTableLock; /* Required table locks for shared-cache mode */
#endif
  int regRowid;        /* Register holding rowid of CREATE TABLE entry */
  int regRoot;         /* Register holding root page number for new objects */


  /* Above is constant between recursions.  Below is reset before and after
  ** each recursion */

  int nVar;            /* Number of '?' variables seen in the SQL so far */
  int nVarExpr;        /* Number of used slots in apVarExpr[] */
  int nVarExprAlloc;   /* Number of allocated slots in apVarExpr[] */
................................................................................
  int sqlite3ViewGetColumnNames(Parse*,Table*);
#else
# define sqlite3ViewGetColumnNames(A,B) 0
#endif

void sqlite3DropTable(Parse*, SrcList*, int, int);
void sqlite3DeleteTable(Table*);







void sqlite3Insert(Parse*, SrcList*, ExprList*, Select*, IdList*, int);
void *sqlite3ArrayAllocate(sqlite3*,void*,int,int,int*,int*,int*);
IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*);
int sqlite3IdListIndex(IdList*,const char*);
SrcList *sqlite3SrcListEnlarge(sqlite3*, SrcList*, int, int);
SrcList *sqlite3SrcListAppend(sqlite3*, SrcList*, Token*, Token*);
SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*,







|







 







>







 







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







 







>







 







>
>
>
>
>
>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
....
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
....
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
....
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
**    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.887 2009/06/23 20:28:54 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
................................................................................
#define UNUSED_PARAMETER2(x,y) UNUSED_PARAMETER(x),UNUSED_PARAMETER(y)

/*
** Forward references to structures
*/
typedef struct AggInfo AggInfo;
typedef struct AuthContext AuthContext;
typedef struct AutoincInfo AutoincInfo;
typedef struct Bitvec Bitvec;
typedef struct RowSet RowSet;
typedef struct CollSeq CollSeq;
typedef struct Column Column;
typedef struct Db Db;
typedef struct Schema Schema;
typedef struct Expr Expr;
................................................................................
  u8 eDest;         /* How to dispose of the results */
  u8 affinity;      /* Affinity used when eDest==SRT_Set */
  int iParm;        /* A parameter used by the eDest disposal method */
  int iMem;         /* Base register where results are written */
  int nMem;         /* Number of registers allocated */
};

/*
** During code generation of statements that do inserts into AUTOINCREMENT 
** tables, the following information is attached to the Table.u.autoInc.p
** pointer of each autoincrement table to record some side information that
** the code generator needs.  We have to keep per-table autoincrement
** information in case inserts are down within triggers.  Triggers do not
** normally coordinate their activities, but we do need to coordinate the
** loading and saving of autoincrement information.
*/
struct AutoincInfo {
  AutoincInfo *pNext;   /* Next info block in a list of them all */
  Table *pTab;          /* Table this info block refers to */
  int iDb;              /* Index in sqlite3.aDb[] of database holding pTab */
  int regCtr;           /* Memory register holding the rowid counter */
};

/*
** Size of the column cache
*/
#ifndef SQLITE_N_COLCACHE
# define SQLITE_N_COLCACHE 10
#endif

................................................................................
  int cookieValue[SQLITE_MAX_ATTACHED+2];  /* Values of cookies to verify */
#ifndef SQLITE_OMIT_SHARED_CACHE
  int nTableLock;        /* Number of locks in aTableLock */
  TableLock *aTableLock; /* Required table locks for shared-cache mode */
#endif
  int regRowid;        /* Register holding rowid of CREATE TABLE entry */
  int regRoot;         /* Register holding root page number for new objects */
  AutoincInfo *pAinc;  /* Information about AUTOINCREMENT counters */

  /* Above is constant between recursions.  Below is reset before and after
  ** each recursion */

  int nVar;            /* Number of '?' variables seen in the SQL so far */
  int nVarExpr;        /* Number of used slots in apVarExpr[] */
  int nVarExprAlloc;   /* Number of allocated slots in apVarExpr[] */
................................................................................
  int sqlite3ViewGetColumnNames(Parse*,Table*);
#else
# define sqlite3ViewGetColumnNames(A,B) 0
#endif

void sqlite3DropTable(Parse*, SrcList*, int, int);
void sqlite3DeleteTable(Table*);
#ifndef SQLITE_OMIT_AUTOINCREMENT
  void sqlite3AutoincrementBegin(Parse *pParse);
  void sqlite3AutoincrementEnd(Parse *pParse);
#else
# define sqlite3AutoincrementBegin(X)
# define sqlite3AutoincrementEnd(X)
#endif
void sqlite3Insert(Parse*, SrcList*, ExprList*, Select*, IdList*, int);
void *sqlite3ArrayAllocate(sqlite3*,void*,int,int,int*,int*,int*);
IdList *sqlite3IdListAppend(sqlite3*, IdList*, Token*);
int sqlite3IdListIndex(IdList*,const char*);
SrcList *sqlite3SrcListEnlarge(sqlite3*, SrcList*, int, int);
SrcList *sqlite3SrcListAppend(sqlite3*, SrcList*, Token*, Token*);
SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*,

Changes to src/tokenize.c.

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
514
515
516
517
518
519
520





521
522
523
524
525
526
527
528
529
530
*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.161 2009/06/17 01:17:13 drh Exp $
*/
#include "sqliteInt.h"
#include <stdlib.h>

/*
** The charMap() macro maps alphabetic characters into their
** lower-case ASCII equivalent.  On ASCII machines, this is just
................................................................................
    */
    sqlite3DeleteTable(pParse->pNewTable);
  }

  sqlite3DeleteTrigger(db, pParse->pNewTrigger);
  sqlite3DbFree(db, pParse->apVarExpr);
  sqlite3DbFree(db, pParse->aAlias);





  while( pParse->pZombieTab ){
    Table *p = pParse->pZombieTab;
    pParse->pZombieTab = p->pNextZombie;
    sqlite3DeleteTable(p);
  }
  if( nErr>0 && pParse->rc==SQLITE_OK ){
    pParse->rc = SQLITE_ERROR;
  }
  return nErr;
}







|







 







>
>
>
>
>










11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
...
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
*************************************************************************
** An tokenizer for SQL
**
** This file contains C code that splits an SQL input string up into
** individual tokens and sends those tokens one-by-one over to the
** parser for analysis.
**
** $Id: tokenize.c,v 1.162 2009/06/23 20:28:54 drh Exp $
*/
#include "sqliteInt.h"
#include <stdlib.h>

/*
** The charMap() macro maps alphabetic characters into their
** lower-case ASCII equivalent.  On ASCII machines, this is just
................................................................................
    */
    sqlite3DeleteTable(pParse->pNewTable);
  }

  sqlite3DeleteTrigger(db, pParse->pNewTrigger);
  sqlite3DbFree(db, pParse->apVarExpr);
  sqlite3DbFree(db, pParse->aAlias);
  while( pParse->pAinc ){
    AutoincInfo *p = pParse->pAinc;
    pParse->pAinc = p->pNext;
    sqlite3DbFree(db, p);
  }
  while( pParse->pZombieTab ){
    Table *p = pParse->pZombieTab;
    pParse->pZombieTab = p->pNextZombie;
    sqlite3DeleteTable(p);
  }
  if( nErr>0 && pParse->rc==SQLITE_OK ){
    pParse->rc = SQLITE_ERROR;
  }
  return nErr;
}

Changes to src/update.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
559
560
561
562
563
564
565








566
567
568
569
570
571
572
**    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.202 2009/05/28 01:00:55 drh Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
................................................................................
    }
  }
  sqlite3VdbeAddOp2(v, OP_Close, iCur, 0);
  if( pTrigger ){
    sqlite3VdbeAddOp2(v, OP_Close, newIdx, 0);
    sqlite3VdbeAddOp2(v, OP_Close, oldIdx, 0);
  }









  /*
  ** Return the number of rows that were changed. If this routine is 
  ** generating code because of a call to sqlite3NestedParse(), do not
  ** invoke the callback function.
  */
  if( db->flags & SQLITE_CountRows && !pParse->trigStack && pParse->nested==0 ){







|







 







>
>
>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
**    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.203 2009/06/23 20:28:54 drh Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_VIRTUALTABLE
/* Forward declaration */
static void updateVirtualTable(
  Parse *pParse,       /* The parsing context */
................................................................................
    }
  }
  sqlite3VdbeAddOp2(v, OP_Close, iCur, 0);
  if( pTrigger ){
    sqlite3VdbeAddOp2(v, OP_Close, newIdx, 0);
    sqlite3VdbeAddOp2(v, OP_Close, oldIdx, 0);
  }

  /* Update the sqlite_sequence table by storing the content of the
  ** maximum rowid counter values recorded while inserting into
  ** autoincrement tables.
  */
  if( pParse->nested==0 && pParse->trigStack==0 ){
    sqlite3AutoincrementEnd(pParse);
  }

  /*
  ** Return the number of rows that were changed. If this routine is 
  ** generating code because of a call to sqlite3NestedParse(), do not
  ** invoke the callback function.
  */
  if( db->flags & SQLITE_CountRows && !pParse->trigStack && pParse->nested==0 ){

Changes to test/autoinc.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
552
553
554
555
556
557
558


559











































































560
#    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 script is testing the AUTOINCREMENT features.
#
# $Id: autoinc.test,v 1.13 2008/08/11 18:44:58 drh Exp $
#

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

# If the library is not compiled with autoincrement support then
# skip all tests in this file.
................................................................................
    CREATE TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
    INSERT INTO t3 SELECT * FROM t2 WHERE y>1;

    SELECT * FROM sqlite_sequence WHERE name='t3';
  }
} {t3 0}















































































finish_test







|







 







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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
#    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 script is testing the AUTOINCREMENT features.
#
# $Id: autoinc.test,v 1.14 2009/06/23 20:28:54 drh Exp $
#

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

# If the library is not compiled with autoincrement support then
# skip all tests in this file.
................................................................................
    CREATE TABLE t3(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
    INSERT INTO t3 SELECT * FROM t2 WHERE y>1;

    SELECT * FROM sqlite_sequence WHERE name='t3';
  }
} {t3 0}

# Ticket #3928.  Make sure that triggers to not make extra slots in
# the SQLITE_SEQUENCE table.
#
do_test autoinc-3928.1 {
  db eval {
    CREATE TABLE t3928(a INTEGER PRIMARY KEY AUTOINCREMENT, b);
    CREATE TRIGGER t3928r1 BEFORE INSERT ON t3928 BEGIN
      INSERT INTO t3928(b) VALUES('before1');
      INSERT INTO t3928(b) VALUES('before2');
    END;
    CREATE TRIGGER t3928r2 AFTER INSERT ON t3928 BEGIN
      INSERT INTO t3928(b) VALUES('after1');
      INSERT INTO t3928(b) VALUES('after2');
    END;
    INSERT INTO t3928(b) VALUES('test');
    SELECT * FROM t3928 ORDER BY a;
  }
} {1 before1 2 after1 3 after2 4 before2 5 after1 6 after2 7 test 8 before1 9 before2 10 after1 11 before1 12 before2 13 after2}
do_test autoinc-3928.2 {
  db eval {
    SELECT * FROM sqlite_sequence WHERE name='t3928'
  }
} {t3928 13}

do_test autoinc-3928.3 {
  db eval {
    DROP TRIGGER t3928r1;
    DROP TRIGGER t3928r2;
    CREATE TRIGGER t3928r3 BEFORE UPDATE ON t3928 
      WHEN typeof(new.b)=='integer' BEGIN
         INSERT INTO t3928(b) VALUES('before-int-' || new.b);
    END;
    CREATE TRIGGER t3928r4 AFTER UPDATE ON t3928 
      WHEN typeof(new.b)=='integer' BEGIN
         INSERT INTO t3928(b) VALUES('after-int-' || new.b);
    END;
    DELETE FROM t3928 WHERE a!=1;
    UPDATE t3928 SET b=456 WHERE a=1;
    SELECT * FROM t3928 ORDER BY a;
  }
} {1 456 14 before-int-456 15 after-int-456}
do_test autoinc-3928.4 {
  db eval {
    SELECT * FROM sqlite_sequence WHERE name='t3928'
  }
} {t3928 15}

do_test autoinc-3928.5 {
  db eval {
    CREATE TABLE t3928b(x);
    INSERT INTO t3928b VALUES(100);
    INSERT INTO t3928b VALUES(200);
    INSERT INTO t3928b VALUES(300);
    DELETE FROM t3928;
    CREATE TABLE t3928c(y INTEGER PRIMARY KEY AUTOINCREMENT, z);
    CREATE TRIGGER t3928br1 BEFORE DELETE ON t3928b BEGIN
      INSERT INTO t3928(b) VALUES('before-del-'||old.x);
      INSERT INTO t3928c(z) VALUES('before-del-'||old.x);
    END;
    CREATE TRIGGER t3928br2 AFTER DELETE ON t3928b BEGIN
      INSERT INTO t3928(b) VALUES('after-del-'||old.x);
      INSERT INTO t3928c(z) VALUES('after-del-'||old.x);
    END;
    DELETE FROM t3928b;
    SELECT * FROM t3928 ORDER BY a;
  }
} {16 before-del-100 17 after-del-100 18 before-del-200 19 after-del-200 20 before-del-300 21 after-del-300}
do_test autoinc-3928.6 {
  db eval {
    SELECT * FROM t3928c ORDER BY y;
  }
} {1 before-del-100 2 after-del-100 3 before-del-200 4 after-del-200 5 before-del-300 6 after-del-300}
do_test autoinc-3928.7 {
  db eval {
    SELECT * FROM sqlite_sequence WHERE name LIKE 't3928%' ORDER BY name;
  }
} {t3928 21 t3928c 6}

finish_test