Re: [sqlite] IEE754-2008 decimal32, decimal64 datatypes and SQLite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jacob Lister wrote: > What provision is there for the inclusion of new data types in SQLite > and is such an inclusion possible or likely in future? Updating or adding a data type would affect the file format and many of the API families (eg sqlite3_bind_*, sqlite3_column_*). The chance of such a change going into the SQLite core is practically zero since it would break compatibility at the file format and API level. If you are ok with your files and API usage being incompatible with "standard" SQLite ones then I'd recommend contracting the SQLite "company" to do the work. (They'd make it fit in best, include tests, maintain it for future SQLite versions and deal with niggly issues such as query optimisers.) http://www.hwaci.com/sw/sqlite/prosupport.html > My current > thinking is to store decimal32/64 bit values as 64 bit integers in an > SQLite database and use custom functions to convert these to/from > integers/strings/doubles etc and perform arithmetic, The problem with repurposing integers is that they can easily be accidentally used as integers without signalling an error. You would be better off using blobs. (Sadly they don't give errors but do give zero so you'd at least be able to detect wrong numbers coming back.) sqlite> select x'aa'+x'bb'; 0 sqlite> select x'aa'/x'bb'; sqlite> select x'aa'-x'bb'; 0 sqlite> select x'aa'*x'bb'; 0 > but obviously a native integrated type would be more convenient Not only do you need the type but every arithmetic operation would also need to be updated. It will be even more fun if you also want to support the - -1985 representations (eg normal SQLite database format) since you'll have both semantics in the same program at the same time. Are you going to be using a CPU that has native -2008 instructions? Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksuytMACgkQmOOfHg372QTQDACfTPmixLnSRXUzps85Z0daDG3y k5MAn1i8J8FwWwzXx4D1MLVlc0PoBXP3 =gBmz -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IEE754-2008 decimal32, decimal64 datatypes and SQLite
On 21 Dec 2009, at 1:09am, Roger Binns wrote: > Not only do you need the type but every arithmetic operation would also need > to be updated. It will be even more fun if you also want to support the > - -1985 representations (eg normal SQLite database format) since you'll have > both semantics in the same program at the same time. Are you going to be > using a CPU that has native -2008 instructions? One would probably do it not by changing any existing datatype but by introducing one or more new ones (yes, I have an idea how much work this would take). No reason why sqlite3 shouldn't support REAL, INTEGER and IEEd and IEEb types, besides bloat. I assume one would use an existing Open C library for doing math on that new type of data, to save time developing one's own, and to ensure compatibility with other apps. As for how these numbers are stored in the database file, I suspect one would just straight to b128 and d128. People who didn't want to waste 128 bits could use REAL and INTEGER as before. There are certain things I don't want to see in SQLite3 (not that anyone cares what I want). Users & privilages, for instance. But I can see the point in commonly used standards like that. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IEE754-2008 decimal32, decimal64 datatypes and SQLite
Simon Slavin wrote: > One would probably do it not by changing any existing datatype but by > introducing one or more new ones The existing double could be repurposed. This would all depend on compatibility and other stuff - ie DRH and Jacob would be far better arbiters between them of the best approach. > No reason why sqlite3 shouldn't support REAL, INTEGER and IEEd and IEEb > types, What about people who care about complex numbers? Or those that do astronomy? And of course we all known about currency. And some people would like to be able to record any date in Earth's past and future. A case can be made for many data types even just trying to represent "numbers". > I assume one would use an existing Open C library for doing math on that new type of data, That would be considerably slower than builtin CPU instructions. You also have the issue of type conversions - eg adding one type of number to another involves conversion plus semantics (over/underflow, precision, various other pedantries). [In case it isn't clear I am referring to processors that implement the -2008 flavoured instructions in addition to -1985 ones.] > to save time developing one's own, and to ensure compatibility with other apps. Surely you mean compatibility with the standard :-) I wouldn't expect SQLite to contain a home made library for this stuff, but there certainly would have to be conditional code for choosing between CPU instructions and emulating them plus various type conversions when the operands don't have matching types. > But I can see the point in commonly used standards like that. XML is a commonly used standard - how about that in SQLite. Kidding :-) Adding new types to SQLite is hard as it affects so much code. Other databases do support adding your own types - maybe this is one area you get to pick "Lite" or "heavy". Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IEE754-2008 decimal32, decimal64 datatypes and SQLite
On 21 Dec 2009, at 6:59am, Roger Binns wrote: > Simon Slavin wrote: >> I assume one would use an existing Open C library for doing math on >> that new type of data, > > That would be considerably slower than builtin CPU instructions. You > also have the issue of type conversions - eg adding one type of number > to another involves conversion plus semantics (over/underflow, > precision, various other pedantries). [In case it isn't clear I am > referring to processors that implement the -2008 flavoured instructions > in addition to -1985 ones.] Showing my ignorance of C here. Does C use proper IEE755 maths when you do mundane things like add two floats together ? I assumed it compiled down to simple CPU calls which were faster than IEE755 but not as clever with things like NaN. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IEE754-2008 decimal32, decimal64 datatypes and SQLite
Simon Slavin wrote: > Showing my ignorance of C here. Does C use proper IEE755 maths when you do > mundane things like add two floats together ? You probably mean IEEE 754. The C standard doesn't mandate the use of IEEE 754 floating point arithmetic. The implementation may choose to implement it: in this case, it should define __STDC_IEC_559__ macro to allow applications that care to detect this fact (and, e.g., switch from emulation library to native instructions). C99 appendix F defines how C language constructs correspond to IEEE 754 types and operations (for those implementations that claim to support IEEE 754). Nowadays, it would be difficult to find a piece of hardware, or a C implementation, that supports floating point arithmetic but doesn't follow IEEE 754. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IEE754-2008 decimal32, decimal64 datatypes and SQLite
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: > Does C use proper IEE755 maths when you do mundane things like add two floats > together ? I assumed it compiled down to simple CPU calls which were faster > than IEE755 but not as clever with things like NaN. The vast majority of processors implement IEEE754-1985 and have native instructions for doing so. The C compiler typically compiles down to those instructions. IEE754-2008 is a superset of -1985 and only some processors have instructions for it. http://en.wikipedia.org/wiki/IEEE_754-2008 http://en.wikipedia.org/wiki/IEEE_754-1985 Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksvnegACgkQmOOfHg372QTGIgCgmzX7zZxNpR+AAK0kk0RPmYNY ncMAnRlAei6kwUHgvlRSUDoJayZNpxtq =2Jlc -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users