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)