/ Check-in [1efa6ed5]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Add simple tests for new sqlite3BtreeCursorHint() functionality.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | cursor-hints
Files: files | file ages | folders
SHA1: 1efa6ed584172291edce78faf9021e577583d03b
User & Date: dan 2014-07-15 11:59:44
Context
2015-08-13
21:43
Convert the hint expression of the CursorHint opcode into a string for display by EXPLAIN. Leaf check-in: 12640cb2 user: drh tags: cursor-hints-displayP4
20:07
Merge in all the trunk changes from the previous year. This breaks the cursor-hint mechanism, but provides a baseline for trouble-shooting. check-in: 82a7a61b user: drh tags: cursor-hints
2014-07-15
11:59
Add simple tests for new sqlite3BtreeCursorHint() functionality. check-in: 1efa6ed5 user: dan tags: cursor-hints
2014-07-14
19:04
In the expression passed to sqlite3BtreeCursorHint() for the inner loops of joins, replace any TK_COLUMN references to columns in the outer loops with TK_REGISTER expressions (Expr.iTable indicates the specific register containing the value). There are no automated tests for this yet. check-in: f9dddd00 user: dan tags: cursor-hints
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to main.mk.

238
239
240
241
242
243
244

245
246
247
248
249
250
251
  $(TOP)/src/test8.c \
  $(TOP)/src/test9.c \
  $(TOP)/src/test_autoext.c \
  $(TOP)/src/test_async.c \
  $(TOP)/src/test_backup.c \
  $(TOP)/src/test_btree.c \
  $(TOP)/src/test_config.c \

  $(TOP)/src/test_demovfs.c \
  $(TOP)/src/test_devsym.c \
  $(TOP)/src/test_fs.c \
  $(TOP)/src/test_func.c \
  $(TOP)/src/test_hexio.c \
  $(TOP)/src/test_init.c \
  $(TOP)/src/test_intarray.c \







>







238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
  $(TOP)/src/test8.c \
  $(TOP)/src/test9.c \
  $(TOP)/src/test_autoext.c \
  $(TOP)/src/test_async.c \
  $(TOP)/src/test_backup.c \
  $(TOP)/src/test_btree.c \
  $(TOP)/src/test_config.c \
  $(TOP)/src/test_cursorhint.c \
  $(TOP)/src/test_demovfs.c \
  $(TOP)/src/test_devsym.c \
  $(TOP)/src/test_fs.c \
  $(TOP)/src/test_func.c \
  $(TOP)/src/test_hexio.c \
  $(TOP)/src/test_init.c \
  $(TOP)/src/test_intarray.c \

Changes to src/tclsqlite.c.

3668
3669
3670
3671
3672
3673
3674

3675
3676
3677
3678
3679
3680
3681
....
3711
3712
3713
3714
3715
3716
3717

3718
3719
3720
3721
3722
3723
3724
    extern int Sqlitetest5_Init(Tcl_Interp*);
    extern int Sqlitetest6_Init(Tcl_Interp*);
    extern int Sqlitetest7_Init(Tcl_Interp*);
    extern int Sqlitetest8_Init(Tcl_Interp*);
    extern int Sqlitetest9_Init(Tcl_Interp*);
    extern int Sqlitetestasync_Init(Tcl_Interp*);
    extern int Sqlitetest_autoext_Init(Tcl_Interp*);

    extern int Sqlitetest_demovfs_Init(Tcl_Interp *);
    extern int Sqlitetest_func_Init(Tcl_Interp*);
    extern int Sqlitetest_hexio_Init(Tcl_Interp*);
    extern int Sqlitetest_init_Init(Tcl_Interp*);
    extern int Sqlitetest_malloc_Init(Tcl_Interp*);
    extern int Sqlitetest_mutex_Init(Tcl_Interp*);
    extern int Sqlitetestschema_Init(Tcl_Interp*);
................................................................................
    Sqlitetest5_Init(interp);
    Sqlitetest6_Init(interp);
    Sqlitetest7_Init(interp);
    Sqlitetest8_Init(interp);
    Sqlitetest9_Init(interp);
    Sqlitetestasync_Init(interp);
    Sqlitetest_autoext_Init(interp);

    Sqlitetest_demovfs_Init(interp);
    Sqlitetest_func_Init(interp);
    Sqlitetest_hexio_Init(interp);
    Sqlitetest_init_Init(interp);
    Sqlitetest_malloc_Init(interp);
    Sqlitetest_mutex_Init(interp);
    Sqlitetestschema_Init(interp);







>







 







>







3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
3681
3682
....
3712
3713
3714
3715
3716
3717
3718
3719
3720
3721
3722
3723
3724
3725
3726
    extern int Sqlitetest5_Init(Tcl_Interp*);
    extern int Sqlitetest6_Init(Tcl_Interp*);
    extern int Sqlitetest7_Init(Tcl_Interp*);
    extern int Sqlitetest8_Init(Tcl_Interp*);
    extern int Sqlitetest9_Init(Tcl_Interp*);
    extern int Sqlitetestasync_Init(Tcl_Interp*);
    extern int Sqlitetest_autoext_Init(Tcl_Interp*);
    extern int Sqlitetest_cursorhint_Init(Tcl_Interp*);
    extern int Sqlitetest_demovfs_Init(Tcl_Interp *);
    extern int Sqlitetest_func_Init(Tcl_Interp*);
    extern int Sqlitetest_hexio_Init(Tcl_Interp*);
    extern int Sqlitetest_init_Init(Tcl_Interp*);
    extern int Sqlitetest_malloc_Init(Tcl_Interp*);
    extern int Sqlitetest_mutex_Init(Tcl_Interp*);
    extern int Sqlitetestschema_Init(Tcl_Interp*);
................................................................................
    Sqlitetest5_Init(interp);
    Sqlitetest6_Init(interp);
    Sqlitetest7_Init(interp);
    Sqlitetest8_Init(interp);
    Sqlitetest9_Init(interp);
    Sqlitetestasync_Init(interp);
    Sqlitetest_autoext_Init(interp);
    Sqlitetest_cursorhint_Init(interp);
    Sqlitetest_demovfs_Init(interp);
    Sqlitetest_func_Init(interp);
    Sqlitetest_hexio_Init(interp);
    Sqlitetest_init_Init(interp);
    Sqlitetest_malloc_Init(interp);
    Sqlitetest_mutex_Init(interp);
    Sqlitetestschema_Init(interp);

Added src/test_cursorhint.c.





































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
/*
** 2008 March 19
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** Code for testing all sorts of SQLite interfaces.  This code
** implements new SQL functions used by the test scripts.
*/
#include "sqlite3.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>
#include <assert.h>

#include "sqliteInt.h"
#include "vdbeInt.h"

struct CursorHintGlobal {
  Tcl_Interp *interp;
  Tcl_Obj *pScript;
} cursorhintglobal;

static char *exprToString(Mem *aMem, Expr *pExpr){
  char *zRet = 0;
  char *zBinOp = 0;

  switch( pExpr->op ){
    case TK_STRING:
      zRet = sqlite3_mprintf("%Q", pExpr->u.zToken);
      break;

    case TK_INTEGER:
      zRet = sqlite3_mprintf("%d", pExpr->u.iValue);
      break;

    case TK_NULL:
      zRet = sqlite3_mprintf("%s", "NULL");
      break;

    case TK_REGISTER: {
      Mem *pMem = &aMem[pExpr->iTable];
      if( pMem->flags & MEM_Int ){
        zRet = sqlite3_mprintf("%lld", pMem->u.i);
      }
      else if( pMem->flags & MEM_Real ){
        zRet = sqlite3_mprintf("%f", pMem->r);
      }
      else if( pMem->flags & MEM_Str ){
        zRet = sqlite3_mprintf("%.*Q", pMem->n, pMem->z);
      }
      else if( pMem->flags & MEM_Blob ){
      }
      else{
        zRet = sqlite3_mprintf("%s", "NULL");
      }
      break;
    }

    case TK_COLUMN: {
      zRet = sqlite3_mprintf("col(%d)", (int)pExpr->iColumn);
      break;
    }

    case TK_LT:      zBinOp = "<";      break;
    case TK_LE:      zBinOp = "<=";     break;
    case TK_GT:      zBinOp = ">";      break;
    case TK_GE:      zBinOp = ">=";     break;
    case TK_NE:      zBinOp = "!=";     break;
    case TK_EQ:      zBinOp = "==";     break;
    case TK_IS:      zBinOp = "IS";     break;
    case TK_ISNOT:   zBinOp = "IS NOT"; break;
    case TK_AND:     zBinOp = "AND";    break;
    case TK_OR:      zBinOp = "OR";     break;
    case TK_PLUS:    zBinOp = "+";      break;
    case TK_STAR:    zBinOp = "*";      break;
    case TK_MINUS:   zBinOp = "-";      break;
    case TK_REM:     zBinOp = "%";      break;
    case TK_BITAND:  zBinOp = "&";      break;
    case TK_BITOR:   zBinOp = "|";      break;
    case TK_SLASH:   zBinOp = "/";      break;
    case TK_LSHIFT:  zBinOp = "<<";     break;
    case TK_RSHIFT:  zBinOp = ">>";     break;
    case TK_CONCAT:  zBinOp = "||";     break;

    default:
      zRet = sqlite3_mprintf("%s", "expr");
      break;
  }

  if( zBinOp ){
    zRet = sqlite3_mprintf("(%z %s %z)", 
        exprToString(aMem, pExpr->pLeft),
        zBinOp,
        exprToString(aMem, pExpr->pRight)
    );
  }

  return zRet;
}

void sqlite3BtreeCursorHintTest(Mem *aMem, Expr *pExpr){
  if( cursorhintglobal.pScript ){
    Tcl_Obj *pEval = Tcl_DuplicateObj(cursorhintglobal.pScript);
    char *zExpr;
    Tcl_Obj *pObj;
    Tcl_IncrRefCount(pEval);
    zExpr = exprToString(aMem, pExpr);
    pObj = Tcl_NewStringObj(zExpr, -1);
    sqlite3_free(zExpr);
    Tcl_ListObjAppendElement(cursorhintglobal.interp, pEval, pObj);
    Tcl_EvalObjEx(cursorhintglobal.interp, pEval, TCL_GLOBAL_ONLY);
    Tcl_DecrRefCount(pEval);
  }
}

/*
** Usage: cursorhint_hook SCRIPT
*/
static int install_cursorhint_hook(
  ClientData clientData, /* Pointer to sqlite3_enable_XXX function */
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int objc,              /* Number of arguments */
  Tcl_Obj *CONST objv[]  /* Command arguments */
){
  if( objc!=1 && objc!=2 ){
    Tcl_WrongNumArgs(interp, 1, objv, "?SCRIPT?");
    return TCL_ERROR;
  }
  if( cursorhintglobal.pScript ){
    Tcl_DecrRefCount(cursorhintglobal.pScript);
    memset(&cursorhintglobal, 0, sizeof(cursorhintglobal));
  }
  if( objc==2 ){
    cursorhintglobal.interp = interp;
    cursorhintglobal.pScript = Tcl_DuplicateObj(objv[1]);
  }
  return TCL_OK;
}

/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest_cursorhint_Init(Tcl_Interp *interp){
  static struct {
     char *zName;
     Tcl_ObjCmdProc *xProc;
  } aObjCmd[] = {
     { "cursorhint_hook",    install_cursorhint_hook },
  };
  int i;
  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, aObjCmd[i].xProc, 0, 0);
  }
  sqlite3_initialize();
  return TCL_OK;
}

Changes to src/vdbe.c.

6180
6181
6182
6183
6184
6185
6186

6187





6188
6189
6190
6191
6192
6193
6194
*/
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);





  break;
}
#endif /* SQLITE_ENABLE_CURSOR_HINTS */

/* Opcode: Noop * * * * *
**
** Do nothing.  This instruction is often useful as a jump







>
|
>
>
>
>
>







6180
6181
6182
6183
6184
6185
6186
6187
6188
6189
6190
6191
6192
6193
6194
6195
6196
6197
6198
6199
6200
*/
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;
}
#endif /* SQLITE_ENABLE_CURSOR_HINTS */

/* Opcode: Noop * * * * *
**
** Do nothing.  This instruction is often useful as a jump

Changes to src/where.c.

2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
){
  Parse *pParse = pWInfo->pParse;
  sqlite3 *db = pParse->db;
  Vdbe *v = pParse->pVdbe;
  WhereLevel *pLevel;
  Expr *pExpr = 0;
  int iCur;
  Bitmask msk;
  WhereClause *pWC;
  WhereTerm *pTerm;
  WhereLoop *pWLoop;
  int i, j;

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







<







2760
2761
2762
2763
2764
2765
2766

2767
2768
2769
2770
2771
2772
2773
){
  Parse *pParse = pWInfo->pParse;
  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];

Added test/cursorhint.test.

















































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
# 2014 July 15
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#

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

do_execsql_test 1.0 {
  CREATE TABLE t1(a,b);
  CREATE TABLE t2(x,y);
  INSERT INTO t1 VALUES(10, 15);
  INSERT INTO t1 VALUES(20, 25);
  INSERT INTO t2 VALUES('ten', 'fifteen');
  INSERT INTO t2 VALUES('twenty', 'twentyfive');
  PRAGMA automatic_index = 0;
}

proc H {expr} {
  lappend ::cursorhint $expr
}

proc do_cursorhint_test {tn sql hints} {
  cursorhint_hook H
  set ::cursorhint [list]
  set testbody [subst {
    execsql {$sql}
    set ::cursorhint
  }]
  uplevel [list do_test $tn $testbody [list {*}$hints]]
  cursorhint_hook
}


do_cursorhint_test 1.1 {
  SELECT * FROM t1 CROSS JOIN t2 WHERE a=x;
} {
  {(10 == col(0))} 
  {(20 == col(0))}
}

do_cursorhint_test 1.2 {
  SELECT * FROM t2 CROSS JOIN t1 WHERE a=x;
} {
  {(col(0) == 'ten')} 
  {(col(0) == 'twenty')}
}

do_cursorhint_test 1.3 {
  SELECT * FROM t1 CROSS JOIN t2 WHERE b=15;
} {
  {(col(1) == 15)} 
}

do_cursorhint_test 1.3 {
  SELECT * FROM t1 CROSS JOIN t2 WHERE y=b+1;
} {
  {(col(1) == (15 + 1))} 
  {(col(1) == (25 + 1))} 
}


finish_test