From 49a177a41f790a494a933963fd7b2f5216f98a85 Mon Sep 17 00:00:00 2001 From: Rob Hudson Date: Mon, 6 Oct 2008 12:54:02 -0700 Subject: Updating SQL output a bit now that I've found more queries to test against. --- debug_toolbar/panels/sql.py | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'debug_toolbar') diff --git a/debug_toolbar/panels/sql.py b/debug_toolbar/panels/sql.py index 22d65a7..a4c18f6 100644 --- a/debug_toolbar/panels/sql.py +++ b/debug_toolbar/panels/sql.py @@ -71,9 +71,9 @@ def reformat_sql(sql): sql = sql.replace('`,`', '`, `') sql = sql.replace('SELECT ', 'SELECT\n\t') sql = sql.replace('` FROM ', '`\nFROM\n\t') - sql = sql.replace('` WHERE ', '`\nWHERE\n\t') - sql = sql.replace('` INNER JOIN ', '`\nINNER JOIN\n\t') - sql = sql.replace('` OUTER JOIN ', '`\nOUTER JOIN\n\t') + sql = sql.replace(' WHERE ', '\nWHERE\n\t') + sql = sql.replace(' INNER JOIN ', '\nINNER JOIN\n\t') + sql = sql.replace(' OUTER JOIN ', '\nOUTER JOIN\n\t') sql = sql.replace(' ORDER BY ', '\nORDER BY\n\t') # Use Pygments to highlight SQL if it's available try: -- cgit v1.2.3 From e9271bf69ab50cb24e38ac2205847c8d32b83ca8 Mon Sep 17 00:00:00 2001 From: Rob Hudson Date: Mon, 6 Oct 2008 13:07:56 -0700 Subject: Just like EXPLAIN, adding the output of straight SELECT statements so you can view the raw SQL output. --- .../templates/debug_toolbar/panels/sql.html | 1 + .../templates/debug_toolbar/panels/sql_select.html | 30 +++++++++++++++++++++ debug_toolbar/urls.py | 1 + debug_toolbar/views.py | 31 ++++++++++++++++++++++ 4 files changed, 63 insertions(+) create mode 100644 debug_toolbar/templates/debug_toolbar/panels/sql_select.html (limited to 'debug_toolbar') diff --git a/debug_toolbar/templates/debug_toolbar/panels/sql.html b/debug_toolbar/templates/debug_toolbar/panels/sql.html index 6a2d077..2abb68f 100644 --- a/debug_toolbar/templates/debug_toolbar/panels/sql.html +++ b/debug_toolbar/templates/debug_toolbar/panels/sql.html @@ -13,6 +13,7 @@ {{ query.time|floatformat:"4" }} {% if query.params %} + SELECT EXPLAIN {% endif %} diff --git a/debug_toolbar/templates/debug_toolbar/panels/sql_select.html b/debug_toolbar/templates/debug_toolbar/panels/sql_select.html new file mode 100644 index 0000000..73109ef --- /dev/null +++ b/debug_toolbar/templates/debug_toolbar/panels/sql_select.html @@ -0,0 +1,30 @@ +« Back +

SQL Selected

+
+
Executed SQL
+ 
{{ sql|safe }}
+
Time
+
{{ time }} ms
+
+{% if result %} + + + + {% for h in headers %} + + {% endfor %} + + + + {% for row in result %} + + {% for column in row %} + + {% endfor %} + + {% endfor %} + +
{{ h|upper }}
{{ column|escape }}
+{% else %} +

Empty set

+{% endif %} diff --git a/debug_toolbar/urls.py b/debug_toolbar/urls.py index e0e4b7a..437d36b 100644 --- a/debug_toolbar/urls.py +++ b/debug_toolbar/urls.py @@ -9,5 +9,6 @@ from django.conf import settings urlpatterns = patterns('', url(r'^__debug__/m/(.*)$', 'debug_toolbar.views.debug_media'), + url(r'^__debug__/sql_select/$', 'debug_toolbar.views.sql_select', name='sql_select'), url(r'^__debug__/sql_explain/$', 'debug_toolbar.views.sql_explain', name='sql_explain'), ) diff --git a/debug_toolbar/views.py b/debug_toolbar/views.py index b67a70b..4b666e1 100644 --- a/debug_toolbar/views.py +++ b/debug_toolbar/views.py @@ -20,6 +20,37 @@ def debug_media(request, path): root = os.path.join(parent, 'media') return django.views.static.serve(request, path, root) +def sql_select(request): + """ + Returns the output of the SQL SELECT statement. + + Expected GET variables: + sql: urlencoded sql with positional arguments + params: JSON encoded parameter values + time: time for SQL to execute passed in from toolbar just for redisplay + hash: the hash of (secret + sql + params) for tamper checking + """ + from debug_toolbar.panels.sql import reformat_sql + sql = request.GET.get('sql', '') + params = request.GET.get('params', '') + hash = sha_constructor(settings.SECRET_KEY + sql + params).hexdigest() + if hash != request.GET.get('hash', ''): + return HttpResponse('

Tamper alert

') # SQL Tampering alert + if sql.lower().startswith('select'): + params = simplejson.loads(params) + cursor = connection.cursor() + cursor.execute(sql, params) + headers = [d[0] for d in cursor.description] + result = cursor.fetchall() + cursor.close() + context = { + 'result': result, + 'sql': reformat_sql(cursor.db.ops.last_executed_query(cursor, sql, params)), + 'time': request.GET.get('time', 0.0), + 'headers': headers, + } + return render_to_response('debug_toolbar/panels/sql_select.html', context) + def sql_explain(request): """ Returns the output of the SQL EXPLAIN on the given query. -- cgit v1.2.3 From a3492e14765e3f898efc26913a8e8c4445a837b4 Mon Sep 17 00:00:00 2001 From: Rob Hudson Date: Mon, 6 Oct 2008 13:53:14 -0700 Subject: Adding query profiling for MySQL. Thanks to Simon Willison for the suggestion. There's a big TODO on this one, which is to not display or attempt to execute the profiling SQL calls if this isn't MySQL and if MySQL isn't >= version 5.0.37. For now it's a nice playground of the profiler if you have MySQL v5.0.37 or higher. --- .../templates/debug_toolbar/panels/sql.html | 1 + .../debug_toolbar/panels/sql_profile.html | 26 ++++++++++++++++ debug_toolbar/urls.py | 1 + debug_toolbar/views.py | 35 ++++++++++++++++++++++ 4 files changed, 63 insertions(+) create mode 100644 debug_toolbar/templates/debug_toolbar/panels/sql_profile.html (limited to 'debug_toolbar') diff --git a/debug_toolbar/templates/debug_toolbar/panels/sql.html b/debug_toolbar/templates/debug_toolbar/panels/sql.html index 2abb68f..d35d83e 100644 --- a/debug_toolbar/templates/debug_toolbar/panels/sql.html +++ b/debug_toolbar/templates/debug_toolbar/panels/sql.html @@ -15,6 +15,7 @@ {% if query.params %} SELECT EXPLAIN + PROFILE {% endif %} {{ query.sql|safe }} diff --git a/debug_toolbar/templates/debug_toolbar/panels/sql_profile.html b/debug_toolbar/templates/debug_toolbar/panels/sql_profile.html new file mode 100644 index 0000000..c1d04ce --- /dev/null +++ b/debug_toolbar/templates/debug_toolbar/panels/sql_profile.html @@ -0,0 +1,26 @@ +« Back +

SQL Profiled

+
+
Executed SQL
+ 
{{ sql|safe }}
+
Time
+
{{ time }} ms
+
+ + + + {% for h in headers %} + + {% endfor %} + + + + {% for row in result %} + + {% for column in row %} + + {% endfor %} + + {% endfor %} + +
{{ h|upper }}
{{ column|escape }}
diff --git a/debug_toolbar/urls.py b/debug_toolbar/urls.py index 437d36b..53a426b 100644 --- a/debug_toolbar/urls.py +++ b/debug_toolbar/urls.py @@ -11,4 +11,5 @@ urlpatterns = patterns('', url(r'^__debug__/m/(.*)$', 'debug_toolbar.views.debug_media'), url(r'^__debug__/sql_select/$', 'debug_toolbar.views.sql_select', name='sql_select'), url(r'^__debug__/sql_explain/$', 'debug_toolbar.views.sql_explain', name='sql_explain'), + url(r'^__debug__/sql_profile/$', 'debug_toolbar.views.sql_profile', name='sql_profile'), ) diff --git a/debug_toolbar/views.py b/debug_toolbar/views.py index 4b666e1..8af879f 100644 --- a/debug_toolbar/views.py +++ b/debug_toolbar/views.py @@ -81,3 +81,38 @@ def sql_explain(request): 'headers': headers, } return render_to_response('debug_toolbar/panels/sql_explain.html', context) + +def sql_profile(request): + """ + Returns the output of running the SQL and getting the profiling statistics. + + Expected GET variables: + sql: urlencoded sql with positional arguments + params: JSON encoded parameter values + time: time for SQL to execute passed in from toolbar just for redisplay + hash: the hash of (secret + sql + params) for tamper checking + """ + from debug_toolbar.panels.sql import reformat_sql + sql = request.GET.get('sql', '') + params = request.GET.get('params', '') + hash = sha_constructor(settings.SECRET_KEY + sql + params).hexdigest() + if hash != request.GET.get('hash', ''): + return HttpResponse('

Tamper alert

') # SQL Tampering alert + if sql.lower().startswith('select'): + params = simplejson.loads(params) + cursor = connection.cursor() + cursor.execute("SET PROFILING=1") # Enable profiling + cursor.execute(sql, params) # Execute SELECT + cursor.execute("SET PROFILING=0") # Disable profiling + # The Query ID should always be 1 here but I'll subselect to get the last one just in case... + cursor.execute("SELECT * FROM information_schema.profiling WHERE query_id=(SELECT query_id FROM information_schema.profiling ORDER BY query_id DESC LIMIT 1)") + headers = [d[0] for d in cursor.description] + result = cursor.fetchall() + cursor.close() + context = { + 'result': result, + 'sql': reformat_sql(cursor.db.ops.last_executed_query(cursor, sql, params)), + 'time': request.GET.get('time', 0.0), + 'headers': headers, + } + return render_to_response('debug_toolbar/panels/sql_explain.html', context) -- cgit v1.2.3