1 /*
2 ** 2013-04-17
3 **
4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
6 **
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
10 **
11 ******************************************************************************
12 **
13 ** This SQLite extension implements functions for the exact display
14 ** and input of IEEE754 Binary64 floating-point numbers.
15 **
16 ** ieee754(X)
17 ** ieee754(Y,Z)
18 **
19 ** In the first form, the value X should be a floating-point number.
20 ** The function will return a string of the form 'ieee754(Y,Z)' where
21 ** Y and Z are integers such that X==Y*pow(2,Z).
22 **
23 ** In the second form, Y and Z are integers which are the mantissa and
24 ** base-2 exponent of a new floating point number. The function returns
25 ** a floating-point value equal to Y*pow(2,Z).
26 **
27 ** Examples:
28 **
29 ** ieee754(2.0) -> 'ieee754(2,0)'
30 ** ieee754(45.25) -> 'ieee754(181,-2)'
31 ** ieee754(2, 0) -> 2.0
32 ** ieee754(181, -2) -> 45.25
33 **
34 ** Two additional functions break apart the one-argument ieee754()
35 ** result into separate integer values:
36 **
37 ** ieee754_mantissa(45.25) -> 181
38 ** ieee754_exponent(45.25) -> -2
39 **
40 ** These functions convert binary64 numbers into blobs and back again.
41 **
42 ** ieee754_from_blob(x'3ff0000000000000') -> 1.0
43 ** ieee754_to_blob(1.0) -> x'3ff0000000000000'
44 **
45 ** In all single-argument functions, if the argument is an 8-byte blob
46 ** then that blob is interpreted as a big-endian binary64 value.
47 **
48 **
49 ** EXACT DECIMAL REPRESENTATION OF BINARY64 VALUES
50 ** -----------------------------------------------
51 **
52 ** This extension in combination with the separate 'decimal' extension
53 ** can be used to compute the exact decimal representation of binary64
54 ** values. To begin, first compute a table of exponent values:
55 **
56 ** CREATE TABLE pow2(x INTEGER PRIMARY KEY, v TEXT);
57 ** WITH RECURSIVE c(x,v) AS (
58 ** VALUES(0,'1')
59 ** UNION ALL
60 ** SELECT x+1, decimal_mul(v,'2') FROM c WHERE x+1<=971
61 ** ) INSERT INTO pow2(x,v) SELECT x, v FROM c;
62 ** WITH RECURSIVE c(x,v) AS (
63 ** VALUES(-1,'0.5')
64 ** UNION ALL
65 ** SELECT x-1, decimal_mul(v,'0.5') FROM c WHERE x-1>=-1075
66 ** ) INSERT INTO pow2(x,v) SELECT x, v FROM c;
67 **
68 ** Then, to compute the exact decimal representation of a floating
69 ** point value (the value 47.49 is used in the example) do:
70 **
71 ** WITH c(n) AS (VALUES(47.49))
72 ** ---------------^^^^^---- Replace with whatever you want
73 ** SELECT decimal_mul(ieee754_mantissa(c.n),pow2.v)
74 ** FROM pow2, c WHERE pow2.x=ieee754_exponent(c.n);
75 **
76 ** Here is a query to show various boundry values for the binary64
77 ** number format:
78 **
79 ** WITH c(name,bin) AS (VALUES
80 ** ('minimum positive value', x'0000000000000001'),
81 ** ('maximum subnormal value', x'000fffffffffffff'),
82 ** ('mininum positive nornal value', x'0010000000000000'),
83 ** ('maximum value', x'7fefffffffffffff'))
84 ** SELECT c.name, decimal_mul(ieee754_mantissa(c.bin),pow2.v)
85 ** FROM pow2, c WHERE pow2.x=ieee754_exponent(c.bin);
86 **
87 */
88 #include "sqlite3ext.h"
89 SQLITE_EXTENSION_INIT1
90 #include <assert.h>
91 #include <string.h>
92
93 /* Mark a function parameter as unused, to suppress nuisance compiler
94 ** warnings. */
95 #ifndef UNUSED_PARAMETER
96 # define UNUSED_PARAMETER(X) (void)(X)
97 #endif
98
99 /*
100 ** Implementation of the ieee754() function
101 */
ieee754func(sqlite3_context * context,int argc,sqlite3_value ** argv)102 static void ieee754func(
103 sqlite3_context *context,
104 int argc,
105 sqlite3_value **argv
106 ){
107 if( argc==1 ){
108 sqlite3_int64 m, a;
109 double r;
110 int e;
111 int isNeg;
112 char zResult[100];
113 assert( sizeof(m)==sizeof(r) );
114 if( sqlite3_value_type(argv[0])==SQLITE_BLOB
115 && sqlite3_value_bytes(argv[0])==sizeof(r)
116 ){
117 const unsigned char *x = sqlite3_value_blob(argv[0]);
118 unsigned int i;
119 sqlite3_uint64 v = 0;
120 for(i=0; i<sizeof(r); i++){
121 v = (v<<8) | x[i];
122 }
123 memcpy(&r, &v, sizeof(r));
124 }else{
125 r = sqlite3_value_double(argv[0]);
126 }
127 if( r<0.0 ){
128 isNeg = 1;
129 r = -r;
130 }else{
131 isNeg = 0;
132 }
133 memcpy(&a,&r,sizeof(a));
134 if( a==0 ){
135 e = 0;
136 m = 0;
137 }else{
138 e = a>>52;
139 m = a & ((((sqlite3_int64)1)<<52)-1);
140 if( e==0 ){
141 m <<= 1;
142 }else{
143 m |= ((sqlite3_int64)1)<<52;
144 }
145 while( e<1075 && m>0 && (m&1)==0 ){
146 m >>= 1;
147 e++;
148 }
149 if( isNeg ) m = -m;
150 }
151 switch( *(int*)sqlite3_user_data(context) ){
152 case 0:
153 sqlite3_snprintf(sizeof(zResult), zResult, "ieee754(%lld,%d)",
154 m, e-1075);
155 sqlite3_result_text(context, zResult, -1, SQLITE_TRANSIENT);
156 break;
157 case 1:
158 sqlite3_result_int64(context, m);
159 break;
160 case 2:
161 sqlite3_result_int(context, e-1075);
162 break;
163 }
164 }else{
165 sqlite3_int64 m, e, a;
166 double r;
167 int isNeg = 0;
168 m = sqlite3_value_int64(argv[0]);
169 e = sqlite3_value_int64(argv[1]);
170 if( m<0 ){
171 isNeg = 1;
172 m = -m;
173 if( m<0 ) return;
174 }else if( m==0 && e>-1000 && e<1000 ){
175 sqlite3_result_double(context, 0.0);
176 return;
177 }
178 while( (m>>32)&0xffe00000 ){
179 m >>= 1;
180 e++;
181 }
182 while( m!=0 && ((m>>32)&0xfff00000)==0 ){
183 m <<= 1;
184 e--;
185 }
186 e += 1075;
187 if( e<=0 ){
188 /* Subnormal */
189 m >>= 1-e;
190 e = 0;
191 }else if( e>0x7ff ){
192 e = 0x7ff;
193 }
194 a = m & ((((sqlite3_int64)1)<<52)-1);
195 a |= e<<52;
196 if( isNeg ) a |= ((sqlite3_uint64)1)<<63;
197 memcpy(&r, &a, sizeof(r));
198 sqlite3_result_double(context, r);
199 }
200 }
201
202 /*
203 ** Functions to convert between blobs and floats.
204 */
ieee754func_from_blob(sqlite3_context * context,int argc,sqlite3_value ** argv)205 static void ieee754func_from_blob(
206 sqlite3_context *context,
207 int argc,
208 sqlite3_value **argv
209 ){
210 UNUSED_PARAMETER(argc);
211 if( sqlite3_value_type(argv[0])==SQLITE_BLOB
212 && sqlite3_value_bytes(argv[0])==sizeof(double)
213 ){
214 double r;
215 const unsigned char *x = sqlite3_value_blob(argv[0]);
216 unsigned int i;
217 sqlite3_uint64 v = 0;
218 for(i=0; i<sizeof(r); i++){
219 v = (v<<8) | x[i];
220 }
221 memcpy(&r, &v, sizeof(r));
222 sqlite3_result_double(context, r);
223 }
224 }
ieee754func_to_blob(sqlite3_context * context,int argc,sqlite3_value ** argv)225 static void ieee754func_to_blob(
226 sqlite3_context *context,
227 int argc,
228 sqlite3_value **argv
229 ){
230 UNUSED_PARAMETER(argc);
231 if( sqlite3_value_type(argv[0])==SQLITE_FLOAT
232 || sqlite3_value_type(argv[0])==SQLITE_INTEGER
233 ){
234 double r = sqlite3_value_double(argv[0]);
235 sqlite3_uint64 v;
236 unsigned char a[sizeof(r)];
237 unsigned int i;
238 memcpy(&v, &r, sizeof(r));
239 for(i=1; i<=sizeof(r); i++){
240 a[sizeof(r)-i] = v&0xff;
241 v >>= 8;
242 }
243 sqlite3_result_blob(context, a, sizeof(r), SQLITE_TRANSIENT);
244 }
245 }
246
247
248 #ifdef _WIN32
249 __declspec(dllexport)
250 #endif
sqlite3_ieee_init(sqlite3 * db,char ** pzErrMsg,const sqlite3_api_routines * pApi)251 int sqlite3_ieee_init(
252 sqlite3 *db,
253 char **pzErrMsg,
254 const sqlite3_api_routines *pApi
255 ){
256 static const struct {
257 char *zFName;
258 int nArg;
259 int iAux;
260 void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
261 } aFunc[] = {
262 { "ieee754", 1, 0, ieee754func },
263 { "ieee754", 2, 0, ieee754func },
264 { "ieee754_mantissa", 1, 1, ieee754func },
265 { "ieee754_exponent", 1, 2, ieee754func },
266 { "ieee754_to_blob", 1, 0, ieee754func_to_blob },
267 { "ieee754_from_blob", 1, 0, ieee754func_from_blob },
268
269 };
270 unsigned int i;
271 int rc = SQLITE_OK;
272 SQLITE_EXTENSION_INIT2(pApi);
273 (void)pzErrMsg; /* Unused parameter */
274 for(i=0; i<sizeof(aFunc)/sizeof(aFunc[0]) && rc==SQLITE_OK; i++){
275 rc = sqlite3_create_function(db, aFunc[i].zFName, aFunc[i].nArg,
276 SQLITE_UTF8|SQLITE_INNOCUOUS,
277 (void*)&aFunc[i].iAux,
278 aFunc[i].xFunc, 0, 0);
279 }
280 return rc;
281 }
282