def process_constraints(conversion):
"""
Continues migration process after data loading.
:param conversion: Conversion
:return: None
"""
are_table_constraints_loaded = MigrationStateManager.get(
conversion, 'per_table_constraints_loaded')
if not are_table_constraints_loaded:
params = [[conversion, table_name]
for table_name in conversion.tables_to_migrate]
ConcurrencyManager.run_in_parallel(
conversion, ConstraintsProcessor.process_constraints_per_table,
params)
if conversion.should_migrate_only_data():
MigrationStateManager.set(conversion,
'per_table_constraints_loaded',
'foreign_keys_loaded', 'views_loaded')
else:
MigrationStateManager.set(conversion,
'per_table_constraints_loaded')
ForeignKeyProcessor.set_foreign_keys(conversion)
MigrationStateManager.set(conversion, 'foreign_keys_loaded')
ViewGenerator.generate_views(conversion)
MigrationStateManager.set(conversion, 'views_loaded')
# !!!Note, dropping of data - pool and state - logs tables MUST be the last step of migration process.
MigrationStateManager.drop_data_pool_table(conversion)
MigrationStateManager.drop_state_logs_table(conversion)
def decode(conversion):
"""
Decodes binary data from from textual representation.
:param conversion: Conversion
:return: None
"""
log_title = 'BinaryDataDecoder::decode'
FsOps.log(conversion, '\t--[%s] Decoding binary data from textual representation.' % log_title)
sql = '''
SELECT table_name, column_name FROM information_schema.columns
WHERE table_catalog = \'%s\' AND table_schema = \'%s\' AND data_type IN (\'bytea\', \'geometry\');
''' % (conversion.target_con_string['database'], conversion.schema)
result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql=sql,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False
)
if result.error:
# No need to continue if no 'bytea' or 'geometry' columns found.
return
params = [[conversion, record['table_name'], record['column_name']] for record in result.data]
ConcurrencyManager.run_in_parallel(conversion, BinaryDataDecoder._decode, params)
def create_indexes(conversion, table_name):
"""
Creates indexes, including PK, on given table.
:param conversion: Conversion
:param table_name: str
:return: None
"""
log_title = 'IndexesProcessor::create_indexes'
original_table_name = ExtraConfigProcessor.get_table_name(conversion, table_name, should_get_original=True)
show_index_result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql='SHOW INDEX FROM `%s`;' % original_table_name,
vendor=DBVendors.MYSQL,
process_exit_on_error=False,
should_return_client=False
)
if show_index_result.error:
return
pg_indexes = {}
for index in show_index_result.data:
pg_column_name = ExtraConfigProcessor.get_column_name(
conversion=conversion,
original_table_name=original_table_name,
current_column_name=index['Column_name'],
should_get_original=False
)
if index['Key_name'] in pg_indexes:
pg_indexes[index['Key_name']]['column_name'].append('"%s"' % pg_column_name)
continue
pg_index_type = 'GIST' if index['Index_type'] == 'SPATIAL' else index['Index_type']
pg_indexes[index['Key_name']] = {
'is_unique': index['Non_unique'] == 0,
'column_name': ['"%s"' % pg_column_name],
'index_type': ' USING %s' % pg_index_type,
}
params = [
[conversion, index_name, table_name, pg_indexes, idx]
for idx, index_name in enumerate(pg_indexes.keys())
]
ConcurrencyManager.run_in_parallel(conversion, IndexesProcessor._set_index, params)
msg = '\t--[%s] "%s"."%s": PK/indices are successfully set...' % (log_title, conversion.schema, table_name)
FsOps.log(conversion, msg, conversion.dic_tables[table_name].table_log_path)
def _process_columns_comments(conversion, table_name):
"""
Creates columns comments.
:param conversion: Conversion
:param table_name: str
:return: None
"""
original_table_name = ExtraConfigProcessor.get_table_name(conversion, table_name, should_get_original=True)
params = [
[conversion, table_name, original_table_name, column]
for column in conversion.dic_tables[table_name].table_columns
if column['Comment'] != ''
]
ConcurrencyManager.run_in_parallel(conversion, CommentsProcessor._set_column_comment, params)
def set_foreign_keys(conversion):
"""
Starts a process of foreign keys migration.
:param conversion: Conversion
:return: None
"""
foreign_keys_processed = MigrationStateManager.get(
conversion, 'foreign_keys_loaded')
if foreign_keys_processed:
return
params = [[conversion, table_name]
for table_name in conversion.tables_to_migrate]
ConcurrencyManager.run_in_parallel(
conversion, ForeignKeyProcessor._get_foreign_keys_metadata, params)
def generate_views(conversion):
"""
Attempts to convert MySQL views to PostgreSQL views.
:param conversion: Conversion
:return: None
"""
views_loaded = MigrationStateManager.get(conversion, 'views_loaded')
if views_loaded:
return
params = [[conversion, view_name]
for view_name in conversion.views_to_migrate]
ConcurrencyManager.run_in_parallel(conversion,
ViewGenerator._generate_single_view,
params)
def process_enum(conversion, table_name):
"""
Defines which columns of the given table are of type "enum".
Sets an appropriate constraint, if appropriate.
:param conversion: Conversion
:param table_name: str
:return: None
"""
log_title = 'EnumProcessor::process_enum'
msg = '\t--[%s] Defines "ENUMs" for table "%s"."%s"' % (log_title, conversion.schema, table_name)
FsOps.log(conversion, msg, conversion.dic_tables[table_name].table_log_path)
original_table_name = ExtraConfigProcessor.get_table_name(conversion, table_name, True)
params = [
[conversion, table_name, original_table_name, column]
for column in conversion.dic_tables[table_name].table_columns
if EnumProcessor._is_enum(column)
]
ConcurrencyManager.run_in_parallel(conversion, EnumProcessor._set_enum, params)
def _set_foreign_keys_for_given_table(conversion, table_name, rows):
"""
Sets foreign keys for given table.
:param conversion: Conversion
:param table_name: str
:param rows: list
:return: None
"""
constraints = {}
original_table_name = ExtraConfigProcessor.get_table_name(
conversion, table_name, should_get_original=True)
for row in rows:
current_column_name = ExtraConfigProcessor.get_column_name(
conversion=conversion,
original_table_name=original_table_name,
current_column_name=row['COLUMN_NAME'],
should_get_original=False)
current_referenced_table_name = ExtraConfigProcessor.get_table_name(
conversion=conversion,
current_table_name=row['REFERENCED_TABLE_NAME'],
should_get_original=False)
original_referenced_table_name = ExtraConfigProcessor.get_table_name(
conversion=conversion,
current_table_name=row['REFERENCED_TABLE_NAME'],
should_get_original=True)
current_referenced_column_name = ExtraConfigProcessor.get_column_name(
conversion=conversion,
original_table_name=original_referenced_table_name,
current_column_name=row['REFERENCED_COLUMN_NAME'],
should_get_original=False)
if row['CONSTRAINT_NAME'] in constraints:
constraints[row['CONSTRAINT_NAME']]['column_name'].append(
'"%s"' % current_column_name)
constraints[
row['CONSTRAINT_NAME']]['referenced_column_name'].append(
'"%s"' % current_referenced_column_name)
return
constraints[row['CONSTRAINT_NAME']] = {}
constraints[row['CONSTRAINT_NAME']]['column_name'] = [
'"%s"' % current_column_name
]
constraints[row['CONSTRAINT_NAME']]['referenced_column_name'] = [
'"%s"' % current_referenced_column_name
]
constraints[row['CONSTRAINT_NAME']][
'referenced_table_name'] = current_referenced_table_name
constraints[
row['CONSTRAINT_NAME']]['update_rule'] = row['UPDATE_RULE']
constraints[
row['CONSTRAINT_NAME']]['delete_rule'] = row['DELETE_RULE']
params = [[conversion, constraints, foreign_key, table_name]
for foreign_key in constraints.keys()]
ConcurrencyManager.run_in_parallel(
conversion, ForeignKeyProcessor._set_single_foreign_key, params)
def load_structure(conversion):
"""
Loads source tables and views, that need to be migrated.
:param conversion: Conversion, the configuration object.
:return: None
"""
log_title = 'StructureLoader::load_structure'
StructureLoader._get_mysql_version(conversion)
have_tables_loaded = MigrationStateManager.get(conversion,
'tables_loaded')
sql = 'SHOW FULL TABLES IN `%s` WHERE 1 = 1' % conversion.mysql_db_name
if len(conversion.include_tables) != 0:
include_tables = ','.join([
'"%s"' % table_name for table_name in conversion.include_tables
])
sql += ' AND Tables_in_%s IN(%s)' % (conversion.mysql_db_name,
include_tables)
if len(conversion.exclude_tables) != 0:
exclude_tables = ','.join([
'"%s"' % table_name for table_name in conversion.exclude_tables
])
sql += ' AND Tables_in_%s NOT IN(%s)' % (conversion.mysql_db_name,
exclude_tables)
sql += ';'
result = DBAccess.query(conversion=conversion,
caller=log_title,
sql=sql,
vendor=DBVendors.MYSQL,
process_exit_on_error=True,
should_return_client=False)
tables_cnt, views_cnt = 0, 0
thread_pool_params = []
for row in result.data:
relation_name = row['Tables_in_' + conversion.mysql_db_name]
if row['Table_type'] == 'BASE TABLE' and Utils.get_index_of(
relation_name, conversion.exclude_tables) == -1:
relation_name = ExtraConfigProcessor.get_table_name(
conversion, relation_name, False)
conversion.tables_to_migrate.append(relation_name)
conversion.dic_tables[relation_name] = Table(
'%s/%s.log' % (conversion.logs_dir_path, relation_name))
thread_pool_params.append(
[conversion, relation_name, have_tables_loaded])
tables_cnt += 1
elif row['Table_type'] == 'VIEW':
conversion.views_to_migrate.append(relation_name)
views_cnt += 1
ConcurrencyManager.run_in_parallel(
conversion, StructureLoader.process_table_before_data_loading,
thread_pool_params)
msg = '''\t--[{0}] Source DB structure is loaded...\n\t--[{0}] Tables to migrate: {1}\n
--[{0}] Views to migrate: {2}'''.format(log_title, tables_cnt,
views_cnt)
FsOps.log(conversion, msg)
MigrationStateManager.set(conversion, 'tables_loaded')