DECIMAL or NUMERIC type for database fields

[Francois (Jersey)]

Dear all,

Having a numeric/decimal support for web2py is key to produce
applications including accounting or financial aspects, and there had
been discussions about it back in March.

I understand that the reason for not including decimal or numerical is
that it is not supported by all the database.

Mysql, postgre and sqlite all support this numeric/decimal feature.

I would be keen to contribute an accounting software to web2py, but I
need DECIMAL or NUMERIC support.

Any comments for or against.

Francois

[Pystar]

Cant you use "float" when specifying the field type in the database
definition?

On May 23, 9:43 pm, "Francois (Jersey)"

[dlypka]

A workaround (until web2py mission control can be convinced to support
decimal type) would be to store them as string (similar to BCD
concept)
and then convert to python decimal when needed. Luckily python itself
supports decimal.

[mdipierro]

Mission control is convinced there is no real reason not to use double
since, even if the database may store them as decimals there is no
decimal type in python, hence this extra conversion can only hurt
you.

You can very much mimic decimals using validators:

class IS_DECIMAL:
def __init__
(self,decimals=2,minimum=-10**9,maximum=10**9,error_message='bla bla
bla'):
self.decimals=decimals
self.minimum=minimum
self.maximum=maximum
self.error_message=error_message
def __call__(self,value):
(v,e)=IS_FLOAT_IN_RANGE
(self.minimum,self.maximum,self.error_message)(value)
if e:
return (v,e)
return int(v*(10**self.decimals))
def formatter(self,value):
return float(value)/(10**self.decimals)

SQLField('myfield','integer',requires=IS_DECIMAL())

In this way you store is as a long integer and always process it as an
integer but you display is with a decimal point.

[dlypka]

I see the fine distinction is that python has decimal 'support' but no
decimal 'type'
The decimal 'support' is described here
http://www.network-theory.co.uk/docs/pytut/DecimalFloatingPointArithmetic.html

I don't see how you can mimic aggregation of a decimal type using that
'virtual' decimal idea within a SQL query
unless you introduce some private SQL Functions to mimic 'virtual
decimal based on int' on the SQL Engine side.

And mimic logic can become a performance issue.

It still looks like complete native SQL decimal type support through
and through web2py would be great.

[Francois (Jersey)]

I thought that Python, since version 2.4 could deal with decimal.
Please have a look at the link http://docs.python.org/library/decimal.html

Am I missing something? If Python can deal with decimal and the
database can as well, would it make sense to have decimal implemented
in Web2py?

Apologies, if my understanding is not correct, as I am not as expert
as many of you.

[Francois (Jersey)]

Floats can not be used, as float may give a different result from
decimal, and this is not acceptable for financial applications:
An explanation of the difference can be found at http://docs.python.org/library/decimal.html

[mdipierro]

You win. This is a good point. I will add Decimal support.

On May 24, 5:05 am, "Francois (Jersey)"

<Francois.Ches...@googlemail.com> wrote:
> I thought that Python, since version 2.4 could deal with decimal.

> Please have a look at the linkhttp://docs.python.org/library/decimal.html

[mdipierro]

Can you help me on this by providing an example of decimal statement
in create table for each of the supported databases?

Massimo

On May 24, 5:07 am, "Francois (Jersey)"

[dlypka]

I wish it were so easy to deal with Microsoft...

[Yarko Tymciurak]

from curiousity, I just read thru the lib docs, and the module source (./Lib/decimal.py)...

It seems like mapping the various DB's rules (for example, see the allowed conversions chart from  T-SQL here: http://msdn.microsoft.com/en-us/library/ms187928.aspx) ... and then running the doctests that do exist for decimal.py, but against values retrieved from each of the backend / decimal storages  will probably be a good thing to do.

Reading the python docs, it will also be interesting to see how people deal with actually using this (I know people want to - I wonder what it will really take the application writer to make this shift.... maybe it will be ok.)

This seems like an undertaking that - by it nature - calls for care...

Ok - back to trying to "enjoy" the weekend (by doing chores around the house?!!! ;-) 

- Yarko

[mdipierro]

I am looking into this and the more I do the more I am convinced there
is not make to implement it so that it behaves the same on all
database backends if they do not support Decimal (and sqlite for
examples does not).

Anyway, I am still working on this...

[Yarko Tymciurak]

This is a bit long, so I will summarize here:

At this point, I recommend web2py support a decimal type in DAL, but it will be support of the Python-decimal "type" (class) and optimized backend storage for the Python form.  Given the stated desires (accuracy) I lay out why this is what makes sense.  Your comments are welcome (but please read through in full).

Massimo -

I think you can ignore sqlite for the moment.  Look at how py decimal works;  In particular, from Lib/decimal.py, lookpast all the exception classes, and the context --- the decimal class itself (in Python 2.6.2) starts around line 500; look at 513:

    def __new__(cls, value="0", context=None):
        """Create a decimal point instance.

        >>> Decimal('3.14')              # string input
        Decimal('3.14')
        >>> Decimal((0, (3, 1, 4), -2))  # tuple (sign, digit_tuple, exponent)
        Decimal('3.14')
        >>> Decimal(314)                 # int or long
        Decimal('314')
        >>> Decimal(Decimal(314))        # another decimal instance
        Decimal('314')
        >>> Decimal('  3.14  \\n')        # leading and trailing whitespace okay
        Decimal('3.14')
        """

        # Note that the coefficient, self._int, is actually stored as
        # a string rather than as a tuple of digits.  This speeds up
        # the "digits to integer" and "integer to digits" conversions
        # that are used in almost every arithmetic operation on
        # Decimals.  This is an internal detail: the as_tuple function
        # and the Decimal constructor still deal with tuples of
        # digits.

My concern, as I think of this, is --- "Why store decimal ON THE DB?"

While the argument for _wanting_ decimal is valid --- what has been coming to my concern is all the conversion between storage methods, and even the limits of decimal storage on various dbs - how would be deal with all this conversion back and forth?  And what would be gained by it?

Even within the python class - they are dealing with "all the conversion:  note the comment:  "the coefficient.... is stored as  a string...."

So the first pertient question, it seems, is "How much of financial calculation will be done BY THE DATABASE SERVER?"
If calculations, additions, etc. will be done on the server, then there is reason to have accuracy, and only fetch final result into web2py app (and then - if it is string or decimal or integer seems to make less difference).

If calculations will be done in python, in the application (let's say, as Guido's example from that book quoted earlier, for tax calculation where rounding effect is shown to impact) --- then what difference what the final storage is on the db?

The main point is --- Decimal()  in python is immutable;  so if you need financial calculations, do them with decimals, and store in some way that you will not get rounding errors (e.g. _not_ float / double perhaps).  But, as the snippet above shows, there are a multitude of ways to store, and ... AND  internally the conversion is expensive...   so what would web2py hope to gain by this "support"?

I think the motivations presented here on list are good,

BUT

I think the assumption of solution is flawed.

I SUGGEST THIS:

If you need decimal for calculation accuracy, then we (web2py) could possibly support a "decimal" type for DAL - so that you can most accurately make calculations, and save them without error on your DB backend.... BUT I think the format of that storage would be defined by DAL (perhaps as a string; perhaps as a tuple represented as string;).

The point is - if web2py supports saving decimal types, they would be Python decimals, and saved in a way that the application writer SHOULD NOT BE CONCERNED WITH - it would be whatever is the most efficient way for a particular back end to maintain value accuracy INTHE PYTHON VARIABLE, it would almost CERTAINLY NOT be an SQL-DECIMAL type, and further it may be a different storage mechanism per backend (although that is not likely necessary).

The warning then would be that if you declare DECIMAL in DAL, it is Python-Decimal, and backend storage is determined by DAL.   IF you need stored procedure calculations on your backend, that is (by definition)  backend specific, and you should then use executesql() to store and get said values, and determine the conversion which achieves best your applications design goals (performance, accuracy).

Regards,
- Yarko

[Yarko Tymciurak]

On Sun, May 24, 2009 at 7:24 PM, Yarko Tymciurak <yar...@gmail.com> wrote:

.........

The warning then would be that if you declare DECIMAL in DAL, it is Python-Decimal, and backend storage is determined by DAL.   IF you need stored procedure calculations on your backend, that is (by definition)  backend specific, and you should then use executesql() to store and get said values, and determine the conversion which achieves best your applications design goals (performance, accuracy).

To support Massimo's last comment more directly - it is not important _at all_ that this be stored "the same way on all db backends" - ONLY that it behaves the same in web2py / your python-decimal types when it is retrieved.... think of it as effectively no more than a "pickle" of your python decimal.

FURTHER --- if your application does calculation BOTH here (web2py) and there (your db), then you have the design option to make the calculations in DECIMAL on your db, and  if your application is ineed demanding, it would be up to you to design the desired solution for your backend:  either have stored procedure calculations store in the appropriate Python-decimal-DAL-storage form,  or - as appropriate for your performance goals - you can make a view, and a stored procedure that would extract your db's decimal into the appropriate storage form for DAL / python-decimal.   (Clearly, string would be the simplest for these mixed comutation, high demand applications).

Regards,
- Yarko

[Yarko Tymciurak]

On Sun, May 24, 2009 at 7:39 PM, Yarko Tymciurak <yar...@gmail.com> wrote:

.....

.   (Clearly, string would be the simplest for these mixed comutation, high demand applications).

comPutation....   (debug mode browsers can't keep up w/ my typing speed?! ;-)
 

Regards,
- Yarko

[mdipierro]

For those db that support decimal, decimal is stored as a string but
treated as double for comparison and expressions.

If the db does not support decimal we have to make a choice: store as
string or store as double.

If we choose to store it as a strings results involving comparisons
and expressions will be give wrong results.

If we choose to store them as double, there may be some precisions
issues.



On May 24, 7:39 pm, Yarko Tymciurak <yark...@gmail.com> wrote:

[Yarko Tymciurak]

*sigh* .... yes, I am coming around to this simple awareness...  and there appears to be no unified decimal approach to sqlite (from what I can tell)....

[Joe Barnhart]

About a million years ago, when I did DB2 programming, we used DECIMAL
a lot. Anything that handled money was a DECIMAL value. We also did
calculations on columns, like SUM(). I think it is worthwhile pursuing
a solution for the DAL in web2py, as we used it a lot for anything
financial. In fact, it was relatively rare that we needed floats or
doubles.

Another thread touched on the topic of making the DAL itself somewhat
modular. The context of the request was to make DAL more tuned to the
needs of GAE developers by exploiting its not-quite-relational feature
set. Perhaps these cases could point to a way to modularize the DAL
such that it becomes an extendable component instead of a fixed one.

In my own case, I have interest in substituting an OODB (developed in-
house) for the SQL features of the DAL. Our OODB is really nothing
like a relational database, but stores and retrieves objects based on
their metadata. Replacing the web2py DAL with my own is beyond my
meager ability at this point, but if the DAL itself had a clear
interface with web2py I might be able to tackle it.

Warm regards,

Joe Barnhart

[mdipierro]

http://groups.google.com/group/web2py/browse_thread/thread/13d9e9fd7334b679

[Francois (Jersey)]

Agree with Joe, on the need to store decimal in the database. I may be
biaised because I have used Sqlalchemy before.

I suppose we should be able to find inspiration from Sqlalchemy code
for decimal...

Please let me know how I can help.