def delete_data_pool_item(conversion, data_pool_id, pg_client, original_session_replication_role):
"""
Deletes given record from the data-pool.
:param conversion: Conversion
:param data_pool_id: int
:param pg_client: PooledDedicatedDBConnection
:param original_session_replication_role: str | None
:return: None
"""
log_title = 'DataLoader::delete_data_pool_item'
data_pool_table_name = MigrationStateManager.get_data_pool_table_name(conversion)
sql = 'DELETE FROM %s WHERE id = %d;' % (data_pool_table_name, data_pool_id)
result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql=sql,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=True,
client=pg_client
)
FsOps.log(conversion, f'\t--[{log_title}] Deleted #{data_pool_id} from data-pool')
if original_session_replication_role and result.client:
DataLoader.enable_triggers(conversion, result.client, original_session_replication_role)
def send_data(conversion):
"""
Sends the data to the loader processes.
:param conversion: Conversion
:return: None
"""
if len(conversion.data_pool) == 0:
return
params_list = [[conversion.config, meta] for meta in conversion.data_pool]
reader_connections = []
number_of_workers = min(
conversion.max_each_db_connection_pool_size,
len(conversion.data_pool),
cpu_count()
)
with ProcessPoolExecutor(max_workers=number_of_workers) as executor:
while len(params_list) != 0:
reader_connection, writer_connection = Pipe(duplex=False)
reader_connections.append(reader_connection)
params = params_list.pop()
params.append(writer_connection)
executor.submit(DataLoader._load, *params)
while reader_connections:
for reader_connection in connection.wait(object_list=reader_connections):
just_populated_table_name = ''
try:
just_populated_table_name = reader_connection.recv()
reader_connection.close()
finally:
reader_connections.remove(reader_connection)
ConstraintsProcessor.process_constraints_per_table(conversion, just_populated_table_name)
MigrationStateManager.set(conversion, 'per_table_constraints_loaded')
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 data_transferred(conversion, data_pool_id):
"""
Enforces consistency before processing a chunk of data.
Ensures there are no data duplications.
In case of normal execution - it is a good practice.
In case of rerunning migration after unexpected failure - it is absolutely mandatory.
:param conversion: Conversion
:param data_pool_id: int
:return: bool
"""
log_title = 'DataLoader::data_transferred'
data_pool_table_name = MigrationStateManager.get_data_pool_table_name(conversion)
result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql='SELECT metadata AS metadata FROM %s WHERE id = %d;' % (data_pool_table_name, data_pool_id),
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=True
)
metadata = result.data[0]['metadata']
target_table_name = '"%s"."%s"' % (conversion.schema, metadata['_tableName'])
probe = DBAccess.query(
conversion=conversion,
caller=log_title,
sql='SELECT * FROM %s LIMIT 1 OFFSET 0;' % target_table_name,
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=False,
client=result.client
)
return len(probe.data) != 0
from SchemaProcessor import SchemaProcessor
from Conversion import Conversion
from MigrationStateManager import MigrationStateManager
from StructureLoader import StructureLoader
from ReportGenerator import ReportGenerator
from DataLoader import DataLoader
from ConstraintsProcessor import ConstraintsProcessor
from DBAccess import DBAccess
from BinaryDataDecoder import BinaryDataDecoder
if __name__ == '__main__':
print(BootProcessor.get_introduction_message())
base_dir = os.getcwd()
config = FsOps.read_config(base_dir)
config = FsOps.read_extra_config(config, base_dir)
conversion = Conversion(config)
FsOps.create_logs_directory(conversion)
BootProcessor.boot(conversion)
FsOps.read_data_types_map(conversion)
SchemaProcessor.create_schema(conversion)
MigrationStateManager.create_state_logs_table(conversion)
MigrationStateManager.create_data_pool_table(conversion)
StructureLoader.load_structure(conversion)
MigrationStateManager.read_data_pool(conversion)
DataLoader.send_data(conversion)
BinaryDataDecoder.decode(conversion)
ConstraintsProcessor.process_constraints(conversion)
DBAccess.close_connection_pools(conversion)
ReportGenerator.generate_report(conversion, 'Migration is accomplished.')
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 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')