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"
22 ** Return the collating function associated with a function.
24 static CollSeq
*sqlite3GetFuncCollSeq(sqlite3_context
*context
){
26 assert( context
->pVdbe
!=0 );
27 pOp
= &context
->pVdbe
->aOp
[context
->iOp
-1];
28 assert( pOp
->opcode
==OP_CollSeq
);
29 assert( pOp
->p4type
==P4_COLLSEQ
);
34 ** Indicate that the accumulator load should be skipped on this
35 ** iteration of the aggregate loop.
37 static void sqlite3SkipAccumulatorLoad(sqlite3_context
*context
){
38 assert( context
->isError
<=0 );
39 context
->isError
= -1;
40 context
->skipFlag
= 1;
44 ** Implementation of the non-aggregate min() and max() functions
46 static void minmaxFunc(
47 sqlite3_context
*context
,
52 int mask
; /* 0 for min() or 0xffffffff for max() */
57 mask
= sqlite3_user_data(context
)==0 ? 0 : -1;
58 pColl
= sqlite3GetFuncCollSeq(context
);
60 assert( mask
==-1 || mask
==0 );
62 if( sqlite3_value_type(argv
[0])==SQLITE_NULL
) return;
63 for(i
=1; i
<argc
; i
++){
64 if( sqlite3_value_type(argv
[i
])==SQLITE_NULL
) return;
65 if( (sqlite3MemCompare(argv
[iBest
], argv
[i
], pColl
)^mask
)>=0 ){
70 sqlite3_result_value(context
, argv
[iBest
]);
74 ** Return the type of the argument.
76 static void typeofFunc(
77 sqlite3_context
*context
,
81 static const char *azType
[] = { "integer", "real", "text", "blob", "null" };
82 int i
= sqlite3_value_type(argv
[0]) - 1;
83 UNUSED_PARAMETER(NotUsed
);
84 assert( i
>=0 && i
<ArraySize(azType
) );
85 assert( SQLITE_INTEGER
==1 );
86 assert( SQLITE_FLOAT
==2 );
87 assert( SQLITE_TEXT
==3 );
88 assert( SQLITE_BLOB
==4 );
89 assert( SQLITE_NULL
==5 );
90 /* EVIDENCE-OF: R-01470-60482 The sqlite3_value_type(V) interface returns
91 ** the datatype code for the initial datatype of the sqlite3_value object
92 ** V. The returned value is one of SQLITE_INTEGER, SQLITE_FLOAT,
93 ** SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. */
94 sqlite3_result_text(context
, azType
[i
], -1, SQLITE_STATIC
);
99 ** Implementation of the length() function
101 static void lengthFunc(
102 sqlite3_context
*context
,
107 UNUSED_PARAMETER(argc
);
108 switch( sqlite3_value_type(argv
[0]) ){
112 sqlite3_result_int(context
, sqlite3_value_bytes(argv
[0]));
116 const unsigned char *z
= sqlite3_value_text(argv
[0]);
117 const unsigned char *z0
;
121 while( (c
= *z
)!=0 ){
124 while( (*z
& 0xc0)==0x80 ){ z
++; z0
++; }
127 sqlite3_result_int(context
, (int)(z
-z0
));
131 sqlite3_result_null(context
);
138 ** Implementation of the abs() function.
140 ** IMP: R-23979-26855 The abs(X) function returns the absolute value of
141 ** the numeric argument X.
143 static void absFunc(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
145 UNUSED_PARAMETER(argc
);
146 switch( sqlite3_value_type(argv
[0]) ){
147 case SQLITE_INTEGER
: {
148 i64 iVal
= sqlite3_value_int64(argv
[0]);
150 if( iVal
==SMALLEST_INT64
){
151 /* IMP: R-31676-45509 If X is the integer -9223372036854775808
152 ** then abs(X) throws an integer overflow error since there is no
153 ** equivalent positive 64-bit two complement value. */
154 sqlite3_result_error(context
, "integer overflow", -1);
159 sqlite3_result_int64(context
, iVal
);
163 /* IMP: R-37434-19929 Abs(X) returns NULL if X is NULL. */
164 sqlite3_result_null(context
);
168 /* Because sqlite3_value_double() returns 0.0 if the argument is not
169 ** something that can be converted into a number, we have:
170 ** IMP: R-01992-00519 Abs(X) returns 0.0 if X is a string or blob
171 ** that cannot be converted to a numeric value.
173 double rVal
= sqlite3_value_double(argv
[0]);
174 if( rVal
<0 ) rVal
= -rVal
;
175 sqlite3_result_double(context
, rVal
);
182 ** Implementation of the instr() function.
184 ** instr(haystack,needle) finds the first occurrence of needle
185 ** in haystack and returns the number of previous characters plus 1,
186 ** or 0 if needle does not occur within haystack.
188 ** If both haystack and needle are BLOBs, then the result is one more than
189 ** the number of bytes in haystack prior to the first occurrence of needle,
190 ** or 0 if needle never occurs in haystack.
192 static void instrFunc(
193 sqlite3_context
*context
,
197 const unsigned char *zHaystack
;
198 const unsigned char *zNeedle
;
201 int typeHaystack
, typeNeedle
;
205 UNUSED_PARAMETER(argc
);
206 typeHaystack
= sqlite3_value_type(argv
[0]);
207 typeNeedle
= sqlite3_value_type(argv
[1]);
208 if( typeHaystack
==SQLITE_NULL
|| typeNeedle
==SQLITE_NULL
) return;
209 nHaystack
= sqlite3_value_bytes(argv
[0]);
210 nNeedle
= sqlite3_value_bytes(argv
[1]);
212 if( typeHaystack
==SQLITE_BLOB
&& typeNeedle
==SQLITE_BLOB
){
213 zHaystack
= sqlite3_value_blob(argv
[0]);
214 zNeedle
= sqlite3_value_blob(argv
[1]);
217 zHaystack
= sqlite3_value_text(argv
[0]);
218 zNeedle
= sqlite3_value_text(argv
[1]);
221 if( zNeedle
==0 || (nHaystack
&& zHaystack
==0) ) return;
222 while( nNeedle
<=nHaystack
&& memcmp(zHaystack
, zNeedle
, nNeedle
)!=0 ){
227 }while( isText
&& (zHaystack
[0]&0xc0)==0x80 );
229 if( nNeedle
>nHaystack
) N
= 0;
231 sqlite3_result_int(context
, N
);
235 ** Implementation of the printf() function.
237 static void printfFunc(
238 sqlite3_context
*context
,
246 sqlite3
*db
= sqlite3_context_db_handle(context
);
248 if( argc
>=1 && (zFormat
= (const char*)sqlite3_value_text(argv
[0]))!=0 ){
252 sqlite3StrAccumInit(&str
, db
, 0, 0, db
->aLimit
[SQLITE_LIMIT_LENGTH
]);
253 str
.printfFlags
= SQLITE_PRINTF_SQLFUNC
;
254 sqlite3_str_appendf(&str
, zFormat
, &x
);
256 sqlite3_result_text(context
, sqlite3StrAccumFinish(&str
), n
,
262 ** Implementation of the substr() function.
264 ** substr(x,p1,p2) returns p2 characters of x[] beginning with p1.
265 ** p1 is 1-indexed. So substr(x,1,1) returns the first character
266 ** of x. If x is text, then we actually count UTF-8 characters.
267 ** If x is a blob, then we count bytes.
269 ** If p1 is negative, then we begin abs(p1) from the end of x[].
271 ** If p2 is negative, return the p2 characters preceding p1.
273 static void substrFunc(
274 sqlite3_context
*context
,
278 const unsigned char *z
;
279 const unsigned char *z2
;
285 assert( argc
==3 || argc
==2 );
286 if( sqlite3_value_type(argv
[1])==SQLITE_NULL
287 || (argc
==3 && sqlite3_value_type(argv
[2])==SQLITE_NULL
)
291 p0type
= sqlite3_value_type(argv
[0]);
292 p1
= sqlite3_value_int(argv
[1]);
293 if( p0type
==SQLITE_BLOB
){
294 len
= sqlite3_value_bytes(argv
[0]);
295 z
= sqlite3_value_blob(argv
[0]);
297 assert( len
==sqlite3_value_bytes(argv
[0]) );
299 z
= sqlite3_value_text(argv
[0]);
303 for(z2
=z
; *z2
; len
++){
304 SQLITE_SKIP_UTF8(z2
);
308 #ifdef SQLITE_SUBSTR_COMPATIBILITY
309 /* If SUBSTR_COMPATIBILITY is defined then substr(X,0,N) work the same as
310 ** as substr(X,1,N) - it returns the first N characters of X. This
311 ** is essentially a back-out of the bug-fix in check-in [5fc125d362df4b8]
312 ** from 2009-02-02 for compatibility of applications that exploited the
313 ** old buggy behavior. */
314 if( p1
==0 ) p1
= 1; /* <rdar://problem/6778339> */
317 p2
= sqlite3_value_int(argv
[2]);
323 p2
= sqlite3_context_db_handle(context
)->aLimit
[SQLITE_LIMIT_LENGTH
];
344 assert( p1
>=0 && p2
>=0 );
345 if( p0type
!=SQLITE_BLOB
){
350 for(z2
=z
; *z2
&& p2
; p2
--){
351 SQLITE_SKIP_UTF8(z2
);
353 sqlite3_result_text64(context
, (char*)z
, z2
-z
, SQLITE_TRANSIENT
,
360 sqlite3_result_blob64(context
, (char*)&z
[p1
], (u64
)p2
, SQLITE_TRANSIENT
);
365 ** Implementation of the round() function
367 #ifndef SQLITE_OMIT_FLOATING_POINT
368 static void roundFunc(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
372 assert( argc
==1 || argc
==2 );
374 if( SQLITE_NULL
==sqlite3_value_type(argv
[1]) ) return;
375 n
= sqlite3_value_int(argv
[1]);
379 if( sqlite3_value_type(argv
[0])==SQLITE_NULL
) return;
380 r
= sqlite3_value_double(argv
[0]);
381 /* If Y==0 and X will fit in a 64-bit int,
382 ** handle the rounding directly,
383 ** otherwise use printf.
385 if( n
==0 && r
>=0 && r
<LARGEST_INT64
-1 ){
386 r
= (double)((sqlite_int64
)(r
+0.5));
387 }else if( n
==0 && r
<0 && (-r
)<LARGEST_INT64
-1 ){
388 r
= -(double)((sqlite_int64
)((-r
)+0.5));
390 zBuf
= sqlite3_mprintf("%.*f",n
,r
);
392 sqlite3_result_error_nomem(context
);
395 sqlite3AtoF(zBuf
, &r
, sqlite3Strlen30(zBuf
), SQLITE_UTF8
);
398 sqlite3_result_double(context
, r
);
403 ** Allocate nByte bytes of space using sqlite3Malloc(). If the
404 ** allocation fails, call sqlite3_result_error_nomem() to notify
405 ** the database handle that malloc() has failed and return NULL.
406 ** If nByte is larger than the maximum string or blob length, then
407 ** raise an SQLITE_TOOBIG exception and return NULL.
409 static void *contextMalloc(sqlite3_context
*context
, i64 nByte
){
411 sqlite3
*db
= sqlite3_context_db_handle(context
);
413 testcase( nByte
==db
->aLimit
[SQLITE_LIMIT_LENGTH
] );
414 testcase( nByte
==db
->aLimit
[SQLITE_LIMIT_LENGTH
]+1 );
415 if( nByte
>db
->aLimit
[SQLITE_LIMIT_LENGTH
] ){
416 sqlite3_result_error_toobig(context
);
419 z
= sqlite3Malloc(nByte
);
421 sqlite3_result_error_nomem(context
);
428 ** Implementation of the upper() and lower() SQL functions.
430 static void upperFunc(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
434 UNUSED_PARAMETER(argc
);
435 z2
= (char*)sqlite3_value_text(argv
[0]);
436 n
= sqlite3_value_bytes(argv
[0]);
437 /* Verify that the call to _bytes() does not invalidate the _text() pointer */
438 assert( z2
==(char*)sqlite3_value_text(argv
[0]) );
440 z1
= contextMalloc(context
, ((i64
)n
)+1);
443 z1
[i
] = (char)sqlite3Toupper(z2
[i
]);
445 sqlite3_result_text(context
, z1
, n
, sqlite3_free
);
449 static void lowerFunc(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
453 UNUSED_PARAMETER(argc
);
454 z2
= (char*)sqlite3_value_text(argv
[0]);
455 n
= sqlite3_value_bytes(argv
[0]);
456 /* Verify that the call to _bytes() does not invalidate the _text() pointer */
457 assert( z2
==(char*)sqlite3_value_text(argv
[0]) );
459 z1
= contextMalloc(context
, ((i64
)n
)+1);
462 z1
[i
] = sqlite3Tolower(z2
[i
]);
464 sqlite3_result_text(context
, z1
, n
, sqlite3_free
);
470 ** Some functions like COALESCE() and IFNULL() and UNLIKELY() are implemented
471 ** as VDBE code so that unused argument values do not have to be computed.
472 ** However, we still need some kind of function implementation for this
473 ** routines in the function table. The noopFunc macro provides this.
474 ** noopFunc will never be called so it doesn't matter what the implementation
475 ** is. We might as well use the "version()" function as a substitute.
477 #define noopFunc versionFunc /* Substitute function - never called */
480 ** Implementation of random(). Return a random integer.
482 static void randomFunc(
483 sqlite3_context
*context
,
485 sqlite3_value
**NotUsed2
488 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
489 sqlite3_randomness(sizeof(r
), &r
);
491 /* We need to prevent a random number of 0x8000000000000000
492 ** (or -9223372036854775808) since when you do abs() of that
493 ** number of you get the same value back again. To do this
494 ** in a way that is testable, mask the sign bit off of negative
495 ** values, resulting in a positive value. Then take the
496 ** 2s complement of that positive value. The end result can
497 ** therefore be no less than -9223372036854775807.
499 r
= -(r
& LARGEST_INT64
);
501 sqlite3_result_int64(context
, r
);
505 ** Implementation of randomblob(N). Return a random blob
506 ** that is N bytes long.
508 static void randomBlob(
509 sqlite3_context
*context
,
516 UNUSED_PARAMETER(argc
);
517 n
= sqlite3_value_int(argv
[0]);
521 p
= contextMalloc(context
, n
);
523 sqlite3_randomness(n
, p
);
524 sqlite3_result_blob(context
, (char*)p
, n
, sqlite3_free
);
529 ** Implementation of the last_insert_rowid() SQL function. The return
530 ** value is the same as the sqlite3_last_insert_rowid() API function.
532 static void last_insert_rowid(
533 sqlite3_context
*context
,
535 sqlite3_value
**NotUsed2
537 sqlite3
*db
= sqlite3_context_db_handle(context
);
538 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
539 /* IMP: R-51513-12026 The last_insert_rowid() SQL function is a
540 ** wrapper around the sqlite3_last_insert_rowid() C/C++ interface
542 sqlite3_result_int64(context
, sqlite3_last_insert_rowid(db
));
546 ** Implementation of the changes() SQL function.
548 ** IMP: R-62073-11209 The changes() SQL function is a wrapper
549 ** around the sqlite3_changes() C/C++ function and hence follows the same
550 ** rules for counting changes.
553 sqlite3_context
*context
,
555 sqlite3_value
**NotUsed2
557 sqlite3
*db
= sqlite3_context_db_handle(context
);
558 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
559 sqlite3_result_int(context
, sqlite3_changes(db
));
563 ** Implementation of the total_changes() SQL function. The return value is
564 ** the same as the sqlite3_total_changes() API function.
566 static void total_changes(
567 sqlite3_context
*context
,
569 sqlite3_value
**NotUsed2
571 sqlite3
*db
= sqlite3_context_db_handle(context
);
572 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
573 /* IMP: R-52756-41993 This function is a wrapper around the
574 ** sqlite3_total_changes() C/C++ interface. */
575 sqlite3_result_int(context
, sqlite3_total_changes(db
));
579 ** A structure defining how to do GLOB-style comparisons.
582 u8 matchAll
; /* "*" or "%" */
583 u8 matchOne
; /* "?" or "_" */
584 u8 matchSet
; /* "[" or 0 */
585 u8 noCase
; /* true to ignore case differences */
589 ** For LIKE and GLOB matching on EBCDIC machines, assume that every
590 ** character is exactly one byte in size. Also, provde the Utf8Read()
591 ** macro for fast reading of the next character in the common case where
592 ** the next character is ASCII.
594 #if defined(SQLITE_EBCDIC)
595 # define sqlite3Utf8Read(A) (*((*A)++))
596 # define Utf8Read(A) (*(A++))
598 # define Utf8Read(A) (A[0]<0x80?*(A++):sqlite3Utf8Read(&A))
601 static const struct compareInfo globInfo
= { '*', '?', '[', 0 };
602 /* The correct SQL-92 behavior is for the LIKE operator to ignore
603 ** case. Thus 'a' LIKE 'A' would be true. */
604 static const struct compareInfo likeInfoNorm
= { '%', '_', 0, 1 };
605 /* If SQLITE_CASE_SENSITIVE_LIKE is defined, then the LIKE operator
606 ** is case sensitive causing 'a' LIKE 'A' to be false */
607 static const struct compareInfo likeInfoAlt
= { '%', '_', 0, 0 };
610 ** Possible error returns from patternMatch()
612 #define SQLITE_MATCH 0
613 #define SQLITE_NOMATCH 1
614 #define SQLITE_NOWILDCARDMATCH 2
617 ** Compare two UTF-8 strings for equality where the first string is
618 ** a GLOB or LIKE expression. Return values:
620 ** SQLITE_MATCH: Match
621 ** SQLITE_NOMATCH: No match
622 ** SQLITE_NOWILDCARDMATCH: No match in spite of having * or % wildcards.
626 ** '*' Matches any sequence of zero or more characters.
628 ** '?' Matches exactly one character.
630 ** [...] Matches one character from the enclosed list of
633 ** [^...] Matches one character not in the enclosed list.
635 ** With the [...] and [^...] matching, a ']' character can be included
636 ** in the list by making it the first character after '[' or '^'. A
637 ** range of characters can be specified using '-'. Example:
638 ** "[a-z]" matches any single lower-case letter. To match a '-', make
639 ** it the last character in the list.
641 ** Like matching rules:
643 ** '%' Matches any sequence of zero or more characters
645 *** '_' Matches any one character
647 ** Ec Where E is the "esc" character and c is any other
648 ** character, including '%', '_', and esc, match exactly c.
650 ** The comments within this routine usually assume glob matching.
652 ** This routine is usually quick, but can be N**2 in the worst case.
654 static int patternCompare(
655 const u8
*zPattern
, /* The glob pattern */
656 const u8
*zString
, /* The string to compare against the glob */
657 const struct compareInfo
*pInfo
, /* Information about how to do the compare */
658 u32 matchOther
/* The escape char (LIKE) or '[' (GLOB) */
660 u32 c
, c2
; /* Next pattern and input string chars */
661 u32 matchOne
= pInfo
->matchOne
; /* "?" or "_" */
662 u32 matchAll
= pInfo
->matchAll
; /* "*" or "%" */
663 u8 noCase
= pInfo
->noCase
; /* True if uppercase==lowercase */
664 const u8
*zEscaped
= 0; /* One past the last escaped input char */
666 while( (c
= Utf8Read(zPattern
))!=0 ){
667 if( c
==matchAll
){ /* Match "*" */
668 /* Skip over multiple "*" characters in the pattern. If there
669 ** are also "?" characters, skip those as well, but consume a
670 ** single character of the input string for each "?" skipped */
671 while( (c
=Utf8Read(zPattern
)) == matchAll
|| c
== matchOne
){
672 if( c
==matchOne
&& sqlite3Utf8Read(&zString
)==0 ){
673 return SQLITE_NOWILDCARDMATCH
;
677 return SQLITE_MATCH
; /* "*" at the end of the pattern matches */
678 }else if( c
==matchOther
){
679 if( pInfo
->matchSet
==0 ){
680 c
= sqlite3Utf8Read(&zPattern
);
681 if( c
==0 ) return SQLITE_NOWILDCARDMATCH
;
683 /* "[...]" immediately follows the "*". We have to do a slow
684 ** recursive search in this case, but it is an unusual case. */
685 assert( matchOther
<0x80 ); /* '[' is a single-byte character */
687 int bMatch
= patternCompare(&zPattern
[-1],zString
,pInfo
,matchOther
);
688 if( bMatch
!=SQLITE_NOMATCH
) return bMatch
;
689 SQLITE_SKIP_UTF8(zString
);
691 return SQLITE_NOWILDCARDMATCH
;
695 /* At this point variable c contains the first character of the
696 ** pattern string past the "*". Search in the input string for the
697 ** first matching character and recursively continue the match from
700 ** For a case-insensitive search, set variable cx to be the same as
701 ** c but in the other case and search the input string for either
708 zStop
[0] = sqlite3Toupper(c
);
709 zStop
[1] = sqlite3Tolower(c
);
716 zString
+= strcspn((const char*)zString
, zStop
);
717 if( zString
[0]==0 ) break;
719 bMatch
= patternCompare(zPattern
,zString
,pInfo
,matchOther
);
720 if( bMatch
!=SQLITE_NOMATCH
) return bMatch
;
724 while( (c2
= Utf8Read(zString
))!=0 ){
725 if( c2
!=c
) continue;
726 bMatch
= patternCompare(zPattern
,zString
,pInfo
,matchOther
);
727 if( bMatch
!=SQLITE_NOMATCH
) return bMatch
;
730 return SQLITE_NOWILDCARDMATCH
;
733 if( pInfo
->matchSet
==0 ){
734 c
= sqlite3Utf8Read(&zPattern
);
735 if( c
==0 ) return SQLITE_NOMATCH
;
741 c
= sqlite3Utf8Read(&zString
);
742 if( c
==0 ) return SQLITE_NOMATCH
;
743 c2
= sqlite3Utf8Read(&zPattern
);
746 c2
= sqlite3Utf8Read(&zPattern
);
749 if( c
==']' ) seen
= 1;
750 c2
= sqlite3Utf8Read(&zPattern
);
752 while( c2
&& c2
!=']' ){
753 if( c2
=='-' && zPattern
[0]!=']' && zPattern
[0]!=0 && prior_c
>0 ){
754 c2
= sqlite3Utf8Read(&zPattern
);
755 if( c
>=prior_c
&& c
<=c2
) seen
= 1;
763 c2
= sqlite3Utf8Read(&zPattern
);
765 if( c2
==0 || (seen
^ invert
)==0 ){
766 return SQLITE_NOMATCH
;
771 c2
= Utf8Read(zString
);
772 if( c
==c2
) continue;
773 if( noCase
&& sqlite3Tolower(c
)==sqlite3Tolower(c2
) && c
<0x80 && c2
<0x80 ){
776 if( c
==matchOne
&& zPattern
!=zEscaped
&& c2
!=0 ) continue;
777 return SQLITE_NOMATCH
;
779 return *zString
==0 ? SQLITE_MATCH
: SQLITE_NOMATCH
;
783 ** The sqlite3_strglob() interface. Return 0 on a match (like strcmp()) and
784 ** non-zero if there is no match.
786 int sqlite3_strglob(const char *zGlobPattern
, const char *zString
){
787 return patternCompare((u8
*)zGlobPattern
, (u8
*)zString
, &globInfo
, '[');
791 ** The sqlite3_strlike() interface. Return 0 on a match and non-zero for
792 ** a miss - like strcmp().
794 int sqlite3_strlike(const char *zPattern
, const char *zStr
, unsigned int esc
){
795 return patternCompare((u8
*)zPattern
, (u8
*)zStr
, &likeInfoNorm
, esc
);
799 ** Count the number of times that the LIKE operator (or GLOB which is
800 ** just a variation of LIKE) gets called. This is used for testing
804 int sqlite3_like_count
= 0;
809 ** Implementation of the like() SQL function. This function implements
810 ** the build-in LIKE operator. The first argument to the function is the
811 ** pattern and the second argument is the string. So, the SQL statements:
815 ** is implemented as like(B,A).
817 ** This same function (with a different compareInfo structure) computes
818 ** the GLOB operator.
820 static void likeFunc(
821 sqlite3_context
*context
,
825 const unsigned char *zA
, *zB
;
828 sqlite3
*db
= sqlite3_context_db_handle(context
);
829 struct compareInfo
*pInfo
= sqlite3_user_data(context
);
831 #ifdef SQLITE_LIKE_DOESNT_MATCH_BLOBS
832 if( sqlite3_value_type(argv
[0])==SQLITE_BLOB
833 || sqlite3_value_type(argv
[1])==SQLITE_BLOB
836 sqlite3_like_count
++;
838 sqlite3_result_int(context
, 0);
842 zB
= sqlite3_value_text(argv
[0]);
843 zA
= sqlite3_value_text(argv
[1]);
845 /* Limit the length of the LIKE or GLOB pattern to avoid problems
846 ** of deep recursion and N*N behavior in patternCompare().
848 nPat
= sqlite3_value_bytes(argv
[0]);
849 testcase( nPat
==db
->aLimit
[SQLITE_LIMIT_LIKE_PATTERN_LENGTH
] );
850 testcase( nPat
==db
->aLimit
[SQLITE_LIMIT_LIKE_PATTERN_LENGTH
]+1 );
851 if( nPat
> db
->aLimit
[SQLITE_LIMIT_LIKE_PATTERN_LENGTH
] ){
852 sqlite3_result_error(context
, "LIKE or GLOB pattern too complex", -1);
855 assert( zB
==sqlite3_value_text(argv
[0]) ); /* Encoding did not change */
858 /* The escape character string must consist of a single UTF-8 character.
859 ** Otherwise, return an error.
861 const unsigned char *zEsc
= sqlite3_value_text(argv
[2]);
862 if( zEsc
==0 ) return;
863 if( sqlite3Utf8CharLen((char*)zEsc
, -1)!=1 ){
864 sqlite3_result_error(context
,
865 "ESCAPE expression must be a single character", -1);
868 escape
= sqlite3Utf8Read(&zEsc
);
870 escape
= pInfo
->matchSet
;
874 sqlite3_like_count
++;
876 sqlite3_result_int(context
,
877 patternCompare(zB
, zA
, pInfo
, escape
)==SQLITE_MATCH
);
882 ** Implementation of the NULLIF(x,y) function. The result is the first
883 ** argument if the arguments are different. The result is NULL if the
884 ** arguments are equal to each other.
886 static void nullifFunc(
887 sqlite3_context
*context
,
891 CollSeq
*pColl
= sqlite3GetFuncCollSeq(context
);
892 UNUSED_PARAMETER(NotUsed
);
893 if( sqlite3MemCompare(argv
[0], argv
[1], pColl
)!=0 ){
894 sqlite3_result_value(context
, argv
[0]);
899 ** Implementation of the sqlite_version() function. The result is the version
900 ** of the SQLite library that is running.
902 static void versionFunc(
903 sqlite3_context
*context
,
905 sqlite3_value
**NotUsed2
907 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
908 /* IMP: R-48699-48617 This function is an SQL wrapper around the
909 ** sqlite3_libversion() C-interface. */
910 sqlite3_result_text(context
, sqlite3_libversion(), -1, SQLITE_STATIC
);
914 ** Implementation of the sqlite_source_id() function. The result is a string
915 ** that identifies the particular version of the source code used to build
918 static void sourceidFunc(
919 sqlite3_context
*context
,
921 sqlite3_value
**NotUsed2
923 UNUSED_PARAMETER2(NotUsed
, NotUsed2
);
924 /* IMP: R-24470-31136 This function is an SQL wrapper around the
925 ** sqlite3_sourceid() C interface. */
926 sqlite3_result_text(context
, sqlite3_sourceid(), -1, SQLITE_STATIC
);
930 ** Implementation of the sqlite_log() function. This is a wrapper around
931 ** sqlite3_log(). The return value is NULL. The function exists purely for
934 static void errlogFunc(
935 sqlite3_context
*context
,
939 UNUSED_PARAMETER(argc
);
940 UNUSED_PARAMETER(context
);
941 sqlite3_log(sqlite3_value_int(argv
[0]), "%s", sqlite3_value_text(argv
[1]));
945 ** Implementation of the sqlite_compileoption_used() function.
946 ** The result is an integer that identifies if the compiler option
947 ** was used to build SQLite.
949 #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS
950 static void compileoptionusedFunc(
951 sqlite3_context
*context
,
955 const char *zOptName
;
957 UNUSED_PARAMETER(argc
);
958 /* IMP: R-39564-36305 The sqlite_compileoption_used() SQL
959 ** function is a wrapper around the sqlite3_compileoption_used() C/C++
962 if( (zOptName
= (const char*)sqlite3_value_text(argv
[0]))!=0 ){
963 sqlite3_result_int(context
, sqlite3_compileoption_used(zOptName
));
966 #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
969 ** Implementation of the sqlite_compileoption_get() function.
970 ** The result is a string that identifies the compiler options
971 ** used to build SQLite.
973 #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS
974 static void compileoptiongetFunc(
975 sqlite3_context
*context
,
981 UNUSED_PARAMETER(argc
);
982 /* IMP: R-04922-24076 The sqlite_compileoption_get() SQL function
983 ** is a wrapper around the sqlite3_compileoption_get() C/C++ function.
985 n
= sqlite3_value_int(argv
[0]);
986 sqlite3_result_text(context
, sqlite3_compileoption_get(n
), -1, SQLITE_STATIC
);
988 #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
990 /* Array for converting from half-bytes (nybbles) into ASCII hex
992 static const char hexdigits
[] = {
993 '0', '1', '2', '3', '4', '5', '6', '7',
994 '8', '9', 'A', 'B', 'C', 'D', 'E', 'F'
998 ** Implementation of the QUOTE() function. This function takes a single
999 ** argument. If the argument is numeric, the return value is the same as
1000 ** the argument. If the argument is NULL, the return value is the string
1001 ** "NULL". Otherwise, the argument is enclosed in single quotes with
1002 ** single-quote escapes.
1004 static void quoteFunc(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
1006 UNUSED_PARAMETER(argc
);
1007 switch( sqlite3_value_type(argv
[0]) ){
1008 case SQLITE_FLOAT
: {
1011 r1
= sqlite3_value_double(argv
[0]);
1012 sqlite3_snprintf(sizeof(zBuf
), zBuf
, "%!.15g", r1
);
1013 sqlite3AtoF(zBuf
, &r2
, 20, SQLITE_UTF8
);
1015 sqlite3_snprintf(sizeof(zBuf
), zBuf
, "%!.20e", r1
);
1017 sqlite3_result_text(context
, zBuf
, -1, SQLITE_TRANSIENT
);
1020 case SQLITE_INTEGER
: {
1021 sqlite3_result_value(context
, argv
[0]);
1026 char const *zBlob
= sqlite3_value_blob(argv
[0]);
1027 int nBlob
= sqlite3_value_bytes(argv
[0]);
1028 assert( zBlob
==sqlite3_value_blob(argv
[0]) ); /* No encoding change */
1029 zText
= (char *)contextMalloc(context
, (2*(i64
)nBlob
)+4);
1032 for(i
=0; i
<nBlob
; i
++){
1033 zText
[(i
*2)+2] = hexdigits
[(zBlob
[i
]>>4)&0x0F];
1034 zText
[(i
*2)+3] = hexdigits
[(zBlob
[i
])&0x0F];
1036 zText
[(nBlob
*2)+2] = '\'';
1037 zText
[(nBlob
*2)+3] = '\0';
1040 sqlite3_result_text(context
, zText
, -1, SQLITE_TRANSIENT
);
1041 sqlite3_free(zText
);
1048 const unsigned char *zArg
= sqlite3_value_text(argv
[0]);
1051 if( zArg
==0 ) return;
1052 for(i
=0, n
=0; zArg
[i
]; i
++){ if( zArg
[i
]=='\'' ) n
++; }
1053 z
= contextMalloc(context
, ((i64
)i
)+((i64
)n
)+3);
1056 for(i
=0, j
=1; zArg
[i
]; i
++){
1058 if( zArg
[i
]=='\'' ){
1064 sqlite3_result_text(context
, z
, j
, sqlite3_free
);
1069 assert( sqlite3_value_type(argv
[0])==SQLITE_NULL
);
1070 sqlite3_result_text(context
, "NULL", 4, SQLITE_STATIC
);
1077 ** The unicode() function. Return the integer unicode code-point value
1078 ** for the first character of the input string.
1080 static void unicodeFunc(
1081 sqlite3_context
*context
,
1083 sqlite3_value
**argv
1085 const unsigned char *z
= sqlite3_value_text(argv
[0]);
1087 if( z
&& z
[0] ) sqlite3_result_int(context
, sqlite3Utf8Read(&z
));
1091 ** The char() function takes zero or more arguments, each of which is
1092 ** an integer. It constructs a string where each character of the string
1093 ** is the unicode character for the corresponding integer argument.
1095 static void charFunc(
1096 sqlite3_context
*context
,
1098 sqlite3_value
**argv
1100 unsigned char *z
, *zOut
;
1102 zOut
= z
= sqlite3_malloc64( argc
*4+1 );
1104 sqlite3_result_error_nomem(context
);
1107 for(i
=0; i
<argc
; i
++){
1110 x
= sqlite3_value_int64(argv
[i
]);
1111 if( x
<0 || x
>0x10ffff ) x
= 0xfffd;
1112 c
= (unsigned)(x
& 0x1fffff);
1114 *zOut
++ = (u8
)(c
&0xFF);
1115 }else if( c
<0x00800 ){
1116 *zOut
++ = 0xC0 + (u8
)((c
>>6)&0x1F);
1117 *zOut
++ = 0x80 + (u8
)(c
& 0x3F);
1118 }else if( c
<0x10000 ){
1119 *zOut
++ = 0xE0 + (u8
)((c
>>12)&0x0F);
1120 *zOut
++ = 0x80 + (u8
)((c
>>6) & 0x3F);
1121 *zOut
++ = 0x80 + (u8
)(c
& 0x3F);
1123 *zOut
++ = 0xF0 + (u8
)((c
>>18) & 0x07);
1124 *zOut
++ = 0x80 + (u8
)((c
>>12) & 0x3F);
1125 *zOut
++ = 0x80 + (u8
)((c
>>6) & 0x3F);
1126 *zOut
++ = 0x80 + (u8
)(c
& 0x3F);
1129 sqlite3_result_text64(context
, (char*)z
, zOut
-z
, sqlite3_free
, SQLITE_UTF8
);
1133 ** The hex() function. Interpret the argument as a blob. Return
1134 ** a hexadecimal rendering as text.
1136 static void hexFunc(
1137 sqlite3_context
*context
,
1139 sqlite3_value
**argv
1142 const unsigned char *pBlob
;
1145 UNUSED_PARAMETER(argc
);
1146 pBlob
= sqlite3_value_blob(argv
[0]);
1147 n
= sqlite3_value_bytes(argv
[0]);
1148 assert( pBlob
==sqlite3_value_blob(argv
[0]) ); /* No encoding change */
1149 z
= zHex
= contextMalloc(context
, ((i64
)n
)*2 + 1);
1151 for(i
=0; i
<n
; i
++, pBlob
++){
1152 unsigned char c
= *pBlob
;
1153 *(z
++) = hexdigits
[(c
>>4)&0xf];
1154 *(z
++) = hexdigits
[c
&0xf];
1157 sqlite3_result_text(context
, zHex
, n
*2, sqlite3_free
);
1162 ** The zeroblob(N) function returns a zero-filled blob of size N bytes.
1164 static void zeroblobFunc(
1165 sqlite3_context
*context
,
1167 sqlite3_value
**argv
1172 UNUSED_PARAMETER(argc
);
1173 n
= sqlite3_value_int64(argv
[0]);
1175 rc
= sqlite3_result_zeroblob64(context
, n
); /* IMP: R-00293-64994 */
1177 sqlite3_result_error_code(context
, rc
);
1182 ** The replace() function. Three arguments are all strings: call
1183 ** them A, B, and C. The result is also a string which is derived
1184 ** from A by replacing every occurrence of B with C. The match
1185 ** must be exact. Collating sequences are not used.
1187 static void replaceFunc(
1188 sqlite3_context
*context
,
1190 sqlite3_value
**argv
1192 const unsigned char *zStr
; /* The input string A */
1193 const unsigned char *zPattern
; /* The pattern string B */
1194 const unsigned char *zRep
; /* The replacement string C */
1195 unsigned char *zOut
; /* The output */
1196 int nStr
; /* Size of zStr */
1197 int nPattern
; /* Size of zPattern */
1198 int nRep
; /* Size of zRep */
1199 i64 nOut
; /* Maximum size of zOut */
1200 int loopLimit
; /* Last zStr[] that might match zPattern[] */
1201 int i
, j
; /* Loop counters */
1202 unsigned cntExpand
; /* Number zOut expansions */
1203 sqlite3
*db
= sqlite3_context_db_handle(context
);
1206 UNUSED_PARAMETER(argc
);
1207 zStr
= sqlite3_value_text(argv
[0]);
1208 if( zStr
==0 ) return;
1209 nStr
= sqlite3_value_bytes(argv
[0]);
1210 assert( zStr
==sqlite3_value_text(argv
[0]) ); /* No encoding change */
1211 zPattern
= sqlite3_value_text(argv
[1]);
1213 assert( sqlite3_value_type(argv
[1])==SQLITE_NULL
1214 || sqlite3_context_db_handle(context
)->mallocFailed
);
1217 if( zPattern
[0]==0 ){
1218 assert( sqlite3_value_type(argv
[1])!=SQLITE_NULL
);
1219 sqlite3_result_value(context
, argv
[0]);
1222 nPattern
= sqlite3_value_bytes(argv
[1]);
1223 assert( zPattern
==sqlite3_value_text(argv
[1]) ); /* No encoding change */
1224 zRep
= sqlite3_value_text(argv
[2]);
1225 if( zRep
==0 ) return;
1226 nRep
= sqlite3_value_bytes(argv
[2]);
1227 assert( zRep
==sqlite3_value_text(argv
[2]) );
1229 assert( nOut
<SQLITE_MAX_LENGTH
);
1230 zOut
= contextMalloc(context
, (i64
)nOut
);
1234 loopLimit
= nStr
- nPattern
;
1236 for(i
=j
=0; i
<=loopLimit
; i
++){
1237 if( zStr
[i
]!=zPattern
[0] || memcmp(&zStr
[i
], zPattern
, nPattern
) ){
1238 zOut
[j
++] = zStr
[i
];
1240 if( nRep
>nPattern
){
1241 nOut
+= nRep
- nPattern
;
1242 testcase( nOut
-1==db
->aLimit
[SQLITE_LIMIT_LENGTH
] );
1243 testcase( nOut
-2==db
->aLimit
[SQLITE_LIMIT_LENGTH
] );
1244 if( nOut
-1>db
->aLimit
[SQLITE_LIMIT_LENGTH
] ){
1245 sqlite3_result_error_toobig(context
);
1250 if( (cntExpand
&(cntExpand
-1))==0 ){
1251 /* Grow the size of the output buffer only on substitutions
1252 ** whose index is a power of two: 1, 2, 4, 8, 16, 32, ... */
1255 zOut
= sqlite3_realloc64(zOut
, (int)nOut
+ (nOut
- nStr
- 1));
1257 sqlite3_result_error_nomem(context
);
1263 memcpy(&zOut
[j
], zRep
, nRep
);
1268 assert( j
+nStr
-i
+1<=nOut
);
1269 memcpy(&zOut
[j
], &zStr
[i
], nStr
-i
);
1273 sqlite3_result_text(context
, (char*)zOut
, j
, sqlite3_free
);
1277 ** Implementation of the TRIM(), LTRIM(), and RTRIM() functions.
1278 ** The userdata is 0x1 for left trim, 0x2 for right trim, 0x3 for both.
1280 static void trimFunc(
1281 sqlite3_context
*context
,
1283 sqlite3_value
**argv
1285 const unsigned char *zIn
; /* Input string */
1286 const unsigned char *zCharSet
; /* Set of characters to trim */
1287 int nIn
; /* Number of bytes in input */
1288 int flags
; /* 1: trimleft 2: trimright 3: trim */
1289 int i
; /* Loop counter */
1290 unsigned char *aLen
= 0; /* Length of each character in zCharSet */
1291 unsigned char **azChar
= 0; /* Individual characters in zCharSet */
1292 int nChar
; /* Number of characters in zCharSet */
1294 if( sqlite3_value_type(argv
[0])==SQLITE_NULL
){
1297 zIn
= sqlite3_value_text(argv
[0]);
1298 if( zIn
==0 ) return;
1299 nIn
= sqlite3_value_bytes(argv
[0]);
1300 assert( zIn
==sqlite3_value_text(argv
[0]) );
1302 static const unsigned char lenOne
[] = { 1 };
1303 static unsigned char * const azOne
[] = { (u8
*)" " };
1306 azChar
= (unsigned char **)azOne
;
1308 }else if( (zCharSet
= sqlite3_value_text(argv
[1]))==0 ){
1311 const unsigned char *z
;
1312 for(z
=zCharSet
, nChar
=0; *z
; nChar
++){
1313 SQLITE_SKIP_UTF8(z
);
1316 azChar
= contextMalloc(context
, ((i64
)nChar
)*(sizeof(char*)+1));
1320 aLen
= (unsigned char*)&azChar
[nChar
];
1321 for(z
=zCharSet
, nChar
=0; *z
; nChar
++){
1322 azChar
[nChar
] = (unsigned char *)z
;
1323 SQLITE_SKIP_UTF8(z
);
1324 aLen
[nChar
] = (u8
)(z
- azChar
[nChar
]);
1329 flags
= SQLITE_PTR_TO_INT(sqlite3_user_data(context
));
1333 for(i
=0; i
<nChar
; i
++){
1335 if( len
<=nIn
&& memcmp(zIn
, azChar
[i
], len
)==0 ) break;
1337 if( i
>=nChar
) break;
1345 for(i
=0; i
<nChar
; i
++){
1347 if( len
<=nIn
&& memcmp(&zIn
[nIn
-len
],azChar
[i
],len
)==0 ) break;
1349 if( i
>=nChar
) break;
1354 sqlite3_free(azChar
);
1357 sqlite3_result_text(context
, (char*)zIn
, nIn
, SQLITE_TRANSIENT
);
1361 #ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
1363 ** The "unknown" function is automatically substituted in place of
1364 ** any unrecognized function name when doing an EXPLAIN or EXPLAIN QUERY PLAN
1365 ** when the SQLITE_ENABLE_UNKNOWN_FUNCTION compile-time option is used.
1366 ** When the "sqlite3" command-line shell is built using this functionality,
1367 ** that allows an EXPLAIN or EXPLAIN QUERY PLAN for complex queries
1368 ** involving application-defined functions to be examined in a generic
1371 static void unknownFunc(
1372 sqlite3_context
*context
,
1374 sqlite3_value
**argv
1378 #endif /*SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION*/
1381 /* IMP: R-25361-16150 This function is omitted from SQLite by default. It
1382 ** is only available if the SQLITE_SOUNDEX compile-time option is used
1383 ** when SQLite is built.
1385 #ifdef SQLITE_SOUNDEX
1387 ** Compute the soundex encoding of a word.
1389 ** IMP: R-59782-00072 The soundex(X) function returns a string that is the
1390 ** soundex encoding of the string X.
1392 static void soundexFunc(
1393 sqlite3_context
*context
,
1395 sqlite3_value
**argv
1400 static const unsigned char iCode
[] = {
1401 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1402 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1403 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1404 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1405 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
1406 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
1407 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
1408 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
1411 zIn
= (u8
*)sqlite3_value_text(argv
[0]);
1412 if( zIn
==0 ) zIn
= (u8
*)"";
1413 for(i
=0; zIn
[i
] && !sqlite3Isalpha(zIn
[i
]); i
++){}
1415 u8 prevcode
= iCode
[zIn
[i
]&0x7f];
1416 zResult
[0] = sqlite3Toupper(zIn
[i
]);
1417 for(j
=1; j
<4 && zIn
[i
]; i
++){
1418 int code
= iCode
[zIn
[i
]&0x7f];
1420 if( code
!=prevcode
){
1422 zResult
[j
++] = code
+ '0';
1432 sqlite3_result_text(context
, zResult
, 4, SQLITE_TRANSIENT
);
1434 /* IMP: R-64894-50321 The string "?000" is returned if the argument
1435 ** is NULL or contains no ASCII alphabetic characters. */
1436 sqlite3_result_text(context
, "?000", 4, SQLITE_STATIC
);
1439 #endif /* SQLITE_SOUNDEX */
1441 #ifndef SQLITE_OMIT_LOAD_EXTENSION
1443 ** A function that loads a shared-library extension then returns NULL.
1445 static void loadExt(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
1446 const char *zFile
= (const char *)sqlite3_value_text(argv
[0]);
1448 sqlite3
*db
= sqlite3_context_db_handle(context
);
1451 /* Disallow the load_extension() SQL function unless the SQLITE_LoadExtFunc
1452 ** flag is set. See the sqlite3_enable_load_extension() API.
1454 if( (db
->flags
& SQLITE_LoadExtFunc
)==0 ){
1455 sqlite3_result_error(context
, "not authorized", -1);
1460 zProc
= (const char *)sqlite3_value_text(argv
[1]);
1464 if( zFile
&& sqlite3_load_extension(db
, zFile
, zProc
, &zErrMsg
) ){
1465 sqlite3_result_error(context
, zErrMsg
, -1);
1466 sqlite3_free(zErrMsg
);
1473 ** An instance of the following structure holds the context of a
1474 ** sum() or avg() aggregate computation.
1476 typedef struct SumCtx SumCtx
;
1478 double rSum
; /* Floating point sum */
1479 i64 iSum
; /* Integer sum */
1480 i64 cnt
; /* Number of elements summed */
1481 u8 overflow
; /* True if integer overflow seen */
1482 u8 approx
; /* True if non-integer value was input to the sum */
1486 ** Routines used to compute the sum, average, and total.
1488 ** The SUM() function follows the (broken) SQL standard which means
1489 ** that it returns NULL if it sums over no inputs. TOTAL returns
1490 ** 0.0 in that case. In addition, TOTAL always returns a float where
1491 ** SUM might return an integer if it never encounters a floating point
1492 ** value. TOTAL never fails, but SUM might through an exception if
1493 ** it overflows an integer.
1495 static void sumStep(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
1499 UNUSED_PARAMETER(argc
);
1500 p
= sqlite3_aggregate_context(context
, sizeof(*p
));
1501 type
= sqlite3_value_numeric_type(argv
[0]);
1502 if( p
&& type
!=SQLITE_NULL
){
1504 if( type
==SQLITE_INTEGER
){
1505 i64 v
= sqlite3_value_int64(argv
[0]);
1507 if( (p
->approx
|p
->overflow
)==0 && sqlite3AddInt64(&p
->iSum
, v
) ){
1508 p
->approx
= p
->overflow
= 1;
1511 p
->rSum
+= sqlite3_value_double(argv
[0]);
1516 #ifndef SQLITE_OMIT_WINDOWFUNC
1517 static void sumInverse(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
1521 UNUSED_PARAMETER(argc
);
1522 p
= sqlite3_aggregate_context(context
, sizeof(*p
));
1523 type
= sqlite3_value_numeric_type(argv
[0]);
1524 /* p is always non-NULL because sumStep() will have been called first
1525 ** to initialize it */
1526 if( ALWAYS(p
) && type
!=SQLITE_NULL
){
1529 assert( type
==SQLITE_INTEGER
|| p
->approx
);
1530 if( type
==SQLITE_INTEGER
&& p
->approx
==0 ){
1531 i64 v
= sqlite3_value_int64(argv
[0]);
1535 p
->rSum
-= sqlite3_value_double(argv
[0]);
1540 # define sumInverse 0
1541 #endif /* SQLITE_OMIT_WINDOWFUNC */
1542 static void sumFinalize(sqlite3_context
*context
){
1544 p
= sqlite3_aggregate_context(context
, 0);
1545 if( p
&& p
->cnt
>0 ){
1547 sqlite3_result_error(context
,"integer overflow",-1);
1548 }else if( p
->approx
){
1549 sqlite3_result_double(context
, p
->rSum
);
1551 sqlite3_result_int64(context
, p
->iSum
);
1555 static void avgFinalize(sqlite3_context
*context
){
1557 p
= sqlite3_aggregate_context(context
, 0);
1558 if( p
&& p
->cnt
>0 ){
1559 sqlite3_result_double(context
, p
->rSum
/(double)p
->cnt
);
1562 static void totalFinalize(sqlite3_context
*context
){
1564 p
= sqlite3_aggregate_context(context
, 0);
1565 /* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
1566 sqlite3_result_double(context
, p
? p
->rSum
: (double)0);
1570 ** The following structure keeps track of state information for the
1571 ** count() aggregate function.
1573 typedef struct CountCtx CountCtx
;
1577 int bInverse
; /* True if xInverse() ever called */
1582 ** Routines to implement the count() aggregate function.
1584 static void countStep(sqlite3_context
*context
, int argc
, sqlite3_value
**argv
){
1586 p
= sqlite3_aggregate_context(context
, sizeof(*p
));
1587 if( (argc
==0 || SQLITE_NULL
!=sqlite3_value_type(argv
[0])) && p
){
1591 #ifndef SQLITE_OMIT_DEPRECATED
1592 /* The sqlite3_aggregate_count() function is deprecated. But just to make
1593 ** sure it still operates correctly, verify that its count agrees with our
1594 ** internal count when using count(*) and when the total count can be
1595 ** expressed as a 32-bit integer. */
1596 assert( argc
==1 || p
==0 || p
->n
>0x7fffffff || p
->bInverse
1597 || p
->n
==sqlite3_aggregate_count(context
) );
1600 static void countFinalize(sqlite3_context
*context
){
1602 p
= sqlite3_aggregate_context(context
, 0);
1603 sqlite3_result_int64(context
, p
? p
->n
: 0);
1605 #ifndef SQLITE_OMIT_WINDOWFUNC
1606 static void countInverse(sqlite3_context
*ctx
, int argc
, sqlite3_value
**argv
){
1608 p
= sqlite3_aggregate_context(ctx
, sizeof(*p
));
1609 /* p is always non-NULL since countStep() will have been called first */
1610 if( (argc
==0 || SQLITE_NULL
!=sqlite3_value_type(argv
[0])) && ALWAYS(p
) ){
1618 # define countInverse 0
1619 #endif /* SQLITE_OMIT_WINDOWFUNC */
1622 ** Routines to implement min() and max() aggregate functions.
1624 static void minmaxStep(
1625 sqlite3_context
*context
,
1627 sqlite3_value
**argv
1629 Mem
*pArg
= (Mem
*)argv
[0];
1631 UNUSED_PARAMETER(NotUsed
);
1633 pBest
= (Mem
*)sqlite3_aggregate_context(context
, sizeof(*pBest
));
1634 if( !pBest
) return;
1636 if( sqlite3_value_type(pArg
)==SQLITE_NULL
){
1637 if( pBest
->flags
) sqlite3SkipAccumulatorLoad(context
);
1638 }else if( pBest
->flags
){
1641 CollSeq
*pColl
= sqlite3GetFuncCollSeq(context
);
1642 /* This step function is used for both the min() and max() aggregates,
1643 ** the only difference between the two being that the sense of the
1644 ** comparison is inverted. For the max() aggregate, the
1645 ** sqlite3_user_data() function returns (void *)-1. For min() it
1646 ** returns (void *)db, where db is the sqlite3* database pointer.
1647 ** Therefore the next statement sets variable 'max' to 1 for the max()
1648 ** aggregate, or 0 for min().
1650 max
= sqlite3_user_data(context
)!=0;
1651 cmp
= sqlite3MemCompare(pBest
, pArg
, pColl
);
1652 if( (max
&& cmp
<0) || (!max
&& cmp
>0) ){
1653 sqlite3VdbeMemCopy(pBest
, pArg
);
1655 sqlite3SkipAccumulatorLoad(context
);
1658 pBest
->db
= sqlite3_context_db_handle(context
);
1659 sqlite3VdbeMemCopy(pBest
, pArg
);
1662 static void minMaxValueFinalize(sqlite3_context
*context
, int bValue
){
1663 sqlite3_value
*pRes
;
1664 pRes
= (sqlite3_value
*)sqlite3_aggregate_context(context
, 0);
1667 sqlite3_result_value(context
, pRes
);
1669 if( bValue
==0 ) sqlite3VdbeMemRelease(pRes
);
1672 #ifndef SQLITE_OMIT_WINDOWFUNC
1673 static void minMaxValue(sqlite3_context
*context
){
1674 return minMaxValueFinalize(context
, 1);
1677 # define minMaxValue 0
1678 #endif /* SQLITE_OMIT_WINDOWFUNC */
1679 static void minMaxFinalize(sqlite3_context
*context
){
1680 return minMaxValueFinalize(context
, 0);
1684 ** group_concat(EXPR, ?SEPARATOR?)
1686 static void groupConcatStep(
1687 sqlite3_context
*context
,
1689 sqlite3_value
**argv
1695 assert( argc
==1 || argc
==2 );
1696 if( sqlite3_value_type(argv
[0])==SQLITE_NULL
) return;
1697 pAccum
= (StrAccum
*)sqlite3_aggregate_context(context
, sizeof(*pAccum
));
1700 sqlite3
*db
= sqlite3_context_db_handle(context
);
1701 int firstTerm
= pAccum
->mxAlloc
==0;
1702 pAccum
->mxAlloc
= db
->aLimit
[SQLITE_LIMIT_LENGTH
];
1705 zSep
= (char*)sqlite3_value_text(argv
[1]);
1706 nSep
= sqlite3_value_bytes(argv
[1]);
1711 if( zSep
) sqlite3_str_append(pAccum
, zSep
, nSep
);
1713 zVal
= (char*)sqlite3_value_text(argv
[0]);
1714 nVal
= sqlite3_value_bytes(argv
[0]);
1715 if( zVal
) sqlite3_str_append(pAccum
, zVal
, nVal
);
1718 #ifndef SQLITE_OMIT_WINDOWFUNC
1719 static void groupConcatInverse(
1720 sqlite3_context
*context
,
1722 sqlite3_value
**argv
1725 assert( argc
==1 || argc
==2 );
1727 if( sqlite3_value_type(argv
[0])==SQLITE_NULL
) return;
1728 pAccum
= (StrAccum
*)sqlite3_aggregate_context(context
, sizeof(*pAccum
));
1729 /* pAccum is always non-NULL since groupConcatStep() will have always
1730 ** run frist to initialize it */
1731 if( ALWAYS(pAccum
) ){
1732 n
= sqlite3_value_bytes(argv
[0]);
1734 n
+= sqlite3_value_bytes(argv
[1]);
1738 if( n
>=pAccum
->nChar
){
1742 memmove(pAccum
->zText
, &pAccum
->zText
[n
], pAccum
->nChar
);
1744 if( pAccum
->nChar
==0 ) pAccum
->mxAlloc
= 0;
1748 # define groupConcatInverse 0
1749 #endif /* SQLITE_OMIT_WINDOWFUNC */
1750 static void groupConcatFinalize(sqlite3_context
*context
){
1752 pAccum
= sqlite3_aggregate_context(context
, 0);
1754 if( pAccum
->accError
==SQLITE_TOOBIG
){
1755 sqlite3_result_error_toobig(context
);
1756 }else if( pAccum
->accError
==SQLITE_NOMEM
){
1757 sqlite3_result_error_nomem(context
);
1759 sqlite3_result_text(context
, sqlite3StrAccumFinish(pAccum
), -1,
1764 #ifndef SQLITE_OMIT_WINDOWFUNC
1765 static void groupConcatValue(sqlite3_context
*context
){
1766 sqlite3_str
*pAccum
;
1767 pAccum
= (sqlite3_str
*)sqlite3_aggregate_context(context
, 0);
1769 if( pAccum
->accError
==SQLITE_TOOBIG
){
1770 sqlite3_result_error_toobig(context
);
1771 }else if( pAccum
->accError
==SQLITE_NOMEM
){
1772 sqlite3_result_error_nomem(context
);
1774 const char *zText
= sqlite3_str_value(pAccum
);
1775 sqlite3_result_text(context
, zText
, -1, SQLITE_TRANSIENT
);
1780 # define groupConcatValue 0
1781 #endif /* SQLITE_OMIT_WINDOWFUNC */
1784 ** This routine does per-connection function registration. Most
1785 ** of the built-in functions above are part of the global function set.
1786 ** This routine only deals with those that are not global.
1788 void sqlite3RegisterPerConnectionBuiltinFunctions(sqlite3
*db
){
1789 int rc
= sqlite3_overload_function(db
, "MATCH", 2);
1790 assert( rc
==SQLITE_NOMEM
|| rc
==SQLITE_OK
);
1791 if( rc
==SQLITE_NOMEM
){
1792 sqlite3OomFault(db
);
1797 ** Set the LIKEOPT flag on the 2-argument function with the given name.
1799 static void setLikeOptFlag(sqlite3
*db
, const char *zName
, u8 flagVal
){
1801 pDef
= sqlite3FindFunction(db
, zName
, 2, SQLITE_UTF8
, 0);
1803 pDef
->funcFlags
|= flagVal
;
1808 ** Register the built-in LIKE and GLOB functions. The caseSensitive
1809 ** parameter determines whether or not the LIKE operator is case
1810 ** sensitive. GLOB is always case sensitive.
1812 void sqlite3RegisterLikeFunctions(sqlite3
*db
, int caseSensitive
){
1813 struct compareInfo
*pInfo
;
1814 if( caseSensitive
){
1815 pInfo
= (struct compareInfo
*)&likeInfoAlt
;
1817 pInfo
= (struct compareInfo
*)&likeInfoNorm
;
1819 sqlite3CreateFunc(db
, "like", 2, SQLITE_UTF8
, pInfo
, likeFunc
, 0, 0, 0, 0, 0);
1820 sqlite3CreateFunc(db
, "like", 3, SQLITE_UTF8
, pInfo
, likeFunc
, 0, 0, 0, 0, 0);
1821 sqlite3CreateFunc(db
, "glob", 2, SQLITE_UTF8
,
1822 (struct compareInfo
*)&globInfo
, likeFunc
, 0, 0, 0, 0, 0);
1823 setLikeOptFlag(db
, "glob", SQLITE_FUNC_LIKE
| SQLITE_FUNC_CASE
);
1824 setLikeOptFlag(db
, "like",
1825 caseSensitive
? (SQLITE_FUNC_LIKE
| SQLITE_FUNC_CASE
) : SQLITE_FUNC_LIKE
);
1829 ** pExpr points to an expression which implements a function. If
1830 ** it is appropriate to apply the LIKE optimization to that function
1831 ** then set aWc[0] through aWc[2] to the wildcard characters and the
1832 ** escape character and then return TRUE. If the function is not a
1833 ** LIKE-style function then return FALSE.
1835 ** The expression "a LIKE b ESCAPE c" is only considered a valid LIKE
1836 ** operator if c is a string literal that is exactly one byte in length.
1837 ** That one byte is stored in aWc[3]. aWc[3] is set to zero if there is
1838 ** no ESCAPE clause.
1840 ** *pIsNocase is set to true if uppercase and lowercase are equivalent for
1841 ** the function (default for LIKE). If the function makes the distinction
1842 ** between uppercase and lowercase (as does GLOB) then *pIsNocase is set to
1845 int sqlite3IsLikeFunction(sqlite3
*db
, Expr
*pExpr
, int *pIsNocase
, char *aWc
){
1848 if( pExpr
->op
!=TK_FUNCTION
|| !pExpr
->x
.pList
){
1851 assert( !ExprHasProperty(pExpr
, EP_xIsSelect
) );
1852 nExpr
= pExpr
->x
.pList
->nExpr
;
1853 pDef
= sqlite3FindFunction(db
, pExpr
->u
.zToken
, nExpr
, SQLITE_UTF8
, 0);
1854 if( NEVER(pDef
==0) || (pDef
->funcFlags
& SQLITE_FUNC_LIKE
)==0 ){
1860 Expr
*pEscape
= pExpr
->x
.pList
->a
[2].pExpr
;
1862 if( pEscape
->op
!=TK_STRING
) return 0;
1863 zEscape
= pEscape
->u
.zToken
;
1864 if( zEscape
[0]==0 || zEscape
[1]!=0 ) return 0;
1865 aWc
[3] = zEscape
[0];
1868 /* The memcpy() statement assumes that the wildcard characters are
1869 ** the first three statements in the compareInfo structure. The
1870 ** asserts() that follow verify that assumption
1872 memcpy(aWc
, pDef
->pUserData
, 3);
1873 assert( (char*)&likeInfoAlt
== (char*)&likeInfoAlt
.matchAll
);
1874 assert( &((char*)&likeInfoAlt
)[1] == (char*)&likeInfoAlt
.matchOne
);
1875 assert( &((char*)&likeInfoAlt
)[2] == (char*)&likeInfoAlt
.matchSet
);
1876 *pIsNocase
= (pDef
->funcFlags
& SQLITE_FUNC_CASE
)==0;
1881 ** All of the FuncDef structures in the aBuiltinFunc[] array above
1882 ** to the global function hash table. This occurs at start-time (as
1883 ** a consequence of calling sqlite3_initialize()).
1885 ** After this routine runs
1887 void sqlite3RegisterBuiltinFunctions(void){
1889 ** The following array holds FuncDef structures for all of the functions
1890 ** defined in this file.
1892 ** The array cannot be constant since changes are made to the
1893 ** FuncDef.pHash elements at start-time. The elements of this array
1894 ** are read-only after initialization is complete.
1896 ** For peak efficiency, put the most frequently used function last.
1898 static FuncDef aBuiltinFunc
[] = {
1899 #ifdef SQLITE_SOUNDEX
1900 FUNCTION(soundex
, 1, 0, 0, soundexFunc
),
1902 #ifndef SQLITE_OMIT_LOAD_EXTENSION
1903 VFUNCTION(load_extension
, 1, 0, 0, loadExt
),
1904 VFUNCTION(load_extension
, 2, 0, 0, loadExt
),
1906 #if SQLITE_USER_AUTHENTICATION
1907 FUNCTION(sqlite_crypt
, 2, 0, 0, sqlite3CryptFunc
),
1909 #ifndef SQLITE_OMIT_COMPILEOPTION_DIAGS
1910 DFUNCTION(sqlite_compileoption_used
,1, 0, 0, compileoptionusedFunc
),
1911 DFUNCTION(sqlite_compileoption_get
, 1, 0, 0, compileoptiongetFunc
),
1912 #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
1913 FUNCTION2(unlikely
, 1, 0, 0, noopFunc
, SQLITE_FUNC_UNLIKELY
),
1914 FUNCTION2(likelihood
, 2, 0, 0, noopFunc
, SQLITE_FUNC_UNLIKELY
),
1915 FUNCTION2(likely
, 1, 0, 0, noopFunc
, SQLITE_FUNC_UNLIKELY
),
1917 FUNCTION2(affinity
, 1, 0, 0, noopFunc
, SQLITE_FUNC_AFFINITY
),
1919 #ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
1920 FUNCTION2(sqlite_offset
, 1, 0, 0, noopFunc
, SQLITE_FUNC_OFFSET
|
1921 SQLITE_FUNC_TYPEOF
),
1923 FUNCTION(ltrim
, 1, 1, 0, trimFunc
),
1924 FUNCTION(ltrim
, 2, 1, 0, trimFunc
),
1925 FUNCTION(rtrim
, 1, 2, 0, trimFunc
),
1926 FUNCTION(rtrim
, 2, 2, 0, trimFunc
),
1927 FUNCTION(trim
, 1, 3, 0, trimFunc
),
1928 FUNCTION(trim
, 2, 3, 0, trimFunc
),
1929 FUNCTION(min
, -1, 0, 1, minmaxFunc
),
1930 FUNCTION(min
, 0, 0, 1, 0 ),
1931 WAGGREGATE(min
, 1, 0, 1, minmaxStep
, minMaxFinalize
, minMaxValue
, 0,
1932 SQLITE_FUNC_MINMAX
),
1933 FUNCTION(max
, -1, 1, 1, minmaxFunc
),
1934 FUNCTION(max
, 0, 1, 1, 0 ),
1935 WAGGREGATE(max
, 1, 1, 1, minmaxStep
, minMaxFinalize
, minMaxValue
, 0,
1936 SQLITE_FUNC_MINMAX
),
1937 FUNCTION2(typeof, 1, 0, 0, typeofFunc
, SQLITE_FUNC_TYPEOF
),
1938 FUNCTION2(length
, 1, 0, 0, lengthFunc
, SQLITE_FUNC_LENGTH
),
1939 FUNCTION(instr
, 2, 0, 0, instrFunc
),
1940 FUNCTION(printf
, -1, 0, 0, printfFunc
),
1941 FUNCTION(unicode
, 1, 0, 0, unicodeFunc
),
1942 FUNCTION(char, -1, 0, 0, charFunc
),
1943 FUNCTION(abs
, 1, 0, 0, absFunc
),
1944 #ifndef SQLITE_OMIT_FLOATING_POINT
1945 FUNCTION(round
, 1, 0, 0, roundFunc
),
1946 FUNCTION(round
, 2, 0, 0, roundFunc
),
1948 FUNCTION(upper
, 1, 0, 0, upperFunc
),
1949 FUNCTION(lower
, 1, 0, 0, lowerFunc
),
1950 FUNCTION(hex
, 1, 0, 0, hexFunc
),
1951 FUNCTION2(ifnull
, 2, 0, 0, noopFunc
, SQLITE_FUNC_COALESCE
),
1952 VFUNCTION(random
, 0, 0, 0, randomFunc
),
1953 VFUNCTION(randomblob
, 1, 0, 0, randomBlob
),
1954 FUNCTION(nullif
, 2, 0, 1, nullifFunc
),
1955 DFUNCTION(sqlite_version
, 0, 0, 0, versionFunc
),
1956 DFUNCTION(sqlite_source_id
, 0, 0, 0, sourceidFunc
),
1957 FUNCTION(sqlite_log
, 2, 0, 0, errlogFunc
),
1958 FUNCTION(quote
, 1, 0, 0, quoteFunc
),
1959 VFUNCTION(last_insert_rowid
, 0, 0, 0, last_insert_rowid
),
1960 VFUNCTION(changes
, 0, 0, 0, changes
),
1961 VFUNCTION(total_changes
, 0, 0, 0, total_changes
),
1962 FUNCTION(replace
, 3, 0, 0, replaceFunc
),
1963 FUNCTION(zeroblob
, 1, 0, 0, zeroblobFunc
),
1964 FUNCTION(substr
, 2, 0, 0, substrFunc
),
1965 FUNCTION(substr
, 3, 0, 0, substrFunc
),
1966 WAGGREGATE(sum
, 1,0,0, sumStep
, sumFinalize
, sumFinalize
, sumInverse
, 0),
1967 WAGGREGATE(total
, 1,0,0, sumStep
,totalFinalize
,totalFinalize
,sumInverse
, 0),
1968 WAGGREGATE(avg
, 1,0,0, sumStep
, avgFinalize
, avgFinalize
, sumInverse
, 0),
1969 WAGGREGATE(count
, 0,0,0, countStep
,
1970 countFinalize
, countFinalize
, countInverse
, SQLITE_FUNC_COUNT
),
1971 WAGGREGATE(count
, 1,0,0, countStep
,
1972 countFinalize
, countFinalize
, countInverse
, 0 ),
1973 WAGGREGATE(group_concat
, 1, 0, 0, groupConcatStep
,
1974 groupConcatFinalize
, groupConcatValue
, groupConcatInverse
, 0),
1975 WAGGREGATE(group_concat
, 2, 0, 0, groupConcatStep
,
1976 groupConcatFinalize
, groupConcatValue
, groupConcatInverse
, 0),
1978 LIKEFUNC(glob
, 2, &globInfo
, SQLITE_FUNC_LIKE
|SQLITE_FUNC_CASE
),
1979 #ifdef SQLITE_CASE_SENSITIVE_LIKE
1980 LIKEFUNC(like
, 2, &likeInfoAlt
, SQLITE_FUNC_LIKE
|SQLITE_FUNC_CASE
),
1981 LIKEFUNC(like
, 3, &likeInfoAlt
, SQLITE_FUNC_LIKE
|SQLITE_FUNC_CASE
),
1983 LIKEFUNC(like
, 2, &likeInfoNorm
, SQLITE_FUNC_LIKE
),
1984 LIKEFUNC(like
, 3, &likeInfoNorm
, SQLITE_FUNC_LIKE
),
1986 #ifdef SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
1987 FUNCTION(unknown
, -1, 0, 0, unknownFunc
),
1989 FUNCTION(coalesce
, 1, 0, 0, 0 ),
1990 FUNCTION(coalesce
, 0, 0, 0, 0 ),
1991 FUNCTION2(coalesce
, -1, 0, 0, noopFunc
, SQLITE_FUNC_COALESCE
),
1993 #ifndef SQLITE_OMIT_ALTERTABLE
1994 sqlite3AlterFunctions();
1996 sqlite3WindowFunctions();
1997 #if defined(SQLITE_ENABLE_STAT3) || defined(SQLITE_ENABLE_STAT4)
1998 sqlite3AnalyzeFunctions();
2000 sqlite3RegisterDateTimeFunctions();
2001 sqlite3InsertBuiltinFuncs(aBuiltinFunc
, ArraySize(aBuiltinFunc
));
2003 #if 0 /* Enable to print out how the built-in functions are hashed */
2007 for(i
=0; i
<SQLITE_FUNC_HASH_SZ
; i
++){
2008 printf("FUNC-HASH %02d:", i
);
2009 for(p
=sqlite3BuiltinFunctions
.a
[i
]; p
; p
=p
->u
.pHash
){
2010 int n
= sqlite3Strlen30(p
->zName
);
2011 int h
= p
->zName
[0] + n
;
2012 printf(" %s(%d)", p
->zName
, h
);