def work(self, files):
try:
if self.ctrl_be.target_version >= Version(5, 7, 10):
self.importer.reset_schemas()
else:
location = download_server_install_script(self.ctrl_be)
if location:
workbench_version_string = get_current_sys_version(None)
server_version_string = get_sys_version_from_script(location)
maj, min, rel = [int(i) for i in workbench_version_string.split(".")]
workbench_version = Version(maj, min, rel)
maj, min, rel = [int(i) for i in server_version_string.split(".")]
server_version = Version(maj, min, rel)
if server_version >= workbench_version:
log_info("Installing sys schema supplied by the server: %s\n" % str(location))
self.install_scripts([(location, None)], "Installing server script")
return
else:
log_info("Server sys schema install script exists but it's outdated compared to the one supplied by Workbench...\n")
log_info("Installing sys schema supplied by workbench\n")
self.install_scripts(files, "Installing Workbench script")
except Exception as e:
log_error("Runtime error when installing the sys schema: %s\n" % str(e))
self._worker_queue.put(e)
# This makes the screen refresh
self._worker_queue.put(None)
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
def create_ui(self):
#self.create_basic_ui("title_dashboard.png", "Dashboard")
self._form_deactivated_conn = mforms.Form.main_form(
).add_deactivated_callback(self.form_deactivated)
self.content = mforms.newScrollPanel(0)
self.drawbox = RenderBox(self)
self.canvas = Canvas(self.set_needs_repaint)
self.drawbox.canvas = self.canvas
self.drawbox.set_size(1024, 700)
self.content.add(self.drawbox)
self.widgets = []
self.last_refresh_time = None
#
self.drawbox.variable_values = self.ctrl_be.server_variables
server_version = Version.fromgrt(self.ctrl_be.target_version)
GLOBAL_DASHBOARD_WIDGETS = GLOBAL_DASHBOARD_WIDGETS_NETWORK + GLOBAL_DASHBOARD_WIDGETS_MYSQL_PRE_80 + GLOBAL_DASHBOARD_WIDGETS_INNODB
if server_version and server_version.is_supported_mysql_version_at_least(
8, 0, 0):
GLOBAL_DASHBOARD_WIDGETS = GLOBAL_DASHBOARD_WIDGETS_NETWORK + GLOBAL_DASHBOARD_WIDGETS_MYSQL_POST_80 + GLOBAL_DASHBOARD_WIDGETS_INNODB
# create all widgets
for caption, wclass, args, init, (
calc,
calc_expr), color, pos, hover_text in GLOBAL_DASHBOARD_WIDGETS:
if caption:
fig = TextFigure(caption)
fig.set_text_color(0.3, 0.3, 0.3)
fig.set_font_size(13)
fig.set_font_bold(True)
self.drawbox.add(fig)
fig.move(pos[0], pos[1] - 20)
w = wclass(calc(calc_expr) if calc else None, *args)
self.drawbox.add(w)
w.set_main_color(color)
w.move(*pos)
if hover_text:
w.hover_text_template = hover_text
if init:
init_calc, init_expr = init
w.init(
init_calc(init_expr).handle(self.ctrl_be.server_variables,
None))
self.widgets.append(w)
self.refresh()
self._refresh_tm = mforms.Utilities.add_timeout(
self.ctrl_be.status_variable_poll_interval, self.refresh)
self.ctrl_be.add_me_for_event("server_started", self)
self.ctrl_be.add_me_for_event("server_stopped", self)
return self.content
def show_in_sidebar(self):
if not self.shown_in_sidebar:
if self.editor.serverVersion:
server_version = Version.fromgrt(self.editor.serverVersion)
else:
server_version = None
self.shown_in_sidebar = True
first = True
self.disabled_pages = {}
for sname, stitle, sitems in self.sidebar_sections:
flags = mforms.TaskSectionShowConfigButton if sname == "wba_instance" else mforms.TaskSectionPlain
if first:
flags |= mforms.TaskSectionToggleModeButton
first = False
if grt.root.wb.options.options['DbSqlEditor:SidebarModeCombined'] == 1:
flags |= mforms.TaskSectionToggleModeButtonPreSelected
self.sidebar.add_section(sname, stitle, flags)
for ident, ititle, icon_path in sitems:
self.sidebar.add_section_entry(sname, ident, ititle, icon_path, mforms.TaskEntryAlwaysActiveLink)
mod, requires_remote_access = self.admin_pages.get(ident, (None, True))
enabled = True
if requires_remote_access and (not self.server_profile or (self.server_profile and not self.server_profile.is_local and not self.server_profile.remote_admin_enabled)):
enabled = False
self.disabled_pages[ident] = "Feature requires remote host access.\nClick the wrench icon to configure a remote administration method for this connection."
elif getattr(mod, "min_server_version", None):
if server_version and not server_version.is_supported_mysql_version_at_least(*mod.min_server_version):
enabled = False
self.disabled_pages[ident] = "This feature requires MySQL version %s or newer" % ".".join([str(x) for x in mod.min_server_version])
self.sidebar.set_section_entry_enabled(ident, enabled)
self.sidebar.set_collapse_states(grt.root.wb.options.options.get('Administrator:sidebar_collapsed_sections', ''))
def __init__(self, editor, is_import):
self.name = ""
self.title = self.name
self.options = {};
self._offset = None
self._limit = None
self._table_w_prefix = None
self._columns = []
self._filepath = None
self._extension = None
self._allow_remote = False
self._editor = editor
self._targetVersion = Version.fromgrt(editor.serverVersion)
self._local = True
self._mapping = []
self._new_table = False
self._last_analyze = False
self._is_import = is_import
self._current_row = 0
self._max_rows = 0
self._thread_event = None
self._user_query = None
self._decimal_separator = ','
self._date_format = '%Y-%m-%d %H:%M:%S'
self._encoding = 'utf-8' #default encoding
self._force_drop_table = False
self._truncate_table = False
self._type_map = {'text':'is_string', 'bigint': 'is_bignumber', 'geometry': 'is_geometry', 'int': 'is_number', 'double':'is_float', 'json': 'is_json'}
self.is_running = False
self.progress_info = None
self.item_count = 0
def __init__(self, editor, is_import):
self.name = ""
self.title = self.name
self.options = {};
self._offset = None
self._limit = None
self._table_w_prefix = None
self._columns = []
self._filepath = None
self._extension = None
self._allow_remote = False
self._editor = editor
self._targetVersion = Version.fromgrt(editor.serverVersion)
self._local = True
self._mapping = []
self._new_table = False
self._last_analyze = False
self._is_import = is_import
self._current_row = 0
self._max_rows = 0
self._thread_event = None
self._user_query = None
self._decimal_separator = ','
self._date_format = '%Y-%m-%d %H:%M:%S'
self._encoding = 'utf-8' #default encoding
self._force_drop_table = False
self._truncate_table = False
self._type_map = {'text':'is_string', 'bigint': 'is_bignumber', 'geometry': 'is_geometry', 'int': 'is_number', 'double':'is_float', 'json': 'is_json'}
self.is_running = False
self.progress_info = None
self.item_count = 0
def __init__(self, owner):
WizardPage.__init__(self, owner, "Import Options", wide=True)
self.layer_name = None
self.column_list = []
self.support_spatial_index = Version.fromgrt(
owner.editor.serverVersion).is_supported_mysql_version_at_least(
5, 7, 5)
def show_in_sidebar(self):
if not self.shown_in_sidebar:
if self.editor.serverVersion:
server_version = Version.fromgrt(self.editor.serverVersion)
else:
server_version = None
self.shown_in_sidebar = True
self.disabled_pages = {}
for sname, saname, stitle, sitems in self.sidebar_sections:
flags = mforms.TaskSectionShowConfigButton if sname == "Instance" else mforms.TaskSectionPlain
self.sidebar.add_section(sname, saname, stitle, flags)
for ident, ianame, ititle, icon_path in sitems:
self.sidebar.add_section_entry(sname, ident, ianame, ititle, icon_path, mforms.TaskEntryAlwaysActiveLink)
mod, requires_remote_access = self.admin_pages.get(ident, (None, True))
enabled = True
if requires_remote_access and (not self.server_profile or (self.server_profile and not self.server_profile.is_local and not self.server_profile.remote_admin_enabled)):
enabled = False
self.disabled_pages[ident] = "Feature requires remote host access.\nClick the wrench icon to configure a remote administration method for this connection."
elif getattr(mod, "min_server_version", None):
if server_version and not server_version.is_supported_mysql_version_at_least(*mod.min_server_version):
enabled = False
self.disabled_pages[ident] = "This feature requires MySQL version %s or newer" % ".".join([str(x) for x in mod.min_server_version])
self.sidebar.set_section_entry_enabled(ident, enabled)
self.sidebar.set_collapse_states(grt.root.wb.options.options.get('Administrator:sidebar_collapsed_sections', ''))
def create_ui(self):
#self.create_basic_ui("title_dashboard.png", "Dashboard")
self._form_deactivated_conn = mforms.Form.main_form().add_deactivated_callback(self.form_deactivated)
self.content = mforms.newScrollPanel(0)
self.drawbox = RenderBox(self)
self.canvas = Canvas(self.set_needs_repaint)
self.drawbox.canvas = self.canvas
self.drawbox.set_size(1024, 700)
self.content.add(self.drawbox)
self.add(self.content, True, True)
self.widgets = []
self.last_refresh_time = None
#
self.drawbox.variable_values = self.ctrl_be.server_variables
server_version = Version.fromgrt(self.ctrl_be.target_version)
GLOBAL_DASHBOARD_WIDGETS = GLOBAL_DASHBOARD_WIDGETS_NETWORK + GLOBAL_DASHBOARD_WIDGETS_MYSQL_PRE_80 + GLOBAL_DASHBOARD_WIDGETS_INNODB
if server_version and server_version.is_supported_mysql_version_at_least(8, 0, 0):
GLOBAL_DASHBOARD_WIDGETS = GLOBAL_DASHBOARD_WIDGETS_NETWORK + GLOBAL_DASHBOARD_WIDGETS_MYSQL_POST_80 + GLOBAL_DASHBOARD_WIDGETS_INNODB
# create all widgets
for caption, wclass, args, init, (calc, calc_expr), color, pos, hover_text in GLOBAL_DASHBOARD_WIDGETS:
if caption:
fig = TextFigure(caption)
fig.set_text_color(0.5, 0.5, 0.5)
fig.set_font_size(11)
fig.set_font_bold(True)
self.drawbox.add(fig)
fig.move(pos[0], pos[1] - 20)
w = wclass(calc(calc_expr) if calc else None, *args)
self.drawbox.add(w)
w.set_main_color(color)
w.move(*pos)
if hover_text:
w.hover_text_template = hover_text
if init:
init_calc, init_expr = init
w.init(init_calc(init_expr).handle(self.ctrl_be.server_variables, None))
self.widgets.append(w)
self.refresh()
self._refresh_tm = mforms.Utilities.add_timeout(self.ctrl_be.status_variable_poll_interval, self.refresh)
self.ctrl_be.add_me_for_event("server_started", self)
self.ctrl_be.add_me_for_event("server_stopped", self)
def refresh(self):
self.tree.clear()
for table in self.table_manager.table_names or []:
try:
rset = self.editor.executeManagementQuery(
self.show_query % {
'table': table.replace("`", "``"),
'schema': self.schema.replace("`", "``")
}, 0)
except grt.DBError as err:
log_warning("Error querying index info for %s.%s: %s\n" %
(self.schema, table, err[0]))
continue
ok = rset.goToFirstRow()
while ok:
if not self.filter or self.filter(rset):
node = self.tree.add_node()
node.set_icon_path(0, self.icon_path)
i = 0
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
format_func = None
field = None
try:
format_func = field_obj['format_func']
field = field_obj['field']
except:
field = field_obj
if ctype == mforms.IntegerColumnType:
if type(field) is int:
node.set_int(i, rset.intFieldValue(field) or 0)
else:
node.set_int(
i,
rset.intFieldValueByName(field) or 0)
else:
if type(field) is int:
node.set_string(
i,
rset.stringFieldValue(field) or ""
if format_func is None else format_func(
rset.stringFieldValue(field)))
else:
node.set_string(
i,
rset.stringFieldValueByName(field) or ""
if format_func is None else format_func(
rset.stringFieldValueByName(field)))
i += 1
ok = rset.nextRow()
self.refresh_row_count()
def query_server_info(self):
self.server_variables = {}
result = self.exec_query("SHOW VARIABLES")
if not result:
# Didn't get the server variables. Assuming the server is stopped
self.last_known_server_running_status = ("stopped", time.time())
return
while result and result.nextRow():
self.server_variables[result.stringByName(
"Variable_name")] = result.stringByName("Value")
self.status_variables_time = time.time()
self.status_variables = {}
result = self.exec_query("SHOW GLOBAL STATUS")
while result and result.nextRow():
self.status_variables[result.stringByName(
"Variable_name")] = result.stringByName("Value")
# check version
if self.server_variables:
self.raw_version = self.server_variables["version"]
self.target_version = Version.fromstr(self.raw_version)
if self.server_profile.server_version != self.raw_version: # Update profile version with live data from server
log_debug(
'%s.connect_sql(): The server version stored in the server instance profile was "%s". '
'Changed it to the version reported by the server: "%s"\n'
% (self.__class__.__name__,
self.server_profile.server_version, self.raw_version))
self.server_profile.server_version = self.raw_version
if self.target_version and self.target_version.is_supported_mysql_version_at_least(
5, 1, 5):
# The command to retrieve plugins was 'SHOW PLUGIN' for v. [5.1.5, 5.1.9)
# and was changed to 'SHOW PLUGINS' from MySQL Server v. 5.1.9 onwards:
plugin_var = 'PLUGINS' if self.target_version.is_supported_mysql_version_at_least(
5, 1, 9) else 'PLUGIN'
result = self.exec_query('SHOW %s' % plugin_var)
# check whether Windows authentication plugin is available
while result and result.nextRow():
name = result.stringByName("Name")
status = result.stringByName("Status")
plugin_type = result.stringByName("Type")
if status == "ACTIVE":
self.server_active_plugins.add((name, plugin_type))
if "offline_mode" in self.server_variables and self.server_variables[
"offline_mode"] == "ON":
#We're in offline mode, need to change server status
self.last_known_server_running_status = ("offline", time.time())
else:
self.last_known_server_running_status = ("running", time.time())
def install_helper(self):
self.installer_panel = HelperInstallPanel(self._owner, self._main_view.editor, self._ctrl_be)
self._content.add(self.installer_panel, 1, 2, 2, 3, 0)
self._owner.relayout() # needed b/c of layout bug in Mac
if self._ctrl_be.target_version >= Version(5, 7, 10):
filechooser = FileChooser(mforms.OpenFile)
filechooser.set_title("Specify the location of mysql_upgrade")
if filechooser.run_modal():
self.installer_panel.importer._upgrade_tool_path = filechooser.get_path()
self.installer_panel.importer.set_password(self._ctrl_be.get_mysql_password())
self.installer_panel.start()
def get_profiles_for(system, version):
path = mforms.App.get().get_resource_path("mysql.profiles")
if not path:
path = mforms.App.get().get_resource_path("")
if not path:
log_error("Could not find mysql.profiles dir\n")
return []
path += "/mysql.profiles"
version = Version.fromstr(version or "5.6")
files = [f for f in os.listdir(path) if f.endswith(".xml")]
profiles = []
matched_profiles = []
for f in files:
data = grt.unserialize(os.path.join(path, f))
if data and data.has_key("sys.system") and data["sys.system"] == system:
profiles.append(data)
profile_version = Version.fromstr(data.get("serverVersion"))
if version.majorNumber == profile_version.majorNumber or version.minorNumber == profile_version.minorNumber:
matched_profiles.append(data)
if matched_profiles:
return matched_profiles
return profiles
def get_query(self):
cols = []
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
try:
cols.append("`%s`" % field_obj['field'])
except:
cols.append("`%s`" % field_obj)
return self.show_query % {
'schema': self.schema,
'columns': ", ".join(cols)
}
def get_profiles_for(system, version):
path = mforms.App.get().get_resource_path("mysql.profiles")
if not path:
path = mforms.App.get().get_resource_path("")
if not path:
log_error("Could not find mysql.profiles dir\n")
return []
path += "/mysql.profiles"
version = Version.fromstr(version or "5.6")
files = [f for f in os.listdir(path) if f.endswith(".xml")]
profiles = []
matched_profiles = []
for f in files:
data = grt.unserialize(os.path.join(path, f))
if data and "sys.system" in data and data["sys.system"] == system:
profiles.append(data)
profile_version = Version.fromstr(data.get("serverVersion"))
if version.majorNumber == profile_version.majorNumber or version.minorNumber == profile_version.minorNumber:
matched_profiles.append(data)
if matched_profiles:
return matched_profiles
return profiles
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
def __init__(self, owner):
WizardPage.__init__(self,
owner,
"Configure Import Settings",
wide=True)
self.last_analyze_status = False
self.input_file_type = 'csv'
self.active_module = self.main.formats[0] # csv
self.encoding_list = {
'cp1250 (windows-1250)': 'cp1250',
'latin2 (iso8859-2)': 'iso8859_2',
'latin1 (iso8859-1)': 'latin_1',
'utf-8': 'utf-8',
'utf-16': 'utf-16'
}
self.dest_cols = []
self.column_mapping = []
self.ds_show_count = 0
self.df_show_count = 0
self.opts_mapping = {}
self.is_server_5_7 = Version.fromgrt(
self.main.editor.serverVersion
).is_supported_mysql_version_at_least(Version.fromstr("5.7.5"))
def __init__(self, owner):
WizardPage.__init__(self, owner, "Configure Import Settings", wide=True)
self.last_analyze_status = False
self.input_file_type = 'csv'
self.active_module = self.main.formats[0] # csv
self.encoding_list = {'cp1250 (windows-1250)':'cp1250',
'latin2 (iso8859-2)':'iso8859_2',
'latin1 (iso8859-1)':'latin_1',
'utf-8':'utf-8',
'utf-16':'utf-16'}
self.dest_cols = []
self.column_mapping = []
self.ds_show_count = 0
self.df_show_count = 0
self.opts_mapping = {}
self.is_server_5_7 = Version.fromgrt(self.main.editor.serverVersion).is_supported_mysql_version_at_least(Version.fromstr("5.7.5"))
def get_query(self):
if len(
self.columns
) == 0: # Probably user doesn't have privileges to list the privilege tables.
return []
output = []
fields = []
fields_where = []
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
field = None
try:
field = field_obj['field']
except:
field = field_obj
if field == "scope":
continue
if field not in ['Db']:
fields.append(field)
if field not in ['User', 'Host', 'Db']:
fields_where.append("%s = 'Y'" % field.replace(" ", "_"))
output.append(
"SELECT '<global>' as Db,%(sel_fields)s FROM mysql.user WHERE %(where_fields)s"
% {
'sel_fields': ",".join(fields),
'where_fields': " OR ".join(fields_where)
})
output.append(
"SELECT Db,%(sel_fields)s FROM mysql.db WHERE '%(schema)s' like db"
% {
'sel_fields': ",".join(fields),
'schema': self.schema
})
return output
def start_import(self):
if not self._last_analyze:
return False
if self._new_table:
if not self.prepare_new_table():
return False
if self._truncate_table:
self.update_progress(0.0, "Truncate table")
self._editor.executeManagementCommand(
"TRUNCATE TABLE %s" % self._table_w_prefix, 1)
result = True
with open(self._filepath, 'rb') as csvfile:
self.update_progress(0.0, "Prepare Import")
dest_col_order = list(
set([i['dest_col'] for i in self._mapping if i['active']]))
query = """PREPARE stmt FROM 'INSERT INTO %s (%s) VALUES(%s)'""" % (
self._table_w_prefix, ",".join([
"`%s`" % col for col in dest_col_order
]), ",".join(["?" for i in dest_col_order]))
col_order = dict([(i['dest_col'], i['col_no'])
for i in self._mapping if i['active']])
col_type = dict([(i['dest_col'], i['type']) for i in self._mapping
if i['active']])
is_server_5_7 = self._targetVersion.is_supported_mysql_version_at_least(
Version.fromstr("5.7.5"))
self._editor.executeManagementCommand(query, 1)
try:
is_header = self.has_header
reader = UniReader(csvfile,
self.dialect,
encoding=self._encoding)
self._max_rows = os.path.getsize(self._filepath)
self.update_progress(0.0, "Begin Import")
for row in reader:
if self._thread_event and self._thread_event.is_set():
self._editor.executeManagementCommand(
"DEALLOCATE PREPARE stmt", 1)
log_debug2("Worker thread was stopped by user")
self.update_progress(
round(self._current_row / self._max_rows, 2),
"Import stopped by user request")
return False
self._current_row = float(csvfile.tell())
if is_header:
is_header = False
continue
for i, col in enumerate(col_order):
if col_order[col] >= len(row):
log_error("Can't find col: %s in row: %s" %
(col_order[col], row))
result = False
break
val = row[col_order[col]]
col_name = col_order[col]
if col_type[col] == "geometry":
if is_server_5_7:
val = """ST_GeomFromText("%s")""" % row[
col_name]
else:
val = """GeomFromText("%s")""" % row[col_name]
self._editor.executeManagementCommand(
"""SET @a%d = %s """ % (i, val), 0)
else:
if col_type[col] == 'double':
val = row[col_name].replace(
self._decimal_separator, '.')
elif col_type[col] == 'datetime':
val = datetime.datetime.strptime(
row[col_name], self._date_format).strftime(
"%Y-%m-%d %H:%M:%S")
if hasattr(val, "replace"):
val = val.replace("\\",
"\\\\").replace("'", "\\'")
if self.options['nullwordaskeyword'][
'value'] == "y" and val.upper() == "NULL":
self._editor.executeManagementCommand(
"""SET @a%d = NULL """ % (i), 0)
else:
self._editor.executeManagementCommand(
"""SET @a%d = '%s' """ % (i, val), 0)
else:
try:
self._editor.executeManagementCommand(
"EXECUTE stmt USING %s" % ", ".join([
'@a%d' % i
for i, col in enumerate(col_order)
]), 0)
self.item_count = self.item_count + 1
self.update_progress(
round(self._current_row / self._max_rows, 2),
"Data import")
except Exception, e:
log_error("Row import failed with error: %s" % e)
self.update_progress(
round(self._current_row / self._max_rows, 2),
"Row import failed with error: %s" % e)
result = False
self.update_progress(1.0, "Import finished")
except Exception, e:
import traceback
log_debug3("Import failed traceback: %s" %
traceback.format_exc())
log_error("Import failed: %s" % e)
def createScriptForCatalogObjects(path, catalog, objectCreationParams):
"""Create a CREATE script with the catalog objects. The catalog must have been previously processed
with generateSQLCreateStatements(), so that the objects have their temp_sql attributes set with
their respective SQL CREATE statements.
"""
def object_heading(type, name):
text = """
-- ----------------------------------------------------------------------------
-- %s %s
-- ----------------------------------------------------------------------------
""" % (type, name)
return text
import time
file = open(path, "w+")
file.write("""-- ----------------------------------------------------------------------------
-- MySQL Workbench Migration
-- Migrated Schemata: %s
-- Source Schemata: %s
-- Created: %s
-- Workbench Version: %s
-- ----------------------------------------------------------------------------
""" % (", ".join([s.name for s in catalog.schemata]), ", ".join([s.oldName for s in catalog.schemata]), time.ctime(), Version.fromgrt(grt.root.wb.info.version)))
preamble = catalog.customData["migration:preamble"]
if preamble and preamble.temp_sql:
#file.write(object_heading("Preamble script", ""))
file.write(preamble.temp_sql+"\n")
for schema in catalog.schemata:
file.write(object_heading("Schema", schema.name))
file.write(schema.temp_sql+";\n")
for table in schema.tables:
file.write(object_heading("Table", "%s.%s" % (schema.name, table.name)))
file.write(table.temp_sql+";\n")
for view in schema.views:
file.write(object_heading("View", "%s.%s" % (schema.name, view.name)))
file.write(view.temp_sql+";\n")
for routine in schema.routines:
file.write(object_heading("Routine", "%s.%s" % (schema.name, routine.name)))
file.write(routine.temp_sql)
for table in schema.tables:
for trigger in table.triggers:
file.write(object_heading("Trigger", "%s.%s" % (schema.name, trigger.name)))
file.write(trigger.temp_sql+";\n")
postamble = catalog.customData["migration:postamble"]
if postamble and postamble.temp_sql:
#file.write(object_heading("Postamble script", ""))
file.write(postamble.temp_sql+"\n")
file.close()
return 1
def __init__(self, editor, schema):
mforms.Box.__init__(self, False)
self.set_managed()
self.set_release_on_add()
self.schema = schema
self.editor = editor
self.target_version = Version.fromgrt(editor.serverVersion)
self.main = mforms.newBox(False)
self.add(self.main, True, True)
self.error_heading = mforms.newLabel("")
self.error_heading.set_style(mforms.BoldStyle)
self.error_body = mforms.newLabel("")
self.error_box = mforms.newBox(False)
self.error_box.set_spacing(8)
self.error_box.set_padding(8)
self.error_box.add(self.error_heading, True, False)
self.error_box.add(self.error_body, True, False)
self.add(self.error_box, True, False)
self.error_box.show(False)
self.main.set_padding(8)
self.main.set_spacing(8)
self.tree = mforms.newTreeNodeView(mforms.TreeFlatList
| mforms.TreeAltRowColors
| mforms.TreeShowColumnLines)
self.tree.set_selection_mode(mforms.TreeSelectMultiple)
#Check if there is method to load the columns, if not, skip.
if hasattr(self, "preload_columns") and callable(
getattr(self, "preload_columns")):
self.preload_columns()
for field, type, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
self.tree.add_column(type, caption, width, False)
self.tree.end_columns()
self.tree.set_allow_sorting(True)
self.main.add(self.tree, True, True)
self.menu = mforms.newContextMenu()
self.menu.add_will_show_callback(self.menu_will_show)
self.tree.add_activated_callback(self.on_activate)
self.tree.set_context_menu(self.menu)
self.icon_path = mforms.App.get().get_resource_path(self.klass +
".16x16.png")
self.bad_icon_path = mforms.App.get().get_resource_path(
self.bad_icon_path)
self.row_count = mforms.newLabel("")
self.row_count.set_text("")
self.refresh_btn = mforms.newButton()
self.refresh_btn.set_text("Refresh")
self.refresh_btn.add_clicked_callback(self.refresh)
self.bbox = mforms.newBox(True)
self.bbox.set_spacing(8)
self.main.add(self.bbox, False, True)
self.bbox.add(self.row_count, False, True)
self.bbox.add_end(self.refresh_btn, False, True)
for caption, callback_name in self.actions:
if not caption:
self.bbox.add(mforms.newLabel(" "), False, True)
continue
btn = mforms.newButton()
btn.set_text(caption)
btn.add_clicked_callback(getattr(self, callback_name))
self.bbox.add(btn, False, True)
def __init__(self, owner):
WizardPage.__init__(self, owner, "Import Options", wide=True)
self.layer_name = None
self.column_list = []
self.support_spatial_index = Version.fromgrt(owner.editor.serverVersion).is_supported_mysql_version_at_least(5, 7, 5)
def upgrade_password_format(self):
queries = []
if self.password != self.confirm_password:
raise WBSecurityValidationError("The new password and its confirmation don't match. Please re-enter them.")
queries.append("use mysql")
if not self.host:
raise WBSecurityValidationError("Host name must not be blank")
fields = {
"user" : escape_sql_string(self.username) or "NULL",
"host" : escape_sql_string(self.host) or "",
"password" : escape_sql_string(self.password or ""),
}
queries.append("SET old_passwords = 0")
if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version.is_supported_mysql_version_at_least(5, 5, 7):
queries.append("UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = '******' AND host = '%(host)s'" % fields)
queries.append("FLUSH PRIVILEGES")
change_pw = CHANGE_PASSWORD_QUERY if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(5,7,6) else CHANGE_PASSWORD_QUERY_576
queries.append(change_pw % fields)
queries.append("FLUSH PRIVILEGES")
action = "changing"
for query in queries:
try:
self._owner.ctrl_be.exec_sql(query)
except QueryError, e:
if e.error == 1142:
raise Exception("Error %s account %s@%s: Insufficient rights to perform operation"%(action, self.username, self.host))
else:
raise Exception("Error %s account %s@%s: %s"%(action, self.username, self.host, e.errortext or e))
except Exception, e:
raise Exception("Error %s account %s@%s: %s"%(action, self.username, self.host, e))
def migrateDatatypeForColumn(self, state, source_column, target_column):
targetCatalog = state.targetCatalog
mysql_simpleTypes = dict( (datatype.name.upper(), datatype) for datatype in targetCatalog.simpleDatatypes )
source_type = source_column.simpleType
if not source_type and source_column.userType:
# evaluate user type
source_type = source_column.userType.actualType
target_column.flags.extend(source_column.userType.flags)
if source_type:
# Decide which mysql datatype corresponds to the column datatype:
source_datatype = source_type.name.upper()
# string data types:
target_datatype = ''
if source_datatype in ['VARCHAR', 'NVARCHAR']:
if source_column.length == -1: # VARCHAR(MAX) or NVARCHAR(MAX)
target_datatype = 'LONGTEXT' #TODO: Give the user the choice for this target datatype
elif 0 < source_column.length < 256:
target_datatype = 'VARCHAR'
else: # MySQL versions > 5.0 can hold up to 65535 chars in a VARCHAR column
target_datatype = 'TEXT' if targetCatalog.version.majorNumber < 5 else 'VARCHAR'
elif source_datatype in ['TEXT', 'NTEXT']:
target_datatype = 'LONGTEXT'
elif source_datatype in ['CHAR', 'NCHAR']: # MSSQL CHAR's (also VARCHAR's) max length is 8000 non Unicode characters
if 0 < source_column.length < 256:
target_datatype = 'CHAR'
else:
target_datatype = 'TEXT'
# integer data types:
elif source_datatype in ['BIGINT', 'INT', 'SMALLINT']:
target_datatype = source_datatype
target_column.precision = -1
elif source_datatype == 'TINYINT':
target_datatype = source_datatype
target_column.precision = -1
if 'UNSIGNED' not in target_column.flags:
target_column.flags.append('UNSIGNED') # In MSSQL TINYINT is unsigned
elif source_datatype == 'UNIQUEIDENTIFIER':
target_datatype = 'VARCHAR'
target_column.length = 64
if 'UNIQUE' not in target_column.flags:
target_column.flags.append('UNIQUE') # uniqueid must be UNIQUE... bug #43098
state.addMigrationLogEntry(0, source_column, target_column,
"Source column type %s was migrated to %s(%s)" % (source_datatype, target_datatype, target_column.length))
elif source_datatype == 'SYSNAME': # the relevant info is in http://msdn.microsoft.com/en-us/library/ms191240(v=sql.105).aspx
target_datatype = 'VARCHAR'
target_column.length = 160
state.addMigrationLogEntry(0, source_column, target_column,
"Source column type %s was migrated to %s(%s)" % (source_datatype, target_datatype, target_column.length))
# floating point datatypes:
elif source_datatype in ['DECIMAL', 'NUMERIC']:
if source_column.scale == 0:
target_datatype = 'BIGINT'
if source_column.precision < 5:
target_datatype = 'SMALLINT'
elif source_column.precision < 7:
target_datatype = 'MEDIUMINT'
elif source_column.precision < 10:
target_datatype = 'INT'
target_column.precision = -1
else:
target_datatype = 'DECIMAL'
elif source_datatype == 'REAL':
target_datatype = 'FLOAT'
elif source_datatype == 'FLOAT':
if source_column.precision > 24:
target_datatype = 'DOUBLE'
target_column.precision = -1
elif source_datatype in ['MONEY', 'SMALLMONEY']:
target_datatype = 'DECIMAL'
target_column.precision = source_column.simpleType.numericPrecision
target_column.scale = source_column.simpleType.numericScale
# binary datatypes:
elif source_datatype == 'IMAGE':
if source_column.length == -1: # VARBINARY(MAX)
target_datatype = 'LONGBLOB' #TODO: Give the user the choice for this target datatype
elif 0 <= source_column.length < 256:
if source_datatype == 'IMAGE':
target_datatype = 'TINYBLOB'
else:
target_datatype = source_datatype
elif 0 <= source_column.length < 65536:
target_datatype = 'MEDIUMBLOB'
else:
target_datatype = 'LONGBLOB'
elif source_datatype == 'VARBINARY' and source_column.length == -1: # VARBINARY(MAX):
target_datatype = 'LONGBLOB'
# datetime datatypes:
elif source_datatype in ['DATETIME', 'SMALLDATETIME', 'DATETIME2', 'DATETIMEOFFSET']:
target_datatype = 'DATETIME'
target_column.precision = -1
target_version = Version.fromgrt(targetCatalog.version)
if target_version.is_supported_mysql_version_at_least(5,6,4) and source_datatype != 'SMALLDATETIME':
target_column.precision = source_column.precision if source_column.precision < 7 else 6
# timestamp datatypes
# In MS SQL Server a nonnullable timestamp column is semantically equivalent to a binary(8) column,
# and a nullable timestamp column is semantically equivalent to a varbinary(8) column.
elif source_datatype in ['TIMESTAMP', 'ROWVERSION']:
target_datatype = 'BINARY' if source_column.isNotNull else 'VARBINARY'
elif source_datatype == 'DATE':
target_datatype = 'DATE'
target_column.precision = -1
elif source_datatype == 'TIME':
target_datatype = 'TIME'
target_column.precision = -1
target_version = Version.fromgrt(targetCatalog.version)
if target_version.is_supported_mysql_version_at_least(5,6,4):
target_column.precision = source_column.precision if source_column.precision < 7 else 6
elif source_datatype == 'BIT':
target_datatype = 'TINYINT'
target_column.length = 1
state.addMigrationLogEntry(0, source_column, target_column,
"Source column type BIT was migrated to TINYINT(1)")
elif source_datatype == 'XML':
target_datatype = 'TEXT'
state.addMigrationLogEntry(0, source_column, target_column,
"Source column type XML was migrated to TEXT")
elif source_datatype in ['GEOMETRY', 'GEOGRAPHY']:
target_datatype = 'GEOMETRY'
elif source_datatype == 'HIERARCHYID':
target_datatype = 'VARCHAR'
target_column.length = 255
state.addMigrationLogEntry(1, source_column, target_column,
"Source column type HIERARCHYID was migrated to VARCHAR(255)")
elif source_datatype == 'SQL_VARIANT':
target_datatype = 'TEXT'
state.addMigrationLogEntry(1, source_column, target_column,
"Source column type %s was migrated to %s(%s)" % (source_datatype, target_datatype, target_column.length))
else:
# just fall back to same type name and hope for the best
target_datatype = source_datatype
if mysql_simpleTypes.has_key(target_datatype):
target_column.simpleType = mysql_simpleTypes[target_datatype]
else:
grt.log_warning("MSSQL migrateTableColumnsToMySQL", "Can't find datatype %s for type %s\n" % (target_datatype, source_datatype))
state.addMigrationLogEntry(2, source_column, target_column,
'Could not migrate column "%s" in "%s": Unknown datatype "%s"' % (target_column.name, source_column.owner.name, source_datatype) )
return False
return True
else:
state.addMigrationLogEntry(2, source_column, target_column,
'Could not migrate type of column "%s" in "%s" (%s)' % (target_column.name, source_column.owner.name, source_column.formattedRawType) )
return False
return True
def get_query(self):
cols = []
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(Version.fromstr(min_version)):
continue
try:
cols.append("`%s`" % field_obj['field'])
except:
cols.append("`%s`" % field_obj)
return self.show_query % {'schema' : self.schema, 'columns' : ", ".join(cols)}
def setup_info_table(self, info_table, info, params):
info_table.set_row_count(len(info) + 1)
for i, item in enumerate(info):
(label, value_source) = item
if callable(value_source):
value = value_source(*params)
else:
value = value_source
if self.controls.has_key(label):
info_table.remove(self.controls[label][0])
else:
l = mforms.newLabel(label + ":")
l.set_name(label)
info_table.add(l, 0, 1, i, i + 1,
mforms.VFillFlag | mforms.HFillFlag)
is_gtid_mode_setable = label == 'GTID Mode:' and self.ctrl_be.target_version >= Version(
5, 7, 6)
if type(value) is bool or value is None:
b = StateIcon()
if label and label != '':
b.set_name(label + " Value")
b.set_state(value)
info_table.add(
b, 1, 2, i, i + 1,
mforms.HFillFlag | mforms.HExpandFlag | mforms.VFillFlag)
self.controls[label] = (b, value_source)
elif type(value) is tuple:
b = StateIcon()
if label and label != '':
b.set_name(label + " Value")
b.set_state(value[0])
if value[0] and value[1]:
b.set_text(value[1])
info_table.add(
b, 1, 2, i, i + 1,
mforms.HFillFlag | mforms.HExpandFlag | mforms.VFillFlag)
self.controls[label] = (b, value_source)
else:
if is_gtid_mode_setable:
self.gtid_mode_selector = mforms.newSelector()
if label and label != '':
self.gtid_mode_selector.set_name(label + " Value")
self.gtid_mode_selector.add_items(
["OFF", "UPGRADE_STEP_1", "UPGRADE_STEP_1", "ON"])
self.gtid_mode_selector.set_selected(
self.gtid_mode_selector.index_of_item_with_title(
value_source))
self.gtid_mode_selector.add_changed_callback(
self._gtid_mode_changed)
info_table.add(
self.gtid_mode_selector, 1, 2, i, i + 1,
mforms.HFillFlag | mforms.HExpandFlag
| mforms.VFillFlag)
self.controls[label] = (self.gtid_mode_selector,
value_source)
else:
l2 = mforms.newLabel(value or "")
if label and label != '':
l2.set_name(label + " Value")
l2.set_style(mforms.BoldStyle)
info_table.add(
l2, 1, 2, i, i + 1, mforms.HFillFlag
| mforms.HExpandFlag | mforms.VFillFlag)
self.controls[label] = (l2, value_source)
info_table.add(mforms.newLabel(""), 0, 1, len(info),
len(info) + 1, mforms.HFillFlag) # blank space
return info_table
def _create_copy_script(self):
path = self.main.plan.state.dataBulkTransferParams["GenerateCopyScript"]
debug_table_copy = self.main.plan.state.dataBulkTransferParams["DebugTableCopy"]
truncate_target_tables = self.main.plan.state.dataBulkTransferParams["TruncateTargetTables"]
worker_count = self.main.plan.state.dataBulkTransferParams["workerCount"]
f = open(path, "w+")
if sys.platform == "win32":
def cmt(s):
return "REM " + s + "\n"
else:
os.chmod(path, 0700)
def cmt(s):
return "# " + s + "\n"
f.write("#!/bin/sh\n")
f.write(cmt("Workbench Table Data copy script"))
f.write(cmt("Workbench Version: %s" % Version.fromgrt(grt.root.wb.info.version)))
f.write(cmt(""))
f.write(cmt("Execute this to copy table data from a source RDBMS to MySQL."))
f.write(cmt("Edit the options below to customize it. You will need to provide passwords, at least."))
f.write(cmt(""))
f.write(
cmt(
"Source DB: %s (%s)"
% (
self.main.plan.migrationSource.connection.hostIdentifier,
self.main.plan.migrationSource.connection.driver.owner.caption,
)
)
)
f.write(cmt("Target DB: %s" % self.main.plan.migrationTarget.connection.hostIdentifier))
f.write("\n\n")
if sys.platform == "win32":
f.write("@ECHO OFF\n")
f.write("REM Source and target DB passwords\n")
f.write("set arg_source_password=\n")
f.write("set arg_target_password=\n")
f.write(
"""
IF [%arg_source_password%] == [] (
IF [%arg_target_password%] == [] (
ECHO WARNING: Both source and target RDBMSes passwords are empty. You should edit this file to set them.
)
)
"""
)
f.write("set arg_worker_count=%d\n" % worker_count)
f.write("REM Uncomment the following options according to your needs\n")
f.write("\n")
f.write("REM Whether target tables should be truncated before copy\n")
f.write(("" if truncate_target_tables else "REM ") + "set arg_truncate_target=--truncate-target\n")
# f.write("REM Copy tables incrementally. Useful for updating table contents after an initial migration\n")
# f.write("REM set arg_incremental_copy=--incremental-copy\n")
f.write("REM Enable debugging output\n")
f.write(("" if debug_table_copy else "REM ") + "set arg_debug_output=--log-level=debug3\n")
f.write("\n\n")
f.write("REM Creation of file with table definitions for copytable\n\n")
# Creates a temporary file name with the tables to be migrated
filename = '"%TMP%\wb_tables_to_migrate.txt"'
f.write("set table_file=%s\n" % filename)
f.write("TYPE NUL > %s\n" % filename)
for table in self._working_set.values():
fields = []
fields.append(table["source_schema"])
fields.append(table["source_table"])
fields.append(table["target_schema"])
fields.append(table["target_table"])
fields.append(table["source_primary_key"].replace("'", r"\'"))
fields.append(table["target_primary_key"].replace("'", r"\'"))
fields.append(table["select_expression"].replace("'", r"\'"))
line = "ECHO %s >> %s" % ("\t".join(fields), filename)
f.write(line + "\n")
f.write("\n\n")
f.write(self.main.plan.wbcopytables_path)
for arg in self._transferer.helper_basic_arglist(True):
f.write(" %s" % arg)
f.write(
' --source-password="******" --target-password="******" --table-file="%table_file%"'
)
f.write(" --thread-count=%arg_worker_count% %arg_truncate_target% %arg_debug_output%")
f.write("\n\n")
f.write("REM Removes the file with the table definitions\n")
f.write("DEL %s\n" % filename)
else:
f.write("# Source and target DB passwords\n")
f.write("arg_source_password=\n")
f.write("arg_target_password=\n")
f.write(
"""
if [ -z "$arg_source_password" ] && [ -z "$arg_target_password" ] ; then
echo WARNING: Both source and target RDBMSes passwords are empty. You should edit this file to set them.
fi
"""
)
f.write("arg_worker_count=%d\n" % worker_count)
f.write("# Uncomment the following options according to your needs\n")
f.write("\n")
f.write("# Whether target tables should be truncated before copy\n")
f.write(("" if truncate_target_tables else "# ") + "arg_truncate_target=--truncate-target\n")
# f.write("# Copy tables incrementally. Useful for updating table contents after an initial migration\n")
# f.write("#arg_incremental_copy=--incremental-copy\n")
f.write("# Enable debugging output\n")
f.write(("" if debug_table_copy else "# ") + "arg_debug_output=--log-level=debug3\n")
f.write("\n")
f.write(self.main.plan.wbcopytables_path)
for arg in self._transferer.helper_basic_arglist(True):
f.write(" %s" % arg)
f.write(' --source-password="******" --target-password="******"')
f.write(" --thread-count=$arg_worker_count $arg_truncate_target $arg_debug_output")
for table in self._working_set.values():
opt = "--table '%s' '%s' '%s' '%s' '%s' '%s' '%s'" % (
table["source_schema"],
table["source_table"],
table["target_schema"],
table["target_table"],
table["source_primary_key"].replace("'", "'"),
table["target_primary_key"].replace("'", "'"),
table["select_expression"].replace("'", "'"),
)
f.write(" " + opt)
f.write("\n\n")
f.close()
self.send_info("Table copy script written to %s" % path)
def __init__(self, conn):
mforms.Form.__init__(self, None)
self._conn = conn
self.versionstr = conn.parameterValues.get("serverVersion", None)
try:
self.version = Version.fromstr(self.versionstr)
except:
self.version = None
self.set_title("Password Expired")
vbox = mforms.newBox(False)
vbox.set_padding(20)
vbox.set_spacing(18)
user = conn.parameterValues["userName"]
l = newLabel(
"Password for MySQL account '%s'@%s expired.\nPlease pick a new password:"******"Mysql@", "")))
l.set_style(mforms.BoldStyle)
vbox.add(l, False, True)
box = mforms.newTable()
box.set_padding(1)
box.set_row_count(3)
box.set_column_count(2)
box.set_column_spacing(7)
box.set_row_spacing(8)
hbox = mforms.newBox(True)
hbox.set_spacing(12)
icon = mforms.newImageBox()
icon.set_image(mforms.App.get().get_resource_path("wb_lock.png"))
hbox.add(icon, False, True)
hbox.add(box, True, True)
vbox.add(hbox, False, True)
self.old_password = mforms.newTextEntry(mforms.PasswordEntry)
self.old_password.set_name("Old Password")
box.add(newLabel("Old Password:"******"New Password")
box.add(newLabel("New Password:"******"Confirm Password")
box.add(newLabel("Confirm:", True), 0, 1, 2, 3,
mforms.HFillFlag | mforms.VFillFlag)
box.add(self.confirm, 1, 2, 2, 3,
mforms.HFillFlag | mforms.HExpandFlag)
self.legacy = mforms.newCheckBox()
self.legacy.set_text("This server version is < 5.7")
if self.version:
self.legacy.set_active(
not self.version.is_supported_mysql_version_at_least(5, 7))
self.legacy.show(False)
else:
self.legacy.show(True)
vbox.add(self.legacy, mforms.HFillFlag | mforms.VFillFlag)
bbox = newBox(True)
bbox.set_spacing(8)
self.ok = newButton()
self.ok.set_text("OK")
self.cancel = newButton()
self.cancel.set_text("Cancel")
mforms.Utilities.add_end_ok_cancel_buttons(bbox, self.ok, self.cancel)
vbox.add_end(bbox, False, True)
self.set_content(vbox)
self.set_size(500, 300)
self.center()
def migrateDatatypeForColumn(self, state, source_column, target_column):
targetCatalog = state.targetCatalog
mysql_simpleTypes = dict((datatype.name.upper(), datatype)
for datatype in targetCatalog.simpleDatatypes)
source_type = source_column.simpleType
if not source_type and source_column.userType:
# evaluate user type
source_type = source_column.userType.actualType
target_column.flags.extend(source_column.userType.flags)
if source_type:
target_version = Version.fromgrt(targetCatalog.version)
# Decide which mysql datatype corresponds to the column datatype:
source_datatype = source_type.name.upper()
grt.log_debug3(
"Migration",
"Migrating source column '%s' - type: %s, length: %s\n" %
(source_column.name, source_datatype, source_column.length))
# string data types:
target_datatype = ''
#NCHAR and NVARCHAR in Microsoft SQL Server is always encoded as UCS-2 (UTF-16)
if source_datatype in [
'NCHAR', 'NVARCHAR'
] and target_version.is_supported_mysql_version_at_least(5, 5, 0):
target_column.characterSetName = 'utf8mb4'
if source_datatype in ['VARCHAR', 'NVARCHAR']:
if source_column.length == -1: # VARCHAR(MAX) or NVARCHAR(MAX)
target_datatype = 'LONGTEXT' #TODO: Give the user the choice for this target datatype
elif 0 < source_column.length < 256:
target_datatype = 'VARCHAR'
else: # MySQL versions > 5.0 can hold up to 65535 chars in a VARCHAR column
target_datatype = 'TEXT' if targetCatalog.version.majorNumber < 5 else 'VARCHAR'
elif source_datatype in ['TEXT', 'NTEXT']:
target_datatype = 'LONGTEXT'
elif source_datatype in [
'CHAR', 'NCHAR'
]: # MSSQL CHAR's (also VARCHAR's) max length is 8000 non Unicode characters
if 0 < source_column.length < 256:
target_datatype = 'CHAR'
else:
target_datatype = 'TEXT'
# integer data types:
elif source_datatype in ['BIGINT', 'INT', 'SMALLINT']:
target_datatype = source_datatype
target_column.precision = -1
elif source_datatype == 'TINYINT':
target_datatype = source_datatype
target_column.precision = -1
if 'UNSIGNED' not in target_column.flags:
target_column.flags.append(
'UNSIGNED') # In MSSQL TINYINT is unsigned
elif source_datatype == 'UNIQUEIDENTIFIER':
target_datatype = 'VARCHAR'
target_column.length = 64
if 'UNIQUE' not in target_column.flags:
target_column.flags.append(
'UNIQUE') # uniqueid must be UNIQUE... bug #43098
state.addMigrationLogEntry(
0, source_column, target_column,
"Source column type %s was migrated to %s(%s)" %
(source_datatype, target_datatype, target_column.length))
elif source_datatype == 'SYSNAME': # the relevant info is in http://msdn.microsoft.com/en-us/library/ms191240(v=sql.105).aspx
target_datatype = 'VARCHAR'
target_column.length = 160
state.addMigrationLogEntry(
0, source_column, target_column,
"Source column type %s was migrated to %s(%s)" %
(source_datatype, target_datatype, target_column.length))
# floating point datatypes:
elif source_datatype in ['DECIMAL', 'NUMERIC']:
if source_column.scale == 0:
target_datatype = 'BIGINT'
if source_column.precision < 5:
target_datatype = 'SMALLINT'
elif source_column.precision < 7:
target_datatype = 'MEDIUMINT'
elif source_column.precision < 10:
target_datatype = 'INT'
target_column.precision = -1
else:
target_datatype = 'DECIMAL'
elif source_datatype == 'REAL':
target_datatype = 'FLOAT'
elif source_datatype == 'FLOAT':
if source_column.precision > 24:
target_datatype = 'DOUBLE'
target_column.precision = -1
elif source_datatype in ['MONEY', 'SMALLMONEY']:
target_datatype = 'DECIMAL'
target_column.precision = source_column.simpleType.numericPrecision
target_column.scale = source_column.simpleType.numericScale
# binary datatypes:
elif source_datatype == 'IMAGE':
target_datatype = 'LONGBLOB'
elif source_datatype == 'VARBINARY' and source_column.length == -1: # VARBINARY(MAX):
target_datatype = 'LONGBLOB'
# datetime datatypes:
elif source_datatype in [
'DATETIME', 'SMALLDATETIME', 'DATETIME2', 'DATETIMEOFFSET'
]:
target_datatype = 'DATETIME'
target_column.length = -1
if target_version.is_supported_mysql_version_at_least(
5, 6, 4) and source_datatype != 'SMALLDATETIME':
target_column.length = source_column.precision if source_column.precision < 7 else 6
# timestamp datatypes
# In MS SQL Server a nonnullable timestamp column is semantically equivalent to a binary(8) column,
# and a nullable timestamp column is semantically equivalent to a varbinary(8) column.
elif source_datatype in ['TIMESTAMP', 'ROWVERSION']:
target_datatype = 'BINARY' if source_column.isNotNull else 'VARBINARY'
elif source_datatype == 'DATE':
target_datatype = 'DATE'
target_column.precision = -1
elif source_datatype == 'TIME':
target_datatype = 'TIME'
target_column.precision = -1
if target_version.is_supported_mysql_version_at_least(5, 6, 4):
target_column.precision = source_column.precision if source_column.precision < 7 else 6
elif source_datatype == 'BIT':
target_datatype = 'TINYINT'
target_column.length = 1
state.addMigrationLogEntry(
0, source_column, target_column,
"Source column type BIT was migrated to TINYINT(1)")
elif source_datatype == 'XML':
target_datatype = 'TEXT'
state.addMigrationLogEntry(
0, source_column, target_column,
"Source column type XML was migrated to TEXT")
elif source_datatype in ['GEOMETRY', 'GEOGRAPHY']:
target_datatype = 'GEOMETRY'
elif source_datatype == 'HIERARCHYID':
target_datatype = 'VARCHAR'
target_column.length = 255
state.addMigrationLogEntry(
1, source_column, target_column,
"Source column type HIERARCHYID was migrated to VARCHAR(255)"
)
elif source_datatype == 'SQL_VARIANT':
target_datatype = 'TEXT'
state.addMigrationLogEntry(
1, source_column, target_column,
"Source column type %s was migrated to %s(%s)" %
(source_datatype, target_datatype, target_column.length))
else:
# just fall back to same type name and hope for the best
target_datatype = source_datatype
if mysql_simpleTypes.has_key(target_datatype):
target_column.simpleType = mysql_simpleTypes[target_datatype]
else:
grt.log_warning(
"Migration", "MSSQL migrateTableColumnsToMySQL",
"Can't find datatype %s for type %s\n" %
(target_datatype, source_datatype))
state.addMigrationLogEntry(
2, source_column, target_column,
'Could not migrate column "%s" in "%s": Unknown datatype "%s"'
% (target_column.name, source_column.owner.name,
source_datatype))
return False
return True
else:
state.addMigrationLogEntry(
2, source_column, target_column,
'Could not migrate type of column "%s" in "%s" (%s)' %
(target_column.name, source_column.owner.name,
source_column.formattedRawType))
return False
return True
def refresh(self):
self.tree.clear()
for table in self.table_manager.table_names or []:
try:
rset = self.editor.executeManagementQuery(self.show_query % {'table' : table.replace("`", "``"), 'schema' : self.schema.replace("`", "``")}, 0)
except grt.DBError, err:
log_warning("Error querying index info for %s.%s: %s\n" % (self.schema, table, err[0]))
continue
ok = rset.goToFirstRow()
while ok:
if not self.filter or self.filter(rset):
node = self.tree.add_node()
node.set_icon_path(0, self.icon_path)
i = 0
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(Version.fromstr(min_version)):
continue
format_func = None
field = None
try:
format_func = field_obj['format_func']
field = field_obj['field']
except:
field = field_obj
if ctype == mforms.IntegerColumnType:
if type(field) is int:
node.set_int(i, rset.intFieldValue(field) or 0)
else:
node.set_int(i, rset.intFieldValueByName(field) or 0)
else:
if type(field) is int:
node.set_string(i, rset.stringFieldValue(field) or "" if format_func is None else format_func(rset.stringFieldValue(field)))
else:
node.set_string(i, rset.stringFieldValueByName(field) or "" if format_func is None else format_func(rset.stringFieldValueByName(field)))
i += 1
ok = rset.nextRow()
"MAX_QUERIES_PER_HOUR" : str(self.max_questions),
"MAX_UPDATES_PER_HOUR" : str(self.max_updates),
"MAX_CONNECTIONS_PER_HOUR" : str(self.max_connections),
}
if self._owner.has_max_user_connections:
self.max_user_connections = result.intByName("max_user_connections")
self._orig_account_limits["MAX_USER_CONNECTIONS"] = str(self.max_user_connections)
if self._owner.has_plugin:
self.auth_plugin = result.stringByName("plugin")
if self._owner.has_authentication_string:
self.auth_string = result.stringByName("authentication_string")
self.password_expired = False
if self._owner.has_password_expired:
self.password_expired = result.stringByName("password_expired") == 'Y'
password_column = 'password' if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(5,7,6) else 'authentication_string'
self.old_authentication = len(result.stringByName(password_column)) == 16
self.blank_password = len(result.stringByName(password_column)) == 0
self._global_privs = set()
for priv in self._owner.global_privilege_names:
if result.stringByName(priv) == 'Y':
self._global_privs.add(priv)
self.forget_custom_privs()
""" not necessary, IS is accessible to all
# privs from information_schema tables
query = GET_ACCOUNT_IS_TABLE_PRIVS_QUERY % {"user":username,"host":hostname}
result = modules.DbMySQLQuery.executeQuery(self._owner._connection, query)
if result < 0:
def get_query(self):
if len(self.columns) == 0: # Probably user doesn't have privileges to list the privilege tables.
return []
output = []
fields = []
fields_where = []
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(Version.fromstr(min_version)):
continue
field = None
try:
field = field_obj['field']
except:
field = field_obj
if field == "scope":
continue
if field not in ['Db']:
fields.append(field)
if field not in ['User','Host','Db']:
fields_where.append("%s = 'Y'" % field.replace(" ","_"))
output.append("SELECT '<global>' as Db,%(sel_fields)s FROM mysql.user WHERE %(where_fields)s" % {'sel_fields' : ",".join(fields), 'where_fields' : " OR ".join(fields_where)})
output.append("SELECT Db,%(sel_fields)s FROM mysql.db WHERE '%(schema)s' like db" % {'sel_fields' : ",".join(fields), 'schema' : self.schema})
return output
def save(self):
queries = []
if self.password != self.confirm_password:
raise WBSecurityValidationError("The new password and its confirmation don't match. Please re-enter them.")
# workaround for server bug with replication #14358854
queries.append("use mysql")
#if not self.username:
# raise WBSecurityValidationError("Username must not be blank")
if not self.host:
raise WBSecurityValidationError("Host name must not be blank")
# check if username + host is duplicated
if self.is_commited and (self.username != self._orig_username or self.host != self._orig_host):
if (self.username, self.host) in self._owner.account_names:
raise WBSecurityValidationError("The '%s' account already exists and cannot be saved." % (self.formatted_name()))
elif not self.is_commited:
if (self.username, self.host, True) in self._owner.account_names:
raise WBSecurityValidationError("The '%s' account already exists and cannot be saved." % (self.formatted_name()))
fields = {
"old_user" : escape_sql_string(self._orig_username) if self._orig_username else self._orig_username,
"old_host" : escape_sql_string(self._orig_host) if self._orig_host else self._orig_host,
"user" : escape_sql_string(self.username) or "NULL",
"host" : escape_sql_string(self.host) or "",
"password" : escape_sql_string(self.password or ""),
"auth_plugin" : escape_sql_string(self.auth_plugin) if self.auth_plugin else None,
"auth_string" : escape_sql_string(self.auth_string) if self.auth_string else None
}
password_already_set = False
if not self.is_commited: # This is a new account
if self.auth_plugin:
if self.auth_string is None:
create_query = CREATE_USER_QUERY_PLUGIN
elif self.auth_plugin == 'mysql_native_password':
if (self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(5, 7, 0)):
create_query = CREATE_USER_QUERY_PLUGIN
else:
create_query = CREATE_USER_QUERY_PLUGIN_AUTH_NATIVE
elif self.auth_plugin == 'caching_sha2_password':
create_query = CREATE_USER_QUERY_PLUGIN_AUTH_CACHING
else:
create_query = CREATE_USER_QUERY_PLUGIN_AUTH_STRING
else:
create_query = CREATE_USER_QUERY
password_already_set = True
queries[:0] = [ create_query % fields ] # This query should be the first in the batch
# WARNING: Now the pwd is sent in clear text
else: # The account already exists
assert self._orig_username is not None and self._orig_host is not None
if self._orig_username != self.username or self._orig_host != self.host: # Rename the user
queries[:0] = [ RENAME_USER_QUERY % fields ] # This query should be the first in the batch
names = ["MAX_QUERIES_PER_HOUR", "MAX_UPDATES_PER_HOUR", "MAX_CONNECTIONS_PER_HOUR"] + (self._owner.has_max_user_connections and ["MAX_USER_CONNECTIONS"] or [])
values = [str(s) for s in [self.max_questions, self.max_updates, self.max_connections] + (self._owner.has_max_user_connections and [self.max_user_connections] or [])]
account_limits = dict(zip(names, values))
limits_changed = account_limits != self._orig_account_limits
is_normal_priv = lambda priv: ( PrivilegeInfo.get(priv, (None,None))[0] and
PrivilegeInfo.get(priv, (None,None))[0][0] != '*'
)
all_normal_privs = set( priv for priv in self._owner.global_privilege_names if is_normal_priv(priv) )
new_granted_privs = (self._global_privs - self._orig_global_privs) & all_normal_privs
orig_revoked_privs = all_normal_privs - self._orig_global_privs
new_revoked_privs = all_normal_privs - self._global_privs - orig_revoked_privs
if new_granted_privs or limits_changed:
if 'Grant_priv' in new_granted_privs:
account_limits['GRANT'] = 'OPTION'
new_granted_privs.remove('Grant_priv')
if (all_normal_privs - new_granted_privs) <= set(['Grant_priv']):
priv_list = ['ALL PRIVILEGES']
else:
priv_list = [ PrivilegeInfo[priv][0] for priv in new_granted_privs ]
fields['granted_privs'] = ', '.join(priv_list) or 'USAGE'
grant_query = GRANT_GLOBAL_PRIVILEGES_QUERY % fields
with_clause = ''
for key, value in account_limits.iteritems(): #name, value in zip(names, values):
if value != self._orig_account_limits.get(key):
if key == "GRANT" and value == "OPTION":
queries.append(grant_query + "WITH GRANT OPTION")
continue
if not with_clause:
with_clause = ' WITH '
with_clause += "%s %s "%(key, value)
if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version >= Version(8, 0, 5):
queries.append(grant_query)
queries.append((ALTER_USER_RESOURCES % fields) + with_clause)
else:
queries.append(grant_query + with_clause)
if new_revoked_privs:
if all_normal_privs - new_revoked_privs: #set(self._owner.global_privilege_names) - revoked_privs_set: # Revoke a subset of all privs
priv_list = [ PrivilegeInfo[priv][0] for priv in new_revoked_privs ]
fields['revoked_privs'] = ', '.join(priv_list)
queries.append(REVOKE_GLOBAL_PRIVILEGES_QUERY % fields)
else: # All privs are to be revoked so use the revoke all query
queries.append(REVOKE_ALL % fields)
if self.password != self._orig_password and not password_already_set:
change_pw = CHANGE_PASSWORD_QUERY if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(5,7,6) else CHANGE_PASSWORD_QUERY_576
blank_pw = BLANK_PASSWORD_QUERY if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(5,7,6) else BLANK_PASSWORD_QUERY
# special hack required by server to handle sha256 password accounts
if self._owner.ctrl_be.target_version and self._owner.ctrl_be.target_version < Version(8, 0, 5):
if self.auth_plugin == "sha256_password":
queries.append("SET old_passwords = 2")
else:
queries.append("SET old_passwords = 0")
if fields["password"]:
queries.append(change_pw % fields)
else:
queries.append(blank_pw % fields)
action = "changing" if self.is_commited else "creating"
for query in queries:
try:
self._owner.ctrl_be.exec_sql(query)
except QueryError, e:
if e.error == 1142:
raise Exception("Error %s account %s@%s: Insufficient rights to perform operation"%(action, self.username, self.host))
else:
raise Exception("Error %s account %s@%s: %s"%(action, self.username, self.host, e.errortext or e))
except Exception, e:
raise Exception("Error %s account %s@%s: %s"%(action, self.username, self.host, e))
def start_import(self):
if not self._last_analyze:
return False
if self._new_table:
if not self.prepare_new_table():
return False
if self._truncate_table:
self.update_progress(0.0, "Truncate table")
self._editor.executeManagementCommand("TRUNCATE TABLE %s" % self._table_w_prefix, 1)
result = True
with open(self._filepath, 'rb') as csvfile:
self.update_progress(0.0, "Prepare Import")
dest_col_order = list(set([i['dest_col'] for i in self._mapping if i['active']]))
query = """PREPARE stmt FROM 'INSERT INTO %s (%s) VALUES(%s)'""" % (self._table_w_prefix, ",".join(["`%s`" % col for col in dest_col_order]), ",".join(["?" for i in dest_col_order]))
col_order = dict([(i['dest_col'], i['col_no']) for i in self._mapping if i['active']])
col_type = dict([(i['dest_col'], i['type']) for i in self._mapping if i['active']])
is_server_5_7 = self._targetVersion.is_supported_mysql_version_at_least(Version.fromstr("5.7.5"))
self._editor.executeManagementCommand(query, 1)
try:
is_header = self.has_header
reader = UniReader(csvfile, self.dialect, encoding=self._encoding)
self._max_rows = os.path.getsize(self._filepath)
self.update_progress(0.0, "Begin Import")
for row in reader:
if self._thread_event and self._thread_event.is_set():
self._editor.executeManagementCommand("DEALLOCATE PREPARE stmt", 1)
log_debug2("Worker thread was stopped by user")
self.update_progress(round(self._current_row / self._max_rows, 2), "Import stopped by user request")
return False
self._current_row = float(csvfile.tell())
if is_header:
is_header = False
continue
for i, col in enumerate(col_order):
if col_order[col] >= len(row):
log_error("Can't find col: %s in row: %s" % (col_order[col], row))
result = False
break
val = row[col_order[col]]
col_name = col_order[col]
if col_type[col] == "geometry":
if is_server_5_7:
val = """ST_GeomFromText("%s")""" % row[col_name]
else:
val = """GeomFromText("%s")""" % row[col_name]
self._editor.executeManagementCommand("""SET @a%d = %s """ % (i, val), 0)
else:
if col_type[col] == 'double':
val = row[col_name].replace(self._decimal_separator, '.')
elif col_type[col] == 'datetime':
val = datetime.datetime.strptime(row[col_name], self._date_format).strftime("%Y-%m-%d %H:%M:%S")
if hasattr(val, "replace"):
val = val.replace("\\", "\\\\").replace("'", "\\'")
self._editor.executeManagementCommand("""SET @a%d = '%s' """ % (i, val), 0)
else:
try:
self._editor.executeManagementCommand("EXECUTE stmt USING %s" % ", ".join(['@a%d' % i for i, col in enumerate(col_order)]), 0)
self.item_count = self.item_count + 1
self.update_progress(round(self._current_row / self._max_rows, 2), "Data import")
except Exception, e:
log_error("Row import failed with error: %s" % e)
self.update_progress(round(self._current_row / self._max_rows, 2), "Row import failed with error: %s" % e)
result = False
self.update_progress(1.0, "Import finished")
except Exception, e:
import traceback
log_debug3("Import failed traceback: %s" % traceback.format_exc())
log_error("Import failed: %s" % e)
class ObjectManager(mforms.Box):
filter = None
icon_column = None
bad_icon_path = "task_error.png"
node_name = None
actions = []
def __init__(self, editor, schema):
mforms.Box.__init__(self, False)
self.set_managed()
self.set_release_on_add()
self.schema = schema
self.editor = editor
self.target_version = Version.fromgrt(editor.serverVersion)
self.main = mforms.newBox(False)
self.add(self.main, True, True)
self.error_heading = mforms.newLabel("")
self.error_heading.set_style(mforms.BoldStyle)
self.error_body = mforms.newLabel("")
self.error_box = mforms.newBox(False)
self.error_box.set_spacing(8)
self.error_box.set_padding(8)
self.error_box.add(self.error_heading, True, False)
self.error_box.add(self.error_body, True, False)
self.add(self.error_box, True, False)
self.error_box.show(False)
self.main.set_padding(8)
self.main.set_spacing(8)
self.tree = mforms.newTreeNodeView(mforms.TreeFlatList
| mforms.TreeAltRowColors
| mforms.TreeShowColumnLines)
self.tree.set_selection_mode(mforms.TreeSelectMultiple)
#Check if there is method to load the columns, if not, skip.
if hasattr(self, "preload_columns") and callable(
getattr(self, "preload_columns")):
self.preload_columns()
for field, type, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
self.tree.add_column(type, caption, width, False)
self.tree.end_columns()
self.tree.set_allow_sorting(True)
self.main.add(self.tree, True, True)
self.menu = mforms.newContextMenu()
self.menu.add_will_show_callback(self.menu_will_show)
self.tree.add_activated_callback(self.on_activate)
self.tree.set_context_menu(self.menu)
self.icon_path = mforms.App.get().get_resource_path(self.klass +
".16x16.png")
self.bad_icon_path = mforms.App.get().get_resource_path(
self.bad_icon_path)
self.row_count = mforms.newLabel("")
self.row_count.set_text("")
self.refresh_btn = mforms.newButton()
self.refresh_btn.set_text("Refresh")
self.refresh_btn.add_clicked_callback(self.refresh)
self.bbox = mforms.newBox(True)
self.bbox.set_spacing(8)
self.main.add(self.bbox, False, True)
self.bbox.add(self.row_count, False, True)
self.bbox.add_end(self.refresh_btn, False, True)
for caption, callback_name in self.actions:
if not caption:
self.bbox.add(mforms.newLabel(" "), False, True)
continue
btn = mforms.newButton()
btn.set_text(caption)
btn.add_clicked_callback(getattr(self, callback_name))
self.bbox.add(btn, False, True)
def show_error(self, title, msg):
self.main.show(False)
self.error_box.show(True)
self.error_heading.set_text(title)
self.error_body.set_text(msg)
def refresh_row_count(self):
self.row_count.set_text("Count: %d" % self.tree.count())
def on_activate(self, node, col):
from sqlide_tableman_ext import show_table_inspector
if show_table_inspector is not None:
if self.klass == 'db.Table':
show_table_inspector(
self.editor,
[(self.schema, node.get_string(
self.name_column).encode("utf8"))])
elif self.klass == 'db.Index' and hasattr(self,
'parent_name_column'):
show_table_inspector(
self.editor,
[(self.schema, node.get_string(
self.parent_name_column).encode("utf8"))], "indexes")
def menu_will_show(self, item):
# item is the parent node which will be None when the
# context menu has just being opened.
# So when the call is done for a sub-menu, no reset is needed.
if item is None:
self.menu.remove_all()
if item is None:
parent_nodes = {}
selection = grt.List()
pobj = None
for node in self.tree.get_selection():
name = node.get_string(self.name_column)
obj = grt.classes.db_query_LiveDBObject()
obj.name = name
obj.schemaName = self.schema
obj.type = self.klass
if hasattr(self, 'parent_name_column'):
parent_name = node.get_string(self.parent_name_column)
if parent_nodes.has_key(parent_name):
obj.owner = pobj
else:
pobj = grt.classes.db_query_LiveDBObject()
obj.owner = pobj
pobj.type = 'db.Table'
pobj.name = parent_name
pobj.schemaName = self.schema
parent_nodes[parent_name] = pobj
selection.append(obj)
if not selection and self.node_name:
obj = grt.classes.db_query_LiveDBObject()
obj.schemaName = self.schema
obj.type = self.node_name
selection.append(obj)
sobj = grt.classes.db_query_LiveDBObject()
sobj.schemaName = self.schema
sobj.name = self.schema
sobj.type = "db.Schema"
obj.owner = sobj
separator = mforms.newMenuItem("", mforms.SeparatorMenuItem)
separator.set_name("bottom_plugins_separator")
self.menu.add_item(separator)
self.menu.add_item_with_title("Refresh", self.refresh,
"refresh")
args = grt.Dict()
args["selection"] = selection
args["menu"] = mforms.togrt(self.menu, "ContextMenu")
args['schema_inspector'] = True
NotificationCenter().send("GRNLiveDBObjectMenuWillShow",
self.editor, args)
def get_query(self):
cols = []
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
try:
cols.append("`%s`" % field_obj['field'])
except:
cols.append("`%s`" % field_obj)
return self.show_query % {
'schema': self.schema,
'columns': ", ".join(cols)
}
def preload_data(self, query):
try:
rset = self.editor.executeManagementQuery(query, 0)
except grt.DBError, e:
if e.args[1] == 1044 or e.args[1] == 1142:
mforms.Utilities.show_error(
"Access Error",
"The current user does not have enough privileges to execute %s.\n\n%s"
% (query, e.args[0]), "OK", "", "")
else:
mforms.Utilities.show_error(
"MySQL Error",
"An error occurred retrieving information about the schema.\nQuery: %s\nError: %s"
% (query, e.args[0]), "OK", "", "")
return
ok = rset.goToFirstRow()
while ok:
if not self.filter or self.filter(rset):
node = self.tree.add_node()
if self.is_row_corrupted(rset):
print rset.stringFieldValueByName("Name"), "IS CORRUPTED"
node.set_icon_path(self.icon_column, self.bad_icon_path)
elif self.icon_column is not None:
node.set_icon_path(self.icon_column, self.icon_path)
i = 0
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
format_func = None
field = None
try:
format_func = field_obj['format_func']
field = field_obj['field']
except:
field = field_obj
if ctype is mforms.IntegerColumnType:
if type(field) is int:
node.set_int(i, rset.intFieldValue(field) or 0)
else:
node.set_int(i,
rset.intFieldValueByName(field) or 0)
elif ctype is mforms.LongIntegerColumnType:
if type(field) is int:
node.set_long(
i, long(rset.stringFieldValue(field) or 0))
else:
node.set_long(
i,
long(rset.stringFieldValueByName(field) or 0))
else:
if type(field) is int:
node.set_string(
i,
rset.stringFieldValue(field) or ""
if format_func is None else format_func(
rset.stringFieldValue(field)))
else:
node.set_string(
i,
rset.stringFieldValueByName(field) or ""
if format_func is None else format_func(
rset.stringFieldValueByName(field)))
i += 1
ok = rset.nextRow()
def preload_data(self, query):
try:
rset = self.editor.executeManagementQuery(query, 0)
except grt.DBError as e:
if e.args[1] == 1044 or e.args[1] == 1142:
mforms.Utilities.show_error(
"Access Error",
"The current user does not have enough privileges to execute %s.\n\n%s"
% (query, e.args[0]), "OK", "", "")
else:
mforms.Utilities.show_error(
"MySQL Error",
"An error occurred retrieving information about the schema.\nQuery: %s\nError: %s"
% (query, e.args[0]), "OK", "", "")
return
ok = rset.goToFirstRow()
while ok:
if not self.filter or self.filter(rset):
node = self.tree.add_node()
if self.is_row_corrupted(rset):
node.set_icon_path(self.icon_column, self.bad_icon_path)
elif self.icon_column is not None:
node.set_icon_path(self.icon_column, self.icon_path)
i = 0
for field_obj, ctype, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(
Version.fromstr(min_version)):
continue
format_func = None
field = None
try:
format_func = field_obj['format_func']
except:
pass
try:
field = field_obj['field']
except:
if self.target_version.is_supported_mysql_version_at_least(
8, 0, 0) and type(field) is str:
field = field_obj.upper()
else:
field = field_obj
if ctype is mforms.IntegerColumnType:
if type(field) is int:
node.set_int(i, rset.intFieldValue(field) or 0)
else:
node.set_int(i,
rset.intFieldValueByName(field) or 0)
elif ctype is mforms.LongIntegerColumnType:
if type(field) is int:
node.set_long(
i, int(rset.stringFieldValue(field) or 0))
else:
node.set_long(
i,
int(rset.stringFieldValueByName(field) or 0))
else:
if type(field) is int:
node.set_string(
i,
rset.stringFieldValue(field) or ""
if format_func is None else format_func(
rset.stringFieldValue(field)))
else:
node.set_string(
i,
rset.stringFieldValueByName(field) or ""
if format_func is None else format_func(
rset.stringFieldValueByName(field)))
i += 1
ok = rset.nextRow()
def _create_copy_script(self):
path = self.main.plan.state.dataBulkTransferParams[
"GenerateCopyScript"]
debug_table_copy = self.main.plan.state.dataBulkTransferParams[
"DebugTableCopy"]
truncate_target_tables = self.main.plan.state.dataBulkTransferParams[
"TruncateTargetTables"]
worker_count = self.main.plan.state.dataBulkTransferParams[
"workerCount"]
f = open(path, "w+")
if sys.platform == "win32":
def cmt(s):
return "REM " + s + "\n"
else:
os.chmod(path, 0700)
def cmt(s):
return "# " + s + "\n"
f.write("#!/bin/sh\n")
f.write(cmt("Workbench Table Data copy script"))
f.write(
cmt("Workbench Version: %s" %
Version.fromgrt(grt.root.wb.info.version)))
f.write(cmt(""))
f.write(
cmt("Execute this to copy table data from a source RDBMS to MySQL."
))
f.write(
cmt("Edit the options below to customize it. You will need to provide passwords, at least."
))
f.write(cmt(""))
f.write(
cmt("Source DB: %s (%s)" %
(self.main.plan.migrationSource.connection.hostIdentifier, self
.main.plan.migrationSource.connection.driver.owner.caption)))
f.write(
cmt("Target DB: %s" %
self.main.plan.migrationTarget.connection.hostIdentifier))
f.write("\n\n")
if sys.platform == "win32":
f.write("@ECHO OFF\n")
f.write("REM Source and target DB passwords\n")
f.write("set arg_source_password=\n")
f.write("set arg_target_password=\n")
f.write("""
IF [%arg_source_password%] == [] (
IF [%arg_target_password%] == [] (
ECHO WARNING: Both source and target RDBMSes passwords are empty. You should edit this file to set them.
)
)
""")
f.write("set arg_worker_count=%d\n" % worker_count)
f.write(
"REM Uncomment the following options according to your needs\n"
)
f.write("\n")
f.write(
"REM Whether target tables should be truncated before copy\n")
f.write(("" if truncate_target_tables else "REM ") +
"set arg_truncate_target=--truncate-target\n")
#f.write("REM Copy tables incrementally. Useful for updating table contents after an initial migration\n")
#f.write("REM set arg_incremental_copy=--incremental-copy\n")
f.write("REM Enable debugging output\n")
f.write(("" if debug_table_copy else "REM ") +
"set arg_debug_output=--log-level=debug3\n")
f.write("\n\n")
f.write(
"REM Creation of file with table definitions for copytable\n\n"
)
# Creates a temporary file name with the tables to be migrated
filename = '"%TMP%\wb_tables_to_migrate.txt"'
f.write("set table_file=%s\n" % filename)
f.write("TYPE NUL > %s\n" % filename)
for table in self._working_set.values():
fields = []
fields.append(table["source_schema"])
fields.append(table["source_table"])
fields.append(table["target_schema"])
fields.append(table["target_table"])
fields.append(table["source_primary_key"].replace("'", r"\'"))
fields.append(table["target_primary_key"].replace("'", r"\'"))
fields.append(table["select_expression"].replace("'", r"\'"))
line = "ECHO %s >> %s" % ("\t".join(fields), filename)
f.write(line + "\n")
f.write("\n\n")
f.write(self.main.plan.wbcopytables_path)
for arg in self._transferer.helper_basic_arglist(True):
f.write(' %s' % arg)
f.write(
' --source-password="******" --target-password="******" --table-file="%table_file%"'
)
f.write(
' --thread-count=%arg_worker_count% %arg_truncate_target% %arg_debug_output%'
)
f.write("\n\n")
f.write("REM Removes the file with the table definitions\n")
f.write("DEL %s\n" % filename)
else:
f.write("# Source and target DB passwords\n")
f.write("arg_source_password=\n")
f.write("arg_target_password=\n")
f.write("""
if [ -z "$arg_source_password" ] && [ -z "$arg_target_password" ] ; then
echo WARNING: Both source and target RDBMSes passwords are empty. You should edit this file to set them.
fi
""")
f.write("arg_worker_count=%d\n" % worker_count)
f.write(
"# Uncomment the following options according to your needs\n")
f.write("\n")
f.write(
"# Whether target tables should be truncated before copy\n")
f.write(("" if truncate_target_tables else "# ") +
"arg_truncate_target=--truncate-target\n")
#f.write("# Copy tables incrementally. Useful for updating table contents after an initial migration\n")
#f.write("#arg_incremental_copy=--incremental-copy\n")
f.write("# Enable debugging output\n")
f.write(("" if debug_table_copy else "# ") +
"arg_debug_output=--log-level=debug3\n")
f.write("\n")
f.write(self.main.plan.wbcopytables_path)
for arg in self._transferer.helper_basic_arglist(True):
f.write(' %s' % arg)
f.write(
' --source-password="******" --target-password="******"'
)
f.write(
' --thread-count=$arg_worker_count $arg_truncate_target $arg_debug_output'
)
for table in self._working_set.values():
opt = "--table '%s' '%s' '%s' '%s' '%s' '%s' '%s'" % (
table["source_schema"], table["source_table"],
table["target_schema"], table["target_table"],
table["source_primary_key"].replace("'", "\'"),
table["target_primary_key"].replace("'", "\'"),
table["select_expression"].replace("'", "\'"))
f.write(" " + opt)
f.write("\n\n")
f.close()
self.send_info("Table copy script written to %s" % path)
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 createScriptForCatalogObjects(path, catalog, objectCreationParams):
"""Create a CREATE script with the catalog objects. The catalog must have been previously processed
with generateSQLCreateStatements(), so that the objects have their temp_sql attributes set with
their respective SQL CREATE statements.
"""
def object_heading(type, name):
text = """
-- ----------------------------------------------------------------------------
-- %s %s
-- ----------------------------------------------------------------------------
""" % (type, name)
return text
import time
file = open(path, "w+")
file.write(
"""-- ----------------------------------------------------------------------------
-- MySQL Workbench Migration
-- Migrated Schemata: %s
-- Source Schemata: %s
-- Created: %s
-- Workbench Version: %s
-- ----------------------------------------------------------------------------
""" % (", ".join([s.name for s in catalog.schemata]), ", ".join([
s.oldName for s in catalog.schemata
]), time.ctime(), Version.fromgrt(grt.root.wb.info.version)))
preamble = catalog.customData["migration:preamble"]
if preamble and preamble.temp_sql:
#file.write(object_heading("Preamble script", ""))
file.write(preamble.temp_sql + "\n")
for schema in catalog.schemata:
file.write(object_heading("Schema", schema.name))
file.write(schema.temp_sql + ";\n")
for table in schema.tables:
file.write(
object_heading("Table", "%s.%s" % (schema.name, table.name)))
file.write(table.temp_sql + ";\n")
for view in schema.views:
file.write(
object_heading("View", "%s.%s" % (schema.name, view.name)))
file.write(view.temp_sql + ";\n")
for routine in schema.routines:
file.write(
object_heading("Routine",
"%s.%s" % (schema.name, routine.name)))
file.write(routine.temp_sql)
for table in schema.tables:
for trigger in table.triggers:
file.write(
object_heading("Trigger",
"%s.%s" % (schema.name, trigger.name)))
file.write(trigger.temp_sql + ";\n")
postamble = catalog.customData["migration:postamble"]
if postamble and postamble.temp_sql:
#file.write(object_heading("Postamble script", ""))
file.write(postamble.temp_sql + "\n")
file.close()
return 1
def __init__(self, editor, schema):
mforms.Box.__init__(self, False)
self.set_managed()
self.set_release_on_add()
self.schema = schema
self.editor = editor
self.target_version = Version.fromgrt(editor.serverVersion)
self.main = mforms.newBox(False)
self.add(self.main, True, True)
self.error_heading = mforms.newLabel("")
self.error_heading.set_style(mforms.BoldStyle)
self.error_body = mforms.newLabel("")
self.error_box = mforms.newBox(False)
self.error_box.set_spacing(8)
self.error_box.set_padding(8)
self.error_box.add(self.error_heading, True, False)
self.error_box.add(self.error_body, True, False)
self.add(self.error_box, True, False)
self.error_box.show(False)
self.main.set_padding(8)
self.main.set_spacing(8)
self.tree = mforms.newTreeView(mforms.TreeFlatList|mforms.TreeAltRowColors|mforms.TreeShowColumnLines)
self.tree.set_selection_mode(mforms.TreeSelectMultiple)
#Check if there is method to load the columns, if not, skip.
if hasattr(self, "preload_columns") and callable(getattr(self, "preload_columns")):
self.preload_columns()
for field, type, caption, width, min_version in self.columns:
if min_version and not self.target_version.is_supported_mysql_version_at_least(Version.fromstr(min_version)):
continue
self.tree.add_column(type, caption, width, False)
self.tree.end_columns()
self.tree.set_allow_sorting(True)
self.main.add(self.tree, True, True)
self.menu = mforms.newContextMenu()
self.menu.add_will_show_callback(self.menu_will_show)
self.tree.add_activated_callback(self.on_activate)
self.tree.set_context_menu(self.menu)
self.icon_path = mforms.App.get().get_resource_path(self.klass+".16x16.png")
self.bad_icon_path = mforms.App.get().get_resource_path(self.bad_icon_path)
self.row_count = mforms.newLabel("")
self.row_count.set_text("");
self.refresh_btn = mforms.newButton()
self.refresh_btn.set_text("Refresh")
self.refresh_btn.add_clicked_callback(self.refresh)
self.bbox = mforms.newBox(True)
self.bbox.set_spacing(8)
self.main.add(self.bbox, False, True)
self.bbox.add(self.row_count, False, True)
self.bbox.add_end(self.refresh_btn, False, True)
for caption, callback_name in self.actions:
if not caption:
self.bbox.add(mforms.newLabel(" "), False, True)
continue
btn = mforms.newButton()
btn.set_text(caption)
btn.add_clicked_callback(getattr(self, callback_name))
self.bbox.add(btn, False, True)
