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