summaryrefslogtreecommitdiffstats
path: root/scripts/remove-non-public-data.pl
diff options
context:
space:
mode:
Diffstat (limited to 'scripts/remove-non-public-data.pl')
-rwxr-xr-xscripts/remove-non-public-data.pl243
1 files changed, 243 insertions, 0 deletions
diff --git a/scripts/remove-non-public-data.pl b/scripts/remove-non-public-data.pl
new file mode 100755
index 000000000..06fc693fa
--- /dev/null
+++ b/scripts/remove-non-public-data.pl
@@ -0,0 +1,243 @@
+#!/usr/bin/perl -w
+# 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.
+
+use strict;
+
+use FindBin qw($RealBin);
+use lib "$RealBin/..", "$RealBin/../lib";
+
+use Bugzilla;
+use Bugzilla::Constants;
+use List::MoreUtils qw(any);
+Bugzilla->usage_mode(USAGE_MODE_CMDLINE);
+
+# tables/columns not listed in this whitelist will be dropped from the
+# 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
+ )],
+);
+
+#
+
+my $db_name = Bugzilla->localconfig->{db_name};
+print <<EOF;
+WARNING
+
+This will delete all non public information from the database '$db_name'.
+
+This database will not function as a Bugzilla database once this script has
+completed.
+
+Press <Return> to continue, or Ctrl+C to cancel..
+EOF
+getc();
+
+my $dbh = Bugzilla->dbh;
+
+# run sanitiseme.pl
+
+print "running sanitizeme.pl\n";
+system "'$RealBin/sanitizeme.pl' --execute";
+
+if ($dbh->selectrow_array("SELECT COUNT(*) FROM bug_group_map")) {
+ 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");
+}
+
+# drop tables/columns
+
+my @tables = map { lc } 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));
+ }
+ }
+ else {
+ print "dropping $table\n";
+ drop_referencing($table);
+ $dbh->do("DROP TABLE $table");
+ }
+}
+
+# remove users with no activity
+
+print "deleting users with no activity\n";
+$dbh->do("
+ DELETE FROM profiles
+ WHERE (SELECT COUNT(*) FROM bugs_activity WHERE bugs_activity.who = profiles.userid) = 0
+ AND (SELECT COUNT(*) FROM bugs WHERE bugs.reporter = profiles.userid) = 0
+ AND (SELECT COUNT(*) FROM bugs WHERE bugs.assigned_to = profiles.userid) = 0
+ AND (SELECT COUNT(*) FROM bugs WHERE bugs.qa_contact = profiles.userid) = 0
+ AND (SELECT COUNT(*) FROM bugs WHERE bugs.qa_contact = profiles.userid) = 0
+ AND (SELECT COUNT(*) FROM longdescs WHERE longdescs.who = profiles.userid) = 0
+ AND (SELECT COUNT(*) FROM longdescs_tags_activity WHERE longdescs_tags_activity.who = profiles.userid) = 0
+ AND (SELECT COUNT(*) FROM attachments WHERE attachments.submitter_id = profiles.userid) = 0
+ AND (SELECT COUNT(*) FROM flags WHERE flags.setter_id = profiles.userid) = 0
+ AND (SELECT COUNT(*) FROM flags WHERE flags.requestee_id = profiles.userid) = 0
+ AND (SELECT COUNT(*) FROM flag_state_activity WHERE flag_state_activity.setter_id = profiles.userid) = 0
+ AND (SELECT COUNT(*) FROM flag_state_activity WHERE flag_state_activity.requestee_id = profiles.userid) = 0
+");
+
+sub drop_referencing {
+ my ($table, $column) = @_;
+ my ($sql, @values);
+
+ # 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}");
+ }
+
+ # 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}");
+ }
+ }
+
+}
+