2 # bugzilla2gforge.pl - transfers bug reports from Bugzilla to gforge.
3 # steev hise, steev AT datamassage.com, december 2001
4 # todd wallentine, tcw AT ksu edu, february 2002
5 # oliver blume, skytecag.com, march 2004
7 # version 1.2 - copyleft 2001 - GNU Public License
9 # to customize this, of course you'll need to change a lot of
10 # the mappings. Most important, the user map.
11 # Mostly these things happen in the "init_maps"
12 # subroutine, though there are a few other places you
13 # might have to customize. Also note in the init_dbh subroutine
14 # you'll need to put in your database users and passwords and hosts.
16 # The script has one characteristic: It creates the artifacts in GForge
17 # with the same id that the bugs have in the bugzilla system. That's
18 # because we put the bug id in our source code comments and keeping
19 # the ids makes it easier to lookup a bug reports in Gforge.
20 # This requires an empty artifacts table.
22 # If you don't care about keeping your bug ids and/or want to import bugzilla
23 # bugs into a running gforge system you have to rewrite the script in some parts.
24 #####################################################################
30 use Data::Dumper; # debugging only.
33 use vars qw( $BZ_DBH $SF_DBH $MREF %ID_MAP $SFGROUP $SFBUGTRACKERID);
37 # change this to the gforge group id of the project you're
39 $SFGROUP = 5; # all the bugs are for this one gforge project.
40 $SFBUGTRACKERID = 101; # value of bug-tracker-id (artifact.group_artifact_id).
42 ($BZ_DBH, $SF_DBH) = &init_dbh; # open all the database handles.
48 #############################################################
49 &create_artifact_categories();
51 &create_artifact_groups();
55 &update_artifacts_count_agg();
57 &update_artifact_seq();
63 print "Done. Transferred " , scalar keys %ID_MAP, " bugs in ", time-$time, " seconds.\n\n";
69 ############ subroutines ##########################
72 # be sure to change the hosts, users, passwords to values appropo
75 # first connect to the Bugzilla mysql database.
77 my $bzhost = 'localhost';
78 my $bzdsn = "DBI:mysql:database=$bzdb;host=$bzhost";
82 my $bz_dbh = DBI->connect($bzdsn, $bzuser, $bzpw);
83 $bz_dbh->{ RaiseError } = 1;
85 # now connect to the gforge postgres database
86 my $sfdb = 'alexandria';
87 my $sfhost = 'localhost'; # probably running locally so unneeded.
88 my $sfdsn = "DBI:PgPP:dbname=$sfdb;host=$sfhost;";
89 my $sfuser = 'postgres';
90 my $sfpw = ''; # no passwd needed
92 my $sf_dbh = DBI->connect($sfdsn, $sfuser, $sfpw);
93 $sf_dbh->{AutoCommit} = 0; # enable transactions, if possible
94 $sf_dbh->{ RaiseError } = 1;
96 return $bz_dbh, $sf_dbh;
100 # this just sets up some hashes and stuff for mapping between
101 # the bugzilla schema and the gforge schema.
103 # this going to return a hash of references.
104 # each reference is an anonymous subroutine.
105 # each reference maps the values of certain fields from
106 # one database to another.
108 # you pass each subroutine the original value and it
109 # returns the mapped value, plus, in some cases,
110 # the name of the field in the destination table where it goes.
114 # first, a few all-purpose mappings
116 # map bugzilla user ids into gforge user ids.
117 # before running this script you have to create the users in gforge manually.
118 $mapref->{user} = sub {
119 my ($bz_userid) = @_;
122 3 => 109, # attrossbach
123 4 => 108, # mreinermann
125 6 => 112, # fgrassinger
126 7 => 110, # hnuernberger
127 11 => 113, # tschuett
129 0 => 100, # none nobody - default
131 if ($usermap->{$bz_userid}) {
132 return $usermap->{$bz_userid};
134 die "User not found: # $bz_userid";
138 $mapref->{bug_id} = sub {
141 # map bug_id and get longdesc for the bug
142 # we keep the bug_id so that we can easily look up old bug reports.
143 # requires emtpy artifacts table of course.
144 # change this if you plan to import into existing artifacts table where you cannot choose your own bug id.
145 $sf->{artifact_id} = $bz->{bug_id};
147 # now add a little note.
148 $sf->{details} = "NOTE: This bug is originally from Bugzilla " .
149 "<a href=\"https://bugzilla.skytec-ag.net/cgi-bin/bugzilla/show_bug.cgi?id=$bz->{bug_id}\">Bug #$bz->{bug_id}</a>.\n\n";
151 # find the first longdesc and use as details field in SF.
152 my $bz_sth = $BZ_DBH->prepare('SELECT thetext FROM longdescs WHERE bug_id=? order by bug_when');
153 $bz_sth->execute($bz->{bug_id});
154 my ($text) = $bz_sth->fetchrow_array;
155 $sf->{details} .= $text;
159 $mapref->{assigned_to} = sub {
161 $sf->{assigned_to} = $MREF->{user}($bz->{assigned_to});
164 $mapref->{bug_severity} = sub {
166 $sf->{details} .= "\nOriginal severity: ". $bz->{bug_severity};
169 $mapref->{bug_status} = sub {
180 $sf->{status_id} = $status_map->{$bz->{bug_status}};
183 $mapref->{creation_ts} = sub {
185 $sf->{open_date} = $bz->{creation_ts};
188 # here we check the status, and if it's a closed bug,
189 # we assign close_date the value of delta_ts.
190 # this assumes that if a bug is closed, closing it
191 # was the last thing ever done to it.
192 $mapref->{delta_ts} = sub {
194 if($sf->{status_id} == 3) {
195 $sf->{close_date} = $bz->{delta_ts};
199 $mapref->{short_desc} = sub {
201 $sf->{summary} = $bz->{short_desc};
204 $mapref->{priority} = sub {
206 $bz->{priority} =~ s/P//; # remove the stupid letter P.
207 $sf->{priority} = $bz->{priority} * 2 - 1;
210 $mapref->{reporter} = sub {
212 $sf->{submitted_by} = $MREF->{user}($bz->{reporter});
215 # we're mapping bugzilla "versions" to gforge "artifact_groups" ids.
216 $mapref->{version} = sub {
218 my $sf_sth = $SF_DBH->prepare('SELECT id FROM artifact_group WHERE group_artifact_id = ? and group_name =?');
219 $sf_sth->execute($SFBUGTRACKERID, $bz->{version});
220 my ($id) = $sf_sth->fetchrow_array;
221 $sf->{artifact_group_id} = $id;
224 # we're mapping bugzilla "components" to gforge "category" ids.
225 $mapref->{component} = sub {
228 my $sf_sth = $SF_DBH->prepare('SELECT id FROM artifact_category WHERE group_artifact_id = ? and category_name =?');
229 $sf_sth->execute($SFBUGTRACKERID, $bz->{component});
230 my ($id) = $sf_sth->fetchrow_array;
231 $sf->{category_id} = $id;
234 # the names of the gforge resolutions are identical,
235 # we just need to map the names to the ids.
236 $mapref->{resolution} = sub {
238 my $resolution_map = {
249 $sf->{resolution_id} = $resolution_map->{$bz->{resolution}};
256 #############################################################
258 # maps bugzilla.bugs into gforge.artifacts
259 sub create_artifacts {
261 # remove existing artifacts from gforge database!!!!!
262 # this is helpful while debugging the script to avoid violation of PK constraints or duplicate entries.
263 # should only be done when working on a fresh and empty database.
264 # if you plan importing into an existing gforge system you might want to re-write part of the code
265 print "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\n";
266 print "Should i empty the artifacts database before importing from bugzilla???? (y/n)\n";
267 print "All existing tracker/forum data will be lost if you do that!\n";
270 print "You choose to delete all existing artifacts.\n";
271 print "Please edit the perl script and remove the exit statement from the line below.\n";
272 print "This is done to prevent accidental removal of your existing bugs.\n";
275 $SF_DBH->do("delete from artifact_message");
276 $SF_DBH->do("delete from artifact_file");
277 $SF_DBH->do("delete from artifact_history");
278 $SF_DBH->do("delete from artifact");
281 my @bzbugs_fields = ( 'bug_id',
295 my $bzbugs_fieldstring = join (", ", @bzbugs_fields);
297 # all the timestamps are stored as unixtime in integer fields
298 # in the gforge database. dumb, but we're stuck with it.
299 $bzbugs_fieldstring =~ s/(\w+_ts)/UNIX_TIMESTAMP($1) AS $1/g;
301 # first, handle the bugs table
302 # get all the records from the table, and loop through them
303 # for each one, loop through the fields, doing the appropriate
304 # conversion for each. build an array of field names and and
305 # an array of values.
306 my $sql = "SELECT $bzbugs_fieldstring from bugs";
307 my $bz_sth = $BZ_DBH->prepare($sql);
310 while(my $bug = $bz_sth->fetchrow_hashref) {
311 print "\n\n****************************************************\nBugzilla bug # $bug->{bug_id}: ";
313 # print "Hit key to continue!\n->";
317 foreach my $field (@bzbugs_fields) {
318 # warn "field is $field.\n";
319 $MREF->{$field}($bug, $sf_bug);
322 # print "original data:\n--------------\n", Dumper($bug);
323 # print "new data: \n--------------\n", Dumper($sf_bug);
325 # insert values into sf db.
326 # first create list of fieldnames and values
328 foreach my $key (sort keys %$sf_bug) {
330 # most of the gforge fields require NOT NULL.
331 # if(length($sf_bug->{$key})<1) { $sf_bug->{$key} = '0' };
332 push @values, "esub($sf_bug->{$key});
333 # print "key: $key -> " . "esub($sf_bug->{$key}) . "\n";
336 push @fields, "group_artifact_id";
337 push @values, $SFBUGTRACKERID;
339 my $n = scalar(@values);
340 my $placeholders = '?,'x$n; chop $placeholders;
342 my $sql = 'INSERT INTO artifact (' . join( ',', @fields)
343 . ") VALUES ($placeholders )";
344 print "-> Inserting new bug #$bug->{bug_id} into table artifact...\n";
345 # warn "bug insert sql: $sql\n";
347 my $sf_sth = $SF_DBH->prepare($sql);
348 $sf_sth->execute(@values);
351 # since we keep the bug_id we don't need the ID_MAP
352 # change this if you plan to import into existing artifacts table where you cannot choose your own bug id.
353 # # after the insert, get the bug_id of the bug just inserted,
354 # # using the postgres "currval" function.
355 # # then add to the ID_MAP hash.
356 # $sql = 'select currval(\'bug_pk_seq\')';
357 # $sf_sth = $SF_DBH->prepare($sql);
359 my $sf_bug_id = $bug->{bug_id};
360 # print " -> transferred to gforge bug $sf_bug_id.\n";
361 $ID_MAP{$bug->{bug_id}} = $sf_bug_id;
364 # artifact_history is not filled, we dont care for that type of data
366 # get the comments from bugzilla.longdescs into gforge.artifact_messages
367 my $bz_sth = $BZ_DBH->prepare('SELECT thetext, UNIX_TIMESTAMP(bug_when) as date, who FROM longdescs WHERE bug_id=? order by bug_when');
368 $bz_sth->execute($bug->{bug_id});
369 my $longdesc = $bz_sth->fetchrow_hashref; # throw this away, we already have it.
371 $sql = "INSERT INTO artifact_message (artifact_id, submitted_by, from_email, adddate, body) VALUES ($sf_bug_id, ?, ?, ?, ?)";
372 # warn "inserting longdescs SQL: $sql";
374 while($longdesc = $bz_sth->fetchrow_hashref) {
375 my $body = "esub($longdesc->{thetext});
376 my $adddate = $longdesc->{date};
377 my $submitted_by = $MREF->{user}($longdesc->{who});
379 print "-> Adding message for bug #$sf_bug_id ...\n";
380 $sf_sth = $SF_DBH->prepare($sql);
381 $sf_sth->execute($submitted_by, $from_email, $adddate, $body);
384 # now copy file attachments (at least try it)
386 $bz_sth = $BZ_DBH->prepare('SELECT thedata, UNIX_TIMESTAMP(creation_ts) as date, description, mimetype, filename, submitter_id, bug_id FROM attachments WHERE bug_id=? order by creation_ts');
387 $bz_sth->execute($bug->{bug_id});
389 $sql = "INSERT INTO artifact_file (artifact_id, description, bin_data, filename, filesize, filetype, adddate, submitted_by) VALUES ($sf_bug_id, ?, ?, ?, ?, ?, ?, ?)";
390 # warn "inserting attachment SQL: $sql";
392 while(my $file = $bz_sth->fetchrow_hashref) {
393 my $bin_data = $file->{thedata};
394 my $filetype = $file->{mimetype};
395 my $filesize = length($bin_data);
396 $bin_data = encode_base64($bin_data);
397 my $adddate = $file->{date};
398 my $submitted_by = $MREF->{user}($file->{submitter_id});
399 my $description = $file->{description};
400 my $filename = $file->{filename};
401 $filename =~ s/\\/\\\\/g;
402 print "-> Adding attachment for bug #$sf_bug_id ...\n";
403 $sf_sth = $SF_DBH->prepare($sql);
404 $sf_sth->execute($description, $bin_data, $filename, $filesize, $filetype,
405 $adddate, $submitted_by);
408 # done with bug activity. done with this bug, actually.
413 #############################################################
414 # update artifacts_counts_agg
415 sub update_artifacts_count_agg {
418 my $sql = "select count(*) as cnt from artifact where group_artifact_id = $SFBUGTRACKERID";
419 my $bugstotal = $SF_DBH->selectrow_array($sql);
421 # get number of open bugs
422 $sql = "select count(*) as cnt from artifact where group_artifact_id = $SFBUGTRACKERID and status_id = 1";
423 my $bugsopen = $SF_DBH->selectrow_array($sql);
425 # update artifacts_count_agg
426 $sql = "update artifact_countS_agg set count = $bugstotal, open_count = $bugsopen where group_artifact_id = $SFBUGTRACKERID";
427 my $res = $SF_DBH->selectrow_array($sql);
431 #############################################################
432 # update artifact_seq
433 sub update_artifact_seq {
435 # since we used the bugzilla bug ids for the artifacts id and
436 # not the artifact_artifact_id_seq to generate ids, we have to adjust the
437 # currval of the sequence (otherwise you wouldnt be able to
438 # post new bug reports.
440 # get max(artifact_id)
441 my $sql = "select max(artifact_id) from artifact";
442 my $maxid = $SF_DBH->selectrow_array($sql);
444 # update sequence' currval
445 print "Updating sequence artifact_artifact_id_seq to $maxid\n";
446 $sql = "select setval('artifact_artifact_id_seq', $maxid)";
447 my $res = $SF_DBH->selectrow_array($sql);
451 #############################################################
453 # map bugzilla.bugs.component to gforge.artifact_category
454 sub create_artifact_categories {
456 my $sql = "select distinct component from bugs";
457 my $bz_sth = $BZ_DBH->prepare($sql);
460 while (my $component = $bz_sth->fetchrow_hashref) {
461 print "Bugzilla component: $component->{component}\n";
462 # check if category already exists
463 $sql = "select count(*) from artifact_category where group_artifact_id = ? and category_name = ?";
464 my $sf_sth = $SF_DBH->prepare($sql);
465 $sf_sth->execute($SFBUGTRACKERID, $component->{component});
466 my $count = $sf_sth->fetchrow_array();
469 $sql = "insert into artifact_category (group_artifact_id, category_name, auto_assign_to) " .
470 "values ($SFBUGTRACKERID, '$component->{component}', " . $MREF->{user}(0) . ")";
472 my $sf_sth = $SF_DBH->prepare($sql);
475 print "Category $component->{component} already exists for bugtracker $SFBUGTRACKERID\n";
480 #############################################################
481 # create artifact groups
482 # map bugzilla.bugs.version to gforge.artifact_group
483 # we use the artifact group value as version information of the buggy software module
484 sub create_artifact_groups {
486 my $sql = "select distinct version from bugs";
487 my $bz_sth = $BZ_DBH->prepare($sql);
490 while (my $version = $bz_sth->fetchrow_hashref) {
491 print "Bugzilla version: $version->{version}\n";
492 # check if group already exists
493 $sql = "select count(*) from artifact_group where group_artifact_id = ? and group_name = ?";
494 my $sf_sth = $SF_DBH->prepare($sql);
495 $sf_sth->execute($SFBUGTRACKERID, $version->{version});
496 my $count = $sf_sth->fetchrow_array();
498 $sql = "insert into artifact_group (group_artifact_id, group_name) " .
499 "values ($SFBUGTRACKERID, '$version->{version}')";
501 my $sf_sth = $SF_DBH->prepare($sql);
504 print "Version $version->{version} already exists for bugtracker $SFBUGTRACKERID\n";
509 #############################################################
511 # check if all bugzilla users have a mapping
512 # if not, then exit with a warning.
517 my $bz_sth = $BZ_DBH->prepare("select $col as user_id, count(*) as cnt from bugs group by $col");
519 while (my $user_id = $bz_sth->fetchrow_hashref) {
520 print "Checking $col user # $user_id->{user_id} = $user_id->{cnt}\n";
521 # check if user has a mapping, if not the scripts exits
522 $MREF->{user}($user_id->{user_id});
526 # select all bugzilla users that have a bug reported or assigned.
527 &$checkit("assigned_to");
528 &$checkit("reporter");
532 # the gforge database should not have any double-quotes.
535 # $text =~ s/"/"/g;