1 /*
2 ** 2016-06-29
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 demonstrates how to create a table-valued-function that
14 ** returns the values in a C-language array.
15 ** Examples:
16 **
17 ** SELECT * FROM carray($ptr,5)
18 **
19 ** The query above returns 5 integers contained in a C-language array
20 ** at the address $ptr. $ptr is a pointer to the array of integers.
21 ** The pointer value must be assigned to $ptr using the
22 ** sqlite3_bind_pointer() interface with a pointer type of "carray".
23 ** For example:
24 **
25 ** static int aX[] = { 53, 9, 17, 2231, 4, 99 };
26 ** int i = sqlite3_bind_parameter_index(pStmt, "$ptr");
27 ** sqlite3_bind_pointer(pStmt, i, aX, "carray", 0);
28 **
29 ** There is an optional third parameter to determine the datatype of
30 ** the C-language array. Allowed values of the third parameter are
31 ** 'int32', 'int64', 'double', 'char*'. Example:
32 **
33 ** SELECT * FROM carray($ptr,10,'char*');
34 **
35 ** The default value of the third parameter is 'int32'.
36 **
37 ** HOW IT WORKS
38 **
39 ** The carray "function" is really a virtual table with the
40 ** following schema:
41 **
42 ** CREATE TABLE carray(
43 ** value,
44 ** pointer HIDDEN,
45 ** count HIDDEN,
46 ** ctype TEXT HIDDEN
47 ** );
48 **
49 ** If the hidden columns "pointer" and "count" are unconstrained, then
50 ** the virtual table has no rows. Otherwise, the virtual table interprets
51 ** the integer value of "pointer" as a pointer to the array and "count"
52 ** as the number of elements in the array. The virtual table steps through
53 ** the array, element by element.
54 */
55 #include "sqlite3ext.h"
56 SQLITE_EXTENSION_INIT1
57 #include <assert.h>
58 #include <string.h>
59
60 /* Allowed values for the mFlags parameter to sqlite3_carray_bind().
61 ** Must exactly match the definitions in carray.h.
62 */
63 #ifndef CARRAY_INT32
64 # define CARRAY_INT32 0 /* Data is 32-bit signed integers */
65 # define CARRAY_INT64 1 /* Data is 64-bit signed integers */
66 # define CARRAY_DOUBLE 2 /* Data is doubles */
67 # define CARRAY_TEXT 3 /* Data is char* */
68 #endif
69
70 #ifndef SQLITE_API
71 # ifdef _WIN32
72 # define SQLITE_API __declspec(dllexport)
73 # else
74 # define SQLITE_API
75 # endif
76 #endif
77
78 #ifndef SQLITE_OMIT_VIRTUALTABLE
79
80 /*
81 ** Names of allowed datatypes
82 */
83 static const char *azType[] = { "int32", "int64", "double", "char*" };
84
85 /*
86 ** Structure used to hold the sqlite3_carray_bind() information
87 */
88 typedef struct carray_bind carray_bind;
89 struct carray_bind {
90 void *aData; /* The data */
91 int nData; /* Number of elements */
92 int mFlags; /* Control flags */
93 void (*xDel)(void*); /* Destructor for aData */
94 };
95
96
97 /* carray_cursor is a subclass of sqlite3_vtab_cursor which will
98 ** serve as the underlying representation of a cursor that scans
99 ** over rows of the result
100 */
101 typedef struct carray_cursor carray_cursor;
102 struct carray_cursor {
103 sqlite3_vtab_cursor base; /* Base class - must be first */
104 sqlite3_int64 iRowid; /* The rowid */
105 void *pPtr; /* Pointer to the array of values */
106 sqlite3_int64 iCnt; /* Number of integers in the array */
107 unsigned char eType; /* One of the CARRAY_type values */
108 };
109
110 /*
111 ** The carrayConnect() method is invoked to create a new
112 ** carray_vtab that describes the carray virtual table.
113 **
114 ** Think of this routine as the constructor for carray_vtab objects.
115 **
116 ** All this routine needs to do is:
117 **
118 ** (1) Allocate the carray_vtab object and initialize all fields.
119 **
120 ** (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the
121 ** result set of queries against carray will look like.
122 */
carrayConnect(sqlite3 * db,void * pAux,int argc,const char * const * argv,sqlite3_vtab ** ppVtab,char ** pzErr)123 static int carrayConnect(
124 sqlite3 *db,
125 void *pAux,
126 int argc, const char *const*argv,
127 sqlite3_vtab **ppVtab,
128 char **pzErr
129 ){
130 sqlite3_vtab *pNew;
131 int rc;
132
133 /* Column numbers */
134 #define CARRAY_COLUMN_VALUE 0
135 #define CARRAY_COLUMN_POINTER 1
136 #define CARRAY_COLUMN_COUNT 2
137 #define CARRAY_COLUMN_CTYPE 3
138
139 rc = sqlite3_declare_vtab(db,
140 "CREATE TABLE x(value,pointer hidden,count hidden,ctype hidden)");
141 if( rc==SQLITE_OK ){
142 pNew = *ppVtab = sqlite3_malloc( sizeof(*pNew) );
143 if( pNew==0 ) return SQLITE_NOMEM;
144 memset(pNew, 0, sizeof(*pNew));
145 }
146 return rc;
147 }
148
149 /*
150 ** This method is the destructor for carray_cursor objects.
151 */
carrayDisconnect(sqlite3_vtab * pVtab)152 static int carrayDisconnect(sqlite3_vtab *pVtab){
153 sqlite3_free(pVtab);
154 return SQLITE_OK;
155 }
156
157 /*
158 ** Constructor for a new carray_cursor object.
159 */
carrayOpen(sqlite3_vtab * p,sqlite3_vtab_cursor ** ppCursor)160 static int carrayOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){
161 carray_cursor *pCur;
162 pCur = sqlite3_malloc( sizeof(*pCur) );
163 if( pCur==0 ) return SQLITE_NOMEM;
164 memset(pCur, 0, sizeof(*pCur));
165 *ppCursor = &pCur->base;
166 return SQLITE_OK;
167 }
168
169 /*
170 ** Destructor for a carray_cursor.
171 */
carrayClose(sqlite3_vtab_cursor * cur)172 static int carrayClose(sqlite3_vtab_cursor *cur){
173 sqlite3_free(cur);
174 return SQLITE_OK;
175 }
176
177
178 /*
179 ** Advance a carray_cursor to its next row of output.
180 */
carrayNext(sqlite3_vtab_cursor * cur)181 static int carrayNext(sqlite3_vtab_cursor *cur){
182 carray_cursor *pCur = (carray_cursor*)cur;
183 pCur->iRowid++;
184 return SQLITE_OK;
185 }
186
187 /*
188 ** Return values of columns for the row at which the carray_cursor
189 ** is currently pointing.
190 */
carrayColumn(sqlite3_vtab_cursor * cur,sqlite3_context * ctx,int i)191 static int carrayColumn(
192 sqlite3_vtab_cursor *cur, /* The cursor */
193 sqlite3_context *ctx, /* First argument to sqlite3_result_...() */
194 int i /* Which column to return */
195 ){
196 carray_cursor *pCur = (carray_cursor*)cur;
197 sqlite3_int64 x = 0;
198 switch( i ){
199 case CARRAY_COLUMN_POINTER: return SQLITE_OK;
200 case CARRAY_COLUMN_COUNT: x = pCur->iCnt; break;
201 case CARRAY_COLUMN_CTYPE: {
202 sqlite3_result_text(ctx, azType[pCur->eType], -1, SQLITE_STATIC);
203 return SQLITE_OK;
204 }
205 default: {
206 switch( pCur->eType ){
207 case CARRAY_INT32: {
208 int *p = (int*)pCur->pPtr;
209 sqlite3_result_int(ctx, p[pCur->iRowid-1]);
210 return SQLITE_OK;
211 }
212 case CARRAY_INT64: {
213 sqlite3_int64 *p = (sqlite3_int64*)pCur->pPtr;
214 sqlite3_result_int64(ctx, p[pCur->iRowid-1]);
215 return SQLITE_OK;
216 }
217 case CARRAY_DOUBLE: {
218 double *p = (double*)pCur->pPtr;
219 sqlite3_result_double(ctx, p[pCur->iRowid-1]);
220 return SQLITE_OK;
221 }
222 case CARRAY_TEXT: {
223 const char **p = (const char**)pCur->pPtr;
224 sqlite3_result_text(ctx, p[pCur->iRowid-1], -1, SQLITE_TRANSIENT);
225 return SQLITE_OK;
226 }
227 }
228 }
229 }
230 sqlite3_result_int64(ctx, x);
231 return SQLITE_OK;
232 }
233
234 /*
235 ** Return the rowid for the current row. In this implementation, the
236 ** rowid is the same as the output value.
237 */
carrayRowid(sqlite3_vtab_cursor * cur,sqlite_int64 * pRowid)238 static int carrayRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
239 carray_cursor *pCur = (carray_cursor*)cur;
240 *pRowid = pCur->iRowid;
241 return SQLITE_OK;
242 }
243
244 /*
245 ** Return TRUE if the cursor has been moved off of the last
246 ** row of output.
247 */
carrayEof(sqlite3_vtab_cursor * cur)248 static int carrayEof(sqlite3_vtab_cursor *cur){
249 carray_cursor *pCur = (carray_cursor*)cur;
250 return pCur->iRowid>pCur->iCnt;
251 }
252
253 /*
254 ** This method is called to "rewind" the carray_cursor object back
255 ** to the first row of output.
256 */
carrayFilter(sqlite3_vtab_cursor * pVtabCursor,int idxNum,const char * idxStr,int argc,sqlite3_value ** argv)257 static int carrayFilter(
258 sqlite3_vtab_cursor *pVtabCursor,
259 int idxNum, const char *idxStr,
260 int argc, sqlite3_value **argv
261 ){
262 carray_cursor *pCur = (carray_cursor *)pVtabCursor;
263 pCur->pPtr = 0;
264 pCur->iCnt = 0;
265 switch( idxNum ){
266 case 1: {
267 carray_bind *pBind = sqlite3_value_pointer(argv[0], "carray-bind");
268 if( pBind==0 ) break;
269 pCur->pPtr = pBind->aData;
270 pCur->iCnt = pBind->nData;
271 pCur->eType = pBind->mFlags & 0x03;
272 break;
273 }
274 case 2:
275 case 3: {
276 pCur->pPtr = sqlite3_value_pointer(argv[0], "carray");
277 pCur->iCnt = pCur->pPtr ? sqlite3_value_int64(argv[1]) : 0;
278 if( idxNum<3 ){
279 pCur->eType = CARRAY_INT32;
280 }else{
281 unsigned char i;
282 const char *zType = (const char*)sqlite3_value_text(argv[2]);
283 for(i=0; i<sizeof(azType)/sizeof(azType[0]); i++){
284 if( sqlite3_stricmp(zType, azType[i])==0 ) break;
285 }
286 if( i>=sizeof(azType)/sizeof(azType[0]) ){
287 pVtabCursor->pVtab->zErrMsg = sqlite3_mprintf(
288 "unknown datatype: %Q", zType);
289 return SQLITE_ERROR;
290 }else{
291 pCur->eType = i;
292 }
293 }
294 break;
295 }
296 }
297 pCur->iRowid = 1;
298 return SQLITE_OK;
299 }
300
301 /*
302 ** SQLite will invoke this method one or more times while planning a query
303 ** that uses the carray virtual table. This routine needs to create
304 ** a query plan for each invocation and compute an estimated cost for that
305 ** plan.
306 **
307 ** In this implementation idxNum is used to represent the
308 ** query plan. idxStr is unused.
309 **
310 ** idxNum is:
311 **
312 ** 1 If only the pointer= constraint exists. In this case, the
313 ** parameter must be bound using sqlite3_carray_bind().
314 **
315 ** 2 if the pointer= and count= constraints exist.
316 **
317 ** 3 if the ctype= constraint also exists.
318 **
319 ** idxNum is 0 otherwise and carray becomes an empty table.
320 */
carrayBestIndex(sqlite3_vtab * tab,sqlite3_index_info * pIdxInfo)321 static int carrayBestIndex(
322 sqlite3_vtab *tab,
323 sqlite3_index_info *pIdxInfo
324 ){
325 int i; /* Loop over constraints */
326 int ptrIdx = -1; /* Index of the pointer= constraint, or -1 if none */
327 int cntIdx = -1; /* Index of the count= constraint, or -1 if none */
328 int ctypeIdx = -1; /* Index of the ctype= constraint, or -1 if none */
329
330 const struct sqlite3_index_constraint *pConstraint;
331 pConstraint = pIdxInfo->aConstraint;
332 for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
333 if( pConstraint->usable==0 ) continue;
334 if( pConstraint->op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue;
335 switch( pConstraint->iColumn ){
336 case CARRAY_COLUMN_POINTER:
337 ptrIdx = i;
338 break;
339 case CARRAY_COLUMN_COUNT:
340 cntIdx = i;
341 break;
342 case CARRAY_COLUMN_CTYPE:
343 ctypeIdx = i;
344 break;
345 }
346 }
347 if( ptrIdx>=0 ){
348 pIdxInfo->aConstraintUsage[ptrIdx].argvIndex = 1;
349 pIdxInfo->aConstraintUsage[ptrIdx].omit = 1;
350 pIdxInfo->estimatedCost = (double)1;
351 pIdxInfo->estimatedRows = 100;
352 pIdxInfo->idxNum = 1;
353 if( cntIdx>=0 ){
354 pIdxInfo->aConstraintUsage[cntIdx].argvIndex = 2;
355 pIdxInfo->aConstraintUsage[cntIdx].omit = 1;
356 pIdxInfo->idxNum = 2;
357 if( ctypeIdx>=0 ){
358 pIdxInfo->aConstraintUsage[ctypeIdx].argvIndex = 3;
359 pIdxInfo->aConstraintUsage[ctypeIdx].omit = 1;
360 pIdxInfo->idxNum = 3;
361 }
362 }
363 }else{
364 pIdxInfo->estimatedCost = (double)2147483647;
365 pIdxInfo->estimatedRows = 2147483647;
366 pIdxInfo->idxNum = 0;
367 }
368 return SQLITE_OK;
369 }
370
371 /*
372 ** This following structure defines all the methods for the
373 ** carray virtual table.
374 */
375 static sqlite3_module carrayModule = {
376 0, /* iVersion */
377 0, /* xCreate */
378 carrayConnect, /* xConnect */
379 carrayBestIndex, /* xBestIndex */
380 carrayDisconnect, /* xDisconnect */
381 0, /* xDestroy */
382 carrayOpen, /* xOpen - open a cursor */
383 carrayClose, /* xClose - close a cursor */
384 carrayFilter, /* xFilter - configure scan constraints */
385 carrayNext, /* xNext - advance a cursor */
386 carrayEof, /* xEof - check for end of scan */
387 carrayColumn, /* xColumn - read data */
388 carrayRowid, /* xRowid - read data */
389 0, /* xUpdate */
390 0, /* xBegin */
391 0, /* xSync */
392 0, /* xCommit */
393 0, /* xRollback */
394 0, /* xFindMethod */
395 0, /* xRename */
396 };
397
398 /*
399 ** Destructor for the carray_bind object
400 */
carrayBindDel(void * pPtr)401 static void carrayBindDel(void *pPtr){
402 carray_bind *p = (carray_bind*)pPtr;
403 if( p->xDel!=SQLITE_STATIC ){
404 p->xDel(p->aData);
405 }
406 sqlite3_free(p);
407 }
408
409 /*
410 ** Invoke this interface in order to bind to the single-argument
411 ** version of CARRAY().
412 */
sqlite3_carray_bind(sqlite3_stmt * pStmt,int idx,void * aData,int nData,int mFlags,void (* xDestroy)(void *))413 SQLITE_API int sqlite3_carray_bind(
414 sqlite3_stmt *pStmt,
415 int idx,
416 void *aData,
417 int nData,
418 int mFlags,
419 void (*xDestroy)(void*)
420 ){
421 carray_bind *pNew;
422 int i;
423 pNew = sqlite3_malloc64(sizeof(*pNew));
424 if( pNew==0 ){
425 if( xDestroy!=SQLITE_STATIC && xDestroy!=SQLITE_TRANSIENT ){
426 xDestroy(aData);
427 }
428 return SQLITE_NOMEM;
429 }
430 pNew->nData = nData;
431 pNew->mFlags = mFlags;
432 if( xDestroy==SQLITE_TRANSIENT ){
433 sqlite3_int64 sz = nData;
434 switch( mFlags & 0x03 ){
435 case CARRAY_INT32: sz *= 4; break;
436 case CARRAY_INT64: sz *= 8; break;
437 case CARRAY_DOUBLE: sz *= 8; break;
438 case CARRAY_TEXT: sz *= sizeof(char*); break;
439 }
440 if( (mFlags & 0x03)==CARRAY_TEXT ){
441 for(i=0; i<nData; i++){
442 const char *z = ((char**)aData)[i];
443 if( z ) sz += strlen(z) + 1;
444 }
445 }
446 pNew->aData = sqlite3_malloc64( sz );
447 if( pNew->aData==0 ){
448 sqlite3_free(pNew);
449 return SQLITE_NOMEM;
450 }
451 if( (mFlags & 0x03)==CARRAY_TEXT ){
452 char **az = (char**)pNew->aData;
453 char *z = (char*)&az[nData];
454 for(i=0; i<nData; i++){
455 const char *zData = ((char**)aData)[i];
456 sqlite3_int64 n;
457 if( zData==0 ){
458 az[i] = 0;
459 continue;
460 }
461 az[i] = z;
462 n = strlen(zData);
463 memcpy(z, zData, n+1);
464 z += n+1;
465 }
466 }else{
467 memcpy(pNew->aData, aData, sz);
468 }
469 pNew->xDel = sqlite3_free;
470 }else{
471 pNew->aData = aData;
472 pNew->xDel = xDestroy;
473 }
474 return sqlite3_bind_pointer(pStmt, idx, pNew, "carray-bind", carrayBindDel);
475 }
476
477
478 /*
479 ** For testing purpose in the TCL test harness, we need a method for
480 ** setting the pointer value. The inttoptr(X) SQL function accomplishes
481 ** this. Tcl script will bind an integer to X and the inttoptr() SQL
482 ** function will use sqlite3_result_pointer() to convert that integer into
483 ** a pointer.
484 **
485 ** This is for testing on TCL only.
486 */
487 #ifdef SQLITE_TEST
inttoptrFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)488 static void inttoptrFunc(
489 sqlite3_context *context,
490 int argc,
491 sqlite3_value **argv
492 ){
493 void *p;
494 sqlite3_int64 i64;
495 i64 = sqlite3_value_int64(argv[0]);
496 if( sizeof(i64)==sizeof(p) ){
497 memcpy(&p, &i64, sizeof(p));
498 }else{
499 int i32 = i64 & 0xffffffff;
500 memcpy(&p, &i32, sizeof(p));
501 }
502 sqlite3_result_pointer(context, p, "carray", 0);
503 }
504 #endif /* SQLITE_TEST */
505
506 #endif /* SQLITE_OMIT_VIRTUALTABLE */
507
sqlite3_carray_init(sqlite3 * db,char ** pzErrMsg,const sqlite3_api_routines * pApi)508 SQLITE_API int sqlite3_carray_init(
509 sqlite3 *db,
510 char **pzErrMsg,
511 const sqlite3_api_routines *pApi
512 ){
513 int rc = SQLITE_OK;
514 SQLITE_EXTENSION_INIT2(pApi);
515 #ifndef SQLITE_OMIT_VIRTUALTABLE
516 rc = sqlite3_create_module(db, "carray", &carrayModule, 0);
517 #ifdef SQLITE_TEST
518 if( rc==SQLITE_OK ){
519 rc = sqlite3_create_function(db, "inttoptr", 1, SQLITE_UTF8, 0,
520 inttoptrFunc, 0, 0);
521 }
522 #endif /* SQLITE_TEST */
523 #endif /* SQLITE_OMIT_VIRTUALTABLE */
524 return rc;
525 }
526