#!/usr/bin/env perl
use
5.008003;
use
lib
qw(/opt/rt3/local/lib /opt/rt3/lib)
;
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
);
}