1 /*
2 This library will provide common mathematical and string functions in
3 SQL queries using the operating system libraries or provided
4 definitions. It includes the following functions:
5
6 Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference,
7 degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp,
8 log, log10, power, sign, sqrt, square, ceil, floor, pi.
9
10 String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim,
11 replace, reverse, proper, padl, padr, padc, strfilter.
12
13 Aggregate: stdev, variance, mode, median, lower_quartile,
14 upper_quartile.
15
16 The string functions ltrim, rtrim, trim, replace are included in
17 recent versions of SQLite and so by default do not build.
18
19 Compilation instructions:
20 Compile this C source file into a dynamic library as follows:
21 * Linux:
22 gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.so
23 * Mac OS X:
24 gcc -fno-common -dynamiclib extension-functions.c -o libsqlitefunctions.dylib
25 (You may need to add flags
26 -I /opt/local/include/ -L/opt/local/lib -lsqlite3
27 if your sqlite3 is installed from Mac ports, or
28 -I /sw/include/ -L/sw/lib -lsqlite3
29 if installed with Fink.)
30 * Windows:
31 1. Install MinGW (http://www.mingw.org/) and you will get the gcc
32 (gnu compiler collection)
33 2. add the path to your path variable (isn't done during the
34 installation!)
35 3. compile:
36 gcc -shared -I "path" -o libsqlitefunctions.so extension-functions.c
37 (path = path of sqlite3ext.h; i.e. C:\programs\sqlite)
38
39 Usage instructions for applications calling the sqlite3 API functions:
40 In your application, call sqlite3_enable_load_extension(db,1) to
41 allow loading external libraries. Then load the library libsqlitefunctions
42 using sqlite3_load_extension; the third argument should be 0.
43 See http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions.
44 Select statements may now use these functions, as in
45 SELECT cos(radians(inclination)) FROM satsum WHERE satnum = 25544;
46
47 Usage instructions for the sqlite3 program:
48 If the program is built so that loading extensions is permitted,
49 the following will work:
50 sqlite> SELECT load_extension('./libsqlitefunctions.so');
51 sqlite> select cos(radians(45));
52 0.707106781186548
53 Note: Loading extensions is by default prohibited as a
54 security measure; see "Security Considerations" in
55 http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions.
56 If the sqlite3 program and library are built this
57 way, you cannot use these functions from the program, you
58 must write your own program using the sqlite3 API, and call
59 sqlite3_enable_load_extension as described above, or else
60 rebuilt the sqlite3 program to allow loadable extensions.
61
62 Alterations:
63 The instructions are for Linux, Mac OS X, and Windows; users of other
64 OSes may need to modify this procedure. In particular, if your math
65 library lacks one or more of the needed trig or log functions, comment
66 out the appropriate HAVE_ #define at the top of file. If you do not
67 wish to make a loadable module, comment out the define for
68 COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE. If you are using a
69 version of SQLite without the trim functions and replace, comment out
70 the HAVE_TRIM #define.
71
72 Liam Healy
73
74 History:
75 2010-01-06 Correct check for argc in squareFunc, and add Windows
76 compilation instructions.
77 2009-06-24 Correct check for argc in properFunc.
78 2008-09-14 Add check that memory was actually allocated after
79 sqlite3_malloc or sqlite3StrDup, call sqlite3_result_error_nomem if
80 not. Thanks to Robert Simpson.
81 2008-06-13 Change to instructions to indicate use of the math library
82 and that program might work.
83 2007-10-01 Minor clarification to instructions.
84 2007-09-29 Compilation as loadable module is optional with
85 COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE.
86 2007-09-28 Use sqlite3_extension_init and macros
87 SQLITE_EXTENSION_INIT1, SQLITE_EXTENSION_INIT2, so that it works with
88 sqlite3_load_extension. Thanks to Eric Higashino and Joe Wilson.
89 New instructions for Mac compilation.
90 2007-09-17 With help from Joe Wilson and Nuno Luca, made use of
91 external interfaces so that compilation is no longer dependent on
92 SQLite source code. Merged source, header, and README into a single
93 file. Added casts so that Mac will compile without warnings (unsigned
94 and signed char).
95 2007-09-05 Included some definitions from sqlite 3.3.13 so that this
96 will continue to work in newer versions of sqlite. Completed
97 description of functions available.
98 2007-03-27 Revised description.
99 2007-03-23 Small cleanup and a bug fix on the code. This was mainly
100 letting errno flag errors encountered in the math library and checking
101 the result, rather than pre-checking. This fixes a bug in power that
102 would cause an error if any non-positive number was raised to any
103 power.
104 2007-02-07 posted by Mikey C to sqlite mailing list.
105 Original code 2006 June 05 by relicoder.
106
107 */
108
109 //#include "config.h"
110
111 // #define COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE 1
112 #define HAVE_ACOSH 1
113 #define HAVE_ASINH 1
114 #define HAVE_ATANH 1
115 #define HAVE_SINH 1
116 #define HAVE_COSH 1
117 #define HAVE_TANH 1
118 #define HAVE_LOG10 1
119 #define HAVE_ISBLANK 1
120 #define SQLITE_SOUNDEX 1
121 #define HAVE_TRIM 1 /* LMH 2007-03-25 if sqlite has trim functions */
122
123 #define __STDC_FORMAT_MACROS
124
125 #ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE
126 #include "sqlite3ext.h"
127 SQLITE_EXTENSION_INIT1
128 #else
129 #include "sqlite3.h"
130 #endif
131
132 #include <ctype.h>
133 /* relicoder */
134 #include <math.h>
135 #include <string.h>
136 #include <stdio.h>
137 #include <errno.h> /* LMH 2007-03-25 */
138
139 #include <stdlib.h>
140 #include <assert.h>
141
142 #ifndef _MAP_H_
143 #define _MAP_H_
144
145 #include <stdint.h>
146 #include <inttypes.h>
147
148 #include "sqlite-extension-func.hh"
149
150 /*
151 ** Simple binary tree implementation to use in median, mode and quartile calculations
152 ** Tree is not necessarily balanced. That would require something like red&black trees of AVL
153 */
154
155 typedef int(*cmp_func)(const void *, const void *);
156 typedef void(*map_iterator)(void*, int64_t, void*);
157
158 typedef struct node{
159 struct node *l;
160 struct node *r;
161 void* data;
162 int64_t count;
163 } node;
164
165 typedef struct map{
166 node *base;
167 cmp_func cmp;
168 short free;
169 } map;
170
171 /*
172 ** creates a map given a comparison function
173 */
174 map map_make(cmp_func cmp);
175
176 /*
177 ** inserts the element e into map m
178 */
179 void map_insert(map *m, void *e);
180
181 /*
182 ** executes function iter over all elements in the map, in key increasing order
183 */
184 void map_iterate(map *m, map_iterator iter, void* p);
185
186 /*
187 ** frees all memory used by a map
188 */
189 void map_destroy(map *m);
190
191 /*
192 ** compares 2 integers
193 ** to use with map_make
194 */
195 int int_cmp(const void *a, const void *b);
196
197 /*
198 ** compares 2 doubles
199 ** to use with map_make
200 */
201 int double_cmp(const void *a, const void *b);
202
203 #endif /* _MAP_H_ */
204
205 typedef uint8_t u8;
206 typedef uint16_t u16;
207 typedef int64_t i64;
208
sqlite3StrDup(const char * z)209 static char *sqlite3StrDup( const char *z ) {
210 char *res = (char *) sqlite3_malloc(strlen(z) + 1 );
211 return strcpy( res, z );
212 }
213
214 /*
215 ** These are copied verbatim from fun.c so as to not have the names exported
216 */
217
218 /* LMH from sqlite3 3.3.13 */
219 /*
220 ** This table maps from the first byte of a UTF-8 character to the number
221 ** of trailing bytes expected. A value '4' indicates that the table key
222 ** is not a legal first byte for a UTF-8 character.
223 */
224 static const u8 xtra_utf8_bytes[256] = {
225 /* 0xxxxxxx */
226 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
227 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
228 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
229 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
230 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
231 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
232 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
233 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
234
235 /* 10wwwwww */
236 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,
237 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,
238 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,
239 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4,
240
241 /* 110yyyyy */
242 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
243 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
244
245 /* 1110zzzz */
246 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
247
248 /* 11110yyy */
249 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4,
250 };
251
252
253 /*
254 ** This table maps from the number of trailing bytes in a UTF-8 character
255 ** to an integer constant that is effectively calculated for each character
256 ** read by a naive implementation of a UTF-8 character reader. The code
257 ** in the READ_UTF8 macro explains things best.
258 */
259 static const int xtra_utf8_bits[] = {
260 0,
261 12416, /* (0xC0 << 6) + (0x80) */
262 925824, /* (0xE0 << 12) + (0x80 << 6) + (0x80) */
263 63447168 /* (0xF0 << 18) + (0x80 << 12) + (0x80 << 6) + 0x80 */
264 };
265
266 /*
267 ** If a UTF-8 character contains N bytes extra bytes (N bytes follow
268 ** the initial byte so that the total character length is N+1) then
269 ** masking the character with utf8_mask[N] must produce a non-zero
270 ** result. Otherwise, we have an (illegal) overlong encoding.
271 */
272 static const unsigned long utf_mask[] = {
273 0x00000000,
274 0xffffff80,
275 0xfffff800,
276 0xffff0000,
277 };
278
279 /* LMH salvaged from sqlite3 3.3.13 source code src/utf.c */
280 #define READ_UTF8(zIn, c) { \
281 int xtra; \
282 c = *(zIn)++; \
283 xtra = xtra_utf8_bytes[c]; \
284 switch( xtra ){ \
285 case 4: c = (int)0xFFFD; break; \
286 case 3: c = (c<<6) + *(zIn)++; \
287 case 2: c = (c<<6) + *(zIn)++; \
288 case 1: c = (c<<6) + *(zIn)++; \
289 c -= xtra_utf8_bits[xtra]; \
290 if( (utf_mask[xtra]&c)==0 \
291 || (c&0xFFFFF800)==0xD800 \
292 || (c&0xFFFFFFFE)==0xFFFE ){ c = 0xFFFD; } \
293 } \
294 }
295
sqlite3ReadUtf8(const unsigned char * z)296 static int sqlite3ReadUtf8(const unsigned char *z){
297 int c;
298 READ_UTF8(z, c);
299 return c;
300 }
301
302 #define SKIP_UTF8(zIn) { \
303 zIn += (xtra_utf8_bytes[*(u8 *)zIn] + 1); \
304 }
305
306 /*
307 ** pZ is a UTF-8 encoded unicode string. If nByte is less than zero,
308 ** return the number of unicode characters in pZ up to (but not including)
309 ** the first 0x00 byte. If nByte is not less than zero, return the
310 ** number of unicode characters in the first nByte of pZ (or up to
311 ** the first 0x00, whichever comes first).
312 */
sqlite3Utf8CharLen(const char * z,int nByte)313 static int sqlite3Utf8CharLen(const char *z, int nByte){
314 int r = 0;
315 const char *zTerm;
316 if( nByte>=0 ){
317 zTerm = &z[nByte];
318 }else{
319 zTerm = (const char *)(-1);
320 }
321 assert( z<=zTerm );
322 while( *z!=0 && z<zTerm ){
323 SKIP_UTF8(z);
324 r++;
325 }
326 return r;
327 }
328
329 /*
330 ** X is a pointer to the first byte of a UTF-8 character. Increment
331 ** X so that it points to the next character. This only works right
332 ** if X points to a well-formed UTF-8 string.
333 */
334 #define sqliteNextChar(X) while( (0xc0&*++(X))==0x80 ){}
335 #define sqliteCharVal(X) sqlite3ReadUtf8(X)
336
337 /*
338 ** This is a macro that facilitates writting wrappers for math.h functions
339 ** it creates code for a function to use in SQlite that gets one numeric input
340 ** and returns a floating point value.
341 **
342 ** Could have been implemented using pointers to functions but this way it's inline
343 ** and thus more efficient. Lower * ranking though...
344 **
345 ** Parameters:
346 ** name: function name to de defined (eg: sinFunc)
347 ** function: function defined in math.h to wrap (eg: sin)
348 ** domain: boolean condition that CAN'T happen in terms of the input parameter rVal
349 ** (eg: rval<0 for sqrt)
350 */
351 /* LMH 2007-03-25 Changed to use errno and remove domain; no pre-checking for errors. */
352 #define GEN_MATH_WRAP_DOUBLE_1(name, function) \
353 static void name(sqlite3_context *context, int argc, sqlite3_value **argv){\
354 double rVal = 0.0, val;\
355 assert( argc==1 );\
356 switch( sqlite3_value_type(argv[0]) ){\
357 case SQLITE_NULL: {\
358 sqlite3_result_null(context);\
359 break;\
360 }\
361 default: {\
362 rVal = sqlite3_value_double(argv[0]);\
363 errno = 0;\
364 val = function(rVal);\
365 if (errno == 0) {\
366 sqlite3_result_double(context, val);\
367 } else {\
368 sqlite3_result_error(context, strerror(errno), errno);\
369 }\
370 break;\
371 }\
372 }\
373 }\
374
375
376 /*
377 ** Example of GEN_MATH_WRAP_DOUBLE_1 usage
378 ** this creates function sqrtFunc to wrap the math.h standard function sqrt(x)=x^0.5
379 */
GEN_MATH_WRAP_DOUBLE_1(sqrtFunc,sqrt)380 GEN_MATH_WRAP_DOUBLE_1(sqrtFunc, sqrt)
381
382 /* trignometric functions */
383 GEN_MATH_WRAP_DOUBLE_1(acosFunc, acos)
384 GEN_MATH_WRAP_DOUBLE_1(asinFunc, asin)
385 GEN_MATH_WRAP_DOUBLE_1(atanFunc, atan)
386
387 /*
388 ** Many of systems don't have inverse hyperbolic trig functions so this will emulate
389 ** them on those systems in terms of log and sqrt (formulas are too trivial to demand
390 ** written proof here)
391 */
392
393 #ifndef HAVE_ACOSH
394 static double acosh(double x){
395 return log(x + sqrt(x*x - 1.0));
396 }
397 #endif
398
GEN_MATH_WRAP_DOUBLE_1(acoshFunc,acosh)399 GEN_MATH_WRAP_DOUBLE_1(acoshFunc, acosh)
400
401 #ifndef HAVE_ASINH
402 static double asinh(double x){
403 return log(x + sqrt(x*x + 1.0));
404 }
405 #endif
406
GEN_MATH_WRAP_DOUBLE_1(asinhFunc,asinh)407 GEN_MATH_WRAP_DOUBLE_1(asinhFunc, asinh)
408
409 #ifndef HAVE_ATANH
410 static double atanh(double x){
411 return (1.0/2.0)*log((1+x)/(1-x)) ;
412 }
413 #endif
414
GEN_MATH_WRAP_DOUBLE_1(atanhFunc,atanh)415 GEN_MATH_WRAP_DOUBLE_1(atanhFunc, atanh)
416
417 /*
418 ** math.h doesn't require cot (cotangent) so it's defined here
419 */
420 static double cot(double x){
421 return 1.0/tan(x);
422 }
423
GEN_MATH_WRAP_DOUBLE_1(sinFunc,sin)424 GEN_MATH_WRAP_DOUBLE_1(sinFunc, sin)
425 GEN_MATH_WRAP_DOUBLE_1(cosFunc, cos)
426 GEN_MATH_WRAP_DOUBLE_1(tanFunc, tan)
427 GEN_MATH_WRAP_DOUBLE_1(cotFunc, cot)
428
429 static double coth(double x){
430 return 1.0/tanh(x);
431 }
432
433 /*
434 ** Many systems don't have hyperbolic trigonometric functions so this will emulate
435 ** them on those systems directly from the definition in terms of exp
436 */
437 #ifndef HAVE_SINH
sinh(double x)438 static double sinh(double x){
439 return (exp(x)-exp(-x))/2.0;
440 }
441 #endif
442
GEN_MATH_WRAP_DOUBLE_1(sinhFunc,sinh)443 GEN_MATH_WRAP_DOUBLE_1(sinhFunc, sinh)
444
445 #ifndef HAVE_COSH
446 static double cosh(double x){
447 return (exp(x)+exp(-x))/2.0;
448 }
449 #endif
450
GEN_MATH_WRAP_DOUBLE_1(coshFunc,cosh)451 GEN_MATH_WRAP_DOUBLE_1(coshFunc, cosh)
452
453 #ifndef HAVE_TANH
454 static double tanh(double x){
455 return sinh(x)/cosh(x);
456 }
457 #endif
458
GEN_MATH_WRAP_DOUBLE_1(tanhFunc,tanh)459 GEN_MATH_WRAP_DOUBLE_1(tanhFunc, tanh)
460
461 GEN_MATH_WRAP_DOUBLE_1(cothFunc, coth)
462
463 /*
464 ** Some systems lack log in base 10. This will emulate it
465 */
466
467 #ifndef HAVE_LOG10
468 static double log10(double x){
469 static double l10 = -1.0;
470 if( l10<0.0 ){
471 l10 = log(10.0);
472 }
473 return log(x)/l10;
474 }
475 #endif
476
GEN_MATH_WRAP_DOUBLE_1(logFunc,log)477 GEN_MATH_WRAP_DOUBLE_1(logFunc, log)
478 GEN_MATH_WRAP_DOUBLE_1(log10Func, log10)
479 GEN_MATH_WRAP_DOUBLE_1(expFunc, exp)
480
481 /*
482 ** Fallback for systems where math.h doesn't define M_PI
483 */
484 #undef M_PI
485 #ifndef M_PI
486 /*
487 ** static double PI = acos(-1.0);
488 ** #define M_PI (PI)
489 */
490 #define M_PI 3.14159265358979323846
491 #endif
492
493 /* Convert Degrees into Radians */
494 static double deg2rad(double x){
495 return x*M_PI/180.0;
496 }
497
498 /* Convert Radians into Degrees */
rad2deg(double x)499 static double rad2deg(double x){
500 return 180.0*x/M_PI;
501 }
502
GEN_MATH_WRAP_DOUBLE_1(rad2degFunc,rad2deg)503 GEN_MATH_WRAP_DOUBLE_1(rad2degFunc, rad2deg)
504 GEN_MATH_WRAP_DOUBLE_1(deg2radFunc, deg2rad)
505
506 /* constant function that returns the value of PI=3.1415... */
507 static void piFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
508 sqlite3_result_double(context, M_PI);
509 }
510
511 /*
512 ** Implements the sqrt function, it has the peculiarity of returning an integer when the
513 ** the argument is an integer.
514 ** Since SQLite isn't strongly typed (almost untyped actually) this is a bit pedantic
515 */
squareFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)516 static void squareFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
517 i64 iVal = 0;
518 double rVal = 0.0;
519 assert( argc==1 );
520 switch( sqlite3_value_type(argv[0]) ){
521 case SQLITE_INTEGER: {
522 iVal = sqlite3_value_int64(argv[0]);
523 sqlite3_result_int64(context, iVal*iVal);
524 break;
525 }
526 case SQLITE_NULL: {
527 sqlite3_result_null(context);
528 break;
529 }
530 default: {
531 rVal = sqlite3_value_double(argv[0]);
532 sqlite3_result_double(context, rVal*rVal);
533 break;
534 }
535 }
536 }
537
538 /*
539 ** Wraps the pow math.h function
540 ** When both the base and the exponent are integers the result should be integer
541 ** (see sqrt just before this). Here the result is always double
542 */
543 /* LMH 2007-03-25 Changed to use errno; no pre-checking for errors. Also removes
544 but that was present in the pre-checking that called sqlite3_result_error on
545 a non-positive first argument, which is not always an error. */
powerFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)546 static void powerFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
547 double r1 = 0.0;
548 double r2 = 0.0;
549 double val;
550
551 assert( argc==2 );
552
553 if( sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL ){
554 sqlite3_result_null(context);
555 }else{
556 r1 = sqlite3_value_double(argv[0]);
557 r2 = sqlite3_value_double(argv[1]);
558 errno = 0;
559 val = pow(r1,r2);
560 if (errno == 0) {
561 sqlite3_result_double(context, val);
562 } else {
563 sqlite3_result_error(context, strerror(errno), errno);
564 }
565 }
566 }
567
568 /*
569 ** atan2 wrapper
570 */
atn2Func(sqlite3_context * context,int argc,sqlite3_value ** argv)571 static void atn2Func(sqlite3_context *context, int argc, sqlite3_value **argv){
572 double r1 = 0.0;
573 double r2 = 0.0;
574
575 assert( argc==2 );
576
577 if( sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL ){
578 sqlite3_result_null(context);
579 }else{
580 r1 = sqlite3_value_double(argv[0]);
581 r2 = sqlite3_value_double(argv[1]);
582 sqlite3_result_double(context, atan2(r1,r2));
583 }
584 }
585
586 /*
587 ** Implementation of the sign() function
588 ** return one of 3 possibilities +1,0 or -1 when the argument is respectively
589 ** positive, 0 or negative.
590 ** When the argument is NULL the result is also NULL (completly conventional)
591 */
signFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)592 static void signFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
593 double rVal=0.0;
594 i64 iVal=0;
595 assert( argc==1 );
596 switch( sqlite3_value_type(argv[0]) ){
597 case SQLITE_INTEGER: {
598 iVal = sqlite3_value_int64(argv[0]);
599 iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
600 sqlite3_result_int64(context, iVal);
601 break;
602 }
603 case SQLITE_NULL: {
604 sqlite3_result_null(context);
605 break;
606 }
607 default: {
608 /* 2nd change below. Line for abs was: if( rVal<0 ) rVal = rVal * -1.0; */
609
610 rVal = sqlite3_value_double(argv[0]);
611 rVal = ( rVal > 0) ? 1: ( rVal < 0 ) ? -1: 0;
612 sqlite3_result_double(context, rVal);
613 break;
614 }
615 }
616 }
617
618
619 /*
620 ** smallest integer value not less than argument
621 */
ceilFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)622 static void ceilFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
623 double rVal=0.0;
624 assert( argc==1 );
625 switch( sqlite3_value_type(argv[0]) ){
626 case SQLITE_INTEGER: {
627 i64 iVal = sqlite3_value_int64(argv[0]);
628 sqlite3_result_int64(context, iVal);
629 break;
630 }
631 case SQLITE_NULL: {
632 sqlite3_result_null(context);
633 break;
634 }
635 default: {
636 rVal = sqlite3_value_double(argv[0]);
637 sqlite3_result_int64(context, (i64) ceil(rVal));
638 break;
639 }
640 }
641 }
642
643 /*
644 ** largest integer value not greater than argument
645 */
floorFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)646 static void floorFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
647 double rVal=0.0;
648 assert( argc==1 );
649 switch( sqlite3_value_type(argv[0]) ){
650 case SQLITE_INTEGER: {
651 i64 iVal = sqlite3_value_int64(argv[0]);
652 sqlite3_result_int64(context, iVal);
653 break;
654 }
655 case SQLITE_NULL: {
656 sqlite3_result_null(context);
657 break;
658 }
659 default: {
660 rVal = sqlite3_value_double(argv[0]);
661 sqlite3_result_int64(context, (i64) floor(rVal));
662 break;
663 }
664 }
665 }
666
667 /*
668 ** Given a string (s) in the first argument and an integer (n) in the second returns the
669 ** string that constains s contatenated n times
670 */
replicateFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)671 static void replicateFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
672 static const char *EMPTY = "";
673 unsigned char *z; /* result string */
674 i64 iCount; /* times to repeat */
675 i64 nLen; /* length of the input string (no multibyte considerations) */
676 i64 nTLen; /* length of the result string (no multibyte considerations) */
677 i64 i=0;
678
679 if( argc!=2 || SQLITE_NULL==sqlite3_value_type(argv[0]) )
680 return;
681
682 iCount = sqlite3_value_int64(argv[1]);
683
684 if( iCount<0 ){
685 sqlite3_result_error(context, "domain error", -1);
686 return;
687 }
688
689 if (iCount == 0) {
690 sqlite3_result_text(context, EMPTY, 0, SQLITE_STATIC);
691 return;
692 }
693
694 nLen = sqlite3_value_bytes(argv[0]);
695 nTLen = nLen*iCount;
696 z= (unsigned char *) sqlite3_malloc(nTLen + 1);
697 if (!z){
698 sqlite3_result_error_nomem(context);
699 if (z) sqlite3_free(z);
700 return;
701 }
702 auto zo = sqlite3_value_text(argv[0]);
703
704 for(i=0; i<iCount; ++i){
705 strcpy((char*)(z+i*nLen), (char*)zo);
706 }
707
708 sqlite3_result_text(context, (char*)z, -1, sqlite3_free);
709 }
710
711 /*
712 ** Some systems (win32 among others) don't have an isblank function, this will emulate it.
713 ** This function is not UFT-8 safe since it only analyses a byte character.
714 */
715 #ifndef HAVE_ISBLANK
isblank(char c)716 int isblank(char c){
717 return( ' '==c || '\t'==c );
718 }
719 #endif
720
properFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)721 static void properFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
722 const unsigned char *z; /* input string */
723 unsigned char *zo; /* output string */
724 unsigned char *zt; /* iterator */
725 char r;
726 int c=1;
727
728 assert( argc==1);
729 if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
730 sqlite3_result_null(context);
731 return;
732 }
733
734 z = sqlite3_value_text(argv[0]);
735 zo = (unsigned char *)sqlite3StrDup((char *) z);
736 if (!zo) {
737 sqlite3_result_error_nomem(context);
738 return;
739 }
740 zt = zo;
741
742 while( (r = *(z++))!=0 ){
743 if( isblank(r) ){
744 c=1;
745 }else{
746 if( c==1 ){
747 r = toupper(r);
748 }else{
749 r = tolower(r);
750 }
751 c=0;
752 }
753 *(zt++) = r;
754 }
755 *zt = '\0';
756
757 sqlite3_result_text(context, (char*)zo, -1, SQLITE_TRANSIENT);
758 sqlite3_free(zo);
759 }
760
761 /*
762 ** given an input string (s) and an integer (n) adds spaces at the begining of s
763 ** until it has a length of n characters.
764 ** When s has a length >=n it's a NOP
765 ** padl(NULL) = NULL
766 */
padlFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)767 static void padlFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
768 i64 ilen; /* length to pad to */
769 i64 zl; /* length of the input string (UTF-8 chars) */
770 int i = 0;
771 const char *zi; /* input string */
772 char *zo; /* output string */
773 char *zt;
774
775 assert( argc==2 );
776
777 if( sqlite3_value_type(argv[0]) == SQLITE_NULL ){
778 sqlite3_result_null(context);
779 }else{
780 zi = (char *)sqlite3_value_text(argv[0]);
781 ilen = sqlite3_value_int64(argv[1]);
782 /* check domain */
783 if(ilen<0){
784 sqlite3_result_error(context, "domain error", -1);
785 return;
786 }
787 zl = sqlite3Utf8CharLen(zi, -1);
788 if( zl>=ilen ){
789 /* string is longer than the requested pad length, return the same string (dup it) */
790 zo = sqlite3StrDup(zi);
791 if (!zo){
792 sqlite3_result_error_nomem(context);
793 return;
794 }
795 sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
796 }else{
797 zo = (char *) sqlite3_malloc(strlen(zi) + ilen - zl + 1);
798 if (!zo){
799 sqlite3_result_error_nomem(context);
800 return;
801 }
802 zt = zo;
803 for(i=1; i+zl<=ilen; ++i){
804 *(zt++)=' ';
805 }
806 /* no need to take UTF-8 into consideration here */
807 strcpy(zt,zi);
808 }
809 sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
810 sqlite3_free(zo);
811 }
812 }
813
814 /*
815 ** given an input string (s) and an integer (n) appends spaces at the end of s
816 ** until it has a length of n characters.
817 ** When s has a length >=n it's a NOP
818 ** padl(NULL) = NULL
819 */
padrFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)820 static void padrFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
821 i64 ilen; /* length to pad to */
822 i64 zl; /* length of the input string (UTF-8 chars) */
823 i64 zll; /* length of the input string (bytes) */
824 int i = 0;
825 const char *zi; /* input string */
826 char *zo; /* output string */
827 char *zt;
828
829 assert( argc==2 );
830
831 if( sqlite3_value_type(argv[0]) == SQLITE_NULL ){
832 sqlite3_result_null(context);
833 }else{
834 zi = (char *)sqlite3_value_text(argv[0]);
835 ilen = sqlite3_value_int64(argv[1]);
836 /* check domain */
837 if(ilen<0){
838 sqlite3_result_error(context, "domain error", -1);
839 return;
840 }
841 zl = sqlite3Utf8CharLen(zi, -1);
842 if( zl>=ilen ){
843 /* string is longer than the requested pad length, return the same string (dup it) */
844 zo = sqlite3StrDup(zi);
845 if (!zo){
846 sqlite3_result_error_nomem(context);
847 return;
848 }
849 sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
850 }else{
851 zll = strlen(zi);
852 zo = (char *) sqlite3_malloc(zll + ilen - zl + 1);
853 if (!zo){
854 sqlite3_result_error_nomem(context);
855 return;
856 }
857 zt = strcpy(zo,zi)+zll;
858 for(i=1; i+zl<=ilen; ++i){
859 *(zt++) = ' ';
860 }
861 *zt = '\0';
862 }
863 sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
864 sqlite3_free(zo);
865 }
866 }
867
868 /*
869 ** given an input string (s) and an integer (n) appends spaces at the end of s
870 ** and adds spaces at the begining of s until it has a length of n characters.
871 ** Tries to add has many characters at the left as at the right.
872 ** When s has a length >=n it's a NOP
873 ** padl(NULL) = NULL
874 */
padcFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)875 static void padcFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
876 i64 ilen; /* length to pad to */
877 i64 zl; /* length of the input string (UTF-8 chars) */
878 i64 zll; /* length of the input string (bytes) */
879 int i = 0;
880 const char *zi; /* input string */
881 char *zo; /* output string */
882 char *zt;
883
884 assert( argc==2 );
885
886 if( sqlite3_value_type(argv[0]) == SQLITE_NULL ){
887 sqlite3_result_null(context);
888 }else{
889 zi = (char *)sqlite3_value_text(argv[0]);
890 ilen = sqlite3_value_int64(argv[1]);
891 /* check domain */
892 if(ilen<0){
893 sqlite3_result_error(context, "domain error", -1);
894 return;
895 }
896 zl = sqlite3Utf8CharLen(zi, -1);
897 if( zl>=ilen ){
898 /* string is longer than the requested pad length, return the same string (dup it) */
899 zo = sqlite3StrDup(zi);
900 if (!zo){
901 sqlite3_result_error_nomem(context);
902 return;
903 }
904 sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
905 }else{
906 zll = strlen(zi);
907 zo = (char *) sqlite3_malloc(zll + ilen - zl + 1);
908 if (!zo){
909 sqlite3_result_error_nomem(context);
910 return;
911 }
912 zt = zo;
913 for(i=1; 2*i+zl<=ilen; ++i){
914 *(zt++) = ' ';
915 }
916 strcpy(zt, zi);
917 zt+=zll;
918 for(; i+zl<=ilen; ++i){
919 *(zt++) = ' ';
920 }
921 *zt = '\0';
922 }
923 sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
924 sqlite3_free(zo);
925 }
926 }
927
928 /*
929 ** given 2 string (s1,s2) returns the string s1 with the characters NOT in s2 removed
930 ** assumes strings are UTF-8 encoded
931 */
strfilterFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)932 static void strfilterFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
933 const char *zi1; /* first parameter string (searched string) */
934 const char *zi2; /* second parameter string (vcontains valid characters) */
935 const char *z1;
936 const char *z21;
937 const char *z22;
938 char *zo; /* output string */
939 char *zot;
940 int c1 = 0;
941 int c2 = 0;
942
943 assert( argc==2 );
944
945 if( sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL ){
946 sqlite3_result_null(context);
947 }else{
948 zi1 = (char *)sqlite3_value_text(argv[0]);
949 zi2 = (char *)sqlite3_value_text(argv[1]);
950 /*
951 ** maybe I could allocate less, but that would imply 2 passes, rather waste
952 ** (possibly) some memory
953 */
954 zo = (char *) sqlite3_malloc(strlen(zi1) + 1);
955 if (!zo){
956 sqlite3_result_error_nomem(context);
957 return;
958 }
959 zot = zo;
960 z1 = zi1;
961 while( (c1=sqliteCharVal((unsigned char *)z1))!=0 ){
962 z21=zi2;
963 while( (c2=sqliteCharVal((unsigned char *)z21))!=0 && c2!=c1 ){
964 sqliteNextChar(z21);
965 }
966 if( c2!=0){
967 z22=z21;
968 sqliteNextChar(z22);
969 strncpy(zot, z21, z22-z21);
970 zot+=z22-z21;
971 }
972 sqliteNextChar(z1);
973 }
974 *zot = '\0';
975
976 sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
977 sqlite3_free(zo);
978 }
979 }
980
981 /*
982 ** Given a string z1, retutns the (0 based) index of it's first occurence
983 ** in z2 after the first s characters.
984 ** Returns -1 when there isn't a match.
985 ** updates p to point to the character where the match occured.
986 ** This is an auxiliary function.
987 */
_substr(const char * z1,const char * z2,int s,const char ** p)988 static int _substr(const char* z1, const char* z2, int s, const char** p){
989 int c = 0;
990 int rVal=-1;
991 const char* zt1;
992 const char* zt2;
993 int c1,c2;
994
995 if( '\0'==*z1 ){
996 return -1;
997 }
998
999 while( (sqliteCharVal((unsigned char *)z2) != 0) && (c++)<s){
1000 sqliteNextChar(z2);
1001 }
1002
1003 c = 0;
1004 while( (sqliteCharVal((unsigned char *)z2)) != 0 ){
1005 zt1 = z1;
1006 zt2 = z2;
1007
1008 do{
1009 c1 = sqliteCharVal((unsigned char *)zt1);
1010 c2 = sqliteCharVal((unsigned char *)zt2);
1011 if (c1 == 0) {
1012 break;
1013 }
1014 if (c2 == 0) {
1015 break;
1016 }
1017 sqliteNextChar(zt1);
1018 sqliteNextChar(zt2);
1019 }while( c1 == c2 && c1 != 0 && c2 != 0 );
1020
1021 if( c1 == 0 ){
1022 rVal = c;
1023 break;
1024 }
1025
1026 sqliteNextChar(z2);
1027 ++c;
1028 }
1029 if(p){
1030 *p=z2;
1031 }
1032 return rVal >=0 ? rVal+s : rVal;
1033 }
1034
1035 /*
1036 ** given 2 input strings (s1,s2) and an integer (n) searches from the nth character
1037 ** for the string s1. Returns the position where the match occured.
1038 ** Characters are counted from 1.
1039 ** 0 is returned when no match occurs.
1040 */
1041
charindexFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)1042 static void charindexFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
1043 const u8 *z1; /* s1 string */
1044 u8 *z2; /* s2 string */
1045 int s=0;
1046 int rVal=0;
1047
1048 assert( argc==3 ||argc==2);
1049
1050 if( SQLITE_NULL==sqlite3_value_type(argv[0]) || SQLITE_NULL==sqlite3_value_type(argv[1])){
1051 sqlite3_result_null(context);
1052 return;
1053 }
1054
1055 z1 = sqlite3_value_text(argv[0]);
1056 if( z1==0 ) return;
1057 z2 = (u8*) sqlite3_value_text(argv[1]);
1058 if(argc==3){
1059 s = sqlite3_value_int(argv[2])-1;
1060 if(s<0){
1061 s=0;
1062 }
1063 }else{
1064 s = 0;
1065 }
1066
1067 rVal = _substr((char *)z1,(char *)z2,s,NULL);
1068 sqlite3_result_int(context, rVal+1);
1069 }
1070
1071 /*
1072 ** given a string (s) and an integer (n) returns the n leftmost (UTF-8) characters
1073 ** if the string has a length<=n or is NULL this function is NOP
1074 */
leftFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)1075 static void leftFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
1076 int c=0;
1077 int cc=0;
1078 int l=0;
1079 const unsigned char *z; /* input string */
1080 const unsigned char *zt;
1081 unsigned char *rz; /* output string */
1082
1083 assert( argc==2);
1084
1085 if( SQLITE_NULL==sqlite3_value_type(argv[0]) || SQLITE_NULL==sqlite3_value_type(argv[1])){
1086 sqlite3_result_null(context);
1087 return;
1088 }
1089
1090 z = sqlite3_value_text(argv[0]);
1091 l = sqlite3_value_int(argv[1]);
1092 zt = z;
1093
1094 while( sqliteCharVal(zt) && c++<l)
1095 sqliteNextChar(zt);
1096
1097 cc=zt-z;
1098
1099 rz = (unsigned char *) sqlite3_malloc(zt - z + 1);
1100 if (!rz){
1101 sqlite3_result_error_nomem(context);
1102 return;
1103 }
1104 strncpy((char*) rz, (char*) z, zt-z);
1105 *(rz+cc) = '\0';
1106 sqlite3_result_text(context, (char*)rz, -1, SQLITE_TRANSIENT);
1107 sqlite3_free(rz);
1108 }
1109
1110 /*
1111 ** given a string (s) and an integer (n) returns the n rightmost (UTF-8) characters
1112 ** if the string has a length<=n or is NULL this function is NOP
1113 */
rightFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)1114 static void rightFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
1115 int l=0;
1116 int c=0;
1117 int cc=0;
1118 const char *z;
1119 const char *zt;
1120 const char *ze;
1121 char *rz;
1122
1123 assert( argc==2);
1124
1125 if( SQLITE_NULL == sqlite3_value_type(argv[0]) || SQLITE_NULL == sqlite3_value_type(argv[1])){
1126 sqlite3_result_null(context);
1127 return;
1128 }
1129
1130 z = (char *)sqlite3_value_text(argv[0]);
1131 l = sqlite3_value_int(argv[1]);
1132 zt = z;
1133
1134 while( sqliteCharVal((unsigned char *)zt)!=0){
1135 sqliteNextChar(zt);
1136 ++c;
1137 }
1138
1139 ze = zt;
1140 zt = z;
1141
1142 cc=c-l;
1143 if(cc<0)
1144 cc=0;
1145
1146 while( cc-- > 0 ){
1147 sqliteNextChar(zt);
1148 }
1149
1150 rz = (char *) sqlite3_malloc(ze - zt + 1);
1151 if (!rz){
1152 sqlite3_result_error_nomem(context);
1153 return;
1154 }
1155 strcpy((char*) rz, (char*) (zt));
1156 sqlite3_result_text(context, (char*)rz, -1, SQLITE_TRANSIENT);
1157 sqlite3_free(rz);
1158 }
1159
1160 #ifndef HAVE_TRIM
1161 /*
1162 ** removes the whitespaces at the begining of a string.
1163 */
ltrim(const char * s)1164 const char* ltrim(const char* s){
1165 while( *s==' ' )
1166 ++s;
1167 return s;
1168 }
1169
1170 /*
1171 ** removes the whitespaces at the end of a string.
1172 ** !mutates the input string!
1173 */
rtrim(char * s)1174 void rtrim(char* s){
1175 char* ss = s+strlen(s)-1;
1176 while( ss>=s && *ss==' ' )
1177 --ss;
1178 *(ss+1)='\0';
1179 }
1180
1181 /*
1182 ** Removes the whitespace at the begining of a string
1183 */
ltrimFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)1184 static void ltrimFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
1185 const char *z;
1186
1187 assert( argc==1);
1188
1189 if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
1190 sqlite3_result_null(context);
1191 return;
1192 }
1193 z = sqlite3_value_text(argv[0]);
1194 sqlite3_result_text(context, ltrim(z), -1, SQLITE_TRANSIENT);
1195 }
1196
1197 /*
1198 ** Removes the whitespace at the end of a string
1199 */
rtrimFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)1200 static void rtrimFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
1201 const char *z;
1202 char *rz;
1203 /* try not to change data in argv */
1204
1205 assert( argc==1);
1206
1207 if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
1208 sqlite3_result_null(context);
1209 return;
1210 }
1211 z = sqlite3_value_text(argv[0]);
1212 rz = sqlite3StrDup(z);
1213 rtrim(rz);
1214 sqlite3_result_text(context, rz, -1, SQLITE_TRANSIENT);
1215 sqlite3_free(rz);
1216 }
1217
1218 /*
1219 ** Removes the whitespace at the begining and end of a string
1220 */
trimFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)1221 static void trimFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
1222 const char *z;
1223 char *rz;
1224 /* try not to change data in argv */
1225
1226 assert( argc==1);
1227
1228 if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
1229 sqlite3_result_null(context);
1230 return;
1231 }
1232 z = sqlite3_value_text(argv[0]);
1233 rz = sqlite3StrDup(z);
1234 rtrim(rz);
1235 sqlite3_result_text(context, ltrim(rz), -1, SQLITE_TRANSIENT);
1236 sqlite3_free(rz);
1237 }
1238 #endif
1239
1240 /*
1241 ** given a pointer to a string s1, the length of that string (l1), a new string (s2)
1242 ** and it's length (l2) appends s2 to s1.
1243 ** All lengths in bytes.
1244 ** This is just an auxiliary function
1245 */
1246 // static void _append(char **s1, int l1, const char *s2, int l2){
1247 // *s1 = realloc(*s1, (l1+l2+1)*sizeof(char));
1248 // strncpy((*s1)+l1, s2, l2);
1249 // *(*(s1)+l1+l2) = '\0';
1250 // }
1251
1252 #ifndef HAVE_TRIM
1253
1254 /*
1255 ** given strings s, s1 and s2 replaces occurrences of s1 in s by s2
1256 */
replaceFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)1257 static void replaceFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
1258 const char *z1; /* string s (first parameter) */
1259 const char *z2; /* string s1 (second parameter) string to look for */
1260 const char *z3; /* string s2 (third parameter) string to replace occurrences of s1 with */
1261 int lz1;
1262 int lz2;
1263 int lz3;
1264 int lzo=0;
1265 char *zo=0;
1266 int ret=0;
1267 const char *zt1;
1268 const char *zt2;
1269
1270 assert( 3==argc );
1271
1272 if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
1273 sqlite3_result_null(context);
1274 return;
1275 }
1276
1277 z1 = sqlite3_value_text(argv[0]);
1278 z2 = sqlite3_value_text(argv[1]);
1279 z3 = sqlite3_value_text(argv[2]);
1280 /* handle possible null values */
1281 if( 0==z2 ){
1282 z2="";
1283 }
1284 if( 0==z3 ){
1285 z3="";
1286 }
1287
1288 lz1 = strlen(z1);
1289 lz2 = strlen(z2);
1290 lz3 = strlen(z3);
1291
1292 #if 0
1293 /* special case when z2 is empty (or null) nothing will be changed */
1294 if( 0==lz2 ){
1295 sqlite3_result_text(context, z1, -1, SQLITE_TRANSIENT);
1296 return;
1297 }
1298 #endif
1299
1300 zt1=z1;
1301 zt2=z1;
1302
1303 while(1){
1304 ret=_substr(z2,zt1 , 0, &zt2);
1305
1306 if( ret<0 )
1307 break;
1308
1309 _append(&zo, lzo, zt1, zt2-zt1);
1310 lzo+=zt2-zt1;
1311 _append(&zo, lzo, z3, lz3);
1312 lzo+=lz3;
1313
1314 zt1=zt2+lz2;
1315 }
1316 _append(&zo, lzo, zt1, lz1-(zt1-z1));
1317 sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
1318 sqlite3_free(zo);
1319 }
1320 #endif
1321
1322 /*
1323 ** given a string returns the same string but with the characters in reverse order
1324 */
reverseFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)1325 static void reverseFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
1326 const char *z;
1327 const char *zt;
1328 char *rz;
1329 char *rzt;
1330 int l = 0;
1331 int i = 0;
1332
1333 assert( 1==argc );
1334
1335 if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
1336 sqlite3_result_null(context);
1337 return;
1338 }
1339 z = (char *)sqlite3_value_text(argv[0]);
1340 l = strlen(z);
1341 rz = (char *) sqlite3_malloc(l + 1);
1342 if (!rz){
1343 sqlite3_result_error_nomem(context);
1344 return;
1345 }
1346 rzt = rz+l;
1347 *(rzt--) = '\0';
1348
1349 zt=z;
1350 while( sqliteCharVal((unsigned char *)zt)!=0 ){
1351 z=zt;
1352 sqliteNextChar(zt);
1353 for(i=1; zt-i>=z; ++i){
1354 *(rzt--)=*(zt-i);
1355 }
1356 }
1357
1358 sqlite3_result_text(context, rz, -1, SQLITE_TRANSIENT);
1359 sqlite3_free(rz);
1360 }
1361
1362 /*
1363 ** An instance of the following structure holds the context of a
1364 ** stdev() or variance() aggregate computation.
1365 ** implementaion of http://en.wikipedia.org/wiki/Algorithms_for_calculating_variance#Algorithm_II
1366 ** less prone to rounding errors
1367 */
1368 typedef struct StdevCtx StdevCtx;
1369 struct StdevCtx {
1370 double rM;
1371 double rS;
1372 i64 cnt; /* number of elements */
1373 };
1374
1375 /*
1376 ** An instance of the following structure holds the context of a
1377 ** mode() or median() aggregate computation.
1378 ** Depends on structures defined in map.c (see map & map)
1379 ** These aggregate functions only work for integers and floats although
1380 ** they could be made to work for strings. This is usually considered meaningless.
1381 ** Only usuall order (for median), no use of collation functions (would this even make sense?)
1382 */
1383 typedef struct ModeCtx ModeCtx;
1384 struct ModeCtx {
1385 i64 riM; /* integer value found so far */
1386 double rdM; /* double value found so far */
1387 i64 cnt; /* number of elements so far */
1388 double pcnt; /* number of elements smaller than a percentile */
1389 i64 mcnt; /* maximum number of occurrences (for mode) */
1390 i64 mn; /* number of occurrences (for mode and percentiles) */
1391 i64 is_double; /* whether the computation is being done for doubles (>0) or integers (=0) */
1392 map* m; /* map structure used for the computation */
1393 int done; /* whether the answer has been found */
1394 };
1395
1396 /*
1397 ** called for each value received during a calculation of stdev or variance
1398 */
varianceStep(sqlite3_context * context,int argc,sqlite3_value ** argv)1399 static void varianceStep(sqlite3_context *context, int argc, sqlite3_value **argv){
1400 StdevCtx *p;
1401
1402 double delta;
1403 double x;
1404
1405 assert( argc==1 );
1406 p = (StdevCtx *) sqlite3_aggregate_context(context, sizeof(*p));
1407 /* only consider non-null values */
1408 if( SQLITE_NULL != sqlite3_value_numeric_type(argv[0]) ){
1409 p->cnt++;
1410 x = sqlite3_value_double(argv[0]);
1411 delta = (x-p->rM);
1412 p->rM += delta/p->cnt;
1413 p->rS += delta*(x-p->rM);
1414 }
1415 }
1416
1417 /*
1418 ** called for each value received during a calculation of mode of median
1419 */
modeStep(sqlite3_context * context,int argc,sqlite3_value ** argv)1420 static void modeStep(sqlite3_context *context, int argc, sqlite3_value **argv){
1421 ModeCtx *p;
1422 i64 xi=0;
1423 double xd=0.0;
1424 i64 *iptr;
1425 double *dptr;
1426 int type;
1427
1428 assert( argc==1 );
1429 type = sqlite3_value_numeric_type(argv[0]);
1430
1431 if( type == SQLITE_NULL)
1432 return;
1433
1434 p = (ModeCtx *) sqlite3_aggregate_context(context, sizeof(*p));
1435
1436 if( 0==(p->m) ){
1437 p->m = (map *) calloc(1, sizeof(map));
1438 if( type==SQLITE_INTEGER ){
1439 /* map will be used for integers */
1440 *(p->m) = map_make(int_cmp);
1441 p->is_double = 0;
1442 }else{
1443 p->is_double = 1;
1444 /* map will be used for doubles */
1445 *(p->m) = map_make(double_cmp);
1446 }
1447 }
1448
1449 ++(p->cnt);
1450
1451 if( 0==p->is_double ){
1452 xi = sqlite3_value_int64(argv[0]);
1453 iptr = (i64*)calloc(1,sizeof(i64));
1454 *iptr = xi;
1455 map_insert(p->m, iptr);
1456 }else{
1457 xd = sqlite3_value_double(argv[0]);
1458 dptr = (double*)calloc(1,sizeof(double));
1459 *dptr = xd;
1460 map_insert(p->m, dptr);
1461 }
1462 }
1463
1464 /*
1465 ** Auxiliary function that iterates all elements in a map and finds the mode
1466 ** (most frequent value)
1467 */
modeIterate(void * e,i64 c,void * pp)1468 static void modeIterate(void* e, i64 c, void* pp){
1469 i64 ei;
1470 double ed;
1471 ModeCtx *p = (ModeCtx*)pp;
1472
1473 if( 0==p->is_double ){
1474 ei = *(int*)(e);
1475
1476 if( p->mcnt==c ){
1477 ++p->mn;
1478 }else if( p->mcnt<c ){
1479 p->riM = ei;
1480 p->mcnt = c;
1481 p->mn=1;
1482 }
1483 }else{
1484 ed = *(double*)(e);
1485
1486 if( p->mcnt==c ){
1487 ++p->mn;
1488 }else if(p->mcnt<c){
1489 p->rdM = ed;
1490 p->mcnt = c;
1491 p->mn=1;
1492 }
1493 }
1494 }
1495
1496 /*
1497 ** Auxiliary function that iterates all elements in a map and finds the median
1498 ** (the value such that the number of elements smaller is equal the number of
1499 ** elements larger)
1500 */
medianIterate(void * e,i64 c,void * pp)1501 static void medianIterate(void* e, i64 c, void* pp){
1502 i64 ei;
1503 double ed;
1504 double iL;
1505 double iR;
1506 int il;
1507 int ir;
1508 ModeCtx *p = (ModeCtx*)pp;
1509
1510 if(p->done>0)
1511 return;
1512
1513 iL = p->pcnt;
1514 iR = p->cnt - p->pcnt;
1515 il = p->mcnt + c;
1516 ir = p->cnt - p->mcnt;
1517
1518 if( il >= iL ){
1519 if( ir >= iR ){
1520 ++p->mn;
1521 if( 0==p->is_double ){
1522 ei = *(int*)(e);
1523 p->riM += ei;
1524 }else{
1525 ed = *(double*)(e);
1526 p->rdM += ed;
1527 }
1528 }else{
1529 p->done=1;
1530 }
1531 }
1532 p->mcnt+=c;
1533 }
1534
1535 /*
1536 ** Returns the mode value
1537 */
modeFinalize(sqlite3_context * context)1538 static void modeFinalize(sqlite3_context *context){
1539 ModeCtx *p;
1540 p = (ModeCtx *) sqlite3_aggregate_context(context, 0);
1541 if( p && p->m ){
1542 map_iterate(p->m, modeIterate, p);
1543 map_destroy(p->m);
1544 free(p->m);
1545
1546 if( 1==p->mn ){
1547 if( 0==p->is_double )
1548 sqlite3_result_int64(context, p->riM);
1549 else
1550 sqlite3_result_double(context, p->rdM);
1551 }
1552 }
1553 }
1554
1555 /*
1556 ** auxiliary function for percentiles
1557 */
_medianFinalize(sqlite3_context * context)1558 static void _medianFinalize(sqlite3_context *context){
1559 ModeCtx *p;
1560 p = (ModeCtx*) sqlite3_aggregate_context(context, 0);
1561 if( p && p->m ){
1562 p->done=0;
1563 map_iterate(p->m, medianIterate, p);
1564 map_destroy(p->m);
1565 free(p->m);
1566
1567 if( 0==p->is_double )
1568 if( 1==p->mn )
1569 sqlite3_result_int64(context, p->riM);
1570 else
1571 sqlite3_result_double(context, p->riM*1.0/p->mn);
1572 else
1573 sqlite3_result_double(context, p->rdM/p->mn);
1574 }
1575 }
1576
1577 /*
1578 ** Returns the median value
1579 */
medianFinalize(sqlite3_context * context)1580 static void medianFinalize(sqlite3_context *context){
1581 ModeCtx *p;
1582 p = (ModeCtx*) sqlite3_aggregate_context(context, 0);
1583 if( p!=0 ){
1584 p->pcnt = (p->cnt)/2.0;
1585 _medianFinalize(context);
1586 }
1587 }
1588
1589 /*
1590 ** Returns the lower_quartile value
1591 */
lower_quartileFinalize(sqlite3_context * context)1592 static void lower_quartileFinalize(sqlite3_context *context){
1593 ModeCtx *p;
1594 p = (ModeCtx*) sqlite3_aggregate_context(context, 0);
1595 if( p!=0 ){
1596 p->pcnt = (p->cnt)/4.0;
1597 _medianFinalize(context);
1598 }
1599 }
1600
1601 /*
1602 ** Returns the upper_quartile value
1603 */
upper_quartileFinalize(sqlite3_context * context)1604 static void upper_quartileFinalize(sqlite3_context *context){
1605 ModeCtx *p;
1606 p = (ModeCtx*) sqlite3_aggregate_context(context, 0);
1607 if( p!=0 ){
1608 p->pcnt = (p->cnt)*3/4.0;
1609 _medianFinalize(context);
1610 }
1611 }
1612
1613 /*
1614 ** Returns the stdev value
1615 */
stdevFinalize(sqlite3_context * context)1616 static void stdevFinalize(sqlite3_context *context){
1617 StdevCtx *p;
1618 p = (StdevCtx *) sqlite3_aggregate_context(context, 0);
1619 if( p && p->cnt>1 ){
1620 sqlite3_result_double(context, sqrt(p->rS/(p->cnt-1)));
1621 }else{
1622 sqlite3_result_double(context, 0.0);
1623 }
1624 }
1625
1626 /*
1627 ** Returns the variance value
1628 */
varianceFinalize(sqlite3_context * context)1629 static void varianceFinalize(sqlite3_context *context){
1630 StdevCtx *p;
1631 p = (StdevCtx *) sqlite3_aggregate_context(context, 0);
1632 if( p && p->cnt>1 ){
1633 sqlite3_result_double(context, p->rS/(p->cnt-1));
1634 }else{
1635 sqlite3_result_double(context, 0.0);
1636 }
1637 }
1638
1639 #ifdef SQLITE_SOUNDEX
1640
1641 /* relicoder factored code */
1642 /*
1643 ** Calculates the soundex value of a string
1644 */
1645
soundex(const u8 * zIn,char * zResult)1646 static void soundex(const u8 *zIn, char *zResult){
1647 int i, j;
1648 static const unsigned char iCode[] = {
1649 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1650 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1651 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1652 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1653 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
1654 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
1655 0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
1656 1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
1657 };
1658
1659 for(i=0; zIn[i] && !isalpha(zIn[i]); i++){}
1660 if( zIn[i] ){
1661 zResult[0] = toupper(zIn[i]);
1662 for(j=1; j<4 && zIn[i]; i++){
1663 int code = iCode[zIn[i]&0x7f];
1664 if( code>0 ){
1665 zResult[j++] = code + '0';
1666 }
1667 }
1668 while( j<4 ){
1669 zResult[j++] = '0';
1670 }
1671 zResult[j] = 0;
1672 }else{
1673 strcpy(zResult, "?000");
1674 }
1675 }
1676
1677 /*
1678 ** computes the number of different characters between the soundex value fo 2 strings
1679 */
differenceFunc(sqlite3_context * context,int argc,sqlite3_value ** argv)1680 static void differenceFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
1681 char zResult1[8];
1682 char zResult2[8];
1683 char *zR1 = zResult1;
1684 char *zR2 = zResult2;
1685 int rVal = 0;
1686 int i = 0;
1687 const u8 *zIn1;
1688 const u8 *zIn2;
1689
1690 assert( argc==2 );
1691
1692 if( sqlite3_value_type(argv[0])==SQLITE_NULL || sqlite3_value_type(argv[1])==SQLITE_NULL ){
1693 sqlite3_result_null(context);
1694 return;
1695 }
1696
1697 zIn1 = (u8*)sqlite3_value_text(argv[0]);
1698 zIn2 = (u8*)sqlite3_value_text(argv[1]);
1699
1700 soundex(zIn1, zR1);
1701 soundex(zIn2, zR2);
1702
1703 for(i=0; i<4; ++i){
1704 if( sqliteCharVal((unsigned char *)zR1)==sqliteCharVal((unsigned char *)zR2) )
1705 ++rVal;
1706 sqliteNextChar(zR1);
1707 sqliteNextChar(zR2);
1708 }
1709 sqlite3_result_int(context, rVal);
1710 }
1711 #endif
1712
1713 /*
1714 ** This function registered all of the above C functions as SQL
1715 ** functions. This should be the only routine in this file with
1716 ** external linkage.
1717 */
common_extension_functions(struct FuncDef ** basic_funcs,struct FuncDefAgg ** agg_funcs)1718 int common_extension_functions(struct FuncDef **basic_funcs,
1719 struct FuncDefAgg **agg_funcs) {
1720 static struct FuncDef aFuncs[] = {
1721 /* math.h */
1722 { "acos", 1, SQLITE_UTF8, 0, acosFunc,
1723 help_text("acos")
1724 .sql_function()
1725 .with_summary("Returns the arccosine of a number, in radians")
1726 .with_parameter({"num", "A cosine value that is between -1 and 1"})
1727 .with_tags({"math"})
1728 .with_example({
1729 "To get the arccosine of 0.2",
1730 "SELECT acos(0.2)"
1731 })},
1732 { "asin", 1, SQLITE_UTF8, 0, asinFunc,
1733 help_text("asin")
1734 .sql_function()
1735 .with_summary("Returns the arcsine of a number, in radians")
1736 .with_parameter({"num", "A sine value that is between -1 and 1"})
1737 .with_tags({"math"})
1738 .with_example({
1739 "To get the arcsine of 0.2",
1740 "SELECT asin(0.2)"
1741 })},
1742 { "atan", 1, SQLITE_UTF8, 0, atanFunc,
1743 help_text("atan")
1744 .sql_function()
1745 .with_summary("Returns the arctangent of a number, in radians")
1746 .with_parameter({"num", "The number"})
1747 .with_tags({"math"})
1748 .with_example({
1749 "To get the arctangent of 0.2",
1750 "SELECT atan(0.2)"
1751 })},
1752 { "atn2", 2, SQLITE_UTF8, 0, atn2Func,
1753 help_text("atn2")
1754 .sql_function()
1755 .with_summary("Returns the angle in the plane between the positive X axis and the ray from (0, 0) to the point (x, y)")
1756 .with_parameter({"y", "The y coordinate of the point"})
1757 .with_parameter({"x", "The x coordinate of the point"})
1758 .with_tags({"math"})
1759 .with_example({
1760 "To get the angle, in degrees, for the point at (5, 5)",
1761 "SELECT degrees(atn2(5, 5))"
1762 })},
1763 /* XXX alias */
1764 { "atan2", 2, SQLITE_UTF8, 0, atn2Func,
1765 help_text("atan2")
1766 .sql_function()
1767 .with_summary("Returns the angle in the plane between the positive X axis and the ray from (0, 0) to the point (x, y)")
1768 .with_parameter({"y", "The y coordinate of the point"})
1769 .with_parameter({"x", "The x coordinate of the point"})
1770 .with_tags({"math"})
1771 .with_example({
1772 "To get the angle, in degrees, for the point at (5, 5)",
1773 "SELECT degrees(atan2(5, 5))"
1774 })},
1775 { "acosh", 1, SQLITE_UTF8, 0, acoshFunc,
1776 help_text("acosh")
1777 .sql_function()
1778 .with_summary("Returns the hyperbolic arccosine of a number")
1779 .with_parameter({"num", "A number that is one or more"})
1780 .with_tags({"math"})
1781 .with_example({
1782 "To get the hyperbolic arccosine of 1.2",
1783 "SELECT acosh(1.2)"
1784 })},
1785 { "asinh", 1, SQLITE_UTF8, 0, asinhFunc,
1786 help_text("asinh")
1787 .sql_function()
1788 .with_summary("Returns the hyperbolic arcsine of a number")
1789 .with_parameter({"num", "The number"})
1790 .with_tags({"math"})
1791 .with_example({
1792 "To get the hyperbolic arcsine of 0.2",
1793 "SELECT asinh(0.2)"
1794 })},
1795 { "atanh", 1, SQLITE_UTF8, 0, atanhFunc,
1796 help_text("atanh")
1797 .sql_function()
1798 .with_summary("Returns the hyperbolic arctangent of a number")
1799 .with_parameter({"num", "The number"})
1800 .with_tags({"math"})
1801 .with_example({
1802 "To get the hyperbolic arctangent of 0.2",
1803 "SELECT atanh(0.2)"
1804 })},
1805
1806 { "difference", 2, SQLITE_UTF8, 0, differenceFunc},
1807 { "degrees", 1, SQLITE_UTF8, 0, rad2degFunc,
1808 help_text("degrees")
1809 .sql_function()
1810 .with_summary("Converts radians to degrees")
1811 .with_parameter({"radians", "The radians value to convert to degrees"})
1812 .with_tags({"math"})
1813 .with_example({
1814 "To convert PI to degrees",
1815 "SELECT degrees(pi())"
1816 })},
1817 { "radians", 1, SQLITE_UTF8, 0, deg2radFunc,
1818 help_text("radians")
1819 .sql_function()
1820 .with_summary("Converts degrees to radians")
1821 .with_parameter({"degrees", "The degrees value to convert to radians"})
1822 .with_tags({"math"})
1823 .with_example({
1824 "To convert 180 degrees to radians",
1825 "SELECT radians(180)"
1826 })},
1827
1828 { "cos", 1, SQLITE_UTF8, 0, cosFunc },
1829 { "sin", 1, SQLITE_UTF8, 0, sinFunc },
1830 { "tan", 1, SQLITE_UTF8, 0, tanFunc },
1831 { "cot", 1, SQLITE_UTF8, 0, cotFunc },
1832 { "cosh", 1, SQLITE_UTF8, 0, coshFunc },
1833 { "sinh", 1, SQLITE_UTF8, 0, sinhFunc },
1834 { "tanh", 1, SQLITE_UTF8, 0, tanhFunc },
1835 { "coth", 1, SQLITE_UTF8, 0, cothFunc },
1836
1837 { "exp", 1, SQLITE_UTF8, 0, expFunc,
1838 help_text("exp")
1839 .sql_function()
1840 .with_summary("Returns the value of e raised to the power of x")
1841 .with_parameter({"x", "The exponent"})
1842 .with_tags({"math"})
1843 .with_example({
1844 "To raise e to 2",
1845 "SELECT exp(2)"
1846 })},
1847 { "log", 1, SQLITE_UTF8, 0, logFunc,
1848 help_text("log")
1849 .sql_function()
1850 .with_summary("Returns the natural logarithm of x")
1851 .with_parameter({"x", "The number"})
1852 .with_tags({"math"})
1853 .with_example({
1854 "To get the natual logarithm of 8",
1855 "SELECT log(8)"
1856 })},
1857 { "log10", 1, SQLITE_UTF8, 0, log10Func,
1858 help_text("log10")
1859 .sql_function()
1860 .with_summary("Returns the base-10 logarithm of X")
1861 .with_parameter({"x", "The number"})
1862 .with_tags({"math"})
1863 .with_example({
1864 "To get the logarithm of 100",
1865 "SELECT log10(100)"
1866 })},
1867 { "power", 2, SQLITE_UTF8, 0, powerFunc,
1868 help_text("power")
1869 .sql_function()
1870 .with_summary("Returns the base to the given exponent")
1871 .with_parameter({"base", "The base number"})
1872 .with_parameter({"exp", "The exponent"})
1873 .with_tags({"math"})
1874 .with_example({
1875 "To raise two to the power of three",
1876 "SELECT power(2, 3)"
1877 })},
1878 { "sign", 1, SQLITE_UTF8, 0, signFunc,
1879 help_text("sign")
1880 .sql_function()
1881 .with_summary("Returns the sign of the given number as -1, 0, or 1")
1882 .with_parameter({"num", "The number"})
1883 .with_tags({"math"})
1884 .with_example({
1885 "To get the sign of 10",
1886 "SELECT sign(10)"
1887 })
1888 .with_example({
1889 "To get the sign of 0",
1890 "SELECT sign(0)"
1891 })
1892 .with_example({
1893 "To get the sign of -10",
1894 "SELECT sign(-10)"
1895 })},
1896 { "sqrt", 1, SQLITE_UTF8, 0, sqrtFunc },
1897 { "square", 1, SQLITE_UTF8, 0, squareFunc,
1898 help_text("square")
1899 .sql_function()
1900 .with_summary("Returns the square of the argument")
1901 .with_parameter({"num", "The number to square"})
1902 .with_tags({"math"})
1903 .with_example({
1904 "To get the square of two",
1905 "SELECT square(2)"
1906 })},
1907
1908 { "ceil", 1, SQLITE_UTF8, 0, ceilFunc,
1909 help_text("ceil")
1910 .sql_function()
1911 .with_summary("Returns the smallest integer that is not less than the argument")
1912 .with_parameter({"num", "The number to raise to the ceiling"})
1913 .with_tags({"math"})
1914 .with_example({
1915 "To get the ceiling of 1.23",
1916 "SELECT ceil(1.23)"
1917 })},
1918 { "floor", 1, SQLITE_UTF8, 0, floorFunc,
1919 help_text("floor")
1920 .sql_function()
1921 .with_summary("Returns the largest integer that is not greater than the argument")
1922 .with_parameter({"num", "The number to lower to the floor"})
1923 .with_tags({"math"})
1924 .with_example({
1925 "To get the floor of 1.23",
1926 "SELECT floor(1.23)"
1927 })},
1928
1929 { "pi", 0, SQLITE_UTF8, 1, piFunc,
1930 help_text("pi")
1931 .sql_function()
1932 .with_summary("Returns the value of PI")
1933 .with_tags({"math"})
1934 .with_example({
1935 "To get the value of PI",
1936 "SELECT pi()"
1937 })},
1938
1939
1940 /* string */
1941 { "replicate", 2, SQLITE_UTF8, 0, replicateFunc,
1942 help_text("replicate")
1943 .sql_function()
1944 .with_summary("Returns the given string concatenated N times.")
1945 .with_parameter({"str", "The string to replicate."})
1946 .with_parameter({"N", "The number of times to replicate the string."})
1947 .with_tags({"string"})
1948 .with_example({
1949 "To repeat the string 'abc' three times",
1950 "SELECT replicate('abc', 3)"
1951 })
1952 },
1953 { "charindex", 2, SQLITE_UTF8, 0, charindexFunc },
1954 { "charindex", 3, SQLITE_UTF8, 0, charindexFunc,
1955 help_text("charindex")
1956 .sql_function()
1957 .with_summary("Finds the first occurrence of the needle within the haystack and returns the number of prior characters plus 1, or 0 if Y is nowhere found within X")
1958 .with_parameter({"needle", "The string to look for in the haystack"})
1959 .with_parameter({"haystack", "The string to search within"})
1960 .with_parameter(help_text("start", "The one-based index within the haystack to start the search")
1961 .optional())
1962 .with_tags({"string"})
1963 .with_example({
1964 "To search for the string 'abc' within 'abcabc' and starting at position 2",
1965 "SELECT charindex('abc', 'abcabc', 2)"
1966 })
1967 .with_example({
1968 "To search for the string 'abc' within 'abcdef' and starting at position 2",
1969 "SELECT charindex('abc', 'abcdef', 2)"
1970 })},
1971 { "leftstr", 2, SQLITE_UTF8, 0, leftFunc,
1972 help_text("leftstr")
1973 .sql_function()
1974 .with_summary("Returns the N leftmost (UTF-8) characters in the given string.")
1975 .with_parameter({"str", "The string to return subset."})
1976 .with_parameter({"N", "The number of characters from the left side of the string to return."})
1977 .with_tags({"string"})
1978 .with_example({
1979 "To get the first character of the string 'abc'",
1980 "SELECT leftstr('abc', 1)"
1981 })
1982 .with_example({
1983 "To get the first ten characters of a string, regardless of size",
1984 "SELECT leftstr('abc', 10)"
1985 })
1986 },
1987 { "rightstr", 2, SQLITE_UTF8, 0, rightFunc,
1988 help_text("rightstr")
1989 .sql_function()
1990 .with_summary("Returns the N rightmost (UTF-8) characters in the given string.")
1991 .with_parameter({"str", "The string to return subset."})
1992 .with_parameter({"N", "The number of characters from the right side of the string to return."})
1993 .with_tags({"string"})
1994 .with_example({
1995 "To get the last character of the string 'abc'",
1996 "SELECT rightstr('abc', 1)"
1997 })
1998 .with_example({
1999 "To get the last ten characters of a string, regardless of size",
2000 "SELECT rightstr('abc', 10)"
2001 })
2002 },
2003 #ifndef HAVE_TRIM
2004 { "ltrim", 1, SQLITE_UTF8, 0, ltrimFunc },
2005 { "rtrim", 1, SQLITE_UTF8, 0, rtrimFunc },
2006 { "trim", 1, SQLITE_UTF8, 0, trimFunc },
2007 { "replace", 3, SQLITE_UTF8, 0, replaceFunc },
2008 #endif
2009 { "reverse", 1, SQLITE_UTF8, 0, reverseFunc,
2010 help_text("reverse")
2011 .sql_function()
2012 .with_summary("Returns the reverse of the given string.")
2013 .with_parameter({"str", "The string to reverse."})
2014 .with_tags({"string"})
2015 .with_example({
2016 "To reverse the string 'abc'",
2017 "SELECT reverse('abc')"
2018 })
2019 },
2020 { "proper", 1, SQLITE_UTF8, 0, properFunc,
2021 help_text("proper")
2022 .sql_function()
2023 .with_summary("Capitalize the first character of words in the given string")
2024 .with_parameter({"str", "The string to capitalize."})
2025 .with_tags({"string"})
2026 .with_example({
2027 "To capitalize the words in the string 'hello, world!'",
2028 "SELECT proper('hello, world!')"
2029 })},
2030 { "padl", 2, SQLITE_UTF8, 0, padlFunc,
2031 help_text("padl")
2032 .sql_function()
2033 .with_summary("Pad the given string with leading spaces until it reaches the desired length")
2034 .with_parameter({"str", "The string to pad"})
2035 .with_parameter({"len", "The minimum desired length of the output string"})
2036 .with_tags({"string"})
2037 .with_example({
2038 "To pad the string 'abc' to a length of six characters",
2039 "SELECT padl('abc', 6)"
2040 })
2041 .with_example({
2042 "To pad the string 'abcdef' to a length of four characters",
2043 "SELECT padl('abcdef', 4)"
2044 })},
2045 { "padr", 2, SQLITE_UTF8, 0, padrFunc,
2046 help_text("padr")
2047 .sql_function()
2048 .with_summary("Pad the given string with trailing spaces until it reaches the desired length")
2049 .with_parameter({"str", "The string to pad"})
2050 .with_parameter({"len", "The minimum desired length of the output string"})
2051 .with_tags({"string"})
2052 .with_example({
2053 "To pad the string 'abc' to a length of six characters",
2054 "SELECT padr('abc', 6) || 'def'"
2055 })
2056 .with_example({
2057 "To pad the string 'abcdef' to a length of four characters",
2058 "SELECT padr('abcdef', 4) || 'ghi'"
2059 })},
2060 { "padc", 2, SQLITE_UTF8, 0, padcFunc,
2061 help_text("padc")
2062 .sql_function()
2063 .with_summary("Pad the given string with enough spaces to make it centered within the given length")
2064 .with_parameter({"str", "The string to pad"})
2065 .with_parameter({"len", "The minimum desired length of the output string"})
2066 .with_tags({"string"})
2067 .with_example({
2068 "To pad the string 'abc' to a length of six characters",
2069 "SELECT padc('abc', 6) || 'def'"
2070 })
2071 .with_example({
2072 "To pad the string 'abcdef' to a length of eight characters",
2073 "SELECT padc('abcdef', 8) || 'ghi'"
2074 })},
2075 { "strfilter", 2, SQLITE_UTF8, 0, strfilterFunc,
2076 help_text("strfilter")
2077 .sql_function()
2078 .with_summary("Returns the source string with only the characters given in the second parameter")
2079 .with_parameter({"source", "The string to filter"})
2080 .with_parameter({"include", "The characters to include in the result"})
2081 .with_tags({"string"})
2082 .with_example({
2083 "To get the 'b', 'c', and 'd' characters from the string 'abcabc'",
2084 "SELECT strfilter('abcabc', 'bcd')"
2085 })},
2086
2087 { NULL }
2088 };
2089
2090 /* Aggregate functions */
2091 static struct FuncDefAgg aAggs[] = {
2092 { "stdev", 1, 0, varianceStep, stdevFinalize },
2093 { "stddev", 1, 0, varianceStep, stdevFinalize },
2094 { "variance", 1, 0, varianceStep, varianceFinalize },
2095 { "mode", 1, 0, modeStep, modeFinalize },
2096 { "median", 1, 0, modeStep, medianFinalize },
2097 { "lower_quartile", 1, 0, modeStep, lower_quartileFinalize },
2098 { "upper_quartile", 1, 0, modeStep, upper_quartileFinalize },
2099
2100 { NULL }
2101 };
2102
2103 *basic_funcs = aFuncs;
2104 *agg_funcs = aAggs;
2105
2106 return SQLITE_OK;
2107 }
2108
2109 #ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE
sqlite3_extension_init(sqlite3 * db,char ** pzErrMsg,const sqlite3_api_routines * pApi)2110 int sqlite3_extension_init(
2111 sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi){
2112 SQLITE_EXTENSION_INIT2(pApi);
2113 RegisterExtensionFunctions(db);
2114 return 0;
2115 }
2116 #endif /* COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE */
2117
map_make(cmp_func cmp)2118 map map_make(cmp_func cmp){
2119 map r;
2120 r.cmp=cmp;
2121 r.base = 0;
2122 r.free = 0;
2123
2124 return r;
2125 }
2126
xcalloc(size_t nmemb,size_t size,const char * s)2127 void* xcalloc(size_t nmemb, size_t size, const char* s){
2128 void* ret = calloc(nmemb, size);
2129 return ret;
2130 }
2131
xfree(void * p)2132 static void xfree(void* p){
2133 free(p);
2134 }
2135
node_insert(node ** n,cmp_func cmp,void * e)2136 void node_insert(node** n, cmp_func cmp, void *e){
2137 int c;
2138 node* nn;
2139 if(*n==0){
2140 nn = (node*)xcalloc(1,sizeof(node), "for node");
2141 nn->data = e;
2142 nn->count = 1;
2143 *n=nn;
2144 }else{
2145 c=cmp((*n)->data,e);
2146 if(0==c){
2147 ++((*n)->count);
2148 xfree(e);
2149 }else if(c>0){
2150 /* put it right here */
2151 node_insert(&((*n)->l), cmp, e);
2152 }else{
2153 node_insert(&((*n)->r), cmp, e);
2154 }
2155 }
2156 }
2157
map_insert(map * m,void * e)2158 void map_insert(map *m, void *e){
2159 node_insert(&(m->base), m->cmp, e);
2160 }
2161
node_iterate(node * n,map_iterator iter,void * p)2162 void node_iterate(node *n, map_iterator iter, void* p){
2163 if(n){
2164 if(n->l)
2165 node_iterate(n->l, iter, p);
2166 iter(n->data, n->count, p);
2167 if(n->r)
2168 node_iterate(n->r, iter, p);
2169 }
2170 }
2171
map_iterate(map * m,map_iterator iter,void * p)2172 void map_iterate(map *m, map_iterator iter, void* p){
2173 node_iterate(m->base, iter, p);
2174 }
2175
node_destroy(node * n)2176 void node_destroy(node *n){
2177 if(0!=n){
2178 xfree(n->data);
2179 if(n->l)
2180 node_destroy(n->l);
2181 if(n->r)
2182 node_destroy(n->r);
2183
2184 xfree(n);
2185 }
2186 }
2187
map_destroy(map * m)2188 void map_destroy(map *m){
2189 node_destroy(m->base);
2190 }
2191
int_cmp(const void * a,const void * b)2192 int int_cmp(const void *a, const void *b){
2193 int64_t aa = *(int64_t *)(a);
2194 int64_t bb = *(int64_t *)(b);
2195 /* printf("cmp %d <=> %d\n",aa,bb); */
2196 if(aa==bb)
2197 return 0;
2198 else if(aa<bb)
2199 return -1;
2200 else
2201 return 1;
2202 }
2203
double_cmp(const void * a,const void * b)2204 int double_cmp(const void *a, const void *b){
2205 double aa = *(double *)(a);
2206 double bb = *(double *)(b);
2207 /* printf("cmp %d <=> %d\n",aa,bb); */
2208 if(aa==bb)
2209 return 0;
2210 else if(aa<bb)
2211 return -1;
2212 else
2213 return 1;
2214 }
2215
print_elem(void * e,int64_t c,void * p)2216 void print_elem(void *e, int64_t c, void* p){
2217 int ee = *(int*)(e);
2218 printf("%d => %" PRId64 "\n", ee,c);
2219 }
2220
2221