4
** The author disclaims copyright to this source code. In place of
5
** a legal notice, here is a blessing:
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.
11
*************************************************************************
12
** This file contains the C functions that implement various SQL
13
** functions of SQLite.
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.
19
** $Id: func.c 326789 2004-07-07 21:25:56Z pahlibar $
25
#include "sqliteInt.h"
29
** Implementation of the non-aggregate min() and max() functions
31
static void minmaxFunc(sqlite_func *context, int argc, const char **argv){
34
int (*xCompare)(const char*, const char*);
35
int mask; /* 0 for min() or 0xffffffff for max() */
38
mask = (int)sqlite_user_data(context);
40
if( zBest==0 ) return;
41
if( argv[1][0]=='n' ){
42
xCompare = sqliteCompare;
46
for(i=2; i<argc; i+=2){
47
if( argv[i]==0 ) return;
48
if( (xCompare(argv[i], zBest)^mask)<0 ){
52
sqlite_set_result_string(context, zBest, -1);
56
** Return the type of the argument.
58
static void typeofFunc(sqlite_func *context, int argc, const char **argv){
60
sqlite_set_result_string(context, argv[1], -1);
64
** Implementation of the length() function
66
static void lengthFunc(sqlite_func *context, int argc, const char **argv){
74
for(len=0; *z; z++){ if( (0xc0&*z)!=0x80 ) len++; }
78
sqlite_set_result_int(context, len);
82
** Implementation of the abs() function
84
static void absFunc(sqlite_func *context, int argc, const char **argv){
89
if( z[0]=='-' && isdigit(z[1]) ) z++;
90
sqlite_set_result_string(context, z, -1);
94
** Implementation of the substr() function
96
static void substrFunc(sqlite_func *context, int argc, const char **argv){
106
p1 = atoi(argv[1]?argv[1]:0);
107
p2 = atoi(argv[2]?argv[2]:0);
109
for(len=0, z2=z; *z2; z2++){ if( (0xc0&*z2)!=0x80 ) len++; }
126
for(i=0; i<p1 && z[i]; i++){
127
if( (z[i]&0xc0)==0x80 ) p1++;
129
while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p1++; }
130
for(; i<p1+p2 && z[i]; i++){
131
if( (z[i]&0xc0)==0x80 ) p2++;
133
while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p2++; }
136
sqlite_set_result_string(context, &z[p1], p2);
140
** Implementation of the round() function
142
static void roundFunc(sqlite_func *context, int argc, const char **argv){
146
assert( argc==1 || argc==2 );
147
if( argv[0]==0 || (argc==2 && argv[1]==0) ) return;
148
n = argc==2 ? atoi(argv[1]) : 0;
151
r = sqliteAtoF(argv[0], 0);
152
sprintf(zBuf,"%.*f",n,r);
153
sqlite_set_result_string(context, zBuf, -1);
157
** Implementation of the upper() and lower() SQL functions.
159
static void upperFunc(sqlite_func *context, int argc, const char **argv){
162
if( argc<1 || argv[0]==0 ) return;
163
z = sqlite_set_result_string(context, argv[0], -1);
166
if( islower(z[i]) ) z[i] = toupper(z[i]);
169
static void lowerFunc(sqlite_func *context, int argc, const char **argv){
172
if( argc<1 || argv[0]==0 ) return;
173
z = sqlite_set_result_string(context, argv[0], -1);
176
if( isupper(z[i]) ) z[i] = tolower(z[i]);
181
** Implementation of the IFNULL(), NVL(), and COALESCE() functions.
182
** All three do the same thing. They return the first non-NULL
185
static void ifnullFunc(sqlite_func *context, int argc, const char **argv){
187
for(i=0; i<argc; i++){
189
sqlite_set_result_string(context, argv[i], -1);
196
** Implementation of random(). Return a random integer.
198
static void randomFunc(sqlite_func *context, int argc, const char **argv){
200
sqliteRandomness(sizeof(r), &r);
201
sqlite_set_result_int(context, r);
205
** Implementation of the last_insert_rowid() SQL function. The return
206
** value is the same as the sqlite_last_insert_rowid() API function.
208
static void last_insert_rowid(sqlite_func *context, int arg, const char **argv){
209
sqlite *db = sqlite_user_data(context);
210
sqlite_set_result_int(context, sqlite_last_insert_rowid(db));
214
** Implementation of the change_count() SQL function. The return
215
** value is the same as the sqlite_changes() API function.
217
static void change_count(sqlite_func *context, int arg, const char **argv){
218
sqlite *db = sqlite_user_data(context);
219
sqlite_set_result_int(context, sqlite_changes(db));
223
** Implementation of the last_statement_change_count() SQL function. The
224
** return value is the same as the sqlite_last_statement_changes() API function.
226
static void last_statement_change_count(sqlite_func *context, int arg,
228
sqlite *db = sqlite_user_data(context);
229
sqlite_set_result_int(context, sqlite_last_statement_changes(db));
233
** Implementation of the like() SQL function. This function implements
234
** the build-in LIKE operator. The first argument to the function is the
235
** string and the second argument is the pattern. So, the SQL statements:
239
** is implemented as like(A,B).
241
static void likeFunc(sqlite_func *context, int arg, const char **argv){
242
if( argv[0]==0 || argv[1]==0 ) return;
243
sqlite_set_result_int(context,
244
sqliteLikeCompare((const unsigned char*)argv[0],
245
(const unsigned char*)argv[1]));
249
** Implementation of the glob() SQL function. This function implements
250
** the build-in GLOB operator. The first argument to the function is the
251
** string and the second argument is the pattern. So, the SQL statements:
255
** is implemented as glob(A,B).
257
static void globFunc(sqlite_func *context, int arg, const char **argv){
258
if( argv[0]==0 || argv[1]==0 ) return;
259
sqlite_set_result_int(context,
260
sqliteGlobCompare((const unsigned char*)argv[0],
261
(const unsigned char*)argv[1]));
265
** Implementation of the NULLIF(x,y) function. The result is the first
266
** argument if the arguments are different. The result is NULL if the
267
** arguments are equal to each other.
269
static void nullifFunc(sqlite_func *context, int argc, const char **argv){
270
if( argv[0]!=0 && sqliteCompare(argv[0],argv[1])!=0 ){
271
sqlite_set_result_string(context, argv[0], -1);
276
** Implementation of the VERSION(*) function. The result is the version
277
** of the SQLite library that is running.
279
static void versionFunc(sqlite_func *context, int argc, const char **argv){
280
sqlite_set_result_string(context, sqlite_version, -1);
284
** EXPERIMENTAL - This is not an official function. The interface may
285
** change. This function may disappear. Do not write code that depends
288
** Implementation of the QUOTE() function. This function takes a single
289
** argument. If the argument is numeric, the return value is the same as
290
** the argument. If the argument is NULL, the return value is the string
291
** "NULL". Otherwise, the argument is enclosed in single quotes with
292
** single-quote escapes.
294
static void quoteFunc(sqlite_func *context, int argc, const char **argv){
297
sqlite_set_result_string(context, "NULL", 4);
298
}else if( sqliteIsNumber(argv[0]) ){
299
sqlite_set_result_string(context, argv[0], -1);
303
for(i=n=0; argv[0][i]; i++){ if( argv[0][i]=='\'' ) n++; }
304
z = sqliteMalloc( i+n+3 );
307
for(i=0, j=1; argv[0][i]; i++){
309
if( argv[0][i]=='\'' ){
315
sqlite_set_result_string(context, z, j);
320
#ifdef SQLITE_SOUNDEX
322
** Compute the soundex encoding of a word.
324
static void soundexFunc(sqlite_func *context, int argc, const char **argv){
328
static const unsigned char iCode[] = {
329
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
330
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
331
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
332
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
333
0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
334
1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
335
0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
336
1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
340
for(i=0; zIn[i] && !isalpha(zIn[i]); i++){}
342
zResult[0] = toupper(zIn[i]);
343
for(j=1; j<4 && zIn[i]; i++){
344
int code = iCode[zIn[i]&0x7f];
346
zResult[j++] = code + '0';
353
sqlite_set_result_string(context, zResult, 4);
355
sqlite_set_result_string(context, "?000", 4);
362
** This function generates a string of random characters. Used for
363
** generating test data.
365
static void randStr(sqlite_func *context, int argc, const char **argv){
366
static const unsigned char zSrc[] =
367
"abcdefghijklmnopqrstuvwxyz"
368
"ABCDEFGHIJKLMNOPQRSTUVWXYZ"
371
int iMin, iMax, n, r, i;
372
unsigned char zBuf[1000];
374
iMin = atoi(argv[0]);
375
if( iMin<0 ) iMin = 0;
376
if( iMin>=sizeof(zBuf) ) iMin = sizeof(zBuf)-1;
381
iMax = atoi(argv[1]);
382
if( iMax<iMin ) iMax = iMin;
383
if( iMax>=sizeof(zBuf) ) iMax = sizeof(zBuf)-1;
389
sqliteRandomness(sizeof(r), &r);
391
n += r%(iMax + 1 - iMin);
393
assert( n<sizeof(zBuf) );
394
sqliteRandomness(n, zBuf);
396
zBuf[i] = zSrc[zBuf[i]%(sizeof(zSrc)-1)];
399
sqlite_set_result_string(context, zBuf, n);
404
** An instance of the following structure holds the context of a
405
** sum() or avg() aggregate computation.
407
typedef struct SumCtx SumCtx;
409
double sum; /* Sum of terms */
410
int cnt; /* Number of elements summed */
414
** Routines used to compute the sum or average.
416
static void sumStep(sqlite_func *context, int argc, const char **argv){
419
p = sqlite_aggregate_context(context, sizeof(*p));
421
p->sum += sqliteAtoF(argv[0], 0);
425
static void sumFinalize(sqlite_func *context){
427
p = sqlite_aggregate_context(context, sizeof(*p));
428
sqlite_set_result_double(context, p ? p->sum : 0.0);
430
static void avgFinalize(sqlite_func *context){
432
p = sqlite_aggregate_context(context, sizeof(*p));
434
sqlite_set_result_double(context, p->sum/(double)p->cnt);
439
** An instance of the following structure holds the context of a
440
** variance or standard deviation computation.
442
typedef struct StdDevCtx StdDevCtx;
444
double sum; /* Sum of terms */
445
double sum2; /* Sum of the squares of terms */
446
int cnt; /* Number of terms counted */
449
#if 0 /* Omit because math library is required */
451
** Routines used to compute the standard deviation as an aggregate.
453
static void stdDevStep(sqlite_func *context, int argc, const char **argv){
457
p = sqlite_aggregate_context(context, sizeof(*p));
459
x = sqliteAtoF(argv[0], 0);
465
static void stdDevFinalize(sqlite_func *context){
466
double rN = sqlite_aggregate_count(context);
467
StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p));
470
sqlite_set_result_double(context,
471
sqrt((p->sum2 - p->sum*p->sum/rCnt)/(rCnt-1.0)));
477
** The following structure keeps track of state information for the
478
** count() aggregate function.
480
typedef struct CountCtx CountCtx;
486
** Routines to implement the count() aggregate function.
488
static void countStep(sqlite_func *context, int argc, const char **argv){
490
p = sqlite_aggregate_context(context, sizeof(*p));
491
if( (argc==0 || argv[0]) && p ){
495
static void countFinalize(sqlite_func *context){
497
p = sqlite_aggregate_context(context, sizeof(*p));
498
sqlite_set_result_int(context, p ? p->n : 0);
502
** This function tracks state information for the min() and max()
503
** aggregate functions.
505
typedef struct MinMaxCtx MinMaxCtx;
507
char *z; /* The best so far */
508
char zBuf[28]; /* Space that can be used for storage */
512
** Routines to implement min() and max() aggregate functions.
514
static void minmaxStep(sqlite_func *context, int argc, const char **argv){
516
int (*xCompare)(const char*, const char*);
517
int mask; /* 0 for min() or 0xffffffff for max() */
520
if( argv[1][0]=='n' ){
521
xCompare = sqliteCompare;
525
mask = (int)sqlite_user_data(context);
526
p = sqlite_aggregate_context(context, sizeof(*p));
527
if( p==0 || argc<1 || argv[0]==0 ) return;
528
if( p->z==0 || (xCompare(argv[0],p->z)^mask)<0 ){
533
len = strlen(argv[0]);
534
if( len < sizeof(p->zBuf)-1 ){
538
p->z = sqliteMalloc( len+1 );
540
if( p->z==0 ) return;
542
strcpy(p->z, argv[0]);
545
static void minMaxFinalize(sqlite_func *context){
547
p = sqlite_aggregate_context(context, sizeof(*p));
549
sqlite_set_result_string(context, p->z, strlen(p->z));
551
if( p && !p->zBuf[0] ){
557
** This function registered all of the above C functions as SQL
558
** functions. This should be the only routine in this file with
561
void sqliteRegisterBuiltinFunctions(sqlite *db){
565
signed char dataType;
566
u8 argType; /* 0: none. 1: db 2: (-1) */
567
void (*xFunc)(sqlite_func*,int,const char**);
569
{ "min", -1, SQLITE_ARGS, 0, minmaxFunc },
570
{ "min", 0, 0, 0, 0 },
571
{ "max", -1, SQLITE_ARGS, 2, minmaxFunc },
572
{ "max", 0, 0, 2, 0 },
573
{ "typeof", 1, SQLITE_TEXT, 0, typeofFunc },
574
{ "length", 1, SQLITE_NUMERIC, 0, lengthFunc },
575
{ "substr", 3, SQLITE_TEXT, 0, substrFunc },
576
{ "abs", 1, SQLITE_NUMERIC, 0, absFunc },
577
{ "round", 1, SQLITE_NUMERIC, 0, roundFunc },
578
{ "round", 2, SQLITE_NUMERIC, 0, roundFunc },
579
{ "upper", 1, SQLITE_TEXT, 0, upperFunc },
580
{ "lower", 1, SQLITE_TEXT, 0, lowerFunc },
581
{ "coalesce", -1, SQLITE_ARGS, 0, ifnullFunc },
582
{ "coalesce", 0, 0, 0, 0 },
583
{ "coalesce", 1, 0, 0, 0 },
584
{ "ifnull", 2, SQLITE_ARGS, 0, ifnullFunc },
585
{ "random", -1, SQLITE_NUMERIC, 0, randomFunc },
586
{ "like", 2, SQLITE_NUMERIC, 0, likeFunc },
587
{ "glob", 2, SQLITE_NUMERIC, 0, globFunc },
588
{ "nullif", 2, SQLITE_ARGS, 0, nullifFunc },
589
{ "sqlite_version",0,SQLITE_TEXT, 0, versionFunc},
590
{ "quote", 1, SQLITE_ARGS, 0, quoteFunc },
591
{ "last_insert_rowid", 0, SQLITE_NUMERIC, 1, last_insert_rowid },
592
{ "change_count", 0, SQLITE_NUMERIC, 1, change_count },
593
{ "last_statement_change_count",
594
0, SQLITE_NUMERIC, 1, last_statement_change_count },
595
#ifdef SQLITE_SOUNDEX
596
{ "soundex", 1, SQLITE_TEXT, 0, soundexFunc},
599
{ "randstr", 2, SQLITE_TEXT, 0, randStr },
605
signed char dataType;
607
void (*xStep)(sqlite_func*,int,const char**);
608
void (*xFinalize)(sqlite_func*);
610
{ "min", 1, 0, 0, minmaxStep, minMaxFinalize },
611
{ "max", 1, 0, 2, minmaxStep, minMaxFinalize },
612
{ "sum", 1, SQLITE_NUMERIC, 0, sumStep, sumFinalize },
613
{ "avg", 1, SQLITE_NUMERIC, 0, sumStep, avgFinalize },
614
{ "count", 0, SQLITE_NUMERIC, 0, countStep, countFinalize },
615
{ "count", 1, SQLITE_NUMERIC, 0, countStep, countFinalize },
617
{ "stddev", 1, SQLITE_NUMERIC, 0, stdDevStep, stdDevFinalize },
620
static const char *azTypeFuncs[] = { "min", "max", "typeof" };
623
for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
624
void *pArg = aFuncs[i].argType==2 ? (void*)(-1) : db;
625
sqlite_create_function(db, aFuncs[i].zName,
626
aFuncs[i].nArg, aFuncs[i].xFunc, pArg);
627
if( aFuncs[i].xFunc ){
628
sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType);
631
for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
632
void *pArg = aAggs[i].argType==2 ? (void*)(-1) : db;
633
sqlite_create_aggregate(db, aAggs[i].zName,
634
aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, pArg);
635
sqlite_function_type(db, aAggs[i].zName, aAggs[i].dataType);
637
for(i=0; i<sizeof(azTypeFuncs)/sizeof(azTypeFuncs[0]); i++){
638
int n = strlen(azTypeFuncs[i]);
639
FuncDef *p = sqliteHashFind(&db->aFunc, azTypeFuncs[i], n);
645
sqliteRegisterDateTimeFunctions(db);