blob: 0916e480fc788ed3f5d51a34cc2921ccf239d650 [file] [log] [blame]
drhdc04c582002-02-24 01:55:15 +00001/*
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**
drh3212e182002-02-28 00:46:26 +000019** $Id: func.c,v 1.7 2002/02/28 00:46:26 drh Exp $
drhdc04c582002-02-24 01:55:15 +000020*/
21#include <ctype.h>
drhd3a149e2002-02-24 17:12:53 +000022#include <math.h>
23#include <stdlib.h>
drh0bce8352002-02-28 00:41:10 +000024#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 zBest = argv[0];
35 for(i=1; i<argc; i++){
36 if( sqliteCompare(argv[i], zBest)<0 ){
37 zBest = argv[i];
38 }
39 }
40 sqlite_set_result_string(context, zBest, -1);
41}
42static void maxFunc(sqlite_func *context, int argc, const char **argv){
43 const char *zBest;
44 int i;
45
46 zBest = argv[0];
47 for(i=1; i<argc; i++){
48 if( sqliteCompare(argv[i], zBest)>0 ){
49 zBest = argv[i];
50 }
51 }
52 sqlite_set_result_string(context, zBest, -1);
53}
54
55/*
56** Implementation of the length() function
57*/
58static void lengthFunc(sqlite_func *context, int argc, const char **argv){
59 const char *z;
60 int len;
61
62 assert( argc==1 );
63 z = argv[0];
64 if( z==0 ){
65 len = 0;
66 }else{
67#ifdef SQLITE_UTF8
68 for(len=0; *z; z++){ if( (0xc0&*z)!=0x80 ) len++; }
69#else
70 len = strlen(z);
71#endif
72 }
73 sqlite_set_result_int(context, len);
74}
75
76/*
77** Implementation of the abs() function
78*/
79static void absFunc(sqlite_func *context, int argc, const char **argv){
80 const char *z;
81 assert( argc==1 );
82 z = argv[0];
83 if( z && z[0]=='-' && isdigit(z[1]) ) z++;
84 sqlite_set_result_string(context, z, -1);
85}
86
87/*
88** Implementation of the substr() function
89*/
90static void substrFunc(sqlite_func *context, int argc, const char **argv){
91 const char *z;
92#ifdef SQLITE_UTF8
93 const char *z2;
94 int i;
95#endif
96 int p1, p2, len;
97 assert( argc==3 );
98 z = argv[0];
99 if( z==0 ) return;
100 p1 = atoi(argv[1]?argv[1]:0);
101 p2 = atoi(argv[2]?argv[2]:0);
102#ifdef SQLITE_UTF8
103 for(len=0, z2=z; *z2; z2++){ if( (0xc0&*z)!=0x80 ) len++; }
104#else
105 len = strlen(z);
106#endif
107 if( p1<0 ){
108 p1 = len-p1;
109 }else if( p1>0 ){
110 p1--;
111 }
112 if( p1+p2>len ){
113 p2 = len-p1;
114 }
115#ifdef SQLITE_UTF8
116 for(i=0; i<p1; i++){
117 assert( z[i] );
118 if( (z[i]&0xc0)!=0x80 ) p1++;
119 }
120 for(; i<p1+p2; i++){
121 assert( z[i] );
122 if( (z[i]&0xc0)!=0x80 ) p2++;
123 }
124#endif
125 sqlite_set_result_string(context, &z[p1], p2);
126}
127
128/*
129** Implementation of the round() function
130*/
131static void roundFunc(sqlite_func *context, int argc, const char **argv){
132 int n;
133 double r;
134 char zBuf[100];
135 assert( argc==1 || argc==2 );
136 n = argc==2 && argv[1] ? atoi(argv[1]) : 0;
137 if( n>30 ) n = 30;
138 if( n<0 ) n = 0;
139 r = argv[0] ? atof(argv[0]) : 0.0;
140 sprintf(zBuf,"%.*f",n,r);
141 sqlite_set_result_string(context, zBuf, -1);
142}
drhdc04c582002-02-24 01:55:15 +0000143
144/*
145** Implementation of the upper() and lower() SQL functions.
146*/
drh1350b032002-02-27 19:00:20 +0000147static void upperFunc(sqlite_func *context, int argc, const char **argv){
drhdc04c582002-02-24 01:55:15 +0000148 char *z;
149 int i;
150 if( argc<1 || argv[0]==0 ) return;
151 z = sqlite_set_result_string(context, argv[0], -1);
152 if( z==0 ) return;
153 for(i=0; z[i]; i++){
154 if( islower(z[i]) ) z[i] = toupper(z[i]);
155 }
156}
drh1350b032002-02-27 19:00:20 +0000157static void lowerFunc(sqlite_func *context, int argc, const char **argv){
drhdc04c582002-02-24 01:55:15 +0000158 char *z;
159 int i;
160 if( argc<1 || argv[0]==0 ) return;
161 z = sqlite_set_result_string(context, argv[0], -1);
162 if( z==0 ) return;
163 for(i=0; z[i]; i++){
164 if( isupper(z[i]) ) z[i] = tolower(z[i]);
165 }
166}
167
168/*
drh3212e182002-02-28 00:46:26 +0000169** Implementation of the IFNULL() and NVL() functions. (both do the
170** same thing. They return their first argument if it is not NULL or
171** their second argument if the first is NULL.
172*/
173static void ifnullFunc(sqlite_func *context, int argc, const char **argv){
174 const char *z;
175 assert( argc==2 );
176 z = argv[0] ? argv[0] : argv[1];
177 sqlite_set_result_string(context, z, -1);
178}
179
180/*
drhd3a149e2002-02-24 17:12:53 +0000181** An instance of the following structure holds the context of a
drhdd5baa92002-02-27 19:50:59 +0000182** sum() or avg() aggregate computation.
183*/
184typedef struct SumCtx SumCtx;
185struct SumCtx {
186 double sum; /* Sum of terms */
187};
188
189/*
190** Routines used to compute the sum or average.
191*/
192static void sumStep(sqlite_func *context, int argc, const char **argv){
193 SumCtx *p;
194 double x;
195 if( argc<1 ) return;
196 p = sqlite_aggregate_context(context, sizeof(*p));
197 if( p==0 ) return;
198 x = argv[0] ? atof(argv[0]) : 0.0;
199 p->sum += x;
200}
201static void sumFinalize(sqlite_func *context){
202 SumCtx *p;
203 p = sqlite_aggregate_context(context, sizeof(*p));
204 if( p ){
205 sqlite_set_result_double(context, p->sum);
206 }
207}
208static void avgFinalize(sqlite_func *context){
209 SumCtx *p;
210 double rN;
211 p = sqlite_aggregate_context(context, sizeof(*p));
212 rN = sqlite_aggregate_count(context);
213 if( p && rN>0.0 ){
214 sqlite_set_result_double(context, p->sum/rN);
215 }
216}
217
218/*
219** An instance of the following structure holds the context of a
drha2ed5602002-02-26 23:55:31 +0000220** variance or standard deviation computation.
drhd3a149e2002-02-24 17:12:53 +0000221*/
222typedef struct StdDevCtx StdDevCtx;
223struct StdDevCtx {
224 double sum; /* Sum of terms */
225 double sum2; /* Sum of the squares of terms */
drhd3a149e2002-02-24 17:12:53 +0000226};
227
228/*
229** Routines used to compute the standard deviation as an aggregate.
230*/
drh1350b032002-02-27 19:00:20 +0000231static void stdDevStep(sqlite_func *context, int argc, const char **argv){
drhd3a149e2002-02-24 17:12:53 +0000232 StdDevCtx *p;
233 double x;
drh1350b032002-02-27 19:00:20 +0000234 if( argc<1 ) return;
235 p = sqlite_aggregate_context(context, sizeof(*p));
236 if( p==0 ) return;
drhdd5baa92002-02-27 19:50:59 +0000237 x = argv[0] ? atof(argv[0]) : 0.0;
drhd3a149e2002-02-24 17:12:53 +0000238 p->sum += x;
239 p->sum2 += x*x;
drhd3a149e2002-02-24 17:12:53 +0000240}
drh1350b032002-02-27 19:00:20 +0000241static void stdDevFinalize(sqlite_func *context){
drhdd5baa92002-02-27 19:50:59 +0000242 double rN = sqlite_aggregate_count(context);
drh1350b032002-02-27 19:00:20 +0000243 StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p));
drhdd5baa92002-02-27 19:50:59 +0000244 if( p && rN>1.0 ){
drhd3a149e2002-02-24 17:12:53 +0000245 sqlite_set_result_double(context,
246 sqrt((p->sum2 - p->sum*p->sum/rN)/(rN-1.0)));
247 }
drhd3a149e2002-02-24 17:12:53 +0000248}
249
drh0bce8352002-02-28 00:41:10 +0000250/*
251** The following structure keeps track of state information for the
252** count() aggregate function.
253*/
254typedef struct CountCtx CountCtx;
255struct CountCtx {
256 int n;
257};
drhdd5baa92002-02-27 19:50:59 +0000258
drh0bce8352002-02-28 00:41:10 +0000259/*
260** Routines to implement the count() aggregate function.
261*/
262static void countStep(sqlite_func *context, int argc, const char **argv){
263 CountCtx *p;
264 p = sqlite_aggregate_context(context, sizeof(*p));
265 if( (argc==0 || argv[0]) && p ){
266 p->n++;
267 }
268}
269static void countFinalize(sqlite_func *context){
270 CountCtx *p;
271 p = sqlite_aggregate_context(context, sizeof(*p));
272 if( p ){
273 sqlite_set_result_int(context, p->n);
274 }
275}
276
277/*
278** This function tracks state information for the min() and max()
279** aggregate functions.
280*/
281typedef struct MinMaxCtx MinMaxCtx;
282struct MinMaxCtx {
283 char *z; /* The best so far */
284 char zBuf[28]; /* Space that can be used for storage */
285};
286
287/*
288** Routines to implement min() and max() aggregate functions.
289*/
290static void minStep(sqlite_func *context, int argc, const char **argv){
291 MinMaxCtx *p;
292 p = sqlite_aggregate_context(context, sizeof(*p));
293 if( p==0 || argc<1 ) return;
294 if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)<0 ){
295 if( p->z && p->z!=p->zBuf ){
296 sqliteFree(p->z);
297 }
298 if( argv[0] ){
299 int len = strlen(argv[0]);
300 if( len < sizeof(p->zBuf) ){
301 p->z = p->zBuf;
302 }else{
303 p->z = sqliteMalloc( len+1 );
304 if( p->z==0 ) return;
305 }
306 strcpy(p->z, argv[0]);
307 }else{
308 p->z = 0;
309 }
310 }
311}
312static void maxStep(sqlite_func *context, int argc, const char **argv){
313 MinMaxCtx *p;
314 p = sqlite_aggregate_context(context, sizeof(*p));
315 if( p==0 || argc<1 ) return;
316 if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)>0 ){
317 if( p->z && p->z!=p->zBuf ){
318 sqliteFree(p->z);
319 }
320 if( argv[0] ){
321 int len = strlen(argv[0]);
322 if( len < sizeof(p->zBuf) ){
323 p->z = p->zBuf;
324 }else{
325 p->z = sqliteMalloc( len+1 );
326 if( p->z==0 ) return;
327 }
328 strcpy(p->z, argv[0]);
329 }else{
330 p->z = 0;
331 }
332 }
333}
334static void minMaxFinalize(sqlite_func *context){
335 MinMaxCtx *p;
336 p = sqlite_aggregate_context(context, sizeof(*p));
337 if( p && p->z ){
338 sqlite_set_result_string(context, p->z, strlen(p->z));
339 }
340 if( p && p->z && p->z!=p->zBuf ){
341 sqliteFree(p->z);
342 }
343}
drhdd5baa92002-02-27 19:50:59 +0000344
drhd3a149e2002-02-24 17:12:53 +0000345/*
drha2ed5602002-02-26 23:55:31 +0000346** This function registered all of the above C functions as SQL
347** functions. This should be the only routine in this file with
348** external linkage.
drhdc04c582002-02-24 01:55:15 +0000349*/
350void sqliteRegisterBuildinFunctions(sqlite *db){
drh0bce8352002-02-28 00:41:10 +0000351 static struct {
352 char *zName;
353 int nArg;
354 void (*xFunc)(sqlite_func*,int,const char**);
355 } aFuncs[] = {
356 { "min", -1, minFunc },
357 { "max", -1, maxFunc },
358 { "length", 1, lengthFunc },
359 { "substr", 3, substrFunc },
360 { "abs", 1, absFunc },
361 { "round", 1, roundFunc },
362 { "round", 2, roundFunc },
363 { "upper", 1, upperFunc },
364 { "lower", 1, lowerFunc },
drh3212e182002-02-28 00:46:26 +0000365 { "ifnull", 2, ifnullFunc },
366 { "nvl", 2, ifnullFunc },
drh0bce8352002-02-28 00:41:10 +0000367 };
368 static struct {
369 char *zName;
370 int nArg;
371 void (*xStep)(sqlite_func*,int,const char**);
372 void (*xFinalize)(sqlite_func*);
373 } aAggs[] = {
374 { "min", 1, minStep, minMaxFinalize },
375 { "max", 1, maxStep, minMaxFinalize },
376 { "sum", 1, sumStep, sumFinalize },
377 { "avg", 1, sumStep, avgFinalize },
378 { "count", 0, countStep, countFinalize },
379 { "count", 1, countStep, countFinalize },
380 { "stddev", 1, stdDevStep, stdDevFinalize },
381 };
382 int i;
383
384 for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
385 sqlite_create_function(db, aFuncs[i].zName,
386 aFuncs[i].nArg, aFuncs[i].xFunc, 0);
387 }
388 for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
389 sqlite_create_aggregate(db, aAggs[i].zName,
390 aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, 0);
391 }
drhdc04c582002-02-24 01:55:15 +0000392}