The good old purge script!

RT Users
1

Hi!

I know you think this one of the old RTFM questions, but
I can´t find the purge script to delete old tickets anywhere.
In the ML archiv it is often mentioned but the only link I found
seems to be dead.

Who can send me a link or the script to purge “dead” tickets.

bye

Sven

Sven Sternberger Tel.: +49 (0) 40 8998 4397
Desy Email: sven.sternberger@desy.de
Notkestr. 85
D-22607 Hamburg

2

Sven Sternberger wrote:

Who can send me a link or the script to purge “dead” tickets.

http://lists.fsck.com/pipermail/rt-users/2002-August/009595.html
Phil Homewood, Systems Janitor, www.SnapGear.com
pdh@snapgear.com Ph: +61 7 3435 2810 Fx: +61 7 3891 3630
SnapGear - Custom Embedded Solutions and Security Appliances

3

There was a link to a purge dead tickets script posted last
month. It didn’t deal with the Links record (as I found
out the hard way). Attached is a corrected one. Remeber
to edit the paths and the email address to fit your installation)

-Tony Aiuto

purgedead (2.23 KB)

4

“TA” == Tony Aiuto tony@ics.com writes:

TA> There was a link to a purge dead tickets script posted last
TA> month. It didn’t deal with the Links record (as I found
TA> out the hard way). Attached is a corrected one. Remeber
TA> to edit the paths and the email address to fit your installation)

Wow… good catch! For anyone who’s already purged old tickets and
needs to find links that reference those purged tickets, give this a
try:

select id from links left join tickets on links.localtarget=tickets.id
where tickets.id is null;

and

select id from links left join tickets on links.localbase=tickets.id
where tickets.id is null;

then delete the id’s returned from the links table. I had exactly
one, luckily enough for me…

Anyhow, I just added these referential constraints so it won’t happen
again:

ALTER TABLE Links ADD CONSTRAINT linksfk1 FOREIGN KEY (LocalTarget) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Links ADD CONSTRAINT linksfk2 FOREIGN KEY (LocalBase) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;

I guess now we know why it is good to have referential constraints in
your database… less bugs in your programs! :wink: