trash-report

#!/bin/bash

show_help()
{
  cat <<EOF

$ExecName version $Version

Usage:
 $ExecName [options]

Generates a report on the data objects in the trash that are older than 31 days
and their volume broken down by the storage resource holding the corresponding
files.

Options:
 -d, --debug      display progress and query time information
 -h, --help       display help text and exit
 -H, --host HOST  connect to the ICAT's DBMS on the host HOST instead of the
                  PostgreSQL default
 -p, --port PORT  connect to the ICAT's DBMS listening on TCP port PORT instead
                  of the PostgreSQL default
 -U, --user USER  authorize the DBMS connection as USER instead of the
                  PostgreSQL default
 -v, --version    display version and exit
EOF
}


readonly Version=1

set -o errexit -o nounset -o pipefail

readonly ExecName=$(basename "$0")

Debug=false


main()
{
  local opts
  opts=$(getopt --longoptions debug,help,host:,port:,user:,version \
                --name "$ExecName" \
                --options dhH:p:U:v \
                -- \
                "$@")
  local ret="$?"
  if [ "$ret" -ne 0 ]
  then
    show_help >&2
    return 1
  fi

  eval set -- "$opts"

  while true
  do
    case "$1" in
      -d|--debug)
      Debug=true;
      shift 1
      ;;
      -h|--help)
        show_help
        return 0
        ;;
      -H|--host)
        export PGHOST="$2"
        shift 2
        ;;
      -p|--port)
        export PGPORT="$2"
        shift 2
        ;;
      -U|--user)
        export PGUSER="$2"
        shift 2
        ;;
      -v|--version)
        show_version
        return 0
        ;;
      --)
        shift
        break
        ;;
      *)
        show_help >&2
        return 1
        ;;
    esac
  done

  local cutOff_s
  cutOff_s=$(date --date "$(date --iso-8601 --date '1 month ago')" '+%s')

  mk_trash_report "$cutOff_s" | psql --quiet ICAT
}


inject_debug_msg()
{
  local msg="$*"

  inject_debug_newline
  inject_debug_stmt \\echo \'"$msg"\'
}


inject_debug_newline()
{
  inject_debug_stmt \\echo \'\'
}


inject_debug_quiet()
{
  local state="$1"

  inject_debug_stmt \\set QUIET "$state"
}


inject_debug_stmt()
{
  local stmt="$*"

  if [ "$Debug" == true ]
  then
    printf '%s\n' "$stmt"
  fi
}


inject_set_title()
{
  local title="$*"

  inject_debug_quiet on
  printf "\\pset title '%s'\n" "$title"
  inject_debug_quiet off
}


mk_trash_report()
{
  local cutOff_s="$1"

  cat <<SQL
\\pset footer off
$(inject_debug_stmt \\timing on)
$(inject_debug_quiet off)

BEGIN;

$(inject_debug_msg Gathering trash collections)
CREATE TEMPORARY TABLE trash_collections (id, owner) AS
WITH RECURSIVE
  colls(id, name, owner) AS (
    SELECT c.coll_id, c.coll_name, u.user_name
    FROM r_coll_main AS c
      JOIN r_user_main AS u ON c.coll_name = '/iplant/trash/home/' || u.user_name
    UNION ALL
    SELECT c1.coll_id, c1.coll_name, c2.owner
    FROM r_coll_main AS c1 JOIN colls AS c2 ON c2.name = c1.parent_coll_name)
SELECT id, owner FROM colls;
CREATE INDEX idx_trash_collections ON trash_collections(id);

$(inject_debug_msg Gathering trash)
CREATE TEMPORARY TABLE trash (id, size, resource, owner, delete) AS
SELECT
  d.data_id,
  d.data_size,
  REGEXP_REPLACE(d.resc_hier, '^.*;', ''),
  c.owner,
  d.modify_ts <= '0$cutOff_s'
FROM r_data_main AS d JOIN trash_collections AS c ON c.id = d.coll_id;
CREATE INDEX idx_trash_owner ON trash(owner);
CREATE INDEX idx_trash_resource ON trash(resource);

$(inject_debug_msg Summarizing trash statistics by owner)
CREATE TEMPORARY TABLE trash_by_owner (owner, delete_count, delete_volume, count, volume) AS
SELECT
  owner,
  COUNT(NULLIF(delete, FALSE)),
  SUM(CASE WHEN delete THEN size ELSE 0 END),
  COUNT(*),
  SUM(size)
FROM trash
GROUP BY owner;
CREATE INDEX idx_trash_by_owner_delete_count ON trash_by_owner(delete_count);
CREATE INDEX idx_trash_by_owner_delete_volume ON trash_by_owner(delete_volume);

\\echo ''

$(inject_debug_newline)
$(inject_set_title Trash Summary)
SELECT
  SUM(delete_count)            AS "To Delete Count",
  SUM(delete_volume) / 2 ^ 40  AS "To Delete Volume (TiB)",
  SUM(count)                   AS "Trash Count",
  SUM(volume) / 2 ^ 40         AS "Trash Volume (TiB)"
FROM trash_by_owner;

$(inject_debug_newline)
$(inject_set_title Trash by Resource)
SELECT
  r.resc_name                                              AS "Storage Resource",
  COUNT(NULLIF(t.delete, FALSE))                           AS "To Delete Count",
  SUM(CASE WHEN t.delete THEN t.size ELSE 0 END) / 2 ^ 40  AS "To Delete Volume (TiB)",
  COUNT(t.id)                                              AS "Trash Count",
  SUM(COALESCE(t.size, 0)) / 2 ^ 40                        AS "Trash Volume (TiB)"
FROM r_resc_main AS r LEFT JOIN trash AS t ON t.resource = r.resc_name
WHERE r.resc_name != 'bundleResc' AND r.resc_type_name IN ('unixfilesystem', 'unix file system')
GROUP BY r.resc_name
ORDER BY r.resc_name;

$(inject_debug_newline)
$(inject_set_title Top 10 Users by Delete Count)
SELECT
  owner                   AS "User",
  delete_count            AS "To Delete Count",
  delete_volume / 2 ^ 30  AS "To Delete Volume (GiB)",
  count                   AS "Trash Count",
  volume / 2 ^ 30         AS "Trash Volume (GiB)"
FROM trash_by_owner
ORDER BY delete_count DESC
LIMIT 10;

$(inject_debug_newline)
$(inject_set_title Top 10 Users by Delete Volume)
SELECT
  owner                   AS "User",
  delete_count            AS "To Delete Count",
  delete_volume / 2 ^ 30  AS "To Delete Volume (GiB)",
  count                   AS "Trash Count",
  volume / 2 ^ 30         AS "Trash Volume (GiB)"
FROM trash_by_owner
ORDER BY delete_volume DESC
LIMIT 10;

$(inject_debug_newline)
$(inject_debug_newline)
ROLLBACK;
SQL
}


show_version()
{
  printf '%s\n' "$Version"
}


main "$@"