1 /* 2 ** 2002 February 23 3 ** 4 ** The author disclaims copyright to this source code. In place of 5 ** a legal notice, here is a blessing: 6 ** 7 ** May you do good and not evil. 8 ** May you find forgiveness for yourself and forgive others. 9 ** May you share freely, never taking more than you give. 10 ** 11 ************************************************************************* 12 ** This file contains the C functions that implement various SQL 13 ** functions of SQLite. 14 ** 15 ** There is only one exported symbol in this file - the function 16 ** sqliteRegisterBuildinFunctions() found at the bottom of the file. 17 ** All other code has file scope. 18 */ 19 #include "sqliteInt.h" 20 #include <stdlib.h> 21 #include <assert.h> 22 #include "vdbeInt.h" 23 24 /* 25 ** Return the collating function associated with a function. 26 */ 27 static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){ 28 return context->pColl; 29 } 30 31 /* 32 ** Indicate that the accumulator load should be skipped on this 33 ** iteration of the aggregate loop. 34 */ 35 static void sqlite3SkipAccumulatorLoad(sqlite3_context *context){ 36 context->skipFlag = 1; 37 } 38 39 /* 40 ** Implementation of the non-aggregate min() and max() functions 41 */ 42 static void minmaxFunc( 43 sqlite3_context *context, 44 int argc, 45 sqlite3_value **argv 46 ){ 47 int i; 48 int mask; /* 0 for min() or 0xffffffff for max() */ 49 int iBest; 50 CollSeq *pColl; 51 52 assert( argc>1 ); 53 mask = sqlite3_user_data(context)==0 ? 0 : -1; 54 pColl = sqlite3GetFuncCollSeq(context); 55 assert( pColl ); 56 assert( mask==-1 || mask==0 ); 57 iBest = 0; 58 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return; 59 for(i=1; i<argc; i++){ 60 if( sqlite3_value_type(argv[i])==SQLITE_NULL ) return; 61 if( (sqlite3MemCompare(argv[iBest], argv[i], pColl)^mask)>=0 ){ 62 testcase( mask==0 ); 63 iBest = i; 64 } 65 } 66 sqlite3_result_value(context, argv[iBest]); 67 } 68 69 /* 70 ** Return the type of the argument. 71 */ 72 static void typeofFunc( 73 sqlite3_context *context, 74 int NotUsed, 75 sqlite3_value **argv 76 ){ 77 const char *z = 0; 78 UNUSED_PARAMETER(NotUsed); 79 switch( sqlite3_value_type(argv[0]) ){ 80 case SQLITE_INTEGER: z = "integer"; break; 81 case SQLITE_TEXT: z = "text"; break; 82 case SQLITE_FLOAT: z = "real"; break; 83 case SQLITE_BLOB: z = "blob"; break; 84 default: z = "null"; break; 85 } 86 sqlite3_result_text(context, z, -1, SQLITE_STATIC); 87 } 88 89 90 /* 91 ** Implementation of the length() function 92 */ 93 static void lengthFunc( 94 sqlite3_context *context, 95 int argc, 96 sqlite3_value **argv 97 ){ 98 int len; 99 100 assert( argc==1 ); 101 UNUSED_PARAMETER(argc); 102 switch( sqlite3_value_type(argv[0]) ){ 103 case SQLITE_BLOB: 104 case SQLITE_INTEGER: 105 case SQLITE_FLOAT: { 106 sqlite3_result_int(context, sqlite3_value_bytes(argv[0])); 107 break; 108 } 109 case SQLITE_TEXT: { 110 const unsigned char *z = sqlite3_value_text(argv[0]); 111 if( z==0 ) return; 112 len = 0; 113 while( *z ){ 114 len++; 115 SQLITE_SKIP_UTF8(z); 116 } 117 sqlite3_result_int(context, len); 118 break; 119 } 120 default: { 121 sqlite3_result_null(context); 122 break; 123 } 124 } 125 } 126 127 /* 128 ** Implementation of the abs() function. 129 ** 130 ** IMP: R-23979-26855 The abs(X) function returns the absolute value of 131 ** the numeric argument X. 132 */ 133 static void absFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ 134 assert( argc==1 ); 135 UNUSED_PARAMETER(argc); 136 switch( sqlite3_value_type(argv[0]) ){ 137 case SQLITE_INTEGER: { 138 i64 iVal = sqlite3_value_int64(argv[0]); 139 if( iVal<0 ){ 140 if( (iVal<<1)==0 ){ 141 /* IMP: R-35460-15084 If X is the integer -9223372036854775807 then 142 ** abs(X) throws an integer overflow error since there is no 143 ** equivalent positive 64-bit two complement value. */ 144 sqlite3_result_error(context, "integer overflow", -1); 145 return; 146 } 147 iVal = -iVal; 148 } 149 sqlite3_result_int64(context, iVal); 150 break; 151 } 152 case SQLITE_NULL: { 153 /* IMP: R-37434-19929 Abs(X) returns NULL if X is NULL. */ 154 sqlite3_result_null(context); 155 break; 156 } 157 default: { 158 /* Because sqlite3_value_double() returns 0.0 if the argument is not 159 ** something that can be converted into a number, we have: 160 ** IMP: R-57326-31541 Abs(X) return 0.0 if X is a string or blob that 161 ** cannot be converted to a numeric value. 162 */ 163 double rVal = sqlite3_value_double(argv[0]); 164 if( rVal<0 ) rVal = -rVal; 165 sqlite3_result_double(context, rVal); 166 break; 167 } 168 } 169 } 170 171 /* 172 ** Implementation of the instr() function. 173 ** 174 ** instr(haystack,needle) finds the first occurrence of needle 175 ** in haystack and returns the number of previous characters plus 1, 176 ** or 0 if needle does not occur within haystack. 177 ** 178 ** If both haystack and needle are BLOBs, then the result is one more than 179 ** the number of bytes in haystack prior to the first occurrence of needle, 180 ** or 0 if needle never occurs in haystack. 181 */ 182 static void instrFunc( 183 sqlite3_context *context, 184 int argc, 185 sqlite3_value **argv 186 ){ 187 const unsigned char *zHaystack; 188 const unsigned char *zNeedle; 189 int nHaystack; 190 int nNeedle; 191 int typeHaystack, typeNeedle; 192 int N = 1; 193 int isText; 194 195 typeHaystack = sqlite3_value_type(argv[0]); 196 typeNeedle = sqlite3_value_type(argv[1]); 197 if( typeHaystack==SQLITE_NULL || typeNeedle==SQLITE_NULL ) return; 198 nHaystack = sqlite3_value_bytes(argv[0]); 199 nNeedle = sqlite3_value_bytes(argv[1]); 200 if( typeHaystack==SQLITE_BLOB && typeNeedle==SQLITE_BLOB ){ 201 zHaystack = sqlite3_value_blob(argv[0]); 202 zNeedle = sqlite3_value_blob(argv[1]); 203 isText = 0; 204 }else{ 205 zHaystack = sqlite3_value_text(argv[0]); 206 zNeedle = sqlite3_value_text(argv[1]); 207 isText = 1; 208 } 209 while( nNeedle<=nHaystack && memcmp(zHaystack, zNeedle, nNeedle)!=0 ){ 210 N++; 211 do{ 212 nHaystack--; 213 zHaystack++; 214 }while( isText && (zHaystack[0]&0xc0)==0x80 ); 215 } 216 if( nNeedle>nHaystack ) N = 0; 217 sqlite3_result_int(context, N); 218 } 219 220 /* 221 ** Implementation of the substr() function. 222 ** 223 ** substr(x,p1,p2) returns p2 characters of x[] beginning with p1. 224 ** p1 is 1-indexed. So substr(x,1,1) returns the first character 225 ** of x. If x is text, then we actually count UTF-8 characters. 226 ** If x is a blob, then we count bytes. 227 ** 228 ** If p1 is negative, then we begin abs(p1) from the end of x[]. 229 ** 230 ** If p2 is negative, return the p2 characters preceeding p1. 231 */ 232 static void substrFunc( 233 sqlite3_context *context, 234 int argc, 235 sqlite3_value **argv 236 ){ 237 const unsigned char *z; 238 const unsigned char *z2; 239 int len; 240 int p0type; 241 i64 p1, p2; 242 int negP2 = 0; 243 244 assert( argc==3 || argc==2 ); 245 if( sqlite3_value_type(argv[1])==SQLITE_NULL 246 || (argc==3 && sqlite3_value_type(argv[2])==SQLITE_NULL) 247 ){ 248 return; 249 } 250 p0type = sqlite3_value_type(argv[0]); 251 p1 = sqlite3_value_int(argv[1]); 252 if( p0type==SQLITE_BLOB ){ 253 len = sqlite3_value_bytes(argv[0]); 254 z = sqlite3_value_blob(argv[0]); 255 if( z==0 ) return; 256 assert( len==sqlite3_value_bytes(argv[0]) ); 257 }else{ 258 z = sqlite3_value_text(argv[0]); 259 if( z==0 ) return; 260 len = 0; 261 if( p1<0 ){ 262 for(z2=z; *z2; len++){ 263 SQLITE_SKIP_UTF8(z2); 264 } 265 } 266 } 267 if( argc==3 ){ 268 p2 = sqlite3_value_int(argv[2]); 269 if( p2<0 ){ 270 p2 = -p2; 271 negP2 = 1; 272 } 273 }else{ 274 p2 = sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_LENGTH]; 275 } 276 if( p1<0 ){ 277 p1 += len; 278 if( p1<0 ){ 279 p2 += p1; 280 if( p2<0 ) p2 = 0; 281 p1 = 0; 282 } 283 }else if( p1>0 ){ 284 p1--; 285 }else if( p2>0 ){ 286 p2--; 287 } 288 if( negP2 ){ 289 p1 -= p2; 290 if( p1<0 ){ 291 p2 += p1; 292 p1 = 0; 293 } 294 } 295 assert( p1>=0 && p2>=0 ); 296 if( p0type!=SQLITE_BLOB ){ 297 while( *z && p1 ){ 298 SQLITE_SKIP_UTF8(z); 299 p1--; 300 } 301 for(z2=z; *z2 && p2; p2--){ 302 SQLITE_SKIP_UTF8(z2); 303 } 304 sqlite3_result_text(context, (char*)z, (int)(z2-z), SQLITE_TRANSIENT); 305 }else{ 306 if( p1+p2>len ){ 307 p2 = len-p1; 308 if( p2<0 ) p2 = 0; 309 } 310 sqlite3_result_blob(context, (char*)&z[p1], (int)p2, SQLITE_TRANSIENT); 311 } 312 } 313 314 /* 315 ** Implementation of the round() function 316 */ 317 #ifndef SQLITE_OMIT_FLOATING_POINT 318 static void roundFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ 319 int n = 0; 320 double r; 321 char *zBuf; 322 assert( argc==1 || argc==2 ); 323 if( argc==2 ){ 324 if( SQLITE_NULL==sqlite3_value_type(argv[1]) ) return; 325 n = sqlite3_value_int(argv[1]); 326 if( n>30 ) n = 30; 327 if( n<0 ) n = 0; 328 } 329 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return; 330 r = sqlite3_value_double(argv[0]); 331 /* If Y==0 and X will fit in a 64-bit int, 332 ** handle the rounding directly, 333 ** otherwise use printf. 334 */ 335 if( n==0 && r>=0 && r<LARGEST_INT64-1 ){ 336 r = (double)((sqlite_int64)(r+0.5)); 337 }else if( n==0 && r<0 && (-r)<LARGEST_INT64-1 ){ 338 r = -(double)((sqlite_int64)((-r)+0.5)); 339 }else{ 340 zBuf = sqlite3_mprintf("%.*f",n,r); 341 if( zBuf==0 ){ 342 sqlite3_result_error_nomem(context); 343 return; 344 } 345 sqlite3AtoF(zBuf, &r, sqlite3Strlen30(zBuf), SQLITE_UTF8); 346 sqlite3_free(zBuf); 347 } 348 sqlite3_result_double(context, r); 349 } 350 #endif 351 352 /* 353 ** Allocate nByte bytes of space using sqlite3_malloc(). If the 354 ** allocation fails, call sqlite3_result_error_nomem() to notify 355 ** the database handle that malloc() has failed and return NULL. 356 ** If nByte is larger than the maximum string or blob length, then 357 ** raise an SQLITE_TOOBIG exception and return NULL. 358 */ 359 static void *contextMalloc(sqlite3_context *context, i64 nByte){ 360 char *z; 361 sqlite3 *db = sqlite3_context_db_handle(context); 362 assert( nByte>0 ); 363 testcase( nByte==db->aLimit[SQLITE_LIMIT_LENGTH] ); 364 testcase( nByte==db->aLimit[SQLITE_LIMIT_LENGTH]+1 ); 365 if( nByte>db->aLimit[SQLITE_LIMIT_LENGTH] ){ 366 sqlite3_result_error_toobig(context); 367 z = 0; 368 }else{ 369 z = sqlite3Malloc((int)nByte); 370 if( !z ){ 371 sqlite3_result_error_nomem(context); 372 } 373 } 374 return z; 375 } 376 377 /* 378 ** Implementation of the upper() and lower() SQL functions. 379 */ 380 static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ 381 char *z1; 382 const char *z2; 383 int i, n; 384 UNUSED_PARAMETER(argc); 385 z2 = (char*)sqlite3_value_text(argv[0]); 386 n = sqlite3_value_bytes(argv[0]); 387 /* Verify that the call to _bytes() does not invalidate the _text() pointer */ 388 assert( z2==(char*)sqlite3_value_text(argv[0]) ); 389 if( z2 ){ 390 z1 = contextMalloc(context, ((i64)n)+1); 391 if( z1 ){ 392 for(i=0; i<n; i++){ 393 z1[i] = (char)sqlite3Toupper(z2[i]); 394 } 395 sqlite3_result_text(context, z1, n, sqlite3_free); 396 } 397 } 398 } 399 static void lowerFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ 400 char *z1; 401 const char *z2; 402 int i, n; 403 UNUSED_PARAMETER(argc); 404 z2 = (char*)sqlite3_value_text(argv[0]); 405 n = sqlite3_value_bytes(argv[0]); 406 /* Verify that the call to _bytes() does not invalidate the _text() pointer */ 407 assert( z2==(char*)sqlite3_value_text(argv[0]) ); 408 if( z2 ){ 409 z1 = contextMalloc(context, ((i64)n)+1); 410 if( z1 ){ 411 for(i=0; i<n; i++){ 412 z1[i] = sqlite3Tolower(z2[i]); 413 } 414 sqlite3_result_text(context, z1, n, sqlite3_free); 415 } 416 } 417 } 418 419 /* 420 ** The COALESCE() and IFNULL() functions are implemented as VDBE code so 421 ** that unused argument values do not have to be computed. However, we 422 ** still need some kind of function implementation for this routines in 423 ** the function table. That function implementation will never be called 424 ** so it doesn't matter what the implementation is. We might as well use 425 ** the "version()" function as a substitute. 426 */ 427 #define ifnullFunc versionFunc /* Substitute function - never called */ 428 429 /* 430 ** Implementation of random(). Return a random integer. 431 */ 432 static void randomFunc( 433 sqlite3_context *context, 434 int NotUsed, 435 sqlite3_value **NotUsed2 436 ){ 437 sqlite_int64 r; 438 UNUSED_PARAMETER2(NotUsed, NotUsed2); 439 sqlite3_randomness(sizeof(r), &r); 440 if( r<0 ){ 441 /* We need to prevent a random number of 0x8000000000000000 442 ** (or -9223372036854775808) since when you do abs() of that 443 ** number of you get the same value back again. To do this 444 ** in a way that is testable, mask the sign bit off of negative 445 ** values, resulting in a positive value. Then take the 446 ** 2s complement of that positive value. The end result can 447 ** therefore be no less than -9223372036854775807. 448 */ 449 r = -(r & LARGEST_INT64); 450 } 451 sqlite3_result_int64(context, r); 452 } 453 454 /* 455 ** Implementation of randomblob(N). Return a random blob 456 ** that is N bytes long. 457 */ 458 static void randomBlob( 459 sqlite3_context *context, 460 int argc, 461 sqlite3_value **argv 462 ){ 463 int n; 464 unsigned char *p; 465 assert( argc==1 ); 466 UNUSED_PARAMETER(argc); 467 n = sqlite3_value_int(argv[0]); 468 if( n<1 ){ 469 n = 1; 470 } 471 p = contextMalloc(context, n); 472 if( p ){ 473 sqlite3_randomness(n, p); 474 sqlite3_result_blob(context, (char*)p, n, sqlite3_free); 475 } 476 } 477 478 /* 479 ** Implementation of the last_insert_rowid() SQL function. The return 480 ** value is the same as the sqlite3_last_insert_rowid() API function. 481 */ 482 static void last_insert_rowid( 483 sqlite3_context *context, 484 int NotUsed, 485 sqlite3_value **NotUsed2 486 ){ 487 sqlite3 *db = sqlite3_context_db_handle(context); 488 UNUSED_PARAMETER2(NotUsed, NotUsed2); 489 /* IMP: R-51513-12026 The last_insert_rowid() SQL function is a 490 ** wrapper around the sqlite3_last_insert_rowid() C/C++ interface 491 ** function. */ 492 sqlite3_result_int64(context, sqlite3_last_insert_rowid(db)); 493 } 494 495 /* 496 ** Implementation of the changes() SQL function. 497 ** 498 ** IMP: R-62073-11209 The changes() SQL function is a wrapper 499 ** around the sqlite3_changes() C/C++ function and hence follows the same 500 ** rules for counting changes. 501 */ 502 static void changes( 503 sqlite3_context *context, 504 int NotUsed, 505 sqlite3_value **NotUsed2 506 ){ 507 sqlite3 *db = sqlite3_context_db_handle(context); 508 UNUSED_PARAMETER2(NotUsed, NotUsed2); 509 sqlite3_result_int(context, sqlite3_changes(db)); 510 } 511 512 /* 513 ** Implementation of the total_changes() SQL function. The return value is 514 ** the same as the sqlite3_total_changes() API function. 515 */ 516 static void total_changes( 517 sqlite3_context *context, 518 int NotUsed, 519 sqlite3_value **NotUsed2 520 ){ 521 sqlite3 *db = sqlite3_context_db_handle(context); 522 UNUSED_PARAMETER2(NotUsed, NotUsed2); 523 /* IMP: R-52756-41993 This function is a wrapper around the 524 ** sqlite3_total_changes() C/C++ interface. */ 525 sqlite3_result_int(context, sqlite3_total_changes(db)); 526 } 527 528 /* 529 ** A structure defining how to do GLOB-style comparisons. 530 */ 531 struct compareInfo { 532 u8 matchAll; 533 u8 matchOne; 534 u8 matchSet; 535 u8 noCase; 536 }; 537 538 /* 539 ** For LIKE and GLOB matching on EBCDIC machines, assume that every 540 ** character is exactly one byte in size. Also, all characters are 541 ** able to participate in upper-case-to-lower-case mappings in EBCDIC 542 ** whereas only characters less than 0x80 do in ASCII. 543 */ 544 #if defined(SQLITE_EBCDIC) 545 # define sqlite3Utf8Read(A) (*((*A)++)) 546 # define GlogUpperToLower(A) A = sqlite3UpperToLower[A] 547 #else 548 # define GlogUpperToLower(A) if( !((A)&~0x7f) ){ A = sqlite3UpperToLower[A]; } 549 #endif 550 551 static const struct compareInfo globInfo = { '*', '?', '[', 0 }; 552 /* The correct SQL-92 behavior is for the LIKE operator to ignore 553 ** case. Thus 'a' LIKE 'A' would be true. */ 554 static const struct compareInfo likeInfoNorm = { '%', '_', 0, 1 }; 555 /* If SQLITE_CASE_SENSITIVE_LIKE is defined, then the LIKE operator 556 ** is case sensitive causing 'a' LIKE 'A' to be false */ 557 static const struct compareInfo likeInfoAlt = { '%', '_', 0, 0 }; 558 559 /* 560 ** Compare two UTF-8 strings for equality where the first string can 561 ** potentially be a "glob" expression. Return true (1) if they 562 ** are the same and false (0) if they are different. 563 ** 564 ** Globbing rules: 565 ** 566 ** '*' Matches any sequence of zero or more characters. 567 ** 568 ** '?' Matches exactly one character. 569 ** 570 ** [...] Matches one character from the enclosed list of 571 ** characters. 572 ** 573 ** [^...] Matches one character not in the enclosed list. 574 ** 575 ** With the [...] and [^...] matching, a ']' character can be included 576 ** in the list by making it the first character after '[' or '^'. A 577 ** range of characters can be specified using '-'. Example: 578 ** "[a-z]" matches any single lower-case letter. To match a '-', make 579 ** it the last character in the list. 580 ** 581 ** This routine is usually quick, but can be N**2 in the worst case. 582 ** 583 ** Hints: to match '*' or '?', put them in "[]". Like this: 584 ** 585 ** abc[*]xyz Matches "abc*xyz" only 586 */ 587 static int patternCompare( 588 const u8 *zPattern, /* The glob pattern */ 589 const u8 *zString, /* The string to compare against the glob */ 590 const struct compareInfo *pInfo, /* Information about how to do the compare */ 591 u32 esc /* The escape character */ 592 ){ 593 u32 c, c2; 594 int invert; 595 int seen; 596 u8 matchOne = pInfo->matchOne; 597 u8 matchAll = pInfo->matchAll; 598 u8 matchSet = pInfo->matchSet; 599 u8 noCase = pInfo->noCase; 600 int prevEscape = 0; /* True if the previous character was 'escape' */ 601 602 while( (c = sqlite3Utf8Read(&zPattern))!=0 ){ 603 if( c==matchAll && !prevEscape ){ 604 while( (c=sqlite3Utf8Read(&zPattern)) == matchAll 605 || c == matchOne ){ 606 if( c==matchOne && sqlite3Utf8Read(&zString)==0 ){ 607 return 0; 608 } 609 } 610 if( c==0 ){ 611 return 1; 612 }else if( c==esc ){ 613 c = sqlite3Utf8Read(&zPattern); 614 if( c==0 ){ 615 return 0; 616 } 617 }else if( c==matchSet ){ 618 assert( esc==0 ); /* This is GLOB, not LIKE */ 619 assert( matchSet<0x80 ); /* '[' is a single-byte character */ 620 while( *zString && patternCompare(&zPattern[-1],zString,pInfo,esc)==0 ){ 621 SQLITE_SKIP_UTF8(zString); 622 } 623 return *zString!=0; 624 } 625 while( (c2 = sqlite3Utf8Read(&zString))!=0 ){ 626 if( noCase ){ 627 GlogUpperToLower(c2); 628 GlogUpperToLower(c); 629 while( c2 != 0 && c2 != c ){ 630 c2 = sqlite3Utf8Read(&zString); 631 GlogUpperToLower(c2); 632 } 633 }else{ 634 while( c2 != 0 && c2 != c ){ 635 c2 = sqlite3Utf8Read(&zString); 636 } 637 } 638 if( c2==0 ) return 0; 639 if( patternCompare(zPattern,zString,pInfo,esc) ) return 1; 640 } 641 return 0; 642 }else if( c==matchOne && !prevEscape ){ 643 if( sqlite3Utf8Read(&zString)==0 ){ 644 return 0; 645 } 646 }else if( c==matchSet ){ 647 u32 prior_c = 0; 648 assert( esc==0 ); /* This only occurs for GLOB, not LIKE */ 649 seen = 0; 650 invert = 0; 651 c = sqlite3Utf8Read(&zString); 652 if( c==0 ) return 0; 653 c2 = sqlite3Utf8Read(&zPattern); 654 if( c2=='^' ){ 655 invert = 1; 656 c2 = sqlite3Utf8Read(&zPattern); 657 } 658 if( c2==']' ){ 659 if( c==']' ) seen = 1; 660 c2 = sqlite3Utf8Read(&zPattern); 661 } 662 while( c2 && c2!=']' ){ 663 if( c2=='-' && zPattern[0]!=']' && zPattern[0]!=0 && prior_c>0 ){ 664 c2 = sqlite3Utf8Read(&zPattern); 665 if( c>=prior_c && c<=c2 ) seen = 1; 666 prior_c = 0; 667 }else{ 668 if( c==c2 ){ 669 seen = 1; 670 } 671 prior_c = c2; 672 } 673 c2 = sqlite3Utf8Read(&zPattern); 674 } 675 if( c2==0 || (seen ^ invert)==0 ){ 676 return 0; 677 } 678 }else if( esc==c && !prevEscape ){ 679 prevEscape = 1; 680 }else{ 681 c2 = sqlite3Utf8Read(&zString); 682 if( noCase ){ 683 GlogUpperToLower(c); 684 GlogUpperToLower(c2); 685 } 686 if( c!=c2 ){ 687 return 0; 688 } 689 prevEscape = 0; 690 } 691 } 692 return *zString==0; 693 } 694 695 /* 696 ** Count the number of times that the LIKE operator (or GLOB which is 697 ** just a variation of LIKE) gets called. This is used for testing 698 ** only. 699 */ 700 #ifdef SQLITE_TEST 701 int sqlite3_like_count = 0; 702 #endif 703 704 705 /* 706 ** Implementation of the like() SQL function. This function implements 707 ** the build-in LIKE operator. The first argument to the function is the 708 ** pattern and the second argument is the string. So, the SQL statements: 709 ** 710 ** A LIKE B 711 ** 712 ** is implemented as like(B,A). 713 ** 714 ** This same function (with a different compareInfo structure) computes 715 ** the GLOB operator. 716 */ 717 static void likeFunc( 718 sqlite3_context *context, 719 int argc, 720 sqlite3_value **argv 721 ){ 722 const unsigned char *zA, *zB; 723 u32 escape = 0; 724 int nPat; 725 sqlite3 *db = sqlite3_context_db_handle(context); 726 727 zB = sqlite3_value_text(argv[0]); 728 zA = sqlite3_value_text(argv[1]); 729 730 /* Limit the length of the LIKE or GLOB pattern to avoid problems 731 ** of deep recursion and N*N behavior in patternCompare(). 732 */ 733 nPat = sqlite3_value_bytes(argv[0]); 734 testcase( nPat==db->aLimit[SQLITE_LIMIT_LIKE_PATTERN_LENGTH] ); 735 testcase( nPat==db->aLimit[SQLITE_LIMIT_LIKE_PATTERN_LENGTH]+1 ); 736 if( nPat > db->aLimit[SQLITE_LIMIT_LIKE_PATTERN_LENGTH] ){ 737 sqlite3_result_error(context, "LIKE or GLOB pattern too complex", -1); 738 return; 739 } 740 assert( zB==sqlite3_value_text(argv[0]) ); /* Encoding did not change */ 741 742 if( argc==3 ){ 743 /* The escape character string must consist of a single UTF-8 character. 744 ** Otherwise, return an error. 745 */ 746 const unsigned char *zEsc = sqlite3_value_text(argv[2]); 747 if( zEsc==0 ) return; 748 if( sqlite3Utf8CharLen((char*)zEsc, -1)!=1 ){ 749 sqlite3_result_error(context, 750 "ESCAPE expression must be a single character", -1); 751 return; 752 } 753 escape = sqlite3Utf8Read(&zEsc); 754 } 755 if( zA && zB ){ 756 struct compareInfo *pInfo = sqlite3_user_data(context); 757 #ifdef SQLITE_TEST 758 sqlite3_like_count++; 759 #endif 760 761 sqlite3_result_int(context, patternCompare(zB, zA, pInfo, escape)); 762 } 763 } 764 765 /* 766 ** Implementation of the NULLIF(x,y) function. The result is the first 767 ** argument if the arguments are different. The result is NULL if the 768 ** arguments are equal to each other. 769 */ 770 static void nullifFunc( 771 sqlite3_context *context, 772 int NotUsed, 773 sqlite3_value **argv 774 ){ 775 CollSeq *pColl = sqlite3GetFuncCollSeq(context); 776 UNUSED_PARAMETER(NotUsed); 777 if( sqlite3MemCompare(argv[0], argv[1], pColl)!=0 ){ 778 sqlite3_result_value(context, argv[0]); 779 } 780 } 781 782 /* 783 ** Implementation of the sqlite_version() function. The result is the version 784 ** of the SQLite library that is running. 785 */ 786 static void versionFunc( 787 sqlite3_context *context, 788 int NotUsed, 789 sqlite3_value **NotUsed2 790 ){ 791 UNUSED_PARAMETER2(NotUsed, NotUsed2); 792 /* IMP: R-48699-48617 This function is an SQL wrapper around the 793 ** sqlite3_libversion() C-interface. */ 794 sqlite3_result_text(context, sqlite3_libversion(), -1, SQLITE_STATIC); 795 } 796 797 /* 798 ** Implementation of the sqlite_source_id() function. The result is a string 799 ** that identifies the particular version of the source code used to build 800 ** SQLite. 801 */ 802 static void sourceidFunc( 803 sqlite3_context *context, 804 int NotUsed, 805 sqlite3_value **NotUsed2 806 ){ 807 UNUSED_PARAMETER2(NotUsed, NotUsed2); 808 /* IMP: R-24470-31136 This function is an SQL wrapper around the 809 ** sqlite3_sourceid() C interface. */ 810 sqlite3_result_text(context, sqlite3_sourceid(), -1, SQLITE_STATIC); 811 } 812 813 /* 814 ** Implementation of the sqlite_log() function. This is a wrapper around 815 ** sqlite3_log(). The return value is NULL. The function exists purely for 816 ** its side-effects. 817 */ 818 static void errlogFunc( 819 sqlite3_context *context, 820 int argc, 821 sqlite3_value **argv 822 ){ 823 UNUSED_PARAMETER(argc); 824 UNUSED_PARAMETER(context); 825 sqlite3_log(sqlite3_value_int(argv[0]), "%s", sqlite3_value_text(argv[1])); 826 } 827 828 /* 829 ** Implementation of the sqlite_compileoption_used() function. 830 ** The result is an integer that identifies if the compiler option 831 ** was used to build SQLite. 832 */ 833 #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS 834 static void compileoptionusedFunc( 835 sqlite3_context *context, 836 int argc, 837 sqlite3_value **argv 838 ){ 839 const char *zOptName; 840 assert( argc==1 ); 841 UNUSED_PARAMETER(argc); 842 /* IMP: R-39564-36305 The sqlite_compileoption_used() SQL 843 ** function is a wrapper around the sqlite3_compileoption_used() C/C++ 844 ** function. 845 */ 846 if( (zOptName = (const char*)sqlite3_value_text(argv[0]))!=0 ){ 847 sqlite3_result_int(context, sqlite3_compileoption_used(zOptName)); 848 } 849 } 850 #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */ 851 852 /* 853 ** Implementation of the sqlite_compileoption_get() function. 854 ** The result is a string that identifies the compiler options 855 ** used to build SQLite. 856 */ 857 #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS 858 static void compileoptiongetFunc( 859 sqlite3_context *context, 860 int argc, 861 sqlite3_value **argv 862 ){ 863 int n; 864 assert( argc==1 ); 865 UNUSED_PARAMETER(argc); 866 /* IMP: R-04922-24076 The sqlite_compileoption_get() SQL function 867 ** is a wrapper around the sqlite3_compileoption_get() C/C++ function. 868 */ 869 n = sqlite3_value_int(argv[0]); 870 sqlite3_result_text(context, sqlite3_compileoption_get(n), -1, SQLITE_STATIC); 871 } 872 #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */ 873 874 /* Array for converting from half-bytes (nybbles) into ASCII hex 875 ** digits. */ 876 static const char hexdigits[] = { 877 '0', '1', '2', '3', '4', '5', '6', '7', 878 '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' 879 }; 880 881 /* 882 ** EXPERIMENTAL - This is not an official function. The interface may 883 ** change. This function may disappear. Do not write code that depends 884 ** on this function. 885 ** 886 ** Implementation of the QUOTE() function. This function takes a single 887 ** argument. If the argument is numeric, the return value is the same as 888 ** the argument. If the argument is NULL, the return value is the string 889 ** "NULL". Otherwise, the argument is enclosed in single quotes with 890 ** single-quote escapes. 891 */ 892 static void quoteFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ 893 assert( argc==1 ); 894 UNUSED_PARAMETER(argc); 895 switch( sqlite3_value_type(argv[0]) ){ 896 case SQLITE_FLOAT: { 897 double r1, r2; 898 char zBuf[50]; 899 r1 = sqlite3_value_double(argv[0]); 900 sqlite3_snprintf(sizeof(zBuf), zBuf, "%!.15g", r1); 901 sqlite3AtoF(zBuf, &r2, 20, SQLITE_UTF8); 902 if( r1!=r2 ){ 903 sqlite3_snprintf(sizeof(zBuf), zBuf, "%!.20e", r1); 904 } 905 sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT); 906 break; 907 } 908 case SQLITE_INTEGER: { 909 sqlite3_result_value(context, argv[0]); 910 break; 911 } 912 case SQLITE_BLOB: { 913 char *zText = 0; 914 char const *zBlob = sqlite3_value_blob(argv[0]); 915 int nBlob = sqlite3_value_bytes(argv[0]); 916 assert( zBlob==sqlite3_value_blob(argv[0]) ); /* No encoding change */ 917 zText = (char *)contextMalloc(context, (2*(i64)nBlob)+4); 918 if( zText ){ 919 int i; 920 for(i=0; i<nBlob; i++){ 921 zText[(i*2)+2] = hexdigits[(zBlob[i]>>4)&0x0F]; 922 zText[(i*2)+3] = hexdigits[(zBlob[i])&0x0F]; 923 } 924 zText[(nBlob*2)+2] = '\''; 925 zText[(nBlob*2)+3] = '\0'; 926 zText[0] = 'X'; 927 zText[1] = '\''; 928 sqlite3_result_text(context, zText, -1, SQLITE_TRANSIENT); 929 sqlite3_free(zText); 930 } 931 break; 932 } 933 case SQLITE_TEXT: { 934 int i,j; 935 u64 n; 936 const unsigned char *zArg = sqlite3_value_text(argv[0]); 937 char *z; 938 939 if( zArg==0 ) return; 940 for(i=0, n=0; zArg[i]; i++){ if( zArg[i]=='\'' ) n++; } 941 z = contextMalloc(context, ((i64)i)+((i64)n)+3); 942 if( z ){ 943 z[0] = '\''; 944 for(i=0, j=1; zArg[i]; i++){ 945 z[j++] = zArg[i]; 946 if( zArg[i]=='\'' ){ 947 z[j++] = '\''; 948 } 949 } 950 z[j++] = '\''; 951 z[j] = 0; 952 sqlite3_result_text(context, z, j, sqlite3_free); 953 } 954 break; 955 } 956 default: { 957 assert( sqlite3_value_type(argv[0])==SQLITE_NULL ); 958 sqlite3_result_text(context, "NULL", 4, SQLITE_STATIC); 959 break; 960 } 961 } 962 } 963 964 /* 965 ** The hex() function. Interpret the argument as a blob. Return 966 ** a hexadecimal rendering as text. 967 */ 968 static void hexFunc( 969 sqlite3_context *context, 970 int argc, 971 sqlite3_value **argv 972 ){ 973 int i, n; 974 const unsigned char *pBlob; 975 char *zHex, *z; 976 assert( argc==1 ); 977 UNUSED_PARAMETER(argc); 978 pBlob = sqlite3_value_blob(argv[0]); 979 n = sqlite3_value_bytes(argv[0]); 980 assert( pBlob==sqlite3_value_blob(argv[0]) ); /* No encoding change */ 981 z = zHex = contextMalloc(context, ((i64)n)*2 + 1); 982 if( zHex ){ 983 for(i=0; i<n; i++, pBlob++){ 984 unsigned char c = *pBlob; 985 *(z++) = hexdigits[(c>>4)&0xf]; 986 *(z++) = hexdigits[c&0xf]; 987 } 988 *z = 0; 989 sqlite3_result_text(context, zHex, n*2, sqlite3_free); 990 } 991 } 992 993 /* 994 ** The zeroblob(N) function returns a zero-filled blob of size N bytes. 995 */ 996 static void zeroblobFunc( 997 sqlite3_context *context, 998 int argc, 999 sqlite3_value **argv 1000 ){ 1001 i64 n; 1002 sqlite3 *db = sqlite3_context_db_handle(context); 1003 assert( argc==1 ); 1004 UNUSED_PARAMETER(argc); 1005 n = sqlite3_value_int64(argv[0]); 1006 testcase( n==db->aLimit[SQLITE_LIMIT_LENGTH] ); 1007 testcase( n==db->aLimit[SQLITE_LIMIT_LENGTH]+1 ); 1008 if( n>db->aLimit[SQLITE_LIMIT_LENGTH] ){ 1009 sqlite3_result_error_toobig(context); 1010 }else{ 1011 sqlite3_result_zeroblob(context, (int)n); /* IMP: R-00293-64994 */ 1012 } 1013 } 1014 1015 /* 1016 ** The replace() function. Three arguments are all strings: call 1017 ** them A, B, and C. The result is also a string which is derived 1018 ** from A by replacing every occurance of B with C. The match 1019 ** must be exact. Collating sequences are not used. 1020 */ 1021 static void replaceFunc( 1022 sqlite3_context *context, 1023 int argc, 1024 sqlite3_value **argv 1025 ){ 1026 const unsigned char *zStr; /* The input string A */ 1027 const unsigned char *zPattern; /* The pattern string B */ 1028 const unsigned char *zRep; /* The replacement string C */ 1029 unsigned char *zOut; /* The output */ 1030 int nStr; /* Size of zStr */ 1031 int nPattern; /* Size of zPattern */ 1032 int nRep; /* Size of zRep */ 1033 i64 nOut; /* Maximum size of zOut */ 1034 int loopLimit; /* Last zStr[] that might match zPattern[] */ 1035 int i, j; /* Loop counters */ 1036 1037 assert( argc==3 ); 1038 UNUSED_PARAMETER(argc); 1039 zStr = sqlite3_value_text(argv[0]); 1040 if( zStr==0 ) return; 1041 nStr = sqlite3_value_bytes(argv[0]); 1042 assert( zStr==sqlite3_value_text(argv[0]) ); /* No encoding change */ 1043 zPattern = sqlite3_value_text(argv[1]); 1044 if( zPattern==0 ){ 1045 assert( sqlite3_value_type(argv[1])==SQLITE_NULL 1046 || sqlite3_context_db_handle(context)->mallocFailed ); 1047 return; 1048 } 1049 if( zPattern[0]==0 ){ 1050 assert( sqlite3_value_type(argv[1])!=SQLITE_NULL ); 1051 sqlite3_result_value(context, argv[0]); 1052 return; 1053 } 1054 nPattern = sqlite3_value_bytes(argv[1]); 1055 assert( zPattern==sqlite3_value_text(argv[1]) ); /* No encoding change */ 1056 zRep = sqlite3_value_text(argv[2]); 1057 if( zRep==0 ) return; 1058 nRep = sqlite3_value_bytes(argv[2]); 1059 assert( zRep==sqlite3_value_text(argv[2]) ); 1060 nOut = nStr + 1; 1061 assert( nOut<SQLITE_MAX_LENGTH ); 1062 zOut = contextMalloc(context, (i64)nOut); 1063 if( zOut==0 ){ 1064 return; 1065 } 1066 loopLimit = nStr - nPattern; 1067 for(i=j=0; i<=loopLimit; i++){ 1068 if( zStr[i]!=zPattern[0] || memcmp(&zStr[i], zPattern, nPattern) ){ 1069 zOut[j++] = zStr[i]; 1070 }else{ 1071 u8 *zOld; 1072 sqlite3 *db = sqlite3_context_db_handle(context); 1073 nOut += nRep - nPattern; 1074 testcase( nOut-1==db->aLimit[SQLITE_LIMIT_LENGTH] ); 1075 testcase( nOut-2==db->aLimit[SQLITE_LIMIT_LENGTH] ); 1076 if( nOut-1>db->aLimit[SQLITE_LIMIT_LENGTH] ){ 1077 sqlite3_result_error_toobig(context); 1078 sqlite3_free(zOut); 1079 return; 1080 } 1081 zOld = zOut; 1082 zOut = sqlite3_realloc(zOut, (int)nOut); 1083 if( zOut==0 ){ 1084 sqlite3_result_error_nomem(context); 1085 sqlite3_free(zOld); 1086 return; 1087 } 1088 memcpy(&zOut[j], zRep, nRep); 1089 j += nRep; 1090 i += nPattern-1; 1091 } 1092 } 1093 assert( j+nStr-i+1==nOut ); 1094 memcpy(&zOut[j], &zStr[i], nStr-i); 1095 j += nStr - i; 1096 assert( j<=nOut ); 1097 zOut[j] = 0; 1098 sqlite3_result_text(context, (char*)zOut, j, sqlite3_free); 1099 } 1100 1101 /* 1102 ** Implementation of the TRIM(), LTRIM(), and RTRIM() functions. 1103 ** The userdata is 0x1 for left trim, 0x2 for right trim, 0x3 for both. 1104 */ 1105 static void trimFunc( 1106 sqlite3_context *context, 1107 int argc, 1108 sqlite3_value **argv 1109 ){ 1110 const unsigned char *zIn; /* Input string */ 1111 const unsigned char *zCharSet; /* Set of characters to trim */ 1112 int nIn; /* Number of bytes in input */ 1113 int flags; /* 1: trimleft 2: trimright 3: trim */ 1114 int i; /* Loop counter */ 1115 unsigned char *aLen = 0; /* Length of each character in zCharSet */ 1116 unsigned char **azChar = 0; /* Individual characters in zCharSet */ 1117 int nChar; /* Number of characters in zCharSet */ 1118 1119 if( sqlite3_value_type(argv[0])==SQLITE_NULL ){ 1120 return; 1121 } 1122 zIn = sqlite3_value_text(argv[0]); 1123 if( zIn==0 ) return; 1124 nIn = sqlite3_value_bytes(argv[0]); 1125 assert( zIn==sqlite3_value_text(argv[0]) ); 1126 if( argc==1 ){ 1127 static const unsigned char lenOne[] = { 1 }; 1128 static unsigned char * const azOne[] = { (u8*)" " }; 1129 nChar = 1; 1130 aLen = (u8*)lenOne; 1131 azChar = (unsigned char **)azOne; 1132 zCharSet = 0; 1133 }else if( (zCharSet = sqlite3_value_text(argv[1]))==0 ){ 1134 return; 1135 }else{ 1136 const unsigned char *z; 1137 for(z=zCharSet, nChar=0; *z; nChar++){ 1138 SQLITE_SKIP_UTF8(z); 1139 } 1140 if( nChar>0 ){ 1141 azChar = contextMalloc(context, ((i64)nChar)*(sizeof(char*)+1)); 1142 if( azChar==0 ){ 1143 return; 1144 } 1145 aLen = (unsigned char*)&azChar[nChar]; 1146 for(z=zCharSet, nChar=0; *z; nChar++){ 1147 azChar[nChar] = (unsigned char *)z; 1148 SQLITE_SKIP_UTF8(z); 1149 aLen[nChar] = (u8)(z - azChar[nChar]); 1150 } 1151 } 1152 } 1153 if( nChar>0 ){ 1154 flags = SQLITE_PTR_TO_INT(sqlite3_user_data(context)); 1155 if( flags & 1 ){ 1156 while( nIn>0 ){ 1157 int len = 0; 1158 for(i=0; i<nChar; i++){ 1159 len = aLen[i]; 1160 if( len<=nIn && memcmp(zIn, azChar[i], len)==0 ) break; 1161 } 1162 if( i>=nChar ) break; 1163 zIn += len; 1164 nIn -= len; 1165 } 1166 } 1167 if( flags & 2 ){ 1168 while( nIn>0 ){ 1169 int len = 0; 1170 for(i=0; i<nChar; i++){ 1171 len = aLen[i]; 1172 if( len<=nIn && memcmp(&zIn[nIn-len],azChar[i],len)==0 ) break; 1173 } 1174 if( i>=nChar ) break; 1175 nIn -= len; 1176 } 1177 } 1178 if( zCharSet ){ 1179 sqlite3_free(azChar); 1180 } 1181 } 1182 sqlite3_result_text(context, (char*)zIn, nIn, SQLITE_TRANSIENT); 1183 } 1184 1185 1186 /* IMP: R-25361-16150 This function is omitted from SQLite by default. It 1187 ** is only available if the SQLITE_SOUNDEX compile-time option is used 1188 ** when SQLite is built. 1189 */ 1190 #ifdef SQLITE_SOUNDEX 1191 /* 1192 ** Compute the soundex encoding of a word. 1193 ** 1194 ** IMP: R-59782-00072 The soundex(X) function returns a string that is the 1195 ** soundex encoding of the string X. 1196 */ 1197 static void soundexFunc( 1198 sqlite3_context *context, 1199 int argc, 1200 sqlite3_value **argv 1201 ){ 1202 char zResult[8]; 1203 const u8 *zIn; 1204 int i, j; 1205 static const unsigned char iCode[] = { 1206 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1207 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1208 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1209 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1210 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0, 1211 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0, 1212 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0, 1213 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0, 1214 }; 1215 assert( argc==1 ); 1216 zIn = (u8*)sqlite3_value_text(argv[0]); 1217 if( zIn==0 ) zIn = (u8*)""; 1218 for(i=0; zIn[i] && !sqlite3Isalpha(zIn[i]); i++){} 1219 if( zIn[i] ){ 1220 u8 prevcode = iCode[zIn[i]&0x7f]; 1221 zResult[0] = sqlite3Toupper(zIn[i]); 1222 for(j=1; j<4 && zIn[i]; i++){ 1223 int code = iCode[zIn[i]&0x7f]; 1224 if( code>0 ){ 1225 if( code!=prevcode ){ 1226 prevcode = code; 1227 zResult[j++] = code + '0'; 1228 } 1229 }else{ 1230 prevcode = 0; 1231 } 1232 } 1233 while( j<4 ){ 1234 zResult[j++] = '0'; 1235 } 1236 zResult[j] = 0; 1237 sqlite3_result_text(context, zResult, 4, SQLITE_TRANSIENT); 1238 }else{ 1239 /* IMP: R-64894-50321 The string "?000" is returned if the argument 1240 ** is NULL or contains no ASCII alphabetic characters. */ 1241 sqlite3_result_text(context, "?000", 4, SQLITE_STATIC); 1242 } 1243 } 1244 #endif /* SQLITE_SOUNDEX */ 1245 1246 #ifndef SQLITE_OMIT_LOAD_EXTENSION 1247 /* 1248 ** A function that loads a shared-library extension then returns NULL. 1249 */ 1250 static void loadExt(sqlite3_context *context, int argc, sqlite3_value **argv){ 1251 const char *zFile = (const char *)sqlite3_value_text(argv[0]); 1252 const char *zProc; 1253 sqlite3 *db = sqlite3_context_db_handle(context); 1254 char *zErrMsg = 0; 1255 1256 if( argc==2 ){ 1257 zProc = (const char *)sqlite3_value_text(argv[1]); 1258 }else{ 1259 zProc = 0; 1260 } 1261 if( zFile && sqlite3_load_extension(db, zFile, zProc, &zErrMsg) ){ 1262 sqlite3_result_error(context, zErrMsg, -1); 1263 sqlite3_free(zErrMsg); 1264 } 1265 } 1266 #endif 1267 1268 1269 /* 1270 ** An instance of the following structure holds the context of a 1271 ** sum() or avg() aggregate computation. 1272 */ 1273 typedef struct SumCtx SumCtx; 1274 struct SumCtx { 1275 double rSum; /* Floating point sum */ 1276 i64 iSum; /* Integer sum */ 1277 i64 cnt; /* Number of elements summed */ 1278 u8 overflow; /* True if integer overflow seen */ 1279 u8 approx; /* True if non-integer value was input to the sum */ 1280 }; 1281 1282 /* 1283 ** Routines used to compute the sum, average, and total. 1284 ** 1285 ** The SUM() function follows the (broken) SQL standard which means 1286 ** that it returns NULL if it sums over no inputs. TOTAL returns 1287 ** 0.0 in that case. In addition, TOTAL always returns a float where 1288 ** SUM might return an integer if it never encounters a floating point 1289 ** value. TOTAL never fails, but SUM might through an exception if 1290 ** it overflows an integer. 1291 */ 1292 static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){ 1293 SumCtx *p; 1294 int type; 1295 assert( argc==1 ); 1296 UNUSED_PARAMETER(argc); 1297 p = sqlite3_aggregate_context(context, sizeof(*p)); 1298 type = sqlite3_value_numeric_type(argv[0]); 1299 if( p && type!=SQLITE_NULL ){ 1300 p->cnt++; 1301 if( type==SQLITE_INTEGER ){ 1302 i64 v = sqlite3_value_int64(argv[0]); 1303 p->rSum += v; 1304 if( (p->approx|p->overflow)==0 && sqlite3AddInt64(&p->iSum, v) ){ 1305 p->overflow = 1; 1306 } 1307 }else{ 1308 p->rSum += sqlite3_value_double(argv[0]); 1309 p->approx = 1; 1310 } 1311 } 1312 } 1313 static void sumFinalize(sqlite3_context *context){ 1314 SumCtx *p; 1315 p = sqlite3_aggregate_context(context, 0); 1316 if( p && p->cnt>0 ){ 1317 if( p->overflow ){ 1318 sqlite3_result_error(context,"integer overflow",-1); 1319 }else if( p->approx ){ 1320 sqlite3_result_double(context, p->rSum); 1321 }else{ 1322 sqlite3_result_int64(context, p->iSum); 1323 } 1324 } 1325 } 1326 static void avgFinalize(sqlite3_context *context){ 1327 SumCtx *p; 1328 p = sqlite3_aggregate_context(context, 0); 1329 if( p && p->cnt>0 ){ 1330 sqlite3_result_double(context, p->rSum/(double)p->cnt); 1331 } 1332 } 1333 static void totalFinalize(sqlite3_context *context){ 1334 SumCtx *p; 1335 p = sqlite3_aggregate_context(context, 0); 1336 /* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */ 1337 sqlite3_result_double(context, p ? p->rSum : (double)0); 1338 } 1339 1340 /* 1341 ** The following structure keeps track of state information for the 1342 ** count() aggregate function. 1343 */ 1344 typedef struct CountCtx CountCtx; 1345 struct CountCtx { 1346 i64 n; 1347 }; 1348 1349 /* 1350 ** Routines to implement the count() aggregate function. 1351 */ 1352 static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){ 1353 CountCtx *p; 1354 p = sqlite3_aggregate_context(context, sizeof(*p)); 1355 if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){ 1356 p->n++; 1357 } 1358 1359 #ifndef SQLITE_OMIT_DEPRECATED 1360 /* The sqlite3_aggregate_count() function is deprecated. But just to make 1361 ** sure it still operates correctly, verify that its count agrees with our 1362 ** internal count when using count(*) and when the total count can be 1363 ** expressed as a 32-bit integer. */ 1364 assert( argc==1 || p==0 || p->n>0x7fffffff 1365 || p->n==sqlite3_aggregate_count(context) ); 1366 #endif 1367 } 1368 static void countFinalize(sqlite3_context *context){ 1369 CountCtx *p; 1370 p = sqlite3_aggregate_context(context, 0); 1371 sqlite3_result_int64(context, p ? p->n : 0); 1372 } 1373 1374 /* 1375 ** Routines to implement min() and max() aggregate functions. 1376 */ 1377 static void minmaxStep( 1378 sqlite3_context *context, 1379 int NotUsed, 1380 sqlite3_value **argv 1381 ){ 1382 Mem *pArg = (Mem *)argv[0]; 1383 Mem *pBest; 1384 UNUSED_PARAMETER(NotUsed); 1385 1386 pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest)); 1387 if( !pBest ) return; 1388 1389 if( sqlite3_value_type(argv[0])==SQLITE_NULL ){ 1390 if( pBest->flags ) sqlite3SkipAccumulatorLoad(context); 1391 }else if( pBest->flags ){ 1392 int max; 1393 int cmp; 1394 CollSeq *pColl = sqlite3GetFuncCollSeq(context); 1395 /* This step function is used for both the min() and max() aggregates, 1396 ** the only difference between the two being that the sense of the 1397 ** comparison is inverted. For the max() aggregate, the 1398 ** sqlite3_user_data() function returns (void *)-1. For min() it 1399 ** returns (void *)db, where db is the sqlite3* database pointer. 1400 ** Therefore the next statement sets variable 'max' to 1 for the max() 1401 ** aggregate, or 0 for min(). 1402 */ 1403 max = sqlite3_user_data(context)!=0; 1404 cmp = sqlite3MemCompare(pBest, pArg, pColl); 1405 if( (max && cmp<0) || (!max && cmp>0) ){ 1406 sqlite3VdbeMemCopy(pBest, pArg); 1407 }else{ 1408 sqlite3SkipAccumulatorLoad(context); 1409 } 1410 }else{ 1411 sqlite3VdbeMemCopy(pBest, pArg); 1412 } 1413 } 1414 static void minMaxFinalize(sqlite3_context *context){ 1415 sqlite3_value *pRes; 1416 pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0); 1417 if( pRes ){ 1418 if( pRes->flags ){ 1419 sqlite3_result_value(context, pRes); 1420 } 1421 sqlite3VdbeMemRelease(pRes); 1422 } 1423 } 1424 1425 /* 1426 ** group_concat(EXPR, ?SEPARATOR?) 1427 */ 1428 static void groupConcatStep( 1429 sqlite3_context *context, 1430 int argc, 1431 sqlite3_value **argv 1432 ){ 1433 const char *zVal; 1434 StrAccum *pAccum; 1435 const char *zSep; 1436 int nVal, nSep; 1437 assert( argc==1 || argc==2 ); 1438 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return; 1439 pAccum = (StrAccum*)sqlite3_aggregate_context(context, sizeof(*pAccum)); 1440 1441 if( pAccum ){ 1442 sqlite3 *db = sqlite3_context_db_handle(context); 1443 int firstTerm = pAccum->useMalloc==0; 1444 pAccum->useMalloc = 2; 1445 pAccum->mxAlloc = db->aLimit[SQLITE_LIMIT_LENGTH]; 1446 if( !firstTerm ){ 1447 if( argc==2 ){ 1448 zSep = (char*)sqlite3_value_text(argv[1]); 1449 nSep = sqlite3_value_bytes(argv[1]); 1450 }else{ 1451 zSep = ","; 1452 nSep = 1; 1453 } 1454 sqlite3StrAccumAppend(pAccum, zSep, nSep); 1455 } 1456 zVal = (char*)sqlite3_value_text(argv[0]); 1457 nVal = sqlite3_value_bytes(argv[0]); 1458 sqlite3StrAccumAppend(pAccum, zVal, nVal); 1459 } 1460 } 1461 static void groupConcatFinalize(sqlite3_context *context){ 1462 StrAccum *pAccum; 1463 pAccum = sqlite3_aggregate_context(context, 0); 1464 if( pAccum ){ 1465 if( pAccum->tooBig ){ 1466 sqlite3_result_error_toobig(context); 1467 }else if( pAccum->mallocFailed ){ 1468 sqlite3_result_error_nomem(context); 1469 }else{ 1470 sqlite3_result_text(context, sqlite3StrAccumFinish(pAccum), -1, 1471 sqlite3_free); 1472 } 1473 } 1474 } 1475 1476 /* 1477 ** This routine does per-connection function registration. Most 1478 ** of the built-in functions above are part of the global function set. 1479 ** This routine only deals with those that are not global. 1480 */ 1481 void sqlite3RegisterBuiltinFunctions(sqlite3 *db){ 1482 int rc = sqlite3_overload_function(db, "MATCH", 2); 1483 assert( rc==SQLITE_NOMEM || rc==SQLITE_OK ); 1484 if( rc==SQLITE_NOMEM ){ 1485 db->mallocFailed = 1; 1486 } 1487 } 1488 1489 /* 1490 ** Set the LIKEOPT flag on the 2-argument function with the given name. 1491 */ 1492 static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){ 1493 FuncDef *pDef; 1494 pDef = sqlite3FindFunction(db, zName, sqlite3Strlen30(zName), 1495 2, SQLITE_UTF8, 0); 1496 if( ALWAYS(pDef) ){ 1497 pDef->flags = flagVal; 1498 } 1499 } 1500 1501 /* 1502 ** Register the built-in LIKE and GLOB functions. The caseSensitive 1503 ** parameter determines whether or not the LIKE operator is case 1504 ** sensitive. GLOB is always case sensitive. 1505 */ 1506 void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){ 1507 struct compareInfo *pInfo; 1508 if( caseSensitive ){ 1509 pInfo = (struct compareInfo*)&likeInfoAlt; 1510 }else{ 1511 pInfo = (struct compareInfo*)&likeInfoNorm; 1512 } 1513 sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0); 1514 sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0, 0); 1515 sqlite3CreateFunc(db, "glob", 2, SQLITE_UTF8, 1516 (struct compareInfo*)&globInfo, likeFunc, 0, 0, 0); 1517 setLikeOptFlag(db, "glob", SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE); 1518 setLikeOptFlag(db, "like", 1519 caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) : SQLITE_FUNC_LIKE); 1520 } 1521 1522 /* 1523 ** pExpr points to an expression which implements a function. If 1524 ** it is appropriate to apply the LIKE optimization to that function 1525 ** then set aWc[0] through aWc[2] to the wildcard characters and 1526 ** return TRUE. If the function is not a LIKE-style function then 1527 ** return FALSE. 1528 */ 1529 int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){ 1530 FuncDef *pDef; 1531 if( pExpr->op!=TK_FUNCTION 1532 || !pExpr->x.pList 1533 || pExpr->x.pList->nExpr!=2 1534 ){ 1535 return 0; 1536 } 1537 assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); 1538 pDef = sqlite3FindFunction(db, pExpr->u.zToken, 1539 sqlite3Strlen30(pExpr->u.zToken), 1540 2, SQLITE_UTF8, 0); 1541 if( NEVER(pDef==0) || (pDef->flags & SQLITE_FUNC_LIKE)==0 ){ 1542 return 0; 1543 } 1544 1545 /* The memcpy() statement assumes that the wildcard characters are 1546 ** the first three statements in the compareInfo structure. The 1547 ** asserts() that follow verify that assumption 1548 */ 1549 memcpy(aWc, pDef->pUserData, 3); 1550 assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll ); 1551 assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne ); 1552 assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet ); 1553 *pIsNocase = (pDef->flags & SQLITE_FUNC_CASE)==0; 1554 return 1; 1555 } 1556 1557 /* 1558 ** All all of the FuncDef structures in the aBuiltinFunc[] array above 1559 ** to the global function hash table. This occurs at start-time (as 1560 ** a consequence of calling sqlite3_initialize()). 1561 ** 1562 ** After this routine runs 1563 */ 1564 void sqlite3RegisterGlobalFunctions(void){ 1565 /* 1566 ** The following array holds FuncDef structures for all of the functions 1567 ** defined in this file. 1568 ** 1569 ** The array cannot be constant since changes are made to the 1570 ** FuncDef.pHash elements at start-time. The elements of this array 1571 ** are read-only after initialization is complete. 1572 */ 1573 static SQLITE_WSD FuncDef aBuiltinFunc[] = { 1574 FUNCTION(ltrim, 1, 1, 0, trimFunc ), 1575 FUNCTION(ltrim, 2, 1, 0, trimFunc ), 1576 FUNCTION(rtrim, 1, 2, 0, trimFunc ), 1577 FUNCTION(rtrim, 2, 2, 0, trimFunc ), 1578 FUNCTION(trim, 1, 3, 0, trimFunc ), 1579 FUNCTION(trim, 2, 3, 0, trimFunc ), 1580 FUNCTION(min, -1, 0, 1, minmaxFunc ), 1581 FUNCTION(min, 0, 0, 1, 0 ), 1582 AGGREGATE(min, 1, 0, 1, minmaxStep, minMaxFinalize ), 1583 FUNCTION(max, -1, 1, 1, minmaxFunc ), 1584 FUNCTION(max, 0, 1, 1, 0 ), 1585 AGGREGATE(max, 1, 1, 1, minmaxStep, minMaxFinalize ), 1586 FUNCTION2(typeof, 1, 0, 0, typeofFunc, SQLITE_FUNC_TYPEOF), 1587 FUNCTION2(length, 1, 0, 0, lengthFunc, SQLITE_FUNC_LENGTH), 1588 FUNCTION(instr, 2, 0, 0, instrFunc ), 1589 FUNCTION(substr, 2, 0, 0, substrFunc ), 1590 FUNCTION(substr, 3, 0, 0, substrFunc ), 1591 FUNCTION(abs, 1, 0, 0, absFunc ), 1592 #ifndef SQLITE_OMIT_FLOATING_POINT 1593 FUNCTION(round, 1, 0, 0, roundFunc ), 1594 FUNCTION(round, 2, 0, 0, roundFunc ), 1595 #endif 1596 FUNCTION(upper, 1, 0, 0, upperFunc ), 1597 FUNCTION(lower, 1, 0, 0, lowerFunc ), 1598 FUNCTION(coalesce, 1, 0, 0, 0 ), 1599 FUNCTION(coalesce, 0, 0, 0, 0 ), 1600 FUNCTION2(coalesce, -1, 0, 0, ifnullFunc, SQLITE_FUNC_COALESCE), 1601 FUNCTION(hex, 1, 0, 0, hexFunc ), 1602 FUNCTION2(ifnull, 2, 0, 0, ifnullFunc, SQLITE_FUNC_COALESCE), 1603 FUNCTION(random, 0, 0, 0, randomFunc ), 1604 FUNCTION(randomblob, 1, 0, 0, randomBlob ), 1605 FUNCTION(nullif, 2, 0, 1, nullifFunc ), 1606 FUNCTION(sqlite_version, 0, 0, 0, versionFunc ), 1607 FUNCTION(sqlite_source_id, 0, 0, 0, sourceidFunc ), 1608 FUNCTION(sqlite_log, 2, 0, 0, errlogFunc ), 1609 #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS 1610 FUNCTION(sqlite_compileoption_used,1, 0, 0, compileoptionusedFunc ), 1611 FUNCTION(sqlite_compileoption_get, 1, 0, 0, compileoptiongetFunc ), 1612 #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */ 1613 FUNCTION(quote, 1, 0, 0, quoteFunc ), 1614 FUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid), 1615 FUNCTION(changes, 0, 0, 0, changes ), 1616 FUNCTION(total_changes, 0, 0, 0, total_changes ), 1617 FUNCTION(replace, 3, 0, 0, replaceFunc ), 1618 FUNCTION(zeroblob, 1, 0, 0, zeroblobFunc ), 1619 #ifdef SQLITE_SOUNDEX 1620 FUNCTION(soundex, 1, 0, 0, soundexFunc ), 1621 #endif 1622 #ifndef SQLITE_OMIT_LOAD_EXTENSION 1623 FUNCTION(load_extension, 1, 0, 0, loadExt ), 1624 FUNCTION(load_extension, 2, 0, 0, loadExt ), 1625 #endif 1626 AGGREGATE(sum, 1, 0, 0, sumStep, sumFinalize ), 1627 AGGREGATE(total, 1, 0, 0, sumStep, totalFinalize ), 1628 AGGREGATE(avg, 1, 0, 0, sumStep, avgFinalize ), 1629 /* AGGREGATE(count, 0, 0, 0, countStep, countFinalize ), */ 1630 {0,SQLITE_UTF8,SQLITE_FUNC_COUNT,0,0,0,countStep,countFinalize,"count",0,0}, 1631 AGGREGATE(count, 1, 0, 0, countStep, countFinalize ), 1632 AGGREGATE(group_concat, 1, 0, 0, groupConcatStep, groupConcatFinalize), 1633 AGGREGATE(group_concat, 2, 0, 0, groupConcatStep, groupConcatFinalize), 1634 1635 LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE), 1636 #ifdef SQLITE_CASE_SENSITIVE_LIKE 1637 LIKEFUNC(like, 2, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE), 1638 LIKEFUNC(like, 3, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE), 1639 #else 1640 LIKEFUNC(like, 2, &likeInfoNorm, SQLITE_FUNC_LIKE), 1641 LIKEFUNC(like, 3, &likeInfoNorm, SQLITE_FUNC_LIKE), 1642 #endif 1643 }; 1644 1645 int i; 1646 FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions); 1647 FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aBuiltinFunc); 1648 1649 for(i=0; i<ArraySize(aBuiltinFunc); i++){ 1650 sqlite3FuncDefInsert(pHash, &aFunc[i]); 1651 } 1652 sqlite3RegisterDateTimeFunctions(); 1653 #ifndef SQLITE_OMIT_ALTERTABLE 1654 sqlite3AlterFunctions(); 1655 #endif 1656 } 1657