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 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 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 query_without_transaction(conversion, caller, sql):
"""
Sends given query to the target PostgreSQL database without wrapping it with transaction.
:param conversion: Conversion, the configuration object.
:param caller: str, a name of the function, that has just sent the query for execution.
:param sql: str
:return: DBAccessQueryResult
"""
client, cursor, error = None, None, None
try:
connection_details = {
'port': conversion.target_con_string['port'],
'host': conversion.target_con_string['host'],
'user': conversion.target_con_string['user'],
'password': conversion.target_con_string['password'],
'database': conversion.target_con_string['database'],
'client_encoding': conversion.target_con_string['charset'],
}
client = psycopg2.connect(**connection_details)
client.set_isolation_level(0)
cursor = client.cursor()
cursor.execute(sql)
except Exception as e:
error = e
FsOps.generate_error(conversion, '\t--[%s] %s' % (caller, e), sql)
finally:
if cursor:
cursor.close()
DBAccess.release_db_client(conversion, client)
return DBAccessQueryResult(None, None, error)
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 _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 _log_not_created_view(conversion, view_name, sql):
"""
Writes a log, containing a code of the view FromMySqlToPostgreSql has just failed to create.
:param conversion: Conversion
:param view_name: str
:param sql: str
:return: None
"""
view_file_path = os.path.join(conversion.not_created_views_path,
'%s.sql' % view_name)
FsOps.write_to_file(view_file_path, 'w', sql)
def process_comments(conversion, table_name):
"""
Migrates comments.
:param conversion: Conversion
:param table_name: str
:return: None
"""
log_title = 'CommentsProcessor::processComments'
msg = '\t--[%s] Creates comments for table "%s"."%s"...' % (log_title, conversion.schema, table_name)
FsOps.log(conversion, msg, conversion.dic_tables[table_name].table_log_path)
CommentsProcessor._process_table_comments(conversion, table_name)
CommentsProcessor._process_columns_comments(conversion, table_name)
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 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 close_connection_pools(conversion):
"""
Closes both connection-pools.
:param conversion: Conversion
:return: None
"""
for pool in (conversion.mysql, conversion.pg):
if pool:
try:
pool.close()
except Exception as e:
FsOps.generate_error(
conversion,
'\t--[DBAccess::close_connection_pools] %s' % e)
def release_db_client(conversion, client):
"""
Releases MySQL or PostgreSQL connection back to appropriate pool.
:param conversion: Conversion, the configuration object.
:param client: PooledDedicatedDBConnection
:return: None
"""
if client:
try:
client.close()
client = None
except Exception as e:
FsOps.generate_error(
conversion, '\t--[DBAccess::release_db_client] %s' % e)
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 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 _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_db_client(conversion, db_vendor):
"""
Obtains PooledDedicatedDBConnection instance.
:param conversion: Conversion, the configuration object.
:param db_vendor: int, mimics enum, representing database vendors: MySQL and PostgreSQL.
:return: PooledDedicatedDBConnection
"""
if db_vendor == DBVendors.PG:
DBAccess._ensure_pg_connection(conversion)
return conversion.pg.connection(shareable=False)
elif db_vendor == DBVendors.MYSQL:
DBAccess._ensure_mysql_connection(conversion)
return conversion.mysql.connection(shareable=False)
else:
FsOps.generate_error(
conversion,
'\t --[DBAccess::get_db_client] unknown db_vendor %s.' %
db_vendor)
sys.exit(1)
def _get_pooled_db(conversion, db_vendor, db_connection_details):
"""
Creates DBUtils.PooledDB instance.
:param conversion: Conversion, the configuration object.
:param db_vendor: int
:param db_connection_details: dict
:return: DBUtils.PooledDB instance
"""
connection_details = {
'port': db_connection_details['port'],
'host': db_connection_details['host'],
'user': db_connection_details['user'],
'password': db_connection_details['password'],
'database': db_connection_details['database'],
'blocking': True,
'maxcached': conversion.max_each_db_connection_pool_size,
'maxconnections': conversion.max_each_db_connection_pool_size,
}
if db_vendor == DBVendors.MYSQL:
connection_details.update({
'creator':
pymysql,
'cursorclass':
pymysql.cursors.DictCursor,
'charset':
db_connection_details['charset'],
})
elif db_vendor == DBVendors.PG:
connection_details.update({
'creator':
psycopg2,
'client_encoding':
db_connection_details['charset'],
})
else:
FsOps.generate_error(
conversion,
'\t --[DBAccess::_get_pooled_db] unknown db_vendor %s.' %
db_vendor)
sys.exit(1)
return PooledDB(**connection_details)
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 _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 generate_report(conversion, last_message):
"""
Generates a summary report.
:param conversion: Conversion
:param last_message: str
:return: None
"""
log_title = 'ReportGenerator::generateReport'
difference_sec = (time.time() - conversion.time_begin)
seconds = math.floor(difference_sec % 60)
difference_sec /= 60
minutes = math.floor(difference_sec % 60)
hours = math.floor(difference_sec / 60)
formatted_hours = '0%d' % hours if hours < 10 else '%d' % hours
formatted_minutes = '0%d' % minutes if minutes < 10 else '%d' % minutes
formatted_seconds = '0%d' % seconds if seconds < 10 else '%d' % seconds
output = '\n\t--[{0}] {1}\n\t--[{0}] Total time: {2}:{3}:{4}\n\t--[{0}] (hours:minutes:seconds)' \
.format(log_title, last_message, formatted_hours, formatted_minutes, formatted_seconds)
FsOps.log(conversion, output)
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 _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 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 _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 BootProcessor import BootProcessor
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 populate_table_worker(
conversion,
table_name,
str_select_field_list,
rows_cnt,
data_pool_id,
connection_to_master
):
"""
Loads a chunk of data using "PostgreSQL COPY".
:param conversion: Conversion
:param table_name: str
:param str_select_field_list: str
:param rows_cnt: int
:param data_pool_id: int
:param connection_to_master: multiprocessing.connection.Connection
:return: None
"""
log_title = 'DataLoader::populate_table_worker'
original_table_name = ExtraConfigProcessor.get_table_name(conversion, table_name, True)
sql = 'SELECT %s FROM `%s`;' % (str_select_field_list, original_table_name)
original_session_replication_role = None
text_stream = None
pg_cursor = None
pg_client = None
mysql_cursor = None
mysql_client = None
try:
mysql_client = DBAccess.get_mysql_unbuffered_client(conversion)
mysql_cursor = mysql_client.cursor()
mysql_cursor.execute(sql)
number_of_inserted_rows = 0
number_of_workers = min(conversion.max_each_db_connection_pool_size / 2, cpu_count())
with ProcessPoolExecutor(max_workers=number_of_workers) as executor:
while True:
batch_size = 25000 # TODO: think about batch size calculation.
batch = mysql_cursor.fetchmany(batch_size)
rows_to_insert = len(batch)
if rows_to_insert == 0:
break
executor.submit(
DataLoader._arrange_and_load_batch,
conversion.config,
table_name,
batch,
rows_cnt,
rows_to_insert,
number_of_inserted_rows
)
except Exception as e:
msg = 'Data retrieved by following MySQL query has been rejected by the target PostgreSQL server.'
FsOps.generate_error(conversion, '\t--[{0}] {1}\n\t--[{0}] {2}'.format(log_title, e, msg), sql)
finally:
try:
connection_to_master.send(table_name)
except Exception as ex:
msg = 'Failed to notify master that %s table\'s populating is finished.' % table_name
FsOps.generate_error(conversion, '\t--[{0}] {1}\n\t--[{0}] {2}'.format(log_title, ex, msg))
finally:
for resource in (connection_to_master, text_stream, pg_cursor, mysql_cursor, mysql_client):
if resource:
resource.close()
DataLoader.delete_data_pool_item(conversion, data_pool_id, pg_client, original_session_replication_role)
def create_sequence(conversion, table_name):
"""
Defines which column in given table has the "auto_increment" attribute.
Creates an appropriate sequence.
: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::create_sequence'
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)
create_sequence_result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql='CREATE SEQUENCE "%s"."%s";' % (conversion.schema, seq_name),
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=True)
if create_sequence_result.error:
DBAccess.release_db_client(conversion,
create_sequence_result.client)
return
sql_set_next_val = 'ALTER TABLE "%s"."%s" ALTER COLUMN "%s"' % (
conversion.schema, table_name, column_name)
sql_set_next_val += " SET DEFAULT NEXTVAL('%s.%s');" % (
conversion.schema, seq_name)
set_next_val_result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql=sql_set_next_val,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=True,
client=create_sequence_result.client)
if set_next_val_result.error:
DBAccess.release_db_client(conversion, set_next_val_result.client)
return
sql_set_sequence_owner = 'ALTER SEQUENCE "{0}"."{1}" OWNED BY "{0}"."{2}"."{3}";'\
.format(conversion.schema, seq_name, table_name, column_name)
set_sequence_owner_result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql=sql_set_sequence_owner,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=True,
client=set_next_val_result.client)
if set_sequence_owner_result.error:
DBAccess.release_db_client(conversion,
set_sequence_owner_result.client)
return
sql_set_sequence_value = 'SELECT SETVAL(\'"{0}"."{1}"\', (SELECT MAX("{2}") FROM "{0}"."{3}"));'\
.format(conversion.schema, seq_name, column_name, table_name)
set_sequence_value_result = DBAccess.query(
conversion=conversion,
caller=log_title,
sql=sql_set_sequence_value,
vendor=DBVendors.PG,
process_exit_on_error=False,
should_return_client=False,
client=set_sequence_owner_result.client)
if not set_sequence_value_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)