Skip to content

Instantly share code, notes, and snippets.

@ake-persson
Created November 9, 2012 14:59
Show Gist options
  • Save ake-persson/4046173 to your computer and use it in GitHub Desktop.
Save ake-persson/4046173 to your computer and use it in GitHub Desktop.
Puppet MySQL host cleanup trigger and views for facts and resources
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;
@ake-persson
Copy link
Author

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 = '';

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment