bin/seed-build-list: huge cleanup

1 files changed, 315 insertions, 259 deletions

diff --git a/bin/seed-build-list b/bin/seed-build-list
index 44766aa..cfcd49e 100755
--- a/bin/seed-build-list
+++ b/bin/seed-build-list
@@ -27,9 +27,9 @@ usage() {
   >&2 echo '    conf/seed-ignore-packages.'
   >&2 echo '  -n|--no-action:'
   >&2 echo '    Do not actually update build-list, just print it.'
-  >&2 echo '  -p|--package $pkgname_regex:'
-  >&2 echo '    Reschedule packages matching $pkgname_regex. Note, that'
-  >&2 echo '    these packages must be known to the database.'
+  >&2 echo '  -p|--package $pkg_regex:'
+  >&2 echo '    Reschedule packages with matching pkgname or pkgbase.'
+  >&2 echo '    Note, that these packages must be known to the database.'
   >&2 echo '  -u|--undelete $url: '
   >&2 echo '    Schedule all former deletion-list packages which do not'
   >&2 echo '    belong on the deletion-list anymore (e.g. can be built)'
@@ -57,15 +57,15 @@ eval set -- "$(
     echo usage
   )"
 
-touch "${tmp_dir}/ignore-packages"
-touch "${tmp_dir}/mirrors"
-touch "${tmp_dir}/package-regexes"
-touch "${tmp_dir}/undelete-mirrors"
-
 auto=false
 update=true
 wait_for_lock='-n'
 
+if [ -r "${base_dir}/conf/seed-ignore-packages" ]; then
+  sed 's/^/2\t/' "${base_dir}/conf/seed-ignore-packages" >> \
+    "${tmp_dir}/ignore-packages"
+fi
+
 while true
 do
   case "$1" in
@@ -77,12 +77,12 @@ do
     ;;
     -i|--ignore)
       shift
-      echo "$1" >> \
+      printf '3\t%s\n' "$1" >> \
         "${tmp_dir}/ignore-packages"
     ;;
     -m|--mirror)
       shift
-      echo "$1" >> \
+      printf '%s\n' "$1" >> \
         "${tmp_dir}/mirrors"
     ;;
     -n|--no-action)
@@ -90,12 +90,12 @@ do
     ;;
     -p|--package)
       shift
-      echo "$1" >> \
+      printf '%s\n' "$1" >> \
         "${tmp_dir}/package-regexes"
     ;;
     -u|--undelete)
       shift
-      echo "$1" >> \
+      printf '%s\n' "$1" >> \
         "${tmp_dir}/undelete-mirrors"
     ;;
     -w|--wait)
@@ -106,7 +106,8 @@ do
       break
     ;;
     *)
-      >&2 echo 'Whoops, forgot to implement option "'"$1"'" internally.'
+      >&2 printf 'Whoops, forgot to implement option "%s" internally.\n' \
+        "$1"
       exit 42
     ;;
   esac
@@ -122,6 +123,8 @@ if [ ! -s "${tmp_dir}/mirrors" ] && \
   [ ! -s "${tmp_dir}/undelete-mirrors" ] && \
   ! ${auto}; then
   # nothing to do
+  >&2 echo 'No options given to do anything.'
+  usage 0
   exit 0
 fi
 
@@ -153,64 +156,65 @@ repos=$(
   printf '%s\n' 'multilib'
 )
 
-# TODO: this needs to be cleaned up - there is too much chaos in here
+# TODO: translate lib32-* packages instead of ignoring them for -u and -m
 
-# generate must-haves query from mirror delta
+# harvest pkgnames from mirror delta
 if [ -s "${tmp_dir}/mirrors" ]; then
   {
+    # theirs
+    while read -r mirror; do
+      if [ -z "${mirror}" ]; then
+        continue
+      fi
+      for repo in ${repos}; do
+        curl -sS "${mirror}/${repo}/os/x86_64/${repo}.db.tar.gz" | \
+          tar -Oxz --wildcards '*/desc' | \
+          sed '
+            /^%FILENAME%$/!d
+            N
+            s/^.*\n//
+            /^lib32-/d
+            s/-x86_64\(\.pkg\.tar\.xz\)$/-i686\1/
+            s/^\(.*\)-\([^-]\+-[^-]\+\)-\([^-]\+\)$/theirs \2 \3 \1/
+          '
+      done
+    done < \
+      "${tmp_dir}/mirrors"
+    # ours
+    # shellcheck disable=SC2016
     {
-      # theirs
-      while read -r mirror; do
-        if [ -z "${mirror}" ]; then
-          continue
-        fi
-        for repo in ${repos}; do
-          curl -sS "${mirror}/${repo}/os/x86_64/${repo}.db.tar.gz" | \
-            tar -Oxz --wildcards '*/desc' | \
-            sed '
-              /^%FILENAME%$/!d
-              N
-              s/^.*\n//
-              s/-x86_64\(\.pkg\.tar\.xz\)$/-i686\1/
-              s/^\(.*\)-\([^-]\+-[^-]\+\)-\([^-]\+\)$/theirs \2 \3 \1/
-            '
-        done
-      done < \
-        "${tmp_dir}/mirrors"
-      # ours
-      # shellcheck disable=SC2016
-      {
-        printf 'SELECT '
-        mysql_package_name_query
-        printf ' FROM `binary_packages`'
-        mysql_join_binary_packages_architectures
-        mysql_join_binary_packages_binary_packages_in_repositories
-        printf ' WHERE NOT `binary_packages_in_repositories`.`is_to_be_deleted`'
-      } | \
-        mysql_run_query | \
-        sed '
-          s/^\(.*\)-\([^-]\+-[^-]\+\)-\([^-]\+\)$/ours \2 \3 \1/
-        '
+      printf 'SELECT '
+      mysql_package_name_query
+      printf ' FROM `binary_packages`'
+      mysql_join_binary_packages_architectures
+      mysql_join_binary_packages_binary_packages_in_repositories
+      printf ' WHERE NOT `binary_packages_in_repositories`.`is_to_be_deleted`'
     } | \
-      expand_version 2 | \
-      sort -k3,4 -k2Vr,2 -k1,1 | \
-      shrink_version 2 | \
-      uniq -f2 | \
-      sed -n '
-        s/^theirs \(\S\+ \)\{2\}//
-        T
-        p
-      ' | \
-      sort -u
-    if [ -r "${base_dir}/conf/seed-ignore-packages" ]; then
-      sed 'p' "${base_dir}/conf/seed-ignore-packages"
-    fi
+      mysql_run_query | \
+      sed '
+        s/^\(.*\)-\([^-]\+-[^-]\+\)-\([^-]\+\)$/ours \2 \3 \1/
+      '
   } | \
-    sort | \
-    uniq -u > \
-    "${tmp_dir}/must-haves"
+    expand_version 2 | \
+    sort -k3,4 -k2Vr,2 -k1,1 | \
+    shrink_version 2 | \
+    uniq -f2 | \
+    sed -n '
+      s/^theirs \(\S\+ \)\{2\}//
+      T
+      p
+    ' | \
+    if [ -r "${base_dir}/conf/seed-ignore-packages" ]; then
+      grep -vxF "$(cat "${base_dir}/conf/seed-ignore-packages")"
+    else
+      cat
+    fi | \
+    sed 's/^/1\t/' | \
+    sort -u >> \
+    "${tmp_dir}/pkgnames"
 fi
 
+# harvest pkgnames from undelete-mirrors
 if [ -s "${tmp_dir}/undelete-mirrors" ]; then
   while read -r mirror; do
     if [ -z "${mirror}" ]; then
@@ -224,83 +228,179 @@ if [ -s "${tmp_dir}/undelete-mirrors" ]; then
           N
           s/^.*\n//
           s/-x86_64\(\.pkg\.tar\.xz\)$/-i686\1/
-          s/^\(.*\)\(-[^-]\+\)\{3\}$/\1/
           /^lib32-/d
+          s/^\(.*\)\(-[^-]\+\)\{3\}$/\1/
         '
     done
   done < \
     "${tmp_dir}/undelete-mirrors" | \
-    sort -u > \
-    "${tmp_dir}/available"
+    if [ -r "${base_dir}/conf/seed-ignore-packages" ]; then
+      grep -vxF "$(cat "${base_dir}/conf/seed-ignore-packages")"
+    else
+      cat
+    fi | \
+    sed 's/^/2\t/' | \
+    sort -u >> \
+    "${tmp_dir}/pkgnames"
+fi
+
+# harvest from the package-regexes
+if [ -s "${tmp_dir}/package-regexes" ]; then
+  # shellcheck disable=SC2016
+  {
+    printf 'CREATE TEMPORARY TABLE `names` (`name` VARCHAR(64));\n'
+    printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `names` (`name`);\n' \
+      "${tmp_dir}/package-regexes"
+
+    printf 'SELECT DISTINCT "pkgbase",`package_sources`.`pkgbase`,`upstream_repositories`.`name`'
+    printf ' FROM `names`'
+    printf ' JOIN `package_sources` ON `package_sources`.`pkgbase` REGEXP `names`.`name`'
+    mysql_join_package_sources_upstream_repositories
+    printf ';\n'
+
+    printf 'SELECT DISTINCT "pkgname",`binary_packages`.`pkgname`'
+    printf ' FROM `names`'
+    printf ' JOIN `binary_packages` ON `binary_packages`.`pkgname` REGEXP `names`.`name`;\n'
+  } | \
+    mysql_run_query | \
+    sed -n '
+      /^pkgbase\s/ {
+        s/^\S\+\s/3\t/
+        w /dev/stdout
+        d
+      }
+      /^pkgname\s/ {
+        s/^\S\+\s/3\t/
+        w /dev/stderr
+        d
+      }
+    ' \
+    >>"${tmp_dir}/pkgbases" \
+    2>>"${tmp_dir}/pkgnames"
 fi
 
 # shellcheck disable=SC2016
 {
-  printf 'CREATE TEMPORARY TABLE `ignore_packages` (`pkgname` VARCHAR(64));\n'
-  if [ -s "${tmp_dir}/ignore-packages" ]; then
-    grep -vxF '' "${tmp_dir}/ignore-packages" | \
-      base64_encode_each | \
-      sed '
-        s/^/(from_base64("/
-        s/$/")),/
-        $s/,$/;/
-        1 s/^/INSERT INTO `ignore_packages` (`pkgname`) VALUES \n/
-      '
+  # create pkgbases to given pkgnames
+  if [ -s "${tmp_dir}/pkgnames" ]; then
+    printf 'CREATE TEMPORARY TABLE `pkgnames` ('
+      printf '`priority` SMALLINT,'
+      printf '`pkgname` VARCHAR(64),'
+      printf 'UNIQUE KEY `pkgname`(`pkgname`)'
+    printf ');\n'
+    printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `pkgnames` (`priority`,`pkgname`);\n' \
+      "${tmp_dir}/pkgnames"
+
+    printf 'SELECT DISTINCT "pkgbase",`pkgnames`.`priority`,`package_sources`.`pkgbase`,`upstream_repositories`.`name`'
+    printf ' FROM `pkgnames`'
+    printf ' JOIN `binary_packages`'
+    printf ' ON `binary_packages`.`pkgname`=`pkgnames`.`pkgname`'
+    mysql_join_binary_packages_build_assignments
+    mysql_join_build_assignments_package_sources
+    mysql_join_package_sources_upstream_repositories
+    printf ';\n'
+
+    printf 'SELECT DISTINCT "pkgname",`pkgnames`.`priority`,`pkgnames`.`pkgname`'
+    printf ' FROM `package_sources`'
+    mysql_join_package_sources_upstream_repositories
+    mysql_join_package_sources_build_assignments
+    mysql_join_build_assignments_binary_packages
+    printf ' RIGHT JOIN `pkgnames`'
+    printf ' ON `pkgnames`.`pkgname`=`binary_packages`.`pkgname`'
+    printf ' WHERE `package_sources`.`pkgbase` IS NULL;\n'
+
+    printf 'DROP TEMPORARY TABLE `pkgnames`;\n'
+  fi
+  # auto-detect pkgbases
+  if ${auto}; then
+    printf 'SELECT DISTINCT "pkgbase",1,`package_sources`.`pkgbase`,`upstream_repositories`.`name` FROM ('
+      printf 'SELECT DISTINCT `binary_packages`.`id`,'
+      printf '`binary_packages`.`build_assignment`'
+      printf ' FROM ('
+        printf 'SELECT `binary_packages`.`pkgname`,'
+        printf 'MAX(`repositories`.`stability`) AS `stability`'
+        printf ' FROM `binary_packages`'
+        mysql_join_binary_packages_binary_packages_in_repositories
+        mysql_join_binary_packages_in_repositories_repositories
+        printf ' AND ('
+          printf '`repositories`.`is_on_master_mirror`'
+          # we need to take into account build-list items here, because
+          # they may make re-scheduling unnecessary
+          printf ' OR `repositories`.`name`="build-list"'
+        printf ') AND `repositories`.`name`!="build-support"'
+        printf ' GROUP BY `binary_packages`.`pkgname`'
+      printf ') AS `least_stable`'
+      printf ' JOIN `binary_packages`'
+      printf ' ON `binary_packages`.`pkgname`=`least_stable`.`pkgname`'
+      mysql_join_binary_packages_binary_packages_in_repositories
+      mysql_join_binary_packages_in_repositories_repositories
+      printf ' AND `repositories`.`stability`=`least_stable`.`stability`'
+      # now we can drop build-list items
+      printf ' AND `repositories`.`is_on_master_mirror`'
+    printf ') AS `binary_packages`'
+    mysql_join_binary_packages_build_assignments
+    mysql_join_build_assignments_package_sources
+    mysql_join_package_sources_upstream_repositories
+    mysql_join_binary_packages_dependencies
+    mysql_join_dependencies_dependency_types
+    printf ' AND `dependency_types`.`relevant_for_binary_packages`'
+    mysql_join_dependencies_install_target_providers '' 'itp_dummy'
+    # some dependencies are not provided by the least stable packages
+    printf ' WHERE NOT EXISTS ('
+      printf 'SELECT 1 FROM `install_target_providers`'
+      mysql_join_install_target_providers_binary_packages '' 'subst_bp'
+      mysql_join_binary_packages_binary_packages_in_repositories 'subst_bp' 'subst_bir'
+      mysql_join_binary_packages_in_repositories_repositories 'subst_bir' 'subst_r'
+      printf ' AND `subst_r`.`name` NOT IN ("build-support","deletion-list","to-be-decided")'
+      printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
+      # this is the least stable, built install_target_provider with that name
+      printf ' AND NOT EXISTS ('
+        printf 'SELECT 1 FROM `binary_packages` AS `ss_bp`'
+        mysql_join_binary_packages_binary_packages_in_repositories 'ss_bp' 'ss_bir'
+        mysql_join_binary_packages_in_repositories_repositories 'ss_bir' 'ss_r'
+        printf ' AND `ss_r`.`name` NOT IN ("build-list","build-support","deletion-list","to-be-decided")'
+        printf ' JOIN `repository_stability_relations` AS `ss_rsr`'
+        printf ' ON `ss_rsr`.`less_stable`=`ss_r`.`stability`'
+        printf ' WHERE `ss_bp`.`pkgname`=`subst_bp`.`pkgname`'
+        printf ' AND `ss_bp`.`id`!=`subst_bp`.`id`'
+        printf ' AND `ss_rsr`.`more_stable`=`subst_r`.`stability`'
+      printf ')'
+    printf ')'
+    printf ';\n'
   fi
-  # packages on the build-list or deletion-list should be ignored
-  printf 'INSERT IGNORE INTO `ignore_packages` (`pkgname`)'
-  printf ' SELECT DISTINCT `ignore_bin`.`pkgname`'
-  printf ' FROM `binary_packages` AS `ignore_bin`'
-  mysql_join_binary_packages_binary_packages_in_repositories 'ignore_bin'
-  printf ' WHERE `binary_packages_in_repositories`.`repository` IN (%s,%s)' \
-    "${repository_ids__any_build_list}" \
-    "${repository_ids__any_deletion_list}"
-  # packages with no not-to-be-deleted and at least one to-be-deleted version should be ignored
-  printf ' OR ('
-  printf '`binary_packages_in_repositories`.`is_to_be_deleted`'
-  printf ' AND NOT EXISTS ('
-    printf 'SELECT 1 FROM `binary_packages` AS `other_bin`'
-    mysql_join_binary_packages_binary_packages_in_repositories 'other_bin' 'other_bpir'
-    printf ' WHERE NOT `other_bpir`.`is_to_be_deleted`'
-    printf ' AND `other_bin`.`pkgname`=`ignore_bin`.`pkgname`'
-  printf '));\n'
 } | \
-  sponge "${tmp_dir}/ignore-packages"
+  mysql_run_query | \
+  sed -n '
+    /^pkgbase\s/ {
+      s/^\S\+\s//
+      w /dev/stderr
+      d
+    }
+    /^pkgname\s/ {
+      s/^\S\+\s//
+      w /dev/stdout
+      d
+    }
+  ' \
+  2>>"${tmp_dir}/pkgbases" \
+  | sponge "${tmp_dir}/pkgnames"
 
-# shellcheck disable=SC2016
-{
-  printf 'CREATE TEMPORARY TABLE `must_haves` (`pkgname` VARCHAR(64));\n'
-  if [ -s "${tmp_dir}/must-haves" ]; then
-    grep -vxF '' "${tmp_dir}/must-haves" | \
-      base64_encode_each | \
-      sed '
-        s/^/(from_base64("/
-        s/$/")),/
-        $s/,$/;/
-        1 s/^/INSERT INTO `must_haves` (`pkgname`) VALUES \n/
-      '
+if [ ! -s "${tmp_dir}/pkgbases" ] && \
+  [ ! -s "${tmp_dir}/pkgnames" ]; then
+  >&2 echo 'No packages matched.'
+  exit
+fi
+
+for s in "${tmp_dir}/pkgbases" "${tmp_dir}/pkgnames"; do
+  if [ -s "${s}" ]; then
+    sort -u "${s}" | \
+      sponge "${s}"
   fi
-} | \
-  sponge "${tmp_dir}/must-haves"
+done
 
-# fetch unknown must-haves from upstream
-# shellcheck disable=SC2016
-printf 'CREATE TEMPORARY TABLE `pkgbases` (`pkgbase` VARCHAR(64), `repository` VARCHAR(64));\n' > \
-  "${tmp_dir}/pkgbases"
-# shellcheck disable=SC2016
-{
-  cat "${tmp_dir}/must-haves" "${tmp_dir}/ignore-packages"
-  printf 'SELECT `must_haves`.`pkgname` FROM `must_haves`'
-  printf ' WHERE NOT EXISTS ('
-    printf 'SELECT 1 FROM `binary_packages`'
-    printf ' WHERE `binary_packages`.`pkgname`=`must_haves`.`pkgname`'
-  printf ') AND NOT EXISTS ('
-    printf 'SELECT 1 FROM `ignore_packages`'
-    printf ' WHERE `ignore_packages`.`pkgname`=`must_haves`.`pkgname`'
-  printf ') AND NOT `must_haves`.`pkgname` LIKE "lib32-%%";\n'
-} | \
-  mysql_run_query | \
-  while read -r pkgname; do
+# pkgnames -> pkgbases (with help from upstream)
+if [ -s "${tmp_dir}/pkgnames" ]; then
+  while read -r priority pkgname; do
     content=$(
       curl -Ss 'https://www.archlinux.org/packages/search/json/?name='"${pkgname}" | \
         tr ',' '\n'
@@ -326,103 +426,84 @@ printf 'CREATE TEMPORARY TABLE `pkgbases` (`pkgbase` VARCHAR(64), `repository` V
         '
     )
     if [ -z "${pkgbase}" ] || [ -z "${repo}" ]; then
-      >&2 printf 'Could not find "%s" which is newer on x86_64!?\n' "${pkgname}"
-      exit 2
+      printf '%s\t%s\n' "${priority}" "${pkgname}"
+      continue
     fi
-    printf '(from_base64("%s"),from_base64("%s")),\n' \
-      "$(printf '%s' "${pkgbase}" | base64 -w0)" \
-      "$(printf '%s' "${repo}" | base64 -w0)"
-  done | \
-  sort -u | \
-  sed '
-    1 s/^/INSERT IGNORE INTO `pkgbases` (`pkgbase`,`repository`) VALUES \n/
-    $s/,$/;/
-  ' >> \
-  "${tmp_dir}/pkgbases"
+    printf '%s\t%s\t%s\n' \
+      "${priority}" \
+      "${pkgbase}" \
+      "${repo}" >> \
+      "${tmp_dir}/pkgbases"
+  done < \
+    "${tmp_dir}/pkgnames" | \
+    sponge "${tmp_dir}/pkgnames"
+fi
+
+if [ -s "${tmp_dir}/pkgnames" ]; then
+  >&2 echo 'Could not find some packages upstream:'
+  >&2 cat "${tmp_dir}/pkgnames"
+  exit 2
+fi
+
+# now we (re)schedule all pkgbases which are:
+#  - not explicitely ignored (priority 3)             <- explicitely scheduled
+#  - not explicitely/implicitely ignored, but not currently available
+#    (priority 2) <- from undelete-mirror-delta
+#  - neither ignored nor to-be-deleted (priority 1)   <- from mirror-delta or auto-rebuild
 
 # shellcheck disable=SC2016
 {
-  cat "${tmp_dir}/must-haves" "${tmp_dir}/ignore-packages" "${tmp_dir}/pkgbases"
-  if ${auto} || \
-    [ -s "${tmp_dir}/available" ]; then
-    printf 'CREATE TEMPORARY TABLE `bin_ids` (`id` BIGINT, UNIQUE KEY (`id`));\n'
-  fi
-  if ${auto}; then
-    printf 'INSERT IGNORE INTO `bin_ids`'
-    printf ' SELECT `binary_packages`.`id` FROM `binary_packages`'
-    mysql_join_binary_packages_binary_packages_in_repositories
-    mysql_join_binary_packages_in_repositories_repositories
-    printf ' AND `repositories`.`is_on_master_mirror`'
-    printf ' AND `repositories`.`name`!="build-support"'
-    mysql_join_binary_packages_dependencies
+  printf 'CREATE TEMPORARY TABLE `ignore_packages` ('
+    printf '`priority` SMALLINT,'
+    printf '`pkgbase` VARCHAR(64),'
+    printf 'UNIQUE KEY `content`(`priority`,`pkgbase`)'
+  printf ');\n'
+  printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `ignore_packages`(`priority`,`pkgbase`);\n' \
+    "${tmp_dir}/ignore-packages"
+
+  printf 'CREATE TEMPORARY TABLE `pkgbases` ('
+    printf '`priority` SMALLINT,'
+    printf '`pkgbase` VARCHAR(64),'
+    printf '`repo` VARCHAR(64),'
+    printf 'UNIQUE KEY `pkgbase`(`pkgbase`)'
+  printf ');\n'
+  printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `pkgbases`(`priority`,`pkgbase`,`repo`);\n' \
+    "${tmp_dir}/pkgbases"
+
+  printf 'INSERT IGNORE INTO `ignore_packages`(`priority`,`pkgbase`)'
+  printf ' SELECT 2,`package_sources`.`pkgbase`'
+  printf ' FROM `package_sources`'
+  mysql_join_package_sources_build_assignments
+  mysql_join_build_assignments_binary_packages
+  printf ' WHERE `build_assignments`.`is_black_listed` IS NOT NULL'
+  printf ' OR `package_sources`.`pkgbase` LIKE "lib32-%%"'
+  printf ' OR EXISTS ('
+    printf 'SELECT 1 FROM `dependencies`'
     mysql_join_dependencies_dependency_types
-    printf ' AND `dependency_types`.`relevant_for_binary_packages`'
-    # nothing "less stable" has the same name
-    printf ' WHERE NOT EXISTS ('
-      printf 'SELECT 1 FROM `binary_packages` AS `subst_bp`'
-      mysql_join_binary_packages_binary_packages_in_repositories 'subst_bp' 'subst_bir'
-      mysql_join_binary_packages_in_repositories_repositories 'subst_bir' 'subst_r'
-      printf ' AND `subst_r`.`name`!="build-support"'
-      printf ' JOIN `repository_stability_relations` ON `repository_stability_relations`.`less_stable`=`subst_r`.`stability`'
-      printf ' WHERE `repository_stability_relations`.`more_stable`=`repositories`.`stability`'
-      printf ' AND `subst_bp`.`id`!=`binary_packages`.`id`'
-      printf ' AND `subst_bp`.`pkgname`=`binary_packages`.`pkgname`'
-    printf ') AND NOT EXISTS ('
-      printf 'SELECT 1 FROM `install_target_providers`'
-      mysql_join_install_target_providers_binary_packages '' 'subst_bp'
-      mysql_join_binary_packages_binary_packages_in_repositories 'subst_bp' 'subst_bir'
-      mysql_join_binary_packages_in_repositories_repositories 'subst_bir' 'subst_r'
-      printf ' AND `subst_r`.`name` NOT IN ("build-support","deletion-list","to-be-decided")'
-      printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
-      # this is the least stable, built install_target_provider with that name
-      printf ' AND NOT EXISTS ('
-        printf 'SELECT 1 FROM `binary_packages` AS `ss_bp`'
-        mysql_join_binary_packages_binary_packages_in_repositories 'ss_bp' 'ss_bir'
-        mysql_join_binary_packages_in_repositories_repositories 'ss_bir' 'ss_r'
-        printf ' AND `ss_r`.`name` NOT IN ("build-list","build-support","deletion-list","to-be-decided")'
-        printf ' JOIN `repository_stability_relations` AS `ss_rsr`'
-        printf ' ON `ss_rsr`.`less_stable`=`ss_r`.`stability`'
-        printf ' WHERE `ss_bp`.`pkgname`=`subst_bp`.`pkgname`'
-        printf ' AND `ss_bp`.`id`!=`subst_bp`.`id`'
-        printf ' AND `ss_rsr`.`more_stable`=`subst_r`.`stability`'
-      printf ')'
+    printf ' AND ('
+      printf '`dependency_types`.`relevant_for_building`'
+      printf ' OR `dependency_types`.`relevant_for_binary_packages`'
     printf ')'
-    printf ';\n'
-  fi
-  if [ -s "${tmp_dir}/available" ]; then
-    printf 'CREATE TEMPORARY TABLE `available` (`pkgname` VARCHAR(64), UNIQUE KEY (`pkgname`));\n'
-    printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `available`;\n' \
-      "${tmp_dir}/available"
-
-    printf 'INSERT IGNORE INTO `bin_ids`'
-    printf ' SELECT `binary_packages`.`id` FROM `binary_packages`'
-    mysql_join_binary_packages_binary_packages_in_repositories
-    printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
-      "${repository_ids__any_deletion_list}"
-    printf ' JOIN `available` ON `available`.`pkgname`=`binary_packages`.`pkgname`'
-    mysql_join_binary_packages_build_assignments
-    printf ' WHERE `binary_packages`.`pkgname` NOT LIKE "lib32-%%"'
-    printf ' AND `build_assignments`.`is_black_listed` IS NULL'
+    mysql_join_dependencies_install_target_providers '' 'it_dummy'
+    printf ' WHERE `dependencies`.`dependent`=`binary_packages`.`id`'
     printf ' AND NOT EXISTS ('
-      printf 'SELECT 1 FROM `dependencies`'
-      mysql_join_dependencies_dependency_types
-      printf ' AND ('
-        printf '`dependency_types`.`relevant_for_building`'
-        printf ' OR `dependency_types`.`relevant_for_binary_packages`'
-      printf ')'
-      mysql_join_dependencies_install_target_providers '' 'it_dummy'
-      printf ' WHERE `dependencies`.`dependent`=`binary_packages`.`id`'
-      printf ' AND NOT EXISTS ('
-        printf 'SELECT 1 FROM `install_target_providers`'
-        mysql_join_install_target_providers_binary_packages '' 'it_bp'
-        mysql_join_binary_packages_binary_packages_in_repositories 'it_bp' 'it_bpir'
-        printf ' WHERE NOT `it_bpir`.`is_to_be_deleted`'
-        printf ' AND `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
-      printf ')'
-    printf ');\n'
-    printf 'DELETE `ignore_packages` FROM `ignore_packages`'
-    printf ' JOIN `available` ON `available`.`pkgname`=`ignore_packages`.`pkgname`;\n'
-  fi
+      printf 'SELECT 1 FROM `install_target_providers`'
+      mysql_join_install_target_providers_binary_packages '' 'it_bp'
+      mysql_join_binary_packages_binary_packages_in_repositories 'it_bp' 'it_bpir'
+      printf ' WHERE NOT `it_bpir`.`is_to_be_deleted`'
+      printf ' AND `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
+    printf ')'
+  printf ');\n'
+
+  printf 'INSERT IGNORE INTO `ignore_packages`(`priority`,`pkgbase`)'
+  printf ' SELECT 1,`package_sources`.`pkgbase`'
+  printf ' FROM `package_sources`'
+  mysql_join_package_sources_build_assignments
+  mysql_join_build_assignments_binary_packages
+  mysql_join_binary_packages_binary_packages_in_repositories
+  printf ' WHERE `binary_packages_in_repositories`.`is_to_be_deleted`'
+  printf ';\n'
+
   printf 'SELECT '
   printf '`pkgbases`.`pkgbase`,'
   printf '`git_repositories`.`head`,'
@@ -430,54 +511,29 @@ printf 'CREATE TEMPORARY TABLE `pkgbases` (`pkgbase` VARCHAR(64), `repository` V
     printf 'SELECT `al32`.`head` FROM `git_repositories` AS `al32`'
     printf ' WHERE `al32`.`name`="archlinux32"'
   printf '),'
-  printf '`pkgbases`.`repository`'
+  printf '`pkgbases`.`repo`'
   printf ' FROM `pkgbases`'
-  printf ' JOIN `upstream_repositories` ON `upstream_repositories`.`name`=`pkgbases`.`repository`'
+  printf ' JOIN `upstream_repositories`'
+  printf ' ON `upstream_repositories`.`name`=`pkgbases`.`repo`'
   mysql_join_upstream_repositories_git_repositories
   printf ' WHERE NOT EXISTS ('
     printf 'SELECT 1 FROM `ignore_packages`'
-    printf ' JOIN `binary_packages` AS `i_bp` ON `ignore_packages`.`pkgname`=`i_bp`.`pkgname`'
-    mysql_join_binary_packages_build_assignments 'i_bp' 'i_ba'
-    mysql_join_build_assignments_package_sources 'i_ba' 'i_ps'
-    printf ' WHERE `i_ps`.`pkgbase`=`pkgbases`.`pkgbase`'
-  printf ');\n'
-  printf 'SELECT '
-  printf '`package_sources`.`pkgbase`,'
-  printf '`git_repositories`.`head`,'
-  printf '('
-    printf 'SELECT `al32`.`head` FROM `git_repositories` AS `al32`'
-    printf ' WHERE `al32`.`name`="archlinux32"'
-  printf '),'
-  printf '`upstream_repositories`.`name`'
-  printf ' FROM `binary_packages`'
-  mysql_join_binary_packages_build_assignments
-  mysql_join_build_assignments_package_sources
-  mysql_join_package_sources_upstream_repositories
-  mysql_join_upstream_repositories_git_repositories
-  printf ' WHERE ('
-  if [ -s "${tmp_dir}/package-regexes" ]; then
-    grep -vxF '' "${tmp_dir}/package-regexes" | \
-      base64_encode_each | \
-      sed '
-        s/^/`binary_packages`.`pkgname` REGEXP from_base64("/
-        s/$/") OR /
-      ' | \
-      tr -d '\n'
-  fi
-  if ${auto} || \
-    [ -s "${tmp_dir}/available" ]; then
-    printf 'EXISTS ('
-      printf 'SELECT 1 FROM `bin_ids`'
-      printf ' WHERE `bin_ids`.`id`=`binary_packages`.`id`'
+    printf ' WHERE `ignore_packages`.`pkgbase`=`pkgbases`.`pkgbase`'
+    printf ' AND `ignore_packages`.`priority`>=`pkgbases`.`priority`'
+  printf ') AND ('
+    printf '`pkgbases`.`priority`!=2'
+    printf ' OR NOT EXISTS ('
+      printf 'SELECT 1 FROM `package_sources`'
+      mysql_join_package_sources_build_assignments
+      mysql_join_build_assignments_binary_packages
+      mysql_join_binary_packages_binary_packages_in_repositories
+      printf ' AND NOT `binary_packages_in_repositories`.`is_to_be_deleted`'
+      mysql_join_binary_packages_in_repositories_repositories
+      printf ' AND ('
+        printf '`repositories`.`is_on_master_mirror`'
+        printf ' OR `repositories`.`name`="build-list"'
+      printf ') WHERE `package_sources`.`pkgbase`=`pkgbases`.`pkgbase`'
     printf ')'
-    printf ' OR '
-  fi
-  printf 'EXISTS ('
-    printf 'SELECT 1 FROM `must_haves`'
-    printf ' WHERE `must_haves`.`pkgname`=`binary_packages`.`pkgname`'
-  printf ')) AND NOT EXISTS ('
-    printf 'SELECT 1 FROM `ignore_packages`'
-    printf ' WHERE `ignore_packages`.`pkgname`=`binary_packages`.`pkgname`'
   printf ');\n'
 } | \
   mysql_run_query | \