Migrating Your Bugtracker Database to Jira Posted on 4 April 2012 | 11:13 pm
I have been working on this new project, It's a bug database migration. Not necessarily simple but pretty straight forward. Take the entire contents of a BugTracker.net DB and migrate it to a new DB, Jira.
Step one, analyze the DB for current fields, number of records, organization of date and export capability. Although the BugTracker.net (BT) has export to excel and some backup capabilities it doesn't look all the smart or convenient or easy, in terms of exporting the entire DB. I start by reading the docs on Bugtracker and searching for info from other folks that have done. I ask the guy the who wrote it, Corey Trager. He's happy to help, if I pay him (Gee, thanks guy). There's not that much info about it out there.
So I start looking around -- find the front-end code and reverse engineer it -- pull out the connect string for the DB. Its a SQL Server 2005 Database (it could have been Mysql, just as well) running on XP or whatever. I download a SQL Server Management Studio and connect to the DB. Pick it apart.
The initial take, ~6500 records, ~70000 comments and updates, 3211 attachments.
Next I analyze the target, Jira. Jira has a great and robust import tool. If you have to do this project. By all means use it. The only convenient way, though, from the BT was to use the comma seperated value method of the Import Tool. So that became my target.
Create a tab delimited file containing the BT database in it entirety. Programmatiaclly and reliably extract the entire content of the BT and convert it into a tab delimited file to be ingested by the Jira Import Plug-In.
I wrote three queries to extract the data from the BT backend SQL Server DB. I wrote 6 tcl scripts that assemble the BT data into a huge tab delimited file.
For the comments, attachments, updates, and emails:
set nocount on SELECT a.bp_bug, a.bp_type, b.us_username, a.bp_date, a.bp_comment,
a.bp_email_from,
a.bp_email_to, a.bp_file, a.bp_size, a.bp_content_type, a.bp_id,
(select '#@#@#@#' newcol) #@#@#@#
FROM dbo.bug_posts a, dbo.users b
WHERE b.us_id = bp_user
ORDER BY a.bp_bug, a.bp_type, a.bp_date;
For the bug relationships:
set nocount on SELECT re_bug1, re_bug2, re_type
FROM dbo.bug_relationships
ORDER BY re_bug1;
For the issues:
set nocount on SELECT bg_id bugtrackerID,
(select pj_name from dbo.projects where bg_project = pj_id) project,
(select 'projectKey' newcol) projectKey,
bg_short_desc summary,
(select us_username from dbo.users
WHERE bg_reported_user = us_id) reporter,
(select ct_name from dbo.categories
WHERE bg_category = ct_id) issueType,
(select pr_name from dbo.priorities
WHERE bg_priority = pr_id) priority,
(select pr_name from dbo.priorities
WHERE bg_priority = pr_id) urgency,
"Custom Field 1" customField1,
"Custom Field 2" customField2,
(select us_username from dbo.users
WHERE bg_assigned_to_user = us_id) assignee,
(select st_name from dbo.statuses
WHERE bg_status = st_id) status,
(select 'newcol' newcol) resolution,
bg_reported_date reported_date,
bg_last_updated_date lastUpdatedDate,
(select udf_name from dbo.user_defined_attribute
WHERE bg_user_defined_attribute = udf_id)
component from bugs,projects
WHERE bg_project = pj_id
ORDER BY bg_id ;
That should get you started.
Ultimately Corey Trager did give me a one interesting clue, asking me "what are you going to do about attachments, like screenshots, if they were posted as blobs into the db?" I looked all over for evidence of inline attachements, blobs in the db, or a way to post them. I didn't find that at all. I did find the attachments in a very unusual place on the file system for the Web front end to Bugtraacker. They were in c:\Temp\Uploads. Basically these were copied into a place were the Jira import tool could find them and poof ... it was done.
The import tool for the Jira went extremetely well. There were issues around the organization of data and arranging the file for the successful import of comments, and attachments, but overall the tools is well designed and works quite well. Error reporting is good and the process can be a bit tedious, but that is just the way it is with delimited files.
Here's a snippet from a conversation about the import between my colleague and I:
4:50 PM L: Ah no
4:50 PM Shawn Kielty:ok
4:50 PM L: it's shows up correctly in the wizard !
4:50 PM Shawn Kielty:yeah ... stunning thing -- that wizard
4:51 PM L: Ok, importing issues is in progress
4:52 PM Shawn Kielty: sweet
4:52 PM L:Yes, this wizard is very good
 Johnny's Cookies Posted on 23 January 2012 | 12:37 am
 These are a big hit around my family at Christmas and originally came from my grandmother Gertrude Kielty. 1 cup shortening (I use butter) 1 cup brown sugar 1 egg 1 3/4 cup sifted flour 1/2 teaspoon salt 1/2 teaspoon soda 1/4 cup thick sour milk (I use sour cream) 3/4 cup pecan haves broken into pieces 1 cup candied cherries (a small container) cut into pieces. 1 pecan half for the top of each cookie Mix all the ingredients together reserving the pecan halves for the cookie tops. Drop small teaspoonfuls of dough onto a greased cookie sheet. Place a pecan halve on top of each cookie. Bake @ 400 degrees for about 10-11 minutes. Makes 3 dozen cookies or so. Zipfizz Posted on 18 January 2012 | 7:45 pm
jade Beach at Big Sur Posted on 18 December 2011 | 2:56 pm
Temple Man Posted on 19 November 2011 | 1:33 pm
Fox Parang XL Posted on 16 November 2011 | 1:31 pm
Hey, Did You Ever Ask Yourselves ... Posted on 6 November 2011 | 12:25 pm
Re-Entry Posted on 20 October 2011 | 2:02 pm
1928 Hawthorne Deluxe Posted on 17 October 2011 | 3:38 am
Bikes in Yingkou, China Posted on 15 October 2011 | 11:48 am
Dear Canon, ... Posted on 14 October 2011 | 4:13 pm
China, Goodbye Posted on 9 October 2011 | 2:22 pm
Yak Bone Pendant Posted on 8 October 2011 | 3:16 pm
Yanjing Beer, Delivered in Bottles by Bicycle. Posted on 7 October 2011 | 5:02 pm
Sleeper to Beijing Posted on 7 October 2011 | 4:18 pm
and the result Posted on 5 October 2011 | 2:19 pm
Home Remedy Posted on 5 October 2011 | 1:54 pm
REI Flash 18 Posted on 5 October 2011 | 6:48 am
Four Stars, and So Much More Posted on 4 October 2011 | 6:26 pm
Trouble in Cameraland Posted on 4 October 2011 | 1:41 pm
Small Farm in Northeast china Posted on 3 October 2011 | 2:48 am
This Sea of China ... Posted on 2 October 2011 | 1:50 pm
Posted on 1 October 2011 | 5:54 am
Red Beach, Red Cranes ... Red Everywhere Posted on 30 September 2011 | 2:35 pm
“Museum for Chinese Volunteers Fighting with Korean Against the Imperialist Americans” Posted on 29 September 2011 | 1:47 pm
| 
