blob: 57a4b29ee9957a34568c1bf938f40d36083902a8 [file] [log] [blame]
drhdc04c582002-02-24 01:55:15 +00001/*
2** 2002 February 23
3**
4** The author disclaims copyright to this source code. In place of
5** a legal notice, here is a blessing:
6**
7** May you do good and not evil.
8** May you find forgiveness for yourself and forgive others.
9** May you share freely, never taking more than you give.
10**
11*************************************************************************
12** This file contains the C functions that implement various SQL
13** functions of SQLite.
14**
15** There is only one exported symbol in this file - the function
16** sqliteRegisterBuildinFunctions() found at the bottom of the file.
17** All other code has file scope.
18**
danielk197762c14b32008-11-19 09:05:26 +000019** $Id: func.c,v 1.205 2008/11/19 09:05:27 danielk1977 Exp $
drhdc04c582002-02-24 01:55:15 +000020*/
drhb659e9b2005-01-28 01:29:08 +000021#include "sqliteInt.h"
drhdc04c582002-02-24 01:55:15 +000022#include <ctype.h>
drhd3a149e2002-02-24 17:12:53 +000023#include <stdlib.h>
drh0bce8352002-02-28 00:41:10 +000024#include <assert.h>
danielk197788208052004-05-25 01:13:20 +000025#include "vdbeInt.h"
drh0bce8352002-02-28 00:41:10 +000026
drh55ef4d92005-08-14 01:20:37 +000027/*
28** Return the collating function associated with a function.
29*/
danielk1977dc1bdc42004-06-11 10:51:27 +000030static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){
31 return context->pColl;
32}
33
drh0bce8352002-02-28 00:41:10 +000034/*
35** Implementation of the non-aggregate min() and max() functions
36*/
drhf9b596e2004-05-26 16:54:42 +000037static void minmaxFunc(
38 sqlite3_context *context,
39 int argc,
40 sqlite3_value **argv
41){
drh0bce8352002-02-28 00:41:10 +000042 int i;
drh268380c2004-02-25 13:47:31 +000043 int mask; /* 0 for min() or 0xffffffff for max() */
drhf9b596e2004-05-26 16:54:42 +000044 int iBest;
danielk1977dc1bdc42004-06-11 10:51:27 +000045 CollSeq *pColl;
drh0bce8352002-02-28 00:41:10 +000046
drh89425d52002-02-28 03:04:48 +000047 if( argc==0 ) return;
drhc44af712004-09-02 15:53:56 +000048 mask = sqlite3_user_data(context)==0 ? 0 : -1;
danielk1977dc1bdc42004-06-11 10:51:27 +000049 pColl = sqlite3GetFuncCollSeq(context);
50 assert( pColl );
danielk1977c572ef72004-05-27 09:28:41 +000051 assert( mask==-1 || mask==0 );
drhf9b596e2004-05-26 16:54:42 +000052 iBest = 0;
drh9c054832004-05-31 18:51:57 +000053 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
drhf9b596e2004-05-26 16:54:42 +000054 for(i=1; i<argc; i++){
drh9c054832004-05-31 18:51:57 +000055 if( sqlite3_value_type(argv[i])==SQLITE_NULL ) return;
danielk1977dc1bdc42004-06-11 10:51:27 +000056 if( (sqlite3MemCompare(argv[iBest], argv[i], pColl)^mask)>=0 ){
drhf9b596e2004-05-26 16:54:42 +000057 iBest = i;
drh0bce8352002-02-28 00:41:10 +000058 }
59 }
drhf4479502004-05-27 03:12:53 +000060 sqlite3_result_value(context, argv[iBest]);
drh0bce8352002-02-28 00:41:10 +000061}
drh0bce8352002-02-28 00:41:10 +000062
drh268380c2004-02-25 13:47:31 +000063/*
64** Return the type of the argument.
65*/
drhf9b596e2004-05-26 16:54:42 +000066static void typeofFunc(
67 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +000068 int NotUsed,
drhf9b596e2004-05-26 16:54:42 +000069 sqlite3_value **argv
70){
danielk197735bb9d02004-05-24 12:55:54 +000071 const char *z = 0;
danielk197762c14b32008-11-19 09:05:26 +000072 UNUSED_PARAMETER(NotUsed);
danielk197735bb9d02004-05-24 12:55:54 +000073 switch( sqlite3_value_type(argv[0]) ){
drh9c054832004-05-31 18:51:57 +000074 case SQLITE_NULL: z = "null"; break;
75 case SQLITE_INTEGER: z = "integer"; break;
76 case SQLITE_TEXT: z = "text"; break;
77 case SQLITE_FLOAT: z = "real"; break;
78 case SQLITE_BLOB: z = "blob"; break;
danielk197735bb9d02004-05-24 12:55:54 +000079 }
danielk1977d8123362004-06-12 09:25:12 +000080 sqlite3_result_text(context, z, -1, SQLITE_STATIC);
drh0bce8352002-02-28 00:41:10 +000081}
82
drh5708d2d2005-06-22 10:53:59 +000083
84/*
drh0bce8352002-02-28 00:41:10 +000085** Implementation of the length() function
86*/
drhf9b596e2004-05-26 16:54:42 +000087static void lengthFunc(
88 sqlite3_context *context,
89 int argc,
90 sqlite3_value **argv
91){
drh0bce8352002-02-28 00:41:10 +000092 int len;
93
94 assert( argc==1 );
drhf9b596e2004-05-26 16:54:42 +000095 switch( sqlite3_value_type(argv[0]) ){
drh9c054832004-05-31 18:51:57 +000096 case SQLITE_BLOB:
97 case SQLITE_INTEGER:
98 case SQLITE_FLOAT: {
drhf4479502004-05-27 03:12:53 +000099 sqlite3_result_int(context, sqlite3_value_bytes(argv[0]));
drhf9b596e2004-05-26 16:54:42 +0000100 break;
101 }
drh9c054832004-05-31 18:51:57 +0000102 case SQLITE_TEXT: {
drh2646da72005-12-09 20:02:05 +0000103 const unsigned char *z = sqlite3_value_text(argv[0]);
drh7a521cf2007-04-25 18:23:52 +0000104 if( z==0 ) return;
drh4a919112007-05-15 11:55:09 +0000105 len = 0;
106 while( *z ){
107 len++;
108 SQLITE_SKIP_UTF8(z);
109 }
drhf4479502004-05-27 03:12:53 +0000110 sqlite3_result_int(context, len);
drhf9b596e2004-05-26 16:54:42 +0000111 break;
112 }
113 default: {
114 sqlite3_result_null(context);
115 break;
116 }
117 }
drh0bce8352002-02-28 00:41:10 +0000118}
119
120/*
121** Implementation of the abs() function
122*/
danielk19770ae8b832004-05-25 12:05:56 +0000123static void absFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
drh0bce8352002-02-28 00:41:10 +0000124 assert( argc==1 );
drhf9b596e2004-05-26 16:54:42 +0000125 switch( sqlite3_value_type(argv[0]) ){
drh9c054832004-05-31 18:51:57 +0000126 case SQLITE_INTEGER: {
danielk1977f93bbbe2004-05-27 10:30:52 +0000127 i64 iVal = sqlite3_value_int64(argv[0]);
drh52fc8492006-02-23 21:43:55 +0000128 if( iVal<0 ){
129 if( (iVal<<1)==0 ){
130 sqlite3_result_error(context, "integer overflow", -1);
131 return;
132 }
133 iVal = -iVal;
134 }
danielk1977f93bbbe2004-05-27 10:30:52 +0000135 sqlite3_result_int64(context, iVal);
drhf9b596e2004-05-26 16:54:42 +0000136 break;
137 }
drh9c054832004-05-31 18:51:57 +0000138 case SQLITE_NULL: {
drhf9b596e2004-05-26 16:54:42 +0000139 sqlite3_result_null(context);
140 break;
141 }
142 default: {
danielk1977f93bbbe2004-05-27 10:30:52 +0000143 double rVal = sqlite3_value_double(argv[0]);
drh52fc8492006-02-23 21:43:55 +0000144 if( rVal<0 ) rVal = -rVal;
danielk1977f93bbbe2004-05-27 10:30:52 +0000145 sqlite3_result_double(context, rVal);
drhf9b596e2004-05-26 16:54:42 +0000146 break;
147 }
148 }
drh0bce8352002-02-28 00:41:10 +0000149}
150
151/*
drhf764e6f2007-05-15 01:13:47 +0000152** Implementation of the substr() function.
153**
154** substr(x,p1,p2) returns p2 characters of x[] beginning with p1.
155** p1 is 1-indexed. So substr(x,1,1) returns the first character
156** of x. If x is text, then we actually count UTF-8 characters.
157** If x is a blob, then we count bytes.
158**
159** If p1 is negative, then we begin abs(p1) from the end of x[].
drh0bce8352002-02-28 00:41:10 +0000160*/
drhf9b596e2004-05-26 16:54:42 +0000161static void substrFunc(
162 sqlite3_context *context,
163 int argc,
164 sqlite3_value **argv
165){
drh2646da72005-12-09 20:02:05 +0000166 const unsigned char *z;
167 const unsigned char *z2;
drh023ae032007-05-08 12:12:16 +0000168 int len;
drhf764e6f2007-05-15 01:13:47 +0000169 int p0type;
drh023ae032007-05-08 12:12:16 +0000170 i64 p1, p2;
drhf9b596e2004-05-26 16:54:42 +0000171
drh64f31512007-10-12 19:11:55 +0000172 assert( argc==3 || argc==2 );
drhf764e6f2007-05-15 01:13:47 +0000173 p0type = sqlite3_value_type(argv[0]);
174 if( p0type==SQLITE_BLOB ){
175 len = sqlite3_value_bytes(argv[0]);
176 z = sqlite3_value_blob(argv[0]);
177 if( z==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000178 assert( len==sqlite3_value_bytes(argv[0]) );
drhf764e6f2007-05-15 01:13:47 +0000179 }else{
180 z = sqlite3_value_text(argv[0]);
181 if( z==0 ) return;
drh4a919112007-05-15 11:55:09 +0000182 len = 0;
183 for(z2=z; *z2; len++){
184 SQLITE_SKIP_UTF8(z2);
185 }
drhf764e6f2007-05-15 01:13:47 +0000186 }
danielk197751ad0ec2004-05-24 12:39:02 +0000187 p1 = sqlite3_value_int(argv[1]);
drh64f31512007-10-12 19:11:55 +0000188 if( argc==3 ){
189 p2 = sqlite3_value_int(argv[2]);
190 }else{
drhbb4957f2008-03-20 14:03:29 +0000191 p2 = sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_LENGTH];
drh64f31512007-10-12 19:11:55 +0000192 }
drh0bce8352002-02-28 00:41:10 +0000193 if( p1<0 ){
drh89425d52002-02-28 03:04:48 +0000194 p1 += len;
drh653bc752002-02-28 03:31:10 +0000195 if( p1<0 ){
196 p2 += p1;
197 p1 = 0;
198 }
drh0bce8352002-02-28 00:41:10 +0000199 }else if( p1>0 ){
200 p1--;
201 }
202 if( p1+p2>len ){
203 p2 = len-p1;
204 }
drhf764e6f2007-05-15 01:13:47 +0000205 if( p0type!=SQLITE_BLOB ){
drh4a919112007-05-15 11:55:09 +0000206 while( *z && p1 ){
207 SQLITE_SKIP_UTF8(z);
208 p1--;
drhf764e6f2007-05-15 01:13:47 +0000209 }
drh4a919112007-05-15 11:55:09 +0000210 for(z2=z; *z2 && p2; p2--){
211 SQLITE_SKIP_UTF8(z2);
drhf764e6f2007-05-15 01:13:47 +0000212 }
drh4a919112007-05-15 11:55:09 +0000213 sqlite3_result_text(context, (char*)z, z2-z, SQLITE_TRANSIENT);
drhf764e6f2007-05-15 01:13:47 +0000214 }else{
215 if( p2<0 ) p2 = 0;
216 sqlite3_result_blob(context, (char*)&z[p1], p2, SQLITE_TRANSIENT);
drh0bce8352002-02-28 00:41:10 +0000217 }
drh0bce8352002-02-28 00:41:10 +0000218}
219
220/*
221** Implementation of the round() function
222*/
danielk19770ae8b832004-05-25 12:05:56 +0000223static void roundFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
danielk197751ad0ec2004-05-24 12:39:02 +0000224 int n = 0;
drh0bce8352002-02-28 00:41:10 +0000225 double r;
drh592ac8c2005-08-13 03:07:47 +0000226 char zBuf[500]; /* larger than the %f representation of the largest double */
drh0bce8352002-02-28 00:41:10 +0000227 assert( argc==1 || argc==2 );
danielk197751ad0ec2004-05-24 12:39:02 +0000228 if( argc==2 ){
drh9c054832004-05-31 18:51:57 +0000229 if( SQLITE_NULL==sqlite3_value_type(argv[1]) ) return;
danielk197751ad0ec2004-05-24 12:39:02 +0000230 n = sqlite3_value_int(argv[1]);
231 if( n>30 ) n = 30;
232 if( n<0 ) n = 0;
233 }
drhd589a922006-03-02 03:02:48 +0000234 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
drh4f26d6c2004-05-26 23:25:30 +0000235 r = sqlite3_value_double(argv[0]);
drhe866fcb2005-07-09 02:38:06 +0000236 sqlite3_snprintf(sizeof(zBuf),zBuf,"%.*f",n,r);
drh502b9622006-04-07 13:26:42 +0000237 sqlite3AtoF(zBuf, &r);
238 sqlite3_result_double(context, r);
drh0bce8352002-02-28 00:41:10 +0000239}
drhdc04c582002-02-24 01:55:15 +0000240
danielk197726783a52007-08-29 14:06:22 +0000241/*
242** Allocate nByte bytes of space using sqlite3_malloc(). If the
243** allocation fails, call sqlite3_result_error_nomem() to notify
244** the database handle that malloc() has failed.
245*/
drhb1a6c3c2008-03-20 16:30:17 +0000246static void *contextMalloc(sqlite3_context *context, i64 nByte){
drhbb4957f2008-03-20 14:03:29 +0000247 char *z;
248 if( nByte>sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_LENGTH] ){
249 sqlite3_result_error_toobig(context);
250 z = 0;
251 }else{
drhe5ae5732008-06-15 02:51:47 +0000252 z = sqlite3Malloc(nByte);
drhbb4957f2008-03-20 14:03:29 +0000253 if( !z && nByte>0 ){
254 sqlite3_result_error_nomem(context);
255 }
danielk1977a1644fd2007-08-29 12:31:25 +0000256 }
257 return z;
258}
259
drhdc04c582002-02-24 01:55:15 +0000260/*
261** Implementation of the upper() and lower() SQL functions.
262*/
danielk19770ae8b832004-05-25 12:05:56 +0000263static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
drh7a521cf2007-04-25 18:23:52 +0000264 char *z1;
265 const char *z2;
drh9310ef22007-04-27 17:16:20 +0000266 int i, n;
drh9c054832004-05-31 18:51:57 +0000267 if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return;
drh7a521cf2007-04-25 18:23:52 +0000268 z2 = (char*)sqlite3_value_text(argv[0]);
drh1f0feef2007-05-15 13:27:07 +0000269 n = sqlite3_value_bytes(argv[0]);
270 /* Verify that the call to _bytes() does not invalidate the _text() pointer */
271 assert( z2==(char*)sqlite3_value_text(argv[0]) );
drh7a521cf2007-04-25 18:23:52 +0000272 if( z2 ){
drhb1a6c3c2008-03-20 16:30:17 +0000273 z1 = contextMalloc(context, ((i64)n)+1);
drh7a521cf2007-04-25 18:23:52 +0000274 if( z1 ){
drh5bb3eb92007-05-04 13:15:55 +0000275 memcpy(z1, z2, n+1);
drh7a521cf2007-04-25 18:23:52 +0000276 for(i=0; z1[i]; i++){
277 z1[i] = toupper(z1[i]);
278 }
279 sqlite3_result_text(context, z1, -1, sqlite3_free);
280 }
drhdc04c582002-02-24 01:55:15 +0000281 }
282}
danielk19770ae8b832004-05-25 12:05:56 +0000283static void lowerFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
drh7a521cf2007-04-25 18:23:52 +0000284 char *z1;
285 const char *z2;
drh9310ef22007-04-27 17:16:20 +0000286 int i, n;
drh9c054832004-05-31 18:51:57 +0000287 if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return;
drh7a521cf2007-04-25 18:23:52 +0000288 z2 = (char*)sqlite3_value_text(argv[0]);
drh1f0feef2007-05-15 13:27:07 +0000289 n = sqlite3_value_bytes(argv[0]);
290 /* Verify that the call to _bytes() does not invalidate the _text() pointer */
291 assert( z2==(char*)sqlite3_value_text(argv[0]) );
drh7a521cf2007-04-25 18:23:52 +0000292 if( z2 ){
drhb1a6c3c2008-03-20 16:30:17 +0000293 z1 = contextMalloc(context, ((i64)n)+1);
drh7a521cf2007-04-25 18:23:52 +0000294 if( z1 ){
drh5bb3eb92007-05-04 13:15:55 +0000295 memcpy(z1, z2, n+1);
drh7a521cf2007-04-25 18:23:52 +0000296 for(i=0; z1[i]; i++){
297 z1[i] = tolower(z1[i]);
298 }
299 sqlite3_result_text(context, z1, -1, sqlite3_free);
300 }
drhdc04c582002-02-24 01:55:15 +0000301 }
302}
303
304/*
drhfbc99082002-02-28 03:14:18 +0000305** Implementation of the IFNULL(), NVL(), and COALESCE() functions.
jplyonb6c9e6e2004-01-19 04:53:24 +0000306** All three do the same thing. They return the first non-NULL
307** argument.
drh3212e182002-02-28 00:46:26 +0000308*/
drhf9b596e2004-05-26 16:54:42 +0000309static void ifnullFunc(
310 sqlite3_context *context,
311 int argc,
312 sqlite3_value **argv
313){
drhfbc99082002-02-28 03:14:18 +0000314 int i;
315 for(i=0; i<argc; i++){
drh9c054832004-05-31 18:51:57 +0000316 if( SQLITE_NULL!=sqlite3_value_type(argv[i]) ){
drhf4479502004-05-27 03:12:53 +0000317 sqlite3_result_value(context, argv[i]);
drhfbc99082002-02-28 03:14:18 +0000318 break;
319 }
320 }
drh3212e182002-02-28 00:46:26 +0000321}
322
323/*
drhf9ffac92002-03-02 19:00:31 +0000324** Implementation of random(). Return a random integer.
325*/
drhf9b596e2004-05-26 16:54:42 +0000326static void randomFunc(
327 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +0000328 int NotUsed,
329 sqlite3_value **NotUsed2
drhf9b596e2004-05-26 16:54:42 +0000330){
drh52fc8492006-02-23 21:43:55 +0000331 sqlite_int64 r;
danielk197762c14b32008-11-19 09:05:26 +0000332 UNUSED_PARAMETER2(NotUsed, NotUsed2);
drh2fa18682008-03-19 14:15:34 +0000333 sqlite3_randomness(sizeof(r), &r);
drh874abbe2006-02-23 21:51:12 +0000334 if( (r<<1)==0 ) r = 0; /* Prevent 0x8000.... as the result so that we */
335 /* can always do abs() of the result */
drh52fc8492006-02-23 21:43:55 +0000336 sqlite3_result_int64(context, r);
drhf9ffac92002-03-02 19:00:31 +0000337}
338
339/*
drh137c7282007-01-29 17:58:28 +0000340** Implementation of randomblob(N). Return a random blob
341** that is N bytes long.
drh63cf66f2007-01-29 15:50:05 +0000342*/
drh137c7282007-01-29 17:58:28 +0000343static void randomBlob(
drh63cf66f2007-01-29 15:50:05 +0000344 sqlite3_context *context,
345 int argc,
346 sqlite3_value **argv
347){
drh137c7282007-01-29 17:58:28 +0000348 int n;
349 unsigned char *p;
drh63cf66f2007-01-29 15:50:05 +0000350 assert( argc==1 );
351 n = sqlite3_value_int(argv[0]);
drh023ae032007-05-08 12:12:16 +0000352 if( n<1 ){
353 n = 1;
354 }
danielk1977a1644fd2007-08-29 12:31:25 +0000355 p = contextMalloc(context, n);
drh02d85832007-05-07 19:31:15 +0000356 if( p ){
drh2fa18682008-03-19 14:15:34 +0000357 sqlite3_randomness(n, p);
drh17435752007-08-16 04:30:38 +0000358 sqlite3_result_blob(context, (char*)p, n, sqlite3_free);
drh02d85832007-05-07 19:31:15 +0000359 }
drh63cf66f2007-01-29 15:50:05 +0000360}
361
362/*
drh6ed41ad2002-04-06 14:10:47 +0000363** Implementation of the last_insert_rowid() SQL function. The return
danielk197724b03fd2004-05-10 10:34:34 +0000364** value is the same as the sqlite3_last_insert_rowid() API function.
drh6ed41ad2002-04-06 14:10:47 +0000365*/
danielk197751ad0ec2004-05-24 12:39:02 +0000366static void last_insert_rowid(
danielk19770ae8b832004-05-25 12:05:56 +0000367 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +0000368 int NotUsed,
369 sqlite3_value **NotUsed2
danielk197751ad0ec2004-05-24 12:39:02 +0000370){
drhfa4a4b92008-03-19 21:45:51 +0000371 sqlite3 *db = sqlite3_context_db_handle(context);
danielk197762c14b32008-11-19 09:05:26 +0000372 UNUSED_PARAMETER2(NotUsed, NotUsed2);
drhf9b596e2004-05-26 16:54:42 +0000373 sqlite3_result_int64(context, sqlite3_last_insert_rowid(db));
drh6ed41ad2002-04-06 14:10:47 +0000374}
375
rdcf146a772004-02-25 22:51:06 +0000376/*
danielk1977b28af712004-06-21 06:50:26 +0000377** Implementation of the changes() SQL function. The return value is the
378** same as the sqlite3_changes() API function.
rdcf146a772004-02-25 22:51:06 +0000379*/
danielk1977b28af712004-06-21 06:50:26 +0000380static void changes(
drhf9b596e2004-05-26 16:54:42 +0000381 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +0000382 int NotUsed,
383 sqlite3_value **NotUsed2
drhf9b596e2004-05-26 16:54:42 +0000384){
drhfa4a4b92008-03-19 21:45:51 +0000385 sqlite3 *db = sqlite3_context_db_handle(context);
danielk197762c14b32008-11-19 09:05:26 +0000386 UNUSED_PARAMETER2(NotUsed, NotUsed2);
drhf4479502004-05-27 03:12:53 +0000387 sqlite3_result_int(context, sqlite3_changes(db));
rdcb0c374f2004-02-20 22:53:38 +0000388}
rdcf146a772004-02-25 22:51:06 +0000389
390/*
danielk1977b28af712004-06-21 06:50:26 +0000391** Implementation of the total_changes() SQL function. The return value is
392** the same as the sqlite3_total_changes() API function.
rdcf146a772004-02-25 22:51:06 +0000393*/
danielk1977b28af712004-06-21 06:50:26 +0000394static void total_changes(
395 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +0000396 int NotUsed,
397 sqlite3_value **NotUsed2
danielk197751ad0ec2004-05-24 12:39:02 +0000398){
drhfa4a4b92008-03-19 21:45:51 +0000399 sqlite3 *db = sqlite3_context_db_handle(context);
danielk197762c14b32008-11-19 09:05:26 +0000400 UNUSED_PARAMETER2(NotUsed, NotUsed2);
danielk1977b28af712004-06-21 06:50:26 +0000401 sqlite3_result_int(context, sqlite3_total_changes(db));
rdcb0c374f2004-02-20 22:53:38 +0000402}
403
drh6ed41ad2002-04-06 14:10:47 +0000404/*
drh4e5ffc52004-08-31 00:52:37 +0000405** A structure defining how to do GLOB-style comparisons.
danielk1977d02eb1f2004-06-06 09:44:03 +0000406*/
drh4e5ffc52004-08-31 00:52:37 +0000407struct compareInfo {
408 u8 matchAll;
409 u8 matchOne;
410 u8 matchSet;
411 u8 noCase;
danielk1977d02eb1f2004-06-06 09:44:03 +0000412};
drh55ef4d92005-08-14 01:20:37 +0000413
drhb9175ae2007-12-07 18:39:04 +0000414/*
415** For LIKE and GLOB matching on EBCDIC machines, assume that every
416** character is exactly one byte in size. Also, all characters are
417** able to participate in upper-case-to-lower-case mappings in EBCDIC
418** whereas only characters less than 0x80 do in ASCII.
419*/
420#if defined(SQLITE_EBCDIC)
421# define sqlite3Utf8Read(A,B,C) (*(A++))
drh6ed4b782007-12-10 18:07:20 +0000422# define GlogUpperToLower(A) A = sqlite3UpperToLower[A]
drhb9175ae2007-12-07 18:39:04 +0000423#else
drh6ed4b782007-12-10 18:07:20 +0000424# define GlogUpperToLower(A) if( A<0x80 ){ A = sqlite3UpperToLower[A]; }
drhb9175ae2007-12-07 18:39:04 +0000425#endif
426
drh4e5ffc52004-08-31 00:52:37 +0000427static const struct compareInfo globInfo = { '*', '?', '[', 0 };
drh55ef4d92005-08-14 01:20:37 +0000428/* The correct SQL-92 behavior is for the LIKE operator to ignore
429** case. Thus 'a' LIKE 'A' would be true. */
430static const struct compareInfo likeInfoNorm = { '%', '_', 0, 1 };
431/* If SQLITE_CASE_SENSITIVE_LIKE is defined, then the LIKE operator
432** is case sensitive causing 'a' LIKE 'A' to be false */
433static const struct compareInfo likeInfoAlt = { '%', '_', 0, 0 };
danielk1977d02eb1f2004-06-06 09:44:03 +0000434
435/*
drh4e5ffc52004-08-31 00:52:37 +0000436** Compare two UTF-8 strings for equality where the first string can
437** potentially be a "glob" expression. Return true (1) if they
438** are the same and false (0) if they are different.
drh0ac65892002-04-20 14:24:41 +0000439**
drh4e5ffc52004-08-31 00:52:37 +0000440** Globbing rules:
drh0ac65892002-04-20 14:24:41 +0000441**
drh4e5ffc52004-08-31 00:52:37 +0000442** '*' Matches any sequence of zero or more characters.
danielk1977d02eb1f2004-06-06 09:44:03 +0000443**
drh4e5ffc52004-08-31 00:52:37 +0000444** '?' Matches exactly one character.
445**
446** [...] Matches one character from the enclosed list of
447** characters.
448**
449** [^...] Matches one character not in the enclosed list.
450**
451** With the [...] and [^...] matching, a ']' character can be included
452** in the list by making it the first character after '[' or '^'. A
453** range of characters can be specified using '-'. Example:
454** "[a-z]" matches any single lower-case letter. To match a '-', make
455** it the last character in the list.
456**
457** This routine is usually quick, but can be N**2 in the worst case.
458**
459** Hints: to match '*' or '?', put them in "[]". Like this:
460**
461** abc[*]xyz Matches "abc*xyz" only
drh0ac65892002-04-20 14:24:41 +0000462*/
danielk19777c6303c2004-11-17 16:41:29 +0000463static int patternCompare(
drh4e5ffc52004-08-31 00:52:37 +0000464 const u8 *zPattern, /* The glob pattern */
465 const u8 *zString, /* The string to compare against the glob */
danielk19777c6303c2004-11-17 16:41:29 +0000466 const struct compareInfo *pInfo, /* Information about how to do the compare */
467 const int esc /* The escape character */
danielk197751ad0ec2004-05-24 12:39:02 +0000468){
drh66150952007-07-23 19:12:41 +0000469 int c, c2;
drh4e5ffc52004-08-31 00:52:37 +0000470 int invert;
471 int seen;
drh4e5ffc52004-08-31 00:52:37 +0000472 u8 matchOne = pInfo->matchOne;
473 u8 matchAll = pInfo->matchAll;
474 u8 matchSet = pInfo->matchSet;
475 u8 noCase = pInfo->noCase;
danielk19777c6303c2004-11-17 16:41:29 +0000476 int prevEscape = 0; /* True if the previous character was 'escape' */
danielk1977d02eb1f2004-06-06 09:44:03 +0000477
drh66150952007-07-23 19:12:41 +0000478 while( (c = sqlite3Utf8Read(zPattern,0,&zPattern))!=0 ){
danielk19777c6303c2004-11-17 16:41:29 +0000479 if( !prevEscape && c==matchAll ){
drh66150952007-07-23 19:12:41 +0000480 while( (c=sqlite3Utf8Read(zPattern,0,&zPattern)) == matchAll
481 || c == matchOne ){
482 if( c==matchOne && sqlite3Utf8Read(zString, 0, &zString)==0 ){
483 return 0;
drh4e5ffc52004-08-31 00:52:37 +0000484 }
danielk1977ad7dd422004-06-06 12:41:49 +0000485 }
drh66150952007-07-23 19:12:41 +0000486 if( c==0 ){
487 return 1;
488 }else if( c==esc ){
489 c = sqlite3Utf8Read(zPattern, 0, &zPattern);
490 if( c==0 ){
491 return 0;
492 }
493 }else if( c==matchSet ){
494 assert( esc==0 ); /* This is GLOB, not LIKE */
495 assert( matchSet<0x80 ); /* '[' is a single-byte character */
496 while( *zString && patternCompare(&zPattern[-1],zString,pInfo,esc)==0 ){
drh4a919112007-05-15 11:55:09 +0000497 SQLITE_SKIP_UTF8(zString);
drh4e5ffc52004-08-31 00:52:37 +0000498 }
499 return *zString!=0;
drh66150952007-07-23 19:12:41 +0000500 }
501 while( (c2 = sqlite3Utf8Read(zString,0,&zString))!=0 ){
502 if( noCase ){
drh6ed4b782007-12-10 18:07:20 +0000503 GlogUpperToLower(c2);
504 GlogUpperToLower(c);
drh66150952007-07-23 19:12:41 +0000505 while( c2 != 0 && c2 != c ){
506 c2 = sqlite3Utf8Read(zString, 0, &zString);
drh6ed4b782007-12-10 18:07:20 +0000507 GlogUpperToLower(c2);
drh4e5ffc52004-08-31 00:52:37 +0000508 }
drh66150952007-07-23 19:12:41 +0000509 }else{
510 while( c2 != 0 && c2 != c ){
511 c2 = sqlite3Utf8Read(zString, 0, &zString);
512 }
drh4e5ffc52004-08-31 00:52:37 +0000513 }
drh66150952007-07-23 19:12:41 +0000514 if( c2==0 ) return 0;
515 if( patternCompare(zPattern,zString,pInfo,esc) ) return 1;
516 }
517 return 0;
518 }else if( !prevEscape && c==matchOne ){
519 if( sqlite3Utf8Read(zString, 0, &zString)==0 ){
drh4e5ffc52004-08-31 00:52:37 +0000520 return 0;
danielk1977d02eb1f2004-06-06 09:44:03 +0000521 }
drh4e5ffc52004-08-31 00:52:37 +0000522 }else if( c==matchSet ){
523 int prior_c = 0;
danielk19777c6303c2004-11-17 16:41:29 +0000524 assert( esc==0 ); /* This only occurs for GLOB, not LIKE */
drh4e5ffc52004-08-31 00:52:37 +0000525 seen = 0;
526 invert = 0;
drh66150952007-07-23 19:12:41 +0000527 c = sqlite3Utf8Read(zString, 0, &zString);
drh4e5ffc52004-08-31 00:52:37 +0000528 if( c==0 ) return 0;
drh66150952007-07-23 19:12:41 +0000529 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);
530 if( c2=='^' ){
531 invert = 1;
532 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);
533 }
drh4e5ffc52004-08-31 00:52:37 +0000534 if( c2==']' ){
535 if( c==']' ) seen = 1;
drh66150952007-07-23 19:12:41 +0000536 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);
drh4e5ffc52004-08-31 00:52:37 +0000537 }
drh66150952007-07-23 19:12:41 +0000538 while( c2 && c2!=']' ){
539 if( c2=='-' && zPattern[0]!=']' && zPattern[0]!=0 && prior_c>0 ){
540 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);
drh4e5ffc52004-08-31 00:52:37 +0000541 if( c>=prior_c && c<=c2 ) seen = 1;
542 prior_c = 0;
drh4e5ffc52004-08-31 00:52:37 +0000543 }else{
drh66150952007-07-23 19:12:41 +0000544 if( c==c2 ){
545 seen = 1;
546 }
drh4e5ffc52004-08-31 00:52:37 +0000547 prior_c = c2;
548 }
drh66150952007-07-23 19:12:41 +0000549 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);
drh4e5ffc52004-08-31 00:52:37 +0000550 }
drh66150952007-07-23 19:12:41 +0000551 if( c2==0 || (seen ^ invert)==0 ){
552 return 0;
553 }
554 }else if( esc==c && !prevEscape ){
danielk19777c6303c2004-11-17 16:41:29 +0000555 prevEscape = 1;
drh4e5ffc52004-08-31 00:52:37 +0000556 }else{
drh66150952007-07-23 19:12:41 +0000557 c2 = sqlite3Utf8Read(zString, 0, &zString);
drh4e5ffc52004-08-31 00:52:37 +0000558 if( noCase ){
drh6ed4b782007-12-10 18:07:20 +0000559 GlogUpperToLower(c);
560 GlogUpperToLower(c2);
drh4e5ffc52004-08-31 00:52:37 +0000561 }
drh66150952007-07-23 19:12:41 +0000562 if( c!=c2 ){
563 return 0;
564 }
danielk19777c6303c2004-11-17 16:41:29 +0000565 prevEscape = 0;
danielk1977d02eb1f2004-06-06 09:44:03 +0000566 }
danielk197751ad0ec2004-05-24 12:39:02 +0000567 }
drh4e5ffc52004-08-31 00:52:37 +0000568 return *zString==0;
drh0ac65892002-04-20 14:24:41 +0000569}
drh4e5ffc52004-08-31 00:52:37 +0000570
drh55ef4d92005-08-14 01:20:37 +0000571/*
572** Count the number of times that the LIKE operator (or GLOB which is
573** just a variation of LIKE) gets called. This is used for testing
574** only.
575*/
576#ifdef SQLITE_TEST
577int sqlite3_like_count = 0;
578#endif
579
danielk19773f6b0872004-06-17 05:36:44 +0000580
581/*
582** Implementation of the like() SQL function. This function implements
583** the build-in LIKE operator. The first argument to the function is the
584** pattern and the second argument is the string. So, the SQL statements:
585**
586** A LIKE B
587**
588** is implemented as like(B,A).
589**
drh55ef4d92005-08-14 01:20:37 +0000590** This same function (with a different compareInfo structure) computes
591** the GLOB operator.
danielk19773f6b0872004-06-17 05:36:44 +0000592*/
593static void likeFunc(
594 sqlite3_context *context,
595 int argc,
596 sqlite3_value **argv
597){
drhbeb818d2007-05-08 15:34:47 +0000598 const unsigned char *zA, *zB;
drh85892bd2007-05-10 13:23:22 +0000599 int escape = 0;
drhbb4957f2008-03-20 14:03:29 +0000600 sqlite3 *db = sqlite3_context_db_handle(context);
drhbeb818d2007-05-08 15:34:47 +0000601
drh1f0feef2007-05-15 13:27:07 +0000602 zB = sqlite3_value_text(argv[0]);
603 zA = sqlite3_value_text(argv[1]);
604
drhbeb818d2007-05-08 15:34:47 +0000605 /* Limit the length of the LIKE or GLOB pattern to avoid problems
606 ** of deep recursion and N*N behavior in patternCompare().
607 */
drhbb4957f2008-03-20 14:03:29 +0000608 if( sqlite3_value_bytes(argv[0]) >
609 db->aLimit[SQLITE_LIMIT_LIKE_PATTERN_LENGTH] ){
drhbeb818d2007-05-08 15:34:47 +0000610 sqlite3_result_error(context, "LIKE or GLOB pattern too complex", -1);
611 return;
612 }
drh1f0feef2007-05-15 13:27:07 +0000613 assert( zB==sqlite3_value_text(argv[0]) ); /* Encoding did not change */
drhbeb818d2007-05-08 15:34:47 +0000614
danielk19777c6303c2004-11-17 16:41:29 +0000615 if( argc==3 ){
616 /* The escape character string must consist of a single UTF-8 character.
617 ** Otherwise, return an error.
618 */
619 const unsigned char *zEsc = sqlite3_value_text(argv[2]);
drh7a521cf2007-04-25 18:23:52 +0000620 if( zEsc==0 ) return;
drhee858132007-05-08 20:37:38 +0000621 if( sqlite3Utf8CharLen((char*)zEsc, -1)!=1 ){
danielk19777c6303c2004-11-17 16:41:29 +0000622 sqlite3_result_error(context,
623 "ESCAPE expression must be a single character", -1);
624 return;
625 }
drh66150952007-07-23 19:12:41 +0000626 escape = sqlite3Utf8Read(zEsc, 0, &zEsc);
danielk19777c6303c2004-11-17 16:41:29 +0000627 }
danielk19773f6b0872004-06-17 05:36:44 +0000628 if( zA && zB ){
drh55ef4d92005-08-14 01:20:37 +0000629 struct compareInfo *pInfo = sqlite3_user_data(context);
630#ifdef SQLITE_TEST
631 sqlite3_like_count++;
632#endif
drhbeb818d2007-05-08 15:34:47 +0000633
danielk1977b56fe1f2007-05-09 08:24:44 +0000634 sqlite3_result_int(context, patternCompare(zB, zA, pInfo, escape));
danielk197751ad0ec2004-05-24 12:39:02 +0000635 }
drh8912d102002-05-26 21:34:58 +0000636}
637
638/*
639** Implementation of the NULLIF(x,y) function. The result is the first
640** argument if the arguments are different. The result is NULL if the
641** arguments are equal to each other.
642*/
drhf9b596e2004-05-26 16:54:42 +0000643static void nullifFunc(
644 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +0000645 int NotUsed,
drhf9b596e2004-05-26 16:54:42 +0000646 sqlite3_value **argv
647){
danielk1977dc1bdc42004-06-11 10:51:27 +0000648 CollSeq *pColl = sqlite3GetFuncCollSeq(context);
danielk197762c14b32008-11-19 09:05:26 +0000649 UNUSED_PARAMETER(NotUsed);
danielk1977dc1bdc42004-06-11 10:51:27 +0000650 if( sqlite3MemCompare(argv[0], argv[1], pColl)!=0 ){
drhf4479502004-05-27 03:12:53 +0000651 sqlite3_result_value(context, argv[0]);
drh8912d102002-05-26 21:34:58 +0000652 }
drh0ac65892002-04-20 14:24:41 +0000653}
654
drh647cb0e2002-11-04 19:32:25 +0000655/*
656** Implementation of the VERSION(*) function. The result is the version
657** of the SQLite library that is running.
658*/
drhf9b596e2004-05-26 16:54:42 +0000659static void versionFunc(
660 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +0000661 int NotUsed,
662 sqlite3_value **NotUsed2
drhf9b596e2004-05-26 16:54:42 +0000663){
danielk197762c14b32008-11-19 09:05:26 +0000664 UNUSED_PARAMETER2(NotUsed, NotUsed2);
danielk1977d8123362004-06-12 09:25:12 +0000665 sqlite3_result_text(context, sqlite3_version, -1, SQLITE_STATIC);
drh647cb0e2002-11-04 19:32:25 +0000666}
667
drh137c7282007-01-29 17:58:28 +0000668/* Array for converting from half-bytes (nybbles) into ASCII hex
669** digits. */
670static const char hexdigits[] = {
671 '0', '1', '2', '3', '4', '5', '6', '7',
672 '8', '9', 'A', 'B', 'C', 'D', 'E', 'F'
673};
danielk1977d641d642004-11-18 15:44:29 +0000674
drh47394702003-08-20 01:03:33 +0000675/*
676** EXPERIMENTAL - This is not an official function. The interface may
677** change. This function may disappear. Do not write code that depends
678** on this function.
679**
680** Implementation of the QUOTE() function. This function takes a single
681** argument. If the argument is numeric, the return value is the same as
682** the argument. If the argument is NULL, the return value is the string
683** "NULL". Otherwise, the argument is enclosed in single quotes with
684** single-quote escapes.
685*/
danielk19770ae8b832004-05-25 12:05:56 +0000686static void quoteFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
drh47394702003-08-20 01:03:33 +0000687 if( argc<1 ) return;
drhf9b596e2004-05-26 16:54:42 +0000688 switch( sqlite3_value_type(argv[0]) ){
drh9c054832004-05-31 18:51:57 +0000689 case SQLITE_NULL: {
danielk1977d8123362004-06-12 09:25:12 +0000690 sqlite3_result_text(context, "NULL", 4, SQLITE_STATIC);
drhf9b596e2004-05-26 16:54:42 +0000691 break;
drh47394702003-08-20 01:03:33 +0000692 }
drh9c054832004-05-31 18:51:57 +0000693 case SQLITE_INTEGER:
694 case SQLITE_FLOAT: {
drhf4479502004-05-27 03:12:53 +0000695 sqlite3_result_value(context, argv[0]);
drhf9b596e2004-05-26 16:54:42 +0000696 break;
697 }
danielk19773f41e972004-06-08 00:39:01 +0000698 case SQLITE_BLOB: {
danielk19773f41e972004-06-08 00:39:01 +0000699 char *zText = 0;
danielk19773f41e972004-06-08 00:39:01 +0000700 char const *zBlob = sqlite3_value_blob(argv[0]);
drh1f0feef2007-05-15 13:27:07 +0000701 int nBlob = sqlite3_value_bytes(argv[0]);
702 assert( zBlob==sqlite3_value_blob(argv[0]) ); /* No encoding change */
drhb1a6c3c2008-03-20 16:30:17 +0000703 zText = (char *)contextMalloc(context, (2*(i64)nBlob)+4);
danielk1977a1644fd2007-08-29 12:31:25 +0000704 if( zText ){
danielk19773f41e972004-06-08 00:39:01 +0000705 int i;
706 for(i=0; i<nBlob; i++){
707 zText[(i*2)+2] = hexdigits[(zBlob[i]>>4)&0x0F];
708 zText[(i*2)+3] = hexdigits[(zBlob[i])&0x0F];
709 }
710 zText[(nBlob*2)+2] = '\'';
711 zText[(nBlob*2)+3] = '\0';
712 zText[0] = 'X';
713 zText[1] = '\'';
danielk1977d8123362004-06-12 09:25:12 +0000714 sqlite3_result_text(context, zText, -1, SQLITE_TRANSIENT);
drh17435752007-08-16 04:30:38 +0000715 sqlite3_free(zText);
danielk19773f41e972004-06-08 00:39:01 +0000716 }
717 break;
718 }
drh9c054832004-05-31 18:51:57 +0000719 case SQLITE_TEXT: {
drh023ae032007-05-08 12:12:16 +0000720 int i,j;
721 u64 n;
drh2646da72005-12-09 20:02:05 +0000722 const unsigned char *zArg = sqlite3_value_text(argv[0]);
drhf9b596e2004-05-26 16:54:42 +0000723 char *z;
724
drh7a521cf2007-04-25 18:23:52 +0000725 if( zArg==0 ) return;
drh023ae032007-05-08 12:12:16 +0000726 for(i=0, n=0; zArg[i]; i++){ if( zArg[i]=='\'' ) n++; }
drhb1a6c3c2008-03-20 16:30:17 +0000727 z = contextMalloc(context, ((i64)i)+((i64)n)+3);
danielk1977a1644fd2007-08-29 12:31:25 +0000728 if( z ){
729 z[0] = '\'';
730 for(i=0, j=1; zArg[i]; i++){
731 z[j++] = zArg[i];
732 if( zArg[i]=='\'' ){
733 z[j++] = '\'';
734 }
drhf9b596e2004-05-26 16:54:42 +0000735 }
danielk1977a1644fd2007-08-29 12:31:25 +0000736 z[j++] = '\'';
737 z[j] = 0;
738 sqlite3_result_text(context, z, j, sqlite3_free);
drhf9b596e2004-05-26 16:54:42 +0000739 }
drhf9b596e2004-05-26 16:54:42 +0000740 }
drh47394702003-08-20 01:03:33 +0000741 }
742}
743
drh137c7282007-01-29 17:58:28 +0000744/*
745** The hex() function. Interpret the argument as a blob. Return
746** a hexadecimal rendering as text.
747*/
748static void hexFunc(
749 sqlite3_context *context,
750 int argc,
751 sqlite3_value **argv
752){
753 int i, n;
754 const unsigned char *pBlob;
755 char *zHex, *z;
756 assert( argc==1 );
drh1f0feef2007-05-15 13:27:07 +0000757 pBlob = sqlite3_value_blob(argv[0]);
drh137c7282007-01-29 17:58:28 +0000758 n = sqlite3_value_bytes(argv[0]);
drh1f0feef2007-05-15 13:27:07 +0000759 assert( pBlob==sqlite3_value_blob(argv[0]) ); /* No encoding change */
drhb1a6c3c2008-03-20 16:30:17 +0000760 z = zHex = contextMalloc(context, ((i64)n)*2 + 1);
danielk1977a1644fd2007-08-29 12:31:25 +0000761 if( zHex ){
762 for(i=0; i<n; i++, pBlob++){
763 unsigned char c = *pBlob;
764 *(z++) = hexdigits[(c>>4)&0xf];
765 *(z++) = hexdigits[c&0xf];
766 }
767 *z = 0;
768 sqlite3_result_text(context, zHex, n*2, sqlite3_free);
drh137c7282007-01-29 17:58:28 +0000769 }
drh137c7282007-01-29 17:58:28 +0000770}
771
drh26b6d902007-03-17 13:27:54 +0000772/*
drh8cff3822007-05-02 02:08:28 +0000773** The zeroblob(N) function returns a zero-filled blob of size N bytes.
774*/
775static void zeroblobFunc(
776 sqlite3_context *context,
777 int argc,
778 sqlite3_value **argv
779){
drh98640a32007-06-07 19:08:32 +0000780 i64 n;
drh8cff3822007-05-02 02:08:28 +0000781 assert( argc==1 );
drh98640a32007-06-07 19:08:32 +0000782 n = sqlite3_value_int64(argv[0]);
783 if( n>SQLITE_MAX_LENGTH ){
784 sqlite3_result_error_toobig(context);
785 }else{
786 sqlite3_result_zeroblob(context, n);
787 }
drh8cff3822007-05-02 02:08:28 +0000788}
789
790/*
drh26b6d902007-03-17 13:27:54 +0000791** The replace() function. Three arguments are all strings: call
792** them A, B, and C. The result is also a string which is derived
793** from A by replacing every occurance of B with C. The match
794** must be exact. Collating sequences are not used.
795*/
796static void replaceFunc(
797 sqlite3_context *context,
798 int argc,
799 sqlite3_value **argv
800){
801 const unsigned char *zStr; /* The input string A */
802 const unsigned char *zPattern; /* The pattern string B */
803 const unsigned char *zRep; /* The replacement string C */
804 unsigned char *zOut; /* The output */
805 int nStr; /* Size of zStr */
806 int nPattern; /* Size of zPattern */
807 int nRep; /* Size of zRep */
drh2e6400b2007-05-08 15:46:18 +0000808 i64 nOut; /* Maximum size of zOut */
drh26b6d902007-03-17 13:27:54 +0000809 int loopLimit; /* Last zStr[] that might match zPattern[] */
810 int i, j; /* Loop counters */
811
812 assert( argc==3 );
drh26b6d902007-03-17 13:27:54 +0000813 zStr = sqlite3_value_text(argv[0]);
drh7a521cf2007-04-25 18:23:52 +0000814 if( zStr==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000815 nStr = sqlite3_value_bytes(argv[0]);
816 assert( zStr==sqlite3_value_text(argv[0]) ); /* No encoding change */
drh26b6d902007-03-17 13:27:54 +0000817 zPattern = sqlite3_value_text(argv[1]);
drh709cff32007-04-27 01:18:02 +0000818 if( zPattern==0 || zPattern[0]==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000819 nPattern = sqlite3_value_bytes(argv[1]);
820 assert( zPattern==sqlite3_value_text(argv[1]) ); /* No encoding change */
drh26b6d902007-03-17 13:27:54 +0000821 zRep = sqlite3_value_text(argv[2]);
drh7a521cf2007-04-25 18:23:52 +0000822 if( zRep==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000823 nRep = sqlite3_value_bytes(argv[2]);
824 assert( zRep==sqlite3_value_text(argv[2]) );
drh2e6400b2007-05-08 15:46:18 +0000825 nOut = nStr + 1;
826 assert( nOut<SQLITE_MAX_LENGTH );
drhb1a6c3c2008-03-20 16:30:17 +0000827 zOut = contextMalloc(context, (i64)nOut);
drh2e6400b2007-05-08 15:46:18 +0000828 if( zOut==0 ){
829 return;
drh26b6d902007-03-17 13:27:54 +0000830 }
drh26b6d902007-03-17 13:27:54 +0000831 loopLimit = nStr - nPattern;
832 for(i=j=0; i<=loopLimit; i++){
833 if( zStr[i]!=zPattern[0] || memcmp(&zStr[i], zPattern, nPattern) ){
834 zOut[j++] = zStr[i];
835 }else{
drh4a50aac2007-08-23 02:47:53 +0000836 u8 *zOld;
drhbb4957f2008-03-20 14:03:29 +0000837 sqlite3 *db = sqlite3_context_db_handle(context);
drh2e6400b2007-05-08 15:46:18 +0000838 nOut += nRep - nPattern;
drhbb4957f2008-03-20 14:03:29 +0000839 if( nOut>=db->aLimit[SQLITE_LIMIT_LENGTH] ){
drha0206bc2007-05-08 15:15:02 +0000840 sqlite3_result_error_toobig(context);
drh633e6d52008-07-28 19:34:53 +0000841 sqlite3DbFree(db, zOut);
danielk197717374e82007-05-08 14:39:04 +0000842 return;
843 }
drh4a50aac2007-08-23 02:47:53 +0000844 zOld = zOut;
drh2e6400b2007-05-08 15:46:18 +0000845 zOut = sqlite3_realloc(zOut, (int)nOut);
846 if( zOut==0 ){
danielk1977a1644fd2007-08-29 12:31:25 +0000847 sqlite3_result_error_nomem(context);
drh633e6d52008-07-28 19:34:53 +0000848 sqlite3DbFree(db, zOld);
drh2e6400b2007-05-08 15:46:18 +0000849 return;
850 }
drh26b6d902007-03-17 13:27:54 +0000851 memcpy(&zOut[j], zRep, nRep);
852 j += nRep;
853 i += nPattern-1;
854 }
855 }
drh2e6400b2007-05-08 15:46:18 +0000856 assert( j+nStr-i+1==nOut );
drh26b6d902007-03-17 13:27:54 +0000857 memcpy(&zOut[j], &zStr[i], nStr-i);
858 j += nStr - i;
859 assert( j<=nOut );
860 zOut[j] = 0;
861 sqlite3_result_text(context, (char*)zOut, j, sqlite3_free);
862}
863
drh309b3382007-03-17 17:52:42 +0000864/*
865** Implementation of the TRIM(), LTRIM(), and RTRIM() functions.
866** The userdata is 0x1 for left trim, 0x2 for right trim, 0x3 for both.
867*/
868static void trimFunc(
869 sqlite3_context *context,
870 int argc,
871 sqlite3_value **argv
872){
873 const unsigned char *zIn; /* Input string */
874 const unsigned char *zCharSet; /* Set of characters to trim */
875 int nIn; /* Number of bytes in input */
drh7209c692008-04-27 18:40:11 +0000876 int flags; /* 1: trimleft 2: trimright 3: trim */
drhd1e3a612007-04-27 21:59:52 +0000877 int i; /* Loop counter */
878 unsigned char *aLen; /* Length of each character in zCharSet */
danielk1977bc67da42007-12-11 04:23:19 +0000879 unsigned char **azChar; /* Individual characters in zCharSet */
drhd1e3a612007-04-27 21:59:52 +0000880 int nChar; /* Number of characters in zCharSet */
881
drh309b3382007-03-17 17:52:42 +0000882 if( sqlite3_value_type(argv[0])==SQLITE_NULL ){
883 return;
884 }
885 zIn = sqlite3_value_text(argv[0]);
drh7a521cf2007-04-25 18:23:52 +0000886 if( zIn==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000887 nIn = sqlite3_value_bytes(argv[0]);
888 assert( zIn==sqlite3_value_text(argv[0]) );
drh309b3382007-03-17 17:52:42 +0000889 if( argc==1 ){
drhd1e3a612007-04-27 21:59:52 +0000890 static const unsigned char lenOne[] = { 1 };
danielk1977a4de4532008-09-02 15:44:08 +0000891 static unsigned char * const azOne[] = { (u8*)" " };
drhd1e3a612007-04-27 21:59:52 +0000892 nChar = 1;
drh8cff3822007-05-02 02:08:28 +0000893 aLen = (u8*)lenOne;
danielk1977bc67da42007-12-11 04:23:19 +0000894 azChar = (unsigned char **)azOne;
drhd1e3a612007-04-27 21:59:52 +0000895 zCharSet = 0;
drh7a521cf2007-04-25 18:23:52 +0000896 }else if( (zCharSet = sqlite3_value_text(argv[1]))==0 ){
drh309b3382007-03-17 17:52:42 +0000897 return;
drhd1e3a612007-04-27 21:59:52 +0000898 }else{
899 const unsigned char *z;
900 for(z=zCharSet, nChar=0; *z; nChar++){
drh4a919112007-05-15 11:55:09 +0000901 SQLITE_SKIP_UTF8(z);
drhd1e3a612007-04-27 21:59:52 +0000902 }
903 if( nChar>0 ){
drhb1a6c3c2008-03-20 16:30:17 +0000904 azChar = contextMalloc(context, ((i64)nChar)*(sizeof(char*)+1));
drhd1e3a612007-04-27 21:59:52 +0000905 if( azChar==0 ){
906 return;
907 }
908 aLen = (unsigned char*)&azChar[nChar];
909 for(z=zCharSet, nChar=0; *z; nChar++){
danielk1977bc67da42007-12-11 04:23:19 +0000910 azChar[nChar] = (unsigned char *)z;
drh4a919112007-05-15 11:55:09 +0000911 SQLITE_SKIP_UTF8(z);
drhd1e3a612007-04-27 21:59:52 +0000912 aLen[nChar] = z - azChar[nChar];
913 }
914 }
drh309b3382007-03-17 17:52:42 +0000915 }
drhd1e3a612007-04-27 21:59:52 +0000916 if( nChar>0 ){
shane1fc41292008-07-08 22:28:48 +0000917 flags = SQLITE_PTR_TO_INT(sqlite3_user_data(context));
drh309b3382007-03-17 17:52:42 +0000918 if( flags & 1 ){
drhd1e3a612007-04-27 21:59:52 +0000919 while( nIn>0 ){
920 int len;
921 for(i=0; i<nChar; i++){
922 len = aLen[i];
923 if( memcmp(zIn, azChar[i], len)==0 ) break;
924 }
925 if( i>=nChar ) break;
926 zIn += len;
927 nIn -= len;
drh309b3382007-03-17 17:52:42 +0000928 }
929 }
930 if( flags & 2 ){
drhd1e3a612007-04-27 21:59:52 +0000931 while( nIn>0 ){
932 int len;
933 for(i=0; i<nChar; i++){
934 len = aLen[i];
935 if( len<=nIn && memcmp(&zIn[nIn-len],azChar[i],len)==0 ) break;
936 }
937 if( i>=nChar ) break;
938 nIn -= len;
drh309b3382007-03-17 17:52:42 +0000939 }
940 }
drhd1e3a612007-04-27 21:59:52 +0000941 if( zCharSet ){
942 sqlite3_free(azChar);
943 }
drh309b3382007-03-17 17:52:42 +0000944 }
945 sqlite3_result_text(context, (char*)zIn, nIn, SQLITE_TRANSIENT);
946}
drh26b6d902007-03-17 13:27:54 +0000947
danielk1977a4de4532008-09-02 15:44:08 +0000948
drhd24cc422003-03-27 12:51:24 +0000949#ifdef SQLITE_SOUNDEX
950/*
951** Compute the soundex encoding of a word.
952*/
drh137c7282007-01-29 17:58:28 +0000953static void soundexFunc(
954 sqlite3_context *context,
955 int argc,
956 sqlite3_value **argv
957){
drhd24cc422003-03-27 12:51:24 +0000958 char zResult[8];
drh4c755c02004-08-08 20:22:17 +0000959 const u8 *zIn;
drhd24cc422003-03-27 12:51:24 +0000960 int i, j;
961 static const unsigned char iCode[] = {
962 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
963 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
964 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
965 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
966 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
967 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
968 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
969 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
970 };
971 assert( argc==1 );
drh4c755c02004-08-08 20:22:17 +0000972 zIn = (u8*)sqlite3_value_text(argv[0]);
drhbdf67e02006-08-19 11:34:01 +0000973 if( zIn==0 ) zIn = (u8*)"";
drhd24cc422003-03-27 12:51:24 +0000974 for(i=0; zIn[i] && !isalpha(zIn[i]); i++){}
975 if( zIn[i] ){
drhbdf67e02006-08-19 11:34:01 +0000976 u8 prevcode = iCode[zIn[i]&0x7f];
drhd24cc422003-03-27 12:51:24 +0000977 zResult[0] = toupper(zIn[i]);
978 for(j=1; j<4 && zIn[i]; i++){
979 int code = iCode[zIn[i]&0x7f];
980 if( code>0 ){
drhbdf67e02006-08-19 11:34:01 +0000981 if( code!=prevcode ){
982 prevcode = code;
983 zResult[j++] = code + '0';
984 }
985 }else{
986 prevcode = 0;
drhd24cc422003-03-27 12:51:24 +0000987 }
988 }
989 while( j<4 ){
990 zResult[j++] = '0';
991 }
992 zResult[j] = 0;
danielk1977d8123362004-06-12 09:25:12 +0000993 sqlite3_result_text(context, zResult, 4, SQLITE_TRANSIENT);
drhd24cc422003-03-27 12:51:24 +0000994 }else{
danielk1977d8123362004-06-12 09:25:12 +0000995 sqlite3_result_text(context, "?000", 4, SQLITE_STATIC);
drhd24cc422003-03-27 12:51:24 +0000996 }
997}
998#endif
999
drhfdb83b22006-06-17 14:12:47 +00001000#ifndef SQLITE_OMIT_LOAD_EXTENSION
1001/*
1002** A function that loads a shared-library extension then returns NULL.
1003*/
1004static void loadExt(sqlite3_context *context, int argc, sqlite3_value **argv){
danielk197765fd59f2006-06-24 11:51:33 +00001005 const char *zFile = (const char *)sqlite3_value_text(argv[0]);
drh7a521cf2007-04-25 18:23:52 +00001006 const char *zProc;
drhfa4a4b92008-03-19 21:45:51 +00001007 sqlite3 *db = sqlite3_context_db_handle(context);
drhfdb83b22006-06-17 14:12:47 +00001008 char *zErrMsg = 0;
1009
1010 if( argc==2 ){
danielk197765fd59f2006-06-24 11:51:33 +00001011 zProc = (const char *)sqlite3_value_text(argv[1]);
drh7a521cf2007-04-25 18:23:52 +00001012 }else{
1013 zProc = 0;
drhfdb83b22006-06-17 14:12:47 +00001014 }
drh7a521cf2007-04-25 18:23:52 +00001015 if( zFile && sqlite3_load_extension(db, zFile, zProc, &zErrMsg) ){
drhfdb83b22006-06-17 14:12:47 +00001016 sqlite3_result_error(context, zErrMsg, -1);
1017 sqlite3_free(zErrMsg);
1018 }
1019}
1020#endif
1021
danielk197701427a62005-01-11 13:02:33 +00001022
drh0ac65892002-04-20 14:24:41 +00001023/*
drhd3a149e2002-02-24 17:12:53 +00001024** An instance of the following structure holds the context of a
drhdd5baa92002-02-27 19:50:59 +00001025** sum() or avg() aggregate computation.
1026*/
1027typedef struct SumCtx SumCtx;
1028struct SumCtx {
drh8c08e862006-02-11 17:34:00 +00001029 double rSum; /* Floating point sum */
1030 i64 iSum; /* Integer sum */
1031 i64 cnt; /* Number of elements summed */
1032 u8 overflow; /* True if integer overflow seen */
1033 u8 approx; /* True if non-integer value was input to the sum */
drhdd5baa92002-02-27 19:50:59 +00001034};
1035
1036/*
drha97fdd32006-01-12 22:17:50 +00001037** Routines used to compute the sum, average, and total.
1038**
1039** The SUM() function follows the (broken) SQL standard which means
1040** that it returns NULL if it sums over no inputs. TOTAL returns
1041** 0.0 in that case. In addition, TOTAL always returns a float where
1042** SUM might return an integer if it never encounters a floating point
drhc806d852006-05-11 13:25:39 +00001043** value. TOTAL never fails, but SUM might through an exception if
1044** it overflows an integer.
drhdd5baa92002-02-27 19:50:59 +00001045*/
danielk19770ae8b832004-05-25 12:05:56 +00001046static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){
drhdd5baa92002-02-27 19:50:59 +00001047 SumCtx *p;
drh3d1d95e2005-09-08 10:37:01 +00001048 int type;
drh3f219f42005-09-08 19:45:57 +00001049 assert( argc==1 );
drh4f26d6c2004-05-26 23:25:30 +00001050 p = sqlite3_aggregate_context(context, sizeof(*p));
drh29d72102006-02-09 22:13:41 +00001051 type = sqlite3_value_numeric_type(argv[0]);
drh3d1d95e2005-09-08 10:37:01 +00001052 if( p && type!=SQLITE_NULL ){
drh739105c2002-05-29 23:22:23 +00001053 p->cnt++;
drh29d72102006-02-09 22:13:41 +00001054 if( type==SQLITE_INTEGER ){
drh8c08e862006-02-11 17:34:00 +00001055 i64 v = sqlite3_value_int64(argv[0]);
1056 p->rSum += v;
1057 if( (p->approx|p->overflow)==0 ){
1058 i64 iNewSum = p->iSum + v;
1059 int s1 = p->iSum >> (sizeof(i64)*8-1);
1060 int s2 = v >> (sizeof(i64)*8-1);
1061 int s3 = iNewSum >> (sizeof(i64)*8-1);
1062 p->overflow = (s1&s2&~s3) | (~s1&~s2&s3);
1063 p->iSum = iNewSum;
drh29d72102006-02-09 22:13:41 +00001064 }
1065 }else{
drh8c08e862006-02-11 17:34:00 +00001066 p->rSum += sqlite3_value_double(argv[0]);
drh29d72102006-02-09 22:13:41 +00001067 p->approx = 1;
drh3f219f42005-09-08 19:45:57 +00001068 }
drh739105c2002-05-29 23:22:23 +00001069 }
drhdd5baa92002-02-27 19:50:59 +00001070}
danielk19770ae8b832004-05-25 12:05:56 +00001071static void sumFinalize(sqlite3_context *context){
drhdd5baa92002-02-27 19:50:59 +00001072 SumCtx *p;
drhabfcea22005-09-06 20:36:48 +00001073 p = sqlite3_aggregate_context(context, 0);
drhc2bd9132005-09-08 20:37:43 +00001074 if( p && p->cnt>0 ){
drh8c08e862006-02-11 17:34:00 +00001075 if( p->overflow ){
1076 sqlite3_result_error(context,"integer overflow",-1);
1077 }else if( p->approx ){
1078 sqlite3_result_double(context, p->rSum);
drhc2bd9132005-09-08 20:37:43 +00001079 }else{
drh8c08e862006-02-11 17:34:00 +00001080 sqlite3_result_int64(context, p->iSum);
drhc2bd9132005-09-08 20:37:43 +00001081 }
drh3d1d95e2005-09-08 10:37:01 +00001082 }
drhdd5baa92002-02-27 19:50:59 +00001083}
danielk19770ae8b832004-05-25 12:05:56 +00001084static void avgFinalize(sqlite3_context *context){
drhdd5baa92002-02-27 19:50:59 +00001085 SumCtx *p;
drhabfcea22005-09-06 20:36:48 +00001086 p = sqlite3_aggregate_context(context, 0);
drh739105c2002-05-29 23:22:23 +00001087 if( p && p->cnt>0 ){
drh8c08e862006-02-11 17:34:00 +00001088 sqlite3_result_double(context, p->rSum/(double)p->cnt);
drhdd5baa92002-02-27 19:50:59 +00001089 }
1090}
drha97fdd32006-01-12 22:17:50 +00001091static void totalFinalize(sqlite3_context *context){
1092 SumCtx *p;
1093 p = sqlite3_aggregate_context(context, 0);
drh8c08e862006-02-11 17:34:00 +00001094 sqlite3_result_double(context, p ? p->rSum : 0.0);
drha97fdd32006-01-12 22:17:50 +00001095}
drhdd5baa92002-02-27 19:50:59 +00001096
1097/*
drh0bce8352002-02-28 00:41:10 +00001098** The following structure keeps track of state information for the
1099** count() aggregate function.
1100*/
1101typedef struct CountCtx CountCtx;
1102struct CountCtx {
drhfc6ad392006-02-09 13:38:19 +00001103 i64 n;
drh0bce8352002-02-28 00:41:10 +00001104};
drhdd5baa92002-02-27 19:50:59 +00001105
drh0bce8352002-02-28 00:41:10 +00001106/*
1107** Routines to implement the count() aggregate function.
1108*/
danielk19770ae8b832004-05-25 12:05:56 +00001109static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){
drh0bce8352002-02-28 00:41:10 +00001110 CountCtx *p;
drh4f26d6c2004-05-26 23:25:30 +00001111 p = sqlite3_aggregate_context(context, sizeof(*p));
drh9c054832004-05-31 18:51:57 +00001112 if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){
drh0bce8352002-02-28 00:41:10 +00001113 p->n++;
1114 }
1115}
danielk19770ae8b832004-05-25 12:05:56 +00001116static void countFinalize(sqlite3_context *context){
drh0bce8352002-02-28 00:41:10 +00001117 CountCtx *p;
drhabfcea22005-09-06 20:36:48 +00001118 p = sqlite3_aggregate_context(context, 0);
drhfc6ad392006-02-09 13:38:19 +00001119 sqlite3_result_int64(context, p ? p->n : 0);
drh0bce8352002-02-28 00:41:10 +00001120}
1121
1122/*
drh0bce8352002-02-28 00:41:10 +00001123** Routines to implement min() and max() aggregate functions.
1124*/
danielk197762c14b32008-11-19 09:05:26 +00001125static void minmaxStep(
1126 sqlite3_context *context,
1127 int NotUsed,
1128 sqlite3_value **argv
1129){
danielk197788208052004-05-25 01:13:20 +00001130 Mem *pArg = (Mem *)argv[0];
drh9eb516c2004-07-18 20:52:32 +00001131 Mem *pBest;
danielk197762c14b32008-11-19 09:05:26 +00001132 UNUSED_PARAMETER(NotUsed);
drh9eb516c2004-07-18 20:52:32 +00001133
1134 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
1135 pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));
danielk19773aeab9e2004-06-24 00:20:04 +00001136 if( !pBest ) return;
drh268380c2004-02-25 13:47:31 +00001137
danielk197788208052004-05-25 01:13:20 +00001138 if( pBest->flags ){
drh9eb516c2004-07-18 20:52:32 +00001139 int max;
1140 int cmp;
danielk1977dc1bdc42004-06-11 10:51:27 +00001141 CollSeq *pColl = sqlite3GetFuncCollSeq(context);
danielk19777e18c252004-05-25 11:47:24 +00001142 /* This step function is used for both the min() and max() aggregates,
1143 ** the only difference between the two being that the sense of the
1144 ** comparison is inverted. For the max() aggregate, the
1145 ** sqlite3_user_data() function returns (void *)-1. For min() it
1146 ** returns (void *)db, where db is the sqlite3* database pointer.
1147 ** Therefore the next statement sets variable 'max' to 1 for the max()
1148 ** aggregate, or 0 for min().
1149 */
drh309b3382007-03-17 17:52:42 +00001150 max = sqlite3_user_data(context)!=0;
danielk1977dc1bdc42004-06-11 10:51:27 +00001151 cmp = sqlite3MemCompare(pBest, pArg, pColl);
danielk197788208052004-05-25 01:13:20 +00001152 if( (max && cmp<0) || (!max && cmp>0) ){
drhb21c8cd2007-08-21 19:33:56 +00001153 sqlite3VdbeMemCopy(pBest, pArg);
danielk197788208052004-05-25 01:13:20 +00001154 }
drh268380c2004-02-25 13:47:31 +00001155 }else{
drhb21c8cd2007-08-21 19:33:56 +00001156 sqlite3VdbeMemCopy(pBest, pArg);
drh0bce8352002-02-28 00:41:10 +00001157 }
1158}
danielk19770ae8b832004-05-25 12:05:56 +00001159static void minMaxFinalize(sqlite3_context *context){
danielk197788208052004-05-25 01:13:20 +00001160 sqlite3_value *pRes;
drhabfcea22005-09-06 20:36:48 +00001161 pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0);
1162 if( pRes ){
1163 if( pRes->flags ){
1164 sqlite3_result_value(context, pRes);
1165 }
1166 sqlite3VdbeMemRelease(pRes);
drh0bce8352002-02-28 00:41:10 +00001167 }
1168}
drhdd5baa92002-02-27 19:50:59 +00001169
drhb0689692007-11-01 17:38:30 +00001170/*
1171** group_concat(EXPR, ?SEPARATOR?)
1172*/
1173static void groupConcatStep(
1174 sqlite3_context *context,
1175 int argc,
1176 sqlite3_value **argv
1177){
1178 const char *zVal;
drhade86482007-11-28 22:36:40 +00001179 StrAccum *pAccum;
drhb0689692007-11-01 17:38:30 +00001180 const char *zSep;
drha2baf3a2008-06-18 15:34:09 +00001181 int nVal, nSep, i;
1182 if( argc==0 || sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
drhade86482007-11-28 22:36:40 +00001183 pAccum = (StrAccum*)sqlite3_aggregate_context(context, sizeof(*pAccum));
1184
1185 if( pAccum ){
drhbb4957f2008-03-20 14:03:29 +00001186 sqlite3 *db = sqlite3_context_db_handle(context);
drhade86482007-11-28 22:36:40 +00001187 pAccum->useMalloc = 1;
drhbb4957f2008-03-20 14:03:29 +00001188 pAccum->mxAlloc = db->aLimit[SQLITE_LIMIT_LENGTH];
drhade86482007-11-28 22:36:40 +00001189 if( pAccum->nChar ){
drha2baf3a2008-06-18 15:34:09 +00001190 if( argc>1 ){
1191 zSep = (char*)sqlite3_value_text(argv[argc-1]);
1192 nSep = sqlite3_value_bytes(argv[argc-1]);
drhb0689692007-11-01 17:38:30 +00001193 }else{
1194 zSep = ",";
drhade86482007-11-28 22:36:40 +00001195 nSep = 1;
drhb0689692007-11-01 17:38:30 +00001196 }
drhade86482007-11-28 22:36:40 +00001197 sqlite3StrAccumAppend(pAccum, zSep, nSep);
drhb0689692007-11-01 17:38:30 +00001198 }
drha2baf3a2008-06-18 15:34:09 +00001199 i = 0;
1200 do{
1201 zVal = (char*)sqlite3_value_text(argv[i]);
1202 nVal = sqlite3_value_bytes(argv[i]);
1203 sqlite3StrAccumAppend(pAccum, zVal, nVal);
1204 i++;
1205 }while( i<argc-1 );
drhb0689692007-11-01 17:38:30 +00001206 }
1207}
1208static void groupConcatFinalize(sqlite3_context *context){
drhade86482007-11-28 22:36:40 +00001209 StrAccum *pAccum;
1210 pAccum = sqlite3_aggregate_context(context, 0);
1211 if( pAccum ){
1212 if( pAccum->tooBig ){
1213 sqlite3_result_error_toobig(context);
1214 }else if( pAccum->mallocFailed ){
1215 sqlite3_result_error_nomem(context);
1216 }else{
1217 sqlite3_result_text(context, sqlite3StrAccumFinish(pAccum), -1,
1218 sqlite3_free);
1219 }
drhb0689692007-11-01 17:38:30 +00001220 }
1221}
drh4e5ffc52004-08-31 00:52:37 +00001222
drhd3a149e2002-02-24 17:12:53 +00001223/*
drha2ed5602002-02-26 23:55:31 +00001224** This function registered all of the above C functions as SQL
1225** functions. This should be the only routine in this file with
1226** external linkage.
drhdc04c582002-02-24 01:55:15 +00001227*/
drh9bb575f2004-09-06 17:24:11 +00001228void sqlite3RegisterBuiltinFunctions(sqlite3 *db){
drh1f01ec12005-02-15 21:36:18 +00001229#ifndef SQLITE_OMIT_ALTERTABLE
1230 sqlite3AlterFunctions(db);
1231#endif
danielk19771e536952007-08-16 10:09:01 +00001232 if( !db->mallocFailed ){
danielk1977832a58a2007-06-22 15:21:15 +00001233 int rc = sqlite3_overload_function(db, "MATCH", 2);
1234 assert( rc==SQLITE_NOMEM || rc==SQLITE_OK );
1235 if( rc==SQLITE_NOMEM ){
danielk19771e536952007-08-16 10:09:01 +00001236 db->mallocFailed = 1;
danielk1977832a58a2007-06-22 15:21:15 +00001237 }
1238 }
danielk1977fd9e1f32005-05-22 10:44:34 +00001239#ifdef SQLITE_SSE
drh37527852006-03-16 16:19:56 +00001240 (void)sqlite3SseFunctions(db);
danielk1977fd9e1f32005-05-22 10:44:34 +00001241#endif
drh55ef4d92005-08-14 01:20:37 +00001242}
1243
1244/*
1245** Set the LIKEOPT flag on the 2-argument function with the given name.
1246*/
drhd64fe2f2005-08-28 17:00:23 +00001247static void setLikeOptFlag(sqlite3 *db, const char *zName, int flagVal){
drh55ef4d92005-08-14 01:20:37 +00001248 FuncDef *pDef;
1249 pDef = sqlite3FindFunction(db, zName, strlen(zName), 2, SQLITE_UTF8, 0);
1250 if( pDef ){
drhd64fe2f2005-08-28 17:00:23 +00001251 pDef->flags = flagVal;
drh55ef4d92005-08-14 01:20:37 +00001252 }
1253}
1254
1255/*
1256** Register the built-in LIKE and GLOB functions. The caseSensitive
1257** parameter determines whether or not the LIKE operator is case
1258** sensitive. GLOB is always case sensitive.
1259*/
1260void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){
1261 struct compareInfo *pInfo;
1262 if( caseSensitive ){
1263 pInfo = (struct compareInfo*)&likeInfoAlt;
1264 }else{
1265 pInfo = (struct compareInfo*)&likeInfoNorm;
1266 }
danielk1977771151b2006-01-17 13:21:40 +00001267 sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0);
1268 sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0);
1269 sqlite3CreateFunc(db, "glob", 2, SQLITE_UTF8,
drh55ef4d92005-08-14 01:20:37 +00001270 (struct compareInfo*)&globInfo, likeFunc, 0,0);
drhd64fe2f2005-08-28 17:00:23 +00001271 setLikeOptFlag(db, "glob", SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE);
1272 setLikeOptFlag(db, "like",
1273 caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) : SQLITE_FUNC_LIKE);
drh55ef4d92005-08-14 01:20:37 +00001274}
1275
1276/*
1277** pExpr points to an expression which implements a function. If
1278** it is appropriate to apply the LIKE optimization to that function
1279** then set aWc[0] through aWc[2] to the wildcard characters and
1280** return TRUE. If the function is not a LIKE-style function then
1281** return FALSE.
1282*/
drhd64fe2f2005-08-28 17:00:23 +00001283int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){
drh55ef4d92005-08-14 01:20:37 +00001284 FuncDef *pDef;
danielk1977cdbd8ef2007-05-12 06:11:12 +00001285 if( pExpr->op!=TK_FUNCTION || !pExpr->pList ){
drh55ef4d92005-08-14 01:20:37 +00001286 return 0;
1287 }
1288 if( pExpr->pList->nExpr!=2 ){
1289 return 0;
1290 }
drh2646da72005-12-09 20:02:05 +00001291 pDef = sqlite3FindFunction(db, (char*)pExpr->token.z, pExpr->token.n, 2,
drh55ef4d92005-08-14 01:20:37 +00001292 SQLITE_UTF8, 0);
drhd64fe2f2005-08-28 17:00:23 +00001293 if( pDef==0 || (pDef->flags & SQLITE_FUNC_LIKE)==0 ){
drh55ef4d92005-08-14 01:20:37 +00001294 return 0;
1295 }
1296
1297 /* The memcpy() statement assumes that the wildcard characters are
1298 ** the first three statements in the compareInfo structure. The
1299 ** asserts() that follow verify that assumption
1300 */
1301 memcpy(aWc, pDef->pUserData, 3);
1302 assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );
1303 assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne );
1304 assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet );
drhd64fe2f2005-08-28 17:00:23 +00001305 *pIsNocase = (pDef->flags & SQLITE_FUNC_CASE)==0;
drh55ef4d92005-08-14 01:20:37 +00001306 return 1;
drhdc04c582002-02-24 01:55:15 +00001307}
danielk19778c0a7912008-08-20 14:49:23 +00001308
drh70a8ca32008-08-21 18:49:27 +00001309/*
drh777c5382008-08-21 20:21:34 +00001310** All all of the FuncDef structures in the aBuiltinFunc[] array above
1311** to the global function hash table. This occurs at start-time (as
1312** a consequence of calling sqlite3_initialize()).
1313**
1314** After this routine runs
drh70a8ca32008-08-21 18:49:27 +00001315*/
1316void sqlite3RegisterGlobalFunctions(void){
danielk197793ce7412008-09-01 19:14:02 +00001317 /*
1318 ** The following array holds FuncDef structures for all of the functions
1319 ** defined in this file.
1320 **
1321 ** The array cannot be constant since changes are made to the
1322 ** FuncDef.pHash elements at start-time. The elements of this array
1323 ** are read-only after initialization is complete.
1324 */
1325 static SQLITE_WSD FuncDef aBuiltinFunc[] = {
1326 FUNCTION(ltrim, 1, 1, 0, trimFunc ),
1327 FUNCTION(ltrim, 2, 1, 0, trimFunc ),
1328 FUNCTION(rtrim, 1, 2, 0, trimFunc ),
1329 FUNCTION(rtrim, 2, 2, 0, trimFunc ),
1330 FUNCTION(trim, 1, 3, 0, trimFunc ),
1331 FUNCTION(trim, 2, 3, 0, trimFunc ),
1332 FUNCTION(min, -1, 0, 1, minmaxFunc ),
1333 FUNCTION(min, 0, 0, 1, 0 ),
1334 AGGREGATE(min, 1, 0, 1, minmaxStep, minMaxFinalize ),
1335 FUNCTION(max, -1, 1, 1, minmaxFunc ),
1336 FUNCTION(max, 0, 1, 1, 0 ),
1337 AGGREGATE(max, 1, 1, 1, minmaxStep, minMaxFinalize ),
1338 FUNCTION(typeof, 1, 0, 0, typeofFunc ),
1339 FUNCTION(length, 1, 0, 0, lengthFunc ),
1340 FUNCTION(substr, 2, 0, 0, substrFunc ),
1341 FUNCTION(substr, 3, 0, 0, substrFunc ),
1342 FUNCTION(abs, 1, 0, 0, absFunc ),
1343 FUNCTION(round, 1, 0, 0, roundFunc ),
1344 FUNCTION(round, 2, 0, 0, roundFunc ),
1345 FUNCTION(upper, 1, 0, 0, upperFunc ),
1346 FUNCTION(lower, 1, 0, 0, lowerFunc ),
1347 FUNCTION(coalesce, 1, 0, 0, 0 ),
1348 FUNCTION(coalesce, -1, 0, 0, ifnullFunc ),
1349 FUNCTION(coalesce, 0, 0, 0, 0 ),
1350 FUNCTION(hex, 1, 0, 0, hexFunc ),
1351 FUNCTION(ifnull, 2, 0, 1, ifnullFunc ),
1352 FUNCTION(random, -1, 0, 0, randomFunc ),
1353 FUNCTION(randomblob, 1, 0, 0, randomBlob ),
1354 FUNCTION(nullif, 2, 0, 1, nullifFunc ),
1355 FUNCTION(sqlite_version, 0, 0, 0, versionFunc ),
1356 FUNCTION(quote, 1, 0, 0, quoteFunc ),
1357 FUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid),
1358 FUNCTION(changes, 0, 0, 0, changes ),
1359 FUNCTION(total_changes, 0, 0, 0, total_changes ),
1360 FUNCTION(replace, 3, 0, 0, replaceFunc ),
1361 FUNCTION(zeroblob, 1, 0, 0, zeroblobFunc ),
1362 #ifdef SQLITE_SOUNDEX
1363 FUNCTION(soundex, 1, 0, 0, soundexFunc ),
1364 #endif
1365 #ifndef SQLITE_OMIT_LOAD_EXTENSION
1366 FUNCTION(load_extension, 1, 0, 0, loadExt ),
1367 FUNCTION(load_extension, 2, 0, 0, loadExt ),
1368 #endif
1369 AGGREGATE(sum, 1, 0, 0, sumStep, sumFinalize ),
1370 AGGREGATE(total, 1, 0, 0, sumStep, totalFinalize ),
1371 AGGREGATE(avg, 1, 0, 0, sumStep, avgFinalize ),
1372 AGGREGATE(count, 0, 0, 0, countStep, countFinalize ),
1373 AGGREGATE(count, 1, 0, 0, countStep, countFinalize ),
1374 AGGREGATE(group_concat, -1, 0, 0, groupConcatStep, groupConcatFinalize),
1375
1376 LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
1377 #ifdef SQLITE_CASE_SENSITIVE_LIKE
1378 LIKEFUNC(like, 2, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
1379 LIKEFUNC(like, 3, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
1380 #else
1381 LIKEFUNC(like, 2, &likeInfoNorm, SQLITE_FUNC_LIKE),
1382 LIKEFUNC(like, 3, &likeInfoNorm, SQLITE_FUNC_LIKE),
1383 #endif
1384 };
1385
drh70a8ca32008-08-21 18:49:27 +00001386 int i;
danielk197793ce7412008-09-01 19:14:02 +00001387 FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions);
drh106cee52008-09-03 17:11:16 +00001388 FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aBuiltinFunc);
danielk197793ce7412008-09-01 19:14:02 +00001389
drh70a8ca32008-08-21 18:49:27 +00001390 for(i=0; i<ArraySize(aBuiltinFunc); i++){
danielk197793ce7412008-09-01 19:14:02 +00001391 sqlite3FuncDefInsert(pHash, &aFunc[i]);
drh70a8ca32008-08-21 18:49:27 +00001392 }
drh777c5382008-08-21 20:21:34 +00001393 sqlite3RegisterDateTimeFunctions();
drh70a8ca32008-08-21 18:49:27 +00001394}