[rt-users] removing deleted tickets from the db

[Filip Jonckers]

Great work Iris !!
 
can you confirm this works on version 3.4.2 ?

did someone already test this ?

this should be on the Wiki ;-)

Filip

 


________________________________

	From: rt-users-bounces at lists.bestpractical.com
[mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Brookes,
Iris
	Sent: maandag 4 juli 2005 23:12
	To: rt-users at lists.bestpractical.com
	Subject: [rt-users] removing deleted tickets from the db
	
	
	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


CONFIDENTIALITY NOTICE
-----------------------------------------------
This E-mail message and any documents which accompany it are intended only for the use of the individual or entity to which addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law.  If the reader is not the intended recipient, any disclosure, distribution or other use of this E-mail message is prohibited.  If you have received this E-mail message in error, please delete and notify the sender immediately.  Thank you.