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.196 2008/07/28 19:34:53 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 = sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_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, i64 nByte){

   247   char *z;

   248   if( nByte>sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_LENGTH] ){

   249     sqlite3_result_error_toobig(context);

   250     z = 0;

   251   }else{

   252     z = sqlite3Malloc(nByte);

   253     if( !z && nByte>0 ){

   254       sqlite3_result_error_nomem(context);

   255     }

   256   }

   257   return z;

   258 }

   259 

   260 /*

   261 ** Implementation of the upper() and lower() SQL functions.

   262 */

   263 static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){

   264   char *z1;

   265   const char *z2;

   266   int i, n;

   267   if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return;

   268   z2 = (char*)sqlite3_value_text(argv[0]);

   269   n = sqlite3_value_bytes(argv[0]);

   270   /* Verify that the call to _bytes() does not invalidate the _text() pointer */

   271   assert( z2==(char*)sqlite3_value_text(argv[0]) );

   272   if( z2 ){

   273     z1 = contextMalloc(context, ((i64)n)+1);

   274     if( z1 ){

   275       memcpy(z1, z2, n+1);

   276       for(i=0; z1[i]; i++){

   277         z1[i] = toupper(z1[i]);

   278       }

   279       sqlite3_result_text(context, z1, -1, sqlite3_free);

   280     }

   281   }

   282 }

   283 static void lowerFunc(sqlite3_context *context, int argc, sqlite3_value **argv){

   284   char *z1;

   285   const char *z2;

   286   int i, n;

   287   if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return;

   288   z2 = (char*)sqlite3_value_text(argv[0]);

   289   n = sqlite3_value_bytes(argv[0]);

   290   /* Verify that the call to _bytes() does not invalidate the _text() pointer */

   291   assert( z2==(char*)sqlite3_value_text(argv[0]) );

   292   if( z2 ){

   293     z1 = contextMalloc(context, ((i64)n)+1);

   294     if( z1 ){

   295       memcpy(z1, z2, n+1);

   296       for(i=0; z1[i]; i++){

   297         z1[i] = tolower(z1[i]);

   298       }

   299       sqlite3_result_text(context, z1, -1, sqlite3_free);

   300     }

   301   }

   302 }

   303 

   304 /*

   305 ** Implementation of the IFNULL(), NVL(), and COALESCE() functions.  

   306 ** All three do the same thing.  They return the first non-NULL

   307 ** argument.

   308 */

   309 static void ifnullFunc(

   310   sqlite3_context *context,

   311   int argc,

   312   sqlite3_value **argv

   313 ){

   314   int i;

   315   for(i=0; i<argc; i++){

   316     if( SQLITE_NULL!=sqlite3_value_type(argv[i]) ){

   317       sqlite3_result_value(context, argv[i]);

   318       break;

   319     }

   320   }

   321 }

   322 

   323 /*

   324 ** Implementation of random().  Return a random integer.  

   325 */

   326 static void randomFunc(

   327   sqlite3_context *context,

   328   int argc,

   329   sqlite3_value **argv

   330 ){

   331   sqlite_int64 r;

   332   sqlite3_randomness(sizeof(r), &r);

   333   if( (r<<1)==0 ) r = 0;  /* Prevent 0x8000.... as the result so that we */

   334                           /* can always do abs() of the result */

   335   sqlite3_result_int64(context, r);

   336 }

   337 

   338 /*

   339 ** Implementation of randomblob(N).  Return a random blob

   340 ** that is N bytes long.

   341 */

   342 static void randomBlob(

   343   sqlite3_context *context,

   344   int argc,

   345   sqlite3_value **argv

   346 ){

   347   int n;

   348   unsigned char *p;

   349   assert( argc==1 );

   350   n = sqlite3_value_int(argv[0]);

   351   if( n<1 ){

   352     n = 1;

   353   }

   354   p = contextMalloc(context, n);

   355   if( p ){

   356     sqlite3_randomness(n, p);

   357     sqlite3_result_blob(context, (char*)p, n, sqlite3_free);

   358   }

   359 }

   360 

   361 /*

   362 ** Implementation of the last_insert_rowid() SQL function.  The return

   363 ** value is the same as the sqlite3_last_insert_rowid() API function.

   364 */

   365 static void last_insert_rowid(

   366   sqlite3_context *context, 

   367   int arg, 

   368   sqlite3_value **argv

   369 ){

   370   sqlite3 *db = sqlite3_context_db_handle(context);

   371   sqlite3_result_int64(context, sqlite3_last_insert_rowid(db));

   372 }

   373 

   374 /*

   375 ** Implementation of the changes() SQL function.  The return value is the

   376 ** same as the sqlite3_changes() API function.

   377 */

   378 static void changes(

   379   sqlite3_context *context,

   380   int arg,

   381   sqlite3_value **argv

   382 ){

   383   sqlite3 *db = sqlite3_context_db_handle(context);

   384   sqlite3_result_int(context, sqlite3_changes(db));

   385 }

   386 

   387 /*

   388 ** Implementation of the total_changes() SQL function.  The return value is

   389 ** the same as the sqlite3_total_changes() API function.

   390 */

   391 static void total_changes(

   392   sqlite3_context *context,

   393   int arg,

   394   sqlite3_value **argv

   395 ){

   396   sqlite3 *db = sqlite3_context_db_handle(context);

   397   sqlite3_result_int(context, sqlite3_total_changes(db));

   398 }

   399 

   400 /*

   401 ** A structure defining how to do GLOB-style comparisons.

   402 */

   403 struct compareInfo {

   404   u8 matchAll;

   405   u8 matchOne;

   406   u8 matchSet;

   407   u8 noCase;

   408 };

   409 

   410 /*

   411 ** For LIKE and GLOB matching on EBCDIC machines, assume that every

   412 ** character is exactly one byte in size.  Also, all characters are

   413 ** able to participate in upper-case-to-lower-case mappings in EBCDIC

   414 ** whereas only characters less than 0x80 do in ASCII.

   415 */

   416 #if defined(SQLITE_EBCDIC)

   417 # define sqlite3Utf8Read(A,B,C)  (*(A++))

   418 # define GlogUpperToLower(A)     A = sqlite3UpperToLower[A]

   419 #else

   420 # define GlogUpperToLower(A)     if( A<0x80 ){ A = sqlite3UpperToLower[A]; }

   421 #endif

   422 

   423 static const struct compareInfo globInfo = { '*', '?', '[', 0 };

   424 /* The correct SQL-92 behavior is for the LIKE operator to ignore

   425 ** case.  Thus  'a' LIKE 'A' would be true. */

   426 static const struct compareInfo likeInfoNorm = { '%', '_',   0, 1 };

   427 /* If SQLITE_CASE_SENSITIVE_LIKE is defined, then the LIKE operator

   428 ** is case sensitive causing 'a' LIKE 'A' to be false */

   429 static const struct compareInfo likeInfoAlt = { '%', '_',   0, 0 };

   430 

   431 /*

   432 ** Compare two UTF-8 strings for equality where the first string can

   433 ** potentially be a "glob" expression.  Return true (1) if they

   434 ** are the same and false (0) if they are different.

   435 **

   436 ** Globbing rules:

   437 **

   438 **      '*'       Matches any sequence of zero or more characters.

   439 **

   440 **      '?'       Matches exactly one character.

   441 **

   442 **     [...]      Matches one character from the enclosed list of

   443 **                characters.

   444 **

   445 **     [^...]     Matches one character not in the enclosed list.

   446 **

   447 ** With the [...] and [^...] matching, a ']' character can be included

   448 ** in the list by making it the first character after '[' or '^'.  A

   449 ** range of characters can be specified using '-'.  Example:

   450 ** "[a-z]" matches any single lower-case letter.  To match a '-', make

   451 ** it the last character in the list.

   452 **

   453 ** This routine is usually quick, but can be N**2 in the worst case.

   454 **

   455 ** Hints: to match '*' or '?', put them in "[]".  Like this:

   456 **

   457 **         abc[*]xyz        Matches "abc*xyz" only

   458 */

   459 static int patternCompare(

   460   const u8 *zPattern,              /* The glob pattern */

   461   const u8 *zString,               /* The string to compare against the glob */

   462   const struct compareInfo *pInfo, /* Information about how to do the compare */

   463   const int esc                    /* The escape character */

   464 ){

   465   int c, c2;

   466   int invert;

   467   int seen;

   468   u8 matchOne = pInfo->matchOne;

   469   u8 matchAll = pInfo->matchAll;

   470   u8 matchSet = pInfo->matchSet;

   471   u8 noCase = pInfo->noCase; 

   472   int prevEscape = 0;     /* True if the previous character was 'escape' */

   473 

   474   while( (c = sqlite3Utf8Read(zPattern,0,&zPattern))!=0 ){

   475     if( !prevEscape && c==matchAll ){

   476       while( (c=sqlite3Utf8Read(zPattern,0,&zPattern)) == matchAll

   477                || c == matchOne ){

   478         if( c==matchOne && sqlite3Utf8Read(zString, 0, &zString)==0 ){

   479           return 0;

   480         }

   481       }

   482       if( c==0 ){

   483         return 1;

   484       }else if( c==esc ){

   485         c = sqlite3Utf8Read(zPattern, 0, &zPattern);

   486         if( c==0 ){

   487           return 0;

   488         }

   489       }else if( c==matchSet ){

   490         assert( esc==0 );         /* This is GLOB, not LIKE */

   491         assert( matchSet<0x80 );  /* '[' is a single-byte character */

   492         while( *zString && patternCompare(&zPattern[-1],zString,pInfo,esc)==0 ){

   493           SQLITE_SKIP_UTF8(zString);

   494         }

   495         return *zString!=0;

   496       }

   497       while( (c2 = sqlite3Utf8Read(zString,0,&zString))!=0 ){

   498         if( noCase ){

   499           GlogUpperToLower(c2);

   500           GlogUpperToLower(c);

   501           while( c2 != 0 && c2 != c ){

   502             c2 = sqlite3Utf8Read(zString, 0, &zString);

   503             GlogUpperToLower(c2);

   504           }

   505         }else{

   506           while( c2 != 0 && c2 != c ){

   507             c2 = sqlite3Utf8Read(zString, 0, &zString);

   508           }

   509         }

   510         if( c2==0 ) return 0;

   511         if( patternCompare(zPattern,zString,pInfo,esc) ) return 1;

   512       }

   513       return 0;

   514     }else if( !prevEscape && c==matchOne ){

   515       if( sqlite3Utf8Read(zString, 0, &zString)==0 ){

   516         return 0;

   517       }

   518     }else if( c==matchSet ){

   519       int prior_c = 0;

   520       assert( esc==0 );    /* This only occurs for GLOB, not LIKE */

   521       seen = 0;

   522       invert = 0;

   523       c = sqlite3Utf8Read(zString, 0, &zString);

   524       if( c==0 ) return 0;

   525       c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);

   526       if( c2=='^' ){

   527         invert = 1;

   528         c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);

   529       }

   530       if( c2==']' ){

   531         if( c==']' ) seen = 1;

   532         c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);

   533       }

   534       while( c2 && c2!=']' ){

   535         if( c2=='-' && zPattern[0]!=']' && zPattern[0]!=0 && prior_c>0 ){

   536           c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);

   537           if( c>=prior_c && c<=c2 ) seen = 1;

   538           prior_c = 0;

   539         }else{

   540           if( c==c2 ){

   541             seen = 1;

   542           }

   543           prior_c = c2;

   544         }

   545         c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);

   546       }

   547       if( c2==0 || (seen ^ invert)==0 ){

   548         return 0;

   549       }

   550     }else if( esc==c && !prevEscape ){

   551       prevEscape = 1;

   552     }else{

   553       c2 = sqlite3Utf8Read(zString, 0, &zString);

   554       if( noCase ){

   555         GlogUpperToLower(c);

   556         GlogUpperToLower(c2);

   557       }

   558       if( c!=c2 ){

   559         return 0;

   560       }

   561       prevEscape = 0;

   562     }

   563   }

   564   return *zString==0;

   565 }

   566 

   567 /*

   568 ** Count the number of times that the LIKE operator (or GLOB which is

   569 ** just a variation of LIKE) gets called.  This is used for testing

   570 ** only.

   571 */

   572 #ifdef SQLITE_TEST

   573 int sqlite3_like_count = 0;

   574 #endif

   575 

   576 

   577 /*

   578 ** Implementation of the like() SQL function.  This function implements

   579 ** the build-in LIKE operator.  The first argument to the function is the

   580 ** pattern and the second argument is the string.  So, the SQL statements:

   581 **

   582 **       A LIKE B

   583 **

   584 ** is implemented as like(B,A).

   585 **

   586 ** This same function (with a different compareInfo structure) computes

   587 ** the GLOB operator.

   588 */

   589 static void likeFunc(

   590   sqlite3_context *context, 

   591   int argc, 

   592   sqlite3_value **argv

   593 ){

   594   const unsigned char *zA, *zB;

   595   int escape = 0;

   596   sqlite3 *db = sqlite3_context_db_handle(context);

   597 

   598   zB = sqlite3_value_text(argv[0]);

   599   zA = sqlite3_value_text(argv[1]);

   600 

   601   /* Limit the length of the LIKE or GLOB pattern to avoid problems

   602   ** of deep recursion and N*N behavior in patternCompare().

   603   */

   604   if( sqlite3_value_bytes(argv[0]) >

   605         db->aLimit[SQLITE_LIMIT_LIKE_PATTERN_LENGTH] ){

   606     sqlite3_result_error(context, "LIKE or GLOB pattern too complex", -1);

   607     return;

   608   }

   609   assert( zB==sqlite3_value_text(argv[0]) );  /* Encoding did not change */

   610 

   611   if( argc==3 ){

   612     /* The escape character string must consist of a single UTF-8 character.

   613     ** Otherwise, return an error.

   614     */

   615     const unsigned char *zEsc = sqlite3_value_text(argv[2]);

   616     if( zEsc==0 ) return;

   617     if( sqlite3Utf8CharLen((char*)zEsc, -1)!=1 ){

   618       sqlite3_result_error(context, 

   619           "ESCAPE expression must be a single character", -1);

   620       return;

   621     }

   622     escape = sqlite3Utf8Read(zEsc, 0, &zEsc);

   623   }

   624   if( zA && zB ){

   625     struct compareInfo *pInfo = sqlite3_user_data(context);

   626 #ifdef SQLITE_TEST

   627     sqlite3_like_count++;

   628 #endif

   629     

   630     sqlite3_result_int(context, patternCompare(zB, zA, pInfo, escape));

   631   }

   632 }

   633 

   634 /*

   635 ** Implementation of the NULLIF(x,y) function.  The result is the first

   636 ** argument if the arguments are different.  The result is NULL if the

   637 ** arguments are equal to each other.

   638 */

   639 static void nullifFunc(

   640   sqlite3_context *context,

   641   int argc,

   642   sqlite3_value **argv

   643 ){

   644   CollSeq *pColl = sqlite3GetFuncCollSeq(context);

   645   if( sqlite3MemCompare(argv[0], argv[1], pColl)!=0 ){

   646     sqlite3_result_value(context, argv[0]);

   647   }

   648 }

   649 

   650 /*

   651 ** Implementation of the VERSION(*) function.  The result is the version

   652 ** of the SQLite library that is running.

   653 */

   654 static void versionFunc(

   655   sqlite3_context *context,

   656   int argc,

   657   sqlite3_value **argv

   658 ){

   659   sqlite3_result_text(context, sqlite3_version, -1, SQLITE_STATIC);

   660 }

   661 

   662 /* Array for converting from half-bytes (nybbles) into ASCII hex

   663 ** digits. */

   664 static const char hexdigits[] = {

   665   '0', '1', '2', '3', '4', '5', '6', '7',

   666   '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' 

   667 };

   668 

   669 /*

   670 ** EXPERIMENTAL - This is not an official function.  The interface may

   671 ** change.  This function may disappear.  Do not write code that depends

   672 ** on this function.

   673 **

   674 ** Implementation of the QUOTE() function.  This function takes a single

   675 ** argument.  If the argument is numeric, the return value is the same as

   676 ** the argument.  If the argument is NULL, the return value is the string

   677 ** "NULL".  Otherwise, the argument is enclosed in single quotes with

   678 ** single-quote escapes.

   679 */

   680 static void quoteFunc(sqlite3_context *context, int argc, sqlite3_value **argv){

   681   if( argc<1 ) return;

   682   switch( sqlite3_value_type(argv[0]) ){

   683     case SQLITE_NULL: {

   684       sqlite3_result_text(context, "NULL", 4, SQLITE_STATIC);

   685       break;

   686     }

   687     case SQLITE_INTEGER:

   688     case SQLITE_FLOAT: {

   689       sqlite3_result_value(context, argv[0]);

   690       break;

   691     }

   692     case SQLITE_BLOB: {

   693       char *zText = 0;

   694       char const *zBlob = sqlite3_value_blob(argv[0]);

   695       int nBlob = sqlite3_value_bytes(argv[0]);

   696       assert( zBlob==sqlite3_value_blob(argv[0]) ); /* No encoding change */

   697       zText = (char *)contextMalloc(context, (2*(i64)nBlob)+4); 

   698       if( zText ){

   699         int i;

   700         for(i=0; i<nBlob; i++){

   701           zText[(i*2)+2] = hexdigits[(zBlob[i]>>4)&0x0F];

   702           zText[(i*2)+3] = hexdigits[(zBlob[i])&0x0F];

   703         }

   704         zText[(nBlob*2)+2] = '\'';

   705         zText[(nBlob*2)+3] = '\0';

   706         zText[0] = 'X';

   707         zText[1] = '\'';

   708         sqlite3_result_text(context, zText, -1, SQLITE_TRANSIENT);

   709         sqlite3_free(zText);

   710       }

   711       break;

   712     }

   713     case SQLITE_TEXT: {

   714       int i,j;

   715       u64 n;

   716       const unsigned char *zArg = sqlite3_value_text(argv[0]);

   717       char *z;

   718 

   719       if( zArg==0 ) return;

   720       for(i=0, n=0; zArg[i]; i++){ if( zArg[i]=='\'' ) n++; }

   721       z = contextMalloc(context, ((i64)i)+((i64)n)+3);

   722       if( z ){

   723         z[0] = '\'';

   724         for(i=0, j=1; zArg[i]; i++){

   725           z[j++] = zArg[i];

   726           if( zArg[i]=='\'' ){

   727             z[j++] = '\'';

   728           }

   729         }

   730         z[j++] = '\'';

   731         z[j] = 0;

   732         sqlite3_result_text(context, z, j, sqlite3_free);

   733       }

   734     }

   735   }

   736 }

   737 

   738 /*

   739 ** The hex() function.  Interpret the argument as a blob.  Return

   740 ** a hexadecimal rendering as text.

   741 */

   742 static void hexFunc(

   743   sqlite3_context *context,

   744   int argc,

   745   sqlite3_value **argv

   746 ){

   747   int i, n;

   748   const unsigned char *pBlob;

   749   char *zHex, *z;

   750   assert( argc==1 );

   751   pBlob = sqlite3_value_blob(argv[0]);

   752   n = sqlite3_value_bytes(argv[0]);

   753   assert( pBlob==sqlite3_value_blob(argv[0]) );  /* No encoding change */

   754   z = zHex = contextMalloc(context, ((i64)n)*2 + 1);

   755   if( zHex ){

   756     for(i=0; i<n; i++, pBlob++){

   757       unsigned char c = *pBlob;

   758       *(z++) = hexdigits[(c>>4)&0xf];

   759       *(z++) = hexdigits[c&0xf];

   760     }

   761     *z = 0;

   762     sqlite3_result_text(context, zHex, n*2, sqlite3_free);

   763   }

   764 }

   765 

   766 /*

   767 ** The zeroblob(N) function returns a zero-filled blob of size N bytes.

   768 */

   769 static void zeroblobFunc(

   770   sqlite3_context *context,

   771   int argc,

   772   sqlite3_value **argv

   773 ){

   774   i64 n;

   775   assert( argc==1 );

   776   n = sqlite3_value_int64(argv[0]);

   777   if( n>SQLITE_MAX_LENGTH ){

   778     sqlite3_result_error_toobig(context);

   779   }else{

   780     sqlite3_result_zeroblob(context, n);

   781   }

   782 }

   783 

   784 /*

   785 ** The replace() function.  Three arguments are all strings: call

   786 ** them A, B, and C. The result is also a string which is derived

   787 ** from A by replacing every occurance of B with C.  The match

   788 ** must be exact.  Collating sequences are not used.

   789 */

   790 static void replaceFunc(

   791   sqlite3_context *context,

   792   int argc,

   793   sqlite3_value **argv

   794 ){

   795   const unsigned char *zStr;        /* The input string A */

   796   const unsigned char *zPattern;    /* The pattern string B */

   797   const unsigned char *zRep;        /* The replacement string C */

   798   unsigned char *zOut;              /* The output */

   799   int nStr;                /* Size of zStr */

   800   int nPattern;            /* Size of zPattern */

   801   int nRep;                /* Size of zRep */

   802   i64 nOut;                /* Maximum size of zOut */

   803   int loopLimit;           /* Last zStr[] that might match zPattern[] */

   804   int i, j;                /* Loop counters */

   805 

   806   assert( argc==3 );

   807   zStr = sqlite3_value_text(argv[0]);

   808   if( zStr==0 ) return;

   809   nStr = sqlite3_value_bytes(argv[0]);

   810   assert( zStr==sqlite3_value_text(argv[0]) );  /* No encoding change */

   811   zPattern = sqlite3_value_text(argv[1]);

   812   if( zPattern==0 || zPattern[0]==0 ) return;

   813   nPattern = sqlite3_value_bytes(argv[1]);

   814   assert( zPattern==sqlite3_value_text(argv[1]) );  /* No encoding change */

   815   zRep = sqlite3_value_text(argv[2]);

   816   if( zRep==0 ) return;

   817   nRep = sqlite3_value_bytes(argv[2]);

   818   assert( zRep==sqlite3_value_text(argv[2]) );

   819   nOut = nStr + 1;

   820   assert( nOut<SQLITE_MAX_LENGTH );

   821   zOut = contextMalloc(context, (i64)nOut);

   822   if( zOut==0 ){

   823     return;

   824   }

   825   loopLimit = nStr - nPattern;  

   826   for(i=j=0; i<=loopLimit; i++){

   827     if( zStr[i]!=zPattern[0] || memcmp(&zStr[i], zPattern, nPattern) ){

   828       zOut[j++] = zStr[i];

   829     }else{

   830       u8 *zOld;

   831       sqlite3 *db = sqlite3_context_db_handle(context);

   832       nOut += nRep - nPattern;

   833       if( nOut>=db->aLimit[SQLITE_LIMIT_LENGTH] ){

   834         sqlite3_result_error_toobig(context);

   835         sqlite3DbFree(db, zOut);

   836         return;

   837       }

   838       zOld = zOut;

   839       zOut = sqlite3_realloc(zOut, (int)nOut);

   840       if( zOut==0 ){

   841         sqlite3_result_error_nomem(context);

   842         sqlite3DbFree(db, zOld);

   843         return;

   844       }

   845       memcpy(&zOut[j], zRep, nRep);

   846       j += nRep;

   847       i += nPattern-1;

   848     }

   849   }

   850   assert( j+nStr-i+1==nOut );

   851   memcpy(&zOut[j], &zStr[i], nStr-i);

   852   j += nStr - i;

   853   assert( j<=nOut );

   854   zOut[j] = 0;

   855   sqlite3_result_text(context, (char*)zOut, j, sqlite3_free);

   856 }

   857 

   858 /*

   859 ** Implementation of the TRIM(), LTRIM(), and RTRIM() functions.

   860 ** The userdata is 0x1 for left trim, 0x2 for right trim, 0x3 for both.

   861 */

   862 static void trimFunc(

   863   sqlite3_context *context,

   864   int argc,

   865   sqlite3_value **argv

   866 ){

   867   const unsigned char *zIn;         /* Input string */

   868   const unsigned char *zCharSet;    /* Set of characters to trim */

   869   int nIn;                          /* Number of bytes in input */

   870   int flags;                        /* 1: trimleft  2: trimright  3: trim */

   871   int i;                            /* Loop counter */

   872   unsigned char *aLen;              /* Length of each character in zCharSet */

   873   unsigned char **azChar;           /* Individual characters in zCharSet */

   874   int nChar;                        /* Number of characters in zCharSet */

   875 

   876   if( sqlite3_value_type(argv[0])==SQLITE_NULL ){

   877     return;

   878   }

   879   zIn = sqlite3_value_text(argv[0]);

   880   if( zIn==0 ) return;

   881   nIn = sqlite3_value_bytes(argv[0]);

   882   assert( zIn==sqlite3_value_text(argv[0]) );

   883   if( argc==1 ){

   884     static const unsigned char lenOne[] = { 1 };

   885     static const unsigned char *azOne[] = { (u8*)" " };

   886     nChar = 1;

   887     aLen = (u8*)lenOne;

   888     azChar = (unsigned char **)azOne;

   889     zCharSet = 0;

   890   }else if( (zCharSet = sqlite3_value_text(argv[1]))==0 ){

   891     return;

   892   }else{

   893     const unsigned char *z;

   894     for(z=zCharSet, nChar=0; *z; nChar++){

   895       SQLITE_SKIP_UTF8(z);

   896     }

   897     if( nChar>0 ){

   898       azChar = contextMalloc(context, ((i64)nChar)*(sizeof(char*)+1));

   899       if( azChar==0 ){

   900         return;

   901       }

   902       aLen = (unsigned char*)&azChar[nChar];

   903       for(z=zCharSet, nChar=0; *z; nChar++){

   904         azChar[nChar] = (unsigned char *)z;

   905         SQLITE_SKIP_UTF8(z);

   906         aLen[nChar] = z - azChar[nChar];

   907       }

   908     }

   909   }

   910   if( nChar>0 ){

   911     flags = SQLITE_PTR_TO_INT(sqlite3_user_data(context));

   912     if( flags & 1 ){

   913       while( nIn>0 ){

   914         int len;

   915         for(i=0; i<nChar; i++){

   916           len = aLen[i];

   917           if( memcmp(zIn, azChar[i], len)==0 ) break;

   918         }

   919         if( i>=nChar ) break;

   920         zIn += len;

   921         nIn -= len;

   922       }

   923     }

   924     if( flags & 2 ){

   925       while( nIn>0 ){

   926         int len;

   927         for(i=0; i<nChar; i++){

   928           len = aLen[i];

   929           if( len<=nIn && memcmp(&zIn[nIn-len],azChar[i],len)==0 ) break;

   930         }

   931         if( i>=nChar ) break;

   932         nIn -= len;

   933       }

   934     }

   935     if( zCharSet ){

   936       sqlite3_free(azChar);

   937     }

   938   }

   939   sqlite3_result_text(context, (char*)zIn, nIn, SQLITE_TRANSIENT);

   940 }

   941 

   942 #ifdef SQLITE_SOUNDEX

   943 /*

   944 ** Compute the soundex encoding of a word.

   945 */

   946 static void soundexFunc(

   947   sqlite3_context *context,

   948   int argc,

   949   sqlite3_value **argv

   950 ){

   951   char zResult[8];

   952   const u8 *zIn;

   953   int i, j;

   954   static const unsigned char iCode[] = {

   955     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,

   956     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,

   957     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,

   958     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,

   959     0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,

   960     1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,

   961     0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,

   962     1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,

   963   };

   964   assert( argc==1 );

   965   zIn = (u8*)sqlite3_value_text(argv[0]);

   966   if( zIn==0 ) zIn = (u8*)"";

   967   for(i=0; zIn[i] && !isalpha(zIn[i]); i++){}

   968   if( zIn[i] ){

   969     u8 prevcode = iCode[zIn[i]&0x7f];

   970     zResult[0] = toupper(zIn[i]);

   971     for(j=1; j<4 && zIn[i]; i++){

   972       int code = iCode[zIn[i]&0x7f];

   973       if( code>0 ){

   974         if( code!=prevcode ){

   975           prevcode = code;

   976           zResult[j++] = code + '0';

   977         }

   978       }else{

   979         prevcode = 0;

   980       }

   981     }

   982     while( j<4 ){

   983       zResult[j++] = '0';

   984     }

   985     zResult[j] = 0;

   986     sqlite3_result_text(context, zResult, 4, SQLITE_TRANSIENT);

   987   }else{

   988     sqlite3_result_text(context, "?000", 4, SQLITE_STATIC);

   989   }

   990 }

   991 #endif

   992 

   993 #ifndef SQLITE_OMIT_LOAD_EXTENSION

   994 /*

   995 ** A function that loads a shared-library extension then returns NULL.

   996 */

   997 static void loadExt(sqlite3_context *context, int argc, sqlite3_value **argv){

   998   const char *zFile = (const char *)sqlite3_value_text(argv[0]);

   999   const char *zProc;

  1000   sqlite3 *db = sqlite3_context_db_handle(context);

  1001   char *zErrMsg = 0;

  1002 

  1003   if( argc==2 ){

  1004     zProc = (const char *)sqlite3_value_text(argv[1]);

  1005   }else{

  1006     zProc = 0;

  1007   }

  1008   if( zFile && sqlite3_load_extension(db, zFile, zProc, &zErrMsg) ){

  1009     sqlite3_result_error(context, zErrMsg, -1);

  1010     sqlite3_free(zErrMsg);

  1011   }

  1012 }

  1013 #endif

  1014 

  1015 

  1016 /*

  1017 ** An instance of the following structure holds the context of a

  1018 ** sum() or avg() aggregate computation.

  1019 */

  1020 typedef struct SumCtx SumCtx;

  1021 struct SumCtx {

  1022   double rSum;      /* Floating point sum */

  1023   i64 iSum;         /* Integer sum */   

  1024   i64 cnt;          /* Number of elements summed */

  1025   u8 overflow;      /* True if integer overflow seen */

  1026   u8 approx;        /* True if non-integer value was input to the sum */

  1027 };

  1028 

  1029 /*

  1030 ** Routines used to compute the sum, average, and total.

  1031 **

  1032 ** The SUM() function follows the (broken) SQL standard which means

  1033 ** that it returns NULL if it sums over no inputs.  TOTAL returns

  1034 ** 0.0 in that case.  In addition, TOTAL always returns a float where

  1035 ** SUM might return an integer if it never encounters a floating point

  1036 ** value.  TOTAL never fails, but SUM might through an exception if

  1037 ** it overflows an integer.

  1038 */

  1039 static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){

  1040   SumCtx *p;

  1041   int type;

  1042   assert( argc==1 );

  1043   p = sqlite3_aggregate_context(context, sizeof(*p));

  1044   type = sqlite3_value_numeric_type(argv[0]);

  1045   if( p && type!=SQLITE_NULL ){

  1046     p->cnt++;

  1047     if( type==SQLITE_INTEGER ){

  1048       i64 v = sqlite3_value_int64(argv[0]);

  1049       p->rSum += v;

  1050       if( (p->approx|p->overflow)==0 ){

  1051         i64 iNewSum = p->iSum + v;

  1052         int s1 = p->iSum >> (sizeof(i64)*8-1);

  1053         int s2 = v       >> (sizeof(i64)*8-1);

  1054         int s3 = iNewSum >> (sizeof(i64)*8-1);

  1055         p->overflow = (s1&s2&~s3) | (~s1&~s2&s3);

  1056         p->iSum = iNewSum;

  1057       }

  1058     }else{

  1059       p->rSum += sqlite3_value_double(argv[0]);

  1060       p->approx = 1;

  1061     }

  1062   }

  1063 }

  1064 static void sumFinalize(sqlite3_context *context){

  1065   SumCtx *p;

  1066   p = sqlite3_aggregate_context(context, 0);

  1067   if( p && p->cnt>0 ){

  1068     if( p->overflow ){

  1069       sqlite3_result_error(context,"integer overflow",-1);

  1070     }else if( p->approx ){

  1071       sqlite3_result_double(context, p->rSum);

  1072     }else{

  1073       sqlite3_result_int64(context, p->iSum);

  1074     }

  1075   }

  1076 }

  1077 static void avgFinalize(sqlite3_context *context){

  1078   SumCtx *p;

  1079   p = sqlite3_aggregate_context(context, 0);

  1080   if( p && p->cnt>0 ){

  1081     sqlite3_result_double(context, p->rSum/(double)p->cnt);

  1082   }

  1083 }

  1084 static void totalFinalize(sqlite3_context *context){

  1085   SumCtx *p;

  1086   p = sqlite3_aggregate_context(context, 0);

  1087   sqlite3_result_double(context, p ? p->rSum : 0.0);

  1088 }

  1089 

  1090 /*

  1091 ** The following structure keeps track of state information for the

  1092 ** count() aggregate function.

  1093 */

  1094 typedef struct CountCtx CountCtx;

  1095 struct CountCtx {

  1096   i64 n;

  1097 };

  1098 

  1099 /*

  1100 ** Routines to implement the count() aggregate function.

  1101 */

  1102 static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){

  1103   CountCtx *p;

  1104   p = sqlite3_aggregate_context(context, sizeof(*p));

  1105   if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){

  1106     p->n++;

  1107   }

  1108 }   

  1109 static void countFinalize(sqlite3_context *context){

  1110   CountCtx *p;

  1111   p = sqlite3_aggregate_context(context, 0);

  1112   sqlite3_result_int64(context, p ? p->n : 0);

  1113 }

  1114 

  1115 /*

  1116 ** Routines to implement min() and max() aggregate functions.

  1117 */

  1118 static void minmaxStep(sqlite3_context *context, int argc, sqlite3_value **argv){

  1119   Mem *pArg  = (Mem *)argv[0];

  1120   Mem *pBest;

  1121 

  1122   if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;

  1123   pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));

  1124   if( !pBest ) return;

  1125 

  1126   if( pBest->flags ){

  1127     int max;

  1128     int cmp;

  1129     CollSeq *pColl = sqlite3GetFuncCollSeq(context);

  1130     /* This step function is used for both the min() and max() aggregates,

  1131     ** the only difference between the two being that the sense of the

  1132     ** comparison is inverted. For the max() aggregate, the

  1133     ** sqlite3_user_data() function returns (void *)-1. For min() it

  1134     ** returns (void *)db, where db is the sqlite3* database pointer.