On Fri, May 07, 2010 at 09:54:38PM +0200, Emmanuel Lacour wrote:
> On Fri, May 07, 2010 at 11:41:57AM -0400, Edna Wigderson wrote:
> > Hello,
> >
> > We need to move an RT queue from one production server to another due to
> > internal reorganization. Both servers run RT 3.8.x . So a mysql dump &
> > restore of the tickets in the queue will be problematic as it might
> > cause ticket # conflicts.
> >
> > Is there any easy/clean way of doing this?
> >
>
> I'm just working on this and have to finish next week ;)
>
> I'm writing a script using sql on source DB and RT api on target DB.
>
> Of course, tickets Id will be changed, but I plan to record the old
> ticket in a CF. for reference.
>
As this may be useful for someone else, here is the script I made to
copy some queues from an RT 3.4.5 to an RT 3.8.8 with MySQL (should work
with most src RT >= 3.4.0 and target RT 3.8.x).
It is intended to be run on the target (RT 3.8.x). It will do some
direct sql to the src RT and create tickets in the target RT using the
RT API.
There is some limitations written at top of the script.
It's a one shot script that is in the state of "it worked here, may be it
can be usefull for you".
of course, there is no warranty that it may not destroy all your datas
...
#!/usr/bin/perl -w
# Copy tickets from one RT to another RT, for one or more named queue(s)
# Known bugs
# - In ticket transactions for queue change, we don't try to find the source or
# target queue
# - We temporarily set Owner right for all users to this queue so we do not have
# right problem to set ticket owner, this right is removed at the end
# - We check CustomFields by Name, assuming there is no duplicate names accross
# them (not often true!)
# - Links that have target not in the tickets we import cannot work, so we
# mangle the URI to point to old RT
use strict;
use 5.010;
use DBI;
use Getopt::Long;
use lib "/opt/rt3/lib";
use RT;
use RT::Interface::CLI qw( CleanEnv );
# Don't buffer STDOUT
$| = 1;
CleanEnv();
RT::LoadConfig();
RT::Init();
# Command line arguments
my @queues;
my $ok = GetOptions(
'queue|q=s@' => \...@queues
);
unless ( $ok && @queues && scalar @queues ) {
say "Usage: sync-tickets.pl --queue=QUEUE1 --queue=QUEUE2 ...";
exit(1);
}
my $start_time = time;
# Log file
my $log_file = "/var/tmp/sync-tickets.$$.log";
open (LOG, "> $log_file") or die "Can't open log file $log_file: $!\n";
say "Logging to $log_file";
##
# Configuration variables
##
# Connexion parameters to src DB
my $src_db_dsn = 'DBI:mysql:dbname=rt34;host=localhost';
my $src_db_user = 'rt34';
my $src_db_pass = 'rtpass';
# Some informations about src RT
# Without trailing '/'
my $src_rt_base_url = 'http://rt34';
my $cf_originalid_name = 'id34';
# Global variables
my ($i, $DstQueue, $ticketid, $val, $msg);
my %principals; # Cache old->new principals ids
my %tickets; # Cache old->new tickets ids
my %customfields; # Cache old->new customfields ids
##
# Connect to SRC DB
##
my $src = DBI->connect($src_db_dsn, $src_db_user, $src_db_pass) or die $DBI::errstr;
##
# Prepare needed sql statements for efficiency
##
# Tickets
my $src_get_tickets_sth = $src->prepare("SELECT Tickets.* FROM Tickets, Queues
WHERE Tickets.Queue = Queues.Id
AND Queues.Name = ? ORDER BY Id ASC");
# Ticket transactions
my $src_get_ticket_transactions_sth = $src->prepare("SELECT * FROM Transactions
WHERE ObjectType='RT::Ticket'
AND ObjectId=? ORDER BY Id ASC");
# Transaction attachments
my $src_get_transaction_attachments_sth = $src->prepare("SELECT * FROM Attachments WHERE
TransactionId = ? ORDER BY Id ASC");
# User
my $src_get_user_sth = $src->prepare("SELECT * FROM Users WHERE Id = ?");
# Principal
my $src_get_principal_sth = $src->prepare("SELECT * FROM Principals WHERE Id = ?");
# Group
my $src_get_group_sth = $src->prepare("SELECT * FROM Groups WHERE Id = ?");
# Ticket customfield values
my $src_get_ticket_ocfvs_sth = $src->prepare("SELECT * FROM ObjectCustomFieldValues
WHERE ObjectType = 'RT::Ticket'
AND ObjectId = ? ORDER BY Id ASC");
# Transaction customfield values
my $src_get_transaction_ocfvs_sth = $src->prepare("SELECT * FROM ObjectCustomFieldValues
WHERE ObjectType = 'RT::Transaction'
AND ObjectId = ? ORDER BY Id ASC");
# Customfield
my $src_get_cf_sth = $src->prepare("SELECT * FROM CustomFields WHERE Id = ? ORDER BY Id ASC");
# Links
my $src_get_ticket_links_sth = $src->prepare("SELECT * FROM Links WHERE LocalTarget = ? ORDER BY Id ASC");
# Privileged user
my $src_user_is_privileged_sth = $src->prepare("SELECT Count(*) FROM Groups, GroupMembers
WHERE Groups.Type = 'Privileged'
AND Groups.Id = GroupMembers.GroupId
AND GroupMembers.MemberId = ?");
# Ticket watchers (except Owner which we got already in ticket table)
my $src_get_ticket_watchers_sth = $src->prepare("SELECT Groups.Type, GroupMembers.* FROM Groups, GroupMembers
WHERE GroupMembers.GroupId=Groups.Id
AND Groups.Domain='RT::Ticket-Role'
AND Groups.Type != 'Owner'
AND Groups.Instance=?");
# Group members
my $src_get_group_members_sth = $src->prepare("SELECT * FROM GroupMembers WHERE GroupId=?");
# Customfield values
my $src_get_customfield_values_sth = $src->prepare("SELECT * FROM CustomFieldValues WHERE CustomField = ?");
##
# Needed functions
##
sub print_time_to_process {
my $end_time = time;
my $time_to_process = $end_time - $start_time;
if ( $time_to_process >= 3600 ) {
$time_to_process = sprintf("%0.2f hours", $time_to_process / 3600);
} elsif ( $time_to_process >= 60 ) {
$time_to_process = sprintf("%0.2f minutes", $time_to_process / 60);
} else {
$time_to_process = sprintf("%0.2f seconds", $time_to_process);
}
say "Time spent: $time_to_process";
say LOG "Time spent: $time_to_process";
}
sub ask_on_error {
my $error_msg = shift;
say STDERR "E: Failed: $error_msg";
say LOG "E: Failed: $error_msg";
print_time_to_process;
print "Continue (y/n)? ";
$| = 1;
my $answer = <STDIN>;
chomp ($answer);
if ( $answer =~ /^\s*Y\s*$/i ) {
return (undef);
} else {
close(LOG);
exit (1);
}
}
sub convert_uri {
my $src_uri = shift;
my $dst_uri = $src_uri;
if ( $src_uri =~ m|^fsck.com-rt://.*/ticket/(\d+)$| ) {
# If we didn't saw the ticket id (ticket out of our SELECT (new tickets for example)
# use RT full URL
unless ( $tickets{$1} ) {
return ($src_rt_base_url."/Ticket/Display.html?id=$1");
}
# Construct URI manually, instead of using API (may be slower if we load the ticket to get the URI)
$dst_uri = 'fsck.com-rt://'.RT->Config->Get('Organization').'/ticket/'.$tickets{$1};
}
return ($dst_uri);
}
sub get_src_principal {
my $src_principal_id = shift;
$src_get_principal_sth->execute($src_principal_id);
my $src_principal = $src_get_principal_sth->fetchall_arrayref( {} );
unless ( $src_principal && scalar(@{$src_principal}) == 1 ) {
return undef;
}
$src_principal = shift @{$src_principal};
return $src_principal;
}
sub get_src_group {
my $src_group_id = shift;
$src_get_group_sth->execute($src_group_id);
my $src_group = $src_get_group_sth->fetchall_arrayref( {} );
unless ( $src_group && scalar(@{$src_group}) == 1 ) {
return undef;
}
$src_group = shift @{$src_group};
return $src_group;
}
sub get_src_user {
my $src_user_id = shift;
$src_get_user_sth->execute($src_user_id);
my $src_user = $src_get_user_sth->fetchall_arrayref( {} );
unless ( $src_user && scalar(@{$src_user}) == 1 ) {
return undef;
}
$src_user = shift @{$src_user};
return $src_user;
}
sub get_src_cf {
my $src_cf_id = shift;
$src_get_cf_sth->execute($src_cf_id);
my $src_cf = $src_get_cf_sth->fetchall_arrayref( {} );
unless ( $src_cf && scalar(@{$src_cf}) == 1 ) {
return undef;
}
$src_cf = shift @{$src_cf};
return $src_cf;
}
sub dst_get_or_create_principal {
my $src_principal_id = shift;
# Caching
return ($principals{$src_principal_id}) if ( $principals{$src_principal_id} );
# Get principal infos
my $src_principal = get_src_principal($src_principal_id);
# Bogus DB
unless ( $src_principal ) {
ask_on_error("E: Principal $src_principal_id not found on src (will use RT::SystemUser if you choose to continue)");
return ($RT::SystemUser->id);
}
my $dst_principal_id;
if ( $src_principal->{PrincipalType} eq 'User' ) {
$dst_principal_id = dst_get_or_create_user($src_principal->{ObjectId});
} elsif ( $src_principal->{PrincipalType} eq 'Group' ) {
$dst_principal_id = dst_get_or_create_group($src_principal->{ObjectId});
} else {
ask_on_error("E: Don't know what to do with a principal of type ".$src_principal->{PrincipalType}."(will use RT::SystemUser if you choose to continue)");
return ($RT::SystemUser->id);
}
$principals{$src_principal->{ObjectId}} = $dst_principal_id;
return ($dst_principal_id);
}
sub dst_get_or_create_group {
my $src_group_id = shift;
# Get Group infos from src
my $src_group = get_src_group($src_group_id);
# Bogus DB
unless ( $src_group ) {
ask_on_error("E: Group $src_group_id not found on src (will use RT::SystemUser if you choose to continue)");
return ($RT::SystemUser->id);
}
# Search or create DstGroup
my $DstGroup = RT::Group->new ( $RT::SystemUser );
$DstGroup->LoadUserDefinedGroup($src_group->{Name});
unless ( $DstGroup->id ) {
say LOG "Creating missing group ".$src_group->{Name}." on dst";
($val, $msg) = $DstGroup->CreateUserDefinedGroup(
Name => $src_group->{Name},
Description => $src_group->{Description},
Disabled => $src_group->{Disabled},
_RecordTransaction => 0,
);
unless ( $val ) {
ask_on_error("E: Failed: $msg (will use RT::SystemUser if you choose to continue)");
return ($RT::SystemUser->id);
}
# Update members
$src_get_group_members_sth->execute($src_group_id);
my $src_group_members = $src_get_group_members_sth->fetchall_arrayref( {} );
foreach my $src_member (@{$src_group_members}) {
my $dst_member_id = dst_get_or_create_principal($src_member->{MemberId});
unless ( $DstGroup->HasMember( $dst_member_id ) ) {
say LOG "Adding $dst_member_id as member";
($val, $msg) = $DstGroup->_AddMember( PrincipalId => $dst_member_id, InsideTransaction => 1 );
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
}
}
}
return ($DstGroup->id);
}
sub dst_get_or_create_user {
my $src_user_id = shift;
# Get User infos from src
my $src_user = get_src_user($src_user_id);
# We may have bogus users
unless ( $src_user || $src_user->{Name} ) {
ask_on_error("E: User $src_user_id not found or bogus on src (will use RT::SystemUser if you choose to continue)");
return ($RT::SystemUser->id);
}
# Search or create DstUser
my $DstUser = RT::User->new ( $RT::SystemUser );
if ( $src_user->{Email} ) {
$DstUser->LoadByEmail($src_user->{Email});
} else {
$DstUser->Load($src_user->{Name});
}
unless ( $DstUser->id ) {
( $src_user->{Disabled} ) ||= 0;
say LOG "Creating missing user ".$src_user->{Name}." on dst";
($val, $msg) = $DstUser->Create(
Name => $src_user->{Name},
Password => $src_user->{Password},
Comments => $src_user->{Comment},
Signature => $src_user->{Signature},
EmailAddress => $src_user->{EmailAddress},
FreeformContactInfo => $src_user->{FreeformContactInfo},
Organization => $src_user->{Organization},
RealName => $src_user->{RealName},
NickName => $src_user->{NickName},
Lang => $src_user->{Lang},
EmailEncoding => $src_user->{EmailEncoding},
WebEncoding => $src_user->{WebEncoding},
ExternalContactInfoId => $src_user->{ExternalContactInfoId},
ContactInfoSystem => $src_user->{ContactInfoSystem},
ExternalAuthId => $src_user->{ExternalAuthId},
AuthSystem => $src_user->{AuthSystem},
Gecos => $src_user->{Gecos},
HomePhone => $src_user->{HomePhone},
WorkPhone => $src_user->{WorkPhone},
MobilePhone => $src_user->{MobilePhone},
PagerPhone => $src_user->{PagerPhone},
Address1 => $src_user->{Address1},
Address2 => $src_user->{Address2},
City => $src_user->{City},
State => $src_user->{State},
Zip => $src_user->{Zip},
Country => $src_user->{Country},
Timezone => $src_user->{Timezone},
PGPKey => $src_user->{PGPKey},
Disabled => $src_user->{Disabled},
_RecordTransaction => 0,
);
unless ( $val ) {
ask_on_error("E: Failed: $msg (will use RT::SystemUser if you choose to continue)");
return ($RT::SystemUser->id);
}
}
# Update privileged status
$src_user_is_privileged_sth->execute($src_user->{id});
my @rows = $src_user_is_privileged_sth->fetchrow_array();
if ( $rows[0] ) {
unless ( $DstUser->Privileged ) {
say LOG "Set privileged";
($val, $msg) = $DstUser->SetPrivileged(1);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
}
}
return ($DstUser->id);
}
sub dst_get_or_create_customfield {
my $src_cf_id = shift;
# Caching
return ($customfields{$src_cf_id}) if ( $customfields{$src_cf_id} );
# Get CF infos from src
my $src_cf = get_src_cf($src_cf_id);
# We may have bogus CF ?
unless ( $src_cf ) {
ask_on_error ("E: CustomField $src_cf_id not found on src");
}
# Search or create DstCF
my $DstCF;
my $DstCFs = RT::CustomFields->new ( $RT::SystemUser );
$DstCFs->LimitToLookupType($src_cf->{LookupType});
$DstCFs->Limit( FIELD => 'Name', OPERATOR => '=', VALUE => $src_cf->{Name} );
if ( $DstCFs->Count ) {
$DstCF = $DstCFs->First;
} else {
say LOG "Creating missing CustomField ".$src_cf->{Name}." on dst";
$DstCF = RT::CustomField->new ( $RT::SystemUser );
($val, $msg) = $DstCF->Create(
Name => $src_cf->{Name},
Type => $src_cf->{Type},
MaxValues => $src_cf->{MaxValues},
Repeated => $src_cf->{Repeated},
Pattern => $src_cf->{Pattern},
LookupType => $src_cf->{LookupType},
Description => $src_cf->{Description},
SortOrder => $src_cf->{SortOrder},
Creator => dst_get_or_create_principal($src_cf->{Creator}),
Created => $src_cf->{Created},
LastUpdatedBy => dst_get_or_create_principal($src_cf->{LastUpdatedBy}),
LastUpdated => $src_cf->{LastUpdated},
Disabled => $src_cf->{Disabled},
);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
# Sync CustomFields values if any
if ( $DstCF->Type =~ /^(Select|Combobox)$/ ) {
$src_get_customfield_values_sth->execute($src_cf->{id});
my $src_customfield_values = $src_get_customfield_values_sth->fetchall_arrayref( {} );
foreach my $src_customfield_value (@{$src_customfield_values}) {
say LOG "Adding value ".$src_customfield_value->{Name};
($val, $msg) = $DstCF->AddValue(
Name => $src_customfield_value->{Name},
Description => $src_customfield_value->{Description},
SortOrder => $src_customfield_value->{SortOrder},
Category => $src_customfield_value->{Category},
);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
}
}
}
# Apply it to the queue
unless ( $DstCF->IsApplied($DstQueue->id) || $DstCF->IsApplied(0) ) {
say LOG "Applying CustomField on queue ".$DstQueue->Name;
($val, $msg) = $DstCF->AddToObject($DstQueue);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
}
$customfields{$src_cf->{id}} = $DstCF->id;
return ($DstCF->id);
}
##
# Create a CustomField to record src/dst tickets ids
##
my $DstOriginalIdCF = RT::CustomField->new( $RT::SystemUser );
$DstOriginalIdCF->Load( $cf_originalid_name );
unless ( $DstOriginalIdCF->id ) {
say LOG "Creating CF $cf_originalid_name to record ticket ID correspondences";
($val, $msg) = $DstOriginalIdCF->Create(
Name => $cf_originalid_name,
Type => 'Freeform',
MaxValues => 1,
Description => 'Original Ticket Id (before migration)',
LookupType => 'RT::Queue-RT::Ticket',
LinkValueTo => $src_rt_base_url.'/Ticket/Display.html?id=__CustomField__',
);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
}
##
# Load Everyone group, we will use it twice later
##
my $Everyone = RT::Group->new( $RT::SystemUser );
$Everyone->LoadSystemInternalGroup( 'Everyone' );
##
# Check queues on dst
##
foreach my $queue_name (@queues) {
# Get queue on src
my $src_queue = $src->selectall_arrayref("SELECT * FROM Queues WHERE Name='$queue_name'", { Slice => {} });
unless ( $src_queue && scalar(@{$src_queue}) == 1 ) {
ask_on_error("E: Queue $queue_name not found on src");
}
$src_queue = shift @{$src_queue};
$DstQueue = RT::Queue->new( $RT::SystemUser );
$DstQueue->Load( $queue_name );
unless ( $DstQueue->id ) {
say LOG "Creating queue $queue_name on dst";
($val, $msg ) = $DstQueue->Create(
Name => $src_queue->{Name},
CorrespondAddress => $src_queue->{CorrespondAddress},
Description => $src_queue->{Description},
CommentAddress => $src_queue->{CommentAddress},
SubjectTag => $src_queue->{SubjectTag},
InitialPriority => $src_queue->{InitialPriority},
FinalPriority => $src_queue->{FinalPriority},
DefaultDueIn => $src_queue->{DefaultDueIn},
Sign => $src_queue->{Sign},
Encrypt => $src_queue->{Encrypt},
);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
}
# Make sure queue is enabled
if ( $Queue->Disabled ) {
($val, $msg) = $Queue->SetDisabled(0);
unless ( $val ) {
ask_on_error("E: Failed to enable queue ".$Queue->Name.": $msg");
}
}
unless ( $Everyone->PrincipalObj->HasRight( Object => $DstQueue, Right => 'OwnTicket' ) ) {
say LOG "Give temporarly OwnTicket right to Everyone on ".$DstQueue->Name;
($val, $msg) = $Everyone->PrincipalObj->GrantRight( Object => $DstQueue, Right => 'OwnTicket' );
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
}
my $DstQueueCFs = $DstQueue->CustomFields;
$DstQueueCFs->Limit( FIELD => 'id', VALUE => $DstOriginalIdCF->id );
unless ( $DstQueueCFs->Count ) {
# Apply it to the queue
say LOG "Applying CF $cf_originalid_name to dst queue";
my $OCF = RT::ObjectCustomField->new($RT::SystemUser);
($val, $msg) = $OCF->Create(
CustomField => $DstOriginalIdCF->id,
ObjectId => $DstQueue->id,
);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
}
# Walk through src tickets
$src_get_tickets_sth->execute($queue_name);
my $src_tickets = $src_get_tickets_sth->fetchall_arrayref( {} );
say "Importing tickets for queue $queue_name" if ( scalar @{$src_tickets} );
$i = 0;
foreach my $src_ticket (@{$src_tickets} ) {
$i++;
printf ("\rProgress: %0.2f%%", 100 * $i / scalar @{$src_tickets});
#
## Ticket
#
say LOG "Importing ticket: ".$src_ticket->{id};
# Check if it has not already been imported (to be able to run the script
# multiple time)
my $CheckDstTickets = RT::Tickets->new( $RT::SystemUser );
$CheckDstTickets->LimitCustomField( CUSTOMFIELD => $cf_originalid_name, VALUE => $src_ticket->{id} );
if ( $CheckDstTickets->Count ) {
say LOG "Already imported";
delete $...@{$src_tickets}{$src_ticket};
delete $tickets{$src_ticket->{id}};
next;
}
my $DstTicket = RT::Ticket->new( $RT::SystemUser );
($ticketid, $msg) = $DstTicket->Create(
Queue => $DstQueue->id,
Type => $src_ticket->{Type},
IssueStatement => $src_ticket->{IssueStatement},
Resolution => $src_ticket->{Resolution},
Owner => dst_get_or_create_principal($src_ticket->{Owner}),
Subject => $src_ticket->{Subject},
InitialPriority => $src_ticket->{InitialPriority},
FinalPriority => $src_ticket->{FinalPriority},
Priority => $src_ticket->{Priority},
TimeEstimated => $src_ticket->{TimeEstimated},
TimeWorked => $src_ticket->{TimeWorked},
Status => $src_ticket->{Status},
TimeLeft => $src_ticket->{TimeLeft},
Told => $src_ticket->{Told},
Starts => $src_ticket->{Starts},
Started => $src_ticket->{Started},
Due => $src_ticket->{Due},
Resolved => $src_ticket->{Resolved},
LastUpdatedBy => dst_get_or_create_principal($src_ticket->{LastUpdatedBy}),
LastUpdated => $src_ticket->{LastUpdated},
Creator => dst_get_or_create_principal($src_ticket->{Creator}),
Created => $src_ticket->{Created},
Disabled => $src_ticket->{Disabled},
_RecordTransaction => 0,
);
unless ( $ticketid ) {
ask_on_error("E: failed: $msg");
}
# Record new id
say LOG "Recording Src ID in CF";
$tickets{$src_ticket->{id}} = $ticketid;
($val, $msg) = $DstTicket->AddCustomFieldValue(
Field => $DstOriginalIdCF->id,
Value => $src_ticket->{id},
RecordTransaction => 0,
);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
say LOG "Old id: ".$src_ticket->{id}.", new id: $ticketid";
}
# End of progress information
print "\n";
say LOG "Revoke temporarly OwnTicket right to Everyone on ".$DstQueue->Name;
($val, $msg) = $Everyone->PrincipalObj->RevokeRight( Object => $DstQueue, Right => 'OwnTicket' );
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
}
# Update Tickets sub-objects (now that we have all old/new ids)
say "Importing ticket sub-objects" if ( scalar keys %tickets );
$i = 0;
foreach my $src_ticket_id (keys %tickets ) {
$i++;
printf ("\rProgress: %0.2f%%", 100 * $i / scalar keys %tickets);
my $dst_ticket_id = $tickets{$src_ticket_id};
say LOG "Importing objects for ticket src:$src_ticket_id dst:$dst_ticket_id";
#
## Ticket CustomField Values
#
my %ticket_ocfvs;
$src_get_ticket_ocfvs_sth->execute($src_ticket_id);
my $src_ticket_ocfvs = $src_get_ticket_ocfvs_sth->fetchall_arrayref( {} );
foreach my $src_ticket_ocfv (@{$src_ticket_ocfvs}) {
say LOG "Importing ticket customfield value:" .$src_ticket_ocfv->{id};
my $DstOCFV = RT::Record->new( $RT::SystemUser );
$DstOCFV->Table('ObjectCustomFieldValues');
$DstOCFV->_Init( $RT::SystemUser );
($val, $msg) = $DstOCFV->Create(
CustomField => dst_get_or_create_customfield($src_ticket_ocfv->{CustomField}),
ObjectType => $src_ticket_ocfv->{ObjectType},
ObjectId => $dst_ticket_id,
SortOrder => $src_ticket_ocfv->{SortOrder},
Content => $src_ticket_ocfv->{Content},
LargeContent => $src_ticket_ocfv->{LargeContent},
ContentType => $src_ticket_ocfv->{ContentType},
ContentEncoding => $src_ticket_ocfv->{ContentEncoding},
Creator => dst_get_or_create_principal($src_ticket_ocfv->{Creator}),
Created => $src_ticket_ocfv->{Created},
LastUpdatedBy => dst_get_or_create_principal($src_ticket_ocfv->{LastUpdatedBy}),
LastUpdated => $src_ticket_ocfv->{LastUpdated},
Disabled => $src_ticket_ocfv->{Disabled},
);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
$ticket_ocfvs{$src_ticket_ocfv->{id}} = $DstOCFV->id;
}
#
## Ticket links
#
$src_get_ticket_links_sth->execute($src_ticket_id);
my $src_ticket_links = $src_get_ticket_links_sth->fetchall_arrayref( {} );
foreach my $src_ticket_link (@{$src_ticket_links}) {
say LOG "Importing link: ".$src_ticket_link->{id};
# Id/URI update
my $base_uri = convert_uri($src_ticket_link->{Base});
my $target_uri = convert_uri($src_ticket_link->{Target});
my $DstLink = RT::Link->new( $RT::SystemUser );
($val, $msg) = $DstLink->Create(
Base => $base_uri,
Target => $target_uri,
Type => $src_ticket_link->{Type},
LocalTarget => $tickets{$src_ticket_link->{LocalTarget}},
LocalBase => $tickets{$src_ticket_link->{LocalBase}},
LastUpdatedBy => dst_get_or_create_principal($src_ticket_link->{LastUpdatedBy}),
LastUpdated => $src_ticket_link->{LastUpdated},
Creator => dst_get_or_create_principal($src_ticket_link->{Creator}),
Created => $src_ticket_link->{Created},,
);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
}
#
## Ticket watchers
#
$src_get_ticket_watchers_sth->execute($src_ticket_id);
my $src_ticket_watchers = $src_get_ticket_watchers_sth->fetchall_arrayref( {} );
foreach my $src_ticket_watcher (@{$src_ticket_watchers}) {
say LOG "Importing watcher ".$src_ticket_watcher->{Type}.": ".$src_ticket_watcher->{MemberId};
my $DstWatcherGroup = RT::Group->new( $RT::SystemUser );
$DstWatcherGroup->LoadTicketRoleGroup(Type => $src_ticket_watcher->{Type}, Ticket => $dst_ticket_id);
unless ( $DstWatcherGroup->id ) {
ask_on_error("E: Failed to load group: $msg");
}
($val, $msg) = $DstWatcherGroup->_AddMember(PrincipalId => dst_get_or_create_principal($src_ticket_watcher->{MemberId}), InsideTransaction => 1);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
}
#
## Ticket transactions
#
my %transactions;
# Ticket transactions
$src_get_ticket_transactions_sth->execute($src_ticket_id);
my $src_ticket_transactions = $src_get_ticket_transactions_sth->fetchall_arrayref( {} );
foreach my $src_ticket_transaction (@{$src_ticket_transactions}) {
say LOG "Importing ticket transaction: ".$src_ticket_transaction->{id};
my $DstTicketTransaction = RT::Record->new( $RT::SystemUser );
$DstTicketTransaction->Table('Transactions');
$DstTicketTransaction->_Init( $RT::SystemUser );
# Some ID changes
my $transaction_field = $src_ticket_transaction->{Field};
$transaction_field = $customfields{$src_ticket_transaction->{Field}} if ( $src_ticket_transaction->{Type} && $src_ticket_transaction->{Type} eq 'CustomField' );
my $transaction_oldvalue = $src_ticket_transaction->{OldValue};
my $transaction_newvalue = $src_ticket_transaction->{NewValue};
if ( $transaction_field && $transaction_field =~ /^(Owner|Cc|Requestor|AdminCc)$/ ) {
$transaction_oldvalue = dst_get_or_create_principal($src_ticket_transaction->{OldValue}) if ( $src_ticket_transaction->{OldValue} );
$transaction_newvalue = dst_get_or_create_principal($src_ticket_transaction->{NewValue}) if ( $src_ticket_transaction->{NewValue} );
}
if ( $transaction_oldvalue && $transaction_oldvalue =~ m|^fsck.com-rt://.*/ticket/(\d+)$| ) {
$transaction_oldvalue = convert_uri($transaction_oldvalue);
}
if ( $transaction_newvalue && $transaction_newvalue =~ m|^fsck.com-rt://.*/ticket/(\d+)$| ) {
$transaction_newvalue = convert_uri($transaction_newvalue);
}
my $transaction_oldreference = $src_ticket_transaction->{OldReference};
my $transaction_newreference = $src_ticket_transaction->{NewReference};
if ( $src_ticket_transaction->{ReferenceType} && $src_ticket_transaction->{ReferenceType} eq 'RT::ObjectCustomFieldValue' ) {
$transaction_oldreference = $ticket_ocfvs{$src_ticket_transaction->{OldReference}} if ( $src_ticket_transaction->{OldReference} );
$transaction_newreference = $ticket_ocfvs{$src_ticket_transaction->{NewReference}} if ( $src_ticket_transaction->{NewReference} );
}
($val, $msg) = $DstTicketTransaction->Create(
ObjectType => 'RT::Ticket',
ObjectId => $dst_ticket_id,
TimeTaken => $src_ticket_transaction->{TimeTaken},
Type => $src_ticket_transaction->{Type},
Field => $transaction_field,
OldValue => $transaction_oldvalue,
NewValue => $transaction_newvalue,
ReferenceType => $src_ticket_transaction->{ReferenceType},
OldReference => $transaction_oldreference,
NewReference => $transaction_newreference,
Data => $src_ticket_transaction->{Data},
Creator => dst_get_or_create_principal($src_ticket_transaction->{Creator}),
Created => $src_ticket_transaction->{Created},
);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
$transactions{$src_ticket_transaction->{id}} = $DstTicketTransaction->id;
#
## Transaction CustomField Values
#
$src_get_transaction_ocfvs_sth->execute($src_ticket_transaction->{id});
my $src_transaction_ocfvs = $src_get_transaction_ocfvs_sth->fetchall_arrayref( {} );
foreach my $src_transaction_ocfv (@{$src_transaction_ocfvs}) {
say LOG "Importing transaction customfield value:" .$src_transaction_ocfv->{id};
my $DstOCFV = RT::Record->new( $RT::SystemUser );
$DstOCFV->Table('ObjectCustomFieldValues');
$DstOCFV->_Init( $RT::SystemUser );
($val, $msg) = $DstOCFV->Create(
CustomField => dst_get_or_create_customfield($src_transaction_ocfv->{CustomField}),
ObjectType => $src_transaction_ocfv->{ObjectType},
ObjectId => $DstTicketTransaction->id,
SortOrder => $src_transaction_ocfv->{SortOrder},
Content => $src_transaction_ocfv->{Content},
LargeContent => $src_transaction_ocfv->{LargeContent},
ContentType => $src_transaction_ocfv->{ContentType},
ContentEncoding => $src_transaction_ocfv->{ContentEncoding},
Creator => dst_get_or_create_principal($src_transaction_ocfv->{Creator}),
Created => $src_transaction_ocfv->{Created},
LastUpdatedBy => dst_get_or_create_principal($src_transaction_ocfv->{LastUpdatedBy}),
LastUpdated => $src_transaction_ocfv->{LastUpdated},
Disabled => $src_transaction_ocfv->{Disabled},
);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
}
#
## Transaction Attachments
#
my %attachments;
$src_get_transaction_attachments_sth->execute($src_ticket_transaction->{id});
my $src_transaction_attachments = $src_get_transaction_attachments_sth->fetchall_arrayref( {} );
foreach my $src_transaction_attachment (@{$src_transaction_attachments}) {
say LOG "Importing attachment: ".$src_transaction_attachment->{id};
my $parentid = ( $src_transaction_attachment->{Parent} == 0 ) ? 0 : $attachments{$src_transaction_attachment->{Parent}};
my $DstTransactionAttachment = RT::Record->new( $RT::SystemUser );
$DstTransactionAttachment->Table('Attachments');
$DstTransactionAttachment->_Init( $RT::SystemUser );
($val, $msg) = $DstTransactionAttachment->Create(
TransactionId => $transactions{$src_transaction_attachment->{TransactionId}},
Parent => $parentid,
MessageId => $src_transaction_attachment->{MessageId},
Subject => $src_transaction_attachment->{Subject},
Filename => $src_transaction_attachment->{Filename},
ContentType => $src_transaction_attachment->{ContentType},
ContentEncoding => $src_transaction_attachment->{ContentEncoding},
Content => $src_transaction_attachment->{Content},
Headers => $src_transaction_attachment->{Headers},
Creator => dst_get_or_create_principal($src_transaction_attachment->{Creator}),
Created => $src_transaction_attachment->{Created},
);
unless ( $val ) {
ask_on_error("E: Failed: $msg");
}
$attachments{$src_transaction_attachment->{id}} = $DstTransactionAttachment->id;
}
}
}
# End of progress information
print "\n";
# Finish statements
$src_get_tickets_sth->finish();
$src_get_ticket_transactions_sth->finish();
$src_get_transaction_attachments_sth->finish();
$src_get_user_sth->finish();
$src_get_principal_sth->finish();
$src_get_group_sth->finish();
$src_get_ticket_ocfvs_sth->finish();
$src_get_transaction_ocfvs_sth->finish();
$src_get_cf_sth->finish();
$src_get_ticket_links_sth->finish();
$src_user_is_privileged_sth->finish();
$src_get_ticket_watchers_sth->finish();
$src_get_group_members_sth->finish();
$src_get_customfield_values_sth->finish();
$src->disconnect;
print_time_to_process;
close(LOG);
RT Training in Washington DC, USA on Oct 25 & 26 2010
Last one this year -- Learn how to get the most out of RT!
