Removing deleted tickets from the db

Brookes_Iris · July 4, 2005, 9:12pm

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

Harald_Kapper · July 5, 2005, 9:09am

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’;

hi
I would love to shrink our rt-database by deleting definitely unwanted stuff
from it - question here: is the above described delete-order confirmed by
someone from bestpractical?

thank you in advance
Harald Kapper, icq# 36178328 http://kapper.net
managing director, owner, loeblichgasse 6
chief executive officer 1090 vienna, .at
tel +43 1 3195500-0, fax +43 1 3195502, hk@kapper.net
--------------mark---------------
The following Statement is False.
The previous Statement is True.
- Welcome to my World -

ps. now running rt-3.4.1 on debian as I got completely stuck on trying to setup
this release on redhat enterprise-linux.

Filip_Jonckers · July 5, 2005, 9:53am

Great work Iris !!

can you confirm this works on version 3.4.2 ?

did someone already test this ?

this should be on the Wiki

FilipFrom: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Brookes,
Iris
Sent: maandag 4 juli 2005 23:12
To: rt-users@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.

Brookes_Iris · July 5, 2005, 2:28pm

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@Interconnect.be]
Sent: Tuesday, July 05, 2005 5:54 AM
To: Brookes, Iris; rt-users@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@lists.bestpractical.com

[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Brookes,
Iris
Sent: maandag 4 juli 2005 23:12
To: rt-users@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.

Filip_Jonckers · July 5, 2005, 3:29pm

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 ?)

Brookes_Iris · July 5, 2005, 3:17pm

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@mudnebr.com [mailto:Ahalya_Nathan@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@mudnebr.com

“Brookes, Iris” Iris.Brookes@tdsecurities.com
Sent by: rt-users-bounces@lists.bestpractical.com

07/05/2005 09:28 AM

To
“Filip Jonckers” fjonckers@Interconnect.be, rt-users@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@Interconnect.be]
Sent: Tuesday, July 05, 2005 5:54 AM
To: Brookes, Iris; rt-users@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@lists.bestpractical.com

[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Brookes,
Iris
Sent: maandag 4 juli 2005 23:12
To: rt-users@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