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 } 157