Backstory
We've been using DCL as a tracking system since 2002. I really liked some of its features, especially the project manangement pieces, but a lot of things pissed me off. But, I used it until my boss axed broader used of it, as it was inflexible and too confusing for some people.
After looking around in the Fall of 2006 for other systems, someone mentioned they used RT for their tracking system. I looked... Oooh it's Perl! Ooooh it's Mason! Oooooh it's stupid flexible! Downloaded, installed, up and running...
I'm loving it.
My boss loves it.
My colleagues love it.
We're adopting it.
Problem
We needed to get stuff out of DCL and into RT. A LOT of stuff. We decided we didn't care about projects, and we never used DCL "tickets", just "work orders". So I spent many hours learning the database internals of RT... and how to take DCL "workorders" and "timecards" and create the analagous in RT.
It's done, and this is the result. The code is nearly completely documented... You'll need to salt the *Map hashes and the database config variables, but other than that, should be good to go, as long as you just want to do what we wanted. Enjoy.
Code (Perl)
New code as of 1/18/2007.. Adds principals, groups, etc. for the TICKETS so that you can change ownership, add people, etc.
#!/usr/bin/perl -w
# This script sucks "workorders" out of DCL, and pushes them
# into an RT3 database as "tickets".
# Code can be considered Public Domain ... m@ 1/2007
# Roughly, a DCL workorder (WO) becomes an RT Ticket, the description and notes in
# the WO become Attachments (described below). TimeCards attached to a WO also
# become Attachments.
# Attachments are two things:
# First, they're a Transaction linked the to Ticket. The Transaction lays out the
# type of "thing" that's being attached, and various other relevant metadata. This
# allows for ACL abstraction, and the linking of diverse "things" to Tickets.
#
# Second, they're an Attachment linked to a Transaction. The Attachment contains the
# "thing".. be it notes about a ticket, e-mail correspondance, a file, etc.
# RT Schemaish
# tickets where id = <some id>
# transactions where ticket = <your ticket id>
# attachments where transactionid = <your transaction id>
# ** Rigid Schema MAP. If you need a DCL field not listed here migrated, diddle away cuz I
# ain't doin' it.
# Core WO:
# dcl.workorders.product = rt3.Tickets.Queue * Need to map dcl products to rt3 Queues
# dcl.workorders.createby = rt3.Tickets.Creator * Need to map dcl uids to rt3 uids
# dcl.workorders.createdon = rt3.Tickets.Created
# dcl.workorders.closedon = rt3.Tickets.Resolved
# dcl.workorders.status = rt3.Tickets.Status * Need to convert dcl ints to rt3 strings
# dcl.workorders.lastactionon = rt3.Tickets.LastUpdated
# dcl.workorders.deadlineon = rt3.Tickets.Due
# dcl.workorders.eststarton = rt3.Tickets.Starts
# dcl.workorders.starton = rt3.Tickets.Started
# dcl.workorders.esthours = rt3.Tickets.TimeEstimated * Need to convert dcl float(10,2) to rt3 ints
# dcl.workorders.totalhours = rt3.Tickets.TimeWorked * Need to convert dcl float(10,2) to rt3 ints
# dcl.workorders.priority = rt3.Tickets.Priority * Need to map dcl priorities to rt3 priorities
# dcl.workorders.summary = rt3.Tickets.Subject * 100char max in dcl, 200char max in rt3
# dcl.workorders.responsible = rt3.Tickets.Owner * Need to map dcl uids to rt3 uids
#
# Must also create a transaction/attachment and:
# dcl.workorders.notes +
# dcl.workorders.description = rt3.Attachments.Content
#
# Time Cards:
# dcl.timecards.inputon = rt3.Transactions.Created
# = rt3.Attachments.Created
# dcl.timecards.actionby = rt3.Transactions.Creator * Need to map dcl uids to rt3 uids
# = rt3.Attachments.Creator * Need to map dcl uids to rt3 uids
# dcl.timecards.hours = rt3.Transactions.TimeTaken * Need to convert dcl float(10,2) to rt3 ints
# dcl.timecards.summary +
# dcl.timecards.description = rt3.Attachments.Content
#
# Groups: Instance is the ticketId!!
#
# id Domain Type Instance
# 902 RT::Ticket-Role Requestor 610
# 903 RT::Ticket-Role Owner 610
# 904 RT::Ticket-Role Cc 610
# 905 RT::Ticket-Role AdminCc 610
#
# Principals: ObjectId is the GroupId!
#
# id PrincipalType ObjectId Disabled
# 902 Group 902 0
# 903 Group 903 0
# 904 Group 904 0
# 905 Group 905 0
#
# GroupMembers:
#
# id GroupId MemberId
# 631 902 24
# 635 903 24
# UID Map:
# Maps DCL UIDs => RT UIDs
my %UserMap = (
5 => 151,
13 => 183,
9 => 24,
4 => 22,
11 => 127,
42 => 186,
43 => 188,
37 => 190,
DEF => 10,
);
# Priority Map:
# Maps DCL Priorities => RT Priorities
my %PrioMap = (
7 => 0,
6 => 1,
5 => 2,
4 => 3,
3 => 4,
2 => 5,
1 => 6,
DEF => 0,
);
# Status Map:
# Maps DCL Status numbers => RT Status strings
my %StatusMap = (
2 => 'resolved',
3 => 'stalled',
5 => 'stalled',
1 => 'open',
6 => 'resolved',
4 => 'new',
DEF => 'resolved',
);
# Queue Map:
# Maps DCL Products => RT Queues
my %QueueMap = (
13 => 11,
15 => 12,
5 => 3,
16 => 11,
2 => 5,
10 => 11,
9 => 14,
4 => 8,
11 => 11,
6 => 4,
7 => 6,
DEF => 13,
);
use strict;
use DBI;
my $dcl_dbhost='127.0.0.1';
my $dcl_dbname='dcl';
my $dcl_dbuser='dcluser';
my $dcl_dbpass='track';
my $rt_dbhost='127.0.0.1';
my $rt_dbname='rt3';
my $rt_dbuser='rt_user';
my $rt_dbpass='rt_pass';
sub DB_Connect {
my ($dbname,$dbhost,$dbuser,$dbpass) = @_;
my $db_h=DBI->connect("DBI:mysql:$dbname:$dbhost","$dbuser","$dbpass") or return 0;
return $db_h;
}
sub Create_Principal_Shit {
my($rth,$rtTIXid,$Creator)=@_;
my @gids;
# Build the groups
for(("Requestor","Owner","Cc","AdminCc")) {
my $sth = $rth->prepare("INSERT INTO Groups(Domain,Type,Instance)
VALUES('RT::Ticket-Role','$_','$rtTIXid')");
if(!$sth) {
die "Error inserting into groups: " . $rth->errstr . "\n";
}
if(!$sth->execute) {
die "Error exec inserting into groups: " . $sth->errstr . "\n";
}
$sth->finish;
my $gid = $rth->{'mysql_insertid'};
push(@gids,$gid);
}
# Build the principals
for(@gids) {
my $sth = $rth->prepare("INSERT INTO Principals(PrincipalType,ObjectId,Disabled)
VALUES('Group','$_','0')");
if(!$sth) {
die "Error inserting into principals: " . $rth->errstr . "\n";
}
if(!$sth->execute) {
die "Error exec inserting into principals: " . $sth->errstr . "\n";
}
$sth->finish;
}
# Build the members
for(($gids[0],$gids[1])) {
my $sth = $rth->prepare("INSERT INTO GroupMembers(GroupId,MemberId)
VALUES('$_','$Creator')");
if(!$sth) {
die "Error inserting into groupmembers: " . $rth->errstr . "\n";
}
if(!$sth->execute) {
die "Error exec inserting into groupmembers: " . $sth->errstr . "\n";
}
$sth->finish;
}
}
sub Create_Attachment {
my($rth,$rtTIXid,$Created,$Creator,$TimeTaken,$Content)=@_;
# print "INSERT INTO Transactions(ObjectType,ObjectID,TimeTaken,Type,Data,Creator,Created) VALUES('RT::Ticket','$rtTIXid','$TimeTaken','Comment','No Subject','$Creator','$Created')";
# print "\n\n";
# Build the transaction
my $sth = $rth->prepare("INSERT INTO Transactions(ObjectType,ObjectID,TimeTaken,Type,Data,Creator,Created)
VALUES('RT::Ticket','$rtTIXid','$TimeTaken','Comment','No Subject','$Creator','$Created')");
if(!$sth) {
die "Error inserting into transacts: " . $rth->errstr . "\n";
}
if(!$sth->execute) {
die "Error exec inserting into transacts: " . $sth->errstr . "\n";
}
$sth->finish;
my $tid = $rth->{'mysql_insertid'};
# my $tid = 666;
# print "INSERT INTO Attachments(TransactionId,Parent,ContentType,ContentEncoding,Content,Creator,Created) VALUES('$tid','0','text/plain','none','$Content','$Creator','$Created')";
# print "\n\n";
# Build the attachment
$sth = $rth->prepare("INSERT INTO Attachments(TransactionId,Parent,ContentType,ContentEncoding,Content,Creator,Created)
VALUES('$tid','0','text/plain','none','$Content','$Creator','$Created')");
if(!$sth) {
die "Error inserting into attachments: " . $rth->errstr . "\n";
}
if(!$sth->execute) {
die "Error exec inserting into attachements: " . $sth->errstr . "\n";
}
$sth->finish;
}
sub Create_Ticket {
my($rth,$Queue,$Creator,$Created,$Resolved,$Status,$LastUpdated,
$Due,$Starts,$Started,$TimeEstimated,$TimeWorked,$Priority,
$Subject,$Owner,$Content)=@_;
# print "INSERT INTO Tickets(Queue,Type,IssueStatement,Resolution,Owner,Subject,Priority,TimeEstimated,TimeWorked,Status,TimeLeft,Starts,Started,Due,Starts,Resolved,LastUpdatedBy,LastUpdated,Creator,Created,Disabled) VALUES('$Queue','ticket','0','$Status','$Owner','$Subject','$Priority','$TimeEstimated','$TimeWorked','$Status','0','$Starts','$Started','$Due','$Resolved','$Owner','$LastUpdated','$Creator','$Created','0')";
# print "\n\n";
# Build the Ticket
my $sth = $rth->prepare("INSERT INTO Tickets(Queue,Type,IssueStatement,Resolution,Owner,
Subject,Priority,TimeEstimated,TimeWorked,Status,TimeLeft,Starts,Started,Due,
Resolved,LastUpdatedBy,LastUpdated,Creator,Created,Disabled)
VALUES('$Queue','ticket','0','$Status','$Owner',
'$Subject','$Priority','$TimeEstimated','$TimeWorked','$Status','0','$Starts','$Started','$Due',
'$Resolved','$Owner','$LastUpdated','$Creator','$Created','0')");
if(!$sth) {
die "Error inserting into tix: " . $rth->errstr . "\n";
}
if(!$sth->execute) {
die "Error exec inserting into tix: " . $sth->errstr . "\n";
}
$sth->finish;
my $tid = $rth->{'mysql_insertid'};
# my $tid = 42;
# Update it cuz I don't know how to set the EffectiveId elsewise
$sth = $rth->prepare("UPDATE Tickets SET EffectiveId = '$tid' WHERE id = '$tid'");
if(!$sth) {
die "Error updating into tix: " . $rth->errstr . "\n";
}
if(!$sth->execute) {
die "Error exec updating into tix: " . $sth->errstr . "\n";
}
$sth->finish;
# make the initial comment
Create_Attachment($rth,$tid,$Created,$Creator,0,$Content);
# Setup the principal shit
Create_Principal_Shit($rth,$tid,$Creator);
return $tid; # Because... why not?
}
sub Get_TimeCards {
my($dclh,$woid)=@_;
my $sth = $dclh->prepare("SELECT inputon,actionby,hours,summary,description
from timecards where jcn='$woid'");
$sth->execute;
my $row = $sth->fetchall_arrayref;
$sth->finish;
return $row;
}
sub Get_WOs {
my($dclh,$rth)=@_;
my $sth = $dclh->prepare("SELECT jcn,product,createby,createdon,closedon,status,lastactionon,
deadlineon,eststarton,starton,esthours,totalhours,priority,summary,responsible,
notes,description from workorders");
$sth->execute;
my $row = $sth->fetchall_arrayref;
$sth->finish;
my @woa=@{$$row[0]};
for(@$row) {
my @woa=@$_;
# Queue filtering based on product... Just for Jeff.
if(exists($QueueMap{$woa[1]})) {
$woa[1] = $QueueMap{$woa[1]};
} else {
$woa[1] = $QueueMap{DEF};
}
# createby
if(exists($UserMap{$woa[2]})) {
$woa[2] = $UserMap{$woa[2]};
} else {
$woa[2] = $UserMap{DEF};
}
# status
if(exists($StatusMap{$woa[5]})) {
$woa[5] = $StatusMap{$woa[5]};
} else {
$woa[5] = $StatusMap{DEF};
}
# priority
if(exists($PrioMap{$woa[12]})) {
$woa[12] = $PrioMap{$woa[12]};
} else {
$woa[12] = $PrioMap{DEF};
}
# responsible
if(exists($UserMap{$woa[14]})) {
$woa[14] = $UserMap{$woa[14]};
} else {
$woa[14] = $UserMap{DEF};
}
# esthours and totalhour * 60
$woa[10] *= 60;
$woa[11] *= 60;
# Munge description and notes into one field.
my $WOContent = $woa[16] . "\n" . $woa[15];
# Sanity
$WOContent =~ s/\r//gm;
$WOContent =~ s/\'|\`|\"//gm;
$woa[13] =~ s/\'|\`|\"//gm;
my $tid=Create_Ticket($rth,$woa[1],$woa[2],$woa[3],$woa[4],$woa[5],$woa[6],$woa[7],$woa[8],$woa[9],$woa[10],$woa[11],$woa[12],$woa[13],$woa[14],$WOContent);
for (@{Get_TimeCards($dclh,$woa[0])}) {
my @tca=@$_;
# actionby
if(exists($UserMap{$tca[1]})) {
$tca[1] = $UserMap{$tca[1]};
} else {
$tca[1] = $UserMap{DEF};
}
# hours
$tca[2] *= 60;
# Munge summary and description into one field.
my $TCContent = $tca[3] . "\n" . $tca[4];
$TCContent =~ s/\r//gm;
$TCContent =~ s/\'|\`|\"//gm;
# Do it!!!
Create_Attachment($rth,$tid,$tca[0],$tca[1],$tca[2],$TCContent);
}
}
}
my $dclh=DB_Connect($dcl_dbname,$dcl_dbhost,$dcl_dbuser,$dcl_dbpass);
my $rth=DB_Connect($rt_dbname,$rt_dbhost,$rt_dbuser,$rt_dbpass);
Get_WOs($dclh,$rth);