Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch vtab-like-operator Excluding Merge-Ins
This is equivalent to a diff from 2025617707 to e70ec71d68
2015-11-25
| ||
01:57 | Enhancement the virtual table interface to support LIKE, GLOB, and REGEXP operators. Also add the sqlite3_strlike() interface, which might be useful as part of the implementation of LIKE on some virtual tables. (check-in: a6bfd4692c user: drh tags: trunk) | |
2015-11-24
| ||
21:23 | Add the sqlite3_strlike() interface, which might be useful for implementing LIKE operators on virtual tables. (Closed-Leaf check-in: e70ec71d68 user: drh tags: vtab-like-operator) | |
18:16 | Fix a problem in whereexpr.c causing a crash while processing a user-function taht accepts zero arguments. (check-in: 069e51b19c user: dan tags: vtab-like-operator) | |
17:44 | Merge latest trunk changes with this branch. (check-in: 99222bb3e5 user: dan tags: vtab-like-operator) | |
16:40 | Remove from os_unix.c pointless logic that tries to prevent a recurrence of a warning message that can only occur once. (check-in: 2025617707 user: drh tags: trunk) | |
15:12 | Fix a comment typo in the unix VFS. No changes to code. (check-in: 32e138796c user: drh tags: trunk) | |
Changes to src/analyze.c.
︙ | ︙ | |||
986 987 988 989 990 991 992 | if( v==0 || NEVER(pTab==0) ){ return; } if( pTab->tnum==0 ){ /* Do not gather statistics on views or virtual tables */ return; } | | | 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 | if( v==0 || NEVER(pTab==0) ){ return; } if( pTab->tnum==0 ){ /* Do not gather statistics on views or virtual tables */ return; } if( sqlite3_strlike("sqlite_%", pTab->zName, 0)==0 ){ /* Do not gather statistics on system tables */ return; } assert( sqlite3BtreeHoldsAllMutexes(db) ); iDb = sqlite3SchemaToIndex(db, pTab->pSchema); assert( iDb>=0 ); assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); |
︙ | ︙ |
Changes to src/func.c.
︙ | ︙ | |||
758 759 760 761 762 763 764 765 766 767 768 769 770 771 | /* ** The sqlite3_strglob() interface. */ int sqlite3_strglob(const char *zGlobPattern, const char *zString){ return patternCompare((u8*)zGlobPattern, (u8*)zString, &globInfo, 0)==0; } /* ** Count the number of times that the LIKE operator (or GLOB which is ** just a variation of LIKE) gets called. This is used for testing ** only. */ #ifdef SQLITE_TEST | > > > > > > > | 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 | /* ** The sqlite3_strglob() interface. */ int sqlite3_strglob(const char *zGlobPattern, const char *zString){ return patternCompare((u8*)zGlobPattern, (u8*)zString, &globInfo, 0)==0; } /* ** The sqlite3_strlike() interface. */ int sqlite3_strlike(const char *zPattern, const char *zStr, unsigned int esc){ return patternCompare((u8*)zPattern, (u8*)zStr, &likeInfoNorm, esc)==0; } /* ** Count the number of times that the LIKE operator (or GLOB which is ** just a variation of LIKE) gets called. This is used for testing ** only. */ #ifdef SQLITE_TEST |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
5705 5706 5707 5708 5709 5710 5711 | ** CAPI3REF: Virtual Table Constraint Operator Codes ** ** These macros defined the allowed values for the ** [sqlite3_index_info].aConstraint[].op field. Each value represents ** an operator that is part of a constraint term in the wHERE clause of ** a query that uses a [virtual table]. */ | | | | | | | > > > | 5705 5706 5707 5708 5709 5710 5711 5712 5713 5714 5715 5716 5717 5718 5719 5720 5721 5722 5723 5724 5725 5726 5727 | ** CAPI3REF: Virtual Table Constraint Operator Codes ** ** These macros defined the allowed values for the ** [sqlite3_index_info].aConstraint[].op field. Each value represents ** an operator that is part of a constraint term in the wHERE clause of ** a query that uses a [virtual table]. */ #define SQLITE_INDEX_CONSTRAINT_EQ 2 #define SQLITE_INDEX_CONSTRAINT_GT 4 #define SQLITE_INDEX_CONSTRAINT_LE 8 #define SQLITE_INDEX_CONSTRAINT_LT 16 #define SQLITE_INDEX_CONSTRAINT_GE 32 #define SQLITE_INDEX_CONSTRAINT_MATCH 64 #define SQLITE_INDEX_CONSTRAINT_LIKE 65 #define SQLITE_INDEX_CONSTRAINT_GLOB 66 #define SQLITE_INDEX_CONSTRAINT_REGEXP 67 /* ** CAPI3REF: Register A Virtual Table Implementation ** METHOD: sqlite3 ** ** ^These routines are used to register a new [virtual table module] name. ** ^Module names must be registered before |
︙ | ︙ | |||
7370 7371 7372 7373 7374 7375 7376 7377 7378 7379 7380 7381 7382 7383 7384 7385 7386 | ** the glob pattern P. ^The definition of glob pattern matching used in ** [sqlite3_strglob(P,X)] is the same as for the "X GLOB P" operator in the ** SQL dialect used by SQLite. ^The sqlite3_strglob(P,X) function is case ** sensitive. ** ** Note that this routine returns zero on a match and non-zero if the strings ** do not match, the same as [sqlite3_stricmp()] and [sqlite3_strnicmp()]. */ int sqlite3_strglob(const char *zGlob, const char *zStr); /* ** CAPI3REF: Error Logging Interface ** ** ^The [sqlite3_log()] interface writes a message into the [error log] ** established by the [SQLITE_CONFIG_LOG] option to [sqlite3_config()]. ** ^If logging is enabled, the zFormat string and subsequent arguments are ** used with [sqlite3_snprintf()] to generate the final output string. | > > > > > > > > > > > > > > > > > > > > > > > > > > > | 7373 7374 7375 7376 7377 7378 7379 7380 7381 7382 7383 7384 7385 7386 7387 7388 7389 7390 7391 7392 7393 7394 7395 7396 7397 7398 7399 7400 7401 7402 7403 7404 7405 7406 7407 7408 7409 7410 7411 7412 7413 7414 7415 7416 | ** the glob pattern P. ^The definition of glob pattern matching used in ** [sqlite3_strglob(P,X)] is the same as for the "X GLOB P" operator in the ** SQL dialect used by SQLite. ^The sqlite3_strglob(P,X) function is case ** sensitive. ** ** Note that this routine returns zero on a match and non-zero if the strings ** do not match, the same as [sqlite3_stricmp()] and [sqlite3_strnicmp()]. ** ** See also: sqlite3_strlike(). */ int sqlite3_strglob(const char *zGlob, const char *zStr); /* ** CAPI3REF: String LIKE Matching * ** ^The [sqlite3_strlike(P,X,E)] interface returns zero if string X matches ** the LIKE pattern P with escape character E, and it returns non-zero if ** string X does not match the like pattern. ** ^The definition of LIKE pattern matching used in ** [sqlite3_strlike(P,X,E)] is the same as for the "X LIKE P ESCAPE E" ** operator in the SQL dialect used by SQLite. ^For "X LIKE P" without ** the ESCAPE clause, set the E parameter of [sqlite3_strlike(P,X,E)] to 0. ** ^As with the LIKE operator, the [sqlite3_str(P,X,E)] function is case ** insensitive - equivalent upper and lower case ASCII characters match ** one another. ** ** ^The [sqlite3_strlike(P,X,E)] function matches Unicode characters, though ** only ASCII characters are case folded. (This is because when SQLite was ** first written, the case folding rules for Unicode where still in flux.) ** ** Note that this routine returns zero on a match and non-zero if the strings ** do not match, the same as [sqlite3_stricmp()] and [sqlite3_strnicmp()]. ** ** See also: sqlite3_strglob(). */ int sqlite3_strlike(const char *zGlob, const char *zStr, unsigned int cEsc); /* ** CAPI3REF: Error Logging Interface ** ** ^The [sqlite3_log()] interface writes a message into the [error log] ** established by the [SQLITE_CONFIG_LOG] option to [sqlite3_config()]. ** ^If logging is enabled, the zFormat string and subsequent arguments are ** used with [sqlite3_snprintf()] to generate the final output string. |
︙ | ︙ |
Changes to src/sqlite3ext.h.
︙ | ︙ | |||
271 272 273 274 275 276 277 278 279 280 281 282 283 284 | sqlite3_value *(*value_dup)(const sqlite3_value*); void (*value_free)(sqlite3_value*); int (*result_zeroblob64)(sqlite3_context*,sqlite3_uint64); int (*bind_zeroblob64)(sqlite3_stmt*, int, sqlite3_uint64); /* Version 3.9.0 and later */ unsigned int (*value_subtype)(sqlite3_value*); void (*result_subtype)(sqlite3_context*,unsigned int); }; /* ** The following macros redefine the API routines so that they are ** redirected through the global sqlite3_api structure. ** ** This header file is also used by the loadext.c source file | > > | 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 | sqlite3_value *(*value_dup)(const sqlite3_value*); void (*value_free)(sqlite3_value*); int (*result_zeroblob64)(sqlite3_context*,sqlite3_uint64); int (*bind_zeroblob64)(sqlite3_stmt*, int, sqlite3_uint64); /* Version 3.9.0 and later */ unsigned int (*value_subtype)(sqlite3_value*); void (*result_subtype)(sqlite3_context*,unsigned int); /* Version 3.10.0 and later */ int (*strlike)(const char*,const char*,unsigned int); }; /* ** The following macros redefine the API routines so that they are ** redirected through the global sqlite3_api structure. ** ** This header file is also used by the loadext.c source file |
︙ | ︙ | |||
510 511 512 513 514 515 516 517 518 519 520 521 522 523 | #define sqlite3_value_dup sqlite3_api->value_dup #define sqlite3_value_free sqlite3_api->value_free #define sqlite3_result_zeroblob64 sqlite3_api->result_zeroblob64 #define sqlite3_bind_zeroblob64 sqlite3_api->bind_zeroblob64 /* Version 3.9.0 and later */ #define sqlite3_value_subtype sqlite3_api->value_subtype #define sqlite3_result_subtype sqlite3_api->result_subtype #endif /* !defined(SQLITE_CORE) && !defined(SQLITE_OMIT_LOAD_EXTENSION) */ #if !defined(SQLITE_CORE) && !defined(SQLITE_OMIT_LOAD_EXTENSION) /* This case when the file really is being compiled as a loadable ** extension */ # define SQLITE_EXTENSION_INIT1 const sqlite3_api_routines *sqlite3_api=0; # define SQLITE_EXTENSION_INIT2(v) sqlite3_api=v; | > > | 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 | #define sqlite3_value_dup sqlite3_api->value_dup #define sqlite3_value_free sqlite3_api->value_free #define sqlite3_result_zeroblob64 sqlite3_api->result_zeroblob64 #define sqlite3_bind_zeroblob64 sqlite3_api->bind_zeroblob64 /* Version 3.9.0 and later */ #define sqlite3_value_subtype sqlite3_api->value_subtype #define sqlite3_result_subtype sqlite3_api->result_subtype /* Version 3.10.0 and later */ #define sqlite3_strlike sqlite3_api->strlike #endif /* !defined(SQLITE_CORE) && !defined(SQLITE_OMIT_LOAD_EXTENSION) */ #if !defined(SQLITE_CORE) && !defined(SQLITE_OMIT_LOAD_EXTENSION) /* This case when the file really is being compiled as a loadable ** extension */ # define SQLITE_EXTENSION_INIT1 const sqlite3_api_routines *sqlite3_api=0; # define SQLITE_EXTENSION_INIT2(v) sqlite3_api=v; |
︙ | ︙ |
Changes to src/test8.c.
︙ | ︙ | |||
852 853 854 855 856 857 858 859 860 861 862 863 864 865 | ** will be used by the next block of code to construct a new ** query. It should also be noted here that the next block ** of code requires the first letter of this operator to be ** in upper-case to trigger the special MATCH handling (i.e. ** wrapping the bound parameter with literal '%'s). */ zOp = "LIKE"; break; } if( zOp[0]=='L' ){ zNew = sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')", zSep, zCol); } else { zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zCol, zOp); } | > > > > > > | 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 | ** will be used by the next block of code to construct a new ** query. It should also be noted here that the next block ** of code requires the first letter of this operator to be ** in upper-case to trigger the special MATCH handling (i.e. ** wrapping the bound parameter with literal '%'s). */ zOp = "LIKE"; break; case SQLITE_INDEX_CONSTRAINT_LIKE: zOp = "like"; break; case SQLITE_INDEX_CONSTRAINT_GLOB: zOp = "glob"; break; case SQLITE_INDEX_CONSTRAINT_REGEXP: zOp = "regexp"; break; } if( zOp[0]=='L' ){ zNew = sqlite3_mprintf(" %s %s LIKE (SELECT '%%'||?||'%%')", zSep, zCol); } else { zNew = sqlite3_mprintf(" %s %s %s ?", zSep, zCol, zOp); } |
︙ | ︙ |
Changes to src/test_tclvar.c.
︙ | ︙ | |||
19 20 21 22 23 24 25 26 27 28 29 30 31 32 | #include "sqliteInt.h" #include "tcl.h" #include <stdlib.h> #include <string.h> #ifndef SQLITE_OMIT_VIRTUALTABLE typedef struct tclvar_vtab tclvar_vtab; typedef struct tclvar_cursor tclvar_cursor; /* ** A tclvar virtual-table object */ struct tclvar_vtab { | > > > > > > > > > | 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | #include "sqliteInt.h" #include "tcl.h" #include <stdlib.h> #include <string.h> #ifndef SQLITE_OMIT_VIRTUALTABLE /* ** Characters that make up the idxStr created by xBestIndex for xFilter. */ #define TCLVAR_NAME_EQ 'e' #define TCLVAR_NAME_MATCH 'm' #define TCLVAR_VALUE_GLOB 'g' #define TCLVAR_VALUE_REGEXP 'r' #define TCLVAR_VALUE_LIKE 'l' typedef struct tclvar_vtab tclvar_vtab; typedef struct tclvar_cursor tclvar_cursor; /* ** A tclvar virtual-table object */ struct tclvar_vtab { |
︙ | ︙ | |||
151 152 153 154 155 156 157 158 | static int tclvarFilter( sqlite3_vtab_cursor *pVtabCursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv ){ tclvar_cursor *pCur = (tclvar_cursor *)pVtabCursor; Tcl_Interp *interp = ((tclvar_vtab *)(pVtabCursor->pVtab))->interp; | > > > > | > | > > > > > > > > > > > > > > > > > > | > > | > | > | > > | < | 160 161 162 163 164 165 166 167 168 169 170 171 172 173 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 210 211 212 213 214 215 216 217 218 219 220 221 222 223 | static int tclvarFilter( sqlite3_vtab_cursor *pVtabCursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv ){ tclvar_cursor *pCur = (tclvar_cursor *)pVtabCursor; Tcl_Interp *interp = ((tclvar_vtab *)(pVtabCursor->pVtab))->interp; Tcl_Obj *p = Tcl_NewStringObj("tclvar_filter_cmd", -1); const char *zEq = ""; const char *zMatch = ""; const char *zGlob = ""; const char *zRegexp = ""; const char *zLike = ""; int i; for(i=0; idxStr[i]; i++){ switch( idxStr[i] ){ case TCLVAR_NAME_EQ: zEq = (const char*)sqlite3_value_text(argv[i]); break; case TCLVAR_NAME_MATCH: zMatch = (const char*)sqlite3_value_text(argv[i]); break; case TCLVAR_VALUE_GLOB: zGlob = (const char*)sqlite3_value_text(argv[i]); break; case TCLVAR_VALUE_REGEXP: zRegexp = (const char*)sqlite3_value_text(argv[i]); break; case TCLVAR_VALUE_LIKE: zLike = (const char*)sqlite3_value_text(argv[i]); break; default: assert( 0 ); } } Tcl_IncrRefCount(p); Tcl_ListObjAppendElement(0, p, Tcl_NewStringObj(zEq, -1)); Tcl_ListObjAppendElement(0, p, Tcl_NewStringObj(zMatch, -1)); Tcl_ListObjAppendElement(0, p, Tcl_NewStringObj(zGlob, -1)); Tcl_ListObjAppendElement(0, p, Tcl_NewStringObj(zRegexp, -1)); Tcl_ListObjAppendElement(0, p, Tcl_NewStringObj(zLike, -1)); Tcl_EvalObjEx(interp, p, TCL_EVAL_GLOBAL); if( pCur->pList1 ){ Tcl_DecrRefCount(pCur->pList1); } if( pCur->pList2 ){ Tcl_DecrRefCount(pCur->pList2); pCur->pList2 = 0; } pCur->i1 = 0; pCur->i2 = 0; pCur->pList1 = Tcl_GetObjResult(interp); Tcl_IncrRefCount(pCur->pList1); Tcl_DecrRefCount(p); return tclvarNext(pVtabCursor); } static int tclvarColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int i){ Tcl_Obj *p1; |
︙ | ︙ | |||
220 221 222 223 224 225 226 | } static int tclvarEof(sqlite3_vtab_cursor *cur){ tclvar_cursor *pCur = (tclvar_cursor*)cur; return (pCur->pList2?0:1); } | > > > > > > > > | | > > | > > > > | < < < < > > > > > | | > | > > > > > > | | | > > > > > > > > > > > > > > > > > > > > > < < | > | > > > > > > > > > > > > > | > > > > > > | > > > | > > > > > | | > > > > > > > > > > > > | 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 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 | } static int tclvarEof(sqlite3_vtab_cursor *cur){ tclvar_cursor *pCur = (tclvar_cursor*)cur; return (pCur->pList2?0:1); } /* ** If nul-terminated string zStr does not already contain the character ** passed as the second argument, append it and return 0. Or, if there is ** already an instance of x in zStr, do nothing return 1; ** ** There is guaranteed to be enough room in the buffer pointed to by zStr ** for the new character and nul-terminator. */ static int tclvarAddToIdxstr(char *zStr, char x){ int i; for(i=0; zStr[i]; i++){ if( zStr[i]==x ) return 1; } zStr[i] = x; zStr[i+1] = '\0'; return 0; } /* ** Return true if variable $::tclvar_set_omit exists and is set to true. ** False otherwise. */ static int tclvarSetOmit(Tcl_Interp *interp){ int rc; int res = 0; Tcl_Obj *pRes; rc = Tcl_Eval(interp, "expr {[info exists ::tclvar_set_omit] && $::tclvar_set_omit}" ); if( rc==TCL_OK ){ pRes = Tcl_GetObjResult(interp); rc = Tcl_GetBooleanFromObj(0, pRes, &res); } return (rc==TCL_OK && res); } /* ** The xBestIndex() method. This virtual table supports the following ** operators: ** ** name = ? (omit flag clear) ** name MATCH ? (omit flag set) ** value GLOB ? (omit flag set iff $::tclvar_set_omit) ** value REGEXP ? (omit flag set iff $::tclvar_set_omit) ** value LIKE ? (omit flag set iff $::tclvar_set_omit) ** ** For each constraint present, the corresponding TCLVAR_XXX character is ** appended to the idxStr value. */ static int tclvarBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){ tclvar_vtab *pTab = (tclvar_vtab*)tab; int ii; char *zStr = sqlite3_malloc(32); int iStr = 0; if( zStr==0 ) return SQLITE_NOMEM; zStr[0] = '\0'; for(ii=0; ii<pIdxInfo->nConstraint; ii++){ struct sqlite3_index_constraint const *pCons = &pIdxInfo->aConstraint[ii]; struct sqlite3_index_constraint_usage *pUsage; pUsage = &pIdxInfo->aConstraintUsage[ii]; if( pCons->usable ){ /* name = ? */ if( pCons->op==SQLITE_INDEX_CONSTRAINT_EQ && pCons->iColumn==0 ){ if( 0==tclvarAddToIdxstr(zStr, TCLVAR_NAME_EQ) ){ pUsage->argvIndex = ++iStr; pUsage->omit = 0; } } /* name MATCH ? */ if( pCons->op==SQLITE_INDEX_CONSTRAINT_MATCH && pCons->iColumn==0 ){ if( 0==tclvarAddToIdxstr(zStr, TCLVAR_NAME_MATCH) ){ pUsage->argvIndex = ++iStr; pUsage->omit = 1; } } /* value GLOB ? */ if( pCons->op==SQLITE_INDEX_CONSTRAINT_GLOB && pCons->iColumn==2 ){ if( 0==tclvarAddToIdxstr(zStr, TCLVAR_VALUE_GLOB) ){ pUsage->argvIndex = ++iStr; pUsage->omit = tclvarSetOmit(pTab->interp); } } /* value REGEXP ? */ if( pCons->op==SQLITE_INDEX_CONSTRAINT_REGEXP && pCons->iColumn==2 ){ if( 0==tclvarAddToIdxstr(zStr, TCLVAR_VALUE_REGEXP) ){ pUsage->argvIndex = ++iStr; pUsage->omit = tclvarSetOmit(pTab->interp); } } /* value LIKE ? */ if( pCons->op==SQLITE_INDEX_CONSTRAINT_LIKE && pCons->iColumn==2 ){ if( 0==tclvarAddToIdxstr(zStr, TCLVAR_VALUE_LIKE) ){ pUsage->argvIndex = ++iStr; pUsage->omit = tclvarSetOmit(pTab->interp); } } } } pIdxInfo->idxStr = zStr; pIdxInfo->needToFreeIdxStr = 1; return SQLITE_OK; } /* ** A virtual table module that provides read-only access to a ** Tcl global variable namespace. |
︙ | ︙ | |||
291 292 293 294 295 296 297 298 299 300 301 302 303 304 | */ static int register_tclvar_module( ClientData clientData, /* Pointer to sqlite3_enable_XXX function */ Tcl_Interp *interp, /* The TCL interpreter that invoked this command */ int objc, /* Number of arguments */ Tcl_Obj *CONST objv[] /* Command arguments */ ){ sqlite3 *db; if( objc!=2 ){ Tcl_WrongNumArgs(interp, 1, objv, "DB"); return TCL_ERROR; } if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR; #ifndef SQLITE_OMIT_VIRTUALTABLE | > | > > > > > > > > > > > > > > > > > > > > > | | 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 | */ static int register_tclvar_module( ClientData clientData, /* Pointer to sqlite3_enable_XXX function */ Tcl_Interp *interp, /* The TCL interpreter that invoked this command */ int objc, /* Number of arguments */ Tcl_Obj *CONST objv[] /* Command arguments */ ){ int rc = TCL_OK; sqlite3 *db; if( objc!=2 ){ Tcl_WrongNumArgs(interp, 1, objv, "DB"); return TCL_ERROR; } if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR; #ifndef SQLITE_OMIT_VIRTUALTABLE sqlite3_create_module(db, "tclvar", &tclvarModule, (void*)interp); rc = Tcl_Eval(interp, "proc like {pattern str} {\n" " set p [string map {% * _ ?} $pattern]\n" " string match $p $str\n" "}\n" "proc tclvar_filter_cmd {eq match glob regexp like} {\n" " set res {}\n" " set pattern $eq\n" " if {$pattern=={}} { set pattern $match }\n" " if {$pattern=={}} { set pattern * }\n" " foreach v [uplevel #0 info vars $pattern] {\n" " if {($glob=={} || [string match $glob [uplevel #0 set $v]])\n" " && ($like=={} || [like $like [uplevel #0 set $v]])\n" " && ($regexp=={} || [regexp $regexp [uplevel #0 set $v]])\n" " } {\n" " lappend res $v\n" " }\n" " }\n" " set res\n" "}\n" ); #endif return rc; } #endif /* ** Register commands with the TCL interpreter. |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
889 890 891 892 893 894 895 896 897 898 899 900 901 902 | if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; assert( pTerm->u.leftColumn>=(-1) ); pIdxCons[j].iColumn = pTerm->u.leftColumn; pIdxCons[j].iTermOffset = i; op = (u8)pTerm->eOperator & WO_ALL; if( op==WO_IN ) op = WO_EQ; pIdxCons[j].op = op; /* The direct assignment in the previous line is possible only because ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The ** following asserts verify this fact. */ assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ ); assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT ); assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE ); | > > > | 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 | if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; assert( pTerm->u.leftColumn>=(-1) ); pIdxCons[j].iColumn = pTerm->u.leftColumn; pIdxCons[j].iTermOffset = i; op = (u8)pTerm->eOperator & WO_ALL; if( op==WO_IN ) op = WO_EQ; if( op==WO_MATCH ){ op = pTerm->eMatchOp; } pIdxCons[j].op = op; /* The direct assignment in the previous line is possible only because ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The ** following asserts verify this fact. */ assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ ); assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT ); assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE ); |
︙ | ︙ |
Changes to src/whereInt.h.
︙ | ︙ | |||
249 250 251 252 253 254 255 256 257 258 259 260 261 262 | WhereOrInfo *pOrInfo; /* Extra information if (eOperator & WO_OR)!=0 */ WhereAndInfo *pAndInfo; /* Extra information if (eOperator& WO_AND)!=0 */ } u; LogEst truthProb; /* Probability of truth for this expression */ u16 eOperator; /* A WO_xx value describing <op> */ u16 wtFlags; /* TERM_xxx bit flags. See below */ u8 nChild; /* Number of children that must disable us */ WhereClause *pWC; /* The clause this term is part of */ Bitmask prereqRight; /* Bitmask of tables used by pExpr->pRight */ Bitmask prereqAll; /* Bitmask of tables referenced by pExpr */ }; /* ** Allowed values of WhereTerm.wtFlags | > | 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 | WhereOrInfo *pOrInfo; /* Extra information if (eOperator & WO_OR)!=0 */ WhereAndInfo *pAndInfo; /* Extra information if (eOperator& WO_AND)!=0 */ } u; LogEst truthProb; /* Probability of truth for this expression */ u16 eOperator; /* A WO_xx value describing <op> */ u16 wtFlags; /* TERM_xxx bit flags. See below */ u8 nChild; /* Number of children that must disable us */ u8 eMatchOp; /* Op for vtab MATCH/LIKE/GLOB/REGEXP terms */ WhereClause *pWC; /* The clause this term is part of */ Bitmask prereqRight; /* Bitmask of tables used by pExpr->pRight */ Bitmask prereqAll; /* Bitmask of tables referenced by pExpr */ }; /* ** Allowed values of WhereTerm.wtFlags |
︙ | ︙ |
Changes to src/whereexpr.c.
︙ | ︙ | |||
273 274 275 276 277 278 279 | #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */ #ifndef SQLITE_OMIT_VIRTUALTABLE /* ** Check to see if the given expression is of the form ** | | > > > | > > > > > > > > > > > > | > < | > > | > | | > | | 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 | #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */ #ifndef SQLITE_OMIT_VIRTUALTABLE /* ** Check to see if the given expression is of the form ** ** column OP expr ** ** where OP is one of MATCH, GLOB, LIKE or REGEXP and "column" is a ** column of a virtual table. ** ** If it is then return TRUE. If not, return FALSE. */ static int isMatchOfColumn( Expr *pExpr, /* Test this expression */ unsigned char *peOp2 /* OUT: 0 for MATCH, or else an op2 value */ ){ struct Op2 { const char *zOp; unsigned char eOp2; } aOp[] = { { "match", SQLITE_INDEX_CONSTRAINT_MATCH }, { "glob", SQLITE_INDEX_CONSTRAINT_GLOB }, { "like", SQLITE_INDEX_CONSTRAINT_LIKE }, { "regexp", SQLITE_INDEX_CONSTRAINT_REGEXP } }; ExprList *pList; Expr *pCol; /* Column reference */ int i; if( pExpr->op!=TK_FUNCTION ){ return 0; } pList = pExpr->x.pList; if( pList==0 || pList->nExpr!=2 ){ return 0; } pCol = pList->a[1].pExpr; if( pCol->op!=TK_COLUMN || !IsVirtual(pCol->pTab) ){ return 0; } for(i=0; i<ArraySize(aOp); i++){ if( sqlite3StrICmp(pExpr->u.zToken, aOp[i].zOp)==0 ){ *peOp2 = aOp[i].eOp2; return 1; } } return 0; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ /* ** If the pBase expression originated in the ON or USING clause of ** a join, then transfer the appropriate markings over to derived. */ |
︙ | ︙ | |||
872 873 874 875 876 877 878 879 880 881 882 883 884 885 | Bitmask extraRight = 0; /* Extra dependencies on LEFT JOIN */ Expr *pStr1 = 0; /* RHS of LIKE/GLOB operator */ int isComplete = 0; /* RHS of LIKE/GLOB ends with wildcard */ int noCase = 0; /* uppercase equivalent to lowercase */ int op; /* Top-level operator. pExpr->op */ Parse *pParse = pWInfo->pParse; /* Parsing context */ sqlite3 *db = pParse->db; /* Database connection */ if( db->mallocFailed ){ return; } pTerm = &pWC->a[idxTerm]; pMaskSet = &pWInfo->sMaskSet; pExpr = pTerm->pExpr; | > | 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 | Bitmask extraRight = 0; /* Extra dependencies on LEFT JOIN */ Expr *pStr1 = 0; /* RHS of LIKE/GLOB operator */ int isComplete = 0; /* RHS of LIKE/GLOB ends with wildcard */ int noCase = 0; /* uppercase equivalent to lowercase */ int op; /* Top-level operator. pExpr->op */ Parse *pParse = pWInfo->pParse; /* Parsing context */ sqlite3 *db = pParse->db; /* Database connection */ unsigned char eOp2; /* op2 value for LIKE/REGEXP/GLOB */ if( db->mallocFailed ){ return; } pTerm = &pWC->a[idxTerm]; pMaskSet = &pWInfo->sMaskSet; pExpr = pTerm->pExpr; |
︙ | ︙ | |||
1095 1096 1097 1098 1099 1100 1101 | #ifndef SQLITE_OMIT_VIRTUALTABLE /* Add a WO_MATCH auxiliary term to the constraint set if the ** current expression is of the form: column MATCH expr. ** This information is used by the xBestIndex methods of ** virtual tables. The native query optimizer does not attempt ** to do anything with MATCH functions. */ | | > | 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 | #ifndef SQLITE_OMIT_VIRTUALTABLE /* Add a WO_MATCH auxiliary term to the constraint set if the ** current expression is of the form: column MATCH expr. ** This information is used by the xBestIndex methods of ** virtual tables. The native query optimizer does not attempt ** to do anything with MATCH functions. */ if( isMatchOfColumn(pExpr, &eOp2) ){ int idxNew; Expr *pRight, *pLeft; WhereTerm *pNewTerm; Bitmask prereqColumn, prereqExpr; pRight = pExpr->x.pList->a[0].pExpr; pLeft = pExpr->x.pList->a[1].pExpr; prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight); prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft); if( (prereqExpr & prereqColumn)==0 ){ Expr *pNewExpr; pNewExpr = sqlite3PExpr(pParse, TK_MATCH, 0, sqlite3ExprDup(db, pRight, 0), 0); idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC); testcase( idxNew==0 ); pNewTerm = &pWC->a[idxNew]; pNewTerm->prereqRight = prereqExpr; pNewTerm->leftCursor = pLeft->iTable; pNewTerm->u.leftColumn = pLeft->iColumn; pNewTerm->eOperator = WO_MATCH; pNewTerm->eMatchOp = eOp2; markTermAsChild(pWC, idxNew, idxTerm); pTerm = &pWC->a[idxTerm]; pTerm->wtFlags |= TERM_COPIED; pNewTerm->prereqAll = pTerm->prereqAll; } } #endif /* SQLITE_OMIT_VIRTUALTABLE */ |
︙ | ︙ |
Changes to test/vtab1.test.
︙ | ︙ | |||
1302 1303 1304 1305 1306 1307 1308 | 1.1 "SELECT a FROM e6 WHERE b>'James'" {4 1 5} {xFilter {SELECT rowid, * FROM 't6' WHERE b > ?} James} 1.2 "SELECT a FROM e6 WHERE b>='J' AND b<'K'" {3 4} {xFilter {SELECT rowid, * FROM 't6' WHERE b >= ? AND b < ?} J K} 1.3 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4} | | | | | | 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 | 1.1 "SELECT a FROM e6 WHERE b>'James'" {4 1 5} {xFilter {SELECT rowid, * FROM 't6' WHERE b > ?} James} 1.2 "SELECT a FROM e6 WHERE b>='J' AND b<'K'" {3 4} {xFilter {SELECT rowid, * FROM 't6' WHERE b >= ? AND b < ?} J K} 1.3 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4} {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} J%} 1.4 "SELECT a FROM e6 WHERE b LIKE 'j%'" {3 4} {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} j%} } { set echo_module {} do_execsql_test 18.$tn.1 $sql $res do_test 18.$tn.2 { lrange $::echo_module 2 end } $filter } do_execsql_test 18.2.0 { PRAGMA case_sensitive_like = ON } foreach {tn sql res filter} { 2.1 "SELECT a FROM e6 WHERE b LIKE 'J%'" {3 4} {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} J%} 2.2 "SELECT a FROM e6 WHERE b LIKE 'j%'" {} {xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} j%} } { set echo_module {} do_execsql_test 18.$tn.1 $sql $res do_test 18.$tn.2 { lrange $::echo_module 2 end } $filter } do_execsql_test 18.2.x { PRAGMA case_sensitive_like = OFF } |
︙ | ︙ |
Changes to test/vtabE.test.
︙ | ︙ | |||
42 43 44 45 46 47 48 | SELECT t1.*, t2.*, abs(t3.b + abs(t2.value + abs(t1.value))) FROM t1 LEFT JOIN t2 ON t2.name = t1.arrayname LEFT JOIN t3 ON t3.a=t2.value WHERE t1.name = 'vtabE' ORDER BY t1.value, t2.value; } } {vtabE vtabE1 11 vtabE1 w x {} vtabE vtabE1 11 vtabE1 y z {} vtabE vtabE2 22 vtabE2 a b {} vtabE vtabE2 22 vtabE2 c d {}} | > > | 42 43 44 45 46 47 48 49 50 | SELECT t1.*, t2.*, abs(t3.b + abs(t2.value + abs(t1.value))) FROM t1 LEFT JOIN t2 ON t2.name = t1.arrayname LEFT JOIN t3 ON t3.a=t2.value WHERE t1.name = 'vtabE' ORDER BY t1.value, t2.value; } } {vtabE vtabE1 11 vtabE1 w x {} vtabE vtabE1 11 vtabE1 y z {} vtabE vtabE2 22 vtabE2 a b {} vtabE vtabE2 22 vtabE2 c d {}} finish_test |
Added test/vtabH.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 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 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 | # 2015 Nov 24 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. Specifically, # it tests that the GLOB, LIKE and REGEXP operators are correctly exposed # to virtual table implementations. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix vtabH ifcapable !vtab { finish_test return } register_echo_module db do_execsql_test 1.0 { CREATE TABLE t6(a, b TEXT); CREATE INDEX i6 ON t6(b, a); CREATE VIRTUAL TABLE e6 USING echo(t6); } foreach {tn sql expect} { 1 "SELECT * FROM e6 WHERE b LIKE 'abc'" { xBestIndex {SELECT rowid, * FROM 't6' WHERE b like ?} xFilter {SELECT rowid, * FROM 't6' WHERE b like ?} abc } 2 "SELECT * FROM e6 WHERE b GLOB 'abc'" { xBestIndex {SELECT rowid, * FROM 't6' WHERE b glob ?} xFilter {SELECT rowid, * FROM 't6' WHERE b glob ?} abc } } { do_test 1.$tn { set echo_module {} execsql $sql set ::echo_module } [list {*}$expect] } #-------------------------------------------------------------------------- register_tclvar_module db set ::xyz 10 do_execsql_test 2.0 { CREATE VIRTUAL TABLE vars USING tclvar; SELECT * FROM vars WHERE name = 'xyz'; } {xyz {} 10} set x1 aback set x2 abaft set x3 abandon set x4 abandonint set x5 babble set x6 baboon set x7 backbone set x8 backarrow set x9 castle db func glob gfunc proc gfunc {a b} { incr ::gfunc return 1 } db func like lfunc proc lfunc {a b} { incr ::gfunc 100 return 1 } db func regexp rfunc proc rfunc {a b} { incr ::gfunc 10000 return 1 } foreach ::tclvar_set_omit {0 1} { foreach {tn expr res cnt} { 1 {value GLOB 'aban*'} {x3 abandon x4 abandonint} 2 2 {value LIKE '%ac%'} {x1 aback x7 backbone x8 backarrow} 300 3 {value REGEXP '^......$'} {x5 babble x6 baboon x9 castle} 30000 } { if {$tn==3} breakpoint db cache flush set ::gfunc 0 if {$::tclvar_set_omit} {set cnt 0} do_test 2.$tclvar_set_omit.$tn.1 { execsql "SELECT name, value FROM vars WHERE name MATCH 'x*' AND $expr" } $res do_test 2.$tclvar_set_omit.$tn.2 { set ::gfunc } $cnt } } finish_test |