SQLite

Check-in [35a8feed0d]
Login

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

Overview
Comment:Added transaction support (CVS 196)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 35a8feed0d10e780c477f7440fbe80637fcf9906
User & Date: drh 2001-04-04 11:48:57.000
Context
2001-04-04
12:32
:-) (CVS 197) (check-in: ab64543744 user: drh tags: trunk)
11:48
Added transaction support (CVS 196) (check-in: 35a8feed0d user: drh tags: trunk)
2001-04-03
16:53
Bug fixes from Oleg Oleinick (CVS 195) (check-in: 1f0197d504 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
**     DROP TABLE
**     CREATE INDEX
**     DROP INDEX
**     creating expressions and ID lists
**     COPY
**     VACUUM
**
** $Id: build.c,v 1.25 2001/01/15 22:51:09 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
** that statement.  Prior action routines should have already







|







29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
**     DROP TABLE
**     CREATE INDEX
**     DROP INDEX
**     creating expressions and ID lists
**     COPY
**     VACUUM
**
** $Id: build.c,v 1.26 2001/04/04 11:48:57 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is called after a single SQL statement has been
** parsed and we want to execute the VDBE code to implement 
** that statement.  Prior action routines should have already
920
921
922
923
924
925
926




























































    }
  }

vacuum_cleanup:
  sqliteFree(zName);
  return;
}



































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
    }
  }

vacuum_cleanup:
  sqliteFree(zName);
  return;
}

/*
** Begin a transaction
*/
void sqliteBeginTransaction(Parse *pParse){
  int rc;
  DbbeMethods *pM;
  sqlite *db;
  if( pParse==0 || (db=pParse->db)==0 || db->pBe==0 ) return;
  if( db->flags & SQLITE_InTrans ) return;
  pM = pParse->db->pBe->x;
  if( pM && pM->BeginTransaction ){
    rc = (*pM->BeginTransaction)(pParse->db->pBe);
  }else{
    rc = SQLITE_OK;
  }
  if( rc==SQLITE_OK ){
    db->flags |= SQLITE_InTrans;
  }
}

/*
** Commit a transaction
*/
void sqliteCommitTransaction(Parse *pParse){
  int rc;
  DbbeMethods *pM;
  sqlite *db;
  if( pParse==0 || (db=pParse->db)==0 || db->pBe==0 ) return;
  if( (db->flags & SQLITE_InTrans)==0 ) return;
  pM = pParse->db->pBe->x;
  if( pM && pM->Commit ){
    rc = (*pM->Commit)(pParse->db->pBe);
  }else{
    rc = SQLITE_OK;
  }
  if( rc==SQLITE_OK ){
    db->flags &= ~SQLITE_InTrans;
  }
}

/*
** Rollback a transaction
*/
void sqliteRollbackTransaction(Parse *pParse){
  int rc;
  DbbeMethods *pM;
  sqlite *db;
  if( pParse==0 || (db=pParse->db)==0 || db->pBe==0 ) return;
  if( (db->flags & SQLITE_InTrans)==0 ) return;
  pM = pParse->db->pBe->x;
  if( pM && pM->Rollback ){
    rc = (*pM->Rollback)(pParse->db->pBe);
  }else{
    rc = SQLITE_OK;
  }
  if( rc==SQLITE_OK ){
    db->flags &= ~SQLITE_InTrans;
  }
}
Changes to src/dbbe.h.
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
** This file defines the interface to the database backend (Dbbe).
**
** The database backend is designed to be as general as possible
** so that it can easily be replaced by a different backend.
** This library was originally designed to support the following
** backends: GDBM, NDBM, SDBM, Berkeley DB.
**
** $Id: dbbe.h,v 1.12 2001/04/03 16:53:22 drh Exp $
*/
#ifndef _SQLITE_DBBE_H_
#define _SQLITE_DBBE_H_
#include <stdio.h>

/*
** The database backend supports two opaque structures.  A Dbbe is







|







24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
** This file defines the interface to the database backend (Dbbe).
**
** The database backend is designed to be as general as possible
** so that it can easily be replaced by a different backend.
** This library was originally designed to support the following
** backends: GDBM, NDBM, SDBM, Berkeley DB.
**
** $Id: dbbe.h,v 1.13 2001/04/04 11:48:57 drh Exp $
*/
#ifndef _SQLITE_DBBE_H_
#define _SQLITE_DBBE_H_
#include <stdio.h>

/*
** The database backend supports two opaque structures.  A Dbbe is
147
148
149
150
151
152
153









154
155
156
157
158
159
160
  /* Write an entry into a table.  If another entry already exists with
  ** the same key, the old entry is discarded first.
  */
  int (*Put)(DbbeCursor*, int nKey, char *pKey, int nData, char *pData);

  /* Remove an entry from the table */
  int (*Delete)(DbbeCursor*, int nKey, char *pKey);









};

/*
** This is the structure returned by sqliteDbbeOpen().  It contains
** information common to all the different backend drivers.
**
** The information in this structure (with the exception the method







>
>
>
>
>
>
>
>
>







147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
  /* Write an entry into a table.  If another entry already exists with
  ** the same key, the old entry is discarded first.
  */
  int (*Put)(DbbeCursor*, int nKey, char *pKey, int nData, char *pData);

  /* Remove an entry from the table */
  int (*Delete)(DbbeCursor*, int nKey, char *pKey);

  /* Begin a transaction. */
  int (*BeginTransaction)(Dbbe*);

  /* Commit a transaction. */
  int (*Commit)(Dbbe*);

  /* Rollback a transaction. */
  int (*Rollback)(Dbbe*);
};

/*
** This is the structure returned by sqliteDbbeOpen().  It contains
** information common to all the different backend drivers.
**
** The information in this structure (with the exception the method
Changes to src/dbbegdbm.c.
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
** sqlite and the code that does the actually reading and writing
** of information to the disk.
**
** This file uses GDBM as the database backend.  It should be
** relatively simple to convert to a different database such
** as NDBM, SDBM, or BerkeleyDB.
**
** $Id: dbbegdbm.c,v 1.5 2001/04/03 16:53:22 drh Exp $
*/
#include "sqliteInt.h"
#include <gdbm.h>
#include <sys/stat.h>
#include <unistd.h>
#include <ctype.h>
#include <time.h>







|







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
** sqlite and the code that does the actually reading and writing
** of information to the disk.
**
** This file uses GDBM as the database backend.  It should be
** relatively simple to convert to a different database such
** as NDBM, SDBM, or BerkeleyDB.
**
** $Id: dbbegdbm.c,v 1.6 2001/04/04 11:48:57 drh Exp $
*/
#include "sqliteInt.h"
#include <gdbm.h>
#include <sys/stat.h>
#include <unistd.h>
#include <ctype.h>
#include <time.h>
64
65
66
67
68
69
70

71
72
73
74
75
76
77
** The following structure contains all information used by GDBM
** database driver.  This is a subclass of the Dbbe structure.
*/
typedef struct Dbbex Dbbex;
struct Dbbex {
  Dbbe dbbe;         /* The base class */
  int write;         /* True for write permission */

  BeFile *pOpen;     /* List of open files */
  char *zDir;        /* Directory hold the database */
};

/*
** An cursor into a database file is an instance of the following structure.
** There can only be a single BeFile structure for each disk file, but







>







64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
** The following structure contains all information used by GDBM
** database driver.  This is a subclass of the Dbbe structure.
*/
typedef struct Dbbex Dbbex;
struct Dbbex {
  Dbbe dbbe;         /* The base class */
  int write;         /* True for write permission */
  int inTrans;       /* Currently in a transaction */
  BeFile *pOpen;     /* List of open files */
  char *zDir;        /* Directory hold the database */
};

/*
** An cursor into a database file is an instance of the following structure.
** There can only be a single BeFile structure for each disk file, but
174
175
176
177
178
179
180

181
182
183
184
185
186
187
  DbbeCursor *pCursr;     /* The new table cursor */
  BeFile *pFile;          /* The underlying data file for this table */
  int rc = SQLITE_OK;     /* Return value */
  int rw_mask;            /* Permissions mask for opening a table */
  int mode;               /* Mode for opening a table */
  Dbbex *pBe = (Dbbex*)pDbbe;


  *ppCursr = 0;
  pCursr = sqliteMalloc( sizeof(*pCursr) );
  if( pCursr==0 ) return SQLITE_NOMEM;
  if( zTable ){
    zFile = sqliteFileOfTable(pBe, zTable);
    for(pFile=pBe->pOpen; pFile; pFile=pFile->pNext){
      if( strcmp(pFile->zName,zFile)==0 ) break;







>







175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
  DbbeCursor *pCursr;     /* The new table cursor */
  BeFile *pFile;          /* The underlying data file for this table */
  int rc = SQLITE_OK;     /* Return value */
  int rw_mask;            /* Permissions mask for opening a table */
  int mode;               /* Mode for opening a table */
  Dbbex *pBe = (Dbbex*)pDbbe;

  if( pBe->inTrans ) writeable = 1;
  *ppCursr = 0;
  pCursr = sqliteMalloc( sizeof(*pCursr) );
  if( pCursr==0 ) return SQLITE_NOMEM;
  if( zTable ){
    zFile = sqliteFileOfTable(pBe, zTable);
    for(pFile=pBe->pOpen; pFile; pFile=pFile->pNext){
      if( strcmp(pFile->zName,zFile)==0 ) break;
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
        zFile = sqliteFileOfTable(pBe, zRandom);
        pFile->dbf = gdbm_open(zFile, 0, rw_mask, mode, 0);
      }while( pFile->dbf==0 && limit-- >= 0);
      pFile->delOnClose = 1;
    }
    pFile->writeable = writeable;
    pFile->zName = zFile;
    pFile->nRef = 1;
    pFile->pPrev = 0;
    if( pBe->pOpen ){
      pBe->pOpen->pPrev = pFile;
    }
    pFile->pNext = pBe->pOpen;
    pBe->pOpen = pFile;
    if( pFile->dbf==0 ){







|







222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
        zFile = sqliteFileOfTable(pBe, zRandom);
        pFile->dbf = gdbm_open(zFile, 0, rw_mask, mode, 0);
      }while( pFile->dbf==0 && limit-- >= 0);
      pFile->delOnClose = 1;
    }
    pFile->writeable = writeable;
    pFile->zName = zFile;
    pFile->nRef = 1 + pBe->inTrans;
    pFile->pPrev = 0;
    if( pBe->pOpen ){
      pBe->pOpen->pPrev = pFile;
    }
    pFile->pNext = pBe->pOpen;
    pBe->pOpen = pFile;
    if( pFile->dbf==0 ){
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
311
312
313
314
315
316
317
318
319
320
321
static void sqliteGdbmDropTable(Dbbe *pBe, const char *zTable){
  char *zFile;            /* Name of the table file */

  zFile = sqliteFileOfTable((Dbbex*)pBe, zTable);
  unlink(zFile);
  sqliteFree(zFile);
}
























/*
** Close a cursor previously opened by sqliteGdbmOpenCursor().
**
** There can be multiple cursors pointing to the same open file.
** The underlying file is not closed until all cursors have been
** closed.  This routine decrements the BeFile.nref field of the
** underlying file and closes the file when nref reaches 0.
*/
static void sqliteGdbmCloseCursor(DbbeCursor *pCursr){
  BeFile *pFile;
  Dbbex *pBe;
  if( pCursr==0 ) return;
  pFile = pCursr->pFile;
  pBe = pCursr->pBe;
  pFile->nRef--;
  if( pFile->dbf!=NULL ){
    gdbm_sync(pFile->dbf);
  }
  if( pFile->nRef<=0 ){
    if( pFile->dbf!=NULL ){
      gdbm_close(pFile->dbf);
    }
    if( pFile->pPrev ){
      pFile->pPrev->pNext = pFile->pNext;
    }else{
      pBe->pOpen = pFile->pNext;
    }
    if( pFile->pNext ){
      pFile->pNext->pPrev = pFile->pPrev;
    }
    if( pFile->delOnClose ){
      unlink(pFile->zName);
    }
    sqliteFree(pFile->zName);
    memset(pFile, 0, sizeof(*pFile));
    sqliteFree(pFile);
  }
  if( pCursr->key.dptr ) free(pCursr->key.dptr);
  if( pCursr->data.dptr ) free(pCursr->data.dptr);
  memset(pCursr, 0, sizeof(*pCursr));
  sqliteFree(pCursr);
}








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




















<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|







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
311
312
313
314
315
316
317
318
319
320
321
322
















323
324
325
326
327
328
329
330
static void sqliteGdbmDropTable(Dbbe *pBe, const char *zTable){
  char *zFile;            /* Name of the table file */

  zFile = sqliteFileOfTable((Dbbex*)pBe, zTable);
  unlink(zFile);
  sqliteFree(zFile);
}

/*
** Unlink a file pointer
*/
static void sqliteUnlinkFile(Dbbex *pBe, BeFile *pFile){
  if( pFile->dbf!=NULL ){
    gdbm_close(pFile->dbf);
  }
  if( pFile->pPrev ){
    pFile->pPrev->pNext = pFile->pNext;
  }else{
    pBe->pOpen = pFile->pNext;
  }
  if( pFile->pNext ){
    pFile->pNext->pPrev = pFile->pPrev;
  }
  if( pFile->delOnClose ){
    unlink(pFile->zName);
  }
  sqliteFree(pFile->zName);
  memset(pFile, 0, sizeof(*pFile));
  sqliteFree(pFile);
}

/*
** Close a cursor previously opened by sqliteGdbmOpenCursor().
**
** There can be multiple cursors pointing to the same open file.
** The underlying file is not closed until all cursors have been
** closed.  This routine decrements the BeFile.nref field of the
** underlying file and closes the file when nref reaches 0.
*/
static void sqliteGdbmCloseCursor(DbbeCursor *pCursr){
  BeFile *pFile;
  Dbbex *pBe;
  if( pCursr==0 ) return;
  pFile = pCursr->pFile;
  pBe = pCursr->pBe;
  pFile->nRef--;
  if( pFile->dbf!=NULL ){
    gdbm_sync(pFile->dbf);
  }
  if( pFile->nRef<=0 ){
















    sqliteUnlinkFile(pBe, pFile);
  }
  if( pCursr->key.dptr ) free(pCursr->key.dptr);
  if( pCursr->data.dptr ) free(pCursr->data.dptr);
  memset(pCursr, 0, sizeof(*pCursr));
  sqliteFree(pCursr);
}

489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
static int sqliteGdbmNew(DbbeCursor *pCursr){
  int iKey;
  datum key;
  int go = 1;

  if( pCursr->pFile==0 || pCursr->pFile->dbf==0 ) return 1;
  while( go ){
    iKey = sqliteRandomInteger();
    if( iKey==0 ) continue;
    key.dptr = (char*)&iKey;
    key.dsize = 4;
    go = gdbm_exists(pCursr->pFile->dbf, key);
  }
  return iKey;
}   







|







498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
static int sqliteGdbmNew(DbbeCursor *pCursr){
  int iKey;
  datum key;
  int go = 1;

  if( pCursr->pFile==0 || pCursr->pFile->dbf==0 ) return 1;
  while( go ){
    iKey = sqliteRandomInteger() & 0x7fffffff;
    if( iKey==0 ) continue;
    key.dptr = (char*)&iKey;
    key.dsize = 4;
    go = gdbm_exists(pCursr->pFile->dbf, key);
  }
  return iKey;
}   
538
539
540
541
542
543
544


































545
546
547
548
549
550
551
  if( pCursr->pFile==0 || pCursr->pFile->dbf==0 ) return SQLITE_ERROR;
  key.dsize = nKey;
  key.dptr = pKey;
  rc = gdbm_delete(pCursr->pFile->dbf, key);
  if( rc ) rc = SQLITE_ERROR;
  return rc;
}



































/*
** This variable contains pointers to all of the access methods
** used to implement the GDBM backend.
*/
static struct DbbeMethods gdbmMethods = {
  /*           Close */   sqliteGdbmClose,







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







547
548
549
550
551
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
  if( pCursr->pFile==0 || pCursr->pFile->dbf==0 ) return SQLITE_ERROR;
  key.dsize = nKey;
  key.dptr = pKey;
  rc = gdbm_delete(pCursr->pFile->dbf, key);
  if( rc ) rc = SQLITE_ERROR;
  return rc;
}

/*
** Begin a transaction.
*/
static int sqliteGdbmBeginTrans(Dbbe *pDbbe){
  Dbbex *pBe = (Dbbex*)pDbbe;
  BeFile *pFile;
  if( pBe->inTrans ) return SQLITE_OK;
  for(pFile=pBe->pOpen; pFile; pFile=pFile->pNext){
    pFile->nRef++;
  }
  pBe->inTrans = 1;
  return SQLITE_OK;  
}

/*
** End a transaction.
*/
static int sqliteGdbmEndTrans(Dbbe *pDbbe){
  Dbbex *pBe = (Dbbex*)pDbbe;
  BeFile *pFile, *pNext;
  if( !pBe->inTrans ) return SQLITE_OK;
  for(pFile=pBe->pOpen; pFile; pFile=pNext){
    pNext = pFile->pNext;
    pFile->nRef--;
    if( pFile->nRef<=0 ){
      sqliteUnlinkFile(pBe, pFile);
    }
  }
  pBe->inTrans = 0;
  return SQLITE_OK;  
}



/*
** This variable contains pointers to all of the access methods
** used to implement the GDBM backend.
*/
static struct DbbeMethods gdbmMethods = {
  /*           Close */   sqliteGdbmClose,
562
563
564
565
566
567
568



569
570
571
572
573
574
575
  /*       KeyLength */   sqliteGdbmKeyLength,
  /*      DataLength */   sqliteGdbmDataLength,
  /*         NextKey */   sqliteGdbmNextKey,
  /*          Rewind */   sqliteGdbmRewind,
  /*             New */   sqliteGdbmNew,
  /*             Put */   sqliteGdbmPut,
  /*          Delete */   sqliteGdbmDelete,



};


/*
** This routine opens a new database.  For the GDBM driver
** implemented here, the database name is the name of the directory
** containing all the files of the database.







>
>
>







605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
  /*       KeyLength */   sqliteGdbmKeyLength,
  /*      DataLength */   sqliteGdbmDataLength,
  /*         NextKey */   sqliteGdbmNextKey,
  /*          Rewind */   sqliteGdbmRewind,
  /*             New */   sqliteGdbmNew,
  /*             Put */   sqliteGdbmPut,
  /*          Delete */   sqliteGdbmDelete,
  /*      BeginTrans */   sqliteGdbmBeginTrans,
  /*          Commit */   sqliteGdbmEndTrans,
  /*        Rollback */   sqliteGdbmEndTrans,
};


/*
** This routine opens a new database.  For the GDBM driver
** implemented here, the database name is the name of the directory
** containing all the files of the database.
Changes to src/dbbemem.c.
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
** sqlite and the code that does the actually reading and writing
** of information to the disk.
**
** This file uses an in-memory hash table as the database backend. 
** Nothing is ever written to disk using this backend.  All information
** is forgotten when the program exits.
**
** $Id: dbbemem.c,v 1.12 2001/04/03 16:53:22 drh Exp $
*/
#include "sqliteInt.h"
#include <sys/stat.h>
#include <unistd.h>
#include <ctype.h>
#include <time.h>








|







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
** sqlite and the code that does the actually reading and writing
** of information to the disk.
**
** This file uses an in-memory hash table as the database backend. 
** Nothing is ever written to disk using this backend.  All information
** is forgotten when the program exits.
**
** $Id: dbbemem.c,v 1.13 2001/04/04 11:48:58 drh Exp $
*/
#include "sqliteInt.h"
#include <sys/stat.h>
#include <unistd.h>
#include <ctype.h>
#include <time.h>

665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
*/
static int sqliteMemNew(DbbeCursor *pCursr){
  int iKey;
  Datum key;
  int go = 1;

  while( go ){
    iKey = sqliteRandomInteger();
    if( iKey==0 ) continue;
    key.p = (char*)&iKey;
    key.n = 4;
    go = ArrayFindElement(&pCursr->pTble->data, key)!=0;
  }
  return iKey;
}   







|







665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
*/
static int sqliteMemNew(DbbeCursor *pCursr){
  int iKey;
  Datum key;
  int go = 1;

  while( go ){
    iKey = sqliteRandomInteger() & 0x7fffffff;
    if( iKey==0 ) continue;
    key.p = (char*)&iKey;
    key.n = 4;
    go = ArrayFindElement(&pCursr->pTble->data, key)!=0;
  }
  return iKey;
}   
Changes to src/expr.c.
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions.
**
** $Id: expr.c,v 1.21 2001/01/15 22:51:10 drh Exp $
*/
#include "sqliteInt.h"

/*
** Walk an expression tree.  Return 1 if the expression is constant
** and 0 if it involves variables.
*/







|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions.
**
** $Id: expr.c,v 1.22 2001/04/04 11:48:58 drh Exp $
*/
#include "sqliteInt.h"

/*
** Walk an expression tree.  Return 1 if the expression is constant
** and 0 if it involves variables.
*/
80
81
82
83
84
85
86










87
88
89
90
91
92
93
      ExprList *pList = pExpr->pList;
      for(i=0; i<pList->nExpr; i++){
        sqliteExprResolveInSelect(pParse, pList->a[i].pExpr);
      }
    }
  }
}











/*
** This routine walks an expression tree and resolves references to
** table columns.  Nodes of the form ID.ID or ID resolve into an
** index to the table in the table list and a column offset.  The opcode
** for such nodes is changed to TK_COLUMN.  The iTable value is changed
** to the index of the referenced table in pTabList plus the pParse->nTab







>
>
>
>
>
>
>
>
>
>







80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
      ExprList *pList = pExpr->pList;
      for(i=0; i<pList->nExpr; i++){
        sqliteExprResolveInSelect(pParse, pList->a[i].pExpr);
      }
    }
  }
}

/*
** Return TRUE if the given string is a row-id column name.
*/
static int sqliteIsRowid(const char *z){
  if( sqliteStrICmp(z, "_ROWID_")==0 ) return 1;
  if( sqliteStrICmp(z, "ROWID")==0 ) return 1;
  if( sqliteStrICmp(z, "OID")==0 ) return 1;
  return 0;
}

/*
** This routine walks an expression tree and resolves references to
** table columns.  Nodes of the form ID.ID or ID resolve into an
** index to the table in the table list and a column offset.  The opcode
** for such nodes is changed to TK_COLUMN.  The iTable value is changed
** to the index of the referenced table in pTabList plus the pParse->nTab
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
** the number of errors seen and leaves an error message on pParse->zErrMsg.
*/
int sqliteExprResolveIds(Parse *pParse, IdList *pTabList, Expr *pExpr){
  if( pExpr==0 ) return 0;
  switch( pExpr->op ){
    /* A lone identifier */
    case TK_ID: {
      int cnt = 0;   /* Number of matches */
      int i;         /* Loop counter */

      char *z = sqliteStrNDup(pExpr->token.z, pExpr->token.n);
      for(i=0; i<pTabList->nId; i++){
        int j;
        Table *pTab = pTabList->a[i].pTab;
        if( pTab==0 ) continue;
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->aCol[j].zName, z)==0 ){
            cnt++;
            pExpr->iTable = i + pParse->nTab;
            pExpr->iColumn = j;
          }
        }





      }
      sqliteFree(z);
      if( cnt==0 ){
        sqliteSetNString(&pParse->zErrMsg, "no such column: ", -1,  
          pExpr->token.z, pExpr->token.n, 0);
        pParse->nErr++;
        return 1;
      }else if( cnt>1 ){
        sqliteSetNString(&pParse->zErrMsg, "ambiguous column name: ", -1,  
          pExpr->token.z, pExpr->token.n, 0);
        pParse->nErr++;
        return 1;
      }
      pExpr->op = TK_COLUMN;
      break; 
    }
  
    /* A table name and column name:  ID.ID */
    case TK_DOT: {
      int cnt = 0;             /* Number of matches */

      int i;                   /* Loop counter */
      Expr *pLeft, *pRight;    /* Left and right subbranches of the expr */
      char *zLeft, *zRight;    /* Text of an identifier */

      pLeft = pExpr->pLeft;
      pRight = pExpr->pRight;
      assert( pLeft && pLeft->op==TK_ID );
      assert( pRight && pRight->op==TK_ID );
      zLeft = sqliteStrNDup(pLeft->token.z, pLeft->token.n);
      zRight = sqliteStrNDup(pRight->token.z, pRight->token.n);

      for(i=0; i<pTabList->nId; i++){
        int j;
        char *zTab;
        Table *pTab = pTabList->a[i].pTab;
        if( pTab==0 ) continue;
        if( pTabList->a[i].zAlias ){
          zTab = pTabList->a[i].zAlias;
        }else{
          zTab = pTab->zName;
        }
        if( sqliteStrICmp(zTab, zLeft)!=0 ) continue;

        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->aCol[j].zName, zRight)==0 ){
            cnt++;
            pExpr->iTable = i + pParse->nTab;
            pExpr->iColumn = j;
          }
        }




      }
      sqliteFree(zLeft);
      sqliteFree(zRight);
      if( cnt==0 ){
        sqliteSetNString(&pParse->zErrMsg, "no such column: ", -1,  
          pLeft->token.z, pLeft->token.n, ".", 1, 
          pRight->token.z, pRight->token.n, 0);







|
|
>












>
>
>
>
>




















>










>











>







>
>
>
>







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
** the number of errors seen and leaves an error message on pParse->zErrMsg.
*/
int sqliteExprResolveIds(Parse *pParse, IdList *pTabList, Expr *pExpr){
  if( pExpr==0 ) return 0;
  switch( pExpr->op ){
    /* A lone identifier */
    case TK_ID: {
      int cnt = 0;      /* Number of matches */
      int i;            /* Loop counter */
      int isRowid = 0;  /* True if this is the ROWID column */
      char *z = sqliteStrNDup(pExpr->token.z, pExpr->token.n);
      for(i=0; i<pTabList->nId; i++){
        int j;
        Table *pTab = pTabList->a[i].pTab;
        if( pTab==0 ) continue;
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->aCol[j].zName, z)==0 ){
            cnt++;
            pExpr->iTable = i + pParse->nTab;
            pExpr->iColumn = j;
          }
        }
      }
      if( cnt==0 && sqliteIsRowid(z) ){
        pExpr->iColumn = -1;
        pExpr->iTable = pParse->nTab;
        cnt = 1 + (pTabList->nId>1);
      }
      sqliteFree(z);
      if( cnt==0 ){
        sqliteSetNString(&pParse->zErrMsg, "no such column: ", -1,  
          pExpr->token.z, pExpr->token.n, 0);
        pParse->nErr++;
        return 1;
      }else if( cnt>1 ){
        sqliteSetNString(&pParse->zErrMsg, "ambiguous column name: ", -1,  
          pExpr->token.z, pExpr->token.n, 0);
        pParse->nErr++;
        return 1;
      }
      pExpr->op = TK_COLUMN;
      break; 
    }
  
    /* A table name and column name:  ID.ID */
    case TK_DOT: {
      int cnt = 0;             /* Number of matches */
      int cntTab = 0;          /* Number of matching tables */
      int i;                   /* Loop counter */
      Expr *pLeft, *pRight;    /* Left and right subbranches of the expr */
      char *zLeft, *zRight;    /* Text of an identifier */

      pLeft = pExpr->pLeft;
      pRight = pExpr->pRight;
      assert( pLeft && pLeft->op==TK_ID );
      assert( pRight && pRight->op==TK_ID );
      zLeft = sqliteStrNDup(pLeft->token.z, pLeft->token.n);
      zRight = sqliteStrNDup(pRight->token.z, pRight->token.n);
      pExpr->iTable = -1;
      for(i=0; i<pTabList->nId; i++){
        int j;
        char *zTab;
        Table *pTab = pTabList->a[i].pTab;
        if( pTab==0 ) continue;
        if( pTabList->a[i].zAlias ){
          zTab = pTabList->a[i].zAlias;
        }else{
          zTab = pTab->zName;
        }
        if( sqliteStrICmp(zTab, zLeft)!=0 ) continue;
        if( 0==(cntTab++) ) pExpr->iTable = i + pParse->nTab;
        for(j=0; j<pTab->nCol; j++){
          if( sqliteStrICmp(pTab->aCol[j].zName, zRight)==0 ){
            cnt++;
            pExpr->iTable = i + pParse->nTab;
            pExpr->iColumn = j;
          }
        }
      }
      if( cnt==0 && cntTab==1 && sqliteIsRowid(zRight) ){
        cnt = 1;
        pExpr->iColumn = -1;
      }
      sqliteFree(zLeft);
      sqliteFree(zRight);
      if( cnt==0 ){
        sqliteSetNString(&pParse->zErrMsg, "no such column: ", -1,  
          pLeft->token.z, pLeft->token.n, ".", 1, 
          pRight->token.z, pRight->token.n, 0);
479
480
481
482
483
484
485


486
487
488
489
490
491
492
493
494
    case TK_UMINUS:   op = OP_Negative; break;
    default: break;
  }
  switch( pExpr->op ){
    case TK_COLUMN: {
      if( pParse->useAgg ){
        sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg, 0, 0);


      }else{
        sqliteVdbeAddOp(v, OP_Field, pExpr->iTable, pExpr->iColumn, 0, 0);
      }
      break;
    }
    case TK_INTEGER: {
      int i = atoi(pExpr->token.z);
      sqliteVdbeAddOp(v, OP_Integer, i, 0, 0, 0);
      break;







>
>

|







502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
    case TK_UMINUS:   op = OP_Negative; break;
    default: break;
  }
  switch( pExpr->op ){
    case TK_COLUMN: {
      if( pParse->useAgg ){
        sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg, 0, 0);
      }else if( pExpr->iColumn>=0 ){
        sqliteVdbeAddOp(v, OP_Field, pExpr->iTable, pExpr->iColumn, 0, 0);
      }else{
        sqliteVdbeAddOp(v, OP_Key, pExpr->iTable, 0, 0, 0);
      }
      break;
    }
    case TK_INTEGER: {
      int i = atoi(pExpr->token.z);
      sqliteVdbeAddOp(v, OP_Integer, i, 0, 0, 0);
      break;
Changes to src/parse.y.
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
**
*************************************************************************
** 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.26 2001/01/04 14:20:18 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
  pParse->sErrToken = TOKEN;
}
%name sqliteParser
%include {
#include "sqliteInt.h"
#include "parse.h"
}


// Input is zero or more commands.
input ::= cmdlist.

// These are extra tokens used by the lexer but never seen by the
// parser.  We put them in a rule so that the parser generator will
// add them to the parse.h output file.
//
input ::= END_OF_FILE ILLEGAL SPACE UNCLOSED_STRING COMMENT FUNCTION
          UMINUS COLUMN AGG_FUNCTION.




// A list of commands is zero or more commands
//
cmdlist ::= ecmd.
cmdlist ::= cmdlist SEMI ecmd.
ecmd ::= explain cmd.  {sqliteExec(pParse);}
ecmd ::= cmd.          {sqliteExec(pParse);}
ecmd ::= .
explain ::= EXPLAIN.    {pParse->explain = 1;}













// The first form of a command is a CREATE TABLE statement.
//
cmd ::= create_table create_table_args.
create_table ::= CREATE(X) TABLE id(Y).    {sqliteStartTable(pParse,&X,&Y);}
create_table_args ::= LP columnlist conslist_opt RP(X).
                                           {sqliteEndTable(pParse,&X);}
columnlist ::= columnlist COMMA column.
columnlist ::= column.

// About the only information used for a column is the name of the
// column.  The type is always just "text".  But the code will accept
// an elaborate typename.  Perhaps someday we'll do something with it.
//
column ::= columnid type carglist. 
columnid ::= id(X).                {sqliteAddColumn(pParse,&X);}





%type id {Token}







id(A) ::= ID(X).     {A = X;}





id(A) ::= STRING(X). {A = X;}

type ::= typename.
type ::= typename LP signed RP.
type ::= typename LP signed COMMA signed RP.
typename ::= id.
typename ::= typename id.
signed ::= INTEGER.
signed ::= PLUS INTEGER.
signed ::= MINUS INTEGER.
carglist ::= carglist carg.
carglist ::= .
carg ::= CONSTRAINT id ccons.
carg ::= ccons.
carg ::= DEFAULT STRING(X).          {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT ID(X).              {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT INTEGER(X).         {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT PLUS INTEGER(X).    {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT MINUS INTEGER(X).   {sqliteAddDefaultValue(pParse,&X,1);}
carg ::= DEFAULT FLOAT(X).           {sqliteAddDefaultValue(pParse,&X,0);}







|














<
<
<
<




|
|
>
>
>










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



|










|
>
>
>
>
>

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



|
|





|







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
**
*************************************************************************
** 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.27 2001/04/04 11:48:58 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
  pParse->sErrToken = TOKEN;
}
%name sqliteParser
%include {
#include "sqliteInt.h"
#include "parse.h"
}





// These are extra tokens used by the lexer but never seen by the
// parser.  We put them in a rule so that the parser generator will
// add them to the parse.h output file.
//
%nonassoc END_OF_FILE ILLEGAL SPACE UNCLOSED_STRING COMMENT FUNCTION
          COLUMN AGG_FUNCTION.

// Input is zero or more commands.
input ::= cmdlist.

// A list of commands is zero or more commands
//
cmdlist ::= ecmd.
cmdlist ::= cmdlist SEMI ecmd.
ecmd ::= explain cmd.  {sqliteExec(pParse);}
ecmd ::= cmd.          {sqliteExec(pParse);}
ecmd ::= .
explain ::= EXPLAIN.    {pParse->explain = 1;}

// Begin and end transactions.  Transaction support is sparse.
// Some backends support only COMMIT and not ROLLBACK.  There can
// be only a single active transaction at a time.
//
cmd ::= BEGIN trans_opt.       {sqliteBeginTransaction(pParse);}
trans_opt ::= .
trans_opt ::= TRANSACTION.
trans_opt ::= TRANSACTION ids.
cmd ::= COMMIT trans_opt.      {sqliteCommitTransaction(pParse);}
cmd ::= END trans_opt.         {sqliteCommitTransaction(pParse);}
cmd ::= ROLLBACK trans_opt.    {sqliteRollbackTransaction(pParse);}

// The first form of a command is a CREATE TABLE statement.
//
cmd ::= create_table create_table_args.
create_table ::= CREATE(X) TABLE ids(Y).   {sqliteStartTable(pParse,&X,&Y);}
create_table_args ::= LP columnlist conslist_opt RP(X).
                                           {sqliteEndTable(pParse,&X);}
columnlist ::= columnlist COMMA column.
columnlist ::= column.

// About the only information used for a column is the name of the
// column.  The type is always just "text".  But the code will accept
// an elaborate typename.  Perhaps someday we'll do something with it.
//
column ::= columnid type carglist. 
columnid ::= ids(X).                {sqliteAddColumn(pParse,&X);}

// An IDENTIFIER can be a generic identifier, or one of several
// keywords.  Any non-standard keyword can also be an identifier.
// We also make DESC and identifier since it comes up so often.
//
%type id {Token}
id(A) ::= DESC(X).       {A = X;}
id(A) ::= ASC(X).        {A = X;}
id(A) ::= DELIMITERS(X). {A = X;}
id(A) ::= EXPLAIN(X).    {A = X;}
id(A) ::= VACUUM(X).     {A = X;}
id(A) ::= BEGIN(X).      {A = X;}
id(A) ::= END(X).        {A = X;}
id(A) ::= ID(X).         {A = X;}

// And "ids" is an identifer-or-string.
//
%type ids {Token}
ids(A) ::= id(X).        {A = X;}
ids(A) ::= STRING(X).    {A = X;}

type ::= typename.
type ::= typename LP signed RP.
type ::= typename LP signed COMMA signed RP.
typename ::= ids.
typename ::= typename ids.
signed ::= INTEGER.
signed ::= PLUS INTEGER.
signed ::= MINUS INTEGER.
carglist ::= carglist carg.
carglist ::= .
carg ::= CONSTRAINT ids ccons.
carg ::= ccons.
carg ::= DEFAULT STRING(X).          {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT ID(X).              {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT INTEGER(X).         {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT PLUS INTEGER(X).    {sqliteAddDefaultValue(pParse,&X,0);}
carg ::= DEFAULT MINUS INTEGER(X).   {sqliteAddDefaultValue(pParse,&X,1);}
carg ::= DEFAULT FLOAT(X).           {sqliteAddDefaultValue(pParse,&X,0);}
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
// key.
//
conslist_opt ::= .
conslist_opt ::= COMMA conslist.
conslist ::= conslist COMMA tcons.
conslist ::= conslist tcons.
conslist ::= tcons.
tcons ::= CONSTRAINT id.
tcons ::= PRIMARY KEY LP idxlist(X) RP. {sqliteCreateIndex(pParse,0,0,X,0,0);}
tcons ::= UNIQUE LP idlist RP.
tcons ::= CHECK expr.
idlist ::= idlist COMMA id.
idlist ::= id.

// The next command format is dropping tables.
//
cmd ::= DROP TABLE id(X).          {sqliteDropTable(pParse,&X);}

// The select statement
//
cmd ::= select(X).  {
  sqliteSelect(pParse, X, SRT_Callback, 0);
  sqliteSelectDelete(X);
}







|



|
|



|







141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
// key.
//
conslist_opt ::= .
conslist_opt ::= COMMA conslist.
conslist ::= conslist COMMA tcons.
conslist ::= conslist tcons.
conslist ::= tcons.
tcons ::= CONSTRAINT ids.
tcons ::= PRIMARY KEY LP idxlist(X) RP. {sqliteCreateIndex(pParse,0,0,X,0,0);}
tcons ::= UNIQUE LP idlist RP.
tcons ::= CHECK expr.
idlist ::= idlist COMMA ids.
idlist ::= ids.

// The next command format is dropping tables.
//
cmd ::= DROP TABLE ids(X).          {sqliteDropTable(pParse,&X);}

// The select statement
//
cmd ::= select(X).  {
  sqliteSelect(pParse, X, SRT_Callback, 0);
  sqliteSelectDelete(X);
}
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
%destructor selcollist {sqliteExprListDelete($$);}
%type sclp {ExprList*}
%destructor sclp {sqliteExprListDelete($$);}
sclp(A) ::= selcollist(X) COMMA.             {A = X;}
sclp(A) ::= .                                {A = 0;}
selcollist(A) ::= STAR.                      {A = 0;}
selcollist(A) ::= sclp(P) expr(X).           {A = sqliteExprListAppend(P,X,0);}
selcollist(A) ::= sclp(P) expr(X) as id(Y).  {A = sqliteExprListAppend(P,X,&Y);}
as ::= .
as ::= AS.


%type seltablist {IdList*}
%destructor seltablist {sqliteIdListDelete($$);}
%type stl_prefix {IdList*}
%destructor stl_prefix {sqliteIdListDelete($$);}
%type from {IdList*}
%destructor from {sqliteIdListDelete($$);}

from(A) ::= FROM seltablist(X).               {A = X;}
stl_prefix(A) ::= seltablist(X) COMMA.        {A = X;}
stl_prefix(A) ::= .                           {A = 0;}
seltablist(A) ::= stl_prefix(X) id(Y).        {A = sqliteIdListAppend(X,&Y);}
seltablist(A) ::= stl_prefix(X) id(Y) as id(Z).
   {A = sqliteIdListAppend(X,&Y);
    sqliteIdListAddAlias(A,&Z);}


%type orderby_opt {ExprList*}
%destructor orderby_opt {sqliteExprListDelete($$);}
%type sortlist {ExprList*}
%destructor sortlist {sqliteExprListDelete($$);}
%type sortitem {Expr*}
%destructor sortitem {sqliteExprDelete($$);}







|














|
|
|
|
>







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
%destructor selcollist {sqliteExprListDelete($$);}
%type sclp {ExprList*}
%destructor sclp {sqliteExprListDelete($$);}
sclp(A) ::= selcollist(X) COMMA.             {A = X;}
sclp(A) ::= .                                {A = 0;}
selcollist(A) ::= STAR.                      {A = 0;}
selcollist(A) ::= sclp(P) expr(X).           {A = sqliteExprListAppend(P,X,0);}
selcollist(A) ::= sclp(P) expr(X) as ids(Y). {A = sqliteExprListAppend(P,X,&Y);}
as ::= .
as ::= AS.


%type seltablist {IdList*}
%destructor seltablist {sqliteIdListDelete($$);}
%type stl_prefix {IdList*}
%destructor stl_prefix {sqliteIdListDelete($$);}
%type from {IdList*}
%destructor from {sqliteIdListDelete($$);}

from(A) ::= FROM seltablist(X).               {A = X;}
stl_prefix(A) ::= seltablist(X) COMMA.        {A = X;}
stl_prefix(A) ::= .                           {A = 0;}
seltablist(A) ::= stl_prefix(X) ids(Y).       {A = sqliteIdListAppend(X,&Y);}
seltablist(A) ::= stl_prefix(X) ids(Y) as ids(Z). {
  A = sqliteIdListAppend(X,&Y);
  sqliteIdListAddAlias(A,&Z);
}

%type orderby_opt {ExprList*}
%destructor orderby_opt {sqliteExprListDelete($$);}
%type sortlist {ExprList*}
%destructor sortlist {sqliteExprListDelete($$);}
%type sortitem {Expr*}
%destructor sortitem {sqliteExprDelete($$);}
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

%type having_opt {Expr*}
%destructor having_opt {sqliteExprDelete($$);}
having_opt(A) ::= .                {A = 0;}
having_opt(A) ::= HAVING expr(X).  {A = X;}


cmd ::= DELETE FROM id(X) where_opt(Y).
    {sqliteDeleteFrom(pParse, &X, Y);}

%type where_opt {Expr*}
%destructor where_opt {sqliteExprDelete($$);}

where_opt(A) ::= .                    {A = 0;}
where_opt(A) ::= WHERE expr(X).       {A = X;}

%type setlist {ExprList*}
%destructor setlist {sqliteExprListDelete($$);}

cmd ::= UPDATE id(X) SET setlist(Y) where_opt(Z).
    {sqliteUpdate(pParse,&X,Y,Z);}

setlist(A) ::= setlist(Z) COMMA id(X) EQ expr(Y).
    {A = sqliteExprListAppend(Z,Y,&X);}
setlist(A) ::= id(X) EQ expr(Y).   {A = sqliteExprListAppend(0,Y,&X);}

cmd ::= INSERT INTO id(X) inscollist_opt(F) VALUES LP itemlist(Y) RP.
               {sqliteInsert(pParse, &X, Y, 0, F);}
cmd ::= INSERT INTO id(X) inscollist_opt(F) select(S).
               {sqliteInsert(pParse, &X, 0, S, F);}


%type itemlist {ExprList*}
%destructor itemlist {sqliteExprListDelete($$);}
%type item {Expr*}
%destructor item {sqliteExprDelete($$);}







|











|


|

|

|

|







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

%type having_opt {Expr*}
%destructor having_opt {sqliteExprDelete($$);}
having_opt(A) ::= .                {A = 0;}
having_opt(A) ::= HAVING expr(X).  {A = X;}


cmd ::= DELETE FROM ids(X) where_opt(Y).
    {sqliteDeleteFrom(pParse, &X, Y);}

%type where_opt {Expr*}
%destructor where_opt {sqliteExprDelete($$);}

where_opt(A) ::= .                    {A = 0;}
where_opt(A) ::= WHERE expr(X).       {A = X;}

%type setlist {ExprList*}
%destructor setlist {sqliteExprListDelete($$);}

cmd ::= UPDATE ids(X) SET setlist(Y) where_opt(Z).
    {sqliteUpdate(pParse,&X,Y,Z);}

setlist(A) ::= setlist(Z) COMMA ids(X) EQ expr(Y).
    {A = sqliteExprListAppend(Z,Y,&X);}
setlist(A) ::= ids(X) EQ expr(Y).   {A = sqliteExprListAppend(0,Y,&X);}

cmd ::= INSERT INTO ids(X) inscollist_opt(F) VALUES LP itemlist(Y) RP.
               {sqliteInsert(pParse, &X, Y, 0, F);}
cmd ::= INSERT INTO ids(X) inscollist_opt(F) select(S).
               {sqliteInsert(pParse, &X, 0, S, F);}


%type itemlist {ExprList*}
%destructor itemlist {sqliteExprListDelete($$);}
%type item {Expr*}
%destructor item {sqliteExprDelete($$);}
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
311
312
313
314
item(A) ::= NULL.            {A = sqliteExpr(TK_NULL, 0, 0, 0);}

%type inscollist_opt {IdList*}
%destructor inscollist_opt {sqliteIdListDelete($$);}
%type inscollist {IdList*}
%destructor inscollist {sqliteIdListDelete($$);}

inscollist_opt(A) ::= .                      {A = 0;}
inscollist_opt(A) ::= LP inscollist(X) RP.   {A = X;}
inscollist(A) ::= inscollist(X) COMMA id(Y). {A = sqliteIdListAppend(X,&Y);}
inscollist(A) ::= id(Y).                     {A = sqliteIdListAppend(0,&Y);}

%left OR.
%left AND.
%right NOT.
%left EQ NE ISNULL NOTNULL IS LIKE GLOB BETWEEN IN.
%left GT GE LT LE.
%left PLUS MINUS.
%left STAR SLASH.
%left CONCAT.
%right UMINUS.

%type expr {Expr*}
%destructor expr {sqliteExprDelete($$);}

expr(A) ::= LP(B) expr(X) RP(E). {A = X; sqliteExprSpan(A,&B,&E);}
expr(A) ::= ID(X).               {A = sqliteExpr(TK_ID, 0, 0, &X);}
expr(A) ::= NULL(X).             {A = sqliteExpr(TK_NULL, 0, 0, &X);}
expr(A) ::= id(X) DOT id(Y). {

  Expr *temp1 = sqliteExpr(TK_ID, 0, 0, &X);
  Expr *temp2 = sqliteExpr(TK_ID, 0, 0, &Y);
  A = sqliteExpr(TK_DOT, temp1, temp2, 0);
}
expr(A) ::= INTEGER(X).      {A = sqliteExpr(TK_INTEGER, 0, 0, &X);}
expr(A) ::= FLOAT(X).        {A = sqliteExpr(TK_FLOAT, 0, 0, &X);}
expr(A) ::= STRING(X).       {A = sqliteExpr(TK_STRING, 0, 0, &X);}







|
|
|
|















<

|
>







309
310
311
312
313
314
315
316
317
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
343
344
item(A) ::= NULL.            {A = sqliteExpr(TK_NULL, 0, 0, 0);}

%type inscollist_opt {IdList*}
%destructor inscollist_opt {sqliteIdListDelete($$);}
%type inscollist {IdList*}
%destructor inscollist {sqliteIdListDelete($$);}

inscollist_opt(A) ::= .                       {A = 0;}
inscollist_opt(A) ::= LP inscollist(X) RP.    {A = X;}
inscollist(A) ::= inscollist(X) COMMA ids(Y). {A = sqliteIdListAppend(X,&Y);}
inscollist(A) ::= ids(Y).                     {A = sqliteIdListAppend(0,&Y);}

%left OR.
%left AND.
%right NOT.
%left EQ NE ISNULL NOTNULL IS LIKE GLOB BETWEEN IN.
%left GT GE LT LE.
%left PLUS MINUS.
%left STAR SLASH.
%left CONCAT.
%right UMINUS.

%type expr {Expr*}
%destructor expr {sqliteExprDelete($$);}

expr(A) ::= LP(B) expr(X) RP(E). {A = X; sqliteExprSpan(A,&B,&E);}

expr(A) ::= NULL(X).             {A = sqliteExpr(TK_NULL, 0, 0, &X);}
expr(A) ::= id(X).               {A = sqliteExpr(TK_ID, 0, 0, &X);}
expr(A) ::= ids(X) DOT ids(Y). {
  Expr *temp1 = sqliteExpr(TK_ID, 0, 0, &X);
  Expr *temp2 = sqliteExpr(TK_ID, 0, 0, &Y);
  A = sqliteExpr(TK_DOT, temp1, temp2, 0);
}
expr(A) ::= INTEGER(X).      {A = sqliteExpr(TK_INTEGER, 0, 0, &X);}
expr(A) ::= FLOAT(X).        {A = sqliteExpr(TK_FLOAT, 0, 0, &X);}
expr(A) ::= STRING(X).       {A = sqliteExpr(TK_STRING, 0, 0, &X);}
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
exprlist(A) ::= exprlist(X) COMMA expritem(Y). 
   {A = sqliteExprListAppend(X,Y,0);}
exprlist(A) ::= expritem(X).            {A = sqliteExprListAppend(0,X,0);}
expritem(A) ::= expr(X).                {A = X;}
expritem(A) ::= .                       {A = 0;}


cmd ::= CREATE(S) uniqueflag INDEX id(X) ON id(Y) LP idxlist(Z) RP(E).
    {sqliteCreateIndex(pParse, &X, &Y, Z, &S, &E);}
uniqueflag ::= UNIQUE.
uniqueflag ::= .

%type idxlist {IdList*}
%destructor idxlist {sqliteIdListDelete($$);}
%type idxitem {Token}

idxlist(A) ::= idxlist(X) COMMA idxitem(Y).  
     {A = sqliteIdListAppend(X,&Y);}
idxlist(A) ::= idxitem(Y).
     {A = sqliteIdListAppend(0,&Y);}
idxitem(A) ::= id(X).           {A = X;}

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

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

cmd ::= VACUUM.                {sqliteVacuum(pParse,0);}
cmd ::= VACUUM id(X).          {sqliteVacuum(pParse,&X);}







|












|

|

|

|



|
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
exprlist(A) ::= exprlist(X) COMMA expritem(Y). 
   {A = sqliteExprListAppend(X,Y,0);}
exprlist(A) ::= expritem(X).            {A = sqliteExprListAppend(0,X,0);}
expritem(A) ::= expr(X).                {A = X;}
expritem(A) ::= .                       {A = 0;}


cmd ::= CREATE(S) uniqueflag INDEX ids(X) ON ids(Y) LP idxlist(Z) RP(E).
    {sqliteCreateIndex(pParse, &X, &Y, Z, &S, &E);}
uniqueflag ::= UNIQUE.
uniqueflag ::= .

%type idxlist {IdList*}
%destructor idxlist {sqliteIdListDelete($$);}
%type idxitem {Token}

idxlist(A) ::= idxlist(X) COMMA idxitem(Y).  
     {A = sqliteIdListAppend(X,&Y);}
idxlist(A) ::= idxitem(Y).
     {A = sqliteIdListAppend(0,&Y);}
idxitem(A) ::= ids(X).          {A = X;}

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

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);}

cmd ::= VACUUM.                {sqliteVacuum(pParse,0);}
cmd ::= VACUUM ids(X).         {sqliteVacuum(pParse,&X);}
Changes to src/select.c.
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements.
**
** $Id: select.c,v 1.29 2001/02/19 23:23:38 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/







|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements.
**
** $Id: select.c,v 1.30 2001/04/04 11:48:58 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
616
617
618
619
620
621
622
623


624
625
626
627
628
629
630
**
**     SRT_Mem         Store first result in memory cell iParm
**
**     SRT_Set         Store results as keys of a table with cursor iParm
**
**     SRT_Union       Store results as a key in a temporary table iParm
**
**     SRT_Except      Remove results form the temporary talbe iParm.


**
** This routine returns the number of errors.  If any errors are
** encountered, then an appropriate error message is left in
** pParse->zErrMsg.
**
** This routine does NOT free the Select structure passed in.  The
** calling function needs to do that.







|
>
>







616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
**
**     SRT_Mem         Store first result in memory cell iParm
**
**     SRT_Set         Store results as keys of a table with cursor iParm
**
**     SRT_Union       Store results as a key in a temporary table iParm
**
**     SRT_Except      Remove results form the temporary table iParm.
**
**     SRT_Table       Store results in temporary table iParm
**
** This routine returns the number of errors.  If any errors are
** encountered, then an appropriate error message is left in
** pParse->zErrMsg.
**
** This routine does NOT free the Select structure passed in.  The
** calling function needs to do that.
Changes to src/sqliteInt.h.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.37 2001/03/20 22:05:00 drh Exp $
*/
#include "sqlite.h"
#include "dbbe.h"
#include "vdbe.h"
#include "parse.h"
#include <gdbm.h>
#include <stdio.h>







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.38 2001/04/04 11:48:58 drh Exp $
*/
#include "sqlite.h"
#include "dbbe.h"
#include "vdbe.h"
#include "parse.h"
#include <gdbm.h>
#include <stdio.h>
140
141
142
143
144
145
146

147
148
149
150
151
152
153

/*
** Possible values for the sqlite.flags.
*/
#define SQLITE_VdbeTrace    0x00000001  /* True to trace VDBE execution */
#define SQLITE_Initialized  0x00000002  /* True after initialization */
#define SQLITE_Interrupt    0x00000004  /* Cancel current operation */


/*
** Current file format version
*/
#define SQLITE_FileFormat 2

/*







>







140
141
142
143
144
145
146
147
148
149
150
151
152
153
154

/*
** Possible values for the sqlite.flags.
*/
#define SQLITE_VdbeTrace    0x00000001  /* True to trace VDBE execution */
#define SQLITE_Initialized  0x00000002  /* True after initialization */
#define SQLITE_Interrupt    0x00000004  /* Cancel current operation */
#define SQLITE_InTrans      0x00000008  /* True if in a transaction */

/*
** Current file format version
*/
#define SQLITE_FileFormat 2

/*
424
425
426
427
428
429
430



int sqliteExprAnalyzeAggregates(Parse*, Expr*);
void sqliteParseInfoReset(Parse*);
Vdbe *sqliteGetVdbe(Parse*);
int sqliteRandomByte(void);
int sqliteRandomInteger(void);
void sqliteRandomName(char*,char*);
char *sqliteDbbeNameToFile(const char*,const char*,const char*);










>
>
>
425
426
427
428
429
430
431
432
433
434
int sqliteExprAnalyzeAggregates(Parse*, Expr*);
void sqliteParseInfoReset(Parse*);
Vdbe *sqliteGetVdbe(Parse*);
int sqliteRandomByte(void);
int sqliteRandomInteger(void);
void sqliteRandomName(char*,char*);
char *sqliteDbbeNameToFile(const char*,const char*,const char*);
void sqliteBeginTransaction(Parse*);
void sqliteCommitTransaction(Parse*);
void sqliteRollbackTransaction(Parse*);
Changes to src/tokenize.c.
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
*************************************************************************
** 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.17 2001/02/11 16:56:24 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
#include <stdlib.h>

/*
** All the keywords of the SQL language are stored as in a hash







|







23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
*************************************************************************
** 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.18 2001/04/04 11:48:58 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>
#include <stdlib.h>

/*
** All the keywords of the SQL language are stored as in a hash
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
** These are the keywords
*/
static Keyword aKeywordTable[] = {
  { "ALL",               0, TK_ALL,              0 },
  { "AND",               0, TK_AND,              0 },
  { "AS",                0, TK_AS,               0 },
  { "ASC",               0, TK_ASC,              0 },

  { "BETWEEN",           0, TK_BETWEEN,          0 },
  { "BY",                0, TK_BY,               0 },
  { "CHECK",             0, TK_CHECK,            0 },

  { "CONSTRAINT",        0, TK_CONSTRAINT,       0 },
  { "COPY",              0, TK_COPY,             0 },
  { "CREATE",            0, TK_CREATE,           0 },
  { "DEFAULT",           0, TK_DEFAULT,          0 },
  { "DELETE",            0, TK_DELETE,           0 },
  { "DELIMITERS",        0, TK_DELIMITERS,       0 },
  { "DESC",              0, TK_DESC,             0 },
  { "DISTINCT",          0, TK_DISTINCT,         0 },
  { "DROP",              0, TK_DROP,             0 },

  { "EXCEPT",            0, TK_EXCEPT,           0 },
  { "EXPLAIN",           0, TK_EXPLAIN,          0 },
  { "FROM",              0, TK_FROM,             0 },
  { "GLOB",              0, TK_GLOB,             0 },
  { "GROUP",             0, TK_GROUP,            0 },
  { "HAVING",            0, TK_HAVING,           0 },
  { "IN",                0, TK_IN,               0 },







>



>









>







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
** These are the keywords
*/
static Keyword aKeywordTable[] = {
  { "ALL",               0, TK_ALL,              0 },
  { "AND",               0, TK_AND,              0 },
  { "AS",                0, TK_AS,               0 },
  { "ASC",               0, TK_ASC,              0 },
  { "BEGIN",             0, TK_BEGIN,            0 },
  { "BETWEEN",           0, TK_BETWEEN,          0 },
  { "BY",                0, TK_BY,               0 },
  { "CHECK",             0, TK_CHECK,            0 },
  { "COMMIT",            0, TK_COMMIT,           0 },
  { "CONSTRAINT",        0, TK_CONSTRAINT,       0 },
  { "COPY",              0, TK_COPY,             0 },
  { "CREATE",            0, TK_CREATE,           0 },
  { "DEFAULT",           0, TK_DEFAULT,          0 },
  { "DELETE",            0, TK_DELETE,           0 },
  { "DELIMITERS",        0, TK_DELIMITERS,       0 },
  { "DESC",              0, TK_DESC,             0 },
  { "DISTINCT",          0, TK_DISTINCT,         0 },
  { "DROP",              0, TK_DROP,             0 },
  { "END",               0, TK_END,              0 },
  { "EXCEPT",            0, TK_EXCEPT,           0 },
  { "EXPLAIN",           0, TK_EXPLAIN,          0 },
  { "FROM",              0, TK_FROM,             0 },
  { "GLOB",              0, TK_GLOB,             0 },
  { "GROUP",             0, TK_GROUP,            0 },
  { "HAVING",            0, TK_HAVING,           0 },
  { "IN",                0, TK_IN,               0 },
83
84
85
86
87
88
89

90
91
92

93
94
95
96
97
98
99
  { "NOT",               0, TK_NOT,              0 },
  { "NOTNULL",           0, TK_NOTNULL,          0 },
  { "NULL",              0, TK_NULL,             0 },
  { "ON",                0, TK_ON,               0 },
  { "OR",                0, TK_OR,               0 },
  { "ORDER",             0, TK_ORDER,            0 },
  { "PRIMARY",           0, TK_PRIMARY,          0 },

  { "SELECT",            0, TK_SELECT,           0 },
  { "SET",               0, TK_SET,              0 },
  { "TABLE",             0, TK_TABLE,            0 },

  { "UNION",             0, TK_UNION,            0 },
  { "UNIQUE",            0, TK_UNIQUE,           0 },
  { "UPDATE",            0, TK_UPDATE,           0 },
  { "USING",             0, TK_USING,            0 },
  { "VACUUM",            0, TK_VACUUM,           0 },
  { "VALUES",            0, TK_VALUES,           0 },
  { "WHERE",             0, TK_WHERE,            0 },







>



>







86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
  { "NOT",               0, TK_NOT,              0 },
  { "NOTNULL",           0, TK_NOTNULL,          0 },
  { "NULL",              0, TK_NULL,             0 },
  { "ON",                0, TK_ON,               0 },
  { "OR",                0, TK_OR,               0 },
  { "ORDER",             0, TK_ORDER,            0 },
  { "PRIMARY",           0, TK_PRIMARY,          0 },
  { "ROLLBACK",          0, TK_ROLLBACK,         0 },
  { "SELECT",            0, TK_SELECT,           0 },
  { "SET",               0, TK_SET,              0 },
  { "TABLE",             0, TK_TABLE,            0 },
  { "TRANSACTION",       0, TK_TRANSACTION,      0 },
  { "UNION",             0, TK_UNION,            0 },
  { "UNIQUE",            0, TK_UNIQUE,           0 },
  { "UPDATE",            0, TK_UPDATE,           0 },
  { "USING",             0, TK_USING,            0 },
  { "VACUUM",            0, TK_VACUUM,           0 },
  { "VALUES",            0, TK_VALUES,           0 },
  { "WHERE",             0, TK_WHERE,            0 },
Changes to src/vdbe.c.
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
** inplicit conversion from one 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.53 2001/03/20 22:05:00 drh Exp $
*/
#include "sqliteInt.h"
#include <unistd.h>
#include <ctype.h>

/*
** SQL is translated into a sequence of instructions to be







|







37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
** inplicit conversion from one 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.54 2001/04/04 11:48:58 drh Exp $
*/
#include "sqliteInt.h"
#include <unistd.h>
#include <ctype.h>

/*
** SQL is translated into a sequence of instructions to be
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
**
** If any of the numeric OP_ values for opcodes defined in sqliteVdbe.h
** change, be sure to change this array to match.  You can use the
** "opNames.awk" awk script which is part of the source tree to regenerate
** this array, then copy and paste it into this file, if you want.
*/
static char *zOpName[] = { 0,
  "OpenIdx",        "OpenTbl",        "Close",          "Fetch",
  "Fcnt",           "New",            "Put",            "Distinct",
  "Found",          "NotFound",       "Delete",         "Field",
  "KeyAsData",      "Key",            "FullKey",        "Rewind",
  "Next",           "Destroy",        "Reorganize",     "ResetIdx",
  "NextIdx",        "PutIdx",         "DeleteIdx",      "MemLoad",
  "MemStore",       "ListOpen",       "ListWrite",      "ListRewind",
  "ListRead",       "ListClose",      "SortOpen",       "SortPut",
  "SortMakeRec",    "SortMakeKey",    "Sort",           "SortNext",
  "SortKey",        "SortCallback",   "SortClose",      "FileOpen",
  "FileRead",       "FileField",      "FileClose",      "AggReset",
  "AggFocus",       "AggIncr",        "AggNext",        "AggSet",
  "AggGet",         "SetInsert",      "SetFound",       "SetNotFound",
  "SetClear",       "MakeRecord",     "MakeKey",        "Goto",
  "If",             "Halt",           "ColumnCount",    "ColumnName",
  "Callback",       "Integer",        "String",         "Null",
  "Pop",            "Dup",            "Pull",           "Add",
  "AddImm",         "Subtract",       "Multiply",       "Divide",
  "Min",            "Max",            "Like",           "Glob",
  "Eq",             "Ne",             "Lt",             "Le",
  "Gt",             "Ge",             "IsNull",         "NotNull",
  "Negative",       "And",            "Or",             "Not",
  "Concat",         "Noop",           "Strlen",         "Substr",
};

/*
** Given the name of an opcode, return its number.  Return 0 if
** there is no match.
**
** This routine is used for testing and debugging.







|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|







807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
**
** If any of the numeric OP_ values for opcodes defined in sqliteVdbe.h
** change, be sure to change this array to match.  You can use the
** "opNames.awk" awk script which is part of the source tree to regenerate
** this array, then copy and paste it into this file, if you want.
*/
static char *zOpName[] = { 0,
  "OpenIdx",           "OpenTbl",           "Close",             "Fetch",
  "Fcnt",              "New",               "Put",               "Distinct",
  "Found",             "NotFound",          "Delete",            "Field",
  "KeyAsData",         "Key",               "FullKey",           "Rewind",
  "Next",              "Destroy",           "Reorganize",        "ResetIdx",
  "NextIdx",           "PutIdx",            "DeleteIdx",         "MemLoad",
  "MemStore",          "ListOpen",          "ListWrite",         "ListRewind",
  "ListRead",          "ListClose",         "SortOpen",          "SortPut",
  "SortMakeRec",       "SortMakeKey",       "Sort",              "SortNext",
  "SortKey",           "SortCallback",      "SortClose",         "FileOpen",
  "FileRead",          "FileField",         "FileClose",         "AggReset",
  "AggFocus",          "AggIncr",           "AggNext",           "AggSet",
  "AggGet",            "SetInsert",         "SetFound",          "SetNotFound",
  "SetClear",          "MakeRecord",        "MakeKey",           "Goto",
  "If",                "Halt",              "ColumnCount",       "ColumnName",
  "Callback",          "Integer",           "String",            "Null",
  "Pop",               "Dup",               "Pull",              "Add",
  "AddImm",            "Subtract",          "Multiply",          "Divide",
  "Min",               "Max",               "Like",              "Glob",
  "Eq",                "Ne",                "Lt",                "Le",
  "Gt",                "Ge",                "IsNull",            "NotNull",
  "Negative",          "And",               "Or",                "Not",
  "Concat",            "Noop",              "Strlen",            "Substr",
};

/*
** Given the name of an opcode, return its number.  Return 0 if
** there is no match.
**
** This routine is used for testing and debugging.
Changes to src/vdbe.h.
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.16 2001/02/19 23:23:39 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines







|







23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.17 2001/04/04 11:48:58 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
#define OP_Not                88
#define OP_Concat             89
#define OP_Noop               90

#define OP_Strlen             91
#define OP_Substr             92

#define OP_MAX                92

/*
** Prototypes for the VDBE interface.  See comments on the implementation
** for a description of what each of these routines does.
*/
Vdbe *sqliteVdbeCreate(sqlite*);
int sqliteVdbeAddOp(Vdbe*,int,int,int,const char*,int);







|







174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
#define OP_Not                88
#define OP_Concat             89
#define OP_Noop               90

#define OP_Strlen             91
#define OP_Substr             92

#define OP_MAX                93

/*
** Prototypes for the VDBE interface.  See comments on the implementation
** for a description of what each of these routines does.
*/
Vdbe *sqliteVdbeCreate(sqlite*);
int sqliteVdbeAddOp(Vdbe*,int,int,int,const char*,int);
Changes to src/where.c.
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.12 2001/02/19 23:23:39 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.







|







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  Also found here are subroutines
** to generate VDBE code to evaluate expressions.
**
** $Id: where.c,v 1.13 2001/04/04 11:48:58 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
156
157
158
159
160
161
162

163
164
165
166
167
168
169
  int brk, cont;             /* Addresses used during code generation */
  int *aOrder;         /* Order in which pTabList entries are searched */
  int nExpr;           /* Number of subexpressions in the WHERE clause */
  int loopMask;        /* One bit set for each outer loop */
  int haveKey;         /* True if KEY is on the stack */
  int base;            /* First available index for OP_Open opcodes */
  Index *aIdx[32];     /* Index to use on each nested loop.  */

  ExprInfo aExpr[50];  /* The WHERE clause is divided into these expressions */

  /* Allocate space for aOrder[]. */
  aOrder = sqliteMalloc( sizeof(int) * pTabList->nId );

  /* Allocate and initialize the WhereInfo structure that will become the
  ** return value.







>







156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
  int brk, cont;             /* Addresses used during code generation */
  int *aOrder;         /* Order in which pTabList entries are searched */
  int nExpr;           /* Number of subexpressions in the WHERE clause */
  int loopMask;        /* One bit set for each outer loop */
  int haveKey;         /* True if KEY is on the stack */
  int base;            /* First available index for OP_Open opcodes */
  Index *aIdx[32];     /* Index to use on each nested loop.  */
  int aDirect[32];     /* If TRUE, then index this table using ROWID */
  ExprInfo aExpr[50];  /* The WHERE clause is divided into these expressions */

  /* Allocate space for aOrder[]. */
  aOrder = sqliteMalloc( sizeof(int) * pTabList->nId );

  /* Allocate and initialize the WhereInfo structure that will become the
  ** return value.
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
  for(i=0; i<pTabList->nId; i++){
    aOrder[i] = i;
  }

  /* Figure out what index to use (if any) for each nested loop.
  ** Make aIdx[i] point to the index to use for the i-th nested loop
  ** where i==0 is the outer loop and i==pTabList->nId-1 is the inner
  ** loop.

  **
  ** Actually, if there are more than 32 tables in the join, only the
  ** first 32 tables are candidates for indices.
  */
  loopMask = 0;
  for(i=0; i<pTabList->nId && i<ARRAYSIZE(aIdx); i++){

    int idx = aOrder[i];
    Table *pTab = pTabList->a[idx].pTab;
    Index *pIdx;
    Index *pBestIdx = 0;
























    /* Do a search for usable indices.  Leave pBestIdx pointing to
    ** the most specific usable index.
    **
    ** "Most specific" means that pBestIdx is the usable index that
    ** has the largest value for nColumn.  A usable index is one for
    ** which there are subexpressions to compute every column of the
    ** index.
    */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      int j;
      int columnMask = 0;

      if( pIdx->nColumn>32 ) continue;
      for(j=0; j<nExpr; j++){
        if( aExpr[j].idxLeft==idx 
             && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
          int iColumn = aExpr[j].p->pLeft->iColumn;







|
>






>




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










<







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
249
250
251
252
253
254
255
256
257
258

259
260
261
262
263
264
265
  for(i=0; i<pTabList->nId; i++){
    aOrder[i] = i;
  }

  /* Figure out what index to use (if any) for each nested loop.
  ** Make aIdx[i] point to the index to use for the i-th nested loop
  ** where i==0 is the outer loop and i==pTabList->nId-1 is the inner
  ** loop.  If the expression uses only the ROWID field, then set
  ** aDirect[i] to 1.
  **
  ** Actually, if there are more than 32 tables in the join, only the
  ** first 32 tables are candidates for indices.
  */
  loopMask = 0;
  for(i=0; i<pTabList->nId && i<ARRAYSIZE(aIdx); i++){
    int j;
    int idx = aOrder[i];
    Table *pTab = pTabList->a[idx].pTab;
    Index *pIdx;
    Index *pBestIdx = 0;

    /* Check to see if there is an expression that uses only the
    ** ROWID field of this table.  If so, set aDirect[i] to 1.
    ** If not, set aDirect[i] to 0.
    */
    aDirect[i] = 0;
    for(j=0; j<nExpr; j++){
      if( aExpr[j].idxLeft==idx && aExpr[j].p->pLeft->iColumn<0
            && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
        aDirect[i] = 1;
        break;
      }
      if( aExpr[j].idxRight==idx && aExpr[j].p->pRight->iColumn<0
            && (aExpr[j].prereqLeft & loopMask)==aExpr[j].prereqLeft ){
        aDirect[i] = 1;
        break;
      }
    }
    if( aDirect[i] ){
      loopMask |= 1<<idx;
      aIdx[i] = 0;
      continue;
    }

    /* Do a search for usable indices.  Leave pBestIdx pointing to
    ** the most specific usable index.
    **
    ** "Most specific" means that pBestIdx is the usable index that
    ** has the largest value for nColumn.  A usable index is one for
    ** which there are subexpressions to compute every column of the
    ** index.
    */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){

      int columnMask = 0;

      if( pIdx->nColumn>32 ) continue;
      for(j=0; j<nExpr; j++){
        if( aExpr[j].idxLeft==idx 
             && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
          int iColumn = aExpr[j].p->pLeft->iColumn;
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
  /* Generate the code to do the search
  */
  pWInfo->iBreak = brk = sqliteVdbeMakeLabel(v);
  loopMask = 0;
  for(i=0; i<pTabList->nId; i++){
    int j, k;
    int idx = aOrder[i];

    Index *pIdx = i<ARRAYSIZE(aIdx) ? aIdx[i] : 0;












    cont = sqliteVdbeMakeLabel(v);


























    if( pIdx==0 ){
      /* Case 1:  There was no usable index.  We must do a complete
      ** scan of the table.
      */

      sqliteVdbeAddOp(v, OP_Next, base+idx, brk, 0, cont);
      haveKey = 0;
    }else{
      /* Case 2:  We do have a usable index in pIdx.
      */

      for(j=0; j<pIdx->nColumn; j++){
        for(k=0; k<nExpr; k++){
          if( aExpr[k].p==0 ) continue;
          if( aExpr[k].idxLeft==idx 
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && aExpr[k].p->pLeft->iColumn==pIdx->aiColumn[j]
          ){







>
|

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


>



|

>







306
307
308
309
310
311
312
313
314
315
316
317
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
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
  /* Generate the code to do the search
  */
  pWInfo->iBreak = brk = sqliteVdbeMakeLabel(v);
  loopMask = 0;
  for(i=0; i<pTabList->nId; i++){
    int j, k;
    int idx = aOrder[i];
    int goDirect;
    Index *pIdx;

    if( i<ARRAYSIZE(aIdx) ){
      pIdx = aIdx[i];
      goDirect = aDirect[i];
    }else{
      pIdx = 0;
      goDirect = 0;
    }

    if( goDirect ){
      /* Case 1:  We can directly reference a single row using the ROWID field.
      */
      cont = brk;
      for(k=0; k<nExpr; k++){
        if( aExpr[k].p==0 ) continue;
        if( aExpr[k].idxLeft==idx 
           && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
           && aExpr[k].p->pLeft->iColumn<0
        ){
          sqliteExprCode(pParse, aExpr[k].p->pRight);
          aExpr[k].p = 0;
          break;
        }
        if( aExpr[k].idxRight==idx 
           && (aExpr[k].prereqLeft & loopMask)==aExpr[k].prereqLeft
           && aExpr[k].p->pRight->iColumn<0
        ){
          sqliteExprCode(pParse, aExpr[k].p->pLeft);
          aExpr[k].p = 0;
          break;
        }
      }
      sqliteVdbeAddOp(v, OP_AddImm, 0, 0, 0, 0);
      if( i==pTabList->nId-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_Fetch, base+idx, 0, 0, 0);
        haveKey = 0;
      }
    }else if( pIdx==0 ){
      /* Case 2:  There was no usable index.  We must do a complete
      ** scan of the table.
      */
      cont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_Next, base+idx, brk, 0, cont);
      haveKey = 0;
    }else{
      /* Case 3:  We do have a usable index in pIdx.
      */
      cont = sqliteVdbeMakeLabel(v);
      for(j=0; j<pIdx->nColumn; j++){
        for(k=0; k<nExpr; k++){
          if( aExpr[k].p==0 ) continue;
          if( aExpr[k].idxLeft==idx 
             && (aExpr[k].prereqRight & loopMask)==aExpr[k].prereqRight 
             && aExpr[k].p->pLeft->iColumn==pIdx->aiColumn[j]
          ){
Changes to test/expr.test.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing expressions.
#
# $Id: expr.test,v 1.9 2000/09/14 01:21:11 drh Exp $

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

# Create a table to work with.
#
execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing expressions.
#
# $Id: expr.test,v 1.10 2001/04/04 11:48:58 drh Exp $

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

# Create a table to work with.
#
execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}
72
73
74
75
76
77
78




79
80
81
82
83
84
85
test_expr expr-1.31 {i1=1, i2=2} {i1==1 OR i2=2} {1}
test_expr expr-1.32 {i1=1, i2=2} {i1=2 OR i2=1} {0}
test_expr expr-1.33 {i1=1, i2=2} {i1=1 OR i2=1} {1}
test_expr expr-1.34 {i1=1, i2=2} {i1=2 OR i2=2} {1}
test_expr expr-1.35 {i1=1, i2=2} {i1-i2=-1} {1}
test_expr expr-1.36 {i1=1, i2=0} {not i1} {0}
test_expr expr-1.37 {i1=1, i2=NULL} {not i2} {1}





test_expr expr-2.1 {r1=1.23, r2=2.34} {r1+r2} 3.57
test_expr expr-2.2 {r1=1.23, r2=2.34} {r1-r2} -1.11
test_expr expr-2.3 {r1=1.23, r2=2.34} {r1*r2} 2.8782
test_expr expr-2.4 {r1=1.23, r2=2.34} {r1/r2} 0.525641025641026
test_expr expr-2.5 {r1=1.23, r2=2.34} {r2/r1} 1.90243902439024
test_expr expr-2.6 {r1=1.23, r2=2.34} {r2<r1} 0







>
>
>
>







72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
test_expr expr-1.31 {i1=1, i2=2} {i1==1 OR i2=2} {1}
test_expr expr-1.32 {i1=1, i2=2} {i1=2 OR i2=1} {0}
test_expr expr-1.33 {i1=1, i2=2} {i1=1 OR i2=1} {1}
test_expr expr-1.34 {i1=1, i2=2} {i1=2 OR i2=2} {1}
test_expr expr-1.35 {i1=1, i2=2} {i1-i2=-1} {1}
test_expr expr-1.36 {i1=1, i2=0} {not i1} {0}
test_expr expr-1.37 {i1=1, i2=NULL} {not i2} {1}
test_expr expr-1.38 {i1=1} {-i1} {-1}
test_expr expr-1.39 {i1=1} {+i1} {1}
test_expr expr-1.40 {i1=1, i2=2} {+(i2+i1)} {3}
test_expr expr-1.41 {i1=1, i2=2} {-(i2+i1)} {-3}

test_expr expr-2.1 {r1=1.23, r2=2.34} {r1+r2} 3.57
test_expr expr-2.2 {r1=1.23, r2=2.34} {r1-r2} -1.11
test_expr expr-2.3 {r1=1.23, r2=2.34} {r1*r2} 2.8782
test_expr expr-2.4 {r1=1.23, r2=2.34} {r1/r2} 0.525641025641026
test_expr expr-2.5 {r1=1.23, r2=2.34} {r2/r1} 1.90243902439024
test_expr expr-2.6 {r1=1.23, r2=2.34} {r2<r1} 0
Changes to test/in.test.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the IN and BETWEEN operator.
#
# $Id: in.test,v 1.3 2000/06/21 13:59:13 drh Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the IN and BETWEEN operator.
#
# $Id: in.test,v 1.4 2001/04/04 11:48:58 drh Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
141
142
143
144
145
146
147






148
149
150
151
152
153
154
} {2 4 8 16 32 64 128 256 1024 2048}
do_test in-4.2 {
  execsql {
    DELETE FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a>8)
  }
  execsql {SELECT a FROM t1 ORDER BY a}
} {1 2 3 4 5 6 7 8}







# Do an IN with a constant RHS but where the RHS has many, many
# elements.  We need to test that collisions in the hash table
# are resolved properly.
#
do_test in-5.1 {
  execsql {







>
>
>
>
>
>







141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
} {2 4 8 16 32 64 128 256 1024 2048}
do_test in-4.2 {
  execsql {
    DELETE FROM t1 WHERE b IN (SELECT b FROM t1 WHERE a>8)
  }
  execsql {SELECT a FROM t1 ORDER BY a}
} {1 2 3 4 5 6 7 8}
do_test in-4.3 {
  execsql {
    DELETE FROM t1 WHERE b NOT IN (SELECT b FROM t1 WHERE a>4)
  }
  execsql {SELECT a FROM t1 ORDER BY a}
} {5 6 7 8}

# Do an IN with a constant RHS but where the RHS has many, many
# elements.  We need to test that collisions in the hash table
# are resolved properly.
#
do_test in-5.1 {
  execsql {
Changes to test/index.test.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.8 2000/10/19 14:10:09 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.9 2001/04/04 11:48:58 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
330
331
332
333
334
335
336

337















338
} {}
do_test index-10.8 {
  execsql {
    SELECT b FROM t1 ORDER BY b;
  }
} {0}


















finish_test







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

330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
} {}
do_test index-10.8 {
  execsql {
    SELECT b FROM t1 ORDER BY b;
  }
} {0}

# Automatically create an index when we specify a primary key.
#
do_test index-11.1 {
  execsql {
    CREATE TABLE t3(
      a text,
      b int,
      c float,
      PRIMARY KEY(b)
    );
  }
  for {set i 1} {$i<=50} {incr i} {
    execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
  }
  execsql {SELECT c, fcnt() FROM t3 WHERE b==10}
} {0.10 2}

finish_test
Changes to test/insert.test.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement.
#
# $Id: insert.test,v 1.4 2000/06/07 14:42:27 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the INSERT statement.
#
# $Id: insert.test,v 1.5 2001/04/04 11:48:58 drh Exp $

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

# Try to insert into a non-existant table.
#
do_test insert-1.1 {
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
do_test insert-1.6c {
  execsql {INSERT INTO test1(three,one) VALUES(7,8)}
  execsql {SELECT * FROM test1 ORDER BY one}
} {{} 5 6 1 2 {} 8 {} 7}

# A table to use for testing default values
#

execsql {
  CREATE TABLE test2(
























    f1 int default 111, 
    f2 real default -4.32,
    f3 text default hi,
    f4 text default 'abc-123',
    f5 varchar(10)
  )
}

do_test insert-2.1 {
  execsql {SELECT * from test2}
} {}
do_test insert-2.2 {
  execsql {INSERT INTO test2(f2,f4) VALUES(-2.22,'hi!')}
  execsql {SELECT * FROM test2}
} {111 -2.22 hi hi! {}}
do_test insert-2.3 {
  execsql {INSERT INTO test2(f1,f5) VALUES(1,'xyzzy')}
  execsql {SELECT * FROM test2 ORDER BY f1}
} {1 -4.32 hi abc-123 xyzzy 111 -2.22 hi hi! {}}

# Do additional inserts with default values, but this time
# on a table that has indices.  In particular we want to verify
# that the correct default values are inserted into the indices.







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


|



|







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
do_test insert-1.6c {
  execsql {INSERT INTO test1(three,one) VALUES(7,8)}
  execsql {SELECT * FROM test1 ORDER BY one}
} {{} 5 6 1 2 {} 8 {} 7}

# A table to use for testing default values
#
do_test insert-2.1 {
  execsql {
    CREATE TABLE test2(
      f1 int default -111, 
      f2 real default +4.32,
      f3 int default +222,
      f4 int default 7.89
    )
  }
  execsql {SELECT * from test2}
} {}
do_test insert-2.2 {
  execsql {INSERT INTO test2(f1,f3) VALUES(+10,-10)}
  execsql {SELECT * FROM test2}
} {10 4.32 -10 7.89}
do_test insert-2.3 {
  execsql {INSERT INTO test2(f2,f4) VALUES(1.23,-3.45)}
  execsql {SELECT * FROM test2 WHERE f1==-111}
} {-111 1.23 222 -3.45}
do_test insert-2.4 {
  execsql {INSERT INTO test2(f1,f2,f4) VALUES(77,+1.23,3.45)}
  execsql {SELECT * FROM test2 WHERE f1==77}
} {77 1.23 222 3.45}
do_test insert-2.10 {
  execsql {
    DROP TABLE test2;
    CREATE TABLE test2(
      f1 int default 111, 
      f2 real default -4.32,
      f3 text default hi,
      f4 text default 'abc-123',
      f5 varchar(10)
    )
  }


  execsql {SELECT * from test2}
} {}
do_test insert-2.11 {
  execsql {INSERT INTO test2(f2,f4) VALUES(-2.22,'hi!')}
  execsql {SELECT * FROM test2}
} {111 -2.22 hi hi! {}}
do_test insert-2.12 {
  execsql {INSERT INTO test2(f1,f5) VALUES(1,'xyzzy')}
  execsql {SELECT * FROM test2 ORDER BY f1}
} {1 -4.32 hi abc-123 xyzzy 111 -2.22 hi hi! {}}

# Do additional inserts with default values, but this time
# on a table that has indices.  In particular we want to verify
# that the correct default values are inserted into the indices.
Changes to test/main.test.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is exercising the code in main.c.
#
# $Id: main.test,v 1.5 2001/03/20 12:55:14 drh Exp $

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

# Tests of the sqlite_complete() function.
#
do_test main-1.1 {







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is exercising the code in main.c.
#
# $Id: main.test,v 1.6 2001/04/04 11:48:58 drh Exp $

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

# Tests of the sqlite_complete() function.
#
do_test main-1.1 {
72
73
74
75
76
77
78






79
80
81
82
83
84
85
do_test main-1.12 {
  db complete {DROP TABLE xyz; -- hi }
} {1}
do_test main-1.13 {
  db complete {DROP TABLE xyz; -- hi
  }
} {1}







# Try to open a database with a corrupt master file.
#
do_test main-2.0 {
  catch {db close}
  foreach f [glob -nocomplain testdb/*] {file delete -force $f}
  file delete -force testdb







>
>
>
>
>
>







72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
do_test main-1.12 {
  db complete {DROP TABLE xyz; -- hi }
} {1}
do_test main-1.13 {
  db complete {DROP TABLE xyz; -- hi
  }
} {1}
do_test main-1.14 {
  db complete {SELECT a-b FROM t1; }
} {1}
do_test main-1.15 {
  db complete {SELECT a-b FROM t1 }
} {0}

# Try to open a database with a corrupt master file.
#
do_test main-2.0 {
  catch {db close}
  foreach f [glob -nocomplain testdb/*] {file delete -force $f}
  file delete -force testdb
Added test/rowid.test.






















































































































































































































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
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
# Copyright (c) 1999, 2000 D. Richard Hipp
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
# 
# You should have received a copy of the GNU General Public
# License along with this library; if not, write to the
# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA  02111-1307, USA.
#
# Author contact information:
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.1 2001/04/04 11:48:58 drh Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {
  execsql {
    CREATE TABLE t1(x int, y int);
    INSERT INTO t1 VALUES(1,2);
    INSERT INTO t1 VALUES(3,4);
    SELECT x FROM t1 ORDER BY y;
  }
} {1 3}
do_test rowid-1.2 {
  set r [execsql {SELECT rowid FROM t1 ORDER BY x}]
  global x2rowid rowid2x
  set x2rowid(1) [lindex $r 0]
  set x2rowid(3) [lindex $r 1]
  set rowid2x($x2rowid(1)) 1
  set rowid2x($x2rowid(3)) 3
  llength $r
} {2}
do_test rowid-1.3 {
  global x2rowid
  set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(1)"
  execsql $sql
} {1}
do_test rowid-1.4 {
  global x2rowid
  set sql "SELECT x FROM t1 WHERE rowid==$x2rowid(3)"
  execsql $sql
} {3}
do_test rowid-1.5 {
  global x2rowid
  set sql "SELECT x FROM t1 WHERE oid==$x2rowid(1)"
  execsql $sql
} {1}
do_test rowid-1.6 {
  global x2rowid
  set sql "SELECT x FROM t1 WHERE OID==$x2rowid(3)"
  execsql $sql
} {3}
do_test rowid-1.7 {
  global x2rowid
  set sql "SELECT x FROM t1 WHERE _rowid_==$x2rowid(1)"
  execsql $sql
} {1}
do_test rowid-1.8 {
  global x2rowid
  set v [execsql {SELECT x, oid FROM t1 order by x}]
  set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
  expr {$v==$v2}
} {1}
do_test rowid-1.9 {
  global x2rowid
  set v [execsql {SELECT x, RowID FROM t1 order by x}]
  set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
  expr {$v==$v2}
} {1}
do_test rowid-1.9 {
  global x2rowid
  set v [execsql {SELECT x, _rowid_ FROM t1 order by x}]
  set v2 [list 1 $x2rowid(1) 3 $x2rowid(3)]
  expr {$v==$v2}
} {1}

# We cannot update or insert the ROWID column
#
do_test rowid-2.1 {
  set v [catch {execsql {INSERT INTO t1(rowid,x,y) VALUES(1234,5,6)}} msg]
  lappend v $msg
} {1 {table t1 has no column named rowid}}
do_test rowid-2.2 {
  set v [catch {execsql {UPDATE t1 SET rowid=12345 WHERE x==1}}]
  lappend v $msg
} {1 {table t1 has no column named rowid}}
do_test rowid-2.3 {
  set v [catch {execsql {INSERT INTO t1(oid,x,y) VALUES(1234,5,6)}} msg]
  lappend v $msg
} {1 {table t1 has no column named oid}}
do_test rowid-2.4 {
  set v [catch {execsql {UPDATE t1 SET oid=12345 WHERE x==1}}]
  lappend v $msg
} {1 {table t1 has no column named oid}}
do_test rowid-2.5 {
  set v [catch {execsql {INSERT INTO t1(_rowid_,x,y) VALUES(1234,5,6)}} msg]
  lappend v $msg
} {1 {table t1 has no column named _rowid_}}
do_test rowid-2.6 {
  set v [catch {execsql {UPDATE t1 SET _rowid_=12345 WHERE x==1}}]
  lappend v $msg
} {1 {table t1 has no column named _rowid_}}

# But we can use ROWID in the WHERE clause of an UPDATE that does not
# change the ROWID.
#
do_test rowid-2.7 {
  global x2rowid
  set sql "UPDATE t1 SET x=2 WHERE OID==$x2rowid(3)"
  execsql $sql
  execsql {SELECT x FROM t1 ORDER BY x}
} {1 2}
do_test rowid-2.8 {
  global x2rowid
  set sql "UPDATE t1 SET x=3 WHERE _rowid_==$x2rowid(3)"
  execsql $sql
  execsql {SELECT x FROM t1 ORDER BY x}
} {1 3}

# We cannot index by ROWID
#
do_test rowid-2.9 {
  set v [catch {execsql {CREATE INDEX idxt1 ON t1(rowid)}} msg]
  lappend v $msg
} {1 {table t1 has no column named rowid}}
do_test rowid-2.10 {
  set v [catch {execsql {CREATE INDEX idxt1 ON t1(_rowid_)}} msg]
  lappend v $msg
} {1 {table t1 has no column named _rowid_}}
do_test rowid-2.11 {
  set v [catch {execsql {CREATE INDEX idxt1 ON t1(oid)}} msg]
  lappend v $msg
} {1 {table t1 has no column named oid}}
do_test rowid-2.12 {
  set v [catch {execsql {CREATE INDEX idxt1 ON t1(x, rowid)}} msg]
  lappend v $msg
} {1 {table t1 has no column named rowid}}

# Columns defined in the CREATE statement override the buildin ROWID
# column names.
#
do_test rowid-3.1 {
  execsql {
    CREATE TABLE t2(rowid int, x int, y int);
    INSERT INTO t2 VALUES(1,2,3);
    INSERT INTO t2 VALUES(4,5,6);
    INSERT INTO t2 VALUES(7,8,9);
    SELECT * FROM t2 ORDER BY x;
  }
} {1 2 3 4 5 6 7 8 9}
do_test rowid-3.2 {
  execsql {SELECT * FROM t2 ORDER BY rowid}
} {1 2 3 4 5 6 7 8 9}
do_test rowid-3.3 {
  execsql {SELECT rowid, x, y FROM t2 ORDER BY rowid}
} {1 2 3 4 5 6 7 8 9}
do_test rowid-3.4 {
  set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
  foreach {a b c d e f} $r1 {}
  set r2 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY x DESC}]
  foreach {u v w x y z} $r2 {}
  expr {$u==$e && $w==$c && $y==$a}
} {1}
do_probtest rowid-3.5 {
  set r1 [execsql {SELECT _rowid_, rowid FROM t2 ORDER BY rowid}]
  foreach {a b c d e f} $r1 {}
  expr {$a!=$b && $c!=$d && $e!=$f}
} {1}

# Let's try some more complex examples, including some joins.
#
do_test rowid-4.1 {
  execsql {
    DELETE FROM t1;
    DELETE FROM t2;
  }
  for {set i 1} {$i<=50} {incr i} {
    execsql "INSERT INTO t1(x,y) VALUES($i,[expr {$i*$i}])"
  }
  execsql {INSERT INTO t2 SELECT _rowid_, x*y, y*y FROM t1}
  execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
} {256}
do_test rowid-4.2 {
  execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
} {256}
do_test rowid-4.2.1 {
  execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.oid==t2.rowid}
} {256}
do_test rowid-4.2.2 {
  execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
} {256}
do_test rowid-4.2.3 {
  execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t2.rowid==t1.rowid}
} {256}
do_test rowid-4.2.4 {
  execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND t1.x==4}
} {256}
do_test rowid-4.2.5 {
  execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
} {256}
do_test rowid-4.2.6 {
  execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t2.rowid==t1.rowid}
} {256}
do_test rowid-4.2.7 {
  execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND t1.x==4}
} {256}
do_test rowid-4.3 {
  execsql {CREATE INDEX idxt1 ON t1(x)}
  execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1.rowid==t2.rowid}
} {256}
do_test rowid-4.3.1 {
  execsql {SELECT t2.y FROM t1, t2 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
} {256}
do_test rowid-4.3.2 {
  execsql {SELECT t2.y FROM t1, t2 WHERE t2.rowid==t1.oid AND 4==t1.x}
} {256}
do_test rowid-4.4 {
  execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1.rowid==t2.rowid}
} {256}
do_test rowid-4.4.1 {
  execsql {SELECT t2.y FROM t2, t1 WHERE t1.x==4 AND t1._rowid_==t2.rowid}
} {256}
do_test rowid-4.4.2 {
  execsql {SELECT t2.y FROM t2, t1 WHERE t2.rowid==t1.oid AND 4==t1.x}
} {256}
do_test rowid-4.5 {
  execsql {CREATE INDEX idxt2 ON t2(y)}
  execsql {
    SELECT t1.x, fcnt() FROM t2, t1 
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }
} {4 3}
do_test rowid-4.5.1 {
  execsql {
    SELECT t1.x, fcnt() FROM t2, t1 
    WHERE t1.OID==t2.rowid AND t2.y==81
  }
} {3 3}
do_test rowid-4.6 {
  execsql {
    SELECT t1.x FROM t1, t2
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }
} {4}

do_test rowid-5.1 {
  execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
  execsql {SELECT max(x) FROM t1}
} {8}
Changes to test/select1.test.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.7 2000/07/29 13:07:00 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.8 2001/04/04 11:48:58 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
258
259
260
261
262
263
264




265
266
267
268
269
270
271
  set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {xyzzy 11 xyzzy 33}}
do_test select1-6.4 {
  set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {xyzzy 33 xyzzy 77}}




do_test select1-6.5 {
  set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {test1.f1+F2 33 test1.f1+F2 77}}
do_test select1-6.6 {
  set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2 
         ORDER BY f2}} msg]







>
>
>
>







258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
  set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {xyzzy 11 xyzzy 33}}
do_test select1-6.4 {
  set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {xyzzy 33 xyzzy 77}}
do_test select1-6.4a {
  set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {f1+F2 33 f1+F2 77}}
do_test select1-6.5 {
  set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {test1.f1+F2 33 test1.f1+F2 77}}
do_test select1-6.6 {
  set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2 
         ORDER BY f2}} msg]
292
293
294
295
296
297
298














299
300
  lappend v $msg
} {1 {ambiguous column name: A.f1}}
do_test select1-6.9 {
  set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 
         ORDER BY A.f1, B.f1}} msg]
  lappend v $msg
} {0 {A.f1 11 B.f1 11 A.f1 11 B.f1 33 A.f1 33 B.f1 11 A.f1 33 B.f1 33}}















finish_test







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


296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
  lappend v $msg
} {1 {ambiguous column name: A.f1}}
do_test select1-6.9 {
  set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 
         ORDER BY A.f1, B.f1}} msg]
  lappend v $msg
} {0 {A.f1 11 B.f1 11 A.f1 11 B.f1 33 A.f1 33 B.f1 11 A.f1 33 B.f1 33}}
do_test select1-6.10 {
  set v [catch {execsql2 {
    SELECT f1 FROM test1 UNION SELECT f2 FROM test1
    ORDER BY f2;
  }} msg]
  lappend v $msg
} {0 {f2 11 f2 22 f2 33 f2 44}}
do_test select1-6.11 {
  set v [catch {execsql2 {
    SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
    ORDER BY f2+100;
  }} msg]
  lappend v $msg
} {0 {f2+100 11 f2+100 33 f2+100 122 f2+100 144}}

finish_test
Changes to test/sort.test.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: sort.test,v 1.1 2000/06/07 00:12:25 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: sort.test,v 1.2 2001/04/04 11:48:58 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
54
55
56
57
58
59
60






61
62
63
64
65
66
67
  file delete data.txt
  execsql {SELECT count(*) FROM t1}
} {8}

do_test sort-1.1 {
  execsql {SELECT n FROM t1 ORDER BY n}
} {1 2 3 4 5 6 7 8}






do_test sort-1.2 {
  execsql {SELECT n FROM t1 ORDER BY n DESC}
} {8 7 6 5 4 3 2 1}
do_test sort-1.3a {
  execsql {SELECT v FROM t1 ORDER BY v}
} {eight five four one seven six three two}
do_test sort-1.3b {







>
>
>
>
>
>







54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
  file delete data.txt
  execsql {SELECT count(*) FROM t1}
} {8}

do_test sort-1.1 {
  execsql {SELECT n FROM t1 ORDER BY n}
} {1 2 3 4 5 6 7 8}
do_test sort-1.1.1 {
  execsql {SELECT n FROM t1 ORDER BY n ASC}
} {1 2 3 4 5 6 7 8}
do_test sort-1.1.1 {
  execsql {SELECT ALL n FROM t1 ORDER BY n ASC}
} {1 2 3 4 5 6 7 8}
do_test sort-1.2 {
  execsql {SELECT n FROM t1 ORDER BY n DESC}
} {8 7 6 5 4 3 2 1}
do_test sort-1.3a {
  execsql {SELECT v FROM t1 ORDER BY v}
} {eight five four one seven six three two}
do_test sort-1.3b {
78
79
80
81
82
83
84









85
86
87



88
89
90
91
92
93
94























95
} {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11}
do_test sort-1.7 {
  execsql {SELECT roman FROM t1 ORDER BY roman}
} {I II III IV V VI VII VIII}
do_test sort-1.8 {
  execsql {SELECT n FROM t1 ORDER BY log, flt}
} {1 2 3 5 4 6 7 8}









do_test sort-1.9 {
  execsql {SELECT n FROM t1 ORDER BY log, flt DESC}
} {1 3 2 7 6 4 5 8}



do_test sort-1.10 {
  execsql {SELECT n FROM t1 ORDER BY log DESC, flt}
} {8 5 4 6 7 2 3 1}
do_test sort-1.11 {
  execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC}
} {8 7 6 4 5 3 2 1}
























finish_test







>
>
>
>
>
>
>
>
>



>
>
>







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

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
} {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11}
do_test sort-1.7 {
  execsql {SELECT roman FROM t1 ORDER BY roman}
} {I II III IV V VI VII VIII}
do_test sort-1.8 {
  execsql {SELECT n FROM t1 ORDER BY log, flt}
} {1 2 3 5 4 6 7 8}
do_test sort-1.8.1 {
  execsql {SELECT n FROM t1 ORDER BY log asc, flt}
} {1 2 3 5 4 6 7 8}
do_test sort-1.8.2 {
  execsql {SELECT n FROM t1 ORDER BY log, flt ASC}
} {1 2 3 5 4 6 7 8}
do_test sort-1.8.3 {
  execsql {SELECT n FROM t1 ORDER BY log ASC, flt asc}
} {1 2 3 5 4 6 7 8}
do_test sort-1.9 {
  execsql {SELECT n FROM t1 ORDER BY log, flt DESC}
} {1 3 2 7 6 4 5 8}
do_test sort-1.9.1 {
  execsql {SELECT n FROM t1 ORDER BY log ASC, flt DESC}
} {1 3 2 7 6 4 5 8}
do_test sort-1.10 {
  execsql {SELECT n FROM t1 ORDER BY log DESC, flt}
} {8 5 4 6 7 2 3 1}
do_test sort-1.11 {
  execsql {SELECT n FROM t1 ORDER BY log DESC, flt DESC}
} {8 7 6 4 5 3 2 1}

# These tests are designed to reach some hard-to-reach places
# inside the string comparison routines.
#
do_test sort-2.1 {
  execsql {
    UPDATE t1 SET v='x' || -flt;
    UPDATE t1 SET v='x-2b' where v=='x-0.123';
    SELECT v FROM t1 ORDER BY v;
  }
} {x-2b x-2.15 x-3.141592653 x-123 x-4221 x0.0013442 x1.6 x11}
do_test sort-2.2 {
  execsql {
    UPDATE t1 SET v='x-2_' where v=='x0.0013442';
    SELECT v FROM t1 ORDER BY v;
  }
} {x-2_ x-2b x-2.15 x-3.141592653 x-123 x-4221 x1.6 x11}
do_test sort-2.3 {
  execsql {
    UPDATE t1 SET v='x ' || (-1.3+0.01*n);
    SELECT v FROM t1 ORDER BY v;
  }
} {{x -1.29} {x -1.28} {x -1.27} {x -1.26} {x -1.25} {x -1.24} {x -1.23} {x -1.22}}

finish_test
Changes to test/table.test.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: table.test,v 1.7 2000/10/19 14:10:09 drh Exp $

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

# Create a basic table and verify it is added to sqlite_master
#
do_test table-1.1 {







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE TABLE statement.
#
# $Id: table.test,v 1.8 2001/04/04 11:48:58 drh Exp $

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

# Create a basic table and verify it is added to sqlite_master
#
do_test table-1.1 {
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
skipif memory:
do_test table-4.1b {
  db close
  sqlite db testdb
  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
} $r

# Drop the even number tables
#
set r {}
for {set i 1} {$i<=100} {incr i 2} {
  lappend r test$i
}
do_test table-4.2 {
  for {set i 2} {$i<=100} {incr i 2} {







|







238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
skipif memory:
do_test table-4.1b {
  db close
  sqlite db testdb
  execsql {SELECT name FROM sqlite_master WHERE type!='meta' ORDER BY name}
} $r

# Drop the even numbered tables
#
set r {}
for {set i 1} {$i<=100} {incr i 2} {
  lappend r test$i
}
do_test table-4.2 {
  for {set i 2} {$i<=100} {incr i 2} {
299
300
301
302
303
304
305
306


























307
#
testif gdbm:
do_test table-6.1 {
  execsql {CREATE TABLE 'Spaces In This Name!'(x int)}
  execsql {INSERT INTO 'spaces in this name!' VALUES(1)}
  set list [glob -nocomplain testdb/spaces*.tbl]
} {testdb/spaces+in+this+name+.tbl}



























finish_test








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

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
327
328
329
330
331
332
333
#
testif gdbm:
do_test table-6.1 {
  execsql {CREATE TABLE 'Spaces In This Name!'(x int)}
  execsql {INSERT INTO 'spaces in this name!' VALUES(1)}
  set list [glob -nocomplain testdb/spaces*.tbl]
} {testdb/spaces+in+this+name+.tbl}

# Try using keywords as table names or column names.
# 
do_test table-7.1 {
  set v [catch {execsql {
    CREATE TABLE weird(
      desc text,
      asc text,
      explain int,
      vacuum boolean,
      delimiters varchar(10)
    )
  }} msg]
  lappend v $msg
} {0 {}}
do_test table-7.2 {
  execsql {
    INSERT INTO weird VALUES('a','b',9,0,'xyz');
    SELECT * FROM weird;
  }
} {a b 9 0 xyz}
do_test table-7.3 {
  execsql2 {
    SELECT * FROM weird;
  }
} {desc a asc b explain 9 vacuum 0 delimiters xyz}

finish_test
Changes to test/tester.tcl.
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.12 2001/03/20 12:55:14 drh Exp $

# Create a test database
#
if {![info exists dbprefix]} {
  if {[info exists env(SQLITE_PREFIX)]} {
    set dbprefix $env(SQLITE_PREFIX):
  } else {







|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.13 2001/04/04 11:48:58 drh Exp $

# Create a test database
#
if {![info exists dbprefix]} {
  if {[info exists env(SQLITE_PREFIX)]} {
    set dbprefix $env(SQLITE_PREFIX):
  } else {
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
    puts "not necessarily indicate a malfunction."
  }
  exit [expr {$nErr>0}]
}

# A procedure to execute SQL
#
proc execsql {sql} {
  # puts "SQL = $sql"
  return [db eval $sql]
}

# Another procedure to execute SQL.  This one includes the field
# names in the returned list.
#
proc execsql2 {sql} {
  set result {}







|

|







182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
    puts "not necessarily indicate a malfunction."
  }
  exit [expr {$nErr>0}]
}

# A procedure to execute SQL
#
proc execsql {sql {db db}} {
  # puts "SQL = $sql"
  return [$db eval $sql]
}

# Another procedure to execute SQL.  This one includes the field
# names in the returned list.
#
proc execsql2 {sql} {
  set result {}
Added test/trans.test.






























































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
# Copyright (c) 1999, 2000 D. Richard Hipp
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
# 
# You should have received a copy of the GNU General Public
# License along with this library; if not, write to the
# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA  02111-1307, USA.
#
# Author contact information:
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is database locks.
#
# $Id: trans.test,v 1.1 2001/04/04 11:48:58 drh Exp $


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

if {$dbprefix=="gdbm:" && $::tcl_platform(platform)!="windows"} {

# Create several tables to work with.
#
do_test trans-1.0 {
  execsql {
    CREATE TABLE one(a int PRIMARY KEY, b text);
    INSERT INTO one VALUES(1,'one');
    INSERT INTO one VALUES(2,'two');
    INSERT INTO one VALUES(3,'three');
    SELECT b FROM one ORDER BY a;
  }
} {one two three}
do_test trans-1.1 {
  execsql {
    CREATE TABLE two(a int PRIMARY KEY, b text);
    INSERT INTO two VALUES(1,'I');
    INSERT INTO two VALUES(5,'V');
    INSERT INTO two VALUES(10,'X');
    SELECT b FROM two ORDER BY a;
  }
} {I V X}
do_test trans-1.9 {
  sqlite altdb ${dbprefix}testdb
  execsql {SELECT b FROM one ORDER BY a} altdb
} {one two three}
do_test trans-1.10 {
  execsql {SELECT b FROM two ORDER BY a} altdb
} {I V X}

# Basic transactions
#
do_test trans-2.1 {
  set v [catch {execsql {BEGIN}} msg]
  lappend v $msg
} {0 {}}
do_test trans-2.2 {
  set v [catch {execsql {END}} msg]
  lappend v $msg
} {0 {}}
do_test trans-2.3 {
  set v [catch {execsql {BEGIN TRANSACTION}} msg]
  lappend v $msg
} {0 {}}
do_test trans-2.4 {
  set v [catch {execsql {COMMIT TRANSACTION}} msg]
  lappend v $msg
} {0 {}}
do_test trans-2.5 {
  set v [catch {execsql {BEGIN TRANSACTION 'foo'}} msg]
  lappend v $msg
} {0 {}}
do_test trans-2.6 {
  set v [catch {execsql {ROLLBACK TRANSACTION 'foo'}} msg]
  lappend v $msg
} {0 {}}
do_test trans-2.10 {
  execsql {
    BEGIN;
    SELECT a FROM one ORDER BY a;
    SELECT a FROM two ORDER BY a;
    END;
  }
} {1 2 3 1 5 10}

# Check the locking behavior
#
do_test trans-3.1 {
  execsql {
    BEGIN;
    SELECT a FROM one ORDER BY a;
  }
} {1 2 3}
do_test trans-3.2 {
  set v [catch {execsql {
    SELECT a FROM two ORDER BY a;
  } altdb} msg]
  lappend v $msg
} {0 {1 5 10}}
do_test trans-3.3 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } altdb} msg]
  lappend v $msg
} {1 {table one is locked}}
do_test trans-3.4 {
  set v [catch {execsql {
    INSERT INTO one VALUES(4,'four');
  }} msg]
  lappend v $msg
} {0 {}}
do_test trans-3.2 {
  set v [catch {execsql {
    SELECT a FROM two ORDER BY a;
  } altdb} msg]
  lappend v $msg
} {0 {1 5 10}}
do_test trans-3.3 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } altdb} msg]
  lappend v $msg
} {1 {table one is locked}}
do_test trans-3.5 {
  set v [catch {execsql {
    INSERT INTO two VALUES(4,'IV');
  }} msg]
  lappend v $msg
} {0 {}}
do_test trans-3.6 {
  set v [catch {execsql {
    SELECT a FROM two ORDER BY a;
  } altdb} msg]
  lappend v $msg
} {1 {table two is locked}}
do_test trans-3.7 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } altdb} msg]
  lappend v $msg
} {1 {table one is locked}}
do_test trans-3.10 {
  execsql {END TRANSACTION}
} {}
do_test trans-3.11 {
  set v [catch {execsql {
    SELECT a FROM two ORDER BY a;
  } altdb} msg]
  lappend v $msg
} {0 {1 4 5 10}}
do_test trans-3.12 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } altdb} msg]
  lappend v $msg
} {0 {1 2 3 4}}
do_test trans-3.13 {
  set v [catch {execsql {
    SELECT a FROM two ORDER BY a;
  } db} msg]
  lappend v $msg
} {0 {1 4 5 10}}
do_test trans-3.14 {
  set v [catch {execsql {
    SELECT a FROM one ORDER BY a;
  } db} msg]
  lappend v $msg
} {0 {1 2 3 4}}

do_test trans-99.1 {
  altdb close
  execsql {
    DROP TABLE one;
    DROP TABLE two;
  }
} {}

finish_test

} ;# end if(gdbm and not windows)
Changes to tool/opNames.awk.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# Read the sqliteVdbe.h file and generate a table of opcode names.
#
BEGIN {
  printf "static char *zOpName[] = { 0,\n"
  n = 0
}
/^#define OP_MAX/ {
  next
}
/^#define OP_/ {
  name = "\"" substr($2,4) "\","
  if( n<3 ){
    printf "  %-16s", name
    n++
  } else {
    printf "  %s\n", name
    n = 0
  }
}
END {












|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# Read the sqliteVdbe.h file and generate a table of opcode names.
#
BEGIN {
  printf "static char *zOpName[] = { 0,\n"
  n = 0
}
/^#define OP_MAX/ {
  next
}
/^#define OP_/ {
  name = "\"" substr($2,4) "\","
  if( n<3 ){
    printf "  %-19s", name
    n++
  } else {
    printf "  %s\n", name
    n = 0
  }
}
END {
Changes to www/changes.tcl.
13
14
15
16
17
18
19
20







21
22
23
24
25
26
27


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

chng {2001 Apr 3 (1.0.28)} {







<li>Changes to the "lemon" parser generator to help it work better when
    compiled using MSVC.</li>
<li>Bug fixes in the TCL interface identified by Oleg Oleinick.</li>
}

chng {2001 Mar 20 (1.0.27)} {
<li>When doing DELETE and UPDATE, the library used to write the record







|
>
>
>
>
>
>
>







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 {2001 Apr 4 (1.0.28)} {
<li>Added limited support for transactions.  At this point, transactions
    will do table locking on the GDBM backend.  There is no support (yet)
    for rollback or atomic commit.</li>
<li>Added special column names ROWID, OID, and _ROWID_ that refer to the
    unique random integer key associated with every row of every table.</li>
<li>Additional tests added to the regression suite to cover the new ROWID
    feature and the TCL interface bugs mentioned below.</li>
<li>Changes to the "lemon" parser generator to help it work better when
    compiled using MSVC.</li>
<li>Bug fixes in the TCL interface identified by Oleg Oleinick.</li>
}

chng {2001 Mar 20 (1.0.27)} {
<li>When doing DELETE and UPDATE, the library used to write the record
Changes to www/lang.tcl.
1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.6 2001/02/20 13:06:31 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>



|







1
2
3
4
5
6
7
8
9
10
11
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.7 2001/04/04 11:48:58 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
44
45
46
47
48
49
50

51
52
53
54
55
56
57
  {INSERT insert}
  {DELETE delete}
  {UPDATE update}
  {SELECT select}
  {COPY copy}
  {EXPLAIN explain}
  {expression expr}

}] {
  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>







>







44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
  {INSERT insert}
  {DELETE delete}
  {UPDATE update}
  {SELECT select}
  {COPY copy}
  {EXPLAIN explain}
  {expression expr}
  {{BEGIN TRANSACTION} transaction}
}] {
  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>
94
95
96
97
98
99
100



















































101
102
103
104
105
106
107
  }
  puts "<h1>$name</h1>\n"
}

proc Example {text} {
  puts "<blockquote><pre>$text</pre></blockquote>"
}




















































Section COPY copy

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








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







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
  }
  puts "<h1>$name</h1>\n"
}

proc Example {text} {
  puts "<blockquote><pre>$text</pre></blockquote>"
}

Section {BEGIN TRANSACTION} createindex

Syntax {sql-statement} {
BEGIN [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
END [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
COMMIT [TRANSACTION [<name>]]
}
Syntax {sql-statement} {
ROLLBACK [TRANSACTION [<name>]]
}

puts {
<p>Support for transactions in SQLite is thin.  Transactions
may not be nested.  The GDBM backend does not support an atomic
commit or rollback, but it does support locking.  (Note, however,
that the compilation instructions on this website for using GDBM under 
Windows will disable locking.)  The MEM backend has no transaction
support and silently ignores all requests to begin or end
transactions.  A new backend is currently under
development for SQLite 2.0 that will support both atomic commits and rollback,
but that driver is not yet available.</p>

<p>Under GDBM, starting a transaction just locks all
tables that are either read or written during the course of the
transaction.  The locks are removed when the transaction is ended.
Thus, transactions can be used to make changes to multiple tables
with the assurance that other threads or processes will not touch
the same tables at the same time. For example:</p>

<blockquote>
<b>SELECT data1, data2, ... FROM table1 WHERE ...;</b><br>
... Make a decision to update the table ...<br>
<b>BEGIN TRANSACTION;<br>
SELECT data1, data2, ... FROM table1 WHERE ...;</b><br>
... Make sure no other process changed the table in between
the first SELECT and the BEGIN TRANSACTION. ...<br>
<b>UPDATE table1 SET data1=... WHERE ...;<br>
END TRANSACTION;</b>
</blockquote>

<p>In the code above, the <b>table1</b> table is locked by
the second SELECT because of the transaction.  Thus we know that
no other process has modified <b>table1</b> when the UPDATE
occurs.  The END TRANSACTION releases the lock.</p>
}


Section COPY copy

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

337
338
339
340
341
342
343













344
345
346
347
348
349
350
side against lower case characters on the other.</p>"
puts {

<p>The GLOB operator is similar to LIKE but uses the Unix
file globbing syntax for its wildcards.  Also, GLOB is case
sensitive, unlike LIKE.  Both GLOB and LIKE may be preceded by
the NOT keyword to invert the sense of the test.</p>














<p>SELECT statements can appear in expressions as either the
right-hand operand of the IN operator or as a scalar quantity.
In both cases, the SELECT should have only a single column in its
result.  Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed.  Any ORDER BY clause on the select is ignored.
A SELECT in an expression is evaluated once before any other processing







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







389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
side against lower case characters on the other.</p>"
puts {

<p>The GLOB operator is similar to LIKE but uses the Unix
file globbing syntax for its wildcards.  Also, GLOB is case
sensitive, unlike LIKE.  Both GLOB and LIKE may be preceded by
the NOT keyword to invert the sense of the test.</p>

<p>A column name can be any of the names defined in the CREATE TABLE
statement or one of the following special identifiers: "<b>ROWID</b>",
"<b>OID</b>", or "<b>_ROWID_</b>".
These special identifiers all describe the
unique random integer key (the "row key") associated every every 
row of every table.
The special identifiers only refer to the row key if the CREATE TABLE
statement does not define a real column with the same name.  Row keys
act like read-only columns.  A row key can be used anywhere a regular
column can be used, except that you cannot change the value
of a row key in an UPDATE or INSERT statement.
"SELECT * ..." does not return the row key.</p>

<p>SELECT statements can appear in expressions as either the
right-hand operand of the IN operator or as a scalar quantity.
In both cases, the SELECT should have only a single column in its
result.  Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed.  Any ORDER BY clause on the select is ignored.
A SELECT in an expression is evaluated once before any other processing