#!/usr/bin/perl
# bugz2sf - transfers bug reports from Bugzilla to Sourceforge.
# steev hise, steev AT datamassage.com, december 2001
#
# version 1.1 - copyleft 2001 - GNU Public License
#
# to customize this, of course you'll need to change a lot of 
# the mappings. Most important, the user and category maps.
# Mostly these things happen in the "init_maps"
# subroutine, though there are a few other places you
# might have to customize. Also note in the init_dbh subroutine
# you'll need to put in your database users and passwords and hosts.
#####################################################################

use strict;
use DBI;
use Data::Dumper;   # debugging only.

use vars qw( $BZ_DBH $SF_DBH $MREF %ID_MAP $SFGROUP);
$| = 1;
my $time = time;

# change this to the sourceforge group id of the project you're
# importing into.
$SFGROUP = 12;   # all the bugs are for this one sourceforge project.

($BZ_DBH, $SF_DBH) = &init_dbh;   # open all the database handles.
$MREF = &init_maps;  

my @bzbugs_fields = ( 'bug_id',
							 'assigned_to',
							 'bug_severity', 
							 'bug_status',
							 'creation_ts',
							 'delta_ts',
							 'short_desc',
							 'priority',
							 'reporter',
							 'component',
							 'resolution',
						);
						
my $bzbugs_fieldstring = join (", ", @bzbugs_fields);

# all the timestamps are stored as unixtime in integer fields
# in the sourceforge database. dumb, but we're stuck with it.
$bzbugs_fieldstring =~ s/(\w+_ts)/UNIX_TIMESTAMP($1) AS $1/g;


# first, handle the bugs table
# get all the records from the table, and loop through them
# for each one, loop through the fields, doing the appropriate
# conversion for each. build an array of field names and and 
# an array of values.
my $sql = "SELECT $bzbugs_fieldstring from bugs";
my $bz_sth = $BZ_DBH->prepare($sql);
$bz_sth->execute;

while(my $bug = $bz_sth->fetchrow_hashref) {
	print "Bugzilla bug $bug->{bug_id}:  ";
	my $sf_bug = {};
	foreach my $field (@bzbugs_fields) {
		# warn "field is $field.\n";
		$MREF->{$field}($bug, $sf_bug);
	}
	
	# print "original data:\n--------------\n", Dumper($bug);
	# print "new data:     \n--------------\n", Dumper($sf_bug);
	
	# insert values into sf db.
	# first create list of fieldnames and values
	my(@fields,@values);
	foreach my $key (sort keys %$sf_bug) {
		push @fields, $key;
		# most of the sourceforge fields require NOT NULL.
		if(length($sf_bug->{$key})<1) { $sf_bug->{$key} = '0' };
		push @values, &quotesub($sf_bug->{$key});		
	}	
	
	push @fields, "group_id";
	push @values, $SFGROUP;
	push @fields, "bug_group_id";
	push @values, 100;
	
	my $n = scalar(@values);
	my $placeholders = '?,'x$n; chop $placeholders;
	
	my $sql = 'INSERT INTO bug (' . join( ',', @fields) 
					. ") VALUES ($placeholders )";	
	# warn "bug insert sql: $sql\n";

	my $sf_sth = $SF_DBH->prepare($sql);
	$sf_sth->execute(@values);
	$sf_sth->finish;
	
	# after the insert, get the bug_id of the bug just inserted,
	# using the postgres "currval" function.
	# then add to the ID_MAP hash.
	$sql = 'select currval(\'bug_pk_seq\')';
	$sf_sth = $SF_DBH->prepare($sql);
	$sf_sth->execute;
	my ($sf_bug_id) =  $sf_sth->fetchrow_array;
	print " ->  transferred to Sourceforge bug $sf_bug_id.\n";
	$ID_MAP{$bug->{bug_id}} = $sf_bug_id;
	$sf_sth->finish;
	
	# now, for this bug, handle the bugs_activity table, 
	# which gets mapped to the bug_history table in SF.
	# this includes mapping the longdesc records into
	# bug_history "detail" records.

	# first, do the long_descs...
	my $bz_sth = $BZ_DBH->prepare('SELECT thetext, UNIX_TIMESTAMP(bug_when) as date, who FROM longdescs WHERE bug_id=? order by bug_when');
	$bz_sth->execute($bug->{bug_id});
	my $longdesc = $bz_sth->fetchrow_hashref;	 # throw this away, we already have it.
	
	$sql = "INSERT INTO bug_history (bug_id, field_name, old_value, mod_by, date) VALUES ($sf_bug_id, 'details', ?, ?, ?)";
	$sf_sth = $SF_DBH->prepare($sql);
	# warn "inserting longdescs SQL: $sql";
	
	while($longdesc = $bz_sth->fetchrow_hashref) {
		my $old_value = &quotesub($longdesc->{thetext});
		my $date = $longdesc->{date};
		my $mod_by = $MREF->{user}($longdesc->{who});
		$sf_sth->execute($old_value, $mod_by, $date);
	}
	$sf_sth->finish;
	$bz_sth->finish;
	
	# now the other kinds of bug activity...
	$sql = 'SELECT who, UNIX_TIMESTAMP(bug_when) as date, fielddefs.name as fieldname, oldvalue FROM bugs_activity, fielddefs WHERE bug_id=? AND bugs_activity.fieldid=fielddefs.fieldid order by bug_when';
	$bz_sth = $BZ_DBH->prepare($sql);
	$bz_sth->execute($bug->{bug_id});
	
	$sql = "INSERT INTO bug_history (bug_id, field_name, old_value, mod_by, date) VALUES ($sf_bug_id, ?, ?, ?, ?)"; 
	$sf_sth = $SF_DBH->prepare($sql);
	# warn "inserting history SQL: $sql";
	
	while(my $activity = $bz_sth->fetchrow_hashref) {
		my $mod_by = $MREF->{user}($activity->{who});
		my $old_value = &quotesub($activity->{oldvalue});
		if(length($old_value) < 1) { $old_value = ' ' };  # the postgres field is NOT NULL
		my $fn = $activity->{fieldname};
		if(length($fn) < 1) { $fn = ' ' };  # the postgres field is NOT NULL
		$sf_sth->execute($fn, $old_value, $mod_by, $activity->{date});
	}
	$sf_sth->finish;
	$bz_sth->finish;
	
	# done with bug activity. done with this bug, actually.	
	
}
$bz_sth->finish;

# finally, handle the dependencies	table, which gets
# mapped to the "bug_bug_dependencies" table in SF.
# this has to be done last because we have to have our complete
# bug_id mapping created.

$bz_sth = $BZ_DBH->prepare('SELECT * FROM dependencies');
$bz_sth->execute;

my $sf_sth = $SF_DBH->prepare('INSERT INTO bug_bug_dependencies (bug_id, is_dependent_on_bug_id) VALUES (?,?)');

while (my $dep =$bz_sth->fetchrow_hashref) {
	my $bug_id = $ID_MAP{$dep->{blocked}};
	my $dependson = $ID_MAP{$dep->{dependson}};
	$sf_sth->execute($bug_id,$dependson);
}
	  
$sf_sth->finish;
$bz_sth->finish;
$BZ_DBH->disconnect;
$SF_DBH->disconnect;

# there. done.
print "Done. Transferred " , scalar keys %ID_MAP, " bugs in ", time-$time, " seconds.\n\n";





############ subroutines  ##########################


# be sure to change the hosts, users, passwords to values appropo
# to your setup.
sub init_dbh {
	# first connect to the Bugzilla mysql database.
	my $bzdb = 'bugs';
	my $bzhost = 'foobar.com';
	my $bzdsn = "DBI:mysql:database=$bzdb;host=$bzhost";
	my $bzuser = 'bugs';
	my $bzpw   = 'yourpwhere';

	my $bz_dbh = DBI->connect($bzdsn, $bzuser, $bzpw);
	$bz_dbh->{ RaiseError } = 1;

	# now connect to the sourceforge postgres database
	my $sfdb = 'sourceforge';
	# my $sfhost = 'barfoo.com';  # probably running locally so uneeded.
	my $sfdsn = "DBI:Pg:dbname=$sfdb";
	my $sfuser = 'postgres';
	my $sfpw   = 'yourpwhere';

	my $sf_dbh = DBI->connect($sfdsn, $sfuser, $sfpw);
	$sf_dbh->{ RaiseError } = 1;
	
	return $bz_dbh, $sf_dbh;
}


# this just sets up some hashes and stuff for mapping between
# the bugzilla schema and the sourceforge schema.
sub init_maps {
	# this going to return a hash of references.
	# each reference is an anonymous subroutine.
	# each reference maps the values of certain fields from
	# one database to another.
	
	# you pass each subroutine the original value and it
	# returns the mapped value, plus, in some cases,
	# the name of the field in the destination table where it goes.
	
	my $mapref = {};
	
	# first, a few all-purpose mappings
	
	$mapref->{user} = sub {
		my ($bz_userid) = @_;
		my $usermap =
					{  1  =>  9,		# jon
						2  =>  4,		# mykle
						3  =>  7,		# petr
						4  =>  7,		# also petr
						5  =>  8,		# alx
						6  =>  3,		# steev
						0	=> 100,		# none - default
					};
		return $usermap->{$bz_userid};
	};	

	$mapref->{bug_id} = sub {
		my($bz, $sf) = @_;
		
		# find the first longdesc and use as details field in SF.
		my $bz_sth = $BZ_DBH->prepare('SELECT thetext FROM longdescs WHERE bug_id=? order by bug_when');
		$bz_sth->execute($bz->{bug_id});
		my ($text) = $bz_sth->fetchrow_array;
		$sf->{details} = $text;
		
		# now add a little note.
		$sf->{details} .= "\n\nNOTE: This bug is originally from Bugzilla, bug_id=$bz->{bug_id}.\n";
	};
	
	$mapref->{assigned_to} = sub {
		my($bz, $sf) = @_;
		$sf->{assigned_to} = $MREF->{user}($bz->{assigned_to});
	};
	
	$mapref->{bug_severity} = sub {
		my($bz, $sf) = @_;
		$sf->{details} .= "Original severity: ". $bz->{bug_severity};
	};
	
	$mapref->{bug_status} = sub {
		my($bz, $sf) = @_;
		my $status_map = {
							'UNCONFIRMED'	=> 1,
							'NEW'				=> 1,
							'ASSIGNED'		=> 1,
							'REOPENED'		=> 1,
							'RESOLVED'		=> 3,
							'VERIFIED'		=> 3,
							'CLOSED'			=> 3,
						};
		$sf->{status_id} = $status_map->{$bz->{bug_status}};
	};
	
	$mapref->{creation_ts} = sub {
		my($bz, $sf) = @_;
		$sf->{date} = $bz->{creation_ts};
	};
	
	# here we check the status, and if it's a closed bug,
	# we assign close_date the value of delta_ts.
	# this assumes that if a bug is closed, closing it
	# was the last thing ever done to it.
	$mapref->{delta_ts} = sub {
		my($bz, $sf) = @_;
		if($sf->{status_id} == 3) {
			$sf->{close_date} = $bz->{delta_ts};
		}
	};
	
	$mapref->{short_desc} = sub {
		my($bz, $sf) = @_;
		$sf->{summary} = $bz->{short_desc};
	};

	$mapref->{priority} = sub {
		my($bz, $sf) = @_;
		$bz->{priority} =~ s/P//;       # remove the stupid letter P.
		$sf->{priority} = $bz->{priority} * 2 - 1;
	};
	
	$mapref->{reporter} = sub {
		my($bz, $sf) = @_;
		$sf->{submitted_by} = $MREF->{user}($bz->{reporter});
	};
	
	# we're mapping bugzilla "components" to sourceforge "category" ids.
	# note that the actual category items in the category table were (must be)
	# entered by hand.
	$mapref->{component} = sub {
		my($bz, $sf) = @_;
		my $component_map = {
					'3D Applet'			=>	2,
					'Apparel Data'		=>	3,
					'Apparel Placement Interface'	=>	5,
					'ApparelizerCmd'					=> 6,
					'database'			=>	7,
					'Fixture Model'	=>	8,
					'Fixture Selection Interface'	=>	5,
					'Render Interface'				=> 5,
					''						=>	100,
				};
		$sf->{category_id} = $component_map->{$bz->{component}};
	};
	
	#  the names of the sourceforge resolutions are identical,
	# we just need to map the names to the ids.
	$mapref->{resolution} = sub {
		my($bz, $sf) = @_;	
		my $resolution_map = {
					''					=> 100,
					'FIXED'			=>	1,
					'INVALID'		=> 2,
					'WONTFIX'		=> 3,
					'LATER'			=> 4,
					'REMIND'			=>	5,
					'DUPLICATE'		=>	101,
					'WORKSFORME'	=>	6,
				};
		$sf->{resolution_id} = $resolution_map->{$bz->{resolution}};
	};
	
	return $mapref;	
}


# the sourceforge database should not have any double-quotes.
sub quotesub {
	my ($text) = @_;
	$text =~ s/"/&quot;/g;
	return $text;
}