[rt-users] The good old purge script!

[Vivek Khera]

>>>>> "TA" == Tony Aiuto <tony at 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! ;-)