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 /* Also VIEWs and TRIGGERs */
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",
256 if( zSql
==0 ) return SQLITE_NOMEM
;
259 sqlite3_finalize(pS2
);
260 sqlite3_prepare_v2(pCur
->db
, zSql
, -1, &pCur
->pStmt
, 0);
264 eNextPhase
= COMPLETION_COLUMNS
;
267 case COMPLETION_COLUMNS
: {
268 if( pCur
->pStmt
==0 ){
271 const char *zSep
= "";
272 sqlite3_prepare_v2(pCur
->db
, "PRAGMA database_list", -1, &pS2
, 0);
273 while( sqlite3_step(pS2
)==SQLITE_ROW
){
274 const char *zDb
= (const char*)sqlite3_column_text(pS2
, 1);
275 zSql
= sqlite3_mprintf(
277 "SELECT pti.name FROM \"%w\".sqlite_master AS sm"
278 " JOIN pragma_table_info(sm.name,%Q) AS pti"
279 " WHERE sm.type='table'",
282 if( zSql
==0 ) return SQLITE_NOMEM
;
285 sqlite3_finalize(pS2
);
286 sqlite3_prepare_v2(pCur
->db
, zSql
, -1, &pCur
->pStmt
, 0);
290 eNextPhase
= COMPLETION_EOF
;
295 /* This case is when the phase presets zCurrentRow */
296 if( pCur
->zCurrentRow
==0 ) continue;
298 if( sqlite3_step(pCur
->pStmt
)==SQLITE_ROW
){
299 /* Extract the next row of content */
300 pCur
->zCurrentRow
= (const char*)sqlite3_column_text(pCur
->pStmt
, iCol
);
302 /* When all rows are finished, advance to the next phase */
303 sqlite3_finalize(pCur
->pStmt
);
305 pCur
->ePhase
= eNextPhase
;
309 if( pCur
->nPrefix
==0 ) break;
310 if( sqlite3_strnicmp(pCur
->zPrefix
, pCur
->zCurrentRow
, pCur
->nPrefix
)==0 ){
319 ** Return values of columns for the row at which the completion_cursor
320 ** is currently pointing.
322 static int completionColumn(
323 sqlite3_vtab_cursor
*cur
, /* The cursor */
324 sqlite3_context
*ctx
, /* First argument to sqlite3_result_...() */
325 int i
/* Which column to return */
327 completion_cursor
*pCur
= (completion_cursor
*)cur
;
329 case COMPLETION_COLUMN_CANDIDATE
: {
330 sqlite3_result_text(ctx
, pCur
->zCurrentRow
, -1, SQLITE_TRANSIENT
);
333 case COMPLETION_COLUMN_PREFIX
: {
334 sqlite3_result_text(ctx
, pCur
->zPrefix
, -1, SQLITE_TRANSIENT
);
337 case COMPLETION_COLUMN_WHOLELINE
: {
338 sqlite3_result_text(ctx
, pCur
->zLine
, -1, SQLITE_TRANSIENT
);
341 case COMPLETION_COLUMN_PHASE
: {
342 sqlite3_result_int(ctx
, pCur
->ePhase
);
350 ** Return the rowid for the current row. In this implementation, the
351 ** rowid is the same as the output value.
353 static int completionRowid(sqlite3_vtab_cursor
*cur
, sqlite_int64
*pRowid
){
354 completion_cursor
*pCur
= (completion_cursor
*)cur
;
355 *pRowid
= pCur
->iRowid
;
360 ** Return TRUE if the cursor has been moved off of the last
363 static int completionEof(sqlite3_vtab_cursor
*cur
){
364 completion_cursor
*pCur
= (completion_cursor
*)cur
;
365 return pCur
->ePhase
>= COMPLETION_EOF
;
369 ** This method is called to "rewind" the completion_cursor object back
370 ** to the first row of output. This method is always called at least
371 ** once prior to any call to completionColumn() or completionRowid() or
374 static int completionFilter(
375 sqlite3_vtab_cursor
*pVtabCursor
,
376 int idxNum
, const char *idxStr
,
377 int argc
, sqlite3_value
**argv
379 completion_cursor
*pCur
= (completion_cursor
*)pVtabCursor
;
381 (void)(idxStr
); /* Unused parameter */
382 (void)(argc
); /* Unused parameter */
383 completionCursorReset(pCur
);
385 pCur
->nPrefix
= sqlite3_value_bytes(argv
[iArg
]);
386 if( pCur
->nPrefix
>0 ){
387 pCur
->zPrefix
= sqlite3_mprintf("%s", sqlite3_value_text(argv
[iArg
]));
388 if( pCur
->zPrefix
==0 ) return SQLITE_NOMEM
;
393 pCur
->nLine
= sqlite3_value_bytes(argv
[iArg
]);
395 pCur
->zLine
= sqlite3_mprintf("%s", sqlite3_value_text(argv
[iArg
]));
396 if( pCur
->zLine
==0 ) return SQLITE_NOMEM
;
400 if( pCur
->zLine
!=0 && pCur
->zPrefix
==0 ){
402 while( i
>0 && (isalnum(pCur
->zLine
[i
-1]) || pCur
->zLine
[i
-1]=='_') ){
405 pCur
->nPrefix
= pCur
->nLine
- i
;
406 if( pCur
->nPrefix
>0 ){
407 pCur
->zPrefix
= sqlite3_mprintf("%.*s", pCur
->nPrefix
, pCur
->zLine
+ i
);
408 if( pCur
->zPrefix
==0 ) return SQLITE_NOMEM
;
412 pCur
->ePhase
= COMPLETION_FIRST_PHASE
;
413 return completionNext(pVtabCursor
);
417 ** SQLite will invoke this method one or more times while planning a query
418 ** that uses the completion virtual table. This routine needs to create
419 ** a query plan for each invocation and compute an estimated cost for that
422 ** There are two hidden parameters that act as arguments to the table-valued
423 ** function: "prefix" and "wholeline". Bit 0 of idxNum is set if "prefix"
424 ** is available and bit 1 is set if "wholeline" is available.
426 static int completionBestIndex(
428 sqlite3_index_info
*pIdxInfo
430 int i
; /* Loop over constraints */
431 int idxNum
= 0; /* The query plan bitmask */
432 int prefixIdx
= -1; /* Index of the start= constraint, or -1 if none */
433 int wholelineIdx
= -1; /* Index of the stop= constraint, or -1 if none */
434 int nArg
= 0; /* Number of arguments that completeFilter() expects */
435 const struct sqlite3_index_constraint
*pConstraint
;
437 (void)(tab
); /* Unused parameter */
438 pConstraint
= pIdxInfo
->aConstraint
;
439 for(i
=0; i
<pIdxInfo
->nConstraint
; i
++, pConstraint
++){
440 if( pConstraint
->usable
==0 ) continue;
441 if( pConstraint
->op
!=SQLITE_INDEX_CONSTRAINT_EQ
) continue;
442 switch( pConstraint
->iColumn
){
443 case COMPLETION_COLUMN_PREFIX
:
447 case COMPLETION_COLUMN_WHOLELINE
:
454 pIdxInfo
->aConstraintUsage
[prefixIdx
].argvIndex
= ++nArg
;
455 pIdxInfo
->aConstraintUsage
[prefixIdx
].omit
= 1;
457 if( wholelineIdx
>=0 ){
458 pIdxInfo
->aConstraintUsage
[wholelineIdx
].argvIndex
= ++nArg
;
459 pIdxInfo
->aConstraintUsage
[wholelineIdx
].omit
= 1;
461 pIdxInfo
->idxNum
= idxNum
;
462 pIdxInfo
->estimatedCost
= (double)5000 - 1000*nArg
;
463 pIdxInfo
->estimatedRows
= 500 - 100*nArg
;
468 ** This following structure defines all the methods for the
469 ** completion virtual table.
471 static sqlite3_module completionModule
= {
474 completionConnect
, /* xConnect */
475 completionBestIndex
, /* xBestIndex */
476 completionDisconnect
, /* xDisconnect */
478 completionOpen
, /* xOpen - open a cursor */
479 completionClose
, /* xClose - close a cursor */
480 completionFilter
, /* xFilter - configure scan constraints */
481 completionNext
, /* xNext - advance a cursor */
482 completionEof
, /* xEof - check for end of scan */
483 completionColumn
, /* xColumn - read data */
484 completionRowid
, /* xRowid - read data */
497 #endif /* SQLITE_OMIT_VIRTUALTABLE */
499 int sqlite3CompletionVtabInit(sqlite3
*db
){
501 #ifndef SQLITE_OMIT_VIRTUALTABLE
502 rc
= sqlite3_create_module(db
, "completion", &completionModule
, 0);
508 __declspec(dllexport
)
510 int sqlite3_completion_init(
513 const sqlite3_api_routines
*pApi
516 SQLITE_EXTENSION_INIT2(pApi
);
517 (void)(pzErrMsg
); /* Unused parameter */
518 #ifndef SQLITE_OMIT_VIRTUALTABLE
519 rc
= sqlite3CompletionVtabInit(db
);