[rt-users] removing deleted tickets

[Josiah Ritchie]

See the replies to message "Dropping the RT database". I think that's what you
want.

JSR/

Jonathan Chen scripted ::

>This script is interesting.   I'm not a perl programmer but I'm
>wondering if it can be modified to delete ALL tickets from ALL queues so
>RT would start assigning tickets with #1 again.  If it is, how would I
>go about it?  If there is a script like this already available, please
>lead me to one.   
> 
>Thanks!
> 
>-Jonathan
>Message: 5
>Date: Tue, 17 Feb 2004 09:09:41 +0100
>From: Dirk Pape <pape-rt at inf.fu-berlin.de>
>Subject: Re: [rt-users] removing deleted tickets
>To: matthew zeier <mrz at intelenet.net>, rt-users at lists.fsck.com
>Message-ID: <2147483647.1077008981 at eremix>
>Content-Type: text/plain; charset="us-ascii"
>
>Hallo matthew,
>
>--Am Montag, 16. Februar 2004 10:41 Uhr -0800 schrieb matthew zeier
><mrz at intelenet.net>:
>
>> Anyone have a method (script) to remove deleted tickets and all it's
>> components from the db?
>
>mine is attached. It works for RT 3 upto 3.0.9. You need to change the
>first line to the pah of your perl binary.
>
>Dirk.
>-------------- next part --------------
>#!/export/perl/bin/perl
>#
># Steve Poirier - <A HREF="mailto:steve at inet-technologies.com
><mailto:steve at inet-technologies.com> ">steve at inet-technologies.com</A>
># edited by Tim Allwine <A HREF="mailto:tallwine at oreilly.com
><mailto:tallwine at oreilly.com> ">tallwine at oreilly.com</A>
># to use with RT2 and MySQL
># edited by Tony Aiuto <tony at ics.com> to deal with Links
># edited by Jonas Lincoln <lincoln at unit.liu.se> to work with RT3 and
>mysql
># edited by Dirk Pape <pape-rt at inf.fu-berlin.de> to cope with
>Role-Groups (RT 3)
>
>use strict;
>my $date = shift;
>my $ticketnbr = 0;
>
>if ( ! $date ) { $date=`/bin/date -I -d "3 months ago"`; }
>print "$date\n";
>
># Replace this with your RT_LIB_PATH
>use lib "/export/rt3/lib";
># Replace this with your RT_ETC_PATH
>use lib "/export/rt3/etc";
>
>use RT::Interface::CLI  qw(CleanEnv loc
>                           GetCurrentUser GetMessageContent);
>use RT::Group;
>use RT;
>
>#Clean out all the nasties from the environment
>CleanEnv();
>
>#Load etc/config.pm and drop privs
>RT::LoadConfig();
>
>#Connect to the database and get RT::SystemUser and RT::Nobody loaded
>RT::Init();
>
>(my $x=RT::Handle->new)->Connect;
>
>my $dbh = $x->dbh() or die "Couldn't connect to RT database";
>
>my $sql = "SELECT id FROM Tickets WHERE Status=? AND LastUpdated <
>\'$date\'";
>my $sth = $dbh->prepare($sql);
>$sth->execute('deleted');
>
># proceed with deletion
>
>my $d_sth     = $dbh->prepare('SELECT id FROM Transactions where
>Ticket=?');
>my $stid      = $dbh->prepare('DELETE FROM Attachments WHERE
>TransactionId=?');
>my $sttrans   = $dbh->prepare( 'DELETE FROM Transactions WHERE
>Ticket=?');
>my $stobjects = $dbh->prepare( 'DELETE FROM TicketCustomFieldValues
>WHERE Ticket=?');
>my $stlinks1  = $dbh->prepare( 'DELETE FROM Links WHERE LocalBase=?');
>my $stlinks2  = $dbh->prepare( 'DELETE FROM Links WHERE LocalTarget=?');
>my $d_groups  = $dbh->prepare( 'SELECT id FROM Groups WHERE
>Domain="RT::Ticket-Role" AND Instance=?');
>my $grpmbrs   = $dbh->prepare( 'DELETE FROM GroupMembers WHERE
>GroupId=?');
>my $groups    = $dbh->prepare( 'DELETE FROM Groups WHERE
>Domain="RT::Ticket-Role" AND Instance=?');
>my $stticket  = $dbh->prepare( 'DELETE FROM Tickets WHERE id=?');
>
>while ( my ($ticketid) = $sth->fetchrow_array() )  {
>    print "\nticket $ticketid is dead and last updated before $date.
>Deleting:  ";
>    $d_sth->execute($ticketid);
>
># delete Attachments for this ticket
>    print "Attachments. ";
>    while ( my ($transid) = $d_sth->fetchrow_array() )  {
>          $stid->execute($transid);
>    }
>
># delete Ticket in Transactions table
>    print "Transactions. ";
>    $sttrans->execute($ticketid);
>
># delete TicketRole groups for this ticket
>    print "TicketRole groupmembers. ";
>    $d_groups->execute($ticketid);
>    while ( my ($groupid) = $d_groups->fetchrow_array() )  {
>          $grpmbrs->execute($groupid);
>    }
>    print "TicketRole groups. ";
>    $groups->execute($ticketid);
>
># delete Ticket in ObjectKeywords table
>    print "ObjectKeywords. ";
>    $stobjects->execute($ticketid);
>
># delete Ticket from the Links table
>    print "Links1. ";
>    $stlinks1->execute($ticketid);
>    print "Links2. ";
>    $stlinks2->execute($ticketid);
>
># delete Ticket from the Tickets table
>    print "Ticket. ";
>    $stticket->execute($ticketid);
>
>    $ticketnbr++;
>}
>print "\nDone. Deleted $ticketnbr tickets.\n\n";
>
>exit;
>
>------------------------------
>
>
> 
>


-- 
System Administrator
Washington Bible College/Capital Bible Seminary
http://www.bible.edu

Sound words, I know, Timothy is to use,
And old wives' fables he is to refuse
But yet grave Paul him nowhere doth forbid
The use of parables, in which lay hid
That gold, those pearls, and precious stones that were
Worth digging for, and that with greatest care.
-- From "The Author's Apology For His Book"
   "The Pilgrim's Progress" by John Bunyan