def __init__(self, server_instance_settings): AppView.__init__(self, False, "admin", True) server_profile = ServerProfile(server_instance_settings, False) self.ctrl_be = wb_admin_control.WbAdminControl(server_profile, connect_sql=True) self.ctrl_be.init() version = self.ctrl_be.get_server_version() if type(version) is tuple: valid_versions = ((4, 0), (4, 1), (5, 0), (5, 1), (5, 2), (5, 4), (5, 5), (5, 6), (6, 0)) if version[:2] not in valid_versions: print version, "UNSUPPORTED" log_warning( _this_file, "%s: Server version %s is NOT supported\n" % (self.__class__.__name__, str(version))) else: log_info( _this_file, "%s: Server version %s is supported\n" % (self.__class__.__name__, str(version))) self.on_close(wb_admin_utils.weakcb(self, "handle_on_close")) # Create sections and add them to the admin page. self.configuration = wb_admin_main.WbAdminMainView( server_profile, self.ctrl_be, self.monitor) self.add(self.configuration, True, True)
def transform_table_identifiers(sql, callable): from grt.modules import MysqlSqlFacade ast_list = MysqlSqlFacade.parseAstFromSqlScript(sql) # Do some validation first for ast in ast_list: if type(ast) is str: log_info("copyAsDrupalQuery", ast) # error mforms.App.get().set_status_text("Cannot format invalid SQL: %s" % ast) return 1 #from sql_reformatter import dump_tree #import sys #myFile = open("C:\\temp\\log.txt", "w") #dump_tree(myFile, ast) #myFile.close() offsets = [] for ast in ast_list: get_table_ident_offsets(offsets, ast) sql2 = "" bb = 0 for b, e in offsets: sql2 += sql[bb:b] + callable(sql[b:e]) bb = e sql2 += sql[bb:] return sql2
def _cell_edited(self, node, column, value): if column == 1: oid = node.get_tag() if oid: object = self._object_dict.get(oid, None) if object: if isinstance(object, grt.classes.db_Column): grt.log_info("Migration", "User changed target column definition from '%s' to '%s'\n"%(node.get_string(column), value)) name, sep, type = value.partition(" ") object.name = name try: if not object.setParseType(type, None): raise Exception("Could not parse column type string '%s'" % type) except Exception, exc: mforms.Utilities.show_error("Change Column Type", "Error changing column type: %s" % exc, "OK", "", "") return node.set_string(1, name+" "+object.formattedRawType) self._regenerateSQL() elif not isinstance(object, grt.classes.db_View) and not isinstance(object, grt.classes.db_Routine) and not isinstance(object, grt.classes.db_Trigger): grt.log_info("Migration", "User renamed target object '%s' to '%s'\n"%(object.name, value)) node.set_string(1, value) object.name = value self._regenerateSQL() else: mforms.Utilities.show_message("Rename Object", "The object cannot be renamed from the tree.", "OK", "", "")
def run(self): self.set_running() grt.log_info("Wizard", "Execute '%s'\n" % self.label) self.owner.send_info("%s..." % self.label) try: self.func() except grt.UserInterrupt: self.owner.send_info("%s cancelled" % self.label) self.set_aborted() return except Exception as e: if not isinstance(self, WizardThreadedTask): print() import traceback traceback.print_exc() msg = "Error during %s: %s" % (self.label, self.owner.format_exception_text(e)) self.owner.send_error(msg) #grt.log_error("Wizard", msg) self.set_failed(msg) raise e self.owner.send_info("%s done" % self.label) #grt.log_info("Wizard", "%s done\n" % self.label) self.set_finished()
def transform_table_identifiers(sql,callable): from grt.modules import MysqlSqlFacade ast_list = MysqlSqlFacade.parseAstFromSqlScript(sql) # Do some validation first for ast in ast_list: if type(ast) is str: log_info("copyAsDrupalQuery",ast) # error mforms.App.get().set_status_text("Cannot format invalid SQL: %s" % ast) return 1 #from sql_reformatter import dump_tree #import sys #myFile = open("C:\\temp\\log.txt", "w") #dump_tree(myFile, ast) #myFile.close() offsets = [] for ast in ast_list: get_table_ident_offsets(offsets, ast) sql2 = "" bb = 0 for b, e in offsets: sql2 += sql[bb:b] + callable(sql[b:e]) bb = e sql2 += sql[bb:] return sql2
def __init__(self, server_instance_settings): AppView.__init__(self, False, "admin", True) server_profile = ServerProfile(server_instance_settings, False) self.ctrl_be = wb_admin_control.WbAdminControl(server_profile, connect_sql=True) self.ctrl_be.init() version = self.ctrl_be.get_server_version() if type(version) is tuple: valid_versions = ((4, 0), (4, 1), (5, 0), (5, 1), (5, 2), (5, 4), (5, 5), (5, 6), (6, 0)) if version[:2] not in valid_versions: print version, "UNSUPPORTED" log_warning( _this_file, "%s: Server version %s is NOT supported\n" % (self.__class__.__name__, str(version)) ) else: log_info(_this_file, "%s: Server version %s is supported\n" % (self.__class__.__name__, str(version))) self.on_close(wb_admin_utils.weakcb(self, "handle_on_close")) # Create sections and add them to the admin page. self.configuration = wb_admin_main.WbAdminMainView(server_profile, self.ctrl_be, self.monitor) self.add(self.configuration, True, True)
def save_file_content_and_backup(self, path, content, backup_extension, as_admin = False, admin_password = None): # Check if dir, where config file will be stored is writable dirname, filename = splitpath(path) if not as_admin and not self.is_dir_writable(dirname.strip(" \r\t\n")): raise PermissionDeniedError("Cannot write to directory %s" % dirname) if self.ssh is not None: ## Get temp dir for using as tmpdir tmpdir, status = self.process_ops.get_cmd_output("echo %temp%") if type(tmpdir) is unicode: tmpdir = tmpdir.encode("utf8") if type(tmpdir) is str: tmpdir = tmpdir.strip(" \r\t\n") if tmpdir[1] == ":": tmpdir = tmpdir[2:] else: log_debug(_this_file, '%s: Temp directory path "%s" is not in expected form. The expected form is something like "C:\\Windows\\Temp"\n' % (self.__class__.__name__, tmpdir) ) tmpdir = None log_debug2(_this_file, '%s: Got temp dir: "%s"\n' % (self.__class__.__name__, tmpdir) ) else: tmpdir = None if not tmpdir: tmpdir = dirname tmpfilename = tmpdir + r"\workbench-temp-file.ini" log_debug(_this_file, '%s: Remotely writing contents to temporary file "%s"\n' % (self.__class__.__name__, tmpfilename) ) log_debug3(_this_file, '%s: %s\n' % (self.__class__.__name__, content) ) self.ssh.set_contents(tmpfilename, content) if backup_extension: log_debug(_this_file, '%s: Backing up "%s"\n' % (self.__class__.__name__, path) ) backup_cmd = "copy /y " + quote_path_win(path) + " " + quote_path_win(path+backup_extension) msg, code = self.process_ops.get_cmd_output(backup_cmd) if code != 0: print backup_cmd, "->", msg log_error(_this_file, '%s: Error backing up file: %s\n' % (self.__class__.__name__, backup_cmd+'->'+msg) ) raise RuntimeError("Error backing up file: %s" % msg) copy_to_dest = "copy /y " + quote_path_win(tmpfilename) + " " + quote_path_win(path) delete_tmp = "del " + quote_path_win(tmpfilename) log_debug(_this_file, '%s: Copying file to final destination: "%s"\n' % (self.__class__.__name__, copy_to_dest) ) msg, code = self.process_ops.get_cmd_output(copy_to_dest) if code != 0: print copy_to_dest, "->", msg log_error(_this_file, '%s: Error copying temporary file over destination file: %s\n%s to %s\n' % (self.__class__.__name__, msg, tmpfilename, path) ) raise RuntimeError("Error copying temporary file over destination file: %s\n%s to %s" % (msg, tmpfilename, path)) log_debug(_this_file, '%s: Deleting tmp file: "%s"\n' % (self.__class__.__name__, delete_tmp) ) msg, code = self.process_ops.get_cmd_output(delete_tmp) if code != 0: print "Could not delete temporary file %s: %s" % (tmpfilename, msg) log_info(_this_file, '%s: Could not delete temporary file "%s": %s\n' % (self.__class__.__name__, tmpfilename, msg) ) else: raise Exception("No SSH session active, cannot save file remotely")
def scan_admin_modules(): # initialize the main WBA modules modules = [WbAdminConfigurationStartup, WbAdminLogs, WbAdminConfigFileUI, WbAdminServerStatus, WbAdminConnections, WbAdminSecurity, WbAdminVariables, WbAdminExport, WbAdminImport, WbAdminDashboard, WbAdminPerformanceSchema, WbAdminPerformanceSchemaInstrumentation] # look for extension modules #--------------------------------------------------------------------------- grt.log_info("WBA", "Looking for extension modules for WBA...\n") init_count = 0 # search in the same dir where the WBA code itself is located extra_mods = {} for location in [os.path.dirname(__file__)]: try: folders = [f for f in os.listdir(location) if f.startswith("wba_") and os.path.isdir(os.path.join(location, f))] except: continue sys.path.append(location) for candidate in folders: if os.path.exists(os.path.join(location, candidate, "__init__.py")): mod = __import__(candidate) if hasattr(mod, "wba_register"): extra_mods[candidate] = mod init_count+= 1 else: # unload the module del sys.modules[mod.__name__] del mod sys.path.pop() if len(extra_mods) != 0: import collections od = collections.OrderedDict(sorted(extra_mods.items())) for mod in od.values(): modules.append(mod) grt.log_info("WBA", "%i extension modules found\n" % init_count) return modules
def _check_server_version(self): version = self.ctrl_be.get_server_version() if type(version) is tuple: valid_versions = ((4,0), (4,1), (5,0), (5,1), (5,2), (5,4), (5,5), (5,6), (5, 7)) if version[:2] not in valid_versions: log_warning(_this_file, "%s: Server version %s is NOT supported\n" % (self.__class__.__name__, str(version)) ) Utilities.show_error("Unsupported Server Version", "The version of the server you're trying to connect to is %i.%i, which is not supported by Workbench."%version[:2], "Close", "Ignore", "") return False else: log_info(_this_file, "%s: Server version %s is supported\n" % (self.__class__.__name__, str(version)) ) return True return None
def connect(cls, connection, password): r = super(PostgresqlReverseEngineering, cls).connect(connection, password) if r: ver = cls.execute_query(connection, "select version()").fetchone()[0] grt.log_info("PostgreSQL RE", "Connected to %s, %s\n" % (connection.name, ver)) ver_parts = [int(n) for n in ver.split()[1].rstrip(",").split(".")] + 4*[0] version = grt.classes.GrtVersion() version.majorNumber, version.minorNumber, version.releaseNumber, version.buildNumber = ver_parts[:4] cls._connections[connection.__id__]["version"] = version if version.majorNumber < 8: raise RuntimeError("PostgreSQL version %s is not a supported migration source.\nAt least version 8 is required." % ver) return r
def _check_instance_profile(self): self.server_profile = ServerProfile(self.connection, self.instance_profile, False) # if no instance info exists, try to auto-detect them if self.instance_profile is None: if self.server_profile.is_local: if autoDetectLocalInstance(self.connection): grt.log_info("Admin", "Auto-created instance profile for connection %s\n" % self.connection.name) # Retry self.server_profile = ServerProfile(self.connection, self.instance_profile, False) else: if autoDetectRemoteInstance(self.connection): grt.log_info("Admin", "Auto-created dummy instance profile for remote connection %s\n" % self.connection.name) # Retry self.server_profile = ServerProfile(self.connection, self.instance_profile, False)
def connect(cls, connection, password): '''Establishes a connection to the server and stores the connection object in the connections pool. It first looks for a connection with the given connection parameters in the connections pool to reuse existent connections. If such connection is found it queries the server to ensure that the connection is alive and reestablishes it if is dead. If no suitable connection is found in the connections pool, a new one is created and stored in the pool. Parameters: =========== connection: an object of the class db_mgmt_Connection storing the parameters for the connection. password: a string with the password to use for the connection (ignored for SQLite). ''' con = None try: con = cls.get_connection(connection) try: if not con.cursor().execute('SELECT 1'): raise Exception('connection error') except Exception as exc: grt.send_info( 'Connection to %s apparently lost, reconnecting...' % connection.hostIdentifier) raise NotConnectedError('Connection error') except NotConnectedError as exc: grt.send_info('Connecting to %s...' % connection.hostIdentifier) con = sqlite3.connect(connection.parameterValues['dbfile']) if not con: grt.send_error('Connection failed', str(exc)) raise connection.parameterValues[ 'wbcopytables_connection_string'] = "'" + connection.parameterValues[ 'dbfile'] + "'" grt.send_info('Connected') cls._connections[connection.__id__] = {'connection': con} if con: ver = cls.execute_query(connection, "SELECT sqlite_version()").fetchone()[0] grt.log_info('SQLite RE', 'Connected to %s, %s' % (connection.name, ver)) ver_parts = server_version_str2tuple(ver) + (0, 0, 0, 0) version = grt.classes.GrtVersion() version.majorNumber, version.minorNumber, version.releaseNumber, version.buildNumber = ver_parts[: 4] cls._connections[connection.__id__]['version'] = version return 1
def load_data(self, connection): m = grt.modules.DbMySQLRE grt.log_info("sqlide_grt", "Connecting...") ok, password = mforms.Utilities.find_or_ask_for_password("Connect to MySQL", connection.hostIdentifier, "root", False) if not ok: return None m.connect(connection, password) grt.log_info("sqlide_grt", "Reverse engineering schema %s..." % self.schema_name) options = {"reverseEngineerTables" : True, "reverseEngineerTriggers" : False, "reverseEngineerViews" : False, "reverseEngineerRoutines" : False} self.catalog = m.reverseEngineer(connection, "", [self.schema_name], options) m.disconnect(connection) return self.catalog
def scan_admin_modules(): # initialize the main WBA modules modules = [WbAdminConfigurationStartup, WbAdminLogs, WbAdminConfigFileUI, WbAdminServerStatus, WbAdminConnections, WbAdminSecurity, WbAdminVariables, WbAdminExport, WbAdminImport] # look for extension modules #--------------------------------------------------------------------------- log_info("WBA", "Looking for extension modules for WBA...\n") init_count = 0 # search in the same dir where the WBA code itself is located for location in [os.path.dirname(__file__)]: try: folders = [f for f in os.listdir(location) if f.startswith("wba_") and os.path.isdir(os.path.join(location, f))] except: continue sys.path.append(location) for candidate in folders: if os.path.exists(os.path.join(location, candidate, "__init__.py")): mod = __import__(candidate) if hasattr(mod, "wba_register"): modules.append(mod) init_count+= 1 else: # unload the module del sys.modules[mod.__name__] del mod sys.path.pop() log_info("WBA", "%i extension modules found\n" % init_count) return modules
def run(self): self.set_running() grt.log_info("Wizard", "Execute '%s'\n" % self.label) self.owner.send_info("%s..." % self.label) try: self.func() except grt.UserInterrupt: self.owner.send_info("%s cancelled" % self.label) self.set_aborted() return except Exception, e: if not isinstance(self, WizardThreadedTask): print import traceback traceback.print_exc() msg = "Error during %s: %s" % (self.label, self.owner.format_exception_text(e)) self.owner.send_error(msg) #grt.log_error("Wizard", msg) self.set_failed(msg) raise e
def load_data(self, connection): m = grt.modules.DbMySQLRE grt.log_info("sqlide_grt", "Connecting...") ok, password = mforms.Utilities.find_or_ask_for_password( "Connect to MySQL", connection.hostIdentifier, "root", False) if not ok: return None m.connect(connection, password) grt.log_info("sqlide_grt", "Reverse engineering schema %s...\n" % self.schema_name) options = { "reverseEngineerTables": True, "reverseEngineerTriggers": False, "reverseEngineerViews": False, "reverseEngineerRoutines": False } self.catalog = m.reverseEngineer(connection, "", [self.schema_name], options) m.disconnect(connection) grt.log_info( "sqlide_grt", "Reverse engineer schema %s finished\n" % self.schema_name) return self.catalog
def exec_cmd(self, command, as_admin=False, admin_password=None, output_handler=None): #if not self.ssh: # raise Exception("No SSH session active") if as_admin: command = wrap_for_sudo(command, self.sudo_prefix) def ssh_output_handler(ssh_channel, handler): import socket loop = True while loop: try: chunk = ssh_channel.recv(128) if chunk is not None and chunk != "": handler(chunk) else: loop = False except socket.timeout: loop = False if output_handler: handler = lambda chan, h=output_handler: ssh_output_handler(chan, h) else: handler = None if self.ssh: # output_handler taken by ssh.exec_cmd is different from the one used elsewhere dummy_text, ret = self.ssh.exec_cmd(command, as_admin=as_admin, admin_password=admin_password, output_handler=handler) else: ret = 1 if output_handler: output_handler("No SSH connection is active") else: print("No SSH connection is active") log_info(_this_file, "No SSH connection is active") return ret
def init_extensions(self, server_profile, ctrl_be): log_info("WBA", "Initializing extension modules for WBA...\n") init_count = 0 # search in the same dir where the WBA code itself is located for location in [os.path.dirname(__file__)]: try: folders = [ f for f in os.listdir(location) if f.startswith("wba_") and os.path.isdir(os.path.join(location, f)) ] except: continue sys.path.append(location) for candidate in folders: if os.path.exists( os.path.join(location, candidate, "__init__.py")): mod = __import__(candidate) if hasattr(mod, "wba_register"): log_info( "WBA", "Registering WBA extension module %s/%s...\n" % (location, candidate)) try: mod.wba_register(server_profile, ctrl_be, self) init_count += 1 log_info( "WBA", "WBA extension module %s/%s registered OK\n" % (location, candidate)) except: import traceback traceback.print_exc() log_error( "WBA", "Exception caught while loading WBA extension module %s/%s\n" % (location, candidate)) del sys.modules[mod.__name__] del mod else: # unload the module del sys.modules[mod.__name__] del mod sys.path.pop() log_info("WBA", "%i extension modules initialized\n" % init_count)
def testInstanceSettingByName(what, connection, server_instance): global test_ssh_connection log_debug(_this_file, "Test %s in %s\n" % (what, connection.name)) profile = ServerProfile(connection, server_instance) if what == "connect_to_host": if test_ssh_connection: test_ssh_connection = None log_info(_this_file, "Instance test: Connecting to %s\n" % profile.ssh_hostname) try: test_ssh_connection = wb_admin_control.WbAdminControl(profile, connect_sql=False) test_ssh_connection.init() grt.send_info("connected.") except Exception, exc: import traceback traceback.print_exc() return "ERROR "+str(exc) except:
def _columns_cell_edited(self, node, column, value): object = self._object_dict.get(node.get_tag(), None) if object and isinstance(object, grt.classes.db_Column): if object.owner.customData.get("migration:lock_temp_sql", False): if mforms.Utilities.show_message("Object is Locked", "The object was manually edited and is locked against updates. Would you like to unlock the object discard your edits to apply this change?", "Unlock Object", "Cancel", "") == mforms.ResultCancel: return grt.log_info("Migration", "User unlocked object '%s' by changing columns tree" % object.name) object.owner.customData["migration:lock_temp_sql"] = False if column == self.COL_TARGET_COLUMN: object.name = value node.set_string(column, value) grt.log_info("Migration", "User renamed target column '%s' to '%s'\n"%(object.name, value)) self._regenerateSQL() elif column == self.COL_TARGET_TYPE: try: if not object.setParseType(value, None): raise Exception("Could not parse column type string '%s'" % value) except Exception, exc: mforms.Utilities.show_error("Change Column Type", "Error changing column type: %s" % exc, "OK", "", "") return node.set_string(column, value) grt.log_info("Migration", "User changed target column type of '%s' to '%s'\n"%(object.name, value)) self._regenerateSQL() elif column == self.COL_TARGET_FLAGS: node.set_string(column, value) object.flags.remove_all() object.flags.extend(value.split()) grt.log_info("Migration", "User changed target column flags of '%s' to '%s'\n"%(object.name, value)) self._regenerateSQL() elif column == self.COL_TARGET_AI: node.set_bool(column, int(value) != 0) object.autoIncrement = (int(value) != 0) grt.log_info("Migration", "User changed target column autoIncrement of '%s' to '%s'\n"%(object.name, value)) self._regenerateSQL() elif column == self.COL_TARGET_NOTNULL: node.set_bool(column, int(value) != 0) object.isNotNull = (int(value) != 0) grt.log_info("Migration", "User changed target column isNotNull of '%s' to '%s'\n"%(object.name, value)) self._regenerateSQL() elif column == self.COL_TARGET_DEFAULT: node.set_string(column, value) object.defaultValue = value object.defaultValueIsNull = value == "NULL" grt.log_info("Migration", "User changed target column default of '%s' to '%s'\n"%(object.name, value)) self._regenerateSQL() elif column == self.COL_TARGET_COLLATION: node.set_string(column, value) object.collationName = value grt.log_info("Migration", "User changed target column collation of '%s' to '%s'\n"%(object.name, value)) self._regenerateSQL()
def run(self): self.set_running() self.owner.send_info("%s...." % self.label) grt.log_info("Wizard", "Starting thread for '%s'\n" % self.label) self._thread = self.TaskThread(self) self._thread.start()
params['password'] = password conn_params = dict(params) conn_params['password'] = '******' connection.parameterValues['wbcopytables_connection_string'] = repr(conn_params) con = sqlanydb.connect(**params) else: con = db_driver.connect(connection, password) if not con: grt.send_error('Connection failed', str(exc)) raise grt.send_info('Connected') cls._connections[connection.__id__] = {'connection': con} if con: ver = cls.execute_query(connection, "SELECT @@version").fetchone()[0] grt.log_info("SQLAnywhere RE", "Connected to %s, %s\n" % (connection.name, ver)) ver_parts = server_version_str2tuple(ver) + (0, 0, 0, 0) version = grt.classes.GrtVersion() version.majorNumber, version.minorNumber, version.releaseNumber, version.buildNumber = ver_parts[:4] cls._connections[connection.__id__]["version"] = version return 1 @classmethod @release_cursors def getCatalogNames(cls, connection): """Returns a list of the available catalogs. [NOTE] This will in fact return the name of the database we are connected to. """ return [cls.execute_query(connection, "SELECT DB_PROPERTY('Name')").fetchone()[0]]
% version[:2], "Close", "Ignore", "", ) app.set_status_text("Could not Open WB Admin") return None app.dock_view(adminTab, "maintab") app.set_view_title(adminTab, "Admin (%s)" % (server_instance.name)) tab_references.append(adminTab) app.set_status_text("WB Admin Opened") log_info(_this_file, "do_open_administrator(): Completed\n") return adminTab # ------------------------------------------------------------------------------- def validate_setting(settings, option, norm_cb, msg): if settings.has_key(option): if norm_cb is not None: norm_cb(settings, option) else: if msg is not None: Utilities.show_warning("WB Administartor", msg, "OK", "", "") norm_cb(settings, option) # -------------------------------------------------------------------------------
return "ERROR "+str(exc) except: print "Unknown error" return "ERROR" try: test_ssh_connection.acquire_admin_access() except Exception, exc: import traceback traceback.print_exc() return "ERROR "+str(exc) os_info = test_ssh_connection.detect_operating_system_version() if os_info: os_type, os_name, os_variant, os_version = os_info log_info(_this_file, "Instance test: detected remote OS: %s (%s), %s, %s\n" % (os_info)) # check if the admin access error was because of wrong OS set if os_type != profile.target_os: return "ERROR Wrong Remote OS configured for connection. Set to %s, but was detected as %s" % (profile.target_os, os_type) else: log_warning(_this_file, "Instance test: could not determine OS version information\n") return "ERROR Could not determine remote OS details" return "OK" elif what == "disconnect": if test_ssh_connection: test_ssh_connection = None return "OK"
def log_info(msg): tb = traceback.extract_stack(limit=2) grt.log_info("%s:%s:%s"%(os.path.basename(tb[-2][0]),tb[-2][2],tb[-2][1]), msg)
if version and version[0] < 5: Utilities.show_error( "Unsupported Server Version", "The version of the server you're trying to connect to is %i.%i, which is not supported by Workbench." % version[:2], "Close", "Ignore", "") app.set_status_text("Could not Open WB Admin") return None app.dock_view(adminTab, "maintab") app.set_view_title(adminTab, "Admin (%s)" % (server_instance.name)) tab_references.append(adminTab) app.set_status_text("WB Admin Opened") log_info(_this_file, "do_open_administrator(): Completed\n") return adminTab #------------------------------------------------------------------------------- def validate_setting(settings, option, norm_cb, msg): if settings.has_key(option): if norm_cb is not None: norm_cb(settings, option) else: if msg is not None: Utilities.show_warning("WB Administartor", msg, "OK", "", "") norm_cb(settings, option) #-------------------------------------------------------------------------------
def connect(cls, connection, password): '''Establishes a connection to the server and stores the connection object in the connections pool. It first looks for a connection with the given connection parameters in the connections pool to reuse existent connections. If such connection is found it queries the server to ensure that the connection is alive and reestablishes it if is dead. If no suitable connection is found in the connections pool, a new one is created and stored in the pool. Parameters: =========== connection: an object of the class db_mgmt_Connection storing the parameters for the connection. password: a string with the password to use for the connection (ignored for SQLite). ''' con = None try: con = cls.get_connection(connection) try: if not con.cursor().execute('SELECT 1'): raise Exception('connection error') except Exception as exc: grt.send_info( 'Connection to %s apparently lost, reconnecting...' % connection.hostIdentifier) raise NotConnectedError('Connection error') except NotConnectedError as exc: grt.send_info('Connecting to %s...' % connection.hostIdentifier) if connection.driver.driverLibraryName == 'sqlanydb': import sqlanydbwrapper as sqlanydb # Replace this to a direct sqlanydb import when it complies with PEP 249 connstr = replace_string_parameters( connection.driver.connectionStringTemplate, dict(connection.parameterValues)) import ast try: all_params_dict = ast.literal_eval(connstr) except Exception as exc: grt.send_error( 'The given connection string is not a valid python dict: %s' % connstr) raise # Remove unreplaced parameters: params = dict( (key, value) for key, value in list(all_params_dict.items()) if not (value.startswith('%') and value.endswith('%'))) params['password'] = password conn_params = dict(params) conn_params['password'] = '******' connection.parameterValues[ 'wbcopytables_connection_string'] = repr(conn_params) con = sqlanydb.connect(**params) else: con = db_driver.connect(connection, password) if not con: grt.send_error('Connection failed', str(exc)) raise grt.send_info('Connected') cls._connections[connection.__id__] = {'connection': con} if con: ver = cls.execute_query(connection, "SELECT @@version").fetchone()[0] grt.log_info("SQLAnywhere RE", "Connected to %s, %s\n" % (connection.name, ver)) ver_parts = server_version_str2tuple(ver) + (0, 0, 0, 0) version = grt.classes.GrtVersion() version.majorNumber, version.minorNumber, version.releaseNumber, version.buildNumber = ver_parts[: 4] cls._connections[connection.__id__]["version"] = version return 1
def _columns_cell_edited(self, node, column, value): object = self._object_dict.get(node.get_tag(), None) if object and isinstance(object, grt.classes.db_Column): if object.owner.customData.get("migration:lock_temp_sql", False): if mforms.Utilities.show_message( "Object is Locked", "The object was manually edited and is locked against updates. Would you like to unlock the object discard your edits to apply this change?", "Unlock Object", "Cancel", "") == mforms.ResultCancel: return grt.log_info( "Migration", "User unlocked object '%s' by changing columns tree" % object.name) object.owner.customData["migration:lock_temp_sql"] = False if column == self.COL_TARGET_COLUMN: object.name = value node.set_string(column, value) grt.log_info( "Migration", "User renamed target column '%s' to '%s'\n" % (object.name, value)) self._regenerateSQL() elif column == self.COL_TARGET_TYPE: try: if not object.setParseType(value, None): raise Exception( "Could not parse column type string '%s'" % value) except Exception, exc: mforms.Utilities.show_error( "Change Column Type", "Error changing column type: %s" % exc, "OK", "", "") return node.set_string(column, value) grt.log_info( "Migration", "User changed target column type of '%s' to '%s'\n" % (object.name, value)) self._regenerateSQL() elif column == self.COL_TARGET_FLAGS: node.set_string(column, value) object.flags.remove_all() object.flags.extend(value.split()) grt.log_info( "Migration", "User changed target column flags of '%s' to '%s'\n" % (object.name, value)) self._regenerateSQL() elif column == self.COL_TARGET_AI: node.set_bool(column, int(value) != 0) object.autoIncrement = (int(value) != 0) grt.log_info( "Migration", "User changed target column autoIncrement of '%s' to '%s'\n" % (object.name, value)) self._regenerateSQL() elif column == self.COL_TARGET_NOTNULL: node.set_bool(column, int(value) != 0) object.isNotNull = (int(value) != 0) grt.log_info( "Migration", "User changed target column isNotNull of '%s' to '%s'\n" % (object.name, value)) self._regenerateSQL() elif column == self.COL_TARGET_DEFAULT: node.set_string(column, value) object.defaultValue = value object.defaultValueIsNull = value == "NULL" grt.log_info( "Migration", "User changed target column default of '%s' to '%s'\n" % (object.name, value)) self._regenerateSQL() elif column == self.COL_TARGET_COLLATION: node.set_string(column, value) object.collationName = value grt.log_info( "Migration", "User changed target column collation of '%s' to '%s'\n" % (object.name, value)) self._regenerateSQL()
def buildCascadedDeleteStatement(editor, object): catalog = editor.customData.get("sqlide_grt:Catalog:%s" % object.schemaName, None) if not catalog: mforms.App.get().set_status_text("Reverse engineering schema for %s.%s..." % (object.schemaName, object.name)) m = grt.modules.DbMySQLRE grt.log_info("sqlide_grt", "Connecting...") ok, password = mforms.Utilities.find_or_ask_for_password("Connect to MySQL", editor.connection.hostIdentifier, "root", False) if not ok: return m.connect(editor.connection, password) grt.log_info("sqlide_grt", "Reverse engineering schema %s..." % object.schemaName) options = {"reverseEngineerTables" : True, "reverseEngineerTriggers" : False, "reverseEngineerViews" : False, "reverseEngineerRoutines" : False} catalog = m.reverseEngineer(editor.connection, "", [object.schemaName], options) editor.customData["sqlide_grt:Catalog:%s" % object.schemaName] = catalog m.disconnect(editor.connection) else: mforms.App.get().set_status_text("Reusing reverse engineered schema for %s.%s..." % (object.schemaName, object.name)) def _get_references_to(catalog, table, visited): if table in visited: return [] visited.append(table) deps = [] for s in catalog.schemata: for t in s.tables: for f in t.foreignKeys: if f.referencedTable == table: if f not in deps: deps.append(f) deps += _get_references_to(catalog, t, visited) return deps # find the table the_table = None for schema in catalog.schemata: if schema.name == object.schemaName: for table in schema.tables: if table.name == object.name: the_table = table break else: break if not the_table: mforms.Utilities.show_error("Cascaded DELETE", "Could not find reverse engineered %s" % object.name, "OK", "", "") return 0 # find all dependencies deps = _get_references_to(catalog, the_table, []) joins = [] tables = [] table_references = [] the_table_alias = the_table.name a = 1 for fk in deps: ralias = "%s%s" % (fk.referencedTable.name[0], a) talias = "%s%s" % (fk.owner.name[0], a+1) a += 2 if ralias not in tables: tables.append(ralias) if talias not in tables: tables.append(talias) if the_table == fk.referencedTable: the_table_alias = ralias ijoin = "%s %s INNER JOIN %s %s" % (fk.referencedTable.name, ralias, fk.owner.name, talias) if ijoin not in table_references: table_references.append(ijoin) subjoins = [] for c in range(len(fk.referencedColumns)): subjoins.append("%s.%s = %s.%s" % (ralias, fk.referencedColumns[c].name, talias, fk.columns[c].name)) joins.append(" AND ".join(subjoins)) if deps: pk = [] for c in the_table.primaryKey.columns: pk.append("%s.%s = <{row id}>" % (the_table_alias, c.referencedColumn.name)) query = "DELETE\n FROM %s\n USING %s\n WHERE %s\n AND %s" % (", ".join(tables), ",\n ".join(table_references), "\n AND ".join(joins), " AND ".join(pk)) else: pk = [] for c in the_table.primaryKey.columns: pk.append("%s = <{row id}>" % c.referencedColumn.name) query = "DELETE FROM %s\n WHERE %s" % (the_table.name, " AND ".join(pk)) mforms.Utilities.set_clipboard_text(query) mforms.App.get().set_status_text("DELETE statement for %s was copied to clipboard" % the_table.name) return 0