blob: 989e29527e8689281f9f92976984e76c3cde634a [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**
danielk1977a1644fd2007-08-29 12:31:25 +000019** $Id: func.c,v 1.170 2007/08/29 12:31:26 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
drh66150952007-07-23 19:12:41 +000027
drh55ef4d92005-08-14 01:20:37 +000028/*
29** Return the collating function associated with a function.
30*/
danielk1977dc1bdc42004-06-11 10:51:27 +000031static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){
32 return context->pColl;
33}
34
drh0bce8352002-02-28 00:41:10 +000035/*
36** Implementation of the non-aggregate min() and max() functions
37*/
drhf9b596e2004-05-26 16:54:42 +000038static void minmaxFunc(
39 sqlite3_context *context,
40 int argc,
41 sqlite3_value **argv
42){
drh0bce8352002-02-28 00:41:10 +000043 int i;
drh268380c2004-02-25 13:47:31 +000044 int mask; /* 0 for min() or 0xffffffff for max() */
drhf9b596e2004-05-26 16:54:42 +000045 int iBest;
danielk1977dc1bdc42004-06-11 10:51:27 +000046 CollSeq *pColl;
drh0bce8352002-02-28 00:41:10 +000047
drh89425d52002-02-28 03:04:48 +000048 if( argc==0 ) return;
drhc44af712004-09-02 15:53:56 +000049 mask = sqlite3_user_data(context)==0 ? 0 : -1;
danielk1977dc1bdc42004-06-11 10:51:27 +000050 pColl = sqlite3GetFuncCollSeq(context);
51 assert( pColl );
danielk1977c572ef72004-05-27 09:28:41 +000052 assert( mask==-1 || mask==0 );
drhf9b596e2004-05-26 16:54:42 +000053 iBest = 0;
drh9c054832004-05-31 18:51:57 +000054 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
drhf9b596e2004-05-26 16:54:42 +000055 for(i=1; i<argc; i++){
drh9c054832004-05-31 18:51:57 +000056 if( sqlite3_value_type(argv[i])==SQLITE_NULL ) return;
danielk1977dc1bdc42004-06-11 10:51:27 +000057 if( (sqlite3MemCompare(argv[iBest], argv[i], pColl)^mask)>=0 ){
drhf9b596e2004-05-26 16:54:42 +000058 iBest = i;
drh0bce8352002-02-28 00:41:10 +000059 }
60 }
drhf4479502004-05-27 03:12:53 +000061 sqlite3_result_value(context, argv[iBest]);
drh0bce8352002-02-28 00:41:10 +000062}
drh0bce8352002-02-28 00:41:10 +000063
drh268380c2004-02-25 13:47:31 +000064/*
65** Return the type of the argument.
66*/
drhf9b596e2004-05-26 16:54:42 +000067static void typeofFunc(
68 sqlite3_context *context,
69 int argc,
70 sqlite3_value **argv
71){
danielk197735bb9d02004-05-24 12:55:54 +000072 const char *z = 0;
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
drh0bce8352002-02-28 00:41:10 +0000172 assert( argc==3 );
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]);
188 p2 = sqlite3_value_int(argv[2]);
drh0bce8352002-02-28 00:41:10 +0000189 if( p1<0 ){
drh89425d52002-02-28 03:04:48 +0000190 p1 += len;
drh653bc752002-02-28 03:31:10 +0000191 if( p1<0 ){
192 p2 += p1;
193 p1 = 0;
194 }
drh0bce8352002-02-28 00:41:10 +0000195 }else if( p1>0 ){
196 p1--;
197 }
198 if( p1+p2>len ){
199 p2 = len-p1;
200 }
drhf764e6f2007-05-15 01:13:47 +0000201 if( p0type!=SQLITE_BLOB ){
drh4a919112007-05-15 11:55:09 +0000202 while( *z && p1 ){
203 SQLITE_SKIP_UTF8(z);
204 p1--;
drhf764e6f2007-05-15 01:13:47 +0000205 }
drh4a919112007-05-15 11:55:09 +0000206 for(z2=z; *z2 && p2; p2--){
207 SQLITE_SKIP_UTF8(z2);
drhf764e6f2007-05-15 01:13:47 +0000208 }
drh4a919112007-05-15 11:55:09 +0000209 sqlite3_result_text(context, (char*)z, z2-z, SQLITE_TRANSIENT);
drhf764e6f2007-05-15 01:13:47 +0000210 }else{
211 if( p2<0 ) p2 = 0;
212 sqlite3_result_blob(context, (char*)&z[p1], p2, SQLITE_TRANSIENT);
drh0bce8352002-02-28 00:41:10 +0000213 }
drh0bce8352002-02-28 00:41:10 +0000214}
215
216/*
217** Implementation of the round() function
218*/
danielk19770ae8b832004-05-25 12:05:56 +0000219static void roundFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
danielk197751ad0ec2004-05-24 12:39:02 +0000220 int n = 0;
drh0bce8352002-02-28 00:41:10 +0000221 double r;
drh592ac8c2005-08-13 03:07:47 +0000222 char zBuf[500]; /* larger than the %f representation of the largest double */
drh0bce8352002-02-28 00:41:10 +0000223 assert( argc==1 || argc==2 );
danielk197751ad0ec2004-05-24 12:39:02 +0000224 if( argc==2 ){
drh9c054832004-05-31 18:51:57 +0000225 if( SQLITE_NULL==sqlite3_value_type(argv[1]) ) return;
danielk197751ad0ec2004-05-24 12:39:02 +0000226 n = sqlite3_value_int(argv[1]);
227 if( n>30 ) n = 30;
228 if( n<0 ) n = 0;
229 }
drhd589a922006-03-02 03:02:48 +0000230 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
drh4f26d6c2004-05-26 23:25:30 +0000231 r = sqlite3_value_double(argv[0]);
drhe866fcb2005-07-09 02:38:06 +0000232 sqlite3_snprintf(sizeof(zBuf),zBuf,"%.*f",n,r);
drh502b9622006-04-07 13:26:42 +0000233 sqlite3AtoF(zBuf, &r);
234 sqlite3_result_double(context, r);
drh0bce8352002-02-28 00:41:10 +0000235}
drhdc04c582002-02-24 01:55:15 +0000236
danielk1977a1644fd2007-08-29 12:31:25 +0000237static void *contextMalloc(sqlite3_context *context, int nByte){
238 char *z = sqlite3_malloc(nByte);
239 if( !z && nByte>0 ){
240 sqlite3_result_error_nomem(context);
241 }
242 return z;
243}
244
drhdc04c582002-02-24 01:55:15 +0000245/*
246** Implementation of the upper() and lower() SQL functions.
247*/
danielk19770ae8b832004-05-25 12:05:56 +0000248static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
drh7a521cf2007-04-25 18:23:52 +0000249 char *z1;
250 const char *z2;
drh9310ef22007-04-27 17:16:20 +0000251 int i, n;
drh9c054832004-05-31 18:51:57 +0000252 if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return;
drh7a521cf2007-04-25 18:23:52 +0000253 z2 = (char*)sqlite3_value_text(argv[0]);
drh1f0feef2007-05-15 13:27:07 +0000254 n = sqlite3_value_bytes(argv[0]);
255 /* Verify that the call to _bytes() does not invalidate the _text() pointer */
256 assert( z2==(char*)sqlite3_value_text(argv[0]) );
drh7a521cf2007-04-25 18:23:52 +0000257 if( z2 ){
danielk1977a1644fd2007-08-29 12:31:25 +0000258 z1 = contextMalloc(context, n+1);
drh7a521cf2007-04-25 18:23:52 +0000259 if( z1 ){
drh5bb3eb92007-05-04 13:15:55 +0000260 memcpy(z1, z2, n+1);
drh7a521cf2007-04-25 18:23:52 +0000261 for(i=0; z1[i]; i++){
262 z1[i] = toupper(z1[i]);
263 }
264 sqlite3_result_text(context, z1, -1, sqlite3_free);
265 }
drhdc04c582002-02-24 01:55:15 +0000266 }
267}
danielk19770ae8b832004-05-25 12:05:56 +0000268static void lowerFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
drh7a521cf2007-04-25 18:23:52 +0000269 char *z1;
270 const char *z2;
drh9310ef22007-04-27 17:16:20 +0000271 int i, n;
drh9c054832004-05-31 18:51:57 +0000272 if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return;
drh7a521cf2007-04-25 18:23:52 +0000273 z2 = (char*)sqlite3_value_text(argv[0]);
drh1f0feef2007-05-15 13:27:07 +0000274 n = sqlite3_value_bytes(argv[0]);
275 /* Verify that the call to _bytes() does not invalidate the _text() pointer */
276 assert( z2==(char*)sqlite3_value_text(argv[0]) );
drh7a521cf2007-04-25 18:23:52 +0000277 if( z2 ){
danielk1977a1644fd2007-08-29 12:31:25 +0000278 z1 = contextMalloc(context, n+1);
drh7a521cf2007-04-25 18:23:52 +0000279 if( z1 ){
drh5bb3eb92007-05-04 13:15:55 +0000280 memcpy(z1, z2, n+1);
drh7a521cf2007-04-25 18:23:52 +0000281 for(i=0; z1[i]; i++){
282 z1[i] = tolower(z1[i]);
283 }
284 sqlite3_result_text(context, z1, -1, sqlite3_free);
285 }
drhdc04c582002-02-24 01:55:15 +0000286 }
287}
288
289/*
drhfbc99082002-02-28 03:14:18 +0000290** Implementation of the IFNULL(), NVL(), and COALESCE() functions.
jplyonb6c9e6e2004-01-19 04:53:24 +0000291** All three do the same thing. They return the first non-NULL
292** argument.
drh3212e182002-02-28 00:46:26 +0000293*/
drhf9b596e2004-05-26 16:54:42 +0000294static void ifnullFunc(
295 sqlite3_context *context,
296 int argc,
297 sqlite3_value **argv
298){
drhfbc99082002-02-28 03:14:18 +0000299 int i;
300 for(i=0; i<argc; i++){
drh9c054832004-05-31 18:51:57 +0000301 if( SQLITE_NULL!=sqlite3_value_type(argv[i]) ){
drhf4479502004-05-27 03:12:53 +0000302 sqlite3_result_value(context, argv[i]);
drhfbc99082002-02-28 03:14:18 +0000303 break;
304 }
305 }
drh3212e182002-02-28 00:46:26 +0000306}
307
308/*
drhf9ffac92002-03-02 19:00:31 +0000309** Implementation of random(). Return a random integer.
310*/
drhf9b596e2004-05-26 16:54:42 +0000311static void randomFunc(
312 sqlite3_context *context,
313 int argc,
314 sqlite3_value **argv
315){
drh52fc8492006-02-23 21:43:55 +0000316 sqlite_int64 r;
danielk19774adee202004-05-08 08:23:19 +0000317 sqlite3Randomness(sizeof(r), &r);
drh874abbe2006-02-23 21:51:12 +0000318 if( (r<<1)==0 ) r = 0; /* Prevent 0x8000.... as the result so that we */
319 /* can always do abs() of the result */
drh52fc8492006-02-23 21:43:55 +0000320 sqlite3_result_int64(context, r);
drhf9ffac92002-03-02 19:00:31 +0000321}
322
323/*
drh137c7282007-01-29 17:58:28 +0000324** Implementation of randomblob(N). Return a random blob
325** that is N bytes long.
drh63cf66f2007-01-29 15:50:05 +0000326*/
drh137c7282007-01-29 17:58:28 +0000327static void randomBlob(
drh63cf66f2007-01-29 15:50:05 +0000328 sqlite3_context *context,
329 int argc,
330 sqlite3_value **argv
331){
drh137c7282007-01-29 17:58:28 +0000332 int n;
333 unsigned char *p;
drh63cf66f2007-01-29 15:50:05 +0000334 assert( argc==1 );
335 n = sqlite3_value_int(argv[0]);
drh023ae032007-05-08 12:12:16 +0000336 if( n<1 ){
337 n = 1;
338 }
339 if( n>SQLITE_MAX_LENGTH ){
drha0206bc2007-05-08 15:15:02 +0000340 sqlite3_result_error_toobig(context);
drh023ae032007-05-08 12:12:16 +0000341 return;
342 }
danielk1977a1644fd2007-08-29 12:31:25 +0000343 p = contextMalloc(context, n);
drh02d85832007-05-07 19:31:15 +0000344 if( p ){
345 sqlite3Randomness(n, p);
drh17435752007-08-16 04:30:38 +0000346 sqlite3_result_blob(context, (char*)p, n, sqlite3_free);
drh02d85832007-05-07 19:31:15 +0000347 }
drh63cf66f2007-01-29 15:50:05 +0000348}
349
350/*
drh6ed41ad2002-04-06 14:10:47 +0000351** Implementation of the last_insert_rowid() SQL function. The return
danielk197724b03fd2004-05-10 10:34:34 +0000352** value is the same as the sqlite3_last_insert_rowid() API function.
drh6ed41ad2002-04-06 14:10:47 +0000353*/
danielk197751ad0ec2004-05-24 12:39:02 +0000354static void last_insert_rowid(
danielk19770ae8b832004-05-25 12:05:56 +0000355 sqlite3_context *context,
danielk197751ad0ec2004-05-24 12:39:02 +0000356 int arg,
357 sqlite3_value **argv
358){
drh9bb575f2004-09-06 17:24:11 +0000359 sqlite3 *db = sqlite3_user_data(context);
drhf9b596e2004-05-26 16:54:42 +0000360 sqlite3_result_int64(context, sqlite3_last_insert_rowid(db));
drh6ed41ad2002-04-06 14:10:47 +0000361}
362
rdcf146a772004-02-25 22:51:06 +0000363/*
danielk1977b28af712004-06-21 06:50:26 +0000364** Implementation of the changes() SQL function. The return value is the
365** same as the sqlite3_changes() API function.
rdcf146a772004-02-25 22:51:06 +0000366*/
danielk1977b28af712004-06-21 06:50:26 +0000367static void changes(
drhf9b596e2004-05-26 16:54:42 +0000368 sqlite3_context *context,
369 int arg,
370 sqlite3_value **argv
371){
drh9bb575f2004-09-06 17:24:11 +0000372 sqlite3 *db = sqlite3_user_data(context);
drhf4479502004-05-27 03:12:53 +0000373 sqlite3_result_int(context, sqlite3_changes(db));
rdcb0c374f2004-02-20 22:53:38 +0000374}
rdcf146a772004-02-25 22:51:06 +0000375
376/*
danielk1977b28af712004-06-21 06:50:26 +0000377** Implementation of the total_changes() SQL function. The return value is
378** the same as the sqlite3_total_changes() API function.
rdcf146a772004-02-25 22:51:06 +0000379*/
danielk1977b28af712004-06-21 06:50:26 +0000380static void total_changes(
381 sqlite3_context *context,
danielk197751ad0ec2004-05-24 12:39:02 +0000382 int arg,
383 sqlite3_value **argv
384){
drh9bb575f2004-09-06 17:24:11 +0000385 sqlite3 *db = sqlite3_user_data(context);
danielk1977b28af712004-06-21 06:50:26 +0000386 sqlite3_result_int(context, sqlite3_total_changes(db));
rdcb0c374f2004-02-20 22:53:38 +0000387}
388
drh6ed41ad2002-04-06 14:10:47 +0000389/*
drh4e5ffc52004-08-31 00:52:37 +0000390** A structure defining how to do GLOB-style comparisons.
danielk1977d02eb1f2004-06-06 09:44:03 +0000391*/
drh4e5ffc52004-08-31 00:52:37 +0000392struct compareInfo {
393 u8 matchAll;
394 u8 matchOne;
395 u8 matchSet;
396 u8 noCase;
danielk1977d02eb1f2004-06-06 09:44:03 +0000397};
drh55ef4d92005-08-14 01:20:37 +0000398
drh4e5ffc52004-08-31 00:52:37 +0000399static const struct compareInfo globInfo = { '*', '?', '[', 0 };
drh55ef4d92005-08-14 01:20:37 +0000400/* The correct SQL-92 behavior is for the LIKE operator to ignore
401** case. Thus 'a' LIKE 'A' would be true. */
402static const struct compareInfo likeInfoNorm = { '%', '_', 0, 1 };
403/* If SQLITE_CASE_SENSITIVE_LIKE is defined, then the LIKE operator
404** is case sensitive causing 'a' LIKE 'A' to be false */
405static const struct compareInfo likeInfoAlt = { '%', '_', 0, 0 };
danielk1977d02eb1f2004-06-06 09:44:03 +0000406
407/*
drh4e5ffc52004-08-31 00:52:37 +0000408** Compare two UTF-8 strings for equality where the first string can
409** potentially be a "glob" expression. Return true (1) if they
410** are the same and false (0) if they are different.
drh0ac65892002-04-20 14:24:41 +0000411**
drh4e5ffc52004-08-31 00:52:37 +0000412** Globbing rules:
drh0ac65892002-04-20 14:24:41 +0000413**
drh4e5ffc52004-08-31 00:52:37 +0000414** '*' Matches any sequence of zero or more characters.
danielk1977d02eb1f2004-06-06 09:44:03 +0000415**
drh4e5ffc52004-08-31 00:52:37 +0000416** '?' Matches exactly one character.
417**
418** [...] Matches one character from the enclosed list of
419** characters.
420**
421** [^...] Matches one character not in the enclosed list.
422**
423** With the [...] and [^...] matching, a ']' character can be included
424** in the list by making it the first character after '[' or '^'. A
425** range of characters can be specified using '-'. Example:
426** "[a-z]" matches any single lower-case letter. To match a '-', make
427** it the last character in the list.
428**
429** This routine is usually quick, but can be N**2 in the worst case.
430**
431** Hints: to match '*' or '?', put them in "[]". Like this:
432**
433** abc[*]xyz Matches "abc*xyz" only
drh0ac65892002-04-20 14:24:41 +0000434*/
danielk19777c6303c2004-11-17 16:41:29 +0000435static int patternCompare(
drh4e5ffc52004-08-31 00:52:37 +0000436 const u8 *zPattern, /* The glob pattern */
437 const u8 *zString, /* The string to compare against the glob */
danielk19777c6303c2004-11-17 16:41:29 +0000438 const struct compareInfo *pInfo, /* Information about how to do the compare */
439 const int esc /* The escape character */
danielk197751ad0ec2004-05-24 12:39:02 +0000440){
drh66150952007-07-23 19:12:41 +0000441 int c, c2;
drh4e5ffc52004-08-31 00:52:37 +0000442 int invert;
443 int seen;
drh4e5ffc52004-08-31 00:52:37 +0000444 u8 matchOne = pInfo->matchOne;
445 u8 matchAll = pInfo->matchAll;
446 u8 matchSet = pInfo->matchSet;
447 u8 noCase = pInfo->noCase;
danielk19777c6303c2004-11-17 16:41:29 +0000448 int prevEscape = 0; /* True if the previous character was 'escape' */
danielk1977d02eb1f2004-06-06 09:44:03 +0000449
drh66150952007-07-23 19:12:41 +0000450 while( (c = sqlite3Utf8Read(zPattern,0,&zPattern))!=0 ){
danielk19777c6303c2004-11-17 16:41:29 +0000451 if( !prevEscape && c==matchAll ){
drh66150952007-07-23 19:12:41 +0000452 while( (c=sqlite3Utf8Read(zPattern,0,&zPattern)) == matchAll
453 || c == matchOne ){
454 if( c==matchOne && sqlite3Utf8Read(zString, 0, &zString)==0 ){
455 return 0;
drh4e5ffc52004-08-31 00:52:37 +0000456 }
danielk1977ad7dd422004-06-06 12:41:49 +0000457 }
drh66150952007-07-23 19:12:41 +0000458 if( c==0 ){
459 return 1;
460 }else if( c==esc ){
461 c = sqlite3Utf8Read(zPattern, 0, &zPattern);
462 if( c==0 ){
463 return 0;
464 }
465 }else if( c==matchSet ){
466 assert( esc==0 ); /* This is GLOB, not LIKE */
467 assert( matchSet<0x80 ); /* '[' is a single-byte character */
468 while( *zString && patternCompare(&zPattern[-1],zString,pInfo,esc)==0 ){
drh4a919112007-05-15 11:55:09 +0000469 SQLITE_SKIP_UTF8(zString);
drh4e5ffc52004-08-31 00:52:37 +0000470 }
471 return *zString!=0;
drh66150952007-07-23 19:12:41 +0000472 }
473 while( (c2 = sqlite3Utf8Read(zString,0,&zString))!=0 ){
474 if( noCase ){
475 c2 = c2<0x80 ? sqlite3UpperToLower[c2] : c2;
476 c = c<0x80 ? sqlite3UpperToLower[c] : c;
477 while( c2 != 0 && c2 != c ){
478 c2 = sqlite3Utf8Read(zString, 0, &zString);
479 if( c2<0x80 ) c2 = sqlite3UpperToLower[c2];
drh4e5ffc52004-08-31 00:52:37 +0000480 }
drh66150952007-07-23 19:12:41 +0000481 }else{
482 while( c2 != 0 && c2 != c ){
483 c2 = sqlite3Utf8Read(zString, 0, &zString);
484 }
drh4e5ffc52004-08-31 00:52:37 +0000485 }
drh66150952007-07-23 19:12:41 +0000486 if( c2==0 ) return 0;
487 if( patternCompare(zPattern,zString,pInfo,esc) ) return 1;
488 }
489 return 0;
490 }else if( !prevEscape && c==matchOne ){
491 if( sqlite3Utf8Read(zString, 0, &zString)==0 ){
drh4e5ffc52004-08-31 00:52:37 +0000492 return 0;
danielk1977d02eb1f2004-06-06 09:44:03 +0000493 }
drh4e5ffc52004-08-31 00:52:37 +0000494 }else if( c==matchSet ){
495 int prior_c = 0;
danielk19777c6303c2004-11-17 16:41:29 +0000496 assert( esc==0 ); /* This only occurs for GLOB, not LIKE */
drh4e5ffc52004-08-31 00:52:37 +0000497 seen = 0;
498 invert = 0;
drh66150952007-07-23 19:12:41 +0000499 c = sqlite3Utf8Read(zString, 0, &zString);
drh4e5ffc52004-08-31 00:52:37 +0000500 if( c==0 ) return 0;
drh66150952007-07-23 19:12:41 +0000501 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);
502 if( c2=='^' ){
503 invert = 1;
504 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);
505 }
drh4e5ffc52004-08-31 00:52:37 +0000506 if( c2==']' ){
507 if( c==']' ) seen = 1;
drh66150952007-07-23 19:12:41 +0000508 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);
drh4e5ffc52004-08-31 00:52:37 +0000509 }
drh66150952007-07-23 19:12:41 +0000510 while( c2 && c2!=']' ){
511 if( c2=='-' && zPattern[0]!=']' && zPattern[0]!=0 && prior_c>0 ){
512 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);
drh4e5ffc52004-08-31 00:52:37 +0000513 if( c>=prior_c && c<=c2 ) seen = 1;
514 prior_c = 0;
drh4e5ffc52004-08-31 00:52:37 +0000515 }else{
drh66150952007-07-23 19:12:41 +0000516 if( c==c2 ){
517 seen = 1;
518 }
drh4e5ffc52004-08-31 00:52:37 +0000519 prior_c = c2;
520 }
drh66150952007-07-23 19:12:41 +0000521 c2 = sqlite3Utf8Read(zPattern, 0, &zPattern);
drh4e5ffc52004-08-31 00:52:37 +0000522 }
drh66150952007-07-23 19:12:41 +0000523 if( c2==0 || (seen ^ invert)==0 ){
524 return 0;
525 }
526 }else if( esc==c && !prevEscape ){
danielk19777c6303c2004-11-17 16:41:29 +0000527 prevEscape = 1;
drh4e5ffc52004-08-31 00:52:37 +0000528 }else{
drh66150952007-07-23 19:12:41 +0000529 c2 = sqlite3Utf8Read(zString, 0, &zString);
drh4e5ffc52004-08-31 00:52:37 +0000530 if( noCase ){
drh66150952007-07-23 19:12:41 +0000531 c = c<0x80 ? sqlite3UpperToLower[c] : c;
532 c2 = c2<0x80 ? sqlite3UpperToLower[c2] : c2;
drh4e5ffc52004-08-31 00:52:37 +0000533 }
drh66150952007-07-23 19:12:41 +0000534 if( c!=c2 ){
535 return 0;
536 }
danielk19777c6303c2004-11-17 16:41:29 +0000537 prevEscape = 0;
danielk1977d02eb1f2004-06-06 09:44:03 +0000538 }
danielk197751ad0ec2004-05-24 12:39:02 +0000539 }
drh4e5ffc52004-08-31 00:52:37 +0000540 return *zString==0;
drh0ac65892002-04-20 14:24:41 +0000541}
drh4e5ffc52004-08-31 00:52:37 +0000542
drh55ef4d92005-08-14 01:20:37 +0000543/*
544** Count the number of times that the LIKE operator (or GLOB which is
545** just a variation of LIKE) gets called. This is used for testing
546** only.
547*/
548#ifdef SQLITE_TEST
549int sqlite3_like_count = 0;
550#endif
551
danielk19773f6b0872004-06-17 05:36:44 +0000552
553/*
554** Implementation of the like() SQL function. This function implements
555** the build-in LIKE operator. The first argument to the function is the
556** pattern and the second argument is the string. So, the SQL statements:
557**
558** A LIKE B
559**
560** is implemented as like(B,A).
561**
drh55ef4d92005-08-14 01:20:37 +0000562** This same function (with a different compareInfo structure) computes
563** the GLOB operator.
danielk19773f6b0872004-06-17 05:36:44 +0000564*/
565static void likeFunc(
566 sqlite3_context *context,
567 int argc,
568 sqlite3_value **argv
569){
drhbeb818d2007-05-08 15:34:47 +0000570 const unsigned char *zA, *zB;
drh85892bd2007-05-10 13:23:22 +0000571 int escape = 0;
drhbeb818d2007-05-08 15:34:47 +0000572
drh1f0feef2007-05-15 13:27:07 +0000573 zB = sqlite3_value_text(argv[0]);
574 zA = sqlite3_value_text(argv[1]);
575
drhbeb818d2007-05-08 15:34:47 +0000576 /* Limit the length of the LIKE or GLOB pattern to avoid problems
577 ** of deep recursion and N*N behavior in patternCompare().
578 */
danielk1977b56fe1f2007-05-09 08:24:44 +0000579 if( sqlite3_value_bytes(argv[0])>SQLITE_MAX_LIKE_PATTERN_LENGTH ){
drhbeb818d2007-05-08 15:34:47 +0000580 sqlite3_result_error(context, "LIKE or GLOB pattern too complex", -1);
581 return;
582 }
drh1f0feef2007-05-15 13:27:07 +0000583 assert( zB==sqlite3_value_text(argv[0]) ); /* Encoding did not change */
drhbeb818d2007-05-08 15:34:47 +0000584
danielk19777c6303c2004-11-17 16:41:29 +0000585 if( argc==3 ){
586 /* The escape character string must consist of a single UTF-8 character.
587 ** Otherwise, return an error.
588 */
589 const unsigned char *zEsc = sqlite3_value_text(argv[2]);
drh7a521cf2007-04-25 18:23:52 +0000590 if( zEsc==0 ) return;
drhee858132007-05-08 20:37:38 +0000591 if( sqlite3Utf8CharLen((char*)zEsc, -1)!=1 ){
danielk19777c6303c2004-11-17 16:41:29 +0000592 sqlite3_result_error(context,
593 "ESCAPE expression must be a single character", -1);
594 return;
595 }
drh66150952007-07-23 19:12:41 +0000596 escape = sqlite3Utf8Read(zEsc, 0, &zEsc);
danielk19777c6303c2004-11-17 16:41:29 +0000597 }
danielk19773f6b0872004-06-17 05:36:44 +0000598 if( zA && zB ){
drh55ef4d92005-08-14 01:20:37 +0000599 struct compareInfo *pInfo = sqlite3_user_data(context);
600#ifdef SQLITE_TEST
601 sqlite3_like_count++;
602#endif
drhbeb818d2007-05-08 15:34:47 +0000603
danielk1977b56fe1f2007-05-09 08:24:44 +0000604 sqlite3_result_int(context, patternCompare(zB, zA, pInfo, escape));
danielk197751ad0ec2004-05-24 12:39:02 +0000605 }
drh8912d102002-05-26 21:34:58 +0000606}
607
608/*
609** Implementation of the NULLIF(x,y) function. The result is the first
610** argument if the arguments are different. The result is NULL if the
611** arguments are equal to each other.
612*/
drhf9b596e2004-05-26 16:54:42 +0000613static void nullifFunc(
614 sqlite3_context *context,
615 int argc,
616 sqlite3_value **argv
617){
danielk1977dc1bdc42004-06-11 10:51:27 +0000618 CollSeq *pColl = sqlite3GetFuncCollSeq(context);
619 if( sqlite3MemCompare(argv[0], argv[1], pColl)!=0 ){
drhf4479502004-05-27 03:12:53 +0000620 sqlite3_result_value(context, argv[0]);
drh8912d102002-05-26 21:34:58 +0000621 }
drh0ac65892002-04-20 14:24:41 +0000622}
623
drh647cb0e2002-11-04 19:32:25 +0000624/*
625** Implementation of the VERSION(*) function. The result is the version
626** of the SQLite library that is running.
627*/
drhf9b596e2004-05-26 16:54:42 +0000628static void versionFunc(
629 sqlite3_context *context,
630 int argc,
631 sqlite3_value **argv
632){
danielk1977d8123362004-06-12 09:25:12 +0000633 sqlite3_result_text(context, sqlite3_version, -1, SQLITE_STATIC);
drh647cb0e2002-11-04 19:32:25 +0000634}
635
drh137c7282007-01-29 17:58:28 +0000636/* Array for converting from half-bytes (nybbles) into ASCII hex
637** digits. */
638static const char hexdigits[] = {
639 '0', '1', '2', '3', '4', '5', '6', '7',
640 '8', '9', 'A', 'B', 'C', 'D', 'E', 'F'
641};
danielk1977d641d642004-11-18 15:44:29 +0000642
drh47394702003-08-20 01:03:33 +0000643/*
644** EXPERIMENTAL - This is not an official function. The interface may
645** change. This function may disappear. Do not write code that depends
646** on this function.
647**
648** Implementation of the QUOTE() function. This function takes a single
649** argument. If the argument is numeric, the return value is the same as
650** the argument. If the argument is NULL, the return value is the string
651** "NULL". Otherwise, the argument is enclosed in single quotes with
652** single-quote escapes.
653*/
danielk19770ae8b832004-05-25 12:05:56 +0000654static void quoteFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
drh47394702003-08-20 01:03:33 +0000655 if( argc<1 ) return;
drhf9b596e2004-05-26 16:54:42 +0000656 switch( sqlite3_value_type(argv[0]) ){
drh9c054832004-05-31 18:51:57 +0000657 case SQLITE_NULL: {
danielk1977d8123362004-06-12 09:25:12 +0000658 sqlite3_result_text(context, "NULL", 4, SQLITE_STATIC);
drhf9b596e2004-05-26 16:54:42 +0000659 break;
drh47394702003-08-20 01:03:33 +0000660 }
drh9c054832004-05-31 18:51:57 +0000661 case SQLITE_INTEGER:
662 case SQLITE_FLOAT: {
drhf4479502004-05-27 03:12:53 +0000663 sqlite3_result_value(context, argv[0]);
drhf9b596e2004-05-26 16:54:42 +0000664 break;
665 }
danielk19773f41e972004-06-08 00:39:01 +0000666 case SQLITE_BLOB: {
danielk19773f41e972004-06-08 00:39:01 +0000667 char *zText = 0;
danielk19773f41e972004-06-08 00:39:01 +0000668 char const *zBlob = sqlite3_value_blob(argv[0]);
drh1f0feef2007-05-15 13:27:07 +0000669 int nBlob = sqlite3_value_bytes(argv[0]);
670 assert( zBlob==sqlite3_value_blob(argv[0]) ); /* No encoding change */
danielk19773f41e972004-06-08 00:39:01 +0000671
drh023ae032007-05-08 12:12:16 +0000672 if( 2*nBlob+4>SQLITE_MAX_LENGTH ){
drha0206bc2007-05-08 15:15:02 +0000673 sqlite3_result_error_toobig(context);
drh023ae032007-05-08 12:12:16 +0000674 return;
675 }
danielk1977a1644fd2007-08-29 12:31:25 +0000676 zText = (char *)contextMalloc(context, (2*nBlob)+4);
677 if( zText ){
danielk19773f41e972004-06-08 00:39:01 +0000678 int i;
679 for(i=0; i<nBlob; i++){
680 zText[(i*2)+2] = hexdigits[(zBlob[i]>>4)&0x0F];
681 zText[(i*2)+3] = hexdigits[(zBlob[i])&0x0F];
682 }
683 zText[(nBlob*2)+2] = '\'';
684 zText[(nBlob*2)+3] = '\0';
685 zText[0] = 'X';
686 zText[1] = '\'';
danielk1977d8123362004-06-12 09:25:12 +0000687 sqlite3_result_text(context, zText, -1, SQLITE_TRANSIENT);
drh17435752007-08-16 04:30:38 +0000688 sqlite3_free(zText);
danielk19773f41e972004-06-08 00:39:01 +0000689 }
690 break;
691 }
drh9c054832004-05-31 18:51:57 +0000692 case SQLITE_TEXT: {
drh023ae032007-05-08 12:12:16 +0000693 int i,j;
694 u64 n;
drh2646da72005-12-09 20:02:05 +0000695 const unsigned char *zArg = sqlite3_value_text(argv[0]);
drhf9b596e2004-05-26 16:54:42 +0000696 char *z;
697
drh7a521cf2007-04-25 18:23:52 +0000698 if( zArg==0 ) return;
drh023ae032007-05-08 12:12:16 +0000699 for(i=0, n=0; zArg[i]; i++){ if( zArg[i]=='\'' ) n++; }
700 if( i+n+3>SQLITE_MAX_LENGTH ){
drha0206bc2007-05-08 15:15:02 +0000701 sqlite3_result_error_toobig(context);
drh023ae032007-05-08 12:12:16 +0000702 return;
703 }
danielk1977a1644fd2007-08-29 12:31:25 +0000704 z = contextMalloc(context, i+n+3);
705 if( z ){
706 z[0] = '\'';
707 for(i=0, j=1; zArg[i]; i++){
708 z[j++] = zArg[i];
709 if( zArg[i]=='\'' ){
710 z[j++] = '\'';
711 }
drhf9b596e2004-05-26 16:54:42 +0000712 }
danielk1977a1644fd2007-08-29 12:31:25 +0000713 z[j++] = '\'';
714 z[j] = 0;
715 sqlite3_result_text(context, z, j, sqlite3_free);
drhf9b596e2004-05-26 16:54:42 +0000716 }
drhf9b596e2004-05-26 16:54:42 +0000717 }
drh47394702003-08-20 01:03:33 +0000718 }
719}
720
drh137c7282007-01-29 17:58:28 +0000721/*
722** The hex() function. Interpret the argument as a blob. Return
723** a hexadecimal rendering as text.
724*/
725static void hexFunc(
726 sqlite3_context *context,
727 int argc,
728 sqlite3_value **argv
729){
730 int i, n;
731 const unsigned char *pBlob;
732 char *zHex, *z;
733 assert( argc==1 );
drh1f0feef2007-05-15 13:27:07 +0000734 pBlob = sqlite3_value_blob(argv[0]);
drh137c7282007-01-29 17:58:28 +0000735 n = sqlite3_value_bytes(argv[0]);
drh023ae032007-05-08 12:12:16 +0000736 if( n*2+1>SQLITE_MAX_LENGTH ){
drha0206bc2007-05-08 15:15:02 +0000737 sqlite3_result_error_toobig(context);
drh023ae032007-05-08 12:12:16 +0000738 return;
739 }
drh1f0feef2007-05-15 13:27:07 +0000740 assert( pBlob==sqlite3_value_blob(argv[0]) ); /* No encoding change */
danielk1977a1644fd2007-08-29 12:31:25 +0000741 z = zHex = contextMalloc(context, n*2 + 1);
742 if( zHex ){
743 for(i=0; i<n; i++, pBlob++){
744 unsigned char c = *pBlob;
745 *(z++) = hexdigits[(c>>4)&0xf];
746 *(z++) = hexdigits[c&0xf];
747 }
748 *z = 0;
749 sqlite3_result_text(context, zHex, n*2, sqlite3_free);
drh137c7282007-01-29 17:58:28 +0000750 }
drh137c7282007-01-29 17:58:28 +0000751}
752
drh26b6d902007-03-17 13:27:54 +0000753/*
drh8cff3822007-05-02 02:08:28 +0000754** The zeroblob(N) function returns a zero-filled blob of size N bytes.
755*/
756static void zeroblobFunc(
757 sqlite3_context *context,
758 int argc,
759 sqlite3_value **argv
760){
drh98640a32007-06-07 19:08:32 +0000761 i64 n;
drh8cff3822007-05-02 02:08:28 +0000762 assert( argc==1 );
drh98640a32007-06-07 19:08:32 +0000763 n = sqlite3_value_int64(argv[0]);
764 if( n>SQLITE_MAX_LENGTH ){
765 sqlite3_result_error_toobig(context);
766 }else{
767 sqlite3_result_zeroblob(context, n);
768 }
drh8cff3822007-05-02 02:08:28 +0000769}
770
771/*
drh26b6d902007-03-17 13:27:54 +0000772** The replace() function. Three arguments are all strings: call
773** them A, B, and C. The result is also a string which is derived
774** from A by replacing every occurance of B with C. The match
775** must be exact. Collating sequences are not used.
776*/
777static void replaceFunc(
778 sqlite3_context *context,
779 int argc,
780 sqlite3_value **argv
781){
782 const unsigned char *zStr; /* The input string A */
783 const unsigned char *zPattern; /* The pattern string B */
784 const unsigned char *zRep; /* The replacement string C */
785 unsigned char *zOut; /* The output */
786 int nStr; /* Size of zStr */
787 int nPattern; /* Size of zPattern */
788 int nRep; /* Size of zRep */
drh2e6400b2007-05-08 15:46:18 +0000789 i64 nOut; /* Maximum size of zOut */
drh26b6d902007-03-17 13:27:54 +0000790 int loopLimit; /* Last zStr[] that might match zPattern[] */
791 int i, j; /* Loop counters */
792
793 assert( argc==3 );
drh26b6d902007-03-17 13:27:54 +0000794 zStr = sqlite3_value_text(argv[0]);
drh7a521cf2007-04-25 18:23:52 +0000795 if( zStr==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000796 nStr = sqlite3_value_bytes(argv[0]);
797 assert( zStr==sqlite3_value_text(argv[0]) ); /* No encoding change */
drh26b6d902007-03-17 13:27:54 +0000798 zPattern = sqlite3_value_text(argv[1]);
drh709cff32007-04-27 01:18:02 +0000799 if( zPattern==0 || zPattern[0]==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000800 nPattern = sqlite3_value_bytes(argv[1]);
801 assert( zPattern==sqlite3_value_text(argv[1]) ); /* No encoding change */
drh26b6d902007-03-17 13:27:54 +0000802 zRep = sqlite3_value_text(argv[2]);
drh7a521cf2007-04-25 18:23:52 +0000803 if( zRep==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000804 nRep = sqlite3_value_bytes(argv[2]);
805 assert( zRep==sqlite3_value_text(argv[2]) );
drh2e6400b2007-05-08 15:46:18 +0000806 nOut = nStr + 1;
807 assert( nOut<SQLITE_MAX_LENGTH );
danielk1977a1644fd2007-08-29 12:31:25 +0000808 zOut = contextMalloc(context, (int)nOut);
drh2e6400b2007-05-08 15:46:18 +0000809 if( zOut==0 ){
810 return;
drh26b6d902007-03-17 13:27:54 +0000811 }
drh26b6d902007-03-17 13:27:54 +0000812 loopLimit = nStr - nPattern;
813 for(i=j=0; i<=loopLimit; i++){
814 if( zStr[i]!=zPattern[0] || memcmp(&zStr[i], zPattern, nPattern) ){
815 zOut[j++] = zStr[i];
816 }else{
drh4a50aac2007-08-23 02:47:53 +0000817 u8 *zOld;
drh2e6400b2007-05-08 15:46:18 +0000818 nOut += nRep - nPattern;
819 if( nOut>=SQLITE_MAX_LENGTH ){
drha0206bc2007-05-08 15:15:02 +0000820 sqlite3_result_error_toobig(context);
danielk197717374e82007-05-08 14:39:04 +0000821 sqlite3_free(zOut);
822 return;
823 }
drh4a50aac2007-08-23 02:47:53 +0000824 zOld = zOut;
drh2e6400b2007-05-08 15:46:18 +0000825 zOut = sqlite3_realloc(zOut, (int)nOut);
826 if( zOut==0 ){
danielk1977a1644fd2007-08-29 12:31:25 +0000827 sqlite3_result_error_nomem(context);
drh4a50aac2007-08-23 02:47:53 +0000828 sqlite3_free(zOld);
drh2e6400b2007-05-08 15:46:18 +0000829 return;
830 }
drh26b6d902007-03-17 13:27:54 +0000831 memcpy(&zOut[j], zRep, nRep);
832 j += nRep;
833 i += nPattern-1;
834 }
835 }
drh2e6400b2007-05-08 15:46:18 +0000836 assert( j+nStr-i+1==nOut );
drh26b6d902007-03-17 13:27:54 +0000837 memcpy(&zOut[j], &zStr[i], nStr-i);
838 j += nStr - i;
839 assert( j<=nOut );
840 zOut[j] = 0;
841 sqlite3_result_text(context, (char*)zOut, j, sqlite3_free);
842}
843
drh309b3382007-03-17 17:52:42 +0000844/*
845** Implementation of the TRIM(), LTRIM(), and RTRIM() functions.
846** The userdata is 0x1 for left trim, 0x2 for right trim, 0x3 for both.
847*/
848static void trimFunc(
849 sqlite3_context *context,
850 int argc,
851 sqlite3_value **argv
852){
853 const unsigned char *zIn; /* Input string */
854 const unsigned char *zCharSet; /* Set of characters to trim */
855 int nIn; /* Number of bytes in input */
drhd1e3a612007-04-27 21:59:52 +0000856 int flags; /* 1: trimleft 2: trimright 3: trim */
857 int i; /* Loop counter */
858 unsigned char *aLen; /* Length of each character in zCharSet */
859 const unsigned char **azChar; /* Individual characters in zCharSet */
860 int nChar; /* Number of characters in zCharSet */
861
drh309b3382007-03-17 17:52:42 +0000862 if( sqlite3_value_type(argv[0])==SQLITE_NULL ){
863 return;
864 }
865 zIn = sqlite3_value_text(argv[0]);
drh7a521cf2007-04-25 18:23:52 +0000866 if( zIn==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000867 nIn = sqlite3_value_bytes(argv[0]);
868 assert( zIn==sqlite3_value_text(argv[0]) );
drh309b3382007-03-17 17:52:42 +0000869 if( argc==1 ){
drhd1e3a612007-04-27 21:59:52 +0000870 static const unsigned char lenOne[] = { 1 };
drh8cff3822007-05-02 02:08:28 +0000871 static const unsigned char *azOne[] = { (u8*)" " };
drhd1e3a612007-04-27 21:59:52 +0000872 nChar = 1;
drh8cff3822007-05-02 02:08:28 +0000873 aLen = (u8*)lenOne;
874 azChar = azOne;
drhd1e3a612007-04-27 21:59:52 +0000875 zCharSet = 0;
drh7a521cf2007-04-25 18:23:52 +0000876 }else if( (zCharSet = sqlite3_value_text(argv[1]))==0 ){
drh309b3382007-03-17 17:52:42 +0000877 return;
drhd1e3a612007-04-27 21:59:52 +0000878 }else{
879 const unsigned char *z;
880 for(z=zCharSet, nChar=0; *z; nChar++){
drh4a919112007-05-15 11:55:09 +0000881 SQLITE_SKIP_UTF8(z);
drhd1e3a612007-04-27 21:59:52 +0000882 }
883 if( nChar>0 ){
danielk1977a1644fd2007-08-29 12:31:25 +0000884 azChar = contextMalloc(context, nChar*(sizeof(char*)+1));
drhd1e3a612007-04-27 21:59:52 +0000885 if( azChar==0 ){
886 return;
887 }
888 aLen = (unsigned char*)&azChar[nChar];
889 for(z=zCharSet, nChar=0; *z; nChar++){
890 azChar[nChar] = z;
drh4a919112007-05-15 11:55:09 +0000891 SQLITE_SKIP_UTF8(z);
drhd1e3a612007-04-27 21:59:52 +0000892 aLen[nChar] = z - azChar[nChar];
893 }
894 }
drh309b3382007-03-17 17:52:42 +0000895 }
drhd1e3a612007-04-27 21:59:52 +0000896 if( nChar>0 ){
drh309b3382007-03-17 17:52:42 +0000897 flags = (int)sqlite3_user_data(context);
898 if( flags & 1 ){
drhd1e3a612007-04-27 21:59:52 +0000899 while( nIn>0 ){
900 int len;
901 for(i=0; i<nChar; i++){
902 len = aLen[i];
903 if( memcmp(zIn, azChar[i], len)==0 ) break;
904 }
905 if( i>=nChar ) break;
906 zIn += len;
907 nIn -= len;
drh309b3382007-03-17 17:52:42 +0000908 }
909 }
910 if( flags & 2 ){
drhd1e3a612007-04-27 21:59:52 +0000911 while( nIn>0 ){
912 int len;
913 for(i=0; i<nChar; i++){
914 len = aLen[i];
915 if( len<=nIn && memcmp(&zIn[nIn-len],azChar[i],len)==0 ) break;
916 }
917 if( i>=nChar ) break;
918 nIn -= len;
drh309b3382007-03-17 17:52:42 +0000919 }
920 }
drhd1e3a612007-04-27 21:59:52 +0000921 if( zCharSet ){
922 sqlite3_free(azChar);
923 }
drh309b3382007-03-17 17:52:42 +0000924 }
925 sqlite3_result_text(context, (char*)zIn, nIn, SQLITE_TRANSIENT);
926}
drh26b6d902007-03-17 13:27:54 +0000927
drhd24cc422003-03-27 12:51:24 +0000928#ifdef SQLITE_SOUNDEX
929/*
930** Compute the soundex encoding of a word.
931*/
drh137c7282007-01-29 17:58:28 +0000932static void soundexFunc(
933 sqlite3_context *context,
934 int argc,
935 sqlite3_value **argv
936){
drhd24cc422003-03-27 12:51:24 +0000937 char zResult[8];
drh4c755c02004-08-08 20:22:17 +0000938 const u8 *zIn;
drhd24cc422003-03-27 12:51:24 +0000939 int i, j;
940 static const unsigned char iCode[] = {
941 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
942 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
943 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
944 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
945 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
946 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
947 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
948 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
949 };
950 assert( argc==1 );
drh4c755c02004-08-08 20:22:17 +0000951 zIn = (u8*)sqlite3_value_text(argv[0]);
drhbdf67e02006-08-19 11:34:01 +0000952 if( zIn==0 ) zIn = (u8*)"";
drhd24cc422003-03-27 12:51:24 +0000953 for(i=0; zIn[i] && !isalpha(zIn[i]); i++){}
954 if( zIn[i] ){
drhbdf67e02006-08-19 11:34:01 +0000955 u8 prevcode = iCode[zIn[i]&0x7f];
drhd24cc422003-03-27 12:51:24 +0000956 zResult[0] = toupper(zIn[i]);
957 for(j=1; j<4 && zIn[i]; i++){
958 int code = iCode[zIn[i]&0x7f];
959 if( code>0 ){
drhbdf67e02006-08-19 11:34:01 +0000960 if( code!=prevcode ){
961 prevcode = code;
962 zResult[j++] = code + '0';
963 }
964 }else{
965 prevcode = 0;
drhd24cc422003-03-27 12:51:24 +0000966 }
967 }
968 while( j<4 ){
969 zResult[j++] = '0';
970 }
971 zResult[j] = 0;
danielk1977d8123362004-06-12 09:25:12 +0000972 sqlite3_result_text(context, zResult, 4, SQLITE_TRANSIENT);
drhd24cc422003-03-27 12:51:24 +0000973 }else{
danielk1977d8123362004-06-12 09:25:12 +0000974 sqlite3_result_text(context, "?000", 4, SQLITE_STATIC);
drhd24cc422003-03-27 12:51:24 +0000975 }
976}
977#endif
978
drhfdb83b22006-06-17 14:12:47 +0000979#ifndef SQLITE_OMIT_LOAD_EXTENSION
980/*
981** A function that loads a shared-library extension then returns NULL.
982*/
983static void loadExt(sqlite3_context *context, int argc, sqlite3_value **argv){
danielk197765fd59f2006-06-24 11:51:33 +0000984 const char *zFile = (const char *)sqlite3_value_text(argv[0]);
drh7a521cf2007-04-25 18:23:52 +0000985 const char *zProc;
drhfdb83b22006-06-17 14:12:47 +0000986 sqlite3 *db = sqlite3_user_data(context);
987 char *zErrMsg = 0;
988
989 if( argc==2 ){
danielk197765fd59f2006-06-24 11:51:33 +0000990 zProc = (const char *)sqlite3_value_text(argv[1]);
drh7a521cf2007-04-25 18:23:52 +0000991 }else{
992 zProc = 0;
drhfdb83b22006-06-17 14:12:47 +0000993 }
drh7a521cf2007-04-25 18:23:52 +0000994 if( zFile && sqlite3_load_extension(db, zFile, zProc, &zErrMsg) ){
drhfdb83b22006-06-17 14:12:47 +0000995 sqlite3_result_error(context, zErrMsg, -1);
996 sqlite3_free(zErrMsg);
997 }
998}
999#endif
1000
drh193a6b42002-07-07 16:52:46 +00001001#ifdef SQLITE_TEST
1002/*
1003** This function generates a string of random characters. Used for
1004** generating test data.
1005*/
danielk19770ae8b832004-05-25 12:05:56 +00001006static void randStr(sqlite3_context *context, int argc, sqlite3_value **argv){
drhbbd82df2004-02-11 09:46:30 +00001007 static const unsigned char zSrc[] =
drh193a6b42002-07-07 16:52:46 +00001008 "abcdefghijklmnopqrstuvwxyz"
1009 "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
1010 "0123456789"
1011 ".-!,:*^+=_|?/<> ";
1012 int iMin, iMax, n, r, i;
drhbbd82df2004-02-11 09:46:30 +00001013 unsigned char zBuf[1000];
drh193a6b42002-07-07 16:52:46 +00001014 if( argc>=1 ){
drhf9b596e2004-05-26 16:54:42 +00001015 iMin = sqlite3_value_int(argv[0]);
drh193a6b42002-07-07 16:52:46 +00001016 if( iMin<0 ) iMin = 0;
1017 if( iMin>=sizeof(zBuf) ) iMin = sizeof(zBuf)-1;
1018 }else{
1019 iMin = 1;
1020 }
1021 if( argc>=2 ){
drhf9b596e2004-05-26 16:54:42 +00001022 iMax = sqlite3_value_int(argv[1]);
drh193a6b42002-07-07 16:52:46 +00001023 if( iMax<iMin ) iMax = iMin;
drh1dba7272004-01-16 13:58:18 +00001024 if( iMax>=sizeof(zBuf) ) iMax = sizeof(zBuf)-1;
drh193a6b42002-07-07 16:52:46 +00001025 }else{
1026 iMax = 50;
1027 }
1028 n = iMin;
1029 if( iMax>iMin ){
danielk19774adee202004-05-08 08:23:19 +00001030 sqlite3Randomness(sizeof(r), &r);
drhbbd82df2004-02-11 09:46:30 +00001031 r &= 0x7fffffff;
drh193a6b42002-07-07 16:52:46 +00001032 n += r%(iMax + 1 - iMin);
1033 }
drh1dba7272004-01-16 13:58:18 +00001034 assert( n<sizeof(zBuf) );
danielk19774adee202004-05-08 08:23:19 +00001035 sqlite3Randomness(n, zBuf);
drh193a6b42002-07-07 16:52:46 +00001036 for(i=0; i<n; i++){
drhbbd82df2004-02-11 09:46:30 +00001037 zBuf[i] = zSrc[zBuf[i]%(sizeof(zSrc)-1)];
drh193a6b42002-07-07 16:52:46 +00001038 }
1039 zBuf[n] = 0;
drh2646da72005-12-09 20:02:05 +00001040 sqlite3_result_text(context, (char*)zBuf, n, SQLITE_TRANSIENT);
danielk1977d8123362004-06-12 09:25:12 +00001041}
drh0e3d7472004-06-19 17:33:07 +00001042#endif /* SQLITE_TEST */
danielk1977d8123362004-06-12 09:25:12 +00001043
drh0e3d7472004-06-19 17:33:07 +00001044#ifdef SQLITE_TEST
danielk1977d8123362004-06-12 09:25:12 +00001045/*
1046** The following two SQL functions are used to test returning a text
1047** result with a destructor. Function 'test_destructor' takes one argument
1048** and returns the same argument interpreted as TEXT. A destructor is
1049** passed with the sqlite3_result_text() call.
1050**
1051** SQL function 'test_destructor_count' returns the number of outstanding
1052** allocations made by 'test_destructor';
1053**
1054** WARNING: Not threadsafe.
1055*/
1056static int test_destructor_count_var = 0;
1057static void destructor(void *p){
1058 char *zVal = (char *)p;
1059 assert(zVal);
1060 zVal--;
drh17435752007-08-16 04:30:38 +00001061 sqlite3_free(zVal);
danielk1977d8123362004-06-12 09:25:12 +00001062 test_destructor_count_var--;
1063}
1064static void test_destructor(
1065 sqlite3_context *pCtx,
1066 int nArg,
1067 sqlite3_value **argv
1068){
1069 char *zVal;
danielk1977f4618892004-06-28 13:09:11 +00001070 int len;
drh9bb575f2004-09-06 17:24:11 +00001071 sqlite3 *db = sqlite3_user_data(pCtx);
danielk1977f4618892004-06-28 13:09:11 +00001072
danielk1977d8123362004-06-12 09:25:12 +00001073 test_destructor_count_var++;
1074 assert( nArg==1 );
1075 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
drhb21c8cd2007-08-21 19:33:56 +00001076 len = sqlite3ValueBytes(argv[0], ENC(db));
danielk1977c6cc77d2007-08-16 10:36:33 +00001077 zVal = sqlite3MallocZero(len+3);
danielk1977f4618892004-06-28 13:09:11 +00001078 zVal[len] = 0;
1079 zVal[len-1] = 0;
danielk1977d8123362004-06-12 09:25:12 +00001080 assert( zVal );
1081 zVal++;
drhb21c8cd2007-08-21 19:33:56 +00001082 memcpy(zVal, sqlite3ValueText(argv[0], ENC(db)), len);
danielk197714db2662006-01-09 16:12:04 +00001083 if( ENC(db)==SQLITE_UTF8 ){
danielk1977f4618892004-06-28 13:09:11 +00001084 sqlite3_result_text(pCtx, zVal, -1, destructor);
drh6c626082004-11-14 21:56:29 +00001085#ifndef SQLITE_OMIT_UTF16
danielk197714db2662006-01-09 16:12:04 +00001086 }else if( ENC(db)==SQLITE_UTF16LE ){
danielk1977f4618892004-06-28 13:09:11 +00001087 sqlite3_result_text16le(pCtx, zVal, -1, destructor);
1088 }else{
1089 sqlite3_result_text16be(pCtx, zVal, -1, destructor);
drh6c626082004-11-14 21:56:29 +00001090#endif /* SQLITE_OMIT_UTF16 */
danielk1977f4618892004-06-28 13:09:11 +00001091 }
danielk1977d8123362004-06-12 09:25:12 +00001092}
1093static void test_destructor_count(
1094 sqlite3_context *pCtx,
1095 int nArg,
1096 sqlite3_value **argv
1097){
1098 sqlite3_result_int(pCtx, test_destructor_count_var);
drh193a6b42002-07-07 16:52:46 +00001099}
drh0e3d7472004-06-19 17:33:07 +00001100#endif /* SQLITE_TEST */
danielk19773f6b0872004-06-17 05:36:44 +00001101
drh0e3d7472004-06-19 17:33:07 +00001102#ifdef SQLITE_TEST
1103/*
1104** Routines for testing the sqlite3_get_auxdata() and sqlite3_set_auxdata()
1105** interface.
1106**
1107** The test_auxdata() SQL function attempts to register each of its arguments
1108** as auxiliary data. If there are no prior registrations of aux data for
1109** that argument (meaning the argument is not a constant or this is its first
1110** call) then the result for that argument is 0. If there is a prior
1111** registration, the result for that argument is 1. The overall result
1112** is the individual argument results separated by spaces.
1113*/
drh17435752007-08-16 04:30:38 +00001114static void free_test_auxdata(void *p) {sqlite3_free(p);}
danielk19773f6b0872004-06-17 05:36:44 +00001115static void test_auxdata(
1116 sqlite3_context *pCtx,
1117 int nArg,
1118 sqlite3_value **argv
1119){
1120 int i;
danielk1977a1644fd2007-08-29 12:31:25 +00001121 char *zRet = contextMalloc(pCtx, nArg*2);
danielk19773f6b0872004-06-17 05:36:44 +00001122 if( !zRet ) return;
danielk1977a1644fd2007-08-29 12:31:25 +00001123 memset(zRet, 0, nArg*2);
danielk19773f6b0872004-06-17 05:36:44 +00001124 for(i=0; i<nArg; i++){
drh2646da72005-12-09 20:02:05 +00001125 char const *z = (char*)sqlite3_value_text(argv[i]);
danielk19773f6b0872004-06-17 05:36:44 +00001126 if( z ){
1127 char *zAux = sqlite3_get_auxdata(pCtx, i);
1128 if( zAux ){
1129 zRet[i*2] = '1';
1130 if( strcmp(zAux, z) ){
danielk1977e0fc5262007-07-26 06:50:05 +00001131 free_test_auxdata((void *)zRet);
danielk19773f6b0872004-06-17 05:36:44 +00001132 sqlite3_result_error(pCtx, "Auxilary data corruption", -1);
1133 return;
1134 }
1135 }else{
1136 zRet[i*2] = '0';
danielk1977a1644fd2007-08-29 12:31:25 +00001137 zAux = contextMalloc(pCtx, strlen(z)+1);
1138 if( zAux ){
1139 strcpy(zAux, z);
1140 sqlite3_set_auxdata(pCtx, i, zAux, free_test_auxdata);
1141 }
danielk19773f6b0872004-06-17 05:36:44 +00001142 }
1143 zRet[i*2+1] = ' ';
1144 }
1145 }
1146 sqlite3_result_text(pCtx, zRet, 2*nArg-1, free_test_auxdata);
1147}
drh0e3d7472004-06-19 17:33:07 +00001148#endif /* SQLITE_TEST */
drh193a6b42002-07-07 16:52:46 +00001149
danielk197701427a62005-01-11 13:02:33 +00001150#ifdef SQLITE_TEST
1151/*
1152** A function to test error reporting from user functions. This function
1153** returns a copy of it's first argument as an error.
1154*/
1155static void test_error(
1156 sqlite3_context *pCtx,
1157 int nArg,
1158 sqlite3_value **argv
1159){
drh2646da72005-12-09 20:02:05 +00001160 sqlite3_result_error(pCtx, (char*)sqlite3_value_text(argv[0]), 0);
danielk197701427a62005-01-11 13:02:33 +00001161}
1162#endif /* SQLITE_TEST */
1163
drh0ac65892002-04-20 14:24:41 +00001164/*
drhd3a149e2002-02-24 17:12:53 +00001165** An instance of the following structure holds the context of a
drhdd5baa92002-02-27 19:50:59 +00001166** sum() or avg() aggregate computation.
1167*/
1168typedef struct SumCtx SumCtx;
1169struct SumCtx {
drh8c08e862006-02-11 17:34:00 +00001170 double rSum; /* Floating point sum */
1171 i64 iSum; /* Integer sum */
1172 i64 cnt; /* Number of elements summed */
1173 u8 overflow; /* True if integer overflow seen */
1174 u8 approx; /* True if non-integer value was input to the sum */
drhdd5baa92002-02-27 19:50:59 +00001175};
1176
1177/*
drha97fdd32006-01-12 22:17:50 +00001178** Routines used to compute the sum, average, and total.
1179**
1180** The SUM() function follows the (broken) SQL standard which means
1181** that it returns NULL if it sums over no inputs. TOTAL returns
1182** 0.0 in that case. In addition, TOTAL always returns a float where
1183** SUM might return an integer if it never encounters a floating point
drhc806d852006-05-11 13:25:39 +00001184** value. TOTAL never fails, but SUM might through an exception if
1185** it overflows an integer.
drhdd5baa92002-02-27 19:50:59 +00001186*/
danielk19770ae8b832004-05-25 12:05:56 +00001187static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){
drhdd5baa92002-02-27 19:50:59 +00001188 SumCtx *p;
drh3d1d95e2005-09-08 10:37:01 +00001189 int type;
drh3f219f42005-09-08 19:45:57 +00001190 assert( argc==1 );
drh4f26d6c2004-05-26 23:25:30 +00001191 p = sqlite3_aggregate_context(context, sizeof(*p));
drh29d72102006-02-09 22:13:41 +00001192 type = sqlite3_value_numeric_type(argv[0]);
drh3d1d95e2005-09-08 10:37:01 +00001193 if( p && type!=SQLITE_NULL ){
drh739105c2002-05-29 23:22:23 +00001194 p->cnt++;
drh29d72102006-02-09 22:13:41 +00001195 if( type==SQLITE_INTEGER ){
drh8c08e862006-02-11 17:34:00 +00001196 i64 v = sqlite3_value_int64(argv[0]);
1197 p->rSum += v;
1198 if( (p->approx|p->overflow)==0 ){
1199 i64 iNewSum = p->iSum + v;
1200 int s1 = p->iSum >> (sizeof(i64)*8-1);
1201 int s2 = v >> (sizeof(i64)*8-1);
1202 int s3 = iNewSum >> (sizeof(i64)*8-1);
1203 p->overflow = (s1&s2&~s3) | (~s1&~s2&s3);
1204 p->iSum = iNewSum;
drh29d72102006-02-09 22:13:41 +00001205 }
1206 }else{
drh8c08e862006-02-11 17:34:00 +00001207 p->rSum += sqlite3_value_double(argv[0]);
drh29d72102006-02-09 22:13:41 +00001208 p->approx = 1;
drh3f219f42005-09-08 19:45:57 +00001209 }
drh739105c2002-05-29 23:22:23 +00001210 }
drhdd5baa92002-02-27 19:50:59 +00001211}
danielk19770ae8b832004-05-25 12:05:56 +00001212static void sumFinalize(sqlite3_context *context){
drhdd5baa92002-02-27 19:50:59 +00001213 SumCtx *p;
drhabfcea22005-09-06 20:36:48 +00001214 p = sqlite3_aggregate_context(context, 0);
drhc2bd9132005-09-08 20:37:43 +00001215 if( p && p->cnt>0 ){
drh8c08e862006-02-11 17:34:00 +00001216 if( p->overflow ){
1217 sqlite3_result_error(context,"integer overflow",-1);
1218 }else if( p->approx ){
1219 sqlite3_result_double(context, p->rSum);
drhc2bd9132005-09-08 20:37:43 +00001220 }else{
drh8c08e862006-02-11 17:34:00 +00001221 sqlite3_result_int64(context, p->iSum);
drhc2bd9132005-09-08 20:37:43 +00001222 }
drh3d1d95e2005-09-08 10:37:01 +00001223 }
drhdd5baa92002-02-27 19:50:59 +00001224}
danielk19770ae8b832004-05-25 12:05:56 +00001225static void avgFinalize(sqlite3_context *context){
drhdd5baa92002-02-27 19:50:59 +00001226 SumCtx *p;
drhabfcea22005-09-06 20:36:48 +00001227 p = sqlite3_aggregate_context(context, 0);
drh739105c2002-05-29 23:22:23 +00001228 if( p && p->cnt>0 ){
drh8c08e862006-02-11 17:34:00 +00001229 sqlite3_result_double(context, p->rSum/(double)p->cnt);
drhdd5baa92002-02-27 19:50:59 +00001230 }
1231}
drha97fdd32006-01-12 22:17:50 +00001232static void totalFinalize(sqlite3_context *context){
1233 SumCtx *p;
1234 p = sqlite3_aggregate_context(context, 0);
drh8c08e862006-02-11 17:34:00 +00001235 sqlite3_result_double(context, p ? p->rSum : 0.0);
drha97fdd32006-01-12 22:17:50 +00001236}
drhdd5baa92002-02-27 19:50:59 +00001237
1238/*
drh0bce8352002-02-28 00:41:10 +00001239** The following structure keeps track of state information for the
1240** count() aggregate function.
1241*/
1242typedef struct CountCtx CountCtx;
1243struct CountCtx {
drhfc6ad392006-02-09 13:38:19 +00001244 i64 n;
drh0bce8352002-02-28 00:41:10 +00001245};
drhdd5baa92002-02-27 19:50:59 +00001246
drh0bce8352002-02-28 00:41:10 +00001247/*
1248** Routines to implement the count() aggregate function.
1249*/
danielk19770ae8b832004-05-25 12:05:56 +00001250static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){
drh0bce8352002-02-28 00:41:10 +00001251 CountCtx *p;
drh4f26d6c2004-05-26 23:25:30 +00001252 p = sqlite3_aggregate_context(context, sizeof(*p));
drh9c054832004-05-31 18:51:57 +00001253 if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){
drh0bce8352002-02-28 00:41:10 +00001254 p->n++;
1255 }
1256}
danielk19770ae8b832004-05-25 12:05:56 +00001257static void countFinalize(sqlite3_context *context){
drh0bce8352002-02-28 00:41:10 +00001258 CountCtx *p;
drhabfcea22005-09-06 20:36:48 +00001259 p = sqlite3_aggregate_context(context, 0);
drhfc6ad392006-02-09 13:38:19 +00001260 sqlite3_result_int64(context, p ? p->n : 0);
drh0bce8352002-02-28 00:41:10 +00001261}
1262
1263/*
drh0bce8352002-02-28 00:41:10 +00001264** Routines to implement min() and max() aggregate functions.
1265*/
danielk19770ae8b832004-05-25 12:05:56 +00001266static void minmaxStep(sqlite3_context *context, int argc, sqlite3_value **argv){
danielk197788208052004-05-25 01:13:20 +00001267 Mem *pArg = (Mem *)argv[0];
drh9eb516c2004-07-18 20:52:32 +00001268 Mem *pBest;
1269
1270 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
1271 pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));
danielk19773aeab9e2004-06-24 00:20:04 +00001272 if( !pBest ) return;
drh268380c2004-02-25 13:47:31 +00001273
danielk197788208052004-05-25 01:13:20 +00001274 if( pBest->flags ){
drh9eb516c2004-07-18 20:52:32 +00001275 int max;
1276 int cmp;
danielk1977dc1bdc42004-06-11 10:51:27 +00001277 CollSeq *pColl = sqlite3GetFuncCollSeq(context);
danielk19777e18c252004-05-25 11:47:24 +00001278 /* This step function is used for both the min() and max() aggregates,
1279 ** the only difference between the two being that the sense of the
1280 ** comparison is inverted. For the max() aggregate, the
1281 ** sqlite3_user_data() function returns (void *)-1. For min() it
1282 ** returns (void *)db, where db is the sqlite3* database pointer.
1283 ** Therefore the next statement sets variable 'max' to 1 for the max()
1284 ** aggregate, or 0 for min().
1285 */
drh309b3382007-03-17 17:52:42 +00001286 max = sqlite3_user_data(context)!=0;
danielk1977dc1bdc42004-06-11 10:51:27 +00001287 cmp = sqlite3MemCompare(pBest, pArg, pColl);
danielk197788208052004-05-25 01:13:20 +00001288 if( (max && cmp<0) || (!max && cmp>0) ){
drhb21c8cd2007-08-21 19:33:56 +00001289 sqlite3VdbeMemCopy(pBest, pArg);
danielk197788208052004-05-25 01:13:20 +00001290 }
drh268380c2004-02-25 13:47:31 +00001291 }else{
drhb21c8cd2007-08-21 19:33:56 +00001292 sqlite3VdbeMemCopy(pBest, pArg);
drh0bce8352002-02-28 00:41:10 +00001293 }
1294}
danielk19770ae8b832004-05-25 12:05:56 +00001295static void minMaxFinalize(sqlite3_context *context){
danielk197788208052004-05-25 01:13:20 +00001296 sqlite3_value *pRes;
drhabfcea22005-09-06 20:36:48 +00001297 pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0);
1298 if( pRes ){
1299 if( pRes->flags ){
1300 sqlite3_result_value(context, pRes);
1301 }
1302 sqlite3VdbeMemRelease(pRes);
drh0bce8352002-02-28 00:41:10 +00001303 }
1304}
drhdd5baa92002-02-27 19:50:59 +00001305
drh4e5ffc52004-08-31 00:52:37 +00001306
drhd3a149e2002-02-24 17:12:53 +00001307/*
drha2ed5602002-02-26 23:55:31 +00001308** This function registered all of the above C functions as SQL
1309** functions. This should be the only routine in this file with
1310** external linkage.
drhdc04c582002-02-24 01:55:15 +00001311*/
drh9bb575f2004-09-06 17:24:11 +00001312void sqlite3RegisterBuiltinFunctions(sqlite3 *db){
drh57196282004-10-06 15:41:16 +00001313 static const struct {
drh0bce8352002-02-28 00:41:10 +00001314 char *zName;
drh268380c2004-02-25 13:47:31 +00001315 signed char nArg;
drh309b3382007-03-17 17:52:42 +00001316 u8 argType; /* ff: db 1: 0, 2: 1, 3: 2,... N: N-1. */
danielk1977f4618892004-06-28 13:09:11 +00001317 u8 eTextRep; /* 1: UTF-16. 0: UTF-8 */
danielk1977dc1bdc42004-06-11 10:51:27 +00001318 u8 needCollSeq;
danielk19770ae8b832004-05-25 12:05:56 +00001319 void (*xFunc)(sqlite3_context*,int,sqlite3_value **);
drh0bce8352002-02-28 00:41:10 +00001320 } aFuncs[] = {
danielk1977f4618892004-06-28 13:09:11 +00001321 { "min", -1, 0, SQLITE_UTF8, 1, minmaxFunc },
1322 { "min", 0, 0, SQLITE_UTF8, 1, 0 },
drh309b3382007-03-17 17:52:42 +00001323 { "max", -1, 1, SQLITE_UTF8, 1, minmaxFunc },
1324 { "max", 0, 1, SQLITE_UTF8, 1, 0 },
danielk1977f4618892004-06-28 13:09:11 +00001325 { "typeof", 1, 0, SQLITE_UTF8, 0, typeofFunc },
1326 { "length", 1, 0, SQLITE_UTF8, 0, lengthFunc },
1327 { "substr", 3, 0, SQLITE_UTF8, 0, substrFunc },
danielk1977f4618892004-06-28 13:09:11 +00001328 { "abs", 1, 0, SQLITE_UTF8, 0, absFunc },
1329 { "round", 1, 0, SQLITE_UTF8, 0, roundFunc },
1330 { "round", 2, 0, SQLITE_UTF8, 0, roundFunc },
1331 { "upper", 1, 0, SQLITE_UTF8, 0, upperFunc },
1332 { "lower", 1, 0, SQLITE_UTF8, 0, lowerFunc },
1333 { "coalesce", -1, 0, SQLITE_UTF8, 0, ifnullFunc },
1334 { "coalesce", 0, 0, SQLITE_UTF8, 0, 0 },
1335 { "coalesce", 1, 0, SQLITE_UTF8, 0, 0 },
drh137c7282007-01-29 17:58:28 +00001336 { "hex", 1, 0, SQLITE_UTF8, 0, hexFunc },
danielk1977f4618892004-06-28 13:09:11 +00001337 { "ifnull", 2, 0, SQLITE_UTF8, 1, ifnullFunc },
1338 { "random", -1, 0, SQLITE_UTF8, 0, randomFunc },
drh137c7282007-01-29 17:58:28 +00001339 { "randomblob", 1, 0, SQLITE_UTF8, 0, randomBlob },
drh94a98362004-09-13 13:13:18 +00001340 { "nullif", 2, 0, SQLITE_UTF8, 1, nullifFunc },
danielk1977f4618892004-06-28 13:09:11 +00001341 { "sqlite_version", 0, 0, SQLITE_UTF8, 0, versionFunc},
1342 { "quote", 1, 0, SQLITE_UTF8, 0, quoteFunc },
drh309b3382007-03-17 17:52:42 +00001343 { "last_insert_rowid", 0, 0xff, SQLITE_UTF8, 0, last_insert_rowid },
1344 { "changes", 0, 0xff, SQLITE_UTF8, 0, changes },
1345 { "total_changes", 0, 0xff, SQLITE_UTF8, 0, total_changes },
drh26b6d902007-03-17 13:27:54 +00001346 { "replace", 3, 0, SQLITE_UTF8, 0, replaceFunc },
drh309b3382007-03-17 17:52:42 +00001347 { "ltrim", 1, 1, SQLITE_UTF8, 0, trimFunc },
1348 { "ltrim", 2, 1, SQLITE_UTF8, 0, trimFunc },
1349 { "rtrim", 1, 2, SQLITE_UTF8, 0, trimFunc },
1350 { "rtrim", 2, 2, SQLITE_UTF8, 0, trimFunc },
1351 { "trim", 1, 3, SQLITE_UTF8, 0, trimFunc },
1352 { "trim", 2, 3, SQLITE_UTF8, 0, trimFunc },
drh8cff3822007-05-02 02:08:28 +00001353 { "zeroblob", 1, 0, SQLITE_UTF8, 0, zeroblobFunc },
drhd24cc422003-03-27 12:51:24 +00001354#ifdef SQLITE_SOUNDEX
drh309b3382007-03-17 17:52:42 +00001355 { "soundex", 1, 0, SQLITE_UTF8, 0, soundexFunc},
drhd24cc422003-03-27 12:51:24 +00001356#endif
drhfdb83b22006-06-17 14:12:47 +00001357#ifndef SQLITE_OMIT_LOAD_EXTENSION
drh309b3382007-03-17 17:52:42 +00001358 { "load_extension", 1, 0xff, SQLITE_UTF8, 0, loadExt },
1359 { "load_extension", 2, 0xff, SQLITE_UTF8, 0, loadExt },
drhfdb83b22006-06-17 14:12:47 +00001360#endif
drh193a6b42002-07-07 16:52:46 +00001361#ifdef SQLITE_TEST
drh309b3382007-03-17 17:52:42 +00001362 { "randstr", 2, 0, SQLITE_UTF8, 0, randStr },
1363 { "test_destructor", 1, 0xff, SQLITE_UTF8, 0, test_destructor},
1364 { "test_destructor_count", 0, 0, SQLITE_UTF8, 0, test_destructor_count},
1365 { "test_auxdata", -1, 0, SQLITE_UTF8, 0, test_auxdata},
1366 { "test_error", 1, 0, SQLITE_UTF8, 0, test_error},
drh193a6b42002-07-07 16:52:46 +00001367#endif
drh0bce8352002-02-28 00:41:10 +00001368 };
drh57196282004-10-06 15:41:16 +00001369 static const struct {
drh0bce8352002-02-28 00:41:10 +00001370 char *zName;
drh268380c2004-02-25 13:47:31 +00001371 signed char nArg;
drh268380c2004-02-25 13:47:31 +00001372 u8 argType;
danielk1977dc1bdc42004-06-11 10:51:27 +00001373 u8 needCollSeq;
danielk19770ae8b832004-05-25 12:05:56 +00001374 void (*xStep)(sqlite3_context*,int,sqlite3_value**);
1375 void (*xFinalize)(sqlite3_context*);
drh0bce8352002-02-28 00:41:10 +00001376 } aAggs[] = {
danielk1977dc1bdc42004-06-11 10:51:27 +00001377 { "min", 1, 0, 1, minmaxStep, minMaxFinalize },
drh309b3382007-03-17 17:52:42 +00001378 { "max", 1, 1, 1, minmaxStep, minMaxFinalize },
danielk1977dc1bdc42004-06-11 10:51:27 +00001379 { "sum", 1, 0, 0, sumStep, sumFinalize },
drha97fdd32006-01-12 22:17:50 +00001380 { "total", 1, 0, 0, sumStep, totalFinalize },
danielk1977dc1bdc42004-06-11 10:51:27 +00001381 { "avg", 1, 0, 0, sumStep, avgFinalize },
1382 { "count", 0, 0, 0, countStep, countFinalize },
1383 { "count", 1, 0, 0, countStep, countFinalize },
drh0bce8352002-02-28 00:41:10 +00001384 };
1385 int i;
1386
1387 for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
drh309b3382007-03-17 17:52:42 +00001388 void *pArg;
1389 u8 argType = aFuncs[i].argType;
1390 if( argType==0xff ){
1391 pArg = db;
1392 }else{
1393 pArg = (void*)(int)argType;
danielk1977c572ef72004-05-27 09:28:41 +00001394 }
danielk1977771151b2006-01-17 13:21:40 +00001395 sqlite3CreateFunc(db, aFuncs[i].zName, aFuncs[i].nArg,
danielk1977f9d64d22004-06-19 08:18:07 +00001396 aFuncs[i].eTextRep, pArg, aFuncs[i].xFunc, 0, 0);
danielk1977dc1bdc42004-06-11 10:51:27 +00001397 if( aFuncs[i].needCollSeq ){
1398 FuncDef *pFunc = sqlite3FindFunction(db, aFuncs[i].zName,
1399 strlen(aFuncs[i].zName), aFuncs[i].nArg, aFuncs[i].eTextRep, 0);
1400 if( pFunc && aFuncs[i].needCollSeq ){
1401 pFunc->needCollSeq = 1;
1402 }
1403 }
drh0bce8352002-02-28 00:41:10 +00001404 }
drh1f01ec12005-02-15 21:36:18 +00001405#ifndef SQLITE_OMIT_ALTERTABLE
1406 sqlite3AlterFunctions(db);
1407#endif
drh198bf392006-01-06 21:52:49 +00001408#ifndef SQLITE_OMIT_PARSER
danielk1977f744bb52005-12-06 17:19:11 +00001409 sqlite3AttachFunctions(db);
drh198bf392006-01-06 21:52:49 +00001410#endif
drh0bce8352002-02-28 00:41:10 +00001411 for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
drh309b3382007-03-17 17:52:42 +00001412 void *pArg = (void*)(int)aAggs[i].argType;
danielk1977771151b2006-01-17 13:21:40 +00001413 sqlite3CreateFunc(db, aAggs[i].zName, aAggs[i].nArg, SQLITE_UTF8,
danielk1977f9d64d22004-06-19 08:18:07 +00001414 pArg, 0, aAggs[i].xStep, aAggs[i].xFinalize);
danielk1977dc1bdc42004-06-11 10:51:27 +00001415 if( aAggs[i].needCollSeq ){
1416 FuncDef *pFunc = sqlite3FindFunction( db, aAggs[i].zName,
danielk1977d8123362004-06-12 09:25:12 +00001417 strlen(aAggs[i].zName), aAggs[i].nArg, SQLITE_UTF8, 0);
danielk1977dc1bdc42004-06-11 10:51:27 +00001418 if( pFunc && aAggs[i].needCollSeq ){
1419 pFunc->needCollSeq = 1;
1420 }
1421 }
drh268380c2004-02-25 13:47:31 +00001422 }
danielk19774adee202004-05-08 08:23:19 +00001423 sqlite3RegisterDateTimeFunctions(db);
danielk19771e536952007-08-16 10:09:01 +00001424 if( !db->mallocFailed ){
danielk1977832a58a2007-06-22 15:21:15 +00001425 int rc = sqlite3_overload_function(db, "MATCH", 2);
1426 assert( rc==SQLITE_NOMEM || rc==SQLITE_OK );
1427 if( rc==SQLITE_NOMEM ){
danielk19771e536952007-08-16 10:09:01 +00001428 db->mallocFailed = 1;
danielk1977832a58a2007-06-22 15:21:15 +00001429 }
1430 }
danielk1977fd9e1f32005-05-22 10:44:34 +00001431#ifdef SQLITE_SSE
drh37527852006-03-16 16:19:56 +00001432 (void)sqlite3SseFunctions(db);
danielk1977fd9e1f32005-05-22 10:44:34 +00001433#endif
drh55ef4d92005-08-14 01:20:37 +00001434#ifdef SQLITE_CASE_SENSITIVE_LIKE
1435 sqlite3RegisterLikeFunctions(db, 1);
1436#else
1437 sqlite3RegisterLikeFunctions(db, 0);
1438#endif
1439}
1440
1441/*
1442** Set the LIKEOPT flag on the 2-argument function with the given name.
1443*/
drhd64fe2f2005-08-28 17:00:23 +00001444static void setLikeOptFlag(sqlite3 *db, const char *zName, int flagVal){
drh55ef4d92005-08-14 01:20:37 +00001445 FuncDef *pDef;
1446 pDef = sqlite3FindFunction(db, zName, strlen(zName), 2, SQLITE_UTF8, 0);
1447 if( pDef ){
drhd64fe2f2005-08-28 17:00:23 +00001448 pDef->flags = flagVal;
drh55ef4d92005-08-14 01:20:37 +00001449 }
1450}
1451
1452/*
1453** Register the built-in LIKE and GLOB functions. The caseSensitive
1454** parameter determines whether or not the LIKE operator is case
1455** sensitive. GLOB is always case sensitive.
1456*/
1457void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){
1458 struct compareInfo *pInfo;
1459 if( caseSensitive ){
1460 pInfo = (struct compareInfo*)&likeInfoAlt;
1461 }else{
1462 pInfo = (struct compareInfo*)&likeInfoNorm;
1463 }
danielk1977771151b2006-01-17 13:21:40 +00001464 sqlite3CreateFunc(db, "like", 2, SQLITE_UTF8, pInfo, likeFunc, 0, 0);
1465 sqlite3CreateFunc(db, "like", 3, SQLITE_UTF8, pInfo, likeFunc, 0, 0);
1466 sqlite3CreateFunc(db, "glob", 2, SQLITE_UTF8,
drh55ef4d92005-08-14 01:20:37 +00001467 (struct compareInfo*)&globInfo, likeFunc, 0,0);
drhd64fe2f2005-08-28 17:00:23 +00001468 setLikeOptFlag(db, "glob", SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE);
1469 setLikeOptFlag(db, "like",
1470 caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) : SQLITE_FUNC_LIKE);
drh55ef4d92005-08-14 01:20:37 +00001471}
1472
1473/*
1474** pExpr points to an expression which implements a function. If
1475** it is appropriate to apply the LIKE optimization to that function
1476** then set aWc[0] through aWc[2] to the wildcard characters and
1477** return TRUE. If the function is not a LIKE-style function then
1478** return FALSE.
1479*/
drhd64fe2f2005-08-28 17:00:23 +00001480int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){
drh55ef4d92005-08-14 01:20:37 +00001481 FuncDef *pDef;
danielk1977cdbd8ef2007-05-12 06:11:12 +00001482 if( pExpr->op!=TK_FUNCTION || !pExpr->pList ){
drh55ef4d92005-08-14 01:20:37 +00001483 return 0;
1484 }
1485 if( pExpr->pList->nExpr!=2 ){
1486 return 0;
1487 }
drh2646da72005-12-09 20:02:05 +00001488 pDef = sqlite3FindFunction(db, (char*)pExpr->token.z, pExpr->token.n, 2,
drh55ef4d92005-08-14 01:20:37 +00001489 SQLITE_UTF8, 0);
drhd64fe2f2005-08-28 17:00:23 +00001490 if( pDef==0 || (pDef->flags & SQLITE_FUNC_LIKE)==0 ){
drh55ef4d92005-08-14 01:20:37 +00001491 return 0;
1492 }
1493
1494 /* The memcpy() statement assumes that the wildcard characters are
1495 ** the first three statements in the compareInfo structure. The
1496 ** asserts() that follow verify that assumption
1497 */
1498 memcpy(aWc, pDef->pUserData, 3);
1499 assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );
1500 assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne );
1501 assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet );
drhd64fe2f2005-08-28 17:00:23 +00001502 *pIsNocase = (pDef->flags & SQLITE_FUNC_CASE)==0;
drh55ef4d92005-08-14 01:20:37 +00001503 return 1;
drhdc04c582002-02-24 01:55:15 +00001504}