/ Check-in [51068dba]
Login

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

Overview
Comment:Experimentally add the SQLite expert functionality to the shell tool.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | expert-in-shell
Files: files | file ages | folders
SHA3-256: 51068dbaeaef13bb80af8126b8c4f3a454dee63de5127d706db50bf789533e60
User & Date: dan 2017-12-19 17:42:13
Context
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
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
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/expert/expert1.test.

    19     19   #
    20     20   if {![info exists testdir]} {
    21     21     set testdir [file join [file dirname [info script]] .. .. test]
    22     22   }
    23     23   source $testdir/tester.tcl
    24     24   set testprefix expert1
    25     25   
    26         -if {$tcl_platform(platform)=="windows"} {
    27         -  set CMD "sqlite3_expert.exe"
    28         -} else {
    29         -  set CMD ".././sqlite3_expert"
    30         -}
           26  +set CLI [test_binary_name sqlite3]
           27  +set CMD [test_binary_name sqlite3_expert]
    31     28   
    32     29   proc squish {txt} {
    33     30     regsub -all {[[:space:]]+} $txt { }
    34     31   }
    35     32   
    36     33   proc do_setup_rec_test {tn setup sql res} {
    37     34     reset_db
................................................................................
    68     65         }
    69     66   
    70     67         $expert destroy
    71     68   
    72     69         set tst [subst -nocommands {set {} [squish [join {$result}]]}]
    73     70         uplevel [list do_test $tn $tst [string trim [squish $res]]]
    74     71       }
           72  +  }
           73  +  3 {
           74  +    if {![file executable $CLI]} { continue }
           75  +
           76  +    proc do_rec_test {tn sql res} {
           77  +      set res [squish [string trim $res]]
           78  +      set tst [subst -nocommands { 
           79  +        squish [string trim [exec $::CLI test.db ".expert" {$sql;}]]
           80  +      }]
           81  +      uplevel [list do_test $tn $tst $res]
           82  +    }
    75     83     }
    76     84   } {
    77     85   
    78     86     eval $setup
    79     87   
    80     88   
    81     89   do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } {

Changes to main.mk.

   688    688   	./mkkeywordhash >keywordhash.h
   689    689   
   690    690   # Source files that go into making shell.c
   691    691   SHELL_SRC = \
   692    692   	$(TOP)/src/shell.c.in \
   693    693   	$(TOP)/ext/misc/shathree.c \
   694    694   	$(TOP)/ext/misc/fileio.c \
   695         -	$(TOP)/ext/misc/completion.c
          695  +	$(TOP)/ext/misc/completion.c \
          696  +	$(TOP)/ext/expert/sqlite3expert.c \
          697  +	$(TOP)/ext/expert/sqlite3expert.h
   696    698   
   697    699   shell.c:	$(SHELL_SRC) $(TOP)/tool/mkshellc.tcl
   698    700   	tclsh $(TOP)/tool/mkshellc.tcl >shell.c
   699    701   
   700    702   
   701    703   
   702    704   # Rules to build the extension objects.

Changes to src/shell.c.in.

   792    792   */
   793    793   #define SQLITE_EXTENSION_INIT1
   794    794   #define SQLITE_EXTENSION_INIT2(X) (void)(X)
   795    795   
   796    796   INCLUDE ../ext/misc/shathree.c
   797    797   INCLUDE ../ext/misc/fileio.c
   798    798   INCLUDE ../ext/misc/completion.c
          799  +INCLUDE ../ext/expert/sqlite3expert.h
          800  +INCLUDE ../ext/expert/sqlite3expert.c
   799    801   
   800    802   #if defined(SQLITE_ENABLE_SESSION)
   801    803   /*
   802    804   ** State information for a single open session
   803    805   */
   804    806   typedef struct OpenSession OpenSession;
   805    807   struct OpenSession {
................................................................................
   817    819   typedef struct SavedModeInfo SavedModeInfo;
   818    820   struct SavedModeInfo {
   819    821     int valid;          /* Is there legit data in here? */
   820    822     int mode;           /* Mode prior to ".explain on" */
   821    823     int showHeader;     /* The ".header" setting prior to ".explain on" */
   822    824     int colWidth[100];  /* Column widths prior to ".explain on" */
   823    825   };
          826  +
          827  +typedef struct ExpertInfo ExpertInfo;
          828  +struct ExpertInfo {
          829  +  sqlite3expert *pExpert;
          830  +  int bVerbose;
          831  +};
   824    832   
   825    833   /*
   826    834   ** State information about the database connection is contained in an
   827    835   ** instance of the following structure.
   828    836   */
   829    837   typedef struct ShellState ShellState;
   830    838   struct ShellState {
................................................................................
   862    870     int *aiIndent;         /* Array of indents used in MODE_Explain */
   863    871     int nIndent;           /* Size of array aiIndent[] */
   864    872     int iIndent;           /* Index of current op in aiIndent[] */
   865    873   #if defined(SQLITE_ENABLE_SESSION)
   866    874     int nSession;             /* Number of active sessions */
   867    875     OpenSession aSession[4];  /* Array of sessions.  [0] is in focus. */
   868    876   #endif
          877  +  ExpertInfo expert;        /* Valid if previous command was ".expert OPT..." */
   869    878   };
   870    879   
   871    880   /*
   872    881   ** These are the allowed shellFlgs values
   873    882   */
   874    883   #define SHFLG_Pagecache      0x00000001 /* The --pagecache option is used */
   875    884   #define SHFLG_Lookaside      0x00000002 /* Lookaside memory is used */
................................................................................
  2244   2253       }else{
  2245   2254         do{
  2246   2255           rc = sqlite3_step(pStmt);
  2247   2256         } while( rc == SQLITE_ROW );
  2248   2257       }
  2249   2258     }
  2250   2259   }
         2260  +
         2261  +/*
         2262  +** This function is called to process SQL if the previous shell command
         2263  +** was ".expert". It passes the SQL in the second argument directly to
         2264  +** the sqlite3expert object.
         2265  +**
         2266  +** If successful, SQLITE_OK is returned. Otherwise, an SQLite error
         2267  +** code. In this case, (*pzErr) may be set to point to a buffer containing
         2268  +** an English language error message. It is the responsibility of the
         2269  +** caller to eventually free this buffer using sqlite3_free().
         2270  +*/
         2271  +static int expertHandleSQL(
         2272  +  ShellState *pState, 
         2273  +  const char *zSql, 
         2274  +  char **pzErr
         2275  +){
         2276  +  assert( pState->expert.pExpert );
         2277  +  assert( pzErr==0 || *pzErr==0 );
         2278  +  return sqlite3_expert_sql(pState->expert.pExpert, zSql, pzErr);
         2279  +}
         2280  +
         2281  +/*
         2282  +** This function is called either to silently clean up the object
         2283  +** created by the ".expert" command (if bCancel==1), or to generate a 
         2284  +** report from it and then clean it up (if bCancel==0).
         2285  +**
         2286  +** If successful, SQLITE_OK is returned. Otherwise, an SQLite error
         2287  +** code. In this case, (*pzErr) may be set to point to a buffer containing
         2288  +** an English language error message. It is the responsibility of the
         2289  +** caller to eventually free this buffer using sqlite3_free().
         2290  +*/
         2291  +static int expertFinish(
         2292  +  ShellState *pState,
         2293  +  int bCancel,
         2294  +  char **pzErr
         2295  +){
         2296  +  int rc = SQLITE_OK;
         2297  +  sqlite3expert *p = pState->expert.pExpert;
         2298  +  assert( p );
         2299  +  assert( bCancel || pzErr==0 || *pzErr==0 );
         2300  +  if( bCancel==0 ){
         2301  +    FILE *out = pState->out;
         2302  +    int bVerbose = pState->expert.bVerbose;
         2303  +
         2304  +    rc = sqlite3_expert_analyze(p, pzErr);
         2305  +    if( rc==SQLITE_OK ){
         2306  +      int nQuery = sqlite3_expert_count(p);
         2307  +      int i;
         2308  +
         2309  +      if( bVerbose ){
         2310  +        const char *zCand = sqlite3_expert_report(p,0,EXPERT_REPORT_CANDIDATES);
         2311  +        raw_printf(out, "-- Candidates -----------------------------\n");
         2312  +        raw_printf(out, "%s\n", zCand);
         2313  +      }
         2314  +      for(i=0; i<nQuery; i++){
         2315  +        const char *zSql = sqlite3_expert_report(p, i, EXPERT_REPORT_SQL);
         2316  +        const char *zIdx = sqlite3_expert_report(p, i, EXPERT_REPORT_INDEXES);
         2317  +        const char *zEQP = sqlite3_expert_report(p, i, EXPERT_REPORT_PLAN);
         2318  +        if( zIdx==0 ) zIdx = "(no new indexes)\n";
         2319  +        if( bVerbose ){
         2320  +          raw_printf(out, "-- Query %d --------------------------------\n",i+1);
         2321  +          raw_printf(out, "%s\n\n", zSql);
         2322  +        }
         2323  +        raw_printf(out, "%s\n", zIdx);
         2324  +        raw_printf(out, "%s\n", zEQP);
         2325  +      }
         2326  +    }
         2327  +  }
         2328  +  sqlite3_expert_destroy(p);
         2329  +  pState->expert.pExpert = 0;
         2330  +  return rc;
         2331  +}
         2332  +
  2251   2333   
  2252   2334   /*
  2253   2335   ** Execute a statement or set of statements.  Print
  2254   2336   ** any result rows/columns depending on the current mode
  2255   2337   ** set via the supplied callback.
  2256   2338   **
  2257   2339   ** This is very similar to SQLite's built-in sqlite3_exec()
................................................................................
  2270   2352     int rc = SQLITE_OK;             /* Return Code */
  2271   2353     int rc2;
  2272   2354     const char *zLeftover;          /* Tail of unprocessed SQL */
  2273   2355   
  2274   2356     if( pzErrMsg ){
  2275   2357       *pzErrMsg = NULL;
  2276   2358     }
         2359  +
         2360  +  if( pArg->expert.pExpert ){
         2361  +    rc = expertHandleSQL(pArg, zSql, pzErrMsg);
         2362  +    return expertFinish(pArg, (rc!=SQLITE_OK), pzErrMsg);
         2363  +  }
  2277   2364   
  2278   2365     while( zSql[0] && (SQLITE_OK == rc) ){
  2279   2366       static const char *zStmtSql;
  2280   2367       rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, &zLeftover);
  2281   2368       if( SQLITE_OK != rc ){
  2282   2369         if( pzErrMsg ){
  2283   2370           *pzErrMsg = save_err_msg(db);
................................................................................
  4064   4151    usage:
  4065   4152     raw_printf(stderr, "Usage %s sub-command ?switches...?\n", azArg[0]);
  4066   4153     raw_printf(stderr, "Where sub-commands are:\n");
  4067   4154     raw_printf(stderr, "    fkey-indexes\n");
  4068   4155     return SQLITE_ERROR;
  4069   4156   }
  4070   4157   
         4158  +/*
         4159  +** Implementation of ".expert" dot command.
         4160  +*/
         4161  +static int expertDotCommand(
         4162  +  ShellState *pState,             /* Current shell tool state */
         4163  +  char **azArg,                   /* Array of arguments passed to dot command */
         4164  +  int nArg                        /* Number of entries in azArg[] */
         4165  +){
         4166  +  int rc = SQLITE_OK;
         4167  +  char *zErr = 0;
         4168  +  int i;
         4169  +  int iSample = 0;
         4170  +
         4171  +  assert( pState->expert.pExpert==0 );
         4172  +  memset(&pState->expert, 0, sizeof(ExpertInfo));
         4173  +
         4174  +  for(i=1; rc==SQLITE_OK && i<nArg; i++){
         4175  +    char *z = azArg[i];
         4176  +    int n;
         4177  +    if( z[0]=='-' && z[1]=='-' ) z++;
         4178  +    n = strlen(z);
         4179  +    if( n>=2 && 0==strncmp(z, "-verbose", n) ){
         4180  +      pState->expert.bVerbose = 1;
         4181  +    }
         4182  +    else if( n>=2 && 0==strncmp(z, "-sample", n) ){
         4183  +      if( i==(nArg-1) ){
         4184  +        raw_printf(stderr, "option requires an argument: %s\n", z);
         4185  +        rc = SQLITE_ERROR;
         4186  +      }else{
         4187  +        iSample = (int)integerValue(azArg[++i]);
         4188  +        if( iSample<0 || iSample>100 ){
         4189  +          raw_printf(stderr, "value out of range: %s\n", azArg[i]);
         4190  +          rc = SQLITE_ERROR;
         4191  +        }
         4192  +      }
         4193  +    }
         4194  +    else{
         4195  +      raw_printf(stderr, "unknown option: %s\n", z);
         4196  +      rc = SQLITE_ERROR;
         4197  +    }
         4198  +  }
         4199  +
         4200  +  if( rc==SQLITE_OK ){
         4201  +    pState->expert.pExpert = sqlite3_expert_new(pState->db, &zErr);
         4202  +    if( pState->expert.pExpert==0 ){
         4203  +      raw_printf(stderr, "sqlite3_expert_new: %s\n", zErr);
         4204  +      rc = SQLITE_ERROR;
         4205  +    }else{
         4206  +      sqlite3_expert_config(
         4207  +          pState->expert.pExpert, EXPERT_CONFIG_SAMPLE, iSample
         4208  +      );
         4209  +    }
         4210  +  }
         4211  +
         4212  +  return rc;
         4213  +}
         4214  +
         4215  +
  4071   4216   
  4072   4217   /*
  4073   4218   ** If an input line begins with "." then invoke this routine to
  4074   4219   ** process that line.
  4075   4220   **
  4076   4221   ** Return 1 on error, 2 to exit, and 0 otherwise.
  4077   4222   */
  4078   4223   static int do_meta_command(char *zLine, ShellState *p){
  4079   4224     int h = 1;
  4080   4225     int nArg = 0;
  4081   4226     int n, c;
  4082   4227     int rc = 0;
  4083   4228     char *azArg[50];
         4229  +
         4230  +  if( p->expert.pExpert ){
         4231  +    expertFinish(p, 1, 0);
         4232  +  }
  4084   4233   
  4085   4234     /* Parse the input line into tokens.
  4086   4235     */
  4087   4236     while( zLine[h] && nArg<ArraySize(azArg) ){
  4088   4237       while( IsSpace(zLine[h]) ){ h++; }
  4089   4238       if( zLine[h]==0 ) break;
  4090   4239       if( zLine[h]=='\'' || zLine[h]=='"' ){
................................................................................
  4431   4580         if( p->mode==MODE_Explain ) p->mode = p->normalMode;
  4432   4581         p->autoExplain = 0;
  4433   4582       }else if( val==99 ){
  4434   4583         if( p->mode==MODE_Explain ) p->mode = p->normalMode;
  4435   4584         p->autoExplain = 1;
  4436   4585       }
  4437   4586     }else
         4587  +
         4588  +  if( c=='e' && strncmp(azArg[0], "expert", n)==0 ){
         4589  +    open_db(p, 0);
         4590  +    expertDotCommand(p, azArg, nArg);
         4591  +  }else
  4438   4592   
  4439   4593     if( c=='f' && strncmp(azArg[0], "fullschema", n)==0 ){
  4440   4594       ShellState data;
  4441   4595       char *zErrMsg = 0;
  4442   4596       int doStats = 0;
  4443   4597       memcpy(&data, p, sizeof(data));
  4444   4598       data.showHeader = 0;

Changes to test/tester.tcl.

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