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