<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1491" name=GENERATOR></HEAD>
<BODY>
<DIV>
<DIV><SPAN class=286030015-05072005><FONT face=Arial color=#0000ff size=2>The
relationship is there in the code. </FONT></SPAN></DIV>
<DIV><SPAN class=286030015-05072005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=286030015-05072005><FONT face=Arial color=#0000ff size=2>For
example, the relationship between the <FONT color=#800000><STRONG>Attachments
and Tickets</STRONG></FONT> tables is as follow:</FONT></SPAN></DIV>
<DIV><SPAN class=286030015-05072005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=286030015-05072005><FONT face=Arial color=#0000ff size=2><FONT
color=#800000><STRONG>Attachments</STRONG></FONT>.TransactionID <==> <FONT
color=#800000><STRONG>Transactions</STRONG></FONT>.ID <==> <FONT
color=#800000><STRONG>Transactions</STRONG></FONT>.ObjectID <==> <FONT
color=#800000><STRONG>Tickets</STRONG></FONT>.EffectiveID</FONT></SPAN></DIV>
<DIV><SPAN class=286030015-05072005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=286030015-05072005><FONT face=Arial color=#0000ff size=2>This
relationship can be found in the first delete statement. </FONT></SPAN></DIV>
<DIV><SPAN class=286030015-05072005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=286030015-05072005><FONT face=Arial color=#0000ff
size=2>Iris</FONT></SPAN></DIV></DIV>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Ahalya_Nathan@mudnebr.com
[mailto:Ahalya_Nathan@mudnebr.com]<BR><B>Sent:</B> Tuesday, July 05, 2005
10:45 AM<BR><B>To:</B> Brookes, Iris<BR><B>Subject:</B> RE: [rt-users]
removing deleted tickets from the db<BR><BR></FONT></DIV><BR><FONT
face=sans-serif size=2>Hi Iris,</FONT> <BR><BR><FONT face=sans-serif
size=2>Could you give the relationships between the tickets .It will really
help.</FONT> <BR><FONT face=sans-serif size=2><BR><BR>Regards,<BR>Ahalya
Nathan<BR>Senior Programmer / Analyst<BR>Information Technology, Metropolitan
Utilities District <BR>(402) 449-8218 phone<BR>(402) 449-8131
fax<BR>ahalya_nathan@mudnebr.com</FONT> <BR><BR><BR>
<TABLE width="100%">
<TBODY>
<TR vAlign=top>
<TD width="40%"><FONT face=sans-serif size=1><B>"Brookes, Iris"
<Iris.Brookes@tdsecurities.com></B> </FONT><BR><FONT
face=sans-serif size=1>Sent by:
rt-users-bounces@lists.bestpractical.com</FONT>
<P><FONT face=sans-serif size=1>07/05/2005 09:28 AM</FONT> </P>
<TD width="59%">
<TABLE width="100%">
<TBODY>
<TR vAlign=top>
<TD>
<DIV align=right><FONT face=sans-serif size=1>To</FONT></DIV>
<TD><FONT face=sans-serif size=1>"Filip Jonckers"
<fjonckers@Interconnect.be>,
<rt-users@lists.bestpractical.com></FONT>
<TR vAlign=top>
<TD>
<DIV align=right><FONT face=sans-serif size=1>cc</FONT></DIV>
<TD>
<TR vAlign=top>
<TD>
<DIV align=right><FONT face=sans-serif size=1>Subject</FONT></DIV>
<TD><FONT face=sans-serif size=1>RE: [rt-users] removing deleted
tickets from the db</FONT></TR></TBODY></TABLE><BR>
<TABLE>
<TBODY>
<TR vAlign=top>
<TD>
<TD></TR></TBODY></TABLE><BR></TR></TBODY></TABLE><BR><BR><BR><FONT
size=2><TT>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. <BR><BR>I don't know if it would be beneficial to anyone, but I do
know it is to me.<BR><BR>Iris<BR><BR>-----Original Message-----<BR>From: Filip
Jonckers [mailto:fjonckers@Interconnect.be]<BR>Sent: Tuesday, July 05, 2005
5:54 AM<BR>To: Brookes, Iris; rt-users@lists.bestpractical.com<BR>Subject: RE:
[rt-users] removing deleted tickets from the db<BR><BR><BR>Great work Iris
!!<BR><BR>can you confirm this works on version 3.4.2 ?<BR><BR>did someone
already test this ?<BR><BR>this should be on the Wiki
;-)<BR><BR>Filip<BR><BR><BR><BR><BR>________________________________<BR><BR>
From:
rt-users-bounces@lists.bestpractical.com<BR>[mailto:rt-users-bounces@lists.bestpractical.com]
On Behalf Of Brookes,<BR>Iris<BR>
Sent: maandag 4 juli 2005 23:12<BR>
To: rt-users@lists.bestpractical.com<BR>
Subject: [rt-users] removing
deleted tickets from the db<BR>
<BR>
<BR> Hello:<BR>
<BR>
I've encountered some difficulties
with RTx-shredder, so I wrote<BR>the following SQL script which resolved my
problem in removing deleted<BR>tickets and I would like to share it.<BR>
<BR>
<BR>========================================================================<BR>==========<BR>
-- this script will delete
all tickets and corresponding<BR>objects/records where the status of the
ticket is "deleted"<BR>
-- this script can be modified as needed<BR>
-- the tables affected in ticket(s) cleanup
are:<BR> --
attachments, cachedgroupmembers,
groups,<BR>objectcustomfieldvalues, transactions and tickets<BR>
-- and should be executed in the
same order<BR> -- note
the relationship between each table<BR>
--<BR>
--<BR> delete
attachments where transactionid in (select id from<BR>transactions where
objecttype like 'RT::Ticket' and objectid in (select<BR>effectiveid from
tickets where type like 'ticket' and status like<BR>'deleted'));<BR>
--<BR>
delete cachedgroupmembers where groupid in
(select id from<BR>groups where domain like 'RT::Ticket-Role' and instance in
(select<BR>effectiveid from tickets where type like 'ticket' and status
like<BR>'deleted'));<BR>
--<BR> delete
groups where domain like 'RT::Ticket-Role' and instance<BR>in (select
effectiveid from tickets where type like 'ticket' and status<BR>like
'deleted');<BR>
--<BR> delete
objectcustomfieldvalues where objecttype like<BR>'RT::Ticket' and objectid in
(select effectiveid from tickets where type<BR>like 'ticket' and status like
'deleted');<BR>
--<BR> delete
transactions where objecttype like 'RT::Ticket' and<BR>objectid in (select
effectiveid from tickets where type like 'ticket'<BR>and status like
'deleted');<BR>
--<BR> delete tickets
where type like 'ticket' and status like<BR>'deleted';<BR>
<BR>
<BR>========================================================================<BR>====<BR>
<BR>
Iris<BR><BR><BR>CONFIDENTIALITY
NOTICE<BR>-----------------------------------------------<BR>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.<BR><BR>_______________________________________________<BR>http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users<BR><BR>Be
sure to check out the RT Wiki at
http://wiki.bestpractical.com<BR></TT></FONT><BR></BLOCKQUOTE></BODY></HTML>