/ Check-in [cf1538d7]
Login

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

Overview
Comment:Added ON CONFLICT support to COPY. Updates to documentation. Bug fixes. (CVS 359)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:cf1538d71c9ce12d5e59f367e03642cbcaf6b717
User & Date: drh 2002-01-30 16:17:24
Context
2002-01-31
15:54
Change to five conflict resolution algorithms: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. This checkin is code only. Documentation and tests are still needed. Also, ABORT is not fully implemented. (CVS 360) check-in: d0e7cf4a user: drh tags: trunk
2002-01-30
16:17
Added ON CONFLICT support to COPY. Updates to documentation. Bug fixes. (CVS 359) check-in: cf1538d7 user: drh tags: trunk
04:32
Better testing of the ON CONFLICT logic. (CVS 358) check-in: 9bbddb8e user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to Makefile.template.

339
340
341
342
343
344
345



346
347
348
349
350
351
352
...
361
362
363
364
365
366
367
368

369
370
371
372
373
374
375

faq.html:	$(TOP)/www/faq.tcl
	tclsh $(TOP)/www/faq.tcl >faq.html

formatchng.html:	$(TOP)/www/formatchng.tcl
	tclsh $(TOP)/www/formatchng.tcl >formatchng.html




download.html:	$(TOP)/www/download.tcl
	tclsh $(TOP)/www/download.tcl >download.html


# Files to be published on the website.
#
DOC = \
................................................................................
  c_interface.html \
  crosscompile.html \
  mingw.html \
  tclsqlite.html \
  download.html \
  speed.html \
  faq.html \
  formatchng.html


doc:	$(DOC)
	mkdir -p doc
	mv $(DOC) doc

install:	sqlite libsqlite.a sqlite.h
	mv sqlite /usr/bin







>
>
>







 







|
>







339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
...
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379

faq.html:	$(TOP)/www/faq.tcl
	tclsh $(TOP)/www/faq.tcl >faq.html

formatchng.html:	$(TOP)/www/formatchng.tcl
	tclsh $(TOP)/www/formatchng.tcl >formatchng.html

conflict.html:	$(TOP)/www/conflict.tcl
	tclsh $(TOP)/www/conflict.tcl >conflict.html

download.html:	$(TOP)/www/download.tcl
	tclsh $(TOP)/www/download.tcl >download.html


# Files to be published on the website.
#
DOC = \
................................................................................
  c_interface.html \
  crosscompile.html \
  mingw.html \
  tclsqlite.html \
  download.html \
  speed.html \
  faq.html \
  formatchng.html \
  conflict.html

doc:	$(DOC)
	mkdir -p doc
	mv $(DOC) doc

install:	sqlite libsqlite.a sqlite.h
	mv sqlite /usr/bin

Changes to src/build.c.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
....
1328
1329
1330
1331
1332
1333
1334
1335

1336
1337
1338
1339
1340
1341
1342
....
1372
1373
1374
1375
1376
1377
1378



1379
1380
1381
1382
1383
1384
1385
....
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422






1423
1424
1425
1426
1427
1428
1429
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.68 2002/01/29 23:07:02 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
................................................................................
** file to fill this table with data.  File might be "stdin".  The optional
** delimiter string identifies the field separators.  The default is a tab.
*/
void sqliteCopy(
  Parse *pParse,       /* The parser context */
  Token *pTableName,   /* The name of the table into which we will insert */
  Token *pFilename,    /* The file from which to obtain information */
  Token *pDelimiter    /* Use this as the field delimiter */

){
  Table *pTab;
  char *zTab;
  int i, j;
  Vdbe *v;
  int addr, end;
  Index *pIdx;
................................................................................
    openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
    sqliteVdbeAddOp(v, openOp, 0, pTab->tnum);
    sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
    for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      sqliteVdbeAddOp(v, openOp, i, pIdx->tnum);
      sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
    }



    end = sqliteVdbeMakeLabel(v);
    addr = sqliteVdbeAddOp(v, OP_FileRead, pTab->nCol, end);
    if( pDelimiter ){
      sqliteVdbeChangeP3(v, addr, pDelimiter->z, pDelimiter->n);
      sqliteVdbeDequoteP3(v, addr);
    }else{
      sqliteVdbeChangeP3(v, addr, "\t", 1);
................................................................................
    }
    if( pTab->iPKey>=0 ){
      sqliteVdbeAddOp(v, OP_FileColumn, pTab->iPKey, 0);
      sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
    }else{
      sqliteVdbeAddOp(v, OP_NewRecno, 0, 0);
    }
    if( pTab->pIndex ){
      sqliteVdbeAddOp(v, OP_Dup, 0, 0);
    }
    for(i=0; i<pTab->nCol; i++){
      if( i==pTab->iPKey ){
        /* The integer primary key column is filled with NULL since its
        ** value is always pulled from the record number */
        sqliteVdbeAddOp(v, OP_String, 0, 0);
      }else{
        sqliteVdbeAddOp(v, OP_FileColumn, i, 0);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
    sqliteVdbeAddOp(v, OP_PutIntKey, 0, 0);
    for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      if( pIdx->pNext ){
        sqliteVdbeAddOp(v, OP_Dup, 0, 0);
      }
      for(j=0; j<pIdx->nColumn; j++){
        sqliteVdbeAddOp(v, OP_FileColumn, pIdx->aiColumn[j], 0);
      }
      sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
      sqliteVdbeAddOp(v, OP_IdxPut, i, pIdx->onError!=OE_None);
    }
    sqliteVdbeAddOp(v, OP_Goto, 0, addr);
    sqliteVdbeResolveLabel(v, end);
    sqliteVdbeAddOp(v, OP_Noop, 0, 0);
    if( (db->flags & SQLITE_InTrans)==0 ){
      sqliteVdbeAddOp(v, OP_Commit, 0, 0);
    }






  }
  
copy_cleanup:
  return;
}

/*







|







 







|
>







 







>
>
>







 







<
<
<









|
|
|
<
|
<
<
<
<
<
<







>
>
>
>
>
>







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
....
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
....
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
....
1390
1391
1392
1393
1394
1395
1396



1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408

1409






1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.69 2002/01/30 16:17:24 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
................................................................................
** file to fill this table with data.  File might be "stdin".  The optional
** delimiter string identifies the field separators.  The default is a tab.
*/
void sqliteCopy(
  Parse *pParse,       /* The parser context */
  Token *pTableName,   /* The name of the table into which we will insert */
  Token *pFilename,    /* The file from which to obtain information */
  Token *pDelimiter,   /* Use this as the field delimiter */
  int onError          /* What to do if a constraint fails */
){
  Table *pTab;
  char *zTab;
  int i, j;
  Vdbe *v;
  int addr, end;
  Index *pIdx;
................................................................................
    openOp = pTab->isTemp ? OP_OpenWrAux : OP_OpenWrite;
    sqliteVdbeAddOp(v, openOp, 0, pTab->tnum);
    sqliteVdbeChangeP3(v, -1, pTab->zName, P3_STATIC);
    for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      sqliteVdbeAddOp(v, openOp, i, pIdx->tnum);
      sqliteVdbeChangeP3(v, -1, pIdx->zName, P3_STATIC);
    }
    if( db->flags & SQLITE_CountRows ){
      sqliteVdbeAddOp(v, OP_Integer, 0, 0);  /* Initialize the row count */
    }
    end = sqliteVdbeMakeLabel(v);
    addr = sqliteVdbeAddOp(v, OP_FileRead, pTab->nCol, end);
    if( pDelimiter ){
      sqliteVdbeChangeP3(v, addr, pDelimiter->z, pDelimiter->n);
      sqliteVdbeDequoteP3(v, addr);
    }else{
      sqliteVdbeChangeP3(v, addr, "\t", 1);
................................................................................
    }
    if( pTab->iPKey>=0 ){
      sqliteVdbeAddOp(v, OP_FileColumn, pTab->iPKey, 0);
      sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
    }else{
      sqliteVdbeAddOp(v, OP_NewRecno, 0, 0);
    }



    for(i=0; i<pTab->nCol; i++){
      if( i==pTab->iPKey ){
        /* The integer primary key column is filled with NULL since its
        ** value is always pulled from the record number */
        sqliteVdbeAddOp(v, OP_String, 0, 0);
      }else{
        sqliteVdbeAddOp(v, OP_FileColumn, i, 0);
      }
    }
    sqliteGenerateConstraintChecks(pParse, pTab, 0, 0, 0, 0, onError, addr);
    sqliteCompleteInsertion(pParse, pTab, 0, 0, 0, 0);
    if( (db->flags & SQLITE_CountRows)!=0 ){

      sqliteVdbeAddOp(v, OP_AddImm, 1, 0);  /* Increment row count */






    }
    sqliteVdbeAddOp(v, OP_Goto, 0, addr);
    sqliteVdbeResolveLabel(v, end);
    sqliteVdbeAddOp(v, OP_Noop, 0, 0);
    if( (db->flags & SQLITE_InTrans)==0 ){
      sqliteVdbeAddOp(v, OP_Commit, 0, 0);
    }
    if( db->flags & SQLITE_CountRows ){
      sqliteVdbeAddOp(v, OP_ColumnCount, 1, 0);
      sqliteVdbeAddOp(v, OP_ColumnName, 0, 0);
      sqliteVdbeChangeP3(v, -1, "rows inserted", P3_STATIC);
      sqliteVdbeAddOp(v, OP_Callback, 1, 0);
    }
  }
  
copy_cleanup:
  return;
}

/*

Changes to src/insert.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
...
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
...
298
299
300
301
302
303
304
305


306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
...
369
370
371
372
373
374
375

376
377
378
379
380
381
382
383
384
385
386
387
388
389
390

391
392
393
394
395
396
397
398
399
400
401
402
...
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
...
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
...
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
...
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530

531
532
533
534
535
536
537
...
539
540
541
542
543
544
545
546
547
548
549
**    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.37 2002/01/30 04:32:01 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
    sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak);
    iCont = sqliteVdbeCurrentAddr(v);
  }

  /* Push the record number for the new entry onto the stack.  The
  ** record number is a randomly generate integer created by NewRecno
  ** except when the table has an INTEGER PRIMARY KEY column, in which
  ** case the record number is the same as that column.  May a copy
  ** because sqliteGenerateConstraintChecks() requires two copies of
  ** the record number.
  */
  if( keyColumn>=0 ){
    if( srcTab>=0 ){
      sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
    }else{
      sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
    }
    sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
  }else{
    sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
  }
  sqliteVdbeAddOp(v, OP_Dup, 0, 0);

  /* Push onto the stack, data for all columns of the new entry, beginning
  ** with the first column.
  */
  for(i=0; i<pTab->nCol; i++){
    if( i==pTab->iPKey ){
      /* The value of the INTEGER PRIMARY KEY column is always a NULL.
................................................................................
    }
  }

  /* Generate code to check constraints and generate index keys and
  ** do the insertion.
  */
  endOfLoop = sqliteVdbeMakeLabel(v);
  sqliteGenerateConstraintChecks(pParse, pTab, base, 0,1,onError,endOfLoop,0);
  sqliteCompleteInsertion(pParse, pTab, base, 0, 1);

  /* If inserting from a SELECT, keep a count of the number of
  ** rows inserted.
  */
  if( srcTab>=0 && (db->flags & SQLITE_CountRows)!=0 ){
    sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
  }
................................................................................

/*
** Generate code to do a constraint check prior to an INSERT or an UPDATE.
**
** When this routine is called, the stack contains (from bottom to top)
** the following values:
**
**    1.  The recno of the row to be updated before it is updated.


**
**    2.  The recno of the row after the update.  (This is usually the
**        same as (1) but can be different if an UPDATE changes an
**        INTEGER PRIMARY KEY column.)
**
**    3.  The data in the first column of the entry after the update.
**
**    i.  Data from middle columns...
**
**    N.  The data in the last column of the entry after the update.
**
** The old recno shown as entry (1) above is omitted if the recnoChng
** parameter is 0.  recnoChange is true if the record number is changing
** and false if not.
**
** The code generated by this routine pushes additional entries onto
** the stack which are the keys for new index entries for the new record.
** The order of index keys is the same as the order of the indices on
** the pTable->pIndex list.  A key is only created for index i if 
** aIdxUsed!=0 and aIdxUsed[i]!=0.
**
................................................................................
*/
void sqliteGenerateConstraintChecks(
  Parse *pParse,      /* The parser context */
  Table *pTab,        /* the table into which we are inserting */
  int base,           /* Index of a read/write cursor pointing at pTab */
  char *aIdxUsed,     /* Which indices are used.  NULL means all are used */
  int recnoChng,      /* True if the record number will change */

  int overrideError,  /* Override onError to this if not OE_Default */
  int ignoreDest,     /* Jump to this label on an OE_Ignore resolution */
  int isUpdate        /* True for UPDATE, False for INSERT */
){
  int i;
  Vdbe *v;
  int nCol;
  int onError;
  int addr;
  int extra;
  int iCur;
  Index *pIdx;
  int seenReplace = 0;
  int jumpInst;
  int contAddr;


  v = sqliteGetVdbe(pParse);
  assert( v!=0 );
  nCol = pTab->nCol;
  recnoChng = (recnoChng!=0);  /* Must be either 1 or 0 */

  /* Test all NOT NULL constraints.
  */
  for(i=0; i<nCol; i++){
    if( i==pTab->iPKey ){
      /* Fix me: Make sure the INTEGER PRIMARY KEY is not NULL. */
      continue;
................................................................................
    addr = sqliteVdbeAddOp(v, OP_NotNull, 0, 0);
    switch( onError ){
      case OE_Abort: {
        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, 0);
        break;
      }
      case OE_Ignore: {
        sqliteVdbeAddOp(v, OP_Pop, nCol+1+recnoChng, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;
      }
      case OE_Replace: {
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
        sqliteVdbeAddOp(v, OP_Push, nCol-i, 0);
................................................................................
  }

  /* Test all CHECK constraints
  */

  /* Test all UNIQUE constraints.  Add index records as we go.
  */
  if( recnoChng && pTab->iPKey>=0 && pTab->keyConf!=OE_Replace 
      && overrideError!=OE_Replace ){
    sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
    jumpInst = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
    onError = pTab->keyConf;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }
    switch( onError ){
      case OE_Abort: {
        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, 0);
        break;
      }
      case OE_Ignore: {
        sqliteVdbeAddOp(v, OP_Pop, nCol+2, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;
      }
      default: assert(0);
    }
    contAddr = sqliteVdbeCurrentAddr(v);
    sqliteVdbeChangeP2(v, jumpInst, contAddr);
................................................................................
    }
    sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
    onError = pIdx->onError;
    if( onError==OE_None ) continue;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }
    sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+recnoChng, 1);
    jumpInst = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
    switch( onError ){
      case OE_Abort: {
        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, 0);
        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        sqliteVdbeAddOp(v, OP_Pop, nCol+extra+2+recnoChng, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;
      }
      case OE_Replace: {
        sqliteGenerateRowDelete(v, pTab, base);
        if( isUpdate ){
          sqliteVdbeAddOp(v, OP_Dup, nCol+extra+1+recnoChng, 1);
          sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
        }
        seenReplace = 1;
        break;
      }
      default: assert(0);
    }
................................................................................
/*
** This routine generates code to finish the INSERT or UPDATE operation
** that was started by a prior call to sqliteGenerateConstraintChecks.
** The stack must contain keys for all active indices followed by data
** and the recno for the new entry.  This routine creates the new
** entries in all indices and in the main table.
**
** The arguments to this routine should be the same as the first five
** arguments to sqliteGenerateConstraintChecks.
*/
void sqliteCompleteInsertion(
  Parse *pParse,      /* The parser context */
  Table *pTab,        /* the table into which we are inserting */
  int base,           /* Index of a read/write cursor pointing at pTab */
  char *aIdxUsed,     /* Which indices are used.  NULL means all are used */
  int recnoChng       /* True if the record number changed */

){
  int i;
  Vdbe *v;
  int nIdx;
  Index *pIdx;

  v = sqliteGetVdbe(pParse);
................................................................................
  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
  for(i=nIdx-1; i>=0; i--){
    if( aIdxUsed && aIdxUsed[i]==0 ) continue;
    sqliteVdbeAddOp(v, OP_IdxPut, base+i+1, 0);
  }
  sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
  sqliteVdbeAddOp(v, OP_PutIntKey, base, 0);
  if( recnoChng ){
    sqliteVdbeAddOp(v, OP_Pop, 1, 0);
  }
}







|







 







|
<
<











<







 







|
|







 







|
>
>

|
<
<







|
|
|







 







>

|
<












>




<







 







|







 







|













|







 







|








|






|







 







|







|
>







 







|



8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
...
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
...
295
296
297
298
299
300
301
302
303
304
305
306


307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
...
366
367
368
369
370
371
372
373
374
375

376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392

393
394
395
396
397
398
399
...
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
...
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
...
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
...
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
...
537
538
539
540
541
542
543
544
545
546
547
**    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.38 2002/01/30 16:17:24 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
    sqliteVdbeAddOp(v, OP_Rewind, srcTab, iBreak);
    iCont = sqliteVdbeCurrentAddr(v);
  }

  /* Push the record number for the new entry onto the stack.  The
  ** record number is a randomly generate integer created by NewRecno
  ** except when the table has an INTEGER PRIMARY KEY column, in which
  ** case the record number is the same as that column. 


  */
  if( keyColumn>=0 ){
    if( srcTab>=0 ){
      sqliteVdbeAddOp(v, OP_Column, srcTab, keyColumn);
    }else{
      sqliteExprCode(pParse, pList->a[keyColumn].pExpr);
    }
    sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
  }else{
    sqliteVdbeAddOp(v, OP_NewRecno, base, 0);
  }


  /* Push onto the stack, data for all columns of the new entry, beginning
  ** with the first column.
  */
  for(i=0; i<pTab->nCol; i++){
    if( i==pTab->iPKey ){
      /* The value of the INTEGER PRIMARY KEY column is always a NULL.
................................................................................
    }
  }

  /* Generate code to check constraints and generate index keys and
  ** do the insertion.
  */
  endOfLoop = sqliteVdbeMakeLabel(v);
  sqliteGenerateConstraintChecks(pParse, pTab, base, 0,0,0, onError, endOfLoop);
  sqliteCompleteInsertion(pParse, pTab, base, 0,0,0);

  /* If inserting from a SELECT, keep a count of the number of
  ** rows inserted.
  */
  if( srcTab>=0 && (db->flags & SQLITE_CountRows)!=0 ){
    sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
  }
................................................................................

/*
** Generate code to do a constraint check prior to an INSERT or an UPDATE.
**
** When this routine is called, the stack contains (from bottom to top)
** the following values:
**
**    1.  The recno of the row to be updated before it is updated.  This
**        value is omitted unless we are doing an UPDATE that involves a
**        change to the record number.
**
**    2.  The recno of the row after the update.


**
**    3.  The data in the first column of the entry after the update.
**
**    i.  Data from middle columns...
**
**    N.  The data in the last column of the entry after the update.
**
** The old recno shown as entry (1) above is omitted unless both isUpdate
** and recnoChng are both 1.  isUpdate is true for UPDATEs and false for
** INSERTs and recnoChng is ture if the record number is being changed.
**
** The code generated by this routine pushes additional entries onto
** the stack which are the keys for new index entries for the new record.
** The order of index keys is the same as the order of the indices on
** the pTable->pIndex list.  A key is only created for index i if 
** aIdxUsed!=0 and aIdxUsed[i]!=0.
**
................................................................................
*/
void sqliteGenerateConstraintChecks(
  Parse *pParse,      /* The parser context */
  Table *pTab,        /* the table into which we are inserting */
  int base,           /* Index of a read/write cursor pointing at pTab */
  char *aIdxUsed,     /* Which indices are used.  NULL means all are used */
  int recnoChng,      /* True if the record number will change */
  int isUpdate,       /* True for UPDATE, False for INSERT */
  int overrideError,  /* Override onError to this if not OE_Default */
  int ignoreDest      /* Jump to this label on an OE_Ignore resolution */

){
  int i;
  Vdbe *v;
  int nCol;
  int onError;
  int addr;
  int extra;
  int iCur;
  Index *pIdx;
  int seenReplace = 0;
  int jumpInst;
  int contAddr;
  int hasTwoRecnos = (isUpdate && recnoChng);

  v = sqliteGetVdbe(pParse);
  assert( v!=0 );
  nCol = pTab->nCol;


  /* Test all NOT NULL constraints.
  */
  for(i=0; i<nCol; i++){
    if( i==pTab->iPKey ){
      /* Fix me: Make sure the INTEGER PRIMARY KEY is not NULL. */
      continue;
................................................................................
    addr = sqliteVdbeAddOp(v, OP_NotNull, 0, 0);
    switch( onError ){
      case OE_Abort: {
        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, 0);
        break;
      }
      case OE_Ignore: {
        sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;
      }
      case OE_Replace: {
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        sqliteVdbeChangeP3(v, -1, pTab->aCol[i].zDflt, P3_STATIC);
        sqliteVdbeAddOp(v, OP_Push, nCol-i, 0);
................................................................................
  }

  /* Test all CHECK constraints
  */

  /* Test all UNIQUE constraints.  Add index records as we go.
  */
  if( (recnoChng || !isUpdate) && pTab->iPKey>=0 && pTab->keyConf!=OE_Replace 
      && overrideError!=OE_Replace ){
    sqliteVdbeAddOp(v, OP_Dup, nCol, 1);
    jumpInst = sqliteVdbeAddOp(v, OP_NotExists, base, 0);
    onError = pTab->keyConf;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }
    switch( onError ){
      case OE_Abort: {
        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, 0);
        break;
      }
      case OE_Ignore: {
        sqliteVdbeAddOp(v, OP_Pop, nCol+1+hasTwoRecnos, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;
      }
      default: assert(0);
    }
    contAddr = sqliteVdbeCurrentAddr(v);
    sqliteVdbeChangeP2(v, jumpInst, contAddr);
................................................................................
    }
    sqliteVdbeAddOp(v, OP_MakeIdxKey, pIdx->nColumn, 0);
    onError = pIdx->onError;
    if( onError==OE_None ) continue;
    if( overrideError!=OE_Default ){
      onError = overrideError;
    }
    sqliteVdbeAddOp(v, OP_Dup, extra+nCol+1+hasTwoRecnos, 1);
    jumpInst = sqliteVdbeAddOp(v, OP_IsUnique, base+iCur+1, 0);
    switch( onError ){
      case OE_Abort: {
        sqliteVdbeAddOp(v, OP_Halt, SQLITE_CONSTRAINT, 0);
        break;
      }
      case OE_Ignore: {
        assert( seenReplace==0 );
        sqliteVdbeAddOp(v, OP_Pop, nCol+extra+2+hasTwoRecnos, 0);
        sqliteVdbeAddOp(v, OP_Goto, 0, ignoreDest);
        break;
      }
      case OE_Replace: {
        sqliteGenerateRowDelete(v, pTab, base);
        if( isUpdate ){
          sqliteVdbeAddOp(v, OP_Dup, nCol+extra+1+hasTwoRecnos, 1);
          sqliteVdbeAddOp(v, OP_MoveTo, base, 0);
        }
        seenReplace = 1;
        break;
      }
      default: assert(0);
    }
................................................................................
/*
** This routine generates code to finish the INSERT or UPDATE operation
** that was started by a prior call to sqliteGenerateConstraintChecks.
** The stack must contain keys for all active indices followed by data
** and the recno for the new entry.  This routine creates the new
** entries in all indices and in the main table.
**
** The arguments to this routine should be the same as the first six
** arguments to sqliteGenerateConstraintChecks.
*/
void sqliteCompleteInsertion(
  Parse *pParse,      /* The parser context */
  Table *pTab,        /* the table into which we are inserting */
  int base,           /* Index of a read/write cursor pointing at pTab */
  char *aIdxUsed,     /* Which indices are used.  NULL means all are used */
  int recnoChng,      /* True if the record number will change */
  int isUpdate        /* True for UPDATE, False for INSERT */
){
  int i;
  Vdbe *v;
  int nIdx;
  Index *pIdx;

  v = sqliteGetVdbe(pParse);
................................................................................
  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, nIdx++){}
  for(i=nIdx-1; i>=0; i--){
    if( aIdxUsed && aIdxUsed[i]==0 ) continue;
    sqliteVdbeAddOp(v, OP_IdxPut, base+i+1, 0);
  }
  sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0);
  sqliteVdbeAddOp(v, OP_PutIntKey, base, 0);
  if( isUpdate && recnoChng ){
    sqliteVdbeAddOp(v, OP_Pop, 1, 0);
  }
}

Changes to src/parse.y.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.43 2002/01/29 18:41:25 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
//

cmd ::= DROP INDEX ids(X).      {sqliteDropIndex(pParse, &X);}


///////////////////////////// The COPY command ///////////////////////////////
//
cmd ::= COPY ids(X) FROM ids(Y) USING DELIMITERS STRING(Z).
    {sqliteCopy(pParse,&X,&Y,&Z);}
cmd ::= COPY ids(X) FROM ids(Y).
    {sqliteCopy(pParse,&X,&Y,0);}

///////////////////////////// The VACUUM command /////////////////////////////
//
cmd ::= VACUUM.                {sqliteVacuum(pParse,0);}
cmd ::= VACUUM ids(X).         {sqliteVacuum(pParse,&X);}

///////////////////////////// The PRAGMA command /////////////////////////////







|







 







|
|
|
|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.44 2002/01/30 16:17:24 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
//

cmd ::= DROP INDEX ids(X).      {sqliteDropIndex(pParse, &X);}


///////////////////////////// The COPY command ///////////////////////////////
//
cmd ::= COPY onconf_u(R) ids(X) FROM ids(Y) USING DELIMITERS STRING(Z).
    {sqliteCopy(pParse,&X,&Y,&Z,R);}
cmd ::= COPY onconf_u(R) ids(X) FROM ids(Y).
    {sqliteCopy(pParse,&X,&Y,0,R);}

///////////////////////////// The VACUUM command /////////////////////////////
//
cmd ::= VACUUM.                {sqliteVacuum(pParse,0);}
cmd ::= VACUUM ids(X).         {sqliteVacuum(pParse,&X);}

///////////////////////////// The PRAGMA command /////////////////////////////

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
...
546
547
548
549
550
551
552
553
**    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.81 2002/01/29 23:07:02 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
void sqliteWhereEnd(WhereInfo*);
void sqliteExprCode(Parse*, Expr*);
void sqliteExprIfTrue(Parse*, Expr*, int);
void sqliteExprIfFalse(Parse*, Expr*, int);
Table *sqliteFindTable(sqlite*,char*);
Index *sqliteFindIndex(sqlite*,char*);
void sqliteUnlinkAndDeleteIndex(sqlite*,Index*);
void sqliteCopy(Parse*, Token*, Token*, Token*);
void sqliteVacuum(Parse*, Token*);
int sqliteGlobCompare(const unsigned char*,const unsigned char*);
int sqliteLikeCompare(const unsigned char*,const unsigned char*);
char *sqliteTableNameFromToken(Token*);
int sqliteExprCheck(Parse*, Expr*, int, int*);
int sqliteExprCompare(Expr*, Expr*);
int sqliteFuncId(Token*);
................................................................................
void sqliteCommitTransaction(Parse*);
void sqliteRollbackTransaction(Parse*);
char *sqlite_mprintf(const char *, ...);
int sqliteExprIsConstant(Expr*);
void sqliteGenerateRowDelete(Vdbe*, Table*, int);
void sqliteGenerateRowIndexDelete(Vdbe*, Table*, int, char*);
void sqliteGenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int);
void sqliteCompleteInsertion(Parse*, Table*, int, char*, int);







|







 







|







 







|
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
...
546
547
548
549
550
551
552
553
**    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.82 2002/01/30 16:17:24 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
void sqliteWhereEnd(WhereInfo*);
void sqliteExprCode(Parse*, Expr*);
void sqliteExprIfTrue(Parse*, Expr*, int);
void sqliteExprIfFalse(Parse*, Expr*, int);
Table *sqliteFindTable(sqlite*,char*);
Index *sqliteFindIndex(sqlite*,char*);
void sqliteUnlinkAndDeleteIndex(sqlite*,Index*);
void sqliteCopy(Parse*, Token*, Token*, Token*, int);
void sqliteVacuum(Parse*, Token*);
int sqliteGlobCompare(const unsigned char*,const unsigned char*);
int sqliteLikeCompare(const unsigned char*,const unsigned char*);
char *sqliteTableNameFromToken(Token*);
int sqliteExprCheck(Parse*, Expr*, int, int*);
int sqliteExprCompare(Expr*, Expr*);
int sqliteFuncId(Token*);
................................................................................
void sqliteCommitTransaction(Parse*);
void sqliteRollbackTransaction(Parse*);
char *sqlite_mprintf(const char *, ...);
int sqliteExprIsConstant(Expr*);
void sqliteGenerateRowDelete(Vdbe*, Table*, int);
void sqliteGenerateRowIndexDelete(Vdbe*, Table*, int, char*);
void sqliteGenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int);
void sqliteCompleteInsertion(Parse*, Table*, int, char*, int, int);

Changes to src/update.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
249
250
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
**    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.30 2002/01/29 23:07:02 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(
................................................................................
    }else{
      sqliteExprCode(pParse, pChanges->a[j].pExpr);
    }
  }

  /* Do constraint checks
  */
  sqliteGenerateConstraintChecks(pParse, pTab, base, aIdxUsed, chngRecno,
                                 onError, addr,1);

  /* Delete the old indices for the current record.
  */
  sqliteGenerateRowIndexDelete(v, pTab, base, aIdxUsed);

  /* If changing the record number, delete the old record.
  */
  if( chngRecno ){
    sqliteVdbeAddOp(v, OP_Delete, 0, 0);
  }

  /* Create the new index entries and the new record.
  */
  sqliteCompleteInsertion(pParse, pTab, base, aIdxUsed, chngRecno);

  /* Increment the row counter 
  */
  if( db->flags & SQLITE_CountRows ){
    sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
  }








|







 







|
|













|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
249
250
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
**    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.31 2002/01/30 16:17:24 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(
................................................................................
    }else{
      sqliteExprCode(pParse, pChanges->a[j].pExpr);
    }
  }

  /* Do constraint checks
  */
  sqliteGenerateConstraintChecks(pParse, pTab, base, aIdxUsed, chngRecno, 1,
                                 onError, addr);

  /* Delete the old indices for the current record.
  */
  sqliteGenerateRowIndexDelete(v, pTab, base, aIdxUsed);

  /* If changing the record number, delete the old record.
  */
  if( chngRecno ){
    sqliteVdbeAddOp(v, OP_Delete, 0, 0);
  }

  /* Create the new index entries and the new record.
  */
  sqliteCompleteInsertion(pParse, pTab, base, aIdxUsed, chngRecno, 1);

  /* Increment the row counter 
  */
  if( db->flags & SQLITE_CountRows ){
    sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
  }

Changes to src/vdbe.c.

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
3820
3821
3822
3823
3824
3825
3826
3827
3828



3829
3830
3831
3832
3833
3834
3835
....
3854
3855
3856
3857
3858
3859
3860
3861
3862
3863




3864
3865




3866
3867
3868
3869
3870
3871
3872
....
3875
3876
3877
3878
3879
3880
3881







3882
3883
3884
3885
3886
3887
3888
....
3894
3895
3896
3897
3898
3899
3900
3901
3902
3903
3904
3905
3906
3907
3908
....
3919
3920
3921
3922
3923
3924
3925
3926
3927

3928
3929
3930





3931
3932
3933
3934
3935
3936
3937
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.111 2002/01/30 00:54:56 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
/* Opcode: FileRead P1 P2 P3
**
** Read a single line of input from the open file (the file opened using
** FileOpen).  If we reach end-of-file, jump immediately to P2.  If
** we are able to get another line, split the line apart using P3 as
** a delimiter.  There should be P1 fields.  If the input line contains
** more than P1 fields, ignore the excess.  If the input line contains
** fewer than P1 fields, assume the remaining fields contain an
** empty strings.



*/
case OP_FileRead: {
  int n, eol, nField, i, c, nDelim;
  char *zDelim, *z;
  if( p->pFile==0 ) goto fileread_jump;
  nField = pOp->p1;
  if( nField<=0 ) goto fileread_jump;
................................................................................
      }
      p->zLine = zLine;
    }
    if( fgets(&p->zLine[n], p->nLineAlloc-n, p->pFile)==0 ){
      eol = 1;
      p->zLine[n] = 0;
    }else{
      while( p->zLine[n] ){ n++; }
      if( n>0 && p->zLine[n-1]=='\n' ){
        n--;




        p->zLine[n] = 0;
        eol = 1;




      }
    }
  }
  if( n==0 ) goto fileread_jump;
  z = p->zLine;
  if( z[0]=='\\' && z[1]=='.' && z[2]==0 ){
    goto fileread_jump;
................................................................................
  if( zDelim==0 ) zDelim = "\t";
  c = zDelim[0];
  nDelim = strlen(zDelim);
  p->azField[0] = z;
  for(i=1; *z!=0 && i<=nField; i++){
    int from, to;
    from = to = 0;







    while( z[from] ){
      if( z[from]=='\\' && z[from+1]!=0 ){
        z[to++] = z[from+1];
        from += 2;
        continue;
      }
      if( z[from]==c && strncmp(&z[from],zDelim,nDelim)==0 ) break;
................................................................................
      if( i<nField ) p->azField[i] = z;
    }else{
      z[to] = 0;
      z = "";
    }
  }
  while( i<nField ){
    p->azField[i++] = "";
  }
  break;

  /* If we reach end-of-file, or if anything goes wrong, jump here.
  ** This code will cause a jump to P2 */
fileread_jump:
  pc = pOp->p2 - 1;
................................................................................
  char *z;
  VERIFY( if( NeedStack(p, p->tos+1) ) goto no_mem; )
  if( VERIFY( i>=0 && i<p->nField && ) p->azField ){
    z = p->azField[i];
  }else{
    z = 0;
  }
  if( z==0 ) z = "";
  p->tos++;

  aStack[p->tos].n = strlen(z) + 1;
  zStack[p->tos] = z;
  aStack[p->tos].flags = STK_Str;





  break;
}

/* Opcode: MemStore P1 P2 *
**
** Write the top of the stack into memory location P1.
** P1 should be a small integer since space is allocated







|







 







|
|
>
>
>







 







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







 







>
>
>
>
>
>
>







 







|







 







<

>
|
|
|
>
>
>
>
>







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
....
3820
3821
3822
3823
3824
3825
3826
3827
3828
3829
3830
3831
3832
3833
3834
3835
3836
3837
3838
....
3857
3858
3859
3860
3861
3862
3863
3864
3865

3866
3867
3868
3869
3870
3871
3872
3873
3874
3875
3876
3877
3878
3879
3880
3881
3882
....
3885
3886
3887
3888
3889
3890
3891
3892
3893
3894
3895
3896
3897
3898
3899
3900
3901
3902
3903
3904
3905
....
3911
3912
3913
3914
3915
3916
3917
3918
3919
3920
3921
3922
3923
3924
3925
....
3936
3937
3938
3939
3940
3941
3942

3943
3944
3945
3946
3947
3948
3949
3950
3951
3952
3953
3954
3955
3956
3957
3958
3959
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.112 2002/01/30 16:17:24 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
................................................................................
/* Opcode: FileRead P1 P2 P3
**
** Read a single line of input from the open file (the file opened using
** FileOpen).  If we reach end-of-file, jump immediately to P2.  If
** we are able to get another line, split the line apart using P3 as
** a delimiter.  There should be P1 fields.  If the input line contains
** more than P1 fields, ignore the excess.  If the input line contains
** fewer than P1 fields, assume the remaining fields contain NULLs.
**
** Input ends if a line consists of just "\.".  A field containing only
** "\N" is a null field.  The backslash \ character can be used be used
** to escape newlines or the delimiter.
*/
case OP_FileRead: {
  int n, eol, nField, i, c, nDelim;
  char *zDelim, *z;
  if( p->pFile==0 ) goto fileread_jump;
  nField = pOp->p1;
  if( nField<=0 ) goto fileread_jump;
................................................................................
      }
      p->zLine = zLine;
    }
    if( fgets(&p->zLine[n], p->nLineAlloc-n, p->pFile)==0 ){
      eol = 1;
      p->zLine[n] = 0;
    }else{
      int c;
      while( (c = p->zLine[n])!=0 ){

        if( c=='\\' ){
          if( p->zLine[n+1]==0 ) break;
          n += 2;
        }else if( c=='\n' ){
          p->zLine[n] = 0;
          eol = 1;
          break;
        }else{
          n++;
        }
      }
    }
  }
  if( n==0 ) goto fileread_jump;
  z = p->zLine;
  if( z[0]=='\\' && z[1]=='.' && z[2]==0 ){
    goto fileread_jump;
................................................................................
  if( zDelim==0 ) zDelim = "\t";
  c = zDelim[0];
  nDelim = strlen(zDelim);
  p->azField[0] = z;
  for(i=1; *z!=0 && i<=nField; i++){
    int from, to;
    from = to = 0;
    if( z[0]=='\\' && z[1]=='N' 
       && (z[2]==0 || strncmp(&z[2],zDelim,nDelim)==0) ){
      if( i<=nField ) p->azField[i-1] = 0;
      z += 2 + nDelim;
      if( i<nField ) p->azField[i] = z;
      continue;
    }
    while( z[from] ){
      if( z[from]=='\\' && z[from+1]!=0 ){
        z[to++] = z[from+1];
        from += 2;
        continue;
      }
      if( z[from]==c && strncmp(&z[from],zDelim,nDelim)==0 ) break;
................................................................................
      if( i<nField ) p->azField[i] = z;
    }else{
      z[to] = 0;
      z = "";
    }
  }
  while( i<nField ){
    p->azField[i++] = 0;
  }
  break;

  /* If we reach end-of-file, or if anything goes wrong, jump here.
  ** This code will cause a jump to P2 */
fileread_jump:
  pc = pOp->p2 - 1;
................................................................................
  char *z;
  VERIFY( if( NeedStack(p, p->tos+1) ) goto no_mem; )
  if( VERIFY( i>=0 && i<p->nField && ) p->azField ){
    z = p->azField[i];
  }else{
    z = 0;
  }

  p->tos++;
  if( z ){
    aStack[p->tos].n = strlen(z) + 1;
    zStack[p->tos] = z;
    aStack[p->tos].flags = STK_Str;
  }else{
    aStack[p->tos].n = 0;
    zStack[p->tos] = 0;
    aStack[p->tos].flags = STK_Null;
  }
  break;
}

/* Opcode: MemStore P1 P2 *
**
** Write the top of the stack into memory location P1.
** P1 should be a small integer since space is allocated

Changes to test/conflict.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.2 2002/01/30 04:32:01 drh Exp $

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

# Create a table with three fields, two of which must be
# UNIQUE.
#
................................................................................
do_test conflict-2.2 {
  catchsql {
    INSERT INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}
do_test conflict-2.3 {
  catchsql {
    INSERT ON CONFLICT IGNORE INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 3}
do_test conflict-2.4 {
  catchsql {
    INSERT ON CONFLICT REPLACE INTO t1 VALUES(1,2,4);







|







 







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the conflict resolution extension
# to SQLite.
#
# $Id: conflict.test,v 1.3 2002/01/30 16:17:25 drh Exp $

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

# Create a table with three fields, two of which must be
# UNIQUE.
#
................................................................................
do_test conflict-2.2 {
  catchsql {
    INSERT INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {1 {constraint failed}}
do_test conflict-2.3 {
  catchsql { 
    INSERT ON CONFLICT IGNORE INTO t1 VALUES(1,2,4);
    SELECT c FROM t1 ORDER BY c;
  }
} {0 3}
do_test conflict-2.4 {
  catchsql {
    INSERT ON CONFLICT REPLACE INTO t1 VALUES(1,2,4);

Changes to test/copy.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
#    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 COPY statement.
#
# $Id: copy.test,v 1.6 2001/09/16 00:13:28 drh Exp $

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

# Create a file of data from which to copy.
#
set f [open data1.txt w]
................................................................................
  execsql {CREATE TABLE test2(a int, x text)}
  set f [open data21.txt w]
  puts $f "123\t$x"
  close $f
  execsql {COPY test2 FROM 'data21.txt'}
  execsql {SELECT x from test2}
} $x


# Test the escape character mechanism
#
do_test copy-3.1 {
  set fd [open data6.txt w]
  puts $fd "hello\\\tworld\t1"
  puts $fd "hello\tworld\\\t2"
................................................................................
  close $fd
  execsql {
    CREATE TABLE t1(a text, b text);
    COPY t1 FROM 'data6.txt';
    SELECT * FROM t1 ORDER BY a;
  }
} {hello {world	2} {hello	world} 1}







































































# Cleanup 
#
file delete -force data1.txt data2.txt data3.txt data4.txt data5.txt data21.txt

finish_test







|







 







>







 







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


|


7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
#    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 COPY statement.
#
# $Id: copy.test,v 1.7 2002/01/30 16:17:25 drh Exp $

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

# Create a file of data from which to copy.
#
set f [open data1.txt w]
................................................................................
  execsql {CREATE TABLE test2(a int, x text)}
  set f [open data21.txt w]
  puts $f "123\t$x"
  close $f
  execsql {COPY test2 FROM 'data21.txt'}
  execsql {SELECT x from test2}
} $x
file delete -force data21.txt

# Test the escape character mechanism
#
do_test copy-3.1 {
  set fd [open data6.txt w]
  puts $fd "hello\\\tworld\t1"
  puts $fd "hello\tworld\\\t2"
................................................................................
  close $fd
  execsql {
    CREATE TABLE t1(a text, b text);
    COPY t1 FROM 'data6.txt';
    SELECT * FROM t1 ORDER BY a;
  }
} {hello {world	2} {hello	world} 1}
do_test copy-3.2 {
  set fd [open data6.txt w]
  puts $fd "1\thello\\\nworld"
  puts $fd "2\thello world"
  close $fd
  execsql {
    DELETE FROM t1;
    COPY t1 FROM 'data6.txt';
    SELECT * FROM t1 ORDER BY a;
  }
} {1 {hello
world} 2 {hello world}}

# Test the embedded NULL logic.
#
do_test copy-4.1 {
  set fd [open data6.txt w]
  puts $fd "1\t\\N"
  puts $fd "\\N\thello world"
  close $fd
  execsql {
    DELETE FROM t1;
    COPY t1 FROM 'data6.txt';
    SELECT * FROM t1 WHERE a IS NULL;
  }
} {{} {hello world}}
do_test copy-4.2 {
  execsql {
    SELECT * FROM t1 WHERE b IS NULL;
  }
} {1 {}}

# Test the conflict resolution logic for COPY
#
do_test copy-5.1 {
  execsql {
    DROP TABLE t1;
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
    COPY t1 FROM 'data5.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {11 22 33 22 33 11}
do_test copy-5.2 {
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {1 {constraint failed}}
do_test copy-5.3 {
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY ON CONFLICT IGNORE t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {0 {11 22 33 22 33 11}}
do_test copy-5.4 {
  set fd [open data6.txt w]
  puts $fd "33|22|44"
  close $fd
  catchsql {
    COPY ON CONFLICT REPLACE t1 FROM 'data6.txt' USING DELIMITERS '|';
    SELECT * FROM t1;
  }
} {0 {22 33 11 33 22 44}}
 

# Cleanup 
#
file delete -force data1.txt data2.txt data3.txt data4.txt data5.txt data6.txt

finish_test

Changes to www/changes.tcl.

12
13
14
15
16
17
18









19
20
21
22
23
24
25
}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}










chng {2002 Jan 28 (2.2.5)} {
<li>Important bug fix: the IN operator was not working if either the
    left-hand or right-hand side was derived from an INTEGER PRIMARY KEY.</li>
<li>Do not escape the backslash '\' character in the output of the
    <b>sqlite</b> command-line access program.</li>
}







>
>
>
>
>
>
>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
}


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Jan 30 (2.3.0 beta)} {
<li>Added the ability to resolve constraint conflicts is ways other than
    an abort and rollback.  See the documentation on the "ON CONFLICT"
    clause for details.</li>
<li>NOT NULL constraints are honored.</li>
<li>The COPY command puts NULLs in columns whose data is '\N'.</li>
<li>In the COPY command, backslash can now be used to escape a newline.</li>
}

chng {2002 Jan 28 (2.2.5)} {
<li>Important bug fix: the IN operator was not working if either the
    left-hand or right-hand side was derived from an INTEGER PRIMARY KEY.</li>
<li>Do not escape the backslash '\' character in the output of the
    <b>sqlite</b> command-line access program.</li>
}

Added www/conflict.tcl.































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
#
# Run this Tcl script to generate the constraint.html file.
#
set rcsid {$Id: conflict.tcl,v 1.1 2002/01/30 16:17:25 drh Exp $ }

puts {<html>
<head>
  <title>Constraint Conflict Resolution in SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
Constraint Conflict Resolution in SQLite
</h1>}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"

puts {
<h2>Introduction</h2>

<p>
In most SQL databases, if you have a UNIQUE constraint on
a table and you try to do an UPDATE or INSERT that violates
that constraint, the database will aborts the operation in
progress and rolls back the current transaction.
This is the default behavior of SQLite.
Beginning with version 2.3.0, though, SQLite allows you to
define alternative ways for dealing with constraint violations.
This article describes those alternatives and how to use them.
</p>

<h2>Conflict Resolution Algorithms</h2>

<p>
The default conflict resolution algorithm is to abort the
operation in progress, rollback all changes, and cancel the
current transaction.  Call this algorithm "ABORT".  Abort
is the standard way of dealing with a constraint error
in most SQL databases.
</p>

<p>
Sometimes ABORT is not the most helpful way of dealing
with constraint violations.  Suppose, for example, you are
inserting 1000 records into a database, all within a single
transaction, but one of those records is malformed and causes
a constraint error.  With the default ABORT behavior, none
of the 1000 records gets inserted.  But sometimes it is 
desirable to just omit the single malformed insert and
finish the other 999.
</p>

<p>
SQLite defines two addition conflict resolution algorithms
called "IGNORE" and "REPLACE".  
If you are trying to do multiple INSERTs or UPDATEs when a constraint
fails for a single row and the conflict behavior is IGNORE, then
that row remains uninserted or unmodified.  But the overall operation
is not aborted and no rollback occurs.  If a constraint
fails and the behavior is REPLACE, then SQLite tries to
delete other rows in the table in order to eliminate the
constraint problem.  Again, the overall operation continues
and no rollback occurs.
</p>

<p>
The default conflict resolution algorithm is always ABORT
but you can specify an alternative algorithm using special
(non-standard) syntax on the INSERT and UPDATE commands.
You can add the clause "ON CONFLICT <algorithm>" immediately
after the "INSERT" or "UPDATE" keywords to specify the 
conflict resolution algorithm to use for that one operation.
(Substitute "ABORT", "IGNORE", or "REPLACE" for <algorithm>,
of course.)
</p>

Consider this example:

<blockquote><pre>
   BEGIN;
   CREATE TABLE t1(
      a INTEGER,
      b INTEGER,
      c INTEGER,
      UNIQUE(a,b)
   );
   INSERT INTO a VALUES(1,2,3);
   COMMIT;

   BEGIN;
   INSERT INTO a VALUES(2,3,4);
   INSERT INTO a VALUES(1,2,5);
</pre></blockquote>

<p>
In the last instruction, the UNIQUE constraint fails
and the entire transaction is rolled back.  The database
now contains a single entry: {1,2,3}.  
</p>

<blockquote><pre>
   BEGIN;
   INSERT ON CONFLICT IGNORE INTO a VALUES(2,3,4);
   INSERT ON CONFLICT IGNORE INTO a VALUES(1,2,5);
   COMMIT;
</pre></blockquote>

<p>This time the "ON CONFLICT IGNORE" clause tells SQLite to use
IGNORE semantics when a constraint fails.  The second
INSERT statement fails, but the database is
not rolled back and there is no failure.  The database
now contains two rows:  {1,2,3} and {2,3,4}.</p>

<blockquote><pre>
   BEGIN;
   INSERT ON CONFLICT REPLACE INTO a VALUES(1,2,5);
   COMMIT;
</pre></blockquote>

<p>Here the "ON CONFLICT REPLACE" clause tells SQLite to use REPLACE
semantics.  The {1,2,3} is deleted when the {1,2,5} row
is inserted in order to satisfy the constraint.  After
the above, the database contains {1,2,5} and {2,3,4}.</p>

<h2>A Syntactic Shortcut</h2>

<p>On an INSERT, the "ON CONFLICT" keywords may be omitted for brevity.
So you can say</p>

<blockquote><pre>
   INSERT IGNORE INTO a VALUES(1,2,5);
</pre></blockquote>

<p>Instead of the more wordy:</p>

<blockquote><pre>
   INSERT ON CONFLICT IGNORE INTO a VALUES(1,2,5);
</pre></blockquote>

<p>Unfortunately, you cannot do this with an UPDATE.</p>

<h2>Changing The Default Conflict Resolution Algorithm</h2>

<p>You can change the default conflict resolution algorithm
on a constraint-by-constraint basis using special (non-standard)
syntax in CREATE TABLE and CREATE INDEX statements.  The
same "ON CONFLICT" clause that appears in INSERT and UPDATE
statements is used but the clause is attached to the constraint
in the CREATE TABLE statement.  Like this:

<blockquote><pre>
   CREATE TABLE t1 (
     a INTEGER,
     b INTEGER,
     c INTEGER,
     UNIQUE(a,b) ON CONFLICT REPLACE
   );
</pre></blockquote>

<p>The ON CONFLICT clause in the above table definition says that
the default conflict resolution algorithm is REPLACE instead
of ABORT.  REPLACE will always be used unless you override
this by saying "INSERT IGNORE" or "INSERT ABORT".</p>

<p>The ON CONFLICT clause can also appear on a NOT NULL constraint,
a PRIMARY KEY constraint, and a CHECK constraint.
(Note, however, that CHECK constraints are not currently enforced
so the ON CONFLICT clause has no effect there.)</p>

<p>A NOT NULL constraint will normally ABORT if you try to insert
a NULL.  But if you substitute the REPLACE algorithm, it tries to insert
the default value in place of the NULL.  If there is no default value,
then REPLACE is the same as ABORT for NOT NULL constraints.
With the IGNORE algorithm on a NOT NULL, the INSERT or UPDATE 
is suppressed if the value is NULL.</p>

<h2>Portability</h2>

<p>The ON CONFLICT syntax is not standard SQL and will not
(as far as is known) work on any other database product.  Furthermore,
the syntax might change in future versions of SQLite.  So use it
with appropriate discretion.</p>
}

puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>

</body></html>}

Changes to www/formatchng.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
77
78
79
80
81
82
83










84
85
86
87
88
89
90
#
# Run this Tcl script to generate the formatchng.html file.
#
set rcsid {$Id: formatchng.tcl,v 1.1 2001/12/22 19:27:41 drh Exp $ }

puts {<html>
<head>
  <title>File Format Changes in SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
  forward compatible with version 2.2.x. If you try to open
  a 2.2.x database with an older 2.1.x library and that database contains
  an INTEGER PRIMARY KEY, you will likely get a coredump.  If the database
  schema does not contain any INTEGER PRIMARY KEYs, then the version 2.1.x
  and version 2.2.x database files will be identical and completely
  interchangeable.</p>
</tr>










</table>
</blockquote>

<p>
To perform a database reload, have ready versions of the
<b>sqlite</b> command-line utility for both the old and new
version of SQLite.  Call these two executables "<b>sqlite-old</b>"



|







 







>
>
>
>
>
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
..
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
#
# Run this Tcl script to generate the formatchng.html file.
#
set rcsid {$Id: formatchng.tcl,v 1.2 2002/01/30 16:17:25 drh Exp $ }

puts {<html>
<head>
  <title>File Format Changes in SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
  forward compatible with version 2.2.x. If you try to open
  a 2.2.x database with an older 2.1.x library and that database contains
  an INTEGER PRIMARY KEY, you will likely get a coredump.  If the database
  schema does not contain any INTEGER PRIMARY KEYs, then the version 2.1.x
  and version 2.2.x database files will be identical and completely
  interchangeable.</p>
</tr>
<tr>
  <td valign="top">2.2.5 to 2.3.0</td>
  <td valign="top">2002-Jan-30</td>
  <td>Beginning with version 2.3.0, SQLite supports some additional syntax
  (the "ON CONFLICT" clause) in the CREATE TABLE and CREATE INDEX statements
  that are stored in the SQLITE_MASTER table.  If you create a database that
  contains this new syntax, then try to read that database using version 2.2.5
  or earlier, the parser will not understand the new syntax and you will get
  an error.  Otherwise, databases for 2.2.x and 2.3.x are interchangeable.</p>
</tr>
</table>
</blockquote>

<p>
To perform a database reload, have ready versions of the
<b>sqlite</b> command-line utility for both the old and new
version of SQLite.  Call these two executables "<b>sqlite-old</b>"

Changes to www/lang.tcl.

1
2
3
4
5
6
7
8
9
10
11
..
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
..
46
47
48
49
50
51
52

53
54
55
56
57
58
59
...
135
136
137
138
139
140
141
142

































143
144
145
146

147

148
149
150
151
152
153
154
...
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
...
189
190
191
192
193
194
195










196
197
198
199
200
201
202
...
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
...
261
262
263
264
265
266
267











268
269
270
271
272
273
274
...
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
...
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
...
509
510
511
512
513
514
515





516
517
518
519
520
521
522
...
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
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.18 2001/12/22 19:27:41 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
that are part of the syntactic markup itself are shown in black roman.</p>

<p>This document is just an overview of the SQL syntax implemented
by SQLite.  Many low-level productions are omitted.  For detailed information
on the language that SQLite understands, refer to the source code.</p>


<p>SQLite implements the follow SQL commands:</p>
<p><ul>
}

foreach {section} [lsort -index 0 -dictionary {
  {{CREATE TABLE} createtable}
  {{CREATE INDEX} createindex}
  {VACUUM vacuum}
................................................................................
  {UPDATE update}
  {SELECT select}
  {COPY copy}
  {EXPLAIN explain}
  {expression expr}
  {{BEGIN TRANSACTION} transaction}
  {PRAGMA pragma}

}] {
  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
}
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>
................................................................................
command. Such transactions persist until a COMMIT or ROLLBACK
or until an error occurs or the database is closed.  If an
error is encountered or the database is closed, the transaction
is automatically rolled back.  The END TRANSACTION command is
a alias for COMMIT.
</p>
}


































Section COPY copy

Syntax {sql-statement} {
COPY <table-name> FROM <filename>

}


puts {
<p>The COPY command is an extension used to load large amounts of
data into a table.  It is modeled after a similar command found
in PostgreSQL.  In fact, the SQLite COPY command is specifically
designed to be able to read the output of the PostgreSQL dump
utility <b>pg_dump</b> so that data can be easily transferred from
................................................................................
names a file from which data will be read.  The filename can be
the <b>STDIN</b> to read data from standard input.<p>

<p>Each line of the input file is converted into a single record
in the table.  Columns are separated by tabs.  If a tab occurs as
data within a column, then that tab is preceded by a baskslash "\"
character.  A baskslash in the data appears as two backslashes in
a row.</p>










<p>When the input data source is STDIN, the input can be terminated
by a line that contains only a baskslash and a dot:}
puts "\"[Operator \\.]\".</p>"

Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE [UNIQUE] INDEX <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )

} {column-name} {
<name> [ ASC | DESC ]
}


puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
Each column name can be followed by one of the "ASC" or "DESC" keywords
................................................................................

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table, nor on the number of columns in an index.</p>

<p>If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  Any attempt to insert a duplicate entry
will result in a rollback and an error message.</p>











<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the index layout.</p>
}
................................................................................
} {column-def} {
<name> <type> [<column-constraint>]*
} {type} {
<typename> |
<typename> ( <number> ) |
<typename> ( <number> , <number> )
} {column-constraint} {
NOT NULL |
PRIMARY KEY [<sort-order>] |
UNIQUE |
CHECK ( <expr> ) |
DEFAULT <value>
} {constraint} {
PRIMARY KEY ( <name> [, <name>]* ) |
UNIQUE ( <name> [, <name>]* ) |
CHECK ( <expr> )
}

puts {
<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  The table name can be either an identifier
or a string.  The only reserved table name is "<b>sqlite_master</b>" which
................................................................................
<p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "TABLE" then the table that is created is only visible to the
process that opened the database and is automatically deleted when
the database is closed.  Any indices created on a temporary table
are also temporary.  Temporary tables and indices are stored in a
separate file distinct from the main database file.</p>












<p>There are no arbitrary limits on the number
of columns or on the number of constraints in a table.
The total amount of data in a single row is limited to about
1 megabytes.  (This limit can be increased to 16MB by changing
a single #define in the source code and recompiling.)</p>

<p>The exact text
................................................................................
<expression> [NOT] IN ( <select> ) |
( <select> )
} {like-op} {
LIKE | GLOB | NOT LIKE | NOT GLOB
}

puts {
<p>This section is different from the others.  Every other section of
this document talks about a particular SQL command.  This section does
not talk about a standalone command but about "expressions" which are 
subcomponent of most other commands.</p>

<p>SQLite understands the following binary operators, in order from
highest to lowest precedence:</p>

................................................................................
"<b>count(distinct</b> <i>COLUMN-NAME</i><b>)</b>" is not.
</p>
}

Section INSERT insert

Syntax {sql-statement} {
INSERT INTO <table-name> [( <column-list> )] VALUES ( <value-list> ) |
INSERT INTO <table-name> [( <column-list> )] <select-statement>
}

puts {
<p>The INSERT statement comes in two basic forms.  The first form
(with the "VALUES" keyword) creates a single new row in an existing table.
If no column-list is specified then the number of values must
be the same as the number of columns in the table.  If a column-list
................................................................................
SELECT statement.  The number of columns in the result of the
SELECT must exactly match the number of columns in the table if
no column list is specified, or it must match the number of columns
name in the column list.  A new entry is made in the table
for every row of the SELECT result.  The SELECT may be simple
or compound.  If the SELECT statement has an ORDER BY clause,
the ORDER BY is ignored.</p>





}

Section SELECT select

Syntax {sql-statement} {
SELECT <result> FROM <table-list> 
[WHERE <expression>]
................................................................................
removing the results of the right SELECT.  When three are more SELECTs
are connected into a compound, they group from left to right.</p>
}

Section UPDATE update

Syntax {sql-statement} {

UPDATE <table-name> SET <assignment> [, <assignment>] [WHERE <expression>]

} {assignment} {
<column-name> = <expression>
}

puts {
<p>The UPDATE statement is used to change the value of columns in 
selected rows of a table.  Each assignment in an UPDATE specifies
a column name to the left of the equals sign and an arbitrary expression
to the right.  The expressions may use the values of other columns.
All expressions are evaluated before any assignments are made.
A WHERE clause can be used to restrict which rows are updated.





}

Section VACUUM vacuum

Syntax {sql-statement} {
VACUUM [<index-or-table-name>]
}



|







 







|







 







>







 








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



|
>

>







 







|
>
>
>
>
>
>
>
>
>










>



>







 







>
>
>
>
>
>
>
>
>
>







 







|
|
|
|


|
|
|







 







>
>
>
>
>
>
>
>
>
>
>







 







|







 







|
|







 







>
>
>
>
>







 







>
|
>










|
>
>
>
>
>







1
2
3
4
5
6
7
8
9
10
11
..
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
..
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
...
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
...
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
...
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
...
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
...
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
...
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
...
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
...
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
...
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.19 2002/01/30 16:17:25 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
that are part of the syntactic markup itself are shown in black roman.</p>

<p>This document is just an overview of the SQL syntax implemented
by SQLite.  Many low-level productions are omitted.  For detailed information
on the language that SQLite understands, refer to the source code.</p>


<p>SQLite implements the follow syntax:</p>
<p><ul>
}

foreach {section} [lsort -index 0 -dictionary {
  {{CREATE TABLE} createtable}
  {{CREATE INDEX} createindex}
  {VACUUM vacuum}
................................................................................
  {UPDATE update}
  {SELECT select}
  {COPY copy}
  {EXPLAIN explain}
  {expression expr}
  {{BEGIN TRANSACTION} transaction}
  {PRAGMA pragma}
  {{ON CONFLICT clause} conflict}
}] {
  puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>"
}
puts {</ul></p>

<p>Details on the implementation of each command are provided in
the sequel.</p>
................................................................................
command. Such transactions persist until a COMMIT or ROLLBACK
or until an error occurs or the database is closed.  If an
error is encountered or the database is closed, the transaction
is automatically rolled back.  The END TRANSACTION command is
a alias for COMMIT.
</p>
}

Section {ON CONFLICT clause} conflict

Syntax {conflict-clause} {
ON CONFLICT <algorithm>
} {algorithm} {
ABORT | IGNORE | REPLACE
}

puts {
<p>The ON CONFLICT clause is not a separate SQL command.  It is a
non-standard clause that can appear in many other SQL commands.
It is given its own section in this document because it is not
part of standard SQL and therefore might not be familiar.</p>

<p>The ON CONFLICT clause specifies an algorithm used to resolve
constraint conflicts.  The default algorithm is ABORT.  When the
ABORT algorithm is in use, any constraint violation causes the
command to abort and the current transaction to be rolled back.
This is the only behavior exhibited by most SQL engines.  But
SQLite allows two alternative behaviors: IGNORE and REPLACE.
The IGNORE algorithm means that when a constraint violation occurs
on a COPY, INSERT or UPDATE, the particular row that caused the constraint
violation is not inserted or changed, but other rows effected by the
COPY, INSERT, or UPDATE are insert or changed as usual.
The command is not aborted and no rollback occurs.
If the algorithm is REPLACE, then SQLite tries to
delete preexisting rows from the table to remove the constraint
violation before inserting or changing the row.</p>

<p>For additional information, see 
<a href="conflict.html">conflict.html</a>.</p>
}

Section COPY copy

Syntax {sql-statement} {
COPY [ <conflict-clause> ] <table-name> FROM <filename>
[ USING DELIMITERS <delim> ]
}


puts {
<p>The COPY command is an extension used to load large amounts of
data into a table.  It is modeled after a similar command found
in PostgreSQL.  In fact, the SQLite COPY command is specifically
designed to be able to read the output of the PostgreSQL dump
utility <b>pg_dump</b> so that data can be easily transferred from
................................................................................
names a file from which data will be read.  The filename can be
the <b>STDIN</b> to read data from standard input.<p>

<p>Each line of the input file is converted into a single record
in the table.  Columns are separated by tabs.  If a tab occurs as
data within a column, then that tab is preceded by a baskslash "\"
character.  A baskslash in the data appears as two backslashes in
a row.  The optional USING DELIMITERS clause can specify a delimiter
other than tab.</p>

<p>If a column consists of the character "\N", that column is filled
with the value NULL.</p>

<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use for this one command.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.</p>

<p>When the input data source is STDIN, the input can be terminated
by a line that contains only a baskslash and a dot:}
puts "\"[Operator \\.]\".</p>"

Section {CREATE INDEX} createindex

Syntax {sql-statement} {
CREATE [UNIQUE] INDEX <index-name> 
ON <table-name> ( <column-name> [, <column-name>]* )
[ <conflict-clause> ]
} {column-name} {
<name> [ ASC | DESC ]
}


puts {
<p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed
by the name of the new index, the keyword "ON", the name of a previously
created table that is to be indexed, and a parenthesized list of names of
columns in the table that are used for the index key.
Each column name can be followed by one of the "ASC" or "DESC" keywords
................................................................................

<p>There are no arbitrary limits on the number of indices that can be
attached to a single table, nor on the number of columns in an index.</p>

<p>If the UNIQUE keyword appears between CREATE and INDEX then duplicate
index entries are not allowed.  Any attempt to insert a duplicate entry
will result in a rollback and an error message.</p>

<p>The optional conflict-clause allows the specification of al alternative
default constraint conflict resolution algorithm for this index.
This only makes sense if the UNIQUE keyword is used since otherwise
there are not constraints on the index.  The default algorithm is
ABORT.  If a COPY, INSERT, or UPDATE statement specifies a particular
conflict resolution algorithm, that algorithm is used in place of
the default algorithm specified here.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.</p>

<p>The exact text
of each CREATE INDEX statement is stored in the <b>sqlite_master</b>
table.  Everytime the database is opened, all CREATE INDEX statements
are read from the <b>sqlite_master</b> table and used to regenerate
SQLite's internal representation of the index layout.</p>
}
................................................................................
} {column-def} {
<name> <type> [<column-constraint>]*
} {type} {
<typename> |
<typename> ( <number> ) |
<typename> ( <number> , <number> )
} {column-constraint} {
NOT NULL [ <conflict-clause> ] |
PRIMARY KEY [<sort-order>] [ <conflict-clause> ] |
UNIQUE [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ] |
DEFAULT <value>
} {constraint} {
PRIMARY KEY ( <name> [, <name>]* ) [ <conflict-clause> ]|
UNIQUE ( <name> [, <name>]* ) [ <conflict-clause> ] |
CHECK ( <expr> ) [ <conflict-clause> ]
}

puts {
<p>A CREATE TABLE statement is basically the keywords "CREATE TABLE"
followed by the name of a new table and a parenthesized list of column
definitions and constraints.  The table name can be either an identifier
or a string.  The only reserved table name is "<b>sqlite_master</b>" which
................................................................................
<p>If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE"
and "TABLE" then the table that is created is only visible to the
process that opened the database and is automatically deleted when
the database is closed.  Any indices created on a temporary table
are also temporary.  Temporary tables and indices are stored in a
separate file distinct from the main database file.</p>

<p>The optional conflict-clause following each constraint
allows the specification of an alternative default
constraint conflict resolution algorithm for that constraint.
The default is abort ABORT.  Different constraints within the same
table may have different default conflict resolution algorithms.
If an COPY, INSERT, or UPDATE command specifies a different conflict
resolution algorithm, then that algorithm is used in place of the
default algorithm specified in the CREATE TABLE statement.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.</p>

<p>There are no arbitrary limits on the number
of columns or on the number of constraints in a table.
The total amount of data in a single row is limited to about
1 megabytes.  (This limit can be increased to 16MB by changing
a single #define in the source code and recompiling.)</p>

<p>The exact text
................................................................................
<expression> [NOT] IN ( <select> ) |
( <select> )
} {like-op} {
LIKE | GLOB | NOT LIKE | NOT GLOB
}

puts {
<p>This section is different from the others.  Most other sections of
this document talks about a particular SQL command.  This section does
not talk about a standalone command but about "expressions" which are 
subcomponent of most other commands.</p>

<p>SQLite understands the following binary operators, in order from
highest to lowest precedence:</p>

................................................................................
"<b>count(distinct</b> <i>COLUMN-NAME</i><b>)</b>" is not.
</p>
}

Section INSERT insert

Syntax {sql-statement} {
INSERT [ <conflict-clause> ] INTO <table-name> [( <column-list> )] VALUES ( <value-list> ) |
INSERT [ <conflict-clause> ] INTO <table-name> [( <column-list> )] <select-statement>
}

puts {
<p>The INSERT statement comes in two basic forms.  The first form
(with the "VALUES" keyword) creates a single new row in an existing table.
If no column-list is specified then the number of values must
be the same as the number of columns in the table.  If a column-list
................................................................................
SELECT statement.  The number of columns in the result of the
SELECT must exactly match the number of columns in the table if
no column list is specified, or it must match the number of columns
name in the column list.  A new entry is made in the table
for every row of the SELECT result.  The SELECT may be simple
or compound.  If the SELECT statement has an ORDER BY clause,
the ORDER BY is ignored.</p>

<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.</p>
}

Section SELECT select

Syntax {sql-statement} {
SELECT <result> FROM <table-list> 
[WHERE <expression>]
................................................................................
removing the results of the right SELECT.  When three are more SELECTs
are connected into a compound, they group from left to right.</p>
}

Section UPDATE update

Syntax {sql-statement} {
UPDATE [ <conflict-clause> ] <table-name>
SET <assignment> [, <assignment>] 
[WHERE <expression>]
} {assignment} {
<column-name> = <expression>
}

puts {
<p>The UPDATE statement is used to change the value of columns in 
selected rows of a table.  Each assignment in an UPDATE specifies
a column name to the left of the equals sign and an arbitrary expression
to the right.  The expressions may use the values of other columns.
All expressions are evaluated before any assignments are made.
A WHERE clause can be used to restrict which rows are updated.</p>

<p>The optional conflict-clause allows the specification of an alternative
constraint conflict resolution algorithm to use during this one command.
See the section titled
<a href="#conflict">ON CONFLICT</a> for additional information.</p>
}

Section VACUUM vacuum

Syntax {sql-statement} {
VACUUM [<index-or-table-name>]
}