Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch expert-in-shell Excluding Merge-Ins
This is equivalent to a diff from 4c782c95 to 51068dba
2017-12-21
| ||
02:17 | Add the experimental ".expert" command to the sqlite3.exe shell. (check-in: 0821bae7 user: drh tags: trunk) | |
2017-12-19
| ||
18:56 | Fix crash in sqlite3_vtab_collation() when called for an IS NOT NULL constraint. (check-in: ad38d2c4 user: dan tags: trunk) | |
17:42 | Experimentally add the SQLite expert functionality to the shell tool. (Closed-Leaf check-in: 51068dba user: dan tags: expert-in-shell) | |
2017-12-16
| ||
20:20 | Add an experimental location(X) SQL function that attempt to return the location of the payload within the database for the record that contains column X. location(X) returns NULL if X is not an ordinary table column or if SQLite cannot figure out the location because it is using a covering index. (check-in: 51be9558 user: drh tags: location-function) | |
19:36 | Add the sqlite3_vtab_collation() function, which allows an xBestIndex callback to determine the collation sequence that SQLite will use for a comparison. And the SQLITE_DBCONFIG_FULL_EQP configuration option, which enhances the output of "EXPLAIN QUERY PLAN" so that it includes statements run by triggers. And the code for the sqlite3_expert extension and command line application. (check-in: 4c782c95 user: dan tags: trunk) | |
19:16 | Merge latest trunk changes into this branch. (Closed-Leaf check-in: d5b597b5 user: dan tags: schemalint) | |
04:37 | Add unnecessary initializations to some local variables in the rtree module to suppress false-positive compiler warnings coming out of MSVC. (check-in: 64487d65 user: drh tags: trunk) | |
Changes to ext/expert/expert1.test.
︙ | ︙ | |||
19 20 21 22 23 24 25 | # if {![info exists testdir]} { set testdir [file join [file dirname [info script]] .. .. test] } source $testdir/tester.tcl set testprefix expert1 | < | < | < | 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | # 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 |
︙ | ︙ | |||
68 69 70 71 72 73 74 75 76 77 78 79 80 81 | } $expert destroy set tst [subst -nocommands {set {} [squish [join {$result}]]}] uplevel [list do_test $tn $tst [string trim [squish $res]]] } } } { eval $setup do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } { | > > > > > > > > > > > | 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 | } $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) } { |
︙ | ︙ |
Changes to main.mk.
︙ | ︙ | |||
688 689 690 691 692 693 694 | ./mkkeywordhash >keywordhash.h # 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 \ | | > > | 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 | ./mkkeywordhash >keywordhash.h # 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/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. |
︙ | ︙ |
Changes to src/shell.c.in.
︙ | ︙ | |||
792 793 794 795 796 797 798 799 800 801 802 803 804 805 | */ #define SQLITE_EXTENSION_INIT1 #define SQLITE_EXTENSION_INIT2(X) (void)(X) INCLUDE ../ext/misc/shathree.c INCLUDE ../ext/misc/fileio.c INCLUDE ../ext/misc/completion.c #if defined(SQLITE_ENABLE_SESSION) /* ** State information for a single open session */ typedef struct OpenSession OpenSession; struct OpenSession { | > > | 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 | */ #define SQLITE_EXTENSION_INIT1 #define SQLITE_EXTENSION_INIT2(X) (void)(X) INCLUDE ../ext/misc/shathree.c INCLUDE ../ext/misc/fileio.c INCLUDE ../ext/misc/completion.c 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 { |
︙ | ︙ | |||
817 818 819 820 821 822 823 824 825 826 827 828 829 830 | 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 { | > > > > > > | 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 | 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 { |
︙ | ︙ | |||
862 863 864 865 866 867 868 869 870 871 872 873 874 875 | 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 */ | > | 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 | 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..." */ }; /* ** These are the allowed shellFlgs values */ #define SHFLG_Pagecache 0x00000001 /* The --pagecache option is used */ #define SHFLG_Lookaside 0x00000002 /* Lookaside memory is used */ |
︙ | ︙ | |||
2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 2254 2255 2256 2257 | }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() | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 2253 2254 2255 2256 2257 2258 2259 2260 2261 2262 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 | }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() |
︙ | ︙ | |||
2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 | 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); | > > > > > | 2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 | 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); |
︙ | ︙ | |||
4064 4065 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076 4077 4078 4079 4080 4081 4082 4083 4084 4085 4086 4087 4088 4089 4090 | usage: raw_printf(stderr, "Usage %s sub-command ?switches...?\n", azArg[0]); raw_printf(stderr, "Where sub-commands are:\n"); raw_printf(stderr, " fkey-indexes\n"); return SQLITE_ERROR; } /* ** 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]=='"' ){ | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 4151 4152 4153 4154 4155 4156 4157 4158 4159 4160 4161 4162 4163 4164 4165 4166 4167 4168 4169 4170 4171 4172 4173 4174 4175 4176 4177 4178 4179 4180 4181 4182 4183 4184 4185 4186 4187 4188 4189 4190 4191 4192 4193 4194 4195 4196 4197 4198 4199 4200 4201 4202 4203 4204 4205 4206 4207 4208 4209 4210 4211 4212 4213 4214 4215 4216 4217 4218 4219 4220 4221 4222 4223 4224 4225 4226 4227 4228 4229 4230 4231 4232 4233 4234 4235 4236 4237 4238 4239 | usage: raw_printf(stderr, "Usage %s sub-command ?switches...?\n", azArg[0]); raw_printf(stderr, "Where sub-commands are:\n"); raw_printf(stderr, " fkey-indexes\n"); return SQLITE_ERROR; } /* ** 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]=='"' ){ |
︙ | ︙ | |||
4431 4432 4433 4434 4435 4436 4437 4438 4439 4440 4441 4442 4443 4444 | 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; | > > > > > | 4580 4581 4582 4583 4584 4585 4586 4587 4588 4589 4590 4591 4592 4593 4594 4595 4596 4597 4598 | 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; |
︙ | ︙ |
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 } |
︙ | ︙ |