Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | The case_sensitive_like pragma added. Test cases added for the LIKE optimization. (CVS 2592) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
72ee21c05e618b6f46f5460f8c85779c |
User & Date: | drh 2005-08-14 01:20:38.000 |
Context
2005-08-14
| ||
01:34 | Declare local-use functions as static. Ticket #1363. (CVS 2593) (check-in: 94efd79088 user: drh tags: trunk) | |
01:20 | The case_sensitive_like pragma added. Test cases added for the LIKE optimization. (CVS 2592) (check-in: 72ee21c05e user: drh tags: trunk) | |
2005-08-13
| ||
18:15 | Provide grave accent quoting of identifiers for MySQL compatibility. Ticket #1337. (CVS 2591) (check-in: 6b7a4e9752 user: drh tags: trunk) | |
Changes
Changes to src/callback.c.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** ** This file contains functions used to access the internal hash tables ** of user defined functions and collation sequences. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** ** This file contains functions used to access the internal hash tables ** of user defined functions and collation sequences. ** ** $Id: callback.c,v 1.3 2005/08/14 01:20:38 drh Exp $ */ #include "sqliteInt.h" /* ** Invoke the 'collation needed' callback to request a collation sequence ** in the database text encoding of name zName, length nName. |
︙ | ︙ | |||
143 144 145 146 147 148 149 | ** array of three CollSeq structures. The first is the collation sequence ** prefferred for UTF-8, the second UTF-16le, and the third UTF-16be. ** ** Stored immediately after the three collation sequences is a copy of ** the collation sequence name. A pointer to this string is stored in ** each collation sequence structure. */ | | | 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 | ** array of three CollSeq structures. The first is the collation sequence ** prefferred for UTF-8, the second UTF-16le, and the third UTF-16be. ** ** Stored immediately after the three collation sequences is a copy of ** the collation sequence name. A pointer to this string is stored in ** each collation sequence structure. */ static CollSeq *findCollSeqEntry( sqlite3 *db, const char *zName, int nName, int create ){ CollSeq *pColl; if( nName<0 ) nName = strlen(zName); |
︙ | ︙ | |||
282 283 284 285 286 287 288 | } /* If the createFlag parameter is true, and the seach did not reveal an ** exact match for the name, number of arguments and encoding, then add a ** new entry to the hash table and return it. */ if( createFlag && bestmatch<6 && | | < | 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 | } /* If the createFlag parameter is true, and the seach did not reveal an ** exact match for the name, number of arguments and encoding, then add a ** new entry to the hash table and return it. */ if( createFlag && bestmatch<6 && (pBest = sqliteMalloc(sizeof(*pBest)+nName)) ){ pBest->nArg = nArg; pBest->pNext = pFirst; pBest->iPrefEnc = enc; memcpy(pBest->zName, zName, nName); pBest->zName[nName] = 0; if( pBest==sqlite3HashInsert(&db->aFunc,pBest->zName,nName,(void*)pBest) ){ sqliteFree(pBest); return 0; } |
︙ | ︙ |
Changes to src/func.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This file contains the C functions that implement various SQL ** functions of SQLite. ** ** There is only one exported symbol in this file - the function ** sqliteRegisterBuildinFunctions() found at the bottom of the file. ** All other code has file scope. ** | | > > > | 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 | ** This file contains the C functions that implement various SQL ** functions of SQLite. ** ** There is only one exported symbol in this file - the function ** sqliteRegisterBuildinFunctions() found at the bottom of the file. ** All other code has file scope. ** ** $Id: func.c,v 1.104 2005/08/14 01:20:38 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> #include <math.h> #include <stdlib.h> #include <assert.h> #include "vdbeInt.h" #include "os.h" /* ** Return the collating function associated with a function. */ static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){ return context->pColl; } /* ** Implementation of the non-aggregate min() and max() functions */ |
︙ | ︙ | |||
304 305 306 307 308 309 310 311 | */ struct compareInfo { u8 matchAll; u8 matchOne; u8 matchSet; u8 noCase; }; static const struct compareInfo globInfo = { '*', '?', '[', 0 }; | > < | | | < | | | < | 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 | */ struct compareInfo { u8 matchAll; u8 matchOne; u8 matchSet; u8 noCase; }; static const struct compareInfo globInfo = { '*', '?', '[', 0 }; /* The correct SQL-92 behavior is for the LIKE operator to ignore ** case. Thus 'a' LIKE 'A' would be true. */ static const struct compareInfo likeInfoNorm = { '%', '_', 0, 1 }; /* If SQLITE_CASE_SENSITIVE_LIKE is defined, then the LIKE operator ** is case sensitive causing 'a' LIKE 'A' to be false */ static const struct compareInfo likeInfoAlt = { '%', '_', 0, 0 }; /* ** X is a pointer to the first byte of a UTF-8 character. Increment ** X so that it points to the next character. This only works right ** if X points to a well-formed UTF-8 string. */ #define sqliteNextChar(X) while( (0xc0&*++(X))==0x80 ){} |
︙ | ︙ | |||
455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 | zString++; prevEscape = 0; } } return *zString==0; } /* ** Implementation of the like() SQL function. This function implements ** the build-in LIKE operator. The first argument to the function is the ** pattern and the second argument is the string. So, the SQL statements: ** ** A LIKE B ** ** is implemented as like(B,A). ** | > > > > > > > > > | | | 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 | zString++; prevEscape = 0; } } return *zString==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 int sqlite3_like_count = 0; #endif /* ** Implementation of the like() SQL function. This function implements ** the build-in LIKE operator. The first argument to the function is the ** pattern and the second argument is the string. So, the SQL statements: ** ** A LIKE B ** ** is implemented as like(B,A). ** ** This same function (with a different compareInfo structure) computes ** the GLOB operator. */ static void likeFunc( sqlite3_context *context, int argc, sqlite3_value **argv ){ const unsigned char *zA = sqlite3_value_text(argv[0]); |
︙ | ︙ | |||
489 490 491 492 493 494 495 | sqlite3_result_error(context, "ESCAPE expression must be a single character", -1); return; } escape = sqlite3ReadUtf8(zEsc); } if( zA && zB ){ | | < < | < < < < < < < < < < | < | | | 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 | sqlite3_result_error(context, "ESCAPE expression must be a single character", -1); return; } escape = sqlite3ReadUtf8(zEsc); } if( zA && zB ){ struct compareInfo *pInfo = sqlite3_user_data(context); #ifdef SQLITE_TEST sqlite3_like_count++; #endif sqlite3_result_int(context, patternCompare(zA, zB, pInfo, escape)); } } /* ** Implementation of the NULLIF(x,y) function. The result is the first ** argument if the arguments are different. The result is NULL if the ** arguments are equal to each other. |
︙ | ︙ | |||
967 968 969 970 971 972 973 | { "upper", 1, 0, SQLITE_UTF8, 0, upperFunc }, { "lower", 1, 0, SQLITE_UTF8, 0, lowerFunc }, { "coalesce", -1, 0, SQLITE_UTF8, 0, ifnullFunc }, { "coalesce", 0, 0, SQLITE_UTF8, 0, 0 }, { "coalesce", 1, 0, SQLITE_UTF8, 0, 0 }, { "ifnull", 2, 0, SQLITE_UTF8, 1, ifnullFunc }, { "random", -1, 0, SQLITE_UTF8, 0, randomFunc }, | < < < | 964 965 966 967 968 969 970 971 972 973 974 975 976 977 | { "upper", 1, 0, SQLITE_UTF8, 0, upperFunc }, { "lower", 1, 0, SQLITE_UTF8, 0, lowerFunc }, { "coalesce", -1, 0, SQLITE_UTF8, 0, ifnullFunc }, { "coalesce", 0, 0, SQLITE_UTF8, 0, 0 }, { "coalesce", 1, 0, SQLITE_UTF8, 0, 0 }, { "ifnull", 2, 0, SQLITE_UTF8, 1, ifnullFunc }, { "random", -1, 0, SQLITE_UTF8, 0, randomFunc }, { "nullif", 2, 0, SQLITE_UTF8, 1, nullifFunc }, { "sqlite_version", 0, 0, SQLITE_UTF8, 0, versionFunc}, { "quote", 1, 0, SQLITE_UTF8, 0, quoteFunc }, { "last_insert_rowid", 0, 1, SQLITE_UTF8, 0, last_insert_rowid }, { "changes", 0, 1, SQLITE_UTF8, 0, changes }, { "total_changes", 0, 1, SQLITE_UTF8, 0, total_changes }, #ifdef SQLITE_SOUNDEX |
︙ | ︙ | |||
1041 1042 1043 1044 1045 1046 1047 | if( pFunc && aAggs[i].needCollSeq ){ pFunc->needCollSeq = 1; } } } sqlite3RegisterDateTimeFunctions(db); #ifdef SQLITE_SSE | > > > > > > > > | > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > | 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 | if( pFunc && aAggs[i].needCollSeq ){ pFunc->needCollSeq = 1; } } } sqlite3RegisterDateTimeFunctions(db); #ifdef SQLITE_SSE sqlite3SseFunctions(db); #endif #ifdef SQLITE_CASE_SENSITIVE_LIKE sqlite3RegisterLikeFunctions(db, 1); #else sqlite3RegisterLikeFunctions(db, 0); #endif } /* ** Set the LIKEOPT flag on the 2-argument function with the given name. */ static void setLikeOptFlag(sqlite3 *db, const char *zName){ FuncDef *pDef; pDef = sqlite3FindFunction(db, zName, strlen(zName), 2, SQLITE_UTF8, 0); if( pDef ){ pDef->flags = SQLITE_FUNC_LIKEOPT; } } /* ** Register the built-in LIKE and GLOB functions. The caseSensitive ** parameter determines whether or not the LIKE operator is case ** sensitive. GLOB is always case sensitive. */ void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){ struct compareInfo *pInfo; if( caseSensitive ){ pInfo = (struct compareInfo*)&likeInfoAlt; }else{ pInfo = (struct compareInfo*)&likeInfoNorm; } sqlite3_create_function(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0); sqlite3_create_function(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0); sqlite3_create_function(db, "glob", 2, SQLITE_UTF8, (struct compareInfo*)&globInfo, likeFunc, 0,0); setLikeOptFlag(db, "glob"); if( caseSensitive ){ setLikeOptFlag(db, "like"); } } /* ** pExpr points to an expression which implements a function. If ** it is appropriate to apply the LIKE optimization to that function ** then set aWc[0] through aWc[2] to the wildcard characters and ** return TRUE. If the function is not a LIKE-style function then ** return FALSE. */ int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, char *aWc){ FuncDef *pDef; if( pExpr->op!=TK_FUNCTION ){ return 0; } if( pExpr->pList->nExpr!=2 ){ return 0; } pDef = sqlite3FindFunction(db, pExpr->token.z, pExpr->token.n, 2, SQLITE_UTF8, 0); if( pDef==0 || (pDef->flags & SQLITE_FUNC_LIKEOPT)==0 ){ return 0; } /* The memcpy() statement assumes that the wildcard characters are ** the first three statements in the compareInfo structure. The ** asserts() that follow verify that assumption */ memcpy(aWc, pDef->pUserData, 3); assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll ); assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne ); assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet ); return 1; } |
Changes to src/main.c.
︙ | ︙ | |||
10 11 12 13 14 15 16 | ** ************************************************************************* ** Main file for the SQLite library. The routines in this file ** implement the programmer interface to the library. Routines in ** other files are for internal use by SQLite and should not be ** accessed by users of the library. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** Main file for the SQLite library. The routines in this file ** implement the programmer interface to the library. Routines in ** other files are for internal use by SQLite and should not be ** accessed by users of the library. ** ** $Id: main.c,v 1.298 2005/08/14 01:20:39 drh Exp $ */ #include "sqliteInt.h" #include "os.h" #include <ctype.h> /* ** The following constant value is used by the SQLITE_BIGENDIAN and |
︙ | ︙ | |||
467 468 469 470 471 472 473 474 475 476 477 478 479 480 | }else{ sqlite3ExpirePreparedStatements(db); } } p = sqlite3FindFunction(db, zFunctionName, nName, nArg, enc, 1); if( p==0 ) return SQLITE_NOMEM; p->xFunc = xFunc; p->xStep = xStep; p->xFinalize = xFinal; p->pUserData = pUserData; return SQLITE_OK; } #ifndef SQLITE_OMIT_UTF16 | > | 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 | }else{ sqlite3ExpirePreparedStatements(db); } } p = sqlite3FindFunction(db, zFunctionName, nName, nArg, enc, 1); if( p==0 ) return SQLITE_NOMEM; p->flags = 0; p->xFunc = xFunc; p->xStep = xStep; p->xFinalize = xFinal; p->pUserData = pUserData; return SQLITE_OK; } #ifndef SQLITE_OMIT_UTF16 |
︙ | ︙ |
Changes to src/pragma.c.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2003 April 6 ** ** 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 contains code used to implement the PRAGMA command. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2003 April 6 ** ** 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 contains code used to implement the PRAGMA command. ** ** $Id: pragma.c,v 1.98 2005/08/14 01:20:39 drh Exp $ */ #include "sqliteInt.h" #include "os.h" #include <ctype.h> /* Ignore this whole file if pragmas are disabled */ |
︙ | ︙ | |||
599 600 601 602 603 604 605 | } }else #endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */ #ifndef NDEBUG if( sqlite3StrICmp(zLeft, "parser_trace")==0 ){ extern void sqlite3ParserTrace(FILE*, char *); | > | | | | > > > > > > > > > > | 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 | } }else #endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */ #ifndef NDEBUG if( sqlite3StrICmp(zLeft, "parser_trace")==0 ){ extern void sqlite3ParserTrace(FILE*, char *); if( zRight ){ if( getBoolean(zRight) ){ sqlite3ParserTrace(stderr, "parser: "); }else{ sqlite3ParserTrace(0, 0); } } }else #endif /* Reinstall the LIKE and GLOB functions. The variant of LIKE ** used will be case sensitive or not depending on the RHS. */ if( sqlite3StrICmp(zLeft, "case_sensitive_like")==0 ){ if( zRight ){ sqlite3RegisterLikeFunctions(db, getBoolean(zRight)); } }else #ifndef SQLITE_OMIT_INTEGRITY_CHECK if( sqlite3StrICmp(zLeft, "integrity_check")==0 ){ int i, j, addr; /* Code that initializes the integrity check program. Set the ** error count 0 |
︙ | ︙ |
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** 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. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** 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. ** ************************************************************************* ** Internal interface definitions for SQLite. ** ** @(#) $Id: sqliteInt.h,v 1.402 2005/08/14 01:20:39 drh Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ /* ** These #defines should enable >2GB file support on Posix if the ** underlying operating system supports it. If the OS lacks |
︙ | ︙ | |||
492 493 494 495 496 497 498 | /* ** Each SQL function is defined by an instance of the following ** structure. A pointer to this structure is stored in the sqlite.aFunc ** hash table. When multiple functions have the same name, the hash table ** points to a linked list of these structures. */ struct FuncDef { | < | > > | > > > > > | 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 | /* ** Each SQL function is defined by an instance of the following ** structure. A pointer to this structure is stored in the sqlite.aFunc ** hash table. When multiple functions have the same name, the hash table ** points to a linked list of these structures. */ struct FuncDef { i16 nArg; /* Number of arguments. -1 means unlimited */ u8 iPrefEnc; /* Preferred text encoding (SQLITE_UTF8, 16LE, 16BE) */ u8 needCollSeq; /* True if sqlite3GetFuncCollSeq() might be called */ u8 flags; /* Some combination of SQLITE_FUNC_* */ void *pUserData; /* User data parameter */ FuncDef *pNext; /* Next function with same name */ void (*xFunc)(sqlite3_context*,int,sqlite3_value**); /* Regular function */ void (*xStep)(sqlite3_context*,int,sqlite3_value**); /* Aggregate step */ void (*xFinalize)(sqlite3_context*); /* Aggregate finializer */ char zName[1]; /* SQL name of the function. MUST BE LAST */ }; /* ** Possible values for FuncDef.flags */ #define SQLITE_FUNC_LIKEOPT 0x01 /* Candidate for the LIKE optimization */ /* ** information about each column of an SQL table is held in an instance ** of this structure. */ struct Column { char *zName; /* Name of this column */ Expr *pDflt; /* Default value of this column */ |
︙ | ︙ | |||
1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 | CollSeq *sqlite3GetCollSeq(sqlite3*, CollSeq *, const char *, int); char sqlite3AffinityType(const Token*); void sqlite3Analyze(Parse*, Token*, Token*); int sqlite3InvokeBusyHandler(BusyHandler*); int sqlite3FindDb(sqlite3*, Token*); void sqlite3AnalysisLoad(sqlite3*,int iDB); void sqlite3DefaultRowEst(Index*); #ifdef SQLITE_SSE #include "sseInt.h" #endif #endif | > > | 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 | CollSeq *sqlite3GetCollSeq(sqlite3*, CollSeq *, const char *, int); char sqlite3AffinityType(const Token*); void sqlite3Analyze(Parse*, Token*, Token*); int sqlite3InvokeBusyHandler(BusyHandler*); int sqlite3FindDb(sqlite3*, Token*); void sqlite3AnalysisLoad(sqlite3*,int iDB); void sqlite3DefaultRowEst(Index*); void sqlite3RegisterLikeFunctions(sqlite3*, int); int sqlite3IsLikeFunction(sqlite3*,Expr*,char*); #ifdef SQLITE_SSE #include "sseInt.h" #endif #endif |
Changes to src/test1.c.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** Code for testing the printf() interface to SQLite. This code ** is not included in the SQLite library. It is used for automated ** testing of the SQLite library. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** Code for testing the printf() interface to SQLite. This code ** is not included in the SQLite library. It is used for automated ** testing of the SQLite library. ** ** $Id: test1.c,v 1.154 2005/08/14 01:20:39 drh Exp $ */ #include "sqliteInt.h" #include "tcl.h" #include "os.h" #include <stdlib.h> #include <string.h> |
︙ | ︙ | |||
2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 | Tcl_SetVar2(interp, "sqlite_options", "autovacuum", "1", TCL_GLOBAL_ONLY); #endif /* SQLITE_OMIT_AUTOVACUUM */ #if !defined(SQLITE_DEFAULT_AUTOVACUUM) || SQLITE_DEFAULT_AUTOVACUUM==0 Tcl_SetVar2(interp,"sqlite_options","default_autovacuum","0",TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp,"sqlite_options","default_autovacuum","1",TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_BLOB_LITERAL Tcl_SetVar2(interp, "sqlite_options", "bloblit", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "bloblit", "1", TCL_GLOBAL_ONLY); #endif | > > > > > > | 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 2806 2807 2808 2809 2810 2811 | Tcl_SetVar2(interp, "sqlite_options", "autovacuum", "1", TCL_GLOBAL_ONLY); #endif /* SQLITE_OMIT_AUTOVACUUM */ #if !defined(SQLITE_DEFAULT_AUTOVACUUM) || SQLITE_DEFAULT_AUTOVACUUM==0 Tcl_SetVar2(interp,"sqlite_options","default_autovacuum","0",TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp,"sqlite_options","default_autovacuum","1",TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_BETWEEN_OPTIMIZATION Tcl_SetVar2(interp, "sqlite_options", "between_opt", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "between_opt", "1", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_BLOB_LITERAL Tcl_SetVar2(interp, "sqlite_options", "bloblit", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "bloblit", "1", TCL_GLOBAL_ONLY); #endif |
︙ | ︙ | |||
2864 2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 2875 2876 2877 2878 2879 2880 2881 2882 2883 | #endif #ifdef SQLITE_OMIT_INTEGRITY_CHECK Tcl_SetVar2(interp, "sqlite_options", "integrityck", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "integrityck", "1", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_MEMORYDB Tcl_SetVar2(interp, "sqlite_options", "memorydb", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "memorydb", "1", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_PAGER_PRAGMAS Tcl_SetVar2(interp, "sqlite_options", "pager_pragmas", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "pager_pragmas", "1", TCL_GLOBAL_ONLY); #endif | > > > > > > > > > > > > | 2870 2871 2872 2873 2874 2875 2876 2877 2878 2879 2880 2881 2882 2883 2884 2885 2886 2887 2888 2889 2890 2891 2892 2893 2894 2895 2896 2897 2898 2899 2900 2901 | #endif #ifdef SQLITE_OMIT_INTEGRITY_CHECK Tcl_SetVar2(interp, "sqlite_options", "integrityck", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "integrityck", "1", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_LIKE_OPTIMIZATION Tcl_SetVar2(interp, "sqlite_options", "like_opt", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "like_opt", "1", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_MEMORYDB Tcl_SetVar2(interp, "sqlite_options", "memorydb", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "memorydb", "1", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_OR_OPTIMIZATION Tcl_SetVar2(interp, "sqlite_options", "or_opt", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "or_opt", "1", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_PAGER_PRAGMAS Tcl_SetVar2(interp, "sqlite_options", "pager_pragmas", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "pager_pragmas", "1", TCL_GLOBAL_ONLY); #endif |
︙ | ︙ | |||
3090 3091 3092 3093 3094 3095 3096 3097 3098 3099 3100 3101 3102 3103 3104 3105 3106 3107 3108 3109 3110 3111 3112 3113 3114 3115 3116 | extern int sqlite3_os_trace; extern int sqlite3_where_trace; extern int sqlite3_sync_count, sqlite3_fullsync_count; extern int sqlite3_opentemp_count; extern int sqlite3_memUsed; extern int sqlite3_memMax; extern char sqlite3_query_plan[]; static char *query_plan = sqlite3_query_plan; for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){ Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0); } for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){ Tcl_CreateObjCommand(interp, aObjCmd[i].zName, aObjCmd[i].xProc, aObjCmd[i].clientData, 0); } Tcl_LinkVar(interp, "sqlite_search_count", (char*)&sqlite3_search_count, TCL_LINK_INT); Tcl_LinkVar(interp, "sqlite_sort_count", (char*)&sqlite3_sort_count, TCL_LINK_INT); Tcl_LinkVar(interp, "sqlite_interrupt_count", (char*)&sqlite3_interrupt_count, TCL_LINK_INT); Tcl_LinkVar(interp, "sqlite_open_file_count", (char*)&sqlite3_open_file_count, TCL_LINK_INT); Tcl_LinkVar(interp, "sqlite_current_time", (char*)&sqlite3_current_time, TCL_LINK_INT); Tcl_LinkVar(interp, "sqlite_os_trace", | > > > | 3108 3109 3110 3111 3112 3113 3114 3115 3116 3117 3118 3119 3120 3121 3122 3123 3124 3125 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 | extern int sqlite3_os_trace; extern int sqlite3_where_trace; extern int sqlite3_sync_count, sqlite3_fullsync_count; extern int sqlite3_opentemp_count; extern int sqlite3_memUsed; extern int sqlite3_memMax; extern char sqlite3_query_plan[]; extern int sqlite3_like_count; static char *query_plan = sqlite3_query_plan; for(i=0; i<sizeof(aCmd)/sizeof(aCmd[0]); i++){ Tcl_CreateCommand(interp, aCmd[i].zName, aCmd[i].xProc, 0, 0); } for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){ Tcl_CreateObjCommand(interp, aObjCmd[i].zName, aObjCmd[i].xProc, aObjCmd[i].clientData, 0); } Tcl_LinkVar(interp, "sqlite_search_count", (char*)&sqlite3_search_count, TCL_LINK_INT); Tcl_LinkVar(interp, "sqlite_sort_count", (char*)&sqlite3_sort_count, TCL_LINK_INT); Tcl_LinkVar(interp, "sqlite_like_count", (char*)&sqlite3_like_count, TCL_LINK_INT); Tcl_LinkVar(interp, "sqlite_interrupt_count", (char*)&sqlite3_interrupt_count, TCL_LINK_INT); Tcl_LinkVar(interp, "sqlite_open_file_count", (char*)&sqlite3_open_file_count, TCL_LINK_INT); Tcl_LinkVar(interp, "sqlite_current_time", (char*)&sqlite3_current_time, TCL_LINK_INT); Tcl_LinkVar(interp, "sqlite_os_trace", |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
105 106 107 108 109 110 111 112 113 114 115 116 117 118 | } pOp = &p->aOp[i]; pOp->opcode = op; pOp->p1 = p1; pOp->p2 = p2; pOp->p3 = 0; pOp->p3type = P3_NOTUSED; #ifdef SQLITE_DEBUG if( sqlite3_vdbe_addop_trace ) sqlite3VdbePrintOp(0, i, &p->aOp[i]); #endif return i; } /* | > | 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 | } pOp = &p->aOp[i]; pOp->opcode = op; pOp->p1 = p1; pOp->p2 = p2; pOp->p3 = 0; pOp->p3type = P3_NOTUSED; p->expired = 0; #ifdef SQLITE_DEBUG if( sqlite3_vdbe_addop_trace ) sqlite3VdbePrintOp(0, i, &p->aOp[i]); #endif return i; } /* |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is reponsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is reponsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** ** $Id: where.c,v 1.162 2005/08/14 01:20:39 drh Exp $ */ #include "sqliteInt.h" /* ** The number of bits in a Bitmask. "BMS" means "BitMask Size". */ #define BMS (sizeof(Bitmask)*8) |
︙ | ︙ | |||
463 464 465 466 467 468 469 470 471 472 473 474 475 476 | ** can be optimized using inequality constraints. Return TRUE if it is ** so and false if not. ** ** In order for the operator to be optimizible, the RHS must be a string ** literal that does not begin with a wildcard. */ static int isLikeOrGlob( Expr *pExpr, /* Test this expression */ int *pnPattern, /* Number of non-wildcard prefix characters */ int *pisComplete /* True if the only wildcard is % in the last character */ ){ const char *z; Expr *pRight, *pLeft; int c, cnt; | > > | | | < < < < < < < < < < < < < < < < < < < < < | | | | | 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 | ** can be optimized using inequality constraints. Return TRUE if it is ** so and false if not. ** ** In order for the operator to be optimizible, the RHS must be a string ** literal that does not begin with a wildcard. */ static int isLikeOrGlob( sqlite3 *db, /* The database */ Expr *pExpr, /* Test this expression */ int *pnPattern, /* Number of non-wildcard prefix characters */ int *pisComplete /* True if the only wildcard is % in the last character */ ){ const char *z; Expr *pRight, *pLeft; ExprList *pList; int c, cnt; char wc[3]; if( !sqlite3IsLikeFunction(db, pExpr, wc) ){ return 0; } pList = pExpr->pList; pRight = pList->a[0].pExpr; if( pRight->op!=TK_STRING ){ return 0; } pLeft = pList->a[1].pExpr; if( pLeft->op!=TK_COLUMN ){ return 0; } sqlite3DequoteExpr(pRight); z = pRight->token.z; for(cnt=0; (c=z[cnt])!=0 && c!=wc[0] && c!=wc[1] && c!=wc[2]; cnt++){} if( cnt==0 || 255==(u8)z[cnt] ){ return 0; } *pisComplete = z[cnt]==wc[0] && z[cnt+1]==0; *pnPattern = cnt; return 1; } #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */ /* ** The input to this routine is an WhereTerm structure with only the |
︙ | ︙ | |||
667 668 669 670 671 672 673 | } #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION /* Add constraints to reduce the search space on a LIKE or GLOB ** operator. */ | | | 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 | } #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION /* Add constraints to reduce the search space on a LIKE or GLOB ** operator. */ if( isLikeOrGlob(pTerm->pWC->pParse->db, pExpr, &nPattern, &isComplete) ){ Expr *pLeft, *pRight; Expr *pStr1, *pStr2; Expr *pNewExpr1, *pNewExpr2; WhereTerm *pNewTerm1, *pNewTerm2; pLeft = pExpr->pList->a[1].pExpr; pRight = pExpr->pList->a[0].pExpr; pStr1 = sqlite3Expr(TK_STRING, 0, 0, 0); |
︙ | ︙ |
Added test/like.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 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 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 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 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 | # 2005 August 13 # # 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. The # focus of this file is testing the LIKE and GLOB operators and # in particular the optimizations that occur to help those operators # run faster. # # $Id: like.test,v 1.1 2005/08/14 01:20:40 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create some sample data to work with. # do_test like-1.0 { execsql { CREATE TABLE t1(x TEXT); } foreach str { a ab abc abcd acd abd bc bcd xyz ABC CDE {ABC abc xyz} } { db eval {INSERT INTO t1 VALUES($str)} } execsql { SELECT count(*) FROM t1; } } {12} # Test that both case sensitive and insensitive version of LIKE work. # do_test like-1.1 { execsql { SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; } } {ABC abc} do_test like-1.2 { execsql { SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1; } } {abc} do_test like-1.3 { execsql { SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1; } } {ABC abc} do_test like-1.4 { execsql { SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1; } } {ABC abc} do_test like-1.5 { execsql { PRAGMA case_sensitive_like=on; SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; } } {abc} do_test like-1.6 { execsql { SELECT x FROM t1 WHERE x GLOB 'abc' ORDER BY 1; } } {abc} do_test like-1.7 { execsql { SELECT x FROM t1 WHERE x LIKE 'ABC' ORDER BY 1; } } {ABC} do_test like-1.8 { execsql { SELECT x FROM t1 WHERE x LIKE 'aBc' ORDER BY 1; } } {} do_test like-1.9 { execsql { PRAGMA case_sensitive_like=off; SELECT x FROM t1 WHERE x LIKE 'abc' ORDER BY 1; } } {ABC abc} # Tests of the REGEXP operator # do_test like-2.1 { proc test_regexp {a b} { return [regexp $a $b] } db function regexp test_regexp execsql { SELECT x FROM t1 WHERE x REGEXP 'abc' ORDER BY 1; } } {{ABC abc xyz} abc abcd} do_test like-2.2 { execsql { SELECT x FROM t1 WHERE x REGEXP '^abc' ORDER BY 1; } } {abc abcd} # For the remaining tests, we need to have the like optimizations # enabled. # ifcapable !like_opt { finish_test return } # This procedure executes the SQL. Then it appends to the result the # "sort" or "nosort" keyword (as in the cksort procedure above) then # it appends the ::sqlite_query_plan variable. # proc queryplan {sql} { set ::sqlite_sort_count 0 set data [execsql $sql] if {$::sqlite_sort_count} {set x sort} {set x nosort} lappend data $x return [concat $data $::sqlite_query_plan] } # Perform tests on the like optimization. # # With no index on t1.x and with case sensitivity turned off, no optimization # is performed. # do_test like-3.1 { set sqlite_like_count 0 queryplan { SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; } } {ABC {ABC abc xyz} abc abcd sort t1 {}} do_test like-3.2 { set sqlite_like_count } {12} # With an index on t1.x and case sensitivity on, optimize completely. # do_test like-3.3 { set sqlite_like_count 0 execsql { PRAGMA case_sensitive_like=on; CREATE INDEX i1 ON t1(x); } queryplan { SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; } } {abc abcd nosort {} i1} do_test like-3.4 { set sqlite_like_count } 0 # Partial optimization when the pattern does not end in '%' # do_test like-3.5 { set sqlite_like_count 0 queryplan { SELECT x FROM t1 WHERE x LIKE 'a_c' ORDER BY 1; } } {abc nosort {} i1} do_test like-3.6 { set sqlite_like_count } 6 do_test like-3.7 { set sqlite_like_count 0 queryplan { SELECT x FROM t1 WHERE x LIKE 'ab%d' ORDER BY 1; } } {abcd abd nosort {} i1} do_test like-3.8 { set sqlite_like_count } 4 do_test like-3.9 { set sqlite_like_count 0 queryplan { SELECT x FROM t1 WHERE x LIKE 'a_c%' ORDER BY 1; } } {abc abcd nosort {} i1} do_test like-3.10 { set sqlite_like_count } 6 # No optimization when the pattern begins with a wildcard. # Note that the index is still used but only for sorting. # do_test like-3.11 { set sqlite_like_count 0 queryplan { SELECT x FROM t1 WHERE x LIKE '%bcd' ORDER BY 1; } } {abcd bcd nosort {} i1} do_test like-3.12 { set sqlite_like_count } 12 # No optimization for case insensitive LIKE # do_test like-3.13 { set sqlite_like_count 0 queryplan { PRAGMA case_sensitive_like=off; SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; } } {ABC {ABC abc xyz} abc abcd nosort {} i1} do_test like-3.14 { set sqlite_like_count } 12 # No optimization without an index. # do_test like-3.15 { set sqlite_like_count 0 queryplan { PRAGMA case_sensitive_like=on; DROP INDEX i1; SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1; } } {abc abcd sort t1 {}} do_test like-3.16 { set sqlite_like_count } 12 # No GLOB optimization without an index. # do_test like-3.17 { set sqlite_like_count 0 queryplan { SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; } } {abc abcd sort t1 {}} do_test like-3.18 { set sqlite_like_count } 12 # GLOB is optimized regardless of the case_sensitive_like setting. # do_test like-3.19 { set sqlite_like_count 0 queryplan { CREATE INDEX i1 ON t1(x); SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; } } {abc abcd nosort {} i1} do_test like-3.20 { set sqlite_like_count } 0 do_test like-3.21 { set sqlite_like_count 0 queryplan { PRAGMA case_sensitive_like=on; SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1; } } {abc abcd nosort {} i1} do_test like-3.22 { set sqlite_like_count } 0 do_test like-3.23 { set sqlite_like_count 0 queryplan { PRAGMA case_sensitive_like=off; SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1; } } {abd acd nosort {} i1} do_test like-3.24 { set sqlite_like_count } 6 finish_test |