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
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
"Custom Field 2
(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
