monotone

monotone Mtn Source Tree

Root/sqlite/func.c

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.1 2003/08/05 23:03:07 graydon Exp $
20*/
21#include <ctype.h>
22#include <math.h>
23#include <stdlib.h>
24#include <assert.h>
25#include "sqliteInt.h"
26
27/*
28** Implementation of the non-aggregate min() and max() functions
29*/
30static void minFunc(sqlite_func *context, int argc, const char **argv){
31 const char *zBest;
32 int i;
33
34 if( argc==0 ) return;
35 zBest = argv[0];
36 if( zBest==0 ) return;
37 for(i=1; i<argc; i++){
38 if( argv[i]==0 ) return;
39 if( sqliteCompare(argv[i], zBest)<0 ){
40 zBest = argv[i];
41 }
42 }
43 sqlite_set_result_string(context, zBest, -1);
44}
45static void maxFunc(sqlite_func *context, int argc, const char **argv){
46 const char *zBest;
47 int i;
48
49 if( argc==0 ) return;
50 zBest = argv[0];
51 if( zBest==0 ) return;
52 for(i=1; i<argc; i++){
53 if( argv[i]==0 ) return;
54 if( sqliteCompare(argv[i], zBest)>0 ){
55 zBest = argv[i];
56 }
57 }
58 sqlite_set_result_string(context, zBest, -1);
59}
60
61/*
62** Implementation of the length() function
63*/
64static void lengthFunc(sqlite_func *context, int argc, const char **argv){
65 const char *z;
66 int len;
67
68 assert( argc==1 );
69 z = argv[0];
70 if( z==0 ) return;
71#ifdef SQLITE_UTF8
72 for(len=0; *z; z++){ if( (0xc0&*z)!=0x80 ) len++; }
73#else
74 len = strlen(z);
75#endif
76 sqlite_set_result_int(context, len);
77}
78
79/*
80** Implementation of the abs() function
81*/
82static void absFunc(sqlite_func *context, int argc, const char **argv){
83 const char *z;
84 assert( argc==1 );
85 z = argv[0];
86 if( z==0 ) return;
87 if( z[0]=='-' && isdigit(z[1]) ) z++;
88 sqlite_set_result_string(context, z, -1);
89}
90
91/*
92** Implementation of the substr() function
93*/
94static void substrFunc(sqlite_func *context, int argc, const char **argv){
95 const char *z;
96#ifdef SQLITE_UTF8
97 const char *z2;
98 int i;
99#endif
100 int p1, p2, len;
101 assert( argc==3 );
102 z = argv[0];
103 if( z==0 ) return;
104 p1 = atoi(argv[1]?argv[1]:0);
105 p2 = atoi(argv[2]?argv[2]:0);
106#ifdef SQLITE_UTF8
107 for(len=0, z2=z; *z2; z2++){ if( (0xc0&*z2)!=0x80 ) len++; }
108#else
109 len = strlen(z);
110#endif
111 if( p1<0 ){
112 p1 += len;
113 if( p1<0 ){
114 p2 += p1;
115 p1 = 0;
116 }
117 }else if( p1>0 ){
118 p1--;
119 }
120 if( p1+p2>len ){
121 p2 = len-p1;
122 }
123#ifdef SQLITE_UTF8
124 for(i=0; i<p1; i++){
125 assert( z[i] );
126 if( (z[i]&0xc0)==0x80 ) p1++;
127 }
128 while( z[i] && (z[i]&0xc0)==0x80 ){ i++; p1++; }
129 for(; i<p1+p2; i++){
130 assert( z[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 = atof(argv[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 char *z;
161 int i;
162 if( argc<1 || argv[0]==0 ) return;
163 z = 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 char *z;
171 int i;
172 if( argc<1 || argv[0]==0 ) return;
173 z = 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 argument
183** non-NULL 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 sqlite_set_result_int(context, sqliteRandomInteger());
200}
201
202/*
203** Implementation of the last_insert_rowid() SQL function. The return
204** value is the same as the sqlite_last_insert_rowid() API function.
205*/
206static void last_insert_rowid(sqlite_func *context, int arg, const char **argv){
207 sqlite *db = sqlite_user_data(context);
208 sqlite_set_result_int(context, sqlite_last_insert_rowid(db));
209}
210
211/*
212** Implementation of the like() SQL function. This function implements
213** the build-in LIKE operator. The first argument to the function is the
214** string and the second argument is the pattern. So, the SQL statements:
215**
216** A LIKE B
217**
218** is implemented as like(A,B).
219*/
220static void likeFunc(sqlite_func *context, int arg, const char **argv){
221 if( argv[0]==0 || argv[1]==0 ) return;
222 sqlite_set_result_int(context, sqliteLikeCompare(argv[0], argv[1]));
223}
224
225/*
226** Implementation of the glob() SQL function. This function implements
227** the build-in GLOB operator. The first argument to the function is the
228** string and the second argument is the pattern. So, the SQL statements:
229**
230** A GLOB B
231**
232** is implemented as glob(A,B).
233*/
234static void globFunc(sqlite_func *context, int arg, const char **argv){
235 if( argv[0]==0 || argv[1]==0 ) return;
236 sqlite_set_result_int(context, sqliteGlobCompare(argv[0], argv[1]));
237}
238
239/*
240** Implementation of the NULLIF(x,y) function. The result is the first
241** argument if the arguments are different. The result is NULL if the
242** arguments are equal to each other.
243*/
244static void nullifFunc(sqlite_func *context, int argc, const char **argv){
245 if( argv[0]!=0 && sqliteCompare(argv[0],argv[1])!=0 ){
246 sqlite_set_result_string(context, argv[0], -1);
247 }
248}
249
250/*
251** Implementation of the VERSION(*) function. The result is the version
252** of the SQLite library that is running.
253*/
254static void versionFunc(sqlite_func *context, int argc, const char **argv){
255 sqlite_set_result_string(context, sqlite_version, -1);
256}
257
258#ifdef SQLITE_SOUNDEX
259/*
260** Compute the soundex encoding of a word.
261*/
262static void soundexFunc(sqlite_func *context, int argc, const char **argv){
263 char zResult[8];
264 const char *zIn;
265 int i, j;
266 static const unsigned char iCode[] = {
267 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
268 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
269 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
270 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
271 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
272 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
273 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
274 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
275 };
276 assert( argc==1 );
277 zIn = argv[0];
278 for(i=0; zIn[i] && !isalpha(zIn[i]); i++){}
279 if( zIn[i] ){
280 zResult[0] = toupper(zIn[i]);
281 for(j=1; j<4 && zIn[i]; i++){
282 int code = iCode[zIn[i]&0x7f];
283 if( code>0 ){
284 zResult[j++] = code + '0';
285 }
286 }
287 while( j<4 ){
288 zResult[j++] = '0';
289 }
290 zResult[j] = 0;
291 sqlite_set_result_string(context, zResult, 4);
292 }else{
293 sqlite_set_result_string(context, zResult, "?000", 4);
294 }
295}
296#endif
297
298#ifdef SQLITE_TEST
299/*
300** This function generates a string of random characters. Used for
301** generating test data.
302*/
303static void randStr(sqlite_func *context, int argc, const char **argv){
304 static const char zSrc[] =
305 "abcdefghijklmnopqrstuvwxyz"
306 "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
307 "0123456789"
308 ".-!,:*^+=_|?/<> ";
309 int iMin, iMax, n, r, i;
310 char zBuf[1000];
311 if( argc>=1 ){
312 iMin = atoi(argv[0]);
313 if( iMin<0 ) iMin = 0;
314 if( iMin>=sizeof(zBuf) ) iMin = sizeof(zBuf)-1;
315 }else{
316 iMin = 1;
317 }
318 if( argc>=2 ){
319 iMax = atoi(argv[1]);
320 if( iMax<iMin ) iMax = iMin;
321 if( iMax>=sizeof(zBuf) ) iMax = sizeof(zBuf);
322 }else{
323 iMax = 50;
324 }
325 n = iMin;
326 if( iMax>iMin ){
327 r = sqliteRandomInteger() & 0x7fffffff;
328 n += r%(iMax + 1 - iMin);
329 }
330 r = 0;
331 for(i=0; i<n; i++){
332 r = (r + sqliteRandomByte())% (sizeof(zSrc)-1);
333 zBuf[i] = zSrc[r];
334 }
335 zBuf[n] = 0;
336 sqlite_set_result_string(context, zBuf, n);
337}
338#endif
339
340/*
341** An instance of the following structure holds the context of a
342** sum() or avg() aggregate computation.
343*/
344typedef struct SumCtx SumCtx;
345struct SumCtx {
346 double sum; /* Sum of terms */
347 int cnt; /* Number of elements summed */
348};
349
350/*
351** Routines used to compute the sum or average.
352*/
353static void sumStep(sqlite_func *context, int argc, const char **argv){
354 SumCtx *p;
355 if( argc<1 ) return;
356 p = sqlite_aggregate_context(context, sizeof(*p));
357 if( p && argv[0] ){
358 p->sum += atof(argv[0]);
359 p->cnt++;
360 }
361}
362static void sumFinalize(sqlite_func *context){
363 SumCtx *p;
364 p = sqlite_aggregate_context(context, sizeof(*p));
365 sqlite_set_result_double(context, p ? p->sum : 0.0);
366}
367static void avgFinalize(sqlite_func *context){
368 SumCtx *p;
369 p = sqlite_aggregate_context(context, sizeof(*p));
370 if( p && p->cnt>0 ){
371 sqlite_set_result_double(context, p->sum/(double)p->cnt);
372 }
373}
374
375/*
376** An instance of the following structure holds the context of a
377** variance or standard deviation computation.
378*/
379typedef struct StdDevCtx StdDevCtx;
380struct StdDevCtx {
381 double sum; /* Sum of terms */
382 double sum2; /* Sum of the squares of terms */
383 int cnt; /* Number of terms counted */
384};
385
386#if 0 /* Omit because math library is required */
387/*
388** Routines used to compute the standard deviation as an aggregate.
389*/
390static void stdDevStep(sqlite_func *context, int argc, const char **argv){
391 StdDevCtx *p;
392 double x;
393 if( argc<1 ) return;
394 p = sqlite_aggregate_context(context, sizeof(*p));
395 if( p && argv[0] ){
396 x = atof(argv[0]);
397 p->sum += x;
398 p->sum2 += x*x;
399 p->cnt++;
400 }
401}
402static void stdDevFinalize(sqlite_func *context){
403 double rN = sqlite_aggregate_count(context);
404 StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p));
405 if( p && p->cnt>1 ){
406 double rCnt = cnt;
407 sqlite_set_result_double(context,
408 sqrt((p->sum2 - p->sum*p->sum/rCnt)/(rCnt-1.0)));
409 }
410}
411#endif
412
413/*
414** The following structure keeps track of state information for the
415** count() aggregate function.
416*/
417typedef struct CountCtx CountCtx;
418struct CountCtx {
419 int n;
420};
421
422/*
423** Routines to implement the count() aggregate function.
424*/
425static void countStep(sqlite_func *context, int argc, const char **argv){
426 CountCtx *p;
427 p = sqlite_aggregate_context(context, sizeof(*p));
428 if( (argc==0 || argv[0]) && p ){
429 p->n++;
430 }
431}
432static void countFinalize(sqlite_func *context){
433 CountCtx *p;
434 p = sqlite_aggregate_context(context, sizeof(*p));
435 sqlite_set_result_int(context, p ? p->n : 0);
436}
437
438/*
439** This function tracks state information for the min() and max()
440** aggregate functions.
441*/
442typedef struct MinMaxCtx MinMaxCtx;
443struct MinMaxCtx {
444 char *z; /* The best so far */
445 char zBuf[28]; /* Space that can be used for storage */
446};
447
448/*
449** Routines to implement min() and max() aggregate functions.
450*/
451static void minStep(sqlite_func *context, int argc, const char **argv){
452 MinMaxCtx *p;
453 p = sqlite_aggregate_context(context, sizeof(*p));
454 if( p==0 || argc<1 || argv[0]==0 ) return;
455 if( p->z==0 || sqliteCompare(argv[0],p->z)<0 ){
456 int len;
457 if( p->z && p->z!=p->zBuf ){
458 sqliteFree(p->z);
459 }
460 len = strlen(argv[0]);
461 if( len < sizeof(p->zBuf) ){
462 p->z = p->zBuf;
463 }else{
464 p->z = sqliteMalloc( len+1 );
465 if( p->z==0 ) return;
466 }
467 strcpy(p->z, argv[0]);
468 }
469}
470static void maxStep(sqlite_func *context, int argc, const char **argv){
471 MinMaxCtx *p;
472 p = sqlite_aggregate_context(context, sizeof(*p));
473 if( p==0 || argc<1 || argv[0]==0 ) return;
474 if( p->z==0 || sqliteCompare(argv[0],p->z)>0 ){
475 int len;
476 if( p->z && p->z!=p->zBuf ){
477 sqliteFree(p->z);
478 }
479 len = strlen(argv[0]);
480 if( len < sizeof(p->zBuf) ){
481 p->z = p->zBuf;
482 }else{
483 p->z = sqliteMalloc( len+1 );
484 if( p->z==0 ) return;
485 }
486 strcpy(p->z, argv[0]);
487 }
488}
489static void minMaxFinalize(sqlite_func *context){
490 MinMaxCtx *p;
491 p = sqlite_aggregate_context(context, sizeof(*p));
492 if( p && p->z ){
493 sqlite_set_result_string(context, p->z, strlen(p->z));
494 }
495 if( p && p->z && p->z!=p->zBuf ){
496 sqliteFree(p->z);
497 }
498}
499
500/*
501** This function registered all of the above C functions as SQL
502** functions. This should be the only routine in this file with
503** external linkage.
504*/
505void sqliteRegisterBuiltinFunctions(sqlite *db){
506 static struct {
507 char *zName;
508 int nArg;
509 int dataType;
510 void (*xFunc)(sqlite_func*,int,const char**);
511 } aFuncs[] = {
512 { "min", -1, SQLITE_ARGS, minFunc },
513 { "min", 0, 0, 0 },
514 { "max", -1, SQLITE_ARGS, maxFunc },
515 { "max", 0, 0, 0 },
516 { "length", 1, SQLITE_NUMERIC, lengthFunc },
517 { "substr", 3, SQLITE_TEXT, substrFunc },
518 { "abs", 1, SQLITE_NUMERIC, absFunc },
519 { "round", 1, SQLITE_NUMERIC, roundFunc },
520 { "round", 2, SQLITE_NUMERIC, roundFunc },
521 { "upper", 1, SQLITE_TEXT, upperFunc },
522 { "lower", 1, SQLITE_TEXT, lowerFunc },
523 { "coalesce", -1, SQLITE_ARGS, ifnullFunc },
524 { "coalesce", 0, 0, 0 },
525 { "coalesce", 1, 0, 0 },
526 { "ifnull", 2, SQLITE_ARGS, ifnullFunc },
527 { "random", -1, SQLITE_NUMERIC, randomFunc },
528 { "like", 2, SQLITE_NUMERIC, likeFunc },
529 { "glob", 2, SQLITE_NUMERIC, globFunc },
530 { "nullif", 2, SQLITE_ARGS, nullifFunc },
531 { "sqlite_version",0,SQLITE_TEXT, versionFunc},
532#ifdef SQLITE_SOUNDEX
533 { "soundex", 1, SQLITE_TEXT, soundexFunc},
534#endif
535#ifdef SQLITE_TEST
536 { "randstr", 2, SQLITE_TEXT, randStr },
537#endif
538 };
539 static struct {
540 char *zName;
541 int nArg;
542 int dataType;
543 void (*xStep)(sqlite_func*,int,const char**);
544 void (*xFinalize)(sqlite_func*);
545 } aAggs[] = {
546 { "min", 1, 0, minStep, minMaxFinalize },
547 { "max", 1, 0, maxStep, minMaxFinalize },
548 { "sum", 1, SQLITE_NUMERIC, sumStep, sumFinalize },
549 { "avg", 1, SQLITE_NUMERIC, sumStep, avgFinalize },
550 { "count", 0, SQLITE_NUMERIC, countStep, countFinalize },
551 { "count", 1, SQLITE_NUMERIC, countStep, countFinalize },
552#if 0
553 { "stddev", 1, SQLITE_NUMERIC, stdDevStep, stdDevFinalize },
554#endif
555 };
556 int i;
557
558 for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
559 sqlite_create_function(db, aFuncs[i].zName,
560 aFuncs[i].nArg, aFuncs[i].xFunc, 0);
561 if( aFuncs[i].xFunc ){
562 sqlite_function_type(db, aFuncs[i].zName, aFuncs[i].dataType);
563 }
564 }
565 sqlite_create_function(db, "last_insert_rowid", 0,
566 last_insert_rowid, db);
567 sqlite_function_type(db, "last_insert_rowid", SQLITE_NUMERIC);
568 for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
569 sqlite_create_aggregate(db, aAggs[i].zName,
570 aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, 0);
571 sqlite_function_type(db, aAggs[i].zName, aAggs[i].dataType);
572 }
573}

Archive Download this file

Branches

Tags

Quick Links:     www.monotone.ca    -     Downloads    -     Documentation    -     Wiki    -     Code Forge    -     Build Status