Home Projects Documentation Press / or click here to open the quicksearch Log in
Import From The Bug Genie Version2x
https://secure.gravatar.com/avatar/8446244ba01096058381c713b4b542f5.png?d=wavatar&s=28
Sun Jul 03 14:43 Authored by lsproc

Introduction  

Importing data from version 2 is only possible by using the import sql statements below. These statements will add your data to the default table set (with the default prefix), so make sure you look through the statements below before running the import script.

The following will be imported after you have run this import script:
  • Projects (with editions, components and releases)
  • Users, groups and teams
  • Issues
  • Log
  • Comments
  • Status types
  • Issue types
The import script will also add a "version 2-style" workflow, and set your imported projects to use it. This is because imported issues will not match up with the new workflow. This import must be run on a completely clean installation of The Bug Genie 3.0.1 to avoid any issues with existing data and inconsistencies.

After the scripts have been run  

When you've run the scripts included on this page, you should perform the following steps:
  1. Set project keys for all projects: You can do this by editing the column key in the table tbg3_projects after the scripts have been run. The key should be a unique, lowercase, non-spaced representation of the project name.
  2. Set permissions for all projects: Either use the permissions configuration page, or change to permissive security mode.
  3. Update issue type 'types': All issue types needs to be defined as a specific issue type. It must be one of the following types (edit the itemdata column in the tbg3_issuetypes table):
    • bug_report - Bug report
    • documentation_request - Documentation request
    • enhancement - Enhancement
    • feature_request - Feature request
    • idea - Idea
    • support_request - Support request
    • task - Task
    • developer_report - Scrum user story


Import scripts  

Main import script: 
START TRANSACTION;

#import ALL non-DEFAULT scopes
INSERT INTO tbg3_scopes (id, enabled, description, administrator, hostname) (SELECT id, enabled, description, admin, hostname FROM bugs2_scopes WHERE id > 1);

#import ALL projects
INSERT INTO tbg3_projects (id, name, prefix, use_prefix, homepage, leader, leader_type, description, doc_url, show_in_summary, qa_responsible, qa_responsible_type, locked, enable_builds, enable_editions, enable_components, scope, deleted, workflow_scheme_id, issuetype_scheme_id) (SELECT id, name, prefix, use_prefix, homepage, lead_by, lead_type, description, doc_url, 1, qa, qa_type, locked, 1, 1, 1, scope, deleted, 1, 1 FROM bugs2_products);

#import project editions
INSERT INTO tbg3_editions (id, name, description, leader, leader_type, doc_url, qa_responsible, qa_responsible_type, release_date, isreleased, isplannedreleased, locked, project, scope) (SELECT id, name, description, lead_by, lead_type, doc_url, qa, qa_type, release_date, released, 1 AS planned_released, locked, product, scope FROM bugs2_editions);

#import project components
INSERT INTO tbg3_components (id, name, version_major, version_minor, version_revision, project, scope) (SELECT id, name, version_major, version_minor, version_revision, product, scope FROM bugs2_components);

#assign components TO editions
INSERT INTO tbg3_editioncomponents (SELECT * FROM bugs2_editioncomponents);

#import project releases
INSERT INTO tbg3_builds (id, name, version_major, version_minor, version_revision, release_date, isreleased, locked, edition, project, scope) (SELECT id, name, version_major, version_minor, version_revision, release_date, released, locked, edition, 0 AS project, scope FROM bugs2_builds);

#import project milestones
INSERT INTO tbg3_milestones (id, name, description, reacheddate, itemtype, `startingdate`, scheduleddate, project, scope) (SELECT id, milestone_name, description, reached, 1, scheduled, scheduled, product, scope FROM bugs2_milestones);

#drop v3 autogenerated teams
DELETE FROM tbg3_teams;

#import ALL teams
INSERT INTO tbg3_teams (id, name, scope) (SELECT id, teamname, scope FROM bugs2_teams);

#assign team memberships
INSERT INTO tbg3_teammembers (SELECT * FROM bugs2_teammembers);

#import ALL users
INSERT INTO tbg3_users (id, username, password, buddyname, realname, email, userstate, homepage, lastseen, activated, enabled, deleted, avatar, use_gravatar, private_email, joined, group_id, scope) (SELECT id, uname, passwd, buddyname, realname, email, state, homepage, lastseen, activated, enabled, deleted, '', 1, 1, joined, group_id, scope FROM bugs2_users WHERE id > 1);

#delete ALL v3 autogenerated datatypes
DELETE FROM tbg3_listtypes;

#import ALL DATA types (STATUS, priority, etc)
INSERT INTO tbg3_listtypes (SELECT * FROM bugs2_listtypes);

#delete ALL v3 autogenerated datatypes
DELETE FROM tbg3_listtypes;

#import ALL DATA types (STATUS, priority, etc)
INSERT INTO tbg3_listtypes (SELECT * FROM bugs2_listtypes);

#delete ALL v3 autogenerated issuetypes
DELETE FROM tbg3_issuetypes;

#import ALL issuetypes
INSERT INTO tbg3_issuetypes (id, name, task, scope) (SELECT id, name, is_task, scope FROM bugs2_issuetypes);

#rename DATA types FROM v2 names TO v3 names
UPDATE tbg3_listtypes SET itemtype = 'category' WHERE itemtype = 'b2_categories';
UPDATE tbg3_listtypes SET itemtype = 'priority' WHERE itemtype = 'b2_prioritylevels';
UPDATE tbg3_listtypes SET itemtype = 'reproducability' WHERE itemtype = 'b2_reprotypes';
UPDATE tbg3_listtypes SET itemtype = 'resolution' WHERE itemtype = 'b2_resolutiontypes';
UPDATE tbg3_listtypes SET itemtype = 'severity' WHERE itemtype = 'b2_severitylevels';
UPDATE tbg3_listtypes SET itemtype = 'status' WHERE itemtype = 'b2_statustypes';

#import ALL issues
INSERT INTO tbg3_issues (id, issue_no, title, posted, last_updated, project_id, description, state, posted_by, owner, assigned_to, reproduction_steps, resolution, issuetype, STATUS, priority, category, severity, reproducability, estimated_hours, spent_hours, percent_complete, assigned_type, owner_type, duplicate_of, deleted, blocking, milestone, scope) (SELECT id, issue_no, title, posted, last_updated, prefix, long_description, state, posted_by, owned_by, assigned_to, reproduction, resolution, issue_type, STATUS, priority, category, severity, reproducability, estimated_time, elapsed_time, percent_complete, assigned_type, owned_type, duplicate, deleted, blocking, milestone, scope FROM bugs2_issues);

#nullify fields that were previously set to 0
UPDATE `tbg3_issues` SET `priority` = null WHERE `priority` = 0;
UPDATE `tbg3_issues` SET `severity` = null WHERE `severity` = 0;
UPDATE `tbg3_issues` SET `reproducability` = null WHERE `reproducability` = 0;
UPDATE `tbg3_issues` SET `resolution` = null WHERE `resolution` = 0;

#import ALL comments
INSERT INTO tbg3_comments (id, target_id, target_type, content, posted, updated, deleted, is_public, module, system_comment, scope, updated_by, posted_by) (SELECT id, target_id, target_type, content, posted, updated, deleted, is_public, module, system_comment, scope, updated_by, posted_by FROM bugs2_comments);

#import ALL log items
INSERT INTO tbg3_log (id, target, target_type, change_type, text, time, uid, scope) (SELECT id, target, target_type, change_type, text, time, uid, scope FROM bugs2_log);

#import "follow-up" issues, implemented AS "favourites" IN v3
INSERT INTO tbg3_userissues (SELECT * FROM bugs2_userissues);

COMMIT;


Adding v2-style workflow: 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

START TRANSACTION;

# Table structure for table `tbg3_workflows`
DROP TABLE IF EXISTS `tbg3_workflows`;
CREATE TABLE IF NOT EXISTS `tbg3_workflows` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `scope` int(10) unsigned DEFAULT '0',
  `name` varchar(200) DEFAULT NULL,
  `description` text,
  `is_active` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

# Dumping data for table `tbg3_workflows`
INSERT INTO `tbg3_workflows` (`id`, `scope`, `name`, `description`, `is_active`) VALUES
(1, 1, 'Default workflow', 'This is the default workflow. It is used by all projects with no specific workflow selected, and for issue types with no specific workflow specified. This workflow cannot be edited or removed.', 1),
(2, 1, 'TBG2-style workflow', NULL, 1);

# Table structure for table `tbg3_workflow_issuetype`
DROP TABLE IF EXISTS `tbg3_workflow_issuetype`;
CREATE TABLE IF NOT EXISTS `tbg3_workflow_issuetype` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `scope` int(10) unsigned DEFAULT '0',
  `workflow_id` int(10) unsigned DEFAULT '0',
  `workflow_scheme_id` int(10) unsigned DEFAULT '0',
  `issutype_id` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;

# Dumping data for table `tbg3_workflow_issuetype`
INSERT INTO `tbg3_workflow_issuetype` (`scope`, `workflow_id`, `workflow_scheme_id`, `issutype_id`) VALUES
(1, 2, 2, 1),
(1, 2, 2, 2),
(1, 2, 2, 3),
(1, 2, 2, 4),
(1, 2, 2, 5),
(1, 2, 2, 6),
(1, 1, 1, 1),
(1, 1, 1, 2),
(1, 1, 1, 3),
(1, 1, 1, 4),
(1, 1, 1, 5),
(1, 1, 1, 6);

# Table structure for table `tbg3_workflow_schemes`
DROP TABLE IF EXISTS `tbg3_workflow_schemes`;
CREATE TABLE IF NOT EXISTS `tbg3_workflow_schemes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `scope` int(10) unsigned DEFAULT '0',
  `name` varchar(200) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

# Dumping data for table `tbg3_workflow_schemes`
INSERT INTO `tbg3_workflow_schemes` (`id`, `scope`, `name`, `description`) VALUES
(1, 1, 'Default workflow scheme', 'This is the default workflow scheme. It is used by all projects with no specific workflow scheme selected. This scheme cannot be edited or removed.'),
(2, 1, 'TBG2-style workflow scheme', NULL);

# Table structure for table `tbg3_workflow_steps`
DROP TABLE IF EXISTS `tbg3_workflow_steps`;
CREATE TABLE IF NOT EXISTS `tbg3_workflow_steps` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `scope` int(10) unsigned DEFAULT '0',
  `status_id` int(10) unsigned DEFAULT '0',
  `workflow_id` int(10) unsigned DEFAULT '0',
  `name` varchar(200) DEFAULT NULL,
  `description` text,
  `editable` tinyint(1) DEFAULT '0',
  `closed` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=19 ;

# Dumping data for table `tbg3_workflow_steps`
INSERT INTO `tbg3_workflow_steps` (`id`, `scope`, `status_id`, `workflow_id`, `name`, `description`, `editable`, `closed`) VALUES
(1, 1, 22, 1, 'New', 'A new issue, not yet handled', 1, 0),
(2, 1, 23, 1, 'Investigating', 'An issue that is being investigated, looked into or is by other means between new and unconfirmed state', 1, 0),
(3, 1, 24, 1, 'Confirmed', 'An issue that has been confirmed', 0, 0),
(4, 1, 26, 1, 'In progress', 'An issue that is being adressed', 0, 0),
(5, 1, 28, 1, 'Ready for testing', 'An issue that has been marked fixed and is ready for testing', 0, 0),
(6, 1, 29, 1, 'Testing', 'An issue where the proposed or implemented solution is currently being tested or approved', 0, 0),
(7, 1, 25, 1, 'Rejected', 'A closed issue that has been rejected', 0, 1),
(8, 1, NULL, 1, 'Closed', 'A closed issue', 0, 1),
(17, 1, NULL, 2, 'Open', 'An open issue', 1, 0),
(18, 1, NULL, 2, 'Closed', 'A Closed issue', 1, 1);

# Table structure for table `tbg3_workflow_step_transitions`
DROP TABLE IF EXISTS `tbg3_workflow_step_transitions`;
CREATE TABLE IF NOT EXISTS `tbg3_workflow_step_transitions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `scope` int(10) unsigned DEFAULT '0',
  `workflow_id` int(10) unsigned DEFAULT '0',
  `from_step_id` int(10) unsigned DEFAULT '0',
  `transition_id` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=43 ;

# Dumping data for table `tbg3_workflow_step_transitions`
INSERT INTO `tbg3_workflow_step_transitions` (`id`, `scope`, `workflow_id`, `from_step_id`, `transition_id`) VALUES
(1, 1, 1, 1, 1),
(2, 1, 1, 1, 3),
(3, 1, 1, 1, 4),
(4, 1, 1, 1, 5),
(5, 1, 1, 2, 2),
(6, 1, 1, 2, 3),
(7, 1, 1, 2, 4),
(8, 1, 1, 2, 5),
(9, 1, 1, 3, 5),
(10, 1, 1, 3, 7),
(11, 1, 1, 4, 4),
(12, 1, 1, 4, 8),
(13, 1, 1, 4, 9),
(14, 1, 1, 5, 9),
(15, 1, 1, 5, 10),
(16, 1, 1, 6, 11),
(17, 1, 1, 6, 12),
(18, 1, 1, 7, 6),
(19, 1, 1, 8, 6),
(42, 1, 2, 17, 26),
(41, 1, 2, 17, 26),
(40, 1, 2, 18, 25),
(39, 1, 2, 18, 25);

# Table structure for table `tbg3_workflow_transitions`
DROP TABLE IF EXISTS `tbg3_workflow_transitions`;
CREATE TABLE IF NOT EXISTS `tbg3_workflow_transitions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `scope` int(10) unsigned DEFAULT '0',
  `workflow_id` int(10) unsigned DEFAULT '0',
  `name` varchar(200) DEFAULT NULL,
  `description` text,
  `outgoing_step_id` int(10) unsigned DEFAULT '0',
  `template` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;

# Dumping data for table `tbg3_workflow_transitions`
INSERT INTO `tbg3_workflow_transitions` (`id`, `scope`, `workflow_id`, `name`, `description`, `outgoing_step_id`, `template`) VALUES
(1, 1, 1, 'Investigate issue', 'Assign the issue to yourself and start investigating it', 2, NULL),
(2, 1, 1, 'Request more information', 'Move issue back to new state for more details', 1, NULL),
(3, 1, 1, 'Confirm issue', 'Confirm that the issue is valid', 3, NULL),
(4, 1, 1, 'Reject issue', 'Reject the issue as invalid', 7, 'main/updateissueproperties'),
(5, 1, 1, 'Accept issue', 'Accept the issue and assign it to yourself', 4, NULL),
(6, 1, 1, 'Reopen issue', 'Reopen the issue', 1, NULL),
(7, 1, 1, 'Assign issue', 'Accept the issue and assign it to someone', 4, 'main/updateissueproperties'),
(8, 1, 1, 'Mark ready for testing', 'Mark the issue as ready to be tested', 5, NULL),
(9, 1, 1, 'Resolve issue', 'Resolve the issue', 8, 'main/updateissueproperties'),
(10, 1, 1, 'Test issue solution', 'Check whether the solution is valid', 6, NULL),
(11, 1, 1, 'Accept issue solution', 'Mark the issue as resolved', 8, 'main/updateissueproperties'),
(12, 1, 1, 'Reject issue solution', 'Reject the proposed solution and mark the issue as in progress', 4, NULL),
(25, 1, 2, 'Reopen issue', 'Reopen this issue', 17, 'main/updateissueproperties'),
(26, 1, 2, 'Close issue', 'Close this issue', 18, 'main/updateissueproperties');

# Table structure for table `tbg3_workflow_transition_actions`
DROP TABLE IF EXISTS `tbg3_workflow_transition_actions`;
CREATE TABLE IF NOT EXISTS `tbg3_workflow_transition_actions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `scope` int(10) unsigned DEFAULT '0',
  `action_type` varchar(100) DEFAULT NULL,
  `target_value` varchar(200) DEFAULT NULL,
  `transition_id` int(10) unsigned DEFAULT '0',
  `workflow_id` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=53 ;

# Dumping data for table `tbg3_workflow_transition_actions`
INSERT INTO `tbg3_workflow_transition_actions` (`id`, `scope`, `action_type`, `target_value`, `transition_id`, `workflow_id`) VALUES
(1, 1, 'assign_self', '0', 1, 1),
(2, 1, 'clear_assignee', '0', 2, 1),
(3, 1, 'set_percent', '10', 3, 1),
(4, 1, 'set_resolution', '0', 4, 1),
(5, 1, 'set_percent', '100', 4, 1),
(6, 1, 'user_stop_working', '0', 4, 1),
(7, 1, 'assign_self', '0', 5, 1),
(8, 1, 'user_start_working', '0', 5, 1),
(9, 1, 'clear_resolution', '0', 6, 1),
(10, 1, 'clear_percent', '0', 6, 1),
(11, 1, 'assign_user', '0', 7, 1),
(12, 1, 'user_start_working', '0', 7, 1),
(13, 1, 'clear_assignee', '0', 8, 1),
(14, 1, 'user_stop_working', '0', 8, 1),
(15, 1, 'set_status', '0', 9, 1),
(16, 1, 'set_resolution', '0', 9, 1),
(17, 1, 'user_stop_working', '0', 9, 1),
(18, 1, 'assign_self', '0', 10, 1),
(19, 1, 'user_start_working', '0', 10, 1),
(20, 1, 'set_resolution', '0', 11, 1),
(21, 1, 'clear_assignee', '0', 11, 1),
(22, 1, 'user_stop_working', '0', 11, 1),
(23, 1, 'set_resolution', '0', 12, 1),
(24, 1, 'clear_assignee', '0', 12, 1),
(25, 1, 'user_stop_working', '0', 12, 1),
(26, 1, 'assign_self', '0', 13, 2),
(27, 1, 'clear_assignee', '0', 14, 2),
(28, 1, 'set_percent', '10', 15, 2),
(29, 1, 'set_resolution', '0', 16, 2),
(30, 1, 'set_percent', '100', 16, 2),
(31, 1, 'user_stop_working', '0', 16, 2),
(32, 1, 'assign_self', '0', 17, 2),
(33, 1, 'user_start_working', '0', 17, 2),
(34, 1, 'clear_resolution', '0', 18, 2),
(35, 1, 'clear_percent', '0', 18, 2),
(36, 1, 'assign_user', '0', 19, 2),
(37, 1, 'user_start_working', '0', 19, 2),
(38, 1, 'clear_assignee', '0', 20, 2),
(39, 1, 'user_stop_working', '0', 20, 2),
(40, 1, 'set_status', '0', 21, 2),
(41, 1, 'set_resolution', '0', 21, 2),
(42, 1, 'user_stop_working', '0', 21, 2),
(43, 1, 'assign_self', '0', 22, 2),
(44, 1, 'user_start_working', '0', 22, 2),
(45, 1, 'set_resolution', '0', 23, 2),
(46, 1, 'clear_assignee', '0', 23, 2),
(47, 1, 'user_stop_working', '0', 23, 2),
(48, 1, 'set_resolution', '0', 24, 2),
(49, 1, 'clear_assignee', '0', 24, 2),
(50, 1, 'user_stop_working', '0', 24, 2),
(51, 1, 'set_resolution', '', 26, 2),
(52, 1, 'set_status', '', 26, 2);

# Table structure for table `tbg3_workflow_transition_validation_rules`
DROP TABLE IF EXISTS `tbg3_workflow_transition_validation_rules`;
CREATE TABLE IF NOT EXISTS `tbg3_workflow_transition_validation_rules` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `scope` int(10) unsigned DEFAULT '0',
  `pre_or_post` varchar(4) DEFAULT NULL,
  `rule` varchar(100) DEFAULT NULL,
  `rule_value` varchar(200) DEFAULT NULL,
  `transition_id` int(10) unsigned DEFAULT '0',
  `workflow_id` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

# Dumping data for table `tbg3_workflow_transition_validation_rules`
INSERT INTO `tbg3_workflow_transition_validation_rules` (`id`, `scope`, `pre_or_post`, `rule`, `rule_value`, `transition_id`, `workflow_id`) VALUES
(1, 1, 'pre', 'max_assigned_issues', '5', 1, 1),
(2, 1, 'post', 'valid_resolution', '15,14,18,13', 4, 1),
(3, 1, 'pre', 'max_assigned_issues', '5', 5, 1),
(4, 1, 'post', 'valid_status', '30,31,32,33', 9, 1),
(5, 1, 'post', 'valid_resolution', '17,14,16', 9, 1),
(6, 1, 'pre', 'max_assigned_issues', '5', 13, 2),
(7, 1, 'post', 'valid_resolution', '15,14,18,13', 16, 2),
(8, 1, 'pre', 'max_assigned_issues', '5', 17, 2),
(9, 1, 'post', 'valid_status', '30,31,32,33', 21, 2),
(10, 1, 'post', 'valid_resolution', '17,14,16', 21, 2);

# Set issues to correct workflow step
UPDATE `tbg3_issues` SET `workflow_step_id` = 18 WHERE `state` = 1;
UPDATE `tbg3_issues` SET `workflow_step_id` = 17 WHERE `state` = 0;

# Set workflow schemes
UPDATE `tbg3_projects` SET `workflow_scheme_id` = 2;

COMMIT;


Attachments 0

Comments 1

CharlesB
Aug 31, 2011 (06:37)
Cancel
It's worth mentioning that this script is not meant to be run on a 3.1.x, as the recommended upgrade path is 2.1.4 -> 3.0.x -> 3.1.x