Merge branch 'master' into fixes

19 files changed, 930 insertions, 429 deletions

diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql
index 822d995..35f5398 100755
--- a/bin/bootstrap-mysql
+++ b/bin/bootstrap-mysql
@@ -367,6 +367,25 @@ if [ ! "$1" = 'slim' ]; then
       printf 'CREATE TEMPORARY TABLE `moveable_binary_packages%s` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n' \
         "${copy}"
     done
+    printf 'DROP TEMPORARY TABLE IF EXISTS `package_blobs`;\n'
+    printf 'CREATE TEMPORARY TABLE `package_blobs` (`ps_a` BIGINT, `ps_b` BIGINT, UNIQUE KEY `content` (`ps_a`,`ps_b`));\n'
+    # all packages being vaguely relevant enter `package_blobs`, because
+    # they might block any other package from moving
+    printf 'INSERT IGNORE INTO `package_blobs` (`ps_a`,`ps_b`)'
+    printf ' SELECT `a_ps`.`id`,`b_ps`.`id`'
+    printf ' FROM `package_sources` AS `a_ps`'
+    printf ' JOIN `package_sources` AS `b_ps`'
+    printf ' ON UNIX_TIMESTAMP(`a_ps`.`commit_time`) - UNIX_TIMESTAMP(`b_ps`.`commit_time`) BETWEEN -10 AND 10'
+    mysql_join_package_sources_build_assignments 'a_ps' 'a_ba'
+    mysql_join_package_sources_build_assignments 'b_ps' 'b_ba'
+    mysql_join_build_assignments_binary_packages 'a_ba' 'a_bp'
+    mysql_join_build_assignments_binary_packages 'b_ba' 'b_bp'
+    mysql_join_binary_packages_repositories 'a_bp' 'a_r'
+    mysql_join_binary_packages_repositories 'b_bp' 'b_r'
+    mysql_join_repositories_repository_stabilities 'a_r' 'a_rs'
+    mysql_join_repositories_repository_stabilities 'b_r' 'b_rs'
+    printf ' WHERE `a_rs`.`name` = `from_stability`'
+    printf ' AND `b_rs`.`name` = `from_stability`;\n'
     # these packages are considered for moving:
     printf 'INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)'
     printf ' SELECT `binary_packages`.`id`,`repository_moves`.`to_repository`'
@@ -408,6 +427,8 @@ if [ ! "$1" = 'slim' ]; then
     # now we delete all unreplaceable and unmoveable packages from the respective
     # list until no further changes are required
     printf 'REPEAT\n'
+    printf 'SET row_count_saved = 0;\n'
+
     # create copies of our temporary tables *yuck*
     for table in 'replaced' 'moveable'; do
       for copy in '' '2'; do
@@ -433,23 +454,23 @@ if [ ! "$1" = 'slim' ]; then
     printf ' AND `dependency_types`.`relevant_for_binary_packages`'
     # ... by a not-deleted, "more stable" package already in place or ...
     printf ' WHERE NOT EXISTS ('
-      printf 'SELECT * FROM `install_target_providers`'
+      printf 'SELECT 1 FROM `install_target_providers`'
       mysql_join_install_target_providers_binary_packages '' 'prov_bp'
       mysql_join_binary_packages_repositories 'prov_bp' 'prov_r'
       printf ' JOIN `repository_stability_relations` ON `prov_r`.`stability`=`repository_stability_relations`.`more_stable`'
       printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
       printf ' AND `target_repositories`.`stability`=`repository_stability_relations`.`less_stable`'
       printf ' AND NOT EXISTS ('
-        printf 'SELECT * FROM `replaced_binary_packages_copy`'
+        printf 'SELECT 1 FROM `replaced_binary_packages_copy`'
         printf ' WHERE `replaced_binary_packages_copy`.`id`=`prov_bp`.`id`'
       printf ')'
     # ... by a moved package
     printf ') AND NOT EXISTS ('
-      printf 'SELECT * FROM `install_target_providers`'
+      printf 'SELECT 1 FROM `install_target_providers`'
       printf ' JOIN `moveable_binary_packages_copy` ON `moveable_binary_packages_copy`.`id`=`install_target_providers`.`package`'
       printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
     printf ');\n'
-    printf 'SET @row_count_saved = ROW_COUNT();\n'
+    printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n'
 
     # refresh copies of our temporary tables *yuck*
     for table in 'replaced' 'moveable'; do
@@ -485,7 +506,7 @@ if [ ! "$1" = 'slim' ]; then
     mysql_join_install_target_providers_dependencies
     printf ' AND NOT EXISTS ('
       # dependencies of replaced packages don't matter
-      printf 'SELECT * FROM `replaced_binary_packages_copy`'
+      printf 'SELECT 1 FROM `replaced_binary_packages_copy`'
       printf ' WHERE `replaced_binary_packages_copy`.`id`=`dependencies`.`dependent`'
     printf ')'
     mysql_join_dependencies_dependency_types
@@ -502,7 +523,7 @@ if [ ! "$1" = 'slim' ]; then
     # a) ^
     printf ' WHERE NOT EXISTS ('
       # no moved package ...
-      printf 'SELECT *'
+      printf 'SELECT 1'
       printf ' FROM `moveable_binary_packages_copy`'
       mysql_join_binary_packages_install_target_providers 'moveable_binary_packages_copy' 'subst_itp'
       # ... provides the same
@@ -510,7 +531,7 @@ if [ ! "$1" = 'slim' ]; then
     # b) 1) ^
     printf ') AND NOT EXISTS ('
       # no current package ...
-      printf 'SELECT *'
+      printf 'SELECT 1'
       printf ' FROM `binary_packages` AS `subst_bp`'
       mysql_join_binary_packages_install_target_providers 'subst_bp' 'subst_itp'
       # ... in a repository ...
@@ -522,14 +543,47 @@ if [ ! "$1" = 'slim' ]; then
       printf ' WHERE `subst_rr`.`less_stable`=`repl_r`.`stability`'
       printf ' AND NOT EXISTS ('
       # ... and which is not replaced ...
-        printf 'SELECT * FROM `replaced_binary_packages_copy2`'
+        printf 'SELECT 1 FROM `replaced_binary_packages_copy2`'
         printf ' WHERE `replaced_binary_packages_copy2`.`id`=`subst_bp`.`id`'
       printf ')'
       # ... and provides the same
       printf ' AND `subst_itp`.`install_target`=`install_target_providers`.`install_target`'
       # b) 2) ^
     printf ');\n'
-    printf 'UNTIL row_count_saved=0 AND ROW_COUNT()=0\n'
+    printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n'
+
+    # refresh copies of our temporary tables *yuck*
+    for table in 'replaced' 'moveable'; do
+      for copy in '' '2'; do
+        if [ "${table}" = 'moveable' ] && \
+          [ "${copy}" = '2' ]; then
+          continue
+        fi
+        printf 'DELETE FROM `%s_binary_packages_copy%s`;\n' "${table}" "${copy}"
+        printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}"
+        printf ' SELECT `%s_binary_packages`.*' "${table}"
+        printf ' FROM `%s_binary_packages`;\n' "${table}"
+      done
+    done
+
+    printf 'DELETE `replaced_binary_packages`,`moveable_binary_packages` FROM `replaced_binary_packages`'
+    printf ' RIGHT JOIN `moveable_binary_packages`'
+    printf ' ON `replaced_binary_packages`.`replaced_by`=`moveable_binary_packages`.`id`'
+    printf ' JOIN `binary_packages`'
+    printf ' ON `binary_packages`.`id`=`moveable_binary_packages`.`id`'
+    mysql_join_binary_packages_build_assignments
+    printf ' JOIN `package_blobs`'
+    printf ' ON `build_assignments`.`package_source`=`package_blobs`.`ps_a`'
+    printf ' JOIN `build_assignments` AS `bl_ba`'
+    printf ' ON `bl_ba`.`package_source`=`package_blobs`.`ps_b`'
+    mysql_join_build_assignments_binary_packages 'bl_ba' 'bl_bp'
+    printf ' WHERE NOT EXISTS ('
+      printf 'SELECT 1 FROM `moveable_binary_packages_copy`'
+      printf ' WHERE `moveable_binary_packages_copy`.`id`=`bl_bp`.`id`'
+    printf ');\n'
+    printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n'
+
+    printf 'UNTIL row_count_saved=0\n'
     printf 'END REPEAT;\n'
     for table in 'moveable' 'replaced'; do
       for copy in '' '2'; do
diff --git a/bin/build-master-status b/bin/build-master-status
index 9503d71..7ab38d4 100755
--- a/bin/build-master-status
+++ b/bin/build-master-status
@@ -19,7 +19,7 @@ fi
 mysql_cleanup
 
 # update todos
-find "${base_dir}/bin/" "${base_dir}/conf/" -type f \
+find "${base_dir}/bin/" "${base_dir}/conf/" "${base_dir}/lib/" -type f \
   -exec grep -nHF '' '{}' \; | \
   sed 's,^'"$(str_to_regex "${base_dir}")"'/,,' | \
   sed -n '
@@ -206,15 +206,20 @@ if [ ! -s "${work_dir}/build-master-sanity" ]; then
     mysql_join_build_assignments_binary_packages
     mysql_join_binary_packages_repositories
     printf ' WHERE `repositories`.`name`="build-list"'
-    printf ' AND NOT EXISTS ('
-      printf 'SELECT * FROM `dependencies`'
-      mysql_join_dependencies_dependency_types
-      printf ' AND `dependency_types`.`relevant_for_binary_packages`'
-      mysql_join_dependencies_install_target_providers
-      mysql_join_install_target_providers_binary_packages '' 'prov_bp'
-      mysql_join_binary_packages_repositories 'prov_bp' 'prov_r'
-      printf ' WHERE `prov_r`.`name`="build-list"'
-      printf ' AND `dependencies`.`dependent`=`binary_packages`.`id`'
+    printf ' AND ('
+      printf 'NOT EXISTS ('
+        printf 'SELECT 1 FROM `dependencies`'
+        mysql_join_dependencies_dependency_types
+        printf ' AND `dependency_types`.`relevant_for_building`'
+        mysql_join_dependencies_install_target_providers
+        mysql_join_install_target_providers_binary_packages '' 'prov_bp'
+        mysql_join_binary_packages_repositories 'prov_bp' 'prov_r'
+        printf ' WHERE `prov_r`.`name`="build-list"'
+        printf ' AND `dependencies`.`dependent`=`binary_packages`.`id`'
+      printf ') OR EXISTS ('
+        printf 'SELECT 1 FROM `build_dependency_loops`'
+        printf ' WHERE `build_dependency_loops`.`build_assignment`=`build_assignments`.`id`'
+      printf ')'
     printf '))'
     printf ');\n'
   } | \
diff --git a/bin/build-packages b/bin/build-packages
index e9ac0c4..fc80ab1 100755
--- a/bin/build-packages
+++ b/bin/build-packages
@@ -466,6 +466,9 @@ while [ "${count}" -ne 0 ]; do
                 find "${package_content_dir}/${pkgfile##*/}" \
                   -name 'opt' -prune , \
                   -exec objdump -x '{}' \; 2>/dev/null | \
+                  sed '
+                    /^architecture:.* i386:x86-64, /,/^architecture:.* i386:x86-32, / d
+                  ' | \
                   grep -w 'NEEDED' | \
                   awk '{print $2}' | \
                   sed '
diff --git a/bin/cleanup b/bin/cleanup
index 83bb209..830cb94 100755
--- a/bin/cleanup
+++ b/bin/cleanup
@@ -60,10 +60,6 @@ find "${build_log_directory}/error" -maxdepth 1 -type f -printf '%f\n' | \
     done
   }
 
-# only keep last 50 lines of ssh-log
-tail -n50 "${work_dir}/ssh-log" | \
-  sponge "${work_dir}/ssh-log"
-
 # only keep namcap logs of last 2 weeks for succeeded packages
 find "${build_log_directory}/success" -maxdepth 1 -type f -mtime +14 \
   -not -exec zgrep -q '^+.*ELF file .* has text relocations' '{}' \; \
diff --git a/bin/copy-to-build-support b/bin/copy-to-build-support
index 5382235..1164c1d 100755
--- a/bin/copy-to-build-support
+++ b/bin/copy-to-build-support
@@ -2,6 +2,8 @@
 
 # copy the given package(s) into build-support
 
+# TODO: enable separate repositories for each architecture
+
 # shellcheck source=../conf/default.conf
 . "${0%/*}/../conf/default.conf"
 
diff --git a/bin/db-update b/bin/db-update
index 54d2f87..28e9861 100755
--- a/bin/db-update
+++ b/bin/db-update
@@ -12,6 +12,8 @@
 # TODO: we should delete more packages than just the ones in repositories
 # where we move to (think of [extra] -> [community])
 
+# TODO: enable separate repositories for each architecture
+
 # shellcheck disable=SC2039
 # shellcheck source=../conf/default.conf
 . "${0%/*}/../conf/default.conf"
@@ -392,7 +394,9 @@ for source_stability in 'testing' 'staging'; do
     printf ' JOIN `replaced_binary_packages` ON `binary_packages`.`id`=`replaced_binary_packages`.`id`;\n'
     printf 'UPDATE `binary_packages`'
     printf ' JOIN `moved_binary_packages` ON `binary_packages`.`id`=`moved_binary_packages`.`id`'
-    printf ' SET `binary_packages`.`repository`=`moved_binary_packages`.`new_repository`;\n'
+    printf ' SET `binary_packages`.`repository`=`moved_binary_packages`.`new_repository`,'
+    printf ' `binary_packages`.`last_moved`=NOW()'
+    printf ' WHERE `binary_packages`.`repository`!=`moved_binary_packages`.`new_repository`;\n'
   } | \
     mysql_run_query
 done
diff --git a/bin/delete-packages b/bin/delete-packages
index 7d2a3bd..5f60b40 100755
--- a/bin/delete-packages
+++ b/bin/delete-packages
@@ -15,6 +15,8 @@
 
 # TODO: delete other to-be-deleted packages if asked to do so
 
+# TODO: enable separate repositories for each architecture
+
 # shellcheck disable=SC2016
 usage() {
   >&2 echo ''
@@ -135,19 +137,19 @@ export TMPDIR="${tmp_dir}"
     printf ')'
   printf ');\n'
 
-  printf ' SELECT DISTINCT "repo",`repositories`.`name`'
+  printf 'SELECT DISTINCT "repo",`repositories`.`name`'
   printf ' FROM `to_deletes`'
   printf ' JOIN `binary_packages` ON `to_deletes`.`id`=`binary_packages`.`id`'
   mysql_join_binary_packages_repositories
   printf ';\n'
 
-  printf ' SELECT "package",`repositories`.`name`,`binary_packages`.`pkgname`'
+  printf 'SELECT "package",`repositories`.`name`,`binary_packages`.`pkgname`'
   printf ' FROM `to_deletes`'
   printf ' JOIN `binary_packages` ON `to_deletes`.`id`=`binary_packages`.`id`'
   mysql_join_binary_packages_repositories
   printf ';\n'
 
-  printf ' SELECT "package-file",`repositories`.`name`,'
+  printf 'SELECT "package-file",`repositories`.`name`,'
   mysql_package_name_query
   printf ' FROM `to_deletes`'
   printf ' JOIN `binary_packages` ON `to_deletes`.`id`=`binary_packages`.`id`'
@@ -155,7 +157,7 @@ export TMPDIR="${tmp_dir}"
   mysql_join_binary_packages_architectures
   printf ';\n'
 
-  printf ' SELECT "package-id",`to_deletes`.`id`'
+  printf 'SELECT "package-id",`to_deletes`.`id`'
   printf ' FROM `to_deletes`'
   printf ';\n'
 } | \
diff --git a/bin/find-obsolete-packages b/bin/find-obsolete-packages
new file mode 100755
index 0000000..3a4bebc
--- /dev/null
+++ b/bin/find-obsolete-packages
@@ -0,0 +1,127 @@
+#!/bin/sh
+
+# shellcheck source=../conf/default.conf
+. "${0%/*}/../conf/default.conf"
+
+usage() {
+  >&2 echo ''
+  >&2 echo 'find-obsolete-packages: find packages which are no longer available upstream.'
+  >&2 echo ''
+  >&2 echo 'possible options:'
+  >&2 echo '  -h|--help:'
+  >&2 echo '    Show this help and exit.'
+  >&2 echo '  -m|--mirror https://mirror.example.com/archlinux'
+  >&2 echo '    Mirror url to take upstream'"'"'s packages from.'
+  >&2 echo '  -n|--no-action:'
+  >&2 echo '    Do not mark packages as to_be_deleted.'
+  >&2 echo '  -w|--wait:'
+  >&2 echo '    Wait for lock if necessary.'
+  [ -z "$1" ] && exit 1 || exit "$1"
+}
+
+eval set -- "$(
+  getopt -o hm:nw \
+    --long help \
+    --long mirror: \
+    --long no-action \
+    --long wait \
+    -n "$(basename "$0")" -- "$@" || \
+  echo usage
+)"
+
+mirror=''
+no_action=false
+wait_for_lock='-n'
+
+while true
+do
+  case "$1" in
+    -h|--help)
+      usage 0
+    ;;
+    -m|--mirror)
+      if [ -n "${mirror}" ]; then
+        >&2 echo 'Already have one -m flag.'
+        usage
+      fi
+      shift
+      mirror="$1"
+    ;;
+    -n|--no-action)
+      no_action=true
+    ;;
+    -w|--wait)
+      wait_for_lock=''
+    ;;
+    --)
+      shift
+      break
+    ;;
+    *)
+      >&2 echo 'Whoops, forgot to implement option "'"$1"'" internally.'
+      exit 42
+    ;;
+  esac
+  shift
+done
+
+if [ $# -ne 0 ]; then
+  >&2 echo 'Too many arguments.'
+  usage
+fi
+
+if [ -z "${mirror}" ]; then
+  >&2 echo 'Flag -m missing'
+  usage
+fi
+
+if ! ${no_action}; then
+  exec 9> "${sanity_check_lock_file}"
+  if ! flock -s ${wait_for_lock} 9; then
+    >&2 echo 'Cannot get sanity-check lock.'
+    exit 1
+  fi
+
+  exec 8> "${build_list_lock_file}"
+  if ! flock ${wait_for_lock} 8; then
+    >&2 echo 'Cannot get build-list lock.'
+    exit 1
+  fi
+fi
+
+{
+  for repo in core extra community; do
+    curl -Ss "${mirror}/${repo}/os/x86_64/${repo}.db.tar.gz" | \
+      tar -tz
+  done | \
+    sed -n '
+      s,\(-[^-]*\)\{2\}/desc$,,
+      T
+      p
+      p
+    '
+  # shellcheck disable=SC2016
+  {
+    printf 'SELECT DISTINCT `binary_packages`.`pkgname`'
+    printf ' FROM `binary_packages`'
+    mysql_join_binary_packages_repositories
+    printf ' WHERE `repositories`.`is_on_master_mirror`'
+    printf ' AND NOT `binary_packages`.`is_to_be_deleted`'
+  } | \
+    mysql_run_query
+} | \
+  sort | \
+  uniq -u | \
+  if ${no_action}; then
+    cat
+  else
+    # shellcheck disable=SC2016
+    base64_encode_each | \
+      sed '
+        s/^/from_base64("/
+        s/$/"),/
+        1 s/^/UPDATE `binary_packages` SET `is_to_be_deleted`=1 WHERE `binary_packages`.`pkgname` IN (/
+        $ s/,$/);/
+      ' | \
+      mysql_run_query
+  fi
diff --git a/bin/get-assignment b/bin/get-assignment
index 7f08d42..34a990e 100755
--- a/bin/get-assignment
+++ b/bin/get-assignment
@@ -18,6 +18,7 @@
 # TODO: honor manual build order of tool-chain:
 # toolchain build order: linux-api-headers->glibc->binutils->gcc->binutils->glibc
 
+# hand_out_assignment $build_assignments.id
 hand_out_assignment() {
 
   # shellcheck disable=SC2016
@@ -32,46 +33,28 @@ hand_out_assignment() {
     mysql_join_build_assignments_binary_packages
     mysql_join_binary_packages_repositories
     printf ' WHERE `repositories`.`name`="build-list"'
-    printf ' AND `package_sources`.`%s`=from_base64("%s")' \
-      'pkgbase' "$(printf '%s' "$1" | base64 -w0)" \
-      'git_revision' "$(printf '%s' "$2" | base64 -w0)" \
-      'mod_git_revision' "$(printf '%s' "$3" | base64 -w0)"
-    printf ' AND `upstream_repositories`.`name`=from_base64("%s")' \
-      "$(printf '%s' "$4" | base64 -w0)"
+    printf ' AND `build_assignments`.`id`=from_base64("%s")' \
+      "$(printf '%s' "$1" | base64 -w0)"
     printf ' LIMIT 1;\n'
+  } | \
+    mysql_run_query | \
+    tr '\t' ' '
 
+  # shellcheck disable=SC2016
+  {
     printf 'UPDATE `build_slaves`'
-    printf ' SET `currently_building` = ('
-    printf ' SELECT `build_assignments`.`id`'
-    printf ' FROM `build_assignments`'
-    mysql_join_build_assignments_package_sources
-    mysql_join_package_sources_upstream_repositories
-    printf ' WHERE'
-    printf ' `package_sources`.`%s` = from_base64("%s") AND' \
-      'pkgbase'          "$(printf '%s' "$1" | base64 -w0)" \
-      'git_revision'     "$(printf '%s' "$2" | base64 -w0)" \
-      'mod_git_revision' "$(printf '%s' "$3" | base64 -w0)"
-    printf ' `upstream_repositories`.`name` = from_base64("%s")' \
-      "$(printf '%s' "$4" | base64 -w0)"
-    printf ')'
+    printf ' SET `currently_building` = from_base64("%s")' \
+      "$(printf '%s' "$1" | base64 -w0)"
     # shellcheck disable=SC2154
-    printf ' WHERE `build_slaves`.`name`=from_base64("%s");\n' \
-      "$(printf '%s' "${slave}" | base64 -w0)"
+    printf ' WHERE `build_slaves`.`id`=from_base64("%s");\n' \
+      "$(printf '%s' "${slave_id}" | base64 -w0)"
 
     printf 'UPDATE `build_assignments`'
-    mysql_join_build_assignments_package_sources
-    mysql_join_package_sources_upstream_repositories
     printf ' SET `build_assignments`.`priority`=0'
-    printf ' WHERE'
-    printf ' `package_sources`.`%s` = from_base64("%s") AND' \
-      'pkgbase'          "$(printf '%s' "$1" | base64 -w0)" \
-      'git_revision'     "$(printf '%s' "$2" | base64 -w0)" \
-      'mod_git_revision' "$(printf '%s' "$3" | base64 -w0)"
-    printf ' `upstream_repositories`.`name` = from_base64("%s");\n' \
-      "$(printf '%s' "$4" | base64 -w0)"
+    printf ' WHERE `build_assignments`.`id`=from_base64("%s");\n' \
+      "$(printf '%s' "${slave_id}" | base64 -w0)"
   } | \
-    mysql_run_query | \
-    tr '\t' ' '
+    mysql_run_query
 
   exit 0
 
@@ -108,13 +91,13 @@ trap clean_up EXIT
 currently_building=$(
   # shellcheck disable=SC2016
   {
-    printf 'SELECT '
-    mysql_query_select_pkgbase_and_revision
+    printf 'SELECT `build_assignments`.`id`'
+    printf ' FROM `build_assignments`'
     mysql_join_build_assignments_build_slaves
     mysql_join_build_assignments_binary_packages
     mysql_join_binary_packages_repositories
-    printf ' WHERE `build_slaves`.`name`=from_base64("%s")' \
-      "$(printf '%s' "${slave}" | base64 -w0)"
+    printf ' WHERE `build_slaves`.`id`=from_base64("%s")' \
+      "$(printf '%s' "${slave_id}" | base64 -w0)"
     printf ' AND `repositories`.`name`="build-list"'
     printf ' LIMIT 1;\n'
   } | \
@@ -122,8 +105,7 @@ currently_building=$(
 )
 
 if [ -n "${currently_building}" ]; then
-  # shellcheck disable=SC2086
-  hand_out_assignment ${currently_building}
+  hand_out_assignment "${currently_building}"
 fi
 
 # a package with [all dependencies met or which is part of a loop]
@@ -135,54 +117,54 @@ fi
 next_building=$(
   # shellcheck disable=SC2016
   {
-    printf 'SELECT '
-    printf '`package_sources`.`pkgbase`=from_base64("%s") AS `requested`,' \
-      "$(
-        printf '%s' "$1" | \
-          base64 -w0
-      )"
-    printf '`build_assignments`.`priority`,'
-    printf 'COALESCE('
-    printf 'MAX(`failed_builds`.`date`),0'
-    printf ') AS `last_trial`,'
-    mysql_query_is_part_of_loop '`build_assignments`.`id`'
-    printf ' AS `part_of_loop`,'
-    mysql_query_select_pkgbase_and_revision
-    mysql_join_build_assignments_binary_packages
-    mysql_join_binary_packages_repositories
-    printf ' LEFT'
-    mysql_join_build_assignments_failed_builds
-    printf ' WHERE `repositories`.`name`="build-list"'
-    printf ' AND NOT EXISTS ('
-      printf ' SELECT *'
-      printf ' FROM `build_slaves`'
-      printf ' WHERE `build_slaves`.`currently_building`=`build_assignments`.`id`'
-    printf ') AND ('
-      printf '`build_assignments`.`is_blocked` IS NULL'
-      printf ' OR'
-      printf ' `package_sources`.`pkgbase`=from_base64("%s")' \
+    printf 'SELECT `q`.`ba_id` FROM'
+    printf '('
+      printf 'SELECT '
+      printf '`package_sources`.`pkgbase`=from_base64("%s") AS `requested`,' \
         "$(
           printf '%s' "$1" | \
             base64 -w0
         )"
-    printf ') AND ('
+      printf '`build_assignments`.`priority`,'
+      printf 'COALESCE('
+      printf 'MAX(`failed_builds`.`date`),0'
+      printf ') AS `last_trial`,'
       mysql_query_is_part_of_loop '`build_assignments`.`id`'
-      printf ' OR NOT '
-      mysql_query_has_pending_dependencies '`build_assignments`.`id`'
-    printf ')'
-    printf ' GROUP BY `build_assignments`.`id`'
-    printf ' ORDER BY `requested` DESC, `priority` DESC, `last_trial`, `part_of_loop`, `build_assignments`.`id`'
-    printf ' LIMIT 1;\n'
+      printf ' AS `part_of_loop`,'
+      printf '`build_assignments`.`id` AS `ba_id`'
+      printf ' FROM `build_assignments`'
+      mysql_join_build_assignments_package_sources
+      mysql_join_build_assignments_binary_packages
+      mysql_join_binary_packages_repositories
+      printf ' LEFT'
+      mysql_join_build_assignments_failed_builds
+      printf ' WHERE `repositories`.`name`="build-list"'
+      printf ' AND NOT EXISTS ('
+        printf ' SELECT 1'
+        printf ' FROM `build_slaves`'
+        printf ' WHERE `build_slaves`.`currently_building`=`build_assignments`.`id`'
+      printf ') AND ('
+        printf '`build_assignments`.`is_blocked` IS NULL'
+        printf ' OR'
+        printf ' `package_sources`.`pkgbase`=from_base64("%s")' \
+          "$(
+            printf '%s' "$1" | \
+              base64 -w0
+          )"
+      printf ') AND ('
+        mysql_query_is_part_of_loop '`build_assignments`.`id`'
+        printf ' OR NOT '
+        mysql_query_has_pending_dependencies '`build_assignments`.`id`'
+      printf ')'
+      printf ' GROUP BY `build_assignments`.`id`'
+      printf ' ORDER BY `requested` DESC, `priority` DESC, `last_trial`, `part_of_loop`, `build_assignments`.`id`'
+      printf ' LIMIT 1'
+    printf ') AS `q`;\n'
   } | \
-    mysql_run_query | \
-    sed '
-      y/\t/ /
-      s/^.* \(\S\+\( \S\+\)\{3\}\)$/\1/
-    '
+    mysql_run_query
 )
 if [ -n "${next_building}" ]; then
-  # shellcheck disable=SC2086
-  hand_out_assignment ${next_building}
+  hand_out_assignment "${next_building}"
 fi
 
 # Check if there are any pending packages at all
diff --git a/bin/get-package-updates b/bin/get-package-updates
index f581e13..73766c8 100755
--- a/bin/get-package-updates
+++ b/bin/get-package-updates
@@ -14,9 +14,6 @@
 
 # TODO: keep database clean in case of abort
 
-# TODO: removing something from the blacklist should trigger a build
-# (if it is found in upstreams repositories)
-
 # shellcheck disable=SC2016
 usage() {
   >&2 echo ''
@@ -25,8 +22,12 @@ usage() {
   >&2 echo ''
   >&2 echo 'possible options:'
   >&2 echo '  -b|--block:   If necessary, wait for lock blocking.'
+  >&2 echo '  -d|--date $datetime:'
+  >&2 echo '                Pull latest commit before $datetime'
+  >&2 echo '                (yyyy-mm-ddThh:mm:ss). Conflicts -n.'
   >&2 echo '  -h|--help:    Show this help and exit.'
   >&2 echo '  -n|--no-pull: Do not pull git repos, merely reorder build list.'
+  >&2 echo '                Conflicts -d.'
   >&2 echo '  -x|--test-exclusion $package:'
   >&2 echo '                Print additionally deleted/excluded packages if'
   >&2 echo '                "$package" would be black listed.'
@@ -34,8 +35,9 @@ usage() {
 }
 
 eval set -- "$(
-  getopt -o bhnx: \
+  getopt -o bd:hnx: \
     --long block \
+    --long date: \
     --long help \
     --long no-pull \
     --long test-exclusion: \
@@ -44,6 +46,7 @@ eval set -- "$(
 )"
 
 block_flag='-n'
+date_time=''
 test_exclusion=''
 pull=true
 
@@ -53,6 +56,10 @@ do
     -b|--block)
       block_flag=''
     ;;
+    -d|--date)
+      shift
+      date_time="$1"
+    ;;
     -h|--help)
       usage 0
     ;;
@@ -85,6 +92,11 @@ if [ $# -ne 0 ]; then
   usage
 fi
 
+if [ -n "${date_time}" ] && ! ${pull}; then
+  >&2 printf -- '-d and -n are mutually exclusive.\n'
+  usage
+fi
+
 if [ -s "${work_dir}/build-master-sanity" ]; then
   >&2 echo 'Build master is not sane.'
   exit
@@ -146,9 +158,15 @@ for repo in ${repo_names}; do
   )'"
   # determine new git revision
   if ${pull}; then
-    eval "new_repo_revisions__${repo}='$(
-      git -C "${repo_path}" rev-parse HEAD
-    )'"
+    if [ -z "${date_time}" ]; then
+      eval "new_repo_revisions__${repo}='$(
+        git -C "${repo_path}" rev-parse HEAD
+      )'"
+    else
+      eval "new_repo_revisions__${repo}='$(
+        git -C "${repo_path}" rev-list -n1 --until "${date_time}" HEAD
+      )'"
+    fi
   else
     eval 'new_repo_revisions__'"${repo}"'="${old_repo_revisions__'"${repo}"'}"'
   fi
@@ -189,11 +207,66 @@ echo 'Check modified packages from the last update, and put them to the build li
 # If a package is deleted, remove from the rebuild list, and add it to the deletion list.
 # If a new package is added, then ensure that it's not on the deletion list.
 
-for repo in ${repo_names}; do
-  eval repo_path='"${repo_paths__'"${repo}"'}"'
-  eval old_repo_revision='"${old_repo_revisions__'"${repo}"'}"'
-  eval new_repo_revision='"${new_repo_revisions__'"${repo}"'}"'
+trigger_rebuild_on_any_file_sed_expression=$(
+  # note: This only works for upstream sources!
+  # shellcheck disable=SC2016
+  {
+    printf 'SELECT DISTINCT CONCAT('
+    printf '`package_sources`.`pkgbase`,"/repos/",'
+    printf '`upstream_repositories`.`name`,"-",'
+    printf 'IF(`architectures`.`name`="any","any","x86_64"))'
+    printf ' FROM `binary_packages`'
+    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_build_assignments
+    mysql_join_build_assignments_architectures
+    mysql_join_build_assignments_package_sources
+    mysql_join_package_sources_upstream_repositories
+    printf ' WHERE `repositories`.`name`="build-list";\n'
+  } | \
+    mysql_run_query | \
+    sed '
+      s@.*@s,^\\(.\\t\0/\\)[^/]\\+,\\1PKGBUILD,@
+    '
+)
+
+{
+  # trigger rebuild of packages removed from blacklist
+  # shellcheck disable=SC2016
   {
+    printf 'SELECT `package_sources`.`pkgbase`,`upstream_repositories`.`name`,`git_repositories`.`name`'
+    printf ' FROM `package_sources`'
+    mysql_join_package_sources_upstream_repositories
+    mysql_join_upstream_repositories_git_repositories
+    printf ' WHERE `package_sources`.`pkgbase` IN ('
+    # shellcheck disable=SC2154
+    git -C "${repo_paths__archlinux32}" diff "${old_repo_revisions__archlinux32}" "${new_repo_revisions__archlinux32}" -- blacklist | \
+      sed -n '
+        s/^-\([^-]\)/\1/
+        T
+        s/\s*#.*$//
+        p
+      ' | \
+      base64_encode_each | \
+      sed '
+        s/^/from_base64("/
+        s/$/")/
+      ' | \
+      tr '\n' ','
+    printf '"");\n'
+  } | \
+    mysql_run_query | \
+    while read -r pkgbase repository git_repository; do
+      printf 'A %s ' \
+        "${pkgbase}"
+      eval 'printf '"'"'%s'"'"' "${new_repo_revisions__'"${git_repository}"'}"'
+      printf ' empty %s\n' \
+        "${repository}"
+    done
+  # actual updates/removes
+  for repo in ${repo_names}; do
+    eval repo_path='"${repo_paths__'"${repo}"'}"'
+    eval old_repo_revision='"${old_repo_revisions__'"${repo}"'}"'
+    eval new_repo_revision='"${new_repo_revisions__'"${repo}"'}"'
     # if old revision unknown, mimic "git diff"-output
     # shellcheck disable=SC2154
     if [ "${old_repo_revision}" = "NONE" ]; then
@@ -202,45 +275,48 @@ for repo in ${repo_names}; do
         sed 's|^|A\t|'
     else
       git -C "${repo_path}" diff --no-renames --name-status "${old_repo_revision}" "${new_repo_revision}"
-    fi
-  } | \
-    # only track changes in PKGBUILDs
-    # TODO: However, packages which are already on the build list should
-    # receive a git_revision bump if _any_ file changed.
-    grep '/PKGBUILD$' | \
-    if [ "${repo}" = "archlinux32" ]; then
-    # modify the directory structure from the modifiaction-repository
-    # to the one of an original source repository
-      # shellcheck disable=SC2016
-      sed 's|^\(.\t\)\([^/]\+\)/\([^/]\+\)/\(.\+\)$|\2 \1\3/repos/\2-x86_64/\4|' | \
-        while read -r pkg_repo rest; do
-          repo=$(find_git_repository_to_package_repository "${pkg_repo}")
-          eval 'printf '"'"'%s %s %s\n'"'" \
-            "$(printf '"${new_repo_revisions__%s}"' "${repo}")" \
-            "$(printf '"${old_repo_revisions__%s}"' "${repo}")" \
-            "'${rest}'"
-        done
-    else
-      sed "s|^|${new_repo_revision} ${old_repo_revision} |"
     fi | \
-    grep '^\S\+ \S\+ .\s[^/]\+/repos/[^/]\+/PKGBUILD$' | \
-    # ignore i686
-    grep -v -- '-i686/PKGBUILD$' | \
-    # ignore staging and testing
-    grep -v -- '[-/]\(staging\|testing\|unstable\)-[^/]\+/PKGBUILD$' | \
-    sed 's|^\(\S\+ \S\+\) \(.\)\t\([^/]\+\)/repos/\([^/]\+\)-[^/-]\+/PKGBUILD$|\2 \3 \1 \4|'
-done | \
-  sort -u | \
-  sed '
-    s|^D\s|0 \0|
-    t
-    s|^[AM]\s|1 \0|
-    t
-    s|^|2 |
-  ' | \
-  sort -k1,1 | \
-  sed 's|^[012] ||' | \
+      # rename any file to "PKGBUILD" for packages on the build-list
+      sed "${trigger_rebuild_on_any_file_sed_expression}" | \
+      # only track changes in PKGBUILDs
+      grep '/PKGBUILD$' | \
+      if [ "${repo}" = "archlinux32" ]; then
+      # modify the directory structure from the modifiaction-repository
+      # to the one of an original source repository
+        # shellcheck disable=SC2016
+        sed 's|^\(.\t\)\([^/]\+\)/\([^/]\+\)/\(.\+\)$|\2 \1\3/repos/\2-x86_64/\4|' | \
+          while read -r pkg_repo rest; do
+            repo=$(find_git_repository_to_package_repository "${pkg_repo}")
+            eval 'printf '"'"'%s %s %s\n'"'" \
+              "$(printf '"${new_repo_revisions__%s}"' "${repo}")" \
+              "$(printf '"${old_repo_revisions__%s}"' "${repo}")" \
+              "'${rest}'"
+          done
+      else
+        sed "s|^|${new_repo_revision} ${old_repo_revision} |"
+      fi | \
+      grep '^\S\+ \S\+ .\s[^/]\+/repos/[^/]\+/PKGBUILD$' | \
+      # ignore i686
+      grep -v -- '-i686/PKGBUILD$' | \
+      # ignore staging and testing
+      grep -v -- '[-/]\(staging\|testing\|unstable\)-[^/]\+/PKGBUILD$' | \
+      sed 's|^\(\S\+ \S\+\) \(.\)\t\([^/]\+\)/repos/\([^/]\+\)-[^/-]\+/PKGBUILD$|\2 \3 \1 \4|'
+  done | \
+    sort -u | \
+    sed '
+      s|^D\s|0 \0|
+      t
+      s|^[AM]\s|1 \0|
+      t
+      s|^|2 |
+    ' | \
+    sort -k1,1 | \
+    sed 's|^[012] ||'
+} | \
   while read -r mode package git_revision old_git_revision repository; do
+    if [ "${old_git_revision}" = 'empty' ]; then
+      old_git_revision=''
+    fi
     if [ "${mode}" = 'D' ]; then
       # deleted PKGBUILD
       # shellcheck disable=SC2154
@@ -376,11 +452,14 @@ while [ -n "${black_listed_new}" ]; do
         mysql_join_package_sources_build_assignments 'a_ps' 'a_ba'
         mysql_join_build_assignments_binary_packages 'a_ba' 'a_bp'
         mysql_join_binary_packages_dependencies 'a_bp'
+        mysql_join_dependencies_dependency_types
+        printf ' AND `dependency_types`.`relevant_for_building`'
+        printf ' AND `dependency_types`.`relevant_for_binary_packages`'
         printf ' WHERE NOT EXISTS ('
-          printf ' SELECT *'
+          printf ' SELECT 1'
           printf ' FROM `install_target_providers`'
           printf ' WHERE NOT EXISTS ('
-            printf ' SELECT *'
+            printf ' SELECT 1'
             printf ' FROM `bl`'
             printf ' JOIN `package_sources` AS `b_ps` ON `bl`.`pkgbase`=`b_ps`.`pkgbase`'
             mysql_join_package_sources_build_assignments 'b_ps' 'b_ba'
@@ -391,7 +470,7 @@ while [ -n "${black_listed_new}" ]; do
         printf ') AND EXISTS ('
         # TODO: This should be corrected at the root: automatic install targets, which are bogus should
         #       not be added in the first place - but how do we detect that?
-          printf ' SELECT *'
+          printf ' SELECT 1'
           printf ' FROM `install_target_providers`'
           printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
         printf ');\n'
diff --git a/bin/ii-connect b/bin/ii-connect
index ca8ea99..5fc5f37 100755
--- a/bin/ii-connect
+++ b/bin/ii-connect
@@ -79,8 +79,10 @@ if [ "$1" = 'watch' ]; then
         channel="${channel##*/}"
         if [ -z "${channel%%#*}" ]; then
           prefix='buildmaster: '
+          sloppy_salutation='buildmaster'
         else
           prefix=''
+          sloppy_salutation=''
         fi
         regex='^\(\S\+ \)\?\S\+ <\S\+> '"${prefix}"'why[- ]don'"'"'\?t[- ]you \(build\|stabilize\|unstage\|keep\|stubbornly_keep\) '
         if grep -q "${regex}" "${out_file}"; then
@@ -130,6 +132,22 @@ if [ "$1" = 'watch' ]; then
             done
           sed -i "/${regex}/d" "${out_file}"
         fi
+        regex='^\(\S\+ \)\?\S\+ <\S\+> .*[Ww]hat'"'"'\?s *[Uu]p'
+        if grep "${regex}" "${out_file}" | \
+          grep -q "${sloppy_salutation}"; then
+          done_something=true
+          sed -i "/${regex}/d" "${out_file}"
+          printf 'up? I'"'"'m up for %s, %s\n' "$(uptime -p | sed 's/^up //')" "$(uptime | sed 's/.*,  //')"  | \
+            sponge "${out_file%/out}/in"
+        fi
+        regex='^\(\S\+ \)\?\S\+ <\S\+> \(.* \)\?[Tt]h\(anks\|x\)\([ ,.!]\|$\)'
+        if grep "${regex}" "${out_file}" | \
+          grep -q "${sloppy_salutation}"; then
+          done_something=true
+          sed -i "/${regex}/d" "${out_file}"
+          printf 'np\n' | \
+            sponge "${out_file%/out}/in"
+        fi
       done
     sleep 1
   done
diff --git a/bin/ping-from-slave b/bin/ping-from-slave
index 3c03b45..7611f44 100755
--- a/bin/ping-from-slave
+++ b/bin/ping-from-slave
@@ -18,7 +18,8 @@ if [ "$(
     printf ' WHERE `build_slaves`.`name`=from_base64("%s");\n' \
       "$(printf '%s' "${slave}" | base64 -w0)"
   } | \
-    mysql_run_query 'unimportant'
+    mysql_run_query 'unimportant' || \
+    echo '1'
   )" -ne 1 ]; then
   >&2 echo 'You do not build anything currently - abort whatever you are doing.'
   exit 2
diff --git a/bin/return-assignment b/bin/return-assignment
index 9e64ff0..bcdb717 100755
--- a/bin/return-assignment
+++ b/bin/return-assignment
@@ -99,7 +99,6 @@ if [ "$5" = 'ERROR' ]; then
         'log_file' 'VARCHAR(512)' | \
         sed 's/,$//'
       printf ');\n'
-      printf 'INSERT INTO `failures` (`date`,`reason`,`log_file`) VALUES'
       fail_reason_identifiers=$(
         {
           printf 'SELECT `fail_reasons`.`id`,replace(to_base64(`fail_reasons`.`identifier`),"\\n","")'
@@ -131,8 +130,10 @@ if [ "$5" = 'ERROR' ]; then
             fi
           done
       done | \
-        sed 's/,$//'
-      printf ';\n'
+        sed '
+          1 s/^/INSERT INTO `failures` (`date`,`reason`,`log_file`) VALUES /
+          s/,$/;\n/
+        '
       printf 'INSERT INTO `failed_builds` (`build_slave`,`build_assignment`,`date`,`reason`,`log_file`)'
       printf ' SELECT '
       printf '`build_slaves`.`%s`,' \
@@ -536,7 +537,8 @@ trigger_mirror_refreshs
         base64 -w0
     )"
   printf ' `binary_packages`.`has_issues`=0,'
-  printf ' `binary_packages`.`is_tested`=0'
+  printf ' `binary_packages`.`is_tested`=0,'
+  printf ' `binary_packages`.`last_moved`=NOW()'
   printf ' WHERE `build_slaves`.`name`=from_base64("%s")' \
     "$(
       printf '%s' "${slave}" | \
diff --git a/bin/sanity-check b/bin/sanity-check
index 06da288..96f028f 100755
--- a/bin/sanity-check
+++ b/bin/sanity-check
@@ -41,6 +41,7 @@ eval set -- "$(
 
 silence=0
 repos="${standalone_package_repositories} ${stable_package_repositories} ${testing_package_repositories} ${staging_package_repositories}"
+archs='i686'
 web=false
 
 while true
@@ -218,11 +219,13 @@ while [ $# -gt 0 ]; do
       errors=$(
         {
           # shellcheck disable=SC2086
-          printf 'expected %s\n' ${repos}
-          ls_master_mirror 'i686' | \
-            sed 's|^|found |'
+          for arch in ${archs}; do
+            printf 'expected '"${arch}"' %s\n' ${repos}
+            ls_master_mirror "${arch}" | \
+              sed 's|^|found '"${arch}"' |'
+          done
         } | \
-          sort -k2 | \
+          sort -k2,3 | \
           uniq -uf1
       )
       if [ -n "${errors}" ]; then
@@ -242,109 +245,111 @@ while [ $# -gt 0 ]; do
 
     package-database)
 
-      for repo in ${repos}; do
-
-        [ ${silence} -gt 0 ] || \
-          printf 'checking consistency of repository "%s" on the master mirror ...' "${repo}" >> \
-          "${tmp_dir}/messages"
+      for arch in ${archs}; do
+        for repo in ${repos}; do
 
-        packages=$(
-          ls_master_mirror "i686/${repo}" | \
-            grep '\.pkg\.tar\.xz\(\.sig\)\?$'
-        ) || true
-
-        errors=$(
-          echo "${packages}" | \
-            grep '\S' | \
-            sed '
-              s|^\(.*\.pkg\.tar\.xz\)$|package \1|
-              s|^\(.*\.pkg\.tar\.xz\)\.sig$|signature \1|
-            ' | \
-            sort -k2 | \
-            uniq -cf1 | \
-            grep -v '^\s*2\s' | \
-            awk '{print $2 " " $3}'
-        ) || true
-        if [ -n "${errors}" ]; then
-          if [ ${silence} -le 1 ]; then
-            printf '\nThe following packages in %s are missing a signature or vice versa:\n%s\n' \
-              "${repo}" \
-              "${errors}" >> \
-              "${tmp_dir}/messages"
-          fi
-          i_am_insane
-        fi
+          [ ${silence} -gt 0 ] || \
+            printf 'checking consistency of repository "%s/%s" on the master mirror ...' "${arch}" "${repo}" >> \
+            "${tmp_dir}/messages"
 
-        ${master_mirror_rsync_command} \
-          "${master_mirror_rsync_directory}/i686/${repo}/${repo}.db.tar.gz" \
-          "${master_mirror_rsync_directory}/i686/${repo}/${repo}.files.tar.gz" \
-          "${tmp_dir}/"
+          packages=$(
+            ls_master_mirror "${arch}/${repo}" | \
+              grep '\.pkg\.tar\.xz\(\.sig\)\?$'
+          ) || true
 
-        errors=$(
-          {
-            tar -Oxzf "${tmp_dir}/${repo}.db.tar.gz" --wildcards '*/desc' 2>/dev/null | \
-              sed -n '
-                /^%FILENAME%$/ {
-                  N
-                  s/^.*\n/in_database /
-                  p
-                }
-              '
+          errors=$(
             echo "${packages}" | \
+              grep '\S' | \
               sed '
-                /\.pkg\.tar\.xz$/ !d
-                s/^/in_repository /
+                s|^\(.*\.pkg\.tar\.xz\)$|package \1|
+                s|^\(.*\.pkg\.tar\.xz\)\.sig$|signature \1|
               ' | \
-              sort -u
-          } | \
-            sort -k2 | \
-            uniq -uf1
-        )
-        if [ -n "${errors}" ]; then
-          if [ ${silence} -le 1 ]; then
-            printf '\nThe following packages in %s are missing from the database or vice versa:\n%s\n' \
-              "${repo}" \
-              "${errors}" >> \
-              "${tmp_dir}/messages"
+              sort -k2 | \
+              uniq -cf1 | \
+              grep -v '^\s*2\s' | \
+              awk '{print $2 " " $3}'
+          ) || true
+          if [ -n "${errors}" ]; then
+            if [ ${silence} -le 1 ]; then
+              printf '\nThe following packages in %s are missing a signature or vice versa:\n%s\n' \
+                "${repo}" \
+                "${errors}" >> \
+                "${tmp_dir}/messages"
+            fi
+            i_am_insane
           fi
-          i_am_insane
-        fi
 
-        errors=$(
-          {
-            tar -tzf "${tmp_dir}/${repo}.files.tar.gz" | \
-              grep '/$' | \
-              sed '
-                s|/$||
-                s|^|in_database |
-              '
-            echo "${packages}" | \
-              grep '\S' | \
-              sed '
-                s|-[^-]\+$||
-                s|^|in_repository |
-              ' | \
-              sort -u
-          } | \
-            sort -k2 | \
-            uniq -uf1
-        )
-        if [ -n "${errors}" ]; then
-          if [ ${silence} -le 1 ]; then
-            printf '\nThe following packages in %s are missing from the file-database or vice versa:\n%s\n' \
-              "${repo}" \
-              "${errors}" >> \
-              "${tmp_dir}/messages"
+          ${master_mirror_rsync_command} \
+            "${master_mirror_rsync_directory}/${arch}/${repo}/${repo}.db.tar.gz" \
+            "${master_mirror_rsync_directory}/${arch}/${repo}/${repo}.files.tar.gz" \
+            "${tmp_dir}/"
+
+          errors=$(
+            {
+              tar -Oxzf "${tmp_dir}/${repo}.db.tar.gz" --wildcards '*/desc' 2>/dev/null | \
+                sed -n '
+                  /^%FILENAME%$/ {
+                    N
+                    s/^.*\n/in_database /
+                    p
+                  }
+                '
+              echo "${packages}" | \
+                sed '
+                  /\.pkg\.tar\.xz$/ !d
+                  s/^/in_repository /
+                ' | \
+                sort -u
+            } | \
+              sort -k2 | \
+              uniq -uf1
+          )
+          if [ -n "${errors}" ]; then
+            if [ ${silence} -le 1 ]; then
+              printf '\nThe following packages in %s are missing from the database or vice versa:\n%s\n' \
+                "${repo}" \
+                "${errors}" >> \
+                "${tmp_dir}/messages"
+            fi
+            i_am_insane
+          fi
+
+          errors=$(
+            {
+              tar -tzf "${tmp_dir}/${repo}.files.tar.gz" | \
+                grep '/$' | \
+                sed '
+                  s|/$||
+                  s|^|in_database |
+                '
+              echo "${packages}" | \
+                grep '\S' | \
+                sed '
+                  s|-[^-]\+$||
+                  s|^|in_repository |
+                ' | \
+                sort -u
+            } | \
+              sort -k2 | \
+              uniq -uf1
+          )
+          if [ -n "${errors}" ]; then
+            if [ ${silence} -le 1 ]; then
+              printf '\nThe following packages in %s are missing from the file-database or vice versa:\n%s\n' \
+                "${repo}" \
+                "${errors}" >> \
+                "${tmp_dir}/messages"
+            fi
+            i_am_insane
           fi
-          i_am_insane
-        fi
 
-        find "${tmp_dir:?}" -mindepth 1 \( -not -name 'messages' \) -delete
+          find "${tmp_dir:?}" -mindepth 1 \( -not -name 'messages' \) -delete
 
-        [ ${silence} -gt 0 ] || \
-          echo ' passed.' >> \
-          "${tmp_dir}/messages"
+          [ ${silence} -gt 0 ] || \
+            echo ' passed.' >> \
+            "${tmp_dir}/messages"
 
+        done
       done
 
     ;;
@@ -359,23 +364,26 @@ while [ $# -gt 0 ]; do
         {
           # shellcheck disable=SC2016
           {
-            printf 'SELECT "mysql",CONCAT(`repositories`.`name`,"/",'
+            printf 'SELECT "mysql",CONCAT(`r_a`.`name`,"/",`repositories`.`name`,"/",'
             mysql_package_name_query
             printf ') FROM `binary_packages`'
             mysql_join_binary_packages_repositories
             printf ' AND `repositories`.`is_on_master_mirror`'
             mysql_join_binary_packages_architectures
+            mysql_join_repositories_architectures '' 'r_a'
           } | \
             mysql_run_query | \
             tr '\t' ' '
-          ls_master_mirror 'i686' | \
-            while read -r repo; do
-              ls_master_mirror "i686/${repo}" | \
-                sed '
-                  /\.pkg\.tar\.xz$/!d
-                  s,^,package-file '"${repo}"'/,
-                '
-            done
+          for arch in ${archs}; do
+            ls_master_mirror "${arch}" | \
+              while read -r repo; do
+                ls_master_mirror "${arch}/${repo}" | \
+                  sed '
+                    /\.pkg\.tar\.xz$/!d
+                    s,^,package-file '"${arch}"'/'"${repo}"'/,
+                  '
+              done
+          done
         } | \
           sed 's/\(-[0-9]\+\)\.0\(-[^- ]\+$\)/\1\2/' | \
           sort -k2 | \
diff --git a/bin/slave-build-connect b/bin/slave-build-connect
index b176c8f..4fcb38a 100755
--- a/bin/slave-build-connect
+++ b/bin/slave-build-connect
@@ -17,9 +17,27 @@ if [ "x${SSH_ORIGINAL_COMMAND%% *}" = 'xget-assignment' ] || \
   fi
 
   # shellcheck disable=SC2016
+  slave_id=$(
+    {
+      printf 'SELECT `build_slaves`.`id` FROM `build_slaves`'
+      printf ' WHERE `build_slaves`.`name`=from_base64("%s");\n' \
+        "$(
+          printf '%s' "$1" | \
+            base64 -w0
+        )"
+    } | \
+      mysql_run_query
+  )
+  if [ -z "${slave_id}" ]; then
+    >&2 printf 'Build slave "%s" is unnknown to the database.\n' "$1"
+    exit 42
+  fi
+
+  # shellcheck disable=SC2016
   {
     printf 'INSERT INTO `ssh_log` (`build_slave`,`action`,`parameters`)'
-    printf ' SELECT `build_slaves`.`id`'
+    printf ' VALUES (%s' \
+      "${slave_id}"
     printf ',from_base64("%s")' \
       "$(
         printf '%s' "${SSH_ORIGINAL_COMMAND%% *}" | \
@@ -29,16 +47,11 @@ if [ "x${SSH_ORIGINAL_COMMAND%% *}" = 'xget-assignment' ] || \
         printf '%s' "${SSH_ORIGINAL_COMMAND#* }" | \
           base64 -w0
       )"
-    printf ' FROM `build_slaves`'
-    printf ' WHERE `build_slaves`.`name`=from_base64("%s");\n' \
-      "$(
-        printf '%s' "$1" | \
-          base64 -w0
-      )"
+    printf ');\n'
   } | \
     mysql_run_query 'unimportant'
 
-  slave="$1" /bin/sh -c "${base_dir}/bin/${SSH_ORIGINAL_COMMAND}"
+  slave="$1" slave_id="${slave_id}" /bin/sh -c "${base_dir}/bin/${SSH_ORIGINAL_COMMAND}"
 
 else
 
diff --git a/lib/common-functions b/lib/common-functions
index a4d51b0..ef10367 100755
--- a/lib/common-functions
+++ b/lib/common-functions
@@ -9,17 +9,6 @@ if [ -z "${base_dir}" ]; then
   . '../conf/default.conf'
 fi
 
-if [ ! -s "${work_dir}/build-master-sanity" ]; then
-  {
-    date
-    printf 'sourcing common-functions for %s\n' "$0"
-    printf '%s parameters:' "$#"
-    printf ' "%s"' "$@"
-    printf '\n'
-  } >> \
-    "${work_dir}/command-log"
-fi
-
 # find_pkgbuilds package repository git_repository git_revision mod_git_revision
 # find the PKGBUILD and modification of $package from $repository
 # sets $PKGBUILD and $PKGBUILD_mod
@@ -561,6 +550,8 @@ recursively_umount_and_rm() {
 #  mangle $arch in PKBUILDs to contain i486, i586, i686
 #  append $sub_pkgrel to the pkgrel
 
+# TODO: actually include the other architectures
+
 mangle_pkgbuild() {
   local PKGBUILD="$1"
   local sub_pkgrel="$2"
diff --git a/lib/mysql-functions b/lib/mysql-functions
index 8101e0c..83b9296 100755
--- a/lib/mysql-functions
+++ b/lib/mysql-functions
@@ -9,6 +9,9 @@ if [ -z "${base_dir}" ]; then
   . '../conf/default.conf'
 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() {
@@ -59,21 +62,27 @@ mysql_run_query() {
   # 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" ]; then
+    if [ ! -s "${work_dir}/build-master-sanity" ] && \
+      [ -z "${file_name_extra}" ]; then
       printf '\001ACTION failed to execute a mysql query - can you have a look at "%s"?.\001\n' \
         "${query_stdin##*/}" \
         | irc_say
-      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
     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
@@ -106,6 +115,7 @@ mysql_add_package_source() {
   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
@@ -145,11 +155,31 @@ mysql_add_package_source() {
   fi
   printf 'I' >&2
 
+  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 IGNORE INTO package_sources'
     printf ' (`pkgbase`,`git_revision`,`mod_git_revision`,`upstream_package_repository`,`uses_upstream`,`uses_modification`,`commit_time`)'
-    printf ' SELECT'
-    printf ' from_base64("%s"),' \
+    printf ' VALUES ('
+    printf 'from_base64("%s"),' \
       "$(
         printf '%s' "${pkgbase}" | \
           base64 -w0
@@ -162,14 +192,12 @@ mysql_add_package_source() {
         printf '%s' "${mod_git_revision}" | \
           base64 -w0
       )"
-    printf ' `upstream_repositories`.`id`,%s,%s,from_unixtime(%s)' \
-      "${uses_upstream}" "${uses_modification}" "${commit_time}"
-    printf ' FROM `upstream_repositories`'
-    printf ' WHERE `upstream_repositories`.`name` = from_base64("%s");' \
-      "$(
-        printf '%s' "${upstream_package_repository}" | \
-          base64 -w0
-      )"
+    printf '%s,' \
+      "${upstream_package_repository_id}" \
+      "${uses_upstream}" \
+      "${uses_modification}"
+    printf 'from_unixtime(%s));\n' \
+      "${commit_time}"
   } | \
     mysql_run_query
   printf 'J' >&2
@@ -264,6 +292,7 @@ mysql_generate_package_metadata() {
       exit 2
     fi
     # remove empty lines and unsupported architectures
+    # TODO: support more architectures
     sed -i '
       /^[^=]*=\s*$/d
       /^\s*arch = /{
@@ -718,30 +747,35 @@ mysql_sanity_check() {
     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
-        '
+    # 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 `repositories`.`name`,`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architectures`.`name`'
+      printf 'SELECT `r_a`.`name`,`repositories`.`name`,`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architectures`.`name`'
       printf ' FROM `binary_packages`'
       mysql_join_binary_packages_architectures
       mysql_join_binary_packages_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,-,
@@ -882,16 +916,19 @@ mysql_cleanup() {
     printf ');\n'
     # remove jobs from build slaves that are not on the build-list
     if [ "${operator}" = 'DELETE' ]; then
-      printf 'UPDATE `build_slaves`'
+      printf 'UPDATE `repositories`'
     else
-      printf 'SELECT COUNT(DISTINCT `build_slaves`.`id`) FROM `build_slaves`'
+      printf 'SELECT COUNT(DISTINCT `build_slaves`.`id`) FROM `repositories`'
     fi
-    mysql_join_build_slaves_binary_packages
-    mysql_join_binary_packages_repositories
+    mysql_join_repositories_binary_packages
+    printf ' AND `repositories`.`name`="build-list"'
+    printf ' RIGHT'
+    mysql_join_binary_packages_build_slaves
     if [ "${operator}" = 'DELETE' ]; then
       printf ' SET `build_slaves`.`currently_building`=NULL'
     fi
-    printf ' WHERE `repositories`.`name`!="build-list";\n'
+    printf ' WHERE `build_slaves`.`currently_building` IS NOT NULL'
+    printf ' AND `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`'
@@ -916,7 +953,7 @@ mysql_cleanup() {
     printf ' AND (`build_slaves`.`last_action` IS NOT NULL'
     printf ' OR `build_slaves`.`logged_lines` IS NOT NULL);\n'
   } | \
-    mysql_run_query
+    mysql_run_query 'unimportant'
 }
 
 # mysql_query_has_pending_dependencies `build_assignment`.`id`
@@ -1155,6 +1192,7 @@ for link in \
   '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' \
diff --git a/misc/database-layout.dmf b/misc/database-layout.dmf
index b74c264..f53237b 100644
--- a/misc/database-layout.dmf
+++ b/misc/database-layout.dmf
@@ -208,8 +208,8 @@
     </item>
     <item type="database-table" id="{9085c36f-5884-41c8-9ca8-9d53973e657d}">
       <position>
-        <x>737</x>
-        <y>38</y>
+        <x>777</x>
+        <y>32</y>
       </position>
       <table>
         <name>statitstics</name>
@@ -540,12 +540,6 @@
             <primary-key>False</primary-key>
           </column>
           <column>
-            <name>repository</name>
-            <data-type>MEDIUMINT</data-type>
-            <required>True</required>
-            <primary-key>False</primary-key>
-          </column>
-          <column>
             <name>epoch</name>
             <data-type>MEDIUMINT</data-type>
             <required>True</required>
@@ -605,13 +599,19 @@
             <required>False</required>
             <primary-key>False</primary-key>
           </column>
+          <column>
+            <name>last_moved</name>
+            <data-type>DATETIME</data-type>
+            <required>True</required>
+            <primary-key>False</primary-key>
+          </column>
         </column-list>
       </table>
     </item>
     <item type="database-table" id="{f8caa2d5-11ba-485c-b273-8183713896bf}">
       <position>
-        <x>554</x>
-        <y>117</y>
+        <x>549</x>
+        <y>105</y>
       </position>
       <table>
         <name>install_target_providers</name>
@@ -671,6 +671,12 @@
             <required>True</required>
             <primary-key>False</primary-key>
           </column>
+          <column>
+            <name>architecture</name>
+            <data-type>SMALLINT</data-type>
+            <required>True</required>
+            <primary-key>False</primary-key>
+          </column>
         </column-list>
       </table>
     </item>
@@ -832,8 +838,8 @@
     </item>
     <item type="database-table" id="{e84f8e5f-28e0-4d8b-a7e5-0f80a896f74e}">
       <position>
-        <x>563</x>
-        <y>42</y>
+        <x>557</x>
+        <y>30</y>
       </position>
       <table>
         <name>install_targets</name>
@@ -1118,6 +1124,69 @@
         </column-list>
       </table>
     </item>
+    <item type="database-table" id="{b33d4844-b4e4-4ef6-944b-36b4113c2b82}">
+      <position>
+        <x>543</x>
+        <y>182</y>
+      </position>
+      <table>
+        <name>binary_packages_in_repositories</name>
+        <color>#ffffff</color>
+        <column-list>
+          <column>
+            <name>id</name>
+            <data-type>BIGINT</data-type>
+            <required>True</required>
+            <primary-key>True</primary-key>
+          </column>
+          <column>
+            <name>package</name>
+            <data-type>BIGINT</data-type>
+            <required>True</required>
+            <primary-key>False</primary-key>
+          </column>
+          <column>
+            <name>repository</name>
+            <data-type>MEDIUMINT</data-type>
+            <required>True</required>
+            <primary-key>False</primary-key>
+          </column>
+        </column-list>
+      </table>
+    </item>
+    <item type="database-relationship" id="{25ec7ee3-9041-4ef4-a5b8-c41a06385813}">
+      <line>
+        <connector-list>
+          <connector>
+            <position>
+              <x>529</x>
+              <y>318.906</y>
+            </position>
+            <angle>180</angle>
+            <hub owner="{844766fc-c5af-4f61-b51b-d3f9393c60ca}"/>
+          </connector>
+          <connector>
+            <position>
+              <x>509.547</x>
+              <y>345.906</y>
+            </position>
+            <angle>0</angle>
+            <hub owner="{04f424bf-6465-478b-9529-0038615b51d9}"/>
+          </connector>
+        </connector-list>
+      </line>
+      <relationship>
+        <cardinality>OneToOne</cardinality>
+        <modality>
+          <child>Mandatory</child>
+          <parent>Mandatory</parent>
+        </modality>
+        <columns>
+          <child>4</child>
+          <parent>0</parent>
+        </columns>
+      </relationship>
+    </item>
     <item type="database-relationship" id="{3539786a-db3b-4c5c-ba35-e89fb027cda8}">
       <line>
         <connector-list>
@@ -1140,7 +1209,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1173,7 +1242,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1206,7 +1275,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1239,7 +1308,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1272,7 +1341,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1305,7 +1374,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1338,7 +1407,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1371,7 +1440,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1404,7 +1473,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1437,7 +1506,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1470,7 +1539,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1503,7 +1572,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1536,7 +1605,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1569,7 +1638,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1602,7 +1671,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1635,7 +1704,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1668,7 +1737,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1701,7 +1770,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1734,7 +1803,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1767,40 +1836,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
-        <modality>
-          <child>Mandatory</child>
-          <parent>Mandatory</parent>
-        </modality>
-        <columns>
-          <child>2</child>
-          <parent>0</parent>
-        </columns>
-      </relationship>
-    </item>
-    <item type="database-relationship" id="{75410d72-ad2c-4357-aab5-e97f9fb74da5}">
-      <line>
-        <connector-list>
-          <connector>
-            <position>
-              <x>511.719</x>
-              <y>191.875</y>
-            </position>
-            <angle>0</angle>
-            <hub owner="{2e6c3b80-fef6-4d2b-8945-7ea6be1b646c}"/>
-          </connector>
-          <connector>
-            <position>
-              <x>613.703</x>
-              <y>273</y>
-            </position>
-            <angle>90</angle>
-            <hub owner="{844766fc-c5af-4f61-b51b-d3f9393c60ca}"/>
-          </connector>
-        </connector-list>
-      </line>
-      <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1833,7 +1869,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1849,8 +1885,8 @@
         <connector-list>
           <connector>
             <position>
-              <x>554</x>
-              <y>148.906</y>
+              <x>549</x>
+              <y>136.906</y>
             </position>
             <angle>180</angle>
             <hub owner="{f8caa2d5-11ba-485c-b273-8183713896bf}"/>
@@ -1866,7 +1902,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1899,7 +1935,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1915,16 +1951,16 @@
         <connector-list>
           <connector>
             <position>
-              <x>632.109</x>
-              <y>117</y>
+              <x>627.109</x>
+              <y>105</y>
             </position>
             <angle>90</angle>
             <hub owner="{f8caa2d5-11ba-485c-b273-8183713896bf}"/>
           </connector>
           <connector>
             <position>
-              <x>613.016</x>
-              <y>91.8125</y>
+              <x>607.016</x>
+              <y>79.8125</y>
             </position>
             <angle>270</angle>
             <hub owner="{e84f8e5f-28e0-4d8b-a7e5-0f80a896f74e}"/>
@@ -1932,7 +1968,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1956,8 +1992,8 @@
           </connector>
           <connector>
             <position>
-              <x>563</x>
-              <y>66.9062</y>
+              <x>557</x>
+              <y>54.9062</y>
             </position>
             <angle>180</angle>
             <hub owner="{e84f8e5f-28e0-4d8b-a7e5-0f80a896f74e}"/>
@@ -1965,7 +2001,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -1982,7 +2018,7 @@
           <connector>
             <position>
               <x>698.406</x>
-              <y>311.906</y>
+              <y>318.906</y>
             </position>
             <angle>0</angle>
             <hub owner="{844766fc-c5af-4f61-b51b-d3f9393c60ca}"/>
@@ -1998,7 +2034,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -2031,7 +2067,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -2064,13 +2100,13 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
         </modality>
         <columns>
-          <child>11</child>
+          <child>10</child>
           <parent>0</parent>
         </columns>
       </relationship>
@@ -2080,7 +2116,7 @@
         <connector-list>
           <connector>
             <position>
-              <x>559.755</x>
+              <x>638.51</x>
               <y>395</y>
             </position>
             <angle>90</angle>
@@ -2088,8 +2124,8 @@
           </connector>
           <connector>
             <position>
-              <x>585.469</x>
-              <y>350.812</y>
+              <x>641.938</x>
+              <y>364.812</y>
             </position>
             <angle>270</angle>
             <hub owner="{844766fc-c5af-4f61-b51b-d3f9393c60ca}"/>
@@ -2097,7 +2133,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -2113,7 +2149,7 @@
         <connector-list>
           <connector>
             <position>
-              <x>638.51</x>
+              <x>559.755</x>
               <y>395</y>
             </position>
             <angle>90</angle>
@@ -2121,8 +2157,8 @@
           </connector>
           <connector>
             <position>
-              <x>641.938</x>
-              <y>350.812</y>
+              <x>585.469</x>
+              <y>364.812</y>
             </position>
             <angle>270</angle>
             <hub owner="{844766fc-c5af-4f61-b51b-d3f9393c60ca}"/>
@@ -2130,7 +2166,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -2163,7 +2199,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -2196,7 +2232,7 @@
         </connector-list>
       </line>
       <relationship>
-        <cardinality>OneToMany</cardinality>
+        <cardinality>OneToOne</cardinality>
         <modality>
           <child>Mandatory</child>
           <parent>Mandatory</parent>
@@ -2207,5 +2243,71 @@
         </columns>
       </relationship>
     </item>
+    <item type="database-relationship" id="{c672e45e-2bd4-4ff1-9b42-fdd2443cae6a}">
+      <line>
+        <connector-list>
+          <connector>
+            <position>
+              <x>543</x>
+              <y>213.906</y>
+            </position>
+            <angle>180</angle>
+            <hub owner="{b33d4844-b4e4-4ef6-944b-36b4113c2b82}"/>
+          </connector>
+          <connector>
+            <position>
+              <x>511.719</x>
+              <y>191.875</y>
+            </position>
+            <angle>0</angle>
+            <hub owner="{2e6c3b80-fef6-4d2b-8945-7ea6be1b646c}"/>
+          </connector>
+        </connector-list>
+      </line>
+      <relationship>
+        <cardinality>OneToOne</cardinality>
+        <modality>
+          <child>Mandatory</child>
+          <parent>Mandatory</parent>
+        </modality>
+        <columns>
+          <child>1</child>
+          <parent>0</parent>
+        </columns>
+      </relationship>
+    </item>
+    <item type="database-relationship" id="{0213a360-5a3b-484f-82c7-320003506c8a}">
+      <line>
+        <connector-list>
+          <connector>
+            <position>
+              <x>647.922</x>
+              <y>245.812</y>
+            </position>
+            <angle>270</angle>
+            <hub owner="{b33d4844-b4e4-4ef6-944b-36b4113c2b82}"/>
+          </connector>
+          <connector>
+            <position>
+              <x>613.703</x>
+              <y>273</y>
+            </position>
+            <angle>90</angle>
+            <hub owner="{844766fc-c5af-4f61-b51b-d3f9393c60ca}"/>
+          </connector>
+        </connector-list>
+      </line>
+      <relationship>
+        <cardinality>OneToMany</cardinality>
+        <modality>
+          <child>Optional</child>
+          <parent>Optional</parent>
+        </modality>
+        <columns>
+          <child>2</child>
+          <parent>0</parent>
+        </columns>
+      </relationship>
+    </item>
   </item-list>
 </diagram>
diff --git a/misc/database-layout.dump b/misc/database-layout.dump
index fffdef4..dc0b537 100644
--- a/misc/database-layout.dump
+++ b/misc/database-layout.dump
@@ -44,6 +44,30 @@ CREATE TEMPORARY TABLE `moveable_binary_packages_copy` (`id` BIGINT, `to_reposit
 DROP TEMPORARY TABLE IF EXISTS `moveable_binary_packages_copy2`;
 DROP TEMPORARY TABLE IF EXISTS `replaced_binary_packages_copy2`;
 CREATE TEMPORARY TABLE `replaced_binary_packages_copy2` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));
+DROP TEMPORARY TABLE IF EXISTS `package_blobs`;
+CREATE TEMPORARY TABLE `package_blobs` (`ps_a` BIGINT, `ps_b` BIGINT, UNIQUE KEY `content` (`ps_a`,`ps_b`));
+INSERT IGNORE INTO `package_blobs` (`ps_a`,`ps_b`)
+	SELECT `a_ps`.`id`,`b_ps`.`id`
+	FROM `package_sources` AS `a_ps`
+	JOIN `package_sources` AS `b_ps`
+	ON UNIX_TIMESTAMP(`a_ps`.`commit_time`) - UNIX_TIMESTAMP(`b_ps`.`commit_time`) BETWEEN -10 AND 10
+	JOIN `build_assignments` AS `a_ba`
+	ON `a_ps`.`id`=`a_ba`.`package_source`
+	JOIN `build_assignments` AS `b_ba`
+	ON `b_ps`.`id`=`b_ba`.`package_source`
+	JOIN `binary_packages` AS `a_bp`
+	ON `a_ba`.`id`=`a_bp`.`build_assignment`
+	JOIN `binary_packages` AS `b_bp`
+	ON `b_ba`.`id`=`b_bp`.`build_assignment`
+	JOIN `repositories` AS `a_r`
+	ON `a_bp`.`repository`=`a_r`.`id`
+	JOIN `repositories` AS `b_r`
+	ON `b_bp`.`repository`=`b_r`.`id`
+	JOIN `repository_stabilities` AS `a_rs`
+	ON `a_r`.`stability`=`a_rs`.`id`
+	JOIN `repository_stabilities` AS `b_rs`
+	ON `b_r`.`stability`=`b_rs`.`id`
+	WHERE `a_rs`.`name` = `from_stability` AND `b_rs`.`name` = `from_stability`;
 INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)
 	SELECT `binary_packages`.`id`,`repository_moves`.`to_repository`
 	FROM `binary_packages`
@@ -80,6 +104,7 @@ INSERT IGNORE INTO `replaced_binary_packages` (`id`,`replaced_by`)
 	JOIN `binary_packages` AS `r_bp`
 	ON `r_r`.`id`=`r_bp`.`repository` AND `r_bp`.`pkgname`=`m_bp`.`pkgname`;
 REPEAT
+SET row_count_saved = 0;
 DELETE
 	FROM `replaced_binary_packages_copy`;
 INSERT IGNORE INTO `replaced_binary_packages_copy`
@@ -108,7 +133,7 @@ DELETE `replaced_binary_packages`,`moveable_binary_packages`
 	JOIN `dependency_types`
 	ON `dependencies`.`dependency_type`=`dependency_types`.`id` AND `dependency_types`.`relevant_for_binary_packages`
 	WHERE NOT EXISTS (
-SELECT *
+SELECT 1
 	FROM `install_target_providers`
 	JOIN `binary_packages` AS `prov_bp`
 	ON `install_target_providers`.`package`=`prov_bp`.`id`
@@ -117,15 +142,15 @@ SELECT *
 	JOIN `repository_stability_relations`
 	ON `prov_r`.`stability`=`repository_stability_relations`.`more_stable`
 	WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on` AND `target_repositories`.`stability`=`repository_stability_relations`.`less_stable` AND NOT EXISTS (
-SELECT *
+SELECT 1
 	FROM `replaced_binary_packages_copy`
 	WHERE `replaced_binary_packages_copy`.`id`=`prov_bp`.`id`)) AND NOT EXISTS (
-SELECT *
+SELECT 1
 	FROM `install_target_providers`
 	JOIN `moveable_binary_packages_copy`
 	ON `moveable_binary_packages_copy`.`id`=`install_target_providers`.`package`
 	WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`);
-SET @row_count_saved = ROW_COUNT();
+SET row_count_saved = row_count_saved + ROW_COUNT();
 DELETE
 	FROM `replaced_binary_packages_copy`;
 INSERT IGNORE INTO `replaced_binary_packages_copy`
@@ -151,7 +176,7 @@ DELETE `replaced_binary_packages`,`moveable_binary_packages`
 	ON `repl_bp`.`id`=`install_target_providers`.`package`
 	JOIN `dependencies`
 	ON `install_target_providers`.`install_target`=`dependencies`.`depending_on` AND NOT EXISTS (
-SELECT *
+SELECT 1
 	FROM `replaced_binary_packages_copy`
 	WHERE `replaced_binary_packages_copy`.`id`=`dependencies`.`dependent`)
 	JOIN `dependency_types`
@@ -165,12 +190,12 @@ SELECT *
 	JOIN `repository_stability_relations` AS `repl_rr`
 	ON `repl_rr`.`more_stable`=`repl_r`.`stability` AND `repl_rr`.`less_stable`=`req_r`.`stability`
 	WHERE NOT EXISTS (
-SELECT *
+SELECT 1
 	FROM `moveable_binary_packages_copy`
 	JOIN `install_target_providers` AS `subst_itp`
 	ON `moveable_binary_packages_copy`.`id`=`subst_itp`.`package`
 	WHERE `subst_itp`.`install_target`=`install_target_providers`.`install_target`) AND NOT EXISTS (
-SELECT *
+SELECT 1
 	FROM `binary_packages` AS `subst_bp`
 	JOIN `install_target_providers` AS `subst_itp`
 	ON `subst_bp`.`id`=`subst_itp`.`package`
@@ -179,10 +204,45 @@ SELECT *
 	JOIN `repository_stability_relations` AS `subst_rr`
 	ON `subst_rr`.`more_stable`=`subst_r`.`stability`
 	WHERE `subst_rr`.`less_stable`=`repl_r`.`stability` AND NOT EXISTS (
-SELECT *
+SELECT 1
 	FROM `replaced_binary_packages_copy2`
 	WHERE `replaced_binary_packages_copy2`.`id`=`subst_bp`.`id`) AND `subst_itp`.`install_target`=`install_target_providers`.`install_target`);
-UNTIL row_count_saved=0 AND ROW_COUNT()=0
+SET row_count_saved = row_count_saved + ROW_COUNT();
+DELETE
+	FROM `replaced_binary_packages_copy`;
+INSERT IGNORE INTO `replaced_binary_packages_copy`
+	SELECT `replaced_binary_packages`.*
+	FROM `replaced_binary_packages`;
+DELETE
+	FROM `replaced_binary_packages_copy2`;
+INSERT IGNORE INTO `replaced_binary_packages_copy2`
+	SELECT `replaced_binary_packages`.*
+	FROM `replaced_binary_packages`;
+DELETE
+	FROM `moveable_binary_packages_copy`;
+INSERT IGNORE INTO `moveable_binary_packages_copy`
+	SELECT `moveable_binary_packages`.*
+	FROM `moveable_binary_packages`;
+DELETE `replaced_binary_packages`,`moveable_binary_packages`
+	FROM `replaced_binary_packages`
+	RIGHT JOIN `moveable_binary_packages`
+	ON `replaced_binary_packages`.`replaced_by`=`moveable_binary_packages`.`id`
+	JOIN `binary_packages`
+	ON `binary_packages`.`id`=`moveable_binary_packages`.`id`
+	JOIN `build_assignments`
+	ON `binary_packages`.`build_assignment`=`build_assignments`.`id`
+	JOIN `package_blobs`
+	ON `build_assignments`.`package_source`=`package_blobs`.`ps_a`
+	JOIN `build_assignments` AS `bl_ba`
+	ON `bl_ba`.`package_source`=`package_blobs`.`ps_b`
+	JOIN `binary_packages` AS `bl_bp`
+	ON `bl_ba`.`id`=`bl_bp`.`build_assignment`
+	WHERE NOT EXISTS (
+SELECT 1
+	FROM `moveable_binary_packages_copy`
+	WHERE `moveable_binary_packages_copy`.`id`=`bl_bp`.`id`);
+SET row_count_saved = row_count_saved + ROW_COUNT();
+UNTIL row_count_saved=0
 END REPEAT;
 DROP TEMPORARY TABLE `moveable_binary_packages_copy`;
 DROP TEMPORARY TABLE `replaced_binary_packages_copy`;
@@ -259,6 +319,7 @@ binary_packages	CREATE TABLE `binary_packages` (
 	 `architecture` smallint(6) NOT NULL,
 	 `is_to_be_deleted` bit(1) NOT NULL,
 	 `sha512sum` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+	 `last_moved` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 	 PRIMARY KEY (`id`),
 	 UNIQUE KEY `file_name` (`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architecture`,`repository`),
 	 UNIQUE KEY `content` (`build_assignment`,`sub_pkgrel`,`pkgname`,`architecture`,`repository`),
@@ -268,6 +329,16 @@ binary_packages	CREATE TABLE `binary_packages` (
 	 CONSTRAINT `binary_packages_ibfk_2` FOREIGN KEY (`architecture`) REFERENCES `architectures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 	 CONSTRAINT `binary_packages_ibfk_3` FOREIGN KEY (`build_assignment`) REFERENCES `build_assignments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+binary_packages_in_repositories	CREATE TABLE `binary_packages_in_repositories` (
+	 `id` bigint(20) NOT NULL AUTO_INCREMENT,
+	 `package` bigint(20) NOT NULL,
+	 `repository` mediumint(9) NOT NULL,
+	 PRIMARY KEY (`id`),
+	 UNIQUE KEY `content` (`package`,`repository`),
+	 KEY `binary_packages_in_repositories_ibfk_2` (`repository`),
+	 CONSTRAINT `binary_packages_in_repositories_ibfk_1` FOREIGN KEY (`package`) REFERENCES `binary_packages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
+	 CONSTRAINT `binary_packages_in_repositories_ibfk_2` FOREIGN KEY (`repository`) REFERENCES `repositories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 build_assignments	CREATE TABLE `build_assignments` (
 	 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 	 `package_source` bigint(20) NOT NULL,
@@ -276,7 +347,7 @@ build_assignments	CREATE TABLE `build_assignments` (
 	 `is_broken` bit(1) NOT NULL,
 	 `priority` smallint(6) NOT NULL,
 	 `is_black_listed` text COLLATE utf8mb4_unicode_ci,
-	 `return_date` datetime DEFAULT NULL,
+	 `return_date` timestamp NULL DEFAULT NULL,
 	 PRIMARY KEY (`id`),
 	 UNIQUE KEY `content` (`package_source`,`architecture`),
 	 KEY `architecture` (`architecture`),
@@ -335,7 +406,7 @@ email_actions	CREATE TABLE `email_actions` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 email_log	CREATE TABLE `email_log` (
 	 `id` bigint(20) NOT NULL AUTO_INCREMENT,
-	 `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 	 `success` bit(1) NOT NULL,
 	 `action` mediumint(9) DEFAULT NULL,
 	 `count` mediumint(9) DEFAULT NULL,
@@ -359,7 +430,7 @@ failed_builds	CREATE TABLE `failed_builds` (
 	 `id` mediumint(9) NOT NULL AUTO_INCREMENT,
 	 `build_slave` mediumint(9) NOT NULL,
 	 `build_assignment` bigint(20) NOT NULL,
-	 `date` datetime NOT NULL,
+	 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 	 `reason` smallint(6) NOT NULL,
 	 `log_file` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL,
 	 PRIMARY KEY (`id`),
@@ -414,7 +485,7 @@ package_sources	CREATE TABLE `package_sources` (
 	 `upstream_package_repository` smallint(6) NOT NULL,
 	 `uses_upstream` bit(1) NOT NULL,
 	 `uses_modification` bit(1) NOT NULL,
-	 `commit_time` datetime NOT NULL,
+	 `commit_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 	 PRIMARY KEY (`id`),
 	 UNIQUE KEY `content` (`pkgbase`,`git_revision`,`mod_git_revision`),
 	 KEY `upstream_package_repository` (`upstream_package_repository`),
@@ -431,10 +502,13 @@ repositories	CREATE TABLE `repositories` (
 	 `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
 	 `stability` mediumint(9) NOT NULL,
 	 `is_on_master_mirror` bit(1) NOT NULL,
+	 `architecture` smallint(6) NOT NULL,
 	 PRIMARY KEY (`id`),
 	 UNIQUE KEY `name` (`name`),
 	 KEY `stability` (`stability`),
-	 CONSTRAINT `repositories_ibfk_1` FOREIGN KEY (`stability`) REFERENCES `repository_stabilities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+	 KEY `architecture` (`architecture`),
+	 CONSTRAINT `repositories_ibfk_1` FOREIGN KEY (`stability`) REFERENCES `repository_stabilities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
+	 CONSTRAINT `repositories_ibfk_2` FOREIGN KEY (`architecture`) REFERENCES `architectures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 repository_moves	CREATE TABLE `repository_moves` (
 	 `id` mediumint(9) NOT NULL AUTO_INCREMENT,
@@ -476,7 +550,7 @@ ssh_keys	CREATE TABLE `ssh_keys` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 ssh_log	CREATE TABLE `ssh_log` (
 	 `id` bigint(20) NOT NULL AUTO_INCREMENT,
-	 `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 	 `build_slave` mediumint(9) DEFAULT NULL,
 	 `action` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
 	 `parameters` text COLLATE utf8mb4_unicode_ci NOT NULL,
@@ -486,7 +560,7 @@ ssh_log	CREATE TABLE `ssh_log` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
 statistics	CREATE TABLE `statistics` (
 	 `id` bigint(20) NOT NULL AUTO_INCREMENT,
-	 `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
+	 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 	 `stable_packages_count` mediumint(9) NOT NULL,
 	 `pending_tasks_count` mediumint(9) NOT NULL,
 	 `pending_packages_count` mediumint(9) NOT NULL,