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 | 7014aff | 2003-11-01 01:53:53 +0000 | [diff] [blame^] | 19 | ** $Id: func.c,v 1.33 2003/11/01 01:53:54 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" |
drh | 771d8c3 | 2003-08-09 21:32:28 +0000 | [diff] [blame] | 26 | #include "os.h" |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 27 | |
| 28 | /* |
| 29 | ** Implementation of the non-aggregate min() and max() functions |
| 30 | */ |
| 31 | static void minFunc(sqlite_func *context, int argc, const char **argv){ |
| 32 | const char *zBest; |
| 33 | int i; |
| 34 | |
drh | 89425d5 | 2002-02-28 03:04:48 +0000 | [diff] [blame] | 35 | if( argc==0 ) return; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 36 | zBest = argv[0]; |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 37 | if( zBest==0 ) return; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 38 | for(i=1; i<argc; i++){ |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 39 | if( argv[i]==0 ) return; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 40 | if( sqliteCompare(argv[i], zBest)<0 ){ |
| 41 | zBest = argv[i]; |
| 42 | } |
| 43 | } |
| 44 | sqlite_set_result_string(context, zBest, -1); |
| 45 | } |
| 46 | static void maxFunc(sqlite_func *context, int argc, const char **argv){ |
| 47 | const char *zBest; |
| 48 | int i; |
| 49 | |
drh | 89425d5 | 2002-02-28 03:04:48 +0000 | [diff] [blame] | 50 | if( argc==0 ) return; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 51 | zBest = argv[0]; |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 52 | if( zBest==0 ) return; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 53 | for(i=1; i<argc; i++){ |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 54 | if( argv[i]==0 ) return; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 55 | if( sqliteCompare(argv[i], zBest)>0 ){ |
| 56 | zBest = argv[i]; |
| 57 | } |
| 58 | } |
| 59 | sqlite_set_result_string(context, zBest, -1); |
| 60 | } |
| 61 | |
| 62 | /* |
| 63 | ** Implementation of the length() function |
| 64 | */ |
| 65 | static void lengthFunc(sqlite_func *context, int argc, const char **argv){ |
| 66 | const char *z; |
| 67 | int len; |
| 68 | |
| 69 | assert( argc==1 ); |
| 70 | z = argv[0]; |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 71 | if( z==0 ) return; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 72 | #ifdef SQLITE_UTF8 |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 73 | for(len=0; *z; z++){ if( (0xc0&*z)!=0x80 ) len++; } |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 74 | #else |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 75 | len = strlen(z); |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 76 | #endif |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 77 | sqlite_set_result_int(context, len); |
| 78 | } |
| 79 | |
| 80 | /* |
| 81 | ** Implementation of the abs() function |
| 82 | */ |
| 83 | static void absFunc(sqlite_func *context, int argc, const char **argv){ |
| 84 | const char *z; |
| 85 | assert( argc==1 ); |
| 86 | z = argv[0]; |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 87 | if( z==0 ) return; |
| 88 | if( z[0]=='-' && isdigit(z[1]) ) z++; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 89 | sqlite_set_result_string(context, z, -1); |
| 90 | } |
| 91 | |
| 92 | /* |
| 93 | ** Implementation of the substr() function |
| 94 | */ |
| 95 | static void substrFunc(sqlite_func *context, int argc, const char **argv){ |
| 96 | const char *z; |
| 97 | #ifdef SQLITE_UTF8 |
| 98 | const char *z2; |
| 99 | int i; |
| 100 | #endif |
| 101 | int p1, p2, len; |
| 102 | assert( argc==3 ); |
| 103 | z = argv[0]; |
| 104 | if( z==0 ) return; |
| 105 | p1 = atoi(argv[1]?argv[1]:0); |
| 106 | p2 = atoi(argv[2]?argv[2]:0); |
| 107 | #ifdef SQLITE_UTF8 |
drh | 47c8a67 | 2002-02-28 04:00:12 +0000 | [diff] [blame] | 108 | for(len=0, z2=z; *z2; z2++){ if( (0xc0&*z2)!=0x80 ) len++; } |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 109 | #else |
| 110 | len = strlen(z); |
| 111 | #endif |
| 112 | if( p1<0 ){ |
drh | 89425d5 | 2002-02-28 03:04:48 +0000 | [diff] [blame] | 113 | p1 += len; |
drh | 653bc75 | 2002-02-28 03:31:10 +0000 | [diff] [blame] | 114 | if( p1<0 ){ |
| 115 | p2 += p1; |
| 116 | p1 = 0; |
| 117 | } |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 118 | }else if( p1>0 ){ |
| 119 | p1--; |
| 120 | } |
| 121 | if( p1+p2>len ){ |
| 122 | p2 = len-p1; |
| 123 | } |
| 124 | #ifdef SQLITE_UTF8 |
| 125 | for(i=0; i<p1; i++){ |
| 126 | assert( z[i] ); |
drh | 47c8a67 | 2002-02-28 04:00:12 +0000 | [diff] [blame] | 127 | if( (z[i]&0xc0)==0x80 ) p1++; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 128 | } |
drh | 47c8a67 | 2002-02-28 04:00:12 +0000 | [diff] [blame] | 129 | while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p1++; } |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 130 | for(; i<p1+p2; i++){ |
| 131 | assert( z[i] ); |
drh | 47c8a67 | 2002-02-28 04:00:12 +0000 | [diff] [blame] | 132 | if( (z[i]&0xc0)==0x80 ) p2++; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 133 | } |
drh | 47c8a67 | 2002-02-28 04:00:12 +0000 | [diff] [blame] | 134 | while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p2++; } |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 135 | #endif |
drh | 653bc75 | 2002-02-28 03:31:10 +0000 | [diff] [blame] | 136 | if( p2<0 ) p2 = 0; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 137 | sqlite_set_result_string(context, &z[p1], p2); |
| 138 | } |
| 139 | |
| 140 | /* |
| 141 | ** Implementation of the round() function |
| 142 | */ |
| 143 | static void roundFunc(sqlite_func *context, int argc, const char **argv){ |
| 144 | int n; |
| 145 | double r; |
| 146 | char zBuf[100]; |
| 147 | assert( argc==1 || argc==2 ); |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 148 | if( argv[0]==0 || (argc==2 && argv[1]==0) ) return; |
| 149 | n = argc==2 ? atoi(argv[1]) : 0; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 150 | if( n>30 ) n = 30; |
| 151 | if( n<0 ) n = 0; |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 152 | r = atof(argv[0]); |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 153 | sprintf(zBuf,"%.*f",n,r); |
| 154 | sqlite_set_result_string(context, zBuf, -1); |
| 155 | } |
drh | dc04c58 | 2002-02-24 01:55:15 +0000 | [diff] [blame] | 156 | |
| 157 | /* |
| 158 | ** Implementation of the upper() and lower() SQL functions. |
| 159 | */ |
drh | 1350b03 | 2002-02-27 19:00:20 +0000 | [diff] [blame] | 160 | static void upperFunc(sqlite_func *context, int argc, const char **argv){ |
drh | dc04c58 | 2002-02-24 01:55:15 +0000 | [diff] [blame] | 161 | char *z; |
| 162 | int i; |
| 163 | if( argc<1 || argv[0]==0 ) return; |
| 164 | z = sqlite_set_result_string(context, argv[0], -1); |
| 165 | if( z==0 ) return; |
| 166 | for(i=0; z[i]; i++){ |
| 167 | if( islower(z[i]) ) z[i] = toupper(z[i]); |
| 168 | } |
| 169 | } |
drh | 1350b03 | 2002-02-27 19:00:20 +0000 | [diff] [blame] | 170 | static void lowerFunc(sqlite_func *context, int argc, const char **argv){ |
drh | dc04c58 | 2002-02-24 01:55:15 +0000 | [diff] [blame] | 171 | char *z; |
| 172 | int i; |
| 173 | if( argc<1 || argv[0]==0 ) return; |
| 174 | z = sqlite_set_result_string(context, argv[0], -1); |
| 175 | if( z==0 ) return; |
| 176 | for(i=0; z[i]; i++){ |
| 177 | if( isupper(z[i]) ) z[i] = tolower(z[i]); |
| 178 | } |
| 179 | } |
| 180 | |
| 181 | /* |
drh | fbc9908 | 2002-02-28 03:14:18 +0000 | [diff] [blame] | 182 | ** Implementation of the IFNULL(), NVL(), and COALESCE() functions. |
| 183 | ** All three do the same thing. They return the first argument |
| 184 | ** non-NULL argument. |
drh | 3212e18 | 2002-02-28 00:46:26 +0000 | [diff] [blame] | 185 | */ |
| 186 | static void ifnullFunc(sqlite_func *context, int argc, const char **argv){ |
drh | fbc9908 | 2002-02-28 03:14:18 +0000 | [diff] [blame] | 187 | int i; |
| 188 | for(i=0; i<argc; i++){ |
| 189 | if( argv[i] ){ |
| 190 | sqlite_set_result_string(context, argv[i], -1); |
| 191 | break; |
| 192 | } |
| 193 | } |
drh | 3212e18 | 2002-02-28 00:46:26 +0000 | [diff] [blame] | 194 | } |
| 195 | |
| 196 | /* |
drh | f9ffac9 | 2002-03-02 19:00:31 +0000 | [diff] [blame] | 197 | ** Implementation of random(). Return a random integer. |
| 198 | */ |
| 199 | static void randomFunc(sqlite_func *context, int argc, const char **argv){ |
| 200 | sqlite_set_result_int(context, sqliteRandomInteger()); |
| 201 | } |
| 202 | |
| 203 | /* |
drh | 6ed41ad | 2002-04-06 14:10:47 +0000 | [diff] [blame] | 204 | ** Implementation of the last_insert_rowid() SQL function. The return |
| 205 | ** value is the same as the sqlite_last_insert_rowid() API function. |
| 206 | */ |
drh | 0ac6589 | 2002-04-20 14:24:41 +0000 | [diff] [blame] | 207 | static void last_insert_rowid(sqlite_func *context, int arg, const char **argv){ |
drh | 6ed41ad | 2002-04-06 14:10:47 +0000 | [diff] [blame] | 208 | sqlite *db = sqlite_user_data(context); |
| 209 | sqlite_set_result_int(context, sqlite_last_insert_rowid(db)); |
| 210 | } |
| 211 | |
| 212 | /* |
drh | 0ac6589 | 2002-04-20 14:24:41 +0000 | [diff] [blame] | 213 | ** Implementation of the like() SQL function. This function implements |
| 214 | ** the build-in LIKE operator. The first argument to the function is the |
| 215 | ** string and the second argument is the pattern. So, the SQL statements: |
| 216 | ** |
| 217 | ** A LIKE B |
| 218 | ** |
| 219 | ** is implemented as like(A,B). |
| 220 | */ |
| 221 | static void likeFunc(sqlite_func *context, int arg, const char **argv){ |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 222 | if( argv[0]==0 || argv[1]==0 ) return; |
drh | ec1bd0b | 2003-08-26 11:41:27 +0000 | [diff] [blame] | 223 | sqlite_set_result_int(context, |
| 224 | sqliteLikeCompare((const unsigned char*)argv[0], |
| 225 | (const unsigned char*)argv[1])); |
drh | 0ac6589 | 2002-04-20 14:24:41 +0000 | [diff] [blame] | 226 | } |
| 227 | |
| 228 | /* |
| 229 | ** Implementation of the glob() SQL function. This function implements |
| 230 | ** the build-in GLOB operator. The first argument to the function is the |
| 231 | ** string and the second argument is the pattern. So, the SQL statements: |
| 232 | ** |
| 233 | ** A GLOB B |
| 234 | ** |
| 235 | ** is implemented as glob(A,B). |
| 236 | */ |
| 237 | static void globFunc(sqlite_func *context, int arg, const char **argv){ |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 238 | if( argv[0]==0 || argv[1]==0 ) return; |
drh | ec1bd0b | 2003-08-26 11:41:27 +0000 | [diff] [blame] | 239 | sqlite_set_result_int(context, |
| 240 | sqliteGlobCompare((const unsigned char*)argv[0], |
| 241 | (const unsigned char*)argv[1])); |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 242 | } |
| 243 | |
| 244 | /* |
| 245 | ** Implementation of the NULLIF(x,y) function. The result is the first |
| 246 | ** argument if the arguments are different. The result is NULL if the |
| 247 | ** arguments are equal to each other. |
| 248 | */ |
| 249 | static void nullifFunc(sqlite_func *context, int argc, const char **argv){ |
| 250 | if( argv[0]!=0 && sqliteCompare(argv[0],argv[1])!=0 ){ |
| 251 | sqlite_set_result_string(context, argv[0], -1); |
| 252 | } |
drh | 0ac6589 | 2002-04-20 14:24:41 +0000 | [diff] [blame] | 253 | } |
| 254 | |
drh | 647cb0e | 2002-11-04 19:32:25 +0000 | [diff] [blame] | 255 | /* |
| 256 | ** Implementation of the VERSION(*) function. The result is the version |
| 257 | ** of the SQLite library that is running. |
| 258 | */ |
| 259 | static void versionFunc(sqlite_func *context, int argc, const char **argv){ |
| 260 | sqlite_set_result_string(context, sqlite_version, -1); |
| 261 | } |
| 262 | |
drh | 4739470 | 2003-08-20 01:03:33 +0000 | [diff] [blame] | 263 | /* |
| 264 | ** EXPERIMENTAL - This is not an official function. The interface may |
| 265 | ** change. This function may disappear. Do not write code that depends |
| 266 | ** on this function. |
| 267 | ** |
| 268 | ** Implementation of the QUOTE() function. This function takes a single |
| 269 | ** argument. If the argument is numeric, the return value is the same as |
| 270 | ** the argument. If the argument is NULL, the return value is the string |
| 271 | ** "NULL". Otherwise, the argument is enclosed in single quotes with |
| 272 | ** single-quote escapes. |
| 273 | */ |
| 274 | static void quoteFunc(sqlite_func *context, int argc, const char **argv){ |
| 275 | if( argc<1 ) return; |
| 276 | if( argv[0]==0 ){ |
| 277 | sqlite_set_result_string(context, "NULL", 4); |
| 278 | }else if( sqliteIsNumber(argv[0]) ){ |
| 279 | sqlite_set_result_string(context, argv[0], -1); |
| 280 | }else{ |
| 281 | int i,j,n; |
| 282 | char *z; |
| 283 | for(i=n=0; argv[0][i]; i++){ if( argv[0][i]=='\'' ) n++; } |
| 284 | z = sqliteMalloc( i+n+3 ); |
| 285 | if( z==0 ) return; |
| 286 | z[0] = '\''; |
| 287 | for(i=0, j=1; argv[0][i]; i++){ |
| 288 | z[j++] = argv[0][i]; |
| 289 | if( argv[0][i]=='\'' ){ |
| 290 | z[j++] = '\''; |
| 291 | } |
| 292 | } |
| 293 | z[j++] = '\''; |
| 294 | z[j] = 0; |
| 295 | sqlite_set_result_string(context, z, j); |
| 296 | sqliteFree(z); |
| 297 | } |
| 298 | } |
| 299 | |
drh | d24cc42 | 2003-03-27 12:51:24 +0000 | [diff] [blame] | 300 | #ifdef SQLITE_SOUNDEX |
| 301 | /* |
| 302 | ** Compute the soundex encoding of a word. |
| 303 | */ |
| 304 | static void soundexFunc(sqlite_func *context, int argc, const char **argv){ |
| 305 | char zResult[8]; |
| 306 | const char *zIn; |
| 307 | int i, j; |
| 308 | static const unsigned char iCode[] = { |
| 309 | 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, |
| 310 | 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, |
| 311 | 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, |
| 312 | 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, |
| 313 | 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0, |
| 314 | 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0, |
| 315 | 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0, |
| 316 | 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0, |
| 317 | }; |
| 318 | assert( argc==1 ); |
| 319 | zIn = argv[0]; |
| 320 | for(i=0; zIn[i] && !isalpha(zIn[i]); i++){} |
| 321 | if( zIn[i] ){ |
| 322 | zResult[0] = toupper(zIn[i]); |
| 323 | for(j=1; j<4 && zIn[i]; i++){ |
| 324 | int code = iCode[zIn[i]&0x7f]; |
| 325 | if( code>0 ){ |
| 326 | zResult[j++] = code + '0'; |
| 327 | } |
| 328 | } |
| 329 | while( j<4 ){ |
| 330 | zResult[j++] = '0'; |
| 331 | } |
| 332 | zResult[j] = 0; |
| 333 | sqlite_set_result_string(context, zResult, 4); |
| 334 | }else{ |
drh | 937dd84 | 2003-06-28 16:20:22 +0000 | [diff] [blame] | 335 | sqlite_set_result_string(context, "?000", 4); |
drh | d24cc42 | 2003-03-27 12:51:24 +0000 | [diff] [blame] | 336 | } |
| 337 | } |
| 338 | #endif |
| 339 | |
drh | 193a6b4 | 2002-07-07 16:52:46 +0000 | [diff] [blame] | 340 | #ifdef SQLITE_TEST |
| 341 | /* |
| 342 | ** This function generates a string of random characters. Used for |
| 343 | ** generating test data. |
| 344 | */ |
| 345 | static void randStr(sqlite_func *context, int argc, const char **argv){ |
| 346 | static const char zSrc[] = |
| 347 | "abcdefghijklmnopqrstuvwxyz" |
| 348 | "ABCDEFGHIJKLMNOPQRSTUVWXYZ" |
| 349 | "0123456789" |
| 350 | ".-!,:*^+=_|?/<> "; |
| 351 | int iMin, iMax, n, r, i; |
| 352 | char zBuf[1000]; |
| 353 | if( argc>=1 ){ |
| 354 | iMin = atoi(argv[0]); |
| 355 | if( iMin<0 ) iMin = 0; |
| 356 | if( iMin>=sizeof(zBuf) ) iMin = sizeof(zBuf)-1; |
| 357 | }else{ |
| 358 | iMin = 1; |
| 359 | } |
| 360 | if( argc>=2 ){ |
| 361 | iMax = atoi(argv[1]); |
| 362 | if( iMax<iMin ) iMax = iMin; |
| 363 | if( iMax>=sizeof(zBuf) ) iMax = sizeof(zBuf); |
| 364 | }else{ |
| 365 | iMax = 50; |
| 366 | } |
| 367 | n = iMin; |
| 368 | if( iMax>iMin ){ |
drh | 3958196 | 2003-05-13 01:52:31 +0000 | [diff] [blame] | 369 | r = sqliteRandomInteger() & 0x7fffffff; |
drh | 193a6b4 | 2002-07-07 16:52:46 +0000 | [diff] [blame] | 370 | n += r%(iMax + 1 - iMin); |
| 371 | } |
| 372 | r = 0; |
| 373 | for(i=0; i<n; i++){ |
| 374 | r = (r + sqliteRandomByte())% (sizeof(zSrc)-1); |
| 375 | zBuf[i] = zSrc[r]; |
| 376 | } |
| 377 | zBuf[n] = 0; |
| 378 | sqlite_set_result_string(context, zBuf, n); |
| 379 | } |
| 380 | #endif |
| 381 | |
drh | 0ac6589 | 2002-04-20 14:24:41 +0000 | [diff] [blame] | 382 | /* |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 383 | ** An instance of the following structure holds the context of a |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 384 | ** sum() or avg() aggregate computation. |
| 385 | */ |
| 386 | typedef struct SumCtx SumCtx; |
| 387 | struct SumCtx { |
| 388 | double sum; /* Sum of terms */ |
drh | 739105c | 2002-05-29 23:22:23 +0000 | [diff] [blame] | 389 | int cnt; /* Number of elements summed */ |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 390 | }; |
| 391 | |
| 392 | /* |
| 393 | ** Routines used to compute the sum or average. |
| 394 | */ |
| 395 | static void sumStep(sqlite_func *context, int argc, const char **argv){ |
| 396 | SumCtx *p; |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 397 | if( argc<1 ) return; |
| 398 | p = sqlite_aggregate_context(context, sizeof(*p)); |
drh | 739105c | 2002-05-29 23:22:23 +0000 | [diff] [blame] | 399 | if( p && argv[0] ){ |
| 400 | p->sum += atof(argv[0]); |
| 401 | p->cnt++; |
| 402 | } |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 403 | } |
| 404 | static void sumFinalize(sqlite_func *context){ |
| 405 | SumCtx *p; |
| 406 | p = sqlite_aggregate_context(context, sizeof(*p)); |
drh | 89425d5 | 2002-02-28 03:04:48 +0000 | [diff] [blame] | 407 | sqlite_set_result_double(context, p ? p->sum : 0.0); |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 408 | } |
| 409 | static void avgFinalize(sqlite_func *context){ |
| 410 | SumCtx *p; |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 411 | p = sqlite_aggregate_context(context, sizeof(*p)); |
drh | 739105c | 2002-05-29 23:22:23 +0000 | [diff] [blame] | 412 | if( p && p->cnt>0 ){ |
| 413 | sqlite_set_result_double(context, p->sum/(double)p->cnt); |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 414 | } |
| 415 | } |
| 416 | |
| 417 | /* |
| 418 | ** An instance of the following structure holds the context of a |
drh | a2ed560 | 2002-02-26 23:55:31 +0000 | [diff] [blame] | 419 | ** variance or standard deviation computation. |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 420 | */ |
| 421 | typedef struct StdDevCtx StdDevCtx; |
| 422 | struct StdDevCtx { |
| 423 | double sum; /* Sum of terms */ |
| 424 | double sum2; /* Sum of the squares of terms */ |
drh | 739105c | 2002-05-29 23:22:23 +0000 | [diff] [blame] | 425 | int cnt; /* Number of terms counted */ |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 426 | }; |
| 427 | |
drh | ef2daf5 | 2002-03-04 02:26:15 +0000 | [diff] [blame] | 428 | #if 0 /* Omit because math library is required */ |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 429 | /* |
| 430 | ** Routines used to compute the standard deviation as an aggregate. |
| 431 | */ |
drh | 1350b03 | 2002-02-27 19:00:20 +0000 | [diff] [blame] | 432 | static void stdDevStep(sqlite_func *context, int argc, const char **argv){ |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 433 | StdDevCtx *p; |
| 434 | double x; |
drh | 1350b03 | 2002-02-27 19:00:20 +0000 | [diff] [blame] | 435 | if( argc<1 ) return; |
| 436 | p = sqlite_aggregate_context(context, sizeof(*p)); |
drh | 739105c | 2002-05-29 23:22:23 +0000 | [diff] [blame] | 437 | if( p && argv[0] ){ |
| 438 | x = atof(argv[0]); |
| 439 | p->sum += x; |
| 440 | p->sum2 += x*x; |
| 441 | p->cnt++; |
| 442 | } |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 443 | } |
drh | 1350b03 | 2002-02-27 19:00:20 +0000 | [diff] [blame] | 444 | static void stdDevFinalize(sqlite_func *context){ |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 445 | double rN = sqlite_aggregate_count(context); |
drh | 1350b03 | 2002-02-27 19:00:20 +0000 | [diff] [blame] | 446 | StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p)); |
drh | 739105c | 2002-05-29 23:22:23 +0000 | [diff] [blame] | 447 | if( p && p->cnt>1 ){ |
| 448 | double rCnt = cnt; |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 449 | sqlite_set_result_double(context, |
drh | 739105c | 2002-05-29 23:22:23 +0000 | [diff] [blame] | 450 | sqrt((p->sum2 - p->sum*p->sum/rCnt)/(rCnt-1.0))); |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 451 | } |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 452 | } |
drh | ef2daf5 | 2002-03-04 02:26:15 +0000 | [diff] [blame] | 453 | #endif |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 454 | |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 455 | /* |
| 456 | ** The following structure keeps track of state information for the |
| 457 | ** count() aggregate function. |
| 458 | */ |
| 459 | typedef struct CountCtx CountCtx; |
| 460 | struct CountCtx { |
| 461 | int n; |
| 462 | }; |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 463 | |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 464 | /* |
| 465 | ** Routines to implement the count() aggregate function. |
| 466 | */ |
| 467 | static void countStep(sqlite_func *context, int argc, const char **argv){ |
| 468 | CountCtx *p; |
| 469 | p = sqlite_aggregate_context(context, sizeof(*p)); |
| 470 | if( (argc==0 || argv[0]) && p ){ |
| 471 | p->n++; |
| 472 | } |
| 473 | } |
| 474 | static void countFinalize(sqlite_func *context){ |
| 475 | CountCtx *p; |
| 476 | p = sqlite_aggregate_context(context, sizeof(*p)); |
drh | f55f25f | 2002-02-28 01:46:11 +0000 | [diff] [blame] | 477 | sqlite_set_result_int(context, p ? p->n : 0); |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 478 | } |
| 479 | |
| 480 | /* |
| 481 | ** This function tracks state information for the min() and max() |
| 482 | ** aggregate functions. |
| 483 | */ |
| 484 | typedef struct MinMaxCtx MinMaxCtx; |
| 485 | struct MinMaxCtx { |
| 486 | char *z; /* The best so far */ |
| 487 | char zBuf[28]; /* Space that can be used for storage */ |
| 488 | }; |
| 489 | |
| 490 | /* |
| 491 | ** Routines to implement min() and max() aggregate functions. |
| 492 | */ |
| 493 | static void minStep(sqlite_func *context, int argc, const char **argv){ |
| 494 | MinMaxCtx *p; |
| 495 | p = sqlite_aggregate_context(context, sizeof(*p)); |
drh | 739105c | 2002-05-29 23:22:23 +0000 | [diff] [blame] | 496 | if( p==0 || argc<1 || argv[0]==0 ) return; |
drh | f570f01 | 2002-05-31 15:51:25 +0000 | [diff] [blame] | 497 | if( p->z==0 || sqliteCompare(argv[0],p->z)<0 ){ |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 498 | int len; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 499 | if( p->z && p->z!=p->zBuf ){ |
| 500 | sqliteFree(p->z); |
| 501 | } |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 502 | len = strlen(argv[0]); |
| 503 | if( len < sizeof(p->zBuf) ){ |
| 504 | p->z = p->zBuf; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 505 | }else{ |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 506 | p->z = sqliteMalloc( len+1 ); |
| 507 | if( p->z==0 ) return; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 508 | } |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 509 | strcpy(p->z, argv[0]); |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 510 | } |
| 511 | } |
| 512 | static void maxStep(sqlite_func *context, int argc, const char **argv){ |
| 513 | MinMaxCtx *p; |
| 514 | p = sqlite_aggregate_context(context, sizeof(*p)); |
drh | 739105c | 2002-05-29 23:22:23 +0000 | [diff] [blame] | 515 | if( p==0 || argc<1 || argv[0]==0 ) return; |
drh | f570f01 | 2002-05-31 15:51:25 +0000 | [diff] [blame] | 516 | if( p->z==0 || sqliteCompare(argv[0],p->z)>0 ){ |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 517 | int len; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 518 | if( p->z && p->z!=p->zBuf ){ |
| 519 | sqliteFree(p->z); |
| 520 | } |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 521 | len = strlen(argv[0]); |
| 522 | if( len < sizeof(p->zBuf) ){ |
| 523 | p->z = p->zBuf; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 524 | }else{ |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 525 | p->z = sqliteMalloc( len+1 ); |
| 526 | if( p->z==0 ) return; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 527 | } |
drh | 8912d10 | 2002-05-26 21:34:58 +0000 | [diff] [blame] | 528 | strcpy(p->z, argv[0]); |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 529 | } |
| 530 | } |
| 531 | static void minMaxFinalize(sqlite_func *context){ |
| 532 | MinMaxCtx *p; |
| 533 | p = sqlite_aggregate_context(context, sizeof(*p)); |
drh | 739105c | 2002-05-29 23:22:23 +0000 | [diff] [blame] | 534 | if( p && p->z ){ |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 535 | sqlite_set_result_string(context, p->z, strlen(p->z)); |
| 536 | } |
| 537 | if( p && p->z && p->z!=p->zBuf ){ |
| 538 | sqliteFree(p->z); |
| 539 | } |
| 540 | } |
drh | dd5baa9 | 2002-02-27 19:50:59 +0000 | [diff] [blame] | 541 | |
drh | d3a149e | 2002-02-24 17:12:53 +0000 | [diff] [blame] | 542 | /* |
drh | a2ed560 | 2002-02-26 23:55:31 +0000 | [diff] [blame] | 543 | ** This function registered all of the above C functions as SQL |
| 544 | ** functions. This should be the only routine in this file with |
| 545 | ** external linkage. |
drh | dc04c58 | 2002-02-24 01:55:15 +0000 | [diff] [blame] | 546 | */ |
drh | 28f4b68 | 2002-06-09 10:14:18 +0000 | [diff] [blame] | 547 | void sqliteRegisterBuiltinFunctions(sqlite *db){ |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 548 | static struct { |
| 549 | char *zName; |
| 550 | int nArg; |
drh | c9b84a1 | 2002-06-20 11:36:48 +0000 | [diff] [blame] | 551 | int dataType; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 552 | void (*xFunc)(sqlite_func*,int,const char**); |
| 553 | } aFuncs[] = { |
drh | c9b84a1 | 2002-06-20 11:36:48 +0000 | [diff] [blame] | 554 | { "min", -1, SQLITE_ARGS, minFunc }, |
| 555 | { "min", 0, 0, 0 }, |
| 556 | { "max", -1, SQLITE_ARGS, maxFunc }, |
| 557 | { "max", 0, 0, 0 }, |
| 558 | { "length", 1, SQLITE_NUMERIC, lengthFunc }, |
| 559 | { "substr", 3, SQLITE_TEXT, substrFunc }, |
| 560 | { "abs", 1, SQLITE_NUMERIC, absFunc }, |
| 561 | { "round", 1, SQLITE_NUMERIC, roundFunc }, |
| 562 | { "round", 2, SQLITE_NUMERIC, roundFunc }, |
| 563 | { "upper", 1, SQLITE_TEXT, upperFunc }, |
| 564 | { "lower", 1, SQLITE_TEXT, lowerFunc }, |
| 565 | { "coalesce", -1, SQLITE_ARGS, ifnullFunc }, |
| 566 | { "coalesce", 0, 0, 0 }, |
| 567 | { "coalesce", 1, 0, 0 }, |
| 568 | { "ifnull", 2, SQLITE_ARGS, ifnullFunc }, |
| 569 | { "random", -1, SQLITE_NUMERIC, randomFunc }, |
| 570 | { "like", 2, SQLITE_NUMERIC, likeFunc }, |
| 571 | { "glob", 2, SQLITE_NUMERIC, globFunc }, |
| 572 | { "nullif", 2, SQLITE_ARGS, nullifFunc }, |
drh | 647cb0e | 2002-11-04 19:32:25 +0000 | [diff] [blame] | 573 | { "sqlite_version",0,SQLITE_TEXT, versionFunc}, |
drh | 4739470 | 2003-08-20 01:03:33 +0000 | [diff] [blame] | 574 | { "quote", 1, SQLITE_ARGS, quoteFunc }, |
drh | d24cc42 | 2003-03-27 12:51:24 +0000 | [diff] [blame] | 575 | #ifdef SQLITE_SOUNDEX |
| 576 | { "soundex", 1, SQLITE_TEXT, soundexFunc}, |
| 577 | #endif |
drh | 193a6b4 | 2002-07-07 16:52:46 +0000 | [diff] [blame] | 578 | #ifdef SQLITE_TEST |
| 579 | { "randstr", 2, SQLITE_TEXT, randStr }, |
| 580 | #endif |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 581 | }; |
| 582 | static struct { |
| 583 | char *zName; |
| 584 | int nArg; |
drh | c9b84a1 | 2002-06-20 11:36:48 +0000 | [diff] [blame] | 585 | int dataType; |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 586 | void (*xStep)(sqlite_func*,int,const char**); |
| 587 | void (*xFinalize)(sqlite_func*); |
| 588 | } aAggs[] = { |
drh | c9b84a1 | 2002-06-20 11:36:48 +0000 | [diff] [blame] | 589 | { "min", 1, 0, minStep, minMaxFinalize }, |
| 590 | { "max", 1, 0, maxStep, minMaxFinalize }, |
| 591 | { "sum", 1, SQLITE_NUMERIC, sumStep, sumFinalize }, |
| 592 | { "avg", 1, SQLITE_NUMERIC, sumStep, avgFinalize }, |
| 593 | { "count", 0, SQLITE_NUMERIC, countStep, countFinalize }, |
| 594 | { "count", 1, SQLITE_NUMERIC, countStep, countFinalize }, |
drh | ef2daf5 | 2002-03-04 02:26:15 +0000 | [diff] [blame] | 595 | #if 0 |
drh | c9b84a1 | 2002-06-20 11:36:48 +0000 | [diff] [blame] | 596 | { "stddev", 1, SQLITE_NUMERIC, stdDevStep, stdDevFinalize }, |
drh | ef2daf5 | 2002-03-04 02:26:15 +0000 | [diff] [blame] | 597 | #endif |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 598 | }; |
| 599 | int i; |
| 600 | |
| 601 | for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ |
| 602 | sqlite_create_function(db, aFuncs[i].zName, |
| 603 | aFuncs[i].nArg, aFuncs[i].xFunc, 0); |
drh | c9b84a1 | 2002-06-20 11:36:48 +0000 | [diff] [blame] | 604 | if( aFuncs[i].xFunc ){ |
| 605 | sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType); |
| 606 | } |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 607 | } |
drh | 6ed41ad | 2002-04-06 14:10:47 +0000 | [diff] [blame] | 608 | sqlite_create_function(db, "last_insert_rowid", 0, |
| 609 | last_insert_rowid, db); |
drh | c9b84a1 | 2002-06-20 11:36:48 +0000 | [diff] [blame] | 610 | sqlite_function_type(db, "last_insert_rowid", SQLITE_NUMERIC); |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 611 | for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){ |
| 612 | sqlite_create_aggregate(db, aAggs[i].zName, |
| 613 | aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, 0); |
drh | c9b84a1 | 2002-06-20 11:36:48 +0000 | [diff] [blame] | 614 | sqlite_function_type(db, aAggs[i].zName, aAggs[i].dataType); |
drh | 0bce835 | 2002-02-28 00:41:10 +0000 | [diff] [blame] | 615 | } |
drh | 7014aff | 2003-11-01 01:53:53 +0000 | [diff] [blame^] | 616 | sqliteRegisterDateTimeFunctions(db); |
drh | dc04c58 | 2002-02-24 01:55:15 +0000 | [diff] [blame] | 617 | } |