diff options
Diffstat (limited to 'scripts/remove-non-public-data.pl')
| -rwxr-xr-x | scripts/remove-non-public-data.pl | 364 |
1 files changed, 212 insertions, 152 deletions
diff --git a/scripts/remove-non-public-data.pl b/scripts/remove-non-public-data.pl index 47c123464..265734d65 100755 --- a/scripts/remove-non-public-data.pl +++ b/scripts/remove-non-public-data.pl @@ -13,10 +13,11 @@ use warnings; use File::Basename qw(dirname); use File::Spec::Functions qw(catfile catdir rel2abs); use Cwd qw(realpath); + BEGIN { - require lib; - my $dir = rel2abs(catdir(dirname(__FILE__), '..')); - lib->import($dir, catdir($dir, 'lib'), catdir($dir, qw(local lib perl5))); + require lib; + my $dir = rel2abs(catdir(dirname(__FILE__), '..')); + lib->import($dir, catdir($dir, 'lib'), catdir($dir, qw(local lib perl5))); } use autodie; @@ -29,94 +30,146 @@ Bugzilla->usage_mode(USAGE_MODE_CMDLINE); # database. my %whitelist = ( - attachments => [qw( - attach_id bug_id creation_ts modification_time description - mimetype ispatch filename submitter_id isobsolete attach_size - )], - bug_mentors => [qw( - bug_id user_id - )], - bug_see_also => [qw( - id bug_id value - )], - bugs => [qw( - bug_id assigned_to bug_file_loc bug_severity bug_status - creation_ts delta_ts short_desc op_sys priority product_id - rep_platform reporter version component_id resolution - target_milestone qa_contact status_whiteboard everconfirmed - estimated_time remaining_time deadline alias cf_rank - cf_crash_signature cf_last_resolved cf_user_story votes - )], - bugs_activity => [qw( - id bug_id attach_id who bug_when fieldid added removed - comment_id - )], - cc => [qw( - bug_id who - )], - classifications => [qw( - id name description sortkey - )], - components => [qw( - id name product_id description isactive - )], - dependencies => [qw( - blocked dependson - )], - duplicates => [qw( - dupe_of dupe - )], - fielddefs => [qw( - id name type custom description obsolete - )], - flag_state_activity => [qw( - id flag_when type_id flag_id setter_id requestee_id bug_id - attachment_id status - )], - flags => [qw( - id type_id status bug_id attach_id creation_date - modification_date setter_id requestee_id - )], - flagtypes => [qw( - id name description target_type is_active - )], - keyworddefs => [qw( - id name description is_active - )], - keywords => [qw( - bug_id keywordid - )], - longdescs => [qw( - comment_id bug_id who bug_when work_time thetext type - extra_data - )], - longdescs_tags => [qw( - id comment_id tag - )], - longdescs_tags_activity => [qw( - id bug_id comment_id who bug_when added removed - )], - milestones => [qw( - id product_id value sortkey isactive - )], - products => [qw( - id name classification_id description isactive defaultmilestone - )], - profiles => [qw( - userid login_name realname is_enabled creation_ts - )], - tracking_flags => [qw( - id field_id name description type sortkey is_active - )], - tracking_flags_bugs => [qw( - id tracking_flag_id bug_id value - )], - tracking_flags_values => [qw( - id tracking_flag_id setter_group_id value sortkey is_active - )], - versions => [qw( - id value product_id isactive - )], + attachments => [ + qw( + attach_id bug_id creation_ts modification_time description + mimetype ispatch filename submitter_id isobsolete attach_size + ) + ], + bug_mentors => [ + qw( + bug_id user_id + ) + ], + bug_see_also => [ + qw( + id bug_id value + ) + ], + bugs => [ + qw( + bug_id assigned_to bug_file_loc bug_severity bug_status + creation_ts delta_ts short_desc op_sys priority product_id + rep_platform reporter version component_id resolution + target_milestone qa_contact status_whiteboard everconfirmed + estimated_time remaining_time deadline alias cf_rank + cf_crash_signature cf_last_resolved cf_user_story votes + ) + ], + bugs_activity => [ + qw( + id bug_id attach_id who bug_when fieldid added removed + comment_id + ) + ], + cc => [ + qw( + bug_id who + ) + ], + classifications => [ + qw( + id name description sortkey + ) + ], + components => [ + qw( + id name product_id description isactive + ) + ], + dependencies => [ + qw( + blocked dependson + ) + ], + duplicates => [ + qw( + dupe_of dupe + ) + ], + fielddefs => [ + qw( + id name type custom description obsolete + ) + ], + flag_state_activity => [ + qw( + id flag_when type_id flag_id setter_id requestee_id bug_id + attachment_id status + ) + ], + flags => [ + qw( + id type_id status bug_id attach_id creation_date + modification_date setter_id requestee_id + ) + ], + flagtypes => [ + qw( + id name description target_type is_active + ) + ], + keyworddefs => [ + qw( + id name description is_active + ) + ], + keywords => [ + qw( + bug_id keywordid + ) + ], + longdescs => [ + qw( + comment_id bug_id who bug_when work_time thetext type + extra_data + ) + ], + longdescs_tags => [ + qw( + id comment_id tag + ) + ], + longdescs_tags_activity => [ + qw( + id bug_id comment_id who bug_when added removed + ) + ], + milestones => [ + qw( + id product_id value sortkey isactive + ) + ], + products => [ + qw( + id name classification_id description isactive defaultmilestone + ) + ], + profiles => [ + qw( + userid login_name realname is_enabled creation_ts + ) + ], + tracking_flags => [ + qw( + id field_id name description type sortkey is_active + ) + ], + tracking_flags_bugs => [ + qw( + id tracking_flag_id bug_id value + ) + ], + tracking_flags_values => [ + qw( + id tracking_flag_id setter_group_id value sortkey is_active + ) + ], + versions => [ + qw( + id value product_id isactive + ) + ], ); # @@ -143,39 +196,47 @@ my $sanitizeme = catfile(realpath(dirname(__FILE__)), 'sanitizeme.pl'); system $sanitizeme, '--execute'; if ($dbh->selectrow_array("SELECT COUNT(*) FROM bug_group_map")) { - die "sanitization failed\n"; + die "sanitization failed\n"; } # drop all views -foreach my $view (sort @{ $dbh->selectcol_arrayref("SHOW FULL TABLES IN $db_name WHERE TABLE_TYPE LIKE 'VIEW'") }) { - print "dropping view $view\n"; - $dbh->do("DROP VIEW $view"); +foreach my $view ( + sort @{ + $dbh->selectcol_arrayref( + "SHOW FULL TABLES IN $db_name WHERE TABLE_TYPE LIKE 'VIEW'") + } + ) +{ + print "dropping view $view\n"; + $dbh->do("DROP VIEW $view"); } # drop tables/columns -my @tables = sort @{ $dbh->selectcol_arrayref("SHOW TABLES") }; +my @tables = sort @{$dbh->selectcol_arrayref("SHOW TABLES")}; foreach my $table (@tables) { - if (exists $whitelist{$table}) { - my @drop_columns; - foreach my $column (map { $_->{Field} } @{ $dbh->selectall_arrayref("DESCRIBE $table", { Slice => {} }) }) { - unless (any { $_ eq $column } @{ $whitelist{$table} }) { - print "dropping references to $table.$column\n"; - drop_referencing($table, $column); - push @drop_columns, "DROP COLUMN $column"; - } - } - if (@drop_columns) { - print "dropping columns from $table\n"; - $dbh->do("ALTER TABLE $table " . join(", ", @drop_columns)); - } + if (exists $whitelist{$table}) { + my @drop_columns; + foreach my $column (map { $_->{Field} } + @{$dbh->selectall_arrayref("DESCRIBE $table", {Slice => {}})}) + { + unless (any { $_ eq $column } @{$whitelist{$table}}) { + print "dropping references to $table.$column\n"; + drop_referencing($table, $column); + push @drop_columns, "DROP COLUMN $column"; + } } - else { - print "dropping $table\n"; - drop_referencing($table); - $dbh->do("DROP TABLE IF EXISTS $table"); + if (@drop_columns) { + print "dropping columns from $table\n"; + $dbh->do("ALTER TABLE $table " . join(", ", @drop_columns)); } + } + else { + print "dropping $table\n"; + drop_referencing($table); + $dbh->do("DROP TABLE IF EXISTS $table"); + } } # remove users with no activity @@ -198,56 +259,55 @@ $dbh->do(" "); sub drop_referencing { - my ($table, $column) = @_; - my ($sql, @values); + my ($table, $column) = @_; + my ($sql, @values); - # drop foreign keys that reference this table/column - $sql = " + # drop foreign keys that reference this table/column + $sql = " SELECT DISTINCT TABLE_NAME 'table', CONSTRAINT_NAME name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = ? AND REFERENCED_TABLE_NAME = ? "; - @values = ($db_name, $table); - if ($column) { - $sql .= " AND REFERENCED_COLUMN_NAME = ?"; - push @values, $column; - } - foreach my $fk (@{ $dbh->selectall_arrayref($sql, { Slice => {} }, @values) }) { - print " dropping fk $fk->{table}.$fk->{name}\n"; - $dbh->do("ALTER TABLE $fk->{table} DROP FOREIGN KEY $fk->{name}"); - } + @values = ($db_name, $table); + if ($column) { + $sql .= " AND REFERENCED_COLUMN_NAME = ?"; + push @values, $column; + } + foreach my $fk (@{$dbh->selectall_arrayref($sql, {Slice => {}}, @values)}) { + print " dropping fk $fk->{table}.$fk->{name}\n"; + $dbh->do("ALTER TABLE $fk->{table} DROP FOREIGN KEY $fk->{name}"); + } - # drop indexes - if ($column) { - # drop associated fk/index - $sql = " + # drop indexes + if ($column) { + + # drop associated fk/index + $sql = " SELECT DISTINCT TABLE_NAME 'table', CONSTRAINT_NAME name, REFERENCED_TABLE_NAME ref FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ? "; - @values = ($db_name, $table, $column); - foreach my $fk (@{ $dbh->selectall_arrayref($sql, { Slice => {} }, @values) }) { - if ($fk->{ref}) { - print " dropping fk $fk->{table}.$fk->{name}\n"; - $dbh->do("ALTER TABLE $fk->{table} DROP FOREIGN KEY $fk->{name}"); - } - else { - print " dropping index $fk->{table}.$fk->{name}\n"; - $dbh->do("ALTER TABLE $fk->{table} DROP INDEX $fk->{name}"); - } - } - - # drop the index - my $rows = $dbh->selectall_arrayref( - "SHOW INDEX FROM $table WHERE Column_name = ?", - { Slice => {} }, - $column - ); - foreach my $fk (@$rows) { - print " dropping index $fk->{Table}.$fk->{Key_name}\n"; - $dbh->do("ALTER TABLE $fk->{Table} DROP INDEX $fk->{Key_name}"); - } + @values = ($db_name, $table, $column); + foreach my $fk (@{$dbh->selectall_arrayref($sql, {Slice => {}}, @values)}) { + if ($fk->{ref}) { + print " dropping fk $fk->{table}.$fk->{name}\n"; + $dbh->do("ALTER TABLE $fk->{table} DROP FOREIGN KEY $fk->{name}"); + } + else { + print " dropping index $fk->{table}.$fk->{name}\n"; + $dbh->do("ALTER TABLE $fk->{table} DROP INDEX $fk->{name}"); + } + } + + # drop the index + my $rows + = $dbh->selectall_arrayref("SHOW INDEX FROM $table WHERE Column_name = ?", + {Slice => {}}, $column); + foreach my $fk (@$rows) { + print " dropping index $fk->{Table}.$fk->{Key_name}\n"; + $dbh->do("ALTER TABLE $fk->{Table} DROP INDEX $fk->{Key_name}"); } + } } |