path: root/bin/get-package-updates

#!/bin/sh

# check for packages that need to be built

# shellcheck disable=SC2119,SC2120

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

# TODO: Find out, why sometimes package updates are missed.

# TODO: correctly handle if pkgbase of a split package is renamed, e.g.:
# $a -> ($a,$b)  ==>  $b -> ($a,$b)

# TODO: keep database clean in case of abort

# TODO: Being on the deletion-list is not a reason not to be scheduled
# if a dependency formerly on the deletion-list is now being built.

# shellcheck disable=SC2016
usage() {
  >&2 echo ''
  >&2 echo 'get-package-updates: check for packages that need to be built,'
  >&2 echo ' and build a list in the proper build order'
  >&2 echo ''
  >&2 echo 'possible options:'
  >&2 echo '  -d|--date $datetime:'
  >&2 echo '                Pull latest commit before $datetime'
  >&2 echo '                (yyyy-mm-ddThh:mm:ss). Conflicts -n.'
  >&2 echo '  -h|--help:    Show this help and exit.'
  >&2 echo '  -n|--no-pull: Do not pull git repos, merely reorder build list.'
  >&2 echo '                Conflicts -d.'
  >&2 echo '  -w|--wait:    If necessary, wait for lock blocking.'
  >&2 echo '  -x|--test-exclusion $package:'
  >&2 echo '                Print additionally deleted/excluded packages if'
  >&2 echo '                "$package" would be black listed.'
  [ -z "$1" ] && exit 1 || exit "$1"
}

eval set -- "$(
  getopt -o d:hnwx: \
    --long date: \
    --long help \
    --long no-pull \
    --long test-exclusion: \
    --long wait \
    -n "$(basename "$0")" -- "$@" || \
  echo usage
)"

block_flag='-n'
date_time=''
test_exclusion=''
pull=true

while true
do
  case "$1" in
    -d|--date)
      shift
      date_time="$1"
    ;;
    -h|--help)
      usage 0
    ;;
    -n|--no-pull)
      pull=false
    ;;
    -w|--wait)
      block_flag=''
    ;;
    -x|--test-exclusion)
      shift
      if [ -n "${test_exclusion}" ]; then
        >&2 printf 'I already have --test-exclusion=%s and you gave me another one.\n' "${test_exclusion}"
        >&2 printf 'But I can only handle one exclusion test at a time.\n'
        exit 2
      fi
      test_exclusion="$1"
    ;;
    --)
      shift
      break
    ;;
    *)
      >&2 echo 'Whoops, forgot to implement option "'"$1"'" internally.'
      exit 42
    ;;
  esac
  shift
done

if [ $# -ne 0 ]; then
  >&2 echo 'Too many arguments.'
  usage
fi

if [ -n "${date_time}" ] && ! ${pull}; then
  >&2 printf -- '-d and -n are mutually exclusive.\n'
  usage
fi

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

# delete_package arch package
# mark $arch/$package for deletion
delete_package() {
  # TODO: Once we want to rely on the database for test_exclusion, we
  # need to run the command below unconditionally, but with some
  # changes, so we can easily revert.
  if [ -z "${test_exclusion}" ]; then
    # shellcheck disable=SC2016
    query_delete_packages=$(
      printf '`architectures` AS `d_a`'
      printf ' JOIN `architecture_compatibilities` AS `a_c`'
      printf ' ON `a_c`.`runs_on`=`d_a`.`id`'
      printf ' AND `d_a`.`name`=from_base64("%s")' \
        "$(printf '%s' "$1" | base64 -w0)"
      printf ' JOIN `build_assignments`'
      printf ' ON (`a_c`.`built_for`=`build_assignments`.`architecture`'
      # "any" references all architectures, but this is not represented
      # in `architecture_compatibilities`: If a package is not buildable
      # for "any", this means literally, that it is not buildable for
      # the _least_ architecture (e.g. it is not generic)
      printf ' OR `d_a`.`name`="any")'
      mysql_join_build_assignments_package_sources
      printf ' AND `package_sources`.`pkgbase`=from_base64("%s")' \
        "$(printf '%s' "$2" | base64 -w0)"
      mysql_join_build_assignments_binary_packages
      mysql_join_binary_packages_binary_packages_in_repositories
    )
    # shellcheck disable=SC2016
    {
      # packages from the build-list/to-be-decided go straight to the deletion-list
      # this happens in two steps, because we need to create one item per
      # target architecture
      printf 'INSERT IGNORE INTO `binary_packages_in_repositories` ('
        printf '`package`,'
        printf '`repository`,'
        printf '`last_moved`,'
        printf '`is_to_be_deleted`'
      printf ') SELECT'
      printf ' `binary_packages`.`id`,'
      printf '`d_r`.`id`,'
      printf 'NOW(),'
      printf '1'
      printf ' FROM'
      printf ' %s' "${query_delete_packages}"
      mysql_join_binary_packages_in_repositories_repositories
      # shellcheck disable=SC2154
      printf ' AND `repositories`.`stability` in (%s,%s)' \
        "${repository_stability_ids__unbuilt}" \
        "${repository_stability_ids__virtual}"
      mysql_join_build_assignments_architectures '' 'ba_a'
      printf ' JOIN `repositories` AS `d_r`'
      printf ' ON ('
        # arch-specific build_assignments must match exactly
        printf '`d_r`.`architecture`=`ba_a`.`id`'
        # "any" build_assignments build for all architectures
        printf ' OR `ba_a`.`name`="any"'
      printf ')'
      # shellcheck disable=SC2154
      printf ' AND `d_r`.`stability`=%s;\n' \
        "${repository_stability_ids__forbidden}"

      printf 'COMMIT;\n'

      printf 'DELETE `binary_packages_in_repositories`'
      printf ' FROM %s' "${query_delete_packages}"
      mysql_join_binary_packages_in_repositories_repositories
      # shellcheck disable=SC2154
      printf ' AND `repositories`.`stability` in (%s,%s);\n' \
        "${repository_stability_ids__unbuilt}" \
        "${repository_stability_ids__virtual}"

      printf 'COMMIT;\n'

      # other packages are marked as `is_to_be_deleted`
      printf 'UPDATE %s' "${query_delete_packages}"
      printf ' SET `binary_packages_in_repositories`.`is_to_be_deleted`=1;\n'
    } | \
      mysql_run_query
  fi
}

something_new=false

for repo in ${repo_names}; do
  eval repo_path='"${repo_paths__'"${repo}"'}"'
  # Update git repositories (official packages, community packages and the repository of package customizations).
  git -C "${repo_path}" remote update || \
    true
  # read previous git revision numbers from database.
  # shellcheck disable=SC2016
  eval "old_repo_revisions__${repo}='$(
    {
      printf 'SELECT `git_repositories`.`head` FROM `git_repositories`'
      printf ' WHERE `git_repositories`.`name`=from_base64("%s");\n' \
        "$(printf '%s' "${repo}" | base64 -w0)"
    } | \
      mysql_run_query
  )'"
  # determine new git revision
  if ${pull}; then
    if [ -z "${date_time}" ]; then
      eval "new_repo_revisions__${repo}='$(
        git -C "${repo_path}" rev-parse HEAD
      )'"
    else
      eval "new_repo_revisions__${repo}='$(
        git -C "${repo_path}" rev-list -n1 --until "${date_time}" HEAD
      )'"
    fi
  else
    eval 'new_repo_revisions__'"${repo}"'="${old_repo_revisions__'"${repo}"'}"'
  fi
  if ! eval '[ "${new_repo_revisions__'"${repo}"'}" = "${old_repo_revisions__'"${repo}"'}" ]'; then
    something_new=true
  fi
done

if ${pull} && \
  ! ${something_new}; then
  >&2 echo 'Nothing changed.'
  exit
fi

# Create a lock file for build list.

exec 9> "${build_list_lock_file}"
if ! verbose_flock ${block_flag} 9; then
  >&2 echo 'come back (shortly) later - I cannot lock build list.'
  exit
fi

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

cleanup() {
  mysql_cleanup
  rm -rf --one-file-system "${tmp_dir:?}"
}
tmp_dir=$(mktemp -d 'tmp.get-package-updates.XXXXXXXXXX' --tmpdir)

trap cleanup EXIT

# shellcheck disable=SC2119
mysql_cleanup

echo 'Check modified packages from the last update, and put them to the build list.'

# Check modified packages from the last update, and put them to the build list.
# If a package is updated, but already on the rebuild list, then just update the git revision number.
# If a package is deleted, remove from the rebuild list, and add it to the deletion list.
# If a new package is added, then ensure that it's not on the deletion list.

trigger_rebuild_on_any_file_sed_expression=$(
  # note: This only works for upstream sources!
  # shellcheck disable=SC2016
  {
    printf 'SELECT DISTINCT CONCAT('
    printf '`package_sources`.`pkgbase`,"/repos/",'
    printf '`upstream_repositories`.`name`,"-",'
    printf 'IF(`architectures`.`name`="any","any","x86_64"))'
    printf ' FROM `binary_packages`'
    mysql_join_binary_packages_binary_packages_in_repositories
    mysql_join_binary_packages_build_assignments
    mysql_join_build_assignments_architectures
    mysql_join_build_assignments_package_sources
    mysql_join_package_sources_upstream_repositories
    printf ' WHERE `binary_packages_in_repositories`.`repository`=%s;\n' \
      "${repository_ids__any_build_list}"
  } | \
    mysql_run_query | \
    sed '
      s@.*@s,^\\(.\\t\0/\\)[^/]\\+,\\1PKGBUILD,@
    '
)

{
  # trigger rebuild of packages removed from blacklist
  # shellcheck disable=SC2016
  {
    printf 'SELECT `package_sources`.`pkgbase`,`upstream_repositories`.`name`,`git_repositories`.`name`'
    printf ' FROM `package_sources`'
    mysql_join_package_sources_upstream_repositories
    mysql_join_upstream_repositories_git_repositories
    printf ' WHERE `package_sources`.`pkgbase` IN ('
    # shellcheck disable=SC2154
    git -C "${repo_paths__archlinux32}" diff "${old_repo_revisions__archlinux32}" "${new_repo_revisions__archlinux32}" -- blacklist | \
      sed -n '
        s/^-\([^-]\)/\1/
        T
        s/\s*#.*$//
        p
      ' | \
      base64_encode_each | \
      sed '
        s/^/from_base64("/
        s/$/")/
      ' | \
      tr '\n' ','
    printf '"");\n'
  } | \
    mysql_run_query | \
    while read -r pkgbase repository git_repository; do
      printf 'A %s ' \
        "${pkgbase}"
      eval 'printf '"'"'%s'"'"' "${new_repo_revisions__'"${git_repository}"'}"'
      printf ' empty %s\n' \
        "${repository}"
    done
  # actual updates/removes
  for repo in ${repo_names}; do
    eval repo_path='"${repo_paths__'"${repo}"'}"'
    eval old_repo_revision='"${old_repo_revisions__'"${repo}"'}"'
    eval new_repo_revision='"${new_repo_revisions__'"${repo}"'}"'
    # if old revision unknown, mimic "git diff"-output
    # shellcheck disable=SC2154
    if [ "${old_repo_revision}" = "NONE" ]; then
      git -C "${repo_path}" archive --format=tar HEAD | \
        tar -t | \
        sed 's|^|A\t|'
    else
      git -C "${repo_path}" diff --no-renames --name-status "${old_repo_revision}" "${new_repo_revision}"
    fi | \
      # rename any file to "PKGBUILD" for packages on the build-list
      sed "${trigger_rebuild_on_any_file_sed_expression}" | \
      # only track changes in PKGBUILDs
      grep '/PKGBUILD$' | \
      if [ "${repo}" = "archlinux32" ]; then
      # modify the directory structure from the modifiaction-repository
      # to the one of an original source repository
        # shellcheck disable=SC2016
        sed 's|^\(.\t\)\([^/]\+\)/\([^/]\+\)/\(.\+\)$|\2 \1\3/repos/\2-x86_64/\4|' | \
          while read -r pkg_repo rest; do
            repo=$(find_git_repository_to_package_repository "${pkg_repo}")
            eval 'printf '"'"'%s %s %s\n'"'" \
              "$(printf '"${new_repo_revisions__%s}"' "${repo}")" \
              "$(printf '"${old_repo_revisions__%s}"' "${repo}")" \
              "'${rest}'"
          done
      else
        sed "s|^|${new_repo_revision} ${old_repo_revision} |"
      fi | \
      grep '^\S\+ \S\+ .\s[^/]\+/repos/[^/]\+/PKGBUILD$' | \
      # ignore i686
      grep -v -- '-i686/PKGBUILD$' | \
      # ignore staging and testing
      grep -v -- '[-/]\(staging\|testing\|unstable\)-[^/]\+/PKGBUILD$' | \
      sed 's|^\(\S\+ \S\+\) \(.\)\t\([^/]\+\)/repos/\([^/]\+\)-[^/-]\+/PKGBUILD$|\2 \3 \1 \4|'
  done | \
    sort -u | \
    sed '
      s|^D\s|0 \0|
      t
      s|^[AM]\s|1 \0|
      t
      s|^|2 |
    ' | \
    sort -k1,1 | \
    sed 's|^[012] ||'
} | \
  while read -r mode package git_revision old_git_revision repository; do
    if [ "${old_git_revision}" = 'empty' ]; then
      old_git_revision=''
    fi
    if [ "${mode}" = 'D' ]; then
      # deleted PKGBUILD
      # shellcheck disable=SC2154
      git_revision="${new_repo_revisions__archlinux32}"
      old_git_revision=''
      found_package=false
      for repository in ${repo_names}; do
        eval 'repo_path="${repo_paths__'"${repository}"'}"'
        if [ "${repository}" = "archlinux32" ]; then
          if git -C "${repo_path}" archive "${new_repo_revisions__archlinux32}" 2> /dev/null | \
            tar -t 2> /dev/null | \
            grep -q "/$(str_to_regex "${package}")/PKGBUILD$"; then
            found_package=true
            break;
          fi
        else
          # shellcheck disable=SC2154
          if eval 'git -C "${repo_path}" archive "${new_repo_revisions__'"${repository}"'}" -- "${package}/repos" 2> /dev/null | ' \
            'tar -t --wildcards "${package}/repos/*/PKGBUILD" 2> /dev/null | ' \
            'cut -d/ -f3 | ' \
            'grep -v '"'"'staging\|testing\|-unstable'"'"' | ' \
            'grep -vq -- '"'"'-i686$'"'"; then
            eval 'git_revision="${new_repo_revisions__'"${repository}"'}"'
            found_package=true
            break;
          fi
        fi
      done
      if ${found_package}; then
# TODO: moving packages between community64 and packages64 is currently broken
# - what exactly is broken? I guess, we're setting the is_to_be_deleted
# flag but should not do so - is that right? Maybe we shuold incorporate
# some detection like http://mirror.archlinux32.org/irc-logs/%23archlinux32/2018-06-13.html#21:14:53
        mode='M'
        repository=$(
          find_package_repository_to_package "${package}" "${repository}" "${git_revision}"
        )
      else
        delete_package "${package}"
        continue
      fi
    fi
    if [ "${mode}" = 'A' ] || [ "${mode}" = 'M' ]; then
      # shellcheck disable=SC2016
      {
        # delete old build assignment and associated binary packages
        # which are not yet built or on the deletion list
        printf 'DELETE `build_assignments`,`binary_packages`,`binary_packages_in_repositories`'
        printf ' FROM `binary_packages`'
        mysql_join_binary_packages_build_assignments
        mysql_join_build_assignments_package_sources
        mysql_join_binary_packages_binary_packages_in_repositories
        mysql_join_binary_packages_in_repositories_repositories
        printf ' WHERE `package_sources`.`pkgbase`=from_base64("%s")' \
          "$(
            printf '%s' "${package}" | \
              base64 -w0
          )"
        # shellcheck disable=SC2154
        printf ' AND `repositories`.`stability` IN (%s,%s);\n' \
          "${repository_stability_ids__unbuilt}" \
          "${repository_stability_ids__forbidden}"
        # remove is-to-be-deleted marker from old binary packages
        printf 'UPDATE `binary_packages_in_repositories`'
        mysql_join_binary_packages_in_repositories_binary_packages
        mysql_join_binary_packages_build_assignments
        mysql_join_build_assignments_package_sources
        printf ' SET `binary_packages_in_repositories`.`is_to_be_deleted`=0'
        printf ' WHERE `package_sources`.`pkgbase`=from_base64("%s");\n' \
          "$(
            printf '%s' "${package}" | \
              base64 -w0
          )"
      } | \
        mysql_run_query
      # shellcheck disable=SC2154
      printf '%s ' "${package}" "${git_revision}" "${new_repo_revisions__archlinux32}" "${repository}" >&2
      mysql_generate_package_metadata "${repository_ids__any_to_be_decided}" "${package}" "${git_revision}" "${old_git_revision}" "${new_repo_revisions__archlinux32}" "${repository}"
      printf '\n' >&2
      continue
    fi

    >&2 echo "unknown git diff mode '${mode}'"
    exit 1
  done

echo 'apply blacklisting'
# ignore blacklisted packages and dependent packages
# this is the first time when all the information is available and up to date

touch "${tmp_dir}/black-listed"
{
  git -C "${repo_paths__archlinux32}" archive "${new_repo_revisions__archlinux32}" -- 'blacklist' | \
    tar -Ox 'blacklist' | \
    sed '
      s/\s*#.*$//
      /^\s*$/d
# TODO: remove the legacy-blacklist-parsing
      /^\S*$/ {
        h
        s/^.*$/Warning: legacy entry in black-list detected:\n"\0"/
        w /dev/stderr
        g
        s/^/any /
      }
    '
  if [ -n "${test_exclusion}" ]; then
    echo "${test_exclusion}"
  fi
  # shellcheck disable=SC2016
  {
    printf 'SELECT DISTINCT "any",`package_sources`.`pkgbase`'
    printf ' FROM `package_sources`'
    printf ' WHERE `package_sources`.`pkgbase` LIKE "lib32-%%"'
  } | \
    mysql_run_query
} | \
  tr ' ' '\t' | \
  sort -u > \
  "${tmp_dir}/black-listed-new"

# TODO: this should be a stored procedure
while [ -s "${tmp_dir}/black-listed-new" ]; do
  cat \
    "${tmp_dir}/black-listed-new" \
    "${tmp_dir}/black-listed" | \
    sort -u | \
    sponge "${tmp_dir}/black-listed"
  {
    sed 'p' "${tmp_dir}/black-listed"

    # shellcheck disable=SC2016
    {
      for suffix in '' '_copy'; do
        printf 'CREATE TEMPORARY TABLE `bl%s` (' \
          "${suffix}"
        printf '`arch` VARCHAR(16),'
        printf '`pkgbase` VARCHAR(64),'
        printf 'UNIQUE KEY `content`(`arch`,`pkgbase`)'
        printf ');\n'
      done
      printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `bl` (`arch`,`pkgbase`);\n' \
        "${tmp_dir}/black-listed"
      printf 'INSERT INTO `bl_copy` (`arch`,`pkgbase`)'
      printf ' SELECT `bl`.`arch`,'
      printf '`bl`.`pkgbase`'
      printf ' FROM `bl`;\n'

      printf 'INSERT IGNORE INTO `bl` (`arch`,`pkgbase`)'
      printf ' SELECT `n_a`.`name`,'
      printf '`bl_copy`.`pkgbase`'
      printf ' FROM `bl_copy`'
      printf ' JOIN `architectures` AS `o_a`'
      printf ' ON `o_a`.`name`=`bl_copy`.`arch`'
      printf ' JOIN `architecture_compatibilities`'
      printf ' ON `architecture_compatibilities`.`runs_on`=`o_a`.`id`'
      printf ' JOIN `architectures` AS `n_a`'
      printf ' ON `architecture_compatibilities`.`built_for`=`n_a`.`id`'
      printf ' OR `o_a`.`name`="any";\n'

      printf 'DELETE FROM `bl`'
      printf ' WHERE `bl`.`arch`="any";\n'

      printf 'SELECT `a_ra`.`name`,`a_ps`.`pkgbase`'
      printf ' FROM `package_sources` AS `a_ps`'
      mysql_join_package_sources_build_assignments 'a_ps' 'a_ba'
      mysql_join_build_assignments_binary_packages 'a_ba' 'a_bp'
      mysql_join_binary_packages_binary_packages_in_repositories 'a_bp' 'a_bpir'
      mysql_join_binary_packages_in_repositories_repositories 'a_bpir' 'a_r'
      mysql_join_repositories_architectures 'a_r' 'a_ra'
      mysql_join_binary_packages_dependencies 'a_bp'
      mysql_join_dependencies_dependency_types
      printf ' AND (`dependency_types`.`relevant_for_building`'
      printf ' OR `dependency_types`.`relevant_for_binary_packages`)'
      printf ' WHERE NOT EXISTS ('
        printf ' SELECT 1'
        printf ' FROM `install_target_providers`'
        printf ' WHERE NOT EXISTS ('
          printf ' SELECT 1'
          printf ' FROM `bl`'
          printf ' JOIN `package_sources` AS `b_ps` ON `bl`.`pkgbase`=`b_ps`.`pkgbase`'
          mysql_join_package_sources_build_assignments 'b_ps' 'b_ba'
          mysql_join_build_assignments_binary_packages 'b_ba' 'b_bp'
          printf ' WHERE `install_target_providers`.`package`=`b_bp`.`id`'
        printf ')'
        printf ' AND `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
      printf ') AND EXISTS ('
      # TODO: This should be corrected at the root: automatic install targets, which are bogus should
      #       not be added in the first place - but how do we detect that? -> add exceptions!
        printf ' SELECT 1'
        printf ' FROM `install_target_providers`'
        printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
      printf ');\n'
    } | \
      mysql_run_query | \
      sort -u
  } | \
    sort | \
    uniq -u | \
    sponge "${tmp_dir}/black-listed-new"
done

deletion_list_count=$(
  wc -l < \
    "${tmp_dir}/black-listed"
)
if [ "${deletion_list_count}" -gt 1000 ]; then
  >&2 printf 'There are %s > 1000 packages on the deletion-list. This does not seem right.\n' \
    "${deletion_list_count}"
  if [ ! -s "${work_dir}/told-irc-about-too-many-deletion-list-packages" ]; then
    # shellcheck disable=SC2119
    {
      printf '\001ACTION refuses to blacklist %s packages.\001\n' "${deletion_list_count}"
      for repo_name in ${repo_names}; do
        eval 'old_revision="${old_repo_revisions__'"${repo_name}"'}"'
        eval 'new_revision="${new_repo_revisions__'"${repo_name}"'}"'
        # shellcheck disable=SC2154
        if [ "${old_revision}" != "${new_revision}" ]; then
          printf '%s: %s -> %s\n' \
            "${repo_name}" \
            "${old_revision}" \
            "${new_revision}"
        fi
      done
    } | \
      tee "${work_dir}/told-irc-about-too-many-deletion-list-packages" | \
      irc_say
  fi
  exit 3
fi
rm -f "${work_dir}/told-irc-about-too-many-deletion-list-packages"

while read -r arch package; do
  delete_package "${arch}" "${package}"
done < \
  "${tmp_dir}/black-listed"

if [ -n "${test_exclusion}" ]; then
  # TODO: reimplement test_exclusion with information from the database
  >&2 echo 'sry, not yet done.'
  exit 0
fi

echo 'Done - mark decisions as final.'

# shellcheck disable=SC2016
{
  # save blacklist into database
  printf 'CREATE TEMPORARY TABLE `bl` (`pkgbase` VARCHAR(64), `reason` TEXT);\n'
  git -C "${repo_paths__archlinux32}" archive "${new_repo_revisions__archlinux32}" -- 'blacklist' | \
    tar -Ox 'blacklist' | \
    sed -n '
      s/^\(\S\+\)\s*#\s*/\1 /
      T
      p
    ' | \
    while read -r pkgbase reason; do
      printf '(from_base64("%s"),from_base64("%s")),\n' \
        "$(printf '%s' "${pkgbase}" | base64 -w0)" \
        "$(printf '%s' "${reason}" | base64 -w0)"
    done | \
    sed '
      1 i INSERT INTO `bl` (`pkgbase`,`reason`) VALUES
      $ s/,$/;/
    '
  printf 'UPDATE `build_assignments`'
  mysql_join_build_assignments_package_sources
  printf ' LEFT JOIN `bl` ON `package_sources`.`pkgbase`=`bl`.`pkgbase`'
  printf ' SET `build_assignments`.`is_black_listed`=`bl`.`reason`;\n'
  printf 'DROP TEMPORARY TABLE `bl`;\n'
  printf 'COMMIT;\n'

  # update hashes of repositories in mysql database
  for repo in ${repo_names}; do
    printf 'UPDATE `git_repositories`'
    printf ' SET `git_repositories`.`head`=from_base64("%s")' \
      "$(eval 'printf '"'"'%s'"'"' "${new_repo_revisions__'"${repo}"'}" | base64 -w0')"
    printf ' WHERE `git_repositories`.`name`=from_base64("%s");\n' \
      "$(printf '%s' "${repo}" | base64 -w0)"
  done
  # move binary_packages from "to-be-decided" to "build-list"
  printf 'UPDATE `binary_packages_in_repositories`'
  mysql_join_binary_packages_in_repositories_binary_packages
  printf ' SET `binary_packages_in_repositories`.`repository`=%s' \
    "${repository_ids__any_build_list}"
  printf ' WHERE `binary_packages_in_repositories`.`repository`=%s;\n' \
    "${repository_ids__any_to_be_decided}"
} | \
  mysql_run_query

# update loop list in database (beware, the packages are expected to be in "build-list",
# not "to-be-decided", so we need to run this after moving the packages from "to-be-decided" to the "build-list".
mysql_find_build_assignment_loops

# Remove the lock file

rm -f "${build_list_lock_file}"