ext/expert/expert.c

   1 /*
   2 ** 2017 April 07
   3 **
   4 ** The author disclaims copyright to this source code.  In place of
   5 ** a legal notice, here is a blessing:
   6 **
   7 **    May you do good and not evil.
   8 **    May you find forgiveness for yourself and forgive others.
   9 **    May you share freely, never taking more than you give.
  10 **
  11 *************************************************************************
  12 */
  13
  14
  15 #include <sqlite3.h>
  16 #include <stdio.h>
  17 #include <stdlib.h>
  18 #include <string.h>
  19 #include "sqlite3expert.h"
  20
  21
  22 static void option_requires_argument(const char *zOpt){
  23   fprintf(stderr, "Option requires an argument: %s\n", zOpt);
  24   exit(-3);
  25 }
  26
  27 static int option_integer_arg(const char *zVal){
  28   return atoi(zVal);
  29 }
  30
  31 static void usage(char **argv){
  32   fprintf(stderr, "\n");
  33   fprintf(stderr, "Usage %s ?OPTIONS? DATABASE\n", argv[0]);
  34   fprintf(stderr, "\n");
  35   fprintf(stderr, "Options are:\n");
  36   fprintf(stderr, "  -sql SQL   (analyze SQL statements passed as argument)\n");
  37   fprintf(stderr, "  -file FILE (read SQL statements from file FILE)\n");
  38   fprintf(stderr, "  -verbose LEVEL (integer verbosity level. default 1)\n");
  39   fprintf(stderr, "  -sample PERCENT (percent of db to sample. default 100)\n");
  40   exit(-1);
  41 }
  42
  43 static int readSqlFromFile(sqlite3expert *p, const char *zFile, char **pzErr){
  44   FILE *in = fopen(zFile, "rb");
  45   long nIn;
  46   size_t nRead;
  47   char *pBuf;
  48   int rc;
  49   if( in==0 ){
  50     *pzErr = sqlite3_mprintf("failed to open file %s\n", zFile);
  51     return SQLITE_ERROR;
  52   }
  53   fseek(in, 0, SEEK_END);
  54   nIn = ftell(in);
  55   rewind(in);
  56   pBuf = sqlite3_malloc64( nIn+1 );
  57   nRead = fread(pBuf, nIn, 1, in);
  58   fclose(in);
  59   if( nRead!=1 ){
  60     sqlite3_free(pBuf);
  61     *pzErr = sqlite3_mprintf("failed to read file %s\n", zFile);
  62     return SQLITE_ERROR;
  63   }
  64   pBuf[nIn] = 0;
  65   rc = sqlite3_expert_sql(p, pBuf, pzErr);
  66   sqlite3_free(pBuf);
  67   return rc;
  68 }
  69
  70 int main(int argc, char **argv){
  71   const char *zDb;
  72   int rc = 0;
  73   char *zErr = 0;
  74   int i;
  75   int iVerbose = 1;               /* -verbose option */
  76
  77   sqlite3 *db = 0;
  78   sqlite3expert *p = 0;
  79
  80   if( argc<2 ) usage(argv);
  81   zDb = argv[argc-1];
  82   if( zDb[0]=='-' ) usage(argv);
  83   rc = sqlite3_open(zDb, &db);
  84   if( rc!=SQLITE_OK ){
  85     fprintf(stderr, "Cannot open db file: %s - %s\n", zDb, sqlite3_errmsg(db));
  86     exit(-2);
  87   }
  88
  89   p = sqlite3_expert_new(db, &zErr);
  90   if( p==0 ){
  91     fprintf(stderr, "Cannot run analysis: %s\n", zErr);
  92     rc = 1;
  93   }else{
  94     for(i=1; i<(argc-1); i++){
  95       char *zArg = argv[i];
  96       int nArg;
  97       if( zArg[0]=='-' && zArg[1]=='-' && zArg[2]!=0 ) zArg++;
  98       nArg = (int)strlen(zArg);
  99       if( nArg>=2 && 0==sqlite3_strnicmp(zArg, "-file", nArg) ){
 100         if( ++i==(argc-1) ) option_requires_argument("-file");
 101         rc = readSqlFromFile(p, argv[i], &zErr);
 102       }
 103
 104       else if( nArg>=3 && 0==sqlite3_strnicmp(zArg, "-sql", nArg) ){
 105         if( ++i==(argc-1) ) option_requires_argument("-sql");
 106         rc = sqlite3_expert_sql(p, argv[i], &zErr);
 107       }
 108
 109       else if( nArg>=3 && 0==sqlite3_strnicmp(zArg, "-sample", nArg) ){
 110         int iSample;
 111         if( ++i==(argc-1) ) option_requires_argument("-sample");
 112         iSample = option_integer_arg(argv[i]);
 113         sqlite3_expert_config(p, EXPERT_CONFIG_SAMPLE, iSample);
 114       }
 115
 116       else if( nArg>=2 && 0==sqlite3_strnicmp(zArg, "-verbose", nArg) ){
 117         if( ++i==(argc-1) ) option_requires_argument("-verbose");
 118         iVerbose = option_integer_arg(argv[i]);
 119       }
 120
 121       else{
 122         usage(argv);
 123       }
 124     }
 125   }
 126
 127   if( rc==SQLITE_OK ){
 128     rc = sqlite3_expert_analyze(p, &zErr);
 129   }
 130
 131   if( rc==SQLITE_OK ){
 132     int nQuery = sqlite3_expert_count(p);
 133     if( iVerbose>0 ){
 134       const char *zCand = sqlite3_expert_report(p,0,EXPERT_REPORT_CANDIDATES);
 135       fprintf(stdout, "-- Candidates -------------------------------\n");
 136       fprintf(stdout, "%s\n", zCand);
 137     }
 138     for(i=0; i<nQuery; i++){
 139       const char *zSql = sqlite3_expert_report(p, i, EXPERT_REPORT_SQL);
 140       const char *zIdx = sqlite3_expert_report(p, i, EXPERT_REPORT_INDEXES);
 141       const char *zEQP = sqlite3_expert_report(p, i, EXPERT_REPORT_PLAN);
 142       if( zIdx==0 ) zIdx = "(no new indexes)\n";
 143       if( iVerbose>0 ){
 144         fprintf(stdout, "-- Query %d ----------------------------------\n",i+1);
 145         fprintf(stdout, "%s\n\n", zSql);
 146       }
 147       fprintf(stdout, "%s\n%s\n", zIdx, zEQP);
 148     }
 149   }else{
 150     fprintf(stderr, "Error: %s\n", zErr ? zErr : "?");
 151   }
 152
 153   sqlite3_expert_destroy(p);
 154   sqlite3_free(zErr);
 155   return rc;
 156 }