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 that 21 ** has been cast to an integer. 22 ** 23 ** There is an optional third parameter to determine the datatype of 24 ** the C-language array. Allowed values of the third parameter are 25 ** 'int32', 'int64', 'double', 'char*'. Example: 26 ** 27 ** SELECT * FROM carray($ptr,10,'char*'); 28 ** 29 ** HOW IT WORKS 30 ** 31 ** The carray "function" is really a virtual table with the 32 ** following schema: 33 ** 34 ** CREATE TABLE carray( 35 ** value, 36 ** pointer HIDDEN, 37 ** count HIDDEN, 38 ** ctype TEXT HIDDEN 39 ** ); 40 ** 41 ** If the hidden columns "pointer" and "count" are unconstrained, then 42 ** the virtual table has no rows. Otherwise, the virtual table interprets 43 ** the integer value of "pointer" as a pointer to the array and "count" 44 ** as the number of elements in the array. The virtual table steps through 45 ** the array, element by element. 46 */ 47 #include "sqlite3ext.h" 48 SQLITE_EXTENSION_INIT1 49 #include <assert.h> 50 #include <string.h> 51 52 #ifndef SQLITE_OMIT_VIRTUALTABLE 53 54 /* 55 ** Allowed datatypes 56 */ 57 #define CARRAY_INT32 0 58 #define CARRAY_INT64 1 59 #define CARRAY_DOUBLE 2 60 #define CARRAY_TEXT 3 61 62 /* 63 ** Names of types 64 */ 65 static const char *azType[] = { "int32", "int64", "double", "char*" }; 66 67 68 /* carray_cursor is a subclass of sqlite3_vtab_cursor which will 69 ** serve as the underlying representation of a cursor that scans 70 ** over rows of the result 71 */ 72 typedef struct carray_cursor carray_cursor; 73 struct carray_cursor { 74 sqlite3_vtab_cursor base; /* Base class - must be first */ 75 sqlite3_int64 iRowid; /* The rowid */ 76 sqlite3_int64 iPtr; /* Pointer to array of values */ 77 sqlite3_int64 iCnt; /* Number of integers in the array */ 78 unsigned char eType; /* One of the CARRAY_type values */ 79 }; 80 81 /* 82 ** The carrayConnect() method is invoked to create a new 83 ** carray_vtab that describes the carray virtual table. 84 ** 85 ** Think of this routine as the constructor for carray_vtab objects. 86 ** 87 ** All this routine needs to do is: 88 ** 89 ** (1) Allocate the carray_vtab object and initialize all fields. 90 ** 91 ** (2) Tell SQLite (via the sqlite3_declare_vtab() interface) what the 92 ** result set of queries against carray will look like. 93 */ 94 static int carrayConnect( 95 sqlite3 *db, 96 void *pAux, 97 int argc, const char *const*argv, 98 sqlite3_vtab **ppVtab, 99 char **pzErr 100 ){ 101 sqlite3_vtab *pNew; 102 int rc; 103 104 /* Column numbers */ 105 #define CARRAY_COLUMN_VALUE 0 106 #define CARRAY_COLUMN_POINTER 1 107 #define CARRAY_COLUMN_COUNT 2 108 #define CARRAY_COLUMN_CTYPE 3 109 110 rc = sqlite3_declare_vtab(db, 111 "CREATE TABLE x(value,pointer hidden,count hidden,ctype hidden)"); 112 if( rc==SQLITE_OK ){ 113 pNew = *ppVtab = sqlite3_malloc( sizeof(*pNew) ); 114 if( pNew==0 ) return SQLITE_NOMEM; 115 memset(pNew, 0, sizeof(*pNew)); 116 } 117 return rc; 118 } 119 120 /* 121 ** This method is the destructor for carray_cursor objects. 122 */ 123 static int carrayDisconnect(sqlite3_vtab *pVtab){ 124 sqlite3_free(pVtab); 125 return SQLITE_OK; 126 } 127 128 /* 129 ** Constructor for a new carray_cursor object. 130 */ 131 static int carrayOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor){ 132 carray_cursor *pCur; 133 pCur = sqlite3_malloc( sizeof(*pCur) ); 134 if( pCur==0 ) return SQLITE_NOMEM; 135 memset(pCur, 0, sizeof(*pCur)); 136 *ppCursor = &pCur->base; 137 return SQLITE_OK; 138 } 139 140 /* 141 ** Destructor for a carray_cursor. 142 */ 143 static int carrayClose(sqlite3_vtab_cursor *cur){ 144 sqlite3_free(cur); 145 return SQLITE_OK; 146 } 147 148 149 /* 150 ** Advance a carray_cursor to its next row of output. 151 */ 152 static int carrayNext(sqlite3_vtab_cursor *cur){ 153 carray_cursor *pCur = (carray_cursor*)cur; 154 pCur->iRowid++; 155 return SQLITE_OK; 156 } 157 158 /* 159 ** Return values of columns for the row at which the carray_cursor 160 ** is currently pointing. 161 */ 162 static int carrayColumn( 163 sqlite3_vtab_cursor *cur, /* The cursor */ 164 sqlite3_context *ctx, /* First argument to sqlite3_result_...() */ 165 int i /* Which column to return */ 166 ){ 167 carray_cursor *pCur = (carray_cursor*)cur; 168 sqlite3_int64 x = 0; 169 switch( i ){ 170 case CARRAY_COLUMN_POINTER: x = pCur->iPtr; break; 171 case CARRAY_COLUMN_COUNT: x = pCur->iCnt; break; 172 case CARRAY_COLUMN_CTYPE: { 173 sqlite3_result_text(ctx, azType[pCur->eType], -1, SQLITE_STATIC); 174 return SQLITE_OK; 175 } 176 default: { 177 switch( pCur->eType ){ 178 case CARRAY_INT32: { 179 int *p = (int*)pCur->iPtr; 180 sqlite3_result_int(ctx, p[pCur->iRowid-1]); 181 return SQLITE_OK; 182 } 183 case CARRAY_INT64: { 184 sqlite3_int64 *p = (sqlite3_int64*)pCur->iPtr; 185 sqlite3_result_int64(ctx, p[pCur->iRowid-1]); 186 return SQLITE_OK; 187 } 188 case CARRAY_DOUBLE: { 189 double *p = (double*)pCur->iPtr; 190 sqlite3_result_double(ctx, p[pCur->iRowid-1]); 191 return SQLITE_OK; 192 } 193 case CARRAY_TEXT: { 194 const char **p = (const char**)pCur->iPtr; 195 sqlite3_result_text(ctx, p[pCur->iRowid-1], -1, SQLITE_TRANSIENT); 196 return SQLITE_OK; 197 } 198 } 199 } 200 } 201 sqlite3_result_int64(ctx, x); 202 return SQLITE_OK; 203 } 204 205 /* 206 ** Return the rowid for the current row. In this implementation, the 207 ** rowid is the same as the output value. 208 */ 209 static int carrayRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){ 210 carray_cursor *pCur = (carray_cursor*)cur; 211 *pRowid = pCur->iRowid; 212 return SQLITE_OK; 213 } 214 215 /* 216 ** Return TRUE if the cursor has been moved off of the last 217 ** row of output. 218 */ 219 static int carrayEof(sqlite3_vtab_cursor *cur){ 220 carray_cursor *pCur = (carray_cursor*)cur; 221 return pCur->iRowid>pCur->iCnt; 222 } 223 224 /* 225 ** This method is called to "rewind" the carray_cursor object back 226 ** to the first row of output. 227 */ 228 static int carrayFilter( 229 sqlite3_vtab_cursor *pVtabCursor, 230 int idxNum, const char *idxStr, 231 int argc, sqlite3_value **argv 232 ){ 233 carray_cursor *pCur = (carray_cursor *)pVtabCursor; 234 if( idxNum ){ 235 pCur->iPtr = sqlite3_value_int64(argv[0]); 236 pCur->iCnt = sqlite3_value_int64(argv[1]); 237 if( idxNum<3 ){ 238 pCur->eType = CARRAY_INT32; 239 }else{ 240 unsigned char i; 241 const char *zType = (const char*)sqlite3_value_text(argv[2]); 242 for(i=0; i<sizeof(azType)/sizeof(azType[0]); i++){ 243 if( sqlite3_stricmp(zType, azType[i])==0 ) break; 244 } 245 if( i>=sizeof(azType)/sizeof(azType[0]) ){ 246 pVtabCursor->pVtab->zErrMsg = sqlite3_mprintf( 247 "unknown datatype: %Q", zType); 248 return SQLITE_ERROR; 249 }else{ 250 pCur->eType = i; 251 } 252 } 253 }else{ 254 pCur->iPtr = 0; 255 pCur->iCnt = 0; 256 } 257 pCur->iRowid = 1; 258 return SQLITE_OK; 259 } 260 261 /* 262 ** SQLite will invoke this method one or more times while planning a query 263 ** that uses the carray virtual table. This routine needs to create 264 ** a query plan for each invocation and compute an estimated cost for that 265 ** plan. 266 ** 267 ** In this implementation idxNum is used to represent the 268 ** query plan. idxStr is unused. 269 ** 270 ** idxNum is 2 if the pointer= and count= constraints exist, 271 ** 3 if the ctype= constraint also exists, and is 0 otherwise. 272 ** If idxNum is 0, then carray becomes an empty table. 273 */ 274 static int carrayBestIndex( 275 sqlite3_vtab *tab, 276 sqlite3_index_info *pIdxInfo 277 ){ 278 int i; /* Loop over constraints */ 279 int ptrIdx = -1; /* Index of the pointer= constraint, or -1 if none */ 280 int cntIdx = -1; /* Index of the count= constraint, or -1 if none */ 281 int ctypeIdx = -1; /* Index of the ctype= constraint, or -1 if none */ 282 283 const struct sqlite3_index_constraint *pConstraint; 284 pConstraint = pIdxInfo->aConstraint; 285 for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){ 286 if( pConstraint->usable==0 ) continue; 287 if( pConstraint->op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue; 288 switch( pConstraint->iColumn ){ 289 case CARRAY_COLUMN_POINTER: 290 ptrIdx = i; 291 break; 292 case CARRAY_COLUMN_COUNT: 293 cntIdx = i; 294 break; 295 case CARRAY_COLUMN_CTYPE: 296 ctypeIdx = i; 297 break; 298 } 299 } 300 if( ptrIdx>=0 && cntIdx>=0 ){ 301 pIdxInfo->aConstraintUsage[ptrIdx].argvIndex = 1; 302 pIdxInfo->aConstraintUsage[ptrIdx].omit = 1; 303 pIdxInfo->aConstraintUsage[cntIdx].argvIndex = 2; 304 pIdxInfo->aConstraintUsage[cntIdx].omit = 1; 305 pIdxInfo->estimatedCost = (double)1; 306 pIdxInfo->estimatedRows = 100; 307 pIdxInfo->idxNum = 2; 308 if( ctypeIdx>=0 ){ 309 pIdxInfo->aConstraintUsage[ctypeIdx].argvIndex = 3; 310 pIdxInfo->aConstraintUsage[ctypeIdx].omit = 1; 311 pIdxInfo->idxNum = 3; 312 } 313 }else{ 314 pIdxInfo->estimatedCost = (double)2147483647; 315 pIdxInfo->estimatedRows = 2147483647; 316 pIdxInfo->idxNum = 0; 317 } 318 return SQLITE_OK; 319 } 320 321 /* 322 ** This following structure defines all the methods for the 323 ** carray virtual table. 324 */ 325 static sqlite3_module carrayModule = { 326 0, /* iVersion */ 327 0, /* xCreate */ 328 carrayConnect, /* xConnect */ 329 carrayBestIndex, /* xBestIndex */ 330 carrayDisconnect, /* xDisconnect */ 331 0, /* xDestroy */ 332 carrayOpen, /* xOpen - open a cursor */ 333 carrayClose, /* xClose - close a cursor */ 334 carrayFilter, /* xFilter - configure scan constraints */ 335 carrayNext, /* xNext - advance a cursor */ 336 carrayEof, /* xEof - check for end of scan */ 337 carrayColumn, /* xColumn - read data */ 338 carrayRowid, /* xRowid - read data */ 339 0, /* xUpdate */ 340 0, /* xBegin */ 341 0, /* xSync */ 342 0, /* xCommit */ 343 0, /* xRollback */ 344 0, /* xFindMethod */ 345 0, /* xRename */ 346 }; 347 348 #endif /* SQLITE_OMIT_VIRTUALTABLE */ 349 350 #ifdef _WIN32 351 __declspec(dllexport) 352 #endif 353 int sqlite3_carray_init( 354 sqlite3 *db, 355 char **pzErrMsg, 356 const sqlite3_api_routines *pApi 357 ){ 358 int rc = SQLITE_OK; 359 SQLITE_EXTENSION_INIT2(pApi); 360 #ifndef SQLITE_OMIT_VIRTUALTABLE 361 rc = sqlite3_create_module(db, "carray", &carrayModule, 0); 362 #endif 363 return rc; 364 } 365