SQLite

Check-in [e1bf96a467]
Login

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

Overview
Comment::-) (CVS 104)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e1bf96a467b739373191bf75e6a097fc0f24bffc
User & Date: drh 2000-06-21 13:59:11.000
Context
2000-06-21
14:00
:-) (CVS 105) (check-in: 516f022206 user: drh tags: trunk)
13:59
:-) (CVS 104) (check-in: e1bf96a467 user: drh tags: trunk)
2000-06-19
19:10
:-) (CVS 103) (check-in: af14a5b3ba 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.18 2000/06/17 13:12:39 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.19 2000/06/21 13:59:11 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
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
}

/*
** Remove the given index from the index hash table, and free
** its memory structures.
**
** The index is removed from the database hash table, but it is
** not unlinked from the table that is being indexed.  Unlinking
** from the table must be done by the calling function.
*/
static void sqliteDeleteIndex(sqlite *db, Index *pIndex){
  int h;
  if( pIndex->zName ){
    h = sqliteHashNoCase(pIndex->zName, 0) % N_HASH;
    if( db->apIdxHash[h]==pIndex ){
      db->apIdxHash[h] = pIndex->pHash;
    }else{
      Index *p;
      for(p=db->apIdxHash[h]; p && p->pHash!=pIndex; p=p->pHash){}
      if( p && p->pHash==pIndex ){
        p->pHash = pIndex->pHash;
      }
    }
  }
  sqliteFree(pIndex);
}

/*
** Remove the memory data structures associated with the given
** table.  No changes are made to disk by this routine.
**
** This routine just deletes the data structure.  It does not unlink
** the table data structure from the hash table.  But does it destroy
** memory structures of the indices associated with the table.
*/
void sqliteDeleteTable(sqlite *db, Table *pTable){
  int i;







|
|




















|







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
}

/*
** Remove the given index from the index hash table, and free
** its memory structures.
**
** The index is removed from the database hash table, but it is
** not unlinked from the Table that is being indexed.  Unlinking
** from the Table must be done by the calling function.
*/
static void sqliteDeleteIndex(sqlite *db, Index *pIndex){
  int h;
  if( pIndex->zName ){
    h = sqliteHashNoCase(pIndex->zName, 0) % N_HASH;
    if( db->apIdxHash[h]==pIndex ){
      db->apIdxHash[h] = pIndex->pHash;
    }else{
      Index *p;
      for(p=db->apIdxHash[h]; p && p->pHash!=pIndex; p=p->pHash){}
      if( p && p->pHash==pIndex ){
        p->pHash = pIndex->pHash;
      }
    }
  }
  sqliteFree(pIndex);
}

/*
** Remove the memory data structures associated with the given
** Table.  No changes are made to disk by this routine.
**
** This routine just deletes the data structure.  It does not unlink
** the table data structure from the hash table.  But does it destroy
** memory structures of the indices associated with the table.
*/
void sqliteDeleteTable(sqlite *db, Table *pTable){
  int i;
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
  pIndex = sqliteMalloc( sizeof(Index) + strlen(zName) + 1 +
                        sizeof(int)*pList->nId );
  if( pIndex==0 ){
    sqliteSetString(&pParse->zErrMsg, "out of memory", 0);
    pParse->nErr++;
    goto exit_create_index;
  }
  pIndex->aiField = (int*)&pIndex[1];
  pIndex->zName = (char*)&pIndex->aiField[pList->nId];
  strcpy(pIndex->zName, zName);
  pIndex->pTable = pTab;
  pIndex->nField = pList->nId;

  /* Scan the names of the columns of the table to be indexed and
  ** load the column indices into the Index structure.  Report an error
  ** if any column is not found.
  */
  for(i=0; i<pList->nId; i++){
    for(j=0; j<pTab->nCol; j++){
      if( sqliteStrICmp(pList->a[i].zName, pTab->aCol[j].zName)==0 ) break;
    }
    if( j>=pTab->nCol ){
      sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName, 
        " has no column named ", pList->a[i].zName, 0);
      pParse->nErr++;
      sqliteFree(pIndex);
      goto exit_create_index;
    }
    pIndex->aiField[i] = j;
  }

  /* Link the new Index structure to its table and to the other
  ** in-memory database structures.
  */
  if( pParse->explain==0 ){
    h = sqliteHashNoCase(pIndex->zName, 0) % N_HASH;







|
|


|
















|







508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
  pIndex = sqliteMalloc( sizeof(Index) + strlen(zName) + 1 +
                        sizeof(int)*pList->nId );
  if( pIndex==0 ){
    sqliteSetString(&pParse->zErrMsg, "out of memory", 0);
    pParse->nErr++;
    goto exit_create_index;
  }
  pIndex->aiColumn = (int*)&pIndex[1];
  pIndex->zName = (char*)&pIndex->aiColumn[pList->nId];
  strcpy(pIndex->zName, zName);
  pIndex->pTable = pTab;
  pIndex->nColumn = pList->nId;

  /* Scan the names of the columns of the table to be indexed and
  ** load the column indices into the Index structure.  Report an error
  ** if any column is not found.
  */
  for(i=0; i<pList->nId; i++){
    for(j=0; j<pTab->nCol; j++){
      if( sqliteStrICmp(pList->a[i].zName, pTab->aCol[j].zName)==0 ) break;
    }
    if( j>=pTab->nCol ){
      sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName, 
        " has no column named ", pList->a[i].zName, 0);
      pParse->nErr++;
      sqliteFree(pIndex);
      goto exit_create_index;
    }
    pIndex->aiColumn[i] = j;
  }

  /* Link the new Index structure to its table and to the other
  ** in-memory database structures.
  */
  if( pParse->explain==0 ){
    h = sqliteHashNoCase(pIndex->zName, 0) % N_HASH;
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
      sqliteVdbeChangeP3(v, base+4, pTab->zName, 0);
      sqliteVdbeChangeP3(v, base+5, pStart->z, n);
    }
    lbl1 = sqliteVdbeMakeLabel(v);
    lbl2 = sqliteVdbeMakeLabel(v);
    sqliteVdbeAddOp(v, OP_Next, 0, lbl2, 0, lbl1);
    sqliteVdbeAddOp(v, OP_Key, 0, 0, 0, 0);
    for(i=0; i<pIndex->nField; i++){
      sqliteVdbeAddOp(v, OP_Field, 0, pIndex->aiField[i], 0, 0);
    }
    sqliteVdbeAddOp(v, OP_MakeKey, pIndex->nField, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_PutIdx, 1, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Goto, 0, lbl1, 0, 0);
    sqliteVdbeAddOp(v, OP_Noop, 0, 0, 0, lbl2);
    sqliteVdbeAddOp(v, OP_Close, 1, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Close, 0, 0, 0, 0);
  }








|
|

|







586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
      sqliteVdbeChangeP3(v, base+4, pTab->zName, 0);
      sqliteVdbeChangeP3(v, base+5, pStart->z, n);
    }
    lbl1 = sqliteVdbeMakeLabel(v);
    lbl2 = sqliteVdbeMakeLabel(v);
    sqliteVdbeAddOp(v, OP_Next, 0, lbl2, 0, lbl1);
    sqliteVdbeAddOp(v, OP_Key, 0, 0, 0, 0);
    for(i=0; i<pIndex->nColumn; i++){
      sqliteVdbeAddOp(v, OP_Field, 0, pIndex->aiColumn[i], 0, 0);
    }
    sqliteVdbeAddOp(v, OP_MakeKey, pIndex->nColumn, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_PutIdx, 1, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Goto, 0, lbl1, 0, 0);
    sqliteVdbeAddOp(v, OP_Noop, 0, 0, 0, lbl2);
    sqliteVdbeAddOp(v, OP_Close, 1, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Close, 0, 0, 0, 0);
  }

830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
    }
    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Put, 0, 0, 0, 0);
    for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      if( pIdx->pNext ){
        sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
      }
      for(j=0; j<pIdx->nField; j++){
        sqliteVdbeAddOp(v, OP_FileField, pIdx->aiField[j], 0, 0, 0);
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nField, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_PutIdx, i, 0, 0, 0);
    }
    sqliteVdbeAddOp(v, OP_Goto, 0, addr, 0, 0);
    sqliteVdbeAddOp(v, OP_Noop, 0, 0, 0, end);
  }
  
copy_cleanup:







|
|

|







830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
    }
    sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Put, 0, 0, 0, 0);
    for(i=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, i++){
      if( pIdx->pNext ){
        sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
      }
      for(j=0; j<pIdx->nColumn; j++){
        sqliteVdbeAddOp(v, OP_FileField, pIdx->aiColumn[j], 0, 0, 0);
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nColumn, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_PutIdx, i, 0, 0, 0);
    }
    sqliteVdbeAddOp(v, OP_Goto, 0, addr, 0, 0);
    sqliteVdbeAddOp(v, OP_Noop, 0, 0, 0, end);
  }
  
copy_cleanup:
Changes to src/dbbe.c.
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
** 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: dbbe.c,v 1.14 2000/06/17 13:12:39 drh Exp $
*/
#include "sqliteInt.h"
#include <gdbm.h>
#include <sys/stat.h>
#include <unistd.h>
#include <ctype.h>
#include <time.h>

/*
** Information about each open disk file is an instance of this 
** structure.  There will only be one such structure for each
** disk file.  If the VDBE opens the same file twice (as will happen
** for a self-join, for example) then two DbbeTable structures are
** created but there is only a single BeFile structure with an
** nRef of 2.
*/
typedef struct BeFile BeFile;
struct BeFile {
  char *zName;            /* Name of the file */
  GDBM_FILE dbf;          /* The file itself */







|












|







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
** 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: dbbe.c,v 1.15 2000/06/21 13:59:11 drh Exp $
*/
#include "sqliteInt.h"
#include <gdbm.h>
#include <sys/stat.h>
#include <unistd.h>
#include <ctype.h>
#include <time.h>

/*
** Information about each open disk file is an instance of this 
** structure.  There will only be one such structure for each
** disk file.  If the VDBE opens the same file twice (as will happen
** for a self-join, for example) then two DbbeCursor structures are
** created but there is only a single BeFile structure with an
** nRef of 2.
*/
typedef struct BeFile BeFile;
struct BeFile {
  char *zName;            /* Name of the file */
  GDBM_FILE dbf;          /* The file itself */
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
  char **azTemp;     /* Names of the temporary files */
  struct rc4 rc4;    /* The random number generator */
};

/*
** 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
** there can be multiple DbbeTable structures.  Each DbbeTable represents
** a cursor pointing to a particular part of the open BeFile.  The
** BeFile.nRef field hold a count of the number of DbbeTable structures
** associated with the same disk file.
*/
struct DbbeTable {
  Dbbe *pBe;         /* The database of which this record is a part */
  BeFile *pFile;     /* The database file for this table */
  datum key;         /* Most recently used key */
  datum data;        /* Most recent data */
  int needRewind;    /* Next key should be the first */
  int readPending;   /* The fetch hasn't actually been done yet */
};







|

|


|







83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
  char **azTemp;     /* Names of the temporary files */
  struct rc4 rc4;    /* The random number generator */
};

/*
** 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
** there can be multiple DbbeCursor structures.  Each DbbeCursor represents
** a cursor pointing to a particular part of the open BeFile.  The
** BeFile.nRef field hold a count of the number of DbbeCursor structures
** associated with the same disk file.
*/
struct DbbeCursor {
  Dbbe *pBe;         /* The database of which this record is a part */
  BeFile *pFile;     /* The database file for this table */
  datum key;         /* Most recently used key */
  datum data;        /* Most recent data */
  int needRewind;    /* Next key should be the first */
  int readPending;   /* The fetch hasn't actually been done yet */
};
222
223
224
225
226
227
228
229

230
231
232
233
234
235
236
237
  sqliteFree(pBe->azTemp);
  sqliteFree(pBe->apTemp);
  memset(pBe, 0, sizeof(*pBe));
  sqliteFree(pBe);
}

/*
** Translate the name of a table into the name of a file that holds

** that table.  Space to hold the filename is obtained from
** sqliteMalloc() and must be freed by the calling function.
*/
static char *sqliteFileOfTable(Dbbe *pBe, const char *zTable){
  char *zFile = 0;
  int i;
  sqliteSetString(&zFile, pBe->zDir, "/", zTable, ".tbl", 0);
  if( zFile==0 ) return 0;







|
>
|







222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
  sqliteFree(pBe->azTemp);
  sqliteFree(pBe->apTemp);
  memset(pBe, 0, sizeof(*pBe));
  sqliteFree(pBe);
}

/*
** Translate the name of an SQL table (or index) into the name 
** of a file that holds the key/data pairs for that table or
** index.  Space to hold the filename is obtained from
** sqliteMalloc() and must be freed by the calling function.
*/
static char *sqliteFileOfTable(Dbbe *pBe, const char *zTable){
  char *zFile = 0;
  int i;
  sqliteSetString(&zFile, pBe->zDir, "/", zTable, ".tbl", 0);
  if( zFile==0 ) return 0;
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290

291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
    zBuf[j++] = zRandomChars[c];
  }
  zBuf[j] = 0;
}

/*
** Open a new table cursor.  Write a pointer to the corresponding
** DbbeTable structure into *ppTable.  Return an integer success
** code:
**
**    SQLITE_OK          It worked!
**
**    SQLITE_NOMEM       sqliteMalloc() failed
**
**    SQLITE_PERM        Attempt to access a file for which file
**                       access permission is denied
**
**    SQLITE_BUSY        Another thread or process is already using
**                       the corresponding file and has that file locked.
**
**    SQLITE_READONLY    The current thread already has this file open
**                       readonly but you are trying to open for writing.
**                       (This can happen if a SELECT callback tries to
**                       do an UPDATE or DELETE.)
**
** If zTable is 0 or "", then a temporary table is created and opened.

** This table will be deleted from the disk when it is closed.
*/
int sqliteDbbeOpenTable(
  Dbbe *pBe,              /* The database the table belongs to */
  const char *zTable,     /* The name of the table */
  int writeable,          /* True to open for writing */
  DbbeTable **ppTable     /* Write the resulting table pointer here */
){
  char *zFile;            /* Name of the table file */
  DbbeTable *pTable;      /* 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 */

  *ppTable = 0;
  pTable = sqliteMalloc( sizeof(*pTable) );
  if( pTable==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;
    }
  }else{
    pFile = 0;







|

















|
>
|

|

|

|


|





|
|
|







266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
    zBuf[j++] = zRandomChars[c];
  }
  zBuf[j] = 0;
}

/*
** Open a new table cursor.  Write a pointer to the corresponding
** DbbeCursor structure into *ppCursr.  Return an integer success
** code:
**
**    SQLITE_OK          It worked!
**
**    SQLITE_NOMEM       sqliteMalloc() failed
**
**    SQLITE_PERM        Attempt to access a file for which file
**                       access permission is denied
**
**    SQLITE_BUSY        Another thread or process is already using
**                       the corresponding file and has that file locked.
**
**    SQLITE_READONLY    The current thread already has this file open
**                       readonly but you are trying to open for writing.
**                       (This can happen if a SELECT callback tries to
**                       do an UPDATE or DELETE.)
**
** If zTable is 0 or "", then a temporary database file is created and
** a cursor to that temporary file is opened.  The temporary file
** will be deleted from the disk when it is closed.
*/
int sqliteDbbeOpenCursor(
  Dbbe *pBe,              /* The database the table belongs to */
  const char *zTable,     /* The SQL name of the file to be opened */
  int writeable,          /* True to open for writing */
  DbbeCursor **ppCursr    /* Write the resulting table pointer here */
){
  char *zFile;            /* Name of the table file */
  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 */

  *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;
    }
  }else{
    pFile = 0;
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417





418
419
420
421
422
423
424
425
426
427
428
429
430
431
  }else{
    sqliteFree(zFile);
    pFile->nRef++;
    if( writeable && !pFile->writeable ){
      rc = SQLITE_READONLY;
    }
  }
  pTable->pBe = pBe;
  pTable->pFile = pFile;
  pTable->readPending = 0;
  pTable->needRewind = 1;
  *ppTable = pTable;
  return rc;
}

/*
** Drop a table from the database.  The file on the disk that corresponds
** to this table is deleted.
*/
void sqliteDbbeDropTable(Dbbe *pBe, const char *zTable){
  char *zFile;            /* Name of the table file */

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

/*
** Reorganize a table to reduce search times and disk usage.
*/
void sqliteDbbeReorganizeTable(Dbbe *pBe, const char *zTable){
  DbbeTable *pTab;

  if( sqliteDbbeOpenTable(pBe, zTable, 1, &pTab)!=SQLITE_OK ){
    return;
  }
  if( pTab && pTab->pFile && pTab->pFile->dbf ){
    gdbm_reorganize(pTab->pFile->dbf);
  }
  if( pTab ){
    sqliteDbbeCloseTable(pTab);
  }
}

/*
** Close a table previously opened by sqliteDbbeOpenTable().





*/
void sqliteDbbeCloseTable(DbbeTable *pTable){
  BeFile *pFile;
  Dbbe *pBe;
  if( pTable==0 ) return;
  pFile = pTable->pFile;
  pBe = pTable->pBe;
  pFile->nRef--;
  if( pFile->dbf!=NULL ){
    gdbm_sync(pFile->dbf);
  }
  if( pFile->nRef<=0 ){
    if( pFile->dbf!=NULL ){
      gdbm_close(pFile->dbf);







|
|
|
|
|



















|

|


|
|

|
|




|
>
>
>
>
>

|


|
|
|







374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
  }else{
    sqliteFree(zFile);
    pFile->nRef++;
    if( writeable && !pFile->writeable ){
      rc = SQLITE_READONLY;
    }
  }
  pCursr->pBe = pBe;
  pCursr->pFile = pFile;
  pCursr->readPending = 0;
  pCursr->needRewind = 1;
  *ppCursr = pCursr;
  return rc;
}

/*
** Drop a table from the database.  The file on the disk that corresponds
** to this table is deleted.
*/
void sqliteDbbeDropTable(Dbbe *pBe, const char *zTable){
  char *zFile;            /* Name of the table file */

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

/*
** Reorganize a table to reduce search times and disk usage.
*/
void sqliteDbbeReorganizeTable(Dbbe *pBe, const char *zTable){
  DbbeCursor *pCrsr;

  if( sqliteDbbeOpenCursor(pBe, zTable, 1, &pCrsr)!=SQLITE_OK ){
    return;
  }
  if( pCrsr && pCrsr->pFile && pCrsr->pFile->dbf ){
    gdbm_reorganize(pCrsr->pFile->dbf);
  }
  if( pCrsr ){
    sqliteDbbeCloseCursor(pCrsr);
  }
}

/*
** Close a cursor previously opened by sqliteDbbeOpenCursor().
**
** 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.
*/
void sqliteDbbeCloseCursor(DbbeCursor *pCursr){
  BeFile *pFile;
  Dbbe *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);
441
442
443
444
445
446
447
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
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
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
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
    if( pFile->delOnClose ){
      unlink(pFile->zName);
    }
    sqliteFree(pFile->zName);
    memset(pFile, 0, sizeof(*pFile));
    sqliteFree(pFile);
  }
  if( pTable->key.dptr ) free(pTable->key.dptr);
  if( pTable->data.dptr ) free(pTable->data.dptr);
  memset(pTable, 0, sizeof(*pTable));
  sqliteFree(pTable);
}

/*
** Clear the given datum
*/
static void datumClear(datum *p){
  if( p->dptr ) free(p->dptr);
  p->dptr = 0;
  p->dsize = 0;
}

/*
** Fetch a single record from an open table.  Return 1 on success
** and 0 on failure.
*/
int sqliteDbbeFetch(DbbeTable *pTable, int nKey, char *pKey){
  datum key;
  key.dsize = nKey;
  key.dptr = pKey;
  datumClear(&pTable->key);
  datumClear(&pTable->data);
  if( pTable->pFile && pTable->pFile->dbf ){
    pTable->data = gdbm_fetch(pTable->pFile->dbf, key);
  }
  return pTable->data.dptr!=0;
}

/*
** Return 1 if the given key is already in the table.  Return 0
** if it is not.
*/
int sqliteDbbeTest(DbbeTable *pTable, int nKey, char *pKey){
  datum key;
  int result = 0;
  key.dsize = nKey;
  key.dptr = pKey;
  if( pTable->pFile && pTable->pFile->dbf ){
    result = gdbm_exists(pTable->pFile->dbf, key);
  }
  return result;
}

/*
** Copy bytes from the current key or data into a buffer supplied by
** the calling function.  Return the number of bytes copied.
*/
int sqliteDbbeCopyKey(DbbeTable *pTable, int offset, int size, char *zBuf){
  int n;
  if( offset>=pTable->key.dsize ) return 0;
  if( offset+size>pTable->key.dsize ){
    n = pTable->key.dsize - offset;
  }else{
    n = size;
  }
  memcpy(zBuf, &pTable->key.dptr[offset], n);
  return n;
}
int sqliteDbbeCopyData(DbbeTable *pTable, int offset, int size, char *zBuf){
  int n;
  if( pTable->readPending && pTable->pFile && pTable->pFile->dbf ){
    pTable->data = gdbm_fetch(pTable->pFile->dbf, pTable->key);
    pTable->readPending = 0;
  }
  if( offset>=pTable->data.dsize ) return 0;
  if( offset+size>pTable->data.dsize ){
    n = pTable->data.dsize - offset;
  }else{
    n = size;
  }
  memcpy(zBuf, &pTable->data.dptr[offset], n);
  return n;
}

/*
** Return a pointer to bytes from the key or data.  The data returned
** is ephemeral.
*/
char *sqliteDbbeReadKey(DbbeTable *pTable, int offset){
  if( offset<0 || offset>=pTable->key.dsize ) return "";
  return &pTable->key.dptr[offset];
}
char *sqliteDbbeReadData(DbbeTable *pTable, int offset){
  if( pTable->readPending && pTable->pFile && pTable->pFile->dbf ){
    pTable->data = gdbm_fetch(pTable->pFile->dbf, pTable->key);
    pTable->readPending = 0;
  }
  if( offset<0 || offset>=pTable->data.dsize ) return "";
  return &pTable->data.dptr[offset];
}

/*
** Return the total number of bytes in either data or key.
*/
int sqliteDbbeKeyLength(DbbeTable *pTable){
  return pTable->key.dsize;
}
int sqliteDbbeDataLength(DbbeTable *pTable){
  if( pTable->readPending && pTable->pFile && pTable->pFile->dbf ){
    pTable->data = gdbm_fetch(pTable->pFile->dbf, pTable->key);
    pTable->readPending = 0;
  }
  return pTable->data.dsize;
}

/*
** Make is so that the next call to sqliteNextKey() finds the first
** key of the table.
*/
int sqliteDbbeRewind(DbbeTable *pTable){
  pTable->needRewind = 1;
  return SQLITE_OK;
}

/*
** Read the next key from the table.  Return 1 on success.  Return
** 0 if there are no more keys.
*/
int sqliteDbbeNextKey(DbbeTable *pTable){
  datum nextkey;
  int rc;
  if( pTable==0 || pTable->pFile==0 || pTable->pFile->dbf==0 ){
    pTable->readPending = 0;
    return 0;
  }
  if( pTable->needRewind ){
    nextkey = gdbm_firstkey(pTable->pFile->dbf);
    pTable->needRewind = 0;
  }else{
    nextkey = gdbm_nextkey(pTable->pFile->dbf, pTable->key);
  }
  datumClear(&pTable->key);
  datumClear(&pTable->data);
  pTable->key = nextkey;
  if( pTable->key.dptr ){
    pTable->readPending = 1;
    rc = 1;
  }else{
    pTable->needRewind = 1;
    pTable->readPending = 0;
    rc = 0;
  }
  return rc;
}

/*
** Get a new integer key.
*/
int sqliteDbbeNew(DbbeTable *pTable){
  int iKey;
  datum key;
  int go = 1;
  int i;
  struct rc4 *pRc4;

  if( pTable->pFile==0 || pTable->pFile->dbf==0 ) return 1;
  pRc4 = &pTable->pBe->rc4;
  while( go ){
    iKey = 0;
    for(i=0; i<4; i++){
      iKey = (iKey<<8) + rc4byte(pRc4);
    }
    key.dptr = (char*)&iKey;
    key.dsize = 4;
    go = gdbm_exists(pTable->pFile->dbf, key);
  }
  return iKey;
}   

/*
** Write an entry into the table.  Overwrite any prior entry with the
** same key.
*/
int sqliteDbbePut(DbbeTable *pTable, int nKey,char *pKey,int nData,char *pData){
  datum data, key;
  int rc;
  if( pTable->pFile==0 || pTable->pFile->dbf==0 ) return SQLITE_ERROR;
  data.dsize = nData;
  data.dptr = pData;
  key.dsize = nKey;
  key.dptr = pKey;
  rc = gdbm_store(pTable->pFile->dbf, key, data, GDBM_REPLACE);
  if( rc ) rc = SQLITE_ERROR;
  datumClear(&pTable->key);
  datumClear(&pTable->data);
  return rc;
}

/*
** Remove an entry from a table, if the entry exists.
*/
int sqliteDbbeDelete(DbbeTable *pTable, int nKey, char *pKey){
  datum key;
  int rc;
  datumClear(&pTable->key);
  datumClear(&pTable->data);
  if( pTable->pFile==0 || pTable->pFile->dbf==0 ) return SQLITE_ERROR;
  key.dsize = nKey;
  key.dptr = pKey;
  rc = gdbm_delete(pTable->pFile->dbf, key);
  if( rc ) rc = SQLITE_ERROR;
  return rc;
}

/*
** Open a temporary file.  The file should be deleted when closed.
**







|
|
|
|












|


|



|
|
|
|

|






|




|
|








|

|
|
|



|


|

|
|
|

|
|
|



|







|
|
|

|
|
|
|

|
|





|
|

|
|
|
|

|






|
|







|


|
|


|
|
|

|

|
|
|
|
|


|
|








|






|
|







|








|


|




|

|
|






|


|
|
|


|







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
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
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
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
    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);
}

/*
** Clear the given datum
*/
static void datumClear(datum *p){
  if( p->dptr ) free(p->dptr);
  p->dptr = 0;
  p->dsize = 0;
}

/*
** Fetch a single record from an open cursor.  Return 1 on success
** and 0 on failure.
*/
int sqliteDbbeFetch(DbbeCursor *pCursr, int nKey, char *pKey){
  datum key;
  key.dsize = nKey;
  key.dptr = pKey;
  datumClear(&pCursr->key);
  datumClear(&pCursr->data);
  if( pCursr->pFile && pCursr->pFile->dbf ){
    pCursr->data = gdbm_fetch(pCursr->pFile->dbf, key);
  }
  return pCursr->data.dptr!=0;
}

/*
** Return 1 if the given key is already in the table.  Return 0
** if it is not.
*/
int sqliteDbbeTest(DbbeCursor *pCursr, int nKey, char *pKey){
  datum key;
  int result = 0;
  key.dsize = nKey;
  key.dptr = pKey;
  if( pCursr->pFile && pCursr->pFile->dbf ){
    result = gdbm_exists(pCursr->pFile->dbf, key);
  }
  return result;
}

/*
** Copy bytes from the current key or data into a buffer supplied by
** the calling function.  Return the number of bytes copied.
*/
int sqliteDbbeCopyKey(DbbeCursor *pCursr, int offset, int size, char *zBuf){
  int n;
  if( offset>=pCursr->key.dsize ) return 0;
  if( offset+size>pCursr->key.dsize ){
    n = pCursr->key.dsize - offset;
  }else{
    n = size;
  }
  memcpy(zBuf, &pCursr->key.dptr[offset], n);
  return n;
}
int sqliteDbbeCopyData(DbbeCursor *pCursr, int offset, int size, char *zBuf){
  int n;
  if( pCursr->readPending && pCursr->pFile && pCursr->pFile->dbf ){
    pCursr->data = gdbm_fetch(pCursr->pFile->dbf, pCursr->key);
    pCursr->readPending = 0;
  }
  if( offset>=pCursr->data.dsize ) return 0;
  if( offset+size>pCursr->data.dsize ){
    n = pCursr->data.dsize - offset;
  }else{
    n = size;
  }
  memcpy(zBuf, &pCursr->data.dptr[offset], n);
  return n;
}

/*
** Return a pointer to bytes from the key or data.  The data returned
** is ephemeral.
*/
char *sqliteDbbeReadKey(DbbeCursor *pCursr, int offset){
  if( offset<0 || offset>=pCursr->key.dsize ) return "";
  return &pCursr->key.dptr[offset];
}
char *sqliteDbbeReadData(DbbeCursor *pCursr, int offset){
  if( pCursr->readPending && pCursr->pFile && pCursr->pFile->dbf ){
    pCursr->data = gdbm_fetch(pCursr->pFile->dbf, pCursr->key);
    pCursr->readPending = 0;
  }
  if( offset<0 || offset>=pCursr->data.dsize ) return "";
  return &pCursr->data.dptr[offset];
}

/*
** Return the total number of bytes in either data or key.
*/
int sqliteDbbeKeyLength(DbbeCursor *pCursr){
  return pCursr->key.dsize;
}
int sqliteDbbeDataLength(DbbeCursor *pCursr){
  if( pCursr->readPending && pCursr->pFile && pCursr->pFile->dbf ){
    pCursr->data = gdbm_fetch(pCursr->pFile->dbf, pCursr->key);
    pCursr->readPending = 0;
  }
  return pCursr->data.dsize;
}

/*
** Make is so that the next call to sqliteNextKey() finds the first
** key of the table.
*/
int sqliteDbbeRewind(DbbeCursor *pCursr){
  pCursr->needRewind = 1;
  return SQLITE_OK;
}

/*
** Read the next key from the table.  Return 1 on success.  Return
** 0 if there are no more keys.
*/
int sqliteDbbeNextKey(DbbeCursor *pCursr){
  datum nextkey;
  int rc;
  if( pCursr==0 || pCursr->pFile==0 || pCursr->pFile->dbf==0 ){
    pCursr->readPending = 0;
    return 0;
  }
  if( pCursr->needRewind ){
    nextkey = gdbm_firstkey(pCursr->pFile->dbf);
    pCursr->needRewind = 0;
  }else{
    nextkey = gdbm_nextkey(pCursr->pFile->dbf, pCursr->key);
  }
  datumClear(&pCursr->key);
  datumClear(&pCursr->data);
  pCursr->key = nextkey;
  if( pCursr->key.dptr ){
    pCursr->readPending = 1;
    rc = 1;
  }else{
    pCursr->needRewind = 1;
    pCursr->readPending = 0;
    rc = 0;
  }
  return rc;
}

/*
** Get a new integer key.
*/
int sqliteDbbeNew(DbbeCursor *pCursr){
  int iKey;
  datum key;
  int go = 1;
  int i;
  struct rc4 *pRc4;

  if( pCursr->pFile==0 || pCursr->pFile->dbf==0 ) return 1;
  pRc4 = &pCursr->pBe->rc4;
  while( go ){
    iKey = 0;
    for(i=0; i<4; i++){
      iKey = (iKey<<8) + rc4byte(pRc4);
    }
    key.dptr = (char*)&iKey;
    key.dsize = 4;
    go = gdbm_exists(pCursr->pFile->dbf, key);
  }
  return iKey;
}   

/*
** Write an entry into the table.  Overwrite any prior entry with the
** same key.
*/
int sqliteDbbePut(DbbeCursor *pCursr, int nKey,char *pKey,int nData,char *pData){
  datum data, key;
  int rc;
  if( pCursr->pFile==0 || pCursr->pFile->dbf==0 ) return SQLITE_ERROR;
  data.dsize = nData;
  data.dptr = pData;
  key.dsize = nKey;
  key.dptr = pKey;
  rc = gdbm_store(pCursr->pFile->dbf, key, data, GDBM_REPLACE);
  if( rc ) rc = SQLITE_ERROR;
  datumClear(&pCursr->key);
  datumClear(&pCursr->data);
  return rc;
}

/*
** Remove an entry from a table, if the entry exists.
*/
int sqliteDbbeDelete(DbbeCursor *pCursr, int nKey, char *pKey){
  datum key;
  int rc;
  datumClear(&pCursr->key);
  datumClear(&pCursr->data);
  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;
}

/*
** Open a temporary file.  The file should be deleted when closed.
**
Changes to src/dbbe.h.
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
** 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.5 2000/06/17 13:12:39 drh Exp $
*/
#ifndef _SQLITE_DBBE_H_
#define _SQLITE_DBBE_H_
#include <stdio.h>

/*
** The database backend supports two opaque structures.  A Dbbe is
** a context for the entire set of tables forming a complete
** database.  A DbbeTable is a single table.
**
** Note that at this level, the term "table" can mean either an
** SQL table or an SQL index.  In this module, a table stores a
** single arbitrary-length key and corresponding arbitrary-length
** data.  The differences between tables and indices, and the
** segregation of data into various fields or columns is handled
** by software at higher layers.
**
** The DbbeTable structure holds some state information, such as
** the key and data from the last retrieval.  For this reason, 
** the backend must allow the creation of multiple independent
** DbbeTable structures for each table in the database.
*/
typedef struct Dbbe Dbbe;
typedef struct DbbeTable DbbeTable;

/*
** The 18 interface routines.
*/

/* Open a complete database */
Dbbe *sqliteDbbeOpen(const char *zName, int write, int create, char **pzErr);

/* Close the whole database. */
void sqliteDbbeClose(Dbbe*);

/* Open a particular table of a previously opened database.
** Create the table if it doesn't already exist and writeable!=0.



**
** If zTableName is 0 or "", then a temporary table is created that
** will be deleted when closed.
*/
int sqliteDbbeOpenTable(Dbbe*, const char *zName, int writeable, DbbeTable **);

/* Delete a table from the database */
void sqliteDbbeDropTable(Dbbe*, const char *zTableName);

/* Reorganize a table to speed access or reduce its disk usage */
void sqliteDbbeReorganizeTable(Dbbe*, const char *zTableName);

/* Close a table */
void sqliteDbbeCloseTable(DbbeTable*);

/* Fetch an entry from a table with the given key.  Return 1 if
** successful and 0 if no such entry exists.
*/
int sqliteDbbeFetch(DbbeTable*, int nKey, char *pKey);

/* Return 1 if the given key is already in the table.  Return 0
** if it is not.
*/
int sqliteDbbeTest(DbbeTable*, int nKey, char *pKey);

/* Retrieve the key or data used for the last fetch.  Only size
** bytes are read beginning with the offset-th byte.  The return
** value is the actual number of bytes read.
*/
int sqliteDbbeCopyKey(DbbeTable*, int offset, int size, char *zBuf);
int sqliteDbbeCopyData(DbbeTable*, int offset, int size, char *zBuf);

/* Retrieve the key or data.  The result is ephemeral.  In other words,
** the result is stored in a buffer that might be overwritten on the next
** call to any DBBE routine.  If the results are needed for longer than
** that, you must make a copy.
*/
char *sqliteDbbeReadKey(DbbeTable*, int offset);
char *sqliteDbbeReadData(DbbeTable*, int offset);

/* Return the length of the most recently fetched key or data. */
int sqliteDbbeKeyLength(DbbeTable*);
int sqliteDbbeDataLength(DbbeTable*);

/* Retrieve the next entry in the table.  The first key is retrieved
** the first time this routine is called, or after a call to
** sqliteDbbeRewind().  The return value is 1 if there is another
** entry, or 0 if there are no more entries. */
int sqliteDbbeNextKey(DbbeTable*);

/* Make it so that the next call to sqliteDbbeNextKey() returns
** the first entry of the table. */
int sqliteDbbeRewind(DbbeTable*);

/* Get a new integer key for this table. */
int sqliteDbbeNew(DbbeTable*);

/* Write an entry into a table.  If another entry already exists with
** the same key, the old entry is discarded first.
*/
int sqliteDbbePut(DbbeTable*, int nKey, char *pKey, int nData, char *pData);

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

/* Open a file suitable for temporary storage */
int sqliteDbbeOpenTempFile(Dbbe*, FILE**);

/* Close a temporary file */
void sqliteDbbeCloseTempFile(Dbbe *, FILE *);








|








|








|


|


|











|
|
>
>
>

|


|







|
|




|




|





|
|






|
|


|
|





|



|


|




|


|







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
** 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.6 2000/06/21 13:59:11 drh Exp $
*/
#ifndef _SQLITE_DBBE_H_
#define _SQLITE_DBBE_H_
#include <stdio.h>

/*
** The database backend supports two opaque structures.  A Dbbe is
** a context for the entire set of tables forming a complete
** database.  A DbbeCursor is a pointer into a single single table.
**
** Note that at this level, the term "table" can mean either an
** SQL table or an SQL index.  In this module, a table stores a
** single arbitrary-length key and corresponding arbitrary-length
** data.  The differences between tables and indices, and the
** segregation of data into various fields or columns is handled
** by software at higher layers.
**
** The DbbeCursor structure holds some state information, such as
** the key and data from the last retrieval.  For this reason, 
** the backend must allow the creation of multiple independent
** DbbeCursor structures for each table in the database.
*/
typedef struct Dbbe Dbbe;
typedef struct DbbeCursor DbbeCursor;

/*
** The 18 interface routines.
*/

/* Open a complete database */
Dbbe *sqliteDbbeOpen(const char *zName, int write, int create, char **pzErr);

/* Close the whole database. */
void sqliteDbbeClose(Dbbe*);

/* Open a cursor into particular file of a previously opened database.
** Create the file if it doesn't already exist and writeable!=0.  zName
** is the base name of the file to be opened.  This routine will add
** an appropriate path and extension to the filename to locate the 
** actual file.
**
** If zName is 0 or "", then a temporary file is created that
** will be deleted when closed.
*/
int sqliteDbbeOpenCursor(Dbbe*, const char *zName, int writeable, DbbeCursor**);

/* Delete a table from the database */
void sqliteDbbeDropTable(Dbbe*, const char *zTableName);

/* Reorganize a table to speed access or reduce its disk usage */
void sqliteDbbeReorganizeTable(Dbbe*, const char *zTableName);

/* Close a cursor */
void sqliteDbbeCloseCursor(DbbeCursor*);

/* Fetch an entry from a table with the given key.  Return 1 if
** successful and 0 if no such entry exists.
*/
int sqliteDbbeFetch(DbbeCursor*, int nKey, char *pKey);

/* Return 1 if the given key is already in the table.  Return 0
** if it is not.
*/
int sqliteDbbeTest(DbbeCursor*, int nKey, char *pKey);

/* Retrieve the key or data used for the last fetch.  Only size
** bytes are read beginning with the offset-th byte.  The return
** value is the actual number of bytes read.
*/
int sqliteDbbeCopyKey(DbbeCursor*, int offset, int size, char *zBuf);
int sqliteDbbeCopyData(DbbeCursor*, int offset, int size, char *zBuf);

/* Retrieve the key or data.  The result is ephemeral.  In other words,
** the result is stored in a buffer that might be overwritten on the next
** call to any DBBE routine.  If the results are needed for longer than
** that, you must make a copy.
*/
char *sqliteDbbeReadKey(DbbeCursor*, int offset);
char *sqliteDbbeReadData(DbbeCursor*, int offset);

/* Return the length of the most recently fetched key or data. */
int sqliteDbbeKeyLength(DbbeCursor*);
int sqliteDbbeDataLength(DbbeCursor*);

/* Retrieve the next entry in the table.  The first key is retrieved
** the first time this routine is called, or after a call to
** sqliteDbbeRewind().  The return value is 1 if there is another
** entry, or 0 if there are no more entries. */
int sqliteDbbeNextKey(DbbeCursor*);

/* Make it so that the next call to sqliteDbbeNextKey() returns
** the first entry of the table. */
int sqliteDbbeRewind(DbbeCursor*);

/* Get a new integer key for this table. */
int sqliteDbbeNew(DbbeCursor*);

/* Write an entry into a table.  If another entry already exists with
** the same key, the old entry is discarded first.
*/
int sqliteDbbePut(DbbeCursor*, int nKey, char *pKey, int nData, char *pData);

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

/* Open a file suitable for temporary storage */
int sqliteDbbeOpenTempFile(Dbbe*, FILE**);

/* Close a temporary file */
void sqliteDbbeCloseTempFile(Dbbe *, FILE *);

Changes to src/delete.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 DELETE FROM statements.
**
** $Id: delete.c,v 1.5 2000/06/17 13:12:39 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process a DELETE FROM statement.
*/
void sqliteDeleteFrom(







|







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 DELETE FROM statements.
**
** $Id: delete.c,v 1.6 2000/06/21 13:59:11 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process a DELETE FROM statement.
*/
void sqliteDeleteFrom(
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
        " may not be modified", 0);
      pParse->nErr++;
      goto delete_from_cleanup;
    }
  }
  pTab = pTabList->a[0].pTab;

  /* Resolve the field names in all the expressions.
  */
  if( pWhere ){
    sqliteExprResolveInSelect(pParse, pWhere);
    if( sqliteExprResolveIds(pParse, pTabList, pWhere) ){
      goto delete_from_cleanup;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){







|







64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
        " may not be modified", 0);
      pParse->nErr++;
      goto delete_from_cleanup;
    }
  }
  pTab = pTabList->a[0].pTab;

  /* Resolve the column names in all the expressions.
  */
  if( pWhere ){
    sqliteExprResolveInSelect(pParse, pWhere);
    if( sqliteExprResolveIds(pParse, pTabList, pWhere) ){
      goto delete_from_cleanup;
    }
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
  addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end, 0, 0);
  if( pTab->pIndex ){
    sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Fetch, base, 0, 0, 0);
    for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
      int j;
      sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
      for(j=0; j<pIdx->nField; j++){
        sqliteVdbeAddOp(v, OP_Field, base, pIdx->aiField[j], 0, 0);
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nField, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_DeleteIdx, base+i, 0, 0, 0);
    }
  }
  sqliteVdbeAddOp(v, OP_Delete, base, 0, 0, 0);
  sqliteVdbeAddOp(v, OP_Goto, 0, addr, 0, 0);
  sqliteVdbeAddOp(v, OP_ListClose, 0, 0, 0, end);

delete_from_cleanup:
  sqliteIdListDelete(pTabList);
  sqliteExprDelete(pWhere);
  return;
}







|
|

|












113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
  addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end, 0, 0);
  if( pTab->pIndex ){
    sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Fetch, base, 0, 0, 0);
    for(i=1, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
      int j;
      sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
      for(j=0; j<pIdx->nColumn; j++){
        sqliteVdbeAddOp(v, OP_Field, base, pIdx->aiColumn[j], 0, 0);
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nColumn, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_DeleteIdx, base+i, 0, 0, 0);
    }
  }
  sqliteVdbeAddOp(v, OP_Delete, base, 0, 0, 0);
  sqliteVdbeAddOp(v, OP_Goto, 0, addr, 0, 0);
  sqliteVdbeAddOp(v, OP_ListClose, 0, 0, 0, end);

delete_from_cleanup:
  sqliteIdListDelete(pTabList);
  sqliteExprDelete(pWhere);
  return;
}
Changes to src/expr.c.
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
**   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.17 2000/06/17 13:12:40 drh Exp $
*/
#include "sqliteInt.h"

/*
** Walk an expression tree.  Return 1 if the expression is constant
** and 0 if it involves variables.
*/
static int isConstant(Expr *p){
  switch( p->op ){
    case TK_ID:
    case TK_FIELD:
    case TK_DOT:
      return 0;
    default: {
      if( p->pLeft && !isConstant(p->pLeft) ) return 0;
      if( p->pRight && !isConstant(p->pRight) ) return 0;
      if( p->pList ){
        int i;







|










|







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
**   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.18 2000/06/21 13:59:11 drh Exp $
*/
#include "sqliteInt.h"

/*
** Walk an expression tree.  Return 1 if the expression is constant
** and 0 if it involves variables.
*/
static int isConstant(Expr *p){
  switch( p->op ){
    case TK_ID:
    case TK_COLUMN:
    case TK_DOT:
      return 0;
    default: {
      if( p->pLeft && !isConstant(p->pLeft) ) return 0;
      if( p->pRight && !isConstant(p->pRight) ) return 0;
      if( p->pList ){
        int i;
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
}

/*
** Walk the expression tree and process operators of the form:
**
**       expr IN (SELECT ...)
**
** These operators have to be processed before field names are
** resolved because each such operator increments pParse->nTab
** to reserve cursor numbers for its own use.  But pParse->nTab
** needs to be constant once we begin resolving field names.
**
** Actually, the processing of IN-SELECT is only started by this
** routine.  This routine allocates a cursor number to the IN-SELECT
** and then moves on.  The code generation is done by 
** sqliteExprResolveIds() which must be called afterwards.
*/
void sqliteExprResolveInSelect(Parse *pParse, Expr *pExpr){







|


|







54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
}

/*
** Walk the expression tree and process operators of the form:
**
**       expr IN (SELECT ...)
**
** These operators have to be processed before column names are
** resolved because each such operator increments pParse->nTab
** to reserve cursor numbers for its own use.  But pParse->nTab
** needs to be constant once we begin resolving column names.
**
** Actually, the processing of IN-SELECT is only started by this
** routine.  This routine allocates a cursor number to the IN-SELECT
** and then moves on.  The code generation is done by 
** sqliteExprResolveIds() which must be called afterwards.
*/
void sqliteExprResolveInSelect(Parse *pParse, Expr *pExpr){
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
      }
    }
  }
}

/*
** This routine walks an expression tree and resolves references to
** table fields.  Nodes of the form ID.ID or ID resolve into an
** index to the table in the table list and a field offset.  The opcode
** for such nodes is changed to TK_FIELD.  The iTable value is changed
** to the index of the referenced table in pTabList plus the pParse->nTab
** value.  The iField value is changed to the index of the field of the 
** referenced table.
**
** We also check for instances of the IN operator.  IN comes in two
** forms:
**
**           expr IN (exprlist)
** and
**           expr IN (SELECT ...)
**
** The first form is handled by creating a set holding the list
** of allowed values.  The second form causes the SELECT to generate 
** a temporary table.
**
** This routine also looks for scalar SELECTs that are part of an expression.
** If it finds any, it generates code to write the value of that select
** into a memory cell.
**
** Unknown fields or tables provoke an error.  The function returns
** 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->iField = j;
          }
        }
      }
      sqliteFree(z);
      if( cnt==0 ){
        sqliteSetNString(&pParse->zErrMsg, "no such field: ", -1,  
          pExpr->token.z, pExpr->token.n, 0);
        pParse->nErr++;
        return 1;
      }else if( cnt>1 ){
        sqliteSetNString(&pParse->zErrMsg, "ambiguous field name: ", -1,  
          pExpr->token.z, pExpr->token.n, 0);
        pParse->nErr++;
        return 1;
      }
      pExpr->op = TK_FIELD;
      break; 
    }
  
    /* A table name and field 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;







|
|
|

|

















|


















|





|




|




|



|







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

/*
** 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
** value.  The iColumn value is changed to the index of the column of the 
** referenced table.
**
** We also check for instances of the IN operator.  IN comes in two
** forms:
**
**           expr IN (exprlist)
** and
**           expr IN (SELECT ...)
**
** The first form is handled by creating a set holding the list
** of allowed values.  The second form causes the SELECT to generate 
** a temporary table.
**
** This routine also looks for scalar SELECTs that are part of an expression.
** If it finds any, it generates code to write the value of that select
** into a memory cell.
**
** Unknown columns or tables provoke an error.  The function returns
** 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;
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
          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->iField = j;
          }
        }
      }
      sqliteFree(zLeft);
      sqliteFree(zRight);
      if( cnt==0 ){
        sqliteSetNString(&pParse->zErrMsg, "no such field: ", -1,  
          pLeft->token.z, pLeft->token.n, ".", 1, 
          pRight->token.z, pRight->token.n, 0);
        pParse->nErr++;
        return 1;
      }else if( cnt>1 ){
        sqliteSetNString(&pParse->zErrMsg, "ambiguous field name: ", -1,  
          pLeft->token.z, pLeft->token.n, ".", 1,
          pRight->token.z, pRight->token.n, 0);
        pParse->nErr++;
        return 1;
      }
      sqliteExprDelete(pLeft);
      pExpr->pLeft = 0;
      sqliteExprDelete(pRight);
      pExpr->pRight = 0;
      pExpr->op = TK_FIELD;
      break;
    }

    case TK_IN: {
      Vdbe *v = sqliteGetVdbe(pParse);
      if( v==0 ) return 1;
      if( sqliteExprResolveIds(pParse, pTabList, pExpr->pLeft) ){







|






|





|









|







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
          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);
        pParse->nErr++;
        return 1;
      }else if( cnt>1 ){
        sqliteSetNString(&pParse->zErrMsg, "ambiguous column name: ", -1,  
          pLeft->token.z, pLeft->token.n, ".", 1,
          pRight->token.z, pRight->token.n, 0);
        pParse->nErr++;
        return 1;
      }
      sqliteExprDelete(pLeft);
      pExpr->pLeft = 0;
      sqliteExprDelete(pRight);
      pExpr->pRight = 0;
      pExpr->op = TK_COLUMN;
      break;
    }

    case TK_IN: {
      Vdbe *v = sqliteGetVdbe(pParse);
      if( v==0 ) return 1;
      if( sqliteExprResolveIds(pParse, pTabList, pExpr->pLeft) ){
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
      }
      break;
    }

    case TK_SELECT: {
      /* This has to be a scalar SELECT.  Generate code to put the
      ** value of this select in a memory cell and record the number
      ** of the memory cell in iField.
      */
      pExpr->iField = pParse->nMem++;
      if( sqliteSelect(pParse, pExpr->pSelect, SRT_Mem, pExpr->iField) ){
        return 1;
      }
      break;
    }

    /* For all else, just recursively walk the tree */
    default: {







|

|
|







259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
      }
      break;
    }

    case TK_SELECT: {
      /* This has to be a scalar SELECT.  Generate code to put the
      ** value of this select in a memory cell and record the number
      ** of the memory cell in iColumn.
      */
      pExpr->iColumn = pParse->nMem++;
      if( sqliteSelect(pParse, pExpr->pSelect, SRT_Mem, pExpr->iColumn) ){
        return 1;
      }
      break;
    }

    /* For all else, just recursively walk the tree */
    default: {
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
      int id = sqliteFuncId(&pExpr->token);
      int n = pExpr->pList ? pExpr->pList->nExpr : 0;
      int no_such_func = 0;
      int too_many_args = 0;
      int too_few_args = 0;
      int is_agg = 0;
      int i;
      pExpr->iField = id;
      switch( id ){
        case FN_Unknown: { 
          no_such_func = 1;
          break;
        }
        case FN_Count: { 
          no_such_func = !allowAgg;







|







351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
      int id = sqliteFuncId(&pExpr->token);
      int n = pExpr->pList ? pExpr->pList->nExpr : 0;
      int no_such_func = 0;
      int too_many_args = 0;
      int too_few_args = 0;
      int is_agg = 0;
      int i;
      pExpr->iColumn = id;
      switch( id ){
        case FN_Unknown: { 
          no_such_func = 1;
          break;
        }
        case FN_Count: { 
          no_such_func = !allowAgg;
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
    case TK_ISNULL:   op = OP_IsNull;   break;
    case TK_NOTNULL:  op = OP_NotNull;  break;
    case TK_NOT:      op = OP_Not;      break;
    case TK_UMINUS:   op = OP_Negative; break;
    default: break;
  }
  switch( pExpr->op ){
    case TK_FIELD: {
      if( pParse->useAgg ){
        sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg, 0, 0);
      }else{
        sqliteVdbeAddOp(v, OP_Field, pExpr->iTable, pExpr->iField, 0, 0);
      }
      break;
    }
    case TK_INTEGER: {
      int i = atoi(pExpr->token.z);
      sqliteVdbeAddOp(v, OP_Integer, i, 0, 0, 0);
      break;







|



|







463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
    case TK_ISNULL:   op = OP_IsNull;   break;
    case TK_NOTNULL:  op = OP_NotNull;  break;
    case TK_NOT:      op = OP_Not;      break;
    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;
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
      dest = sqliteVdbeCurrentAddr(v) + 2;
      sqliteVdbeAddOp(v, op, 0, dest, 0, 0);
      sqliteVdbeAddOp(v, OP_AddImm, -1, 0, 0, 0);
      break;
    }
    case TK_AGG_FUNCTION: {
      sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg, 0, 0);
      if( pExpr->iField==FN_Avg ){
        assert( pParse->iAggCount>=0 && pParse->iAggCount<pParse->nAgg );
        sqliteVdbeAddOp(v, OP_AggGet, 0, pParse->iAggCount, 0, 0);
        sqliteVdbeAddOp(v, OP_Divide, 0, 0, 0, 0);
      }
      break;
    }
    case TK_FUNCTION: {
      int id = pExpr->iField;
      int op;
      int i;
      ExprList *pList = pExpr->pList;
      if( id==FN_Fcnt ){
        sqliteVdbeAddOp(v, OP_Fcnt, 0, 0, 0, 0);
        break;
      }
      op = id==FN_Min ? OP_Min : OP_Max;
      for(i=0; i<pList->nExpr; i++){
        sqliteExprCode(pParse, pList->a[i].pExpr);
        if( i>0 ){
          sqliteVdbeAddOp(v, op, 0, 0, 0, 0);
        }
      }
      break;
    }
    case TK_SELECT: {
      sqliteVdbeAddOp(v, OP_MemLoad, pExpr->iField, 0, 0, 0);
      break;
    }
    case TK_IN: {
      int addr;
      sqliteVdbeAddOp(v, OP_Integer, 1, 0, 0, 0);
      sqliteExprCode(pParse, pExpr->pLeft);
      addr = sqliteVdbeCurrentAddr(v);







|







|

















|







558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
      dest = sqliteVdbeCurrentAddr(v) + 2;
      sqliteVdbeAddOp(v, op, 0, dest, 0, 0);
      sqliteVdbeAddOp(v, OP_AddImm, -1, 0, 0, 0);
      break;
    }
    case TK_AGG_FUNCTION: {
      sqliteVdbeAddOp(v, OP_AggGet, 0, pExpr->iAgg, 0, 0);
      if( pExpr->iColumn==FN_Avg ){
        assert( pParse->iAggCount>=0 && pParse->iAggCount<pParse->nAgg );
        sqliteVdbeAddOp(v, OP_AggGet, 0, pParse->iAggCount, 0, 0);
        sqliteVdbeAddOp(v, OP_Divide, 0, 0, 0, 0);
      }
      break;
    }
    case TK_FUNCTION: {
      int id = pExpr->iColumn;
      int op;
      int i;
      ExprList *pList = pExpr->pList;
      if( id==FN_Fcnt ){
        sqliteVdbeAddOp(v, OP_Fcnt, 0, 0, 0, 0);
        break;
      }
      op = id==FN_Min ? OP_Min : OP_Max;
      for(i=0; i<pList->nExpr; i++){
        sqliteExprCode(pParse, pList->a[i].pExpr);
        if( i>0 ){
          sqliteVdbeAddOp(v, op, 0, 0, 0, 0);
        }
      }
      break;
    }
    case TK_SELECT: {
      sqliteVdbeAddOp(v, OP_MemLoad, pExpr->iColumn, 0, 0, 0);
      break;
    }
    case TK_IN: {
      int addr;
      sqliteVdbeAddOp(v, OP_Integer, 1, 0, 0, 0);
      sqliteExprCode(pParse, pExpr->pLeft);
      addr = sqliteVdbeCurrentAddr(v);
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
int sqliteExprAnalyzeAggregates(Parse *pParse, Expr *pExpr){
  int i;
  AggExpr *aAgg;
  int nErr = 0;

  if( pExpr==0 ) return 0;
  switch( pExpr->op ){
    case TK_FIELD: {
      aAgg = pParse->aAgg;
      for(i=0; i<pParse->nAgg; i++){
        if( aAgg[i].isAgg ) continue;
        if( aAgg[i].pExpr->iTable==pExpr->iTable
         && aAgg[i].pExpr->iField==pExpr->iField ){
          break;
        }
      }
      if( i>=pParse->nAgg ){
        i = appendAggInfo(pParse);
        if( i<0 ) return 1;
        pParse->aAgg[i].isAgg = 0;
        pParse->aAgg[i].pExpr = pExpr;
      }
      pExpr->iAgg = i;
      break;
    }
    case TK_AGG_FUNCTION: {
      if( pExpr->iField==FN_Count || pExpr->iField==FN_Avg ){
        if( pParse->iAggCount>=0 ){
          i = pParse->iAggCount;
        }else{
          i = appendAggInfo(pParse);
          if( i<0 ) return 1;
          pParse->aAgg[i].isAgg = 1;
          pParse->aAgg[i].pExpr = 0;
          pParse->iAggCount = i;
        }
        if( pExpr->iField==FN_Count ){
          pExpr->iAgg = i;
          break;
        }
      }
      aAgg = pParse->aAgg;
      for(i=0; i<pParse->nAgg; i++){
        if( !aAgg[i].isAgg ) continue;







|




|













|









|







860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
int sqliteExprAnalyzeAggregates(Parse *pParse, Expr *pExpr){
  int i;
  AggExpr *aAgg;
  int nErr = 0;

  if( pExpr==0 ) return 0;
  switch( pExpr->op ){
    case TK_COLUMN: {
      aAgg = pParse->aAgg;
      for(i=0; i<pParse->nAgg; i++){
        if( aAgg[i].isAgg ) continue;
        if( aAgg[i].pExpr->iTable==pExpr->iTable
         && aAgg[i].pExpr->iColumn==pExpr->iColumn ){
          break;
        }
      }
      if( i>=pParse->nAgg ){
        i = appendAggInfo(pParse);
        if( i<0 ) return 1;
        pParse->aAgg[i].isAgg = 0;
        pParse->aAgg[i].pExpr = pExpr;
      }
      pExpr->iAgg = i;
      break;
    }
    case TK_AGG_FUNCTION: {
      if( pExpr->iColumn==FN_Count || pExpr->iColumn==FN_Avg ){
        if( pParse->iAggCount>=0 ){
          i = pParse->iAggCount;
        }else{
          i = appendAggInfo(pParse);
          if( i<0 ) return 1;
          pParse->aAgg[i].isAgg = 1;
          pParse->aAgg[i].pExpr = 0;
          pParse->iAggCount = i;
        }
        if( pExpr->iColumn==FN_Count ){
          pExpr->iAgg = i;
          break;
        }
      }
      aAgg = pParse->aAgg;
      for(i=0; i<pParse->nAgg; i++){
        if( !aAgg[i].isAgg ) continue;
Changes to src/insert.c.
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
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements.
**
** $Id: insert.c,v 1.10 2000/06/17 13:12:40 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
**    insert into TABLE (IDLIST) select
**
** The IDLIST following the table name is always optional.  If omitted,
** then a list of all columns for the table is substituted.  The IDLIST
** appears in the pField parameter.  pField is NULL if IDLIST is omitted.
**
** The pList parameter holds EXPRLIST in the first form of the INSERT
** statement above, and pSelect is NULL.  For the second form, pList is
** NULL and pSelect is a pointer to the select statement used to generate
** data for the insert.
*/
void sqliteInsert(
  Parse *pParse,        /* Parser context */
  Token *pTableName,    /* Name of table into which we are inserting */
  ExprList *pList,      /* List of values to be inserted */
  Select *pSelect,      /* A SELECT statement to use as the data source */
  IdList *pField        /* Field names corresponding to IDLIST. */
){
  Table *pTab;          /* The table to insert into */
  char *zTab;           /* Name of the table into which we are inserting */
  int i, j, idx;        /* Loop counters */
  Vdbe *v;              /* Generate code into this virtual machine */
  Index *pIdx;          /* For looping over indices of the table */
  int srcTab;           /* Date comes from this temporary cursor if >=0 */
  int nField;           /* Number of columns in the data */
  int base;             /* First available cursor */
  int iCont, iBreak;    /* Beginning and end of the loop over srcTab */

  /* Locate the table into which we will be inserting new information.
  */
  zTab = sqliteTableNameFromToken(pTableName);
  pTab = sqliteFindTable(pParse->db, zTab);







|











|











|







|







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
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle INSERT statements.
**
** $Id: insert.c,v 1.11 2000/06/21 13:59:12 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
**    insert into TABLE (IDLIST) select
**
** The IDLIST following the table name is always optional.  If omitted,
** then a list of all columns for the table is substituted.  The IDLIST
** appears in the pColumn parameter.  pColumn is NULL if IDLIST is omitted.
**
** The pList parameter holds EXPRLIST in the first form of the INSERT
** statement above, and pSelect is NULL.  For the second form, pList is
** NULL and pSelect is a pointer to the select statement used to generate
** data for the insert.
*/
void sqliteInsert(
  Parse *pParse,        /* Parser context */
  Token *pTableName,    /* Name of table into which we are inserting */
  ExprList *pList,      /* List of values to be inserted */
  Select *pSelect,      /* A SELECT statement to use as the data source */
  IdList *pColumn       /* Column names corresponding to IDLIST. */
){
  Table *pTab;          /* The table to insert into */
  char *zTab;           /* Name of the table into which we are inserting */
  int i, j, idx;        /* Loop counters */
  Vdbe *v;              /* Generate code into this virtual machine */
  Index *pIdx;          /* For looping over indices of the table */
  int srcTab;           /* Date comes from this temporary cursor if >=0 */
  int nColumn;          /* Number of columns in the data */
  int base;             /* First available cursor */
  int iCont, iBreak;    /* Beginning and end of the loop over srcTab */

  /* Locate the table into which we will be inserting new information.
  */
  zTab = sqliteTableNameFromToken(pTableName);
  pTab = sqliteFindTable(pParse->db, zTab);
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
  if( pSelect ){
    int rc;
    srcTab = pParse->nTab++;
    sqliteVdbeAddOp(v, OP_Open, srcTab, 1, 0, 0);
    rc = sqliteSelect(pParse, pSelect, SRT_Table, srcTab);
    if( rc ) goto insert_cleanup;
    assert( pSelect->pEList );
    nField = pSelect->pEList->nExpr;
  }else{
    srcTab = -1;
    assert( pList );
    nField = pList->nExpr;
  }

  /* Make sure the number of columns in the source data matches the number
  ** of columns to be inserted into the table.
  */
  if( pField==0 && nField!=pTab->nCol ){
    char zNum1[30];
    char zNum2[30];
    sprintf(zNum1,"%d", nField);
    sprintf(zNum2,"%d", pTab->nCol);
    sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
       " has ", zNum2, " columns but ",
       zNum1, " values were supplied", 0);
    pParse->nErr++;
    goto insert_cleanup;
  }
  if( pField!=0 && nField!=pField->nId ){
    char zNum1[30];
    char zNum2[30];
    sprintf(zNum1,"%d", nField);
    sprintf(zNum2,"%d", pField->nId);
    sqliteSetString(&pParse->zErrMsg, zNum1, " values for ",
       zNum2, " columns", 0);
    pParse->nErr++;
    goto insert_cleanup;
  }

  /* If the INSERT statement included an IDLIST term, then make sure
  ** all elements of the IDLIST really are columns of the table and 
  ** remember the column indices.
  */
  if( pField ){
    for(i=0; i<pField->nId; i++){
      pField->a[i].idx = -1;
    }
    for(i=0; i<pField->nId; i++){
      for(j=0; j<pTab->nCol; j++){
        if( sqliteStrICmp(pField->a[i].zName, pTab->aCol[j].zName)==0 ){
          pField->a[i].idx = j;
          break;
        }
      }
      if( j>=pTab->nCol ){
        sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
           " has no column named ", pField->a[i].zName, 0);
        pParse->nErr++;
        goto insert_cleanup;
      }
    }
  }

  /* Open cursors into the table that is received the new data and







|



|





|


|







|


|
|










|
|
|

|

|
|





|







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
  if( pSelect ){
    int rc;
    srcTab = pParse->nTab++;
    sqliteVdbeAddOp(v, OP_Open, srcTab, 1, 0, 0);
    rc = sqliteSelect(pParse, pSelect, SRT_Table, srcTab);
    if( rc ) goto insert_cleanup;
    assert( pSelect->pEList );
    nColumn = pSelect->pEList->nExpr;
  }else{
    srcTab = -1;
    assert( pList );
    nColumn = pList->nExpr;
  }

  /* Make sure the number of columns in the source data matches the number
  ** of columns to be inserted into the table.
  */
  if( pColumn==0 && nColumn!=pTab->nCol ){
    char zNum1[30];
    char zNum2[30];
    sprintf(zNum1,"%d", nColumn);
    sprintf(zNum2,"%d", pTab->nCol);
    sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
       " has ", zNum2, " columns but ",
       zNum1, " values were supplied", 0);
    pParse->nErr++;
    goto insert_cleanup;
  }
  if( pColumn!=0 && nColumn!=pColumn->nId ){
    char zNum1[30];
    char zNum2[30];
    sprintf(zNum1,"%d", nColumn);
    sprintf(zNum2,"%d", pColumn->nId);
    sqliteSetString(&pParse->zErrMsg, zNum1, " values for ",
       zNum2, " columns", 0);
    pParse->nErr++;
    goto insert_cleanup;
  }

  /* If the INSERT statement included an IDLIST term, then make sure
  ** all elements of the IDLIST really are columns of the table and 
  ** remember the column indices.
  */
  if( pColumn ){
    for(i=0; i<pColumn->nId; i++){
      pColumn->a[i].idx = -1;
    }
    for(i=0; i<pColumn->nId; i++){
      for(j=0; j<pTab->nCol; j++){
        if( sqliteStrICmp(pColumn->a[i].zName, pTab->aCol[j].zName)==0 ){
          pColumn->a[i].idx = j;
          break;
        }
      }
      if( j>=pTab->nCol ){
        sqliteSetString(&pParse->zErrMsg, "table ", pTab->zName,
           " has no column named ", pColumn->a[i].zName, 0);
        pParse->nErr++;
        goto insert_cleanup;
      }
    }
  }

  /* Open cursors into the table that is received the new data and
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
  /* Create a new entry in the table and fill it with data.
  */
  sqliteVdbeAddOp(v, OP_New, 0, 0, 0, 0);
  if( pTab->pIndex ){
    sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
  }
  for(i=0; i<pTab->nCol; i++){
    if( pField==0 ){
      j = i;
    }else{
      for(j=0; j<pField->nId; j++){
        if( pField->a[j].idx==i ) break;
      }
    }
    if( pField && j>=pField->nId ){
      char *zDflt = pTab->aCol[i].zDflt;
      if( zDflt==0 ){
        sqliteVdbeAddOp(v, OP_Null, 0, 0, 0, 0);
      }else{
        sqliteVdbeAddOp(v, OP_String, 0, 0, zDflt, 0);
      }
    }else if( srcTab>=0 ){







|


|
|


|







175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
  /* Create a new entry in the table and fill it with data.
  */
  sqliteVdbeAddOp(v, OP_New, 0, 0, 0, 0);
  if( pTab->pIndex ){
    sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
  }
  for(i=0; i<pTab->nCol; i++){
    if( pColumn==0 ){
      j = i;
    }else{
      for(j=0; j<pColumn->nId; j++){
        if( pColumn->a[j].idx==i ) break;
      }
    }
    if( pColumn && j>=pColumn->nId ){
      char *zDflt = pTab->aCol[i].zDflt;
      if( zDflt==0 ){
        sqliteVdbeAddOp(v, OP_Null, 0, 0, 0, 0);
      }else{
        sqliteVdbeAddOp(v, OP_String, 0, 0, zDflt, 0);
      }
    }else if( srcTab>=0 ){
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
  /* Create appropriate entries for the new data row in all indices
  ** of the table.
  */
  for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
    if( pIdx->pNext ){
      sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
    }
    for(i=0; i<pIdx->nField; i++){
      int idx = pIdx->aiField[i];
      if( pField==0 ){
        j = idx;
      }else{
        for(j=0; j<pField->nId; j++){
          if( pField->a[j].idx==idx ) break;
        }
      }
      if( pField && j>=pField->nId ){
        char *zDflt = pTab->aCol[idx].zDflt;
        if( zDflt==0 ){
          sqliteVdbeAddOp(v, OP_Null, 0, 0, 0, 0);
        }else{
          sqliteVdbeAddOp(v, OP_String, 0, 0, zDflt, 0);
        }
      }else if( srcTab>=0 ){
        sqliteVdbeAddOp(v, OP_Field, srcTab, idx, 0, 0); 
      }else{
        sqliteExprCode(pParse, pList->a[j].pExpr);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nField, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_PutIdx, idx+base, 0, 0, 0);
  }

  /* The bottom of the loop, if the data source is a SELECT statement
  */
  if( srcTab>=0 ){
    sqliteVdbeAddOp(v, OP_Goto, 0, iCont, 0, 0);
    sqliteVdbeAddOp(v, OP_Noop, 0, 0, 0, iBreak);
  }

insert_cleanup:
  if( pList ) sqliteExprListDelete(pList);
  if( pSelect ) sqliteSelectDelete(pSelect);
  sqliteIdListDelete(pField);
}







|
|
|


|
|


|












|













|

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
  /* Create appropriate entries for the new data row in all indices
  ** of the table.
  */
  for(idx=1, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext, idx++){
    if( pIdx->pNext ){
      sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
    }
    for(i=0; i<pIdx->nColumn; i++){
      int idx = pIdx->aiColumn[i];
      if( pColumn==0 ){
        j = idx;
      }else{
        for(j=0; j<pColumn->nId; j++){
          if( pColumn->a[j].idx==idx ) break;
        }
      }
      if( pColumn && j>=pColumn->nId ){
        char *zDflt = pTab->aCol[idx].zDflt;
        if( zDflt==0 ){
          sqliteVdbeAddOp(v, OP_Null, 0, 0, 0, 0);
        }else{
          sqliteVdbeAddOp(v, OP_String, 0, 0, zDflt, 0);
        }
      }else if( srcTab>=0 ){
        sqliteVdbeAddOp(v, OP_Field, srcTab, idx, 0, 0); 
      }else{
        sqliteExprCode(pParse, pList->a[j].pExpr);
      }
    }
    sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nColumn, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_PutIdx, idx+base, 0, 0, 0);
  }

  /* The bottom of the loop, if the data source is a SELECT statement
  */
  if( srcTab>=0 ){
    sqliteVdbeAddOp(v, OP_Goto, 0, iCont, 0, 0);
    sqliteVdbeAddOp(v, OP_Noop, 0, 0, 0, iBreak);
  }

insert_cleanup:
  if( pList ) sqliteExprListDelete(pList);
  if( pSelect ) sqliteSelectDelete(pSelect);
  sqliteIdListDelete(pColumn);
}
Changes to src/main.c.
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.12 2000/06/07 23:51:50 drh Exp $
*/
#include "sqliteInt.h"

/*
** This is the callback routine for the code that initializes the
** database.  Each callback contains text of a CREATE TABLE or
** CREATE INDEX statement that must be parsed to yield the internal







|







22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
**
*************************************************************************
** Main file for the SQLite library.  The routines in this file
** implement the programmer interface to the library.  Routines in
** other files are for internal use by SQLite and should not be
** accessed by users of the library.
**
** $Id: main.c,v 1.13 2000/06/21 13:59:12 drh Exp $
*/
#include "sqliteInt.h"

/*
** This is the callback routine for the code that initializes the
** database.  Each callback contains text of a CREATE TABLE or
** CREATE INDEX statement that must be parsed to yield the internal
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
  **        type       text,    --  Either "table" or "index"
  **        name       text,    --  Name of table or index
  **        tbl_name   text,    --  Associated table 
  **        sql        text     --  The CREATE statement for this object
  **    );
  **
  ** The sqlite_master table contains a single entry for each table
  ** and each index.  The "type" field tells whether the entry is
  ** a table or index.  The "name" field is the name of the object.
  ** The "tbl_name" is the name of the associated table.  For tables,
  ** the tbl_name field is always the same as name.  For indices, the
  ** tbl_name field contains the name of the table that the index
  ** indexes.  Finally, the sql field contains the complete text of
  ** the CREATE TABLE or CREATE INDEX statement that originally created
  ** the table or index.
  **
  ** The following program invokes its callback on the SQL for each
  ** table then goes back and invokes the callback on the
  ** SQL for each index.  The callback will invoke the
  ** parser to build the internal representation of the







|
|

|
|
|







86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
  **        type       text,    --  Either "table" or "index"
  **        name       text,    --  Name of table or index
  **        tbl_name   text,    --  Associated table 
  **        sql        text     --  The CREATE statement for this object
  **    );
  **
  ** The sqlite_master table contains a single entry for each table
  ** and each index.  The "type" column tells whether the entry is
  ** a table or index.  The "name" column is the name of the object.
  ** The "tbl_name" is the name of the associated table.  For tables,
  ** the tbl_name column is always the same as name.  For indices, the
  ** tbl_name column contains the name of the table that the index
  ** indexes.  Finally, the "sql" column contains the complete text of
  ** the CREATE TABLE or CREATE INDEX statement that originally created
  ** the table or index.
  **
  ** The following program invokes its callback on the SQL for each
  ** table then goes back and invokes the callback on the
  ** SQL for each index.  The callback will invoke the
  ** parser to build the internal representation of the
Changes to src/parse.y.
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
**
*************************************************************************
** 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.22 2000/06/19 19:09:09 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
  pParse->sErrToken = TOKEN;







|







22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
**
*************************************************************************
** 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.23 2000/06/21 13:59:12 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
  pParse->sErrToken = TOKEN;
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
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 FIELD 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);}







|







46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
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);}
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
cmd ::= UPDATE id(X) SET setlist(Y) where_opt(Z).
    {sqliteUpdate(pParse,&X,Y,Z);}

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

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


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







|

|







246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
cmd ::= UPDATE id(X) SET setlist(Y) where_opt(Z).
    {sqliteUpdate(pParse,&X,Y,Z);}

setlist(A) ::= id(X) EQ expr(Y) COMMA setlist(Z).
    {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($$);}
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
item(A) ::= MINUS FLOAT(X).  {
  A = sqliteExpr(TK_UMINUS, 0, 0, 0);
  A->pLeft = sqliteExpr(TK_FLOAT, 0, 0, &X);
}
item(A) ::= STRING(X).       {A = sqliteExpr(TK_STRING, 0, 0, &X);}
item(A) ::= NULL.            {A = sqliteExpr(TK_NULL, 0, 0, 0);}

%type fieldlist_opt {IdList*}
%destructor fieldlist_opt {sqliteIdListDelete($$);}
%type fieldlist {IdList*}
%destructor fieldlist {sqliteIdListDelete($$);}

fieldlist_opt(A) ::= .                    {A = 0;}
fieldlist_opt(A) ::= LP fieldlist(X) RP.  {A = X;}
fieldlist(A) ::= fieldlist(X) COMMA id(Y). {A = sqliteIdListAppend(X,&Y);}
fieldlist(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.







|
|
|
|

|
|
|
|







274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
item(A) ::= MINUS FLOAT(X).  {
  A = sqliteExpr(TK_UMINUS, 0, 0, 0);
  A->pLeft = sqliteExpr(TK_FLOAT, 0, 0, &X);
}
item(A) ::= STRING(X).       {A = sqliteExpr(TK_STRING, 0, 0, &X);}
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.
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.24 2000/06/19 19:09:09 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.25 2000/06/21 13:59:12 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
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
}

/*
** This routine generates the code for the inside of the inner loop
** of a SELECT.
**
** The pEList is used to determine the values for each column in the
** result row.  Except  if pEList==NULL, then we just read nField
** elements from the srcTab table.
*/
static int selectInnerLoop(
  Parse *pParse,          /* The parser context */
  ExprList *pEList,       /* List of values being extracted */
  int srcTab,             /* Pull data from this table */
  int nField,             /* Number of fields in the source table */
  ExprList *pOrderBy,     /* If not NULL, sort results using this key */
  int distinct,           /* If >=0, make sure results are distinct */
  int eDest,              /* How to dispose of the results */
  int iParm,              /* An argument to the disposal method */
  int iContinue,          /* Jump here to continue with next row */
  int iBreak              /* Jump here to break out of the inner loop */
){
  Vdbe *v = pParse->pVdbe;
  int i;

  /* Pull the requested fields.
  */
  if( pEList ){
    for(i=0; i<pEList->nExpr; i++){
      sqliteExprCode(pParse, pEList->a[i].pExpr);
    }
    nField = pEList->nExpr;
  }else{
    for(i=0; i<nField; i++){
      sqliteVdbeAddOp(v, OP_Field, srcTab, i, 0, 0);
    }
  }

  /* If the current result is not distinct, skip the rest
  ** of the processing for the current row.
  */







|






|










|





|

|







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
}

/*
** This routine generates the code for the inside of the inner loop
** of a SELECT.
**
** The pEList is used to determine the values for each column in the
** result row.  Except  if pEList==NULL, then we just read nColumn
** elements from the srcTab table.
*/
static int selectInnerLoop(
  Parse *pParse,          /* The parser context */
  ExprList *pEList,       /* List of values being extracted */
  int srcTab,             /* Pull data from this table */
  int nColumn,            /* Number of columns in the source table */
  ExprList *pOrderBy,     /* If not NULL, sort results using this key */
  int distinct,           /* If >=0, make sure results are distinct */
  int eDest,              /* How to dispose of the results */
  int iParm,              /* An argument to the disposal method */
  int iContinue,          /* Jump here to continue with next row */
  int iBreak              /* Jump here to break out of the inner loop */
){
  Vdbe *v = pParse->pVdbe;
  int i;

  /* Pull the requested columns.
  */
  if( pEList ){
    for(i=0; i<pEList->nExpr; i++){
      sqliteExprCode(pParse, pEList->a[i].pExpr);
    }
    nColumn = pEList->nExpr;
  }else{
    for(i=0; i<nColumn; i++){
      sqliteVdbeAddOp(v, OP_Field, srcTab, i, 0, 0);
    }
  }

  /* If the current result is not distinct, skip the rest
  ** of the processing for the current row.
  */
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
  }

  /* If there is an ORDER BY clause, then store the results
  ** in a sorter.
  */
  if( pOrderBy ){
    char *zSortOrder;
    sqliteVdbeAddOp(v, OP_SortMakeRec, nField, 0, 0, 0);
    zSortOrder = sqliteMalloc( pOrderBy->nExpr + 1 );
    if( zSortOrder==0 ) return 1;
    for(i=0; i<pOrderBy->nExpr; i++){
      zSortOrder[i] = pOrderBy->a[i].sortOrder ? '-' : '+';
      sqliteExprCode(pParse, pOrderBy->a[i].pExpr);
    }
    zSortOrder[pOrderBy->nExpr] = 0;
    sqliteVdbeAddOp(v, OP_SortMakeKey, pOrderBy->nExpr, 0, zSortOrder, 0);
    sqliteFree(zSortOrder);
    sqliteVdbeAddOp(v, OP_SortPut, 0, 0, 0, 0);
  }else 

  /* In this mode, write each query result to the key of the temporary
  ** table iParm.
  */
  if( eDest==SRT_Union ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nField, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_String, iParm, 0, "", 0);
    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
  }else 

  /* Store the result as data using a unique key.
  */
  if( eDest==SRT_Table ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nField, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_New, iParm, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Pull, 1, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
  }else 

  /* Construct a record from the query result, but instead of
  ** saving that record, use it as a key to delete elements from
  ** the temporary table iParm.
  */
  if( eDest==SRT_Except ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nField, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Delete, iParm, 0, 0, 0);
  }else 

  /* If we are creating a set for an "expr IN (SELECT ...)" construct,
  ** then there should be a single item on the stack.  Write this
  ** item into the set table with bogus data.
  */
  if( eDest==SRT_Set ){
    assert( nField==1 );
    sqliteVdbeAddOp(v, OP_String, 0, 0, "", 0);
    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
  }else 


  /* If this is a scalar select that is part of an expression, then
  ** store the results in the appropriate memory cell and break out
  ** of the scan loop.
  */
  if( eDest==SRT_Mem ){
    assert( nField==1 );
    sqliteVdbeAddOp(v, OP_MemStore, iParm, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Goto, 0, iBreak, 0, 0);
  }else

  /* If none of the above, send the data to the callback function.
  */
  {
    sqliteVdbeAddOp(v, OP_Callback, nField, 0, 0, 0);
  }
  return 0;
}

/*
** If the inner loop was generated using a non-null pOrderBy argument,
** then the results were placed in a sorter.  After the loop is terminated
** we need to run the sorter and output the results.  The following
** routine generates the code needed to do that.
*/
static void generateSortTail(Vdbe *v, int nField){
  int end = sqliteVdbeMakeLabel(v);
  int addr;
  sqliteVdbeAddOp(v, OP_Sort, 0, 0, 0, 0);
  addr = sqliteVdbeAddOp(v, OP_SortNext, 0, end, 0, 0);
  sqliteVdbeAddOp(v, OP_SortCallback, nField, 0, 0, 0);
  sqliteVdbeAddOp(v, OP_Goto, 0, addr, 0, 0);
  sqliteVdbeAddOp(v, OP_SortClose, 0, 0, 0, end);
}

/*
** Generate code that will tell the VDBE how many columns there
** are in the result and the name for each column.  This information







|
















|







|










|








|










|







|










|




|







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
  }

  /* If there is an ORDER BY clause, then store the results
  ** in a sorter.
  */
  if( pOrderBy ){
    char *zSortOrder;
    sqliteVdbeAddOp(v, OP_SortMakeRec, nColumn, 0, 0, 0);
    zSortOrder = sqliteMalloc( pOrderBy->nExpr + 1 );
    if( zSortOrder==0 ) return 1;
    for(i=0; i<pOrderBy->nExpr; i++){
      zSortOrder[i] = pOrderBy->a[i].sortOrder ? '-' : '+';
      sqliteExprCode(pParse, pOrderBy->a[i].pExpr);
    }
    zSortOrder[pOrderBy->nExpr] = 0;
    sqliteVdbeAddOp(v, OP_SortMakeKey, pOrderBy->nExpr, 0, zSortOrder, 0);
    sqliteFree(zSortOrder);
    sqliteVdbeAddOp(v, OP_SortPut, 0, 0, 0, 0);
  }else 

  /* In this mode, write each query result to the key of the temporary
  ** table iParm.
  */
  if( eDest==SRT_Union ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_String, iParm, 0, "", 0);
    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
  }else 

  /* Store the result as data using a unique key.
  */
  if( eDest==SRT_Table ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_New, iParm, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Pull, 1, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
  }else 

  /* Construct a record from the query result, but instead of
  ** saving that record, use it as a key to delete elements from
  ** the temporary table iParm.
  */
  if( eDest==SRT_Except ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Delete, iParm, 0, 0, 0);
  }else 

  /* If we are creating a set for an "expr IN (SELECT ...)" construct,
  ** then there should be a single item on the stack.  Write this
  ** item into the set table with bogus data.
  */
  if( eDest==SRT_Set ){
    assert( nColumn==1 );
    sqliteVdbeAddOp(v, OP_String, 0, 0, "", 0);
    sqliteVdbeAddOp(v, OP_Put, iParm, 0, 0, 0);
  }else 


  /* If this is a scalar select that is part of an expression, then
  ** store the results in the appropriate memory cell and break out
  ** of the scan loop.
  */
  if( eDest==SRT_Mem ){
    assert( nColumn==1 );
    sqliteVdbeAddOp(v, OP_MemStore, iParm, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_Goto, 0, iBreak, 0, 0);
  }else

  /* If none of the above, send the data to the callback function.
  */
  {
    sqliteVdbeAddOp(v, OP_Callback, nColumn, 0, 0, 0);
  }
  return 0;
}

/*
** If the inner loop was generated using a non-null pOrderBy argument,
** then the results were placed in a sorter.  After the loop is terminated
** we need to run the sorter and output the results.  The following
** routine generates the code needed to do that.
*/
static void generateSortTail(Vdbe *v, int nColumn){
  int end = sqliteVdbeMakeLabel(v);
  int addr;
  sqliteVdbeAddOp(v, OP_Sort, 0, 0, 0, 0);
  addr = sqliteVdbeAddOp(v, OP_SortNext, 0, end, 0, 0);
  sqliteVdbeAddOp(v, OP_SortCallback, nColumn, 0, 0, 0);
  sqliteVdbeAddOp(v, OP_Goto, 0, addr, 0, 0);
  sqliteVdbeAddOp(v, OP_SortClose, 0, 0, 0, end);
}

/*
** Generate code that will tell the VDBE how many columns there
** are in the result and the name for each column.  This information
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
    Expr *p;
    if( pEList->a[i].zName ){
      char *zName = pEList->a[i].zName;
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
      continue;
    }
    p = pEList->a[i].pExpr;
    if( p->op!=TK_FIELD || pTabList==0 ){
      char zName[30];
      sprintf(zName, "column%d", i+1);
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
    }else{
      if( pTabList->nId>1 ){
        char *zName = 0;
        Table *pTab = pTabList->a[p->iTable].pTab;
        char *zTab;
 
        zTab = pTabList->a[p->iTable].zAlias;
        if( zTab==0 ) zTab = pTab->zName;
        sqliteSetString(&zName, zTab, ".", pTab->aCol[p->iField].zName, 0);
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
        sqliteFree(zName);
      }else{
        Table *pTab = pTabList->a[0].pTab;
        char *zName = pTab->aCol[p->iField].zName;
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
      }
    }
  }
}

/*







|











|




|







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
    Expr *p;
    if( pEList->a[i].zName ){
      char *zName = pEList->a[i].zName;
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
      continue;
    }
    p = pEList->a[i].pExpr;
    if( p->op!=TK_COLUMN || pTabList==0 ){
      char zName[30];
      sprintf(zName, "column%d", i+1);
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
    }else{
      if( pTabList->nId>1 ){
        char *zName = 0;
        Table *pTab = pTabList->a[p->iTable].pTab;
        char *zTab;
 
        zTab = pTabList->a[p->iTable].zAlias;
        if( zTab==0 ) zTab = pTab->zName;
        sqliteSetString(&zName, zTab, ".", pTab->aCol[p->iColumn].zName, 0);
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
        sqliteFree(zName);
      }else{
        Table *pTab = pTabList->a[0].pTab;
        char *zName = pTab->aCol[p->iColumn].zName;
        sqliteVdbeAddOp(v, OP_ColumnName, i, 0, zName, 0);
      }
    }
  }
}

/*
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
  }
  return z;
}

/*
** For the given SELECT statement, do two things.
**
**    (1)  Fill in the pTab fields of the IdList that defines the set
**         of tables we are scanning.
**
**    (2)  If the columns to be extracted variable (pEList) is NULL
**         (meaning that a "*" was used in the SQL statement) then
**         create a fake pEList containing the names of all columns
**         of all tables.
**
** Return 0 on success.  If there are problems, leave an error message







|
|







277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
  }
  return z;
}

/*
** For the given SELECT statement, do two things.
**
**    (1)  Fill in the pTabList->a[].pTab fields in the IdList that 
**         defines the set of tables that should be scanned.
**
**    (2)  If the columns to be extracted variable (pEList) is NULL
**         (meaning that a "*" was used in the SQL statement) then
**         create a fake pEList containing the names of all columns
**         of all tables.
**
** Return 0 on success.  If there are problems, leave an error message
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
  }
  return 0;
}

/*
** This routine associates entries in an ORDER BY expression list with
** columns in a result.  For each ORDER BY expression, the opcode of
** the top-level node is changed to TK_FIELD and the iField value of
** the top-level node is filled in with column number and the iTable
** value of the top-level node is filled with iTable parameter.
**
** If there are prior SELECT clauses, they are processed first.  A match
** in an earlier SELECT takes precedence over a later SELECT.
**
** Any entry that does not match is flagged as an error.  The number







|







334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
  }
  return 0;
}

/*
** This routine associates entries in an ORDER BY expression list with
** columns in a result.  For each ORDER BY expression, the opcode of
** the top-level node is changed to TK_COLUMN and the iColumn value of
** the top-level node is filled in with column number and the iTable
** value of the top-level node is filled with iTable parameter.
**
** If there are prior SELECT clauses, they are processed first.  A match
** in an earlier SELECT takes precedence over a later SELECT.
**
** Any entry that does not match is flagged as an error.  The number
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
        }
        sqliteFree(zLabel);
      }
      if( match==0 && sqliteExprCompare(pE, pEList->a[j].pExpr) ){
        match = 1;
      }
      if( match ){
        pE->op = TK_FIELD;
        pE->iField = j;
        pE->iTable = iTable;
        pOrderBy->a[i].done = 1;
        break;
      }
    }
    if( !match && mustComplete ){
      char zBuf[30];







|
|







386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
        }
        sqliteFree(zLabel);
      }
      if( match==0 && sqliteExprCompare(pE, pEList->a[j].pExpr) ){
        match = 1;
      }
      if( match ){
        pE->op = TK_COLUMN;
        pE->iColumn = j;
        pE->iTable = iTable;
        pOrderBy->a[i].done = 1;
        break;
      }
    }
    if( !match && mustComplete ){
      char zBuf[30];
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
  int eDest,             /* One of: SRT_Callback Mem Set Union Except */
  int iParm              /* Save result in this memory location, if >=0 */
){
  int i;
  WhereInfo *pWInfo;
  Vdbe *v;
  int isAgg = 0;         /* True for select lists like "count(*)" */
  ExprList *pEList;      /* List of fields to extract.  NULL means "*" */
  IdList *pTabList;      /* List of tables to select from */
  Expr *pWhere;          /* The WHERE clause.  May be NULL */
  ExprList *pOrderBy;    /* The ORDER BY clause.  May be NULL */
  ExprList *pGroupBy;    /* The GROUP BY clause.  May be NULL */
  Expr *pHaving;         /* The HAVING clause.  May be NULL */
  int isDistinct;        /* True if the DISTINCT keyword is present */
  int distinct;          /* Table to use for the distinct set */







|







626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
  int eDest,             /* One of: SRT_Callback Mem Set Union Except */
  int iParm              /* Save result in this memory location, if >=0 */
){
  int i;
  WhereInfo *pWInfo;
  Vdbe *v;
  int isAgg = 0;         /* True for select lists like "count(*)" */
  ExprList *pEList;      /* List of columns to extract.  NULL means "*" */
  IdList *pTabList;      /* List of tables to select from */
  Expr *pWhere;          /* The WHERE clause.  May be NULL */
  ExprList *pOrderBy;    /* The ORDER BY clause.  May be NULL */
  ExprList *pGroupBy;    /* The GROUP BY clause.  May be NULL */
  Expr *pHaving;         /* The HAVING clause.  May be NULL */
  int isDistinct;        /* True if the DISTINCT keyword is present */
  int distinct;          /* Table to use for the distinct set */
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
  ** errors before this routine starts.
  */
  if( pParse->nErr>0 ) return 1;
  sqliteParseInfoReset(pParse);

  /* Look up every table in the table list and create an appropriate
  ** columnlist in pEList if there isn't one already.  (The parser leaves
  ** a NULL in the pEList field if the SQL said "SELECT * FROM ...")
  */
  if( fillInColumnList(pParse, p) ){
    return 1;
  }
  pEList = p->pEList;

  /* Allocate a temporary table to use for the DISTINCT set, if







|







665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
  ** errors before this routine starts.
  */
  if( pParse->nErr>0 ) return 1;
  sqliteParseInfoReset(pParse);

  /* Look up every table in the table list and create an appropriate
  ** columnlist in pEList if there isn't one already.  (The parser leaves
  ** a NULL in the p->pEList if the SQL said "SELECT * FROM ...")
  */
  if( fillInColumnList(pParse, p) ){
    return 1;
  }
  pEList = p->pEList;

  /* Allocate a temporary table to use for the DISTINCT set, if
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
  }
  if( pHaving ) sqliteExprResolveInSelect(pParse, pHaving);

  /* At this point, we should have allocated all the cursors that we
  ** need to handle subquerys and temporary tables.  From here on we
  ** are committed to keeping the same value for pParse->nTab.
  **
  ** Resolve the field names and do a semantics check on all the expressions.
  */
  for(i=0; i<pEList->nExpr; i++){
    if( sqliteExprResolveIds(pParse, pTabList, pEList->a[i].pExpr) ){
      return 1;
    }
    if( sqliteExprCheck(pParse, pEList->a[i].pExpr, 1, &isAgg) ){
      return 1;







|







720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
  }
  if( pHaving ) sqliteExprResolveInSelect(pParse, pHaving);

  /* At this point, we should have allocated all the cursors that we
  ** need to handle subquerys and temporary tables.  From here on we
  ** are committed to keeping the same value for pParse->nTab.
  **
  ** Resolve the column names and do a semantics check on all the expressions.
  */
  for(i=0; i<pEList->nExpr; i++){
    if( sqliteExprResolveIds(pParse, pTabList, pEList->a[i].pExpr) ){
      return 1;
    }
    if( sqliteExprCheck(pParse, pEList->a[i].pExpr, 1, &isAgg) ){
      return 1;
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
        sqliteVdbeAddOp(v, OP_AggIncr, 1, i, 0, 0);
        continue;
      }
      assert( pE->op==TK_AGG_FUNCTION );
      assert( pE->pList!=0 && pE->pList->nExpr==1 );
      sqliteExprCode(pParse, pE->pList->a[0].pExpr);
      sqliteVdbeAddOp(v, OP_AggGet, 0, i, 0, 0);
      switch( pE->iField ){
        case FN_Min:  op = OP_Min;   break;
        case FN_Max:  op = OP_Max;   break;
        case FN_Avg:  op = OP_Add;   break;
        case FN_Sum:  op = OP_Add;   break;
      }
      sqliteVdbeAddOp(v, op, 0, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_AggSet, 0, i, 0, 0);







|







902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
        sqliteVdbeAddOp(v, OP_AggIncr, 1, i, 0, 0);
        continue;
      }
      assert( pE->op==TK_AGG_FUNCTION );
      assert( pE->pList!=0 && pE->pList->nExpr==1 );
      sqliteExprCode(pParse, pE->pList->a[0].pExpr);
      sqliteVdbeAddOp(v, OP_AggGet, 0, i, 0, 0);
      switch( pE->iColumn ){
        case FN_Min:  op = OP_Min;   break;
        case FN_Max:  op = OP_Max;   break;
        case FN_Avg:  op = OP_Add;   break;
        case FN_Sum:  op = OP_Add;   break;
      }
      sqliteVdbeAddOp(v, op, 0, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_AggSet, 0, i, 0, 0);
Changes to src/shell.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 code to implement the "sqlite" command line
** utility for accessing SQLite databases.
**
** $Id: shell.c,v 1.14 2000/06/15 16:49:49 drh Exp $
*/
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include "sqlite.h"
#include <unistd.h>
#include <ctype.h>







|







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 code to implement the "sqlite" command line
** utility for accessing SQLite databases.
**
** $Id: shell.c,v 1.15 2000/06/21 13:59:12 drh Exp $
*/
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include "sqlite.h"
#include <unistd.h>
#include <ctype.h>
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
  char separator[20];    /* Separator character for MODE_List */
  int colWidth[30];      /* Width of each column when in column mode */
};

/*
** These are the allowed modes.
*/
#define MODE_Line     0  /* One field per line.  Blank line between records */
#define MODE_Column   1  /* One record per line in neat columns */
#define MODE_List     2  /* One record per line with a separator */
#define MODE_Html     3  /* Generate an XHTML table */
#define MODE_Insert   4  /* Generate SQL "insert" statements */

/*
** Number of elements in an array







|







132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
  char separator[20];    /* Separator character for MODE_List */
  int colWidth[30];      /* Width of each column when in column mode */
};

/*
** These are the allowed modes.
*/
#define MODE_Line     0  /* One column per line.  Blank line between records */
#define MODE_Column   1  /* One record per line in neat columns */
#define MODE_List     2  /* One record per line with a separator */
#define MODE_Html     3  /* Generate an XHTML table */
#define MODE_Insert   4  /* Generate SQL "insert" statements */

/*
** Number of elements in an array
Changes to src/sqliteInt.h.
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
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.24 2000/06/11 23:50:13 drh Exp $
*/
#include "sqlite.h"
#include "dbbe.h"
#include "vdbe.h"
#include "parse.h"
#include <gdbm.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <assert.h>




/* #define MEMORY_DEBUG 1 */










#ifdef MEMORY_DEBUG
# define sqliteMalloc(X)    sqliteMalloc_(X,__FILE__,__LINE__)
# define sqliteFree(X)      sqliteFree_(X,__FILE__,__LINE__)
# define sqliteRealloc(X,Y) sqliteRealloc_(X,Y,__FILE__,__LINE__)
# define sqliteStrDup(X)    sqliteStrDup_(X,__FILE__,__LINE__)
# define sqliteStrNDup(X,Y) sqliteStrNDup_(X,Y,__FILE__,__LINE__)
  void sqliteStrRealloc(char**);







|











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







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
** Author contact information:
**   drh@hwaci.com
**   http://www.hwaci.com/drh/
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.25 2000/06/21 13:59:12 drh Exp $
*/
#include "sqlite.h"
#include "dbbe.h"
#include "vdbe.h"
#include "parse.h"
#include <gdbm.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <assert.h>

/*
** If memory allocation problems are found, recompile with
**
**      -DMEMORY_DEBUG=1
**
** to enable some sanity checking on malloc() and free().  To
** check for memory leaks, recompile with
**
**      -DMEMORY_DEBUG=2
**
** and a line of text will be written to standard error for
** each malloc() and free().  This output can be analyzed
** by an AWK script to determine if there are any leaks.
*/
#ifdef MEMORY_DEBUG
# define sqliteMalloc(X)    sqliteMalloc_(X,__FILE__,__LINE__)
# define sqliteFree(X)      sqliteFree_(X,__FILE__,__LINE__)
# define sqliteRealloc(X,Y) sqliteRealloc_(X,Y,__FILE__,__LINE__)
# define sqliteStrDup(X)    sqliteStrDup_(X,__FILE__,__LINE__)
# define sqliteStrNDup(X,Y) sqliteStrNDup_(X,Y,__FILE__,__LINE__)
  void sqliteStrRealloc(char**);
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
#ifdef MEMORY_DEBUG
int sqlite_nMalloc;         /* Number of sqliteMalloc() calls */
int sqlite_nFree;           /* Number of sqliteFree() calls */
int sqlite_iMallocFail;     /* Fail sqliteMalloc() after this many calls */
#endif

/*
** The number of entries in the in-memory hash table holding the
** schema.
*/
#define N_HASH        51

/*
** Name of the master database table.  The master database table
** is a special table that holds the names and attributes of all
** user tables and indices.
*/
#define MASTER_NAME   "sqlite_master"

/*
** A convenience macro that returns the number of elements in
** an array.
*/
#define ArraySize(X)    (sizeof(X)/sizeof(X[0]))

/*
** Integer identifiers for functions.
*/
#define FN_Unknown    0
#define FN_Count      1
#define FN_Min        2
#define FN_Max        3
#define FN_Sum        4
#define FN_Avg        5







|
|

















|







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
#ifdef MEMORY_DEBUG
int sqlite_nMalloc;         /* Number of sqliteMalloc() calls */
int sqlite_nFree;           /* Number of sqliteFree() calls */
int sqlite_iMallocFail;     /* Fail sqliteMalloc() after this many calls */
#endif

/*
** The number of entries in the in-memory hash array holding the
** database schema.
*/
#define N_HASH        51

/*
** Name of the master database table.  The master database table
** is a special table that holds the names and attributes of all
** user tables and indices.
*/
#define MASTER_NAME   "sqlite_master"

/*
** A convenience macro that returns the number of elements in
** an array.
*/
#define ArraySize(X)    (sizeof(X)/sizeof(X[0]))

/*
** Integer identifiers for built-in SQL functions.
*/
#define FN_Unknown    0
#define FN_Count      1
#define FN_Min        2
#define FN_Max        3
#define FN_Sum        4
#define FN_Avg        5
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
  Dbbe *pBe;                 /* The backend driver */
  int flags;                 /* Miscellanous flags */
  Table *apTblHash[N_HASH];  /* All tables of the database */
  Index *apIdxHash[N_HASH];  /* All indices of the database */
};

/*
** Possible values for the flags field of sqlite
*/
#define SQLITE_VdbeTrace    0x00000001
#define SQLITE_Initialized  0x00000002

/*
** information about each column of a table is held in an instance
** of this structure.
*/
struct Column {
  char *zName;        /* Name of this column */
  char *zDflt;        /* Default value of this column */
  int notNull;        /* True if there is a NOT NULL constraing */
};

/*
** Each table is represented in memory by
** an instance of the following structure
*/
struct Table {
  char *zName;        /* Name of the table */
  Table *pHash;       /* Next table with same hash on zName */
  int nCol;           /* Number of columns in this table */
  Column *aCol;       /* Information about each column */
  int readOnly;       /* True if this table should not be written by the user */
  Index *pIndex;      /* List of indices on this table. */
};

/*
** Each index is represented in memory by and
** instance of the following structure.















*/
struct Index {
  char *zName;        /* Name of this index */
  Index *pHash;       /* Next index with the same hash on zName */
  int nField;         /* Number of fields in the table indexed by this index */
  int *aiField;       /* Indices of fields used by this index.  1st is 0 */
  Table *pTable;      /* The table being indexed */
  int isUnique;       /* True if keys must all be unique */
  Index *pNext;       /* The next index associated with the same table */
};

/*
** Each token coming out of the lexer is an instance of
** this structure.
*/
struct Token {
  char *z;      /* Text of the token */
  int n;        /* Number of characters in this token */
};

/*
** Each node of an expression in the parse tree is an instance
** of this structure
*/
struct Expr {
  int op;                /* Operation performed by this node */
  Expr *pLeft, *pRight;  /* Left and right subnodes */
  ExprList *pList;       /* A list of expressions used as a function argument */
  Token token;           /* An operand token */
  int iTable, iField;    /* When op==TK_FIELD, then this node means the
                         ** iField-th field of the iTable-th table.  When
                         ** op==TK_FUNCTION, iField holds the function id */
  int iAgg;              /* When op==TK_FIELD and pParse->useAgg==TRUE, pull
                         ** value from these element of the aggregator */
  Select *pSelect;       /* When the expression is a sub-select */
};

/*
** A list of expressions.  Each expression may optionally have a
** name.  An expr/name combination can be used in several ways, such
** as the list of "expr AS ID" fields following a "SELECT" or in the







|





|



|
|
|



|
|


|
|
|
|
|
|



|

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


|
|
|
|
|
|
|




















|
|
|
|
|







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
  Dbbe *pBe;                 /* The backend driver */
  int flags;                 /* Miscellanous flags */
  Table *apTblHash[N_HASH];  /* All tables of the database */
  Index *apIdxHash[N_HASH];  /* All indices of the database */
};

/*
** Possible values for the sqlite.flags.
*/
#define SQLITE_VdbeTrace    0x00000001
#define SQLITE_Initialized  0x00000002

/*
** information about each column of an SQL table is held in an instance
** of this structure.
*/
struct Column {
  char *zName;     /* Name of this column */
  char *zDflt;     /* Default value of this column */
  int notNull;     /* True if there is a NOT NULL constraint */
};

/*
** Each SQL table is represented in memory by
** an instance of the following structure.
*/
struct Table {
  char *zName;     /* Name of the table */
  Table *pHash;    /* Next table with same hash on zName */
  int nCol;        /* Number of columns in this table */
  Column *aCol;    /* Information about each column */
  int readOnly;    /* True if this table should not be written by the user */
  Index *pIndex;   /* List of SQL indexes on this table. */
};

/*
** Each SQL index is represented in memory by and
** instance of the following structure.
**
** The columns of the table that are to be indexed are described
** by the aiColumn[] field of this structure.  For example, suppose
** we have the following table and index:
**
**     CREATE TABLE Ex1(c1 int, c2 int, c3 text);
**     CREATE INDEX Ex2 ON Ex1(c3,c1);
**
** In the Table structure describing Ex1, nCol==3 because there are
** three columns in the table.  In the Index structure describing
** Ex2, nColumn==2 since 2 of the 3 columns of Ex1 are indexed.
** The value of aiColumn is {2, 0}.  aiColumn[0]==2 because the 
** first column to be indexed (c3) has an index of 2 in Ex1.aCol[].
** The second column to be indexed (c1) has an index of 0 in
** Ex1.aCol[], hence Ex2.aiColumn[1]==0.
*/
struct Index {
  char *zName;     /* Name of this index */
  Index *pHash;    /* Next index with the same hash on zName */
  int nColumn;     /* Number of columns in the table used by this index */
  int *aiColumn;   /* Which columns are used by this index.  1st is 0 */
  Table *pTable;   /* The SQL table being indexed */
  int isUnique;    /* True if keys must all be unique */
  Index *pNext;    /* The next index associated with the same table */
};

/*
** Each token coming out of the lexer is an instance of
** this structure.
*/
struct Token {
  char *z;      /* Text of the token */
  int n;        /* Number of characters in this token */
};

/*
** Each node of an expression in the parse tree is an instance
** of this structure
*/
struct Expr {
  int op;                /* Operation performed by this node */
  Expr *pLeft, *pRight;  /* Left and right subnodes */
  ExprList *pList;       /* A list of expressions used as a function argument */
  Token token;           /* An operand token */
  int iTable, iColumn;   /* When op==TK_COLUMN, then this expr node means the
                         ** iColumn-th field of the iTable-th table.  When
                         ** op==TK_FUNCTION, iColumn holds the function id */
  int iAgg;              /* When op==TK_COLUMN and pParse->useAgg==TRUE, pull
                         ** result from the iAgg-th element of the aggregator */
  Select *pSelect;       /* When the expression is a sub-select */
};

/*
** A list of expressions.  Each expression may optionally have a
** name.  An expr/name combination can be used in several ways, such
** as the list of "expr AS ID" fields following a "SELECT" or in the
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
** A list of identifiers.
*/
struct IdList {
  int nId;         /* Number of identifiers on the list */
  struct {
    char *zName;      /* Text of the identifier. */
    char *zAlias;     /* The "B" part of a "A AS B" phrase.  zName is the "A" */
    Table *pTab;      /* Table corresponding to zName */
    int idx;          /* Index of a field named zName in a table */
  } *a;            /* One entry for each identifier on the list */
};

/*
** The WHERE clause processing routine has two halves.  The
** first part does the start of the WHERE loop and the second
** half does the tail of the WHERE loop.  An instance of







|
|







233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
** A list of identifiers.
*/
struct IdList {
  int nId;         /* Number of identifiers on the list */
  struct {
    char *zName;      /* Text of the identifier. */
    char *zAlias;     /* The "B" part of a "A AS B" phrase.  zName is the "A" */
    Table *pTab;      /* An SQL table corresponding to zName */
    int idx;          /* Index in some Table.aCol[] of a column named zName */
  } *a;            /* One entry for each identifier on the list */
};

/*
** The WHERE clause processing routine has two halves.  The
** first part does the start of the WHERE loop and the second
** half does the tail of the WHERE loop.  An instance of
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
  ExprList *pEList;      /* The fields of the result */
  IdList *pSrc;          /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
  ExprList *pGroupBy;    /* The GROUP BY clause */
  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  int op;                /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  Select *pPrior;        /* Prior select to which this one joins */
};

/*
** The results of a select can be distributed in several ways.
*/
#define SRT_Callback     1  /* Invoke a callback with each row of result */
#define SRT_Mem          2  /* Store result in a memory cell */







|







267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
  ExprList *pEList;      /* The fields of the result */
  IdList *pSrc;          /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
  ExprList *pGroupBy;    /* The GROUP BY clause */
  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  int op;                /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  Select *pPrior;        /* Prior select in a compound select statement */
};

/*
** The results of a select can be distributed in several ways.
*/
#define SRT_Callback     1  /* Invoke a callback with each row of result */
#define SRT_Mem          2  /* Store result in a memory cell */
Changes to src/update.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 UPDATE statements.
**
** $Id: update.c,v 1.7 2000/06/19 19:09:09 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(







|







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 UPDATE statements.
**
** $Id: update.c,v 1.8 2000/06/21 13:59:12 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
  WhereInfo *pWInfo;     /* Information about the WHERE clause */
  Vdbe *v;               /* The virtual database engine */
  Index *pIdx;           /* For looping over indices */
  int nIdx;              /* Number of indices that need updating */
  int base;              /* Index of first available table cursor */
  Index **apIdx = 0;     /* An array of indices that need updating too */
  int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
                         ** an expression for the i-th field of the table.
                         ** aXRef[i]==-1 if the i-th field is not changed. */

  /* Locate the table which we want to update.  This table has to be
  ** put in an IdList structure because some of the subroutines we
  ** will be calling are designed to work with multiple tables and expect
  ** an IdList* parameter instead of just a Table* parameger.
  */
  pTabList = sqliteIdListAppend(0, pTableName);







|
|







44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
  WhereInfo *pWInfo;     /* Information about the WHERE clause */
  Vdbe *v;               /* The virtual database engine */
  Index *pIdx;           /* For looping over indices */
  int nIdx;              /* Number of indices that need updating */
  int base;              /* Index of first available table cursor */
  Index **apIdx = 0;     /* An array of indices that need updating too */
  int *aXRef = 0;        /* aXRef[i] is the index in pChanges->a[] of the
                         ** an expression for the i-th column of the table.
                         ** aXRef[i]==-1 if the i-th column is not changed. */

  /* Locate the table which we want to update.  This table has to be
  ** put in an IdList structure because some of the subroutines we
  ** will be calling are designed to work with multiple tables and expect
  ** an IdList* parameter instead of just a Table* parameger.
  */
  pTabList = sqliteIdListAppend(0, pTableName);
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
    }
  }
  pTab = pTabList->a[0].pTab;
  aXRef = sqliteMalloc( sizeof(int) * pTab->nCol );
  if( aXRef==0 ) goto update_cleanup;
  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;

  /* Resolve the field names in all the expressions in both the
  ** WHERE clause and in the new values.  Also find the field index
  ** for each field to be updated in the pChanges array.
  */
  if( pWhere ){
    sqliteExprResolveInSelect(pParse, pWhere);
  }
  for(i=0; i<pChanges->nExpr; i++){
    sqliteExprResolveInSelect(pParse, pChanges->a[i].pExpr);
  }







|
|
|







73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
    }
  }
  pTab = pTabList->a[0].pTab;
  aXRef = sqliteMalloc( sizeof(int) * pTab->nCol );
  if( aXRef==0 ) goto update_cleanup;
  for(i=0; i<pTab->nCol; i++) aXRef[i] = -1;

  /* Resolve the column names in all the expressions in both the
  ** WHERE clause and in the new values.  Also find the column index
  ** for each column to be updated in the pChanges array.
  */
  if( pWhere ){
    sqliteExprResolveInSelect(pParse, pWhere);
  }
  for(i=0; i<pChanges->nExpr; i++){
    sqliteExprResolveInSelect(pParse, pChanges->a[i].pExpr);
  }
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
    for(j=0; j<pTab->nCol; j++){
      if( sqliteStrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){
        aXRef[j] = i;
        break;
      }
    }
    if( j>=pTab->nCol ){
      sqliteSetString(&pParse->zErrMsg, "no such field: ", 
         pChanges->a[i].zName, 0);
      pParse->nErr++;
      goto update_cleanup;
    }
  }

  /* Allocate memory for the array apIdx[] and fill it pointers to every
  ** index that needs to be updated.  Indices only need updating if their
  ** key includes one of the fields named in pChanges.
  */
  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    for(i=0; i<pIdx->nField; i++){
      if( aXRef[pIdx->aiField[i]]>=0 ) break;
    }
    if( i<pIdx->nField ) nIdx++;
  }
  apIdx = sqliteMalloc( sizeof(Index*) * nIdx );
  if( apIdx==0 ) goto update_cleanup;
  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    for(i=0; i<pIdx->nField; i++){
      if( aXRef[pIdx->aiField[i]]>=0 ) break;
    }
    if( i<pIdx->nField ) apIdx[nIdx++] = pIdx;
  }

  /* Begin generating code.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto update_cleanup;








|








|


|
|

|




|
|

|







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
    for(j=0; j<pTab->nCol; j++){
      if( sqliteStrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){
        aXRef[j] = i;
        break;
      }
    }
    if( j>=pTab->nCol ){
      sqliteSetString(&pParse->zErrMsg, "no such column: ", 
         pChanges->a[i].zName, 0);
      pParse->nErr++;
      goto update_cleanup;
    }
  }

  /* Allocate memory for the array apIdx[] and fill it pointers to every
  ** index that needs to be updated.  Indices only need updating if their
  ** key includes one of the columns named in pChanges.
  */
  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    for(i=0; i<pIdx->nColumn; i++){
      if( aXRef[pIdx->aiColumn[i]]>=0 ) break;
    }
    if( i<pIdx->nColumn ) nIdx++;
  }
  apIdx = sqliteMalloc( sizeof(Index*) * nIdx );
  if( apIdx==0 ) goto update_cleanup;
  for(nIdx=0, pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    for(i=0; i<pIdx->nColumn; i++){
      if( aXRef[pIdx->aiColumn[i]]>=0 ) break;
    }
    if( i<pIdx->nColumn ) apIdx[nIdx++] = pIdx;
  }

  /* Begin generating code.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) goto update_cleanup;

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
  base = pParse->nTab;
  sqliteVdbeAddOp(v, OP_Open, base, 1, pTab->zName, 0);
  for(i=0; i<nIdx; i++){
    sqliteVdbeAddOp(v, OP_Open, base+i+1, 1, apIdx[i]->zName, 0);
  }

  /* Loop over every record that needs updating.  We have to load
  ** the old data for each record to be updated because some fields
  ** might not change and we will need to copy the old value.
  ** Also, the old data is needed to delete the old index entires.
  */
  end = sqliteVdbeMakeLabel(v);
  addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end, 0, 0);
  sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
  sqliteVdbeAddOp(v, OP_Fetch, base, 0, 0, 0);

  /* Delete the old indices for the current record.
  */
  for(i=0; i<nIdx; i++){
    sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
    pIdx = apIdx[i];
    for(j=0; j<pIdx->nField; j++){
      sqliteVdbeAddOp(v, OP_Field, base, pIdx->aiField[j], 0, 0);
    }
    sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nField, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_DeleteIdx, base+i+1, 0, 0, 0);
  }

  /* Compute a completely new data for this record.  
  */
  for(i=0; i<pTab->nCol; i++){
    j = aXRef[i];
    if( j<0 ){
      sqliteVdbeAddOp(v, OP_Field, base, i, 0, 0);
    }else{
      sqliteExprCode(pParse, pChanges->a[j].pExpr);
    }
  }

  /* Insert new index entries that correspond to the new data
  */
  for(i=0; i<nIdx; i++){
    sqliteVdbeAddOp(v, OP_Dup, pTab->nCol, 0, 0, 0); /* The KEY */
    pIdx = apIdx[i];
    for(j=0; j<pIdx->nField; j++){
      sqliteVdbeAddOp(v, OP_Dup, j+pTab->nCol-pIdx->aiField[j], 0, 0, 0);
    }
    sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nField, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_PutIdx, base+i+1, 0, 0, 0);
  }

  /* Write the new data back into the database.
  */
  sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0, 0, 0);
  sqliteVdbeAddOp(v, OP_Put, base, 0, 0, 0);







|













|
|

|



















|
|

|







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
  base = pParse->nTab;
  sqliteVdbeAddOp(v, OP_Open, base, 1, pTab->zName, 0);
  for(i=0; i<nIdx; i++){
    sqliteVdbeAddOp(v, OP_Open, base+i+1, 1, apIdx[i]->zName, 0);
  }

  /* Loop over every record that needs updating.  We have to load
  ** the old data for each record to be updated because some columns
  ** might not change and we will need to copy the old value.
  ** Also, the old data is needed to delete the old index entires.
  */
  end = sqliteVdbeMakeLabel(v);
  addr = sqliteVdbeAddOp(v, OP_ListRead, 0, end, 0, 0);
  sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
  sqliteVdbeAddOp(v, OP_Fetch, base, 0, 0, 0);

  /* Delete the old indices for the current record.
  */
  for(i=0; i<nIdx; i++){
    sqliteVdbeAddOp(v, OP_Dup, 0, 0, 0, 0);
    pIdx = apIdx[i];
    for(j=0; j<pIdx->nColumn; j++){
      sqliteVdbeAddOp(v, OP_Field, base, pIdx->aiColumn[j], 0, 0);
    }
    sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nColumn, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_DeleteIdx, base+i+1, 0, 0, 0);
  }

  /* Compute a completely new data for this record.  
  */
  for(i=0; i<pTab->nCol; i++){
    j = aXRef[i];
    if( j<0 ){
      sqliteVdbeAddOp(v, OP_Field, base, i, 0, 0);
    }else{
      sqliteExprCode(pParse, pChanges->a[j].pExpr);
    }
  }

  /* Insert new index entries that correspond to the new data
  */
  for(i=0; i<nIdx; i++){
    sqliteVdbeAddOp(v, OP_Dup, pTab->nCol, 0, 0, 0); /* The KEY */
    pIdx = apIdx[i];
    for(j=0; j<pIdx->nColumn; j++){
      sqliteVdbeAddOp(v, OP_Dup, j+pTab->nCol-pIdx->aiColumn[j], 0, 0, 0);
    }
    sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nColumn, 0, 0, 0);
    sqliteVdbeAddOp(v, OP_PutIdx, base+i+1, 0, 0, 0);
  }

  /* Write the new data back into the database.
  */
  sqliteVdbeAddOp(v, OP_MakeRecord, pTab->nCol, 0, 0, 0);
  sqliteVdbeAddOp(v, OP_Put, base, 0, 0, 0);
Changes to src/util.c.
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.12 2000/06/08 13:36:41 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

#ifdef MEMORY_DEBUG








|







22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.13 2000/06/21 13:59:12 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

#ifdef MEMORY_DEBUG

356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
    h = h<<3 ^ h ^ UpperToLower[c];
  }
  if( h<0 ) h = -h;
  return h;
}

/*
** Some system shave stricmp().  Others have strcasecmp().  Because
** there is no consistency, we will define our own.
*/
int sqliteStrICmp(const char *zLeft, const char *zRight){
  register unsigned char *a, *b;
  a = (unsigned char *)zLeft;
  b = (unsigned char *)zRight;
  while( *a!=0 && UpperToLower[*a]==UpperToLower[*b]){ a++; b++; }







|







356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
    h = h<<3 ^ h ^ UpperToLower[c];
  }
  if( h<0 ) h = -h;
  return h;
}

/*
** Some systems have stricmp().  Others have strcasecmp().  Because
** there is no consistency, we will define our own.
*/
int sqliteStrICmp(const char *zLeft, const char *zRight){
  register unsigned char *a, *b;
  a = (unsigned char *)zLeft;
  b = (unsigned char *)zRight;
  while( *a!=0 && UpperToLower[*a]==UpperToLower[*b]){ a++; b++; }
Changes to src/vdbe.c.
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
** 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.33 2000/06/11 23:50:13 drh Exp $
*/
#include "sqliteInt.h"
#include <unistd.h>

/*
** SQL is translated into a sequence of instructions to be
** executed by a virtual machine.  Each instruction is an instance
** of the following structure.
*/
typedef struct VdbeOp Op;

/*






** Every table that the virtual machine has open is represented by an
** instance of the following structure.
*/
struct VdbeTable {
  DbbeTable *pTable;    /* The table structure of the backend */
  int index;            /* The next index to extract */
  int keyAsData;        /* The OP_Field command works on key instead of data */
};
typedef struct VdbeTable VdbeTable;

/*
** A sorter builds a list of elements to be sorted.  Each element of
** the list is an instance of the following structure.
*/
typedef struct Sorter Sorter;
struct Sorter {







|












>
>
>
>
>
>
|


|
|



|







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
** 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.34 2000/06/21 13:59:13 drh Exp $
*/
#include "sqliteInt.h"
#include <unistd.h>

/*
** SQL is translated into a sequence of instructions to be
** executed by a virtual machine.  Each instruction is an instance
** of the following structure.
*/
typedef struct VdbeOp Op;

/*
** A cursor is a pointer into a database file.  The database file
** can represent either an SQL table or an SQL index.  Each file is
** a bag of key/data pairs.  The cursor can loop over all key/data
** pairs (in an arbitrary order) or it can retrieve a particular
** key/data pair given a copy of the key.
** 
** Every cursor that the virtual machine has open is represented by an
** instance of the following structure.
*/
struct Cursor {
  DbbeCursor *pCursor;  /* The cursor structure of the backend */
  int index;            /* The next index to extract */
  int keyAsData;        /* The OP_Field command works on key instead of data */
};
typedef struct Cursor Cursor;

/*
** A sorter builds a list of elements to be sorted.  Each element of
** the list is an instance of the following structure.
*/
typedef struct Sorter Sorter;
struct Sorter {
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
#define STK_Null      0x0001   /* Value is NULL */
#define STK_Str       0x0002   /* Value is a string */
#define STK_Int       0x0004   /* Value is an integer */
#define STK_Real      0x0008   /* Value is a real number */
#define STK_Dyn       0x0010   /* Need to call sqliteFree() on zStack[*] */

/*
** An Agg structure describes and Aggregator.  Each Agg consists of
** zero or more Aggregator elements (AggElem).  Each AggElem contains
** a key and one or more values.  The values are used in processing
** aggregate functions in a SELECT.  The key is used to implement
** the GROUP BY clause of a select.
*/
typedef struct Agg Agg;
typedef struct AggElem AggElem;
struct Agg {
  int nMem;              /* Number of values stored in each AggElem */
  AggElem *pCurrent;     /* The AggElem currently in focus */
  int nElem;             /* The number of AggElems */
  int nHash;             /* Number of slots in apHash[] */
  AggElem **apHash;      /* A hash table for looking up AggElems by zKey */
  AggElem *pFirst;       /* A list of all AggElems */
};
struct AggElem {
  char *zKey;            /* The key to this AggElem */
  AggElem *pHash;        /* Next AggElem with the same hash on zKey */
  AggElem *pNext;        /* Next AggElem in a list of them all */
  Mem aMem[1];           /* The values for this AggElem */
};

/*
** A Set structure is used for quick testing to see if a value
** is part of a small set.  Sets are used to implement code like
** this:
**            x.y IN ('hi','hoo','hum')
*/
typedef struct Set Set;
typedef struct SetElem SetElem;
struct Set {
  SetElem *pAll;         /* All elements of this set */
  SetElem *apHash[41];   /* A hash table for all elements in this set */
};
struct SetElem {
  SetElem *pHash;        /* Next element with the same hash on zKey */
  SetElem *pNext;        /* Next element in a list of them all */
  char zKey[1];          /* Value of this key */
};








|












|



















|







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
#define STK_Null      0x0001   /* Value is NULL */
#define STK_Str       0x0002   /* Value is a string */
#define STK_Int       0x0004   /* Value is an integer */
#define STK_Real      0x0008   /* Value is a real number */
#define STK_Dyn       0x0010   /* Need to call sqliteFree() on zStack[*] */

/*
** An Agg structure describes an Aggregator.  Each Agg consists of
** zero or more Aggregator elements (AggElem).  Each AggElem contains
** a key and one or more values.  The values are used in processing
** aggregate functions in a SELECT.  The key is used to implement
** the GROUP BY clause of a select.
*/
typedef struct Agg Agg;
typedef struct AggElem AggElem;
struct Agg {
  int nMem;              /* Number of values stored in each AggElem */
  AggElem *pCurrent;     /* The AggElem currently in focus */
  int nElem;             /* The number of AggElems */
  int nHash;             /* Number of slots in apHash[] */
  AggElem **apHash;      /* A hash array for looking up AggElems by zKey */
  AggElem *pFirst;       /* A list of all AggElems */
};
struct AggElem {
  char *zKey;            /* The key to this AggElem */
  AggElem *pHash;        /* Next AggElem with the same hash on zKey */
  AggElem *pNext;        /* Next AggElem in a list of them all */
  Mem aMem[1];           /* The values for this AggElem */
};

/*
** A Set structure is used for quick testing to see if a value
** is part of a small set.  Sets are used to implement code like
** this:
**            x.y IN ('hi','hoo','hum')
*/
typedef struct Set Set;
typedef struct SetElem SetElem;
struct Set {
  SetElem *pAll;         /* All elements of this set */
  SetElem *apHash[41];   /* A hash array for all elements in this set */
};
struct SetElem {
  SetElem *pHash;        /* Next element with the same hash on zKey */
  SetElem *pNext;        /* Next element in a list of them all */
  char zKey[1];          /* Value of this key */
};

171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
  int nLabelAlloc;    /* Number of slots allocated in aLabel[] */
  int *aLabel;        /* Space to hold the labels */
  int tos;            /* Index of top of stack */
  int nStackAlloc;    /* Size of the stack */
  Stack *aStack;      /* The operand stack, except string values */
  char **zStack;      /* Text or binary values of the stack */
  char **azColName;   /* Becomes the 4th parameter to callbacks */
  int nTable;         /* Number of slots in aTab[] */
  VdbeTable *aTab;    /* On element of this array for each open table */
  int nList;          /* Number of slots in apList[] */
  FILE **apList;      /* An open file for each list */
  int nSort;          /* Number of slots in apSort[] */
  Sorter **apSort;    /* An open sorter list */
  FILE *pFile;        /* At most one open file handler */
  int nField;         /* Number of file fields */
  char **azField;     /* Data for each file field */







|
|







177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
  int nLabelAlloc;    /* Number of slots allocated in aLabel[] */
  int *aLabel;        /* Space to hold the labels */
  int tos;            /* Index of top of stack */
  int nStackAlloc;    /* Size of the stack */
  Stack *aStack;      /* The operand stack, except string values */
  char **zStack;      /* Text or binary values of the stack */
  char **azColName;   /* Becomes the 4th parameter to callbacks */
  int nCursor;        /* Number of slots in aCsr[] */
  Cursor *aCsr;       /* On element of this array for each open cursor */
  int nList;          /* Number of slots in apList[] */
  FILE **apList;      /* An open file for each list */
  int nSort;          /* Number of slots in apSort[] */
  Sorter **apSort;    /* An open sorter list */
  FILE *pFile;        /* At most one open file handler */
  int nField;         /* Number of file fields */
  char **azField;     /* Data for each file field */
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
    sqliteFree(pElem);
  }
  sqliteFree(p->apHash);
  memset(p, 0, sizeof(*p));
}

/*
** Add the given AggElem to the hash table
*/
static void AggEnhash(Agg *p, AggElem *pElem){
  int h = sqliteHashNoCase(pElem->zKey, 0) % p->nHash;
  pElem->pHash = p->apHash[h];
  p->apHash[h] = pElem;
}

/*
** Change the size of the hash table to the amount given.
*/
static void AggRehash(Agg *p, int nHash){
  int size;
  AggElem *pElem;
  if( p->nHash==nHash ) return;
  size = nHash * sizeof(AggElem*);
  p->apHash = sqliteRealloc(p->apHash, size );







|








|







396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
    sqliteFree(pElem);
  }
  sqliteFree(p->apHash);
  memset(p, 0, sizeof(*p));
}

/*
** Add the given AggElem to the hash array
*/
static void AggEnhash(Agg *p, AggElem *pElem){
  int h = sqliteHashNoCase(pElem->zKey, 0) % p->nHash;
  pElem->pHash = p->apHash[h];
  p->apHash[h] = pElem;
}

/*
** Change the size of the hash array to the amount given.
*/
static void AggRehash(Agg *p, int nHash){
  int size;
  AggElem *pElem;
  if( p->nHash==nHash ) return;
  size = nHash * sizeof(AggElem*);
  p->apHash = sqliteRealloc(p->apHash, size );
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
  }
  return 0;
}

/*
** Clean up the VM after execution.
**
** This routine will automatically close any tables, list, and/or
** sorters that were left open.
*/
static void Cleanup(Vdbe *p){
  int i;
  PopStack(p, p->tos+1);
  sqliteFree(p->azColName);
  p->azColName = 0;
  for(i=0; i<p->nTable; i++){
    if( p->aTab[i].pTable ){
      sqliteDbbeCloseTable(p->aTab[i].pTable);
      p->aTab[i].pTable = 0;
    }
  }
  sqliteFree(p->aTab);
  p->aTab = 0;
  p->nTable = 0;
  for(i=0; i<p->nMem; i++){
    if( p->aMem[i].s.flags & STK_Dyn ){
      sqliteFree(p->aMem[i].z);
    }
  }
  sqliteFree(p->aMem);
  p->aMem = 0;







|







|
|
|
|


|
|
|







636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
  }
  return 0;
}

/*
** Clean up the VM after execution.
**
** This routine will automatically close any cursors, list, and/or
** sorters that were left open.
*/
static void Cleanup(Vdbe *p){
  int i;
  PopStack(p, p->tos+1);
  sqliteFree(p->azColName);
  p->azColName = 0;
  for(i=0; i<p->nCursor; i++){
    if( p->aCsr[i].pCursor ){
      sqliteDbbeCloseCursor(p->aCsr[i].pCursor);
      p->aCsr[i].pCursor = 0;
    }
  }
  sqliteFree(p->aCsr);
  p->aCsr = 0;
  p->nCursor = 0;
  for(i=0; i<p->nMem; i++){
    if( p->aMem[i].s.flags & STK_Dyn ){
      sqliteFree(p->aMem[i].z);
    }
  }
  sqliteFree(p->aMem);
  p->aMem = 0;
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
int sqliteVdbeList(
  Vdbe *p,                   /* The VDBE */
  sqlite_callback xCallback, /* The callback */
  void *pArg,                /* 1st argument to callback */
  char **pzErrMsg            /* Error msg written here */
){
  int i, rc;
  char *azField[6];
  char zAddr[20];
  char zP1[20];
  char zP2[20];
  static char *azColumnNames[] = {
     "addr", "opcode", "p1", "p2", "p3", 0
  };

  if( xCallback==0 ) return 0;
  azField[0] = zAddr;
  azField[2] = zP1;
  azField[3] = zP2;
  azField[5] = 0;
  rc = SQLITE_OK;
  /* if( pzErrMsg ){ *pzErrMsg = 0; } */
  for(i=0; rc==SQLITE_OK && i<p->nOp; i++){
    sprintf(zAddr,"%d",i);
    sprintf(zP1,"%d", p->aOp[i].p1);
    sprintf(zP2,"%d", p->aOp[i].p2);
    azField[4] = p->aOp[i].p3;
    azField[1] = zOpName[p->aOp[i].opcode];
    if( xCallback(pArg, 5, azField, azColumnNames) ){
      rc = SQLITE_ABORT;
    }
  }
  return rc;
}

/*







|








|
|
|
|






|
|
|







788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
int sqliteVdbeList(
  Vdbe *p,                   /* The VDBE */
  sqlite_callback xCallback, /* The callback */
  void *pArg,                /* 1st argument to callback */
  char **pzErrMsg            /* Error msg written here */
){
  int i, rc;
  char *azValue[6];
  char zAddr[20];
  char zP1[20];
  char zP2[20];
  static char *azColumnNames[] = {
     "addr", "opcode", "p1", "p2", "p3", 0
  };

  if( xCallback==0 ) return 0;
  azValue[0] = zAddr;
  azValue[2] = zP1;
  azValue[3] = zP2;
  azValue[5] = 0;
  rc = SQLITE_OK;
  /* if( pzErrMsg ){ *pzErrMsg = 0; } */
  for(i=0; rc==SQLITE_OK && i<p->nOp; i++){
    sprintf(zAddr,"%d",i);
    sprintf(zP1,"%d", p->aOp[i].p1);
    sprintf(zP2,"%d", p->aOp[i].p2);
    azValue[4] = p->aOp[i].p3;
    azValue[1] = zOpName[p->aOp[i].opcode];
    if( xCallback(pArg, 5, azValue, azColumnNames) ){
      rc = SQLITE_ABORT;
    }
  }
  return rc;
}

/*
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
          p->aStack[tos].flags = STK_Real;
        }
        break;
      }

      /* Opcode: Not * * *
      **
      ** Treat the top of the stack as a boolean value.  Replace it
      ** with its complement.
      */
      case OP_Not: {
        int tos = p->tos;
        if( p->tos<0 ) goto not_enough_stack;
        Integerify(p, tos);
        Release(p, tos);







|







1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
          p->aStack[tos].flags = STK_Real;
        }
        break;
      }

      /* Opcode: Not * * *
      **
      ** Interpret the top of the stack as a boolean value.  Replace it
      ** with its complement.
      */
      case OP_Not: {
        int tos = p->tos;
        if( p->tos<0 ) goto not_enough_stack;
        Integerify(p, tos);
        Release(p, tos);
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689






1690

1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
        p->aStack[p->tos].flags = STK_Str|STK_Dyn;
        p->zStack[p->tos] = zNewKey;
        break;
      }

      /* Opcode: Open P1 P2 P3
      **
      ** Open a new cursor for the database table named P3.  Give the
      ** cursor an identifier P1.
      ** Open readonly if P2==0 and for reading and writing if P2!=0.
      ** The table is created if it does not already exist and P2!=0.
      ** If there is already another cursor opened with identifier P1,
      ** then the old cursor is closed first.
      ** All cursors are automatically closed when
      ** the VDBE finishes execution.  The P1 values need not be
      ** contiguous but all P1 values should be small integers.  It is
      ** an error for P1 to be negative.
      **






      ** If P3 is null or an empty string, a temporary table created.

      ** This table is automatically deleted when the cursor is closed.
      */
      case OP_Open: {
        int i = pOp->p1;
        if( i<0 ) goto bad_instruction;
        if( i>=p->nTable ){
          int j;
          p->aTab = sqliteRealloc( p->aTab, (i+1)*sizeof(VdbeTable) );
          if( p->aTab==0 ){ p->nTable = 0; goto no_mem; }
          for(j=p->nTable; j<=i; j++) p->aTab[j].pTable = 0;
          p->nTable = i+1;
        }else if( p->aTab[i].pTable ){
          sqliteDbbeCloseTable(p->aTab[i].pTable);
        }
        rc = sqliteDbbeOpenTable(p->pBe, pOp->p3, pOp->p2, &p->aTab[i].pTable);
        switch( rc ){
          case SQLITE_BUSY: {
            sqliteSetString(pzErrMsg,"table ", pOp->p3, " is locked", 0);
            break;
          }
          case SQLITE_PERM: {
            sqliteSetString(pzErrMsg, pOp->p2 ? "write" : "read",
              " permission denied for table ", pOp->p3, 0);
            break;
          }
          case SQLITE_READONLY: {
            sqliteSetString(pzErrMsg,"table ", pOp->p3, 
               " is readonly", 0);
            break;
          }
          case SQLITE_NOMEM: {
            goto no_mem;
          }
        }
        p->aTab[i].index = 0;
        p->aTab[i].keyAsData = 0;
        break;
      }

      /* Opcode: Close P1 * *
      **
      ** Close a database table previously opened as P1.  If P1 is not
      ** currently open, this instruction is a no-op.
      */
      case OP_Close: {
        int i = pOp->p1;
        if( i>=0 && i<p->nTable && p->aTab[i].pTable ){
          sqliteDbbeCloseTable(p->aTab[i].pTable);
          p->aTab[i].pTable = 0;
        }
        break;
      }

      /* Opcode: Fetch P1 * *
      **
      ** Pop the top of the stack and use its value as a key to fetch
      ** a record from database table or index P1.  The data is held
      ** in the P1 cursor until needed.  The data is not pushed onto the
      ** stack.
      */
      case OP_Fetch: {
        int i = pOp->p1;
        int tos = p->tos;
        if( tos<0 ) goto not_enough_stack;
        if( i>=0 && i<p->nTable && p->aTab[i].pTable ){
          if( p->aStack[tos].flags & STK_Int ){
            sqliteDbbeFetch(p->aTab[i].pTable, sizeof(int), 
                           (char*)&p->aStack[tos].i);
          }else{
            if( Stringify(p, tos) ) goto no_mem;
            sqliteDbbeFetch(p->aTab[i].pTable, p->aStack[tos].n, 
                           p->zStack[tos]);
          }
          p->nFetch++;
        }
        PopStack(p, 1);
        break;
      }







|
|
<
<
<
<
<
<



>
>
>
>
>
>
|
>
|




|

|
|
|
|
|
|

|



















|
|





|




|
|
|







|
|
<





|

|



|







1678
1679
1680
1681
1682
1683
1684
1685
1686






1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755

1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
        p->aStack[p->tos].flags = STK_Str|STK_Dyn;
        p->zStack[p->tos] = zNewKey;
        break;
      }

      /* Opcode: Open P1 P2 P3
      **
      ** Open a new cursor for the database file named P3.  Give the
      ** cursor an identifier P1.  The P1 values need not be






      ** contiguous but all P1 values should be small integers.  It is
      ** an error for P1 to be negative.
      **
      ** Open readonly if P2==0 and for reading and writing if P2!=0.
      ** The file is created if it does not already exist and P2!=0.
      ** If there is already another cursor opened with identifier P1,
      ** then the old cursor is closed first.  All cursors are
      ** automatically closed when the VDBE finishes execution.
      **
      ** If P3 is null or an empty string, a temporary database file
      ** is created.  This temporary database file is automatically 
      ** deleted when the cursor is closed.
      */
      case OP_Open: {
        int i = pOp->p1;
        if( i<0 ) goto bad_instruction;
        if( i>=p->nCursor ){
          int j;
          p->aCsr = sqliteRealloc( p->aCsr, (i+1)*sizeof(Cursor) );
          if( p->aCsr==0 ){ p->nCursor = 0; goto no_mem; }
          for(j=p->nCursor; j<=i; j++) p->aCsr[j].pCursor = 0;
          p->nCursor = i+1;
        }else if( p->aCsr[i].pCursor ){
          sqliteDbbeCloseCursor(p->aCsr[i].pCursor);
        }
        rc = sqliteDbbeOpenCursor(p->pBe, pOp->p3, pOp->p2,&p->aCsr[i].pCursor);
        switch( rc ){
          case SQLITE_BUSY: {
            sqliteSetString(pzErrMsg,"table ", pOp->p3, " is locked", 0);
            break;
          }
          case SQLITE_PERM: {
            sqliteSetString(pzErrMsg, pOp->p2 ? "write" : "read",
              " permission denied for table ", pOp->p3, 0);
            break;
          }
          case SQLITE_READONLY: {
            sqliteSetString(pzErrMsg,"table ", pOp->p3, 
               " is readonly", 0);
            break;
          }
          case SQLITE_NOMEM: {
            goto no_mem;
          }
        }
        p->aCsr[i].index = 0;
        p->aCsr[i].keyAsData = 0;
        break;
      }

      /* Opcode: Close P1 * *
      **
      ** Close a cursor previously opened as P1.  If P1 is not
      ** currently open, this instruction is a no-op.
      */
      case OP_Close: {
        int i = pOp->p1;
        if( i>=0 && i<p->nCursor && p->aCsr[i].pCursor ){
          sqliteDbbeCloseCursor(p->aCsr[i].pCursor);
          p->aCsr[i].pCursor = 0;
        }
        break;
      }

      /* Opcode: Fetch P1 * *
      **
      ** Pop the top of the stack and use its value as a key to fetch
      ** a record from cursor P1.  The key/data pair is held
      ** in the P1 cursor until needed.

      */
      case OP_Fetch: {
        int i = pOp->p1;
        int tos = p->tos;
        if( tos<0 ) goto not_enough_stack;
        if( i>=0 && i<p->nCursor && p->aCsr[i].pCursor ){
          if( p->aStack[tos].flags & STK_Int ){
            sqliteDbbeFetch(p->aCsr[i].pCursor, sizeof(int), 
                           (char*)&p->aStack[tos].i);
          }else{
            if( Stringify(p, tos) ) goto no_mem;
            sqliteDbbeFetch(p->aCsr[i].pCursor, p->aStack[tos].n, 
                           p->zStack[tos]);
          }
          p->nFetch++;
        }
        PopStack(p, 1);
        break;
      }
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935


1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946





1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994

1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049

2050

2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062








2063

2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100

2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142

2143
2144


2145


2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184

2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
        p->aStack[i].flags = STK_Int;
        break;
      }

      /* Opcode: Distinct P1 P2 *
      **
      ** Use the top of the stack as a key.  If a record with that key
      ** does not exist in table P1, then jump to P2.  If the record
      ** does already exist, then fall thru.  The record is not retrieved.
      ** The key is not popped from the stack.
      **
      ** This operation is similar to NotFound except that this operation
      ** does not pop the key from the stack.
      */
      /* Opcode: Found P1 P2 *
      **
      ** Use the top of the stack as a key.  If a record with that key
      ** does exist in table P1, then jump to P2.  If the record
      ** does not exist, then fall thru.  The record is not retrieved.
      ** The key is popped from the stack.
      */
      /* Opcode: NotFound P1 P2 *
      **
      ** Use the top of the stack as a key.  If a record with that key
      ** does not exist in table P1, then jump to P2.  If the record
      ** does exist, then fall thru.  The record is not retrieved.
      ** The key is popped from the stack.
      **
      ** The difference between this operation and Distinct is that
      ** Distinct does not pop the key from the stack.
      */
      case OP_Distinct:
      case OP_NotFound:
      case OP_Found: {
        int i = pOp->p1;
        int tos = p->tos;
        int alreadyExists = 0;
        if( tos<0 ) goto not_enough_stack;
        if( i>=0 && i<p->nTable && p->aTab[i].pTable ){
          if( p->aStack[tos].flags & STK_Int ){
            alreadyExists = sqliteDbbeTest(p->aTab[i].pTable, sizeof(int), 
                                          (char*)&p->aStack[tos].i);
          }else{
            if( Stringify(p, tos) ) goto no_mem;
            alreadyExists = sqliteDbbeTest(p->aTab[i].pTable, p->aStack[tos].n, 
                                           p->zStack[tos]);
          }
        }
        if( pOp->opcode==OP_Found ){
          if( alreadyExists ) pc = pOp->p2 - 1;
        }else{
          if( !alreadyExists ) pc = pOp->p2 - 1;
        }
        if( pOp->opcode!=OP_Distinct ){
          PopStack(p, 1);
        }
        break;
      }

      /* Opcode: New P1 * *
      **
      ** Get a new integer key not previous used by table P1 and
      ** push it onto the stack.
      */
      case OP_New: {
        int i = pOp->p1;
        int v;
        if( i<0 || i>=p->nTable || p->aTab[i].pTable==0 ){
          v = 0;
        }else{
          v = sqliteDbbeNew(p->aTab[i].pTable);
        }
        NeedStack(p, p->tos+1);
        p->tos++;
        p->aStack[p->tos].i = v;
        p->aStack[p->tos].flags = STK_Int;
        break;
      }

      /* Opcode: Put P1 * *
      **
      ** Write an entry into the database table P1.  A new entry is
      ** created if it doesn't already exist, or the data for an existing
      ** entry is overwritten.  The data is the value on the top of the
      ** stack.  The key is the next value down on the stack.  The stack
      ** is popped twice by this instruction.
      */
      case OP_Put: {
        int tos = p->tos;
        int nos = p->tos-1;
        int i = pOp->p1;
        if( nos<0 ) goto not_enough_stack;
        if( i>=0 && i<p->nTable && p->aTab[i].pTable!=0 ){
          char *zKey;
          int nKey;
          if( (p->aStack[nos].flags & STK_Int)==0 ){
            if( Stringify(p, nos) ) goto no_mem;
            nKey = p->aStack[nos].n;
            zKey = p->zStack[nos];
          }else{
            nKey = sizeof(int);
            zKey = (char*)&p->aStack[nos].i;
          }
          sqliteDbbePut(p->aTab[i].pTable, nKey, zKey,
                        p->aStack[tos].n, p->zStack[tos]);
        }
        PopStack(p, 2);
        break;
      }

      /* Opcode: Delete P1 * *
      **
      ** The top of the stack is a key.  Remove this key and its data
      ** from database table P1.  Then pop the stack to discard the key.
      */
      case OP_Delete: {
        int tos = p->tos;
        int i = pOp->p1;
        if( tos<0 ) goto not_enough_stack;
        if( i>=0 && i<p->nTable && p->aTab[i].pTable!=0 ){
          char *zKey;
          int nKey;
          if( p->aStack[tos].flags & STK_Int ){
            nKey = sizeof(int);
            zKey = (char*)&p->aStack[tos].i;
          }else{
            if( Stringify(p, tos) ) goto no_mem;
            nKey = p->aStack[tos].n;
            zKey = p->zStack[tos];
          }
          sqliteDbbeDelete(p->aTab[i].pTable, nKey, zKey);
        }
        PopStack(p, 1);
        break;
      }

      /* Opcode: KeyAsData P1 P2 *
      **
      ** Turn the key-as-data mode for cursor P1 either on (if P2==1) or
      ** off (if P2==0).  In key-as-data mode, the OP_Fetch opcode pulls
      ** data off of the key rather than the data.  This is useful for
      ** processing compound selects.
      */
      case OP_KeyAsData: {
        int i = pOp->p1;
        if( i>=0 && i<p->nTable && p->aTab[i].pTable!=0 ){
          p->aTab[i].keyAsData = pOp->p2;
        }
        break;
      }

      /* Opcode: Field P1 P2 *
      **


      ** Push onto the stack the value of the P2-th field from the
      ** most recent Fetch from table P1.
      ** 
      ** The value pushed is just a pointer to the data in the cursor.
      ** The value will go away the next time a record is fetched from P1,
      ** or when P1 is closed.  Make a copy of the string (using
      ** "Concat 1 0 0") if it needs to persist longer than that.
      **
      ** If the KeyAsData opcode has previously executed on this cursor,
      ** then the field might be extracted from the key rather than the
      ** data.





      */
      case OP_Field: {
        int *pAddr;
        int amt;
        int i = pOp->p1;
        int p2 = pOp->p2;
        int tos = ++p->tos;
        DbbeTable *pTab;
        char *z;

        if( NeedStack(p, tos) ) goto no_mem;
        if( i>=0 && i<p->nTable && (pTab = p->aTab[i].pTable)!=0 ){
          if( p->aTab[i].keyAsData ){
            amt = sqliteDbbeKeyLength(pTab);
            if( amt<=sizeof(int)*(p2+1) ){
              p->aStack[tos].flags = STK_Null;
              break;
            }
            pAddr = (int*)sqliteDbbeReadKey(pTab, sizeof(int)*p2);
            if( *pAddr==0 ){
              p->aStack[tos].flags = STK_Null;
              break;
            }
            z = sqliteDbbeReadKey(pTab, *pAddr);
          }else{
            amt = sqliteDbbeDataLength(pTab);
            if( amt<=sizeof(int)*(p2+1) ){
              p->aStack[tos].flags = STK_Null;
              break;
            }
            pAddr = (int*)sqliteDbbeReadData(pTab, sizeof(int)*p2);
            if( *pAddr==0 ){
              p->aStack[tos].flags = STK_Null;
              break;
            }
            z = sqliteDbbeReadData(pTab, *pAddr);
          }
          p->zStack[tos] = z;
          p->aStack[tos].n = strlen(z) + 1;
          p->aStack[tos].flags = STK_Str;
        }
        break;
      }

      /* Opcode: Key P1 * *
      **
      ** Push onto the stack an integer which is the first 4 bytes of the
      ** the key to the current entry in a sequential scan of the table P1.

      ** A sequential scan is started using the Next opcode.
      */
      case OP_Key: {
        int i = pOp->p1;
        int tos = ++p->tos;
        DbbeTable *pTab;

        if( NeedStack(p, p->tos) ) goto no_mem;
        if( i>=0 && i<p->nTable && (pTab = p->aTab[i].pTable)!=0 ){
          char *z = sqliteDbbeReadKey(pTab, 0);
          if( p->aTab[i].keyAsData ){
            p->zStack[tos] = z;
            p->aStack[tos].flags = STK_Str;
            p->aStack[tos].n = sqliteDbbeKeyLength(pTab);
          }else{
            memcpy(&p->aStack[tos].i, z, sizeof(int));
            p->aStack[tos].flags = STK_Int;
          }
        }
        break;
      }

      /* Opcode: Rewind P1 * *
      **
      ** The next use of the Key or Field or Next instruction for P1 
      ** will refer to the first entry in the table.
      */
      case OP_Rewind: {
        int i = pOp->p1;
        if( i>=0 && i<p->nTable && p->aTab[i].pTable!=0 ){
          sqliteDbbeRewind(p->aTab[i].pTable);
        }
        break;
      }

      /* Opcode: Next P1 P2 *
      **
      ** Advance P1 to the next entry in the table.  Or, if there are no
      ** more entries, rewind P1 and jump to location P2.
      */
      case OP_Next: {
        int i = pOp->p1;
        if( i>=0 && i<p->nTable && p->aTab[i].pTable!=0 ){
          if( sqliteDbbeNextKey(p->aTab[i].pTable)==0 ){
            pc = pOp->p2 - 1;
          }else{
            p->nFetch++;
          }
        }
        break;
      }

      /* Opcode: ResetIdx P1 * *
      **
      ** Begin treating the current row of table P1 as an index.  The next

      ** NextIdx instruction will refer to the first index in the table.

      */
      case OP_ResetIdx: {
        int i = pOp->p1;
        if( i>=0 && i<p->nTable ){
          p->aTab[i].index = 0;
        }
        break;
      }

      /* Opcode: NextIdx P1 P2 *
      **
      ** Push the next index from the current entry of table P1 onto the








      ** stack and advance the pointer.  If there are no more indices, then

      ** reset the table entry and jump to P2
      */
      case OP_NextIdx: {
        int i = pOp->p1;
        int tos = ++p->tos;
        DbbeTable *pTab;

        if( NeedStack(p, p->tos) ) goto no_mem;
        p->zStack[tos] = 0;
        if( i>=0 && i<p->nTable && (pTab = p->aTab[i].pTable)!=0 ){
          int *aIdx;
          int nIdx;
          int j;
          nIdx = sqliteDbbeDataLength(pTab)/sizeof(int);
          aIdx = (int*)sqliteDbbeReadData(pTab, 0);
          for(j=p->aTab[i].index; j<nIdx; j++){
            if( aIdx[j]!=0 ){
              p->aStack[tos].i = aIdx[j];
              p->aStack[tos].flags = STK_Int;
              break;
            }
          }
          if( j>=nIdx ){
            j = -1;
            pc = pOp->p2 - 1;
            PopStack(p, 1);
          }
          p->aTab[i].index = j+1;
        }
        break;
      }

      /* Opcode: PutIdx P1 * *
      **
      ** The top of the stack hold an index key (probably made using the
      ** MakeKey instruction) and next on stack holds an index value for
      ** a table.  Locate the record in the index P1 that has the key 

      ** and insert the index value into its
      ** data.  Write the results back to the index.
      ** If the key doesn't exist it is created.
      */
      case OP_PutIdx: {
        int i = pOp->p1;
        int tos = p->tos;
        int nos = tos - 1;
        DbbeTable *pTab;
        if( nos<0 ) goto not_enough_stack;
        if( i>=0 && i<p->nTable && (pTab = p->aTab[i].pTable)!=0 ){
          int r;
          int newVal;
          Integerify(p, nos);
          newVal = p->aStack[nos].i;
          if( Stringify(p, tos) ) goto no_mem;
          r = sqliteDbbeFetch(pTab, p->aStack[tos].n, p->zStack[tos]);
          if( r==0 ){
            /* Create a new record for this index */
            sqliteDbbePut(pTab, p->aStack[tos].n, p->zStack[tos],
                          sizeof(int), (char*)&newVal);
          }else{
            /* Extend the existing record */
            int nIdx;
            int *aIdx;
            nIdx = sqliteDbbeDataLength(pTab)/sizeof(int);
            aIdx = sqliteMalloc( sizeof(int)*(nIdx+1) );
            if( aIdx==0 ) goto no_mem;
            sqliteDbbeCopyData(pTab, 0, nIdx*sizeof(int), (char*)aIdx);
            aIdx[nIdx] = newVal;
            sqliteDbbePut(pTab, p->aStack[tos].n, p->zStack[tos],
                          sizeof(int)*(nIdx+1), (char*)aIdx);
            sqliteFree(aIdx);
          }
        }
        PopStack(p, 2);
        break;
      }

      /* Opcode: DeleteIdx P1 * *
      **
      ** The top of the stack is a key and next on stack is an index value.

      ** Locate the record
      ** in index P1 that has the key and remove the index value from its


      ** data.  Write the results back to the table.  If after removing


      ** the index value no more indices remain in the record, then the
      ** record is removed from the table.
      */
      case OP_DeleteIdx: {
        int i = pOp->p1;
        int tos = p->tos;
        int nos = tos - 1;
        DbbeTable *pTab;
        if( nos<0 ) goto not_enough_stack;
        if( i>=0 && i<p->nTable && (pTab = p->aTab[i].pTable)!=0 ){
          int *aIdx;
          int nIdx;
          int j;
          int r;
          int oldVal;
          Integerify(p, nos);
          oldVal = p->aStack[nos].i;
          if( Stringify(p, tos) ) goto no_mem;
          r = sqliteDbbeFetch(pTab, p->aStack[tos].n, p->zStack[tos]);
          if( r==0 ) break;
          nIdx = sqliteDbbeDataLength(pTab)/sizeof(int);
          aIdx = (int*)sqliteDbbeReadData(pTab, 0);
          for(j=0; j<nIdx && aIdx[j]!=oldVal; j++){}
          if( j>=nIdx ) break;
          aIdx[j] = aIdx[nIdx-1];
          if( nIdx==1 ){
            sqliteDbbeDelete(pTab, p->aStack[tos].n, p->zStack[tos]);
          }else{
            sqliteDbbePut(pTab, p->aStack[tos].n, p->zStack[tos], 
                          sizeof(int)*(nIdx-1), (char*)aIdx);
          }
        }
        PopStack(p, 2);
        break;
      }

      /* Opcode: Destroy * * P3
      **
      ** Drop the table whose name is P3.  The file that holds this table

      ** is removed from the disk drive.
      */
      case OP_Destroy: {
        sqliteDbbeDropTable(p->pBe, pOp->p3);
        break;
      }

      /* Opcode: Reorganize * * P3
      **
      ** Compress, optimize, and tidy up the GDBM file named by P3.
      */
      case OP_Reorganize: {
        sqliteDbbeReorganizeTable(p->pBe, pOp->p3);
        break;
      }

      /* Opcode: ListOpen P1 * *
      **
      ** Open a file used for temporary storage of index numbers.  P1
      ** will server as a handle to this temporary file for future
      ** interactions.  If another temporary file with the P1 handle is
      ** already opened, the prior file is closed and a new one opened
      ** in its place.
      */
      case OP_ListOpen: {
        int i = pOp->p1;







|









|






|













|

|



|
















|
|




|


|










|










|










|









|





|










|














|
|






>
>
|
|









>
>
>
>
>







|



|
|
|




|




|

|




|




|











|
>
|




|


|
|
|


|











|



|
|






|
|



|
|










|
>
|
>



|
|






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




|



|



|
|
|











|






|
|
|
>
|
|
<





|

|





|


|





|


|

|










|
>
|
|
>
>
|
>
>
|
<





|

|








|

|
|




|

|









|
>
|

















|







1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128

2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176

2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
        p->aStack[i].flags = STK_Int;
        break;
      }

      /* Opcode: Distinct P1 P2 *
      **
      ** Use the top of the stack as a key.  If a record with that key
      ** does not exist in file P1, then jump to P2.  If the record
      ** does already exist, then fall thru.  The record is not retrieved.
      ** The key is not popped from the stack.
      **
      ** This operation is similar to NotFound except that this operation
      ** does not pop the key from the stack.
      */
      /* Opcode: Found P1 P2 *
      **
      ** Use the top of the stack as a key.  If a record with that key
      ** does exist in file P1, then jump to P2.  If the record
      ** does not exist, then fall thru.  The record is not retrieved.
      ** The key is popped from the stack.
      */
      /* Opcode: NotFound P1 P2 *
      **
      ** Use the top of the stack as a key.  If a record with that key
      ** does not exist in file P1, then jump to P2.  If the record
      ** does exist, then fall thru.  The record is not retrieved.
      ** The key is popped from the stack.
      **
      ** The difference between this operation and Distinct is that
      ** Distinct does not pop the key from the stack.
      */
      case OP_Distinct:
      case OP_NotFound:
      case OP_Found: {
        int i = pOp->p1;
        int tos = p->tos;
        int alreadyExists = 0;
        if( tos<0 ) goto not_enough_stack;
        if( i>=0 && i<p->nCursor && p->aCsr[i].pCursor ){
          if( p->aStack[tos].flags & STK_Int ){
            alreadyExists = sqliteDbbeTest(p->aCsr[i].pCursor, sizeof(int), 
                                          (char*)&p->aStack[tos].i);
          }else{
            if( Stringify(p, tos) ) goto no_mem;
            alreadyExists = sqliteDbbeTest(p->aCsr[i].pCursor,p->aStack[tos].n, 
                                           p->zStack[tos]);
          }
        }
        if( pOp->opcode==OP_Found ){
          if( alreadyExists ) pc = pOp->p2 - 1;
        }else{
          if( !alreadyExists ) pc = pOp->p2 - 1;
        }
        if( pOp->opcode!=OP_Distinct ){
          PopStack(p, 1);
        }
        break;
      }

      /* Opcode: New P1 * *
      **
      ** Get a new integer key not previous used by the database file
      ** associated with cursor P1 and push it onto the stack.
      */
      case OP_New: {
        int i = pOp->p1;
        int v;
        if( i<0 || i>=p->nCursor || p->aCsr[i].pCursor==0 ){
          v = 0;
        }else{
          v = sqliteDbbeNew(p->aCsr[i].pCursor);
        }
        NeedStack(p, p->tos+1);
        p->tos++;
        p->aStack[p->tos].i = v;
        p->aStack[p->tos].flags = STK_Int;
        break;
      }

      /* Opcode: Put P1 * *
      **
      ** Write an entry into the database file P1.  A new entry is
      ** created if it doesn't already exist, or the data for an existing
      ** entry is overwritten.  The data is the value on the top of the
      ** stack.  The key is the next value down on the stack.  The stack
      ** is popped twice by this instruction.
      */
      case OP_Put: {
        int tos = p->tos;
        int nos = p->tos-1;
        int i = pOp->p1;
        if( nos<0 ) goto not_enough_stack;
        if( i>=0 && i<p->nCursor && p->aCsr[i].pCursor!=0 ){
          char *zKey;
          int nKey;
          if( (p->aStack[nos].flags & STK_Int)==0 ){
            if( Stringify(p, nos) ) goto no_mem;
            nKey = p->aStack[nos].n;
            zKey = p->zStack[nos];
          }else{
            nKey = sizeof(int);
            zKey = (char*)&p->aStack[nos].i;
          }
          sqliteDbbePut(p->aCsr[i].pCursor, nKey, zKey,
                        p->aStack[tos].n, p->zStack[tos]);
        }
        PopStack(p, 2);
        break;
      }

      /* Opcode: Delete P1 * *
      **
      ** The top of the stack is a key.  Remove this key and its data
      ** from database file P1.  Then pop the stack to discard the key.
      */
      case OP_Delete: {
        int tos = p->tos;
        int i = pOp->p1;
        if( tos<0 ) goto not_enough_stack;
        if( i>=0 && i<p->nCursor && p->aCsr[i].pCursor!=0 ){
          char *zKey;
          int nKey;
          if( p->aStack[tos].flags & STK_Int ){
            nKey = sizeof(int);
            zKey = (char*)&p->aStack[tos].i;
          }else{
            if( Stringify(p, tos) ) goto no_mem;
            nKey = p->aStack[tos].n;
            zKey = p->zStack[tos];
          }
          sqliteDbbeDelete(p->aCsr[i].pCursor, nKey, zKey);
        }
        PopStack(p, 1);
        break;
      }

      /* Opcode: KeyAsData P1 P2 *
      **
      ** Turn the key-as-data mode for cursor P1 either on (if P2==1) or
      ** off (if P2==0).  In key-as-data mode, the OP_Fetch opcode pulls
      ** data off of the key rather than the data.  This is useful for
      ** processing compound selects.
      */
      case OP_KeyAsData: {
        int i = pOp->p1;
        if( i>=0 && i<p->nCursor && p->aCsr[i].pCursor!=0 ){
          p->aCsr[i].keyAsData = pOp->p2;
        }
        break;
      }

      /* Opcode: Field P1 P2 *
      **
      ** Interpret the data in the most recent fetch from cursor P1
      ** is a structure built using the MakeRecord instruction.
      ** Push onto the stack the value of the P2-th field of that
      ** structure.
      ** 
      ** The value pushed is just a pointer to the data in the cursor.
      ** The value will go away the next time a record is fetched from P1,
      ** or when P1 is closed.  Make a copy of the string (using
      ** "Concat 1 0 0") if it needs to persist longer than that.
      **
      ** If the KeyAsData opcode has previously executed on this cursor,
      ** then the field might be extracted from the key rather than the
      ** data.
      **
      ** Viewed from a higher level, this instruction retrieves the
      ** data from a single column in a particular row of an SQL table
      ** file.  Perhaps the name of this instruction should be
      ** "Column" instead of "Field"...
      */
      case OP_Field: {
        int *pAddr;
        int amt;
        int i = pOp->p1;
        int p2 = pOp->p2;
        int tos = ++p->tos;
        DbbeCursor *pCrsr;
        char *z;

        if( NeedStack(p, tos) ) goto no_mem;
        if( i>=0 && i<p->nCursor && (pCrsr = p->aCsr[i].pCursor)!=0 ){
          if( p->aCsr[i].keyAsData ){
            amt = sqliteDbbeKeyLength(pCrsr);
            if( amt<=sizeof(int)*(p2+1) ){
              p->aStack[tos].flags = STK_Null;
              break;
            }
            pAddr = (int*)sqliteDbbeReadKey(pCrsr, sizeof(int)*p2);
            if( *pAddr==0 ){
              p->aStack[tos].flags = STK_Null;
              break;
            }
            z = sqliteDbbeReadKey(pCrsr, *pAddr);
          }else{
            amt = sqliteDbbeDataLength(pCrsr);
            if( amt<=sizeof(int)*(p2+1) ){
              p->aStack[tos].flags = STK_Null;
              break;
            }
            pAddr = (int*)sqliteDbbeReadData(pCrsr, sizeof(int)*p2);
            if( *pAddr==0 ){
              p->aStack[tos].flags = STK_Null;
              break;
            }
            z = sqliteDbbeReadData(pCrsr, *pAddr);
          }
          p->zStack[tos] = z;
          p->aStack[tos].n = strlen(z) + 1;
          p->aStack[tos].flags = STK_Str;
        }
        break;
      }

      /* Opcode: Key P1 * *
      **
      ** Push onto the stack an integer which is the first 4 bytes of the
      ** the key to the current entry in a sequential scan of the database
      ** file P1.  The sequential scan should have been started using the 
      ** Next opcode.
      */
      case OP_Key: {
        int i = pOp->p1;
        int tos = ++p->tos;
        DbbeCursor *pCrsr;

        if( NeedStack(p, p->tos) ) goto no_mem;
        if( i>=0 && i<p->nCursor && (pCrsr = p->aCsr[i].pCursor)!=0 ){
          char *z = sqliteDbbeReadKey(pCrsr, 0);
          if( p->aCsr[i].keyAsData ){
            p->zStack[tos] = z;
            p->aStack[tos].flags = STK_Str;
            p->aStack[tos].n = sqliteDbbeKeyLength(pCrsr);
          }else{
            memcpy(&p->aStack[tos].i, z, sizeof(int));
            p->aStack[tos].flags = STK_Int;
          }
        }
        break;
      }

      /* Opcode: Rewind P1 * *
      **
      ** The next use of the Key or Field or Next instruction for P1 
      ** will refer to the first entry in the database file.
      */
      case OP_Rewind: {
        int i = pOp->p1;
        if( i>=0 && i<p->nCursor && p->aCsr[i].pCursor!=0 ){
          sqliteDbbeRewind(p->aCsr[i].pCursor);
        }
        break;
      }

      /* Opcode: Next P1 P2 *
      **
      ** Advance P1 to the next key/data pair in the file.  Or, if there are no
      ** more key/data pairs, rewind P1 and jump to location P2.
      */
      case OP_Next: {
        int i = pOp->p1;
        if( i>=0 && i<p->nCursor && p->aCsr[i].pCursor!=0 ){
          if( sqliteDbbeNextKey(p->aCsr[i].pCursor)==0 ){
            pc = pOp->p2 - 1;
          }else{
            p->nFetch++;
          }
        }
        break;
      }

      /* Opcode: ResetIdx P1 * *
      **
      ** Begin treating the current data in cursor P1 as a bunch of integer
      ** keys to records of a (separate) SQL table file.  This instruction
      ** causes the new NextIdx instruction push the first integer table
      ** key in the data.
      */
      case OP_ResetIdx: {
        int i = pOp->p1;
        if( i>=0 && i<p->nCursor ){
          p->aCsr[i].index = 0;
        }
        break;
      }

      /* Opcode: NextIdx P1 P2 *
      **
      ** The P1 cursor points to an SQL index.  The data from the most
      ** recent fetch on that cursor consists of a bunch of integers where
      ** each integer is the key to a record in an SQL table file.
      ** This instruction grabs the next integer table key from the data
      ** of P1 and pushes that integer onto the stack.  The first time
      ** this instruction is executed after a fetch, the first integer 
      ** table key is pushed.  Subsequent integer table keys are pushed 
      ** in each subsequent execution of this instruction.
      **
      ** If there are no more integer table keys in the data of P1
      ** when this instruction is executed, then nothing gets pushed and
      ** there is an immediate jump to instruction P2.
      */
      case OP_NextIdx: {
        int i = pOp->p1;
        int tos = ++p->tos;
        DbbeCursor *pCrsr;

        if( NeedStack(p, p->tos) ) goto no_mem;
        p->zStack[tos] = 0;
        if( i>=0 && i<p->nCursor && (pCrsr = p->aCsr[i].pCursor)!=0 ){
          int *aIdx;
          int nIdx;
          int j;
          nIdx = sqliteDbbeDataLength(pCrsr)/sizeof(int);
          aIdx = (int*)sqliteDbbeReadData(pCrsr, 0);
          for(j=p->aCsr[i].index; j<nIdx; j++){
            if( aIdx[j]!=0 ){
              p->aStack[tos].i = aIdx[j];
              p->aStack[tos].flags = STK_Int;
              break;
            }
          }
          if( j>=nIdx ){
            j = -1;
            pc = pOp->p2 - 1;
            PopStack(p, 1);
          }
          p->aCsr[i].index = j+1;
        }
        break;
      }

      /* Opcode: PutIdx P1 * *
      **
      ** The top of the stack hold an SQL index key (probably made using the
      ** MakeKey instruction) and next on stack holds an integer which
      ** the key to an SQL table entry.  Locate the record in cursor P1
      ** that has the same key as on the TOS.  Create a new record if
      ** necessary.  Then append the integer table key to the data for that
      ** record and write it back to the P1 file.

      */
      case OP_PutIdx: {
        int i = pOp->p1;
        int tos = p->tos;
        int nos = tos - 1;
        DbbeCursor *pCrsr;
        if( nos<0 ) goto not_enough_stack;
        if( i>=0 && i<p->nCursor && (pCrsr = p->aCsr[i].pCursor)!=0 ){
          int r;
          int newVal;
          Integerify(p, nos);
          newVal = p->aStack[nos].i;
          if( Stringify(p, tos) ) goto no_mem;
          r = sqliteDbbeFetch(pCrsr, p->aStack[tos].n, p->zStack[tos]);
          if( r==0 ){
            /* Create a new record for this index */
            sqliteDbbePut(pCrsr, p->aStack[tos].n, p->zStack[tos],
                          sizeof(int), (char*)&newVal);
          }else{
            /* Extend the existing record */
            int nIdx;
            int *aIdx;
            nIdx = sqliteDbbeDataLength(pCrsr)/sizeof(int);
            aIdx = sqliteMalloc( sizeof(int)*(nIdx+1) );
            if( aIdx==0 ) goto no_mem;
            sqliteDbbeCopyData(pCrsr, 0, nIdx*sizeof(int), (char*)aIdx);
            aIdx[nIdx] = newVal;
            sqliteDbbePut(pCrsr, p->aStack[tos].n, p->zStack[tos],
                          sizeof(int)*(nIdx+1), (char*)aIdx);
            sqliteFree(aIdx);
          }
        }
        PopStack(p, 2);
        break;
      }

      /* Opcode: DeleteIdx P1 * *
      **
      ** The top of the stack is a key and next on stack is integer
      ** which is the key to a record in an SQL table.
      ** Locate the record in the cursor P1 (P1 represents an SQL index)
      ** that has the same key as the top of stack.  Then look through
      ** the integer table-keys contained in the data of the P1 record.
      ** Remove the integer table-key that matches the NOS and write the
      ** revised data back to P1 with the same key.
      **
      ** If this routine removes the very last integer table-key from
      ** the P1 data, then the corresponding P1 record is deleted.

      */
      case OP_DeleteIdx: {
        int i = pOp->p1;
        int tos = p->tos;
        int nos = tos - 1;
        DbbeCursor *pCrsr;
        if( nos<0 ) goto not_enough_stack;
        if( i>=0 && i<p->nCursor && (pCrsr = p->aCsr[i].pCursor)!=0 ){
          int *aIdx;
          int nIdx;
          int j;
          int r;
          int oldVal;
          Integerify(p, nos);
          oldVal = p->aStack[nos].i;
          if( Stringify(p, tos) ) goto no_mem;
          r = sqliteDbbeFetch(pCrsr, p->aStack[tos].n, p->zStack[tos]);
          if( r==0 ) break;
          nIdx = sqliteDbbeDataLength(pCrsr)/sizeof(int);
          aIdx = (int*)sqliteDbbeReadData(pCrsr, 0);
          for(j=0; j<nIdx && aIdx[j]!=oldVal; j++){}
          if( j>=nIdx ) break;
          aIdx[j] = aIdx[nIdx-1];
          if( nIdx==1 ){
            sqliteDbbeDelete(pCrsr, p->aStack[tos].n, p->zStack[tos]);
          }else{
            sqliteDbbePut(pCrsr, p->aStack[tos].n, p->zStack[tos], 
                          sizeof(int)*(nIdx-1), (char*)aIdx);
          }
        }
        PopStack(p, 2);
        break;
      }

      /* Opcode: Destroy * * P3
      **
      ** Drop the disk file whose name is P3.  All key/data pairs in
      ** the file are deleted and the file itself is removed
      ** from the disk.
      */
      case OP_Destroy: {
        sqliteDbbeDropTable(p->pBe, pOp->p3);
        break;
      }

      /* Opcode: Reorganize * * P3
      **
      ** Compress, optimize, and tidy up the GDBM file named by P3.
      */
      case OP_Reorganize: {
        sqliteDbbeReorganizeTable(p->pBe, pOp->p3);
        break;
      }

      /* Opcode: ListOpen P1 * *
      **
      ** Open a file used for temporary storage of integer table keys.  P1
      ** will server as a handle to this temporary file for future
      ** interactions.  If another temporary file with the P1 handle is
      ** already opened, the prior file is closed and a new one opened
      ** in its place.
      */
      case OP_ListOpen: {
        int i = pOp->p1;
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
        }
        break;
      }

      /* Opcode: ListRead P1 P2 *
      **
      ** Attempt to read an integer from temporary storage buffer P1
      ** and push it onto the stack.  If the storage buffer is empty
      ** push nothing but instead jump to P2.
      */
      case OP_ListRead: {
        int i = pOp->p1;
        int val, amt;
        if( i<0 || i>=p->nList || p->apList[i]==0 ) goto bad_instruction;
        amt = fread(&val, sizeof(int), 1, p->apList[i]);







|







2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
        }
        break;
      }

      /* Opcode: ListRead P1 P2 *
      **
      ** Attempt to read an integer from temporary storage buffer P1
      ** and push it onto the stack.  If the storage buffer is empty, 
      ** push nothing but instead jump to P2.
      */
      case OP_ListRead: {
        int i = pOp->p1;
        int val, amt;
        if( i<0 || i>=p->nList || p->apList[i]==0 ) goto bad_instruction;
        amt = fread(&val, sizeof(int), 1, p->apList[i]);
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
        pc = pOp->p2 - 1;
        break;
      }

      /* Opcode: FileField P1 * *
      **
      ** Push onto the stack the P1-th field of the most recently read line
      ** from the file.
      */
      case OP_FileField: {
        int i = pOp->p1;
        char *z;
        if( NeedStack(p, p->tos+1) ) goto no_mem;
        if( i>=0 && i<p->nField && p->azField ){
          z = p->azField[i];







|







2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
        pc = pOp->p2 - 1;
        break;
      }

      /* Opcode: FileField P1 * *
      **
      ** Push onto the stack the P1-th field of the most recently read line
      ** from the input file.
      */
      case OP_FileField: {
        int i = pOp->p1;
        char *z;
        if( NeedStack(p, p->tos+1) ) goto no_mem;
        if( i>=0 && i<p->nField && p->azField ){
          z = p->azField[i];
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
        }
        PopStack(p, 1);
        break; 
      }

      /* Opcode: AggIncr P1 P2 *
      **
      ** Increment the P2-th field of the aggregate element current
      ** in focus by an amount P1.
      */
      case OP_AggIncr: {
        AggElem *pFocus = AggInFocus(p->agg);
        int i = pOp->p2;
        if( pFocus==0 ) goto no_mem;
        if( i>=0 && i<p->agg.nMem ){
          Mem *pMem = &pFocus->aMem[i];







|
|







2854
2855
2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
        }
        PopStack(p, 1);
        break; 
      }

      /* Opcode: AggIncr P1 P2 *
      **
      ** Increase the integer value in the P2-th field of the aggregate
      ** element current in focus by an amount P1.
      */
      case OP_AggIncr: {
        AggElem *pFocus = AggInFocus(p->agg);
        int i = pOp->p2;
        if( pFocus==0 ) goto no_mem;
        if( i>=0 && i<p->agg.nMem ){
          Mem *pMem = &pFocus->aMem[i];
Changes to src/where.c.
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
**   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.7 2000/06/06 13:54:16 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.
*/
typedef struct ExprInfo ExprInfo;
struct ExprInfo {
  Expr *p;                /* Pointer to the subexpression */
  int indexable;          /* True if this subexprssion is usable by an index */
  int idxLeft;            /* p->pLeft is a field in this table number. -1 if
                          ** p->pLeft is not the field of any table */
  int idxRight;           /* p->pRight is a field in this table number. -1 if
                          ** p->pRight is not the field of any table */
  unsigned prereqLeft;    /* Tables referenced by p->pLeft */
  unsigned prereqRight;   /* Tables referenced by p->pRight */
};

/*
** Determine the number of elements in an array.
*/







|












|
|
|
|







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
**   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.8 2000/06/21 13:59:13 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.
*/
typedef struct ExprInfo ExprInfo;
struct ExprInfo {
  Expr *p;                /* Pointer to the subexpression */
  int indexable;          /* True if this subexprssion is usable by an index */
  int idxLeft;            /* p->pLeft is a column in this table number. -1 if
                          ** p->pLeft is not the column of any table */
  int idxRight;           /* p->pRight is a column in this table number. -1 if
                          ** p->pRight is not the column of any table */
  unsigned prereqLeft;    /* Tables referenced by p->pLeft */
  unsigned prereqRight;   /* Tables referenced by p->pRight */
};

/*
** Determine the number of elements in an array.
*/
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
** "base" is the cursor number (the value of the iTable field) that
** corresponds to the first entry in the table list.  This is the
** same as pParse->nTab.
*/
static int exprTableUsage(int base, Expr *p){
  unsigned int mask = 0;
  if( p==0 ) return 0;
  if( p->op==TK_FIELD ){
    return 1<< (p->iTable - base);
  }
  if( p->pRight ){
    mask = exprTableUsage(base, p->pRight);
  }
  if( p->pLeft ){
    mask |= exprTableUsage(base, p->pLeft);







|







90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
** "base" is the cursor number (the value of the iTable field) that
** corresponds to the first entry in the table list.  This is the
** same as pParse->nTab.
*/
static int exprTableUsage(int base, Expr *p){
  unsigned int mask = 0;
  if( p==0 ) return 0;
  if( p->op==TK_COLUMN ){
    return 1<< (p->iTable - base);
  }
  if( p->pRight ){
    mask = exprTableUsage(base, p->pRight);
  }
  if( p->pLeft ){
    mask |= exprTableUsage(base, p->pLeft);
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
  Expr *pExpr = pInfo->p;
  pInfo->prereqLeft = exprTableUsage(base, pExpr->pLeft);
  pInfo->prereqRight = exprTableUsage(base, pExpr->pRight);
  pInfo->indexable = 0;
  pInfo->idxLeft = -1;
  pInfo->idxRight = -1;
  if( pExpr->op==TK_EQ && (pInfo->prereqRight & pInfo->prereqLeft)==0 ){
    if( pExpr->pRight->op==TK_FIELD ){
      pInfo->idxRight = pExpr->pRight->iTable - base;
      pInfo->indexable = 1;
    }
    if( pExpr->pLeft->op==TK_FIELD ){
      pInfo->idxLeft = pExpr->pLeft->iTable - base;
      pInfo->indexable = 1;
    }
  }
}

/*







|



|







120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
  Expr *pExpr = pInfo->p;
  pInfo->prereqLeft = exprTableUsage(base, pExpr->pLeft);
  pInfo->prereqRight = exprTableUsage(base, pExpr->pRight);
  pInfo->indexable = 0;
  pInfo->idxLeft = -1;
  pInfo->idxRight = -1;
  if( pExpr->op==TK_EQ && (pInfo->prereqRight & pInfo->prereqLeft)==0 ){
    if( pExpr->pRight->op==TK_COLUMN ){
      pInfo->idxRight = pExpr->pRight->iTable - base;
      pInfo->indexable = 1;
    }
    if( pExpr->pLeft->op==TK_COLUMN ){
      pInfo->idxLeft = pExpr->pLeft->iTable - base;
      pInfo->indexable = 1;
    }
  }
}

/*
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
268
269
    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 nField.  A usable index is one for
    ** which there are subexpressions to compute every field of the
    ** index.
    */
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      int j;
      int fieldMask = 0;

      if( pIdx->nField>32 ) continue;
      for(j=0; j<nExpr; j++){
        if( aExpr[j].idxLeft==idx 
             && (aExpr[j].prereqRight & loopMask)==aExpr[j].prereqRight ){
          int iField = aExpr[j].p->pLeft->iField;
          int k;
          for(k=0; k<pIdx->nField; k++){
            if( pIdx->aiField[k]==iField ){
              fieldMask |= 1<<k;
              break;
            }
          }
        }
        if( aExpr[j].idxRight==idx 
             && (aExpr[j].prereqLeft & loopMask)==aExpr[j].prereqLeft ){
          int iField = aExpr[j].p->pRight->iField;
          int k;
          for(k=0; k<pIdx->nField; k++){
            if( pIdx->aiField[k]==iField ){
              fieldMask |= 1<<k;
              break;
            }
          }
        }
      }
      if( fieldMask + 1 == (1<<pIdx->nField) ){
        if( pBestIdx==0 || pBestIdx->nField<pIdx->nField ){
          pBestIdx = pIdx;
        }
      }
    }
    aIdx[i] = pBestIdx;
    loopMask |= 1<<idx;
  }







|
|




|

|



|

|
|
|






|

|
|
|





|
|







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
268
269
    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;
          int k;
          for(k=0; k<pIdx->nColumn; k++){
            if( pIdx->aiColumn[k]==iColumn ){
              columnMask |= 1<<k;
              break;
            }
          }
        }
        if( aExpr[j].idxRight==idx 
             && (aExpr[j].prereqLeft & loopMask)==aExpr[j].prereqLeft ){
          int iColumn = aExpr[j].p->pRight->iColumn;
          int k;
          for(k=0; k<pIdx->nColumn; k++){
            if( pIdx->aiColumn[k]==iColumn ){
              columnMask |= 1<<k;
              break;
            }
          }
        }
      }
      if( columnMask + 1 == (1<<pIdx->nColumn) ){
        if( pBestIdx==0 || pBestIdx->nColumn<pIdx->nColumn ){
          pBestIdx = pIdx;
        }
      }
    }
    aIdx[i] = pBestIdx;
    loopMask |= 1<<idx;
  }
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
      ** 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->nField; 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->iField==pIdx->aiField[j]
          ){
            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->iField==pIdx->aiField[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pLeft);
            aExpr[k].p = 0;
            break;
          }
        }
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nField, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_Fetch, base+pTabList->nId+i, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_NextIdx, base+pTabList->nId+i, brk, 0, cont);
      if( i==pTabList->nId-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_Fetch, idx, 0, 0, 0);
        haveKey = 0;







|




|







|







|







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
      ** 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]
          ){
            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==pIdx->aiColumn[j]
          ){
            sqliteExprCode(pParse, aExpr[k].p->pLeft);
            aExpr[k].p = 0;
            break;
          }
        }
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nColumn, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_Fetch, base+pTabList->nId+i, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_NextIdx, base+pTabList->nId+i, brk, 0, cont);
      if( i==pTabList->nId-1 && pushKey ){
        haveKey = 1;
      }else{
        sqliteVdbeAddOp(v, OP_Fetch, idx, 0, 0, 0);
        haveKey = 0;
Changes to test/delete.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 DELETE FROM statement.
#
# $Id: delete.test,v 1.5 2000/06/08 15:10:48 drh Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-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 DELETE FROM statement.
#
# $Id: delete.test,v 1.6 2000/06/21 13:59:13 drh Exp $

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

# Try to delete from a non-existant table.
#
do_test delete-1.1 {
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

# Semantic errors in the WHERE clause
#
do_test delete-4.1 {
  execsql {CREATE TABLE table2(f1 int, f2 int)}
  set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
  lappend v $msg
} {1 {no such field: f3}}

do_test delete-4.2 {
  set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
  lappend v $msg
} {1 {no such function: xyzzy}}

finish_test







|







68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

# Semantic errors in the WHERE clause
#
do_test delete-4.1 {
  execsql {CREATE TABLE table2(f1 int, f2 int)}
  set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
  lappend v $msg
} {1 {no such column: f3}}

do_test delete-4.2 {
  set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
  lappend v $msg
} {1 {no such function: xyzzy}}

finish_test
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.2 2000/06/07 23:51:51 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.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 {
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
do_test in-2.10 {
  set v [catch {execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))}} msg]
  lappend v $msg
} {1 {right-hand side of IN operator must be constant}}
do_test in-2.11 {
  set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg]
  lappend v $msg
} {1 {no such field: c}}

# Testing the IN operator where the right-hand side is a SELECT
#
do_test in-3.1 {
  execsql {
    SELECT a FROM t1
    WHERE b IN (SELECT b FROM t1 WHERE a<5)







|







102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
do_test in-2.10 {
  set v [catch {execsql {SELECT a FROM t1 WHERE min(0,b IN (a,30))}} msg]
  lappend v $msg
} {1 {right-hand side of IN operator must be constant}}
do_test in-2.11 {
  set v [catch {execsql {SELECT a FROM t1 WHERE c IN (10,20)}} msg]
  lappend v $msg
} {1 {no such column: c}}

# Testing the IN operator where the right-hand side is a SELECT
#
do_test in-3.1 {
  execsql {
    SELECT a FROM t1
    WHERE b IN (SELECT b FROM t1 WHERE a<5)
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.5 2000/06/07 15:11:27 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.6 2000/06/21 13:59:13 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
  set v [catch {execsql {SELECT * FROM test2, test1}} msg]
  lappend v $msg
} {1 {no such table: test2}}

execsql {INSERT INTO test1(f1,f2) VALUES(11,22)}


# Make sure the fields are extracted correctly.
#
do_test select1-1.4 {
  execsql {SELECT f1 FROM test1}
} {11}
do_test select1-1.5 {
  execsql {SELECT f2 FROM test1}
} {22}







|







45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
  set v [catch {execsql {SELECT * FROM test2, test1}} msg]
  lappend v $msg
} {1 {no such table: test2}}

execsql {INSERT INTO test1(f1,f2) VALUES(11,22)}


# Make sure the columns are extracted correctly.
#
do_test select1-1.4 {
  execsql {SELECT f1 FROM test1}
} {11}
do_test select1-1.5 {
  execsql {SELECT f2 FROM test1}
} {22}
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
  set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 33}

execsql {CREATE TABLE test2(t1 test, t2 text)}
execsql {INSERT INTO test2 VALUES('abc','xyz')}

# Check for field naming
#
do_test select1-6.1 {
  set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {f1 11 f1 33}}
do_test select1-6.2 {
  set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg]







|







240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
  set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 33}

execsql {CREATE TABLE test2(t1 test, t2 text)}
execsql {INSERT INTO test2 VALUES('abc','xyz')}

# Check for column naming
#
do_test select1-6.1 {
  set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 {f1 11 f1 33}}
do_test select1-6.2 {
  set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg]
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
         ORDER BY f2}} msg]
  lappend v $msg
} {0 {A.f1 11 test2.t1 abc A.f1 33 test2.t1 abc}}
do_test select1-6.8 {
  set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B 
         ORDER BY f2}} msg]
  lappend v $msg
} {1 {ambiguous field name: f1}}
do_test select1-6.8b {
  set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 
         ORDER BY f2}} msg]
  lappend v $msg
} {1 {ambiguous field name: f2}}
do_test select1-6.8c {
  set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A 
         ORDER BY f2}} msg]
  lappend v $msg
} {1 {ambiguous field 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







|




|




|







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
         ORDER BY f2}} msg]
  lappend v $msg
} {0 {A.f1 11 test2.t1 abc A.f1 33 test2.t1 abc}}
do_test select1-6.8 {
  set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B 
         ORDER BY f2}} msg]
  lappend v $msg
} {1 {ambiguous column name: f1}}
do_test select1-6.8b {
  set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B 
         ORDER BY f2}} msg]
  lappend v $msg
} {1 {ambiguous column name: f2}}
do_test select1-6.8c {
  set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A 
         ORDER BY f2}} msg]
  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
Changes to test/select2.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: select2.test,v 1.6 2000/06/08 16:54:40 drh Exp $

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

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}







|







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: select2.test,v 1.7 2000/06/21 13:59:13 drh Exp $

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

# Create a table with some data
#
execsql {CREATE TABLE tbl1(f1 int, f2 int)}
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
} {500}
do_test select2-3.2c {
  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
} {500}
do_test select2-3.2d {
  set t1 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}} 1] 0]
  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2=1000}} 1] 0]
  expr {$t1*0.9<$t2 && $t2*0.9<$t1}
} {1}

# Make sure queries run faster with an index than without
#
do_test select2-3.3 {
  set t1 [lindex [time {execsql {SELECT f1 from tbl2 WHERE f2==2000}} 1] 0]
  execsql {DROP INDEX idx1}
  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2==2000}} 1] 0]
  expr {$t1*25 < $t2}
} {1}

finish_test







|












102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
} {500}
do_test select2-3.2c {
  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
} {500}
do_test select2-3.2d {
  set t1 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}} 1] 0]
  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2=1000}} 1] 0]
  expr {$t1*0.8<$t2 && $t2*0.8<$t1}
} {1}

# Make sure queries run faster with an index than without
#
do_test select2-3.3 {
  set t1 [lindex [time {execsql {SELECT f1 from tbl2 WHERE f2==2000}} 1] 0]
  execsql {DROP INDEX idx1}
  set t2 [lindex [time {execsql {SELECT f1 FROM tbl2 WHERE f2==2000}} 1] 0]
  expr {$t1*25 < $t2}
} {1}

finish_test
Changes to test/select5.test.
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select5.test,v 1.3 2000/06/08 16:26:25 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]







|







20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select5.test,v 1.4 2000/06/21 13:59:13 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
# Some error messages associated with aggregates and GROUP BY
#
do_test select5-2.1 {
  set v [catch {execsql {
    SELECT y, count(*) FROM t1 GROUP BY z ORDER BY y
  }} msg]
  lappend v $msg
} {1 {no such field: z}}
do_test select5-2.2 {
  set v [catch {execsql {
    SELECT y, count(*) FROM t1 GROUP BY z(y) ORDER BY y
  }} msg]
  lappend v $msg
} {1 {no such function: z}}
do_test select5-2.3 {







|







62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
# Some error messages associated with aggregates and GROUP BY
#
do_test select5-2.1 {
  set v [catch {execsql {
    SELECT y, count(*) FROM t1 GROUP BY z ORDER BY y
  }} msg]
  lappend v $msg
} {1 {no such column: z}}
do_test select5-2.2 {
  set v [catch {execsql {
    SELECT y, count(*) FROM t1 GROUP BY z(y) ORDER BY y
  }} msg]
  lappend v $msg
} {1 {no such function: z}}
do_test select5-2.3 {
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
  lappend v $msg
} {1 {no such function: z}}
do_test select5-2.5 {
  set v [catch {execsql {
    SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<z ORDER BY y
  }} msg]
  lappend v $msg
} {1 {no such field: z}}

# Get the Agg function to rehash in vdbe.c
#
do_test select5-3.1 {
  execsql {
    SELECT x, count(*), avg(y) FROM t1 GROUP BY x HAVING x<4 ORDER BY x
  }







|







86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
  lappend v $msg
} {1 {no such function: z}}
do_test select5-2.5 {
  set v [catch {execsql {
    SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<z ORDER BY y
  }} msg]
  lappend v $msg
} {1 {no such column: z}}

# Get the Agg function to rehash in vdbe.c
#
do_test select5-3.1 {
  execsql {
    SELECT x, count(*), avg(y) FROM t1 GROUP BY x HAVING x<4 ORDER BY x
  }
Changes to test/update.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 UPDATE statement.
#
# $Id: update.test,v 1.3 2000/06/19 19:09:09 drh Exp $

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

# Try to update an non-existent table
#
do_test update-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 UPDATE statement.
#
# $Id: update.test,v 1.4 2000/06/21 13:59:14 drh Exp $

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

# Try to update an non-existent table
#
do_test update-1.1 {
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
  for {set i 1} {$i<=10} {incr i} {
    set sql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
    execsql $sql
  }
  execsql {SELECT * FROM test1 ORDER BY f1}
} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}

# Unknown field name in an expression
#
do_test update-3.2 {
  set v [catch {execsql {UPDATE test1 SET f1=f3*2 WHERE f2==32}} msg]
  lappend v $msg
} {1 {no such field: f3}}
do_test update-3.3 {
  set v [catch {execsql {UPDATE test1 SET f1=test2.f1*2 WHERE f2==32}} msg]
  lappend v $msg
} {1 {no such field: test2.f1}}
do_test update-3.4 {
  set v [catch {execsql {UPDATE test1 SET f3=f1*2 WHERE f2==32}} msg]
  lappend v $msg
} {1 {no such field: f3}}

# Actually do some updates
#
do_test update-3.5 {
  execsql {UPDATE test1 SET f2=f2*3}
  execsql {SELECT * FROM test1 ORDER BY f1}
} {1 6 2 12 3 24 4 48 5 96 6 192 7 384 8 768 9 1536 10 3072}







|




|



|



|







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
  for {set i 1} {$i<=10} {incr i} {
    set sql "INSERT INTO test1 VALUES($i,[expr {int(pow(2,$i))}])"
    execsql $sql
  }
  execsql {SELECT * FROM test1 ORDER BY f1}
} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}

# Unknown column name in an expression
#
do_test update-3.2 {
  set v [catch {execsql {UPDATE test1 SET f1=f3*2 WHERE f2==32}} msg]
  lappend v $msg
} {1 {no such column: f3}}
do_test update-3.3 {
  set v [catch {execsql {UPDATE test1 SET f1=test2.f1*2 WHERE f2==32}} msg]
  lappend v $msg
} {1 {no such column: test2.f1}}
do_test update-3.4 {
  set v [catch {execsql {UPDATE test1 SET f3=f1*2 WHERE f2==32}} msg]
  lappend v $msg
} {1 {no such column: f3}}

# Actually do some updates
#
do_test update-3.5 {
  execsql {UPDATE test1 SET f2=f2*3}
  execsql {SELECT * FROM test1 ORDER BY f1}
} {1 6 2 12 3 24 4 48 5 96 6 192 7 384 8 768 9 1536 10 3072}
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
# Error messages
#
do_test update-4.1 {
  set v [catch {execsql {
    UPDATE test1 SET x=11 WHERE f1=1025
  }} msg]
  lappend v $msg
} {1 {no such field: x}}
do_test update-4.2 {
  set v [catch {execsql {
    UPDATE test1 SET f1=x(11) WHERE f1=1025
  }} msg]
  lappend v $msg
} {1 {no such function: x}}
do_test update-4.3 {
  set v [catch {execsql {
    UPDATE test1 SET f1=11 WHERE x=1025
  }} msg]
  lappend v $msg
} {1 {no such field: x}}
do_test update-4.4 {
  set v [catch {execsql {
    UPDATE test1 SET f1=11 WHERE x(f1)=1025
  }} msg]
  lappend v $msg
} {1 {no such function: x}}






finish_test







|











|













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
# Error messages
#
do_test update-4.1 {
  set v [catch {execsql {
    UPDATE test1 SET x=11 WHERE f1=1025
  }} msg]
  lappend v $msg
} {1 {no such column: x}}
do_test update-4.2 {
  set v [catch {execsql {
    UPDATE test1 SET f1=x(11) WHERE f1=1025
  }} msg]
  lappend v $msg
} {1 {no such function: x}}
do_test update-4.3 {
  set v [catch {execsql {
    UPDATE test1 SET f1=11 WHERE x=1025
  }} msg]
  lappend v $msg
} {1 {no such column: x}}
do_test update-4.4 {
  set v [catch {execsql {
    UPDATE test1 SET f1=11 WHERE x(f1)=1025
  }} msg]
  lappend v $msg
} {1 {no such function: x}}






finish_test
Changes to www/c_interface.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: c_interface.tcl,v 1.4 2000/06/06 18:24:42 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</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: c_interface.tcl,v 1.5 2000/06/21 13:59:14 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
pointer to an opaque <b>sqlite</b> structure.  This pointer will
be the first argument to all subsequent SQLite function calls that
deal with the same database.  NULL is returned if the open fails
for any reason.</p>

<h2>Closing the database</h2>

<p>To close an SQLite database, just call the <b>sqlite_close()</b>
function passing it the sqlite structure pointer that was obtained
from a prior call to <b>sqlite_open</b>.

<h2>Executing SQL statements</h2>

<p>The <b>sqlite_exec()</b> function is used to process SQL statements
and queries.  This function requires 5 parameters as follows:</p>







|







84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
pointer to an opaque <b>sqlite</b> structure.  This pointer will
be the first argument to all subsequent SQLite function calls that
deal with the same database.  NULL is returned if the open fails
for any reason.</p>

<h2>Closing the database</h2>

<p>To close an SQLite database, call the <b>sqlite_close()</b>
function passing it the sqlite structure pointer that was obtained
from a prior call to <b>sqlite_open</b>.

<h2>Executing SQL statements</h2>

<p>The <b>sqlite_exec()</b> function is used to process SQL statements
and queries.  This function requires 5 parameters as follows:</p>
142
143
144
145
146
147
148
























































149
150
151
152
153
154
155
argv[i] == 0
</pre></blockquote>
<p>The names of the columns are contained in the fourth argument.</p>

<p>The callback function should normally return 0.  If the callback
function returns non-zero, the query is immediately aborted and 
<b>sqlite_exec()</b> will return SQLITE_ABORT.</p>

























































<h2>Testing for a complete SQL statement</h2>

<p>The last interface routine to SQLite is a convenience function used
to test whether or not a string forms a complete SQL statement.
If the <b>sqlite_complete()</b> function returns true when its input
is a string, then the argument forms a complete SQL statement.







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







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
argv[i] == 0
</pre></blockquote>
<p>The names of the columns are contained in the fourth argument.</p>

<p>The callback function should normally return 0.  If the callback
function returns non-zero, the query is immediately aborted and 
<b>sqlite_exec()</b> will return SQLITE_ABORT.</p>

<p>The <b>sqlite_exec()</b> function returns an integer to indicate
success or failure of the operation.  The following are possible
return values:</p>

<blockquote>
<dl>
<dt>SQLITE_OK</dt>
<dd><p>This value is returned if everything worked and there were no errors.
</p></dd>
<dt>SQLITE_INTERNAL</dt>
<dd><p>This value indicates that an internal consistency check within
the SQLite library failed.  This can only happen if there is a bug in
the SQLite library.  If you ever get an SQLITE_INTERNAL reply from
an <b>sqlite_exec()</b> call, please report the problem on the SQLite
mailing list.
</p></dd>
<dt>SQLITE_ERROR</dt>
<dd><p>This return value indicates that there was an error in the SQL
that was passed into the <b>sqlite_exec()</b>.
</p></dd>
<dt>SQLITE_PERM</dt>
<dd><p>This return value says that the access permissions on one of the
GDBM files is such that the file cannot be opened.
</p></dd>
<dt>SQLITE_ABORT</dt>
<dd><p>This value is returned if the callback function returns non-zero.
</p></dd>
<dt>SQLITE_BUSY</dt>
<dd><p>This return code indicates that one of the underlying GDBM files
is locked because it is currently being accessed by another thread or
process.  GDBM allows mutiple readers of the same file, but only one
writer.  So multiple processes can query an SQLite database at once.
But only a single process can write to an SQLite database at one time.
If an attempt is made to write to an SQLite database that another
process is currently reading, the write is not performed and 
<b>sqlite_exec()</b> returns SQLITE_BUSY.  Similarly, an attempt to read
an SQLite database that is currently being written by another process
will return SQLITE_BUSY.  In both cases, the write or query attempt
can be retried after the other process finishes.</p>
<p>Note that locking is done at the file level.  One process can
write to table ABC (for example) while another process simultaneously
reads from a different table XYZ.  But you cannot have two processes reading
and writing table ABC at the same time.
</p></dd>
<dt>SQLITE_NOMEM</dt>
<dd><p>This value is returned if a call to <b>malloc()</b> fails.
</p></dd>
<dt>SQLITE_READONLY</dt>
<dd><p>This return code indicates that an attempt was made to write to
a database file that was originally opened for reading only.  This can
happen if the callback from a query attempts to update the table
being queried.
</p></dd>
</dl>
</blockquote>

<h2>Testing for a complete SQL statement</h2>

<p>The last interface routine to SQLite is a convenience function used
to test whether or not a string forms a complete SQL statement.
If the <b>sqlite_complete()</b> function returns true when its input
is a string, then the argument forms a complete SQL statement.
Changes to www/sqlite.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: sqlite.tcl,v 1.8 2000/06/08 19:43:40 drh Exp $}

puts {<html>
<head>
  <title>sqlite: A program of interacting with SQLite databases</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: sqlite.tcl,v 1.9 2000/06/21 13:59:14 drh Exp $}

puts {<html>
<head>
  <title>sqlite: A program of interacting with SQLite databases</title>
</head>
<body bgcolor=white>
<h1 align=center>
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
.dump                  Dump database in a text format
.exit                  Exit this program
.explain               Set output mode suitable for EXPLAIN
.header ON|OFF         Turn display of headers on or off
.help                  Show this message
.indices TABLE         Show names of all indices on TABLE
.mode MODE             Set mode to one of "line", "column", "list", or "html"

.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator string for "list" mode
.tables                List names all tables in the database
.width NUM NUM ...     Set column widths for "column" mode
sqlite> 
}

puts {
<h2>Changing Output Formats</h2>

<p>The sqlite program is able to show the results of a query
in four different formats: "line", "column", "list", and "html".
You can use the ".mode" dot command to switch between these three output
formats.</p>

<p>The default output mode is "list".  In
list mode, each record of a query result is written on one line of
output and each field within that record is separated by a specific
separator string.  The default separator is a pipe symbol ("|").
List mode is especially useful when you are going to send the output
of a query to another program (such as AWK) for additional processing.</p>}

Code {
sqlite> (((.mode list)))
sqlite> (((select * from tbl1;)))







>













|





|







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
.dump                  Dump database in a text format
.exit                  Exit this program
.explain               Set output mode suitable for EXPLAIN
.header ON|OFF         Turn display of headers on or off
.help                  Show this message
.indices TABLE         Show names of all indices on TABLE
.mode MODE             Set mode to one of "line", "column", "list", or "html"
.mode insert TABLE     Generate SQL insert statements for TABLE
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator string for "list" mode
.tables                List names all tables in the database
.width NUM NUM ...     Set column widths for "column" mode
sqlite> 
}

puts {
<h2>Changing Output Formats</h2>

<p>The sqlite program is able to show the results of a query
in five different formats: "line", "column", "list", "html", and "insert".
You can use the ".mode" dot command to switch between these three output
formats.</p>

<p>The default output mode is "list".  In
list mode, each record of a query result is written on one line of
output and each column within that record is separated by a specific
separator string.  The default separator is a pipe symbol ("|").
List mode is especially useful when you are going to send the output
of a query to another program (such as AWK) for additional processing.</p>}

Code {
sqlite> (((.mode list)))
sqlite> (((select * from tbl1;)))
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
sqlite> (((select * from tbl1;)))
hello, 10
goodbye, 20
sqlite>
}

puts {
<p>In "line" mode, each field in a record of the database
is shown on a line by itself.  Each line consists of the field
name, an equal sign and the field data.  Successive records are
separated by a blank line.  Here is an example of line mode
output:</p>}

Code {
sqlite> (((.mode line)))
sqlite> (((select * from tbl1;)))
one = hello







|
|
|







193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
sqlite> (((select * from tbl1;)))
hello, 10
goodbye, 20
sqlite>
}

puts {
<p>In "line" mode, each column in a row of the database
is shown on a line by itself.  Each line consists of the column
name, an equal sign and the column data.  Successive records are
separated by a blank line.  Here is an example of line mode
output:</p>}

Code {
sqlite> (((.mode line)))
sqlite> (((select * from tbl1;)))
one = hello
259
260
261
262
263
264
265





266
267
268
269
270
271
272
sqlite> (((select * from tbl1;)))
hello         10    
goodbye       20    
sqlite>
}

puts {





<p>The last output mode is "html".  In this mode, sqlite writes
the results of the query as an XHTML table.  The beginning
&lt;TABLE&gt; and the ending &lt;/TABLE&gt; are not written, but
all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s
are.  The html output mode is envisioned as being useful for
CGI.</p>
}







>
>
>
>
>







260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
sqlite> (((select * from tbl1;)))
hello         10    
goodbye       20    
sqlite>
}

puts {
<p>Another useful output mode is "insert".  In insert mode, the output
is formatted to look like SQL INSERT statements.  You can use insert
mode to generate text that can later be used to input data into a 
different database.</p>

<p>The last output mode is "html".  In this mode, sqlite writes
the results of the query as an XHTML table.  The beginning
&lt;TABLE&gt; and the ending &lt;/TABLE&gt; are not written, but
all of the intervening &lt;TR&gt;s, &lt;TH&gt;s, and &lt;TD&gt;s
are.  The html output mode is envisioned as being useful for
CGI.</p>
}