[Kde-finance-apps] Query methods for Alkimia Database Class

[Fernando Vilas]

On Wednesday, June 16, 2010 14:03:31 Thomas Baumgart wrote:
> Hi Mukesh,
> 
> on Wednesday 16 June 2010 Mukesh Gupta wrote:
> > Hello Thomas ,Klass ,Fernando,Alvaro and all,
> > 
[...]
> 
> > How should the amount field be represented in database? Can we store it
> > as a string and write an alkvalue method to convert string to alkvalue. Or
> >  we should store both numerator and denominator of the alkvalue as
> >  Double in database.
> 
> Regarding the storage of the numbers: they have arbitrary size and
> precision which would cause some trouble if you simply use a double (which
> has limited precision). An AlkValue has a string representation which can
> be stored in a database field (varchar). The AlkValue ctor can take this
> string and convert it back to its internal representation, so there's no
> need to write a special function for that. Given that the db-field is wide
> enough to fit all characters, you will not loose and information.
> 
> The problem is, that you cannot use db functions like sum() to calculate a
> balance using that string representation. The KMyMoney implementation thus
> has two db fields, one takes the string and the other a numeric value.
> Fernando certainly knows more about that part.
> 

Thomas does a good job listing the issues below: numeric values may not have 
the range/precision for all cases, but strings do not support database 
aggregate functions for numbers. Even if the database supports the new 
IEEE-754 2008 standard quad precision (128-bit) floating point, that is still a 
subset of the values representable with the arbitrary precision library in 
use.

We stored both in the database in KMM because they were both available from 
the object, and we were still learning how it needed to be stored. At first, we 
had plans of doing some calculations, like sum, in the database, but with 
transactions that split assets, it became simpler to reuse code at the 
application layer. That is, a balance *must* be a running sum, because when an 
asset splits, the current balance must be adjusted using the split multiple 
before resuming the addition of subsequent transactions.

So far, our calculations are still done at the application layer, and the 
MyMoneyMoney (similar to AlkValue) objects are created from the stored string. 
The major disadvantage here is the time required to fetch 1000's of strings 
from the database then parse them with a regex.

[...]
> 
> Using doubles will present you some rounding problems down the line. That
> is one reason, why we want to use AlkValue for all internal math. You have
> been warned ;)
> 
> Using a "long long" (which is also not available on all systems) you have
> 64 bits for the numerator and 64 bits for the denominator (this is what
> KMyMoney uses today). This gives you also 19 digits of precision.
> 
[...]

Given that we use an arbitrary precision library, *any* fixed precision 
implementation will also cause problems down the line when a range or 
precision cap is reached. These sorts of problems are normally difficult to 
debug, especially with file anonymizers that change the transaction values.

-- 
Thanks,
Fernando Vilas
fvilas at iname.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part.
Url : http://mail.kde.org/pipermail/kde-finance-apps/attachments/20100619/2140b989/attachment.sig