ext/misc/completion.c

   1 /*
   2 ** 2017-07-10
   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 implements an eponymous virtual table that returns suggested
  14 ** completions for a partial SQL input.
  15 **
  16 ** Suggested usage:
  17 **
  18 **     SELECT DISTINCT candidate COLLATE nocase
  19 **       FROM completion($prefix,$wholeline)
  20 **      ORDER BY 1;
  21 **
  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.
  25 **
  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.
  30 **
  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.
  34 **
  35 */
  36 #include "sqlite3ext.h"
  37 SQLITE_EXTENSION_INIT1
  38 #include <assert.h>
  39 #include <string.h>
  40 #include <ctype.h>
  41
  42 #ifndef SQLITE_OMIT_VIRTUALTABLE
  43
  44 /* completion_vtab is a subclass of sqlite3_vtab which will
  45 ** serve as the underlying representation of a completion virtual table
  46 */
  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 */
  51 };
  52
  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
  56 */
  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   int szRow;                 /* Length of the zCurrentRow string */
  66   sqlite3_stmt *pStmt;       /* Current statement */
  67   sqlite3_int64 iRowid;      /* The rowid */
  68   int ePhase;                /* Current phase */
  69   int j;                     /* inter-phase counter */
  70 };
  71
  72 /* Values for ePhase:
  73 */
  74 #define COMPLETION_FIRST_PHASE   1
  75 #define COMPLETION_KEYWORDS      1
  76 #define COMPLETION_PRAGMAS       2
  77 #define COMPLETION_FUNCTIONS     3
  78 #define COMPLETION_COLLATIONS    4
  79 #define COMPLETION_INDEXES       5
  80 #define COMPLETION_TRIGGERS      6
  81 #define COMPLETION_DATABASES     7
  82 #define COMPLETION_TABLES        8    /* Also VIEWs and TRIGGERs */
  83 #define COMPLETION_COLUMNS       9
  84 #define COMPLETION_MODULES       10
  85 #define COMPLETION_EOF           11
  86
  87 /*
  88 ** The completionConnect() method is invoked to create a new
  89 ** completion_vtab that describes the completion virtual table.
  90 **
  91 ** Think of this routine as the constructor for completion_vtab objects.
  92 **
  93 ** All this routine needs to do is:
  94 **
  95 **    (1) Allocate the completion_vtab object and initialize all fields.
  96 **
  97 **    (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the
  98 **        result set of queries against completion will look like.
  99 */
 100 static int completionConnect(
 101   sqlite3 *db,
 102   void *pAux,
 103   int argc, const char *const*argv,
 104   sqlite3_vtab **ppVtab,
 105   char **pzErr
 106 ){
 107   completion_vtab *pNew;
 108   int rc;
 109
 110   (void)(pAux);    /* Unused parameter */
 111   (void)(argc);    /* Unused parameter */
 112   (void)(argv);    /* Unused parameter */
 113   (void)(pzErr);   /* Unused parameter */
 114
 115 /* Column numbers */
 116 #define COMPLETION_COLUMN_CANDIDATE 0  /* Suggested completion of the input */
 117 #define COMPLETION_COLUMN_PREFIX    1  /* Prefix of the word to be completed */
 118 #define COMPLETION_COLUMN_WHOLELINE 2  /* Entire line seen so far */
 119 #define COMPLETION_COLUMN_PHASE     3  /* ePhase - used for debugging only */
 120
 121   sqlite3_vtab_config(db, SQLITE_VTAB_INNOCUOUS);
 122   rc = sqlite3_declare_vtab(db,
 123       "CREATE TABLE x("
 124       "  candidate TEXT,"
 125       "  prefix TEXT HIDDEN,"
 126       "  wholeline TEXT HIDDEN,"
 127       "  phase INT HIDDEN"        /* Used for debugging only */
 128       ")");
 129   if( rc==SQLITE_OK ){
 130     pNew = sqlite3_malloc( sizeof(*pNew) );
 131     *ppVtab = (sqlite3_vtab*)pNew;
 132     if( pNew==0 ) return SQLITE_NOMEM;
 133     memset(pNew, 0, sizeof(*pNew));
 134     pNew->db = db;
 135   }
 136   return rc;
 137 }
 138
 139 /*
 140 ** This method is the destructor for completion_cursor objects.
 141 */
 142 static int completionDisconnect(sqlite3_vtab *pVtab){
 143   sqlite3_free(pVtab);
 144   return SQLITE_OK;
 145 }
 146
 147 /*
 148 ** Constructor for a new completion_cursor object.
 149 */
 150 static int completionOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
 151   completion_cursor *pCur;
 152   pCur = sqlite3_malloc( sizeof(*pCur) );
 153   if( pCur==0 ) return SQLITE_NOMEM;
 154   memset(pCur, 0, sizeof(*pCur));
 155   pCur->db = ((completion_vtab*)p)->db;
 156   *ppCursor = &pCur->base;
 157   return SQLITE_OK;
 158 }
 159
 160 /*
 161 ** Reset the completion_cursor.
 162 */
 163 static void completionCursorReset(completion_cursor *pCur){
 164   sqlite3_free(pCur->zPrefix);   pCur->zPrefix = 0;  pCur->nPrefix = 0;
 165   sqlite3_free(pCur->zLine);     pCur->zLine = 0;    pCur->nLine = 0;
 166   sqlite3_finalize(pCur->pStmt); pCur->pStmt = 0;
 167   pCur->j = 0;
 168 }
 169
 170 /*
 171 ** Destructor for a completion_cursor.
 172 */
 173 static int completionClose(sqlite3_vtab_cursor *cur){
 174   completionCursorReset((completion_cursor*)cur);
 175   sqlite3_free(cur);
 176   return SQLITE_OK;
 177 }
 178
 179 /*
 180 ** Advance a completion_cursor to its next row of output.
 181 **
 182 ** The ->ePhase, ->j, and ->pStmt fields of the completion_cursor object
 183 ** record the current state of the scan.  This routine sets ->zCurrentRow
 184 ** to the current row of output and then returns.  If no more rows remain,
 185 ** then ->ePhase is set to COMPLETION_EOF which will signal the virtual
 186 ** table that has reached the end of its scan.
 187 **
 188 ** The current implementation just lists potential identifiers and
 189 ** keywords and filters them by zPrefix.  Future enhancements should
 190 ** take zLine into account to try to restrict the set of identifiers and
 191 ** keywords based on what would be legal at the current point of input.
 192 */
 193 static int completionNext(sqlite3_vtab_cursor *cur){
 194   completion_cursor *pCur = (completion_cursor*)cur;
 195   int eNextPhase = 0;  /* Next phase to try if current phase reaches end */
 196   int iCol = -1;       /* If >=0, step pCur->pStmt and use the i-th column */
 197   pCur->iRowid++;
 198   while( pCur->ePhase!=COMPLETION_EOF ){
 199     switch( pCur->ePhase ){
 200       case COMPLETION_KEYWORDS: {
 201         if( pCur->j >= sqlite3_keyword_count() ){
 202           pCur->zCurrentRow = 0;
 203           pCur->ePhase = COMPLETION_DATABASES;
 204         }else{
 205           sqlite3_keyword_name(pCur->j++, &pCur->zCurrentRow, &pCur->szRow);
 206         }
 207         iCol = -1;
 208         break;
 209       }
 210       case COMPLETION_DATABASES: {
 211         if( pCur->pStmt==0 ){
 212           sqlite3_prepare_v2(pCur->db, "PRAGMA database_list", -1,
 213                              &pCur->pStmt, 0);
 214         }
 215         iCol = 1;
 216         eNextPhase = COMPLETION_TABLES;
 217         break;
 218       }
 219       case COMPLETION_TABLES: {
 220         if( pCur->pStmt==0 ){
 221           sqlite3_stmt *pS2;
 222           char *zSql = 0;
 223           const char *zSep = "";
 224           sqlite3_prepare_v2(pCur->db, "PRAGMA database_list", -1, &pS2, 0);
 225           while( sqlite3_step(pS2)==SQLITE_ROW ){
 226             const char *zDb = (const char*)sqlite3_column_text(pS2, 1);
 227             zSql = sqlite3_mprintf(
 228                "%z%s"
 229                "SELECT name FROM \"%w\".sqlite_master",
 230                zSql, zSep, zDb
 231             );
 232             if( zSql==0 ) return SQLITE_NOMEM;
 233             zSep = " UNION ";
 234           }
 235           sqlite3_finalize(pS2);
 236           sqlite3_prepare_v2(pCur->db, zSql, -1, &pCur->pStmt, 0);
 237           sqlite3_free(zSql);
 238         }
 239         iCol = 0;
 240         eNextPhase = COMPLETION_COLUMNS;
 241         break;
 242       }
 243       case COMPLETION_COLUMNS: {
 244         if( pCur->pStmt==0 ){
 245           sqlite3_stmt *pS2;
 246           char *zSql = 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(
 252                "%z%s"
 253                "SELECT pti.name FROM \"%w\".sqlite_master AS sm"
 254                        " JOIN pragma_table_info(sm.name,%Q) AS pti"
 255                " WHERE sm.type='table'",
 256                zSql, zSep, zDb, zDb
 257             );
 258             if( zSql==0 ) return SQLITE_NOMEM;
 259             zSep = " UNION ";
 260           }
 261           sqlite3_finalize(pS2);
 262           sqlite3_prepare_v2(pCur->db, zSql, -1, &pCur->pStmt, 0);
 263           sqlite3_free(zSql);
 264         }
 265         iCol = 0;
 266         eNextPhase = COMPLETION_EOF;
 267         break;
 268       }
 269     }
 270     if( iCol<0 ){
 271       /* This case is when the phase presets zCurrentRow */
 272       if( pCur->zCurrentRow==0 ) continue;
 273     }else{
 274       if( sqlite3_step(pCur->pStmt)==SQLITE_ROW ){
 275         /* Extract the next row of content */
 276         pCur->zCurrentRow = (const char*)sqlite3_column_text(pCur->pStmt, iCol);
 277         pCur->szRow = sqlite3_column_bytes(pCur->pStmt, iCol);
 278       }else{
 279         /* When all rows are finished, advance to the next phase */
 280         sqlite3_finalize(pCur->pStmt);
 281         pCur->pStmt = 0;
 282         pCur->ePhase = eNextPhase;
 283         continue;
 284       }
 285     }
 286     if( pCur->nPrefix==0 ) break;
 287     if( pCur->nPrefix<=pCur->szRow
 288      && sqlite3_strnicmp(pCur->zPrefix, pCur->zCurrentRow, pCur->nPrefix)==0
 289     ){
 290       break;
 291     }
 292   }
 293
 294   return SQLITE_OK;
 295 }
 296
 297 /*
 298 ** Return values of columns for the row at which the completion_cursor
 299 ** is currently pointing.
 300 */
 301 static int completionColumn(
 302   sqlite3_vtab_cursor *cur,   /* The cursor */
 303   sqlite3_context *ctx,       /* First argument to sqlite3_result_...() */
 304   int i                       /* Which column to return */
 305 ){
 306   completion_cursor *pCur = (completion_cursor*)cur;
 307   switch( i ){
 308     case COMPLETION_COLUMN_CANDIDATE: {
 309       sqlite3_result_text(ctx, pCur->zCurrentRow, pCur->szRow,SQLITE_TRANSIENT);
 310       break;
 311     }
 312     case COMPLETION_COLUMN_PREFIX: {
 313       sqlite3_result_text(ctx, pCur->zPrefix, -1, SQLITE_TRANSIENT);
 314       break;
 315     }
 316     case COMPLETION_COLUMN_WHOLELINE: {
 317       sqlite3_result_text(ctx, pCur->zLine, -1, SQLITE_TRANSIENT);
 318       break;
 319     }
 320     case COMPLETION_COLUMN_PHASE: {
 321       sqlite3_result_int(ctx, pCur->ePhase);
 322       break;
 323     }
 324   }
 325   return SQLITE_OK;
 326 }
 327
 328 /*
 329 ** Return the rowid for the current row.  In this implementation, the
 330 ** rowid is the same as the output value.
 331 */
 332 static int completionRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
 333   completion_cursor *pCur = (completion_cursor*)cur;
 334   *pRowid = pCur->iRowid;
 335   return SQLITE_OK;
 336 }
 337
 338 /*
 339 ** Return TRUE if the cursor has been moved off of the last
 340 ** row of output.
 341 */
 342 static int completionEof(sqlite3_vtab_cursor *cur){
 343   completion_cursor *pCur = (completion_cursor*)cur;
 344   return pCur->ePhase >= COMPLETION_EOF;
 345 }
 346
 347 /*
 348 ** This method is called to "rewind" the completion_cursor object back
 349 ** to the first row of output.  This method is always called at least
 350 ** once prior to any call to completionColumn() or completionRowid() or
 351 ** completionEof().
 352 */
 353 static int completionFilter(
 354   sqlite3_vtab_cursor *pVtabCursor,
 355   int idxNum, const char *idxStr,
 356   int argc, sqlite3_value **argv
 357 ){
 358   completion_cursor *pCur = (completion_cursor *)pVtabCursor;
 359   int iArg = 0;
 360   (void)(idxStr);   /* Unused parameter */
 361   (void)(argc);     /* Unused parameter */
 362   completionCursorReset(pCur);
 363   if( idxNum & 1 ){
 364     pCur->nPrefix = sqlite3_value_bytes(argv[iArg]);
 365     if( pCur->nPrefix>0 ){
 366       pCur->zPrefix = sqlite3_mprintf("%s", sqlite3_value_text(argv[iArg]));
 367       if( pCur->zPrefix==0 ) return SQLITE_NOMEM;
 368     }
 369     iArg = 1;
 370   }
 371   if( idxNum & 2 ){
 372     pCur->nLine = sqlite3_value_bytes(argv[iArg]);
 373     if( pCur->nLine>0 ){
 374       pCur->zLine = sqlite3_mprintf("%s", sqlite3_value_text(argv[iArg]));
 375       if( pCur->zLine==0 ) return SQLITE_NOMEM;
 376     }
 377   }
 378   if( pCur->zLine!=0 && pCur->zPrefix==0 ){
 379     int i = pCur->nLine;
 380     while( i>0 && (isalnum(pCur->zLine[i-1]) || pCur->zLine[i-1]=='_') ){
 381       i--;
 382     }
 383     pCur->nPrefix = pCur->nLine - i;
 384     if( pCur->nPrefix>0 ){
 385       pCur->zPrefix = sqlite3_mprintf("%.*s", pCur->nPrefix, pCur->zLine + i);
 386       if( pCur->zPrefix==0 ) return SQLITE_NOMEM;
 387     }
 388   }
 389   pCur->iRowid = 0;
 390   pCur->ePhase = COMPLETION_FIRST_PHASE;
 391   return completionNext(pVtabCursor);
 392 }
 393
 394 /*
 395 ** SQLite will invoke this method one or more times while planning a query
 396 ** that uses the completion virtual table.  This routine needs to create
 397 ** a query plan for each invocation and compute an estimated cost for that
 398 ** plan.
 399 **
 400 ** There are two hidden parameters that act as arguments to the table-valued
 401 ** function:  "prefix" and "wholeline".  Bit 0 of idxNum is set if "prefix"
 402 ** is available and bit 1 is set if "wholeline" is available.
 403 */
 404 static int completionBestIndex(
 405   sqlite3_vtab *tab,
 406   sqlite3_index_info *pIdxInfo
 407 ){
 408   int i;                 /* Loop over constraints */
 409   int idxNum = 0;        /* The query plan bitmask */
 410   int prefixIdx = -1;    /* Index of the start= constraint, or -1 if none */
 411   int wholelineIdx = -1; /* Index of the stop= constraint, or -1 if none */
 412   int nArg = 0;          /* Number of arguments that completeFilter() expects */
 413   const struct sqlite3_index_constraint *pConstraint;
 414
 415   (void)(tab);    /* Unused parameter */
 416   pConstraint = pIdxInfo->aConstraint;
 417   for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
 418     if( pConstraint->usable==0 ) continue;
 419     if( pConstraint->op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue;
 420     switch( pConstraint->iColumn ){
 421       case COMPLETION_COLUMN_PREFIX:
 422         prefixIdx = i;
 423         idxNum |= 1;
 424         break;
 425       case COMPLETION_COLUMN_WHOLELINE:
 426         wholelineIdx = i;
 427         idxNum |= 2;
 428         break;
 429     }
 430   }
 431   if( prefixIdx>=0 ){
 432     pIdxInfo->aConstraintUsage[prefixIdx].argvIndex = ++nArg;
 433     pIdxInfo->aConstraintUsage[prefixIdx].omit = 1;
 434   }
 435   if( wholelineIdx>=0 ){
 436     pIdxInfo->aConstraintUsage[wholelineIdx].argvIndex = ++nArg;
 437     pIdxInfo->aConstraintUsage[wholelineIdx].omit = 1;
 438   }
 439   pIdxInfo->idxNum = idxNum;
 440   pIdxInfo->estimatedCost = (double)5000 - 1000*nArg;
 441   pIdxInfo->estimatedRows = 500 - 100*nArg;
 442   return SQLITE_OK;
 443 }
 444
 445 /*
 446 ** This following structure defines all the methods for the
 447 ** completion virtual table.
 448 */
 449 static sqlite3_module completionModule = {
 450   0,                         /* iVersion */
 451   0,                         /* xCreate */
 452   completionConnect,         /* xConnect */
 453   completionBestIndex,       /* xBestIndex */
 454   completionDisconnect,      /* xDisconnect */
 455   0,                         /* xDestroy */
 456   completionOpen,            /* xOpen - open a cursor */
 457   completionClose,           /* xClose - close a cursor */
 458   completionFilter,          /* xFilter - configure scan constraints */
 459   completionNext,            /* xNext - advance a cursor */
 460   completionEof,             /* xEof - check for end of scan */
 461   completionColumn,          /* xColumn - read data */
 462   completionRowid,           /* xRowid - read data */
 463   0,                         /* xUpdate */
 464   0,                         /* xBegin */
 465   0,                         /* xSync */
 466   0,                         /* xCommit */
 467   0,                         /* xRollback */
 468   0,                         /* xFindMethod */
 469   0,                         /* xRename */
 470   0,                         /* xSavepoint */
 471   0,                         /* xRelease */
 472   0,                         /* xRollbackTo */
 473   0                          /* xShadowName */
 474 };
 475
 476 #endif /* SQLITE_OMIT_VIRTUALTABLE */
 477
 478 int sqlite3CompletionVtabInit(sqlite3 *db){
 479   int rc = SQLITE_OK;
 480 #ifndef SQLITE_OMIT_VIRTUALTABLE
 481   rc = sqlite3_create_module(db, "completion", &completionModule, 0);
 482 #endif
 483   return rc;
 484 }
 485
 486 #ifdef _WIN32
 487 __declspec(dllexport)
 488 #endif
 489 int sqlite3_completion_init(
 490   sqlite3 *db,
 491   char **pzErrMsg,
 492   const sqlite3_api_routines *pApi
 493 ){
 494   int rc = SQLITE_OK;
 495   SQLITE_EXTENSION_INIT2(pApi);
 496   (void)(pzErrMsg);  /* Unused parameter */
 497 #ifndef SQLITE_OMIT_VIRTUALTABLE
 498   rc = sqlite3CompletionVtabInit(db);
 499 #endif
 500   return rc;
 501 }