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   rc = sqlite3_declare_vtab(db,
 122       "CREATE TABLE x("
 123       "  candidate TEXT,"
 124       "  prefix TEXT HIDDEN,"
 125       "  wholeline TEXT HIDDEN,"
 126       "  phase INT HIDDEN"        /* Used for debugging only */
 127       ")");
 128   if( rc==SQLITE_OK ){
 129     pNew = sqlite3_malloc( sizeof(*pNew) );
 130     *ppVtab = (sqlite3_vtab*)pNew;
 131     if( pNew==0 ) return SQLITE_NOMEM;
 132     memset(pNew, 0, sizeof(*pNew));
 133     pNew->db = db;
 134   }
 135   return rc;
 136 }
 137
 138 /*
 139 ** This method is the destructor for completion_cursor objects.
 140 */
 141 static int completionDisconnect(sqlite3_vtab *pVtab){
 142   sqlite3_free(pVtab);
 143   return SQLITE_OK;
 144 }
 145
 146 /*
 147 ** Constructor for a new completion_cursor object.
 148 */
 149 static int completionOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
 150   completion_cursor *pCur;
 151   pCur = sqlite3_malloc( sizeof(*pCur) );
 152   if( pCur==0 ) return SQLITE_NOMEM;
 153   memset(pCur, 0, sizeof(*pCur));
 154   pCur->db = ((completion_vtab*)p)->db;
 155   *ppCursor = &pCur->base;
 156   return SQLITE_OK;
 157 }
 158
 159 /*
 160 ** Reset the completion_cursor.
 161 */
 162 static void completionCursorReset(completion_cursor *pCur){
 163   sqlite3_free(pCur->zPrefix);   pCur->zPrefix = 0;  pCur->nPrefix = 0;
 164   sqlite3_free(pCur->zLine);     pCur->zLine = 0;    pCur->nLine = 0;
 165   sqlite3_finalize(pCur->pStmt); pCur->pStmt = 0;
 166   pCur->j = 0;
 167 }
 168
 169 /*
 170 ** Destructor for a completion_cursor.
 171 */
 172 static int completionClose(sqlite3_vtab_cursor *cur){
 173   completionCursorReset((completion_cursor*)cur);
 174   sqlite3_free(cur);
 175   return SQLITE_OK;
 176 }
 177
 178 /*
 179 ** Advance a completion_cursor to its next row of output.
 180 **
 181 ** The ->ePhase, ->j, and ->pStmt fields of the completion_cursor object
 182 ** record the current state of the scan.  This routine sets ->zCurrentRow
 183 ** to the current row of output and then returns.  If no more rows remain,
 184 ** then ->ePhase is set to COMPLETION_EOF which will signal the virtual
 185 ** table that has reached the end of its scan.
 186 **
 187 ** The current implementation just lists potential identifiers and
 188 ** keywords and filters them by zPrefix.  Future enhancements should
 189 ** take zLine into account to try to restrict the set of identifiers and
 190 ** keywords based on what would be legal at the current point of input.
 191 */
 192 static int completionNext(sqlite3_vtab_cursor *cur){
 193   completion_cursor *pCur = (completion_cursor*)cur;
 194   int eNextPhase = 0;  /* Next phase to try if current phase reaches end */
 195   int iCol = -1;       /* If >=0, step pCur->pStmt and use the i-th column */
 196   pCur->iRowid++;
 197   while( pCur->ePhase!=COMPLETION_EOF ){
 198     switch( pCur->ePhase ){
 199       case COMPLETION_KEYWORDS: {
 200         if( pCur->j >= sqlite3_keyword_count() ){
 201           pCur->zCurrentRow = 0;
 202           pCur->ePhase = COMPLETION_DATABASES;
 203         }else{
 204           sqlite3_keyword_name(pCur->j++, &pCur->zCurrentRow, &pCur->szRow);
 205         }
 206         iCol = -1;
 207         break;
 208       }
 209       case COMPLETION_DATABASES: {
 210         if( pCur->pStmt==0 ){
 211           sqlite3_prepare_v2(pCur->db, "PRAGMA database_list", -1,
 212                              &pCur->pStmt, 0);
 213         }
 214         iCol = 1;
 215         eNextPhase = COMPLETION_TABLES;
 216         break;
 217       }
 218       case COMPLETION_TABLES: {
 219         if( pCur->pStmt==0 ){
 220           sqlite3_stmt *pS2;
 221           char *zSql = 0;
 222           const char *zSep = "";
 223           sqlite3_prepare_v2(pCur->db, "PRAGMA database_list", -1, &pS2, 0);
 224           while( sqlite3_step(pS2)==SQLITE_ROW ){
 225             const char *zDb = (const char*)sqlite3_column_text(pS2, 1);
 226             zSql = sqlite3_mprintf(
 227                "%z%s"
 228                "SELECT name FROM \"%w\".sqlite_master",
 229                zSql, zSep, zDb
 230             );
 231             if( zSql==0 ) return SQLITE_NOMEM;
 232             zSep = " UNION ";
 233           }
 234           sqlite3_finalize(pS2);
 235           sqlite3_prepare_v2(pCur->db, zSql, -1, &pCur->pStmt, 0);
 236           sqlite3_free(zSql);
 237         }
 238         iCol = 0;
 239         eNextPhase = COMPLETION_COLUMNS;
 240         break;
 241       }
 242       case COMPLETION_COLUMNS: {
 243         if( pCur->pStmt==0 ){
 244           sqlite3_stmt *pS2;
 245           char *zSql = 0;
 246           const char *zSep = "";
 247           sqlite3_prepare_v2(pCur->db, "PRAGMA database_list", -1, &pS2, 0);
 248           while( sqlite3_step(pS2)==SQLITE_ROW ){
 249             const char *zDb = (const char*)sqlite3_column_text(pS2, 1);
 250             zSql = sqlite3_mprintf(
 251                "%z%s"
 252                "SELECT pti.name FROM \"%w\".sqlite_master AS sm"
 253                        " JOIN pragma_table_info(sm.name,%Q) AS pti"
 254                " WHERE sm.type='table'",
 255                zSql, zSep, zDb, zDb
 256             );
 257             if( zSql==0 ) return SQLITE_NOMEM;
 258             zSep = " UNION ";
 259           }
 260           sqlite3_finalize(pS2);
 261           sqlite3_prepare_v2(pCur->db, zSql, -1, &pCur->pStmt, 0);
 262           sqlite3_free(zSql);
 263         }
 264         iCol = 0;
 265         eNextPhase = COMPLETION_EOF;
 266         break;
 267       }
 268     }
 269     if( iCol<0 ){
 270       /* This case is when the phase presets zCurrentRow */
 271       if( pCur->zCurrentRow==0 ) continue;
 272     }else{
 273       if( sqlite3_step(pCur->pStmt)==SQLITE_ROW ){
 274         /* Extract the next row of content */
 275         pCur->zCurrentRow = (const char*)sqlite3_column_text(pCur->pStmt, iCol);
 276         pCur->szRow = sqlite3_column_bytes(pCur->pStmt, iCol);
 277       }else{
 278         /* When all rows are finished, advance to the next phase */
 279         sqlite3_finalize(pCur->pStmt);
 280         pCur->pStmt = 0;
 281         pCur->ePhase = eNextPhase;
 282         continue;
 283       }
 284     }
 285     if( pCur->nPrefix==0 ) break;
 286     if( pCur->nPrefix<=pCur->szRow
 287      && sqlite3_strnicmp(pCur->zPrefix, pCur->zCurrentRow, pCur->nPrefix)==0
 288     ){
 289       break;
 290     }
 291   }
 292
 293   return SQLITE_OK;
 294 }
 295
 296 /*
 297 ** Return values of columns for the row at which the completion_cursor
 298 ** is currently pointing.
 299 */
 300 static int completionColumn(
 301   sqlite3_vtab_cursor *cur,   /* The cursor */
 302   sqlite3_context *ctx,       /* First argument to sqlite3_result_...() */
 303   int i                       /* Which column to return */
 304 ){
 305   completion_cursor *pCur = (completion_cursor*)cur;
 306   switch( i ){
 307     case COMPLETION_COLUMN_CANDIDATE: {
 308       sqlite3_result_text(ctx, pCur->zCurrentRow, pCur->szRow,SQLITE_TRANSIENT);
 309       break;
 310     }
 311     case COMPLETION_COLUMN_PREFIX: {
 312       sqlite3_result_text(ctx, pCur->zPrefix, -1, SQLITE_TRANSIENT);
 313       break;
 314     }
 315     case COMPLETION_COLUMN_WHOLELINE: {
 316       sqlite3_result_text(ctx, pCur->zLine, -1, SQLITE_TRANSIENT);
 317       break;
 318     }
 319     case COMPLETION_COLUMN_PHASE: {
 320       sqlite3_result_int(ctx, pCur->ePhase);
 321       break;
 322     }
 323   }
 324   return SQLITE_OK;
 325 }
 326
 327 /*
 328 ** Return the rowid for the current row.  In this implementation, the
 329 ** rowid is the same as the output value.
 330 */
 331 static int completionRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
 332   completion_cursor *pCur = (completion_cursor*)cur;
 333   *pRowid = pCur->iRowid;
 334   return SQLITE_OK;
 335 }
 336
 337 /*
 338 ** Return TRUE if the cursor has been moved off of the last
 339 ** row of output.
 340 */
 341 static int completionEof(sqlite3_vtab_cursor *cur){
 342   completion_cursor *pCur = (completion_cursor*)cur;
 343   return pCur->ePhase >= COMPLETION_EOF;
 344 }
 345
 346 /*
 347 ** This method is called to "rewind" the completion_cursor object back
 348 ** to the first row of output.  This method is always called at least
 349 ** once prior to any call to completionColumn() or completionRowid() or
 350 ** completionEof().
 351 */
 352 static int completionFilter(
 353   sqlite3_vtab_cursor *pVtabCursor,
 354   int idxNum, const char *idxStr,
 355   int argc, sqlite3_value **argv
 356 ){
 357   completion_cursor *pCur = (completion_cursor *)pVtabCursor;
 358   int iArg = 0;
 359   (void)(idxStr);   /* Unused parameter */
 360   (void)(argc);     /* Unused parameter */
 361   completionCursorReset(pCur);
 362   if( idxNum & 1 ){
 363     pCur->nPrefix = sqlite3_value_bytes(argv[iArg]);
 364     if( pCur->nPrefix>0 ){
 365       pCur->zPrefix = sqlite3_mprintf("%s", sqlite3_value_text(argv[iArg]));
 366       if( pCur->zPrefix==0 ) return SQLITE_NOMEM;
 367     }
 368     iArg++;
 369   }
 370   if( idxNum & 2 ){
 371     pCur->nLine = sqlite3_value_bytes(argv[iArg]);
 372     if( pCur->nLine>0 ){
 373       pCur->zLine = sqlite3_mprintf("%s", sqlite3_value_text(argv[iArg]));
 374       if( pCur->zLine==0 ) return SQLITE_NOMEM;
 375     }
 376     iArg++;
 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 };
 474
 475 #endif /* SQLITE_OMIT_VIRTUALTABLE */
 476
 477 int sqlite3CompletionVtabInit(sqlite3 *db){
 478   int rc = SQLITE_OK;
 479 #ifndef SQLITE_OMIT_VIRTUALTABLE
 480   rc = sqlite3_create_module(db, "completion", &completionModule, 0);
 481 #endif
 482   return rc;
 483 }
 484
 485 #ifdef _WIN32
 486 __declspec(dllexport)
 487 #endif
 488 int sqlite3_completion_init(
 489   sqlite3 *db,
 490   char **pzErrMsg,
 491   const sqlite3_api_routines *pApi
 492 ){
 493   int rc = SQLITE_OK;
 494   SQLITE_EXTENSION_INIT2(pApi);
 495   (void)(pzErrMsg);  /* Unused parameter */
 496 #ifndef SQLITE_OMIT_VIRTUALTABLE
 497   rc = sqlite3CompletionVtabInit(db);
 498 #endif
 499   return rc;
 500 }