ext/misc/explain.c

   1 /*
   2 ** 2018-09-16
   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 ** This file demonstrates an eponymous virtual table that returns the
  14 ** EXPLAIN output from an SQL statement.
  15 **
  16 ** Usage example:
  17 **
  18 **     .load ./explain
  19 **     SELECT p2 FROM explain('SELECT * FROM sqlite_schema')
  20 **      WHERE opcode='OpenRead';
  21 **
  22 ** This module was originally written to help simplify SQLite testing,
  23 ** by providing an easier means of verifying certain patterns in the
  24 ** generated bytecode.
  25 */
  26 #if !defined(SQLITEINT_H)
  27 #include "sqlite3ext.h"
  28 #endif
  29 SQLITE_EXTENSION_INIT1
  30 #include <assert.h>
  31 #include <string.h>
  32
  33 #ifndef SQLITE_OMIT_VIRTUALTABLE
  34
  35 /* explain_vtab is a subclass of sqlite3_vtab which will
  36 ** serve as the underlying representation of a explain virtual table
  37 */
  38 typedef struct explain_vtab explain_vtab;
  39 struct explain_vtab {
  40   sqlite3_vtab base;  /* Base class - must be first */
  41   sqlite3 *db;        /* Database connection for this explain vtab */
  42 };
  43
  44 /* explain_cursor is a subclass of sqlite3_vtab_cursor which will
  45 ** serve as the underlying representation of a cursor that scans
  46 ** over rows of the result from an EXPLAIN operation.
  47 */
  48 typedef struct explain_cursor explain_cursor;
  49 struct explain_cursor {
  50   sqlite3_vtab_cursor base;  /* Base class - must be first */
  51   sqlite3 *db;               /* Database connection for this cursor */
  52   char *zSql;                /* Value for the EXPLN_COLUMN_SQL column */
  53   sqlite3_stmt *pExplain;    /* Statement being explained */
  54   int rc;                    /* Result of last sqlite3_step() on pExplain */
  55 };
  56
  57 /*
  58 ** The explainConnect() method is invoked to create a new
  59 ** explain_vtab that describes the explain virtual table.
  60 **
  61 ** Think of this routine as the constructor for explain_vtab objects.
  62 **
  63 ** All this routine needs to do is:
  64 **
  65 **    (1) Allocate the explain_vtab object and initialize all fields.
  66 **
  67 **    (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the
  68 **        result set of queries against explain will look like.
  69 */
  70 static int explainConnect(
  71   sqlite3 *db,
  72   void *pAux,
  73   int argc, const char *const*argv,
  74   sqlite3_vtab **ppVtab,
  75   char **pzErr
  76 ){
  77   explain_vtab *pNew;
  78   int rc;
  79
  80 /* Column numbers */
  81 #define EXPLN_COLUMN_ADDR     0   /* Instruction address */
  82 #define EXPLN_COLUMN_OPCODE   1   /* Opcode */
  83 #define EXPLN_COLUMN_P1       2   /* Operand 1 */
  84 #define EXPLN_COLUMN_P2       3   /* Operand 2 */
  85 #define EXPLN_COLUMN_P3       4   /* Operand 3 */
  86 #define EXPLN_COLUMN_P4       5   /* Operand 4 */
  87 #define EXPLN_COLUMN_P5       6   /* Operand 5 */
  88 #define EXPLN_COLUMN_COMMENT  7   /* Comment */
  89 #define EXPLN_COLUMN_SQL      8   /* SQL that is being explained */
  90
  91
  92   rc = sqlite3_declare_vtab(db,
  93      "CREATE TABLE x(addr,opcode,p1,p2,p3,p4,p5,comment,sql HIDDEN)");
  94   if( rc==SQLITE_OK ){
  95     pNew = sqlite3_malloc( sizeof(*pNew) );
  96     *ppVtab = (sqlite3_vtab*)pNew;
  97     if( pNew==0 ) return SQLITE_NOMEM;
  98     memset(pNew, 0, sizeof(*pNew));
  99     pNew->db = db;
 100   }
 101   return rc;
 102 }
 103
 104 /*
 105 ** This method is the destructor for explain_cursor objects.
 106 */
 107 static int explainDisconnect(sqlite3_vtab *pVtab){
 108   sqlite3_free(pVtab);
 109   return SQLITE_OK;
 110 }
 111
 112 /*
 113 ** Constructor for a new explain_cursor object.
 114 */
 115 static int explainOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
 116   explain_cursor *pCur;
 117   pCur = sqlite3_malloc( sizeof(*pCur) );
 118   if( pCur==0 ) return SQLITE_NOMEM;
 119   memset(pCur, 0, sizeof(*pCur));
 120   pCur->db = ((explain_vtab*)p)->db;
 121   *ppCursor = &pCur->base;
 122   return SQLITE_OK;
 123 }
 124
 125 /*
 126 ** Destructor for a explain_cursor.
 127 */
 128 static int explainClose(sqlite3_vtab_cursor *cur){
 129   explain_cursor *pCur = (explain_cursor*)cur;
 130   sqlite3_finalize(pCur->pExplain);
 131   sqlite3_free(pCur->zSql);
 132   sqlite3_free(pCur);
 133   return SQLITE_OK;
 134 }
 135
 136
 137 /*
 138 ** Advance a explain_cursor to its next row of output.
 139 */
 140 static int explainNext(sqlite3_vtab_cursor *cur){
 141   explain_cursor *pCur = (explain_cursor*)cur;
 142   pCur->rc = sqlite3_step(pCur->pExplain);
 143   if( pCur->rc!=SQLITE_DONE && pCur->rc!=SQLITE_ROW ) return pCur->rc;
 144   return SQLITE_OK;
 145 }
 146
 147 /*
 148 ** Return values of columns for the row at which the explain_cursor
 149 ** is currently pointing.
 150 */
 151 static int explainColumn(
 152   sqlite3_vtab_cursor *cur,   /* The cursor */
 153   sqlite3_context *ctx,       /* First argument to sqlite3_result_...() */
 154   int i                       /* Which column to return */
 155 ){
 156   explain_cursor *pCur = (explain_cursor*)cur;
 157   if( i==EXPLN_COLUMN_SQL ){
 158     sqlite3_result_text(ctx, pCur->zSql, -1, SQLITE_TRANSIENT);
 159   }else{
 160     sqlite3_result_value(ctx, sqlite3_column_value(pCur->pExplain, i));
 161   }
 162   return SQLITE_OK;
 163 }
 164
 165 /*
 166 ** Return the rowid for the current row.  In this implementation, the
 167 ** rowid is the same as the output value.
 168 */
 169 static int explainRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
 170   explain_cursor *pCur = (explain_cursor*)cur;
 171   *pRowid = sqlite3_column_int64(pCur->pExplain, 0);
 172   return SQLITE_OK;
 173 }
 174
 175 /*
 176 ** Return TRUE if the cursor has been moved off of the last
 177 ** row of output.
 178 */
 179 static int explainEof(sqlite3_vtab_cursor *cur){
 180   explain_cursor *pCur = (explain_cursor*)cur;
 181   return pCur->rc!=SQLITE_ROW;
 182 }
 183
 184 /*
 185 ** This method is called to "rewind" the explain_cursor object back
 186 ** to the first row of output.  This method is always called at least
 187 ** once prior to any call to explainColumn() or explainRowid() or
 188 ** explainEof().
 189 **
 190 ** The argv[0] is the SQL statement that is to be explained.
 191 */
 192 static int explainFilter(
 193   sqlite3_vtab_cursor *pVtabCursor,
 194   int idxNum, const char *idxStr,
 195   int argc, sqlite3_value **argv
 196 ){
 197   explain_cursor *pCur = (explain_cursor *)pVtabCursor;
 198   char *zSql = 0;
 199   int rc;
 200   sqlite3_finalize(pCur->pExplain);
 201   pCur->pExplain = 0;
 202   if( sqlite3_value_type(argv[0])!=SQLITE_TEXT ){
 203     pCur->rc = SQLITE_DONE;
 204     return SQLITE_OK;
 205   }
 206   sqlite3_free(pCur->zSql);
 207   pCur->zSql = sqlite3_mprintf("%s", sqlite3_value_text(argv[0]));
 208   if( pCur->zSql ){
 209     zSql = sqlite3_mprintf("EXPLAIN %s", pCur->zSql);
 210   }
 211   if( zSql==0 ){
 212     rc = SQLITE_NOMEM;
 213   }else{
 214     rc = sqlite3_prepare_v2(pCur->db, zSql, -1, &pCur->pExplain, 0);
 215     sqlite3_free(zSql);
 216   }
 217   if( rc ){
 218     sqlite3_finalize(pCur->pExplain);
 219     pCur->pExplain = 0;
 220     sqlite3_free(pCur->zSql);
 221     pCur->zSql = 0;
 222   }else{
 223     pCur->rc = sqlite3_step(pCur->pExplain);
 224     rc = (pCur->rc==SQLITE_DONE || pCur->rc==SQLITE_ROW) ? SQLITE_OK : pCur->rc;
 225   }
 226   return rc;
 227 }
 228
 229 /*
 230 ** SQLite will invoke this method one or more times while planning a query
 231 ** that uses the explain virtual table.  This routine needs to create
 232 ** a query plan for each invocation and compute an estimated cost for that
 233 ** plan.
 234 */
 235 static int explainBestIndex(
 236   sqlite3_vtab *tab,
 237   sqlite3_index_info *pIdxInfo
 238 ){
 239   int i;                  /* Loop counter */
 240   int idx = -1;           /* Index of a usable == constraint against SQL */
 241   int unusable = 0;       /* True if there are unusable constraints on SQL */
 242
 243   pIdxInfo->estimatedRows = 500;
 244   for(i=0; i<pIdxInfo->nConstraint; i++){
 245     struct sqlite3_index_constraint *p = &pIdxInfo->aConstraint[i];
 246     if( p->iColumn!=EXPLN_COLUMN_SQL ) continue;
 247     if( !p->usable ){
 248       unusable = 1;
 249     }else if( p->op==SQLITE_INDEX_CONSTRAINT_EQ ){
 250       idx = i;
 251     }
 252   }
 253   if( idx>=0 ){
 254     /* There exists a usable == constraint against the SQL column */
 255     pIdxInfo->estimatedCost = 10.0;
 256     pIdxInfo->idxNum = 1;
 257     pIdxInfo->aConstraintUsage[idx].argvIndex = 1;
 258     pIdxInfo->aConstraintUsage[idx].omit = 1;
 259   }else if( unusable ){
 260     /* There are unusable constraints against the SQL column.  Do not allow
 261     ** this plan to continue forward. */
 262     return SQLITE_CONSTRAINT;
 263   }
 264   return SQLITE_OK;
 265 }
 266
 267 /*
 268 ** This following structure defines all the methods for the
 269 ** explain virtual table.
 270 */
 271 static sqlite3_module explainModule = {
 272   0,                         /* iVersion */
 273   0,                         /* xCreate */
 274   explainConnect,            /* xConnect */
 275   explainBestIndex,          /* xBestIndex */
 276   explainDisconnect,         /* xDisconnect */
 277   0,                         /* xDestroy */
 278   explainOpen,               /* xOpen - open a cursor */
 279   explainClose,              /* xClose - close a cursor */
 280   explainFilter,             /* xFilter - configure scan constraints */
 281   explainNext,               /* xNext - advance a cursor */
 282   explainEof,                /* xEof - check for end of scan */
 283   explainColumn,             /* xColumn - read data */
 284   explainRowid,              /* xRowid - read data */
 285   0,                         /* xUpdate */
 286   0,                         /* xBegin */
 287   0,                         /* xSync */
 288   0,                         /* xCommit */
 289   0,                         /* xRollback */
 290   0,                         /* xFindMethod */
 291   0,                         /* xRename */
 292   0,                         /* xSavepoint */
 293   0,                         /* xRelease */
 294   0,                         /* xRollbackTo */
 295   0,                         /* xShadowName */
 296   0                          /* xIntegrity */
 297 };
 298
 299 #endif /* SQLITE_OMIT_VIRTUALTABLE */
 300
 301 int sqlite3ExplainVtabInit(sqlite3 *db){
 302   int rc = SQLITE_OK;
 303 #ifndef SQLITE_OMIT_VIRTUALTABLE
 304   rc = sqlite3_create_module(db, "explain", &explainModule, 0);
 305 #endif
 306   return rc;
 307 }
 308
 309 #ifdef _WIN32
 310 __declspec(dllexport)
 311 #endif
 312 int sqlite3_explain_init(
 313   sqlite3 *db,
 314   char **pzErrMsg,
 315   const sqlite3_api_routines *pApi
 316 ){
 317   int rc = SQLITE_OK;
 318   SQLITE_EXTENSION_INIT2(pApi);
 319 #ifndef SQLITE_OMIT_VIRTUALTABLE
 320   rc = sqlite3ExplainVtabInit(db);
 321 #endif
 322   return rc;
 323 }