[Rt-devel] Question about the data model!

[Sven Sternberger]

Jesse Vincent wrote:
> 
> 
>>purge_script.pl by Steve Poirier 
..
> It sounds like this script might not handle everything it needs to. 
> Where does it come from?

I found it in the rt-user mailinglist but at the moment the mailinglist
search engine seems to be not working, so I couldn't sent you the exact
location. Instead I add the scripts which I used as
attachment.

purge_script.pl (the old one I used for a long time on rt2)
purgedead.pl (the new on I used 2 oder 3 times on the rt3)

best regards!
-------------- next part --------------
#!/opt/perl/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, "/opt/rt2/lib/"; do "/opt/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" sven.sternberger at desy.de' 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;



-------------- next part --------------
#!/opt/perl/bin/perl
#
# Steve Poirier - <A HREF="mailto:steve at inet-technologies.com">steve at inet-technologies.com</A>
# edited by Tim Allwine <A HREF="mailto:tallwine at oreilly.com">tallwine at oreilly.com</A>
# to use with RT2 and MySQL
# edited by Tony Aiuto <tony at ics.com> to deal with Links
# edited by Jonas Lincoln <lincoln at unit.liu.se> to work with RT3 and mysql
# edited by Dirk Pape <pape-rt at inf.fu-berlin.de> to cope with Role-Groups (RT 3)

use strict;
my $date = shift;
my $ticketnbr = 0;

if ( ! $date ) { $date=`/bin/date -I -d "3 months ago"`; }
print "$date\n";

# Replace this with your RT_LIB_PATH
#use lib "/export/rt3/lib";
use lib "/opt/rt2/lib";
# Replace this with your RT_ETC_PATH
#use lib "/export/rt3/etc";
use lib "/opt/rt2/etc";

use RT::Interface::CLI  qw(CleanEnv loc
			   GetCurrentUser GetMessageContent);
use RT::Group;
use RT;

#Clean out all the nasties from the environment
CleanEnv();

#Load etc/config.pm and drop privs
RT::LoadConfig();

#Connect to the database and get RT::SystemUser and RT::Nobody loaded
RT::Init();

(my $x=RT::Handle->new)->Connect;

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

my $sql = "SELECT id FROM Tickets WHERE Status=? AND LastUpdated < \'$date\'";
my $sth = $dbh->prepare($sql);
$sth->execute('deleted');

# 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 $stobjects = $dbh->prepare( 'DELETE FROM TicketCustomFieldValues WHERE Ticket=?');
my $stlinks1  = $dbh->prepare( 'DELETE FROM Links WHERE LocalBase=?');
my $stlinks2  = $dbh->prepare( 'DELETE FROM Links WHERE LocalTarget=?');
my $d_groups  = $dbh->prepare( 'SELECT id FROM Groups WHERE Domain="RT::Ticket-Role" AND Instance=?');
my $grpmbrs   = $dbh->prepare( 'DELETE FROM GroupMembers WHERE GroupId=?');
my $groups    = $dbh->prepare( 'DELETE FROM Groups WHERE Domain="RT::Ticket-Role" AND Instance=?');
my $stticket  = $dbh->prepare( 'DELETE FROM Tickets WHERE id=?');

while ( my ($ticketid) = $sth->fetchrow_array() )  {
    print "\nticket $ticketid is dead and last updated before $date. 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 TicketRole groups for this ticket
    print "TicketRole groupmembers. ";
    $d_groups->execute($ticketid);
    while ( my ($groupid) = $d_groups->fetchrow_array() )  {
	  $grpmbrs->execute($groupid);
    }
    print "TicketRole groups. ";
    $groups->execute($ticketid);

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

# delete Ticket from the Links table
    print "Links1. ";
    $stlinks1->execute($ticketid);
    print "Links2. ";
    $stlinks2->execute($ticketid);

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

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

exit;