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 ** 19 ** $Id: func.c,v 1.30 2003/08/26 11:41:27 drh Exp $ 20 */ 21 #include <ctype.h> 22 #include <math.h> 23 #include <stdlib.h> 24 #include <assert.h> 25 #include "sqliteInt.h" 26 #include "os.h" 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 35 if( argc==0 ) return; 36 zBest = argv[0]; 37 if( zBest==0 ) return; 38 for(i=1; i<argc; i++){ 39 if( argv[i]==0 ) return; 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 50 if( argc==0 ) return; 51 zBest = argv[0]; 52 if( zBest==0 ) return; 53 for(i=1; i<argc; i++){ 54 if( argv[i]==0 ) return; 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]; 71 if( z==0 ) return; 72 #ifdef SQLITE_UTF8 73 for(len=0; *z; z++){ if( (0xc0&*z)!=0x80 ) len++; } 74 #else 75 len = strlen(z); 76 #endif 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]; 87 if( z==0 ) return; 88 if( z[0]=='-' && isdigit(z[1]) ) z++; 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 108 for(len=0, z2=z; *z2; z2++){ if( (0xc0&*z2)!=0x80 ) len++; } 109 #else 110 len = strlen(z); 111 #endif 112 if( p1<0 ){ 113 p1 += len; 114 if( p1<0 ){ 115 p2 += p1; 116 p1 = 0; 117 } 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] ); 127 if( (z[i]&0xc0)==0x80 ) p1++; 128 } 129 while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p1++; } 130 for(; i<p1+p2; i++){ 131 assert( z[i] ); 132 if( (z[i]&0xc0)==0x80 ) p2++; 133 } 134 while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p2++; } 135 #endif 136 if( p2<0 ) p2 = 0; 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 ); 148 if( argv[0]==0 || (argc==2 && argv[1]==0) ) return; 149 n = argc==2 ? atoi(argv[1]) : 0; 150 if( n>30 ) n = 30; 151 if( n<0 ) n = 0; 152 r = atof(argv[0]); 153 sprintf(zBuf,"%.*f",n,r); 154 sqlite_set_result_string(context, zBuf, -1); 155 } 156 157 /* 158 ** Implementation of the upper() and lower() SQL functions. 159 */ 160 static void upperFunc(sqlite_func *context, int argc, const char **argv){ 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 } 170 static void lowerFunc(sqlite_func *context, int argc, const char **argv){ 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 /* 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. 185 */ 186 static void ifnullFunc(sqlite_func *context, int argc, const char **argv){ 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 } 194 } 195 196 /* 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 /* 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 */ 207 static void last_insert_rowid(sqlite_func *context, int arg, const char **argv){ 208 sqlite *db = sqlite_user_data(context); 209 sqlite_set_result_int(context, sqlite_last_insert_rowid(db)); 210 } 211 212 /* 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){ 222 if( argv[0]==0 || argv[1]==0 ) return; 223 sqlite_set_result_int(context, 224 sqliteLikeCompare((const unsigned char*)argv[0], 225 (const unsigned char*)argv[1])); 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){ 238 if( argv[0]==0 || argv[1]==0 ) return; 239 sqlite_set_result_int(context, 240 sqliteGlobCompare((const unsigned char*)argv[0], 241 (const unsigned char*)argv[1])); 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 } 253 } 254 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 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 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{ 335 sqlite_set_result_string(context, "?000", 4); 336 } 337 } 338 #endif 339 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 ){ 369 r = sqliteRandomInteger() & 0x7fffffff; 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 382 /* 383 ** An instance of the following structure holds the context of a 384 ** sum() or avg() aggregate computation. 385 */ 386 typedef struct SumCtx SumCtx; 387 struct SumCtx { 388 double sum; /* Sum of terms */ 389 int cnt; /* Number of elements summed */ 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; 397 if( argc<1 ) return; 398 p = sqlite_aggregate_context(context, sizeof(*p)); 399 if( p && argv[0] ){ 400 p->sum += atof(argv[0]); 401 p->cnt++; 402 } 403 } 404 static void sumFinalize(sqlite_func *context){ 405 SumCtx *p; 406 p = sqlite_aggregate_context(context, sizeof(*p)); 407 sqlite_set_result_double(context, p ? p->sum : 0.0); 408 } 409 static void avgFinalize(sqlite_func *context){ 410 SumCtx *p; 411 p = sqlite_aggregate_context(context, sizeof(*p)); 412 if( p && p->cnt>0 ){ 413 sqlite_set_result_double(context, p->sum/(double)p->cnt); 414 } 415 } 416 417 /* 418 ** An instance of the following structure holds the context of a 419 ** variance or standard deviation computation. 420 */ 421 typedef struct StdDevCtx StdDevCtx; 422 struct StdDevCtx { 423 double sum; /* Sum of terms */ 424 double sum2; /* Sum of the squares of terms */ 425 int cnt; /* Number of terms counted */ 426 }; 427 428 #if 0 /* Omit because math library is required */ 429 /* 430 ** Routines used to compute the standard deviation as an aggregate. 431 */ 432 static void stdDevStep(sqlite_func *context, int argc, const char **argv){ 433 StdDevCtx *p; 434 double x; 435 if( argc<1 ) return; 436 p = sqlite_aggregate_context(context, sizeof(*p)); 437 if( p && argv[0] ){ 438 x = atof(argv[0]); 439 p->sum += x; 440 p->sum2 += x*x; 441 p->cnt++; 442 } 443 } 444 static void stdDevFinalize(sqlite_func *context){ 445 double rN = sqlite_aggregate_count(context); 446 StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p)); 447 if( p && p->cnt>1 ){ 448 double rCnt = cnt; 449 sqlite_set_result_double(context, 450 sqrt((p->sum2 - p->sum*p->sum/rCnt)/(rCnt-1.0))); 451 } 452 } 453 #endif 454 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 }; 463 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)); 477 sqlite_set_result_int(context, p ? p->n : 0); 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)); 496 if( p==0 || argc<1 || argv[0]==0 ) return; 497 if( p->z==0 || sqliteCompare(argv[0],p->z)<0 ){ 498 int len; 499 if( p->z && p->z!=p->zBuf ){ 500 sqliteFree(p->z); 501 } 502 len = strlen(argv[0]); 503 if( len < sizeof(p->zBuf) ){ 504 p->z = p->zBuf; 505 }else{ 506 p->z = sqliteMalloc( len+1 ); 507 if( p->z==0 ) return; 508 } 509 strcpy(p->z, argv[0]); 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)); 515 if( p==0 || argc<1 || argv[0]==0 ) return; 516 if( p->z==0 || sqliteCompare(argv[0],p->z)>0 ){ 517 int len; 518 if( p->z && p->z!=p->zBuf ){ 519 sqliteFree(p->z); 520 } 521 len = strlen(argv[0]); 522 if( len < sizeof(p->zBuf) ){ 523 p->z = p->zBuf; 524 }else{ 525 p->z = sqliteMalloc( len+1 ); 526 if( p->z==0 ) return; 527 } 528 strcpy(p->z, argv[0]); 529 } 530 } 531 static void minMaxFinalize(sqlite_func *context){ 532 MinMaxCtx *p; 533 p = sqlite_aggregate_context(context, sizeof(*p)); 534 if( p && p->z ){ 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 } 541 542 /**************************************************************************** 543 ** Time and date functions. 544 ** 545 ** SQLite processes all times and dates as Julian Day numbers. The 546 ** dates and times are stored as the number of days since noon 547 ** in Greenwich on November 24, 4714 B.C. according to the Gregorian 548 ** calendar system. 549 ** 550 ** This implement requires years to be expressed as a 4-digit number 551 ** which means that only dates between 0000-01-01 and 9999-12-31 can 552 ** be represented, even though julian day numbers allow a much wider 553 ** range of dates. 554 ** 555 ** The Gregorian calendar system is used for all dates and times, 556 ** even those that predate the Gregorian calendar. Historians usually 557 ** use the Julian calendar for dates prior to 1582-10-15 and for some 558 ** dates afterwards, depending on locale. Beware of this difference. 559 ** 560 ** The conversion algorithms are implemented based on descriptions 561 ** in the following text: 562 ** 563 ** Jean Meeus 564 ** Astronomical Algorithms, 2nd Edition, 1998 565 ** ISBM 0-943396-61-1 566 ** Willmann-Bell, Inc 567 ** Richmond, Virginia (USA) 568 */ 569 #ifndef SQLITE_OMIT_DATETIME_FUNCS 570 571 /* 572 ** Convert N digits from zDate into an integer. Return 573 ** -1 if zDate does not begin with N digits. 574 */ 575 static int getDigits(const char *zDate, int N){ 576 int val = 0; 577 while( N-- ){ 578 if( !isdigit(*zDate) ) return -1; 579 val = val*10 + *zDate - '0'; 580 zDate++; 581 } 582 return val; 583 } 584 585 /* 586 ** Parse times of the form HH:MM:SS or HH:MM. Store the 587 ** result (in days) in *prJD. 588 ** 589 ** Return 1 if there is a parsing error and 0 on success. 590 */ 591 static int parseHhMmSs(const char *zDate, double *prJD){ 592 int h, m, s; 593 h = getDigits(zDate, 2); 594 if( h<0 || zDate[2]!=':' ) return 1; 595 zDate += 3; 596 m = getDigits(zDate, 2); 597 if( m<0 || m>59 ) return 1; 598 zDate += 2; 599 if( *zDate==':' ){ 600 s = getDigits(&zDate[1], 2); 601 if( s<0 || s>59 ) return 1; 602 zDate += 3; 603 }else{ 604 s = 0; 605 } 606 while( isspace(*zDate) ){ zDate++; } 607 *prJD = (h*3600.0 + m*60.0 + s)/86400.0; 608 return 0; 609 } 610 611 /* 612 ** Parse dates of the form 613 ** 614 ** YYYY-MM-DD HH:MM:SS 615 ** YYYY-MM-DD HH:MM 616 ** YYYY-MM-DD 617 ** 618 ** Write the result as a julian day number in *prJD. Return 0 619 ** on success and 1 if the input string is not a well-formed 620 ** date. 621 */ 622 static int parseYyyyMmDd(const char *zDate, double *prJD){ 623 int Y, M, D; 624 double rTime; 625 int A, B, X1, X2; 626 627 Y = getDigits(zDate, 4); 628 if( Y<0 || zDate[4]!='-' ) return 1; 629 zDate += 5; 630 M = getDigits(zDate, 2); 631 if( M<=0 || M>12 || zDate[2]!='-' ) return 1; 632 zDate += 3; 633 D = getDigits(zDate, 2); 634 if( D<=0 || D>31 ) return 1; 635 zDate += 2; 636 while( isspace(*zDate) ){ zDate++; } 637 if( isdigit(*zDate) ){ 638 if( parseHhMmSs(zDate, &rTime) ) return 1; 639 }else if( *zDate==0 ){ 640 rTime = 0.0; 641 }else{ 642 return 1; 643 } 644 645 /* The year, month, and day are now stored in Y, M, and D. Convert 646 ** these into the Julian Day number. See Meeus page 61. 647 */ 648 if( M<=2 ){ 649 Y--; 650 M += 12; 651 } 652 A = Y/100; 653 B = 2 - A + (A/4); 654 X1 = 365.25*(Y+4716); 655 X2 = 30.6001*(M+1); 656 *prJD = X1 + X2 + D + B - 1524.5 + rTime; 657 return 0; 658 } 659 660 /* 661 ** Attempt to parse the given string into a Julian Day Number. Return 662 ** the number of errors. 663 ** 664 ** The following are acceptable forms for the input string: 665 ** 666 ** YYYY-MM-DD 667 ** YYYY-MM-DD HH:MM 668 ** YYYY-MM-DD HH:MM:SS 669 ** HH:MM 670 ** HH:MM:SS 671 ** DDDD.DD 672 ** now 673 */ 674 static int parseDateOrTime(const char *zDate, double *prJD){ 675 int i; 676 for(i=0; isdigit(zDate[i]); i++){} 677 if( i==4 && zDate[i]=='-' ){ 678 return parseYyyyMmDd(zDate, prJD); 679 }else if( i==2 && zDate[i]==':' ){ 680 return parseHhMmSs(zDate, prJD); 681 }else if( i==0 && sqliteStrICmp(zDate,"now")==0 ){ 682 return sqliteOsCurrentTime(prJD); 683 }else if( sqliteIsNumber(zDate) ){ 684 *prJD = atof(zDate); 685 return 0; 686 } 687 return 1; 688 } 689 690 /* 691 ** A structure for holding date and time. 692 */ 693 typedef struct DateTime DateTime; 694 struct DateTime { 695 double rJD; /* The julian day number */ 696 int Y, M, D; /* Year, month, and day */ 697 int h, m, s; /* Hour minute and second */ 698 }; 699 700 /* 701 ** Break up a julian day number into year, month, day, hour, minute, second. 702 ** This function assume the Gregorian calendar - even for dates prior 703 ** to the invention of the Gregorian calendar in 1582. 704 ** 705 ** See Meeus page 63. 706 ** 707 ** If mode==1 only the year, month, and day are computed. If mode==2 708 ** then only the hour, minute, and second are computed. If mode==3 then 709 ** everything is computed. If mode==0, this routine is a no-op. 710 */ 711 static void decomposeDate(DateTime *p, int mode){ 712 int Z; 713 Z = p->rJD + 0.5; 714 if( mode & 1 ){ 715 int A, B, C, D, E, X1; 716 A = (Z - 1867216.25)/36524.25; 717 A = Z + 1 + A - (A/4); 718 B = A + 1524; 719 C = (B - 122.1)/365.25; 720 D = 365.25*C; 721 E = (B-D)/30.6001; 722 X1 = 30.6001*E; 723 p->D = B - D - X1; 724 p->M = E<14 ? E-1 : E-13; 725 p->Y = p->M>2 ? C - 4716 : C - 4715; 726 } 727 if( mode & 2 ){ 728 p->s = (p->rJD + 0.5 - Z)*86400.0; 729 p->h = p->s/3600; 730 p->s -= p->h*3600; 731 p->m = p->s/60; 732 p->s -= p->m*60; 733 } 734 } 735 736 /* 737 ** Check to see that all arguments are valid date strings. If any 738 ** argument is not a valid date string, return 0. If all arguments 739 ** are valid, return 1 and write into *p->rJD the sum of the julian day 740 ** numbers for all date strings. 741 ** 742 ** A "valid" date string is one that is accepted by parseDateOrTime(). 743 ** 744 ** The mode argument is passed through to decomposeDate() in order to 745 ** fill in the year, month, day, hour, minute, and second of the *p 746 ** structure, if desired. 747 */ 748 static int isDate(int argc, const char **argv, DateTime *p, int mode){ 749 double r; 750 int i; 751 p->rJD = 0.0; 752 for(i=0; i<argc; i++){ 753 if( argv[i]==0 ) return 0; 754 if( parseDateOrTime(argv[i], &r) ) return 0; 755 p->rJD += r; 756 } 757 decomposeDate(p, mode); 758 return 1; 759 } 760 761 762 /* 763 ** The following routines implement the various date and time functions 764 ** of SQLite. 765 */ 766 static void juliandayFunc(sqlite_func *context, int argc, const char **argv){ 767 DateTime x; 768 if( isDate(argc, argv, &x, 0) ){ 769 sqlite_set_result_double(context, x.rJD); 770 } 771 } 772 static void timestampFunc(sqlite_func *context, int argc, const char **argv){ 773 DateTime x; 774 if( isDate(argc, argv, &x, 3) ){ 775 char zBuf[100]; 776 sprintf(zBuf, "%04d-%02d-%02d %02d:%02d:%02d",x.Y, x.M, x.D, x.h, x.m, x.s); 777 sqlite_set_result_string(context, zBuf, -1); 778 } 779 } 780 static void timeFunc(sqlite_func *context, int argc, const char **argv){ 781 DateTime x; 782 if( isDate(argc, argv, &x, 2) ){ 783 char zBuf[100]; 784 sprintf(zBuf, "%02d:%02d:%02d", x.h, x.m, x.s); 785 sqlite_set_result_string(context, zBuf, -1); 786 } 787 } 788 static void dateFunc(sqlite_func *context, int argc, const char **argv){ 789 DateTime x; 790 if( isDate(argc, argv, &x, 1) ){ 791 char zBuf[100]; 792 sprintf(zBuf, "%04d-%02d-%02d", x.Y, x.M, x.D); 793 sqlite_set_result_string(context, zBuf, -1); 794 } 795 } 796 static void yearFunc(sqlite_func *context, int argc, const char **argv){ 797 DateTime x; 798 if( isDate(argc, argv, &x, 1) ){ 799 sqlite_set_result_int(context, x.Y); 800 } 801 } 802 static void monthFunc(sqlite_func *context, int argc, const char **argv){ 803 DateTime x; 804 if( isDate(argc, argv, &x, 1) ){ 805 sqlite_set_result_int(context, x.M); 806 } 807 } 808 static void dayofweekFunc(sqlite_func *context, int argc, const char **argv){ 809 DateTime x; 810 if( isDate(argc, argv, &x, 0) ){ 811 int Z = x.rJD + 1.5; 812 sqlite_set_result_int(context, Z % 7); 813 } 814 } 815 static void dayofmonthFunc(sqlite_func *context, int argc, const char **argv){ 816 DateTime x; 817 if( isDate(argc, argv, &x, 1) ){ 818 sqlite_set_result_int(context, x.D); 819 } 820 } 821 static void secondFunc(sqlite_func *context, int argc, const char **argv){ 822 DateTime x; 823 if( isDate(argc, argv, &x, 2) ){ 824 sqlite_set_result_int(context, x.s); 825 } 826 } 827 static void minuteFunc(sqlite_func *context, int argc, const char **argv){ 828 DateTime x; 829 if( isDate(argc, argv, &x, 2) ){ 830 sqlite_set_result_int(context, x.m); 831 } 832 } 833 static void hourFunc(sqlite_func *context, int argc, const char **argv){ 834 DateTime x; 835 if( isDate(argc, argv, &x, 2) ){ 836 sqlite_set_result_int(context, x.h); 837 } 838 } 839 #endif /* !defined(SQLITE_OMIT_DATETIME_FUNCS) */ 840 /***************************************************************************/ 841 842 /* 843 ** This function registered all of the above C functions as SQL 844 ** functions. This should be the only routine in this file with 845 ** external linkage. 846 */ 847 void sqliteRegisterBuiltinFunctions(sqlite *db){ 848 static struct { 849 char *zName; 850 int nArg; 851 int dataType; 852 void (*xFunc)(sqlite_func*,int,const char**); 853 } aFuncs[] = { 854 { "min", -1, SQLITE_ARGS, minFunc }, 855 { "min", 0, 0, 0 }, 856 { "max", -1, SQLITE_ARGS, maxFunc }, 857 { "max", 0, 0, 0 }, 858 { "length", 1, SQLITE_NUMERIC, lengthFunc }, 859 { "substr", 3, SQLITE_TEXT, substrFunc }, 860 { "abs", 1, SQLITE_NUMERIC, absFunc }, 861 { "round", 1, SQLITE_NUMERIC, roundFunc }, 862 { "round", 2, SQLITE_NUMERIC, roundFunc }, 863 { "upper", 1, SQLITE_TEXT, upperFunc }, 864 { "lower", 1, SQLITE_TEXT, lowerFunc }, 865 { "coalesce", -1, SQLITE_ARGS, ifnullFunc }, 866 { "coalesce", 0, 0, 0 }, 867 { "coalesce", 1, 0, 0 }, 868 { "ifnull", 2, SQLITE_ARGS, ifnullFunc }, 869 { "random", -1, SQLITE_NUMERIC, randomFunc }, 870 { "like", 2, SQLITE_NUMERIC, likeFunc }, 871 { "glob", 2, SQLITE_NUMERIC, globFunc }, 872 { "nullif", 2, SQLITE_ARGS, nullifFunc }, 873 { "sqlite_version",0,SQLITE_TEXT, versionFunc}, 874 { "quote", 1, SQLITE_ARGS, quoteFunc }, 875 #ifndef SQLITE_OMIT_DATETIME_FUNCS 876 { "julianday", -1, SQLITE_NUMERIC, juliandayFunc }, 877 { "timestamp", -1, SQLITE_TEXT, timestampFunc }, 878 { "time", -1, SQLITE_TEXT, timeFunc }, 879 { "date", -1, SQLITE_TEXT, dateFunc }, 880 { "year", -1, SQLITE_NUMERIC, yearFunc }, 881 { "month", -1, SQLITE_NUMERIC, monthFunc }, 882 { "dayofmonth",-1, SQLITE_NUMERIC, dayofmonthFunc }, 883 { "dayofweek", -1, SQLITE_NUMERIC, dayofweekFunc }, 884 { "hour", -1, SQLITE_NUMERIC, hourFunc }, 885 { "minute", -1, SQLITE_NUMERIC, minuteFunc }, 886 { "second", -1, SQLITE_NUMERIC, secondFunc }, 887 #endif 888 #ifdef SQLITE_SOUNDEX 889 { "soundex", 1, SQLITE_TEXT, soundexFunc}, 890 #endif 891 #ifdef SQLITE_TEST 892 { "randstr", 2, SQLITE_TEXT, randStr }, 893 #endif 894 }; 895 static struct { 896 char *zName; 897 int nArg; 898 int dataType; 899 void (*xStep)(sqlite_func*,int,const char**); 900 void (*xFinalize)(sqlite_func*); 901 } aAggs[] = { 902 { "min", 1, 0, minStep, minMaxFinalize }, 903 { "max", 1, 0, maxStep, minMaxFinalize }, 904 { "sum", 1, SQLITE_NUMERIC, sumStep, sumFinalize }, 905 { "avg", 1, SQLITE_NUMERIC, sumStep, avgFinalize }, 906 { "count", 0, SQLITE_NUMERIC, countStep, countFinalize }, 907 { "count", 1, SQLITE_NUMERIC, countStep, countFinalize }, 908 #if 0 909 { "stddev", 1, SQLITE_NUMERIC, stdDevStep, stdDevFinalize }, 910 #endif 911 }; 912 int i; 913 914 for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ 915 sqlite_create_function(db, aFuncs[i].zName, 916 aFuncs[i].nArg, aFuncs[i].xFunc, 0); 917 if( aFuncs[i].xFunc ){ 918 sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType); 919 } 920 } 921 sqlite_create_function(db, "last_insert_rowid", 0, 922 last_insert_rowid, db); 923 sqlite_function_type(db, "last_insert_rowid", SQLITE_NUMERIC); 924 for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){ 925 sqlite_create_aggregate(db, aAggs[i].zName, 926 aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, 0); 927 sqlite_function_type(db, aAggs[i].zName, aAggs[i].dataType); 928 } 929 } 930