I saw them. But If I drop the database, wouldn’t it remove all my settings for each queues/users/script settings? I only want to remove all tickets and nothing more.
-JonathanFrom: Josiah Ritchie [mailto:jritchie@bible.edu]
Sent: Tue 2/17/2004 10:35 AM
To: Jonathan Chen; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] removing deleted tickets
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@inf.fu-berlin.de
Subject: Re: [rt-users] removing deleted tickets
To: matthew zeier mrz@intelenet.net, rt-users@lists.fsck.com
Message-ID: 2147483647.1077008981@eremix
Content-Type: text/plain; charset=“us-ascii”
Hallo matthew,
–Am Montag, 16. Februar 2004 10:41 Uhr -0800 schrieb matthew zeier
mrz@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
mailto:steve@inet-technologies.com ">steve@inet-technologies.com
mailto:tallwine@oreilly.com ">tallwine@oreilly.com
to use with RT2 and MySQL
edited by Tony Aiuto tony@ics.com to deal with Links
edited by Jonas Lincoln lincoln@unit.liu.se to work with RT3 and
mysql
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
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