| 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) */ |