def setUp(self):
settings.read_properties("pydbcopy.conf")
self.source_host = MySQLHost(settings.source_host, settings.source_user, \
settings.source_password, settings.source_database)
self.dest_host = MySQLHost(settings.target_host, settings.target_user, \
settings.target_password, settings.target_database)
#
# Bring up the fixture
#
c = self.source_host.conn.cursor()
c.execute("SET AUTOCOMMIT=1")
# Create tmp_pydbcopy_test table and a single row
c.execute("create table if not exists tmp_pydbcopy_test ( id integer primary key, test_string varchar(50) )")
c.execute("insert into tmp_pydbcopy_test (id,test_string) values (1,'test')")
# Create tmp_hashed_pydbcopy_test table and three rows
c.execute("create table if not exists tmp_hashed_pydbcopy_test ( id integer primary key, test_string varchar(50), fieldHash varchar(50) )")
c.execute("insert into tmp_hashed_pydbcopy_test (id,test_string,fieldHash) values (1,'test','123')")
c.execute("insert into tmp_hashed_pydbcopy_test (id,test_string,fieldHash) values (2,'test1','234')")
c.execute("insert into tmp_hashed_pydbcopy_test (id,test_string,fieldHash) values (3,'test2','345')")
# Create tmp_pydbcopy_modified_table and a single row
c.execute("create table if not exists tmp_pydbcopy_modified_test ( id integer primary key, test_string varchar(50), lastModifiedDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP )")
c.execute("insert into tmp_pydbcopy_modified_test (id,test_string, lastModifiedDate) values (1,'test', '2010-11-23 05:00:00')")
c.close()
c = self.dest_host.conn.cursor()
c.execute("SET AUTOCOMMIT=1")
c.execute("create table if not exists tmp_pydbcopy_modified_test ( id integer primary key, test_string varchar(50), lastModifiedDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP )")
c.execute("insert into tmp_pydbcopy_modified_test (id,test_string, lastModifiedDate) values (1,'test', '2010-11-22 05:00:00')")
c.close()
def verify_and_copy_table(table):
"""
This routine verifies the specified table's row count on the source is within a certain
configurable threshold and if it is copies it to the local database. If the tables schema
on the source and target are equal and the source has not been moddified more recently
than the target then the copy is skipped. An incremental copy is attempted and if failed
then a full copy is attempted.
This is the main routine that the set of tables is mapped through by the multi-processing
pool. This routine returns the following codes:
0 = successful copy
1 = skipped copying table due to no change detected.
-1 = failed validity check, source row count is too different than target
Any other return value is an unknown failure.
"""
logging.getLogger('PyDBCopy')
# set up DB connections to both source and target DB
source_host = MySQLHost(settings.source_host, settings.source_user, \
settings.source_password, settings.source_database)
dest_host = MySQLHost(settings.target_host, settings.target_user, \
settings.target_password, settings.target_database)
if table not in settings.tables_to_skip_verification:
if not perform_validity_check(table, source_host, dest_host,
settings.verify_threshold):
return -1
if settings.no_last_mod_check \
or not dest_host.table_exists(table) \
or not schema_compare(table, source_host, dest_host, True) \
or not is_last_mod_same(table, source_host, dest_host):
copied = False
try:
if not settings.force_full:
logger.info("Starting incremental copy of table %s from %s(%s) to %s(%s)" % \
(table, source_host.database, source_host.host, dest_host.database, dest_host.host))
copied = perform_incremental_copy(table, source_host,
dest_host, settings.scp_user,
settings.dump_dir)
if copied:
logger.info("Successful incremental copy of table %s" %
table)
else:
logger.warn("Failed incremental copy of table %s" % table)
if not copied:
logger.info("Starting full copy of table %s from %s(%s) to %s(%s)" % \
(table, source_host.database, source_host.host, dest_host.database, dest_host.host))
copied = perform_full_copy(table, source_host, dest_host,
settings.scp_user,
settings.dump_dir)
if copied:
logger.info("Successful full copy of table %s" % table)
else:
logger.error("Failed full copy of table %s" % table)
except:
logger.error("Failed copy of table %s", table, exc_info=1)
if not copied:
return -3
else:
logger.info(
"Skipping copying of table %s (source/dest have same row count and last mod date)"
% table)
return 1
return 0
