SQLite

Check-in [1cc97711fa]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 1cc97711fa86a3938f0930200476d1b0991e4b893a8be3a19015423a3de56bef
User & Date: drh 2017-07-11 01:38:45.049
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: 0e21355527 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: 1cc97711fa user: drh tags: trunk)
00:09
Incomplete implementation of the COMPLETION table-valued function. So far it only works for SQL keywords. (check-in: caefbc723b user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/misc/completion.c.
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

66
67
68
69
70
71
72
73
74
  sqlite3_int64 iRowid;      /* The rowid */
  int ePhase;                /* Current phase */
  int j;                     /* inter-phase counter */
};

/* Values for ePhase:
*/
#define COMPLETION_FIRST_PHASE   0
#define COMPLETION_KEYWORDS      1
#define COMPLETION_PRAGMAS       2
#define COMPLETION_FUNCTIONS     3
#define COMPLETION_COLLATIONS    4
#define COMPLETION_INDEXES       5
#define COMPLETION_TRIGGERS      6
#define COMPLETION_DATABASES     7
#define COMPLETION_TABLES        8

#define COMPLETION_MODULES       9
#define COMPLETION_LAST_PHASE    10

/*
** The completionConnect() method is invoked to create a new
** completion_vtab that describes the completion virtual table.
**
** Think of this routine as the constructor for completion_vtab objects.
**







|








>
|
|







50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
  sqlite3_int64 iRowid;      /* The rowid */
  int ePhase;                /* Current phase */
  int j;                     /* inter-phase counter */
};

/* Values for ePhase:
*/
#define COMPLETION_FIRST_PHASE   1
#define COMPLETION_KEYWORDS      1
#define COMPLETION_PRAGMAS       2
#define COMPLETION_FUNCTIONS     3
#define COMPLETION_COLLATIONS    4
#define COMPLETION_INDEXES       5
#define COMPLETION_TRIGGERS      6
#define COMPLETION_DATABASES     7
#define COMPLETION_TABLES        8
#define COMPLETION_COLUMNS       9
#define COMPLETION_MODULES       10
#define COMPLETION_EOF           11

/*
** The completionConnect() method is invoked to create a new
** completion_vtab that describes the completion virtual table.
**
** Think of this routine as the constructor for completion_vtab objects.
**
134
135
136
137
138
139
140

141
142
143
144
145
146
147
/*
** Reset the completion_cursor.
*/
static void completionCursorReset(completion_cursor *pCur){
  sqlite3_free(pCur->zPrefix);   pCur->zPrefix = 0;  pCur->nPrefix = 0;
  sqlite3_free(pCur->zLine);     pCur->zLine = 0;    pCur->nLine = 0;
  sqlite3_finalize(pCur->pStmt); pCur->pStmt = 0;

}

/*
** Destructor for a completion_cursor.
*/
static int completionClose(sqlite3_vtab_cursor *cur){
  completionCursorReset((completion_cursor*)cur);







>







135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
/*
** Reset the completion_cursor.
*/
static void completionCursorReset(completion_cursor *pCur){
  sqlite3_free(pCur->zPrefix);   pCur->zPrefix = 0;  pCur->nPrefix = 0;
  sqlite3_free(pCur->zLine);     pCur->zLine = 0;    pCur->nLine = 0;
  sqlite3_finalize(pCur->pStmt); pCur->pStmt = 0;
  pCur->j = 0;
}

/*
** Destructor for a completion_cursor.
*/
static int completionClose(sqlite3_vtab_cursor *cur){
  completionCursorReset((completion_cursor*)cur);
174
175
176
177
178
179
180


181

182



183



184

185

186





187





188
189












190



191
192
193
194
195
196











197


















198










199
200
201
202
203
204
205
206
207
208
209
};

/*
** Advance a completion_cursor to its next row of output.
*/
static int completionNext(sqlite3_vtab_cursor *cur){
  completion_cursor *pCur = (completion_cursor*)cur;


  pCur->iRowid++;

  if( pCur->ePhase==COMPLETION_FIRST_PHASE ){



    pCur->ePhase = COMPLETION_KEYWORDS;



    pCur->j = -1;

  }

  if( pCur->ePhase==COMPLETION_KEYWORDS ){





    while(1){





      const char *z;
      pCur->j++;












      if( pCur->j >=  sizeof(completionKwrds)/sizeof(completionKwrds[0]) ){



        pCur->ePhase = COMPLETION_LAST_PHASE;
        break;
      }
      z = completionKwrds[pCur->j];
      if( pCur->nPrefix==0 
       || sqlite3_strnicmp(pCur->zPrefix, z, pCur->nPrefix)==0











      ){


















        pCur->zCurrentRow = z;










        break;
      }
    }
  }
  return SQLITE_OK;
}

/*
** Return values of columns for the row at which the completion_cursor
** is currently pointing.
*/







>
>

>
|
>
>
>
|
>
>
>
|
>
|
>
|
>
>
>
>
>
|
>
>
>
>
>
|
|
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
|


|
|
|
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
|
|
|
|







176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
};

/*
** Advance a completion_cursor to its next row of output.
*/
static int completionNext(sqlite3_vtab_cursor *cur){
  completion_cursor *pCur = (completion_cursor*)cur;
  int eNextPhase = 0;/* Next phase to try if current phase reaches end */
  int iCol = -1;     /* If >=0 then step pCur->pStmt and use the i-th column */
  pCur->iRowid++;
  while( pCur->ePhase!=COMPLETION_EOF ){
    switch( pCur->ePhase ){
      case COMPLETION_KEYWORDS: {
        if( pCur->j >=  sizeof(completionKwrds)/sizeof(completionKwrds[0]) ){
          pCur->zCurrentRow = 0;
          pCur->ePhase = COMPLETION_DATABASES;
        }else{
          pCur->zCurrentRow = completionKwrds[pCur->j++];
        }
        iCol = -1;
        break;
      }
      case COMPLETION_DATABASES: {
        if( pCur->pStmt==0 ){
          sqlite3_prepare_v2(pCur->db, "PRAGMA database_list", -1,
                             &pCur->pStmt, 0);
        }
        iCol = 1;
        eNextPhase = COMPLETION_TABLES;
        break;
      }
      case COMPLETION_TABLES: {
        if( pCur->pStmt==0 ){
          sqlite3_stmt *pS2;
          char *zSql = 0;
          const char *zSep = "";
          sqlite3_prepare_v2(pCur->db, "PRAGMA database_list", -1, &pS2, 0);
          while( sqlite3_step(pS2)==SQLITE_ROW ){
            const char *zDb = (const char*)sqlite3_column_text(pS2, 1);
            zSql = sqlite3_mprintf(
               "%z%s"
               "SELECT name FROM \"%w\".sqlite_master"
               " WHERE type='table'",
               zSql, zSep, zDb
            );
            if( zSql==0 ) return SQLITE_NOMEM;
            zSep = " UNION ";
          }
          sqlite3_finalize(pS2);
          sqlite3_prepare_v2(pCur->db, zSql, -1, &pCur->pStmt, 0);
          sqlite3_free(zSql);
        }
        iCol = 0;
        eNextPhase = COMPLETION_COLUMNS;
        break;
      }
      case COMPLETION_COLUMNS: {
        if( pCur->pStmt==0 ){
          sqlite3_stmt *pS2;
          char *zSql = 0;
          const char *zSep = "";
          sqlite3_prepare_v2(pCur->db, "PRAGMA database_list", -1, &pS2, 0);
          while( sqlite3_step(pS2)==SQLITE_ROW ){
            const char *zDb = (const char*)sqlite3_column_text(pS2, 1);
            zSql = sqlite3_mprintf(
               "%z%s"
               "SELECT pti.name FROM \"%w\".sqlite_master AS sm"
                       " JOIN pragma_table_info(sm.name,%Q) AS pti"
               " WHERE sm.type='table'",
               zSql, zSep, zDb, zDb
            );
            if( zSql==0 ) return SQLITE_NOMEM;
            zSep = " UNION ";
          }
          sqlite3_finalize(pS2);
          sqlite3_prepare_v2(pCur->db, zSql, -1, &pCur->pStmt, 0);
          sqlite3_free(zSql);
        }
        iCol = 0;
        eNextPhase = COMPLETION_EOF;
        break;
      }
    }
    if( iCol<0 ){
      /* This case is when the phase presets zCurrentRow */
      if( pCur->zCurrentRow==0 ) continue;
    }else{
      if( sqlite3_step(pCur->pStmt)==SQLITE_ROW ){
        /* Extract the next row of content */
        pCur->zCurrentRow = (const char*)sqlite3_column_text(pCur->pStmt, iCol);
      }else{
        /* When all rows are finished, advance to the next phase */
        sqlite3_finalize(pCur->pStmt);
        pCur->pStmt = 0;
        pCur->ePhase = eNextPhase;
        continue;
      }
    }
    if( pCur->nPrefix==0 ) break;
    if( sqlite3_strnicmp(pCur->zPrefix, pCur->zCurrentRow, pCur->nPrefix)==0 ){
      break;
    }
  }

  return SQLITE_OK;
}

/*
** Return values of columns for the row at which the completion_cursor
** is currently pointing.
*/
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256

/*
** Return TRUE if the cursor has been moved off of the last
** row of output.
*/
static int completionEof(sqlite3_vtab_cursor *cur){
  completion_cursor *pCur = (completion_cursor*)cur;
  return pCur->ePhase >= COMPLETION_LAST_PHASE;
}

/*
** This method is called to "rewind" the completion_cursor object back
** to the first row of output.  This method is always called at least
** once prior to any call to completionColumn() or completionRowid() or 
** completionEof().







|







319
320
321
322
323
324
325
326
327
328
329
330
331
332
333

/*
** Return TRUE if the cursor has been moved off of the last
** row of output.
*/
static int completionEof(sqlite3_vtab_cursor *cur){
  completion_cursor *pCur = (completion_cursor*)cur;
  return pCur->ePhase >= COMPLETION_EOF;
}

/*
** This method is called to "rewind" the completion_cursor object back
** to the first row of output.  This method is always called at least
** once prior to any call to completionColumn() or completionRowid() or 
** completionEof().