#!/usr/bin/env perl
use 5.008003;
use strict;
### after: use lib qw(@RT_LIB_PATH@);
use lib qw(/opt/rt3/local/lib /opt/rt3/lib);
use RT;
RT::LoadConfig();
RT::Init();
print "Are you sure want to delete forever tickets with status 'deleted'? [y/N]: ";
exit unless <> =~ /^y(es)?$/i;
my $dbh = $RT::Handle->dbh;
$dbh->{'RaiseError'} = 1;
$dbh->{'PrintError'} = 1;
exit unless int $dbh->do('DELETE FROM Tickets WHERE Status = ?', undef, 'deleted');
$dbh->do(
'DELETE t.* FROM Tickets t LEFT JOIN Tickets c
ON c.id = t.EffectiveId WHERE c.id IS NULL'
);
$dbh->do(
'DELETE t.* FROM Groups t LEFT JOIN Tickets c
ON c.id = t.Instance WHERE t.Domain = ? AND c.id IS NULL',
undef, 'RT::Ticket-Role',
);
$dbh->do(
'DELETE t.* FROM Principals t LEFT JOIN Groups c
ON c.id = t.id WHERE t.PrincipalType = ? AND c.id IS NULL',
undef, 'Group'
);
$dbh->do(
'DELETE t.* FROM GroupMembers t LEFT JOIN Groups c
ON c.id = t.GroupId WHERE c.id IS NULL'
);
$dbh->do(
'DELETE t.* FROM CachedGroupMembers t LEFT JOIN Groups c
ON c.id = t.GroupId WHERE c.id IS NULL'
);
while(1) {
last unless int $dbh->do(
'DELETE t.* FROM CachedGroupMembers t LEFT JOIN CachedGroupMembers c
ON c.id = t.Via WHERE t.Via != t.id AND c.id IS NULL'
);
}
foreach my $t (qw(Ticket Group)) {
$dbh->do(
"DELETE t.* FROM Transactions t LEFT JOIN ${t}s c
ON c.id = t.ObjectId WHERE t.ObjectType = ? AND c.id IS NULL",
undef, 'RT::'.$t,
);
}
$dbh->do(
'DELETE t.* FROM Attachments t LEFT JOIN Transactions c
ON c.id = t.TransactionId WHERE c.id IS NULL'
);
foreach my $t (qw(Ticket Group)) {
$dbh->do(
"DELETE t.* FROM ObjectCustomFieldValues t LEFT JOIN ${t}s c
ON c.id = t.ObjectId WHERE t.ObjectType = ? AND c.id IS NULL",
undef, 'RT::'.$t,
);
}
my %type_and_table = (
ObjectCustomFieldValues => 'RT::ObjectCustomFieldValue',
Attachments => 'RT::Attachment',
Transactions => 'RT::Transaction',
Tickets => 'RT::Ticket',
Groups => 'RT::Group',
CachedGroupMembers => 'RT::CachedGroupMember',
GroupMembers => 'RT::GroupMember',
Principals => 'RT::Principal',
);
while ( my ($table, $type) = each %type_and_table ) {
$dbh->do(
"DELETE t.* FROM Attributes t LEFT JOIN $table c
ON c.id = t.ObjectId
WHERE t.ObjectType = ? AND c.id IS NULL",
undef, $type
);
}