bin/common-functions -> lib/common-functions, bin/mysql-functions -> lib/mysql-functions

1 files changed, 1098 insertions, 0 deletions

diff --git a/lib/mysql-functions b/lib/mysql-functions
new file mode 100755
index 0000000..185d9a9
--- /dev/null
+++ b/lib/mysql-functions
@@ -0,0 +1,1098 @@
+#!/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_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 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_command}
+}
+
+# mysql_add_binary_package $pkgbase $git_revision $mod_git_revision $upstream_package_repository $pkgname $sub_pkgrel $architecture $repository
+
+# shellcheck disable=SC2031,SC2086,SC2154
+mysql_add_binary_package() {
+  local names='pkgbase git_revision mod_git_revision upstream_package_repository pkgname sub_pkgrel architecture repository'
+  local name
+  for name in ${names}; do
+    eval 'local '"${name}"
+    eval "${name}"'=$(
+      printf "%s" "$1" |
+        base64 -w0
+    )'
+    shift
+  done
+
+  {
+    printf 'INSERT IGNORE INTO binary_packages'
+    printf ' ('
+    printf '`%s`, ' 'sub_pkgrel' 'pkgname' 'package_source' 'repository' 'architecture' 'has_issues' 'is_tested' 'is_to_be_deleted'
+    printf ') SELECT'
+    printf ' from_base64("%s"), ' "${sub_pkgrel}" "${pkgname}"
+    printf ' `%s`.`id`,' 'package_sources' 'repositories' 'architectures'
+    printf ' 0, 0, 0'
+    printf ' FROM'
+    printf ' `%s` JOIN' 'package_sources' 'repositories' 'architectures'
+    printf ' `upstream_repositories` ON `package_sources`.`upstream_package_repository` = `upstream_repositories`.`id`'
+    printf ' WHERE'
+    printf ' `%s`.`name` = from_base64("%s") AND' \
+      'repositories' "${repository}" \
+      'architectures' "${architecture}"
+    printf ' `package_sources`.`%s` = from_base64("%s") AND' \
+      'pkgbase' "${pkgbase}" \
+      'git_revision' "${git_revision}" \
+      'mod_git_revision' "${mod_git_revision}"
+    printf ' `upstream_repositories`.`name` = from_base64("%s")' \
+      "${upstream_package_repository}"
+  } | \
+    sed '
+      s|, )|)|g
+      s|, FROM| FROM|g
+    ' | \
+    ${mysql_command}
+}
+
+# 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_command} --html
+}
+
+# mysql_add_install_target $install_target
+
+# shellcheck disable=2086
+mysql_add_install_target() {
+  local install_target
+  install_target=$(
+    printf "%s" "$1" | \
+      base64 -w0
+  )
+
+  {
+    printf 'INSERT IGNORE INTO `install_targets` (`name`)'
+    printf ' VALUES (from_base64("%s"))' \
+      "${install_target}"
+  } | \
+    ${mysql_command}
+}
+
+# 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 | \
+            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 ' (from_base64("%s"));\n' \
+            "${pkgname}"
+
+          printf 'INSERT INTO `rundepends` VALUES\n'
+          echo "${rundepends}" | \
+            sort -u | \
+            grep -vxF '' | \
+            sed '
+              s|^| (from_base64("|
+              s|$|")),|
+            '
+          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_command}
+    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_command} --raw --batch | \
+    sed '
+      /^CONCAT("/d
+      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_command} --batch | \
+      sed '
+        1d
+        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_command} --raw --batch | \
+      sed '
+        1d
+        y/\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_command}
+}
+
+# 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(*)'
+  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'
+    printf 'UPDATE `build_slaves`'
+    mysql_join_build_slaves_binary_packages
+    mysql_join_binary_packages_repositories
+    printf ' SET `currently_building`=NULL'
+    printf ' WHERE `repositories`.`name`!="build-list";\n'
+  } | \
+    ${mysql_command}
+}
+
+# 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_command} --raw --batch | \
+    sed '1d' | \
+    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_command} --raw --batch | \
+          sed '1d' | \
+          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_command}
+}
+
+# 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_command}
+}
+
+# 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' \
+ \
+  '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' \
+ \
+  '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