path: root/Bugzilla/Migrate/Flyspray.pm

# 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 File::MimeInfo;

# FIXME: this is currently broken in Bugzilla::Migrate
#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_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',
  },
  priority => {
    0 => "--",
    1 => "Low",
    2 => "Normal",
    3 => "High",
    4 => "Highest",
    5 => "Immediate",
    6 => "Flash",
  },
  bug_severity => {
    0 => "--",
    1 => "enhancement",
    2 => "minor",
    3 => "normal",
    4 => "major",
    5 => "blocker",
  },
};

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@xinu.at',
    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()) {
    if (!validate_email_syntax($row->{email_address})) {
      $self->debug("Skipping invalid email: " . $row->{email_address}, 3);
      next;
    }
    my $item = {
      login_name    => $row->{email_address},
      realname      => $row->{real_name},
      cryptpassword => "*",
    };
    push @result, $item;
    $self->{map_user_id_to_email}->{$row->{user_id}} = $item->{login_name};
  }

  # Remove duplicate users based on email; keep the latest account
  my %seen;
  @result = reverse grep { !$seen{$_->{login_name}}++ } reverse @result;

  # fallback user for cases where the real user can not be
  # created or has been deleted
  push @result,
    {
    login_name    => $self->config("unknown_user_email"),
    realname      => 'Unknown User',
    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.task_severity,
			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
		GROUP BY t.task_id, a.task_id
		");
  $sth->execute();

  #WHERE t.task_id IN (21668, 22102, 23845, 23884)

  my @result;
  while (my $row = $sth->fetchrow_hashref()) {
    my $bug = {
      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_severity},
      priority     => $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},
    };
    $bug->{version}    = "unspecified" if $bug->{version} eq "None";
    $bug->{resolution} = "UNSPECIFIED" if $bug->{resolution} eq "None";
    $bug->{bug_status} = "RESOLVED"    if $bug->{resolution} ne "";
    $bug->{component}  = $self->{map_component_id_to_name}->{-1}
      if not defined $self->{project_id_components}->{$row->{project_id}}
      ->{$bug->{component}};

    # handle initial comment/description
    if (length($bug->{comment}) > 65535) {
      $bug->{comment}
        = "--- The description has been moved to an attachment because it is longer than 65535 characters ---";
      unshift @{$bug->{attachments}},
        {
        submitter   => $bug->{reporter},
        data        => $row->{detailed_desc},
        description => "Initial bug description",
        mimetype    => "text/plain",
        filename    => "description.txt",
        creation_ts => $self->parse_date($row->{date_opened}),
        };
    }

    # CC/bug watchers
    my $cc_sth = $self->{dbh}->prepare_cached("
			SELECT user_id
			FROM $fsdb.${fsprfx}notifications WHERE task_id = ?");
    $cc_sth->execute($row->{task_id});
    while (my $row = $cc_sth->fetchrow_hashref()) {
      push @{$bug->{cc}}, $self->_get_username($row->{user_id});
    }


    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 @{$bug->{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 @{$bug->{comments}},
            {
            who      => $self->_get_username($history->{user_id}),
            bug_when => $self->parse_date($history->{event_date}),
            thetext  => $closure_comment,
            };
        }

        push @{$bug->{history}},
          {
          who      => $self->_get_username($history->{user_id}),
          bug_when => $self->parse_date($history->{event_date}),
          field    => "resolution",
          removed  => "",
          added    => $history->{resolution_name},
          };
        if ($self->translate_value('resolution', $history->{resolution_name} // "") eq
          "DUPLICATE")
        {
          if (
            $self->translate_value('resolution', $bug->{resolution} // "") eq "DUPLICATE"
            and $closure_comment =~ m/FS#(\d+)/)
          {
            push @{$self->{dupes}},
              {
              dupe_of  => int($1),
              dupe     => $row->{task_id},
              who      => $self->_get_username($history->{user_id}),
              bug_when => $self->parse_date($history->{event_date}),
              };

            push @{$bug->{comments}},
              {
              type       => CMT_DUPE_OF,
              extra_data => int($1),
              who        => $self->_get_username($history->{user_id}),
              bug_when   => $self->parse_date($history->{event_date}),
              };

            push @{$self->{bug_map}->{int($1)}->{comments}},
              {
              type       => CMT_HAS_DUPE,
              extra_data => $row->{task_id},
              who        => $self->_get_username($history->{user_id}),
              bug_when   => $self->parse_date($history->{event_date}),
              }
              if defined $self->{bug_map}->{int($1)};
          }
        }
      }
      elsif ($history->{event_type} == 3) {

        # type 3 -> field changed
        if ($history->{field_changed} eq "item_status") {
          push @{$bug->{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()) {
      my $path = $attpath . '/' . $attachment->{file_name};
      next unless -f $path;
      next unless -s $path;
      my $attachment_item = {
        submitter   => $self->_get_username($attachment->{added_by}),
        creation_ts => $self->parse_date($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_path => $path,
      };

#$attachment_item->{mimetype} = mimetype($path) if $attachment_item->{mimetype} eq "" or $attachment_item->{mimetype} !~ m/.+\/.+/;
      $attachment_item->{mimetype} = mimetype($path);

      # 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 @{$bug->{attachments}}, $attachment_item;

      #$self->debug($bug);
      #die if $attachment->{date_added} == 1110715864;
    }

    $self->{bug_map}->{$bug->{bug_id}} = $bug;

    push @result, $bug;
  }

  #$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};
  $self->{email_to_keep}->{$email} = 1;
  return $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;
}

sub after_read {
  my ($self) = @_;

  # only migrate users that have comments/attachments/are used somewhere
  $self->{users} = [grep { defined $self->{email_to_keep}->{$_->{login_name}} }
      @{$self->{users}}];
}

sub after_insert {
  my ($self) = @_;

  $self->debug("Marking duplicate bugs");
  $self->debug($self->{dupes}, 3);
  for my $entry (@{$self->{dupes}}) {
    my $dupeOf_bug = Bugzilla::Bug->new($entry->{dupe_of});
    $self->{dbh}->do("INSERT INTO duplicates (dupe_of, dupe) VALUES (?, ?)",
      undef, $entry->{dupe_of}, $entry->{dupe})
      unless defined $dupeOf_bug->{error};
  }
}


# 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;