repl-report

#!/usr/bin/env bash

help() {
	cat <<EOF

$EXEC_NAME version $VERSION

Usage:
 $EXEC_NAME (-h|--help)
 $EXEC_NAME (-v|--version)
 $EXEC_NAME [-d|--debug][(-A|--age) AGE][(-C|--collection) COLLECTION]
  [(-H|--host) HOST][(-P|--port) PORT][(-U|--user) USER]

This program generates a report on the data objects that are unreplicated or
have a stale replica. The report lists the numbers of unreplicated and stale
data objects and their volumes broken down by the storage resource holding the
corresponding good replicas. It also lists the same numbers grouped by root
resource.

The caller must have an initialized iRODS session, i.e., called \`iinit\` to open
an iRODS session with a cached authentication credentials.

This works with iRODS 4.2.8 or later.

Options:
 -A, --age AGE                how many days old a data object must be to be
                              replicated. A negative number means no time
                              restriction. default: 1
 -C, --collection COLLECTION  only consider this collection when locating
                              unreplicated data objects.
 -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 USER instead
                              of the default
 -v, --version                display version and exit

Environment Variables:
 PGHOST  the default PostgreSQL host
 PGPORT  the default PostgreSQL listening port
 PGUSER  the default PostgreSQL user for authorizating the ICAT DB connection

© 2023, The Arizona Board of Regents on behalf of The University of Arizona. For
license information, see https://cyverse.org/license.
EOF
}

readonly VERSION=9

set -o errexit -o nounset -o pipefail

export PGHOST PGPORT PGUSER

readonly DEFAULT_AGE=1

EXEC_NAME="$(basename "$(realpath --canonicalize-missing "$0")")"
readonly EXEC_NAME

declare DEBUG

main() {
	declare -A optMap=(
		[help]=''
		[version]=''
		[age]="$DEFAULT_AGE"
		[collection]='' )

	if ! map_opts optMap "$@"; then
		help >&2
		return 1
	fi

	if [[ -n "${optMap[help]}" ]]; then
		help
		return 0
	fi

	if [[ -n "${optMap[version]}" ]]; then
		printf '%s\n' "$VERSION"
		return 0
	fi

	mk_report "${optMap[age]}" "${optMap[collection]}"
}

map_opts() {
	local mapVar="$1"
	shift

	local opts
	if ! opts="$(prep_opts "$@")"; then
		return 1
	fi

	eval set -- "$opts"

	while true; do
		case "$1" in
			-A|--age)
				eval "$mapVar"'[age]='"'$2'"
				shift 2
				;;
			-C|--collection)
				eval "$mapVar"'[collection]='"'$2'"
				shift 2
				;;
			-d|--debug)
				DEBUG=debug
				shift
				;;
			-h|--help)
				eval "$mapVar"'[help]=help'
				shift
				;;
			-H|--host)
				PGHOST="$2"
				shift 2
				;;
			-P|--port)
				PGPORT="$2"
				shift 2
				;;
			-U|--user)
				PGUSER="$2"
				shift 2
				;;
			-v|--version)
				eval "$mapVar"'[version]=version'
				shift
				;;
			--)
				shift
				break
				;;
		esac
	done

	readonly DEBUG
}

prep_opts() {
	getopt \
		--longoptions age:,collection:,debug,help,host:,port:,user:,version \
		--options A:C:dhH:P:U:v \
		--name "$EXEC_NAME" \
		-- \
		"$@"
}

mk_report() {
	local age="$1"
	local coll="$2"

	psql --quiet ICAT <<EOSQL
\\pset footer off
$(inject_debug_stmt '\timing on')
$(inject_debug_quiet off)

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

$(inject_table_storage_root_mapping)
$(inject_table_unreplicated_data "$coll")
$(inject_table_unreplicated_replicas "$age")
$(inject_table_stale_data)
$(inject_table_stale_replicas "$age")

\\echo

$(inject_debug_newline)
$(inject_set_title 'Unreplicated in Storage Resources')
SELECT
	COALESCE(u.store_resc, s.store_resc)                     AS "Storage Resource",
	COALESCE(u.count, 0)                                     AS "Unreplicated Count",
	ROUND(CAST(COALESCE(u.size, 0) / 2 ^ 40 AS NUMERIC), 3)  AS "Unreplicated Volume (TiB)",
	COALESCE(s.count, 0)                                     AS "Stale Count",
	ROUND(CAST(COALESCE(s.size, 0) / 2 ^ 40 AS NUMERIC), 3)  AS "Stale Volume (TiB)"
FROM (
		SELECT store_resc, COUNT(*) AS count, SUM(size) AS size
		FROM unreplicated_replicas
		GROUP BY store_resc
	) AS u
	FULL JOIN (
		SELECT store_resc, COUNT(*) AS count, SUM(size) AS size
		FROM stale_replicas
		GROUP BY store_resc
	) AS s
	ON s.store_resc = u.store_resc
ORDER BY 1;

$(inject_debug_newline)
$(inject_set_title 'Unreplicated in Root Resources')
SELECT
	COALESCE(u.root_resc, s.root_resc)                       AS "Root Resource",
	COALESCE(u.count, 0)                                     AS "Unreplicated Count",
	ROUND(CAST(COALESCE(u.size, 0) / 2 ^ 40 AS NUMERIC), 3)  AS "Unreplicated Volume (TiB)",
	COALESCE(s.count, 0)                                     AS "Stale Count",
	ROUND(CAST(COALESCE(s.size, 0) / 2 ^ 40 AS NUMERIC), 3)  AS "Stale Volume (TiB)"
FROM (
		SELECT root_resc, COUNT(*) AS count, SUM(size) AS size
		FROM unreplicated_replicas
		GROUP BY root_resc
	) AS u
	FULL JOIN (
		SELECT root_resc, COUNT(*) AS count, SUM(size) AS size
		FROM stale_replicas
		GROUP BY root_resc
	) AS s
	ON s.root_resc = u.root_resc
ORDER BY 1;

$(inject_debug_newline)
$(inject_set_title 'All Unreplicated')
SELECT
	u.count                                                  AS "Unreplicated Count",
	ROUND(CAST(COALESCE(u.size, 0) / 2 ^ 40 AS NUMERIC), 3)  AS "Unreplicated Volume (TiB)",
	s.count                                                  AS "Stale Count",
	ROUND(CAST(COALESCE(s.size, 0) / 2 ^ 40 AS NUMERIC), 3)  AS "Stale Volume (TiB)"
FROM (SELECT COUNT(*) AS count, SUM(size) AS size FROM unreplicated_replicas) AS u,
	 (SELECT COUNT(*) AS count, SUM(size) AS size FROM stale_replicas) AS s;

$(inject_debug_newline)
$(inject_debug_newline)
ROLLBACK;
EOSQL
}

inject_table_stale_data() {
	cat <<EOF
$(inject_debug_msg 'Gathering stale data')

CREATE TEMPORARY TABLE stale_data(id) ON COMMIT DROP AS
SELECT DISTINCT data_id FROM r_data_main WHERE data_is_dirty = 0;

CREATE INDEX idx_stale_data ON stale_data(id);
EOF
}

inject_table_stale_replicas() {
	local age="$1"

	cat <<EOF
$(inject_debug_msg 'Gathering stale replicas')

CREATE TEMPORARY TABLE stale_replicas(root_resc, store_resc, size) ON COMMIT DROP AS
SELECT s.root_name, s.storage_name, d.data_size
FROM storage_root_mapping AS s JOIN r_data_main AS d ON d.resc_id = s.storage_id
WHERE d.data_id IN (SELECT id FROM stale_data)
	AND d.data_is_dirty = 1
	AND ( $(inject_time_restriction d modify_ts "$age") );

CREATE INDEX idx_stale_replicas_root_resc ON stale_replicas(root_resc);

CREATE INDEX idx_stale_replicas_store_resc ON stale_replicas(store_resc);
EOF
}

inject_table_storage_root_mapping() {
	cat <<EOF
$(inject_debug_msg 'Creating storage to root resource mapping')

CREATE TEMPORARY TABLE storage_root_mapping(storage_id, storage_name, root_name) ON COMMIT DROP AS
WITH RECURSIVE child_mapping AS (
	SELECT
			resc_id AS id,
			resc_name AS name,
			(resc_net != 'EMPTY_RESC_HOST') AS storage,
			resc_name AS root
		FROM r_resc_main
		WHERE resc_parent = '' AND resc_name != 'bundleResc'
	UNION SELECT r.resc_id, r.resc_name, r.resc_net != 'EMPTY_RESC_HOST', m.root
		FROM r_resc_main AS r JOIN child_mapping AS m ON m.id::TEXT = r.resc_parent )
SELECT id, name, root FROM child_mapping WHERE storage;

CREATE INDEX idx_storage_root_mapping_storage_id ON storage_root_mapping(storage_id);
EOF
}

inject_table_unreplicated_data() {
	local coll="$1"

	cat <<EOF
$(inject_debug_msg 'Gathering unreplicated data')

CREATE TEMPORARY TABLE unreplicated_data(id) ON COMMIT DROP AS
SELECT data_id
FROM r_data_main
WHERE ( $(inject_collection_restriction r_data_main "$coll") )
GROUP BY data_id
HAVING COUNT(*) = 1;

CREATE INDEX idx_unreplicated_data_id ON unreplicated_data(id);
EOF
}

inject_table_unreplicated_replicas() {
	local age="$1"

	cat<<EOF
$(inject_debug_msg 'Gathering unreplicated replicas')

CREATE TEMPORARY TABLE unreplicated_replicas(root_resc, store_resc, size) ON COMMIT DROP AS
SELECT s.root_name, s.storage_name, d.data_size
FROM storage_root_mapping AS s JOIN r_data_main AS d ON d.resc_id = s.storage_id
WHERE d.data_id IN (SELECT id FROM unreplicated_data)
	AND ( $(inject_time_restriction d create_ts "$age") );

CREATE INDEX idx_unreplicated_replicas_root_resc ON unreplicated_replicas(root_resc);

CREATE INDEX idx_unreplicated_replicas_store_resc ON unreplicated_replicas(store_resc);
EOF
}

inject_collection_restriction() {
	local table="$1"
	local coll="$2"

	if [[ -n "$coll" ]]; then
		local collQuery
		printf -v collQuery \
			$'SELECT coll_id FROM r_coll_main WHERE coll_name = \'%s\' OR coll_name LIKE \'%s/%%\'' \
			"$coll" "$coll"

		printf '%s.coll_id IN (%s)' "$table" "$collQuery"
	else
		printf TRUE
	fi
}

inject_time_restriction() {
	local table="$1"
	local column="$2"
	local age="$3"

	if (( age < 0 )); then
		# No time restriction

		printf TRUE
	else
		# Determine midnight of the desired day.

		local supCreateDate
		supCreateDate="$(date --iso-8601 --date "$age days ago")"

		local supCreateTs
		supCreateTs="$(date --date "$supCreateDate" '+%s')"
		printf $'%s.%s < \'%011d\'' "$table" "$column" "$supCreateTs"
	fi
}

inject_set_title() {
	local title="$1"

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

inject_debug_quiet() {
	local state="$1"

	inject_debug_stmt "\\set QUIET $state"
}

inject_debug_msg() {
	local msg="$1"

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

inject_debug_newline() {
	inject_debug_stmt '\echo'
}

inject_debug_stmt() {
	local stmt="$1"

	if [[ -n "${DEBUG-}" ]]; then
		printf '%s\n' "$stmt"
	fi
}

main "$@"