path: root/lib/mysql-functions

#!/bin/sh

# contains functions used to access mysql db

# shellcheck disable=SC2016,SC2039,SC2119,SC2120

if [ -z "${base_dir}" ]; then
  # just to make shellcheck happy
  . '../lib/load-configuration'
fi

# base64_encode_each encode each line of stdin with base64

base64_encode_each() {
  local line

  while read -r line; do
    printf '%s' \
      "${line}" | \
      base64 -w0
    printf '\n'
  done
}

# mysql_run_query
# wrapper function to query mysql
mysql_run_query() {
  local query_stdin
  local query_stdout
  local query_stderr

  local file_name_extra
  local file
  local files
  local number

  file_name_extra=''
  if [ "x$1" = 'xunimportant' ]; then
    shift
    file_name_extra='unimportant_'
  elif [ -s "${work_dir}/build-master-sanity" ]; then
    file_name_extra='was_insane_'
  fi

  # we save the query in a file and delete that file if the query succeeded
  query_stdin=$(mktemp "${work_dir}/tmp.mysql-functions.${file_name_extra}query.$(date +'%Y-%m-%dT%T').XXXXXX.stdin")
  query_stdout="${query_stdin%.stdin}.stdout"
  query_stderr="${query_stdin%.stdin}.stderr"
  cat > "${query_stdin}"
  for _ in {1..10}; do
    if [ -f "${query_stdout}" ]; then
      wait_some_time 10 10
    fi
    if ${mysql_command} -N --raw --batch "$@" \
      < "${query_stdin}" \
      > "${query_stdout}" \
      2>> "${query_stderr}"; then
      if grep -qF 'DELETE' "${query_stdin}"; then
        sed -n '
          s/^.*INFILE "\(\S\+\)".*$/\1/
          T
          p
        ' "${query_stdin}" | \
          while read -r file; do
            cp "${file}" "${work_dir}/successful.${query_stdin##*/}.infile.${file##*/}"
          done
        mv "${query_stdin}" "${work_dir}/successful.${query_stdin##*/}"
      else
        rm "${query_stdin}"
      fi
      rm "${query_stderr}"
    fi
    if ! [ -f "${query_stdin}" ]; then
      # success!
      break
    fi
  done
  # a present query_file means there was an error
  if [ -f "${query_stdin}" ]; then
    >&2 printf 'I (%s) could not complete a mysql query:\n' \
      "$(hostname)"
    >&2 sed 's/^/mysql<  /' "${query_stdin}"
    >&2 sed 's/^/mysql>2 /' "${query_stderr}"
    files="${query_stdin} ${query_stdout} ${query_stderr}$(
      sed -n '
        s/^.*INFILE "\(\S\+\)".*$/\1/
        T
        p
      ' "${query_stdin}" | \
        sponge | \
        cat -n | \
        while read -r number file; do
          sed -i '
            s@"'"$(str_to_regex "${file}")"'"@"'"${query_stdin%.stdin}.infile.${number}"'"@g
          ' "${query_stdin}"
          cp "${file}" "${query_stdin%.stdin}.infile.${number}"
          printf ' %s' "${query_stdin%.stdin}.infile.${number}"
        done
    )"
    if ${i_am_the_master}; then
      if [ ! -s "${work_dir}/build-master-sanity" ] && \
        [ -z "${file_name_extra}" ]; then
        printf '\001ACTION failed to execute a mysql query - can you have a look at "%s"?.\001\n' \
          "${query_stdin##*/}" \
          | irc_say
      fi
      for file in ${files}; do
        cp "${file}" "${webserver_directory}/mysql-queries/${file##*/}.txt"
        chmod go+r "${webserver_directory}/mysql-queries/${file##*/}.txt"
      done
    fi
    if [ -z "${file_name_extra}" ]; then
      echo 'A mysql query failed.' > \
        "${work_dir}/build-master-sanity"
    else
      # shellcheck disable=SC2086
      rm -f ${files}
    fi
    return 2
  fi
  # shellcheck disable=SC2094
  {
    rm "${query_stdout}"
    cat
  } < \
    "${query_stdout}"
}

# mysql_add_package_source $pkgbase $git_revision $mod_git_revision $upstream_package_repository $srcinfo_file

# shellcheck disable=SC2086
mysql_add_package_source() {
  local pkgbase
  local git_revision
  local mod_git_revision
  local upstream_package_repository
  local srcinfo_file

  pkgbase="$1"
  git_revision="$2"
  mod_git_revision="$3"
  upstream_package_repository="$4"
  srcinfo_file="$5"

  local uses_upstream
  local uses_modification
  local repo
  local upstream_package_repository_id

  if grep -qx 'PKGBUILD_mod = \S\+' "${srcinfo_file}"; then
    uses_modification=1
  else
    uses_modification=0
  fi
  if grep -qx 'PKGBUILD = \S\+' "${srcinfo_file}"; then
    uses_upstream=1
  else
    uses_upstream=0
  fi

  upstream_package_repository_id=$(
    {
      printf 'SELECT `upstream_repositories`.`id`'
      printf ' FROM `upstream_repositories`'
      printf ' WHERE `upstream_repositories`.`name` = from_base64("%s")' \
        "$(
          printf '%s' "${upstream_package_repository}" | \
            base64 -w0
        )"
      printf ' LIMIT 1;\n'
    } | \
      mysql_run_query
  )

  if [ -z "${upstream_package_repository_id}" ]; then
    >&2 printf 'Cannot find upstream package repository "%s" in the database.\n' \
      "${upstream_package_repository}"
    exit 2
  fi

  {
    printf 'INSERT INTO `package_sources`'
    printf ' (`pkgbase`,`git_revision`,`mod_git_revision`,`upstream_package_repository`,`uses_upstream`,`uses_modification`)'
    printf ' VALUES ('
    printf 'from_base64("%s"),' \
      "$(
        printf '%s' "${pkgbase}" | \
          base64 -w0
      )" \
      "$(
        printf '%s' "${git_revision}" | \
          base64 -w0
      )" \
      "$(
        printf '%s' "${mod_git_revision}" | \
          base64 -w0
      )"
    printf '%s,' \
      "${upstream_package_repository_id}" \
      "${uses_upstream}" \
      "${uses_modification}" | \
      sed 's/,$/)/'
    printf ' ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`package_sources`.`id`);\n'
    printf 'SELECT LAST_INSERT_ID();\n'
  } | \
    mysql_run_query
}

# mysql_generate_package_metadata $current_repository_id $package $git_revision $mod_git_revision $repository

# generate the meta data of a package (dependencies, built packages, ...) in the database

mysql_generate_package_metadata() {

  mysql_load_min_and_max_versions

  ( # new shell is intentional
    current_repository_id="$1"
    package="$2"
    git_revision="$3"
    mod_git_revision="$4"
    repository="$5"

    if [[ "${current_repository_id}" = *[!0-9]* ]]; then
      >&2 printf 'mysql_generate_package_metadata(): invalid current_repository_id="%s".\n' \
        "${current_repository_id}"
      exit 2
    fi

    temp_dir=$(mktemp -d 'tmp.mysql-functions.mysql_generate_package_metadata.XXXXXXXXXX' --tmpdir)
    trap 'rm -rf --one-file-system "${temp_dir}"' EXIT

    if ! make_source_info "${package}" "${repository}" "${git_revision}" "${mod_git_revision}" "${temp_dir}/SRCINFO"; then
      >&2 printf '"make_source_info %s %s %s %s %s" failed.\n' "${package}" "${repository}" "${git_revision}" "${mod_git_revision}" "${temp_dir}/SRCINFO"
      exit 2
    fi
    # remove empty lines and unsupported architectures
    sed -i '
      /^[^=]*=\s*$/d
      /^\s*arch = /{
        / \(i[46]86\|pentium4\|any\)$/!d
      }
    ' "${temp_dir}/SRCINFO"

    if [ ! -s "${temp_dir}/SRCINFO" ]; then
      >&2 printf '"make_source_info" had empty output - eh, what?\n'
      exit 2
    fi
    printf '\n\n' >> "${temp_dir}/SRCINFO"

    pkgbase=$(
      grep '^pkgbase = ' "${temp_dir}/SRCINFO" | \
        cut -d' ' -f3
    )
    if [ -z "${pkgbase}" ]; then
      >&2 printf '"make_source_info" did not return a "pkgbase" - eh, what?\n'
      exit 2
    fi

    # add the package source
    package_source_id=$(
      mysql_add_package_source "${pkgbase}" "${git_revision}" "${mod_git_revision}" "${repository}" "${temp_dir}/SRCINFO"
    )

    {
      sed -n '
        s/^\tarch = //
        T
        p
      ' "${temp_dir}/SRCINFO" | \
        sort -u | \
        grep -vxF 'any' || \
        echo 'any'
    } > \
      "${temp_dir}/architectures"

    # select any specific arch (which will be building the 'any' part of a split package)
    any_arch=$(
      tail -n1 "${temp_dir}/architectures"
    )

    # iterate over all pkgnames
    grep '^pkgname = ' "${temp_dir}/SRCINFO" | \
      cut -d' ' -f3 | \
      while read -r pkgname; do
        # iff this pkgname defines some arch, ...
        if sed -n '
          /^pkgname = '"$(str_to_regex "${pkgname}")"'$/,/^$/ p
        ' "${temp_dir}/SRCINFO" | \
          grep -q '^\sarch = '; then
          # ... it will override all archs defined in pkgbase
          sed '
            /^pkgbase = /,/^$/ {
              /^\sarch = /d
            }
          ' "${temp_dir}/SRCINFO"
        else
          cat "${temp_dir}/SRCINFO"
        fi | \
          sed -n '
            /^pkgbase = \|^pkgname = '"$(str_to_regex "${pkgname}")"'$/,/^$/ {
              s/^pkgname/\t\0/
              /^\S/d
              /^\s*$/d
              s/^\s*//
              p
            }
          ' > \
          "${temp_dir}/SRCINFO.tmp"

        epoch=$(
          {
            grep -m1 '^epoch = ' "${temp_dir}/SRCINFO.tmp" || \
              echo 'epoch = 0'
          } | \
            cut -d' ' -f3
        )
        pkgver=$(
          grep -m1 '^pkgver = ' "${temp_dir}/SRCINFO.tmp" | \
            cut -d' ' -f3
        )
        pkgrel=$(
          grep -m1 '^pkgrel = ' "${temp_dir}/SRCINFO.tmp" | \
            cut -d' ' -f3
        )

        # iterate over all archs
        grep '^arch = ' "${temp_dir}/SRCINFO.tmp" | \
          cut -d' ' -f3 | \
          while read -r arch; do
            build_arch=$(
              # this binary package is either built by the build_assignment
              # with the identical arch (if existent) or by the one
              # with arch=$any_arch
              grep -xF "${arch}" "${temp_dir}/architectures" || \
                echo "${any_arch}"
            )
            sed '
              s/^\(\S\+\)_'"${arch}"' = /\1 = /
              s/^pkgname = \(\S\+\)$/\0\nprovides = \1='"${epoch}"':'"${pkgver}"'\ndepends = base\nmakedepends = base-devel/
            ' "${temp_dir}/SRCINFO.tmp" | \
            sed '
              s/^arch = \S\+$/arch/
              t
              s/^provides = /provides\t/
              t mangle_version
              s/^groups = /groups\t/
              t mangle_version
              s/^makedepends = /makedepends\t/
              t mangle_version
              s/^checkdepends = /checkdepends\t/
              t mangle_version
              s/^depends = /rundepends\t/
              t mangle_version
              d
              :mangle_version
# TODO: we might want to keep the pkgrel part and only remove the
# sub_pkgrel part - but then we need to include pkgrels on the provide
# side, too(?)
              s/\(=\|<\|<=\|>=\|>\)\([^[:space:]-]\+\)\(-\S*\)\?$/\t\1\t\2/
              t split_epoch
              h
              s/$/\t>=\t'"${min_version}"'/
              s/\(\s[0-9]\+\):\(\S\+\)$/\1\t\2/
              p
              g
              s/$/\t<=\t'"${max_version}"'/
              /^\S\+depends\s/d
              :split_epoch
              s/\(\s[0-9]\+\):\(\S\+\)$/\1\t\2/
              t
              s/\s\S\+$/\t0\0/
            ' | \
            sed '
              s/^/'"$(
                printf '%s\t' \
                  "${pkgname}" \
                  "${arch}" \
                  "${epoch}" \
                  "${pkgver}" \
                  "${pkgrel}" \
                  "${build_arch}"
              )"'/
            '
          done
        rm "${temp_dir}/SRCINFO.tmp"
      done | \
      sort -u > \
      "${temp_dir}/database-input"

    {
      printf 'CREATE TEMPORARY TABLE `links`('
        printf '`pkgname` VARCHAR(64),'
        printf '`architecture` VARCHAR(16),'
        printf '`epoch` MEDIUMINT,'
        printf '`pkgver` VARCHAR(64),'
        printf '`pkgrel` MEDIUMINT,'
        printf '`build_architecture` VARCHAR(16),'
        printf '`type` VARCHAR(16),'
        printf '`install_target_name` VARCHAR(128),'
        printf '`version_relation` VARCHAR(2),'
        printf '`install_target_epoch` MEDIUMINT,'
        printf '`install_target_version` VARCHAR(64)'
      printf ');\n'
      printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `links`;\n' \
        "${temp_dir}/database-input"

      printf 'SET @sub_pkgrel=('
        printf 'SELECT IFNULL('
          printf 'MAX('
          printf '`binary_packages`.`sub_pkgrel`+'
          printf 'IF(`binary_packages_in_repositories`.`repository` IN (%s,%s),0,1)' \
            "${repository_ids__any_build_list}" \
            "${repository_ids__any_to_be_decided}"
          printf '),'
          printf '0'
        printf ')'
        printf ' FROM `links`'
        printf ' JOIN `architectures`'
        printf ' ON `architectures`.`name`=`links`.`architecture`'
        mysql_join_architectures_binary_packages
        printf ' AND `binary_packages`.`%s`=`links`.`%s`' \
          'pkgname' 'pkgname' \
          'epoch' 'epoch' \
          'pkgver' 'pkgver' \
          'pkgrel' 'pkgrel'
        printf ' LEFT'
        mysql_join_binary_packages_binary_packages_in_repositories
      printf ');\n'

      printf 'INSERT IGNORE INTO `build_assignments` ('
        printf '`package_source`,'
        printf '`architecture`'
      printf ') SELECT'
      printf ' %s,`architectures`.`id`' \
        "${package_source_id}"
      printf ' FROM `links`'
      printf ' JOIN `architectures`'
      printf ' ON `architectures`.`name`=`links`.`build_architecture`'
      printf ' WHERE `links`.`type`="arch";\n'

      # we delete unbuilt binary_packages of identical version prior to
      # inserting, so we can _update_ a build list entry (e.g. because
      # the source changed, but the version stays the same)
      printf 'DELETE `binary_packages`'
      printf ' FROM `links`'
      printf ' JOIN `architectures`'
      printf ' ON `links`.`architecture`=`architectures`.`name`'
      printf ' JOIN `architectures` AS `ba_a`'
      printf ' ON `ba_a`.`name`=`links`.`build_architecture`'
      mysql_join_architectures_build_assignments 'ba_a'
      mysql_join_build_assignments_binary_packages
      printf ' AND `binary_packages`.`%s`=`links`.`%s`' \
        'pkgname' 'pkgname' \
        'epoch' 'epoch' \
        'pkgver' 'pkgver' \
        'pkgrel' 'pkgrel'
      printf ' AND `binary_packages`.`sub_pkgrel`=@sub_pkgrel'
      printf ' AND `binary_packages`.`architecture`=`architectures`.`id`'
      printf ' WHERE `links`.`type`="arch"'
      # the below test should always give "true", but it is a useful
      # safeguard to not delete built packages from the database
      printf ' AND NOT EXISTS('
        printf 'SELECT 1'
        printf ' FROM `binary_packages_in_repositories`'
        mysql_join_binary_packages_in_repositories_repositories
        printf ' WHERE `repositories`.`is_on_master_mirror`'
        printf ' AND `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
      printf ');\n'

      printf 'INSERT IGNORE INTO `binary_packages` ('
        printf '`build_assignment`,'
        printf '`pkgname`,'
        printf '`epoch`,'
        printf '`pkgver`,'
        printf '`pkgrel`,'
        printf '`sub_pkgrel`,'
        printf '`architecture`,'
        printf '`compression`'
      printf ') SELECT'
      printf ' `build_assignments`.`id`,'
      printf '`links`.`%s`,' \
        'pkgname' \
        'epoch' \
        'pkgver' \
        'pkgrel'
      printf '@sub_pkgrel,'
      printf '`architectures`.`id`,'
      printf '%s' \
        "${compression_ids__tar_zst}"
      printf ' FROM `links`'
      printf ' JOIN `architectures`'
      printf ' ON `links`.`architecture`=`architectures`.`name`'
      printf ' JOIN `architectures` AS `ba_a`'
      printf ' ON `ba_a`.`name`=`links`.`build_architecture`'
      mysql_join_architectures_build_assignments 'ba_a'
      printf ' AND `build_assignments`.`package_source`=%s' \
        "${package_source_id}"
      printf ' WHERE `links`.`type`="arch";\n'

      printf 'INSERT IGNORE INTO `binary_packages_in_repositories` ('
      printf ' `package`,'
      printf '`repository`'
      printf ') SELECT'
      printf ' `binary_packages`.`id`,'
# TODO: we might want to move packages of blacklisted architectures to
# the deletion-list instead of the build-list
      printf '%s' \
        "${current_repository_id}"
      printf ' FROM `links`'
      printf ' JOIN `architectures`'
      printf ' ON `architectures`.`name`=`links`.`architecture`'
      mysql_join_architectures_binary_packages
      printf ' AND `binary_packages`.`sub_pkgrel`=@sub_pkgrel'
      printf ' AND `binary_packages`.`%s`=`links`.`%s`' \
        'pkgname' 'pkgname' \
        'epoch' 'epoch' \
        'pkgver' 'pkgver' \
        'pkgrel' 'pkgrel'
      printf ';\n'

      if [ "${current_repository_id}" -eq "${repository_ids__any_build_list}" ] || \
        [ "${current_repository_id}" -eq "${repository_ids__any_to_be_decided}" ]; then
        printf 'DELETE `binary_packages_in_repositories`'
        printf ' FROM `links`'
        printf ' JOIN `architectures`'
        printf ' ON `architectures`.`name`=`links`.`architecture`'
        mysql_join_architectures_binary_packages
        printf ' AND `binary_packages`.`pkgname`=`links`.`pkgname`'
        printf ' AND ('
          printf '`binary_packages`.`sub_pkgrel`!=@sub_pkgrel'
          printf ' OR `binary_packages`.`%s`!=`links`.`%s`' \
            'epoch' 'epoch' \
            'pkgver' 'pkgver' \
            'pkgrel' 'pkgrel'
        printf ')'
        mysql_join_binary_packages_binary_packages_in_repositories
        printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
          "${current_repository_id}"
        printf ';\n'

        printf 'DELETE `binary_packages`'
        printf ' FROM `links`'
        printf ' JOIN `architectures`'
        printf ' ON `architectures`.`name`=`links`.`architecture`'
        mysql_join_architectures_binary_packages
        printf ' AND `binary_packages`.`pkgname`=`links`.`pkgname`'
        printf ' AND ('
          printf '`binary_packages`.`sub_pkgrel`!=@sub_pkgrel'
          printf ' OR `binary_packages`.`%s`!=`links`.`%s`' \
            'epoch' 'epoch' \
            'pkgver' 'pkgver' \
            'pkgrel' 'pkgrel'
        printf ') LEFT'
        mysql_join_binary_packages_binary_packages_in_repositories
        printf ' WHERE `binary_packages_in_repositories`.`id` IS NULL'
        printf ' AND `binary_packages`.`sha512sum` IS NULL;\n'
      fi

      printf 'INSERT IGNORE INTO `install_targets` (`name`)'
      printf ' SELECT `links`.`install_target_name`'
      printf ' FROM `links`;\n'

      printf 'INSERT IGNORE INTO `versions` (`epoch`,`version`)'
      printf ' SELECT'
      printf ' `links`.`install_target_epoch`,'
      printf '`links`.`install_target_version`'
      printf ' FROM `links`'
      printf ' WHERE `links`.`install_target_epoch` IS NOT NULL'
      printf ' AND `links`.`install_target_version` IS NOT NULL'
      printf ';\n'

      for link in 'groups' 'provides' 'makedepends' 'checkdepends' 'rundepends'; do
        case "${link}" in
          'groups'|'provides')
            printf 'INSERT IGNORE INTO `install_target_providers` ('
              printf '`package`,'
              printf '`install_target`,'
              printf '`version`,'
              printf '`install_target_is_group`'
            printf ') SELECT'
            printf ' `binary_packages`.`id`,'
            printf '`install_targets`.`id`,'
            printf '`versions`.`id`,'
            if [ "${link}" = 'groups' ]; then
              printf '1'
            else
              printf '0'
            fi
          ;;
          'makedepends'|'checkdepends'|'rundepends')
            printf 'INSERT IGNORE INTO `dependencies` ('
              printf '`dependent`,'
              printf '`depending_on`,'
              printf '`dependency_type`,'
              printf '`version`,'
              printf '`version_relation`'
            printf ') SELECT'
            printf ' `binary_packages`.`id`,'
            printf '`install_targets`.`id`,'
            printf '`dependency_types`.`id`,'
            printf '`versions`.`id`,'
            printf '`links`.`version_relation`'
          ;;
        esac
        printf ' FROM `links`'
        printf ' JOIN `architectures`'
        printf ' ON `architectures`.`name`=`links`.`architecture`'
        mysql_join_architectures_binary_packages
        printf ' AND `binary_packages`.`sub_pkgrel`=@sub_pkgrel'
        printf ' AND `binary_packages`.`%s`=`links`.`%s`' \
          'pkgname' 'pkgname' \
          'epoch' 'epoch' \
          'pkgver' 'pkgver' \
          'pkgrel' 'pkgrel'
        printf ' JOIN `install_targets`'
        printf ' ON `install_targets`.`name`=`links`.`install_target_name`'
        printf ' JOIN `versions`'
        printf ' ON `versions`.`epoch`=`links`.`install_target_epoch`'
        printf ' AND `versions`.`version`=`links`.`install_target_version`'
        if [ "${link}" = 'makedepends' ] || \
          [ "${link}" = 'checkdepends' ] || \
          [ "${link}" = 'rundepends' ]; then
          printf ' JOIN `dependency_types`'
          printf ' ON `dependency_types`.`name`="%s"' \
            "${link%depends}"
        fi
        printf ' WHERE `links`.`type`="%s";\n' \
          "${link}"
      done
      printf 'DROP TEMPORARY TABLE `links`;\n'
    } | \
      mysql_run_query

  )
}

# mysql_sanity_check
# do a sanity check on the mysql database

mysql_sanity_check() {
  {
    printf 'SELECT CONCAT('
      printf '"\\"",'
      printf '`ba_arch`.`name`,'
      printf '"\\" build-assignment building \\"",'
      printf '`bp_arch`.`name`,'
      printf '"\\" binary package: ",'
      printf '`binary_packages`.`pkgname`'
    printf ')'
    printf ' FROM `binary_packages`'
    mysql_join_binary_packages_build_assignments
    mysql_join_binary_packages_architectures '' 'bp_arch'
    mysql_join_build_assignments_architectures '' 'ba_arch'
    printf ' LEFT JOIN `architecture_compatibilities`'
    printf ' ON `architecture_compatibilities`.`runs_on`=`build_assignments`.`architecture`'
    printf ' AND `architecture_compatibilities`.`built_for`=`binary_packages`.`architecture`'
    printf ' AND `architecture_compatibilities`.`fully_compatible`'
    printf ' WHERE `architecture_compatibilities`.`id` IS NULL;\n'
    printf 'SELECT DISTINCT CONCAT("package multiple times in equally stable repositories: ",`a_ra`.`name`,"/{",`a_r`.`name`,",",`b_r`.`name`,"}/",`a`.`pkgname`)'
    printf ' FROM `binary_packages` AS `a`'
    mysql_join_binary_packages_binary_packages_in_repositories 'a' 'a_bir'
    mysql_join_binary_packages_in_repositories_repositories 'a_bir' 'a_r'
    mysql_join_repositories_architectures 'a_r' 'a_ra'
    printf ' JOIN `repositories` AS `b_r`'
    printf ' ON `a_r`.`stability`=`b_r`.`stability`'
    printf ' AND `a_r`.`architecture`=`b_r`.`architecture`'
    mysql_join_repositories_binary_packages_in_repositories 'b_r' 'b_bir'
    mysql_join_binary_packages_in_repositories_binary_packages 'b_bir' 'b'
    printf ' AND `a`.`pkgname`=`b`.`pkgname`'
    printf ' AND `a`.`id`!=`b`.`id`'
    printf ' AND ('
      printf '`a_r`.`architecture`!=%s' \
        "${architecture_ids__any}"
      printf ' OR `a`.`architecture`=`b`.`architecture`'
    printf ')'
    printf ' WHERE `a_r`.`stability` NOT IN (%s,%s)' \
      "${repository_stability_ids__forbidden}" \
      "${repository_stability_ids__virtual}"
    printf ' AND `b_r`.`stability` NOT IN (%s,%s);\n' \
      "${repository_stability_ids__forbidden}" \
      "${repository_stability_ids__virtual}"
    printf 'SELECT DISTINCT CONCAT("split-package with differing sub_pkgrels on the build-list: ",`a`.`pkgname`)'
    printf ' FROM `binary_packages` AS `a`'
    printf ' JOIN `binary_packages` AS `b`'
    printf ' ON `a`.`build_assignment`=`b`.`build_assignment`'
    mysql_join_binary_packages_binary_packages_in_repositories 'a' 'a_bir'
    mysql_join_binary_packages_binary_packages_in_repositories 'b' 'b_bir'
    printf ' WHERE `a`.`sub_pkgrel`!=`b`.`sub_pkgrel`'
    printf ' AND `%s_bir`.`repository`=%s' \
      'a' "${repository_ids__any_build_list}" \
      'b' "${repository_ids__any_build_list}"
    printf ';\n'
    printf 'SELECT DISTINCT CONCAT("non-virtual binary-package without checksum: ",'
    mysql_package_name_query
    printf ') FROM `binary_packages`'
    mysql_join_binary_packages_architectures
    printf ' LEFT'
    mysql_join_binary_packages_compressions
    mysql_join_binary_packages_binary_packages_in_repositories
    mysql_join_binary_packages_in_repositories_repositories
    printf ' WHERE `repositories`.`is_on_master_mirror`'
    printf ' AND `binary_packages`.`sha512sum` IS NULL;\n'
  } | \
    mysql_run_query | \
    sed '
      s,^,<font color="#FF0000">,
      s,$,</font>,
    '
  ( # new shell is intentional
    temp_dir=$(mktemp -d 'tmp.mysql-functions.mysql_sanity_check.XXXXXXXXXX' --tmpdir)
    trap 'rm -rf --one-file-system "${temp_dir}"' EXIT

    {
      printf 'SELECT DISTINCT `architectures`.`name`'
      printf ' FROM `architectures`'
      printf ' WHERE `architectures`.`id`!=%s;\n' \
        "${architecture_ids__any}"
    } | \
      mysql_run_query | \
      while read -r arch; do
        for dir in $(ls_master_mirror "${arch}"); do
          ls_master_mirror "${arch}/${dir}" | \
            sed '
              /\.pkg\.\('"${package_compression_suffix_regex}"'\)$/!d
              s|^|'"${arch}"'/'"${dir}"'/|
            '
        done
      done | \
      sort > \
      "${temp_dir}/master-mirror-listing"

    {
      printf 'SELECT CONCAT(`r_a`.`name`,"/",`repositories`.`name`,"/",'
      mysql_package_name_query
      printf ') FROM `binary_packages_in_repositories`'
      mysql_join_binary_packages_in_repositories_binary_packages
      mysql_join_binary_packages_architectures
      printf ' LEFT'
      mysql_join_binary_packages_compressions
      mysql_join_binary_packages_in_repositories_repositories
      mysql_join_repositories_architectures '' 'r_a'
      printf ' WHERE `repositories`.`is_on_master_mirror`'
    } | \
      mysql_run_query | \
      sed '
        s,\t,/,
        s,\t,/,
        s,\t,-,
        s,\t,:,
        s,\t,-,
        s,\t,.,
        s,\t,-,
      ' | \
      sort > \
      "${temp_dir}/mysql-packages"

    diff -u \
      "${temp_dir}/master-mirror-listing" \
      "${temp_dir}/mysql-packages"

    # shellcheck disable=SC2041
    ls_master_mirror 'pool' | \
      sed '
        /\.pkg\.\('"${package_compression_suffix_regex}"'\)\(\.sig\)\?$/ !d
      ' | \
      sort -u > \
      "${temp_dir}/master-mirror-pool"

    {
      printf 'SELECT '
      mysql_package_name_query
      printf ' FROM `binary_packages`'
      mysql_join_binary_packages_architectures
      printf ' LEFT'
      mysql_join_binary_packages_compressions
      printf ' WHERE NOT EXISTS ('
        printf 'SELECT 1'
        printf ' FROM `binary_packages_in_repositories`'
        mysql_join_binary_packages_in_repositories_repositories
        printf ' WHERE NOT `repositories`.`is_on_master_mirror`'
        printf ' AND `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
      printf ') OR EXISTS ('
        printf 'SELECT 1'
        printf ' FROM `binary_packages_in_repositories`'
        mysql_join_binary_packages_in_repositories_repositories
        printf ' WHERE `repositories`.`is_on_master_mirror`'
        printf ' AND `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
      printf ')'
    } | \
      mysql_run_query | \
      sed '
        s/^.*$/\0\n\0.sig/
      ' | \
      sort > \
      "${temp_dir}/mysql-packages-pool"

    diff -u \
      "${temp_dir}/master-mirror-pool" \
      "${temp_dir}/mysql-packages-pool"
  )
}

# mysql_find_build_assignment_loops
# recreate the `build_dependency_loops` table
mysql_find_build_assignment_loops() {
  ( # new shell is intentional
    tmp_file=$(mktemp 'tmp.mysql-functions.mysql_find_build_assignment_loops.XXXXXXXXXX' --tmpdir)
    trap 'rm "${tmp_file}"' EXIT
    {
      printf 'SELECT DISTINCT `architectures`.`id`'
      printf ' FROM `architectures`'
      printf ' WHERE `architectures`.`id`!=%s;\n' \
        "${architecture_ids__any}"
    } | \
      mysql_run_query | \
      while read -r arch_id; do
        {
          printf 'SELECT DISTINCT'
          printf ' `ncy_ba`.`id`,'
          printf '`nt_ba`.`id`'
          printf ' FROM `dependencies`'
          mysql_join_dependencies_dependency_types
          mysql_join_dependencies_install_target_providers_with_versions
          mysql_join_install_target_providers_binary_packages '' 'ncy_bp'
          mysql_join_dependencies_binary_packages '' 'nt_bp'
          for which in 'ncy' 'nt'; do
            mysql_join_binary_packages_binary_packages_in_repositories "${which}"'_bp' "${which}"'_bpir'
            mysql_join_binary_packages_build_assignments "${which}"'_bp' "${which}"'_ba'
            printf ' JOIN `architecture_compatibilities` AS `%s_ac`' \
              "${which}"
            printf ' ON `%s_ac`.`built_for`=`%s_ba`.`architecture`' \
              "${which}" "${which}"
            printf ' AND `%s_ac`.`runs_on`=%s' \
              "${which}" "${arch_id}"
            printf ' AND `%s_ac`.`fully_compatible`' \
              "${which}"
          done
          printf ' WHERE `nt_bpir`.`repository`=%s' \
            "${repository_ids__any_build_list}"
          printf ' AND `ncy_bpir`.`repository`=%s' \
            "${repository_ids__any_build_list}"
          printf ' AND `dependency_types`.`relevant_for_building`'
          printf ' AND ('
            printf '`dependency_types`.`relevant_for_binary_packages`'
            printf ' OR `nt_bp`.`pkgname` LIKE "haskell-%%"'
            printf ' OR NOT EXISTS ('
              printf 'SELECT 1'
              printf ' FROM `binary_packages_in_repositories` AS `subst_bpir`'
              mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r'
              printf ' AND `subst_r`.`is_on_master_mirror`'
              mysql_join_binary_packages_in_repositories_binary_packages 'subst_bpir' 'subst_bp'
              mysql_join_binary_packages_build_assignments 'subst_bp' 'subst_ba'
              printf ' JOIN `architecture_compatibilities` AS `subst_ac`'
              printf ' ON `subst_ac`.`built_for`=`subst_ba`.`architecture`'
              printf ' AND `subst_ac`.`runs_on`=%s' \
                "${arch_id}"
              printf ' AND `subst_ac`.`fully_compatible`'
              mysql_join_binary_packages_in_repositories_install_target_providers 'subst_bpir' 'subst_itp'
              mysql_join_install_target_providers_versions 'subst_itp' 'subst_itp_v'
              printf ' WHERE `subst_itp`.`install_target`=`dependencies`.`depending_on`'
              printf ' AND '
              mysql_query_ordering_correct \
                '`dependencies_versions`.`order`' \
                '`subst_itp_v`.`order`' \
                '`dependencies`.`version_relation`'
            printf ')'
          printf ');\n'
        } | \
          mysql_run_query | \
          tr '\t' ' ' | \
          tsort 2>&1 >/dev/null | \
          sed 's/^tsort:\s*//'
      done | \
      awk '
        BEGIN {
          i=0
        };
        /^-: input contains a loop:$/ {
          i=i+1
        };
        !/^-: input contains a loop:$/ {
          print i "\t" $1
        }
      ' > \
      "${tmp_file}"
    {
      printf 'DELETE FROM `build_dependency_loops`;\n'
      printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `build_dependency_loops` (`loop`,`build_assignment`);\n' \
        "${tmp_file}"
    } | \
      mysql_run_query
  )
}

# mysql_cleanup [dry]
# clean up left overs from mysql database
mysql_cleanup() {
  local operator
  if [ "$#" = '0' ]; then
    operator='DELETE'
  elif [ "$#" = '1' ] && [ "x$1" = 'xdry' ]; then
    operator='SELECT COUNT(1)'
  else
    >&2 echo 'Unknown parameter'
    >&2 echo 'Call "mysql_clean_up" or "mysql_clean_up dry".'
    exit 2
  fi
  {
    # remove to-be-decided binary_packages_in_repositories
    printf '%s ' \
      "${operator}"
    if [ "${operator}" = 'DELETE' ]; then
      printf '`binary_packages_in_repositories` '
    fi
    printf 'FROM `binary_packages`'
    mysql_join_binary_packages_binary_packages_in_repositories
    printf ' WHERE `binary_packages_in_repositories`.`repository`=%s;\n' \
      "${repository_ids__any_to_be_decided}"
    # remove virtual binary_packages w/o binary_packages_in_repositories
    printf '%s ' \
      "${operator}"
    if [ "${operator}" = 'DELETE' ]; then
      printf '`binary_packages` '
    fi
    printf 'FROM `binary_packages`'
    printf ' WHERE `binary_packages`.`sha512sum` IS NULL'
    printf ' AND NOT EXISTS ('
      printf 'SELECT 1'
      printf ' FROM `binary_packages_in_repositories` '
      printf 'WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
    printf ');\n'
    # remove build_assignments w/o binary_package
    printf '%s FROM `build_assignments` ' \
      "${operator}"
    printf 'WHERE NOT EXISTS '
    printf '('
      printf 'SELECT 1'
      printf ' FROM `binary_packages` '
      printf 'WHERE `binary_packages`.`build_assignment`=`build_assignments`.`id`'
    printf ');\n'
    # remove failed_builds with unbroken build_assignments
    printf '%s ' \
      "${operator}"
    if [ "${operator}" = 'DELETE' ]; then
      printf '`failed_builds` '
    fi
    printf 'FROM `failed_builds` '
    mysql_join_failed_builds_build_assignments
    printf 'WHERE NOT `build_assignments`.`is_broken`'
    printf ';\n'
    # remove package_sources w/o build_assignment
    printf '%s FROM `package_sources` ' \
      "${operator}"
    printf 'WHERE NOT EXISTS '
    printf '('
      printf 'SELECT 1'
      printf ' FROM `build_assignments` '
      printf 'WHERE `build_assignments`.`package_source`=`package_sources`.`id`'
    printf ');\n'
    # remove jobs from build slaves that are not on the build-list
    if [ "${operator}" = 'DELETE' ]; then
      printf 'UPDATE `binary_packages_in_repositories`'
    else
      printf 'SELECT COUNT(DISTINCT `build_slaves`.`id`)'
      printf ' FROM `binary_packages_in_repositories`'
    fi
    mysql_join_binary_packages_in_repositories_binary_packages
    printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
      "${repository_ids__any_build_list}"
    printf ' RIGHT'
    mysql_join_binary_packages_build_slaves
    if [ "${operator}" = 'DELETE' ]; then
      printf ' SET `build_slaves`.`currently_building`=NULL'
    fi
    printf ' WHERE `build_slaves`.`currently_building` IS NOT NULL'
    printf ' AND `binary_packages_in_repositories`.`id` IS NULL;\n'
    # remove build orders from build slaves which have not connected within 1h
    if [ "${operator}" = 'DELETE' ]; then
      printf 'UPDATE `build_slaves`'
      printf ' SET `build_slaves`.`currently_building`=NULL'
    else
      printf 'SELECT COUNT(1)'
      printf ' FROM `build_slaves`'
    fi
    printf ' WHERE `build_slaves`.`currently_building` IS NOT NULL'
    printf ' AND TIMEDIFF(NOW(),('
      printf 'SELECT MAX(`ssh_log`.`date`)'
      printf ' FROM `ssh_log`'
      printf ' WHERE `ssh_log`.`build_slave`=`build_slaves`.`id`'
    printf ')) > "1:00:00";\n'
    # remove `last_action`, `trials` and `logged_lines` from buildslaves without an assignment
    if [ "${operator}" = 'DELETE' ]; then
      printf 'UPDATE `build_slaves`'
      printf ' SET `build_slaves`.`last_action`=NULL,'
      printf ' `build_slaves`.`logged_lines`=NULL,'
      printf ' `build_slaves`.`trials`=NULL'
    else
      printf 'SELECT COUNT(1)'
      printf ' FROM `build_slaves`'
    fi
    printf ' WHERE `build_slaves`.`currently_building` IS NULL'
    printf ' AND (`build_slaves`.`last_action` IS NOT NULL'
    printf ' OR `build_slaves`.`logged_lines` IS NOT NULL'
    printf ' OR `build_slaves`.`trials` IS NOT NULL);\n'
    # shrink the priorities
    printf 'CREATE TEMPORARY TABLE `ps%s`(`old_p` SMALLINT, `new_p` SMALLINT, UNIQUE KEY `old_p`(`old_p`));\n' \
      '' '_2' '_3'
    printf 'INSERT INTO `ps`(`old_p`) VALUES (0);\n'
    printf 'INSERT IGNORE INTO `ps`(`old_p`)'
    printf ' SELECT `build_assignments`.`priority`'
    printf ' FROM `build_assignments`;\n'
    for copy in '2' '3'; do
      printf 'INSERT INTO `ps_%s`(`old_p`)' \
        "${copy}"
      printf ' SELECT `ps`.`old_p`'
      printf ' FROM `ps`;\n'
    done
    printf 'UPDATE `ps`'
    printf ' LEFT JOIN ('
      printf 'SELECT'
      printf ' `ps_2`.`old_p` AS `limit_p`,'
      printf 'COUNT(`ps_3`.`old_p`) AS `count_p`'
      printf ' FROM `ps_2`'
      printf ' JOIN `ps_3`'
      printf ' ON `ps_3`.`old_p`<`ps_2`.`old_p`'
      printf ' GROUP BY `ps_2`.`old_p`'
    printf ') AS `ps_q`'
    printf ' ON `ps_q`.`limit_p`=`ps`.`old_p`'
    printf ' SET `ps`.`new_p`=IFNULL(`ps_q`.`count_p`,0)'
    printf ';\n'
    printf 'UPDATE `build_assignments`'
    printf ' JOIN `ps`'
    printf ' ON `build_assignments`.`priority`=`ps`.`old_p`'
    printf ' SET `build_assignments`.`priority`=`ps`.`new_p`;\n'
    printf 'DROP TEMPORARY TABLE `ps%s`;\n' \
      '' '_2' '_3'
  } | \
    mysql_run_query 'unimportant'
}

# mysql_query_has_pending_dependencies builder-architecture.id `build_assignment`.`id`
# print a mysql query giving whether dependencies are pending (unsuccessfully built
# dependencies do not count after 1 day)
mysql_query_has_pending_dependencies() {
  # we have pending dependencies ...
  printf 'EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `binary_packages` AS `todos`'
    mysql_join_binary_packages_binary_packages_in_repositories 'todos' 'todo_bpirs'
    printf ' AND `todo_bpirs`.`repository`=%s' \
      "${repository_ids__any_build_list}"
  # ... if any dependency ...
    mysql_join_binary_packages_dependencies 'todos' 'l_deps'
    mysql_join_dependencies_versions 'l_deps' 'l_dep_vs'
    mysql_join_dependencies_dependency_types 'l_deps' 'l_dep_ts'
    mysql_join_dependencies_install_targets 'l_deps'
  # ... is relevant for building ...
    printf ' AND `l_dep_ts`.`relevant_for_building`'
    printf ' WHERE `todos`.`build_assignment`=%s' \
      "$2"
    printf ' AND IF('
  # ... and if (a) relevant for binary_packages ...
      printf '`l_dep_ts`.`relevant_for_binary_packages`'
  # (which includes makedependencies which are haskell packages ...
      printf ' OR ('
        printf '`install_targets`.`name` LIKE "haskell-%%"'
  # ... but only if the package itself is not "ghc"!)
        printf ' AND `todos`.`pkgname` NOT IN ("ghc","ghc-libs","ghc-static")'
      printf '),'
  # ... has some unbuilt provider ...
      printf 'EXISTS ('
        printf 'SELECT 1'
        printf ' FROM `install_target_providers` AS `l_itps`'
        mysql_join_install_target_providers_versions 'l_itps' 'l_itp_vs'
        mysql_join_install_target_providers_binary_packages 'l_itps' 'dep_bp'
        mysql_join_binary_packages_binary_packages_in_repositories 'dep_bp' 'dep_bpir'
        printf ' AND `dep_bpir`.`repository`=%s' \
          "${repository_ids__any_build_list}"
        mysql_join_binary_packages_build_assignments 'dep_bp' 'dep_ba'
        # ignore dependencies which are broken >=1 day
        printf ' AND ('
          printf 'NOT EXISTS ('
            printf 'SELECT 1'
            printf ' FROM `failed_builds`'
            printf ' WHERE `failed_builds`.`date`<=ADDTIME(NOW(),"-1 00:00:00")'
            printf ' AND `failed_builds`.`build_assignment`=`dep_ba`.`id`'
          printf ')'
          # haskell-* is a hopeless case in this regard
          printf ' OR `dep_bp`.`pkgname` LIKE "haskell-%%"'
        printf ')'
        printf ' JOIN `architecture_compatibilities`'
        printf ' ON `architecture_compatibilities`.`built_for`=`dep_bp`.`architecture`'
        printf ' AND `architecture_compatibilities`.`fully_compatible`'
        printf ' WHERE `l_itps`.`install_target`=`l_deps`.`depending_on`'
        printf ' AND `dep_bp`.`build_assignment`!=%s' \
          "$2"
        printf ' AND `architecture_compatibilities`.`runs_on`=%s' \
          "$1"
        # version is irelevant: either the package is built and we don't
        # care or the package is not built and we don't know
      printf '),'
  # ... or (b) not relevant for binary_packages (e.g. a makedepends) has
  # no built provider
      printf 'NOT EXISTS ('
        printf 'SELECT 1'
        printf ' FROM `install_target_providers` AS `l_itps`'
        mysql_join_install_target_providers_versions 'l_itps' 'l_itp_vs'
        mysql_join_install_target_providers_binary_packages 'l_itps' 'dep_bp'
        mysql_join_binary_packages_binary_packages_in_repositories 'dep_bp' 'dep_bpir'
        mysql_join_binary_packages_in_repositories_repositories 'dep_bpir' 'dep_r'
        printf ' WHERE `l_itps`.`install_target`=`l_deps`.`depending_on`'
        printf ' AND ('
          printf '`dep_r`.`is_on_master_mirror`'
          printf ' OR `dep_bp`.`build_assignment`=%s' \
            "$2"
        printf ') AND `dep_r`.`architecture`=%s' \
          "$1"
        printf ' AND '
        mysql_query_ordering_correct \
          '`l_dep_vs`.`order`' \
          '`l_itp_vs`.`order`' \
          '`l_deps`.`version_relation`'
      printf ')'
    printf ')'
  printf ')'
}

# mysql_query_generate_packages_with_pending_dependencies
# print the query to generate the temporary table
# `packages_with_pending_dependencies`
mysql_query_generate_packages_with_pending_dependencies() {
  printf 'CREATE TEMPORARY TABLE `install_target_statuses`('
    printf '`id` BIGINT NOT NULL,'
    printf '`version_relation` VARCHAR(2) NOT NULL,'
    printf '`version_order` BIGINT NOT NULL,'
    printf '`builder_architecture` SMALLINT NOT NULL,'
    printf '`exists_built` BIT NOT NULL,'
    printf '`exists_unbuilt` BIT NOT NULL,'
    printf 'UNIQUE KEY `content`(`id`,`builder_architecture`),'
    printf 'KEY(`id`),'
    printf 'KEY(`builder_architecture`)'
  printf ');\n'

  printf 'INSERT IGNORE INTO `install_target_statuses`('
    printf '`id`,'
    printf '`version_relation`,'
    printf '`version_order`,'
    printf '`builder_architecture`,'
    printf '`exists_built`,'
    printf '`exists_unbuilt`'
  printf ') SELECT '
  printf '`dependencies`.`%s`,' \
    'depending_on' \
    'version_relation'
  printf '`versions`.`order`,'
  printf '`architectures`.`id`,0,0'
  printf ' FROM `dependencies`'
  mysql_join_dependencies_versions
  printf ' JOIN `architectures`;\n'

  printf 'UPDATE `install_target_statuses`'
  mysql_join_install_targets_install_target_providers 'install_target_statuses'
  mysql_join_install_target_providers_versions
  printf ' AND'
  mysql_query_ordering_correct \
    '`install_target_statuses`.`version_order`' \
    '`versions`.`order`' \
    '`install_target_statuses`.`version_relation`'
  mysql_join_install_target_providers_binary_packages
  printf ' AND ('
    printf '`binary_packages`.`architecture`=`install_target_statuses`.`builder_architecture`'
    printf ' OR `binary_packages`.`architecture`=%s' \
      "${architecture_ids__any}"
    printf ' OR `install_target_statuses`.`builder_architecture`=%s' \
      "${architecture_ids__any}"
  printf ')'
  mysql_join_binary_packages_binary_packages_in_repositories
  printf ' SET `install_target_statuses`.`exists_unbuilt`=1'
  printf ' WHERE `binary_packages_in_repositories`.`repository`=%s;\n' \
    "${repository_ids__any_build_list}"

  printf 'UPDATE `install_target_statuses`'
  mysql_join_install_targets_install_target_providers 'install_target_statuses'
  mysql_join_install_target_providers_versions
  printf ' AND'
  mysql_query_ordering_correct \
    '`install_target_statuses`.`version_order`' \
    '`versions`.`order`' \
    '`install_target_statuses`.`version_relation`'
  mysql_join_install_target_providers_binary_packages
  printf ' AND ('
    printf '`binary_packages`.`architecture`=`install_target_statuses`.`builder_architecture`'
    printf ' OR `binary_packages`.`architecture`=%s' \
      "${architecture_ids__any}"
    printf ' OR `install_target_statuses`.`builder_architecture`=%s' \
      "${architecture_ids__any}"
  printf ')'
  mysql_join_binary_packages_binary_packages_in_repositories
  mysql_join_binary_packages_in_repositories_repositories
  printf ' SET `install_target_statuses`.`exists_built`=1'
  printf ' WHERE `repositories`.`is_on_master_mirror`;\n'

  printf 'CREATE TEMPORARY TABLE `packages_with_pending_dependencies`('
    printf '`builder_architecture` SMALLINT NOT NULL,'
    printf '`build_assignment` BIGINT NOT NULL,'
    printf 'UNIQUE KEY `content`(`builder_architecture`,`build_assignment`),'
    printf 'KEY(`builder_architecture`),'
    printf 'KEY(`build_assignment`)'
  printf ');\n'

  printf 'INSERT IGNORE INTO `packages_with_pending_dependencies`('
    printf '`builder_architecture`,'
    printf '`build_assignment`'
  printf ') SELECT'
  printf ' `install_target_statuses`.`builder_architecture`,'
  printf '`build_assignments`.`id`'
  printf ' FROM `install_target_statuses`'
  printf ' JOIN `install_targets`'
  printf ' ON `install_targets`.`id`=`install_target_statuses`.`id`'
  mysql_join_install_targets_dependencies
  printf ' AND `dependencies`.`version_relation`=`install_target_statuses`.`version_relation`'
  mysql_join_dependencies_versions
  printf ' AND `versions`.`order`=`install_target_statuses`.`version_order`'
  mysql_join_dependencies_dependency_types
  printf ' AND `dependency_types`.`relevant_for_building`'
  printf ' AND ('
    printf 'NOT `install_target_statuses`.`exists_built`'
    printf 'OR ('
      printf '`install_target_statuses`.`exists_unbuilt`'
      printf ' AND ('
        printf '`dependency_types`.`relevant_for_binary_packages`'
        printf ' OR `install_targets`.`name` LIKE "haskell-%%"'
      printf ')'
    printf ')'
  printf ')'
  mysql_join_dependencies_binary_packages
  mysql_join_binary_packages_binary_packages_in_repositories
  printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
    "${repository_ids__any_build_list}"
  mysql_join_binary_packages_build_assignments
  printf ' WHERE `build_assignments`.`architecture`=`install_target_statuses`.`builder_architecture`'
  printf ' OR `build_assignments`.`architecture`=%s' \
    "${architecture_ids__any}"
  printf ' OR `install_target_statuses`.`builder_architecture`=%s' \
    "${architecture_ids__any}"
  printf ';\n'
  printf 'DROP TEMPORARY TABLE `install_target_statuses`;\n'
}

# mysql_query_is_part_of_loop `build_assignment`.`id`
# print a mysql query giving whether the package is part of a loop
mysql_query_is_part_of_loop() {
  printf 'EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `build_dependency_loops`'
    printf ' WHERE `build_dependency_loops`.`build_assignment`=%s' \
      "$1"
  printf ')'
}

# mysql_query_select_pkgbase_and_revision
# print the part of a mysql query giving:
# pkgbase git_revision mod_git_revision upstream_package_repository
mysql_query_select_pkgbase_and_revision() {
  printf '`package_sources`.`%s`,' \
    'pkgbase' \
    'git_revision' \
    'mod_git_revision'
  printf '`upstream_repositories`.`name`'
  printf ' FROM `build_assignments`'
  mysql_join_build_assignments_package_sources
  mysql_join_package_sources_upstream_repositories
}

# mysql_package_name_query [binary_packages] [architectures] [compressions]
# print a mysql query of the full name of a package file

mysql_package_name_query() {
  local bp_name="${1:-binary_packages}"
  local a_name="${2:-architectures}"
  local c_name="${3:-compressions}"
  printf 'CONCAT('
  printf '`%s`.`pkgname`,"-",' "${bp_name}"
  printf 'IF(`%s`.`epoch`=0,"",CONCAT(`%s`.`epoch`,":")),' "${bp_name}" "${bp_name}"
  printf '`%s`.`pkgver`,"-",' "${bp_name}"
  printf '`%s`.`pkgrel`,' "${bp_name}"
  printf 'IF(`%s`.`sub_pkgrel_omitted`,"",CONCAT(".",`%s`.`sub_pkgrel`)),"-",' "${bp_name}" "${bp_name}"
  printf '`%s`.`name`,".pkg.",' "${a_name}"
  printf 'IF('
    printf '`%s`.`suffix` IS NULL,' "${c_name}"
    printf '"tar.zst",'
    printf '`%s`.`suffix`' "${c_name}"
  printf ')'
  printf ')'
}

# mysql_join_*_*
# print 'JOIN' part of mysql query to connect the respective tables
# these functions take 2 optional arguments, acting as aliases for
# the tables

# mysql_join__generic $table_a $column_a $table_b $column_b
# create mysql_join_${table_a}_${table_b}() function

mysql_join__generic() {
  eval "$(
    printf 'mysql_join_%s_%s() {\n' "$1" "$3"
    printf '  printf '"'"' JOIN `%s`'"'"'\n' "$3"
    printf '  if [ -n "$2" ]; then\n'
    printf '    printf '"'"' AS `%%s`'"'"' "$2"\n'
    printf '  fi\n'
    printf '  if [ -n "$1" ]; then\n'
    printf '    printf '"'"' ON `%%s`.`%s`='"'"' "$1"\n' "$2"
    printf '  else\n'
    printf '    printf '"'"' ON `%s`.`%s`='"'"'\n' "$1" "$2"
    printf '  fi\n'
    printf '  if [ -n "$2" ]; then\n'
    printf '    printf '"'"'`%%s`.`%s`'"'"' "$2"\n' "$4"
    printf '  else\n'
    printf '    printf '"'"'`%s`.`%s`'"'"'\n' "$3" "$4"
    printf '  fi\n'
    printf '}\n'
  )"
}

for link in \
  'allowed_email_actions:action:email_actions' \
  'allowed_email_actions:gpg_key:gpg_keys' \
  \
  'binary_packages:architecture:architectures' \
  'binary_packages:build_assignment:build_assignments' \
  'binary_packages:compression:compressions' \
  \
  'binary_packages_in_repositories:package:binary_packages' \
  'binary_packages_in_repositories:repository:repositories' \
  \
  'build_assignments:architecture:architectures' \
  'build_assignments:package_source:package_sources' \
  \
  'build_dependency_loops:build_assignment:build_assignments' \
  'build_dependency_loops:build_assignment build_assignment:binary_packages' \
  \
  'build_slaves:currently_building:build_assignments' \
  'build_slaves:currently_building build_assignment:binary_packages' \
  'build_slaves:ssh_key:ssh_keys' \
  \
  'dependencies:depending_on:install_targets' \
  'dependencies:dependent:binary_packages' \
  'dependencies:dependent package:binary_packages_in_repositories' \
  'dependencies:dependency_type:dependency_types' \
  'dependencies:version:versions' \
  \
  'email_log:action:email_actions' \
  'email_log:gpg_key:gpg_keys' \
  \
  'failed_builds:reason:fail_reasons' \
  'failed_builds:build_assignment:build_assignments' \
  'failed_builds:build_slave:build_slaves' \
  \
  'gpg_keys:owner:persons' \
  \
  'install_target_providers:package:binary_packages' \
  'install_target_providers:package package:binary_packages_in_repositories' \
  'install_target_providers:install_target:install_targets' \
  'install_target_providers:install_target depending_on:dependencies' \
  'install_target_providers:version:versions' \
  \
  'package_sources:upstream_package_repository:upstream_repositories' \
  'package_sources:pkgbase pkgbase:toolchain_order' \
  \
  'repositories:stability:repository_stabilities' \
  'repositories:architecture:architectures' \
  \
  'repository_moves:upstream_package_repository:upstream_repositories' \
  'repository_moves:upstream_package_repository upstream_package_repository:package_sources' \
  \
  'ssh_keys:owner:persons' \
  \
  'ssh_log:build_slave:build_slaves' \
  \
  'upstream_repositories:git_repository:git_repositories'; do
# A join for these cannot be done, because it's not clear on what to join:
#  'repository_stability_relations:more_stable:repository_stabilities'
#  'repository_stability_relations:less_stable:repository_stabilities'

  table_b="${link##*:}"
  table_a="${link%:*}"
  column_b="${table_a##*:}"
  table_a="${table_a%:*}"
  column_a="${column_b% *}"
  if [ "${column_a}" = "${column_b}" ]; then
    column_b='id'
  else
    column_b="${column_b##* }"
  fi

  mysql_join__generic "${table_a}" "${column_a}" "${table_b}" "${column_b}"
  mysql_join__generic "${table_b}" "${column_b}" "${table_a}" "${column_a}"
done

# mysql_join_install_target_providers_dependencies_with_versions() and
# mysql_join_dependencies_install_target_providers_with_versions() also
# join via `versions`
mysql_join_install_target_providers_dependencies_with_versions() {
  mysql_join_install_target_providers_dependencies "$1" "$2"
  mysql_join_install_target_providers_versions "$1" "${1:-install_target_providers}_versions"
  mysql_join_dependencies_versions "$2" "${2:-dependencies}_versions"
  printf ' AND '
  mysql_query_ordering_correct \
    '`'"${2:-dependencies}_versions"'`.`order`' \
    '`'"${1:-install_target_providers}_versions"'`.`order`' \
    '`'"${2:-dependencies}"'`.`version_relation`'
}

mysql_join_dependencies_install_target_providers_with_versions() {
  local relation
  mysql_join_dependencies_install_target_providers "$1" "$2"
  mysql_join_install_target_providers_versions "$2" "${2:-install_target_providers}_versions"
  mysql_join_dependencies_versions "$1" "${1:-dependencies}_versions"
  printf ' AND '
  mysql_query_ordering_correct \
    '`'"${1:-dependencies}_versions"'`.`order`' \
    '`'"${2:-install_target_providers}_versions"'`.`order`' \
    '`'"${1:-dependencies}"'`.`version_relation`'
}

# mysql_retrieve_static_information
# retrieve some static information from the database:
# - names of architectures -> $architectures
# - ids of architectures -> $architecture_ids__$arch
# - ids of non-any architectures -> $non_any_architecture_ids
# - ids of package repositories -> $repository_ids__$arch_$repo
# - names of upstream_repositories -> $upstream_repository_names
# - names, paths and heads of git_repositories
# - ids of repository_stabilities -> $repository_stability_ids__$stability
# - ids of compressions -> $compression_ids__$compression
# - $package_compression_suffix_regex
mysql_retrieve_static_information() {
  eval "$(
    {
      printf 'SELECT CONCAT('
      printf '"architectures=\\"",'
      printf 'GROUP_CONCAT(`architectures`.`name` SEPARATOR " "),'
      printf '"\\"")'
      printf ' FROM `architectures`'
      printf ' GROUP BY "1";\n'
      printf 'SELECT REPLACE(CONCAT('
      printf '"architecture_ids__",'
      printf '`architectures`.`name`,"=",'
      printf '`architectures`.`id`'
      printf '),"-","_")'
      printf ' FROM `architectures`;\n'
      printf 'SELECT CONCAT('
      printf '"non_any_architecture_ids=",'
      printf 'GROUP_CONCAT('
      printf '`architectures`.`id`'
      printf ' ORDER BY `architectures`.`id`'
      printf '))'
      printf ' FROM `architectures`'
      printf ' WHERE `architectures`.`name`!="any";\n'
      printf 'SELECT REPLACE(CONCAT('
      printf '"repository_ids__",'
      printf '`architectures`.`name`,"_",'
      printf '`repositories`.`name`,"=",'
      printf '`repositories`.`id`'
      printf '),"-","_")'
      printf ' FROM `repositories`'
      mysql_join_repositories_architectures
      printf ';\n'
      printf 'SELECT CONCAT('
      printf '"upstream_repository_names=\\"",'
      printf 'GROUP_CONCAT(`upstream_repositories`.`name` SEPARATOR " "),'
      printf '"\\"")'
      printf ' FROM `upstream_repositories`'
      printf ' GROUP BY "1";\n'
      printf 'SELECT CONCAT('
      printf '"repo_names=\\"",'
      printf 'GROUP_CONCAT(`git_repositories`.`name` SEPARATOR " "),'
      printf '"\\"")'
      printf ' FROM `git_repositories`'
      printf ' GROUP BY "1";\n'
      printf 'SELECT CONCAT('
      printf '"repo_paths__",'
      printf '`git_repositories`.`name`,"=",'
      printf '"\\"",`git_repositories`.`directory`,"\\";'
      printf 'repo_heads__",'
      printf '`git_repositories`.`name`,"=",'
      printf '"\\"",`git_repositories`.`head`,"\\"")'
      printf ' FROM `git_repositories`;\n'
      printf 'SELECT CONCAT('
      printf '"repository_stability_ids__",'
      printf '`repository_stabilities`.`name`,"=",'
      printf '`repository_stabilities`.`id`)'
      printf ' FROM `repository_stabilities`;\n'
      printf 'SELECT REPLACE(CONCAT('
      printf '"compression_ids__",'
      printf 'REPLACE('
        printf '`compressions`.`suffix`,".","_"'
      printf '),"=",'
      printf '`compressions`.`id`'
      printf '),"-","_")'
      printf ' FROM `compressions`;\n'
      printf 'SELECT CONCAT('
      printf '"package_compression_suffix_regex=\\"",'
      printf 'GROUP_CONCAT('
        printf 'REPLACE('
          printf '`compressions`.`suffix`,'
          printf '".","\\\\."'
      printf ') SEPARATOR "\\\\|"),'
      printf '"\\""'
      printf ') FROM `compressions`;\n'
    } | \
      mysql_run_query
  )"
}

# mysql_query_and_delete_unneeded_binary_packages
# print a query which lists and deletes binary_packages which are not
# linked from binary_packages_in_repositories
mysql_query_and_delete_unneeded_binary_packages() {
  printf 'SELECT CONCAT("pool/",'
  mysql_package_name_query
  printf ') FROM `binary_packages`'
  mysql_join_binary_packages_architectures
  printf ' LEFT'
  mysql_join_binary_packages_compressions
  printf ' WHERE NOT EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `binary_packages_in_repositories`'
    printf ' WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
  printf ');\n'
  printf 'DELETE `binary_packages`'
  printf ' FROM `binary_packages`'
  printf ' WHERE NOT EXISTS ('
    printf 'SELECT 1'
    printf ' FROM `binary_packages_in_repositories`'
    printf ' WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
  printf ');\n'
}

# mysql_sort_versions [-f]
# sort the table of versions, iff versions without order exist or -f was
# given

mysql_sort_versions() {
  ( # new shell is intentional
    tmp_file=$(mktemp 'tmp.mysql_sort_versions.XXXXXXXXXX' --tmpdir)
    trap 'rm "${tmp_file}"' EXIT
    if [ "x$1" != 'x-f' ] && \
      {
        printf 'SELECT'
        printf ' COUNT(1)'
        printf ' FROM `versions`'
        printf ' WHERE `versions`.`order` IS NULL'
      } | \
        mysql_run_query | \
        grep -qxF '0'; then
      # nothing to do
      exit 0
    fi
    {
      printf 'SELECT '
      printf '`versions`.`%s`,' \
        'id' \
        'epoch' \
        'version' | \
        sed 's/,$//'
      printf ' FROM `versions`;\n'
    } | \
      mysql_run_query | \
      expand_version 3 | \
      sort -k2n,2 -k3V,3 | \
      cut -f1 | \
      cat -n | \
      awk '{print $1 " " $2}' | \
      tr ' ' '\t' > \
      "${tmp_file}"
    max_order=$(
      {
        printf 'SELECT'
        printf ' GREATEST(%s,' \
          "$(
            wc -l < "${tmp_file}"
          )"
        printf 'MAX(`versions`.`order`))'
        printf ' FROM `versions`;\n'
      } | \
        mysql_run_query
    )
    {
      printf 'CREATE TEMPORARY TABLE `vs`('
        printf '`id` BIGINT,'
        printf '`ord` BIGINT,'
        printf 'UNIQUE KEY(`id`),'
        printf 'UNIQUE KEY(`ord`)'
      printf ');\n'
      printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `vs`(`ord`,`id`);\n' \
        "${tmp_file}"
      printf 'UPDATE `versions`'
      printf ' SET `versions`.`order`=`versions`.`order`+1+%s;\n' \
        "${max_order}"
      printf 'COMMIT;\n'
      printf 'UPDATE `versions`'
      printf ' JOIN `vs`'
      printf ' ON `vs`.`id`=`versions`.`id`'
      printf ' SET `versions`.`order`=`vs`.`ord`;\n'
    } | \
      mysql_run_query
  )
}

# mysql_load_min_and_max_versions
# load the minimal and maximal versions into min_version and max_version

mysql_load_min_and_max_versions() {
  if [ -z "${min_version}" ]; then
    min_version=$(
      {
        printf 'SELECT CONCAT(`versions`.`epoch`,":",`versions`.`version`)'
        printf ' FROM `versions`'
        printf ' WHERE `versions`.`order` IS NOT NULL'
        printf ' ORDER BY `versions`.`order`'
        printf ' LIMIT 1'
      } | \
      mysql_run_query
    )
  fi

  if [ -z "${max_version}" ]; then
    max_version=$(
      {
        printf 'SELECT CONCAT(`versions`.`epoch`,":",`versions`.`version`)'
        printf ' FROM `versions`'
        printf ' WHERE `versions`.`order` IS NOT NULL'
        printf ' ORDER BY `versions`.`order` DESC'
        printf ' LIMIT 1'
      } | \
      mysql_run_query
    )
  fi

  if [ -z "${min_version_id}" ]; then
    min_version_id=$(
      {
        printf 'SELECT `versions`.`id`'
        printf ' FROM `versions`'
        printf ' WHERE `versions`.`order` IS NOT NULL'
        printf ' ORDER BY `versions`.`order`'
        printf ' LIMIT 1'
      } | \
      mysql_run_query
    )
  fi

  if [ -z "${max_version_id}" ]; then
    max_version_id=$(
      {
        printf 'SELECT `versions`.`id`'
        printf ' FROM `versions`'
        printf ' WHERE `versions`.`order` IS NOT NULL'
        printf ' ORDER BY `versions`.`order` DESC'
        printf ' LIMIT 1'
      } | \
      mysql_run_query
    )
  fi
}

# mysql_query_remove_old_binary_packages_from_build_list
# print a query to remove old versions of packages from the build-list
mysql_query_remove_old_binary_packages_from_build_list() {
  printf 'DELETE `d_bpir`'
  printf ' FROM `binary_packages_in_repositories` AS `d_bpir`'
  mysql_join_binary_packages_in_repositories_binary_packages 'd_bpir' 'd_bp'
  printf ' JOIN `versions` as `d_v`'
  printf ' ON `d_bp`.`epoch`=`d_v`.`epoch`'
  printf ' AND `d_bp`.`pkgver`=`d_v`.`version`'
  printf ' JOIN `binary_packages` AS `n_bp`'
  printf ' ON `d_bp`.`pkgname`=`n_bp`.`pkgname`'
  printf ' JOIN `versions` as `n_v`'
  printf ' ON `n_bp`.`epoch`=`n_v`.`epoch`'
  printf ' AND `n_bp`.`pkgver`=`n_v`.`version`'
  mysql_join_binary_packages_binary_packages_in_repositories 'n_bp' 'n_bpir'
  printf ' WHERE `d_bpir`.`repository`=%s' \
    "${repository_ids__any_build_list}"
  printf ' AND `n_bpir`.`repository`=%s' \
    "${repository_ids__any_build_list}"
  printf ' AND ('
    printf '`n_v`.`order`>`d_v`.`order`'
    printf ' OR ('
      printf '`n_v`.`order`=`d_v`.`order`'
      printf ' AND ('
        printf '`n_bp`.`pkgrel`>`d_bp`.`pkgrel`'
        printf ' OR ('
          printf '`n_bp`.`pkgrel`=`d_bp`.`pkgrel`'
          printf ' AND `n_bp`.`sub_pkgrel`>`d_bp`.`sub_pkgrel`'
        printf ')'
      printf ')'
    printf ')'
  printf ');\n'

  printf 'COMMIT;\n'

  mysql_query_and_delete_unneeded_binary_packages | \
    grep -v '^SELECT '
}

# mysql_query_ordering_correct dependency.version.order install_target_provider.version.order dependency.version_relation
# print a query yielding wether the ordering is correct
mysql_query_ordering_correct() {
  local relation
  printf '('
    for relation in '<' '<=' '>' '>=' '='; do
      printf '('
        printf '%s="%s"' \
          "${3}" \
          "${relation}"
        printf ' AND %s%s%s' \
          "${2}" \
          "${relation}" \
          "${1}"
      printf ') OR '
    done | \
      sed '
        s/ OR $//
      '
  printf ')'
}

# mysql_determine_majority_build_slave_architecture_id
# retreive the `architectures`.`id` of the majority of the currently
# active build slaves
mysql_determine_majority_build_slave_architecture_id() {
  {
    printf 'SELECT'
    printf ' COUNT(DISTINCT `ssh_log`.`id`) AS `count`,'
    printf '`architectures`.`id`'
    printf ' FROM `ssh_log`'
    printf ' JOIN `architectures`'
    printf ' ON `ssh_log`.`parameters`=CONCAT(`architectures`.`name`," ")'
    printf ' WHERE `action`="get-assignment"'
    printf ' AND `ssh_log`.`date`>ADDTIME(NOW(),"-1 00:00:00")'
    printf ' GROUP BY `ssh_log`.`parameters`'
    printf ' ORDER BY `count` DESC'
    printf ' LIMIT 1'
  } | \
    mysql_run_query | \
    cut -f2
}

# mysql_query_delete_packages $package_sources_condition $repositories_condition

# print a mysql query for deleting:
#  - binary_packages_in_repositories matching $condition
#  - binary_packages linked to *only* deleted
#    binary_packages_in_repositories
#  - build_assignments linked to *only* deleted binary_packages
#  - package_sources linked to *only* deleted build_assignments
# This is done in reverse order to not loose the handle on the
# to-be-deleted binary_packages_in_repositories
mysql_query_delete_packages() {
  local depth package_sources_condition repositories_condition
  package_sources_condition="$1"
  repositories_condition="$2"
  for depth in 4 3 2 1; do
    printf 'DELETE '
    if [ ${depth} -ge 4 ]; then
      # try to delete package_sources, too
      printf '`package_sources`,'
    fi
    if [ ${depth} -ge 3 ]; then
      # try to delete build_assignments, too
      printf '`build_assignments`,'
    fi
    if [ ${depth} -ge 2 ]; then
      # try to delete binary_packages, too
      printf '`binary_packages`,'
    fi
    printf '`binary_packages_in_repositories`'
    printf ' FROM `repositories`'
    mysql_join_repositories_binary_packages_in_repositories
    printf ' AND %s' \
      "${repositories_condition}"
    mysql_join_binary_packages_in_repositories_binary_packages
    mysql_join_binary_packages_build_assignments
    mysql_join_build_assignments_package_sources
    printf ' AND %s' \
      "${package_sources_condition}"
    case "${depth}" in
      '2')
        printf ' LEFT JOIN ('
          printf '`binary_packages_in_repositories` AS `keep_bpir`'
          mysql_join_binary_packages_in_repositories_repositories 'keep_bpir' 'keep_r'
          printf ' AND %s' "${repositories_condition}" \
          | sed '
            s/`repositories`/`keep_r`/g
          '
        printf ') ON `keep_bpir`.`package`=`binary_packages`.`id`'
      ;;
      '3')
        printf ' LEFT JOIN ('
          printf '`binary_packages` AS `keep_bp`'
          mysql_join_binary_packages_binary_packages_in_repositories 'keep_bp' 'keep_bpir'
          mysql_join_binary_packages_in_repositories_repositories 'keep_bpir' 'keep_r'
          printf ' AND NOT (%s)' "${repositories_condition}" \
          | sed '
            s/`repositories`/`keep_r`/g
          '
        printf ') ON `keep_bp`.`build_assignment`=`build_assignments`.`id`'
      ;;
      '4')
        printf ' LEFT JOIN ('
          printf '`build_assignments` AS `keep_ba`'
          mysql_join_build_assignments_binary_packages 'keep_ba' 'keep_bp'
          mysql_join_binary_packages_binary_packages_in_repositories 'keep_bp' 'keep_bpir'
          mysql_join_binary_packages_in_repositories_repositories 'keep_bpir' 'keep_r'
          printf ' AND %s' "${repositories_condition}" \
          | sed '
            s/`repositories`/`keep_r`/g
          '
        printf ') ON `keep_ba`.`package_source`=`package_sources`.`id`'
      ;;
    esac
    if [ ${depth} -ge 2 ]; then
      # only if all packages unbuilt or on the deletion-list
      printf ' WHERE `keep_r`.`id` IS NULL'
    fi
    printf ';\n'
  done
}