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$
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*/
31static void minmaxFunc(sqlite_func *context, int argc, const char **argv){
32  const char *zBest;
33  int i;
34  int (*xCompare)(const char*, const char*);
35  int mask;    /* 0 for min() or 0xffffffff for max() */
36
37  if( argc==0 ) return;
38  mask = (int)sqlite_user_data(context);
39  zBest = argv[0];
40  if( zBest==0 ) return;
41  if( argv[1][0]=='n' ){
42    xCompare = sqliteCompare;
43  }else{
44    xCompare = strcmp;
45  }
46  for(i=2; i<argc; i+=2){
47    if( argv[i]==0 ) return;
48    if( (xCompare(argv[i], zBest)^mask)<0 ){
49      zBest = argv[i];
50    }
51  }
52  sqlite_set_result_string(context, zBest, -1);
53}
54
55/*
56** Return the type of the argument.
57*/
58static void typeofFunc(sqlite_func *context, int argc, const char **argv){
59  assert( argc==2 );
60  sqlite_set_result_string(context, argv[1], -1);
61}
62
63/*
64** Implementation of the length() function
65*/
66static void lengthFunc(sqlite_func *context, int argc, const char **argv){
67  const char *z;
68  int len;
69
70  assert( argc==1 );
71  z = argv[0];
72  if( z==0 ) return;
73#ifdef SQLITE_UTF8
74  for(len=0; *z; z++){ if( (0xc0&*z)!=0x80 ) len++; }
75#else
76  len = strlen(z);
77#endif
78  sqlite_set_result_int(context, len);
79}
80
81/*
82** Implementation of the abs() function
83*/
84static void absFunc(sqlite_func *context, int argc, const char **argv){
85  const char *z;
86  assert( argc==1 );
87  z = argv[0];
88  if( z==0 ) return;
89  if( z[0]=='-' && isdigit(z[1]) ) z++;
90  sqlite_set_result_string(context, z, -1);
91}
92
93/*
94** Implementation of the substr() function
95*/
96static void substrFunc(sqlite_func *context, int argc, const char **argv){
97  const char *z;
98#ifdef SQLITE_UTF8
99  const char *z2;
100  int i;
101#endif
102  int p1, p2, len;
103  assert( argc==3 );
104  z = argv[0];
105  if( z==0 ) return;
106  p1 = atoi(argv[1]?argv[1]:0);
107  p2 = atoi(argv[2]?argv[2]:0);
108#ifdef SQLITE_UTF8
109  for(len=0, z2=z; *z2; z2++){ if( (0xc0&*z2)!=0x80 ) len++; }
110#else
111  len = strlen(z);
112#endif
113  if( p1<0 ){
114    p1 += len;
115    if( p1<0 ){
116      p2 += p1;
117      p1 = 0;
118    }
119  }else if( p1>0 ){
120    p1--;
121  }
122  if( p1+p2>len ){
123    p2 = len-p1;
124  }
125#ifdef SQLITE_UTF8
126  for(i=0; i<p1 && z[i]; i++){
127    if( (z[i]&0xc0)==0x80 ) p1++;
128  }
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++;
132  }
133  while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p2++; }
134#endif
135  if( p2<0 ) p2 = 0;
136  sqlite_set_result_string(context, &z[p1], p2);
137}
138
139/*
140** Implementation of the round() function
141*/
142static void roundFunc(sqlite_func *context, int argc, const char **argv){
143  int n;
144  double r;
145  char zBuf[100];
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;
149  if( n>30 ) n = 30;
150  if( n<0 ) n = 0;
151  r = sqliteAtoF(argv[0], 0);
152  sprintf(zBuf,"%.*f",n,r);
153  sqlite_set_result_string(context, zBuf, -1);
154}
155
156/*
157** Implementation of the upper() and lower() SQL functions.
158*/
159static void upperFunc(sqlite_func *context, int argc, const char **argv){
160  unsigned char *z;
161  int i;
162  if( argc<1 || argv[0]==0 ) return;
163  z = (unsigned char*)sqlite_set_result_string(context, argv[0], -1);
164  if( z==0 ) return;
165  for(i=0; z[i]; i++){
166    if( islower(z[i]) ) z[i] = toupper(z[i]);
167  }
168}
169static void lowerFunc(sqlite_func *context, int argc, const char **argv){
170  unsigned char *z;
171  int i;
172  if( argc<1 || argv[0]==0 ) return;
173  z = (unsigned char*)sqlite_set_result_string(context, argv[0], -1);
174  if( z==0 ) return;
175  for(i=0; z[i]; i++){
176    if( isupper(z[i]) ) z[i] = tolower(z[i]);
177  }
178}
179
180/*
181** Implementation of the IFNULL(), NVL(), and COALESCE() functions.
182** All three do the same thing.  They return the first non-NULL
183** argument.
184*/
185static void ifnullFunc(sqlite_func *context, int argc, const char **argv){
186  int i;
187  for(i=0; i<argc; i++){
188    if( argv[i] ){
189      sqlite_set_result_string(context, argv[i], -1);
190      break;
191    }
192  }
193}
194
195/*
196** Implementation of random().  Return a random integer.
197*/
198static void randomFunc(sqlite_func *context, int argc, const char **argv){
199  int r;
200  sqliteRandomness(sizeof(r), &r);
201  sqlite_set_result_int(context, r);
202}
203
204/*
205** Implementation of the last_insert_rowid() SQL function.  The return
206** value is the same as the sqlite_last_insert_rowid() API function.
207*/
208static 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));
211}
212
213/*
214** Implementation of the change_count() SQL function.  The return
215** value is the same as the sqlite_changes() API function.
216*/
217static 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));
220}
221
222/*
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.
225*/
226static void last_statement_change_count(sqlite_func *context, int arg,
227                                        const char **argv){
228  sqlite *db = sqlite_user_data(context);
229  sqlite_set_result_int(context, sqlite_last_statement_changes(db));
230}
231
232/*
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:
236**
237**       A LIKE B
238**
239** is implemented as like(A,B).
240*/
241static 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]));
246}
247
248/*
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:
252**
253**       A GLOB B
254**
255** is implemented as glob(A,B).
256*/
257static 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]));
262}
263
264/*
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.
268*/
269static 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);
272  }
273}
274
275/*
276** Implementation of the VERSION(*) function.  The result is the version
277** of the SQLite library that is running.
278*/
279static void versionFunc(sqlite_func *context, int argc, const char **argv){
280  sqlite_set_result_string(context, sqlite_version, -1);
281}
282
283/*
284** EXPERIMENTAL - This is not an official function.  The interface may
285** change.  This function may disappear.  Do not write code that depends
286** on this function.
287**
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.
293*/
294static void quoteFunc(sqlite_func *context, int argc, const char **argv){
295  if( argc<1 ) return;
296  if( argv[0]==0 ){
297    sqlite_set_result_string(context, "NULL", 4);
298  }else if( sqliteIsNumber(argv[0]) ){
299    sqlite_set_result_string(context, argv[0], -1);
300  }else{
301    int i,j,n;
302    char *z;
303    for(i=n=0; argv[0][i]; i++){ if( argv[0][i]=='\'' ) n++; }
304    z = sqliteMalloc( i+n+3 );
305    if( z==0 ) return;
306    z[0] = '\'';
307    for(i=0, j=1; argv[0][i]; i++){
308      z[j++] = argv[0][i];
309      if( argv[0][i]=='\'' ){
310        z[j++] = '\'';
311      }
312    }
313    z[j++] = '\'';
314    z[j] = 0;
315    sqlite_set_result_string(context, z, j);
316    sqliteFree(z);
317  }
318}
319
320#ifdef SQLITE_SOUNDEX
321/*
322** Compute the soundex encoding of a word.
323*/
324static void soundexFunc(sqlite_func *context, int argc, const char **argv){
325  char zResult[8];
326  const char *zIn;
327  int i, j;
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,
337  };
338  assert( argc==1 );
339  zIn = argv[0];
340  for(i=0; zIn[i] && !isalpha(zIn[i]); i++){}
341  if( zIn[i] ){
342    zResult[0] = toupper(zIn[i]);
343    for(j=1; j<4 && zIn[i]; i++){
344      int code = iCode[zIn[i]&0x7f];
345      if( code>0 ){
346        zResult[j++] = code + '0';
347      }
348    }
349    while( j<4 ){
350      zResult[j++] = '0';
351    }
352    zResult[j] = 0;
353    sqlite_set_result_string(context, zResult, 4);
354  }else{
355    sqlite_set_result_string(context, "?000", 4);
356  }
357}
358#endif
359
360#ifdef SQLITE_TEST
361/*
362** This function generates a string of random characters.  Used for
363** generating test data.
364*/
365static void randStr(sqlite_func *context, int argc, const char **argv){
366  static const unsigned char zSrc[] =
367     "abcdefghijklmnopqrstuvwxyz"
368     "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
369     "0123456789"
370     ".-!,:*^+=_|?/<> ";
371  int iMin, iMax, n, r, i;
372  unsigned char zBuf[1000];
373  if( argc>=1 ){
374    iMin = atoi(argv[0]);
375    if( iMin<0 ) iMin = 0;
376    if( iMin>=sizeof(zBuf) ) iMin = sizeof(zBuf)-1;
377  }else{
378    iMin = 1;
379  }
380  if( argc>=2 ){
381    iMax = atoi(argv[1]);
382    if( iMax<iMin ) iMax = iMin;
383    if( iMax>=sizeof(zBuf) ) iMax = sizeof(zBuf)-1;
384  }else{
385    iMax = 50;
386  }
387  n = iMin;
388  if( iMax>iMin ){
389    sqliteRandomness(sizeof(r), &r);
390    r &= 0x7fffffff;
391    n += r%(iMax + 1 - iMin);
392  }
393  assert( n<sizeof(zBuf) );
394  sqliteRandomness(n, zBuf);
395  for(i=0; i<n; i++){
396    zBuf[i] = zSrc[zBuf[i]%(sizeof(zSrc)-1)];
397  }
398  zBuf[n] = 0;
399  sqlite_set_result_string(context, zBuf, n);
400}
401#endif
402
403/*
404** An instance of the following structure holds the context of a
405** sum() or avg() aggregate computation.
406*/
407typedef struct SumCtx SumCtx;
408struct SumCtx {
409  double sum;     /* Sum of terms */
410  int cnt;        /* Number of elements summed */
411};
412
413/*
414** Routines used to compute the sum or average.
415*/
416static void sumStep(sqlite_func *context, int argc, const char **argv){
417  SumCtx *p;
418  if( argc<1 ) return;
419  p = sqlite_aggregate_context(context, sizeof(*p));
420  if( p && argv[0] ){
421    p->sum += sqliteAtoF(argv[0], 0);
422    p->cnt++;
423  }
424}
425static void sumFinalize(sqlite_func *context){
426  SumCtx *p;
427  p = sqlite_aggregate_context(context, sizeof(*p));
428  sqlite_set_result_double(context, p ? p->sum : 0.0);
429}
430static void avgFinalize(sqlite_func *context){
431  SumCtx *p;
432  p = sqlite_aggregate_context(context, sizeof(*p));
433  if( p && p->cnt>0 ){
434    sqlite_set_result_double(context, p->sum/(double)p->cnt);
435  }
436}
437
438/*
439** An instance of the following structure holds the context of a
440** variance or standard deviation computation.
441*/
442typedef struct StdDevCtx StdDevCtx;
443struct StdDevCtx {
444  double sum;     /* Sum of terms */
445  double sum2;    /* Sum of the squares of terms */
446  int cnt;        /* Number of terms counted */
447};
448
449#if 0   /* Omit because math library is required */
450/*
451** Routines used to compute the standard deviation as an aggregate.
452*/
453static void stdDevStep(sqlite_func *context, int argc, const char **argv){
454  StdDevCtx *p;
455  double x;
456  if( argc<1 ) return;
457  p = sqlite_aggregate_context(context, sizeof(*p));
458  if( p && argv[0] ){
459    x = sqliteAtoF(argv[0], 0);
460    p->sum += x;
461    p->sum2 += x*x;
462    p->cnt++;
463  }
464}
465static void stdDevFinalize(sqlite_func *context){
466  double rN = sqlite_aggregate_count(context);
467  StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p));
468  if( p && p->cnt>1 ){
469    double rCnt = cnt;
470    sqlite_set_result_double(context,
471       sqrt((p->sum2 - p->sum*p->sum/rCnt)/(rCnt-1.0)));
472  }
473}
474#endif
475
476/*
477** The following structure keeps track of state information for the
478** count() aggregate function.
479*/
480typedef struct CountCtx CountCtx;
481struct CountCtx {
482  int n;
483};
484
485/*
486** Routines to implement the count() aggregate function.
487*/
488static void countStep(sqlite_func *context, int argc, const char **argv){
489  CountCtx *p;
490  p = sqlite_aggregate_context(context, sizeof(*p));
491  if( (argc==0 || argv[0]) && p ){
492    p->n++;
493  }
494}
495static void countFinalize(sqlite_func *context){
496  CountCtx *p;
497  p = sqlite_aggregate_context(context, sizeof(*p));
498  sqlite_set_result_int(context, p ? p->n : 0);
499}
500
501/*
502** This function tracks state information for the min() and max()
503** aggregate functions.
504*/
505typedef struct MinMaxCtx MinMaxCtx;
506struct MinMaxCtx {
507  char *z;         /* The best so far */
508  char zBuf[28];   /* Space that can be used for storage */
509};
510
511/*
512** Routines to implement min() and max() aggregate functions.
513*/
514static void minmaxStep(sqlite_func *context, int argc, const char **argv){
515  MinMaxCtx *p;
516  int (*xCompare)(const char*, const char*);
517  int mask;    /* 0 for min() or 0xffffffff for max() */
518
519  assert( argc==2 );
520  if( argv[0]==0 ) return;  /* Ignore NULL values */
521  if( argv[1][0]=='n' ){
522    xCompare = sqliteCompare;
523  }else{
524    xCompare = strcmp;
525  }
526  mask = (int)sqlite_user_data(context);
527  assert( mask==0 || mask==-1 );
528  p = sqlite_aggregate_context(context, sizeof(*p));
529  if( p==0 || argc<1 ) return;
530  if( p->z==0 || (xCompare(argv[0],p->z)^mask)<0 ){
531    int len;
532    if( p->zBuf[0] ){
533      sqliteFree(p->z);
534    }
535    len = strlen(argv[0]);
536    if( len < sizeof(p->zBuf)-1 ){
537      p->z = &p->zBuf[1];
538      p->zBuf[0] = 0;
539    }else{
540      p->z = sqliteMalloc( len+1 );
541      p->zBuf[0] = 1;
542      if( p->z==0 ) return;
543    }
544    strcpy(p->z, argv[0]);
545  }
546}
547static void minMaxFinalize(sqlite_func *context){
548  MinMaxCtx *p;
549  p = sqlite_aggregate_context(context, sizeof(*p));
550  if( p && p->z && p->zBuf[0]<2 ){
551    sqlite_set_result_string(context, p->z, strlen(p->z));
552  }
553  if( p && p->zBuf[0] ){
554    sqliteFree(p->z);
555  }
556}
557
558/*
559** This function registered all of the above C functions as SQL
560** functions.  This should be the only routine in this file with
561** external linkage.
562*/
563void sqliteRegisterBuiltinFunctions(sqlite *db){
564  static struct {
565     char *zName;
566     signed char nArg;
567     signed char dataType;
568     u8 argType;               /* 0: none.  1: db  2: (-1) */
569     void (*xFunc)(sqlite_func*,int,const char**);
570  } aFuncs[] = {
571    { "min",       -1, SQLITE_ARGS,    0, minmaxFunc },
572    { "min",        0, 0,              0, 0          },
573    { "max",       -1, SQLITE_ARGS,    2, minmaxFunc },
574    { "max",        0, 0,              2, 0          },
575    { "typeof",     1, SQLITE_TEXT,    0, typeofFunc },
576    { "length",     1, SQLITE_NUMERIC, 0, lengthFunc },
577    { "substr",     3, SQLITE_TEXT,    0, substrFunc },
578    { "abs",        1, SQLITE_NUMERIC, 0, absFunc    },
579    { "round",      1, SQLITE_NUMERIC, 0, roundFunc  },
580    { "round",      2, SQLITE_NUMERIC, 0, roundFunc  },
581    { "upper",      1, SQLITE_TEXT,    0, upperFunc  },
582    { "lower",      1, SQLITE_TEXT,    0, lowerFunc  },
583    { "coalesce",  -1, SQLITE_ARGS,    0, ifnullFunc },
584    { "coalesce",   0, 0,              0, 0          },
585    { "coalesce",   1, 0,              0, 0          },
586    { "ifnull",     2, SQLITE_ARGS,    0, ifnullFunc },
587    { "random",    -1, SQLITE_NUMERIC, 0, randomFunc },
588    { "like",       2, SQLITE_NUMERIC, 0, likeFunc   },
589    { "glob",       2, SQLITE_NUMERIC, 0, globFunc   },
590    { "nullif",     2, SQLITE_ARGS,    0, nullifFunc },
591    { "sqlite_version",0,SQLITE_TEXT,  0, versionFunc},
592    { "quote",      1, SQLITE_ARGS,    0, quoteFunc  },
593    { "last_insert_rowid", 0, SQLITE_NUMERIC, 1, last_insert_rowid },
594    { "change_count",      0, SQLITE_NUMERIC, 1, change_count      },
595    { "last_statement_change_count",
596                           0, SQLITE_NUMERIC, 1, last_statement_change_count },
597#ifdef SQLITE_SOUNDEX
598    { "soundex",    1, SQLITE_TEXT,    0, soundexFunc},
599#endif
600#ifdef SQLITE_TEST
601    { "randstr",    2, SQLITE_TEXT,    0, randStr    },
602#endif
603  };
604  static struct {
605    char *zName;
606    signed char nArg;
607    signed char dataType;
608    u8 argType;
609    void (*xStep)(sqlite_func*,int,const char**);
610    void (*xFinalize)(sqlite_func*);
611  } aAggs[] = {
612    { "min",    1, 0,              0, minmaxStep,   minMaxFinalize },
613    { "max",    1, 0,              2, minmaxStep,   minMaxFinalize },
614    { "sum",    1, SQLITE_NUMERIC, 0, sumStep,      sumFinalize    },
615    { "avg",    1, SQLITE_NUMERIC, 0, sumStep,      avgFinalize    },
616    { "count",  0, SQLITE_NUMERIC, 0, countStep,    countFinalize  },
617    { "count",  1, SQLITE_NUMERIC, 0, countStep,    countFinalize  },
618#if 0
619    { "stddev", 1, SQLITE_NUMERIC, 0, stdDevStep,   stdDevFinalize },
620#endif
621  };
622  static const char *azTypeFuncs[] = { "min", "max", "typeof" };
623  int i;
624
625  for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
626    void *pArg;
627    switch( aFuncs[i].argType ){
628      case 0:  pArg = 0;           break;
629      case 1:  pArg = db;          break;
630      case 2:  pArg = (void*)(-1); break;
631    }
632    sqlite_create_function(db, aFuncs[i].zName,
633           aFuncs[i].nArg, aFuncs[i].xFunc, pArg);
634    if( aFuncs[i].xFunc ){
635      sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType);
636    }
637  }
638  for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
639    void *pArg;
640    switch( aAggs[i].argType ){
641      case 0:  pArg = 0;           break;
642      case 1:  pArg = db;          break;
643      case 2:  pArg = (void*)(-1); break;
644    }
645    sqlite_create_aggregate(db, aAggs[i].zName,
646           aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, pArg);
647    sqlite_function_type(db, aAggs[i].zName, aAggs[i].dataType);
648  }
649  for(i=0; i<sizeof(azTypeFuncs)/sizeof(azTypeFuncs[0]); i++){
650    int n = strlen(azTypeFuncs[i]);
651    FuncDef *p = sqliteHashFind(&db->aFunc, azTypeFuncs[i], n);
652    while( p ){
653      p->includeTypes = 1;
654      p = p->pNext;
655    }
656  }
657  sqliteRegisterDateTimeFunctions(db);
658}
659