Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch withdrawn Excluding Merge-Ins
This is equivalent to a diff from 36acc0a9 to d1ef9eab
2017-10-12
| ||
20:37 | Add the sqlite_dbpage virtual table (enabled using SQLITE_ENABLE_DBPAGE_VTAB). Make that virtual table and dbstat available to the command-line shell. (check-in: eaeeb09d user: drh tags: trunk) | |
15:28 | Add the experimental "sqlite_expert" extension. Used to find index definitions that might help with specified SQL queries. (Later:) Parked on a dead-end branch due to an assertion fault in TH3. We will merge again after the bug is fixed. (Closed-Leaf check-in: d1ef9eab user: dan tags: withdrawn) | |
14:13 | Update this branch to match latest trunk. (check-in: d325da6c user: dan tags: schemalint) | |
14:03 | Merge fixes from trunk. All changes are on makefiles and test scripts. There are no core code changes. (check-in: 1fb87a0c user: drh tags: branch-3.21) | |
13:47 | The src/shell.c file is now generated from src/shell.c.in, so remove shell.c from version control and update the makefiles to build it automatically. (check-in: 36acc0a9 user: drh tags: trunk) | |
13:21 | Merge fixes from the 3.21 branch. (check-in: 29292169 user: drh tags: trunk) | |
Changes to Makefile.in.
︙ | ︙ | |||
414 415 416 417 418 419 420 421 422 423 424 425 426 427 | $(TOP)/ext/fts3/fts3_test.c \ $(TOP)/ext/session/test_session.c \ $(TOP)/ext/rbu/test_rbu.c # Statically linked extensions # TESTSRC += \ $(TOP)/ext/misc/amatch.c \ $(TOP)/ext/misc/carray.c \ $(TOP)/ext/misc/closure.c \ $(TOP)/ext/misc/csv.c \ $(TOP)/ext/misc/eval.c \ $(TOP)/ext/misc/fileio.c \ $(TOP)/ext/misc/fuzzer.c \ | > > | 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 | $(TOP)/ext/fts3/fts3_test.c \ $(TOP)/ext/session/test_session.c \ $(TOP)/ext/rbu/test_rbu.c # Statically linked extensions # TESTSRC += \ $(TOP)/ext/expert/sqlite3expert.c \ $(TOP)/ext/expert/test_expert.c \ $(TOP)/ext/misc/amatch.c \ $(TOP)/ext/misc/carray.c \ $(TOP)/ext/misc/closure.c \ $(TOP)/ext/misc/csv.c \ $(TOP)/ext/misc/eval.c \ $(TOP)/ext/misc/fileio.c \ $(TOP)/ext/misc/fuzzer.c \ |
︙ | ︙ | |||
1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 | echo "static const char *zMainloop = " >> $@ $(TCLSH_CMD) $(TOP)/tool/tostr.tcl $(TOP)/tool/spaceanal.tcl >> $@ echo "; return zMainloop; }" >> $@ sqlite3_analyzer$(TEXE): sqlite3_analyzer.c $(LTLINK) sqlite3_analyzer.c -o $@ $(LIBTCL) $(TLIBS) dbdump$(TEXE): $(TOP)/ext/misc/dbdump.c sqlite3.lo $(LTLINK) -DDBDUMP_STANDALONE -o $@ \ $(TOP)/ext/misc/dbdump.c sqlite3.lo $(TLIBS) showdb$(TEXE): $(TOP)/tool/showdb.c sqlite3.lo $(LTLINK) -o $@ $(TOP)/tool/showdb.c sqlite3.lo $(TLIBS) | > > > | 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 | echo "static const char *zMainloop = " >> $@ $(TCLSH_CMD) $(TOP)/tool/tostr.tcl $(TOP)/tool/spaceanal.tcl >> $@ echo "; return zMainloop; }" >> $@ sqlite3_analyzer$(TEXE): sqlite3_analyzer.c $(LTLINK) sqlite3_analyzer.c -o $@ $(LIBTCL) $(TLIBS) sqlite3_expert$(TEXE): $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c $(LTLINK) $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c -o sqlite3_expert $(TLIBS) dbdump$(TEXE): $(TOP)/ext/misc/dbdump.c sqlite3.lo $(LTLINK) -DDBDUMP_STANDALONE -o $@ \ $(TOP)/ext/misc/dbdump.c sqlite3.lo $(TLIBS) showdb$(TEXE): $(TOP)/tool/showdb.c sqlite3.lo $(LTLINK) -o $@ $(TOP)/tool/showdb.c sqlite3.lo $(TLIBS) |
︙ | ︙ |
Changes to Makefile.msc.
︙ | ︙ | |||
1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 | $(TOP)\ext\fts3\fts3_test.c \ $(TOP)\ext\rbu\test_rbu.c \ $(TOP)\ext\session\test_session.c # Statically linked extensions. # TESTEXT = \ $(TOP)\ext\misc\amatch.c \ $(TOP)\ext\misc\carray.c \ $(TOP)\ext\misc\closure.c \ $(TOP)\ext\misc\csv.c \ $(TOP)\ext\misc\eval.c \ $(TOP)\ext\misc\fileio.c \ $(TOP)\ext\misc\fuzzer.c \ | > > | 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 | $(TOP)\ext\fts3\fts3_test.c \ $(TOP)\ext\rbu\test_rbu.c \ $(TOP)\ext\session\test_session.c # Statically linked extensions. # TESTEXT = \ $(TOP)\ext\expert\sqlite3expert.c \ $(TOP)\ext\expert\test_expert.c \ $(TOP)\ext\misc\amatch.c \ $(TOP)\ext\misc\carray.c \ $(TOP)\ext\misc\closure.c \ $(TOP)\ext\misc\csv.c \ $(TOP)\ext\misc\eval.c \ $(TOP)\ext\misc\fileio.c \ $(TOP)\ext\misc\fuzzer.c \ |
︙ | ︙ | |||
2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 | echo static const char *zMainloop = >> $@ $(TCLSH_CMD) $(TOP)\tool\tostr.tcl $(TOP)\tool\spaceanal.tcl >> $@ echo ; return zMainloop; } >> $@ sqlite3_analyzer.exe: sqlite3_analyzer.c $(LIBRESOBJS) $(LTLINK) $(NO_WARN) -DBUILD_sqlite -I$(TCLINCDIR) sqlite3_analyzer.c \ /link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS) dbdump.exe: $(TOP)\ext\misc\dbdump.c $(SQLITE3C) $(SQLITE3H) $(LTLINK) $(NO_WARN) -DDBDUMP_STANDALONE $(TOP)\ext\misc\dbdump.c $(SQLITE3C) \ /link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) testloadext.lo: $(TOP)\src\test_loadext.c $(LTCOMPILE) $(NO_WARN) -c $(TOP)\src\test_loadext.c | > > > | 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 | echo static const char *zMainloop = >> $@ $(TCLSH_CMD) $(TOP)\tool\tostr.tcl $(TOP)\tool\spaceanal.tcl >> $@ echo ; return zMainloop; } >> $@ sqlite3_analyzer.exe: sqlite3_analyzer.c $(LIBRESOBJS) $(LTLINK) $(NO_WARN) -DBUILD_sqlite -I$(TCLINCDIR) sqlite3_analyzer.c \ /link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS) sqlite3_expert.exe: $(SQLITE3C) $(TOP)\ext\expert\sqlite3expert.h $(TOP)\ext\expert\sqlite3expert.c $(TOP)\ext\expert\expert.c $(LTLINK) $(NO_WARN) $(TOP)\ext\expert\sqlite3expert.c $(TOP)\ext\expert\expert.c $(SQLITE3C) $(TLIBS) dbdump.exe: $(TOP)\ext\misc\dbdump.c $(SQLITE3C) $(SQLITE3H) $(LTLINK) $(NO_WARN) -DDBDUMP_STANDALONE $(TOP)\ext\misc\dbdump.c $(SQLITE3C) \ /link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) testloadext.lo: $(TOP)\src\test_loadext.c $(LTCOMPILE) $(NO_WARN) -c $(TOP)\src\test_loadext.c |
︙ | ︙ |
Added ext/expert/README.md.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | ## SQLite Expert Extension This folder contains code for a simple system to propose useful indexes given a database and a set of SQL queries. It works as follows: 1. The user database schema is copied to a temporary database. 1. All SQL queries are prepared against the temporary database. Information regarding the WHERE and ORDER BY clauses, and other query features that affect index selection are recorded. 1. The information gathered in step 2 is used to create candidate indexes - indexes that the planner might have made use of in the previous step, had they been available. 1. A subset of the data in the user database is used to generate statistics for all existing indexes and the candidate indexes generated in step 3 above. 1. The SQL queries are prepared a second time. If the planner uses any of the indexes created in step 3, they are recommended to the user. # C API The SQLite expert C API is defined in sqlite3expert.h. Most uses will proceed as follows: 1. An sqlite3expert object is created by calling **sqlite3\_expert\_new()**. A database handle opened by the user is passed as an argument. 1. The sqlite3expert object is configured with one or more SQL statements by making one or more calls to **sqlite3\_expert\_sql()**. Each call may specify a single SQL statement, or multiple statements separated by semi-colons. 1. Optionally, the **sqlite3\_expert\_config()** API may be used to configure the size of the data subset used to generate index statistics. Using a smaller subset of the data can speed up the analysis. 1. **sqlite3\_expert\_analyze()** is called to run the analysis. 1. One or more calls are made to **sqlite3\_expert\_report()** to extract components of the results of the analysis. 1. **sqlite3\_expert\_destroy()** is called to free all resources. Refer to comments in sqlite3expert.h for further details. # sqlite3_expert application The file "expert.c" contains the code for a command line application that uses the API described above. It can be compiled with (for example): <pre> gcc -O2 sqlite3.c expert.c sqlite3expert.c -o sqlite3_expert </pre> Assuming the database is named "test.db", it can then be run to analyze a single query: <pre> ./sqlite3_expert -sql <sql-query> test.db </pre> Or an entire text file worth of queries with: <pre> ./sqlite3_expert -file <text-file> test.db </pre> By default, sqlite3\_expert generates index statistics using all the data in the user database. For a large database, this may be prohibitively time consuming. The "-sample" option may be used to configure sqlite3\_expert to generate statistics based on an integer percentage of the user database as follows: <pre> # Generate statistics based on 25% of the user database rows: ./sqlite3_expert -sample 25 -sql <sql-query> test.db # Do not generate any statistics at all: ./sqlite3_expert -sample 0 -sql <sql-query> test.db </pre> |
Added ext/expert/expert.c.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 | /* ** 2017 April 07 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* */ #include <sqlite3.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include "sqlite3expert.h" static void option_requires_argument(const char *zOpt){ fprintf(stderr, "Option requires an argument: %s\n", zOpt); exit(-3); } static int option_integer_arg(const char *zVal){ return atoi(zVal); } static void usage(char **argv){ fprintf(stderr, "\n"); fprintf(stderr, "Usage %s ?OPTIONS? DATABASE\n", argv[0]); fprintf(stderr, "\n"); fprintf(stderr, "Options are:\n"); fprintf(stderr, " -sql SQL (analyze SQL statements passed as argument)\n"); fprintf(stderr, " -file FILE (read SQL statements from file FILE)\n"); fprintf(stderr, " -verbose LEVEL (integer verbosity level. default 1)\n"); fprintf(stderr, " -sample PERCENT (percent of db to sample. default 100)\n"); exit(-1); } static int readSqlFromFile(sqlite3expert *p, const char *zFile, char **pzErr){ FILE *in = fopen(zFile, "rb"); long nIn; size_t nRead; char *pBuf; int rc; if( in==0 ){ *pzErr = sqlite3_mprintf("failed to open file %s\n", zFile); return SQLITE_ERROR; } fseek(in, 0, SEEK_END); nIn = ftell(in); rewind(in); pBuf = sqlite3_malloc64( nIn+1 ); nRead = fread(pBuf, nIn, 1, in); fclose(in); if( nRead!=1 ){ sqlite3_free(pBuf); *pzErr = sqlite3_mprintf("failed to read file %s\n", zFile); return SQLITE_ERROR; } pBuf[nIn] = 0; rc = sqlite3_expert_sql(p, pBuf, pzErr); sqlite3_free(pBuf); return rc; } int main(int argc, char **argv){ const char *zDb; int rc = 0; char *zErr = 0; int i; int iVerbose = 1; /* -verbose option */ sqlite3 *db = 0; sqlite3expert *p = 0; if( argc<2 ) usage(argv); zDb = argv[argc-1]; if( zDb[0]=='-' ) usage(argv); rc = sqlite3_open(zDb, &db); if( rc!=SQLITE_OK ){ fprintf(stderr, "Cannot open db file: %s - %s\n", zDb, sqlite3_errmsg(db)); exit(-2); } p = sqlite3_expert_new(db, &zErr); if( p==0 ){ fprintf(stderr, "Cannot run analysis: %s\n", zErr); rc = 1; }else{ for(i=1; i<(argc-1); i++){ char *zArg = argv[i]; if( zArg[0]=='-' && zArg[1]=='-' && zArg[2]!=0 ) zArg++; int nArg = (int)strlen(zArg); if( nArg>=2 && 0==sqlite3_strnicmp(zArg, "-file", nArg) ){ if( ++i==(argc-1) ) option_requires_argument("-file"); rc = readSqlFromFile(p, argv[i], &zErr); } else if( nArg>=3 && 0==sqlite3_strnicmp(zArg, "-sql", nArg) ){ if( ++i==(argc-1) ) option_requires_argument("-sql"); rc = sqlite3_expert_sql(p, argv[i], &zErr); } else if( nArg>=3 && 0==sqlite3_strnicmp(zArg, "-sample", nArg) ){ int iSample; if( ++i==(argc-1) ) option_requires_argument("-sample"); iSample = option_integer_arg(argv[i]); sqlite3_expert_config(p, EXPERT_CONFIG_SAMPLE, iSample); } else if( nArg>=2 && 0==sqlite3_strnicmp(zArg, "-verbose", nArg) ){ if( ++i==(argc-1) ) option_requires_argument("-verbose"); iVerbose = option_integer_arg(argv[i]); } else{ usage(argv); } } } if( rc==SQLITE_OK ){ rc = sqlite3_expert_analyze(p, &zErr); } if( rc==SQLITE_OK ){ int nQuery = sqlite3_expert_count(p); if( iVerbose>0 ){ const char *zCand = sqlite3_expert_report(p,0,EXPERT_REPORT_CANDIDATES); fprintf(stdout, "-- Candidates -------------------------------\n"); fprintf(stdout, "%s\n", zCand); } for(i=0; i<nQuery; i++){ const char *zSql = sqlite3_expert_report(p, i, EXPERT_REPORT_SQL); const char *zIdx = sqlite3_expert_report(p, i, EXPERT_REPORT_INDEXES); const char *zEQP = sqlite3_expert_report(p, i, EXPERT_REPORT_PLAN); if( zIdx==0 ) zIdx = "(no new indexes)\n"; if( iVerbose>0 ){ fprintf(stdout, "-- Query %d ----------------------------------\n",i+1); fprintf(stdout, "%s\n\n", zSql); } fprintf(stdout, "%s\n%s\n", zIdx, zEQP); } }else{ fprintf(stderr, "Error: %s\n", zErr ? zErr : "?"); } sqlite3_expert_destroy(p); sqlite3_free(zErr); return rc; } |
Added ext/expert/expert1.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 | # 2009 Nov 11 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # The focus of this file is testing the CLI shell tool. Specifically, # the ".recommend" command. # # # Test plan: # # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } source $testdir/tester.tcl set testprefix expert1 if {$tcl_platform(platform)=="windows"} { set CMD "sqlite3_expert.exe" } else { set CMD ".././sqlite3_expert" } proc squish {txt} { regsub -all {[[:space:]]+} $txt { } } proc do_setup_rec_test {tn setup sql res} { reset_db db eval $setup uplevel [list do_rec_test $tn $sql $res] } foreach {tn setup} { 1 { if {![file executable $CMD]} { continue } proc do_rec_test {tn sql res} { set res [squish [string trim $res]] set tst [subst -nocommands { squish [string trim [exec $::CMD -verbose 0 -sql {$sql;} test.db]] }] uplevel [list do_test $tn $tst $res] } } 2 { if {[info commands sqlite3_expert_new]==""} { continue } proc do_rec_test {tn sql res} { set expert [sqlite3_expert_new db] $expert sql $sql $expert analyze set result [list] for {set i 0} {$i < [$expert count]} {incr i} { set idx [string trim [$expert report $i indexes]] if {$idx==""} {set idx "(no new indexes)"} lappend result $idx lappend result [string trim [$expert report $i plan]] } $expert destroy set tst [subst -nocommands {set {} [squish [join {$result}]]}] uplevel [list do_test $tn $tst [string trim [squish $res]]] } } } { eval $setup do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } { SELECT * FROM t1 } { (no new indexes) 0|0|0|SCAN TABLE t1 } do_setup_rec_test $tn.2 { CREATE TABLE t1(a, b, c); } { SELECT * FROM t1 WHERE b>?; } { CREATE INDEX t1_idx_00000062 ON t1(b); 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?) } do_setup_rec_test $tn.3 { CREATE TABLE t1(a, b, c); } { SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ? } { CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE); 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?) } do_setup_rec_test $tn.4 { CREATE TABLE t1(a, b, c); } { SELECT a FROM t1 ORDER BY b; } { CREATE INDEX t1_idx_00000062 ON t1(b); 0|0|0|SCAN TABLE t1 USING INDEX t1_idx_00000062 } do_setup_rec_test $tn.5 { CREATE TABLE t1(a, b, c); } { SELECT a FROM t1 WHERE a=? ORDER BY b; } { CREATE INDEX t1_idx_000123a7 ON t1(a, b); 0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?) } do_setup_rec_test $tn.6 { CREATE TABLE t1(a, b, c); } { SELECT min(a) FROM t1 } { CREATE INDEX t1_idx_00000061 ON t1(a); 0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061 } do_setup_rec_test $tn.7 { CREATE TABLE t1(a, b, c); } { SELECT * FROM t1 ORDER BY a, b, c; } { CREATE INDEX t1_idx_033e95fe ON t1(a, b, c); 0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe } #do_setup_rec_test $tn.1.8 { # CREATE TABLE t1(a, b, c); #} { # SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC; #} { # CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c); # 0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222 #} do_setup_rec_test $tn.8.1 { CREATE TABLE t1(a COLLATE NOCase, b, c); } { SELECT * FROM t1 WHERE a=? } { CREATE INDEX t1_idx_00000061 ON t1(a); 0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?) } do_setup_rec_test $tn.8.2 { CREATE TABLE t1(a, b COLLATE nocase, c); } { SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC; } { CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c); 0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5cb97285 } # Tables with names that require quotes. # do_setup_rec_test $tn.9.1 { CREATE TABLE "t t"(a, b, c); } { SELECT * FROM "t t" WHERE a=? } { CREATE INDEX 't t_idx_00000061' ON 't t'(a); 0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) } do_setup_rec_test $tn.9.2 { CREATE TABLE "t t"(a, b, c); } { SELECT * FROM "t t" WHERE b BETWEEN ? AND ? } { CREATE INDEX 't t_idx_00000062' ON 't t'(b); 0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?) } # Columns with names that require quotes. # do_setup_rec_test $tn.10.1 { CREATE TABLE t3(a, "b b", c); } { SELECT * FROM t3 WHERE "b b" = ? } { CREATE INDEX t3_idx_00050c52 ON t3('b b'); 0|0|0|SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?) } do_setup_rec_test $tn.10.2 { CREATE TABLE t3(a, "b b", c); } { SELECT * FROM t3 ORDER BY "b b" } { CREATE INDEX t3_idx_00050c52 ON t3('b b'); 0|0|0|SCAN TABLE t3 USING INDEX t3_idx_00050c52 } # Transitive constraints # do_setup_rec_test $tn.11.1 { CREATE TABLE t5(a, b); CREATE TABLE t6(c, d); } { SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=? } { CREATE INDEX t5_idx_000123a7 ON t5(a, b); CREATE INDEX t6_idx_00000063 ON t6(c); 0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?) } # OR terms. # do_setup_rec_test $tn.12.1 { CREATE TABLE t7(a, b); } { SELECT * FROM t7 WHERE a=? OR b=? } { CREATE INDEX t7_idx_00000062 ON t7(b); CREATE INDEX t7_idx_00000061 ON t7(a); 0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?) } # rowid terms. # do_setup_rec_test $tn.13.1 { CREATE TABLE t8(a, b); } { SELECT * FROM t8 WHERE rowid=? } { (no new indexes) 0|0|0|SEARCH TABLE t8 USING INTEGER PRIMARY KEY (rowid=?) } do_setup_rec_test $tn.13.2 { CREATE TABLE t8(a, b); } { SELECT * FROM t8 ORDER BY rowid } { (no new indexes) 0|0|0|SCAN TABLE t8 } do_setup_rec_test $tn.13.3 { CREATE TABLE t8(a, b); } { SELECT * FROM t8 WHERE a=? ORDER BY rowid } { CREATE INDEX t8_idx_00000061 ON t8(a); 0|0|0|SEARCH TABLE t8 USING INDEX t8_idx_00000061 (a=?) } # Triggers # do_setup_rec_test $tn.14 { CREATE TABLE t9(a, b, c); CREATE TABLE t10(a, b, c); CREATE TRIGGER t9t AFTER INSERT ON t9 BEGIN UPDATE t10 SET a=new.a WHERE b = new.b; END; } { INSERT INTO t9 VALUES(?, ?, ?); } { CREATE INDEX t10_idx_00000062 ON t10(b); 0|0|0|SEARCH TABLE t10 USING INDEX t10_idx_00000062 (b=?) } do_setup_rec_test $tn.15 { CREATE TABLE t1(a, b); CREATE TABLE t2(c, d); WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s; WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s; } { SELECT * FROM t2, t1 WHERE b=? AND d=? AND t2.rowid=t1.rowid } { CREATE INDEX t2_idx_00000064 ON t2(d); 0|0|0|SEARCH TABLE t2 USING INDEX t2_idx_00000064 (d=?) 0|1|1|SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) } } proc do_candidates_test {tn sql res} { set res [squish [string trim $res]] set expert [sqlite3_expert_new db] $expert sql $sql $expert analyze set candidates [squish [string trim [$expert report 0 candidates]]] $expert destroy uplevel [list do_test $tn [list set {} $candidates] $res] } reset_db do_execsql_test 3.0 { CREATE TABLE t1(a, b); CREATE TABLE t2(c, d); WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s; WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100) INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s; } do_candidates_test 3.1 { SELECT * FROM t1,t2 WHERE (b=? OR a=?) AND (c=? OR d=?) } { CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20 CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50 CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20 CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5 } do_candidates_test 3.2 { SELECT * FROM t1,t2 WHERE a=? AND b=? AND c=? AND d=? } { CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 17 CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5 } do_execsql_test 3.2 { CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50 CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20 CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 16 CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20 CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5 CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5 ANALYZE; SELECT * FROM sqlite_stat1 ORDER BY 1, 2; } { t1 t1_idx_00000061 {100 50} t1 t1_idx_00000062 {100 20} t1 t1_idx_000123a7 {100 50 17} t2 t2_idx_00000063 {100 20} t2 t2_idx_00000064 {100 5} t2 t2_idx_0001295b {100 20 5} } finish_test |
Added ext/expert/sqlite3expert.c.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 | /* ** 2017 April 09 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* */ #include "sqlite3expert.h" #include <assert.h> #include <string.h> #include <stdio.h> typedef sqlite3_int64 i64; typedef sqlite3_uint64 u64; typedef struct IdxColumn IdxColumn; typedef struct IdxConstraint IdxConstraint; typedef struct IdxScan IdxScan; typedef struct IdxStatement IdxStatement; typedef struct IdxTable IdxTable; typedef struct IdxWrite IdxWrite; #define STRLEN (int)strlen /* ** A temp table name that we assume no user database will actually use. ** If this assumption proves incorrect triggers on the table with the ** conflicting name will be ignored. */ #define UNIQUE_TABLE_NAME "t592690916721053953805701627921227776" /* ** A single constraint. Equivalent to either "col = ?" or "col < ?" (or ** any other type of single-ended range constraint on a column). ** ** pLink: ** Used to temporarily link IdxConstraint objects into lists while ** creating candidate indexes. */ struct IdxConstraint { char *zColl; /* Collation sequence */ int bRange; /* True for range, false for eq */ int iCol; /* Constrained table column */ int bFlag; /* Used by idxFindCompatible() */ int bDesc; /* True if ORDER BY <expr> DESC */ IdxConstraint *pNext; /* Next constraint in pEq or pRange list */ IdxConstraint *pLink; /* See above */ }; /* ** A single scan of a single table. */ struct IdxScan { IdxTable *pTab; /* Associated table object */ int iDb; /* Database containing table zTable */ i64 covering; /* Mask of columns required for cov. index */ IdxConstraint *pOrder; /* ORDER BY columns */ IdxConstraint *pEq; /* List of == constraints */ IdxConstraint *pRange; /* List of < constraints */ IdxScan *pNextScan; /* Next IdxScan object for same analysis */ }; /* ** Information regarding a single database table. Extracted from ** "PRAGMA table_info" by function idxGetTableInfo(). */ struct IdxColumn { char *zName; char *zColl; int iPk; }; struct IdxTable { int nCol; char *zName; /* Table name */ IdxColumn *aCol; IdxTable *pNext; /* Next table in linked list of all tables */ }; /* ** An object of the following type is created for each unique table/write-op ** seen. The objects are stored in a singly-linked list beginning at ** sqlite3expert.pWrite. */ struct IdxWrite { IdxTable *pTab; int eOp; /* SQLITE_UPDATE, DELETE or INSERT */ IdxWrite *pNext; }; /* ** Each statement being analyzed is represented by an instance of this ** structure. */ struct IdxStatement { int iId; /* Statement number */ char *zSql; /* SQL statement */ char *zIdx; /* Indexes */ char *zEQP; /* Plan */ IdxStatement *pNext; }; /* ** A hash table for storing strings. With space for a payload string ** with each entry. Methods are: ** ** idxHashInit() ** idxHashClear() ** idxHashAdd() ** idxHashSearch() */ #define IDX_HASH_SIZE 1023 typedef struct IdxHashEntry IdxHashEntry; typedef struct IdxHash IdxHash; struct IdxHashEntry { char *zKey; /* nul-terminated key */ char *zVal; /* nul-terminated value string */ char *zVal2; /* nul-terminated value string 2 */ IdxHashEntry *pHashNext; /* Next entry in same hash bucket */ IdxHashEntry *pNext; /* Next entry in hash */ }; struct IdxHash { IdxHashEntry *pFirst; IdxHashEntry *aHash[IDX_HASH_SIZE]; }; /* ** sqlite3expert object. */ struct sqlite3expert { int iSample; /* Percentage of tables to sample for stat1 */ sqlite3 *db; /* User database */ sqlite3 *dbm; /* In-memory db for this analysis */ sqlite3 *dbv; /* Vtab schema for this analysis */ IdxTable *pTable; /* List of all IdxTable objects */ IdxScan *pScan; /* List of scan objects */ IdxWrite *pWrite; /* List of write objects */ IdxStatement *pStatement; /* List of IdxStatement objects */ int bRun; /* True once analysis has run */ char **pzErrmsg; int rc; /* Error code from whereinfo hook */ IdxHash hIdx; /* Hash containing all candidate indexes */ char *zCandidates; /* For EXPERT_REPORT_CANDIDATES */ }; /* ** Allocate and return nByte bytes of zeroed memory using sqlite3_malloc(). ** If the allocation fails, set *pRc to SQLITE_NOMEM and return NULL. */ static void *idxMalloc(int *pRc, int nByte){ void *pRet; assert( *pRc==SQLITE_OK ); assert( nByte>0 ); pRet = sqlite3_malloc(nByte); if( pRet ){ memset(pRet, 0, nByte); }else{ *pRc = SQLITE_NOMEM; } return pRet; } /* ** Initialize an IdxHash hash table. */ static void idxHashInit(IdxHash *pHash){ memset(pHash, 0, sizeof(IdxHash)); } /* ** Reset an IdxHash hash table. */ static void idxHashClear(IdxHash *pHash){ int i; for(i=0; i<IDX_HASH_SIZE; i++){ IdxHashEntry *pEntry; IdxHashEntry *pNext; for(pEntry=pHash->aHash[i]; pEntry; pEntry=pNext){ pNext = pEntry->pHashNext; sqlite3_free(pEntry->zVal2); sqlite3_free(pEntry); } } memset(pHash, 0, sizeof(IdxHash)); } /* ** Return the index of the hash bucket that the string specified by the ** arguments to this function belongs. */ static int idxHashString(const char *z, int n){ unsigned int ret = 0; int i; for(i=0; i<n; i++){ ret += (ret<<3) + (unsigned char)(z[i]); } return (int)(ret % IDX_HASH_SIZE); } /* ** If zKey is already present in the hash table, return non-zero and do ** nothing. Otherwise, add an entry with key zKey and payload string zVal to ** the hash table passed as the second argument. */ static int idxHashAdd( int *pRc, IdxHash *pHash, const char *zKey, const char *zVal ){ int nKey = STRLEN(zKey); int iHash = idxHashString(zKey, nKey); int nVal = (zVal ? STRLEN(zVal) : 0); IdxHashEntry *pEntry; assert( iHash>=0 ); for(pEntry=pHash->aHash[iHash]; pEntry; pEntry=pEntry->pHashNext){ if( STRLEN(pEntry->zKey)==nKey && 0==memcmp(pEntry->zKey, zKey, nKey) ){ return 1; } } pEntry = idxMalloc(pRc, sizeof(IdxHashEntry) + nKey+1 + nVal+1); if( pEntry ){ pEntry->zKey = (char*)&pEntry[1]; memcpy(pEntry->zKey, zKey, nKey); if( zVal ){ pEntry->zVal = &pEntry->zKey[nKey+1]; memcpy(pEntry->zVal, zVal, nVal); } pEntry->pHashNext = pHash->aHash[iHash]; pHash->aHash[iHash] = pEntry; pEntry->pNext = pHash->pFirst; pHash->pFirst = pEntry; } return 0; } /* ** If zKey/nKey is present in the hash table, return a pointer to the ** hash-entry object. */ static IdxHashEntry *idxHashFind(IdxHash *pHash, const char *zKey, int nKey){ int iHash; IdxHashEntry *pEntry; if( nKey<0 ) nKey = STRLEN(zKey); iHash = idxHashString(zKey, nKey); assert( iHash>=0 ); for(pEntry=pHash->aHash[iHash]; pEntry; pEntry=pEntry->pHashNext){ if( STRLEN(pEntry->zKey)==nKey && 0==memcmp(pEntry->zKey, zKey, nKey) ){ return pEntry; } } return 0; } /* ** If the hash table contains an entry with a key equal to the string ** passed as the final two arguments to this function, return a pointer ** to the payload string. Otherwise, if zKey/nKey is not present in the ** hash table, return NULL. */ static const char *idxHashSearch(IdxHash *pHash, const char *zKey, int nKey){ IdxHashEntry *pEntry = idxHashFind(pHash, zKey, nKey); if( pEntry ) return pEntry->zVal; return 0; } /* ** Allocate and return a new IdxConstraint object. Set the IdxConstraint.zColl ** variable to point to a copy of nul-terminated string zColl. */ static IdxConstraint *idxNewConstraint(int *pRc, const char *zColl){ IdxConstraint *pNew; int nColl = STRLEN(zColl); assert( *pRc==SQLITE_OK ); pNew = (IdxConstraint*)idxMalloc(pRc, sizeof(IdxConstraint) * nColl + 1); if( pNew ){ pNew->zColl = (char*)&pNew[1]; memcpy(pNew->zColl, zColl, nColl+1); } return pNew; } /* ** An error associated with database handle db has just occurred. Pass ** the error message to callback function xOut. */ static void idxDatabaseError( sqlite3 *db, /* Database handle */ char **pzErrmsg /* Write error here */ ){ *pzErrmsg = sqlite3_mprintf("%s", sqlite3_errmsg(db)); } /* ** Prepare an SQL statement. */ static int idxPrepareStmt( sqlite3 *db, /* Database handle to compile against */ sqlite3_stmt **ppStmt, /* OUT: Compiled SQL statement */ char **pzErrmsg, /* OUT: sqlite3_malloc()ed error message */ const char *zSql /* SQL statement to compile */ ){ int rc = sqlite3_prepare_v2(db, zSql, -1, ppStmt, 0); if( rc!=SQLITE_OK ){ *ppStmt = 0; idxDatabaseError(db, pzErrmsg); } return rc; } /* ** Prepare an SQL statement using the results of a printf() formatting. */ static int idxPrintfPrepareStmt( sqlite3 *db, /* Database handle to compile against */ sqlite3_stmt **ppStmt, /* OUT: Compiled SQL statement */ char **pzErrmsg, /* OUT: sqlite3_malloc()ed error message */ const char *zFmt, /* printf() format of SQL statement */ ... /* Trailing printf() arguments */ ){ va_list ap; int rc; char *zSql; va_start(ap, zFmt); zSql = sqlite3_vmprintf(zFmt, ap); if( zSql==0 ){ rc = SQLITE_NOMEM; }else{ rc = idxPrepareStmt(db, ppStmt, pzErrmsg, zSql); sqlite3_free(zSql); } va_end(ap); return rc; } /************************************************************************* ** Beginning of virtual table implementation. */ typedef struct ExpertVtab ExpertVtab; struct ExpertVtab { sqlite3_vtab base; IdxTable *pTab; sqlite3expert *pExpert; }; typedef struct ExpertCsr ExpertCsr; struct ExpertCsr { sqlite3_vtab_cursor base; sqlite3_stmt *pData; }; static char *expertDequote(const char *zIn){ int n = STRLEN(zIn); char *zRet = sqlite3_malloc(n); assert( zIn[0]=='\'' ); assert( zIn[n-1]=='\'' ); if( zRet ){ int iOut = 0; int iIn = 0; for(iIn=1; iIn<(n-1); iIn++){ if( zIn[iIn]=='\'' ){ assert( zIn[iIn+1]=='\'' ); iIn++; } zRet[iOut++] = zIn[iIn]; } zRet[iOut] = '\0'; } return zRet; } /* ** This function is the implementation of both the xConnect and xCreate ** methods of the r-tree virtual table. ** ** argv[0] -> module name ** argv[1] -> database name ** argv[2] -> table name ** argv[...] -> column names... */ static int expertConnect( sqlite3 *db, void *pAux, int argc, const char *const*argv, sqlite3_vtab **ppVtab, char **pzErr ){ sqlite3expert *pExpert = (sqlite3expert*)pAux; ExpertVtab *p = 0; int rc; if( argc!=4 ){ *pzErr = sqlite3_mprintf("internal error!"); rc = SQLITE_ERROR; }else{ char *zCreateTable = expertDequote(argv[3]); if( zCreateTable ){ rc = sqlite3_declare_vtab(db, zCreateTable); if( rc==SQLITE_OK ){ p = idxMalloc(&rc, sizeof(ExpertVtab)); } if( rc==SQLITE_OK ){ p->pExpert = pExpert; p->pTab = pExpert->pTable; assert( sqlite3_stricmp(p->pTab->zName, argv[2])==0 ); } sqlite3_free(zCreateTable); }else{ rc = SQLITE_NOMEM; } } *ppVtab = (sqlite3_vtab*)p; return rc; } static int expertDisconnect(sqlite3_vtab *pVtab){ ExpertVtab *p = (ExpertVtab*)pVtab; sqlite3_free(p); return SQLITE_OK; } static int expertBestIndex(sqlite3_vtab *pVtab, sqlite3_index_info *pIdxInfo){ ExpertVtab *p = (ExpertVtab*)pVtab; sqlite3 *dbv = p->pExpert->dbv; int rc = SQLITE_OK; int n = 0; IdxScan *pScan; const int opmask = SQLITE_INDEX_CONSTRAINT_EQ | SQLITE_INDEX_CONSTRAINT_GT | SQLITE_INDEX_CONSTRAINT_LT | SQLITE_INDEX_CONSTRAINT_GE | SQLITE_INDEX_CONSTRAINT_LE; pScan = idxMalloc(&rc, sizeof(IdxScan)); if( pScan ){ int i; /* Link the new scan object into the list */ pScan->pTab = p->pTab; pScan->pNextScan = p->pExpert->pScan; p->pExpert->pScan = pScan; /* Add the constraints to the IdxScan object */ for(i=0; i<pIdxInfo->nConstraint; i++){ struct sqlite3_index_constraint *pCons = &pIdxInfo->aConstraint[i]; if( pCons->usable && pCons->iColumn>=0 && p->pTab->aCol[pCons->iColumn].iPk==0 && (pCons->op & opmask) ){ IdxConstraint *pNew; const char *zColl = sqlite3_vtab_collation(dbv, i); pNew = idxNewConstraint(&rc, zColl); if( pNew ){ pNew->iCol = pCons->iColumn; if( pCons->op==SQLITE_INDEX_CONSTRAINT_EQ ){ pNew->pNext = pScan->pEq; pScan->pEq = pNew; }else{ pNew->bRange = 1; pNew->pNext = pScan->pRange; pScan->pRange = pNew; } } n++; pIdxInfo->aConstraintUsage[i].argvIndex = n; } } /* Add the ORDER BY to the IdxScan object */ for(i=pIdxInfo->nOrderBy-1; i>=0; i--){ int iCol = pIdxInfo->aOrderBy[i].iColumn; if( iCol>=0 ){ IdxConstraint *pNew = idxNewConstraint(&rc, p->pTab->aCol[iCol].zColl); if( pNew ){ pNew->iCol = iCol; pNew->bDesc = pIdxInfo->aOrderBy[i].desc; pNew->pNext = pScan->pOrder; pNew->pLink = pScan->pOrder; pScan->pOrder = pNew; n++; } } } } pIdxInfo->estimatedCost = 1000000.0 / n; return rc; } static int expertUpdate( sqlite3_vtab *pVtab, int nData, sqlite3_value **azData, sqlite_int64 *pRowid ){ return SQLITE_OK; } /* ** Virtual table module xOpen method. */ static int expertOpen(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){ int rc = SQLITE_OK; ExpertCsr *pCsr; pCsr = idxMalloc(&rc, sizeof(ExpertCsr)); *ppCursor = (sqlite3_vtab_cursor*)pCsr; return rc; } /* ** Virtual table module xClose method. */ static int expertClose(sqlite3_vtab_cursor *cur){ ExpertCsr *pCsr = (ExpertCsr*)cur; sqlite3_finalize(pCsr->pData); sqlite3_free(pCsr); return SQLITE_OK; } /* ** Virtual table module xEof method. ** ** Return non-zero if the cursor does not currently point to a valid ** record (i.e if the scan has finished), or zero otherwise. */ static int expertEof(sqlite3_vtab_cursor *cur){ ExpertCsr *pCsr = (ExpertCsr*)cur; return pCsr->pData==0; } /* ** Virtual table module xNext method. */ static int expertNext(sqlite3_vtab_cursor *cur){ ExpertCsr *pCsr = (ExpertCsr*)cur; int rc = SQLITE_OK; assert( pCsr->pData ); rc = sqlite3_step(pCsr->pData); if( rc!=SQLITE_ROW ){ rc = sqlite3_finalize(pCsr->pData); pCsr->pData = 0; }else{ rc = SQLITE_OK; } return rc; } /* ** Virtual table module xRowid method. */ static int expertRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){ *pRowid = 0; return SQLITE_OK; } /* ** Virtual table module xColumn method. */ static int expertColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){ ExpertCsr *pCsr = (ExpertCsr*)cur; sqlite3_value *pVal; pVal = sqlite3_column_value(pCsr->pData, i); if( pVal ){ sqlite3_result_value(ctx, pVal); } return SQLITE_OK; } /* ** Virtual table module xFilter method. */ static int expertFilter( sqlite3_vtab_cursor *cur, int idxNum, const char *idxStr, int argc, sqlite3_value **argv ){ ExpertCsr *pCsr = (ExpertCsr*)cur; ExpertVtab *pVtab = (ExpertVtab*)(cur->pVtab); sqlite3expert *pExpert = pVtab->pExpert; int rc; rc = sqlite3_finalize(pCsr->pData); pCsr->pData = 0; if( rc==SQLITE_OK ){ rc = idxPrintfPrepareStmt(pExpert->db, &pCsr->pData, &pVtab->base.zErrMsg, "SELECT * FROM main.%Q WHERE sample()", pVtab->pTab->zName ); } if( rc==SQLITE_OK ){ rc = expertNext(cur); } return rc; } static int idxRegisterVtab(sqlite3expert *p){ static sqlite3_module expertModule = { 2, /* iVersion */ expertConnect, /* xCreate - create a table */ expertConnect, /* xConnect - connect to an existing table */ expertBestIndex, /* xBestIndex - Determine search strategy */ expertDisconnect, /* xDisconnect - Disconnect from a table */ expertDisconnect, /* xDestroy - Drop a table */ expertOpen, /* xOpen - open a cursor */ expertClose, /* xClose - close a cursor */ expertFilter, /* xFilter - configure scan constraints */ expertNext, /* xNext - advance a cursor */ expertEof, /* xEof */ expertColumn, /* xColumn - read data */ expertRowid, /* xRowid - read data */ expertUpdate, /* xUpdate - write data */ 0, /* xBegin - begin transaction */ 0, /* xSync - sync transaction */ 0, /* xCommit - commit transaction */ 0, /* xRollback - rollback transaction */ 0, /* xFindFunction - function overloading */ 0, /* xRename - rename the table */ 0, /* xSavepoint */ 0, /* xRelease */ 0, /* xRollbackTo */ }; return sqlite3_create_module(p->dbv, "expert", &expertModule, (void*)p); } /* ** End of virtual table implementation. *************************************************************************/ /* ** Finalize SQL statement pStmt. If (*pRc) is SQLITE_OK when this function ** is called, set it to the return value of sqlite3_finalize() before ** returning. Otherwise, discard the sqlite3_finalize() return value. */ static void idxFinalize(int *pRc, sqlite3_stmt *pStmt){ int rc = sqlite3_finalize(pStmt); if( *pRc==SQLITE_OK ) *pRc = rc; } /* ** Attempt to allocate an IdxTable structure corresponding to table zTab ** in the main database of connection db. If successful, set (*ppOut) to ** point to the new object and return SQLITE_OK. Otherwise, return an ** SQLite error code and set (*ppOut) to NULL. In this case *pzErrmsg may be ** set to point to an error string. ** ** It is the responsibility of the caller to eventually free either the ** IdxTable object or error message using sqlite3_free(). */ static int idxGetTableInfo( sqlite3 *db, /* Database connection to read details from */ const char *zTab, /* Table name */ IdxTable **ppOut, /* OUT: New object (if successful) */ char **pzErrmsg /* OUT: Error message (if not) */ ){ sqlite3_stmt *p1 = 0; int nCol = 0; int nTab = STRLEN(zTab); int nByte = sizeof(IdxTable) + nTab + 1; IdxTable *pNew = 0; int rc, rc2; char *pCsr = 0; rc = idxPrintfPrepareStmt(db, &p1, pzErrmsg, "PRAGMA table_info=%Q", zTab); while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){ const char *zCol = (const char*)sqlite3_column_text(p1, 1); nByte += 1 + STRLEN(zCol); rc = sqlite3_table_column_metadata( db, "main", zTab, zCol, 0, &zCol, 0, 0, 0 ); nByte += 1 + STRLEN(zCol); nCol++; } rc2 = sqlite3_reset(p1); if( rc==SQLITE_OK ) rc = rc2; nByte += sizeof(IdxColumn) * nCol; if( rc==SQLITE_OK ){ pNew = idxMalloc(&rc, nByte); } if( rc==SQLITE_OK ){ pNew->aCol = (IdxColumn*)&pNew[1]; pNew->nCol = nCol; pCsr = (char*)&pNew->aCol[nCol]; } nCol = 0; while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(p1) ){ const char *zCol = (const char*)sqlite3_column_text(p1, 1); int nCopy = STRLEN(zCol) + 1; pNew->aCol[nCol].zName = pCsr; pNew->aCol[nCol].iPk = sqlite3_column_int(p1, 5); memcpy(pCsr, zCol, nCopy); pCsr += nCopy; rc = sqlite3_table_column_metadata( db, "main", zTab, zCol, 0, &zCol, 0, 0, 0 ); if( rc==SQLITE_OK ){ nCopy = STRLEN(zCol) + 1; pNew->aCol[nCol].zColl = pCsr; memcpy(pCsr, zCol, nCopy); pCsr += nCopy; } nCol++; } idxFinalize(&rc, p1); if( rc!=SQLITE_OK ){ sqlite3_free(pNew); pNew = 0; }else{ pNew->zName = pCsr; memcpy(pNew->zName, zTab, nTab+1); } *ppOut = pNew; return rc; } /* ** This function is a no-op if *pRc is set to anything other than ** SQLITE_OK when it is called. ** ** If *pRc is initially set to SQLITE_OK, then the text specified by ** the printf() style arguments is appended to zIn and the result returned ** in a buffer allocated by sqlite3_malloc(). sqlite3_free() is called on ** zIn before returning. */ static char *idxAppendText(int *pRc, char *zIn, const char *zFmt, ...){ va_list ap; char *zAppend = 0; char *zRet = 0; int nIn = zIn ? STRLEN(zIn) : 0; int nAppend = 0; va_start(ap, zFmt); if( *pRc==SQLITE_OK ){ zAppend = sqlite3_vmprintf(zFmt, ap); if( zAppend ){ nAppend = STRLEN(zAppend); zRet = (char*)sqlite3_malloc(nIn + nAppend + 1); } if( zAppend && zRet ){ memcpy(zRet, zIn, nIn); memcpy(&zRet[nIn], zAppend, nAppend+1); }else{ sqlite3_free(zRet); zRet = 0; *pRc = SQLITE_NOMEM; } sqlite3_free(zAppend); sqlite3_free(zIn); } va_end(ap); return zRet; } /* ** Return true if zId must be quoted in order to use it as an SQL ** identifier, or false otherwise. */ static int idxIdentifierRequiresQuotes(const char *zId){ int i; for(i=0; zId[i]; i++){ if( !(zId[i]=='_') && !(zId[i]>='0' && zId[i]<='9') && !(zId[i]>='a' && zId[i]<='z') && !(zId[i]>='A' && zId[i]<='Z') ){ return 1; } } return 0; } /* ** This function appends an index column definition suitable for constraint ** pCons to the string passed as zIn and returns the result. */ static char *idxAppendColDefn( int *pRc, /* IN/OUT: Error code */ char *zIn, /* Column defn accumulated so far */ IdxTable *pTab, /* Table index will be created on */ IdxConstraint *pCons ){ char *zRet = zIn; IdxColumn *p = &pTab->aCol[pCons->iCol]; if( zRet ) zRet = idxAppendText(pRc, zRet, ", "); if( idxIdentifierRequiresQuotes(p->zName) ){ zRet = idxAppendText(pRc, zRet, "%Q", p->zName); }else{ zRet = idxAppendText(pRc, zRet, "%s", p->zName); } if( sqlite3_stricmp(p->zColl, pCons->zColl) ){ if( idxIdentifierRequiresQuotes(pCons->zColl) ){ zRet = idxAppendText(pRc, zRet, " COLLATE %Q", pCons->zColl); }else{ zRet = idxAppendText(pRc, zRet, " COLLATE %s", pCons->zColl); } } if( pCons->bDesc ){ zRet = idxAppendText(pRc, zRet, " DESC"); } return zRet; } /* ** Search database dbm for an index compatible with the one idxCreateFromCons() ** would create from arguments pScan, pEq and pTail. If no error occurs and ** such an index is found, return non-zero. Or, if no such index is found, ** return zero. ** ** If an error occurs, set *pRc to an SQLite error code and return zero. */ static int idxFindCompatible( int *pRc, /* OUT: Error code */ sqlite3* dbm, /* Database to search */ IdxScan *pScan, /* Scan for table to search for index on */ IdxConstraint *pEq, /* List of == constraints */ IdxConstraint *pTail /* List of range constraints */ ){ const char *zTbl = pScan->pTab->zName; sqlite3_stmt *pIdxList = 0; IdxConstraint *pIter; int nEq = 0; /* Number of elements in pEq */ int rc; /* Count the elements in list pEq */ for(pIter=pEq; pIter; pIter=pIter->pLink) nEq++; rc = idxPrintfPrepareStmt(dbm, &pIdxList, 0, "PRAGMA index_list=%Q", zTbl); while( rc==SQLITE_OK && sqlite3_step(pIdxList)==SQLITE_ROW ){ int bMatch = 1; IdxConstraint *pT = pTail; sqlite3_stmt *pInfo = 0; const char *zIdx = (const char*)sqlite3_column_text(pIdxList, 1); /* Zero the IdxConstraint.bFlag values in the pEq list */ for(pIter=pEq; pIter; pIter=pIter->pLink) pIter->bFlag = 0; rc = idxPrintfPrepareStmt(dbm, &pInfo, 0, "PRAGMA index_xInfo=%Q", zIdx); while( rc==SQLITE_OK && sqlite3_step(pInfo)==SQLITE_ROW ){ int iIdx = sqlite3_column_int(pInfo, 0); int iCol = sqlite3_column_int(pInfo, 1); const char *zColl = (const char*)sqlite3_column_text(pInfo, 4); if( iIdx<nEq ){ for(pIter=pEq; pIter; pIter=pIter->pLink){ if( pIter->bFlag ) continue; if( pIter->iCol!=iCol ) continue; if( sqlite3_stricmp(pIter->zColl, zColl) ) continue; pIter->bFlag = 1; break; } if( pIter==0 ){ bMatch = 0; break; } }else{ if( pT ){ if( pT->iCol!=iCol || sqlite3_stricmp(pT->zColl, zColl) ){ bMatch = 0; break; } pT = pT->pLink; } } } idxFinalize(&rc, pInfo); if( rc==SQLITE_OK && bMatch ){ sqlite3_finalize(pIdxList); return 1; } } idxFinalize(&rc, pIdxList); *pRc = rc; return 0; } static int idxCreateFromCons( sqlite3expert *p, IdxScan *pScan, IdxConstraint *pEq, IdxConstraint *pTail ){ sqlite3 *dbm = p->dbm; int rc = SQLITE_OK; if( (pEq || pTail) && 0==idxFindCompatible(&rc, dbm, pScan, pEq, pTail) ){ IdxTable *pTab = pScan->pTab; char *zCols = 0; char *zIdx = 0; IdxConstraint *pCons; int h = 0; const char *zFmt; for(pCons=pEq; pCons; pCons=pCons->pLink){ zCols = idxAppendColDefn(&rc, zCols, pTab, pCons); } for(pCons=pTail; pCons; pCons=pCons->pLink){ zCols = idxAppendColDefn(&rc, zCols, pTab, pCons); } if( rc==SQLITE_OK ){ /* Hash the list of columns to come up with a name for the index */ const char *zTable = pScan->pTab->zName; char *zName; /* Index name */ int i; for(i=0; zCols[i]; i++){ h += ((h<<3) + zCols[i]); } zName = sqlite3_mprintf("%s_idx_%08x", zTable, h); if( zName==0 ){ rc = SQLITE_NOMEM; }else{ if( idxIdentifierRequiresQuotes(zTable) ){ zFmt = "CREATE INDEX '%q' ON %Q(%s)"; }else{ zFmt = "CREATE INDEX %s ON %s(%s)"; } zIdx = sqlite3_mprintf(zFmt, zName, zTable, zCols); if( !zIdx ){ rc = SQLITE_NOMEM; }else{ rc = sqlite3_exec(dbm, zIdx, 0, 0, p->pzErrmsg); idxHashAdd(&rc, &p->hIdx, zName, zIdx); } sqlite3_free(zName); sqlite3_free(zIdx); } } sqlite3_free(zCols); } return rc; } /* ** Return true if list pList (linked by IdxConstraint.pLink) contains ** a constraint compatible with *p. Otherwise return false. */ static int idxFindConstraint(IdxConstraint *pList, IdxConstraint *p){ IdxConstraint *pCmp; for(pCmp=pList; pCmp; pCmp=pCmp->pLink){ if( p->iCol==pCmp->iCol ) return 1; } return 0; } static int idxCreateFromWhere( sqlite3expert *p, IdxScan *pScan, /* Create indexes for this scan */ IdxConstraint *pTail /* range/ORDER BY constraints for inclusion */ ){ IdxConstraint *p1 = 0; IdxConstraint *pCon; int rc; /* Gather up all the == constraints. */ for(pCon=pScan->pEq; pCon; pCon=pCon->pNext){ if( !idxFindConstraint(p1, pCon) && !idxFindConstraint(pTail, pCon) ){ pCon->pLink = p1; p1 = pCon; } } /* Create an index using the == constraints collected above. And the ** range constraint/ORDER BY terms passed in by the caller, if any. */ rc = idxCreateFromCons(p, pScan, p1, pTail); /* If no range/ORDER BY passed by the caller, create a version of the ** index for each range constraint. */ if( pTail==0 ){ for(pCon=pScan->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){ assert( pCon->pLink==0 ); if( !idxFindConstraint(p1, pCon) && !idxFindConstraint(pTail, pCon) ){ rc = idxCreateFromCons(p, pScan, p1, pCon); } } } return rc; } /* ** Create candidate indexes in database [dbm] based on the data in ** linked-list pScan. */ static int idxCreateCandidates(sqlite3expert *p, char **pzErr){ int rc = SQLITE_OK; IdxScan *pIter; for(pIter=p->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){ rc = idxCreateFromWhere(p, pIter, 0); if( rc==SQLITE_OK && pIter->pOrder ){ rc = idxCreateFromWhere(p, pIter, pIter->pOrder); } } return rc; } /* ** Free all elements of the linked list starting at pConstraint. */ static void idxConstraintFree(IdxConstraint *pConstraint){ IdxConstraint *pNext; IdxConstraint *p; for(p=pConstraint; p; p=pNext){ pNext = p->pNext; sqlite3_free(p); } } /* ** Free all elements of the linked list starting from pScan up until pLast ** (pLast is not freed). */ static void idxScanFree(IdxScan *pScan, IdxScan *pLast){ IdxScan *p; IdxScan *pNext; for(p=pScan; p!=pLast; p=pNext){ pNext = p->pNextScan; idxConstraintFree(p->pOrder); idxConstraintFree(p->pEq); idxConstraintFree(p->pRange); sqlite3_free(p); } } /* ** Free all elements of the linked list starting from pStatement up ** until pLast (pLast is not freed). */ static void idxStatementFree(IdxStatement *pStatement, IdxStatement *pLast){ IdxStatement *p; IdxStatement *pNext; for(p=pStatement; p!=pLast; p=pNext){ pNext = p->pNext; sqlite3_free(p->zEQP); sqlite3_free(p->zIdx); sqlite3_free(p); } } /* ** Free the linked list of IdxTable objects starting at pTab. */ static void idxTableFree(IdxTable *pTab){ IdxTable *pIter; IdxTable *pNext; for(pIter=pTab; pIter; pIter=pNext){ pNext = pIter->pNext; sqlite3_free(pIter); } } /* ** Free the linked list of IdxWrite objects starting at pTab. */ static void idxWriteFree(IdxWrite *pTab){ IdxWrite *pIter; IdxWrite *pNext; for(pIter=pTab; pIter; pIter=pNext){ pNext = pIter->pNext; sqlite3_free(pIter); } } /* ** This function is called after candidate indexes have been created. It ** runs all the queries to see which indexes they prefer, and populates ** IdxStatement.zIdx and IdxStatement.zEQP with the results. */ int idxFindIndexes( sqlite3expert *p, char **pzErr /* OUT: Error message (sqlite3_malloc) */ ){ IdxStatement *pStmt; sqlite3 *dbm = p->dbm; int rc = SQLITE_OK; IdxHash hIdx; idxHashInit(&hIdx); for(pStmt=p->pStatement; rc==SQLITE_OK && pStmt; pStmt=pStmt->pNext){ IdxHashEntry *pEntry; sqlite3_stmt *pExplain = 0; idxHashClear(&hIdx); rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr, "EXPLAIN QUERY PLAN %s", pStmt->zSql ); while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){ int iSelectid = sqlite3_column_int(pExplain, 0); int iOrder = sqlite3_column_int(pExplain, 1); int iFrom = sqlite3_column_int(pExplain, 2); const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3); int nDetail = STRLEN(zDetail); int i; for(i=0; i<nDetail; i++){ const char *zIdx = 0; if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){ zIdx = &zDetail[i+13]; }else if( memcmp(&zDetail[i], " USING COVERING INDEX ", 22)==0 ){ zIdx = &zDetail[i+22]; } if( zIdx ){ const char *zSql; int nIdx = 0; while( zIdx[nIdx]!='\0' && (zIdx[nIdx]!=' ' || zIdx[nIdx+1]!='(') ){ nIdx++; } zSql = idxHashSearch(&p->hIdx, zIdx, nIdx); if( zSql ){ idxHashAdd(&rc, &hIdx, zSql, 0); if( rc ) goto find_indexes_out; } break; } } pStmt->zEQP = idxAppendText(&rc, pStmt->zEQP, "%d|%d|%d|%s\n", iSelectid, iOrder, iFrom, zDetail ); } for(pEntry=hIdx.pFirst; pEntry; pEntry=pEntry->pNext){ pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s;\n", pEntry->zKey); } idxFinalize(&rc, pExplain); } find_indexes_out: idxHashClear(&hIdx); return rc; } static int idxAuthCallback( void *pCtx, int eOp, const char *z3, const char *z4, const char *zDb, const char *zTrigger ){ int rc = SQLITE_OK; if( eOp==SQLITE_INSERT || eOp==SQLITE_UPDATE || eOp==SQLITE_DELETE ){ if( sqlite3_stricmp(zDb, "main")==0 ){ sqlite3expert *p = (sqlite3expert*)pCtx; IdxTable *pTab; for(pTab=p->pTable; pTab; pTab=pTab->pNext){ if( 0==sqlite3_stricmp(z3, pTab->zName) ) break; } if( pTab ){ IdxWrite *pWrite; for(pWrite=p->pWrite; pWrite; pWrite=pWrite->pNext){ if( pWrite->pTab==pTab && pWrite->eOp==eOp ) break; } if( pWrite==0 ){ pWrite = idxMalloc(&rc, sizeof(IdxWrite)); if( rc==SQLITE_OK ){ pWrite->pTab = pTab; pWrite->eOp = eOp; pWrite->pNext = p->pWrite; p->pWrite = pWrite; } } } } } return rc; } static int idxProcessOneTrigger( sqlite3expert *p, IdxWrite *pWrite, char **pzErr ){ static const char *zInt = UNIQUE_TABLE_NAME; static const char *zDrop = "DROP TABLE " UNIQUE_TABLE_NAME; IdxTable *pTab = pWrite->pTab; const char *zTab = pTab->zName; const char *zSql = "SELECT 'CREATE TEMP' || substr(sql, 7) FROM sqlite_master " "WHERE tbl_name = %Q AND type IN ('table', 'trigger') " "ORDER BY type;"; sqlite3_stmt *pSelect = 0; int rc = SQLITE_OK; char *zWrite = 0; /* Create the table and its triggers in the temp schema */ rc = idxPrintfPrepareStmt(p->db, &pSelect, pzErr, zSql, zTab, zTab); while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSelect) ){ const char *zCreate = (const char*)sqlite3_column_text(pSelect, 0); rc = sqlite3_exec(p->dbv, zCreate, 0, 0, pzErr); } idxFinalize(&rc, pSelect); /* Rename the table in the temp schema to zInt */ if( rc==SQLITE_OK ){ char *z = sqlite3_mprintf("ALTER TABLE temp.%Q RENAME TO %Q", zTab, zInt); if( z==0 ){ rc = SQLITE_NOMEM; }else{ rc = sqlite3_exec(p->dbv, z, 0, 0, pzErr); sqlite3_free(z); } } switch( pWrite->eOp ){ case SQLITE_INSERT: { int i; zWrite = idxAppendText(&rc, zWrite, "INSERT INTO %Q VALUES(", zInt); for(i=0; i<pTab->nCol; i++){ zWrite = idxAppendText(&rc, zWrite, "%s?", i==0 ? "" : ", "); } zWrite = idxAppendText(&rc, zWrite, ")"); break; } case SQLITE_UPDATE: { int i; zWrite = idxAppendText(&rc, zWrite, "UPDATE %Q SET ", zInt); for(i=0; i<pTab->nCol; i++){ zWrite = idxAppendText(&rc, zWrite, "%s%Q=?", i==0 ? "" : ", ", pTab->aCol[i].zName ); } break; } default: { assert( pWrite->eOp==SQLITE_DELETE ); if( rc==SQLITE_OK ){ zWrite = sqlite3_mprintf("DELETE FROM %Q", zInt); if( zWrite==0 ) rc = SQLITE_NOMEM; } } } if( rc==SQLITE_OK ){ sqlite3_stmt *pX = 0; rc = sqlite3_prepare_v2(p->dbv, zWrite, -1, &pX, 0); idxFinalize(&rc, pX); if( rc!=SQLITE_OK ){ idxDatabaseError(p->dbv, pzErr); } } sqlite3_free(zWrite); if( rc==SQLITE_OK ){ rc = sqlite3_exec(p->dbv, zDrop, 0, 0, pzErr); } return rc; } static int idxProcessTriggers(sqlite3expert *p, char **pzErr){ int rc = SQLITE_OK; IdxWrite *pEnd = 0; IdxWrite *pFirst = p->pWrite; while( rc==SQLITE_OK && pFirst!=pEnd ){ IdxWrite *pIter; for(pIter=pFirst; rc==SQLITE_OK && pIter!=pEnd; pIter=pIter->pNext){ rc = idxProcessOneTrigger(p, pIter, pzErr); } pEnd = pFirst; pFirst = p->pWrite; } return rc; } static int idxCreateVtabSchema(sqlite3expert *p, char **pzErrmsg){ int rc = idxRegisterVtab(p); sqlite3_stmt *pSchema = 0; /* For each table in the main db schema: ** ** 1) Add an entry to the p->pTable list, and ** 2) Create the equivalent virtual table in dbv. */ rc = idxPrepareStmt(p->db, &pSchema, pzErrmsg, "SELECT type, name, sql, 1 FROM sqlite_master " "WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%%' " " UNION ALL " "SELECT type, name, sql, 2 FROM sqlite_master " "WHERE type = 'trigger'" " AND tbl_name IN(SELECT name FROM sqlite_master WHERE type = 'view') " "ORDER BY 4, 1" ); while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSchema) ){ const char *zType = (const char*)sqlite3_column_text(pSchema, 0); const char *zName = (const char*)sqlite3_column_text(pSchema, 1); const char *zSql = (const char*)sqlite3_column_text(pSchema, 2); if( zType[0]=='v' || zType[1]=='r' ){ rc = sqlite3_exec(p->dbv, zSql, 0, 0, pzErrmsg); }else{ IdxTable *pTab; rc = idxGetTableInfo(p->db, zName, &pTab, pzErrmsg); if( rc==SQLITE_OK ){ int i; char *zInner = 0; char *zOuter = 0; pTab->pNext = p->pTable; p->pTable = pTab; /* The statement the vtab will pass to sqlite3_declare_vtab() */ zInner = idxAppendText(&rc, 0, "CREATE TABLE x("); for(i=0; i<pTab->nCol; i++){ zInner = idxAppendText(&rc, zInner, "%s%Q COLLATE %s", (i==0 ? "" : ", "), pTab->aCol[i].zName, pTab->aCol[i].zColl ); } zInner = idxAppendText(&rc, zInner, ")"); /* The CVT statement to create the vtab */ zOuter = idxAppendText(&rc, 0, "CREATE VIRTUAL TABLE %Q USING expert(%Q)", zName, zInner ); if( rc==SQLITE_OK ){ rc = sqlite3_exec(p->dbv, zOuter, 0, 0, pzErrmsg); } sqlite3_free(zInner); sqlite3_free(zOuter); } } } idxFinalize(&rc, pSchema); return rc; } struct IdxSampleCtx { int iTarget; double target; /* Target nRet/nRow value */ double nRow; /* Number of rows seen */ double nRet; /* Number of rows returned */ }; static void idxSampleFunc( sqlite3_context *pCtx, int argc, sqlite3_value **argv ){ struct IdxSampleCtx *p = (struct IdxSampleCtx*)sqlite3_user_data(pCtx); int bRet; assert( argc==0 ); if( p->nRow==0.0 ){ bRet = 1; }else{ bRet = (p->nRet / p->nRow) <= p->target; if( bRet==0 ){ unsigned short rnd; sqlite3_randomness(2, (void*)&rnd); bRet = ((int)rnd % 100) <= p->iTarget; } } sqlite3_result_int(pCtx, bRet); p->nRow += 1.0; p->nRet += (double)bRet; } struct IdxRemCtx { int nSlot; struct IdxRemSlot { int eType; /* SQLITE_NULL, INTEGER, REAL, TEXT, BLOB */ i64 iVal; /* SQLITE_INTEGER value */ double rVal; /* SQLITE_FLOAT value */ int nByte; /* Bytes of space allocated at z */ int n; /* Size of buffer z */ char *z; /* SQLITE_TEXT/BLOB value */ } aSlot[1]; }; /* ** Implementation of scalar function rem(). */ static void idxRemFunc( sqlite3_context *pCtx, int argc, sqlite3_value **argv ){ struct IdxRemCtx *p = (struct IdxRemCtx*)sqlite3_user_data(pCtx); struct IdxRemSlot *pSlot; int iSlot; assert( argc==2 ); iSlot = sqlite3_value_int(argv[0]); assert( iSlot<=p->nSlot ); pSlot = &p->aSlot[iSlot]; switch( pSlot->eType ){ case SQLITE_NULL: /* no-op */ break; case SQLITE_INTEGER: sqlite3_result_int64(pCtx, pSlot->iVal); break; case SQLITE_FLOAT: sqlite3_result_double(pCtx, pSlot->rVal); break; case SQLITE_BLOB: sqlite3_result_blob(pCtx, pSlot->z, pSlot->n, SQLITE_TRANSIENT); break; case SQLITE_TEXT: sqlite3_result_text(pCtx, pSlot->z, pSlot->n, SQLITE_TRANSIENT); break; } pSlot->eType = sqlite3_value_type(argv[1]); switch( pSlot->eType ){ case SQLITE_NULL: /* no-op */ break; case SQLITE_INTEGER: pSlot->iVal = sqlite3_value_int64(argv[1]); break; case SQLITE_FLOAT: pSlot->rVal = sqlite3_value_double(argv[1]); break; case SQLITE_BLOB: case SQLITE_TEXT: { int nByte = sqlite3_value_bytes(argv[1]); if( nByte>pSlot->nByte ){ char *zNew = (char*)sqlite3_realloc(pSlot->z, nByte*2); if( zNew==0 ){ sqlite3_result_error_nomem(pCtx); return; } pSlot->nByte = nByte*2; pSlot->z = zNew; } pSlot->n = nByte; if( pSlot->eType==SQLITE_BLOB ){ memcpy(pSlot->z, sqlite3_value_blob(argv[1]), nByte); }else{ memcpy(pSlot->z, sqlite3_value_text(argv[1]), nByte); } break; } } } static int idxLargestIndex(sqlite3 *db, int *pnMax, char **pzErr){ int rc = SQLITE_OK; const char *zMax = "SELECT max(i.seqno) FROM " " sqlite_master AS s, " " pragma_index_list(s.name) AS l, " " pragma_index_info(l.name) AS i " "WHERE s.type = 'table'"; sqlite3_stmt *pMax = 0; *pnMax = 0; rc = idxPrepareStmt(db, &pMax, pzErr, zMax); if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pMax) ){ *pnMax = sqlite3_column_int(pMax, 0) + 1; } idxFinalize(&rc, pMax); return rc; } static int idxPopulateOneStat1( sqlite3expert *p, sqlite3_stmt *pIndexXInfo, sqlite3_stmt *pWriteStat, const char *zTab, const char *zIdx, char **pzErr ){ char *zCols = 0; char *zOrder = 0; char *zQuery = 0; int nCol = 0; int i; sqlite3_stmt *pQuery = 0; int *aStat = 0; int rc = SQLITE_OK; assert( p->iSample>0 ); /* Formulate the query text */ sqlite3_bind_text(pIndexXInfo, 1, zIdx, -1, SQLITE_STATIC); while( SQLITE_OK==rc && SQLITE_ROW==sqlite3_step(pIndexXInfo) ){ const char *zComma = zCols==0 ? "" : ", "; const char *zName = (const char*)sqlite3_column_text(pIndexXInfo, 0); const char *zColl = (const char*)sqlite3_column_text(pIndexXInfo, 1); zCols = idxAppendText(&rc, zCols, "%sx.%Q IS rem(%d, x.%Q) COLLATE %s", zComma, zName, nCol, zName, zColl ); zOrder = idxAppendText(&rc, zOrder, "%s%d", zComma, ++nCol); } if( rc==SQLITE_OK ){ if( p->iSample==100 ){ zQuery = sqlite3_mprintf( "SELECT %s FROM %Q x ORDER BY %s", zCols, zTab, zOrder ); }else{ zQuery = sqlite3_mprintf( "SELECT %s FROM temp."UNIQUE_TABLE_NAME" x ORDER BY %s", zCols, zOrder ); } } sqlite3_free(zCols); sqlite3_free(zOrder); /* Formulate the query text */ if( rc==SQLITE_OK ){ sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv); rc = idxPrepareStmt(dbrem, &pQuery, pzErr, zQuery); } sqlite3_free(zQuery); if( rc==SQLITE_OK ){ aStat = (int*)idxMalloc(&rc, sizeof(int)*(nCol+1)); } if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){ IdxHashEntry *pEntry; char *zStat = 0; for(i=0; i<=nCol; i++) aStat[i] = 1; while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pQuery) ){ aStat[0]++; for(i=0; i<nCol; i++){ if( sqlite3_column_int(pQuery, i)==0 ) break; } for(/*no-op*/; i<nCol; i++){ aStat[i+1]++; } } if( rc==SQLITE_OK ){ int s0 = aStat[0]; zStat = sqlite3_mprintf("%d", s0); if( zStat==0 ) rc = SQLITE_NOMEM; for(i=1; rc==SQLITE_OK && i<=nCol; i++){ zStat = idxAppendText(&rc, zStat, " %d", (s0+aStat[i]/2) / aStat[i]); } } if( rc==SQLITE_OK ){ sqlite3_bind_text(pWriteStat, 1, zTab, -1, SQLITE_STATIC); sqlite3_bind_text(pWriteStat, 2, zIdx, -1, SQLITE_STATIC); sqlite3_bind_text(pWriteStat, 3, zStat, -1, SQLITE_STATIC); sqlite3_step(pWriteStat); rc = sqlite3_reset(pWriteStat); } pEntry = idxHashFind(&p->hIdx, zIdx, STRLEN(zIdx)); if( pEntry ){ assert( pEntry->zVal2==0 ); pEntry->zVal2 = zStat; }else{ sqlite3_free(zStat); } } sqlite3_free(aStat); idxFinalize(&rc, pQuery); return rc; } static int idxBuildSampleTable(sqlite3expert *p, const char *zTab){ int rc; char *zSql; rc = sqlite3_exec(p->dbv,"DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0); if( rc!=SQLITE_OK ) return rc; zSql = sqlite3_mprintf( "CREATE TABLE temp." UNIQUE_TABLE_NAME " AS SELECT * FROM %Q", zTab ); if( zSql==0 ) return SQLITE_NOMEM; rc = sqlite3_exec(p->dbv, zSql, 0, 0, 0); sqlite3_free(zSql); return rc; } /* ** This function is called as part of sqlite3_expert_analyze(). Candidate ** indexes have already been created in database sqlite3expert.dbm, this ** function populates sqlite_stat1 table in the same database. ** ** The stat1 data is generated by querying the */ static int idxPopulateStat1(sqlite3expert *p, char **pzErr){ int rc = SQLITE_OK; int nMax =0; struct IdxRemCtx *pCtx = 0; struct IdxSampleCtx samplectx; int i; i64 iPrev = -100000; sqlite3_stmt *pAllIndex = 0; sqlite3_stmt *pIndexXInfo = 0; sqlite3_stmt *pWrite = 0; const char *zAllIndex = "SELECT s.rowid, s.name, l.name FROM " " sqlite_master AS s, " " pragma_index_list(s.name) AS l " "WHERE s.type = 'table'"; const char *zIndexXInfo = "SELECT name, coll FROM pragma_index_xinfo(?) WHERE key"; const char *zWrite = "INSERT INTO sqlite_stat1 VALUES(?, ?, ?)"; /* If iSample==0, no sqlite_stat1 data is required. */ if( p->iSample==0 ) return SQLITE_OK; rc = idxLargestIndex(p->dbm, &nMax, pzErr); if( nMax<=0 || rc!=SQLITE_OK ) return rc; rc = sqlite3_exec(p->dbm, "ANALYZE; PRAGMA writable_schema=1", 0, 0, 0); if( rc==SQLITE_OK ){ int nByte = sizeof(struct IdxRemCtx) + (sizeof(struct IdxRemSlot) * nMax); pCtx = (struct IdxRemCtx*)idxMalloc(&rc, nByte); } if( rc==SQLITE_OK ){ sqlite3 *dbrem = (p->iSample==100 ? p->db : p->dbv); rc = sqlite3_create_function( dbrem, "rem", 2, SQLITE_UTF8, (void*)pCtx, idxRemFunc, 0, 0 ); } if( rc==SQLITE_OK ){ rc = sqlite3_create_function( p->db, "sample", 0, SQLITE_UTF8, (void*)&samplectx, idxSampleFunc, 0, 0 ); } if( rc==SQLITE_OK ){ pCtx->nSlot = nMax+1; rc = idxPrepareStmt(p->dbm, &pAllIndex, pzErr, zAllIndex); } if( rc==SQLITE_OK ){ rc = idxPrepareStmt(p->dbm, &pIndexXInfo, pzErr, zIndexXInfo); } if( rc==SQLITE_OK ){ rc = idxPrepareStmt(p->dbm, &pWrite, pzErr, zWrite); } while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pAllIndex) ){ i64 iRowid = sqlite3_column_int64(pAllIndex, 0); const char *zTab = (const char*)sqlite3_column_text(pAllIndex, 1); const char *zIdx = (const char*)sqlite3_column_text(pAllIndex, 2); if( p->iSample<100 && iPrev!=iRowid ){ samplectx.target = (double)p->iSample / 100.0; samplectx.iTarget = p->iSample; samplectx.nRow = 0.0; samplectx.nRet = 0.0; rc = idxBuildSampleTable(p, zTab); if( rc!=SQLITE_OK ) break; } rc = idxPopulateOneStat1(p, pIndexXInfo, pWrite, zTab, zIdx, pzErr); iPrev = iRowid; } if( rc==SQLITE_OK && p->iSample<100 ){ rc = sqlite3_exec(p->dbv, "DROP TABLE IF EXISTS temp." UNIQUE_TABLE_NAME, 0,0,0 ); } idxFinalize(&rc, pAllIndex); idxFinalize(&rc, pIndexXInfo); idxFinalize(&rc, pWrite); for(i=0; i<pCtx->nSlot; i++){ sqlite3_free(pCtx->aSlot[i].z); } sqlite3_free(pCtx); if( rc==SQLITE_OK ){ rc = sqlite3_exec(p->dbm, "ANALYZE sqlite_master", 0, 0, 0); } sqlite3_exec(p->db, "DROP TABLE IF EXISTS temp."UNIQUE_TABLE_NAME,0,0,0); return rc; } /* ** Allocate a new sqlite3expert object. */ sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErrmsg){ int rc = SQLITE_OK; sqlite3expert *pNew; pNew = (sqlite3expert*)idxMalloc(&rc, sizeof(sqlite3expert)); /* Open two in-memory databases to work with. The "vtab database" (dbv) ** will contain a virtual table corresponding to each real table in ** the user database schema, and a copy of each view. It is used to ** collect information regarding the WHERE, ORDER BY and other clauses ** of the user's query. */ if( rc==SQLITE_OK ){ pNew->db = db; pNew->iSample = 100; rc = sqlite3_open(":memory:", &pNew->dbv); } if( rc==SQLITE_OK ){ rc = sqlite3_open(":memory:", &pNew->dbm); if( rc==SQLITE_OK ){ sqlite3_db_config(pNew->dbm, SQLITE_DBCONFIG_FULL_EQP, 1, (int*)0); } } /* Copy the entire schema of database [db] into [dbm]. */ if( rc==SQLITE_OK ){ sqlite3_stmt *pSql; rc = idxPrintfPrepareStmt(pNew->db, &pSql, pzErrmsg, "SELECT sql FROM sqlite_master WHERE name NOT LIKE 'sqlite_%%'" " AND sql NOT LIKE 'CREATE VIRTUAL %%'" ); while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pSql) ){ const char *zSql = (const char*)sqlite3_column_text(pSql, 0); rc = sqlite3_exec(pNew->dbm, zSql, 0, 0, pzErrmsg); } idxFinalize(&rc, pSql); } /* Create the vtab schema */ if( rc==SQLITE_OK ){ rc = idxCreateVtabSchema(pNew, pzErrmsg); } /* Register the auth callback with dbv */ if( rc==SQLITE_OK ){ sqlite3_set_authorizer(pNew->dbv, idxAuthCallback, (void*)pNew); } /* If an error has occurred, free the new object and reutrn NULL. Otherwise, ** return the new sqlite3expert handle. */ if( rc!=SQLITE_OK ){ sqlite3_expert_destroy(pNew); pNew = 0; } return pNew; } /* ** Configure an sqlite3expert object. */ int sqlite3_expert_config(sqlite3expert *p, int op, ...){ int rc = SQLITE_OK; va_list ap; va_start(ap, op); switch( op ){ case EXPERT_CONFIG_SAMPLE: { int iVal = va_arg(ap, int); if( iVal<0 ) iVal = 0; if( iVal>100 ) iVal = 100; p->iSample = iVal; break; } default: rc = SQLITE_NOTFOUND; break; } va_end(ap); return rc; } /* ** Add an SQL statement to the analysis. */ int sqlite3_expert_sql( sqlite3expert *p, /* From sqlite3_expert_new() */ const char *zSql, /* SQL statement to add */ char **pzErr /* OUT: Error message (if any) */ ){ IdxScan *pScanOrig = p->pScan; IdxStatement *pStmtOrig = p->pStatement; int rc = SQLITE_OK; const char *zStmt = zSql; if( p->bRun ) return SQLITE_MISUSE; while( rc==SQLITE_OK && zStmt && zStmt[0] ){ sqlite3_stmt *pStmt = 0; rc = sqlite3_prepare_v2(p->dbv, zStmt, -1, &pStmt, &zStmt); if( rc==SQLITE_OK ){ if( pStmt ){ IdxStatement *pNew; const char *z = sqlite3_sql(pStmt); int n = STRLEN(z); pNew = (IdxStatement*)idxMalloc(&rc, sizeof(IdxStatement) + n+1); if( rc==SQLITE_OK ){ pNew->zSql = (char*)&pNew[1]; memcpy(pNew->zSql, z, n+1); pNew->pNext = p->pStatement; if( p->pStatement ) pNew->iId = p->pStatement->iId+1; p->pStatement = pNew; } sqlite3_finalize(pStmt); } }else{ idxDatabaseError(p->dbv, pzErr); } } if( rc!=SQLITE_OK ){ idxScanFree(p->pScan, pScanOrig); idxStatementFree(p->pStatement, pStmtOrig); p->pScan = pScanOrig; p->pStatement = pStmtOrig; } return rc; } int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr){ int rc; IdxHashEntry *pEntry; /* Do trigger processing to collect any extra IdxScan structures */ rc = idxProcessTriggers(p, pzErr); /* Create candidate indexes within the in-memory database file */ if( rc==SQLITE_OK ){ rc = idxCreateCandidates(p, pzErr); } /* Generate the stat1 data */ if( rc==SQLITE_OK ){ rc = idxPopulateStat1(p, pzErr); } /* Formulate the EXPERT_REPORT_CANDIDATES text */ for(pEntry=p->hIdx.pFirst; pEntry; pEntry=pEntry->pNext){ p->zCandidates = idxAppendText(&rc, p->zCandidates, "%s;%s%s\n", pEntry->zVal, pEntry->zVal2 ? " -- stat1: " : "", pEntry->zVal2 ); } /* Figure out which of the candidate indexes are preferred by the query ** planner and report the results to the user. */ if( rc==SQLITE_OK ){ rc = idxFindIndexes(p, pzErr); } if( rc==SQLITE_OK ){ p->bRun = 1; } return rc; } /* ** Return the total number of statements that have been added to this ** sqlite3expert using sqlite3_expert_sql(). */ int sqlite3_expert_count(sqlite3expert *p){ int nRet = 0; if( p->pStatement ) nRet = p->pStatement->iId+1; return nRet; } /* ** Return a component of the report. */ const char *sqlite3_expert_report(sqlite3expert *p, int iStmt, int eReport){ const char *zRet = 0; IdxStatement *pStmt; if( p->bRun==0 ) return 0; for(pStmt=p->pStatement; pStmt && pStmt->iId!=iStmt; pStmt=pStmt->pNext); switch( eReport ){ case EXPERT_REPORT_SQL: if( pStmt ) zRet = pStmt->zSql; break; case EXPERT_REPORT_INDEXES: if( pStmt ) zRet = pStmt->zIdx; break; case EXPERT_REPORT_PLAN: if( pStmt ) zRet = pStmt->zEQP; break; case EXPERT_REPORT_CANDIDATES: zRet = p->zCandidates; break; } return zRet; } /* ** Free an sqlite3expert object. */ void sqlite3_expert_destroy(sqlite3expert *p){ if( p ){ sqlite3_close(p->dbm); sqlite3_close(p->dbv); idxScanFree(p->pScan, 0); idxStatementFree(p->pStatement, 0); idxTableFree(p->pTable); idxWriteFree(p->pWrite); idxHashClear(&p->hIdx); sqlite3_free(p->zCandidates); sqlite3_free(p); } } |
Added ext/expert/sqlite3expert.h.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | /* ** 2017 April 07 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* */ #include "sqlite3.h" typedef struct sqlite3expert sqlite3expert; /* ** Create a new sqlite3expert object. ** ** If successful, a pointer to the new object is returned and (*pzErr) set ** to NULL. Or, if an error occurs, NULL is returned and (*pzErr) set to ** an English-language error message. In this case it is the responsibility ** of the caller to eventually free the error message buffer using ** sqlite3_free(). */ sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErr); /* ** Configure an sqlite3expert object. ** ** EXPERT_CONFIG_SAMPLE: ** By default, sqlite3_expert_analyze() generates sqlite_stat1 data for ** each candidate index. This involves scanning and sorting the entire ** contents of each user database table once for each candidate index ** associated with the table. For large databases, this can be ** prohibitively slow. This option allows the sqlite3expert object to ** be configured so that sqlite_stat1 data is instead generated based on a ** subset of each table, or so that no sqlite_stat1 data is used at all. ** ** A single integer argument is passed to this option. If the value is less ** than or equal to zero, then no sqlite_stat1 data is generated or used by ** the analysis - indexes are recommended based on the database schema only. ** Or, if the value is 100 or greater, complete sqlite_stat1 data is ** generated for each candidate index (this is the default). Finally, if the ** value falls between 0 and 100, then it represents the percentage of user ** table rows that should be considered when generating sqlite_stat1 data. ** ** Examples: ** ** // Do not generate any sqlite_stat1 data ** sqlite3_expert_config(pExpert, EXPERT_CONFIG_SAMPLE, 0); ** ** // Generate sqlite_stat1 data based on 10% of the rows in each table. ** sqlite3_expert_config(pExpert, EXPERT_CONFIG_SAMPLE, 10); */ int sqlite3_expert_config(sqlite3expert *p, int op, ...); #define EXPERT_CONFIG_SAMPLE 1 /* int */ /* ** Specify zero or more SQL statements to be included in the analysis. ** ** Buffer zSql must contain zero or more complete SQL statements. This ** function parses all statements contained in the buffer and adds them ** to the internal list of statements to analyze. If successful, SQLITE_OK ** is returned and (*pzErr) set to NULL. Or, if an error occurs - for example ** due to a error in the SQL - an SQLite error code is returned and (*pzErr) ** may be set to point to an English language error message. In this case ** the caller is responsible for eventually freeing the error message buffer ** using sqlite3_free(). ** ** If an error does occur while processing one of the statements in the ** buffer passed as the second argument, none of the statements in the ** buffer are added to the analysis. ** ** This function must be called before sqlite3_expert_analyze(). If a call ** to this function is made on an sqlite3expert object that has already ** been passed to sqlite3_expert_analyze() SQLITE_MISUSE is returned ** immediately and no statements are added to the analysis. */ int sqlite3_expert_sql( sqlite3expert *p, /* From a successful sqlite3_expert_new() */ const char *zSql, /* SQL statement(s) to add */ char **pzErr /* OUT: Error message (if any) */ ); /* ** This function is called after the sqlite3expert object has been configured ** with all SQL statements using sqlite3_expert_sql() to actually perform ** the analysis. Once this function has been called, it is not possible to ** add further SQL statements to the analysis. ** ** If successful, SQLITE_OK is returned and (*pzErr) is set to NULL. Or, if ** an error occurs, an SQLite error code is returned and (*pzErr) set to ** point to a buffer containing an English language error message. In this ** case it is the responsibility of the caller to eventually free the buffer ** using sqlite3_free(). ** ** If an error does occur within this function, the sqlite3expert object ** is no longer useful for any purpose. At that point it is no longer ** possible to add further SQL statements to the object or to re-attempt ** the analysis. The sqlite3expert object must still be freed using a call ** sqlite3_expert_destroy(). */ int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr); /* ** Return the total number of statements loaded using sqlite3_expert_sql(). ** The total number of SQL statements may be different from the total number ** to calls to sqlite3_expert_sql(). */ int sqlite3_expert_count(sqlite3expert*); /* ** Return a component of the report. ** ** This function is called after sqlite3_expert_analyze() to extract the ** results of the analysis. Each call to this function returns either a ** NULL pointer or a pointer to a buffer containing a nul-terminated string. ** The value passed as the third argument must be one of the EXPERT_REPORT_* ** #define constants defined below. ** ** For some EXPERT_REPORT_* parameters, the buffer returned contains ** information relating to a specific SQL statement. In these cases that ** SQL statement is identified by the value passed as the second argument. ** SQL statements are numbered from 0 in the order in which they are parsed. ** If an out-of-range value (less than zero or equal to or greater than the ** value returned by sqlite3_expert_count()) is passed as the second argument ** along with such an EXPERT_REPORT_* parameter, NULL is always returned. ** ** EXPERT_REPORT_SQL: ** Return the text of SQL statement iStmt. ** ** EXPERT_REPORT_INDEXES: ** Return a buffer containing the CREATE INDEX statements for all recommended ** indexes for statement iStmt. If there are no new recommeded indexes, NULL ** is returned. ** ** EXPERT_REPORT_PLAN: ** Return a buffer containing the EXPLAIN QUERY PLAN output for SQL query ** iStmt after the proposed indexes have been added to the database schema. ** ** EXPERT_REPORT_CANDIDATES: ** Return a pointer to a buffer containing the CREATE INDEX statements ** for all indexes that were tested (for all SQL statements). The iStmt ** parameter is ignored for EXPERT_REPORT_CANDIDATES calls. */ const char *sqlite3_expert_report(sqlite3expert*, int iStmt, int eReport); /* ** Values for the third argument passed to sqlite3_expert_report(). */ #define EXPERT_REPORT_SQL 1 #define EXPERT_REPORT_INDEXES 2 #define EXPERT_REPORT_PLAN 3 #define EXPERT_REPORT_CANDIDATES 4 /* ** Free an (sqlite3expert*) handle and all associated resources. There ** should be one call to this function for each successful call to ** sqlite3-expert_new(). */ void sqlite3_expert_destroy(sqlite3expert*); |
Added ext/expert/test_expert.c.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 | /* ** 2017 April 07 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* */ #if defined(SQLITE_TEST) #include "sqlite3expert.h" #include <assert.h> #include <string.h> #if defined(INCLUDE_SQLITE_TCL_H) # include "sqlite_tcl.h" #else # include "tcl.h" # ifndef SQLITE_TCLAPI # define SQLITE_TCLAPI # endif #endif /* ** Extract an sqlite3* db handle from the object passed as the second ** argument. If successful, set *pDb to point to the db handle and return ** TCL_OK. Otherwise, return TCL_ERROR. */ static int dbHandleFromObj(Tcl_Interp *interp, Tcl_Obj *pObj, sqlite3 **pDb){ Tcl_CmdInfo info; if( 0==Tcl_GetCommandInfo(interp, Tcl_GetString(pObj), &info) ){ Tcl_AppendResult(interp, "no such handle: ", Tcl_GetString(pObj), 0); return TCL_ERROR; } *pDb = *(sqlite3 **)info.objClientData; return TCL_OK; } /* ** Tclcmd: $expert sql SQL ** $expert analyze ** $expert count ** $expert report STMT EREPORT ** $expert destroy */ static int SQLITE_TCLAPI testExpertCmd( void *clientData, Tcl_Interp *interp, int objc, Tcl_Obj *CONST objv[] ){ sqlite3expert *pExpert = (sqlite3expert*)clientData; struct Subcmd { const char *zSub; int nArg; const char *zMsg; } aSub[] = { { "sql", 1, "TABLE", }, /* 0 */ { "analyze", 0, "", }, /* 1 */ { "count", 0, "", }, /* 2 */ { "report", 2, "STMT EREPORT", }, /* 3 */ { "destroy", 0, "", }, /* 4 */ { 0 } }; int iSub; int rc = TCL_OK; char *zErr = 0; if( objc<2 ){ Tcl_WrongNumArgs(interp, 1, objv, "SUBCOMMAND ..."); return TCL_ERROR; } rc = Tcl_GetIndexFromObjStruct(interp, objv[1], aSub, sizeof(aSub[0]), "sub-command", 0, &iSub ); if( rc!=TCL_OK ) return rc; if( objc!=2+aSub[iSub].nArg ){ Tcl_WrongNumArgs(interp, 2, objv, aSub[iSub].zMsg); return TCL_ERROR; } switch( iSub ){ case 0: { /* sql */ char *zArg = Tcl_GetString(objv[2]); rc = sqlite3_expert_sql(pExpert, zArg, &zErr); break; } case 1: { /* analyze */ rc = sqlite3_expert_analyze(pExpert, &zErr); break; } case 2: { /* count */ int n = sqlite3_expert_count(pExpert); Tcl_SetObjResult(interp, Tcl_NewIntObj(n)); break; } case 3: { /* report */ const char *aEnum[] = { "sql", "indexes", "plan", "candidates", 0 }; int iEnum; int iStmt; const char *zReport; if( Tcl_GetIntFromObj(interp, objv[2], &iStmt) || Tcl_GetIndexFromObj(interp, objv[3], aEnum, "report", 0, &iEnum) ){ return TCL_ERROR; } assert( EXPERT_REPORT_SQL==1 ); assert( EXPERT_REPORT_INDEXES==2 ); assert( EXPERT_REPORT_PLAN==3 ); assert( EXPERT_REPORT_CANDIDATES==4 ); zReport = sqlite3_expert_report(pExpert, iStmt, 1+iEnum); Tcl_SetObjResult(interp, Tcl_NewStringObj(zReport, -1)); break; } default: /* destroy */ assert( iSub==4 ); Tcl_DeleteCommand(interp, Tcl_GetString(objv[0])); break; } if( rc!=TCL_OK ){ if( zErr ){ Tcl_SetObjResult(interp, Tcl_NewStringObj(zErr, -1)); }else{ extern const char *sqlite3ErrName(int); Tcl_SetObjResult(interp, Tcl_NewStringObj(sqlite3ErrName(rc), -1)); } } sqlite3_free(zErr); return rc; } static void SQLITE_TCLAPI testExpertDel(void *clientData){ sqlite3expert *pExpert = (sqlite3expert*)clientData; sqlite3_expert_destroy(pExpert); } /* ** sqlite3_expert_new DB */ static int SQLITE_TCLAPI test_sqlite3_expert_new( void * clientData, Tcl_Interp *interp, int objc, Tcl_Obj *CONST objv[] ){ static int iCmd = 0; sqlite3 *db; char *zCmd = 0; char *zErr = 0; sqlite3expert *pExpert; int rc = TCL_OK; if( objc!=2 ){ Tcl_WrongNumArgs(interp, 1, objv, "DB"); return TCL_ERROR; } if( dbHandleFromObj(interp, objv[1], &db) ){ return TCL_ERROR; } zCmd = sqlite3_mprintf("sqlite3expert%d", ++iCmd); if( zCmd==0 ){ Tcl_AppendResult(interp, "out of memory", (char*)0); return TCL_ERROR; } pExpert = sqlite3_expert_new(db, &zErr); if( pExpert==0 ){ Tcl_AppendResult(interp, zErr, (char*)0); rc = TCL_ERROR; }else{ void *p = (void*)pExpert; Tcl_CreateObjCommand(interp, zCmd, testExpertCmd, p, testExpertDel); Tcl_SetObjResult(interp, Tcl_NewStringObj(zCmd, -1)); } sqlite3_free(zCmd); sqlite3_free(zErr); return rc; } int TestExpert_Init(Tcl_Interp *interp){ struct Cmd { const char *zCmd; Tcl_ObjCmdProc *xProc; } aCmd[] = { { "sqlite3_expert_new", test_sqlite3_expert_new }, }; int i; for(i=0; i<sizeof(aCmd)/sizeof(struct Cmd); i++){ struct Cmd *p = &aCmd[i]; Tcl_CreateObjCommand(interp, p->zCmd, p->xProc, 0, 0); } return TCL_OK; } #endif |
Changes to main.mk.
︙ | ︙ | |||
273 274 275 276 277 278 279 280 281 282 283 284 285 286 | shell.c \ sqlite3.h # Source code to the test files. # TESTSRC = \ $(TOP)/ext/fts3/fts3_term.c \ $(TOP)/ext/fts3/fts3_test.c \ $(TOP)/ext/rbu/test_rbu.c \ $(TOP)/src/test1.c \ $(TOP)/src/test2.c \ $(TOP)/src/test3.c \ $(TOP)/src/test4.c \ | > > | 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 | shell.c \ sqlite3.h # Source code to the test files. # TESTSRC = \ $(TOP)/ext/expert/sqlite3expert.c \ $(TOP)/ext/expert/test_expert.c \ $(TOP)/ext/fts3/fts3_term.c \ $(TOP)/ext/fts3/fts3_test.c \ $(TOP)/ext/rbu/test_rbu.c \ $(TOP)/src/test1.c \ $(TOP)/src/test2.c \ $(TOP)/src/test3.c \ $(TOP)/src/test4.c \ |
︙ | ︙ | |||
781 782 783 784 785 786 787 788 789 790 791 792 793 794 | echo "static const char *zMainloop = " >> $@ tclsh $(TOP)/tool/tostr.tcl $(TOP)/tool/spaceanal.tcl >> $@ echo "; return zMainloop; }" >> $@ sqlite3_analyzer$(EXE): sqlite3_analyzer.c $(TCCX) $(TCL_FLAGS) sqlite3_analyzer.c -o $@ $(LIBTCL) $(THREADLIB) dbdump$(EXE): $(TOP)/ext/misc/dbdump.c sqlite3.o $(TCCX) -DDBDUMP_STANDALONE -o dbdump$(EXE) \ $(TOP)/ext/misc/dbdump.c sqlite3.o $(THREADLIB) # Rules to build the 'testfixture' application. # TESTFIXTURE_FLAGS = -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 | > > > | 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 | echo "static const char *zMainloop = " >> $@ tclsh $(TOP)/tool/tostr.tcl $(TOP)/tool/spaceanal.tcl >> $@ echo "; return zMainloop; }" >> $@ sqlite3_analyzer$(EXE): sqlite3_analyzer.c $(TCCX) $(TCL_FLAGS) sqlite3_analyzer.c -o $@ $(LIBTCL) $(THREADLIB) sqlite3_expert$(EXE): $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c $(TCCX) $(TOP)/ext/expert/sqlite3expert.h $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c -o sqlite3_expert $(THREADLIB) dbdump$(EXE): $(TOP)/ext/misc/dbdump.c sqlite3.o $(TCCX) -DDBDUMP_STANDALONE -o dbdump$(EXE) \ $(TOP)/ext/misc/dbdump.c sqlite3.o $(THREADLIB) # Rules to build the 'testfixture' application. # TESTFIXTURE_FLAGS = -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 |
︙ | ︙ | |||
986 987 988 989 990 991 992 993 994 995 996 997 998 | rm -f wordcount wordcount.exe rm -f rbu rbu.exe rm -f srcck1 srcck1.exe rm -f sqlite3.c sqlite3-*.c fts?amal.c tclsqlite3.c rm -f sqlite3rc.h rm -f shell.c sqlite3ext.h rm -f sqlite3_analyzer sqlite3_analyzer.exe sqlite3_analyzer.c rm -f sqlite-*-output.vsix rm -f mptester mptester.exe rm -f fuzzershell fuzzershell.exe rm -f fuzzcheck fuzzcheck.exe rm -f sqldiff sqldiff.exe rm -f fts5.* fts5parse.* | > | 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 | rm -f wordcount wordcount.exe rm -f rbu rbu.exe rm -f srcck1 srcck1.exe rm -f sqlite3.c sqlite3-*.c fts?amal.c tclsqlite3.c rm -f sqlite3rc.h rm -f shell.c sqlite3ext.h rm -f sqlite3_analyzer sqlite3_analyzer.exe sqlite3_analyzer.c rm -f sqlite3_expert sqlite3_expert.exe rm -f sqlite-*-output.vsix rm -f mptester mptester.exe rm -f fuzzershell fuzzershell.exe rm -f fuzzcheck fuzzcheck.exe rm -f sqldiff sqldiff.exe rm -f fts5.* fts5parse.* |
Changes to src/main.c.
︙ | ︙ | |||
801 802 803 804 805 806 807 808 809 810 811 812 813 814 | } case SQLITE_DBCONFIG_LOOKASIDE: { void *pBuf = va_arg(ap, void*); /* IMP: R-26835-10964 */ int sz = va_arg(ap, int); /* IMP: R-47871-25994 */ int cnt = va_arg(ap, int); /* IMP: R-04460-53386 */ rc = setupLookaside(db, pBuf, sz, cnt); break; } default: { static const struct { int op; /* The opcode */ u32 mask; /* Mask of the bit in sqlite3.flags to set/clear */ } aFlagOp[] = { { SQLITE_DBCONFIG_ENABLE_FKEY, SQLITE_ForeignKeys }, | > > > > > > > > > > > > > > > | 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 | } case SQLITE_DBCONFIG_LOOKASIDE: { void *pBuf = va_arg(ap, void*); /* IMP: R-26835-10964 */ int sz = va_arg(ap, int); /* IMP: R-47871-25994 */ int cnt = va_arg(ap, int); /* IMP: R-04460-53386 */ rc = setupLookaside(db, pBuf, sz, cnt); break; } case SQLITE_DBCONFIG_FULL_EQP: { int onoff = va_arg(ap, int); int *pRes = va_arg(ap, int*); if( onoff>0 ){ db->bFullEQP = 1; }else if( onoff==0 ){ db->bFullEQP = 0; } sqlite3ExpirePreparedStatements(db); if( pRes ){ *pRes = db->bFullEQP; } rc = SQLITE_OK; break; } default: { static const struct { int op; /* The opcode */ u32 mask; /* Mask of the bit in sqlite3.flags to set/clear */ } aFlagOp[] = { { SQLITE_DBCONFIG_ENABLE_FKEY, SQLITE_ForeignKeys }, |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 | ** the returned data set are: ** ** cid: Column id (numbered from left to right, starting at 0) ** name: Column name ** type: Column declaration type. ** notnull: True if 'NOT NULL' is part of column declaration ** dflt_value: The default value for the column, if any. */ case PragTyp_TABLE_INFO: if( zRight ){ Table *pTab; pTab = sqlite3LocateTable(pParse, LOCATE_NOERR, zRight, zDb); if( pTab ){ int i, k; int nHidden = 0; | > | 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 | ** the returned data set are: ** ** cid: Column id (numbered from left to right, starting at 0) ** name: Column name ** type: Column declaration type. ** notnull: True if 'NOT NULL' is part of column declaration ** dflt_value: The default value for the column, if any. ** pk: Non-zero for PK fields. */ case PragTyp_TABLE_INFO: if( zRight ){ Table *pTab; pTab = sqlite3LocateTable(pParse, LOCATE_NOERR, zRight, zDb); if( pTab ){ int i, k; int nHidden = 0; |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
2045 2046 2047 2048 2049 2050 2051 | ** operation before closing the connection. This option may be used to ** override this behaviour. The first parameter passed to this operation ** is an integer - non-zero to disable checkpoints-on-close, or zero (the ** default) to enable them. The second parameter is a pointer to an integer ** into which is written 0 or 1 to indicate whether checkpoints-on-close ** have been disabled - 0 if they are not disabled, 1 if they are. ** </dd> | < > > > > > > > > > | | | 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085 2086 2087 2088 2089 | ** operation before closing the connection. This option may be used to ** override this behaviour. The first parameter passed to this operation ** is an integer - non-zero to disable checkpoints-on-close, or zero (the ** default) to enable them. The second parameter is a pointer to an integer ** into which is written 0 or 1 to indicate whether checkpoints-on-close ** have been disabled - 0 if they are not disabled, 1 if they are. ** </dd> ** <dt>SQLITE_DBCONFIG_ENABLE_QPSG</dt> ** <dd>^(The SQLITE_DBCONFIG_ENABLE_QPSG option activates or deactivates ** the [query planner stability guarantee] (QPSG). When the QPSG is active, ** a single SQL query statement will always use the same algorithm regardless ** of values of [bound parameters].)^ The QPSG disables some query optimizations ** that look at the values of bound parameters, which can make some queries ** slower. But the QPSG has the advantage of more predictable behavior. With ** the QPSG active, SQLite will always use the same query plan in the field as ** was used during testing in the lab. ** </dd> ** <dt>SQLITE_DBCONFIG_FULL_EQP</dt> ** <dd> By default, the output of EXPLAIN QUERY PLAN commands does not ** include output for any operations performed by trigger programs. This ** option is used to set or clear (the default) a flag that governs this ** behavior. The first parameter passed to this operation is an integer - ** non-zero to enable output for trigger programs, or zero to disable it. ** The second parameter is a pointer to an integer into which is written ** 0 or 1 to indicate whether output-for-triggers has been disabled - 0 if ** it is not disabled, 1 if it is. ** </dd> ** </dl> */ #define SQLITE_DBCONFIG_MAINDBNAME 1000 /* const char* */ #define SQLITE_DBCONFIG_LOOKASIDE 1001 /* void* int int */ #define SQLITE_DBCONFIG_ENABLE_FKEY 1002 /* int int* */ #define SQLITE_DBCONFIG_ENABLE_TRIGGER 1003 /* int int* */ #define SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER 1004 /* int int* */ #define SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION 1005 /* int int* */ #define SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE 1006 /* int int* */ #define SQLITE_DBCONFIG_ENABLE_QPSG 1007 /* int int* */ #define SQLITE_DBCONFIG_FULL_EQP 1008 /* int int* */ /* ** CAPI3REF: Enable Or Disable Extended Result Codes ** METHOD: sqlite3 ** ** ^The sqlite3_extended_result_codes() routine enables or disables the ** [extended result codes] feature of SQLite. ^The extended result |
︙ | ︙ | |||
8272 8273 8274 8275 8276 8277 8278 8279 8280 8281 8282 8283 8284 8285 | ** value returned is one of [SQLITE_ROLLBACK], [SQLITE_IGNORE], [SQLITE_FAIL], ** [SQLITE_ABORT], or [SQLITE_REPLACE], according to the [ON CONFLICT] mode ** of the SQL statement that triggered the call to the [xUpdate] method of the ** [virtual table]. */ int sqlite3_vtab_on_conflict(sqlite3 *); /* ** CAPI3REF: Conflict resolution modes ** KEYWORDS: {conflict resolution mode} ** ** These constants are returned by [sqlite3_vtab_on_conflict()] to ** inform a [virtual table] implementation what the [ON CONFLICT] mode ** is for the SQL statement being evaluated. | > > > > > > > > > > > > > > > > | 8280 8281 8282 8283 8284 8285 8286 8287 8288 8289 8290 8291 8292 8293 8294 8295 8296 8297 8298 8299 8300 8301 8302 8303 8304 8305 8306 8307 8308 8309 | ** value returned is one of [SQLITE_ROLLBACK], [SQLITE_IGNORE], [SQLITE_FAIL], ** [SQLITE_ABORT], or [SQLITE_REPLACE], according to the [ON CONFLICT] mode ** of the SQL statement that triggered the call to the [xUpdate] method of the ** [virtual table]. */ int sqlite3_vtab_on_conflict(sqlite3 *); /* ** CAPI3REF: Determine The Collation For a Virtual Table Constraint ** ** This function may only be called from within a call to the [xBestIndex] ** method of a [virtual table implementation]. ** ** The first argument must be the database handle with which the virtual ** table is associated (the one passed to the [xConnect] or [xCreate] method ** to create the sqlite3_vtab object. The second argument must be an index ** into the aConstraint[] array belonging to the sqlite3_index_info structure ** passed to xBestIndex. This function returns a pointer to a buffer ** containing the name of the collation sequence for the corresponding ** constraint. */ SQLITE_EXPERIMENTAL const char *sqlite3_vtab_collation(sqlite3*, int); /* ** CAPI3REF: Conflict resolution modes ** KEYWORDS: {conflict resolution mode} ** ** These constants are returned by [sqlite3_vtab_on_conflict()] to ** inform a [virtual table] implementation what the [ON CONFLICT] mode ** is for the SQL statement being evaluated. |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 | signed char nextAutovac; /* Autovac setting after VACUUM if >=0 */ u8 suppressErr; /* Do not issue error messages if true */ u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */ u8 mTrace; /* zero or more SQLITE_TRACE flags */ u8 skipBtreeMutex; /* True if no shared-cache backends */ u8 nSqlExec; /* Number of pending OP_SqlExec opcodes */ int nextPagesize; /* Pagesize after VACUUM if >0 */ u32 magic; /* Magic number for detect library misuse */ int nChange; /* Value returned by sqlite3_changes() */ int nTotalChange; /* Value returned by sqlite3_total_changes() */ int aLimit[SQLITE_N_LIMIT]; /* Limits */ int nMaxSorterMmap; /* Maximum size of regions mapped by sorter */ struct sqlite3InitInfo { /* Information used during initialization */ | > | 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 | signed char nextAutovac; /* Autovac setting after VACUUM if >=0 */ u8 suppressErr; /* Do not issue error messages if true */ u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */ u8 mTrace; /* zero or more SQLITE_TRACE flags */ u8 skipBtreeMutex; /* True if no shared-cache backends */ u8 nSqlExec; /* Number of pending OP_SqlExec opcodes */ u8 bFullEQP; /* Include triggers in EQP output */ int nextPagesize; /* Pagesize after VACUUM if >0 */ u32 magic; /* Magic number for detect library misuse */ int nChange; /* Value returned by sqlite3_changes() */ int nTotalChange; /* Value returned by sqlite3_total_changes() */ int aLimit[SQLITE_N_LIMIT]; /* Limits */ int nMaxSorterMmap; /* Maximum size of regions mapped by sorter */ struct sqlite3InitInfo { /* Information used during initialization */ |
︙ | ︙ | |||
1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 | #endif #ifndef SQLITE_OMIT_VIRTUALTABLE int nVTrans; /* Allocated size of aVTrans */ Hash aModule; /* populated by sqlite3_create_module() */ VtabCtx *pVtabCtx; /* Context for active vtab connect/create */ VTable **aVTrans; /* Virtual tables with open transactions */ VTable *pDisconnect; /* Disconnect these in next sqlite3_prepare() */ #endif Hash aFunc; /* Hash table of connection functions */ Hash aCollSeq; /* All collating sequences */ BusyHandler busyHandler; /* Busy callback */ Db aDbStatic[2]; /* Static space for the 2 default backends */ Savepoint *pSavepoint; /* List of active savepoints */ int busyTimeout; /* Busy handler timeout, in msec */ | > | 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 | #endif #ifndef SQLITE_OMIT_VIRTUALTABLE int nVTrans; /* Allocated size of aVTrans */ Hash aModule; /* populated by sqlite3_create_module() */ VtabCtx *pVtabCtx; /* Context for active vtab connect/create */ VTable **aVTrans; /* Virtual tables with open transactions */ VTable *pDisconnect; /* Disconnect these in next sqlite3_prepare() */ void *pBestIndexCtx; /* For sqlite3_vtab_collation() */ #endif Hash aFunc; /* Hash table of connection functions */ Hash aCollSeq; /* All collating sequences */ BusyHandler busyHandler; /* Busy callback */ Db aDbStatic[2]; /* Static space for the 2 default backends */ Savepoint *pSavepoint; /* List of active savepoints */ int busyTimeout; /* Busy handler timeout, in msec */ |
︙ | ︙ |
Changes to src/tclsqlite.c.
︙ | ︙ | |||
4176 4177 4178 4179 4180 4181 4182 4183 4184 4185 4186 4187 4188 4189 | extern int Sqlitequota_Init(Tcl_Interp*); extern int Sqlitemultiplex_Init(Tcl_Interp*); extern int SqliteSuperlock_Init(Tcl_Interp*); extern int SqlitetestSyscall_Init(Tcl_Interp*); #if defined(SQLITE_ENABLE_SESSION) && defined(SQLITE_ENABLE_PREUPDATE_HOOK) extern int TestSession_Init(Tcl_Interp*); #endif extern int Fts5tcl_Init(Tcl_Interp *); extern int SqliteRbu_Init(Tcl_Interp*); extern int Sqlitetesttcl_Init(Tcl_Interp*); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) extern int Sqlitetestfts3_Init(Tcl_Interp *interp); #endif | > | 4176 4177 4178 4179 4180 4181 4182 4183 4184 4185 4186 4187 4188 4189 4190 | extern int Sqlitequota_Init(Tcl_Interp*); extern int Sqlitemultiplex_Init(Tcl_Interp*); extern int SqliteSuperlock_Init(Tcl_Interp*); extern int SqlitetestSyscall_Init(Tcl_Interp*); #if defined(SQLITE_ENABLE_SESSION) && defined(SQLITE_ENABLE_PREUPDATE_HOOK) extern int TestSession_Init(Tcl_Interp*); #endif extern int TestExpert_Init(Tcl_Interp*); extern int Fts5tcl_Init(Tcl_Interp *); extern int SqliteRbu_Init(Tcl_Interp*); extern int Sqlitetesttcl_Init(Tcl_Interp*); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) extern int Sqlitetestfts3_Init(Tcl_Interp *interp); #endif |
︙ | ︙ | |||
4224 4225 4226 4227 4228 4229 4230 4231 4232 4233 4234 4235 4236 4237 | Sqlitequota_Init(interp); Sqlitemultiplex_Init(interp); SqliteSuperlock_Init(interp); SqlitetestSyscall_Init(interp); #if defined(SQLITE_ENABLE_SESSION) && defined(SQLITE_ENABLE_PREUPDATE_HOOK) TestSession_Init(interp); #endif Fts5tcl_Init(interp); SqliteRbu_Init(interp); Sqlitetesttcl_Init(interp); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) Sqlitetestfts3_Init(interp); #endif | > | 4225 4226 4227 4228 4229 4230 4231 4232 4233 4234 4235 4236 4237 4238 4239 | Sqlitequota_Init(interp); Sqlitemultiplex_Init(interp); SqliteSuperlock_Init(interp); SqlitetestSyscall_Init(interp); #if defined(SQLITE_ENABLE_SESSION) && defined(SQLITE_ENABLE_PREUPDATE_HOOK) TestSession_Init(interp); #endif TestExpert_Init(interp); Fts5tcl_Init(interp); SqliteRbu_Init(interp); Sqlitetesttcl_Init(interp); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) Sqlitetestfts3_Init(interp); #endif |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 | int nSub = 0; /* Number of sub-vdbes seen so far */ SubProgram **apSub = 0; /* Array of sub-vdbes */ Mem *pSub = 0; /* Memory cell hold array of subprogs */ sqlite3 *db = p->db; /* The database connection */ int i; /* Loop counter */ int rc = SQLITE_OK; /* Return code */ Mem *pMem = &p->aMem[1]; /* First Mem of result set */ assert( p->explain ); assert( p->magic==VDBE_MAGIC_RUN ); assert( p->rc==SQLITE_OK || p->rc==SQLITE_BUSY || p->rc==SQLITE_NOMEM ); /* Even though this opcode does not use dynamic strings for ** the result, result columns may become dynamic if the user calls | > > | 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 | int nSub = 0; /* Number of sub-vdbes seen so far */ SubProgram **apSub = 0; /* Array of sub-vdbes */ Mem *pSub = 0; /* Memory cell hold array of subprogs */ sqlite3 *db = p->db; /* The database connection */ int i; /* Loop counter */ int rc = SQLITE_OK; /* Return code */ Mem *pMem = &p->aMem[1]; /* First Mem of result set */ int bFull = (p->explain==1 || db->bFullEQP); Op *pOp = 0; assert( p->explain ); assert( p->magic==VDBE_MAGIC_RUN ); assert( p->rc==SQLITE_OK || p->rc==SQLITE_BUSY || p->rc==SQLITE_NOMEM ); /* Even though this opcode does not use dynamic strings for ** the result, result columns may become dynamic if the user calls |
︙ | ︙ | |||
1662 1663 1664 1665 1666 1667 1668 | ** listing has finished and sqlite3_step() should return SQLITE_DONE. ** nRow is the sum of the number of rows in the main program, plus ** the sum of the number of rows in all trigger subprograms encountered ** so far. The nRow value will increase as new trigger subprograms are ** encountered, but p->pc will eventually catch up to nRow. */ nRow = p->nOp; | | < | | | < < < < | < < > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | | | | | | | | | | < < < < < < < < < < < < < < < < < < < | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > | 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 | ** listing has finished and sqlite3_step() should return SQLITE_DONE. ** nRow is the sum of the number of rows in the main program, plus ** the sum of the number of rows in all trigger subprograms encountered ** so far. The nRow value will increase as new trigger subprograms are ** encountered, but p->pc will eventually catch up to nRow. */ nRow = p->nOp; if( bFull ){ /* The first 8 memory cells are used for the result set. So we will ** commandeer the 9th cell to use as storage for an array of pointers ** to trigger subprograms. The VDBE is guaranteed to have at least 9 ** cells. */ assert( p->nMem>9 ); pSub = &p->aMem[9]; if( pSub->flags&MEM_Blob ){ /* On the first call to sqlite3_step(), pSub will hold a NULL. It is ** initialized to a BLOB by the P4_SUBPROGRAM processing logic below */ nSub = pSub->n/sizeof(Vdbe*); apSub = (SubProgram **)pSub->z; } for(i=0; i<nSub; i++){ nRow += apSub[i]->nOp; } } do{ i = p->pc++; if( i>=nRow ){ p->rc = SQLITE_OK; rc = SQLITE_DONE; break; } if( i<p->nOp ){ /* The output line number is small enough that we are still in the ** main program. */ pOp = &p->aOp[i]; }else{ /* We are currently listing subprograms. Figure out which one and ** pick up the appropriate opcode. */ int j; i -= p->nOp; for(j=0; i>=apSub[j]->nOp; j++){ i -= apSub[j]->nOp; } pOp = &apSub[j]->aOp[i]; } /* When an OP_Program opcode is encounter (the only opcode that has ** a P4_SUBPROGRAM argument), expand the size of the array of subprograms ** kept in p->aMem[9].z to hold the new program - assuming this subprogram ** has not already been seen. */ if( bFull && pOp->p4type==P4_SUBPROGRAM ){ int nByte = (nSub+1)*sizeof(SubProgram*); int j; for(j=0; j<nSub; j++){ if( apSub[j]==pOp->p4.pProgram ) break; } if( j==nSub ){ rc = sqlite3VdbeMemGrow(pSub, nByte, nSub!=0); if( rc!=SQLITE_OK ) break; apSub = (SubProgram **)pSub->z; apSub[nSub++] = pOp->p4.pProgram; pSub->flags |= MEM_Blob; pSub->n = nSub*sizeof(SubProgram*); nRow += pOp->p4.pProgram->nOp; } } }while( p->explain==2 && pOp->opcode!=OP_Explain ); if( rc==SQLITE_OK ){ if( db->u1.isInterrupted ){ p->rc = SQLITE_INTERRUPT; rc = SQLITE_ERROR; sqlite3VdbeError(p, sqlite3ErrStr(p->rc)); }else{ char *zP4; if( p->explain==1 ){ pMem->flags = MEM_Int; pMem->u.i = i; /* Program counter */ pMem++; pMem->flags = MEM_Static|MEM_Str|MEM_Term; pMem->z = (char*)sqlite3OpcodeName(pOp->opcode); /* Opcode */ assert( pMem->z!=0 ); pMem->n = sqlite3Strlen30(pMem->z); pMem->enc = SQLITE_UTF8; pMem++; } pMem->flags = MEM_Int; pMem->u.i = pOp->p1; /* P1 */ pMem++; pMem->flags = MEM_Int; pMem->u.i = pOp->p2; /* P2 */ pMem++; pMem->flags = MEM_Int; pMem->u.i = pOp->p3; /* P3 */ pMem++; if( sqlite3VdbeMemClearAndResize(pMem, 100) ){ /* P4 */ assert( p->db->mallocFailed ); return SQLITE_ERROR; } pMem->flags = MEM_Str|MEM_Term; zP4 = displayP4(pOp, pMem->z, pMem->szMalloc); if( zP4!=pMem->z ){ pMem->n = 0; sqlite3VdbeMemSetStr(pMem, zP4, -1, SQLITE_UTF8, 0); }else{ assert( pMem->z!=0 ); pMem->n = sqlite3Strlen30(pMem->z); pMem->enc = SQLITE_UTF8; } pMem++; if( p->explain==1 ){ if( sqlite3VdbeMemClearAndResize(pMem, 4) ){ assert( p->db->mallocFailed ); return SQLITE_ERROR; } pMem->flags = MEM_Str|MEM_Term; pMem->n = 2; sqlite3_snprintf(3, pMem->z, "%.2x", pOp->p5); /* P5 */ pMem->enc = SQLITE_UTF8; pMem++; #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS if( sqlite3VdbeMemClearAndResize(pMem, 500) ){ assert( p->db->mallocFailed ); return SQLITE_ERROR; } pMem->flags = MEM_Str|MEM_Term; pMem->n = displayComment(pOp, zP4, pMem->z, 500); pMem->enc = SQLITE_UTF8; #else pMem->flags = MEM_Null; /* Comment */ #endif } p->nResColumn = 8 - 4*(p->explain-1); p->pResultSet = &p->aMem[1]; p->rc = SQLITE_OK; rc = SQLITE_ROW; } } return rc; } #endif /* SQLITE_OMIT_EXPLAIN */ #ifdef SQLITE_DEBUG /* |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 | WHERETRACE(0xffff, (" bIn=%d prereqIn=%04llx prereqOut=%04llx\n", *pbIn, (sqlite3_uint64)mPrereq, (sqlite3_uint64)(pNew->prereq & ~mPrereq))); return rc; } /* ** Add all WhereLoop objects for a table of the join identified by ** pBuilder->pNew->iTab. That table is guaranteed to be a virtual table. ** ** If there are no LEFT or CROSS JOIN joins in the query, both mPrereq and ** mUnusable are set to 0. Otherwise, mPrereq is a mask of all FROM clause | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 3148 3149 3150 3151 3152 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 | WHERETRACE(0xffff, (" bIn=%d prereqIn=%04llx prereqOut=%04llx\n", *pbIn, (sqlite3_uint64)mPrereq, (sqlite3_uint64)(pNew->prereq & ~mPrereq))); return rc; } /* ** Context object used to pass information from whereLoopAddVirtual() ** to sqlite3_vtab_collation(). */ struct BestIndexCtx { WhereClause *pWC; sqlite3_index_info *pIdxInfo; Parse *pParse; }; /* ** If this function is invoked from within an xBestIndex() callback, it ** returns a pointer to a buffer containing the name of the collation ** sequence associated with element iCons of the sqlite3_index_info.aConstraint ** array. Or, if iCons is out of range or there is no active xBestIndex ** call, return NULL. */ const char *sqlite3_vtab_collation(sqlite3 *db, int iCons){ struct BestIndexCtx *p = (struct BestIndexCtx*)db->pBestIndexCtx; const char *zRet = 0; if( p && iCons>=0 && iCons<p->pIdxInfo->nConstraint ){ int iTerm = p->pIdxInfo->aConstraint[iCons].iTermOffset; Expr *pX = p->pWC->a[iTerm].pExpr; CollSeq *pC = sqlite3BinaryCompareCollSeq(p->pParse,pX->pLeft,pX->pRight); zRet = (pC ? pC->zName : "BINARY"); } return zRet; } /* ** Add all WhereLoop objects for a table of the join identified by ** pBuilder->pNew->iTab. That table is guaranteed to be a virtual table. ** ** If there are no LEFT or CROSS JOIN joins in the query, both mPrereq and ** mUnusable are set to 0. Otherwise, mPrereq is a mask of all FROM clause |
︙ | ︙ | |||
3173 3174 3175 3176 3177 3178 3179 3180 3181 3182 3183 3184 3185 3186 | struct SrcList_item *pSrc; /* The FROM clause term to search */ sqlite3_index_info *p; /* Object to pass to xBestIndex() */ int nConstraint; /* Number of constraints in p */ int bIn; /* True if plan uses IN(...) operator */ WhereLoop *pNew; Bitmask mBest; /* Tables used by best possible plan */ u16 mNoOmit; assert( (mPrereq & mUnusable)==0 ); pWInfo = pBuilder->pWInfo; pParse = pWInfo->pParse; pWC = pBuilder->pWC; pNew = pBuilder->pNew; pSrc = &pWInfo->pTabList->a[pNew->iTab]; | > > | 3202 3203 3204 3205 3206 3207 3208 3209 3210 3211 3212 3213 3214 3215 3216 3217 | struct SrcList_item *pSrc; /* The FROM clause term to search */ sqlite3_index_info *p; /* Object to pass to xBestIndex() */ int nConstraint; /* Number of constraints in p */ int bIn; /* True if plan uses IN(...) operator */ WhereLoop *pNew; Bitmask mBest; /* Tables used by best possible plan */ u16 mNoOmit; struct BestIndexCtx bic; void *pSaved; assert( (mPrereq & mUnusable)==0 ); pWInfo = pBuilder->pWInfo; pParse = pWInfo->pParse; pWC = pBuilder->pWC; pNew = pBuilder->pNew; pSrc = &pWInfo->pTabList->a[pNew->iTab]; |
︙ | ︙ | |||
3194 3195 3196 3197 3198 3199 3200 3201 3202 3203 3204 3205 3206 3207 | pNew->u.vtab.needFree = 0; nConstraint = p->nConstraint; if( whereLoopResize(pParse->db, pNew, nConstraint) ){ sqlite3DbFree(pParse->db, p); return SQLITE_NOMEM_BKPT; } /* First call xBestIndex() with all constraints usable. */ WHERETRACE(0x40, (" VirtualOne: all usable\n")); rc = whereLoopAddVirtualOne(pBuilder, mPrereq, ALLBITS, 0, p, mNoOmit, &bIn); /* If the call to xBestIndex() with all terms enabled produced a plan ** that does not require any source tables (IOW: a plan with mBest==0), ** then there is no point in making any further calls to xBestIndex() | > > > > > > | 3225 3226 3227 3228 3229 3230 3231 3232 3233 3234 3235 3236 3237 3238 3239 3240 3241 3242 3243 3244 | pNew->u.vtab.needFree = 0; nConstraint = p->nConstraint; if( whereLoopResize(pParse->db, pNew, nConstraint) ){ sqlite3DbFree(pParse->db, p); return SQLITE_NOMEM_BKPT; } bic.pWC = pWC; bic.pIdxInfo = p; bic.pParse = pParse; pSaved = pParse->db->pBestIndexCtx; pParse->db->pBestIndexCtx = (void*)&bic; /* First call xBestIndex() with all constraints usable. */ WHERETRACE(0x40, (" VirtualOne: all usable\n")); rc = whereLoopAddVirtualOne(pBuilder, mPrereq, ALLBITS, 0, p, mNoOmit, &bIn); /* If the call to xBestIndex() with all terms enabled produced a plan ** that does not require any source tables (IOW: a plan with mBest==0), ** then there is no point in making any further calls to xBestIndex() |
︙ | ︙ | |||
3270 3271 3272 3273 3274 3275 3276 3277 3278 3279 3280 3281 3282 3283 | rc = whereLoopAddVirtualOne( pBuilder, mPrereq, mPrereq, WO_IN, p, mNoOmit, &bIn); } } if( p->needToFreeIdxStr ) sqlite3_free(p->idxStr); sqlite3DbFreeNN(pParse->db, p); return rc; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ /* ** Add WhereLoop entries to handle OR terms. This works for either ** btrees or virtual tables. | > | 3307 3308 3309 3310 3311 3312 3313 3314 3315 3316 3317 3318 3319 3320 3321 | rc = whereLoopAddVirtualOne( pBuilder, mPrereq, mPrereq, WO_IN, p, mNoOmit, &bIn); } } if( p->needToFreeIdxStr ) sqlite3_free(p->idxStr); sqlite3DbFreeNN(pParse->db, p); pParse->db->pBestIndexCtx = pSaved; return rc; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ /* ** Add WhereLoop entries to handle OR terms. This works for either ** btrees or virtual tables. |
︙ | ︙ |
Changes to test/permutations.test.
︙ | ︙ | |||
82 83 84 85 86 87 88 | # various test scripts: # # $alltests # $allquicktests # set alltests [list] foreach f [glob $testdir/*.test] { lappend alltests [file tail $f] } | | | > | 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | # various test scripts: # # $alltests # $allquicktests # set alltests [list] foreach f [glob $testdir/*.test] { lappend alltests [file tail $f] } foreach f [glob -nocomplain \ $testdir/../ext/rtree/*.test \ $testdir/../ext/fts5/test/*.test \ $testdir/../ext/expert/*.test \ $testdir/../ext/lsm1/test/*.test \ ] { lappend alltests $f } foreach f [glob -nocomplain $testdir/../ext/session/*.test] { lappend alltests $f } |
︙ | ︙ |