Index: src/func.c ================================================================== --- src/func.c +++ src/func.c @@ -14,11 +14,11 @@ ** ** 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.87 2004/11/14 21:56:30 drh Exp $ +** $Id: func.c,v 1.88 2004/11/17 16:41:30 danielk1977 Exp $ */ #include #include #include #include @@ -345,36 +345,44 @@ ** ** Hints: to match '*' or '?', put them in "[]". Like this: ** ** abc[*]xyz Matches "abc*xyz" only */ -int patternCompare( +static int patternCompare( const u8 *zPattern, /* The glob pattern */ const u8 *zString, /* The string to compare against the glob */ - const struct compareInfo *pInfo /* Information about how to do the compare */ + const struct compareInfo *pInfo, /* Information about how to do the compare */ + const int esc /* The escape character */ ){ register int c; int invert; int seen; int c2; u8 matchOne = pInfo->matchOne; u8 matchAll = pInfo->matchAll; u8 matchSet = pInfo->matchSet; u8 noCase = pInfo->noCase; + int prevEscape = 0; /* True if the previous character was 'escape' */ while( (c = *zPattern)!=0 ){ - if( c==matchAll ){ + if( !prevEscape && c==matchAll ){ while( (c=zPattern[1]) == matchAll || c == matchOne ){ if( c==matchOne ){ if( *zString==0 ) return 0; sqliteNextChar(zString); } zPattern++; + } + if( c && sqlite3ReadUtf8(&zPattern[1])==esc ){ + u8 const *zTemp = &zPattern[1]; + sqliteNextChar(zTemp); + c = *zTemp; } if( c==0 ) return 1; if( c==matchSet ){ - while( *zString && patternCompare(&zPattern[1],zString,pInfo)==0 ){ + assert( esc==0 ); /* This is GLOB, not LIKE */ + while( *zString && patternCompare(&zPattern[1],zString,pInfo,esc)==0 ){ sqliteNextChar(zString); } return *zString!=0; }else{ while( (c2 = *zString)!=0 ){ @@ -384,21 +392,22 @@ while( c2 != 0 && c2 != c ){ c2 = sqlite3UpperToLower[*++zString]; } }else{ while( c2 != 0 && c2 != c ){ c2 = *++zString; } } if( c2==0 ) return 0; - if( patternCompare(&zPattern[1],zString,pInfo) ) return 1; + if( patternCompare(&zPattern[1],zString,pInfo,esc) ) return 1; sqliteNextChar(zString); } return 0; } - }else if( c==matchOne ){ + }else if( !prevEscape && c==matchOne ){ if( *zString==0 ) return 0; sqliteNextChar(zString); zPattern++; }else if( c==matchSet ){ int prior_c = 0; + assert( esc==0 ); /* This only occurs for GLOB, not LIKE */ seen = 0; invert = 0; c = sqliteCharVal(zString); if( c==0 ) return 0; c2 = *++zPattern; @@ -422,18 +431,22 @@ sqliteNextChar(zPattern); } if( c2==0 || (seen ^ invert)==0 ) return 0; sqliteNextChar(zString); zPattern++; + }else if( !prevEscape && sqlite3ReadUtf8(zPattern)==esc){ + prevEscape = 1; + sqliteNextChar(zPattern); }else{ if( noCase ){ if( sqlite3UpperToLower[c] != sqlite3UpperToLower[*zString] ) return 0; }else{ if( c != *zString ) return 0; } zPattern++; zString++; + prevEscape = 0; } } return *zString==0; } @@ -455,12 +468,25 @@ int argc, sqlite3_value **argv ){ const unsigned char *zA = sqlite3_value_text(argv[0]); const unsigned char *zB = sqlite3_value_text(argv[1]); + int escape = 0; + if( argc==3 ){ + /* The escape character string must consist of a single UTF-8 character. + ** Otherwise, return an error. + */ + const unsigned char *zEsc = sqlite3_value_text(argv[2]); + if( sqlite3utf8CharLen(zEsc, -1)!=1 ){ + sqlite3_result_error(context, + "ESCAPE expression must be a single character", -1); + return; + } + escape = sqlite3ReadUtf8(zEsc); + } if( zA && zB ){ - sqlite3_result_int(context, patternCompare(zA, zB, &likeInfo)); + sqlite3_result_int(context, patternCompare(zA, zB, &likeInfo, escape)); } } /* ** Implementation of the glob() SQL function. This function implements @@ -467,17 +493,17 @@ ** the build-in GLOB operator. The first argument to the function is the ** string and the second argument is the pattern. So, the SQL statements: ** ** A GLOB B ** -** is implemented as glob(A,B). +** is implemented as glob(B,A). */ static void globFunc(sqlite3_context *context, int arg, sqlite3_value **argv){ const unsigned char *zA = sqlite3_value_text(argv[0]); const unsigned char *zB = sqlite3_value_text(argv[1]); if( zA && zB ){ - sqlite3_result_int(context, patternCompare(zA, zB, &globInfo)); + sqlite3_result_int(context, patternCompare(zA, zB, &globInfo, 0)); } } /* ** Implementation of the NULLIF(x,y) function. The result is the first @@ -990,10 +1016,11 @@ { "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 }, { "like", 2, 0, SQLITE_UTF8, 0, likeFunc }, + { "like", 3, 0, SQLITE_UTF8, 0, likeFunc }, { "glob", 2, 0, SQLITE_UTF8, 0, globFunc }, { "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 }, Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -12,11 +12,11 @@ ** This file contains SQLite's grammar for SQL. Process this file ** using the lemon parser generator to generate C code that runs ** the parser. Lemon will also generate a header file containing ** numeric codes for all of the tokens. ** -** @(#) $Id: parse.y,v 1.156 2004/11/13 15:59:15 drh Exp $ +** @(#) $Id: parse.y,v 1.157 2004/11/17 16:41:30 danielk1977 Exp $ */ %token_prefix TK_ %token_type {Token} %default_type {Token} %extra_argument {Parse *pParse} @@ -167,10 +167,11 @@ %left OR. %left AND. %right NOT. %left IS LIKE GLOB BETWEEN IN ISNULL NOTNULL NE EQ. %left GT LE LT GE. +%right ESCAPE. %left BITAND BITOR LSHIFT RSHIFT. %left PLUS MINUS. %left STAR SLASH REM. %left CONCAT. %right UMINUS UPLUS BITNOT. @@ -635,18 +636,25 @@ %type likeop {struct LikeOp} likeop(A) ::= LIKE. {A.opcode = TK_LIKE; A.not = 0;} likeop(A) ::= GLOB. {A.opcode = TK_GLOB; A.not = 0;} likeop(A) ::= NOT LIKE. {A.opcode = TK_LIKE; A.not = 1;} likeop(A) ::= NOT GLOB. {A.opcode = TK_GLOB; A.not = 1;} -expr(A) ::= expr(X) likeop(OP) expr(Y). [LIKE] { +%type escape {Expr*} +escape(X) ::= ESCAPE expr(A). [ESCAPE] {X = A;} +escape(X) ::= . [ESCAPE] {X = 0;} +expr(A) ::= expr(X) likeop(OP) expr(Y) escape(E). [LIKE] { ExprList *pList = sqlite3ExprListAppend(0, Y, 0); pList = sqlite3ExprListAppend(pList, X, 0); + if( E ){ + pList = sqlite3ExprListAppend(pList, E, 0); + } A = sqlite3ExprFunction(pList, 0); if( A ) A->op = OP.opcode; if( OP.not ) A = sqlite3Expr(TK_NOT, A, 0, 0); sqlite3ExprSpan(A, &X->span, &Y->span); } + expr(A) ::= expr(X) ISNULL(E). { A = sqlite3Expr(TK_ISNULL, X, 0, 0); sqlite3ExprSpan(A,&X->span,&E); } expr(A) ::= expr(X) IS NULL(E). { Index: test/expr.test ================================================================== --- test/expr.test +++ test/expr.test @@ -9,11 +9,11 @@ # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing expressions. # -# $Id: expr.test,v 1.39 2004/11/15 01:40:48 drh Exp $ +# $Id: expr.test,v 1.40 2004/11/17 16:41:29 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table to work with. @@ -265,10 +265,49 @@ test_expr expr-5.54 {t1='abc', t2=NULL} {t1 LIKE t2} {{}} test_expr expr-5.55 {t1='abc', t2=NULL} {t1 NOT LIKE t2} {{}} test_expr expr-5.56 {t1='abc', t2=NULL} {t2 LIKE t1} {{}} test_expr expr-5.57 {t1='abc', t2=NULL} {t2 NOT LIKE t1} {{}} +# LIKE expressions that use ESCAPE characters. +test_expr expr-5.58 {t1='abc', t2='A_C'} {t1 LIKE t2 ESCAPE '7'} 1 +test_expr expr-5.59 {t1='a_c', t2='A7_C'} {t1 LIKE t2 ESCAPE '7'} 1 +test_expr expr-5.60 {t1='abc', t2='A7_C'} {t1 LIKE t2 ESCAPE '7'} 0 +test_expr expr-5.61 {t1='a7Xc', t2='A7_C'} {t1 LIKE t2 ESCAPE '7'} 0 +test_expr expr-5.62 {t1='abcde', t2='A%E'} {t1 LIKE t2 ESCAPE '7'} 1 +test_expr expr-5.63 {t1='abcde', t2='A7%E'} {t1 LIKE t2 ESCAPE '7'} 0 +test_expr expr-5.64 {t1='a7cde', t2='A7%E'} {t1 LIKE t2 ESCAPE '7'} 0 +test_expr expr-5.65 {t1='a7cde', t2='A77%E'} {t1 LIKE t2 ESCAPE '7'} 1 +test_expr expr-5.66 {t1='abc7', t2='A%77'} {t1 LIKE t2 ESCAPE '7'} 1 +test_expr expr-5.67 {t1='abc_', t2='A%7_'} {t1 LIKE t2 ESCAPE '7'} 1 +test_expr expr-5.68 {t1='abc7', t2='A%7_'} {t1 LIKE t2 ESCAPE '7'} 0 + +# These are the same test as the block above, but using a multi-byte +# character as the escape character. +if {"\u1234"!="u1234"} { + test_expr expr-5.69 "t1='abc', t2='A_C'" \ + "t1 LIKE t2 ESCAPE '\u1234'" 1 + test_expr expr-5.70 "t1='a_c', t2='A\u1234_C'" \ + "t1 LIKE t2 ESCAPE '\u1234'" 1 + test_expr expr-5.71 "t1='abc', t2='A\u1234_C'" \ + "t1 LIKE t2 ESCAPE '\u1234'" 0 + test_expr expr-5.72 "t1='a\u1234Xc', t2='A\u1234_C'" \ + "t1 LIKE t2 ESCAPE '\u1234'" 0 + test_expr expr-5.73 "t1='abcde', t2='A%E'" \ + "t1 LIKE t2 ESCAPE '\u1234'" 1 + test_expr expr-5.74 "t1='abcde', t2='A\u1234%E'" \ + "t1 LIKE t2 ESCAPE '\u1234'" 0 + test_expr expr-5.75 "t1='a\u1234cde', t2='A\u1234%E'" \ + "t1 LIKE t2 ESCAPE '\u1234'" 0 + test_expr expr-5.76 "t1='a\u1234cde', t2='A\u1234\u1234%E'" \ + "t1 LIKE t2 ESCAPE '\u1234'" 1 + test_expr expr-5.77 "t1='abc\u1234', t2='A%\u1234\u1234'" \ + "t1 LIKE t2 ESCAPE '\u1234'" 1 + test_expr expr-5.78 "t1='abc_', t2='A%\u1234_'" \ + "t1 LIKE t2 ESCAPE '\u1234'" 1 + test_expr expr-5.79 "t1='abc\u1234', t2='A%\u1234_'" \ + "t1 LIKE t2 ESCAPE '\u1234'" 0 +} test_expr expr-6.1 {t1='abc', t2='xyz'} {t1 GLOB t2} 0 test_expr expr-6.2 {t1='abc', t2='ABC'} {t1 GLOB t2} 0 test_expr expr-6.3 {t1='abc', t2='A?C'} {t1 GLOB t2} 0 test_expr expr-6.4 {t1='abc', t2='a?c'} {t1 GLOB t2} 1 ADDED test/lock4.test Index: test/lock4.test ================================================================== --- test/lock4.test +++ test/lock4.test @@ -0,0 +1,117 @@ +# 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. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. The focus +# of this file is modifications made to tables while SELECT queries are +# active on the tables. Using this capability in a program is tricky +# because results can be difficult to predict, but can be useful. +# +# $Id: lock4.test,v 1.1 2004/11/17 16:41:29 danielk1977 Exp $ +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_test lock4-1.0 { + execsql { + CREATE TABLE t1(a, b); + INSERT INTO t1 VALUES(1, 2); + } +} {} + +# Check that we can INSERT into a table while doing a SELECT on it. +do_test lock4-1.1 { + db eval {SELECT * FROM t1} { + if {$a<5} { + execsql "INSERT INTO t1 VALUES($a+1, ($a+1)*2)" + } + } +} {} +do_test lock4-1.2 { + execsql { + SELECT * FROM t1 + } +} {1 2 2 4 3 6 4 8 5 10} + +# Check that we can UPDATE a table while doing a SELECT on it. +do_test lock4-1.3 { + db eval {SELECT * FROM t1 WHERE (a%2)=0} { + execsql "UPDATE t1 SET b = b/2 WHERE a = $a" + } +} {} +do_test lock4-1.4 { + execsql { + SELECT * FROM t1 + } +} {1 2 2 2 3 6 4 4 5 10} + +# Check that we can DELETE from a table while doing a SELECT on it. +do_test lock4-1.5 { + db eval {SELECT * FROM t1 WHERE (a%2)=0} { + execsql "DELETE FROM t1 WHERE a = $a" + } +} {} +do_test lock4-1.6 { + execsql { + SELECT * FROM t1 + } +} {1 2 3 6 5 10} + +# Check what happens when a row is deleted while a cursor is still using +# the row (because of a SELECT that does a join). +do_test lock4-2.0 { + execsql { + CREATE TABLE t2(c); + INSERT INTO t2 VALUES('one'); + INSERT INTO t2 VALUES('two'); + } +} {} +do_test lock4-2.1 { + set res [list] + db eval {SELECT a, b, c FROM t1, t2} { + lappend res $a $b $c + if {0==[string compare $c one]} { + execsql "DELETE FROM t1 WHERE a = $a" + } + } + set res +} {1 2 one 1 2 two 3 6 one 3 6 two 5 10 one 5 10 two} +do_test lock4-2.2 { + execsql { + SELECT * FROM t1; + } +} {} + +# do_test lock4-2.3 { +# execsql " +# INSERT INTO t1 VALUES('[string repeat 1 750]', '[string repeat 2 750]') +# " +# } {} +# do_test lock4-2.4 { +# set res [list] +# db eval {SELECT a, b, c FROM t1, t2} { +# lappend res $a $b $c +# if {0==[string compare $c one]} { +# execsql "DELETE FROM t1 WHERE a = '$a'" +# } +# } +# set res +# } [list \ +# [string repeat 1 750] [string repeat 2 750] one \ +# [string repeat 1 750] [string repeat 2 750] two +# ] +# do_test lock4-2.5 { +# execsql { +# SELECT * FROM t1; +# } +# } {} + +finish_test + Index: tool/mkkeywordhash.c ================================================================== --- tool/mkkeywordhash.c +++ tool/mkkeywordhash.c @@ -131,10 +131,11 @@ { "DISTINCT", "TK_DISTINCT", ALWAYS }, { "DROP", "TK_DROP", ALWAYS }, { "END", "TK_END", ALWAYS }, { "EACH", "TK_EACH", TRIGGER }, { "ELSE", "TK_ELSE", ALWAYS }, + { "ESCAPE", "TK_ESCAPE", ALWAYS }, { "EXCEPT", "TK_EXCEPT", COMPOUND }, { "EXCLUSIVE", "TK_EXCLUSIVE", ALWAYS }, { "EXPLAIN", "TK_EXPLAIN", EXPLAIN }, { "FAIL", "TK_FAIL", CONFLICT|TRIGGER }, { "FOR", "TK_FOR", TRIGGER },