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 #if !defined(SQLITEEXPERT_H) 14 #define SQLITEEXPERT_H 1 15 #include "sqlite3.h" 16 17 typedef struct sqlite3expert sqlite3expert; 18 19 /* 20 ** Create a new sqlite3expert object. 21 ** 22 ** If successful, a pointer to the new object is returned and (*pzErr) set 23 ** to NULL. Or, if an error occurs, NULL is returned and (*pzErr) set to 24 ** an English-language error message. In this case it is the responsibility 25 ** of the caller to eventually free the error message buffer using 26 ** sqlite3_free(). 27 */ 28 sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErr); 29 30 /* 31 ** Configure an sqlite3expert object. 32 ** 33 ** EXPERT_CONFIG_SAMPLE: 34 ** By default, sqlite3_expert_analyze() generates sqlite_stat1 data for 35 ** each candidate index. This involves scanning and sorting the entire 36 ** contents of each user database table once for each candidate index 37 ** associated with the table. For large databases, this can be 38 ** prohibitively slow. This option allows the sqlite3expert object to 39 ** be configured so that sqlite_stat1 data is instead generated based on a 40 ** subset of each table, or so that no sqlite_stat1 data is used at all. 41 ** 42 ** A single integer argument is passed to this option. If the value is less 43 ** than or equal to zero, then no sqlite_stat1 data is generated or used by 44 ** the analysis - indexes are recommended based on the database schema only. 45 ** Or, if the value is 100 or greater, complete sqlite_stat1 data is 46 ** generated for each candidate index (this is the default). Finally, if the 47 ** value falls between 0 and 100, then it represents the percentage of user 48 ** table rows that should be considered when generating sqlite_stat1 data. 49 ** 50 ** Examples: 51 ** 52 ** // Do not generate any sqlite_stat1 data 53 ** sqlite3_expert_config(pExpert, EXPERT_CONFIG_SAMPLE, 0); 54 ** 55 ** // Generate sqlite_stat1 data based on 10% of the rows in each table. 56 ** sqlite3_expert_config(pExpert, EXPERT_CONFIG_SAMPLE, 10); 57 */ 58 int sqlite3_expert_config(sqlite3expert *p, int op, ...); 59 60 #define EXPERT_CONFIG_SAMPLE 1 /* int */ 61 62 /* 63 ** Specify zero or more SQL statements to be included in the analysis. 64 ** 65 ** Buffer zSql must contain zero or more complete SQL statements. This 66 ** function parses all statements contained in the buffer and adds them 67 ** to the internal list of statements to analyze. If successful, SQLITE_OK 68 ** is returned and (*pzErr) set to NULL. Or, if an error occurs - for example 69 ** due to a error in the SQL - an SQLite error code is returned and (*pzErr) 70 ** may be set to point to an English language error message. In this case 71 ** the caller is responsible for eventually freeing the error message buffer 72 ** using sqlite3_free(). 73 ** 74 ** If an error does occur while processing one of the statements in the 75 ** buffer passed as the second argument, none of the statements in the 76 ** buffer are added to the analysis. 77 ** 78 ** This function must be called before sqlite3_expert_analyze(). If a call 79 ** to this function is made on an sqlite3expert object that has already 80 ** been passed to sqlite3_expert_analyze() SQLITE_MISUSE is returned 81 ** immediately and no statements are added to the analysis. 82 */ 83 int sqlite3_expert_sql( 84 sqlite3expert *p, /* From a successful sqlite3_expert_new() */ 85 const char *zSql, /* SQL statement(s) to add */ 86 char **pzErr /* OUT: Error message (if any) */ 87 ); 88 89 90 /* 91 ** This function is called after the sqlite3expert object has been configured 92 ** with all SQL statements using sqlite3_expert_sql() to actually perform 93 ** the analysis. Once this function has been called, it is not possible to 94 ** add further SQL statements to the analysis. 95 ** 96 ** If successful, SQLITE_OK is returned and (*pzErr) is set to NULL. Or, if 97 ** an error occurs, an SQLite error code is returned and (*pzErr) set to 98 ** point to a buffer containing an English language error message. In this 99 ** case it is the responsibility of the caller to eventually free the buffer 100 ** using sqlite3_free(). 101 ** 102 ** If an error does occur within this function, the sqlite3expert object 103 ** is no longer useful for any purpose. At that point it is no longer 104 ** possible to add further SQL statements to the object or to re-attempt 105 ** the analysis. The sqlite3expert object must still be freed using a call 106 ** sqlite3_expert_destroy(). 107 */ 108 int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr); 109 110 /* 111 ** Return the total number of statements loaded using sqlite3_expert_sql(). 112 ** The total number of SQL statements may be different from the total number 113 ** to calls to sqlite3_expert_sql(). 114 */ 115 int sqlite3_expert_count(sqlite3expert*); 116 117 /* 118 ** Return a component of the report. 119 ** 120 ** This function is called after sqlite3_expert_analyze() to extract the 121 ** results of the analysis. Each call to this function returns either a 122 ** NULL pointer or a pointer to a buffer containing a nul-terminated string. 123 ** The value passed as the third argument must be one of the EXPERT_REPORT_* 124 ** #define constants defined below. 125 ** 126 ** For some EXPERT_REPORT_* parameters, the buffer returned contains 127 ** information relating to a specific SQL statement. In these cases that 128 ** SQL statement is identified by the value passed as the second argument. 129 ** SQL statements are numbered from 0 in the order in which they are parsed. 130 ** If an out-of-range value (less than zero or equal to or greater than the 131 ** value returned by sqlite3_expert_count()) is passed as the second argument 132 ** along with such an EXPERT_REPORT_* parameter, NULL is always returned. 133 ** 134 ** EXPERT_REPORT_SQL: 135 ** Return the text of SQL statement iStmt. 136 ** 137 ** EXPERT_REPORT_INDEXES: 138 ** Return a buffer containing the CREATE INDEX statements for all recommended 139 ** indexes for statement iStmt. If there are no new recommeded indexes, NULL 140 ** is returned. 141 ** 142 ** EXPERT_REPORT_PLAN: 143 ** Return a buffer containing the EXPLAIN QUERY PLAN output for SQL query 144 ** iStmt after the proposed indexes have been added to the database schema. 145 ** 146 ** EXPERT_REPORT_CANDIDATES: 147 ** Return a pointer to a buffer containing the CREATE INDEX statements 148 ** for all indexes that were tested (for all SQL statements). The iStmt 149 ** parameter is ignored for EXPERT_REPORT_CANDIDATES calls. 150 */ 151 const char *sqlite3_expert_report(sqlite3expert*, int iStmt, int eReport); 152 153 /* 154 ** Values for the third argument passed to sqlite3_expert_report(). 155 */ 156 #define EXPERT_REPORT_SQL 1 157 #define EXPERT_REPORT_INDEXES 2 158 #define EXPERT_REPORT_PLAN 3 159 #define EXPERT_REPORT_CANDIDATES 4 160 161 /* 162 ** Free an (sqlite3expert*) handle and all associated resources. There 163 ** should be one call to this function for each successful call to 164 ** sqlite3-expert_new(). 165 */ 166 void sqlite3_expert_destroy(sqlite3expert*); 167 168 #endif /* !defined(SQLITEEXPERT_H) */ 169