Thread: Re: [SQLObject] Decimals in sqlite (Page 2)
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Christopher S. <csi...@gm...> - 2008-02-13 23:40:51
|
On Wednesday 13 February 2008 17:24:51 Oleg Broytmann wrote: > Couldn't we get an incorrect result trying to compare right-padding > "009.11" and "2112.123456789"? I.e., could we rely solely on the size, not > counting the number of digits in fraction? Is there a case where ORDER BY would operate on quantities with different size/precision parameters? If you're always doing ORDER BY a single column with a fixed size/precision, then the case above (for size=13, precision=9) would actually be "0009.110000000" and "2112.123456789"; I think all strings of that format should sort the same way as the numbers they represent. Actually you'd need some special handling for negative numbers. So you'd need to make the padded width size+2, and make sure that the dash for the negative sign stayed in front of the zero-padding. You couldn't just use str.rjust(size+2, '0') on a negative decimal. But this isn't very complicated logic. cs |
From: Oleg B. <ph...@ph...> - 2008-02-13 23:45:21
|
On Wed, Feb 13, 2008 at 05:40:44PM -0600, Christopher Singley wrote: > If you're always doing ORDER BY a single column with a fixed size/precision, > then the case above (for size=13, precision=9) would actually be > "0009.110000000" and "2112.123456789"; I think all strings of that format > should sort the same way as the numbers they represent. Yes, but think about (size=5, precision=5). SQLObject doesn't do a quantization, and SQLite will store "2112.123456789" - it is just a string - but what about the padding?.. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Christopher S. <csi...@gm...> - 2008-02-14 05:59:32
|
On Wednesday 13 February 2008 17:45:25 Oleg Broytmann wrote: > Yes, but think about (size=5, precision=5). SQLObject doesn't do > a quantization, and SQLite will store "2112.123456789" - it is just > a string - but what about the padding?.. How does the data get marshalled by SQLObject and/or sqlite? The size/precision constraints aren't really enforced by sqlite, are they? Does SQLObject police this? Anyway, if we require that SQLObject work with legacy databases unmanaged by SQLObject, then I doubt that padding will make ORDER BY work right, in general. But if you ask me, having ORDER BY broken is better than having object types broken and potential inaccuracies in data that needs guaranteed accuracy. cs |
From: Oleg B. <ph...@ph...> - 2008-02-14 10:56:44
|
On Wed, Feb 13, 2008 at 11:59:22PM -0600, Christopher Singley wrote: > How does the data get marshalled by SQLObject and/or sqlite? See sqlobject/converters.py. > The > size/precision constraints aren't really enforced by sqlite, are they? In current situation SQLite converts them to float, what we are going to fix. > Does > SQLObject police this? No. > Anyway, if we require that SQLObject work with legacy databases unmanaged by > SQLObject, then I doubt that padding will make ORDER BY work right, in > general. > > But if you ask me, having ORDER BY broken is better than having object types > broken and potential inaccuracies in data that needs guaranteed accuracy. I have a solution - a second type of DecimalCol - named DecimalStringCol, may be - to allow the user to choose which implementation (s)he wants - the current one (ok for most DB except SQLite) or strings-based. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Christopher S. <csi...@gm...> - 2008-02-14 13:52:22
|
On Thursday 14 February 2008 04:56:37 Oleg Broytmann wrote: > See sqlobject/converters.py. So SQLObject passes the size/precision parameters to the DB backend only when the table is created, and after that leaves the DB to enforce these constraints? Well, sqlite doesn't enforce anything, so these constraints just disappear. What about DecimalValidator in sqlobject/col.py (or a subclass)? That would seem like a logical place to manually quantize decimals going to/from sqlobject. There's a 'state' argument that could maybe be used to pass in size/precision. > I have a solution - a second type of DecimalCol - named > DecimalStringCol, may be - to allow the user to choose which implementation > (s)he wants - the current one (ok for most DB except SQLite) or > strings-based. Giving the user choice is good. However, I think the current implementation of DecimalCol on sqlite is quite broken; maybe that shouldn't be left as the default. If a user expects an object to represent a decimal; but the objectl doesn't really have size and precision, doesn't really store numbers accurately, and isn't even consistent with the language's typing... to me, that is a dangerous structure. cs |
From: Oleg B. <ph...@ph...> - 2008-02-14 14:27:22
|
On Thu, Feb 14, 2008 at 07:52:10AM -0600, Christopher Singley wrote: > Well, sqlite doesn't enforce anything, so these constraints > just disappear. Well, SQLite doesn't have a decimal type, to begin with... > What about DecimalValidator in sqlobject/col.py (or a subclass)? That > would seem like a logical place to manually quantize decimals going > to/from sqlobject. Does SQLObject really needs to quantize user's decimals? Why not just pass them to the backend as is? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Christopher S. <csi...@gm...> - 2008-02-14 14:46:46
|
On Thursday 14 February 2008 08:27:23 Oleg Broytmann wrote: > Does SQLObject really needs to quantize user's decimals? Why not just > pass them to the backend as is? Well, the most important thing is to get DecimalCol() actually returning decimals... I wouldn't want to lose focus of the easiest & most important goal. But on top of that, failing to quantize the data introduces inconsistencies. The precision is an important and fundamental characteristic of a Decimal. If I define a DecimalCol as size=10, precision=5, then when I fetch that attribute from the database, I really don't want it to have size=20, precision=2. I think the class definition should be enforced for each instance. Otherwise, why bother having these parameters if they are meaningless? Don't we want to be able to use SQLObject to write database-agnostic code? If every other DB engine offers strict guarantees about size/precision, shouldn't sqlobject do the same? cs |
From: Oleg B. <ph...@ph...> - 2008-02-14 14:59:21
|
On Thu, Feb 14, 2008 at 08:46:40AM -0600, Christopher Singley wrote: > failing to quantize the data introduces > inconsistencies. Inconsistency between what? between different backends? Are they really consistent by themselves? > If I define a DecimalCol as size=10, precision=5, then when I fetch that > attribute from the database, I really don't want it to have size=20, > precision=2. Yes, but there is no harm in having size=20, precision=5. > I think the class definition should be enforced for each instance. Otherwise, > why bother having these parameters if they are meaningless? They are passed to the backend. > Don't we want to be able to use SQLObject to write database-agnostic code? As far as possible but not further. By using strings to store decimals we are making a workaround for the backend that doesn't have a decimal type. But the workaround has a price. > If > every other DB engine offers strict guarantees about size/precision, > shouldn't sqlobject do the same? They guarantee minimal precision. Quantization in SQLObject makes that worse, not better. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2008-02-14 16:31:17
|
On Thu, Feb 14, 2008 at 05:59:23PM +0300, Oleg Broytmann wrote: > we are making a workaround for the backend that doesn't have a decimal > type. But the workaround has a price. Well, my resolution on the issue is: DecimalCol will not be changed. I will add documentation explaining the type affinity problem in SQLite. There will be a new DecimalStringCol that stores Decimals as text. If you want quantization please write a validator class, and I will include it into SQLObject. Initially it will be off, but a user can easily use it - every column has a "validator" keyword argument. With all these changes a user is free to choose an implementation and its price - DecimalCol or DecimalStringCol, or DecimalCol+quantization, or DecimalStringCol+quantization. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Christopher S. <csi...@gm...> - 2008-02-14 16:40:17
|
On Thursday 14 February 2008 10:31:10 Oleg Broytmann wrote: > I will add documentation explaining the type affinity problem in SQLite. I think that's a good idea. Let me know if I can help. > There will be a new DecimalStringCol that stores Decimals as text. If > you want quantization please write a validator class, and I will include it > into SQLObject. I'll be happy to write a validator class for DecimalStringCol - that's easy, if I can pass it it size/precision. What I can't figure out is how to set the state for the validator. Where in the code do these validators get called? cs |
From: Oleg B. <ph...@ph...> - 2008-02-14 17:09:55
|
On Thu, Feb 14, 2008 at 10:40:08AM -0600, Christopher Singley wrote: > I'll be happy to write a validator class for DecimalStringCol - that's easy, > if I can pass it it size/precision. When SQLObject instantiate a validator it can pass any initialization values the validator wants. For a user-instantiate validator, though, it is the user's responsibility to pass all necessary initialization. Unfortunately, SQLObject doesn't pass the current column - only the value and the SQLObject instance are passed. That, probably, should be fixed. > What I can't figure out is how to set > the state for the validator. You don't need to. > Where in the code do these validators get > called? In main.py. Lookup to_python and from_python there. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2008-07-01 16:29:55
|
On Thu, Feb 14, 2008 at 07:31:10PM +0300, Oleg Broytmann wrote: > DecimalCol will not be changed. I will add documentation explaining the > type affinity problem in SQLite. > There will be a new DecimalStringCol that stores Decimals as text. It is now in the trunk. > If > you want quantization please write a validator class, and I will include it > into SQLObject. Initially it will be off, but a user can easily use it > - every column has a "validator" keyword argument. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Christopher S. <csi...@gm...> - 2008-07-01 17:54:21
|
On Tuesday 01 July 2008 11:29:56 Oleg Broytmann wrote: > On Thu, Feb 14, 2008 at 07:31:10PM +0300, Oleg Broytmann wrote: > > DecimalCol will not be changed. I will add documentation explaining > > the type affinity problem in SQLite. > > There will be a new DecimalStringCol that stores Decimals as text. > > It is now in the trunk. > > > If > > you want quantization please write a validator class, and I will include > > it into SQLObject. Initially it will be off, but a user can easily use it > > - every column has a "validator" keyword argument. > > Oleg. Oleg, Thank you. I'd be happy to write a validator, if I can figure out how it should work. For the special case of CurrencyCol, which has precision known in advance, this is easy: ===== class CurrencyValidator(sqlobject.col.DecimalValidator): HUNDREDTHS = Decimal('9.99') def to_python(self, value, state): value = super(CurrencyValidator, self).to_python(value, state) if isinstance(value, Decimal): value = value.quantize(self.HUNDREDTHS) return value def from_python(self, value, state): value = super(CurrencyValidator, self).from_python(value, state) if isinstance(value, Decimal): value = value.quantize(self.HUNDREDTHS) return value ====== But for a general DecimalCol, where the precision isn't known in advance, obviously this simple strategy won't work. An alternative strategy would be like this: ====== class PreciseDecimalValidator(sqlobject.col.DecimalValidator): def __init__(self, *args, **kw): size = kw.pop('size', None) precision = kw.pop('precision', None) if precision: self.PRECISION = Decimal(10) ** (-1 * int(precision)) super(PreciseDecimalValidator, self).__init__(*args, **kw) def to_python(self, value, state): value = super(PreciseDecimalValidator, self).to_python(value, state) if self.PRECISION and isinstance(value, Decimal): value = value.quantize(self.PRECISION) return value def from_python(self, value, state): value = super(PreciseDecimalValidator, self).from_python(value, state) if self.PRECISION and isinstance(value, Decimal): value = value.quantize(self.PRECISION) return value precision = 4 class Example(sqlobject.SQLObject): value = sqlobject.DecimalCol(size=10, precision=precision, validator=PreciseDecimalValidator(precision=precision)) ======= But this is very inelegant. Is there a way to use introspection instead? Thanks, cs |
From: Oleg B. <ph...@ph...> - 2008-07-01 18:53:14
|
On Tue, Jul 01, 2008 at 12:52:31PM -0500, Christopher Singley wrote: > class Example(sqlobject.SQLObject): > value = sqlobject.DecimalCol(size=10, precision=precision, > validator=PreciseDecimalValidator(precision=precision)) > > But this is very inelegant. Is there a way to use introspection instead? I do not consider this as inelegant. But we can decide to add an additional parameter: class Example(sqlobject.SQLObject): value = sqlobject.DecimalCol(size=10, precision=precision, quantize=precision) DecimalCol will pass the 'quantize' parameter down to the validator. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2008-07-01 18:56:21
|
On Tue, Jul 01, 2008 at 10:53:16PM +0400, Oleg Broytmann wrote: > we can decide to add an > additional parameter: > > class Example(sqlobject.SQLObject): > value = sqlobject.DecimalCol(size=10, precision=precision, > quantize=precision) Or, if 'quantize' is always equal to precision, that could be class Example(sqlobject.SQLObject): value = sqlobject.DecimalCol(size=10, precision=precision, quantize=True) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Christopher S. <csi...@gm...> - 2008-07-01 20:19:05
|
On Tuesday 01 July 2008 13:56:26 Oleg Broytmann wrote: > Or, if 'quantize' is always equal to precision Since I believe the intent is to enforce consistency between the Python model and the persistent storage, that should be the case. > class Example(sqlobject.SQLObject): > value = sqlobject.DecimalCol(size=10, precision=precision, > quantize=True) How about something like this? class DecimalStringValidator(DecimalValidator): def __init__(self, *args, **kw): size = kw.pop('size', NoDefault) precision = kw.pop('precision', NoDefault) assert precision >= 0, \ "You must give a precision argument as a positive integer" self.precision = Decimal(10) ** (-1 * int(precision)) super(DecimalStringValidator, self).__init__(*args, **kw) def to_python(self, value, state): value = super(DecimalStringValidator, self).to_python(value, state) if isinstance(value, Decimal): value = value.quantize(self.precision) return value def from_python(self, value, state): value = super(DecimalStringValidator, self).from_python(value, state) if isinstance(value, Decimal): value = value.quantize(self.precision) return value class SODecimalStringCol(SOStringCol): def __init__(self, **kw): self.size = kw.pop('size', NoDefault) assert self.size >= 0, \ "You must give a size argument as a positive integer" self.precision = kw.pop('precision', NoDefault) assert self.precision >= 0, \ "You must give a precision argument as a positive integer" kw['length'] = int(self.size) + int(self.precision) self.quantize = kw.pop('quantize', False) assert isinstance(self.quantize, bool), \ "quantize argument must be Boolean True/False" super(SODecimalStringCol, self).__init__(**kw) def createValidators(self): validators = super(SODecimalStringCol, self).createValidators() if self.quantize: validators.insert(0, DecimalStringValidator(precision=self.precision)) class DecimalStringCol(StringCol): baseClass = SODecimalStringCol |
From: Oleg B. <ph...@ph...> - 2008-07-01 20:55:17
|
On Tue, Jul 01, 2008 at 03:17:14PM -0500, Christopher Singley wrote: > How about something like this? It looks good. I will test it and add to the trunk. Thank you! > class DecimalStringValidator(DecimalValidator): > def __init__(self, *args, **kw): > size = kw.pop('size', NoDefault) > precision = kw.pop('precision', NoDefault) > assert precision >= 0, \ > "You must give a precision argument as a positive integer" > self.precision = Decimal(10) ** (-1 * int(precision)) > super(DecimalStringValidator, self).__init__(*args, **kw) > > def to_python(self, value, state): > value = super(DecimalStringValidator, self).to_python(value, state) > if isinstance(value, Decimal): > value = value.quantize(self.precision) > return value > > def from_python(self, value, state): > value = super(DecimalStringValidator, self).from_python(value, state) > if isinstance(value, Decimal): > value = value.quantize(self.precision) > return value > > class SODecimalStringCol(SOStringCol): > def __init__(self, **kw): > self.size = kw.pop('size', NoDefault) > assert self.size >= 0, \ > "You must give a size argument as a positive integer" > self.precision = kw.pop('precision', NoDefault) > assert self.precision >= 0, \ > "You must give a precision argument as a positive integer" > kw['length'] = int(self.size) + int(self.precision) > self.quantize = kw.pop('quantize', False) > assert isinstance(self.quantize, bool), \ > "quantize argument must be Boolean True/False" > super(SODecimalStringCol, self).__init__(**kw) > > def createValidators(self): > validators = super(SODecimalStringCol, self).createValidators() > if self.quantize: > validators.insert(0, > DecimalStringValidator(precision=self.precision)) > > class DecimalStringCol(StringCol): > baseClass = SODecimalStringCol Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2008-07-11 14:54:12
|
On Tue, Jul 01, 2008 at 03:17:14PM -0500, Christopher Singley wrote: > class DecimalStringValidator(DecimalValidator): > def __init__(self, *args, **kw): > size = kw.pop('size', NoDefault) size is not used in the __init__(). > precision = kw.pop('precision', NoDefault) > assert precision >= 0, \ > "You must give a precision argument as a positive integer" > self.precision = Decimal(10) ** (-1 * int(precision)) > super(DecimalStringValidator, self).__init__(*args, **kw) > > def to_python(self, value, state): > value = super(DecimalStringValidator, self).to_python(value, state) > if isinstance(value, Decimal): > value = value.quantize(self.precision) > return value > > def from_python(self, value, state): > value = super(DecimalStringValidator, self).from_python(value, state) > if isinstance(value, Decimal): > value = value.quantize(self.precision) > return value from_python() is supposed to return a string - this is Decimal*String*Converter, right? Shouldn't it be implemented as def from_python(self, value, state): value = super(DecimalStringValidator, self).from_python(value, state) if isinstance(value, Decimal): value = str(value.quantize(self.precision)) # or .to_eng_string() return value ? Without str() the query is INSERT INTO test (d) VALUES (10.0000) but converting to floats is exactly what we want to prevent. With str() the query is INSERT INTO test (d) VALUES ('10.0000') Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Christopher S. <csi...@gm...> - 2008-07-11 15:36:08
|
On Friday 11 July 2008 09:36:22 Oleg Broytmann wrote: > size is not used in the __init__(). I don't think size is very important for python Decimals (only for storage engines). In working with decimals, the problem is always with precision, rounding, strict equality, and so forth. Maybe I am just short-sighted? It would not be difficult to strictly validate size, if desired. > ? Without str() the query is > > INSERT INTO test (d) VALUES (10.0000) > > but converting to floats is exactly what we want to prevent. With str() > the query is > > INSERT INTO test (d) VALUES ('10.0000') Of course you are correct. I apologize for the careless error. How about this: ==== class DecimalStringValidator(DecimalValidator): def __init__(self, *args, **kw): precision = kw.pop('precision', NoDefault) assert precision >= 0, \ "You must give a precision argument as a positive integer" self.precision = Decimal('10') ** (-1 * int(precision)) size = kw.pop('size', NoDefault) assert size >= precision, \ "Precision argument can't be larger than size" self.max = Decimal('10') ** (int(size) - int(precision)) super(DecimalStringValidator, self).__init__(*args, **kw) def to_python(self, value, state): value = super(DecimalStringValidator, self).to_python(value, state) if isinstance(value, Decimal): value = value.quantize(self.precision) assert value < self.max, \ "Value must be less than %s" % int(self.max) return value def from_python(self, value, state): value = super(DecimalStringValidator, self).from_python(value, state) if isinstance(value, Decimal): value = value.quantize(self.precision) assert value < self.max, \ "Value must be less than %s" % int(self.max) return str(value) class SODecimalStringCol(SOStringCol): def __init__(self, **kw): self.size = kw.pop('size', NoDefault) assert self.size >= 0, \ "You must give a size argument as a positive integer" self.precision = kw.pop('precision', NoDefault) assert self.precision >= 0, \ "You must give a precision argument as a positive integer" kw['length'] = int(self.size) + int(self.precision) self.quantize = kw.pop('quantize', False) assert isinstance(self.quantize, bool), \ "quantize argument must be Boolean True/False" super(SODecimalStringCol, self).__init__(**kw) def createValidators(self): validators = super(SODecimalStringCol, self).createValidators() if self.quantize: validators.insert(0, DecimalStringValidator(size=self.size, precision=self.precision)) class DecimalStringCol(StringCol): baseClass = SODecimalStringCol |
From: Oleg B. <ph...@ph...> - 2008-07-11 16:23:10
|
On Fri, Jul 11, 2008 at 10:34:07AM -0500, Christopher Singley wrote: > > ? Without str() the query is > > > > INSERT INTO test (d) VALUES (10.0000) > > > > but converting to floats is exactly what we want to prevent. With str() > > the query is > > > > INSERT INTO test (d) VALUES ('10.0000') > > Of course you are correct. I apologize for the careless error. > > How about this: It requires a bit more work because we need to to always convert the value to a string. To do this the column always creates DecimalStringValidator: class DecimalStringValidator(DecimalValidator): def to_python(self, value, state): value = super(DecimalStringValidator, self).to_python(value, state) if self.precision and isinstance(value, Decimal): value = value.quantize(self.precision) return value def from_python(self, value, state): value = super(DecimalStringValidator, self).from_python(value, state) if isinstance(value, Decimal): if self.precision: value = value.quantize(self.precision) value = value.to_eng_string() return value class SODecimalStringCol(SOStringCol): def __init__(self, **kw): self.size = kw.pop('size', NoDefault) assert (self.size is not NoDefault) and (self.size >= 0), \ "You must give a size argument as a positive integer" self.precision = kw.pop('precision', NoDefault) assert (self.precision is not NoDefault) and (self.precision >= 0), \ "You must give a precision argument as a positive integer" kw['length'] = int(self.size) + int(self.precision) self.quantize = kw.pop('quantize', False) assert isinstance(self.quantize, bool), \ "quantize argument must be Boolean True/False" super(SODecimalStringCol, self).__init__(**kw) def createValidators(self): if self.quantize: v = DecimalStringValidator(precision=Decimal(10) ** (-1 * int(self.precision))) else: v = DecimalStringValidator(precision=0) return [v] + super(SODecimalStringCol, self).createValidators() class DecimalStringCol(StringCol): baseClass = SODecimalStringCol (Now I have to copy your self.max to my implementation...) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2008-07-11 16:39:30
|
On Fri, Jul 11, 2008 at 08:23:05PM +0400, Oleg Broytmann wrote: > (Now I have to copy your self.max to my implementation...) It is now in the trunk, revision 3504. Thank you very much! Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Christopher S. <csi...@gm...> - 2008-07-11 17:34:43
|
On Friday 11 July 2008 11:39:28 Oleg Broytmann wrote: > It is now in the trunk, revision 3504. Thank you very much! I'm surprised that one as unskilled as myself can make a contribution, but happy to do it. If anyone else out there is trying to develop desktop financial applications for SQLObject using sqlite as a backend, this new DecimalStringCol should be helpful in ensuring accurate accounting. As always, thank you for your efforts in maintaining this wonderful tool. cs |
From: Oleg B. <ph...@ph...> - 2008-07-11 18:10:51
|
On Fri, Jul 11, 2008 at 12:32:18PM -0500, Christopher Singley wrote: > On Friday 11 July 2008 11:39:28 Oleg Broytmann wrote: > > It is now in the trunk, revision 3504. Thank you very much! > > I'm surprised that one as unskilled as myself can make a contribution, but > happy to do it. As a Russian proverb says, "It's not gods who make pots". (-: Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |