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.43.2.3 2004/07/18 23:03:11 drh Exp $ 20 */ 21 #include <ctype.h> 22 #include <math.h> 23 #include <stdlib.h> 24 #include <sys/u8_textprep.h> 25 #include <assert.h> 26 #include "sqliteInt.h" 27 #include "os.h" 28 29 /* 30 ** Implementation of the non-aggregate min() and max() functions 31 */ 32 static void minmaxFunc(sqlite_func *context, int argc, const char **argv){ 33 const char *zBest; 34 int i; 35 int (*xCompare)(const char*, const char*); 36 int mask; /* 0 for min() or 0xffffffff for max() */ 37 38 if( argc==0 ) return; 39 mask = (int)sqlite_user_data(context); 40 zBest = argv[0]; 41 if( zBest==0 ) return; 42 if( argv[1][0]=='n' ){ 43 xCompare = sqliteCompare; 44 }else{ 45 xCompare = strcmp; 46 } 47 for(i=2; i<argc; i+=2){ 48 if( argv[i]==0 ) return; 49 if( (xCompare(argv[i], zBest)^mask)<0 ){ 50 zBest = argv[i]; 51 } 52 } 53 sqlite_set_result_string(context, zBest, -1); 54 } 55 56 /* 57 ** Return the type of the argument. 58 */ 59 static void typeofFunc(sqlite_func *context, int argc, const char **argv){ 60 assert( argc==2 ); 61 sqlite_set_result_string(context, argv[1], -1); 62 } 63 64 /* 65 ** Implementation of the length() function 66 */ 67 static void lengthFunc(sqlite_func *context, int argc, const char **argv){ 68 const char *z; 69 int len; 70 71 assert( argc==1 ); 72 z = argv[0]; 73 if( z==0 ) return; 74 #ifdef SQLITE_UTF8 75 for(len=0; *z; z++){ if( (0xc0&*z)!=0x80 ) len++; } 76 #else 77 len = strlen(z); 78 #endif 79 sqlite_set_result_int(context, len); 80 } 81 82 /* 83 ** Implementation of the abs() function 84 */ 85 static void absFunc(sqlite_func *context, int argc, const char **argv){ 86 const char *z; 87 assert( argc==1 ); 88 z = argv[0]; 89 if( z==0 ) return; 90 if( z[0]=='-' && isdigit(z[1]) ) z++; 91 sqlite_set_result_string(context, z, -1); 92 } 93 94 /* 95 ** Implementation of the substr() function 96 */ 97 static void substrFunc(sqlite_func *context, int argc, const char **argv){ 98 const char *z; 99 #ifdef SQLITE_UTF8 100 const char *z2; 101 int i; 102 #endif 103 int p1, p2, len; 104 assert( argc==3 ); 105 z = argv[0]; 106 if( z==0 ) return; 107 p1 = atoi(argv[1]?argv[1]:0); 108 p2 = atoi(argv[2]?argv[2]:0); 109 #ifdef SQLITE_UTF8 110 for(len=0, z2=z; *z2; z2++){ if( (0xc0&*z2)!=0x80 ) len++; } 111 #else 112 len = strlen(z); 113 #endif 114 if( p1<0 ){ 115 p1 += len; 116 if( p1<0 ){ 117 p2 += p1; 118 p1 = 0; 119 } 120 }else if( p1>0 ){ 121 p1--; 122 } 123 if( p1+p2>len ){ 124 p2 = len-p1; 125 } 126 #ifdef SQLITE_UTF8 127 for(i=0; i<p1 && z[i]; i++){ 128 if( (z[i]&0xc0)==0x80 ) p1++; 129 } 130 while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p1++; } 131 for(; i<p1+p2 && z[i]; i++){ 132 if( (z[i]&0xc0)==0x80 ) p2++; 133 } 134 while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p2++; } 135 #endif 136 if( p2<0 ) p2 = 0; 137 sqlite_set_result_string(context, &z[p1], p2); 138 } 139 140 /* 141 ** Implementation of the round() function 142 */ 143 static void roundFunc(sqlite_func *context, int argc, const char **argv){ 144 int n; 145 double r; 146 char zBuf[100]; 147 assert( argc==1 || argc==2 ); 148 if( argv[0]==0 || (argc==2 && argv[1]==0) ) return; 149 n = argc==2 ? atoi(argv[1]) : 0; 150 if( n>30 ) n = 30; 151 if( n<0 ) n = 0; 152 r = sqliteAtoF(argv[0], 0); 153 sprintf(zBuf,"%.*f",n,r); 154 sqlite_set_result_string(context, zBuf, -1); 155 } 156 157 /* 158 ** Implementation of the upper() and lower() SQL functions. 159 */ 160 static void upperFunc(sqlite_func *context, int argc, const char **argv){ 161 unsigned char *z; 162 int i; 163 if( argc<1 || argv[0]==0 ) return; 164 z = (unsigned char*)sqlite_set_result_string(context, argv[0], -1); 165 if( z==0 ) return; 166 for(i=0; z[i]; i++){ 167 if( islower(z[i]) ) z[i] = toupper(z[i]); 168 } 169 } 170 static void lowerFunc(sqlite_func *context, int argc, const char **argv){ 171 unsigned char *z; 172 int i; 173 if( argc<1 || argv[0]==0 ) return; 174 z = (unsigned char*)sqlite_set_result_string(context, argv[0], -1); 175 if( z==0 ) return; 176 for(i=0; z[i]; i++){ 177 if( isupper(z[i]) ) z[i] = tolower(z[i]); 178 } 179 } 180 181 /* 182 * A utility wrapper around u8_textprep_str() that returns an allocated 183 * string. The result must be freed or passed to 184 * sqlite_set_result_string(). 185 * 186 * This is a Solaris-specific function, though it could be made 187 * portable. u8_textprep_str() and friends are CDDL'ed. This code was 188 * added to this file without changing the public domain notice, and 189 * therefore is in the public domain as well. 190 */ 191 static 192 char * 193 utf8textprep(const char *s, int flag) 194 { 195 char *res = NULL; 196 char *outs; 197 size_t inlen, outlen, inbytesleft, outbytesleft; 198 int rc, err; 199 200 /* 201 * u8_textprep_str() does not allocate memory. The input and 202 * output buffers may differ in size (though that would be more 203 * likely when normalization is done). We have to loop over it... 204 * 205 * To improve the chances that we can avoid looping we add 10 206 * bytes of output buffer room the first go around. 207 */ 208 inlen = inbytesleft = strlen(s); 209 outlen = outbytesleft = inlen + 10; 210 if ((res = sqliteMalloc(outlen)) == NULL) 211 return (NULL); 212 outs = res; 213 214 while ((rc = u8_textprep_str((char *)s, &inbytesleft, outs, 215 &outbytesleft, flag, U8_UNICODE_LATEST, &err)) < 0 && 216 err == E2BIG) { 217 if ((res = sqliteRealloc(res, outlen + inbytesleft)) == NULL) 218 return (NULL); 219 /* adjust input/output buffer pointers */ 220 s += (inlen - inbytesleft); 221 outs = res + outlen - outbytesleft; 222 /* adjust outbytesleft and outlen */ 223 outlen += inbytesleft; 224 outbytesleft += inbytesleft; 225 } 226 227 if (rc < 0) { 228 free(res); 229 res = NULL; 230 return (NULL); 231 } 232 233 res[outlen - outbytesleft] = '\0'; 234 235 return (res); 236 } 237 /* 238 * A Unicode-capable case-folding (to lower) function 239 * 240 * See block comment for case_fold_utf8(). 241 */ 242 static 243 void 244 lower_utf8Func(sqlite_func *context, int argc, const char **argv) 245 { 246 char *lower = NULL; 247 248 /* 249 * SQLite functions can take many arguments, but this function 250 * uses only one, and we call sqlite_create_function() with 251 * nArg == 1. 252 */ 253 assert(argc <= 1); 254 255 if (argv[0] != NULL) 256 lower = utf8textprep(argv[0], U8_TEXTPREP_TOLOWER); 257 258 out: 259 (void) sqlite_set_result_string(context, lower, -1); 260 free(lower); 261 } 262 static 263 void 264 upper_utf8Func(sqlite_func *context, int argc, const char **argv) 265 { 266 char *upper = NULL; 267 268 /* 269 * SQLite functions can take many arguments, but this function 270 * uses only one, and we call sqlite_create_function() with 271 * nArg == 1. 272 */ 273 assert(argc <= 1); 274 275 if (argv[0] != NULL) 276 upper = utf8textprep(argv[0], U8_TEXTPREP_TOUPPER); 277 278 out: 279 (void) sqlite_set_result_string(context, upper, -1); 280 free(upper); 281 } 282 283 /* 284 ** Implementation of the IFNULL(), NVL(), and COALESCE() functions. 285 ** All three do the same thing. They return the first non-NULL 286 ** argument. 287 */ 288 static void ifnullFunc(sqlite_func *context, int argc, const char **argv){ 289 int i; 290 for(i=0; i<argc; i++){ 291 if( argv[i] ){ 292 sqlite_set_result_string(context, argv[i], -1); 293 break; 294 } 295 } 296 } 297 298 /* 299 ** Implementation of random(). Return a random integer. 300 */ 301 static void randomFunc(sqlite_func *context, int argc, const char **argv){ 302 int r; 303 sqliteRandomness(sizeof(r), &r); 304 sqlite_set_result_int(context, r); 305 } 306 307 /* 308 ** Implementation of the last_insert_rowid() SQL function. The return 309 ** value is the same as the sqlite_last_insert_rowid() API function. 310 */ 311 static void last_insert_rowid(sqlite_func *context, int arg, const char **argv){ 312 sqlite *db = sqlite_user_data(context); 313 sqlite_set_result_int(context, sqlite_last_insert_rowid(db)); 314 } 315 316 /* 317 ** Implementation of the change_count() SQL function. The return 318 ** value is the same as the sqlite_changes() API function. 319 */ 320 static void change_count(sqlite_func *context, int arg, const char **argv){ 321 sqlite *db = sqlite_user_data(context); 322 sqlite_set_result_int(context, sqlite_changes(db)); 323 } 324 325 /* 326 ** Implementation of the last_statement_change_count() SQL function. The 327 ** return value is the same as the sqlite_last_statement_changes() API function. 328 */ 329 static void last_statement_change_count(sqlite_func *context, int arg, 330 const char **argv){ 331 sqlite *db = sqlite_user_data(context); 332 sqlite_set_result_int(context, sqlite_last_statement_changes(db)); 333 } 334 335 /* 336 ** Implementation of the like() SQL function. This function implements 337 ** the build-in LIKE operator. The first argument to the function is the 338 ** string and the second argument is the pattern. So, the SQL statements: 339 ** 340 ** A LIKE B 341 ** 342 ** is implemented as like(A,B). 343 */ 344 static void likeFunc(sqlite_func *context, int arg, const char **argv){ 345 if( argv[0]==0 || argv[1]==0 ) return; 346 sqlite_set_result_int(context, 347 sqliteLikeCompare((const unsigned char*)argv[0], 348 (const unsigned char*)argv[1])); 349 } 350 351 /* 352 ** Implementation of the glob() SQL function. This function implements 353 ** the build-in GLOB operator. The first argument to the function is the 354 ** string and the second argument is the pattern. So, the SQL statements: 355 ** 356 ** A GLOB B 357 ** 358 ** is implemented as glob(A,B). 359 */ 360 static void globFunc(sqlite_func *context, int arg, const char **argv){ 361 if( argv[0]==0 || argv[1]==0 ) return; 362 sqlite_set_result_int(context, 363 sqliteGlobCompare((const unsigned char*)argv[0], 364 (const unsigned char*)argv[1])); 365 } 366 367 /* 368 ** Implementation of the NULLIF(x,y) function. The result is the first 369 ** argument if the arguments are different. The result is NULL if the 370 ** arguments are equal to each other. 371 */ 372 static void nullifFunc(sqlite_func *context, int argc, const char **argv){ 373 if( argv[0]!=0 && sqliteCompare(argv[0],argv[1])!=0 ){ 374 sqlite_set_result_string(context, argv[0], -1); 375 } 376 } 377 378 /* 379 ** Implementation of the VERSION(*) function. The result is the version 380 ** of the SQLite library that is running. 381 */ 382 static void versionFunc(sqlite_func *context, int argc, const char **argv){ 383 sqlite_set_result_string(context, sqlite_version, -1); 384 } 385 386 /* 387 ** EXPERIMENTAL - This is not an official function. The interface may 388 ** change. This function may disappear. Do not write code that depends 389 ** on this function. 390 ** 391 ** Implementation of the QUOTE() function. This function takes a single 392 ** argument. If the argument is numeric, the return value is the same as 393 ** the argument. If the argument is NULL, the return value is the string 394 ** "NULL". Otherwise, the argument is enclosed in single quotes with 395 ** single-quote escapes. 396 */ 397 static void quoteFunc(sqlite_func *context, int argc, const char **argv){ 398 if( argc<1 ) return; 399 if( argv[0]==0 ){ 400 sqlite_set_result_string(context, "NULL", 4); 401 }else if( sqliteIsNumber(argv[0]) ){ 402 sqlite_set_result_string(context, argv[0], -1); 403 }else{ 404 int i,j,n; 405 char *z; 406 for(i=n=0; argv[0][i]; i++){ if( argv[0][i]=='\'' ) n++; } 407 z = sqliteMalloc( i+n+3 ); 408 if( z==0 ) return; 409 z[0] = '\''; 410 for(i=0, j=1; argv[0][i]; i++){ 411 z[j++] = argv[0][i]; 412 if( argv[0][i]=='\'' ){ 413 z[j++] = '\''; 414 } 415 } 416 z[j++] = '\''; 417 z[j] = 0; 418 sqlite_set_result_string(context, z, j); 419 sqliteFree(z); 420 } 421 } 422 423 #ifdef SQLITE_SOUNDEX 424 /* 425 ** Compute the soundex encoding of a word. 426 */ 427 static void soundexFunc(sqlite_func *context, int argc, const char **argv){ 428 char zResult[8]; 429 const char *zIn; 430 int i, j; 431 static const unsigned char iCode[] = { 432 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 433 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 434 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 435 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 436 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0, 437 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0, 438 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0, 439 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0, 440 }; 441 assert( argc==1 ); 442 zIn = argv[0]; 443 for(i=0; zIn[i] && !isalpha(zIn[i]); i++){} 444 if( zIn[i] ){ 445 zResult[0] = toupper(zIn[i]); 446 for(j=1; j<4 && zIn[i]; i++){ 447 int code = iCode[zIn[i]&0x7f]; 448 if( code>0 ){ 449 zResult[j++] = code + '0'; 450 } 451 } 452 while( j<4 ){ 453 zResult[j++] = '0'; 454 } 455 zResult[j] = 0; 456 sqlite_set_result_string(context, zResult, 4); 457 }else{ 458 sqlite_set_result_string(context, "?000", 4); 459 } 460 } 461 #endif 462 463 #ifdef SQLITE_TEST 464 /* 465 ** This function generates a string of random characters. Used for 466 ** generating test data. 467 */ 468 static void randStr(sqlite_func *context, int argc, const char **argv){ 469 static const unsigned char zSrc[] = 470 "abcdefghijklmnopqrstuvwxyz" 471 "ABCDEFGHIJKLMNOPQRSTUVWXYZ" 472 "0123456789" 473 ".-!,:*^+=_|?/<> "; 474 int iMin, iMax, n, r, i; 475 unsigned char zBuf[1000]; 476 if( argc>=1 ){ 477 iMin = atoi(argv[0]); 478 if( iMin<0 ) iMin = 0; 479 if( iMin>=sizeof(zBuf) ) iMin = sizeof(zBuf)-1; 480 }else{ 481 iMin = 1; 482 } 483 if( argc>=2 ){ 484 iMax = atoi(argv[1]); 485 if( iMax<iMin ) iMax = iMin; 486 if( iMax>=sizeof(zBuf) ) iMax = sizeof(zBuf)-1; 487 }else{ 488 iMax = 50; 489 } 490 n = iMin; 491 if( iMax>iMin ){ 492 sqliteRandomness(sizeof(r), &r); 493 r &= 0x7fffffff; 494 n += r%(iMax + 1 - iMin); 495 } 496 assert( n<sizeof(zBuf) ); 497 sqliteRandomness(n, zBuf); 498 for(i=0; i<n; i++){ 499 zBuf[i] = zSrc[zBuf[i]%(sizeof(zSrc)-1)]; 500 } 501 zBuf[n] = 0; 502 sqlite_set_result_string(context, zBuf, n); 503 } 504 #endif 505 506 /* 507 ** An instance of the following structure holds the context of a 508 ** sum() or avg() aggregate computation. 509 */ 510 typedef struct SumCtx SumCtx; 511 struct SumCtx { 512 double sum; /* Sum of terms */ 513 int cnt; /* Number of elements summed */ 514 }; 515 516 /* 517 ** Routines used to compute the sum or average. 518 */ 519 static void sumStep(sqlite_func *context, int argc, const char **argv){ 520 SumCtx *p; 521 if( argc<1 ) return; 522 p = sqlite_aggregate_context(context, sizeof(*p)); 523 if( p && argv[0] ){ 524 p->sum += sqliteAtoF(argv[0], 0); 525 p->cnt++; 526 } 527 } 528 static void sumFinalize(sqlite_func *context){ 529 SumCtx *p; 530 p = sqlite_aggregate_context(context, sizeof(*p)); 531 sqlite_set_result_double(context, p ? p->sum : 0.0); 532 } 533 static void avgFinalize(sqlite_func *context){ 534 SumCtx *p; 535 p = sqlite_aggregate_context(context, sizeof(*p)); 536 if( p && p->cnt>0 ){ 537 sqlite_set_result_double(context, p->sum/(double)p->cnt); 538 } 539 } 540 541 /* 542 ** An instance of the following structure holds the context of a 543 ** variance or standard deviation computation. 544 */ 545 typedef struct StdDevCtx StdDevCtx; 546 struct StdDevCtx { 547 double sum; /* Sum of terms */ 548 double sum2; /* Sum of the squares of terms */ 549 int cnt; /* Number of terms counted */ 550 }; 551 552 #if 0 /* Omit because math library is required */ 553 /* 554 ** Routines used to compute the standard deviation as an aggregate. 555 */ 556 static void stdDevStep(sqlite_func *context, int argc, const char **argv){ 557 StdDevCtx *p; 558 double x; 559 if( argc<1 ) return; 560 p = sqlite_aggregate_context(context, sizeof(*p)); 561 if( p && argv[0] ){ 562 x = sqliteAtoF(argv[0], 0); 563 p->sum += x; 564 p->sum2 += x*x; 565 p->cnt++; 566 } 567 } 568 static void stdDevFinalize(sqlite_func *context){ 569 double rN = sqlite_aggregate_count(context); 570 StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p)); 571 if( p && p->cnt>1 ){ 572 double rCnt = cnt; 573 sqlite_set_result_double(context, 574 sqrt((p->sum2 - p->sum*p->sum/rCnt)/(rCnt-1.0))); 575 } 576 } 577 #endif 578 579 /* 580 ** The following structure keeps track of state information for the 581 ** count() aggregate function. 582 */ 583 typedef struct CountCtx CountCtx; 584 struct CountCtx { 585 int n; 586 }; 587 588 /* 589 ** Routines to implement the count() aggregate function. 590 */ 591 static void countStep(sqlite_func *context, int argc, const char **argv){ 592 CountCtx *p; 593 p = sqlite_aggregate_context(context, sizeof(*p)); 594 if( (argc==0 || argv[0]) && p ){ 595 p->n++; 596 } 597 } 598 static void countFinalize(sqlite_func *context){ 599 CountCtx *p; 600 p = sqlite_aggregate_context(context, sizeof(*p)); 601 sqlite_set_result_int(context, p ? p->n : 0); 602 } 603 604 /* 605 ** This function tracks state information for the min() and max() 606 ** aggregate functions. 607 */ 608 typedef struct MinMaxCtx MinMaxCtx; 609 struct MinMaxCtx { 610 char *z; /* The best so far */ 611 char zBuf[28]; /* Space that can be used for storage */ 612 }; 613 614 /* 615 ** Routines to implement min() and max() aggregate functions. 616 */ 617 static void minmaxStep(sqlite_func *context, int argc, const char **argv){ 618 MinMaxCtx *p; 619 int (*xCompare)(const char*, const char*); 620 int mask; /* 0 for min() or 0xffffffff for max() */ 621 622 assert( argc==2 ); 623 if( argv[0]==0 ) return; /* Ignore NULL values */ 624 if( argv[1][0]=='n' ){ 625 xCompare = sqliteCompare; 626 }else{ 627 xCompare = strcmp; 628 } 629 mask = (int)sqlite_user_data(context); 630 assert( mask==0 || mask==-1 ); 631 p = sqlite_aggregate_context(context, sizeof(*p)); 632 if( p==0 || argc<1 ) return; 633 if( p->z==0 || (xCompare(argv[0],p->z)^mask)<0 ){ 634 int len; 635 if( p->zBuf[0] ){ 636 sqliteFree(p->z); 637 } 638 len = strlen(argv[0]); 639 if( len < sizeof(p->zBuf)-1 ){ 640 p->z = &p->zBuf[1]; 641 p->zBuf[0] = 0; 642 }else{ 643 p->z = sqliteMalloc( len+1 ); 644 p->zBuf[0] = 1; 645 if( p->z==0 ) return; 646 } 647 strcpy(p->z, argv[0]); 648 } 649 } 650 static void minMaxFinalize(sqlite_func *context){ 651 MinMaxCtx *p; 652 p = sqlite_aggregate_context(context, sizeof(*p)); 653 if( p && p->z && p->zBuf[0]<2 ){ 654 sqlite_set_result_string(context, p->z, strlen(p->z)); 655 } 656 if( p && p->zBuf[0] ){ 657 sqliteFree(p->z); 658 } 659 } 660 661 /* 662 ** This function registered all of the above C functions as SQL 663 ** functions. This should be the only routine in this file with 664 ** external linkage. 665 */ 666 void sqliteRegisterBuiltinFunctions(sqlite *db){ 667 static struct { 668 char *zName; 669 signed char nArg; 670 signed char dataType; 671 u8 argType; /* 0: none. 1: db 2: (-1) */ 672 void (*xFunc)(sqlite_func*,int,const char**); 673 } aFuncs[] = { 674 { "min", -1, SQLITE_ARGS, 0, minmaxFunc }, 675 { "min", 0, 0, 0, 0 }, 676 { "max", -1, SQLITE_ARGS, 2, minmaxFunc }, 677 { "max", 0, 0, 2, 0 }, 678 { "typeof", 1, SQLITE_TEXT, 0, typeofFunc }, 679 { "length", 1, SQLITE_NUMERIC, 0, lengthFunc }, 680 { "substr", 3, SQLITE_TEXT, 0, substrFunc }, 681 { "abs", 1, SQLITE_NUMERIC, 0, absFunc }, 682 { "round", 1, SQLITE_NUMERIC, 0, roundFunc }, 683 { "round", 2, SQLITE_NUMERIC, 0, roundFunc }, 684 { "upper", 1, SQLITE_TEXT, 0, upperFunc }, 685 { "lower", 1, SQLITE_TEXT, 0, lowerFunc }, 686 { "lower_utf8", 1, SQLITE_TEXT, 0, lower_utf8Func }, 687 { "upper_utf8", 1, SQLITE_TEXT, 0, upper_utf8Func }, 688 { "coalesce", -1, SQLITE_ARGS, 0, ifnullFunc }, 689 { "coalesce", 0, 0, 0, 0 }, 690 { "coalesce", 1, 0, 0, 0 }, 691 { "ifnull", 2, SQLITE_ARGS, 0, ifnullFunc }, 692 { "random", -1, SQLITE_NUMERIC, 0, randomFunc }, 693 { "like", 2, SQLITE_NUMERIC, 0, likeFunc }, 694 { "glob", 2, SQLITE_NUMERIC, 0, globFunc }, 695 { "nullif", 2, SQLITE_ARGS, 0, nullifFunc }, 696 { "sqlite_version",0,SQLITE_TEXT, 0, versionFunc }, 697 { "quote", 1, SQLITE_ARGS, 0, quoteFunc }, 698 { "last_insert_rowid", 0, SQLITE_NUMERIC, 1, last_insert_rowid }, 699 { "change_count", 0, SQLITE_NUMERIC, 1, change_count }, 700 { "last_statement_change_count", 701 0, SQLITE_NUMERIC, 1, last_statement_change_count }, 702 #ifdef SQLITE_SOUNDEX 703 { "soundex", 1, SQLITE_TEXT, 0, soundexFunc}, 704 #endif 705 #ifdef SQLITE_TEST 706 { "randstr", 2, SQLITE_TEXT, 0, randStr }, 707 #endif 708 }; 709 static struct { 710 char *zName; 711 signed char nArg; 712 signed char dataType; 713 u8 argType; 714 void (*xStep)(sqlite_func*,int,const char**); 715 void (*xFinalize)(sqlite_func*); 716 } aAggs[] = { 717 { "min", 1, 0, 0, minmaxStep, minMaxFinalize }, 718 { "max", 1, 0, 2, minmaxStep, minMaxFinalize }, 719 { "sum", 1, SQLITE_NUMERIC, 0, sumStep, sumFinalize }, 720 { "avg", 1, SQLITE_NUMERIC, 0, sumStep, avgFinalize }, 721 { "count", 0, SQLITE_NUMERIC, 0, countStep, countFinalize }, 722 { "count", 1, SQLITE_NUMERIC, 0, countStep, countFinalize }, 723 #if 0 724 { "stddev", 1, SQLITE_NUMERIC, 0, stdDevStep, stdDevFinalize }, 725 #endif 726 }; 727 static const char *azTypeFuncs[] = { "min", "max", "typeof" }; 728 int i; 729 730 for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ 731 void *pArg; 732 switch( aFuncs[i].argType ){ 733 case 0: pArg = 0; break; 734 case 1: pArg = db; break; 735 case 2: pArg = (void*)(-1); break; 736 } 737 sqlite_create_function(db, aFuncs[i].zName, 738 aFuncs[i].nArg, aFuncs[i].xFunc, pArg); 739 if( aFuncs[i].xFunc ){ 740 sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType); 741 } 742 } 743 for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){ 744 void *pArg; 745 switch( aAggs[i].argType ){ 746 case 0: pArg = 0; break; 747 case 1: pArg = db; break; 748 case 2: pArg = (void*)(-1); break; 749 } 750 sqlite_create_aggregate(db, aAggs[i].zName, 751 aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, pArg); 752 sqlite_function_type(db, aAggs[i].zName, aAggs[i].dataType); 753 } 754 for(i=0; i<sizeof(azTypeFuncs)/sizeof(azTypeFuncs[0]); i++){ 755 int n = strlen(azTypeFuncs[i]); 756 FuncDef *p = sqliteHashFind(&db->aFunc, azTypeFuncs[i], n); 757 while( p ){ 758 p->includeTypes = 1; 759 p = p->pNext; 760 } 761 } 762 sqliteRegisterDateTimeFunctions(db); 763 } 764