From ba5f2280a29cccb04454eb0d467f604b37fbb899 Mon Sep 17 00:00:00 2001
From: Erich Eckner <git@eckner.net>
Date: Tue, 2 Jul 2019 14:55:07 +0200
Subject: bin/bootstrap-mysql: restructure blacklist_packages to use less/no
 "EXISTS" queries

---
 bin/bootstrap-mysql | 68 +++++++++++++++++++++++++----------------------------
 1 file changed, 32 insertions(+), 36 deletions(-)

(limited to 'bin/bootstrap-mysql')

diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql
index 63d02d8..820d689 100755
--- a/bin/bootstrap-mysql
+++ b/bin/bootstrap-mysql
@@ -669,7 +669,6 @@ fi
   printf 'END\n'
   printf '//\n'
 
-# TODO: too slow!
   printf 'DROP PROCEDURE IF EXISTS `blacklist_packages`//\n'
   printf 'CREATE PROCEDURE `blacklist_packages`()\n'
   printf 'blacklist_packages:BEGIN\n'
@@ -734,53 +733,50 @@ fi
     printf ' OR `dependency_types`.`relevant_for_binary_packages`)'
     # 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? -> add exceptions!
-    printf ' WHERE EXISTS ('
-      printf 'SELECT 1'
+    mysql_join_dependencies_install_target_providers '' 'itp_dummy'
+    mysql_join_install_target_providers_binary_packages 'itp_dummy' 'itp_bp_dummy'
+    printf ' AND ('
+      printf '`itp_bp_dummy`.`architecture`=`a_ba`.`architecture`'
+      printf ' OR `a_ba`.`architecture`=%s' \
+        "${architecture_ids__any}"
+      printf ' OR `itp_bp_dummy`.`architecture`=%s' \
+        "${architecture_ids__any}"
+    printf ')'
+    printf ' LEFT JOIN ('
+      printf 'SELECT DISTINCT'
+        printf ' `install_target_providers`.`install_target`,'
+        printf '`architectures`.`id` AS `architecture`'
       printf ' FROM `install_target_providers`'
       mysql_join_install_target_providers_binary_packages
+      mysql_join_binary_packages_build_assignments
+      mysql_join_build_assignments_package_sources
       printf ' JOIN `architectures`'
       printf ' ON ('
         printf '`architectures`.`id`=`binary_packages`.`architecture`'
         printf ' OR `binary_packages`.`architecture`=%s' \
           "${architecture_ids__any}"
-      printf ')'
-      printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
-      printf ' AND ('
-        printf '`architectures`.`id`=`a_ba`.`architecture`'
-        printf ' OR `a_ba`.`architecture`=%s' \
+        printf ' OR `architectures`.`id`=%s' \
           "${architecture_ids__any}"
       printf ')'
-    printf ') AND NOT EXISTS ('
-      printf 'SELECT 1'
-      printf ' FROM `install_target_providers`'
-      mysql_join_install_target_providers_binary_packages
-      printf ' JOIN `architectures`'
-      printf ' ON ('
-        printf '`architectures`.`id`=`binary_packages`.`architecture`'
-        printf ' OR `binary_packages`.`architecture`=%s' \
-          "${architecture_ids__any}"
-      printf ')'
-      printf ' WHERE NOT EXISTS ('
-        printf 'SELECT 1'
-        printf ' FROM `bl_copy`'
-        printf ' JOIN `package_sources` AS `b_ps`'
-        printf ' ON `bl_copy`.`pkgbase`=`b_ps`.`pkgbase`'
-        mysql_join_package_sources_build_assignments 'b_ps' 'b_ba'
-        printf ' AND ('
-          printf '`b_ba`.`architecture`=`bl_copy`.`arch`'
-          printf ' OR `bl_copy`.`arch`=%s' \
-            "${architecture_ids__any}"
-        printf ')'
-        mysql_join_build_assignments_binary_packages 'b_ba' 'b_bp'
-        printf ' WHERE `install_target_providers`.`package`=`b_bp`.`id`'
-      printf ')'
-      printf ' AND `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
+      printf ' LEFT JOIN `bl_copy`'
+      printf ' ON `bl_copy`.`pkgbase`=`package_sources`.`pkgbase`'
       printf ' AND ('
-        printf '`architectures`.`id`=`a_ba`.`architecture`'
-        printf ' OR `a_ba`.`architecture`=%s' \
+        printf '`bl_copy`.`arch`=`build_assignments`.`architecture`'
+        printf ' OR `bl_copy`.`arch`=%s' \
           "${architecture_ids__any}"
+        # we intentionally do not allow arch-specific packages to cause
+        # "any"-packages to be blacklisted (this is seldom and makes the
+        # query quite complex, because we must check if /all/
+        # arch-specific packages are unavailable)
       printf ')'
-    printf ');\n'
+      # we're only interested in those which are not (yet) blacklisted
+      printf ' WHERE `bl_copy`.`pkgbase` IS NULL'
+    printf ') AS `itp_query`' # list of not-blacklisted install targets
+    printf ' ON `itp_query`.`install_target`=`dependencies`.`depending_on`'
+    printf ' AND `itp_query`.`architecture`=`a_ba`.`architecture`'
+    # we only add those to the blacklist, that have not install_target_provider
+    printf ' WHERE `itp_query`.`install_target` IS NULL'
+    printf ';\n'
 
   printf 'UNTIL ROW_COUNT()=0\n'
   printf 'END REPEAT;\n'
-- 
cgit v1.2.3