Bitwise Flag Type

[Alex Good]

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:

class TestFlags(object):
    flag_value_one = 1
    flag_value_two = 2
    flag_value_three = 4
    flag_value_four = 8

class TestModel(TestBase):
    __tablename__  = "testmodel"
    id = Column("id", Integer, primary_key=True)
    flags = Column("flags", Integer)
    flag_one = column_property(Column("flags", Flag(TestFlags, "flag_value_one")))
    flag_two = column_property(Column("flags", Flag(TestFlags, "flag_value_two")))
    flag_three = column_property(Column("flags", Flag(TestFlags, "flag_value_three")))
    flag_four = column_property(Column("flags", Flag(TestFlags, "flag_value_four")))

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))

Which works great for querying values but not for updating or inserting them. Another point which I can't figure out is what to do if updating multiple flags in the same step. It seems to me that it would make more sense to do this in process_bind_param but then obviously there is no current value of the flag integer for me to operate against. So, I'm a bit lost, any ideas on how to implement this would be greatly appreciated.

Thanks
Alex

[Michael Bayer]

On Mar 31, 2014, at 5:36 AM, Alex Good <quix...@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)

[Jonathan Vanasco]

I'm interested in what you find. I know TypeDecorator is the right solution, I was looking at that for this exact same situation a few weeks ago ( https://groups.google.com/forum/#!searchin/sqlalchemy/vanasco%7Csort:date/sqlalchemy/sQtOYxSUiqI/5ns2vWMFaGAJ )

I have a similar situation.  I wrote a generic bitwise wrapper class that I was ashamed of, but I guess I should eventually release it.  i can toss it on github under the MIT if you'd like.

The way I have my bitwise stuff working is this:

* I create an class that inherits from my `BitwiseSet` class.  That subclass stores a mapping of the bitwise values, the parent class has functions for common bitwise operations ( add, remove, has_any, has_all -- both by string and integer ).   The parent class has an 'encode' and 'decode' function, which returns an int or list of elements (as int or string ).

    class BitwiseClassFieldA(BitwiseSet):

        set_ = {

               'flag_a' << 1,

               'flag_b' << 2,

               'flag_c' << 3,

        }

* I have a property on each of my sqlalchemy objects that works something like this...

    class Foo(SqlAlchemyObject):

         bitwise_field_a = sa.Column( sa.Integer, default=0 )

         

         @property

         def bitwise_manager_field_a(self):

                if self._bitwise_manager_field_a is None:

                      self. bitwise_manager_field_a = BitwiseClassFieldA(self.bitwise_field_a)

                return self.bitwise_manager_field_a

         _ bitwise_manager_field_a = None

* when i do saves , i call the manager's `encode` function

      instance.bitwise_field_a = instance.bitwise_manager_field_a.encode()

anyways, when I was trying to get a TypeDecorator working, I was focused on the Integer field having a decorator and managing my object -- not on defining many column attributes like you are.  i think that might be an easier avenue, because you want to affect the column itself for these comparisons.

[Alex]

The hybrid property and custom comparator approach works like a charm, I had an inkling that hybrid propertyies were the correct approach but hadn't really thought about subclassing hybrid_property, this has helped me understand the custom types architecture a lot, thank very much.

Alex

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/Mu9m1dVU1Gw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Alex]

Hmm, looks like I spoke too soon. Testing against a SQLite database the hybrid attribute approach works fine but I'm having some trouble with SQL Server. Basically, given the structure that Michael laid out, the following query:

    model = TestModel(
        flags=1
    )
    session.add(model)
    session.commit()
    result = session.query(TestModel).filter(TestModel.flag_one == True).first()

Resullts in this exception:

ProgrammingError: (ProgrammingError) (102, "Incorrect syntax near '='.DB-Lib error message 102, severity 15:
General SQL Server error: Check messages from the SQL Server
") 'SELECT TOP 1 testmodel.id AS testmodel_id, testmodel.flags AS testmodel_flags
FROM testmodel
WHERE ((testmodel.flags & %(flags_1)s) > %(param_1)s) = 1' {'flags_1': 1, 'param_1': 0}

So it looks like the equality comparison is coercing True to 1, I can't figure out which hook I need to use to change this. I've tried to use coerce_compared_value with no effect.

Alex

[Michael Bayer]

On Apr 1, 2014, at 6:34 AM, Alex <quix...@googlemail.com> wrote:

Hmm, looks like I spoke too soon. Testing against a SQLite database the hybrid attribute approach works fine but I'm having some trouble with SQL Server. Basically, given the structure that Michael laid out, the following query:

    model = TestModel(
        flags=1
    )
    session.add(model)
    session.commit()
    result = session.query(TestModel).filter(TestModel.flag_one == True).first()

Resullts in this exception:

ProgrammingError: (ProgrammingError) (102, "Incorrect syntax near '='.DB-Lib error message 102, severity 15:
General SQL Server error: Check messages from the SQL Server
") 'SELECT TOP 1 testmodel.id AS testmodel_id, testmodel.flags AS testmodel_flags
FROM testmodel
WHERE ((testmodel.flags & %(flags_1)s) > %(param_1)s) = 1' {'flags_1': 1, 'param_1': 0}

So it looks like the equality comparison is coercing True to 1, I can't figure out which hook I need to use to change this. I've tried to use coerce_compared_value with no effect.

SQL server doesn’t have a “boolean” type, you can only use one and zero.   the issue there is more likely the bitwise comparison operators or the nesting of the parenthesis.   get that query to work first at the SQL server console to figure out the syntax it wants.

[Alex]

Yeah, its a very frustrating aspect of SQL Server. Anyway, a query that works is the following:

SELECT testmodel.id AS testmodel_id, testmodel.flags AS testmodel_flags
FROM testmodel
WHERE (testmodel.flags & 1) > 0

I can get sqlalchemy to emit this like so:

session.query(TestModel).filter(TestModel.flag_one)

And the negation of it:

session.query(TestModel).filter(not_(TestModel.flag_one))

I can't figure out how to emit the required SQL on comparison with a boolean value though.

Alex

[Michael Bayer]

On Apr 1, 2014, at 11:28 AM, Alex <quix...@googlemail.com> wrote:

Yeah, its a very frustrating aspect of SQL Server. Anyway, a query that works is the following:

SELECT testmodel.id AS testmodel_id, testmodel.flags AS testmodel_flags
FROM testmodel
WHERE (testmodel.flags & 1) > 0

I can get sqlalchemy to emit this like so:

session.query(TestModel).filter(TestModel.flag_one)

And the negation of it:

session.query(TestModel).filter(not_(TestModel.flag_one))

I can't figure out how to emit the required SQL on comparison with a boolean value though.

well we’ve tried to improve boolean rendering to work around this “= 1”  “is true” thing, but I’ve identified more bugs in that regard, and even with the bug fixes that doesn’t really help this specific situation. 

Here, you can have:

filter(TestModel.flag_one)

filter(~TestModel.flag_one)

or:

filter(TestModel.flag_one == True)

filter(TestModel.flag_one == False)

to have both at the same time, is more work.  You need to build an __eq__() method that swallows the “True” and negates on “False”, and playing with this there some various ways to do this, trying a few here I’m having semi-success.   The most foolproof would be to build another special type that does this.