/ Check-in [4c782c95]
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 the sqlite3_vtab_collation() function, which allows an xBestIndex callback to determine the collation sequence that SQLite will use for a comparison. And the SQLITE_DBCONFIG_FULL_EQP configuration option, which enhances the output of "EXPLAIN QUERY PLAN" so that it includes statements run by triggers. And the code for the sqlite3_expert extension and command line application.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4c782c950204c09c1d8f857c39c4cf476539ec4e7eee6fd86419d47cf0f8b9e0
User & Date: dan 2017-12-16 19:36:52
Context
2017-12-19
18:56
Fix crash in sqlite3_vtab_collation() when called for an IS NOT NULL constraint. check-in: ad38d2c4 user: dan tags: trunk
17:42
Experimentally add the SQLite expert functionality to the shell tool. Closed-Leaf check-in: 51068dba user: dan tags: expert-in-shell
2017-12-16
20:20
Add an experimental location(X) SQL function that attempt to return the location of the payload within the database for the record that contains column X. location(X) returns NULL if X is not an ordinary table column or if SQLite cannot figure out the location because it is using a covering index. check-in: 51be9558 user: drh tags: location-function
19:36
Add the sqlite3_vtab_collation() function, which allows an xBestIndex callback to determine the collation sequence that SQLite will use for a comparison. And the SQLITE_DBCONFIG_FULL_EQP configuration option, which enhances the output of "EXPLAIN QUERY PLAN" so that it includes statements run by triggers. And the code for the sqlite3_expert extension and command line application. check-in: 4c782c95 user: dan tags: trunk
19:16
Merge latest trunk changes into this branch. Closed-Leaf check-in: d5b597b5 user: dan tags: schemalint
04:37
Add unnecessary initializations to some local variables in the rtree module to suppress false-positive compiler warnings coming out of MSVC. check-in: 64487d65 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to Makefile.in.

   421    421     $(TOP)/ext/fts3/fts3_test.c  \
   422    422     $(TOP)/ext/session/test_session.c \
   423    423     $(TOP)/ext/rbu/test_rbu.c 
   424    424   
   425    425   # Statically linked extensions
   426    426   #
   427    427   TESTSRC += \
          428  +  $(TOP)/ext/expert/sqlite3expert.c \
          429  +  $(TOP)/ext/expert/test_expert.c \
   428    430     $(TOP)/ext/misc/amatch.c \
   429    431     $(TOP)/ext/misc/carray.c \
   430    432     $(TOP)/ext/misc/closure.c \
   431    433     $(TOP)/ext/misc/csv.c \
   432    434     $(TOP)/ext/misc/eval.c \
   433    435     $(TOP)/ext/misc/fileio.c \
   434    436     $(TOP)/ext/misc/fuzzer.c \
................................................................................
  1186   1188   
  1187   1189   sqlite3_analyzer.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/spaceanal.tcl $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in
  1188   1190   	$(TCLSH_CMD) $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in >sqlite3_analyzer.c
  1189   1191   
  1190   1192   sqlite3_analyzer$(TEXE): sqlite3_analyzer.c
  1191   1193   	$(LTLINK) sqlite3_analyzer.c -o $@ $(LIBTCL) $(TLIBS)
  1192   1194   
         1195  +sqlite3_expert$(TEXE): $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c
         1196  +	$(LTLINK)	$(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c -o sqlite3_expert $(TLIBS)
         1197  +
  1193   1198   CHECKER_DEPS =\
  1194   1199     $(TOP)/tool/mkccode.tcl \
  1195   1200     sqlite3.c \
  1196   1201     $(TOP)/src/tclsqlite.c \
  1197   1202     $(TOP)/ext/repair/sqlite3_checker.tcl \
  1198   1203     $(TOP)/ext/repair/checkindex.c \
  1199   1204     $(TOP)/ext/repair/checkfreelist.c \

Changes to Makefile.msc.

  1396   1396     $(TOP)\ext\fts3\fts3_test.c \
  1397   1397     $(TOP)\ext\rbu\test_rbu.c \
  1398   1398     $(TOP)\ext\session\test_session.c
  1399   1399   
  1400   1400   # Statically linked extensions.
  1401   1401   #
  1402   1402   TESTEXT = \
         1403  +  $(TOP)\ext\expert\sqlite3expert.c \
         1404  +  $(TOP)\ext\expert\test_expert.c \
  1403   1405     $(TOP)\ext\misc\amatch.c \
  1404   1406     $(TOP)\ext\misc\carray.c \
  1405   1407     $(TOP)\ext\misc\closure.c \
  1406   1408     $(TOP)\ext\misc\csv.c \
  1407   1409     $(TOP)\ext\misc\eval.c \
  1408   1410     $(TOP)\ext\misc\fileio.c \
  1409   1411     $(TOP)\ext\misc\fuzzer.c \
................................................................................
  2215   2217   
  2216   2218   sqlite3_analyzer.c:	$(SQLITE3C) $(SQLITE3H) $(TOP)\src\tclsqlite.c $(TOP)\tool\spaceanal.tcl $(TOP)\tool\mkccode.tcl $(TOP)\tool\sqlite3_analyzer.c.in $(SQLITE_TCL_DEP)
  2217   2219   	$(TCLSH_CMD) $(TOP)\tool\mkccode.tcl $(TOP)\tool\sqlite3_analyzer.c.in > $@
  2218   2220   
  2219   2221   sqlite3_analyzer.exe:	sqlite3_analyzer.c $(LIBRESOBJS)
  2220   2222   	$(LTLINK) $(NO_WARN) -DBUILD_sqlite -I$(TCLINCDIR) sqlite3_analyzer.c \
  2221   2223   		/link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS)
         2224  +
         2225  +sqlite3_expert.exe: $(SQLITE3C) $(TOP)\ext\expert\sqlite3expert.h $(TOP)\ext\expert\sqlite3expert.c $(TOP)\ext\expert\expert.c
         2226  +	$(LTLINK) $(NO_WARN)	$(TOP)\ext\expert\sqlite3expert.c $(TOP)\ext\expert\expert.c $(SQLITE3C) $(TLIBS)
  2222   2227   
  2223   2228   CHECKER_DEPS =\
  2224   2229     $(TOP)/tool/mkccode.tcl \
  2225   2230     sqlite3.c \
  2226   2231     $(TOP)/src/tclsqlite.c \
  2227   2232     $(TOP)/ext/repair/sqlite3_checker.tcl \
  2228   2233     $(TOP)/ext/repair/checkindex.c \

Added ext/expert/README.md.

            1  +## SQLite Expert Extension
            2  +
            3  +This folder contains code for a simple system to propose useful indexes
            4  +given a database and a set of SQL queries. It works as follows:
            5  +
            6  +  1. The user database schema is copied to a temporary database.
            7  +
            8  +  1. All SQL queries are prepared against the temporary database.
            9  +     Information regarding the WHERE and ORDER BY clauses, and other query
           10  +     features that affect index selection are recorded.
           11  +
           12  +  1. The information gathered in step 2 is used to create candidate 
           13  +     indexes - indexes that the planner might have made use of in the previous
           14  +     step, had they been available.
           15  +
           16  +  1. A subset of the data in the user database is used to generate statistics
           17  +     for all existing indexes and the candidate indexes generated in step 3
           18  +     above.
           19  +
           20  +  1. The SQL queries are prepared a second time. If the planner uses any
           21  +     of the indexes created in step 3, they are recommended to the user.
           22  +
           23  +# C API
           24  +
           25  +The SQLite expert C API is defined in sqlite3expert.h. Most uses will proceed
           26  +as follows:
           27  +
           28  +  1. An sqlite3expert object is created by calling **sqlite3\_expert\_new()**.
           29  +     A database handle opened by the user is passed as an argument.
           30  +
           31  +  1. The sqlite3expert object is configured with one or more SQL statements
           32  +     by making one or more calls to **sqlite3\_expert\_sql()**. Each call may
           33  +     specify a single SQL statement, or multiple statements separated by
           34  +     semi-colons.
           35  +  
           36  +  1. Optionally, the **sqlite3\_expert\_config()** API may be used to 
           37  +     configure the size of the data subset used to generate index statistics.
           38  +     Using a smaller subset of the data can speed up the analysis.
           39  +
           40  +  1. **sqlite3\_expert\_analyze()** is called to run the analysis.
           41  +
           42  +  1. One or more calls are made to **sqlite3\_expert\_report()** to extract
           43  +     components of the results of the analysis.
           44  +
           45  +  1. **sqlite3\_expert\_destroy()** is called to free all resources.
           46  +
           47  +Refer to comments in sqlite3expert.h for further details.
           48  +
           49  +# sqlite3_expert application
           50  +
           51  +The file "expert.c" contains the code for a command line application that
           52  +uses the API described above. It can be compiled with (for example):
           53  +
           54  +<pre>
           55  +  gcc -O2 sqlite3.c expert.c sqlite3expert.c -o sqlite3_expert
           56  +</pre>
           57  +
           58  +Assuming the database is named "test.db", it can then be run to analyze a
           59  +single query:
           60  +
           61  +<pre>
           62  +  ./sqlite3_expert -sql &lt;sql-query&gt; test.db
           63  +</pre>
           64  +
           65  +Or an entire text file worth of queries with:
           66  +
           67  +<pre>
           68  +  ./sqlite3_expert -file &lt;text-file&gt; test.db
           69  +</pre>
           70  +
           71  +By default, sqlite3\_expert generates index statistics using all the data in
           72  +the user database. For a large database, this may be prohibitively time
           73  +consuming. The "-sample" option may be used to configure sqlite3\_expert to
           74  +generate statistics based on an integer percentage of the user database as
           75  +follows:
           76  +
           77  +<pre>
           78  +  # Generate statistics based on 25% of the user database rows:
           79  +  ./sqlite3_expert -sample 25 -sql &lt;sql-query&gt; test.db
           80  +
           81  +  # Do not generate any statistics at all:
           82  +  ./sqlite3_expert -sample 0 -sql &lt;sql-query&gt; test.db
           83  +</pre>

Added ext/expert/expert.c.

            1  +/*
            2  +** 2017 April 07
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +*/
           13  +
           14  +
           15  +#include <sqlite3.h>
           16  +#include <stdio.h>
           17  +#include <stdlib.h>
           18  +#include <string.h>
           19  +#include "sqlite3expert.h"
           20  +
           21  +
           22  +static void option_requires_argument(const char *zOpt){
           23  +  fprintf(stderr, "Option requires an argument: %s\n", zOpt);
           24  +  exit(-3);
           25  +}
           26  +
           27  +static int option_integer_arg(const char *zVal){
           28  +  return atoi(zVal);
           29  +}
           30  +
           31  +static void usage(char **argv){
           32  +  fprintf(stderr, "\n");
           33  +  fprintf(stderr, "Usage %s ?OPTIONS? DATABASE\n", argv[0]);
           34  +  fprintf(stderr, "\n");
           35  +  fprintf(stderr, "Options are:\n");
           36  +  fprintf(stderr, "  -sql SQL   (analyze SQL statements passed as argument)\n");
           37  +  fprintf(stderr, "  -file FILE (read SQL statements from file FILE)\n");
           38  +  fprintf(stderr, "  -verbose LEVEL (integer verbosity level. default 1)\n");
           39  +  fprintf(stderr, "  -sample PERCENT (percent of db to sample. default 100)\n");
           40  +  exit(-1);
           41  +}
           42  +
           43  +static int readSqlFromFile(sqlite3expert *p, const char *zFile, char **pzErr){
           44  +  FILE *in = fopen(zFile, "rb");
           45  +  long nIn;
           46  +  size_t nRead;
           47  +  char *pBuf;
           48  +  int rc;
           49  +  if( in==0 ){
           50  +    *pzErr = sqlite3_mprintf("failed to open file %s\n", zFile);
           51  +    return SQLITE_ERROR;
           52  +  }
           53  +  fseek(in, 0, SEEK_END);
           54  +  nIn = ftell(in);
           55  +  rewind(in);
           56  +  pBuf = sqlite3_malloc64( nIn+1 );
           57  +  nRead = fread(pBuf, nIn, 1, in);
           58  +  fclose(in);
           59  +  if( nRead!=1 ){
           60  +    sqlite3_free(pBuf);
           61  +    *pzErr = sqlite3_mprintf("failed to read file %s\n", zFile);
           62  +    return SQLITE_ERROR;
           63  +  }
           64  +  pBuf[nIn] = 0;
           65  +  rc = sqlite3_expert_sql(p, pBuf, pzErr);
           66  +  sqlite3_free(pBuf);
           67  +  return rc;
           68  +}
           69  +
           70  +int main(int argc, char **argv){
           71  +  const char *zDb;
           72  +  int rc = 0;
           73  +  char *zErr = 0;
           74  +  int i;
           75  +  int iVerbose = 1;               /* -verbose option */
           76  +
           77  +  sqlite3 *db = 0;
           78  +  sqlite3expert *p = 0;
           79  +
           80  +  if( argc<2 ) usage(argv);
           81  +  zDb = argv[argc-1];
           82  +  if( zDb[0]=='-' ) usage(argv);
           83  +  rc = sqlite3_open(zDb, &db);
           84  +  if( rc!=SQLITE_OK ){
           85  +    fprintf(stderr, "Cannot open db file: %s - %s\n", zDb, sqlite3_errmsg(db));
           86  +    exit(-2);
           87  +  }
           88  +
           89  +  p = sqlite3_expert_new(db, &zErr);
           90  +  if( p==0 ){
           91  +    fprintf(stderr, "Cannot run analysis: %s\n", zErr);
           92  +    rc = 1;
           93  +  }else{
           94  +    for(i=1; i<(argc-1); i++){
           95  +      char *zArg = argv[i];
           96  +      if( zArg[0]=='-' && zArg[1]=='-' && zArg[2]!=0 ) zArg++;
           97  +      int nArg = (int)strlen(zArg);
           98  +      if( nArg>=2 && 0==sqlite3_strnicmp(zArg, "-file", nArg) ){
           99  +        if( ++i==(argc-1) ) option_requires_argument("-file");
          100  +        rc = readSqlFromFile(p, argv[i], &zErr);
          101  +      }
          102  +
          103  +      else if( nArg>=3 && 0==sqlite3_strnicmp(zArg, "-sql", nArg) ){
          104  +        if( ++i==(argc-1) ) option_requires_argument("-sql");
          105  +        rc = sqlite3_expert_sql(p, argv[i], &zErr);
          106  +      }
          107  +
          108  +      else if( nArg>=3 && 0==sqlite3_strnicmp(zArg, "-sample", nArg) ){
          109  +        int iSample;
          110  +        if( ++i==(argc-1) ) option_requires_argument("-sample");
          111  +        iSample = option_integer_arg(argv[i]);
          112  +        sqlite3_expert_config(p, EXPERT_CONFIG_SAMPLE, iSample);
          113  +      }
          114  +
          115  +      else if( nArg>=2 && 0==sqlite3_strnicmp(zArg, "-verbose", nArg) ){
          116  +        if( ++i==(argc-1) ) option_requires_argument("-verbose");
          117  +        iVerbose = option_integer_arg(argv[i]);
          118  +      }
          119  +
          120  +      else{
          121  +        usage(argv);
          122  +      }
          123  +    }
          124  +  }
          125  +
          126  +  if( rc==SQLITE_OK ){
          127  +    rc = sqlite3_expert_analyze(p, &zErr);
          128  +  }
          129  +
          130  +  if( rc==SQLITE_OK ){
          131  +    int nQuery = sqlite3_expert_count(p);
          132  +    if( iVerbose>0 ){
          133  +      const char *zCand = sqlite3_expert_report(p,0,EXPERT_REPORT_CANDIDATES);
          134  +      fprintf(stdout, "-- Candidates -------------------------------\n");
          135  +      fprintf(stdout, "%s\n", zCand);
          136  +    }
          137  +    for(i=0; i<nQuery; i++){
          138  +      const char *zSql = sqlite3_expert_report(p, i, EXPERT_REPORT_SQL);
          139  +      const char *zIdx = sqlite3_expert_report(p, i, EXPERT_REPORT_INDEXES);
          140  +      const char *zEQP = sqlite3_expert_report(p, i, EXPERT_REPORT_PLAN);
          141  +      if( zIdx==0 ) zIdx = "(no new indexes)\n";
          142  +      if( iVerbose>0 ){
          143  +        fprintf(stdout, "-- Query %d ----------------------------------\n",i+1);
          144  +        fprintf(stdout, "%s\n\n", zSql);
          145  +      }
          146  +      fprintf(stdout, "%s\n%s\n", zIdx, zEQP);
          147  +    }
          148  +  }else{
          149  +    fprintf(stderr, "Error: %s\n", zErr ? zErr : "?");
          150  +  }
          151  +
          152  +  sqlite3_expert_destroy(p);
          153  +  sqlite3_free(zErr);
          154  +  return rc;
          155  +}

Added ext/expert/expert1.test.

            1  +# 2009 Nov 11
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# The focus of this file is testing the CLI shell tool. Specifically,
           13  +# the ".recommend" command.
           14  +#
           15  +#
           16  +
           17  +# Test plan:
           18  +#
           19  +#
           20  +if {![info exists testdir]} {
           21  +  set testdir [file join [file dirname [info script]] .. .. test]
           22  +}
           23  +source $testdir/tester.tcl
           24  +set testprefix expert1
           25  +
           26  +if {$tcl_platform(platform)=="windows"} {
           27  +  set CMD "sqlite3_expert.exe"
           28  +} else {
           29  +  set CMD ".././sqlite3_expert"
           30  +}
           31  +
           32  +proc squish {txt} {
           33  +  regsub -all {[[:space:]]+} $txt { }
           34  +}
           35  +
           36  +proc do_setup_rec_test {tn setup sql res} {
           37  +  reset_db
           38  +  db eval $setup
           39  +  uplevel [list do_rec_test $tn $sql $res]
           40  +}
           41  +
           42  +foreach {tn setup} {
           43  +  1 {
           44  +    if {![file executable $CMD]} { continue }
           45  +
           46  +    proc do_rec_test {tn sql res} {
           47  +      set res [squish [string trim $res]]
           48  +      set tst [subst -nocommands { 
           49  +        squish [string trim [exec $::CMD -verbose 0 -sql {$sql;} test.db]]
           50  +      }]
           51  +      uplevel [list do_test $tn $tst $res]
           52  +    }
           53  +  }
           54  +  2 {
           55  +    if {[info commands sqlite3_expert_new]==""} { continue }
           56  +
           57  +    proc do_rec_test {tn sql res} {
           58  +      set expert [sqlite3_expert_new db]
           59  +      $expert sql $sql
           60  +      $expert analyze
           61  +
           62  +      set result [list]
           63  +      for {set i 0} {$i < [$expert count]} {incr i} {
           64  +        set idx [string trim [$expert report $i indexes]]
           65  +        if {$idx==""} {set idx "(no new indexes)"}
           66  +        lappend result $idx
           67  +        lappend result [string trim [$expert report $i plan]]
           68  +      }
           69  +
           70  +      $expert destroy
           71  +
           72  +      set tst [subst -nocommands {set {} [squish [join {$result}]]}]
           73  +      uplevel [list do_test $tn $tst [string trim [squish $res]]]
           74  +    }
           75  +  }
           76  +} {
           77  +
           78  +  eval $setup
           79  +
           80  +
           81  +do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } {
           82  +  SELECT * FROM t1
           83  +} {
           84  +  (no new indexes)
           85  +  0|0|0|SCAN TABLE t1
           86  +}
           87  +
           88  +do_setup_rec_test $tn.2 {
           89  +  CREATE TABLE t1(a, b, c);
           90  +} {
           91  +  SELECT * FROM t1 WHERE b>?;
           92  +} {
           93  +  CREATE INDEX t1_idx_00000062 ON t1(b);
           94  +  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?)
           95  +}
           96  +
           97  +do_setup_rec_test $tn.3 {
           98  +  CREATE TABLE t1(a, b, c);
           99  +} {
          100  +  SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
          101  +} {
          102  +  CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE);
          103  +  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?)
          104  +}
          105  +
          106  +do_setup_rec_test $tn.4 {
          107  +  CREATE TABLE t1(a, b, c);
          108  +} {
          109  +  SELECT a FROM t1 ORDER BY b;
          110  +} {
          111  +  CREATE INDEX t1_idx_00000062 ON t1(b);
          112  +  0|0|0|SCAN TABLE t1 USING INDEX t1_idx_00000062
          113  +}
          114  +
          115  +do_setup_rec_test $tn.5 {
          116  +  CREATE TABLE t1(a, b, c);
          117  +} {
          118  +  SELECT a FROM t1 WHERE a=? ORDER BY b;
          119  +} {
          120  +  CREATE INDEX t1_idx_000123a7 ON t1(a, b);
          121  +  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
          122  +}
          123  +
          124  +do_setup_rec_test $tn.6 {
          125  +  CREATE TABLE t1(a, b, c);
          126  +} {
          127  +  SELECT min(a) FROM t1
          128  +} {
          129  +  CREATE INDEX t1_idx_00000061 ON t1(a);
          130  +  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061
          131  +}
          132  +
          133  +do_setup_rec_test $tn.7 {
          134  +  CREATE TABLE t1(a, b, c);
          135  +} {
          136  +  SELECT * FROM t1 ORDER BY a, b, c;
          137  +} {
          138  +  CREATE INDEX t1_idx_033e95fe ON t1(a, b, c);
          139  +  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe
          140  +}
          141  +
          142  +#do_setup_rec_test $tn.1.8 {
          143  +#  CREATE TABLE t1(a, b, c);
          144  +#} {
          145  +#  SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
          146  +#} {
          147  +#  CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c);
          148  +#  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222
          149  +#}
          150  +
          151  +do_setup_rec_test $tn.8.1 {
          152  +  CREATE TABLE t1(a COLLATE NOCase, b, c);
          153  +} {
          154  +  SELECT * FROM t1 WHERE a=?
          155  +} {
          156  +  CREATE INDEX t1_idx_00000061 ON t1(a);
          157  +  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?)
          158  +}
          159  +do_setup_rec_test $tn.8.2 {
          160  +  CREATE TABLE t1(a, b COLLATE nocase, c);
          161  +} {
          162  +  SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC;
          163  +} {
          164  +  CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c);
          165  +  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5cb97285
          166  +}
          167  +
          168  +
          169  +# Tables with names that require quotes.
          170  +#
          171  +do_setup_rec_test $tn.9.1 {
          172  +  CREATE TABLE "t t"(a, b, c);
          173  +} {
          174  +  SELECT * FROM "t t" WHERE a=?
          175  +} {
          176  +  CREATE INDEX 't t_idx_00000061' ON 't t'(a);
          177  +  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) 
          178  +}
          179  +
          180  +do_setup_rec_test $tn.9.2 {
          181  +  CREATE TABLE "t t"(a, b, c);
          182  +} {
          183  +  SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
          184  +} {
          185  +  CREATE INDEX 't t_idx_00000062' ON 't t'(b);
          186  +  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?)
          187  +}
          188  +
          189  +# Columns with names that require quotes.
          190  +#
          191  +do_setup_rec_test $tn.10.1 {
          192  +  CREATE TABLE t3(a, "b b", c);
          193  +} {
          194  +  SELECT * FROM t3 WHERE "b b" = ?
          195  +} {
          196  +  CREATE INDEX t3_idx_00050c52 ON t3('b b');
          197  +  0|0|0|SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?)
          198  +}
          199  +
          200  +do_setup_rec_test $tn.10.2 {
          201  +  CREATE TABLE t3(a, "b b", c);
          202  +} {
          203  +  SELECT * FROM t3 ORDER BY "b b"
          204  +} {
          205  +  CREATE INDEX t3_idx_00050c52 ON t3('b b');
          206  +  0|0|0|SCAN TABLE t3 USING INDEX t3_idx_00050c52
          207  +}
          208  +
          209  +# Transitive constraints
          210  +#
          211  +do_setup_rec_test $tn.11.1 {
          212  +  CREATE TABLE t5(a, b);
          213  +  CREATE TABLE t6(c, d);
          214  +} {
          215  +  SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
          216  +} {
          217  +  CREATE INDEX t5_idx_000123a7 ON t5(a, b);
          218  +  CREATE INDEX t6_idx_00000063 ON t6(c);
          219  +  0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 
          220  +  0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
          221  +}
          222  +
          223  +# OR terms.
          224  +#
          225  +do_setup_rec_test $tn.12.1 {
          226  +  CREATE TABLE t7(a, b);
          227  +} {
          228  +  SELECT * FROM t7 WHERE a=? OR b=?
          229  +} {
          230  +  CREATE INDEX t7_idx_00000062 ON t7(b);
          231  +  CREATE INDEX t7_idx_00000061 ON t7(a);
          232  +  0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 
          233  +  0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
          234  +}
          235  +
          236  +# rowid terms.
          237  +#
          238  +do_setup_rec_test $tn.13.1 {
          239  +  CREATE TABLE t8(a, b);
          240  +} {
          241  +  SELECT * FROM t8 WHERE rowid=?
          242  +} {
          243  +  (no new indexes)
          244  +  0|0|0|SEARCH TABLE t8 USING INTEGER PRIMARY KEY (rowid=?)
          245  +}
          246  +do_setup_rec_test $tn.13.2 {
          247  +  CREATE TABLE t8(a, b);
          248  +} {
          249  +  SELECT * FROM t8 ORDER BY rowid
          250  +} {
          251  +  (no new indexes)
          252  +  0|0|0|SCAN TABLE t8
          253  +}
          254  +do_setup_rec_test $tn.13.3 {
          255  +  CREATE TABLE t8(a, b);
          256  +} {
          257  +  SELECT * FROM t8 WHERE a=? ORDER BY rowid
          258  +} {
          259  +  CREATE INDEX t8_idx_00000061 ON t8(a); 
          260  +  0|0|0|SEARCH TABLE t8 USING INDEX t8_idx_00000061 (a=?)
          261  +}
          262  +
          263  +# Triggers
          264  +#
          265  +do_setup_rec_test $tn.14 {
          266  +  CREATE TABLE t9(a, b, c);
          267  +  CREATE TABLE t10(a, b, c);
          268  +  CREATE TRIGGER t9t AFTER INSERT ON t9 BEGIN
          269  +    UPDATE t10 SET a=new.a WHERE b = new.b;
          270  +  END;
          271  +} {
          272  +  INSERT INTO t9 VALUES(?, ?, ?);
          273  +} {
          274  +  CREATE INDEX t10_idx_00000062 ON t10(b); 
          275  +  0|0|0|SEARCH TABLE t10 USING INDEX t10_idx_00000062 (b=?)
          276  +}
          277  +
          278  +do_setup_rec_test $tn.15 {
          279  +  CREATE TABLE t1(a, b);
          280  +  CREATE TABLE t2(c, d);
          281  +
          282  +  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
          283  +  INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s;
          284  +
          285  +  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
          286  +  INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s;
          287  +} {
          288  +  SELECT * FROM t2, t1 WHERE b=? AND d=? AND t2.rowid=t1.rowid
          289  +} {
          290  +  CREATE INDEX t2_idx_00000064 ON t2(d);
          291  +  0|0|0|SEARCH TABLE t2 USING INDEX t2_idx_00000064 (d=?) 
          292  +  0|1|1|SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
          293  +}
          294  +
          295  +}
          296  +
          297  +proc do_candidates_test {tn sql res} {
          298  +  set res [squish [string trim $res]]
          299  +
          300  +  set expert [sqlite3_expert_new db]
          301  +  $expert sql $sql
          302  +  $expert analyze
          303  +
          304  +  set candidates [squish [string trim [$expert report 0 candidates]]]
          305  +  $expert destroy
          306  +
          307  +  uplevel [list do_test $tn [list set {} $candidates] $res]
          308  +}
          309  +
          310  +
          311  +reset_db
          312  +do_execsql_test 3.0 {
          313  +  CREATE TABLE t1(a, b);
          314  +  CREATE TABLE t2(c, d);
          315  +
          316  +  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
          317  +  INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s;
          318  +
          319  +  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
          320  +  INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s;
          321  +}
          322  +do_candidates_test 3.1 {
          323  +  SELECT * FROM t1,t2 WHERE (b=? OR a=?) AND (c=? OR d=?)
          324  +} {
          325  +  CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20 
          326  +  CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50 
          327  +  CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20 
          328  +  CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5
          329  +}
          330  +
          331  +do_candidates_test 3.2 {
          332  +  SELECT * FROM t1,t2 WHERE a=? AND b=? AND c=? AND d=?
          333  +} {
          334  +  CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 17
          335  +  CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5
          336  +}
          337  +
          338  +do_execsql_test 3.2 {
          339  +  CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50 
          340  +  CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20 
          341  +  CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 16
          342  +
          343  +  CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20 
          344  +  CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5
          345  +  CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5
          346  +
          347  +  ANALYZE;
          348  +  SELECT * FROM sqlite_stat1 ORDER BY 1, 2;
          349  +} {
          350  +  t1 t1_idx_00000061 {100 50} 
          351  +  t1 t1_idx_00000062 {100 20}
          352  +  t1 t1_idx_000123a7 {100 50 17}
          353  +  t2 t2_idx_00000063 {100 20} 
          354  +  t2 t2_idx_00000064 {100 5} 
          355  +  t2 t2_idx_0001295b {100 20 5}
          356  +}
          357  +
          358  +
          359  +finish_test
          360  +

Added ext/expert/sqlite3expert.c.

            1  +/*
            2  +** 2017 April 09
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +*/
           13  +#include "sqlite3expert.h"
           14  +#include <assert.h>
           15  +#include <string.h>
           16  +#include <stdio.h>
           17  +
           18  +typedef sqlite3_int64 i64;
           19  +typedef sqlite3_uint64 u64;
           20  +
           21  +typedef struct IdxColumn IdxColumn;
           22  +typedef struct IdxConstraint IdxConstraint;
           23  +typedef struct IdxScan IdxScan;
           24  +typedef struct IdxStatement IdxStatement;
           25  +typedef struct IdxTable IdxTable;
           26  +typedef struct IdxWrite IdxWrite;
           27  +
           28  +#define STRLEN  (int)strlen
           29  +
           30  +/*
           31  +** A temp table name that we assume no user database will actually use.
           32  +** If this assumption proves incorrect triggers on the table with the
           33  +** conflicting name will be ignored.
           34  +*/
           35  +#define UNIQUE_TABLE_NAME "t592690916721053953805701627921227776"
           36  +
           37  +/*
           38  +** A single constraint. Equivalent to either "col = ?" or "col < ?" (or
           39  +** any other type of single-ended range constraint on a column).
           40  +**
           41  +** pLink:
           42  +**   Used to temporarily link IdxConstraint objects into lists while
           43  +**   creating candidate indexes.
           44  +*/
           45  +struct IdxConstraint {
           46  +  char *zColl;                    /* Collation sequence */
           47  +  int bRange;                     /* True for range, false for eq */
           48  +  int iCol;                       /* Constrained table column */
           49  +  int bFlag;                      /* Used by idxFindCompatible() */
           50  +  int bDesc;                      /* True if ORDER BY <expr> DESC */
           51  +  IdxConstraint *pNext;           /* Next constraint in pEq or pRange list */
           52  +  IdxConstraint *pLink;           /* See above */
           53  +};
           54  +
           55  +/*
           56  +** A single scan of a single table.
           57  +*/
           58  +struct IdxScan {
           59  +  IdxTable *pTab;                 /* Associated table object */
           60  +  int iDb;                        /* Database containing table zTable */
           61  +  i64 covering;                   /* Mask of columns required for cov. index */
           62  +  IdxConstraint *pOrder;          /* ORDER BY columns */
           63  +  IdxConstraint *pEq;             /* List of == constraints */
           64  +  IdxConstraint *pRange;          /* List of < constraints */
           65  +  IdxScan *pNextScan;             /* Next IdxScan object for same analysis */
           66  +};
           67  +
           68  +/*
           69  +** Information regarding a single database table. Extracted from 
           70  +** "PRAGMA table_info" by function idxGetTableInfo().
           71  +*/
           72  +struct IdxColumn {
           73  +  char *zName;
           74  +  char *zColl;
           75  +  int iPk;
           76  +};
           77  +struct IdxTable {
           78  +  int nCol;
           79  +  char *zName;                    /* Table name */
           80  +  IdxColumn *aCol;
           81  +  IdxTable *pNext;                /* Next table in linked list of all tables */
           82  +};
           83  +
           84  +/*
           85  +** An object of the following type is created for each unique table/write-op
           86  +** seen. The objects are stored in a singly-linked list beginning at
           87  +** sqlite3expert.pWrite.
           88  +*/
           89  +struct IdxWrite {
           90  +  IdxTable *pTab;
           91  +  int eOp;                        /* SQLITE_UPDATE, DELETE or INSERT */
           92  +  IdxWrite *pNext;
           93  +};
           94  +
           95  +/*
           96  +** Each statement being analyzed is represented by an instance of this
           97  +** structure.
           98  +*/
           99  +struct IdxStatement {
          100  +  int iId;                        /* Statement number */
          101  +  char *zSql;                     /* SQL statement */
          102  +  char *zIdx;                     /* Indexes */
          103  +  char *zEQP;                     /* Plan */
          104  +  IdxStatement *pNext;
          105  +};
          106  +
          107  +
          108  +/*
          109  +** A hash table for storing strings. With space for a payload string
          110  +** with each entry. Methods are:
          111  +**
          112  +**   idxHashInit()
          113  +**   idxHashClear()
          114  +**   idxHashAdd()
          115  +**   idxHashSearch()
          116  +*/
          117  +#define IDX_HASH_SIZE 1023
          118  +typedef struct IdxHashEntry IdxHashEntry;
          119  +typedef struct IdxHash IdxHash;
          120  +struct IdxHashEntry {
          121  +  char *zKey;                     /* nul-terminated key */
          122  +  char *zVal;                     /* nul-terminated value string */
          123  +  char *zVal2;                    /* nul-terminated value string 2 */
          124  +  IdxHashEntry *pHashNext;        /* Next entry in same hash bucket */
          125  +  IdxHashEntry *pNext;            /* Next entry in hash */
          126  +};
          127  +struct IdxHash {
          128  +  IdxHashEntry *pFirst;
          129  +  IdxHashEntry *aHash[IDX_HASH_SIZE];
          130  +};
          131  +
          132  +/*
          133  +** sqlite3expert object.
          134  +*/
          135  +struct sqlite3expert {
          136  +  int iSample;                    /* Percentage of tables to sample for stat1 */
          137  +  sqlite3 *db;                    /* User database */
          138  +  sqlite3 *dbm;                   /* In-memory db for this analysis */
          139  +  sqlite3 *dbv;                   /* Vtab schema for this analysis */
          140  +  IdxTable *pTable;               /* List of all IdxTable objects */
          141  +  IdxScan *pScan;                 /* List of scan objects */
          142  +  IdxWrite *pWrite;               /* List of write objects */
          143  +  IdxStatement *pStatement;       /* List of IdxStatement objects */
          144  +  int bRun;                       /* True once analysis has run */
          145  +  char **pzErrmsg;
          146  +  int rc;                         /* Error code from whereinfo hook */
          147  +  IdxHash hIdx;                   /* Hash containing all candidate indexes */
          148  +  char *zCandidates;              /* For EXPERT_REPORT_CANDIDATES */
          149  +};
          150  +
          151  +
          152  +/*
          153  +** Allocate and return nByte bytes of zeroed memory using sqlite3_malloc(). 
          154  +** If the allocation fails, set *pRc to SQLITE_NOMEM and return NULL.
          155  +*/
          156  +static void *idxMalloc(int *pRc, int nByte){
          157  +  void *pRet;
          158  +  assert( *pRc==SQLITE_OK );
          159  +  assert( nByte>0 );
          160  +  pRet = sqlite3_malloc(nByte);
          161  +  if( pRet ){
          162  +    memset(pRet, 0, nByte);
          163  +  }else{
          164  +    *pRc = SQLITE_NOMEM;
          165  +  }
          166  +  return pRet;
          167  +}
          168  +
          169  +/*
          170  +** Initialize an IdxHash hash table.
          171  +*/
          172  +static void idxHashInit(IdxHash *pHash){
          173  +  memset(pHash, 0, sizeof(IdxHash));
          174  +}
          175  +
          176  +/*
          177  +** Reset an IdxHash hash table.
          178  +*/
          179  +static void idxHashClear(IdxHash *pHash){
          180  +  int i;
          181  +  for(i=0; i<IDX_HASH_SIZE; i++){
          182  +    IdxHashEntry *pEntry;
          183  +    IdxHashEntry *pNext;
          184  +    for(pEntry=pHash->aHash[i]; pEntry; pEntry=pNext){
          185  +      pNext = pEntry->pHashNext;
          186  +      sqlite3_free(pEntry->zVal2);
          187  +      sqlite3_free(pEntry);
          188  +    }
          189  +  }
          190  +  memset(pHash, 0, sizeof(IdxHash));
          191  +}
          192  +
          193  +/*
          194  +** Return the index of the hash bucket that the string specified by the
          195  +** arguments to this function belongs.
          196  +*/
          197  +static int idxHashString(const char *z, int n){
          198  +  unsigned int ret = 0;
          199  +  int i;
          200  +  for(i=0; i<n; i++){
          201  +    ret += (ret<<3) + (unsigned char)(z[i]);
          202  +  }
          203  +  return (int)(ret % IDX_HASH_SIZE);
          204  +}
          205  +
          206  +/*
          207  +** If zKey is already present in the hash table, return non-zero and do
          208  +** nothing. Otherwise, add an entry with key zKey and payload string zVal to
          209  +** the hash table passed as the second argument. 
          210  +*/
          211  +static int idxHashAdd(
          212  +  int *pRc, 
          213  +  IdxHash *pHash, 
          214  +  const char *zKey,
          215  +  const char *zVal
          216  +){
          217  +  int nKey = STRLEN(zKey);
          218  +  int iHash = idxHashString(zKey, nKey);
          219  +  int nVal = (zVal ? STRLEN(zVal) : 0);
          220  +  IdxHashEntry *pEntry;
          221  +  assert( iHash>=0 );
          222  +  for(pEntry=pHash->aHash[iHash]; pEntry; pEntry=pEntry->pHashNext){
          223  +    if( STRLEN(pEntry->zKey)==nKey && 0==memcmp(pEntry->zKey, zKey, nKey) ){
          224  +      return 1;
          225  +    }
          226  +  }
          227  +  pEntry = idxMalloc(pRc, sizeof(IdxHashEntry) + nKey+1 + nVal+1);
          228  +  if( pEntry ){
          229  +    pEntry->zKey = (char*)&pEntry[1];
          230  +    memcpy(pEntry->zKey, zKey, nKey);
          231  +    if( zVal ){
          232  +      pEntry->zVal = &pEntry->zKey[nKey+1];
          233  +      memcpy(pEntry->zVal, zVal, nVal);
          234  +    }
          235  +    pEntry->pHashNext = pHash->aHash[iHash];
          236  +    pHash->aHash[iHash] = pEntry;
          237  +
          238  +    pEntry->pNext = pHash->pFirst;
          239  +    pHash->pFirst = pEntry;
          240  +  }
          241  +  return 0;
          242  +}
          243  +
          244  +/*
          245  +** If zKey/nKey is present in the hash table, return a pointer to the 
          246  +** hash-entry object.
          247  +*/
          248  +static IdxHashEntry *idxHashFind(IdxHash *pHash, const char *zKey, int nKey){
          249  +  int iHash;
          250  +  IdxHashEntry *pEntry;
          251  +  if( nKey<0 ) nKey = STRLEN(zKey);
          252  +  iHash = idxHashString(zKey, nKey);
          253  +  assert( iHash>=0 );
          254  +  for(pEntry=pHash->aHash[iHash]; pEntry; pEntry=pEntry->pHashNext){
          255  +    if( STRLEN(pEntry->zKey)==nKey && 0==memcmp(pEntry->zKey, zKey, nKey) ){
          256  +      return pEntry;
          257  +    }
          258  +  }
          259  +  return 0;
          260  +}
          261  +
          262  +/*
          263  +** If the hash table contains an entry with a key equal to the string
          264  +** passed as the final two arguments to this function, return a pointer
          265  +** to the payload string. Otherwise, if zKey/nKey is not present in the
          266  +** hash table, return NULL.
          267  +*/
          268  +static const char *idxHashSearch(IdxHash *pHash, const char *zKey, int nKey){
          269  +  IdxHashEntry *pEntry = idxHashFind(pHash, zKey, nKey);
          270  +  if( pEntry ) return pEntry->zVal;
          271  +  return 0;
          272  +}
          273  +
          274  +/*
          275  +** Allocate and return a new IdxConstraint object. Set the IdxConstraint.zColl
          276  +** variable to point to a copy of nul-terminated string zColl.
          277  +*/
          278  +static IdxConstraint *idxNewConstraint(int *pRc, const char *zColl){
          279  +  IdxConstraint *pNew;
          280  +  int nColl = STRLEN(zColl);
          281  +
          282  +  assert( *pRc==SQLITE_OK );
          283  +  pNew = (IdxConstraint*)idxMalloc(pRc, sizeof(IdxConstraint) * nColl + 1);
          284  +  if( pNew ){
          285  +    pNew->zColl = (char*)&pNew[1];
          286  +    memcpy(pNew->zColl, zColl, nColl+1);
          287  +  }
          288  +  return pNew;
          289  +}
          290  +
          291  +/*
          292  +** An error associated with database handle db has just occurred. Pass
          293  +** the error message to callback function xOut.
          294  +*/
          295  +static void idxDatabaseError(
          296  +  sqlite3 *db,                    /* Database handle */
          297  +  char **pzErrmsg                 /* Write error here */
          298  +){
          299  +  *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
          300  +}
          301  +
          302  +/*
          303  +** Prepare an SQL statement.
          304  +*/
          305  +static int idxPrepareStmt(
          306  +  sqlite3 *db,                    /* Database handle to compile against */
          307  +  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
          308  +  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
          309  +  const char *zSql                /* SQL statement to compile */
          310  +){
          311  +  int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0);
          312  +  if( rc!=SQLITE_OK ){
          313  +    *ppStmt = 0;
          314  +    idxDatabaseError(db, pzErrmsg);
          315  +  }
          316  +  return rc;
          317  +}
          318  +
          319  +/*
          320  +** Prepare an SQL statement using the results of a printf() formatting.
          321  +*/
          322  +static int idxPrintfPrepareStmt(
          323  +  sqlite3 *db,                    /* Database handle to compile against */
          324  +  sqlite3_stmt **ppStmt,          /* OUT: Compiled SQL statement */
          325  +  char **pzErrmsg,                /* OUT: sqlite3_malloc()ed error message */
          326  +  const char *zFmt,               /* printf() format of SQL statement */
          327  +  ...                             /* Trailing printf() arguments */
          328  +){
          329  +  va_list ap;
          330  +  int rc;
          331  +  char *zSql;
          332  +  va_start(ap, zFmt);
          333  +  zSql = sqlite3_vmprintf(zFmt, ap);
          334  +  if( zSql==0 ){
          335  +    rc = SQLITE_NOMEM;
          336  +  }else{
          337  +    rc = idxPrepareStmt(db, ppStmt, pzErrmsg, zSql);
          338  +    sqlite3_free(zSql);
          339  +  }
          340  +  va_end(ap);
          341  +  return rc;
          342  +}
          343  +
          344  +
          345  +/*************************************************************************
          346  +** Beginning of virtual table implementation.
          347  +*/
          348  +typedef struct ExpertVtab ExpertVtab;
          349  +struct ExpertVtab {
          350  +  sqlite3_vtab base;
          351  +  IdxTable *pTab;
          352  +  sqlite3expert *pExpert;
          353  +};
          354  +
          355  +typedef struct ExpertCsr ExpertCsr;
          356  +struct ExpertCsr {
          357  +  sqlite3_vtab_cursor base;
          358  +  sqlite3_stmt *pData;
          359  +};
          360  +
          361  +static char *expertDequote(const char *zIn){
          362  +  int n = STRLEN(zIn);
          363  +  char *zRet = sqlite3_malloc(n);
          364  +
          365  +  assert( zIn[0]=='\'' );
          366  +  assert( zIn[n-1]=='\'' );
          367  +
          368  +  if( zRet ){
          369  +    int iOut = 0;
          370  +    int iIn = 0;
          371  +    for(iIn=1; iIn<(n-1); iIn++){
          372  +      if( zIn[iIn]=='\'' ){
          373  +        assert( zIn[iIn+1]=='\'' );
          374  +        iIn++;
          375  +      }
          376  +      zRet[iOut++] = zIn[iIn];
          377  +    }
          378  +    zRet[iOut] = '\0';
          379  +  }
          380  +
          381  +  return zRet;
          382  +}
          383  +
          384  +/* 
          385  +** This function is the implementation of both the xConnect and xCreate
          386  +** methods of the r-tree virtual table.
          387  +**
          388  +**   argv[0]   -> module name
          389  +**   argv[1]   -> database name
          390  +**   argv[2]   -> table name
          391  +**   argv[...] -> column names...
          392  +*/
          393  +static int expertConnect(
          394  +  sqlite3 *db,
          395  +  void *pAux,
          396  +  int argc, const char *const*argv,
          397  +  sqlite3_vtab **ppVtab,
          398  +  char **pzErr
          399  +){
          400  +  sqlite3expert *pExpert = (sqlite3expert*)pAux;
          401  +  ExpertVtab *p = 0;
          402  +  int rc;
          403  +
          404  +  if( argc!=4 ){
          405  +    *pzErr = sqlite3_mprintf("internal error!");
          406  +    rc = SQLITE_ERROR;
          407  +  }else{
          408  +    char *zCreateTable = expertDequote(argv[3]);
          409  +    if( zCreateTable ){
          410  +      rc = sqlite3_declare_vtab(db, zCreateTable);
          411  +      if( rc==SQLITE_OK ){
          412  +        p = idxMalloc(&rc, sizeof(ExpertVtab));
          413  +      }
          414  +      if( rc==SQLITE_OK ){
          415  +        p->pExpert = pExpert;
          416  +        p->pTab = pExpert->pTable;
          417  +        assert( sqlite3_stricmp(p->pTab->zName, argv[2])==0 );
          418  +      }
          419  +      sqlite3_free(zCreateTable);
          420  +    }else{
          421  +      rc = SQLITE_NOMEM;
          422  +    }
          423  +  }
          424  +
          425  +  *ppVtab = (sqlite3_vtab*)p;
          426  +  return rc;
          427  +}
          428  +
          429  +static int expertDisconnect(sqlite3_vtab *pVtab){
          430  +  ExpertVtab *p = (ExpertVtab*)pVtab;
          431  +  sqlite3_free(p);
          432  +  return SQLITE_OK;
          433  +}
          434  +
          435  +static int expertBestIndex(sqlite3_vtab *pVtab, sqlite3_index_info *pIdxInfo){
          436  +  ExpertVtab *p = (ExpertVtab*)pVtab;
          437  +  sqlite3 *dbv = p->pExpert->dbv;
          438  +  int rc = SQLITE_OK;
          439  +  int n = 0;
          440  +  IdxScan *pScan;
          441  +  const int opmask = 
          442  +    SQLITE_INDEX_CONSTRAINT_EQ | SQLITE_INDEX_CONSTRAINT_GT |
          443  +    SQLITE_INDEX_CONSTRAINT_LT | SQLITE_INDEX_CONSTRAINT_GE |
          444  +    SQLITE_INDEX_CONSTRAINT_LE;
          445  +
          446  +  pScan = idxMalloc(&rc, sizeof(IdxScan));
          447  +  if( pScan ){
          448  +    int i;
          449  +
          450  +    /* Link the new scan object into the list */
          451  +    pScan->pTab = p->pTab;
          452  +    pScan->pNextScan = p->pExpert->pScan;
          453  +    p->pExpert->pScan = pScan;
          454  +
          455  +    /* Add the constraints to the IdxScan object */
          456  +    for(i=0; i<pIdxInfo->nConstraint; i++){
          457  +      struct sqlite3_index_constraint *pCons = &pIdxInfo->aConstraint[i];
          458  +      if( pCons->usable 
          459  +       && pCons->iColumn>=0 
          460  +       && p->pTab->aCol[pCons->iColumn].iPk==0
          461  +       && (pCons->op & opmask) 
          462  +      ){
          463  +        IdxConstraint *pNew;
          464  +        const char *zColl = sqlite3_vtab_collation(dbv, i);
          465  +        pNew = idxNewConstraint(&rc, zColl);
          466  +        if( pNew ){
          467  +          pNew->iCol = pCons->iColumn;
          468  +          if( pCons->op==SQLITE_INDEX_CONSTRAINT_EQ ){
          469  +            pNew->pNext = pScan->pEq;
          470  +            pScan->pEq = pNew;
          471  +          }else{
          472  +            pNew->bRange = 1;
          473  +            pNew->pNext = pScan->pRange;
          474  +            pScan->pRange = pNew;
          475  +          }
          476  +        }
          477  +        n++;
          478  +        pIdxInfo->aConstraintUsage[i].argvIndex = n;
          479  +      }
          480  +    }
          481  +
          482  +    /* Add the ORDER BY to the IdxScan object */
          483  +    for(i=pIdxInfo->nOrderBy-1; i>=0; i--){
          484  +      int iCol = pIdxInfo->aOrderBy[i].iColumn;
          485  +      if( iCol>=0 ){
          486  +        IdxConstraint *pNew = idxNewConstraint(&rc, p->pTab->aCol[iCol].zColl);
          487  +        if( pNew ){
          488  +          pNew->iCol = iCol;
          489  +          pNew->bDesc = pIdxInfo->aOrderBy[i].desc;
          490  +          pNew->pNext = pScan->pOrder;
          491  +          pNew->pLink = pScan->pOrder;
          492  +          pScan->pOrder = pNew;
          493  +          n++;
          494  +        }
          495  +      }
          496  +    }
          497  +  }
          498  +
          499  +  pIdxInfo->estimatedCost = 1000000.0 / n;
          500  +  return rc;
          501  +}
          502  +
          503  +static int expertUpdate(
          504  +  sqlite3_vtab *pVtab, 
          505  +  int nData, 
          506  +  sqlite3_value **azData, 
          507  +  sqlite_int64 *pRowid
          508  +){
          509  +  return SQLITE_OK;
          510  +}
          511  +
          512  +/* 
          513  +** Virtual table module xOpen method.
          514  +*/
          515  +static int expertOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
          516  +  int rc = SQLITE_OK;
          517  +  ExpertCsr *pCsr;
          518  +  pCsr = idxMalloc(&rc, sizeof(ExpertCsr));
          519  +  *ppCursor = (sqlite3_vtab_cursor*)pCsr;
          520  +  return rc;
          521  +}
          522  +
          523  +/* 
          524  +** Virtual table module xClose method.
          525  +*/
          526  +static int expertClose(sqlite3_vtab_cursor *cur){
          527  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          528  +  sqlite3_finalize(pCsr->pData);
          529  +  sqlite3_free(pCsr);
          530  +  return SQLITE_OK;
          531  +}
          532  +
          533  +/*
          534  +** Virtual table module xEof method.
          535  +**
          536  +** Return non-zero if the cursor does not currently point to a valid 
          537  +** record (i.e if the scan has finished), or zero otherwise.
          538  +*/
          539  +static int expertEof(sqlite3_vtab_cursor *cur){
          540  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          541  +  return pCsr->pData==0;
          542  +}
          543  +
          544  +/* 
          545  +** Virtual table module xNext method.
          546  +*/
          547  +static int expertNext(sqlite3_vtab_cursor *cur){
          548  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          549  +  int rc = SQLITE_OK;
          550  +
          551  +  assert( pCsr->pData );
          552  +  rc = sqlite3_step(pCsr->pData);
          553  +  if( rc!=SQLITE_ROW ){
          554  +    rc = sqlite3_finalize(pCsr->pData);
          555  +    pCsr->pData = 0;
          556  +  }else{
          557  +    rc = SQLITE_OK;
          558  +  }
          559  +
          560  +  return rc;
          561  +}
          562  +
          563  +/* 
          564  +** Virtual table module xRowid method.
          565  +*/
          566  +static int expertRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          567  +  *pRowid = 0;
          568  +  return SQLITE_OK;
          569  +}
          570  +
          571  +/* 
          572  +** Virtual table module xColumn method.
          573  +*/
          574  +static int expertColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){
          575  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          576  +  sqlite3_value *pVal;
          577  +  pVal = sqlite3_column_value(pCsr->pData, i);
          578  +  if( pVal ){
          579  +    sqlite3_result_value(ctx, pVal);
          580  +  }
          581  +  return SQLITE_OK;
          582  +}
          583  +
          584  +/* 
          585  +** Virtual table module xFilter method.
          586  +*/
          587  +static int expertFilter(
          588  +  sqlite3_vtab_cursor *cur, 
          589  +  int idxNum, const char *idxStr,
          590  +  int argc, sqlite3_value **argv
          591  +){
          592  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          593  +  ExpertVtab *pVtab = (ExpertVtab*)(cur->pVtab);
          594  +  sqlite3expert *pExpert = pVtab->pExpert;
          595  +  int rc;
          596  +
          597  +  rc = sqlite3_finalize(pCsr->pData);
          598  +  pCsr->pData = 0;
          599  +  if( rc==SQLITE_OK ){
          600  +    rc = idxPrintfPrepareStmt(pExpert->db, &pCsr->pData, &pVtab->base.zErrMsg,
          601  +        "SELECT * FROM main.%Q WHERE sample()", pVtab->pTab->zName
          602  +    );
          603  +  }
          604  +
          605  +  if( rc==SQLITE_OK ){
          606  +    rc = expertNext(cur);
          607  +  }
          608  +  return rc;
          609  +}
          610  +
          611  +static int idxRegisterVtab(sqlite3expert *p){
          612  +  static sqlite3_module expertModule = {
          613  +    2,                            /* iVersion */
          614  +    expertConnect,                /* xCreate - create a table */
          615  +    expertConnect,                /* xConnect - connect to an existing table */
          616  +    expertBestIndex,              /* xBestIndex - Determine search strategy */
          617  +    expertDisconnect,             /* xDisconnect - Disconnect from a table */
          618  +    expertDisconnect,             /* xDestroy - Drop a table */
          619  +    expertOpen,                   /* xOpen - open a cursor */
          620  +    expertClose,                  /* xClose - close a cursor */
          621  +    expertFilter,                 /* xFilter - configure scan constraints */
          622  +    expertNext,                   /* xNext - advance a cursor */
          623  +    expertEof,                    /* xEof */
          624  +    expertColumn,                 /* xColumn - read data */
          625  +    expertRowid,                  /* xRowid - read data */
          626  +    expertUpdate,                 /* xUpdate - write data */
          627  +    0,                            /* xBegin - begin transaction */
          628  +    0,                            /* xSync - sync transaction */
          629  +    0,                            /* xCommit - commit transaction */
          630  +    0,                            /* xRollback - rollback transaction */
          631  +    0,                            /* xFindFunction - function overloading */
          632  +    0,                            /* xRename - rename the table */
          633  +    0,                            /* xSavepoint */
          634  +    0,                            /* xRelease */
          635  +    0,                            /* xRollbackTo */
          636  +  };
          637  +
          638  +  return sqlite3_create_module(p->dbv, "expert", &expertModule, (void*)p);
          639  +}
          640  +/*
          641  +** End of virtual table implementation.
          642  +*************************************************************************/
          643  +/*
          644  +** Finalize SQL statement pStmt. If (*pRc) is SQLITE_OK when this function
          645  +** is called, set it to the return value of sqlite3_finalize() before
          646  +** returning. Otherwise, discard the sqlite3_finalize() return value.
          647  +*/
          648  +static void idxFinalize(int *pRc, sqlite3_stmt *pStmt){
          649  +  int rc = sqlite3_finalize(pStmt);
          650  +  if( *pRc==SQLITE_OK ) *pRc = rc;
          651  +}
          652  +
          653  +/*
          654  +** Attempt to allocate an IdxTable structure corresponding to table zTab
          655  +** in the main database of connection db. If successful, set (*ppOut) to
          656  +** point to the new object and return SQLITE_OK. Otherwise, return an
          657  +** SQLite error code and set (*ppOut) to NULL. In this case *pzErrmsg may be
          658  +** set to point to an error string.
          659  +**
          660  +** It is the responsibility of the caller to eventually free either the
          661  +** IdxTable object or error message using sqlite3_free().
          662  +*/
          663  +static int idxGetTableInfo(
          664  +  sqlite3 *db,                    /* Database connection to read details from */
          665  +  const char *zTab,               /* Table name */
          666  +  IdxTable **ppOut,               /* OUT: New object (if successful) */
          667  +  char **pzErrmsg                 /* OUT: Error message (if not) */
          668  +){
          669  +  sqlite3_stmt *p1 = 0;
          670  +  int nCol = 0;
          671  +  int nTab = STRLEN(zTab);
          672  +  int nByte = sizeof(IdxTable) + nTab + 1;
          673  +  IdxTable *pNew = 0;
          674  +  int rc, rc2;
          675  +  char *pCsr = 0;
          676  +
          677  +  rc = idxPrintfPrepareStmt(db, &p1, pzErrmsg, "PRAGMA table_info=%Q", zTab);
          678  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
          679  +    const char *zCol = (const char*)sqlite3_column_text(p1, 1);
          680  +    nByte += 1 + STRLEN(zCol);
          681  +    rc = sqlite3_table_column_metadata(
          682  +        db, "main", zTab, zCol, 0, &zCol, 0, 0, 0
          683  +    );
          684  +    nByte += 1 + STRLEN(zCol);
          685  +    nCol++;
          686  +  }
          687  +  rc2 = sqlite3_reset(p1);
          688  +  if( rc==SQLITE_OK ) rc = rc2;
          689  +
          690  +  nByte += sizeof(IdxColumn) * nCol;
          691  +  if( rc==SQLITE_OK ){
          692  +    pNew = idxMalloc(&rc, nByte);
          693  +  }
          694  +  if( rc==SQLITE_OK ){
          695  +    pNew->aCol = (IdxColumn*)&pNew[1];
          696  +    pNew->nCol = nCol;
          697  +    pCsr = (char*)&pNew->aCol[nCol];
          698  +  }
          699  +
          700  +  nCol = 0;
          701  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
          702  +    const char *zCol = (const char*)sqlite3_column_text(p1, 1);
          703  +    int nCopy = STRLEN(zCol) + 1;
          704  +    pNew->aCol[nCol].zName = pCsr;
          705  +    pNew->aCol[nCol].iPk = sqlite3_column_int(p1, 5);
          706  +    memcpy(pCsr, zCol, nCopy);
          707  +    pCsr += nCopy;
          708  +
          709  +    rc = sqlite3_table_column_metadata(
          710  +        db, "main", zTab, zCol, 0, &zCol, 0, 0, 0
          711  +    );
          712  +    if( rc==SQLITE_OK ){
          713  +      nCopy = STRLEN(zCol) + 1;
          714  +      pNew->aCol[nCol].zColl = pCsr;
          715  +      memcpy(pCsr, zCol, nCopy);
          716  +      pCsr += nCopy;
          717  +    }
          718  +
          719  +    nCol++;
          720  +  }
          721  +  idxFinalize(&rc, p1);
          722  +
          723  +  if( rc!=SQLITE_OK ){
          724  +    sqlite3_free(pNew);
          725  +    pNew = 0;
          726  +  }else{
          727  +    pNew->zName = pCsr;
          728  +    memcpy(pNew->zName, zTab, nTab+1);
          729  +  }
          730  +
          731  +  *ppOut = pNew;
          732  +  return rc;
          733  +}
          734  +
          735  +/*
          736  +** This function is a no-op if *pRc is set to anything other than 
          737  +** SQLITE_OK when it is called.
          738  +**
          739  +** If *pRc is initially set to SQLITE_OK, then the text specified by
          740  +** the printf() style arguments is appended to zIn and the result returned
          741  +** in a buffer allocated by sqlite3_malloc(). sqlite3_free() is called on
          742  +** zIn before returning.
          743  +*/
          744  +static char *idxAppendText(int *pRc, char *zIn, const char *zFmt, ...){
          745  +  va_list ap;
          746  +  char *zAppend = 0;
          747  +  char *zRet = 0;
          748  +  int nIn = zIn ? STRLEN(zIn) : 0;
          749  +  int nAppend = 0;
          750  +  va_start(ap, zFmt);
          751  +  if( *pRc==SQLITE_OK ){
          752  +    zAppend = sqlite3_vmprintf(zFmt, ap);
          753  +    if( zAppend ){
          754  +      nAppend = STRLEN(zAppend);
          755  +      zRet = (char*)sqlite3_malloc(nIn + nAppend + 1);
          756  +    }
          757  +    if( zAppend && zRet ){
          758  +      memcpy(zRet, zIn, nIn);
          759  +      memcpy(&zRet[nIn], zAppend, nAppend+1);
          760  +    }else{
          761  +      sqlite3_free(zRet);
          762  +      zRet = 0;
          763  +      *pRc = SQLITE_NOMEM;
          764  +    }
          765  +    sqlite3_free(zAppend);
          766  +    sqlite3_free(zIn);
          767  +  }
          768  +  va_end(ap);
          769  +  return zRet;
          770  +}
          771  +
          772  +/*
          773  +** Return true if zId must be quoted in order to use it as an SQL
          774  +** identifier, or false otherwise.
          775  +*/
          776  +static int idxIdentifierRequiresQuotes(const char *zId){
          777  +  int i;
          778  +  for(i=0; zId[i]; i++){
          779  +    if( !(zId[i]=='_')
          780  +     && !(zId[i]>='0' && zId[i]<='9')
          781  +     && !(zId[i]>='a' && zId[i]<='z')
          782  +     && !(zId[i]>='A' && zId[i]<='Z')
          783  +    ){
          784  +      return 1;
          785  +    }
          786  +  }
          787  +  return 0;
          788  +}
          789  +
          790  +/*
          791  +** This function appends an index column definition suitable for constraint
          792  +** pCons to the string passed as zIn and returns the result.
          793  +*/
          794  +static char *idxAppendColDefn(
          795  +  int *pRc,                       /* IN/OUT: Error code */
          796  +  char *zIn,                      /* Column defn accumulated so far */
          797  +  IdxTable *pTab,                 /* Table index will be created on */
          798  +  IdxConstraint *pCons
          799  +){
          800  +  char *zRet = zIn;
          801  +  IdxColumn *p = &pTab->aCol[pCons->iCol];
          802  +  if( zRet ) zRet = idxAppendText(pRc, zRet, ", ");
          803  +
          804  +  if( idxIdentifierRequiresQuotes(p->zName) ){
          805  +    zRet = idxAppendText(pRc, zRet, "%Q", p->zName);
          806  +  }else{
          807  +    zRet = idxAppendText(pRc, zRet, "%s", p->zName);
          808  +  }
          809  +
          810  +  if( sqlite3_stricmp(p->zColl, pCons->zColl) ){
          811  +    if( idxIdentifierRequiresQuotes(pCons->zColl) ){
          812  +      zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl);
          813  +    }else{
          814  +      zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl);
          815  +    }
          816  +  }
          817  +
          818  +  if( pCons->bDesc ){
          819  +    zRet = idxAppendText(pRc, zRet, " DESC");
          820  +  }
          821  +  return zRet;
          822  +}
          823  +
          824  +/*
          825  +** Search database dbm for an index compatible with the one idxCreateFromCons()
          826  +** would create from arguments pScan, pEq and pTail. If no error occurs and 
          827  +** such an index is found, return non-zero. Or, if no such index is found,
          828  +** return zero.
          829  +**
          830  +** If an error occurs, set *pRc to an SQLite error code and return zero.
          831  +*/
          832  +static int idxFindCompatible(
          833  +  int *pRc,                       /* OUT: Error code */
          834  +  sqlite3* dbm,                   /* Database to search */
          835  +  IdxScan *pScan,                 /* Scan for table to search for index on */
          836  +  IdxConstraint *pEq,             /* List of == constraints */
          837  +  IdxConstraint *pTail            /* List of range constraints */
          838  +){
          839  +  const char *zTbl = pScan->pTab->zName;
          840  +  sqlite3_stmt *pIdxList = 0;
          841  +  IdxConstraint *pIter;
          842  +  int nEq = 0;                    /* Number of elements in pEq */
          843  +  int rc;
          844  +
          845  +  /* Count the elements in list pEq */
          846  +  for(pIter=pEq; pIter; pIter=pIter->pLink) nEq++;
          847  +
          848  +  rc = idxPrintfPrepareStmt(dbm, &pIdxList, 0, "PRAGMA index_list=%Q", zTbl);
          849  +  while( rc==SQLITE_OK && sqlite3_step(pIdxList)==SQLITE_ROW ){
          850  +    int bMatch = 1;
          851  +    IdxConstraint *pT = pTail;
          852  +    sqlite3_stmt *pInfo = 0;
          853  +    const char *zIdx = (const char*)sqlite3_column_text(pIdxList, 1);
          854  +
          855  +    /* Zero the IdxConstraint.bFlag values in the pEq list */
          856  +    for(pIter=pEq; pIter; pIter=pIter->pLink) pIter->bFlag = 0;
          857  +
          858  +    rc = idxPrintfPrepareStmt(dbm, &pInfo, 0, "PRAGMA index_xInfo=%Q", zIdx);
          859  +    while( rc==SQLITE_OK && sqlite3_step(pInfo)==SQLITE_ROW ){
          860  +      int iIdx = sqlite3_column_int(pInfo, 0);
          861  +      int iCol = sqlite3_column_int(pInfo, 1);
          862  +      const char *zColl = (const char*)sqlite3_column_text(pInfo, 4);
          863  +
          864  +      if( iIdx<nEq ){
          865  +        for(pIter=pEq; pIter; pIter=pIter->pLink){
          866  +          if( pIter->bFlag ) continue;
          867  +          if( pIter->iCol!=iCol ) continue;
          868  +          if( sqlite3_stricmp(pIter->zColl, zColl) ) continue;
          869  +          pIter->bFlag = 1;
          870  +          break;
          871  +        }
          872  +        if( pIter==0 ){
          873  +          bMatch = 0;
          874  +          break;
          875  +        }
          876  +      }else{
          877  +        if( pT ){
          878  +          if( pT->iCol!=iCol || sqlite3_stricmp(pT->zColl, zColl) ){
          879  +            bMatch = 0;
          880  +            break;
          881  +          }
          882  +          pT = pT->pLink;
          883  +        }
          884  +      }
          885  +    }
          886  +    idxFinalize(&rc, pInfo);
          887  +
          888  +    if( rc==SQLITE_OK && bMatch ){
          889  +      sqlite3_finalize(pIdxList);
          890  +      return 1;
          891  +    }
          892  +  }
          893  +  idxFinalize(&rc, pIdxList);
          894  +
          895  +  *pRc = rc;
          896  +  return 0;
          897  +}
          898  +
          899  +static int idxCreateFromCons(
          900  +  sqlite3expert *p,
          901  +  IdxScan *pScan,
          902  +  IdxConstraint *pEq, 
          903  +  IdxConstraint *pTail
          904  +){
          905  +  sqlite3 *dbm = p->dbm;
          906  +  int rc = SQLITE_OK;
          907  +  if( (pEq || pTail) && 0==idxFindCompatible(&rc, dbm, pScan, pEq, pTail) ){
          908  +    IdxTable *pTab = pScan->pTab;
          909  +    char *zCols = 0;
          910  +    char *zIdx = 0;
          911  +    IdxConstraint *pCons;
          912  +    int h = 0;
          913  +    const char *zFmt;
          914  +
          915  +    for(pCons=pEq; pCons; pCons=pCons->pLink){
          916  +      zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
          917  +    }
          918  +    for(pCons=pTail; pCons; pCons=pCons->pLink){
          919  +      zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
          920  +    }
          921  +
          922  +    if( rc==SQLITE_OK ){
          923  +      /* Hash the list of columns to come up with a name for the index */
          924  +      const char *zTable = pScan->pTab->zName;
          925  +      char *zName;                /* Index name */
          926  +      int i;
          927  +      for(i=0; zCols[i]; i++){
          928  +        h += ((h<<3) + zCols[i]);
          929  +      }
          930  +      zName = sqlite3_mprintf("%s_idx_%08x", zTable, h);
          931  +      if( zName==0 ){ 
          932  +        rc = SQLITE_NOMEM;
          933  +      }else{
          934  +        if( idxIdentifierRequiresQuotes(zTable) ){
          935  +          zFmt = "CREATE INDEX '%q' ON %Q(%s)";
          936  +        }else{
          937  +          zFmt = "CREATE INDEX %s ON %s(%s)";
          938  +        }
          939  +        zIdx = sqlite3_mprintf(zFmt, zName, zTable, zCols);
          940  +        if( !zIdx ){
          941  +          rc = SQLITE_NOMEM;
          942  +        }else{
          943  +          rc = sqlite3_exec(dbm, zIdx, 0, 0, p->pzErrmsg);
          944  +          idxHashAdd(&rc, &p->hIdx, zName, zIdx);
          945  +        }
          946  +        sqlite3_free(zName);
          947  +        sqlite3_free(zIdx);
          948  +      }
          949  +    }
          950  +
          951  +    sqlite3_free(zCols);
          952  +  }
          953  +  return rc;
          954  +}
          955  +
          956  +/*
          957  +** Return true if list pList (linked by IdxConstraint.pLink) contains
          958  +** a constraint compatible with *p. Otherwise return false.
          959  +*/
          960  +static int idxFindConstraint(IdxConstraint *pList, IdxConstraint *p){
          961  +  IdxConstraint *pCmp;
          962  +  for(pCmp=pList; pCmp; pCmp=pCmp->pLink){
          963  +    if( p->iCol==pCmp->iCol ) return 1;
          964  +  }
          965  +  return 0;
          966  +}
          967  +
          968  +static int idxCreateFromWhere(
          969  +  sqlite3expert *p, 
          970  +  IdxScan *pScan,                 /* Create indexes for this scan */
          971  +  IdxConstraint *pTail            /* range/ORDER BY constraints for inclusion */
          972  +){
          973  +  IdxConstraint *p1 = 0;
          974  +  IdxConstraint *pCon;
          975  +  int rc;
          976  +
          977  +  /* Gather up all the == constraints. */
          978  +  for(pCon=pScan->pEq; pCon; pCon=pCon->pNext){
          979  +    if( !idxFindConstraint(p1, pCon) && !idxFindConstraint(pTail, pCon) ){
          980  +      pCon->pLink = p1;
          981  +      p1 = pCon;
          982  +    }
          983  +  }
          984  +
          985  +  /* Create an index using the == constraints collected above. And the
          986  +  ** range constraint/ORDER BY terms passed in by the caller, if any. */
          987  +  rc = idxCreateFromCons(p, pScan, p1, pTail);
          988  +
          989  +  /* If no range/ORDER BY passed by the caller, create a version of the
          990  +  ** index for each range constraint.  */
          991  +  if( pTail==0 ){
          992  +    for(pCon=pScan->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){
          993  +      assert( pCon->pLink==0 );
          994  +      if( !idxFindConstraint(p1, pCon) && !idxFindConstraint(pTail, pCon) ){
          995  +        rc = idxCreateFromCons(p, pScan, p1, pCon);
          996  +      }
          997  +    }
          998  +  }
          999  +
         1000  +  return rc;
         1001  +}
         1002  +
         1003  +/*
         1004  +** Create candidate indexes in database [dbm] based on the data in 
         1005  +** linked-list pScan.
         1006  +*/
         1007  +static int idxCreateCandidates(sqlite3expert *p, char **pzErr){
         1008  +  int rc = SQLITE_OK;
         1009  +  IdxScan *pIter;
         1010  +
         1011  +  for(pIter=p->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
         1012  +    rc = idxCreateFromWhere(p, pIter, 0);
         1013  +    if( rc==SQLITE_OK && pIter->pOrder ){
         1014  +      rc = idxCreateFromWhere(p, pIter, pIter->pOrder);
         1015  +    }
         1016  +  }
         1017  +
         1018  +  return rc;
         1019  +}
         1020  +
         1021  +/*
         1022  +** Free all elements of the linked list starting at pConstraint.
         1023  +*/
         1024  +static void idxConstraintFree(IdxConstraint *pConstraint){
         1025  +  IdxConstraint *pNext;
         1026  +  IdxConstraint *p;
         1027  +
         1028  +  for(p=pConstraint; p; p=pNext){
         1029  +    pNext = p->pNext;
         1030  +    sqlite3_free(p);
         1031  +  }
         1032  +}
         1033  +
         1034  +/*
         1035  +** Free all elements of the linked list starting from pScan up until pLast
         1036  +** (pLast is not freed).
         1037  +*/
         1038  +static void idxScanFree(IdxScan *pScan, IdxScan *pLast){
         1039  +  IdxScan *p;
         1040  +  IdxScan *pNext;
         1041  +  for(p=pScan; p!=pLast; p=pNext){
         1042  +    pNext = p->pNextScan;
         1043  +    idxConstraintFree(p->pOrder);
         1044  +    idxConstraintFree(p->pEq);
         1045  +    idxConstraintFree(p->pRange);
         1046  +    sqlite3_free(p);
         1047  +  }
         1048  +}
         1049  +
         1050  +/*
         1051  +** Free all elements of the linked list starting from pStatement up 
         1052  +** until pLast (pLast is not freed).
         1053  +*/
         1054  +static void idxStatementFree(IdxStatement *pStatement, IdxStatement *pLast){
         1055  +  IdxStatement *p;
         1056  +  IdxStatement *pNext;
         1057  +  for(p=pStatement; p!=pLast; p=pNext){
         1058  +    pNext = p->pNext;
         1059  +    sqlite3_free(p->zEQP);
         1060  +    sqlite3_free(p->zIdx);
         1061  +    sqlite3_free(p);
         1062  +  }
         1063  +}
         1064  +
         1065  +/*
         1066  +** Free the linked list of IdxTable objects starting at pTab.
         1067  +*/
         1068  +static void idxTableFree(IdxTable *pTab){
         1069  +  IdxTable *pIter;
         1070  +  IdxTable *pNext;
         1071  +  for(pIter=pTab; pIter; pIter=pNext){
         1072  +    pNext = pIter->pNext;
         1073  +    sqlite3_free(pIter);
         1074  +  }
         1075  +}
         1076  +
         1077  +/*
         1078  +** Free the linked list of IdxWrite objects starting at pTab.
         1079  +*/
         1080  +static void idxWriteFree(IdxWrite *pTab){
         1081  +  IdxWrite *pIter;
         1082  +  IdxWrite *pNext;
         1083  +  for(pIter=pTab; pIter; pIter=pNext){
         1084  +    pNext = pIter->pNext;
         1085  +    sqlite3_free(pIter);
         1086  +  }
         1087  +}
         1088  +
         1089  +
         1090  +
         1091  +/*
         1092  +** This function is called after candidate indexes have been created. It
         1093  +** runs all the queries to see which indexes they prefer, and populates
         1094  +** IdxStatement.zIdx and IdxStatement.zEQP with the results.
         1095  +*/
         1096  +int idxFindIndexes(
         1097  +  sqlite3expert *p,
         1098  +  char **pzErr                         /* OUT: Error message (sqlite3_malloc) */
         1099  +){
         1100  +  IdxStatement *pStmt;
         1101  +  sqlite3 *dbm = p->dbm;
         1102  +  int rc = SQLITE_OK;
         1103  +
         1104  +  IdxHash hIdx;
         1105  +  idxHashInit(&hIdx);
         1106  +
         1107  +  for(pStmt=p->pStatement; rc==SQLITE_OK && pStmt; pStmt=pStmt->pNext){
         1108  +    IdxHashEntry *pEntry;
         1109  +    sqlite3_stmt *pExplain = 0;
         1110  +    idxHashClear(&hIdx);
         1111  +    rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,
         1112  +        "EXPLAIN QUERY PLAN %s", pStmt->zSql
         1113  +    );
         1114  +    while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
         1115  +      int iSelectid = sqlite3_column_int(pExplain, 0);
         1116  +      int iOrder = sqlite3_column_int(pExplain, 1);
         1117  +      int iFrom = sqlite3_column_int(pExplain, 2);
         1118  +      const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
         1119  +      int nDetail = STRLEN(zDetail);
         1120  +      int i;
         1121  +
         1122  +      for(i=0; i<nDetail; i++){
         1123  +        const char *zIdx = 0;
         1124  +        if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){
         1125  +          zIdx = &zDetail[i+13];
         1126  +        }else if( memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 ){
         1127  +          zIdx = &zDetail[i+22];
         1128  +        }
         1129  +        if( zIdx ){
         1130  +          const char *zSql;
         1131  +          int nIdx = 0;
         1132  +          while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){
         1133  +            nIdx++;
         1134  +          }
         1135  +          zSql = idxHashSearch(&p->hIdx, zIdx, nIdx);
         1136  +          if( zSql ){
         1137  +            idxHashAdd(&rc, &hIdx, zSql, 0);
         1138  +            if( rc ) goto find_indexes_out;
         1139  +          }
         1140  +          break;
         1141  +        }
         1142  +      }
         1143  +
         1144  +      pStmt->zEQP = idxAppendText(&rc, pStmt->zEQP, "%d|%d|%d|%s\n", 
         1145  +          iSelectid, iOrder, iFrom, zDetail
         1146  +      );
         1147  +    }
         1148  +
         1149  +    for(pEntry=hIdx.pFirst; pEntry; pEntry=pEntry->pNext){
         1150  +      pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s;\n", pEntry->zKey);
         1151  +    }
         1152  +
         1153  +    idxFinalize(&rc, pExplain);
         1154  +  }
         1155  +
         1156  + find_indexes_out:
         1157  +  idxHashClear(&hIdx);
         1158  +  return rc;
         1159  +}
         1160  +
         1161  +static int idxAuthCallback(
         1162  +  void *pCtx,
         1163  +  int eOp,
         1164  +  const char *z3,
         1165  +  const char *z4,
         1166  +  const char *zDb,
         1167  +  const char *zTrigger
         1168  +){
         1169  +  int rc = SQLITE_OK;
         1170  +  if( eOp==SQLITE_INSERT || eOp==SQLITE_UPDATE || eOp==SQLITE_DELETE ){
         1171  +    if( sqlite3_stricmp(zDb, "main")==0 ){
         1172  +      sqlite3expert *p = (sqlite3expert*)pCtx;
         1173  +      IdxTable *pTab;
         1174  +      for(pTab=p->pTable; pTab; pTab=pTab->pNext){
         1175  +        if( 0==sqlite3_stricmp(z3, pTab->zName) ) break;
         1176  +      }
         1177  +      if( pTab ){
         1178  +        IdxWrite *pWrite;
         1179  +        for(pWrite=p->pWrite; pWrite; pWrite=pWrite->pNext){
         1180  +          if( pWrite->pTab==pTab && pWrite->eOp==eOp ) break;
         1181  +        }
         1182  +        if( pWrite==0 ){
         1183  +          pWrite = idxMalloc(&rc, sizeof(IdxWrite));
         1184  +          if( rc==SQLITE_OK ){
         1185  +            pWrite->pTab = pTab;
         1186  +            pWrite->eOp = eOp;
         1187  +            pWrite->pNext = p->pWrite;
         1188  +            p->pWrite = pWrite;
         1189  +          }
         1190  +        }
         1191  +      }
         1192  +    }
         1193  +  }
         1194  +  return rc;
         1195  +}
         1196  +
         1197  +static int idxProcessOneTrigger(
         1198  +  sqlite3expert *p, 
         1199  +  IdxWrite *pWrite, 
         1200  +  char **pzErr
         1201  +){
         1202  +  static const char *zInt = UNIQUE_TABLE_NAME;
         1203  +  static const char *zDrop = "DROP TABLE " UNIQUE_TABLE_NAME;
         1204  +  IdxTable *pTab = pWrite->pTab;
         1205  +  const char *zTab = pTab->zName;
         1206  +  const char *zSql = 
         1207  +    "SELECT 'CREATE TEMP' || substr(sql, 7) FROM sqlite_master "
         1208  +    "WHERE tbl_name = %Q AND type IN ('table', 'trigger') "
         1209  +    "ORDER BY type;";
         1210  +  sqlite3_stmt *pSelect = 0;
         1211  +  int rc = SQLITE_OK;
         1212  +  char *zWrite = 0;
         1213  +
         1214  +  /* Create the table and its triggers in the temp schema */
         1215  +  rc = idxPrintfPrepareStmt(p->db, &pSelect, pzErr, zSql, zTab, zTab);
         1216  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSelect) ){
         1217  +    const char *zCreate = (const char*)sqlite3_column_text(pSelect, 0);
         1218  +    rc = sqlite3_exec(p->dbv, zCreate, 0, 0, pzErr);
         1219  +  }
         1220  +  idxFinalize(&rc, pSelect);
         1221  +
         1222  +  /* Rename the table in the temp schema to zInt */
         1223  +  if( rc==SQLITE_OK ){
         1224  +    char *z = sqlite3_mprintf("ALTER TABLE temp.%Q RENAME TO %Q", zTab, zInt);
         1225  +    if( z==0 ){
         1226  +      rc = SQLITE_NOMEM;
         1227  +    }else{
         1228  +      rc = sqlite3_exec(p->dbv, z, 0, 0, pzErr);
         1229  +      sqlite3_free(z);
         1230  +    }
         1231  +  }
         1232  +
         1233  +  switch( pWrite->eOp ){
         1234  +    case SQLITE_INSERT: {
         1235  +      int i;
         1236  +      zWrite = idxAppendText(&rc, zWrite, "INSERT INTO %Q VALUES(", zInt);
         1237  +      for(i=0; i<pTab->nCol; i++){
         1238  +        zWrite = idxAppendText(&rc, zWrite, "%s?", i==0 ? "" : ", ");
         1239  +      }
         1240  +      zWrite = idxAppendText(&rc, zWrite, ")");
         1241  +      break;
         1242  +    }
         1243  +    case SQLITE_UPDATE: {
         1244  +      int i;
         1245  +      zWrite = idxAppendText(&rc, zWrite, "UPDATE %Q SET ", zInt);
         1246  +      for(i=0; i<pTab->nCol; i++){
         1247  +        zWrite = idxAppendText(&rc, zWrite, "%s%Q=?", i==0 ? "" : ", ", 
         1248  +            pTab->aCol[i].zName
         1249  +        );
         1250  +      }
         1251  +      break;
         1252  +    }
         1253  +    default: {
         1254  +      assert( pWrite->eOp==SQLITE_DELETE );
         1255  +      if( rc==SQLITE_OK ){
         1256  +        zWrite = sqlite3_mprintf("DELETE FROM %Q", zInt);
         1257  +        if( zWrite==0 ) rc = SQLITE_NOMEM;
         1258  +      }
         1259  +    }
         1260  +  }
         1261  +
         1262  +  if( rc==SQLITE_OK ){
         1263  +    sqlite3_stmt *pX = 0;
         1264  +    rc = sqlite3_prepare_v2(p->dbv, zWrite, -1, &pX, 0);
         1265  +    idxFinalize(&rc, pX);
         1266  +    if( rc!=SQLITE_OK ){
         1267  +      idxDatabaseError(p->dbv, pzErr);
         1268  +    }
         1269  +  }
         1270  +  sqlite3_free(zWrite);
         1271  +
         1272  +  if( rc==SQLITE_OK ){
         1273  +    rc = sqlite3_exec(p->dbv, zDrop, 0, 0, pzErr);
         1274  +  }
         1275  +
         1276  +  return rc;
         1277  +}
         1278  +
         1279  +static int idxProcessTriggers(sqlite3expert *p, char **pzErr){
         1280  +  int rc = SQLITE_OK;
         1281  +  IdxWrite *pEnd = 0;
         1282  +  IdxWrite *pFirst = p->pWrite;
         1283  +
         1284  +  while( rc==SQLITE_OK && pFirst!=pEnd ){
         1285  +    IdxWrite *pIter;
         1286  +    for(pIter=pFirst; rc==SQLITE_OK && pIter!=pEnd; pIter=pIter->pNext){
         1287  +      rc = idxProcessOneTrigger(p, pIter, pzErr);
         1288  +    }
         1289  +    pEnd = pFirst;
         1290  +    pFirst = p->pWrite;
         1291  +  }
         1292  +
         1293  +  return rc;
         1294  +}
         1295  +
         1296  +
         1297  +static int idxCreateVtabSchema(sqlite3expert *p, char **pzErrmsg){
         1298  +  int rc = idxRegisterVtab(p);
         1299  +  sqlite3_stmt *pSchema = 0;
         1300  +
         1301  +  /* For each table in the main db schema:
         1302  +  **
         1303  +  **   1) Add an entry to the p->pTable list, and
         1304  +  **   2) Create the equivalent virtual table in dbv.
         1305  +  */
         1306  +  rc = idxPrepareStmt(p->db, &pSchema, pzErrmsg,
         1307  +      "SELECT type, name, sql, 1 FROM sqlite_master "
         1308  +      "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%%' "
         1309  +      " UNION ALL "
         1310  +      "SELECT type, name, sql, 2 FROM sqlite_master "
         1311  +      "WHERE type = 'trigger'"
         1312  +      "  AND tbl_name IN(SELECT name FROM sqlite_master WHERE type = 'view') "
         1313  +      "ORDER BY 4, 1"
         1314  +  );
         1315  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSchema) ){
         1316  +    const char *zType = (const char*)sqlite3_column_text(pSchema, 0);
         1317  +    const char *zName = (const char*)sqlite3_column_text(pSchema, 1);
         1318  +    const char *zSql = (const char*)sqlite3_column_text(pSchema, 2);
         1319  +
         1320  +    if( zType[0]=='v' || zType[1]=='r' ){
         1321  +      rc = sqlite3_exec(p->dbv, zSql, 0, 0, pzErrmsg);
         1322  +    }else{
         1323  +      IdxTable *pTab;
         1324  +      rc = idxGetTableInfo(p->db, zName, &pTab, pzErrmsg);
         1325  +      if( rc==SQLITE_OK ){
         1326  +        int i;
         1327  +        char *zInner = 0;
         1328  +        char *zOuter = 0;
         1329  +        pTab->pNext = p->pTable;
         1330  +        p->pTable = pTab;
         1331  +
         1332  +        /* The statement the vtab will pass to sqlite3_declare_vtab() */
         1333  +        zInner = idxAppendText(&rc, 0, "CREATE TABLE x(");
         1334  +        for(i=0; i<pTab->nCol; i++){
         1335  +          zInner = idxAppendText(&rc, zInner, "%s%Q COLLATE %s", 
         1336  +              (i==0 ? "" : ", "), pTab->aCol[i].zName, pTab->aCol[i].zColl
         1337  +          );
         1338  +        }
         1339  +        zInner = idxAppendText(&rc, zInner, ")");
         1340  +
         1341  +        /* The CVT statement to create the vtab */
         1342  +        zOuter = idxAppendText(&rc, 0, 
         1343  +            "CREATE VIRTUAL TABLE %Q USING expert(%Q)", zName, zInner
         1344  +        );
         1345  +        if( rc==SQLITE_OK ){
         1346  +          rc = sqlite3_exec(p->dbv, zOuter, 0, 0, pzErrmsg);
         1347  +        }
         1348  +        sqlite3_free(zInner);
         1349  +        sqlite3_free(zOuter);
         1350  +      }
         1351  +    }
         1352  +  }
         1353  +  idxFinalize(&rc, pSchema);
         1354  +  return rc;
         1355  +}
         1356  +
         1357  +struct IdxSampleCtx {
         1358  +  int iTarget;
         1359  +  double target;                  /* Target nRet/nRow value */
         1360  +  double nRow;                    /* Number of rows seen */
         1361  +  double nRet;                    /* Number of rows returned */
         1362  +};
         1363  +
         1364  +static void idxSampleFunc(
         1365  +  sqlite3_context *pCtx,
         1366  +  int argc,
         1367  +  sqlite3_value **argv
         1368  +){
         1369  +  struct IdxSampleCtx *p = (struct IdxSampleCtx*)sqlite3_user_data(pCtx);
         1370  +  int bRet;
         1371  +
         1372  +  assert( argc==0 );
         1373  +  if( p->nRow==0.0 ){
         1374  +    bRet = 1;
         1375  +  }else{
         1376  +    bRet = (p->nRet / p->nRow) <= p->target;
         1377  +    if( bRet==0 ){
         1378  +      unsigned short rnd;
         1379  +      sqlite3_randomness(2, (void*)&rnd);
         1380  +      bRet = ((int)rnd % 100) <= p->iTarget;
         1381  +    }
         1382  +  }
         1383  +
         1384  +  sqlite3_result_int(pCtx, bRet);
         1385  +  p->nRow += 1.0;
         1386  +  p->nRet += (double)bRet;
         1387  +}
         1388  +
         1389  +struct IdxRemCtx {
         1390  +  int nSlot;
         1391  +  struct IdxRemSlot {
         1392  +    int eType;                    /* SQLITE_NULL, INTEGER, REAL, TEXT, BLOB */
         1393  +    i64 iVal;                     /* SQLITE_INTEGER value */
         1394  +    double rVal;                  /* SQLITE_FLOAT value */
         1395  +    int nByte;                    /* Bytes of space allocated at z */
         1396  +    int n;                        /* Size of buffer z */
         1397  +    char *z;                      /* SQLITE_TEXT/BLOB value */
         1398  +  } aSlot[1];
         1399  +};
         1400  +
         1401  +/*
         1402  +** Implementation of scalar function rem().
         1403  +*/
         1404  +static void idxRemFunc(
         1405  +  sqlite3_context *pCtx,
         1406  +  int argc,
         1407  +  sqlite3_value **argv
         1408  +){
         1409  +  struct IdxRemCtx *p = (struct IdxRemCtx*)sqlite3_user_data(pCtx);
         1410  +  struct IdxRemSlot *pSlot;
         1411  +  int iSlot;
         1412  +  assert( argc==2 );
         1413  +
         1414  +  iSlot = sqlite3_value_int(argv[0]);
         1415  +  assert( iSlot<=p->nSlot );
         1416  +  pSlot = &p->aSlot[iSlot];
         1417  +
         1418  +  switch( pSlot->eType ){
         1419  +    case SQLITE_NULL:
         1420  +      /* no-op */
         1421  +      break;
         1422  +
         1423  +    case SQLITE_INTEGER:
         1424  +      sqlite3_result_int64(pCtx, pSlot->iVal);
         1425  +      break;
         1426  +
         1427  +    case SQLITE_FLOAT:
         1428  +      sqlite3_result_double(pCtx, pSlot->rVal);
         1429  +      break;
         1430  +
         1431  +    case SQLITE_BLOB:
         1432  +      sqlite3_result_blob(pCtx, pSlot->z, pSlot->n, SQLITE_TRANSIENT);
         1433  +      break;
         1434  +
         1435  +    case SQLITE_TEXT:
         1436  +      sqlite3_result_text(pCtx, pSlot->z, pSlot->n, SQLITE_TRANSIENT);
         1437  +      break;
         1438  +  }
         1439  +
         1440  +  pSlot->eType = sqlite3_value_type(argv[1]);
         1441  +  switch( pSlot->eType ){
         1442  +    case SQLITE_NULL:
         1443  +      /* no-op */
         1444  +      break;
         1445  +
         1446  +    case SQLITE_INTEGER:
         1447  +      pSlot->iVal = sqlite3_value_int64(argv[1]);
         1448  +      break;
         1449  +
         1450  +    case SQLITE_FLOAT:
         1451  +      pSlot->rVal = sqlite3_value_double(argv[1]);
         1452  +      break;
         1453  +
         1454  +    case SQLITE_BLOB:
         1455  +    case SQLITE_TEXT: {
         1456  +      int nByte = sqlite3_value_bytes(argv[1]);
         1457  +      if( nByte>pSlot->nByte ){
         1458  +        char *zNew = (char*)sqlite3_realloc(pSlot->z, nByte*2);
         1459  +        if( zNew==0 ){
         1460  +          sqlite3_result_error_nomem(pCtx);
         1461  +          return;
         1462  +        }
         1463  +        pSlot->nByte = nByte*2;
         1464  +        pSlot->z = zNew;
         1465  +      }
         1466  +      pSlot->n = nByte;
         1467  +      if( pSlot->eType==SQLITE_BLOB ){
         1468  +        memcpy(pSlot->z, sqlite3_value_blob(argv[1]), nByte);
         1469  +      }else{
         1470  +        memcpy(pSlot->z, sqlite3_value_text(argv[1]), nByte);
         1471  +      }
         1472  +      break;
         1473  +    }
         1474  +  }
         1475  +}
         1476  +
         1477  +static int idxLargestIndex(sqlite3 *db, int *pnMax, char **pzErr){
         1478  +  int rc = SQLITE_OK;
         1479  +  const char *zMax = 
         1480  +    "SELECT max(i.seqno) FROM "
         1481  +    "  sqlite_master AS s, "
         1482  +    "  pragma_index_list(s.name) AS l, "
         1483  +    "  pragma_index_info(l.name) AS i "
         1484  +    "WHERE s.type = 'table'";
         1485  +  sqlite3_stmt *pMax = 0;
         1486  +
         1487  +  *pnMax = 0;
         1488  +  rc = idxPrepareStmt(db, &pMax, pzErr, zMax);
         1489  +  if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pMax) ){
         1490  +    *pnMax = sqlite3_column_int(pMax, 0) + 1;
         1491  +  }
         1492  +  idxFinalize(&rc, pMax);
         1493  +
         1494  +  return rc;
         1495  +}
         1496  +
         1497  +static int idxPopulateOneStat1(
         1498  +  sqlite3expert *p,
         1499  +  sqlite3_stmt *pIndexXInfo,
         1500  +  sqlite3_stmt *pWriteStat,
         1501  +  const char *zTab,
         1502  +  const char *zIdx,
         1503  +  char **pzErr
         1504  +){
         1505  +  char *zCols = 0;
         1506  +  char *zOrder = 0;
         1507  +  char *zQuery = 0;
         1508  +  int nCol = 0;
         1509  +  int i;
         1510  +  sqlite3_stmt *pQuery = 0;
         1511  +  int *aStat = 0;
         1512  +  int rc = SQLITE_OK;
         1513  +
         1514  +  assert( p->iSample>0 );
         1515  +
         1516  +  /* Formulate the query text */
         1517  +  sqlite3_bind_text(pIndexXInfo, 1, zIdx, -1, SQLITE_STATIC);
         1518  +  while( SQLITE_OK==rc && SQLITE_ROW==sqlite3_step(pIndexXInfo) ){
         1519  +    const char *zComma = zCols==0 ? "" : ", ";
         1520  +    const char *zName = (const char*)sqlite3_column_text(pIndexXInfo, 0);
         1521  +    const char *zColl = (const char*)sqlite3_column_text(pIndexXInfo, 1);
         1522  +    zCols = idxAppendText(&rc, zCols, 
         1523  +        "%sx.%Q IS rem(%d, x.%Q) COLLATE %s", zComma, zName, nCol, zName, zColl
         1524  +    );
         1525  +    zOrder = idxAppendText(&rc, zOrder, "%s%d", zComma, ++nCol);
         1526  +  }
         1527  +  if( rc==SQLITE_OK ){
         1528  +    if( p->iSample==100 ){
         1529  +      zQuery = sqlite3_mprintf(
         1530  +          "SELECT %s FROM %Q x ORDER BY %s", zCols, zTab, zOrder
         1531  +      );
         1532  +    }else{
         1533  +      zQuery = sqlite3_mprintf(
         1534  +          "SELECT %s FROM temp."UNIQUE_TABLE_NAME" x ORDER BY %s", zCols, zOrder
         1535  +      );
         1536  +    }
         1537  +  }
         1538  +  sqlite3_free(zCols);
         1539  +  sqlite3_free(zOrder);
         1540  +
         1541  +  /* Formulate the query text */
         1542  +  if( rc==SQLITE_OK ){
         1543  +    sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
         1544  +    rc = idxPrepareStmt(dbrem, &pQuery, pzErr, zQuery);
         1545  +  }
         1546  +  sqlite3_free(zQuery);
         1547  +
         1548  +  if( rc==SQLITE_OK ){
         1549  +    aStat = (int*)idxMalloc(&rc, sizeof(int)*(nCol+1));
         1550  +  }
         1551  +  if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){
         1552  +    IdxHashEntry *pEntry;
         1553  +    char *zStat = 0;
         1554  +    for(i=0; i<=nCol; i++) aStat[i] = 1;
         1555  +    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){
         1556  +      aStat[0]++;
         1557  +      for(i=0; i<nCol; i++){
         1558  +        if( sqlite3_column_int(pQuery, i)==0 ) break;
         1559  +      }
         1560  +      for(/*no-op*/; i<nCol; i++){
         1561  +        aStat[i+1]++;
         1562  +      }
         1563  +    }
         1564  +
         1565  +    if( rc==SQLITE_OK ){
         1566  +      int s0 = aStat[0];
         1567  +      zStat = sqlite3_mprintf("%d", s0);
         1568  +      if( zStat==0 ) rc = SQLITE_NOMEM;
         1569  +      for(i=1; rc==SQLITE_OK && i<=nCol; i++){
         1570  +        zStat = idxAppendText(&rc, zStat, " %d", (s0+aStat[i]/2) / aStat[i]);
         1571  +      }
         1572  +    }
         1573  +
         1574  +    if( rc==SQLITE_OK ){
         1575  +      sqlite3_bind_text(pWriteStat, 1, zTab, -1, SQLITE_STATIC);
         1576  +      sqlite3_bind_text(pWriteStat, 2, zIdx, -1, SQLITE_STATIC);
         1577  +      sqlite3_bind_text(pWriteStat, 3, zStat, -1, SQLITE_STATIC);
         1578  +      sqlite3_step(pWriteStat);
         1579  +      rc = sqlite3_reset(pWriteStat);
         1580  +    }
         1581  +
         1582  +    pEntry = idxHashFind(&p->hIdx, zIdx, STRLEN(zIdx));
         1583  +    if( pEntry ){
         1584  +      assert( pEntry->zVal2==0 );
         1585  +      pEntry->zVal2 = zStat;
         1586  +    }else{
         1587  +      sqlite3_free(zStat);
         1588  +    }
         1589  +  }
         1590  +  sqlite3_free(aStat);
         1591  +  idxFinalize(&rc, pQuery);
         1592  +
         1593  +  return rc;
         1594  +}
         1595  +
         1596  +static int idxBuildSampleTable(sqlite3expert *p, const char *zTab){
         1597  +  int rc;
         1598  +  char *zSql;
         1599  +
         1600  +  rc = sqlite3_exec(p->dbv,"DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
         1601  +  if( rc!=SQLITE_OK ) return rc;
         1602  +
         1603  +  zSql = sqlite3_mprintf(
         1604  +      "CREATE TABLE temp." UNIQUE_TABLE_NAME " AS SELECT * FROM %Q", zTab
         1605  +  );
         1606  +  if( zSql==0 ) return SQLITE_NOMEM;
         1607  +  rc = sqlite3_exec(p->dbv, zSql, 0, 0, 0);
         1608  +  sqlite3_free(zSql);
         1609  +
         1610  +  return rc;
         1611  +}
         1612  +
         1613  +/*
         1614  +** This function is called as part of sqlite3_expert_analyze(). Candidate
         1615  +** indexes have already been created in database sqlite3expert.dbm, this
         1616  +** function populates sqlite_stat1 table in the same database.
         1617  +**
         1618  +** The stat1 data is generated by querying the 
         1619  +*/
         1620  +static int idxPopulateStat1(sqlite3expert *p, char **pzErr){
         1621  +  int rc = SQLITE_OK;
         1622  +  int nMax =0;
         1623  +  struct IdxRemCtx *pCtx = 0;
         1624  +  struct IdxSampleCtx samplectx; 
         1625  +  int i;
         1626  +  i64 iPrev = -100000;
         1627  +  sqlite3_stmt *pAllIndex = 0;
         1628  +  sqlite3_stmt *pIndexXInfo = 0;
         1629  +  sqlite3_stmt *pWrite = 0;
         1630  +
         1631  +  const char *zAllIndex =
         1632  +    "SELECT s.rowid, s.name, l.name FROM "
         1633  +    "  sqlite_master AS s, "
         1634  +    "  pragma_index_list(s.name) AS l "
         1635  +    "WHERE s.type = 'table'";
         1636  +  const char *zIndexXInfo = 
         1637  +    "SELECT name, coll FROM pragma_index_xinfo(?) WHERE key";
         1638  +  const char *zWrite = "INSERT INTO sqlite_stat1 VALUES(?, ?, ?)";
         1639  +
         1640  +  /* If iSample==0, no sqlite_stat1 data is required. */
         1641  +  if( p->iSample==0 ) return SQLITE_OK;
         1642  +
         1643  +  rc = idxLargestIndex(p->dbm, &nMax, pzErr);
         1644  +  if( nMax<=0 || rc!=SQLITE_OK ) return rc;
         1645  +
         1646  +  rc = sqlite3_exec(p->dbm, "ANALYZE; PRAGMA writable_schema=1", 0, 0, 0);
         1647  +
         1648  +  if( rc==SQLITE_OK ){
         1649  +    int nByte = sizeof(struct IdxRemCtx) + (sizeof(struct IdxRemSlot) * nMax);
         1650  +    pCtx = (struct IdxRemCtx*)idxMalloc(&rc, nByte);
         1651  +  }
         1652  +
         1653  +  if( rc==SQLITE_OK ){
         1654  +    sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
         1655  +    rc = sqlite3_create_function(
         1656  +        dbrem, "rem", 2, SQLITE_UTF8, (void*)pCtx, idxRemFunc, 0, 0
         1657  +    );
         1658  +  }
         1659  +  if( rc==SQLITE_OK ){
         1660  +    rc = sqlite3_create_function(
         1661  +        p->db, "sample", 0, SQLITE_UTF8, (void*)&samplectx, idxSampleFunc, 0, 0
         1662  +    );
         1663  +  }
         1664  +
         1665  +  if( rc==SQLITE_OK ){
         1666  +    pCtx->nSlot = nMax+1;
         1667  +    rc = idxPrepareStmt(p->dbm, &pAllIndex, pzErr, zAllIndex);
         1668  +  }
         1669  +  if( rc==SQLITE_OK ){
         1670  +    rc = idxPrepareStmt(p->dbm, &pIndexXInfo, pzErr, zIndexXInfo);
         1671  +  }
         1672  +  if( rc==SQLITE_OK ){
         1673  +    rc = idxPrepareStmt(p->dbm, &pWrite, pzErr, zWrite);
         1674  +  }
         1675  +
         1676  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pAllIndex) ){
         1677  +    i64 iRowid = sqlite3_column_int64(pAllIndex, 0);
         1678  +    const char *zTab = (const char*)sqlite3_column_text(pAllIndex, 1);
         1679  +    const char *zIdx = (const char*)sqlite3_column_text(pAllIndex, 2);
         1680  +    if( p->iSample<100 && iPrev!=iRowid ){
         1681  +      samplectx.target = (double)p->iSample / 100.0;
         1682  +      samplectx.iTarget = p->iSample;
         1683  +      samplectx.nRow = 0.0;
         1684  +      samplectx.nRet = 0.0;
         1685  +      rc = idxBuildSampleTable(p, zTab);
         1686  +      if( rc!=SQLITE_OK ) break;
         1687  +    }
         1688  +    rc = idxPopulateOneStat1(p, pIndexXInfo, pWrite, zTab, zIdx, pzErr);
         1689  +    iPrev = iRowid;
         1690  +  }
         1691  +  if( rc==SQLITE_OK && p->iSample<100 ){
         1692  +    rc = sqlite3_exec(p->dbv, 
         1693  +        "DROP TABLE IF EXISTS temp." UNIQUE_TABLE_NAME, 0,0,0
         1694  +    );
         1695  +  }
         1696  +
         1697  +  idxFinalize(&rc, pAllIndex);
         1698  +  idxFinalize(&rc, pIndexXInfo);
         1699  +  idxFinalize(&rc, pWrite);
         1700  +
         1701  +  for(i=0; i<pCtx->nSlot; i++){
         1702  +    sqlite3_free(pCtx->aSlot[i].z);
         1703  +  }
         1704  +  sqlite3_free(pCtx);
         1705  +
         1706  +  if( rc==SQLITE_OK ){
         1707  +    rc = sqlite3_exec(p->dbm, "ANALYZE sqlite_master", 0, 0, 0);
         1708  +  }
         1709  +
         1710  +  sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
         1711  +  return rc;
         1712  +}
         1713  +
         1714  +/*
         1715  +** Allocate a new sqlite3expert object.
         1716  +*/
         1717  +sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErrmsg){
         1718  +  int rc = SQLITE_OK;
         1719  +  sqlite3expert *pNew;
         1720  +
         1721  +  pNew = (sqlite3expert*)idxMalloc(&rc, sizeof(sqlite3expert));
         1722  +
         1723  +  /* Open two in-memory databases to work with. The "vtab database" (dbv)
         1724  +  ** will contain a virtual table corresponding to each real table in
         1725  +  ** the user database schema, and a copy of each view. It is used to
         1726  +  ** collect information regarding the WHERE, ORDER BY and other clauses
         1727  +  ** of the user's query.
         1728  +  */
         1729  +  if( rc==SQLITE_OK ){
         1730  +    pNew->db = db;
         1731  +    pNew->iSample = 100;
         1732  +    rc = sqlite3_open(":memory:", &pNew->dbv);
         1733  +  }
         1734  +  if( rc==SQLITE_OK ){
         1735  +    rc = sqlite3_open(":memory:", &pNew->dbm);
         1736  +    if( rc==SQLITE_OK ){
         1737  +      sqlite3_db_config(pNew->dbm, SQLITE_DBCONFIG_FULL_EQP, 1, (int*)0);
         1738  +    }
         1739  +  }
         1740  +  
         1741  +
         1742  +  /* Copy the entire schema of database [db] into [dbm]. */
         1743  +  if( rc==SQLITE_OK ){
         1744  +    sqlite3_stmt *pSql;
         1745  +    rc = idxPrintfPrepareStmt(pNew->db, &pSql, pzErrmsg, 
         1746  +        "SELECT sql FROM sqlite_master WHERE name NOT LIKE 'sqlite_%%'"
         1747  +        " AND sql NOT LIKE 'CREATE VIRTUAL %%'"
         1748  +    );
         1749  +    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
         1750  +      const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
         1751  +      rc = sqlite3_exec(pNew->dbm, zSql, 0, 0, pzErrmsg);
         1752  +    }
         1753  +    idxFinalize(&rc, pSql);
         1754  +  }
         1755  +
         1756  +  /* Create the vtab schema */
         1757  +  if( rc==SQLITE_OK ){
         1758  +    rc = idxCreateVtabSchema(pNew, pzErrmsg);
         1759  +  }
         1760  +
         1761  +  /* Register the auth callback with dbv */
         1762  +  if( rc==SQLITE_OK ){
         1763  +    sqlite3_set_authorizer(pNew->dbv, idxAuthCallback, (void*)pNew);
         1764  +  }
         1765  +
         1766  +  /* If an error has occurred, free the new object and reutrn NULL. Otherwise,
         1767  +  ** return the new sqlite3expert handle.  */
         1768  +  if( rc!=SQLITE_OK ){
         1769  +    sqlite3_expert_destroy(pNew);
         1770  +    pNew = 0;
         1771  +  }
         1772  +  return pNew;
         1773  +}
         1774  +
         1775  +/*
         1776  +** Configure an sqlite3expert object.
         1777  +*/
         1778  +int sqlite3_expert_config(sqlite3expert *p, int op, ...){
         1779  +  int rc = SQLITE_OK;
         1780  +  va_list ap;
         1781  +  va_start(ap, op);
         1782  +  switch( op ){
         1783  +    case EXPERT_CONFIG_SAMPLE: {
         1784  +      int iVal = va_arg(ap, int);
         1785  +      if( iVal<0 ) iVal = 0;
         1786  +      if( iVal>100 ) iVal = 100;
         1787  +      p->iSample = iVal;
         1788  +      break;
         1789  +    }
         1790  +    default:
         1791  +      rc = SQLITE_NOTFOUND;
         1792  +      break;
         1793  +  }
         1794  +
         1795  +  va_end(ap);
         1796  +  return rc;
         1797  +}
         1798  +
         1799  +/*
         1800  +** Add an SQL statement to the analysis.
         1801  +*/
         1802  +int sqlite3_expert_sql(
         1803  +  sqlite3expert *p,               /* From sqlite3_expert_new() */
         1804  +  const char *zSql,               /* SQL statement to add */
         1805  +  char **pzErr                    /* OUT: Error message (if any) */
         1806  +){
         1807  +  IdxScan *pScanOrig = p->pScan;
         1808  +  IdxStatement *pStmtOrig = p->pStatement;
         1809  +  int rc = SQLITE_OK;
         1810  +  const char *zStmt = zSql;
         1811  +
         1812  +  if( p->bRun ) return SQLITE_MISUSE;
         1813  +
         1814  +  while( rc==SQLITE_OK && zStmt && zStmt[0] ){
         1815  +    sqlite3_stmt *pStmt = 0;
         1816  +    rc = sqlite3_prepare_v2(p->dbv, zStmt, -1, &pStmt, &zStmt);
         1817  +    if( rc==SQLITE_OK ){
         1818  +      if( pStmt ){
         1819  +        IdxStatement *pNew;
         1820  +        const char *z = sqlite3_sql(pStmt);
         1821  +        int n = STRLEN(z);
         1822  +        pNew = (IdxStatement*)idxMalloc(&rc, sizeof(IdxStatement) + n+1);
         1823  +        if( rc==SQLITE_OK ){
         1824  +          pNew->zSql = (char*)&pNew[1];
         1825  +          memcpy(pNew->zSql, z, n+1);
         1826  +          pNew->pNext = p->pStatement;
         1827  +          if( p->pStatement ) pNew->iId = p->pStatement->iId+1;
         1828  +          p->pStatement = pNew;
         1829  +        }
         1830  +        sqlite3_finalize(pStmt);
         1831  +      }
         1832  +    }else{
         1833  +      idxDatabaseError(p->dbv, pzErr);
         1834  +    }
         1835  +  }
         1836  +
         1837  +  if( rc!=SQLITE_OK ){
         1838  +    idxScanFree(p->pScan, pScanOrig);
         1839  +    idxStatementFree(p->pStatement, pStmtOrig);
         1840  +    p->pScan = pScanOrig;
         1841  +    p->pStatement = pStmtOrig;
         1842  +  }
         1843  +
         1844  +  return rc;
         1845  +}
         1846  +
         1847  +int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr){
         1848  +  int rc;
         1849  +  IdxHashEntry *pEntry;
         1850  +
         1851  +  /* Do trigger processing to collect any extra IdxScan structures */
         1852  +  rc = idxProcessTriggers(p, pzErr);
         1853  +
         1854  +  /* Create candidate indexes within the in-memory database file */
         1855  +  if( rc==SQLITE_OK ){
         1856  +    rc = idxCreateCandidates(p, pzErr);
         1857  +  }
         1858  +
         1859  +  /* Generate the stat1 data */
         1860  +  if( rc==SQLITE_OK ){
         1861  +    rc = idxPopulateStat1(p, pzErr);
         1862  +  }
         1863  +
         1864  +  /* Formulate the EXPERT_REPORT_CANDIDATES text */
         1865  +  for(pEntry=p->hIdx.pFirst; pEntry; pEntry=pEntry->pNext){
         1866  +    p->zCandidates = idxAppendText(&rc, p->zCandidates, 
         1867  +        "%s;%s%s\n", pEntry->zVal, 
         1868  +        pEntry->zVal2 ? " -- stat1: " : "", pEntry->zVal2
         1869  +    );
         1870  +  }
         1871  +
         1872  +  /* Figure out which of the candidate indexes are preferred by the query
         1873  +  ** planner and report the results to the user.  */
         1874  +  if( rc==SQLITE_OK ){
         1875  +    rc = idxFindIndexes(p, pzErr);
         1876  +  }
         1877  +
         1878  +  if( rc==SQLITE_OK ){
         1879  +    p->bRun = 1;
         1880  +  }
         1881  +  return rc;
         1882  +}
         1883  +
         1884  +/*
         1885  +** Return the total number of statements that have been added to this
         1886  +** sqlite3expert using sqlite3_expert_sql().
         1887  +*/
         1888  +int sqlite3_expert_count(sqlite3expert *p){
         1889  +  int nRet = 0;
         1890  +  if( p->pStatement ) nRet = p->pStatement->iId+1;
         1891  +  return nRet;
         1892  +}
         1893  +
         1894  +/*
         1895  +** Return a component of the report.
         1896  +*/
         1897  +const char *sqlite3_expert_report(sqlite3expert *p, int iStmt, int eReport){
         1898  +  const char *zRet = 0;
         1899  +  IdxStatement *pStmt;
         1900  +
         1901  +  if( p->bRun==0 ) return 0;
         1902  +  for(pStmt=p->pStatement; pStmt && pStmt->iId!=iStmt; pStmt=pStmt->pNext);
         1903  +  switch( eReport ){
         1904  +    case EXPERT_REPORT_SQL:
         1905  +      if( pStmt ) zRet = pStmt->zSql;
         1906  +      break;
         1907  +    case EXPERT_REPORT_INDEXES:
         1908  +      if( pStmt ) zRet = pStmt->zIdx;
         1909  +      break;
         1910  +    case EXPERT_REPORT_PLAN:
         1911  +      if( pStmt ) zRet = pStmt->zEQP;
         1912  +      break;
         1913  +    case EXPERT_REPORT_CANDIDATES:
         1914  +      zRet = p->zCandidates;
         1915  +      break;
         1916  +  }
         1917  +  return zRet;
         1918  +}
         1919  +
         1920  +/*
         1921  +** Free an sqlite3expert object.
         1922  +*/
         1923  +void sqlite3_expert_destroy(sqlite3expert *p){
         1924  +  if( p ){
         1925  +    sqlite3_close(p->dbm);
         1926  +    sqlite3_close(p->dbv);
         1927  +    idxScanFree(p->pScan, 0);
         1928  +    idxStatementFree(p->pStatement, 0);
         1929  +    idxTableFree(p->pTable);
         1930  +    idxWriteFree(p->pWrite);
         1931  +    idxHashClear(&p->hIdx);
         1932  +    sqlite3_free(p->zCandidates);
         1933  +    sqlite3_free(p);
         1934  +  }
         1935  +}

Added ext/expert/sqlite3expert.h.

            1  +/*
            2  +** 2017 April 07
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +*/
           13  +
           14  +
           15  +#include "sqlite3.h"
           16  +
           17  +typedef struct sqlite3expert sqlite3expert;
           18  +
           19  +/*
           20  +** Create a new sqlite3expert object.
           21  +**
           22  +** If successful, a pointer to the new object is returned and (*pzErr) set
           23  +** to NULL. Or, if an error occurs, NULL is returned and (*pzErr) set to
           24  +** an English-language error message. In this case it is the responsibility
           25  +** of the caller to eventually free the error message buffer using
           26  +** sqlite3_free().
           27  +*/
           28  +sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErr);
           29  +
           30  +/*
           31  +** Configure an sqlite3expert object.
           32  +**
           33  +** EXPERT_CONFIG_SAMPLE:
           34  +**   By default, sqlite3_expert_analyze() generates sqlite_stat1 data for
           35  +**   each candidate index. This involves scanning and sorting the entire
           36  +**   contents of each user database table once for each candidate index
           37  +**   associated with the table. For large databases, this can be 
           38  +**   prohibitively slow. This option allows the sqlite3expert object to
           39  +**   be configured so that sqlite_stat1 data is instead generated based on a
           40  +**   subset of each table, or so that no sqlite_stat1 data is used at all.
           41  +**
           42  +**   A single integer argument is passed to this option. If the value is less
           43  +**   than or equal to zero, then no sqlite_stat1 data is generated or used by
           44  +**   the analysis - indexes are recommended based on the database schema only.
           45  +**   Or, if the value is 100 or greater, complete sqlite_stat1 data is
           46  +**   generated for each candidate index (this is the default). Finally, if the
           47  +**   value falls between 0 and 100, then it represents the percentage of user
           48  +**   table rows that should be considered when generating sqlite_stat1 data.
           49  +**
           50  +**   Examples:
           51  +**
           52  +**     // Do not generate any sqlite_stat1 data
           53  +**     sqlite3_expert_config(pExpert, EXPERT_CONFIG_SAMPLE, 0);
           54  +**
           55  +**     // Generate sqlite_stat1 data based on 10% of the rows in each table.
           56  +**     sqlite3_expert_config(pExpert, EXPERT_CONFIG_SAMPLE, 10);
           57  +*/
           58  +int sqlite3_expert_config(sqlite3expert *p, int op, ...);
           59  +
           60  +#define EXPERT_CONFIG_SAMPLE 1    /* int */
           61  +
           62  +/*
           63  +** Specify zero or more SQL statements to be included in the analysis.
           64  +**
           65  +** Buffer zSql must contain zero or more complete SQL statements. This
           66  +** function parses all statements contained in the buffer and adds them
           67  +** to the internal list of statements to analyze. If successful, SQLITE_OK
           68  +** is returned and (*pzErr) set to NULL. Or, if an error occurs - for example
           69  +** due to a error in the SQL - an SQLite error code is returned and (*pzErr)
           70  +** may be set to point to an English language error message. In this case
           71  +** the caller is responsible for eventually freeing the error message buffer
           72  +** using sqlite3_free().
           73  +**
           74  +** If an error does occur while processing one of the statements in the
           75  +** buffer passed as the second argument, none of the statements in the
           76  +** buffer are added to the analysis.
           77  +**
           78  +** This function must be called before sqlite3_expert_analyze(). If a call
           79  +** to this function is made on an sqlite3expert object that has already
           80  +** been passed to sqlite3_expert_analyze() SQLITE_MISUSE is returned
           81  +** immediately and no statements are added to the analysis.
           82  +*/
           83  +int sqlite3_expert_sql(
           84  +  sqlite3expert *p,               /* From a successful sqlite3_expert_new() */
           85  +  const char *zSql,               /* SQL statement(s) to add */
           86  +  char **pzErr                    /* OUT: Error message (if any) */
           87  +);
           88  +
           89  +
           90  +/*
           91  +** This function is called after the sqlite3expert object has been configured
           92  +** with all SQL statements using sqlite3_expert_sql() to actually perform
           93  +** the analysis. Once this function has been called, it is not possible to
           94  +** add further SQL statements to the analysis.
           95  +**
           96  +** If successful, SQLITE_OK is returned and (*pzErr) is set to NULL. Or, if
           97  +** an error occurs, an SQLite error code is returned and (*pzErr) set to 
           98  +** point to a buffer containing an English language error message. In this
           99  +** case it is the responsibility of the caller to eventually free the buffer
          100  +** using sqlite3_free().
          101  +**
          102  +** If an error does occur within this function, the sqlite3expert object
          103  +** is no longer useful for any purpose. At that point it is no longer
          104  +** possible to add further SQL statements to the object or to re-attempt
          105  +** the analysis. The sqlite3expert object must still be freed using a call
          106  +** sqlite3_expert_destroy().
          107  +*/
          108  +int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr);
          109  +
          110  +/*
          111  +** Return the total number of statements loaded using sqlite3_expert_sql().
          112  +** The total number of SQL statements may be different from the total number
          113  +** to calls to sqlite3_expert_sql().
          114  +*/
          115  +int sqlite3_expert_count(sqlite3expert*);
          116  +
          117  +/*
          118  +** Return a component of the report.
          119  +**
          120  +** This function is called after sqlite3_expert_analyze() to extract the
          121  +** results of the analysis. Each call to this function returns either a
          122  +** NULL pointer or a pointer to a buffer containing a nul-terminated string.
          123  +** The value passed as the third argument must be one of the EXPERT_REPORT_*
          124  +** #define constants defined below.
          125  +**
          126  +** For some EXPERT_REPORT_* parameters, the buffer returned contains 
          127  +** information relating to a specific SQL statement. In these cases that
          128  +** SQL statement is identified by the value passed as the second argument.
          129  +** SQL statements are numbered from 0 in the order in which they are parsed.
          130  +** If an out-of-range value (less than zero or equal to or greater than the
          131  +** value returned by sqlite3_expert_count()) is passed as the second argument
          132  +** along with such an EXPERT_REPORT_* parameter, NULL is always returned.
          133  +**
          134  +** EXPERT_REPORT_SQL:
          135  +**   Return the text of SQL statement iStmt.
          136  +**
          137  +** EXPERT_REPORT_INDEXES:
          138  +**   Return a buffer containing the CREATE INDEX statements for all recommended
          139  +**   indexes for statement iStmt. If there are no new recommeded indexes, NULL 
          140  +**   is returned.
          141  +**
          142  +** EXPERT_REPORT_PLAN:
          143  +**   Return a buffer containing the EXPLAIN QUERY PLAN output for SQL query
          144  +**   iStmt after the proposed indexes have been added to the database schema.
          145  +**
          146  +** EXPERT_REPORT_CANDIDATES:
          147  +**   Return a pointer to a buffer containing the CREATE INDEX statements 
          148  +**   for all indexes that were tested (for all SQL statements). The iStmt
          149  +**   parameter is ignored for EXPERT_REPORT_CANDIDATES calls.
          150  +*/
          151  +const char *sqlite3_expert_report(sqlite3expert*, int iStmt, int eReport);
          152  +
          153  +/*
          154  +** Values for the third argument passed to sqlite3_expert_report().
          155  +*/
          156  +#define EXPERT_REPORT_SQL        1
          157  +#define EXPERT_REPORT_INDEXES    2
          158  +#define EXPERT_REPORT_PLAN       3
          159  +#define EXPERT_REPORT_CANDIDATES 4
          160  +
          161  +/*
          162  +** Free an (sqlite3expert*) handle and all associated resources. There 
          163  +** should be one call to this function for each successful call to 
          164  +** sqlite3-expert_new().
          165  +*/
          166  +void sqlite3_expert_destroy(sqlite3expert*);
          167  +
          168  +

Added ext/expert/test_expert.c.

            1  +/*
            2  +** 2017 April 07
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +*/
           13  +
           14  +#if defined(SQLITE_TEST)
           15  +
           16  +#include "sqlite3expert.h"
           17  +#include <assert.h>
           18  +#include <string.h>
           19  +
           20  +#if defined(INCLUDE_SQLITE_TCL_H)
           21  +#  include "sqlite_tcl.h"
           22  +#else
           23  +#  include "tcl.h"
           24  +#  ifndef SQLITE_TCLAPI
           25  +#    define SQLITE_TCLAPI
           26  +#  endif
           27  +#endif
           28  +
           29  +/*
           30  +** Extract an sqlite3* db handle from the object passed as the second
           31  +** argument. If successful, set *pDb to point to the db handle and return
           32  +** TCL_OK. Otherwise, return TCL_ERROR.
           33  +*/
           34  +static int dbHandleFromObj(Tcl_Interp *interp, Tcl_Obj *pObj, sqlite3 **pDb){
           35  +  Tcl_CmdInfo info;
           36  +  if( 0==Tcl_GetCommandInfo(interp, Tcl_GetString(pObj), &info) ){
           37  +    Tcl_AppendResult(interp, "no such handle: ", Tcl_GetString(pObj), 0);
           38  +    return TCL_ERROR;
           39  +  }
           40  +
           41  +  *pDb = *(sqlite3 **)info.objClientData;
           42  +  return TCL_OK;
           43  +}
           44  +
           45  +
           46  +/*
           47  +** Tclcmd:  $expert sql SQL
           48  +**          $expert analyze
           49  +**          $expert count
           50  +**          $expert report STMT EREPORT
           51  +**          $expert destroy
           52  +*/
           53  +static int SQLITE_TCLAPI testExpertCmd(
           54  +  void *clientData,
           55  +  Tcl_Interp *interp,
           56  +  int objc,
           57  +  Tcl_Obj *CONST objv[]
           58  +){
           59  +  sqlite3expert *pExpert = (sqlite3expert*)clientData;
           60  +  struct Subcmd {
           61  +    const char *zSub;
           62  +    int nArg;
           63  +    const char *zMsg;
           64  +  } aSub[] = {
           65  +    { "sql",       1, "TABLE",        }, /* 0 */
           66  +    { "analyze",   0, "",             }, /* 1 */
           67  +    { "count",     0, "",             }, /* 2 */
           68  +    { "report",    2, "STMT EREPORT", }, /* 3 */
           69  +    { "destroy",   0, "",             }, /* 4 */
           70  +    { 0 }
           71  +  };
           72  +  int iSub;
           73  +  int rc = TCL_OK;
           74  +  char *zErr = 0;
           75  +
           76  +  if( objc<2 ){
           77  +    Tcl_WrongNumArgs(interp, 1, objv, "SUBCOMMAND ...");
           78  +    return TCL_ERROR;
           79  +  }
           80  +  rc = Tcl_GetIndexFromObjStruct(interp, 
           81  +      objv[1], aSub, sizeof(aSub[0]), "sub-command", 0, &iSub
           82  +  );
           83  +  if( rc!=TCL_OK ) return rc;
           84  +  if( objc!=2+aSub[iSub].nArg ){
           85  +    Tcl_WrongNumArgs(interp, 2, objv, aSub[iSub].zMsg);
           86  +    return TCL_ERROR;
           87  +  }
           88  +
           89  +  switch( iSub ){
           90  +    case 0: {      /* sql */
           91  +      char *zArg = Tcl_GetString(objv[2]);
           92  +      rc = sqlite3_expert_sql(pExpert, zArg, &zErr);
           93  +      break;
           94  +    }
           95  +
           96  +    case 1: {      /* analyze */
           97  +      rc = sqlite3_expert_analyze(pExpert, &zErr);
           98  +      break;
           99  +    }
          100  +
          101  +    case 2: {      /* count */
          102  +      int n = sqlite3_expert_count(pExpert);
          103  +      Tcl_SetObjResult(interp, Tcl_NewIntObj(n));
          104  +      break;
          105  +    }
          106  +
          107  +    case 3: {      /* report */
          108  +      const char *aEnum[] = {
          109  +        "sql", "indexes", "plan", "candidates", 0
          110  +      };
          111  +      int iEnum;
          112  +      int iStmt;
          113  +      const char *zReport;
          114  +
          115  +      if( Tcl_GetIntFromObj(interp, objv[2], &iStmt) 
          116  +       || Tcl_GetIndexFromObj(interp, objv[3], aEnum, "report", 0, &iEnum)
          117  +      ){
          118  +        return TCL_ERROR;
          119  +      }
          120  +
          121  +      assert( EXPERT_REPORT_SQL==1 );
          122  +      assert( EXPERT_REPORT_INDEXES==2 );
          123  +      assert( EXPERT_REPORT_PLAN==3 );
          124  +      assert( EXPERT_REPORT_CANDIDATES==4 );
          125  +      zReport = sqlite3_expert_report(pExpert, iStmt, 1+iEnum);
          126  +      Tcl_SetObjResult(interp, Tcl_NewStringObj(zReport, -1));
          127  +      break;
          128  +    }
          129  +
          130  +    default:       /* destroy */
          131  +      assert( iSub==4 );     
          132  +      Tcl_DeleteCommand(interp, Tcl_GetString(objv[0]));
          133  +      break;
          134  +  }
          135  +
          136  +  if( rc!=TCL_OK ){
          137  +    if( zErr ){
          138  +      Tcl_SetObjResult(interp, Tcl_NewStringObj(zErr, -1));
          139  +    }else{
          140  +      extern const char *sqlite3ErrName(int);
          141  +      Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1));
          142  +    }
          143  +  }
          144  +  sqlite3_free(zErr);
          145  +  return rc;
          146  +}
          147  +
          148  +static void SQLITE_TCLAPI testExpertDel(void *clientData){
          149  +  sqlite3expert *pExpert = (sqlite3expert*)clientData;
          150  +  sqlite3_expert_destroy(pExpert);
          151  +}
          152  +
          153  +/*
          154  +** sqlite3_expert_new DB
          155  +*/
          156  +static int SQLITE_TCLAPI test_sqlite3_expert_new(
          157  +  void * clientData,
          158  +  Tcl_Interp *interp,
          159  +  int objc,
          160  +  Tcl_Obj *CONST objv[]
          161  +){
          162  +  static int iCmd = 0;
          163  +  sqlite3 *db;
          164  +  char *zCmd = 0;
          165  +  char *zErr = 0;
          166  +  sqlite3expert *pExpert;
          167  +  int rc = TCL_OK;
          168  +
          169  +  if( objc!=2 ){
          170  +    Tcl_WrongNumArgs(interp, 1, objv, "DB");
          171  +    return TCL_ERROR;
          172  +  }
          173  +  if( dbHandleFromObj(interp, objv[1], &db) ){
          174  +    return TCL_ERROR;
          175  +  }
          176  +
          177  +  zCmd = sqlite3_mprintf("sqlite3expert%d", ++iCmd);
          178  +  if( zCmd==0 ){
          179  +    Tcl_AppendResult(interp, "out of memory", (char*)0);
          180  +    return TCL_ERROR;
          181  +  }
          182  +
          183  +  pExpert = sqlite3_expert_new(db, &zErr);
          184  +  if( pExpert==0 ){
          185  +    Tcl_AppendResult(interp, zErr, (char*)0);
          186  +    rc = TCL_ERROR;
          187  +  }else{
          188  +    void *p = (void*)pExpert;
          189  +    Tcl_CreateObjCommand(interp, zCmd, testExpertCmd, p, testExpertDel);
          190  +    Tcl_SetObjResult(interp, Tcl_NewStringObj(zCmd, -1));
          191  +  }
          192  +
          193  +  sqlite3_free(zCmd);
          194  +  sqlite3_free(zErr);
          195  +  return rc;
          196  +}
          197  +
          198  +int TestExpert_Init(Tcl_Interp *interp){
          199  +  struct Cmd {
          200  +    const char *zCmd;
          201  +    Tcl_ObjCmdProc *xProc;
          202  +  } aCmd[] = {
          203  +    { "sqlite3_expert_new", test_sqlite3_expert_new },
          204  +  };
          205  +  int i;
          206  +
          207  +  for(i=0; i<sizeof(aCmd)/sizeof(struct Cmd); i++){
          208  +    struct Cmd *p = &aCmd[i];
          209  +    Tcl_CreateObjCommand(interp, p->zCmd, p->xProc, 0, 0);
          210  +  }
          211  +
          212  +  return TCL_OK;
          213  +}
          214  +
          215  +#endif

Changes to main.mk.

   293    293     shell.c \
   294    294     sqlite3.h
   295    295   
   296    296   
   297    297   # Source code to the test files.
   298    298   #
   299    299   TESTSRC = \
          300  +  $(TOP)/ext/expert/sqlite3expert.c \
          301  +  $(TOP)/ext/expert/test_expert.c \
   300    302     $(TOP)/ext/fts3/fts3_term.c \
   301    303     $(TOP)/ext/fts3/fts3_test.c \
   302    304     $(TOP)/ext/rbu/test_rbu.c \
   303    305     $(TOP)/src/test1.c \
   304    306     $(TOP)/src/test2.c \
   305    307     $(TOP)/src/test3.c \
   306    308     $(TOP)/src/test4.c \
................................................................................
   805    807   
   806    808   sqlite3_analyzer.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/spaceanal.tcl $(TOP)/tool/sqlite3_analyzer.c.in $(TOP)/tool/mkccode.tcl
   807    809   	tclsh $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in >sqlite3_analyzer.c
   808    810   
   809    811   sqlite3_analyzer$(EXE): sqlite3_analyzer.c
   810    812   	$(TCCX) $(TCL_FLAGS) sqlite3_analyzer.c -o $@ $(LIBTCL) $(THREADLIB) 
   811    813   
          814  +sqlite3_expert$(EXE): $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c
          815  +	$(TCCX) -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c -o sqlite3_expert$(EXE) $(THREADLIB)
          816  +
   812    817   CHECKER_DEPS =\
   813    818     $(TOP)/tool/mkccode.tcl \
   814    819     sqlite3.c \
   815    820     $(TOP)/src/tclsqlite.c \
   816    821     $(TOP)/ext/repair/sqlite3_checker.tcl \
   817    822     $(TOP)/ext/repair/checkindex.c \
   818    823     $(TOP)/ext/repair/checkfreelist.c \
................................................................................
  1031   1036   	rm -f wordcount wordcount.exe
  1032   1037   	rm -f rbu rbu.exe
  1033   1038   	rm -f srcck1 srcck1.exe
  1034   1039   	rm -f sqlite3.c sqlite3-*.c fts?amal.c tclsqlite3.c
  1035   1040   	rm -f sqlite3rc.h
  1036   1041   	rm -f shell.c sqlite3ext.h
  1037   1042   	rm -f sqlite3_analyzer sqlite3_analyzer.exe sqlite3_analyzer.c
         1043  +	rm -f sqlite3_expert sqlite3_expert.exe 
  1038   1044   	rm -f sqlite-*-output.vsix
  1039   1045   	rm -f mptester mptester.exe
  1040   1046   	rm -f fuzzershell fuzzershell.exe
  1041   1047   	rm -f fuzzcheck fuzzcheck.exe
  1042   1048   	rm -f sqldiff sqldiff.exe
  1043   1049   	rm -f fts5.* fts5parse.*
  1044   1050   	rm -f lsm.h lsm1.c

Changes to src/main.c.

   801    801       }
   802    802       case SQLITE_DBCONFIG_LOOKASIDE: {
   803    803         void *pBuf = va_arg(ap, void*); /* IMP: R-26835-10964 */
   804    804         int sz = va_arg(ap, int);       /* IMP: R-47871-25994 */
   805    805         int cnt = va_arg(ap, int);      /* IMP: R-04460-53386 */
   806    806         rc = setupLookaside(db, pBuf, sz, cnt);
   807    807         break;
          808  +    }
          809  +    case SQLITE_DBCONFIG_FULL_EQP: {
          810  +      int onoff = va_arg(ap, int);
          811  +      int *pRes = va_arg(ap, int*);
          812  +      if( onoff>0 ){
          813  +        db->bFullEQP = 1;
          814  +      }else if( onoff==0 ){
          815  +        db->bFullEQP = 0;
          816  +      }
          817  +      sqlite3ExpirePreparedStatements(db);
          818  +      if( pRes ){
          819  +        *pRes = db->bFullEQP;
          820  +      }
          821  +      rc = SQLITE_OK;
          822  +      break;
   808    823       }
   809    824       default: {
   810    825         static const struct {
   811    826           int op;      /* The opcode */
   812    827           u32 mask;    /* Mask of the bit in sqlite3.flags to set/clear */
   813    828         } aFlagOp[] = {
   814    829           { SQLITE_DBCONFIG_ENABLE_FKEY,           SQLITE_ForeignKeys    },

Changes to src/pragma.c.

  1076   1076     ** the returned data set are:
  1077   1077     **
  1078   1078     ** cid:        Column id (numbered from left to right, starting at 0)
  1079   1079     ** name:       Column name
  1080   1080     ** type:       Column declaration type.
  1081   1081     ** notnull:    True if 'NOT NULL' is part of column declaration
  1082   1082     ** dflt_value: The default value for the column, if any.
         1083  +  ** pk:         Non-zero for PK fields.
  1083   1084     */
  1084   1085     case PragTyp_TABLE_INFO: if( zRight ){
  1085   1086       Table *pTab;
  1086   1087       pTab = sqlite3LocateTable(pParse, LOCATE_NOERR, zRight, zDb);
  1087   1088       if( pTab ){
  1088   1089         int i, k;
  1089   1090         int nHidden = 0;

Changes to src/sqlite.h.in.

  2056   2056   ** operation before closing the connection. This option may be used to
  2057   2057   ** override this behaviour. The first parameter passed to this operation
  2058   2058   ** is an integer - non-zero to disable checkpoints-on-close, or zero (the
  2059   2059   ** default) to enable them. The second parameter is a pointer to an integer
  2060   2060   ** into which is written 0 or 1 to indicate whether checkpoints-on-close
  2061   2061   ** have been disabled - 0 if they are not disabled, 1 if they are.
  2062   2062   ** </dd>
  2063         -**
  2064   2063   ** <dt>SQLITE_DBCONFIG_ENABLE_QPSG</dt>
  2065   2064   ** <dd>^(The SQLITE_DBCONFIG_ENABLE_QPSG option activates or deactivates
  2066   2065   ** the [query planner stability guarantee] (QPSG).  When the QPSG is active,
  2067   2066   ** a single SQL query statement will always use the same algorithm regardless
  2068   2067   ** of values of [bound parameters].)^ The QPSG disables some query optimizations
  2069   2068   ** that look at the values of bound parameters, which can make some queries
  2070   2069   ** slower.  But the QPSG has the advantage of more predictable behavior.  With
  2071   2070   ** the QPSG active, SQLite will always use the same query plan in the field as
  2072   2071   ** was used during testing in the lab.
  2073   2072   ** </dd>
  2074         -**
         2073  +** <dt>SQLITE_DBCONFIG_FULL_EQP</dt>
         2074  +** <dd> By default, the output of EXPLAIN QUERY PLAN commands does not 
         2075  +** include output for any operations performed by trigger programs. This
         2076  +** option is used to set or clear (the default) a flag that governs this
         2077  +** behavior. The first parameter passed to this operation is an integer -
         2078  +** non-zero to enable output for trigger programs, or zero to disable it.
         2079  +** The second parameter is a pointer to an integer into which is written 
         2080  +** 0 or 1 to indicate whether output-for-triggers has been disabled - 0 if 
         2081  +** it is not disabled, 1 if it is.  
         2082  +** </dd>
  2075   2083   ** </dl>
  2076   2084   */
  2077   2085   #define SQLITE_DBCONFIG_MAINDBNAME            1000 /* const char* */
  2078   2086   #define SQLITE_DBCONFIG_LOOKASIDE             1001 /* void* int int */
  2079   2087   #define SQLITE_DBCONFIG_ENABLE_FKEY           1002 /* int int* */
  2080   2088   #define SQLITE_DBCONFIG_ENABLE_TRIGGER        1003 /* int int* */
  2081   2089   #define SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER 1004 /* int int* */
  2082   2090   #define SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION 1005 /* int int* */
  2083   2091   #define SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE      1006 /* int int* */
  2084   2092   #define SQLITE_DBCONFIG_ENABLE_QPSG           1007 /* int int* */
  2085         -
         2093  +#define SQLITE_DBCONFIG_FULL_EQP              1008 /* int int* */
  2086   2094   
  2087   2095   /*
  2088   2096   ** CAPI3REF: Enable Or Disable Extended Result Codes
  2089   2097   ** METHOD: sqlite3
  2090   2098   **
  2091   2099   ** ^The sqlite3_extended_result_codes() routine enables or disables the
  2092   2100   ** [extended result codes] feature of SQLite. ^The extended result
................................................................................
  8283   8291   ** value returned is one of [SQLITE_ROLLBACK], [SQLITE_IGNORE], [SQLITE_FAIL],
  8284   8292   ** [SQLITE_ABORT], or [SQLITE_REPLACE], according to the [ON CONFLICT] mode
  8285   8293   ** of the SQL statement that triggered the call to the [xUpdate] method of the
  8286   8294   ** [virtual table].
  8287   8295   */
  8288   8296   int sqlite3_vtab_on_conflict(sqlite3 *);
  8289   8297   
         8298  +/*
         8299  +** CAPI3REF: Determine The Collation For a Virtual Table Constraint
         8300  +**
         8301  +** This function may only be called from within a call to the [xBestIndex]
         8302  +** method of a [virtual table implementation]. 
         8303  +**
         8304  +** The first argument must be the database handle with which the virtual 
         8305  +** table is associated (the one passed to the [xConnect] or [xCreate] method 
         8306  +** to create the sqlite3_vtab object. The second argument must be an index
         8307  +** into the aConstraint[] array belonging to the sqlite3_index_info structure
         8308  +** passed to xBestIndex. This function returns a pointer to a buffer 
         8309  +** containing the name of the collation sequence for the corresponding
         8310  +** constraint.
         8311  +*/
         8312  +SQLITE_EXPERIMENTAL const char *sqlite3_vtab_collation(sqlite3*, int);
         8313  +
  8290   8314   /*
  8291   8315   ** CAPI3REF: Conflict resolution modes
  8292   8316   ** KEYWORDS: {conflict resolution mode}
  8293   8317   **
  8294   8318   ** These constants are returned by [sqlite3_vtab_on_conflict()] to
  8295   8319   ** inform a [virtual table] implementation what the [ON CONFLICT] mode
  8296   8320   ** is for the SQL statement being evaluated.

Changes to src/sqliteInt.h.

  1352   1352     signed char nextAutovac;      /* Autovac setting after VACUUM if >=0 */
  1353   1353     u8 suppressErr;               /* Do not issue error messages if true */
  1354   1354     u8 vtabOnConflict;            /* Value to return for s3_vtab_on_conflict() */
  1355   1355     u8 isTransactionSavepoint;    /* True if the outermost savepoint is a TS */
  1356   1356     u8 mTrace;                    /* zero or more SQLITE_TRACE flags */
  1357   1357     u8 skipBtreeMutex;            /* True if no shared-cache backends */
  1358   1358     u8 nSqlExec;                  /* Number of pending OP_SqlExec opcodes */
         1359  +  u8 bFullEQP;                  /* Include triggers in EQP output */
  1359   1360     int nextPagesize;             /* Pagesize after VACUUM if >0 */
  1360   1361     u32 magic;                    /* Magic number for detect library misuse */
  1361   1362     int nChange;                  /* Value returned by sqlite3_changes() */
  1362   1363     int nTotalChange;             /* Value returned by sqlite3_total_changes() */
  1363   1364     int aLimit[SQLITE_N_LIMIT];   /* Limits */
  1364   1365     int nMaxSorterMmap;           /* Maximum size of regions mapped by sorter */
  1365   1366     struct sqlite3InitInfo {      /* Information used during initialization */
................................................................................
  1417   1418   #endif
  1418   1419   #ifndef SQLITE_OMIT_VIRTUALTABLE
  1419   1420     int nVTrans;                  /* Allocated size of aVTrans */
  1420   1421     Hash aModule;                 /* populated by sqlite3_create_module() */
  1421   1422     VtabCtx *pVtabCtx;            /* Context for active vtab connect/create */
  1422   1423     VTable **aVTrans;             /* Virtual tables with open transactions */
  1423   1424     VTable *pDisconnect;    /* Disconnect these in next sqlite3_prepare() */
         1425  +  void *pBestIndexCtx;          /* For sqlite3_vtab_collation() */
  1424   1426   #endif
  1425   1427     Hash aFunc;                   /* Hash table of connection functions */
  1426   1428     Hash aCollSeq;                /* All collating sequences */
  1427   1429     BusyHandler busyHandler;      /* Busy callback */
  1428   1430     Db aDbStatic[2];              /* Static space for the 2 default backends */
  1429   1431     Savepoint *pSavepoint;        /* List of active savepoints */
  1430   1432     int busyTimeout;              /* Busy handler timeout, in msec */

Changes to src/test_tclsh.c.

   100    100     extern int Sqlitetesttcl_Init(Tcl_Interp*);
   101    101   #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4)
   102    102     extern int Sqlitetestfts3_Init(Tcl_Interp *interp);
   103    103   #endif
   104    104   #ifdef SQLITE_ENABLE_ZIPVFS
   105    105     extern int Zipvfs_Init(Tcl_Interp*);
   106    106   #endif
          107  +  extern int TestExpert_Init(Tcl_Interp*);
          108  +
   107    109     Tcl_CmdInfo cmdInfo;
   108    110   
   109    111     /* Since the primary use case for this binary is testing of SQLite,
   110    112     ** be sure to generate core files if we crash */
   111    113   #if defined(unix)
   112    114     { struct rlimit x;
   113    115       getrlimit(RLIMIT_CORE, &x);
................................................................................
   162    164     Fts5tcl_Init(interp);
   163    165     SqliteRbu_Init(interp);
   164    166     Sqlitetesttcl_Init(interp);
   165    167   
   166    168   #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4)
   167    169     Sqlitetestfts3_Init(interp);
   168    170   #endif
          171  +  TestExpert_Init(interp);
   169    172   
   170    173     Tcl_CreateObjCommand(
   171    174         interp, "load_testfixture_extensions", load_testfixture_extensions,0,0
   172    175     );
   173    176     return 0;
   174    177   }
   175    178   

Changes to src/vdbeaux.c.

  1635   1635     int nSub = 0;                        /* Number of sub-vdbes seen so far */
  1636   1636     SubProgram **apSub = 0;              /* Array of sub-vdbes */
  1637   1637     Mem *pSub = 0;                       /* Memory cell hold array of subprogs */
  1638   1638     sqlite3 *db = p->db;                 /* The database connection */
  1639   1639     int i;                               /* Loop counter */
  1640   1640     int rc = SQLITE_OK;                  /* Return code */
  1641   1641     Mem *pMem = &p->aMem[1];             /* First Mem of result set */
         1642  +  int bFull = (p->explain==1 || db->bFullEQP);
         1643  +  Op *pOp = 0;
  1642   1644   
  1643   1645     assert( p->explain );
  1644   1646     assert( p->magic==VDBE_MAGIC_RUN );
  1645   1647     assert( p->rc==SQLITE_OK || p->rc==SQLITE_BUSY || p->rc==SQLITE_NOMEM );
  1646   1648   
  1647   1649     /* Even though this opcode does not use dynamic strings for
  1648   1650     ** the result, result columns may become dynamic if the user calls
  1649   1651     ** sqlite3_column_text16(), causing a translation to UTF-16 encoding.
  1650   1652     */
  1651   1653     releaseMemArray(pMem, 8);
  1652   1654     p->pResultSet = 0;
  1653   1655   
  1654         -  if( p->rc==SQLITE_NOMEM_BKPT ){
         1656  +  if( p->rc==SQLITE_NOMEM ){
  1655   1657       /* This happens if a malloc() inside a call to sqlite3_column_text() or
  1656   1658       ** sqlite3_column_text16() failed.  */
  1657   1659       sqlite3OomFault(db);
  1658   1660       return SQLITE_ERROR;
  1659   1661     }
  1660   1662   
  1661   1663     /* When the number of output rows reaches nRow, that means the
................................................................................
  1662   1664     ** listing has finished and sqlite3_step() should return SQLITE_DONE.
  1663   1665     ** nRow is the sum of the number of rows in the main program, plus
  1664   1666     ** the sum of the number of rows in all trigger subprograms encountered
  1665   1667     ** so far.  The nRow value will increase as new trigger subprograms are
  1666   1668     ** encountered, but p->pc will eventually catch up to nRow.
  1667   1669     */
  1668   1670     nRow = p->nOp;
  1669         -  if( p->explain==1 ){
         1671  +  if( bFull ){
  1670   1672       /* The first 8 memory cells are used for the result set.  So we will
  1671   1673       ** commandeer the 9th cell to use as storage for an array of pointers
  1672   1674       ** to trigger subprograms.  The VDBE is guaranteed to have at least 9
  1673   1675       ** cells.  */
  1674   1676       assert( p->nMem>9 );
  1675   1677       pSub = &p->aMem[9];
  1676   1678       if( pSub->flags&MEM_Blob ){
................................................................................
  1682   1684       for(i=0; i<nSub; i++){
  1683   1685         nRow += apSub[i]->nOp;
  1684   1686       }
  1685   1687     }
  1686   1688   
  1687   1689     do{
  1688   1690       i = p->pc++;
  1689         -  }while( i<nRow && p->explain==2 && p->aOp[i].opcode!=OP_Explain );
  1690         -  if( i>=nRow ){
  1691         -    p->rc = SQLITE_OK;
  1692         -    rc = SQLITE_DONE;
  1693         -  }else if( db->u1.isInterrupted ){
  1694         -    p->rc = SQLITE_INTERRUPT;
  1695         -    rc = SQLITE_ERROR;
  1696         -    sqlite3VdbeError(p, sqlite3ErrStr(p->rc));
  1697         -  }else{
  1698         -    char *zP4;
  1699         -    Op *pOp;
         1691  +    if( i>=nRow ){
         1692  +      p->rc = SQLITE_OK;
         1693  +      rc = SQLITE_DONE;
         1694  +      break;
         1695  +    }
  1700   1696       if( i<p->nOp ){
  1701   1697         /* The output line number is small enough that we are still in the
  1702   1698         ** main program. */
  1703   1699         pOp = &p->aOp[i];
  1704   1700       }else{
  1705   1701         /* We are currently listing subprograms.  Figure out which one and
  1706   1702         ** pick up the appropriate opcode. */
................................................................................
  1707   1703         int j;
  1708   1704         i -= p->nOp;
  1709   1705         for(j=0; i>=apSub[j]->nOp; j++){
  1710   1706           i -= apSub[j]->nOp;
  1711   1707         }
  1712   1708         pOp = &apSub[j]->aOp[i];
  1713   1709       }
  1714         -    if( p->explain==1 ){
         1710  +
         1711  +    /* When an OP_Program opcode is encounter (the only opcode that has
         1712  +    ** a P4_SUBPROGRAM argument), expand the size of the array of subprograms
         1713  +    ** kept in p->aMem[9].z to hold the new program - assuming this subprogram
         1714  +    ** has not already been seen.
         1715  +    */
         1716  +    if( bFull && pOp->p4type==P4_SUBPROGRAM ){
         1717  +      int nByte = (nSub+1)*sizeof(SubProgram*);
         1718  +      int j;
         1719  +      for(j=0; j<nSub; j++){
         1720  +        if( apSub[j]==pOp->p4.pProgram ) break;
         1721  +      }
         1722  +      if( j==nSub ){
         1723  +        p->rc = sqlite3VdbeMemGrow(pSub, nByte, nSub!=0);
         1724  +        if( p->rc!=SQLITE_OK ){
         1725  +          rc = SQLITE_ERROR;
         1726  +          break;
         1727  +        }
         1728  +        apSub = (SubProgram **)pSub->z;
         1729  +        apSub[nSub++] = pOp->p4.pProgram;
         1730  +        pSub->flags |= MEM_Blob;
         1731  +        pSub->n = nSub*sizeof(SubProgram*);
         1732  +        nRow += pOp->p4.pProgram->nOp;
         1733  +      }
         1734  +    }
         1735  +  }while( p->explain==2 && pOp->opcode!=OP_Explain );
         1736  +
         1737  +  if( rc==SQLITE_OK ){
         1738  +    if( db->u1.isInterrupted ){
         1739  +      p->rc = SQLITE_INTERRUPT;
         1740  +      rc = SQLITE_ERROR;
         1741  +      sqlite3VdbeError(p, sqlite3ErrStr(p->rc));
         1742  +    }else{
         1743  +      char *zP4;
         1744  +      if( p->explain==1 ){
         1745  +        pMem->flags = MEM_Int;
         1746  +        pMem->u.i = i;                                /* Program counter */
         1747  +        pMem++;
         1748  +    
         1749  +        pMem->flags = MEM_Static|MEM_Str|MEM_Term;
         1750  +        pMem->z = (char*)sqlite3OpcodeName(pOp->opcode); /* Opcode */
         1751  +        assert( pMem->z!=0 );
         1752  +        pMem->n = sqlite3Strlen30(pMem->z);
         1753  +        pMem->enc = SQLITE_UTF8;
         1754  +        pMem++;
         1755  +      }
         1756  +
         1757  +      pMem->flags = MEM_Int;
         1758  +      pMem->u.i = pOp->p1;                          /* P1 */
         1759  +      pMem++;
         1760  +
  1715   1761         pMem->flags = MEM_Int;
  1716         -      pMem->u.i = i;                                /* Program counter */
         1762  +      pMem->u.i = pOp->p2;                          /* P2 */
  1717   1763         pMem++;
  1718         -  
  1719         -      pMem->flags = MEM_Static|MEM_Str|MEM_Term;
  1720         -      pMem->z = (char*)sqlite3OpcodeName(pOp->opcode); /* Opcode */
  1721         -      assert( pMem->z!=0 );
  1722         -      pMem->n = sqlite3Strlen30(pMem->z);
  1723         -      pMem->enc = SQLITE_UTF8;
         1764  +
         1765  +      pMem->flags = MEM_Int;
         1766  +      pMem->u.i = pOp->p3;                          /* P3 */
  1724   1767         pMem++;
  1725   1768   
  1726         -      /* When an OP_Program opcode is encounter (the only opcode that has
  1727         -      ** a P4_SUBPROGRAM argument), expand the size of the array of subprograms
  1728         -      ** kept in p->aMem[9].z to hold the new program - assuming this subprogram
  1729         -      ** has not already been seen.
  1730         -      */
  1731         -      if( pOp->p4type==P4_SUBPROGRAM ){
  1732         -        int nByte = (nSub+1)*sizeof(SubProgram*);
  1733         -        int j;
  1734         -        for(j=0; j<nSub; j++){
  1735         -          if( apSub[j]==pOp->p4.pProgram ) break;
  1736         -        }
  1737         -        if( j==nSub && SQLITE_OK==sqlite3VdbeMemGrow(pSub, nByte, nSub!=0) ){
  1738         -          apSub = (SubProgram **)pSub->z;
  1739         -          apSub[nSub++] = pOp->p4.pProgram;
  1740         -          pSub->flags |= MEM_Blob;
  1741         -          pSub->n = nSub*sizeof(SubProgram*);
  1742         -        }
  1743         -      }
  1744         -    }
  1745         -
  1746         -    pMem->flags = MEM_Int;
  1747         -    pMem->u.i = pOp->p1;                          /* P1 */
  1748         -    pMem++;
  1749         -
  1750         -    pMem->flags = MEM_Int;
  1751         -    pMem->u.i = pOp->p2;                          /* P2 */
  1752         -    pMem++;
  1753         -
  1754         -    pMem->flags = MEM_Int;
  1755         -    pMem->u.i = pOp->p3;                          /* P3 */
  1756         -    pMem++;
  1757         -
  1758         -    if( sqlite3VdbeMemClearAndResize(pMem, 100) ){ /* P4 */
  1759         -      assert( p->db->mallocFailed );
  1760         -      return SQLITE_ERROR;
  1761         -    }
  1762         -    pMem->flags = MEM_Str|MEM_Term;
  1763         -    zP4 = displayP4(pOp, pMem->z, pMem->szMalloc);
  1764         -    if( zP4!=pMem->z ){
  1765         -      pMem->n = 0;
  1766         -      sqlite3VdbeMemSetStr(pMem, zP4, -1, SQLITE_UTF8, 0);
  1767         -    }else{
  1768         -      assert( pMem->z!=0 );
  1769         -      pMem->n = sqlite3Strlen30(pMem->z);
  1770         -      pMem->enc = SQLITE_UTF8;
  1771         -    }
  1772         -    pMem++;
  1773         -
  1774         -    if( p->explain==1 ){
  1775         -      if( sqlite3VdbeMemClearAndResize(pMem, 4) ){
         1769  +      if( sqlite3VdbeMemClearAndResize(pMem, 100) ){ /* P4 */
  1776   1770           assert( p->db->mallocFailed );
  1777   1771           return SQLITE_ERROR;
  1778   1772         }
  1779   1773         pMem->flags = MEM_Str|MEM_Term;
  1780         -      pMem->n = 2;
  1781         -      sqlite3_snprintf(3, pMem->z, "%.2x", pOp->p5);   /* P5 */
  1782         -      pMem->enc = SQLITE_UTF8;
         1774  +      zP4 = displayP4(pOp, pMem->z, pMem->szMalloc);
         1775  +      if( zP4!=pMem->z ){
         1776  +        pMem->n = 0;
         1777  +        sqlite3VdbeMemSetStr(pMem, zP4, -1, SQLITE_UTF8, 0);
         1778  +      }else{
         1779  +        assert( pMem->z!=0 );
         1780  +        pMem->n = sqlite3Strlen30(pMem->z);
         1781  +        pMem->enc = SQLITE_UTF8;
         1782  +      }
  1783   1783         pMem++;
  1784         -  
         1784  +
         1785  +      if( p->explain==1 ){
         1786  +        if( sqlite3VdbeMemClearAndResize(pMem, 4) ){
         1787  +          assert( p->db->mallocFailed );
         1788  +          return SQLITE_ERROR;
         1789  +        }
         1790  +        pMem->flags = MEM_Str|MEM_Term;
         1791  +        pMem->n = 2;
         1792  +        sqlite3_snprintf(3, pMem->z, "%.2x", pOp->p5);   /* P5 */
         1793  +        pMem->enc = SQLITE_UTF8;
         1794  +        pMem++;
         1795  +    
  1785   1796   #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
  1786         -      if( sqlite3VdbeMemClearAndResize(pMem, 500) ){
  1787         -        assert( p->db->mallocFailed );
  1788         -        return SQLITE_ERROR;
  1789         -      }
  1790         -      pMem->flags = MEM_Str|MEM_Term;
  1791         -      pMem->n = displayComment(pOp, zP4, pMem->z, 500);
  1792         -      pMem->enc = SQLITE_UTF8;
         1797  +        if( sqlite3VdbeMemClearAndResize(pMem, 500) ){
         1798  +          assert( p->db->mallocFailed );
         1799  +          return SQLITE_ERROR;
         1800  +        }
         1801  +        pMem->flags = MEM_Str|MEM_Term;
         1802  +        pMem->n = displayComment(pOp, zP4, pMem->z, 500);
         1803  +        pMem->enc = SQLITE_UTF8;
  1793   1804   #else
  1794         -      pMem->flags = MEM_Null;                       /* Comment */
         1805  +        pMem->flags = MEM_Null;                       /* Comment */
  1795   1806   #endif
         1807  +      }
         1808  +
         1809  +      p->nResColumn = 8 - 4*(p->explain-1);
         1810  +      p->pResultSet = &p->aMem[1];
         1811  +      p->rc = SQLITE_OK;
         1812  +      rc = SQLITE_ROW;
  1796   1813       }
  1797         -
  1798         -    p->nResColumn = 8 - 4*(p->explain-1);
  1799         -    p->pResultSet = &p->aMem[1];
  1800         -    p->rc = SQLITE_OK;
  1801         -    rc = SQLITE_ROW;
  1802   1814     }
  1803   1815     return rc;
  1804   1816   }
  1805   1817   #endif /* SQLITE_OMIT_EXPLAIN */
  1806   1818   
  1807   1819   #ifdef SQLITE_DEBUG
  1808   1820   /*

Changes to src/where.c.

  3134   3134     WHERETRACE(0xffff, ("  bIn=%d prereqIn=%04llx prereqOut=%04llx\n",
  3135   3135                         *pbIn, (sqlite3_uint64)mPrereq,
  3136   3136                         (sqlite3_uint64)(pNew->prereq & ~mPrereq)));
  3137   3137   
  3138   3138     return rc;
  3139   3139   }
  3140   3140   
         3141  +
         3142  +/*
         3143  +** Context object used to pass information from whereLoopAddVirtual()
         3144  +** to sqlite3_vtab_collation().
         3145  +*/
         3146  +struct BestIndexCtx {
         3147  +  WhereClause *pWC;
         3148  +  sqlite3_index_info *pIdxInfo;
         3149  +  Parse *pParse;
         3150  +};
         3151  +
         3152  +/*
         3153  +** If this function is invoked from within an xBestIndex() callback, it
         3154  +** returns a pointer to a buffer containing the name of the collation
         3155  +** sequence associated with element iCons of the sqlite3_index_info.aConstraint
         3156  +** array. Or, if iCons is out of range or there is no active xBestIndex
         3157  +** call, return NULL.
         3158  +*/
         3159  +const char *sqlite3_vtab_collation(sqlite3 *db, int iCons){
         3160  +  struct BestIndexCtx *p = (struct BestIndexCtx*)db->pBestIndexCtx;
         3161  +  const char *zRet = 0;
         3162  +  if( p && iCons>=0 && iCons<p->pIdxInfo->nConstraint ){
         3163  +    int iTerm = p->pIdxInfo->aConstraint[iCons].iTermOffset;
         3164  +    Expr *pX = p->pWC->a[iTerm].pExpr;
         3165  +    CollSeq *pC = sqlite3BinaryCompareCollSeq(p->pParse,pX->pLeft,pX->pRight);
         3166  +    zRet = (pC ? pC->zName : "BINARY");
         3167  +  }
         3168  +  return zRet;
         3169  +}
  3141   3170   
  3142   3171   /*
  3143   3172   ** Add all WhereLoop objects for a table of the join identified by
  3144   3173   ** pBuilder->pNew->iTab.  That table is guaranteed to be a virtual table.
  3145   3174   **
  3146   3175   ** If there are no LEFT or CROSS JOIN joins in the query, both mPrereq and
  3147   3176   ** mUnusable are set to 0. Otherwise, mPrereq is a mask of all FROM clause
................................................................................
  3176   3205     struct SrcList_item *pSrc;   /* The FROM clause term to search */
  3177   3206     sqlite3_index_info *p;       /* Object to pass to xBestIndex() */
  3178   3207     int nConstraint;             /* Number of constraints in p */
  3179   3208     int bIn;                     /* True if plan uses IN(...) operator */
  3180   3209     WhereLoop *pNew;
  3181   3210     Bitmask mBest;               /* Tables used by best possible plan */
  3182   3211     u16 mNoOmit;
         3212  +  struct BestIndexCtx bic;
         3213  +  void *pSaved;
  3183   3214   
  3184   3215     assert( (mPrereq & mUnusable)==0 );
  3185   3216     pWInfo = pBuilder->pWInfo;
  3186   3217     pParse = pWInfo->pParse;
  3187   3218     pWC = pBuilder->pWC;
  3188   3219     pNew = pBuilder->pNew;
  3189   3220     pSrc = &pWInfo->pTabList->a[pNew->iTab];
................................................................................
  3197   3228     pNew->u.vtab.needFree = 0;
  3198   3229     nConstraint = p->nConstraint;
  3199   3230     if( whereLoopResize(pParse->db, pNew, nConstraint) ){
  3200   3231       sqlite3DbFree(pParse->db, p);
  3201   3232       return SQLITE_NOMEM_BKPT;
  3202   3233     }
  3203   3234   
         3235  +  bic.pWC = pWC;
         3236  +  bic.pIdxInfo = p;
         3237  +  bic.pParse = pParse;
         3238  +  pSaved = pParse->db->pBestIndexCtx;
         3239  +  pParse->db->pBestIndexCtx = (void*)&bic;
         3240  +
  3204   3241     /* First call xBestIndex() with all constraints usable. */
  3205   3242     WHERETRACE(0x40, ("  VirtualOne: all usable\n"));
  3206   3243     rc = whereLoopAddVirtualOne(pBuilder, mPrereq, ALLBITS, 0, p, mNoOmit, &bIn);
  3207   3244   
  3208   3245     /* If the call to xBestIndex() with all terms enabled produced a plan
  3209   3246     ** that does not require any source tables (IOW: a plan with mBest==0),
  3210   3247     ** then there is no point in making any further calls to xBestIndex() 
................................................................................
  3273   3310         rc = whereLoopAddVirtualOne(
  3274   3311             pBuilder, mPrereq, mPrereq, WO_IN, p, mNoOmit, &bIn);
  3275   3312       }
  3276   3313     }
  3277   3314   
  3278   3315     if( p->needToFreeIdxStr ) sqlite3_free(p->idxStr);
  3279   3316     sqlite3DbFreeNN(pParse->db, p);
         3317  +  pParse->db->pBestIndexCtx = pSaved;
  3280   3318     return rc;
  3281   3319   }
  3282   3320   #endif /* SQLITE_OMIT_VIRTUALTABLE */
  3283   3321   
  3284   3322   /*
  3285   3323   ** Add WhereLoop entries to handle OR terms.  This works for either
  3286   3324   ** btrees or virtual tables.

Changes to test/permutations.test.

    82     82   # various test scripts:
    83     83   #
    84     84   #   $alltests
    85     85   #   $allquicktests
    86     86   #
    87     87   set alltests [list]
    88     88   foreach f [glob $testdir/*.test] { lappend alltests [file tail $f] }
    89         -foreach f [glob -nocomplain       \
    90         -    $testdir/../ext/rtree/*.test  \
           89  +foreach f [glob -nocomplain            \
           90  +    $testdir/../ext/rtree/*.test       \
    91     91       $testdir/../ext/fts5/test/*.test   \
           92  +    $testdir/../ext/expert/*.test      \
    92     93       $testdir/../ext/lsm1/test/*.test   \
    93     94   ] {
    94     95     lappend alltests $f 
    95     96   }
    96     97   foreach f [glob -nocomplain $testdir/../ext/session/*.test] { 
    97     98     lappend alltests $f 
    98     99   }