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.133 2006/08/19 11:34:01 drh Exp $

    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 /*

    30 ** Return the collating function associated with a function.

    31 */

    32 static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){

    33   return context->pColl;

    34 }

    35 

    36 /*

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

    38 */

    39 static void minmaxFunc(

    40   sqlite3_context *context,

    41   int argc,

    42   sqlite3_value **argv

    43 ){

    44   int i;

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

    46   int iBest;

    47   CollSeq *pColl;

    48 

    49   if( argc==0 ) return;

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

    51   pColl = sqlite3GetFuncCollSeq(context);

    52   assert( pColl );

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

    54   iBest = 0;

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

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

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

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

    59       iBest = i;

    60     }

    61   }

    62   sqlite3_result_value(context, argv[iBest]);

    63 }

    64 

    65 /*

    66 ** Return the type of the argument.

    67 */

    68 static void typeofFunc(

    69   sqlite3_context *context,

    70   int argc,

    71   sqlite3_value **argv

    72 ){

    73   const char *z = 0;

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

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

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

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

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

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

    80   }

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

    82 }

    83 

    84 

    85 /*

    86 ** Implementation of the length() function

    87 */

    88 static void lengthFunc(

    89   sqlite3_context *context,

    90   int argc,

    91   sqlite3_value **argv

    92 ){

    93   int len;

    94 

    95   assert( argc==1 );

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

    97     case SQLITE_BLOB:

    98     case SQLITE_INTEGER:

    99     case SQLITE_FLOAT: {

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

   101       break;

   102     }

   103     case SQLITE_TEXT: {

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

   105       for(len=0; *z; z++){ if( (0xc0&*z)!=0x80 ) len++; }

   106       sqlite3_result_int(context, len);

   107       break;

   108     }

   109     default: {

   110       sqlite3_result_null(context);

   111       break;

   112     }

   113   }

   114 }

   115 

   116 /*

   117 ** Implementation of the abs() function

   118 */

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

   120   assert( argc==1 );

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

   122     case SQLITE_INTEGER: {

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

   124       if( iVal<0 ){

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

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

   127           return;

   128         }

   129         iVal = -iVal;

   130       } 

   131       sqlite3_result_int64(context, iVal);

   132       break;

   133     }

   134     case SQLITE_NULL: {

   135       sqlite3_result_null(context);

   136       break;

   137     }

   138     default: {

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

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

   141       sqlite3_result_double(context, rVal);

   142       break;

   143     }

   144   }

   145 }

   146 

   147 /*

   148 ** Implementation of the substr() function

   149 */

   150 static void substrFunc(

   151   sqlite3_context *context,

   152   int argc,

   153   sqlite3_value **argv

   154 ){

   155   const unsigned char *z;

   156   const unsigned char *z2;

   157   int i;

   158   int p1, p2, len;

   159 

   160   assert( argc==3 );

   161   z = sqlite3_value_text(argv[0]);

   162   if( z==0 ) return;

   163   p1 = sqlite3_value_int(argv[1]);

   164   p2 = sqlite3_value_int(argv[2]);

   165   for(len=0, z2=z; *z2; z2++){ if( (0xc0&*z2)!=0x80 ) len++; }

   166   if( p1<0 ){

   167     p1 += len;

   168     if( p1<0 ){

   169       p2 += p1;

   170       p1 = 0;

   171     }

   172   }else if( p1>0 ){

   173     p1--;

   174   }

   175   if( p1+p2>len ){

   176     p2 = len-p1;

   177   }

   178   for(i=0; i<p1 && z[i]; i++){

   179     if( (z[i]&0xc0)==0x80 ) p1++;

   180   }

   181   while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p1++; }

   182   for(; i<p1+p2 && z[i]; i++){

   183     if( (z[i]&0xc0)==0x80 ) p2++;

   184   }

   185   while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p2++; }

   186   if( p2<0 ) p2 = 0;

   187   sqlite3_result_text(context, (char*)&z[p1], p2, SQLITE_TRANSIENT);

   188 }

   189 

   190 /*

   191 ** Implementation of the round() function

   192 */

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

   194   int n = 0;

   195   double r;

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

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

   198   if( argc==2 ){

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

   200     n = sqlite3_value_int(argv[1]);

   201     if( n>30 ) n = 30;

   202     if( n<0 ) n = 0;

   203   }

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

   205   r = sqlite3_value_double(argv[0]);

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

   207   sqlite3AtoF(zBuf, &r);

   208   sqlite3_result_double(context, r);

   209 }

   210 

   211 /*

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

   213 */

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

   215   unsigned char *z;

   216   int i;

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

   218   z = sqliteMalloc(sqlite3_value_bytes(argv[0])+1);

   219   if( z==0 ) return;

   220   strcpy((char*)z, (char*)sqlite3_value_text(argv[0]));

   221   for(i=0; z[i]; i++){

   222     z[i] = toupper(z[i]);

   223   }

   224   sqlite3_result_text(context, (char*)z, -1, SQLITE_TRANSIENT);

   225   sqliteFree(z);

   226 }

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

   228   unsigned char *z;

   229   int i;

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

   231   z = sqliteMalloc(sqlite3_value_bytes(argv[0])+1);

   232   if( z==0 ) return;

   233   strcpy((char*)z, (char*)sqlite3_value_text(argv[0]));

   234   for(i=0; z[i]; i++){

   235     z[i] = tolower(z[i]);

   236   }

   237   sqlite3_result_text(context, (char*)z, -1, SQLITE_TRANSIENT);

   238   sqliteFree(z);

   239 }

   240 

   241 /*

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

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

   244 ** argument.

   245 */

   246 static void ifnullFunc(

   247   sqlite3_context *context,

   248   int argc,

   249   sqlite3_value **argv

   250 ){

   251   int i;

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

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

   254       sqlite3_result_value(context, argv[i]);

   255       break;

   256     }

   257   }

   258 }

   259 

   260 /*

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

   262 */

   263 static void randomFunc(

   264   sqlite3_context *context,

   265   int argc,

   266   sqlite3_value **argv

   267 ){

   268   sqlite_int64 r;

   269   sqlite3Randomness(sizeof(r), &r);

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

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

   272   sqlite3_result_int64(context, r);

   273 }

   274 

   275 /*

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

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

   278 */

   279 static void last_insert_rowid(

   280   sqlite3_context *context, 

   281   int arg, 

   282   sqlite3_value **argv

   283 ){

   284   sqlite3 *db = sqlite3_user_data(context);

   285   sqlite3_result_int64(context, sqlite3_last_insert_rowid(db));

   286 }

   287 

   288 /*

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

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

   291 */

   292 static void 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_changes(db));

   299 }

   300 

   301 /*

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

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

   304 */

   305 static void total_changes(

   306   sqlite3_context *context,

   307   int arg,

   308   sqlite3_value **argv

   309 ){

   310   sqlite3 *db = sqlite3_user_data(context);

   311   sqlite3_result_int(context, sqlite3_total_changes(db));

   312 }

   313 

   314 /*

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

   316 */

   317 struct compareInfo {

   318   u8 matchAll;

   319   u8 matchOne;

   320   u8 matchSet;

   321   u8 noCase;

   322 };

   323 

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

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

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

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

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

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

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

   331 

   332 /*

   333 ** X is a pointer to the first byte of a UTF-8 character.  Increment

   334 ** X so that it points to the next character.  This only works right

   335 ** if X points to a well-formed UTF-8 string.

   336 */

   337 #define sqliteNextChar(X)  while( (0xc0&*++(X))==0x80 ){}

   338 #define sqliteCharVal(X)   sqlite3ReadUtf8(X)

   339 

   340 

   341 /*

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

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

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

   345 **

   346 ** Globbing rules:

   347 **

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

   349 **

   350 **      '?'       Matches exactly one character.

   351 **

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

   353 **                characters.

   354 **

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

   356 **

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

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

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

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

   361 ** it the last character in the list.

   362 **

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

   364 **

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

   366 **

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

   368 */

   369 static int patternCompare(

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

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

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

   373   const int esc                    /* The escape character */

   374 ){

   375   register int c;

   376   int invert;

   377   int seen;

   378   int c2;

   379   u8 matchOne = pInfo->matchOne;

   380   u8 matchAll = pInfo->matchAll;

   381   u8 matchSet = pInfo->matchSet;

   382   u8 noCase = pInfo->noCase; 

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

   384 

   385   while( (c = *zPattern)!=0 ){

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

   387       while( (c=zPattern[1]) == matchAll || c == matchOne ){

   388         if( c==matchOne ){

   389           if( *zString==0 ) return 0;

   390           sqliteNextChar(zString);

   391         }

   392         zPattern++;

   393       }

   394       if( c && esc && sqlite3ReadUtf8(&zPattern[1])==esc ){

   395         u8 const *zTemp = &zPattern[1];

   396         sqliteNextChar(zTemp);

   397         c = *zTemp;

   398       }

   399       if( c==0 ) return 1;

   400       if( c==matchSet ){

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

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

   403           sqliteNextChar(zString);

   404         }

   405         return *zString!=0;

   406       }else{

   407         while( (c2 = *zString)!=0 ){

   408           if( noCase ){

   409             c2 = sqlite3UpperToLower[c2];

   410             c = sqlite3UpperToLower[c];

   411             while( c2 != 0 && c2 != c ){ c2 = sqlite3UpperToLower[*++zString]; }

   412           }else{

   413             while( c2 != 0 && c2 != c ){ c2 = *++zString; }

   414           }

   415           if( c2==0 ) return 0;

   416           if( patternCompare(&zPattern[1],zString,pInfo,esc) ) return 1;

   417           sqliteNextChar(zString);

   418         }

   419         return 0;

   420       }

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

   422       if( *zString==0 ) return 0;

   423       sqliteNextChar(zString);

   424       zPattern++;

   425     }else if( c==matchSet ){

   426       int prior_c = 0;

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

   428       seen = 0;

   429       invert = 0;

   430       c = sqliteCharVal(zString);

   431       if( c==0 ) return 0;

   432       c2 = *++zPattern;

   433       if( c2=='^' ){ invert = 1; c2 = *++zPattern; }

   434       if( c2==']' ){

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

   436         c2 = *++zPattern;

   437       }

   438       while( (c2 = sqliteCharVal(zPattern))!=0 && c2!=']' ){

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

   440           zPattern++;

   441           c2 = sqliteCharVal(zPattern);

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

   443           prior_c = 0;

   444         }else if( c==c2 ){

   445           seen = 1;

   446           prior_c = c2;

   447         }else{

   448           prior_c = c2;

   449         }

   450         sqliteNextChar(zPattern);

   451       }

   452       if( c2==0 || (seen ^ invert)==0 ) return 0;

   453       sqliteNextChar(zString);

   454       zPattern++;

   455     }else if( esc && !prevEscape && sqlite3ReadUtf8(zPattern)==esc){

   456       prevEscape = 1;

   457       sqliteNextChar(zPattern);

   458     }else{

   459       if( noCase ){

   460         if( sqlite3UpperToLower[c] != sqlite3UpperToLower[*zString] ) return 0;

   461       }else{

   462         if( c != *zString ) return 0;

   463       }

   464       zPattern++;

   465       zString++;

   466       prevEscape = 0;

   467     }

   468   }

   469   return *zString==0;

   470 }

   471 

   472 /*

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

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

   475 ** only.

   476 */

   477 #ifdef SQLITE_TEST

   478 int sqlite3_like_count = 0;

   479 #endif

   480 

   481 

   482 /*

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

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

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

   486 **

   487 **       A LIKE B

   488 **

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

   490 **

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

   492 ** the GLOB operator.

   493 */

   494 static void likeFunc(

   495   sqlite3_context *context, 

   496   int argc, 

   497   sqlite3_value **argv

   498 ){

   499   const unsigned char *zA = sqlite3_value_text(argv[0]);

   500   const unsigned char *zB = sqlite3_value_text(argv[1]);

   501   int escape = 0;

   502   if( argc==3 ){

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

   504     ** Otherwise, return an error.

   505     */

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

   507     if( sqlite3utf8CharLen((char*)zEsc, -1)!=1 ){

   508       sqlite3_result_error(context, 

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

   510       return;

   511     }

   512     escape = sqlite3ReadUtf8(zEsc);

   513   }

   514   if( zA && zB ){

   515     struct compareInfo *pInfo = sqlite3_user_data(context);

   516 #ifdef SQLITE_TEST

   517     sqlite3_like_count++;

   518 #endif

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

   520   }

   521 }

   522 

   523 /*

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

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

   526 ** arguments are equal to each other.

   527 */

   528 static void nullifFunc(

   529   sqlite3_context *context,

   530   int argc,

   531   sqlite3_value **argv

   532 ){

   533   CollSeq *pColl = sqlite3GetFuncCollSeq(context);

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

   535     sqlite3_result_value(context, argv[0]);

   536   }

   537 }

   538 

   539 /*

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

   541 ** of the SQLite library that is running.

   542 */

   543 static void versionFunc(

   544   sqlite3_context *context,

   545   int argc,

   546   sqlite3_value **argv

   547 ){

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

   549 }

   550 

   551 /*

   552 ** The MATCH() function is unimplemented.  If anybody tries to use it,

   553 ** return an error.

   554 */

   555 static void matchStub(

   556   sqlite3_context *context,

   557   int argc,

   558   sqlite3_value **argv

   559 ){

   560   static const char zErr[] = "MATCH is not implemented";

   561   sqlite3_result_error(context, zErr, sizeof(zErr)-1);

   562 }

   563 

   564 

   565 /*

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

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

   568 ** on this function.

   569 **

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

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

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

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

   574 ** single-quote escapes.

   575 */

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

   577   if( argc<1 ) return;

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

   579     case SQLITE_NULL: {

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

   581       break;

   582     }

   583     case SQLITE_INTEGER:

   584     case SQLITE_FLOAT: {

   585       sqlite3_result_value(context, argv[0]);

   586       break;

   587     }

   588     case SQLITE_BLOB: {

   589       static const char hexdigits[] = { 

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

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

   592       };

   593       char *zText = 0;

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

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

   596 

   597       zText = (char *)sqliteMalloc((2*nBlob)+4); 

   598       if( !zText ){

   599         sqlite3_result_error(context, "out of memory", -1);

   600       }else{

   601         int i;

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

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

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

   605         }

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

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

   608         zText[0] = 'X';

   609         zText[1] = '\'';

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

   611         sqliteFree(zText);

   612       }

   613       break;

   614     }

   615     case SQLITE_TEXT: {

   616       int i,j,n;

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

   618       char *z;

   619 

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

   621       z = sqliteMalloc( i+n+3 );

   622       if( z==0 ) return;

   623       z[0] = '\'';

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

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

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

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

   628         }

   629       }

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

   631       z[j] = 0;

   632       sqlite3_result_text(context, z, j, SQLITE_TRANSIENT);

   633       sqliteFree(z);

   634     }

   635   }

   636 }

   637 

   638 #ifdef SQLITE_SOUNDEX

   639 /*

   640 ** Compute the soundex encoding of a word.

   641 */

   642 static void soundexFunc(sqlite3_context *context, int argc, sqlite3_value **argv){

   643   char zResult[8];

   644   const u8 *zIn;

   645   int i, j;

   646   static const unsigned char iCode[] = {

   647     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,

   648     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,

   649     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,

   650     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,

   651     0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,

   652     1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,

   653     0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,

   654     1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,

   655   };

   656   assert( argc==1 );

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

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

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

   660   if( zIn[i] ){

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

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

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

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

   665       if( code>0 ){

   666         if( code!=prevcode ){

   667           prevcode = code;

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

   669         }

   670       }else{

   671         prevcode = 0;

   672       }

   673     }

   674     while( j<4 ){

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

   676     }

   677     zResult[j] = 0;

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

   679   }else{

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

   681   }

   682 }

   683 #endif

   684 

   685 #ifndef SQLITE_OMIT_LOAD_EXTENSION

   686 /*

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

   688 */

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

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

   691   const char *zProc = 0;

   692   sqlite3 *db = sqlite3_user_data(context);

   693   char *zErrMsg = 0;

   694 

   695   if( argc==2 ){

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

   697   }

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

   699     sqlite3_result_error(context, zErrMsg, -1);

   700     sqlite3_free(zErrMsg);

   701   }

   702 }

   703 #endif

   704 

   705 #ifdef SQLITE_TEST

   706 /*

   707 ** This function generates a string of random characters.  Used for

   708 ** generating test data.

   709 */

   710 static void randStr(sqlite3_context *context, int argc, sqlite3_value **argv){

   711   static const unsigned char zSrc[] = 

   712      "abcdefghijklmnopqrstuvwxyz"

   713      "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

   714      "0123456789"

   715      ".-!,:*^+=_|?/<> ";

   716   int iMin, iMax, n, r, i;

   717   unsigned char zBuf[1000];

   718   if( argc>=1 ){

   719     iMin = sqlite3_value_int(argv[0]);

   720     if( iMin<0 ) iMin = 0;

   721     if( iMin>=sizeof(zBuf) ) iMin = sizeof(zBuf)-1;

   722   }else{

   723     iMin = 1;

   724   }

   725   if( argc>=2 ){

   726     iMax = sqlite3_value_int(argv[1]);

   727     if( iMax<iMin ) iMax = iMin;

   728     if( iMax>=sizeof(zBuf) ) iMax = sizeof(zBuf)-1;

   729   }else{

   730     iMax = 50;

   731   }

   732   n = iMin;

   733   if( iMax>iMin ){

   734     sqlite3Randomness(sizeof(r), &r);

   735     r &= 0x7fffffff;

   736     n += r%(iMax + 1 - iMin);

   737   }

   738   assert( n<sizeof(zBuf) );

   739   sqlite3Randomness(n, zBuf);

   740   for(i=0; i<n; i++){

   741     zBuf[i] = zSrc[zBuf[i]%(sizeof(zSrc)-1)];

   742   }

   743   zBuf[n] = 0;

   744   sqlite3_result_text(context, (char*)zBuf, n, SQLITE_TRANSIENT);

   745 }

   746 #endif /* SQLITE_TEST */

   747 

   748 #ifdef SQLITE_TEST

   749 /*

   750 ** The following two SQL functions are used to test returning a text

   751 ** result with a destructor. Function 'test_destructor' takes one argument

   752 ** and returns the same argument interpreted as TEXT. A destructor is

   753 ** passed with the sqlite3_result_text() call.

   754 **

   755 ** SQL function 'test_destructor_count' returns the number of outstanding 

   756 ** allocations made by 'test_destructor';

   757 **

   758 ** WARNING: Not threadsafe.

   759 */

   760 static int test_destructor_count_var = 0;

   761 static void destructor(void *p){

   762   char *zVal = (char *)p;

   763   assert(zVal);

   764   zVal--;

   765   sqliteFree(zVal);

   766   test_destructor_count_var--;

   767 }

   768 static void test_destructor(

   769   sqlite3_context *pCtx, 

   770   int nArg,

   771   sqlite3_value **argv

   772 ){

   773   char *zVal;

   774   int len;

   775   sqlite3 *db = sqlite3_user_data(pCtx);

   776  

   777   test_destructor_count_var++;

   778   assert( nArg==1 );

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

   780   len = sqlite3ValueBytes(argv[0], ENC(db)); 

   781   zVal = sqliteMalloc(len+3);

   782   zVal[len] = 0;

   783   zVal[len-1] = 0;

   784   assert( zVal );

   785   zVal++;

   786   memcpy(zVal, sqlite3ValueText(argv[0], ENC(db)), len);

   787   if( ENC(db)==SQLITE_UTF8 ){

   788     sqlite3_result_text(pCtx, zVal, -1, destructor);

   789 #ifndef SQLITE_OMIT_UTF16

   790   }else if( ENC(db)==SQLITE_UTF16LE ){

   791     sqlite3_result_text16le(pCtx, zVal, -1, destructor);

   792   }else{

   793     sqlite3_result_text16be(pCtx, zVal, -1, destructor);

   794 #endif /* SQLITE_OMIT_UTF16 */

   795   }

   796 }

   797 static void test_destructor_count(

   798   sqlite3_context *pCtx, 

   799   int nArg,

   800   sqlite3_value **argv

   801 ){

   802   sqlite3_result_int(pCtx, test_destructor_count_var);

   803 }

   804 #endif /* SQLITE_TEST */

   805 

   806 #ifdef SQLITE_TEST

   807 /*

   808 ** Routines for testing the sqlite3_get_auxdata() and sqlite3_set_auxdata()

   809 ** interface.

   810 **

   811 ** The test_auxdata() SQL function attempts to register each of its arguments

   812 ** as auxiliary data.  If there are no prior registrations of aux data for

   813 ** that argument (meaning the argument is not a constant or this is its first

   814 ** call) then the result for that argument is 0.  If there is a prior

   815 ** registration, the result for that argument is 1.  The overall result

   816 ** is the individual argument results separated by spaces.

   817 */

   818 static void free_test_auxdata(void *p) {sqliteFree(p);}

   819 static void test_auxdata(

   820   sqlite3_context *pCtx, 

   821   int nArg,

   822   sqlite3_value **argv

   823 ){

   824   int i;

   825   char *zRet = sqliteMalloc(nArg*2);

   826   if( !zRet ) return;

   827   for(i=0; i<nArg; i++){

   828     char const *z = (char*)sqlite3_value_text(argv[i]);

   829     if( z ){

   830       char *zAux = sqlite3_get_auxdata(pCtx, i);

   831       if( zAux ){

   832         zRet[i*2] = '1';

   833         if( strcmp(zAux, z) ){

   834           sqlite3_result_error(pCtx, "Auxilary data corruption", -1);

   835           return;

   836         }

   837       }else{

   838         zRet[i*2] = '0';

   839         zAux = sqliteStrDup(z);

   840         sqlite3_set_auxdata(pCtx, i, zAux, free_test_auxdata);

   841       }

   842       zRet[i*2+1] = ' ';

   843     }

   844   }

   845   sqlite3_result_text(pCtx, zRet, 2*nArg-1, free_test_auxdata);

   846 }

   847 #endif /* SQLITE_TEST */

   848 

   849 #ifdef SQLITE_TEST

   850 /*

   851 ** A function to test error reporting from user functions. This function

   852 ** returns a copy of it's first argument as an error.

   853 */

   854 static void test_error(

   855   sqlite3_context *pCtx, 

   856   int nArg,

   857   sqlite3_value **argv

   858 ){

   859   sqlite3_result_error(pCtx, (char*)sqlite3_value_text(argv[0]), 0);

   860 }

   861 #endif /* SQLITE_TEST */

   862 

   863 /*

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

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

   866 */

   867 typedef struct SumCtx SumCtx;

   868 struct SumCtx {

   869   double rSum;      /* Floating point sum */

   870   i64 iSum;         /* Integer sum */   

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

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

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

   874 };

   875 

   876 /*

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

   878 **

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

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

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

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

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

   884 ** it overflows an integer.

   885 */

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

   887   SumCtx *p;

   888   int type;

   889   assert( argc==1 );

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

   891   type = sqlite3_value_numeric_type(argv[0]);

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

   893     p->cnt++;

   894     if( type==SQLITE_INTEGER ){

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

   896       p->rSum += v;

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

   898         i64 iNewSum = p->iSum + v;

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

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

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

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

   903         p->iSum = iNewSum;

   904       }

   905     }else{

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

   907       p->approx = 1;

   908     }

   909   }

   910 }

   911 static void sumFinalize(sqlite3_context *context){

   912   SumCtx *p;

   913   p = sqlite3_aggregate_context(context, 0);

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

   915     if( p->overflow ){

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

   917     }else if( p->approx ){

   918       sqlite3_result_double(context, p->rSum);

   919     }else{

   920       sqlite3_result_int64(context, p->iSum);

   921     }

   922   }

   923 }

   924 static void avgFinalize(sqlite3_context *context){

   925   SumCtx *p;

   926   p = sqlite3_aggregate_context(context, 0);

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

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

   929   }

   930 }

   931 static void totalFinalize(sqlite3_context *context){

   932   SumCtx *p;

   933   p = sqlite3_aggregate_context(context, 0);

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

   935 }

   936 

   937 /*

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

   939 ** count() aggregate function.

   940 */

   941 typedef struct CountCtx CountCtx;

   942 struct CountCtx {

   943   i64 n;

   944 };

   945 

   946 /*

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

   948 */

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

   950   CountCtx *p;

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

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

   953     p->n++;

   954   }

   955 }   

   956 static void countFinalize(sqlite3_context *context){

   957   CountCtx *p;

   958   p = sqlite3_aggregate_context(context, 0);

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

   960 }

   961 

   962 /*

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

   964 */

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

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

   967   Mem *pBest;

   968 

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

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

   971   if( !pBest ) return;

   972 

   973   if( pBest->flags ){

   974     int max;

   975     int cmp;

   976     CollSeq *pColl = sqlite3GetFuncCollSeq(context);

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

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

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

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

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

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

   983     ** aggregate, or 0 for min().

   984     */

   985     max = ((sqlite3_user_data(context)==(void *)-1)?1:0);

   986     cmp = sqlite3MemCompare(pBest, pArg, pColl);

   987     if( (max && cmp<0) || (!max && cmp>0) ){

   988       sqlite3VdbeMemCopy(pBest, pArg);

   989     }

   990   }else{

   991     sqlite3VdbeMemCopy(pBest, pArg);

   992   }

   993 }

   994 static void minMaxFinalize(sqlite3_context *context){

   995   sqlite3_value *pRes;

   996   pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0);

   997   if( pRes ){

   998     if( pRes->flags ){

   999       sqlite3_result_value(context, pRes);

  1000     }

  1001     sqlite3VdbeMemRelease(pRes);

  1002   }

  1003 }

  1004 

  1005 

  1006 /*

  1007 ** This function registered all of the above C functions as SQL

  1008 ** functions.  This should be the only routine in this file with

  1009 ** external linkage.

  1010 */

  1011 void sqlite3RegisterBuiltinFunctions(sqlite3 *db){

  1012   static const struct {

  1013      char *zName;

  1014      signed char nArg;

  1015      u8 argType;           /* 0: none.  1: db  2: (-1) */

  1016      u8 eTextRep;          /* 1: UTF-16.  0: UTF-8 */

  1017      u8 needCollSeq;

  1018      void (*xFunc)(sqlite3_context*,int,sqlite3_value **);

  1019   } aFuncs[] = {

  1020     { "min",               -1, 0, SQLITE_UTF8,    1, minmaxFunc },

  1021     { "min",                0, 0, SQLITE_UTF8,    1, 0          },

  1022     { "max",               -1, 2, SQLITE_UTF8,    1, minmaxFunc },

  1023     { "max",                0, 2, SQLITE_UTF8,    1, 0          },

  1024     { "typeof",             1, 0, SQLITE_UTF8,    0, typeofFunc },

  1025     { "length",             1, 0, SQLITE_UTF8,    0, lengthFunc },

  1026     { "substr",             3, 0, SQLITE_UTF8,    0, substrFunc },

  1027 #ifndef SQLITE_OMIT_UTF16

  1028     { "substr",             3, 0, SQLITE_UTF16LE, 0, sqlite3utf16Substr },

  1029 #endif

  1030     { "abs",                1, 0, SQLITE_UTF8,    0, absFunc    },

  1031     { "round",              1, 0, SQLITE_UTF8,    0, roundFunc  },

  1032     { "round",              2, 0, SQLITE_UTF8,    0, roundFunc  },

  1033     { "upper",              1, 0, SQLITE_UTF8,    0, upperFunc  },

  1034     { "lower",              1, 0, SQLITE_UTF8,    0, lowerFunc  },

  1035     { "coalesce",          -1, 0, SQLITE_UTF8,    0, ifnullFunc },

  1036     { "coalesce",           0, 0, SQLITE_UTF8,    0, 0          },

  1037     { "coalesce",           1, 0, SQLITE_UTF8,    0, 0          },

  1038     { "ifnull",             2, 0, SQLITE_UTF8,    1, ifnullFunc },

  1039     { "random",            -1, 0, SQLITE_UTF8,    0, randomFunc },

  1040     { "nullif",             2, 0, SQLITE_UTF8,    1, nullifFunc },

  1041     { "sqlite_version",     0, 0, SQLITE_UTF8,    0, versionFunc},

  1042     { "quote",              1, 0, SQLITE_UTF8,    0, quoteFunc  },

  1043     { "last_insert_rowid",  0, 1, SQLITE_UTF8,    0, last_insert_rowid },

  1044     { "changes",            0, 1, SQLITE_UTF8,    0, changes    },

  1045     { "total_changes",      0, 1, SQLITE_UTF8,    0, total_changes },

  1046     { "match",              2, 0, SQLITE_UTF8,    0, matchStub },

  1047 #ifdef SQLITE_SOUNDEX

  1048     { "soundex",            1, 0, SQLITE_UTF8, 0, soundexFunc},

  1049 #endif

  1050 #ifndef SQLITE_OMIT_LOAD_EXTENSION

  1051     { "load_extension",     1, 1, SQLITE_UTF8,    0, loadExt },

  1052     { "load_extension",     2, 1, SQLITE_UTF8,    0, loadExt },

  1053 #endif

  1054 #ifdef SQLITE_TEST

  1055     { "randstr",               2, 0, SQLITE_UTF8, 0, randStr    },

  1056     { "test_destructor",       1, 1, SQLITE_UTF8, 0, test_destructor},

  1057     { "test_destructor_count", 0, 0, SQLITE_UTF8, 0, test_destructor_count},

  1058     { "test_auxdata",         -1, 0, SQLITE_UTF8, 0, test_auxdata},

  1059     { "test_error",            1, 0, SQLITE_UTF8, 0, test_error},

  1060 #endif

  1061   };

  1062   static const struct {

  1063     char *zName;

  1064     signed char nArg;

  1065     u8 argType;

  1066     u8 needCollSeq;

  1067     void (*xStep)(sqlite3_context*,int,sqlite3_value**);

  1068     void (*xFinalize)(sqlite3_context*);

  1069   } aAggs[] = {

  1070     { "min",    1, 0, 1, minmaxStep,   minMaxFinalize },

  1071     { "max",    1, 2, 1, minmaxStep,   minMaxFinalize },

  1072     { "sum",    1, 0, 0, sumStep,      sumFinalize    },

  1073     { "total",  1, 0, 0, sumStep,      totalFinalize    },

  1074     { "avg",    1, 0, 0, sumStep,      avgFinalize    },

  1075     { "count",  0, 0, 0, countStep,    countFinalize  },

  1076     { "count",  1, 0, 0, countStep,    countFinalize  },

  1077   };

  1078   int i;

  1079 

  1080   for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){

  1081     void *pArg = 0;

  1082     switch( aFuncs[i].argType ){

  1083       case 1: pArg = db; break;

  1084       case 2: pArg = (void *)(-1); break;

  1085     }

  1086     sqlite3CreateFunc(db, aFuncs[i].zName, aFuncs[i].nArg,

  1087         aFuncs[i].eTextRep, pArg, aFuncs[i].xFunc, 0, 0);

  1088     if( aFuncs[i].needCollSeq ){

  1089       FuncDef *pFunc = sqlite3FindFunction(db, aFuncs[i].zName, 

  1090           strlen(aFuncs[i].zName), aFuncs[i].nArg, aFuncs[i].eTextRep, 0);

  1091       if( pFunc && aFuncs[i].needCollSeq ){

  1092         pFunc->needCollSeq = 1;

  1093       }

  1094     }

  1095   }

  1096 #ifndef SQLITE_OMIT_ALTERTABLE

  1097   sqlite3AlterFunctions(db);

  1098 #endif

  1099 #ifndef SQLITE_OMIT_PARSER

  1100   sqlite3AttachFunctions(db);

  1101 #endif

  1102   for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){

  1103     void *pArg = 0;

  1104     switch( aAggs[i].argType ){

  1105       case 1: pArg = db; break;

  1106       case 2: pArg = (void *)(-1); break;

  1107     }

  1108     sqlite3CreateFunc(db, aAggs[i].zName, aAggs[i].nArg, SQLITE_UTF8, 

  1109         pArg, 0, aAggs[i].xStep, aAggs[i].xFinalize);

  1110     if( aAggs[i].needCollSeq ){

  1111       FuncDef *pFunc = sqlite3FindFunction( db, aAggs[i].zName,

  1112           strlen(aAggs[i].zName), aAggs[i].nArg, SQLITE_UTF8, 0);

  1113       if( pFunc && aAggs[i].needCollSeq ){

  1114         pFunc->needCollSeq = 1;

  1115       }

  1116     }

  1117   }

  1118   sqlite3RegisterDateTimeFunctions(db);

  1119 #ifdef SQLITE_SSE

  1120   (void)sqlite3SseFunctions(db);

  1121 #endif

  1122 #ifdef SQLITE_CASE_SENSITIVE_LIKE

  1123   sqlite3RegisterLikeFunctions(db, 1);

  1124 #else

  1125   sqlite3RegisterLikeFunctions(db, 0);

  1126 #endif

  1127 }

  1128 

  1129 /*

  1130 ** Set the LIKEOPT flag on the 2-argument function with the given name.

  1131 */

  1132 static void setLikeOptFlag(sqlite3 *db, const char *zName, int flagVal){

  1133   FuncDef *pDef;

  1134   pDef = sqlite3FindFunction(db, zName, strlen(zName), 2, SQLITE_UTF8, 0);

  1135   if( pDef ){