VI-DB vi-db-initial

CREATE DATABASE vi;

# VC table - this is the only table that must be manually populated
CREATE TABLE vc (vcid TINYINT UNSIGNED AUTO_INCREMENT, name VARCHAR(32), site VARCHAR(3), ver VARCHAR(7), build INT UNSIGNED, last_pass DATETIME, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vcid), UNIQUE KEY (name));

# Cluster table
CREATE TABLE cluster (clid TINYINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, name VARCHAR(32), exist BOOL, ha BOOL, drs VARCHAR(18), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (clid), UNIQUE KEY (vcid, name));

# VM tables
CREATE TABLE vm (vmid INT UNSIGNED AUTO_INCREMENT, uuid VARCHAR(37), vmvcid SMALLINT UNSIGNED, pid VARCHAR(37), name VARCHAR(32), exist BOOL, is_on BOOL, vcid TINYINT UNSIGNED, clid TINYINT UNSIGNED, esxid SMALLINT UNSIGNED, hostname VARCHAR(255), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid), UNIQUE KEY (uuid, pid), UNIQUE KEY (vmvcid, vcid));

CREATE TABLE vm_ext (vmid INT UNSIGNED, cpu TINYINT UNSIGNED, mem INT UNSIGNED, vc_path VARCHAR(512), vmx_path VARCHAR(512), osid SMALLINT UNSIGNED, scsi_hw VARCHAR(16), notes VARCHAR(512), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid));

# ESX tables
CREATE TABLE esx (esxid SMALLINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, clid TINYINT UNSIGNED, name VARCHAR(32), exist BOOL, state VARCHAR(16), ver VARCHAR(7), build INT UNSIGNED, model VARCHAR(32), cpu_core TINYINT UNSIGNED, cpu_sock TINYINT UNSIGNED, mem INT UNSIGNED COMMENT 'MB', cpu_model VARCHAR(128), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (esxid), UNIQUE KEY (vcid, clid, name));

CREATE TABLE esx_state (esxid SMALLINT UNSIGNED, state VARCHAR(16), status VARCHAR(6), cpu_pc TINYINT, mem_pc TINYINT, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (esxid));

CREATE TABLE esx2ds (esxid SMALLINT UNSIGNED, dsid SMALLINT UNSIGNED, PRIMARY KEY (esxid, dsid));
CREATE TABLE esx2nw (esxid SMALLINT UNSIGNED, nwid SMALLINT UNSIGNED, PRIMARY KEY (esxid, nwid));

# Datastore and VMDK tables
CREATE TABLE ds (dsid SMALLINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, name VARCHAR(32), exist BOOL, size INT UNSIGNED, used INT UNSIGNED, san_model VARCHAR(32), lun TINYINT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (dsid), UNIQUE KEY (vcid, name));

CREATE TABLE vmdk (dkid INT UNSIGNED AUTO_INCREMENT, dsid SMALLINT UNSIGNED, vmid INT UNSIGNED, num SMALLINT UNSIGNED, size INT UNSIGNED, thin BOOL, path VARCHAR(256), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (dkid), UNIQUE KEY (vmid, num));

# Networking tables
CREATE TABLE nw (nwid SMALLINT UNSIGNED AUTO_INCREMENT, vcid TINYINT UNSIGNED, name VARCHAR(64), exist BOOL, vlan SMALLINT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (nwid), UNIQUE KEY (vcid, name));

CREATE TABLE vm_nic (vnicid INT UNSIGNED AUTO_INCREMENT, vmid INT UNSIGNED, num SMALLINT UNSIGNED, nwid SMALLINT UNSIGNED, type VARCHAR(16), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vnicid), UNIQUE KEY (vmid, num));
CREATE TABLE vm2ip (vmid INT UNSIGNED, vnicid INT UNSIGNED, ip INT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid, vnicid));

CREATE TABLE vm2ip (vmid INT UNSIGNED, vnicid INT UNSIGNED, ip INT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid, vnicid));

# Other tables
CREATE TABLE os (osid TINYINT UNSIGNED AUTO_INCREMENT, guest_name VARCHAR(128), short_name VARCHAR(32), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (osid), UNIQUE KEY (guest_name));

CREATE TABLE script_run (runid INT UNSIGNED AUTO_INCREMENT, start DATETIME, end DATETIME, version VARCHAR(8), error INT UNSIGNED, warn INT UNSIGNED, PRIMARY KEY (runid));

CREATE TABLE cluster_stats (clid TINYINT UNSIGNED, dated DATE, esxs TINYINT UNSIGNED, vms_exist SMALLINT UNSIGNED, vms_on SMALLINT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (clid, dated));

CREATE TABLE snap (vmid INT UNSIGNED, sid INT UNSIGNED, vcid TINYINT UNSIGNED, name VARCHAR(256), descr VARCHAR(1024), created DATETIME, quiesced BOOL, vm_on BOOL, size MEDIUMINT UNSIGNED, parent_sid INT UNSIGNED, stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (vmid, sid), UNIQUE KEY (vcid, sid));

# Eventing
CREATE DEFINER=`root`@`192.168.196.1` PROCEDURE `update_cluster_stats`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE clust_id INT;
    DECLARE clust CURSOR FOR SELECT clid FROM cluster;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN clust;
    
    read_loop: LOOP
        FETCH clust INTO clust_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        INSERT INTO cluster_stats (clid, dated, esxs, vms_exist, vms_on)
            SELECT clust_id, NOW(), 
            (SELECT COUNT(*) FROM esx WHERE clid=clust_id AND exist=1) AS esxs, 
            COUNT(*) AS vms_exist, SUM(IF(is_on=1,1,0)) AS vms_on
            FROM vm WHERE clid=clust_id AND exist=1;
        
    END LOOP;
    
    CLOSE clust;
END

CREATE EVENT do_update_cluster_stats ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE, '23:30:00') DO CALL update_cluster_stats();