growth-report

#!/bin/bash

print_help()
{
  cat << EOF
Usage:
 $ExecName [options]

Generates a report showing how the data store has grown each month. For each
month, it displays the total number of data objects first created in that month
and the total volume of data contained in those files. Only data objects that
still exist in the data store are counted, i.e., if a data object where added
and then removed from the data store, it would not be counted. The report is
written to stdout in CSV format.

Options:
 -f, --first-year <first_year>  the first year in the report
 -H, --host <host>              connect to the ICAT's DBMS on the host <host>,
                                default '$DEFAULT_HOST'
 -l, --last-year  <last_year>   the last year in the report
 -p, --port <port>              connect to the ICAT's DBMS listening on TCP port
                                <port>, default '$DEFAULT_PORT'
 -U, --user <user>              authorize the DBMS connection as user USER 
                                instead of the default

 -h, --help  display help text and exit
EOF
}


set -e

export PGUSER

readonly DEFAULT_HOST=localhost
readonly DEFAULT_PORT=5432

readonly ExecName=$(basename "$0")

readonly Opts=$(getopt --name "$ExecName" \
                       --options f:hH:l:p:U: \
                       --longoptions first-year:,help,host:,last-year:,port:user, \
                       -- \
                       "$@")

if [ "$?" -ne 0 ]
then
  printf '\n' >&2
  print_help >&2
  exit 1
fi

eval set -- "$Opts"

while true
do
  case "$1" in
    -f|--first-year)
      readonly MinTime=$(date --date "$2"-1-1 '+%s')
      shift 2
      ;;
    -h|--help)
      print_help
      exit 0
      ;;
    -H|--host)
      readonly Host="$2"
      shift 2
      ;;
    -l|--last-year)
      # Take the beginning of the next year and subtract one second to handle leap seconds correctly
      readonly MaxTime=$(($(date --date $(($2 + 1))-1-1 '+%s') - 1))
      shift 2
      ;;
    -p|--port)
      readonly Port="$2"
      shift 2
      ;;
    -U|--user)
      PGUSER="$2"
      shift 2
      ;;
    --)
      shift
      break
      ;;
    *)
      printf '\n' >&2
      print_help >&2
      exit 1
      ;;
  esac
done

if [ -z "$Host" ]
then
  readonly Host="$DEFAULT_HOST"
fi

if [ -z "$Port" ]
then
  readonly Port="$DEFAULT_PORT"
fi

CreateTS='MIN(CAST(create_ts AS INTEGER))'

if [ -n "$MinTime" -a -n "$MaxTime" ]
then
  readonly CreateFilter="$CreateTS BETWEEN $MinTime AND $MaxTime"
elif [ -n "$MinTime" ]
then
  readonly CreateFilter="$CreateTS >= $MinTime"
elif [ -n "$MaxTime" ]
then
  readonly CreateFilter="$CreateTS <= $MaxTime"
else
  readonly CreateFilter=TRUE
fi

psql --no-align --tuples-only --field-separator , --host "$Host" --port "$Port" ICAT <<SQL
\\timing on

BEGIN;

\\echo

CREATE TEMPORARY TABLE data_objs(create_ts, size) AS
SELECT $CreateTS, AVG(data_size) FROM r_data_main GROUP BY data_id HAVING $CreateFilter;

\\echo

SELECT
  SUBSTRING(CAST(DATE_TRUNC('month', TO_TIMESTAMP(create_ts)) AS TEXT) FROM 1 FOR 7),
  COUNT(*),
  CAST(SUM(size) AS BIGINT)
FROM data_objs
GROUP BY DATE_TRUNC('month', TO_TIMESTAMP(create_ts))
ORDER BY 1;

\\echo

ROLLBACK;
SQL