File: C:/Program Files/MySQL/MySQL Workbench 8.0/modules/wb_query_analysis_grt.py
# Copyright (c) 2012, 2019, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms, as
# designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
# the GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
from wb import DefineModule
import grt
import mforms
from workbench.log import log_warning, log_error
from workbench.utils import Version
from workbench.graphics.cairo_utils import Context
from explain_renderer import ExplainContext, decode_json
from mforms import Color, ControlBackgroundColor, TextColor, TextBackgroundColor
from workbench.notifications import nc
ModuleInfo = DefineModule(name= "SQLIDEQueryAnalysis", author= "Oracle Corp.", version= "1.0")
class JSONTreeViewer(mforms.TreeView):
def __init__(self):
mforms.TreeView.__init__(self, mforms.TreeAltRowColors|mforms.TreeShowColumnLines|mforms.TreeShowRowLines)
self.add_column(mforms.StringColumnType, "Key", 200)
self.add_column(mforms.StringColumnType, "Value", 300)
self.end_columns()
def display_data(self, data):
def add_nodes(node, create_node, data):
if type(data) is dict:
if node:
node.set_string(1, "<dict>")
for key, value in list(data.items()):
ch = create_node()
ch.set_string(0, key)
add_nodes(ch, ch.add_child, value)
elif type(data) is list:
if node:
node.set_string(1, "<list>")
for i, value in enumerate(data):
ch = create_node()
ch.set_string(0, str(i))
add_nodes(ch, ch.add_child, value)
else:
if not node:
node = create_node()
if type(data) is bool:
node.set_string(1, "true" if data else "false")
else:
node.set_string(1, str(data))
data = decode_json(data)
self.clear()
add_nodes(None, self.add_node, data)
class RenderBox(mforms.PyDrawBox):
def __init__(self, context, scroll):
mforms.PyDrawBox.__init__(self)
self.set_managed()
self.set_release_on_add()
self.scroll = scroll
self.set_instance(self)
self.offset = (0, 0)
self.size = None
self.vertical = False
self.node_spacing = 20
self.econtext = context
self.drag_offset = None
def mouse_down(self, b, x, y):
if b == 0:
self.drag_offset = (x, y)
x -= self.offset[0]
y -= self.offset[1]
self.econtext._canvas.mouse_down(b, x, y)
if self.econtext.overview_mode and b == 0:
self.econtext.mouse_down(x, y)
self.econtext.close_tooltip()
def mouse_up(self, b, x, y):
if b == 0:
self.drag_offset = None
x -= self.offset[0]
y -= self.offset[1]
self.econtext._canvas.mouse_up(b, x, y)
def mouse_move(self, x, y):
x -= self.offset[0]
y -= self.offset[1]
# for drag panning
#if self.drag_offset:
# self.scroll.scroll_to(self.drag_offset[0] - x, self.drag_offset[1] - y)
self.econtext._canvas.mouse_move(x, y)
if self.econtext.overview_mode:
self.econtext.mouse_moved(x, y)
self.set_needs_repaint()
def mouse_leave(self):
self.econtext._canvas.mouse_leave()
def relayout(self):
w, h = self.econtext.layout()
if self.get_width() != w or self.get_height() != h:
self.set_size(w, h)
def repaint(self, cr, x, y, w, h):
c = Context(cr)
c.set_source_rgb(1, 0xfc/255.0, 0xe5/255.0)
try:
dw, dh = self.econtext.size
xx = 0
yy = 0
if dw < self.get_width():
xx = (self.get_width() - dw)/2
if dh < self.get_height():
yy = (self.get_height() - dh)/2
self.offset = (xx, yy)
self.econtext.set_offset(xx, yy)
self.econtext.repaint(c)
except Exception:
import traceback
log_error("Exception rendering explain output: %s\n" % traceback.format_exc())
def newToolBarItem(*args):
item = mforms.ToolBarItem(*args)
item.set_managed()
return item
EXPLAIN_COLUMN_WIDTHS = {
"id" : 40,
"select_type" : 120,
"table" : 100,
"type" : 50,
"possible_keys" : 200,
"key" : 200,
"key_len" : 40,
"ref" : 200,
"rows" : 50,
"Extra" : 200
}
class QueryPlanTab(mforms.Box):
node_spacing = 30
vertical = True
def __init__(self, owner, json_text, context, server_version):
mforms.Box.__init__(self, False)
self.set_managed()
self.set_release_on_add()
self._context = context
get_resource_path = mforms.App.get().get_resource_path
self.toolbar = mforms.newToolBar(mforms.SecondaryToolBar)
self.toolbar.set_back_color("#ffffff")
self.switcher_item = newToolBarItem(mforms.SelectorItem)
self.toolbar.add_item(self.switcher_item)
s = newToolBarItem(mforms.SeparatorItem)
self.toolbar.add_item(s)
l = newToolBarItem(mforms.LabelItem)
l.set_text("Display Info:")
self.toolbar.add_item(l)
item = newToolBarItem(mforms.SelectorItem)
item.set_selector_items(["Read + Eval cost", "Data Read per Join"])
item.add_activated_callback(self.display_cost)
self.toolbar.add_item(item)
cost_type_item = item
# cost info was added in 5.7.2
has_cost_info = server_version >= Version(5, 7)
if not has_cost_info:
item.set_enabled(False)
#item = newToolBarItem(mforms.SelectorItem)
# item.set_selector_items(["Show Aggregated Costs", "Show Individual Costs"])
# item.add_activated_callback(self.toggle_aggregated)
# self.toolbar.add_item(item)
#btn = newToolBarItem(mforms.SegmentedToggleItem)
#btn.set_icon(get_resource_path("qe_resultset-tb-switcher_grid_off_mac.png"))
#btn.set_alt_icon(get_resource_path("qe_resultset-tb-switcher_grid_on_mac.png"))
#self.toolbar.add_item(btn)
#btn = newToolBarItem(mforms.SegmentedToggleItem)
#btn.set_icon(get_resource_path("qe_resultset-tb-switcher_explain_off.png"))
#btn.set_alt_icon(get_resource_path("qe_resultset-tb-switcher_explain_on.png"))
#self.toolbar.add_item(btn)
s = newToolBarItem(mforms.SeparatorItem)
self.toolbar.add_item(s)
btn = newToolBarItem(mforms.ActionItem)
btn.set_icon(get_resource_path("tiny_saveas.png"))
btn.add_activated_callback(self.save)
btn.set_tooltip("Save image to an external file.")
self.toolbar.add_item(btn)
s = newToolBarItem(mforms.SeparatorItem)
self.toolbar.add_item(s)
l = newToolBarItem(mforms.LabelItem)
l.set_text("Overview:")
self.toolbar.add_item(l)
btn = newToolBarItem(mforms.ActionItem)
btn.set_icon(get_resource_path("qe_sql-editor-explain-tb-overview.png"))
btn.add_activated_callback(self.overview)
btn.set_tooltip("Zoom out the diagram.")
self.toolbar.add_item(btn)
s = newToolBarItem(mforms.SeparatorItem)
self.toolbar.add_item(s)
l = newToolBarItem(mforms.LabelItem)
l.set_text("View Source:")
self.toolbar.add_item(l)
btn = newToolBarItem(mforms.ToggleItem)
btn.set_icon(get_resource_path("statusbar_output.png"))
btn.set_alt_icon(get_resource_path("statusbar_output.png"))
btn.add_activated_callback(self.switch_to_raw)
btn.set_tooltip("View the raw JSON explain data.")
self.toolbar.add_item(btn)
self.add(self.toolbar, False, True)
# Query Plan diagram
self.scroll = mforms.newScrollPanel(mforms.ScrollPanelNoFlags)
self.scroll.set_visible_scrollers(True, True)
#self.img = mforms.newImageBox()
self.drawbox = RenderBox(self._context, self.scroll)
self.scroll.add(self.drawbox)
self.drawbox.node_spacing = self.node_spacing
self.drawbox.vertical = self.vertical
self.add(self.scroll, True, True)
self.display_cost(cost_type_item)
# textbox to view the json data
self._raw_explain = mforms.CodeEditor()
self._raw_explain.set_value(json_text)
self._raw_explain.set_language(mforms.LanguageJson)
self._raw_explain.set_features(mforms.FeatureReadOnly | mforms.FeatureFolding, True)
self.add(self._raw_explain, True, True)
self._raw_explain.show(False)
nc.add_observer(self.updateColors, "GNColorsChanged")
backgroundColor = Color.getSystemColor(TextBackgroundColor)
self.scroll.set_back_color(backgroundColor.to_html())
def display_cost(self, item):
text = item.get_text()
if text:
cost = text.lower().split()[0]
if cost == "read":
self._context.show_cost_info_type("read_eval_cost")
elif cost == "data":
self._context.show_cost_info_type("data_read_per_join")
else:
grt.log_error("vexplain", "Unknown cost info type: %s\n" % cost)
self.drawbox.set_needs_repaint()
def toggle_aggregated(self, item):
self._context.show_aggregated_cost_info("aggregated" in item.get_text().lower())
self.drawbox.set_needs_repaint()
def switch_to_raw(self, item):
flag = item.get_checked()
self._raw_explain.show(flag)
self.scroll.show(not flag)
def save(self, item):
ch = mforms.FileChooser(mforms.SaveFile)
directory = grt.root.wb.options.options.get("wb.VisualExplain:LastFileChooserDirectory", "")
if directory:
ch.set_directory(directory)
ch.set_extensions("PNG image (*.png)|*.png", "png")
ch.set_title("Save Image As")
ch.set_path("explain.png")
if ch.run_modal():
self._context.export_to_png(ch.get_path())
grt.root.wb.options.options["wb.VisualExplain:LastFileChooserDirectory"] = ch.get_directory()
def set_needs_repaint(self, x, y, w, h):
self.drawbox.set_needs_repaint()
def overview(self, item):
self._context.enter_overview_mode()
self.drawbox.set_needs_repaint()
def updateColors(self, name, sender, info):
backgroundColor = Color.getSystemColor(TextBackgroundColor)
self.scroll.set_back_color(backgroundColor.to_html())
class TabularExplainTab(mforms.Box):
node_spacing = 30
vertical = True
def __init__(self, owner, explain, server_version):
mforms.Box.__init__(self, False)
self.set_managed()
self.set_release_on_add()
self.toolbar = mforms.newToolBar(mforms.SecondaryToolBar)
self.toolbar.set_back_color("#ffffff")
self.switcher_item = newToolBarItem(mforms.SelectorItem)
self.toolbar.add_item(self.switcher_item)
self.add(self.toolbar, False, False)
self.explain_tree = mforms.newTreeView(mforms.TreeFlatList|mforms.TreeShowColumnLines|mforms.TreeShowRowLines|mforms.TreeAltRowColors)
self.explain_tree.add_column_resized_callback(self.column_resized)
c = len(explain.columns)
rows_column = None
saved_widths = grt.root.wb.state.get("wb.query.analysis:ExplainTreeColumnWidths", None)
if saved_widths:
saved_widths = [int(i) for i in saved_widths.split(",")]
for i, column in enumerate(explain.columns):
width = saved_widths[i] if saved_widths and i < len(saved_widths) else EXPLAIN_COLUMN_WIDTHS.get(column.name, 100)
if column.name == "rows":
rows_column = i
self.explain_tree.add_column(mforms.LongIntegerColumnType, column.name, width)
else:
self.explain_tree.add_column(mforms.StringColumnType, column.name, width)
self.explain_tree.end_columns()
if explain.goToFirstRow():
while True:
node = self.explain_tree.add_node()
for i in range(c):
value = explain.stringFieldValue(i)
if i == rows_column:
node.set_long(i, int(value) if value else 0)
else:
node.set_string(i, value)
if not explain.nextRow():
break
explain.reset_references()
self.add(self.explain_tree, True, True)
def column_resized(self, column):
sizes = []
for i in range(self.explain_tree.get_column_count()):
sizes.append(self.explain_tree.get_column_width(i))
grt.root.wb.state["wb.query.analysis:ExplainTreeColumnWidths"] = ",".join([str(i) for i in sizes])
class ExplainTab(mforms.AppView):
_query_plan = None
_costs_tree = None
_explain_context = None
def __init__(self, server_version, query, json_text, explain):
mforms.AppView.__init__(self, False, "Query Explain", "Query Explain", False)
self.set_managed()
self.set_release_on_add()
self.on_close(self.on_tab_close)
self._form_deactivated_conn = mforms.Form.main_form().add_deactivated_callback(self.form_deactivated)
if not json_text and not explain:
label = mforms.newLabel("Explain data not available for statement")
label.set_style(mforms.BigBoldStyle)
label.set_text_align(mforms.MiddleCenter)
self.add(label, True, True)
return
self._query = query
self.tabview = mforms.newTabView(mforms.TabViewTabless)
self.tabview.add_tab_changed_callback(self.tab_changed)
self.set_back_color("#ffffff")
default_tab = grt.root.wb.state.get("wb.query.analysis:ActiveExplainTab", 0)
json_data = None
if json_text:
try:
json_data = decode_json(json_text)
except Exception as e:
import traceback
log_error("Error creating query plan: %s\n" % traceback.format_exc())
mforms.Utilities.show_error("Query Plan Generation Error",
"An unexpected error occurred parsing JSON query explain data.\nPlease file a bug report at http://bugs.mysql.com along with the query and the Raw Explain Data.\n\nException: %s" % e,
"OK", "", "")
if json_data:
try:
self._explain_context = ExplainContext(json_data, server_version)
self._query_plan = QueryPlanTab(self, json_text, self._explain_context, server_version)
self._explain_context.init_canvas(self._query_plan.drawbox, self._query_plan.scroll, self._query_plan.set_needs_repaint)
#self._explain_context._canvas.set_background_color(1, 0xfc/255.0, 0xe5/255.0)
# Initial layouting of the plan diagram
self._query_plan.drawbox.relayout()
self.tabview.add_page(self._query_plan, "Visual Explain")
self._query_plan.switcher_item.set_name("Visual Explain Switcher")
self._query_plan.switcher_item.setInternalName("visual_explain_switcher")
self._query_plan.switcher_item.set_selector_items(["Visual Explain", "Tabular Explain"])
self._query_plan.switcher_item.add_activated_callback(self.switch_view)
except Exception as e:
import traceback
log_error("Error creating query plan: %s\n" % traceback.format_exc())
mforms.Utilities.show_error("Query Plan Generation Error",
"An unexpected error occurred during creation of the graphical query plan.\nPlease file a bug report at http://bugs.mysql.com along with the query and the Raw Explain Data.\n\nException: %s" % e,
"OK", "", "")
else:
log_error("No JSON data for explain\n")
# Good old explain
if explain:
self._tabular_explain = TabularExplainTab(self, explain, server_version)
self.tabview.add_page(self._tabular_explain, "Tabular Explain")
self._tabular_explain.switcher_item.set_name("Tabular Explain Switcher")
self._tabular_explain.switcher_item.setInternalName("tabular_explain_switcher")
self._tabular_explain.switcher_item.set_selector_items(["Visual Explain", "Tabular Explain"])
self._tabular_explain.switcher_item.set_text("Tabular Explain")
self._tabular_explain.switcher_item.add_activated_callback(self.switch_view)
self.add(self.tabview, True, True)
if self._explain_context:
self.tabview.set_active_tab(default_tab)
_switching = False
def switch_view(self, item):
if self._switching: return
self._switching = True
new_view = item.get_text()
if new_view == "Visual Explain":
self.tabview.set_active_tab(0)
elif new_view == "Tabular Explain":
self.tabview.set_active_tab(1)
else:
raise Exception("Unknown "+new_view)
source = item.getInternalName()
# switch back the selector
if source == "visual_explain_switcher":
item.set_text("Visual Explain")
elif source == "tabular_explain_switcher":
item.set_text("Tabular Explain")
self._switching = False
def tab_changed(self):
if self._query_plan and self._explain_context:
if self.tabview.get_active_tab() == 0:
self._explain_context._canvas.activate()
else:
self._explain_context._canvas.deactivate()
self._explain_context.close_tooltip()
grt.root.wb.state["wb.query.analysis:ActiveExplainTab"] = self.tabview.get_active_tab()
def fill_costs_tree(self, json):
tree = mforms.newTreeView(mforms.TreeFlatList|mforms.TreeShowColumnLines|mforms.TreeShowRowLines|mforms.TreeAltRowColors)
return tree
def form_deactivated(self):
if self._query_plan and self._explain_context:
self._explain_context.close_tooltip()
def on_tab_close(self):
self.form_deactivated()
if self._form_deactivated_conn:
self._form_deactivated_conn.disconnect()
self._form_deactivated_conn = None
return True
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryEditor, grt.classes.db_query_ResultPanel)
def visualExplain(editor, result_panel):
version = Version.fromgrt(editor.owner.serverVersion)
statement = editor.currentStatement
if statement:
try:
explain = editor.owner.executeQuery("EXPLAIN %s" % statement, 1)
except Exception as e:
log_warning("Could not execute EXPLAIN %s: %s\n" % (statement, e))
explain = None
json = None
if explain and version.is_supported_mysql_version_at_least(5, 6):
rset = editor.owner.executeQuery("EXPLAIN FORMAT=JSON %s" % statement, 1)
if rset and rset.goToFirstRow():
json = rset.stringFieldValue(0)
rset.reset_references()
view = ExplainTab(version, statement, json, explain if explain else None)
dock = mforms.fromgrt(result_panel.dockingPoint)
view.set_identifier("execution_plan")
view.set_title("Execution\nPlan")
dock.dock_view(view, "output_type-executionplan.png", 0)
dock.select_view(view)
return 0
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryEditor, grt.STRING, grt.STRING)
def visualExplainForConnection(editor, conn_id, the_query):
version = Version.fromgrt(editor.owner.serverVersion)
try:
explain = editor.owner.executeManagementQuery("EXPLAIN FOR CONNECTION %s" % conn_id, 1)
except grt.DBError as e:
if e.args[1] == 0:
mforms.Utilities.show_message("Explain for Connection",
"Explain for connection %s did not generate any output." % conn_id, "OK", "", "")
else:
mforms.Utilities.show_error("Explain for Connection",
"Error executing explain for connection %s\n%s" % (conn_id, e), "OK", "", "")
return 0
except Exception as e:
mforms.Utilities.show_error("Explain for Connection",
"Error executing explain for connection %s\n%s" % (conn_id, e), "OK", "", "")
return 0
if not explain:
mforms.Utilities.show_error("Explain for Connection",
"Error executing explain for connection %s" % conn_id, "OK", "", "")
return 0
if version.is_supported_mysql_version_at_least(5, 6):
rset = editor.owner.executeManagementQuery("EXPLAIN FORMAT=JSON FOR CONNECTION %s" % conn_id, 1)
if rset and rset.goToFirstRow():
json = rset.stringFieldValue(0)
rset.reset_references()
else:
json = None
view = ExplainTab(version, the_query, json, explain if explain else None)
view.set_identifier("execution_plan")
dock = mforms.fromgrt(editor.resultDockingPoint)
dock.dock_view(view, "", 0)
dock.select_view(view)
view.set_title("Explain for Connection")
return 0