path: root/bin/db-update

#!/bin/sh

# move binary packages from staging to testing (if possible [1]) and
# additionally tested packages from testing to the respective stable
# repository (if possible [1])

# The condition [1] is explained in the stored function
# calculate_maximal_moveable_set which is created in bin/bootsrap-mysql

# TODO: make (all) locking less restrictive

# TODO: use the intention-queue

# shellcheck disable=SC2039,SC2119,SC2120

# shellcheck source=../lib/load-configuration
. "${0%/*}/../lib/load-configuration"

# shellcheck disable=SC2016
usage() {
  >&2 echo ''
  >&2 echo 'db-update [options] [packages]:'
  >&2 echo ' move tested packages from testing to stable.'
  >&2 echo ' move possible packages from staging to testing.'
  >&2 echo ''
  >&2 echo 'possible options:'
  >&2 echo '  -f|--force $arch/$repo/$pkgname:'
  >&2 echo '    Force movement of given package and move nothing else.'
  >&2 echo '  -h|--help:'
  >&2 echo '    Show this help and exit.'
  >&2 echo '  -i|--ignore-insanity:'
  >&2 echo '    Do not abort when insane.'
  >&2 echo '  -n|--no-action:'
  >&2 echo '    Only print what would be moved.'
  >&2 echo '  -o|--only $identifier:'
  >&2 echo '    Only try to move packages *from* the given'
  >&2 echo '    stability/architecture. $identifier must match one of'
  >&2 echo '    the regexes'
  >&2 echo '      (testing|staging)'
  >&2 echo '      !?(i486|i686|pentium4)'
  >&2 echo '      !?(i486|i686|pentium4)/(testing|staging)'
  >&2 echo '    where preceding exclamation mark negates the selection'
  >&2 echo '    of the architecture (but not the stability!).'
  >&2 echo '    Can be given multiple times to aggregate the selected'
  >&2 echo '    repositories.'
  >&2 echo '  -p|--progressive:'
  >&2 echo '    Move forward any package which replaces no package whose'
  >&2 echo '    dependencies are all available somewhere.'
  >&2 echo '    Note, that this _may_ move _less_ packages.'
  >&2 echo '  -w|--wait:'
  >&2 echo '    If necessary, wait for lock blocking.'
  [ -z "$1" ] && exit 1 || exit "$1"
}

eval set -- "$(
  getopt -o f:hino:pw \
    --long force \
    --long help \
    --long ignore-insanity \
    --long no-action \
    --long only: \
    --long progressive \
    --long wait \
    -n "$(basename "$0")" -- "$@" || \
  echo usage
)"

block_flag='-n'
ignore_insanity=false
no_action=false
progressive=false
force_pkgs=''
only=''

while true
do
  case "$1" in
    -f|--force)
      shift
      force_pkgs=$(
        printf '%s' "$1" | \
          base64 -w0
        printf '\n%s' "${force_pkgs}"
      )
    ;;
    -h|--help)
      usage 0
    ;;
    -i|--ignore-insanity)
      ignore_insanity=true
    ;;
    -n|--no-action)
      no_action=true
    ;;
    -o|--only)
      shift
      if ! printf '%s\n' "$1" \
      | grep -qx '\(testing\|staging\)\|!\?\(i486\|i686\|pentium4\)\|!\?\(i486\|i686\|pentium4\)/\(testing\|staging\)'; then
        >&2 printf 'Syntax error, --only parameter "%s" does not match required regex.\n' "$1"
        usage
      fi
      only="$1 ${only}"
    ;;
    -p|--progressive)
      progressive=true
    ;;
    -w|--wait)
      block_flag=''
    ;;
    --)
      shift
      break
    ;;
    *)
      >&2 echo 'Whoops, forgot to implement option "'"$1"'" internally.'
      exit 42
    ;;
  esac
  shift
done

if [ $# -ne 0 ]; then
  >&2 echo 'db-update: too many arguments'
  usage
fi

if ${progressive} && \
  [ -n "${force_pkgs}" ]; then
  >&2 echo 'db-update: conflicting arguments'
  usage
fi

if [ -s "${work_dir}/build-master-sanity" ]; then
  >&2 echo 'Build master is not sane.'
  if ! ${ignore_insanity}; then
    exit
  fi
fi

if [ -n "${only}" ]; then
  only=$(
    printf '%s\n' "${only}" \
    | tr ' ' '\n'
  )
  only=$(
    printf '%s\n' "${only}" \
    | grep '^[^!]' \
    || true
    printf '%s\n' "${only}" \
    | sed "$(
        for arch in i486 i686 pentium4; do
          printf 's@^!%s\\(/.\\+\\)\\?$@' \
            "${arch}"
          for arch2 in i486 i686 pentium4; do
            if [ "${arch}" == "${arch2}" ]; then
              continue
            fi
            printf '%s\\1\\n' \
              "${arch2}"
          done \
          | sed 's/\\n$//'
          printf '@\nt\n'
        done
      )"'
      d
    '
  )
fi

if ! ${no_action}; then
  # Create lock.
  exec 9> "${package_database_lock_file}"
  if ! verbose_flock ${block_flag} 9; then
    >&2 echo 'come back (shortly) later - I cannot lock package database.'
    exit 0
  fi

  exec 8> "${sanity_check_lock_file}"
  if ! verbose_flock -s ${block_flag} 8; then
    >&2 echo 'come back (shortly) later - sanity-check currently running.'
    exit 0
  fi

  # shellcheck disable=SC2016
  {
    printf 'UPDATE `binary_packages`'
    printf ' JOIN ('
      printf 'SELECT `binary_packages_in_repositories`.`package`,'
      printf 'MIN(`binary_packages_in_repositories`.`last_moved`) AS `first_last_moved`'
      printf ' FROM `binary_packages_in_repositories`'
      mysql_join_binary_packages_in_repositories_repositories
      # shellcheck disable=SC2154
      printf ' WHERE `repositories`.`stability`=%s' \
        "${repository_stability_ids__testing}"
      printf ' GROUP BY `binary_packages_in_repositories`.`package`'
    printf ') AS `binary_packages_in_repositories`'
    printf ' ON `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
    printf ' SET `binary_packages`.`is_tested`=1'
    printf ' WHERE NOT `binary_packages`.`has_issues`'
    printf ' AND NOT `binary_packages`.`is_tested`'
    printf ' AND `binary_packages_in_repositories`.`first_last_moved`<SUBDATE(NOW(),from_base64("%s"))' \
      "$(
        printf -- '%s' "${max_testing_duration}" | \
          base64 -w0
      )"
  } | \
    mysql_run_query
fi

# Create tmp_dir and trap.
tmp_dir=$(mktemp -d "${work_dir}/tmp.db-update.XXXXXXXXXX")
trap 'rm -rf --one-file-system "${tmp_dir}"' EXIT

export TMPDIR="${tmp_dir}"


# shellcheck disable=SC2016
{
  printf 'SELECT DISTINCT'
  printf ' `repositories`.`architecture`,'
  printf '`architectures`.`name`,'
  printf '`repositories`.`stability`,'
  printf '`repository_stabilities`.`name`'
  printf ' FROM `repositories`'
  mysql_join_repositories_architectures
  mysql_join_repositories_repository_stabilities
  printf ' JOIN `repository_moves`'
  printf ' ON `repository_moves`.`from_repository`=`repositories`.`id`'
  printf ' WHERE `repositories`.`is_on_master_mirror`'
  printf ' ORDER BY `repositories`.`stability`,'
  printf 'SHA2('
    printf 'CONCAT('
      printf '`repositories`.`architecture`,'
      printf '`repositories`.`stability`,'
      printf 'NOW()'
    printf '),'
    printf '256'
  printf ')'
} | \
  mysql_run_query | \
  while read -r arch_id arch source_stability_id source_stability; do

    if [ -n "${only}" ]; then
      does_match=false
      for possible_match in ${only}; do
        if [ "${arch}/${source_stability}" = "${possible_match}" ] \
        || [ "${source_stability}" = "${possible_match}" ] \
        || [ "${arch}" = "${possible_match}" ]; then
          does_match=true
          break
        fi
      done
      if ! ${does_match}; then
        continue
      fi
    fi

    find "${tmp_dir}" -xdev -mindepth 1 -delete

    {
      # Note, that we do not need to check that two repositories a and b
      # are for the same architecture if we joined them via
      # repository_moves, because only repositories with the same
      # architectures should be listed there.
      if [ -n "${force_pkgs}" ]; then
        printf 'DROP TEMPORARY TABLE IF EXISTS `%s_bpir`;\n' \
          'moveable' 'replaced'
        printf 'CREATE TEMPORARY TABLE `replaced_bpir` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n'
        printf 'CREATE TEMPORARY TABLE `moveable_bpir` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n'
        printf 'INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`)'
        printf ' SELECT '
        printf '`binary_packages_in_repositories`.`id`,NULL'
        printf ' FROM `binary_packages_in_repositories`'
        mysql_join_binary_packages_in_repositories_repositories
        mysql_join_repositories_architectures
        mysql_join_binary_packages_in_repositories_binary_packages
        printf ' WHERE CONCAT('
          printf '`architectures`.`name`,"/",'
          printf '`repositories`.`name`,"/",'
          printf '`binary_packages`.`pkgname`'
        printf ') IN ('
        # shellcheck disable=SC2086
        printf 'from_base64("%s"),' \
          ${force_pkgs} | \
          sed 's/,$/);\n/'
        printf 'DELETE `moveable_bpir`'
        printf ' FROM `moveable_bpir`'
        printf ' JOIN `binary_packages_in_repositories`'
        printf ' ON `binary_packages_in_repositories`.`id`=`moveable_bpir`.`id`'
        mysql_join_binary_packages_in_repositories_binary_packages
        mysql_join_binary_packages_in_repositories_repositories
        printf ' WHERE `repositories`.`stability`!=%s' \
          "${source_stability_id}"
        printf ' OR `repositories`.`architecture`!=%s;\n' \
          "${arch_id}"
        printf 'UPDATE `moveable_bpir`'
        printf ' JOIN `binary_packages_in_repositories`'
        printf ' ON `binary_packages_in_repositories`.`id`=`moveable_bpir`.`id`'
        mysql_join_binary_packages_in_repositories_binary_packages
        mysql_join_binary_packages_build_assignments
        mysql_join_build_assignments_package_sources
        mysql_join_package_sources_upstream_repositories
        mysql_join_upstream_repositories_repository_moves
        printf ' AND `repository_moves`.`from_repository`=`binary_packages_in_repositories`.`repository`'
        printf ' SET `moveable_bpir`.`to_repository`=`repository_moves`.`to_repository`;\n'
        printf 'DELETE FROM `moveable_bpir`'
        printf ' WHERE `moveable_bpir`.`to_repository` IS NULL;\n'
        printf 'INSERT IGNORE INTO `replaced_bpir` (`id`,`replaced_by`)'
        printf ' SELECT `binary_packages_in_repositories`.`id`,`moveable_bpir`.`id`'
        printf ' FROM `moveable_bpir`'
        printf ' JOIN `binary_packages_in_repositories` AS `subst_bpir`'
        printf ' ON `moveable_bpir`.`id`=`subst_bpir`.`id`'
        mysql_join_binary_packages_in_repositories_binary_packages 'subst_bpir' 'subst_bp'
        printf ' JOIN `binary_packages`'
        printf ' ON `binary_packages`.`pkgname`=`subst_bp`.`pkgname`'
        mysql_join_binary_packages_binary_packages_in_repositories
        mysql_join_binary_packages_in_repositories_repositories
        printf ' JOIN `repositories` AS `m_to_r`'
        printf ' ON `moveable_bpir`.`to_repository`=`m_to_r`.`id`'
        printf ' AND `repositories`.`stability`=`m_to_r`.`stability`'
        printf ' AND `repositories`.`architecture`=`m_to_r`.`architecture`;\n'
      elif ${progressive}; then
        printf 'DROP TEMPORARY TABLE IF EXISTS `%s_bpir`;\n' \
          'moveable' 'replaced'
        printf 'CREATE TEMPORARY TABLE `replaced_bpir` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n'
        printf 'CREATE TEMPORARY TABLE `moveable_bpir` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n'

        # packages that replace broken packages
        printf 'INSERT IGNORE INTO `replaced_bpir` (`id`,`replaced_by`)'
        printf ' SELECT `old_bpir`.`id`,`new_bpir`.`id`'
        printf ' FROM `binary_packages_in_repositories` AS `new_bpir`'
        mysql_join_binary_packages_in_repositories_binary_packages 'new_bpir' 'new_bp'
        mysql_join_binary_packages_in_repositories_repositories 'new_bpir' 'from_r'
        printf ' AND `from_r`.`stability`=%s' \
          "${source_stability_id}"
        printf ' AND `from_r`.`architecture`=%s' \
          "${arch_id}"
        printf ' AND `from_r`.`is_on_master_mirror`'
        mysql_join_binary_packages_build_assignments 'new_bp'
        mysql_join_build_assignments_package_sources
        mysql_join_package_sources_upstream_repositories
        mysql_join_upstream_repositories_repository_moves
        printf ' AND `repository_moves`.`from_repository`=`from_r`.`id`'
        printf ' JOIN `repositories` AS `to_r`'
        printf ' ON `repository_moves`.`to_repository`=`to_r`.`id`'
        printf ' JOIN `binary_packages` AS `old_bp`'
        printf ' ON `new_bp`.`pkgname`=`old_bp`.`pkgname`'
        mysql_join_binary_packages_binary_packages_in_repositories 'old_bp' 'old_bpir'
        mysql_join_binary_packages_in_repositories_repositories 'old_bpir' 'old_r'
        printf ' AND `old_r`.`stability`=`to_r`.`stability`'
        printf ' AND `old_r`.`architecture`=`to_r`.`architecture`'
        mysql_join_binary_packages_dependencies 'old_bp'
        mysql_join_dependencies_dependency_types
        printf ' AND `dependency_types`.`relevant_for_binary_packages`'
        mysql_join_dependencies_versions '' 'd_v'
        printf ' WHERE NOT EXISTS ('
          printf 'SELECT 1'
          printf ' FROM `install_target_providers`'
          mysql_join_install_target_providers_binary_packages '' 'itp_bp'
          mysql_join_binary_packages_binary_packages_in_repositories 'itp_bp' 'itp_bpir'
          mysql_join_binary_packages_in_repositories_repositories 'itp_bpir' 'itp_r'
          mysql_join_install_target_providers_versions '' 'itp_v'
          printf ' JOIN `repository_stability_relations` AS `rsr_a`'
          printf ' ON `rsr_a`.`more_stable`=`itp_r`.`stability`'
          printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
          printf ' AND'
          mysql_query_ordering_correct \
            '`d_v`.`order`' \
            '`itp_v`.`order`' \
            '`dependencies`.`version_relation`'
          printf ' AND `itp_r`.`architecture`=`old_r`.`architecture`'
          printf ' AND `rsr_a`.`less_stable`=`old_r`.`stability`'
          # this is the least stable package of stability at least to_r.stability
          printf ' AND NOT EXISTS ('
            printf 'SELECT 1'
            printf ' FROM `binary_packages` AS `subst_bp`'
            mysql_join_binary_packages_binary_packages_in_repositories 'subst_bp' 'subst_bpir'
            mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r'
            printf ' JOIN `repository_stability_relations` AS `rsr_b`'
            printf ' ON `rsr_b`.`less_stable`=`subst_r`.`stability`'
            printf ' JOIN `repository_stability_relations` AS `rsr_c`'
            printf ' ON `rsr_c`.`more_stable`=`subst_r`.`stability`'
            printf ' WHERE `subst_bp`.`pkgname`=`itp_bp`.`pkgname`'
            printf ' AND `subst_bp`.`id`!=`itp_bp`.`id`'
            printf ' AND `rsr_b`.`more_stable`=`itp_r`.`stability`'
            printf ' AND `rsr_c`.`less_stable`=`old_r`.`stability`'
            printf ' AND `subst_r`.`architecture`=`itp_r`.`architecture`'
          printf ')'
        printf ');\n'

        printf 'INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`)'
        printf ' SELECT `replaced_bpir`.`replaced_by`,`binary_packages_in_repositories`.`repository`'
        printf ' FROM `replaced_bpir`'
        printf ' JOIN `binary_packages_in_repositories`'
        printf ' ON `binary_packages_in_repositories`.`id`=`replaced_bpir`.`id`'
        printf ';\n'

        # packages which replace nothing
        printf 'INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`)'
        printf ' SELECT `binary_packages_in_repositories`.`id`,`repository_moves`.`to_repository`'
        printf ' FROM `binary_packages_in_repositories`'
        mysql_join_binary_packages_in_repositories_binary_packages
        mysql_join_binary_packages_in_repositories_repositories
        printf ' AND `repositories`.`is_on_master_mirror`'
        printf ' AND `repositories`.`stability`=%s' \
          "${source_stability_id}"
        printf ' AND `repositories`.`architecture`=%s' \
          "${arch_id}"
        mysql_join_binary_packages_build_assignments
        mysql_join_build_assignments_package_sources
        mysql_join_package_sources_upstream_repositories
        mysql_join_upstream_repositories_repository_moves
        printf ' AND `repository_moves`.`from_repository`=`binary_packages_in_repositories`.`repository`'
        printf ' JOIN `repositories` as `to_r`'
        printf ' ON `to_r`.`id`=`repository_moves`.`to_repository`'
        printf ' WHERE NOT EXISTS ('
          printf 'SELECT 1'
          printf ' FROM `binary_packages_in_repositories` AS `repl_bpir`'
          mysql_join_binary_packages_in_repositories_binary_packages 'repl_bpir' 'repl_bp'
          mysql_join_binary_packages_in_repositories_repositories 'repl_bpir' 'repl_r'
          printf ' WHERE `repl_bp`.`pkgname`=`binary_packages`.`pkgname`'
          printf ' AND `repl_r`.`stability`=`to_r`.`stability`'
          printf ' AND `repl_r`.`architecture`=`to_r`.`architecture`'
        printf ');\n'
      else
        printf 'CALL calculate_maximal_moveable_set(%s,%s);\n' \
          "${arch_id}" \
          "${source_stability_id}"
      fi

      printf 'CREATE TEMPORARY TABLE `rps` (`id` MEDIUMINT, UNIQUE INDEX (`id`));\n'
      printf 'INSERT IGNORE INTO `rps` (`id`)'
      printf ' SELECT `moveable_bpir`.`to_repository`'
      printf ' FROM `moveable_bpir`;\n'
      printf 'INSERT IGNORE INTO `rps` (`id`)'
      printf ' SELECT `binary_packages_in_repositories`.`repository`'
      printf ' FROM `moveable_bpir`'
      printf ' JOIN `binary_packages_in_repositories`'
      printf ' ON `moveable_bpir`.`id`=`binary_packages_in_repositories`.`id`;\n'
      printf 'INSERT IGNORE INTO `rps` (`id`)'
      printf ' SELECT `binary_packages_in_repositories`.`repository`'
      printf ' FROM `replaced_bpir`'
      printf ' JOIN `binary_packages_in_repositories`'
      printf ' ON `replaced_bpir`.`id`=`binary_packages_in_repositories`.`id`;\n'

      printf 'SELECT "repositories",`architectures`.`name`,`repositories`.`name`'
      printf ' FROM `rps`'
      printf ' JOIN `repositories`'
      printf ' ON `rps`.`id`=`repositories`.`id`'
      mysql_join_repositories_architectures
      printf ';\n'

      printf 'SELECT "mv.id",`moveable_bpir`.`id`,`moveable_bpir`.`to_repository`'
      printf ' FROM `moveable_bpir`;\n'

      printf 'SELECT "mv",'
      mysql_package_name_query
      printf ',`r_a`.`name`,`repositories`.`name`,`new_r_a`.`name`,`new_repo`.`name`'
      printf ' FROM `moveable_bpir`'
      printf ' JOIN `binary_packages_in_repositories`'
      printf ' ON `moveable_bpir`.`id`=`binary_packages_in_repositories`.`id`'
      mysql_join_binary_packages_in_repositories_binary_packages
      mysql_join_binary_packages_in_repositories_repositories
      mysql_join_binary_packages_architectures
      printf ' LEFT'
      mysql_join_binary_packages_compressions
      mysql_join_repositories_architectures '' 'r_a'
      printf ' JOIN `repositories` AS `new_repo`'
      printf ' ON `new_repo`.`id`=`moveable_bpir`.`to_repository`'
      mysql_join_repositories_architectures 'new_repo' 'new_r_a'
      printf ';\n'

      printf 'SELECT "rm.id",`replaced_bpir`.`id`'
      printf ' FROM `replaced_bpir`;\n'

      printf 'SELECT "rm",'
      mysql_package_name_query
      printf ',`r_a`.`name`,`repositories`.`name`'
      printf ' FROM `replaced_bpir`'
      printf ' JOIN `binary_packages_in_repositories`'
      printf ' ON `replaced_bpir`.`id`=`binary_packages_in_repositories`.`id`'
      mysql_join_binary_packages_in_repositories_binary_packages
      mysql_join_binary_packages_in_repositories_repositories
      mysql_join_binary_packages_architectures
      printf ' LEFT'
      mysql_join_binary_packages_compressions
      mysql_join_repositories_architectures '' 'r_a'
      printf ';\n'
    } | \
      mysql_run_query | \
      tr '\t' ' ' | \
      grep '^\(repositories\|\(rm\|mv\)\(\.id\)\?\) ' | \
      while read -r what content; do
        printf '%s\n' "${content}" >> \
          "${tmp_dir}/${what}"
      done

    if [ ! -s "${tmp_dir}/repositories" ]; then
      >&2 printf 'Nothing to move here (from %s/%s).\n' \
        "${arch}" \
        "${source_stability}"
      continue
    fi

    touch \
      "${tmp_dir}/mv" \
      "${tmp_dir}/mv.id" \
      "${tmp_dir}/rm" \
      "${tmp_dir}/rm.id"

    # shellcheck disable=SC2086
    for s in "${tmp_dir}/"*; do
      sort -u "${s}" | \
        sponge "${s}"
    done

    # receive the repository databases from the master mirror
    mkdir "${tmp_dir}/dbs"
    while read -r arch repo; do
      mkdir -p "${tmp_dir}/dbs/${arch}/${repo}"
      # shellcheck disable=SC2086
      failsafe_rsync \
        "${master_mirror_rsync_directory}/${arch}/${repo}/${repo}.db."* \
        "${master_mirror_rsync_directory}/${arch}/${repo}/${repo}.files."* \
        "${tmp_dir}/dbs/${arch}/${repo}/"
    done < \
      "${tmp_dir}/repositories"

    # remove to-be-deleted packages
    # shellcheck disable=SC2094
    cut -d' ' -f2,3 < \
      "${tmp_dir}/rm" | \
      sort -u | \
      while read -r arch repo; do
        grep " $(str_to_regex "${arch} ${repo}")\$" "${tmp_dir}/rm" | \
          sed '
            s/\(-[^-]\+\)\{3\} \S\+ \S\+$//
          ' | \
          xargs -r repo-remove -q "${tmp_dir}/dbs/${arch}/${repo}/${repo}.db.tar.gz"
      done

    # copy and delete moved packages
    # shellcheck disable=SC2094
    cut -d' ' -f2,3,4,5 < \
      "${tmp_dir}/mv" | \
      sort -u | \
      while read -r from_arch from_repo to_arch to_repo; do
        grep " $(str_to_regex "${from_arch} ${from_repo} ${to_arch} ${to_repo}")\$" "${tmp_dir}/mv" | \
          sed '
            s/-[^-]\+\( \S\+\)\{4\}$//
          ' | \
          xargs -r "${base_dir}/bin/repo-copy" \
            "${tmp_dir}/dbs/${from_arch}/${from_repo}/${from_repo}.db.tar.gz" \
            "${tmp_dir}/dbs/${to_arch}/${to_repo}/${to_repo}.db.tar.gz"
        grep " $(str_to_regex "${from_arch} ${from_repo} ${to_arch} ${to_repo}")\$" "${tmp_dir}/mv" | \
          sed '
            s/\(-[^-]\+\)\{3\}\( \S\+\)\{4\}$//
          ' | \
          xargs -r repo-remove -q \
            "${tmp_dir}/dbs/${from_arch}/${from_repo}/${from_repo}.db.tar.gz"
      done

    # create real file names of packages, because
    # mysql_query_and_delete_unneeded_binary_packages does so, too
    sed -i '
      s,^\(\S\+\) \(\S\+\) \(\S\+\)$,\2/\3/\1,
    ' "${tmp_dir}/rm"

    # somewhat inaccurate
    if ! ${no_action}; then
      {
        printf 'CREATE TEMPORARY TABLE `replaced_bpir` (`id` BIGINT, UNIQUE KEY (`id`));\n'
        printf 'CREATE TEMPORARY TABLE `moved_bpir` (`id` BIGINT, `new_repository` MEDIUMINT, UNIQUE KEY (`id`));\n'
        printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `%s` COLUMNS TERMINATED BY " ";\n' \
          "${tmp_dir}/mv.id" 'moved_bpir' \
          "${tmp_dir}/rm.id" 'replaced_bpir'
        printf 'DELETE `binary_packages_in_repositories`'
        printf ' FROM `binary_packages_in_repositories`'
        printf ' JOIN `replaced_bpir`'
        printf ' ON `binary_packages_in_repositories`.`id`=`replaced_bpir`.`id`;\n'
        mysql_query_and_delete_unneeded_binary_packages
        printf 'UPDATE `binary_packages_in_repositories`'
        printf ' JOIN `moved_bpir`'
        printf ' ON `binary_packages_in_repositories`.`id`=`moved_bpir`.`id`'
        printf ' SET `binary_packages_in_repositories`.`repository`=`moved_bpir`.`new_repository`,'
        printf '`binary_packages_in_repositories`.`last_moved`=NOW()'
        printf ' WHERE `binary_packages_in_repositories`.`repository`!=`moved_bpir`.`new_repository`;\n'
      } | \
        mysql_run_query | \
        sort -u >> \
        "${tmp_dir}/rm"
    fi

    # move the packages remotely via sftp
    {
      sed '
        s/^/rm "/
        s/$/"/
        p
        s/"$/.sig"/
      ' "${tmp_dir}/rm"
      sed '
        s,^\(\S\+\) \(\S\+\) \(\S\+\) \(\S\+\) \(\S\+\)$,rename "\2/\3/\1" "\4/\5/\1"\nrename "\2/\3/\1.sig" "\4/\5/\1.sig",
      ' "${tmp_dir}/mv"
      echo 'quit'
    } | \
      if ${no_action}; then
        sed 's|^|sftp: |'
      else
        failsafe_sftp
      fi

    if ${no_action}; then
      continue
    fi

    # and push our local *.db.tar.gz via rsync
    while read -r arch repo; do
      recompress_gz \
        "${tmp_dir}" \
        "${tmp_dir}/dbs/${arch}/${repo}/${repo}."*".tar.gz" \
        "${tmp_dir}/dbs/${arch}/${repo}/${repo}."*".tar.gz.old"
      # shellcheck disable=SC2086
      failsafe_rsync \
        "${tmp_dir}/dbs/${arch}/${repo}/${repo}.db."* \
        "${tmp_dir}/dbs/${arch}/${repo}/${repo}.files."* \
        "${master_mirror_rsync_directory}/${arch}/${repo}/"
    done < \
      "${tmp_dir}/repositories"

  done

trigger_mirror_refreshs