On Mar 31, 2014, at 5:36 AM, Alex Good <[email protected]> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
