I am struggling a bit with a design question. I have a database of
articles, where each article has a price in a specific currency. The
currency and their conversion rates are in a separate table. In order to
be able to do fast queries I also keep the price in Euros in the article
table. The classes look like this:
class Currency(BaseObject):
"""A currency
Currencies are identified by their ISO 4217 three letter currency
code.
"""
__tablename__ = "currency"
code = schema.Column(types.String(3), primary_key=True)
rate = schema.Column(types.Numeric(precision=6, scale=2), nullable=False)
def __repr__(self):
return "<Currency %s rate=%.2f>" % (self.code, self.rate)
class Article(BaseObject):
__tablename__ = "clothing_image"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
title = schema.Column(types.Unicode(80), nullable=False)
currency = schema.Column(types.String(3),
schema.ForeignKey("currency.code", ondelete="RESTRICT"),
nullable=False, default="EUR")
_price = schema.Column("price", types.Numeric(precision=6, scale=2),
nullable=False)
price_euro = schema.Column(types.Numeric(precision=6, scale=2),
nullable=False, index=True)
def _get_price(self):
return self._price
def _set_price(self, value):
currency=meta.Session.query(Currency).get(self.currency)
self.price_euro=value*currency.rate
self._price=value
price = orm.synonym("_price", descriptor=property(_get_price, _set_price))
this breaks down when you create a new Article instance: as soon as you
set the price on the newly created instance the _set_price method is
called. The query to get the currency in there triggers a flush, which
fails since the (non-nullable) title is not set at that point. If this
happens in a unittest the result appears to be a hang or python
segfault.
I am wondering what the best solution to this issue is. I can think of
several options:
- create INSERT and UPDATE triggers and use those to update the
price_euro column. Downside: code is less portable and you can
no longer use SQLite for testing.
- require a manual updated for price_euro. Downside: this needlessly
complicates the internal API
- forget about the price_euro column and always do a table join.
Downside: SQL queries become more complex and expensive.
I am hoping someone here has encountered this pattern before and came up
with a good solution.
Wichert.
--
Wichert Akkerman <[email protected]> It is simple to make things.
http://www.wiggy.net/ It is hard to make things simple.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
