View Issue Details

WikiJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0008071mantisbtmigrationpublic2007-06-15 12:572012-10-08 05:31
Reportermarc Assigned Todregad  
PrioritynormalSeverityminorReproducibilityN/A
Status closedResolutionno change required 
Product Version1.0.7 
Summary0008071: Migration script for Bugzilla 2.16 (script by Cris Daniluk)
Description

I rediscovered Cris' script here:
http://web.archive.org/web/*/http://crisdaniluk.com/bugzilla2mantis/bugzilla2mantis.pl

I made some changes, and added the import of attachments. I used it to successfully convert a Bugzilla 2.16.7 installation to Mantis 1.0.7.

Script is attached.

Tagspatch
Attached Files
bugzilla2mantis.pl (28,256 bytes)    
#!/usr/bin/perl
#############################################################################
# bugzilla2mantis.pl                                                        #
# Script to convert data in bugzilla (2.16-18) to Mantis (0.19.0)           #
# Probably works with other versions of bugzilla as its datamodel is fairly #
# stable. Likely to work with many versions of Mantis as well, as recent    #
# functionality is either not supported by Bugzilla or not imported.        #
# Relationships require at least Mantis 0.19.0. Everything else should work #
# on Mantis 0.18.3 (and all of 0.18.x theoretically). If you have luck using#
# specific releases of either Mantis or Bugzilla, please send them to       #
# cris@crisdaniluk.com.                                                     #
#                                                                           #
#############################################################################

# -------- begin -------
# The original web site for this script doesn't exist anymore, but
# I rediscovered this script here:
# http://web.archive.org/web/*/http://crisdaniluk.com/bugzilla2mantis/bugzilla2mantis.pl
# 
# I made some small changes, and added the possibility to
# import attachments. Also I had to add a conversion from 
# utf8 to latin1, see end of script.
# I prefixed all my changes with # Marc:
# 
# I used this script successfully to convert a Bugzilla 2.16.7 database
# to Mantis 1.0.7.
# 
# Marc, yoda at schli dot ch
# -------- end --------

# LICENSE                                                                   
# 1 You may use this code freely and without any obligation to me. 
# 2 You may use it in commercial applications. 
# 3 You may embed it. You may change it. 
# 4 You may print out thousands of copies to wallpaper your bedroom.
# 5 You may even hide the fact that this was written by someone else
# 6 BUT - You may NOT say you wrote it
# 7 AND - You may spontaneously lose all of your information in Bugzilla and
#   have unforseen problems in Mantis. I do not represent that this code 
#   works. You can make that determination on your own, after reviewing the 
#   code.
# 8 You should let me know what versions it does and does not work with
# 9 You should (but aren't obligated to)send me any enhancements you make. 
#   If you choose to, you understand your enhancements will be subject to 
#   these terms. You will be given credit.
# 

# Things not imported
# Attachments - Because this does not directly load into Mantis, but
#               instead creates a SQL dump file for you, it can't really
#               handle the attachment blobs. I'm working on a separate
#               script to handle the attachment migration.
#     (Marc: this works now)
# Milestones - mantis only uses versions. Versions can easily include
# milestones, which is I think what most people do in Bugzilla as well.
# If you need milestones that exist separate from versions, I recommend
# either adding a custom field for milestone in mantis (a GREAT mantis
# feature), or appending them to versions (i.e. "1.0 M1", "1.0 M2", ...)
# Field Defs - This is a funky feature that doesn't have a direct match in
#              Mantis. If someone thinks this might be useful to massage
#              and import, please let me know, but its really useless...
# Flags
# Named Queries - These are user level saved queries. Bugzilla uses a weird
#                 format to store these... not worth it.
# Quips - I love quips, but I REALLY love not having people ask me if I
#         spent company time writing all of them
# Series 
# Tokens
# Votes - what a stupid feature


# Things sort of imported
# Users - permissions just don't match from Bugzilla to Mantis. Bugzilla
#         has a lot more "horizontal" permissions but Mantis has a lot of
#         vertical control (project specific access). Unfortunately you'll
#         have to update permissions by hand, but this shouldn't be a big
#         deal unless this is a huge migration. If so, look at emailflags
#         ALSO, passwords are not imported so the DEFAULT mantis password
#         is used. This is 'root'. 
#         If you have a user with ID 1 in bugzilla, it will become the
#         admin user, overwriting the existing admin account. There is no
#         easy way around this since this preserves all IDs

# IMPORT SETTINGS

# Import relationships?
$importRelationships = 1; # 1/0

# Merge - You can choose to merge Bugzilla into your existing
# Mantis project. Note that at present time, consolidation is not
# done. Users, projects, etc will be duplicated. If you need to have
# this not happen, it is fairly easy to do by manually adding the
# users and/or projects and circumventing that part of the import.
# THIS IS NOT COMPLETELY IMPLEMENTED YET.
#$preserveIds = 1; # 1/0

$sqlDumpFile = 'bugzilla2mantis.sql';

# DATABASE CONNECTION SETTINGS
# This was designed for MySQL. MySQL automatically
# updates the sequences if they're bypassed. Postgres
# and Oracle won't. Be careful if you're not using MySQL.
$b_dsn = 'dbi:mysql:bugs:localhost:3306';
$b_user = 'bugs';
$b_password = '!bugs123';

# VERSION SETTINGS

# Bugzillas previous to 2.18 did not have their own project id or
# private notes. If you're using 2.18, set these to 1.
$bugzillaHasProjectID = 0;
$bugzillaHasPrivateNotes = 0;

# BUGZILLA => MANTIS ATTRIBUTE MAPPINGS
# Feel free to customize these. Sometimes Mantis or Bugzilla would have
# a unique attribute (e.g. Bugzilla has 9 priorities, but Mantis has 6)
# so I choose some conservative defaults. You probably don't care.
$bgzPriority{'P1'} = 10;
$bgzPriority{'P2'} = 20;
$bgzPriority{'P3'} = 20;
$bgzPriority{'P4'} = 30;
$bgzPriority{'P5'} = 30;
$bgzPriority{'P6'} = 40;
$bgzPriority{'P7'} = 40;
$bgzPriority{'P8'} = 50;
$bgzPriority{'P9'} = 60;

# Mantis has no normal so I demoted minor to trivial to squeeze in normal
# Marc: added all the existing mantis status
$bgzSeverity{'enhancement'} = 10; #feature
$bgzSeverity{'trivial'} = 20; #trivial
#                        30; #text
$bgzSeverity{'minor'} = 40; #tweak
$bgzSeverity{'normal'} = 50; #minor
$bgzSeverity{'major'} = 60; #major
$bgzSeverity{'critical'} = 70; #crash
$bgzSeverity{'blocker'} = 80; #block

$bgzStatus{'UNCONFIRMED'} = 10; # new
$bgzStatus{'NEW'} = 10; # new
$bgzStatus{'ASSIGNED'} = 50; # assigned
$bgzStatus{'REOPENED'} = 20; # SPECIAL CASE - Changes resolution!
$bgzStatus{'RESOLVED'} = 80; # resolved
$bgzStatus{'VERIFIED'} = 40; # confirmed
$bgzStatus{'CLOSED'} = 90; # closed

$bgzResolution{'FIXED'} = 20; # fixed
$bgzResolution{'INVALID'} = 70; # not a bug
$bgzResolution{'WONTFIX'} = 90; # wont fix
$bgzResolution{'LATER'} = 80; # suspended
$bgzResolution{'REMIND'} = 80; # suspended
$bgzResolution{'DUPLICATE'} = 60; # duplicate
$bgzResolution{'WORKSFORME'} = 40; # unable to duplicate
$bgzResolution{'MOVED'} = 10; # open
$bgzResolution{'REOPENED'} = 30; # reopened


# END OF CONFIGURABLE STUFF

use DBI;
use POSIX;


use 5.004;

open(SQL, ">$sqlDumpFile")or die('Could not open SQL dump file');

my $b_dbh = DBI->connect($b_dsn, $b_user, $b_password);

# BUGZILLA PROFILES => MANTIS USERS
# id <= userid
# username <= login_name
# realname <= realname
# email <= login_name
# password <= cryptpassword
# date_created = blank
# last_visit = blank
# enabled = 1
# protected = 0
# access_level = 10
# login_count = 0
# cookie_string = blank
my @users;
{ 

  my $cookieCounter = 1; # This is dumb. Even for Mantis..
	my $strQuery = "SELECT userid, login_name, realname, cryptpassword FROM profiles";
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  while (my ($id, $name, $realname, $passwd) = $qry->fetchrow) {

    my %user;
    $user{'id'} = $id;
    $user{'username'} = $name;
    $user{'email'} = $name;
    $user{'realname'} = $realname;
    $user{'password'} = '63a9f0ea7bb98050796b649e85481845';
    $user{'date_created'} = '';
    $user{'last_visit'} = '';
    $user{'enabled'} = 1;
    $user{'protected'} = 0;
    $user{'access_level'} = 10;
    $user{'login_count'} = 0;
    $user{'cookie_string'} = 'cookiestring'.++$cookieCounter;

    # Admin override..
    if ($id == 1) {
      $user{'access_level'} = 90;
    }

    push(@users, \%user);

  }

  foreach $user (@users) {
    print SQL "INSERT INTO mantis_user_table (id, username, realname, email, password, date_created, last_visit, enabled, protected, access_level, login_count, cookie_string) VALUES(";
    print SQL $b_dbh->quote(${$user}{'id'}).", ";
    print SQL $b_dbh->quote(${$user}{'username'}).", ";
    print SQL $b_dbh->quote(${$user}{'realname'}).", ";
    print SQL $b_dbh->quote(${$user}{'email'}).", ";
    print SQL $b_dbh->quote(${$user}{'password'}).", ";
    print SQL $b_dbh->quote(${$user}{'date_created'}).", ";
    print SQL $b_dbh->quote(${$user}{'last_visit'}).", ";
    print SQL $b_dbh->quote(${$user}{'enabled'}).", ";
    print SQL $b_dbh->quote(${$user}{'protected'}).", ";
    print SQL $b_dbh->quote(${$user}{'access_level'}).", ";
    print SQL $b_dbh->quote(${$user}{'login_count'}).", ";
    print SQL $b_dbh->quote(${$user}{'cookie_string'}).");\n";
  }
}

# Milestoning and voting are not portable concepts in Mantis
# Milestones will be ported, but not the default project settings.
# enabled <= disallownew
# access_min = 10 (public)
# view_state = 10 (public)
# file_path = nil
# status = 10
my @projects;
my %projectNameIdMap;

{
 
  my $projectId = 0;
  my $strQuery;
  if ($bugzillaHasProjectID == 1) {
    $strQuery = "SELECT id, name, description, disallownew FROM products";
  } else {
    $strQuery = "SELECT 1, product AS name, description, disallownew FROM products";
  }

  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  while (my ($id, $name, $desc, $disallow) = $qry->fetchrow) {

    my %project;
    if ($bugzillaHasProjectID == 1) {
      $project{'id'} = $id;
    } else {
      $project{'id'} = ++$projectId;
    }
    $projectNameIdMap{$name} = $project{'id'};

    $project{'name'} = $name;
    $project{'description'} = $desc;
    # Marc: fixed, was: = disallow;
    $project{'enabled'} = !$disallow;
    $project{'access_min'} = 10;
    $project{'view_state'} = 10;
    $project{'file_path'} = '';
    $project{'status'} = 10;
    push(@projects, \%project);

  }

  foreach $project (@projects) {
    print SQL "INSERT INTO mantis_project_table (id, name, status, enabled, view_state, access_min, file_path, description) VALUES(";
    print SQL $b_dbh->quote(${$project}{'id'}).", ";
    print SQL $b_dbh->quote(${$project}{'name'}).", ";
    print SQL $b_dbh->quote(${$project}{'status'}).", ";
    print SQL $b_dbh->quote(${$project}{'enabled'}).", ";
    print SQL $b_dbh->quote(${$project}{'view_state'}).", ";
    print SQL $b_dbh->quote(${$project}{'access_min'}).", ";
    print SQL $b_dbh->quote(${$project}{'file_path'}).", ";
    print SQL $b_dbh->quote(${$project}{'description'}).");\n";
  }
}

# BUGZILLA COMPONENTS => MANTIS CATEGORIES
# project_id <= product_id*
# category <= name
# user_id <= initialowner*
# Mantis does not use IDs for category names, so category names within
# a project MUST be unique, which is not the case in Bugzilla. To make
# sure this is not a problem, we will append the Bugzilla id to the name.
# Note that this ID will not have any intrinsic meaning in Mantis itself,
# but will allow an administrator to rename components if necessary.
my @components;
my %categoryNameIdMap;
{

  my $strQuery;
  my $categoryId = 0;
  if ($bugzillaHasProjectID == 1) {
    $strQuery = "SELECT id, name, product_id, initialowner FROM components ORDER BY product_id";
  } else {
    $strQuery = "SELECT 1, value, program, initialowner FROM components ORDER BY program";
  }
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $lastProduct = -1;
  my %categoryNames;

  while (my ($id, $name, $product, $owner) = $qry->fetchrow) {

    # If new product(ject), reset the dupe check
    if (($bugzillaHasProjectID == 1) && ($lastProduct != $product)) {
      %categoryNames = {};
    } elsif (($bugzillaHasProjectID) == 0 && ($lastProduct ne $product)) {
      %categoryNames = {};
    }
    # Check to see that this name doesn't already exist
    if (exists $categoryNames{$name}) {
     $name .= ' '.$id;
    }

    my %category; 
    if ($bugzillaHasProjectID == 1) {
      $category{'project_id'} = $product;
    } else {
      $category{'project_id'} = $projectNameIdMap{$product};
    }
    $category{'category'} = $name;
    $category{'user_id'} = $owner;

    $categoryNameIdMap{$category{'name'}} = $category{'id'};

    # Store grouping info for dupe check
    $categoryNames{$name} = $name;
    $lastProduct = $product;

    push(@categories, \%category);

  }

  foreach $category (@categories) {
    print SQL "INSERT INTO mantis_project_category_table (project_id, category, user_id) VALUES(";
    print SQL $b_dbh->quote(${$category}{'project_id'}).", ";
    print SQL $b_dbh->quote(${$category}{'category'}).", ";
    print SQL $b_dbh->quote(${$category}{'user_id'}).");\n";
  } 
}

# BUGZILLA VERSIONS => MANTIS VERSIONS
# id = generated
# project_id <= product_id*
# version <= value
# date_order = now
# description = blank
# released = 0

my @versions;
{
  my $strQuery;
  if ($bugzillaHasProjectID == 1) {
    $strQuery = "SELECT value, product_id FROM versions";
  } else {
    $strQuery = "SELECT value, program FROM versions";
  }

  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $verID = 1;

  while (my ($value, $product) = $qry->fetchrow) {

    my %version;
    $version{'id'} = $verID++;
    if ($bugzillaHasProjectID == 1) {
      $version{'project_id'} = $product;
    } else {
      $version{'project_id'} = $projectNameIdMap{$product};
    }

    $version{'version'} = $value;
    $version{'description'} = '';
    $version{'date_order'} = '';
    $version{'released'} = '0';
 
    push(@versions, \%version);

  }

  foreach $version (@versions) {
    print SQL "INSERT INTO mantis_project_version_table (id, project_id, version, date_order, description, released) VALUES(";
    print SQL $b_dbh->quote(${$version}{'id'}).", ";
    print SQL $b_dbh->quote(${$version}{'project_id'}).", ";
    print SQL $b_dbh->quote(${$version}{'version'}).", ";
    print SQL $b_dbh->quote(${$version}{'date_order'}).", ";
    print SQL $b_dbh->quote(${$version}{'description'}).", ";
    print SQL $b_dbh->quote(${$version}{'released'}).");\n";
  }

}

# BUGZILLA BUGS => MANTIS BUGS
# id <= bug_id
# project_id <= product_id*
# reporter_id <= reporter
# handler_id <= assigned_to 
# duplicate_id <= blank
# priority <= priority
# severity <= bug_severity
# reproducibility <= 10
# status <= bug_status
# resolution <= resolution
# projection <= 10
# category <= component_id
# date_submitted <= creation_ts
# last_updated <= delta_ts
# eta <= 10
# bug_text_id <= -1 (set later)
# os <= op_sys
# os_build <= blank
# platform <= rep_platform
# version <= version
# fixed_in_version <= target_milestone
# build <= blank
# profile_id <= null
# view_state <= 10
# summary <= short_desc
# sponsorship_total <= null
my %bugs;
{
  my $strQuery;

  if ($bugzillaHasProjectID == 1) {
    $strQuery = "SELECT bug_id, assigned_to, bug_file_loc, bug_severity, bug_status, creation_ts, delta_ts, short_desc, op_sys, priority, product_id, rep_platform, reporter, version, component_id, resolution, target_milestone, short_desc FROM bugs";
  } else {
    $strQuery = "SELECT bug_id, assigned_to, bug_file_loc, bug_severity, bug_status, creation_ts, delta_ts, short_desc, op_sys, priority, product, rep_platform, reporter, version, component, resolution, target_milestone, short_desc FROM bugs";
  }


  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;


  while (my ($bug_id, $assigned_to, $bug_file_loc, $bug_severity, $bug_status, $creation_ts, $delta_ts, $short_desc, $op_sys, $priority, $product_id, $rep_platform, $reporter, $version, $component_id, $resolution, $target_milestone, $short_desc) = $qry->fetchrow) {

    my %bug;
    
    $bug{'id'} = $bug_id;
    
    if ($bugzillaHasProjectID == 1) {
      $bug{'project_id'} = $product_id;
      $bug{'category'} = $component_id;
    } else {
      $bug{'project_id'} = $projectNameIdMap{$product_id};
      $bug{'category'} = $categoryNameIdMap{$component_id};
    }

    $bug{'reporter_id'} = $reporter;
    $bug{'handler_id'} = $assigned_to;
    $bug{'duplicate_id'} = ''; # null
    $bug{'priority'} = $bgzPriority{$priority};
    if (!(exists $bgzPriority{$priority})) { 
      $bug{'priority'} = 50;
    }
    $bug{'severity'} = $bgzSeverity{$bug_severity};
    $bug{'reproducibility'} = 10;
    $bug{'status'} = $bgzStatus{$bug_status};
    $bug{'resolution'} = $bgzResolution{$resolution};
    $bug{'projection'} = 10;
    $bug{'category'} = $component_id;
    $bug{'date_submitted'} = $creation_ts;
    $bug{'last_updated'} = $delta_ts;
    $bug{'eta'} = 10;
    $bug{'bug_text_id'} = -1;
    $bug{'os'} = $op_sys;
    $bug{'os_build'} = '';
    $bug{'platform'} = $rep_platform;
    $bug{'version'} = $version;
    $bug{'fixed_in_version'} = $target_milestone;
    $bug{'build'} = '';
    $bug{'profile_id'} = ''; # null
    $bug{'view_state'} = 10;
    # Marc: convert to iso
    $bug{'summary'} = toISO($short_desc);
    $bug{'sponsorship_total'} = ''; # null
    
    # If status is reopened, status should be assigned
    # and resolution should be REOPENED
    # Marc: changed, didn't make sense.
    if ($bug_status eq 'REOPENED') {
      $bug{'resolution'} = $bgzResolution{'REOPENED'};
    }
    if ($bug{'resolution'} == '') {
      $bug{'resolution'} = 10;
    }

    $bugs{$bug_id} = \%bug;

  }
  
# We don't iterate bugs out to the db yet to prevent a RI problem
}

# BUGZILLA LONGDESCS => MANTIS BUGTEXTS/BUGNOTES
# Mantis has a separate table for the first bug note, which it considers
# to be the bug description. Bugzilla doesn't have the idea of a
# description. In order to make Mantis work as it should, the first 
# longdesc for each bug will be the bug description. Subsequent ones
# will be bugnotes.
#
# DESCS
# id <= generated
# description <= thetext
# steps_to_reproduce <= blank
# additional_information <= blank
# 
# NOTES
# id <= generated
# bug_id <= bug_id
# reporter_id <= who
# bugnote_text_id <= generated
# view_state <= isprivate (10/50)
# date_submitted <= bug_when
# last_modified <= nil
#
# NOTES (text ref)
# id <= generated
# note <= thetext

my @bugdescs;
my @bugnotes;
{
  my $strQuery;
  if ($bugzillaHasPrivateNotes == 1) {
    $strQuery = "SELECT bug_id, who, bug_when, thetext, isprivate FROM longdescs ORDER BY bug_id";
  } else {
    $strQuery = "SELECT bug_id, who, bug_when, thetext, 10 FROM longdescs ORDER BY bug_id";
  }

  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $lastBug = -1;
  my $bugNoteId = 0;
  my $bugDescId = 0;

  while (my ($bug_id, $who, $bug_when, $thetext, $isprivate) = $qry->fetchrow) {


    if ($lastBug == $bug_id) {
      # Bugnote
      my %note;
      $note{'id'} = ++$bugNoteId;
      $note{'bug_id'} = $bug_id;
      $note{'reporter_id'} = $who;
      $note{'bugnote_text_id'} = $bugNoteId;
      $note{'view_state'} = $isprivate==0?50:10;
      $note{'date_submitted'} = $bug_when;
      $note{'last_modified'} = '';
      $note{'note'} = toISO($thetext);
      push(@bugnotes, \%note);
    } else {
      my %desc;
      $desc{'id'} = ++$bugDescId;
      $desc{'description'} = toISO($thetext);
      push(@bugdescs, \%desc);
   
      # We also have to update the bug...
      $bug = $bugs{$bug_id};
      if ($bug != null) {
        # Marc: didn't work
        #${$bug}{'bug_text_id'} = $bugDescId;
        #$bugs{$bug_id} = \%bug;
        $$bug{'bug_text_id'} = $bugDescId;
      }
    }

    $lastBug = $bug_id;

  }

  foreach $desc (@bugdescs) {
    print SQL "INSERT INTO mantis_bug_text_table (id, description, steps_to_reproduce, additional_information) VALUES(";
    print SQL $b_dbh->quote(${$desc}{'id'}).", ";
    print SQL $b_dbh->quote(${$desc}{'description'}).", ";
#    print SQL $b_dbh->quote(${$desc}{'steps_to_reproduce'}).", ";
#    print SQL $b_dbh->quote(${$desc}{'additional_information'}).");\n";
    print SQL $b_dbh->quote("").", ";
    print SQL $b_dbh->quote("").");\n";
  } 

  foreach $note (@bugnotes) {
    print SQL "INSERT INTO mantis_bugnote_text_table (id, note) VALUES(";
    print SQL $b_dbh->quote(${$note}{'id'}).", ";
    print SQL $b_dbh->quote(${$note}{'note'}).");\n";

    print SQL "INSERT INTO mantis_bugnote_table (id, bug_id, reporter_id, bugnote_text_id, view_state, date_submitted, last_modified) VALUES(";
    print SQL $b_dbh->quote(${$note}{'id'}).", ";
    print SQL $b_dbh->quote(${$note}{'bug_id'}).", ";
    print SQL $b_dbh->quote(${$note}{'reporter_id'}).", ";
    print SQL $b_dbh->quote(${$note}{'bugnote_text_id'}).", ";
    print SQL $b_dbh->quote(${$note}{'view_state'}).", ";
    print SQL $b_dbh->quote(${$note}{'date_submitted'}).", ";
    print SQL $b_dbh->quote(${$note}{'last_modified'}).");\n";


  } 
}

# now iterate out the bugs themselves..
foreach $bug (values %bugs) {
#  $bug = $bugs{$bugid};
  print SQL "INSERT INTO mantis_bug_table (id, project_id, reporter_id, handler_id, duplicate_id, priority, severity, reproducibility, status, resolution, projection, category, date_submitted, last_updated, eta, bug_text_id, os, os_build, platform, version, fixed_in_version, build, profile_id, view_state, summary, sponsorship_total) VALUES(";
  print SQL $b_dbh->quote(${$bug}{'id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'project_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'reporter_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'handler_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'duplicate_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'priority'}).", ";
  print SQL $b_dbh->quote(${$bug}{'severity'}).", ";
  print SQL $b_dbh->quote(${$bug}{'reproducibility'}).", ";
  print SQL $b_dbh->quote(${$bug}{'status'}).", ";
  print SQL $b_dbh->quote(${$bug}{'resolution'}).", ";
  print SQL $b_dbh->quote(${$bug}{'projection'}).", ";
  print SQL $b_dbh->quote(${$bug}{'category'}).", ";
  print SQL $b_dbh->quote(${$bug}{'date_submitted'}).", ";
  print SQL $b_dbh->quote(${$bug}{'last_updated'}).", ";
  print SQL $b_dbh->quote(${$bug}{'eta'}).", ";
  print SQL $b_dbh->quote(${$bug}{'bug_text_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'os'}).", ";
  print SQL $b_dbh->quote(${$bug}{'os_build'}).", ";
  print SQL $b_dbh->quote(${$bug}{'platform'}).", ";
  print SQL $b_dbh->quote(${$bug}{'version'}).", ";
  print SQL $b_dbh->quote(${$bug}{'fixed_in_version'}).", ";
  print SQL $b_dbh->quote(${$bug}{'build'}).", ";
  print SQL $b_dbh->quote(${$bug}{'profile_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'view_state'}).", ";
  print SQL $b_dbh->quote(${$bug}{'summary'}).", ";
  print SQL $b_dbh->quote(${$bug}{'sponsorship_total'}).");\n";
}

# BUGZILLA DEPENDENCIES => MANTIS RELATIONSHIPS
# id = generated
# source_bug_id <= blocked
# destination_bug_id <= dependson
# relationship_type = 1 - This is a related to, not child of. 
# Mantis doesn't have a specific blocker relationship type now.

my @relationships;

if ($importRelationships == 1) {

  my $strQuery = "SELECT blocked, dependson FROM dependencies";
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $relID = 1;

  while (my ($blocked, $dependson) = $qry->fetchrow) {

    my %relationship;
    $relationship{'id'} = $relID++;
    $relationship{'source_bug_id'} = $blocked;
    $relationship{'destination_bug_id'} = $dependson;
    $relationship{'relationship_type'} = '1';
 
    push(@relationships, \%relationship);

  }

  foreach $relationship (@relationships) {
    print SQL "INSERT INTO mantis_bug_relationship_table (id, source_bug_id, destination_bug_id, relationship_type) VALUES(";
    print SQL $b_dbh->quote(${$relationship}{'id'}).", ";
    print SQL $b_dbh->quote(${$relationship}{'source_bug_id'}).", ";
    print SQL $b_dbh->quote(${$relationship}{'destination_bug_id'}).", ";
    print SQL $b_dbh->quote(${$relationship}{'relationship_type'}).");\n";
  }

}

# Need to cache Bugzilla fields for activity
my %fieldNameIdMap;
{
  my $strQuery = "SELECT fieldid, description FROM fielddefs";
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;
  
  while (my ($id, $desc) = $qry->fetchrow) {
    
    $fieldNameIdMap{$id} = $desc;

  }
}

# BUGZILLA ACTIVITY => MANTIS HISTORY
# id <= generated
# user_id <= wo
# bug_id <= bug_id
# date_modified <= bug_when
# field_name <= fieldid
# new_value <= added
# old_value <= removed
# type <= 
my @history;
{ 

  my $activityId = 0;

  my $strQuery = "SELECT bug_id, who, bug_when, fieldid, added, removed FROM bugs_activity";
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  while (my ($bug_id, $who, $bug_when, $fieldid, $added, $removed) = $qry->fetchrow) {

    my %elem;
    $elem{'id'} = ++$activityId;
    $elem{'bug_id'} = $bug_id;
    $elem{'date_modified'} = $bug_when;
    $elem{'user_id'} = $who;
    $elem{'field_name'} = $fieldNameIdMap{$fieldid};
    $elem{'new_value'} = $added;
    $elem{'old_value'} = $removed;
    $elem{'type'} = 0;

    push(@history, \%elem);

  }

  foreach $elem (@history) {
    print SQL "INSERT INTO mantis_bug_history_table (id, user_id, bug_id, date_modified, field_name, old_value, new_value, type) VALUES(";
    print SQL $b_dbh->quote(${$elem}{'id'}).", ";
    print SQL $b_dbh->quote(${$elem}{'user_id'}).", ";
    print SQL $b_dbh->quote(${$elem}{'bug_id'}).", ";
    print SQL $b_dbh->quote(${$elem}{'date_modified'}).", ";
    print SQL $b_dbh->quote(${$elem}{'field_name'}).", ";
    print SQL $b_dbh->quote(${$elem}{'old_value'}).", ";
    print SQL $b_dbh->quote(${$elem}{'new_value'}).", ";
    print SQL $b_dbh->quote(${$elem}{'type'}).");\n";
  }
}


# Marc: convert attachments

# BUGZILLA ATTACHMENTS => MANTIS BUG_FILE
# id = generated
# bug_id <= bug_id*
# title <= blank
# description = description
# diskfile = ????
# filename = filename
# folder = blank
# filesize = generated
# file_type = mimetype
# date_added = creation_ts
# content = thedata

my @files;
{
  my $strQuery;
  $strQuery = "SELECT bug_id, description, filename, mimetype, creation_ts, thedata FROM attachments";

  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $fileID = 1;

  while (my ($bug_id, $description, $filename, $mimetype, $creation_ts, $thedata) = $qry->fetchrow) {

    my %file;
    $file{'id'} = $fileID++;
    $file{'bug_id'} = $bug_id;

    $file{'title'} = $description;
    $file{'description'} = '';
    $file{'diskfile'} = '';
    $file{'filename'} = $filename;
    $file{'folder'} = '';
    $file{'filesize'} = length($thedata);
    $file{'file_type'} = $mimetype;
    $file{'date_added'} = $creation_ts;
    $file{'content'} = $thedata;

    push(@files, \%file);

  }

  foreach $file (@files) {
    print SQL "INSERT INTO mantis_bug_file_table (id, bug_id, title, description, diskfile, filename, folder, filesize, file_type, date_added, content) VALUES(";
    print SQL $b_dbh->quote(${$file}{'id'}).", ";
    print SQL $b_dbh->quote(${$file}{'bug_id'}).", ";
    print SQL $b_dbh->quote(${$file}{'title'}).", ";
    print SQL $b_dbh->quote(${$file}{'description'}).", ";
    print SQL $b_dbh->quote(${$file}{'diskfile'}).", ";
    print SQL $b_dbh->quote(${$file}{'filename'}).", ";
    print SQL $b_dbh->quote(${$file}{'folder'}).", ";
    print SQL $b_dbh->quote(${$file}{'filesize'}).", ";
    print SQL $b_dbh->quote(${$file}{'file_type'}).", ";
    print SQL $b_dbh->quote(${$file}{'date_added'}).", ";
    print SQL $b_dbh->quote(${$file}{'content'}).");\n";
  }

}

# Marc: convert some text to iso

use Unicode::String qw(latin1 utf8);

sub toISO($) {
        my $text = $_[0];
        # if this host was UTF-8 encoded:
        my $text_iso  = (utf8($text))->latin1;
        my $text_utf8 = (latin1($text_iso))->utf8; # reverse check

        if ($text ne $text_utf8) {
#                print STDERR "Unequal reverse check! It seems your input data \"$text\" is ",
#                        "ISO encoded already, so you don't need the latin1 encoding stuff here!\n";
                # I'm going to fix this
                $text_iso = $text;
        }
        $text_iso;
}
bugzilla2mantis.pl (28,256 bytes)   
bugzilla2mantis-2.22.pl (28,382 bytes)    
#!/usr/bin/perl
#############################################################################
# bugzilla2mantis.pl                                                        #
# Script to convert data in bugzilla (2.16-18) to Mantis (0.19.0)           #
# Probably works with other versions of bugzilla as its datamodel is fairly #
# stable. Likely to work with many versions of Mantis as well, as recent    #
# functionality is either not supported by Bugzilla or not imported.        #
# Relationships require at least Mantis 0.19.0. Everything else should work #
# on Mantis 0.18.3 (and all of 0.18.x theoretically). If you have luck using#
# specific releases of either Mantis or Bugzilla, please send them to       #
# cris@crisdaniluk.com.                                                     #
#                                                                           #
#############################################################################

# -------- begin -------
# The original web site for this script doesn't exist anymore, but
# I rediscovered this script here:
# http://web.archive.org/web/*/http://crisdaniluk.com/bugzilla2mantis/bugzilla2mantis.pl
# 
# I made some small changes, and added the possibility to
# import attachments. Also I had to add a conversion from 
# utf8 to latin1, see end of script.
# I prefixed all my changes with # Marc:
# 
# I used this script successfully to convert a Bugzilla 2.16.7 database
# to Mantis 1.0.7.
# 
# Marc, yoda at schli dot ch
# -------- end --------

# LICENSE                                                                   
# 1 You may use this code freely and without any obligation to me. 
# 2 You may use it in commercial applications. 
# 3 You may embed it. You may change it. 
# 4 You may print out thousands of copies to wallpaper your bedroom.
# 5 You may even hide the fact that this was written by someone else
# 6 BUT - You may NOT say you wrote it
# 7 AND - You may spontaneously lose all of your information in Bugzilla and
#   have unforseen problems in Mantis. I do not represent that this code 
#   works. You can make that determination on your own, after reviewing the 
#   code.
# 8 You should let me know what versions it does and does not work with
# 9 You should (but aren't obligated to)send me any enhancements you make. 
#   If you choose to, you understand your enhancements will be subject to 
#   these terms. You will be given credit.
# 

# Things not imported
# Attachments - Because this does not directly load into Mantis, but
#               instead creates a SQL dump file for you, it can't really
#               handle the attachment blobs. I'm working on a separate
#               script to handle the attachment migration.
#     (Marc: this works now)
# Milestones - mantis only uses versions. Versions can easily include
# milestones, which is I think what most people do in Bugzilla as well.
# If you need milestones that exist separate from versions, I recommend
# either adding a custom field for milestone in mantis (a GREAT mantis
# feature), or appending them to versions (i.e. "1.0 M1", "1.0 M2", ...)
# Field Defs - This is a funky feature that doesn't have a direct match in
#              Mantis. If someone thinks this might be useful to massage
#              and import, please let me know, but its really useless...
# Flags
# Named Queries - These are user level saved queries. Bugzilla uses a weird
#                 format to store these... not worth it.
# Quips - I love quips, but I REALLY love not having people ask me if I
#         spent company time writing all of them
# Series 
# Tokens
# Votes - what a stupid feature


# Things sort of imported
# Users - permissions just don't match from Bugzilla to Mantis. Bugzilla
#         has a lot more "horizontal" permissions but Mantis has a lot of
#         vertical control (project specific access). Unfortunately you'll
#         have to update permissions by hand, but this shouldn't be a big
#         deal unless this is a huge migration. If so, look at emailflags
#         ALSO, passwords are not imported so the DEFAULT mantis password
#         is used. This is 'root'. 
#         If you have a user with ID 1 in bugzilla, it will become the
#         admin user, overwriting the existing admin account. There is no
#         easy way around this since this preserves all IDs

# IMPORT SETTINGS

# Import relationships?
$importRelationships = 1; # 1/0

# Merge - You can choose to merge Bugzilla into your existing
# Mantis project. Note that at present time, consolidation is not
# done. Users, projects, etc will be duplicated. If you need to have
# this not happen, it is fairly easy to do by manually adding the
# users and/or projects and circumventing that part of the import.
# THIS IS NOT COMPLETELY IMPLEMENTED YET.
#$preserveIds = 1; # 1/0

$sqlDumpFile = 'bugzilla2mantis.sql';

# DATABASE CONNECTION SETTINGS
# This was designed for MySQL. MySQL automatically
# updates the sequences if they're bypassed. Postgres
# and Oracle won't. Be careful if you're not using MySQL.
$b_dsn = 'dbi:mysql:bugs:localhost:3306';
$b_user = 'bugs';
$b_password = 'redbox';

# VERSION SETTINGS

# Bugzillas previous to 2.18 did not have their own project id or
# private notes. If you're using 2.18, set these to 1.
$bugzillaHasProjectID = 1;
$bugzillaHasPrivateNotes = 1;

# BUGZILLA => MANTIS ATTRIBUTE MAPPINGS
# Feel free to customize these. Sometimes Mantis or Bugzilla would have
# a unique attribute (e.g. Bugzilla has 9 priorities, but Mantis has 6)
# so I choose some conservative defaults. You probably don't care.
$bgzPriority{'P1'} = 10;
$bgzPriority{'P2'} = 20;
$bgzPriority{'P3'} = 20;
$bgzPriority{'P4'} = 30;
$bgzPriority{'P5'} = 30;
$bgzPriority{'P6'} = 40;
$bgzPriority{'P7'} = 40;
$bgzPriority{'P8'} = 50;
$bgzPriority{'P9'} = 60;

# Mantis has no normal so I demoted minor to trivial to squeeze in normal
# Marc: added all the existing mantis status
$bgzSeverity{'enhancement'} = 10; #feature
$bgzSeverity{'trivial'} = 20; #trivial
#                        30; #text
$bgzSeverity{'minor'} = 40; #tweak
$bgzSeverity{'normal'} = 50; #minor
$bgzSeverity{'major'} = 60; #major
$bgzSeverity{'critical'} = 70; #crash
$bgzSeverity{'blocker'} = 80; #block

$bgzStatus{'UNCONFIRMED'} = 10; # new
$bgzStatus{'NEW'} = 10; # new
$bgzStatus{'ASSIGNED'} = 50; # assigned
$bgzStatus{'REOPENED'} = 20; # SPECIAL CASE - Changes resolution!
$bgzStatus{'RESOLVED'} = 80; # resolved
$bgzStatus{'VERIFIED'} = 40; # confirmed
$bgzStatus{'CLOSED'} = 90; # closed

$bgzResolution{'FIXED'} = 20; # fixed
$bgzResolution{'INVALID'} = 70; # not a bug
$bgzResolution{'WONTFIX'} = 90; # wont fix
$bgzResolution{'LATER'} = 80; # suspended
$bgzResolution{'REMIND'} = 80; # suspended
$bgzResolution{'DUPLICATE'} = 60; # duplicate
$bgzResolution{'WORKSFORME'} = 40; # unable to duplicate
$bgzResolution{'MOVED'} = 10; # open
$bgzResolution{'REOPENED'} = 30; # reopened


# END OF CONFIGURABLE STUFF

use DBI;
use POSIX;


use 5.004;

open(SQL, ">$sqlDumpFile")or die('Could not open SQL dump file');

my $b_dbh = DBI->connect($b_dsn, $b_user, $b_password);

# BUGZILLA PROFILES => MANTIS USERS
# id <= userid
# username <= login_name
# realname <= realname
# email <= login_name
# password <= cryptpassword
# date_created = blank
# last_visit = blank
# enabled = 1
# protected = 0
# access_level = 10
# login_count = 0
# cookie_string = blank
my @users;
{ 

  my $cookieCounter = 1; # This is dumb. Even for Mantis..
	my $strQuery = "SELECT userid, login_name, realname, cryptpassword FROM profiles";
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  while (my ($id, $name, $realname, $passwd) = $qry->fetchrow) {

    my %user;
    $user{'id'} = $id;
    $user{'username'} = $name;
    $user{'email'} = $name;
    $user{'realname'} = $realname;
    $user{'password'} = '63a9f0ea7bb98050796b649e85481845';
    $user{'date_created'} = '';
    $user{'last_visit'} = '';
    $user{'enabled'} = 1;
    $user{'protected'} = 0;
    $user{'access_level'} = 10;
    $user{'login_count'} = 0;
    $user{'cookie_string'} = 'cookiestring'.++$cookieCounter;

    # Admin override..
    if ($id == 1) {
      $user{'access_level'} = 90;
    }

    push(@users, \%user);

  }

  foreach $user (@users) {
    print SQL "INSERT INTO mantis_user_table (id, username, realname, email, password, date_created, last_visit, enabled, protected, access_level, login_count, cookie_string) VALUES(";
    print SQL $b_dbh->quote(${$user}{'id'}).", ";
    print SQL $b_dbh->quote(${$user}{'username'}).", ";
    print SQL $b_dbh->quote(${$user}{'realname'}).", ";
    print SQL $b_dbh->quote(${$user}{'email'}).", ";
    print SQL $b_dbh->quote(${$user}{'password'}).", ";
    print SQL $b_dbh->quote(${$user}{'date_created'}).", ";
    print SQL $b_dbh->quote(${$user}{'last_visit'}).", ";
    print SQL $b_dbh->quote(${$user}{'enabled'}).", ";
    print SQL $b_dbh->quote(${$user}{'protected'}).", ";
    print SQL $b_dbh->quote(${$user}{'access_level'}).", ";
    print SQL $b_dbh->quote(${$user}{'login_count'}).", ";
    print SQL $b_dbh->quote(${$user}{'cookie_string'}).");\n";
  }
}

# Milestoning and voting are not portable concepts in Mantis
# Milestones will be ported, but not the default project settings.
# enabled <= disallownew
# access_min = 10 (public)
# view_state = 10 (public)
# file_path = nil
# status = 10
my @projects;
my %projectNameIdMap;

{
 
  my $projectId = 0;
  my $strQuery;
  if ($bugzillaHasProjectID == 1) {
    $strQuery = "SELECT id, name, description, disallownew FROM products";
  } else {
    $strQuery = "SELECT 1, product AS name, description, disallownew FROM products";
  }

  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  while (my ($id, $name, $desc, $disallow) = $qry->fetchrow) {

    my %project;
    if ($bugzillaHasProjectID == 1) {
      $project{'id'} = $id;
    } else {
      $project{'id'} = ++$projectId;
    }
    $projectNameIdMap{$name} = $project{'id'};

    $project{'name'} = $name;
    $project{'description'} = $desc;
    # Marc: fixed, was: = disallow;
    $project{'enabled'} = !$disallow;
    $project{'access_min'} = 10;
    $project{'view_state'} = 10;
    $project{'file_path'} = '';
    $project{'status'} = 10;
    push(@projects, \%project);

  }

  foreach $project (@projects) {
    print SQL "INSERT INTO mantis_project_table (id, name, status, enabled, view_state, access_min, file_path, description) VALUES(";
    print SQL $b_dbh->quote(${$project}{'id'}).", ";
    print SQL $b_dbh->quote(${$project}{'name'}).", ";
    print SQL $b_dbh->quote(${$project}{'status'}).", ";
    print SQL $b_dbh->quote(${$project}{'enabled'}).", ";
    print SQL $b_dbh->quote(${$project}{'view_state'}).", ";
    print SQL $b_dbh->quote(${$project}{'access_min'}).", ";
    print SQL $b_dbh->quote(${$project}{'file_path'}).", ";
    print SQL $b_dbh->quote(${$project}{'description'}).");\n";
  }
}

# BUGZILLA COMPONENTS => MANTIS CATEGORIES
# project_id <= product_id*
# category <= name
# user_id <= initialowner*
# Mantis does not use IDs for category names, so category names within
# a project MUST be unique, which is not the case in Bugzilla. To make
# sure this is not a problem, we will append the Bugzilla id to the name.
# Note that this ID will not have any intrinsic meaning in Mantis itself,
# but will allow an administrator to rename components if necessary.
my @components;
my %categoryNameIdMap;
{

  my $strQuery;
  my $categoryId = 0;
  if ($bugzillaHasProjectID == 1) {
    $strQuery = "SELECT id, name, product_id, initialowner FROM components ORDER BY product_id";
  } else {
    $strQuery = "SELECT 1, value, program, initialowner FROM components ORDER BY program";
  }
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $lastProduct = -1;
  my %categoryNames;

  while (my ($id, $name, $product, $owner) = $qry->fetchrow) {

    # If new product(ject), reset the dupe check
    if (($bugzillaHasProjectID == 1) && ($lastProduct != $product)) {
      %categoryNames = {};
    } elsif (($bugzillaHasProjectID) == 0 && ($lastProduct ne $product)) {
      %categoryNames = {};
    }
    # Check to see that this name doesn't already exist
    if (exists $categoryNames{$name}) {
     $name .= ' '.$id;
    }

    my %category; 
    if ($bugzillaHasProjectID == 1) {
      $category{'project_id'} = $product;
    } else {
      $category{'project_id'} = $projectNameIdMap{$product};
    }
    $category{'category'} = $name;
    $category{'user_id'} = $owner;

    $categoryNameIdMap{$category{'name'}} = $category{'id'};

    # Store grouping info for dupe check
    $categoryNames{$name} = $name;
    $lastProduct = $product;

    push(@categories, \%category);

  }

  foreach $category (@categories) {
    print SQL "INSERT INTO mantis_project_category_table (project_id, category, user_id) VALUES(";
    print SQL $b_dbh->quote(${$category}{'project_id'}).", ";
    print SQL $b_dbh->quote(${$category}{'category'}).", ";
    print SQL $b_dbh->quote(${$category}{'user_id'}).");\n";
  } 
}

# BUGZILLA VERSIONS => MANTIS VERSIONS
# id = generated
# project_id <= product_id*
# version <= value
# date_order = now
# description = blank
# released = 0

my @versions;
{
  my $strQuery;
  if ($bugzillaHasProjectID == 1) {
    $strQuery = "SELECT value, product_id FROM versions";
  } else {
    $strQuery = "SELECT value, program FROM versions";
  }

  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $verID = 1;

  while (my ($value, $product) = $qry->fetchrow) {

    my %version;
    $version{'id'} = $verID++;
    if ($bugzillaHasProjectID == 1) {
      $version{'project_id'} = $product;
    } else {
      $version{'project_id'} = $projectNameIdMap{$product};
    }

    $version{'version'} = $value;
    $version{'description'} = '';
    $version{'date_order'} = '';
    $version{'released'} = '0';
 
    push(@versions, \%version);

  }

  foreach $version (@versions) {
    print SQL "INSERT INTO mantis_project_version_table (id, project_id, version, date_order, description, released) VALUES(";
    print SQL $b_dbh->quote(${$version}{'id'}).", ";
    print SQL $b_dbh->quote(${$version}{'project_id'}).", ";
    print SQL $b_dbh->quote(${$version}{'version'}).", ";
    print SQL $b_dbh->quote(${$version}{'date_order'}).", ";
    print SQL $b_dbh->quote(${$version}{'description'}).", ";
    print SQL $b_dbh->quote(${$version}{'released'}).");\n";
  }

}

# BUGZILLA BUGS => MANTIS BUGS
# id <= bug_id
# project_id <= product_id*
# reporter_id <= reporter
# handler_id <= assigned_to 
# duplicate_id <= blank
# priority <= priority
# severity <= bug_severity
# reproducibility <= 10
# status <= bug_status
# resolution <= resolution
# projection <= 10
# category <= component_id
# date_submitted <= creation_ts
# last_updated <= delta_ts
# eta <= 10
# bug_text_id <= -1 (set later)
# os <= op_sys
# os_build <= blank
# platform <= rep_platform
# version <= version
# fixed_in_version <= target_milestone
# build <= blank
# profile_id <= null
# view_state <= 10
# summary <= short_desc
# sponsorship_total <= null
my %bugs;
{
  my $strQuery;

  if ($bugzillaHasProjectID == 1) {
    $strQuery = "SELECT bug_id, assigned_to, bug_file_loc, bug_severity, bug_status, creation_ts, delta_ts, short_desc, op_sys, priority, product_id, rep_platform, reporter, version, component_id, resolution, target_milestone, short_desc FROM bugs";
  } else {
    $strQuery = "SELECT bug_id, assigned_to, bug_file_loc, bug_severity, bug_status, creation_ts, delta_ts, short_desc, op_sys, priority, product, rep_platform, reporter, version, component, resolution, target_milestone, short_desc FROM bugs";
  }


  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;


  while (my ($bug_id, $assigned_to, $bug_file_loc, $bug_severity, $bug_status, $creation_ts, $delta_ts, $short_desc, $op_sys, $priority, $product_id, $rep_platform, $reporter, $version, $component_id, $resolution, $target_milestone, $short_desc) = $qry->fetchrow) {

    my %bug;
    
    $bug{'id'} = $bug_id;
    
    if ($bugzillaHasProjectID == 1) {
      $bug{'project_id'} = $product_id;
      $bug{'category'} = $component_id;
    } else {
      $bug{'project_id'} = $projectNameIdMap{$product_id};
      $bug{'category'} = $categoryNameIdMap{$component_id};
    }

    $bug{'reporter_id'} = $reporter;
    $bug{'handler_id'} = $assigned_to;
    $bug{'duplicate_id'} = ''; # null
    $bug{'priority'} = $bgzPriority{$priority};
    if (!(exists $bgzPriority{$priority})) { 
      $bug{'priority'} = 50;
    }
    $bug{'severity'} = $bgzSeverity{$bug_severity};
    $bug{'reproducibility'} = 10;
    $bug{'status'} = $bgzStatus{$bug_status};
    $bug{'resolution'} = $bgzResolution{$resolution};
    $bug{'projection'} = 10;
    $bug{'category'} = $component_id;
    $bug{'date_submitted'} = $creation_ts;
    $bug{'last_updated'} = $delta_ts;
    $bug{'eta'} = 10;
    $bug{'bug_text_id'} = -1;
    $bug{'os'} = $op_sys;
    $bug{'os_build'} = '';
    $bug{'platform'} = $rep_platform;
    $bug{'version'} = $version;
    $bug{'fixed_in_version'} = $target_milestone;
    $bug{'build'} = '';
    $bug{'profile_id'} = ''; # null
    $bug{'view_state'} = 10;
    # Marc: convert to iso
    $bug{'summary'} = toISO($short_desc);
    $bug{'sponsorship_total'} = ''; # null
    
    # If status is reopened, status should be assigned
    # and resolution should be REOPENED
    # Marc: changed, didn't make sense.
    if ($bug_status eq 'REOPENED') {
      $bug{'resolution'} = $bgzResolution{'REOPENED'};
    }
    if ($bug{'resolution'} == '') {
      $bug{'resolution'} = 10;
    }

    $bugs{$bug_id} = \%bug;

  }
  
# We don't iterate bugs out to the db yet to prevent a RI problem
}

# BUGZILLA LONGDESCS => MANTIS BUGTEXTS/BUGNOTES
# Mantis has a separate table for the first bug note, which it considers
# to be the bug description. Bugzilla doesn't have the idea of a
# description. In order to make Mantis work as it should, the first 
# longdesc for each bug will be the bug description. Subsequent ones
# will be bugnotes.
#
# DESCS
# id <= generated
# description <= thetext
# steps_to_reproduce <= blank
# additional_information <= blank
# 
# NOTES
# id <= generated
# bug_id <= bug_id
# reporter_id <= who
# bugnote_text_id <= generated
# view_state <= isprivate (10/50)
# date_submitted <= bug_when
# last_modified <= nil
#
# NOTES (text ref)
# id <= generated
# note <= thetext

my @bugdescs;
my @bugnotes;
{
  my $strQuery;
  if ($bugzillaHasPrivateNotes == 1) {
    $strQuery = "SELECT bug_id, who, bug_when, thetext, isprivate FROM longdescs ORDER BY bug_id";
  } else {
    $strQuery = "SELECT bug_id, who, bug_when, thetext, 10 FROM longdescs ORDER BY bug_id";
  }

  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $lastBug = -1;
  my $bugNoteId = 0;
  my $bugDescId = 0;

  while (my ($bug_id, $who, $bug_when, $thetext, $isprivate) = $qry->fetchrow) {


    if ($lastBug == $bug_id) {
      # Bugnote
      my %note;
      $note{'id'} = ++$bugNoteId;
      $note{'bug_id'} = $bug_id;
      $note{'reporter_id'} = $who;
      $note{'bugnote_text_id'} = $bugNoteId;
      $note{'view_state'} = $isprivate==0?50:10;
      $note{'date_submitted'} = $bug_when;
      $note{'last_modified'} = '';
      $note{'note'} = toISO($thetext);
      push(@bugnotes, \%note);
    } else {
      my %desc;
      $desc{'id'} = ++$bugDescId;
      $desc{'description'} = toISO($thetext);
      push(@bugdescs, \%desc);
   
      # We also have to update the bug...
      $bug = $bugs{$bug_id};
      if ($bug != null) {
        # Marc: didn't work
        #${$bug}{'bug_text_id'} = $bugDescId;
        #$bugs{$bug_id} = \%bug;
        $$bug{'bug_text_id'} = $bugDescId;
      }
    }

    $lastBug = $bug_id;

  }

  foreach $desc (@bugdescs) {
    print SQL "INSERT INTO mantis_bug_text_table (id, description, steps_to_reproduce, additional_information) VALUES(";
    print SQL $b_dbh->quote(${$desc}{'id'}).", ";
    print SQL $b_dbh->quote(${$desc}{'description'}).", ";
#    print SQL $b_dbh->quote(${$desc}{'steps_to_reproduce'}).", ";
#    print SQL $b_dbh->quote(${$desc}{'additional_information'}).");\n";
    print SQL $b_dbh->quote("").", ";
    print SQL $b_dbh->quote("").");\n";
  } 

  foreach $note (@bugnotes) {
    print SQL "INSERT INTO mantis_bugnote_text_table (id, note) VALUES(";
    print SQL $b_dbh->quote(${$note}{'id'}).", ";
    print SQL $b_dbh->quote(${$note}{'note'}).");\n";

    print SQL "INSERT INTO mantis_bugnote_table (id, bug_id, reporter_id, bugnote_text_id, view_state, date_submitted, last_modified) VALUES(";
    print SQL $b_dbh->quote(${$note}{'id'}).", ";
    print SQL $b_dbh->quote(${$note}{'bug_id'}).", ";
    print SQL $b_dbh->quote(${$note}{'reporter_id'}).", ";
    print SQL $b_dbh->quote(${$note}{'bugnote_text_id'}).", ";
    print SQL $b_dbh->quote(${$note}{'view_state'}).", ";
    print SQL $b_dbh->quote(${$note}{'date_submitted'}).", ";
    print SQL $b_dbh->quote(${$note}{'last_modified'}).");\n";


  } 
}

# now iterate out the bugs themselves..
foreach $bug (values %bugs) {
#  $bug = $bugs{$bugid};
  print SQL "INSERT INTO mantis_bug_table (id, project_id, reporter_id, handler_id, duplicate_id, priority, severity, reproducibility, status, resolution, projection, category, date_submitted, last_updated, eta, bug_text_id, os, os_build, platform, version, fixed_in_version, build, profile_id, view_state, summary, sponsorship_total) VALUES(";
  print SQL $b_dbh->quote(${$bug}{'id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'project_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'reporter_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'handler_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'duplicate_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'priority'}).", ";
  print SQL $b_dbh->quote(${$bug}{'severity'}).", ";
  print SQL $b_dbh->quote(${$bug}{'reproducibility'}).", ";
  print SQL $b_dbh->quote(${$bug}{'status'}).", ";
  print SQL $b_dbh->quote(${$bug}{'resolution'}).", ";
  print SQL $b_dbh->quote(${$bug}{'projection'}).", ";
  print SQL $b_dbh->quote(${$bug}{'category'}).", ";
  print SQL $b_dbh->quote(${$bug}{'date_submitted'}).", ";
  print SQL $b_dbh->quote(${$bug}{'last_updated'}).", ";
  print SQL $b_dbh->quote(${$bug}{'eta'}).", ";
  print SQL $b_dbh->quote(${$bug}{'bug_text_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'os'}).", ";
  print SQL $b_dbh->quote(${$bug}{'os_build'}).", ";
  print SQL $b_dbh->quote(${$bug}{'platform'}).", ";
  print SQL $b_dbh->quote(${$bug}{'version'}).", ";
  print SQL $b_dbh->quote(${$bug}{'fixed_in_version'}).", ";
  print SQL $b_dbh->quote(${$bug}{'build'}).", ";
  print SQL $b_dbh->quote(${$bug}{'profile_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'view_state'}).", ";
  print SQL $b_dbh->quote(${$bug}{'summary'}).", ";
  print SQL $b_dbh->quote(${$bug}{'sponsorship_total'}).");\n";
}

# BUGZILLA DEPENDENCIES => MANTIS RELATIONSHIPS
# id = generated
# source_bug_id <= blocked
# destination_bug_id <= dependson
# relationship_type = 1 - This is a related to, not child of. 
# Mantis doesn't have a specific blocker relationship type now.

my @relationships;

if ($importRelationships == 1) {

  my $strQuery = "SELECT blocked, dependson FROM dependencies";
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $relID = 1;

  while (my ($blocked, $dependson) = $qry->fetchrow) {

    my %relationship;
    $relationship{'id'} = $relID++;
    $relationship{'source_bug_id'} = $blocked;
    $relationship{'destination_bug_id'} = $dependson;
    $relationship{'relationship_type'} = '1';
 
    push(@relationships, \%relationship);

  }

  foreach $relationship (@relationships) {
    print SQL "INSERT INTO mantis_bug_relationship_table (id, source_bug_id, destination_bug_id, relationship_type) VALUES(";
    print SQL $b_dbh->quote(${$relationship}{'id'}).", ";
    print SQL $b_dbh->quote(${$relationship}{'source_bug_id'}).", ";
    print SQL $b_dbh->quote(${$relationship}{'destination_bug_id'}).", ";
    print SQL $b_dbh->quote(${$relationship}{'relationship_type'}).");\n";
  }

}

# Need to cache Bugzilla fields for activity
my %fieldNameIdMap;
{
  my $strQuery = "SELECT fieldid, description FROM fielddefs";
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;
  
  while (my ($id, $desc) = $qry->fetchrow) {
    
    $fieldNameIdMap{$id} = $desc;

  }
}

# BUGZILLA ACTIVITY => MANTIS HISTORY
# id <= generated
# user_id <= wo
# bug_id <= bug_id
# date_modified <= bug_when
# field_name <= fieldid
# new_value <= added
# old_value <= removed
# type <= 
my @history;
{ 

  my $activityId = 0;

  my $strQuery = "SELECT bug_id, who, bug_when, fieldid, added, removed FROM bugs_activity";
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  while (my ($bug_id, $who, $bug_when, $fieldid, $added, $removed) = $qry->fetchrow) {

    my %elem;
    $elem{'id'} = ++$activityId;
    $elem{'bug_id'} = $bug_id;
    $elem{'date_modified'} = $bug_when;
    $elem{'user_id'} = $who;
    $elem{'field_name'} = $fieldNameIdMap{$fieldid};
    $elem{'new_value'} = $added;
    $elem{'old_value'} = $removed;
    $elem{'type'} = 0;

    push(@history, \%elem);

  }

  foreach $elem (@history) {
    print SQL "INSERT INTO mantis_bug_history_table (id, user_id, bug_id, date_modified, field_name, old_value, new_value, type) VALUES(";
    print SQL $b_dbh->quote(${$elem}{'id'}).", ";
    print SQL $b_dbh->quote(${$elem}{'user_id'}).", ";
    print SQL $b_dbh->quote(${$elem}{'bug_id'}).", ";
    print SQL $b_dbh->quote(${$elem}{'date_modified'}).", ";
    print SQL $b_dbh->quote(${$elem}{'field_name'}).", ";
    print SQL $b_dbh->quote(${$elem}{'old_value'}).", ";
    print SQL $b_dbh->quote(${$elem}{'new_value'}).", ";
    print SQL $b_dbh->quote(${$elem}{'type'}).");\n";
  }
}


# Marc: convert attachments

# BUGZILLA ATTACHMENTS => MANTIS BUG_FILE
# id = generated
# bug_id <= bug_id*
# title <= blank
# description = description
# diskfile = ????
# filename = filename
# folder = blank
# filesize = generated
# file_type = mimetype
# date_added = creation_ts
# content = thedata

my @files;
{
  my $strQuery;
  $strQuery = "SELECT attachments.bug_id, attachments.description, attachments.filename, attachments.mimetype, attachments.creation_ts, attach_data.thedata FROM attachments,attach_data where attach_data.id=attachments.attach_id ";

  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $fileID = 1;

  while (my ($bug_id, $description, $filename, $mimetype, $creation_ts, $thedata) = $qry->fetchrow) {

    my %file;
    $file{'id'} = $fileID++;
    $file{'bug_id'} = $bug_id;

    $file{'title'} = $description;
    $file{'description'} = '';
    $file{'diskfile'} = '';
    $file{'filename'} = $filename;
    $file{'folder'} = '';
    $file{'filesize'} = length($thedata);
    $file{'file_type'} = $mimetype;
    $file{'date_added'} = $creation_ts;
    $file{'content'} = $thedata;

    push(@files, \%file);

  }

  foreach $file (@files) {
    print SQL "INSERT INTO mantis_bug_file_table (id, bug_id, title, description, diskfile, filename, folder, filesize, file_type, date_added, content) VALUES(";
    print SQL $b_dbh->quote(${$file}{'id'}).", ";
    print SQL $b_dbh->quote(${$file}{'bug_id'}).", ";
    print SQL $b_dbh->quote(${$file}{'title'}).", ";
    print SQL $b_dbh->quote(${$file}{'description'}).", ";
    print SQL $b_dbh->quote(${$file}{'diskfile'}).", ";
    print SQL $b_dbh->quote(${$file}{'filename'}).", ";
    print SQL $b_dbh->quote(${$file}{'folder'}).", ";
    print SQL $b_dbh->quote(${$file}{'filesize'}).", ";
    print SQL $b_dbh->quote(${$file}{'file_type'}).", ";
    print SQL $b_dbh->quote(${$file}{'date_added'}).", ";
    print SQL $b_dbh->quote(${$file}{'content'}).");\n";
  }

}

# Marc: convert some text to iso

use Unicode::String qw(latin1 utf8);

sub toISO($) {
        my $text = $_[0];
        # if this host was UTF-8 encoded:
        my $text_iso  = (utf8($text))->latin1;
        my $text_utf8 = (latin1($text_iso))->utf8; # reverse check

        if ($text ne $text_utf8) {
#                print STDERR "Unequal reverse check! It seems your input data \"$text\" is ",
#                        "ISO encoded already, so you don't need the latin1 encoding stuff here!\n";
                # I'm going to fix this
                $text_iso = $text;
        }
        $text_iso;
}
bugzilla2mantis-2.22.pl (28,382 bytes)   
bugzilla2mantis-3.04.pl (29,208 bytes)
bugzilla2mantis-3.2.3.pl (32,921 bytes)
bugzilla2mantis-3.2.3_fixed.pl (33,219 bytes)

Relationships

related to 0007469 closeddregad bugzilla2mantis.php script 
related to 0009283 closedvboctor "Cris Daniluk " link is not opening 
related to 0008251 closeddregad Migration script to Mantis 

Activities

epierre

epierre

2007-12-12 08:27

reporter   ~0016434

I've attached a corrected version for Bugzilla 2.22 which has an attachement table separated from the previous versions
epierre

epierre

2007-12-12 11:41

reporter   ~0016435

there is an issue on severity that should be put to a value, import fails if it is NULL.

a last point to check: utf8 import seems to be faulty... it corrupts data if original base was latin1 with accents, and destionation is latin1 too.
harlan

harlan

2008-06-03 19:32

reporter   ~0018001

I tried the 2.22 script on a bugzilla 2.22 system. The first problem I had was that mantis_project_category_table did not exist. I manually created it.

-- I'm not an SQL guy - how difficult would it be to have the script generate code to automatically add missing tables?

The second problem I had was that the INSERTs for the mantis_bug_table are using a 'category' entry, which does not seem to exist.
illes

illes

2008-09-08 09:40

reporter   ~0019333

Does the script can be used for a Bugzilla 3.0 installation?
veitg

veitg

2008-10-15 10:00

reporter   ~0019565

Hi.

Grabbed bugzilla2mantis-2.22.pl to get it to work with bugzilla 3.04 and mantis 1.1.2. Had to add some fixes to get it running. Here's what I changed (use diff to get the idea - not nice but works for me):

  • excluded bugzilla admin account from migration (mantis default install already has one that leads to conflicts during import (duplicate id (1))).
  • in summary view in mantis, components were listed as IDs after migration - not the names. now names are inserted correctly.
  • date_order of project versions was null when importing. leads to a failure when copying version from proj a to b. now now() is used for that.
  • component leaders were set after migration, but they weren't assigned to the project itself as members. Leads to empty dropdowns on the component details. Now, component owners are always added as project members with the role developer (made sense for us).

Perhaps we missed some things - but it looked well so far. I'll come back if there're any issues left.

BTW: would be great if everything would run in one transaction - so on every err one has to delete the tables manually :(. Perhaps someone needs this:

delete from mantis_project_table;
delete from mantis_user_table where id > 1;
delete from mantis_project_category_table;
delete from mantis_project_version_table;
delete from mantis_bug_text_table;
delete from mantis_bugnote_text_table;
delete from mantis_bugnote_table;
delete from mantis_bug_table;
delete from mantis_bug_history_table;
delete from mantis_bug_file_table;
delete from mantis_project_user_list_table;

Have fun.

Regards,
Veit
phowells

phowells

2009-05-22 08:54

reporter   ~0021882

Last edited: 2009-05-22 09:04

I am not a perl person but I have made modifications to the 3.04 script to include custom fields, its tailored to my needs but I feel if someone else looks at it they can do some real data driven sql development. I have attached it called bugzilla2mantis-3.2.3.pl

I have successfully ran this and it does work but it messes up on some of the dates but I believe that has more to do with out bugzilla database having dirty data.
KarlReichert

KarlReichert

2010-02-03 12:33

reporter   ~0024301

I tried both, v3.04 and v3.2.3_fixed of the perl scripts, but both a throwing the same error (the first one, I guess the following ones are caused by the first one):

C:>perl bugzilla2mantis-3.04.pl
DBD::mysql::st execute failed: Unknown column 'id' in 'field list' at bugzilla2mantis-3.04.pl line 714.
DBD::mysql::st fetchrow failed: fetch() without execute() at bugzilla2mantis-3.04.pl line 716.

C:>perl bugzilla2mantis-3.2.3_fixed.pl
DBD::mysql::st execute failed: Unknown column 'id' in 'field list' at bugzilla2mantis-3.2.3_fixed.pl line 715.
DBD::mysql::st fetchrow failed: fetch() without execute() at bugzilla2mantis-3.2.3_fixed.pl line 717.
DBD::mysql::st execute failed: Unknown column 'fielddefs.type' in 'field list' at bugzilla2mantis-3.2.3_fixed.pl line 844.
DBD::mysql::st fetchrow failed: fetch() without execute() at bugzilla2mantis-3.2.3_fixed.pl line 848.

The Bugzilla version im trying to migrate from is v2.22.1
phowells

phowells

2010-02-03 13:21

reporter   ~0024302

I would use the Bugzilla 2.22 script then, the newer versions of bugzilla have different columns and that is why you can not query the columns.
KarlReichert

KarlReichert

2010-02-04 05:38

reporter   ~0024310

Thanks, this dumped my Bugzilla data without problems :)

But when importing the data into my Mantis database, it fails, because I already have existing projects in there. The dump tries to create new projects starting with ID 1, which of course fails, because an existing project already has ID 1. The same problem with categories, users ...

So it seems, that this script is only working with an empty Mantis database. I guess, there are two options:
a) go through the whole SQL dump file and replace all IDs with non-existing ones, which of course would mean a lot of work
b) edit the script to use different starting IDs, e.g. starting with IDs from 100 on or so, for all stuff (projects, users, categories, ...)

Or is there any other way to migrate an existing Bugzilla database into an already used Mantis database? I think, as long as this is not possible, a lot of users, willing to switch to Mantis, are not doing it, because of that fact!
kaner

kaner

2011-01-14 10:02

reporter   ~0027939

Here's a patch that makes this work with the latest Mantis for me:

--- /root/bugzilla2mantis-3.2.3_fixed.pl 2011-01-14 13:56:55.000000000 +0100
+++ ./bugzilla2mantis.pl 2011-01-14 16:01:23.000000000 +0100
@@ -359,7 +359,7 @@

my %projuser;

foreach $category (@categories) {

  • print SQL "INSERT INTO mantis_project_category_table (project_id, category, user_id) VALUES(";

  • print SQL "INSERT INTO mantis_category_table (project_id, name, user_id) VALUES(";
    print SQL $b_dbh->quote(${$category}{'project_id'}).", ";
    print SQL $b_dbh->quote(${$category}{'category'}).", ";
    print SQL $b_dbh->quote(${$category}{'user_id'}).");\n";
    @@ -461,7 +461,7 @@
    my $strQuery;

    if ($bugzillaHasProjectID == 1) {

  • $strQuery = "SELECT bug_id, assigned_to, bug_file_loc, bug_severity, bug_status, creation_ts, delta_ts, short_desc, op_sys, priority, product_id, rep_platform, reporter, version, (select name from components where id = component_id), resolution, target_milestone, short_desc FROM bugs";
  • $strQuery = "SELECT bug_id, assigned_to, bug_file_loc, bug_severity, bug_status, creation_ts, delta_ts, short_desc, op_sys, priority, product_id, rep_platform, reporter, version, (select id from components where id = component_id), resolution, target_milestone, short_desc FROM bugs";
    } else {
    $strQuery = "SELECT bug_id, assigned_to, bug_file_loc, bug_severity, bug_status, creation_ts, delta_ts, short_desc, op_sys, priority, product, rep_platform, reporter, version, component_id, resolution, target_milestone, short_desc FROM bugs";
    }
    @@ -639,7 +639,7 @@

    now iterate out the bugs themselves..

    foreach $bug (values %bugs) {

    $bug = $bugs{$bugid};

  • print SQL "INSERT INTO mantis_bug_table (id, project_id, reporter_id, handler_id, duplicate_id, priority, severity, reproducibility, status, resolution, projection, category, date_submitted, last_updated, eta, bug_text_id, os, os_build, platform, version, fixed_in_version, build, profile_id, view_state, summary, sponsorship_total) VALUES(";
  • print SQL "INSERT INTO mantis_bug_table (id, project_id, reporter_id, handler_id, duplicate_id, priority, severity, reproducibility, status, resolution, projection, category_id, date_submitted, last_updated, eta, bug_text_id, os, os_build, platform, target_version, fixed_in_version, build, profile_id, view_state, summary, sponsorship_total) VALUES(";
    print SQL $b_dbh->quote(${$bug}{'id'}).", ";
    print SQL $b_dbh->quote(${$bug}{'project_id'}).", ";
    print SQL $b_dbh->quote(${$bug}{'reporter_id'}).", ";
dregad

dregad

2012-09-27 12:54

developer   ~0032971

Last edited: 2012-09-27 12:57

This is documented in the FAQ on the wiki
http://www.mantisbt.org/wiki/doku.php/mantisbt:faq#migration_from_bugzilla_to_mantisbt