protected override void ExecuteCommand(MantisDatabase db, DbCommand cmd, CleanOptions options, TaskDataStore store)
{
List <int> projectids = db.ReadList <int>($"SELECT id FROM {db.Table(MantisTable.project)}");
// delete assignbment snon-existent projects
db.DeleteMatching("custom field project assignments", $"DELETE FROM {db.Table(MantisTable.custom_field_project)} WHERE project_id NOT IN @@ids", projectids);
// now delete all custom fields which do not have any assignments anymore
List <int> customfieldids = db.ReadList <int>($"SELECT DISTINCT field_id FROM {db.Table(MantisTable.custom_field_project)}");
db.DeleteMatching("custom fields", $"DELETE FROM {db.Table(MantisTable.custom_field)} WHERE id NOT IN @@ids", customfieldids);
db.DeleteMatching("custom field strings", $"DELETE FROM {db.Table(MantisTable.custom_field_string)} WHERE field_id NOT IN @@ids", customfieldids);
}
protected override void ExecuteCommand(MantisDatabase db, DbCommand cmd, CleanOptions options, TaskDataStore store)
{
// delete bugs which belong to non-existent projects
List <int> projectids = store.Get <List <int> >(DataKey.KeepProjectIDs);
db.DeleteMatching("bugs", $"DELETE FROM {db.Table(MantisTable.bug)} WHERE project_id NOT IN @@ids", projectids);
// we cannot use DELETE FROM WHERE [id] IN (SELECT **), see https://dba.stackexchange.com/questions/84805/delete-seems-to-hang
// or multiple-table DELETES because they always time out
// get the list of bug_ids beforehand and list them separately
// todo: if tehre are too many ids (command gets too long), we might need to delete in batches
// delete all bug_texts which are not used anymore
List <int> bugtextids = db.ReadList <int>($"SELECT bug_text_id FROM {db.Table(MantisTable.bug)}");
db.DeleteMatching("bug text entries", $"DELETE FROM {db.Table(MantisTable.bug_text)} WHERE id NOT IN @@ids", bugtextids);
// delete all bug_notes which reference non-existent bugs
List <int> bugids = db.ReadList <int>($"SELECT id FROM {db.Table(MantisTable.bug)}");
db.DeleteMatching("bug notes", $"DELETE FROM {db.Table(MantisTable.bugnote)} WHERE bug_id NOT IN @@ids", bugids);
// delete all bug_note_texts which reference non-existent bug notes
List <int> notetextids = db.ReadList <int>($"SELECT bugnote_text_id FROM {db.Table(MantisTable.bugnote)}");
db.DeleteMatching("bug note text entries", $"DELETE FROM {db.Table(MantisTable.bugnote_text)} WHERE id NOT IN @@ids", notetextids);
// delete all bug files which reference non-existent bugs (takes some time)
#if RELEASE
int oldTimeout = cmd.CommandTimeout;
cmd.CommandTimeout = (int)TimeSpan.FromSeconds(30).TotalMilliseconds;
db.DeleteMatching("bug attached files", $"DELETE FROM {db.Table(MantisTable.bug_file)} WHERE bug_id NOT IN @@ids", bugids);
cmd.CommandTimeout = oldTimeout;
#endif
// delete all bug history entries which reference non-existent bugs
db.DeleteMatching("bug history entries", $"DELETE FROM {db.Table(MantisTable.bug_history)} WHERE bug_id NOT IN @@ids", bugids);
// delete all bug monitor entries which reference non-existent bugs
db.DeleteMatching("bug monitor entries", $"DELETE FROM {db.Table(MantisTable.bug_monitor)} WHERE bug_id NOT IN @@ids", bugids);
// delete all bug relationships which reference non-existent bugs
db.DeleteMatching("bug relationships", $"DELETE FROM {db.Table(MantisTable.bug_relationship)} WHERE source_bug_id NOT IN @@ids OR destination_bug_id NOT IN @@ids", bugids);
// delete all bug revision entries which reference non-existent bugs
db.DeleteMatching("bug revision entries", $"DELETE FROM {db.Table(MantisTable.bug_revision)} WHERE bug_id NOT IN @@ids", bugids);
// delete all bug tags which reference non-existent bugs
db.DeleteMatching("bug tags", $"DELETE FROM {db.Table(MantisTable.bug_tag)} WHERE bug_id NOT IN @@ids", bugids);
}
protected override void ExecuteCommand(MantisDatabase db, DbCommand cmd, CleanOptions options, TaskDataStore store)
{
// delete all users which are not referenced anymore
List <int> userids = db.ReadList <int>($"SELECT DISTINCT reporter_id FROM {db.Table(MantisTable.bugnote)}");
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.bug_file)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.bug_history)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.bug_monitor)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.bug_revision)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT reporter_id FROM {db.Table(MantisTable.bug)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT handler_id FROM {db.Table(MantisTable.bug)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.bug_tag)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.category)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.config)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.filters)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT poster_id FROM {db.Table(MantisTable.news)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.project_file)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.project_user_list)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.sponsorship)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.tag)}"));
userids.Union(db.ReadList <int>($"SELECT DISTINCT user_id FROM {db.Table(MantisTable.user_profile)}"));
// remove all users which are not referenced anymore
db.DeleteMatching("users", $"DELETE FROM {db.Table(MantisTable.user)} WHERE id NOT IN @@ids", userids);
db.DeleteMatching("user profiles", $"DELETE FROM {db.Table(MantisTable.user_profile)} WHERE user_id NOT IN @@ids", userids);
// Remove confidential/personal user data
cmd.CommandText = $"UPDATE {db.Table(MantisTable.user)} SET email='redacted',password='******',login_count=0,lost_password_request_count=0,failed_login_count=0,cookie_string=CONCAT('redacted_', id),last_visit=0,date_created=0";
int count = cmd.ExecuteNonQuery();
LogService.Info($"Removed personal data from all remaining {count} user profiles");
// delete personal data
db.DeleteAll("user preferences", MantisTable.user_pref);
db.DeleteAll("user print preferences", MantisTable.user_print_pref);
}
protected override void ExecuteCommand(MantisDatabase db, DbCommand cmd, CleanOptions options, TaskDataStore store)
{
List <int> projectids = db.ReadList <int>($"SELECT id FROM {db.Table(MantisTable.project)}");
db.DeleteMatching("categories", $"DELETE FROM {db.Table(MantisTable.category)} WHERE project_id NOT IN @@ids", projectids);
}