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.203 2008/09/03 17:11:16 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 ** Return the collating function associated with a function.

    29 */

    30 static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){

    31   return context->pColl;

    32 }

    33 

    34 /*

    35 ** Implementation of the non-aggregate min() and max() functions

    36 */

    37 static void minmaxFunc(

    38   sqlite3_context *context,

    39   int argc,

    40   sqlite3_value **argv

    41 ){

    42   int i;

    43   int mask;    /* 0 for min() or 0xffffffff for max() */

    44   int iBest;

    45   CollSeq *pColl;

    46 

    47   if( argc==0 ) return;

    48   mask = sqlite3_user_data(context)==0 ? 0 : -1;

    49   pColl = sqlite3GetFuncCollSeq(context);

    50   assert( pColl );

    51   assert( mask==-1 || mask==0 );

    52   iBest = 0;

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

    54   for(i=1; i<argc; i++){

    55     if( sqlite3_value_type(argv[i])==SQLITE_NULL ) return;

    56     if( (sqlite3MemCompare(argv[iBest], argv[i], pColl)^mask)>=0 ){

    57       iBest = i;

    58     }

    59   }

    60   sqlite3_result_value(context, argv[iBest]);

    61 }

    62 

    63 /*

    64 ** Return the type of the argument.

    65 */

    66 static void typeofFunc(

    67   sqlite3_context *context,

    68   int argc,

    69   sqlite3_value **argv

    70 ){

    71   const char *z = 0;

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

    73     case SQLITE_NULL:    z = "null";    break;

    74     case SQLITE_INTEGER: z = "integer"; break;

    75     case SQLITE_TEXT:    z = "text";    break;

    76     case SQLITE_FLOAT:   z = "real";    break;

    77     case SQLITE_BLOB:    z = "blob";    break;

    78   }

    79   sqlite3_result_text(context, z, -1, SQLITE_STATIC);

    80 }

    81 

    82 

    83 /*

    84 ** Implementation of the length() function

    85 */

    86 static void lengthFunc(

    87   sqlite3_context *context,

    88   int argc,

    89   sqlite3_value **argv

    90 ){

    91   int len;

    92 

    93   assert( argc==1 );

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

    95     case SQLITE_BLOB:

    96     case SQLITE_INTEGER:

    97     case SQLITE_FLOAT: {

    98       sqlite3_result_int(context, sqlite3_value_bytes(argv[0]));

    99       break;

   100     }

   101     case SQLITE_TEXT: {

   102       const unsigned char *z = sqlite3_value_text(argv[0]);

   103       if( z==0 ) return;

   104       len = 0;

   105       while( *z ){

   106         len++;

   107         SQLITE_SKIP_UTF8(z);

   108       }

   109       sqlite3_result_int(context, len);

   110       break;

   111     }

   112     default: {

   113       sqlite3_result_null(context);

   114       break;

   115     }

   116   }

   117 }

   118 

   119 /*

   120 ** Implementation of the abs() function

   121 */

   122 static void absFunc(sqlite3_context *context, int argc, sqlite3_value **argv){

   123   assert( argc==1 );

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

   125     case SQLITE_INTEGER: {

   126       i64 iVal = sqlite3_value_int64(argv[0]);

   127       if( iVal<0 ){

   128         if( (iVal<<1)==0 ){

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

   130           return;

   131         }

   132         iVal = -iVal;

   133       } 

   134       sqlite3_result_int64(context, iVal);

   135       break;

   136     }

   137     case SQLITE_NULL: {

   138       sqlite3_result_null(context);

   139       break;

   140     }

   141     default: {

   142       double rVal = sqlite3_value_double(argv[0]);

   143       if( rVal<0 ) rVal = -rVal;

   144       sqlite3_result_double(context, rVal);

   145       break;

   146     }

   147   }

   148 }

   149 

   150 /*

   151 ** Implementation of the substr() function.

   152 **

   153 ** substr(x,p1,p2)  returns p2 characters of x[] beginning with p1.

   154 ** p1 is 1-indexed.  So substr(x,1,1) returns the first character

   155 ** of x.  If x is text, then we actually count UTF-8 characters.

   156 ** If x is a blob, then we count bytes.

   157 **

   158 ** If p1 is negative, then we begin abs(p1) from the end of x[].

   159 */

   160 static void substrFunc(

   161   sqlite3_context *context,

   162   int argc,

   163   sqlite3_value **argv

   164 ){

   165   const unsigned char *z;

   166   const unsigned char *z2;

   167   int len;

   168   int p0type;

   169   i64 p1, p2;

   170 

   171   assert( argc==3 || argc==2 );

   172   p0type = sqlite3_value_type(argv[0]);

   173   if( p0type==SQLITE_BLOB ){

   174     len = sqlite3_value_bytes(argv[0]);

   175     z = sqlite3_value_blob(argv[0]);

   176     if( z==0 ) return;

   177     assert( len==sqlite3_value_bytes(argv[0]) );

   178   }else{

   179     z = sqlite3_value_text(argv[0]);

   180     if( z==0 ) return;

   181     len = 0;

   182     for(z2=z; *z2; len++){

   183       SQLITE_SKIP_UTF8(z2);

   184     }

   185   }

   186   p1 = sqlite3_value_int(argv[1]);

   187   if( argc==3 ){

   188     p2 = sqlite3_value_int(argv[2]);

   189   }else{

   190     p2 = sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_LENGTH];

   191   }

   192   if( p1<0 ){

   193     p1 += len;

   194     if( p1<0 ){

   195       p2 += p1;

   196       p1 = 0;

   197     }

   198   }else if( p1>0 ){

   199     p1--;

   200   }

   201   if( p1+p2>len ){

   202     p2 = len-p1;

   203   }

   204   if( p0type!=SQLITE_BLOB ){

   205     while( *z && p1 ){

   206       SQLITE_SKIP_UTF8(z);

   207       p1--;

   208     }

   209     for(z2=z; *z2 && p2; p2--){

   210       SQLITE_SKIP_UTF8(z2);

   211     }

   212     sqlite3_result_text(context, (char*)z, z2-z, SQLITE_TRANSIENT);

   213   }else{

   214     if( p2<0 ) p2 = 0;

   215     sqlite3_result_blob(context, (char*)&z[p1], p2, SQLITE_TRANSIENT);

   216   }

   217 }

   218 

   219 /*

   220 ** Implementation of the round() function

   221 */

   222 static void roundFunc(sqlite3_context *context, int argc, sqlite3_value **argv){

   223   int n = 0;

   224   double r;

   225   char zBuf[500];  /* larger than the %f representation of the largest double */

   226   assert( argc==1 || argc==2 );

   227   if( argc==2 ){

   228     if( SQLITE_NULL==sqlite3_value_type(argv[1]) ) return;

   229     n = sqlite3_value_int(argv[1]);

   230     if( n>30 ) n = 30;

   231     if( n<0 ) n = 0;

   232   }

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

   234   r = sqlite3_value_double(argv[0]);

   235   sqlite3_snprintf(sizeof(zBuf),zBuf,"%.*f",n,r);

   236   sqlite3AtoF(zBuf, &r);

   237   sqlite3_result_double(context, r);

   238 }

   239 

   240 /*

   241 ** Allocate nByte bytes of space using sqlite3_malloc(). If the

   242 ** allocation fails, call sqlite3_result_error_nomem() to notify

   243 ** the database handle that malloc() has failed.

   244 */

   245 static void *contextMalloc(sqlite3_context *context, i64 nByte){

   246   char *z;

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

   248     sqlite3_result_error_toobig(context);

   249     z = 0;

   250   }else{

   251     z = sqlite3Malloc(nByte);

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

   253       sqlite3_result_error_nomem(context);

   254     }

   255   }

   256   return z;

   257 }

   258 

   259 /*

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

   261 */

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

   263   char *z1;

   264   const char *z2;

   265   int i, n;

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

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

   268   n = sqlite3_value_bytes(argv[0]);

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

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

   271   if( z2 ){

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

   273     if( z1 ){

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

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

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

   277       }

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

   279     }

   280   }

   281 }

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

   283   char *z1;

   284   const char *z2;

   285   int i, n;

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

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

   288   n = sqlite3_value_bytes(argv[0]);

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

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

   291   if( z2 ){

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

   293     if( z1 ){

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

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

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

   297       }

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

   299     }

   300   }

   301 }

   302 

   303 /*

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

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

   306 ** argument.

   307 */

   308 static void ifnullFunc(

   309   sqlite3_context *context,

   310   int argc,

   311   sqlite3_value **argv

   312 ){

   313   int i;

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

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

   316       sqlite3_result_value(context, argv[i]);

   317       break;

   318     }

   319   }

   320 }

   321 

   322 /*

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

   324 */

   325 static void randomFunc(

   326   sqlite3_context *context,

   327   int argc,

   328   sqlite3_value **argv

   329 ){

   330   sqlite_int64 r;

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

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

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

   334   sqlite3_result_int64(context, r);

   335 }

   336 

   337 /*

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

   339 ** that is N bytes long.

   340 */

   341 static void randomBlob(

   342   sqlite3_context *context,

   343   int argc,

   344   sqlite3_value **argv

   345 ){

   346   int n;

   347   unsigned char *p;

   348   assert( argc==1 );

   349   n = sqlite3_value_int(argv[0]);

   350   if( n<1 ){

   351     n = 1;

   352   }

   353   p = contextMalloc(context, n);

   354   if( p ){

   355     sqlite3_randomness(n, p);

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

   357   }

   358 }

   359 

   360 /*

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

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

   363 */

   364 static void last_insert_rowid(

   365   sqlite3_context *context, 

   366   int arg, 

   367   sqlite3_value **argv

   368 ){

   369   sqlite3 *db = sqlite3_context_db_handle(context);

   370   sqlite3_result_int64(context, sqlite3_last_insert_rowid(db));

   371 }

   372 

   373 /*

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

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

   376 */

   377 static void changes(

   378   sqlite3_context *context,

   379   int arg,

   380   sqlite3_value **argv

   381 ){

   382   sqlite3 *db = sqlite3_context_db_handle(context);

   383   sqlite3_result_int(context, sqlite3_changes(db));

   384 }

   385 

   386 /*

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

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

   389 */

   390 static void total_changes(

   391   sqlite3_context *context,

   392   int arg,

   393   sqlite3_value **argv

   394 ){

   395   sqlite3 *db = sqlite3_context_db_handle(context);

   396   sqlite3_result_int(context, sqlite3_total_changes(db));

   397 }

   398 

   399 /*

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

   401 */

   402 struct compareInfo {

   403   u8 matchAll;

   404   u8 matchOne;

   405   u8 matchSet;

   406   u8 noCase;

   407 };

   408 

   409 /*

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

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

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

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

   414 */

   415 #if defined(SQLITE_EBCDIC)

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

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

   418 #else

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

   420 #endif

   421 

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

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

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

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

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

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

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

   429 

   430 /*

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

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

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

   434 **

   435 ** Globbing rules:

   436 **

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

   438 **

   439 **      '?'       Matches exactly one character.

   440 **

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

   442 **                characters.

   443 **

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

   445 **

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

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

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

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

   450 ** it the last character in the list.

   451 **

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

   453 **

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

   455 **

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

   457 */

   458 static int patternCompare(

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

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

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

   462   const int esc                    /* The escape character */

   463 ){

   464   int c, c2;

   465   int invert;

   466   int seen;

   467   u8 matchOne = pInfo->matchOne;

   468   u8 matchAll = pInfo->matchAll;

   469   u8 matchSet = pInfo->matchSet;

   470   u8 noCase = pInfo->noCase; 

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

   472 

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

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

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

   476                || c == matchOne ){

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

   478           return 0;

   479         }

   480       }

   481       if( c==0 ){

   482         return 1;

   483       }else if( c==esc ){

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

   485         if( c==0 ){

   486           return 0;

   487         }

   488       }else if( c==matchSet ){

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

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

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

   492           SQLITE_SKIP_UTF8(zString);

   493         }

   494         return *zString!=0;

   495       }

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

   497         if( noCase ){

   498           GlogUpperToLower(c2);

   499           GlogUpperToLower(c);

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

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

   502             GlogUpperToLower(c2);

   503           }

   504         }else{

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

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

   507           }

   508         }

   509         if( c2==0 ) return 0;

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

   511       }

   512       return 0;

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

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

   515         return 0;

   516       }

   517     }else if( c==matchSet ){

   518       int prior_c = 0;

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

   520       seen = 0;

   521       invert = 0;

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

   523       if( c==0 ) return 0;

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

   525       if( c2=='^' ){

   526         invert = 1;

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

   528       }

   529       if( c2==']' ){

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

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

   532       }

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

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

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

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

   537           prior_c = 0;

   538         }else{

   539           if( c==c2 ){

   540             seen = 1;

   541           }

   542           prior_c = c2;

   543         }

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

   545       }

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

   547         return 0;

   548       }

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

   550       prevEscape = 1;

   551     }else{

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

   553       if( noCase ){

   554         GlogUpperToLower(c);

   555         GlogUpperToLower(c2);

   556       }

   557       if( c!=c2 ){

   558         return 0;

   559       }

   560       prevEscape = 0;

   561     }

   562   }

   563   return *zString==0;

   564 }

   565 

   566 /*

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

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

   569 ** only.

   570 */

   571 #ifdef SQLITE_TEST

   572 int sqlite3_like_count = 0;

   573 #endif

   574 

   575 

   576 /*

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

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

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

   580 **

   581 **       A LIKE B

   582 **

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

   584 **

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

   586 ** the GLOB operator.

   587 */

   588 static void likeFunc(

   589   sqlite3_context *context, 

   590   int argc, 

   591   sqlite3_value **argv

   592 ){

   593   const unsigned char *zA, *zB;

   594   int escape = 0;

   595   sqlite3 *db = sqlite3_context_db_handle(context);

   596 

   597   zB = sqlite3_value_text(argv[0]);

   598   zA = sqlite3_value_text(argv[1]);

   599 

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

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

   602   */

   603   if( sqlite3_value_bytes(argv[0]) >

   604         db->aLimit[SQLITE_LIMIT_LIKE_PATTERN_LENGTH] ){

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

   606     return;

   607   }

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

   609 

   610   if( argc==3 ){

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

   612     ** Otherwise, return an error.

   613     */

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

   615     if( zEsc==0 ) return;

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

   617       sqlite3_result_error(context, 

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

   619       return;

   620     }

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

   622   }

   623   if( zA && zB ){

   624     struct compareInfo *pInfo = sqlite3_user_data(context);

   625 #ifdef SQLITE_TEST

   626     sqlite3_like_count++;

   627 #endif

   628     

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

   630   }

   631 }

   632 

   633 /*

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

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

   636 ** arguments are equal to each other.

   637 */

   638 static void nullifFunc(

   639   sqlite3_context *context,

   640   int argc,

   641   sqlite3_value **argv

   642 ){

   643   CollSeq *pColl = sqlite3GetFuncCollSeq(context);

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

   645     sqlite3_result_value(context, argv[0]);

   646   }

   647 }

   648 

   649 /*

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

   651 ** of the SQLite library that is running.

   652 */

   653 static void versionFunc(

   654   sqlite3_context *context,

   655   int argc,

   656   sqlite3_value **argv

   657 ){

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

   659 }

   660 

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

   662 ** digits. */

   663 static const char hexdigits[] = {

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

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

   666 };

   667 

   668 /*

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

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

   671 ** on this function.

   672 **

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

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

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

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

   677 ** single-quote escapes.

   678 */

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

   680   if( argc<1 ) return;

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

   682     case SQLITE_NULL: {

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

   684       break;

   685     }

   686     case SQLITE_INTEGER:

   687     case SQLITE_FLOAT: {

   688       sqlite3_result_value(context, argv[0]);

   689       break;

   690     }

   691     case SQLITE_BLOB: {

   692       char *zText = 0;

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

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

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

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

   697       if( zText ){

   698         int i;

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

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

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

   702         }

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

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

   705         zText[0] = 'X';

   706         zText[1] = '\'';

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

   708         sqlite3_free(zText);

   709       }

   710       break;

   711     }

   712     case SQLITE_TEXT: {

   713       int i,j;

   714       u64 n;

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

   716       char *z;

   717 

   718       if( zArg==0 ) return;

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

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

   721       if( z ){

   722         z[0] = '\'';

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

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

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

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

   727           }

   728         }

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

   730         z[j] = 0;

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

   732       }

   733     }

   734   }

   735 }

   736 

   737 /*

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

   739 ** a hexadecimal rendering as text.

   740 */

   741 static void hexFunc(

   742   sqlite3_context *context,

   743   int argc,

   744   sqlite3_value **argv

   745 ){

   746   int i, n;

   747   const unsigned char *pBlob;

   748   char *zHex, *z;

   749   assert( argc==1 );

   750   pBlob = sqlite3_value_blob(argv[0]);

   751   n = sqlite3_value_bytes(argv[0]);

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

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

   754   if( zHex ){

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

   756       unsigned char c = *pBlob;

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

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

   759     }

   760     *z = 0;

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

   762   }

   763 }

   764 

   765 /*

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

   767 */

   768 static void zeroblobFunc(

   769   sqlite3_context *context,

   770   int argc,

   771   sqlite3_value **argv

   772 ){

   773   i64 n;

   774   assert( argc==1 );

   775   n = sqlite3_value_int64(argv[0]);

   776   if( n>SQLITE_MAX_LENGTH ){

   777     sqlite3_result_error_toobig(context);

   778   }else{

   779     sqlite3_result_zeroblob(context, n);

   780   }

   781 }

   782 

   783 /*

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

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

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

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

   788 */

   789 static void replaceFunc(

   790   sqlite3_context *context,

   791   int argc,

   792   sqlite3_value **argv

   793 ){

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

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

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

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

   798   int nStr;                /* Size of zStr */

   799   int nPattern;            /* Size of zPattern */

   800   int nRep;                /* Size of zRep */

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

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

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

   804 

   805   assert( argc==3 );

   806   zStr = sqlite3_value_text(argv[0]);

   807   if( zStr==0 ) return;

   808   nStr = sqlite3_value_bytes(argv[0]);

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

   810   zPattern = sqlite3_value_text(argv[1]);

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

   812   nPattern = sqlite3_value_bytes(argv[1]);

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

   814   zRep = sqlite3_value_text(argv[2]);

   815   if( zRep==0 ) return;

   816   nRep = sqlite3_value_bytes(argv[2]);

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

   818   nOut = nStr + 1;

   819   assert( nOut<SQLITE_MAX_LENGTH );

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

   821   if( zOut==0 ){

   822     return;

   823   }

   824   loopLimit = nStr - nPattern;  

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

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

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

   828     }else{

   829       u8 *zOld;

   830       sqlite3 *db = sqlite3_context_db_handle(context);

   831       nOut += nRep - nPattern;

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

   833         sqlite3_result_error_toobig(context);

   834         sqlite3DbFree(db, zOut);

   835         return;

   836       }

   837       zOld = zOut;

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

   839       if( zOut==0 ){

   840         sqlite3_result_error_nomem(context);

   841         sqlite3DbFree(db, zOld);

   842         return;

   843       }

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

   845       j += nRep;

   846       i += nPattern-1;

   847     }

   848   }

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

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

   851   j += nStr - i;

   852   assert( j<=nOut );

   853   zOut[j] = 0;

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

   855 }

   856 

   857 /*

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

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

   860 */

   861 static void trimFunc(

   862   sqlite3_context *context,

   863   int argc,

   864   sqlite3_value **argv

   865 ){

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

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

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

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

   870   int i;                            /* Loop counter */

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

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

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

   874 

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

   876     return;

   877   }

   878   zIn = sqlite3_value_text(argv[0]);

   879   if( zIn==0 ) return;

   880   nIn = sqlite3_value_bytes(argv[0]);

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

   882   if( argc==1 ){

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

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

   885     nChar = 1;

   886     aLen = (u8*)lenOne;

   887     azChar = (unsigned char **)azOne;

   888     zCharSet = 0;

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

   890     return;

   891   }else{

   892     const unsigned char *z;

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

   894       SQLITE_SKIP_UTF8(z);

   895     }

   896     if( nChar>0 ){

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

   898       if( azChar==0 ){

   899         return;

   900       }

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

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

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

   904         SQLITE_SKIP_UTF8(z);

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

   906       }

   907     }

   908   }

   909   if( nChar>0 ){

   910     flags = SQLITE_PTR_TO_INT(sqlite3_user_data(context));

   911     if( flags & 1 ){

   912       while( nIn>0 ){

   913         int len;

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

   915           len = aLen[i];

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

   917         }

   918         if( i>=nChar ) break;

   919         zIn += len;

   920         nIn -= len;

   921       }

   922     }

   923     if( flags & 2 ){

   924       while( nIn>0 ){

   925         int len;

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

   927           len = aLen[i];

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

   929         }

   930         if( i>=nChar ) break;

   931         nIn -= len;

   932       }

   933     }

   934     if( zCharSet ){

   935       sqlite3_free(azChar);

   936     }

   937   }

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

   939 }

   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.

  1135     ** Therefore the next statement sets variable 'max' to 1 for the max()