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   rc = sqlite3_declare_vtab(db,
101      "CREATE TABLE x(sql,ncol,ro,busy,nscan,nsort,naidx,nstep,"
102                     "reprep,run,mem)");
103   if( rc==SQLITE_OK ){
104     pNew = sqlite3_malloc64( sizeof(*pNew) );
105     *ppVtab = (sqlite3_vtab*)pNew;
106     if( pNew==0 ) return SQLITE_NOMEM;
107     memset(pNew, 0, sizeof(*pNew));
108     pNew->db = db;
109   }
110   return rc;
111 }
112 
113 /*
114 ** This method is the destructor for stmt_cursor objects.
115 */
116 static int stmtDisconnect(sqlite3_vtab *pVtab){
117   sqlite3_free(pVtab);
118   return SQLITE_OK;
119 }
120 
121 /*
122 ** Constructor for a new stmt_cursor object.
123 */
124 static int stmtOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
125   stmt_cursor *pCur;
126   pCur = sqlite3_malloc64( sizeof(*pCur) );
127   if( pCur==0 ) return SQLITE_NOMEM;
128   memset(pCur, 0, sizeof(*pCur));
129   pCur->db = ((stmt_vtab*)p)->db;
130   *ppCursor = &pCur->base;
131   return SQLITE_OK;
132 }
133 
134 static void stmtCsrReset(stmt_cursor *pCur){
135   StmtRow *pRow = 0;
136   StmtRow *pNext = 0;
137   for(pRow=pCur->pRow; pRow; pRow=pNext){
138     pNext = pRow->pNext;
139     sqlite3_free(pRow);
140   }
141   pCur->pRow = 0;
142 }
143 
144 /*
145 ** Destructor for a stmt_cursor.
146 */
147 static int stmtClose(sqlite3_vtab_cursor *cur){
148   stmtCsrReset((stmt_cursor*)cur);
149   sqlite3_free(cur);
150   return SQLITE_OK;
151 }
152 
153 
154 /*
155 ** Advance a stmt_cursor to its next row of output.
156 */
157 static int stmtNext(sqlite3_vtab_cursor *cur){
158   stmt_cursor *pCur = (stmt_cursor*)cur;
159   StmtRow *pNext = pCur->pRow->pNext;
160   sqlite3_free(pCur->pRow);
161   pCur->pRow = pNext;
162   return SQLITE_OK;
163 }
164 
165 /*
166 ** Return values of columns for the row at which the stmt_cursor
167 ** is currently pointing.
168 */
169 static int stmtColumn(
170   sqlite3_vtab_cursor *cur,   /* The cursor */
171   sqlite3_context *ctx,       /* First argument to sqlite3_result_...() */
172   int i                       /* Which column to return */
173 ){
174   stmt_cursor *pCur = (stmt_cursor*)cur;
175   StmtRow *pRow = pCur->pRow;
176   if( i==STMT_COLUMN_SQL ){
177     sqlite3_result_text(ctx, pRow->zSql, -1, SQLITE_TRANSIENT);
178   }else{
179     sqlite3_result_int(ctx, pRow->aCol[i]);
180   }
181   return SQLITE_OK;
182 }
183 
184 /*
185 ** Return the rowid for the current row.  In this implementation, the
186 ** rowid is the same as the output value.
187 */
188 static int stmtRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
189   stmt_cursor *pCur = (stmt_cursor*)cur;
190   *pRowid = pCur->pRow->iRowid;
191   return SQLITE_OK;
192 }
193 
194 /*
195 ** Return TRUE if the cursor has been moved off of the last
196 ** row of output.
197 */
198 static int stmtEof(sqlite3_vtab_cursor *cur){
199   stmt_cursor *pCur = (stmt_cursor*)cur;
200   return pCur->pRow==0;
201 }
202 
203 /*
204 ** This method is called to "rewind" the stmt_cursor object back
205 ** to the first row of output.  This method is always called at least
206 ** once prior to any call to stmtColumn() or stmtRowid() or
207 ** stmtEof().
208 */
209 static int stmtFilter(
210   sqlite3_vtab_cursor *pVtabCursor,
211   int idxNum, const char *idxStr,
212   int argc, sqlite3_value **argv
213 ){
214   stmt_cursor *pCur = (stmt_cursor *)pVtabCursor;
215   sqlite3_stmt *p = 0;
216   sqlite3_int64 iRowid = 1;
217   StmtRow **ppRow = 0;
218 
219   stmtCsrReset(pCur);
220   ppRow = &pCur->pRow;
221   for(p=sqlite3_next_stmt(pCur->db, 0); p; p=sqlite3_next_stmt(pCur->db, p)){
222     const char *zSql = sqlite3_sql(p);
223     sqlite3_int64 nSql = zSql ? strlen(zSql)+1 : 0;
224     StmtRow *pNew = (StmtRow*)sqlite3_malloc64(sizeof(StmtRow) + nSql);
225 
226     if( pNew==0 ) return SQLITE_NOMEM;
227     memset(pNew, 0, sizeof(StmtRow));
228     if( zSql ){
229       pNew->zSql = (char*)&pNew[1];
230       memcpy(pNew->zSql, zSql, nSql);
231     }
232     pNew->aCol[STMT_COLUMN_NCOL] = sqlite3_column_count(p);
233     pNew->aCol[STMT_COLUMN_RO] = sqlite3_stmt_readonly(p);
234     pNew->aCol[STMT_COLUMN_BUSY] = sqlite3_stmt_busy(p);
235     pNew->aCol[STMT_COLUMN_NSCAN] = sqlite3_stmt_status(
236         p, SQLITE_STMTSTATUS_FULLSCAN_STEP, 0
237     );
238     pNew->aCol[STMT_COLUMN_NSORT] = sqlite3_stmt_status(
239         p, SQLITE_STMTSTATUS_SORT, 0
240     );
241     pNew->aCol[STMT_COLUMN_NAIDX] = sqlite3_stmt_status(
242         p, SQLITE_STMTSTATUS_AUTOINDEX, 0
243     );
244     pNew->aCol[STMT_COLUMN_NSTEP] = sqlite3_stmt_status(
245         p, SQLITE_STMTSTATUS_VM_STEP, 0
246     );
247     pNew->aCol[STMT_COLUMN_REPREP] = sqlite3_stmt_status(
248         p, SQLITE_STMTSTATUS_REPREPARE, 0
249     );
250     pNew->aCol[STMT_COLUMN_RUN] = sqlite3_stmt_status(
251         p, SQLITE_STMTSTATUS_RUN, 0
252     );
253     pNew->aCol[STMT_COLUMN_MEM] = sqlite3_stmt_status(
254         p, SQLITE_STMTSTATUS_MEMUSED, 0
255     );
256     pNew->iRowid = iRowid++;
257     *ppRow = pNew;
258     ppRow = &pNew->pNext;
259   }
260 
261   return SQLITE_OK;
262 }
263 
264 /*
265 ** SQLite will invoke this method one or more times while planning a query
266 ** that uses the stmt virtual table.  This routine needs to create
267 ** a query plan for each invocation and compute an estimated cost for that
268 ** plan.
269 */
270 static int stmtBestIndex(
271   sqlite3_vtab *tab,
272   sqlite3_index_info *pIdxInfo
273 ){
274   pIdxInfo->estimatedCost = (double)500;
275   pIdxInfo->estimatedRows = 500;
276   return SQLITE_OK;
277 }
278 
279 /*
280 ** This following structure defines all the methods for the
281 ** stmt virtual table.
282 */
283 static sqlite3_module stmtModule = {
284   0,                         /* iVersion */
285   0,                         /* xCreate */
286   stmtConnect,               /* xConnect */
287   stmtBestIndex,             /* xBestIndex */
288   stmtDisconnect,            /* xDisconnect */
289   0,                         /* xDestroy */
290   stmtOpen,                  /* xOpen - open a cursor */
291   stmtClose,                 /* xClose - close a cursor */
292   stmtFilter,                /* xFilter - configure scan constraints */
293   stmtNext,                  /* xNext - advance a cursor */
294   stmtEof,                   /* xEof - check for end of scan */
295   stmtColumn,                /* xColumn - read data */
296   stmtRowid,                 /* xRowid - read data */
297   0,                         /* xUpdate */
298   0,                         /* xBegin */
299   0,                         /* xSync */
300   0,                         /* xCommit */
301   0,                         /* xRollback */
302   0,                         /* xFindMethod */
303   0,                         /* xRename */
304   0,                         /* xSavepoint */
305   0,                         /* xRelease */
306   0,                         /* xRollbackTo */
307   0,                         /* xShadowName */
308 };
309 
310 #endif /* SQLITE_OMIT_VIRTUALTABLE */
311 
312 int sqlite3StmtVtabInit(sqlite3 *db){
313   int rc = SQLITE_OK;
314 #ifndef SQLITE_OMIT_VIRTUALTABLE
315   rc = sqlite3_create_module(db, "sqlite_stmt", &stmtModule, 0);
316 #endif
317   return rc;
318 }
319 
320 #ifndef SQLITE_CORE
321 #ifdef _WIN32
322 __declspec(dllexport)
323 #endif
324 int sqlite3_stmt_init(
325   sqlite3 *db,
326   char **pzErrMsg,
327   const sqlite3_api_routines *pApi
328 ){
329   int rc = SQLITE_OK;
330   SQLITE_EXTENSION_INIT2(pApi);
331 #ifndef SQLITE_OMIT_VIRTUALTABLE
332   rc = sqlite3StmtVtabInit(db);
333 #endif
334   return rc;
335 }
336 #endif /* SQLITE_CORE */
337 #endif /* !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_STMTVTAB) */
338