4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
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.
11 *************************************************************************
12 ** This file contains the C-language implementations for many of the SQL
13 ** functions of SQLite. (Some function, and in particular the date and
14 ** time functions, are implemented separately.)
16 #include "sqliteInt.h"
19 #ifndef SQLITE_OMIT_FLOATING_POINT
25 ** Return the collating function associated with a function.
27 static CollSeq
*sqlite3GetFuncCollSeq(sqlite3_context
*context
){
29 assert( context
->pVdbe
!=0 );
30 pOp
= &context
->pVdbe
->aOp
[context
->iOp
-1];
31 assert( pOp
->opcode
==OP_CollSeq
);
32 assert( pOp
->p4type
==P4_COLLSEQ
);
37 ** Indicate that the accumulator load should be skipped on this
38 ** iteration of the aggregate loop.
40 static void sqlite3SkipAccumulatorLoad(sqlite3_context
*context
){
41 assert( context
->isError
<=0 );
42 context
->isError
= -1;
43 context
->skipFlag
= 1;
47 ** Implementation of the non-aggregate min() and max() functions
49 static void minmaxFunc(
50 sqlite3_context
*context
,
55 int mask
; /* 0 for min() or 0xffffffff for max() */
60 mask
= sqlite3_user_data(context
)==0 ? 0 : -1;
61 pColl
= sqlite3GetFuncCollSeq(context
);
63 assert( mask
==-1 || mask
==0 );
65 if( sqlite3_value_type(argv
[0])==SQLITE_NULL
) return;
66 for(i
=1; i
<argc
; i
++){
67 if( sqlite3_value_type(argv
[i
])==SQLITE_NULL
) return;
68 if( (sqlite3MemCompare(argv
[iBest
], argv
[i
], pColl
)^mask
)>=0 ){
73 sqlite3_result_value(context
, argv
[iBest
]);
77 ** Return the type of the argument.
79 static void typeofFunc(
80 sqlite3_context
*context
,
84 static const char *azType
[] = { "integer", "real", "text", "blob", "null" };
85 int i
= sqlite3_value_type(argv
[0]) - 1;
86 UNUSED_PARAMETER(NotUsed
);
87 assert( i
>=0 && i
<ArraySize(azType
) );
88 assert( SQLITE_INTEGER
==1 );
89 assert( SQLITE_FLOAT
==2 );
90 assert( SQLITE_TEXT
==3 );
91 assert( SQLITE_BLOB
==4 );
92 assert( SQLITE_NULL
==5 );
93 /* EVIDENCE-OF: R-01470-60482 The sqlite3_value_type(V) interface returns
94 ** the datatype code for the initial datatype of the sqlite3_value object
95 ** V. The returned value is one of SQLITE_INTEGER, SQLITE_FLOAT,
96 ** SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. */
97 sqlite3_result_text(context
, azType
[i
], -1, SQLITE_STATIC
);
102 ** Implementation of the length() function
104 static void lengthFunc(
105 sqlite3_context
*context
,
110 UNUSED_PARAMETER(argc
);
111 switch( sqlite3_value_type(argv
[0]) ){
115 sqlite3_result_int(context
, sqlite3_value_bytes(argv
[0]));
119 const unsigned char *z
= sqlite3_value_text(argv
[0]);
120 const unsigned char *z0
;
124 while( (c
= *z
)!=0 ){
127 while( (*z
& 0xc0)==0x80 ){ z
++; z0
++; }
130 sqlite3_result_int(context
, (int)(z
-z0
));
134 sqlite3_result_null(context
);
141 ** Implementation of the abs() function.
143 ** IMP: R-23979-26855 The abs(X) function returns the absolute value of
144 ** the numeric argument X.
146 static void absFunc(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
148 UNUSED_PARAMETER(argc
);
149 switch( sqlite3_value_type(argv
[0]) ){
150 case SQLITE_INTEGER
: {
151 i64 iVal
= sqlite3_value_int64(argv
[0]);
153 if( iVal
==SMALLEST_INT64
){
154 /* IMP: R-31676-45509 If X is the integer -9223372036854775808
155 ** then abs(X) throws an integer overflow error since there is no
156 ** equivalent positive 64-bit two complement value. */
157 sqlite3_result_error(context
, "integer overflow", -1);
162 sqlite3_result_int64(context
, iVal
);
166 /* IMP: R-37434-19929 Abs(X) returns NULL if X is NULL. */
167 sqlite3_result_null(context
);
171 /* Because sqlite3_value_double() returns 0.0 if the argument is not
172 ** something that can be converted into a number, we have:
173 ** IMP: R-01992-00519 Abs(X) returns 0.0 if X is a string or blob
174 ** that cannot be converted to a numeric value.
176 double rVal
= sqlite3_value_double(argv
[0]);
177 if( rVal
<0 ) rVal
= -rVal
;
178 sqlite3_result_double(context
, rVal
);
185 ** Implementation of the instr() function.
187 ** instr(haystack,needle) finds the first occurrence of needle
188 ** in haystack and returns the number of previous characters plus 1,
189 ** or 0 if needle does not occur within haystack.
191 ** If both haystack and needle are BLOBs, then the result is one more than
192 ** the number of bytes in haystack prior to the first occurrence of needle,
193 ** or 0 if needle never occurs in haystack.
195 static void instrFunc(
196 sqlite3_context
*context
,
200 const unsigned char *zHaystack
;
201 const unsigned char *zNeedle
;
204 int typeHaystack
, typeNeedle
;
207 unsigned char firstChar
;
208 sqlite3_value
*pC1
= 0;
209 sqlite3_value
*pC2
= 0;
211 UNUSED_PARAMETER(argc
);
212 typeHaystack
= sqlite3_value_type(argv
[0]);
213 typeNeedle
= sqlite3_value_type(argv
[1]);
214 if( typeHaystack
==SQLITE_NULL
|| typeNeedle
==SQLITE_NULL
) return;
215 nHaystack
= sqlite3_value_bytes(argv
[0]);
216 nNeedle
= sqlite3_value_bytes(argv
[1]);
218 if( typeHaystack
==SQLITE_BLOB
&& typeNeedle
==SQLITE_BLOB
){
219 zHaystack
= sqlite3_value_blob(argv
[0]);
220 zNeedle
= sqlite3_value_blob(argv
[1]);
222 }else if( typeHaystack
!=SQLITE_BLOB
&& typeNeedle
!=SQLITE_BLOB
){
223 zHaystack
= sqlite3_value_text(argv
[0]);
224 zNeedle
= sqlite3_value_text(argv
[1]);
227 pC1
= sqlite3_value_dup(argv
[0]);
228 zHaystack
= sqlite3_value_text(pC1
);
229 if( zHaystack
==0 ) goto endInstrOOM
;
230 nHaystack
= sqlite3_value_bytes(pC1
);
231 pC2
= sqlite3_value_dup(argv
[1]);
232 zNeedle
= sqlite3_value_text(pC2
);
233 if( zNeedle
==0 ) goto endInstrOOM
;
234 nNeedle
= sqlite3_value_bytes(pC2
);
237 if( zNeedle
==0 || (nHaystack
&& zHaystack
==0) ) goto endInstrOOM
;
238 firstChar
= zNeedle
[0];
239 while( nNeedle
<=nHaystack
240 && (zHaystack
[0]!=firstChar
|| memcmp(zHaystack
, zNeedle
, nNeedle
)!=0)
246 }while( isText
&& (zHaystack
[0]&0xc0)==0x80 );
248 if( nNeedle
>nHaystack
) N
= 0;
250 sqlite3_result_int(context
, N
);
252 sqlite3_value_free(pC1
);
253 sqlite3_value_free(pC2
);
256 sqlite3_result_error_nomem(context
);
261 ** Implementation of the printf() function.
263 static void printfFunc(
264 sqlite3_context
*context
,
272 sqlite3
*db
= sqlite3_context_db_handle(context
);
274 if( argc
>=1 && (zFormat
= (const char*)sqlite3_value_text(argv
[0]))!=0 ){
278 sqlite3StrAccumInit(&str
, db
, 0, 0, db
->aLimit
[SQLITE_LIMIT_LENGTH
]);
279 str
.printfFlags
= SQLITE_PRINTF_SQLFUNC
;
280 sqlite3_str_appendf(&str
, zFormat
, &x
);
282 sqlite3_result_text(context
, sqlite3StrAccumFinish(&str
), n
,
288 ** Implementation of the substr() function.
290 ** substr(x,p1,p2) returns p2 characters of x[] beginning with p1.
291 ** p1 is 1-indexed. So substr(x,1,1) returns the first character
292 ** of x. If x is text, then we actually count UTF-8 characters.
293 ** If x is a blob, then we count bytes.
295 ** If p1 is negative, then we begin abs(p1) from the end of x[].
297 ** If p2 is negative, return the p2 characters preceding p1.
299 static void substrFunc(
300 sqlite3_context
*context
,
304 const unsigned char *z
;
305 const unsigned char *z2
;
311 assert( argc
==3 || argc
==2 );
312 if( sqlite3_value_type(argv
[1])==SQLITE_NULL
313 || (argc
==3 && sqlite3_value_type(argv
[2])==SQLITE_NULL
)
317 p0type
= sqlite3_value_type(argv
[0]);
318 p1
= sqlite3_value_int(argv
[1]);
319 if( p0type
==SQLITE_BLOB
){
320 len
= sqlite3_value_bytes(argv
[0]);
321 z
= sqlite3_value_blob(argv
[0]);
323 assert( len
==sqlite3_value_bytes(argv
[0]) );
325 z
= sqlite3_value_text(argv
[0]);
329 for(z2
=z
; *z2
; len
++){
330 SQLITE_SKIP_UTF8(z2
);
334 #ifdef SQLITE_SUBSTR_COMPATIBILITY
335 /* If SUBSTR_COMPATIBILITY is defined then substr(X,0,N) work the same as
336 ** as substr(X,1,N) - it returns the first N characters of X. This
337 ** is essentially a back-out of the bug-fix in check-in [5fc125d362df4b8]
338 ** from 2009-02-02 for compatibility of applications that exploited the
339 ** old buggy behavior. */
340 if( p1
==0 ) p1
= 1; /* <rdar://problem/6778339> */
343 p2
= sqlite3_value_int(argv
[2]);
349 p2
= sqlite3_context_db_handle(context
)->aLimit
[SQLITE_LIMIT_LENGTH
];
370 assert( p1
>=0 && p2
>=0 );
371 if( p0type
!=SQLITE_BLOB
){
376 for(z2
=z
; *z2
&& p2
; p2
--){
377 SQLITE_SKIP_UTF8(z2
);
379 sqlite3_result_text64(context
, (char*)z
, z2
-z
, SQLITE_TRANSIENT
,
386 sqlite3_result_blob64(context
, (char*)&z
[p1
], (u64
)p2
, SQLITE_TRANSIENT
);
391 ** Implementation of the round() function
393 #ifndef SQLITE_OMIT_FLOATING_POINT
394 static void roundFunc(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
398 assert( argc
==1 || argc
==2 );
400 if( SQLITE_NULL
==sqlite3_value_type(argv
[1]) ) return;
401 n
= sqlite3_value_int(argv
[1]);
405 if( sqlite3_value_type(argv
[0])==SQLITE_NULL
) return;
406 r
= sqlite3_value_double(argv
[0]);
407 /* If Y==0 and X will fit in a 64-bit int,
408 ** handle the rounding directly,
409 ** otherwise use printf.
411 if( r
<-4503599627370496.0 || r
>+4503599627370496.0 ){
412 /* The value has no fractional part so there is nothing to round */
414 r
= (double)((sqlite_int64
)(r
+(r
<0?-0.5:+0.5)));
416 zBuf
= sqlite3_mprintf("%.*f",n
,r
);
418 sqlite3_result_error_nomem(context
);
421 sqlite3AtoF(zBuf
, &r
, sqlite3Strlen30(zBuf
), SQLITE_UTF8
);
424 sqlite3_result_double(context
, r
);
429 ** Allocate nByte bytes of space using sqlite3Malloc(). If the
430 ** allocation fails, call sqlite3_result_error_nomem() to notify
431 ** the database handle that malloc() has failed and return NULL.
432 ** If nByte is larger than the maximum string or blob length, then
433 ** raise an SQLITE_TOOBIG exception and return NULL.
435 static void *contextMalloc(sqlite3_context
*context
, i64 nByte
){
437 sqlite3
*db
= sqlite3_context_db_handle(context
);
439 testcase( nByte
==db
->aLimit
[SQLITE_LIMIT_LENGTH
] );
440 testcase( nByte
==db
->aLimit
[SQLITE_LIMIT_LENGTH
]+1 );
441 if( nByte
>db
->aLimit
[SQLITE_LIMIT_LENGTH
] ){
442 sqlite3_result_error_toobig(context
);
445 z
= sqlite3Malloc(nByte
);
447 sqlite3_result_error_nomem(context
);
454 ** Implementation of the upper() and lower() SQL functions.
456 static void upperFunc(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
460 UNUSED_PARAMETER(argc
);
461 z2
= (char*)sqlite3_value_text(argv
[0]);
462 n
= sqlite3_value_bytes(argv
[0]);
463 /* Verify that the call to _bytes() does not invalidate the _text() pointer */
464 assert( z2
==(char*)sqlite3_value_text(argv
[0]) );
466 z1
= contextMalloc(context
, ((i64
)n
)+1);
469 z1
[i
] = (char)sqlite3Toupper(z2
[i
]);
471 sqlite3_result_text(context
, z1
, n
, sqlite3_free
);
475 static void lowerFunc(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
479 UNUSED_PARAMETER(argc
);
480 z2
= (char*)sqlite3_value_text(argv
[0]);
481 n
= sqlite3_value_bytes(argv
[0]);
482 /* Verify that the call to _bytes() does not invalidate the _text() pointer */
483 assert( z2
==(char*)sqlite3_value_text(argv
[0]) );
485 z1
= contextMalloc(context
, ((i64
)n
)+1);
488 z1
[i
] = sqlite3Tolower(z2
[i
]);
490 sqlite3_result_text(context
, z1
, n
, sqlite3_free
);
496 ** Some functions like COALESCE() and IFNULL() and UNLIKELY() are implemented
497 ** as VDBE code so that unused argument values do not have to be computed.
498 ** However, we still need some kind of function implementation for this
499 ** routines in the function table. The noopFunc macro provides this.
500 ** noopFunc will never be called so it doesn't matter what the implementation
501 ** is. We might as well use the "version()" function as a substitute.
503 #define noopFunc versionFunc /* Substitute function - never called */
506 ** Implementation of random(). Return a random integer.
508 static void randomFunc(
509 sqlite3_context
*context
,
511 sqlite3_value
**NotUsed2
514 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
515 sqlite3_randomness(sizeof(r
), &r
);
517 /* We need to prevent a random number of 0x8000000000000000
518 ** (or -9223372036854775808) since when you do abs() of that
519 ** number of you get the same value back again. To do this
520 ** in a way that is testable, mask the sign bit off of negative
521 ** values, resulting in a positive value. Then take the
522 ** 2s complement of that positive value. The end result can
523 ** therefore be no less than -9223372036854775807.
525 r
= -(r
& LARGEST_INT64
);
527 sqlite3_result_int64(context
, r
);
531 ** Implementation of randomblob(N). Return a random blob
532 ** that is N bytes long.
534 static void randomBlob(
535 sqlite3_context
*context
,
542 UNUSED_PARAMETER(argc
);
543 n
= sqlite3_value_int64(argv
[0]);
547 p
= contextMalloc(context
, n
);
549 sqlite3_randomness(n
, p
);
550 sqlite3_result_blob(context
, (char*)p
, n
, sqlite3_free
);
555 ** Implementation of the last_insert_rowid() SQL function. The return
556 ** value is the same as the sqlite3_last_insert_rowid() API function.
558 static void last_insert_rowid(
559 sqlite3_context
*context
,
561 sqlite3_value
**NotUsed2
563 sqlite3
*db
= sqlite3_context_db_handle(context
);
564 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
565 /* IMP: R-51513-12026 The last_insert_rowid() SQL function is a
566 ** wrapper around the sqlite3_last_insert_rowid() C/C++ interface
568 sqlite3_result_int64(context
, sqlite3_last_insert_rowid(db
));
572 ** Implementation of the changes() SQL function.
574 ** IMP: R-62073-11209 The changes() SQL function is a wrapper
575 ** around the sqlite3_changes() C/C++ function and hence follows the same
576 ** rules for counting changes.
579 sqlite3_context
*context
,
581 sqlite3_value
**NotUsed2
583 sqlite3
*db
= sqlite3_context_db_handle(context
);
584 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
585 sqlite3_result_int(context
, sqlite3_changes(db
));
589 ** Implementation of the total_changes() SQL function. The return value is
590 ** the same as the sqlite3_total_changes() API function.
592 static void total_changes(
593 sqlite3_context
*context
,
595 sqlite3_value
**NotUsed2
597 sqlite3
*db
= sqlite3_context_db_handle(context
);
598 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
599 /* IMP: R-52756-41993 This function is a wrapper around the
600 ** sqlite3_total_changes() C/C++ interface. */
601 sqlite3_result_int(context
, sqlite3_total_changes(db
));
605 ** A structure defining how to do GLOB-style comparisons.
608 u8 matchAll
; /* "*" or "%" */
609 u8 matchOne
; /* "?" or "_" */
610 u8 matchSet
; /* "[" or 0 */
611 u8 noCase
; /* true to ignore case differences */
615 ** For LIKE and GLOB matching on EBCDIC machines, assume that every
616 ** character is exactly one byte in size. Also, provde the Utf8Read()
617 ** macro for fast reading of the next character in the common case where
618 ** the next character is ASCII.
620 #if defined(SQLITE_EBCDIC)
621 # define sqlite3Utf8Read(A) (*((*A)++))
622 # define Utf8Read(A) (*(A++))
624 # define Utf8Read(A) (A[0]<0x80?*(A++):sqlite3Utf8Read(&A))
627 static const struct compareInfo globInfo
= { '*', '?', '[', 0 };
628 /* The correct SQL-92 behavior is for the LIKE operator to ignore
629 ** case. Thus 'a' LIKE 'A' would be true. */
630 static const struct compareInfo likeInfoNorm
= { '%', '_', 0, 1 };
631 /* If SQLITE_CASE_SENSITIVE_LIKE is defined, then the LIKE operator
632 ** is case sensitive causing 'a' LIKE 'A' to be false */
633 static const struct compareInfo likeInfoAlt
= { '%', '_', 0, 0 };
636 ** Possible error returns from patternMatch()
638 #define SQLITE_MATCH 0
639 #define SQLITE_NOMATCH 1
640 #define SQLITE_NOWILDCARDMATCH 2
643 ** Compare two UTF-8 strings for equality where the first string is
644 ** a GLOB or LIKE expression. Return values:
646 ** SQLITE_MATCH: Match
647 ** SQLITE_NOMATCH: No match
648 ** SQLITE_NOWILDCARDMATCH: No match in spite of having * or % wildcards.
652 ** '*' Matches any sequence of zero or more characters.
654 ** '?' Matches exactly one character.
656 ** [...] Matches one character from the enclosed list of
659 ** [^...] Matches one character not in the enclosed list.
661 ** With the [...] and [^...] matching, a ']' character can be included
662 ** in the list by making it the first character after '[' or '^'. A
663 ** range of characters can be specified using '-'. Example:
664 ** "[a-z]" matches any single lower-case letter. To match a '-', make
665 ** it the last character in the list.
667 ** Like matching rules:
669 ** '%' Matches any sequence of zero or more characters
671 *** '_' Matches any one character
673 ** Ec Where E is the "esc" character and c is any other
674 ** character, including '%', '_', and esc, match exactly c.
676 ** The comments within this routine usually assume glob matching.
678 ** This routine is usually quick, but can be N**2 in the worst case.
680 static int patternCompare(
681 const u8
*zPattern
, /* The glob pattern */
682 const u8
*zString
, /* The string to compare against the glob */
683 const struct compareInfo
*pInfo
, /* Information about how to do the compare */
684 u32 matchOther
/* The escape char (LIKE) or '[' (GLOB) */
686 u32 c
, c2
; /* Next pattern and input string chars */
687 u32 matchOne
= pInfo
->matchOne
; /* "?" or "_" */
688 u32 matchAll
= pInfo
->matchAll
; /* "*" or "%" */
689 u8 noCase
= pInfo
->noCase
; /* True if uppercase==lowercase */
690 const u8
*zEscaped
= 0; /* One past the last escaped input char */
692 while( (c
= Utf8Read(zPattern
))!=0 ){
693 if( c
==matchAll
){ /* Match "*" */
694 /* Skip over multiple "*" characters in the pattern. If there
695 ** are also "?" characters, skip those as well, but consume a
696 ** single character of the input string for each "?" skipped */
697 while( (c
=Utf8Read(zPattern
)) == matchAll
|| c
== matchOne
){
698 if( c
==matchOne
&& sqlite3Utf8Read(&zString
)==0 ){
699 return SQLITE_NOWILDCARDMATCH
;
703 return SQLITE_MATCH
; /* "*" at the end of the pattern matches */
704 }else if( c
==matchOther
){
705 if( pInfo
->matchSet
==0 ){
706 c
= sqlite3Utf8Read(&zPattern
);
707 if( c
==0 ) return SQLITE_NOWILDCARDMATCH
;
709 /* "[...]" immediately follows the "*". We have to do a slow
710 ** recursive search in this case, but it is an unusual case. */
711 assert( matchOther
<0x80 ); /* '[' is a single-byte character */
713 int bMatch
= patternCompare(&zPattern
[-1],zString
,pInfo
,matchOther
);
714 if( bMatch
!=SQLITE_NOMATCH
) return bMatch
;
715 SQLITE_SKIP_UTF8(zString
);
717 return SQLITE_NOWILDCARDMATCH
;
721 /* At this point variable c contains the first character of the
722 ** pattern string past the "*". Search in the input string for the
723 ** first matching character and recursively continue the match from
726 ** For a case-insensitive search, set variable cx to be the same as
727 ** c but in the other case and search the input string for either
734 zStop
[0] = sqlite3Toupper(c
);
735 zStop
[1] = sqlite3Tolower(c
);
742 zString
+= strcspn((const char*)zString
, zStop
);
743 if( zString
[0]==0 ) break;
745 bMatch
= patternCompare(zPattern
,zString
,pInfo
,matchOther
);
746 if( bMatch
!=SQLITE_NOMATCH
) return bMatch
;
750 while( (c2
= Utf8Read(zString
))!=0 ){
751 if( c2
!=c
) continue;
752 bMatch
= patternCompare(zPattern
,zString
,pInfo
,matchOther
);
753 if( bMatch
!=SQLITE_NOMATCH
) return bMatch
;
756 return SQLITE_NOWILDCARDMATCH
;
759 if( pInfo
->matchSet
==0 ){
760 c
= sqlite3Utf8Read(&zPattern
);
761 if( c
==0 ) return SQLITE_NOMATCH
;
767 c
= sqlite3Utf8Read(&zString
);
768 if( c
==0 ) return SQLITE_NOMATCH
;
769 c2
= sqlite3Utf8Read(&zPattern
);
772 c2
= sqlite3Utf8Read(&zPattern
);
775 if( c
==']' ) seen
= 1;
776 c2
= sqlite3Utf8Read(&zPattern
);
778 while( c2
&& c2
!=']' ){
779 if( c2
=='-' && zPattern
[0]!=']' && zPattern
[0]!=0 && prior_c
>0 ){
780 c2
= sqlite3Utf8Read(&zPattern
);
781 if( c
>=prior_c
&& c
<=c2
) seen
= 1;
789 c2
= sqlite3Utf8Read(&zPattern
);
791 if( c2
==0 || (seen
^ invert
)==0 ){
792 return SQLITE_NOMATCH
;
797 c2
= Utf8Read(zString
);
798 if( c
==c2
) continue;
799 if( noCase
&& sqlite3Tolower(c
)==sqlite3Tolower(c2
) && c
<0x80 && c2
<0x80 ){
802 if( c
==matchOne
&& zPattern
!=zEscaped
&& c2
!=0 ) continue;
803 return SQLITE_NOMATCH
;
805 return *zString
==0 ? SQLITE_MATCH
: SQLITE_NOMATCH
;
809 ** The sqlite3_strglob() interface. Return 0 on a match (like strcmp()) and
810 ** non-zero if there is no match.
812 int sqlite3_strglob(const char *zGlobPattern
, const char *zString
){
813 return patternCompare((u8
*)zGlobPattern
, (u8
*)zString
, &globInfo
, '[');
817 ** The sqlite3_strlike() interface. Return 0 on a match and non-zero for
818 ** a miss - like strcmp().
820 int sqlite3_strlike(const char *zPattern
, const char *zStr
, unsigned int esc
){
821 return patternCompare((u8
*)zPattern
, (u8
*)zStr
, &likeInfoNorm
, esc
);
825 ** Count the number of times that the LIKE operator (or GLOB which is
826 ** just a variation of LIKE) gets called. This is used for testing
830 int sqlite3_like_count
= 0;
835 ** Implementation of the like() SQL function. This function implements
836 ** the build-in LIKE operator. The first argument to the function is the
837 ** pattern and the second argument is the string. So, the SQL statements:
841 ** is implemented as like(B,A).
843 ** This same function (with a different compareInfo structure) computes
844 ** the GLOB operator.
846 static void likeFunc(
847 sqlite3_context
*context
,
851 const unsigned char *zA
, *zB
;
854 sqlite3
*db
= sqlite3_context_db_handle(context
);
855 struct compareInfo
*pInfo
= sqlite3_user_data(context
);
857 #ifdef SQLITE_LIKE_DOESNT_MATCH_BLOBS
858 if( sqlite3_value_type(argv
[0])==SQLITE_BLOB
859 || sqlite3_value_type(argv
[1])==SQLITE_BLOB
862 sqlite3_like_count
++;
864 sqlite3_result_int(context
, 0);
869 /* Limit the length of the LIKE or GLOB pattern to avoid problems
870 ** of deep recursion and N*N behavior in patternCompare().
872 nPat
= sqlite3_value_bytes(argv
[0]);
873 testcase( nPat
==db
->aLimit
[SQLITE_LIMIT_LIKE_PATTERN_LENGTH
] );
874 testcase( nPat
==db
->aLimit
[SQLITE_LIMIT_LIKE_PATTERN_LENGTH
]+1 );
875 if( nPat
> db
->aLimit
[SQLITE_LIMIT_LIKE_PATTERN_LENGTH
] ){
876 sqlite3_result_error(context
, "LIKE or GLOB pattern too complex", -1);
880 /* The escape character string must consist of a single UTF-8 character.
881 ** Otherwise, return an error.
883 const unsigned char *zEsc
= sqlite3_value_text(argv
[2]);
884 if( zEsc
==0 ) return;
885 if( sqlite3Utf8CharLen((char*)zEsc
, -1)!=1 ){
886 sqlite3_result_error(context
,
887 "ESCAPE expression must be a single character", -1);
890 escape
= sqlite3Utf8Read(&zEsc
);
892 escape
= pInfo
->matchSet
;
894 zB
= sqlite3_value_text(argv
[0]);
895 zA
= sqlite3_value_text(argv
[1]);
898 sqlite3_like_count
++;
900 sqlite3_result_int(context
,
901 patternCompare(zB
, zA
, pInfo
, escape
)==SQLITE_MATCH
);
906 ** Implementation of the NULLIF(x,y) function. The result is the first
907 ** argument if the arguments are different. The result is NULL if the
908 ** arguments are equal to each other.
910 static void nullifFunc(
911 sqlite3_context
*context
,
915 CollSeq
*pColl
= sqlite3GetFuncCollSeq(context
);
916 UNUSED_PARAMETER(NotUsed
);
917 if( sqlite3MemCompare(argv
[0], argv
[1], pColl
)!=0 ){
918 sqlite3_result_value(context
, argv
[0]);
923 ** Implementation of the sqlite_version() function. The result is the version
924 ** of the SQLite library that is running.
926 static void versionFunc(
927 sqlite3_context
*context
,
929 sqlite3_value
**NotUsed2
931 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
932 /* IMP: R-48699-48617 This function is an SQL wrapper around the
933 ** sqlite3_libversion() C-interface. */
934 sqlite3_result_text(context
, sqlite3_libversion(), -1, SQLITE_STATIC
);
938 ** Implementation of the sqlite_source_id() function. The result is a string
939 ** that identifies the particular version of the source code used to build
942 static void sourceidFunc(
943 sqlite3_context
*context
,
945 sqlite3_value
**NotUsed2
947 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
948 /* IMP: R-24470-31136 This function is an SQL wrapper around the
949 ** sqlite3_sourceid() C interface. */
950 sqlite3_result_text(context
, sqlite3_sourceid(), -1, SQLITE_STATIC
);
954 ** Implementation of the sqlite_log() function. This is a wrapper around
955 ** sqlite3_log(). The return value is NULL. The function exists purely for
958 static void errlogFunc(
959 sqlite3_context
*context
,
963 UNUSED_PARAMETER(argc
);
964 UNUSED_PARAMETER(context
);
965 sqlite3_log(sqlite3_value_int(argv
[0]), "%s", sqlite3_value_text(argv
[1]));
969 ** Implementation of the sqlite_compileoption_used() function.
970 ** The result is an integer that identifies if the compiler option
971 ** was used to build SQLite.
973 #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS
974 static void compileoptionusedFunc(
975 sqlite3_context
*context
,
979 const char *zOptName
;
981 UNUSED_PARAMETER(argc
);
982 /* IMP: R-39564-36305 The sqlite_compileoption_used() SQL
983 ** function is a wrapper around the sqlite3_compileoption_used() C/C++
986 if( (zOptName
= (const char*)sqlite3_value_text(argv
[0]))!=0 ){
987 sqlite3_result_int(context
, sqlite3_compileoption_used(zOptName
));
990 #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
993 ** Implementation of the sqlite_compileoption_get() function.
994 ** The result is a string that identifies the compiler options
995 ** used to build SQLite.
997 #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS
998 static void compileoptiongetFunc(
999 sqlite3_context
*context
,
1001 sqlite3_value
**argv
1005 UNUSED_PARAMETER(argc
);
1006 /* IMP: R-04922-24076 The sqlite_compileoption_get() SQL function
1007 ** is a wrapper around the sqlite3_compileoption_get() C/C++ function.
1009 n
= sqlite3_value_int(argv
[0]);
1010 sqlite3_result_text(context
, sqlite3_compileoption_get(n
), -1, SQLITE_STATIC
);
1012 #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
1014 /* Array for converting from half-bytes (nybbles) into ASCII hex
1016 static const char hexdigits
[] = {
1017 '0', '1', '2', '3', '4', '5', '6', '7',
1018 '8', '9', 'A', 'B', 'C', 'D', 'E', 'F'
1022 ** Implementation of the QUOTE() function. This function takes a single
1023 ** argument. If the argument is numeric, the return value is the same as
1024 ** the argument. If the argument is NULL, the return value is the string
1025 ** "NULL". Otherwise, the argument is enclosed in single quotes with
1026 ** single-quote escapes.
1028 static void quoteFunc(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
1030 UNUSED_PARAMETER(argc
);
1031 switch( sqlite3_value_type(argv
[0]) ){
1032 case SQLITE_FLOAT
: {
1035 r1
= sqlite3_value_double(argv
[0]);
1036 sqlite3_snprintf(sizeof(zBuf
), zBuf
, "%!.15g", r1
);
1037 sqlite3AtoF(zBuf
, &r2
, 20, SQLITE_UTF8
);
1039 sqlite3_snprintf(sizeof(zBuf
), zBuf
, "%!.20e", r1
);
1041 sqlite3_result_text(context
, zBuf
, -1, SQLITE_TRANSIENT
);
1044 case SQLITE_INTEGER
: {
1045 sqlite3_result_value(context
, argv
[0]);
1050 char const *zBlob
= sqlite3_value_blob(argv
[0]);
1051 int nBlob
= sqlite3_value_bytes(argv
[0]);
1052 assert( zBlob
==sqlite3_value_blob(argv
[0]) ); /* No encoding change */
1053 zText
= (char *)contextMalloc(context
, (2*(i64
)nBlob
)+4);
1056 for(i
=0; i
<nBlob
; i
++){
1057 zText
[(i
*2)+2] = hexdigits
[(zBlob
[i
]>>4)&0x0F];
1058 zText
[(i
*2)+3] = hexdigits
[(zBlob
[i
])&0x0F];
1060 zText
[(nBlob
*2)+2] = '\'';
1061 zText
[(nBlob
*2)+3] = '\0';
1064 sqlite3_result_text(context
, zText
, -1, SQLITE_TRANSIENT
);
1065 sqlite3_free(zText
);
1072 const unsigned char *zArg
= sqlite3_value_text(argv
[0]);
1075 if( zArg
==0 ) return;
1076 for(i
=0, n
=0; zArg
[i
]; i
++){ if( zArg
[i
]=='\'' ) n
++; }
1077 z
= contextMalloc(context
, ((i64
)i
)+((i64
)n
)+3);
1080 for(i
=0, j
=1; zArg
[i
]; i
++){
1082 if( zArg
[i
]=='\'' ){
1088 sqlite3_result_text(context
, z
, j
, sqlite3_free
);
1093 assert( sqlite3_value_type(argv
[0])==SQLITE_NULL
);
1094 sqlite3_result_text(context
, "NULL", 4, SQLITE_STATIC
);
1101 ** The unicode() function. Return the integer unicode code-point value
1102 ** for the first character of the input string.
1104 static void unicodeFunc(
1105 sqlite3_context
*context
,
1107 sqlite3_value
**argv
1109 const unsigned char *z
= sqlite3_value_text(argv
[0]);
1111 if( z
&& z
[0] ) sqlite3_result_int(context
, sqlite3Utf8Read(&z
));
1115 ** The char() function takes zero or more arguments, each of which is
1116 ** an integer. It constructs a string where each character of the string
1117 ** is the unicode character for the corresponding integer argument.
1119 static void charFunc(
1120 sqlite3_context
*context
,
1122 sqlite3_value
**argv
1124 unsigned char *z
, *zOut
;
1126 zOut
= z
= sqlite3_malloc64( argc
*4+1 );
1128 sqlite3_result_error_nomem(context
);
1131 for(i
=0; i
<argc
; i
++){
1134 x
= sqlite3_value_int64(argv
[i
]);
1135 if( x
<0 || x
>0x10ffff ) x
= 0xfffd;
1136 c
= (unsigned)(x
& 0x1fffff);
1138 *zOut
++ = (u8
)(c
&0xFF);
1139 }else if( c
<0x00800 ){
1140 *zOut
++ = 0xC0 + (u8
)((c
>>6)&0x1F);
1141 *zOut
++ = 0x80 + (u8
)(c
& 0x3F);
1142 }else if( c
<0x10000 ){
1143 *zOut
++ = 0xE0 + (u8
)((c
>>12)&0x0F);
1144 *zOut
++ = 0x80 + (u8
)((c
>>6) & 0x3F);
1145 *zOut
++ = 0x80 + (u8
)(c
& 0x3F);
1147 *zOut
++ = 0xF0 + (u8
)((c
>>18) & 0x07);
1148 *zOut
++ = 0x80 + (u8
)((c
>>12) & 0x3F);
1149 *zOut
++ = 0x80 + (u8
)((c
>>6) & 0x3F);
1150 *zOut
++ = 0x80 + (u8
)(c
& 0x3F);
1153 sqlite3_result_text64(context
, (char*)z
, zOut
-z
, sqlite3_free
, SQLITE_UTF8
);
1157 ** The hex() function. Interpret the argument as a blob. Return
1158 ** a hexadecimal rendering as text.
1160 static void hexFunc(
1161 sqlite3_context
*context
,
1163 sqlite3_value
**argv
1166 const unsigned char *pBlob
;
1169 UNUSED_PARAMETER(argc
);
1170 pBlob
= sqlite3_value_blob(argv
[0]);
1171 n
= sqlite3_value_bytes(argv
[0]);
1172 assert( pBlob
==sqlite3_value_blob(argv
[0]) ); /* No encoding change */
1173 z
= zHex
= contextMalloc(context
, ((i64
)n
)*2 + 1);
1175 for(i
=0; i
<n
; i
++, pBlob
++){
1176 unsigned char c
= *pBlob
;
1177 *(z
++) = hexdigits
[(c
>>4)&0xf];
1178 *(z
++) = hexdigits
[c
&0xf];
1181 sqlite3_result_text(context
, zHex
, n
*2, sqlite3_free
);
1186 ** The zeroblob(N) function returns a zero-filled blob of size N bytes.
1188 static void zeroblobFunc(
1189 sqlite3_context
*context
,
1191 sqlite3_value
**argv
1196 UNUSED_PARAMETER(argc
);
1197 n
= sqlite3_value_int64(argv
[0]);
1199 rc
= sqlite3_result_zeroblob64(context
, n
); /* IMP: R-00293-64994 */
1201 sqlite3_result_error_code(context
, rc
);
1206 ** The replace() function. Three arguments are all strings: call
1207 ** them A, B, and C. The result is also a string which is derived
1208 ** from A by replacing every occurrence of B with C. The match
1209 ** must be exact. Collating sequences are not used.
1211 static void replaceFunc(
1212 sqlite3_context
*context
,
1214 sqlite3_value
**argv
1216 const unsigned char *zStr
; /* The input string A */
1217 const unsigned char *zPattern
; /* The pattern string B */
1218 const unsigned char *zRep
; /* The replacement string C */
1219 unsigned char *zOut
; /* The output */
1220 int nStr
; /* Size of zStr */
1221 int nPattern
; /* Size of zPattern */
1222 int nRep
; /* Size of zRep */
1223 i64 nOut
; /* Maximum size of zOut */
1224 int loopLimit
; /* Last zStr[] that might match zPattern[] */
1225 int i
, j
; /* Loop counters */
1226 unsigned cntExpand
; /* Number zOut expansions */
1227 sqlite3
*db
= sqlite3_context_db_handle(context
);
1230 UNUSED_PARAMETER(argc
);
1231 zStr
= sqlite3_value_text(argv
[0]);
1232 if( zStr
==0 ) return;
1233 nStr
= sqlite3_value_bytes(argv
[0]);
1234 assert( zStr
==sqlite3_value_text(argv
[0]) ); /* No encoding change */
1235 zPattern
= sqlite3_value_text(argv
[1]);
1237 assert( sqlite3_value_type(argv
[1])==SQLITE_NULL
1238 || sqlite3_context_db_handle(context
)->mallocFailed
);
1241 if( zPattern
[0]==0 ){
1242 assert( sqlite3_value_type(argv
[1])!=SQLITE_NULL
);
1243 sqlite3_result_value(context
, argv
[0]);
1246 nPattern
= sqlite3_value_bytes(argv
[1]);
1247 assert( zPattern
==sqlite3_value_text(argv
[1]) ); /* No encoding change */
1248 zRep
= sqlite3_value_text(argv
[2]);
1249 if( zRep
==0 ) return;
1250 nRep
= sqlite3_value_bytes(argv
[2]);
1251 assert( zRep
==sqlite3_value_text(argv
[2]) );
1253 assert( nOut
<SQLITE_MAX_LENGTH
);
1254 zOut
= contextMalloc(context
, (i64
)nOut
);
1258 loopLimit
= nStr
- nPattern
;
1260 for(i
=j
=0; i
<=loopLimit
; i
++){
1261 if( zStr
[i
]!=zPattern
[0] || memcmp(&zStr
[i
], zPattern
, nPattern
) ){
1262 zOut
[j
++] = zStr
[i
];
1264 if( nRep
>nPattern
){
1265 nOut
+= nRep
- nPattern
;
1266 testcase( nOut
-1==db
->aLimit
[SQLITE_LIMIT_LENGTH
] );
1267 testcase( nOut
-2==db
->aLimit
[SQLITE_LIMIT_LENGTH
] );
1268 if( nOut
-1>db
->aLimit
[SQLITE_LIMIT_LENGTH
] ){
1269 sqlite3_result_error_toobig(context
);
1274 if( (cntExpand
&(cntExpand
-1))==0 ){
1275 /* Grow the size of the output buffer only on substitutions
1276 ** whose index is a power of two: 1, 2, 4, 8, 16, 32, ... */
1279 zOut
= sqlite3_realloc64(zOut
, (int)nOut
+ (nOut
- nStr
- 1));
1281 sqlite3_result_error_nomem(context
);
1287 memcpy(&zOut
[j
], zRep
, nRep
);
1292 assert( j
+nStr
-i
+1<=nOut
);
1293 memcpy(&zOut
[j
], &zStr
[i
], nStr
-i
);
1297 sqlite3_result_text(context
, (char*)zOut
, j
, sqlite3_free
);
1301 ** Implementation of the TRIM(), LTRIM(), and RTRIM() functions.
1302 ** The userdata is 0x1 for left trim, 0x2 for right trim, 0x3 for both.
1304 static void trimFunc(
1305 sqlite3_context
*context
,
1307 sqlite3_value
**argv
1309 const unsigned char *zIn
; /* Input string */
1310 const unsigned char *zCharSet
; /* Set of characters to trim */
1311 int nIn
; /* Number of bytes in input */
1312 int flags
; /* 1: trimleft 2: trimright 3: trim */
1313 int i
; /* Loop counter */
1314 unsigned char *aLen
= 0; /* Length of each character in zCharSet */
1315 unsigned char **azChar
= 0; /* Individual characters in zCharSet */
1316 int nChar
; /* Number of characters in zCharSet */
1318 if( sqlite3_value_type(argv
[0])==SQLITE_NULL
){
1321 zIn
= sqlite3_value_text(argv
[0]);
1322 if( zIn
==0 ) return;
1323 nIn
= sqlite3_value_bytes(argv
[0]);
1324 assert( zIn
==sqlite3_value_text(argv
[0]) );
1326 static const unsigned char lenOne
[] = { 1 };
1327 static unsigned char * const azOne
[] = { (u8
*)" " };
1330 azChar
= (unsigned char **)azOne
;
1332 }else if( (zCharSet
= sqlite3_value_text(argv
[1]))==0 ){
1335 const unsigned char *z
;
1336 for(z
=zCharSet
, nChar
=0; *z
; nChar
++){
1337 SQLITE_SKIP_UTF8(z
);
1340 azChar
= contextMalloc(context
, ((i64
)nChar
)*(sizeof(char*)+1));
1344 aLen
= (unsigned char*)&azChar
[nChar
];
1345 for(z
=zCharSet
, nChar
=0; *z
; nChar
++){
1346 azChar
[nChar
] = (unsigned char *)z
;
1347 SQLITE_SKIP_UTF8(z
);
1348 aLen
[nChar
] = (u8
)(z
- azChar
[nChar
]);
1353 flags
= SQLITE_PTR_TO_INT(sqlite3_user_data(context
));
1357 for(i
=0; i
<nChar
; i
++){
1359 if( len
<=nIn
&& memcmp(zIn
, azChar
[i
], len
)==0 ) break;
1361 if( i
>=nChar
) break;
1369 for(i
=0; i
<nChar
; i
++){
1371 if( len
<=nIn
&& memcmp(&zIn
[nIn
-len
],azChar
[i
],len
)==0 ) break;
1373 if( i
>=nChar
) break;
1378 sqlite3_free(azChar
);
1381 sqlite3_result_text(context
, (char*)zIn
, nIn
, SQLITE_TRANSIENT
);
1385 #ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
1387 ** The "unknown" function is automatically substituted in place of
1388 ** any unrecognized function name when doing an EXPLAIN or EXPLAIN QUERY PLAN
1389 ** when the SQLITE_ENABLE_UNKNOWN_FUNCTION compile-time option is used.
1390 ** When the "sqlite3" command-line shell is built using this functionality,
1391 ** that allows an EXPLAIN or EXPLAIN QUERY PLAN for complex queries
1392 ** involving application-defined functions to be examined in a generic
1395 static void unknownFunc(
1396 sqlite3_context
*context
,
1398 sqlite3_value
**argv
1402 #endif /*SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION*/
1405 /* IMP: R-25361-16150 This function is omitted from SQLite by default. It
1406 ** is only available if the SQLITE_SOUNDEX compile-time option is used
1407 ** when SQLite is built.
1409 #ifdef SQLITE_SOUNDEX
1411 ** Compute the soundex encoding of a word.
1413 ** IMP: R-59782-00072 The soundex(X) function returns a string that is the
1414 ** soundex encoding of the string X.
1416 static void soundexFunc(
1417 sqlite3_context
*context
,
1419 sqlite3_value
**argv
1424 static const unsigned char iCode
[] = {
1425 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1426 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1427 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1428 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1429 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
1430 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
1431 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
1432 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
1435 zIn
= (u8
*)sqlite3_value_text(argv
[0]);
1436 if( zIn
==0 ) zIn
= (u8
*)"";
1437 for(i
=0; zIn
[i
] && !sqlite3Isalpha(zIn
[i
]); i
++){}
1439 u8 prevcode
= iCode
[zIn
[i
]&0x7f];
1440 zResult
[0] = sqlite3Toupper(zIn
[i
]);
1441 for(j
=1; j
<4 && zIn
[i
]; i
++){
1442 int code
= iCode
[zIn
[i
]&0x7f];
1444 if( code
!=prevcode
){
1446 zResult
[j
++] = code
+ '0';
1456 sqlite3_result_text(context
, zResult
, 4, SQLITE_TRANSIENT
);
1458 /* IMP: R-64894-50321 The string "?000" is returned if the argument
1459 ** is NULL or contains no ASCII alphabetic characters. */
1460 sqlite3_result_text(context
, "?000", 4, SQLITE_STATIC
);
1463 #endif /* SQLITE_SOUNDEX */
1465 #ifndef SQLITE_OMIT_LOAD_EXTENSION
1467 ** A function that loads a shared-library extension then returns NULL.
1469 static void loadExt(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
1470 const char *zFile
= (const char *)sqlite3_value_text(argv
[0]);
1472 sqlite3
*db
= sqlite3_context_db_handle(context
);
1475 /* Disallow the load_extension() SQL function unless the SQLITE_LoadExtFunc
1476 ** flag is set. See the sqlite3_enable_load_extension() API.
1478 if( (db
->flags
& SQLITE_LoadExtFunc
)==0 ){
1479 sqlite3_result_error(context
, "not authorized", -1);
1484 zProc
= (const char *)sqlite3_value_text(argv
[1]);
1488 if( zFile
&& sqlite3_load_extension(db
, zFile
, zProc
, &zErrMsg
) ){
1489 sqlite3_result_error(context
, zErrMsg
, -1);
1490 sqlite3_free(zErrMsg
);
1497 ** An instance of the following structure holds the context of a
1498 ** sum() or avg() aggregate computation.
1500 typedef struct SumCtx SumCtx
;
1502 double rSum
; /* Floating point sum */
1503 i64 iSum
; /* Integer sum */
1504 i64 cnt
; /* Number of elements summed */
1505 u8 overflow
; /* True if integer overflow seen */
1506 u8 approx
; /* True if non-integer value was input to the sum */
1510 ** Routines used to compute the sum, average, and total.
1512 ** The SUM() function follows the (broken) SQL standard which means
1513 ** that it returns NULL if it sums over no inputs. TOTAL returns
1514 ** 0.0 in that case. In addition, TOTAL always returns a float where
1515 ** SUM might return an integer if it never encounters a floating point
1516 ** value. TOTAL never fails, but SUM might through an exception if
1517 ** it overflows an integer.
1519 static void sumStep(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
1523 UNUSED_PARAMETER(argc
);
1524 p
= sqlite3_aggregate_context(context
, sizeof(*p
));
1525 type
= sqlite3_value_numeric_type(argv
[0]);
1526 if( p
&& type
!=SQLITE_NULL
){
1528 if( type
==SQLITE_INTEGER
){
1529 i64 v
= sqlite3_value_int64(argv
[0]);
1531 if( (p
->approx
|p
->overflow
)==0 && sqlite3AddInt64(&p
->iSum
, v
) ){
1532 p
->approx
= p
->overflow
= 1;
1535 p
->rSum
+= sqlite3_value_double(argv
[0]);
1540 #ifndef SQLITE_OMIT_WINDOWFUNC
1541 static void sumInverse(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
1545 UNUSED_PARAMETER(argc
);
1546 p
= sqlite3_aggregate_context(context
, sizeof(*p
));
1547 type
= sqlite3_value_numeric_type(argv
[0]);
1548 /* p is always non-NULL because sumStep() will have been called first
1549 ** to initialize it */
1550 if( ALWAYS(p
) && type
!=SQLITE_NULL
){
1553 assert( type
==SQLITE_INTEGER
|| p
->approx
);
1554 if( type
==SQLITE_INTEGER
&& p
->approx
==0 ){
1555 i64 v
= sqlite3_value_int64(argv
[0]);
1559 p
->rSum
-= sqlite3_value_double(argv
[0]);
1564 # define sumInverse 0
1565 #endif /* SQLITE_OMIT_WINDOWFUNC */
1566 static void sumFinalize(sqlite3_context
*context
){
1568 p
= sqlite3_aggregate_context(context
, 0);
1569 if( p
&& p
->cnt
>0 ){
1571 sqlite3_result_error(context
,"integer overflow",-1);
1572 }else if( p
->approx
){
1573 sqlite3_result_double(context
, p
->rSum
);
1575 sqlite3_result_int64(context
, p
->iSum
);
1579 static void avgFinalize(sqlite3_context
*context
){
1581 p
= sqlite3_aggregate_context(context
, 0);
1582 if( p
&& p
->cnt
>0 ){
1583 sqlite3_result_double(context
, p
->rSum
/(double)p
->cnt
);
1586 static void totalFinalize(sqlite3_context
*context
){
1588 p
= sqlite3_aggregate_context(context
, 0);
1589 /* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
1590 sqlite3_result_double(context
, p
? p
->rSum
: (double)0);
1594 ** The following structure keeps track of state information for the
1595 ** count() aggregate function.
1597 typedef struct CountCtx CountCtx
;
1601 int bInverse
; /* True if xInverse() ever called */
1606 ** Routines to implement the count() aggregate function.
1608 static void countStep(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
1610 p
= sqlite3_aggregate_context(context
, sizeof(*p
));
1611 if( (argc
==0 || SQLITE_NULL
!=sqlite3_value_type(argv
[0])) && p
){
1615 #ifndef SQLITE_OMIT_DEPRECATED
1616 /* The sqlite3_aggregate_count() function is deprecated. But just to make
1617 ** sure it still operates correctly, verify that its count agrees with our
1618 ** internal count when using count(*) and when the total count can be
1619 ** expressed as a 32-bit integer. */
1620 assert( argc
==1 || p
==0 || p
->n
>0x7fffffff || p
->bInverse
1621 || p
->n
==sqlite3_aggregate_count(context
) );
1624 static void countFinalize(sqlite3_context
*context
){
1626 p
= sqlite3_aggregate_context(context
, 0);
1627 sqlite3_result_int64(context
, p
? p
->n
: 0);
1629 #ifndef SQLITE_OMIT_WINDOWFUNC
1630 static void countInverse(sqlite3_context
*ctx
, int argc
, sqlite3_value
**argv
){
1632 p
= sqlite3_aggregate_context(ctx
, sizeof(*p
));
1633 /* p is always non-NULL since countStep() will have been called first */
1634 if( (argc
==0 || SQLITE_NULL
!=sqlite3_value_type(argv
[0])) && ALWAYS(p
) ){
1642 # define countInverse 0
1643 #endif /* SQLITE_OMIT_WINDOWFUNC */
1646 ** Routines to implement min() and max() aggregate functions.
1648 static void minmaxStep(
1649 sqlite3_context
*context
,
1651 sqlite3_value
**argv
1653 Mem
*pArg
= (Mem
*)argv
[0];
1655 UNUSED_PARAMETER(NotUsed
);
1657 pBest
= (Mem
*)sqlite3_aggregate_context(context
, sizeof(*pBest
));
1658 if( !pBest
) return;
1660 if( sqlite3_value_type(pArg
)==SQLITE_NULL
){
1661 if( pBest
->flags
) sqlite3SkipAccumulatorLoad(context
);
1662 }else if( pBest
->flags
){
1665 CollSeq
*pColl
= sqlite3GetFuncCollSeq(context
);
1666 /* This step function is used for both the min() and max() aggregates,
1667 ** the only difference between the two being that the sense of the
1668 ** comparison is inverted. For the max() aggregate, the
1669 ** sqlite3_user_data() function returns (void *)-1. For min() it
1670 ** returns (void *)db, where db is the sqlite3* database pointer.
1671 ** Therefore the next statement sets variable 'max' to 1 for the max()
1672 ** aggregate, or 0 for min().
1674 max
= sqlite3_user_data(context
)!=0;
1675 cmp
= sqlite3MemCompare(pBest
, pArg
, pColl
);
1676 if( (max
&& cmp
<0) || (!max
&& cmp
>0) ){
1677 sqlite3VdbeMemCopy(pBest
, pArg
);
1679 sqlite3SkipAccumulatorLoad(context
);
1682 pBest
->db
= sqlite3_context_db_handle(context
);
1683 sqlite3VdbeMemCopy(pBest
, pArg
);
1686 static void minMaxValueFinalize(sqlite3_context
*context
, int bValue
){
1687 sqlite3_value
*pRes
;
1688 pRes
= (sqlite3_value
*)sqlite3_aggregate_context(context
, 0);
1691 sqlite3_result_value(context
, pRes
);
1693 if( bValue
==0 ) sqlite3VdbeMemRelease(pRes
);
1696 #ifndef SQLITE_OMIT_WINDOWFUNC
1697 static void minMaxValue(sqlite3_context
*context
){
1698 minMaxValueFinalize(context
, 1);
1701 # define minMaxValue 0
1702 #endif /* SQLITE_OMIT_WINDOWFUNC */
1703 static void minMaxFinalize(sqlite3_context
*context
){
1704 minMaxValueFinalize(context
, 0);
1708 ** group_concat(EXPR, ?SEPARATOR?)
1710 static void groupConcatStep(
1711 sqlite3_context
*context
,
1713 sqlite3_value
**argv
1719 assert( argc
==1 || argc
==2 );
1720 if( sqlite3_value_type(argv
[0])==SQLITE_NULL
) return;
1721 pAccum
= (StrAccum
*)sqlite3_aggregate_context(context
, sizeof(*pAccum
));
1724 sqlite3
*db
= sqlite3_context_db_handle(context
);
1725 int firstTerm
= pAccum
->mxAlloc
==0;
1726 pAccum
->mxAlloc
= db
->aLimit
[SQLITE_LIMIT_LENGTH
];
1729 zSep
= (char*)sqlite3_value_text(argv
[1]);
1730 nSep
= sqlite3_value_bytes(argv
[1]);
1735 if( zSep
) sqlite3_str_append(pAccum
, zSep
, nSep
);
1737 zVal
= (char*)sqlite3_value_text(argv
[0]);
1738 nVal
= sqlite3_value_bytes(argv
[0]);
1739 if( zVal
) sqlite3_str_append(pAccum
, zVal
, nVal
);
1742 #ifndef SQLITE_OMIT_WINDOWFUNC
1743 static void groupConcatInverse(
1744 sqlite3_context
*context
,
1746 sqlite3_value
**argv
1750 assert( argc
==1 || argc
==2 );
1751 if( sqlite3_value_type(argv
[0])==SQLITE_NULL
) return;
1752 pAccum
= (StrAccum
*)sqlite3_aggregate_context(context
, sizeof(*pAccum
));
1753 /* pAccum is always non-NULL since groupConcatStep() will have always
1754 ** run frist to initialize it */
1755 if( ALWAYS(pAccum
) ){
1756 n
= sqlite3_value_bytes(argv
[0]);
1758 n
+= sqlite3_value_bytes(argv
[1]);
1762 if( n
>=(int)pAccum
->nChar
){
1766 memmove(pAccum
->zText
, &pAccum
->zText
[n
], pAccum
->nChar
);
1768 if( pAccum
->nChar
==0 ) pAccum
->mxAlloc
= 0;
1772 # define groupConcatInverse 0
1773 #endif /* SQLITE_OMIT_WINDOWFUNC */
1774 static void groupConcatFinalize(sqlite3_context
*context
){
1776 pAccum
= sqlite3_aggregate_context(context
, 0);
1778 if( pAccum
->accError
==SQLITE_TOOBIG
){
1779 sqlite3_result_error_toobig(context
);
1780 }else if( pAccum
->accError
==SQLITE_NOMEM
){
1781 sqlite3_result_error_nomem(context
);
1783 sqlite3_result_text(context
, sqlite3StrAccumFinish(pAccum
), -1,
1788 #ifndef SQLITE_OMIT_WINDOWFUNC
1789 static void groupConcatValue(sqlite3_context
*context
){
1790 sqlite3_str
*pAccum
;
1791 pAccum
= (sqlite3_str
*)sqlite3_aggregate_context(context
, 0);
1793 if( pAccum
->accError
==SQLITE_TOOBIG
){
1794 sqlite3_result_error_toobig(context
);
1795 }else if( pAccum
->accError
==SQLITE_NOMEM
){
1796 sqlite3_result_error_nomem(context
);
1798 const char *zText
= sqlite3_str_value(pAccum
);
1799 sqlite3_result_text(context
, zText
, -1, SQLITE_TRANSIENT
);
1804 # define groupConcatValue 0
1805 #endif /* SQLITE_OMIT_WINDOWFUNC */
1808 ** This routine does per-connection function registration. Most
1809 ** of the built-in functions above are part of the global function set.
1810 ** This routine only deals with those that are not global.
1812 void sqlite3RegisterPerConnectionBuiltinFunctions(sqlite3
*db
){
1813 int rc
= sqlite3_overload_function(db
, "MATCH", 2);
1814 assert( rc
==SQLITE_NOMEM
|| rc
==SQLITE_OK
);
1815 if( rc
==SQLITE_NOMEM
){
1816 sqlite3OomFault(db
);
1821 ** Re-register the built-in LIKE functions. The caseSensitive
1822 ** parameter determines whether or not the LIKE operator is case
1825 void sqlite3RegisterLikeFunctions(sqlite3
*db
, int caseSensitive
){
1826 struct compareInfo
*pInfo
;
1828 if( caseSensitive
){
1829 pInfo
= (struct compareInfo
*)&likeInfoAlt
;
1830 flags
= SQLITE_FUNC_LIKE
| SQLITE_FUNC_CASE
;
1832 pInfo
= (struct compareInfo
*)&likeInfoNorm
;
1833 flags
= SQLITE_FUNC_LIKE
;
1835 sqlite3CreateFunc(db
, "like", 2, SQLITE_UTF8
, pInfo
, likeFunc
, 0, 0, 0, 0, 0);
1836 sqlite3CreateFunc(db
, "like", 3, SQLITE_UTF8
, pInfo
, likeFunc
, 0, 0, 0, 0, 0);
1837 sqlite3FindFunction(db
, "like", 2, SQLITE_UTF8
, 0)->funcFlags
|= flags
;
1838 sqlite3FindFunction(db
, "like", 3, SQLITE_UTF8
, 0)->funcFlags
|= flags
;
1842 ** pExpr points to an expression which implements a function. If
1843 ** it is appropriate to apply the LIKE optimization to that function
1844 ** then set aWc[0] through aWc[2] to the wildcard characters and the
1845 ** escape character and then return TRUE. If the function is not a
1846 ** LIKE-style function then return FALSE.
1848 ** The expression "a LIKE b ESCAPE c" is only considered a valid LIKE
1849 ** operator if c is a string literal that is exactly one byte in length.
1850 ** That one byte is stored in aWc[3]. aWc[3] is set to zero if there is
1851 ** no ESCAPE clause.
1853 ** *pIsNocase is set to true if uppercase and lowercase are equivalent for
1854 ** the function (default for LIKE). If the function makes the distinction
1855 ** between uppercase and lowercase (as does GLOB) then *pIsNocase is set to
1858 int sqlite3IsLikeFunction(sqlite3
*db
, Expr
*pExpr
, int *pIsNocase
, char *aWc
){
1861 if( pExpr
->op
!=TK_FUNCTION
|| !pExpr
->x
.pList
){
1864 assert( !ExprHasProperty(pExpr
, EP_xIsSelect
) );
1865 nExpr
= pExpr
->x
.pList
->nExpr
;
1866 pDef
= sqlite3FindFunction(db
, pExpr
->u
.zToken
, nExpr
, SQLITE_UTF8
, 0);
1867 #ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
1868 if( pDef
==0 ) return 0;
1870 if( NEVER(pDef
==0) || (pDef
->funcFlags
& SQLITE_FUNC_LIKE
)==0 ){
1876 Expr
*pEscape
= pExpr
->x
.pList
->a
[2].pExpr
;
1878 if( pEscape
->op
!=TK_STRING
) return 0;
1879 zEscape
= pEscape
->u
.zToken
;
1880 if( zEscape
[0]==0 || zEscape
[1]!=0 ) return 0;
1881 aWc
[3] = zEscape
[0];
1884 /* The memcpy() statement assumes that the wildcard characters are
1885 ** the first three statements in the compareInfo structure. The
1886 ** asserts() that follow verify that assumption
1888 memcpy(aWc
, pDef
->pUserData
, 3);
1889 assert( (char*)&likeInfoAlt
== (char*)&likeInfoAlt
.matchAll
);
1890 assert( &((char*)&likeInfoAlt
)[1] == (char*)&likeInfoAlt
.matchOne
);
1891 assert( &((char*)&likeInfoAlt
)[2] == (char*)&likeInfoAlt
.matchSet
);
1892 *pIsNocase
= (pDef
->funcFlags
& SQLITE_FUNC_CASE
)==0;
1897 ** All of the FuncDef structures in the aBuiltinFunc[] array above
1898 ** to the global function hash table. This occurs at start-time (as
1899 ** a consequence of calling sqlite3_initialize()).
1901 ** After this routine runs
1903 void sqlite3RegisterBuiltinFunctions(void){
1905 ** The following array holds FuncDef structures for all of the functions
1906 ** defined in this file.
1908 ** The array cannot be constant since changes are made to the
1909 ** FuncDef.pHash elements at start-time. The elements of this array
1910 ** are read-only after initialization is complete.
1912 ** For peak efficiency, put the most frequently used function last.
1914 static FuncDef aBuiltinFunc
[] = {
1915 /***** Functions only available with SQLITE_TESTCTRL_INTERNAL_FUNCTIONS *****/
1916 TEST_FUNC(implies_nonnull_row
, 2, INLINEFUNC_implies_nonnull_row
, 0),
1917 TEST_FUNC(expr_compare
, 2, INLINEFUNC_expr_compare
, 0),
1918 TEST_FUNC(expr_implies_expr
, 2, INLINEFUNC_expr_implies_expr
, 0),
1920 TEST_FUNC(affinity
, 1, INLINEFUNC_affinity
, 0),
1922 /***** Regular functions *****/
1923 #ifdef SQLITE_SOUNDEX
1924 FUNCTION(soundex
, 1, 0, 0, soundexFunc
),
1926 #ifndef SQLITE_OMIT_LOAD_EXTENSION
1927 SFUNCTION(load_extension
, 1, 0, 0, loadExt
),
1928 SFUNCTION(load_extension
, 2, 0, 0, loadExt
),
1930 #if SQLITE_USER_AUTHENTICATION
1931 FUNCTION(sqlite_crypt
, 2, 0, 0, sqlite3CryptFunc
),
1933 #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS
1934 DFUNCTION(sqlite_compileoption_used
,1, 0, 0, compileoptionusedFunc
),
1935 DFUNCTION(sqlite_compileoption_get
, 1, 0, 0, compileoptiongetFunc
),
1936 #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
1937 INLINE_FUNC(unlikely
, 1, INLINEFUNC_unlikely
, SQLITE_FUNC_UNLIKELY
),
1938 INLINE_FUNC(likelihood
, 2, INLINEFUNC_unlikely
, SQLITE_FUNC_UNLIKELY
),
1939 INLINE_FUNC(likely
, 1, INLINEFUNC_unlikely
, SQLITE_FUNC_UNLIKELY
),
1940 #ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
1941 FUNCTION2(sqlite_offset
, 1, 0, 0, noopFunc
, SQLITE_FUNC_OFFSET
|
1942 SQLITE_FUNC_TYPEOF
),
1944 FUNCTION(ltrim
, 1, 1, 0, trimFunc
),
1945 FUNCTION(ltrim
, 2, 1, 0, trimFunc
),
1946 FUNCTION(rtrim
, 1, 2, 0, trimFunc
),
1947 FUNCTION(rtrim
, 2, 2, 0, trimFunc
),
1948 FUNCTION(trim
, 1, 3, 0, trimFunc
),
1949 FUNCTION(trim
, 2, 3, 0, trimFunc
),
1950 FUNCTION(min
, -1, 0, 1, minmaxFunc
),
1951 FUNCTION(min
, 0, 0, 1, 0 ),
1952 WAGGREGATE(min
, 1, 0, 1, minmaxStep
, minMaxFinalize
, minMaxValue
, 0,
1953 SQLITE_FUNC_MINMAX
),
1954 FUNCTION(max
, -1, 1, 1, minmaxFunc
),
1955 FUNCTION(max
, 0, 1, 1, 0 ),
1956 WAGGREGATE(max
, 1, 1, 1, minmaxStep
, minMaxFinalize
, minMaxValue
, 0,
1957 SQLITE_FUNC_MINMAX
),
1958 FUNCTION2(typeof, 1, 0, 0, typeofFunc
, SQLITE_FUNC_TYPEOF
),
1959 FUNCTION2(length
, 1, 0, 0, lengthFunc
, SQLITE_FUNC_LENGTH
),
1960 FUNCTION(instr
, 2, 0, 0, instrFunc
),
1961 FUNCTION(printf
, -1, 0, 0, printfFunc
),
1962 FUNCTION(unicode
, 1, 0, 0, unicodeFunc
),
1963 FUNCTION(char, -1, 0, 0, charFunc
),
1964 FUNCTION(abs
, 1, 0, 0, absFunc
),
1965 #ifndef SQLITE_OMIT_FLOATING_POINT
1966 FUNCTION(round
, 1, 0, 0, roundFunc
),
1967 FUNCTION(round
, 2, 0, 0, roundFunc
),
1969 FUNCTION(upper
, 1, 0, 0, upperFunc
),
1970 FUNCTION(lower
, 1, 0, 0, lowerFunc
),
1971 FUNCTION(hex
, 1, 0, 0, hexFunc
),
1972 INLINE_FUNC(ifnull
, 2, INLINEFUNC_coalesce
, SQLITE_FUNC_COALESCE
),
1973 VFUNCTION(random
, 0, 0, 0, randomFunc
),
1974 VFUNCTION(randomblob
, 1, 0, 0, randomBlob
),
1975 FUNCTION(nullif
, 2, 0, 1, nullifFunc
),
1976 DFUNCTION(sqlite_version
, 0, 0, 0, versionFunc
),
1977 DFUNCTION(sqlite_source_id
, 0, 0, 0, sourceidFunc
),
1978 FUNCTION(sqlite_log
, 2, 0, 0, errlogFunc
),
1979 FUNCTION(quote
, 1, 0, 0, quoteFunc
),
1980 VFUNCTION(last_insert_rowid
, 0, 0, 0, last_insert_rowid
),
1981 VFUNCTION(changes
, 0, 0, 0, changes
),
1982 VFUNCTION(total_changes
, 0, 0, 0, total_changes
),
1983 FUNCTION(replace
, 3, 0, 0, replaceFunc
),
1984 FUNCTION(zeroblob
, 1, 0, 0, zeroblobFunc
),
1985 FUNCTION(substr
, 2, 0, 0, substrFunc
),
1986 FUNCTION(substr
, 3, 0, 0, substrFunc
),
1987 WAGGREGATE(sum
, 1,0,0, sumStep
, sumFinalize
, sumFinalize
, sumInverse
, 0),
1988 WAGGREGATE(total
, 1,0,0, sumStep
,totalFinalize
,totalFinalize
,sumInverse
, 0),
1989 WAGGREGATE(avg
, 1,0,0, sumStep
, avgFinalize
, avgFinalize
, sumInverse
, 0),
1990 WAGGREGATE(count
, 0,0,0, countStep
,
1991 countFinalize
, countFinalize
, countInverse
, SQLITE_FUNC_COUNT
),
1992 WAGGREGATE(count
, 1,0,0, countStep
,
1993 countFinalize
, countFinalize
, countInverse
, 0 ),
1994 WAGGREGATE(group_concat
, 1, 0, 0, groupConcatStep
,
1995 groupConcatFinalize
, groupConcatValue
, groupConcatInverse
, 0),
1996 WAGGREGATE(group_concat
, 2, 0, 0, groupConcatStep
,
1997 groupConcatFinalize
, groupConcatValue
, groupConcatInverse
, 0),
1999 LIKEFUNC(glob
, 2, &globInfo
, SQLITE_FUNC_LIKE
|SQLITE_FUNC_CASE
),
2000 #ifdef SQLITE_CASE_SENSITIVE_LIKE
2001 LIKEFUNC(like
, 2, &likeInfoAlt
, SQLITE_FUNC_LIKE
|SQLITE_FUNC_CASE
),
2002 LIKEFUNC(like
, 3, &likeInfoAlt
, SQLITE_FUNC_LIKE
|SQLITE_FUNC_CASE
),
2004 LIKEFUNC(like
, 2, &likeInfoNorm
, SQLITE_FUNC_LIKE
),
2005 LIKEFUNC(like
, 3, &likeInfoNorm
, SQLITE_FUNC_LIKE
),
2007 #ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
2008 FUNCTION(unknown
, -1, 0, 0, unknownFunc
),
2010 FUNCTION(coalesce
, 1, 0, 0, 0 ),
2011 FUNCTION(coalesce
, 0, 0, 0, 0 ),
2012 INLINE_FUNC(coalesce
, -1, INLINEFUNC_coalesce
, SQLITE_FUNC_COALESCE
),
2014 #ifndef SQLITE_OMIT_ALTERTABLE
2015 sqlite3AlterFunctions();
2017 sqlite3WindowFunctions();
2018 sqlite3RegisterDateTimeFunctions();
2019 sqlite3InsertBuiltinFuncs(aBuiltinFunc
, ArraySize(aBuiltinFunc
));
2021 #if 0 /* Enable to print out how the built-in functions are hashed */
2025 for(i
=0; i
<SQLITE_FUNC_HASH_SZ
; i
++){
2026 printf("FUNC-HASH %02d:", i
);
2027 for(p
=sqlite3BuiltinFunctions
.a
[i
]; p
; p
=p
->u
.pHash
){
2028 int n
= sqlite3Strlen30(p
->zName
);
2029 int h
= p
->zName
[0] + n
;
2030 printf(" %s(%d)", p
->zName
, h
);