Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes to ext/expert/expert.c.

   115    115   
   116    116     if( rc==SQLITE_OK ){
   117    117       rc = sqlite3_expert_analyze(p, &zErr);
   118    118     }
   119    119   
   120    120     if( rc==SQLITE_OK ){
   121    121       int nQuery = sqlite3_expert_count(p);
          122  +    if( iVerbose>0 ){
          123  +      const char *zCand = sqlite3_expert_report(p,0,EXPERT_REPORT_CANDIDATES);
          124  +      fprintf(stdout, "-- Candidates -------------------------------\n");
          125  +      fprintf(stdout, "%s\n", zCand);
          126  +    }
   122    127       for(i=0; i<nQuery; i++){
   123    128         const char *zSql = sqlite3_expert_report(p, i, EXPERT_REPORT_SQL);
   124    129         const char *zIdx = sqlite3_expert_report(p, i, EXPERT_REPORT_INDEXES);
   125    130         const char *zEQP = sqlite3_expert_report(p, i, EXPERT_REPORT_PLAN);
          131  +      if( zIdx==0 ) zIdx = "(no new indexes)";
   126    132         if( iVerbose>0 ){
   127         -        fprintf(stdout, "-- query %d ----------------------------------\n",i+1);
          133  +        fprintf(stdout, "-- Query %d ----------------------------------\n",i+1);
   128    134           fprintf(stdout, "%s\n\n", zSql);
   129    135         }
   130    136         fprintf(stdout, "%s\n%s\n", zIdx, zEQP);
   131    137       }
   132    138     }else if( zErr ){
   133    139       fprintf(stderr, "Error: %s\n", zErr);
   134    140     }

Changes to ext/expert/expert1.test.

    57     57       proc do_rec_test {tn sql res} {
    58     58         set expert [sqlite3_expert_new db]
    59     59         $expert sql $sql
    60     60         $expert analyze
    61     61   
    62     62         set result [list]
    63     63         for {set i 0} {$i < [$expert count]} {incr i} {
    64         -        lappend result [string trim [$expert report $i indexes]]
           64  +        set idx [string trim [$expert report $i indexes]]
           65  +        if {$idx==""} {set idx "(no new indexes)"}
           66  +        lappend result $idx
    65     67           lappend result [string trim [$expert report $i plan]]
    66     68         }
    67     69   
    68     70         $expert destroy
    69     71   
    70     72         set tst [subst -nocommands {set {} [squish [join {$result}]]}]
    71     73         uplevel [list do_test $tn $tst [string trim [squish $res]]]

Changes to ext/expert/sqlite3expert.c.

   130    130   
   131    131   /*
   132    132   ** sqlite3expert object.
   133    133   */
   134    134   struct sqlite3expert {
   135    135     sqlite3 *db;                    /* User database */
   136    136     sqlite3 *dbm;                   /* In-memory db for this analysis */
   137         -  int bRun;                       /* True once analysis has run */
   138         -  char **pzErrmsg;
   139    137     IdxScan *pScan;                 /* List of scan objects */
   140    138     IdxStatement *pStatement;       /* List of IdxStatement objects */
          139  +  int bRun;                       /* True once analysis has run */
          140  +  char **pzErrmsg;
   141    141     int rc;                         /* Error code from whereinfo hook */
   142    142     IdxHash hIdx;                   /* Hash containing all candidate indexes */
          143  +  char *zCandidates;              /* For EXPERT_REPORT_CANDIDATES */
   143    144   };
   144    145   
   145    146   
   146    147   /*
   147    148   ** Allocate and return nByte bytes of zeroed memory using sqlite3_malloc(). 
   148    149   ** If the allocation fails, set *pRc to SQLITE_NOMEM and return NULL.
   149    150   */
................................................................................
   944    945             iSelectid, iOrder, iFrom, zDetail
   945    946         );
   946    947       }
   947    948   
   948    949       for(pEntry=hIdx.pFirst; pEntry; pEntry=pEntry->pNext){
   949    950         pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s;\n", pEntry->zKey);
   950    951       }
   951         -    if( pStmt->zIdx==0 ){
   952         -      pStmt->zIdx = idxAppendText(&rc, 0, "(no new indexes)\n");
   953         -    }
   954    952   
   955    953       idxFinalize(&rc, pExplain);
   956    954     }
   957    955   
   958    956    find_indexes_out:
   959    957     idxHashClear(&hIdx);
   960    958     return rc;
................................................................................
  1045   1043     }
  1046   1044   
  1047   1045     return rc;
  1048   1046   }
  1049   1047   
  1050   1048   int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr){
  1051   1049     int rc;
         1050  +  IdxHashEntry *pEntry;
  1052   1051   
  1053   1052     /* Create candidate indexes within the in-memory database file */
  1054   1053     rc = idxCreateCandidates(p, pzErr);
         1054  +
         1055  +  for(pEntry=p->hIdx.pFirst; pEntry; pEntry=pEntry->pNext){
         1056  +    p->zCandidates = idxAppendText(&rc, p->zCandidates, "%s;\n", pEntry->zVal);
         1057  +  }
  1055   1058   
  1056   1059     /* Figure out which of the candidate indexes are preferred by the query
  1057   1060     ** planner and report the results to the user.  */
  1058   1061     if( rc==SQLITE_OK ){
  1059   1062       rc = idxFindIndexes(p, pzErr);
  1060   1063     }
  1061   1064   
................................................................................
  1080   1083   */
  1081   1084   const char *sqlite3_expert_report(sqlite3expert *p, int iStmt, int eReport){
  1082   1085     const char *zRet = 0;
  1083   1086     IdxStatement *pStmt;
  1084   1087   
  1085   1088     if( p->bRun==0 ) return 0;
  1086   1089     for(pStmt=p->pStatement; pStmt && pStmt->iId!=iStmt; pStmt=pStmt->pNext);
  1087         -  if( pStmt ){
  1088         -    switch( eReport ){
  1089         -      case EXPERT_REPORT_SQL:
  1090         -        zRet = pStmt->zSql;
  1091         -        break;
  1092         -      case EXPERT_REPORT_INDEXES:
  1093         -        zRet = pStmt->zIdx;
  1094         -        break;
  1095         -      case EXPERT_REPORT_PLAN:
  1096         -        zRet = pStmt->zEQP;
  1097         -        break;
  1098         -    }
         1090  +  switch( eReport ){
         1091  +    case EXPERT_REPORT_SQL:
         1092  +      if( pStmt ) zRet = pStmt->zSql;
         1093  +      break;
         1094  +    case EXPERT_REPORT_INDEXES:
         1095  +      if( pStmt ) zRet = pStmt->zIdx;
         1096  +      break;
         1097  +    case EXPERT_REPORT_PLAN:
         1098  +      if( pStmt ) zRet = pStmt->zEQP;
         1099  +      break;
         1100  +    case EXPERT_REPORT_CANDIDATES:
         1101  +      zRet = p->zCandidates;
         1102  +      break;
  1099   1103     }
  1100   1104     return zRet;
  1101   1105   }
  1102   1106   
  1103   1107   /*
  1104   1108   ** Free an sqlite3expert object.
  1105   1109   */

Changes to ext/expert/sqlite3expert.h.

    14     14   
    15     15   #include "sqlite3.h"
    16     16   
    17     17   typedef struct sqlite3expert sqlite3expert;
    18     18   
    19     19   /*
    20     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().
    21     27   */
    22     28   sqlite3expert *sqlite3_expert_new(sqlite3 *db, char **pzErr);
    23     29   
    24     30   /*
    25         -** Add an SQL statement to the analysis.
           31  +** Specify zero or more SQL statements to be included in the analysis.
           32  +**
           33  +** Buffer zSql must contain zero or more complete SQL statements. This
           34  +** function parses all statements contained in the buffer and adds them
           35  +** to the internal list of statements to analyze. If successful, SQLITE_OK
           36  +** is returned and (*pzErr) set to NULL. Or, if an error occurs - for example
           37  +** due to a error in the SQL - an SQLite error code is returned and (*pzErr)
           38  +** may be set to point to an English language error message. In this case
           39  +** the caller is responsible for eventually freeing the error message buffer
           40  +** using sqlite3_free().
           41  +**
           42  +** If an error does occur while processing one of the statements in the
           43  +** buffer passed as the second argument, none of the statements in the
           44  +** buffer are added to the analysis.
           45  +**
           46  +** This function must be called before sqlite3_expert_analyze(). If a call
           47  +** to this function is made on an sqlite3expert object that has already
           48  +** been passed to sqlite3_expert_analyze() SQLITE_MISUSE is returned
           49  +** immediately and no statements are added to the analysis.
    26     50   */
    27     51   int sqlite3_expert_sql(
    28         -  sqlite3expert *p,               /* From sqlite3_expert_new() */
    29         -  const char *zSql,               /* SQL statement to add */
           52  +  sqlite3expert *p,               /* From a successful sqlite3_expert_new() */
           53  +  const char *zSql,               /* SQL statement(s) to add */
    30     54     char **pzErr                    /* OUT: Error message (if any) */
    31     55   );
    32     56   
           57  +/*
           58  +** This function is called after the sqlite3expert object has been configured
           59  +** with all SQL statements using sqlite3_expert_sql() to actually perform
           60  +** the analysis. Once this function has been called, it is not possible to
           61  +** add further SQL statements to the analysis.
           62  +**
           63  +** If successful, SQLITE_OK is returned and (*pzErr) is set to NULL. Or, if
           64  +** an error occurs, an SQLite error code is returned and (*pzErr) set to 
           65  +** point to a buffer containing an English language error message. In this
           66  +** case it is the responsibility of the caller to eventually free the buffer
           67  +** using sqlite3_free().
           68  +**
           69  +** If an error does occur within this function, the sqlite3expert object
           70  +** is no longer useful for any purpose. At that point it is no longer
           71  +** possible to add further SQL statements to the object or to re-attempt
           72  +** the analysis. The sqlite3expert object must still be freed using a call
           73  +** sqlite3_expert_destroy().
           74  +*/
    33     75   int sqlite3_expert_analyze(sqlite3expert *p, char **pzErr);
    34     76   
    35     77   /*
    36         -** Return the total number of SQL queries loaded via sqlite3_expert_sql().
           78  +** Return the total number of statements loaded using sqlite3_expert_sql().
           79  +** The total number of SQL statements may be different from the total number
           80  +** to calls to sqlite3_expert_sql().
    37     81   */
    38     82   int sqlite3_expert_count(sqlite3expert*);
    39     83   
    40     84   /*
    41     85   ** Return a component of the report.
           86  +**
           87  +** This function is called after sqlite3_expert_analyze() to extract the
           88  +** results of the analysis. Each call to this function returns either a
           89  +** NULL pointer or a pointer to a buffer containing a nul-terminated string.
           90  +** The value passed as the third argument must be one of the EXPERT_REPORT_*
           91  +** #define constants defined below.
           92  +**
           93  +** For some EXPERT_REPORT_* parameters, the buffer returned contains 
           94  +** information relating to a specific SQL statement. In these cases that
           95  +** SQL statement is identified by the value passed as the second argument.
           96  +** SQL statements are numbered from 0 in the order in which they are parsed.
           97  +** If an out-of-range value (less than zero or equal to or greater than the
           98  +** value returned by sqlite3_expert_count()) is passed as the second argument
           99  +** along with such an EXPERT_REPORT_* parameter, NULL is always returned.
          100  +**
          101  +** EXPERT_REPORT_SQL:
          102  +**   Return the text of SQL statement iStmt.
          103  +**
          104  +** EXPERT_REPORT_INDEXES:
          105  +**   Return a buffer containing the CREATE INDEX statements for all recommended
          106  +**   indexes for statement iStmt. If there are no new recommeded indexes, NULL 
          107  +**   is returned.
          108  +**
          109  +** EXPERT_REPORT_PLAN:
          110  +**   Return a buffer containing the EXPLAIN QUERY PLAN output for SQL query
          111  +**   iStmt after the proposed indexes have been added to the database schema.
          112  +**
          113  +** EXPERT_REPORT_CANDIDATES:
          114  +**   Return a pointer to a buffer containing the CREATE INDEX statements 
          115  +**   for all indexes that were tested (for all SQL statements). The iStmt
          116  +**   parameter is ignored for EXPERT_REPORT_CANDIDATES calls.
    42    117   */
    43    118   const char *sqlite3_expert_report(sqlite3expert*, int iStmt, int eReport);
    44    119   
    45    120   /*
    46    121   ** Values for the third argument passed to sqlite3_expert_report().
    47    122   */
    48    123   #define EXPERT_REPORT_SQL        1
    49    124   #define EXPERT_REPORT_INDEXES    2
    50    125   #define EXPERT_REPORT_PLAN       3
          126  +#define EXPERT_REPORT_CANDIDATES 4
    51    127   
    52    128   /*
    53         -** Free an (sqlite3expert*) handle allocated by sqlite3-expert_new().
          129  +** Free an (sqlite3expert*) handle and all associated resources. There 
          130  +** should be one call to this function for each successful call to 
          131  +** sqlite3-expert_new().
    54    132   */
    55    133   void sqlite3_expert_destroy(sqlite3expert*);
    56    134   
    57    135