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_catalog = current_database()
and t1.table_schema = current_schema()
and t1.column_default like 'nextval(%%)'
and t2.table_catalog = current_database()
and t2.table_schema = current_schema()
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_old_stored_routine_info(self):
"""
Retrieves information about all stored routines in the current schema.
"""
query = """
select t1.routine_name routine_name
, t1.routine_type routine_type
, array_to_string(array_agg(case when (parameter_name is not null) then
concat(t2.parameter_mode, ' ',
t2.parameter_name, ' ',
t2.udt_name)
end order by t2.ordinal_position asc), ',') routine_args
from information_schema.routines t1
left outer join information_schema.parameters t2 on t2.specific_catalog = t1.specific_catalog and
t2.specific_schema = t1.specific_schema and
t2.specific_name = t1.specific_name and
t2.parameter_name is not null
where routine_catalog = current_database()
and routine_schema = current_schema()
group by t1.routine_name
, t1.routine_type
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_routine_parameters_info(self):
query = """
select t2.parameter_name parameter_name
, t2.data_type parameter_type
, t2.udt_name column_type
from information_schema.routines t1
left outer join information_schema.parameters t2 on t2.specific_catalog = t1.specific_catalog and
t2.specific_schema = t1.specific_schema and
t2.specific_name = t1.specific_name and
t2.parameter_name is not null
where t1.routine_catalog = current_database()
and t1.routine_schema = current_schema()
and t1.routine_name = '%s'
order by t2.ordinal_position
""" % 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_old_stored_routine_info(self):
"""
Retrieves information about all stored routines in the current schema.
"""
query = """
select t1.routine_name routine_name
, t1.routine_type routine_type
, array_to_string(array_agg(case when (parameter_name is not null) then
concat(t2.parameter_mode, ' ',
t2.parameter_name, ' ',
t2.udt_name)
end order by t2.ordinal_position asc), ',') routine_args
from information_schema.routines t1
left outer join information_schema.parameters t2 on t2.specific_catalog = t1.specific_catalog and
t2.specific_schema = t1.specific_schema and
t2.specific_name = t1.specific_name and
t2.parameter_name is not null
where routine_catalog = current_database()
and routine_schema = current_schema()
group by t1.routine_name
, t1.routine_type
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_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_catalog = current_database()
and table_schema = current_schema()
and table_name similar to '[a-zA-Z0-9_]*'
and column_name similar to '[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 1=0 and table_catalog = current_database()
and table_name similar to '[a-zA-Z0-9_]*'
and column_name similar to '[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_routine_parameters_info(self):
query = """
select t2.parameter_name parameter_name
, t2.data_type parameter_type
, t2.udt_name column_type
from information_schema.routines t1
left outer join information_schema.parameters t2 on t2.specific_catalog = t1.specific_catalog and
t2.specific_schema = t1.specific_schema and
t2.specific_name = t1.specific_name and
t2.parameter_name is not null
where t1.routine_catalog = current_database()
and t1.routine_schema = current_schema()
and t1.routine_name = '%s'
order by t2.ordinal_position
""" % 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_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"
, udt_name "column_type"
, null "table_schema"
from information_schema.columns
where table_catalog = current_database()
and table_schema = current_schema()
union all
select table_name "table_name"
, column_name "column_name"
, udt_name "column_type"
, table_schema "table_schema"
from information_schema.columns
where table_catalog = current_database()
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']
self._replace_pairs[key] = value
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"
, udt_name "column_type"
, null "table_schema"
from information_schema.columns
where table_catalog = current_database()
and table_schema = current_schema()
union all
select table_name "table_name"
, column_name "column_name"
, udt_name "column_type"
, table_schema "table_schema"
from information_schema.columns
where table_catalog = current_database()
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']
self._replace_pairs[key] = value
