blob: 85ec68b73f133cbf11429e9fdc20884dd084c5e1 [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**
drh769e97e2009-04-01 16:33:37 +000019** $Id: func.c,v 1.226 2009/04/01 16:33:38 drh Exp $
drhdc04c582002-02-24 01:55:15 +000020*/
drhb659e9b2005-01-28 01:29:08 +000021#include "sqliteInt.h"
drhd3a149e2002-02-24 17:12:53 +000022#include <stdlib.h>
drh0bce8352002-02-28 00:41:10 +000023#include <assert.h>
danielk197788208052004-05-25 01:13:20 +000024#include "vdbeInt.h"
drh0bce8352002-02-28 00:41:10 +000025
drh55ef4d92005-08-14 01:20:37 +000026/*
27** Return the collating function associated with a function.
28*/
danielk1977dc1bdc42004-06-11 10:51:27 +000029static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){
30 return context->pColl;
31}
32
drh0bce8352002-02-28 00:41:10 +000033/*
34** Implementation of the non-aggregate min() and max() functions
35*/
drhf9b596e2004-05-26 16:54:42 +000036static void minmaxFunc(
37 sqlite3_context *context,
38 int argc,
39 sqlite3_value **argv
40){
drh0bce8352002-02-28 00:41:10 +000041 int i;
drh268380c2004-02-25 13:47:31 +000042 int mask; /* 0 for min() or 0xffffffff for max() */
drhf9b596e2004-05-26 16:54:42 +000043 int iBest;
danielk1977dc1bdc42004-06-11 10:51:27 +000044 CollSeq *pColl;
drh0bce8352002-02-28 00:41:10 +000045
drh65595cd2009-02-02 16:32:55 +000046 assert( argc>1 );
drhc44af712004-09-02 15:53:56 +000047 mask = sqlite3_user_data(context)==0 ? 0 : -1;
danielk1977dc1bdc42004-06-11 10:51:27 +000048 pColl = sqlite3GetFuncCollSeq(context);
49 assert( pColl );
danielk1977c572ef72004-05-27 09:28:41 +000050 assert( mask==-1 || mask==0 );
drhf9b596e2004-05-26 16:54:42 +000051 iBest = 0;
drh9c054832004-05-31 18:51:57 +000052 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
drhf9b596e2004-05-26 16:54:42 +000053 for(i=1; i<argc; i++){
drh9c054832004-05-31 18:51:57 +000054 if( sqlite3_value_type(argv[i])==SQLITE_NULL ) return;
danielk1977dc1bdc42004-06-11 10:51:27 +000055 if( (sqlite3MemCompare(argv[iBest], argv[i], pColl)^mask)>=0 ){
drh65595cd2009-02-02 16:32:55 +000056 testcase( 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_INTEGER: z = "integer"; break;
75 case SQLITE_TEXT: z = "text"; break;
76 case SQLITE_FLOAT: z = "real"; break;
77 case SQLITE_BLOB: z = "blob"; break;
drh65595cd2009-02-02 16:32:55 +000078 default: z = "null"; 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 );
danielk1977f3d3c272008-11-19 16:52:44 +000095 UNUSED_PARAMETER(argc);
drhf9b596e2004-05-26 16:54:42 +000096 switch( sqlite3_value_type(argv[0]) ){
drh9c054832004-05-31 18:51:57 +000097 case SQLITE_BLOB:
98 case SQLITE_INTEGER:
99 case SQLITE_FLOAT: {
drhf4479502004-05-27 03:12:53 +0000100 sqlite3_result_int(context, sqlite3_value_bytes(argv[0]));
drhf9b596e2004-05-26 16:54:42 +0000101 break;
102 }
drh9c054832004-05-31 18:51:57 +0000103 case SQLITE_TEXT: {
drh2646da72005-12-09 20:02:05 +0000104 const unsigned char *z = sqlite3_value_text(argv[0]);
drh7a521cf2007-04-25 18:23:52 +0000105 if( z==0 ) return;
drh4a919112007-05-15 11:55:09 +0000106 len = 0;
107 while( *z ){
108 len++;
109 SQLITE_SKIP_UTF8(z);
110 }
drhf4479502004-05-27 03:12:53 +0000111 sqlite3_result_int(context, len);
drhf9b596e2004-05-26 16:54:42 +0000112 break;
113 }
114 default: {
115 sqlite3_result_null(context);
116 break;
117 }
118 }
drh0bce8352002-02-28 00:41:10 +0000119}
120
121/*
122** Implementation of the abs() function
123*/
danielk19770ae8b832004-05-25 12:05:56 +0000124static void absFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
drh0bce8352002-02-28 00:41:10 +0000125 assert( argc==1 );
danielk1977f3d3c272008-11-19 16:52:44 +0000126 UNUSED_PARAMETER(argc);
drhf9b596e2004-05-26 16:54:42 +0000127 switch( sqlite3_value_type(argv[0]) ){
drh9c054832004-05-31 18:51:57 +0000128 case SQLITE_INTEGER: {
danielk1977f93bbbe2004-05-27 10:30:52 +0000129 i64 iVal = sqlite3_value_int64(argv[0]);
drh52fc8492006-02-23 21:43:55 +0000130 if( iVal<0 ){
131 if( (iVal<<1)==0 ){
132 sqlite3_result_error(context, "integer overflow", -1);
133 return;
134 }
135 iVal = -iVal;
136 }
danielk1977f93bbbe2004-05-27 10:30:52 +0000137 sqlite3_result_int64(context, iVal);
drhf9b596e2004-05-26 16:54:42 +0000138 break;
139 }
drh9c054832004-05-31 18:51:57 +0000140 case SQLITE_NULL: {
drhf9b596e2004-05-26 16:54:42 +0000141 sqlite3_result_null(context);
142 break;
143 }
144 default: {
danielk1977f93bbbe2004-05-27 10:30:52 +0000145 double rVal = sqlite3_value_double(argv[0]);
drh52fc8492006-02-23 21:43:55 +0000146 if( rVal<0 ) rVal = -rVal;
danielk1977f93bbbe2004-05-27 10:30:52 +0000147 sqlite3_result_double(context, rVal);
drhf9b596e2004-05-26 16:54:42 +0000148 break;
149 }
150 }
drh0bce8352002-02-28 00:41:10 +0000151}
152
153/*
drhf764e6f2007-05-15 01:13:47 +0000154** Implementation of the substr() function.
155**
156** substr(x,p1,p2) returns p2 characters of x[] beginning with p1.
157** p1 is 1-indexed. So substr(x,1,1) returns the first character
158** of x. If x is text, then we actually count UTF-8 characters.
159** If x is a blob, then we count bytes.
160**
161** If p1 is negative, then we begin abs(p1) from the end of x[].
drh0bce8352002-02-28 00:41:10 +0000162*/
drhf9b596e2004-05-26 16:54:42 +0000163static void substrFunc(
164 sqlite3_context *context,
165 int argc,
166 sqlite3_value **argv
167){
drh2646da72005-12-09 20:02:05 +0000168 const unsigned char *z;
169 const unsigned char *z2;
drh023ae032007-05-08 12:12:16 +0000170 int len;
drhf764e6f2007-05-15 01:13:47 +0000171 int p0type;
drh023ae032007-05-08 12:12:16 +0000172 i64 p1, p2;
drh65595cd2009-02-02 16:32:55 +0000173 int negP2 = 0;
drhf9b596e2004-05-26 16:54:42 +0000174
drh64f31512007-10-12 19:11:55 +0000175 assert( argc==3 || argc==2 );
drh8198d252009-02-01 19:42:37 +0000176 if( sqlite3_value_type(argv[1])==SQLITE_NULL
177 || (argc==3 && sqlite3_value_type(argv[2])==SQLITE_NULL)
178 ){
179 return;
180 }
drhf764e6f2007-05-15 01:13:47 +0000181 p0type = sqlite3_value_type(argv[0]);
182 if( p0type==SQLITE_BLOB ){
183 len = sqlite3_value_bytes(argv[0]);
184 z = sqlite3_value_blob(argv[0]);
185 if( z==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000186 assert( len==sqlite3_value_bytes(argv[0]) );
drhf764e6f2007-05-15 01:13:47 +0000187 }else{
188 z = sqlite3_value_text(argv[0]);
189 if( z==0 ) return;
drh4a919112007-05-15 11:55:09 +0000190 len = 0;
191 for(z2=z; *z2; len++){
192 SQLITE_SKIP_UTF8(z2);
193 }
drhf764e6f2007-05-15 01:13:47 +0000194 }
danielk197751ad0ec2004-05-24 12:39:02 +0000195 p1 = sqlite3_value_int(argv[1]);
drh64f31512007-10-12 19:11:55 +0000196 if( argc==3 ){
197 p2 = sqlite3_value_int(argv[2]);
drh65595cd2009-02-02 16:32:55 +0000198 if( p2<0 ){
199 p2 = -p2;
200 negP2 = 1;
201 }
drh64f31512007-10-12 19:11:55 +0000202 }else{
drhbb4957f2008-03-20 14:03:29 +0000203 p2 = sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_LENGTH];
drh64f31512007-10-12 19:11:55 +0000204 }
drh0bce8352002-02-28 00:41:10 +0000205 if( p1<0 ){
drh89425d52002-02-28 03:04:48 +0000206 p1 += len;
drh653bc752002-02-28 03:31:10 +0000207 if( p1<0 ){
208 p2 += p1;
drh65595cd2009-02-02 16:32:55 +0000209 if( p2<0 ) p2 = 0;
drh653bc752002-02-28 03:31:10 +0000210 p1 = 0;
211 }
drh0bce8352002-02-28 00:41:10 +0000212 }else if( p1>0 ){
213 p1--;
drh65595cd2009-02-02 16:32:55 +0000214 }else if( p2>0 ){
215 p2--;
drh0bce8352002-02-28 00:41:10 +0000216 }
drh65595cd2009-02-02 16:32:55 +0000217 if( negP2 ){
218 p1 -= p2;
drh4e79c592009-02-01 19:23:32 +0000219 if( p1<0 ){
220 p2 += p1;
221 p1 = 0;
222 }
223 }
drh65595cd2009-02-02 16:32:55 +0000224 assert( p1>=0 && p2>=0 );
drh0bce8352002-02-28 00:41:10 +0000225 if( p1+p2>len ){
226 p2 = len-p1;
drh478000c2009-02-03 13:10:54 +0000227 if( p2<0 ) p2 = 0;
drh0bce8352002-02-28 00:41:10 +0000228 }
drhf764e6f2007-05-15 01:13:47 +0000229 if( p0type!=SQLITE_BLOB ){
drh4a919112007-05-15 11:55:09 +0000230 while( *z && p1 ){
231 SQLITE_SKIP_UTF8(z);
232 p1--;
drhf764e6f2007-05-15 01:13:47 +0000233 }
drh4a919112007-05-15 11:55:09 +0000234 for(z2=z; *z2 && p2; p2--){
235 SQLITE_SKIP_UTF8(z2);
drhf764e6f2007-05-15 01:13:47 +0000236 }
drh1bd10f82008-12-10 21:19:56 +0000237 sqlite3_result_text(context, (char*)z, (int)(z2-z), SQLITE_TRANSIENT);
drhf764e6f2007-05-15 01:13:47 +0000238 }else{
drh1bd10f82008-12-10 21:19:56 +0000239 sqlite3_result_blob(context, (char*)&z[p1], (int)p2, SQLITE_TRANSIENT);
drh0bce8352002-02-28 00:41:10 +0000240 }
drh0bce8352002-02-28 00:41:10 +0000241}
242
243/*
244** Implementation of the round() function
245*/
shanefbd60f82009-02-04 03:59:25 +0000246#ifndef SQLITE_OMIT_FLOATING_POINT
danielk19770ae8b832004-05-25 12:05:56 +0000247static void roundFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
danielk197751ad0ec2004-05-24 12:39:02 +0000248 int n = 0;
drh0bce8352002-02-28 00:41:10 +0000249 double r;
drh592ac8c2005-08-13 03:07:47 +0000250 char zBuf[500]; /* larger than the %f representation of the largest double */
drh0bce8352002-02-28 00:41:10 +0000251 assert( argc==1 || argc==2 );
danielk197751ad0ec2004-05-24 12:39:02 +0000252 if( argc==2 ){
drh9c054832004-05-31 18:51:57 +0000253 if( SQLITE_NULL==sqlite3_value_type(argv[1]) ) return;
danielk197751ad0ec2004-05-24 12:39:02 +0000254 n = sqlite3_value_int(argv[1]);
255 if( n>30 ) n = 30;
256 if( n<0 ) n = 0;
257 }
drhd589a922006-03-02 03:02:48 +0000258 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
drh4f26d6c2004-05-26 23:25:30 +0000259 r = sqlite3_value_double(argv[0]);
drhe866fcb2005-07-09 02:38:06 +0000260 sqlite3_snprintf(sizeof(zBuf),zBuf,"%.*f",n,r);
drh502b9622006-04-07 13:26:42 +0000261 sqlite3AtoF(zBuf, &r);
262 sqlite3_result_double(context, r);
drh0bce8352002-02-28 00:41:10 +0000263}
shanefbd60f82009-02-04 03:59:25 +0000264#endif
drhdc04c582002-02-24 01:55:15 +0000265
danielk197726783a52007-08-29 14:06:22 +0000266/*
267** Allocate nByte bytes of space using sqlite3_malloc(). If the
268** allocation fails, call sqlite3_result_error_nomem() to notify
269** the database handle that malloc() has failed.
270*/
drhb1a6c3c2008-03-20 16:30:17 +0000271static void *contextMalloc(sqlite3_context *context, i64 nByte){
drhbb4957f2008-03-20 14:03:29 +0000272 char *z;
273 if( nByte>sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_LENGTH] ){
274 sqlite3_result_error_toobig(context);
275 z = 0;
276 }else{
drh1bd10f82008-12-10 21:19:56 +0000277 z = sqlite3Malloc((int)nByte);
drhbb4957f2008-03-20 14:03:29 +0000278 if( !z && nByte>0 ){
279 sqlite3_result_error_nomem(context);
280 }
danielk1977a1644fd2007-08-29 12:31:25 +0000281 }
282 return z;
283}
284
drhdc04c582002-02-24 01:55:15 +0000285/*
286** Implementation of the upper() and lower() SQL functions.
287*/
danielk19770ae8b832004-05-25 12:05:56 +0000288static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
drh7a521cf2007-04-25 18:23:52 +0000289 char *z1;
290 const char *z2;
drh9310ef22007-04-27 17:16:20 +0000291 int i, n;
drh1d34fde2009-02-03 15:50:33 +0000292 UNUSED_PARAMETER(argc);
drh7a521cf2007-04-25 18:23:52 +0000293 z2 = (char*)sqlite3_value_text(argv[0]);
drh1f0feef2007-05-15 13:27:07 +0000294 n = sqlite3_value_bytes(argv[0]);
295 /* Verify that the call to _bytes() does not invalidate the _text() pointer */
296 assert( z2==(char*)sqlite3_value_text(argv[0]) );
drh7a521cf2007-04-25 18:23:52 +0000297 if( z2 ){
drhb1a6c3c2008-03-20 16:30:17 +0000298 z1 = contextMalloc(context, ((i64)n)+1);
drh7a521cf2007-04-25 18:23:52 +0000299 if( z1 ){
drh5bb3eb92007-05-04 13:15:55 +0000300 memcpy(z1, z2, n+1);
drh7a521cf2007-04-25 18:23:52 +0000301 for(i=0; z1[i]; i++){
danielk197778ca0e72009-01-20 16:53:39 +0000302 z1[i] = (char)sqlite3Toupper(z1[i]);
drh7a521cf2007-04-25 18:23:52 +0000303 }
304 sqlite3_result_text(context, z1, -1, sqlite3_free);
305 }
drhdc04c582002-02-24 01:55:15 +0000306 }
307}
danielk19770ae8b832004-05-25 12:05:56 +0000308static void lowerFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
danielk197778ca0e72009-01-20 16:53:39 +0000309 u8 *z1;
drh7a521cf2007-04-25 18:23:52 +0000310 const char *z2;
drh9310ef22007-04-27 17:16:20 +0000311 int i, n;
drh1d34fde2009-02-03 15:50:33 +0000312 UNUSED_PARAMETER(argc);
drh7a521cf2007-04-25 18:23:52 +0000313 z2 = (char*)sqlite3_value_text(argv[0]);
drh1f0feef2007-05-15 13:27:07 +0000314 n = sqlite3_value_bytes(argv[0]);
315 /* Verify that the call to _bytes() does not invalidate the _text() pointer */
316 assert( z2==(char*)sqlite3_value_text(argv[0]) );
drh7a521cf2007-04-25 18:23:52 +0000317 if( z2 ){
drhb1a6c3c2008-03-20 16:30:17 +0000318 z1 = contextMalloc(context, ((i64)n)+1);
drh7a521cf2007-04-25 18:23:52 +0000319 if( z1 ){
drh5bb3eb92007-05-04 13:15:55 +0000320 memcpy(z1, z2, n+1);
drh7a521cf2007-04-25 18:23:52 +0000321 for(i=0; z1[i]; i++){
danielk197778ca0e72009-01-20 16:53:39 +0000322 z1[i] = sqlite3Tolower(z1[i]);
drh7a521cf2007-04-25 18:23:52 +0000323 }
danielk197778ca0e72009-01-20 16:53:39 +0000324 sqlite3_result_text(context, (char *)z1, -1, sqlite3_free);
drh7a521cf2007-04-25 18:23:52 +0000325 }
drhdc04c582002-02-24 01:55:15 +0000326 }
327}
328
329/*
drhfbc99082002-02-28 03:14:18 +0000330** Implementation of the IFNULL(), NVL(), and COALESCE() functions.
jplyonb6c9e6e2004-01-19 04:53:24 +0000331** All three do the same thing. They return the first non-NULL
332** argument.
drh3212e182002-02-28 00:46:26 +0000333*/
drhf9b596e2004-05-26 16:54:42 +0000334static void ifnullFunc(
335 sqlite3_context *context,
336 int argc,
337 sqlite3_value **argv
338){
drhfbc99082002-02-28 03:14:18 +0000339 int i;
340 for(i=0; i<argc; i++){
drh9c054832004-05-31 18:51:57 +0000341 if( SQLITE_NULL!=sqlite3_value_type(argv[i]) ){
drhf4479502004-05-27 03:12:53 +0000342 sqlite3_result_value(context, argv[i]);
drhfbc99082002-02-28 03:14:18 +0000343 break;
344 }
345 }
drh3212e182002-02-28 00:46:26 +0000346}
347
348/*
drhf9ffac92002-03-02 19:00:31 +0000349** Implementation of random(). Return a random integer.
350*/
drhf9b596e2004-05-26 16:54:42 +0000351static void randomFunc(
352 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +0000353 int NotUsed,
354 sqlite3_value **NotUsed2
drhf9b596e2004-05-26 16:54:42 +0000355){
drh52fc8492006-02-23 21:43:55 +0000356 sqlite_int64 r;
danielk197762c14b32008-11-19 09:05:26 +0000357 UNUSED_PARAMETER2(NotUsed, NotUsed2);
drh2fa18682008-03-19 14:15:34 +0000358 sqlite3_randomness(sizeof(r), &r);
drh874abbe2006-02-23 21:51:12 +0000359 if( (r<<1)==0 ) r = 0; /* Prevent 0x8000.... as the result so that we */
360 /* can always do abs() of the result */
drh52fc8492006-02-23 21:43:55 +0000361 sqlite3_result_int64(context, r);
drhf9ffac92002-03-02 19:00:31 +0000362}
363
364/*
drh137c7282007-01-29 17:58:28 +0000365** Implementation of randomblob(N). Return a random blob
366** that is N bytes long.
drh63cf66f2007-01-29 15:50:05 +0000367*/
drh137c7282007-01-29 17:58:28 +0000368static void randomBlob(
drh63cf66f2007-01-29 15:50:05 +0000369 sqlite3_context *context,
370 int argc,
371 sqlite3_value **argv
372){
drh137c7282007-01-29 17:58:28 +0000373 int n;
374 unsigned char *p;
drh63cf66f2007-01-29 15:50:05 +0000375 assert( argc==1 );
danielk1977f3d3c272008-11-19 16:52:44 +0000376 UNUSED_PARAMETER(argc);
drh63cf66f2007-01-29 15:50:05 +0000377 n = sqlite3_value_int(argv[0]);
drh023ae032007-05-08 12:12:16 +0000378 if( n<1 ){
379 n = 1;
380 }
danielk1977a1644fd2007-08-29 12:31:25 +0000381 p = contextMalloc(context, n);
drh02d85832007-05-07 19:31:15 +0000382 if( p ){
drh2fa18682008-03-19 14:15:34 +0000383 sqlite3_randomness(n, p);
drh17435752007-08-16 04:30:38 +0000384 sqlite3_result_blob(context, (char*)p, n, sqlite3_free);
drh02d85832007-05-07 19:31:15 +0000385 }
drh63cf66f2007-01-29 15:50:05 +0000386}
387
388/*
drh6ed41ad2002-04-06 14:10:47 +0000389** Implementation of the last_insert_rowid() SQL function. The return
danielk197724b03fd2004-05-10 10:34:34 +0000390** value is the same as the sqlite3_last_insert_rowid() API function.
drh6ed41ad2002-04-06 14:10:47 +0000391*/
danielk197751ad0ec2004-05-24 12:39:02 +0000392static void last_insert_rowid(
danielk19770ae8b832004-05-25 12:05:56 +0000393 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +0000394 int NotUsed,
395 sqlite3_value **NotUsed2
danielk197751ad0ec2004-05-24 12:39:02 +0000396){
drhfa4a4b92008-03-19 21:45:51 +0000397 sqlite3 *db = sqlite3_context_db_handle(context);
danielk197762c14b32008-11-19 09:05:26 +0000398 UNUSED_PARAMETER2(NotUsed, NotUsed2);
drhf9b596e2004-05-26 16:54:42 +0000399 sqlite3_result_int64(context, sqlite3_last_insert_rowid(db));
drh6ed41ad2002-04-06 14:10:47 +0000400}
401
rdcf146a772004-02-25 22:51:06 +0000402/*
danielk1977b28af712004-06-21 06:50:26 +0000403** Implementation of the changes() SQL function. The return value is the
404** same as the sqlite3_changes() API function.
rdcf146a772004-02-25 22:51:06 +0000405*/
danielk1977b28af712004-06-21 06:50:26 +0000406static void changes(
drhf9b596e2004-05-26 16:54:42 +0000407 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +0000408 int NotUsed,
409 sqlite3_value **NotUsed2
drhf9b596e2004-05-26 16:54:42 +0000410){
drhfa4a4b92008-03-19 21:45:51 +0000411 sqlite3 *db = sqlite3_context_db_handle(context);
danielk197762c14b32008-11-19 09:05:26 +0000412 UNUSED_PARAMETER2(NotUsed, NotUsed2);
drhf4479502004-05-27 03:12:53 +0000413 sqlite3_result_int(context, sqlite3_changes(db));
rdcb0c374f2004-02-20 22:53:38 +0000414}
rdcf146a772004-02-25 22:51:06 +0000415
416/*
danielk1977b28af712004-06-21 06:50:26 +0000417** Implementation of the total_changes() SQL function. The return value is
418** the same as the sqlite3_total_changes() API function.
rdcf146a772004-02-25 22:51:06 +0000419*/
danielk1977b28af712004-06-21 06:50:26 +0000420static void total_changes(
421 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +0000422 int NotUsed,
423 sqlite3_value **NotUsed2
danielk197751ad0ec2004-05-24 12:39:02 +0000424){
drhfa4a4b92008-03-19 21:45:51 +0000425 sqlite3 *db = sqlite3_context_db_handle(context);
danielk197762c14b32008-11-19 09:05:26 +0000426 UNUSED_PARAMETER2(NotUsed, NotUsed2);
danielk1977b28af712004-06-21 06:50:26 +0000427 sqlite3_result_int(context, sqlite3_total_changes(db));
rdcb0c374f2004-02-20 22:53:38 +0000428}
429
drh6ed41ad2002-04-06 14:10:47 +0000430/*
drh4e5ffc52004-08-31 00:52:37 +0000431** A structure defining how to do GLOB-style comparisons.
danielk1977d02eb1f2004-06-06 09:44:03 +0000432*/
drh4e5ffc52004-08-31 00:52:37 +0000433struct compareInfo {
434 u8 matchAll;
435 u8 matchOne;
436 u8 matchSet;
437 u8 noCase;
danielk1977d02eb1f2004-06-06 09:44:03 +0000438};
drh55ef4d92005-08-14 01:20:37 +0000439
drhb9175ae2007-12-07 18:39:04 +0000440/*
441** For LIKE and GLOB matching on EBCDIC machines, assume that every
442** character is exactly one byte in size. Also, all characters are
443** able to participate in upper-case-to-lower-case mappings in EBCDIC
444** whereas only characters less than 0x80 do in ASCII.
445*/
446#if defined(SQLITE_EBCDIC)
drh769e97e2009-04-01 16:33:37 +0000447# define sqlite3Utf8Read(A,C) (*(A++))
drh6ed4b782007-12-10 18:07:20 +0000448# define GlogUpperToLower(A) A = sqlite3UpperToLower[A]
drhb9175ae2007-12-07 18:39:04 +0000449#else
drh6ed4b782007-12-10 18:07:20 +0000450# define GlogUpperToLower(A) if( A<0x80 ){ A = sqlite3UpperToLower[A]; }
drhb9175ae2007-12-07 18:39:04 +0000451#endif
452
drh4e5ffc52004-08-31 00:52:37 +0000453static const struct compareInfo globInfo = { '*', '?', '[', 0 };
drh55ef4d92005-08-14 01:20:37 +0000454/* The correct SQL-92 behavior is for the LIKE operator to ignore
455** case. Thus 'a' LIKE 'A' would be true. */
456static const struct compareInfo likeInfoNorm = { '%', '_', 0, 1 };
457/* If SQLITE_CASE_SENSITIVE_LIKE is defined, then the LIKE operator
458** is case sensitive causing 'a' LIKE 'A' to be false */
459static const struct compareInfo likeInfoAlt = { '%', '_', 0, 0 };
danielk1977d02eb1f2004-06-06 09:44:03 +0000460
461/*
drh4e5ffc52004-08-31 00:52:37 +0000462** Compare two UTF-8 strings for equality where the first string can
463** potentially be a "glob" expression. Return true (1) if they
464** are the same and false (0) if they are different.
drh0ac65892002-04-20 14:24:41 +0000465**
drh4e5ffc52004-08-31 00:52:37 +0000466** Globbing rules:
drh0ac65892002-04-20 14:24:41 +0000467**
drh4e5ffc52004-08-31 00:52:37 +0000468** '*' Matches any sequence of zero or more characters.
danielk1977d02eb1f2004-06-06 09:44:03 +0000469**
drh4e5ffc52004-08-31 00:52:37 +0000470** '?' Matches exactly one character.
471**
472** [...] Matches one character from the enclosed list of
473** characters.
474**
475** [^...] Matches one character not in the enclosed list.
476**
477** With the [...] and [^...] matching, a ']' character can be included
478** in the list by making it the first character after '[' or '^'. A
479** range of characters can be specified using '-'. Example:
480** "[a-z]" matches any single lower-case letter. To match a '-', make
481** it the last character in the list.
482**
483** This routine is usually quick, but can be N**2 in the worst case.
484**
485** Hints: to match '*' or '?', put them in "[]". Like this:
486**
487** abc[*]xyz Matches "abc*xyz" only
drh0ac65892002-04-20 14:24:41 +0000488*/
danielk19777c6303c2004-11-17 16:41:29 +0000489static int patternCompare(
drh4e5ffc52004-08-31 00:52:37 +0000490 const u8 *zPattern, /* The glob pattern */
491 const u8 *zString, /* The string to compare against the glob */
danielk19777c6303c2004-11-17 16:41:29 +0000492 const struct compareInfo *pInfo, /* Information about how to do the compare */
493 const int esc /* The escape character */
danielk197751ad0ec2004-05-24 12:39:02 +0000494){
drh66150952007-07-23 19:12:41 +0000495 int c, c2;
drh4e5ffc52004-08-31 00:52:37 +0000496 int invert;
497 int seen;
drh4e5ffc52004-08-31 00:52:37 +0000498 u8 matchOne = pInfo->matchOne;
499 u8 matchAll = pInfo->matchAll;
500 u8 matchSet = pInfo->matchSet;
501 u8 noCase = pInfo->noCase;
danielk19777c6303c2004-11-17 16:41:29 +0000502 int prevEscape = 0; /* True if the previous character was 'escape' */
danielk1977d02eb1f2004-06-06 09:44:03 +0000503
drh769e97e2009-04-01 16:33:37 +0000504 while( (c = sqlite3Utf8Read(zPattern,&zPattern))!=0 ){
danielk19777c6303c2004-11-17 16:41:29 +0000505 if( !prevEscape && c==matchAll ){
drh769e97e2009-04-01 16:33:37 +0000506 while( (c=sqlite3Utf8Read(zPattern,&zPattern)) == matchAll
drh66150952007-07-23 19:12:41 +0000507 || c == matchOne ){
drh769e97e2009-04-01 16:33:37 +0000508 if( c==matchOne && sqlite3Utf8Read(zString, &zString)==0 ){
drh66150952007-07-23 19:12:41 +0000509 return 0;
drh4e5ffc52004-08-31 00:52:37 +0000510 }
danielk1977ad7dd422004-06-06 12:41:49 +0000511 }
drh66150952007-07-23 19:12:41 +0000512 if( c==0 ){
513 return 1;
514 }else if( c==esc ){
drh769e97e2009-04-01 16:33:37 +0000515 c = sqlite3Utf8Read(zPattern, &zPattern);
drh66150952007-07-23 19:12:41 +0000516 if( c==0 ){
517 return 0;
518 }
519 }else if( c==matchSet ){
520 assert( esc==0 ); /* This is GLOB, not LIKE */
521 assert( matchSet<0x80 ); /* '[' is a single-byte character */
522 while( *zString && patternCompare(&zPattern[-1],zString,pInfo,esc)==0 ){
drh4a919112007-05-15 11:55:09 +0000523 SQLITE_SKIP_UTF8(zString);
drh4e5ffc52004-08-31 00:52:37 +0000524 }
525 return *zString!=0;
drh66150952007-07-23 19:12:41 +0000526 }
drh769e97e2009-04-01 16:33:37 +0000527 while( (c2 = sqlite3Utf8Read(zString,&zString))!=0 ){
drh66150952007-07-23 19:12:41 +0000528 if( noCase ){
drh6ed4b782007-12-10 18:07:20 +0000529 GlogUpperToLower(c2);
530 GlogUpperToLower(c);
drh66150952007-07-23 19:12:41 +0000531 while( c2 != 0 && c2 != c ){
drh769e97e2009-04-01 16:33:37 +0000532 c2 = sqlite3Utf8Read(zString, &zString);
drh6ed4b782007-12-10 18:07:20 +0000533 GlogUpperToLower(c2);
drh4e5ffc52004-08-31 00:52:37 +0000534 }
drh66150952007-07-23 19:12:41 +0000535 }else{
536 while( c2 != 0 && c2 != c ){
drh769e97e2009-04-01 16:33:37 +0000537 c2 = sqlite3Utf8Read(zString, &zString);
drh66150952007-07-23 19:12:41 +0000538 }
drh4e5ffc52004-08-31 00:52:37 +0000539 }
drh66150952007-07-23 19:12:41 +0000540 if( c2==0 ) return 0;
541 if( patternCompare(zPattern,zString,pInfo,esc) ) return 1;
542 }
543 return 0;
544 }else if( !prevEscape && c==matchOne ){
drh769e97e2009-04-01 16:33:37 +0000545 if( sqlite3Utf8Read(zString, &zString)==0 ){
drh4e5ffc52004-08-31 00:52:37 +0000546 return 0;
danielk1977d02eb1f2004-06-06 09:44:03 +0000547 }
drh4e5ffc52004-08-31 00:52:37 +0000548 }else if( c==matchSet ){
549 int prior_c = 0;
danielk19777c6303c2004-11-17 16:41:29 +0000550 assert( esc==0 ); /* This only occurs for GLOB, not LIKE */
drh4e5ffc52004-08-31 00:52:37 +0000551 seen = 0;
552 invert = 0;
drh769e97e2009-04-01 16:33:37 +0000553 c = sqlite3Utf8Read(zString, &zString);
drh4e5ffc52004-08-31 00:52:37 +0000554 if( c==0 ) return 0;
drh769e97e2009-04-01 16:33:37 +0000555 c2 = sqlite3Utf8Read(zPattern, &zPattern);
drh66150952007-07-23 19:12:41 +0000556 if( c2=='^' ){
557 invert = 1;
drh769e97e2009-04-01 16:33:37 +0000558 c2 = sqlite3Utf8Read(zPattern, &zPattern);
drh66150952007-07-23 19:12:41 +0000559 }
drh4e5ffc52004-08-31 00:52:37 +0000560 if( c2==']' ){
561 if( c==']' ) seen = 1;
drh769e97e2009-04-01 16:33:37 +0000562 c2 = sqlite3Utf8Read(zPattern, &zPattern);
drh4e5ffc52004-08-31 00:52:37 +0000563 }
drh66150952007-07-23 19:12:41 +0000564 while( c2 && c2!=']' ){
565 if( c2=='-' && zPattern[0]!=']' && zPattern[0]!=0 && prior_c>0 ){
drh769e97e2009-04-01 16:33:37 +0000566 c2 = sqlite3Utf8Read(zPattern, &zPattern);
drh4e5ffc52004-08-31 00:52:37 +0000567 if( c>=prior_c && c<=c2 ) seen = 1;
568 prior_c = 0;
drh4e5ffc52004-08-31 00:52:37 +0000569 }else{
drh66150952007-07-23 19:12:41 +0000570 if( c==c2 ){
571 seen = 1;
572 }
drh4e5ffc52004-08-31 00:52:37 +0000573 prior_c = c2;
574 }
drh769e97e2009-04-01 16:33:37 +0000575 c2 = sqlite3Utf8Read(zPattern, &zPattern);
drh4e5ffc52004-08-31 00:52:37 +0000576 }
drh66150952007-07-23 19:12:41 +0000577 if( c2==0 || (seen ^ invert)==0 ){
578 return 0;
579 }
580 }else if( esc==c && !prevEscape ){
danielk19777c6303c2004-11-17 16:41:29 +0000581 prevEscape = 1;
drh4e5ffc52004-08-31 00:52:37 +0000582 }else{
drh769e97e2009-04-01 16:33:37 +0000583 c2 = sqlite3Utf8Read(zString, &zString);
drh4e5ffc52004-08-31 00:52:37 +0000584 if( noCase ){
drh6ed4b782007-12-10 18:07:20 +0000585 GlogUpperToLower(c);
586 GlogUpperToLower(c2);
drh4e5ffc52004-08-31 00:52:37 +0000587 }
drh66150952007-07-23 19:12:41 +0000588 if( c!=c2 ){
589 return 0;
590 }
danielk19777c6303c2004-11-17 16:41:29 +0000591 prevEscape = 0;
danielk1977d02eb1f2004-06-06 09:44:03 +0000592 }
danielk197751ad0ec2004-05-24 12:39:02 +0000593 }
drh4e5ffc52004-08-31 00:52:37 +0000594 return *zString==0;
drh0ac65892002-04-20 14:24:41 +0000595}
drh4e5ffc52004-08-31 00:52:37 +0000596
drh55ef4d92005-08-14 01:20:37 +0000597/*
598** Count the number of times that the LIKE operator (or GLOB which is
599** just a variation of LIKE) gets called. This is used for testing
600** only.
601*/
602#ifdef SQLITE_TEST
603int sqlite3_like_count = 0;
604#endif
605
danielk19773f6b0872004-06-17 05:36:44 +0000606
607/*
608** Implementation of the like() SQL function. This function implements
609** the build-in LIKE operator. The first argument to the function is the
610** pattern and the second argument is the string. So, the SQL statements:
611**
612** A LIKE B
613**
614** is implemented as like(B,A).
615**
drh55ef4d92005-08-14 01:20:37 +0000616** This same function (with a different compareInfo structure) computes
617** the GLOB operator.
danielk19773f6b0872004-06-17 05:36:44 +0000618*/
619static void likeFunc(
620 sqlite3_context *context,
621 int argc,
622 sqlite3_value **argv
623){
drhbeb818d2007-05-08 15:34:47 +0000624 const unsigned char *zA, *zB;
drh85892bd2007-05-10 13:23:22 +0000625 int escape = 0;
drhbb4957f2008-03-20 14:03:29 +0000626 sqlite3 *db = sqlite3_context_db_handle(context);
drhbeb818d2007-05-08 15:34:47 +0000627
drh1f0feef2007-05-15 13:27:07 +0000628 zB = sqlite3_value_text(argv[0]);
629 zA = sqlite3_value_text(argv[1]);
630
drhbeb818d2007-05-08 15:34:47 +0000631 /* Limit the length of the LIKE or GLOB pattern to avoid problems
632 ** of deep recursion and N*N behavior in patternCompare().
633 */
drhbb4957f2008-03-20 14:03:29 +0000634 if( sqlite3_value_bytes(argv[0]) >
635 db->aLimit[SQLITE_LIMIT_LIKE_PATTERN_LENGTH] ){
drhbeb818d2007-05-08 15:34:47 +0000636 sqlite3_result_error(context, "LIKE or GLOB pattern too complex", -1);
637 return;
638 }
drh1f0feef2007-05-15 13:27:07 +0000639 assert( zB==sqlite3_value_text(argv[0]) ); /* Encoding did not change */
drhbeb818d2007-05-08 15:34:47 +0000640
danielk19777c6303c2004-11-17 16:41:29 +0000641 if( argc==3 ){
642 /* The escape character string must consist of a single UTF-8 character.
643 ** Otherwise, return an error.
644 */
645 const unsigned char *zEsc = sqlite3_value_text(argv[2]);
drh7a521cf2007-04-25 18:23:52 +0000646 if( zEsc==0 ) return;
drhee858132007-05-08 20:37:38 +0000647 if( sqlite3Utf8CharLen((char*)zEsc, -1)!=1 ){
danielk19777c6303c2004-11-17 16:41:29 +0000648 sqlite3_result_error(context,
649 "ESCAPE expression must be a single character", -1);
650 return;
651 }
drh769e97e2009-04-01 16:33:37 +0000652 escape = sqlite3Utf8Read(zEsc, &zEsc);
danielk19777c6303c2004-11-17 16:41:29 +0000653 }
danielk19773f6b0872004-06-17 05:36:44 +0000654 if( zA && zB ){
drh55ef4d92005-08-14 01:20:37 +0000655 struct compareInfo *pInfo = sqlite3_user_data(context);
656#ifdef SQLITE_TEST
657 sqlite3_like_count++;
658#endif
drhbeb818d2007-05-08 15:34:47 +0000659
danielk1977b56fe1f2007-05-09 08:24:44 +0000660 sqlite3_result_int(context, patternCompare(zB, zA, pInfo, escape));
danielk197751ad0ec2004-05-24 12:39:02 +0000661 }
drh8912d102002-05-26 21:34:58 +0000662}
663
664/*
665** Implementation of the NULLIF(x,y) function. The result is the first
666** argument if the arguments are different. The result is NULL if the
667** arguments are equal to each other.
668*/
drhf9b596e2004-05-26 16:54:42 +0000669static void nullifFunc(
670 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +0000671 int NotUsed,
drhf9b596e2004-05-26 16:54:42 +0000672 sqlite3_value **argv
673){
danielk1977dc1bdc42004-06-11 10:51:27 +0000674 CollSeq *pColl = sqlite3GetFuncCollSeq(context);
danielk197762c14b32008-11-19 09:05:26 +0000675 UNUSED_PARAMETER(NotUsed);
danielk1977dc1bdc42004-06-11 10:51:27 +0000676 if( sqlite3MemCompare(argv[0], argv[1], pColl)!=0 ){
drhf4479502004-05-27 03:12:53 +0000677 sqlite3_result_value(context, argv[0]);
drh8912d102002-05-26 21:34:58 +0000678 }
drh0ac65892002-04-20 14:24:41 +0000679}
680
drh647cb0e2002-11-04 19:32:25 +0000681/*
682** Implementation of the VERSION(*) function. The result is the version
683** of the SQLite library that is running.
684*/
drhf9b596e2004-05-26 16:54:42 +0000685static void versionFunc(
686 sqlite3_context *context,
danielk197762c14b32008-11-19 09:05:26 +0000687 int NotUsed,
688 sqlite3_value **NotUsed2
drhf9b596e2004-05-26 16:54:42 +0000689){
danielk197762c14b32008-11-19 09:05:26 +0000690 UNUSED_PARAMETER2(NotUsed, NotUsed2);
danielk1977d8123362004-06-12 09:25:12 +0000691 sqlite3_result_text(context, sqlite3_version, -1, SQLITE_STATIC);
drh647cb0e2002-11-04 19:32:25 +0000692}
693
drh137c7282007-01-29 17:58:28 +0000694/* Array for converting from half-bytes (nybbles) into ASCII hex
695** digits. */
696static const char hexdigits[] = {
697 '0', '1', '2', '3', '4', '5', '6', '7',
698 '8', '9', 'A', 'B', 'C', 'D', 'E', 'F'
699};
danielk1977d641d642004-11-18 15:44:29 +0000700
drh47394702003-08-20 01:03:33 +0000701/*
702** EXPERIMENTAL - This is not an official function. The interface may
703** change. This function may disappear. Do not write code that depends
704** on this function.
705**
706** Implementation of the QUOTE() function. This function takes a single
707** argument. If the argument is numeric, the return value is the same as
708** the argument. If the argument is NULL, the return value is the string
709** "NULL". Otherwise, the argument is enclosed in single quotes with
710** single-quote escapes.
711*/
danielk19770ae8b832004-05-25 12:05:56 +0000712static void quoteFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
drha0df4cc2009-02-02 17:29:59 +0000713 assert( argc==1 );
drh1d34fde2009-02-03 15:50:33 +0000714 UNUSED_PARAMETER(argc);
drhf9b596e2004-05-26 16:54:42 +0000715 switch( sqlite3_value_type(argv[0]) ){
drh9c054832004-05-31 18:51:57 +0000716 case SQLITE_INTEGER:
717 case SQLITE_FLOAT: {
drhf4479502004-05-27 03:12:53 +0000718 sqlite3_result_value(context, argv[0]);
drhf9b596e2004-05-26 16:54:42 +0000719 break;
720 }
danielk19773f41e972004-06-08 00:39:01 +0000721 case SQLITE_BLOB: {
danielk19773f41e972004-06-08 00:39:01 +0000722 char *zText = 0;
danielk19773f41e972004-06-08 00:39:01 +0000723 char const *zBlob = sqlite3_value_blob(argv[0]);
drh1f0feef2007-05-15 13:27:07 +0000724 int nBlob = sqlite3_value_bytes(argv[0]);
725 assert( zBlob==sqlite3_value_blob(argv[0]) ); /* No encoding change */
drhb1a6c3c2008-03-20 16:30:17 +0000726 zText = (char *)contextMalloc(context, (2*(i64)nBlob)+4);
danielk1977a1644fd2007-08-29 12:31:25 +0000727 if( zText ){
danielk19773f41e972004-06-08 00:39:01 +0000728 int i;
729 for(i=0; i<nBlob; i++){
730 zText[(i*2)+2] = hexdigits[(zBlob[i]>>4)&0x0F];
731 zText[(i*2)+3] = hexdigits[(zBlob[i])&0x0F];
732 }
733 zText[(nBlob*2)+2] = '\'';
734 zText[(nBlob*2)+3] = '\0';
735 zText[0] = 'X';
736 zText[1] = '\'';
danielk1977d8123362004-06-12 09:25:12 +0000737 sqlite3_result_text(context, zText, -1, SQLITE_TRANSIENT);
drh17435752007-08-16 04:30:38 +0000738 sqlite3_free(zText);
danielk19773f41e972004-06-08 00:39:01 +0000739 }
740 break;
741 }
drh9c054832004-05-31 18:51:57 +0000742 case SQLITE_TEXT: {
drh023ae032007-05-08 12:12:16 +0000743 int i,j;
744 u64 n;
drh2646da72005-12-09 20:02:05 +0000745 const unsigned char *zArg = sqlite3_value_text(argv[0]);
drhf9b596e2004-05-26 16:54:42 +0000746 char *z;
747
drh7a521cf2007-04-25 18:23:52 +0000748 if( zArg==0 ) return;
drh023ae032007-05-08 12:12:16 +0000749 for(i=0, n=0; zArg[i]; i++){ if( zArg[i]=='\'' ) n++; }
drhb1a6c3c2008-03-20 16:30:17 +0000750 z = contextMalloc(context, ((i64)i)+((i64)n)+3);
danielk1977a1644fd2007-08-29 12:31:25 +0000751 if( z ){
752 z[0] = '\'';
753 for(i=0, j=1; zArg[i]; i++){
754 z[j++] = zArg[i];
755 if( zArg[i]=='\'' ){
756 z[j++] = '\'';
757 }
drhf9b596e2004-05-26 16:54:42 +0000758 }
danielk1977a1644fd2007-08-29 12:31:25 +0000759 z[j++] = '\'';
760 z[j] = 0;
761 sqlite3_result_text(context, z, j, sqlite3_free);
drhf9b596e2004-05-26 16:54:42 +0000762 }
drha0df4cc2009-02-02 17:29:59 +0000763 break;
764 }
765 default: {
766 assert( sqlite3_value_type(argv[0])==SQLITE_NULL );
767 sqlite3_result_text(context, "NULL", 4, SQLITE_STATIC);
768 break;
drhf9b596e2004-05-26 16:54:42 +0000769 }
drh47394702003-08-20 01:03:33 +0000770 }
771}
772
drh137c7282007-01-29 17:58:28 +0000773/*
774** The hex() function. Interpret the argument as a blob. Return
775** a hexadecimal rendering as text.
776*/
777static void hexFunc(
778 sqlite3_context *context,
779 int argc,
780 sqlite3_value **argv
781){
782 int i, n;
783 const unsigned char *pBlob;
784 char *zHex, *z;
785 assert( argc==1 );
danielk1977f3d3c272008-11-19 16:52:44 +0000786 UNUSED_PARAMETER(argc);
drh1f0feef2007-05-15 13:27:07 +0000787 pBlob = sqlite3_value_blob(argv[0]);
drh137c7282007-01-29 17:58:28 +0000788 n = sqlite3_value_bytes(argv[0]);
drh1f0feef2007-05-15 13:27:07 +0000789 assert( pBlob==sqlite3_value_blob(argv[0]) ); /* No encoding change */
drhb1a6c3c2008-03-20 16:30:17 +0000790 z = zHex = contextMalloc(context, ((i64)n)*2 + 1);
danielk1977a1644fd2007-08-29 12:31:25 +0000791 if( zHex ){
792 for(i=0; i<n; i++, pBlob++){
793 unsigned char c = *pBlob;
794 *(z++) = hexdigits[(c>>4)&0xf];
795 *(z++) = hexdigits[c&0xf];
796 }
797 *z = 0;
798 sqlite3_result_text(context, zHex, n*2, sqlite3_free);
drh137c7282007-01-29 17:58:28 +0000799 }
drh137c7282007-01-29 17:58:28 +0000800}
801
drh26b6d902007-03-17 13:27:54 +0000802/*
drh8cff3822007-05-02 02:08:28 +0000803** The zeroblob(N) function returns a zero-filled blob of size N bytes.
804*/
805static void zeroblobFunc(
806 sqlite3_context *context,
807 int argc,
808 sqlite3_value **argv
809){
drh98640a32007-06-07 19:08:32 +0000810 i64 n;
drh8cff3822007-05-02 02:08:28 +0000811 assert( argc==1 );
danielk1977f3d3c272008-11-19 16:52:44 +0000812 UNUSED_PARAMETER(argc);
drh98640a32007-06-07 19:08:32 +0000813 n = sqlite3_value_int64(argv[0]);
814 if( n>SQLITE_MAX_LENGTH ){
815 sqlite3_result_error_toobig(context);
816 }else{
drh1bd10f82008-12-10 21:19:56 +0000817 sqlite3_result_zeroblob(context, (int)n);
drh98640a32007-06-07 19:08:32 +0000818 }
drh8cff3822007-05-02 02:08:28 +0000819}
820
821/*
drh26b6d902007-03-17 13:27:54 +0000822** The replace() function. Three arguments are all strings: call
823** them A, B, and C. The result is also a string which is derived
824** from A by replacing every occurance of B with C. The match
825** must be exact. Collating sequences are not used.
826*/
827static void replaceFunc(
828 sqlite3_context *context,
829 int argc,
830 sqlite3_value **argv
831){
832 const unsigned char *zStr; /* The input string A */
833 const unsigned char *zPattern; /* The pattern string B */
834 const unsigned char *zRep; /* The replacement string C */
835 unsigned char *zOut; /* The output */
836 int nStr; /* Size of zStr */
837 int nPattern; /* Size of zPattern */
838 int nRep; /* Size of zRep */
drh2e6400b2007-05-08 15:46:18 +0000839 i64 nOut; /* Maximum size of zOut */
drh26b6d902007-03-17 13:27:54 +0000840 int loopLimit; /* Last zStr[] that might match zPattern[] */
841 int i, j; /* Loop counters */
842
843 assert( argc==3 );
danielk1977f3d3c272008-11-19 16:52:44 +0000844 UNUSED_PARAMETER(argc);
drh26b6d902007-03-17 13:27:54 +0000845 zStr = sqlite3_value_text(argv[0]);
drh7a521cf2007-04-25 18:23:52 +0000846 if( zStr==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000847 nStr = sqlite3_value_bytes(argv[0]);
848 assert( zStr==sqlite3_value_text(argv[0]) ); /* No encoding change */
drh26b6d902007-03-17 13:27:54 +0000849 zPattern = sqlite3_value_text(argv[1]);
drha605fe82009-02-01 18:08:40 +0000850 if( zPattern==0 ){
drh23336062009-02-03 13:19:12 +0000851 assert( sqlite3_value_type(argv[1])==SQLITE_NULL
852 || sqlite3_context_db_handle(context)->mallocFailed );
drha605fe82009-02-01 18:08:40 +0000853 return;
854 }
855 if( zPattern[0]==0 ){
856 assert( sqlite3_value_type(argv[1])!=SQLITE_NULL );
857 sqlite3_result_value(context, argv[0]);
858 return;
859 }
drh1f0feef2007-05-15 13:27:07 +0000860 nPattern = sqlite3_value_bytes(argv[1]);
861 assert( zPattern==sqlite3_value_text(argv[1]) ); /* No encoding change */
drh26b6d902007-03-17 13:27:54 +0000862 zRep = sqlite3_value_text(argv[2]);
drh7a521cf2007-04-25 18:23:52 +0000863 if( zRep==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000864 nRep = sqlite3_value_bytes(argv[2]);
865 assert( zRep==sqlite3_value_text(argv[2]) );
drh2e6400b2007-05-08 15:46:18 +0000866 nOut = nStr + 1;
867 assert( nOut<SQLITE_MAX_LENGTH );
drhb1a6c3c2008-03-20 16:30:17 +0000868 zOut = contextMalloc(context, (i64)nOut);
drh2e6400b2007-05-08 15:46:18 +0000869 if( zOut==0 ){
870 return;
drh26b6d902007-03-17 13:27:54 +0000871 }
drh26b6d902007-03-17 13:27:54 +0000872 loopLimit = nStr - nPattern;
873 for(i=j=0; i<=loopLimit; i++){
874 if( zStr[i]!=zPattern[0] || memcmp(&zStr[i], zPattern, nPattern) ){
875 zOut[j++] = zStr[i];
876 }else{
drh4a50aac2007-08-23 02:47:53 +0000877 u8 *zOld;
drhbb4957f2008-03-20 14:03:29 +0000878 sqlite3 *db = sqlite3_context_db_handle(context);
drh2e6400b2007-05-08 15:46:18 +0000879 nOut += nRep - nPattern;
drhbb4957f2008-03-20 14:03:29 +0000880 if( nOut>=db->aLimit[SQLITE_LIMIT_LENGTH] ){
drha0206bc2007-05-08 15:15:02 +0000881 sqlite3_result_error_toobig(context);
drh633e6d52008-07-28 19:34:53 +0000882 sqlite3DbFree(db, zOut);
danielk197717374e82007-05-08 14:39:04 +0000883 return;
884 }
drh4a50aac2007-08-23 02:47:53 +0000885 zOld = zOut;
drh2e6400b2007-05-08 15:46:18 +0000886 zOut = sqlite3_realloc(zOut, (int)nOut);
887 if( zOut==0 ){
danielk1977a1644fd2007-08-29 12:31:25 +0000888 sqlite3_result_error_nomem(context);
drh633e6d52008-07-28 19:34:53 +0000889 sqlite3DbFree(db, zOld);
drh2e6400b2007-05-08 15:46:18 +0000890 return;
891 }
drh26b6d902007-03-17 13:27:54 +0000892 memcpy(&zOut[j], zRep, nRep);
893 j += nRep;
894 i += nPattern-1;
895 }
896 }
drh2e6400b2007-05-08 15:46:18 +0000897 assert( j+nStr-i+1==nOut );
drh26b6d902007-03-17 13:27:54 +0000898 memcpy(&zOut[j], &zStr[i], nStr-i);
899 j += nStr - i;
900 assert( j<=nOut );
901 zOut[j] = 0;
902 sqlite3_result_text(context, (char*)zOut, j, sqlite3_free);
903}
904
drh309b3382007-03-17 17:52:42 +0000905/*
906** Implementation of the TRIM(), LTRIM(), and RTRIM() functions.
907** The userdata is 0x1 for left trim, 0x2 for right trim, 0x3 for both.
908*/
909static void trimFunc(
910 sqlite3_context *context,
911 int argc,
912 sqlite3_value **argv
913){
914 const unsigned char *zIn; /* Input string */
915 const unsigned char *zCharSet; /* Set of characters to trim */
916 int nIn; /* Number of bytes in input */
drh7209c692008-04-27 18:40:11 +0000917 int flags; /* 1: trimleft 2: trimright 3: trim */
drhd1e3a612007-04-27 21:59:52 +0000918 int i; /* Loop counter */
drh1bd10f82008-12-10 21:19:56 +0000919 unsigned char *aLen = 0; /* Length of each character in zCharSet */
920 unsigned char **azChar = 0; /* Individual characters in zCharSet */
drhd1e3a612007-04-27 21:59:52 +0000921 int nChar; /* Number of characters in zCharSet */
922
drh309b3382007-03-17 17:52:42 +0000923 if( sqlite3_value_type(argv[0])==SQLITE_NULL ){
924 return;
925 }
926 zIn = sqlite3_value_text(argv[0]);
drh7a521cf2007-04-25 18:23:52 +0000927 if( zIn==0 ) return;
drh1f0feef2007-05-15 13:27:07 +0000928 nIn = sqlite3_value_bytes(argv[0]);
929 assert( zIn==sqlite3_value_text(argv[0]) );
drh309b3382007-03-17 17:52:42 +0000930 if( argc==1 ){
drhd1e3a612007-04-27 21:59:52 +0000931 static const unsigned char lenOne[] = { 1 };
danielk1977a4de4532008-09-02 15:44:08 +0000932 static unsigned char * const azOne[] = { (u8*)" " };
drhd1e3a612007-04-27 21:59:52 +0000933 nChar = 1;
drh8cff3822007-05-02 02:08:28 +0000934 aLen = (u8*)lenOne;
danielk1977bc67da42007-12-11 04:23:19 +0000935 azChar = (unsigned char **)azOne;
drhd1e3a612007-04-27 21:59:52 +0000936 zCharSet = 0;
drh7a521cf2007-04-25 18:23:52 +0000937 }else if( (zCharSet = sqlite3_value_text(argv[1]))==0 ){
drh309b3382007-03-17 17:52:42 +0000938 return;
drhd1e3a612007-04-27 21:59:52 +0000939 }else{
940 const unsigned char *z;
941 for(z=zCharSet, nChar=0; *z; nChar++){
drh4a919112007-05-15 11:55:09 +0000942 SQLITE_SKIP_UTF8(z);
drhd1e3a612007-04-27 21:59:52 +0000943 }
944 if( nChar>0 ){
drhb1a6c3c2008-03-20 16:30:17 +0000945 azChar = contextMalloc(context, ((i64)nChar)*(sizeof(char*)+1));
drhd1e3a612007-04-27 21:59:52 +0000946 if( azChar==0 ){
947 return;
948 }
949 aLen = (unsigned char*)&azChar[nChar];
950 for(z=zCharSet, nChar=0; *z; nChar++){
danielk1977bc67da42007-12-11 04:23:19 +0000951 azChar[nChar] = (unsigned char *)z;
drh4a919112007-05-15 11:55:09 +0000952 SQLITE_SKIP_UTF8(z);
drh3abbd392008-12-10 23:04:13 +0000953 aLen[nChar] = (u8)(z - azChar[nChar]);
drhd1e3a612007-04-27 21:59:52 +0000954 }
955 }
drh309b3382007-03-17 17:52:42 +0000956 }
drhd1e3a612007-04-27 21:59:52 +0000957 if( nChar>0 ){
shane1fc41292008-07-08 22:28:48 +0000958 flags = SQLITE_PTR_TO_INT(sqlite3_user_data(context));
drh309b3382007-03-17 17:52:42 +0000959 if( flags & 1 ){
drhd1e3a612007-04-27 21:59:52 +0000960 while( nIn>0 ){
drh1bd10f82008-12-10 21:19:56 +0000961 int len = 0;
drhd1e3a612007-04-27 21:59:52 +0000962 for(i=0; i<nChar; i++){
963 len = aLen[i];
964 if( memcmp(zIn, azChar[i], len)==0 ) break;
965 }
966 if( i>=nChar ) break;
967 zIn += len;
968 nIn -= len;
drh309b3382007-03-17 17:52:42 +0000969 }
970 }
971 if( flags & 2 ){
drhd1e3a612007-04-27 21:59:52 +0000972 while( nIn>0 ){
drh1bd10f82008-12-10 21:19:56 +0000973 int len = 0;
drhd1e3a612007-04-27 21:59:52 +0000974 for(i=0; i<nChar; i++){
975 len = aLen[i];
976 if( len<=nIn && memcmp(&zIn[nIn-len],azChar[i],len)==0 ) break;
977 }
978 if( i>=nChar ) break;
979 nIn -= len;
drh309b3382007-03-17 17:52:42 +0000980 }
981 }
drhd1e3a612007-04-27 21:59:52 +0000982 if( zCharSet ){
983 sqlite3_free(azChar);
984 }
drh309b3382007-03-17 17:52:42 +0000985 }
986 sqlite3_result_text(context, (char*)zIn, nIn, SQLITE_TRANSIENT);
987}
drh26b6d902007-03-17 13:27:54 +0000988
danielk1977a4de4532008-09-02 15:44:08 +0000989
drhd24cc422003-03-27 12:51:24 +0000990#ifdef SQLITE_SOUNDEX
991/*
992** Compute the soundex encoding of a word.
993*/
drh137c7282007-01-29 17:58:28 +0000994static void soundexFunc(
995 sqlite3_context *context,
996 int argc,
997 sqlite3_value **argv
998){
drhd24cc422003-03-27 12:51:24 +0000999 char zResult[8];
drh4c755c02004-08-08 20:22:17 +00001000 const u8 *zIn;
drhd24cc422003-03-27 12:51:24 +00001001 int i, j;
1002 static const unsigned char iCode[] = {
1003 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1004 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1005 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1006 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1007 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
1008 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
1009 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
1010 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
1011 };
1012 assert( argc==1 );
drh4c755c02004-08-08 20:22:17 +00001013 zIn = (u8*)sqlite3_value_text(argv[0]);
drhbdf67e02006-08-19 11:34:01 +00001014 if( zIn==0 ) zIn = (u8*)"";
drhdc86e2b2009-01-24 11:30:42 +00001015 for(i=0; zIn[i] && !sqlite3Isalpha(zIn[i]); i++){}
drhd24cc422003-03-27 12:51:24 +00001016 if( zIn[i] ){
drhbdf67e02006-08-19 11:34:01 +00001017 u8 prevcode = iCode[zIn[i]&0x7f];
danielk197778ca0e72009-01-20 16:53:39 +00001018 zResult[0] = sqlite3Toupper(zIn[i]);
drhd24cc422003-03-27 12:51:24 +00001019 for(j=1; j<4 && zIn[i]; i++){
1020 int code = iCode[zIn[i]&0x7f];
1021 if( code>0 ){
drhbdf67e02006-08-19 11:34:01 +00001022 if( code!=prevcode ){
1023 prevcode = code;
1024 zResult[j++] = code + '0';
1025 }
1026 }else{
1027 prevcode = 0;
drhd24cc422003-03-27 12:51:24 +00001028 }
1029 }
1030 while( j<4 ){
1031 zResult[j++] = '0';
1032 }
1033 zResult[j] = 0;
danielk1977d8123362004-06-12 09:25:12 +00001034 sqlite3_result_text(context, zResult, 4, SQLITE_TRANSIENT);
drhd24cc422003-03-27 12:51:24 +00001035 }else{
danielk1977d8123362004-06-12 09:25:12 +00001036 sqlite3_result_text(context, "?000", 4, SQLITE_STATIC);
drhd24cc422003-03-27 12:51:24 +00001037 }
1038}
1039#endif
1040
drhfdb83b22006-06-17 14:12:47 +00001041#ifndef SQLITE_OMIT_LOAD_EXTENSION
1042/*
1043** A function that loads a shared-library extension then returns NULL.
1044*/
1045static void loadExt(sqlite3_context *context, int argc, sqlite3_value **argv){
danielk197765fd59f2006-06-24 11:51:33 +00001046 const char *zFile = (const char *)sqlite3_value_text(argv[0]);
drh7a521cf2007-04-25 18:23:52 +00001047 const char *zProc;
drhfa4a4b92008-03-19 21:45:51 +00001048 sqlite3 *db = sqlite3_context_db_handle(context);
drhfdb83b22006-06-17 14:12:47 +00001049 char *zErrMsg = 0;
1050
1051 if( argc==2 ){
danielk197765fd59f2006-06-24 11:51:33 +00001052 zProc = (const char *)sqlite3_value_text(argv[1]);
drh7a521cf2007-04-25 18:23:52 +00001053 }else{
1054 zProc = 0;
drhfdb83b22006-06-17 14:12:47 +00001055 }
drh7a521cf2007-04-25 18:23:52 +00001056 if( zFile && sqlite3_load_extension(db, zFile, zProc, &zErrMsg) ){
drhfdb83b22006-06-17 14:12:47 +00001057 sqlite3_result_error(context, zErrMsg, -1);
1058 sqlite3_free(zErrMsg);
1059 }
1060}
1061#endif
1062
danielk197701427a62005-01-11 13:02:33 +00001063
drh0ac65892002-04-20 14:24:41 +00001064/*
drhd3a149e2002-02-24 17:12:53 +00001065** An instance of the following structure holds the context of a
drhdd5baa92002-02-27 19:50:59 +00001066** sum() or avg() aggregate computation.
1067*/
1068typedef struct SumCtx SumCtx;
1069struct SumCtx {
drh8c08e862006-02-11 17:34:00 +00001070 double rSum; /* Floating point sum */
1071 i64 iSum; /* Integer sum */
1072 i64 cnt; /* Number of elements summed */
1073 u8 overflow; /* True if integer overflow seen */
1074 u8 approx; /* True if non-integer value was input to the sum */
drhdd5baa92002-02-27 19:50:59 +00001075};
1076
1077/*
drha97fdd32006-01-12 22:17:50 +00001078** Routines used to compute the sum, average, and total.
1079**
1080** The SUM() function follows the (broken) SQL standard which means
1081** that it returns NULL if it sums over no inputs. TOTAL returns
1082** 0.0 in that case. In addition, TOTAL always returns a float where
1083** SUM might return an integer if it never encounters a floating point
drhc806d852006-05-11 13:25:39 +00001084** value. TOTAL never fails, but SUM might through an exception if
1085** it overflows an integer.
drhdd5baa92002-02-27 19:50:59 +00001086*/
danielk19770ae8b832004-05-25 12:05:56 +00001087static void sumStep(sqlite3_context *context, int argc, sqlite3_value **argv){
drhdd5baa92002-02-27 19:50:59 +00001088 SumCtx *p;
drh3d1d95e2005-09-08 10:37:01 +00001089 int type;
drh3f219f42005-09-08 19:45:57 +00001090 assert( argc==1 );
danielk1977f3d3c272008-11-19 16:52:44 +00001091 UNUSED_PARAMETER(argc);
drh4f26d6c2004-05-26 23:25:30 +00001092 p = sqlite3_aggregate_context(context, sizeof(*p));
drh29d72102006-02-09 22:13:41 +00001093 type = sqlite3_value_numeric_type(argv[0]);
drh3d1d95e2005-09-08 10:37:01 +00001094 if( p && type!=SQLITE_NULL ){
drh739105c2002-05-29 23:22:23 +00001095 p->cnt++;
drh29d72102006-02-09 22:13:41 +00001096 if( type==SQLITE_INTEGER ){
drh8c08e862006-02-11 17:34:00 +00001097 i64 v = sqlite3_value_int64(argv[0]);
1098 p->rSum += v;
1099 if( (p->approx|p->overflow)==0 ){
1100 i64 iNewSum = p->iSum + v;
drh1bd10f82008-12-10 21:19:56 +00001101 int s1 = (int)(p->iSum >> (sizeof(i64)*8-1));
1102 int s2 = (int)(v >> (sizeof(i64)*8-1));
1103 int s3 = (int)(iNewSum >> (sizeof(i64)*8-1));
1104 p->overflow = ((s1&s2&~s3) | (~s1&~s2&s3))?1:0;
drh8c08e862006-02-11 17:34:00 +00001105 p->iSum = iNewSum;
drh29d72102006-02-09 22:13:41 +00001106 }
1107 }else{
drh8c08e862006-02-11 17:34:00 +00001108 p->rSum += sqlite3_value_double(argv[0]);
drh29d72102006-02-09 22:13:41 +00001109 p->approx = 1;
drh3f219f42005-09-08 19:45:57 +00001110 }
drh739105c2002-05-29 23:22:23 +00001111 }
drhdd5baa92002-02-27 19:50:59 +00001112}
danielk19770ae8b832004-05-25 12:05:56 +00001113static void sumFinalize(sqlite3_context *context){
drhdd5baa92002-02-27 19:50:59 +00001114 SumCtx *p;
drhabfcea22005-09-06 20:36:48 +00001115 p = sqlite3_aggregate_context(context, 0);
drhc2bd9132005-09-08 20:37:43 +00001116 if( p && p->cnt>0 ){
drh8c08e862006-02-11 17:34:00 +00001117 if( p->overflow ){
1118 sqlite3_result_error(context,"integer overflow",-1);
1119 }else if( p->approx ){
1120 sqlite3_result_double(context, p->rSum);
drhc2bd9132005-09-08 20:37:43 +00001121 }else{
drh8c08e862006-02-11 17:34:00 +00001122 sqlite3_result_int64(context, p->iSum);
drhc2bd9132005-09-08 20:37:43 +00001123 }
drh3d1d95e2005-09-08 10:37:01 +00001124 }
drhdd5baa92002-02-27 19:50:59 +00001125}
danielk19770ae8b832004-05-25 12:05:56 +00001126static void avgFinalize(sqlite3_context *context){
drhdd5baa92002-02-27 19:50:59 +00001127 SumCtx *p;
drhabfcea22005-09-06 20:36:48 +00001128 p = sqlite3_aggregate_context(context, 0);
drh739105c2002-05-29 23:22:23 +00001129 if( p && p->cnt>0 ){
drh8c08e862006-02-11 17:34:00 +00001130 sqlite3_result_double(context, p->rSum/(double)p->cnt);
drhdd5baa92002-02-27 19:50:59 +00001131 }
1132}
drha97fdd32006-01-12 22:17:50 +00001133static void totalFinalize(sqlite3_context *context){
1134 SumCtx *p;
1135 p = sqlite3_aggregate_context(context, 0);
shanefbd60f82009-02-04 03:59:25 +00001136 /* (double)0 In case of SQLITE_OMIT_FLOATING_POINT... */
1137 sqlite3_result_double(context, p ? p->rSum : (double)0);
drha97fdd32006-01-12 22:17:50 +00001138}
drhdd5baa92002-02-27 19:50:59 +00001139
1140/*
drh0bce8352002-02-28 00:41:10 +00001141** The following structure keeps track of state information for the
1142** count() aggregate function.
1143*/
1144typedef struct CountCtx CountCtx;
1145struct CountCtx {
drhfc6ad392006-02-09 13:38:19 +00001146 i64 n;
drh0bce8352002-02-28 00:41:10 +00001147};
drhdd5baa92002-02-27 19:50:59 +00001148
drh0bce8352002-02-28 00:41:10 +00001149/*
1150** Routines to implement the count() aggregate function.
1151*/
danielk19770ae8b832004-05-25 12:05:56 +00001152static void countStep(sqlite3_context *context, int argc, sqlite3_value **argv){
drh0bce8352002-02-28 00:41:10 +00001153 CountCtx *p;
drh4f26d6c2004-05-26 23:25:30 +00001154 p = sqlite3_aggregate_context(context, sizeof(*p));
drh9c054832004-05-31 18:51:57 +00001155 if( (argc==0 || SQLITE_NULL!=sqlite3_value_type(argv[0])) && p ){
drh0bce8352002-02-28 00:41:10 +00001156 p->n++;
1157 }
1158}
danielk19770ae8b832004-05-25 12:05:56 +00001159static void countFinalize(sqlite3_context *context){
drh0bce8352002-02-28 00:41:10 +00001160 CountCtx *p;
drhabfcea22005-09-06 20:36:48 +00001161 p = sqlite3_aggregate_context(context, 0);
drhfc6ad392006-02-09 13:38:19 +00001162 sqlite3_result_int64(context, p ? p->n : 0);
drh0bce8352002-02-28 00:41:10 +00001163}
1164
1165/*
drh0bce8352002-02-28 00:41:10 +00001166** Routines to implement min() and max() aggregate functions.
1167*/
danielk197762c14b32008-11-19 09:05:26 +00001168static void minmaxStep(
1169 sqlite3_context *context,
1170 int NotUsed,
1171 sqlite3_value **argv
1172){
danielk197788208052004-05-25 01:13:20 +00001173 Mem *pArg = (Mem *)argv[0];
drh9eb516c2004-07-18 20:52:32 +00001174 Mem *pBest;
danielk197762c14b32008-11-19 09:05:26 +00001175 UNUSED_PARAMETER(NotUsed);
drh9eb516c2004-07-18 20:52:32 +00001176
1177 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
1178 pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));
danielk19773aeab9e2004-06-24 00:20:04 +00001179 if( !pBest ) return;
drh268380c2004-02-25 13:47:31 +00001180
danielk197788208052004-05-25 01:13:20 +00001181 if( pBest->flags ){
drh9eb516c2004-07-18 20:52:32 +00001182 int max;
1183 int cmp;
danielk1977dc1bdc42004-06-11 10:51:27 +00001184 CollSeq *pColl = sqlite3GetFuncCollSeq(context);
danielk19777e18c252004-05-25 11:47:24 +00001185 /* This step function is used for both the min() and max() aggregates,
1186 ** the only difference between the two being that the sense of the
1187 ** comparison is inverted. For the max() aggregate, the
1188 ** sqlite3_user_data() function returns (void *)-1. For min() it
1189 ** returns (void *)db, where db is the sqlite3* database pointer.
1190 ** Therefore the next statement sets variable 'max' to 1 for the max()
1191 ** aggregate, or 0 for min().
1192 */
drh309b3382007-03-17 17:52:42 +00001193 max = sqlite3_user_data(context)!=0;
danielk1977dc1bdc42004-06-11 10:51:27 +00001194 cmp = sqlite3MemCompare(pBest, pArg, pColl);
danielk197788208052004-05-25 01:13:20 +00001195 if( (max && cmp<0) || (!max && cmp>0) ){
drhb21c8cd2007-08-21 19:33:56 +00001196 sqlite3VdbeMemCopy(pBest, pArg);
danielk197788208052004-05-25 01:13:20 +00001197 }
drh268380c2004-02-25 13:47:31 +00001198 }else{
drhb21c8cd2007-08-21 19:33:56 +00001199 sqlite3VdbeMemCopy(pBest, pArg);
drh0bce8352002-02-28 00:41:10 +00001200 }
1201}
danielk19770ae8b832004-05-25 12:05:56 +00001202static void minMaxFinalize(sqlite3_context *context){
danielk197788208052004-05-25 01:13:20 +00001203 sqlite3_value *pRes;
drhabfcea22005-09-06 20:36:48 +00001204 pRes = (sqlite3_value *)sqlite3_aggregate_context(context, 0);
1205 if( pRes ){
1206 if( pRes->flags ){
1207 sqlite3_result_value(context, pRes);
1208 }
1209 sqlite3VdbeMemRelease(pRes);
drh0bce8352002-02-28 00:41:10 +00001210 }
1211}
drhdd5baa92002-02-27 19:50:59 +00001212
drhb0689692007-11-01 17:38:30 +00001213/*
1214** group_concat(EXPR, ?SEPARATOR?)
1215*/
1216static void groupConcatStep(
1217 sqlite3_context *context,
1218 int argc,
1219 sqlite3_value **argv
1220){
1221 const char *zVal;
drhade86482007-11-28 22:36:40 +00001222 StrAccum *pAccum;
drhb0689692007-11-01 17:38:30 +00001223 const char *zSep;
drh07d31172009-02-02 21:57:05 +00001224 int nVal, nSep;
1225 assert( argc==1 || argc==2 );
1226 if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
drhade86482007-11-28 22:36:40 +00001227 pAccum = (StrAccum*)sqlite3_aggregate_context(context, sizeof(*pAccum));
1228
1229 if( pAccum ){
drhbb4957f2008-03-20 14:03:29 +00001230 sqlite3 *db = sqlite3_context_db_handle(context);
drhade86482007-11-28 22:36:40 +00001231 pAccum->useMalloc = 1;
drhbb4957f2008-03-20 14:03:29 +00001232 pAccum->mxAlloc = db->aLimit[SQLITE_LIMIT_LENGTH];
drhade86482007-11-28 22:36:40 +00001233 if( pAccum->nChar ){
drh07d31172009-02-02 21:57:05 +00001234 if( argc==2 ){
1235 zSep = (char*)sqlite3_value_text(argv[1]);
1236 nSep = sqlite3_value_bytes(argv[1]);
drhb0689692007-11-01 17:38:30 +00001237 }else{
1238 zSep = ",";
drhade86482007-11-28 22:36:40 +00001239 nSep = 1;
drhb0689692007-11-01 17:38:30 +00001240 }
drhade86482007-11-28 22:36:40 +00001241 sqlite3StrAccumAppend(pAccum, zSep, nSep);
drhb0689692007-11-01 17:38:30 +00001242 }
drh07d31172009-02-02 21:57:05 +00001243 zVal = (char*)sqlite3_value_text(argv[0]);
1244 nVal = sqlite3_value_bytes(argv[0]);
1245 sqlite3StrAccumAppend(pAccum, zVal, nVal);
drhb0689692007-11-01 17:38:30 +00001246 }
1247}
1248static void groupConcatFinalize(sqlite3_context *context){
drhade86482007-11-28 22:36:40 +00001249 StrAccum *pAccum;
1250 pAccum = sqlite3_aggregate_context(context, 0);
1251 if( pAccum ){
1252 if( pAccum->tooBig ){
1253 sqlite3_result_error_toobig(context);
1254 }else if( pAccum->mallocFailed ){
1255 sqlite3_result_error_nomem(context);
1256 }else{
1257 sqlite3_result_text(context, sqlite3StrAccumFinish(pAccum), -1,
1258 sqlite3_free);
1259 }
drhb0689692007-11-01 17:38:30 +00001260 }
1261}
drh4e5ffc52004-08-31 00:52:37 +00001262
drhd3a149e2002-02-24 17:12:53 +00001263/*
drha2ed5602002-02-26 23:55:31 +00001264** This function registered all of the above C functions as SQL
1265** functions. This should be the only routine in this file with
1266** external linkage.
drhdc04c582002-02-24 01:55:15 +00001267*/
drh9bb575f2004-09-06 17:24:11 +00001268void sqlite3RegisterBuiltinFunctions(sqlite3 *db){
drh1f01ec12005-02-15 21:36:18 +00001269#ifndef SQLITE_OMIT_ALTERTABLE
1270 sqlite3AlterFunctions(db);
1271#endif
danielk19771e536952007-08-16 10:09:01 +00001272 if( !db->mallocFailed ){
danielk1977832a58a2007-06-22 15:21:15 +00001273 int rc = sqlite3_overload_function(db, "MATCH", 2);
1274 assert( rc==SQLITE_NOMEM || rc==SQLITE_OK );
1275 if( rc==SQLITE_NOMEM ){
danielk19771e536952007-08-16 10:09:01 +00001276 db->mallocFailed = 1;
danielk1977832a58a2007-06-22 15:21:15 +00001277 }
1278 }
danielk1977fd9e1f32005-05-22 10:44:34 +00001279#ifdef SQLITE_SSE
drh37527852006-03-16 16:19:56 +00001280 (void)sqlite3SseFunctions(db);
danielk1977fd9e1f32005-05-22 10:44:34 +00001281#endif
drh55ef4d92005-08-14 01:20:37 +00001282}
1283
1284/*
1285** Set the LIKEOPT flag on the 2-argument function with the given name.
1286*/
drh1bd10f82008-12-10 21:19:56 +00001287static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){
drh55ef4d92005-08-14 01:20:37 +00001288 FuncDef *pDef;
drhea678832008-12-10 19:26:22 +00001289 pDef = sqlite3FindFunction(db, zName, sqlite3Strlen30(zName),
1290 2, SQLITE_UTF8, 0);
drh55ef4d92005-08-14 01:20:37 +00001291 if( pDef ){
drhd64fe2f2005-08-28 17:00:23 +00001292 pDef->flags = flagVal;
drh55ef4d92005-08-14 01:20:37 +00001293 }
1294}
1295
1296/*
1297** Register the built-in LIKE and GLOB functions. The caseSensitive
1298** parameter determines whether or not the LIKE operator is case
1299** sensitive. GLOB is always case sensitive.
1300*/
1301void sqlite3RegisterLikeFunctions(sqlite3 *db, int caseSensitive){
1302 struct compareInfo *pInfo;
1303 if( caseSensitive ){
1304 pInfo = (struct compareInfo*)&likeInfoAlt;
1305 }else{
1306 pInfo = (struct compareInfo*)&likeInfoNorm;
1307 }
danielk1977717811c2009-03-27 15:26:03 +00001308 sqlite3CreateFunc(db, "like", 2, SQLITE_ANY, pInfo, likeFunc, 0, 0);
1309 sqlite3CreateFunc(db, "like", 3, SQLITE_ANY, pInfo, likeFunc, 0, 0);
1310 sqlite3CreateFunc(db, "glob", 2, SQLITE_ANY,
drh55ef4d92005-08-14 01:20:37 +00001311 (struct compareInfo*)&globInfo, likeFunc, 0,0);
drhd64fe2f2005-08-28 17:00:23 +00001312 setLikeOptFlag(db, "glob", SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE);
1313 setLikeOptFlag(db, "like",
1314 caseSensitive ? (SQLITE_FUNC_LIKE | SQLITE_FUNC_CASE) : SQLITE_FUNC_LIKE);
drh55ef4d92005-08-14 01:20:37 +00001315}
1316
1317/*
1318** pExpr points to an expression which implements a function. If
1319** it is appropriate to apply the LIKE optimization to that function
1320** then set aWc[0] through aWc[2] to the wildcard characters and
1321** return TRUE. If the function is not a LIKE-style function then
1322** return FALSE.
1323*/
drhd64fe2f2005-08-28 17:00:23 +00001324int sqlite3IsLikeFunction(sqlite3 *db, Expr *pExpr, int *pIsNocase, char *aWc){
drh55ef4d92005-08-14 01:20:37 +00001325 FuncDef *pDef;
danielk19776ab3a2e2009-02-19 14:39:25 +00001326 if( pExpr->op!=TK_FUNCTION
1327 || !pExpr->x.pList
1328 || pExpr->x.pList->nExpr!=2
1329 ){
drh55ef4d92005-08-14 01:20:37 +00001330 return 0;
1331 }
danielk19776ab3a2e2009-02-19 14:39:25 +00001332 assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
drh2646da72005-12-09 20:02:05 +00001333 pDef = sqlite3FindFunction(db, (char*)pExpr->token.z, pExpr->token.n, 2,
drh55ef4d92005-08-14 01:20:37 +00001334 SQLITE_UTF8, 0);
drhd64fe2f2005-08-28 17:00:23 +00001335 if( pDef==0 || (pDef->flags & SQLITE_FUNC_LIKE)==0 ){
drh55ef4d92005-08-14 01:20:37 +00001336 return 0;
1337 }
1338
1339 /* The memcpy() statement assumes that the wildcard characters are
1340 ** the first three statements in the compareInfo structure. The
1341 ** asserts() that follow verify that assumption
1342 */
1343 memcpy(aWc, pDef->pUserData, 3);
1344 assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll );
1345 assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne );
1346 assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet );
drhd64fe2f2005-08-28 17:00:23 +00001347 *pIsNocase = (pDef->flags & SQLITE_FUNC_CASE)==0;
drh55ef4d92005-08-14 01:20:37 +00001348 return 1;
drhdc04c582002-02-24 01:55:15 +00001349}
danielk19778c0a7912008-08-20 14:49:23 +00001350
drh70a8ca32008-08-21 18:49:27 +00001351/*
drh777c5382008-08-21 20:21:34 +00001352** All all of the FuncDef structures in the aBuiltinFunc[] array above
1353** to the global function hash table. This occurs at start-time (as
1354** a consequence of calling sqlite3_initialize()).
1355**
1356** After this routine runs
drh70a8ca32008-08-21 18:49:27 +00001357*/
1358void sqlite3RegisterGlobalFunctions(void){
danielk197793ce7412008-09-01 19:14:02 +00001359 /*
1360 ** The following array holds FuncDef structures for all of the functions
1361 ** defined in this file.
1362 **
1363 ** The array cannot be constant since changes are made to the
1364 ** FuncDef.pHash elements at start-time. The elements of this array
1365 ** are read-only after initialization is complete.
1366 */
1367 static SQLITE_WSD FuncDef aBuiltinFunc[] = {
1368 FUNCTION(ltrim, 1, 1, 0, trimFunc ),
1369 FUNCTION(ltrim, 2, 1, 0, trimFunc ),
1370 FUNCTION(rtrim, 1, 2, 0, trimFunc ),
1371 FUNCTION(rtrim, 2, 2, 0, trimFunc ),
1372 FUNCTION(trim, 1, 3, 0, trimFunc ),
1373 FUNCTION(trim, 2, 3, 0, trimFunc ),
1374 FUNCTION(min, -1, 0, 1, minmaxFunc ),
1375 FUNCTION(min, 0, 0, 1, 0 ),
1376 AGGREGATE(min, 1, 0, 1, minmaxStep, minMaxFinalize ),
1377 FUNCTION(max, -1, 1, 1, minmaxFunc ),
1378 FUNCTION(max, 0, 1, 1, 0 ),
1379 AGGREGATE(max, 1, 1, 1, minmaxStep, minMaxFinalize ),
1380 FUNCTION(typeof, 1, 0, 0, typeofFunc ),
1381 FUNCTION(length, 1, 0, 0, lengthFunc ),
1382 FUNCTION(substr, 2, 0, 0, substrFunc ),
1383 FUNCTION(substr, 3, 0, 0, substrFunc ),
1384 FUNCTION(abs, 1, 0, 0, absFunc ),
shanefbd60f82009-02-04 03:59:25 +00001385#ifndef SQLITE_OMIT_FLOATING_POINT
danielk197793ce7412008-09-01 19:14:02 +00001386 FUNCTION(round, 1, 0, 0, roundFunc ),
1387 FUNCTION(round, 2, 0, 0, roundFunc ),
shanefbd60f82009-02-04 03:59:25 +00001388#endif
danielk197793ce7412008-09-01 19:14:02 +00001389 FUNCTION(upper, 1, 0, 0, upperFunc ),
1390 FUNCTION(lower, 1, 0, 0, lowerFunc ),
1391 FUNCTION(coalesce, 1, 0, 0, 0 ),
1392 FUNCTION(coalesce, -1, 0, 0, ifnullFunc ),
1393 FUNCTION(coalesce, 0, 0, 0, 0 ),
1394 FUNCTION(hex, 1, 0, 0, hexFunc ),
1395 FUNCTION(ifnull, 2, 0, 1, ifnullFunc ),
drh9373b012009-02-02 01:50:39 +00001396 FUNCTION(random, 0, 0, 0, randomFunc ),
danielk197793ce7412008-09-01 19:14:02 +00001397 FUNCTION(randomblob, 1, 0, 0, randomBlob ),
1398 FUNCTION(nullif, 2, 0, 1, nullifFunc ),
1399 FUNCTION(sqlite_version, 0, 0, 0, versionFunc ),
1400 FUNCTION(quote, 1, 0, 0, quoteFunc ),
1401 FUNCTION(last_insert_rowid, 0, 0, 0, last_insert_rowid),
1402 FUNCTION(changes, 0, 0, 0, changes ),
1403 FUNCTION(total_changes, 0, 0, 0, total_changes ),
1404 FUNCTION(replace, 3, 0, 0, replaceFunc ),
1405 FUNCTION(zeroblob, 1, 0, 0, zeroblobFunc ),
1406 #ifdef SQLITE_SOUNDEX
1407 FUNCTION(soundex, 1, 0, 0, soundexFunc ),
1408 #endif
1409 #ifndef SQLITE_OMIT_LOAD_EXTENSION
1410 FUNCTION(load_extension, 1, 0, 0, loadExt ),
1411 FUNCTION(load_extension, 2, 0, 0, loadExt ),
1412 #endif
1413 AGGREGATE(sum, 1, 0, 0, sumStep, sumFinalize ),
1414 AGGREGATE(total, 1, 0, 0, sumStep, totalFinalize ),
1415 AGGREGATE(avg, 1, 0, 0, sumStep, avgFinalize ),
danielk1977a5533162009-02-24 10:01:51 +00001416 /* AGGREGATE(count, 0, 0, 0, countStep, countFinalize ), */
1417 {0,SQLITE_UTF8,SQLITE_FUNC_COUNT,0,0,0,countStep,countFinalize,"count",0},
danielk197793ce7412008-09-01 19:14:02 +00001418 AGGREGATE(count, 1, 0, 0, countStep, countFinalize ),
drh07d31172009-02-02 21:57:05 +00001419 AGGREGATE(group_concat, 1, 0, 0, groupConcatStep, groupConcatFinalize),
1420 AGGREGATE(group_concat, 2, 0, 0, groupConcatStep, groupConcatFinalize),
danielk197793ce7412008-09-01 19:14:02 +00001421
1422 LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
1423 #ifdef SQLITE_CASE_SENSITIVE_LIKE
1424 LIKEFUNC(like, 2, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
1425 LIKEFUNC(like, 3, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
1426 #else
1427 LIKEFUNC(like, 2, &likeInfoNorm, SQLITE_FUNC_LIKE),
1428 LIKEFUNC(like, 3, &likeInfoNorm, SQLITE_FUNC_LIKE),
1429 #endif
1430 };
1431
drh70a8ca32008-08-21 18:49:27 +00001432 int i;
danielk197793ce7412008-09-01 19:14:02 +00001433 FuncDefHash *pHash = &GLOBAL(FuncDefHash, sqlite3GlobalFunctions);
drh106cee52008-09-03 17:11:16 +00001434 FuncDef *aFunc = (FuncDef*)&GLOBAL(FuncDef, aBuiltinFunc);
danielk197793ce7412008-09-01 19:14:02 +00001435
drh70a8ca32008-08-21 18:49:27 +00001436 for(i=0; i<ArraySize(aBuiltinFunc); i++){
danielk197793ce7412008-09-01 19:14:02 +00001437 sqlite3FuncDefInsert(pHash, &aFunc[i]);
drh70a8ca32008-08-21 18:49:27 +00001438 }
drh777c5382008-08-21 20:21:34 +00001439 sqlite3RegisterDateTimeFunctions();
drh70a8ca32008-08-21 18:49:27 +00001440}