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