[rt-users] removing deleted tickets from the db

[Brookes, Iris]

The relationship is there in the code. 
 
For example, the relationship between the Attachments and Tickets tables is as follow:
 
Attachments.TransactionID <==> Transactions.ID <==> Transactions.ObjectID <==> Tickets.EffectiveID
 
This relationship can be found in the first delete statement. 
 
Iris

-----Original Message-----
From: Ahalya_Nathan at mudnebr.com [mailto:Ahalya_Nathan at mudnebr.com]
Sent: Tuesday, July 05, 2005 10:45 AM
To: Brookes, Iris
Subject: RE: [rt-users] removing deleted tickets from the db



Hi Iris, 

Could you give the relationships between the tickets .It will really help. 


Regards,
Ahalya Nathan
Senior Programmer / Analyst
Information Technology, Metropolitan Utilities District 
(402) 449-8218 phone
(402) 449-8131 fax
ahalya_nathan at mudnebr.com 



"Brookes, Iris" <Iris.Brookes at tdsecurities.com> 
Sent by: rt-users-bounces at lists.bestpractical.com 


07/05/2005 09:28 AM 


To
"Filip Jonckers" <fjonckers at Interconnect.be>, <rt-users at lists.bestpractical.com> 

cc

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.

_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT Wiki at http://wiki.bestpractical.com



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20050705/76df033b/attachment.htm>