Django-users: Programmers can spend a lot of time developing a nice index, and a database can spend many cycles rebalancing it...
...all to see a stray "refactor" blow that index away, by changing a generated SELECT, forcing the database to skip the index. To catch that issue, before soak test time, I invented an assertion which converts a QuerySet into SQL, calls EXPLAIN on it, and inspects the result for tell-tale signatures: query, report = \ self.assert_compliant_sql( Order.objects.all(), sqlite3=dict( TableLock='orders_order' ), mysql=dict( key='awesome_index' ) ) self.assert_regex_contains('ORDER BY.*purchasedate', query) Note the assertion takes one batch of "matchers" per database engine. This permits you to TDD using SQLite3, and integration-test with MySQL, or Brand X. Note also the assertion returns its findings, so you can continue to apply assertions to it. My TODO list is: - optional Django 1.2 - optional not_ prefix on the matchers (not_type='ALL' !) - handle ? escapers - squeak if you match a field not appearing in EXPLAIN's output The source appears below my sig (where I am aware archivers might trim it). Warning: Per my other thread, calling EXPLAIN on an a SQLite3 connection seems to scramble it. Not sure why, but I don't care, because one should not use SQLite3 in production. -- Phlip http://c2.com/cgi/wiki?ZeekLand def assert_compliant_sql(self, qs, **kw): from django.conf import settings query = qs.query.as_sql()[0] # TODO deal with ? escapers from django.db import connection cursor = connection.cursor() # TODO cursor = connections[qs.db].cursor() cursor.execute('EXPLAIN ' + query) report = cursor.fetchall() engine = settings.DATABASE_ENGINE expect = kw.get(engine, None) fail = {} def _regex_in(regex, array): import re for element in array: if re.search(regex, element): return True return False if expect: if engine == 'sqlite3': for row in report: expectation = expect.get(row[1], None) if expectation: if not _regex_in(expectation, row[2:]): fail[row[1]] = expectation else: field_names = [d[0] for d in cursor.description] for key, value in expect.items(): at = field_names.index(key) # TODO squeak if it ain't there if not _regex_in(value, zip(*report)[at]): fail[key] = value if fail: from pprint import pformat results = 'EXPLAIN ' + query + '\n\n' + pformat(report) msg = results + '\n\n fails to match, for the ' + engine + ' engine:\n' + \ pformat(fail) raise AssertionError(msg) return (query, report) -- 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.