I’ve been working on fleshing out the tests for Django-SQLAlchemy and right away I discovered a problem. Incidentally, this is why I love unit testing. The problems was that a very simple test of the query contains filter syntax was failing. I was expecting four items but was getting five back. After digging into it I discovered the problem was with SQLite. SQLite doesn’t seem to respect case when using the LIKE syntax.
Let us say we have the following setup:
from apps.blog.models import Category
class TestContains(object):
def setup(self):
Category.__table__.insert().execute({'name': 'Python'},
{'name': 'PHP'}, {'name': 'Ruby'}, {'name': 'Smalltalk'},
{'name': 'CSharp'}, {'name': 'Modula'}, {'name': 'Algol'},
{'name': 'Forth'}, {'name': 'Pascal'})
a query with contains in Django on SQLite, like the following, will return five results, instead of the expected four results:
>>> Category.objects.filter(name__contains='a').count()
2008-03-28 20:41:43,228 INFO sqlalchemy.engine.base.Engine.0x..f0 BEGIN
2008-03-28 20:41:43,229 INFO sqlalchemy.engine.base.Engine.0x..f0 SELECT count(foo_category.id) AS count_1
FROM foo_category
WHERE foo_category.name LIKE ?
2008-03-28 20:41:43,229 INFO sqlalchemy.engine.base.Engine.0x..f0 ['%a%']
5
(Incidentally that’s actually going through the Django-SQLAlchemy backend and not Django’s ORM.)
So I kind of hemmed and hawed for a bit trying to figure out how I could make this work under different databases, at least from the testing standpoint. It finally occurred to me that SQLAlchemy has to be able to test their ORM against a lot of different backends so they must have a nice solution to this.
Decorators to the Rescue
It turns out that SQLAlchemy has implemented an elegant set of decorators for just this problem. They were also written in such a way that it was quite easy for me to extract them and modify them slightly to work with Django-SQLAlchemy tests. So what’s in this package?
- fails_if(callable_) – Mark a test as expected to fail if
callable_ returns True.
- future – Mark a test as expected to unconditionally fail.
- fails_on(dbs) – Mark a test as expected to fail on one or more database implementations.
- fails_on_everything_except(dbs) – Mark a test as expected to fail on most database implementations.
- unsupported(dbs) – Mark a test as unsupported by one or more database implementations.
- exclude(db, op, spec) – Mark a test as unsupported by specific database server versions. This decorator allows an impressive list of options, for example
@exclude('mydb', '<', (1,0))
There’s a lot more than that, but I will not detail them all here. If you want to dig through it all check out test/testlib/testing.py module.
The Implementation
So once I was able to extract and modify these decorators I ended up with very elegant syntax for my tests. Here is a sample:
class TestContains(object):
def setup(self):
Category.__table__.insert().execute({'name': 'Python'},
{'name': 'PHP'}, {'name': 'Ruby'}, {'name': 'Smalltalk'},
{'name': 'CSharp'}, {'name': 'Modula'}, {'name': 'Algol'},
{'name': 'Forth'}, {'name': 'Pascal'})
@fails_on('sqlite')
def test_should_contain_string_in_name(self):
assert 4 == Category.objects.filter(name__contains='a').count()
assert 1 == Category.objects.filter(name__contains='A').count()
@fails_on_everything_except('sqlite')
def test_should_contain_string_in_name_on_sqlite(self):
assert 5 == Category.objects.filter(name__contains='a').count()
assert 5 == Category.objects.filter(name__contains='A').count()
def test_should_contain_string_in_name_regardless_of_case(self):
assert 5 == Category.objects.filter(name__icontains='a').count()
assert 5 == Category.objects.filter(name__icontains='A').count()
Special thanks goes to Mike Bayer and the rest of the contributors to SQLAlchemy for providing such a great solution. I am constantly amazed by their code.
