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
On Sun, May 24, 2009 at 6:23 PM, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> 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...
>
> On May 24, 1:33 pm, Yarko Tymciurak <yark...@gmail.com> wrote:
> > 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
> >
> > On Sun, May 24, 2009 at 10:16 AM, dlypka <dly...@gmail.com> wrote:
> >
> > > I wish it were so easy to deal with Microsoft...
> >
> > > On May 24, 10:50 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> > > > 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)"
> >
> > > > <francois.ches...@googlemail.com> wrote:
> > > > > 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 athttp://
> > > docs.python.org/library/decimal.html
> >
> >
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
