Re: [sqlite] Lack of "decimal" support

[indraneel_in]

I too faced a similar situation..

the following thing worked

set the Column type to Double
and insert rows using Double data type for java..





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Lack-of-decimal-support-tp57530p74046.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Simon Slavin]

On 28 Mar 2011, at 5:09am, Nico Williams wrote:

> On Mar 27, 2011 10:20 PM, "Darren Duncan"  wrote:
>> 
>> 
>> SQLite already has Blobs, and I see those as being exactly the same thing
> 
> Blobs are _octet_ strings.  Sure, one could write functions that encode and
> manipulate bit strings as blobs were the first octet (or three bits from it)
> encodes the remainder of the bit string length div 8.

Why bother ?  It'd either be slow or involve a long lookup table.  Just use the 
ASCII codes for '0' and '1' as your octets.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Nico Williams]

On Mar 27, 2011 10:20 PM, "Darren Duncan"  wrote:
>
> Nico Williams wrote:
> > User defined types.  There are two types in particular that I'd like
> > to see added:
> >
> >  - Bit strings.  Bit strings are like character strings, but the
> > elements can only be bits.  The key is that bit string length matters
> > when it comes to collation (000 sorts before ).  Related
> > sub-types: IP (v4 and 6) addresses (e.g., 10/8 is easily represented
> > as an eight bit long bit string: 1010, while 10.1.2.3.4 is easily
> > represented as a 32 bit long bit string).
>
> SQLite already has Blobs, and I see those as being exactly the same thing,
which
> is a string of bits.  Maybe you're just wanting more operators so it is
easier
> to introspect or manipulate them?

Blobs are _octet_ strings.  Sure, one could write functions that encode and
manipulate bit strings as blobs were the first octet (or three bits from it)
encodes the remainder of the bit string length div 8.   (SQLite3 would have
to do something similar anyways.  The difference lies in syntax.)

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Nico Williams]

On Sun, Mar 27, 2011 at 10:20 PM, Darren Duncan  wrote:
> Nico Williams wrote:
>> User defined types.  There are two types in particular that I'd like
>> to see added:
>>
>>  - Bit strings.  Bit strings are like character strings, but the
>> elements can only be bits.  The key is that bit string length matters
>> when it comes to collation (000 sorts before ).  Related
>> sub-types: IP (v4 and 6) addresses (e.g., 10/8 is easily represented
>> as an eight bit long bit string: 1010, while 10.1.2.3.4 is easily
>> represented as a 32 bit long bit string).
>
> SQLite already has Blobs, and I see those as being exactly the same thing, 
> which
> is a string of bits.  Maybe you're just wanting more operators so it is easier
> to introspect or manipulate them? -- Darren Duncan

Blobs are _octet_ strings.  Sure, one could write functions that
encode and manipulate bit strings as blobs were the first octet (or
three bits from it) encodes the remainder of the bit string length div
8.   (SQLite3 would have to do something similar anyways.  The
difference lies in syntax.)

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Darren Duncan]

Nico Williams wrote:
> User defined types.  There are two types in particular that I'd like
> to see added:
> 
>  - Bit strings.  Bit strings are like character strings, but the
> elements can only be bits.  The key is that bit string length matters
> when it comes to collation (000 sorts before ).  Related
> sub-types: IP (v4 and 6) addresses (e.g., 10/8 is easily represented
> as an eight bit long bit string: 1010, while 10.1.2.3.4 is easily
> represented as a 32 bit long bit string).

SQLite already has Blobs, and I see those as being exactly the same thing, 
which 
is a string of bits.  Maybe you're just wanting more operators so it is easier 
to introspect or manipulate them? -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Nico Williams]

On Sun, Mar 27, 2011 at 11:33 AM, jeff archer <jarch...@yahoo.com> wrote:
>>From: Patrick Earl <pate...@patearl.net>
>>Subject: Re: [sqlite] Lack of "decimal" support
>>
>>If SQLite can't decide on a base-10 format itself, perhaps the answer
>>lies in enhancing the API to allow for custom type storage and
>>operators.
>
> So, like a virtual type interface.  This would be a nice feature.

User defined types.  There are two types in particular that I'd like
to see added:

 - Bit strings.  Bit strings are like character strings, but the
elements can only be bits.  The key is that bit string length matters
when it comes to collation (000 sorts before ).  Related
sub-types: IP (v4 and 6) addresses (e.g., 10/8 is easily represented
as an eight bit long bit string: 1010, while 10.1.2.3.4 is easily
represented as a 32 bit long bit string).

 - Bignums.  64-bit signed integers are not enough :(

However, adding user-defined types must not be easy, since, among
other things, it means making the lexer run-time pluggable so it can
recognize constant value tokens for the new type (unless one is
willing to rebuild SQLite every time one adds a type).

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Simon Slavin]

On 27 Mar 2011, at 7:17pm, Darren Duncan wrote:

> Simon Slavin wrote:
>> I forgot a bunch of functions.  You need to be able to do comparisons, so you
>> can determine whether one decimal is greater, less or equal to another.  And
>> users will want abs(), max(), min(), round(), avg(), sum(), and total().
> 
> What is this "total" function you speak of and how does it differ from "sum"?

http://www.sqlite.org/lang_aggfunc.html

> Also, it's better to call a function "mean" than "avg" if that's what is 
> intended, since there are other kinds of averages like "median" and "mode".

Sorry, it's in the Standard that way.  See, for example,

http://www.postgresql.org/docs/9.0/static/functions-aggregate.html

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Darren Duncan]

Simon Slavin wrote:
> I forgot a bunch of functions.  You need to be able to do comparisons, so you
> can determine whether one decimal is greater, less or equal to another.  And
> users will want abs(), max(), min(), round(), avg(), sum(), and total().

What is this "total" function you speak of and how does it differ from "sum"?

Also, it's better to call a function "mean" than "avg" if that's what is 
intended, since there are other kinds of averages like "median" and "mode".

-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[jeff archer]

>From: Patrick Earl <pate...@patearl.net>
>Subject: Re: [sqlite] Lack of "decimal" support
>
>If SQLite can't decide on a base-10 format itself, perhaps the answer
>lies in enhancing the API to allow for custom type storage and
>operators.
>
 
So, like a virtual type interface.  This would be a nice feature.  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Simon Slavin]

On 27 Mar 2011, at 11:49am, Simon Slavin wrote:

> decimal + decimal --> decimal
> decimal - decimal --> decimal
> decimal * real --> decimal
> decimal / real --> decimal
> decimal / decimal --> real

I forgot a bunch of functions.  You need to be able to do comparisons, so you 
can determine whether one decimal is greater, less or equal to another.  And 
users will want abs(), max(), min(), round(), avg(), sum(), and total().

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Jean-Christophe Deschamps]

>This page has a lot of info about
>Decimal Number support, including
>a set of libraries:
>
>http://speleotrove.com/decimal/

Yes!  IBM and Intel are two of the big names having done significant 
work in this direction and made research and/or results publicly available.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Jean-Christophe Deschamps]

>There are many cases where people are doing calculations or using 
>numbers expecting them to retain all digits.  This would allow the BCD 
>type to be used for that if they really need it.

Currency conversions (rarely exact!) or tax (or margin) calculations 
come to mind as very common uses requiring a bit more than plus and 
minus operations with extended integers support and expected to be 
available at the SQL level along with correct rounding.

It's true that embedding anything like full GNU GMP would make "lite" a 
joke, but a decent subset and something like decimal(n,p) type would 
certainly be beneficial for a large number of SLite users.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Konrad J Hambrick]

All --

This page has a lot of info about
Decimal Number support, including
a set of libraries:

http://speleotrove.com/decimal/

-- kjh


Black, Michael (IS) wrote, On 03/27/2011 06:29 AM:
> Base 10 multiplication is needed.  Although money is the main reason for 
> doing BCD due to cumulative errors it's not the only reason.  So I'd 
> recommend adding decimal*decimal ->  decimal just to be complete.  It's easy 
> enough to implement using the + function so could just be noted as "slow".
>
> There are many cases where people are doing calculations or using numbers 
> expecting them to retain all digits.  This would allow the BCD type to be 
> used for that if they really need it.
>
> Michael D. Black
> Senior Scientist
> NG Information Systems
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Simon Slavin [slav...@bigfraud.org]
> Sent: Sunday, March 27, 2011 5:49 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Lack of "decimal" support
>
> For those who are scared by this, there really isn't that much to do.  Let 
> us, for the same of argument, say we're going to allow decimal values to an 
> arbitrary length: any number of digits, possibly with a decimal point 
> somewhere along the string, possibly starting with a minus sign.
>
> The major choice is how the numbers are going to be stored.  The two 
> conventional ways are with one digit per byte (either using ASCII 0 == 0x30 
> or with 0 == 0x00), or with two digits per byte, often known as Binary-Coded 
> Decimal.  The first option makes for faster input, output, easier reading of 
> file formats, and simpler programming and debugging.  The second makes for a 
> smaller database file and speeds up some calculations using tables.  You 
> generally turn the minus sign into a bit flag.
>
> Once you have decided that you'll need conversion routines.  These will 
> either be to-and-from REAL or to-and-from TEXT.
>
> The other things to be implemented are the maths routines.  There are really 
> only five of these to worry about since, as someone pointed out upthread, one 
> does not multiply two amounts of money together.  There are other things you 
> don't do either: you don't add a decimal to a real, nor raise decimals to 
> powers (the log of a decimal value doesn't mean much).  So you need
>
> decimal + decimal -->  decimal
> decimal - decimal -->  decimal
> decimal * real -->  decimal
> decimal / real -->  decimal
> decimal / decimal -->  real
>
> The first four are slower than adding reals together.  The last one is tricky 
> and usually dealt with by turning the decimals into reals before division 
> (since the answer will be a real anyhow, this doesn't lose much precision).  
> The same tactic can be used if the programmer asks for anything weird (e.g. 
> decimal + real) though those normally indicate that the programmer made a 
> programming error and some compilers flag them with warnings.
>
> That's pretty-much all there is to it.  It doesn't represent huge growth to 
> the SQLite codebase, or even of the SQLite compiled code, though it will stop 
> database files which use the format from being backward-compatible, and it 
> will add a lot of tests to the test suite.  I don't know if it could be added 
> as a compilation option or not: you need someone who understands how SQLite 
> is built.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Black, Michael (IS)]

Base 10 multiplication is needed.  Although money is the main reason for doing 
BCD due to cumulative errors it's not the only reason.  So I'd recommend adding 
decimal*decimal -> decimal just to be complete.  It's easy enough to implement 
using the + function so could just be noted as "slow".

There are many cases where people are doing calculations or using numbers 
expecting them to retain all digits.  This would allow the BCD type to be used 
for that if they really need it.

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Sunday, March 27, 2011 5:49 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Lack of "decimal" support

For those who are scared by this, there really isn't that much to do.  Let us, 
for the same of argument, say we're going to allow decimal values to an 
arbitrary length: any number of digits, possibly with a decimal point somewhere 
along the string, possibly starting with a minus sign.

The major choice is how the numbers are going to be stored.  The two 
conventional ways are with one digit per byte (either using ASCII 0 == 0x30 or 
with 0 == 0x00), or with two digits per byte, often known as Binary-Coded 
Decimal.  The first option makes for faster input, output, easier reading of 
file formats, and simpler programming and debugging.  The second makes for a 
smaller database file and speeds up some calculations using tables.  You 
generally turn the minus sign into a bit flag.

Once you have decided that you'll need conversion routines.  These will either 
be to-and-from REAL or to-and-from TEXT.

The other things to be implemented are the maths routines.  There are really 
only five of these to worry about since, as someone pointed out upthread, one 
does not multiply two amounts of money together.  There are other things you 
don't do either: you don't add a decimal to a real, nor raise decimals to 
powers (the log of a decimal value doesn't mean much).  So you need

decimal + decimal --> decimal
decimal - decimal --> decimal
decimal * real --> decimal
decimal / real --> decimal
decimal / decimal --> real

The first four are slower than adding reals together.  The last one is tricky 
and usually dealt with by turning the decimals into reals before division 
(since the answer will be a real anyhow, this doesn't lose much precision).  
The same tactic can be used if the programmer asks for anything weird (e.g. 
decimal + real) though those normally indicate that the programmer made a 
programming error and some compilers flag them with warnings.

That's pretty-much all there is to it.  It doesn't represent huge growth to the 
SQLite codebase, or even of the SQLite compiled code, though it will stop 
database files which use the format from being backward-compatible, and it will 
add a lot of tests to the test suite.  I don't know if it could be added as a 
compilation option or not: you need someone who understands how SQLite is built.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Simon Slavin]

For those who are scared by this, there really isn't that much to do.  Let us, 
for the same of argument, say we're going to allow decimal values to an 
arbitrary length: any number of digits, possibly with a decimal point somewhere 
along the string, possibly starting with a minus sign.

The major choice is how the numbers are going to be stored.  The two 
conventional ways are with one digit per byte (either using ASCII 0 == 0x30 or 
with 0 == 0x00), or with two digits per byte, often known as Binary-Coded 
Decimal.  The first option makes for faster input, output, easier reading of 
file formats, and simpler programming and debugging.  The second makes for a 
smaller database file and speeds up some calculations using tables.  You 
generally turn the minus sign into a bit flag.

Once you have decided that you'll need conversion routines.  These will either 
be to-and-from REAL or to-and-from TEXT.

The other things to be implemented are the maths routines.  There are really 
only five of these to worry about since, as someone pointed out upthread, one 
does not multiply two amounts of money together.  There are other things you 
don't do either: you don't add a decimal to a real, nor raise decimals to 
powers (the log of a decimal value doesn't mean much).  So you need

decimal + decimal --> decimal
decimal - decimal --> decimal
decimal * real --> decimal
decimal / real --> decimal
decimal / decimal --> real

The first four are slower than adding reals together.  The last one is tricky 
and usually dealt with by turning the decimals into reals before division 
(since the answer will be a real anyhow, this doesn't lose much precision).  
The same tactic can be used if the programmer asks for anything weird (e.g. 
decimal + real) though those normally indicate that the programmer made a 
programming error and some compilers flag them with warnings.

That's pretty-much all there is to it.  It doesn't represent huge growth to the 
SQLite codebase, or even of the SQLite compiled code, though it will stop 
database files which use the format from being backward-compatible, and it will 
add a lot of tests to the test suite.  I don't know if it could be added as a 
compilation option or not: you need someone who understands how SQLite is built.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[TR Shaw]

On Mar 26, 2011, at 10:15 PM, BareFeetWare wrote:

> On 27/03/2011, at 12:39 PM, Patrick Earl wrote:
> 
>> Base-10 numbers are frequently used in financial calculations because
>> of their exact nature.  SQLite forces us to store decimal numbers as
>> text to ensure precision is not lost.  Unfortunately, this prevents
>> even simple operations such as retrieving all rows where an employee's
>> salary is greater than '100' (coded as a string since decimal types
>> are stored as strings).
> 
> Can you store all money amounts as integers, as the cents value? That is 
> exact, searchable etc.
> 

No US National debt is over that :-(

Tom


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[BareFeetWare]

On 27/03/2011, at 3:04 PM, Patrick Earl wrote:

> If you use a view to return a double, you've lost the exact value you were 
> trying to save by storing the decimal as a text value.

I'm not suggesting storing as a text value. I'm suggesting storing as an 
integer and only converting to a float (or, I guess a text value is possible 
too) for display purposes.

> If you continue to work with it as an integer, it's exact, but that requires 
> continual awareness of the number of decimal places at any point in time.

If we're talking about money amounts, isn't the number of decimal places always 
two? ie an amount stored as an integer as 12345 means 12345 cents means 123.45 
dollars.

> In essence, you have to build significant numeric infrastructure into your 
> program to emulate the missing numeric infrastructure in SQLite.

Perhaps I'm missing something of your requirement. I use integer storage for 
exact lossless money amounts.

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Darren Duncan]

Patrick Earl wrote:
> On Sat, Mar 26, 2011 at 10:03 PM, Darren Duncan  
> wrote:
>> You could store your exact precision numbers as a pair of integers 
>> representing
>> a numerator/denominator ratio and then have math operators that work on these
>> pairs like they were one number.  You would then know at the end how to move 
>> the
>> radix point since that was kept track of along with the number. -- Darren 
>> Duncan
> 
> If you did this, you wouldn't be able to compare numbers in the
> database without resorting to division.

Sure you can.  You make sure the two operands have the same denominator and 
then 
compare the numerators.  Or you resort to multiplication, as they taught in 
grade school (dividing by a fraction is the same as multiplying by its 
inverse). 
  Everything is just integers.

If your normal operations are just straight-up addition/subtraction and 
multiplication and all your operands have the same radix (are in base 10), then 
your results are all guaranteed to be in base-10 as well, since any 
denominators 
in results would be positive powers of 10.  Likewise if you're doing division 
but you ensure that any divisor is a power of 10.

> If you just specified how
> many fixed decimal places there were, you could zero-pad strings if
> you only needed to perform comparison operations.  Obviously you'd
> need to create custom operations, as you suggest, for other math
> operators.

We should be able to avoid strings with this entirely.

> If SQLite can't decide on a base-10 format itself, perhaps the answer
> lies in enhancing the API to allow for custom type storage and
> operators.

-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Patrick Earl]

On Sat, Mar 26, 2011 at 10:03 PM, Darren Duncan  wrote:
> You could store your exact precision numbers as a pair of integers 
> representing
> a numerator/denominator ratio and then have math operators that work on these
> pairs like they were one number.  You would then know at the end how to move 
> the
> radix point since that was kept track of along with the number. -- Darren 
> Duncan

If you did this, you wouldn't be able to compare numbers in the
database without resorting to division.  If you just specified how
many fixed decimal places there were, you could zero-pad strings if
you only needed to perform comparison operations.  Obviously you'd
need to create custom operations, as you suggest, for other math
operators.

If SQLite can't decide on a base-10 format itself, perhaps the answer
lies in enhancing the API to allow for custom type storage and
operators.

 Patrick Earl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Patrick Earl]

If you use a view to return a double, you've lost the exact value you
were trying to save by storing the decimal as a text value.  If you
continue to work with it as an integer, it's exact, but that requires
continual awareness of the number of decimal places at any point in
time.  In essence, you have to build significant numeric
infrastructure into your program to emulate the missing numeric
infrastructure in SQLite.

Patrick Earl

On Sat, Mar 26, 2011 at 9:52 PM, BareFeetWare  wrote:
> On 27/03/2011, at 2:09 PM, Patrick Earl wrote:
>
>> if you're in a context where you don't have significant understanding of the 
>> user's query, how do you determine if 1.05 is $1.05 or 105%?
>
> Can you give us a bit more background and an example of this?
>
> How is the interface for the query represented to the user and what can they 
> enter there to create a query?
>
> You can probably do this fairly easily via views which display data in a 
> particular format for the user to see or create a query.
>
> Tom
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
>  --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Darren Duncan]

Patrick Earl wrote:
> That is true, but then when you are formulating generic queries within
> a place such as an ORM like NHibernate, you would need to figure out
> when to translate the user's "100" into "1".  As well, if you
> multiplied numbers, you'd need to re-scale the result.  For example,
> (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :(  If one
> wanted to get excessively complicated, they could implement a series
> of user functions that perform decimal operations using strings and
> then reformulate queries to replace + with decimal_add(x,y).  That
> said, it'd be so much nicer if there was just native support for
> base-10 numbers. :)

You could store your exact precision numbers as a pair of integers representing 
a numerator/denominator ratio and then have math operators that work on these 
pairs like they were one number.  You would then know at the end how to move 
the 
radix point since that was kept track of along with the number. -- Darren Duncan

> On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare  
> wrote:
>> On 27/03/2011, at 12:39 PM, Patrick Earl wrote:
>>
>>> Base-10 numbers are frequently used in financial calculations because
>>> of their exact nature.  SQLite forces us to store decimal numbers as
>>> text to ensure precision is not lost.  Unfortunately, this prevents
>>> even simple operations such as retrieving all rows where an employee's
>>> salary is greater than '100' (coded as a string since decimal types
>>> are stored as strings).
>> Can you store all money amounts as integers, as the cents value? That is 
>> exact, searchable etc.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[BareFeetWare]

On 27/03/2011, at 2:09 PM, Patrick Earl wrote:

> if you're in a context where you don't have significant understanding of the 
> user's query, how do you determine if 1.05 is $1.05 or 105%?

Can you give us a bit more background and an example of this?

How is the interface for the query represented to the user and what can they 
enter there to create a query?

You can probably do this fairly easily via views which display data in a 
particular format for the user to see or create a query.

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Patrick Earl]

You're right, it doesn't make sens to multiply dollars, but if you're
in a context where you don't have significant understanding of the
user's query, how do you determine if 1.05 is $1.05 or 105%?

I understand that one can custom-code everything for SQLite and get
reasonable results in some cases, but please understand that I'm
looking for solutions that don't require the framework to understand
the user's intentions any more than "I want to work with base-10
numbers up to a certain precision/scale."

  Patrick Earl

On Sat, Mar 26, 2011 at 8:43 PM, Gerry Snyder  wrote:
> Do money values really get multiplied together?
>
> What is the meaning of square cents as a unit?
>
> Gerry
>
> On 3/26/11, Patrick Earl  wrote:
>> That is true, but then when you are formulating generic queries within
>> a place such as an ORM like NHibernate, you would need to figure out
>> when to translate the user's "100" into "1".  As well, if you
>> multiplied numbers, you'd need to re-scale the result.  For example,
>> (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :(  If one
>> wanted to get excessively complicated, they could implement a series
>> of user functions that perform decimal operations using strings and
>> then reformulate queries to replace + with decimal_add(x,y).  That
>> said, it'd be so much nicer if there was just native support for
>> base-10 numbers. :)
>>
>>        Patrick Earl
>>
>> On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare 
>> wrote:
>>> On 27/03/2011, at 12:39 PM, Patrick Earl wrote:
>>>
 Base-10 numbers are frequently used in financial calculations because
 of their exact nature.  SQLite forces us to store decimal numbers as
 text to ensure precision is not lost.  Unfortunately, this prevents
 even simple operations such as retrieving all rows where an employee's
 salary is greater than '100' (coded as a string since decimal types
 are stored as strings).
>>>
>>> Can you store all money amounts as integers, as the cents value? That is
>>> exact, searchable etc.
>>>
>>> Thanks,
>>> Tom
>>> BareFeetWare
>>>
>>> --
>>> iPhone/iPad/iPod and Mac software development, specialising in databases
>>> develo...@barefeetware.com
>>>  --
>>> Comparison of SQLite GUI tools:
>>> http://www.barefeetware.com/sqlite/compare/?ml
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
> Sent from my mobile device
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[BareFeetWare]

> On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare  
> wrote:
>> Can you store all money amounts as integers, as the cents value? That is 
>> exact, searchable etc.

On 27/03/2011, at 1:27 PM, Patrick Earl wrote:

> That is true, but then when you are formulating generic queries within
> a place such as an ORM like NHibernate, you would need to figure out
> when to translate the user's "100" into "1".

You can keep all internal transactions as integers, so there are no float 
rounding errors. You only have to translate the final figures if you want to 
display to the user as dollars. You can do this in selects or use views to 
convert the data if needed. For instance:

create table Staff
(   ID integer primary key not null
,   Name text collate nocase not null
,   Salary integer -- in cents
)
;
create view "Staff Dollars"
as
select
ID
,   Name
,   round(Salary/ 100.0, 2) as Salary
from "Staff"
;

> As well, if you multiplied numbers, you'd need to re-scale the result.  For 
> example, (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :(

I can't think of any reason for multiplying two money amounts. You would only 
ever multiple a money amount by a plane number, so you only ever have to /100 
if you want to present your final answer in dollars. I do this for invoice 
totals, tax return calculations and similar.

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Gerry Snyder]

Do money values really get multiplied together?

What is the meaning of square cents as a unit?

Gerry

On 3/26/11, Patrick Earl  wrote:
> That is true, but then when you are formulating generic queries within
> a place such as an ORM like NHibernate, you would need to figure out
> when to translate the user's "100" into "1".  As well, if you
> multiplied numbers, you'd need to re-scale the result.  For example,
> (1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :(  If one
> wanted to get excessively complicated, they could implement a series
> of user functions that perform decimal operations using strings and
> then reformulate queries to replace + with decimal_add(x,y).  That
> said, it'd be so much nicer if there was just native support for
> base-10 numbers. :)
>
>Patrick Earl
>
> On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare 
> wrote:
>> On 27/03/2011, at 12:39 PM, Patrick Earl wrote:
>>
>>> Base-10 numbers are frequently used in financial calculations because
>>> of their exact nature.  SQLite forces us to store decimal numbers as
>>> text to ensure precision is not lost.  Unfortunately, this prevents
>>> even simple operations such as retrieving all rows where an employee's
>>> salary is greater than '100' (coded as a string since decimal types
>>> are stored as strings).
>>
>> Can you store all money amounts as integers, as the cents value? That is
>> exact, searchable etc.
>>
>> Thanks,
>> Tom
>> BareFeetWare
>>
>> --
>> iPhone/iPad/iPod and Mac software development, specialising in databases
>> develo...@barefeetware.com
>>  --
>> Comparison of SQLite GUI tools:
>> http://www.barefeetware.com/sqlite/compare/?ml
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
Sent from my mobile device
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Patrick Earl]

That is true, but then when you are formulating generic queries within
a place such as an ORM like NHibernate, you would need to figure out
when to translate the user's "100" into "1".  As well, if you
multiplied numbers, you'd need to re-scale the result.  For example,
(1 * 1) would be (100 * 100 = 1), which is 1 * 1 = 100. :(  If one
wanted to get excessively complicated, they could implement a series
of user functions that perform decimal operations using strings and
then reformulate queries to replace + with decimal_add(x,y).  That
said, it'd be so much nicer if there was just native support for
base-10 numbers. :)

   Patrick Earl

On Sat, Mar 26, 2011 at 8:15 PM, BareFeetWare  wrote:
> On 27/03/2011, at 12:39 PM, Patrick Earl wrote:
>
>> Base-10 numbers are frequently used in financial calculations because
>> of their exact nature.  SQLite forces us to store decimal numbers as
>> text to ensure precision is not lost.  Unfortunately, this prevents
>> even simple operations such as retrieving all rows where an employee's
>> salary is greater than '100' (coded as a string since decimal types
>> are stored as strings).
>
> Can you store all money amounts as integers, as the cents value? That is 
> exact, searchable etc.
>
> Thanks,
> Tom
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
>  --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[BareFeetWare]

On 27/03/2011, at 12:39 PM, Patrick Earl wrote:

> Base-10 numbers are frequently used in financial calculations because
> of their exact nature.  SQLite forces us to store decimal numbers as
> text to ensure precision is not lost.  Unfortunately, this prevents
> even simple operations such as retrieving all rows where an employee's
> salary is greater than '100' (coded as a string since decimal types
> are stored as strings).

Can you store all money amounts as integers, as the cents value? That is exact, 
searchable etc.

Thanks,
Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Patrick Earl]

I've found the decimal numbers to be most generally useful in narrow
ranges.  For reference, here are a couple notes on how other databases
implement them:

MSSQL stores up to 38 digits in 17 bytes, with a specific precision.
http://msdn.microsoft.com/en-us/library/aa258832(v=sql.80).aspx

PostgreSQL is more flexible and supports up to 1000 digits.

http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

In order to get a jump on the implementation, I would suggest that it
might be possible to use C routines from the PostgreSQL project or
some appropriately licensed library.  Perhaps an author from a numeric
library would be willing to donate their work to the SQLite project.

 Patrick Earl

On Sat, Mar 26, 2011 at 7:43 PM, Simon Slavin  wrote:
>
> On 27 Mar 2011, at 2:39am, Patrick Earl wrote:
>
>> Base-10 numbers are frequently used in financial calculations because
>> of their exact nature.  SQLite forces us to store decimal numbers as
>> text to ensure precision is not lost.  Unfortunately, this prevents
>> even simple operations such as retrieving all rows where an employee's
>> salary is greater than '100' (coded as a string since decimal types
>> are stored as strings).
>>
>> I would like to encourage the developers to consider adding support
>> for base-10 numbers.  This is clearly a very pertinent issue, as even
>> this month there was another thread regarding decimal support.
>
> Intersting idea.  You will need to develop your own C routines to do 
> calculations with decimals.  Do you feel they should be implemented at a 
> fixed length or would you want to be able to use decimal strings of arbitrary 
> lengths ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lack of "decimal" support

[Simon Slavin]

On 27 Mar 2011, at 2:39am, Patrick Earl wrote:

> Base-10 numbers are frequently used in financial calculations because
> of their exact nature.  SQLite forces us to store decimal numbers as
> text to ensure precision is not lost.  Unfortunately, this prevents
> even simple operations such as retrieving all rows where an employee's
> salary is greater than '100' (coded as a string since decimal types
> are stored as strings).
> 
> I would like to encourage the developers to consider adding support
> for base-10 numbers.  This is clearly a very pertinent issue, as even
> this month there was another thread regarding decimal support.

Intersting idea.  You will need to develop your own C routines to do 
calculations with decimals.  Do you feel they should be implemented at a fixed 
length or would you want to be able to use decimal strings of arbitrary lengths 
?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Lack of "decimal" support

[Patrick Earl]

Base-10 numbers are frequently used in financial calculations because
of their exact nature.  SQLite forces us to store decimal numbers as
text to ensure precision is not lost.  Unfortunately, this prevents
even simple operations such as retrieving all rows where an employee's
salary is greater than '100' (coded as a string since decimal types
are stored as strings).

I would like to encourage the developers to consider adding support
for base-10 numbers.  This is clearly a very pertinent issue, as even
this month there was another thread regarding decimal support.

Thanks for your consideration.

   Patrick Earl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users