From 1b7d5c5699c30a31729d07746a1e0b96a4410e9f Mon Sep 17 00:00:00 2001
From: Erich Eckner <git@eckner.net>
Date: Thu, 4 Jul 2019 14:47:48 +0200
Subject: bin/bootstrap-mysql: accellerate `blacklist_packages` by using a
 temporary table

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

(limited to 'bin/bootstrap-mysql')

diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql
index 54d9da6..967a65d 100755
--- a/bin/bootstrap-mysql
+++ b/bin/bootstrap-mysql
@@ -691,6 +691,14 @@ fi
   printf ' JOIN `architectures`'
   printf ' ON `architectures`.`name`=`blacklist`.`arch`;\n'
 
+  printf 'CREATE TEMPORARY TABLE `available_install_targets` ('
+    printf '`architecture` SMALLINT,'
+    printf '`install_target` BIGINT,'
+    printf 'UNIQUE KEY `content`(`architecture`,`install_target`),'
+    printf 'KEY (`architecture`),'
+    printf 'KEY (`install_target`)'
+  printf ');\n'
+
   printf 'REPEAT\n'
 
     printf 'DELETE FROM `bl_copy`;\n'
@@ -721,6 +729,37 @@ fi
     printf ' `bl`.`pkgbase`'
     printf ' FROM `bl`;\n'
 
+    printf 'DELETE FROM `available_install_targets`;\n'
+    printf 'INSERT IGNORE INTO `available_install_targets` (`architecture`,`install_target`)'
+    printf 'SELECT'
+      printf ' `architectures`.`id`,'
+      printf '`install_target_providers`.`install_target`'
+    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 ' OR `architectures`.`id`=%s' \
+        "${architecture_ids__any}"
+    printf ')'
+    printf ' LEFT JOIN `bl_copy`'
+    printf ' ON `bl_copy`.`pkgbase`=`package_sources`.`pkgbase`'
+    printf ' AND ('
+      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 ')'
+    # we're only interested in those which are not (yet) blacklisted
+    printf ' WHERE `bl_copy`.`pkgbase` IS NULL;\n'
+
     printf 'INSERT IGNORE INTO `bl` (`arch`,`pkgbase`)'
     printf ' SELECT'
     printf ' `a_bp`.`architecture`,'
@@ -744,40 +783,11 @@ fi
       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 ' OR `architectures`.`id`=%s' \
-          "${architecture_ids__any}"
-      printf ')'
-      printf ' LEFT JOIN `bl_copy`'
-      printf ' ON `bl_copy`.`pkgbase`=`package_sources`.`pkgbase`'
-      printf ' AND ('
-        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 ')'
-      # 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`'
+    printf ' LEFT JOIN `available_install_targets`' # list of not-blacklisted install targets
+    printf ' ON `available_install_targets`.`install_target`=`dependencies`.`depending_on`'
+    printf ' AND `available_install_targets`.`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 ' WHERE `available_install_targets`.`install_target` IS NULL'
     printf ';\n'
 
   printf 'UNTIL ROW_COUNT()=0\n'
@@ -791,9 +801,10 @@ fi
   printf ' ON `architectures`.`id`=`bl`.`arch`'
   printf ';\n'
 
-  printf 'DROP TEMPORARY TABLE `bl%s`;\n' \
-    '' \
-    '_copy'
+  printf 'DROP TEMPORARY TABLE `%s`;\n' \
+    'bl' \
+    'bl_copy' \
+    'available_install_targets'
 
   printf 'END\n'
   printf '//\n'
-- 
cgit v1.2.3