View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0010087 | mantisbt | migration | public | 2009-01-26 15:57 | 2009-09-08 10:15 |
Reporter | azatoth | Assigned To | |||
Priority | normal | Severity | feature | Reproducibility | N/A |
Status | acknowledged | Resolution | open | ||
Product Version | 1.2.0a3 | ||||
Summary | 0010087: Flyspray conversion script | ||||
Description | An conversion script from flyspray (0.9.8); this will truncate all relevant tables, so use with precaution :) | ||||
Tags | No tags attached. | ||||
Attached Files | mantisconvert.pl (18,802 bytes)
#!/usr/bin/perl use strict; use warnings; use Term::ProgressBar 2.00; use DateTime::Format::MySQL; use DateTime; use DBI; use Getopt::Long; use File::Slurp; use File::Spec; my $attachment_dir = 'attachments'; my %conv_priority = ( 1 => 10, # Low 2 => 20, # Normal 3 => 30, # High 4 => 40, # Urgent 5 => 50, # Immediate 6 => 60, # Flash ); my %conv_severity = ( 1 => 20, # Very Low 2 => 40, # Low 3 => 50, # Medium 4 => 60, # High 5 => 70, # Critical ); my %conv_resolution = ( 1 => 10, # None 2 => 70, # Not a bug 3 => 90, # Won't fix 4 => 90, # Won't implement 5 => 40, # WFM 6 => 60, # Dupe 7 => 80, # Deferred 8 => 20, # Fixed 9 => 20, # Implemented ); my %conv_status = ( 1 => 10, # Unconfirmed 2 => 10, # New 3 => 50, # Assigned 4 => 30, # Researching 5 => 30, # Waiting on customer 6 => 10, # Requires testing 7 => 20, # Repoened 8 => 90, # Closed (removed in 0.9.6) ); my %conv_history = ( 0 => 0, # Fields changed in a task 1 => 1, # New task created 2 => 0, # Task closed 3 => 6, # Task edited (for backwards compatibility with events prior to the history system) 4 => 2, # Comment added 5 => 3, # Comment edited 6 => 4, # Comment deleted 7 => 9, # Attachment added 8 => 10, # Attachment deleted 9 => 12, # User added to notification list 10 => 13, # User removed from notification list 11 => undef, # Related task added to this task 12 => undef, # Related task removed from this task 13 => undef, # Task re-opened 14 => undef, # Task assigned to user / re-assigned to different user / Unassigned 15 => undef, # This task was added to another task's related list 16 => undef, # This task was removed from another task's related list 17 => undef, # Reminder added 18 => undef, # Reminder deleted 19 => undef, # User took ownership 20 => undef, # Closure request made 21 => undef, # Re-opening request made 22 => undef, # Adding a new dependency 23 => undef, # This task added as a dependency of another task 24 => undef, # Removing a dependency 25 => undef, # This task removed from another task's dependency list 26 => undef, # Task was made private 27 => undef, # Task was made public 28 => undef, # PM request denied ); my $cur_step = 0; my $max_step = 10; ###################################### my $verbose = 0; GetOptions( "verbose!" => \$verbose ); my( $local_max_step, $local_cur_step ); my $progress = Term::ProgressBar->new({name => 'Progress', count => $max_step*100, ETA => 'linear', } ); $progress->max_update_rate(1); my $olddsn = 'dbi:mysql:flyspray'; my $olddbuser = ''; my $olddbpass = ''; my $newdsn = 'dbi:mysql:mantis'; my $newdbuser = ''; my $newdbpass = ''; my $olddbh = DBI->connect( $olddsn, $olddbuser, $olddbpass); my $newdbh = DBI->connect( $newdsn, $newdbuser, $newdbpass); $newdbh->{mysql_enable_utf8} = 1; $newdbh->do("SET NAMES 'utf8'"); my( $newsth, $oldsth ); my( $newsth2, $oldsth2 ); # Users $progress->message("Importing users"); $cur_step++; $oldsth = $olddbh->prepare("SELECT DISTINCT flyspray_users.user_id, flyspray_groups.group_name, flyspray_history.event_date AS date, user_name, real_name, user_pass, email_address, account_enabled FROM flyspray_users LEFT JOIN flyspray_users_in_groups ON flyspray_users.user_id = flyspray_users_in_groups.user_id LEFT JOIN flyspray_groups ON flyspray_groups.group_id = flyspray_users_in_groups.group_id LEFT JOIN flyspray_history ON flyspray_users.user_id = flyspray_history.user_id GROUP BY flyspray_users.user_id"); $newsth = $newdbh->prepare("INSERT INTO mantis_user_table (id, username, realname, email, password, enabled, access_level, cookie_string, date_created) VALUES (?,?,?,?,?,?,?,?,?)"); $oldsth->execute(); $local_max_step = $oldsth->rows; $local_cur_step = 0; $newdbh->do("TRUNCATE mantis_user_table"); $progress->message("Truncating table mantis_user_table") if $verbose; my %accesslevels = ( 'Admin' => 90, 'Developers' => 55, 'Reporters' => 25 ); my $cookieCounter = 1; foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing user %s", $entry->{user_name}) if $verbose; $newsth->execute( $entry->{user_id}, $entry->{user_name}, $entry->{real_name}, $entry->{email_address}, $entry->{user_pass}, $entry->{account_enabled}, $accesslevels{$entry->{group_name}} // 10, 'cookiestring'.++$cookieCounter, $entry->{date} ? DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date})) : 0 ); } $progress->update($cur_step*100); # Projects $progress->message("Importing projects"); my %projects; $cur_step++; $oldsth = $olddbh->prepare("SELECT project_id, project_title, project_is_active, intro_message, anon_open FROM flyspray_projects"); $newsth = $newdbh->prepare("INSERT INTO mantis_project_table (id, name, enabled, description, view_state) VALUES(?,?,?,?,?)"); $oldsth->execute(); $local_max_step = $oldsth->rows; $local_cur_step = 0; $newdbh->do("TRUNCATE mantis_project_table"); $progress->message("Truncating table mantis_project_table") if $verbose; foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing project %s", $entry->{project_title}) if $verbose; $newsth->execute( $entry->{project_id}, $entry->{project_title}, $entry->{project_is_active}, $entry->{intro_message}, $entry->{anon_open} == 1 ? 10 : 50 ); $projects{$entry->{project_id}} = $entry->{project_title}; } $progress->update($cur_step*100); # Categories $progress->message("Importing categories"); $cur_step++; $oldsth = $olddbh->prepare("SELECT category_id, category_name, project_id, category_owner FROM flyspray_list_category "); $newsth = $newdbh->prepare("INSERT INTO mantis_category_table (project_id, name, user_id) VALUES(?,?,?)"); $newsth2 = $newdbh->prepare("INSERT IGNORE INTO mantis_project_user_list_table (project_id, user_id, access_level) VALUES(?,?,?)"); $oldsth->execute(); $local_max_step = $oldsth->rows; $local_cur_step = 0; $newdbh->do("TRUNCATE mantis_category_table"); $progress->message("Truncating table mantis_category_table") if $verbose; $newdbh->do("TRUNCATE mantis_project_user_list_table"); $progress->message("Truncating table mantis_project_user_list_table") if $verbose; my $lastProduct = -1; my %categoryNames; my %categorylinks; foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) { if( $lastProduct != $entry->{project_id} ) { %categoryNames = (); } ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing category '%s' for project '%s'", $entry->{category_name}, $projects{$entry->{project_id}}) if $verbose; $newsth->execute( $entry->{project_id}, exists $categoryNames{$entry->{category_name}} ? "$entry->{category_name} $entry->{category_id}" : $entry->{category_name}, $entry->{category_owner} ); $categorylinks{$entry->{category_id}} = $newdbh->last_insert_id(undef,undef,undef,undef); $newsth2->execute( $entry->{project_id}, $entry->{category_owner}, 55 ); $categoryNames{$entry->{category_name}} = 1; $lastProduct = $entry->{project_id}; } $progress->update($cur_step*100); # Versions $progress->message("Importing versions"); $cur_step++; $oldsth = $olddbh->prepare("SELECT version_id, project_id, version_name, version_tense FROM flyspray_list_version "); $newsth = $newdbh->prepare("INSERT IGNORE INTO mantis_project_version_table (project_id, version, released, obsolete) VALUES(?,?,?,?)"); $oldsth->execute(); $local_max_step = $oldsth->rows; $local_cur_step = 0; my %versionlinks; $newdbh->do("TRUNCATE mantis_project_version_table"); $progress->message("Truncating table mantis_project_version_table") if $verbose; foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing version '%s' for project '%s'", $entry->{version_name}, $projects{$entry->{project_id}}) if $verbose; $newsth->execute( $entry->{project_id}, $entry->{version_name}, $entry->{version_tense} == 3 ? 1 : 0, $entry->{version_tense} == 1 ? 1 : 0, ); $versionlinks{$entry->{version_id}} = $entry->{version_name}; } $progress->update($cur_step*100); # Bugs $progress->message("Importing bugs"); $cur_step++; $oldsth = $olddbh->prepare(" SELECT task_id, attached_to_project, date_opened, opened_by, is_closed, date_closed, closed_by, item_summary, detailed_desc, item_status, assigned_to, resolution_reason, product_category, product_version, closedby_version, operating_system, task_severity, task_priority, last_edited_by, last_edited_time, percent_complete, mark_private, due_date FROM flyspray_tasks "); $newsth = $newdbh->prepare(" INSERT INTO mantis_bug_text_table (description, steps_to_reproduce, additional_information) VALUES(?,?,?) "); $newsth2 = $newdbh->prepare(" INSERT INTO mantis_bug_table ( id, project_id, reporter_id, handler_id, priority, severity, status, resolution, category_id, date_submitted, last_updated, bug_text_id, version, fixed_in_version, summary, target_version ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) "); $oldsth->execute(); $local_max_step = $oldsth->rows; $local_cur_step = 0; $newdbh->do("TRUNCATE mantis_bug_text_table"); $progress->message("Truncating table mantis_bug_text_table") if $verbose; $newdbh->do("TRUNCATE mantis_bug_table"); $progress->message("Truncating table mantis_bug_table") if $verbose; foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing bug '%d' for project '%s'", $entry->{task_id}, $projects{$entry->{attached_to_project}}) if $verbose; $newsth->execute( $entry->{detailed_desc}, '', '' ); my $bug_text_id = $newdbh->last_insert_id(undef,undef,undef,undef); $newsth2->execute( $entry->{task_id}, $entry->{attached_to_project}, $entry->{opened_by}, $entry->{assigned_to}, $conv_priority{$entry->{task_priority}}, $conv_severity{$entry->{task_severity}}, $entry->{is_closed} ? 90 : $entry->{resolution_reason} == 8 ? 80 : $conv_status{$entry->{item_status}}, $entry->{resolution_reason} ? $conv_resolution{$entry->{resolution_reason}} : 10, $categorylinks{$entry->{product_category}}, DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_opened})), DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{last_edited_time})), $bug_text_id, $versionlinks{$entry->{product_version}}, $entry->{closedby_version} && $entry->{is_closed} ? $versionlinks{$entry->{closedby_version}} : '', $entry->{item_summary}, $entry->{closedby_version} ? $versionlinks{$entry->{closedby_version}} : '', ); } $progress->update($cur_step*100); # Bug notes $progress->message("Importing bug notes"); $cur_step++; my %commentlinks; $oldsth = $olddbh->prepare("SELECT comment_id, task_id, date_added, user_id, comment_text FROM flyspray_comments "); $newsth = $newdbh->prepare(" INSERT INTO mantis_bugnote_text_table (note) VALUES(?) "); $newsth2 = $newdbh->prepare(" INSERT INTO mantis_bugnote_table ( bug_id, reporter_id, bugnote_text_id, date_submitted, last_modified ) VALUES(?,?,?,?,?) "); $oldsth->execute(); $local_max_step = $oldsth->rows; $local_cur_step = 0; $newdbh->do("TRUNCATE mantis_bugnote_text_table"); $progress->message("Truncating table mantis_bugnote_text_table") if $verbose; $newdbh->do("TRUNCATE mantis_bugnote_table"); $progress->message("Truncating table mantis_bugnote_table") if $verbose; foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing bugnote '%d' for bug '%s'", $entry->{comment_id}, $entry->{task_id}) if $verbose; $newsth->execute( $entry->{comment_text}, ); my $bugnote_text_id = $newdbh->last_insert_id(undef,undef,undef,undef); $newsth2->execute( $entry->{task_id}, $entry->{user_id}, $bugnote_text_id, DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_added})), DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_added})), ); my $bugnote_id = $newdbh->last_insert_id(undef,undef,undef,undef); $commentlinks{$entry->{comment_id}} = sprintf "%07d", $bugnote_id; } $progress->update($cur_step*100); # Relations $progress->message("Importing relations"); $oldsth = $olddbh->prepare("SELECT task_id, dep_task_id FROM flyspray_dependencies"); $oldsth2 = $olddbh->prepare("SELECT this_task, related_task FROM flyspray_related"); $newsth = $newdbh->prepare("INSERT INTO mantis_bug_relationship_table (source_bug_id, destination_bug_id, relationship_type) VALUES(?,?,?)"); $oldsth->execute(); $oldsth2->execute(); $local_max_step = $oldsth->rows + $oldsth2->rows; $local_cur_step = 0; $newdbh->do("TRUNCATE mantis_bug_relationship_table"); $progress->message("Truncating table mantis_bug_relationship_table") if $verbose; foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing dependency from bug %d to bug %d", $entry->{dep_task_id}, $entry->{task_id}) if $verbose; $newsth->execute( $entry->{dep_task_id}, $entry->{task_id}, 2, ); } foreach my $entry ( @{$oldsth2->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing relation from bug %d to bug %d", $entry->{this_task}, $entry->{related_task}) if $verbose; $newsth->execute( $entry->{this_task}, $entry->{related_task}, 1, ); } $cur_step++; $progress->update($cur_step*100); # Monitoring $progress->message("Importing monitors"); $oldsth = $olddbh->prepare("SELECT task_id, user_id FROM flyspray_notifications"); $newsth = $newdbh->prepare("INSERT INTO mantis_bug_monitor_table (bug_id, user_id) VALUES(?,?)"); $oldsth->execute(); $local_max_step = $oldsth->rows; $local_cur_step = 0; $newdbh->do("TRUNCATE mantis_bug_monitor_table"); $progress->message("Truncating table mantis_bug_monitor_table") if $verbose; foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing monitoring for bug %d by user %d", $entry->{task_id}, $entry->{user_id}) if $verbose; $newsth->execute( $entry->{task_id}, $entry->{user_id}, ); } $cur_step++; $progress->update($cur_step*100); # Files $progress->message("Importing files"); my %filelinks; $oldsth = $olddbh->prepare("SELECT attachment_id,task_id, comment_id, orig_name, file_name, file_desc, file_type, file_size, added_by, date_added FROM flyspray_attachments"); $newsth = $newdbh->prepare("INSERT INTO mantis_bug_file_table ( bug_id, title, description, diskfile, filename, folder, filesize, file_type, date_added, content ) VALUES(?,?,?,?,?,?,?,?,?,?)"); $oldsth->execute(); $local_max_step = $oldsth->rows; $local_cur_step = 0; $newdbh->do("TRUNCATE mantis_bug_file_table"); $progress->message("Truncating table mantis_bug_file_table") if $verbose; foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); my $file = File::Spec->catfile($attachment_dir, $entry->{file_name}); next unless -f $file; my $content = read_file( $file ); $progress->message(sprintf "Importing file %d", $entry->{orig_name}) if $verbose; $newsth->execute( $entry->{task_id}, $entry->{orig_name}, $entry->{file_desc}, '', $entry->{orig_name}, '', $entry->{file_size}, $entry->{file_type}, DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_added})), $content, ); $filelinks{$entry->{attachment_id}} = $entry->{orig_name}; } $cur_step++; $progress->update($cur_step*100); # History $progress->message("Importing history"); $cur_step++; $oldsth = $olddbh->prepare("SELECT task_id, user_id, event_date, event_type, old_value, new_value, field_changed FROM flyspray_history"); $newsth = $newdbh->prepare("INSERT INTO mantis_bug_history_table ( user_id, bug_id, date_modified, field_name, old_value, new_value, type ) VALUES(?,?,?,?,?,?,?)"); $oldsth->execute(); $local_max_step = $oldsth->rows; $local_cur_step = 0; my $last_status = 10; $newdbh->do("TRUNCATE mantis_bug_history_table"); $progress->message("Truncating table mantis_bug_history_table") if $verbose; foreach my $entry ( @{$oldsth->fetchall_arrayref({})} ) { my $type = $conv_history{$entry->{event_type}}; ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); if( defined $type ) { my $old_value = $entry->{old_value}; my $new_value = $entry->{new_value}; my $field_name = $entry->{field_changed}; if( $entry->{event_type} == 4 ) { $old_value = $commentlinks{$new_value}; next unless $old_value; $new_value = ''; } if( $entry->{event_type} == 9 ) { $old_value = $new_value; $new_value = ''; } if( $entry->{event_type} == 7 ) { $old_value = $filelinks{$new_value}; next unless $old_value; $new_value = ''; } if( $entry->{event_type} == 2 ) { $field_name = 'status'; $old_value = $last_status; $new_value = 90; } if( $entry->{event_type} == 0 ) { if( $entry->{field_changed} eq 'task_severity' ) { $field_name = 'severity'; $old_value = $conv_severity{$old_value}; $new_value = $conv_severity{$new_value}; } elsif( $entry->{field_changed} eq 'item_status' ) { $field_name = 'status'; $old_value = $conv_status{$old_value}; $new_value = $conv_status{$new_value}; $last_status = $new_value; } elsif( $entry->{field_changed} eq 'percent_complete' ) { next; } } $progress->message(sprintf "Importing history for bug %d and user %d", $entry->{task_id}, $entry->{user_id}) if $verbose; $newsth->execute( $entry->{user_id}, $entry->{task_id}, DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{event_date})), $field_name, $old_value, $new_value, $type, ); } } $progress->update($cur_step*100); mantisconvert-1.0.pl (22,960 bytes)
#!/usr/bin/perl # Copyright © 2009 Carl Fürstenberg (AzaToth) # # Redistribution and use in source and binary forms, with or without # modification, are permitted provided that the following conditions # are met: # 1. Redistributions of source code must retain the above copyright # notice, this list of conditions and the following disclaimer. # 2. Redistributions in binary form must reproduce the above copyright # notice, this list of conditions and the following disclaimer in the # documentation and/or other materials provided with the distribution. # 3. Neither the name of the University nor the names of its contributors # may be used to endorse or promote products derived from this software # without specific prior written permission. # # THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ``AS IS'' AND # ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE # IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE # ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE # FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL # DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS # OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) # HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT # LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY # OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF # SUCH DAMAGE. use strict; use warnings; # Old database (flyspray) my $olddsn = 'dbi:mysql:flyspray'; my $olddbuser = ''; my $olddbpass = ''; # New database (mantis) my $newdsn = 'dbi:mysql:mantis'; my $newdbuser = ''; my $newdbpass = ''; # Directory where attachments are stored for flyspray my $attachment_dir = 'attachments'; my %conv_priority = ( 1 => 10, # Low 2 => 20, # Normal 3 => 30, # High 4 => 40, # Urgent 5 => 50, # Immediate 6 => 60, # Flash ); my %conv_severity = ( 1 => 20, # Very Low 2 => 40, # Low 3 => 50, # Medium 4 => 60, # High 5 => 70, # Critical ); my %conv_resolution = ( 1 => 10, # None 2 => 70, # Not a bug 3 => 90, # Won't fix 4 => 90, # Won't implement 5 => 40, # WFM 6 => 60, # Dupe 7 => 80, # Deferred 8 => 20, # Fixed 9 => 20, # Implemented ); my %conv_status = ( 1 => 10, # Unconfirmed 2 => 10, # New 3 => 50, # Assigned 4 => 30, # Researching 5 => 30, # Waiting on customer 6 => 10, # Requires testing 7 => 20, # Repoened 8 => 90, # Closed (removed in 0.9.6) ); my %conv_history = ( 0 => 0, # Fields changed in a task 1 => 1, # New task created 2 => 0, # Task closed 3 => 6, # Task edited (for backwards compatibility with events prior to the history system) 4 => 2, # Comment added 5 => 3, # Comment edited 6 => 4, # Comment deleted 7 => 9, # Attachment added 8 => 10, # Attachment deleted 9 => 12, # User added to notification list 10 => 13, # User removed from notification list 11 => undef, # Related task added to this task 12 => undef, # Related task removed from this task 13 => undef, # Task re-opened 14 => undef, # Task assigned to user / re-assigned to different user / Unassigned 15 => undef, # This task was added to another task's related list 16 => undef, # This task was removed from another task's related list 17 => undef, # Reminder added 18 => undef, # Reminder deleted 19 => undef, # User took ownership 20 => undef, # Closure request made 21 => undef, # Re-opening request made 22 => undef, # Adding a new dependency 23 => undef, # This task added as a dependency of another task 24 => undef, # Removing a dependency 25 => undef, # This task removed from another task's dependency list 26 => undef, # Task was made private 27 => undef, # Task was made public 28 => undef, # PM request denied ); my %accesslevels = ( 'Admin' => 90, 'Developers' => 55, 'Reporters' => 25 ); #################*UGLY CODE BELOW, BEWARE!*##################### use Term::ProgressBar 2.00; use DateTime::Format::MySQL; use DateTime; use DBI; use Getopt::Long; use File::Slurp; use File::Spec; my $VERSION = 1.0; my $cur_step = 0; my $max_step = 11; my $verbose = 0; my %projects; my %categoryNames; my %categorylinks; my %versionlinks; my %userbugs; my %filelinks; my %commentlinks; GetOptions( "verbose!" => \$verbose ); my( $local_max_step, $local_cur_step ); my $progress = Term::ProgressBar->new( { name => 'Progress', count => $max_step*100, ETA => 'linear', } ); my $olddbh = DBI->connect( $olddsn, $olddbuser, $olddbpass); my $newdbh = DBI->connect( $newdsn, $newdbuser, $newdbpass); $newdbh->{mysql_enable_utf8} = 1; $newdbh->do("SET NAMES 'utf8'"); # Truncating { $progress->message("Truncating tables"); $cur_step++; $newdbh->do("TRUNCATE mantis_user_table"); $progress->message("Truncating table mantis_user_table") if $verbose; $newdbh->do("TRUNCATE mantis_project_table"); $progress->message("Truncating table mantis_project_table") if $verbose; $newdbh->do("TRUNCATE mantis_category_table"); $progress->message("Truncating table mantis_category_table") if $verbose; $newdbh->do("TRUNCATE mantis_project_user_list_table"); $progress->message("Truncating table mantis_project_user_list_table") if $verbose; $newdbh->do("TRUNCATE mantis_project_version_table"); $progress->message("Truncating table mantis_project_version_table") if $verbose; $newdbh->do("TRUNCATE mantis_bug_text_table"); $progress->message("Truncating table mantis_bug_text_table") if $verbose; $newdbh->do("TRUNCATE mantis_bug_table"); $progress->message("Truncating table mantis_bug_table") if $verbose; $newdbh->do("TRUNCATE mantis_bugnote_text_table"); $progress->message("Truncating table mantis_bugnote_text_table") if $verbose; $newdbh->do("TRUNCATE mantis_bugnote_table"); $progress->message("Truncating table mantis_bugnote_table") if $verbose; $newdbh->do("TRUNCATE mantis_bug_relationship_table"); $progress->message("Truncating table mantis_bug_relationship_table") if $verbose; $newdbh->do("TRUNCATE mantis_bug_monitor_table"); $progress->message("Truncating table mantis_bug_monitor_table") if $verbose; $newdbh->do("TRUNCATE mantis_bug_file_table"); $progress->message("Truncating table mantis_bug_file_table") if $verbose; $newdbh->do("TRUNCATE mantis_bug_history_table"); $progress->message("Truncating table mantis_bug_history_table") if $verbose; } # Users { $progress->message("Importing users"); $cur_step++; my $flyspray_users = $olddbh->prepare(" SELECT DISTINCT flyspray_users.user_id, flyspray_groups.group_name, flyspray_history.event_date AS date, user_name, real_name, user_pass, email_address, account_enabled FROM flyspray_users LEFT JOIN flyspray_users_in_groups ON flyspray_users.user_id = flyspray_users_in_groups.user_id LEFT JOIN flyspray_groups ON flyspray_groups.group_id = flyspray_users_in_groups.group_id LEFT JOIN flyspray_history ON flyspray_users.user_id = flyspray_history.user_id GROUP BY flyspray_users.user_id "); my $mantis_user_table = $newdbh->prepare(" INSERT INTO mantis_user_table ( id, username, realname, email, password, enabled, access_level, cookie_string, date_created ) VALUES (?,?,?,?,?,?,?,?,?) "); $flyspray_users->execute(); $local_max_step = $flyspray_users->rows; $local_cur_step = 0; my $cookieCounter = 1; foreach my $entry ( @{$flyspray_users->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing user %s", $entry->{user_name}) if $verbose; $mantis_user_table->execute( $entry->{user_id}, $entry->{user_name}, $entry->{real_name}, $entry->{email_address}, $entry->{user_pass}, $entry->{account_enabled}, $accesslevels{$entry->{group_name}} // 10, 'cookiestring'.++$cookieCounter, $entry->{date} ? DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date})) : 0 ); } $progress->update($cur_step*100); } # Projects { $progress->message("Importing projects"); $cur_step++; my $flyspray_projects = $olddbh->prepare(" SELECT project_id, project_title, project_is_active, intro_message, anon_open FROM flyspray_projects "); my $mantis_project_table = $newdbh->prepare(" INSERT INTO mantis_project_table ( id, name, enabled, description, view_state ) VALUES(?,?,?,?,?) "); $flyspray_projects->execute(); $local_max_step = $flyspray_projects->rows; $local_cur_step = 0; foreach my $entry ( @{$flyspray_projects->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing project %s", $entry->{project_title}) if $verbose; $mantis_project_table->execute( $entry->{project_id}, $entry->{project_title}, $entry->{project_is_active}, $entry->{intro_message}, $entry->{anon_open} == 1 ? 10 : 50 ); $projects{$entry->{project_id}} = $entry->{project_title}; } $progress->update($cur_step*100); } # Categories { $progress->message("Importing categories"); $cur_step++; my $flyspray_list_category = $olddbh->prepare(" SELECT category_id, category_name, project_id, category_owner FROM flyspray_list_category "); my $mantis_category_table = $newdbh->prepare(" INSERT INTO mantis_category_table ( project_id, name, user_id ) VALUES(?,?,?) "); my $mantis_project_user_list_table = $newdbh->prepare(" INSERT IGNORE INTO mantis_project_user_list_table ( project_id, user_id, access_level ) VALUES(?,?,?) "); $flyspray_list_category->execute(); $local_max_step = $flyspray_list_category->rows; $local_cur_step = 0; my $lastProduct = -1; foreach my $entry ( @{$flyspray_list_category->fetchall_arrayref({})} ) { if( $lastProduct != $entry->{project_id} ) { %categoryNames = (); } ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing category '%s' for project '%s'", $entry->{category_name}, $projects{$entry->{project_id}}) if $verbose; $mantis_category_table->execute( $entry->{project_id}, exists $categoryNames{$entry->{category_name}} ? "$entry->{category_name} $entry->{category_id}" : $entry->{category_name}, $entry->{category_owner} ); $categorylinks{$entry->{category_id}} = $newdbh->last_insert_id(undef,undef,undef,undef); $mantis_project_user_list_table->execute( $entry->{project_id}, $entry->{category_owner}, 55 ); $categoryNames{$entry->{category_name}} = 1; $lastProduct = $entry->{project_id}; } $progress->update($cur_step*100); } # Versions { $progress->message("Importing versions"); $cur_step++; my $flyspray_list_version = $olddbh->prepare(" SELECT version_id, project_id, version_name, version_tense FROM flyspray_list_version "); my $mantis_project_version_table = $newdbh->prepare(" INSERT IGNORE INTO mantis_project_version_table ( project_id, version, released, obsolete ) VALUES(?,?,?,?) "); $flyspray_list_version->execute(); $local_max_step = $flyspray_list_version->rows; $local_cur_step = 0; foreach my $entry ( @{$flyspray_list_version->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing version '%s' for project '%s'", $entry->{version_name}, $projects{$entry->{project_id}}) if $verbose; $mantis_project_version_table->execute( $entry->{project_id}, $entry->{version_name}, $entry->{version_tense} == 3 ? 1 : 0, $entry->{version_tense} == 1 ? 1 : 0, ); $versionlinks{$entry->{version_id}} = $entry->{version_name}; } $progress->update($cur_step*100); } # Bugs { $progress->message("Importing bugs"); $cur_step++; my $flyspray_tasks = $olddbh->prepare(" SELECT task_id, attached_to_project, date_opened, opened_by, is_closed, date_closed, closed_by, item_summary, detailed_desc, item_status, assigned_to, resolution_reason, product_category, product_version, closedby_version, operating_system, task_severity, task_priority, last_edited_by, last_edited_time, percent_complete, mark_private, due_date FROM flyspray_tasks "); my $mantis_bug_text_table = $newdbh->prepare(" INSERT INTO mantis_bug_text_table ( description, steps_to_reproduce, additional_information ) VALUES(?,?,?) "); my $mantis_bug_table = $newdbh->prepare(" INSERT INTO mantis_bug_table ( id, project_id, reporter_id, handler_id, priority, severity, status, resolution, category_id, date_submitted, last_updated, bug_text_id, version, fixed_in_version, summary, target_version ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) "); $flyspray_tasks->execute(); $local_max_step = $flyspray_tasks->rows; $local_cur_step = 0; foreach my $entry ( @{$flyspray_tasks->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing bug '%d' for project '%s'", $entry->{task_id}, $projects{$entry->{attached_to_project}}) if $verbose; $mantis_bug_text_table->execute( $entry->{detailed_desc}, '', '' ); $userbugs{$entry->{task_id}} = {}; my $bug_text_id = $newdbh->last_insert_id(undef,undef,undef,undef); if( defined $entry->{opened_by} ) { $userbugs{$entry->{task_id}}{$entry->{opened_by}} = 1; } if( defined $entry->{assigned_to} ) { $userbugs{$entry->{task_id}}{$entry->{assigned_to}} = 1; } $mantis_bug_table->execute( $entry->{task_id}, $entry->{attached_to_project}, $entry->{opened_by}, $entry->{assigned_to}, $conv_priority{$entry->{task_priority}}, $conv_severity{$entry->{task_severity}}, $entry->{is_closed} ? 90 : $entry->{resolution_reason} == 8 ? 80 : $conv_status{$entry->{item_status}}, $entry->{resolution_reason} ? $conv_resolution{$entry->{resolution_reason}} : 10, $categorylinks{$entry->{product_category}}, DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_opened})), DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{last_edited_time})), $bug_text_id, $versionlinks{$entry->{product_version}}, $entry->{closedby_version} && $entry->{is_closed} ? $versionlinks{$entry->{closedby_version}} : '', $entry->{item_summary}, $entry->{closedby_version} ? $versionlinks{$entry->{closedby_version}} : '', ); } $progress->update($cur_step*100); } # Bug notes { $progress->message("Importing bug notes"); $cur_step++; my $flyspray_comments = $olddbh->prepare(" SELECT comment_id, task_id, date_added, user_id, comment_text FROM flyspray_comments "); my $mantis_bugnote_text_table = $newdbh->prepare(" INSERT INTO mantis_bugnote_text_table ( note ) VALUES(?) "); my $mantis_bugnote_table = $newdbh->prepare(" INSERT INTO mantis_bugnote_table ( bug_id, reporter_id, bugnote_text_id, date_submitted, last_modified ) VALUES(?,?,?,?,?) "); $flyspray_comments->execute(); $local_max_step = $flyspray_comments->rows; $local_cur_step = 0; foreach my $entry ( @{$flyspray_comments->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing bugnote '%d' for bug '%s'", $entry->{comment_id}, $entry->{task_id}) if $verbose; $mantis_bugnote_text_table->execute( $entry->{comment_text}, ); my $bugnote_text_id = $newdbh->last_insert_id(undef,undef,undef,undef); if( defined $entry->{user_id} ) { $userbugs{$entry->{task_id}}{$entry->{user_id}} = 1; } $mantis_bugnote_table->execute( $entry->{task_id}, $entry->{user_id}, $bugnote_text_id, DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_added})), DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_added})), ); my $bugnote_id = $newdbh->last_insert_id(undef,undef,undef,undef); $commentlinks{$entry->{comment_id}} = sprintf "%07d", $bugnote_id; } $progress->update($cur_step*100); } # Relations { $progress->message("Importing relations"); $cur_step++; my $flyspray_dependencies = $olddbh->prepare(" SELECT task_id, dep_task_id FROM flyspray_dependencies "); my $flyspray_related = $olddbh->prepare(" SELECT this_task, related_task FROM flyspray_related "); my $mantis_bug_relationship_table = $newdbh->prepare(" INSERT INTO mantis_bug_relationship_table ( source_bug_id, destination_bug_id, relationship_type ) VALUES(?,?,?)"); $flyspray_dependencies->execute(); $flyspray_related->execute(); $local_max_step = $flyspray_dependencies->rows + $flyspray_related->rows; $local_cur_step = 0; foreach my $entry ( @{$flyspray_dependencies->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing dependency from bug %d to bug %d", $entry->{dep_task_id}, $entry->{task_id}) if $verbose; $mantis_bug_relationship_table->execute( $entry->{dep_task_id}, $entry->{task_id}, 2, ); } foreach my $entry ( @{$flyspray_related->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing relation from bug %d to bug %d", $entry->{this_task}, $entry->{related_task}) if $verbose; $mantis_bug_relationship_table->execute( $entry->{this_task}, $entry->{related_task}, 1, ); } $progress->update($cur_step*100); } # Monitoring { $progress->message("Importing monitors"); $cur_step++; my $flyspray_notifications = $olddbh->prepare(" SELECT task_id, user_id FROM flyspray_notifications "); my $mantis_bug_monitor_table = $newdbh->prepare(" INSERT INTO mantis_bug_monitor_table ( bug_id, user_id ) VALUES(?,?)"); $flyspray_notifications->execute(); $local_max_step = $flyspray_notifications->rows; $local_cur_step = 0; foreach my $entry ( @{$flyspray_notifications->fetchall_arrayref({})} ) { next if exists $userbugs{$entry->{task_id}}{$entry->{user_id}}; # Skip implicit monitoring ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); $progress->message(sprintf "Importing monitoring for bug %d by user %d", $entry->{task_id}, $entry->{user_id}) if $verbose; $mantis_bug_monitor_table->execute( $entry->{task_id}, $entry->{user_id}, ); } $progress->update($cur_step*100); } # Files { $progress->message("Importing files"); $cur_step++; my $flyspray_attachments = $olddbh->prepare(" SELECT attachment_id,task_id, comment_id, orig_name, file_name, file_desc, file_type, file_size, added_by, date_added FROM flyspray_attachments "); my $mantis_bug_file_table = $newdbh->prepare(" INSERT INTO mantis_bug_file_table ( bug_id, title, description, diskfile, filename, folder, filesize, file_type, date_added, content ) VALUES(?,?,?,?,?,?,?,?,?,?) "); $flyspray_attachments->execute(); $local_max_step = $flyspray_attachments->rows; $local_cur_step = 0; foreach my $entry ( @{$flyspray_attachments->fetchall_arrayref({})} ) { ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); my $file = File::Spec->catfile($attachment_dir, $entry->{file_name}); next unless -f $file; my $content = read_file( $file ); $progress->message(sprintf "Importing file %d", $entry->{orig_name}) if $verbose; $mantis_bug_file_table->execute( $entry->{task_id}, $entry->{orig_name}, $entry->{file_desc}, '', $entry->{orig_name}, '', $entry->{file_size}, $entry->{file_type}, DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{date_added})), $content, ); $filelinks{$entry->{attachment_id}} = $entry->{orig_name}; } $progress->update($cur_step*100); } # History { $progress->message("Importing history"); $cur_step++; my $flyspray_history = $olddbh->prepare(" SELECT task_id, user_id, event_date, event_type, old_value, new_value, field_changed FROM flyspray_history "); my $mantis_bug_history_table = $newdbh->prepare(" INSERT INTO mantis_bug_history_table ( user_id, bug_id, date_modified, field_name, old_value, new_value, type ) VALUES(?,?,?,?,?,?,?) "); $flyspray_history->execute(); $local_max_step = $flyspray_history->rows; $local_cur_step = 0; my $last_status = 10; foreach my $entry ( @{$flyspray_history->fetchall_arrayref({})} ) { my $type = $conv_history{$entry->{event_type}}; ++$local_cur_step; $progress->update(($cur_step - 1 + ($local_cur_step / $local_max_step) )*100 ); if( defined $type ) { my $old_value = $entry->{old_value}; my $new_value = $entry->{new_value}; my $field_name = $entry->{field_changed}; if( $entry->{event_type} == 4 ) { $old_value = $commentlinks{$new_value}; next unless $old_value; $new_value = ''; } if( $entry->{event_type} == 9 ) { $old_value = $new_value; $new_value = ''; next if exists $userbugs{$entry->{task_id}}{$old_value}; # skipping implicit history monitor additions } if( $entry->{event_type} == 7 ) { $old_value = $filelinks{$new_value}; next unless $old_value; $new_value = ''; } if( $entry->{event_type} == 2 ) { $field_name = 'status'; $old_value = $last_status; $new_value = 90; } if( $entry->{event_type} == 0 ) { if( $entry->{field_changed} eq 'task_severity' ) { $field_name = 'severity'; $old_value = $conv_severity{$old_value}; $new_value = $conv_severity{$new_value}; } elsif( $entry->{field_changed} eq 'item_status' ) { $field_name = 'status'; $old_value = $conv_status{$old_value}; $new_value = $conv_status{$new_value}; $last_status = $new_value; } elsif( $entry->{field_changed} eq 'percent_complete' ) { next; } } $progress->message(sprintf "Importing history for bug %d and user %d", $entry->{task_id}, $entry->{user_id}) if $verbose; $mantis_bug_history_table->execute( $entry->{user_id}, $entry->{task_id}, DateTime::Format::MySQL->format_datetime(DateTime->from_epoch(epoch => $entry->{event_date})), $field_name, $old_value, $new_value, $type, ); } } $progress->update($cur_step*100); } | ||||
Cleaned up the code and added an BSD license, designated a version 1.0 for reference, the previous one can be removed. |
|
Thanks for your contribution. I assigned the issue to myself, to add a link to it from somewhere on our website. |
|
Added a link form the FAQ in the wiki: I'll leave the issue open to allow @azatoth or others to add further updates as necessary. |
|
The script mantisconvert-1.0.pl may work for Mantis 1.2.0a3 and FlySpray 0.9.8, but for Mantis 1.2.0rc1 and FlySpray 0.9.9.6 it did not work tue to some table changes in FlySpray and Mantis. I modified the Script so it process FlySpray 0.9.9.6 => Mantis 1.2.0rc1. Attention: |
|