Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add header comments to the API functions in sqlite3expert.h. Include a list of all candidate indexes in the report output by the sqlite3_expert program. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | schemalint |
Files: | files | file ages | folders |
SHA3-256: |
0c45c5eb9f0f171b8d7c5f0d2973f9f5 |
User & Date: | dan 2017-04-11 17:43:12.842 |
Context
2017-04-11
| ||
18:29 | Fix a formatting issue in the output of the sqlite3_expert program. (check-in: cc8c358106 user: dan tags: schemalint) | |
17:43 | Add header comments to the API functions in sqlite3expert.h. Include a list of all candidate indexes in the report output by the sqlite3_expert program. (check-in: 0c45c5eb9f user: dan tags: schemalint) | |
2017-04-10
| ||
20:00 | Add ext/expert/README.md. (check-in: 9318f1b9ed user: dan tags: schemalint) | |
Changes
Changes to ext/expert/expert.c.
︙ | ︙ | |||
115 116 117 118 119 120 121 122 123 124 125 126 | if( rc==SQLITE_OK ){ rc = sqlite3_expert_analyze(p, &zErr); } if( rc==SQLITE_OK ){ int nQuery = sqlite3_expert_count(p); 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( iVerbose>0 ){ | > > > > > > | | 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 | if( rc==SQLITE_OK ){ rc = sqlite3_expert_analyze(p, &zErr); } if( rc==SQLITE_OK ){ int nQuery = sqlite3_expert_count(p); if( iVerbose>0 ){ const char *zCand = sqlite3_expert_report(p,0,EXPERT_REPORT_CANDIDATES); fprintf(stdout, "-- Candidates -------------------------------\n"); fprintf(stdout, "%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)"; if( iVerbose>0 ){ fprintf(stdout, "-- Query %d ----------------------------------\n",i+1); fprintf(stdout, "%s\n\n", zSql); } fprintf(stdout, "%s\n%s\n", zIdx, zEQP); } }else if( zErr ){ fprintf(stderr, "Error: %s\n", zErr); } |
︙ | ︙ |
Changes to ext/expert/expert1.test.
︙ | ︙ | |||
57 58 59 60 61 62 63 | proc do_rec_test {tn sql res} { set expert [sqlite3_expert_new db] $expert sql $sql $expert analyze set result [list] for {set i 0} {$i < [$expert count]} {incr i} { | | > > | 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | proc do_rec_test {tn sql res} { set expert [sqlite3_expert_new db] $expert sql $sql $expert analyze set result [list] for {set i 0} {$i < [$expert count]} {incr i} { set idx [string trim [$expert report $i indexes]] if {$idx==""} {set idx "(no new indexes)"} lappend result $idx lappend result [string trim [$expert report $i plan]] } $expert destroy set tst [subst -nocommands {set {} [squish [join {$result}]]}] uplevel [list do_test $tn $tst [string trim [squish $res]]] |
︙ | ︙ |
Changes to ext/expert/sqlite3expert.c.
︙ | ︙ | |||
130 131 132 133 134 135 136 | /* ** sqlite3expert object. */ struct sqlite3expert { sqlite3 *db; /* User database */ sqlite3 *dbm; /* In-memory db for this analysis */ | < < > > > | 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 | /* ** sqlite3expert object. */ struct sqlite3expert { sqlite3 *db; /* User database */ sqlite3 *dbm; /* In-memory db for this analysis */ IdxScan *pScan; /* List of scan objects */ IdxStatement *pStatement; /* List of IdxStatement objects */ int bRun; /* True once analysis has run */ char **pzErrmsg; int rc; /* Error code from whereinfo hook */ IdxHash hIdx; /* Hash containing all candidate indexes */ char *zCandidates; /* For EXPERT_REPORT_CANDIDATES */ }; /* ** Allocate and return nByte bytes of zeroed memory using sqlite3_malloc(). ** If the allocation fails, set *pRc to SQLITE_NOMEM and return NULL. */ |
︙ | ︙ | |||
944 945 946 947 948 949 950 | iSelectid, iOrder, iFrom, zDetail ); } for(pEntry=hIdx.pFirst; pEntry; pEntry=pEntry->pNext){ pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s;\n", pEntry->zKey); } | < < < | 945 946 947 948 949 950 951 952 953 954 955 956 957 958 | iSelectid, iOrder, iFrom, zDetail ); } for(pEntry=hIdx.pFirst; pEntry; pEntry=pEntry->pNext){ pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s;\n", pEntry->zKey); } idxFinalize(&rc, pExplain); } find_indexes_out: idxHashClear(&hIdx); return rc; |
︙ | ︙ | |||
1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 | } return rc; } int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr){ int rc; /* Create candidate indexes within the in-memory database file */ rc = idxCreateCandidates(p, pzErr); /* Figure out which of the candidate indexes are preferred by the query ** planner and report the results to the user. */ if( rc==SQLITE_OK ){ rc = idxFindIndexes(p, pzErr); } | > > > > > | 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 | } return rc; } int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr){ int rc; IdxHashEntry *pEntry; /* Create candidate indexes within the in-memory database file */ rc = idxCreateCandidates(p, pzErr); for(pEntry=p->hIdx.pFirst; pEntry; pEntry=pEntry->pNext){ p->zCandidates = idxAppendText(&rc, p->zCandidates, "%s;\n", pEntry->zVal); } /* Figure out which of the candidate indexes are preferred by the query ** planner and report the results to the user. */ if( rc==SQLITE_OK ){ rc = idxFindIndexes(p, pzErr); } |
︙ | ︙ | |||
1080 1081 1082 1083 1084 1085 1086 | */ const char *sqlite3_expert_report(sqlite3expert *p, int iStmt, int eReport){ const char *zRet = 0; IdxStatement *pStmt; if( p->bRun==0 ) return 0; for(pStmt=p->pStatement; pStmt && pStmt->iId!=iStmt; pStmt=pStmt->pNext); | < | | | | | | | | | | < > > > | 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 | */ const char *sqlite3_expert_report(sqlite3expert *p, int iStmt, int eReport){ const char *zRet = 0; IdxStatement *pStmt; if( p->bRun==0 ) return 0; for(pStmt=p->pStatement; pStmt && pStmt->iId!=iStmt; pStmt=pStmt->pNext); switch( eReport ){ case EXPERT_REPORT_SQL: if( pStmt ) zRet = pStmt->zSql; break; case EXPERT_REPORT_INDEXES: if( pStmt ) zRet = pStmt->zIdx; break; case EXPERT_REPORT_PLAN: if( pStmt ) zRet = pStmt->zEQP; break; case EXPERT_REPORT_CANDIDATES: zRet = p->zCandidates; break; } return zRet; } /* ** Free an sqlite3expert object. */ |
︙ | ︙ |
Changes to ext/expert/sqlite3expert.h.
︙ | ︙ | |||
14 15 16 17 18 19 20 21 22 23 24 | #include "sqlite3.h" typedef struct sqlite3expert sqlite3expert; /* ** Create a new sqlite3expert object. */ sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErr); /* | > > > > > > > > > > > > > > > > > > > | > > > > > | | > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > | 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 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 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 | #include "sqlite3.h" typedef struct sqlite3expert sqlite3expert; /* ** Create a new sqlite3expert object. ** ** If successful, a pointer to the new object is returned and (*pzErr) set ** to NULL. Or, if an error occurs, NULL is returned and (*pzErr) set to ** an English-language error message. In this case it is the responsibility ** of the caller to eventually free the error message buffer using ** sqlite3_free(). */ sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErr); /* ** Specify zero or more SQL statements to be included in the analysis. ** ** Buffer zSql must contain zero or more complete SQL statements. This ** function parses all statements contained in the buffer and adds them ** to the internal list of statements to analyze. If successful, SQLITE_OK ** is returned and (*pzErr) set to NULL. Or, if an error occurs - for example ** due to a error in the SQL - an SQLite error code is returned and (*pzErr) ** may be set to point to an English language error message. In this case ** the caller is responsible for eventually freeing the error message buffer ** using sqlite3_free(). ** ** If an error does occur while processing one of the statements in the ** buffer passed as the second argument, none of the statements in the ** buffer are added to the analysis. ** ** This function must be called before sqlite3_expert_analyze(). If a call ** to this function is made on an sqlite3expert object that has already ** been passed to sqlite3_expert_analyze() SQLITE_MISUSE is returned ** immediately and no statements are added to the analysis. */ int sqlite3_expert_sql( sqlite3expert *p, /* From a successful sqlite3_expert_new() */ const char *zSql, /* SQL statement(s) to add */ char **pzErr /* OUT: Error message (if any) */ ); /* ** This function is called after the sqlite3expert object has been configured ** with all SQL statements using sqlite3_expert_sql() to actually perform ** the analysis. Once this function has been called, it is not possible to ** add further SQL statements to the analysis. ** ** If successful, SQLITE_OK is returned and (*pzErr) is set to NULL. Or, if ** an error occurs, an SQLite error code is returned and (*pzErr) set to ** point to a buffer containing an English language error message. In this ** case it is the responsibility of the caller to eventually free the buffer ** using sqlite3_free(). ** ** If an error does occur within this function, the sqlite3expert object ** is no longer useful for any purpose. At that point it is no longer ** possible to add further SQL statements to the object or to re-attempt ** the analysis. The sqlite3expert object must still be freed using a call ** sqlite3_expert_destroy(). */ int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr); /* ** Return the total number of statements loaded using sqlite3_expert_sql(). ** The total number of SQL statements may be different from the total number ** to calls to sqlite3_expert_sql(). */ int sqlite3_expert_count(sqlite3expert*); /* ** Return a component of the report. ** ** This function is called after sqlite3_expert_analyze() to extract the ** results of the analysis. Each call to this function returns either a ** NULL pointer or a pointer to a buffer containing a nul-terminated string. ** The value passed as the third argument must be one of the EXPERT_REPORT_* ** #define constants defined below. ** ** For some EXPERT_REPORT_* parameters, the buffer returned contains ** information relating to a specific SQL statement. In these cases that ** SQL statement is identified by the value passed as the second argument. ** SQL statements are numbered from 0 in the order in which they are parsed. ** If an out-of-range value (less than zero or equal to or greater than the ** value returned by sqlite3_expert_count()) is passed as the second argument ** along with such an EXPERT_REPORT_* parameter, NULL is always returned. ** ** EXPERT_REPORT_SQL: ** Return the text of SQL statement iStmt. ** ** EXPERT_REPORT_INDEXES: ** Return a buffer containing the CREATE INDEX statements for all recommended ** indexes for statement iStmt. If there are no new recommeded indexes, NULL ** is returned. ** ** EXPERT_REPORT_PLAN: ** Return a buffer containing the EXPLAIN QUERY PLAN output for SQL query ** iStmt after the proposed indexes have been added to the database schema. ** ** EXPERT_REPORT_CANDIDATES: ** Return a pointer to a buffer containing the CREATE INDEX statements ** for all indexes that were tested (for all SQL statements). The iStmt ** parameter is ignored for EXPERT_REPORT_CANDIDATES calls. */ const char *sqlite3_expert_report(sqlite3expert*, int iStmt, int eReport); /* ** Values for the third argument passed to sqlite3_expert_report(). */ #define EXPERT_REPORT_SQL 1 #define EXPERT_REPORT_INDEXES 2 #define EXPERT_REPORT_PLAN 3 #define EXPERT_REPORT_CANDIDATES 4 /* ** Free an (sqlite3expert*) handle and all associated resources. There ** should be one call to this function for each successful call to ** sqlite3-expert_new(). */ void sqlite3_expert_destroy(sqlite3expert*); |