Here''s some happy-fun Perl to make your storeconfig/virtual resources day better. I probably should have written this in Ruby, but perl comes naturally :) #!/usr/bin/perl # # Perl DBI to remove all related entries from Puppet''s storeconfigs database. # Use when a host is removed from the Puppet network forcibly # Alternatives: run puppet on host to be removed with a blank manifest (node blah {}) # use DBI; my $dsn = ''DBI:mysql:database_server_here''; my $db_user = ''root''; my $db_pass = ''xxx''; my $dbh = DBI->connect($dsn, $db_user, $db_pass); my $query; if (($#ARGV + 1) ne 1) { print "Fail.\n"; print "Usage: $ARGV[0] host_name\n"; } else { my $result; print "Delete from hosts table. Affected rows: "; $result = $dbh->do(''delete from hosts where name = \''$1\''''); print "$result\n"; print "Delete from resources. Affected rows: "; $result = $dbh->do(''delete from resources where host_id not in (select id from hosts)''); print "$result\n"; print "Delete from fact_values, Affected rows: "; $result = $dbh->do(''delete from fact_values where host_id not in (select id from hosts)''); print "$result\n"; print "Delete from param_values, Affected rows: "; $result = $dbh->do(''delete from param_values where resource_id not in (select id from resources)''); print "$result\n"; print "Delete from resource_tags, Affected rows: "; $result = $dbh->do(''delete from resource_tags where resource_id not in (select id from resources)''); print "$result\n"; print "Delete from puppet_tags, Affected rows: "; $result = $dbh->do(''delete from puppet_tags where id not in (select id from resources)''); print "$result\n"; } Arjuna Christensen | Systems Engineer Maximum Internet Ltd 7a Parkhead Pl, Albany, North Shore, 0632 | PO Box 8006, Auckland, 1150, NZ DDI: + 64 9 913 9683 | Ph: +64 9 915 1825 | Fax:: +64 9 300 7227 arjuna.christensen@maxnet.co.nz| www.maxnet.co.nz ________________________________ Maxnet | mission critical internet ________________________________ This email (including any attachments) is confidential and intended only for the person to whom it is addressed. If you have received this email in error, please notify the sender immediately and erase all copies of this message and attachments. The views expressed in this email do not necessarily reflect those held by Maxnet.
Hate to beat a dead horse, but I''ll restate what I mentioned in a previous posting - this cleanup could be done with a single SQL DELETE statement if foreign keys were used. --- Arjuna Christensen <arjuna.christensen@maxnet.co.nz> wrote:> Here''s some happy-fun Perl to make your > storeconfig/virtual resources day better. I probably > should have written this in Ruby, but perl comes > naturally :) > > #!/usr/bin/perl > # > # Perl DBI to remove all related entries from > Puppet''s storeconfigs database. > # Use when a host is removed from the Puppet network > forcibly > # Alternatives: run puppet on host to be removed > with a blank manifest (node blah {}) > # > use DBI; > > my $dsn = ''DBI:mysql:database_server_here''; > my $db_user = ''root''; > my $db_pass = ''xxx''; > my $dbh = DBI->connect($dsn, $db_user, $db_pass); > my $query; > > > if (($#ARGV + 1) ne 1) { > print "Fail.\n"; > print "Usage: $ARGV[0] host_name\n"; > } > else { > my $result; > print "Delete from hosts table. Affected rows: "; > $result = $dbh->do(''delete from hosts where name > = \''$1\''''); > print "$result\n"; > print "Delete from resources. Affected rows: "; > $result = $dbh->do(''delete from resources where > host_id not in (select id from hosts)''); > print "$result\n"; > print "Delete from fact_values, Affected rows: "; > $result = $dbh->do(''delete from fact_values where > host_id not in (select id from hosts)''); > print "$result\n"; > print "Delete from param_values, Affected rows: > "; > $result = $dbh->do(''delete from param_values > where resource_id not in (select id from > resources)''); > print "$result\n"; > print "Delete from resource_tags, Affected rows: > "; > $result = $dbh->do(''delete from resource_tags > where resource_id not in (select id from > resources)''); > print "$result\n"; > print "Delete from puppet_tags, Affected rows: "; > $result = $dbh->do(''delete from puppet_tags where > id not in (select id from resources)''); > print "$result\n"; > } > > Arjuna Christensen | Systems Engineer > Maximum Internet Ltd > 7a Parkhead Pl, Albany, North Shore, 0632 | PO Box > 8006, Auckland, 1150, NZ > DDI: + 64 9 913 9683 | Ph: +64 9 915 1825 | Fax:: > +64 9 300 7227 > arjuna.christensen@maxnet.co.nz| www.maxnet.co.nz > ________________________________ > Maxnet | mission critical internet > ________________________________ > This email (including any attachments) is > confidential and intended only for the person to > whom it is addressed. > If you have received this email in error, please > notify the sender immediately and erase all copies > of this message > and attachments. The views expressed in this email > do not necessarily reflect those held by Maxnet. > > > _______________________________________________ > Puppet-users mailing list > Puppet-users@madstop.com >https://mail.madstop.com/mailman/listinfo/puppet-users>____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
On Feb 12, 2008, at 10:34 AM, John Philips wrote:> Hate to beat a dead horse, but I''ll restate what I > mentioned in a previous posting - this cleanup could > be done with a single SQL DELETE statement if foreign > keys were used.I don''t understand what you mean (which isn''t surprising; I''m basically in denial about SQL). -- It is absurd to divide people into good and bad. People are either charming or tedious. -- Oscar Wilde --------------------------------------------------------------------- Luke Kanies | http://reductivelabs.com | http://madstop.com
Here''s my recommended script (completely untested off the top of my head): http://pastie.caboo.se/150654 -Blake On Feb 12, 2008, at 8:34 AM, John Philips wrote:> Hate to beat a dead horse, but I''ll restate what I > mentioned in a previous posting - this cleanup could > be done with a single SQL DELETE statement if foreign > keys were used. > > --- Arjuna Christensen > <arjuna.christensen@maxnet.co.nz> wrote: > >> Here''s some happy-fun Perl to make your >> storeconfig/virtual resources day better. I probably >> should have written this in Ruby, but perl comes >> naturally :) >> >> #!/usr/bin/perl >> # >> # Perl DBI to remove all related entries from >> Puppet''s storeconfigs database. >> # Use when a host is removed from the Puppet network >> forcibly >> # Alternatives: run puppet on host to be removed >> with a blank manifest (node blah {}) >> # >> use DBI; >> >> my $dsn = ''DBI:mysql:database_server_here''; >> my $db_user = ''root''; >> my $db_pass = ''xxx''; >> my $dbh = DBI->connect($dsn, $db_user, $db_pass); >> my $query; >> >> >> if (($#ARGV + 1) ne 1) { >> print "Fail.\n"; >> print "Usage: $ARGV[0] host_name\n"; >> } >> else { >> my $result; >> print "Delete from hosts table. Affected rows: "; >> $result = $dbh->do(''delete from hosts where name >> = \''$1\''''); >> print "$result\n"; >> print "Delete from resources. Affected rows: "; >> $result = $dbh->do(''delete from resources where >> host_id not in (select id from hosts)''); >> print "$result\n"; >> print "Delete from fact_values, Affected rows: "; >> $result = $dbh->do(''delete from fact_values where >> host_id not in (select id from hosts)''); >> print "$result\n"; >> print "Delete from param_values, Affected rows: >> "; >> $result = $dbh->do(''delete from param_values >> where resource_id not in (select id from >> resources)''); >> print "$result\n"; >> print "Delete from resource_tags, Affected rows: >> "; >> $result = $dbh->do(''delete from resource_tags >> where resource_id not in (select id from >> resources)''); >> print "$result\n"; >> print "Delete from puppet_tags, Affected rows: "; >> $result = $dbh->do(''delete from puppet_tags where >> id not in (select id from resources)''); >> print "$result\n"; >> } >> >> Arjuna Christensen | Systems Engineer >> Maximum Internet Ltd >> 7a Parkhead Pl, Albany, North Shore, 0632 | PO Box >> 8006, Auckland, 1150, NZ >> DDI: + 64 9 913 9683 | Ph: +64 9 915 1825 | Fax:: >> +64 9 300 7227 >> arjuna.christensen@maxnet.co.nz| www.maxnet.co.nz >> ________________________________ >> Maxnet | mission critical internet >> ________________________________ >> This email (including any attachments) is >> confidential and intended only for the person to >> whom it is addressed. >> If you have received this email in error, please >> notify the sender immediately and erase all copies >> of this message >> and attachments. The views expressed in this email >> do not necessarily reflect those held by Maxnet. >> >> >> _______________________________________________ >> Puppet-users mailing list >> Puppet-users@madstop.com >> > https://mail.madstop.com/mailman/listinfo/puppet-users >> > > > > > ____________________________________________________________________________________ > Be a better friend, newshound, and > know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ > > _______________________________________________ > Puppet-users mailing list > Puppet-users@madstop.com > https://mail.madstop.com/mailman/listinfo/puppet-users
--- Luke Kanies <luke@madstop.com> wrote:> On Feb 12, 2008, at 10:34 AM, John Philips wrote: > > > Hate to beat a dead horse, but I''ll restate what I > > mentioned in a previous posting - this cleanup > could > > be done with a single SQL DELETE statement if > foreign > > keys were used. > > > I don''t understand what you mean (which isn''t > surprising; I''m > basically in denial about SQL).Luke, Foreign keys basically define relationships between tables [1]. There''s a CASCADE option that would be useful for puppet''s storedconfigs. Here''s one example - relationship between the hosts and fact_values tables. ALTER TABLE fact_values ADD CONSTRAINT fact_values-hosts_fkey FOREIGN KEY (host_id) REFERENCES hosts (id) ON DELETE CASCADE ON UPDATE CASCADE Now any changes/deletions to ''hosts'' would automatically update/delete appropriate rows in ''fact_values''. Similar foreign key relationships can be setup between the other tables to provide the behavior I originally harped about - using one DELETE statement to delete a host. I''d be happy to create/submit a patch for this but I''m not sure if puppetmaster creates the tables with static SQL or uses some ActiveRecord/Ruby magic. [1] http://en.wikipedia.org/wiki/Foreign_key ____________________________________________________________________________________ Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
As Blake mentioned below, think the best way to do it is with the following.. Some of his Arguments were wrong (dbpass != dbpassword).. Shame i''ve got no way to work out row count affected or anything. #!/usr/bin/env ruby require ''puppet/rails'' hostname = ARGV[1] Puppet[:dbadapter] = "mysql" Puppet[:dbserver] = "localhost" Puppet[:dbuser] = "user" Puppet[:dbpassword] = "pass" Puppet::Rails.connect @host = Puppet::Rails::Host.find_by_name "#{hostname}" @host.destroy unless @host.nil? Arjuna Christensen | Systems Engineer Maximum Internet Ltd 7a Parkhead Pl, Albany, North Shore, 0632 | PO Box 8006, Auckland, 1150, NZ DDI: + 64 9 913 9683 | Ph: +64 9 915 1825 | Fax:: +64 9 300 7227 arjuna.christensen@maxnet.co.nz| www.maxnet.co.nz ________________________________ Maxnet | mission critical internet ________________________________ This email (including any attachments) is confidential and intended only for the person to whom it is addressed. If you have received this email in error, please notify the sender immediately and erase all copies of this message and attachments. The views expressed in this email do not necessarily reflect those held by Maxnet. -----Original Message----- From: puppet-users-bounces@madstop.com [mailto:puppet-users-bounces@madstop.com] On Behalf Of Luke Kanies Sent: Wednesday, 13 February 2008 6:20 a.m. To: Puppet User Discussion Subject: Re: [Puppet-users] Storeconfigs purge Perl On Feb 12, 2008, at 10:34 AM, John Philips wrote:> Hate to beat a dead horse, but I''ll restate what I > mentioned in a previous posting - this cleanup could > be done with a single SQL DELETE statement if foreign > keys were used.I don''t understand what you mean (which isn''t surprising; I''m basically in denial about SQL). -- It is absurd to divide people into good and bad. People are either charming or tedious. -- Oscar Wilde --------------------------------------------------------------------- Luke Kanies | http://reductivelabs.com | http://madstop.com _______________________________________________ Puppet-users mailing list Puppet-users@madstop.com https://mail.madstop.com/mailman/listinfo/puppet-users
On Feb 12, 2008, at 2:12 PM, John Philips wrote:>> > > Luke, > > Foreign keys basically define relationships between > tables [1]. There''s a CASCADE option that would be > useful for puppet''s storedconfigs. Here''s one example > - relationship between the hosts and fact_values > tables. > > ALTER TABLE fact_values ADD CONSTRAINT > fact_values-hosts_fkey FOREIGN KEY (host_id) > REFERENCES hosts (id) ON DELETE CASCADE ON UPDATE > CASCADE > > Now any changes/deletions to ''hosts'' would > automatically update/delete appropriate rows in > ''fact_values''. > > Similar foreign key relationships can be setup between > the other tables to provide the behavior I originally > harped about - using one DELETE statement to delete a > host. > > I''d be happy to create/submit a patch for this but I''m > not sure if puppetmaster creates the tables with > static SQL or uses some ActiveRecord/Ruby magic.Ah; so it''s just a case of telling the db which foreign keys we''re already using. Meaning, we should be using the foreign key capabilities of the db, rather than just "we should be using foreign keys", which we already are doing. The db schema is handled by ActiveRecord: http://reductivelabs.com/trac/puppet/browser/lib/puppet/rails/database/schema.rb Patches obviously accepted, if you can enable the foreign_key stuff from there. -- It is said that power corrupts, but actually it''s more true that power attracts the corruptible. The sane are usually attracted by other things than power. -- David Brin --------------------------------------------------------------------- Luke Kanies | http://reductivelabs.com | http://madstop.com
On Feb 12, 2008, at 12:23 PM, Luke Kanies wrote:> On Feb 12, 2008, at 2:12 PM, John Philips wrote: >>> >> >> Luke, >> >> Foreign keys basically define relationships between >> tables [1]. There''s a CASCADE option that would be >> useful for puppet''s storedconfigs. Here''s one example >> - relationship between the hosts and fact_values >> tables. >> >> ALTER TABLE fact_values ADD CONSTRAINT >> fact_values-hosts_fkey FOREIGN KEY (host_id) >> REFERENCES hosts (id) ON DELETE CASCADE ON UPDATE >> CASCADE >> >> Now any changes/deletions to ''hosts'' would >> automatically update/delete appropriate rows in >> ''fact_values''. >> >> Similar foreign key relationships can be setup between >> the other tables to provide the behavior I originally >> harped about - using one DELETE statement to delete a >> host. >> >> I''d be happy to create/submit a patch for this but I''m >> not sure if puppetmaster creates the tables with >> static SQL or uses some ActiveRecord/Ruby magic. > > Ah; so it''s just a case of telling the db which foreign keys we''re > already using. > > Meaning, we should be using the foreign key capabilities of the db, > rather than just "we should be using foreign keys", which we already > are doing. > > The db schema is handled by ActiveRecord: > > http://reductivelabs.com/trac/puppet/browser/lib/puppet/rails/database/schema.rb > > Patches obviously accepted, if you can enable the foreign_key stuff > from there.The problem is, ActiveRecord doesn''t support foreign keys be default, so an addon is required. I originally included it in the schema but when the schema was redone we dumped it to simplify things and reduce external dependencies. The plugin I was using is: http://www.redhillonrails.org/#foreign_key_migrations -Blake
--- Blake Barnett <shadoi@gmail.com> wrote:> The problem is, ActiveRecord doesn''t support foreign > keys be default, > so an addon is required. I originally included it > in the schema but > when the schema was redone we dumped it to simplify > things and reduce > external dependencies. The plugin I was using is: >http://www.redhillonrails.org/#foreign_key_migrations Blake, that makes perfect sense. I now have more respect for Puppet and less for ActiveRecord :-) Hopefully ActiveRecord will provide foreign key support without a plugin in a future version. ____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Arjuna Christensen schrieb:> As Blake mentioned below, think the best way to do it is with the following.. Some of his Arguments were wrong (dbpass != dbpassword).. Shame i''ve got no way to work out row count affected or anything. > > #!/usr/bin/env ruby > > require ''puppet/rails'' > > hostname = ARGV[1] > > Puppet[:dbadapter] = "mysql" > Puppet[:dbserver] = "localhost" > Puppet[:dbuser] = "user" > Puppet[:dbpassword] = "pass" > Puppet::Rails.connect > > @host = Puppet::Rails::Host.find_by_name "#{hostname}" > @host.destroy unless @host.nil?Yeah, that looks _much_ better than my SQL stuff. Could this be _pretty please_ be included as "puppetclean" or something like that in the distribution (perhaps just in contrib/)? Regards, DavidS