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