ext/misc/stmt.c

   1 /*
   2 ** 2017-05-31
   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 information
  14 ** about all prepared statements for the database connection.
  15 **
  16 ** Usage example:
  17 **
  18 **     .load ./stmt
  19 **     .mode line
  20 **     .header on
  21 **     SELECT * FROM stmt;
  22 */
  23 #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_STMTVTAB)
  24 #if !defined(SQLITEINT_H)
  25 #include "sqlite3ext.h"
  26 #endif
  27 SQLITE_EXTENSION_INIT1
  28 #include <assert.h>
  29 #include <string.h>
  30
  31 #ifndef SQLITE_OMIT_VIRTUALTABLE
  32
  33 /* stmt_vtab is a subclass of sqlite3_vtab which will
  34 ** serve as the underlying representation of a stmt virtual table
  35 */
  36 typedef struct stmt_vtab stmt_vtab;
  37 struct stmt_vtab {
  38   sqlite3_vtab base;  /* Base class - must be first */
  39   sqlite3 *db;        /* Database connection for this stmt vtab */
  40 };
  41
  42 /* stmt_cursor is a subclass of sqlite3_vtab_cursor which will
  43 ** serve as the underlying representation of a cursor that scans
  44 ** over rows of the result
  45 */
  46 typedef struct stmt_cursor stmt_cursor;
  47 struct stmt_cursor {
  48   sqlite3_vtab_cursor base;  /* Base class - must be first */
  49   sqlite3 *db;               /* Database connection for this cursor */
  50   sqlite3_stmt *pStmt;       /* Statement cursor is currently pointing at */
  51   sqlite3_int64 iRowid;      /* The rowid */
  52 };
  53
  54 /*
  55 ** The stmtConnect() method is invoked to create a new
  56 ** stmt_vtab that describes the stmt virtual table.
  57 **
  58 ** Think of this routine as the constructor for stmt_vtab objects.
  59 **
  60 ** All this routine needs to do is:
  61 **
  62 **    (1) Allocate the stmt_vtab object and initialize all fields.
  63 **
  64 **    (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the
  65 **        result set of queries against stmt will look like.
  66 */
  67 static int stmtConnect(
  68   sqlite3 *db,
  69   void *pAux,
  70   int argc, const char *const*argv,
  71   sqlite3_vtab **ppVtab,
  72   char **pzErr
  73 ){
  74   stmt_vtab *pNew;
  75   int rc;
  76
  77 /* Column numbers */
  78 #define STMT_COLUMN_SQL     0   /* SQL for the statement */
  79 #define STMT_COLUMN_NCOL    1   /* Number of result columns */
  80 #define STMT_COLUMN_RO      2   /* True if read-only */
  81 #define STMT_COLUMN_BUSY    3   /* True if currently busy */
  82 #define STMT_COLUMN_NSCAN   4   /* SQLITE_STMTSTATUS_FULLSCAN_STEP */
  83 #define STMT_COLUMN_NSORT   5   /* SQLITE_STMTSTATUS_SORT */
  84 #define STMT_COLUMN_NAIDX   6   /* SQLITE_STMTSTATUS_AUTOINDEX */
  85 #define STMT_COLUMN_NSTEP   7   /* SQLITE_STMTSTATUS_VM_STEP */
  86 #define STMT_COLUMN_REPREP  8   /* SQLITE_STMTSTATUS_REPREPARE */
  87 #define STMT_COLUMN_RUN     9   /* SQLITE_STMTSTATUS_RUN */
  88 #define STMT_COLUMN_MEM    10   /* SQLITE_STMTSTATUS_MEMUSED */
  89
  90
  91   rc = sqlite3_declare_vtab(db,
  92      "CREATE TABLE x(sql,ncol,ro,busy,nscan,nsort,naidx,nstep,"
  93                     "reprep,run,mem)");
  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 stmt_cursor objects.
 106 */
 107 static int stmtDisconnect(sqlite3_vtab *pVtab){
 108   sqlite3_free(pVtab);
 109   return SQLITE_OK;
 110 }
 111
 112 /*
 113 ** Constructor for a new stmt_cursor object.
 114 */
 115 static int stmtOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
 116   stmt_cursor *pCur;
 117   pCur = sqlite3_malloc( sizeof(*pCur) );
 118   if( pCur==0 ) return SQLITE_NOMEM;
 119   memset(pCur, 0, sizeof(*pCur));
 120   pCur->db = ((stmt_vtab*)p)->db;
 121   *ppCursor = &pCur->base;
 122   return SQLITE_OK;
 123 }
 124
 125 /*
 126 ** Destructor for a stmt_cursor.
 127 */
 128 static int stmtClose(sqlite3_vtab_cursor *cur){
 129   sqlite3_free(cur);
 130   return SQLITE_OK;
 131 }
 132
 133
 134 /*
 135 ** Advance a stmt_cursor to its next row of output.
 136 */
 137 static int stmtNext(sqlite3_vtab_cursor *cur){
 138   stmt_cursor *pCur = (stmt_cursor*)cur;
 139   pCur->iRowid++;
 140   pCur->pStmt = sqlite3_next_stmt(pCur->db, pCur->pStmt);
 141   return SQLITE_OK;
 142 }
 143
 144 /*
 145 ** Return values of columns for the row at which the stmt_cursor
 146 ** is currently pointing.
 147 */
 148 static int stmtColumn(
 149   sqlite3_vtab_cursor *cur,   /* The cursor */
 150   sqlite3_context *ctx,       /* First argument to sqlite3_result_...() */
 151   int i                       /* Which column to return */
 152 ){
 153   stmt_cursor *pCur = (stmt_cursor*)cur;
 154   switch( i ){
 155     case STMT_COLUMN_SQL: {
 156       sqlite3_result_text(ctx, sqlite3_sql(pCur->pStmt), -1, SQLITE_TRANSIENT);
 157       break;
 158     }
 159     case STMT_COLUMN_NCOL: {
 160       sqlite3_result_int(ctx, sqlite3_column_count(pCur->pStmt));
 161       break;
 162     }
 163     case STMT_COLUMN_RO: {
 164       sqlite3_result_int(ctx, sqlite3_stmt_readonly(pCur->pStmt));
 165       break;
 166     }
 167     case STMT_COLUMN_BUSY: {
 168       sqlite3_result_int(ctx, sqlite3_stmt_busy(pCur->pStmt));
 169       break;
 170     }
 171     case STMT_COLUMN_MEM: {
 172       i = SQLITE_STMTSTATUS_MEMUSED +
 173             STMT_COLUMN_NSCAN - SQLITE_STMTSTATUS_FULLSCAN_STEP;
 174       /* Fall thru */
 175     }
 176     case STMT_COLUMN_NSCAN:
 177     case STMT_COLUMN_NSORT:
 178     case STMT_COLUMN_NAIDX:
 179     case STMT_COLUMN_NSTEP:
 180     case STMT_COLUMN_REPREP:
 181     case STMT_COLUMN_RUN: {
 182       sqlite3_result_int(ctx, sqlite3_stmt_status(pCur->pStmt,
 183                       i-STMT_COLUMN_NSCAN+SQLITE_STMTSTATUS_FULLSCAN_STEP, 0));
 184       break;
 185     }
 186   }
 187   return SQLITE_OK;
 188 }
 189
 190 /*
 191 ** Return the rowid for the current row.  In this implementation, the
 192 ** rowid is the same as the output value.
 193 */
 194 static int stmtRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
 195   stmt_cursor *pCur = (stmt_cursor*)cur;
 196   *pRowid = pCur->iRowid;
 197   return SQLITE_OK;
 198 }
 199
 200 /*
 201 ** Return TRUE if the cursor has been moved off of the last
 202 ** row of output.
 203 */
 204 static int stmtEof(sqlite3_vtab_cursor *cur){
 205   stmt_cursor *pCur = (stmt_cursor*)cur;
 206   return pCur->pStmt==0;
 207 }
 208
 209 /*
 210 ** This method is called to "rewind" the stmt_cursor object back
 211 ** to the first row of output.  This method is always called at least
 212 ** once prior to any call to stmtColumn() or stmtRowid() or
 213 ** stmtEof().
 214 */
 215 static int stmtFilter(
 216   sqlite3_vtab_cursor *pVtabCursor,
 217   int idxNum, const char *idxStr,
 218   int argc, sqlite3_value **argv
 219 ){
 220   stmt_cursor *pCur = (stmt_cursor *)pVtabCursor;
 221   pCur->pStmt = 0;
 222   pCur->iRowid = 0;
 223   return stmtNext(pVtabCursor);
 224 }
 225
 226 /*
 227 ** SQLite will invoke this method one or more times while planning a query
 228 ** that uses the stmt virtual table.  This routine needs to create
 229 ** a query plan for each invocation and compute an estimated cost for that
 230 ** plan.
 231 */
 232 static int stmtBestIndex(
 233   sqlite3_vtab *tab,
 234   sqlite3_index_info *pIdxInfo
 235 ){
 236   pIdxInfo->estimatedCost = (double)500;
 237   pIdxInfo->estimatedRows = 500;
 238   return SQLITE_OK;
 239 }
 240
 241 /*
 242 ** This following structure defines all the methods for the
 243 ** stmt virtual table.
 244 */
 245 static sqlite3_module stmtModule = {
 246   0,                         /* iVersion */
 247   0,                         /* xCreate */
 248   stmtConnect,               /* xConnect */
 249   stmtBestIndex,             /* xBestIndex */
 250   stmtDisconnect,            /* xDisconnect */
 251   0,                         /* xDestroy */
 252   stmtOpen,                  /* xOpen - open a cursor */
 253   stmtClose,                 /* xClose - close a cursor */
 254   stmtFilter,                /* xFilter - configure scan constraints */
 255   stmtNext,                  /* xNext - advance a cursor */
 256   stmtEof,                   /* xEof - check for end of scan */
 257   stmtColumn,                /* xColumn - read data */
 258   stmtRowid,                 /* xRowid - read data */
 259   0,                         /* xUpdate */
 260   0,                         /* xBegin */
 261   0,                         /* xSync */
 262   0,                         /* xCommit */
 263   0,                         /* xRollback */
 264   0,                         /* xFindMethod */
 265   0,                         /* xRename */
 266   0,                         /* xSavepoint */
 267   0,                         /* xRelease */
 268   0,                         /* xRollbackTo */
 269 };
 270
 271 #endif /* SQLITE_OMIT_VIRTUALTABLE */
 272
 273 int sqlite3StmtVtabInit(sqlite3 *db){
 274   int rc = SQLITE_OK;
 275 #ifndef SQLITE_OMIT_VIRTUALTABLE
 276   rc = sqlite3_create_module(db, "sqlite_stmt", &stmtModule, 0);
 277 #endif
 278   return rc;
 279 }
 280
 281 #ifndef SQLITE_CORE
 282 #ifdef _WIN32
 283 __declspec(dllexport)
 284 #endif
 285 int sqlite3_stmt_init(
 286   sqlite3 *db,
 287   char **pzErrMsg,
 288   const sqlite3_api_routines *pApi
 289 ){
 290   int rc = SQLITE_OK;
 291   SQLITE_EXTENSION_INIT2(pApi);
 292 #ifndef SQLITE_OMIT_VIRTUALTABLE
 293   rc = sqlite3StmtVtabInit(db);
 294 #endif
 295   return rc;
 296 }
 297 #endif /* SQLITE_CORE */
 298 #endif /* !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_STMTVTAB) */