/ Check-in [1cc97711]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 Unified Diffs 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
...
134
135
136
137
138
139
140

141
142
143
144
145
146
147
...
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
...
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
  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.
**
................................................................................
/*
** 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);
................................................................................
};

/*
** 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.
*/
................................................................................

/*
** 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().







|








>
|
|







 







>







 







>
>

>
|
>
>
>
|
>
>
>
|
>
|
<
<
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|


<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
<
<
|
|
|
|







 







|







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
...
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
...
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
...
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
  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.
**
................................................................................
/*
** 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);
................................................................................
};

/*
** 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.
*/
................................................................................

/*
** 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().