[rt-users] removing deleted tickets from the db

[Filip Jonckers]

it sure is Iris ! :-)

will be trying the script in a few days ..

I guess Jesse is the only person who can give his "blessing" on the
completeness of the sql script :-)
(maybe add it to the distribution ?)
 

> -----Original Message-----
> From: Brookes, Iris [mailto:Iris.Brookes at tdsecurities.com] 
> Sent: dinsdag 5 juli 2005 16:29
> To: Filip Jonckers; rt-users at lists.bestpractical.com
> Subject: RE: [rt-users] removing deleted tickets from the db
> 
> I'm using version 3.4.1 and I'm new to RT. Since installing 
> RT 2 months ago, I've only been playing  with it and I've 
> encountered problems trying to cleanup the db. I broke the db 
> a few times and had to drop and rebuild it; so I think I've 
> worked out the relationships between the tables affected. 
> 
> I don't know if it would be beneficial to anyone, but I do 
> know it is to me.
> 
> Iris
> 
> -----Original Message-----
> From: Filip Jonckers [mailto:fjonckers at Interconnect.be]
> Sent: Tuesday, July 05, 2005 5:54 AM
> To: Brookes, Iris; rt-users at lists.bestpractical.com
> Subject: RE: [rt-users] removing deleted tickets from the db
> 
> 
> 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.
> 
>