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 <wich...@wiggy.net> 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 sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---