bin/seed-build-list -> bin/schedule-for-rebuild

* adapt name to what it can do
* building completely new packages is not needed anymore
* building completely new packages was complex anyways

1 files changed, 599 insertions, 0 deletions

diff --git a/bin/schedule-for-rebuild b/bin/schedule-for-rebuild
new file mode 100755
index 0000000..086f578
--- /dev/null
+++ b/bin/schedule-for-rebuild
@@ -0,0 +1,599 @@
+#!/bin/sh
+
+# shellcheck disable=SC2119,SC2120
+
+# schedule selected packages for a rebuild
+#
+# This uses the following schedule:
+# - find scheduleworthy packages by pkgname/pkgbase (broken
+#   dependencies, explicite regex) and their architecture
+# - remove all ignored packages (seed-ignore, explicitely ignored,
+#   blacklist)
+# - run mysql_generate_package_metadata on each of them once (ignoring
+#   the architecture for now)
+
+# shellcheck source=../lib/load-configuration
+. "${0%/*}/../lib/load-configuration"
+
+# TODO: allow to reschedule filtered by architecture
+
+# TODO: remove hard-coded package suffixes
+
+# TODO: -a reschedules too many packages (for example, packages, that have just been rescheduled in a previous run)
+
+# TODO: -a and -p leave the buildmaster in an unclean state
+
+# shellcheck disable=SC2016
+usage() {
+  >&2 echo ''
+  >&2 echo 'schedule-for-rebuild [options]:'
+  >&2 echo '  schedule selected packages for a rebuild'
+  >&2 echo ''
+  >&2 echo 'possible options:'
+  >&2 echo '  -a|--auto:'
+  >&2 echo '    Automatically schedule 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|$arch/$pkgbase:'
+  >&2 echo '    Never schedule the given package (for the given $arch).'
+  >&2 echo '  -j|--jostle'
+  >&2 echo '    Give new build assignments the highest priority.'
+  >&2 echo '  -n|--no-action:'
+  >&2 echo '    Do not actually schedule anything, just print it.'
+  >&2 echo '  -p|--package $pkg_regex:'
+  >&2 echo '    Schedule packages with matching pkgname or pkgbase.'
+  >&2 echo '  -s|--skip-arch $arch:'
+  >&2 echo '    Ignore packages, that are outdated on $arch only (-a).'
+  >&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:jnp:s:w \
+    --long auto \
+    --long force \
+    --long help \
+    --long ignore: \
+    --long jostle \
+    --long no-action \
+    --long package: \
+    --long skip-arch: \
+    --long wait \
+    -n "$(basename "$0")" -- "$@" || \
+    echo usage
+  )"
+
+auto=false
+ignore_mysql_generate_package_metadata_errors=false
+jostle=false
+update=true
+wait_for_lock='-n'
+
+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
+      if [ -z "${1%%*/*}" ]; then
+        printf '%s\n' "$1" \
+        | tr '/' '\t'
+      else
+        printf 'any\t%s\n' "$1"
+      fi \
+      >> "${tmp_dir}/ignore-packages"
+    ;;
+    -j|--jostle)
+      jostle=true
+    ;;
+    -n|--no-action)
+      update=false
+    ;;
+    -p|--package)
+      shift
+      printf '%s\n' "$1" >> \
+        "${tmp_dir}/package-regexes"
+    ;;
+    -s|--skip-arch)
+      shift
+      printf '%s\n' "$1" >> \
+        "${tmp_dir}/skip-archs"
+    ;;
+    -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}/package-regexes" ] && \
+  ! ${auto}; then
+  # nothing to do
+  >&2 echo 'No options given to do anything.'
+  usage 0
+  exit 0
+fi
+
+if [ -s "${tmp_dir}/skip-archs" ] && \
+  ! ${auto}; then
+  >&2 echo 'Invalid combination of options: -s|--skip-archs requires -a|--auto.'
+  usage 2
+  exit 2
+fi
+
+# get locks
+if ${update}; then
+  exec 9> "${sanity_check_lock_file}"
+  # shellcheck disable=SC2086
+  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}"
+  # shellcheck disable=SC2086
+  if ! verbose_flock ${wait_for_lock} 8; then
+    >&2 echo 'Cannot get build-list lock.'
+    exit 1
+  fi
+fi
+
+if [ -r "${conf_dir}/seed-ignore-packages" ]; then
+  sed '
+    s@/@\t@
+    t
+    s/^/any\t/
+  ' "${conf_dir}/seed-ignore-packages" >> \
+    "${tmp_dir}/ignore-packages"
+fi
+
+# shellcheck disable=SC2154
+git -C "${repo_paths__archlinux32}" archive "${repo_heads__archlinux32}" -- blacklist \
+| tar -t \
+| sed '
+  s@^blacklist/\([^/]\+\)/[^/]\+/\([^/]\+\)$@\1\t\2@
+  t
+  d
+' \
+| tee "${tmp_dir}/blacklist" \
+>> "${tmp_dir}/ignore-packages"
+
+for list in 'ignore-packages' 'blacklist'; do
+  expand_blacklist_architectures "${tmp_dir}/architecture-compatibilities" \
+  < "${tmp_dir}/${list}" \
+  | sponge "${tmp_dir}/${list}"
+done
+
+touch "${tmp_dir}/skip-archs"
+
+# 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 '
+      printf '"any",'
+      printf '`package_sources`.`pkgbase`,'
+      printf '`package_sources`.`git_revision`,'
+      printf '`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 '
+      printf '"any",'
+      printf '`package_sources`.`pkgbase`,'
+      printf '`package_sources`.`git_revision`,'
+      printf '`upstream_repositories`.`name`'
+    printf ' FROM `binary_packages`'
+    mysql_join_binary_packges_build_assignments
+    mysql_join_build_assignments_package_sources
+    mysql_join_package_sources_upstream_repositories
+    printf ' JOIN `names`'
+    printf ' ON `binary_packages`.`pkgname` REGEXP `names`.`name`'
+    printf ' OR `package_sources`.`pkgbase` REGEXP `names`.`name`'
+    printf ' LEFT JOIN `versions`'
+    printf ' ON `versions`.`epoch`=`binary_packages`.`epoch`'
+    printf ' AND `versions`.`version`=`binary_packages`.`pkgver`'
+    printf ' WHERE NOT EXISTS ('
+      printf 'SELECT 1'
+      printf ' FROM `binary_packages` AS `newer_bp`'
+      printf ' JOIN `versions` AS `newer_v`'
+      printf ' ON `newer_v`.`epoch`=`newer_bp`.`epoch`'
+      printf ' AND `newer_v`.`version`=`newer_bp`.`pkgver`'
+      printf ' WHERE (`newer_v`.`order`>`versions`.`order`'
+        printf ' OR ('
+          printf '`newer_v`.`order`=`versions`.`order`'
+          printf ' AND `newer_bp`.`pkgrel`>`binary_packages`.`pkgrel`'
+          # sub_pkgrel does not need to be compared, as it is only
+          # relevant for binary_packages with the same build_assignment
+        printf ')'
+      printf ')'
+      printf ' AND `binary_packages`.`%s`=`newer_bp`.`%s`' \
+        'pkgname' 'pkgname' \
+        'architecture' 'architecture'
+    printf ');\n'
+  } | \
+    mysql_run_query | \
+    tr '\t' ' ' | \
+    sort -u \
+    >>"${tmp_dir}/pkgbases"
+fi
+
+# auto-detect pkgbases
+if ${auto}; then
+  # shellcheck disable=SC2016
+  {
+    printf 'CREATE TEMPORARY TABLE `relevant_archs`(`id` SMALLINT, PRIMARY KEY `id`(`id`));\n'
+    printf 'INSERT INTO `relevant_archs`(`id`)'
+    printf ' SELECT `architectures`.`id`'
+    printf ' FROM `architectures`'
+    if [ -s "${tmp_dir}/skip-archs" ]; then
+      printf ' WHERE `architectures`.`name` NOT IN ('
+        base64_encode_each \
+        <"${tmp_dir}/skip-archs" \
+        | sed '
+          s/^.*$/from_base64("\0")/
+          $! s/$/,/
+        ' \
+        | tr -d '\n'
+      printf ')'
+    fi
+    printf ';\n'
+
+    # schedule any package, that:
+    #  1) is not on the build-list currently and
+    #  2) is not in ignore-packages and
+    #  3) is not on the deletion-list or to-be-deleted and
+    #  4) 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 `relevant_archs`'
+    printf ' ON `relevant_archs`.`id`=`repositories`.`architecture`'
+    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`,"-",'
+        printf '`binary_packages`.`architecture`,"-",'
+        printf '`repositories`.`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) not on the build-list
+    printf ' WHERE `least_stable_bp`.`is_on_build_list`;'
+
+    printf 'CREATE TEMPORARY TABLE `ignore_packages`('
+      printf '`architecture` VARCHAR(16),'
+      printf '`pkgbase` VARCHAR(64),'
+      printf 'UNIQUE KEY `content`(`architecture`,`pkgbase`)'
+    printf ');\n'
+    if [ -r "${tmp_dir}/ignore-packages" ]; then
+      printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `ignore_packages` COLUMNS TERMINATED BY " ";\n' \
+        "${tmp_dir}/ignore-packages"
+    fi
+
+    printf 'SELECT DISTINCT '
+      printf '`ba_a`.`name`,'
+      printf '`package_sources`.`pkgbase`,'
+      printf '`package_sources`.`git_revision`,'
+      printf '`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_architectures '' 'ba_a'
+    mysql_join_build_assignments_package_sources
+    mysql_join_package_sources_upstream_repositories
+    # 2) not ignored
+    printf ' WHERE NOT EXISTS ('
+      printf 'SELECT 1'
+      printf ' FROM `ignore_packages`'
+      printf ' WHERE ('
+        printf '`ignore_packages`.`pkgbase`=`binary_packages`.`pkgname`'
+        printf ' OR `ignore_packages`.`pkgbase`=`package_sources`.`pkgbase`'
+      printf ') AND `ba_a`.`name`=`ignore_packages`.`architecture`'
+    # 3) not on the deletion-list or to-be-deleted
+    printf ') AND NOT EXISTS ('
+      printf 'SELECT 1'
+      printf ' FROM `binary_packages` AS `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'
+      printf ' AND ('
+        printf '`d_r`.`stability`=%s' \
+          "${repository_stability_ids__forbidden}"
+        printf ' OR `d_bpir`.`is_to_be_deleted`'
+      printf ') WHERE `d_bp`.`pkgname`=`binary_packages`.`pkgname`'
+      printf ' AND `ba_a`.`name`=`d_r`.`architecture`'
+    # 4) some dependencies are not provided by the least stable packages
+    printf ') AND 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'
+    printf 'DROP TEMPORARY TABLE `ignore_packages`;\n'
+  } \
+  | mysql_run_query \
+  | tr '\t' ' ' \
+  | sort -u \
+  >>"${tmp_dir}/pkgbases"
+fi
+
+if [ ! -s "${tmp_dir}/pkgbases" ]; then
+  >&2 echo 'No packages matched.'
+  exit
+fi
+
+sort -u "${tmp_dir}/pkgbases" \
+| sponge "${tmp_dir}/pkgbases"
+
+# get the current HEADs
+
+if ${update}; then
+  cut -d' ' -f 2,3,4 "${tmp_dir}/pkgbases" \
+  | sort -u \
+  | while read -r pkgbase git_rev repo; do
+    success=false
+    # shellcheck disable=SC2154
+    printf '%s ' "${pkgbase}" "${git_rev}" "${repo_heads__archlinux32}" "${repo}" \
+    | sed 's/ $/\n/'
+    if ! mysql_generate_package_metadata "${repository_ids__any_build_list}" "${pkgbase}" "${git_rev}" "${repo_heads__archlinux32}" "${repo}" && \
+      ! ${ignore_mysql_generate_package_metadata_errors}; then
+      exit 2
+    fi
+  done
+
+  cut -d' ' -f2 "${tmp_dir}/pkgbases" \
+  | sort -u \
+  | sponge "${tmp_dir}/pkgbases"
+
+  # 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}/pkgbases"
+
+# TODO: this seems to be broken (deletes too few packages) - still valid
+# after implementing the correct blacklisting below?
+    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
+
+    printf 'CREATE TEMPORARY TABLE `blacklist` ('
+      printf '`architecture` VARCHAR (16),'
+      printf '`pkgbase` VARCHAR (64),'
+      printf 'UNIQUE KEY `content`(`architecture`,`pkgbase`)'
+    printf ');\n'
+    printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `blacklist` COLUMNS TERMINATED BY " " (`architecture`,`pkgbase`);\n' \
+      "${tmp_dir}/blacklist"
+
+    printf 'INSERT IGNORE INTO `binary_packages_in_repositories` ('
+      printf '`package`,'
+      printf '`repository`,'
+      printf '`is_to_be_deleted`'
+    printf ')'
+    printf ' SELECT'
+    printf ' `binary_packages`.`id`,'
+    printf '`dl_r`.`id`,'
+    printf '1'
+    printf ' FROM `blacklist`'
+    printf ' JOIN `package_sources`'
+    printf ' ON `blacklist`.`pkgbase`=`package_sources`.`pkgbase`'
+    mysql_join_package_sources_build_assignments
+    mysql_join_build_assignments_architectures
+    printf ' AND `architectures`.`name`=`blacklist`.`architecture`'
+    mysql_join_build_assignments_binary_packages
+    mysql_join_binary_packages_binary_packages_in_repositories
+    printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
+      "${repository_ids__any_build_list}"
+    printf ' JOIN `architecture_compatibilities` AS `ac`'
+    printf ' ON `ac`.`built_for`=`binary_packages`.`architecture`'
+    printf ' AND `ac`.`fully_compatible`'
+    printf ' JOIN `repositories` AS `dl_r`'
+    printf ' ON `ac`.`runs_on`=`dl_r`.`architecture`'
+    # shellcheck disable=SC2154
+    printf ' AND `dl_r`.`stability`=%s;\n' \
+      "${repository_stability_ids__forbidden}"
+
+    printf 'DELETE `binary_packages_in_repositories`'
+    printf ' FROM `blacklist`'
+    printf ' JOIN `package_sources`'
+    printf ' ON `blacklist`.`pkgbase`=`package_sources`.`pkgbase`'
+    mysql_join_package_sources_build_assignments
+    mysql_join_build_assignments_architectures
+    printf ' AND `architectures`.`name`=`blacklist`.`architecture`'
+    mysql_join_build_assignments_binary_packages
+    mysql_join_binary_packages_binary_packages_in_repositories
+    printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
+      "${repository_ids__any_build_list}"
+    printf ' AND ('
+      printf '`binary_packages`.`architecture`=`architectures`.`id`'
+      printf ' OR `architectures`.`id`=%s' \
+        "${architecture_ids__any}"
+    printf ');\n'
+
+    # delete deletion-list part of packages which are on the
+    # deletion-list and the build-list
+    mysql_query_delete_packages \
+      '1'"$(
+        printf ' JOIN `binary_packages` AS `j_bp`'
+        printf ' ON `j_bp`.`pkgname`=`binary_packages`.`pkgname`'
+        printf ' JOIN `architecture_compatibilities` AS `ac`'
+        printf ' ON `ac`.`fully_compatible`'
+        printf ' AND `ac`.`built_for`=`binary_packages`.`architecture`'
+        printf ' JOIN `architecture_compatibilities` AS `ac_j`'
+        printf ' ON `ac_j`.`fully_compatible`'
+        printf ' AND `ac_j`.`built_for`=`j_bp`.`architecture`'
+        printf ' AND `ac_j`.`runs_on`=`ac`.`runs_on`'
+        mysql_join_binary_packages_binary_packages_in_repositories 'j_bp' 'j_bpir'
+      )"' AND `j_bpir`.`repository`='"${repository_ids__any_build_list}" \
+      '`repositories`.`stability`='"${repository_stability_ids__forbidden}"
+  } \
+  | 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}/pkgbases"
+fi