/ Check-in [c15f6ffc]
Login

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

Overview
Comment:Add index access methods to the DBBE in preparation for adding a new DBBE for the btree.c module. (CVS 236)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:c15f6ffc4d41f30a06d750c8015226713ae0126b
User & Date: drh 2001-08-19 18:19:46
Context
2001-08-20
00:33
Restore btree to the main line. (CVS 237) check-in: 2e6aff98 user: drh tags: trunk
2001-08-19
18:19
Add index access methods to the DBBE in preparation for adding a new DBBE for the btree.c module. (CVS 236) check-in: c15f6ffc user: drh tags: trunk
2001-07-23
14:35
Version 1.0.32 (CVS 471) check-in: cfc86dc4 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/dbbe.h.

24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
...
156
157
158
159
160
161
162















163
164
165
166
167
168
169
** This file defines the interface to the database backend (Dbbe).
**
** The database backend is designed to be as general as possible
** so that it can easily be replaced by a different backend.
** This library was originally designed to support the following
** backends: GDBM, NDBM, SDBM, Berkeley DB.
**
** $Id: dbbe.h,v 1.13 2001/04/04 11:48:57 drh Exp $
*/
#ifndef _SQLITE_DBBE_H_
#define _SQLITE_DBBE_H_
#include <stdio.h>

/*
** The database backend supports two opaque structures.  A Dbbe is
................................................................................
  int (*BeginTransaction)(Dbbe*);

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

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















};

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







|







 







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







24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
...
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
** 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.14 2001/08/19 18:19:46 drh Exp $
*/
#ifndef _SQLITE_DBBE_H_
#define _SQLITE_DBBE_H_
#include <stdio.h>

/*
** The database backend supports two opaque structures.  A Dbbe is
................................................................................
  int (*BeginTransaction)(Dbbe*);

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

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

  /* Begin searching an index where the key is given. */
  int (*BeginIndex)(DbbeCursor*, int nKey, char *pKey);

  /* Return the integer key for the next index entry, or return 0 if
  ** there are no more index entries. */
  int (*NextIndex)(DbbeCursor*);

  /* Add a new index entry to the file.  The key and record number are
  ** given. */
  int (*PutIndex)(DbbeCursor*, int nKey, char *pKey, int recno);

  /* Delete an index entry from the file.  The key and record number are
  ** given. */
  int (*DeleteIndex)(DbbeCursor*, int nKey, char *pKey, int recno);
};

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

Changes to src/dbbegdbm.c.

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
..
83
84
85
86
87
88
89

90
91
92
93
94
95
96
...
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
...
582
583
584
585
586
587
588








589





































































































590
591
592
593
594
595
596
...
610
611
612
613
614
615
616




617
618
619
620
621
622
623
** sqlite and the code that does the actually reading and writing
** of information to the disk.
**
** This file uses GDBM as the database backend.  It should be
** relatively simple to convert to a different database such
** as NDBM, SDBM, or BerkeleyDB.
**
** $Id: dbbegdbm.c,v 1.8 2001/04/28 16:52:41 drh Exp $
*/
#ifndef DISABLE_GDBM
#include "sqliteInt.h"
#include <gdbm.h>
#include <sys/stat.h>
#include <unistd.h>
#include <ctype.h>
................................................................................
** associated with the same disk file.
*/
struct DbbeCursor {
  Dbbex *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 */
};

/*
** The "mkdir()" function only takes one argument under Windows.
*/
................................................................................
  sqliteFree(pCursr);
}

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

  rc = sqliteGdbmOpenCursor(pBe, zTable, 1, 0, &pCrsr);
  if( rc!=SQLITE_OK ){
    return rc;
  }
  if( pCrsr && pCrsr->pFile && pCrsr->pFile->dbf ){
    gdbm_reorganize(pCrsr->pFile->dbf);
  }
  if( pCrsr ){
    sqliteGdbmCloseCursor(pCrsr);
  }
  return SQLITE_OK;
}

/*
** Clear the given datum
*/
................................................................................
      sqliteUnlinkFile(pBe, pFile);
    }
  }
  pBe->inTrans = 0;
  return SQLITE_OK;  
}
















































































































/*
** This variable contains pointers to all of the access methods
** used to implement the GDBM backend.
*/
static struct DbbeMethods gdbmMethods = {
  /*           Close */   sqliteGdbmClose,
................................................................................
  /*          Rewind */   sqliteGdbmRewind,
  /*             New */   sqliteGdbmNew,
  /*             Put */   sqliteGdbmPut,
  /*          Delete */   sqliteGdbmDelete,
  /*      BeginTrans */   sqliteGdbmBeginTrans,
  /*          Commit */   sqliteGdbmEndTrans,
  /*        Rollback */   sqliteGdbmEndTrans,




};


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







|







 







>







 







|


|



|
|

|
|







 







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







 







>
>
>
>







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
..
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
...
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
...
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
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
...
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
** sqlite and the code that does the actually reading and writing
** of information to the disk.
**
** This file uses GDBM as the database backend.  It should be
** relatively simple to convert to a different database such
** as NDBM, SDBM, or BerkeleyDB.
**
** $Id: dbbegdbm.c,v 1.9 2001/08/19 18:19:46 drh Exp $
*/
#ifndef DISABLE_GDBM
#include "sqliteInt.h"
#include <gdbm.h>
#include <sys/stat.h>
#include <unistd.h>
#include <ctype.h>
................................................................................
** associated with the same disk file.
*/
struct DbbeCursor {
  Dbbex *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 nextIndex;     /* Next index entry to search */
  int needRewind;    /* Next key should be the first */
  int readPending;   /* The fetch hasn't actually been done yet */
};

/*
** The "mkdir()" function only takes one argument under Windows.
*/
................................................................................
  sqliteFree(pCursr);
}

/*
** Reorganize a table to reduce search times and disk usage.
*/
static int sqliteGdbmReorganizeTable(Dbbe *pBe, const char *zTable){
  DbbeCursor *pCursr;
  int rc;

  rc = sqliteGdbmOpenCursor(pBe, zTable, 1, 0, &pCursr);
  if( rc!=SQLITE_OK ){
    return rc;
  }
  if( pCursr && pCursr->pFile && pCursr->pFile->dbf ){
    gdbm_reorganize(pCursr->pFile->dbf);
  }
  if( pCursr ){
    sqliteGdbmCloseCursor(pCursr);
  }
  return SQLITE_OK;
}

/*
** Clear the given datum
*/
................................................................................
      sqliteUnlinkFile(pBe, pFile);
    }
  }
  pBe->inTrans = 0;
  return SQLITE_OK;  
}

/*
** Begin scanning an index for the given key.  Return 1 on success and
** 0 on failure.
*/
static int sqliteGdbmBeginIndex(DbbeCursor *pCursr, int nKey, char *pKey){
  if( !sqliteGdbmFetch(pCursr, nKey, pKey) ) return 0;
  pCursr->nextIndex = 0;
  return 1;
}

/*
** Return an integer key which is the next record number in the index search
** that was started by a prior call to BeginIndex.  Return 0 if all records
** have already been searched.
*/
static int sqliteGdbmNextIndex(DbbeCursor *pCursr){
  int *aIdx;
  int nIdx;
  int k;
  nIdx = pCursr->data.dsize/sizeof(int);
  aIdx = (int*)pCursr->data.dptr;
  if( nIdx>1 ){
    k = *(aIdx++);
    if( k>nIdx-1 ) k = nIdx-1;
  }else{
    k = nIdx;
  }
  while( pCursr->nextIndex < k ){
    int recno = aIdx[pCursr->nextIndex++];
    if( recno!=0 ) return recno;
  }
  pCursr->nextIndex = 0;
  return 0;
}

/*
** Write a new record number and key into an index table.  Return a status
** code.
*/
static int sqliteGdbmPutIndex(DbbeCursor *pCursr, int nKey, char *pKey, int N){
  int r = sqliteGdbmFetch(pCursr, nKey, pKey);
  if( r==0 ){
    /* Create a new record for this index */
    sqliteGdbmPut(pCursr, nKey, pKey, sizeof(int), (char*)&N);
  }else{
    /* Extend the existing record */
    int nIdx;
    int *aIdx;
    int k;
            
    nIdx = pCursr->data.dsize/sizeof(int);
    if( nIdx==1 ){
      aIdx = sqliteMalloc( sizeof(int)*4 );
      if( aIdx==0 ) return SQLITE_NOMEM;
      aIdx[0] = 2;
      sqliteGdbmCopyData(pCursr, 0, sizeof(int), (char*)&aIdx[1]);
      aIdx[2] = N;
      sqliteGdbmPut(pCursr, nKey, pKey, sizeof(int)*4, (char*)aIdx);
      sqliteFree(aIdx);
    }else{
      aIdx = (int*)sqliteGdbmReadData(pCursr, 0);
      k = aIdx[0];
      if( k<nIdx-1 ){
        aIdx[k+1] = N;
        aIdx[0]++;
        sqliteGdbmPut(pCursr, nKey, pKey, sizeof(int)*nIdx, (char*)aIdx);
      }else{
        nIdx *= 2;
        aIdx = sqliteMalloc( sizeof(int)*nIdx );
        if( aIdx==0 ) return SQLITE_NOMEM;
        sqliteGdbmCopyData(pCursr, 0, sizeof(int)*(k+1), (char*)aIdx);
        aIdx[k+1] = N;
        aIdx[0]++;
        sqliteGdbmPut(pCursr, nKey, pKey, sizeof(int)*nIdx, (char*)aIdx);
        sqliteFree(aIdx);
      }
    }
  }
  return SQLITE_OK;
}

/*
** Delete an index entry.  Return a status code.
*/
static 
int sqliteGdbmDeleteIndex(DbbeCursor *pCursr, int nKey, char *pKey, int N){
  int *aIdx;
  int nIdx;
  int j, k;
  int rc;
  rc = sqliteGdbmFetch(pCursr, nKey, pKey);
  if( !rc ) return SQLITE_OK;
  nIdx = pCursr->data.dsize/sizeof(int);
  aIdx = (int*)sqliteGdbmReadData(pCursr, 0);
  if( (nIdx==1 && aIdx[0]==N) || (aIdx[0]==1 && aIdx[1]==N) ){
    sqliteGdbmDelete(pCursr, nKey, pKey);
  }else{
    k = aIdx[0];
    for(j=1; j<=k && aIdx[j]!=N; j++){}
    if( j>k ) return SQLITE_OK;
    aIdx[j] = aIdx[k];
    aIdx[k] = 0;
    aIdx[0]--;
    if( aIdx[0]*3 + 1 < nIdx ){
      nIdx /= 2;
    }
    sqliteGdbmPut(pCursr, nKey, pKey, sizeof(int)*nIdx, (char*)aIdx);
  }
  return SQLITE_OK;
}

/*
** This variable contains pointers to all of the access methods
** used to implement the GDBM backend.
*/
static struct DbbeMethods gdbmMethods = {
  /*           Close */   sqliteGdbmClose,
................................................................................
  /*          Rewind */   sqliteGdbmRewind,
  /*             New */   sqliteGdbmNew,
  /*             Put */   sqliteGdbmPut,
  /*          Delete */   sqliteGdbmDelete,
  /*      BeginTrans */   sqliteGdbmBeginTrans,
  /*          Commit */   sqliteGdbmEndTrans,
  /*        Rollback */   sqliteGdbmEndTrans,
  /*      BeginIndex */   sqliteGdbmBeginIndex,
  /*       NextIndex */   sqliteGdbmNextIndex,
  /*        PutIndex */   sqliteGdbmPutIndex,
  /*     DeleteIndex */   sqliteGdbmDeleteIndex,
};


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

Changes to src/dbbemem.c.

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
...
367
368
369
370
371
372
373

374
375
376
377
378
379
380
...
717
718
719
720
721
722
723















































































































724
725
726
727
728
729
730
...
741
742
743
744
745
746
747







748
749
750
751
752
753
754
** sqlite and the code that does the actually reading and writing
** of information to the disk.
**
** This file uses an in-memory hash table as the database backend. 
** Nothing is ever written to disk using this backend.  All information
** is forgotten when the program exits.
**
** $Id: dbbemem.c,v 1.15 2001/04/28 16:52:42 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>


typedef struct Array Array;
typedef struct ArrayElem ArrayElem;
................................................................................
** associated with the same disk file.
*/
struct DbbeCursor {
  Dbbex *pBe;        /* The database of which this record is a part */
  MTable *pTble;     /* The database file for this table */
  ArrayElem *elem;   /* Most recently accessed record */
  int needRewind;    /* Next key should be the first */

};

/*
** Forward declaration
*/
static void sqliteMemCloseCursor(DbbeCursor *pCursr);

................................................................................
  data.n = 0;
  data = ArrayInsert(&pCursr->pTble->data, key, data);
  if( data.p ){
    sqliteFree(data.p);
  }
  return SQLITE_OK;
}
















































































































/*
** This variable contains pointers to all of the access methods
** used to implement the MEMORY backend.
*/
static struct DbbeMethods memoryMethods = {
  /*           Close */   sqliteMemClose,
................................................................................
  /*       KeyLength */   sqliteMemKeyLength,
  /*      DataLength */   sqliteMemDataLength,
  /*         NextKey */   sqliteMemNextKey,
  /*          Rewind */   sqliteMemRewind,
  /*             New */   sqliteMemNew,
  /*             Put */   sqliteMemPut,
  /*          Delete */   sqliteMemDelete,







};

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







|







 







>







 







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







 







>
>
>
>
>
>
>







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
...
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
...
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
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
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
...
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
** sqlite and the code that does the actually reading and writing
** of information to the disk.
**
** This file uses an in-memory hash table as the database backend. 
** Nothing is ever written to disk using this backend.  All information
** is forgotten when the program exits.
**
** $Id: dbbemem.c,v 1.16 2001/08/19 18:19:46 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>


typedef struct Array Array;
typedef struct ArrayElem ArrayElem;
................................................................................
** associated with the same disk file.
*/
struct DbbeCursor {
  Dbbex *pBe;        /* The database of which this record is a part */
  MTable *pTble;     /* The database file for this table */
  ArrayElem *elem;   /* Most recently accessed record */
  int needRewind;    /* Next key should be the first */
  int nextIndex;     /* Next recno in an index entry */
};

/*
** Forward declaration
*/
static void sqliteMemCloseCursor(DbbeCursor *pCursr);

................................................................................
  data.n = 0;
  data = ArrayInsert(&pCursr->pTble->data, key, data);
  if( data.p ){
    sqliteFree(data.p);
  }
  return SQLITE_OK;
}

/*
** Begin scanning an index for the given key.  Return 1 on success and
** 0 on failure.
*/
static int sqliteMemBeginIndex(DbbeCursor *pCursr, int nKey, char *pKey){
  if( !sqliteMemFetch(pCursr, nKey, pKey) ) return 0;
  pCursr->nextIndex = 0;
  return 1;
}

/*
** Return an integer key which is the next record number in the index search
** that was started by a prior call to BeginIndex.  Return 0 if all records
** have already been searched.
*/
static int sqliteMemNextIndex(DbbeCursor *pCursr){
  int *aIdx;
  int nIdx;
  int k;
  nIdx = sqliteMemDataLength(pCursr)/sizeof(int);
  aIdx = (int*)sqliteMemReadData(pCursr, 0);
  if( nIdx>1 ){
    k = *(aIdx++);
    if( k>nIdx-1 ) k = nIdx-1;
  }else{
    k = nIdx;
  }
  while( pCursr->nextIndex < k ){
    int recno = aIdx[pCursr->nextIndex++];
    if( recno!=0 ) return recno;
  }
  pCursr->nextIndex = 0;
  return 0;
}

/*
** Write a new record number and key into an index table.  Return a status
** code.
*/
static int sqliteMemPutIndex(DbbeCursor *pCursr, int nKey, char *pKey, int N){
  int r = sqliteMemFetch(pCursr, nKey, pKey);
  if( r==0 ){
    /* Create a new record for this index */
    sqliteMemPut(pCursr, nKey, pKey, sizeof(int), (char*)&N);
  }else{
    /* Extend the existing record */
    int nIdx;
    int *aIdx;
    int k;
            
    nIdx = sqliteMemDataLength(pCursr)/sizeof(int);
    if( nIdx==1 ){
      aIdx = sqliteMalloc( sizeof(int)*4 );
      if( aIdx==0 ) return SQLITE_NOMEM;
      aIdx[0] = 2;
      sqliteMemCopyData(pCursr, 0, sizeof(int), (char*)&aIdx[1]);
      aIdx[2] = N;
      sqliteMemPut(pCursr, nKey, pKey, sizeof(int)*4, (char*)aIdx);
      sqliteFree(aIdx);
    }else{
      aIdx = (int*)sqliteMemReadData(pCursr, 0);
      k = aIdx[0];
      if( k<nIdx-1 ){
        aIdx[k+1] = N;
        aIdx[0]++;
        sqliteMemPut(pCursr, nKey, pKey, sizeof(int)*nIdx, (char*)aIdx);
      }else{
        nIdx *= 2;
        aIdx = sqliteMalloc( sizeof(int)*nIdx );
        if( aIdx==0 ) return SQLITE_NOMEM;
        sqliteMemCopyData(pCursr, 0, sizeof(int)*(k+1), (char*)aIdx);
        aIdx[k+1] = N;
        aIdx[0]++;
        sqliteMemPut(pCursr, nKey, pKey, sizeof(int)*nIdx, (char*)aIdx);
        sqliteFree(aIdx);
      }
    }
  }
  return SQLITE_OK;
}

/*
** Delete an index entry.  Return a status code.
*/
static int sqliteMemDeleteIndex(DbbeCursor *pCursr,int nKey,char *pKey, int N){
  int *aIdx;
  int nIdx;
  int j, k;
  int rc;
  rc = sqliteMemFetch(pCursr, nKey, pKey);
  if( !rc ) return SQLITE_OK;
  nIdx = sqliteMemDataLength(pCursr)/sizeof(int);
  if( nIdx==0 ) return SQLITE_OK;
  aIdx = (int*)sqliteMemReadData(pCursr, 0);
  if( (nIdx==1 && aIdx[0]==N) || (aIdx[0]==1 && aIdx[1]==N) ){
    sqliteMemDelete(pCursr, nKey, pKey);
  }else{
    k = aIdx[0];
    for(j=1; j<=k && aIdx[j]!=N; j++){}
    if( j>k ) return SQLITE_OK;
    aIdx[j] = aIdx[k];
    aIdx[k] = 0;
    aIdx[0]--;
    if( aIdx[0]*3 + 1 < nIdx ){
      nIdx /= 2;
    }
    sqliteMemPut(pCursr, nKey, pKey, sizeof(int)*nIdx, (char*)aIdx);
  }
  return SQLITE_OK;
}

/*
** This variable contains pointers to all of the access methods
** used to implement the MEMORY backend.
*/
static struct DbbeMethods memoryMethods = {
  /*           Close */   sqliteMemClose,
................................................................................
  /*       KeyLength */   sqliteMemKeyLength,
  /*      DataLength */   sqliteMemDataLength,
  /*         NextKey */   sqliteMemNextKey,
  /*          Rewind */   sqliteMemRewind,
  /*             New */   sqliteMemNew,
  /*             Put */   sqliteMemPut,
  /*          Delete */   sqliteMemDelete,
  /*      BeginTrans */   0,
  /*          Commit */   0,
  /*        Rollback */   0,
  /*      BeginIndex */   sqliteMemBeginIndex,
  /*       NextIndex */   sqliteMemNextIndex,
  /*        PutIndex */   sqliteMemPutIndex,
  /*     DeleteIndex */   sqliteMemDeleteIndex,
};

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

Changes to src/vdbe.c.

37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
...
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
....
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256


2257


2258
2259

2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307

2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
** 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.58 2001/04/28 16:52:42 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** SQL is translated into a sequence of instructions to be
** executed by a virtual machine.  Each instruction is an instance
................................................................................
** this array, then copy and paste it into this file, if you want.
*/
static char *zOpName[] = { 0,
  "OpenIdx",           "OpenTbl",           "Close",             "Fetch",
  "Fcnt",              "New",               "Put",               "Distinct",
  "Found",             "NotFound",          "Delete",            "Field",
  "KeyAsData",         "Key",               "FullKey",           "Rewind",
  "Next",              "Destroy",           "Reorganize",        "ResetIdx",
  "NextIdx",           "PutIdx",            "DeleteIdx",         "MemLoad",
  "MemStore",          "ListOpen",          "ListWrite",         "ListRewind",
  "ListRead",          "ListClose",         "SortOpen",          "SortPut",
  "SortMakeRec",       "SortMakeKey",       "Sort",              "SortNext",
  "SortKey",           "SortCallback",      "SortClose",         "FileOpen",
  "FileRead",          "FileField",         "FileClose",         "AggReset",
  "AggFocus",          "AggIncr",           "AggNext",           "AggSet",
................................................................................
            p->nFetch++;
          }
        }
        p->aCsr[i].keyIsValid = 0;
        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;

        VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
        zStack[tos] = 0;
        if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
          int *aIdx;
          int nIdx;
          int j, k;
          nIdx = pBex->DataLength(pCrsr)/sizeof(int);
          aIdx = (int*)pBex->ReadData(pCrsr, 0);
          if( nIdx>1 ){
            k = *(aIdx++);
            if( k>nIdx-1 ) k = nIdx-1;
          }else{
            k = nIdx;
          }
          p->aCsr[i].keyIsValid = 0;
          for(j=p->aCsr[i].index; j<k; j++){
            if( aIdx[j]!=0 ){
              aStack[tos].i = p->aCsr[i].lastKey = aIdx[j];
              p->aCsr[i].keyIsValid = 1;
              aStack[tos].flags = STK_Int;
              break;
            }
          }
          if( j>=k ){
            j = -1;

            pc = pOp->p2 - 1;
            POPSTACK;
          }
          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;
        VERIFY( if( nos<0 ) goto not_enough_stack; )
        if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
          int r;
          int newVal;
          Integerify(p, nos);
          newVal = aStack[nos].i;
          if( Stringify(p, tos) ) goto no_mem;
          r = pBex->Fetch(pCrsr, aStack[tos].n, zStack[tos]);
          if( r==0 ){
            /* Create a new record for this index */
            pBex->Put(pCrsr, aStack[tos].n, zStack[tos],
                          sizeof(int), (char*)&newVal);
          }else{
            /* Extend the existing record */
            int nIdx;
            int *aIdx;
            int k;
            
            nIdx = pBex->DataLength(pCrsr)/sizeof(int);
            if( nIdx==1 ){
              aIdx = sqliteMalloc( sizeof(int)*4 );
              if( aIdx==0 ) goto no_mem;
              aIdx[0] = 2;
              pBex->CopyData(pCrsr, 0, sizeof(int), (char*)&aIdx[1]);
              aIdx[2] = newVal;
              pBex->Put(pCrsr, aStack[tos].n, zStack[tos],
                    sizeof(int)*4, (char*)aIdx);
              sqliteFree(aIdx);
            }else{
              aIdx = (int*)pBex->ReadData(pCrsr, 0);
              k = aIdx[0];
              if( k<nIdx-1 ){
                aIdx[k+1] = newVal;
                aIdx[0]++;
                pBex->Put(pCrsr, aStack[tos].n, zStack[tos],
                    sizeof(int)*nIdx, (char*)aIdx);
              }else{
                nIdx *= 2;
                aIdx = sqliteMalloc( sizeof(int)*nIdx );
                if( aIdx==0 ) goto no_mem;
                pBex->CopyData(pCrsr, 0, sizeof(int)*(k+1), (char*)aIdx);
                aIdx[k+1] = newVal;
                aIdx[0]++;
                pBex->Put(pCrsr, aStack[tos].n, zStack[tos],
                      sizeof(int)*nIdx, (char*)aIdx);
                sqliteFree(aIdx);
              }
            }              
          }
        }
        POPSTACK;
        POPSTACK;
        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;
        VERIFY( if( nos<0 ) goto not_enough_stack; )
        if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
          int *aIdx;
          int nIdx;
          int j, k;
          int r;
          int oldVal;
          Integerify(p, nos);
          oldVal = aStack[nos].i;
          if( Stringify(p, tos) ) goto no_mem;
          r = pBex->Fetch(pCrsr, aStack[tos].n, zStack[tos]);
          if( r==0 ) break;
          nIdx = pBex->DataLength(pCrsr)/sizeof(int);
          aIdx = (int*)pBex->ReadData(pCrsr, 0);
          if( (nIdx==1 && aIdx[0]==oldVal) || (aIdx[0]==1 && aIdx[1]==oldVal) ){
            pBex->Delete(pCrsr, aStack[tos].n, zStack[tos]);
          }else{
            k = aIdx[0];
            for(j=1; j<=k && aIdx[j]!=oldVal; j++){}
            if( j>k ) break;
            aIdx[j] = aIdx[k];
            aIdx[k] = 0;
            aIdx[0]--;
            if( aIdx[0]*3 + 1 < nIdx ){
              nIdx /= 2;
            }
            pBex->Put(pCrsr, aStack[tos].n, zStack[tos], 
                          sizeof(int)*nIdx, (char*)aIdx);
          }
        }
        POPSTACK;
        POPSTACK;
        break;
      }

      /* Opcode: Destroy * * P3







|







 







|







 







|

|
|
|
|

|

>
>
|
>
>
|

>





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









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



<








|
|
|
<








<
<

<

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









|
|
|
<
<
<
<
<
<








<
<
<
<
<

<

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







37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
...
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
....
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273







2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284












2285
2286
2287
2288





2289
2290
2291
2292

2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303

2304
2305
2306
2307
2308
2309
2310
2311


2312

2313



2314






































2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326






2327
2328
2329
2330
2331
2332
2333
2334





2335

2336
















2337


2338
2339
2340
2341
2342
2343
2344
** 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.59 2001/08/19 18:19:46 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** SQL is translated into a sequence of instructions to be
** executed by a virtual machine.  Each instruction is an instance
................................................................................
** this array, then copy and paste it into this file, if you want.
*/
static char *zOpName[] = { 0,
  "OpenIdx",           "OpenTbl",           "Close",             "Fetch",
  "Fcnt",              "New",               "Put",               "Distinct",
  "Found",             "NotFound",          "Delete",            "Field",
  "KeyAsData",         "Key",               "FullKey",           "Rewind",
  "Next",              "Destroy",           "Reorganize",        "BeginIdx",
  "NextIdx",           "PutIdx",            "DeleteIdx",         "MemLoad",
  "MemStore",          "ListOpen",          "ListWrite",         "ListRewind",
  "ListRead",          "ListClose",         "SortOpen",          "SortPut",
  "SortMakeRec",       "SortMakeKey",       "Sort",              "SortNext",
  "SortKey",           "SortCallback",      "SortClose",         "FileOpen",
  "FileRead",          "FileField",         "FileClose",         "AggReset",
  "AggFocus",          "AggIncr",           "AggNext",           "AggSet",
................................................................................
            p->nFetch++;
          }
        }
        p->aCsr[i].keyIsValid = 0;
        break;
      }

      /* Opcode: BeginIdx P1 * *
      **
      ** Begin searching an index for records with the key found on the
      ** top of the stack.  The stack is popped once.  Subsequent calls
      ** to NextIdx will push record numbers onto the stack until all
      ** records with the same key have been returned.
      */
      case OP_BeginIdx: {
        int i = pOp->p1;
        int tos = p->tos;
        VERIFY( if( tos<0 ) goto not_enough_stack; )
        if( i>=0 && i<p->nCursor && p->aCsr[i].pCursor ){
          if( Stringify(p, tos) ) goto no_mem;
          pBex->BeginIndex(p->aCsr[i].pCursor, aStack[tos].n, zStack[tos]);
          p->aCsr[i].keyIsValid = 0;
        }
        POPSTACK;
        break;
      }

      /* Opcode: NextIdx P1 P2 *
      **
      ** The P1 cursor points to an SQL index for which a BeginIdx operation
      ** has been issued.  This operation retrieves the next record number and
      ** pushes that record number onto the stack.  Or, if there are no more
      ** record numbers for the given key, this opcode pushes nothing onto the







      ** stack but instead jumps to instruction P2.
      */
      case OP_NextIdx: {
        int i = pOp->p1;
        int tos = ++p->tos;
        DbbeCursor *pCrsr;

        VERIFY( if( NeedStack(p, p->tos) ) goto no_mem; )
        zStack[tos] = 0;
        if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){
          int recno = pBex->NextIndex(pCrsr);












          if( recno!=0 ){
            p->aCsr[i].lastKey = aStack[tos].i = recno;
            p->aCsr[i].keyIsValid = 1;
            aStack[tos].flags = STK_Int;





          }else{
            pc = pOp->p2 - 1;
            POPSTACK;
          }

        }
        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 record number for an SQL table entry.  This opcode makes an entry
      ** in the index table P1 that associates the key with the record number.
      ** But the record number and the key are popped from the stack.

      */
      case OP_PutIdx: {
        int i = pOp->p1;
        int tos = p->tos;
        int nos = tos - 1;
        DbbeCursor *pCrsr;
        VERIFY( if( nos<0 ) goto not_enough_stack; )
        if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){


          Integerify(p, nos);

          if( Stringify(p, tos) ) goto no_mem;



          pBex->PutIndex(pCrsr, aStack[tos].n, zStack[tos], aStack[nos].i);






































        }
        POPSTACK;
        POPSTACK;
        break;
      }

      /* Opcode: DeleteIdx P1 * *
      **
      ** The top of the stack is a key and next on stack is integer
      ** which is a record number for an SQL table.  The operation removes
      ** any entry to the index table P1 that associates the key with the
      ** record number.






      */
      case OP_DeleteIdx: {
        int i = pOp->p1;
        int tos = p->tos;
        int nos = tos - 1;
        DbbeCursor *pCrsr;
        VERIFY( if( nos<0 ) goto not_enough_stack; )
        if( VERIFY( i>=0 && i<p->nCursor && ) (pCrsr = p->aCsr[i].pCursor)!=0 ){





          Integerify(p, nos);

          if( Stringify(p, tos) ) goto no_mem;
















          pBex->DeleteIndex(pCrsr, aStack[tos].n, zStack[tos], aStack[nos].i);


        }
        POPSTACK;
        POPSTACK;
        break;
      }

      /* Opcode: Destroy * * P3

Changes to src/vdbe.h.

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

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines
................................................................................
#define OP_FullKey            15
#define OP_Rewind             16
#define OP_Next               17

#define OP_Destroy            18
#define OP_Reorganize         19

#define OP_ResetIdx           20
#define OP_NextIdx            21
#define OP_PutIdx             22
#define OP_DeleteIdx          23

#define OP_MemLoad            24
#define OP_MemStore           25








|







 







|







23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
..
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
*************************************************************************
** Header file for the Virtual DataBase Engine (VDBE)
**
** This header defines the interface to the virtual database engine
** or VDBE.  The VDBE implements an abstract machine that runs a
** simple program to access and modify the underlying database.
**
** $Id: vdbe.h,v 1.18 2001/08/19 18:19:46 drh Exp $
*/
#ifndef _SQLITE_VDBE_H_
#define _SQLITE_VDBE_H_
#include <stdio.h>

/*
** A single VDBE is an opaque structure named "Vdbe".  Only routines
................................................................................
#define OP_FullKey            15
#define OP_Rewind             16
#define OP_Next               17

#define OP_Destroy            18
#define OP_Reorganize         19

#define OP_BeginIdx           20
#define OP_NextIdx            21
#define OP_PutIdx             22
#define OP_DeleteIdx          23

#define OP_MemLoad            24
#define OP_MemStore           25

Changes to src/where.c.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
**   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.14 2001/04/11 14:28:43 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.
................................................................................
            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, base+idx, 0, 0, 0);
        haveKey = 0;
      }







|







 







|







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
**   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.15 2001/08/19 18:19:46 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.
................................................................................
            sqliteExprCode(pParse, aExpr[k].p->pLeft);
            aExpr[k].p = 0;
            break;
          }
        }
      }
      sqliteVdbeAddOp(v, OP_MakeKey, pIdx->nColumn, 0, 0, 0);
      sqliteVdbeAddOp(v, OP_BeginIdx, 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, base+idx, 0, 0, 0);
        haveKey = 0;
      }

Changes to test/index.test.

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

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
................................................................................
      PRIMARY KEY(b)
    );
  }
  for {set i 1} {$i<=50} {incr i} {
    execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
  }
  execsql {SELECT c, fcnt() FROM t3 WHERE b==10}
} {0.10 2}

finish_test







|







 







|


19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
...
345
346
347
348
349
350
351
352
353
354
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the CREATE INDEX statement.
#
# $Id: index.test,v 1.10 2001/08/19 18:19:46 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
................................................................................
      PRIMARY KEY(b)
    );
  }
  for {set i 1} {$i<=50} {incr i} {
    execsql "INSERT INTO t3 VALUES('x${i}x',$i,0.$i)"
  }
  execsql {SELECT c, fcnt() FROM t3 WHERE b==10}
} {0.10 1}

finish_test

Changes to test/rowid.test.

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
...
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
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.1 2001/04/04 11:48:58 drh Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {
................................................................................
} {256}
do_test rowid-4.5 {
  execsql {CREATE INDEX idxt2 ON t2(y)}
  execsql {
    SELECT t1.x, fcnt() FROM t2, t1 
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }
} {4 3}
do_test rowid-4.5.1 {
  execsql {
    SELECT t1.x, fcnt() FROM t2, t1 
    WHERE t1.OID==t2.rowid AND t2.y==81
  }
} {3 3}
do_test rowid-4.6 {
  execsql {
    SELECT t1.x FROM t1, t2
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }
} {4}

do_test rowid-5.1 {
  execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
  execsql {SELECT max(x) FROM t1}
} {8}







|







 







|





|











20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
...
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
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.2 2001/08/19 18:19:46 drh Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {
................................................................................
} {256}
do_test rowid-4.5 {
  execsql {CREATE INDEX idxt2 ON t2(y)}
  execsql {
    SELECT t1.x, fcnt() FROM t2, t1 
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }
} {4 2}
do_test rowid-4.5.1 {
  execsql {
    SELECT t1.x, fcnt() FROM t2, t1 
    WHERE t1.OID==t2.rowid AND t2.y==81
  }
} {3 2}
do_test rowid-4.6 {
  execsql {
    SELECT t1.x FROM t1, t2
    WHERE t2.y==256 AND t1.rowid==t2.rowid
  }
} {4}

do_test rowid-5.1 {
  execsql {DELETE FROM t1 WHERE _rowid_ IN (SELECT oid FROM t1 WHERE x>8)}
  execsql {SELECT max(x) FROM t1}
} {8}

Changes to test/select2.test.

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
...
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
#   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.10 2001/03/15 18:21:22 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)}
................................................................................
  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
} {500}
do_test select2-3.2c {
  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
} {500}
do_test select2-3.2d {
  execsql {SELECT fcnt() FROM tbl2 WHERE 1000=f2}
} {2}
do_test select2-3.2e {
  execsql {SELECT fcnt() FROM tbl2 WHERE f2=1000}
} {2}

# omit the time-dependent tests
#
testif gdbm:
do_probtest select2-3.2f {
  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]







|







 







|


|







19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
...
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
#   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.11 2001/08/19 18:19:46 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)}
................................................................................
  execsql {SELECT f1 FROM tbl2 WHERE 1000=f2}
} {500}
do_test select2-3.2c {
  execsql {SELECT f1 FROM tbl2 WHERE f2=1000}
} {500}
do_test select2-3.2d {
  execsql {SELECT fcnt() FROM tbl2 WHERE 1000=f2}
} {1}
do_test select2-3.2e {
  execsql {SELECT fcnt() FROM tbl2 WHERE f2=1000}
} {1}

# omit the time-dependent tests
#
testif gdbm:
do_probtest select2-3.2f {
  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]

Changes to test/where.test.

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
..
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.1 2000/06/12 12:20:49 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
# function to verify the results.  fcnt(*) returns the number of Fetch
# operations that have occurred up to the point where fcnt(*) is invoked.
# By verifing that fcnt(*) returns a small number we know that an index
# was used instead of an exhaustive search.
#
do_test where-1.1 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=10}
} {3 121 2}
do_test where-1.2 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=11}
} {3 144 2}
do_test where-1.3 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE 11=w}
} {3 144 2}
do_test where-1.4 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE 11=w AND x>2}
} {3 144 2}
do_test where-1.5 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 2}
do_test where-1.6 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 2}
do_test where-1.7 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 2}
do_test where-1.8 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 2}
do_test where-1.9 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y=144 AND w>10 AND x=3}
} {3 144 2}
do_test where-1.10 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 2}
do_test where-1.11 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 2}

# Do the same kind of thing except use a join as the data source.
#
do_test where-2.1 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND y=s AND r=8977
  }
} {34 67 4}
do_test where-2.2 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND s=y AND r=8977
  }
} {34 67 4}
do_test where-2.3 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 4}
do_test where-2.4 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 4}
do_test where-2.5 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
  }
} {34 67 4}
do_test where-2.6 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND p=77 AND s=y AND w>5
  }
} {24 77 4}
do_test where-2.7 {
  execsql {
    SELECT w, p, fcnt(*) FROM t1, t2
    WHERE x=q AND p>77 AND s=y AND w=5
  }
} {5 96 4}

# Lets do a 3-way join.
#
do_test where-3.1 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
  }
} {11 90 11 6}
do_test where-3.2 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
  }
} {12 89 12 6}
do_test where-3.3 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
  }
} {15 86 86 6}

finish_test







|







 







|


|


|


|


|


|


|


|


|


|


|








|





|





|





|





|





|





|








|





|





|


19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
..
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
#   drh@hwaci.com
#   http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.2 2001/08/19 18:19:46 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
# function to verify the results.  fcnt(*) returns the number of Fetch
# operations that have occurred up to the point where fcnt(*) is invoked.
# By verifing that fcnt(*) returns a small number we know that an index
# was used instead of an exhaustive search.
#
do_test where-1.1 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=10}
} {3 121 1}
do_test where-1.2 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=11}
} {3 144 1}
do_test where-1.3 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE 11=w}
} {3 144 1}
do_test where-1.4 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE 11=w AND x>2}
} {3 144 1}
do_test where-1.5 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 1}
do_test where-1.6 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y<200 AND x>2 AND w=11}
} {3 144 1}
do_test where-1.7 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w=11 AND y<200 AND x>2}
} {3 144 1}
do_test where-1.8 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE w>10 AND y=144 AND x=3}
} {3 144 1}
do_test where-1.9 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE y=144 AND w>10 AND x=3}
} {3 144 1}
do_test where-1.10 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 1}
do_test where-1.11 {
  execsql {SELECT x, y, fcnt(*) FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 1}

# Do the same kind of thing except use a join as the data source.
#
do_test where-2.1 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND y=s AND r=8977
  }
} {34 67 2}
do_test where-2.2 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND s=y AND r=8977
  }
} {34 67 2}
do_test where-2.3 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 2}
do_test where-2.4 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10
  }
} {34 67 2}
do_test where-2.5 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10
  }
} {34 67 2}
do_test where-2.6 {
  execsql {
    SELECT w, p, fcnt(*) FROM t2, t1
    WHERE x=q AND p=77 AND s=y AND w>5
  }
} {24 77 2}
do_test where-2.7 {
  execsql {
    SELECT w, p, fcnt(*) FROM t1, t2
    WHERE x=q AND p>77 AND s=y AND w=5
  }
} {5 96 2}

# Lets do a 3-way join.
#
do_test where-3.1 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11
  }
} {11 90 11 3}
do_test where-3.2 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
    WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12
  }
} {12 89 12 3}
do_test where-3.3 {
  execsql {
    SELECT A.w, B.p, C.w, fcnt(*) FROM t1 as A, t2 as B, t1 as C
    WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y
  }
} {15 86 86 3}

finish_test