/ Check-in [1cc97711]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:The COMPLETION virtual table now looks at the names of databases, tables, and columns in addition to SQL keywords.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 1cc97711fa86a3938f0930200476d1b0991e4b893a8be3a19015423a3de56bef
User & Date: drh 2017-07-11 01:38:45
Context
2017-07-11
02:05
Add the "phase" output column on the COMPLETION table-valued function, for debugging. Improved comments on the implementation. check-in: 0e213555 user: drh tags: trunk
01:38
The COMPLETION virtual table now looks at the names of databases, tables, and columns in addition to SQL keywords. check-in: 1cc97711 user: drh tags: trunk
00:09
Incomplete implementation of the COMPLETION table-valued function. So far it only works for SQL keywords. check-in: caefbc72 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/completion.c.

    50     50     sqlite3_int64 iRowid;      /* The rowid */
    51     51     int ePhase;                /* Current phase */
    52     52     int j;                     /* inter-phase counter */
    53     53   };
    54     54   
    55     55   /* Values for ePhase:
    56     56   */
    57         -#define COMPLETION_FIRST_PHASE   0
           57  +#define COMPLETION_FIRST_PHASE   1
    58     58   #define COMPLETION_KEYWORDS      1
    59     59   #define COMPLETION_PRAGMAS       2
    60     60   #define COMPLETION_FUNCTIONS     3
    61     61   #define COMPLETION_COLLATIONS    4
    62     62   #define COMPLETION_INDEXES       5
    63     63   #define COMPLETION_TRIGGERS      6
    64     64   #define COMPLETION_DATABASES     7
    65     65   #define COMPLETION_TABLES        8
    66         -#define COMPLETION_MODULES       9
    67         -#define COMPLETION_LAST_PHASE    10
           66  +#define COMPLETION_COLUMNS       9
           67  +#define COMPLETION_MODULES       10
           68  +#define COMPLETION_EOF           11
    68     69   
    69     70   /*
    70     71   ** The completionConnect() method is invoked to create a new
    71     72   ** completion_vtab that describes the completion virtual table.
    72     73   **
    73     74   ** Think of this routine as the constructor for completion_vtab objects.
    74     75   **
................................................................................
   134    135   /*
   135    136   ** Reset the completion_cursor.
   136    137   */
   137    138   static void completionCursorReset(completion_cursor *pCur){
   138    139     sqlite3_free(pCur->zPrefix);   pCur->zPrefix = 0;  pCur->nPrefix = 0;
   139    140     sqlite3_free(pCur->zLine);     pCur->zLine = 0;    pCur->nLine = 0;
   140    141     sqlite3_finalize(pCur->pStmt); pCur->pStmt = 0;
          142  +  pCur->j = 0;
   141    143   }
   142    144   
   143    145   /*
   144    146   ** Destructor for a completion_cursor.
   145    147   */
   146    148   static int completionClose(sqlite3_vtab_cursor *cur){
   147    149     completionCursorReset((completion_cursor*)cur);
................................................................................
   174    176   };
   175    177   
   176    178   /*
   177    179   ** Advance a completion_cursor to its next row of output.
   178    180   */
   179    181   static int completionNext(sqlite3_vtab_cursor *cur){
   180    182     completion_cursor *pCur = (completion_cursor*)cur;
          183  +  int eNextPhase = 0;/* Next phase to try if current phase reaches end */
          184  +  int iCol = -1;     /* If >=0 then step pCur->pStmt and use the i-th column */
   181    185     pCur->iRowid++;
   182         -  if( pCur->ePhase==COMPLETION_FIRST_PHASE ){
   183         -    pCur->ePhase = COMPLETION_KEYWORDS;
   184         -    pCur->j = -1;
   185         -  }
   186         -  if( pCur->ePhase==COMPLETION_KEYWORDS ){
   187         -    while(1){
   188         -      const char *z;
   189         -      pCur->j++;
   190         -      if( pCur->j >=  sizeof(completionKwrds)/sizeof(completionKwrds[0]) ){
   191         -        pCur->ePhase = COMPLETION_LAST_PHASE;
          186  +  while( pCur->ePhase!=COMPLETION_EOF ){
          187  +    switch( pCur->ePhase ){
          188  +      case COMPLETION_KEYWORDS: {
          189  +        if( pCur->j >=  sizeof(completionKwrds)/sizeof(completionKwrds[0]) ){
          190  +          pCur->zCurrentRow = 0;
          191  +          pCur->ePhase = COMPLETION_DATABASES;
          192  +        }else{
          193  +          pCur->zCurrentRow = completionKwrds[pCur->j++];
          194  +        }
          195  +        iCol = -1;
          196  +        break;
          197  +      }
          198  +      case COMPLETION_DATABASES: {
          199  +        if( pCur->pStmt==0 ){
          200  +          sqlite3_prepare_v2(pCur->db, "PRAGMA database_list", -1,
          201  +                             &pCur->pStmt, 0);
          202  +        }
          203  +        iCol = 1;
          204  +        eNextPhase = COMPLETION_TABLES;
          205  +        break;
          206  +      }
          207  +      case COMPLETION_TABLES: {
          208  +        if( pCur->pStmt==0 ){
          209  +          sqlite3_stmt *pS2;
          210  +          char *zSql = 0;
          211  +          const char *zSep = "";
          212  +          sqlite3_prepare_v2(pCur->db, "PRAGMA database_list", -1, &pS2, 0);
          213  +          while( sqlite3_step(pS2)==SQLITE_ROW ){
          214  +            const char *zDb = (const char*)sqlite3_column_text(pS2, 1);
          215  +            zSql = sqlite3_mprintf(
          216  +               "%z%s"
          217  +               "SELECT name FROM \"%w\".sqlite_master"
          218  +               " WHERE type='table'",
          219  +               zSql, zSep, zDb
          220  +            );
          221  +            if( zSql==0 ) return SQLITE_NOMEM;
          222  +            zSep = " UNION ";
          223  +          }
          224  +          sqlite3_finalize(pS2);
          225  +          sqlite3_prepare_v2(pCur->db, zSql, -1, &pCur->pStmt, 0);
          226  +          sqlite3_free(zSql);
          227  +        }
          228  +        iCol = 0;
          229  +        eNextPhase = COMPLETION_COLUMNS;
   192    230           break;
   193    231         }
   194         -      z = completionKwrds[pCur->j];
   195         -      if( pCur->nPrefix==0 
   196         -       || sqlite3_strnicmp(pCur->zPrefix, z, pCur->nPrefix)==0
   197         -      ){
   198         -        pCur->zCurrentRow = z;
          232  +      case COMPLETION_COLUMNS: {
          233  +        if( pCur->pStmt==0 ){
          234  +          sqlite3_stmt *pS2;
          235  +          char *zSql = 0;
          236  +          const char *zSep = "";
          237  +          sqlite3_prepare_v2(pCur->db, "PRAGMA database_list", -1, &pS2, 0);
          238  +          while( sqlite3_step(pS2)==SQLITE_ROW ){
          239  +            const char *zDb = (const char*)sqlite3_column_text(pS2, 1);
          240  +            zSql = sqlite3_mprintf(
          241  +               "%z%s"
          242  +               "SELECT pti.name FROM \"%w\".sqlite_master AS sm"
          243  +                       " JOIN pragma_table_info(sm.name,%Q) AS pti"
          244  +               " WHERE sm.type='table'",
          245  +               zSql, zSep, zDb, zDb
          246  +            );
          247  +            if( zSql==0 ) return SQLITE_NOMEM;
          248  +            zSep = " UNION ";
          249  +          }
          250  +          sqlite3_finalize(pS2);
          251  +          sqlite3_prepare_v2(pCur->db, zSql, -1, &pCur->pStmt, 0);
          252  +          sqlite3_free(zSql);
          253  +        }
          254  +        iCol = 0;
          255  +        eNextPhase = COMPLETION_EOF;
   199    256           break;
   200    257         }
   201    258       }
          259  +    if( iCol<0 ){
          260  +      /* This case is when the phase presets zCurrentRow */
          261  +      if( pCur->zCurrentRow==0 ) continue;
          262  +    }else{
          263  +      if( sqlite3_step(pCur->pStmt)==SQLITE_ROW ){
          264  +        /* Extract the next row of content */
          265  +        pCur->zCurrentRow = (const char*)sqlite3_column_text(pCur->pStmt, iCol);
          266  +      }else{
          267  +        /* When all rows are finished, advance to the next phase */
          268  +        sqlite3_finalize(pCur->pStmt);
          269  +        pCur->pStmt = 0;
          270  +        pCur->ePhase = eNextPhase;
          271  +        continue;
          272  +      }
          273  +    }
          274  +    if( pCur->nPrefix==0 ) break;
          275  +    if( sqlite3_strnicmp(pCur->zPrefix, pCur->zCurrentRow, pCur->nPrefix)==0 ){
          276  +      break;
          277  +    }
   202    278     }
          279  +
   203    280     return SQLITE_OK;
   204    281   }
   205    282   
   206    283   /*
   207    284   ** Return values of columns for the row at which the completion_cursor
   208    285   ** is currently pointing.
   209    286   */
................................................................................
   242    319   
   243    320   /*
   244    321   ** Return TRUE if the cursor has been moved off of the last
   245    322   ** row of output.
   246    323   */
   247    324   static int completionEof(sqlite3_vtab_cursor *cur){
   248    325     completion_cursor *pCur = (completion_cursor*)cur;
   249         -  return pCur->ePhase >= COMPLETION_LAST_PHASE;
          326  +  return pCur->ePhase >= COMPLETION_EOF;
   250    327   }
   251    328   
   252    329   /*
   253    330   ** This method is called to "rewind" the completion_cursor object back
   254    331   ** to the first row of output.  This method is always called at least
   255    332   ** once prior to any call to completionColumn() or completionRowid() or 
   256    333   ** completionEof().