Problem with sqlite3 and Decimal

[Frank Millman]

"Chris Angelico"  wrote in message 
news:CAPTjJmrfw-qNx-a=3Q2qJ244FGVxz3MPe4WA-WDUsmchXUfZbA at mail.gmail.com...
>
> On Sun, Dec 13, 2015 at 4:00 PM, Frank Millman <frank at chagford.com> wrote:
> > My new solution is to pass a 'scale' factor into my aggregate function. 
> > The
> > function uses the Decimal quantize method to round the result before
> > returning. So far it seems to be working.
>
> So, effectively, you're using fixed point arithmetic. As long as
> you're restricting yourself to adding values together, that's easy; be
> careful of multiplying by tax percentages, as you might flick to
> float.
>
> Really, you'd do a lot better to move to PostgreSQL.

Thanks for the warning, but I think I am safe.

There is only one exceptional case where I use my 'aggregate' function. It 
is a substitute for the following SQL statement -

    UPDATE table SET balance = balance + ? WHERE date > ?

Normally it works fine. However, I wanted to populate my database with some 
real-world values, so I wrote a program to generate a few thousand 
transactions, and that triggered the rounding errors that caused me to start 
this thread.

I have replaced the statement with -

    UPDATE table SET balance = aggregate(balance, ?, ?) WHERE date > ?

This prevents rounding errors from creeping in.

In all other cases, I use unadorned SQL to calculate a scalar value, which I 
round to the appropriate scaling factor before storing the result.

Regarding PostgreSQL, I have mentioned before that I offer my users a choice 
of 3 databases - PostgreSQL, Sql Server, and sqlite3 - so I have to make 
sure that my app works with all of them. I agree that for serious database 
work one should use PostgreSQL or Sql Server. But I think that sqlite3 is 
perfect for demos and for one-man businesses. It is fast, lightweight, and 
very 'standards compliant'. It does have some quirks, but these are clearly 
documented and the mailing list is very responsive.

Frank