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()