path: root/lib/mysql-functions

#!/bin/sh

# contains functions used to access mysql db

# shellcheck disable=SC2016,SC2039

if [ -z "${base_dir}" ]; then
  # just to make shellcheck happy
  . '../conf/default.conf'
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_file
  if [ -s "${work_dir}/build-master-sanity" ]; then
  # If the build master is insane, the calling command should only check
  # if the build master is still insane - we do not want to log that.
    ${mysql_command} -N --raw --batch "$@"
  else
    # we save the query in a file and delete that file if the query succeeded
    query_file=$(mktemp "${work_dir}/tmp.mysql-functions.query.$(date +'%Y-%m-%dT%T').XXXXXX")
    cat > "${query_file}"
    for _ in {1..10}; do
      {
        printf '%s %s: ' "$0" "${mysql_command} -N --raw --batch $*"
        date
      } \
        | tee -a "${work_dir}/mysql.stdin" \
        | tee -a "${work_dir}/mysql.stdout" \
        >> "${work_dir}/mysql.stderr"
      cat "${query_file}" \
        >> "${work_dir}/mysql.stdin"
      {
        ${mysql_command} -N --raw --batch "$@" \
          < "${query_file}" \
          2>> "${work_dir}/mysql.stderr" \
          && rm "${query_file}"
      } \
        | tee -a "${work_dir}/mysql.stdout"
      if ! [ -f "${query_file}" ]; then
        # success!
        for s in \
          "${work_dir}/mysql.stdin" \
          "${work_dir}/mysql.stdout" \
          "${work_dir}/mysql.stderr"; do
          {
            tail -n 10000 "$s"
            printf '%s %s done: ' "$0" "${mysql_command} $*"
            date
          } \
            | sponge "$s"
        done
        break
      fi
      for s in \
        "${work_dir}/mysql.stdin" \
        "${work_dir}/mysql.stdout" \
        "${work_dir}/mysql.stderr"; do
        {
          printf '%s %s FAILED: ' "$0" "${mysql_command} $*"
          date
        } \
          >> "$s"
      done
    done
    # a present query_file means there was an error
    if [ -f "${query_file}" ]; then
      >&2 printf 'I could not complete a mysql query!\n'
      if [ ! -s "${work_dir}/build-master-sanity" ]; then
        printf '\001ACTION failed to execute a mysql query - can you have a look at "%s"?.\001\n' \
          "${query_file##*/}" \
          | irc_say
      fi
      echo 'A mysql query failed.' > \
        "${work_dir}/build-master-sanity"
      return 2
    fi
  fi
}

# mysql_add_package_source $pkgbase $git_revision $mod_git_revision $upstream_package_repository

# shellcheck disable=SC2086
mysql_add_package_source() {
  local names='pkgbase git_revision mod_git_revision upstream_package_repository'
  local values
  local uses_upstream
  local uses_modification
  local repo

  if git -C "${repo_paths__archlinux32}" archive "$3" -- "$4/$1" >/dev/null 2>&1; then
    uses_modification=1
  else
    uses_modification=0
  fi
  uses_upstream=0
  for repo in ${repo_names}; do
    if [ "${repo}" = 'archlinux32' ]; then
      continue
    fi
    if eval 'git -C "${repo_paths__'"${repo}"'}" archive "$2" -- "$1/repos/$4-*/PKGBUILD" 2>/dev/null' | \
      tar -t 2>/dev/null | \
      sed 's,-x86_64/,-any/,' | \
      grep -qFx "$1/repos/$4-any/PKGBUILD"; then
      uses_upstream=1
    fi
  done

  for _ in ${names}; do
    values="${values}$(
      printf '%s' "$1" | \
        base64 -w0
    ) "
    shift
  done
  values="${values% }"

  {
    printf 'INSERT IGNORE INTO package_sources'
    printf ' ('
    printf '`%s`, ' ${names}
    printf '`uses_upstream`,`uses_modification`'
    printf ') SELECT'
    printf ' from_base64("%s"), ' ${values% *}
    printf ' `upstream_repositories`.`id`,%s,%s' \
      ${uses_upstream} ${uses_modification}
    printf ' FROM `upstream_repositories`'
    printf ' WHERE `upstream_repositories`.`name` = from_base64("%s");' \
      "${values##* }"
  } | \
    mysql_run_query
}

# mysql_show_binary_package $pkgname $pkgver $pkgrel $sub_pkgrel

# shellcheck disable=SC2031,SC2086,SC2154
mysql_show_binary_package() {
  local names='pkgname pkgver pkgrel sub_pkgrel'
  local name
  for name in ${names}; do
    eval 'local '"${name}"
    eval "${name}"'=$(
      printf "%s" "$1" |
        base64 -w0
    )'
    shift
  done

  {
    printf 'SELECT'
    printf ' `%s`.`%s`,' \
      'repositories' 'name' \
      'binary_packages' 'pkgname' \
      'package_sources' 'pkgver' \
      'package_sources' 'pkgrel' \
      'binary_packages' 'sub_pkgrel' \
      'architectures' 'name' \
      'package_sources' 'pkgbase' \
      'package_sources' 'git_revision' \
      'package_sources' 'mod_git_revision' \
      'upstream_repositories' 'name'
    printf ' FROM `binary_packages`'
    mysql_join_binary_packages_architectures
    mysql_join_binary_packages_repositories
    mysql_join_binary_packages_build_assignments
    mysql_join_build_assignments_package_sources
    mysql_join_package_sources_upstream_repositories
    printf ' WHERE'
    printf ' `%s`.`%s` = from_base64("%s") AND' \
      'binary_packages' 'pkgname' "${pkgname}" \
      'binary_packages' 'sub_pkgrel' "${sub_pkgrel}" \
      'package_sources' 'pkgver' "${pkgver}" \
      'package_sources' 'pkgrel' "${pkgrel}"
    printf ';'
  } | \
    sed '
      s|, FROM| FROM|g
      s|AND;|;|g
    ' | \
    mysql_run_query --html --column-names
}

# mysql_generate_package_metadata $current_repository $package $git_revision $mod_git_revision $repository
# or
# mysql_generate_package_metadata $current_repository $package.$git_revision.$mod_git_revision.$repository
# if sub_pkgrel should be determined automatically
# and
# mysql_generate_package_metadata $sub_pkgrel $current_repository $package $git_revision $mod_git_revision $repository
# or
# mysql_generate_package_metadata $sub_pkgrel $current_repository $package.$git_revision.$mod_git_revision.$repository
# if $sub_pkgrel should be forced

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

mysql_generate_package_metadata() {

  ( # new shell is intentional
    case "$1" in
      ''|*[!0-9]*)
        unset forced_sub_pkgrel
      ;;
      *)
        forced_sub_pkgrel=$(
          printf '%s' "$1" | \
            base64 -w0
        )
        shift
      ;;
    esac
    current_repository="$1"
    package="$2"

    if [ $# -eq 2 ]; then
      # second form
      repository="${package##*.}"
      package="${package%.*}"
      mod_git_revision="${package##*.}"
      package="${package%.*}"
      git_revision="${package##*.}"
      package="${package%.*}"
    else
      git_revision="$3"
      mod_git_revision="$4"
      repository="$5"
    fi

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

    printf '.' >&2
    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
    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"

    printf '.' >&2
    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
    mysql_add_package_source "${pkgbase}" "${git_revision}" "${mod_git_revision}" "${repository}"
    printf '.' >&2

    # now we encode everything in base64
    current_repository=$(
      printf '%s' "${current_repository}" | \
        base64 -w0
    )
    pkgbase=$(
      printf '%s' "${pkgbase}" | \
        base64 -w0
    )
    git_revision=$(
      printf '%s' "${git_revision}" | \
        base64 -w0
    )
    mod_git_revision=$(
      printf '%s' "${mod_git_revision}" | \
        base64 -w0
    )
    repository=$(
      printf '%s' "${repository}" | \
        base64 -w0
    )

    # add the build assignment(s)
    {
      archs=$(
        sed -n '
          s/^\tarch = //
          T
          p
        ' "${temp_dir}/SRCINFO" | \
          grep -vxF 'any' | \
          sort -u
      )
      if [ -z "${archs}" ]; then
        echo 'any'
      else
        printf '%s\n' "${archs}"
      fi
    } | \
      while read -r arch; do
        printf 'INSERT IGNORE INTO `build_assignments` (`package_source`,`architecture`,`is_blocked`,`is_broken`,`priority`)'
        printf ' SELECT `package_sources`.`id`,`architectures`.`id`,NULL,0,0'
        printf ' FROM `architectures` JOIN `package_sources`'
        printf ' WHERE `architectures`.`name` = from_base64("%s")' \
          "$(
            printf '%s' "${arch}" | \
              base64 -w0
          )"
        printf ' AND `package_sources`.`%s` = from_base64("%s")' \
          'pkgbase' "${pkgbase}" \
          'git_revision' "${git_revision}" \
          'mod_git_revision' "${mod_git_revision}"
        printf ';\n'
      done > \
      "${temp_dir}/add-build-assignments-command"

    # TODO: correctly link between binary_packages and build_assignments using any_arch

    # shellcheck disable=SC2034
    # select any specific arch (which will be building the 'any' part of a split package)
    any_arch=$(
      {
        sed -n '
          s/^\tarch = //
          T
          p
        ' "${temp_dir}/SRCINFO" | \
          sort -r | \
          grep -vxFm 1 'any' || \
          echo 'any'
      } | \
        base64_encode_each
    )

    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}")"'$/{
            :a
              N
              /\n$/{
                p
                T
              }
              ba
          }
        ' "${temp_dir}/SRCINFO" | \
          sed '
            /^\S/d
            s/^\s*//
          ' > \
          "${temp_dir}/BINARYINFO.${pkgname64}"

        grep '^arch = ' "${temp_dir}/BINARYINFO.${pkgname64}" | \
          cut -d' ' -f3 | \
          while read -r arch; do
            arch64=$(
              printf '%s' "${arch}" | \
                base64 -w0
            )
            sed '
              s/^\(\S\+\)_'"${arch}"' = /\1 = /
            ' "${temp_dir}/BINARYINFO.${pkgname64}" > \
              "${temp_dir}/ARCHINFO ${pkgname64} ${arch64}"
          done
      done
    find "${temp_dir}" -mindepth 1 -maxdepth 1 -name 'ARCHINFO * *' -printf '%f\n' | \
      while read -r _ pkgname arch; do
        pkgver=$(
          grep '^pkgver = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            base64_encode_each
        )
        pkgrel=$(
          grep '^pkgrel = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \
            cut -d' ' -f3 | \
            sed 's/\.[0-9]\+$//' | \
            base64_encode_each
        )
        epoch=$(
          {
            grep '^epoch = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" || \
              echo 'epoch = 0'
          } | \
            cut -d' ' -f3 | \
            base64_encode_each
        )
        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
        )
        if [ -n "${forced_sub_pkgrel}" ]; then
          sub_pkgrel='from_base64("'"${forced_sub_pkgrel}"'")'
        else
          sub_pkgrel=$(
            printf '(SELECT COALESCE('
            # do not add binary packages which are currently on the
            # build-list or in $current_repository (beware of split
            # packages!)
            printf '(SELECT `sub_pkgrel` FROM `binary_packages`'
            mysql_join_binary_packages_architectures
            mysql_join_binary_packages_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 `repositories`.`name` IN ("build-list",from_base64("%s"))),' \
              "${current_repository}"
            # 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))'
          )
        fi
        {
          printf 'INSERT IGNORE INTO `binary_packages` ('
          printf '`%s`,' \
            'build_assignment' \
            'repository' \
            'architecture' \
            'epoch' \
            'pkgver' \
            'pkgrel' \
            'pkgname' \
            'sub_pkgrel' \
            'has_issues' \
            'is_tested' \
            'is_to_be_deleted'
          printf ') SELECT '
          printf '`%s`.`id`,' \
            'build_assignments' \
            'repositories' \
            'architectures'
          printf 'from_base64("%s"),' \
            "${epoch}" \
            "${pkgver}" \
            "${pkgrel}" \
            "${pkgname}"
          printf '%s,0,0,0 FROM' \
            "${sub_pkgrel}"
          printf ' `%s` JOIN' \
            'repositories' \
            'architectures' \
            'build_assignments'
          mysql_join_build_assignments_package_sources
          mysql_join_package_sources_upstream_repositories
          printf ' WHERE'
          printf ' `%s`.`%s` = from_base64("%s") AND' \
            'repositories' 'name' "${current_repository}" \
            'architectures' 'name' "${arch}" \
            'package_sources' 'pkgbase' "${pkgbase}" \
            'package_sources' 'git_revision' "${git_revision}" \
            'package_sources' 'mod_git_revision' "${mod_git_revision}" \
            'upstream_repositories' 'name' "${repository}"
          printf ';\n'
        } | \
          sed '
            s|,)|)|g
            s| JOIN JOIN | JOIN |
            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
            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
    printf '.' >&2

    {
      if [ -s "${temp_dir}/add-build-assignments-command" ]; then
        cat "${temp_dir}/add-build-assignments-command"
      fi
      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
    printf '.' >&2

  )
}

# 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 on build list: ",`a`.`pkgname`)'
    printf ' FROM `binary_packages` AS `a`'
    printf ' JOIN `binary_packages` AS `b`'
    printf ' ON `a`.`pkgname`=`b`.`pkgname`'
    printf ' AND `a`.`repository`=`b`.`repository`'
    printf ' AND `a`.`id`!=`b`.`id`'
    mysql_join_binary_packages_repositories 'a'
    printf ' WHERE `repositories`.`name`="build-list";\n'
    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_repositories 'a' 'arep'
    mysql_join_binary_packages_repositories 'b' 'brep'
    printf ' WHERE `a`.`sub_pkgrel`!=`b`.`sub_pkgrel`'
    printf ' AND `%srep`.`name`="build-list"' \
      'a' 'b'
    printf ';\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

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

    {
      printf 'SELECT `repositories`.`name`,`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architectures`.`name`'
      printf ' FROM `binary_packages`'
      mysql_join_binary_packages_architectures
      mysql_join_binary_packages_repositories
      printf ' WHERE `repositories`.`is_on_master_mirror`'
    } | \
      mysql_run_query | \
      sed '
        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"
  )
}

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_repositories 'packages_dependency' 'repositories_dependency'
      mysql_join_binary_packages_repositories 'packages_dependent' 'repositories_dependent'
      printf ' WHERE `repositories_dependent`.`name`="build-list" AND `repositories_dependency`.`name`="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
  local partition
  if [ "$#" = '0' ]; then
    operator='DELETE'
  elif [ "$#" = '1' ] && [ "x$1" = 'xdry' ]; then
    operator='SELECT COUNT(*)'
  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
    printf '%s ' \
      "${operator}"
    if [ "${operator}" = 'DELETE' ]; then
      printf '`binary_packages` '
    fi
    printf 'FROM `binary_packages`'
    mysql_join_binary_packages_repositories
    printf ' WHERE `repositories`.`name`="to-be-decided";\n'
    # remove dependencies w/o binary_package or install_target
    printf '%s FROM `dependencies` ' \
      "${operator}"
    printf 'WHERE NOT EXISTS '
    printf '('
      printf 'SELECT * FROM `binary_packages` '
      printf 'WHERE `dependencies`.`dependent`=`binary_packages`.`id`'
    printf ') OR NOT EXISTS '
    printf '('
      printf 'SELECT * FROM `install_targets` '
      printf 'WHERE `dependencies`.`depending_on`=`install_targets`.`id`'
    printf ');\n'
    # remove install_target_providers w/o binary_package or install_target
    printf '%s FROM `install_target_providers` ' \
      "${operator}"
    printf 'WHERE NOT EXISTS '
    printf '('
      printf 'SELECT * FROM `binary_packages` '
      printf 'WHERE `install_target_providers`.`package`=`binary_packages`.`id`'
    printf ') OR NOT EXISTS '
    printf '('
      printf 'SELECT * FROM `install_targets` '
      printf 'WHERE `install_target_providers`.`install_target`=`install_targets`.`id`'
    printf ');\n'
    # remove build_assignments w/o binary_package
    printf '%s FROM `build_assignments` ' \
      "${operator}"
    printf 'WHERE NOT EXISTS '
    printf '('
      printf 'SELECT * 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 failed_builds w/o build_assignment
    printf '%s FROM `failed_builds` ' \
      "${operator}"
    printf 'WHERE NOT EXISTS '
    printf '('
      printf 'SELECT * FROM `build_assignments` '
      printf 'WHERE `build_assignments`.`id`=`failed_builds`.`build_assignment`'
    printf ');\n'
    # remove package_sources w/o build_assignment
    printf '%s FROM `package_sources` ' \
      "${operator}"
    printf 'WHERE NOT EXISTS '
    printf '('
      printf 'SELECT * 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 `build_slaves`'
    else
      printf 'SELECT COUNT(DISTINCT `build_slaves`.`id`) FROM `build_slaves`'
    fi
    mysql_join_build_slaves_binary_packages
    mysql_join_binary_packages_repositories
    if [ "${operator}" = 'DELETE' ]; then
      printf ' SET `build_slaves`.`currently_building`=NULL'
    fi
    printf ' WHERE `repositories`.`name`!="build-list";\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(*) FROM `build_slaves`'
    fi
    printf ' WHERE `build_slaves`.`currently_building` IS NOT NULL'
    printf ' AND TIMEDIFF(NOW(),`build_slaves`.`last_connection`) > "1:00:00";\n'
    # remove `logged_lines` from buildslaves without an assignment
    if [ "${operator}" = 'DELETE' ]; then
      printf 'UPDATE `build_slaves`'
      printf ' SET `build_slaves`.`logged_lines`=NULL'
    else
      printf 'SELECT COUNT(*) FROM `build_slaves`'
    fi
    printf ' WHERE `build_slaves`.`currently_building` IS NULL'
    printf ' AND `build_slaves`.`logged_lines` IS NOT NULL;\n'
    for partition in $(seq 0 15); do
      # remove obsolete file_providers
      printf '%s FROM `file_providers` PARTITION (p%s)' \
        "${operator}" "${partition}"
      printf ' WHERE NOT EXISTS ('
        printf 'SELECT * FROM `binary_packages`'
        printf ' WHERE `binary_packages`.`id`=`file_providers`.`package`'
      printf ') OR NOT EXISTS ('
        printf 'SELECT * FROM `files` PARTITION (p%s)' \
          "${partition}"
        printf ' WHERE `files`.`id`=`file_providers`.`file`'
        printf ' AND `files`.`name_hash`=`file_providers`.`name_hash`'
      printf ');\n'
      # remove obsolete files
      printf '%s FROM `files` PARTITION (p%s)' \
        "${operator}" "${partition}"
      printf ' WHERE NOT EXISTS ('
        printf 'SELECT * FROM `file_providers` PARTITION (p%s)' \
          "${partition}"
        printf ' WHERE `files`.`id`=`file_providers`.`file`'
        printf ' AND `files`.`name_hash`=`file_providers`.`name_hash`'
      printf ');\n'
    done
  } | \
    mysql_run_query
}

# 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 * FROM `binary_packages` as `to_dos`'
    mysql_join_binary_packages_repositories 'to_dos' 'to_do_repos'
    mysql_join_binary_packages_dependencies 'to_dos'
    mysql_join_dependencies_install_target_providers
    mysql_join_install_target_providers_binary_packages '' 'bin_deps'
    mysql_join_binary_packages_repositories 'bin_deps' 'dep_repos'
    printf ' WHERE'
    printf ' `%s`.`name`="build-list" AND' \
      'dep_repos' 'to_do_repos'
    printf ' `bin_deps`.`build_assignment`!=`to_dos`.`build_assignment` AND'
    printf ' `to_dos`.`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 * 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_repair_binary_packages_without_build_assignment
# try to generate valid build assignments to binary packages without
# a valid one yet
mysql_repair_binary_packages_without_build_assignment() {
  {
    printf 'SELECT '
    printf '`binary_packages`.`id`'
    printf ',replace(to_base64(%s),"\\n","")' \
      '`binary_packages`.`pkgname`' \
      '`architectures`.`name`'
    printf ' FROM `binary_packages`'
    mysql_join_binary_packages_architectures
    printf ' WHERE `binary_packages`.`build_assignment`<0'
  } | \
    mysql_run_query | \
    while read -r id pkgname arch; do
      pkgname=$(
        printf '%s' "${pkgname}" | \
          base64 -d
      )
      pkgbase=$(
        curl -Ss "$(
          printf 'https://www.archlinux.org/packages/search/json/?name=%s' \
            "${pkgname}"
        )" | \
          sed '
            s/^.*"results":\s*\[//
            s/}\s*,\s*{/\n/g
          ' | \
          grep '"pkgname":\s*"'"$(str_to_regex "${pkgname}")"'"' | \
          tr ',' '\n' | \
          grep '"pkgbase":' | \
          cut -d'"' -f4 | \
          sort -u | \
          head -n1
      )
      if [ -z "${pkgbase}" ] && \
        {
          printf 'SELECT count(*) FROM `package_sources`'
          printf ' WHERE `package_sources`.`pkgbase`=from_base64("%s")' \
            "$(printf '%s' "${pkgname}" | base64 -w0)"
        } | \
          mysql_run_query | \
          grep -qvxF '0'; then
        pkgbase="${pkgname}"
      fi
      if [ -z "${pkgbase}" ]; then
        >&2 printf 'Could not find "%s" upstream.\n' "${pkgname}"
        continue
      fi
      pkgbase=$(
        printf '%s' "${pkgbase}" | \
          base64 -w0
      )
      printf 'INSERT IGNORE INTO `build_assignments` (`package_source`,`architecture`,`is_blocked`,`is_broken`,`priority`)'
      printf ' SELECT `package_sources`.`id`,`architectures`.`id`,0,0,0'
      printf ' FROM `package_sources`'
      printf ' JOIN `architectures`'
      printf ' WHERE `package_sources`.`pkgbase`=from_base64("%s")' "${pkgbase}"
      printf ' AND `architectures`.`name`=from_base64("%s")' "${arch}"
      printf ' LIMIT 1;\n'
      printf 'UPDATE `binary_packages`'
      printf ' JOIN `build_assignments`'
      mysql_join_binary_packages_build_assignments
      printf ' SET `binary_packages`.`build_assignment`=`build_assignments`.`id`'
      printf ' WHERE `binary_packages`.`id`=%s' "${id}"
      printf ' AND `package_sources`.`pkgbase`=from_base64("%s");\n' "${pkgbase}"
    done | \
    mysql_run_query
}

# mysql_remove_duplicate_binary_packages
# remove duplicate binary_packages, matching pkgname, epoch, pkgver, pkgrel,
# having difference of 1 in sub_pkgrel

mysql_remove_duplicate_build_order() {
  {
    printf 'CREATE TEMPORARY TABLE `ren`'
    printf ' (`old` BIGINT, `new` BIGINT, `repo` BIGINT, `sub_pkgrel` BIGINT);\n'
    printf 'INSERT INTO `ren` (`old`,`new`,`repo`,`sub_pkgrel`)'
    printf ' SELECT `old`.`id`,`new`.`id`,`old`.`repository`,`old`.`sub_pkgrel`'
    printf ' FROM `binary_packages` as `old`'
    printf ' JOIN `binary_packages` as `new` ON'
    printf ' `old`.`%s`=`new`.`%s` AND' \
      'pkgname' 'pkgname' \
      'epoch' 'epoch' \
      'pkgver' 'pkgver' \
      'pkgrel' 'pkgrel'
    printf ' `old`.`sub_pkgrel`+1=`new`.`sub_pkgrel`'
    mysql_join_binary_packages_repositories 'old' 'orep'
    mysql_join_binary_packages_repositories 'new' 'nrep'
    printf ' WHERE `orep`.`name`!="to-be-decided"'
    printf ' AND `nrep`.`name`="to-be-decided";\n'
    printf 'UPDATE IGNORE `dependencies`'
    printf ' JOIN `ren` ON `ren`.`old`=`dependencies`.`dependent`'
    printf ' SET `dependencies`.`dependent`=`ren`.`new`;\n'
    printf 'UPDATE IGNORE `install_target_providers`'
    printf ' JOIN `ren` ON `ren`.`old`=`install_target_providers`.`package`'
    printf ' SET `install_target_providers`.`package`=`ren`.`new`;\n'
    printf 'DELETE FROM `binary_packages`'
    printf ' WHERE EXISTS ('
      printf 'SELECT * FROM `ren`'
      printf ' WHERE `ren`.`old`=`binary_packages`.`id`'
    printf ');\n'
    printf 'UPDATE IGNORE `binary_packages`'
    printf ' JOIN `ren` ON `ren`.`new`=`binary_packages`.`id`'
    printf ' SET `binary_packages`.`repository`=`ren`.`repo`,'
    printf ' `binary_packages`.`sub_pkgrel`=`ren`.`sub_pkgrel`;\n'
  } | \
    mysql_run_query
}

# mysql_package_name_query
# print a mysql query of the full name of a package file

mysql_package_name_query() {
  printf 'CONCAT('
  printf '`binary_packages`.`pkgname`,"-",'
  printf 'IF(`binary_packages`.`epoch`=0,"",CONCAT(`binary_packages`.`epoch`,":")),'
  printf '`binary_packages`.`pkgver`,"-",'
  printf '`binary_packages`.`pkgrel`,".",'
  printf '`binary_packages`.`sub_pkgrel`,"-",'
  printf '`architectures`.`name`,".pkg.tar.xz"'
  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 \
  'binary_packages:architecture:architectures' \
  'binary_packages:repository:repositories' \
  'binary_packages:build_assignment:build_assignments' \
  \
  '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' \
  \
  'dependencies:depending_on:install_targets' \
  'dependencies:dependent:binary_packages' \
  'dependencies:dependency_type:dependency_types' \
  \
  'failed_builds:reason:fail_reason' \
  'failed_builds:build_assignment:build_assignments' \
  'failed_builds:build_slave:build_slaves' \
  \
  'file_providers:package:binary_packages' \
  'file_providers:file:files' \
  \
  'install_target_providers:package:binary_packages' \
  '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' \
  \
  'repository_moves:upstream_package_repository:upstream_repositories' \
  'repository_moves:upstream_package_repository upstream_package_repository:package_sources' \
  \
  '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_insert_package_files_query $file_list_file
# Writes a query, inserting files listed in $file_list_file into database.
# The file is expected to have the following \t separated columns:
# pkgname epoch pkgver pkgrel sub_pkgrel arch path name
mysql_insert_package_files_query() {

  local partition
  local partitions_count

  partitions_count=16

  if [ ! -r "$1" ]; then
    >&2 printf 'mysql_insert_package_files: File "%s" is not readable.\n' \
      "$1"
    return 2
  fi

  printf 'DROP TEMPORARY TABLE IF EXISTS `pkg_files`;\n'
  printf 'CREATE TEMPORARY TABLE `pkg_files` ('
  printf '`pkgname` VARCHAR(64),'
  printf '`epoch` MEDIUMINT,'
  printf '`pkgver` VARCHAR(64),'
  printf '`pkgrel` MEDIUMINT,'
  printf '`sub_pkgrel` MEDIUMINT,'
  printf '`arch` VARCHAR(16),'
  printf '`path` TEXT,'
  printf '`name` TEXT,'
  printf '`absolute_name_hash` VARCHAR(56),'
  printf '`name_hash` MEDIUMINT'
  printf ');\n'
  printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `pkg_files`' \
    "${1}"
  printf ' (`pkgname`, `epoch`, `pkgver`, `pkgrel`, `sub_pkgrel`, `arch`, `path`, `name`);\n'
  printf 'UPDATE `pkg_files` SET'
  printf ' `pkg_files`.`name_hash`=CAST(CONV(SUBSTR(SHA2(`pkg_files`.`name`,224),1,2),16,10) AS INT),'
  printf ' `pkg_files`.`absolute_name_hash`=SHA2(CONCAT(`pkg_files`.`path`,`pkg_files`.`name`),224);\n'
  for partition in $(seq 0 $((partitions_count-1))); do
    printf 'INSERT IGNORE INTO `files` PARTITION (p%s) (`path`,`name`,`name_hash`,`absolute_name_hash`)' \
      "${partition}"
    printf ' SELECT '
    printf '`pkg_files`.`%s`,' \
      'path' 'name' 'name_hash' 'absolute_name_hash' | \
      sed 's/,$//'
    printf ' FROM `pkg_files`'
    printf ' WHERE `pkg_files`.`name_hash` MOD %s = %s;\n' \
      "${partitions_count}" "${partition}"
    printf 'INSERT IGNORE INTO `file_providers` PARTITION (p%s) (`package`,`file`,`file_name_hash`)' \
      "${partition}"
    printf ' SELECT `binary_packages`.`id`,`files`.`id`,`files`.`name_hash` FROM `pkg_files`'
    printf ' JOIN `binary_packages` ON'
    printf ' `binary_packages`.`%s`=`pkg_files`.`%s` AND' \
      'pkgname' 'pkgname' \
      'epoch' 'epoch' \
      'pkgver' 'pkgver' \
      'pkgrel' 'pkgrel' \
      'sub_pkgrel' 'sub_pkgrel' | \
      sed 's/ AND$//'
    if [ -n "${slave}" ]; then
      mysql_join_binary_packages_build_slaves
      printf ' AND `build_slaves`.`name`=from_base64("%s")' \
        "$(
          printf '%s' "${slave}" | \
            base64 -w0
        )"
    fi
    mysql_join_binary_packages_architectures
    printf ' AND `pkg_files`.`arch`=`architectures`.`name`'
    mysql_join_binary_packages_repositories
    if [ -n "${slave}" ]; then
      printf ' AND `repositories`.`name`="build-list"'
    else
      printf ' AND `repositories`.`is_on_master_mirror`'
    fi
    printf ' JOIN `files` PARTITION (p%s)' \
      "${partition}"
    printf ' ON `files`.`name_hash`=`pkg_files`.`name_hash`'
    printf ' AND `files`.`absolute_name_hash`=`pkg_files`.`absolute_name_hash`'
    printf ' WHERE `pkg_files`.`name_hash` MOD %s = %s;\n' \
      "${partitions_count}" "${partition}"
  done

}