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: |
edceaccd66a65d6b36e53ce33d760a7b |
| 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
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 <sql-query> test.db 63 +</pre> 64 + 65 +Or an entire text file worth of queries with: 66 + 67 +<pre> 68 + ./sqlite3_expert -file <text-file> 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 <sql-query> test.db 80 + 81 + # Do not generate any statistics at all: 82 + ./sqlite3_expert -sample 0 -sql <sql-query> 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 ); 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 ); 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 ); 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 ); 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