ext/expert/sqlite3expert.h

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