Add "delete" option on History table

[mdietze]

There's a ton of old runs in the database, many of which have errors or are in unknown states and only a few of which are needed. Some of these runs are useful for sorting out errors, but they don't need to be permanently archived. It would be good to add a delete button on the History table so that it's easier to drop old workflows, ensembles, runs, and all the other table entries and files associated with a run. Right now this can't be done easily in BETY because a single workflow can touch a lot of tables, plus deleting database entries doesn't delete the underlying files themselves, but leaves them as 'orphans' on the server.

[robkooper]

See pull request #513

[robkooper]

Some code to remove large set of workflows:

dbparam <- c(user='bety', password='bety', host='localhost', dbname='bety',driver='PostgreSQL')
require(PEcAn.db)
require(RPostgreSQL)

remove.workflow <- function(workflowid, dbparam) {
  con <- db.open(dbparam)

  db.query(paste0("DELETE FROM inputs_runs USING ensembles, runs WHERE ensembles.workflow_id=", workflowid, " AND runs.ensemble_id=ensembles.id and inputs_runs.run_id=runs.id;"), con=con)
  db.query(paste0("DELETE FROM runs USING ensembles WHERE ensembles.workflow_id=", workflowid, " AND runs.ensemble_id=ensembles.id;"), con);
  db.query(paste0("DELETE FROM ensembles WHERE ensembles.workflow_id=", workflowid), con);
  db.query(paste0("DELETE FROM workflows WHERE id=", workflowid), con);

  db.close(con)
}

for(i in 106:172) { remove.workflow(i, dbparam) }