decimal type in web2py

[DenesL]

Thinking about adding a decimal type to web2py.

Syntax:
Field('x','decimal',length=10,digits=2)

defaults should be based on DB back-end or just 10,2 for example
(easier)?

ideal IS_DEC_IN_RANGE validator should also be based on DB back-end,
definite values are easier but would limit values for otherwise
limitless POSTGRESQL values.

pros/cons?


Summary of DB back-end info on decimal support

p=precision, s=scale, [means optional]

---DB---- -----syntax----- --limits--- --dft--
MSSQL2005 decimal[(p[,s])] 0<=s<=p<=38 18,0
DB2/400 " 1<=s<=p<=31 5,0

MYSQL
spec: For DECIMAL(M,D), the maximum M is 65
create: CREATE TABLE foo( bar decimal(10,2) )

POSTGRESQL
spec: DECIMAL(M,D), exact, no limit
create: CREATE TABLE foo( bar decimal(10,2) )

SQLite : no

FIREBIRD
spec: For DECIMAL(P,S), Precision must be from 1 to 18. Precision >=
Scale
create: CREATE TABLE foo( bar decimal(10,4) ) i.e. This allows:
pppppp.ssss

Denes

[Douglas Soares de Andrade]

Em Thu, 12 Nov 2009 04:10:46 -0800 (PST)
DenesL <dene...@yahoo.ca> escreveu:

> Thinking about adding a decimal type to web2py.
>
> Syntax:
> Field('x','decimal',length=10,digits=2)
>
> defaults should be based on DB back-end or just 10,2 for example
> (easier)?
>
> ideal IS_DEC_IN_RANGE validator should also be based on DB back-end,
> definite values are easier but would limit values for otherwise
> limitless POSTGRESQL values.
>
> pros/cons?
>
>
> Summary of DB back-end info on decimal support
>
> p=precision, s=scale, [means optional]
>
> ---DB---- -----syntax----- --limits--- --dft--
> MSSQL2005 decimal[(p[,s])] 0<=s<=p<=38 18,0
> DB2/400 " 1<=s<=p<=31 5,0
>
> MYSQL
> spec: For DECIMAL(M,D), the maximum M is 65
> create: CREATE TABLE foo( bar decimal(10,2) )
>
> POSTGRESQL
> spec: DECIMAL(M,D), exact, no limit
> create: CREATE TABLE foo( bar decimal(10,2) )
>
> SQLite : no

What about sqlite ? It would be a Float in sqlite ?

[DenesL]

On Nov 12, 7:34 am, Douglas Soares de Andrade <dsandr...@gmail.com>
wrote:

>
> > SQLite : no
>
> What about sqlite ? It would be a Float in sqlite ?

No, it would have to be natively supported in the DB.
Otherwise the conversion problems that we are trying to avoid would
still occur.

[villas]

On Nov 12, 12:10 pm, DenesL <denes1...@yahoo.ca> wrote:
> ideal IS_DEC_IN_RANGE validator should also be based on DB back-end,
> definite values are easier but would limit values for otherwise
> limitless POSTGRESQL values.

As this feature has already taken a long time to specify, my vote
would be to simplify it as much as possible to get it off the ground.
If it helps to implement it quickly, I would suggest we should go
with the following:

1. Decimal to be default (18,2) -- which seems to be lowest common
denominator of all the DBs. Assume 2 decimal places because I guess
95% of requirement is for currency.
2. On Sqlite, or other DBs which do not support Decimal, simply map
to Float so that apps will at least run. But document this thoroughly
as a limitation of Sqlite.

In the future, there could be some kind of emulation for Sqlite
(maybe using integers) and also make the number of Decimal places
variable. However, my plea would be: let's not delay the
implementation of a simple Decimal field type just because it isn't
available for Sqlite.

Just my 2c, thanks for listening :-)

Regards, David

[mdipierro]

There are two issues:
- decimal would have to be supported but all backends to work properly
- decimal is not supported by Python. If you retrieve a value from the
db it would be converted to float and then you insert it in the db,
you get a different value, unless you use the decimal class to
represent it.

Is this much complication really worth the trouble?

Massimo

[villas]

On Nov 13, 7:22 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> There are two issues:
> - decimal would have to be supported but all backends to work properly
> - decimal is not supported by Python.

> Is this much complication really worth the trouble?

In my mind the problems of not having Decimal are:
1. How we show a long column of figures as 2 decimal places and
guarantee that it adds up to a total?
2. If we access the DB outside Web2py, how could we guarantee the
same thing? I mean we could have account balances being computed as a
stored procedure. Or, reports being produced by an external reporting
app. Accountants simply need to be absolutely certain that things in
the DB add up consistently.

Perhaps we should ask ourselves this: why does every serious database
have a Decimal field type?
I think that all the financial people of the world have demanded it
and they use those DBs. Those that don't will use any old DB and
won't be bothered if their columns don't quite add up.

Your issue number 1 therefore does not seem valid. Why should
accountants not be able to use Web2py just because some of the other
users choose not to use a serious DB? After all, the other users
probably won't even notice that their numbers have been mapped to
float! How many of them have kicked up a fuss about it so far?
Hardly any as far as I can see.

With regards your issue 2, I'm not an expert in Python, but I'm pretty
sure it will be able to add up a column of numbers from the DB
accurately. However, assuming that this is indeed a real issue, I
shall simply ask my serious DB to do the adding up for me instead!

You ask, is it worth the trouble. Well, if you want enterprise
accountants and financial people to use Web2py, I can only think that
a Decimal field type will do.

Finally, if you believe that a lack of demand from your user base
makes you feel reluctant to address the issue, then please bear this
in mind: the people that care about such things are probably not yet
using Web2py because of this limitation. Enterprise frameworks should
be capable of cross-balancing accounts, period.

Please forgive me if I have missed any other obvious solutions to the
problem.

Regards, David

[DenesL]

On 13 nov, 02:22, mdipierro <mdipie...@cs.depaul.edu> wrote:
> There are two issues:
> - decimal would have to be supported but all backends to work properly

SQLite might be only one that does not support it.
IMO the position should be: if you need decimal you will have to use
something other than SQLite.

> - decimal is not supported by Python. If you retrieve a value from the
> db it would be converted to float and then you insert it in the db,
> you get a different value, unless you use the decimal class to
> represent it.

Of course it does, since 2.4.
from decimal import *

>
> Is this much complication really worth the trouble?

Yes, mainly for financial info but it has other uses too.

[Douglas Soares de Andrade]

Em Fri, 13 Nov 2009 04:09:43 -0800 (PST)
villas <vill...@gmail.com> escreveu:

I have to agree here, im working with Django now (shame on me) and i
did had a hard time with Decimal fields, so i just stored the values
as strings in the db and that did the trick.

Im our case, i would vote to add decimal support to web2py (even
having some upa-dupa formatting capabilities included).

For sqlite (which i guess is what almost all of us use for tests), we
can store it as int in the db ignoring the decimal part till sqlite
supports it.

What about it ?

[Kuba Kucharski]

+1

--
Kuba

[mdipierro]

I have a possible implementation of decimal in trunk for sqlite

Field('dollars','decimal(7,3)')

3 decimals. Can you give it a try? Can we do better?

Massimo


On Nov 13, 7:39 am, Kuba Kucharski <kuba.kuchar...@gmail.com> wrote:
> +1
>
> --
> Kuba

[mdipierro]

In trunk, again you can now do (sqlite only)

>>> import decimal
>>> db=DAL()
>>> db.define_table('a',Field('b','decimal(10,3)'))
>>> b=123456789123.987654321
>>> db.a.insert(b=b)
>>> for row in db(db.a.id>0).select():
>>> print row.b, type(row.b)
123456789123.987 <class 'decimal.Decimal'>

while this works it is not clear to how it should work with forms and
validators. Right now you insert decimal, retrieve, search, orderby,
add but you cannot put them in forms.

[mdipierro]

I sent the wrong example. here is the right one

>>> import decimal
>>> db=DAL()
>>> db.define_table('a',Field('b','decimal(10,3)'))

>>> b=decimal.Decimal('123456789123.987654321')

>>> db.a.insert(b=b)
>>> for row in db(db.a.id>0).select():
>>> print row.b, type(row.b)
123456789123.987 <class 'decimal.Decimal'>

This may work in forms but please check. There is also a new
validator

IS_DECIMAL_IN_RANGE(a,b)

where a and b can be themselves decimals

[villas]

On Nov 13, 9:59 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> In trunk, again you can now do (sqlite only)

> while this works it is not clear to how it should work with forms and
> validators. Right now you insert decimal, retrieve, search, orderby,
> add but you cannot put them in forms.

Great, I like the layout/flash too!

Using the integers will give a 'perfectly' accurate result, but I can
see why this is a tricky exercise affecting all the forms/validators
etc.

Maybe you're aiming too high for Sqlite? I mean, how about this:

1. Carry on using float for Sqlite.
2. Round off the floats to the required places, i.e. Decimal(7,3)
rounds and saves 123.456789 as 123.46
3. When we retrieve the value from the database we get 123.460000001.
We simply trim it before using it.
4. Will the amounts add up OK? Yes, except for extreme case uses --
and you can be confident that those guys are already using Postgres
and Firebird etc.

I know what you're thinking, it isn't perfect. But do the Sqlite
users care? No; and there's no point in getting the moon on a stick
if a lollypop will do!

Again just 2cts; I don't want to think of you spending hours on
getting Sqlite 100% perfect when no one has ever asked for that.

Best regards, D

[mdipierro]

I think you are right.

Massimo

[mdipierro]

On a second thought. The problem is not the rounding. If we use float
internally we do not have the precision of 1c in $1billion.

On Nov 13, 5:10 pm, villas <villa...@gmail.com> wrote:

[Jonathan Lundell]

On Nov 13, 2009, at 4:09 PM, mdipierro wrote:

>
> On a second thought. The problem is not the rounding. If we use float
> internally we do not have the precision of 1c in $1billion.

Float has 53 bits, no?

[Massimo Di Pierro]

In theory double has 53 bits. Yet I tried and it does not seem to work
properly. It rounds to the cents. You may want to try the attached
files. Perhaps I am doing something wrong.

[Jonathan Lundell]

What are you expecting to see?

[Jonathan Lundell]

Never mind; I see what you mean.

[Jonathan Lundell]

On Nov 13, 2009, at 4:36 PM, Massimo Di Pierro wrote:

I'm not advocating floating point; I think it's a pain, and if integer is working, that's better. But....

>>> from decimal import *
>>> b = Decimal("1234567891.987654321")
>>> b
Decimal('1234567891.987654321')
>>> float(b)
1234567891.9876542
>>> str(float(b))
'1234567891.99'
>>> repr(float(b))
'1234567891.9876542'

I haven't checked, but it looks like an artifact of float.str.

[mdipierro]

Fascinating. As an experiment I replaced str with repr and it usually
works with float. Float is better then integer because allows
migrations from one decimal type to float and vice versa and allows
for expressions involving different field types without major errors.

The problem is that when it fails, it has major failures.

Everybody should be aware that there will be much bigger rounding
errors with type='decimal' on sqlite than with type='double'. With
double the rounding errors will be of the order of required decimal
precision, much bigger than double precision.

That is because is

>>> a = 0.3/3 # is float
>>> print str(a)[0:5]
0.1

while

>>> from decimal import Decimal
>>> b=Decimal(repr(a)[0:5])
>>> print b
0.099

Conversions like the one above are necessary step to get data in/out
of database.

The more I look into this the more I think we should not support it if
the database does not support it.

Massimo

[mdipierro]

decimal is a big can of worms:

http://www.mail-archive.com/django...@googlegroups.com/msg89642.html

[mdipierro]

status report.

1) I reverted from 'repr' to 'str'. we are going to have rounding
errors with sqlite but not as big as in my example before.

2) I also have added support for decimal for all the other databases
that support it. There is an issue with some adapters so I do not
swear by this. Moreover I did not have time to check it carefully.

I am caching a flight tomorrow morning so I will probably be unable to
look at this again for 24hrs.

If you get a chance to test it please let me know. Just knowing that
you can get data in and out using any database other than sqlite would
be important.

Mind that you cannot do:

db.define_table('a',Field('b','decimal(10,3)))
db.a.insert(b=3.14)

You have to do

db.a.insert(b='3.14')

Massimo

P.S. if anybody from the list is going to be to SuperComputing Sunday-
Tuesday and want to meet for a coffee, send me an email.

[yamandu]

Maybe it´s a stupid statement, if it is forgive me, but here it goes:
And if was there some kind of mask system, something similar to that
in COBOL for example (yes, I know...but it was very practical)
in the db model. Many db apps have this.
In this case it could use regex or some something simpler.
It would be useful to this issue and many others and I think it must
not depend on db backend.
This functionality should integrate (obviously) along the framework
from the db model to the widgets and would enforce validation so much.

Am I talking bullshit? Let me know why!

[villas]

> I am caching a flight tomorrow morning so I will probably be unable to
> look at this again for 24hrs.

Have a good trip! This feature can wait a few more days...

FYI here is feedback from Firebird. The result was:
ProgrammingError: (-842, 'isc_dsql_prepare: \n Dynamic SQL Error\n
SQL error code = @1\n Precision must be from 1 to
18')

I had a poke around and I believe you need to change 1560 in sql.py
From: precision, scale = [int(x) for x in field.type[9:-1].split
(',')]
To : precision, scale = [int(x) for x in field.type[8:-1].split(',')]

[Jonathan Lundell]

On Nov 13, 2009, at 9:43 PM, mdipierro wrote:

>
> Fascinating. As an experiment I replaced str with repr and it usually
> works with float. Float is better then integer because allows
> migrations from one decimal type to float and vice versa and allows
> for expressions involving different field types without major errors.
>
> The problem is that when it fails, it has major failures.
>
> Everybody should be aware that there will be much bigger rounding
> errors with type='decimal' on sqlite than with type='double'. With
> double the rounding errors will be of the order of required decimal
> precision, much bigger than double precision.
>
> That is because is
>
>>>> a = 0.3/3 # is float
>>>> print str(a)[0:5]
> 0.1
>
> while
>
>>>> from decimal import Decimal
>>>> b=Decimal(repr(a)[0:5])
>>>> print b
> 0.099
>
> Conversions like the one above are necessary step to get data in/out
> of database.
>
> The more I look into this the more I think we should not support it if
> the database does not support it.

Perhaps the string conversion should use a formatted string, with the precision of the database field.

[mdipierro]

good idea. I will add that.

[DenesL]

New decimal tested OK with MS SQL.

[DenesL]

DB2/400 also tested OK.
Notes:
1) Was able to access keyed table with PACKED numeric field as
decimal.
2) New non-keyed table created decimal fields as ZONED.

[mdipierro]

can you explain more. I do not understand.

[DenesL]

It is not a problem.
I am just recording my observations for future reference.

Note 1 says that you can access packed decimal fields with the new
decimal type (they can also be accessed as double, the driver handles
the conversion).
Note 2 says what happens when it creates a new table, decimal fields
get created as zoned.

Denes.

[mdipierro]

are "packed" and "zoned" field types in db2? what is the difference?

[DenesL]

Yes.
Zoned: 1 digit per byte
Packed: 2 digits per byte

http://books.google.ca/books?id=elh8ydHWtTYC&pg=PA143