def _get_labels(self): """ Gets all primary key labels from the MySQL database. """ query_string = """ select t1.TABLE_NAME `table_name` , t1.COLUMN_NAME `id` , t2.COLUMN_NAME `label` from information_schema.COLUMNS t1 inner join information_schema.COLUMNS t2 ON t1.TABLE_NAME = t2.TABLE_NAME where t1.TABLE_SCHEMA = database() and t1.EXTRA = 'auto_increment' and t2.TABLE_SCHEMA = database() and t2.COLUMN_NAME like '%%\\_label'""" tables = StaticDataLayer.execute_rows(query_string) for table in tables: query_string = """ select `%s` as `id` , `%s` as `label` from `%s` where nullif(`%s`,'') is not null""" % (table['id'], table['label'], table['table_name'], table['label']) rows = StaticDataLayer.execute_rows(query_string) for row in rows: self._labels[row['label']] = row['id']
def _get_routine_parameters_info(self): query = """ select t2.PARAMETER_NAME parameter_name , t2.DATA_TYPE parameter_type , t2.DTD_IDENTIFIER column_type , t2.CHARACTER_SET_NAME character_set_name , t2.COLLATION_NAME collation from information_schema.ROUTINES t1 left outer join information_schema.PARAMETERS t2 on t2.SPECIFIC_SCHEMA = t1.ROUTINE_SCHEMA and t2.SPECIFIC_NAME = t1.ROUTINE_NAME and t2.PARAMETER_MODE is not null where t1.ROUTINE_SCHEMA = database() and t1.ROUTINE_NAME = '%s'""" % self._routine_name routine_parameters = StaticDataLayer.execute_rows(query) for routine_parameter in routine_parameters: if routine_parameter['parameter_name']: value = routine_parameter['column_type'] if 'character_set_name' in routine_parameter: if routine_parameter['character_set_name']: value += ' character set %s' % routine_parameter['character_set_name'] if 'collation' in routine_parameter: if routine_parameter['character_set_name']: value += ' collation %s' % routine_parameter['collation'] self._parameters.append({'name': routine_parameter['parameter_name'], 'data_type': routine_parameter['parameter_type'], 'data_type_descriptor': value})
def _get_correct_sql_mode(self): """ Gets the SQL mode in the order as preferred by MySQL. """ sql = "set sql_mode = %s" % self._sql_mode StaticDataLayer.execute_none(sql) query = "select @@sql_mode;" tmp = StaticDataLayer.execute_rows(query) self._sql_mode = tmp[0]['@@sql_mode']
def get_bulk_insert_table_columns_info(self): """ Gets the column names and column types of the current table for bulk insert. """ query = """ select 1 from information_schema.TABLES where TABLE_SCHEMA = database() and TABLE_NAME = '%s'""" % self._table_name table_is_non_temporary = StaticDataLayer.execute_rows(query) if len(table_is_non_temporary) == 0: query = 'call %s()' % self._routine_name StaticDataLayer.execute_sp_none(query) query = "describe `%s`" % self._table_name columns = StaticDataLayer.execute_rows(query) tmp_column_types = [] tmp_fields = [] n1 = 0 for column in columns: p = re.compile('(\\w+)') c_type = p.findall(column['Type']) tmp_column_types.append(c_type[0]) tmp_fields.append(column['Field']) n1 += 1 n2 = len(self._columns) if len(table_is_non_temporary) == 0: query = "drop temporary table `%s`" % self._table_name StaticDataLayer.execute_none(query) if n1 != n2: raise Exception( "Number of fields %d and number of columns %d don't match." % (n1, n2)) self._columns_types = tmp_column_types self._fields = tmp_fields
def get_bulk_insert_table_columns_info(self): """ Gets the column names and column types of the current table for bulk insert. """ query = """ select 1 from information_schema.TABLES where TABLE_SCHEMA = database() and TABLE_NAME = '%s'""" % self._table_name table_is_non_temporary = StaticDataLayer.execute_rows(query) if len(table_is_non_temporary) == 0: query = 'call %s()' % self._routine_name StaticDataLayer.execute_sp_none(query) query = "describe `%s`" % self._table_name columns = StaticDataLayer.execute_rows(query) tmp_column_types = [] tmp_fields = [] n1 = 0 for column in columns: p = re.compile('(\\w+)') c_type = p.findall(column['Type']) tmp_column_types.append(c_type[0]) tmp_fields.append(column['Field']) n1 += 1 n2 = len(self._columns) if len(table_is_non_temporary) == 0: query = "drop temporary table `%s`" % self._table_name StaticDataLayer.execute_none(query) if n1 != n2: raise Exception("Number of fields %d and number of columns %d don't match." % (n1, n2)) self._columns_types = tmp_column_types self._fields = tmp_fields
def _get_columns(self): """ Retrieves metadata all columns in the MySQL schema. """ query = """ ( select table_name , column_name , data_type , character_maximum_length , numeric_precision , ordinal_position from information_schema.COLUMNS where table_schema = database() and table_name rlike '^[a-zA-Z0-9_]*$' and column_name rlike '^[a-zA-Z0-9_]*$' order by table_name , ordinal_position ) union all ( select concat(table_schema,'.',table_name) table_name , column_name , data_type , character_maximum_length , numeric_precision , ordinal_position from information_schema.COLUMNS where table_name rlike '^[a-zA-Z0-9_]*$' and column_name rlike '^[a-zA-Z0-9_]*$' order by table_schema , table_name , ordinal_position ) """ rows = StaticDataLayer.execute_rows(query) for row in rows: # Enhance row with the actual length of the column. row['length'] = self.derive_field_length(row) if row['table_name'] in self._columns: if row['column_name'] in self._columns[row['table_name']]: pass else: self._columns[row['table_name']][row['column_name']] = row else: self._columns[row['table_name']] = {row['column_name']: row}
def _get_old_stored_routine_info(self): """ Retrieves information about all stored routines in the current schema. """ query = """ select ROUTINE_NAME routine_name , ROUTINE_TYPE routine_type , SQL_MODE sql_mode , CHARACTER_SET_CLIENT character_set_client , COLLATION_CONNECTION collation_connection from information_schema.ROUTINES where ROUTINE_SCHEMA = database() order by routine_name""" rows = StaticDataLayer.execute_rows(query) self._rdbms_old_metadata = {} for row in rows: self._rdbms_old_metadata[row['routine_name']] = row
def _get_column_type(self): """ Selects schema, table, column names and the column type from MySQL and saves them as replace pairs. """ sql = """ select TABLE_NAME table_name , COLUMN_NAME column_name , COLUMN_TYPE column_type , CHARACTER_SET_NAME character_set_name , NULL table_schema from information_schema.COLUMNS where TABLE_SCHEMA = database() union all select TABLE_NAME table_name , COLUMN_NAME column_name , COLUMN_TYPE column_type , CHARACTER_SET_NAME character_set_name , TABLE_SCHEMA table_schema from information_schema.COLUMNS order by table_schema , table_name , column_name""" rows = StaticDataLayer.execute_rows(sql) for row in rows: key = '@' if row['table_schema']: key += row['table_schema'] + '.' key += row['table_name'] + '.' + row['column_name'] + '%type@' key = key.lower() value = row['column_type'] if row['character_set_name']: value += ' character set ' + row['character_set_name'] self._replace_pairs[key] = value
def _get_routine_parameters_info(self): query = """ select t2.PARAMETER_NAME parameter_name , t2.DATA_TYPE parameter_type , t2.DTD_IDENTIFIER column_type , t2.CHARACTER_SET_NAME character_set_name , t2.COLLATION_NAME collation from information_schema.ROUTINES t1 left outer join information_schema.PARAMETERS t2 on t2.SPECIFIC_SCHEMA = t1.ROUTINE_SCHEMA and t2.SPECIFIC_NAME = t1.ROUTINE_NAME and t2.PARAMETER_MODE is not null where t1.ROUTINE_SCHEMA = database() and t1.ROUTINE_NAME = '%s'""" % self._routine_name routine_parameters = StaticDataLayer.execute_rows(query) for routine_parameter in routine_parameters: if routine_parameter['parameter_name']: value = routine_parameter['column_type'] if 'character_set_name' in routine_parameter: if routine_parameter['character_set_name']: value += ' character set %s' % routine_parameter[ 'character_set_name'] if 'collation' in routine_parameter: if routine_parameter['character_set_name']: value += ' collation %s' % routine_parameter[ 'collation'] self._parameters.append({ 'name': routine_parameter['parameter_name'], 'data_type': routine_parameter['parameter_type'], 'data_type_descriptor': value })