sqlite3/func.c

   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$
  20 */
  21 #include "sqliteInt.h"
  22 #include <ctype.h>
  23 #include <math.h>
  24 #include <stdlib.h>
  25 #include <assert.h>
  26 #include "vdbeInt.h"
  27 #include "os.h"
  28
  29 static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){
  30   return context->pColl;
  31 }
  32
  33 /*
  34 ** Implementation of the non-aggregate min() and max() functions
  35 */
  36 static void minmaxFunc(
  37   sqlite3_context *context,
  38   int argc,
  39   sqlite3_value **argv
  40 ){
  41   int i;
  42   int mask;    /* 0 for min() or 0xffffffff for max() */
  43   int iBest;
  44   CollSeq *pColl;
  45
  46   if( argc==0 ) return;
  47   mask = sqlite3_user_data(context)==0 ? 0 : -1;
  48   pColl = sqlite3GetFuncCollSeq(context);
  49   assert( pColl );
  50   assert( mask==-1 || mask==0 );
  51   iBest = 0;
  52   if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  53   for(i=1; i<argc; i++){
  54     if( sqlite3_value_type(argv[i])==SQLITE_NULL ) return;
  55     if( (sqlite3MemCompare(argv[iBest], argv[i], pColl)^mask)>=0 ){
  56       iBest = i;
  57     }
  58   }
  59   sqlite3_result_value(context, argv[iBest]);
  60 }
  61
  62 /*
  63 ** Return the type of the argument.
  64 */
  65 static void typeofFunc(
  66   sqlite3_context *context,
  67   int argc,
  68   sqlite3_value **argv
  69 ){
  70   const char *z = 0;
  71   switch( sqlite3_value_type(argv[0]) ){
  72     case SQLITE_NULL:    z = "null";    break;
  73     case SQLITE_INTEGER: z = "integer"; break;
  74     case SQLITE_TEXT:    z = "text";    break;
  75     case SQLITE_FLOAT:   z = "real";    break;
  76     case SQLITE_BLOB:    z = "blob";    break;
  77   }
  78   sqlite3_result_text(context, z, -1, SQLITE_STATIC);
  79 }
  80
  81 /*
  82 ** Implementation of the length() function
  83 */
  84 static void lengthFunc(
  85   sqlite3_context *context,
  86   int argc,
  87   sqlite3_value **argv
  88 ){
  89   int len;
  90
  91   assert( argc==1 );
  92   switch( sqlite3_value_type(argv[0]) ){
  93     case SQLITE_BLOB:
  94     case SQLITE_INTEGER:
  95     case SQLITE_FLOAT: {
  96       sqlite3_result_int(context, sqlite3_value_bytes(argv[0]));
  97       break;
  98     }
  99     case SQLITE_TEXT: {
 100       const char *z = sqlite3_value_text(argv[0]);
 101       for(len=0; *z; z++){ if( (0xc0&*z)!=0x80 ) len++; }
 102       sqlite3_result_int(context, len);
 103       break;
 104     }
 105     default: {
 106       sqlite3_result_null(context);
 107       break;
 108     }
 109   }
 110 }
 111
 112 /*
 113 ** Implementation of the abs() function
 114 */
 115 static void absFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
 116   assert( argc==1 );
 117   switch( sqlite3_value_type(argv[0]) ){
 118     case SQLITE_INTEGER: {
 119       i64 iVal = sqlite3_value_int64(argv[0]);
 120       if( iVal<0 ) iVal = iVal * -1;
 121       sqlite3_result_int64(context, iVal);
 122       break;
 123     }
 124     case SQLITE_NULL: {
 125       sqlite3_result_null(context);
 126       break;
 127     }
 128     default: {
 129       double rVal = sqlite3_value_double(argv[0]);
 130       if( rVal<0 ) rVal = rVal * -1.0;
 131       sqlite3_result_double(context, rVal);
 132       break;
 133     }
 134   }
 135 }
 136
 137 /*
 138 ** Implementation of the substr() function
 139 */
 140 static void substrFunc(
 141   sqlite3_context *context,
 142   int argc,
 143   sqlite3_value **argv
 144 ){
 145   const char *z;
 146   const char *z2;
 147   int i;
 148   int p1, p2, len;
 149
 150   assert( argc==3 );
 151   z = sqlite3_value_text(argv[0]);
 152   if( z==0 ) return;
 153   p1 = sqlite3_value_int(argv[1]);
 154   p2 = sqlite3_value_int(argv[2]);
 155   for(len=0, z2=z; *z2; z2++){ if( (0xc0&*z2)!=0x80 ) len++; }
 156   if( p1<0 ){
 157     p1 += len;
 158     if( p1<0 ){
 159       p2 += p1;
 160       p1 = 0;
 161     }
 162   }else if( p1>0 ){
 163     p1--;
 164   }
 165   if( p1+p2>len ){
 166     p2 = len-p1;
 167   }
 168   for(i=0; i<p1 && z[i]; i++){
 169     if( (z[i]&0xc0)==0x80 ) p1++;
 170   }
 171   while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p1++; }
 172   for(; i<p1+p2 && z[i]; i++){
 173     if( (z[i]&0xc0)==0x80 ) p2++;
 174   }
 175   while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p2++; }
 176   if( p2<0 ) p2 = 0;
 177   sqlite3_result_text(context, &z[p1], p2, SQLITE_TRANSIENT);
 178 }
 179
 180 /*
 181 ** Implementation of the round() function
 182 */
 183 static void roundFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
 184   int n = 0;
 185   double r;
 186   char zBuf[100];
 187   assert( argc==1 || argc==2 );
 188   if( argc==2 ){
 189     if( SQLITE_NULL==sqlite3_value_type(argv[1]) ) return;
 190     n = sqlite3_value_int(argv[1]);
 191     if( n>30 ) n = 30;
 192     if( n<0 ) n = 0;
 193   }
 194   if( SQLITE_NULL==sqlite3_value_type(argv[0]) ) return;
 195   r = sqlite3_value_double(argv[0]);
 196   sprintf(zBuf,"%.*f",n,r);
 197   sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
 198 }
 199
 200 /*
 201 ** Implementation of the upper() and lower() SQL functions.
 202 */
 203 static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
 204   unsigned char *z;
 205   int i;
 206   if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return;
 207   z = sqliteMalloc(sqlite3_value_bytes(argv[0])+1);
 208   if( z==0 ) return;
 209   strcpy(z, sqlite3_value_text(argv[0]));
 210   for(i=0; z[i]; i++){
 211     z[i] = toupper(z[i]);
 212   }
 213   sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT);
 214   sqliteFree(z);
 215 }
 216 static void lowerFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
 217   unsigned char *z;
 218   int i;
 219   if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return;
 220   z = sqliteMalloc(sqlite3_value_bytes(argv[0])+1);
 221   if( z==0 ) return;
 222   strcpy(z, sqlite3_value_text(argv[0]));
 223   for(i=0; z[i]; i++){
 224     z[i] = tolower(z[i]);
 225   }
 226   sqlite3_result_text(context, z, -1, SQLITE_TRANSIENT);
 227   sqliteFree(z);
 228 }
 229
 230 /*
 231 ** Implementation of the IFNULL(), NVL(), and COALESCE() functions.
 232 ** All three do the same thing.  They return the first non-NULL
 233 ** argument.
 234 */
 235 static void ifnullFunc(
 236   sqlite3_context *context,
 237   int argc,
 238   sqlite3_value **argv
 239 ){
 240   int i;
 241   for(i=0; i<argc; i++){
 242     if( SQLITE_NULL!=sqlite3_value_type(argv[i]) ){
 243       sqlite3_result_value(context, argv[i]);
 244       break;
 245     }
 246   }
 247 }
 248
 249 /*
 250 ** Implementation of random().  Return a random integer.
 251 */
 252 static void randomFunc(
 253   sqlite3_context *context,
 254   int argc,
 255   sqlite3_value **argv
 256 ){
 257   int r;
 258   sqlite3Randomness(sizeof(r), &r);
 259   sqlite3_result_int(context, r);
 260 }
 261
 262 /*
 263 ** Implementation of the last_insert_rowid() SQL function.  The return
 264 ** value is the same as the sqlite3_last_insert_rowid() API function.
 265 */
 266 static void last_insert_rowid(
 267   sqlite3_context *context,
 268   int arg,
 269   sqlite3_value **argv
 270 ){
 271   sqlite3 *db = sqlite3_user_data(context);
 272   sqlite3_result_int64(context, sqlite3_last_insert_rowid(db));
 273 }
 274
 275 /*
 276 ** Implementation of the changes() SQL function.  The return value is the
 277 ** same as the sqlite3_changes() API function.
 278 */
 279 static void changes(
 280   sqlite3_context *context,
 281   int arg,
 282   sqlite3_value **argv
 283 ){
 284   sqlite3 *db = sqlite3_user_data(context);
 285   sqlite3_result_int(context, sqlite3_changes(db));
 286 }
 287
 288 /*
 289 ** Implementation of the total_changes() SQL function.  The return value is
 290 ** the same as the sqlite3_total_changes() API function.
 291 */
 292 static void total_changes(
 293   sqlite3_context *context,
 294   int arg,
 295   sqlite3_value **argv
 296 ){
 297   sqlite3 *db = sqlite3_user_data(context);
 298   sqlite3_result_int(context, sqlite3_total_changes(db));
 299 }
 300
 301 /*
 302 ** A structure defining how to do GLOB-style comparisons.
 303 */
 304 struct compareInfo {
 305   u8 matchAll;
 306   u8 matchOne;
 307   u8 matchSet;
 308   u8 noCase;
 309 };
 310 static const struct compareInfo globInfo = { '*', '?', '[', 0 };
 311 static const struct compareInfo likeInfo = { '%', '_',   0, 1 };
 312
 313 /*
 314 ** X is a pointer to the first byte of a UTF-8 character.  Increment
 315 ** X so that it points to the next character.  This only works right
 316 ** if X points to a well-formed UTF-8 string.
 317 */
 318 #define sqliteNextChar(X)  while( (0xc0&*++(X))==0x80 ){}
 319 #define sqliteCharVal(X)   sqlite3ReadUtf8(X)
 320
 321
 322 /*
 323 ** Compare two UTF-8 strings for equality where the first string can
 324 ** potentially be a "glob" expression.  Return true (1) if they
 325 ** are the same and false (0) if they are different.
 326 **
 327 ** Globbing rules:
 328 **
 329 **      '*'       Matches any sequence of zero or more characters.
 330 **
 331 **      '?'       Matches exactly one character.
 332 **
 333 **     [...]      Matches one character from the enclosed list of
 334 **                characters.
 335 **
 336 **     [^...]     Matches one character not in the enclosed list.
 337 **
 338 ** With the [...] and [^...] matching, a ']' character can be included
 339 ** in the list by making it the first character after '[' or '^'.  A
 340 ** range of characters can be specified using '-'.  Example:
 341 ** "[a-z]" matches any single lower-case letter.  To match a '-', make
 342 ** it the last character in the list.
 343 **
 344 ** This routine is usually quick, but can be N**2 in the worst case.
 345 **
 346 ** Hints: to match '*' or '?', put them in "[]".  Like this:
 347 **
 348 **         abc[*]xyz        Matches "abc*xyz" only
 349 */
 350 static int patternCompare(
 351   const u8 *zPattern,              /* The glob pattern */
 352   const u8 *zString,               /* The string to compare against the glob */
 353   const struct compareInfo *pInfo, /* Information about how to do the compare */
 354   const int esc                    /* The escape character */
 355 ){
 356   register int c;
 357   int invert;
 358   int seen;
 359   int c2;
 360   u8 matchOne = pInfo->matchOne;
 361   u8 matchAll = pInfo->matchAll;
 362   u8 matchSet = pInfo->matchSet;
 363   u8 noCase = pInfo->noCase;
 364   int prevEscape = 0;     /* True if the previous character was 'escape' */
 365
 366   while( (c = *zPattern)!=0 ){
 367     if( !prevEscape && c==matchAll ){
 368       while( (c=zPattern[1]) == matchAll || c == matchOne ){
 369         if( c==matchOne ){
 370           if( *zString==0 ) return 0;
 371           sqliteNextChar(zString);
 372         }
 373         zPattern++;
 374       }
 375       if( c && esc && sqlite3ReadUtf8(&zPattern[1])==esc ){
 376         u8 const *zTemp = &zPattern[1];
 377         sqliteNextChar(zTemp);
 378         c = *zTemp;
 379       }
 380       if( c==0 ) return 1;
 381       if( c==matchSet ){
 382         assert( esc==0 );   /* This is GLOB, not LIKE */
 383         while( *zString && patternCompare(&zPattern[1],zString,pInfo,esc)==0 ){
 384           sqliteNextChar(zString);
 385         }
 386         return *zString!=0;
 387       }else{
 388         while( (c2 = *zString)!=0 ){
 389           if( noCase ){
 390             c2 = sqlite3UpperToLower[c2];
 391             c = sqlite3UpperToLower[c];
 392             while( c2 != 0 && c2 != c ){ c2 = sqlite3UpperToLower[*++zString]; }
 393           }else{
 394             while( c2 != 0 && c2 != c ){ c2 = *++zString; }
 395           }
 396           if( c2==0 ) return 0;
 397           if( patternCompare(&zPattern[1],zString,pInfo,esc) ) return 1;
 398           sqliteNextChar(zString);
 399         }
 400         return 0;
 401       }
 402     }else if( !prevEscape && c==matchOne ){
 403       if( *zString==0 ) return 0;
 404       sqliteNextChar(zString);
 405       zPattern++;
 406     }else if( c==matchSet ){
 407       int prior_c = 0;
 408       assert( esc==0 );    /* This only occurs for GLOB, not LIKE */
 409       seen = 0;
 410       invert = 0;
 411       c = sqliteCharVal(zString);
 412       if( c==0 ) return 0;
 413       c2 = *++zPattern;
 414       if( c2=='^' ){ invert = 1; c2 = *++zPattern; }
 415       if( c2==']' ){
 416         if( c==']' ) seen = 1;
 417         c2 = *++zPattern;
 418       }
 419       while( (c2 = sqliteCharVal(zPattern))!=0 && c2!=']' ){
 420         if( c2=='-' && zPattern[1]!=']' && zPattern[1]!=0 && prior_c>0 ){
 421           zPattern++;
 422           c2 = sqliteCharVal(zPattern);
 423           if( c>=prior_c && c<=c2 ) seen = 1;
 424           prior_c = 0;
 425         }else if( c==c2 ){
 426           seen = 1;
 427           prior_c = c2;
 428         }else{
 429           prior_c = c2;
 430         }
 431         sqliteNextChar(zPattern);
 432       }
 433       if( c2==0 || (seen ^ invert)==0 ) return 0;
 434       sqliteNextChar(zString);
 435       zPattern++;
 436     }else if( esc && !prevEscape && sqlite3ReadUtf8(zPattern)==esc){
 437       prevEscape = 1;
 438       sqliteNextChar(zPattern);
 439     }else{
 440       if( noCase ){
 441         if( sqlite3UpperToLower[c] != sqlite3UpperToLower[*zString] ) return 0;
 442       }else{
 443         if( c != *zString ) return 0;
 444       }
 445       zPattern++;
 446       zString++;
 447       prevEscape = 0;
 448     }
 449   }
 450   return *zString==0;
 451 }
 452
 453
 454 /*
 455 ** Implementation of the like() SQL function.  This function implements
 456 ** the build-in LIKE operator.  The first argument to the function is the
 457 ** pattern and the second argument is the string.  So, the SQL statements:
 458 **
 459 **       A LIKE B
 460 **
 461 ** is implemented as like(B,A).
 462 **
 463 ** If the pointer retrieved by via a call to sqlite3_user_data() is
 464 ** not NULL, then this function uses UTF-16. Otherwise UTF-8.
 465 */
 466 static void likeFunc(
 467   sqlite3_context *context,
 468   int argc,
 469   sqlite3_value **argv
 470 ){
 471   const unsigned char *zA = sqlite3_value_text(argv[0]);
 472   const unsigned char *zB = sqlite3_value_text(argv[1]);
 473   int escape = 0;
 474   if( argc==3 ){
 475     /* The escape character string must consist of a single UTF-8 character.
 476     ** Otherwise, return an error.
 477     */
 478     const unsigned char *zEsc = sqlite3_value_text(argv[2]);
 479     if( sqlite3utf8CharLen(zEsc, -1)!=1 ){
 480       sqlite3_result_error(context,
 481           "ESCAPE expression must be a single character", -1);
 482       return;
 483     }
 484     escape = sqlite3ReadUtf8(zEsc);
 485   }
 486   if( zA && zB ){
 487     sqlite3_result_int(context, patternCompare(zA, zB, &likeInfo, escape));
 488   }
 489 }
 490
 491 /*
 492 ** Implementation of the glob() SQL function.  This function implements
 493 ** the build-in GLOB operator.  The first argument to the function is the
 494 ** string and the second argument is the pattern.  So, the SQL statements:
 495 **
 496 **       A GLOB B
 497 **
 498 ** is implemented as glob(B,A).
 499 */
 500 static void globFunc(sqlite3_context *context, int arg, sqlite3_value **argv){
 501   const unsigned char *zA = sqlite3_value_text(argv[0]);
 502   const unsigned char *zB = sqlite3_value_text(argv[1]);
 503   if( zA && zB ){
 504     sqlite3_result_int(context, patternCompare(zA, zB, &globInfo, 0));
 505   }
 506 }
 507
 508 /*
 509 ** Implementation of the NULLIF(x,y) function.  The result is the first
 510 ** argument if the arguments are different.  The result is NULL if the
 511 ** arguments are equal to each other.
 512 */
 513 static void nullifFunc(
 514   sqlite3_context *context,
 515   int argc,
 516   sqlite3_value **argv
 517 ){
 518   CollSeq *pColl = sqlite3GetFuncCollSeq(context);
 519   if( sqlite3MemCompare(argv[0], argv[1], pColl)!=0 ){
 520     sqlite3_result_value(context, argv[0]);
 521   }
 522 }
 523
 524 /*
 525 ** Implementation of the VERSION(*) function.  The result is the version
 526 ** of the SQLite library that is running.
 527 */
 528 static void versionFunc(
 529   sqlite3_context *context,
 530   int argc,
 531   sqlite3_value **argv
 532 ){
 533   sqlite3_result_text(context, sqlite3_version, -1, SQLITE_STATIC);
 534 }
 535
 536
 537 /*
 538 ** EXPERIMENTAL - This is not an official function.  The interface may
 539 ** change.  This function may disappear.  Do not write code that depends
 540 ** on this function.
 541 **
 542 ** Implementation of the QUOTE() function.  This function takes a single
 543 ** argument.  If the argument is numeric, the return value is the same as
 544 ** the argument.  If the argument is NULL, the return value is the string
 545 ** "NULL".  Otherwise, the argument is enclosed in single quotes with
 546 ** single-quote escapes.
 547 */
 548 static void quoteFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
 549   if( argc<1 ) return;
 550   switch( sqlite3_value_type(argv[0]) ){
 551     case SQLITE_NULL: {
 552       sqlite3_result_text(context, "NULL", 4, SQLITE_STATIC);
 553       break;
 554     }
 555     case SQLITE_INTEGER:
 556     case SQLITE_FLOAT: {
 557       sqlite3_result_value(context, argv[0]);
 558       break;
 559     }
 560     case SQLITE_BLOB: {
 561       static const char hexdigits[] = {
 562         '0', '1', '2', '3', '4', '5', '6', '7',
 563         '8', '9', 'A', 'B', 'C', 'D', 'E', 'F'
 564       };
 565       char *zText = 0;
 566       int nBlob = sqlite3_value_bytes(argv[0]);
 567       char const *zBlob = sqlite3_value_blob(argv[0]);
 568
 569       zText = (char *)sqliteMalloc((2*nBlob)+4);
 570       if( !zText ){
 571         sqlite3_result_error(context, "out of memory", -1);
 572       }else{
 573         int i;
 574         for(i=0; i<nBlob; i++){
 575           zText[(i*2)+2] = hexdigits[(zBlob[i]>>4)&0x0F];
 576           zText[(i*2)+3] = hexdigits[(zBlob[i])&0x0F];
 577         }
 578         zText[(nBlob*2)+2] = '\'';
 579         zText[(nBlob*2)+3] = '\0';
 580         zText[0] = 'X';
 581         zText[1] = '\'';
 582         sqlite3_result_text(context, zText, -1, SQLITE_TRANSIENT);
 583         sqliteFree(zText);
 584       }
 585       break;
 586     }
 587     case SQLITE_TEXT: {
 588       int i,j,n;
 589       const char *zArg = sqlite3_value_text(argv[0]);
 590       char *z;
 591
 592       for(i=n=0; zArg[i]; i++){ if( zArg[i]=='\'' ) n++; }
 593       z = sqliteMalloc( i+n+3 );
 594       if( z==0 ) return;
 595       z[0] = '\'';
 596       for(i=0, j=1; zArg[i]; i++){
 597         z[j++] = zArg[i];
 598         if( zArg[i]=='\'' ){
 599           z[j++] = '\'';
 600         }
 601       }
 602       z[j++] = '\'';
 603       z[j] = 0;
 604       sqlite3_result_text(context, z, j, SQLITE_TRANSIENT);
 605       sqliteFree(z);
 606     }
 607   }
 608 }
 609
 610 #ifdef SQLITE_SOUNDEX
 611 /*
 612 ** Compute the soundex encoding of a word.
 613 */
 614 static void soundexFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
 615   char zResult[8];
 616   const u8 *zIn;
 617   int i, j;
 618   static const unsigned char iCode[] = {
 619     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
 620     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
 621     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
 622     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
 623     0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
 624     1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
 625     0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
 626     1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
 627   };
 628   assert( argc==1 );
 629   zIn = (u8*)sqlite3_value_text(argv[0]);
 630   for(i=0; zIn[i] && !isalpha(zIn[i]); i++){}
 631   if( zIn[i] ){
 632     zResult[0] = toupper(zIn[i]);
 633     for(j=1; j<4 && zIn[i]; i++){
 634       int code = iCode[zIn[i]&0x7f];
 635       if( code>0 ){
 636         zResult[j++] = code + '0';
 637       }
 638     }
 639     while( j<4 ){
 640       zResult[j++] = '0';
 641     }
 642     zResult[j] = 0;
 643     sqlite3_result_text(context, zResult, 4, SQLITE_TRANSIENT);
 644   }else{
 645     sqlite3_result_text(context, "?000", 4, SQLITE_STATIC);
 646   }
 647 }
 648 #endif
 649
 650 #ifdef SQLITE_TEST
 651 /*
 652 ** This function generates a string of random characters.  Used for
 653 ** generating test data.
 654 */
 655 static void randStr(sqlite3_context *context, int argc, sqlite3_value **argv){
 656   static const unsigned char zSrc[] =
 657      "abcdefghijklmnopqrstuvwxyz"
 658      "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
 659      "0123456789"
 660      ".-!,:*^+=_|?/<> ";
 661   int iMin, iMax, n, r, i;
 662   unsigned char zBuf[1000];
 663   if( argc>=1 ){
 664     iMin = sqlite3_value_int(argv[0]);
 665     if( iMin<0 ) iMin = 0;
 666     if( iMin>=sizeof(zBuf) ) iMin = sizeof(zBuf)-1;
 667   }else{
 668     iMin = 1;
 669   }
 670   if( argc>=2 ){
 671     iMax = sqlite3_value_int(argv[1]);
 672     if( iMax<iMin ) iMax = iMin;
 673     if( iMax>=sizeof(zBuf) ) iMax = sizeof(zBuf)-1;
 674   }else{
 675     iMax = 50;
 676   }
 677   n = iMin;
 678   if( iMax>iMin ){
 679     sqlite3Randomness(sizeof(r), &r);
 680     r &= 0x7fffffff;
 681     n += r%(iMax + 1 - iMin);
 682   }
 683   assert( n<sizeof(zBuf) );
 684   sqlite3Randomness(n, zBuf);
 685   for(i=0; i<n; i++){
 686     zBuf[i] = zSrc[zBuf[i]%(sizeof(zSrc)-1)];
 687   }
 688   zBuf[n] = 0;
 689   sqlite3_result_text(context, zBuf, n, SQLITE_TRANSIENT);
 690 }
 691 #endif /* SQLITE_TEST */
 692
 693 #ifdef SQLITE_TEST
 694 /*
 695 ** The following two SQL functions are used to test returning a text
 696 ** result with a destructor. Function 'test_destructor' takes one argument
 697 ** and returns the same argument interpreted as TEXT. A destructor is
 698 ** passed with the sqlite3_result_text() call.
 699 **
 700 ** SQL function 'test_destructor_count' returns the number of outstanding
 701 ** allocations made by 'test_destructor';
 702 **
 703 ** WARNING: Not threadsafe.
 704 */
 705 static int test_destructor_count_var = 0;
 706 static void destructor(void *p){
 707   char *zVal = (char *)p;
 708   assert(zVal);
 709   zVal--;
 710   sqliteFree(zVal);
 711   test_destructor_count_var--;
 712 }
 713 static void test_destructor(
 714   sqlite3_context *pCtx,
 715   int nArg,
 716   sqlite3_value **argv
 717 ){
 718   char *zVal;
 719   int len;
 720   sqlite3 *db = sqlite3_user_data(pCtx);
 721
 722   test_destructor_count_var++;
 723   assert( nArg==1 );
 724   if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
 725   len = sqlite3ValueBytes(argv[0], db->enc);
 726   zVal = sqliteMalloc(len+3);
 727   zVal[len] = 0;
 728   zVal[len-1] = 0;
 729   assert( zVal );
 730   zVal++;
 731   memcpy(zVal, sqlite3ValueText(argv[0], db->enc), len);
 732   if( db->enc==SQLITE_UTF8 ){
 733     sqlite3_result_text(pCtx, zVal, -1, destructor);
 734 #ifndef SQLITE_OMIT_UTF16
 735   }else if( db->enc==SQLITE_UTF16LE ){
 736     sqlite3_result_text16le(pCtx, zVal, -1, destructor);
 737   }else{
 738     sqlite3_result_text16be(pCtx, zVal, -1, destructor);
 739 #endif /* SQLITE_OMIT_UTF16 */
 740   }
 741 }
 742 static void test_destructor_count(
 743   sqlite3_context *pCtx,
 744   int nArg,
 745   sqlite3_value **argv
 746 ){
 747   sqlite3_result_int(pCtx, test_destructor_count_var);
 748 }
 749 #endif /* SQLITE_TEST */
 750
 751 #ifdef SQLITE_TEST
 752 /*
 753 ** Routines for testing the sqlite3_get_auxdata() and sqlite3_set_auxdata()
 754 ** interface.
 755 **
 756 ** The test_auxdata() SQL function attempts to register each of its arguments
 757 ** as auxiliary data.  If there are no prior registrations of aux data for
 758 ** that argument (meaning the argument is not a constant or this is its first
 759 ** call) then the result for that argument is 0.  If there is a prior
 760 ** registration, the result for that argument is 1.  The overall result
 761 ** is the individual argument results separated by spaces.
 762 */
 763 static void free_test_auxdata(void *p) {sqliteFree(p);}
 764 static void test_auxdata(
 765   sqlite3_context *pCtx,
 766   int nArg,
 767   sqlite3_value **argv
 768 ){
 769   int i;
 770   char *zRet = sqliteMalloc(nArg*2);
 771   if( !zRet ) return;
 772   for(i=0; i<nArg; i++){
 773     char const *z = sqlite3_value_text(argv[i]);
 774     if( z ){
 775       char *zAux = sqlite3_get_auxdata(pCtx, i);
 776       if( zAux ){
 777         zRet[i*2] = '1';
 778         if( strcmp(zAux, z) ){
 779           sqlite3_result_error(pCtx, "Auxilary data corruption", -1);
 780           return;
 781         }
 782       }else{
 783         zRet[i*2] = '0';
 784         zAux = sqliteStrDup(z);
 785         sqlite3_set_auxdata(pCtx, i, zAux, free_test_auxdata);
 786       }
 787       zRet[i*2+1] = ' ';
 788     }
 789   }
 790   sqlite3_result_text(pCtx, zRet, 2*nArg-1, free_test_auxdata);
 791 }
 792 #endif /* SQLITE_TEST */
 793
 794 #ifdef SQLITE_TEST
 795 /*
 796 ** A function to test error reporting from user functions. This function
 797 ** returns a copy of it's first argument as an error.
 798 */
 799 static void test_error(
 800   sqlite3_context *pCtx,
 801   int nArg,
 802   sqlite3_value **argv
 803 ){
 804   sqlite3_result_error(pCtx, sqlite3_value_text(argv[0]), 0);
 805 }
 806 #endif /* SQLITE_TEST */
 807
 808 /*
 809 ** An instance of the following structure holds the context of a
 810 ** sum() or avg() aggregate computation.
 811 */
 812 typedef struct SumCtx SumCtx;
 813 struct SumCtx {
 814   double sum;     /* Sum of terms */
 815   int cnt;        /* Number of elements summed */
 816 };
 817
 818 /*
 819 ** Routines used to compute the sum or average.
 820 */
 821 static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){
 822   SumCtx *p;
 823   if( argc<1 ) return;
 824   p = sqlite3_aggregate_context(context, sizeof(*p));
 825   if( p && SQLITE_NULL!=sqlite3_value_type(argv[0]) ){
 826     p->sum += sqlite3_value_double(argv[0]);
 827     p->cnt++;
 828   }
 829 }
 830 static void sumFinalize(sqlite3_context *context){
 831   SumCtx *p;
 832   p = sqlite3_aggregate_context(context, sizeof(*p));
 833   sqlite3_result_double(context, p ? p->sum : 0.0);
 834 }
 835 static void avgFinalize(sqlite3_context *context){
 836   SumCtx *p;
 837   p = sqlite3_aggregate_context(context, sizeof(*p));
 838   if( p && p->cnt>0 ){
 839     sqlite3_result_double(context, p->sum/(double)p->cnt);
 840   }
 841 }
 842
 843 /*
 844 ** An instance of the following structure holds the context of a
 845 ** variance or standard deviation computation.
 846 */
 847 typedef struct StdDevCtx StdDevCtx;
 848 struct StdDevCtx {
 849   double sum;     /* Sum of terms */
 850   double sum2;    /* Sum of the squares of terms */
 851   int cnt;        /* Number of terms counted */
 852 };
 853
 854 /*
 855 ** The following structure keeps track of state information for the
 856 ** count() aggregate function.
 857 */
 858 typedef struct CountCtx CountCtx;
 859 struct CountCtx {
 860   int n;
 861 };
 862
 863 /*
 864 ** Routines to implement the count() aggregate function.
 865 */
 866 static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){
 867   CountCtx *p;
 868   p = sqlite3_aggregate_context(context, sizeof(*p));
 869   if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){
 870     p->n++;
 871   }
 872 }
 873 static void countFinalize(sqlite3_context *context){
 874   CountCtx *p;
 875   p = sqlite3_aggregate_context(context, sizeof(*p));
 876   sqlite3_result_int(context, p ? p->n : 0);
 877 }
 878
 879 /*
 880 ** This function tracks state information for the min() and max()
 881 ** aggregate functions.
 882 */
 883 typedef struct MinMaxCtx MinMaxCtx;
 884 struct MinMaxCtx {
 885   char *z;         /* The best so far */
 886   char zBuf[28];   /* Space that can be used for storage */
 887 };
 888
 889 /*
 890 ** Routines to implement min() and max() aggregate functions.
 891 */
 892 static void minmaxStep(sqlite3_context *context, int argc, sqlite3_value **argv){
 893   Mem *pArg  = (Mem *)argv[0];
 894   Mem *pBest;
 895
 896   if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
 897   pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));
 898   if( !pBest ) return;
 899
 900   if( pBest->flags ){
 901     int max;
 902     int cmp;
 903     CollSeq *pColl = sqlite3GetFuncCollSeq(context);
 904     /* This step function is used for both the min() and max() aggregates,
 905     ** the only difference between the two being that the sense of the
 906     ** comparison is inverted. For the max() aggregate, the
 907     ** sqlite3_user_data() function returns (void *)-1. For min() it
 908     ** returns (void *)db, where db is the sqlite3* database pointer.
 909     ** Therefore the next statement sets variable 'max' to 1 for the max()
 910     ** aggregate, or 0 for min().
 911     */
 912     max = ((sqlite3_user_data(context)==(void *)-1)?1:0);
 913     cmp = sqlite3MemCompare(pBest, pArg, pColl);
 914     if( (max && cmp<0) || (!max && cmp>0) ){
 915       sqlite3VdbeMemCopy(pBest, pArg);
 916     }
 917   }else{
 918     sqlite3VdbeMemCopy(pBest, pArg);
 919   }
 920 }
 921 static void minMaxFinalize(sqlite3_context *context){
 922   sqlite3_value *pRes;
 923   pRes = (sqlite3_value *)sqlite3_aggregate_context(context, sizeof(Mem));
 924   if( pRes->flags ){
 925     sqlite3_result_value(context, pRes);
 926   }
 927   sqlite3VdbeMemRelease(pRes);
 928 }
 929
 930
 931 /*
 932 ** This function registered all of the above C functions as SQL
 933 ** functions.  This should be the only routine in this file with
 934 ** external linkage.
 935 */
 936 void sqlite3RegisterBuiltinFunctions(sqlite3 *db){
 937   static const struct {
 938      char *zName;
 939      signed char nArg;
 940      u8 argType;           /* 0: none.  1: db  2: (-1) */
 941      u8 eTextRep;          /* 1: UTF-16.  0: UTF-8 */
 942      u8 needCollSeq;
 943      void (*xFunc)(sqlite3_context*,int,sqlite3_value **);
 944   } aFuncs[] = {
 945     { "min",               -1, 0, SQLITE_UTF8,    1, minmaxFunc },
 946     { "min",                0, 0, SQLITE_UTF8,    1, 0          },
 947     { "max",               -1, 2, SQLITE_UTF8,    1, minmaxFunc },
 948     { "max",                0, 2, SQLITE_UTF8,    1, 0          },
 949     { "typeof",             1, 0, SQLITE_UTF8,    0, typeofFunc },
 950     { "length",             1, 0, SQLITE_UTF8,    0, lengthFunc },
 951     { "substr",             3, 0, SQLITE_UTF8,    0, substrFunc },
 952 #ifndef SQLITE_OMIT_UTF16
 953     { "substr",             3, 0, SQLITE_UTF16LE, 0, sqlite3utf16Substr },
 954 #endif
 955     { "abs",                1, 0, SQLITE_UTF8,    0, absFunc    },
 956     { "round",              1, 0, SQLITE_UTF8,    0, roundFunc  },
 957     { "round",              2, 0, SQLITE_UTF8,    0, roundFunc  },
 958     { "upper",              1, 0, SQLITE_UTF8,    0, upperFunc  },
 959     { "lower",              1, 0, SQLITE_UTF8,    0, lowerFunc  },
 960     { "coalesce",          -1, 0, SQLITE_UTF8,    0, ifnullFunc },
 961     { "coalesce",           0, 0, SQLITE_UTF8,    0, 0          },
 962     { "coalesce",           1, 0, SQLITE_UTF8,    0, 0          },
 963     { "ifnull",             2, 0, SQLITE_UTF8,    1, ifnullFunc },
 964     { "random",            -1, 0, SQLITE_UTF8,    0, randomFunc },
 965     { "like",               2, 0, SQLITE_UTF8,    0, likeFunc   },
 966     { "like",               3, 0, SQLITE_UTF8,    0, likeFunc   },
 967     { "glob",               2, 0, SQLITE_UTF8,    0, globFunc   },
 968     { "nullif",             2, 0, SQLITE_UTF8,    1, nullifFunc },
 969     { "sqlite_version",     0, 0, SQLITE_UTF8,    0, versionFunc},
 970     { "quote",              1, 0, SQLITE_UTF8,    0, quoteFunc  },
 971     { "last_insert_rowid",  0, 1, SQLITE_UTF8,    0, last_insert_rowid },
 972     { "changes",            0, 1, SQLITE_UTF8,    0, changes    },
 973     { "total_changes",      0, 1, SQLITE_UTF8,    0, total_changes },
 974 #ifdef SQLITE_SOUNDEX
 975     { "soundex",            1, 0, SQLITE_UTF8, 0, soundexFunc},
 976 #endif
 977 #ifdef SQLITE_TEST
 978     { "randstr",               2, 0, SQLITE_UTF8, 0, randStr    },
 979     { "test_destructor",       1, 1, SQLITE_UTF8, 0, test_destructor},
 980     { "test_destructor_count", 0, 0, SQLITE_UTF8, 0, test_destructor_count},
 981     { "test_auxdata",         -1, 0, SQLITE_UTF8, 0, test_auxdata},
 982     { "test_error",            1, 0, SQLITE_UTF8, 0, test_error},
 983 #endif
 984   };
 985   static const struct {
 986     char *zName;
 987     signed char nArg;
 988     u8 argType;
 989     u8 needCollSeq;
 990     void (*xStep)(sqlite3_context*,int,sqlite3_value**);
 991     void (*xFinalize)(sqlite3_context*);
 992   } aAggs[] = {
 993     { "min",    1, 0, 1, minmaxStep,   minMaxFinalize },
 994     { "max",    1, 2, 1, minmaxStep,   minMaxFinalize },
 995     { "sum",    1, 0, 0, sumStep,      sumFinalize    },
 996     { "avg",    1, 0, 0, sumStep,      avgFinalize    },
 997     { "count",  0, 0, 0, countStep,    countFinalize  },
 998     { "count",  1, 0, 0, countStep,    countFinalize  },
 999   };
1000   int i;
1001
1002   for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
1003     void *pArg = 0;
1004     switch( aFuncs[i].argType ){
1005       case 1: pArg = db; break;
1006       case 2: pArg = (void *)(-1); break;
1007     }
1008     sqlite3_create_function(db, aFuncs[i].zName, aFuncs[i].nArg,
1009         aFuncs[i].eTextRep, pArg, aFuncs[i].xFunc, 0, 0);
1010     if( aFuncs[i].needCollSeq ){
1011       FuncDef *pFunc = sqlite3FindFunction(db, aFuncs[i].zName,
1012           strlen(aFuncs[i].zName), aFuncs[i].nArg, aFuncs[i].eTextRep, 0);
1013       if( pFunc && aFuncs[i].needCollSeq ){
1014         pFunc->needCollSeq = 1;
1015       }
1016     }
1017   }
1018 #ifndef SQLITE_OMIT_ALTERTABLE
1019   sqlite3AlterFunctions(db);
1020 #endif
1021   for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
1022     void *pArg = 0;
1023     switch( aAggs[i].argType ){
1024       case 1: pArg = db; break;
1025       case 2: pArg = (void *)(-1); break;
1026     }
1027     sqlite3_create_function(db, aAggs[i].zName, aAggs[i].nArg, SQLITE_UTF8,
1028         pArg, 0, aAggs[i].xStep, aAggs[i].xFinalize);
1029     if( aAggs[i].needCollSeq ){
1030       FuncDef *pFunc = sqlite3FindFunction( db, aAggs[i].zName,
1031           strlen(aAggs[i].zName), aAggs[i].nArg, SQLITE_UTF8, 0);
1032       if( pFunc && aAggs[i].needCollSeq ){
1033         pFunc->needCollSeq = 1;
1034       }
1035     }
1036   }
1037   sqlite3RegisterDateTimeFunctions(db);
1038 #ifdef SQLITE_SSE
1039   {
1040     sqlite3SseFunctions(db);
1041   }
1042 #endif
1043 }