ANN: SQLite3 Decimal Extension

classic Classic list List threaded Threaded
31 messages Options
12
Reply | Threaded
Open this post in threaded view
|

ANN: SQLite3 Decimal Extension

Lifepillar-2
[I hope that this kind of announcement is not off-topic here]

SQLite3 Decimal is an extension implementing exact decimal arithmetic
for SQLite3. It is currently unfinished and under development.  At this
point anything, including the user interface and the internals, is
subject to change. I am publishing it early because I am seeking to get
as much feedback as possible to "get it right".

The extension is currently using the excellent decNumber library, but it
does not aim to become an IEEE 754 conforming implementation. It is also
totally unrelated to SQL decimal/numeric types.

Decimals are stored as blobs and the storage format is a minor variant
of decimalInfinite (https://arxiv.org/abs/1506.01598). That is a totally
ordered encoding, so decimals can be compared directly (memcmp()) and
also indexed (does SQLite support indexes on blobs?).

[Here, I must thank Dr. Hipp, with whom I had a brief email exchange
severals moons ago, who convinced me that the IEEE 754 encoding was not
an ideal storage format for databases]

By default, the precision is limited to 39 digits and exponents must be
in the range [-99,999,999,+99,999,999] (for some mathematical
operations, the exponent must not exceed 99,999 in absolute value). Such
parameters may be configured at compile time and also changed at
runtime. Any integer or fractional number satisfying such requirements
can be manipulated. Note that the on-disk representation is *not*
subject to such limits and can accommodate arbitrarily small or
arbitrarily large decimals. With the defaults just mentioned, a decimal
occupies between 1 and 24 bytes on disk (plus any overhead that blobs
may add).

As I have said, I welcome any feedback, from the super-technical to the
end-user oriented. There is no manual so far, but the code is mostly
documented. You may find a sample session in the repository's home page.

Also, this is my first public Fossil repository: if you have any
suggestions on how I should improve its configuration, let me know.

So, here for the adventurous ones:

Official repository:

    https://chiselapp.com/user/lifepillar/repository/sqlite3decimal

Git mirror (which exists only for testing `fossil git export`...):

    https://github.com/lifepillar/sqlite3decimal-mirror

Enjoy,
Life.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Dominique Devienne
On Wed, Apr 3, 2019 at 2:31 PM Lifepillar <[hidden email]> wrote:

> [I hope that this kind of announcement is not off-topic here]
>

Not at all, IMHO.


> SQLite3 Decimal is an extension implementing exact decimal arithmetic
> for SQLite3. It is currently unfinished and under development.


I'm curious, what was your motivation for doing this?
Use cases envisioned for its use?


> Decimals are stored as blobs and the storage format is a minor variant
> of decimalInfinite (https://arxiv.org/abs/1506.01598).


Does it use some kind of magic cookie?
How can you distinguish your encoding from an arbitrary blob?

Unfortunately, SQLite does not support UDTs, but it did grow in recent
months
the https://www.sqlite.org/c3ref/value_subtype.html API, which plays a
poor-man's
substitute when call chaining.

That OTOH has no influence on how the value will be *stored in tables*,
unfortunately, so you cannot have alternate representation of the value,
one optimized for storage, another for processing (in call chains), so maybe
it's all moot, mentioning this.

That is a totally
> ordered encoding, so decimals can be compared directly (memcmp()) and
> also indexed (does SQLite support indexes on blobs?).
>

Yes it does.


> As I have said, I welcome any feedback, from the super-technical to the
> end-user oriented. There is no manual so far, but the code is mostly
> documented. You may find a sample session in the repository's home page.
>

From an End-User POV, the call-chained-functions are "heavy looking".
So maybe a printf-like API that parses a now-looking math expression
and replaces its placeholders with the var-arg supplied arguments to the
function
would make the "SQL" much more readable.

Since you cannot extend the regular arithmetic expressions in SQLite to act
on your UDTs
differently than the built-in processing, that would be the next best thing
IMHO.

All in all, it looks very well done. You're obviously a very competent
programmer IMHO :).

Thanks, --DD

PS: Seems like you're using non-ascii chars in your source code, notably
the #pragma mark.
  That's pretty, but asking for trouble IMHO, and likely to cause issues
for cross-platform support.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Lifepillar-2
In reply to this post by Lifepillar-2
> > SQLite3 Decimal is an extension implementing exact decimal arithmetic
> > for SQLite3. It is currently unfinished and under development.
>
>
> I'm curious, what was your motivation for doing this?
> Use cases envisioned for its use?

Mainly financial applications. Beancount’s author
(http://furius.ca/beancount/) has a series of documents arguing convincingly
about the need for a library like this.

> > Decimals are stored as blobs and the storage format is a minor variant
> > of decimalInfinite (https://arxiv.org/abs/1506.01598).
>
> Does it use some kind of magic cookie?

No.

> How can you distinguish your encoding from an arbitrary blob?

I don't. In fact, you may pass any blob, but if it doesn’t have the right
format, it results in a decoding error (which, currently, may not be very
well tolerated by the library-I have to check that).

> Unfortunately, SQLite does not support UDTs, but it did grow in recent
> months
> the https://www.sqlite.org/c3ref/value_subtype.html API, which plays a
> poor-man's
> substitute when call chaining.

Thanks, I didn’t know about subtypes. I’ll read about them, although, at a
quick glance, I am not sure how they might be used to enforce type
checking in my case.

> That OTOH has no influence on how the value will be *stored in tables*,
> unfortunately, so you cannot have alternate representation of the value,
> one optimized for storage, another for processing (in call chains), so maybe
> it's all moot, mentioning this.
>
> That is a totally
> > ordered encoding, so decimals can be compared directly (memcmp()) and
> > also indexed (does SQLite support indexes on blobs?).
> >
>
> Yes it does.

Great!

> > As I have said, I welcome any feedback, from the super-technical to the
> > end-user oriented. There is no manual so far, but the code is mostly
> > documented. You may find a sample session in the repository's home page.
> >
>
> From an End-User POV, the call-chained-functions are "heavy looking”.

Yes, I agree. Keep in mind that the current interface is most likely bound
to be changed.

> So maybe a printf-like API that parses a now-looking math expression
> and replaces its placeholders with the var-arg supplied arguments to the
> function
> would make the "SQL" much more readable.
>
> Since you cannot extend the regular arithmetic expressions in SQLite to act
> on your UDTs
> differently than the built-in processing, that would be the next best thing
> IMHO.

I will think about that!

> All in all, it looks very well done. You're obviously a very competent
> programmer IMHO :).

It looks so, because I stand on the shoulder of giants ;)

> Thanks, --DD
>
> PS: Seems like you're using non-ascii chars in your source code, notably
> the #pragma mark.
>   That's pretty, but asking for trouble IMHO, and likely to cause issues
> for cross-platform support.

Yes, that and a few other things need to be polished. My initial
idea was to keep this project for myself, so I took a liberal approach
style-wise. But it has grown more than I expected...

Thanks for the feedback,
Life.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Warren Young
In reply to this post by Lifepillar-2
On Apr 3, 2019, at 6:30 AM, Lifepillar <[hidden email]> wrote:
>
> By default, the precision is limited to 39 digits and exponents must be
> in the range [-99,999,999,+99,999,999] (for some mathematical
> operations, the exponent must not exceed 99,999 in absolute value).

That’s enough. :)

I once calculated that 30 digits was enough to give a precise location to every particle the size of a grain of sand or larger in the known universe.  You just need to define as many columns in your database as is needed for your physics: 3 for normal 3-space, 4 for spacetime, 10, 11, or 26 for string theory…

9 extra digits gets you down to the “large molecule” scale.

5+ digits worth of negative exponents is enough to get down to the sub-atomic scale.

Put another way, your defaults are already so large that no conceivable physical entity could build a computer big enough to simultaneously contain every distinct state your data type represents.  It’ll do. :)

I did the original calculation because someone was complaining that MySQL’s default — at the time — of 30 digits to the left of the decimal point wasn’t enough for their application.  MySQL’s decimal type then allows another 30 digits to the *right* of the decimal point.  The original poster in that thread remained silent when challenged to divulge what application required more precision than that.

(The current MySQL limit default for the DECIMAL type is 65 total digits, with a configurable number of digits the right of the decimal, up to 30.)

> Also, this is my first public Fossil repository: if you have any
> suggestions on how I should improve its configuration, let me know.

The CRLF line endings in the decNumber directory can cause problems if you ever cause a merge conflict.

Fossil has no problems doing merges on arbitrary data, but when it writes out the conflict-resolution files to help the user manually fix a merge conflict, it does so with the assumption that you’re using LF-only line endings, causing stray CRs in the output.

Unless you really need CRLF for some reason, it’s best to convert those line endings.  That’ll also avoid the need to set the Fossil crlf-glob setting.

> Git mirror (which exists only for testing `fossil git export`...):
>
>    https://github.com/lifepillar/sqlite3decimal-mirror

I wouldn’t say “only.”  If nothing else, it’s advertising for your project, and it’ll make it more likely to show up in web searches.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Warren Young
In reply to this post by Dominique Devienne
On Apr 3, 2019, at 7:05 AM, Dominique Devienne <[hidden email]> wrote:
>
> Seems like you're using non-ascii chars in your source code, notably
> the #pragma mark.

I think you’ve got two nits there, not one.

As for the non-ASCII characters, they’re UTF-8, which is the de facto standard character set on the Internet since around the time of The Bubble.  Ignoring the embedded world, I can’t think of an in-support OS that doesn’t have built-in support for UTF-8.  The only place I’d caution against using such characters is in printf() output and such, and then only because the Windows Console defaults to UTF-16LE.

As for the nonstandard #pragmas, ISO C99 has you covered there.  Thus saith §6.10.6 of #pragmas not of the STDC form: ”Any such pragma that is not recognized by the implementation is ignored.”  In other words, if a compiler that doesn’t understand #pragma mark complains about it or refuses to compile code using it, it’s non-conforming.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Simon Slavin-3
In reply to this post by Warren Young
On 3 Apr 2019, at 6:37pm, Warren Young <[hidden email]> wrote:

> I once calculated that 30 digits was enough to give a precise location to every particle the size of a grain of sand or larger in the known universe.  You just need to define as many columns in your database as is needed for your physics: 3 for normal 3-space, 4 for spacetime, 10, 11, or 26 for string theory…

The first 39 digits of pi allow you to do maths on the size of the universe in units the width of a hydrogen atom.  Let's add one extra digit to deal with rounding errors.  Pi may have more than 40 digits, but the others have no practical use in this universe.

(Yes, you might consider them to be theoretically useful for things like number theory or testing computer components which do certain kinds of maths.)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Warren Young
In reply to this post by Lifepillar-2
On Apr 3, 2019, at 6:30 AM, Lifepillar <[hidden email]> wrote:
>
> does SQLite support indexes on blobs?

It claims to:

$ sqlite3 x.db
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> create table x (a blob);
sqlite> create index xi on x(a);
sqlite> explain query plan select a from x where a=5;
QUERY PLAN
`--SEARCH TABLE x USING COVERING INDEX xi (a=?)
sqlite>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Simon Slavin-3
On 3 Apr 2019, at 6:51pm, Warren Young <[hidden email]> wrote:

> On Apr 3, 2019, at 6:30 AM, Lifepillar <[hidden email]> wrote:
>
>> does SQLite support indexes on blobs?
>
> It claims to:

Indeed.  Be careful to verify whether, from the perspective of your programming language, it considers the first or the last byte to be most significant.  I've seen people caught out by a similar issue.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Joshua Thomas Wise
> [Here, I must thank Dr. Hipp, with whom I had a brief email exchange
> severals moons ago, who convinced me that the IEEE 754 encoding was not
> an ideal storage format for databases]


I’m curious, what were the reasons behind Dr. Hipp’s opinion on this?

> On Apr 3, 2019, at 1:56 PM, Simon Slavin <[hidden email]> wrote:
>
> On 3 Apr 2019, at 6:51pm, Warren Young <[hidden email]> wrote:
>
>> On Apr 3, 2019, at 6:30 AM, Lifepillar <[hidden email]> wrote:
>>
>>> does SQLite support indexes on blobs?
>>
>> It claims to:
>
> Indeed.  Be careful to verify whether, from the perspective of your programming language, it considers the first or the last byte to be most significant.  I've seen people caught out by a similar issue.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Lifepillar-2


> On 3 Apr 2019, at 20:04, Joshua Thomas Wise <[hidden email]> wrote:
>
>> [Here, I must thank Dr. Hipp, with whom I had a brief email exchange
>> severals moons ago, who convinced me that the IEEE 754 encoding was not
>> an ideal storage format for databases]
>
> I’m curious, what were the reasons behind Dr. Hipp’s opinion on this?

At the time of SQLite4, I wrote to him asking why he had dismissed IEEE
754 as a storage format in favor of a custom encoding. His answer was
that he wanted comparisons to be performed using memcmp(), which IEEE
754 does not allow.

There may have been other reasons (complexity, range, ...), but that one
stuck with me and prompted me to start searching for order-preserving
encodings.

Life.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Lifepillar-2
In reply to this post by Warren Young
On 3 Apr 2019, at 19:37, Warren Young <[hidden email]> wrote:
>
> On Apr 3, 2019, at 6:30 AM, Lifepillar <[hidden email]> wrote:
>>
>> By default, the precision is limited to 39 digits and exponents must be
>> in the range [-99,999,999,+99,999,999] (for some mathematical
>> operations, the exponent must not exceed 99,999 in absolute value).
>
> That’s enough. :)

Should you need more, the storage format would not need to be changed!

>> Also, this is my first public Fossil repository: if you have any
>> suggestions on how I should improve its configuration, let me know.
>
> The CRLF line endings in the decNumber directory can cause problems if you ever cause a merge conflict.
>
> Fossil has no problems doing merges on arbitrary data, but when it writes out the conflict-resolution files to help the user manually fix a merge conflict, it does so with the assumption that you’re using LF-only line endings, causing stray CRs in the output.

Thanks, I did not know that.

> Unless you really need CRLF for some reason, it’s best to convert those line endings.  That’ll also avoid the need to set the Fossil crlf-glob setting.

So far I have left line endings as I have found them, but I will
proceed to convert them following your advice.


>> Git mirror (which exists only for testing `fossil git export`...):
>>
>>   https://github.com/lifepillar/sqlite3decimal-mirror
>
> I wouldn’t say “only.”  If nothing else, it’s advertising for your project, and it’ll make it more likely to show up in web searches.

Actually, I aimed at targeted advertising in this mailing list :) There
was some discussion about big numbers in the past, so I thought my
project might be interesting for some people here.

And perhaps I should have added that I welcome feedback at Chisel
(tickets for now: I haven't configured the permissions for the forum
yet), if you wish to discuss the project in more detail.

Life.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Joshua Thomas Wise
In reply to this post by Lifepillar-2
From my naive understanding, memcmp() is used to efficiently compare long strings of bytes. But where in SQLite3 is it necessary to compare long strings of floating point numbers? I, of course, can imagine SQL queries plucking single floating point values from rows or indexes, but I can’t imagine where the long strings would be. Could you enlighten me?


> On Apr 3, 2019, at 3:23 PM, Lifepillar <[hidden email]> wrote:
>
>
>
>> On 3 Apr 2019, at 20:04, Joshua Thomas Wise <[hidden email]> wrote:
>>
>>> [Here, I must thank Dr. Hipp, with whom I had a brief email exchange
>>> severals moons ago, who convinced me that the IEEE 754 encoding was not
>>> an ideal storage format for databases]
>>
>> I’m curious, what were the reasons behind Dr. Hipp’s opinion on this?
>
> At the time of SQLite4, I wrote to him asking why he had dismissed IEEE
> 754 as a storage format in favor of a custom encoding. His answer was
> that he wanted comparisons to be performed using memcmp(), which IEEE
> 754 does not allow.
>
> There may have been other reasons (complexity, range, ...), but that one
> stuck with me and prompted me to start searching for order-preserving
> encodings.
>
> Life.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Richard Hipp-3
On 4/3/19, Joshua Wise <[hidden email]> wrote:
> From my naive understanding, memcmp() is used to efficiently compare long
> strings of bytes. But where in SQLite3 is it necessary to compare long
> strings of floating point numbers? I, of course, can imagine SQL queries
> plucking single floating point values from rows or indexes, but I can’t
> imagine where the long strings would be. Could you enlighten me?

Comparing keys in a btree search uses a lot of CPU cycles.  If the
comparison can be done using memcmp() rather than some custom
function, the comparison goes much faster, which makes searching
btrees faster.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Joshua Thomas Wise
Oh of course, that makes sense. I suppose that means querying on REAL indexes should be slower than querying on INTEGER indexes, in the current SQLite3 implementation? Has a benchmark of this ever been done?


> On Apr 3, 2019, at 5:29 PM, Richard Hipp <[hidden email]> wrote:
>
> On 4/3/19, Joshua Wise <[hidden email]> wrote:
>> From my naive understanding, memcmp() is used to efficiently compare long
>> strings of bytes. But where in SQLite3 is it necessary to compare long
>> strings of floating point numbers? I, of course, can imagine SQL queries
>> plucking single floating point values from rows or indexes, but I can’t
>> imagine where the long strings would be. Could you enlighten me?
>
> Comparing keys in a btree search uses a lot of CPU cycles.  If the
> comparison can be done using memcmp() rather than some custom
> function, the comparison goes much faster, which makes searching
> btrees faster.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Thomas Kurz
In reply to this post by Lifepillar-2
I appreciate your effort towards this extension. In my opinion, however, this is (along with bigint-support) a feature that belongs into core (for that reason alone to get math operations, comparisons, aggregates, etc. working in an intuitive way).

Years ago, for SQLite4, there seem to have been plans for "decimal math". The concept looked very useful and I'd suggest taking that idea up again, but this time for SQLite3 ;-)




----- Original Message -----
From: Lifepillar <[hidden email]>
To: [hidden email] <[hidden email]>
Sent: Wednesday, April 3, 2019, 14:30:52
Subject: [sqlite] ANN: SQLite3 Decimal Extension

[I hope that this kind of announcement is not off-topic here]

SQLite3 Decimal is an extension implementing exact decimal arithmetic
for SQLite3. It is currently unfinished and under development.  At this
point anything, including the user interface and the internals, is
subject to change. I am publishing it early because I am seeking to get
as much feedback as possible to "get it right".

The extension is currently using the excellent decNumber library, but it
does not aim to become an IEEE 754 conforming implementation. It is also
totally unrelated to SQL decimal/numeric types.

Decimals are stored as blobs and the storage format is a minor variant
of decimalInfinite (https://arxiv.org/abs/1506.01598). That is a totally
ordered encoding, so decimals can be compared directly (memcmp()) and
also indexed (does SQLite support indexes on blobs?).

[Here, I must thank Dr. Hipp, with whom I had a brief email exchange
severals moons ago, who convinced me that the IEEE 754 encoding was not
an ideal storage format for databases]

By default, the precision is limited to 39 digits and exponents must be
in the range [-99,999,999,+99,999,999] (for some mathematical
operations, the exponent must not exceed 99,999 in absolute value). Such
parameters may be configured at compile time and also changed at
runtime. Any integer or fractional number satisfying such requirements
can be manipulated. Note that the on-disk representation is *not*
subject to such limits and can accommodate arbitrarily small or
arbitrarily large decimals. With the defaults just mentioned, a decimal
occupies between 1 and 24 bytes on disk (plus any overhead that blobs
may add).

As I have said, I welcome any feedback, from the super-technical to the
end-user oriented. There is no manual so far, but the code is mostly
documented. You may find a sample session in the repository's home page.

Also, this is my first public Fossil repository: if you have any
suggestions on how I should improve its configuration, let me know.

So, here for the adventurous ones:

Official repository:

    https://chiselapp.com/user/lifepillar/repository/sqlite3decimal

Git mirror (which exists only for testing `fossil git export`...):

    https://github.com/lifepillar/sqlite3decimal-mirror

Enjoy,
Life.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Lifepillar-2
On 4 Apr 2019, at 10:37, Thomas Kurz <[hidden email]> wrote:
>
> I appreciate your effort towards this extension. In my opinion, however, this is (along with bigint-support) a feature that belongs into core (for that reason alone to get math operations, comparisons, aggregates, etc. working in an intuitive way).
>
> Years ago, for SQLite4, there seem to have been plans for "decimal math". The concept looked very useful and I'd suggest taking that idea up again, but this time for SQLite3 ;-)

You raise an interesting point. From a purely technical perspective, you are absolutely right: ui and performance would only benefit from full integration into SQLite. But an extension may be maintained independent of the main project’s goals, constraints, people and directions. Patching SQLite requires, IMO, coordination with SQLite’s developers. AFAIK, SQLite4 is a dead end: is there still any interest, or maybe a plan, for “decimal math” in the core?

Life.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

James K. Lowden
In reply to this post by Richard Hipp-3
On Wed, 3 Apr 2019 17:29:47 -0400
Richard Hipp <[hidden email]> wrote:

> On 4/3/19, Joshua Wise <[hidden email]> wrote:
> > From my naive understanding, memcmp() is used to efficiently
> > compare long strings of bytes. But where in SQLite3 is it necessary
> > to compare long strings of floating point numbers? I, of course,
> > can imagine SQL queries plucking single floating point values from
> > rows or indexes, but I can?t imagine where the long strings would
> > be. Could you enlighten me?
>
> Comparing keys in a btree search uses a lot of CPU cycles.  If the
> comparison can be done using memcmp() rather than some custom
> function, the comparison goes much faster, which makes searching
> btrees faster.

On the other hand, what table has a floating point number in its key?  

How do you even express the value of such a key for an exact match?  

There is also a significant cost of converting to and from IEEE
format.  In my experience bulk-loading quantitative databases, I
encountered many occasions in which parsing the input accounted for 50%
of the computation.  The limit wasn't network bandwidth or server
speed, it was data conversion.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

James K. Lowden
In reply to this post by Warren Young
On Wed, 3 Apr 2019 11:37:47 -0600
Warren Young <[hidden email]> wrote:

> Put another way, your defaults are already so large that no
> conceivable physical entity could build a computer big enough to
> simultaneously contain every distinct state your data type represents.

Exactly (as it were).  

Physical entities cannot be measured to more than 6 orders of
magnitude.  Finer than that, error takes over.  

What is the distance from New York to Los Angeles?  

We know it's 3,944 km.  Do we know it's 3,944,000 meters?  3,944,000,000
mm? We do not.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

James K. Lowden
In reply to this post by Lifepillar-2
On Wed, 3 Apr 2019 14:30:52 +0200
Lifepillar <[hidden email]> wrote:


> SQLite3 Decimal is an extension implementing exact decimal arithmetic
> for SQLite3. It is currently unfinished and under development.  
...
> I welcome any feedback, from the super-technical to the
> end-user oriented. There is no manual so far, but the code is mostly
> documented.

What does divide-by-zero yield?  

If NULL, no amount of exactitude will matter.  If the library is based
on math, on the other hand, that would be a boon to SQLite users.  

--jkl


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ANN: SQLite3 Decimal Extension

Joshua Thomas Wise
In reply to this post by James K. Lowden
> On the other hand, what table has a floating point number in its key?  
>
> How do you even express the value of such a key for an exact match?  

Well I imagine it can be very useful for range queries. Imagine Julian dates, coordinate points, rankings, etc.

I suppose in the most common case, parsing the on-disk format to IEEE would only be necessary on a small number of rows, after the range query has already used memcmp() to find the rows it cares about. But yeah, in the case of bulk loading it’s probably only a loss.

> On Apr 4, 2019, at 11:15 AM, James K. Lowden <[hidden email]> wrote:
>
> On Wed, 3 Apr 2019 17:29:47 -0400
> Richard Hipp <[hidden email]> wrote:
>
>> On 4/3/19, Joshua Wise <[hidden email]> wrote:
>>> From my naive understanding, memcmp() is used to efficiently
>>> compare long strings of bytes. But where in SQLite3 is it necessary
>>> to compare long strings of floating point numbers? I, of course,
>>> can imagine SQL queries plucking single floating point values from
>>> rows or indexes, but I can?t imagine where the long strings would
>>> be. Could you enlighten me?
>>
>> Comparing keys in a btree search uses a lot of CPU cycles.  If the
>> comparison can be done using memcmp() rather than some custom
>> function, the comparison goes much faster, which makes searching
>> btrees faster.
>
> On the other hand, what table has a floating point number in its key?  
>
> How do you even express the value of such a key for an exact match?  
>
> There is also a significant cost of converting to and from IEEE
> format.  In my experience bulk-loading quantitative databases, I
> encountered many occasions in which parsing the input accounted for 50%
> of the computation.  The limit wasn't network bandwidth or server
> speed, it was data conversion.  
>
> --jkl
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12