def _set_index(conversion, index_name, table_name, pg_indexes, idx):
"""
Sets appropriate index.
:param conversion: conversion
:param index_name: str
:param table_name: str
:param pg_indexes: dict
:param idx: int
:return: None
"""
log_title = 'IndexesProcessor::_set_index'
sql_add_index = ''
if index_name.lower() == 'primary':
sql_add_index += 'ALTER TABLE "%s"."%s" ADD PRIMARY KEY(%s);' \
% (conversion.schema, table_name, ','.join(pg_indexes[index_name]['column_name']))
else:
column_name = '%s%s' % (pg_indexes[index_name]['column_name'][0][1:-1], str(idx))
index_type = 'UNIQUE' if pg_indexes[index_name]['is_unique'] else ''
sql_add_index += 'CREATE %s INDEX' % index_type
sql_add_index += ' "%s_%s_%s_idx"' % (conversion.schema, table_name, column_name)
sql_add_index += ' ON "%s"."%s"' % (conversion.schema, table_name)
sql_add_index += ' %s ' % pg_indexes[index_name]['index_type']
sql_add_index += '(%s);' % ','.join(pg_indexes[index_name]['column_name'])
DBAccess.query(
conversion=conversion,
caller=log_title,
sql=sql_add_index,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False
)
def create_schema(conversion):
"""
Creates a new PostgreSQL schema if it does not exist yet.
:param conversion: Conversion, the configuration object.
:return: None
"""
log_title = 'SchemaProcessor::create_schema'
result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql=
'SELECT schema_name FROM information_schema.schemata WHERE schema_name = \'%s\';'
% conversion.schema,
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=True)
if len(result.data) == 0:
DBAccess.query(conversion=conversion,
caller=log_title,
sql='CREATE SCHEMA "%s";' % conversion.schema,
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=False,
client=result.client)
def _check_connection(conversion):
"""
Checks correctness of connection details of both MySQL and PostgreSQL.
:param conversion: Conversion, the configuration object.
:return: str
"""
log_title = 'BootProcessor::check_connection'
result_message = ''
sql = 'SELECT 1;'
mysql_result = DBAccess.query(conversion=conversion,
caller=log_title,
sql=sql,
vendor=DBVendors.MYSQL,
process_exit_on_error=False,
should_return_client=False)
result_message += ' MySQL connection error: %s' % mysql_result.error if mysql_result.error else ''
pg_result = DBAccess.query(conversion=conversion,
caller=log_title,
sql=sql,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False)
result_message += ' PostgreSQL connection error: %s' % pg_result.error if pg_result.error else ''
return result_message
def _set_single_foreign_key(conversion, constraints, foreign_key,
table_name):
"""
Creates a single foreign key.
:param conversion: Conversion
:param constraints: dict
:param foreign_key: dict
:param table_name: str
:return: None
"""
log_title = 'ForeignKeyProcessor::_set_single_foreign_key'
sql = 'ALTER TABLE "{0}"."{1}" ADD FOREIGN KEY ({2}) REFERENCES "{0}"."{3}"({4}) ON UPDATE {5} ON DELETE {6};'\
.format(
conversion.schema,
table_name,
','.join(constraints[foreign_key]['column_name']),
constraints[foreign_key]['referenced_table_name'],
','.join(constraints[foreign_key]['referenced_column_name']),
constraints[foreign_key]['update_rule'],
constraints[foreign_key]['delete_rule']
)
DBAccess.query(conversion=conversion,
caller=log_title,
sql=sql,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False)
def prepare_data_chunks(conversion, table_name, have_data_chunks_processed):
"""
Prepares a list of tables metadata.
:param conversion: Conversion
:param table_name: str
:param have_data_chunks_processed: bool
:return: None
"""
if have_data_chunks_processed:
return
log_title = 'DataChunksProcessor::prepare_data_chunks'
log_path = conversion.dic_tables[table_name].table_log_path
original_table_name = ExtraConfigProcessor.get_table_name(conversion, table_name, True)
select_field_list = ColumnsDataArranger.arrange_columns_data(
conversion.dic_tables[table_name].table_columns,
conversion.mysql_version
)
rows_cnt_result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql='SELECT COUNT(1) AS rows_count FROM `%s`;' % original_table_name,
vendor=DBVendors.MYSQL,
process_exit_on_error=True,
should_return_client=False
)
rows_cnt = int(rows_cnt_result.data[0]['rows_count'])
msg = '\t--[%s] Total rows to insert into "%s"."%s": %d' % (log_title, conversion.schema, table_name, rows_cnt)
FsOps.log(conversion, msg, log_path)
meta = {
'_tableName': table_name,
'_selectFieldList': select_field_list,
'_rowsCnt': rows_cnt,
}
sql = 'INSERT INTO "{0}"."data_pool_{0}{1}"("metadata") VALUES (%(meta)s);' \
.format(conversion.schema, conversion.mysql_db_name)
DBAccess.query(
conversion=conversion,
caller=log_title,
sql=sql,
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=False,
client=None,
bindings={'meta': json.dumps(meta)}
)
def drop_state_logs_table(conversion):
"""
Drop state logs temporary table.
:param conversion: Conversion
:return: None
"""
DBAccess.query(
conversion=conversion,
caller='MigrationStateManager::drop_state_logs_table',
sql='DROP TABLE %s;' %
MigrationStateManager.get_state_logs_table_name(conversion),
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False)
def create_state_logs_table(conversion):
"""
Creates the "{schema}"."state_logs_{schema + mysql_db_name}" temporary table.
:param conversion: Conversion, the configuration object.
:return: None
"""
log_title = 'MigrationStateManager::create_state_logs_table'
table_name = MigrationStateManager.get_state_logs_table_name(
conversion)
sql = '''
CREATE TABLE IF NOT EXISTS %s("tables_loaded" BOOLEAN, "per_table_constraints_loaded" BOOLEAN,
"foreign_keys_loaded" BOOLEAN, "views_loaded" BOOLEAN);
''' % table_name
result = DBAccess.query(conversion=conversion,
caller=log_title,
sql=sql,
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=True)
result = DBAccess.query(conversion=conversion,
caller=log_title,
sql='SELECT COUNT(1) AS cnt FROM %s' %
table_name,
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=True,
client=result.client)
msg = '\t--[%s] Table %s' % (log_title, table_name)
if result.data[0]['cnt'] == 0:
DBAccess.query(
conversion=conversion,
caller=log_title,
sql='INSERT INTO %s VALUES (FALSE, FALSE, FALSE, FALSE);' %
table_name,
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=False,
client=result.client)
msg += ' is created.'
else:
msg += ' already exists.'
FsOps.log(conversion, msg)
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 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 boot(conversion):
"""
Boots the migration.
:param conversion: Conversion, the configuration object.
"return" None
"""
connection_error_message = BootProcessor._check_connection(conversion)
if connection_error_message:
error_message = '\t --[BootProcessor::boot] %s.' % connection_error_message
FsOps.generate_error(conversion, error_message)
sys.exit(1)
sql = """
SELECT EXISTS(SELECT 1 FROM information_schema.tables
WHERE table_schema = '%s' AND table_name = '%s') AS state_logs_table_exist;
""" % (conversion.schema, conversion.schema + conversion.mysql_db_name)
result = DBAccess.query(conversion=conversion,
caller='BootProcessor::boot',
sql=sql,
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=False)
state_logs_table_exist = result.data[0]['state_logs_table_exist']
state_message = '''\n\t--[BootProcessor::boot] FromMySqlToPostgreSql is ready to restart after some failure.
\n\t--[BootProcessor::boot] Consider checking log files at the end of migration.''' \
if state_logs_table_exist \
else '\n\t--[BootProcessor::boot] FromMySqlToPostgreSql is ready to start.'
print(state_message)
conversion.time_begin = time.time()
def set(conversion, *states):
"""
Updates the state-log.
:param conversion: Conversion
:param states: tuple
:return: None
"""
table_name = MigrationStateManager.get_state_logs_table_name(
conversion)
states_sql = ','.join(['%s = TRUE' % state for state in states])
DBAccess.query(conversion=conversion,
caller='MigrationStateManager::set',
sql='UPDATE %s SET %s;' % (table_name, states_sql),
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=False)
def _load(config, data_pool_item, connection_to_master):
"""
Loads the data using separate process.
:param config: dict
:param data_pool_item: dict
:param connection_to_master: multiprocessing.connection.PipeConnection
:return: None
"""
log_title = 'DataLoader::_load'
conversion = Conversion(config)
msg = '\t--[%s] Loading the data into "%s"."%s" table...' \
% (log_title, conversion.schema, data_pool_item['_tableName'])
FsOps.log(conversion, msg)
is_recovery_mode = DataLoader.data_transferred(conversion, data_pool_item['_id'])
if is_recovery_mode:
pg_client = DBAccess.get_db_client(conversion, DBVendors.PG)
DataLoader.delete_data_pool_item(conversion, data_pool_item['_id'], pg_client)
else:
DataLoader.populate_table_worker(
conversion,
data_pool_item['_tableName'],
data_pool_item['_selectFieldList'],
data_pool_item['_rowsCnt'],
data_pool_item['_id'],
connection_to_master
)
def _set_column_comment(conversion, table_name, original_table_name, column):
"""
Creates comment on specified column.
:param conversion: Conversion
:param table_name: str
:param original_table_name: str
:param column: dict
:return: None
"""
log_title = 'CommentsProcessor::_set_column_comment'
column_name = ExtraConfigProcessor.get_column_name(
conversion=conversion,
original_table_name=original_table_name,
current_column_name=column['Field'],
should_get_original=False
)
comment = CommentsProcessor._escape_quotes(column['Comment'])
create_comment_result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql='COMMENT ON COLUMN "%s"."%s"."%s" IS \'%s\';' % (conversion.schema, table_name, column_name, comment),
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False
)
if create_comment_result.error:
return
msg = '\t--[%s] Set comment for "%s"."%s" column: "%s"...' \
% (log_title, conversion.schema, table_name, column_name)
FsOps.log(conversion, msg, conversion.dic_tables[table_name].table_log_path)
def drop_data_pool_table(conversion):
"""
Drops data pool temporary table.
:param conversion: Conversion, the configuration object.
:return: None
"""
log_title = 'MigrationStateManager::drop_data_pool_table'
table_name = MigrationStateManager.get_data_pool_table_name(conversion)
DBAccess.query(conversion=conversion,
caller=log_title,
sql='DROP TABLE %s;' % table_name,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False)
FsOps.log(conversion,
'\t--[%s] table %s is dropped...' % (log_title, table_name))
def enable_triggers(conversion, pg_client, original_session_replication_role):
"""
Enables all triggers and rules for current database session.
!!!DO NOT release the client, it will be released after current data-chunk deletion.
:param conversion: Conversion
:param pg_client: PooledDedicatedDBConnection
:param original_session_replication_role: str
:return: None
"""
DBAccess.query(
conversion=conversion,
caller='DataLoader::enable_triggers',
sql='SET session_replication_role = %s;' % original_session_replication_role,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False,
client=pg_client
)
def _process_table_comments(conversion, table_name):
"""
Creates table comments.
:param conversion: Conversion
:param table_name: str
:return: None
"""
log_title = 'CommentsProcessor::_process_table_comments'
sql_select_comment = '''
SELECT table_comment AS table_comment FROM information_schema.tables
WHERE table_schema = '%s' AND table_name = '%s';
''' % (
conversion.mysql_db_name,
ExtraConfigProcessor.get_table_name(conversion, table_name, should_get_original=True)
)
select_comments_result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql=sql_select_comment,
vendor=DBVendors.MYSQL,
process_exit_on_error=False,
should_return_client=False
)
if select_comments_result.error:
return
comment = CommentsProcessor._escape_quotes(select_comments_result.data[0]['table_comment'])
sql_create_comment = 'COMMENT ON TABLE "%s"."%s" IS \'%s\';' % (conversion.schema, table_name, comment)
create_comment_result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql=sql_create_comment,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False
)
if create_comment_result.error:
return
msg = '\t--[%s] Successfully set comment for table "%s"."%s"' % (log_title, conversion.schema, table_name)
FsOps.log(conversion, msg, conversion.dic_tables[table_name].table_log_path)
def _get_foreign_keys_metadata(conversion, table_name):
"""
Retrieves foreign keys metadata.
:param conversion: Conversion
:param table_name: str
:return: None
"""
log_title = 'ForeignKeyProcessor::_get_foreign_keys_metadata'
msg = '\t--[%s] Search foreign keys for table "%s"."%s"...' % (
log_title, conversion.schema, table_name)
FsOps.log(conversion, msg)
sql = """
SELECT
cols.COLUMN_NAME, refs.REFERENCED_TABLE_NAME, refs.REFERENCED_COLUMN_NAME,
cRefs.UPDATE_RULE, cRefs.DELETE_RULE, cRefs.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.`COLUMNS` AS cols
INNER JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS refs
ON refs.TABLE_SCHEMA = cols.TABLE_SCHEMA
AND refs.REFERENCED_TABLE_SCHEMA = cols.TABLE_SCHEMA
AND refs.TABLE_NAME = cols.TABLE_NAME
AND refs.COLUMN_NAME = cols.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS cRefs
ON cRefs.CONSTRAINT_SCHEMA = cols.TABLE_SCHEMA
AND cRefs.CONSTRAINT_NAME = refs.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS links
ON links.TABLE_SCHEMA = cols.TABLE_SCHEMA
AND links.REFERENCED_TABLE_SCHEMA = cols.TABLE_SCHEMA
AND links.REFERENCED_TABLE_NAME = cols.TABLE_NAME
AND links.REFERENCED_COLUMN_NAME = cols.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS cLinks
ON cLinks.CONSTRAINT_SCHEMA = cols.TABLE_SCHEMA
AND cLinks.CONSTRAINT_NAME = links.CONSTRAINT_NAME
WHERE cols.TABLE_SCHEMA = '%s' AND cols.TABLE_NAME = '%s';
""" % (conversion.mysql_db_name,
ExtraConfigProcessor.get_table_name(
conversion, table_name, should_get_original=True))
result = DBAccess.query(conversion=conversion,
caller=log_title,
sql=sql,
vendor=DBVendors.MYSQL,
process_exit_on_error=False,
should_return_client=False)
if result.error:
return
extra_rows = ExtraConfigProcessor.parse_foreign_keys(
conversion, table_name)
full_rows = (result.data or []) + extra_rows
ForeignKeyProcessor._set_foreign_keys_for_given_table(
conversion, table_name, full_rows)
msg = '\t--[%s] Foreign keys for table "%s"."%s" are set...' % (
log_title, conversion.schema, table_name)
FsOps.log(conversion, msg)
def create_data_pool_table(conversion):
"""
Creates data pool temporary table.
:param conversion: Conversion, the configuration object.
:return: None
"""
log_title = 'MigrationStateManager::create_data_pool_table'
table_name = MigrationStateManager.get_data_pool_table_name(conversion)
DBAccess.query(
conversion=conversion,
caller=log_title,
sql=
'CREATE TABLE IF NOT EXISTS %s("id" BIGSERIAL, "metadata" JSON);' %
table_name,
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=False)
FsOps.log(conversion,
'\t--[%s] table %s is created...' % (log_title, table_name))
def _generate_single_view(conversion, view_name):
"""
Attempts to convert given view from MySQL to PostgreSQL.
:param conversion: Conversion
:param view_name: str
:return: None
"""
log_title = 'ViewGenerator::_generate_single_view'
show_create_view_result = DBAccess.query(conversion=conversion,
caller=log_title,
vendor=DBVendors.MYSQL,
process_exit_on_error=False,
should_return_client=False,
sql='SHOW CREATE VIEW `%s`;' %
view_name)
if show_create_view_result.error:
return
create_pg_view_sql = ViewGenerator._generate_view_code(
schema=conversion.schema,
view_name=view_name,
mysql_view_code=show_create_view_result.data[0]['Create View'])
create_pg_view_result = DBAccess.query(
conversion=conversion,
caller=log_title,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False,
should_return_programming_error=True,
sql=create_pg_view_sql)
if create_pg_view_result.error:
ViewGenerator._log_not_created_view(conversion, view_name,
create_pg_view_sql)
return
FsOps.log(
conversion, '\t--[%s] View "%s"."%s" is created...' %
(log_title, conversion.schema, view_name))
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 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
def disable_triggers(conversion, pg_client):
"""
Disables all triggers and rules for current database session.
!!!DO NOT release the client, it will be released after current data-chunk deletion.
:param conversion: Conversion
:param pg_client: PooledDedicatedDBConnection
:return: str
"""
sql = 'SHOW session_replication_role;'
original_session_replication_role = 'origin'
log_title = 'DataLoader::disable_triggers'
query_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
)
if query_result.data:
original_session_replication_role = query_result.data[0]['session_replication_role']
DBAccess.query(
conversion=conversion,
caller=log_title,
sql='SET session_replication_role = replica;',
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False,
client=query_result.client
)
return original_session_replication_role
def get(conversion, param):
"""
Retrieves appropriate state-log.
:param conversion: Conversion, the configuration object.
:param param: str, state-log parameter.
:return: bool
"""
table_name = MigrationStateManager.get_state_logs_table_name(
conversion)
result = DBAccess.query(conversion=conversion,
caller='MigrationStateManager::get',
sql='SELECT %s FROM %s;' % (param, table_name),
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=False)
return result.data[0][param]
def _decode(conversion, table_name, column_name):
log_title = 'BinaryDataDecoder::_decode'
sql = 'UPDATE {0}."{1}" SET "{2}" = DECODE(ENCODE("{2}", {3}}), {4});' \
.format(conversion.schema, table_name, column_name, "'escape'", "'hex'")
result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql=sql,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False
)
if not result.error:
msg = '\t--[%s] Decoded binary data from textual representation for table "%s"."%s".' \
% (log_title, conversion.schema, table_name)
FsOps.log(conversion, msg)
def set_sequence_value(conversion, table_name):
"""
Sets sequence value.
:param conversion: Conversion
:param table_name: str
:return: None
"""
original_table_name = ExtraConfigProcessor.get_table_name(
conversion, table_name, True)
table_columns_list = conversion.dic_tables[table_name].table_columns
auto_increment_columns = [
column for column in table_columns_list
if column['Extra'] == 'auto_increment'
]
if len(auto_increment_columns) == 0:
return # No auto-incremented column found.
log_title = 'SequenceProcessor::set_sequence_value'
auto_increment_column = auto_increment_columns[0]['Field']
column_name = ExtraConfigProcessor.get_column_name(
conversion=conversion,
original_table_name=original_table_name,
current_column_name=auto_increment_column,
should_get_original=False)
seq_name = '%s_%s_seq' % (table_name, column_name)
sql = 'SELECT SETVAL(\'"%s"."%s"\',' % (conversion.schema, seq_name)
sql += '(SELECT MAX("%s") FROM "%s"."%s"));' % (
column_name, conversion.schema, table_name)
result = DBAccess.query(conversion=conversion,
caller=log_title,
sql=sql,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False)
if not result.error:
msg = '\t--[%s] Sequence "%s"."%s" is created...' % (
log_title, conversion.schema, seq_name)
FsOps.log(conversion, msg,
conversion.dic_tables[table_name].table_log_path)
def _get_mysql_version(conversion):
"""
Retrieves the source db version.
:param conversion: Conversion, the configuration object.
:return: None
"""
result = DBAccess.query(conversion=conversion,
caller='StructureLoader::get_mysql_version',
sql='SELECT VERSION() AS mysql_version;',
vendor=DBVendors.MYSQL,
process_exit_on_error=False,
should_return_client=False)
if not result.error:
split_version = result.data[0]['mysql_version'].split('.')
major_version = split_version[0]
minor_version_with_postfix = ''.join(split_version[1:])
minor_version = minor_version_with_postfix.split('-')[0]
conversion.mysql_version = '{0}.{1}'.format(
major_version, minor_version)
def read_data_pool(conversion):
"""
Reads temporary table ("{schema}"."data_pool_{schema + mysql_db_name}"), and generates data-pool.
:param conversion: Conversion
:return: None
"""
log_title = 'MigrationStateManager::read_data_pool'
table_name = MigrationStateManager.get_data_pool_table_name(conversion)
result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql='SELECT id AS id, metadata AS metadata FROM %s;' % table_name,
vendor=DBVendors.PG,
process_exit_on_error=True,
should_return_client=False)
for row in result.data:
metadata = row['metadata']
metadata['_id'] = row['id']
conversion.data_pool.append(metadata)
FsOps.log(conversion, '\t--[%s] Data-Pool is loaded...' % log_title)
def _arrange_and_load_batch(
conversion_config,
table_name,
batch,
rows_cnt,
rows_to_insert,
number_of_inserted_rows
):
"""
Formats a batch of data as csv, and passes it to COPY.
:param conversion_config: dict
:param table_name: str
:param batch: list
:param rows_cnt: int
:param rows_to_insert: int
:param number_of_inserted_rows: int
:return: None
"""
conversion = Conversion(conversion_config)
pg_client = DBAccess.get_db_client(conversion, DBVendors.PG)
pg_cursor = pg_client.cursor()
if conversion.should_migrate_only_data():
# TODO: how to pass original_session_replication_role to the parent?
original_session_replication_role = DataLoader.disable_triggers(conversion, pg_client)
rows = ColumnsDataArranger.prepare_batch_for_copy(batch)
text_stream = io.StringIO()
text_stream.write(rows)
text_stream.seek(0)
pg_cursor.copy_from(text_stream, '"%s"."%s"' % (conversion.schema, table_name))
pg_client.commit()
number_of_inserted_rows += rows_to_insert
msg = '\t--[{0}] For now inserted: {4} rows, Total rows to insert into "{2}"."{3}": {1}' \
.format(log_title, rows_cnt, conversion.schema, table_name, number_of_inserted_rows)
print(msg) # TODO: check why FsOps.log() below doesn't work as expected.
FsOps.log(conversion, msg)
def _set_enum(conversion, table_name, original_table_name, column):
"""
Checks if given column is an enum.
Sets the enum, if appropriate.
:param conversion: Conversion
:param table_name: str
:param original_table_name: str
:param column: dict
:return: None
"""
log_title = 'EnumProcessor::_set_enum'
column_name = ExtraConfigProcessor.get_column_name(
conversion=conversion,
original_table_name=original_table_name,
current_column_name=column['Field'],
should_get_original=False
)
enum_values = column['Type'].split('(')[1] # Exists due to EnumProcessor._is_enum execution result.
sql = 'ALTER TABLE "%s"."%s" ADD CHECK ("%s" IN (%s);' \
% (conversion.schema, table_name, column_name, enum_values)
result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql=sql,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False
)
if not result.error:
msg = '\t--[%s] Set "ENUM" for "%s"."%s"."%s"...' \
% (log_title, conversion.schema, table_name, column_name)
FsOps.log(conversion, msg, conversion.dic_tables[table_name].table_log_path)
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.')
