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 */ 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 171 /* Limit the range of e. Ticket 22dea1cfdb9151e4 2021-03-02 */ 172 if( e>10000 ){ 173 e = 10000; 174 }else if( e<-10000 ){ 175 e = -10000; 176 } 177 178 if( m<0 ){ 179 isNeg = 1; 180 m = -m; 181 if( m<0 ) return; 182 }else if( m==0 && e>-1000 && e<1000 ){ 183 sqlite3_result_double(context, 0.0); 184 return; 185 } 186 while( (m>>32)&0xffe00000 ){ 187 m >>= 1; 188 e++; 189 } 190 while( m!=0 && ((m>>32)&0xfff00000)==0 ){ 191 m <<= 1; 192 e--; 193 } 194 e += 1075; 195 if( e<=0 ){ 196 /* Subnormal */ 197 if( 1-e >= 64 ){ 198 m = 0; 199 }else{ 200 m >>= 1-e; 201 } 202 e = 0; 203 }else if( e>0x7ff ){ 204 e = 0x7ff; 205 } 206 a = m & ((((sqlite3_int64)1)<<52)-1); 207 a |= e<<52; 208 if( isNeg ) a |= ((sqlite3_uint64)1)<<63; 209 memcpy(&r, &a, sizeof(r)); 210 sqlite3_result_double(context, r); 211 } 212 } 213 214 /* 215 ** Functions to convert between blobs and floats. 216 */ 217 static void ieee754func_from_blob( 218 sqlite3_context *context, 219 int argc, 220 sqlite3_value **argv 221 ){ 222 UNUSED_PARAMETER(argc); 223 if( sqlite3_value_type(argv[0])==SQLITE_BLOB 224 && sqlite3_value_bytes(argv[0])==sizeof(double) 225 ){ 226 double r; 227 const unsigned char *x = sqlite3_value_blob(argv[0]); 228 unsigned int i; 229 sqlite3_uint64 v = 0; 230 for(i=0; i<sizeof(r); i++){ 231 v = (v<<8) | x[i]; 232 } 233 memcpy(&r, &v, sizeof(r)); 234 sqlite3_result_double(context, r); 235 } 236 } 237 static void ieee754func_to_blob( 238 sqlite3_context *context, 239 int argc, 240 sqlite3_value **argv 241 ){ 242 UNUSED_PARAMETER(argc); 243 if( sqlite3_value_type(argv[0])==SQLITE_FLOAT 244 || sqlite3_value_type(argv[0])==SQLITE_INTEGER 245 ){ 246 double r = sqlite3_value_double(argv[0]); 247 sqlite3_uint64 v; 248 unsigned char a[sizeof(r)]; 249 unsigned int i; 250 memcpy(&v, &r, sizeof(r)); 251 for(i=1; i<=sizeof(r); i++){ 252 a[sizeof(r)-i] = v&0xff; 253 v >>= 8; 254 } 255 sqlite3_result_blob(context, a, sizeof(r), SQLITE_TRANSIENT); 256 } 257 } 258 259 260 #ifdef _WIN32 261 __declspec(dllexport) 262 #endif 263 int sqlite3_ieee_init( 264 sqlite3 *db, 265 char **pzErrMsg, 266 const sqlite3_api_routines *pApi 267 ){ 268 static const struct { 269 char *zFName; 270 int nArg; 271 int iAux; 272 void (*xFunc)(sqlite3_context*,int,sqlite3_value**); 273 } aFunc[] = { 274 { "ieee754", 1, 0, ieee754func }, 275 { "ieee754", 2, 0, ieee754func }, 276 { "ieee754_mantissa", 1, 1, ieee754func }, 277 { "ieee754_exponent", 1, 2, ieee754func }, 278 { "ieee754_to_blob", 1, 0, ieee754func_to_blob }, 279 { "ieee754_from_blob", 1, 0, ieee754func_from_blob }, 280 281 }; 282 unsigned int i; 283 int rc = SQLITE_OK; 284 SQLITE_EXTENSION_INIT2(pApi); 285 (void)pzErrMsg; /* Unused parameter */ 286 for(i=0; i<sizeof(aFunc)/sizeof(aFunc[0]) && rc==SQLITE_OK; i++){ 287 rc = sqlite3_create_function(db, aFunc[i].zFName, aFunc[i].nArg, 288 SQLITE_UTF8|SQLITE_INNOCUOUS, 289 (void*)&aFunc[i].iAux, 290 aFunc[i].xFunc, 0, 0); 291 } 292 return rc; 293 } 294