From c6f7a2096c1a3224f76e7d49ad7f794146208a7d Mon Sep 17 00:00:00 2001
From: Erich Eckner <git@eckner.net>
Date: Tue, 25 Feb 2020 15:30:41 +0100
Subject: bin/nit-picker: check residence-duration of packages in testing and
 staging

---
 bin/nit-picker | 85 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 85 insertions(+)

diff --git a/bin/nit-picker b/bin/nit-picker
index 1815558..37b0321 100755
--- a/bin/nit-picker
+++ b/bin/nit-picker
@@ -187,6 +187,19 @@ while pgrep -x ii >/dev/null \
           mysql_join_binary_packages_in_repositories_repositories
           printf ' AND `repositories`.`is_on_master_mirror`;\n'
 
+          printf 'SELECT "repository-duration",CONCAT('
+            printf '`architectures`.`name`,'
+            printf '"/",'
+            printf '`repositories`.`name`'
+          printf '),'
+          printf '`repositories`.`stability`'
+          printf ' FROM `repositories`'
+          mysql_join_repositories_architectures
+          # shellcheck disable=SC2154
+          printf ' WHERE `repositories`.`stability` IN (%s,%s);\n' \
+            "${repository_stability_ids__testing}" \
+            "${repository_stability_ids__staging}"
+
           printf 'SELECT DISTINCT'
           printf ' "upstream-availability",'
           printf '`binary_packages`.`pkgname`'
@@ -661,6 +674,78 @@ while pgrep -x ii >/dev/null \
             sleep 60
           fi
         ;;
+        'repository-duration')
+          case "${parameters#* }" in
+            "${repository_stability_ids__testing}")
+              duration=$((max_testing_duration+3))
+            ;;
+            "${repository_stability_ids__staging}")
+              duration=3
+            ;;
+            *)
+              >&2 printf 'unknown stability: %s\n' "${parameters#* }"
+              continue
+            ;;
+          esac
+          errors=$(
+            ${master_mirror_rsync_command} "${master_mirror_rsync_directory}/${parameters% *}/" \
+            | sed '
+              s/^l\(\S\+\s\+\)\{2\}\(\(\S\+\s\+\)\{2\}\S\+\.pkg\.tar\S*\)\.sig -> \S\+$/\2/
+              t
+              d
+            ' \
+            | {
+              while read -r date time file; do
+                printf '%s %s\n' \
+                  "$(
+                    date +%s -d"${date} ${time}"
+                  )" \
+                  "${file}"
+              done
+              printf '%s ####CUT_HERE####\n' \
+                "$((
+                  $(date +%s) - duration*60*60*24
+                ))"
+            } \
+            | sort -k1n,1 \
+            | sed '/ ####CUT_HERE####$/q' \
+            | sed '$d' \
+            | while read -r time file; do
+              printf '%s: since %s (%s days)\n' \
+                "${file}" \
+                "$(
+                  date -d"@${time}" -I
+                )" \
+                "$((
+                  ($(date +%s) - time)/24/60/60
+                ))"
+            done
+          )
+          if [ -z "${errors}" ]; then
+            continue
+          fi
+          {
+            printf 'The following packages are longer than %s days in %s:\n' \
+              "${duration}" \
+              "${parameters% *}"
+            printf '%s\n' "${errors}" \
+            | head -n5
+            if [ "$(
+              printf '%s\n' "${errors}" \
+              | wc -l
+            )" -gt 5 ]; then
+              printf '... (%s total)\n' \
+                "$(
+                  printf '%s\n' "${errors}" \
+                  | wc -l
+                )"
+            fi
+          } \
+          | local_irc_say
+          if [ $# -eq 0 ]; then
+            sleep 60
+          fi
+        ;;
         *)
           >&2 printf 'action "%s" is not yet implemented ...\n' "${action}"
         ;;
-- 
cgit v1.2.3