[rt-users] Deleting dead tickets (especially attachments)

[Simon Cozens]

Scott A. McIntyre:
> About a year ago there was a reference on the list to a perl script for
> removing data from the database for dead tickets -- unfortunately, it
> looks like the web site where that was hosted, and perhaps the company
> itself, no longer exist.
> 
> Does anyone have a copy? 

Yep. It's attached.

-- 
"Irrigation of the land with seawater desalinated by fusion power is ancient.
It's called 'rain'."
-- Michael McClary, in alt.fusion
-------------- next part --------------
#!/usr/bin/perl
#
# Steve Poirier - steve at inet-technologies.com
# edited by Tim Allwine tallwine at oreilly.com
# to use with RT2 and MySQL

use strict;
my $ticketnbr = 0;

BEGIN { push @INC, "/usr/local/share/rt2/lib/"; do "/usr/local/share/rt2/etc/config.pm"; }
use RT;
(my $x=RT::Handle->new)->Connect;

my $dbh = $x->dbh() or die "Couldn't connect to RT database";

my $sql = q{SELECT id FROM Tickets where Status=?};
my $sth = $dbh->prepare($sql);
$sth->execute('dead');

# proceed with deletion

my $d_sth = $dbh->prepare('SELECT id FROM Transactions where Ticket=?');
my $stid = $dbh->prepare('DELETE FROM Attachments WHERE TransactionId=?');
my $sttrans = $dbh->prepare( 'DELETE FROM Transactions WHERE Ticket=?');
my $stwatch = $dbh->prepare( 'DELETE FROM Watchers WHERE Value=?');
my $stobjects =$dbh->prepare( 'DELETE FROM ObjectKeywords WHERE ObjectId=?');
my $stticket = $dbh->prepare( 'DELETE FROM Tickets WHERE id=?');

open MAIL, '|mail -s "dead tickets" database at rt.oucs.ox.ac.uk' or die $!;

print MAIL $sth->rows()," dead tickets were removed.\n\n";

while ( my ($ticketid) = $sth->fetchrow_array() )  {
    #print "\nticket $ticketid is dead. Deleting:  ";
    $d_sth->execute($ticketid);

# delete Attachments for this ticket
    #print "Attachments. ";
    while ( my ($transid) = $d_sth->fetchrow_array() )  {
	$stid->execute($transid);
    }

# delete Ticket in Transactions table
    #print "Transactions. ";
    $sttrans->execute($ticketid);

# delete Ticket in ticket in Watchers table
    #print "Watchers. ";
    $stwatch->execute($ticketid);

# delete Ticket in ObjectKeywords table
    #print "ObjectKeywords. ";
    $stobjects->execute($ticketid);

# delete Ticket from the Tickets table
    #print "Ticket. ";
    $stticket->execute($ticketid);

    $ticketnbr++;
}
#print "\nDone. Deleted $ticketnbr tickets.\n\n";

exit;