def sync_mysql_database(): if status.get_enabled(): print() print("MySQL Sync: MySQL Sync Triggered!") from .VM import XenVm from .Host import XenHost from XenXenXenSe.VM import VM from XenXenXenSe.Host import Host from XenXenXenSe.session import create_session for cluster_id in xen_credentials: session = create_session(cluster_id) # ================================== print("MySQL Sync: MySQL Host Sync Triggered!") hosts = Host.list_host(session) for host in hosts: host.update(cluster_id, host) XenHost.remove_orphaned(cluster_id) # =================================== print("MySQL Sync: MySQL VM Sync Triggered!") vms = VM.list_vm(session) for _vm in vms: XenVm.update(cluster_id, _vm) XenVm.remove_orphaned(cluster_id) print("MySQL Sync: MySQL Sync Completed!") print()
def remove_orphaned(cls, cluster_id): if status.get_enabled(): try: connection = pymysql.connect( **mysql_credentials, cursorclass=pymysql.cursors.DictCursor) with connection.cursor() as cursor: from XenXenXenSe.session import create_session cls.sql = "SELECT * FROM `hosts`" cursor.execute(cls.sql) result = cursor.fetchall() for host_v in result: cluster_id = host_v['cluster_id'] host_uuid = host_v['host_uuid'] session = create_session(cluster_id) _host = Host.get_by_uuid(session, host_uuid) if _host is None: cls.sql = "DELETE FROM `hosts` WHERE `cluster_id`=%s AND `host_uuid`=%s" cursor.execute(cls.sql, (cluster_id, host_uuid)) connection.commit() except Exception as e: print("MySQL Sync: remove_orphaned failed.", e)
def init_connection(): if status.get_enabled(): print("MySQL Sync: Terminating Multiple Initialization") return if mysql_credentials is None: print("MySQL Sync: MySQL Caching is disabled!") return print() try: connection = pymysql.connect(**mysql_credentials, cursorclass=pymysql.cursors.DictCursor) with connection.cursor() as cursor: sql = '''CREATE TABLE IF NOT EXISTS `hosts` ( `cluster_id` VARCHAR(255) NOT NULL, `host_uuid` VARCHAR(255) NOT NULL, `lastUpdate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `cpu` TEXT NOT NULL, `cpu_speed` FLOAT NOT NULL, `free_memory` BIGINT NOT NULL, `memory` BIGINT NOT NULL );''' cursor.execute(sql) sql = '''CREATE TABLE IF NOT EXISTS `vms` ( `cluster_id` VARCHAR(255) NOT NULL, `vm_uuid` VARCHAR(255) NOT NULL, `lastUpdate` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `name` TEXT NOT NULL, `description` MEDIUMTEXT NOT NULL, `memory` BIGINT NOT NULL, `vCPUs` INT NOT NULL, `power` TEXT NOT NULL );''' cursor.execute(sql) connection.commit() connection.close() status.set_enabled(True) schedule.every(mysql_host_update_rate).seconds.do(sync_mysql_host_database) schedule.every(mysql_update_rate).seconds.do(sync_mysql_database) print("MySQL Sync: MySQL Caching is enabled!") except Exception as e: print("Database generation failed.", e)
def update(cls, cluster_id, _vm: VM): if _vm is None: print("nope") return if status.get_enabled(): try: connection = pymysql.connect( **mysql_credentials, cursorclass=pymysql.cursors.DictCursor) uuid = _vm.get_uuid() with connection.cursor() as cursor: cls.sql = "SELECT * FROM `vms` WHERE `cluster_id`=%s AND `vm_uuid`=%s" cursor.execute(cls.sql, (cluster_id, uuid)) vCPUs = int(_vm.get_vCPUs()) memory = int(_vm.get_memory()) name = _vm.get_name() description = _vm.get_description() power = _vm.get_power_state() if cursor.rowcount == 0: cls.sql = "INSERT INTO `vms` (`cluster_id`, `vm_uuid`, `vCPUs`, `memory`, `name`, `description`, `power`) VALUES (%s, %s, %s, %s, %s, %s, %s)" cursor.execute(cls.sql, (cluster_id, uuid, vCPUs, memory, name, description, power)) else: vm_data = cursor.fetchone() is_different = (int(vm_data['vCPUs']) != vCPUs or int(vm_data['memory']) != memory or vm_data['name'] != name or vm_data['description'] != description or vm_data['power'] != power) if is_different: cls.sql = "UPDATE `vms` SET `lastUpdate`=NOW(), `vCPUs`=%s, `memory`=%s, `name`=%s, `description`=%s, `power`=%s WHERE `cluster_id`=%s AND `vm_uuid`=%s" cursor.execute(cls.sql, (vCPUs, memory, name, description, power, cluster_id, uuid)) else: cls.sql = "UPDATE `vms` SET `lastUpdate`=NOW() WHERE `cluster_id`=%s AND `vm_uuid`=%s" cursor.execute(cls.sql, (cluster_id, uuid)) connection.commit() except Exception as e: print("MySQL Sync: update failed.", e, "\n", cls.sql)
def update(cls, cluster_id, _host: Host): if _host is None: print("nope") return if status.get_enabled(): try: connection = pymysql.connect( **mysql_credentials, cursorclass=pymysql.cursors.DictCursor) uuid = _host.get_uuid() with connection.cursor() as cursor: cls.sql = "SELECT * FROM `hosts` WHERE `cluster_id`=%s AND `host_uuid`=%s" cursor.execute(cls.sql, (cluster_id, uuid)) cpu_info = _host.get_cpu_info() cpu = cpu_info['modelname'] speed = float(cpu_info['speed']) free_memory = int(_host.get_free_memory()) memory = int(_host.get_total_memory()) if cursor.rowcount == 0: cls.sql = "INSERT INTO `hosts` (`cluster_id`, `host_uuid`, `cpu`, `cpu_speed`, `free_memory`, `memory`) VALUES (%s, %s, %s, %s, %s, %s)" cursor.execute(cls.sql, (cluster_id, uuid, cpu, speed, free_memory, memory)) else: host_data = cursor.fetchone() is_different = (host_data['cpu'] != cpu or host_data['cpu_speed'] != speed or host_data['free_memory'] != free_memory or host_data['memory'] != memory) if is_different: cls.sql = "UPDATE `hosts` SET `lastUpdate`=NOW(), `cpu`=%s, `cpu_speed`=%s, `free_memory`=%s, `memory`=%s WHERE `cluster_id`=%s AND `host_uuid`=%s" cursor.execute(cls.sql, (cpu, speed, free_memory, memory, cluster_id, uuid)) else: cls.sql = "UPDATE `hosts` SET `lastUpdate`=NOW() WHERE `cluster_id`=%s AND `host_uuid`=%s" cursor.execute(cls.sql, (cluster_id, uuid)) connection.commit() except Exception as e: print("MySQL Sync: update failed.", e, cls.sql)