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 __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 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.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, 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 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 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 _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 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 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 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)
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, 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 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, 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)
