LuaSQLite3

Check-in [d1f2e3f61b]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add tests for load_extension.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d1f2e3f61be8bb66d0a257e78d56a9471d40cd2a
User & Date: e 2015-01-05 23:16:45
Context
2015-01-06
03:06
Add docn for db:load_extension check-in: c85464e1bc user: e tags: trunk
2015-01-05
23:16
Add tests for load_extension. check-in: d1f2e3f61b user: e tags: trunk
21:26
Update rockspec for bug fix release. check-in: 1d5512efeb user: e tags: fsl_9v, trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added extras/extension-functions.c.

            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  +#ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE
          124  +#include "sqlite3ext.h"
          125  +SQLITE_EXTENSION_INIT1
          126  +#else
          127  +#include "sqlite3.h"
          128  +#endif
          129  +
          130  +#include <ctype.h>
          131  +/* relicoder */
          132  +#include <math.h>
          133  +#include <string.h>
          134  +#include <stdio.h>
          135  +#include <errno.h>		/* LMH 2007-03-25 */
          136  +
          137  +#include <stdlib.h>
          138  +#include <assert.h>
          139  +
          140  +#ifndef _MAP_H_
          141  +#define _MAP_H_
          142  +
          143  +#include <stdint.h>
          144  +
          145  +/*
          146  +** Simple binary tree implementation to use in median, mode and quartile calculations
          147  +** Tree is not necessarily balanced. That would require something like red&black trees of AVL
          148  +*/
          149  +
          150  +typedef int(*cmp_func)(const void *, const void *);
          151  +typedef void(*map_iterator)(void*, int64_t, void*);
          152  +
          153  +typedef struct node{
          154  +  struct node *l;
          155  +  struct node *r;
          156  +  void* data;
          157  +  int64_t count;
          158  +} node;
          159  +
          160  +typedef struct map{
          161  +  node *base;
          162  +  cmp_func cmp;
          163  +  short free;
          164  +} map;
          165  +
          166  +/*
          167  +** creates a map given a comparison function
          168  +*/
          169  +map map_make(cmp_func cmp);
          170  +
          171  +/*
          172  +** inserts the element e into map m
          173  +*/
          174  +void map_insert(map *m, void *e);
          175  +
          176  +/*
          177  +** executes function iter over all elements in the map, in key increasing order
          178  +*/
          179  +void map_iterate(map *m, map_iterator iter, void* p);
          180  +
          181  +/*
          182  +** frees all memory used by a map
          183  +*/
          184  +void map_destroy(map *m);
          185  +
          186  +/*
          187  +** compares 2 integers
          188  +** to use with map_make
          189  +*/
          190  +int int_cmp(const void *a, const void *b);
          191  +
          192  +/*
          193  +** compares 2 doubles
          194  +** to use with map_make
          195  +*/
          196  +int double_cmp(const void *a, const void *b);
          197  +
          198  +#endif /* _MAP_H_ */
          199  +
          200  +typedef uint8_t         u8;
          201  +typedef uint16_t        u16;
          202  +typedef int64_t         i64;
          203  +
          204  +static char *sqlite3StrDup( const char *z ) {
          205  +    char *res = sqlite3_malloc( strlen(z)+1 );
          206  +    return strcpy( res, z );
          207  +}
          208  +
          209  +/*
          210  +** These are copied verbatim from fun.c so as to not have the names exported
          211  +*/
          212  +
          213  +/* LMH from sqlite3 3.3.13 */
          214  +/*
          215  +** This table maps from the first byte of a UTF-8 character to the number
          216  +** of trailing bytes expected. A value '4' indicates that the table key
          217  +** is not a legal first byte for a UTF-8 character.
          218  +*/
          219  +static const u8 xtra_utf8_bytes[256]  = {
          220  +/* 0xxxxxxx */
          221  +0, 0, 0, 0, 0, 0, 0, 0,     0, 0, 0, 0, 0, 0, 0, 0,
          222  +0, 0, 0, 0, 0, 0, 0, 0,     0, 0, 0, 0, 0, 0, 0, 0,
          223  +0, 0, 0, 0, 0, 0, 0, 0,     0, 0, 0, 0, 0, 0, 0, 0,
          224  +0, 0, 0, 0, 0, 0, 0, 0,     0, 0, 0, 0, 0, 0, 0, 0,
          225  +0, 0, 0, 0, 0, 0, 0, 0,     0, 0, 0, 0, 0, 0, 0, 0,
          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  +
          230  +/* 10wwwwww */
          231  +4, 4, 4, 4, 4, 4, 4, 4,     4, 4, 4, 4, 4, 4, 4, 4,
          232  +4, 4, 4, 4, 4, 4, 4, 4,     4, 4, 4, 4, 4, 4, 4, 4,
          233  +4, 4, 4, 4, 4, 4, 4, 4,     4, 4, 4, 4, 4, 4, 4, 4,
          234  +4, 4, 4, 4, 4, 4, 4, 4,     4, 4, 4, 4, 4, 4, 4, 4,
          235  +
          236  +/* 110yyyyy */
          237  +1, 1, 1, 1, 1, 1, 1, 1,     1, 1, 1, 1, 1, 1, 1, 1,
          238  +1, 1, 1, 1, 1, 1, 1, 1,     1, 1, 1, 1, 1, 1, 1, 1,
          239  +
          240  +/* 1110zzzz */
          241  +2, 2, 2, 2, 2, 2, 2, 2,     2, 2, 2, 2, 2, 2, 2, 2,
          242  +
          243  +/* 11110yyy */
          244  +3, 3, 3, 3, 3, 3, 3, 3,     4, 4, 4, 4, 4, 4, 4, 4,
          245  +};
          246  +
          247  +
          248  +/*
          249  +** This table maps from the number of trailing bytes in a UTF-8 character
          250  +** to an integer constant that is effectively calculated for each character
          251  +** read by a naive implementation of a UTF-8 character reader. The code
          252  +** in the READ_UTF8 macro explains things best.
          253  +*/
          254  +static const int xtra_utf8_bits[] =  {
          255  +  0,
          256  +  12416,          /* (0xC0 << 6) + (0x80) */
          257  +  925824,         /* (0xE0 << 12) + (0x80 << 6) + (0x80) */
          258  +  63447168        /* (0xF0 << 18) + (0x80 << 12) + (0x80 << 6) + 0x80 */
          259  +};
          260  +
          261  +/*
          262  +** If a UTF-8 character contains N bytes extra bytes (N bytes follow
          263  +** the initial byte so that the total character length is N+1) then
          264  +** masking the character with utf8_mask[N] must produce a non-zero
          265  +** result.  Otherwise, we have an (illegal) overlong encoding.
          266  +*/
          267  +static const int utf_mask[] = {
          268  +  0x00000000,
          269  +  0xffffff80,
          270  +  0xfffff800,
          271  +  0xffff0000,
          272  +};
          273  +
          274  +/* LMH salvaged from sqlite3 3.3.13 source code src/utf.c */
          275  +#define READ_UTF8(zIn, c) { \
          276  +  int xtra;                                            \
          277  +  c = *(zIn)++;                                        \
          278  +  xtra = xtra_utf8_bytes[c];                           \
          279  +  switch( xtra ){                                      \
          280  +    case 4: c = (int)0xFFFD; break;                    \
          281  +    case 3: c = (c<<6) + *(zIn)++;                     \
          282  +    case 2: c = (c<<6) + *(zIn)++;                     \
          283  +    case 1: c = (c<<6) + *(zIn)++;                     \
          284  +    c -= xtra_utf8_bits[xtra];                         \
          285  +    if( (utf_mask[xtra]&c)==0                          \
          286  +        || (c&0xFFFFF800)==0xD800                      \
          287  +        || (c&0xFFFFFFFE)==0xFFFE ){  c = 0xFFFD; }    \
          288  +  }                                                    \
          289  +}
          290  +
          291  +static int sqlite3ReadUtf8(const unsigned char *z){
          292  +  int c;
          293  +  READ_UTF8(z, c);
          294  +  return c;
          295  +}
          296  +
          297  +#define SKIP_UTF8(zIn) {                               \
          298  +  zIn += (xtra_utf8_bytes[*(u8 *)zIn] + 1);            \
          299  +}
          300  +
          301  +/*
          302  +** pZ is a UTF-8 encoded unicode string. If nByte is less than zero,
          303  +** return the number of unicode characters in pZ up to (but not including)
          304  +** the first 0x00 byte. If nByte is not less than zero, return the
          305  +** number of unicode characters in the first nByte of pZ (or up to 
          306  +** the first 0x00, whichever comes first).
          307  +*/
          308  +static int sqlite3Utf8CharLen(const char *z, int nByte){
          309  +  int r = 0;
          310  +  const char *zTerm;
          311  +  if( nByte>=0 ){
          312  +    zTerm = &z[nByte];
          313  +  }else{
          314  +    zTerm = (const char *)(-1);
          315  +  }
          316  +  assert( z<=zTerm );
          317  +  while( *z!=0 && z<zTerm ){
          318  +    SKIP_UTF8(z);
          319  +    r++;
          320  +  }
          321  +  return r;
          322  +}
          323  +
          324  +/*
          325  +** X is a pointer to the first byte of a UTF-8 character.  Increment
          326  +** X so that it points to the next character.  This only works right
          327  +** if X points to a well-formed UTF-8 string.
          328  +*/
          329  +#define sqliteNextChar(X)  while( (0xc0&*++(X))==0x80 ){}
          330  +#define sqliteCharVal(X)   sqlite3ReadUtf8(X)
          331  +
          332  +/*
          333  +** This is a macro that facilitates writting wrappers for math.h functions
          334  +** it creates code for a function to use in SQlite that gets one numeric input
          335  +** and returns a floating point value.
          336  +**
          337  +** Could have been implemented using pointers to functions but this way it's inline
          338  +** and thus more efficient. Lower * ranking though...
          339  +** 
          340  +** Parameters:
          341  +** name:      function name to de defined (eg: sinFunc)
          342  +** function:  function defined in math.h to wrap (eg: sin)
          343  +** domain:    boolean condition that CAN'T happen in terms of the input parameter rVal
          344  +**            (eg: rval<0 for sqrt)
          345  +*/
          346  +/* LMH 2007-03-25 Changed to use errno and remove domain; no pre-checking for errors. */
          347  +#define GEN_MATH_WRAP_DOUBLE_1(name, function) \
          348  +static void name(sqlite3_context *context, int argc, sqlite3_value **argv){\
          349  +  double rVal = 0.0, val;\
          350  +  assert( argc==1 );\
          351  +  switch( sqlite3_value_type(argv[0]) ){\
          352  +    case SQLITE_NULL: {\
          353  +      sqlite3_result_null(context);\
          354  +      break;\
          355  +    }\
          356  +    default: {\
          357  +      rVal = sqlite3_value_double(argv[0]);\
          358  +      errno = 0;\
          359  +      val = function(rVal);\
          360  +      if (errno == 0) {\
          361  +        sqlite3_result_double(context, val);\
          362  +      } else {\
          363  +        sqlite3_result_error(context, strerror(errno), errno);\
          364  +      }\
          365  +      break;\
          366  +    }\
          367  +  }\
          368  +}\
          369  +
          370  +
          371  +/*
          372  +** Example of GEN_MATH_WRAP_DOUBLE_1 usage
          373  +** this creates function sqrtFunc to wrap the math.h standard function sqrt(x)=x^0.5
          374  +*/
          375  +GEN_MATH_WRAP_DOUBLE_1(sqrtFunc, sqrt)
          376  +
          377  +/* trignometric functions */
          378  +GEN_MATH_WRAP_DOUBLE_1(acosFunc, acos)
          379  +GEN_MATH_WRAP_DOUBLE_1(asinFunc, asin)
          380  +GEN_MATH_WRAP_DOUBLE_1(atanFunc, atan)
          381  +
          382  +/*
          383  +** Many of systems don't have inverse hyperbolic trig functions so this will emulate
          384  +** them on those systems in terms of log and sqrt (formulas are too trivial to demand 
          385  +** written proof here)
          386  +*/
          387  +
          388  +#ifndef HAVE_ACOSH
          389  +static double acosh(double x){
          390  +  return log(x + sqrt(x*x - 1.0));
          391  +}
          392  +#endif
          393  +
          394  +GEN_MATH_WRAP_DOUBLE_1(acoshFunc, acosh)
          395  +
          396  +#ifndef HAVE_ASINH
          397  +static double asinh(double x){
          398  +  return log(x + sqrt(x*x + 1.0));
          399  +}
          400  +#endif
          401  +
          402  +GEN_MATH_WRAP_DOUBLE_1(asinhFunc, asinh)
          403  +
          404  +#ifndef HAVE_ATANH
          405  +static double atanh(double x){
          406  +  return (1.0/2.0)*log((1+x)/(1-x)) ;
          407  +}
          408  +#endif
          409  +
          410  +GEN_MATH_WRAP_DOUBLE_1(atanhFunc, atanh)
          411  +
          412  +/*
          413  +** math.h doesn't require cot (cotangent) so it's defined here
          414  +*/
          415  +static double cot(double x){
          416  +  return 1.0/tan(x);
          417  +}
          418  +
          419  +GEN_MATH_WRAP_DOUBLE_1(sinFunc, sin)
          420  +GEN_MATH_WRAP_DOUBLE_1(cosFunc, cos)
          421  +GEN_MATH_WRAP_DOUBLE_1(tanFunc, tan)
          422  +GEN_MATH_WRAP_DOUBLE_1(cotFunc, cot)
          423  +
          424  +static double coth(double x){
          425  +  return 1.0/tanh(x);
          426  +}
          427  +
          428  +/*
          429  +** Many systems don't have hyperbolic trigonometric functions so this will emulate
          430  +** them on those systems directly from the definition in terms of exp
          431  +*/
          432  +#ifndef HAVE_SINH
          433  +static double sinh(double x){
          434  +  return (exp(x)-exp(-x))/2.0;
          435  +}
          436  +#endif
          437  +
          438  +GEN_MATH_WRAP_DOUBLE_1(sinhFunc, sinh)
          439  +
          440  +#ifndef HAVE_COSH
          441  +static double cosh(double x){
          442  +  return (exp(x)+exp(-x))/2.0;
          443  +}
          444  +#endif
          445  +
          446  +GEN_MATH_WRAP_DOUBLE_1(coshFunc, cosh)
          447  +
          448  +#ifndef HAVE_TANH
          449  +static double tanh(double x){
          450  +  return sinh(x)/cosh(x);
          451  +}
          452  +#endif
          453  +
          454  +GEN_MATH_WRAP_DOUBLE_1(tanhFunc, tanh)
          455  +
          456  +GEN_MATH_WRAP_DOUBLE_1(cothFunc, coth)
          457  +
          458  +/*
          459  +** Some systems lack log in base 10. This will emulate it
          460  +*/
          461  +
          462  +#ifndef HAVE_LOG10
          463  +static double log10(double x){
          464  +  static double l10 = -1.0;
          465  +  if( l10<0.0 ){
          466  +    l10 = log(10.0);
          467  +  }
          468  +  return log(x)/l10;
          469  +}
          470  +#endif
          471  +
          472  +GEN_MATH_WRAP_DOUBLE_1(logFunc, log)
          473  +GEN_MATH_WRAP_DOUBLE_1(log10Func, log10)
          474  +GEN_MATH_WRAP_DOUBLE_1(expFunc, exp)
          475  +
          476  +/*
          477  +** Fallback for systems where math.h doesn't define M_PI
          478  +*/
          479  +#undef M_PI
          480  +#ifndef M_PI
          481  +/*
          482  +** static double PI = acos(-1.0);
          483  +** #define M_PI (PI)
          484  +*/
          485  +#define M_PI 3.14159265358979323846
          486  +#endif
          487  +
          488  +/* Convert Degrees into Radians */
          489  +static double deg2rad(double x){
          490  +  return x*M_PI/180.0;
          491  +}
          492  +
          493  +/* Convert Radians into Degrees */
          494  +static double rad2deg(double x){
          495  +  return 180.0*x/M_PI;
          496  +}
          497  +
          498  +GEN_MATH_WRAP_DOUBLE_1(rad2degFunc, rad2deg)
          499  +GEN_MATH_WRAP_DOUBLE_1(deg2radFunc, deg2rad)
          500  +
          501  +/* constant function that returns the value of PI=3.1415... */
          502  +static void piFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
          503  +  sqlite3_result_double(context, M_PI);
          504  +}
          505  +
          506  +/*
          507  +** Implements the sqrt function, it has the peculiarity of returning an integer when the
          508  +** the argument is an integer.
          509  +** Since SQLite isn't strongly typed (almost untyped actually) this is a bit pedantic
          510  +*/
          511  +static void squareFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
          512  +  i64 iVal = 0;
          513  +  double rVal = 0.0;
          514  +  assert( argc==1 );
          515  +  switch( sqlite3_value_type(argv[0]) ){
          516  +    case SQLITE_INTEGER: {
          517  +      iVal = sqlite3_value_int64(argv[0]);
          518  +      sqlite3_result_int64(context, iVal*iVal);
          519  +      break;
          520  +    }
          521  +    case SQLITE_NULL: {
          522  +      sqlite3_result_null(context);
          523  +      break;
          524  +    }
          525  +    default: {
          526  +      rVal = sqlite3_value_double(argv[0]);
          527  +      sqlite3_result_double(context, rVal*rVal);
          528  +      break;
          529  +    }
          530  +  }
          531  +}
          532  +
          533  +/*
          534  +** Wraps the pow math.h function
          535  +** When both the base and the exponent are integers the result should be integer
          536  +** (see sqrt just before this). Here the result is always double
          537  +*/
          538  +/* LMH 2007-03-25 Changed to use errno; no pre-checking for errors.  Also removes
          539  +  but that was present in the pre-checking that called sqlite3_result_error on 
          540  +  a non-positive first argument, which is not always an error. */
          541  +static void powerFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
          542  +  double r1 = 0.0;
          543  +  double r2 = 0.0;
          544  +  double val;
          545  +
          546  +  assert( argc==2 );
          547  +  
          548  +  if( sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL ){
          549  +    sqlite3_result_null(context); 
          550  +  }else{
          551  +    r1 = sqlite3_value_double(argv[0]);
          552  +    r2 = sqlite3_value_double(argv[1]);
          553  +    errno = 0;
          554  +    val = pow(r1,r2);
          555  +    if (errno == 0) {
          556  +      sqlite3_result_double(context, val);
          557  +    } else {  
          558  +      sqlite3_result_error(context, strerror(errno), errno);
          559  +    }  
          560  +  }
          561  +}
          562  +
          563  +/*
          564  +** atan2 wrapper
          565  +*/
          566  +static void atn2Func(sqlite3_context *context, int argc, sqlite3_value **argv){
          567  +  double r1 = 0.0;
          568  +  double r2 = 0.0;
          569  +
          570  +  assert( argc==2 );
          571  +  
          572  +  if( sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL ){
          573  +    sqlite3_result_null(context); 
          574  +  }else{
          575  +    r1 = sqlite3_value_double(argv[0]);
          576  +    r2 = sqlite3_value_double(argv[1]);
          577  +    sqlite3_result_double(context, atan2(r1,r2));
          578  +  }
          579  +}
          580  +
          581  +/*
          582  +** Implementation of the sign() function
          583  +** return one of 3 possibilities +1,0 or -1 when the argument is respectively
          584  +** positive, 0 or negative.
          585  +** When the argument is NULL the result is also NULL (completly conventional)
          586  +*/
          587  +static void signFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
          588  +  double rVal=0.0;
          589  +  i64 iVal=0;
          590  +  assert( argc==1 );
          591  +  switch( sqlite3_value_type(argv[0]) ){
          592  +    case SQLITE_INTEGER: {
          593  +      iVal = sqlite3_value_int64(argv[0]);
          594  +      iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
          595  +      sqlite3_result_int64(context, iVal);
          596  +      break;
          597  +    }
          598  +    case SQLITE_NULL: {
          599  +      sqlite3_result_null(context);
          600  +      break;
          601  +    }
          602  +    default: {
          603  + /* 2nd change below. Line for abs was: if( rVal<0 ) rVal = rVal * -1.0;  */
          604  +
          605  +      rVal = sqlite3_value_double(argv[0]);
          606  +      rVal = ( rVal > 0) ? 1: ( rVal < 0 ) ? -1: 0;
          607  +      sqlite3_result_double(context, rVal);
          608  +      break;
          609  +    }
          610  +  }
          611  +}
          612  +
          613  +
          614  +/*
          615  +** smallest integer value not less than argument
          616  +*/
          617  +static void ceilFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
          618  +  double rVal=0.0;
          619  +  i64 iVal=0;
          620  +  assert( argc==1 );
          621  +  switch( sqlite3_value_type(argv[0]) ){
          622  +    case SQLITE_INTEGER: {
          623  +      i64 iVal = sqlite3_value_int64(argv[0]);
          624  +      sqlite3_result_int64(context, iVal);
          625  +      break;
          626  +    }
          627  +    case SQLITE_NULL: {
          628  +      sqlite3_result_null(context);
          629  +      break;
          630  +    }
          631  +    default: {
          632  +      rVal = sqlite3_value_double(argv[0]);
          633  +      sqlite3_result_int64(context, (i64) ceil(rVal));
          634  +      break;
          635  +    }
          636  +  }
          637  +}
          638  +
          639  +/*
          640  +** largest integer value not greater than argument
          641  +*/
          642  +static void floorFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
          643  +  double rVal=0.0;
          644  +  i64 iVal=0;
          645  +  assert( argc==1 );
          646  +  switch( sqlite3_value_type(argv[0]) ){
          647  +    case SQLITE_INTEGER: {
          648  +      i64 iVal = sqlite3_value_int64(argv[0]);
          649  +      sqlite3_result_int64(context, iVal);
          650  +      break;
          651  +    }
          652  +    case SQLITE_NULL: {
          653  +      sqlite3_result_null(context);
          654  +      break;
          655  +    }
          656  +    default: {
          657  +      rVal = sqlite3_value_double(argv[0]);
          658  +      sqlite3_result_int64(context, (i64) floor(rVal));
          659  +      break;
          660  +    }
          661  +  }
          662  +}
          663  +
          664  +/*
          665  +** Given a string (s) in the first argument and an integer (n) in the second returns the 
          666  +** string that constains s contatenated n times
          667  +*/
          668  +static void replicateFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
          669  +  unsigned char *z;        /* input string */
          670  +  unsigned char *zo;       /* result string */
          671  +  i64 iCount;              /* times to repeat */
          672  +  i64 nLen;                /* length of the input string (no multibyte considerations) */ 
          673  +  i64 nTLen;               /* length of the result string (no multibyte considerations) */
          674  +  i64 i=0;
          675  +
          676  +  if( argc!=2 || SQLITE_NULL==sqlite3_value_type(argv[0]) )
          677  +    return;
          678  +
          679  +  iCount = sqlite3_value_int64(argv[1]);
          680  +
          681  +  if( iCount<0 ){
          682  +    sqlite3_result_error(context, "domain error", -1);
          683  +  }else{
          684  +
          685  +    nLen  = sqlite3_value_bytes(argv[0]);
          686  +    nTLen = nLen*iCount;
          687  +    z=sqlite3_malloc(nTLen+1);
          688  +    zo=sqlite3_malloc(nLen+1);
          689  +    if (!z || !zo){
          690  +      sqlite3_result_error_nomem(context);
          691  +      if (z) sqlite3_free(z);
          692  +      if (zo) sqlite3_free(zo);
          693  +      return;
          694  +    }
          695  +    strcpy((char*)zo, (char*)sqlite3_value_text(argv[0]));
          696  +
          697  +    for(i=0; i<iCount; ++i){
          698  +      strcpy((char*)(z+i*nLen), (char*)zo);
          699  +    }
          700  +
          701  +    sqlite3_result_text(context, (char*)z, -1, SQLITE_TRANSIENT);
          702  +    sqlite3_free(z);
          703  +    sqlite3_free(zo);
          704  +  }
          705  +}
          706  +
          707  +/* 
          708  +** Some systems (win32 among others) don't have an isblank function, this will emulate it.
          709  +** This function is not UFT-8 safe since it only analyses a byte character.
          710  +*/
          711  +#ifndef HAVE_ISBLANK
          712  +int isblank(char c){
          713  +  return( ' '==c || '\t'==c );
          714  +}
          715  +#endif
          716  +
          717  +static void properFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
          718  +  const unsigned char *z;     /* input string */
          719  +  unsigned char *zo;          /* output string */
          720  +  unsigned char *zt;          /* iterator */
          721  +  char r;
          722  +  int c=1;
          723  +
          724  +  assert( argc==1);
          725  +  if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
          726  +    sqlite3_result_null(context);
          727  +    return;
          728  +  }
          729  +
          730  +  z = sqlite3_value_text(argv[0]);
          731  +  zo = (unsigned char *)sqlite3StrDup((char *) z);
          732  +  if (!zo) {
          733  +    sqlite3_result_error_nomem(context);
          734  +    return;
          735  +  }
          736  +  zt = zo;
          737  +
          738  +  while( (r = *(z++))!=0 ){
          739  +    if( isblank(r) ){
          740  +      c=1;
          741  +    }else{
          742  +      if( c==1 ){
          743  +        r = toupper(r);
          744  +      }else{
          745  +        r = tolower(r);
          746  +      }
          747  +      c=0;
          748  +    }
          749  +    *(zt++) = r;
          750  +  }
          751  +  *zt = '\0';
          752  +
          753  +  sqlite3_result_text(context, (char*)zo, -1, SQLITE_TRANSIENT);
          754  +  sqlite3_free(zo);
          755  +}
          756  +
          757  +/*
          758  +** given an input string (s) and an integer (n) adds spaces at the begining of  s
          759  +** until it has a length of n characters.
          760  +** When s has a length >=n it's a NOP
          761  +** padl(NULL) = NULL
          762  +*/
          763  +static void padlFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
          764  +  i64 ilen;          /* length to pad to */
          765  +  i64 zl;            /* length of the input string (UTF-8 chars) */
          766  +  int i = 0;
          767  +  const char *zi;    /* input string */
          768  +  char *zo;          /* output string */
          769  +  char *zt;
          770  +
          771  +  assert( argc==2 );
          772  +  
          773  +  if( sqlite3_value_type(argv[0]) == SQLITE_NULL ){
          774  +    sqlite3_result_null(context); 
          775  +  }else{
          776  +    zi = (char *)sqlite3_value_text(argv[0]);
          777  +    ilen = sqlite3_value_int64(argv[1]);
          778  +    /* check domain */
          779  +    if(ilen<0){
          780  +      sqlite3_result_error(context, "domain error", -1);
          781  +      return;
          782  +    }
          783  +    zl = sqlite3Utf8CharLen(zi, -1);
          784  +    if( zl>=ilen ){
          785  +      /* string is longer than the requested pad length, return the same string (dup it) */
          786  +      zo = sqlite3StrDup(zi);
          787  +      if (!zo){
          788  +        sqlite3_result_error_nomem(context);
          789  +        return;
          790  +      }
          791  +      sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
          792  +    }else{
          793  +      zo = sqlite3_malloc(strlen(zi)+ilen-zl+1);
          794  +      if (!zo){
          795  +        sqlite3_result_error_nomem(context);
          796  +        return;
          797  +      }
          798  +      zt = zo;
          799  +      for(i=1; i+zl<=ilen; ++i){
          800  +        *(zt++)=' ';
          801  +      }
          802  +      /* no need to take UTF-8 into consideration here */
          803  +      strcpy(zt,zi);
          804  +    }
          805  +    sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
          806  +    sqlite3_free(zo);
          807  +  }
          808  +}
          809  +
          810  +/*
          811  +** given an input string (s) and an integer (n) appends spaces at the end of  s
          812  +** until it has a length of n characters.
          813  +** When s has a length >=n it's a NOP
          814  +** padl(NULL) = NULL
          815  +*/
          816  +static void padrFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
          817  +  i64 ilen;          /* length to pad to */
          818  +  i64 zl;            /* length of the input string (UTF-8 chars) */
          819  +  i64 zll;           /* length of the input string (bytes) */
          820  +  int i = 0;
          821  +  const char *zi;    /* input string */
          822  +  char *zo;          /* output string */
          823  +  char *zt;
          824  +
          825  +  assert( argc==2 );
          826  +  
          827  +  if( sqlite3_value_type(argv[0]) == SQLITE_NULL ){
          828  +    sqlite3_result_null(context); 
          829  +  }else{
          830  +    zi = (char *)sqlite3_value_text(argv[0]);
          831  +    ilen = sqlite3_value_int64(argv[1]);
          832  +    /* check domain */
          833  +    if(ilen<0){
          834  +      sqlite3_result_error(context, "domain error", -1);
          835  +      return;
          836  +    }
          837  +    zl = sqlite3Utf8CharLen(zi, -1);
          838  +    if( zl>=ilen ){
          839  +      /* string is longer than the requested pad length, return the same string (dup it) */
          840  +      zo = sqlite3StrDup(zi);
          841  +      if (!zo){
          842  +        sqlite3_result_error_nomem(context);
          843  +        return;
          844  +      }
          845  +      sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
          846  +    }else{
          847  +      zll = strlen(zi);
          848  +      zo = sqlite3_malloc(zll+ilen-zl+1);
          849  +      if (!zo){
          850  +        sqlite3_result_error_nomem(context);
          851  +        return;
          852  +      }
          853  +      zt = strcpy(zo,zi)+zll;
          854  +      for(i=1; i+zl<=ilen; ++i){
          855  +        *(zt++) = ' ';
          856  +      }
          857  +      *zt = '\0';
          858  +    }
          859  +    sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
          860  +    sqlite3_free(zo);
          861  +  }
          862  +}
          863  +
          864  +/*
          865  +** given an input string (s) and an integer (n) appends spaces at the end of  s
          866  +** and adds spaces at the begining of s until it has a length of n characters.
          867  +** Tries to add has many characters at the left as at the right.
          868  +** When s has a length >=n it's a NOP
          869  +** padl(NULL) = NULL
          870  +*/
          871  +static void padcFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
          872  +  i64 ilen;           /* length to pad to */
          873  +  i64 zl;             /* length of the input string (UTF-8 chars) */
          874  +  i64 zll;            /* length of the input string (bytes) */
          875  +  int i = 0;
          876  +  const char *zi;     /* input string */
          877  +  char *zo;           /* output string */
          878  +  char *zt;
          879  +
          880  +  assert( argc==2 );
          881  +  
          882  +  if( sqlite3_value_type(argv[0]) == SQLITE_NULL ){
          883  +    sqlite3_result_null(context); 
          884  +  }else{
          885  +    zi = (char *)sqlite3_value_text(argv[0]);
          886  +    ilen = sqlite3_value_int64(argv[1]);
          887  +    /* check domain */
          888  +    if(ilen<0){
          889  +      sqlite3_result_error(context, "domain error", -1);
          890  +      return;
          891  +    }
          892  +    zl = sqlite3Utf8CharLen(zi, -1);
          893  +    if( zl>=ilen ){
          894  +      /* string is longer than the requested pad length, return the same string (dup it) */
          895  +      zo = sqlite3StrDup(zi);
          896  +      if (!zo){
          897  +        sqlite3_result_error_nomem(context);
          898  +        return;
          899  +      }
          900  +      sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
          901  +    }else{
          902  +      zll = strlen(zi);
          903  +      zo = sqlite3_malloc(zll+ilen-zl+1);
          904  +      if (!zo){
          905  +        sqlite3_result_error_nomem(context);
          906  +        return;
          907  +      }
          908  +      zt = zo;
          909  +      for(i=1; 2*i+zl<=ilen; ++i){
          910  +        *(zt++) = ' ';
          911  +      }
          912  +      strcpy(zt, zi);
          913  +      zt+=zll;
          914  +      for(; i+zl<=ilen; ++i){
          915  +        *(zt++) = ' ';
          916  +      }
          917  +      *zt = '\0';
          918  +    }
          919  +    sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
          920  +    sqlite3_free(zo);
          921  +  }
          922  +}
          923  +
          924  +/*
          925  +** given 2 string (s1,s2) returns the string s1 with the characters NOT in s2 removed
          926  +** assumes strings are UTF-8 encoded
          927  +*/
          928  +static void strfilterFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
          929  +  const char *zi1;        /* first parameter string (searched string) */
          930  +  const char *zi2;        /* second parameter string (vcontains valid characters) */
          931  +  const char *z1;
          932  +  const char *z21;
          933  +  const char *z22;
          934  +  char *zo;               /* output string */
          935  +  char *zot;
          936  +  int c1 = 0;
          937  +  int c2 = 0;
          938  +
          939  +  assert( argc==2 );
          940  +  
          941  +  if( sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL ){
          942  +    sqlite3_result_null(context); 
          943  +  }else{
          944  +    zi1 = (char *)sqlite3_value_text(argv[0]);
          945  +    zi2 = (char *)sqlite3_value_text(argv[1]);
          946  +    /* 
          947  +    ** maybe I could allocate less, but that would imply 2 passes, rather waste 
          948  +    ** (possibly) some memory
          949  +    */
          950  +    zo = sqlite3_malloc(strlen(zi1)+1); 
          951  +    if (!zo){
          952  +      sqlite3_result_error_nomem(context);
          953  +      return;
          954  +    }
          955  +    zot = zo;
          956  +    z1 = zi1;
          957  +    while( (c1=sqliteCharVal((unsigned char *)z1))!=0 ){
          958  +      z21=zi2;
          959  +      while( (c2=sqliteCharVal((unsigned char *)z21))!=0 && c2!=c1 ){
          960  +        sqliteNextChar(z21);
          961  +      }
          962  +      if( c2!=0){
          963  +        z22=z21;
          964  +        sqliteNextChar(z22);
          965  +        strncpy(zot, z21, z22-z21);
          966  +        zot+=z22-z21;
          967  +      }
          968  +      sqliteNextChar(z1);
          969  +    }
          970  +    *zot = '\0';
          971  +
          972  +    sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
          973  +    sqlite3_free(zo);
          974  +  }
          975  +}
          976  +
          977  +/*
          978  +** Given a string z1, retutns the (0 based) index of it's first occurence
          979  +** in z2 after the first s characters.
          980  +** Returns -1 when there isn't a match.
          981  +** updates p to point to the character where the match occured.
          982  +** This is an auxiliary function.
          983  +*/
          984  +static int _substr(const char* z1, const char* z2, int s, const char** p){
          985  +  int c = 0;
          986  +  int rVal=-1;
          987  +  const char* zt1;
          988  +  const char* zt2;
          989  +  int c1,c2;
          990  +
          991  +  if( '\0'==*z1 ){
          992  +    return -1;
          993  +  }
          994  +  
          995  +  while( (sqliteCharVal((unsigned char *)z2) != 0) && (c++)<s){
          996  +    sqliteNextChar(z2);
          997  +  }
          998  +  
          999  +  c = 0;
         1000  +  while( (sqliteCharVal((unsigned char *)z2)) != 0 ){
         1001  +    zt1 = z1;
         1002  +    zt2 = z2;
         1003  +
         1004  +    do{
         1005  +      c1 = sqliteCharVal((unsigned char *)zt1);
         1006  +      c2 = sqliteCharVal((unsigned char *)zt2);
         1007  +      sqliteNextChar(zt1);
         1008  +      sqliteNextChar(zt2);
         1009  +    }while( c1 == c2 && c1 != 0 && c2 != 0 );
         1010  +
         1011  +    if( c1 == 0 ){
         1012  +      rVal = c;
         1013  +      break; 
         1014  +    }
         1015  +    
         1016  +    sqliteNextChar(z2);
         1017  +    ++c;
         1018  +  }
         1019  +  if(p){
         1020  +    *p=z2;
         1021  +  }
         1022  +  return rVal >=0 ? rVal+s : rVal;
         1023  +}
         1024  +
         1025  +/*
         1026  +** given 2 input strings (s1,s2) and an integer (n) searches from the nth character
         1027  +** for the string s1. Returns the position where the match occured.
         1028  +** Characters are counted from 1.
         1029  +** 0 is returned when no match occurs.
         1030  +*/
         1031  +
         1032  +static void charindexFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
         1033  +  const u8 *z1;          /* s1 string */
         1034  +  u8 *z2;                /* s2 string */
         1035  +  int s=0;
         1036  +  int rVal=0;
         1037  +
         1038  +  assert( argc==3 ||argc==2);
         1039  +
         1040  +  if( SQLITE_NULL==sqlite3_value_type(argv[0]) || SQLITE_NULL==sqlite3_value_type(argv[1])){
         1041  +    sqlite3_result_null(context);
         1042  +    return;
         1043  +  }
         1044  +
         1045  +  z1 = sqlite3_value_text(argv[0]);
         1046  +  if( z1==0 ) return;
         1047  +  z2 = (u8*) sqlite3_value_text(argv[1]);
         1048  +  if(argc==3){
         1049  +    s = sqlite3_value_int(argv[2])-1;
         1050  +    if(s<0){
         1051  +      s=0;
         1052  +    }
         1053  +  }else{
         1054  +    s = 0;
         1055  +  }
         1056  +
         1057  +  rVal = _substr((char *)z1,(char *)z2,s,NULL);
         1058  +  sqlite3_result_int(context, rVal+1);
         1059  +}
         1060  +
         1061  +/*
         1062  +** given a string (s) and an integer (n) returns the n leftmost (UTF-8) characters
         1063  +** if the string has a length<=n or is NULL this function is NOP
         1064  +*/
         1065  +static void leftFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
         1066  +  int c=0;
         1067  +  int cc=0;
         1068  +  int l=0;
         1069  +  const unsigned char *z;       /* input string */
         1070  +  const unsigned char *zt;
         1071  +  unsigned char *rz;            /* output string */
         1072  +
         1073  +  assert( argc==2);
         1074  +
         1075  +  if( SQLITE_NULL==sqlite3_value_type(argv[0]) || SQLITE_NULL==sqlite3_value_type(argv[1])){
         1076  +    sqlite3_result_null(context);
         1077  +    return;
         1078  +  }
         1079  +
         1080  +  z  = sqlite3_value_text(argv[0]);
         1081  +  l  = sqlite3_value_int(argv[1]);
         1082  +  zt = z;
         1083  +
         1084  +  while( sqliteCharVal(zt) && c++<l)
         1085  +    sqliteNextChar(zt);
         1086  +
         1087  +  cc=zt-z;
         1088  +
         1089  +  rz = sqlite3_malloc(zt-z+1);
         1090  +  if (!rz){
         1091  +    sqlite3_result_error_nomem(context);
         1092  +    return;
         1093  +  }
         1094  +  strncpy((char*) rz, (char*) z, zt-z);
         1095  +  *(rz+cc) = '\0';
         1096  +  sqlite3_result_text(context, (char*)rz, -1, SQLITE_TRANSIENT); 
         1097  +  sqlite3_free(rz);
         1098  +}
         1099  +
         1100  +/*
         1101  +** given a string (s) and an integer (n) returns the n rightmost (UTF-8) characters
         1102  +** if the string has a length<=n or is NULL this function is NOP
         1103  +*/
         1104  +static void rightFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
         1105  +  int l=0;
         1106  +  int c=0;
         1107  +  int cc=0;
         1108  +  const char *z;
         1109  +  const char *zt;
         1110  +  const char *ze;
         1111  +  char *rz;
         1112  +
         1113  +  assert( argc==2);
         1114  +
         1115  +  if( SQLITE_NULL == sqlite3_value_type(argv[0]) || SQLITE_NULL == sqlite3_value_type(argv[1])){
         1116  +    sqlite3_result_null(context);
         1117  +    return;
         1118  +  }
         1119  +
         1120  +  z  = (char *)sqlite3_value_text(argv[0]);
         1121  +  l  = sqlite3_value_int(argv[1]);
         1122  +  zt = z;
         1123  +
         1124  +  while( sqliteCharVal((unsigned char *)zt)!=0){
         1125  +    sqliteNextChar(zt);
         1126  +    ++c;
         1127  +  }
         1128  +
         1129  +  ze = zt;
         1130  +  zt = z;
         1131  +
         1132  +  cc=c-l;
         1133  +  if(cc<0)
         1134  +    cc=0;
         1135  +  
         1136  +  while( cc-- > 0 ){
         1137  +    sqliteNextChar(zt);
         1138  +  }
         1139  +
         1140  +  rz = sqlite3_malloc(ze-zt+1);
         1141  +  if (!rz){
         1142  +    sqlite3_result_error_nomem(context);
         1143  +    return;
         1144  +  }
         1145  +  strcpy((char*) rz, (char*) (zt));
         1146  +  sqlite3_result_text(context, (char*)rz, -1, SQLITE_TRANSIENT); 
         1147  +  sqlite3_free(rz);
         1148  +}
         1149  +
         1150  +#ifndef HAVE_TRIM
         1151  +/*
         1152  +** removes the whitespaces at the begining of a string.
         1153  +*/
         1154  +const char* ltrim(const char* s){
         1155  +  while( *s==' ' )
         1156  +    ++s;
         1157  +  return s;
         1158  +}
         1159  +
         1160  +/*
         1161  +** removes the whitespaces at the end of a string.
         1162  +** !mutates the input string!
         1163  +*/
         1164  +void rtrim(char* s){
         1165  +  char* ss = s+strlen(s)-1;
         1166  +  while( ss>=s && *ss==' ' )
         1167  +    --ss;
         1168  +  *(ss+1)='\0';
         1169  +}
         1170  +
         1171  +/*
         1172  +**  Removes the whitespace at the begining of a string
         1173  +*/
         1174  +static void ltrimFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
         1175  +  const char *z;
         1176  +
         1177  +  assert( argc==1);
         1178  +
         1179  +  if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
         1180  +    sqlite3_result_null(context);
         1181  +    return;
         1182  +  }
         1183  +  z = sqlite3_value_text(argv[0]);
         1184  +  sqlite3_result_text(context, ltrim(z), -1, SQLITE_TRANSIENT); 
         1185  +}
         1186  +
         1187  +/*
         1188  +**  Removes the whitespace at the end of a string
         1189  +*/
         1190  +static void rtrimFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
         1191  +  const char *z;
         1192  +  char *rz;
         1193  +  /* try not to change data in argv */
         1194  +
         1195  +  assert( argc==1);
         1196  +
         1197  +  if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
         1198  +    sqlite3_result_null(context);
         1199  +    return;
         1200  +  }
         1201  +  z = sqlite3_value_text(argv[0]);
         1202  +  rz = sqlite3StrDup(z);
         1203  +  rtrim(rz);
         1204  +  sqlite3_result_text(context, rz, -1, SQLITE_TRANSIENT);
         1205  +  sqlite3_free(rz);
         1206  +}
         1207  +
         1208  +/*
         1209  +**  Removes the whitespace at the begining and end of a string
         1210  +*/
         1211  +static void trimFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
         1212  +  const char *z;
         1213  +  char *rz;
         1214  +  /* try not to change data in argv */
         1215  +
         1216  +  assert( argc==1);
         1217  +
         1218  +  if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
         1219  +    sqlite3_result_null(context);
         1220  +    return;
         1221  +  }
         1222  +  z = sqlite3_value_text(argv[0]);
         1223  +  rz = sqlite3StrDup(z);
         1224  +  rtrim(rz);
         1225  +  sqlite3_result_text(context, ltrim(rz), -1, SQLITE_TRANSIENT);
         1226  +  sqlite3_free(rz);
         1227  +}
         1228  +#endif
         1229  +
         1230  +/*
         1231  +** given a pointer to a string s1, the length of that string (l1), a new string (s2)
         1232  +** and it's length (l2) appends s2 to s1.
         1233  +** All lengths in bytes.
         1234  +** This is just an auxiliary function
         1235  +*/
         1236  +// static void _append(char **s1, int l1, const char *s2, int l2){
         1237  +//   *s1 = realloc(*s1, (l1+l2+1)*sizeof(char));
         1238  +//   strncpy((*s1)+l1, s2, l2);
         1239  +//   *(*(s1)+l1+l2) = '\0';
         1240  +// }
         1241  +
         1242  +#ifndef HAVE_TRIM
         1243  +
         1244  +/*
         1245  +** given strings s, s1 and s2 replaces occurrences of s1 in s by s2
         1246  +*/
         1247  +static void replaceFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
         1248  +  const char *z1;     /* string s (first parameter) */
         1249  +  const char *z2;     /* string s1 (second parameter) string to look for */
         1250  +  const char *z3;     /* string s2 (third parameter) string to replace occurrences of s1 with */
         1251  +  int lz1;
         1252  +  int lz2;
         1253  +  int lz3;
         1254  +  int lzo=0;
         1255  +  char *zo=0;
         1256  +  int ret=0;
         1257  +  const char *zt1;
         1258  +  const char *zt2;
         1259  +
         1260  +  assert( 3==argc );
         1261  +
         1262  +  if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
         1263  +    sqlite3_result_null(context);
         1264  +    return;
         1265  +  }
         1266  +
         1267  +  z1 = sqlite3_value_text(argv[0]);
         1268  +  z2 = sqlite3_value_text(argv[1]);
         1269  +  z3 = sqlite3_value_text(argv[2]);
         1270  +  /* handle possible null values */
         1271  +  if( 0==z2 ){
         1272  +    z2="";
         1273  +  }
         1274  +  if( 0==z3 ){
         1275  +    z3="";
         1276  +  }
         1277  +
         1278  +  lz1 = strlen(z1);
         1279  +  lz2 = strlen(z2);
         1280  +  lz3 = strlen(z3);
         1281  +
         1282  +#if 0
         1283  +  /* special case when z2 is empty (or null) nothing will be changed */
         1284  +  if( 0==lz2 ){
         1285  +    sqlite3_result_text(context, z1, -1, SQLITE_TRANSIENT);
         1286  +    return;
         1287  +  }
         1288  +#endif
         1289  +
         1290  +  zt1=z1;
         1291  +  zt2=z1;
         1292  +
         1293  +  while(1){
         1294  +    ret=_substr(z2,zt1 , 0, &zt2);
         1295  +
         1296  +    if( ret<0 )
         1297  +      break;
         1298  +
         1299  +    _append(&zo, lzo, zt1, zt2-zt1);
         1300  +    lzo+=zt2-zt1;
         1301  +    _append(&zo, lzo, z3, lz3);
         1302  +    lzo+=lz3;
         1303  +
         1304  +    zt1=zt2+lz2;
         1305  +  }
         1306  +  _append(&zo, lzo, zt1, lz1-(zt1-z1));
         1307  +  sqlite3_result_text(context, zo, -1, SQLITE_TRANSIENT);
         1308  +  sqlite3_free(zo);
         1309  +}
         1310  +#endif
         1311  +
         1312  +/*
         1313  +** given a string returns the same string but with the characters in reverse order
         1314  +*/
         1315  +static void reverseFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
         1316  +  const char *z;
         1317  +  const char *zt;
         1318  +  char *rz;
         1319  +  char *rzt;
         1320  +  int l = 0;
         1321  +  int i = 0;
         1322  +
         1323  +  assert( 1==argc );
         1324  +
         1325  +  if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
         1326  +    sqlite3_result_null(context);
         1327  +    return;
         1328  +  }
         1329  +  z = (char *)sqlite3_value_text(argv[0]);
         1330  +  l = strlen(z);
         1331  +  rz = sqlite3_malloc(l+1);
         1332  +  if (!rz){
         1333  +    sqlite3_result_error_nomem(context);
         1334  +    return;
         1335  +  }
         1336  +  rzt = rz+l;
         1337  +  *(rzt--) = '\0';
         1338  +
         1339  +  zt=z;
         1340  +  while( sqliteCharVal((unsigned char *)zt)!=0 ){
         1341  +    z=zt;
         1342  +    sqliteNextChar(zt);
         1343  +    for(i=1; zt-i>=z; ++i){
         1344  +      *(rzt--)=*(zt-i);
         1345  +    }
         1346  +  }
         1347  +
         1348  +  sqlite3_result_text(context, rz, -1, SQLITE_TRANSIENT);
         1349  +  sqlite3_free(rz);
         1350  +}
         1351  +
         1352  +/*
         1353  +** An instance of the following structure holds the context of a
         1354  +** stdev() or variance() aggregate computation.
         1355  +** implementaion of http://en.wikipedia.org/wiki/Algorithms_for_calculating_variance#Algorithm_II
         1356  +** less prone to rounding errors
         1357  +*/
         1358  +typedef struct StdevCtx StdevCtx;
         1359  +struct StdevCtx {
         1360  +  double rM;
         1361  +  double rS;
         1362  +  i64 cnt;          /* number of elements */
         1363  +};
         1364  +
         1365  +/*
         1366  +** An instance of the following structure holds the context of a
         1367  +** mode() or median() aggregate computation.
         1368  +** Depends on structures defined in map.c (see map & map)
         1369  +** These aggregate functions only work for integers and floats although
         1370  +** they could be made to work for strings. This is usually considered meaningless.
         1371  +** Only usuall order (for median), no use of collation functions (would this even make sense?)
         1372  +*/
         1373  +typedef struct ModeCtx ModeCtx;
         1374  +struct ModeCtx {
         1375  +  i64 riM;            /* integer value found so far */
         1376  +  double rdM;         /* double value found so far */
         1377  +  i64 cnt;            /* number of elements so far */
         1378  +  double pcnt;        /* number of elements smaller than a percentile */
         1379  +  i64 mcnt;           /* maximum number of occurrences (for mode) */
         1380  +  i64 mn;             /* number of occurrences (for mode and percentiles) */
         1381  +  i64 is_double;      /* whether the computation is being done for doubles (>0) or integers (=0) */
         1382  +  map* m;             /* map structure used for the computation */
         1383  +  int done;           /* whether the answer has been found */
         1384  +};
         1385  +
         1386  +/*
         1387  +** called for each value received during a calculation of stdev or variance
         1388  +*/
         1389  +static void varianceStep(sqlite3_context *context, int argc, sqlite3_value **argv){
         1390  +  StdevCtx *p;
         1391  +
         1392  +  double delta;
         1393  +  double x;
         1394  +
         1395  +  assert( argc==1 );
         1396  +  p = sqlite3_aggregate_context(context, sizeof(*p));
         1397  +  /* only consider non-null values */
         1398  +  if( SQLITE_NULL != sqlite3_value_numeric_type(argv[0]) ){
         1399  +    p->cnt++;
         1400  +    x = sqlite3_value_double(argv[0]);
         1401  +    delta = (x-p->rM);
         1402  +    p->rM += delta/p->cnt;
         1403  +    p->rS += delta*(x-p->rM);
         1404  +  }
         1405  +}
         1406  +
         1407  +/*
         1408  +** called for each value received during a calculation of mode of median
         1409  +*/
         1410  +static void modeStep(sqlite3_context *context, int argc, sqlite3_value **argv){
         1411  +  ModeCtx *p;
         1412  +  i64 xi=0;
         1413  +  double xd=0.0;
         1414  +  i64 *iptr;
         1415  +  double *dptr;
         1416  +  int type;
         1417  +
         1418  +  assert( argc==1 );
         1419  +  type = sqlite3_value_numeric_type(argv[0]);
         1420  +
         1421  +  if( type == SQLITE_NULL)
         1422  +    return;
         1423  +  
         1424  +  p = sqlite3_aggregate_context(context, sizeof(*p));
         1425  +
         1426  +  if( 0==(p->m) ){
         1427  +    p->m = calloc(1, sizeof(map));
         1428  +    if( type==SQLITE_INTEGER ){
         1429  +      /* map will be used for integers */
         1430  +      *(p->m) = map_make(int_cmp);
         1431  +      p->is_double = 0;
         1432  +    }else{
         1433  +      p->is_double = 1;
         1434  +      /* map will be used for doubles */
         1435  +      *(p->m) = map_make(double_cmp);
         1436  +    }
         1437  +  }
         1438  +
         1439  +  ++(p->cnt);
         1440  +
         1441  +  if( 0==p->is_double ){
         1442  +    xi = sqlite3_value_int64(argv[0]);
         1443  +    iptr = (i64*)calloc(1,sizeof(i64));
         1444  +    *iptr = xi;
         1445  +    map_insert(p->m, iptr);
         1446  +  }else{
         1447  +    xd = sqlite3_value_double(argv[0]);
         1448  +    dptr = (double*)calloc(1,sizeof(double));
         1449  +    *dptr = xd;
         1450  +    map_insert(p->m, dptr);
         1451  +  }
         1452  +}
         1453  +
         1454  +/*
         1455  +**  Auxiliary function that iterates all elements in a map and finds the mode
         1456  +**  (most frequent value)
         1457  +*/
         1458  +static void modeIterate(void* e, i64 c, void* pp){
         1459  +  i64 ei;
         1460  +  double ed;
         1461  +  ModeCtx *p = (ModeCtx*)pp;
         1462  +  
         1463  +  if( 0==p->is_double ){
         1464  +    ei = *(int*)(e);
         1465  +
         1466  +	if( p->mcnt==c ){
         1467  +      ++p->mn;
         1468  +    }else if( p->mcnt<c ){
         1469  +      p->riM = ei;
         1470  +      p->mcnt = c;
         1471  +	  p->mn=1;
         1472  +    }
         1473  +  }else{
         1474  +    ed = *(double*)(e);
         1475  +
         1476  +	if( p->mcnt==c ){
         1477  +      ++p->mn;
         1478  +    }else if(p->mcnt<c){
         1479  +      p->rdM = ed;
         1480  +      p->mcnt = c;
         1481  +	  p->mn=1;
         1482  +    }
         1483  +  }
         1484  +}
         1485  +
         1486  +/*
         1487  +**  Auxiliary function that iterates all elements in a map and finds the median
         1488  +**  (the value such that the number of elements smaller is equal the the number of 
         1489  +**  elements larger)
         1490  +*/
         1491  +static void medianIterate(void* e, i64 c, void* pp){
         1492  +  i64 ei;
         1493  +  double ed;
         1494  +  double iL;
         1495  +  double iR;
         1496  +  int il;
         1497  +  int ir;
         1498  +  ModeCtx *p = (ModeCtx*)pp;
         1499  +
         1500  +  if(p->done>0)
         1501  +    return;
         1502  +
         1503  +  iL = p->pcnt;
         1504  +  iR = p->cnt - p->pcnt;
         1505  +  il = p->mcnt + c;
         1506  +  ir = p->cnt - p->mcnt;
         1507  +
         1508  +  if( il >= iL ){
         1509  +    if( ir >= iR ){
         1510  +    ++p->mn;
         1511  +      if( 0==p->is_double ){
         1512  +        ei = *(int*)(e);
         1513  +        p->riM += ei;
         1514  +      }else{
         1515  +        ed = *(double*)(e);
         1516  +        p->rdM += ed;
         1517  +      }
         1518  +    }else{
         1519  +      p->done=1;
         1520  +    }
         1521  +  }
         1522  +  p->mcnt+=c;
         1523  +}
         1524  +
         1525  +/*
         1526  +** Returns the mode value
         1527  +*/
         1528  +static void modeFinalize(sqlite3_context *context){
         1529  +  ModeCtx *p;
         1530  +  p = sqlite3_aggregate_context(context, 0);
         1531  +  if( p && p->m ){
         1532  +    map_iterate(p->m, modeIterate, p);
         1533  +    map_destroy(p->m);
         1534  +    free(p->m);
         1535  +
         1536  +    if( 1==p->mn ){
         1537  +      if( 0==p->is_double )
         1538  +        sqlite3_result_int64(context, p->riM);
         1539  +      else
         1540  +        sqlite3_result_double(context, p->rdM);
         1541  +    }
         1542  +  }
         1543  +}
         1544  +
         1545  +/*
         1546  +** auxiliary function for percentiles
         1547  +*/
         1548  +static void _medianFinalize(sqlite3_context *context){
         1549  +  ModeCtx *p;
         1550  +  p = (ModeCtx*) sqlite3_aggregate_context(context, 0);
         1551  +  if( p && p->m ){
         1552  +    p->done=0;
         1553  +    map_iterate(p->m, medianIterate, p);
         1554  +    map_destroy(p->m);
         1555  +    free(p->m);
         1556  +
         1557  +    if( 0==p->is_double )
         1558  +      if( 1==p->mn )
         1559  +      	sqlite3_result_int64(context, p->riM);
         1560  +      else
         1561  +      	sqlite3_result_double(context, p->riM*1.0/p->mn);
         1562  +    else
         1563  +      sqlite3_result_double(context, p->rdM/p->mn);
         1564  +  }
         1565  +}
         1566  +
         1567  +/*
         1568  +** Returns the median value
         1569  +*/
         1570  +static void medianFinalize(sqlite3_context *context){
         1571  +  ModeCtx *p;
         1572  +  p = (ModeCtx*) sqlite3_aggregate_context(context, 0);
         1573  +  if( p!=0 ){
         1574  +    p->pcnt = (p->cnt)/2.0;
         1575  +    _medianFinalize(context);
         1576  +  }
         1577  +}
         1578  +
         1579  +/*
         1580  +** Returns the lower_quartile value
         1581  +*/
         1582  +static void lower_quartileFinalize(sqlite3_context *context){
         1583  +  ModeCtx *p;
         1584  +  p = (ModeCtx*) sqlite3_aggregate_context(context, 0);
         1585  +  if( p!=0 ){
         1586  +    p->pcnt = (p->cnt)/4.0;
         1587  +    _medianFinalize(context);
         1588  +  }
         1589  +}
         1590  +
         1591  +/*
         1592  +** Returns the upper_quartile value
         1593  +*/
         1594  +static void upper_quartileFinalize(sqlite3_context *context){
         1595  +  ModeCtx *p;
         1596  +  p = (ModeCtx*) sqlite3_aggregate_context(context, 0);
         1597  +  if( p!=0 ){
         1598  +    p->pcnt = (p->cnt)*3/4.0;
         1599  +    _medianFinalize(context);
         1600  +  }
         1601  +}
         1602  +
         1603  +/*
         1604  +** Returns the stdev value
         1605  +*/
         1606  +static void stdevFinalize(sqlite3_context *context){
         1607  +  StdevCtx *p;
         1608  +  p = sqlite3_aggregate_context(context, 0);
         1609  +  if( p && p->cnt>1 ){
         1610  +    sqlite3_result_double(context, sqrt(p->rS/(p->cnt-1)));
         1611  +  }else{
         1612  +    sqlite3_result_double(context, 0.0);
         1613  +  }
         1614  +}
         1615  +
         1616  +/*
         1617  +** Returns the variance value
         1618  +*/
         1619  +static void varianceFinalize(sqlite3_context *context){
         1620  +  StdevCtx *p;
         1621  +  p = sqlite3_aggregate_context(context, 0);
         1622  +  if( p && p->cnt>1 ){
         1623  +    sqlite3_result_double(context, p->rS/(p->cnt-1));
         1624  +  }else{
         1625  +    sqlite3_result_double(context, 0.0);
         1626  +  }
         1627  +}
         1628  +
         1629  +#ifdef SQLITE_SOUNDEX
         1630  +
         1631  +/* relicoder factored code */
         1632  +/*
         1633  +** Calculates the soundex value of a string
         1634  +*/
         1635  +
         1636  +static void soundex(const u8 *zIn, char *zResult){
         1637  +  int i, j;
         1638  +  static const unsigned char iCode[] = {
         1639  +    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
         1640  +    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
         1641  +    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
         1642  +    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
         1643  +    0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
         1644  +    1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
         1645  +    0, 0, 1, 2, 3, 0, 1, 2, 0, 0, 2, 2, 4, 5, 5, 0,
         1646  +    1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
         1647  +  };
         1648  +
         1649  +  for(i=0; zIn[i] && !isalpha(zIn[i]); i++){}
         1650  +  if( zIn[i] ){
         1651  +    zResult[0] = toupper(zIn[i]);
         1652  +    for(j=1; j<4 && zIn[i]; i++){
         1653  +      int code = iCode[zIn[i]&0x7f];
         1654  +      if( code>0 ){
         1655  +        zResult[j++] = code + '0';
         1656  +      }
         1657  +    }
         1658  +    while( j<4 ){
         1659  +      zResult[j++] = '0';
         1660  +    }
         1661  +    zResult[j] = 0;
         1662  +  }else{
         1663  +    strcpy(zResult, "?000");
         1664  +  }
         1665  +}
         1666  +
         1667  +/*
         1668  +** computes the number of different characters between the soundex value fo 2 strings
         1669  +*/
         1670  +static void differenceFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
         1671  +  char zResult1[8];
         1672  +  char zResult2[8];
         1673  +  char *zR1 = zResult1;
         1674  +  char *zR2 = zResult2;
         1675  +  int rVal = 0;
         1676  +  int i = 0;
         1677  +  const u8 *zIn1;
         1678  +  const u8 *zIn2;
         1679  +
         1680  +  assert( argc==2 );
         1681  +  
         1682  +  if( sqlite3_value_type(argv[0])==SQLITE_NULL || sqlite3_value_type(argv[1])==SQLITE_NULL ){
         1683  +    sqlite3_result_null(context);
         1684  +    return;
         1685  +  }
         1686  +  
         1687  +  zIn1 = (u8*)sqlite3_value_text(argv[0]);
         1688  +  zIn2 = (u8*)sqlite3_value_text(argv[1]);
         1689  +
         1690  +  soundex(zIn1, zR1);
         1691  +  soundex(zIn2, zR2);
         1692  +
         1693  +  for(i=0; i<4; ++i){
         1694  +    if( sqliteCharVal((unsigned char *)zR1)==sqliteCharVal((unsigned char *)zR2) )
         1695  +      ++rVal;
         1696  +    sqliteNextChar(zR1);
         1697  +    sqliteNextChar(zR2);
         1698  +  }
         1699  +  sqlite3_result_int(context, rVal);
         1700  +}
         1701  +#endif
         1702  +
         1703  +/*
         1704  +** This function registered all of the above C functions as SQL
         1705  +** functions.  This should be the only routine in this file with
         1706  +** external linkage.
         1707  +*/
         1708  +int RegisterExtensionFunctions(sqlite3 *db){
         1709  +  static const struct FuncDef {
         1710  +     char *zName;
         1711  +     signed char nArg;
         1712  +     u8 argType;           /* 0: none.  1: db  2: (-1) */
         1713  +     u8 eTextRep;          /* 1: UTF-16.  0: UTF-8 */
         1714  +     u8 needCollSeq;
         1715  +     void (*xFunc)(sqlite3_context*,int,sqlite3_value **);
         1716  +  } aFuncs[] = {
         1717  +    /* math.h */
         1718  +    { "acos",               1, 0, SQLITE_UTF8,    0, acosFunc  },
         1719  +    { "asin",               1, 0, SQLITE_UTF8,    0, asinFunc  },
         1720  +    { "atan",               1, 0, SQLITE_UTF8,    0, atanFunc  },
         1721  +    { "atn2",               2, 0, SQLITE_UTF8,    0, atn2Func  },
         1722  +    /* XXX alias */
         1723  +    { "atan2",              2, 0, SQLITE_UTF8,    0, atn2Func  },
         1724  +    { "acosh",              1, 0, SQLITE_UTF8,    0, acoshFunc  },
         1725  +    { "asinh",              1, 0, SQLITE_UTF8,    0, asinhFunc  },
         1726  +    { "atanh",              1, 0, SQLITE_UTF8,    0, atanhFunc  },
         1727  +
         1728  +    { "difference",         2, 0, SQLITE_UTF8,    0, differenceFunc},
         1729  +    { "degrees",            1, 0, SQLITE_UTF8,    0, rad2degFunc  },
         1730  +    { "radians",            1, 0, SQLITE_UTF8,    0, deg2radFunc  },
         1731  +
         1732  +    { "cos",                1, 0, SQLITE_UTF8,    0, cosFunc  },
         1733  +    { "sin",                1, 0, SQLITE_UTF8,    0, sinFunc },
         1734  +    { "tan",                1, 0, SQLITE_UTF8,    0, tanFunc },
         1735  +    { "cot",                1, 0, SQLITE_UTF8,    0, cotFunc },
         1736  +    { "cosh",               1, 0, SQLITE_UTF8,    0, coshFunc  },
         1737  +    { "sinh",               1, 0, SQLITE_UTF8,    0, sinhFunc },
         1738  +    { "tanh",               1, 0, SQLITE_UTF8,    0, tanhFunc },
         1739  +    { "coth",               1, 0, SQLITE_UTF8,    0, cothFunc },
         1740  +
         1741  +    { "exp",                1, 0, SQLITE_UTF8,    0, expFunc  },
         1742  +    { "log",                1, 0, SQLITE_UTF8,    0, logFunc  },
         1743  +    { "log10",              1, 0, SQLITE_UTF8,    0, log10Func  },
         1744  +    { "power",              2, 0, SQLITE_UTF8,    0, powerFunc  },
         1745  +    { "sign",               1, 0, SQLITE_UTF8,    0, signFunc },
         1746  +    { "sqrt",               1, 0, SQLITE_UTF8,    0, sqrtFunc },
         1747  +    { "square",             1, 0, SQLITE_UTF8,    0, squareFunc },
         1748  +
         1749  +    { "ceil",               1, 0, SQLITE_UTF8,    0, ceilFunc },
         1750  +    { "floor",              1, 0, SQLITE_UTF8,    0, floorFunc },
         1751  +
         1752  +    { "pi",                 0, 0, SQLITE_UTF8,    1, piFunc },
         1753  +
         1754  +
         1755  +    /* string */
         1756  +    { "replicate",          2, 0, SQLITE_UTF8,    0, replicateFunc },
         1757  +    { "charindex",          2, 0, SQLITE_UTF8,    0, charindexFunc },
         1758  +    { "charindex",          3, 0, SQLITE_UTF8,    0, charindexFunc },
         1759  +    { "leftstr",            2, 0, SQLITE_UTF8,    0, leftFunc },
         1760  +    { "rightstr",           2, 0, SQLITE_UTF8,    0, rightFunc },
         1761  +#ifndef HAVE_TRIM
         1762  +    { "ltrim",              1, 0, SQLITE_UTF8,    0, ltrimFunc },
         1763  +    { "rtrim",              1, 0, SQLITE_UTF8,    0, rtrimFunc },
         1764  +    { "trim",               1, 0, SQLITE_UTF8,    0, trimFunc },
         1765  +    { "replace",            3, 0, SQLITE_UTF8,    0, replaceFunc },
         1766  +#endif
         1767  +    { "reverse",            1, 0, SQLITE_UTF8,    0, reverseFunc },
         1768  +    { "proper",             1, 0, SQLITE_UTF8,    0, properFunc },
         1769  +    { "padl",               2, 0, SQLITE_UTF8,    0, padlFunc },
         1770  +    { "padr",               2, 0, SQLITE_UTF8,    0, padrFunc },
         1771  +    { "padc",               2, 0, SQLITE_UTF8,    0, padcFunc },
         1772  +    { "strfilter",          2, 0, SQLITE_UTF8,    0, strfilterFunc },
         1773  +
         1774  +  };
         1775  +  /* Aggregate functions */
         1776  +  static const struct FuncDefAgg {
         1777  +    char *zName;
         1778  +    signed char nArg;
         1779  +    u8 argType;
         1780  +    u8 needCollSeq;
         1781  +    void (*xStep)(sqlite3_context*,int,sqlite3_value**);
         1782  +    void (*xFinalize)(sqlite3_context*);
         1783  +  } aAggs[] = {
         1784  +    { "stdev",            1, 0, 0, varianceStep, stdevFinalize  },
         1785  +    { "variance",         1, 0, 0, varianceStep, varianceFinalize  },
         1786  +    { "mode",             1, 0, 0, modeStep,     modeFinalize  },
         1787  +    { "median",           1, 0, 0, modeStep,     medianFinalize  },
         1788  +    { "lower_quartile",   1, 0, 0, modeStep,     lower_quartileFinalize  },
         1789  +    { "upper_quartile",   1, 0, 0, modeStep,     upper_quartileFinalize  },
         1790  +  };
         1791  +  int i;
         1792  +
         1793  +  for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){
         1794  +    void *pArg = 0;
         1795  +    switch( aFuncs[i].argType ){
         1796  +      case 1: pArg = db; break;
         1797  +      case 2: pArg = (void *)(-1); break;
         1798  +    }
         1799  +    //sqlite3CreateFunc
         1800  +    /* LMH no error checking */
         1801  +    sqlite3_create_function(db, aFuncs[i].zName, aFuncs[i].nArg,
         1802  +        aFuncs[i].eTextRep, pArg, aFuncs[i].xFunc, 0, 0);
         1803  +#if 0
         1804  +    if( aFuncs[i].needCollSeq ){
         1805  +      struct FuncDef *pFunc = sqlite3FindFunction(db, aFuncs[i].zName, 
         1806  +          strlen(aFuncs[i].zName), aFuncs[i].nArg, aFuncs[i].eTextRep, 0);
         1807  +      if( pFunc && aFuncs[i].needCollSeq ){
         1808  +        pFunc->needCollSeq = 1;
         1809  +      }
         1810  +    }
         1811  +#endif
         1812  +  }
         1813  +
         1814  +  for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){
         1815  +    void *pArg = 0;
         1816  +    switch( aAggs[i].argType ){
         1817  +      case 1: pArg = db; break;
         1818  +      case 2: pArg = (void *)(-1); break;
         1819  +    }
         1820  +    //sqlite3CreateFunc
         1821  +    /* LMH no error checking */
         1822  +    sqlite3_create_function(db, aAggs[i].zName, aAggs[i].nArg, SQLITE_UTF8, 
         1823  +        pArg, 0, aAggs[i].xStep, aAggs[i].xFinalize);
         1824  +#if 0
         1825  +    if( aAggs[i].needCollSeq ){
         1826  +      struct FuncDefAgg *pFunc = sqlite3FindFunction( db, aAggs[i].zName,
         1827  +          strlen(aAggs[i].zName), aAggs[i].nArg, SQLITE_UTF8, 0);
         1828  +      if( pFunc && aAggs[i].needCollSeq ){
         1829  +        pFunc->needCollSeq = 1;
         1830  +      }
         1831  +    }
         1832  +#endif
         1833  +  }
         1834  +  return 0;
         1835  +}
         1836  +
         1837  +#ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE
         1838  +int sqlite3_extension_init(
         1839  +    sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi){
         1840  +  SQLITE_EXTENSION_INIT2(pApi);
         1841  +  RegisterExtensionFunctions(db);
         1842  +  return 0;
         1843  +}
         1844  +#endif /* COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE */
         1845  +
         1846  +map map_make(cmp_func cmp){
         1847  +  map r;
         1848  +  r.cmp=cmp;
         1849  +  r.base = 0;
         1850  +
         1851  +  return r;
         1852  +}
         1853  +
         1854  +void* xcalloc(size_t nmemb, size_t size, char* s){
         1855  +  void* ret = calloc(nmemb, size);
         1856  +  return ret;
         1857  +}
         1858  +
         1859  +void xfree(void* p){
         1860  +  free(p);
         1861  +}
         1862  +
         1863  +void node_insert(node** n, cmp_func cmp, void *e){
         1864  +  int c;
         1865  +  node* nn;
         1866  +  if(*n==0){
         1867  +    nn = (node*)xcalloc(1,sizeof(node), "for node");
         1868  +    nn->data = e;
         1869  +    nn->count = 1;
         1870  +    *n=nn;
         1871  +  }else{
         1872  +    c=cmp((*n)->data,e);
         1873  +    if(0==c){
         1874  +      ++((*n)->count);
         1875  +      xfree(e);
         1876  +    }else if(c>0){
         1877  +      /* put it right here */
         1878  +      node_insert(&((*n)->l), cmp, e);
         1879  +    }else{
         1880  +      node_insert(&((*n)->r), cmp, e);
         1881  +    }
         1882  +  }
         1883  +}
         1884  +
         1885  +void map_insert(map *m, void *e){
         1886  +  node_insert(&(m->base), m->cmp, e);
         1887  +}
         1888  +
         1889  +void node_iterate(node *n, map_iterator iter, void* p){
         1890  +  if(n){
         1891  +    if(n->l)
         1892  +      node_iterate(n->l, iter, p);
         1893  +    iter(n->data, n->count, p);
         1894  +    if(n->r)
         1895  +      node_iterate(n->r, iter, p);
         1896  +  }
         1897  +}
         1898  +
         1899  +void map_iterate(map *m, map_iterator iter, void* p){
         1900  +  node_iterate(m->base, iter, p);
         1901  +}
         1902  +
         1903  +void node_destroy(node *n){
         1904  +  if(0!=n){
         1905  +    xfree(n->data);
         1906  +    if(n->l)
         1907  +      node_destroy(n->l);
         1908  +    if(n->r)
         1909  +      node_destroy(n->r);
         1910  +
         1911  +    xfree(n);
         1912  +  }
         1913  +}
         1914  +
         1915  +void map_destroy(map *m){
         1916  +  node_destroy(m->base);
         1917  +}
         1918  +
         1919  +int int_cmp(const void *a, const void *b){
         1920  +  int64_t aa = *(int64_t *)(a);
         1921  +  int64_t bb = *(int64_t *)(b);
         1922  +  /* printf("cmp %d <=> %d\n",aa,bb); */
         1923  +  if(aa==bb)
         1924  +    return 0;
         1925  +  else if(aa<bb)
         1926  +    return -1;
         1927  +  else
         1928  +    return 1;
         1929  +}
         1930  +
         1931  +int double_cmp(const void *a, const void *b){
         1932  +  double aa = *(double *)(a);
         1933  +  double bb = *(double *)(b);
         1934  +  /* printf("cmp %d <=> %d\n",aa,bb); */
         1935  +  if(aa==bb)
         1936  +    return 0;
         1937  +  else if(aa<bb)
         1938  +    return -1;
         1939  +  else
         1940  +    return 1;
         1941  +}
         1942  +
         1943  +void print_elem(void *e, int64_t c, void* p){
         1944  +  int ee = *(int*)(e);
         1945  +  printf("%d => %lld\n", ee,c);
         1946  +}
         1947  +

Added test/test-dyld.lua.

            1  +--
            2  +-- test for load_extension 
            3  +--
            4  +-- before running this script, you must compile extension-functions.c
            5  +-- e.g., in directory extras:
            6  +-- gcc -fno-common -dynamiclib extension-functions.c -o libsqlitefunctions.dylib
            7  +--
            8  +-- then run this script from the top level directory: lua test/test-dyld.lua 
            9  +
           10  +local sqlite3 = require "lsqlite3"
           11  +
           12  +local os = os
           13  +
           14  +local lunit = require "lunitx"
           15  +
           16  +local tests_sqlite3
           17  +
           18  +if _VERSION >= 'Lua 5.2' then
           19  +
           20  +    tests_sqlite3 = lunit.module('tests-sqlite3','seeall')
           21  +    _ENV = tests_sqlite3
           22  +
           23  +else
           24  +
           25  +    module('tests_sqlite3', lunit.testcase, package.seeall)
           26  +    tests_sqlite3 = _M
           27  +
           28  +end
           29  +
           30  +-- compat
           31  +
           32  +function lunit_wrap (name, fcn)
           33  +   tests_sqlite3['test_o_'..name] = fcn
           34  +end
           35  +
           36  +function lunit_TestCase (name)
           37  +   return lunit.module(name,'seeall')
           38  +end
           39  +
           40  +local db_dyld = lunit_TestCase("Load Extension")
           41  +
           42  +function db_dyld.setup()
           43  +  db_dyld.db = assert( sqlite3.open_memory() )
           44  +  assert_equal( sqlite3.OK, db_dyld.db:exec("CREATE TABLE test (id, name)") )
           45  +  assert_equal( sqlite3.OK, db_dyld.db:exec("INSERT INTO test VALUES (1, 'Hello World')") )
           46  +  assert_equal( sqlite3.OK, db_dyld.db:exec("INSERT INTO test VALUES (2, 'Hello Lua')") )
           47  +  assert_equal( sqlite3.OK, db_dyld.db:exec("INSERT INTO test VALUES (3, 'Hello sqlite3')") )
           48  +end
           49  +
           50  +function db_dyld.teardown()
           51  +  assert( db_dyld.db:close() )
           52  +end
           53  +
           54  +function db_dyld.test()
           55  +    local db = db_dyld.db
           56  +    assert_function( db.load_extension )
           57  +    assert_true( db:load_extension "extras/libsqlitefunctions" )
           58  +    for row in db:nrows("SELECT log10(id) as val FROM test WHERE name='Hello World'") do
           59  +      assert_equal (row.val, 0.0)
           60  +    end
           61  +    for row in db:nrows("SELECT reverse(name) as val FROM test WHERE id = 2") do
           62  +      assert_equal (row.val, 'auL olleH')
           63  +    end
           64  +    for row in db:nrows("SELECT padl(name, 16) as val FROM test WHERE id = 3") do
           65  +      assert_equal (row.val, '   Hello sqlite3')
           66  +    end
           67  +end
           68  +