path: root/bin/seed-build-list

#!/bin/sh

# shellcheck disable=SC2119,SC2120

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

# TODO: allow to filter by architecture

# TODO: -a|--auto schedules too much - or is the scheduling really /that/ broken?

# shellcheck disable=SC2016
usage() {
  >&2 echo ''
  >&2 echo 'seed-build-list [options]:'
  >&2 echo '  seed the build list from different sources'
  >&2 echo ''
  >&2 echo 'possible options:'
  >&2 echo '  -a|--auto:'
  >&2 echo '    Automatically reschedule packages which have run-time'
  >&2 echo '    dependencies that are not available from any real'
  >&2 echo '    repository and which cannot be replaced by another,'
  >&2 echo '    less stable version of the same package.'
  >&2 echo '  -f|--force'
  >&2 echo '    Do not exit if mysql_generate_package_metadata() failed.'
  >&2 echo '  -h|--help:'
  >&2 echo '    Show this help and exit.'
  >&2 echo '  -i|--ignore $pkgbase:'
  >&2 echo '    Do not update the given package.'
  >&2 echo '  -j|--jostle'
  >&2 echo '    Give new build assignments the highest priority.'
  >&2 echo '  -m|--mirror $url:'
  >&2 echo '    Schedule all packages, that are newer on the given'
  >&2 echo '    x86_64 mirror - except packages in'
  >&2 echo '    conf/seed-ignore-packages.'
  >&2 echo '  -n|--no-action:'
  >&2 echo '    Do not actually update build-list, just print it.'
  >&2 echo '  -p|--package $pkg_regex:'
  >&2 echo '    Reschedule packages with matching pkgname or pkgbase.'
  >&2 echo '    Note, that these packages must be known to the database.'
  >&2 echo '  -u|--undelete $url: '
  >&2 echo '    Schedule all former deletion-list packages which do not'
  >&2 echo '    belong on the deletion-list anymore (e.g. can be built)'
  >&2 echo '    and which are available on the given x86_64 mirror.'
  >&2 echo '  -w|--wait:'
  >&2 echo '    Wait for lock if necessary.'
  [ -z "$1" ] && exit 1 || exit "$1"
}

tmp_dir=$(mktemp -d 'tmp.seed-build-list.XXXXXXXXXX' --tmpdir)
# shellcheck disable=SC2064
trap "rm -rf --one-file-system '${tmp_dir:?}'" EXIT

eval set -- "$(
  getopt -o afhi:jm:np:u:w \
    --long auto \
    --long force \
    --long help \
    --long ignore: \
    --long jostle \
    --long mirror: \
    --long no-action \
    --long package: \
    --long undelete: \
    --long wait \
    -n "$(basename "$0")" -- "$@" || \
    echo usage
  )"

auto=false
ignore_mysql_generate_package_metadata_errors=false
jostle=false
update=true
wait_for_lock='-n'

if [ -r "${base_dir}/conf/seed-ignore-packages" ]; then
  sed 's/^/2\t/' "${base_dir}/conf/seed-ignore-packages" >> \
    "${tmp_dir}/ignore-packages"
fi

while true
do
  case "$1" in
    -a|--auto)
      auto=true
    ;;
    -f|--force)
      ignore_mysql_generate_package_metadata_errors=true
    ;;
    -h|--help)
      usage 0
    ;;
    -i|--ignore)
      shift
      printf '3\t%s\n' "$1" >> \
        "${tmp_dir}/ignore-packages"
    ;;
    -j|--jostle)
      jostle=true
    ;;
    -m|--mirror)
      shift
      printf '%s\n' "$1" >> \
        "${tmp_dir}/mirrors"
    ;;
    -n|--no-action)
      update=false
    ;;
    -p|--package)
      shift
      printf '%s\n' "$1" >> \
        "${tmp_dir}/package-regexes"
    ;;
    -u|--undelete)
      shift
      printf '%s\n' "$1" >> \
        "${tmp_dir}/undelete-mirrors"
    ;;
    -w|--wait)
      wait_for_lock=''
    ;;
    --)
      shift
      break
    ;;
    *)
      >&2 printf 'Whoops, forgot to implement option "%s" internally.\n' \
        "$1"
      exit 42
    ;;
  esac
  shift
done

if [ $# -ne 0 ]; then
  usage 1
fi

if [ ! -s "${tmp_dir}/mirrors" ] && \
  [ ! -s "${tmp_dir}/package-regexes" ] && \
  [ ! -s "${tmp_dir}/undelete-mirrors" ] && \
  ! ${auto}; then
  # nothing to do
  >&2 echo 'No options given to do anything.'
  usage 0
  exit 0
fi

# get locks
if ${update}; then
  exec 9> "${sanity_check_lock_file}"
  if ! verbose_flock -s ${wait_for_lock} 9; then
    >&2 echo 'Cannot get sanity-check lock.'
    exit 1
  fi

  exec 8> "${build_list_lock_file}"
  if ! verbose_flock ${wait_for_lock} 8; then
    >&2 echo 'Cannot get build-list lock.'
    exit 1
  fi
fi

# shellcheck disable=SC2016
repos=$(
  {
    printf 'SELECT DISTINCT `repositories`.`name`'
    printf ' FROM `repositories`'
    # shellcheck disable=SC2154
    printf ' WHERE `repositories`.`stability`=%s;\n' \
      "${repository_stability_ids__stable}"
  } | \
    mysql_run_query
  printf '%s\n' 'multilib'
)

# TODO: translate lib32-* packages instead of ignoring them for -u and -m

# harvest pkgnames from mirror delta
if [ -s "${tmp_dir}/mirrors" ]; then
  {
    # theirs
    while read -r mirror; do
      if [ -z "${mirror}" ]; then
        continue
      fi
      for repo in ${repos}; do
        curl -sS "${mirror}/${repo}/os/x86_64/${repo}.db.tar.gz" | \
          tar -Oxz --wildcards '*/desc' | \
          sed '
            /^%FILENAME%$/!d
            N
            s/^.*\n//
            /^lib32-/d
            s/^\(.*-\)x86_64\(\.pkg\.tar\.xz\)$/\1i486\2\n\1i686\2\n\1pentium4\2/
          ' | \
          sed '
            s/^\(.*\)-\([^-]\+-[^-]\+\)-\([^-]\+\)$/theirs \2 \3 \1/
          '
      done
    done < \
      "${tmp_dir}/mirrors"
    # ours
    # shellcheck disable=SC2016
    {
      printf 'SELECT '
      mysql_package_name_query
      printf ' FROM `binary_packages`'
      mysql_join_binary_packages_architectures
      mysql_join_binary_packages_binary_packages_in_repositories
      printf ' WHERE NOT `binary_packages_in_repositories`.`is_to_be_deleted`'
    } | \
      mysql_run_query | \
      sed '
        s/^\(.*\)-\([^-]\+-[^-.]\+\)\(\.[^-.]\+\)\?-\([^-]\+\)$/ours \2 \4 \1/
        s/^\(.* \)any\(\.\S\+ \S\+\)$/\0\n\1i486\2\n\1i686\2\n\1pentium4\2/
      '
  } | \
    expand_version 2 | \
    sort -k3,4 -k2Vr,2 -k1,1 | \
    shrink_version 2 | \
    uniq -f2 | \
    sed -n '
      s/^theirs \(\S\+ \)\{2\}//
      T
      p
    ' | \
    if [ -r "${base_dir}/conf/seed-ignore-packages" ]; then
      grep -vxF "$(cat "${base_dir}/conf/seed-ignore-packages")"
    else
      cat
    fi | \
    sed 's/^/1\t/' | \
    sort -u >> \
    "${tmp_dir}/pkgnames"
fi

# harvest pkgnames from undelete-mirrors
if [ -s "${tmp_dir}/undelete-mirrors" ]; then
  while read -r mirror; do
    if [ -z "${mirror}" ]; then
      continue
    fi
    for repo in ${repos}; do
      curl -sS "${mirror}/${repo}/os/x86_64/${repo}.db.tar.gz" | \
        tar -Oxz --wildcards '*/desc' | \
        sed '
          /^%FILENAME%$/!d
          N
          s/^.*\n//
          /^lib32-/d
          s/^\(.*-\)x86_64\(\.pkg\.tar\.xz\)$/\1i486\2\n\1i686\2\n\1pentium4\2/
        ' | \
        sed '
          s/^\(.*\)\(-[^-]\+\)\{3\}$/\1/
        '
    done
  done < \
    "${tmp_dir}/undelete-mirrors" | \
    if [ -r "${base_dir}/conf/seed-ignore-packages" ]; then
      grep -vxF "$(cat "${base_dir}/conf/seed-ignore-packages")"
    else
      cat
    fi | \
    sed 's/^/2\t/' | \
    sort -u >> \
    "${tmp_dir}/pkgnames"
fi

# harvest from the package-regexes
if [ -s "${tmp_dir}/package-regexes" ]; then
  # shellcheck disable=SC2016
  {
    printf 'CREATE TEMPORARY TABLE `names` (`name` VARCHAR(64));\n'
    printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `names` (`name`);\n' \
      "${tmp_dir}/package-regexes"

    printf 'SELECT DISTINCT "pkgbase",`package_sources`.`pkgbase`,`upstream_repositories`.`name`'
    printf ' FROM `names`'
    printf ' JOIN `package_sources`'
    printf ' ON `package_sources`.`pkgbase` REGEXP `names`.`name`'
    mysql_join_package_sources_upstream_repositories
    printf ';\n'

    printf 'SELECT DISTINCT "pkgname",`binary_packages`.`pkgname`'
    printf ' FROM `names`'
    printf ' JOIN `binary_packages`'
    printf ' ON `binary_packages`.`pkgname` REGEXP `names`.`name`;\n'
  } | \
    mysql_run_query | \
    sed -n '
      /^pkgbase\s/ {
        s/^\S\+\s/3\t/
        w /dev/stdout
        d
      }
      /^pkgname\s/ {
        s/^\S\+\s/3\t/
        w /dev/stderr
        d
      }
    ' \
    >>"${tmp_dir}/pkgbases" \
    2>>"${tmp_dir}/pkgnames"
fi

# shellcheck disable=SC2016
{
  # create pkgbases to given pkgnames
  if [ -s "${tmp_dir}/pkgnames" ]; then
    printf 'CREATE TEMPORARY TABLE `pkgnames` ('
      printf '`priority` SMALLINT,'
      printf '`pkgname` VARCHAR(64),'
      printf 'UNIQUE KEY `pkgname`(`pkgname`)'
    printf ');\n'
    printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `pkgnames` (`priority`,`pkgname`);\n' \
      "${tmp_dir}/pkgnames"

    printf 'SELECT DISTINCT "pkgbase",`pkgnames`.`priority`,`package_sources`.`pkgbase`,`upstream_repositories`.`name`'
    printf ' FROM `pkgnames`'
    printf ' JOIN `binary_packages`'
    printf ' ON `binary_packages`.`pkgname`=`pkgnames`.`pkgname`'
    mysql_join_binary_packages_build_assignments
    mysql_join_build_assignments_package_sources
    mysql_join_package_sources_upstream_repositories
    printf ';\n'

    printf 'SELECT DISTINCT "pkgname",`pkgnames`.`priority`,`pkgnames`.`pkgname`'
    printf ' FROM `package_sources`'
    mysql_join_package_sources_upstream_repositories
    mysql_join_package_sources_build_assignments
    mysql_join_build_assignments_binary_packages
    printf ' RIGHT JOIN `pkgnames`'
    printf ' ON `pkgnames`.`pkgname`=`binary_packages`.`pkgname`'
    printf ' WHERE `package_sources`.`pkgbase` IS NULL;\n'

    printf 'DROP TEMPORARY TABLE `pkgnames`;\n'
  fi
  # auto-detect pkgbases
  if ${auto}; then
    # schedule any package, that:
    #  1) is not on the build-list currently and
    #  2) has some dependency which is not provided by any package which
    #   a) is also on the build-list or
    #   b) is the least stable package with that name and for that architecture
    printf 'CREATE TEMPORARY TABLE `least_stable_bp%s`(`id` BIGINT, `is_on_build_list` BIT(1), PRIMARY KEY `id`(`id`));\n' \
      '' '_copy'
    # add least-stable built packages
    printf 'INSERT IGNORE INTO `least_stable_bp`(`id`,`is_on_build_list`)'
    printf ' SELECT `binary_packages`.`id`,'
    printf ' `bl_bpir`.`id` IS NOT NULL'
    printf ' FROM `binary_packages`'
    mysql_join_binary_packages_binary_packages_in_repositories
    mysql_join_binary_packages_in_repositories_repositories
    printf ' JOIN ('
      printf 'SELECT `binary_packages`.`pkgname`,'
      printf '`binary_packages`.`architecture`,'
      # TODO: this might become wrong if we ever change the order of the
      # rows in repository_stabilities -- we might want to refer to
      # repository_stability_relations here somehow ...
      printf 'MAX(`repositories`.`stability`) AS `stability`'
      printf ' FROM `binary_packages`'
      mysql_join_binary_packages_binary_packages_in_repositories
      mysql_join_binary_packages_in_repositories_repositories
      printf ' WHERE `repositories`.`is_on_master_mirror`'
      printf ' AND `repositories`.`name`!="build-support"'
      printf ' GROUP BY CONCAT('
        printf '`binary_packages`.`pkgname`,"-",`binary_packages`.`architecture`'
      printf ')'
    printf ') AS `sub_q`'
    printf ' ON `sub_q`.`pkgname`=`binary_packages`.`pkgname`'
    printf ' AND `sub_q`.`architecture`=`binary_packages`.`architecture`'
    printf ' AND `sub_q`.`stability`=`repositories`.`stability`'
    printf ' LEFT JOIN ('
      printf '`binary_packages` AS `bl_bp`'
      mysql_join_binary_packages_binary_packages_in_repositories 'bl_bp' 'bl_bpir'
      printf ' AND `bl_bpir`.`repository`=%s' \
        "${repository_ids__any_build_list}"
    printf ')'
    printf ' ON `bl_bp`.`pkgname`=`binary_packages`.`pkgname`'
    printf ' AND ('
      printf '`bl_bp`.`architecture`=`binary_packages`.`architecture`'
      printf ' OR `bl_bp`.`architecture`=%s' \
        "${architecture_ids__any}"
      printf ' OR `binary_packages`.`architecture`=%s' \
        "${architecture_ids__any}"
    printf ');\n'

    # add all build-list packages
    printf 'INSERT IGNORE INTO `least_stable_bp`(`id`,`is_on_build_list`)'
    printf ' SELECT `binary_packages_in_repositories`.`package`,1'
    printf ' FROM `binary_packages_in_repositories`'
    printf ' WHERE `binary_packages_in_repositories`.`repository`=%s;\n' \
      "${repository_ids__any_build_list}"

    # this copy shall hold the install-target-providers
    printf 'INSERT IGNORE INTO `least_stable_bp_copy`(`id`,`is_on_build_list`)'
    printf ' SELECT `least_stable_bp`.`id`,'
    printf '`least_stable_bp`.`is_on_build_list`'
    printf ' FROM `least_stable_bp`;\n'

    # remove packages that should never be rescheduled
    printf 'DELETE `least_stable_bp`'
    printf ' FROM `least_stable_bp`'
    printf ' JOIN `binary_packages`'
    printf ' ON `binary_packages`.`id`=`least_stable_bp`.`id`'
    # 1)
    printf ' WHERE `least_stable_bp`.`is_on_build_list`'
    # TODO: care about i486 and pentium4 and any here, too - if it is
    # complete enough
    # shellcheck disable=SC2154
    printf ' OR `binary_packages`.`architecture`!=%s;\n' \
      "${architecture_ids__i686}"

    printf 'SELECT DISTINCT "pkgbase",1,`package_sources`.`pkgbase`,`upstream_repositories`.`name`'
    printf ' FROM `least_stable_bp`'
    printf ' JOIN `binary_packages`'
    printf ' ON `least_stable_bp`.`id`=`binary_packages`.`id`'
    mysql_join_binary_packages_dependencies
    mysql_join_dependencies_dependency_types
    printf ' AND `dependency_types`.`relevant_for_binary_packages`'
    mysql_join_dependencies_versions '' 'd_v'
    mysql_join_binary_packages_build_assignments
    mysql_join_build_assignments_package_sources
    mysql_join_package_sources_upstream_repositories
    # some dependencies are not provided by the least stable packages
    printf ' WHERE NOT EXISTS ('
      printf 'SELECT 1'
      printf ' FROM `least_stable_bp_copy`'
      printf ' JOIN `binary_packages` AS `itp_bp`'
      printf ' ON `least_stable_bp_copy`.`id`=`itp_bp`.`id`'
      mysql_join_binary_packages_install_target_providers 'itp_bp'
      mysql_join_install_target_providers_versions '' 'itp_v'
      # must provide the right install_target
      printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
      # must provide the right version
      printf ' AND ('
      mysql_query_ordering_correct \
        '`d_v`.`order`' \
        '`itp_v`.`order`' \
        '`dependencies`.`version_relation`'
      # or be on the build-list
      printf ' OR `least_stable_bp_copy`.`is_on_build_list`'
      # must provide the right architecture
      printf ') AND ('
        printf '`itp_bp`.`architecture`=`binary_packages`.`architecture`'
        printf ' OR `itp_bp`.`architecture`=%s' \
          "${architecture_ids__any}"
        printf ' OR `binary_packages`.`architecture`=%s' \
          "${architecture_ids__any}"
      printf ')'
    printf ');\n'
    printf 'DROP TEMPORARY TABLE `least_stable_bp%s`;\n' \
      '' '_copy'
  fi
} | \
  mysql_run_query | \
  sed -n '
    /^pkgbase\s/ {
      s/^\S\+\s//
      w /dev/stderr
      d
    }
    /^pkgname\s/ {
      s/^\S\+\s//
      w /dev/stdout
      d
    }
  ' \
  2>>"${tmp_dir}/pkgbases" \
  | sponge "${tmp_dir}/pkgnames"

if [ ! -s "${tmp_dir}/pkgbases" ] && \
  [ ! -s "${tmp_dir}/pkgnames" ]; then
  >&2 echo 'No packages matched.'
  exit
fi

for s in "${tmp_dir}/pkgbases" "${tmp_dir}/pkgnames"; do
  if [ -s "${s}" ]; then
    sort -u "${s}" | \
      sponge "${s}"
  fi
done

# pkgnames -> pkgbases (with help from upstream)
if [ -s "${tmp_dir}/pkgnames" ]; then
  while read -r priority pkgname; do
    content=$(
      curl -Ss 'https://www.archlinux.org/packages/search/json/?name='"${pkgname}" | \
        tr ',' '\n'
    )
    repo=$(
      printf '%s\n' "${content}" | \
        sed -n '
          s/^\s*"repo"\s*:\s*"//
          T
          s/".*$//
          T
          p
        '
    )
    pkgbase=$(
      printf '%s\n' "${content}" | \
        sed -n '
          s/^\s*"pkgbase"\s*:\s*"//
          T
          s/".*$//
          T
          p
        '
    )
    if [ -z "${pkgbase}" ] || [ -z "${repo}" ]; then
      printf '%s\t%s\n' "${priority}" "${pkgname}"
      continue
    fi
    printf '%s\t%s\t%s\n' \
      "${priority}" \
      "${pkgbase}" \
      "${repo}" >> \
      "${tmp_dir}/pkgbases"
  done < \
    "${tmp_dir}/pkgnames" | \
    sponge "${tmp_dir}/pkgnames"
fi

if [ -s "${tmp_dir}/pkgnames" ]; then
  >&2 echo 'Could not find some packages upstream:'
  >&2 cat "${tmp_dir}/pkgnames"
  exit 2
fi

# now we (re)schedule all pkgbases which are:
#  - not explicitely ignored (priority 3)             <- explicitely scheduled
#  - not explicitely/implicitely ignored, but not currently available
#    (priority 2) <- from undelete-mirror-delta
#  - neither ignored nor to-be-deleted (priority 1)   <- from mirror-delta or auto-rebuild

# shellcheck disable=SC2016
{
  printf 'CREATE TEMPORARY TABLE `ignore_packages` ('
    printf '`priority` SMALLINT,'
    printf '`pkgbase` VARCHAR(64),'
    printf 'UNIQUE KEY `content`(`priority`,`pkgbase`)'
  printf ');\n'
  printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `ignore_packages`(`priority`,`pkgbase`);\n' \
    "${tmp_dir}/ignore-packages"

  printf 'CREATE TEMPORARY TABLE `pkgbases` ('
    printf '`priority` SMALLINT,'
    printf '`pkgbase` VARCHAR(64),'
    printf '`repo` VARCHAR(64),'
    printf 'UNIQUE KEY `pkgbase`(`pkgbase`)'
  printf ');\n'
  printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `pkgbases`(`priority`,`pkgbase`,`repo`);\n' \
    "${tmp_dir}/pkgbases"

  printf 'INSERT IGNORE INTO `ignore_packages`(`priority`,`pkgbase`)'
  printf ' SELECT 2,`package_sources`.`pkgbase`'
  printf ' FROM `package_sources`'
  mysql_join_package_sources_build_assignments
  mysql_join_build_assignments_binary_packages
  printf ' WHERE `build_assignments`.`is_black_listed` IS NOT NULL'
  printf ' OR `package_sources`.`pkgbase` LIKE "lib32-%%"'
  printf ' OR EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `dependencies`'
    mysql_join_dependencies_dependency_types
    printf ' AND ('
      printf '`dependency_types`.`relevant_for_building`'
      printf ' OR `dependency_types`.`relevant_for_binary_packages`'
    printf ')'
    mysql_join_dependencies_install_target_providers_with_versions '' 'it_dummy'
    printf ' WHERE `dependencies`.`dependent`=`binary_packages`.`id`'
    printf ' AND NOT EXISTS ('
      printf 'SELECT 1'
      printf ' FROM `install_target_providers`'
      mysql_join_install_target_providers_binary_packages '' 'it_bp'
      mysql_join_binary_packages_binary_packages_in_repositories 'it_bp' 'it_bpir'
      printf ' WHERE NOT `it_bpir`.`is_to_be_deleted`'
      printf ' AND `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
    printf ')'
  printf ');\n'

  printf 'INSERT IGNORE INTO `ignore_packages`(`priority`,`pkgbase`)'
  printf ' SELECT 1,`package_sources`.`pkgbase`'
  printf ' FROM `package_sources`'
  mysql_join_package_sources_build_assignments
  mysql_join_build_assignments_binary_packages
  mysql_join_binary_packages_binary_packages_in_repositories
  printf ' WHERE `binary_packages_in_repositories`.`is_to_be_deleted`'
  printf ';\n'

  printf 'SELECT '
  printf '`pkgbases`.`pkgbase`,'
  printf '`git_repositories`.`head`,'
  printf '('
    printf 'SELECT `al32`.`head`'
    printf ' FROM `git_repositories` AS `al32`'
    printf ' WHERE `al32`.`name`="archlinux32"'
  printf '),'
  printf '`pkgbases`.`repo`'
  printf ' FROM `pkgbases`'
  printf ' JOIN `upstream_repositories`'
  printf ' ON `upstream_repositories`.`name`=`pkgbases`.`repo`'
  mysql_join_upstream_repositories_git_repositories
  printf ' WHERE NOT EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `ignore_packages`'
    printf ' WHERE `ignore_packages`.`pkgbase`=`pkgbases`.`pkgbase`'
    printf ' AND `ignore_packages`.`priority`>=`pkgbases`.`priority`'
  printf ') AND ('
    printf '`pkgbases`.`priority`!=2'
    printf ' OR NOT EXISTS ('
      printf 'SELECT 1'
      printf ' FROM `package_sources`'
      mysql_join_package_sources_build_assignments
      mysql_join_build_assignments_binary_packages
      mysql_join_binary_packages_binary_packages_in_repositories
      printf ' AND NOT `binary_packages_in_repositories`.`is_to_be_deleted`'
      mysql_join_binary_packages_in_repositories_repositories
      printf ' AND ('
        printf '`repositories`.`is_on_master_mirror`'
        printf ' OR `repositories`.`name`="build-list"'
      printf ') WHERE `package_sources`.`pkgbase`=`pkgbases`.`pkgbase`'
    printf ')'
  printf ');\n'
} | \
  mysql_run_query | \
  tr '\t' ' ' | \
  sort -u > \
  "${tmp_dir}/update-list"

if ${update}; then
  while read -r pkgbase git_rev mod_git_rev repo; do
    success=false
    # shellcheck disable=SC2154
    for gr_r in \
      "${git_rev}:${repo}" \
      "${repo_heads__packages}:core" \
      "${repo_heads__packages}:extra" \
      "${repo_heads__community}:community"; do
      printf '%s ' "${pkgbase}" "${gr_r%:*}" "${mod_git_rev}" "${gr_r#*:}" | \
        sed 's/ $/\n/'
      if mysql_generate_package_metadata "${repository_ids__any_build_list}" "${pkgbase}" "${gr_r%:*}" "${mod_git_rev}" "${gr_r#*:}"; then
        success=true
        break
      fi
    done
    if ! ${success} && \
      ! ${ignore_mysql_generate_package_metadata_errors}; then
      exit 2
    fi
  done < \
    "${tmp_dir}/update-list"

  cut -d' ' -f1 < \
    "${tmp_dir}/update-list" | \
    sort -u | \
    sponge "${tmp_dir}/update-list"

  # shellcheck disable=SC2016
  {
    printf 'CREATE TEMPORARY TABLE `pkgbases` ('
      printf '`pkgbase` VARCHAR (64),'
      printf 'UNIQUE KEY `pkgbase`(`pkgbase`)'
    printf ');\n'
    printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `pkgbases`(`pkgbase`);\n' \
      "${tmp_dir}/update-list"

    printf 'DELETE `d_bpir`'
    printf ' FROM `pkgbases`'
    # the package on the deletion-list ...
    printf ' JOIN `package_sources` AS `d_ps`'
    printf ' ON `pkgbases`.`pkgbase`=`d_ps`.`pkgbase`'
    mysql_join_package_sources_build_assignments 'd_ps' 'd_ba'
    mysql_join_build_assignments_binary_packages 'd_ba' 'd_bp'
    mysql_join_binary_packages_binary_packages_in_repositories 'd_bp' 'd_bpir'
    mysql_join_binary_packages_in_repositories_repositories 'd_bpir' 'd_r'
    # shellcheck disable=SC2154
    printf ' AND `d_r`.`stability`=%s' \
      "${repository_stability_ids__forbidden}"
    # ... needs a counter-part on the build-list (which was just created)
    printf ' JOIN `package_sources` AS `a_ps`'
    printf ' ON `d_ps`.`pkgbase`=`a_ps`.`pkgbase`'
    mysql_join_package_sources_build_assignments 'a_ps' 'a_ba'
    mysql_join_build_assignments_binary_packages 'a_ba' 'a_bp'
    printf ' JOIN `architecture_compatibilities` AS `ac`'
    printf ' ON `ac`.`built_for`=`a_bp`.`architecture`'
    printf ' AND `ac`.`runs_on`=`d_r`.`architecture`'
    printf ' AND `ac`.`fully_compatible`'
    mysql_join_binary_packages_binary_packages_in_repositories 'a_bp' 'a_bpir'
    printf ' AND `a_bpir`.`repository`=%s;\n' \
      "${repository_ids__any_build_list}"

    if ${jostle}; then
      printf 'SET @`max_priority`=('
        printf 'SELECT'
        printf ' MAX(`build_assignments`.`priority`)+1'
        printf ' FROM `build_assignments`'
      printf ');\n'
      printf 'UPDATE `build_assignments`'
      mysql_join_build_assignments_package_sources
      printf ' JOIN `pkgbases`'
      printf ' ON `pkgbases`.`pkgbase`=`package_sources`.`pkgbase`'
      printf ' SET `build_assignments`.`priority`=@`max_priority`'
      printf ';\n'
    fi
  } | \
    mysql_run_query

  mysql_cleanup

  mysql_sort_versions

  mysql_find_build_assignment_loops

  # remove duplicate binary_packages from "build-list"
  mysql_query_remove_old_binary_packages_from_build_list | \
    mysql_run_query
else
  cat "${tmp_dir}/update-list"
fi