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

# TODO: replace most/all insert-select queries by separate select and insert
# queries

# 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

  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.stdin.$(date +'%Y-%m-%dT%T').XXXXXX")
  query_stdout=$(mktemp "${work_dir}/tmp.mysql-functions.${file_name_extra}query.stdout.$(date +'%Y-%m-%dT%T').XXXXXX")
  query_stderr=$(mktemp "${work_dir}/tmp.mysql-functions.${file_name_extra}query.stderr.$(date +'%Y-%m-%dT%T').XXXXXX")
  cat > "${query_stdin}"
  for _ in {1..10}; do
    ${mysql_command} -N --raw --batch "$@" \
      < "${query_stdin}" \
      > "${query_stdout}" \
      2>> "${query_stderr}" \
      && rm "${query_stdin}" "${query_stderr}"
    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 could not complete a mysql query!\n'
    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 \
      "${query_stdin}" \
      "${query_stdout}" \
      "${query_stderr}"; do
      cp "${file}" "${webserver_directory}/mysql-queries/${file##*/}.txt"
      chmod go+r "${webserver_directory}/mysql-queries/${file##*/}.txt"
    done
    if [ -z "${file_name_extra}" ]; then
      echo 'A mysql query failed.' > \
        "${work_dir}/build-master-sanity"
    else
      rm -f \
        "${query_stdin}" \
        "${query_stdout}" \
        "${query_stderr}"
    fi
    return 2
  fi
  cat "${query_stdout}"
  rm "${query_stdout}"
}

# mysql_add_package_source $pkgbase $git_revision $old_git_revision $mod_git_revision $upstream_package_repository $srcinfo_file
# $old_git_revision may be empty, in which case the whole history will be searched (slower)

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

  pkgbase="$1"
  git_revision="$2"
  old_git_revision="${3:+$3..}"
  mod_git_revision="$4"
  upstream_package_repository="$5"
  srcinfo_file="$6"

  local uses_upstream
  local uses_modification
  local repo
  local repo_path
  local commit_time
  local pkgbuild
  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
    eval 'repo_path="${repo_paths__'"$(sed -n 's/^upstream_git_repository = //;T;p' "${srcinfo_file}")"'}"'
    pkgbuild=$(
      sed -n '
        s/^PKGBUILD = //
        T
        p
      ' "${srcinfo_file}"
    )
    commit_time=$(
      git -C "${repo_path}" log -n 1 --pretty=format:%ct "${old_git_revision}${git_revision}" -- "${pkgbuild}"
    )
    if [ -z "${commit_time}" ]; then
      # We may have modified git_mod_revision!
      commit_time=$(
        git -C "${repo_path}" log -n 1 --pretty=format:%ct "${git_revision}" -- "${pkgbuild}"
      )
    fi
  else
    uses_upstream=0
    commit_time=$(date '+%s')
  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`,`commit_time`)'
    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}"
    printf 'from_unixtime(%s))' \
      "${commit_time}"
    printf ' ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`package_sources`.`id`);\n'
    printf 'SELECT LAST_INSERT_ID();\n'
  } | \
    mysql_run_query
}

# TODO: return ids on INSERT queries and use those in subsequent queries

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

# generate the meta data of a package (dependencies, built packages, ...) in the database
# $old_git_revision may be empty, in which case the whole history will be searched (slower)

mysql_generate_package_metadata() {

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

    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
      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
    # TODO: support more architectures
    sed -i '
      /^[^=]*=\s*$/d
      /^\s*arch = /{
        / \(i686\|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}" "${old_git_revision}" "${mod_git_revision}" "${repository}" "${temp_dir}/SRCINFO"
    )

    # TODO: architectures should be identified by id, not "(SELECT ...)"

    # add the build assignment(s)
    {
      sed -n '
        s/^\tarch = //
        T
        p
      ' "${temp_dir}/SRCINFO" | \
        sort -u | \
        grep -vxF 'any' || \
        echo 'any'
    } | \
      base64_encode_each | \
      while read -r arch; do
        printf 'INSERT INTO `build_assignments` (`package_source`,`architecture`,`is_blocked`,`is_broken`,`priority`)'
        printf ' SELECT %s,`architectures`.`id`,NULL,0,0' \
          "${package_source_id}"
        printf ' FROM `architectures`'
        printf ' WHERE `architectures`.`name` = from_base64("%s")' \
          "${arch}"
        printf ' ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`build_assignments`.`id`);\n'
        printf 'SELECT "%s",LAST_INSERT_ID();\n' \
          "${arch}"
      done | \
      mysql_run_query > \
      "${temp_dir}/build-assignments.id"

    # select any specific arch (which will be building the 'any' part of a split package)
    any_arch=$(
      cut -f1 < \
        "${temp_dir}/build-assignments.id" | \
        sort | \
        head -n1 | \
        tr -d '\n' | \
        base64 -w0
    )

    # iterate over all pkgnames
    grep '^pkgname = ' "${temp_dir}/SRCINFO" | \
      cut -d' ' -f3 | \
      while read -r pkgname; do
        pkgname64=$(
          printf '%s' "${pkgname}" | \
            base64 -w0
        )
        sed -n '
          /^pkgbase = \|^pkgname = '"$(str_to_regex "${pkgname}")"'$/,/^$/ {
            /^\S/d
            s/^\s*//
            p
          }
        ' "${temp_dir}/SRCINFO" > \
          "${temp_dir}/BINARYINFO.${pkgname64}"

        # iterate over all archs
        grep '^arch = ' "${temp_dir}/BINARYINFO.${pkgname64}" | \
          cut -d' ' -f3 | \
          while read -r arch; do
            arch64=$(
              printf '%s' "${arch}" | \
                base64 -w0
            )
            {
              {
                # 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 "^${arch64}"'\s' "${temp_dir}/build-assignments.id" || \
                  grep "^${any_arch}"'\s' "${temp_dir}/build-assignments.id"
              } | \
                sed '
                  s/^.*\s/build-assignment-id = /
                '
              sed '
                s/^\(\S\+\)_'"${arch}"' = /\1 = /
              ' "${temp_dir}/BINARYINFO.${pkgname64}"
            } > \
              "${temp_dir}/ARCHINFO ${pkgname64} ${arch64}"
          done
      done

    # TODO: This should become multiple mysql queries:
    # 1st inserting install_targets
    # 2nd inserting binary_packages (and binary_packages_in_repositories)
    # 3rd,4th inserting dependencies and install_target_providers
    # The links between the above should _solely_ be made by the ids of
    # the respective rows.

    # now each "ARCHINFO $pkgname $arch" file represents one binary package
    find "${temp_dir}" -mindepth 1 -maxdepth 1 -name 'ARCHINFO * *' -printf '%f\n' | \
      while read -r _ pkgname arch; do
        build_assignment_id=$(
          grep '^build-assignment-id = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3
        )
        pkgver=$(
          grep '^pkgver = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            tr -d '\n' | \
            base64 -w0
        )
        pkgrel=$(
          grep '^pkgrel = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            sed 's/\.[0-9]\+$//' | \
            tr -d '\n' | \
            base64 -w0
        )
        epoch=$(
          {
            grep '^epoch = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" || \
              echo 'epoch = 0'
          } | \
            cut -d' ' -f3 | \
            tr -d '\n' | \
            base64 -w0
        )
        # TODO: allow versioned install_targets
        provides=$(
          grep '^\(groups\|provides\) = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            sed 's/[<>=].*$//' | \
            base64_encode_each
        )
        makedepends=$(
          grep '^makedepends = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            sed 's/[<>=].*$//' | \
            base64_encode_each
        )
        checkdepends=$(
          grep '^checkdepends = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            sed 's/[<>=].*$//' | \
            base64_encode_each
        )
        rundepends=$(
          grep '^depends = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            sed 's/[<>=].*$//' | \
            base64_encode_each
        )
        sub_pkgrel=$(
          printf '(SELECT COALESCE('
          # do not add binary packages which are currently on the
          # build-list or in $current_repository_id (beware of split
          # packages!)
          printf '(SELECT `sub_pkgrel` FROM `binary_packages`'
          mysql_join_binary_packages_architectures
          mysql_join_binary_packages_binary_packages_in_repositories
          printf ' WHERE'
          printf ' `binary_packages`.`%s`=from_base64("%s") AND' \
            'epoch' "${epoch}" \
            'pkgver' "${pkgver}" \
            'pkgrel' "${pkgrel}" \
            'pkgname' "${pkgname}"
          printf ' `architectures`.`name`=from_base64("%s")' \
            "${arch}"
          printf ' AND `binary_packages_in_repositories`.`repository` IN (%s,%s)),' \
            "${repository_ids__any_build_list}" \
            "${current_repository_id}"
          # max(sub_pkgrel)+1
          printf '(SELECT 1+MAX(`binary_packages`.`sub_pkgrel`) FROM `binary_packages`'
          mysql_join_binary_packages_architectures
          printf ' WHERE'
          printf ' `binary_packages`.`%s`=from_base64("%s") AND' \
            'epoch' "${epoch}" \
            'pkgver' "${pkgver}" \
            'pkgrel' "${pkgrel}" \
            'pkgname' "${pkgname}"
          if printf '%s' "${arch}" | base64 -d | grep -qxF 'any'; then
            # 'any' gets higher sub_pkgrel than any architecture
            printf ' 1'
          else
            # not-'any' gets higher sub_pkgrel than same or 'any' architecture
            printf ' (`architectures`.`name`=from_base64("%s") OR `architectures`.`name`="any")' \
              "${arch}"
          fi
          printf ')'
          printf ',0))'
        )
        { # TODO: identify arch by id
          printf 'INSERT IGNORE INTO `binary_packages` ('
          printf '`%s`,' \
            'build_assignment' \
            'architecture' \
            'epoch' \
            'pkgver' \
            'pkgrel' \
            'pkgname' \
            'sub_pkgrel' \
            'has_issues' \
            'is_tested'
          printf ') SELECT '
          printf '%s,' \
            "${build_assignment_id}"
          printf '`%s`.`id`,' \
            'architectures'
          printf 'from_base64("%s"),' \
            "${epoch}" \
            "${pkgver}" \
            "${pkgrel}" \
            "${pkgname}"
          printf '%s,0,0 FROM `architectures`' \
            "${sub_pkgrel}"
          printf ' WHERE'
          printf ' `architectures`.`name` = from_base64("%s")' \
            "${arch}"
          printf ';\n'
          printf 'INSERT IGNORE INTO `binary_packages_in_repositories` (`package`,`repository`,`is_to_be_deleted`)'
          printf ' SELECT LAST_INSERT_ID(),%s,0;\n' \
            "${current_repository_id}"
          printf 'COMMIT;\n'
        } | \
          sed '
            s|,)|)|g
            s| JOIN WHERE | WHERE |
            s| AND;$|;|
          ' >> \
          "${temp_dir}/add-binary-packages-command"
        {
          printf 'CREATE TEMPORARY TABLE `%s` (`name` VARCHAR(64));\n' \
            'provides' \
            'makedepends' \
            'checkdepends' \
            'rundepends'

          printf 'INSERT INTO `provides` VALUES\n'
          echo "${provides}" | \
            sort -u | \
            grep -vxF '' | \
            sed '
              s|^| (from_base64("|
              s|$|")),|
            '
          printf ' (CONCAT(from_base64("%s"),"-",from_base64("%s"),":",from_base64("%s"),"-",from_base64("%s"))),\n' \
            "${pkgname}" \
            "${epoch}" \
            "${pkgver}" \
            "${pkgrel}"
          printf ' (CONCAT(from_base64("%s"),"-",from_base64("%s"),":",from_base64("%s"))),\n' \
            "${pkgname}" \
            "${epoch}" \
            "${pkgver}"
          printf ' (from_base64("%s"));\n' \
            "${pkgname}"

          printf 'INSERT INTO `rundepends` VALUES\n'
          echo "${rundepends}" | \
            sort -u | \
            grep -vxF '' | \
            sed '
              s|^| (from_base64("|
              s|$|")),|
            '
          if printf '%s' "${pkgname}" | \
            base64 -d | \
            grep -q -- '-doc$'; then
            printf ' (CONCAT(from_base64("%s"),"-",from_base64("%s"),":",from_base64("%s"))),\n' \
              "$(printf '%s' "${pkgname}" | base64 -d | sed 's/-doc$//' | base64 -w0)" \
              "${epoch}" \
              "${pkgver}"
          fi
          printf ' ("base");\n'

          echo "${checkdepends}" | \
            sort -u | \
            grep -vxF '' | \
            sed '
              s|^| (from_base64("|
              s|$|")),|
              1 s/^/INSERT INTO `checkdepends` VALUES \n/
              $ s/,$/;/
            '

          printf 'INSERT INTO `makedepends` VALUES\n'
          echo "${makedepends}" | \
            sort -u | \
            grep -vxF '' | \
            sed '
              s|^| (from_base64("|
              s|$|")),|
            '
          printf ' ("base-devel");\n'

          printf 'INSERT IGNORE INTO `install_targets` (`name`)'
          printf ' SELECT (`name`) FROM `%s` UNION' \
            'provides' \
            'makedepends' \
            'checkdepends' \
            'rundepends' | \
            sed 's| UNION$|;\n|'

          for link in 'provides' 'makedepends' 'checkdepends' 'rundepends'; do
            case "${link}" in
              'provides')
                printf 'INSERT IGNORE INTO `install_target_providers` (`package`,`install_target`) SELECT'
                printf ' `binary_packages`.`id`,`install_targets`.`id` FROM'
              ;;
              'makedepends'|'checkdepends'|'rundepends')
                printf 'INSERT IGNORE INTO `dependencies` (`dependent`,`depending_on`,`dependency_type`) SELECT'
                printf ' `binary_packages`.`id`,`install_targets`.`id`,`dependency_types`.`id` FROM'
                printf ' `dependency_types` JOIN'
              ;;
            esac
            printf ' `binary_packages`'
            mysql_join_binary_packages_architectures
            printf ' JOIN `install_targets`'
            printf ' JOIN `%s`' "${link}"
            printf ' ON `%s`.`name` = `install_targets`.`name`' "${link}"
            printf ' WHERE'
            if [ "${link}" = 'makedepends' ] || \
              [ "${link}" = 'checkdepends' ] || \
              [ "${link}" = 'rundepends' ]; then
              printf ' `dependency_types`.`name` = "%s" AND' \
                "${link%depends}"
            fi
            # TODO: identify binary_packages by id
            printf ' `binary_packages`.`%s` = from_base64("%s") AND' \
              'epoch' "${epoch}" \
              'pkgver' "${pkgver}" \
              'pkgrel' "${pkgrel}" \
              'pkgname' "${pkgname}"
            # we do not want to match the sub_pkgrel:
            # a) it is tedious to do so (because it may be calculated
            #    dynamically)
            # b) it is not necessary to do so: if only the sub_pkgrel
            #    changed, the dependencies and provided install_targets
            #    should not have changed
            printf ' `architectures`.`name` = from_base64("%s");\n' \
              "${arch}"
            # the repository is of no relevance: it hardly matters for
            # the dependencies
          done

          printf 'DROP TABLE `%s`;\n' \
            'provides' \
            'makedepends' \
            'checkdepends' \
            'rundepends'
        } >> \
          "${temp_dir}/add-install-targets-command"
      done

    {
      if [ -s "${temp_dir}/add-binary-packages-command" ]; then
        cat "${temp_dir}/add-binary-packages-command"
      fi
      if [ -s "${temp_dir}/add-install-targets-command" ]; then
        cat "${temp_dir}/add-install-targets-command"
      fi
    } | \
      mysql_run_query

  )
}

# mysql_sanity_check
# do a sanity check on the mysql database

mysql_sanity_check() {
  {
    printf 'SELECT CONCAT("\\"any\\" build-assignment building \\"",`bp_arch`.`name`,"\\" binary package: ",`binary_packages`.`pkgname`)'
    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 ' WHERE `bp_arch`.`name`!="any"'
    printf ' AND `ba_arch`.`name`="any";\n'
    printf 'SELECT DISTINCT CONCAT("package multiple times in equally stable repositories: {",`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'
    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 ' WHERE `a_r`.`id` NOT IN (%s,%s)' \
      "${repository_ids__any_deletion_list}" \
      "${repository_ids__any_to_be_decided}"
    printf ' AND `b_r`.`id` NOT IN (%s,%s);\n' \
      "${repository_ids__any_deletion_list}" \
      "${repository_ids__any_to_be_decided}"
    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` 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
    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

    # shellcheck disable=SC2041
    for arch in 'i686'; do
      for dir in $(ls_master_mirror "${arch}"); do
        ls_master_mirror "${arch}/${dir}" | \
          sed -n '
            s/\.pkg\.tar\.xz$//
            T
            s/-\([0-9]\+\)-\([^-]\+\)$/-\1.0-\2/
            s/-\([^-:]\+-[^-]\+-[^-]\+\)$/-0:\1/
            s|^|'"${arch}"'/'"${dir}"'/|
            p
          '
      done
    done | \
      sort > \
      "${temp_dir}/master-mirror-listing"

    {
      printf 'SELECT `r_a`.`name`,`repositories`.`name`,'
      printf '`binary_packages`.`%s`,' \
        'pkgname' \
        'epoch' \
        'pkgver' \
        'pkgrel' \
        'sub_pkgrel'
      printf '`architectures`.`name`'
      printf ' FROM `binary_packages_in_repositories`'
      mysql_join_binary_packages_in_repositories_binary_packages
      mysql_join_binary_packages_architectures
      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 -n '
        s/\.pkg\.tar\.xz\(\.sig\)\?$//
        T
        s/-\([0-9]\+\)-\([^-]\+\)$/-\1.0-\2/
        s/-\([^-:]\+-[^-]\+-[^-]\+\)$/-0:\1/
        p
      ' | \
      sort | \
      uniq -c | \
      grep '^\s*2\s' | \
      awk '{print $2}' > \
      "${temp_dir}/master-mirror-pool"

    {
      printf 'SELECT '
      printf '`binary_packages`.`%s`,' \
        'pkgname' \
        'epoch' \
        'pkgver' \
        'pkgrel' \
        'sub_pkgrel'
      printf '`architectures`.`name`'
      printf ' FROM `binary_packages`'
      mysql_join_binary_packages_architectures
      printf ' WHERE NOT EXISTS ('
        printf 'SELECT 1 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 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,\t,-,
        s,\t,:,
        s,\t,-,
        s,\t,.,
        s,\t,-,
      ' | \
      sort > \
      "${temp_dir}/mysql-packages-pool"

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

mysql_find_build_assignment_loops() {
  new_loops=$(
    {
      printf 'SELECT DISTINCT `packages_dependency`.`build_assignment`,`packages_dependent`.`build_assignment`'
      printf ' FROM `dependencies`'
      mysql_join_dependencies_install_target_providers
      mysql_join_install_target_providers_binary_packages '' 'packages_dependency'
      mysql_join_dependencies_binary_packages '' 'packages_dependent'
      mysql_join_binary_packages_binary_packages_in_repositories 'packages_dependency' 'packages_in_repository_dependency'
      mysql_join_binary_packages_binary_packages_in_repositories 'packages_dependent' 'packages_in_repository_dependent'
      printf ' WHERE `packages_in_repository_dependent`.`repository`=%s' \
        "${repository_ids__any_build_list}"
      printf ' AND `packages_in_repository_dependency`.`repository`=%s;\n' \
        "${repository_ids__any_build_list}"
    } | \
      mysql_run_query | \
      tr '\t' ' ' | \
      tsort 2>&1 >/dev/null | \
      sed 's/^tsort:\s*//' | \
      {
        loop=0
        while read -r id; do
          if [ "x${id}" = 'x-: input contains a loop:' ]; then
            loop=$((loop+1))
            continue
          fi
          if ! printf '%s' "${id}" | tr '\n' ' ' | grep -q '^[0-9]\+$'; then
            >&2 printf 'ERROR: non-numeric id "%s"\n' "${id}"
            continue
          fi
          printf '(%s,%s),' "${loop}" "${id}"
        done | \
          sed 's/,$//'
      }
  )
  {
    printf 'DELETE FROM `build_dependency_loops`;\n'
    if [ -n "${new_loops}" ]; then
      printf 'INSERT INTO `build_dependency_loops` (`loop`,`build_assignment`) VALUES %s;\n' \
        "${new_loops}"
    fi
  } | \
    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 and binary_packages_in_repositories
    printf '%s ' \
      "${operator}"
    if [ "${operator}" = 'DELETE' ]; then
      printf '`binary_packages`,`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 build_assignments w/o binary_package
    printf '%s FROM `build_assignments` ' \
      "${operator}"
    printf 'WHERE NOT EXISTS '
    printf '('
      printf 'SELECT 1 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 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`) 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) FROM `build_slaves`'
    fi
    printf ' WHERE `build_slaves`.`currently_building` IS NOT NULL'
    printf ' AND TIMEDIFF(NOW(),('
      printf 'SELECT MAX(`ssh_log`.`date`) 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) 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'
  } | \
    mysql_run_query 'unimportant'
}

# mysql_query_has_pending_dependencies `build_assignment`.`id`
# print a mysql query giving wether dependencies are pending
mysql_query_has_pending_dependencies() {
  printf 'EXISTS ('
    printf 'SELECT 1 FROM `binary_packages_in_repositories` as `todos_bpir`'
    mysql_join_binary_packages_in_repositories_binary_packages 'todos_bpir' 'todos'
    mysql_join_binary_packages_dependencies 'todos'
    mysql_join_dependencies_install_target_providers
    mysql_join_install_target_providers_binary_packages '' 'deps'
    mysql_join_binary_packages_binary_packages_in_repositories 'deps' 'deps_bpir'
    printf ' WHERE'
    printf ' `%s`.`repository`=%s AND' \
      'deps_bpir' "${repository_ids__any_build_list}" \
      'todos_bpir' "${repository_ids__any_build_list}"
    printf ' `deps`.`build_assignment`!=`todos`.`build_assignment` AND'
    printf ' `todos`.`build_assignment`=%s' \
      "$1"
  printf ')'
}

# mysql_query_is_part_of_loop `build_assignment`.`id`
# print a mysql query giving wether the package is part of a loop
mysql_query_is_part_of_loop() {
  printf 'EXISTS ('
    printf 'SELECT 1 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]
# 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}"
  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.tar.xz"' "${a_name}"
  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_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:dependency_type:dependency_types' \
  \
  'email_log:action:email_actions' \
  'email_log:gpg_key:gpg_keys' \
  \
  'failed_builds:reason:fail_reason' \
  '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' \
  \
  'package_sources:upstream_package_repository:upstream_repositories' \
  \
  '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_retrieve_static_information
# retrieve some static information from the database:
# - ids of package repositories -> $repository_ids__$arch_$repo
# - names and paths of git_repositories
# - ids of repository_stabilities
mysql_retrieve_static_information() {
  eval "$(
    {
      printf 'SELECT REPLACE(CONCAT('
      printf '"repository_ids__",'
      printf '`architectures`.`name`,"_",'
      printf '`repositories`.`name`,"=",'
      printf '`repositories`.`id`'
      printf '),"-","_") FROM `repositories`'
      mysql_join_repositories_architectures
      printf ';\n'
      printf 'SELECT CONCAT('
      printf '"repo_names=\\"",'
      printf 'REPLACE(GROUP_CONCAT(`git_repositories`.`name`),","," "),'
      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 ' FROM `git_repositories`;\n'
      printf 'SELECT CONCAT('
      printf '"repository_stability_ids__",'
      printf '`repository_stabilities`.`name`,"=",'
      printf '`repository_stabilities`.`id`)'
      printf ' FROM `repository_stabilities`;\n'
    } | \
      mysql_run_query
  )"
}

# TODO: give the real file names, not the file names reconstructed from
# the database (remember: $sub_pkgrel=0 may be omitted for older packages)

# 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 ' WHERE NOT EXISTS ('
    printf 'SELECT 1 FROM `binary_packages_in_repositories`'
    printf ' WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
  printf ');\n'
  printf 'DELETE `binary_packages` FROM `binary_packages`'
  printf ' WHERE NOT EXISTS ('
    printf 'SELECT 1 FROM `binary_packages_in_repositories`'
    printf ' WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
  printf ');\n'
}