/ 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 Unified Diffs Ignore Whitespace Patch

Changes to ext/expert/expert1.test.

19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
..
68
69
70
71
72
73
74











75
76
77
78
79
80
81
#
if {![info exists testdir]} {
  set testdir [file join [file dirname [info script]] .. .. test]
}
source $testdir/tester.tcl
set testprefix expert1

if {$tcl_platform(platform)=="windows"} {
  set CMD "sqlite3_expert.exe"
} else {
  set CMD ".././sqlite3_expert"
}

proc squish {txt} {
  regsub -all {[[:space:]]+} $txt { }
}

proc do_setup_rec_test {tn setup sql res} {
  reset_db
................................................................................
      }

      $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) } {







|
|
<
<
<







 







>
>
>
>
>
>
>
>
>
>
>







19
20
21
22
23
24
25
26
27



28
29
30
31
32
33
34
..
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
#
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
................................................................................
      }

      $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
695


696
697
698
699
700
701
702
	./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



shell.c:	$(SHELL_SRC) $(TOP)/tool/mkshellc.tcl
	tclsh $(TOP)/tool/mkshellc.tcl >shell.c



# Rules to build the extension objects.







|
>
>







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
...
817
818
819
820
821
822
823






824
825
826
827
828
829
830
...
862
863
864
865
866
867
868

869
870
871
872
873
874
875
....
2244
2245
2246
2247
2248
2249
2250









































































2251
2252
2253
2254
2255
2256
2257
....
2270
2271
2272
2273
2274
2275
2276





2277
2278
2279
2280
2281
2282
2283
....
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
....
4431
4432
4433
4434
4435
4436
4437





4438
4439
4440
4441
4442
4443
4444
*/
#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 {
................................................................................
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 {
................................................................................
  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 */
................................................................................
    }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()
................................................................................
  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);
................................................................................
 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]=='"' ){
................................................................................
      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;







>
>







 







>
>
>
>
>
>







 







>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>













>
>
>
>







 







>
>
>
>
>







792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
...
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
...
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
....
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
....
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
....
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
....
4580
4581
4582
4583
4584
4585
4586
4587
4588
4589
4590
4591
4592
4593
4594
4595
4596
4597
4598
*/
#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 {
................................................................................
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 {
................................................................................
  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 */
................................................................................
    }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()
................................................................................
  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);
................................................................................
 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]=='"' ){
................................................................................
      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
2274
2275
2276
2277
2278
2279
2280




2281
2282
2283
2284
2285
2286
2287
  eval sqlite3_config_pagecache $::old_pagecache_config
  unset ::old_pagecache_config 
  sqlite3_initialize
  autoinstall_test_functions
  sqlite3 db test.db
}

proc test_find_binary {nm} {
  if {$::tcl_platform(platform)=="windows"} {
    set ret "$nm.exe"
  } else {
    set ret $nm
  }
  set ret [file normalize [file join $::cmdlinearg(TESTFIXTURE_HOME) $ret]]




  if {![file executable $ret]} {
    finish_test
    return ""
  }
  return $ret
}








|





|
>
>
>
>







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
}