drh | dc04c58 | 2002-02-24 01:55:15 +0000 | [diff] [blame] | 1 | /* |
| 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 | ** |
drh | 3212e18 | 2002-02-28 00:46:26 +0000 | [diff] [blame^] | 19 | ** $Id: func.c,v 1.7 2002/02/28 00:46:26 drh Exp $ |
drh | dc04c58 | 2002-02-24 01:55:15 +0000 | [diff] [blame] | 20 | */ |
| 21 | #include <ctype.h> |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 22 | #include <math.h> |
| 23 | #include <stdlib.h> |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 24 | #include <assert.h> |
| 25 | #include "sqliteInt.h" |
| 26 | |
| 27 | /* |
| 28 | ** Implementation of the non-aggregate min() and max() functions |
| 29 | */ |
| 30 | static void minFunc(sqlite_func *context, int argc, const char **argv){ |
| 31 | const char *zBest; |
| 32 | int i; |
| 33 | |
| 34 | zBest = argv[0]; |
| 35 | for(i=1; i<argc; i++){ |
| 36 | if( sqliteCompare(argv[i], zBest)<0 ){ |
| 37 | zBest = argv[i]; |
| 38 | } |
| 39 | } |
| 40 | sqlite_set_result_string(context, zBest, -1); |
| 41 | } |
| 42 | static void maxFunc(sqlite_func *context, int argc, const char **argv){ |
| 43 | const char *zBest; |
| 44 | int i; |
| 45 | |
| 46 | zBest = argv[0]; |
| 47 | for(i=1; i<argc; i++){ |
| 48 | if( sqliteCompare(argv[i], zBest)>0 ){ |
| 49 | zBest = argv[i]; |
| 50 | } |
| 51 | } |
| 52 | sqlite_set_result_string(context, zBest, -1); |
| 53 | } |
| 54 | |
| 55 | /* |
| 56 | ** Implementation of the length() function |
| 57 | */ |
| 58 | static void lengthFunc(sqlite_func *context, int argc, const char **argv){ |
| 59 | const char *z; |
| 60 | int len; |
| 61 | |
| 62 | assert( argc==1 ); |
| 63 | z = argv[0]; |
| 64 | if( z==0 ){ |
| 65 | len = 0; |
| 66 | }else{ |
| 67 | #ifdef SQLITE_UTF8 |
| 68 | for(len=0; *z; z++){ if( (0xc0&*z)!=0x80 ) len++; } |
| 69 | #else |
| 70 | len = strlen(z); |
| 71 | #endif |
| 72 | } |
| 73 | sqlite_set_result_int(context, len); |
| 74 | } |
| 75 | |
| 76 | /* |
| 77 | ** Implementation of the abs() function |
| 78 | */ |
| 79 | static void absFunc(sqlite_func *context, int argc, const char **argv){ |
| 80 | const char *z; |
| 81 | assert( argc==1 ); |
| 82 | z = argv[0]; |
| 83 | if( z && z[0]=='-' && isdigit(z[1]) ) z++; |
| 84 | sqlite_set_result_string(context, z, -1); |
| 85 | } |
| 86 | |
| 87 | /* |
| 88 | ** Implementation of the substr() function |
| 89 | */ |
| 90 | static void substrFunc(sqlite_func *context, int argc, const char **argv){ |
| 91 | const char *z; |
| 92 | #ifdef SQLITE_UTF8 |
| 93 | const char *z2; |
| 94 | int i; |
| 95 | #endif |
| 96 | int p1, p2, len; |
| 97 | assert( argc==3 ); |
| 98 | z = argv[0]; |
| 99 | if( z==0 ) return; |
| 100 | p1 = atoi(argv[1]?argv[1]:0); |
| 101 | p2 = atoi(argv[2]?argv[2]:0); |
| 102 | #ifdef SQLITE_UTF8 |
| 103 | for(len=0, z2=z; *z2; z2++){ if( (0xc0&*z)!=0x80 ) len++; } |
| 104 | #else |
| 105 | len = strlen(z); |
| 106 | #endif |
| 107 | if( p1<0 ){ |
| 108 | p1 = len-p1; |
| 109 | }else if( p1>0 ){ |
| 110 | p1--; |
| 111 | } |
| 112 | if( p1+p2>len ){ |
| 113 | p2 = len-p1; |
| 114 | } |
| 115 | #ifdef SQLITE_UTF8 |
| 116 | for(i=0; i<p1; i++){ |
| 117 | assert( z[i] ); |
| 118 | if( (z[i]&0xc0)!=0x80 ) p1++; |
| 119 | } |
| 120 | for(; i<p1+p2; i++){ |
| 121 | assert( z[i] ); |
| 122 | if( (z[i]&0xc0)!=0x80 ) p2++; |
| 123 | } |
| 124 | #endif |
| 125 | sqlite_set_result_string(context, &z[p1], p2); |
| 126 | } |
| 127 | |
| 128 | /* |
| 129 | ** Implementation of the round() function |
| 130 | */ |
| 131 | static void roundFunc(sqlite_func *context, int argc, const char **argv){ |
| 132 | int n; |
| 133 | double r; |
| 134 | char zBuf[100]; |
| 135 | assert( argc==1 || argc==2 ); |
| 136 | n = argc==2 && argv[1] ? atoi(argv[1]) : 0; |
| 137 | if( n>30 ) n = 30; |
| 138 | if( n<0 ) n = 0; |
| 139 | r = argv[0] ? atof(argv[0]) : 0.0; |
| 140 | sprintf(zBuf,"%.*f",n,r); |
| 141 | sqlite_set_result_string(context, zBuf, -1); |
| 142 | } |
drh | dc04c58 | 2002-02-24 01:55:15 +0000 | [diff] [blame] | 143 | |
| 144 | /* |
| 145 | ** Implementation of the upper() and lower() SQL functions. |
| 146 | */ |
drh | 1350b03 | 2002-02-27 19:00:20 +0000 | [diff] [blame] | 147 | static void upperFunc(sqlite_func *context, int argc, const char **argv){ |
drh | dc04c58 | 2002-02-24 01:55:15 +0000 | [diff] [blame] | 148 | char *z; |
| 149 | int i; |
| 150 | if( argc<1 || argv[0]==0 ) return; |
| 151 | z = sqlite_set_result_string(context, argv[0], -1); |
| 152 | if( z==0 ) return; |
| 153 | for(i=0; z[i]; i++){ |
| 154 | if( islower(z[i]) ) z[i] = toupper(z[i]); |
| 155 | } |
| 156 | } |
drh | 1350b03 | 2002-02-27 19:00:20 +0000 | [diff] [blame] | 157 | static void lowerFunc(sqlite_func *context, int argc, const char **argv){ |
drh | dc04c58 | 2002-02-24 01:55:15 +0000 | [diff] [blame] | 158 | char *z; |
| 159 | int i; |
| 160 | if( argc<1 || argv[0]==0 ) return; |
| 161 | z = sqlite_set_result_string(context, argv[0], -1); |
| 162 | if( z==0 ) return; |
| 163 | for(i=0; z[i]; i++){ |
| 164 | if( isupper(z[i]) ) z[i] = tolower(z[i]); |
| 165 | } |
| 166 | } |
| 167 | |
| 168 | /* |
drh | 3212e18 | 2002-02-28 00:46:26 +0000 | [diff] [blame^] | 169 | ** Implementation of the IFNULL() and NVL() functions. (both do the |
| 170 | ** same thing. They return their first argument if it is not NULL or |
| 171 | ** their second argument if the first is NULL. |
| 172 | */ |
| 173 | static void ifnullFunc(sqlite_func *context, int argc, const char **argv){ |
| 174 | const char *z; |
| 175 | assert( argc==2 ); |
| 176 | z = argv[0] ? argv[0] : argv[1]; |
| 177 | sqlite_set_result_string(context, z, -1); |
| 178 | } |
| 179 | |
| 180 | /* |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 181 | ** An instance of the following structure holds the context of a |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 182 | ** sum() or avg() aggregate computation. |
| 183 | */ |
| 184 | typedef struct SumCtx SumCtx; |
| 185 | struct SumCtx { |
| 186 | double sum; /* Sum of terms */ |
| 187 | }; |
| 188 | |
| 189 | /* |
| 190 | ** Routines used to compute the sum or average. |
| 191 | */ |
| 192 | static void sumStep(sqlite_func *context, int argc, const char **argv){ |
| 193 | SumCtx *p; |
| 194 | double x; |
| 195 | if( argc<1 ) return; |
| 196 | p = sqlite_aggregate_context(context, sizeof(*p)); |
| 197 | if( p==0 ) return; |
| 198 | x = argv[0] ? atof(argv[0]) : 0.0; |
| 199 | p->sum += x; |
| 200 | } |
| 201 | static void sumFinalize(sqlite_func *context){ |
| 202 | SumCtx *p; |
| 203 | p = sqlite_aggregate_context(context, sizeof(*p)); |
| 204 | if( p ){ |
| 205 | sqlite_set_result_double(context, p->sum); |
| 206 | } |
| 207 | } |
| 208 | static void avgFinalize(sqlite_func *context){ |
| 209 | SumCtx *p; |
| 210 | double rN; |
| 211 | p = sqlite_aggregate_context(context, sizeof(*p)); |
| 212 | rN = sqlite_aggregate_count(context); |
| 213 | if( p && rN>0.0 ){ |
| 214 | sqlite_set_result_double(context, p->sum/rN); |
| 215 | } |
| 216 | } |
| 217 | |
| 218 | /* |
| 219 | ** An instance of the following structure holds the context of a |
drh | a2ed560 | 2002-02-26 23:55:31 +0000 | [diff] [blame] | 220 | ** variance or standard deviation computation. |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 221 | */ |
| 222 | typedef struct StdDevCtx StdDevCtx; |
| 223 | struct StdDevCtx { |
| 224 | double sum; /* Sum of terms */ |
| 225 | double sum2; /* Sum of the squares of terms */ |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 226 | }; |
| 227 | |
| 228 | /* |
| 229 | ** Routines used to compute the standard deviation as an aggregate. |
| 230 | */ |
drh | 1350b03 | 2002-02-27 19:00:20 +0000 | [diff] [blame] | 231 | static void stdDevStep(sqlite_func *context, int argc, const char **argv){ |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 232 | StdDevCtx *p; |
| 233 | double x; |
drh | 1350b03 | 2002-02-27 19:00:20 +0000 | [diff] [blame] | 234 | if( argc<1 ) return; |
| 235 | p = sqlite_aggregate_context(context, sizeof(*p)); |
| 236 | if( p==0 ) return; |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 237 | x = argv[0] ? atof(argv[0]) : 0.0; |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 238 | p->sum += x; |
| 239 | p->sum2 += x*x; |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 240 | } |
drh | 1350b03 | 2002-02-27 19:00:20 +0000 | [diff] [blame] | 241 | static void stdDevFinalize(sqlite_func *context){ |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 242 | double rN = sqlite_aggregate_count(context); |
drh | 1350b03 | 2002-02-27 19:00:20 +0000 | [diff] [blame] | 243 | StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p)); |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 244 | if( p && rN>1.0 ){ |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 245 | sqlite_set_result_double(context, |
| 246 | sqrt((p->sum2 - p->sum*p->sum/rN)/(rN-1.0))); |
| 247 | } |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 248 | } |
| 249 | |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 250 | /* |
| 251 | ** The following structure keeps track of state information for the |
| 252 | ** count() aggregate function. |
| 253 | */ |
| 254 | typedef struct CountCtx CountCtx; |
| 255 | struct CountCtx { |
| 256 | int n; |
| 257 | }; |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 258 | |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 259 | /* |
| 260 | ** Routines to implement the count() aggregate function. |
| 261 | */ |
| 262 | static void countStep(sqlite_func *context, int argc, const char **argv){ |
| 263 | CountCtx *p; |
| 264 | p = sqlite_aggregate_context(context, sizeof(*p)); |
| 265 | if( (argc==0 || argv[0]) && p ){ |
| 266 | p->n++; |
| 267 | } |
| 268 | } |
| 269 | static void countFinalize(sqlite_func *context){ |
| 270 | CountCtx *p; |
| 271 | p = sqlite_aggregate_context(context, sizeof(*p)); |
| 272 | if( p ){ |
| 273 | sqlite_set_result_int(context, p->n); |
| 274 | } |
| 275 | } |
| 276 | |
| 277 | /* |
| 278 | ** This function tracks state information for the min() and max() |
| 279 | ** aggregate functions. |
| 280 | */ |
| 281 | typedef struct MinMaxCtx MinMaxCtx; |
| 282 | struct MinMaxCtx { |
| 283 | char *z; /* The best so far */ |
| 284 | char zBuf[28]; /* Space that can be used for storage */ |
| 285 | }; |
| 286 | |
| 287 | /* |
| 288 | ** Routines to implement min() and max() aggregate functions. |
| 289 | */ |
| 290 | static void minStep(sqlite_func *context, int argc, const char **argv){ |
| 291 | MinMaxCtx *p; |
| 292 | p = sqlite_aggregate_context(context, sizeof(*p)); |
| 293 | if( p==0 || argc<1 ) return; |
| 294 | if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)<0 ){ |
| 295 | if( p->z && p->z!=p->zBuf ){ |
| 296 | sqliteFree(p->z); |
| 297 | } |
| 298 | if( argv[0] ){ |
| 299 | int len = strlen(argv[0]); |
| 300 | if( len < sizeof(p->zBuf) ){ |
| 301 | p->z = p->zBuf; |
| 302 | }else{ |
| 303 | p->z = sqliteMalloc( len+1 ); |
| 304 | if( p->z==0 ) return; |
| 305 | } |
| 306 | strcpy(p->z, argv[0]); |
| 307 | }else{ |
| 308 | p->z = 0; |
| 309 | } |
| 310 | } |
| 311 | } |
| 312 | static void maxStep(sqlite_func *context, int argc, const char **argv){ |
| 313 | MinMaxCtx *p; |
| 314 | p = sqlite_aggregate_context(context, sizeof(*p)); |
| 315 | if( p==0 || argc<1 ) return; |
| 316 | if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)>0 ){ |
| 317 | if( p->z && p->z!=p->zBuf ){ |
| 318 | sqliteFree(p->z); |
| 319 | } |
| 320 | if( argv[0] ){ |
| 321 | int len = strlen(argv[0]); |
| 322 | if( len < sizeof(p->zBuf) ){ |
| 323 | p->z = p->zBuf; |
| 324 | }else{ |
| 325 | p->z = sqliteMalloc( len+1 ); |
| 326 | if( p->z==0 ) return; |
| 327 | } |
| 328 | strcpy(p->z, argv[0]); |
| 329 | }else{ |
| 330 | p->z = 0; |
| 331 | } |
| 332 | } |
| 333 | } |
| 334 | static void minMaxFinalize(sqlite_func *context){ |
| 335 | MinMaxCtx *p; |
| 336 | p = sqlite_aggregate_context(context, sizeof(*p)); |
| 337 | if( p && p->z ){ |
| 338 | sqlite_set_result_string(context, p->z, strlen(p->z)); |
| 339 | } |
| 340 | if( p && p->z && p->z!=p->zBuf ){ |
| 341 | sqliteFree(p->z); |
| 342 | } |
| 343 | } |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 344 | |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 345 | /* |
drh | a2ed560 | 2002-02-26 23:55:31 +0000 | [diff] [blame] | 346 | ** This function registered all of the above C functions as SQL |
| 347 | ** functions. This should be the only routine in this file with |
| 348 | ** external linkage. |
drh | dc04c58 | 2002-02-24 01:55:15 +0000 | [diff] [blame] | 349 | */ |
| 350 | void sqliteRegisterBuildinFunctions(sqlite *db){ |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 351 | static struct { |
| 352 | char *zName; |
| 353 | int nArg; |
| 354 | void (*xFunc)(sqlite_func*,int,const char**); |
| 355 | } aFuncs[] = { |
| 356 | { "min", -1, minFunc }, |
| 357 | { "max", -1, maxFunc }, |
| 358 | { "length", 1, lengthFunc }, |
| 359 | { "substr", 3, substrFunc }, |
| 360 | { "abs", 1, absFunc }, |
| 361 | { "round", 1, roundFunc }, |
| 362 | { "round", 2, roundFunc }, |
| 363 | { "upper", 1, upperFunc }, |
| 364 | { "lower", 1, lowerFunc }, |
drh | 3212e18 | 2002-02-28 00:46:26 +0000 | [diff] [blame^] | 365 | { "ifnull", 2, ifnullFunc }, |
| 366 | { "nvl", 2, ifnullFunc }, |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 367 | }; |
| 368 | static struct { |
| 369 | char *zName; |
| 370 | int nArg; |
| 371 | void (*xStep)(sqlite_func*,int,const char**); |
| 372 | void (*xFinalize)(sqlite_func*); |
| 373 | } aAggs[] = { |
| 374 | { "min", 1, minStep, minMaxFinalize }, |
| 375 | { "max", 1, maxStep, minMaxFinalize }, |
| 376 | { "sum", 1, sumStep, sumFinalize }, |
| 377 | { "avg", 1, sumStep, avgFinalize }, |
| 378 | { "count", 0, countStep, countFinalize }, |
| 379 | { "count", 1, countStep, countFinalize }, |
| 380 | { "stddev", 1, stdDevStep, stdDevFinalize }, |
| 381 | }; |
| 382 | int i; |
| 383 | |
| 384 | for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ |
| 385 | sqlite_create_function(db, aFuncs[i].zName, |
| 386 | aFuncs[i].nArg, aFuncs[i].xFunc, 0); |
| 387 | } |
| 388 | for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){ |
| 389 | sqlite_create_aggregate(db, aAggs[i].zName, |
| 390 | aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, 0); |
| 391 | } |
drh | dc04c58 | 2002-02-24 01:55:15 +0000 | [diff] [blame] | 392 | } |