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