4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
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.
11 *************************************************************************
13 ** This file implements an eponymous virtual table that returns suggested
14 ** completions for a partial SQL input.
18 ** SELECT DISTINCT candidate COLLATE nocase
19 ** FROM completion($prefix,$wholeline)
22 ** The two query parameters are optional. $prefix is the text of the
23 ** current word being typed and that is to be completed. $wholeline is
24 ** the complete input line, used for context.
26 ** The raw completion() table might return the same candidate multiple
27 ** times, for example if the same column name is used to two or more
28 ** tables. And the candidates are returned in an arbitrary order. Hence,
29 ** the DISTINCT and ORDER BY are recommended.
31 ** This virtual table operates at the speed of human typing, and so there
32 ** is no attempt to make it fast. Even a slow implementation will be much
33 ** faster than any human can type.
36 #include "sqlite3ext.h"
37 SQLITE_EXTENSION_INIT1
42 #ifndef SQLITE_OMIT_VIRTUALTABLE
44 /* completion_vtab is a subclass of sqlite3_vtab which will
45 ** serve as the underlying representation of a completion virtual table
47 typedef struct completion_vtab completion_vtab
;
48 struct completion_vtab
{
49 sqlite3_vtab base
; /* Base class - must be first */
50 sqlite3
*db
; /* Database connection for this completion vtab */
53 /* completion_cursor is a subclass of sqlite3_vtab_cursor which will
54 ** serve as the underlying representation of a cursor that scans
55 ** over rows of the result
57 typedef struct completion_cursor completion_cursor
;
58 struct completion_cursor
{
59 sqlite3_vtab_cursor base
; /* Base class - must be first */
60 sqlite3
*db
; /* Database connection for this cursor */
61 int nPrefix
, nLine
; /* Number of bytes in zPrefix and zLine */
62 char *zPrefix
; /* The prefix for the word we want to complete */
63 char *zLine
; /* The whole that we want to complete */
64 const char *zCurrentRow
; /* Current output row */
65 sqlite3_stmt
*pStmt
; /* Current statement */
66 sqlite3_int64 iRowid
; /* The rowid */
67 int ePhase
; /* Current phase */
68 int j
; /* inter-phase counter */
73 #define COMPLETION_FIRST_PHASE 1
74 #define COMPLETION_KEYWORDS 1
75 #define COMPLETION_PRAGMAS 2
76 #define COMPLETION_FUNCTIONS 3
77 #define COMPLETION_COLLATIONS 4
78 #define COMPLETION_INDEXES 5
79 #define COMPLETION_TRIGGERS 6
80 #define COMPLETION_DATABASES 7
81 #define COMPLETION_TABLES 8
82 #define COMPLETION_COLUMNS 9
83 #define COMPLETION_MODULES 10
84 #define COMPLETION_EOF 11
87 ** The completionConnect() method is invoked to create a new
88 ** completion_vtab that describes the completion virtual table.
90 ** Think of this routine as the constructor for completion_vtab objects.
92 ** All this routine needs to do is:
94 ** (1) Allocate the completion_vtab object and initialize all fields.
96 ** (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the
97 ** result set of queries against completion will look like.
99 static int completionConnect(
102 int argc
, const char *const*argv
,
103 sqlite3_vtab
**ppVtab
,
106 completion_vtab
*pNew
;
109 (void)(pAux
); /* Unused parameter */
110 (void)(argc
); /* Unused parameter */
111 (void)(argv
); /* Unused parameter */
112 (void)(pzErr
); /* Unused parameter */
115 #define COMPLETION_COLUMN_CANDIDATE 0 /* Suggested completion of the input */
116 #define COMPLETION_COLUMN_PREFIX 1 /* Prefix of the word to be completed */
117 #define COMPLETION_COLUMN_WHOLELINE 2 /* Entire line seen so far */
118 #define COMPLETION_COLUMN_PHASE 3 /* ePhase - used for debugging only */
120 rc
= sqlite3_declare_vtab(db
,
123 " prefix TEXT HIDDEN,"
124 " wholeline TEXT HIDDEN,"
125 " phase INT HIDDEN" /* Used for debugging only */
128 pNew
= sqlite3_malloc( sizeof(*pNew
) );
129 *ppVtab
= (sqlite3_vtab
*)pNew
;
130 if( pNew
==0 ) return SQLITE_NOMEM
;
131 memset(pNew
, 0, sizeof(*pNew
));
138 ** This method is the destructor for completion_cursor objects.
140 static int completionDisconnect(sqlite3_vtab
*pVtab
){
146 ** Constructor for a new completion_cursor object.
148 static int completionOpen(sqlite3_vtab
*p
, sqlite3_vtab_cursor
**ppCursor
){
149 completion_cursor
*pCur
;
150 pCur
= sqlite3_malloc( sizeof(*pCur
) );
151 if( pCur
==0 ) return SQLITE_NOMEM
;
152 memset(pCur
, 0, sizeof(*pCur
));
153 pCur
->db
= ((completion_vtab
*)p
)->db
;
154 *ppCursor
= &pCur
->base
;
159 ** Reset the completion_cursor.
161 static void completionCursorReset(completion_cursor
*pCur
){
162 sqlite3_free(pCur
->zPrefix
); pCur
->zPrefix
= 0; pCur
->nPrefix
= 0;
163 sqlite3_free(pCur
->zLine
); pCur
->zLine
= 0; pCur
->nLine
= 0;
164 sqlite3_finalize(pCur
->pStmt
); pCur
->pStmt
= 0;
169 ** Destructor for a completion_cursor.
171 static int completionClose(sqlite3_vtab_cursor
*cur
){
172 completionCursorReset((completion_cursor
*)cur
);
178 ** All SQL keywords understood by SQLite
180 static const char *completionKwrds
[] = {
181 "ABORT", "ACTION", "ADD", "AFTER", "ALL", "ALTER", "ANALYZE", "AND", "AS",
182 "ASC", "ATTACH", "AUTOINCREMENT", "BEFORE", "BEGIN", "BETWEEN", "BY",
183 "CASCADE", "CASE", "CAST", "CHECK", "COLLATE", "COLUMN", "COMMIT",
184 "CONFLICT", "CONSTRAINT", "CREATE", "CROSS", "CURRENT_DATE",
185 "CURRENT_TIME", "CURRENT_TIMESTAMP", "DATABASE", "DEFAULT", "DEFERRABLE",
186 "DEFERRED", "DELETE", "DESC", "DETACH", "DISTINCT", "DROP", "EACH",
187 "ELSE", "END", "ESCAPE", "EXCEPT", "EXCLUSIVE", "EXISTS", "EXPLAIN",
188 "FAIL", "FOR", "FOREIGN", "FROM", "FULL", "GLOB", "GROUP", "HAVING", "IF",
189 "IGNORE", "IMMEDIATE", "IN", "INDEX", "INDEXED", "INITIALLY", "INNER",
190 "INSERT", "INSTEAD", "INTERSECT", "INTO", "IS", "ISNULL", "JOIN", "KEY",
191 "LEFT", "LIKE", "LIMIT", "MATCH", "NATURAL", "NO", "NOT", "NOTNULL",
192 "NULL", "OF", "OFFSET", "ON", "OR", "ORDER", "OUTER", "PLAN", "PRAGMA",
193 "PRIMARY", "QUERY", "RAISE", "RECURSIVE", "REFERENCES", "REGEXP",
194 "REINDEX", "RELEASE", "RENAME", "REPLACE", "RESTRICT", "RIGHT",
195 "ROLLBACK", "ROW", "SAVEPOINT", "SELECT", "SET", "TABLE", "TEMP",
196 "TEMPORARY", "THEN", "TO", "TRANSACTION", "TRIGGER", "UNION", "UNIQUE",
197 "UPDATE", "USING", "VACUUM", "VALUES", "VIEW", "VIRTUAL", "WHEN", "WHERE",
200 #define completionKwCount \
201 (int)(sizeof(completionKwrds)/sizeof(completionKwrds[0]))
204 ** Advance a completion_cursor to its next row of output.
206 ** The ->ePhase, ->j, and ->pStmt fields of the completion_cursor object
207 ** record the current state of the scan. This routine sets ->zCurrentRow
208 ** to the current row of output and then returns. If no more rows remain,
209 ** then ->ePhase is set to COMPLETION_EOF which will signal the virtual
210 ** table that has reached the end of its scan.
212 ** The current implementation just lists potential identifiers and
213 ** keywords and filters them by zPrefix. Future enhancements should
214 ** take zLine into account to try to restrict the set of identifiers and
215 ** keywords based on what would be legal at the current point of input.
217 static int completionNext(sqlite3_vtab_cursor
*cur
){
218 completion_cursor
*pCur
= (completion_cursor
*)cur
;
219 int eNextPhase
= 0; /* Next phase to try if current phase reaches end */
220 int iCol
= -1; /* If >=0, step pCur->pStmt and use the i-th column */
222 while( pCur
->ePhase
!=COMPLETION_EOF
){
223 switch( pCur
->ePhase
){
224 case COMPLETION_KEYWORDS
: {
225 if( pCur
->j
>= completionKwCount
){
226 pCur
->zCurrentRow
= 0;
227 pCur
->ePhase
= COMPLETION_DATABASES
;
229 pCur
->zCurrentRow
= completionKwrds
[pCur
->j
++];
234 case COMPLETION_DATABASES
: {
235 if( pCur
->pStmt
==0 ){
236 sqlite3_prepare_v2(pCur
->db
, "PRAGMA database_list", -1,
240 eNextPhase
= COMPLETION_TABLES
;
243 case COMPLETION_TABLES
: {
244 if( pCur
->pStmt
==0 ){
247 const char *zSep
= "";
248 sqlite3_prepare_v2(pCur
->db
, "PRAGMA database_list", -1, &pS2
, 0);
249 while( sqlite3_step(pS2
)==SQLITE_ROW
){
250 const char *zDb
= (const char*)sqlite3_column_text(pS2
, 1);
251 zSql
= sqlite3_mprintf(
253 "SELECT name FROM \"%w\".sqlite_master"
254 " WHERE type='table'",
257 if( zSql
==0 ) return SQLITE_NOMEM
;
260 sqlite3_finalize(pS2
);
261 sqlite3_prepare_v2(pCur
->db
, zSql
, -1, &pCur
->pStmt
, 0);
265 eNextPhase
= COMPLETION_COLUMNS
;
268 case COMPLETION_COLUMNS
: {
269 if( pCur
->pStmt
==0 ){
272 const char *zSep
= "";
273 sqlite3_prepare_v2(pCur
->db
, "PRAGMA database_list", -1, &pS2
, 0);
274 while( sqlite3_step(pS2
)==SQLITE_ROW
){
275 const char *zDb
= (const char*)sqlite3_column_text(pS2
, 1);
276 zSql
= sqlite3_mprintf(
278 "SELECT pti.name FROM \"%w\".sqlite_master AS sm"
279 " JOIN pragma_table_info(sm.name,%Q) AS pti"
280 " WHERE sm.type='table'",
283 if( zSql
==0 ) return SQLITE_NOMEM
;
286 sqlite3_finalize(pS2
);
287 sqlite3_prepare_v2(pCur
->db
, zSql
, -1, &pCur
->pStmt
, 0);
291 eNextPhase
= COMPLETION_EOF
;
296 /* This case is when the phase presets zCurrentRow */
297 if( pCur
->zCurrentRow
==0 ) continue;
299 if( sqlite3_step(pCur
->pStmt
)==SQLITE_ROW
){
300 /* Extract the next row of content */
301 pCur
->zCurrentRow
= (const char*)sqlite3_column_text(pCur
->pStmt
, iCol
);
303 /* When all rows are finished, advance to the next phase */
304 sqlite3_finalize(pCur
->pStmt
);
306 pCur
->ePhase
= eNextPhase
;
310 if( pCur
->nPrefix
==0 ) break;
311 if( sqlite3_strnicmp(pCur
->zPrefix
, pCur
->zCurrentRow
, pCur
->nPrefix
)==0 ){
320 ** Return values of columns for the row at which the completion_cursor
321 ** is currently pointing.
323 static int completionColumn(
324 sqlite3_vtab_cursor
*cur
, /* The cursor */
325 sqlite3_context
*ctx
, /* First argument to sqlite3_result_...() */
326 int i
/* Which column to return */
328 completion_cursor
*pCur
= (completion_cursor
*)cur
;
330 case COMPLETION_COLUMN_CANDIDATE
: {
331 sqlite3_result_text(ctx
, pCur
->zCurrentRow
, -1, SQLITE_TRANSIENT
);
334 case COMPLETION_COLUMN_PREFIX
: {
335 sqlite3_result_text(ctx
, pCur
->zPrefix
, -1, SQLITE_TRANSIENT
);
338 case COMPLETION_COLUMN_WHOLELINE
: {
339 sqlite3_result_text(ctx
, pCur
->zLine
, -1, SQLITE_TRANSIENT
);
342 case COMPLETION_COLUMN_PHASE
: {
343 sqlite3_result_int(ctx
, pCur
->ePhase
);
351 ** Return the rowid for the current row. In this implementation, the
352 ** rowid is the same as the output value.
354 static int completionRowid(sqlite3_vtab_cursor
*cur
, sqlite_int64
*pRowid
){
355 completion_cursor
*pCur
= (completion_cursor
*)cur
;
356 *pRowid
= pCur
->iRowid
;
361 ** Return TRUE if the cursor has been moved off of the last
364 static int completionEof(sqlite3_vtab_cursor
*cur
){
365 completion_cursor
*pCur
= (completion_cursor
*)cur
;
366 return pCur
->ePhase
>= COMPLETION_EOF
;
370 ** This method is called to "rewind" the completion_cursor object back
371 ** to the first row of output. This method is always called at least
372 ** once prior to any call to completionColumn() or completionRowid() or
375 static int completionFilter(
376 sqlite3_vtab_cursor
*pVtabCursor
,
377 int idxNum
, const char *idxStr
,
378 int argc
, sqlite3_value
**argv
380 completion_cursor
*pCur
= (completion_cursor
*)pVtabCursor
;
382 (void)(idxStr
); /* Unused parameter */
383 (void)(argc
); /* Unused parameter */
384 completionCursorReset(pCur
);
386 pCur
->nPrefix
= sqlite3_value_bytes(argv
[iArg
]);
387 if( pCur
->nPrefix
>0 ){
388 pCur
->zPrefix
= sqlite3_mprintf("%s", sqlite3_value_text(argv
[iArg
]));
389 if( pCur
->zPrefix
==0 ) return SQLITE_NOMEM
;
394 pCur
->nLine
= sqlite3_value_bytes(argv
[iArg
]);
396 pCur
->zLine
= sqlite3_mprintf("%s", sqlite3_value_text(argv
[iArg
]));
397 if( pCur
->zLine
==0 ) return SQLITE_NOMEM
;
401 if( pCur
->zLine
!=0 && pCur
->zPrefix
==0 ){
403 while( i
>0 && (isalnum(pCur
->zLine
[i
-1]) || pCur
->zLine
[i
-1]=='_') ){
406 pCur
->nPrefix
= pCur
->nLine
- i
;
407 if( pCur
->nPrefix
>0 ){
408 pCur
->zPrefix
= sqlite3_mprintf("%.*s", pCur
->nPrefix
, pCur
->zLine
+ i
);
409 if( pCur
->zPrefix
==0 ) return SQLITE_NOMEM
;
413 pCur
->ePhase
= COMPLETION_FIRST_PHASE
;
414 return completionNext(pVtabCursor
);
418 ** SQLite will invoke this method one or more times while planning a query
419 ** that uses the completion virtual table. This routine needs to create
420 ** a query plan for each invocation and compute an estimated cost for that
423 ** There are two hidden parameters that act as arguments to the table-valued
424 ** function: "prefix" and "wholeline". Bit 0 of idxNum is set if "prefix"
425 ** is available and bit 1 is set if "wholeline" is available.
427 static int completionBestIndex(
429 sqlite3_index_info
*pIdxInfo
431 int i
; /* Loop over constraints */
432 int idxNum
= 0; /* The query plan bitmask */
433 int prefixIdx
= -1; /* Index of the start= constraint, or -1 if none */
434 int wholelineIdx
= -1; /* Index of the stop= constraint, or -1 if none */
435 int nArg
= 0; /* Number of arguments that completeFilter() expects */
436 const struct sqlite3_index_constraint
*pConstraint
;
438 (void)(tab
); /* Unused parameter */
439 pConstraint
= pIdxInfo
->aConstraint
;
440 for(i
=0; i
<pIdxInfo
->nConstraint
; i
++, pConstraint
++){
441 if( pConstraint
->usable
==0 ) continue;
442 if( pConstraint
->op
!=SQLITE_INDEX_CONSTRAINT_EQ
) continue;
443 switch( pConstraint
->iColumn
){
444 case COMPLETION_COLUMN_PREFIX
:
448 case COMPLETION_COLUMN_WHOLELINE
:
455 pIdxInfo
->aConstraintUsage
[prefixIdx
].argvIndex
= ++nArg
;
456 pIdxInfo
->aConstraintUsage
[prefixIdx
].omit
= 1;
458 if( wholelineIdx
>=0 ){
459 pIdxInfo
->aConstraintUsage
[wholelineIdx
].argvIndex
= ++nArg
;
460 pIdxInfo
->aConstraintUsage
[wholelineIdx
].omit
= 1;
462 pIdxInfo
->idxNum
= idxNum
;
463 pIdxInfo
->estimatedCost
= (double)5000 - 1000*nArg
;
464 pIdxInfo
->estimatedRows
= 500 - 100*nArg
;
469 ** This following structure defines all the methods for the
470 ** completion virtual table.
472 static sqlite3_module completionModule
= {
475 completionConnect
, /* xConnect */
476 completionBestIndex
, /* xBestIndex */
477 completionDisconnect
, /* xDisconnect */
479 completionOpen
, /* xOpen - open a cursor */
480 completionClose
, /* xClose - close a cursor */
481 completionFilter
, /* xFilter - configure scan constraints */
482 completionNext
, /* xNext - advance a cursor */
483 completionEof
, /* xEof - check for end of scan */
484 completionColumn
, /* xColumn - read data */
485 completionRowid
, /* xRowid - read data */
498 #endif /* SQLITE_OMIT_VIRTUALTABLE */
500 int sqlite3CompletionVtabInit(sqlite3
*db
){
502 #ifndef SQLITE_OMIT_VIRTUALTABLE
503 rc
= sqlite3_create_module(db
, "completion", &completionModule
, 0);
509 __declspec(dllexport
)
511 int sqlite3_completion_init(
514 const sqlite3_api_routines
*pApi
517 SQLITE_EXTENSION_INIT2(pApi
);
518 (void)(pzErrMsg
); /* Unused parameter */
519 #ifndef SQLITE_OMIT_VIRTUALTABLE
520 rc
= sqlite3CompletionVtabInit(db
);