[rt-users] removing deleted tickets from the db

[Brookes, Iris]

Hello:
 
I've encountered some difficulties with RTx-shredder, so I wrote the following SQL script which resolved my problem in removing deleted tickets and I would like to share it.
 
==================================================================================
-- this script will delete all tickets and corresponding objects/records where the status of the ticket is "deleted"
-- this script can be modified as needed
-- the tables affected in ticket(s) cleanup are:
--          attachments, cachedgroupmembers, groups, objectcustomfieldvalues, transactions and tickets
-- and should be executed in the same order
-- note the relationship between each table
--
--
delete attachments where transactionid in (select id from transactions where objecttype like 'RT::Ticket' and objectid in (select effectiveid from tickets where type like 'ticket' and status like 'deleted'));
--
delete cachedgroupmembers where groupid in (select id from groups where domain like 'RT::Ticket-Role' and instance in (select effectiveid from tickets where type like 'ticket' and status like 'deleted'));
--
delete groups where domain like 'RT::Ticket-Role' and instance in (select effectiveid from tickets where type like 'ticket' and status like 'deleted');
--
delete objectcustomfieldvalues where objecttype like 'RT::Ticket' and objectid in (select effectiveid from tickets where type like 'ticket' and status like 'deleted');
--
delete transactions where objecttype like 'RT::Ticket' and objectid in (select effectiveid from tickets where type like 'ticket' and status like 'deleted');
--
delete tickets where type like 'ticket' and status like 'deleted';

============================================================================
 
Iris
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20050704/ced7e78c/attachment.htm>