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.
