use puppet DROP TRIGGER IF EXISTS trg_hosts_delete; DELIMITER $$ CREATE TRIGGER trg_hosts_delete AFTER DELETE ON hosts FOR EACH ROW BEGIN # Cleanup host related data DELETE FROM fact_values WHERE host_id = OLD.id; DELETE resource_tags FROM resources, resource_tags WHERE resource_id = resources.id AND host_id = OLD.id; DELETE param_values FROM resources, param_values WHERE resource_id = resources.id AND host_id = OLD.id; DELETE resources FROM resources WHERE host_id = OLD.id; # Cleanup names/tags without any relations DELETE fact_names FROM fact_names LEFT JOIN fact_values ON (fact_names.id = fact_name_id) WHERE fact_name_id IS NULL; DELETE puppet_tags FROM puppet_tags LEFT JOIN resource_tags ON (puppet_tags.id = puppet_tag_id) WHERE puppet_tag_id = NULL; DELETE param_names FROM param_names LEFT JOIN param_values ON (param_names.id = param_name_id) WHERE param_name_id = NULL; END; $$ DELIMITER ; # Resource view DROP VIEW IF EXISTS v_resources; CREATE VIEW v_resources AS SELECT hosts.name AS Hostname, resources.title AS Resource, resources.restype AS 'Resource Type', IF(resources.exported, 'Yes', 'No') AS Exported, param_names.name AS Parameter, puppet_tags.name as Tag, param_values.value AS Value FROM hosts, resources, resource_tags, puppet_tags, param_values, param_names WHERE hosts.id = resources.host_id AND resources.id = param_values.resource_id AND resources.id = resource_tags.resource_id AND resource_tags.puppet_tag_id = puppet_tags.id AND param_values.param_name_id = param_names.id; # Fact view DROP VIEW IF EXISTS v_facts; CREATE VIEW v_facts AS SELECT hosts.name as Hostname, fact_names.name as Fact, fact_values.value as Value FROM hosts, fact_values, fact_names WHERE hosts.id = fact_values.host_id AND fact_values.fact_name_id = fact_names.id;