aboutsummaryrefslogtreecommitdiffstats
path: root/debug_toolbar/panels/sql
diff options
context:
space:
mode:
Diffstat (limited to 'debug_toolbar/panels/sql')
-rw-r--r--debug_toolbar/panels/sql/__init__.py1
-rw-r--r--debug_toolbar/panels/sql/forms.py90
-rw-r--r--debug_toolbar/panels/sql/panel.py210
-rw-r--r--debug_toolbar/panels/sql/tracking.py168
-rw-r--r--debug_toolbar/panels/sql/utils.py37
-rw-r--r--debug_toolbar/panels/sql/views.py113
6 files changed, 619 insertions, 0 deletions
diff --git a/debug_toolbar/panels/sql/__init__.py b/debug_toolbar/panels/sql/__init__.py
new file mode 100644
index 0000000..90e05cb
--- /dev/null
+++ b/debug_toolbar/panels/sql/__init__.py
@@ -0,0 +1 @@
+from debug_toolbar.panels.sql.panel import SQLDebugPanel # noqa
diff --git a/debug_toolbar/panels/sql/forms.py b/debug_toolbar/panels/sql/forms.py
new file mode 100644
index 0000000..c18be0c
--- /dev/null
+++ b/debug_toolbar/panels/sql/forms.py
@@ -0,0 +1,90 @@
+from __future__ import unicode_literals
+
+import json
+import hashlib
+
+from django import forms
+from django.conf import settings
+from django.db import connections
+from django.utils.encoding import force_text
+from django.utils.functional import cached_property
+from django.core.exceptions import ValidationError
+
+from debug_toolbar.panels.sql.utils import reformat_sql
+
+
+class SQLSelectForm(forms.Form):
+ """
+ Validate params
+
+ sql: The sql statement with interpolated params
+ raw_sql: The sql statement with placeholders
+ params: JSON encoded parameter values
+ duration: time for SQL to execute passed in from toolbar just for redisplay
+ hash: the hash of (secret + sql + params) for tamper checking
+ """
+ sql = forms.CharField()
+ raw_sql = forms.CharField()
+ params = forms.CharField()
+ alias = forms.CharField(required=False, initial='default')
+ duration = forms.FloatField()
+ hash = forms.CharField()
+
+ def __init__(self, *args, **kwargs):
+ initial = kwargs.get('initial', None)
+
+ if initial is not None:
+ initial['hash'] = self.make_hash(initial)
+
+ super(SQLSelectForm, self).__init__(*args, **kwargs)
+
+ for name in self.fields:
+ self.fields[name].widget = forms.HiddenInput()
+
+ def clean_raw_sql(self):
+ value = self.cleaned_data['raw_sql']
+
+ if not value.lower().strip().startswith('select'):
+ raise ValidationError("Only 'select' queries are allowed.")
+
+ return value
+
+ def clean_params(self):
+ value = self.cleaned_data['params']
+
+ try:
+ return json.loads(value)
+ except ValueError:
+ raise ValidationError('Is not valid JSON')
+
+ def clean_alias(self):
+ value = self.cleaned_data['alias']
+
+ if value not in connections:
+ raise ValidationError("Database alias '%s' not found" % value)
+
+ return value
+
+ def clean_hash(self):
+ hash = self.cleaned_data['hash']
+
+ if hash != self.make_hash(self.data):
+ raise ValidationError('Tamper alert')
+
+ return hash
+
+ def reformat_sql(self):
+ return reformat_sql(self.cleaned_data['sql'])
+
+ def make_hash(self, data):
+ params = (force_text(settings.SECRET_KEY) +
+ force_text(data['sql']) + force_text(data['params']))
+ return hashlib.sha1(params.encode('utf-8')).hexdigest()
+
+ @property
+ def connection(self):
+ return connections[self.cleaned_data['alias']]
+
+ @cached_property
+ def cursor(self):
+ return self.connection.cursor()
diff --git a/debug_toolbar/panels/sql/panel.py b/debug_toolbar/panels/sql/panel.py
new file mode 100644
index 0000000..cb80901
--- /dev/null
+++ b/debug_toolbar/panels/sql/panel.py
@@ -0,0 +1,210 @@
+from __future__ import unicode_literals
+
+import uuid
+from copy import copy
+
+from django.conf.urls import patterns, url
+from django.db import connections
+from django.utils.translation import ugettext_lazy as _, ungettext_lazy as __
+
+from debug_toolbar.panels import DebugPanel
+from debug_toolbar.panels.sql.forms import SQLSelectForm
+from debug_toolbar.utils import render_stacktrace
+from debug_toolbar.panels.sql.utils import reformat_sql
+from debug_toolbar.panels.sql.tracking import CursorWrapper
+
+
+def get_isolation_level_display(engine, level):
+ if engine == 'psycopg2':
+ import psycopg2.extensions
+ choices = {
+ psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT: _('Autocommit'),
+ psycopg2.extensions.ISOLATION_LEVEL_READ_UNCOMMITTED: _('Read uncommitted'),
+ psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED: _('Read committed'),
+ psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ: _('Repeatable read'),
+ psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE: _('Serializable'),
+ }
+ else:
+ raise ValueError(engine)
+ return choices.get(level)
+
+
+def get_transaction_status_display(engine, level):
+ if engine == 'psycopg2':
+ import psycopg2.extensions
+ choices = {
+ psycopg2.extensions.TRANSACTION_STATUS_IDLE: _('Idle'),
+ psycopg2.extensions.TRANSACTION_STATUS_ACTIVE: _('Active'),
+ psycopg2.extensions.TRANSACTION_STATUS_INTRANS: _('In transaction'),
+ psycopg2.extensions.TRANSACTION_STATUS_INERROR: _('In error'),
+ psycopg2.extensions.TRANSACTION_STATUS_UNKNOWN: _('Unknown'),
+ }
+ else:
+ raise ValueError(engine)
+ return choices.get(level)
+
+
+class SQLDebugPanel(DebugPanel):
+ """
+ Panel that displays information about the SQL queries run while processing
+ the request.
+ """
+ name = 'SQL'
+ template = 'debug_toolbar/panels/sql.html'
+ has_content = True
+
+ def __init__(self, *args, **kwargs):
+ super(SQLDebugPanel, self).__init__(*args, **kwargs)
+ self._offset = dict((k, len(connections[k].queries)) for k in connections)
+ self._sql_time = 0
+ self._num_queries = 0
+ self._queries = []
+ self._databases = {}
+ self._transaction_status = {}
+ self._transaction_ids = {}
+
+ def get_transaction_id(self, alias):
+ if alias not in connections:
+ return
+ conn = connections[alias].connection
+ if not conn:
+ return
+
+ engine = conn.__class__.__module__.split('.', 1)[0]
+ if engine == 'psycopg2':
+ cur_status = conn.get_transaction_status()
+ else:
+ raise ValueError(engine)
+
+ last_status = self._transaction_status.get(alias)
+ self._transaction_status[alias] = cur_status
+
+ if not cur_status:
+ # No available state
+ return None
+
+ if cur_status != last_status:
+ if cur_status:
+ self._transaction_ids[alias] = uuid.uuid4().hex
+ else:
+ self._transaction_ids[alias] = None
+
+ return self._transaction_ids[alias]
+
+ def record(self, alias, **kwargs):
+ self._queries.append((alias, kwargs))
+ if alias not in self._databases:
+ self._databases[alias] = {
+ 'time_spent': kwargs['duration'],
+ 'num_queries': 1,
+ }
+ else:
+ self._databases[alias]['time_spent'] += kwargs['duration']
+ self._databases[alias]['num_queries'] += 1
+ self._sql_time += kwargs['duration']
+ self._num_queries += 1
+
+ @classmethod
+ def get_urls(cls):
+ return patterns('debug_toolbar.panels.sql.views', # noqa
+ url(r'^sql_select/$', 'sql_select', name='sql_select'),
+ url(r'^sql_explain/$', 'sql_explain', name='sql_explain'),
+ url(r'^sql_profile/$', 'sql_profile', name='sql_profile'),
+ )
+
+ def nav_title(self):
+ return _('SQL')
+
+ def nav_subtitle(self):
+ return __("%d query in %.2fms", "%d queries in %.2fms",
+ self._num_queries) % (self._num_queries, self._sql_time)
+
+ def title(self):
+ count = len(self._databases)
+ return __('SQL Queries from %(count)d connection',
+ 'SQL Queries from %(count)d connections',
+ count) % dict(count=count)
+
+ def enable_instrumentation(self):
+ # This is thread-safe because database connections are thread-local.
+ for connection in connections.all():
+ if not hasattr(connection, '_djdt_cursor'):
+ connection._djdt_cursor = connection.cursor
+ connection.cursor = lambda: CursorWrapper(
+ connection._djdt_cursor(), connection, self)
+
+ def disable_instrumentation(self):
+ for connection in connections.all():
+ if hasattr(connection, '_djdt_cursor'):
+ del connection._djdt_cursor
+ del connection.cursor
+
+ def process_response(self, request, response):
+ if self._queries:
+ width_ratio_tally = 0
+ factor = int(256.0 / (len(self._databases) * 2.5))
+ for n, db in enumerate(self._databases.values()):
+ rgb = [0, 0, 0]
+ color = n % 3
+ rgb[color] = 256 - n / 3 * factor
+ nn = color
+ # XXX: pretty sure this is horrible after so many aliases
+ while rgb[color] < factor:
+ nc = min(256 - rgb[color], 256)
+ rgb[color] += nc
+ nn += 1
+ if nn > 2:
+ nn = 0
+ rgb[nn] = nc
+ db['rgb_color'] = rgb
+
+ trans_ids = {}
+ trans_id = None
+ i = 0
+ for alias, query in self._queries:
+ trans_id = query.get('trans_id')
+ last_trans_id = trans_ids.get(alias)
+
+ if trans_id != last_trans_id:
+ if last_trans_id:
+ self._queries[(i - 1)][1]['ends_trans'] = True
+ trans_ids[alias] = trans_id
+ if trans_id:
+ query['starts_trans'] = True
+ if trans_id:
+ query['in_trans'] = True
+
+ query['alias'] = alias
+ if 'iso_level' in query:
+ query['iso_level'] = get_isolation_level_display(query['engine'],
+ query['iso_level'])
+ if 'trans_status' in query:
+ query['trans_status'] = get_transaction_status_display(query['engine'],
+ query['trans_status'])
+
+ query['form'] = SQLSelectForm(auto_id=None, initial=copy(query))
+
+ if query['sql']:
+ query['sql'] = reformat_sql(query['sql'])
+ query['rgb_color'] = self._databases[alias]['rgb_color']
+ try:
+ query['width_ratio'] = (query['duration'] / self._sql_time) * 100
+ query['width_ratio_relative'] = (
+ 100.0 * query['width_ratio'] / (100.0 - width_ratio_tally))
+ except ZeroDivisionError:
+ query['width_ratio'] = 0
+ query['width_ratio_relative'] = 0
+ query['start_offset'] = width_ratio_tally
+ query['end_offset'] = query['width_ratio'] + query['start_offset']
+ width_ratio_tally += query['width_ratio']
+ query['stacktrace'] = render_stacktrace(query['stacktrace'])
+ i += 1
+
+ if trans_id:
+ self._queries[(i - 1)][1]['ends_trans'] = True
+
+ self.record_stats({
+ 'databases': sorted(self._databases.items(), key=lambda x: -x[1]['time_spent']),
+ 'queries': [q for a, q in self._queries],
+ 'sql_time': self._sql_time,
+ })
diff --git a/debug_toolbar/panels/sql/tracking.py b/debug_toolbar/panels/sql/tracking.py
new file mode 100644
index 0000000..fd56ff9
--- /dev/null
+++ b/debug_toolbar/panels/sql/tracking.py
@@ -0,0 +1,168 @@
+from __future__ import unicode_literals
+
+import sys
+
+import json
+from threading import local
+from time import time
+
+from django.template import Node
+from django.utils.encoding import force_text
+from django.utils import six
+
+from debug_toolbar.utils import tidy_stacktrace, get_template_info, get_stack
+from debug_toolbar.utils import settings as dt_settings
+
+
+class SQLQueryTriggered(Exception):
+ """Thrown when template panel triggers a query"""
+ pass
+
+
+class ThreadLocalState(local):
+ def __init__(self):
+ self.enabled = True
+
+ @property
+ def Wrapper(self):
+ if self.enabled:
+ return NormalCursorWrapper
+ return ExceptionCursorWrapper
+
+ def recording(self, v):
+ self.enabled = v
+
+
+state = ThreadLocalState()
+recording = state.recording # export function
+
+
+def CursorWrapper(*args, **kwds): # behave like a class
+ return state.Wrapper(*args, **kwds)
+
+
+class ExceptionCursorWrapper(object):
+ """
+ Wraps a cursor and raises an exception on any operation.
+ Used in Templates panel.
+ """
+ def __init__(self, cursor, db, logger):
+ pass
+
+ def __getattr__(self, attr):
+ raise SQLQueryTriggered()
+
+
+class NormalCursorWrapper(object):
+ """
+ Wraps a cursor and logs queries.
+ """
+
+ def __init__(self, cursor, db, logger):
+ self.cursor = cursor
+ # Instance of a BaseDatabaseWrapper subclass
+ self.db = db
+ # logger must implement a ``record`` method
+ self.logger = logger
+
+ def _quote_expr(self, element):
+ if isinstance(element, six.string_types):
+ return "'%s'" % force_text(element).replace("'", "''")
+ else:
+ return repr(element)
+
+ def _quote_params(self, params):
+ if not params:
+ return params
+ if isinstance(params, dict):
+ return dict((key, self._quote_expr(value))
+ for key, value in params.items())
+ return list(map(self._quote_expr, params))
+
+ def _decode(self, param):
+ try:
+ return force_text(param, strings_only=True)
+ except UnicodeDecodeError:
+ return '(encoded string)'
+
+ def execute(self, sql, params=()):
+ start_time = time()
+ try:
+ return self.cursor.execute(sql, params)
+ finally:
+ stop_time = time()
+ duration = (stop_time - start_time) * 1000
+ if dt_settings.CONFIG['ENABLE_STACKTRACES']:
+ stacktrace = tidy_stacktrace(reversed(get_stack()))
+ else:
+ stacktrace = []
+ _params = ''
+ try:
+ _params = json.dumps(list(map(self._decode, params)))
+ except Exception:
+ pass # object not JSON serializable
+
+ template_info = None
+ cur_frame = sys._getframe().f_back
+ try:
+ while cur_frame is not None:
+ if cur_frame.f_code.co_name == 'render':
+ node = cur_frame.f_locals['self']
+ if isinstance(node, Node):
+ template_info = get_template_info(node.source)
+ break
+ cur_frame = cur_frame.f_back
+ except Exception:
+ pass
+ del cur_frame
+
+ alias = getattr(self.db, 'alias', 'default')
+ conn = self.db.connection
+ # HACK: avoid imports
+ if conn:
+ engine = conn.__class__.__module__.split('.', 1)[0]
+ else:
+ engine = 'unknown'
+
+ params = {
+ 'engine': engine,
+ 'alias': alias,
+ 'sql': self.db.ops.last_executed_query(
+ self.cursor, sql, self._quote_params(params)),
+ 'duration': duration,
+ 'raw_sql': sql,
+ 'params': _params,
+ 'stacktrace': stacktrace,
+ 'start_time': start_time,
+ 'stop_time': stop_time,
+ 'is_slow': duration > dt_settings.CONFIG['SQL_WARNING_THRESHOLD'],
+ 'is_select': sql.lower().strip().startswith('select'),
+ 'template_info': template_info,
+ }
+
+ if engine == 'psycopg2':
+ # If an erroneous query was ran on the connection, it might
+ # be in a state where checking isolation_level raises an
+ # exception.
+ try:
+ iso_level = conn.isolation_level
+ except conn.InternalError:
+ iso_level = 'unknown'
+ params.update({
+ 'trans_id': self.logger.get_transaction_id(alias),
+ 'trans_status': conn.get_transaction_status(),
+ 'iso_level': iso_level,
+ 'encoding': conn.encoding,
+ })
+
+ # We keep `sql` to maintain backwards compatibility
+ self.logger.record(**params)
+
+ def executemany(self, sql, param_list):
+ return self.cursor.executemany(sql, param_list)
+
+ def __getattr__(self, attr):
+ return getattr(self.cursor, attr)
+
+ def __iter__(self):
+ return iter(self.cursor)
diff --git a/debug_toolbar/panels/sql/utils.py b/debug_toolbar/panels/sql/utils.py
new file mode 100644
index 0000000..00728a3
--- /dev/null
+++ b/debug_toolbar/panels/sql/utils.py
@@ -0,0 +1,37 @@
+from __future__ import unicode_literals
+
+import re
+
+from django.utils.html import escape
+
+import sqlparse
+from sqlparse import tokens as T
+
+
+class BoldKeywordFilter:
+ """sqlparse filter to bold SQL keywords"""
+ def process(self, stack, stream):
+ """Process the token stream"""
+ for token_type, value in stream:
+ is_keyword = token_type in T.Keyword
+ if is_keyword:
+ yield T.Text, '<strong>'
+ yield token_type, escape(value)
+ if is_keyword:
+ yield T.Text, '</strong>'
+
+
+def reformat_sql(sql):
+ stack = sqlparse.engine.FilterStack()
+ stack.preprocess.append(BoldKeywordFilter()) # add our custom filter
+ stack.postprocess.append(sqlparse.filters.SerializerUnicode()) # tokens -> strings
+ return swap_fields(''.join(stack.run(sql)))
+
+
+def swap_fields(sql):
+ expr = r'SELECT</strong> (...........*?) <strong>FROM'
+ subs = (r'SELECT</strong> '
+ r'<a class="djDebugUncollapsed djDebugToggle" href="#">&#8226;&#8226;&#8226;</a> '
+ r'<a class="djDebugCollapsed djDebugToggle" href="#">\1</a> '
+ r'<strong>FROM')
+ return re.sub(expr, subs, sql)
diff --git a/debug_toolbar/panels/sql/views.py b/debug_toolbar/panels/sql/views.py
new file mode 100644
index 0000000..346cf6e
--- /dev/null
+++ b/debug_toolbar/panels/sql/views.py
@@ -0,0 +1,113 @@
+from __future__ import unicode_literals
+
+from django.http import HttpResponseBadRequest
+from django.shortcuts import render
+from django.views.decorators.csrf import csrf_exempt
+
+from debug_toolbar.panels.sql.forms import SQLSelectForm
+
+
+@csrf_exempt
+def sql_select(request):
+ """Returns the output of the SQL SELECT statement"""
+ form = SQLSelectForm(request.POST or None)
+
+ if form.is_valid():
+ sql = form.cleaned_data['raw_sql']
+ params = form.cleaned_data['params']
+ cursor = form.cursor
+ cursor.execute(sql, params)
+ headers = [d[0] for d in cursor.description]
+ result = cursor.fetchall()
+ cursor.close()
+ context = {
+ 'result': result,
+ 'sql': form.reformat_sql(),
+ 'duration': form.cleaned_data['duration'],
+ 'headers': headers,
+ 'alias': form.cleaned_data['alias'],
+ }
+ return render(request, 'debug_toolbar/panels/sql_select.html', context)
+ return HttpResponseBadRequest('Form errors')
+
+
+@csrf_exempt
+def sql_explain(request):
+ """Returns the output of the SQL EXPLAIN on the given query"""
+ form = SQLSelectForm(request.POST or None)
+
+ if form.is_valid():
+ sql = form.cleaned_data['raw_sql']
+ params = form.cleaned_data['params']
+ cursor = form.cursor
+
+ conn = form.connection
+ engine = conn.__class__.__module__.split('.', 1)[0]
+
+ if engine == "sqlite3":
+ # SQLite's EXPLAIN dumps the low-level opcodes generated for a query;
+ # EXPLAIN QUERY PLAN dumps a more human-readable summary
+ # See http://www.sqlite.org/lang_explain.html for details
+ cursor.execute("EXPLAIN QUERY PLAN %s" % (sql,), params)
+ elif engine == "psycopg2":
+ cursor.execute("EXPLAIN ANALYZE %s" % (sql,), params)
+ else:
+ cursor.execute("EXPLAIN %s" % (sql,), params)
+
+ headers = [d[0] for d in cursor.description]
+ result = cursor.fetchall()
+ cursor.close()
+ context = {
+ 'result': result,
+ 'sql': form.reformat_sql(),
+ 'duration': form.cleaned_data['duration'],
+ 'headers': headers,
+ 'alias': form.cleaned_data['alias'],
+ }
+ return render(request, 'debug_toolbar/panels/sql_explain.html', context)
+ return HttpResponseBadRequest('Form errors')
+
+
+@csrf_exempt
+def sql_profile(request):
+ """Returns the output of running the SQL and getting the profiling statistics"""
+ form = SQLSelectForm(request.POST or None)
+
+ if form.is_valid():
+ sql = form.cleaned_data['raw_sql']
+ params = form.cleaned_data['params']
+ cursor = form.cursor
+ result = None
+ headers = None
+ result_error = None
+ try:
+ 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()
+ except Exception:
+ result_error = "Profiling is either not available or not supported by your database."
+ cursor.close()
+ context = {
+ 'result': result,
+ 'result_error': result_error,
+ 'sql': form.reformat_sql(),
+ 'duration': form.cleaned_data['duration'],
+ 'headers': headers,
+ 'alias': form.cleaned_data['alias'],
+ }
+ return render(request, 'debug_toolbar/panels/sql_profile.html', context)
+ return HttpResponseBadRequest('Form errors')
generated by cgit v1.2.3 (git 2.25.1) at 2025-11-01 14:12:44 +0000