Puppet MySQL host cleanup trigger and views for facts and resources

gistfile1.sql

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;

This will allow you to cleanup host related facts, resources etc. using one command: DELETE from hosts WHERE hostname = '';

It will also allow you to do quicker lookup of facts with the views like: SELECT * FROM v_facts WHERE hostname = '';