On Mar 31, 2014, at 5:36 AM, Alex Good <quixop...@googlemail.com> wrote:
> Hi there, > > I'm working with a database where the original designer has chosen to > represent a large number of values as bitflags. Nearly every table in the > database has a 'flags' integer which is bitwise ORred or ANDed with constants > stored in the application code to extract the relevant options. I'm trying to > wrap this in a custom type which will allow me to refer to these values > individually as booleans and I can't figure out the correct approach. Here's > an example: > > > So what I want to be able to do is these kind of interactions > > m = session.query(TestModel).filter(TestModel.flag_one == True).first() > m.flag_one = False > session.add(m.flag_one) > session.commit() > assert((m.flags & 1) == 0) > > I can see how one would accomplish this with hybrid attributes but with every > table having 5 to 10 flags defined on it that would lead to a lot of typing, > hence the custom type approach. I attempted the following: > > class Flag(TypeDecorator): > impl = Integer > > def __init__(self, flag_constants, flag_name, *args, **kwargs): > super(Flag, self).__init__(*args, **kwargs) > self.flag_constants = flag_constants > self.flag_name = flag_name > > def column_expression(self, col): > return col.op("&")(getattr(self.flag_constants, self.flag_name)) The way you do this is you intercept expressions in Python and convert them on a per-expression basis to what's needed. You'd be focusing here on the comparator factory feature of types: http://docs.sqlalchemy.org/en/latest/core/types.html#types-operators I'd probably map the column just once and provide hybrid accessors on top of it. However, there is unfortunately a critical issue I've just discovered for ticket #3012, which is that we can't override __and__() and __or__() directly within this system. That's a bug. So we'll work around it using an ad-hoc operator, since your hybrids are doing the work anyway: class FlagType(TypeDecorator): impl = Integer class comparator_factory(TypeDecorator.Comparator): def _and(self, other): return self.op("&")(other) def _or(self, other): return self.op("|")(other) class FlagThing(hybrid_property): def __init__(self, attrib_name, flag): self.attrib_name = attrib_name self.flag = flag super(FlagThing, self).__init__( fget=self._get, expr=self._get_expr, fset=self._set ) def _get(self, instance): return (getattr(instance, self.attrib_name) & self.flag > 0) # if 3012 were fixed, this part wouldn't be needed def _get_expr(self, instance): return (getattr(instance, self.attrib_name)._and(self.flag) > 0) def _set(self, instance, value): existing = getattr(instance, self.attrib_name) if value: existing |= self.flag else: existing ^= self.flag setattr(instance, self.attrib_name, existing) class TestModel(Base): __tablename__ = "testmodel" id = Column("id", Integer, primary_key=True) flags = Column("flags", FlagType()) flag_one = FlagThing("flags", TestFlags.flag_value_one) flag_two = FlagThing("flags", TestFlags.flag_value_two) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.