Djangoists:
Suppose I want a test to fail if the database would respond to a given
QuerySet without using my favorite index.
This test case would attempt, at developer test time, to solve the
same problem as a "soak test" would, at acceptance testing time. So
the earlier a test fails - without soaking! - the better. Even if my
favorite index works, today, an innocent-looking refactor, tomorrow,
might somehow prevent the database from using it.
To solve that problem (for tests that use MySQL), add an assertion
like this:
self.assert_performant(Order.objects.all(),
must_have='my_favorite_index')
When that fails, it emits all this:
AssertionError: SELECT `orders_order`.`id`, ... ORDER BY
`orders_order`.`purchasedate` DESC
should contain `my_favorite_index` in EXPLAIN
((1L, u'SIMPLE', u'orders_order', u'ALL', None, None, None, None, 5L,
u'Using filesort'),)
The assertion converted Order.objects.all() into its SQL SELECT
statement, and then ran EXPLAIN on this. Because 'my_favorite_index'
did not appear anywhere in the results, the assertion faults, and
reports all its constituent details.
To deny that an EXPLAIN may contain a given string, such as the
dreaded 'ALL' (indicating a query that hits every record in a table,
no matter how long), use without='ALL'. The default behavior, with no
arguments, is without='ALL'. And the assertion returns both the query
and the explanation (as a tuple of tuples of tuples), for more
arbitrarily complex assertions.
The sauce:
def assert_performant(self, qs, without=None, must_have=None):
query = str(qs.query)
from django.db import connection, transaction
cursor = connection.cursor()
cursor.execute('EXPLAIN ' + query)
report = cursor.fetchall()
results = '` in EXPLAIN\n\n' + repr(report)
if not without and not must_have: without = 'ALL'
if must_have:
self.assertTrue( must_have in repr(report), query + '\n\n
should contain `' +
must_have + results)
if without:
self.assertFalse( without in repr(report), query + '\n\n
should not contain `' +
without + results )
return (query, report)
Warning: Developer tests should run with short, suite fixtures.
However, a database may see fit to skip and index and read a whole
table, if it fits in memory. Oh, and even worse, developer tests
should use sqlite3, to permit TDD speed, and its EXPLAIN command
returns ... something different.
Other than that, I have used this technique, copiously, on other
projects, and whenever that assertion failed it indeed saved us from
accidentally deploying a slow SQL statement to our server!
--
Phlip
http://zeekland.zeroplayer.com/Pigleg_Too/1
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
