#!/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: intentionally break packages that failed to build for a certain time

# TODO: -p seems to try to move stuff from community to community and from
# extra to extra - which of course leads to problems.

# 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 '  -e|--everything:'
  >&2 echo '    Force movement of every package and move nothing else.'
  >&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 accumulate 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 '  -s|--stuck:'
  >&2 echo '    Force movement of every package, that is stuck for too'
  >&2 echo '    long, and move nothing else.'
  >&2 echo '  -w|--wait:'
  >&2 echo '    If necessary, wait for lock blocking.'
  [ -z "$1" ] && exit 1 || exit "$1"
}

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

block_flag='-n'
ignore_insanity=false
no_action_flag=''
progressive=false
force_pkgs=''
force_every_pkg=false
force_stuck_pkgs=false
only=''

while true
do
  case "$1" in
    -e|--everything)
      force_every_pkg=true
    ;;
    -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_flag='-n'
    ;;
    -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
      >&2 echo 'Do not use this option, it is currently broken.'
      exit 42
    ;;
    -s|--stuck)
      force_stuck_pkgs=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

number_of_force_flags=0
${progressive} \
&& number_of_force_flags=$((number_of_force_flags+1))
[ -n "${force_pkgs}" ] \
&& number_of_force_flags=$((number_of_force_flags+1))
${force_every_pkg} \
&& number_of_force_flags=$((number_of_force_flags+1))
${force_stuck_pkgs} \
&& number_of_force_flags=$((number_of_force_flags+1))

if [ ${number_of_force_flags} -gt 1 ]; 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 [ -z "${no_action_flag}" ]; then
  # Create lock.
  exec 9> "${package_database_lock_file}"
  # shellcheck disable=SC2086
  if ! verbose_flock ${block_flag} 9; then
    >&2 echo 'come back (shortly) later - I cannot lock package database.'
    exit 0
  fi

  # shellcheck disable=SC2086
  if intentions_left ${block_flag}; then
    >&2 echo 'come back (shortly) later - There are still intentions in the queue.'
    exit 0
  fi

  exec 8> "${sanity_check_lock_file}"
  # shellcheck disable=SC2086
  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}"

# mysql_query_update_replaced_bpir
# print a query to fill the `replaced_bpir` table from `moveable_bpir`

# shellcheck disable=SC2016
mysql_query_update_replaced_bpir() {
  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'
}

# 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}" ] || \
        ${force_every_pkg} || \
        ${force_stuck_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
        printf ' AND `repositories`.`is_on_master_mirror`'
        printf ' AND `repositories`.`stability`=%s' \
          "${source_stability_id}"
        printf ' AND `repositories`.`architecture`=%s;\n' \
          "${arch_id}"
        mysql_join_repositories_architectures
        mysql_join_binary_packages_in_repositories_binary_packages
        printf ' JOIN `binary_packages` AS `f_bp`'
        printf ' ON `binary_packages`.`build_assignment`=`f_bp`.`build_assignment`'
        mysql_join_binary_packages_binary_packages_in_repositories 'f_bp' 'f_bpir'
        printf ' AND `f_bpir`.`repository`=`repositories`.`id`'
        if [ -n "${force_pkgs}" ]; then
          printf ' WHERE CONCAT('
            printf '`architectures`.`name`,"/",'
            printf '`repositories`.`name`,"/",'
            printf '`f_bp`.`pkgname`'
          printf ') IN ('
          # shellcheck disable=SC2086
          printf 'from_base64("%s"),' \
            ${force_pkgs} | \
            sed 's/,$/);\n/'
        elif ${force_stuck_pkgs}; then
          printf ' WHERE `binary_packages_in_repositories`.`last_moved` < DATE_SUB(NOW(),INTERVAL '
          # shellcheck disable=SC2154
          case "${source_stability_id}" in
            "${repository_stability_ids__testing}")
              printf '%s.5' "$((max_testing_duration+2))"
            ;;
            "${repository_stability_ids__staging}")
              printf '2.5'
            ;;
            *)
              >&2 printf 'unknown source stability id: %s\n' "${source_stability_id}"
              printf '1000000'
            ;;
          esac
          printf ' day);\n'
        else # ${force_every_pkg}
          printf ';\n'
        fi

        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'
        mysql_query_update_replaced_bpir
      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`)'
# FIXME: `tm_bpir`.`id` is plain wrong, here. It should be the bpir.id of the package being moved,
# not of the one being replaced. Uwaga: we need to fill tm_bpir.id into replaced_bpir, too!
        printf ' SELECT `tm_bpir`.`id`,`tm_bpir`.`repository`'
        printf ' FROM `replaced_bpir`'
        printf ' JOIN `binary_packages_in_repositories`'
        printf ' ON `binary_packages_in_repositories`.`id`=`replaced_bpir`.`id`'
        mysql_join_binary_packages_in_repositories_binary_packages
        printf ' JOIN `binary_packages` AS `tm_bp`'
        printf ' ON `tm_bp`.`build_assignment`=`binary_packages`.`build_assignment`'
        mysql_join_binary_packages_binary_packages_in_repositories 'tm_bp' 'tm_bpir'
        printf ' AND `tm_bpir`.`repository`=`binary_packages_in_repositories`.`repository`'
        printf ';\n'

        mysql_query_update_replaced_bpir

        # 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"

    perma_tmp_dir=$(mktemp -d "${work_dir}/tmp.db-update.permanent.XXXXXXXXXX")
    # shellcheck disable=SC2086
    for s in "${tmp_dir}/"*; do
      sort -u "${s}" \
      > "${perma_tmp_dir}/${s##*/}"
    done

    # receive the repository databases from the master mirror
    while read -r arch repo; do
      {
        printf 'mkdir -p "%s/dbs/%s/%s"\n' \
          "${perma_tmp_dir}" \
          "${arch}" \
          "${repo}"
        printf 'failsafe_rsync'
        for suffix in 'db' 'files'; do
          printf ' "%s/%s/%s/%s.%s."*' \
            "${master_mirror_rsync_directory}" \
            "${arch}" \
            "${repo}" \
            "${repo}" \
            "${suffix}"
        done
        printf ' "%s/dbs/%s/%s/"\n' \
          "${perma_tmp_dir}" \
          "${arch}" \
          "${repo}"
      } \
      | intent_something "${no_action_flag}"
    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
        {
# TODO: sign repositories
          printf 'repo-remove -q "%s/dbs/%s/%s/%s.db.tar.gz" ' \
            "${perma_tmp_dir}" \
            "${arch}" \
            "${repo}" \
            "${repo}"
          grep " $(str_to_regex "${arch} ${repo}")\$" "${tmp_dir}/rm" \
          | sed '
            s/\(-[^-]\+\)\{3\} \S\+ \S\+$//
          ' \
          | tr '\n' ' '
          printf '\n'
        } \
        | intent_something "${no_action_flag}"
      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
        {
          printf '"%s/bin/repo-copy" ' \
            "${base_dir}"
          printf '"%s/dbs/%s/%s/%s.db.tar.gz" ' \
            "${perma_tmp_dir}" "${from_arch}" "${from_repo}" "${from_repo}" \
            "${perma_tmp_dir}" "${to_arch}" "${to_repo}" "${to_repo}"
          grep " $(str_to_regex "${from_arch} ${from_repo} ${to_arch} ${to_repo}")\$" "${tmp_dir}/mv" \
          | sed '
            s/-[^-]\+\( \S\+\)\{4\}$//
          ' \
          | tr '\n' ' '
          printf '\n'
          printf 'repo-remove -q "%s/dbs/%s/%s/%s.db.tar.gz" ' \
            "${perma_tmp_dir}" \
            "${from_arch}" \
            "${from_repo}" \
            "${from_repo}"
          grep " $(str_to_regex "${from_arch} ${from_repo} ${to_arch} ${to_repo}")\$" "${tmp_dir}/mv" \
          | sed '
            s/\(-[^-]\+\)\{3\}\( \S\+\)\{4\}$//
          ' \
          | tr '\n' ' '
          printf '\n'
        } \
        | intent_something "${no_action_flag}"
      done

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

    # somewhat inaccurate
    {
      printf '{\n'
      printf '  mysql_run_query'
      printf ' | sort -u'
      printf ' >> "%s/rm"\n' \
        "${perma_tmp_dir}"
      printf '} <<END_OF_MYSQL_QUERY\n'

      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' \
        "${perma_tmp_dir}/mv.id" 'moved_bpir' \
        "${perma_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'

      printf 'END_OF_MYSQL_QUERY\n'
    } \
    | sed 's/`/\\`/g' \
    | intent_something "${no_action_flag}"

    # move the packages remotely via sftp
    {
      printf '{\n'
      printf '  sed '"'"'\n'
      printf '    s/^/rm "/\n'
      printf '    s/$/"/\n'
      printf '    p\n'
      printf '    s/"$/.sig"/\n'
      printf '  '"'"' "%s/rm"\n' \
        "${perma_tmp_dir}"
      printf '  sed '"'"'\n'
      printf '    s,^\\(\\S\\+\\) \\(\\S\\+\\) \\(\\S\\+\\) \\(\\S\\+\\) \\(\\S\\+\\)$,'
      printf 'rm "\\2/\\3/\\1"\\n'
      printf 'rm "\\2/\\3/\\1.sig"\\n'
      printf 'ln -s "../../pool/\\1" "\\4/\\5/\\1"\\n'
      printf 'ln -s "../../pool/\\1.sig" "\\4/\\5/\\1.sig",\n'
      printf '  '"'"' "%s/mv"\n' \
        "${perma_tmp_dir}"
      printf '  echo quit\n'
      printf '} | failsafe_sftp\n'
    } \
    | intent_something "${no_action_flag}"

    # and push our local *.db.tar.gz via rsync
    while read -r arch repo; do
      {
        printf 'recompress_gz "%s"' \
          "${tmp_dir}"
        for suffix in '' '.old'; do
          printf ' "%s/dbs/%s/%s/%s."*".tar.gz%s"' \
            "${perma_tmp_dir}" \
            "${arch}" \
            "${repo}" \
            "${repo}" \
            "${suffix}"
        done
        printf '\n'
        printf 'failsafe_rsync '
        for suffix in 'db' 'files'; do
          printf '"%s/dbs/%s/%s/%s.%s."* ' \
            "${perma_tmp_dir}" \
            "${arch}" \
            "${repo}" \
            "${repo}" \
            "${suffix}"
        done
        printf ' "%s/%s/%s/"\n' \
          "${master_mirror_rsync_directory}" \
          "${arch}" \
          "${repo}"
      } \
      | intent_something "${no_action_flag}"
    done < \
      "${tmp_dir}/repositories"

    printf 'rm -rf --one-file-system "%s"\n' \
      "${perma_tmp_dir}" \
    | intent_something

    execute_all_intentions

  done

trigger_mirror_refreshs