public void Run()
{
using (MantisDatabase db = new MantisDatabase(Options))
{
TaskDataStore dataStore = new TaskDataStore();
foreach (var task in Tasks)
{
try
{
LogService.Info(task.Description + "...");
task.Run(db, Options, dataStore);
}
catch (TaskFailedException failedExc)
{
LogService.Error($"Task execution failed: {failedExc.Message}");
if (failedExc.ContinueExecution)
{
LogService.Info($"But execution can be continued");
}
else
{
throw new Exception("Unrecoverable error");
}
}
catch (Exception e)
{
throw new Exception("Unrecoverable error: " + e.Message);
}
}
}
}
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)
{
// 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)
{
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)
{
// 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)
{
db.DeleteAll("api tokens", MantisTable.api_token);
db.DeleteAll("tokens", MantisTable.tokens);
db.DeleteAll("emails", MantisTable.email);
db.DeleteAll("filters", MantisTable.filters);
db.DeleteAll("news", MantisTable.news);
db.DeleteAll("sponsorship entries", MantisTable.sponsorship);
}
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)
{
// 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 abstract void ExecuteCommand(MantisDatabase db, DbCommand cmd, CleanOptions options, TaskDataStore store);
public void Run(MantisDatabase db, CleanOptions options, TaskDataStore store)
{
ExecuteCommand(db, db.CreateCommand(), options, store);
}
