OLD | NEW |
(Empty) | |
| 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 } |
OLD | NEW |