/ Check-in [edceaccd]
Login

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

Overview
Comment:Merge recent enhancements from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | appendvfs
Files: files | file ages | folders
SHA3-256: edceaccd66a65d6b36e53ce33d760a7bd9c2261a592d12189f5f55417b5d5d74
User & Date: drh 2017-12-23 18:40:39
Context
2018-01-06
04:34
Add sqltclsh.exe to the windows makefile. check-in: a6d5c7c2 user: drh tags: appendvfs
2017-12-23
18:40
Merge recent enhancements from trunk. check-in: edceaccd user: drh tags: appendvfs
14:39
Simplification to the error handling logic in the extension loader. check-in: 07c77314 user: drh tags: trunk
2017-12-14
19:24
Fixes to the appendvfs.c extension. Add the "sqltclsh" application that uses appendvfs.c to find its scripts. check-in: ee248b52 user: drh tags: appendvfs
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 \
................................................................................
  1193   1195   
  1194   1196   sqltclsh.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/sqltclsh.tcl $(TOP)/ext/misc/appendvfs.c $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqltclsh.c.in	
  1195   1197   	$(TCLSH_CMD) $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqltclsh.c.in >sqltclsh.c
  1196   1198   
  1197   1199   sqltclsh$(TEXE): sqltclsh.c
  1198   1200   	$(LTLINK) sqltclsh.c -o $@ $(LIBTCL) $(TLIBS)
  1199   1201   
         1202  +sqlite3_expert$(TEXE): $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c
         1203  +	$(LTLINK)	$(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c -o sqlite3_expert $(TLIBS)
         1204  +
  1200   1205   CHECKER_DEPS =\
  1201   1206     $(TOP)/tool/mkccode.tcl \
  1202   1207     sqlite3.c \
  1203   1208     $(TOP)/src/tclsqlite.c \
  1204   1209     $(TOP)/ext/repair/sqlite3_checker.tcl \
  1205   1210     $(TOP)/ext/repair/checkindex.c \
  1206   1211     $(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  +set CLI [test_binary_name sqlite3]
           27  +set CMD [test_binary_name sqlite3_expert]
           28  +
           29  +proc squish {txt} {
           30  +  regsub -all {[[:space:]]+} $txt { }
           31  +}
           32  +
           33  +proc do_setup_rec_test {tn setup sql res} {
           34  +  reset_db
           35  +  db eval $setup
           36  +  uplevel [list do_rec_test $tn $sql $res]
           37  +}
           38  +
           39  +foreach {tn setup} {
           40  +  1 {
           41  +    if {![file executable $CMD]} { continue }
           42  +
           43  +    proc do_rec_test {tn sql res} {
           44  +      set res [squish [string trim $res]]
           45  +      set tst [subst -nocommands { 
           46  +        squish [string trim [exec $::CMD -verbose 0 -sql {$sql;} test.db]]
           47  +      }]
           48  +      uplevel [list do_test $tn $tst $res]
           49  +    }
           50  +  }
           51  +  2 {
           52  +    if {[info commands sqlite3_expert_new]==""} { continue }
           53  +
           54  +    proc do_rec_test {tn sql res} {
           55  +      set expert [sqlite3_expert_new db]
           56  +      $expert sql $sql
           57  +      $expert analyze
           58  +
           59  +      set result [list]
           60  +      for {set i 0} {$i < [$expert count]} {incr i} {
           61  +        set idx [string trim [$expert report $i indexes]]
           62  +        if {$idx==""} {set idx "(no new indexes)"}
           63  +        lappend result $idx
           64  +        lappend result [string trim [$expert report $i plan]]
           65  +      }
           66  +
           67  +      $expert destroy
           68  +
           69  +      set tst [subst -nocommands {set {} [squish [join {$result}]]}]
           70  +      uplevel [list do_test $tn $tst [string trim [squish $res]]]
           71  +    }
           72  +  }
           73  +  3 {
           74  +    if {![file executable $CLI]} { continue }
           75  +
           76  +    proc do_rec_test {tn sql res} {
           77  +      set res [squish [string trim $res]]
           78  +      set tst [subst -nocommands { 
           79  +        squish [string trim [exec $::CLI test.db ".expert" {$sql;}]]
           80  +      }]
           81  +      uplevel [list do_test $tn $tst $res]
           82  +    }
           83  +  }
           84  +} {
           85  +
           86  +  eval $setup
           87  +
           88  +
           89  +do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } {
           90  +  SELECT * FROM t1
           91  +} {
           92  +  (no new indexes)
           93  +  0|0|0|SCAN TABLE t1
           94  +}
           95  +
           96  +do_setup_rec_test $tn.2 {
           97  +  CREATE TABLE t1(a, b, c);
           98  +} {
           99  +  SELECT * FROM t1 WHERE b>?;
          100  +} {
          101  +  CREATE INDEX t1_idx_00000062 ON t1(b);
          102  +  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?)
          103  +}
          104  +
          105  +do_setup_rec_test $tn.3 {
          106  +  CREATE TABLE t1(a, b, c);
          107  +} {
          108  +  SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
          109  +} {
          110  +  CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE);
          111  +  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?)
          112  +}
          113  +
          114  +do_setup_rec_test $tn.4 {
          115  +  CREATE TABLE t1(a, b, c);
          116  +} {
          117  +  SELECT a FROM t1 ORDER BY b;
          118  +} {
          119  +  CREATE INDEX t1_idx_00000062 ON t1(b);
          120  +  0|0|0|SCAN TABLE t1 USING INDEX t1_idx_00000062
          121  +}
          122  +
          123  +do_setup_rec_test $tn.5 {
          124  +  CREATE TABLE t1(a, b, c);
          125  +} {
          126  +  SELECT a FROM t1 WHERE a=? ORDER BY b;
          127  +} {
          128  +  CREATE INDEX t1_idx_000123a7 ON t1(a, b);
          129  +  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
          130  +}
          131  +
          132  +do_setup_rec_test $tn.6 {
          133  +  CREATE TABLE t1(a, b, c);
          134  +} {
          135  +  SELECT min(a) FROM t1
          136  +} {
          137  +  CREATE INDEX t1_idx_00000061 ON t1(a);
          138  +  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061
          139  +}
          140  +
          141  +do_setup_rec_test $tn.7 {
          142  +  CREATE TABLE t1(a, b, c);
          143  +} {
          144  +  SELECT * FROM t1 ORDER BY a, b, c;
          145  +} {
          146  +  CREATE INDEX t1_idx_033e95fe ON t1(a, b, c);
          147  +  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe
          148  +}
          149  +
          150  +#do_setup_rec_test $tn.1.8 {
          151  +#  CREATE TABLE t1(a, b, c);
          152  +#} {
          153  +#  SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
          154  +#} {
          155  +#  CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c);
          156  +#  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222
          157  +#}
          158  +
          159  +do_setup_rec_test $tn.8.1 {
          160  +  CREATE TABLE t1(a COLLATE NOCase, b, c);
          161  +} {
          162  +  SELECT * FROM t1 WHERE a=?
          163  +} {
          164  +  CREATE INDEX t1_idx_00000061 ON t1(a);
          165  +  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?)
          166  +}
          167  +do_setup_rec_test $tn.8.2 {
          168  +  CREATE TABLE t1(a, b COLLATE nocase, c);
          169  +} {
          170  +  SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC;
          171  +} {
          172  +  CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c);
          173  +  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5cb97285
          174  +}
          175  +
          176  +
          177  +# Tables with names that require quotes.
          178  +#
          179  +do_setup_rec_test $tn.9.1 {
          180  +  CREATE TABLE "t t"(a, b, c);
          181  +} {
          182  +  SELECT * FROM "t t" WHERE a=?
          183  +} {
          184  +  CREATE INDEX 't t_idx_00000061' ON 't t'(a);
          185  +  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) 
          186  +}
          187  +
          188  +do_setup_rec_test $tn.9.2 {
          189  +  CREATE TABLE "t t"(a, b, c);
          190  +} {
          191  +  SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
          192  +} {
          193  +  CREATE INDEX 't t_idx_00000062' ON 't t'(b);
          194  +  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?)
          195  +}
          196  +
          197  +# Columns with names that require quotes.
          198  +#
          199  +do_setup_rec_test $tn.10.1 {
          200  +  CREATE TABLE t3(a, "b b", c);
          201  +} {
          202  +  SELECT * FROM t3 WHERE "b b" = ?
          203  +} {
          204  +  CREATE INDEX t3_idx_00050c52 ON t3('b b');
          205  +  0|0|0|SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?)
          206  +}
          207  +
          208  +do_setup_rec_test $tn.10.2 {
          209  +  CREATE TABLE t3(a, "b b", c);
          210  +} {
          211  +  SELECT * FROM t3 ORDER BY "b b"
          212  +} {
          213  +  CREATE INDEX t3_idx_00050c52 ON t3('b b');
          214  +  0|0|0|SCAN TABLE t3 USING INDEX t3_idx_00050c52
          215  +}
          216  +
          217  +# Transitive constraints
          218  +#
          219  +do_setup_rec_test $tn.11.1 {
          220  +  CREATE TABLE t5(a, b);
          221  +  CREATE TABLE t6(c, d);
          222  +} {
          223  +  SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
          224  +} {
          225  +  CREATE INDEX t5_idx_000123a7 ON t5(a, b);
          226  +  CREATE INDEX t6_idx_00000063 ON t6(c);
          227  +  0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 
          228  +  0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
          229  +}
          230  +
          231  +# OR terms.
          232  +#
          233  +do_setup_rec_test $tn.12.1 {
          234  +  CREATE TABLE t7(a, b);
          235  +} {
          236  +  SELECT * FROM t7 WHERE a=? OR b=?
          237  +} {
          238  +  CREATE INDEX t7_idx_00000062 ON t7(b);
          239  +  CREATE INDEX t7_idx_00000061 ON t7(a);
          240  +  0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 
          241  +  0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
          242  +}
          243  +
          244  +# rowid terms.
          245  +#
          246  +do_setup_rec_test $tn.13.1 {
          247  +  CREATE TABLE t8(a, b);
          248  +} {
          249  +  SELECT * FROM t8 WHERE rowid=?
          250  +} {
          251  +  (no new indexes)
          252  +  0|0|0|SEARCH TABLE t8 USING INTEGER PRIMARY KEY (rowid=?)
          253  +}
          254  +do_setup_rec_test $tn.13.2 {
          255  +  CREATE TABLE t8(a, b);
          256  +} {
          257  +  SELECT * FROM t8 ORDER BY rowid
          258  +} {
          259  +  (no new indexes)
          260  +  0|0|0|SCAN TABLE t8
          261  +}
          262  +do_setup_rec_test $tn.13.3 {
          263  +  CREATE TABLE t8(a, b);
          264  +} {
          265  +  SELECT * FROM t8 WHERE a=? ORDER BY rowid
          266  +} {
          267  +  CREATE INDEX t8_idx_00000061 ON t8(a); 
          268  +  0|0|0|SEARCH TABLE t8 USING INDEX t8_idx_00000061 (a=?)
          269  +}
          270  +
          271  +# Triggers
          272  +#
          273  +do_setup_rec_test $tn.14 {
          274  +  CREATE TABLE t9(a, b, c);
          275  +  CREATE TABLE t10(a, b, c);
          276  +  CREATE TRIGGER t9t AFTER INSERT ON t9 BEGIN
          277  +    UPDATE t10 SET a=new.a WHERE b = new.b;
          278  +  END;
          279  +} {
          280  +  INSERT INTO t9 VALUES(?, ?, ?);
          281  +} {
          282  +  CREATE INDEX t10_idx_00000062 ON t10(b); 
          283  +  0|0|0|SEARCH TABLE t10 USING INDEX t10_idx_00000062 (b=?)
          284  +}
          285  +
          286  +do_setup_rec_test $tn.15 {
          287  +  CREATE TABLE t1(a, b);
          288  +  CREATE TABLE t2(c, d);
          289  +
          290  +  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
          291  +  INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s;
          292  +
          293  +  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
          294  +  INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s;
          295  +} {
          296  +  SELECT * FROM t2, t1 WHERE b=? AND d=? AND t2.rowid=t1.rowid
          297  +} {
          298  +  CREATE INDEX t2_idx_00000064 ON t2(d);
          299  +  0|0|0|SEARCH TABLE t2 USING INDEX t2_idx_00000064 (d=?) 
          300  +  0|1|1|SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
          301  +}
          302  +
          303  +do_setup_rec_test $tn.16 {
          304  +  CREATE TABLE t1(a, b);
          305  +} {
          306  +  SELECT * FROM t1 WHERE b IS NOT NULL;
          307  +} {
          308  +  (no new indexes)
          309  +  0|0|0|SCAN TABLE t1
          310  +}
          311  +
          312  +}
          313  +
          314  +proc do_candidates_test {tn sql res} {
          315  +  set res [squish [string trim $res]]
          316  +
          317  +  set expert [sqlite3_expert_new db]
          318  +  $expert sql $sql
          319  +  $expert analyze
          320  +
          321  +  set candidates [squish [string trim [$expert report 0 candidates]]]
          322  +  $expert destroy
          323  +
          324  +  uplevel [list do_test $tn [list set {} $candidates] $res]
          325  +}
          326  +
          327  +
          328  +reset_db
          329  +do_execsql_test 3.0 {
          330  +  CREATE TABLE t1(a, b);
          331  +  CREATE TABLE t2(c, d);
          332  +
          333  +  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
          334  +  INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s;
          335  +
          336  +  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
          337  +  INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s;
          338  +}
          339  +do_candidates_test 3.1 {
          340  +  SELECT * FROM t1,t2 WHERE (b=? OR a=?) AND (c=? OR d=?)
          341  +} {
          342  +  CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20 
          343  +  CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50 
          344  +  CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20 
          345  +  CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5
          346  +}
          347  +
          348  +do_candidates_test 3.2 {
          349  +  SELECT * FROM t1,t2 WHERE a=? AND b=? AND c=? AND d=?
          350  +} {
          351  +  CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 17
          352  +  CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5
          353  +}
          354  +
          355  +do_execsql_test 3.2 {
          356  +  CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50 
          357  +  CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20 
          358  +  CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 16
          359  +
          360  +  CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20 
          361  +  CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5
          362  +  CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5
          363  +
          364  +  ANALYZE;
          365  +  SELECT * FROM sqlite_stat1 ORDER BY 1, 2;
          366  +} {
          367  +  t1 t1_idx_00000061 {100 50} 
          368  +  t1 t1_idx_00000062 {100 20}
          369  +  t1 t1_idx_000123a7 {100 50 17}
          370  +  t2 t2_idx_00000063 {100 20} 
          371  +  t2 t2_idx_00000064 {100 5} 
          372  +  t2 t2_idx_0001295b {100 20 5}
          373  +}
          374  +
          375  +
          376  +finish_test
          377  +

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  +  int rc = SQLITE_OK;
          438  +  int n = 0;
          439  +  IdxScan *pScan;
          440  +  const int opmask = 
          441  +    SQLITE_INDEX_CONSTRAINT_EQ | SQLITE_INDEX_CONSTRAINT_GT |
          442  +    SQLITE_INDEX_CONSTRAINT_LT | SQLITE_INDEX_CONSTRAINT_GE |
          443  +    SQLITE_INDEX_CONSTRAINT_LE;
          444  +
          445  +  pScan = idxMalloc(&rc, sizeof(IdxScan));
          446  +  if( pScan ){
          447  +    int i;
          448  +
          449  +    /* Link the new scan object into the list */
          450  +    pScan->pTab = p->pTab;
          451  +    pScan->pNextScan = p->pExpert->pScan;
          452  +    p->pExpert->pScan = pScan;
          453  +
          454  +    /* Add the constraints to the IdxScan object */
          455  +    for(i=0; i<pIdxInfo->nConstraint; i++){
          456  +      struct sqlite3_index_constraint *pCons = &pIdxInfo->aConstraint[i];
          457  +      if( pCons->usable 
          458  +       && pCons->iColumn>=0 
          459  +       && p->pTab->aCol[pCons->iColumn].iPk==0
          460  +       && (pCons->op & opmask) 
          461  +      ){
          462  +        IdxConstraint *pNew;
          463  +        const char *zColl = sqlite3_vtab_collation(pIdxInfo, i);
          464  +        pNew = idxNewConstraint(&rc, zColl);
          465  +        if( pNew ){
          466  +          pNew->iCol = pCons->iColumn;
          467  +          if( pCons->op==SQLITE_INDEX_CONSTRAINT_EQ ){
          468  +            pNew->pNext = pScan->pEq;
          469  +            pScan->pEq = pNew;
          470  +          }else{
          471  +            pNew->bRange = 1;
          472  +            pNew->pNext = pScan->pRange;
          473  +            pScan->pRange = pNew;
          474  +          }
          475  +        }
          476  +        n++;
          477  +        pIdxInfo->aConstraintUsage[i].argvIndex = n;
          478  +      }
          479  +    }
          480  +
          481  +    /* Add the ORDER BY to the IdxScan object */
          482  +    for(i=pIdxInfo->nOrderBy-1; i>=0; i--){
          483  +      int iCol = pIdxInfo->aOrderBy[i].iColumn;
          484  +      if( iCol>=0 ){
          485  +        IdxConstraint *pNew = idxNewConstraint(&rc, p->pTab->aCol[iCol].zColl);
          486  +        if( pNew ){
          487  +          pNew->iCol = iCol;
          488  +          pNew->bDesc = pIdxInfo->aOrderBy[i].desc;
          489  +          pNew->pNext = pScan->pOrder;
          490  +          pNew->pLink = pScan->pOrder;
          491  +          pScan->pOrder = pNew;
          492  +          n++;
          493  +        }
          494  +      }
          495  +    }
          496  +  }
          497  +
          498  +  pIdxInfo->estimatedCost = 1000000.0 / n;
          499  +  return rc;
          500  +}
          501  +
          502  +static int expertUpdate(
          503  +  sqlite3_vtab *pVtab, 
          504  +  int nData, 
          505  +  sqlite3_value **azData, 
          506  +  sqlite_int64 *pRowid
          507  +){
          508  +  return SQLITE_OK;
          509  +}
          510  +
          511  +/* 
          512  +** Virtual table module xOpen method.
          513  +*/
          514  +static int expertOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
          515  +  int rc = SQLITE_OK;
          516  +  ExpertCsr *pCsr;
          517  +  pCsr = idxMalloc(&rc, sizeof(ExpertCsr));
          518  +  *ppCursor = (sqlite3_vtab_cursor*)pCsr;
          519  +  return rc;
          520  +}
          521  +
          522  +/* 
          523  +** Virtual table module xClose method.
          524  +*/
          525  +static int expertClose(sqlite3_vtab_cursor *cur){
          526  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          527  +  sqlite3_finalize(pCsr->pData);
          528  +  sqlite3_free(pCsr);
          529  +  return SQLITE_OK;
          530  +}
          531  +
          532  +/*
          533  +** Virtual table module xEof method.
          534  +**
          535  +** Return non-zero if the cursor does not currently point to a valid 
          536  +** record (i.e if the scan has finished), or zero otherwise.
          537  +*/
          538  +static int expertEof(sqlite3_vtab_cursor *cur){
          539  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          540  +  return pCsr->pData==0;
          541  +}
          542  +
          543  +/* 
          544  +** Virtual table module xNext method.
          545  +*/
          546  +static int expertNext(sqlite3_vtab_cursor *cur){
          547  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          548  +  int rc = SQLITE_OK;
          549  +
          550  +  assert( pCsr->pData );
          551  +  rc = sqlite3_step(pCsr->pData);
          552  +  if( rc!=SQLITE_ROW ){
          553  +    rc = sqlite3_finalize(pCsr->pData);
          554  +    pCsr->pData = 0;
          555  +  }else{
          556  +    rc = SQLITE_OK;
          557  +  }
          558  +
          559  +  return rc;
          560  +}
          561  +
          562  +/* 
          563  +** Virtual table module xRowid method.
          564  +*/
          565  +static int expertRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
          566  +  *pRowid = 0;
          567  +  return SQLITE_OK;
          568  +}
          569  +
          570  +/* 
          571  +** Virtual table module xColumn method.
          572  +*/
          573  +static int expertColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){
          574  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          575  +  sqlite3_value *pVal;
          576  +  pVal = sqlite3_column_value(pCsr->pData, i);
          577  +  if( pVal ){
          578  +    sqlite3_result_value(ctx, pVal);
          579  +  }
          580  +  return SQLITE_OK;
          581  +}
          582  +
          583  +/* 
          584  +** Virtual table module xFilter method.
          585  +*/
          586  +static int expertFilter(
          587  +  sqlite3_vtab_cursor *cur, 
          588  +  int idxNum, const char *idxStr,
          589  +  int argc, sqlite3_value **argv
          590  +){
          591  +  ExpertCsr *pCsr = (ExpertCsr*)cur;
          592  +  ExpertVtab *pVtab = (ExpertVtab*)(cur->pVtab);
          593  +  sqlite3expert *pExpert = pVtab->pExpert;
          594  +  int rc;
          595  +
          596  +  rc = sqlite3_finalize(pCsr->pData);
          597  +  pCsr->pData = 0;
          598  +  if( rc==SQLITE_OK ){
          599  +    rc = idxPrintfPrepareStmt(pExpert->db, &pCsr->pData, &pVtab->base.zErrMsg,
          600  +        "SELECT * FROM main.%Q WHERE sample()", pVtab->pTab->zName
          601  +    );
          602  +  }
          603  +
          604  +  if( rc==SQLITE_OK ){
          605  +    rc = expertNext(cur);
          606  +  }
          607  +  return rc;
          608  +}
          609  +
          610  +static int idxRegisterVtab(sqlite3expert *p){
          611  +  static sqlite3_module expertModule = {
          612  +    2,                            /* iVersion */
          613  +    expertConnect,                /* xCreate - create a table */
          614  +    expertConnect,                /* xConnect - connect to an existing table */
          615  +    expertBestIndex,              /* xBestIndex - Determine search strategy */
          616  +    expertDisconnect,             /* xDisconnect - Disconnect from a table */
          617  +    expertDisconnect,             /* xDestroy - Drop a table */
          618  +    expertOpen,                   /* xOpen - open a cursor */
          619  +    expertClose,                  /* xClose - close a cursor */
          620  +    expertFilter,                 /* xFilter - configure scan constraints */
          621  +    expertNext,                   /* xNext - advance a cursor */
          622  +    expertEof,                    /* xEof */
          623  +    expertColumn,                 /* xColumn - read data */
          624  +    expertRowid,                  /* xRowid - read data */
          625  +    expertUpdate,                 /* xUpdate - write data */
          626  +    0,                            /* xBegin - begin transaction */
          627  +    0,                            /* xSync - sync transaction */
          628  +    0,                            /* xCommit - commit transaction */
          629  +    0,                            /* xRollback - rollback transaction */
          630  +    0,                            /* xFindFunction - function overloading */
          631  +    0,                            /* xRename - rename the table */
          632  +    0,                            /* xSavepoint */
          633  +    0,                            /* xRelease */
          634  +    0,                            /* xRollbackTo */
          635  +  };
          636  +
          637  +  return sqlite3_create_module(p->dbv, "expert", &expertModule, (void*)p);
          638  +}
          639  +/*
          640  +** End of virtual table implementation.
          641  +*************************************************************************/
          642  +/*
          643  +** Finalize SQL statement pStmt. If (*pRc) is SQLITE_OK when this function
          644  +** is called, set it to the return value of sqlite3_finalize() before
          645  +** returning. Otherwise, discard the sqlite3_finalize() return value.
          646  +*/
          647  +static void idxFinalize(int *pRc, sqlite3_stmt *pStmt){
          648  +  int rc = sqlite3_finalize(pStmt);
          649  +  if( *pRc==SQLITE_OK ) *pRc = rc;
          650  +}
          651  +
          652  +/*
          653  +** Attempt to allocate an IdxTable structure corresponding to table zTab
          654  +** in the main database of connection db. If successful, set (*ppOut) to
          655  +** point to the new object and return SQLITE_OK. Otherwise, return an
          656  +** SQLite error code and set (*ppOut) to NULL. In this case *pzErrmsg may be
          657  +** set to point to an error string.
          658  +**
          659  +** It is the responsibility of the caller to eventually free either the
          660  +** IdxTable object or error message using sqlite3_free().
          661  +*/
          662  +static int idxGetTableInfo(
          663  +  sqlite3 *db,                    /* Database connection to read details from */
          664  +  const char *zTab,               /* Table name */
          665  +  IdxTable **ppOut,               /* OUT: New object (if successful) */
          666  +  char **pzErrmsg                 /* OUT: Error message (if not) */
          667  +){
          668  +  sqlite3_stmt *p1 = 0;
          669  +  int nCol = 0;
          670  +  int nTab = STRLEN(zTab);
          671  +  int nByte = sizeof(IdxTable) + nTab + 1;
          672  +  IdxTable *pNew = 0;
          673  +  int rc, rc2;
          674  +  char *pCsr = 0;
          675  +
          676  +  rc = idxPrintfPrepareStmt(db, &p1, pzErrmsg, "PRAGMA table_info=%Q", zTab);
          677  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
          678  +    const char *zCol = (const char*)sqlite3_column_text(p1, 1);
          679  +    nByte += 1 + STRLEN(zCol);
          680  +    rc = sqlite3_table_column_metadata(
          681  +        db, "main", zTab, zCol, 0, &zCol, 0, 0, 0
          682  +    );
          683  +    nByte += 1 + STRLEN(zCol);
          684  +    nCol++;
          685  +  }
          686  +  rc2 = sqlite3_reset(p1);
          687  +  if( rc==SQLITE_OK ) rc = rc2;
          688  +
          689  +  nByte += sizeof(IdxColumn) * nCol;
          690  +  if( rc==SQLITE_OK ){
          691  +    pNew = idxMalloc(&rc, nByte);
          692  +  }
          693  +  if( rc==SQLITE_OK ){
          694  +    pNew->aCol = (IdxColumn*)&pNew[1];
          695  +    pNew->nCol = nCol;
          696  +    pCsr = (char*)&pNew->aCol[nCol];
          697  +  }
          698  +
          699  +  nCol = 0;
          700  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){
          701  +    const char *zCol = (const char*)sqlite3_column_text(p1, 1);
          702  +    int nCopy = STRLEN(zCol) + 1;
          703  +    pNew->aCol[nCol].zName = pCsr;
          704  +    pNew->aCol[nCol].iPk = sqlite3_column_int(p1, 5);
          705  +    memcpy(pCsr, zCol, nCopy);
          706  +    pCsr += nCopy;
          707  +
          708  +    rc = sqlite3_table_column_metadata(
          709  +        db, "main", zTab, zCol, 0, &zCol, 0, 0, 0
          710  +    );
          711  +    if( rc==SQLITE_OK ){
          712  +      nCopy = STRLEN(zCol) + 1;
          713  +      pNew->aCol[nCol].zColl = pCsr;
          714  +      memcpy(pCsr, zCol, nCopy);
          715  +      pCsr += nCopy;
          716  +    }
          717  +
          718  +    nCol++;
          719  +  }
          720  +  idxFinalize(&rc, p1);
          721  +
          722  +  if( rc!=SQLITE_OK ){
          723  +    sqlite3_free(pNew);
          724  +    pNew = 0;
          725  +  }else{
          726  +    pNew->zName = pCsr;
          727  +    memcpy(pNew->zName, zTab, nTab+1);
          728  +  }
          729  +
          730  +  *ppOut = pNew;
          731  +  return rc;
          732  +}
          733  +
          734  +/*
          735  +** This function is a no-op if *pRc is set to anything other than 
          736  +** SQLITE_OK when it is called.
          737  +**
          738  +** If *pRc is initially set to SQLITE_OK, then the text specified by
          739  +** the printf() style arguments is appended to zIn and the result returned
          740  +** in a buffer allocated by sqlite3_malloc(). sqlite3_free() is called on
          741  +** zIn before returning.
          742  +*/
          743  +static char *idxAppendText(int *pRc, char *zIn, const char *zFmt, ...){
          744  +  va_list ap;
          745  +  char *zAppend = 0;
          746  +  char *zRet = 0;
          747  +  int nIn = zIn ? STRLEN(zIn) : 0;
          748  +  int nAppend = 0;
          749  +  va_start(ap, zFmt);
          750  +  if( *pRc==SQLITE_OK ){
          751  +    zAppend = sqlite3_vmprintf(zFmt, ap);
          752  +    if( zAppend ){
          753  +      nAppend = STRLEN(zAppend);
          754  +      zRet = (char*)sqlite3_malloc(nIn + nAppend + 1);
          755  +    }
          756  +    if( zAppend && zRet ){
          757  +      memcpy(zRet, zIn, nIn);
          758  +      memcpy(&zRet[nIn], zAppend, nAppend+1);
          759  +    }else{
          760  +      sqlite3_free(zRet);
          761  +      zRet = 0;
          762  +      *pRc = SQLITE_NOMEM;
          763  +    }
          764  +    sqlite3_free(zAppend);
          765  +    sqlite3_free(zIn);
          766  +  }
          767  +  va_end(ap);
          768  +  return zRet;
          769  +}
          770  +
          771  +/*
          772  +** Return true if zId must be quoted in order to use it as an SQL
          773  +** identifier, or false otherwise.
          774  +*/
          775  +static int idxIdentifierRequiresQuotes(const char *zId){
          776  +  int i;
          777  +  for(i=0; zId[i]; i++){
          778  +    if( !(zId[i]=='_')
          779  +     && !(zId[i]>='0' && zId[i]<='9')
          780  +     && !(zId[i]>='a' && zId[i]<='z')
          781  +     && !(zId[i]>='A' && zId[i]<='Z')
          782  +    ){
          783  +      return 1;
          784  +    }
          785  +  }
          786  +  return 0;
          787  +}
          788  +
          789  +/*
          790  +** This function appends an index column definition suitable for constraint
          791  +** pCons to the string passed as zIn and returns the result.
          792  +*/
          793  +static char *idxAppendColDefn(
          794  +  int *pRc,                       /* IN/OUT: Error code */
          795  +  char *zIn,                      /* Column defn accumulated so far */
          796  +  IdxTable *pTab,                 /* Table index will be created on */
          797  +  IdxConstraint *pCons
          798  +){
          799  +  char *zRet = zIn;
          800  +  IdxColumn *p = &pTab->aCol[pCons->iCol];
          801  +  if( zRet ) zRet = idxAppendText(pRc, zRet, ", ");
          802  +
          803  +  if( idxIdentifierRequiresQuotes(p->zName) ){
          804  +    zRet = idxAppendText(pRc, zRet, "%Q", p->zName);
          805  +  }else{
          806  +    zRet = idxAppendText(pRc, zRet, "%s", p->zName);
          807  +  }
          808  +
          809  +  if( sqlite3_stricmp(p->zColl, pCons->zColl) ){
          810  +    if( idxIdentifierRequiresQuotes(pCons->zColl) ){
          811  +      zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl);
          812  +    }else{
          813  +      zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl);
          814  +    }
          815  +  }
          816  +
          817  +  if( pCons->bDesc ){
          818  +    zRet = idxAppendText(pRc, zRet, " DESC");
          819  +  }
          820  +  return zRet;
          821  +}
          822  +
          823  +/*
          824  +** Search database dbm for an index compatible with the one idxCreateFromCons()
          825  +** would create from arguments pScan, pEq and pTail. If no error occurs and 
          826  +** such an index is found, return non-zero. Or, if no such index is found,
          827  +** return zero.
          828  +**
          829  +** If an error occurs, set *pRc to an SQLite error code and return zero.
          830  +*/
          831  +static int idxFindCompatible(
          832  +  int *pRc,                       /* OUT: Error code */
          833  +  sqlite3* dbm,                   /* Database to search */
          834  +  IdxScan *pScan,                 /* Scan for table to search for index on */
          835  +  IdxConstraint *pEq,             /* List of == constraints */
          836  +  IdxConstraint *pTail            /* List of range constraints */
          837  +){
          838  +  const char *zTbl = pScan->pTab->zName;
          839  +  sqlite3_stmt *pIdxList = 0;
          840  +  IdxConstraint *pIter;
          841  +  int nEq = 0;                    /* Number of elements in pEq */
          842  +  int rc;
          843  +
          844  +  /* Count the elements in list pEq */
          845  +  for(pIter=pEq; pIter; pIter=pIter->pLink) nEq++;
          846  +
          847  +  rc = idxPrintfPrepareStmt(dbm, &pIdxList, 0, "PRAGMA index_list=%Q", zTbl);
          848  +  while( rc==SQLITE_OK && sqlite3_step(pIdxList)==SQLITE_ROW ){
          849  +    int bMatch = 1;
          850  +    IdxConstraint *pT = pTail;
          851  +    sqlite3_stmt *pInfo = 0;
          852  +    const char *zIdx = (const char*)sqlite3_column_text(pIdxList, 1);
          853  +
          854  +    /* Zero the IdxConstraint.bFlag values in the pEq list */
          855  +    for(pIter=pEq; pIter; pIter=pIter->pLink) pIter->bFlag = 0;
          856  +
          857  +    rc = idxPrintfPrepareStmt(dbm, &pInfo, 0, "PRAGMA index_xInfo=%Q", zIdx);
          858  +    while( rc==SQLITE_OK && sqlite3_step(pInfo)==SQLITE_ROW ){
          859  +      int iIdx = sqlite3_column_int(pInfo, 0);
          860  +      int iCol = sqlite3_column_int(pInfo, 1);
          861  +      const char *zColl = (const char*)sqlite3_column_text(pInfo, 4);
          862  +
          863  +      if( iIdx<nEq ){
          864  +        for(pIter=pEq; pIter; pIter=pIter->pLink){
          865  +          if( pIter->bFlag ) continue;
          866  +          if( pIter->iCol!=iCol ) continue;
          867  +          if( sqlite3_stricmp(pIter->zColl, zColl) ) continue;
          868  +          pIter->bFlag = 1;
          869  +          break;
          870  +        }
          871  +        if( pIter==0 ){
          872  +          bMatch = 0;
          873  +          break;
          874  +        }
          875  +      }else{
          876  +        if( pT ){
          877  +          if( pT->iCol!=iCol || sqlite3_stricmp(pT->zColl, zColl) ){
          878  +            bMatch = 0;
          879  +            break;
          880  +          }
          881  +          pT = pT->pLink;
          882  +        }
          883  +      }
          884  +    }
          885  +    idxFinalize(&rc, pInfo);
          886  +
          887  +    if( rc==SQLITE_OK && bMatch ){
          888  +      sqlite3_finalize(pIdxList);
          889  +      return 1;
          890  +    }
          891  +  }
          892  +  idxFinalize(&rc, pIdxList);
          893  +
          894  +  *pRc = rc;
          895  +  return 0;
          896  +}
          897  +
          898  +static int idxCreateFromCons(
          899  +  sqlite3expert *p,
          900  +  IdxScan *pScan,
          901  +  IdxConstraint *pEq, 
          902  +  IdxConstraint *pTail
          903  +){
          904  +  sqlite3 *dbm = p->dbm;
          905  +  int rc = SQLITE_OK;
          906  +  if( (pEq || pTail) && 0==idxFindCompatible(&rc, dbm, pScan, pEq, pTail) ){
          907  +    IdxTable *pTab = pScan->pTab;
          908  +    char *zCols = 0;
          909  +    char *zIdx = 0;
          910  +    IdxConstraint *pCons;
          911  +    int h = 0;
          912  +    const char *zFmt;
          913  +
          914  +    for(pCons=pEq; pCons; pCons=pCons->pLink){
          915  +      zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
          916  +    }
          917  +    for(pCons=pTail; pCons; pCons=pCons->pLink){
          918  +      zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
          919  +    }
          920  +
          921  +    if( rc==SQLITE_OK ){
          922  +      /* Hash the list of columns to come up with a name for the index */
          923  +      const char *zTable = pScan->pTab->zName;
          924  +      char *zName;                /* Index name */
          925  +      int i;
          926  +      for(i=0; zCols[i]; i++){
          927  +        h += ((h<<3) + zCols[i]);
          928  +      }
          929  +      zName = sqlite3_mprintf("%s_idx_%08x", zTable, h);
          930  +      if( zName==0 ){ 
          931  +        rc = SQLITE_NOMEM;
          932  +      }else{
          933  +        if( idxIdentifierRequiresQuotes(zTable) ){
          934  +          zFmt = "CREATE INDEX '%q' ON %Q(%s)";
          935  +        }else{
          936  +          zFmt = "CREATE INDEX %s ON %s(%s)";
          937  +        }
          938  +        zIdx = sqlite3_mprintf(zFmt, zName, zTable, zCols);
          939  +        if( !zIdx ){
          940  +          rc = SQLITE_NOMEM;
          941  +        }else{
          942  +          rc = sqlite3_exec(dbm, zIdx, 0, 0, p->pzErrmsg);
          943  +          idxHashAdd(&rc, &p->hIdx, zName, zIdx);
          944  +        }
          945  +        sqlite3_free(zName);
          946  +        sqlite3_free(zIdx);
          947  +      }
          948  +    }
          949  +
          950  +    sqlite3_free(zCols);
          951  +  }
          952  +  return rc;
          953  +}
          954  +
          955  +/*
          956  +** Return true if list pList (linked by IdxConstraint.pLink) contains
          957  +** a constraint compatible with *p. Otherwise return false.
          958  +*/
          959  +static int idxFindConstraint(IdxConstraint *pList, IdxConstraint *p){
          960  +  IdxConstraint *pCmp;
          961  +  for(pCmp=pList; pCmp; pCmp=pCmp->pLink){
          962  +    if( p->iCol==pCmp->iCol ) return 1;
          963  +  }
          964  +  return 0;
          965  +}
          966  +
          967  +static int idxCreateFromWhere(
          968  +  sqlite3expert *p, 
          969  +  IdxScan *pScan,                 /* Create indexes for this scan */
          970  +  IdxConstraint *pTail            /* range/ORDER BY constraints for inclusion */
          971  +){
          972  +  IdxConstraint *p1 = 0;
          973  +  IdxConstraint *pCon;
          974  +  int rc;
          975  +
          976  +  /* Gather up all the == constraints. */
          977  +  for(pCon=pScan->pEq; pCon; pCon=pCon->pNext){
          978  +    if( !idxFindConstraint(p1, pCon) && !idxFindConstraint(pTail, pCon) ){
          979  +      pCon->pLink = p1;
          980  +      p1 = pCon;
          981  +    }
          982  +  }
          983  +
          984  +  /* Create an index using the == constraints collected above. And the
          985  +  ** range constraint/ORDER BY terms passed in by the caller, if any. */
          986  +  rc = idxCreateFromCons(p, pScan, p1, pTail);
          987  +
          988  +  /* If no range/ORDER BY passed by the caller, create a version of the
          989  +  ** index for each range constraint.  */
          990  +  if( pTail==0 ){
          991  +    for(pCon=pScan->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){
          992  +      assert( pCon->pLink==0 );
          993  +      if( !idxFindConstraint(p1, pCon) && !idxFindConstraint(pTail, pCon) ){
          994  +        rc = idxCreateFromCons(p, pScan, p1, pCon);
          995  +      }
          996  +    }
          997  +  }
          998  +
          999  +  return rc;
         1000  +}
         1001  +
         1002  +/*
         1003  +** Create candidate indexes in database [dbm] based on the data in 
         1004  +** linked-list pScan.
         1005  +*/
         1006  +static int idxCreateCandidates(sqlite3expert *p, char **pzErr){
         1007  +  int rc = SQLITE_OK;
         1008  +  IdxScan *pIter;
         1009  +
         1010  +  for(pIter=p->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
         1011  +    rc = idxCreateFromWhere(p, pIter, 0);
         1012  +    if( rc==SQLITE_OK && pIter->pOrder ){
         1013  +      rc = idxCreateFromWhere(p, pIter, pIter->pOrder);
         1014  +    }
         1015  +  }
         1016  +
         1017  +  return rc;
         1018  +}
         1019  +
         1020  +/*
         1021  +** Free all elements of the linked list starting at pConstraint.
         1022  +*/
         1023  +static void idxConstraintFree(IdxConstraint *pConstraint){
         1024  +  IdxConstraint *pNext;
         1025  +  IdxConstraint *p;
         1026  +
         1027  +  for(p=pConstraint; p; p=pNext){
         1028  +    pNext = p->pNext;
         1029  +    sqlite3_free(p);
         1030  +  }
         1031  +}
         1032  +
         1033  +/*
         1034  +** Free all elements of the linked list starting from pScan up until pLast
         1035  +** (pLast is not freed).
         1036  +*/
         1037  +static void idxScanFree(IdxScan *pScan, IdxScan *pLast){
         1038  +  IdxScan *p;
         1039  +  IdxScan *pNext;
         1040  +  for(p=pScan; p!=pLast; p=pNext){
         1041  +    pNext = p->pNextScan;
         1042  +    idxConstraintFree(p->pOrder);
         1043  +    idxConstraintFree(p->pEq);
         1044  +    idxConstraintFree(p->pRange);
         1045  +    sqlite3_free(p);
         1046  +  }
         1047  +}
         1048  +
         1049  +/*
         1050  +** Free all elements of the linked list starting from pStatement up 
         1051  +** until pLast (pLast is not freed).
         1052  +*/
         1053  +static void idxStatementFree(IdxStatement *pStatement, IdxStatement *pLast){
         1054  +  IdxStatement *p;
         1055  +  IdxStatement *pNext;
         1056  +  for(p=pStatement; p!=pLast; p=pNext){
         1057  +    pNext = p->pNext;
         1058  +    sqlite3_free(p->zEQP);
         1059  +    sqlite3_free(p->zIdx);
         1060  +    sqlite3_free(p);
         1061  +  }
         1062  +}
         1063  +
         1064  +/*
         1065  +** Free the linked list of IdxTable objects starting at pTab.
         1066  +*/
         1067  +static void idxTableFree(IdxTable *pTab){
         1068  +  IdxTable *pIter;
         1069  +  IdxTable *pNext;
         1070  +  for(pIter=pTab; pIter; pIter=pNext){
         1071  +    pNext = pIter->pNext;
         1072  +    sqlite3_free(pIter);
         1073  +  }
         1074  +}
         1075  +
         1076  +/*
         1077  +** Free the linked list of IdxWrite objects starting at pTab.
         1078  +*/
         1079  +static void idxWriteFree(IdxWrite *pTab){
         1080  +  IdxWrite *pIter;
         1081  +  IdxWrite *pNext;
         1082  +  for(pIter=pTab; pIter; pIter=pNext){
         1083  +    pNext = pIter->pNext;
         1084  +    sqlite3_free(pIter);
         1085  +  }
         1086  +}
         1087  +
         1088  +
         1089  +
         1090  +/*
         1091  +** This function is called after candidate indexes have been created. It
         1092  +** runs all the queries to see which indexes they prefer, and populates
         1093  +** IdxStatement.zIdx and IdxStatement.zEQP with the results.
         1094  +*/
         1095  +int idxFindIndexes(
         1096  +  sqlite3expert *p,
         1097  +  char **pzErr                         /* OUT: Error message (sqlite3_malloc) */
         1098  +){
         1099  +  IdxStatement *pStmt;
         1100  +  sqlite3 *dbm = p->dbm;
         1101  +  int rc = SQLITE_OK;
         1102  +
         1103  +  IdxHash hIdx;
         1104  +  idxHashInit(&hIdx);
         1105  +
         1106  +  for(pStmt=p->pStatement; rc==SQLITE_OK && pStmt; pStmt=pStmt->pNext){
         1107  +    IdxHashEntry *pEntry;
         1108  +    sqlite3_stmt *pExplain = 0;
         1109  +    idxHashClear(&hIdx);
         1110  +    rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,
         1111  +        "EXPLAIN QUERY PLAN %s", pStmt->zSql
         1112  +    );
         1113  +    while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
         1114  +      int iSelectid = sqlite3_column_int(pExplain, 0);
         1115  +      int iOrder = sqlite3_column_int(pExplain, 1);
         1116  +      int iFrom = sqlite3_column_int(pExplain, 2);
         1117  +      const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
         1118  +      int nDetail = STRLEN(zDetail);
         1119  +      int i;
         1120  +
         1121  +      for(i=0; i<nDetail; i++){
         1122  +        const char *zIdx = 0;
         1123  +        if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){
         1124  +          zIdx = &zDetail[i+13];
         1125  +        }else if( memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 ){
         1126  +          zIdx = &zDetail[i+22];
         1127  +        }
         1128  +        if( zIdx ){
         1129  +          const char *zSql;
         1130  +          int nIdx = 0;
         1131  +          while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){
         1132  +            nIdx++;
         1133  +          }
         1134  +          zSql = idxHashSearch(&p->hIdx, zIdx, nIdx);
         1135  +          if( zSql ){
         1136  +            idxHashAdd(&rc, &hIdx, zSql, 0);
         1137  +            if( rc ) goto find_indexes_out;
         1138  +          }
         1139  +          break;
         1140  +        }
         1141  +      }
         1142  +
         1143  +      pStmt->zEQP = idxAppendText(&rc, pStmt->zEQP, "%d|%d|%d|%s\n", 
         1144  +          iSelectid, iOrder, iFrom, zDetail
         1145  +      );
         1146  +    }
         1147  +
         1148  +    for(pEntry=hIdx.pFirst; pEntry; pEntry=pEntry->pNext){
         1149  +      pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s;\n", pEntry->zKey);
         1150  +    }
         1151  +
         1152  +    idxFinalize(&rc, pExplain);
         1153  +  }
         1154  +
         1155  + find_indexes_out:
         1156  +  idxHashClear(&hIdx);
         1157  +  return rc;
         1158  +}
         1159  +
         1160  +static int idxAuthCallback(
         1161  +  void *pCtx,
         1162  +  int eOp,
         1163  +  const char *z3,
         1164  +  const char *z4,
         1165  +  const char *zDb,
         1166  +  const char *zTrigger
         1167  +){
         1168  +  int rc = SQLITE_OK;
         1169  +  if( eOp==SQLITE_INSERT || eOp==SQLITE_UPDATE || eOp==SQLITE_DELETE ){
         1170  +    if( sqlite3_stricmp(zDb, "main")==0 ){
         1171  +      sqlite3expert *p = (sqlite3expert*)pCtx;
         1172  +      IdxTable *pTab;
         1173  +      for(pTab=p->pTable; pTab; pTab=pTab->pNext){
         1174  +        if( 0==sqlite3_stricmp(z3, pTab->zName) ) break;
         1175  +      }
         1176  +      if( pTab ){
         1177  +        IdxWrite *pWrite;
         1178  +        for(pWrite=p->pWrite; pWrite; pWrite=pWrite->pNext){
         1179  +          if( pWrite->pTab==pTab && pWrite->eOp==eOp ) break;
         1180  +        }
         1181  +        if( pWrite==0 ){
         1182  +          pWrite = idxMalloc(&rc, sizeof(IdxWrite));
         1183  +          if( rc==SQLITE_OK ){
         1184  +            pWrite->pTab = pTab;
         1185  +            pWrite->eOp = eOp;
         1186  +            pWrite->pNext = p->pWrite;
         1187  +            p->pWrite = pWrite;
         1188  +          }
         1189  +        }
         1190  +      }
         1191  +    }
         1192  +  }
         1193  +  return rc;
         1194  +}
         1195  +
         1196  +static int idxProcessOneTrigger(
         1197  +  sqlite3expert *p, 
         1198  +  IdxWrite *pWrite, 
         1199  +  char **pzErr
         1200  +){
         1201  +  static const char *zInt = UNIQUE_TABLE_NAME;
         1202  +  static const char *zDrop = "DROP TABLE " UNIQUE_TABLE_NAME;
         1203  +  IdxTable *pTab = pWrite->pTab;
         1204  +  const char *zTab = pTab->zName;
         1205  +  const char *zSql = 
         1206  +    "SELECT 'CREATE TEMP' || substr(sql, 7) FROM sqlite_master "
         1207  +    "WHERE tbl_name = %Q AND type IN ('table', 'trigger') "
         1208  +    "ORDER BY type;";
         1209  +  sqlite3_stmt *pSelect = 0;
         1210  +  int rc = SQLITE_OK;
         1211  +  char *zWrite = 0;
         1212  +
         1213  +  /* Create the table and its triggers in the temp schema */
         1214  +  rc = idxPrintfPrepareStmt(p->db, &pSelect, pzErr, zSql, zTab, zTab);
         1215  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSelect) ){
         1216  +    const char *zCreate = (const char*)sqlite3_column_text(pSelect, 0);
         1217  +    rc = sqlite3_exec(p->dbv, zCreate, 0, 0, pzErr);
         1218  +  }
         1219  +  idxFinalize(&rc, pSelect);
         1220  +
         1221  +  /* Rename the table in the temp schema to zInt */
         1222  +  if( rc==SQLITE_OK ){
         1223  +    char *z = sqlite3_mprintf("ALTER TABLE temp.%Q RENAME TO %Q", zTab, zInt);
         1224  +    if( z==0 ){
         1225  +      rc = SQLITE_NOMEM;
         1226  +    }else{
         1227  +      rc = sqlite3_exec(p->dbv, z, 0, 0, pzErr);
         1228  +      sqlite3_free(z);
         1229  +    }
         1230  +  }
         1231  +
         1232  +  switch( pWrite->eOp ){
         1233  +    case SQLITE_INSERT: {
         1234  +      int i;
         1235  +      zWrite = idxAppendText(&rc, zWrite, "INSERT INTO %Q VALUES(", zInt);
         1236  +      for(i=0; i<pTab->nCol; i++){
         1237  +        zWrite = idxAppendText(&rc, zWrite, "%s?", i==0 ? "" : ", ");
         1238  +      }
         1239  +      zWrite = idxAppendText(&rc, zWrite, ")");
         1240  +      break;
         1241  +    }
         1242  +    case SQLITE_UPDATE: {
         1243  +      int i;
         1244  +      zWrite = idxAppendText(&rc, zWrite, "UPDATE %Q SET ", zInt);
         1245  +      for(i=0; i<pTab->nCol; i++){
         1246  +        zWrite = idxAppendText(&rc, zWrite, "%s%Q=?", i==0 ? "" : ", ", 
         1247  +            pTab->aCol[i].zName
         1248  +        );
         1249  +      }
         1250  +      break;
         1251  +    }
         1252  +    default: {
         1253  +      assert( pWrite->eOp==SQLITE_DELETE );
         1254  +      if( rc==SQLITE_OK ){
         1255  +        zWrite = sqlite3_mprintf("DELETE FROM %Q", zInt);
         1256  +        if( zWrite==0 ) rc = SQLITE_NOMEM;
         1257  +      }
         1258  +    }
         1259  +  }
         1260  +
         1261  +  if( rc==SQLITE_OK ){
         1262  +    sqlite3_stmt *pX = 0;
         1263  +    rc = sqlite3_prepare_v2(p->dbv, zWrite, -1, &pX, 0);
         1264  +    idxFinalize(&rc, pX);
         1265  +    if( rc!=SQLITE_OK ){
         1266  +      idxDatabaseError(p->dbv, pzErr);
         1267  +    }
         1268  +  }
         1269  +  sqlite3_free(zWrite);
         1270  +
         1271  +  if( rc==SQLITE_OK ){
         1272  +    rc = sqlite3_exec(p->dbv, zDrop, 0, 0, pzErr);
         1273  +  }
         1274  +
         1275  +  return rc;
         1276  +}
         1277  +
         1278  +static int idxProcessTriggers(sqlite3expert *p, char **pzErr){
         1279  +  int rc = SQLITE_OK;
         1280  +  IdxWrite *pEnd = 0;
         1281  +  IdxWrite *pFirst = p->pWrite;
         1282  +
         1283  +  while( rc==SQLITE_OK && pFirst!=pEnd ){
         1284  +    IdxWrite *pIter;
         1285  +    for(pIter=pFirst; rc==SQLITE_OK && pIter!=pEnd; pIter=pIter->pNext){
         1286  +      rc = idxProcessOneTrigger(p, pIter, pzErr);
         1287  +    }
         1288  +    pEnd = pFirst;
         1289  +    pFirst = p->pWrite;
         1290  +  }
         1291  +
         1292  +  return rc;
         1293  +}
         1294  +
         1295  +
         1296  +static int idxCreateVtabSchema(sqlite3expert *p, char **pzErrmsg){
         1297  +  int rc = idxRegisterVtab(p);
         1298  +  sqlite3_stmt *pSchema = 0;
         1299  +
         1300  +  /* For each table in the main db schema:
         1301  +  **
         1302  +  **   1) Add an entry to the p->pTable list, and
         1303  +  **   2) Create the equivalent virtual table in dbv.
         1304  +  */
         1305  +  rc = idxPrepareStmt(p->db, &pSchema, pzErrmsg,
         1306  +      "SELECT type, name, sql, 1 FROM sqlite_master "
         1307  +      "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%%' "
         1308  +      " UNION ALL "
         1309  +      "SELECT type, name, sql, 2 FROM sqlite_master "
         1310  +      "WHERE type = 'trigger'"
         1311  +      "  AND tbl_name IN(SELECT name FROM sqlite_master WHERE type = 'view') "
         1312  +      "ORDER BY 4, 1"
         1313  +  );
         1314  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSchema) ){
         1315  +    const char *zType = (const char*)sqlite3_column_text(pSchema, 0);
         1316  +    const char *zName = (const char*)sqlite3_column_text(pSchema, 1);
         1317  +    const char *zSql = (const char*)sqlite3_column_text(pSchema, 2);
         1318  +
         1319  +    if( zType[0]=='v' || zType[1]=='r' ){
         1320  +      rc = sqlite3_exec(p->dbv, zSql, 0, 0, pzErrmsg);
         1321  +    }else{
         1322  +      IdxTable *pTab;
         1323  +      rc = idxGetTableInfo(p->db, zName, &pTab, pzErrmsg);
         1324  +      if( rc==SQLITE_OK ){
         1325  +        int i;
         1326  +        char *zInner = 0;
         1327  +        char *zOuter = 0;
         1328  +        pTab->pNext = p->pTable;
         1329  +        p->pTable = pTab;
         1330  +
         1331  +        /* The statement the vtab will pass to sqlite3_declare_vtab() */
         1332  +        zInner = idxAppendText(&rc, 0, "CREATE TABLE x(");
         1333  +        for(i=0; i<pTab->nCol; i++){
         1334  +          zInner = idxAppendText(&rc, zInner, "%s%Q COLLATE %s", 
         1335  +              (i==0 ? "" : ", "), pTab->aCol[i].zName, pTab->aCol[i].zColl
         1336  +          );
         1337  +        }
         1338  +        zInner = idxAppendText(&rc, zInner, ")");
         1339  +
         1340  +        /* The CVT statement to create the vtab */
         1341  +        zOuter = idxAppendText(&rc, 0, 
         1342  +            "CREATE VIRTUAL TABLE %Q USING expert(%Q)", zName, zInner
         1343  +        );
         1344  +        if( rc==SQLITE_OK ){
         1345  +          rc = sqlite3_exec(p->dbv, zOuter, 0, 0, pzErrmsg);
         1346  +        }
         1347  +        sqlite3_free(zInner);
         1348  +        sqlite3_free(zOuter);
         1349  +      }
         1350  +    }
         1351  +  }
         1352  +  idxFinalize(&rc, pSchema);
         1353  +  return rc;
         1354  +}
         1355  +
         1356  +struct IdxSampleCtx {
         1357  +  int iTarget;
         1358  +  double target;                  /* Target nRet/nRow value */
         1359  +  double nRow;                    /* Number of rows seen */
         1360  +  double nRet;                    /* Number of rows returned */
         1361  +};
         1362  +
         1363  +static void idxSampleFunc(
         1364  +  sqlite3_context *pCtx,
         1365  +  int argc,
         1366  +  sqlite3_value **argv
         1367  +){
         1368  +  struct IdxSampleCtx *p = (struct IdxSampleCtx*)sqlite3_user_data(pCtx);
         1369  +  int bRet;
         1370  +
         1371  +  assert( argc==0 );
         1372  +  if( p->nRow==0.0 ){
         1373  +    bRet = 1;
         1374  +  }else{
         1375  +    bRet = (p->nRet / p->nRow) <= p->target;
         1376  +    if( bRet==0 ){
         1377  +      unsigned short rnd;
         1378  +      sqlite3_randomness(2, (void*)&rnd);
         1379  +      bRet = ((int)rnd % 100) <= p->iTarget;
         1380  +    }
         1381  +  }
         1382  +
         1383  +  sqlite3_result_int(pCtx, bRet);
         1384  +  p->nRow += 1.0;
         1385  +  p->nRet += (double)bRet;
         1386  +}
         1387  +
         1388  +struct IdxRemCtx {
         1389  +  int nSlot;
         1390  +  struct IdxRemSlot {
         1391  +    int eType;                    /* SQLITE_NULL, INTEGER, REAL, TEXT, BLOB */
         1392  +    i64 iVal;                     /* SQLITE_INTEGER value */
         1393  +    double rVal;                  /* SQLITE_FLOAT value */
         1394  +    int nByte;                    /* Bytes of space allocated at z */
         1395  +    int n;                        /* Size of buffer z */
         1396  +    char *z;                      /* SQLITE_TEXT/BLOB value */
         1397  +  } aSlot[1];
         1398  +};
         1399  +
         1400  +/*
         1401  +** Implementation of scalar function rem().
         1402  +*/
         1403  +static void idxRemFunc(
         1404  +  sqlite3_context *pCtx,
         1405  +  int argc,
         1406  +  sqlite3_value **argv
         1407  +){
         1408  +  struct IdxRemCtx *p = (struct IdxRemCtx*)sqlite3_user_data(pCtx);
         1409  +  struct IdxRemSlot *pSlot;
         1410  +  int iSlot;
         1411  +  assert( argc==2 );
         1412  +
         1413  +  iSlot = sqlite3_value_int(argv[0]);
         1414  +  assert( iSlot<=p->nSlot );
         1415  +  pSlot = &p->aSlot[iSlot];
         1416  +
         1417  +  switch( pSlot->eType ){
         1418  +    case SQLITE_NULL:
         1419  +      /* no-op */
         1420  +      break;
         1421  +
         1422  +    case SQLITE_INTEGER:
         1423  +      sqlite3_result_int64(pCtx, pSlot->iVal);
         1424  +      break;
         1425  +
         1426  +    case SQLITE_FLOAT:
         1427  +      sqlite3_result_double(pCtx, pSlot->rVal);
         1428  +      break;
         1429  +
         1430  +    case SQLITE_BLOB:
         1431  +      sqlite3_result_blob(pCtx, pSlot->z, pSlot->n, SQLITE_TRANSIENT);
         1432  +      break;
         1433  +
         1434  +    case SQLITE_TEXT:
         1435  +      sqlite3_result_text(pCtx, pSlot->z, pSlot->n, SQLITE_TRANSIENT);
         1436  +      break;
         1437  +  }
         1438  +
         1439  +  pSlot->eType = sqlite3_value_type(argv[1]);
         1440  +  switch( pSlot->eType ){
         1441  +    case SQLITE_NULL:
         1442  +      /* no-op */
         1443  +      break;
         1444  +
         1445  +    case SQLITE_INTEGER:
         1446  +      pSlot->iVal = sqlite3_value_int64(argv[1]);
         1447  +      break;
         1448  +
         1449  +    case SQLITE_FLOAT:
         1450  +      pSlot->rVal = sqlite3_value_double(argv[1]);
         1451  +      break;
         1452  +
         1453  +    case SQLITE_BLOB:
         1454  +    case SQLITE_TEXT: {
         1455  +      int nByte = sqlite3_value_bytes(argv[1]);
         1456  +      if( nByte>pSlot->nByte ){
         1457  +        char *zNew = (char*)sqlite3_realloc(pSlot->z, nByte*2);
         1458  +        if( zNew==0 ){
         1459  +          sqlite3_result_error_nomem(pCtx);
         1460  +          return;
         1461  +        }
         1462  +        pSlot->nByte = nByte*2;
         1463  +        pSlot->z = zNew;
         1464  +      }
         1465  +      pSlot->n = nByte;
         1466  +      if( pSlot->eType==SQLITE_BLOB ){
         1467  +        memcpy(pSlot->z, sqlite3_value_blob(argv[1]), nByte);
         1468  +      }else{
         1469  +        memcpy(pSlot->z, sqlite3_value_text(argv[1]), nByte);
         1470  +      }
         1471  +      break;
         1472  +    }
         1473  +  }
         1474  +}
         1475  +
         1476  +static int idxLargestIndex(sqlite3 *db, int *pnMax, char **pzErr){
         1477  +  int rc = SQLITE_OK;
         1478  +  const char *zMax = 
         1479  +    "SELECT max(i.seqno) FROM "
         1480  +    "  sqlite_master AS s, "
         1481  +    "  pragma_index_list(s.name) AS l, "
         1482  +    "  pragma_index_info(l.name) AS i "
         1483  +    "WHERE s.type = 'table'";
         1484  +  sqlite3_stmt *pMax = 0;
         1485  +
         1486  +  *pnMax = 0;
         1487  +  rc = idxPrepareStmt(db, &pMax, pzErr, zMax);
         1488  +  if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pMax) ){
         1489  +    *pnMax = sqlite3_column_int(pMax, 0) + 1;
         1490  +  }
         1491  +  idxFinalize(&rc, pMax);
         1492  +
         1493  +  return rc;
         1494  +}
         1495  +
         1496  +static int idxPopulateOneStat1(
         1497  +  sqlite3expert *p,
         1498  +  sqlite3_stmt *pIndexXInfo,
         1499  +  sqlite3_stmt *pWriteStat,
         1500  +  const char *zTab,
         1501  +  const char *zIdx,
         1502  +  char **pzErr
         1503  +){
         1504  +  char *zCols = 0;
         1505  +  char *zOrder = 0;
         1506  +  char *zQuery = 0;
         1507  +  int nCol = 0;
         1508  +  int i;
         1509  +  sqlite3_stmt *pQuery = 0;
         1510  +  int *aStat = 0;
         1511  +  int rc = SQLITE_OK;
         1512  +
         1513  +  assert( p->iSample>0 );
         1514  +
         1515  +  /* Formulate the query text */
         1516  +  sqlite3_bind_text(pIndexXInfo, 1, zIdx, -1, SQLITE_STATIC);
         1517  +  while( SQLITE_OK==rc && SQLITE_ROW==sqlite3_step(pIndexXInfo) ){
         1518  +    const char *zComma = zCols==0 ? "" : ", ";
         1519  +    const char *zName = (const char*)sqlite3_column_text(pIndexXInfo, 0);
         1520  +    const char *zColl = (const char*)sqlite3_column_text(pIndexXInfo, 1);
         1521  +    zCols = idxAppendText(&rc, zCols, 
         1522  +        "%sx.%Q IS rem(%d, x.%Q) COLLATE %s", zComma, zName, nCol, zName, zColl
         1523  +    );
         1524  +    zOrder = idxAppendText(&rc, zOrder, "%s%d", zComma, ++nCol);
         1525  +  }
         1526  +  if( rc==SQLITE_OK ){
         1527  +    if( p->iSample==100 ){
         1528  +      zQuery = sqlite3_mprintf(
         1529  +          "SELECT %s FROM %Q x ORDER BY %s", zCols, zTab, zOrder
         1530  +      );
         1531  +    }else{
         1532  +      zQuery = sqlite3_mprintf(
         1533  +          "SELECT %s FROM temp."UNIQUE_TABLE_NAME" x ORDER BY %s", zCols, zOrder
         1534  +      );
         1535  +    }
         1536  +  }
         1537  +  sqlite3_free(zCols);
         1538  +  sqlite3_free(zOrder);
         1539  +
         1540  +  /* Formulate the query text */
         1541  +  if( rc==SQLITE_OK ){
         1542  +    sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
         1543  +    rc = idxPrepareStmt(dbrem, &pQuery, pzErr, zQuery);
         1544  +  }
         1545  +  sqlite3_free(zQuery);
         1546  +
         1547  +  if( rc==SQLITE_OK ){
         1548  +    aStat = (int*)idxMalloc(&rc, sizeof(int)*(nCol+1));
         1549  +  }
         1550  +  if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){
         1551  +    IdxHashEntry *pEntry;
         1552  +    char *zStat = 0;
         1553  +    for(i=0; i<=nCol; i++) aStat[i] = 1;
         1554  +    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){
         1555  +      aStat[0]++;
         1556  +      for(i=0; i<nCol; i++){
         1557  +        if( sqlite3_column_int(pQuery, i)==0 ) break;
         1558  +      }
         1559  +      for(/*no-op*/; i<nCol; i++){
         1560  +        aStat[i+1]++;
         1561  +      }
         1562  +    }
         1563  +
         1564  +    if( rc==SQLITE_OK ){
         1565  +      int s0 = aStat[0];
         1566  +      zStat = sqlite3_mprintf("%d", s0);
         1567  +      if( zStat==0 ) rc = SQLITE_NOMEM;
         1568  +      for(i=1; rc==SQLITE_OK && i<=nCol; i++){
         1569  +        zStat = idxAppendText(&rc, zStat, " %d", (s0+aStat[i]/2) / aStat[i]);
         1570  +      }
         1571  +    }
         1572  +
         1573  +    if( rc==SQLITE_OK ){
         1574  +      sqlite3_bind_text(pWriteStat, 1, zTab, -1, SQLITE_STATIC);
         1575  +      sqlite3_bind_text(pWriteStat, 2, zIdx, -1, SQLITE_STATIC);
         1576  +      sqlite3_bind_text(pWriteStat, 3, zStat, -1, SQLITE_STATIC);
         1577  +      sqlite3_step(pWriteStat);
         1578  +      rc = sqlite3_reset(pWriteStat);
         1579  +    }
         1580  +
         1581  +    pEntry = idxHashFind(&p->hIdx, zIdx, STRLEN(zIdx));
         1582  +    if( pEntry ){
         1583  +      assert( pEntry->zVal2==0 );
         1584  +      pEntry->zVal2 = zStat;
         1585  +    }else{
         1586  +      sqlite3_free(zStat);
         1587  +    }
         1588  +  }
         1589  +  sqlite3_free(aStat);
         1590  +  idxFinalize(&rc, pQuery);
         1591  +
         1592  +  return rc;
         1593  +}
         1594  +
         1595  +static int idxBuildSampleTable(sqlite3expert *p, const char *zTab){
         1596  +  int rc;
         1597  +  char *zSql;
         1598  +
         1599  +  rc = sqlite3_exec(p->dbv,"DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
         1600  +  if( rc!=SQLITE_OK ) return rc;
         1601  +
         1602  +  zSql = sqlite3_mprintf(
         1603  +      "CREATE TABLE temp." UNIQUE_TABLE_NAME " AS SELECT * FROM %Q", zTab
         1604  +  );
         1605  +  if( zSql==0 ) return SQLITE_NOMEM;
         1606  +  rc = sqlite3_exec(p->dbv, zSql, 0, 0, 0);
         1607  +  sqlite3_free(zSql);
         1608  +
         1609  +  return rc;
         1610  +}
         1611  +
         1612  +/*
         1613  +** This function is called as part of sqlite3_expert_analyze(). Candidate
         1614  +** indexes have already been created in database sqlite3expert.dbm, this
         1615  +** function populates sqlite_stat1 table in the same database.
         1616  +**
         1617  +** The stat1 data is generated by querying the 
         1618  +*/
         1619  +static int idxPopulateStat1(sqlite3expert *p, char **pzErr){
         1620  +  int rc = SQLITE_OK;
         1621  +  int nMax =0;
         1622  +  struct IdxRemCtx *pCtx = 0;
         1623  +  struct IdxSampleCtx samplectx; 
         1624  +  int i;
         1625  +  i64 iPrev = -100000;
         1626  +  sqlite3_stmt *pAllIndex = 0;
         1627  +  sqlite3_stmt *pIndexXInfo = 0;
         1628  +  sqlite3_stmt *pWrite = 0;
         1629  +
         1630  +  const char *zAllIndex =
         1631  +    "SELECT s.rowid, s.name, l.name FROM "
         1632  +    "  sqlite_master AS s, "
         1633  +    "  pragma_index_list(s.name) AS l "
         1634  +    "WHERE s.type = 'table'";
         1635  +  const char *zIndexXInfo = 
         1636  +    "SELECT name, coll FROM pragma_index_xinfo(?) WHERE key";
         1637  +  const char *zWrite = "INSERT INTO sqlite_stat1 VALUES(?, ?, ?)";
         1638  +
         1639  +  /* If iSample==0, no sqlite_stat1 data is required. */
         1640  +  if( p->iSample==0 ) return SQLITE_OK;
         1641  +
         1642  +  rc = idxLargestIndex(p->dbm, &nMax, pzErr);
         1643  +  if( nMax<=0 || rc!=SQLITE_OK ) return rc;
         1644  +
         1645  +  rc = sqlite3_exec(p->dbm, "ANALYZE; PRAGMA writable_schema=1", 0, 0, 0);
         1646  +
         1647  +  if( rc==SQLITE_OK ){
         1648  +    int nByte = sizeof(struct IdxRemCtx) + (sizeof(struct IdxRemSlot) * nMax);
         1649  +    pCtx = (struct IdxRemCtx*)idxMalloc(&rc, nByte);
         1650  +  }
         1651  +
         1652  +  if( rc==SQLITE_OK ){
         1653  +    sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv);
         1654  +    rc = sqlite3_create_function(
         1655  +        dbrem, "rem", 2, SQLITE_UTF8, (void*)pCtx, idxRemFunc, 0, 0
         1656  +    );
         1657  +  }
         1658  +  if( rc==SQLITE_OK ){
         1659  +    rc = sqlite3_create_function(
         1660  +        p->db, "sample", 0, SQLITE_UTF8, (void*)&samplectx, idxSampleFunc, 0, 0
         1661  +    );
         1662  +  }
         1663  +
         1664  +  if( rc==SQLITE_OK ){
         1665  +    pCtx->nSlot = nMax+1;
         1666  +    rc = idxPrepareStmt(p->dbm, &pAllIndex, pzErr, zAllIndex);
         1667  +  }
         1668  +  if( rc==SQLITE_OK ){
         1669  +    rc = idxPrepareStmt(p->dbm, &pIndexXInfo, pzErr, zIndexXInfo);
         1670  +  }
         1671  +  if( rc==SQLITE_OK ){
         1672  +    rc = idxPrepareStmt(p->dbm, &pWrite, pzErr, zWrite);
         1673  +  }
         1674  +
         1675  +  while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pAllIndex) ){
         1676  +    i64 iRowid = sqlite3_column_int64(pAllIndex, 0);
         1677  +    const char *zTab = (const char*)sqlite3_column_text(pAllIndex, 1);
         1678  +    const char *zIdx = (const char*)sqlite3_column_text(pAllIndex, 2);
         1679  +    if( p->iSample<100 && iPrev!=iRowid ){
         1680  +      samplectx.target = (double)p->iSample / 100.0;
         1681  +      samplectx.iTarget = p->iSample;
         1682  +      samplectx.nRow = 0.0;
         1683  +      samplectx.nRet = 0.0;
         1684  +      rc = idxBuildSampleTable(p, zTab);
         1685  +      if( rc!=SQLITE_OK ) break;
         1686  +    }
         1687  +    rc = idxPopulateOneStat1(p, pIndexXInfo, pWrite, zTab, zIdx, pzErr);
         1688  +    iPrev = iRowid;
         1689  +  }
         1690  +  if( rc==SQLITE_OK && p->iSample<100 ){
         1691  +    rc = sqlite3_exec(p->dbv, 
         1692  +        "DROP TABLE IF EXISTS temp." UNIQUE_TABLE_NAME, 0,0,0
         1693  +    );
         1694  +  }
         1695  +
         1696  +  idxFinalize(&rc, pAllIndex);
         1697  +  idxFinalize(&rc, pIndexXInfo);
         1698  +  idxFinalize(&rc, pWrite);
         1699  +
         1700  +  for(i=0; i<pCtx->nSlot; i++){
         1701  +    sqlite3_free(pCtx->aSlot[i].z);
         1702  +  }
         1703  +  sqlite3_free(pCtx);
         1704  +
         1705  +  if( rc==SQLITE_OK ){
         1706  +    rc = sqlite3_exec(p->dbm, "ANALYZE sqlite_master", 0, 0, 0);
         1707  +  }
         1708  +
         1709  +  sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0);
         1710  +  return rc;
         1711  +}
         1712  +
         1713  +/*
         1714  +** Allocate a new sqlite3expert object.
         1715  +*/
         1716  +sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErrmsg){
         1717  +  int rc = SQLITE_OK;
         1718  +  sqlite3expert *pNew;
         1719  +
         1720  +  pNew = (sqlite3expert*)idxMalloc(&rc, sizeof(sqlite3expert));
         1721  +
         1722  +  /* Open two in-memory databases to work with. The "vtab database" (dbv)
         1723  +  ** will contain a virtual table corresponding to each real table in
         1724  +  ** the user database schema, and a copy of each view. It is used to
         1725  +  ** collect information regarding the WHERE, ORDER BY and other clauses
         1726  +  ** of the user's query.
         1727  +  */
         1728  +  if( rc==SQLITE_OK ){
         1729  +    pNew->db = db;
         1730  +    pNew->iSample = 100;
         1731  +    rc = sqlite3_open(":memory:", &pNew->dbv);
         1732  +  }
         1733  +  if( rc==SQLITE_OK ){
         1734  +    rc = sqlite3_open(":memory:", &pNew->dbm);
         1735  +    if( rc==SQLITE_OK ){
         1736  +      sqlite3_db_config(pNew->dbm, SQLITE_DBCONFIG_TRIGGER_EQP, 1, (int*)0);
         1737  +    }
         1738  +  }
         1739  +  
         1740  +
         1741  +  /* Copy the entire schema of database [db] into [dbm]. */
         1742  +  if( rc==SQLITE_OK ){
         1743  +    sqlite3_stmt *pSql;
         1744  +    rc = idxPrintfPrepareStmt(pNew->db, &pSql, pzErrmsg, 
         1745  +        "SELECT sql FROM sqlite_master WHERE name NOT LIKE 'sqlite_%%'"
         1746  +        " AND sql NOT LIKE 'CREATE VIRTUAL %%'"
         1747  +    );
         1748  +    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){
         1749  +      const char *zSql = (const char*)sqlite3_column_text(pSql, 0);
         1750  +      rc = sqlite3_exec(pNew->dbm, zSql, 0, 0, pzErrmsg);
         1751  +    }
         1752  +    idxFinalize(&rc, pSql);
         1753  +  }
         1754  +
         1755  +  /* Create the vtab schema */
         1756  +  if( rc==SQLITE_OK ){
         1757  +    rc = idxCreateVtabSchema(pNew, pzErrmsg);
         1758  +  }
         1759  +
         1760  +  /* Register the auth callback with dbv */
         1761  +  if( rc==SQLITE_OK ){
         1762  +    sqlite3_set_authorizer(pNew->dbv, idxAuthCallback, (void*)pNew);
         1763  +  }
         1764  +
         1765  +  /* If an error has occurred, free the new object and reutrn NULL. Otherwise,
         1766  +  ** return the new sqlite3expert handle.  */
         1767  +  if( rc!=SQLITE_OK ){
         1768  +    sqlite3_expert_destroy(pNew);
         1769  +    pNew = 0;
         1770  +  }
         1771  +  return pNew;
         1772  +}
         1773  +
         1774  +/*
         1775  +** Configure an sqlite3expert object.
         1776  +*/
         1777  +int sqlite3_expert_config(sqlite3expert *p, int op, ...){
         1778  +  int rc = SQLITE_OK;
         1779  +  va_list ap;
         1780  +  va_start(ap, op);
         1781  +  switch( op ){
         1782  +    case EXPERT_CONFIG_SAMPLE: {
         1783  +      int iVal = va_arg(ap, int);
         1784  +      if( iVal<0 ) iVal = 0;
         1785  +      if( iVal>100 ) iVal = 100;
         1786  +      p->iSample = iVal;
         1787  +      break;
         1788  +    }
         1789  +    default:
         1790  +      rc = SQLITE_NOTFOUND;
         1791  +      break;
         1792  +  }
         1793  +
         1794  +  va_end(ap);
         1795  +  return rc;
         1796  +}
         1797  +
         1798  +/*
         1799  +** Add an SQL statement to the analysis.
         1800  +*/
         1801  +int sqlite3_expert_sql(
         1802  +  sqlite3expert *p,               /* From sqlite3_expert_new() */
         1803  +  const char *zSql,               /* SQL statement to add */
         1804  +  char **pzErr                    /* OUT: Error message (if any) */
         1805  +){
         1806  +  IdxScan *pScanOrig = p->pScan;
         1807  +  IdxStatement *pStmtOrig = p->pStatement;
         1808  +  int rc = SQLITE_OK;
         1809  +  const char *zStmt = zSql;
         1810  +
         1811  +  if( p->bRun ) return SQLITE_MISUSE;
         1812  +
         1813  +  while( rc==SQLITE_OK && zStmt && zStmt[0] ){
         1814  +    sqlite3_stmt *pStmt = 0;
         1815  +    rc = sqlite3_prepare_v2(p->dbv, zStmt, -1, &pStmt, &zStmt);
         1816  +    if( rc==SQLITE_OK ){
         1817  +      if( pStmt ){
         1818  +        IdxStatement *pNew;
         1819  +        const char *z = sqlite3_sql(pStmt);
         1820  +        int n = STRLEN(z);
         1821  +        pNew = (IdxStatement*)idxMalloc(&rc, sizeof(IdxStatement) + n+1);
         1822  +        if( rc==SQLITE_OK ){
         1823  +          pNew->zSql = (char*)&pNew[1];
         1824  +          memcpy(pNew->zSql, z, n+1);
         1825  +          pNew->pNext = p->pStatement;
         1826  +          if( p->pStatement ) pNew->iId = p->pStatement->iId+1;
         1827  +          p->pStatement = pNew;
         1828  +        }
         1829  +        sqlite3_finalize(pStmt);
         1830  +      }
         1831  +    }else{
         1832  +      idxDatabaseError(p->dbv, pzErr);
         1833  +    }
         1834  +  }
         1835  +
         1836  +  if( rc!=SQLITE_OK ){
         1837  +    idxScanFree(p->pScan, pScanOrig);
         1838  +    idxStatementFree(p->pStatement, pStmtOrig);
         1839  +    p->pScan = pScanOrig;
         1840  +    p->pStatement = pStmtOrig;
         1841  +  }
         1842  +
         1843  +  return rc;
         1844  +}
         1845  +
         1846  +int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr){
         1847  +  int rc;
         1848  +  IdxHashEntry *pEntry;
         1849  +
         1850  +  /* Do trigger processing to collect any extra IdxScan structures */
         1851  +  rc = idxProcessTriggers(p, pzErr);
         1852  +
         1853  +  /* Create candidate indexes within the in-memory database file */
         1854  +  if( rc==SQLITE_OK ){
         1855  +    rc = idxCreateCandidates(p, pzErr);
         1856  +  }
         1857  +
         1858  +  /* Generate the stat1 data */
         1859  +  if( rc==SQLITE_OK ){
         1860  +    rc = idxPopulateStat1(p, pzErr);
         1861  +  }
         1862  +
         1863  +  /* Formulate the EXPERT_REPORT_CANDIDATES text */
         1864  +  for(pEntry=p->hIdx.pFirst; pEntry; pEntry=pEntry->pNext){
         1865  +    p->zCandidates = idxAppendText(&rc, p->zCandidates, 
         1866  +        "%s;%s%s\n", pEntry->zVal, 
         1867  +        pEntry->zVal2 ? " -- stat1: " : "", pEntry->zVal2
         1868  +    );
         1869  +  }
         1870  +
         1871  +  /* Figure out which of the candidate indexes are preferred by the query
         1872  +  ** planner and report the results to the user.  */
         1873  +  if( rc==SQLITE_OK ){
         1874  +    rc = idxFindIndexes(p, pzErr);
         1875  +  }
         1876  +
         1877  +  if( rc==SQLITE_OK ){
         1878  +    p->bRun = 1;
         1879  +  }
         1880  +  return rc;
         1881  +}
         1882  +
         1883  +/*
         1884  +** Return the total number of statements that have been added to this
         1885  +** sqlite3expert using sqlite3_expert_sql().
         1886  +*/
         1887  +int sqlite3_expert_count(sqlite3expert *p){
         1888  +  int nRet = 0;
         1889  +  if( p->pStatement ) nRet = p->pStatement->iId+1;
         1890  +  return nRet;
         1891  +}
         1892  +
         1893  +/*
         1894  +** Return a component of the report.
         1895  +*/
         1896  +const char *sqlite3_expert_report(sqlite3expert *p, int iStmt, int eReport){
         1897  +  const char *zRet = 0;
         1898  +  IdxStatement *pStmt;
         1899  +
         1900  +  if( p->bRun==0 ) return 0;
         1901  +  for(pStmt=p->pStatement; pStmt && pStmt->iId!=iStmt; pStmt=pStmt->pNext);
         1902  +  switch( eReport ){
         1903  +    case EXPERT_REPORT_SQL:
         1904  +      if( pStmt ) zRet = pStmt->zSql;
         1905  +      break;
         1906  +    case EXPERT_REPORT_INDEXES:
         1907  +      if( pStmt ) zRet = pStmt->zIdx;
         1908  +      break;
         1909  +    case EXPERT_REPORT_PLAN:
         1910  +      if( pStmt ) zRet = pStmt->zEQP;
         1911  +      break;
         1912  +    case EXPERT_REPORT_CANDIDATES:
         1913  +      zRet = p->zCandidates;
         1914  +      break;
         1915  +  }
         1916  +  return zRet;
         1917  +}
         1918  +
         1919  +/*
         1920  +** Free an sqlite3expert object.
         1921  +*/
         1922  +void sqlite3_expert_destroy(sqlite3expert *p){
         1923  +  if( p ){
         1924  +    sqlite3_close(p->dbm);
         1925  +    sqlite3_close(p->dbv);
         1926  +    idxScanFree(p->pScan, 0);
         1927  +    idxStatementFree(p->pStatement, 0);
         1928  +    idxTableFree(p->pTable);
         1929  +    idxWriteFree(p->pWrite);
         1930  +    idxHashClear(&p->hIdx);
         1931  +    sqlite3_free(p->zCandidates);
         1932  +    sqlite3_free(p);
         1933  +  }
         1934  +}

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 ext/misc/shathree.c.

    74     74   };
    75     75   
    76     76   /*
    77     77   ** A single step of the Keccak mixing function for a 1600-bit state
    78     78   */
    79     79   static void KeccakF1600Step(SHA3Context *p){
    80     80     int i;
    81         -  u64 B0, B1, B2, B3, B4;
    82         -  u64 C0, C1, C2, C3, C4;
    83         -  u64 D0, D1, D2, D3, D4;
           81  +  u64 b0, b1, b2, b3, b4;
           82  +  u64 c0, c1, c2, c3, c4;
           83  +  u64 d0, d1, d2, d3, d4;
    84     84     static const u64 RC[] = {
    85     85       0x0000000000000001ULL,  0x0000000000008082ULL,
    86     86       0x800000000000808aULL,  0x8000000080008000ULL,
    87     87       0x000000000000808bULL,  0x0000000080000001ULL,
    88     88       0x8000000080008081ULL,  0x8000000000008009ULL,
    89     89       0x000000000000008aULL,  0x0000000000000088ULL,
    90     90       0x0000000080008009ULL,  0x000000008000000aULL,
................................................................................
    91     91       0x000000008000808bULL,  0x800000000000008bULL,
    92     92       0x8000000000008089ULL,  0x8000000000008003ULL,
    93     93       0x8000000000008002ULL,  0x8000000000000080ULL,
    94     94       0x000000000000800aULL,  0x800000008000000aULL,
    95     95       0x8000000080008081ULL,  0x8000000000008080ULL,
    96     96       0x0000000080000001ULL,  0x8000000080008008ULL
    97     97     };
    98         -# define A00 (p->u.s[0])
    99         -# define A01 (p->u.s[1])
   100         -# define A02 (p->u.s[2])
   101         -# define A03 (p->u.s[3])
   102         -# define A04 (p->u.s[4])
   103         -# define A10 (p->u.s[5])
   104         -# define A11 (p->u.s[6])
   105         -# define A12 (p->u.s[7])
   106         -# define A13 (p->u.s[8])
   107         -# define A14 (p->u.s[9])
   108         -# define A20 (p->u.s[10])
   109         -# define A21 (p->u.s[11])
   110         -# define A22 (p->u.s[12])
   111         -# define A23 (p->u.s[13])
   112         -# define A24 (p->u.s[14])
   113         -# define A30 (p->u.s[15])
   114         -# define A31 (p->u.s[16])
   115         -# define A32 (p->u.s[17])
   116         -# define A33 (p->u.s[18])
   117         -# define A34 (p->u.s[19])
   118         -# define A40 (p->u.s[20])
   119         -# define A41 (p->u.s[21])
   120         -# define A42 (p->u.s[22])
   121         -# define A43 (p->u.s[23])
   122         -# define A44 (p->u.s[24])
           98  +# define a00 (p->u.s[0])
           99  +# define a01 (p->u.s[1])
          100  +# define a02 (p->u.s[2])
          101  +# define a03 (p->u.s[3])
          102  +# define a04 (p->u.s[4])
          103  +# define a10 (p->u.s[5])
          104  +# define a11 (p->u.s[6])
          105  +# define a12 (p->u.s[7])
          106  +# define a13 (p->u.s[8])
          107  +# define a14 (p->u.s[9])
          108  +# define a20 (p->u.s[10])
          109  +# define a21 (p->u.s[11])
          110  +# define a22 (p->u.s[12])
          111  +# define a23 (p->u.s[13])
          112  +# define a24 (p->u.s[14])
          113  +# define a30 (p->u.s[15])
          114  +# define a31 (p->u.s[16])
          115  +# define a32 (p->u.s[17])
          116  +# define a33 (p->u.s[18])
          117  +# define a34 (p->u.s[19])
          118  +# define a40 (p->u.s[20])
          119  +# define a41 (p->u.s[21])
          120  +# define a42 (p->u.s[22])
          121  +# define a43 (p->u.s[23])
          122  +# define a44 (p->u.s[24])
   123    123   # define ROL64(a,x) ((a<<x)|(a>>(64-x)))
   124    124   
   125    125     for(i=0; i<24; i+=4){
   126         -    C0 = A00^A10^A20^A30^A40;
   127         -    C1 = A01^A11^A21^A31^A41;
   128         -    C2 = A02^A12^A22^A32^A42;
   129         -    C3 = A03^A13^A23^A33^A43;
   130         -    C4 = A04^A14^A24^A34^A44;
   131         -    D0 = C4^ROL64(C1, 1);
   132         -    D1 = C0^ROL64(C2, 1);
   133         -    D2 = C1^ROL64(C3, 1);
   134         -    D3 = C2^ROL64(C4, 1);
   135         -    D4 = C3^ROL64(C0, 1);
   136         -
   137         -    B0 = (A00^D0);
   138         -    B1 = ROL64((A11^D1), 44);
   139         -    B2 = ROL64((A22^D2), 43);
   140         -    B3 = ROL64((A33^D3), 21);
   141         -    B4 = ROL64((A44^D4), 14);
   142         -    A00 =   B0 ^((~B1)&  B2 );
   143         -    A00 ^= RC[i];
   144         -    A11 =   B1 ^((~B2)&  B3 );
   145         -    A22 =   B2 ^((~B3)&  B4 );
   146         -    A33 =   B3 ^((~B4)&  B0 );
   147         -    A44 =   B4 ^((~B0)&  B1 );
   148         -
   149         -    B2 = ROL64((A20^D0), 3);
   150         -    B3 = ROL64((A31^D1), 45);
   151         -    B4 = ROL64((A42^D2), 61);
   152         -    B0 = ROL64((A03^D3), 28);
   153         -    B1 = ROL64((A14^D4), 20);
   154         -    A20 =   B0 ^((~B1)&  B2 );
   155         -    A31 =   B1 ^((~B2)&  B3 );
   156         -    A42 =   B2 ^((~B3)&  B4 );
   157         -    A03 =   B3 ^((~B4)&  B0 );
   158         -    A14 =   B4 ^((~B0)&  B1 );
   159         -
   160         -    B4 = ROL64((A40^D0), 18);
   161         -    B0 = ROL64((A01^D1), 1);
   162         -    B1 = ROL64((A12^D2), 6);
   163         -    B2 = ROL64((A23^D3), 25);
   164         -    B3 = ROL64((A34^D4), 8);
   165         -    A40 =   B0 ^((~B1)&  B2 );
   166         -    A01 =   B1 ^((~B2)&  B3 );
   167         -    A12 =   B2 ^((~B3)&  B4 );
   168         -    A23 =   B3 ^((~B4)&  B0 );
   169         -    A34 =   B4 ^((~B0)&  B1 );
   170         -
   171         -    B1 = ROL64((A10^D0), 36);
   172         -    B2 = ROL64((A21^D1), 10);
   173         -    B3 = ROL64((A32^D2), 15);
   174         -    B4 = ROL64((A43^D3), 56);
   175         -    B0 = ROL64((A04^D4), 27);
   176         -    A10 =   B0 ^((~B1)&  B2 );
   177         -    A21 =   B1 ^((~B2)&  B3 );
   178         -    A32 =   B2 ^((~B3)&  B4 );
   179         -    A43 =   B3 ^((~B4)&  B0 );
   180         -    A04 =   B4 ^((~B0)&  B1 );
   181         -
   182         -    B3 = ROL64((A30^D0), 41);
   183         -    B4 = ROL64((A41^D1), 2);
   184         -    B0 = ROL64((A02^D2), 62);
   185         -    B1 = ROL64((A13^D3), 55);
   186         -    B2 = ROL64((A24^D4), 39);
   187         -    A30 =   B0 ^((~B1)&  B2 );
   188         -    A41 =   B1 ^((~B2)&  B3 );
   189         -    A02 =   B2 ^((~B3)&  B4 );
   190         -    A13 =   B3 ^((~B4)&  B0 );
   191         -    A24 =   B4 ^((~B0)&  B1 );
          126  +    c0 = a00^a10^a20^a30^a40;
          127  +    c1 = a01^a11^a21^a31^a41;
          128  +    c2 = a02^a12^a22^a32^a42;
          129  +    c3 = a03^a13^a23^a33^a43;
          130  +    c4 = a04^a14^a24^a34^a44;
          131  +    d0 = c4^ROL64(c1, 1);
          132  +    d1 = c0^ROL64(c2, 1);
          133  +    d2 = c1^ROL64(c3, 1);
          134  +    d3 = c2^ROL64(c4, 1);
          135  +    d4 = c3^ROL64(c0, 1);
          136  +
          137  +    b0 = (a00^d0);
          138  +    b1 = ROL64((a11^d1), 44);
          139  +    b2 = ROL64((a22^d2), 43);
          140  +    b3 = ROL64((a33^d3), 21);
          141  +    b4 = ROL64((a44^d4), 14);
          142  +    a00 =   b0 ^((~b1)&  b2 );
          143  +    a00 ^= RC[i];
          144  +    a11 =   b1 ^((~b2)&  b3 );
          145  +    a22 =   b2 ^((~b3)&  b4 );
          146  +    a33 =   b3 ^((~b4)&  b0 );
          147  +    a44 =   b4 ^((~b0)&  b1 );
          148  +
          149  +    b2 = ROL64((a20^d0), 3);
          150  +    b3 = ROL64((a31^d1), 45);
          151  +    b4 = ROL64((a42^d2), 61);
          152  +    b0 = ROL64((a03^d3), 28);
          153  +    b1 = ROL64((a14^d4), 20);
          154  +    a20 =   b0 ^((~b1)&  b2 );
          155  +    a31 =   b1 ^((~b2)&  b3 );
          156  +    a42 =   b2 ^((~b3)&  b4 );
          157  +    a03 =   b3 ^((~b4)&  b0 );
          158  +    a14 =   b4 ^((~b0)&  b1 );
          159  +
          160  +    b4 = ROL64((a40^d0), 18);
          161  +    b0 = ROL64((a01^d1), 1);
          162  +    b1 = ROL64((a12^d2), 6);
          163  +    b2 = ROL64((a23^d3), 25);
          164  +    b3 = ROL64((a34^d4), 8);
          165  +    a40 =   b0 ^((~b1)&  b2 );
          166  +    a01 =   b1 ^((~b2)&  b3 );
          167  +    a12 =   b2 ^((~b3)&  b4 );
          168  +    a23 =   b3 ^((~b4)&  b0 );
          169  +    a34 =   b4 ^((~b0)&  b1 );
          170  +
          171  +    b1 = ROL64((a10^d0), 36);
          172  +    b2 = ROL64((a21^d1), 10);
          173  +    b3 = ROL64((a32^d2), 15);
          174  +    b4 = ROL64((a43^d3), 56);
          175  +    b0 = ROL64((a04^d4), 27);
          176  +    a10 =   b0 ^((~b1)&  b2 );
          177  +    a21 =   b1 ^((~b2)&  b3 );
          178  +    a32 =   b2 ^((~b3)&  b4 );
          179  +    a43 =   b3 ^((~b4)&  b0 );
          180  +    a04 =   b4 ^((~b0)&  b1 );
          181  +
          182  +    b3 = ROL64((a30^d0), 41);
          183  +    b4 = ROL64((a41^d1), 2);
          184  +    b0 = ROL64((a02^d2), 62);
          185  +    b1 = ROL64((a13^d3), 55);
          186  +    b2 = ROL64((a24^d4), 39);
          187  +    a30 =   b0 ^((~b1)&  b2 );
          188  +    a41 =   b1 ^((~b2)&  b3 );
          189  +    a02 =   b2 ^((~b3)&  b4 );
          190  +    a13 =   b3 ^((~b4)&  b0 );
          191  +    a24 =   b4 ^((~b0)&  b1 );
          192  +
          193  +    c0 = a00^a20^a40^a10^a30;
          194  +    c1 = a11^a31^a01^a21^a41;
          195  +    c2 = a22^a42^a12^a32^a02;
          196  +    c3 = a33^a03^a23^a43^a13;
          197  +    c4 = a44^a14^a34^a04^a24;
          198  +    d0 = c4^ROL64(c1, 1);
          199  +    d1 = c0^ROL64(c2, 1);
          200  +    d2 = c1^ROL64(c3, 1);
          201  +    d3 = c2^ROL64(c4, 1);
          202  +    d4 = c3^ROL64(c0, 1);
          203  +
          204  +    b0 = (a00^d0);
          205  +    b1 = ROL64((a31^d1), 44);
          206  +    b2 = ROL64((a12^d2), 43);
          207  +    b3 = ROL64((a43^d3), 21);
          208  +    b4 = ROL64((a24^d4), 14);
          209  +    a00 =   b0 ^((~b1)&  b2 );
          210  +    a00 ^= RC[i+1];
          211  +    a31 =   b1 ^((~b2)&  b3 );
          212  +    a12 =   b2 ^((~b3)&  b4 );
          213  +    a43 =   b3 ^((~b4)&  b0 );
          214  +    a24 =   b4 ^((~b0)&  b1 );
          215  +
          216  +    b2 = ROL64((a40^d0), 3);
          217  +    b3 = ROL64((a21^d1), 45);
          218  +    b4 = ROL64((a02^d2), 61);
          219  +    b0 = ROL64((a33^d3), 28);
          220  +    b1 = ROL64((a14^d4), 20);
          221  +    a40 =   b0 ^((~b1)&  b2 );
          222  +    a21 =   b1 ^((~b2)&  b3 );
          223  +    a02 =   b2 ^((~b3)&  b4 );
          224  +    a33 =   b3 ^((~b4)&  b0 );
          225  +    a14 =   b4 ^((~b0)&  b1 );
          226  +
          227  +    b4 = ROL64((a30^d0), 18);
          228  +    b0 = ROL64((a11^d1), 1);
          229  +    b1 = ROL64((a42^d2), 6);
          230  +    b2 = ROL64((a23^d3), 25);
          231  +    b3 = ROL64((a04^d4), 8);
          232  +    a30 =   b0 ^((~b1)&  b2 );
          233  +    a11 =   b1 ^((~b2)&  b3 );
          234  +    a42 =   b2 ^((~b3)&  b4 );
          235  +    a23 =   b3 ^((~b4)&  b0 );
          236  +    a04 =   b4 ^((~b0)&  b1 );
          237  +
          238  +    b1 = ROL64((a20^d0), 36);
          239  +    b2 = ROL64((a01^d1), 10);
          240  +    b3 = ROL64((a32^d2), 15);
          241  +    b4 = ROL64((a13^d3), 56);
          242  +    b0 = ROL64((a44^d4), 27);
          243  +    a20 =   b0 ^((~b1)&  b2 );
          244  +    a01 =   b1 ^((~b2)&  b3 );
          245  +    a32 =   b2 ^((~b3)&  b4 );
          246  +    a13 =   b3 ^((~b4)&  b0 );
          247  +    a44 =   b4 ^((~b0)&  b1 );
          248  +
          249  +    b3 = ROL64((a10^d0), 41);
          250  +    b4 = ROL64((a41^d1), 2);
          251  +    b0 = ROL64((a22^d2), 62);
          252  +    b1 = ROL64((a03^d3), 55);
          253  +    b2 = ROL64((a34^d4), 39);
          254  +    a10 =   b0 ^((~b1)&  b2 );
          255  +    a41 =   b1 ^((~b2)&  b3 );
          256  +    a22 =   b2 ^((~b3)&  b4 );
          257  +    a03 =   b3 ^((~b4)&  b0 );
          258  +    a34 =   b4 ^((~b0)&  b1 );
          259  +
          260  +    c0 = a00^a40^a30^a20^a10;
          261  +    c1 = a31^a21^a11^a01^a41;
          262  +    c2 = a12^a02^a42^a32^a22;
          263  +    c3 = a43^a33^a23^a13^a03;
          264  +    c4 = a24^a14^a04^a44^a34;
          265  +    d0 = c4^ROL64(c1, 1);
          266  +    d1 = c0^ROL64(c2, 1);
          267  +    d2 = c1^ROL64(c3, 1);
          268  +    d3 = c2^ROL64(c4, 1);
          269  +    d4 = c3^ROL64(c0, 1);
          270  +
          271  +    b0 = (a00^d0);
          272  +    b1 = ROL64((a21^d1), 44);
          273  +    b2 = ROL64((a42^d2), 43);
          274  +    b3 = ROL64((a13^d3), 21);
          275  +    b4 = ROL64((a34^d4), 14);
          276  +    a00 =   b0 ^((~b1)&  b2 );
          277  +    a00 ^= RC[i+2];
          278  +    a21 =   b1 ^((~b2)&  b3 );
          279  +    a42 =   b2 ^((~b3)&  b4 );
          280  +    a13 =   b3 ^((~b4)&  b0 );
          281  +    a34 =   b4 ^((~b0)&  b1 );
          282  +
          283  +    b2 = ROL64((a30^d0), 3);
          284  +    b3 = ROL64((a01^d1), 45);
          285  +    b4 = ROL64((a22^d2), 61);
          286  +    b0 = ROL64((a43^d3), 28);
          287  +    b1 = ROL64((a14^d4), 20);
          288  +    a30 =   b0 ^((~b1)&  b2 );
          289  +    a01 =   b1 ^((~b2)&  b3 );
          290  +    a22 =   b2 ^((~b3)&  b4 );
          291  +    a43 =   b3 ^((~b4)&  b0 );
          292  +    a14 =   b4 ^((~b0)&  b1 );
          293  +
          294  +    b4 = ROL64((a10^d0), 18);
          295  +    b0 = ROL64((a31^d1), 1);
          296  +    b1 = ROL64((a02^d2), 6);
          297  +    b2 = ROL64((a23^d3), 25);
          298  +    b3 = ROL64((a44^d4), 8);
          299  +    a10 =   b0 ^((~b1)&  b2 );
          300  +    a31 =   b1 ^((~b2)&  b3 );
          301  +    a02 =   b2 ^((~b3)&  b4 );
          302  +    a23 =   b3 ^((~b4)&  b0 );
          303  +    a44 =   b4 ^((~b0)&  b1 );
          304  +
          305  +    b1 = ROL64((a40^d0), 36);
          306  +    b2 = ROL64((a11^d1), 10);
          307  +    b3 = ROL64((a32^d2), 15);
          308  +    b4 = ROL64((a03^d3), 56);
          309  +    b0 = ROL64((a24^d4), 27);
          310  +    a40 =   b0 ^((~b1)&  b2 );
          311  +    a11 =   b1 ^((~b2)&  b3 );
          312  +    a32 =   b2 ^((~b3)&  b4 );
          313  +    a03 =   b3 ^((~b4)&  b0 );
          314  +    a24 =   b4 ^((~b0)&  b1 );
          315  +
          316  +    b3 = ROL64((a20^d0), 41);
          317  +    b4 = ROL64((a41^d1), 2);
          318  +    b0 = ROL64((a12^d2), 62);
          319  +    b1 = ROL64((a33^d3), 55);
          320  +    b2 = ROL64((a04^d4), 39);
          321  +    a20 =   b0 ^((~b1)&  b2 );
          322  +    a41 =   b1 ^((~b2)&  b3 );
          323  +    a12 =   b2 ^((~b3)&  b4 );
          324  +    a33 =   b3 ^((~b4)&  b0 );
          325  +    a04 =   b4 ^((~b0)&  b1 );
          326  +
          327  +    c0 = a00^a30^a10^a40^a20;
          328  +    c1 = a21^a01^a31^a11^a41;
          329  +    c2 = a42^a22^a02^a32^a12;
          330  +    c3 = a13^a43^a23^a03^a33;
          331  +    c4 = a34^a14^a44^a24^a04;
          332  +    d0 = c4^ROL64(c1, 1);
          333  +    d1 = c0^ROL64(c2, 1);
          334  +    d2 = c1^ROL64(c3, 1);
          335  +    d3 = c2^ROL64(c4, 1);
          336  +    d4 = c3^ROL64(c0, 1);
          337  +
          338  +    b0 = (a00^d0);
          339  +    b1 = ROL64((a01^d1), 44);
          340  +    b2 = ROL64((a02^d2), 43);
          341  +    b3 = ROL64((a03^d3), 21);
          342  +    b4 = ROL64((a04^d4), 14);
          343  +    a00 =   b0 ^((~b1)&  b2 );
          344  +    a00 ^= RC[i+3];
          345  +    a01 =   b1 ^((~b2)&  b3 );
          346  +    a02 =   b2 ^((~b3)&  b4 );
          347  +    a03 =   b3 ^((~b4)&  b0 );
          348  +    a04 =   b4 ^((~b0)&  b1 );
          349  +
          350  +    b2 = ROL64((a10^d0), 3);
          351  +    b3 = ROL64((a11^d1), 45);
          352  +    b4 = ROL64((a12^d2), 61);
          353  +    b0 = ROL64((a13^d3), 28);
          354  +    b1 = ROL64((a14^d4), 20);
          355  +    a10 =   b0 ^((~b1)&  b2 );
          356  +    a11 =   b1 ^((~b2)&  b3 );
          357  +    a12 =   b2 ^((~b3)&  b4 );
          358  +    a13 =   b3 ^((~b4)&  b0 );
          359  +    a14 =   b4 ^((~b0)&  b1 );
          360  +
          361  +    b4 = ROL64((a20^d0), 18);
          362  +    b0 = ROL64((a21^d1), 1);
          363  +    b1 = ROL64((a22^d2), 6);
          364  +    b2 = ROL64((a23^d3), 25);
          365  +    b3 = ROL64((a24^d4), 8);
          366  +    a20 =   b0 ^((~b1)&  b2 );
          367  +    a21 =   b1 ^((~b2)&  b3 );
          368  +    a22 =   b2 ^((~b3)&  b4 );
          369  +    a23 =   b3 ^((~b4)&  b0 );
          370  +    a24 =   b4 ^((~b0)&  b1 );
   192    371   
   193         -    C0 = A00^A20^A40^A10^A30;
   194         -    C1 = A11^A31^A01^A21^A41;
   195         -    C2 = A22^A42^A12^A32^A02;
   196         -    C3 = A33^A03^A23^A43^A13;
   197         -    C4 = A44^A14^A34^A04^A24;
   198         -    D0 = C4^ROL64(C1, 1);
   199         -    D1 = C0^ROL64(C2, 1);
   200         -    D2 = C1^ROL64(C3, 1);
   201         -    D3 = C2^ROL64(C4, 1);
   202         -    D4 = C3^ROL64(C0, 1);
   203         -
   204         -    B0 = (A00^D0);
   205         -    B1 = ROL64((A31^D1), 44);
   206         -    B2 = ROL64((A12^D2), 43);
   207         -    B3 = ROL64((A43^D3), 21);
   208         -    B4 = ROL64((A24^D4), 14);
   209         -    A00 =   B0 ^((~B1)&  B2 );
   210         -    A00 ^= RC[i+1];
   211         -    A31 =   B1 ^((~B2)&  B3 );
   212         -    A12 =   B2 ^((~B3)&  B4 );
   213         -    A43 =   B3 ^((~B4)&  B0 );
   214         -    A24 =   B4 ^((~B0)&  B1 );
   215         -
   216         -    B2 = ROL64((A40^D0), 3);
   217         -    B3 = ROL64((A21^D1), 45);
   218         -    B4 = ROL64((A02^D2), 61);
   219         -    B0 = ROL64((A33^D3), 28);
   220         -    B1 = ROL64((A14^D4), 20);
   221         -    A40 =   B0 ^((~B1)&  B2 );
   222         -    A21 =   B1 ^((~B2)&  B3 );
   223         -    A02 =   B2 ^((~B3)&  B4 );
   224         -    A33 =   B3 ^((~B4)&  B0 );
   225         -    A14 =   B4 ^((~B0)&  B1 );
   226         -
   227         -    B4 = ROL64((A30^D0), 18);
   228         -    B0 = ROL64((A11^D1), 1);
   229         -    B1 = ROL64((A42^D2), 6);
   230         -    B2 = ROL64((A23^D3), 25);
   231         -    B3 = ROL64((A04^D4), 8);
   232         -    A30 =   B0 ^((~B1)&  B2 );
   233         -    A11 =   B1 ^((~B2)&  B3 );
   234         -    A42 =   B2 ^((~B3)&  B4 );
   235         -    A23 =   B3 ^((~B4)&  B0 );
   236         -    A04 =   B4 ^((~B0)&  B1 );
   237         -
   238         -    B1 = ROL64((A20^D0), 36);
   239         -    B2 = ROL64((A01^D1), 10);
   240         -    B3 = ROL64((A32^D2), 15);
   241         -    B4 = ROL64((A13^D3), 56);
   242         -    B0 = ROL64((A44^D4), 27);
   243         -    A20 =   B0 ^((~B1)&  B2 );
   244         -    A01 =   B1 ^((~B2)&  B3 );
   245         -    A32 =   B2 ^((~B3)&  B4 );
   246         -    A13 =   B3 ^((~B4)&  B0 );
   247         -    A44 =   B4 ^((~B0)&  B1 );
   248         -
   249         -    B3 = ROL64((A10^D0), 41);
   250         -    B4 = ROL64((A41^D1), 2);
   251         -    B0 = ROL64((A22^D2), 62);
   252         -    B1 = ROL64((A03^D3), 55);
   253         -    B2 = ROL64((A34^D4), 39);
   254         -    A10 =   B0 ^((~B1)&  B2 );
   255         -    A41 =   B1 ^((~B2)&  B3 );
   256         -    A22 =   B2 ^((~B3)&  B4 );
   257         -    A03 =   B3 ^((~B4)&  B0 );
   258         -    A34 =   B4 ^((~B0)&  B1 );
          372  +    b1 = ROL64((a30^d0), 36);
          373  +    b2 = ROL64((a31^d1), 10);
          374  +    b3 = ROL64((a32^d2), 15);
          375  +    b4 = ROL64((a33^d3), 56);
          376  +    b0 = ROL64((a34^d4), 27);
          377  +    a30 =   b0 ^((~b1)&  b2 );
          378  +    a31 =   b1 ^((~b2)&  b3 );
          379  +    a32 =   b2 ^((~b3)&  b4 );
          380  +    a33 =   b3 ^((~b4)&  b0 );
          381  +    a34 =   b4 ^((~b0)&  b1 );
   259    382   
   260         -    C0 = A00^A40^A30^A20^A10;
   261         -    C1 = A31^A21^A11^A01^A41;
   262         -    C2 = A12^A02^A42^A32^A22;
   263         -    C3 = A43^A33^A23^A13^A03;
   264         -    C4 = A24^A14^A04^A44^A34;
   265         -    D0 = C4^ROL64(C1, 1);
   266         -    D1 = C0^ROL64(C2, 1);
   267         -    D2 = C1^ROL64(C3, 1);
   268         -    D3 = C2^ROL64(C4, 1);
   269         -    D4 = C3^ROL64(C0, 1);
   270         -
   271         -    B0 = (A00^D0);
   272         -    B1 = ROL64((A21^D1), 44);
   273         -    B2 = ROL64((A42^D2), 43);
   274         -    B3 = ROL64((A13^D3), 21);
   275         -    B4 = ROL64((A34^D4), 14);
   276         -    A00 =   B0 ^((~B1)&  B2 );
   277         -    A00 ^= RC[i+2];
   278         -    A21 =   B1 ^((~B2)&  B3 );
   279         -    A42 =   B2 ^((~B3)&  B4 );
   280         -    A13 =   B3 ^((~B4)&  B0 );
   281         -    A34 =   B4 ^((~B0)&  B1 );
   282         -
   283         -    B2 = ROL64((A30^D0), 3);
   284         -    B3 = ROL64((A01^D1), 45);
   285         -    B4 = ROL64((A22^D2), 61);
   286         -    B0 = ROL64((A43^D3), 28);
   287         -    B1 = ROL64((A14^D4), 20);
   288         -    A30 =   B0 ^((~B1)&  B2 );
   289         -    A01 =   B1 ^((~B2)&  B3 );
   290         -    A22 =   B2 ^((~B3)&  B4 );
   291         -    A43 =   B3 ^((~B4)&  B0 );
   292         -    A14 =   B4 ^((~B0)&  B1 );
   293         -
   294         -    B4 = ROL64((A10^D0), 18);
   295         -    B0 = ROL64((A31^D1), 1);
   296         -    B1 = ROL64((A02^D2), 6);
   297         -    B2 = ROL64((A23^D3), 25);
   298         -    B3 = ROL64((A44^D4), 8);
   299         -    A10 =   B0 ^((~B1)&  B2 );
   300         -    A31 =   B1 ^((~B2)&  B3 );
   301         -    A02 =   B2 ^((~B3)&  B4 );
   302         -    A23 =   B3 ^((~B4)&  B0 );
   303         -    A44 =   B4 ^((~B0)&  B1 );
   304         -
   305         -    B1 = ROL64((A40^D0), 36);
   306         -    B2 = ROL64((A11^D1), 10);
   307         -    B3 = ROL64((A32^D2), 15);
   308         -    B4 = ROL64((A03^D3), 56);
   309         -    B0 = ROL64((A24^D4), 27);
   310         -    A40 =   B0 ^((~B1)&  B2 );
   311         -    A11 =   B1 ^((~B2)&  B3 );
   312         -    A32 =   B2 ^((~B3)&  B4 );
   313         -    A03 =   B3 ^((~B4)&  B0 );
   314         -    A24 =   B4 ^((~B0)&  B1 );
   315         -
   316         -    B3 = ROL64((A20^D0), 41);
   317         -    B4 = ROL64((A41^D1), 2);
   318         -    B0 = ROL64((A12^D2), 62);
   319         -    B1 = ROL64((A33^D3), 55);
   320         -    B2 = ROL64((A04^D4), 39);
   321         -    A20 =   B0 ^((~B1)&  B2 );
   322         -    A41 =   B1 ^((~B2)&  B3 );
   323         -    A12 =   B2 ^((~B3)&  B4 );
   324         -    A33 =   B3 ^((~B4)&  B0 );
   325         -    A04 =   B4 ^((~B0)&  B1 );
   326         -
   327         -    C0 = A00^A30^A10^A40^A20;
   328         -    C1 = A21^A01^A31^A11^A41;
   329         -    C2 = A42^A22^A02^A32^A12;
   330         -    C3 = A13^A43^A23^A03^A33;
   331         -    C4 = A34^A14^A44^A24^A04;
   332         -    D0 = C4^ROL64(C1, 1);
   333         -    D1 = C0^ROL64(C2, 1);
   334         -    D2 = C1^ROL64(C3, 1);
   335         -    D3 = C2^ROL64(C4, 1);
   336         -    D4 = C3^ROL64(C0, 1);
   337         -
   338         -    B0 = (A00^D0);
   339         -    B1 = ROL64((A01^D1), 44);
   340         -    B2 = ROL64((A02^D2), 43);
   341         -    B3 = ROL64((A03^D3), 21);
   342         -    B4 = ROL64((A04^D4), 14);
   343         -    A00 =   B0 ^((~B1)&  B2 );
   344         -    A00 ^= RC[i+3];
   345         -    A01 =   B1 ^((~B2)&  B3 );
   346         -    A02 =   B2 ^((~B3)&  B4 );
   347         -    A03 =   B3 ^((~B4)&  B0 );
   348         -    A04 =   B4 ^((~B0)&  B1 );
   349         -
   350         -    B2 = ROL64((A10^D0), 3);
   351         -    B3 = ROL64((A11^D1), 45);
   352         -    B4 = ROL64((A12^D2), 61);
   353         -    B0 = ROL64((A13^D3), 28);
   354         -    B1 = ROL64((A14^D4), 20);
   355         -    A10 =   B0 ^((~B1)&  B2 );
   356         -    A11 =   B1 ^((~B2)&  B3 );
   357         -    A12 =   B2 ^((~B3)&  B4 );
   358         -    A13 =   B3 ^((~B4)&  B0 );
   359         -    A14 =   B4 ^((~B0)&  B1 );
   360         -
   361         -    B4 = ROL64((A20^D0), 18);
   362         -    B0 = ROL64((A21^D1), 1);
   363         -    B1 = ROL64((A22^D2), 6);
   364         -    B2 = ROL64((A23^D3), 25);
   365         -    B3 = ROL64((A24^D4), 8);
   366         -    A20 =   B0 ^((~B1)&  B2 );
   367         -    A21 =   B1 ^((~B2)&  B3 );
   368         -    A22 =   B2 ^((~B3)&  B4 );
   369         -    A23 =   B3 ^((~B4)&  B0 );
   370         -    A24 =   B4 ^((~B0)&  B1 );
   371         -
   372         -    B1 = ROL64((A30^D0), 36);
   373         -    B2 = ROL64((A31^D1), 10);
   374         -    B3 = ROL64((A32^D2), 15);
   375         -    B4 = ROL64((A33^D3), 56);
   376         -    B0 = ROL64((A34^D4), 27);
   377         -    A30 =   B0 ^((~B1)&  B2 );
   378         -    A31 =   B1 ^((~B2)&  B3 );
   379         -    A32 =   B2 ^((~B3)&  B4 );
   380         -    A33 =   B3 ^((~B4)&  B0 );
   381         -    A34 =   B4 ^((~B0)&  B1 );
   382         -
   383         -    B3 = ROL64((A40^D0), 41);
   384         -    B4 = ROL64((A41^D1), 2);
   385         -    B0 = ROL64((A42^D2), 62);
   386         -    B1 = ROL64((A43^D3), 55);
   387         -    B2 = ROL64((A44^D4), 39);
   388         -    A40 =   B0 ^((~B1)&  B2 );
   389         -    A41 =   B1 ^((~B2)&  B3 );
   390         -    A42 =   B2 ^((~B3)&  B4 );
   391         -    A43 =   B3 ^((~B4)&  B0 );
   392         -    A44 =   B4 ^((~B0)&  B1 );
          383  +    b3 = ROL64((a40^d0), 41);
          384  +    b4 = ROL64((a41^d1), 2);
          385  +    b0 = ROL64((a42^d2), 62);
          386  +    b1 = ROL64((a43^d3), 55);
          387  +    b2 = ROL64((a44^d4), 39);
          388  +    a40 =   b0 ^((~b1)&  b2 );
          389  +    a41 =   b1 ^((~b2)&  b3 );
          390  +    a42 =   b2 ^((~b3)&  b4 );
          391  +    a43 =   b3 ^((~b4)&  b0 );
          392  +    a44 =   b4 ^((~b0)&  b1 );
   393    393     }
   394    394   }
   395    395   
   396    396   /*
   397    397   ** Initialize a new hash.  iSize determines the size of the hash
   398    398   ** in bits and should be one of 224, 256, 384, or 512.  Or iSize
   399    399   ** can be zero to use the default hash size of 256 bits.

Changes to ext/misc/unionvtab.c.

    51     51   **     3. The smallest rowid in the range of rowids that may be stored in the
    52     52   **        database table (an integer).
    53     53   **
    54     54   **     4. The largest rowid in the range of rowids that may be stored in the
    55     55   **        database table (an integer).
    56     56   **
    57     57   ** SWARMVTAB
           58  +**
           59  +**  LEGACY SYNTAX:
    58     60   **
    59     61   **   A "swarmvtab" virtual table is created similarly to a unionvtab table:
    60     62   **
    61     63   **     CREATE VIRTUAL TABLE <name>
    62     64   **      USING swarmvtab(<sql-statement>, <callback>);
    63     65   **
    64     66   **   The difference is that for a swarmvtab table, the first column returned
    65     67   **   by the <sql statement> must return a path or URI that can be used to open
    66     68   **   the database file containing the source table.  The <callback> option
    67     69   **   is optional.  If included, it is the name of an application-defined
    68     70   **   SQL function that is invoked with the URI of the file, if the file
    69         -**   does not already exist on disk.
           71  +**   does not already exist on disk when required by swarmvtab.
           72  +**
           73  +**  NEW SYNTAX:
           74  +**
           75  +**   Using the new syntax, a swarmvtab table is created with:
           76  +**
           77  +**      CREATE VIRTUAL TABLE <name> USING swarmvtab(
           78  +**        <sql-statement> [, <options>]
           79  +**      );
           80  +**
           81  +**   where valid <options> are:
           82  +**
           83  +**      missing=<udf-function-name>
           84  +**      openclose=<udf-function-name>
           85  +**      maxopen=<integer>
           86  +**      <sql-parameter>=<text-value>
           87  +**
           88  +**   The <sql-statement> must return the same 4 columns as for a swarmvtab
           89  +**   table in legacy mode. However, it may also return a 5th column - the
           90  +**   "context" column. The text value returned in this column is not used
           91  +**   at all by the swarmvtab implementation, except that it is passed as
           92  +**   an additional argument to the two UDF functions that may be invoked
           93  +**   (see below).
           94  +**
           95  +**   The "missing" option, if present, specifies the name of an SQL UDF
           96  +**   function to be invoked if a database file is not already present on
           97  +**   disk when required by swarmvtab. If the <sql-statement> did not provide
           98  +**   a context column, it is invoked as:
           99  +**
          100  +**     SELECT <missing-udf>(<database filename/uri>);
          101  +**
          102  +**   Or, if there was a context column:
          103  +**
          104  +**     SELECT <missing-udf>(<database filename/uri>, <context>);
          105  +**
          106  +**   The "openclose" option may also specify a UDF function. This function
          107  +**   is invoked right before swarmvtab opens a database, and right after
          108  +**   it closes one. The first argument - or first two arguments, if
          109  +**   <sql-statement> supplied the context column - is the same as for
          110  +**   the "missing" UDF. Following this, the UDF is passed integer value
          111  +**   0 before a db is opened, and 1 right after it is closed. If both
          112  +**   a missing and openclose UDF is supplied, the application should expect
          113  +**   the following sequence of calls (for a single database):
          114  +**
          115  +**      SELECT <openclose-udf>(<db filename>, <context>, 0);
          116  +**      if( db not already on disk ){
          117  +**          SELECT <missing-udf>(<db filename>, <context>);
          118  +**      }
          119  +**      ... swarmvtab uses database ...
          120  +**      SELECT <openclose-udf>(<db filename>, <context>, 1);
          121  +**
          122  +**   The "maxopen" option is used to configure the maximum number of
          123  +**   database files swarmvtab will hold open simultaneously (default 9).
          124  +**
          125  +**   If an option name begins with a ":" character, then it is assumed
          126  +**   to be an SQL parameter. In this case, the specified text value is
          127  +**   bound to the same variable of the <sql-statement> before it is 
          128  +**   executed. It is an error of the named SQL parameter does not exist.
          129  +**   For example:
          130  +**
          131  +**     CREATE VIRTUAL TABLE swarm USING swarmvtab(
          132  +**       'SELECT :path || localfile, tbl, min, max FROM swarmdir',
          133  +**       :path='/home/user/databases/'
          134  +**       missing='missing_func'
          135  +**     );
    70    136   */
    71    137   
    72    138   #include "sqlite3ext.h"
    73    139   SQLITE_EXTENSION_INIT1
    74    140   #include <assert.h>
    75    141   #include <string.h>
          142  +#include <stdlib.h>
    76    143   
    77    144   #ifndef SQLITE_OMIT_VIRTUALTABLE
    78    145   
    79    146   /*
    80    147   ** Largest and smallest possible 64-bit signed integers. These macros
    81    148   ** copied from sqliteInt.h.
    82    149   */
................................................................................
   124    191     char *zDb;                      /* Database containing source table */
   125    192     char *zTab;                     /* Source table name */
   126    193     sqlite3_int64 iMin;             /* Minimum rowid */
   127    194     sqlite3_int64 iMax;             /* Maximum rowid */
   128    195   
   129    196     /* Fields used by swarmvtab only */
   130    197     char *zFile;                    /* Database file containing table zTab */
          198  +  char *zContext;                 /* Context string, if any */
   131    199     int nUser;                      /* Current number of users */
   132    200     sqlite3 *db;                    /* Database handle */
   133    201     UnionSrc *pNextClosable;        /* Next in list of closable sources */
   134    202   };
   135    203   
   136    204   /*
   137    205   ** Virtual table  type for union vtab.
................................................................................
   141    209     sqlite3 *db;                    /* Database handle */
   142    210     int bSwarm;                     /* 1 for "swarmvtab", 0 for "unionvtab" */
   143    211     int iPK;                        /* INTEGER PRIMARY KEY column, or -1 */
   144    212     int nSrc;                       /* Number of elements in the aSrc[] array */
   145    213     UnionSrc *aSrc;                 /* Array of source tables, sorted by rowid */
   146    214   
   147    215     /* Used by swarmvtab only */
          216  +  int bHasContext;                /* Has context strings */
   148    217     char *zSourceStr;               /* Expected unionSourceToStr() value */
   149         -  char *zNotFoundCallback;        /* UDF to invoke if file not found on open */
          218  +  sqlite3_stmt *pNotFound;        /* UDF to invoke if file not found on open */
          219  +  sqlite3_stmt *pOpenClose;       /* UDF to invoke on open and close */
          220  +
   150    221     UnionSrc *pClosable;            /* First in list of closable sources */
   151    222     int nOpen;                      /* Current number of open sources */
   152    223     int nMaxOpen;                   /* Maximum number of open sources */
   153    224   };
   154    225   
   155    226   /*
   156    227   ** Virtual table cursor type for union vtab.
................................................................................
   346    417     if( *pRc==SQLITE_OK ){
   347    418       *pRc = rc;
   348    419       if( rc ){
   349    420         *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db));
   350    421       }
   351    422     }
   352    423   }
          424  +
          425  +/*
          426  +** If an "openclose" UDF was supplied when this virtual table was created,
          427  +** invoke it now. The first argument passed is the name of the database
          428  +** file for source pSrc. The second is integer value bClose.
          429  +**
          430  +** If successful, return SQLITE_OK. Otherwise an SQLite error code. In this
          431  +** case if argument pzErr is not NULL, also set (*pzErr) to an English
          432  +** language error message. The caller is responsible for eventually freeing 
          433  +** any error message using sqlite3_free().
          434  +*/
          435  +static int unionInvokeOpenClose(
          436  +  UnionTab *pTab, 
          437  +  UnionSrc *pSrc, 
          438  +  int bClose,
          439  +  char **pzErr
          440  +){
          441  +  int rc = SQLITE_OK;
          442  +  if( pTab->pOpenClose ){
          443  +    sqlite3_bind_text(pTab->pOpenClose, 1, pSrc->zFile, -1, SQLITE_STATIC);
          444  +    if( pTab->bHasContext ){
          445  +      sqlite3_bind_text(pTab->pOpenClose, 2, pSrc->zContext, -1, SQLITE_STATIC);
          446  +    }
          447  +    sqlite3_bind_int(pTab->pOpenClose, 2+pTab->bHasContext, bClose);
          448  +    sqlite3_step(pTab->pOpenClose);
          449  +    if( SQLITE_OK!=(rc = sqlite3_reset(pTab->pOpenClose)) ){
          450  +      if( pzErr ){
          451  +        *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db));
          452  +      }
          453  +    }
          454  +  }
          455  +  return rc;
          456  +}
   353    457   
   354    458   /*
   355    459   ** This function is a no-op for unionvtab. For swarmvtab, it attempts to
   356    460   ** close open database files until at most nMax are open. An SQLite error
   357    461   ** code is returned if an error occurs, or SQLITE_OK otherwise.
   358    462   */
   359    463   static void unionCloseSources(UnionTab *pTab, int nMax){
   360    464     while( pTab->pClosable && pTab->nOpen>nMax ){
          465  +    UnionSrc *p;
   361    466       UnionSrc **pp;
   362    467       for(pp=&pTab->pClosable; (*pp)->pNextClosable; pp=&(*pp)->pNextClosable);
   363         -    assert( (*pp)->db );
   364         -    sqlite3_close((*pp)->db);
   365         -    (*pp)->db = 0;
          468  +    p = *pp;
          469  +    assert( p->db );
          470  +    sqlite3_close(p->db);
          471  +    p->db = 0;
   366    472       *pp = 0;
   367    473       pTab->nOpen--;
          474  +    unionInvokeOpenClose(pTab, p, 1, 0);
   368    475     }
   369    476   }
   370    477   
   371    478   /*
   372    479   ** xDisconnect method.
   373    480   */
   374    481   static int unionDisconnect(sqlite3_vtab *pVtab){
   375    482     if( pVtab ){
   376    483       UnionTab *pTab = (UnionTab*)pVtab;
   377    484       int i;
   378    485       for(i=0; i<pTab->nSrc; i++){
   379    486         UnionSrc *pSrc = &pTab->aSrc[i];
          487  +      if( pSrc->db ){
          488  +        unionInvokeOpenClose(pTab, pSrc, 1, 0);
          489  +      }
   380    490         sqlite3_free(pSrc->zDb);
   381    491         sqlite3_free(pSrc->zTab);
   382    492         sqlite3_free(pSrc->zFile);
          493  +      sqlite3_free(pSrc->zContext);
   383    494         sqlite3_close(pSrc->db);
   384    495       }
          496  +    sqlite3_finalize(pTab->pNotFound);
          497  +    sqlite3_finalize(pTab->pOpenClose);
   385    498       sqlite3_free(pTab->zSourceStr);
   386         -    sqlite3_free(pTab->zNotFoundCallback);
   387    499       sqlite3_free(pTab->aSrc);
   388    500       sqlite3_free(pTab);
   389    501     }
   390    502     return SQLITE_OK;
   391    503   }
   392    504   
   393    505   /*
................................................................................
   492    604       sqlite3_free(z);
   493    605     }
   494    606     sqlite3_free(z0);
   495    607   
   496    608     return rc;
   497    609   }
   498    610   
   499         -
   500    611   /*
   501    612   ** Try to open the swarmvtab database.  If initially unable, invoke the
   502    613   ** not-found callback UDF and then try again.
   503    614   */
   504    615   static int unionOpenDatabaseInner(UnionTab *pTab, UnionSrc *pSrc, char **pzErr){
   505         -  int rc = SQLITE_OK;
   506         -  static const int openFlags = 
   507         -       SQLITE_OPEN_READONLY | SQLITE_OPEN_URI;
          616  +  static const int openFlags = SQLITE_OPEN_READONLY | SQLITE_OPEN_URI;
          617  +  int rc;
          618  +
          619  +  rc = unionInvokeOpenClose(pTab, pSrc, 0, pzErr);
          620  +  if( rc!=SQLITE_OK ) return rc;
          621  +
   508    622     rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0);
   509    623     if( rc==SQLITE_OK ) return rc;
   510         -  if( pTab->zNotFoundCallback ){
   511         -    char *zSql = sqlite3_mprintf("SELECT \"%w\"(%Q);",
   512         -                    pTab->zNotFoundCallback, pSrc->zFile);
          624  +  if( pTab->pNotFound ){
   513    625       sqlite3_close(pSrc->db);
   514    626       pSrc->db = 0;
   515         -    if( zSql==0 ){
   516         -      *pzErr = sqlite3_mprintf("out of memory");
   517         -      return SQLITE_NOMEM;
          627  +    sqlite3_bind_text(pTab->pNotFound, 1, pSrc->zFile, -1, SQLITE_STATIC);
          628  +    if( pTab->bHasContext ){
          629  +      sqlite3_bind_text(pTab->pNotFound, 2, pSrc->zContext, -1, SQLITE_STATIC);
   518    630       }
   519         -    rc = sqlite3_exec(pTab->db, zSql, 0, 0, pzErr);
   520         -    sqlite3_free(zSql);
   521         -    if( rc ) return rc;
          631  +    sqlite3_step(pTab->pNotFound);
          632  +    if( SQLITE_OK!=(rc = sqlite3_reset(pTab->pNotFound)) ){
          633  +      *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db));
          634  +      return rc;
          635  +    }
   522    636       rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0);
   523    637     }
   524    638     if( rc!=SQLITE_OK ){
   525    639       *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pSrc->db));
   526    640     }
   527    641     return rc;
   528    642   }
................................................................................
   568    682       if( rc==SQLITE_OK ){
   569    683         pSrc->pNextClosable = pTab->pClosable;
   570    684         pTab->pClosable = pSrc;
   571    685         pTab->nOpen++;
   572    686       }else{
   573    687         sqlite3_close(pSrc->db);
   574    688         pSrc->db = 0;
          689  +      unionInvokeOpenClose(pTab, pSrc, 1, 0);
   575    690       }
   576    691     }
   577    692   
   578    693     return rc;
   579    694   }
   580    695   
   581    696   
................................................................................
   622    737           pTab->pClosable = pSrc;
   623    738         }
   624    739         unionCloseSources(pTab, pTab->nMaxOpen);
   625    740       }
   626    741     }
   627    742     return rc;
   628    743   }
          744  +
          745  +/* 
          746  +** Return true if the argument is a space, tab, CR or LF character.
          747  +*/
          748  +static int union_isspace(char c){
          749  +  return (c==' ' || c=='\n' || c=='\r' || c=='\t');
          750  +}
          751  +
          752  +/* 
          753  +** Return true if the argument is an alphanumeric character in the 
          754  +** ASCII range.
          755  +*/
          756  +static int union_isidchar(char c){
          757  +  return ((c>='a' && c<='z') || (c>='A' && c<'Z') || (c>='0' && c<='9'));
          758  +}
          759  +
          760  +/*
          761  +** This function is called to handle all arguments following the first 
          762  +** (the SQL statement) passed to a swarmvtab (not unionvtab) CREATE 
          763  +** VIRTUAL TABLE statement. It may bind parameters to the SQL statement 
          764  +** or configure members of the UnionTab object passed as the second
          765  +** argument.
          766  +**
          767  +** Refer to header comments at the top of this file for a description
          768  +** of the arguments parsed.
          769  +**
          770  +** This function is a no-op if *pRc is other than SQLITE_OK when it is
          771  +** called. Otherwise, if an error occurs, *pRc is set to an SQLite error
          772  +** code. In this case *pzErr may be set to point to a buffer containing
          773  +** an English language error message. It is the responsibility of the 
          774  +** caller to eventually free the buffer using sqlite3_free().
          775  +*/
          776  +static void unionConfigureVtab(
          777  +  int *pRc,                       /* IN/OUT: Error code */
          778  +  UnionTab *pTab,                 /* Table to configure */
          779  +  sqlite3_stmt *pStmt,            /* SQL statement to find sources */
          780  +  int nArg,                       /* Number of entries in azArg[] array */
          781  +  const char * const *azArg,      /* Array of arguments to consider */
          782  +  char **pzErr                    /* OUT: Error message */
          783  +){
          784  +  int rc = *pRc;
          785  +  int i;
          786  +  if( rc==SQLITE_OK ){
          787  +    pTab->bHasContext = (sqlite3_column_count(pStmt)>4);
          788  +  }
          789  +  for(i=0; rc==SQLITE_OK && i<nArg; i++){
          790  +    char *zArg = unionStrdup(&rc, azArg[i]);
          791  +    if( zArg ){
          792  +      int nOpt = 0;               /* Size of option name in bytes */
          793  +      char *zOpt;                 /* Pointer to option name */
          794  +      char *zVal;                 /* Pointer to value */
          795  +
          796  +      unionDequote(zArg);
          797  +      zOpt = zArg;
          798  +      while( union_isspace(*zOpt) ) zOpt++;
          799  +      zVal = zOpt;
          800  +      if( *zVal==':' ) zVal++;
          801  +      while( union_isidchar(*zVal) ) zVal++;
          802  +      nOpt = zVal-zOpt;
          803  +
          804  +      while( union_isspace(*zVal) ) zVal++;
          805  +      if( *zVal=='=' ){
          806  +        zOpt[nOpt] = '\0';
          807  +        zVal++;
          808  +        while( union_isspace(*zVal) ) zVal++;
          809  +        zVal = unionStrdup(&rc, zVal);
          810  +        if( zVal ){
          811  +          unionDequote(zVal);
          812  +          if( zOpt[0]==':' ){
          813  +            /* A value to bind to the SQL statement */
          814  +            int iParam = sqlite3_bind_parameter_index(pStmt, zOpt);
          815  +            if( iParam==0 ){
          816  +              *pzErr = sqlite3_mprintf(
          817  +                  "swarmvtab: no such SQL parameter: %s", zOpt
          818  +              );
          819  +              rc = SQLITE_ERROR;
          820  +            }else{
          821  +              rc = sqlite3_bind_text(pStmt, iParam, zVal, -1, SQLITE_TRANSIENT);
          822  +            }
          823  +          }else if( nOpt==7 && 0==sqlite3_strnicmp(zOpt, "maxopen", 7) ){
          824  +            pTab->nMaxOpen = atoi(zVal);
          825  +            if( pTab->nMaxOpen<=0 ){
          826  +              *pzErr = sqlite3_mprintf("swarmvtab: illegal maxopen value");
          827  +              rc = SQLITE_ERROR;
          828  +            }
          829  +          }else if( nOpt==7 && 0==sqlite3_strnicmp(zOpt, "missing", 7) ){
          830  +            if( pTab->pNotFound ){
          831  +              *pzErr = sqlite3_mprintf(
          832  +                  "swarmvtab: duplicate \"missing\" option");
          833  +              rc = SQLITE_ERROR;
          834  +            }else{
          835  +              pTab->pNotFound = unionPreparePrintf(&rc, pzErr, pTab->db,
          836  +                  "SELECT \"%w\"(?%s)", zVal, pTab->bHasContext ? ",?" : ""
          837  +              );
          838  +            }
          839  +          }else if( nOpt==9 && 0==sqlite3_strnicmp(zOpt, "openclose", 9) ){
          840  +            if( pTab->pOpenClose ){
          841  +              *pzErr = sqlite3_mprintf(
          842  +                  "swarmvtab: duplicate \"openclose\" option");
          843  +              rc = SQLITE_ERROR;
          844  +            }else{
          845  +              pTab->pOpenClose = unionPreparePrintf(&rc, pzErr, pTab->db,
          846  +                  "SELECT \"%w\"(?,?%s)", zVal, pTab->bHasContext ? ",?" : ""
          847  +              );
          848  +            }
          849  +          }else{
          850  +            *pzErr = sqlite3_mprintf("swarmvtab: unrecognized option: %s",zOpt);
          851  +            rc = SQLITE_ERROR;
          852  +          }
          853  +          sqlite3_free(zVal);
          854  +        }
          855  +      }else{
          856  +        if( i==0 && nArg==1 ){
          857  +          pTab->pNotFound = unionPreparePrintf(&rc, pzErr, pTab->db,
          858  +              "SELECT \"%w\"(?)", zArg
          859  +          );
          860  +        }else{
          861  +          *pzErr = sqlite3_mprintf( "swarmvtab: parse error: %s", azArg[i]);
          862  +          rc = SQLITE_ERROR;
          863  +        }
          864  +      }
          865  +      sqlite3_free(zArg);
          866  +    }
          867  +  }
          868  +  *pRc = rc;
          869  +}
   629    870   
   630    871   /* 
   631    872   ** xConnect/xCreate method.
   632    873   **
   633    874   ** The argv[] array contains the following:
   634    875   **
   635    876   **   argv[0]   -> module name  ("unionvtab" or "swarmvtab")
................................................................................
   650    891     int bSwarm = (pAux==0 ? 0 : 1);
   651    892     const char *zVtab = (bSwarm ? "swarmvtab" : "unionvtab");
   652    893   
   653    894     if( sqlite3_stricmp("temp", argv[1]) ){
   654    895       /* unionvtab tables may only be created in the temp schema */
   655    896       *pzErr = sqlite3_mprintf("%s tables must be created in TEMP schema", zVtab);
   656    897       rc = SQLITE_ERROR;
   657         -  }else if( argc!=4 && argc!=5 ){
          898  +  }else if( argc<4 || (argc>4 && bSwarm==0) ){
   658    899       *pzErr = sqlite3_mprintf("wrong number of arguments for %s", zVtab);
   659    900       rc = SQLITE_ERROR;
   660    901     }else{
   661    902       int nAlloc = 0;               /* Allocated size of pTab->aSrc[] */
   662    903       sqlite3_stmt *pStmt = 0;      /* Argument statement */
   663    904       char *zArg = unionStrdup(&rc, argv[3]);      /* Copy of argument to CVT */
   664    905   
................................................................................
   669    910       unionDequote(zArg);
   670    911       pStmt = unionPreparePrintf(&rc, pzErr, db, 
   671    912           "SELECT * FROM (%z) ORDER BY 3", zArg
   672    913       );
   673    914   
   674    915       /* Allocate the UnionTab structure */
   675    916       pTab = unionMalloc(&rc, sizeof(UnionTab));
          917  +    if( pTab ){
          918  +      assert( rc==SQLITE_OK );
          919  +      pTab->db = db;
          920  +      pTab->bSwarm = bSwarm;
          921  +      pTab->nMaxOpen = SWARMVTAB_MAX_OPEN;
          922  +    }
          923  +
          924  +    /* Parse other CVT arguments, if any */
          925  +    if( bSwarm ){
          926  +      unionConfigureVtab(&rc, pTab, pStmt, argc-4, &argv[4], pzErr);
          927  +    }
   676    928   
   677    929       /* Iterate through the rows returned by the SQL statement specified
   678    930       ** as an argument to the CREATE VIRTUAL TABLE statement. */
   679    931       while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
   680    932         const char *zDb = (const char*)sqlite3_column_text(pStmt, 0);
   681    933         const char *zTab = (const char*)sqlite3_column_text(pStmt, 1);
   682    934         sqlite3_int64 iMin = sqlite3_column_int64(pStmt, 2);
................................................................................
   711    963           pSrc->iMin = iMin;
   712    964           pSrc->iMax = iMax;
   713    965           if( bSwarm ){
   714    966             pSrc->zFile = unionStrdup(&rc, zDb);
   715    967           }else{
   716    968             pSrc->zDb = unionStrdup(&rc, zDb);
   717    969           }
          970  +        if( pTab->bHasContext ){
          971  +          const char *zContext = (const char*)sqlite3_column_text(pStmt, 4);
          972  +          pSrc->zContext = unionStrdup(&rc, zContext);
          973  +        }
   718    974         }
   719    975       }
   720    976       unionFinalize(&rc, pStmt, pzErr);
   721    977       pStmt = 0;
   722    978   
   723         -    /* Capture the not-found callback UDF name */
   724         -    if( rc==SQLITE_OK && argc>=5 ){
   725         -      pTab->zNotFoundCallback = unionStrdup(&rc, argv[4]);
   726         -      unionDequote(pTab->zNotFoundCallback);
   727         -    }
   728         -
   729    979       /* It is an error if the SELECT statement returned zero rows. If only
   730    980       ** because there is no way to determine the schema of the virtual 
   731    981       ** table in this case.  */
   732    982       if( rc==SQLITE_OK && pTab->nSrc==0 ){
   733    983         *pzErr = sqlite3_mprintf("no source tables configured");
   734    984         rc = SQLITE_ERROR;
   735    985       }
   736    986   
   737    987       /* For unionvtab, verify that all source tables exist and have 
   738    988       ** compatible schemas. For swarmvtab, attach the first database and
   739    989       ** check that the first table is a rowid table only.  */
   740    990       if( rc==SQLITE_OK ){
   741         -      pTab->db = db;
   742         -      pTab->bSwarm = bSwarm;
   743         -      pTab->nMaxOpen = SWARMVTAB_MAX_OPEN;
   744    991         if( bSwarm ){
   745    992           rc = unionOpenDatabase(pTab, 0, pzErr);
   746    993         }else{
   747    994           rc = unionSourceCheck(pTab, pzErr);
   748    995         }
   749    996       }
   750    997   

Changes to ext/rtree/rtree.c.

  2017   2017     Rtree *pRtree,               /* Rtree table */
  2018   2018     RtreeCell *pCell,            /* Cell to insert into rtree */
  2019   2019     int iHeight,                 /* Height of sub-tree rooted at pCell */
  2020   2020     RtreeNode **ppLeaf           /* OUT: Selected leaf page */
  2021   2021   ){
  2022   2022     int rc;
  2023   2023     int ii;
  2024         -  RtreeNode *pNode;
         2024  +  RtreeNode *pNode = 0;
  2025   2025     rc = nodeAcquire(pRtree, 1, 0, &pNode);
  2026   2026   
  2027   2027     for(ii=0; rc==SQLITE_OK && ii<(pRtree->iDepth-iHeight); ii++){
  2028   2028       int iCell;
  2029   2029       sqlite3_int64 iBest = 0;
  2030   2030   
  2031   2031       RtreeDValue fMinGrowth = RTREE_ZERO;
................................................................................
  2892   2892     **
  2893   2893     ** This is equivalent to copying the contents of the child into
  2894   2894     ** the root node (the operation that Gutman's paper says to perform 
  2895   2895     ** in this scenario).
  2896   2896     */
  2897   2897     if( rc==SQLITE_OK && pRtree->iDepth>0 && NCELL(pRoot)==1 ){
  2898   2898       int rc2;
  2899         -    RtreeNode *pChild;
         2899  +    RtreeNode *pChild = 0;
  2900   2900       i64 iChild = nodeGetRowid(pRtree, pRoot, 0);
  2901   2901       rc = nodeAcquire(pRtree, iChild, pRoot, &pChild);
  2902   2902       if( rc==SQLITE_OK ){
  2903   2903         rc = removeNode(pRtree, pChild, pRtree->iDepth-1);
  2904   2904       }
  2905   2905       rc2 = nodeRelease(pRtree, pChild);
  2906   2906       if( rc==SQLITE_OK ) rc = rc2;

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 \
................................................................................
   687    689   	./mkkeywordhash >keywordhash.h
   688    690   
   689    691   # Source files that go into making shell.c
   690    692   SHELL_SRC = \
   691    693   	$(TOP)/src/shell.c.in \
   692    694   	$(TOP)/ext/misc/shathree.c \
   693    695   	$(TOP)/ext/misc/fileio.c \
   694         -	$(TOP)/ext/misc/completion.c
          696  +	$(TOP)/ext/misc/completion.c \
          697  +	$(TOP)/ext/expert/sqlite3expert.c \
          698  +	$(TOP)/ext/expert/sqlite3expert.h
   695    699   
   696    700   shell.c:	$(SHELL_SRC) $(TOP)/tool/mkshellc.tcl
   697    701   	tclsh $(TOP)/tool/mkshellc.tcl >shell.c
   698    702   
   699    703   
   700    704   
   701    705   # Rules to build the extension objects.
................................................................................
   812    816   
   813    817   sqltclsh.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/sqltclsh.tcl $(TOP)/ext/misc/appendvfs.c $(TOP)/tool/mkccode.tcl
   814    818   	tclsh $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqltclsh.c.in >sqltclsh.c
   815    819   
   816    820   sqltclsh$(EXE): sqltclsh.c
   817    821   	$(TCCX) $(TCL_FLAGS) sqltclsh.c -o $@ $(LIBTCL) $(THREADLIB) 
   818    822   
          823  +sqlite3_expert$(EXE): $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c
          824  +	$(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)
          825  +
   819    826   CHECKER_DEPS =\
   820    827     $(TOP)/tool/mkccode.tcl \
   821    828     sqlite3.c \
   822    829     $(TOP)/src/tclsqlite.c \
   823    830     $(TOP)/ext/repair/sqlite3_checker.tcl \
   824    831     $(TOP)/ext/repair/checkindex.c \
   825    832     $(TOP)/ext/repair/checkfreelist.c \
................................................................................
  1038   1045   	rm -f wordcount wordcount.exe
  1039   1046   	rm -f rbu rbu.exe
  1040   1047   	rm -f srcck1 srcck1.exe
  1041   1048   	rm -f sqlite3.c sqlite3-*.c fts?amal.c tclsqlite3.c
  1042   1049   	rm -f sqlite3rc.h
  1043   1050   	rm -f shell.c sqlite3ext.h
  1044   1051   	rm -f sqlite3_analyzer sqlite3_analyzer.exe sqlite3_analyzer.c
         1052  +	rm -f sqlite3_expert sqlite3_expert.exe 
  1045   1053   	rm -f sqlite-*-output.vsix
  1046   1054   	rm -f mptester mptester.exe
  1047   1055   	rm -f fuzzershell fuzzershell.exe
  1048   1056   	rm -f fuzzcheck fuzzcheck.exe
  1049   1057   	rm -f sqldiff sqldiff.exe
  1050   1058   	rm -f fts5.* fts5parse.*
  1051   1059   	rm -f lsm.h lsm1.c

Changes to src/build.c.

  1961   1961         assert(pParse->nTab==1);
  1962   1962         sqlite3MayAbort(pParse);
  1963   1963         sqlite3VdbeAddOp3(v, OP_OpenWrite, 1, pParse->regRoot, iDb);
  1964   1964         sqlite3VdbeChangeP5(v, OPFLAG_P2ISREG);
  1965   1965         pParse->nTab = 2;
  1966   1966         addrTop = sqlite3VdbeCurrentAddr(v) + 1;
  1967   1967         sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop);
  1968         -      sqlite3SelectDestInit(&dest, SRT_Coroutine, regYield);
  1969         -      sqlite3Select(pParse, pSelect, &dest);
  1970         -      sqlite3VdbeEndCoroutine(v, regYield);
  1971         -      sqlite3VdbeJumpHere(v, addrTop - 1);
  1972         -      if( pParse->nErr ) return;
  1973   1968         pSelTab = sqlite3ResultSetOfSelect(pParse, pSelect);
  1974   1969         if( pSelTab==0 ) return;
  1975   1970         assert( p->aCol==0 );
  1976   1971         p->nCol = pSelTab->nCol;
  1977   1972         p->aCol = pSelTab->aCol;
  1978   1973         pSelTab->nCol = 0;
  1979   1974         pSelTab->aCol = 0;
  1980   1975         sqlite3DeleteTable(db, pSelTab);
         1976  +      sqlite3SelectDestInit(&dest, SRT_Coroutine, regYield);
         1977  +      sqlite3Select(pParse, pSelect, &dest);
         1978  +      sqlite3VdbeEndCoroutine(v, regYield);
         1979  +      sqlite3VdbeJumpHere(v, addrTop - 1);
  1981   1980         addrInsLoop = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm);
  1982   1981         VdbeCoverage(v);
  1983   1982         sqlite3VdbeAddOp3(v, OP_MakeRecord, dest.iSdst, dest.nSdst, regRec);
  1984   1983         sqlite3TableAffinity(v, p, 0);
  1985   1984         sqlite3VdbeAddOp2(v, OP_NewRowid, 1, regRowid);
  1986   1985         sqlite3VdbeAddOp3(v, OP_Insert, 1, regRec, regRowid);
  1987   1986         sqlite3VdbeGoto(v, addrInsLoop);

Changes to src/loadext.c.

   492    492   
   493    493     zEntry = zProc ? zProc : "sqlite3_extension_init";
   494    494   
   495    495     handle = sqlite3OsDlOpen(pVfs, zFile);
   496    496   #if SQLITE_OS_UNIX || SQLITE_OS_WIN
   497    497     for(ii=0; ii<ArraySize(azEndings) && handle==0; ii++){
   498    498       char *zAltFile = sqlite3_mprintf("%s.%s", zFile, azEndings[ii]);
          499  +    int bExists = 0;
   499    500       if( zAltFile==0 ) return SQLITE_NOMEM_BKPT;
   500         -    handle = sqlite3OsDlOpen(pVfs, zAltFile);
          501  +    sqlite3OsAccess(pVfs, zAltFile, SQLITE_ACCESS_EXISTS, &bExists);
          502  +    if( bExists )  handle = sqlite3OsDlOpen(pVfs, zAltFile);
   501    503       sqlite3_free(zAltFile);
   502    504     }
   503    505   #endif
   504    506     if( handle==0 ){
   505    507       if( pzErrMsg ){
   506    508         *pzErrMsg = zErrmsg = sqlite3_malloc64(nMsg);
   507    509         if( zErrmsg ){

Changes to src/main.c.

   813    813         } aFlagOp[] = {
   814    814           { SQLITE_DBCONFIG_ENABLE_FKEY,           SQLITE_ForeignKeys    },
   815    815           { SQLITE_DBCONFIG_ENABLE_TRIGGER,        SQLITE_EnableTrigger  },
   816    816           { SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, SQLITE_Fts3Tokenizer  },
   817    817           { SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, SQLITE_LoadExtension  },
   818    818           { SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE,      SQLITE_NoCkptOnClose  },
   819    819           { SQLITE_DBCONFIG_ENABLE_QPSG,           SQLITE_EnableQPSG     },
          820  +        { SQLITE_DBCONFIG_TRIGGER_EQP,           SQLITE_TriggerEQP     },
   820    821         };
   821    822         unsigned int i;
   822    823         rc = SQLITE_ERROR; /* IMP: R-42790-23372 */
   823    824         for(i=0; i<ArraySize(aFlagOp); i++){
   824    825           if( aFlagOp[i].op==op ){
   825    826             int onoff = va_arg(ap, int);
   826    827             int *pRes = va_arg(ap, int*);

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/select.c.

  1377   1377     char const *zOrigDb = 0;
  1378   1378     char const *zOrigTab = 0;
  1379   1379     char const *zOrigCol = 0;
  1380   1380   #endif
  1381   1381   
  1382   1382     assert( pExpr!=0 );
  1383   1383     assert( pNC->pSrcList!=0 );
         1384  +  assert( pExpr->op!=TK_AGG_COLUMN );  /* This routine runes before aggregates
         1385  +                                       ** are processed */
  1384   1386     switch( pExpr->op ){
  1385         -    case TK_AGG_COLUMN:
  1386   1387       case TK_COLUMN: {
  1387   1388         /* The expression is a column. Locate the table the column is being
  1388   1389         ** extracted from in NameContext.pSrcList. This table may be real
  1389   1390         ** database table or a subquery.
  1390   1391         */
  1391   1392         Table *pTab = 0;            /* Table structure column is extracted from */
  1392   1393         Select *pS = 0;             /* Select the column is extracted from */
  1393   1394         int iCol = pExpr->iColumn;  /* Index of column in pTab */
  1394         -      testcase( pExpr->op==TK_AGG_COLUMN );
  1395         -      testcase( pExpr->op==TK_COLUMN );
  1396   1395         while( pNC && !pTab ){
  1397   1396           SrcList *pTabList = pNC->pSrcList;
  1398   1397           for(j=0;j<pTabList->nSrc && pTabList->a[j].iCursor!=pExpr->iTable;j++);
  1399   1398           if( j<pTabList->nSrc ){
  1400   1399             pTab = pTabList->a[j].pTab;
  1401   1400             pS = pTabList->a[j].pSelect;
  1402   1401           }else{
................................................................................
  1592   1591       return;
  1593   1592     }
  1594   1593   #endif
  1595   1594   
  1596   1595     if( pParse->colNamesSet || db->mallocFailed ) return;
  1597   1596     /* Column names are determined by the left-most term of a compound select */
  1598   1597     while( pSelect->pPrior ) pSelect = pSelect->pPrior;
         1598  +  SELECTTRACE(1,pParse,pSelect,("generating column names\n"));
  1599   1599     pTabList = pSelect->pSrc;
  1600   1600     pEList = pSelect->pEList;
  1601   1601     assert( v!=0 );
  1602   1602     assert( pTabList!=0 );
  1603   1603     pParse->colNamesSet = 1;
  1604   1604     fullName = (db->flags & SQLITE_FullColNames)!=0;
  1605   1605     srcName = (db->flags & SQLITE_ShortColNames)!=0 || fullName;
................................................................................
  1700   1700         /* If the column contains an "AS <name>" phrase, use <name> as the name */
  1701   1701       }else{
  1702   1702         Expr *pColExpr = sqlite3ExprSkipCollate(pEList->a[i].pExpr);
  1703   1703         while( pColExpr->op==TK_DOT ){
  1704   1704           pColExpr = pColExpr->pRight;
  1705   1705           assert( pColExpr!=0 );
  1706   1706         }
  1707         -      if( (pColExpr->op==TK_COLUMN || pColExpr->op==TK_AGG_COLUMN)
  1708         -       && pColExpr->pTab!=0 
  1709         -      ){
         1707  +      assert( pColExpr->op!=TK_AGG_COLUMN );
         1708  +      if( pColExpr->op==TK_COLUMN ){
  1710   1709           /* For columns use the column name name */
  1711   1710           int iCol = pColExpr->iColumn;
  1712   1711           Table *pTab = pColExpr->pTab;
         1712  +        assert( pTab!=0 );
  1713   1713           if( iCol<0 ) iCol = pTab->iPKey;
  1714   1714           zName = iCol>=0 ? pTab->aCol[iCol].zName : "rowid";
  1715   1715         }else if( pColExpr->op==TK_ID ){
  1716   1716           assert( !ExprHasProperty(pColExpr, EP_IntValue) );
  1717   1717           zName = pColExpr->u.zToken;
  1718   1718         }else{
  1719   1719           /* Use the original text of the column expression as its name */

Changes to src/shell.c.in.

   792    792   */
   793    793   #define SQLITE_EXTENSION_INIT1
   794    794   #define SQLITE_EXTENSION_INIT2(X) (void)(X)
   795    795   
   796    796   INCLUDE ../ext/misc/shathree.c
   797    797   INCLUDE ../ext/misc/fileio.c
   798    798   INCLUDE ../ext/misc/completion.c
          799  +INCLUDE ../ext/expert/sqlite3expert.h
          800  +INCLUDE ../ext/expert/sqlite3expert.c
   799    801   
   800    802   #if defined(SQLITE_ENABLE_SESSION)
   801    803   /*
   802    804   ** State information for a single open session
   803    805   */
   804    806   typedef struct OpenSession OpenSession;
   805    807   struct OpenSession {
................................................................................
   817    819   typedef struct SavedModeInfo SavedModeInfo;
   818    820   struct SavedModeInfo {
   819    821     int valid;          /* Is there legit data in here? */
   820    822     int mode;           /* Mode prior to ".explain on" */
   821    823     int showHeader;     /* The ".header" setting prior to ".explain on" */
   822    824     int colWidth[100];  /* Column widths prior to ".explain on" */
   823    825   };
          826  +
          827  +typedef struct ExpertInfo ExpertInfo;
          828  +struct ExpertInfo {
          829  +  sqlite3expert *pExpert;
          830  +  int bVerbose;
          831  +};
   824    832   
   825    833   /*
   826    834   ** State information about the database connection is contained in an
   827    835   ** instance of the following structure.
   828    836   */
   829    837   typedef struct ShellState ShellState;
   830    838   struct ShellState {
................................................................................
   862    870     int *aiIndent;         /* Array of indents used in MODE_Explain */
   863    871     int nIndent;           /* Size of array aiIndent[] */
   864    872     int iIndent;           /* Index of current op in aiIndent[] */
   865    873   #if defined(SQLITE_ENABLE_SESSION)
   866    874     int nSession;             /* Number of active sessions */
   867    875     OpenSession aSession[4];  /* Array of sessions.  [0] is in focus. */
   868    876   #endif
          877  +  ExpertInfo expert;        /* Valid if previous command was ".expert OPT..." */
   869    878   };
          879  +
          880  +/* Allowed values for ShellState.autoEQP
          881  +*/
          882  +#define AUTOEQP_off      0
          883  +#define AUTOEQP_on       1
          884  +#define AUTOEQP_trigger  2
          885  +#define AUTOEQP_full     3
   870    886   
   871    887   /*
   872    888   ** These are the allowed shellFlgs values
   873    889   */
   874    890   #define SHFLG_Pagecache      0x00000001 /* The --pagecache option is used */
   875    891   #define SHFLG_Lookaside      0x00000002 /* Lookaside memory is used */
   876    892   #define SHFLG_Backslash      0x00000004 /* The --backslash option is used */
................................................................................
  2244   2260       }else{
  2245   2261         do{
  2246   2262           rc = sqlite3_step(pStmt);
  2247   2263         } while( rc == SQLITE_ROW );
  2248   2264       }
  2249   2265     }
  2250   2266   }
         2267  +
         2268  +/*
         2269  +** This function is called to process SQL if the previous shell command
         2270  +** was ".expert". It passes the SQL in the second argument directly to
         2271  +** the sqlite3expert object.
         2272  +**
         2273  +** If successful, SQLITE_OK is returned. Otherwise, an SQLite error
         2274  +** code. In this case, (*pzErr) may be set to point to a buffer containing
         2275  +** an English language error message. It is the responsibility of the
         2276  +** caller to eventually free this buffer using sqlite3_free().
         2277  +*/
         2278  +static int expertHandleSQL(
         2279  +  ShellState *pState, 
         2280  +  const char *zSql, 
         2281  +  char **pzErr
         2282  +){
         2283  +  assert( pState->expert.pExpert );
         2284  +  assert( pzErr==0 || *pzErr==0 );
         2285  +  return sqlite3_expert_sql(pState->expert.pExpert, zSql, pzErr);
         2286  +}
         2287  +
         2288  +/*
         2289  +** This function is called either to silently clean up the object
         2290  +** created by the ".expert" command (if bCancel==1), or to generate a 
         2291  +** report from it and then clean it up (if bCancel==0).
         2292  +**
         2293  +** If successful, SQLITE_OK is returned. Otherwise, an SQLite error
         2294  +** code. In this case, (*pzErr) may be set to point to a buffer containing
         2295  +** an English language error message. It is the responsibility of the
         2296  +** caller to eventually free this buffer using sqlite3_free().
         2297  +*/
         2298  +static int expertFinish(
         2299  +  ShellState *pState,
         2300  +  int bCancel,
         2301  +  char **pzErr
         2302  +){
         2303  +  int rc = SQLITE_OK;
         2304  +  sqlite3expert *p = pState->expert.pExpert;
         2305  +  assert( p );
         2306  +  assert( bCancel || pzErr==0 || *pzErr==0 );
         2307  +  if( bCancel==0 ){
         2308  +    FILE *out = pState->out;
         2309  +    int bVerbose = pState->expert.bVerbose;
         2310  +
         2311  +    rc = sqlite3_expert_analyze(p, pzErr);
         2312  +    if( rc==SQLITE_OK ){
         2313  +      int nQuery = sqlite3_expert_count(p);
         2314  +      int i;
         2315  +
         2316  +      if( bVerbose ){
         2317  +        const char *zCand = sqlite3_expert_report(p,0,EXPERT_REPORT_CANDIDATES);
         2318  +        raw_printf(out, "-- Candidates -----------------------------\n");
         2319  +        raw_printf(out, "%s\n", zCand);
         2320  +      }
         2321  +      for(i=0; i<nQuery; i++){
         2322  +        const char *zSql = sqlite3_expert_report(p, i, EXPERT_REPORT_SQL);
         2323  +        const char *zIdx = sqlite3_expert_report(p, i, EXPERT_REPORT_INDEXES);
         2324  +        const char *zEQP = sqlite3_expert_report(p, i, EXPERT_REPORT_PLAN);
         2325  +        if( zIdx==0 ) zIdx = "(no new indexes)\n";
         2326  +        if( bVerbose ){
         2327  +          raw_printf(out, "-- Query %d --------------------------------\n",i+1);
         2328  +          raw_printf(out, "%s\n\n", zSql);
         2329  +        }
         2330  +        raw_printf(out, "%s\n", zIdx);
         2331  +        raw_printf(out, "%s\n", zEQP);
         2332  +      }
         2333  +    }
         2334  +  }
         2335  +  sqlite3_expert_destroy(p);
         2336  +  pState->expert.pExpert = 0;
         2337  +  return rc;
         2338  +}
         2339  +
  2251   2340   
  2252   2341   /*
  2253   2342   ** Execute a statement or set of statements.  Print
  2254   2343   ** any result rows/columns depending on the current mode
  2255   2344   ** set via the supplied callback.
  2256   2345   **
  2257   2346   ** This is very similar to SQLite's built-in sqlite3_exec()
................................................................................
  2270   2359     int rc = SQLITE_OK;             /* Return Code */
  2271   2360     int rc2;
  2272   2361     const char *zLeftover;          /* Tail of unprocessed SQL */
  2273   2362   
  2274   2363     if( pzErrMsg ){
  2275   2364       *pzErrMsg = NULL;
  2276   2365     }
         2366  +
         2367  +  if( pArg->expert.pExpert ){
         2368  +    rc = expertHandleSQL(pArg, zSql, pzErrMsg);
         2369  +    return expertFinish(pArg, (rc!=SQLITE_OK), pzErrMsg);
         2370  +  }
  2277   2371   
  2278   2372     while( zSql[0] && (SQLITE_OK == rc) ){
  2279   2373       static const char *zStmtSql;
  2280   2374       rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover);
  2281   2375       if( SQLITE_OK != rc ){
  2282   2376         if( pzErrMsg ){
  2283   2377           *pzErrMsg = save_err_msg(db);
................................................................................
  2304   2398           utf8_printf(pArg->out, "%s\n", zStmtSql ? zStmtSql : zSql);
  2305   2399         }
  2306   2400   
  2307   2401         /* Show the EXPLAIN QUERY PLAN if .eqp is on */
  2308   2402         if( pArg && pArg->autoEQP && sqlite3_strlike("EXPLAIN%",zStmtSql,0)!=0 ){
  2309   2403           sqlite3_stmt *pExplain;
  2310   2404           char *zEQP;
         2405  +        int triggerEQP = 0;
  2311   2406           disable_debug_trace_modes();
         2407  +        sqlite3_db_config(db, SQLITE_DBCONFIG_TRIGGER_EQP, -1, &triggerEQP);
         2408  +        if( pArg->autoEQP>=AUTOEQP_trigger ){
         2409  +          sqlite3_db_config(db, SQLITE_DBCONFIG_TRIGGER_EQP, 1, 0);
         2410  +        }
  2312   2411           zEQP = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zStmtSql);
  2313   2412           rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
  2314   2413           if( rc==SQLITE_OK ){
  2315   2414             while( sqlite3_step(pExplain)==SQLITE_ROW ){
  2316   2415               raw_printf(pArg->out,"--EQP-- %d,",sqlite3_column_int(pExplain, 0));
  2317   2416               raw_printf(pArg->out,"%d,", sqlite3_column_int(pExplain, 1));
  2318   2417               raw_printf(pArg->out,"%d,", sqlite3_column_int(pExplain, 2));
  2319   2418               utf8_printf(pArg->out,"%s\n", sqlite3_column_text(pExplain, 3));
  2320   2419             }
  2321   2420           }
  2322   2421           sqlite3_finalize(pExplain);
  2323   2422           sqlite3_free(zEQP);
  2324         -        if( pArg->autoEQP>=2 ){
         2423  +        if( pArg->autoEQP>=AUTOEQP_full ){
  2325   2424             /* Also do an EXPLAIN for ".eqp full" mode */
  2326   2425             zEQP = sqlite3_mprintf("EXPLAIN %s", zStmtSql);
  2327   2426             rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
  2328   2427             if( rc==SQLITE_OK ){
  2329   2428               pArg->cMode = MODE_Explain;
  2330   2429               explain_data_prepare(pArg, pExplain);
  2331   2430               exec_prepared_stmt(pArg, pExplain, xCallback);
  2332   2431               explain_data_delete(pArg);
  2333   2432             }
  2334   2433             sqlite3_finalize(pExplain);
  2335   2434             sqlite3_free(zEQP);
  2336   2435           }
         2436  +        sqlite3_db_config(db, SQLITE_DBCONFIG_TRIGGER_EQP, triggerEQP, 0);
  2337   2437           restore_debug_trace_modes();
  2338   2438         }
  2339   2439   
  2340   2440         if( pArg ){
  2341   2441           pArg->cMode = pArg->mode;
  2342   2442           if( pArg->autoExplain
  2343   2443            && sqlite3_column_count(pStmt)==8
................................................................................
  2688   2788     ".dbinfo ?DB?           Show status information about the database\n"
  2689   2789     ".dump ?TABLE? ...      Dump the database in an SQL text format\n"
  2690   2790     "                         If TABLE specified, only dump tables matching\n"
  2691   2791     "                         LIKE pattern TABLE.\n"
  2692   2792     ".echo on|off           Turn command echo on or off\n"
  2693   2793     ".eqp on|off|full       Enable or disable automatic EXPLAIN QUERY PLAN\n"
  2694   2794     ".exit                  Exit this program\n"
         2795  +  ".expert                EXPERIMENTAL. Suggest indexes for specified queries\n"
  2695   2796   /* Because explain mode comes on automatically now, the ".explain" mode
  2696   2797   ** is removed from the help screen.  It is still supported for legacy, however */
  2697   2798   /*".explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic\n"*/
  2698   2799     ".fullschema ?--indent? Show schema and the content of sqlite_stat tables\n"
  2699   2800     ".headers on|off        Turn display of headers on or off\n"
  2700   2801     ".help                  Show this message\n"
  2701   2802     ".import FILE TABLE     Import data from FILE into TABLE\n"
................................................................................
  4064   4165    usage:
  4065   4166     raw_printf(stderr, "Usage %s sub-command ?switches...?\n", azArg[0]);
  4066   4167     raw_printf(stderr, "Where sub-commands are:\n");
  4067   4168     raw_printf(stderr, "    fkey-indexes\n");
  4068   4169     return SQLITE_ERROR;
  4069   4170   }
  4070   4171   
         4172  +/*
         4173  +** Implementation of ".expert" dot command.
         4174  +*/
         4175  +static int expertDotCommand(
         4176  +  ShellState *pState,             /* Current shell tool state */
         4177  +  char **azArg,                   /* Array of arguments passed to dot command */
         4178  +  int nArg                        /* Number of entries in azArg[] */
         4179  +){
         4180  +  int rc = SQLITE_OK;
         4181  +  char *zErr = 0;
         4182  +  int i;
         4183  +  int iSample = 0;
         4184  +
         4185  +  assert( pState->expert.pExpert==0 );
         4186  +  memset(&pState->expert, 0, sizeof(ExpertInfo));
         4187  +
         4188  +  for(i=1; rc==SQLITE_OK && i<nArg; i++){
         4189  +    char *z = azArg[i];
         4190  +    int n;
         4191  +    if( z[0]=='-' && z[1]=='-' ) z++;
         4192  +    n = strlen(z);
         4193  +    if( n>=2 && 0==strncmp(z, "-verbose", n) ){
         4194  +      pState->expert.bVerbose = 1;
         4195  +    }
         4196  +    else if( n>=2 && 0==strncmp(z, "-sample", n) ){
         4197  +      if( i==(nArg-1) ){
         4198  +        raw_printf(stderr, "option requires an argument: %s\n", z);
         4199  +        rc = SQLITE_ERROR;
         4200  +      }else{
         4201  +        iSample = (int)integerValue(azArg[++i]);
         4202  +        if( iSample<0 || iSample>100 ){
         4203  +          raw_printf(stderr, "value out of range: %s\n", azArg[i]);
         4204  +          rc = SQLITE_ERROR;
         4205  +        }
         4206  +      }
         4207  +    }
         4208  +    else{
         4209  +      raw_printf(stderr, "unknown option: %s\n", z);
         4210  +      rc = SQLITE_ERROR;
         4211  +    }
         4212  +  }
         4213  +
         4214  +  if( rc==SQLITE_OK ){
         4215  +    pState->expert.pExpert = sqlite3_expert_new(pState->db, &zErr);
         4216  +    if( pState->expert.pExpert==0 ){
         4217  +      raw_printf(stderr, "sqlite3_expert_new: %s\n", zErr);
         4218  +      rc = SQLITE_ERROR;
         4219  +    }else{
         4220  +      sqlite3_expert_config(
         4221  +          pState->expert.pExpert, EXPERT_CONFIG_SAMPLE, iSample
         4222  +      );
         4223  +    }
         4224  +  }
         4225  +
         4226  +  return rc;
         4227  +}
         4228  +
         4229  +
  4071   4230   
  4072   4231   /*
  4073   4232   ** If an input line begins with "." then invoke this routine to
  4074   4233   ** process that line.
  4075   4234   **
  4076   4235   ** Return 1 on error, 2 to exit, and 0 otherwise.
  4077   4236   */
  4078   4237   static int do_meta_command(char *zLine, ShellState *p){
  4079   4238     int h = 1;
  4080   4239     int nArg = 0;
  4081   4240     int n, c;
  4082   4241     int rc = 0;
  4083   4242     char *azArg[50];
         4243  +
         4244  +  if( p->expert.pExpert ){
         4245  +    expertFinish(p, 1, 0);
         4246  +  }
  4084   4247   
  4085   4248     /* Parse the input line into tokens.
  4086   4249     */
  4087   4250     while( zLine[h] && nArg<ArraySize(azArg) ){
  4088   4251       while( IsSpace(zLine[h]) ){ h++; }
  4089   4252       if( zLine[h]==0 ) break;
  4090   4253       if( zLine[h]=='\'' || zLine[h]=='"' ){
................................................................................
  4393   4556         rc = 1;
  4394   4557       }
  4395   4558     }else
  4396   4559   
  4397   4560     if( c=='e' && strncmp(azArg[0], "eqp", n)==0 ){
  4398   4561       if( nArg==2 ){
  4399   4562         if( strcmp(azArg[1],"full")==0 ){
  4400         -        p->autoEQP = 2;
         4563  +        p->autoEQP = AUTOEQP_full;
         4564  +      }else if( strcmp(azArg[1],"trigger")==0 ){
         4565  +        p->autoEQP = AUTOEQP_trigger;
  4401   4566         }else{
  4402   4567           p->autoEQP = booleanValue(azArg[1]);
  4403   4568         }
  4404   4569       }else{
  4405         -      raw_printf(stderr, "Usage: .eqp on|off|full\n");
         4570  +      raw_printf(stderr, "Usage: .eqp off|on|trigger|full\n");
  4406   4571         rc = 1;
  4407   4572       }
  4408   4573     }else
  4409   4574   
  4410   4575     if( c=='e' && strncmp(azArg[0], "exit", n)==0 ){
  4411   4576       if( nArg>1 && (rc = (int)integerValue(azArg[1]))!=0 ) exit(rc);
  4412   4577       rc = 2;
................................................................................
  4431   4596         if( p->mode==MODE_Explain ) p->mode = p->normalMode;
  4432   4597         p->autoExplain = 0;
  4433   4598       }else if( val==99 ){
  4434   4599         if( p->mode==MODE_Explain ) p->mode = p->normalMode;
  4435   4600         p->autoExplain = 1;
  4436   4601       }
  4437   4602     }else
         4603  +
         4604  +  if( c=='e' && strncmp(azArg[0], "expert", n)==0 ){
         4605  +    open_db(p, 0);
         4606  +    expertDotCommand(p, azArg, nArg);
         4607  +  }else
  4438   4608   
  4439   4609     if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){
  4440   4610       ShellState data;
  4441   4611       char *zErrMsg = 0;
  4442   4612       int doStats = 0;
  4443   4613       memcpy(&data, p, sizeof(data));
  4444   4614       data.showHeader = 0;
................................................................................
  5746   5916       }
  5747   5917       x = system(zCmd);
  5748   5918       sqlite3_free(zCmd);
  5749   5919       if( x ) raw_printf(stderr, "System command returns %d\n", x);
  5750   5920     }else
  5751   5921   
  5752   5922     if( c=='s' && strncmp(azArg[0], "show", n)==0 ){
  5753         -    static const char *azBool[] = { "off", "on", "full", "unk" };
         5923  +    static const char *azBool[] = { "off", "on", "trigger", "full"};
  5754   5924       int i;
  5755   5925       if( nArg!=1 ){
  5756   5926         raw_printf(stderr, "Usage: .show\n");
  5757   5927         rc = 1;
  5758   5928         goto meta_command_exit;
  5759   5929       }
  5760   5930       utf8_printf(p->out, "%12.12s: %s\n","echo",
................................................................................
  6898   7068       }else if( strcmp(z,"-header")==0 ){
  6899   7069         data.showHeader = 1;
  6900   7070       }else if( strcmp(z,"-noheader")==0 ){
  6901   7071         data.showHeader = 0;
  6902   7072       }else if( strcmp(z,"-echo")==0 ){
  6903   7073         ShellSetFlag(&data, SHFLG_Echo);
  6904   7074       }else if( strcmp(z,"-eqp")==0 ){
  6905         -      data.autoEQP = 1;
         7075  +      data.autoEQP = AUTOEQP_on;
  6906   7076       }else if( strcmp(z,"-eqpfull")==0 ){
  6907         -      data.autoEQP = 2;
         7077  +      data.autoEQP = AUTOEQP_full;
  6908   7078       }else if( strcmp(z,"-stats")==0 ){
  6909   7079         data.statsOn = 1;
  6910   7080       }else if( strcmp(z,"-scanstats")==0 ){
  6911   7081         data.scanstatsOn = 1;
  6912   7082       }else if( strcmp(z,"-backslash")==0 ){
  6913   7083         /* Undocumented command-line option: -backslash
  6914   7084         ** Causes C-style backslash escapes to be evaluated in SQL statements

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_TRIGGER_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_TRIGGER_EQP           1008 /* int int* */
         2094  +#define SQLITE_DBCONFIG_MAX                   1008 /* Largest DBCONFIG */
  2086   2095   
  2087   2096   /*
  2088   2097   ** CAPI3REF: Enable Or Disable Extended Result Codes
  2089   2098   ** METHOD: sqlite3
  2090   2099   **
  2091   2100   ** ^The sqlite3_extended_result_codes() routine enables or disables the
  2092   2101   ** [extended result codes] feature of SQLite. ^The extended result
................................................................................
  7029   7038   #define SQLITE_TESTCTRL_ONCE_RESET_THRESHOLD    19
  7030   7039   #define SQLITE_TESTCTRL_NEVER_CORRUPT           20
  7031   7040   #define SQLITE_TESTCTRL_VDBE_COVERAGE           21
  7032   7041   #define SQLITE_TESTCTRL_BYTEORDER               22
  7033   7042   #define SQLITE_TESTCTRL_ISINIT                  23
  7034   7043   #define SQLITE_TESTCTRL_SORTER_MMAP             24
  7035   7044   #define SQLITE_TESTCTRL_IMPOSTER                25
  7036         -#define SQLITE_TESTCTRL_LAST                    25
         7045  +#define SQLITE_TESTCTRL_LAST                    25  /* Largest TESTCTRL */
  7037   7046   
  7038   7047   /*
  7039   7048   ** CAPI3REF: SQLite Runtime Status
  7040   7049   **
  7041   7050   ** ^These interfaces are used to retrieve runtime status information
  7042   7051   ** about the performance of SQLite, and optionally to reset various
  7043   7052   ** highwater marks.  ^The first argument is an integer code for
................................................................................
  8283   8292   ** value returned is one of [SQLITE_ROLLBACK], [SQLITE_IGNORE], [SQLITE_FAIL],
  8284   8293   ** [SQLITE_ABORT], or [SQLITE_REPLACE], according to the [ON CONFLICT] mode
  8285   8294   ** of the SQL statement that triggered the call to the [xUpdate] method of the
  8286   8295   ** [virtual table].
  8287   8296   */
  8288   8297   int sqlite3_vtab_on_conflict(sqlite3 *);
  8289   8298   
         8299  +/*
         8300  +** CAPI3REF: Determine The Collation For a Virtual Table Constraint
         8301  +**
         8302  +** This function may only be called from within a call to the [xBestIndex]
         8303  +** method of a [virtual table implementation]. 
         8304  +**
         8305  +** The first argument must be the sqlite3_index_info object that is the
         8306  +** first parameter to the xBestIndex() method. The second argument must be
         8307  +** an index into the aConstraint[] array belonging to the sqlite3_index_info
         8308  +** structure 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_index_info*,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.

  1416   1416     unsigned nProgressOps;        /* Number of opcodes for progress callback */
  1417   1417   #endif
  1418   1418   #ifndef SQLITE_OMIT_VIRTUALTABLE
  1419   1419     int nVTrans;                  /* Allocated size of aVTrans */
  1420   1420     Hash aModule;                 /* populated by sqlite3_create_module() */
  1421   1421     VtabCtx *pVtabCtx;            /* Context for active vtab connect/create */
  1422   1422     VTable **aVTrans;             /* Virtual tables with open transactions */
  1423         -  VTable *pDisconnect;    /* Disconnect these in next sqlite3_prepare() */
         1423  +  VTable *pDisconnect;          /* Disconnect these in next sqlite3_prepare() */
  1424   1424   #endif
  1425   1425     Hash aFunc;                   /* Hash table of connection functions */
  1426   1426     Hash aCollSeq;                /* All collating sequences */
  1427   1427     BusyHandler busyHandler;      /* Busy callback */
  1428   1428     Db aDbStatic[2];              /* Static space for the 2 default backends */
  1429   1429     Savepoint *pSavepoint;        /* List of active savepoints */
  1430   1430     int busyTimeout;              /* Busy handler timeout, in msec */
................................................................................
  1491   1491   #define SQLITE_LoadExtension  0x00010000  /* Enable load_extension */
  1492   1492   #define SQLITE_LoadExtFunc    0x00020000  /* Enable load_extension() SQL func */
  1493   1493   #define SQLITE_EnableTrigger  0x00040000  /* True to enable triggers */
  1494   1494   #define SQLITE_DeferFKs       0x00080000  /* Defer all FK constraints */
  1495   1495   #define SQLITE_QueryOnly      0x00100000  /* Disable database changes */
  1496   1496   #define SQLITE_CellSizeCk     0x00200000  /* Check btree cell sizes on load */
  1497   1497   #define SQLITE_Fts3Tokenizer  0x00400000  /* Enable fts3_tokenizer(2) */
  1498         -#define SQLITE_EnableQPSG     0x00800000  /* Query Planner Stability Guarantee */
         1498  +#define SQLITE_EnableQPSG     0x00800000  /* Query Planner Stability Guarantee*/
         1499  +#define SQLITE_TriggerEQP     0x01000000  /* Show trigger EXPLAIN QUERY PLAN */
         1500  +
  1499   1501   /* Flags used only if debugging */
  1500   1502   #ifdef SQLITE_DEBUG
  1501   1503   #define SQLITE_SqlTrace       0x08000000  /* Debug print SQL as it executes */
  1502   1504   #define SQLITE_VdbeListing    0x10000000  /* Debug listings of VDBE programs */
  1503   1505   #define SQLITE_VdbeTrace      0x20000000  /* True to trace VDBE execution */
  1504   1506   #define SQLITE_VdbeAddopTrace 0x40000000  /* Trace sqlite3VdbeAddOp() calls */
  1505   1507   #define SQLITE_VdbeEQP        0x80000000  /* Debug EXPLAIN QUERY PLAN */

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/treeview.c.

   503    503     if( pList==0 ){
   504    504       sqlite3TreeViewLine(pView, "%s (empty)", zLabel);
   505    505     }else{
   506    506       int i;
   507    507       sqlite3TreeViewLine(pView, "%s", zLabel);
   508    508       for(i=0; i<pList->nExpr; i++){
   509    509         int j = pList->a[i].u.x.iOrderByCol;
   510         -      if( j ){
          510  +      char *zName = pList->a[i].zName;
          511  +      if( j || zName ){
   511    512           sqlite3TreeViewPush(pView, 0);
          513  +      }
          514  +      if( zName ){
          515  +        sqlite3TreeViewLine(pView, "AS %s", zName);
          516  +      }
          517  +      if( j ){
   512    518           sqlite3TreeViewLine(pView, "iOrderByCol=%d", j);
   513    519         }
   514    520         sqlite3TreeViewExpr(pView, pList->a[i].pExpr, i<pList->nExpr-1);
   515         -      if( j ) sqlite3TreeViewPop(pView);
          521  +      if( j || zName ){
          522  +        sqlite3TreeViewPop(pView);
          523  +      }
   516    524       }
   517    525     }
   518    526   }
   519    527   void sqlite3TreeViewExprList(
   520    528     TreeView *pView,
   521    529     const ExprList *pList,
   522    530     u8 moreToFollow,

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 bListSubprogs = (p->explain==1 || (db->flags & SQLITE_TriggerEQP)!=0);
         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( bListSubprogs ){
  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( bListSubprogs && 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.

    14     14   ** generating the code that loops through a table looking for applicable
    15     15   ** rows.  Indices are selected and used to speed the search when doing
    16     16   ** so is applicable.  Because this module is responsible for selecting
    17     17   ** indices, you might also think of this module as the "query optimizer".
    18     18   */
    19     19   #include "sqliteInt.h"
    20     20   #include "whereInt.h"
           21  +
           22  +/*
           23  +** Extra information appended to the end of sqlite3_index_info but not
           24  +** visible to the xBestIndex function, at least not directly.  The
           25  +** sqlite3_vtab_collation() interface knows how to reach it, however.
           26  +**
           27  +** This object is not an API and can be changed from one release to the
           28  +** next.  As long as allocateIndexInfo() and sqlite3_vtab_collation()
           29  +** agree on the structure, all will be well.
           30  +*/
           31  +typedef struct HiddenIndexInfo HiddenIndexInfo;
           32  +struct HiddenIndexInfo {
           33  +  WhereClause *pWC;   /* The Where clause being analyzed */
           34  +  Parse *pParse;      /* The parsing context */
           35  +};
    21     36   
    22     37   /* Forward declaration of methods */
    23     38   static int whereLoopResize(sqlite3*, WhereLoop*, int);
    24     39   
    25     40   /* Test variable that can be set to enable WHERE tracing */
    26     41   #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
    27     42   /***/ int sqlite3WhereTrace = 0;
................................................................................
   837    852   #ifndef SQLITE_OMIT_VIRTUALTABLE
   838    853   /*
   839    854   ** Allocate and populate an sqlite3_index_info structure. It is the 
   840    855   ** responsibility of the caller to eventually release the structure
   841    856   ** by passing the pointer returned by this function to sqlite3_free().
   842    857   */
   843    858   static sqlite3_index_info *allocateIndexInfo(
   844         -  Parse *pParse,
   845         -  WhereClause *pWC,
          859  +  Parse *pParse,                  /* The parsing context */
          860  +  WhereClause *pWC,               /* The WHERE clause being analyzed */
   846    861     Bitmask mUnusable,              /* Ignore terms with these prereqs */
   847         -  struct SrcList_item *pSrc,
   848         -  ExprList *pOrderBy,
          862  +  struct SrcList_item *pSrc,      /* The FROM clause term that is the vtab */
          863  +  ExprList *pOrderBy,             /* The ORDER BY clause */
   849    864     u16 *pmNoOmit                   /* Mask of terms not to omit */
   850    865   ){
   851    866     int i, j;
   852    867     int nTerm;
   853    868     struct sqlite3_index_constraint *pIdxCons;
   854    869     struct sqlite3_index_orderby *pIdxOrderBy;
   855    870     struct sqlite3_index_constraint_usage *pUsage;
          871  +  struct HiddenIndexInfo *pHidden;
   856    872     WhereTerm *pTerm;
   857    873     int nOrderBy;
   858    874     sqlite3_index_info *pIdxInfo;
   859    875     u16 mNoOmit = 0;
   860    876   
   861    877     /* Count the number of possible WHERE clause constraints referring
   862    878     ** to this virtual table */
................................................................................
   890    906       }
   891    907     }
   892    908   
   893    909     /* Allocate the sqlite3_index_info structure
   894    910     */
   895    911     pIdxInfo = sqlite3DbMallocZero(pParse->db, sizeof(*pIdxInfo)
   896    912                              + (sizeof(*pIdxCons) + sizeof(*pUsage))*nTerm
   897         -                           + sizeof(*pIdxOrderBy)*nOrderBy );
          913  +                           + sizeof(*pIdxOrderBy)*nOrderBy + sizeof(*pHidden) );
   898    914     if( pIdxInfo==0 ){
   899    915       sqlite3ErrorMsg(pParse, "out of memory");
   900    916       return 0;
   901    917     }
   902    918   
   903    919     /* Initialize the structure.  The sqlite3_index_info structure contains
   904    920     ** many fields that are declared "const" to prevent xBestIndex from
   905    921     ** changing them.  We have to do some funky casting in order to
   906    922     ** initialize those fields.
   907    923     */
   908         -  pIdxCons = (struct sqlite3_index_constraint*)&pIdxInfo[1];
          924  +  pHidden = (struct HiddenIndexInfo*)&pIdxInfo[1];
          925  +  pIdxCons = (struct sqlite3_index_constraint*)&pHidden[1];
   909    926     pIdxOrderBy = (struct sqlite3_index_orderby*)&pIdxCons[nTerm];
   910    927     pUsage = (struct sqlite3_index_constraint_usage*)&pIdxOrderBy[nOrderBy];
   911    928     *(int*)&pIdxInfo->nConstraint = nTerm;
   912    929     *(int*)&pIdxInfo->nOrderBy = nOrderBy;
   913    930     *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint = pIdxCons;
   914    931     *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy;
   915    932     *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage =
   916    933                                                                      pUsage;
   917    934   
          935  +  pHidden->pWC = pWC;
          936  +  pHidden->pParse = pParse;
   918    937     for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
   919    938       u16 op;
   920    939       if( pTerm->leftCursor != pSrc->iCursor ) continue;
   921    940       if( pTerm->prereqRight & mUnusable ) continue;
   922    941       assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
   923    942       testcase( pTerm->eOperator & WO_IN );
   924    943       testcase( pTerm->eOperator & WO_IS );
................................................................................
  3134   3153     WHERETRACE(0xffff, ("  bIn=%d prereqIn=%04llx prereqOut=%04llx\n",
  3135   3154                         *pbIn, (sqlite3_uint64)mPrereq,
  3136   3155                         (sqlite3_uint64)(pNew->prereq & ~mPrereq)));
  3137   3156   
  3138   3157     return rc;
  3139   3158   }
  3140   3159   
         3160  +/*
         3161  +** If this function is invoked from within an xBestIndex() callback, it
         3162  +** returns a pointer to a buffer containing the name of the collation
         3163  +** sequence associated with element iCons of the sqlite3_index_info.aConstraint
         3164  +** array. Or, if iCons is out of range or there is no active xBestIndex
         3165  +** call, return NULL.
         3166  +*/
         3167  +const char *sqlite3_vtab_collation(sqlite3_index_info *pIdxInfo, int iCons){
         3168  +  HiddenIndexInfo *pHidden = (HiddenIndexInfo*)&pIdxInfo[1];
         3169  +  const char *zRet = 0;
         3170  +  if( iCons>=0 && iCons<pIdxInfo->nConstraint ){
         3171  +    CollSeq *pC = 0;
         3172  +    int iTerm = pIdxInfo->aConstraint[iCons].iTermOffset;
         3173  +    Expr *pX = pHidden->pWC->a[iTerm].pExpr;
         3174  +    if( pX->pLeft ){
         3175  +      pC = sqlite3BinaryCompareCollSeq(pHidden->pParse, pX->pLeft, pX->pRight);
         3176  +    }
         3177  +    zRet = (pC ? pC->zName : "BINARY");
         3178  +  }
         3179  +  return zRet;
         3180  +}
  3141   3181   
  3142   3182   /*
  3143   3183   ** Add all WhereLoop objects for a table of the join identified by
  3144   3184   ** pBuilder->pNew->iTab.  That table is guaranteed to be a virtual table.
  3145   3185   **
  3146   3186   ** If there are no LEFT or CROSS JOIN joins in the query, both mPrereq and
  3147   3187   ** mUnusable are set to 0. Otherwise, mPrereq is a mask of all FROM clause

Changes to src/wherecode.c.

   124    124     WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
   125    125     int iLevel,                     /* Value for "level" column of output */
   126    126     int iFrom,                      /* Value for "from" column of output */
   127    127     u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
   128    128   ){
   129    129     int ret = 0;
   130    130   #if !defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_STMT_SCANSTATUS)
   131         -  if( pParse->explain==2 )
          131  +  if( sqlite3ParseToplevel(pParse)->explain==2 )
   132    132   #endif
   133    133     {
   134    134       struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
   135    135       Vdbe *v = pParse->pVdbe;      /* VM being constructed */
   136    136       sqlite3 *db = pParse->db;     /* Database handle */
   137    137       int iId = pParse->iSelectId;  /* Select id (left-most output column) */
   138    138       int isSearch;                 /* True for a SEARCH. False for SCAN. */

Changes to test/colname.test.

   373    373   } {a 1 n 3}
   374    374   do_test colname-9.211 {
   375    375     execsql2 {SELECT t1.a AS n, v3.a FROM t1 JOIN v3}
   376    376   } {n 1 a 3}
   377    377   do_test colname-9.210 {
   378    378     execsql2 {SELECT t1.a, v3.a AS n FROM t1 JOIN v3}
   379    379   } {a 1 n 3}
          380  +
          381  +# 2017-12-23:  Ticket https://www.sqlite.org/src/info/3b4450072511e621
          382  +# Inconsistent column names in CREATE TABLE AS
          383  +#
          384  +# Verify that the names of columns in the created table of a CREATE TABLE AS
          385  +# are the same as the names of result columns in the SELECT statement.
          386  +#
          387  +do_execsql_test colname-9.300 {
          388  +  DROP TABLE IF EXISTS t1;
          389  +  DROP TABLE IF EXISTS t2;
          390  +  CREATE TABLE t1(aaa INT);
          391  +  INSERT INTO t1(aaa) VALUES(123);
          392  +}
          393  +do_test colname-9.310 {
          394  +  execsql2 {SELECT BBb FROM (SELECT aaa AS Bbb FROM t1)}
          395  +} {Bbb 123}
          396  +do_execsql_test colname-9.320 {
          397  +  CREATE TABLE t2 AS SELECT BBb FROM (SELECT aaa AS Bbb FROM t1);
          398  +  SELECT name FROM pragma_table_info('t2');
          399  +} {Bbb}
          400  +
   380    401   
   381    402   # Make sure the quotation marks get removed from the column names
   382    403   # when constructing a new table from an aggregate SELECT.
   383    404   # Email from Juergen Palm on 2017-07-11.
   384    405   #
   385    406   do_execsql_test colname-10.100 {
   386    407     DROP TABLE IF EXISTS t1;

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   }

Changes to test/swarmvtab.test.

   209    209   do_catchsql_test 3.1 {
   210    210     CREATE VIRTUAL TABLE temp.xyz USING swarmvtab(
   211    211       'VALUES
   212    212           ("test.db1", "t1", 1, 10),
   213    213           ("test.db2", "t1", 11, 20)
   214    214       ', 'fetch_db_no_such_function'
   215    215     );
   216         -} {1 {no such function: fetch_db_no_such_function}}
          216  +} {1 {sql error: no such function: fetch_db_no_such_function}}
   217    217   
   218    218   do_catchsql_test 3.2 {
   219    219     CREATE VIRTUAL TABLE temp.xyz USING swarmvtab(
   220    220       'VALUES
   221    221           ("test.db1", "t1", 1, 10),
   222    222           ("test.db2", "t1", 11, 20)
   223    223       ', 'fetch_db'

Changes to test/swarmvtab2.test.

    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is the "swarmvtab" extension
    13     13   #
    14     14   
    15     15   set testdir [file dirname $argv0]
    16     16   source $testdir/tester.tcl
    17         -set testprefix swarmvtab
           17  +set testprefix swarmvtab2
    18     18   do_not_use_codec
    19     19   
    20     20   ifcapable !vtab {
    21     21     finish_test
    22     22     return
    23     23   }
    24     24   

Added test/swarmvtab3.test.

            1  +# 2017-07-15
            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  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is the "swarmvtab" extension
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set testprefix swarmvtab3
           18  +do_not_use_codec
           19  +
           20  +ifcapable !vtab {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +load_static_extension db unionvtab
           26  +
           27  +set nFile $sqlite_open_file_count
           28  +
           29  +do_execsql_test 1.0 {
           30  +  CREATE TEMP TABLE swarm(id, tbl, minval, maxval);
           31  +}
           32  +
           33  +# Set up 100 databases with filenames "remote_test.dbN", where N is between
           34  +# 0 and 99.
           35  +do_test 1.1 {
           36  +  for {set i 0} {$i < 100} {incr i} {
           37  +    set file remote_test.db$i
           38  +    forcedelete $file
           39  +    forcedelete test.db$i
           40  +    sqlite3 rrr $file
           41  +    rrr eval {
           42  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
           43  +      INSERT INTO t1 VALUES($i, $i);
           44  +    }
           45  +    rrr close
           46  +    db eval {
           47  +      INSERT INTO swarm VALUES($i, 't1', $i, $i);
           48  +    }
           49  +    set ::dbcache(test.db$i) 0
           50  +  }
           51  +} {}
           52  +
           53  +proc missing_db {filename} {
           54  +  set remote "remote_$filename"
           55  +  forcedelete $filename
           56  +  file copy $remote $filename
           57  +}
           58  +db func missing_db missing_db
           59  +
           60  +proc openclose_db {filename bClose} {
           61  +  if {$bClose} {
           62  +    incr ::dbcache($filename) -1
           63  +  } else {
           64  +    incr ::dbcache($filename) 1
           65  +  }
           66  +  if {$::dbcache($filename)==0} {
           67  +    forcedelete $filename
           68  +  }
           69  +}
           70  +db func openclose_db openclose_db
           71  +
           72  +proc check_dbcache {} {
           73  +  set n 0
           74  +  for {set i 0} {$i<100} {incr i} {
           75  +    set exists [file exists test.db$i]
           76  +    if {$exists!=($::dbcache(test.db$i)!=0)} {
           77  +      error "inconsistent ::dbcache and disk"
           78  +    }
           79  +    incr n $exists
           80  +  }
           81  +  return $n
           82  +}
           83  +
           84  +foreach {tn nMaxOpen cvt} {
           85  +  1 5 {
           86  +    CREATE VIRTUAL TABLE temp.s USING swarmvtab(
           87  +        'SELECT :prefix || id, tbl, minval, minval FROM swarm',
           88  +        :prefix='test.db',
           89  +        missing=missing_db,
           90  +        openclose=openclose_db,
           91  +        maxopen=5
           92  +    )
           93  +  }
           94  +
           95  +  2 3 {
           96  +    CREATE VIRTUAL TABLE temp.s USING swarmvtab(
           97  +        'SELECT :prefix || id, tbl, minval, minval FROM swarm',
           98  +        :prefix='test.db',
           99  +        missing =       'missing_db',
          100  +        openclose=[openclose_db],
          101  +        maxopen = 3
          102  +    )
          103  +  }
          104  +
          105  +  3 1 {
          106  +    CREATE VIRTUAL TABLE temp.s USING swarmvtab(
          107  +        'SELECT :prefix||''.''||:suffix||id, tbl, minval, minval FROM swarm',
          108  +        :prefix=test, :suffix=db,
          109  +        missing =       'missing_db',
          110  +        openclose=[openclose_db],
          111  +        maxopen = 1
          112  +    )
          113  +  }
          114  +
          115  +} {
          116  +  execsql { DROP TABLE IF EXISTS s }
          117  +  
          118  +  do_execsql_test 1.$tn.1 $cvt
          119  +
          120  +  do_execsql_test 1.$tn.2 {
          121  +    SELECT b FROM s WHERE a<10;
          122  +  } {0 1 2 3 4 5 6 7 8 9}
          123  +
          124  +  do_test 1.$tn.3 { check_dbcache } $nMaxOpen
          125  +
          126  +  do_execsql_test 1.$tn.4 {
          127  +    SELECT b FROM s WHERE (b%10)=0;
          128  +  } {0 10 20 30 40 50 60 70 80 90}
          129  +
          130  +  do_test 1.$tn.5 { check_dbcache } $nMaxOpen
          131  +}
          132  +
          133  +execsql { DROP TABLE IF EXISTS s }
          134  +for {set i 0} {$i < 100} {incr i} {
          135  +  forcedelete remote_test.db$i
          136  +}
          137  +
          138  +#----------------------------------------------------------------------------
          139  +#
          140  +do_execsql_test 2.0 {
          141  +  DROP TABLE IF EXISTS swarm;
          142  +  CREATE TEMP TABLE swarm(file, tbl, minval, maxval, ctx);
          143  +}
          144  +
          145  +catch { array unset ::dbcache }
          146  +
          147  +# Set up 100 databases with filenames "remote_test.dbN", where N is a
          148  +# random integer between 0 and 1,000,000
          149  +# 0 and 99.
          150  +do_test 2.1 {
          151  +  for {set i 0} {$i < 100} {incr i} {
          152  +    while 1 {
          153  +      set ctx [expr abs(int(rand() *1000000))]
          154  +      if {[info exists ::dbcache($ctx)]==0} break
          155  +    }
          156  +
          157  +    set file test_remote.db$ctx
          158  +    forcedelete $file
          159  +    forcedelete test.db$i
          160  +    sqlite3 rrr $file
          161  +    rrr eval {
          162  +      CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
          163  +      INSERT INTO t1 VALUES($i, $i);
          164  +    }
          165  +    rrr close
          166  +    db eval {
          167  +      INSERT INTO swarm VALUES('test.db' || $i, 't1', $i, $i, $file)
          168  +    }
          169  +    set ::dbcache(test.db$i) 0
          170  +  }
          171  +} {}
          172  +
          173  +proc missing_db {filename ctx} {
          174  +  file copy $ctx $filename
          175  +}
          176  +db func missing_db missing_db
          177  +
          178  +proc openclose_db {filename ctx bClose} {
          179  +  if {$bClose} {
          180  +    incr ::dbcache($filename) -1
          181  +  } else {
          182  +    incr ::dbcache($filename) 1
          183  +  }
          184  +  if {$::dbcache($filename)==0} {
          185  +    forcedelete $filename
          186  +  }
          187  +}
          188  +db func openclose_db openclose_db
          189  +
          190  +proc check_dbcache {} {
          191  +  set n 0
          192  +  foreach k [array names ::dbcache] {
          193  +    set exists [file exists $k]
          194  +    if {$exists!=($::dbcache($k)!=0)} {
          195  +      error "inconsistent ::dbcache and disk ($k)"
          196  +    }
          197  +    incr n $exists
          198  +  }
          199  +  return $n
          200  +}
          201  +
          202  +foreach {tn nMaxOpen cvt} {
          203  +  2 5 {
          204  +    CREATE VIRTUAL TABLE temp.s USING swarmvtab(
          205  +        'SELECT file, tbl, minval, minval, ctx FROM swarm',
          206  +        missing=missing_db,
          207  +        openclose=openclose_db,
          208  +        maxopen=5
          209  +    )
          210  +  }
          211  +} {
          212  +  execsql { DROP TABLE IF EXISTS s }
          213  +  
          214  +  do_execsql_test 1.$tn.1 $cvt
          215  +
          216  +  do_execsql_test 1.$tn.2 {
          217  +    SELECT b FROM s WHERE a<10;
          218  +  } {0 1 2 3 4 5 6 7 8 9}
          219  +
          220  +  do_test 1.$tn.3 { check_dbcache } $nMaxOpen
          221  +
          222  +  do_execsql_test 1.$tn.4 {
          223  +    SELECT b FROM s WHERE (b%10)=0;
          224  +  } {0 10 20 30 40 50 60 70 80 90}
          225  +
          226  +  do_test 1.$tn.5 { check_dbcache } $nMaxOpen
          227  +}
          228  +
          229  +db close
          230  +forcedelete {*}[glob test_remote.db*]
          231  +
          232  +finish_test
          233  +

Changes to test/tester.tcl.

  2267   2267     eval sqlite3_config_pagecache $::old_pagecache_config
  2268   2268     unset ::old_pagecache_config 
  2269   2269     sqlite3_initialize
  2270   2270     autoinstall_test_functions
  2271   2271     sqlite3 db test.db
  2272   2272   }
  2273   2273   
  2274         -proc test_find_binary {nm} {
         2274  +proc test_binary_name {nm} {
  2275   2275     if {$::tcl_platform(platform)=="windows"} {
  2276   2276       set ret "$nm.exe"
  2277   2277     } else {
  2278   2278       set ret $nm
  2279   2279     }
  2280         -  set ret [file normalize [file join $::cmdlinearg(TESTFIXTURE_HOME) $ret]]
         2280  +  file normalize [file join $::cmdlinearg(TESTFIXTURE_HOME) $ret]
         2281  +}
         2282  +
         2283  +proc test_find_binary {nm} {
         2284  +  set ret [test_binary_name $nm]
  2281   2285     if {![file executable $ret]} {
  2282   2286       finish_test
  2283   2287       return ""
  2284   2288     }
  2285   2289     return $ret
  2286   2290   }
  2287   2291   

Changes to tool/lempar.c.

   647    647   };
   648    648   
   649    649   static void yy_accept(yyParser*);  /* Forward Declaration */
   650    650   
   651    651   /*
   652    652   ** Perform a reduce action and the shift that must immediately
   653    653   ** follow the reduce.
          654  +**
          655  +** The yyLookahead and yyLookaheadToken parameters provide reduce actions
          656  +** access to the lookahead token (if any).  The yyLookahead will be YYNOCODE
          657  +** if the lookahead token has already been consumed.  As this procedure is
          658  +** only called from one place, optimizing compilers will in-line it, which
          659  +** means that the extra parameters have no performance impact.
   654    660   */
   655    661   static void yy_reduce(
   656    662     yyParser *yypParser,         /* The parser */
   657         -  unsigned int yyruleno        /* Number of the rule by which to reduce */
          663  +  unsigned int yyruleno,       /* Number of the rule by which to reduce */
          664  +  int yyLookahead,             /* Lookahead token, or YYNOCODE if none */
          665  +  ParseTOKENTYPE yyLookaheadToken  /* Value of the lookahead token */
   658    666   ){
   659    667     int yygoto;                     /* The next state */
   660    668     int yyact;                      /* The next action */
   661    669     yyStackEntry *yymsp;            /* The top of the parser's stack */
   662    670     int yysize;                     /* Amount to pop the stack */
   663    671     ParseARG_FETCH;
   664    672     yymsp = yypParser->yytos;
................................................................................
   849    857       if( yyact <= YY_MAX_SHIFTREDUCE ){
   850    858         yy_shift(yypParser,yyact,yymajor,yyminor);
   851    859   #ifndef YYNOERRORRECOVERY
   852    860         yypParser->yyerrcnt--;
   853    861   #endif
   854    862         yymajor = YYNOCODE;
   855    863       }else if( yyact <= YY_MAX_REDUCE ){
   856         -      yy_reduce(yypParser,yyact-YY_MIN_REDUCE);
          864  +      yy_reduce(yypParser,yyact-YY_MIN_REDUCE,yymajor,yyminor);
   857    865       }else{
   858    866         assert( yyact == YY_ERROR_ACTION );
   859    867         yyminorunion.yy0 = yyminor;
   860    868   #ifdef YYERRORSYMBOL
   861    869         int yymx;
   862    870   #endif
   863    871   #ifndef NDEBUG