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, 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)