Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Merge enhancements from trunk. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | sqlar-shell-support |
Files: | files | file ages | folders |
SHA3-256: |
150f07fec1e6d1fc0601820d717d8712 |
User & Date: | drh 2017-12-23 18:34:49.545 |
Context
2017-12-26
| ||
20:39 | Add new file ext/misc/zipfile.c, containing a virtual table for read-only access to simple zip archives. (check-in: 8e366b99b1 user: dan tags: sqlar-shell-support) | |
2017-12-23
| ||
18:34 | Merge enhancements from trunk. (check-in: 150f07fec1 user: drh tags: sqlar-shell-support) | |
14:39 | Simplification to the error handling logic in the extension loader. (check-in: 07c773148d user: drh tags: trunk) | |
2017-12-16
| ||
19:11 | Do not use the compress() and uncompress() functions in ext/misc/compress.c - they are not quite compatible with the spec. Instead use new functions in ext/misc/sqlar.c. (check-in: 7652b3c237 user: dan tags: sqlar-shell-support) | |
Changes
Changes to Makefile.in.
︙ | ︙ | |||
421 422 423 424 425 426 427 428 429 430 431 432 433 434 | $(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 \ | > > | 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 | $(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 \ |
︙ | ︙ | |||
1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 | sqlite3_analyzer.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/spaceanal.tcl $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in $(TCLSH_CMD) $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in >sqlite3_analyzer.c sqlite3_analyzer$(TEXE): sqlite3_analyzer.c $(LTLINK) sqlite3_analyzer.c -o $@ $(LIBTCL) $(TLIBS) CHECKER_DEPS =\ $(TOP)/tool/mkccode.tcl \ sqlite3.c \ $(TOP)/src/tclsqlite.c \ $(TOP)/ext/repair/sqlite3_checker.tcl \ $(TOP)/ext/repair/checkindex.c \ $(TOP)/ext/repair/checkfreelist.c \ | > > > | 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 | sqlite3_analyzer.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/spaceanal.tcl $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in $(TCLSH_CMD) $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in >sqlite3_analyzer.c 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) CHECKER_DEPS =\ $(TOP)/tool/mkccode.tcl \ sqlite3.c \ $(TOP)/src/tclsqlite.c \ $(TOP)/ext/repair/sqlite3_checker.tcl \ $(TOP)/ext/repair/checkindex.c \ $(TOP)/ext/repair/checkfreelist.c \ |
︙ | ︙ |
Changes to Makefile.msc.
︙ | ︙ | |||
1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 | $(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 \ | > > | 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 | $(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 \ |
︙ | ︙ | |||
2301 2302 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 | sqlite3_analyzer.c: $(SQLITE3C) $(SQLITE3H) $(TOP)\src\tclsqlite.c $(TOP)\tool\spaceanal.tcl $(TOP)\tool\mkccode.tcl $(TOP)\tool\sqlite3_analyzer.c.in $(SQLITE_TCL_DEP) $(TCLSH_CMD) $(TOP)\tool\mkccode.tcl $(TOP)\tool\sqlite3_analyzer.c.in > $@ sqlite3_analyzer.exe: sqlite3_analyzer.c $(LIBRESOBJS) $(LTLINK) $(NO_WARN) -DBUILD_sqlite -I$(TCLINCDIR) sqlite3_analyzer.c \ /link $(LDFLAGS) $(LTLINKOPTS) $(LTLIBPATHS) $(LIBRESOBJS) $(LTLIBS) $(TLIBS) CHECKER_DEPS =\ $(TOP)/tool/mkccode.tcl \ sqlite3.c \ $(TOP)/src/tclsqlite.c \ $(TOP)/ext/repair/sqlite3_checker.tcl \ $(TOP)/ext/repair/checkindex.c \ | > > > | 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 2315 2316 2317 2318 2319 | sqlite3_analyzer.c: $(SQLITE3C) $(SQLITE3H) $(TOP)\src\tclsqlite.c $(TOP)\tool\spaceanal.tcl $(TOP)\tool\mkccode.tcl $(TOP)\tool\sqlite3_analyzer.c.in $(SQLITE_TCL_DEP) $(TCLSH_CMD) $(TOP)\tool\mkccode.tcl $(TOP)\tool\sqlite3_analyzer.c.in > $@ 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) CHECKER_DEPS =\ $(TOP)/tool/mkccode.tcl \ sqlite3.c \ $(TOP)/src/tclsqlite.c \ $(TOP)/ext/repair/sqlite3_checker.tcl \ $(TOP)/ext/repair/checkindex.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 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 | # 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 set CLI [test_binary_name sqlite3] set CMD [test_binary_name 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]]] } } 3 { if {![file executable $CLI]} { continue } proc do_rec_test {tn sql res} { set res [squish [string trim $res]] set tst [subst -nocommands { squish [string trim [exec $::CLI test.db ".expert" {$sql;}]] }] uplevel [list do_test $tn $tst $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=?) } do_setup_rec_test $tn.16 { CREATE TABLE t1(a, b); } { SELECT * FROM t1 WHERE b IS NOT NULL; } { (no new indexes) 0|0|0|SCAN TABLE t1 } } 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 | /* ** 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; 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(pIdxInfo, 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_TRIGGER_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 ext/misc/shathree.c.
︙ | ︙ | |||
74 75 76 77 78 79 80 | }; /* ** A single step of the Keccak mixing function for a 1600-bit state */ static void KeccakF1600Step(SHA3Context *p){ int i; | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | < > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | < > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | < > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 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 | }; /* ** A single step of the Keccak mixing function for a 1600-bit state */ static void KeccakF1600Step(SHA3Context *p){ int i; u64 b0, b1, b2, b3, b4; u64 c0, c1, c2, c3, c4; u64 d0, d1, d2, d3, d4; static const u64 RC[] = { 0x0000000000000001ULL, 0x0000000000008082ULL, 0x800000000000808aULL, 0x8000000080008000ULL, 0x000000000000808bULL, 0x0000000080000001ULL, 0x8000000080008081ULL, 0x8000000000008009ULL, 0x000000000000008aULL, 0x0000000000000088ULL, 0x0000000080008009ULL, 0x000000008000000aULL, 0x000000008000808bULL, 0x800000000000008bULL, 0x8000000000008089ULL, 0x8000000000008003ULL, 0x8000000000008002ULL, 0x8000000000000080ULL, 0x000000000000800aULL, 0x800000008000000aULL, 0x8000000080008081ULL, 0x8000000000008080ULL, 0x0000000080000001ULL, 0x8000000080008008ULL }; # define a00 (p->u.s[0]) # define a01 (p->u.s[1]) # define a02 (p->u.s[2]) # define a03 (p->u.s[3]) # define a04 (p->u.s[4]) # define a10 (p->u.s[5]) # define a11 (p->u.s[6]) # define a12 (p->u.s[7]) # define a13 (p->u.s[8]) # define a14 (p->u.s[9]) # define a20 (p->u.s[10]) # define a21 (p->u.s[11]) # define a22 (p->u.s[12]) # define a23 (p->u.s[13]) # define a24 (p->u.s[14]) # define a30 (p->u.s[15]) # define a31 (p->u.s[16]) # define a32 (p->u.s[17]) # define a33 (p->u.s[18]) # define a34 (p->u.s[19]) # define a40 (p->u.s[20]) # define a41 (p->u.s[21]) # define a42 (p->u.s[22]) # define a43 (p->u.s[23]) # define a44 (p->u.s[24]) # define ROL64(a,x) ((a<<x)|(a>>(64-x))) for(i=0; i<24; i+=4){ c0 = a00^a10^a20^a30^a40; c1 = a01^a11^a21^a31^a41; c2 = a02^a12^a22^a32^a42; c3 = a03^a13^a23^a33^a43; c4 = a04^a14^a24^a34^a44; d0 = c4^ROL64(c1, 1); d1 = c0^ROL64(c2, 1); d2 = c1^ROL64(c3, 1); d3 = c2^ROL64(c4, 1); d4 = c3^ROL64(c0, 1); b0 = (a00^d0); b1 = ROL64((a11^d1), 44); b2 = ROL64((a22^d2), 43); b3 = ROL64((a33^d3), 21); b4 = ROL64((a44^d4), 14); a00 = b0 ^((~b1)& b2 ); a00 ^= RC[i]; a11 = b1 ^((~b2)& b3 ); a22 = b2 ^((~b3)& b4 ); a33 = b3 ^((~b4)& b0 ); a44 = b4 ^((~b0)& b1 ); b2 = ROL64((a20^d0), 3); b3 = ROL64((a31^d1), 45); b4 = ROL64((a42^d2), 61); b0 = ROL64((a03^d3), 28); b1 = ROL64((a14^d4), 20); a20 = b0 ^((~b1)& b2 ); a31 = b1 ^((~b2)& b3 ); a42 = b2 ^((~b3)& b4 ); a03 = b3 ^((~b4)& b0 ); a14 = b4 ^((~b0)& b1 ); b4 = ROL64((a40^d0), 18); b0 = ROL64((a01^d1), 1); b1 = ROL64((a12^d2), 6); b2 = ROL64((a23^d3), 25); b3 = ROL64((a34^d4), 8); a40 = b0 ^((~b1)& b2 ); a01 = b1 ^((~b2)& b3 ); a12 = b2 ^((~b3)& b4 ); a23 = b3 ^((~b4)& b0 ); a34 = b4 ^((~b0)& b1 ); b1 = ROL64((a10^d0), 36); b2 = ROL64((a21^d1), 10); b3 = ROL64((a32^d2), 15); b4 = ROL64((a43^d3), 56); b0 = ROL64((a04^d4), 27); a10 = b0 ^((~b1)& b2 ); a21 = b1 ^((~b2)& b3 ); a32 = b2 ^((~b3)& b4 ); a43 = b3 ^((~b4)& b0 ); a04 = b4 ^((~b0)& b1 ); b3 = ROL64((a30^d0), 41); b4 = ROL64((a41^d1), 2); b0 = ROL64((a02^d2), 62); b1 = ROL64((a13^d3), 55); b2 = ROL64((a24^d4), 39); a30 = b0 ^((~b1)& b2 ); a41 = b1 ^((~b2)& b3 ); a02 = b2 ^((~b3)& b4 ); a13 = b3 ^((~b4)& b0 ); a24 = b4 ^((~b0)& b1 ); c0 = a00^a20^a40^a10^a30; c1 = a11^a31^a01^a21^a41; c2 = a22^a42^a12^a32^a02; c3 = a33^a03^a23^a43^a13; c4 = a44^a14^a34^a04^a24; d0 = c4^ROL64(c1, 1); d1 = c0^ROL64(c2, 1); d2 = c1^ROL64(c3, 1); d3 = c2^ROL64(c4, 1); d4 = c3^ROL64(c0, 1); b0 = (a00^d0); b1 = ROL64((a31^d1), 44); b2 = ROL64((a12^d2), 43); b3 = ROL64((a43^d3), 21); b4 = ROL64((a24^d4), 14); a00 = b0 ^((~b1)& b2 ); a00 ^= RC[i+1]; a31 = b1 ^((~b2)& b3 ); a12 = b2 ^((~b3)& b4 ); a43 = b3 ^((~b4)& b0 ); a24 = b4 ^((~b0)& b1 ); b2 = ROL64((a40^d0), 3); b3 = ROL64((a21^d1), 45); b4 = ROL64((a02^d2), 61); b0 = ROL64((a33^d3), 28); b1 = ROL64((a14^d4), 20); a40 = b0 ^((~b1)& b2 ); a21 = b1 ^((~b2)& b3 ); a02 = b2 ^((~b3)& b4 ); a33 = b3 ^((~b4)& b0 ); a14 = b4 ^((~b0)& b1 ); b4 = ROL64((a30^d0), 18); b0 = ROL64((a11^d1), 1); b1 = ROL64((a42^d2), 6); b2 = ROL64((a23^d3), 25); b3 = ROL64((a04^d4), 8); a30 = b0 ^((~b1)& b2 ); a11 = b1 ^((~b2)& b3 ); a42 = b2 ^((~b3)& b4 ); a23 = b3 ^((~b4)& b0 ); a04 = b4 ^((~b0)& b1 ); b1 = ROL64((a20^d0), 36); b2 = ROL64((a01^d1), 10); b3 = ROL64((a32^d2), 15); b4 = ROL64((a13^d3), 56); b0 = ROL64((a44^d4), 27); a20 = b0 ^((~b1)& b2 ); a01 = b1 ^((~b2)& b3 ); a32 = b2 ^((~b3)& b4 ); a13 = b3 ^((~b4)& b0 ); a44 = b4 ^((~b0)& b1 ); b3 = ROL64((a10^d0), 41); b4 = ROL64((a41^d1), 2); b0 = ROL64((a22^d2), 62); b1 = ROL64((a03^d3), 55); b2 = ROL64((a34^d4), 39); a10 = b0 ^((~b1)& b2 ); a41 = b1 ^((~b2)& b3 ); a22 = b2 ^((~b3)& b4 ); a03 = b3 ^((~b4)& b0 ); a34 = b4 ^((~b0)& b1 ); c0 = a00^a40^a30^a20^a10; c1 = a31^a21^a11^a01^a41; c2 = a12^a02^a42^a32^a22; c3 = a43^a33^a23^a13^a03; c4 = a24^a14^a04^a44^a34; d0 = c4^ROL64(c1, 1); d1 = c0^ROL64(c2, 1); d2 = c1^ROL64(c3, 1); d3 = c2^ROL64(c4, 1); d4 = c3^ROL64(c0, 1); b0 = (a00^d0); b1 = ROL64((a21^d1), 44); b2 = ROL64((a42^d2), 43); b3 = ROL64((a13^d3), 21); b4 = ROL64((a34^d4), 14); a00 = b0 ^((~b1)& b2 ); a00 ^= RC[i+2]; a21 = b1 ^((~b2)& b3 ); a42 = b2 ^((~b3)& b4 ); a13 = b3 ^((~b4)& b0 ); a34 = b4 ^((~b0)& b1 ); b2 = ROL64((a30^d0), 3); b3 = ROL64((a01^d1), 45); b4 = ROL64((a22^d2), 61); b0 = ROL64((a43^d3), 28); b1 = ROL64((a14^d4), 20); a30 = b0 ^((~b1)& b2 ); a01 = b1 ^((~b2)& b3 ); a22 = b2 ^((~b3)& b4 ); a43 = b3 ^((~b4)& b0 ); a14 = b4 ^((~b0)& b1 ); b4 = ROL64((a10^d0), 18); b0 = ROL64((a31^d1), 1); b1 = ROL64((a02^d2), 6); b2 = ROL64((a23^d3), 25); b3 = ROL64((a44^d4), 8); a10 = b0 ^((~b1)& b2 ); a31 = b1 ^((~b2)& b3 ); a02 = b2 ^((~b3)& b4 ); a23 = b3 ^((~b4)& b0 ); a44 = b4 ^((~b0)& b1 ); b1 = ROL64((a40^d0), 36); b2 = ROL64((a11^d1), 10); b3 = ROL64((a32^d2), 15); b4 = ROL64((a03^d3), 56); b0 = ROL64((a24^d4), 27); a40 = b0 ^((~b1)& b2 ); a11 = b1 ^((~b2)& b3 ); a32 = b2 ^((~b3)& b4 ); a03 = b3 ^((~b4)& b0 ); a24 = b4 ^((~b0)& b1 ); b3 = ROL64((a20^d0), 41); b4 = ROL64((a41^d1), 2); b0 = ROL64((a12^d2), 62); b1 = ROL64((a33^d3), 55); b2 = ROL64((a04^d4), 39); a20 = b0 ^((~b1)& b2 ); a41 = b1 ^((~b2)& b3 ); a12 = b2 ^((~b3)& b4 ); a33 = b3 ^((~b4)& b0 ); a04 = b4 ^((~b0)& b1 ); c0 = a00^a30^a10^a40^a20; c1 = a21^a01^a31^a11^a41; c2 = a42^a22^a02^a32^a12; c3 = a13^a43^a23^a03^a33; c4 = a34^a14^a44^a24^a04; d0 = c4^ROL64(c1, 1); d1 = c0^ROL64(c2, 1); d2 = c1^ROL64(c3, 1); d3 = c2^ROL64(c4, 1); d4 = c3^ROL64(c0, 1); b0 = (a00^d0); b1 = ROL64((a01^d1), 44); b2 = ROL64((a02^d2), 43); b3 = ROL64((a03^d3), 21); b4 = ROL64((a04^d4), 14); a00 = b0 ^((~b1)& b2 ); a00 ^= RC[i+3]; a01 = b1 ^((~b2)& b3 ); a02 = b2 ^((~b3)& b4 ); a03 = b3 ^((~b4)& b0 ); a04 = b4 ^((~b0)& b1 ); b2 = ROL64((a10^d0), 3); b3 = ROL64((a11^d1), 45); b4 = ROL64((a12^d2), 61); b0 = ROL64((a13^d3), 28); b1 = ROL64((a14^d4), 20); a10 = b0 ^((~b1)& b2 ); a11 = b1 ^((~b2)& b3 ); a12 = b2 ^((~b3)& b4 ); a13 = b3 ^((~b4)& b0 ); a14 = b4 ^((~b0)& b1 ); b4 = ROL64((a20^d0), 18); b0 = ROL64((a21^d1), 1); b1 = ROL64((a22^d2), 6); b2 = ROL64((a23^d3), 25); b3 = ROL64((a24^d4), 8); a20 = b0 ^((~b1)& b2 ); a21 = b1 ^((~b2)& b3 ); a22 = b2 ^((~b3)& b4 ); a23 = b3 ^((~b4)& b0 ); a24 = b4 ^((~b0)& b1 ); b1 = ROL64((a30^d0), 36); b2 = ROL64((a31^d1), 10); b3 = ROL64((a32^d2), 15); b4 = ROL64((a33^d3), 56); b0 = ROL64((a34^d4), 27); a30 = b0 ^((~b1)& b2 ); a31 = b1 ^((~b2)& b3 ); a32 = b2 ^((~b3)& b4 ); a33 = b3 ^((~b4)& b0 ); a34 = b4 ^((~b0)& b1 ); b3 = ROL64((a40^d0), 41); b4 = ROL64((a41^d1), 2); b0 = ROL64((a42^d2), 62); b1 = ROL64((a43^d3), 55); b2 = ROL64((a44^d4), 39); a40 = b0 ^((~b1)& b2 ); a41 = b1 ^((~b2)& b3 ); a42 = b2 ^((~b3)& b4 ); a43 = b3 ^((~b4)& b0 ); a44 = b4 ^((~b0)& b1 ); } } /* ** Initialize a new hash. iSize determines the size of the hash ** in bits and should be one of 224, 256, 384, or 512. Or iSize ** can be zero to use the default hash size of 256 bits. |
︙ | ︙ |
Changes to ext/misc/unionvtab.c.
︙ | ︙ | |||
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | ** 3. The smallest rowid in the range of rowids that may be stored in the ** database table (an integer). ** ** 4. The largest rowid in the range of rowids that may be stored in the ** database table (an integer). ** ** SWARMVTAB ** ** A "swarmvtab" virtual table is created similarly to a unionvtab table: ** ** CREATE VIRTUAL TABLE <name> ** USING swarmvtab(<sql-statement>, <callback>); ** ** The difference is that for a swarmvtab table, the first column returned ** by the <sql statement> must return a path or URI that can be used to open ** the database file containing the source table. The <callback> option ** is optional. If included, it is the name of an application-defined ** SQL function that is invoked with the URI of the file, if the file | > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | ** 3. The smallest rowid in the range of rowids that may be stored in the ** database table (an integer). ** ** 4. The largest rowid in the range of rowids that may be stored in the ** database table (an integer). ** ** SWARMVTAB ** ** LEGACY SYNTAX: ** ** A "swarmvtab" virtual table is created similarly to a unionvtab table: ** ** CREATE VIRTUAL TABLE <name> ** USING swarmvtab(<sql-statement>, <callback>); ** ** The difference is that for a swarmvtab table, the first column returned ** by the <sql statement> must return a path or URI that can be used to open ** the database file containing the source table. The <callback> option ** is optional. If included, it is the name of an application-defined ** SQL function that is invoked with the URI of the file, if the file ** does not already exist on disk when required by swarmvtab. ** ** NEW SYNTAX: ** ** Using the new syntax, a swarmvtab table is created with: ** ** CREATE VIRTUAL TABLE <name> USING swarmvtab( ** <sql-statement> [, <options>] ** ); ** ** where valid <options> are: ** ** missing=<udf-function-name> ** openclose=<udf-function-name> ** maxopen=<integer> ** <sql-parameter>=<text-value> ** ** The <sql-statement> must return the same 4 columns as for a swarmvtab ** table in legacy mode. However, it may also return a 5th column - the ** "context" column. The text value returned in this column is not used ** at all by the swarmvtab implementation, except that it is passed as ** an additional argument to the two UDF functions that may be invoked ** (see below). ** ** The "missing" option, if present, specifies the name of an SQL UDF ** function to be invoked if a database file is not already present on ** disk when required by swarmvtab. If the <sql-statement> did not provide ** a context column, it is invoked as: ** ** SELECT <missing-udf>(<database filename/uri>); ** ** Or, if there was a context column: ** ** SELECT <missing-udf>(<database filename/uri>, <context>); ** ** The "openclose" option may also specify a UDF function. This function ** is invoked right before swarmvtab opens a database, and right after ** it closes one. The first argument - or first two arguments, if ** <sql-statement> supplied the context column - is the same as for ** the "missing" UDF. Following this, the UDF is passed integer value ** 0 before a db is opened, and 1 right after it is closed. If both ** a missing and openclose UDF is supplied, the application should expect ** the following sequence of calls (for a single database): ** ** SELECT <openclose-udf>(<db filename>, <context>, 0); ** if( db not already on disk ){ ** SELECT <missing-udf>(<db filename>, <context>); ** } ** ... swarmvtab uses database ... ** SELECT <openclose-udf>(<db filename>, <context>, 1); ** ** The "maxopen" option is used to configure the maximum number of ** database files swarmvtab will hold open simultaneously (default 9). ** ** If an option name begins with a ":" character, then it is assumed ** to be an SQL parameter. In this case, the specified text value is ** bound to the same variable of the <sql-statement> before it is ** executed. It is an error of the named SQL parameter does not exist. ** For example: ** ** CREATE VIRTUAL TABLE swarm USING swarmvtab( ** 'SELECT :path || localfile, tbl, min, max FROM swarmdir', ** :path='/home/user/databases/' ** missing='missing_func' ** ); */ #include "sqlite3ext.h" SQLITE_EXTENSION_INIT1 #include <assert.h> #include <string.h> #include <stdlib.h> #ifndef SQLITE_OMIT_VIRTUALTABLE /* ** Largest and smallest possible 64-bit signed integers. These macros ** copied from sqliteInt.h. */ |
︙ | ︙ | |||
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 | char *zDb; /* Database containing source table */ char *zTab; /* Source table name */ sqlite3_int64 iMin; /* Minimum rowid */ sqlite3_int64 iMax; /* Maximum rowid */ /* Fields used by swarmvtab only */ char *zFile; /* Database file containing table zTab */ int nUser; /* Current number of users */ sqlite3 *db; /* Database handle */ UnionSrc *pNextClosable; /* Next in list of closable sources */ }; /* ** Virtual table type for union vtab. */ struct UnionTab { sqlite3_vtab base; /* Base class - must be first */ sqlite3 *db; /* Database handle */ int bSwarm; /* 1 for "swarmvtab", 0 for "unionvtab" */ int iPK; /* INTEGER PRIMARY KEY column, or -1 */ int nSrc; /* Number of elements in the aSrc[] array */ UnionSrc *aSrc; /* Array of source tables, sorted by rowid */ /* Used by swarmvtab only */ char *zSourceStr; /* Expected unionSourceToStr() value */ | > > | > > | 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 | char *zDb; /* Database containing source table */ char *zTab; /* Source table name */ sqlite3_int64 iMin; /* Minimum rowid */ sqlite3_int64 iMax; /* Maximum rowid */ /* Fields used by swarmvtab only */ char *zFile; /* Database file containing table zTab */ char *zContext; /* Context string, if any */ int nUser; /* Current number of users */ sqlite3 *db; /* Database handle */ UnionSrc *pNextClosable; /* Next in list of closable sources */ }; /* ** Virtual table type for union vtab. */ struct UnionTab { sqlite3_vtab base; /* Base class - must be first */ sqlite3 *db; /* Database handle */ int bSwarm; /* 1 for "swarmvtab", 0 for "unionvtab" */ int iPK; /* INTEGER PRIMARY KEY column, or -1 */ int nSrc; /* Number of elements in the aSrc[] array */ UnionSrc *aSrc; /* Array of source tables, sorted by rowid */ /* Used by swarmvtab only */ int bHasContext; /* Has context strings */ char *zSourceStr; /* Expected unionSourceToStr() value */ sqlite3_stmt *pNotFound; /* UDF to invoke if file not found on open */ sqlite3_stmt *pOpenClose; /* UDF to invoke on open and close */ UnionSrc *pClosable; /* First in list of closable sources */ int nOpen; /* Current number of open sources */ int nMaxOpen; /* Maximum number of open sources */ }; /* ** Virtual table cursor type for union vtab. |
︙ | ︙ | |||
346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 | if( *pRc==SQLITE_OK ){ *pRc = rc; if( rc ){ *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db)); } } } /* ** This function is a no-op for unionvtab. For swarmvtab, it attempts to ** close open database files until at most nMax are open. An SQLite error ** code is returned if an error occurs, or SQLITE_OK otherwise. */ static void unionCloseSources(UnionTab *pTab, int nMax){ while( pTab->pClosable && pTab->nOpen>nMax ){ UnionSrc **pp; for(pp=&pTab->pClosable; (*pp)->pNextClosable; pp=&(*pp)->pNextClosable); | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | | > > > > > | > | | 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 | if( *pRc==SQLITE_OK ){ *pRc = rc; if( rc ){ *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db)); } } } /* ** If an "openclose" UDF was supplied when this virtual table was created, ** invoke it now. The first argument passed is the name of the database ** file for source pSrc. The second is integer value bClose. ** ** If successful, return SQLITE_OK. Otherwise an SQLite error code. In this ** case if argument pzErr is not NULL, also set (*pzErr) to an English ** language error message. The caller is responsible for eventually freeing ** any error message using sqlite3_free(). */ static int unionInvokeOpenClose( UnionTab *pTab, UnionSrc *pSrc, int bClose, char **pzErr ){ int rc = SQLITE_OK; if( pTab->pOpenClose ){ sqlite3_bind_text(pTab->pOpenClose, 1, pSrc->zFile, -1, SQLITE_STATIC); if( pTab->bHasContext ){ sqlite3_bind_text(pTab->pOpenClose, 2, pSrc->zContext, -1, SQLITE_STATIC); } sqlite3_bind_int(pTab->pOpenClose, 2+pTab->bHasContext, bClose); sqlite3_step(pTab->pOpenClose); if( SQLITE_OK!=(rc = sqlite3_reset(pTab->pOpenClose)) ){ if( pzErr ){ *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db)); } } } return rc; } /* ** This function is a no-op for unionvtab. For swarmvtab, it attempts to ** close open database files until at most nMax are open. An SQLite error ** code is returned if an error occurs, or SQLITE_OK otherwise. */ static void unionCloseSources(UnionTab *pTab, int nMax){ while( pTab->pClosable && pTab->nOpen>nMax ){ UnionSrc *p; UnionSrc **pp; for(pp=&pTab->pClosable; (*pp)->pNextClosable; pp=&(*pp)->pNextClosable); p = *pp; assert( p->db ); sqlite3_close(p->db); p->db = 0; *pp = 0; pTab->nOpen--; unionInvokeOpenClose(pTab, p, 1, 0); } } /* ** xDisconnect method. */ static int unionDisconnect(sqlite3_vtab *pVtab){ if( pVtab ){ UnionTab *pTab = (UnionTab*)pVtab; int i; for(i=0; i<pTab->nSrc; i++){ UnionSrc *pSrc = &pTab->aSrc[i]; if( pSrc->db ){ unionInvokeOpenClose(pTab, pSrc, 1, 0); } sqlite3_free(pSrc->zDb); sqlite3_free(pSrc->zTab); sqlite3_free(pSrc->zFile); sqlite3_free(pSrc->zContext); sqlite3_close(pSrc->db); } sqlite3_finalize(pTab->pNotFound); sqlite3_finalize(pTab->pOpenClose); sqlite3_free(pTab->zSourceStr); sqlite3_free(pTab->aSrc); sqlite3_free(pTab); } return SQLITE_OK; } /* |
︙ | ︙ | |||
492 493 494 495 496 497 498 | sqlite3_free(z); } sqlite3_free(z0); return rc; } | < > | | > > | | < < > | > > > > | | < < < | 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 | sqlite3_free(z); } sqlite3_free(z0); return rc; } /* ** Try to open the swarmvtab database. If initially unable, invoke the ** not-found callback UDF and then try again. */ static int unionOpenDatabaseInner(UnionTab *pTab, UnionSrc *pSrc, char **pzErr){ static const int openFlags = SQLITE_OPEN_READONLY | SQLITE_OPEN_URI; int rc; rc = unionInvokeOpenClose(pTab, pSrc, 0, pzErr); if( rc!=SQLITE_OK ) return rc; rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0); if( rc==SQLITE_OK ) return rc; if( pTab->pNotFound ){ sqlite3_close(pSrc->db); pSrc->db = 0; sqlite3_bind_text(pTab->pNotFound, 1, pSrc->zFile, -1, SQLITE_STATIC); if( pTab->bHasContext ){ sqlite3_bind_text(pTab->pNotFound, 2, pSrc->zContext, -1, SQLITE_STATIC); } sqlite3_step(pTab->pNotFound); if( SQLITE_OK!=(rc = sqlite3_reset(pTab->pNotFound)) ){ *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db)); return rc; } rc = sqlite3_open_v2(pSrc->zFile, &pSrc->db, openFlags, 0); } if( rc!=SQLITE_OK ){ *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(pSrc->db)); } return rc; } |
︙ | ︙ | |||
568 569 570 571 572 573 574 575 576 577 578 579 580 581 | if( rc==SQLITE_OK ){ pSrc->pNextClosable = pTab->pClosable; pTab->pClosable = pSrc; pTab->nOpen++; }else{ sqlite3_close(pSrc->db); pSrc->db = 0; } } return rc; } | > | 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 | if( rc==SQLITE_OK ){ pSrc->pNextClosable = pTab->pClosable; pTab->pClosable = pSrc; pTab->nOpen++; }else{ sqlite3_close(pSrc->db); pSrc->db = 0; unionInvokeOpenClose(pTab, pSrc, 1, 0); } } return rc; } |
︙ | ︙ | |||
622 623 624 625 626 627 628 629 630 631 632 633 634 635 | pTab->pClosable = pSrc; } unionCloseSources(pTab, pTab->nMaxOpen); } } return rc; } /* ** xConnect/xCreate method. ** ** The argv[] array contains the following: ** ** argv[0] -> module name ("unionvtab" or "swarmvtab") | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | pTab->pClosable = pSrc; } unionCloseSources(pTab, pTab->nMaxOpen); } } return rc; } /* ** Return true if the argument is a space, tab, CR or LF character. */ static int union_isspace(char c){ return (c==' ' || c=='\n' || c=='\r' || c=='\t'); } /* ** Return true if the argument is an alphanumeric character in the ** ASCII range. */ static int union_isidchar(char c){ return ((c>='a' && c<='z') || (c>='A' && c<'Z') || (c>='0' && c<='9')); } /* ** This function is called to handle all arguments following the first ** (the SQL statement) passed to a swarmvtab (not unionvtab) CREATE ** VIRTUAL TABLE statement. It may bind parameters to the SQL statement ** or configure members of the UnionTab object passed as the second ** argument. ** ** Refer to header comments at the top of this file for a description ** of the arguments parsed. ** ** This function is a no-op if *pRc is other than SQLITE_OK when it is ** called. Otherwise, if an error occurs, *pRc is set to an SQLite error ** code. In this case *pzErr may be set to point to a buffer containing ** an English language error message. It is the responsibility of the ** caller to eventually free the buffer using sqlite3_free(). */ static void unionConfigureVtab( int *pRc, /* IN/OUT: Error code */ UnionTab *pTab, /* Table to configure */ sqlite3_stmt *pStmt, /* SQL statement to find sources */ int nArg, /* Number of entries in azArg[] array */ const char * const *azArg, /* Array of arguments to consider */ char **pzErr /* OUT: Error message */ ){ int rc = *pRc; int i; if( rc==SQLITE_OK ){ pTab->bHasContext = (sqlite3_column_count(pStmt)>4); } for(i=0; rc==SQLITE_OK && i<nArg; i++){ char *zArg = unionStrdup(&rc, azArg[i]); if( zArg ){ int nOpt = 0; /* Size of option name in bytes */ char *zOpt; /* Pointer to option name */ char *zVal; /* Pointer to value */ unionDequote(zArg); zOpt = zArg; while( union_isspace(*zOpt) ) zOpt++; zVal = zOpt; if( *zVal==':' ) zVal++; while( union_isidchar(*zVal) ) zVal++; nOpt = zVal-zOpt; while( union_isspace(*zVal) ) zVal++; if( *zVal=='=' ){ zOpt[nOpt] = '\0'; zVal++; while( union_isspace(*zVal) ) zVal++; zVal = unionStrdup(&rc, zVal); if( zVal ){ unionDequote(zVal); if( zOpt[0]==':' ){ /* A value to bind to the SQL statement */ int iParam = sqlite3_bind_parameter_index(pStmt, zOpt); if( iParam==0 ){ *pzErr = sqlite3_mprintf( "swarmvtab: no such SQL parameter: %s", zOpt ); rc = SQLITE_ERROR; }else{ rc = sqlite3_bind_text(pStmt, iParam, zVal, -1, SQLITE_TRANSIENT); } }else if( nOpt==7 && 0==sqlite3_strnicmp(zOpt, "maxopen", 7) ){ pTab->nMaxOpen = atoi(zVal); if( pTab->nMaxOpen<=0 ){ *pzErr = sqlite3_mprintf("swarmvtab: illegal maxopen value"); rc = SQLITE_ERROR; } }else if( nOpt==7 && 0==sqlite3_strnicmp(zOpt, "missing", 7) ){ if( pTab->pNotFound ){ *pzErr = sqlite3_mprintf( "swarmvtab: duplicate \"missing\" option"); rc = SQLITE_ERROR; }else{ pTab->pNotFound = unionPreparePrintf(&rc, pzErr, pTab->db, "SELECT \"%w\"(?%s)", zVal, pTab->bHasContext ? ",?" : "" ); } }else if( nOpt==9 && 0==sqlite3_strnicmp(zOpt, "openclose", 9) ){ if( pTab->pOpenClose ){ *pzErr = sqlite3_mprintf( "swarmvtab: duplicate \"openclose\" option"); rc = SQLITE_ERROR; }else{ pTab->pOpenClose = unionPreparePrintf(&rc, pzErr, pTab->db, "SELECT \"%w\"(?,?%s)", zVal, pTab->bHasContext ? ",?" : "" ); } }else{ *pzErr = sqlite3_mprintf("swarmvtab: unrecognized option: %s",zOpt); rc = SQLITE_ERROR; } sqlite3_free(zVal); } }else{ if( i==0 && nArg==1 ){ pTab->pNotFound = unionPreparePrintf(&rc, pzErr, pTab->db, "SELECT \"%w\"(?)", zArg ); }else{ *pzErr = sqlite3_mprintf( "swarmvtab: parse error: %s", azArg[i]); rc = SQLITE_ERROR; } } sqlite3_free(zArg); } } *pRc = rc; } /* ** xConnect/xCreate method. ** ** The argv[] array contains the following: ** ** argv[0] -> module name ("unionvtab" or "swarmvtab") |
︙ | ︙ | |||
650 651 652 653 654 655 656 | int bSwarm = (pAux==0 ? 0 : 1); const char *zVtab = (bSwarm ? "swarmvtab" : "unionvtab"); if( sqlite3_stricmp("temp", argv[1]) ){ /* unionvtab tables may only be created in the temp schema */ *pzErr = sqlite3_mprintf("%s tables must be created in TEMP schema", zVtab); rc = SQLITE_ERROR; | | > > > > > > > > > > > | 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 | int bSwarm = (pAux==0 ? 0 : 1); const char *zVtab = (bSwarm ? "swarmvtab" : "unionvtab"); if( sqlite3_stricmp("temp", argv[1]) ){ /* unionvtab tables may only be created in the temp schema */ *pzErr = sqlite3_mprintf("%s tables must be created in TEMP schema", zVtab); rc = SQLITE_ERROR; }else if( argc<4 || (argc>4 && bSwarm==0) ){ *pzErr = sqlite3_mprintf("wrong number of arguments for %s", zVtab); rc = SQLITE_ERROR; }else{ int nAlloc = 0; /* Allocated size of pTab->aSrc[] */ sqlite3_stmt *pStmt = 0; /* Argument statement */ char *zArg = unionStrdup(&rc, argv[3]); /* Copy of argument to CVT */ /* Prepare the SQL statement. Instead of executing it directly, sort ** the results by the "minimum rowid" field. This makes it easier to ** check that there are no rowid range overlaps between source tables ** and that the UnionTab.aSrc[] array is always sorted by rowid. */ unionDequote(zArg); pStmt = unionPreparePrintf(&rc, pzErr, db, "SELECT * FROM (%z) ORDER BY 3", zArg ); /* Allocate the UnionTab structure */ pTab = unionMalloc(&rc, sizeof(UnionTab)); if( pTab ){ assert( rc==SQLITE_OK ); pTab->db = db; pTab->bSwarm = bSwarm; pTab->nMaxOpen = SWARMVTAB_MAX_OPEN; } /* Parse other CVT arguments, if any */ if( bSwarm ){ unionConfigureVtab(&rc, pTab, pStmt, argc-4, &argv[4], pzErr); } /* Iterate through the rows returned by the SQL statement specified ** as an argument to the CREATE VIRTUAL TABLE statement. */ while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){ const char *zDb = (const char*)sqlite3_column_text(pStmt, 0); const char *zTab = (const char*)sqlite3_column_text(pStmt, 1); sqlite3_int64 iMin = sqlite3_column_int64(pStmt, 2); |
︙ | ︙ | |||
711 712 713 714 715 716 717 718 719 720 721 722 | pSrc->iMin = iMin; pSrc->iMax = iMax; if( bSwarm ){ pSrc->zFile = unionStrdup(&rc, zDb); }else{ pSrc->zDb = unionStrdup(&rc, zDb); } } } unionFinalize(&rc, pStmt, pzErr); pStmt = 0; | > > > > < < < < < < < < < | 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 | pSrc->iMin = iMin; pSrc->iMax = iMax; if( bSwarm ){ pSrc->zFile = unionStrdup(&rc, zDb); }else{ pSrc->zDb = unionStrdup(&rc, zDb); } if( pTab->bHasContext ){ const char *zContext = (const char*)sqlite3_column_text(pStmt, 4); pSrc->zContext = unionStrdup(&rc, zContext); } } } unionFinalize(&rc, pStmt, pzErr); pStmt = 0; /* It is an error if the SELECT statement returned zero rows. If only ** because there is no way to determine the schema of the virtual ** table in this case. */ if( rc==SQLITE_OK && pTab->nSrc==0 ){ *pzErr = sqlite3_mprintf("no source tables configured"); rc = SQLITE_ERROR; } /* For unionvtab, verify that all source tables exist and have ** compatible schemas. For swarmvtab, attach the first database and ** check that the first table is a rowid table only. */ if( rc==SQLITE_OK ){ if( bSwarm ){ rc = unionOpenDatabase(pTab, 0, pzErr); }else{ rc = unionSourceCheck(pTab, pzErr); } } |
︙ | ︙ |
Changes to ext/rtree/rtree.c.
︙ | ︙ | |||
2017 2018 2019 2020 2021 2022 2023 | Rtree *pRtree, /* Rtree table */ RtreeCell *pCell, /* Cell to insert into rtree */ int iHeight, /* Height of sub-tree rooted at pCell */ RtreeNode **ppLeaf /* OUT: Selected leaf page */ ){ int rc; int ii; | | | 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 | Rtree *pRtree, /* Rtree table */ RtreeCell *pCell, /* Cell to insert into rtree */ int iHeight, /* Height of sub-tree rooted at pCell */ RtreeNode **ppLeaf /* OUT: Selected leaf page */ ){ int rc; int ii; RtreeNode *pNode = 0; rc = nodeAcquire(pRtree, 1, 0, &pNode); for(ii=0; rc==SQLITE_OK && ii<(pRtree->iDepth-iHeight); ii++){ int iCell; sqlite3_int64 iBest = 0; RtreeDValue fMinGrowth = RTREE_ZERO; |
︙ | ︙ | |||
2892 2893 2894 2895 2896 2897 2898 | ** ** This is equivalent to copying the contents of the child into ** the root node (the operation that Gutman's paper says to perform ** in this scenario). */ if( rc==SQLITE_OK && pRtree->iDepth>0 && NCELL(pRoot)==1 ){ int rc2; | | | 2892 2893 2894 2895 2896 2897 2898 2899 2900 2901 2902 2903 2904 2905 2906 | ** ** This is equivalent to copying the contents of the child into ** the root node (the operation that Gutman's paper says to perform ** in this scenario). */ if( rc==SQLITE_OK && pRtree->iDepth>0 && NCELL(pRoot)==1 ){ int rc2; RtreeNode *pChild = 0; i64 iChild = nodeGetRowid(pRtree, pRoot, 0); rc = nodeAcquire(pRtree, iChild, pRoot, &pChild); if( rc==SQLITE_OK ){ rc = removeNode(pRtree, pChild, pRtree->iDepth-1); } rc2 = nodeRelease(pRtree, pChild); if( rc==SQLITE_OK ) rc = rc2; |
︙ | ︙ |
Changes to main.mk.
︙ | ︙ | |||
293 294 295 296 297 298 299 300 301 302 303 304 305 306 | 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 \ | > > | 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 | 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 \ |
︙ | ︙ | |||
687 688 689 690 691 692 693 | # Source files that go into making shell.c SHELL_SRC = \ $(TOP)/src/shell.c.in \ $(TOP)/ext/misc/shathree.c \ $(TOP)/ext/misc/fileio.c \ $(TOP)/ext/misc/completion.c \ | | > > | 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 | # Source files that go into making shell.c SHELL_SRC = \ $(TOP)/src/shell.c.in \ $(TOP)/ext/misc/shathree.c \ $(TOP)/ext/misc/fileio.c \ $(TOP)/ext/misc/completion.c \ $(TOP)/ext/misc/sqlar.c \ $(TOP)/ext/expert/sqlite3expert.c \ $(TOP)/ext/expert/sqlite3expert.h shell.c: $(SHELL_SRC) $(TOP)/tool/mkshellc.tcl tclsh $(TOP)/tool/mkshellc.tcl >shell.c # Rules to build the extension objects. |
︙ | ︙ | |||
806 807 808 809 810 811 812 813 814 815 816 817 818 819 | sqlite3_analyzer.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/spaceanal.tcl $(TOP)/tool/sqlite3_analyzer.c.in $(TOP)/tool/mkccode.tcl tclsh $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in >sqlite3_analyzer.c sqlite3_analyzer$(EXE): sqlite3_analyzer.c $(TCCX) $(TCL_FLAGS) sqlite3_analyzer.c -o $@ $(LIBTCL) $(THREADLIB) CHECKER_DEPS =\ $(TOP)/tool/mkccode.tcl \ sqlite3.c \ $(TOP)/src/tclsqlite.c \ $(TOP)/ext/repair/sqlite3_checker.tcl \ $(TOP)/ext/repair/checkindex.c \ $(TOP)/ext/repair/checkfreelist.c \ | > > > | 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 | sqlite3_analyzer.c: sqlite3.c $(TOP)/src/tclsqlite.c $(TOP)/tool/spaceanal.tcl $(TOP)/tool/sqlite3_analyzer.c.in $(TOP)/tool/mkccode.tcl tclsh $(TOP)/tool/mkccode.tcl $(TOP)/tool/sqlite3_analyzer.c.in >sqlite3_analyzer.c 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) -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION $(TOP)/ext/expert/sqlite3expert.c $(TOP)/ext/expert/expert.c sqlite3.c -o sqlite3_expert$(EXE) $(THREADLIB) CHECKER_DEPS =\ $(TOP)/tool/mkccode.tcl \ sqlite3.c \ $(TOP)/src/tclsqlite.c \ $(TOP)/ext/repair/sqlite3_checker.tcl \ $(TOP)/ext/repair/checkindex.c \ $(TOP)/ext/repair/checkfreelist.c \ |
︙ | ︙ | |||
1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 | 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.* rm -f lsm.h lsm1.c | > | 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 | 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.* rm -f lsm.h lsm1.c |
Changes to src/build.c.
︙ | ︙ | |||
1961 1962 1963 1964 1965 1966 1967 | assert(pParse->nTab==1); sqlite3MayAbort(pParse); sqlite3VdbeAddOp3(v, OP_OpenWrite, 1, pParse->regRoot, iDb); sqlite3VdbeChangeP5(v, OPFLAG_P2ISREG); pParse->nTab = 2; addrTop = sqlite3VdbeCurrentAddr(v) + 1; sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop); | < < < < < > > > > | 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 | assert(pParse->nTab==1); sqlite3MayAbort(pParse); sqlite3VdbeAddOp3(v, OP_OpenWrite, 1, pParse->regRoot, iDb); sqlite3VdbeChangeP5(v, OPFLAG_P2ISREG); pParse->nTab = 2; addrTop = sqlite3VdbeCurrentAddr(v) + 1; sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, addrTop); pSelTab = sqlite3ResultSetOfSelect(pParse, pSelect); if( pSelTab==0 ) return; assert( p->aCol==0 ); p->nCol = pSelTab->nCol; p->aCol = pSelTab->aCol; pSelTab->nCol = 0; pSelTab->aCol = 0; sqlite3DeleteTable(db, pSelTab); sqlite3SelectDestInit(&dest, SRT_Coroutine, regYield); sqlite3Select(pParse, pSelect, &dest); sqlite3VdbeEndCoroutine(v, regYield); sqlite3VdbeJumpHere(v, addrTop - 1); addrInsLoop = sqlite3VdbeAddOp1(v, OP_Yield, dest.iSDParm); VdbeCoverage(v); sqlite3VdbeAddOp3(v, OP_MakeRecord, dest.iSdst, dest.nSdst, regRec); sqlite3TableAffinity(v, p, 0); sqlite3VdbeAddOp2(v, OP_NewRowid, 1, regRowid); sqlite3VdbeAddOp3(v, OP_Insert, 1, regRec, regRowid); sqlite3VdbeGoto(v, addrInsLoop); |
︙ | ︙ |
Changes to src/loadext.c.
︙ | ︙ | |||
492 493 494 495 496 497 498 499 | zEntry = zProc ? zProc : "sqlite3_extension_init"; handle = sqlite3OsDlOpen(pVfs, zFile); #if SQLITE_OS_UNIX || SQLITE_OS_WIN for(ii=0; ii<ArraySize(azEndings) && handle==0; ii++){ char *zAltFile = sqlite3_mprintf("%s.%s", zFile, azEndings[ii]); if( zAltFile==0 ) return SQLITE_NOMEM_BKPT; | > > | | 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 | zEntry = zProc ? zProc : "sqlite3_extension_init"; handle = sqlite3OsDlOpen(pVfs, zFile); #if SQLITE_OS_UNIX || SQLITE_OS_WIN for(ii=0; ii<ArraySize(azEndings) && handle==0; ii++){ char *zAltFile = sqlite3_mprintf("%s.%s", zFile, azEndings[ii]); int bExists = 0; if( zAltFile==0 ) return SQLITE_NOMEM_BKPT; sqlite3OsAccess(pVfs, zAltFile, SQLITE_ACCESS_EXISTS, &bExists); if( bExists ) handle = sqlite3OsDlOpen(pVfs, zAltFile); sqlite3_free(zAltFile); } #endif if( handle==0 ){ if( pzErrMsg ){ *pzErrMsg = zErrmsg = sqlite3_malloc64(nMsg); if( zErrmsg ){ |
︙ | ︙ |
Changes to src/main.c.
︙ | ︙ | |||
813 814 815 816 817 818 819 820 821 822 823 824 825 826 | } aFlagOp[] = { { SQLITE_DBCONFIG_ENABLE_FKEY, SQLITE_ForeignKeys }, { SQLITE_DBCONFIG_ENABLE_TRIGGER, SQLITE_EnableTrigger }, { SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, SQLITE_Fts3Tokenizer }, { SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, SQLITE_LoadExtension }, { SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE, SQLITE_NoCkptOnClose }, { SQLITE_DBCONFIG_ENABLE_QPSG, SQLITE_EnableQPSG }, }; unsigned int i; rc = SQLITE_ERROR; /* IMP: R-42790-23372 */ for(i=0; i<ArraySize(aFlagOp); i++){ if( aFlagOp[i].op==op ){ int onoff = va_arg(ap, int); int *pRes = va_arg(ap, int*); | > | 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 | } aFlagOp[] = { { SQLITE_DBCONFIG_ENABLE_FKEY, SQLITE_ForeignKeys }, { SQLITE_DBCONFIG_ENABLE_TRIGGER, SQLITE_EnableTrigger }, { SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, SQLITE_Fts3Tokenizer }, { SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, SQLITE_LoadExtension }, { SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE, SQLITE_NoCkptOnClose }, { SQLITE_DBCONFIG_ENABLE_QPSG, SQLITE_EnableQPSG }, { SQLITE_DBCONFIG_TRIGGER_EQP, SQLITE_TriggerEQP }, }; unsigned int i; rc = SQLITE_ERROR; /* IMP: R-42790-23372 */ for(i=0; i<ArraySize(aFlagOp); i++){ if( aFlagOp[i].op==op ){ int onoff = va_arg(ap, int); int *pRes = va_arg(ap, int*); |
︙ | ︙ |
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/select.c.
︙ | ︙ | |||
1377 1378 1379 1380 1381 1382 1383 1384 | char const *zOrigDb = 0; char const *zOrigTab = 0; char const *zOrigCol = 0; #endif assert( pExpr!=0 ); assert( pNC->pSrcList!=0 ); switch( pExpr->op ){ | > > < < < | 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 | char const *zOrigDb = 0; char const *zOrigTab = 0; char const *zOrigCol = 0; #endif assert( pExpr!=0 ); assert( pNC->pSrcList!=0 ); assert( pExpr->op!=TK_AGG_COLUMN ); /* This routine runes before aggregates ** are processed */ switch( pExpr->op ){ case TK_COLUMN: { /* The expression is a column. Locate the table the column is being ** extracted from in NameContext.pSrcList. This table may be real ** database table or a subquery. */ Table *pTab = 0; /* Table structure column is extracted from */ Select *pS = 0; /* Select the column is extracted from */ int iCol = pExpr->iColumn; /* Index of column in pTab */ while( pNC && !pTab ){ SrcList *pTabList = pNC->pSrcList; for(j=0;j<pTabList->nSrc && pTabList->a[j].iCursor!=pExpr->iTable;j++); if( j<pTabList->nSrc ){ pTab = pTabList->a[j].pTab; pS = pTabList->a[j].pSelect; }else{ |
︙ | ︙ | |||
1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 | return; } #endif if( pParse->colNamesSet || db->mallocFailed ) return; /* Column names are determined by the left-most term of a compound select */ while( pSelect->pPrior ) pSelect = pSelect->pPrior; pTabList = pSelect->pSrc; pEList = pSelect->pEList; assert( v!=0 ); assert( pTabList!=0 ); pParse->colNamesSet = 1; fullName = (db->flags & SQLITE_FullColNames)!=0; srcName = (db->flags & SQLITE_ShortColNames)!=0 || fullName; | > | 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 | return; } #endif if( pParse->colNamesSet || db->mallocFailed ) return; /* Column names are determined by the left-most term of a compound select */ while( pSelect->pPrior ) pSelect = pSelect->pPrior; SELECTTRACE(1,pParse,pSelect,("generating column names\n")); pTabList = pSelect->pSrc; pEList = pSelect->pEList; assert( v!=0 ); assert( pTabList!=0 ); pParse->colNamesSet = 1; fullName = (db->flags & SQLITE_FullColNames)!=0; srcName = (db->flags & SQLITE_ShortColNames)!=0 || fullName; |
︙ | ︙ | |||
1700 1701 1702 1703 1704 1705 1706 | /* If the column contains an "AS <name>" phrase, use <name> as the name */ }else{ Expr *pColExpr = sqlite3ExprSkipCollate(pEList->a[i].pExpr); while( pColExpr->op==TK_DOT ){ pColExpr = pColExpr->pRight; assert( pColExpr!=0 ); } | | | < > | 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 | /* If the column contains an "AS <name>" phrase, use <name> as the name */ }else{ Expr *pColExpr = sqlite3ExprSkipCollate(pEList->a[i].pExpr); while( pColExpr->op==TK_DOT ){ pColExpr = pColExpr->pRight; assert( pColExpr!=0 ); } assert( pColExpr->op!=TK_AGG_COLUMN ); if( pColExpr->op==TK_COLUMN ){ /* For columns use the column name name */ int iCol = pColExpr->iColumn; Table *pTab = pColExpr->pTab; assert( pTab!=0 ); if( iCol<0 ) iCol = pTab->iPKey; zName = iCol>=0 ? pTab->aCol[iCol].zName : "rowid"; }else if( pColExpr->op==TK_ID ){ assert( !ExprHasProperty(pColExpr, EP_IntValue) ); zName = pColExpr->u.zToken; }else{ /* Use the original text of the column expression as its name */ |
︙ | ︙ |
Changes to src/shell.c.in.
︙ | ︙ | |||
795 796 797 798 799 800 801 802 803 804 805 806 807 808 | INCLUDE ../ext/misc/shathree.c INCLUDE ../ext/misc/fileio.c INCLUDE ../ext/misc/completion.c #ifdef SQLITE_HAVE_ZLIB INCLUDE ../ext/misc/sqlar.c #endif #if defined(SQLITE_ENABLE_SESSION) /* ** State information for a single open session */ typedef struct OpenSession OpenSession; struct OpenSession { | > > | 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 | INCLUDE ../ext/misc/shathree.c INCLUDE ../ext/misc/fileio.c INCLUDE ../ext/misc/completion.c #ifdef SQLITE_HAVE_ZLIB INCLUDE ../ext/misc/sqlar.c #endif INCLUDE ../ext/expert/sqlite3expert.h INCLUDE ../ext/expert/sqlite3expert.c #if defined(SQLITE_ENABLE_SESSION) /* ** State information for a single open session */ typedef struct OpenSession OpenSession; struct OpenSession { |
︙ | ︙ | |||
820 821 822 823 824 825 826 827 828 829 830 831 832 833 | typedef struct SavedModeInfo SavedModeInfo; struct SavedModeInfo { int valid; /* Is there legit data in here? */ int mode; /* Mode prior to ".explain on" */ int showHeader; /* The ".header" setting prior to ".explain on" */ int colWidth[100]; /* Column widths prior to ".explain on" */ }; /* ** State information about the database connection is contained in an ** instance of the following structure. */ typedef struct ShellState ShellState; struct ShellState { | > > > > > > | 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 | typedef struct SavedModeInfo SavedModeInfo; struct SavedModeInfo { int valid; /* Is there legit data in here? */ int mode; /* Mode prior to ".explain on" */ int showHeader; /* The ".header" setting prior to ".explain on" */ int colWidth[100]; /* Column widths prior to ".explain on" */ }; typedef struct ExpertInfo ExpertInfo; struct ExpertInfo { sqlite3expert *pExpert; int bVerbose; }; /* ** State information about the database connection is contained in an ** instance of the following structure. */ typedef struct ShellState ShellState; struct ShellState { |
︙ | ︙ | |||
865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 | int *aiIndent; /* Array of indents used in MODE_Explain */ int nIndent; /* Size of array aiIndent[] */ int iIndent; /* Index of current op in aiIndent[] */ #if defined(SQLITE_ENABLE_SESSION) int nSession; /* Number of active sessions */ OpenSession aSession[4]; /* Array of sessions. [0] is in focus. */ #endif }; /* ** These are the allowed shellFlgs values */ #define SHFLG_Pagecache 0x00000001 /* The --pagecache option is used */ #define SHFLG_Lookaside 0x00000002 /* Lookaside memory is used */ #define SHFLG_Backslash 0x00000004 /* The --backslash option is used */ | > > > > > > > > | 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 | int *aiIndent; /* Array of indents used in MODE_Explain */ int nIndent; /* Size of array aiIndent[] */ int iIndent; /* Index of current op in aiIndent[] */ #if defined(SQLITE_ENABLE_SESSION) int nSession; /* Number of active sessions */ OpenSession aSession[4]; /* Array of sessions. [0] is in focus. */ #endif ExpertInfo expert; /* Valid if previous command was ".expert OPT..." */ }; /* Allowed values for ShellState.autoEQP */ #define AUTOEQP_off 0 #define AUTOEQP_on 1 #define AUTOEQP_trigger 2 #define AUTOEQP_full 3 /* ** These are the allowed shellFlgs values */ #define SHFLG_Pagecache 0x00000001 /* The --pagecache option is used */ #define SHFLG_Lookaside 0x00000002 /* Lookaside memory is used */ #define SHFLG_Backslash 0x00000004 /* The --backslash option is used */ |
︙ | ︙ | |||
2247 2248 2249 2250 2251 2252 2253 2254 2255 2256 2257 2258 2259 2260 | }else{ do{ rc = sqlite3_step(pStmt); } while( rc == SQLITE_ROW ); } } } /* ** Execute a statement or set of statements. Print ** any result rows/columns depending on the current mode ** set via the supplied callback. ** ** This is very similar to SQLite's built-in sqlite3_exec() | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 2263 2264 2265 2266 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 2315 2316 2317 2318 2319 2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 | }else{ do{ rc = sqlite3_step(pStmt); } while( rc == SQLITE_ROW ); } } } /* ** This function is called to process SQL if the previous shell command ** was ".expert". It passes the SQL in the second argument directly to ** the sqlite3expert object. ** ** If successful, SQLITE_OK is returned. Otherwise, an SQLite error ** code. In this case, (*pzErr) may be set to point to a buffer containing ** an English language error message. It is the responsibility of the ** caller to eventually free this buffer using sqlite3_free(). */ static int expertHandleSQL( ShellState *pState, const char *zSql, char **pzErr ){ assert( pState->expert.pExpert ); assert( pzErr==0 || *pzErr==0 ); return sqlite3_expert_sql(pState->expert.pExpert, zSql, pzErr); } /* ** This function is called either to silently clean up the object ** created by the ".expert" command (if bCancel==1), or to generate a ** report from it and then clean it up (if bCancel==0). ** ** If successful, SQLITE_OK is returned. Otherwise, an SQLite error ** code. In this case, (*pzErr) may be set to point to a buffer containing ** an English language error message. It is the responsibility of the ** caller to eventually free this buffer using sqlite3_free(). */ static int expertFinish( ShellState *pState, int bCancel, char **pzErr ){ int rc = SQLITE_OK; sqlite3expert *p = pState->expert.pExpert; assert( p ); assert( bCancel || pzErr==0 || *pzErr==0 ); if( bCancel==0 ){ FILE *out = pState->out; int bVerbose = pState->expert.bVerbose; rc = sqlite3_expert_analyze(p, pzErr); if( rc==SQLITE_OK ){ int nQuery = sqlite3_expert_count(p); int i; if( bVerbose ){ const char *zCand = sqlite3_expert_report(p,0,EXPERT_REPORT_CANDIDATES); raw_printf(out, "-- Candidates -----------------------------\n"); raw_printf(out, "%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( bVerbose ){ raw_printf(out, "-- Query %d --------------------------------\n",i+1); raw_printf(out, "%s\n\n", zSql); } raw_printf(out, "%s\n", zIdx); raw_printf(out, "%s\n", zEQP); } } } sqlite3_expert_destroy(p); pState->expert.pExpert = 0; return rc; } /* ** Execute a statement or set of statements. Print ** any result rows/columns depending on the current mode ** set via the supplied callback. ** ** This is very similar to SQLite's built-in sqlite3_exec() |
︙ | ︙ | |||
2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 | int rc = SQLITE_OK; /* Return Code */ int rc2; const char *zLeftover; /* Tail of unprocessed SQL */ if( pzErrMsg ){ *pzErrMsg = NULL; } while( zSql[0] && (SQLITE_OK == rc) ){ static const char *zStmtSql; rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover); if( SQLITE_OK != rc ){ if( pzErrMsg ){ *pzErrMsg = save_err_msg(db); | > > > > > | 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 | int rc = SQLITE_OK; /* Return Code */ int rc2; const char *zLeftover; /* Tail of unprocessed SQL */ if( pzErrMsg ){ *pzErrMsg = NULL; } if( pArg->expert.pExpert ){ rc = expertHandleSQL(pArg, zSql, pzErrMsg); return expertFinish(pArg, (rc!=SQLITE_OK), pzErrMsg); } while( zSql[0] && (SQLITE_OK == rc) ){ static const char *zStmtSql; rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover); if( SQLITE_OK != rc ){ if( pzErrMsg ){ *pzErrMsg = save_err_msg(db); |
︙ | ︙ | |||
2307 2308 2309 2310 2311 2312 2313 2314 2315 2316 2317 2318 2319 2320 2321 2322 2323 2324 2325 2326 | utf8_printf(pArg->out, "%s\n", zStmtSql ? zStmtSql : zSql); } /* Show the EXPLAIN QUERY PLAN if .eqp is on */ if( pArg && pArg->autoEQP && sqlite3_strlike("EXPLAIN%",zStmtSql,0)!=0 ){ sqlite3_stmt *pExplain; char *zEQP; disable_debug_trace_modes(); zEQP = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zStmtSql); rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0); if( rc==SQLITE_OK ){ while( sqlite3_step(pExplain)==SQLITE_ROW ){ raw_printf(pArg->out,"--EQP-- %d,",sqlite3_column_int(pExplain, 0)); raw_printf(pArg->out,"%d,", sqlite3_column_int(pExplain, 1)); raw_printf(pArg->out,"%d,", sqlite3_column_int(pExplain, 2)); utf8_printf(pArg->out,"%s\n", sqlite3_column_text(pExplain, 3)); } } sqlite3_finalize(pExplain); sqlite3_free(zEQP); | > > > > > | > | 2401 2402 2403 2404 2405 2406 2407 2408 2409 2410 2411 2412 2413 2414 2415 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446 | utf8_printf(pArg->out, "%s\n", zStmtSql ? zStmtSql : zSql); } /* Show the EXPLAIN QUERY PLAN if .eqp is on */ if( pArg && pArg->autoEQP && sqlite3_strlike("EXPLAIN%",zStmtSql,0)!=0 ){ sqlite3_stmt *pExplain; char *zEQP; int triggerEQP = 0; disable_debug_trace_modes(); sqlite3_db_config(db, SQLITE_DBCONFIG_TRIGGER_EQP, -1, &triggerEQP); if( pArg->autoEQP>=AUTOEQP_trigger ){ sqlite3_db_config(db, SQLITE_DBCONFIG_TRIGGER_EQP, 1, 0); } zEQP = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zStmtSql); rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0); if( rc==SQLITE_OK ){ while( sqlite3_step(pExplain)==SQLITE_ROW ){ raw_printf(pArg->out,"--EQP-- %d,",sqlite3_column_int(pExplain, 0)); raw_printf(pArg->out,"%d,", sqlite3_column_int(pExplain, 1)); raw_printf(pArg->out,"%d,", sqlite3_column_int(pExplain, 2)); utf8_printf(pArg->out,"%s\n", sqlite3_column_text(pExplain, 3)); } } sqlite3_finalize(pExplain); sqlite3_free(zEQP); if( pArg->autoEQP>=AUTOEQP_full ){ /* Also do an EXPLAIN for ".eqp full" mode */ zEQP = sqlite3_mprintf("EXPLAIN %s", zStmtSql); rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0); if( rc==SQLITE_OK ){ pArg->cMode = MODE_Explain; explain_data_prepare(pArg, pExplain); exec_prepared_stmt(pArg, pExplain, xCallback); explain_data_delete(pArg); } sqlite3_finalize(pExplain); sqlite3_free(zEQP); } sqlite3_db_config(db, SQLITE_DBCONFIG_TRIGGER_EQP, triggerEQP, 0); restore_debug_trace_modes(); } if( pArg ){ pArg->cMode = pArg->mode; if( pArg->autoExplain && sqlite3_column_count(pStmt)==8 |
︙ | ︙ | |||
2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 2701 2702 2703 2704 | ".dbinfo ?DB? Show status information about the database\n" ".dump ?TABLE? ... Dump the database in an SQL text format\n" " If TABLE specified, only dump tables matching\n" " LIKE pattern TABLE.\n" ".echo on|off Turn command echo on or off\n" ".eqp on|off|full Enable or disable automatic EXPLAIN QUERY PLAN\n" ".exit Exit this program\n" /* Because explain mode comes on automatically now, the ".explain" mode ** is removed from the help screen. It is still supported for legacy, however */ /*".explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic\n"*/ ".fullschema ?--indent? Show schema and the content of sqlite_stat tables\n" ".headers on|off Turn display of headers on or off\n" ".help Show this message\n" ".import FILE TABLE Import data from FILE into TABLE\n" | > | 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 | ".dbinfo ?DB? Show status information about the database\n" ".dump ?TABLE? ... Dump the database in an SQL text format\n" " If TABLE specified, only dump tables matching\n" " LIKE pattern TABLE.\n" ".echo on|off Turn command echo on or off\n" ".eqp on|off|full Enable or disable automatic EXPLAIN QUERY PLAN\n" ".exit Exit this program\n" ".expert EXPERIMENTAL. Suggest indexes for specified queries\n" /* Because explain mode comes on automatically now, the ".explain" mode ** is removed from the help screen. It is still supported for legacy, however */ /*".explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic\n"*/ ".fullschema ?--indent? Show schema and the content of sqlite_stat tables\n" ".headers on|off Turn display of headers on or off\n" ".help Show this message\n" ".import FILE TABLE Import data from FILE into TABLE\n" |
︙ | ︙ | |||
4717 4718 4719 4720 4721 4722 4723 4724 4725 4726 4727 4728 4729 4730 4731 4732 4733 4734 4735 4736 4737 4738 4739 4740 4741 4742 4743 | sqlite3_close(db); } } return rc; } /* ** If an input line begins with "." then invoke this routine to ** process that line. ** ** Return 1 on error, 2 to exit, and 0 otherwise. */ static int do_meta_command(char *zLine, ShellState *p){ int h = 1; int nArg = 0; int n, c; int rc = 0; char *azArg[50]; /* Parse the input line into tokens. */ while( zLine[h] && nArg<ArraySize(azArg) ){ while( IsSpace(zLine[h]) ){ h++; } if( zLine[h]==0 ) break; if( zLine[h]=='\'' || zLine[h]=='"' ){ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 4818 4819 4820 4821 4822 4823 4824 4825 4826 4827 4828 4829 4830 4831 4832 4833 4834 4835 4836 4837 4838 4839 4840 4841 4842 4843 4844 4845 4846 4847 4848 4849 4850 4851 4852 4853 4854 4855 4856 4857 4858 4859 4860 4861 4862 4863 4864 4865 4866 4867 4868 4869 4870 4871 4872 4873 4874 4875 4876 4877 4878 4879 4880 4881 4882 4883 4884 4885 4886 4887 4888 4889 4890 4891 4892 4893 4894 4895 4896 4897 4898 4899 4900 4901 4902 4903 4904 4905 | sqlite3_close(db); } } return rc; } /* ** Implementation of ".expert" dot command. */ static int expertDotCommand( ShellState *pState, /* Current shell tool state */ char **azArg, /* Array of arguments passed to dot command */ int nArg /* Number of entries in azArg[] */ ){ int rc = SQLITE_OK; char *zErr = 0; int i; int iSample = 0; assert( pState->expert.pExpert==0 ); memset(&pState->expert, 0, sizeof(ExpertInfo)); for(i=1; rc==SQLITE_OK && i<nArg; i++){ char *z = azArg[i]; int n; if( z[0]=='-' && z[1]=='-' ) z++; n = strlen(z); if( n>=2 && 0==strncmp(z, "-verbose", n) ){ pState->expert.bVerbose = 1; } else if( n>=2 && 0==strncmp(z, "-sample", n) ){ if( i==(nArg-1) ){ raw_printf(stderr, "option requires an argument: %s\n", z); rc = SQLITE_ERROR; }else{ iSample = (int)integerValue(azArg[++i]); if( iSample<0 || iSample>100 ){ raw_printf(stderr, "value out of range: %s\n", azArg[i]); rc = SQLITE_ERROR; } } } else{ raw_printf(stderr, "unknown option: %s\n", z); rc = SQLITE_ERROR; } } if( rc==SQLITE_OK ){ pState->expert.pExpert = sqlite3_expert_new(pState->db, &zErr); if( pState->expert.pExpert==0 ){ raw_printf(stderr, "sqlite3_expert_new: %s\n", zErr); rc = SQLITE_ERROR; }else{ sqlite3_expert_config( pState->expert.pExpert, EXPERT_CONFIG_SAMPLE, iSample ); } } return rc; } /* ** If an input line begins with "." then invoke this routine to ** process that line. ** ** Return 1 on error, 2 to exit, and 0 otherwise. */ static int do_meta_command(char *zLine, ShellState *p){ int h = 1; int nArg = 0; int n, c; int rc = 0; char *azArg[50]; if( p->expert.pExpert ){ expertFinish(p, 1, 0); } /* Parse the input line into tokens. */ while( zLine[h] && nArg<ArraySize(azArg) ){ while( IsSpace(zLine[h]) ){ h++; } if( zLine[h]==0 ) break; if( zLine[h]=='\'' || zLine[h]=='"' ){ |
︙ | ︙ | |||
5053 5054 5055 5056 5057 5058 5059 | rc = 1; } }else if( c=='e' && strncmp(azArg[0], "eqp", n)==0 ){ if( nArg==2 ){ if( strcmp(azArg[1],"full")==0 ){ | | > > | | 5215 5216 5217 5218 5219 5220 5221 5222 5223 5224 5225 5226 5227 5228 5229 5230 5231 5232 5233 5234 5235 5236 | rc = 1; } }else if( c=='e' && strncmp(azArg[0], "eqp", n)==0 ){ if( nArg==2 ){ if( strcmp(azArg[1],"full")==0 ){ p->autoEQP = AUTOEQP_full; }else if( strcmp(azArg[1],"trigger")==0 ){ p->autoEQP = AUTOEQP_trigger; }else{ p->autoEQP = booleanValue(azArg[1]); } }else{ raw_printf(stderr, "Usage: .eqp off|on|trigger|full\n"); rc = 1; } }else if( c=='e' && strncmp(azArg[0], "exit", n)==0 ){ if( nArg>1 && (rc = (int)integerValue(azArg[1]))!=0 ) exit(rc); rc = 2; |
︙ | ︙ | |||
5091 5092 5093 5094 5095 5096 5097 5098 5099 5100 5101 5102 5103 5104 | if( p->mode==MODE_Explain ) p->mode = p->normalMode; p->autoExplain = 0; }else if( val==99 ){ if( p->mode==MODE_Explain ) p->mode = p->normalMode; p->autoExplain = 1; } }else if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){ ShellState data; char *zErrMsg = 0; int doStats = 0; memcpy(&data, p, sizeof(data)); data.showHeader = 0; | > > > > > | 5255 5256 5257 5258 5259 5260 5261 5262 5263 5264 5265 5266 5267 5268 5269 5270 5271 5272 5273 | if( p->mode==MODE_Explain ) p->mode = p->normalMode; p->autoExplain = 0; }else if( val==99 ){ if( p->mode==MODE_Explain ) p->mode = p->normalMode; p->autoExplain = 1; } }else if( c=='e' && strncmp(azArg[0], "expert", n)==0 ){ open_db(p, 0); expertDotCommand(p, azArg, nArg); }else if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){ ShellState data; char *zErrMsg = 0; int doStats = 0; memcpy(&data, p, sizeof(data)); data.showHeader = 0; |
︙ | ︙ | |||
6406 6407 6408 6409 6410 6411 6412 | } x = system(zCmd); sqlite3_free(zCmd); if( x ) raw_printf(stderr, "System command returns %d\n", x); }else if( c=='s' && strncmp(azArg[0], "show", n)==0 ){ | | | 6575 6576 6577 6578 6579 6580 6581 6582 6583 6584 6585 6586 6587 6588 6589 | } x = system(zCmd); sqlite3_free(zCmd); if( x ) raw_printf(stderr, "System command returns %d\n", x); }else if( c=='s' && strncmp(azArg[0], "show", n)==0 ){ static const char *azBool[] = { "off", "on", "trigger", "full"}; int i; if( nArg!=1 ){ raw_printf(stderr, "Usage: .show\n"); rc = 1; goto meta_command_exit; } utf8_printf(p->out, "%12.12s: %s\n","echo", |
︙ | ︙ | |||
7558 7559 7560 7561 7562 7563 7564 | }else if( strcmp(z,"-header")==0 ){ data.showHeader = 1; }else if( strcmp(z,"-noheader")==0 ){ data.showHeader = 0; }else if( strcmp(z,"-echo")==0 ){ ShellSetFlag(&data, SHFLG_Echo); }else if( strcmp(z,"-eqp")==0 ){ | | | | 7727 7728 7729 7730 7731 7732 7733 7734 7735 7736 7737 7738 7739 7740 7741 7742 7743 | }else if( strcmp(z,"-header")==0 ){ data.showHeader = 1; }else if( strcmp(z,"-noheader")==0 ){ data.showHeader = 0; }else if( strcmp(z,"-echo")==0 ){ ShellSetFlag(&data, SHFLG_Echo); }else if( strcmp(z,"-eqp")==0 ){ data.autoEQP = AUTOEQP_on; }else if( strcmp(z,"-eqpfull")==0 ){ data.autoEQP = AUTOEQP_full; }else if( strcmp(z,"-stats")==0 ){ data.statsOn = 1; }else if( strcmp(z,"-scanstats")==0 ){ data.scanstatsOn = 1; }else if( strcmp(z,"-backslash")==0 ){ /* Undocumented command-line option: -backslash ** Causes C-style backslash escapes to be evaluated in SQL statements |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
2056 2057 2058 2059 2060 2061 2062 | ** 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> | < > > > > > > > > > | | > | 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 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 | ** 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_TRIGGER_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_TRIGGER_EQP 1008 /* int int* */ #define SQLITE_DBCONFIG_MAX 1008 /* Largest DBCONFIG */ /* ** 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 |
︙ | ︙ | |||
7029 7030 7031 7032 7033 7034 7035 | #define SQLITE_TESTCTRL_ONCE_RESET_THRESHOLD 19 #define SQLITE_TESTCTRL_NEVER_CORRUPT 20 #define SQLITE_TESTCTRL_VDBE_COVERAGE 21 #define SQLITE_TESTCTRL_BYTEORDER 22 #define SQLITE_TESTCTRL_ISINIT 23 #define SQLITE_TESTCTRL_SORTER_MMAP 24 #define SQLITE_TESTCTRL_IMPOSTER 25 | | | 7038 7039 7040 7041 7042 7043 7044 7045 7046 7047 7048 7049 7050 7051 7052 | #define SQLITE_TESTCTRL_ONCE_RESET_THRESHOLD 19 #define SQLITE_TESTCTRL_NEVER_CORRUPT 20 #define SQLITE_TESTCTRL_VDBE_COVERAGE 21 #define SQLITE_TESTCTRL_BYTEORDER 22 #define SQLITE_TESTCTRL_ISINIT 23 #define SQLITE_TESTCTRL_SORTER_MMAP 24 #define SQLITE_TESTCTRL_IMPOSTER 25 #define SQLITE_TESTCTRL_LAST 25 /* Largest TESTCTRL */ /* ** CAPI3REF: SQLite Runtime Status ** ** ^These interfaces are used to retrieve runtime status information ** about the performance of SQLite, and optionally to reset various ** highwater marks. ^The first argument is an integer code for |
︙ | ︙ | |||
8283 8284 8285 8286 8287 8288 8289 8290 8291 8292 8293 8294 8295 8296 | ** 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. | > > > > > > > > > > > > > > > | 8292 8293 8294 8295 8296 8297 8298 8299 8300 8301 8302 8303 8304 8305 8306 8307 8308 8309 8310 8311 8312 8313 8314 8315 8316 8317 8318 8319 8320 | ** 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 sqlite3_index_info object that is the ** first parameter to the xBestIndex() method. 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_index_info*,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.
︙ | ︙ | |||
1416 1417 1418 1419 1420 1421 1422 | unsigned nProgressOps; /* Number of opcodes for progress callback */ #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 */ | | | 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 | unsigned nProgressOps; /* Number of opcodes for progress callback */ #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 */ |
︙ | ︙ | |||
1491 1492 1493 1494 1495 1496 1497 | #define SQLITE_LoadExtension 0x00010000 /* Enable load_extension */ #define SQLITE_LoadExtFunc 0x00020000 /* Enable load_extension() SQL func */ #define SQLITE_EnableTrigger 0x00040000 /* True to enable triggers */ #define SQLITE_DeferFKs 0x00080000 /* Defer all FK constraints */ #define SQLITE_QueryOnly 0x00100000 /* Disable database changes */ #define SQLITE_CellSizeCk 0x00200000 /* Check btree cell sizes on load */ #define SQLITE_Fts3Tokenizer 0x00400000 /* Enable fts3_tokenizer(2) */ | | > > | 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 | #define SQLITE_LoadExtension 0x00010000 /* Enable load_extension */ #define SQLITE_LoadExtFunc 0x00020000 /* Enable load_extension() SQL func */ #define SQLITE_EnableTrigger 0x00040000 /* True to enable triggers */ #define SQLITE_DeferFKs 0x00080000 /* Defer all FK constraints */ #define SQLITE_QueryOnly 0x00100000 /* Disable database changes */ #define SQLITE_CellSizeCk 0x00200000 /* Check btree cell sizes on load */ #define SQLITE_Fts3Tokenizer 0x00400000 /* Enable fts3_tokenizer(2) */ #define SQLITE_EnableQPSG 0x00800000 /* Query Planner Stability Guarantee*/ #define SQLITE_TriggerEQP 0x01000000 /* Show trigger EXPLAIN QUERY PLAN */ /* Flags used only if debugging */ #ifdef SQLITE_DEBUG #define SQLITE_SqlTrace 0x08000000 /* Debug print SQL as it executes */ #define SQLITE_VdbeListing 0x10000000 /* Debug listings of VDBE programs */ #define SQLITE_VdbeTrace 0x20000000 /* True to trace VDBE execution */ #define SQLITE_VdbeAddopTrace 0x40000000 /* Trace sqlite3VdbeAddOp() calls */ #define SQLITE_VdbeEQP 0x80000000 /* Debug EXPLAIN QUERY PLAN */ |
︙ | ︙ |
Changes to src/test_tclsh.c.
︙ | ︙ | |||
100 101 102 103 104 105 106 107 108 109 110 111 112 113 | extern int Sqlitetesttcl_Init(Tcl_Interp*); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) extern int Sqlitetestfts3_Init(Tcl_Interp *interp); #endif #ifdef SQLITE_ENABLE_ZIPVFS extern int Zipvfs_Init(Tcl_Interp*); #endif Tcl_CmdInfo cmdInfo; /* Since the primary use case for this binary is testing of SQLite, ** be sure to generate core files if we crash */ #if defined(unix) { struct rlimit x; getrlimit(RLIMIT_CORE, &x); | > > | 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 | extern int Sqlitetesttcl_Init(Tcl_Interp*); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) extern int Sqlitetestfts3_Init(Tcl_Interp *interp); #endif #ifdef SQLITE_ENABLE_ZIPVFS extern int Zipvfs_Init(Tcl_Interp*); #endif extern int TestExpert_Init(Tcl_Interp*); Tcl_CmdInfo cmdInfo; /* Since the primary use case for this binary is testing of SQLite, ** be sure to generate core files if we crash */ #if defined(unix) { struct rlimit x; getrlimit(RLIMIT_CORE, &x); |
︙ | ︙ | |||
162 163 164 165 166 167 168 169 170 171 172 173 174 175 | Fts5tcl_Init(interp); SqliteRbu_Init(interp); Sqlitetesttcl_Init(interp); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) Sqlitetestfts3_Init(interp); #endif Tcl_CreateObjCommand( interp, "load_testfixture_extensions", load_testfixture_extensions,0,0 ); return 0; } | > | 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 | Fts5tcl_Init(interp); SqliteRbu_Init(interp); Sqlitetesttcl_Init(interp); #if defined(SQLITE_ENABLE_FTS3) || defined(SQLITE_ENABLE_FTS4) Sqlitetestfts3_Init(interp); #endif TestExpert_Init(interp); Tcl_CreateObjCommand( interp, "load_testfixture_extensions", load_testfixture_extensions,0,0 ); return 0; } |
︙ | ︙ |
Changes to src/treeview.c.
︙ | ︙ | |||
503 504 505 506 507 508 509 | if( pList==0 ){ sqlite3TreeViewLine(pView, "%s (empty)", zLabel); }else{ int i; sqlite3TreeViewLine(pView, "%s", zLabel); for(i=0; i<pList->nExpr; i++){ int j = pList->a[i].u.x.iOrderByCol; | > | > > > > > > | > | 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 | if( pList==0 ){ sqlite3TreeViewLine(pView, "%s (empty)", zLabel); }else{ int i; sqlite3TreeViewLine(pView, "%s", zLabel); for(i=0; i<pList->nExpr; i++){ int j = pList->a[i].u.x.iOrderByCol; char *zName = pList->a[i].zName; if( j || zName ){ sqlite3TreeViewPush(pView, 0); } if( zName ){ sqlite3TreeViewLine(pView, "AS %s", zName); } if( j ){ sqlite3TreeViewLine(pView, "iOrderByCol=%d", j); } sqlite3TreeViewExpr(pView, pList->a[i].pExpr, i<pList->nExpr-1); if( j || zName ){ sqlite3TreeViewPop(pView); } } } } void sqlite3TreeViewExprList( TreeView *pView, const ExprList *pList, u8 moreToFollow, |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 | 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 ** sqlite3_column_text16(), causing a translation to UTF-16 encoding. */ releaseMemArray(pMem, 8); p->pResultSet = 0; | > > | | < | | | < < < < | < < > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | | | | | | | | | | < < < < < < < < < < < < < < < < < < < | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > | 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 | 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 bListSubprogs = (p->explain==1 || (db->flags & SQLITE_TriggerEQP)!=0); 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 ** sqlite3_column_text16(), causing a translation to UTF-16 encoding. */ releaseMemArray(pMem, 8); p->pResultSet = 0; if( p->rc==SQLITE_NOMEM ){ /* This happens if a malloc() inside a call to sqlite3_column_text() or ** sqlite3_column_text16() failed. */ sqlite3OomFault(db); return SQLITE_ERROR; } /* When the number of output rows reaches nRow, that means the ** 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( bListSubprogs ){ /* 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( bListSubprogs && 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 ){ p->rc = sqlite3VdbeMemGrow(pSub, nByte, nSub!=0); if( p->rc!=SQLITE_OK ){ rc = SQLITE_ERROR; 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.
︙ | ︙ | |||
14 15 16 17 18 19 20 21 22 23 24 25 26 27 | ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". */ #include "sqliteInt.h" #include "whereInt.h" /* Forward declaration of methods */ static int whereLoopResize(sqlite3*, WhereLoop*, int); /* Test variable that can be set to enable WHERE tracing */ #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG) /***/ int sqlite3WhereTrace = 0; | > > > > > > > > > > > > > > > | 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 | ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". */ #include "sqliteInt.h" #include "whereInt.h" /* ** Extra information appended to the end of sqlite3_index_info but not ** visible to the xBestIndex function, at least not directly. The ** sqlite3_vtab_collation() interface knows how to reach it, however. ** ** This object is not an API and can be changed from one release to the ** next. As long as allocateIndexInfo() and sqlite3_vtab_collation() ** agree on the structure, all will be well. */ typedef struct HiddenIndexInfo HiddenIndexInfo; struct HiddenIndexInfo { WhereClause *pWC; /* The Where clause being analyzed */ Parse *pParse; /* The parsing context */ }; /* Forward declaration of methods */ static int whereLoopResize(sqlite3*, WhereLoop*, int); /* Test variable that can be set to enable WHERE tracing */ #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG) /***/ int sqlite3WhereTrace = 0; |
︙ | ︙ | |||
837 838 839 840 841 842 843 | #ifndef SQLITE_OMIT_VIRTUALTABLE /* ** Allocate and populate an sqlite3_index_info structure. It is the ** responsibility of the caller to eventually release the structure ** by passing the pointer returned by this function to sqlite3_free(). */ static sqlite3_index_info *allocateIndexInfo( | | | | | > | 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 | #ifndef SQLITE_OMIT_VIRTUALTABLE /* ** Allocate and populate an sqlite3_index_info structure. It is the ** responsibility of the caller to eventually release the structure ** by passing the pointer returned by this function to sqlite3_free(). */ static sqlite3_index_info *allocateIndexInfo( Parse *pParse, /* The parsing context */ WhereClause *pWC, /* The WHERE clause being analyzed */ Bitmask mUnusable, /* Ignore terms with these prereqs */ struct SrcList_item *pSrc, /* The FROM clause term that is the vtab */ ExprList *pOrderBy, /* The ORDER BY clause */ u16 *pmNoOmit /* Mask of terms not to omit */ ){ int i, j; int nTerm; struct sqlite3_index_constraint *pIdxCons; struct sqlite3_index_orderby *pIdxOrderBy; struct sqlite3_index_constraint_usage *pUsage; struct HiddenIndexInfo *pHidden; WhereTerm *pTerm; int nOrderBy; sqlite3_index_info *pIdxInfo; u16 mNoOmit = 0; /* Count the number of possible WHERE clause constraints referring ** to this virtual table */ |
︙ | ︙ | |||
890 891 892 893 894 895 896 | } } /* Allocate the sqlite3_index_info structure */ pIdxInfo = sqlite3DbMallocZero(pParse->db, sizeof(*pIdxInfo) + (sizeof(*pIdxCons) + sizeof(*pUsage))*nTerm | | > | > > | 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 | } } /* Allocate the sqlite3_index_info structure */ pIdxInfo = sqlite3DbMallocZero(pParse->db, sizeof(*pIdxInfo) + (sizeof(*pIdxCons) + sizeof(*pUsage))*nTerm + sizeof(*pIdxOrderBy)*nOrderBy + sizeof(*pHidden) ); if( pIdxInfo==0 ){ sqlite3ErrorMsg(pParse, "out of memory"); return 0; } /* Initialize the structure. The sqlite3_index_info structure contains ** many fields that are declared "const" to prevent xBestIndex from ** changing them. We have to do some funky casting in order to ** initialize those fields. */ pHidden = (struct HiddenIndexInfo*)&pIdxInfo[1]; pIdxCons = (struct sqlite3_index_constraint*)&pHidden[1]; pIdxOrderBy = (struct sqlite3_index_orderby*)&pIdxCons[nTerm]; pUsage = (struct sqlite3_index_constraint_usage*)&pIdxOrderBy[nOrderBy]; *(int*)&pIdxInfo->nConstraint = nTerm; *(int*)&pIdxInfo->nOrderBy = nOrderBy; *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint = pIdxCons; *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy; *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage = pUsage; pHidden->pWC = pWC; pHidden->pParse = pParse; for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){ u16 op; if( pTerm->leftCursor != pSrc->iCursor ) continue; if( pTerm->prereqRight & mUnusable ) continue; assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); testcase( pTerm->eOperator & WO_IN ); testcase( pTerm->eOperator & WO_IS ); |
︙ | ︙ | |||
3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 | 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 | > > > > > > > > > > > > > > > > > > > > > | 3153 3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 3176 3177 3178 3179 3180 3181 3182 3183 3184 3185 3186 3187 | WHERETRACE(0xffff, (" bIn=%d prereqIn=%04llx prereqOut=%04llx\n", *pbIn, (sqlite3_uint64)mPrereq, (sqlite3_uint64)(pNew->prereq & ~mPrereq))); return rc; } /* ** 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_index_info *pIdxInfo, int iCons){ HiddenIndexInfo *pHidden = (HiddenIndexInfo*)&pIdxInfo[1]; const char *zRet = 0; if( iCons>=0 && iCons<pIdxInfo->nConstraint ){ CollSeq *pC = 0; int iTerm = pIdxInfo->aConstraint[iCons].iTermOffset; Expr *pX = pHidden->pWC->a[iTerm].pExpr; if( pX->pLeft ){ pC = sqlite3BinaryCompareCollSeq(pHidden->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 |
︙ | ︙ |
Changes to src/wherecode.c.
︙ | ︙ | |||
124 125 126 127 128 129 130 | WhereLevel *pLevel, /* Scan to write OP_Explain opcode for */ int iLevel, /* Value for "level" column of output */ int iFrom, /* Value for "from" column of output */ u16 wctrlFlags /* Flags passed to sqlite3WhereBegin() */ ){ int ret = 0; #if !defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_STMT_SCANSTATUS) | | | 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 | WhereLevel *pLevel, /* Scan to write OP_Explain opcode for */ int iLevel, /* Value for "level" column of output */ int iFrom, /* Value for "from" column of output */ u16 wctrlFlags /* Flags passed to sqlite3WhereBegin() */ ){ int ret = 0; #if !defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_STMT_SCANSTATUS) if( sqlite3ParseToplevel(pParse)->explain==2 ) #endif { struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom]; Vdbe *v = pParse->pVdbe; /* VM being constructed */ sqlite3 *db = pParse->db; /* Database handle */ int iId = pParse->iSelectId; /* Select id (left-most output column) */ int isSearch; /* True for a SEARCH. False for SCAN. */ |
︙ | ︙ |
Changes to test/colname.test.
︙ | ︙ | |||
373 374 375 376 377 378 379 380 381 382 383 384 385 386 | } {a 1 n 3} do_test colname-9.211 { execsql2 {SELECT t1.a AS n, v3.a FROM t1 JOIN v3} } {n 1 a 3} do_test colname-9.210 { execsql2 {SELECT t1.a, v3.a AS n FROM t1 JOIN v3} } {a 1 n 3} # Make sure the quotation marks get removed from the column names # when constructing a new table from an aggregate SELECT. # Email from Juergen Palm on 2017-07-11. # do_execsql_test colname-10.100 { DROP TABLE IF EXISTS t1; | > > > > > > > > > > > > > > > > > > > > > | 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 | } {a 1 n 3} do_test colname-9.211 { execsql2 {SELECT t1.a AS n, v3.a FROM t1 JOIN v3} } {n 1 a 3} do_test colname-9.210 { execsql2 {SELECT t1.a, v3.a AS n FROM t1 JOIN v3} } {a 1 n 3} # 2017-12-23: Ticket https://www.sqlite.org/src/info/3b4450072511e621 # Inconsistent column names in CREATE TABLE AS # # Verify that the names of columns in the created table of a CREATE TABLE AS # are the same as the names of result columns in the SELECT statement. # do_execsql_test colname-9.300 { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(aaa INT); INSERT INTO t1(aaa) VALUES(123); } do_test colname-9.310 { execsql2 {SELECT BBb FROM (SELECT aaa AS Bbb FROM t1)} } {Bbb 123} do_execsql_test colname-9.320 { CREATE TABLE t2 AS SELECT BBb FROM (SELECT aaa AS Bbb FROM t1); SELECT name FROM pragma_table_info('t2'); } {Bbb} # Make sure the quotation marks get removed from the column names # when constructing a new table from an aggregate SELECT. # Email from Juergen Palm on 2017-07-11. # do_execsql_test colname-10.100 { DROP TABLE IF EXISTS t1; |
︙ | ︙ |
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 } |
︙ | ︙ |
Changes to test/swarmvtab.test.
︙ | ︙ | |||
209 210 211 212 213 214 215 | do_catchsql_test 3.1 { CREATE VIRTUAL TABLE temp.xyz USING swarmvtab( 'VALUES ("test.db1", "t1", 1, 10), ("test.db2", "t1", 11, 20) ', 'fetch_db_no_such_function' ); | | | 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 | do_catchsql_test 3.1 { CREATE VIRTUAL TABLE temp.xyz USING swarmvtab( 'VALUES ("test.db1", "t1", 1, 10), ("test.db2", "t1", 11, 20) ', 'fetch_db_no_such_function' ); } {1 {sql error: no such function: fetch_db_no_such_function}} do_catchsql_test 3.2 { CREATE VIRTUAL TABLE temp.xyz USING swarmvtab( 'VALUES ("test.db1", "t1", 1, 10), ("test.db2", "t1", 11, 20) ', 'fetch_db' |
︙ | ︙ |
Changes to test/swarmvtab2.test.
︙ | ︙ | |||
10 11 12 13 14 15 16 | #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is the "swarmvtab" extension # set testdir [file dirname $argv0] source $testdir/tester.tcl | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is the "swarmvtab" extension # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix swarmvtab2 do_not_use_codec ifcapable !vtab { finish_test return } |
︙ | ︙ |
Added test/swarmvtab3.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 | # 2017-07-15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is the "swarmvtab" extension # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix swarmvtab3 do_not_use_codec ifcapable !vtab { finish_test return } load_static_extension db unionvtab set nFile $sqlite_open_file_count do_execsql_test 1.0 { CREATE TEMP TABLE swarm(id, tbl, minval, maxval); } # Set up 100 databases with filenames "remote_test.dbN", where N is between # 0 and 99. do_test 1.1 { for {set i 0} {$i < 100} {incr i} { set file remote_test.db$i forcedelete $file forcedelete test.db$i sqlite3 rrr $file rrr eval { CREATE TABLE t1(a INTEGER PRIMARY KEY, b); INSERT INTO t1 VALUES($i, $i); } rrr close db eval { INSERT INTO swarm VALUES($i, 't1', $i, $i); } set ::dbcache(test.db$i) 0 } } {} proc missing_db {filename} { set remote "remote_$filename" forcedelete $filename file copy $remote $filename } db func missing_db missing_db proc openclose_db {filename bClose} { if {$bClose} { incr ::dbcache($filename) -1 } else { incr ::dbcache($filename) 1 } if {$::dbcache($filename)==0} { forcedelete $filename } } db func openclose_db openclose_db proc check_dbcache {} { set n 0 for {set i 0} {$i<100} {incr i} { set exists [file exists test.db$i] if {$exists!=($::dbcache(test.db$i)!=0)} { error "inconsistent ::dbcache and disk" } incr n $exists } return $n } foreach {tn nMaxOpen cvt} { 1 5 { CREATE VIRTUAL TABLE temp.s USING swarmvtab( 'SELECT :prefix || id, tbl, minval, minval FROM swarm', :prefix='test.db', missing=missing_db, openclose=openclose_db, maxopen=5 ) } 2 3 { CREATE VIRTUAL TABLE temp.s USING swarmvtab( 'SELECT :prefix || id, tbl, minval, minval FROM swarm', :prefix='test.db', missing = 'missing_db', openclose=[openclose_db], maxopen = 3 ) } 3 1 { CREATE VIRTUAL TABLE temp.s USING swarmvtab( 'SELECT :prefix||''.''||:suffix||id, tbl, minval, minval FROM swarm', :prefix=test, :suffix=db, missing = 'missing_db', openclose=[openclose_db], maxopen = 1 ) } } { execsql { DROP TABLE IF EXISTS s } do_execsql_test 1.$tn.1 $cvt do_execsql_test 1.$tn.2 { SELECT b FROM s WHERE a<10; } {0 1 2 3 4 5 6 7 8 9} do_test 1.$tn.3 { check_dbcache } $nMaxOpen do_execsql_test 1.$tn.4 { SELECT b FROM s WHERE (b%10)=0; } {0 10 20 30 40 50 60 70 80 90} do_test 1.$tn.5 { check_dbcache } $nMaxOpen } execsql { DROP TABLE IF EXISTS s } for {set i 0} {$i < 100} {incr i} { forcedelete remote_test.db$i } #---------------------------------------------------------------------------- # do_execsql_test 2.0 { DROP TABLE IF EXISTS swarm; CREATE TEMP TABLE swarm(file, tbl, minval, maxval, ctx); } catch { array unset ::dbcache } # Set up 100 databases with filenames "remote_test.dbN", where N is a # random integer between 0 and 1,000,000 # 0 and 99. do_test 2.1 { for {set i 0} {$i < 100} {incr i} { while 1 { set ctx [expr abs(int(rand() *1000000))] if {[info exists ::dbcache($ctx)]==0} break } set file test_remote.db$ctx forcedelete $file forcedelete test.db$i sqlite3 rrr $file rrr eval { CREATE TABLE t1(a INTEGER PRIMARY KEY, b); INSERT INTO t1 VALUES($i, $i); } rrr close db eval { INSERT INTO swarm VALUES('test.db' || $i, 't1', $i, $i, $file) } set ::dbcache(test.db$i) 0 } } {} proc missing_db {filename ctx} { file copy $ctx $filename } db func missing_db missing_db proc openclose_db {filename ctx bClose} { if {$bClose} { incr ::dbcache($filename) -1 } else { incr ::dbcache($filename) 1 } if {$::dbcache($filename)==0} { forcedelete $filename } } db func openclose_db openclose_db proc check_dbcache {} { set n 0 foreach k [array names ::dbcache] { set exists [file exists $k] if {$exists!=($::dbcache($k)!=0)} { error "inconsistent ::dbcache and disk ($k)" } incr n $exists } return $n } foreach {tn nMaxOpen cvt} { 2 5 { CREATE VIRTUAL TABLE temp.s USING swarmvtab( 'SELECT file, tbl, minval, minval, ctx FROM swarm', missing=missing_db, openclose=openclose_db, maxopen=5 ) } } { execsql { DROP TABLE IF EXISTS s } do_execsql_test 1.$tn.1 $cvt do_execsql_test 1.$tn.2 { SELECT b FROM s WHERE a<10; } {0 1 2 3 4 5 6 7 8 9} do_test 1.$tn.3 { check_dbcache } $nMaxOpen do_execsql_test 1.$tn.4 { SELECT b FROM s WHERE (b%10)=0; } {0 10 20 30 40 50 60 70 80 90} do_test 1.$tn.5 { check_dbcache } $nMaxOpen } db close forcedelete {*}[glob test_remote.db*] finish_test |
Changes to test/tester.tcl.
︙ | ︙ | |||
2267 2268 2269 2270 2271 2272 2273 | eval sqlite3_config_pagecache $::old_pagecache_config unset ::old_pagecache_config sqlite3_initialize autoinstall_test_functions sqlite3 db test.db } | | | > > > > | 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 | eval sqlite3_config_pagecache $::old_pagecache_config unset ::old_pagecache_config sqlite3_initialize autoinstall_test_functions sqlite3 db test.db } proc test_binary_name {nm} { if {$::tcl_platform(platform)=="windows"} { set ret "$nm.exe" } else { set ret $nm } file normalize [file join $::cmdlinearg(TESTFIXTURE_HOME) $ret] } proc test_find_binary {nm} { set ret [test_binary_name $nm] if {![file executable $ret]} { finish_test return "" } return $ret } |
︙ | ︙ |
Changes to tool/lempar.c.
︙ | ︙ | |||
647 648 649 650 651 652 653 654 655 656 | }; static void yy_accept(yyParser*); /* Forward Declaration */ /* ** Perform a reduce action and the shift that must immediately ** follow the reduce. */ static void yy_reduce( yyParser *yypParser, /* The parser */ | > > > > > > | > > | 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 | }; static void yy_accept(yyParser*); /* Forward Declaration */ /* ** Perform a reduce action and the shift that must immediately ** follow the reduce. ** ** The yyLookahead and yyLookaheadToken parameters provide reduce actions ** access to the lookahead token (if any). The yyLookahead will be YYNOCODE ** if the lookahead token has already been consumed. As this procedure is ** only called from one place, optimizing compilers will in-line it, which ** means that the extra parameters have no performance impact. */ static void yy_reduce( yyParser *yypParser, /* The parser */ unsigned int yyruleno, /* Number of the rule by which to reduce */ int yyLookahead, /* Lookahead token, or YYNOCODE if none */ ParseTOKENTYPE yyLookaheadToken /* Value of the lookahead token */ ){ int yygoto; /* The next state */ int yyact; /* The next action */ yyStackEntry *yymsp; /* The top of the parser's stack */ int yysize; /* Amount to pop the stack */ ParseARG_FETCH; yymsp = yypParser->yytos; |
︙ | ︙ | |||
849 850 851 852 853 854 855 | if( yyact <= YY_MAX_SHIFTREDUCE ){ yy_shift(yypParser,yyact,yymajor,yyminor); #ifndef YYNOERRORRECOVERY yypParser->yyerrcnt--; #endif yymajor = YYNOCODE; }else if( yyact <= YY_MAX_REDUCE ){ | | | 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 | if( yyact <= YY_MAX_SHIFTREDUCE ){ yy_shift(yypParser,yyact,yymajor,yyminor); #ifndef YYNOERRORRECOVERY yypParser->yyerrcnt--; #endif yymajor = YYNOCODE; }else if( yyact <= YY_MAX_REDUCE ){ yy_reduce(yypParser,yyact-YY_MIN_REDUCE,yymajor,yyminor); }else{ assert( yyact == YY_ERROR_ACTION ); yyminorunion.yy0 = yyminor; #ifdef YYERRORSYMBOL int yymx; #endif #ifndef NDEBUG |
︙ | ︙ |