def replicate_table(table_name):
if table_name is None:
raise Exception("table_name must have a value")
src_db_connection = None
replicated_db_connection = None
src_cursor = None
query_sentence = "<Unknown>"
try:
src_db_connection = commonFunctions.connect_to_db(config.mysql)
replicated_db_connection = commonFunctions.connect_to_db(
config.replicator['db'])
query_sentence = (
"SELECT * FROM %s where %s = false or %s is null" %
(table_name, config.replicator['replicated_attribute'],
config.replicator['replicated_attribute']))
logging.info("Fetching records from src table: %s", query_sentence)
logging.info(
"Number of records to be replicated for table {}: {}".format(
table_name,
count_pending_replication_records_for_table(
config.mysql, table_name)))
src_cursor = src_db_connection.cursor()
src_cursor.execute(query_sentence)
while True:
record = commonFunctions.fetch_one_record_as_dictionary(src_cursor)
if record is None:
logging.debug("Next record was empty")
break
logging.debug("duplicating record %s" % record['id'])
insert_record_in_replicated_db(record, table_name,
config.replicator['db'])
update_record_as_replicated(record, table_name, config.mysql)
return True
except mariadb.Error as error:
logging.debug("Error saving to DB: {}".format(error))
logging.error("Query is %s", query_sentence)
return False
finally:
if src_cursor is not None:
src_cursor.close()
if src_db_connection is not None:
src_db_connection.close()
if replicated_db_connection is not None:
replicated_db_connection.close()
def count_pending_replication_records_for_table(db_connection_config,
table_name):
cursor = None
connection = None
try:
query_sentence = (
"SELECT count(*) FROM %s where %s = false or %s is null" %
(table_name, config.replicator['replicated_attribute'],
config.replicator['replicated_attribute']))
logging.debug("Counting records from src table: %s", query_sentence)
connection = commonFunctions.connect_to_db(db_connection_config)
cursor = connection.cursor()
cursor.execute(query_sentence)
count = cursor.fetchone()
return count[0]
except mariadb.Error as error:
logging.debug("Error saving to DB: {}".format(error))
logging.error("Query is %s", query_sentence)
return -1
finally:
if cursor is not None:
cursor.close()
if connection is not None:
connection.close()
def optimize_table(table_name):
if table_name is None:
raise Exception("table_name must have a value")
connection = None
cursor = None
try:
connection = commonFunctions.connect_to_db(config.mysql)
optimize_sentence = ("OPTIMIZE TABLE %s" % (table_name))
#delete_sentence = ("SELECT Count(*) FROM %s WHERE %s = true AND DATE_ADD(NOW(), INTERVAL - %s HOUR) > measurement_date" % (table_name, config.replicator['replicated_attribute'], max_age_in_source_in_hours))
logging.info("Optimizing table %s with sentence %s" %
(table_name, optimize_sentence))
cursor = connection.cursor()
cursor.execute(optimize_sentence)
commonFunctions.flush_cursor(cursor)
connection.commit()
logging.info("Optimize successful")
except mariadb.Error as error:
logging.debug("Error saving to DB: {}".format(error))
logging.error("Query is %s", optimize_sentence)
logging.error(error)
logging.error(traceback.format_exc())
raise error
finally:
if cursor is not None:
cursor.close()
if connection is not None:
connection.close()
def delete_replicated_up_to(table_name, max_age_in_source_in_hours):
if table_name is None:
raise Exception("table_name must have a value")
if max_age_in_source_in_hours is None:
raise Exception("max_age_in_source_in_hours must have a value")
connection = None
cursor = None
try:
connection = commonFunctions.connect_to_db(config.mysql)
delete_sentence = (
"DELETE FROM %s WHERE %s = true AND DATE_ADD(NOW(), INTERVAL - %s HOUR) > measurement_date"
% (table_name, config.replicator['replicated_attribute'],
max_age_in_source_in_hours))
#delete_sentence = ("SELECT Count(*) FROM %s WHERE %s = true AND DATE_ADD(NOW(), INTERVAL - %s HOUR) > measurement_date" % (table_name, config.replicator['replicated_attribute'], max_age_in_source_in_hours))
logging.info(
"Deleting replicated records older than %s hours with sentence %s"
% (max_age_in_source_in_hours, delete_sentence))
cursor = connection.cursor()
cursor.execute(delete_sentence)
connection.commit()
logging.info("Delete successful")
except mariadb.Error as error:
logging.debug("Error saving to DB: {}".format(error))
logging.error("Query is %s", delete_sentence)
logging.error(error)
logging.error(traceback.format_exc())
raise error
finally:
if cursor is not None:
cursor.close()
if connection is not None:
connection.close()
def update_record_as_replicated(record, table_name, db_connection_config):
if record is None:
raise Exception("record must have a value")
connection = None
cursor = None
try:
connection = commonFunctions.connect_to_db(db_connection_config)
update_sentence = (
"UPDATE %s SET %s = true where id = %s LIMIT 1" %
(table_name, config.replicator['replicated_attribute'],
record['id']))
logging.debug("Updating record %s with sentence %s" %
(record['id'], update_sentence))
cursor = connection.cursor()
cursor.execute(update_sentence)
connection.commit()
logging.debug("Update successful")
except mariadb.Error as error:
logging.debug("Error saving to DB: {}".format(error))
logging.error("Query is %s", update_sentence)
logging.error(error)
logging.error(traceback.format_exc())
return -1
finally:
if cursor is not None:
cursor.close()
if connection is not None:
connection.close()
def insert_record_in_replicated_db(original_record, table_name,
db_connection_config):
if original_record is None:
raise Exception("record must have a value")
if table_name is None:
raise Exception("table_name must have a value")
connection = None
cursor = None
try:
#Don't duplicate the id, let the DB generate one
connection = commonFunctions.connect_to_db(db_connection_config)
record = original_record.copy()
del record['id']
del record[config.replicator['replicated_attribute']]
insert_sentence = ("INSERT INTO %s (%s) VALUES (%s)" %
(table_name, ", ".join(record.keys()),
get_values_with_types_for_insert(record)))
logging.debug("Inserting record using sentence %s" % (insert_sentence))
cursor = connection.cursor()
cursor.execute(insert_sentence)
connection.commit()
logging.debug("Insert successful")
except mariadb.Error as error:
logging.debug("Error saving to DB: {}".format(error))
logging.error("Query is %s", insert_sentence)
raise error
finally:
if cursor is not None:
cursor.close()
if connection is not None:
connection.close()