/ Check-in [fc3fb5cd]
Login

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

Overview
Comment:Refactor the sqlite3BtreeCursorHint() interface for improved maintainability.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | cursor-hints
Files: files | file ages | folders
SHA1: fc3fb5cd0d2c123a069e5b18b62bb1f708c8698a
User & Date: drh 2015-08-14 15:05:55
Context
2015-08-14
18:50
Fix the cursor hint mechanism so that it does the right thing for indexed lookups. check-in: 581e3d49 user: drh tags: cursor-hints
15:05
Refactor the sqlite3BtreeCursorHint() interface for improved maintainability. check-in: fc3fb5cd user: drh tags: cursor-hints
01:03
Fix the CursorHint so that it includes the scan boundary constraints. On the expression text for the CursorHint opcode, show rowid correctly. check-in: f0d428d1 user: drh tags: cursor-hints
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/btree.c.

835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
















852
853
854
855
856
857
858
....
9560
9561
9562
9563
9564
9565
9566
9567
9568
9569
9570
9571
9572
9573
9574
9575
9576
9577
9578
9579
9580
9581
    *pDifferentRow = 0;
  }
  return SQLITE_OK;
}

#ifdef SQLITE_ENABLE_CURSOR_HINTS
/*
** Give a hint to the cursor that it only has to deliver rows for which
** the expression pExpr is true.  Within this expression, rows of the
** cursor are identified by Expr.op==TK_COLUMN with Expr.iTable==iTable.
**
** This interfaces is not used by the standard storage engine of SQLite.
** It is only useful to application that replace SQLite's built-in storage
** engine with their own.
*/
void sqlite3BtreeCursorHint(BtCursor *pCur, int iTable, const Expr *pExpr){
  /* Alternative storage engines might use this. */
















}
#endif /* SQLITE_ENABLE_CURSOR_HINTS */

#ifndef SQLITE_OMIT_AUTOVACUUM
/*
** Given a page number of a regular database page, return the page
** number for the pointer-map page that contains the entry for the
................................................................................
    }
  }

  pBt->btsFlags &= ~BTS_NO_WAL;
  return rc;
}

/*
** set the mask of hint flags for cursor pCsr.
*/
void sqlite3BtreeCursorHints(BtCursor *pCsr, unsigned int mask){
  assert( mask==BTREE_BULKLOAD || mask==BTREE_SEEK_EQ || mask==0 );
  pCsr->hints = mask;
}

#ifdef SQLITE_DEBUG
/*
** Return true if the cursor has a hint specified.  This routine is
** only used from within assert() statements
*/
int sqlite3BtreeCursorHasHint(BtCursor *pCsr, unsigned int mask){
  return (pCsr->hints & mask)!=0;







|
|
|

|
|
|

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







 







<
<
<
<
<
<
<
<







835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
....
9576
9577
9578
9579
9580
9581
9582








9583
9584
9585
9586
9587
9588
9589
    *pDifferentRow = 0;
  }
  return SQLITE_OK;
}

#ifdef SQLITE_ENABLE_CURSOR_HINTS
/*
** Provide hints to the cursor.  The particular hint given (and the type
** and number of the varargs parameters) is determined by the eHintType
** parameter.  See the definitions of the BTREE_HINT_* macros for details.
**
** Hints are not (currently) used by the native SQLite implementation.
** This mechanism is provided for systems that substitute an alternative
** storage engine.
*/
void sqlite3BtreeCursorHint(BtCursor *pCur, int eHintType, ...){
  va_list ap;
  va_start(ap, eHintType);
  switch( eHintType ){
    case BTREE_HINT_FLAGS: {
      pCur->hints = va_arg(ap, unsigned int);
      assert( pCur->hints==BTREE_SEEK_EQ || pCur->hints==BTREE_BULKLOAD
                  || pCur->hints==0 );
      break;
    }
    case BTREE_HINT_RANGE: {
      (void)va_arg(ap, Expr*);
      (void)va_arg(ap, struct Mem*);
      /* Range expression not used in this implementation */
      break;
    }
  }
  va_end(ap);
}
#endif /* SQLITE_ENABLE_CURSOR_HINTS */

#ifndef SQLITE_OMIT_AUTOVACUUM
/*
** Given a page number of a regular database page, return the page
** number for the pointer-map page that contains the entry for the
................................................................................
    }
  }

  pBt->btsFlags &= ~BTS_NO_WAL;
  return rc;
}









#ifdef SQLITE_DEBUG
/*
** Return true if the cursor has a hint specified.  This routine is
** only used from within assert() statements
*/
int sqlite3BtreeCursorHasHint(BtCursor *pCsr, unsigned int mask){
  return (pCsr->hints & mask)!=0;

Changes to src/btree.h.

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
...
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
...
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
#define BTREE_TEXT_ENCODING       5
#define BTREE_USER_VERSION        6
#define BTREE_INCR_VACUUM         7
#define BTREE_APPLICATION_ID      8
#define BTREE_DATA_VERSION        15  /* A virtual meta-value */

/*







































** Values that may be OR'd together to form the second argument of an
** sqlite3BtreeCursorHints() call.
**
** The BTREE_BULKLOAD flag is set on index cursors when the index is going
** to be filled with content that is already in sorted order.
**
** The BTREE_SEEK_EQ flag is set on cursors that will get OP_SeekGE or
** OP_SeekLE opcodes for a range search, but where the range of entries
** selected will all have the same key.  In other words, the cursor will
** be used only for equality key searches.
**
*/
#define BTREE_BULKLOAD 0x00000001  /* Used to full index in sorted order */
#define BTREE_SEEK_EQ  0x00000002  /* EQ seeks only - no range seeks */





int sqlite3BtreeCursor(
  Btree*,                              /* BTree containing table to open */
  int iTable,                          /* Index of root page */
  int wrFlag,                          /* 1 for writing.  0 for read-only */
  struct KeyInfo*,                     /* First argument to compare function */
  BtCursor *pCursor                    /* Space to write cursor structure */
................................................................................
  UnpackedRecord *pUnKey,
  i64 intKey,
  int bias,
  int *pRes
);
int sqlite3BtreeCursorHasMoved(BtCursor*);
int sqlite3BtreeCursorRestore(BtCursor*, int*);
#ifdef SQLITE_ENABLE_CURSOR_HINTS
void sqlite3BtreeCursorHint(BtCursor*, int, const Expr*);
#endif
int sqlite3BtreeDelete(BtCursor*);
int sqlite3BtreeInsert(BtCursor*, const void *pKey, i64 nKey,
                                  const void *pData, int nData,
                                  int nZero, int bias, int seekResult);
int sqlite3BtreeFirst(BtCursor*, int *pRes);
int sqlite3BtreeLast(BtCursor*, int *pRes);
int sqlite3BtreeNext(BtCursor*, int *pRes);
................................................................................
char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*);
struct Pager *sqlite3BtreePager(Btree*);

int sqlite3BtreePutData(BtCursor*, u32 offset, u32 amt, void*);
void sqlite3BtreeIncrblobCursor(BtCursor *);
void sqlite3BtreeClearCursor(BtCursor *);
int sqlite3BtreeSetVersion(Btree *pBt, int iVersion);
void sqlite3BtreeCursorHints(BtCursor *, unsigned int mask);
#ifdef SQLITE_DEBUG
int sqlite3BtreeCursorHasHint(BtCursor*, unsigned int mask);
#endif
int sqlite3BtreeIsReadonly(Btree *pBt);
int sqlite3HeaderSizeBtree(void);

#ifndef NDEBUG







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












>
>
>
>







 







<
<
<







 







<







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
...
224
225
226
227
228
229
230



231
232
233
234
235
236
237
...
247
248
249
250
251
252
253

254
255
256
257
258
259
260
#define BTREE_TEXT_ENCODING       5
#define BTREE_USER_VERSION        6
#define BTREE_INCR_VACUUM         7
#define BTREE_APPLICATION_ID      8
#define BTREE_DATA_VERSION        15  /* A virtual meta-value */

/*
** Kinds of hints that can be passed into the sqlite3BtreeCursorHint()
** interface.
**
** Note that cursor hints are not used by the canonical SQLite b-tree
** ayer.  Cursor hints are provided so that systems that substitute their
** on custom b-tree layer can have access to additional information that might
** boost performance.  Hints are only provided if SQLite is compiled with
** SQLITE_ENABLE_CURSOR_HINTS.  The hinting interface is undocumented
** (except for comments such as this) and is subject to change from one
** release of SQLite to the next.
**
** BTREE_HINT_FLAGS  (arguments: unsigned int)
**
**     Some combinatation of BTREE_BULKLOAD and BTREE_SEEK_EQ flags.  The
**     argument is a single unsigned integer which overwrites all prior
**     flag settings.
**
** BTREE_HINT_RANGE  (arguments: Expr*, Mem*)
**
**     The first argument is an Expr* (which is guaranteed to be constant for
**     the lifetime of the cursor) that defines constraints on which rows
**     might be fetched with this cursor.  The Expr* tree may contain
**     TK_REGISTER nodes that refer to values stored in the array of registers
**     passed as the second parameter.  In other words, if Expr.op==TK_REGISTER
**     then the value of the node is the value in Mem[pExpr.iTable].  Any
**     TK_COLUMN node in the expression tree refers to the Expr.iColumn-th
**     column of the b-tree of the cursor.  The Expr tree will not contain
**     any function calls nor subqueries nor references to b-trees other than
**     the cursor being hinted.
**
**     The design of the _RANGE hint is aid b-tree implementations that try
**     to prefetch content from remote machines - to provide those
**     implementations with limits on what needs to be prefetched and thereby
**     reduce network bandwidth.
*/
#define BTREE_HINT_FLAGS 1       /* Set flags indicating cursor usage */
#define BTREE_HINT_RANGE 2       /* Range constraints on queries */

/*
** Values that may be OR'd together to form the argument to the
** BTREE_HINT_FLAGS hint for sqlite3BtreeCursorHint():
**
** The BTREE_BULKLOAD flag is set on index cursors when the index is going
** to be filled with content that is already in sorted order.
**
** The BTREE_SEEK_EQ flag is set on cursors that will get OP_SeekGE or
** OP_SeekLE opcodes for a range search, but where the range of entries
** selected will all have the same key.  In other words, the cursor will
** be used only for equality key searches.
**
*/
#define BTREE_BULKLOAD 0x00000001  /* Used to full index in sorted order */
#define BTREE_SEEK_EQ  0x00000002  /* EQ seeks only - no range seeks */

#ifdef SQLITE_ENABLE_CURSOR_HINTS
void sqlite3BtreeCursorHint(BtCursor*, int, ...);
#endif

int sqlite3BtreeCursor(
  Btree*,                              /* BTree containing table to open */
  int iTable,                          /* Index of root page */
  int wrFlag,                          /* 1 for writing.  0 for read-only */
  struct KeyInfo*,                     /* First argument to compare function */
  BtCursor *pCursor                    /* Space to write cursor structure */
................................................................................
  UnpackedRecord *pUnKey,
  i64 intKey,
  int bias,
  int *pRes
);
int sqlite3BtreeCursorHasMoved(BtCursor*);
int sqlite3BtreeCursorRestore(BtCursor*, int*);



int sqlite3BtreeDelete(BtCursor*);
int sqlite3BtreeInsert(BtCursor*, const void *pKey, i64 nKey,
                                  const void *pData, int nData,
                                  int nZero, int bias, int seekResult);
int sqlite3BtreeFirst(BtCursor*, int *pRes);
int sqlite3BtreeLast(BtCursor*, int *pRes);
int sqlite3BtreeNext(BtCursor*, int *pRes);
................................................................................
char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*);
struct Pager *sqlite3BtreePager(Btree*);

int sqlite3BtreePutData(BtCursor*, u32 offset, u32 amt, void*);
void sqlite3BtreeIncrblobCursor(BtCursor *);
void sqlite3BtreeClearCursor(BtCursor *);
int sqlite3BtreeSetVersion(Btree *pBt, int iVersion);

#ifdef SQLITE_DEBUG
int sqlite3BtreeCursorHasHint(BtCursor*, unsigned int mask);
#endif
int sqlite3BtreeIsReadonly(Btree *pBt);
int sqlite3HeaderSizeBtree(void);

#ifndef NDEBUG

Changes to src/vdbe.c.

3380
3381
3382
3383
3384
3385
3386

3387
3388

3389
3390
3391
3392
3393
3394
3395
....
6549
6550
6551
6552
6553
6554
6555
6556
6557
6558
6559
6560
6561
6562
6563
6564
6565
6566
6567
6568
6569
6570
6571
6572
6573
6574
6575
6576
6577
  ** and report database corruption if they were not, but this check has
  ** since moved into the btree layer.  */  
  pCur->isTable = pOp->p4type!=P4_KEYINFO;

open_cursor_set_hints:
  assert( OPFLAG_BULKCSR==BTREE_BULKLOAD );
  assert( OPFLAG_SEEKEQ==BTREE_SEEK_EQ );

  sqlite3BtreeCursorHints(pCur->pCursor,
                          (pOp->p5 & (OPFLAG_BULKCSR|OPFLAG_SEEKEQ)));

  break;
}

/* Opcode: OpenEphemeral P1 P2 * P4 P5
** Synopsis: nColumn=P2
**
** Open a new cursor P1 to a transient table.
................................................................................
#endif /* SQLITE_DEBUG */
#endif /* SQLITE_OMIT_TRACE */
  if( pOp->p2 ) goto jump_to_p2;
  break;
}

#ifdef SQLITE_ENABLE_CURSOR_HINTS
/* Opcode: CursorHint P1 P2 * P4 *
**
** Provide a hint to cursor P1 that it only needs to return rows that
** satisfy the Expr tree given in P4.  P2 is the table number of cursor P1
** such that references to cursor P1 in the Expr tree are given by
** Expr.iTable==P2.
*/
case OP_CursorHint: {
  VdbeCursor *pC;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  assert( pOp->p4type==P4_EXPR );
  pC = p->apCsr[pOp->p1];
  if( pC ){
    sqlite3BtreeCursorHint(pC->pCursor, pOp->p2, pOp->p4.pExpr);
#ifdef SQLITE_TEST
    void sqlite3BtreeCursorHintTest(Mem*, Expr*);
    sqlite3BtreeCursorHintTest(p->aMem, pOp->p4.pExpr);
#endif
  }
  break;
}







>
|
|
>







 







|


|
|
|








|







3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
3395
3396
3397
....
6551
6552
6553
6554
6555
6556
6557
6558
6559
6560
6561
6562
6563
6564
6565
6566
6567
6568
6569
6570
6571
6572
6573
6574
6575
6576
6577
6578
6579
  ** and report database corruption if they were not, but this check has
  ** since moved into the btree layer.  */  
  pCur->isTable = pOp->p4type!=P4_KEYINFO;

open_cursor_set_hints:
  assert( OPFLAG_BULKCSR==BTREE_BULKLOAD );
  assert( OPFLAG_SEEKEQ==BTREE_SEEK_EQ );
#ifdef SQLITE_ENABLE_CURSOR_HINTS
  sqlite3BtreeCursorHint(pCur->pCursor, BTREE_HINT_FLAGS,
                         (pOp->p5 & (OPFLAG_BULKCSR|OPFLAG_SEEKEQ)));
#endif
  break;
}

/* Opcode: OpenEphemeral P1 P2 * P4 P5
** Synopsis: nColumn=P2
**
** Open a new cursor P1 to a transient table.
................................................................................
#endif /* SQLITE_DEBUG */
#endif /* SQLITE_OMIT_TRACE */
  if( pOp->p2 ) goto jump_to_p2;
  break;
}

#ifdef SQLITE_ENABLE_CURSOR_HINTS
/* Opcode: CursorHint P1 * * P4 *
**
** Provide a hint to cursor P1 that it only needs to return rows that
** satisfy the Expr in P4.  TK_REGISTER terms in the P4 expression refer
** to values currently held in registers.  TK_COLUMN terms in the P4
** expression refer to columns in the b-tree to which cursor P1 is pointing.
*/
case OP_CursorHint: {
  VdbeCursor *pC;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  assert( pOp->p4type==P4_EXPR );
  pC = p->apCsr[pOp->p1];
  if( pC ){
    sqlite3BtreeCursorHint(pC->pCursor, BTREE_HINT_RANGE, pOp->p4.pExpr, aMem);
#ifdef SQLITE_TEST
    void sqlite3BtreeCursorHintTest(Mem*, Expr*);
    sqlite3BtreeCursorHintTest(p->aMem, pOp->p4.pExpr);
#endif
  }
  break;
}

Changes to src/wherecode.c.

588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
...
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
}

#ifdef SQLITE_ENABLE_CURSOR_HINTS

/*
** This function is called on every node of an expression tree used as an
** argument to the OP_CursorHint instruction. If the node is a TK_COLUMN
** that accesses any cursor other than (pWalker->u.i), do the following:
**
**   1) allocate a register and code an OP_Column instruction to read 
**      the specified column into the new register, and
**
**   2) transform the expression node to a TK_REGISTER node that reads 
**      from the newly populated register.
*/
................................................................................
  sqlite3 *db = pParse->db;
  Vdbe *v = pParse->pVdbe;
  WhereLevel *pLevel;
  Expr *pExpr = 0;
  int iCur;
  WhereClause *pWC;
  WhereTerm *pTerm;
  WhereLoop *pWLoop;
  int i, j;

  if( OptimizationDisabled(db, SQLITE_CursorHints) ) return;
  pLevel = &pWInfo->a[iLevel];
  pWLoop = pLevel->pWLoop;
  iCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor;
  pWC = &pWInfo->sWC;
  for(i=0; i<pWC->nTerm; i++){
    pTerm = &pWC->a[i];
    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
    if( pTerm->prereqAll & pLevel->notReady ) continue;
    if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) continue;







|







 







<
|



<







588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
...
634
635
636
637
638
639
640

641
642
643
644

645
646
647
648
649
650
651
}

#ifdef SQLITE_ENABLE_CURSOR_HINTS

/*
** This function is called on every node of an expression tree used as an
** argument to the OP_CursorHint instruction. If the node is a TK_COLUMN
** that accesses any cursor other than (pWalker->u.n), do the following:
**
**   1) allocate a register and code an OP_Column instruction to read 
**      the specified column into the new register, and
**
**   2) transform the expression node to a TK_REGISTER node that reads 
**      from the newly populated register.
*/
................................................................................
  sqlite3 *db = pParse->db;
  Vdbe *v = pParse->pVdbe;
  WhereLevel *pLevel;
  Expr *pExpr = 0;
  int iCur;
  WhereClause *pWC;
  WhereTerm *pTerm;

  int i;

  if( OptimizationDisabled(db, SQLITE_CursorHints) ) return;
  pLevel = &pWInfo->a[iLevel];

  iCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor;
  pWC = &pWInfo->sWC;
  for(i=0; i<pWC->nTerm; i++){
    pTerm = &pWC->a[i];
    if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
    if( pTerm->prereqAll & pLevel->notReady ) continue;
    if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) continue;