bin/get-assignment: use temporary table instead of sub-query

1 files changed, 138 insertions, 130 deletions

diff --git a/bin/get-assignment b/bin/get-assignment
index 4e9b040..8cf4a52 100755
--- a/bin/get-assignment
+++ b/bin/get-assignment
@@ -160,142 +160,150 @@ next_building=$(
   # shellcheck disable=SC2016
   {
     mysql_query_create_toolchain_order
-    printf 'SELECT `q`.`ba_id` FROM'
-    printf '('
-      printf 'SELECT '
+
+    printf 'CREATE TEMPORARY TABLE `considered_build_assignments` ('
+      printf '`id` BIGINT,'
+      printf ' UNIQUE KEY `id`(`id`)'
+    printf ');\n'
+
+    printf 'INSERT IGNORE INTO `considered_build_assignments` (`id`)'
+    printf ' SELECT `build_assignments`.`id`'
+    printf ' FROM `build_assignments`'
+    mysql_join_build_assignments_package_sources
+    mysql_join_build_assignments_binary_packages
+    mysql_join_binary_packages_binary_packages_in_repositories
+    printf ' JOIN `architecture_compatibilities`'
+    printf ' ON `architecture_compatibilities`.`built_for`=`build_assignments`.`architecture`'
+    printf ' AND `architecture_compatibilities`.`fully_compatible`'
+    printf ' JOIN `architectures`'
+    printf ' ON `architecture_compatibilities`.`runs_on`=`architectures`.`id`'
+    printf ' WHERE 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'
       if [ -n "${requested}" ]; then
-        printf '`package_sources`.`pkgbase`=from_base64("%s") AS `requested`,' \
+        printf ' OR'
+        printf ' `package_sources`.`pkgbase`=from_base64("%s")' \
           "${requested}"
-      else
-        printf '0 AS `requested`,'
       fi
-      printf 'IFNULL(`toolchain_query`.`priority`,3) AS `toolchain_priority`,'
-      # 0: in toolchain, never blocked
-      # 1: in toolchain, unblocked exists, not-yet-built
-      # 2: in toolchain, unblocked exists, already built
-      # (should normally become "0", before being handed out)
-      # 3: not in toolchain or only blocked in toolchain
-      printf '`build_assignments`.`priority`,'
-      printf 'COALESCE('
-      printf 'MAX(`failed_builds`.`date`),0'
-      printf ') AS `last_trial`,'
+    printf ') AND ('
       mysql_query_is_part_of_loop '`build_assignments`.`id`'
-      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_binary_packages_in_repositories
-      printf ' JOIN `architecture_compatibilities`'
-      printf ' ON `architecture_compatibilities`.`built_for`=`build_assignments`.`architecture`'
-      printf ' AND `architecture_compatibilities`.`fully_compatible`'
-      printf ' JOIN `architectures`'
-      printf ' ON `architecture_compatibilities`.`runs_on`=`architectures`.`id`'
-      printf ' LEFT'
-      mysql_join_build_assignments_failed_builds
-      printf ' LEFT JOIN ('
-        # Does this job wait for something else in `toolchain_order`?
-        printf 'SELECT '
-        printf '`tc_q`.`pkgbase`,'
-        printf '`tc_q`.`architecture`,'
+      printf ' OR NOT '
+      mysql_query_has_pending_dependencies '`architectures`.`name`' '`build_assignments`.`id`'
+    printf ')'
+    printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
+      "${repository_ids__any_build_list}"
+    printf ' AND `architectures`.`name` = from_base64("%s");\n' \
+      "${arch}"
+
+    printf 'SELECT `considered_build_assignments`.`id`,package_sources.pkgbase FROM'
+    printf ' `considered_build_assignments`'
+    printf ' JOIN `build_assignments`'
+    printf ' ON `build_assignments`.`id`=`considered_build_assignments`.`id`'
+    mysql_join_build_assignments_package_sources
+    mysql_join_build_assignments_binary_packages
+    printf ' LEFT JOIN ('
+      printf 'SELECT'
+      printf ' `failed_builds`.`build_assignment`,'
+      printf 'MAX(`failed_builds`.`date`) AS `date`'
+      printf ' FROM `failed_builds`'
+      printf ' GROUP BY `failed_builds`.`build_assignment`'
+    printf ') AS `latest_failed_builds`'
+    printf ' ON `latest_failed_builds`.`build_assignment`=`build_assignments`.`id`'
+    printf ' LEFT JOIN ('
+      # Does this job wait for something else in `toolchain_order`?
+      printf 'SELECT '
+      printf '`tc_q`.`pkgbase`,'
+      printf '`tc_q`.`architecture`,'
+      printf 'IF('
+        printf '`tc_q`.`is_blocked`,'
         printf 'IF('
-          printf '`tc_q`.`is_blocked`,'
+          printf '`tc_q`.`is_built`,'
+          printf '2,'
+          printf '1'
+        printf '),'
+        printf '0'
+      printf ') AS `priority`'
+      printf ' FROM ('
+        printf 'SELECT '
+        printf '`late`.`pkgbase`,'
+        printf '`binary_packages`.`architecture`,'
+        printf 'MAX('
           printf 'IF('
-            printf '`tc_q`.`is_built`,'
-            printf '2,'
-            printf '1'
-          printf '),'
-          printf '0'
-        printf ') AS `priority`'
-        printf ' FROM ('
-          printf 'SELECT '
-          printf '`late`.`pkgbase`,'
-          printf '`binary_packages`.`architecture`,'
-          printf 'MAX('
-            printf 'IF('
-              printf 'EXISTS ('
-                printf 'SELECT 1'
-                printf ' FROM `binary_packages` AS `built_bp`'
-                mysql_join_binary_packages_binary_packages_in_repositories 'built_bp' 'built_bpir'
-                mysql_join_binary_packages_in_repositories_repositories 'built_bpir' 'built_r'
-                printf ' AND `built_r`.`is_on_master_mirror`'
-                printf ' WHERE `built_bp`.`build_assignment`=`build_assignments`.`id`'
-              printf '),'
-              printf '1,'
-              printf '0'
-            printf ')'
-          printf ') AS `is_built`,'
-          printf 'MAX('
-            printf 'IF('
-              printf 'EXISTS ('
-                printf 'SELECT 1'
-                printf ' FROM `package_sources` AS `block_ps`'
-                mysql_join_package_sources_build_assignments 'block_ps' 'block_ba'
-                mysql_join_build_assignments_binary_packages 'block_ba' 'block_bp'
-                mysql_join_binary_packages_binary_packages_in_repositories 'block_bp' 'block_bpir'
-                printf ' AND `block_bpir`.`repository`=%s' \
-                  "${repository_ids__any_build_list}"
-                printf ' JOIN `toolchain_order_copy` AS `early`'
-                printf ' ON `early`.`pkgbase`=`block_ps`.`pkgbase`'
-                printf ' WHERE `early`.`number`<`late`.`number`'
-                printf ' AND `early`.`pkgbase`!=`late`.`pkgbase`'
-                printf ' AND ('
-                  printf '`block_bp`.`architecture`=`binary_packages`.`architecture`'
-                  # shellcheck disable=SC2154
-                  printf ' OR `%s`.`architecture`=%s' \
-                    'block_bp' "${architecture_ids__any}" \
-                    'binary_packages' "${architecture_ids__any}"
-                printf ')'
-              printf '),'
-              printf '1,'
-              printf '0'
-            printf ')'
-          printf ') AS `is_blocked`'
-          printf ' FROM `toolchain_order` AS `late`'
-          printf ' JOIN `package_sources`'
-          printf ' ON `package_sources`.`pkgbase`=`late`.`pkgbase`'
-          mysql_join_package_sources_build_assignments
-          mysql_join_build_assignments_binary_packages
-          mysql_join_binary_packages_binary_packages_in_repositories
-          printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
-            "${repository_ids__any_build_list}"
-          printf ' GROUP BY CONCAT(`late`.`pkgbase`,"-",`binary_packages`.`architecture`)'
-        printf ') AS `tc_q`'
-      printf ') AS `toolchain_query`'
-      printf ' ON `toolchain_query`.`pkgbase`=`package_sources`.`pkgbase`'
-      printf ' AND `toolchain_query`.`architecture`=`binary_packages`.`architecture`'
-      printf ' WHERE 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'
-        if [ -n "${requested}" ]; then
-          printf ' OR'
-          printf ' `package_sources`.`pkgbase`=from_base64("%s")' \
-            "${requested}"
-        fi
-      printf ') AND ('
-        mysql_query_is_part_of_loop '`build_assignments`.`id`'
-        printf ' OR NOT '
-        mysql_query_has_pending_dependencies '`architectures`.`name`' '`build_assignments`.`id`'
-      printf ')'
-      printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
-        "${repository_ids__any_build_list}"
-      printf ' AND `architectures`.`name` = from_base64("%s")' \
-        "${arch}"
-      printf ' GROUP BY `build_assignments`.`id`'
-      printf ' ORDER BY'
-      printf ' `requested` DESC,'
-      printf ' `toolchain_priority`,'
-      printf ' `priority` DESC,'
-      printf ' `last_trial`,'
-      printf ' `architectures`.`id`=`build_assignments`.`architecture` DESC,'
-      printf ' `part_of_loop`,'
-      printf ' `package_sources`.`commit_time`,'
-      printf ' `build_assignments`.`id`'
-      printf ' LIMIT 1'
-    printf ') AS `q`;\n'
+            printf 'EXISTS ('
+              printf 'SELECT 1'
+              printf ' FROM `binary_packages` AS `built_bp`'
+              mysql_join_binary_packages_binary_packages_in_repositories 'built_bp' 'built_bpir'
+              mysql_join_binary_packages_in_repositories_repositories 'built_bpir' 'built_r'
+              printf ' AND `built_r`.`is_on_master_mirror`'
+              printf ' WHERE `built_bp`.`build_assignment`=`build_assignments`.`id`'
+            printf '),'
+            printf '1,'
+            printf '0'
+          printf ')'
+        printf ') AS `is_built`,'
+        printf 'MAX('
+          printf 'IF('
+            printf 'EXISTS ('
+              printf 'SELECT 1'
+              printf ' FROM `package_sources` AS `block_ps`'
+              mysql_join_package_sources_build_assignments 'block_ps' 'block_ba'
+              mysql_join_build_assignments_binary_packages 'block_ba' 'block_bp'
+              mysql_join_binary_packages_binary_packages_in_repositories 'block_bp' 'block_bpir'
+              printf ' AND `block_bpir`.`repository`=%s' \
+                "${repository_ids__any_build_list}"
+              printf ' JOIN `toolchain_order_copy` AS `early`'
+              printf ' ON `early`.`pkgbase`=`block_ps`.`pkgbase`'
+              printf ' WHERE `early`.`number`<`late`.`number`'
+              printf ' AND `early`.`pkgbase`!=`late`.`pkgbase`'
+              printf ' AND ('
+                printf '`block_bp`.`architecture`=`binary_packages`.`architecture`'
+                # shellcheck disable=SC2154
+                printf ' OR `%s`.`architecture`=%s' \
+                  'block_bp' "${architecture_ids__any}" \
+                  'binary_packages' "${architecture_ids__any}"
+              printf ')'
+            printf '),'
+            printf '1,'
+            printf '0'
+          printf ')'
+        printf ') AS `is_blocked`'
+        printf ' FROM `toolchain_order` AS `late`'
+        printf ' JOIN `package_sources`'
+        printf ' ON `package_sources`.`pkgbase`=`late`.`pkgbase`'
+        mysql_join_package_sources_build_assignments
+        mysql_join_build_assignments_binary_packages
+        mysql_join_binary_packages_binary_packages_in_repositories
+        printf ' AND `binary_packages_in_repositories`.`repository`=%s' \
+          "${repository_ids__any_build_list}"
+        printf ' GROUP BY CONCAT(`late`.`pkgbase`,"-",`binary_packages`.`architecture`)'
+      printf ') AS `tc_q`'
+    printf ') AS `toolchain_query`'
+    printf ' ON `toolchain_query`.`pkgbase`=`package_sources`.`pkgbase`'
+    printf ' AND `toolchain_query`.`architecture`=`binary_packages`.`architecture`'
+    printf ' JOIN `architectures`'
+    printf ' ON `architectures`.`name`=from_base64("%s")' \
+      "${arch}"
+    printf ' ORDER BY '
+    if [ -n "${requested}" ]; then
+      printf 'IF(`package_sources`.`pkgbase`=from_base64("%s"),1,0) DESC,' \
+        "${requested}"
+    fi
+    # 0: in toolchain, should be built now
+    # 1: in toolchain, should be built later
+    # 2: not in toolchain
+    printf 'IFNULL(`toolchain_query`.`priority`,2),'
+    printf '`build_assignments`.`priority` DESC,'
+    printf 'IFNULL(`latest_failed_builds`.`date`,0),'
+    printf 'IF(`architectures`.`id`=`build_assignments`.`architecture`,1,0) DESC,'
+    printf 'IF('
+      mysql_query_is_part_of_loop '`build_assignments`.`id`'
+    printf ',1,0),'
+    printf '`package_sources`.`commit_time`,'
+    printf '`build_assignments`.`id`'
+    printf ' LIMIT 1;\n'
   } | \
     mysql_run_query
 )