SQLite

Check-in [1f40441204]
Login

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

Overview
Comment:Add an interface to the SQLITE_STMTSTATUS_AUTOINDEX status information to the TCL bindings. Add some simple automatic index test cases.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1: 1f40441204d9a912b1d6b67ff6ff9e17146c7abd
User & Date: drh 2010-04-07 19:32:00.000
Context
2010-04-07
20:32
Pull over the latest changes from the trunk. (check-in: e388fe8be8 user: drh tags: experimental)
19:32
Add an interface to the SQLITE_STMTSTATUS_AUTOINDEX status information to the TCL bindings. Add some simple automatic index test cases. (check-in: 1f40441204 user: drh tags: experimental)
16:54
Wrap all automatic index changes inside SQLITE_OMIT_AUTOMATIC_INDEX. Add the automatic_index PRAGMA to turn it on and off. (check-in: a811a47fbe user: drh tags: experimental)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/tclsqlite.c.
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
  int rc;                    /* Return code of most recent sqlite3_exec() */
  Tcl_Obj *pCollateNeeded;   /* Collation needed script */
  SqlPreparedStmt *stmtList; /* List of prepared statements*/
  SqlPreparedStmt *stmtLast; /* Last statement in the list */
  int maxStmt;               /* The next maximum number of stmtList */
  int nStmt;                 /* Number of statements in stmtList */
  IncrblobChannel *pIncrblob;/* Linked list of open incrblob channels */
  int nStep, nSort;          /* Statistics for most recent operation */
  int nTransaction;          /* Number of nested [transaction] methods */
};

struct IncrblobChannel {
  sqlite3_blob *pBlob;      /* sqlite3 blob handle */
  SqliteDb *pDb;            /* Associated database connection */
  int iSeek;                /* Current seek offset */







|







128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
  int rc;                    /* Return code of most recent sqlite3_exec() */
  Tcl_Obj *pCollateNeeded;   /* Collation needed script */
  SqlPreparedStmt *stmtList; /* List of prepared statements*/
  SqlPreparedStmt *stmtLast; /* Last statement in the list */
  int maxStmt;               /* The next maximum number of stmtList */
  int nStmt;                 /* Number of statements in stmtList */
  IncrblobChannel *pIncrblob;/* Linked list of open incrblob channels */
  int nStep, nSort, nIndex;  /* Statistics for most recent operation */
  int nTransaction;          /* Number of nested [transaction] methods */
};

struct IncrblobChannel {
  sqlite3_blob *pBlob;      /* sqlite3 blob handle */
  SqliteDb *pDb;            /* Associated database connection */
  int iSeek;                /* Current seek offset */
1347
1348
1349
1350
1351
1352
1353

1354
1355
1356
1357
1358
1359
1360
      if( p->pArray ){
        dbEvalRowInfo(p, 0, 0);
      }
      rcs = sqlite3_reset(pStmt);

      pDb->nStep = sqlite3_stmt_status(pStmt,SQLITE_STMTSTATUS_FULLSCAN_STEP,1);
      pDb->nSort = sqlite3_stmt_status(pStmt,SQLITE_STMTSTATUS_SORT,1);

      dbReleaseColumnNames(p);
      p->pPreStmt = 0;

      if( rcs!=SQLITE_OK ){
        /* If a run-time error occurs, report the error and stop reading
        ** the SQL.  */
        Tcl_SetObjResult(pDb->interp, dbTextToObj(sqlite3_errmsg(pDb->db)));







>







1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
      if( p->pArray ){
        dbEvalRowInfo(p, 0, 0);
      }
      rcs = sqlite3_reset(pStmt);

      pDb->nStep = sqlite3_stmt_status(pStmt,SQLITE_STMTSTATUS_FULLSCAN_STEP,1);
      pDb->nSort = sqlite3_stmt_status(pStmt,SQLITE_STMTSTATUS_SORT,1);
      pDb->nIndex = sqlite3_stmt_status(pStmt,SQLITE_STMTSTATUS_AUTOINDEX,1);
      dbReleaseColumnNames(p);
      p->pPreStmt = 0;

      if( rcs!=SQLITE_OK ){
        /* If a run-time error occurs, report the error and stop reading
        ** the SQL.  */
        Tcl_SetObjResult(pDb->interp, dbTextToObj(sqlite3_errmsg(pDb->db)));
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547


2548
2549

2550
2551
2552
2553
2554
2555
2556
      rc = TCL_ERROR;
    }
    sqlite3_close(pSrc);
    break;
  }

  /*
  **     $db status (step|sort)
  **
  ** Display SQLITE_STMTSTATUS_FULLSCAN_STEP or 
  ** SQLITE_STMTSTATUS_SORT for the most recent eval.
  */
  case DB_STATUS: {
    int v;
    const char *zOp;
    if( objc!=3 ){
      Tcl_WrongNumArgs(interp, 2, objv, "(step|sort)");
      return TCL_ERROR;
    }
    zOp = Tcl_GetString(objv[2]);
    if( strcmp(zOp, "step")==0 ){
      v = pDb->nStep;
    }else if( strcmp(zOp, "sort")==0 ){
      v = pDb->nSort;


    }else{
      Tcl_AppendResult(interp, "bad argument: should be step or sort", 

            (char*)0);
      return TCL_ERROR;
    }
    Tcl_SetObjResult(interp, Tcl_NewIntObj(v));
    break;
  }
  







|
















>
>

|
>







2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
      rc = TCL_ERROR;
    }
    sqlite3_close(pSrc);
    break;
  }

  /*
  **     $db status (step|sort|autoindex)
  **
  ** Display SQLITE_STMTSTATUS_FULLSCAN_STEP or 
  ** SQLITE_STMTSTATUS_SORT for the most recent eval.
  */
  case DB_STATUS: {
    int v;
    const char *zOp;
    if( objc!=3 ){
      Tcl_WrongNumArgs(interp, 2, objv, "(step|sort)");
      return TCL_ERROR;
    }
    zOp = Tcl_GetString(objv[2]);
    if( strcmp(zOp, "step")==0 ){
      v = pDb->nStep;
    }else if( strcmp(zOp, "sort")==0 ){
      v = pDb->nSort;
    }else if( strcmp(zOp, "autoindex")==0 ){
      v = pDb->nIndex;
    }else{
      Tcl_AppendResult(interp, 
            "bad argument: should be autoindex, step, or sort", 
            (char*)0);
      return TCL_ERROR;
    }
    Tcl_SetObjResult(interp, Tcl_NewIntObj(v));
    break;
  }
  
Added test/autoindex1.test.


























































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
# 2010 April 07
#
# 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.  The
# focus of this script is testing automatic index creation logic.
#

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

# If the library is not compiled with automatic index support then
# skip all tests in this file.
#
ifcapable {!autoindex} {
  finish_test
  return
}

# With automatic index turned off, we do a full scan of the T2 table
do_test autoindex1-100 {
  db eval {
    CREATE TABLE t1(a,b);
    INSERT INTO t1 VALUES(1,11);
    INSERT INTO t1 VALUES(2,22);
    INSERT INTO t1 SELECT a+2, b+22 FROM t1;
    INSERT INTO t1 SELECT a+4, b+44 FROM t1;
    CREATE TABLE t2(c,d);
    INSERT INTO t2 SELECT a, 900+b FROM t1;
  }
  db eval {
    PRAGMA automatic_index=OFF;
    SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
  }
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
do_test autoindex1-101 {
  db status step
} {63}
do_test autoindex1-102 {
  db status autoindex
} {0}

# With autoindex turned on, we build an index once and then use that index
# to find T2 values.
do_test autoindex1-110 {
  db eval {
    PRAGMA automatic_index=ON;
    SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
  }
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
do_test autoindex1-111 {
  db status step
} {7}
do_test autoindex1-112 {
  db status autoindex
} {7}

# The same test as above, but this time the T2 query is a subquery rather
# than a join.
do_test autoindex1-200 {
  db eval {
    PRAGMA automatic_index=OFF;
    SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
  }
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
do_test autoindex1-201 {
  db status step
} {35}
do_test autoindex1-202 {
  db status autoindex
} {0}
do_test autoindex1-210 {
  db eval {
    PRAGMA automatic_index=ON;
    SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
  }
} {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
do_test autoindex1-211 {
  db status step
} {7}
do_test autoindex1-212 {
  db status autoindex
} {7}



finish_test