WIP: Add flyspray migration class

Signed-off-by: Florian Pritz <bluewind@xinu.at>

1 files changed, 492 insertions, 0 deletions

diff --git a/Bugzilla/Migrate/Flyspray.pm b/Bugzilla/Migrate/Flyspray.pm
new file mode 100644
index 000000000..863fedafa
--- /dev/null
+++ b/Bugzilla/Migrate/Flyspray.pm
@@ -0,0 +1,492 @@
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+# This Source Code Form is "Incompatible With Secondary Licenses", as
+# defined by the Mozilla Public License, v. 2.0.
+
+package Bugzilla::Migrate::Flyspray;
+
+use 5.14.0;
+use strict;
+use warnings;
+
+use parent qw(Bugzilla::Migrate);
+
+use Bugzilla::Constants;
+use Bugzilla::Util qw(validate_email_syntax);
+use Bugzilla::DB;
+
+use IO::File;
+use List::Util qw(first none);
+use SQL::Abstract;
+
+#use constant REQUIRED_MODULES => [
+    #{
+        #package => 'SQL-Abstract',
+        #module  => 'SQL::Abstract',
+        #version => 0,
+    #},
+#];
+
+use constant FIELD_MAP => {
+    'BugID'          => 'bug_id',
+    'Category'       => 'product',
+    'Subject'        => 'short_desc',
+    'Responsible'    => 'assigned_to',
+    'State'          => 'bug_status',
+    'Class'          => 'cf_type',
+    'Classification' => '',
+    'Originator'     => 'reporter',
+    'CreationTS'     => 'creation_ts',
+    'Last-Modified'  => 'delta_ts',
+    'Release'        => 'version',
+    'Severity'       => 'bug_severity',
+    'comment'        => 'comment',
+    'comments'       => 'comments',
+    'history'        => 'history',
+	'attachments'    => 'attachments',
+};
+
+use constant VALUE_MAP => {
+    bug_severity => {
+        'critical' => 'critical',
+        'high'     => 'high',
+        'medium'   => 'medium',
+        'low'      => 'low',
+        'very low' => 'very low',
+    },
+    bug_status => {
+        'unconfirmed'         => 'NEW',
+        'new'                 => 'NEW',
+        'unassigned'          => 'NEW',
+        'assigned'            => 'CONFIRMED',
+        'researching'         => 'CONFIRMED',
+        'waiting on response' => 'WAITING',
+        'requires testing'    => 'WAITING',
+    },
+    resolution => {
+        'no response'      => 'NORESPONSE',
+        'upstream'         => 'UPSTREAM',
+        'not a bug'        => 'NOTABUG',
+        'won\'t fix'       => 'WONTFIX',
+        'won\'t implement' => 'WONTFIX',
+        'works for me'     => 'WORKSFORME',
+        'duplicate'        => 'DUPLICATE',
+        'deferred'         => 'DEFERRED',
+        'fixed'            => 'FIXED',
+        'implemented'      => 'FIXED',
+    },
+    bug_severity => {
+        'Flash'     => 'Flash',
+        'Immediate' => 'Immediate',
+        'Urgent'    => 'Urgent',
+        'High'      => 'High',
+        'Normal'    => 'Normal',
+        'Low'       => 'Low',
+    },
+};
+
+use constant FLYSPRAY_CONFIG_VARS => (
+    {
+        name    => 'flyspray_db',
+        default => 'bugzilla_flyspray',
+        desc    => <<END,
+# The database name of the flyspray databse.
+END
+    },
+    {
+        name    => 'flyspray_prefix',
+        default => 'flyspray_',
+        desc    => <<'END',
+# The prefix of the flyspray database tables.
+END
+    },
+    {
+        name    => 'component_owner',
+        default => 'bluewind',
+        desc    => <<'END',
+# The username of the user that is set as the initial owner for components.
+END
+    },
+    {
+        name    => 'unknown_user_email',
+        default => 'nobody@localhost.localdomain',
+        desc    => <<'END',
+# The email address for the fallback user if a task is created by/assigned to an unknown user.
+END
+    },
+    {
+        name    => 'attachment_path',
+        default => './flyspray-attachments',
+        desc    => <<'END',
+# The path of the flyspray attachments directory (or a copy).
+END
+    },
+);
+
+sub CONFIG_VARS {
+    my $self = shift;
+    my @vars = (FLYSPRAY_CONFIG_VARS, $self->SUPER::CONFIG_VARS);
+    my $field_map = first { $_->{name} eq 'translate_fields' } @vars;
+    $field_map->{default} = FIELD_MAP;
+    my $value_map = first { $_->{name} eq 'translate_values' } @vars;
+    $value_map->{default} = VALUE_MAP;
+    return @vars;
+}
+
+use constant NON_COMMENT_FIELDS => qw(
+);
+
+sub new {
+    my ($class) = @_;
+    my $self = { };
+    bless $self, $class;
+	$self->{dbh} = Bugzilla->dbh;
+	$self->{sql} = SQL::Abstract->new;
+    return $self;
+}
+
+sub _select_fs {
+	my ($self, $table, $fields, $where) = @_;
+
+	my @fields = qw(category_name shot_in_list);
+	my $db = $self->config('flyspray_db');
+	my($stmt, @bind) = $self->{sql}->select(
+		[map { sprintf("%s.%s%s", $self->config('flyspray_db'), $self->config('flyspray_prefix'), $_) } @$table],
+		$fields,
+		$where
+	);
+	$self->debug($stmt, 2);
+	$self->debug(\@bind, 2);
+	my $sth = $self->{dbh}->prepare($stmt);
+	$sth->execute(@bind);
+
+	return $sth;
+}
+
+sub _read_users {
+	my $self = shift;
+
+	my $sth = $self->_select_fs(["users"], [qw(user_id user_name real_name email_address)]);
+
+	my @result;
+	while (my $row = $sth->fetchrow_hashref()) {
+		next unless validate_email_syntax($row->{email_address});
+		my $item = {
+			login_name => $row->{user_name} =~ s/\s//gr =~ s/@//gr,
+			realname => $row->{real_name},
+			email => $row->{email_address},
+			cryptpassword => "*",
+		};
+        push @result, $item;
+		$self->{map_user_id_to_email}->{$row->{user_id}} = $item->{email};
+		$self->{map_email_to_name}->{$row->{email_address}} = $item->{login_name};
+	}
+
+	# Remove duplicate users based on email; keep the latesst account
+	my %seen;
+	@result = reverse grep { !$seen{$_->{email}}++ } reverse @result;
+
+	# fallback user for cases where the real user can not be
+	# created or has been deleted
+	push @result, {
+		login_name => 'UnknownUser',
+		realname => 'Unknown User',
+		email => $self->config("unknown_user_email"),
+		cryptpassword => "*",
+		is_enabled => 0,
+	};
+	$self->{map_user_id_to_email}->{-1} = $self->config("unknown_user_email");
+	$self->{map_email_to_name}->{$self->config("unknown_user_email")} = 'UnknownUser';
+
+	$self->debug(\@result, 3);
+	#$self->debug([grep { $_->{email} eq 'xx@x.com'  } @result], 2);
+	$self->debug("Migrating ".(@result+0)." users");
+	return \@result;
+}
+
+sub _read_products {
+	my $self = shift;
+
+	my $sth = $self->_select_fs(["projects"], [qw(project_id project_title intro_message)]);
+
+	my @result;
+	while (my $row = $sth->fetchrow_hashref()) {
+		push @result, {
+			name => $row->{project_title},
+			description => $row->{intro_message} || "N/A",
+			isactive => 1,
+			components => $self->_get_fs_project_components($row->{project_id}),
+			version => "unspecified",
+		};
+		$self->{map_project_id_to_name}->{$row->{project_id}} = $row->{project_title};
+	}
+
+	$self->debug(\@result, 3);
+	$self->debug("Migrating ".(@result+0)." products");
+	return \@result;
+}
+
+sub _get_fs_project_components {
+	my $self = shift;
+	my $project_id = shift;
+
+	my $sth = $self->_select_fs(["list_category"], [qw(category_id category_name show_in_list)], {project_id => $project_id});
+
+	my @result;
+	while (my $row = $sth->fetchrow_hashref()) {
+		push @result, {
+			name => $row->{category_name},
+			description => "N/A",
+			initialowner => $self->config("component_owner"),
+			isactive => 1,
+		};
+		$self->{map_component_id_to_name}->{$row->{category_id}} = $row->{category_name};
+        $self->{project_id_components}->{$project_id}->{$row->{category_name}} = 1;
+	}
+    push @result, {
+        name => "Uncategorized",
+        description => "N/A",
+        initialowner => $self->config("component_owner"),
+        isactive => 1,
+    };
+    $self->{map_component_id_to_name}->{-1} = "Uncategorized";
+
+	$self->debug(\@result, 3);
+	$self->debug("Found ".(@result+0)." components in project $project_id");
+	return \@result;
+}
+
+sub _read_bugs {
+	my $self = shift;
+	my $fsdb = $self->config("flyspray_db");
+	my $fsprfx = $self->config("flyspray_prefix");
+
+	#my $sth = $self->_select_fs([qw(tasks users)], [qw(task_id product_category project_id item_summary detailed_desc user_name)], {'tasks.opened_by' => \' = users.user_id' });
+	my $sth = $self->{dbh}->prepare("
+		SELECT
+			t.task_id,
+			t.product_category,
+			t.project_id,
+			t.item_summary,
+			t.detailed_desc,
+			t.opened_by,
+			t.task_priority,
+			t.item_status,
+			t.date_opened,
+			t.last_edited_time,
+			a.user_id assigned_to,
+			ls.status_name,
+			t.is_closed,
+			lr.resolution_name,
+			lv.version_name
+		FROM $fsdb.${fsprfx}tasks t
+		LEFT OUTER JOIN $fsdb.${fsprfx}assigned a ON a.task_id = t.task_id
+		LEFT OUTER JOIN $fsdb.${fsprfx}list_status ls ON ls.status_id = t.item_status
+		LEFT OUTER JOIN $fsdb.${fsprfx}list_resolution lr ON lr.resolution_id = t.resolution_reason
+		LEFT OUTER JOIN $fsdb.${fsprfx}list_version lv ON lv.version_id = t.product_version
+		WHERE t.task_id IN (23884, 22102)
+		GROUP BY t.task_id, a.task_id
+		");
+	$sth->execute();
+
+	my @result;
+	while (my $row = $sth->fetchrow_hashref()) {
+		my $item = {
+			short_desc => $row->{item_summary},
+			product => $self->{map_project_id_to_name}->{$row->{project_id}},
+			component => $self->{map_component_id_to_name}->{$row->{product_category}} // $self->{map_component_id_to_name}->{-1},
+			bug_id => $row->{task_id},
+			comments => [],
+			history => [],
+			attachments => [],
+			assigned_to => $self->_get_username($row->{assigned_to}),
+			op_sys => "Linux",
+			rep_platform => "PC",
+			bug_status => $row->{status_name} // "Unknown",
+			bug_severity => $row->{task_priority},
+			resolution => $row->{is_closed} ? $row->{resolution_name} // 'UNKNOWN' : '',
+			reporter => $self->_get_username($row->{opened_by}),
+			creation_ts => $row->{date_opened},
+			delta_ts => $row->{last_edited_time},
+			version => $row->{version_name} // "unspecified",
+			comment => $row->{detailed_desc},
+		};
+		$item->{version} = "unspecified" if $item->{version} eq "None";
+		$item->{resolution} = "UNSPECIFIED" if $item->{resolution} eq "None";
+		$item->{bug_status} = "RESOLVED" if $item->{resolution} ne "";
+        $item->{component} = $self->{map_component_id_to_name}->{-1} if not defined $self->{project_id_components}->{$row->{project_id}}->{$item->{component}};
+
+        # handle initial comment/description
+		if (length($item->{comment}) > 65535) {
+			$item->{comment} = "--- The description has been moved to an attachment because it is longer than 65535 characters ---";
+			unshift @{$item->{attachments}}, {
+				data => $row->{detailed_desc},
+				description => "Initial bug description",
+				mimetype => "text/plain",
+				filename => "description.txt",
+				creation_ts => $self->parse_date($row->{date_opened}),
+			};
+		}
+
+		my $comments = {};
+        # fetch comments
+		my $comments_sth = $self->{dbh}->prepare_cached("
+			SELECT comment_id, user_id, date_added, comment_text
+			FROM $fsdb.${fsprfx}comments WHERE task_id = ?");
+		$comments_sth->execute($row->{task_id});
+		while (my $comment = $comments_sth->fetchrow_hashref()) {
+			my $comment_item = {
+				who => $self->_get_username($comment->{user_id}),
+				bug_when => $self->parse_date($comment->{date_added}),
+				thetext => $comment->{comment_text},
+			};
+			push @{$item->{comments}}, $comment_item;
+			$comments->{$comment->{comment_id}} = $comment_item;
+		}
+
+        # get history
+		my $history_sth = $self->{dbh}->prepare_cached("
+			SELECT user_id,
+				event_type,
+				event_date,
+				field_changed,
+				old_value,
+				new_value,
+				lr.resolution_name,
+				lsn.status_name status_name_new,
+				lso.status_name status_name_old
+			FROM $fsdb.${fsprfx}history
+			LEFT OUTER JOIN $fsdb.${fsprfx}list_resolution lr ON lr.resolution_id = new_value
+			LEFT OUTER JOIN $fsdb.${fsprfx}list_status lsn ON lsn.status_id = new_value
+			LEFT OUTER JOIN $fsdb.${fsprfx}list_status lso ON lso.status_id = old_value
+			WHERE task_id = ?");
+		$history_sth->execute($row->{task_id});
+		while (my $history = $history_sth->fetchrow_hashref()) {
+			if ($history->{event_type} == 2) {
+			# type 2 -> closed bug
+				my $closure_comment = $history->{old_value};
+				if ($closure_comment ne "") {
+					push @{$item->{comments}}, {
+						who => $self->_get_username($history->{user_id}),
+						bug_when => $self->parse_date($history->{event_date}),
+						thetext => $closure_comment,
+					};
+				}
+
+				push @{$item->{history}}, {
+					who => $self->_get_username($history->{user_id}),
+					bug_when => $self->parse_date($history->{event_date}),
+					field => "resolution",
+					removed => "",
+					added => $history->{resolution_name},
+				};
+			} elsif ($history->{event_type} == 3) {
+			# type 3 -> field changed
+				if ($history->{field_changed} eq "item_status") {
+					push @{$item->{history}}, {
+						who => $self->_get_username($history->{user_id}),
+						bug_when => $self->parse_date($history->{event_date}),
+						field => "bug_status",
+						removed => $history->{status_name_old} // "",
+						added => $history->{status_name_new},
+					};
+				}
+				# TODO Add more field type mappings
+			}
+			# TODO Add more events?
+			# optional for now: CC changes, assignee changes, milestone/version changes
+		}
+
+        # TODO get attachments
+		my $attachments_sth = $self->{dbh}->prepare_cached("
+			SELECT task_id, orig_name, file_name, file_desc, file_type, file_size, added_by, date_added
+			FROM $fsdb.${fsprfx}attachments
+			WHERE task_id = ?
+			");
+		$attachments_sth->execute($row->{task_id});
+		my $attpath = $self->config("attachment_path");
+		while (my $attachment = $attachments_sth->fetchrow_hashref()) {
+			push @{$item->{attachments}}, {
+				submitter => $self->_get_username($attachment->{added_by}),
+				creation_ts => $attachment->{date_added},
+				description => $attachment->{file_desc} || $attachment->{orig_name},
+				filename => $attachment->{orig_name},
+				mimetype => $attachment->{file_type},
+				ispatch => $attachment->{file_type} =~ m/^text\/x-diff/ ? 1 : 0,
+				data => IO::File->new($attpath.'/'.$attachment->{file_name}, 'r'),
+			};
+			# TODO: set extra_data and type of comment when attachment is part of a comment.
+			# problem: how to get attachment id? not yet in db
+			# change _insert_attachments/_insert_comments?
+		}
+
+		push @result, $item;
+	}
+
+	#$self->debug(\@result, 3);
+	#@result = grep { $_->{bug_id} == 56677  } @result;
+	#$self->debug([grep { $_->{bug_id} == 56677  } @result], 2);
+	#$self->debug([keys %{{map { $_->{version} => 1 } @result}}], 2);
+	#die;
+	$self->debug("Migrating ".(@result+0)." bugs");
+
+	# Ensure bug IDs are kept by preventing the validator from unsetting them
+	my $orig_validator = \&Bugzilla::Bug::run_create_validators;
+	*{Bugzilla::Bug::run_create_validators} = sub {
+		my ($class, $params) = @_;
+		my $bug_id = $params->{bug_id};
+		my $ret = $orig_validator->(@_);
+		$ret->{bug_id} = $bug_id;
+		return $ret;
+	};
+
+	# insert_create_data is not implemented in Bugzilla::Bug
+	#my $orig_create = \&Bugzilla::Bug::insert_create_data;
+	my $orig_create = \&Bugzilla::Object::insert_create_data;
+	*{Bugzilla::Bug::insert_create_data} = sub {
+		my ($class, $params) = @_;
+		my $bug_id = $params->{bug_id};
+		$orig_create->(@_);
+
+		# XXX: The next lines are copied from Bugzilla::Bug::insert_create_data and may need to be adjusted in the future!
+		# XXX: They also trigger hooks/audit again which is not really nice
+		my $object = $class->new($bug_id);
+		Bugzilla::Hook::process('object_end_of_create', { class => $class,
+														  object => $object });
+		$object->audit_log(AUDIT_CREATE) if $object->AUDIT_CREATES;
+		return $object;
+	};
+
+	return \@result;
+}
+
+sub _get_username {
+	my ($self, $userid) = @_;
+	my $email = $self->{map_user_id_to_email}->{$userid // -1} // $self->{map_user_id_to_email}->{-1};
+	return $self->{map_email_to_name}->{$email};
+}
+
+
+sub translate_bug {
+    my ($self, $fields) = @_;
+
+    my ($bug, $other_fields) = $self->SUPER::translate_bug($fields);
+
+	#TODO remove this function if not used
+
+    return wantarray ? ($bug, $other_fields) : $bug;
+}
+
+
+# Timestamps from flyspray are unix timestamps which Date::Parse
+# can't parse so the original parse_date() sub will just die
+sub parse_date {
+    my ($self, $date) = @_;
+	my $dt = DateTime->from_epoch( epoch => $date );
+    $dt->set_time_zone(Bugzilla->local_timezone);
+    return $dt->iso8601;
+}
+
+1;