Model.update() fails for BitFields if using flags

[CarstenGrohmann]

I use peewee in an application with millions of objects. I would use Model.update() for an effective mass update
instead of creating objects and running Model.bulk_update().

Currently peewee generates wrong SQL statements for BitFields with flags:

>>> queue = BitTest.update(is_8=False)
>>> queue.sql()
('UPDATE "bittest" SET (("flags" & ?) != ?) = ?', [8, 0, True])
>>> queue.execute()
peewee.OperationalError: near "(": syntax error

Test with SQLite CLI

# sqlite3 test.sqlite
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM "bittest";
1|4
2|2
sqlite> UPDATE "bittest" SET (("flags" & 8) != 0) = True;
Error: near "(": syntax error
sqlite> UPDATE "bittest" SET flags = flags | 8;
sqlite> SELECT * FROM "bittest";
1|12
2|10
sqlite>

Complete example

from peewee import *

db = SqliteDatabase('test.sqlite')


class BitTest(Model):
    class Meta:
        database = db

    id = IntegerField()

    F_0 = 0
    F_1 = 2 ** 0
    F_2 = 2 ** 1
    F_4 = 2 ** 2
    F_8 = 2 ** 3

    flags = BitField(default=0)

    is_0 = flags.flag(F_0)
    is_1 = flags.flag(F_1)
    is_2 = flags.flag(F_2)
    is_4 = flags.flag(F_4)
    is_8 = flags.flag(F_8)


db.create_tables([BitTest])

b1 = BitTest.create(id=1, flags=BitTest.F_1)
b2 = BitTest.create(id=2, flags=BitTest.F_1)

print "\n"
print """Test 1 run: "BitTest.update(flags=BitTest.F_2)" """
query = BitTest.update(flags=BitTest.F_2)
print "Generated SQL: ", query.sql()
try:
    query.execute()
except OperationalError as e:
    print "Update failed: %s" % e
else:
    print "Update executed"

print "\n"
print """Test 2 run: "BitTest.update(flags=BitTest.F_4).where(BitTest.id == 1)" """
query = BitTest.update(flags=BitTest.F_4).where(BitTest.id == 1)
print "Generated SQL: ", query.sql()
try:
    query.execute()
except OperationalError as e:
    print "Update failed: %s" % e
else:
    print "Update executed"

print "\n"
print """Test 3 run: "BitTest.update(is_8=True)" """
query = BitTest.update(is_8=True)
print "Generated SQL: ", query.sql()
try:
    query.execute()
except OperationalError as e:
    print "Update failed: %s" % e
else:
    print "Update executed"

print "\n"
print """Test 4 run: "BitTest.update(is_8=False)" """
query = BitTest.update(is_8=False)
print "Generated SQL: ", query.sql()
try:
    query.execute()
except OperationalError as e:
    print "Update failed: %s" % e
else:
    print "Update executed"

Output example

# ./test_syntax.py


Test 1 run: "BitTest.update(flags=BitTest.F_2)"
Generated SQL:  ('UPDATE "bittest" SET "flags" = ?', [2])
Update executed


Test 2 run: "BitTest.update(flags=BitTest.F_4).where(BitTest.id == 1)"
Generated SQL:  ('UPDATE "bittest" SET "flags" = ? WHERE ("bittest"."id" = ?)', [4, 1])
Update executed


Test 3 run: "BitTest.update(is_8=True)"
Generated SQL:  ('UPDATE "bittest" SET (("flags" & ?) != ?) = ?', [8, 0, True])
Update failed: near "(": syntax error


Test 4 run: "BitTest.update(is_8=False)"
Generated SQL:  ('UPDATE "bittest" SET (("flags" & ?) != ?) = ?', [8, 0, False])
Update failed: near "(": syntax error

[coleifer]

The flags is a descriptor which, when no instance is present (e.g. accessing BitTest.F_4) returns an expression equivalent to BitTest.flags & 4 != 0 which is useful for testing truthiness. What we would need to support your example is to detect that the flag bit is being used in the left-hand side of a set operation -- and this is going to be quite tricky I think.

For now, what you want is:

# clear bit
BitTest.update(flags=BitTest.flags & ~8)

# set bit
BitTest.update(flags=BitTest.flags | 8)

I'm going to think about this, thanks for the report.

[coleifer]

The API I have come up with is:

# Set the "is_8" bit:
BitTest.update(flags=BitTest.is_8.set())

# Clear the "is_8" bit:
BitTest.update(flags=BitTest.is_8.clear())

This change is merged into master.

[CarstenGrohmann]

The changes are great.

Thank you!