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 */
stmtConnect(sqlite3 * db,void * pAux,int argc,const char * const * argv,sqlite3_vtab ** ppVtab,char ** pzErr)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 */
stmtDisconnect(sqlite3_vtab * pVtab)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 */
stmtOpen(sqlite3_vtab * p,sqlite3_vtab_cursor ** ppCursor)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
stmtCsrReset(stmt_cursor * pCur)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 */
stmtClose(sqlite3_vtab_cursor * cur)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 */
stmtNext(sqlite3_vtab_cursor * cur)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 */
stmtColumn(sqlite3_vtab_cursor * cur,sqlite3_context * ctx,int i)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 */
stmtRowid(sqlite3_vtab_cursor * cur,sqlite_int64 * pRowid)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 */
stmtEof(sqlite3_vtab_cursor * cur)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 */
stmtFilter(sqlite3_vtab_cursor * pVtabCursor,int idxNum,const char * idxStr,int argc,sqlite3_value ** argv)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 */
stmtBestIndex(sqlite3_vtab * tab,sqlite3_index_info * pIdxInfo)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
sqlite3StmtVtabInit(sqlite3 * db)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
sqlite3_stmt_init(sqlite3 * db,char ** pzErrMsg,const sqlite3_api_routines * pApi)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