protected override void ExecuteCommand(MantisDatabase db, DbCommand cmd, CleanOptions options, TaskDataStore store) { List <int> projectids = store.Get <List <int> >(DataKey.KeepProjectIDs); db.DeleteMatching("projects", $"DELETE FROM {db.Table(MantisTable.project)} WHERE id NOT IN @@ids", projectids); db.DeleteMatching("project files", $"DELETE FROM {db.Table(MantisTable.project_file)} WHERE project_id NOT IN @@ids", projectids); db.DeleteMatching("project hierarchy entries", $"DELETE FROM {db.Table(MantisTable.project_hierarchy)} WHERE parent_id NOT IN @@ids OR child_id NOT IN @@ids", projectids); db.DeleteMatching("project user assignments", $"DELETE FROM {db.Table(MantisTable.project_user_list)} WHERE project_id NOT IN @@ids", projectids); db.DeleteMatching("project versions", $"DELETE FROM {db.Table(MantisTable.project_version)} WHERE project_id NOT IN @@ids", projectids); }
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); }
/// <summary> /// Removes all rows form the given table /// </summary> public static void DeleteAll(this MantisDatabase db, string what, MantisTable table) { using (var cmd = db.CreateCommand()) { cmd.CommandText = $"DELETE FROM {db.Table(table)}"; int count = cmd.ExecuteNonQuery(); LogService.Info($"Deleted {count} {what}"); } }
public void Run(MantisDatabase db, CleanOptions options, TaskDataStore store) { try { using (var cmd = db.CreateCommand()) { cmd.CommandText = $"SELECT value FROM {db.Table(MantisTable.config)} WHERE config_id = 'database_version'"; var vr = cmd.ExecuteScalar(); LogService.Info("Mantis database version is " + vr?.ToString() ?? ""); } } catch (Exception e) { throw new TaskFailedException(false, "Connection test failed: " + e.Message); } }
protected override void ExecuteCommand(MantisDatabase db, DbCommand cmd, CleanOptions options, TaskDataStore store) { // get the id of the project to keep cmd.CommandText = $"SELECT id, name FROM {db.Table(MantisTable.project)} WHERE name IN @@projectnames"; cmd.AddArrayParameters("@@projectnames", options.ProjectNamesToKeep); List <int> keepIds = new List <int>(); using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { keepIds.Add(dr.Get <int>("id")); LogService.Info($"Project '{dr.Get<String>("name")}' with id {dr.Get<int>("id")} will be kept"); } } if (options.ProjectNamesToKeep.Count() != keepIds.Count) { throw new TaskFailedException("Not all mentioned projects werde found in the database"); } store.Set(DataKey.KeepProjectIDs, keepIds); }
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); }
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); }