Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | Add the ESCAPE clause to the LIKE operator. Not fully tested yet. (CVS 2107) |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
49268c2b7a84c4c618214dac8bef0f54 |
| User & Date: | danielk1977 2004-11-17 16:41:29 |
|
2004-11-18
| ||
| 02:04 | Clarify the documentation of the sqlite3_create_function API. Ticket #1004. (CVS 2108) (check-in: ae45ad86 user: drh tags: trunk) | |
|
2004-11-17
| ||
| 16:41 | Add the ESCAPE clause to the LIKE operator. Not fully tested yet. (CVS 2107) (check-in: 49268c2b user: danielk1977 tags: trunk) | |
| 10:22 | Extra tests and resulting bugfixes for btree cursors. (CVS 2106) (check-in: e1530854 user: danielk1977 tags: trunk) | |
Changes to src/func.c.
12 12 ** This file contains the C functions that implement various SQL 13 13 ** functions of SQLite. 14 14 ** 15 15 ** There is only one exported symbol in this file - the function 16 16 ** sqliteRegisterBuildinFunctions() found at the bottom of the file. 17 17 ** All other code has file scope. 18 18 ** 19 -** $Id: func.c,v 1.87 2004/11/14 21:56:30 drh Exp $ 19 +** $Id: func.c,v 1.88 2004/11/17 16:41:30 danielk1977 Exp $ 20 20 */ 21 21 #include <ctype.h> 22 22 #include <math.h> 23 23 #include <stdlib.h> 24 24 #include <assert.h> 25 25 #include "sqliteInt.h" 26 26 #include "vdbeInt.h" ................................................................................ 343 343 ** 344 344 ** This routine is usually quick, but can be N**2 in the worst case. 345 345 ** 346 346 ** Hints: to match '*' or '?', put them in "[]". Like this: 347 347 ** 348 348 ** abc[*]xyz Matches "abc*xyz" only 349 349 */ 350 -int patternCompare( 350 +static int patternCompare( 351 351 const u8 *zPattern, /* The glob pattern */ 352 352 const u8 *zString, /* The string to compare against the glob */ 353 - const struct compareInfo *pInfo /* Information about how to do the compare */ 353 + const struct compareInfo *pInfo, /* Information about how to do the compare */ 354 + const int esc /* The escape character */ 354 355 ){ 355 356 register int c; 356 357 int invert; 357 358 int seen; 358 359 int c2; 359 360 u8 matchOne = pInfo->matchOne; 360 361 u8 matchAll = pInfo->matchAll; 361 362 u8 matchSet = pInfo->matchSet; 362 363 u8 noCase = pInfo->noCase; 364 + int prevEscape = 0; /* True if the previous character was 'escape' */ 363 365 364 366 while( (c = *zPattern)!=0 ){ 365 - if( c==matchAll ){ 367 + if( !prevEscape && c==matchAll ){ 366 368 while( (c=zPattern[1]) == matchAll || c == matchOne ){ 367 369 if( c==matchOne ){ 368 370 if( *zString==0 ) return 0; 369 371 sqliteNextChar(zString); 370 372 } 371 373 zPattern++; 374 + } 375 + if( c && sqlite3ReadUtf8(&zPattern[1])==esc ){ 376 + u8 const *zTemp = &zPattern[1]; 377 + sqliteNextChar(zTemp); 378 + c = *zTemp; 372 379 } 373 380 if( c==0 ) return 1; 374 381 if( c==matchSet ){ 375 - while( *zString && patternCompare(&zPattern[1],zString,pInfo)==0 ){ 382 + assert( esc==0 ); /* This is GLOB, not LIKE */ 383 + while( *zString && patternCompare(&zPattern[1],zString,pInfo,esc)==0 ){ 376 384 sqliteNextChar(zString); 377 385 } 378 386 return *zString!=0; 379 387 }else{ 380 388 while( (c2 = *zString)!=0 ){ 381 389 if( noCase ){ 382 390 c2 = sqlite3UpperToLower[c2]; 383 391 c = sqlite3UpperToLower[c]; 384 392 while( c2 != 0 && c2 != c ){ c2 = sqlite3UpperToLower[*++zString]; } 385 393 }else{ 386 394 while( c2 != 0 && c2 != c ){ c2 = *++zString; } 387 395 } 388 396 if( c2==0 ) return 0; 389 - if( patternCompare(&zPattern[1],zString,pInfo) ) return 1; 397 + if( patternCompare(&zPattern[1],zString,pInfo,esc) ) return 1; 390 398 sqliteNextChar(zString); 391 399 } 392 400 return 0; 393 401 } 394 - }else if( c==matchOne ){ 402 + }else if( !prevEscape && c==matchOne ){ 395 403 if( *zString==0 ) return 0; 396 404 sqliteNextChar(zString); 397 405 zPattern++; 398 406 }else if( c==matchSet ){ 399 407 int prior_c = 0; 408 + assert( esc==0 ); /* This only occurs for GLOB, not LIKE */ 400 409 seen = 0; 401 410 invert = 0; 402 411 c = sqliteCharVal(zString); 403 412 if( c==0 ) return 0; 404 413 c2 = *++zPattern; 405 414 if( c2=='^' ){ invert = 1; c2 = *++zPattern; } 406 415 if( c2==']' ){ ................................................................................ 420 429 prior_c = c2; 421 430 } 422 431 sqliteNextChar(zPattern); 423 432 } 424 433 if( c2==0 || (seen ^ invert)==0 ) return 0; 425 434 sqliteNextChar(zString); 426 435 zPattern++; 436 + }else if( !prevEscape && sqlite3ReadUtf8(zPattern)==esc){ 437 + prevEscape = 1; 438 + sqliteNextChar(zPattern); 427 439 }else{ 428 440 if( noCase ){ 429 441 if( sqlite3UpperToLower[c] != sqlite3UpperToLower[*zString] ) return 0; 430 442 }else{ 431 443 if( c != *zString ) return 0; 432 444 } 433 445 zPattern++; 434 446 zString++; 447 + prevEscape = 0; 435 448 } 436 449 } 437 450 return *zString==0; 438 451 } 439 452 440 453 441 454 /* ................................................................................ 453 466 static void likeFunc( 454 467 sqlite3_context *context, 455 468 int argc, 456 469 sqlite3_value **argv 457 470 ){ 458 471 const unsigned char *zA = sqlite3_value_text(argv[0]); 459 472 const unsigned char *zB = sqlite3_value_text(argv[1]); 473 + int escape = 0; 474 + if( argc==3 ){ 475 + /* The escape character string must consist of a single UTF-8 character. 476 + ** Otherwise, return an error. 477 + */ 478 + const unsigned char *zEsc = sqlite3_value_text(argv[2]); 479 + if( sqlite3utf8CharLen(zEsc, -1)!=1 ){ 480 + sqlite3_result_error(context, 481 + "ESCAPE expression must be a single character", -1); 482 + return; 483 + } 484 + escape = sqlite3ReadUtf8(zEsc); 485 + } 460 486 if( zA && zB ){ 461 - sqlite3_result_int(context, patternCompare(zA, zB, &likeInfo)); 487 + sqlite3_result_int(context, patternCompare(zA, zB, &likeInfo, escape)); 462 488 } 463 489 } 464 490 465 491 /* 466 492 ** Implementation of the glob() SQL function. This function implements 467 493 ** the build-in GLOB operator. The first argument to the function is the 468 494 ** string and the second argument is the pattern. So, the SQL statements: 469 495 ** 470 496 ** A GLOB B 471 497 ** 472 -** is implemented as glob(A,B). 498 +** is implemented as glob(B,A). 473 499 */ 474 500 static void globFunc(sqlite3_context *context, int arg, sqlite3_value **argv){ 475 501 const unsigned char *zA = sqlite3_value_text(argv[0]); 476 502 const unsigned char *zB = sqlite3_value_text(argv[1]); 477 503 if( zA && zB ){ 478 - sqlite3_result_int(context, patternCompare(zA, zB, &globInfo)); 504 + sqlite3_result_int(context, patternCompare(zA, zB, &globInfo, 0)); 479 505 } 480 506 } 481 507 482 508 /* 483 509 ** Implementation of the NULLIF(x,y) function. The result is the first 484 510 ** argument if the arguments are different. The result is NULL if the 485 511 ** arguments are equal to each other. ................................................................................ 988 1014 { "lower", 1, 0, SQLITE_UTF8, 0, lowerFunc }, 989 1015 { "coalesce", -1, 0, SQLITE_UTF8, 0, ifnullFunc }, 990 1016 { "coalesce", 0, 0, SQLITE_UTF8, 0, 0 }, 991 1017 { "coalesce", 1, 0, SQLITE_UTF8, 0, 0 }, 992 1018 { "ifnull", 2, 0, SQLITE_UTF8, 1, ifnullFunc }, 993 1019 { "random", -1, 0, SQLITE_UTF8, 0, randomFunc }, 994 1020 { "like", 2, 0, SQLITE_UTF8, 0, likeFunc }, 1021 + { "like", 3, 0, SQLITE_UTF8, 0, likeFunc }, 995 1022 { "glob", 2, 0, SQLITE_UTF8, 0, globFunc }, 996 1023 { "nullif", 2, 0, SQLITE_UTF8, 1, nullifFunc }, 997 1024 { "sqlite_version", 0, 0, SQLITE_UTF8, 0, versionFunc}, 998 1025 { "quote", 1, 0, SQLITE_UTF8, 0, quoteFunc }, 999 1026 { "last_insert_rowid", 0, 1, SQLITE_UTF8, 0, last_insert_rowid }, 1000 1027 { "changes", 0, 1, SQLITE_UTF8, 0, changes }, 1001 1028 { "total_changes", 0, 1, SQLITE_UTF8, 0, total_changes },
Changes to src/parse.y.
10 10 ** 11 11 ************************************************************************* 12 12 ** This file contains SQLite's grammar for SQL. Process this file 13 13 ** using the lemon parser generator to generate C code that runs 14 14 ** the parser. Lemon will also generate a header file containing 15 15 ** numeric codes for all of the tokens. 16 16 ** 17 -** @(#) $Id: parse.y,v 1.156 2004/11/13 15:59:15 drh Exp $ 17 +** @(#) $Id: parse.y,v 1.157 2004/11/17 16:41:30 danielk1977 Exp $ 18 18 */ 19 19 %token_prefix TK_ 20 20 %token_type {Token} 21 21 %default_type {Token} 22 22 %extra_argument {Parse *pParse} 23 23 %syntax_error { 24 24 if( pParse->zErrMsg==0 ){ ................................................................................ 165 165 // constraint. 166 166 // 167 167 %left OR. 168 168 %left AND. 169 169 %right NOT. 170 170 %left IS LIKE GLOB BETWEEN IN ISNULL NOTNULL NE EQ. 171 171 %left GT LE LT GE. 172 +%right ESCAPE. 172 173 %left BITAND BITOR LSHIFT RSHIFT. 173 174 %left PLUS MINUS. 174 175 %left STAR SLASH REM. 175 176 %left CONCAT. 176 177 %right UMINUS UPLUS BITNOT. 177 178 178 179 // And "ids" is an identifer-or-string. ................................................................................ 633 634 expr(A) ::= expr(X) REM(OP) expr(Y). {A = sqlite3Expr(@OP, X, Y, 0);} 634 635 expr(A) ::= expr(X) CONCAT(OP) expr(Y). {A = sqlite3Expr(@OP, X, Y, 0);} 635 636 %type likeop {struct LikeOp} 636 637 likeop(A) ::= LIKE. {A.opcode = TK_LIKE; A.not = 0;} 637 638 likeop(A) ::= GLOB. {A.opcode = TK_GLOB; A.not = 0;} 638 639 likeop(A) ::= NOT LIKE. {A.opcode = TK_LIKE; A.not = 1;} 639 640 likeop(A) ::= NOT GLOB. {A.opcode = TK_GLOB; A.not = 1;} 640 -expr(A) ::= expr(X) likeop(OP) expr(Y). [LIKE] { 641 +%type escape {Expr*} 642 +escape(X) ::= ESCAPE expr(A). [ESCAPE] {X = A;} 643 +escape(X) ::= . [ESCAPE] {X = 0;} 644 +expr(A) ::= expr(X) likeop(OP) expr(Y) escape(E). [LIKE] { 641 645 ExprList *pList = sqlite3ExprListAppend(0, Y, 0); 642 646 pList = sqlite3ExprListAppend(pList, X, 0); 647 + if( E ){ 648 + pList = sqlite3ExprListAppend(pList, E, 0); 649 + } 643 650 A = sqlite3ExprFunction(pList, 0); 644 651 if( A ) A->op = OP.opcode; 645 652 if( OP.not ) A = sqlite3Expr(TK_NOT, A, 0, 0); 646 653 sqlite3ExprSpan(A, &X->span, &Y->span); 647 654 } 655 + 648 656 expr(A) ::= expr(X) ISNULL(E). { 649 657 A = sqlite3Expr(TK_ISNULL, X, 0, 0); 650 658 sqlite3ExprSpan(A,&X->span,&E); 651 659 } 652 660 expr(A) ::= expr(X) IS NULL(E). { 653 661 A = sqlite3Expr(TK_ISNULL, X, 0, 0); 654 662 sqlite3ExprSpan(A,&X->span,&E);
Changes to test/expr.test.
7 7 # May you find forgiveness for yourself and forgive others. 8 8 # May you share freely, never taking more than you give. 9 9 # 10 10 #*********************************************************************** 11 11 # This file implements regression tests for SQLite library. The 12 12 # focus of this file is testing expressions. 13 13 # 14 -# $Id: expr.test,v 1.39 2004/11/15 01:40:48 drh Exp $ 14 +# $Id: expr.test,v 1.40 2004/11/17 16:41:29 danielk1977 Exp $ 15 15 16 16 set testdir [file dirname $argv0] 17 17 source $testdir/tester.tcl 18 18 19 19 # Create a table to work with. 20 20 # 21 21 execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)} ................................................................................ 263 263 } 264 264 265 265 test_expr expr-5.54 {t1='abc', t2=NULL} {t1 LIKE t2} {{}} 266 266 test_expr expr-5.55 {t1='abc', t2=NULL} {t1 NOT LIKE t2} {{}} 267 267 test_expr expr-5.56 {t1='abc', t2=NULL} {t2 LIKE t1} {{}} 268 268 test_expr expr-5.57 {t1='abc', t2=NULL} {t2 NOT LIKE t1} {{}} 269 269 270 +# LIKE expressions that use ESCAPE characters. 271 +test_expr expr-5.58 {t1='abc', t2='A_C'} {t1 LIKE t2 ESCAPE '7'} 1 272 +test_expr expr-5.59 {t1='a_c', t2='A7_C'} {t1 LIKE t2 ESCAPE '7'} 1 273 +test_expr expr-5.60 {t1='abc', t2='A7_C'} {t1 LIKE t2 ESCAPE '7'} 0 274 +test_expr expr-5.61 {t1='a7Xc', t2='A7_C'} {t1 LIKE t2 ESCAPE '7'} 0 275 +test_expr expr-5.62 {t1='abcde', t2='A%E'} {t1 LIKE t2 ESCAPE '7'} 1 276 +test_expr expr-5.63 {t1='abcde', t2='A7%E'} {t1 LIKE t2 ESCAPE '7'} 0 277 +test_expr expr-5.64 {t1='a7cde', t2='A7%E'} {t1 LIKE t2 ESCAPE '7'} 0 278 +test_expr expr-5.65 {t1='a7cde', t2='A77%E'} {t1 LIKE t2 ESCAPE '7'} 1 279 +test_expr expr-5.66 {t1='abc7', t2='A%77'} {t1 LIKE t2 ESCAPE '7'} 1 280 +test_expr expr-5.67 {t1='abc_', t2='A%7_'} {t1 LIKE t2 ESCAPE '7'} 1 281 +test_expr expr-5.68 {t1='abc7', t2='A%7_'} {t1 LIKE t2 ESCAPE '7'} 0 282 + 283 +# These are the same test as the block above, but using a multi-byte 284 +# character as the escape character. 285 +if {"\u1234"!="u1234"} { 286 + test_expr expr-5.69 "t1='abc', t2='A_C'" \ 287 + "t1 LIKE t2 ESCAPE '\u1234'" 1 288 + test_expr expr-5.70 "t1='a_c', t2='A\u1234_C'" \ 289 + "t1 LIKE t2 ESCAPE '\u1234'" 1 290 + test_expr expr-5.71 "t1='abc', t2='A\u1234_C'" \ 291 + "t1 LIKE t2 ESCAPE '\u1234'" 0 292 + test_expr expr-5.72 "t1='a\u1234Xc', t2='A\u1234_C'" \ 293 + "t1 LIKE t2 ESCAPE '\u1234'" 0 294 + test_expr expr-5.73 "t1='abcde', t2='A%E'" \ 295 + "t1 LIKE t2 ESCAPE '\u1234'" 1 296 + test_expr expr-5.74 "t1='abcde', t2='A\u1234%E'" \ 297 + "t1 LIKE t2 ESCAPE '\u1234'" 0 298 + test_expr expr-5.75 "t1='a\u1234cde', t2='A\u1234%E'" \ 299 + "t1 LIKE t2 ESCAPE '\u1234'" 0 300 + test_expr expr-5.76 "t1='a\u1234cde', t2='A\u1234\u1234%E'" \ 301 + "t1 LIKE t2 ESCAPE '\u1234'" 1 302 + test_expr expr-5.77 "t1='abc\u1234', t2='A%\u1234\u1234'" \ 303 + "t1 LIKE t2 ESCAPE '\u1234'" 1 304 + test_expr expr-5.78 "t1='abc_', t2='A%\u1234_'" \ 305 + "t1 LIKE t2 ESCAPE '\u1234'" 1 306 + test_expr expr-5.79 "t1='abc\u1234', t2='A%\u1234_'" \ 307 + "t1 LIKE t2 ESCAPE '\u1234'" 0 308 +} 270 309 271 310 test_expr expr-6.1 {t1='abc', t2='xyz'} {t1 GLOB t2} 0 272 311 test_expr expr-6.2 {t1='abc', t2='ABC'} {t1 GLOB t2} 0 273 312 test_expr expr-6.3 {t1='abc', t2='A?C'} {t1 GLOB t2} 0 274 313 test_expr expr-6.4 {t1='abc', t2='a?c'} {t1 GLOB t2} 1 275 314 test_expr expr-6.5 {t1='abc', t2='abc?'} {t1 GLOB t2} 0 276 315 test_expr expr-6.6 {t1='abc', t2='A*C'} {t1 GLOB t2} 0
Added test/lock4.test.
1 +# 2001 September 15 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# This file implements regression tests for SQLite library. The focus 12 +# of this file is modifications made to tables while SELECT queries are 13 +# active on the tables. Using this capability in a program is tricky 14 +# because results can be difficult to predict, but can be useful. 15 +# 16 +# $Id: lock4.test,v 1.1 2004/11/17 16:41:29 danielk1977 Exp $ 17 +# 18 + 19 +set testdir [file dirname $argv0] 20 +source $testdir/tester.tcl 21 + 22 +do_test lock4-1.0 { 23 + execsql { 24 + CREATE TABLE t1(a, b); 25 + INSERT INTO t1 VALUES(1, 2); 26 + } 27 +} {} 28 + 29 +# Check that we can INSERT into a table while doing a SELECT on it. 30 +do_test lock4-1.1 { 31 + db eval {SELECT * FROM t1} { 32 + if {$a<5} { 33 + execsql "INSERT INTO t1 VALUES($a+1, ($a+1)*2)" 34 + } 35 + } 36 +} {} 37 +do_test lock4-1.2 { 38 + execsql { 39 + SELECT * FROM t1 40 + } 41 +} {1 2 2 4 3 6 4 8 5 10} 42 + 43 +# Check that we can UPDATE a table while doing a SELECT on it. 44 +do_test lock4-1.3 { 45 + db eval {SELECT * FROM t1 WHERE (a%2)=0} { 46 + execsql "UPDATE t1 SET b = b/2 WHERE a = $a" 47 + } 48 +} {} 49 +do_test lock4-1.4 { 50 + execsql { 51 + SELECT * FROM t1 52 + } 53 +} {1 2 2 2 3 6 4 4 5 10} 54 + 55 +# Check that we can DELETE from a table while doing a SELECT on it. 56 +do_test lock4-1.5 { 57 + db eval {SELECT * FROM t1 WHERE (a%2)=0} { 58 + execsql "DELETE FROM t1 WHERE a = $a" 59 + } 60 +} {} 61 +do_test lock4-1.6 { 62 + execsql { 63 + SELECT * FROM t1 64 + } 65 +} {1 2 3 6 5 10} 66 + 67 +# Check what happens when a row is deleted while a cursor is still using 68 +# the row (because of a SELECT that does a join). 69 +do_test lock4-2.0 { 70 + execsql { 71 + CREATE TABLE t2(c); 72 + INSERT INTO t2 VALUES('one'); 73 + INSERT INTO t2 VALUES('two'); 74 + } 75 +} {} 76 +do_test lock4-2.1 { 77 + set res [list] 78 + db eval {SELECT a, b, c FROM t1, t2} { 79 + lappend res $a $b $c 80 + if {0==[string compare $c one]} { 81 + execsql "DELETE FROM t1 WHERE a = $a" 82 + } 83 + } 84 + set res 85 +} {1 2 one 1 2 two 3 6 one 3 6 two 5 10 one 5 10 two} 86 +do_test lock4-2.2 { 87 + execsql { 88 + SELECT * FROM t1; 89 + } 90 +} {} 91 + 92 +# do_test lock4-2.3 { 93 +# execsql " 94 +# INSERT INTO t1 VALUES('[string repeat 1 750]', '[string repeat 2 750]') 95 +# " 96 +# } {} 97 +# do_test lock4-2.4 { 98 +# set res [list] 99 +# db eval {SELECT a, b, c FROM t1, t2} { 100 +# lappend res $a $b $c 101 +# if {0==[string compare $c one]} { 102 +# execsql "DELETE FROM t1 WHERE a = '$a'" 103 +# } 104 +# } 105 +# set res 106 +# } [list \ 107 +# [string repeat 1 750] [string repeat 2 750] one \ 108 +# [string repeat 1 750] [string repeat 2 750] two 109 +# ] 110 +# do_test lock4-2.5 { 111 +# execsql { 112 +# SELECT * FROM t1; 113 +# } 114 +# } {} 115 + 116 +finish_test 117 +
Changes to tool/mkkeywordhash.c.
129 129 { "DESC", "TK_DESC", ALWAYS }, 130 130 { "DETACH", "TK_DETACH", ATTACH }, 131 131 { "DISTINCT", "TK_DISTINCT", ALWAYS }, 132 132 { "DROP", "TK_DROP", ALWAYS }, 133 133 { "END", "TK_END", ALWAYS }, 134 134 { "EACH", "TK_EACH", TRIGGER }, 135 135 { "ELSE", "TK_ELSE", ALWAYS }, 136 + { "ESCAPE", "TK_ESCAPE", ALWAYS }, 136 137 { "EXCEPT", "TK_EXCEPT", COMPOUND }, 137 138 { "EXCLUSIVE", "TK_EXCLUSIVE", ALWAYS }, 138 139 { "EXPLAIN", "TK_EXPLAIN", EXPLAIN }, 139 140 { "FAIL", "TK_FAIL", CONFLICT|TRIGGER }, 140 141 { "FOR", "TK_FOR", TRIGGER }, 141 142 { "FOREIGN", "TK_FOREIGN", FKEY }, 142 143 { "FROM", "TK_FROM", ALWAYS },