Nice.

Say goodbye to transitive equality though.

> On 3 Dec 2016, at 5:02 AM, Keith Medcalf <kmedc...@dessus.com> wrote:
> 
> 
> feq (and friends) are an extension I wrote that does proper floating point 
> comparisons:
> 
> /*
> ** 2015-11-09
> **
> ** The author disclaims copyright to this source code.  In place of
> ** a legal notice, here is a blessing:
> **
> **    May you do good and not evil.
> **    May you find forgiveness for yourself and forgive others.
> **    May you share freely, never taking more than you give.
> **
> ******************************************************************************
> **
> ** This SQLite extension implements functions for comparison of floating point
> ** double precision numbers x and y where x is within a specified number of
> ** ULPs of y.
> **
> ** The default number of ULPs if not specified is 5.
> ** The size of an ULP is based on the scale of x.
> ** If only one value is specified it will be a y value and x will be 0.0
> **
> **   ulp(x)     -> ULP precision of x (increment to next representable number)
> **   epsilon(x) -> ULP precision of x (alias of ulp)
> **   ulps(x, y) -> number of ULP of x in (x - y)
> **
> **   flt(x[, y[, u]])   -> x less than y
> **   fle(x[, y[, u]])   -> x less or equal y
> **   feq(x[, y[, u]])   -> x equal y
> **   fge(x[, y[, u]])   -> x greater or equal y
> **   fgt(x[, y[, u]])   -> x greater than y
> **   fne(x[, y[, u]])   -> x not equal y
> **
> */
> #ifdef __cplusplus
> extern "C" {
> #endif
> 
> /*
> ** If the MATH.H is already included, use the math library
> ** otherwise make sure memcpy is defined
> */
> 
> #ifndef _MATH_H_
> #include <string.h>
> #endif
> 
> #ifndef SQLITE_PRIVATE
>    #define SQLITE_PRIVATE static
> #endif
> 
> #ifdef SQLITE_CORE
>    #include "sqlite3.h"
> #else
>    #ifdef _HAVE_SQLITE_CONFIG_H
>        #include "config.h"
>    #endif
>    #include "sqlite3ext.h"
>    SQLITE_EXTENSION_INIT1
> #endif
> 
> /*
> ** Common Functions
> */
> 
> #ifdef _MATH_H_
> 
> /*
> ** If we have the math library then use it
> */
> 
> static double epsilon(double value)
> {
>    int exponent;
>    double mantissa = frexp(value, &exponent);
>    return ldexp(1.0, exponent - 53);
> }
> #define sqlfcmp_fabs fabs
> 
> #else
> 
> /*
> ** If we do not have the math library already, do not force it to be included
> */
> 
> static double epsilon(double value)
> {
>    sqlite_int64 a;
>    double r = value;
>    if (r < 0)
>        r = -r;
>    memcpy(&a, &r, sizeof(a));
>    if (a == 0)
>        a = (970LL << 52);
>    else
>        a = ((a >> 52) - 52LL) << 52;
>    memcpy(&r, &a, sizeof(a));
>    return r;
> }
> 
> static double sqlfcmp_fabs(double v)
> {
>    if (v < 0)
>        return -v;
>    return v;
> }
> #endif
> 
> static double distance(double x, double y)
> {
>    return (x - y) / epsilon(x);
> }
> 
> /*
> ** Return the distance to the next representable number
> */
> 
> SQLITE_PRIVATE void _ulp(sqlite3_context *context, int argc, sqlite3_value 
> **argv)
> {
>    sqlite3_result_double(context, epsilon(sqlite3_value_double(argv[0])));
> }
> 
> /*
> ** Return the number of representable numbers between x and y based on the 
> ULP of x
> ** if only one parameter, it is y and x is 0.0
> */
> 
> SQLITE_PRIVATE void _ulps(sqlite3_context *context, int argc, sqlite3_value 
> **argv)
> {
>    double x = 0.0;
>    double y = 0.0;
>    uintptr_t flag = (uintptr_t)sqlite3_user_data(context);
>    if ((argc == 0) || (argc > 2))
>        return;
>    if (argc == 1)
>        y = sqlite3_value_double(argv[0]);
>    else
>    {
>        x = sqlite3_value_double(argv[0]);
>        y = sqlite3_value_double(argv[1]);
>    }
>    sqlite3_result_double(context, distance(x, y));
> }
> 
> /*
> ** Perform Floating Point (Double) Comparisons using ULP of first parameter
> **   first parameter will be 0.0 if only one parameter specified, which will 
> be y
> ** User Context defines operations
> **  flag == 0       x != y
> **  flag & 1        x < y
> **  flag & 2        x = y
> **  flag & 4        x > y
> ** Flag values are additive
> **  flag & 3        x <= y
> **  flag & 6        x >= y
> ** optional third parameter is ULPs of x to consider equal, defaults to 5
> ** if only one parameter then test if x within 5 ULPs of 0.0
> */
> 
> SQLITE_PRIVATE void _fpc(sqlite3_context *context, int argc, sqlite3_value 
> **argv)
> {
>    double x = 0.0;
>    double y = 0.0;
>    double ulps = 0.0;
>    double delta = 5.0;
>    uintptr_t flag = (uintptr_t)sqlite3_user_data(context);
>    if ((argc == 0) || (argc > 3))
>        return;
>    if (argc == 1)
>        y = sqlite3_value_double(argv[0]);
>    else
>    {
>        x = sqlite3_value_double(argv[0]);
>        y = sqlite3_value_double(argv[1]);
>    }
>    if (argc > 2)
>        delta = sqlfcmp_fabs(sqlite3_value_double(argv[2]));
>    ulps = distance(x, y);
>    if (   ((flag & 2)  && (sqlfcmp_fabs(ulps) <= delta))   /* flag & 2 means 
> test equal */
>        || ((flag & 1)  && (ulps < -delta))                 /* flag & 1 means 
> test less than */
>        || ((flag & 4)  && (ulps > delta))                  /* flag & 4 means 
> test greater than */
>        || ((flag == 0) && (sqlfcmp_fabs(ulps) > delta)))   /* no flags test 
> not equal */
>        sqlite3_result_int(context, 1);
>    else
>        sqlite3_result_int(context, 0);
> }
> 
> /*
> ** If compiling as a builtin extension, don't export the initializer -- make 
> it SQLITE_PRIVATE
> ** Change name of initializer to sqlite3_init_<nameOfExtension>
> */
> 
> /* SQLite invokes this routine once when it loads the extension.
> ** Create new functions, collating sequences, and virtual table
> ** modules here.  This is usually the only exported symbol in
> ** the shared library.
> */
> 
> #ifdef _WIN32
> #ifndef SQLITE_CORE
> __declspec(dllexport)
> #endif
> #endif
> #ifdef SQLITE_CORE
> static
> #endif
> int sqlite3_sqlfcmp_init(sqlite3 *db, char **pzErrMsg, const 
> sqlite3_api_routines *pApi)
> {
>    int nErr = 0;
> 
>    SQLITE_EXTENSION_INIT2(pApi);
> 
>    nErr += sqlite3_create_function(db, "ulp",          1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  0,              _ulp,           0, 0);
>    nErr += sqlite3_create_function(db, "epsilon",      1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  0,              _ulp,           0, 0);
>    nErr += sqlite3_create_function(db, "ulps",        -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  0,              _ulps,          0, 0);
>    nErr += sqlite3_create_function(db, "fne",         -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  0,              _fpc,           0, 0);
>    nErr += sqlite3_create_function(db, "flt",         -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)1,       _fpc,           0, 0);
>    nErr += sqlite3_create_function(db, "feq",         -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)2,       _fpc,           0, 0);
>    nErr += sqlite3_create_function(db, "fle",         -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)3,       _fpc,           0, 0);
>    nErr += sqlite3_create_function(db, "fgt",         -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)4,       _fpc,           0, 0);
>    nErr += sqlite3_create_function(db, "fge",         -1, 
> SQLITE_ANY|SQLITE_DETERMINISTIC,  (void*)6,       _fpc,           0, 0);
> 
>    return nErr ? SQLITE_ERROR : SQLITE_OK;
> }
> 
> #ifdef __cplusplus
> }
> #endif
> 
> 
>> -----Original Message-----
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Warren Young
>> Sent: Friday, 2 December, 2016 11:37
>> To: SQLite mailing list
>> Subject: Re: [sqlite] Datatype for prices (1,500)
>> 
>>> On Nov 30, 2016, at 6:53 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
>>> 
>>> sqlite> SELECT CASE WHEN (SELECT feq(0, sum(amount)) FROM transactions)
>> 
>> What’s feq()?  I don’t see it in the SQLite documentation.
>> 
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to