bin/build-master-status: accellerate query by using "GROUP BY"

1 files changed, 128 insertions, 157 deletions

diff --git a/bin/build-master-status b/bin/build-master-status
index c38d491..16d6c5a 100755
--- a/bin/build-master-status
+++ b/bin/build-master-status
@@ -96,170 +96,141 @@ if [ -s "${tmp_dir}/todos" ]; then
 fi
 rm -f "${tmp_dir}/todos"
 
-# TODO: accellerate this query by using "GROUP BY"?
 if [ ! -s "${work_dir}/build-master-sanity" ]; then
-  now=$(
-    date '+%F %H:%M:%S' | \
-      base64 -w0
-  )
   # shellcheck disable=SC2016
   {
-    printf 'SELECT `architectures`.`id`'
-    printf ' FROM `architectures`;\n'
-  } | \
-    mysql_run_query 'unimportant' | \
-    while read -r arch_id; do
-      printf 'INSERT IGNORE INTO `statistics` ('
-      printf '`%s`,' \
-        'stable_packages_count' \
-        'pending_tasks_count' \
-        'pending_packages_count' \
-        'staging_packages_count' \
-        'testing_packages_count' \
-        'tested_packages_count' \
-        'broken_tasks_count' \
-        'dependency_loops_count' \
-        'dependency_looped_tasks_count' \
-        'locked_tasks_count' \
-        'blocked_tasks_count' \
-        'next_tasks_count' \
-        'architecture' \
-        'date' | \
-        sed 's/,$//'
-      printf ') VALUES ('
-      # stable_packages_count
-      printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
-      printf ' `binary_packages`'
-      mysql_join_binary_packages_binary_packages_in_repositories
-      mysql_join_binary_packages_in_repositories_repositories
-      # shellcheck disable=SC2154
-      printf ' WHERE `repositories`.`stability`=%s' \
-        "${repository_stability_ids__stable}"
-      printf ' AND `repositories`.`architecture`=%s),' \
-        "${arch_id}"
-      # pending_tasks_count
-      printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM'
-      printf ' `build_assignments`'
+    printf 'INSERT IGNORE INTO `statistics` ('
+    printf '`%s`,' \
+      'pending_tasks_count' \
+      'pending_packages_count' \
+      'broken_tasks_count' \
+      'dependency_loops_count' \
+      'dependency_looped_tasks_count' \
+      'locked_tasks_count' \
+      'blocked_tasks_count' \
+      'next_tasks_count' \
+      'stable_packages_count' \
+      'staging_packages_count' \
+      'testing_packages_count' \
+      'tested_packages_count' \
+      'architecture' | \
+      sed 's/,$//'
+    printf ') SELECT '
+    printf '`ba_q`.`%s`,' \
+      'pending_tasks_count' \
+      'pending_packages_count' \
+      'broken_tasks_count' \
+      'dependency_loops_count' \
+      'dependency_looped_tasks_count' \
+      'locked_tasks_count' \
+      'blocked_tasks_count' \
+      'next_tasks_count'
+    printf '`bp_q`.`%s`,' \
+      'stable_packages_count' \
+      'staging_packages_count' \
+      'testing_packages_count' \
+      'tested_packages_count'
+    printf '`architectures`.`id`'
+    printf ' FROM `architectures`'
+    printf ' JOIN ('
+      printf 'SELECT'
+      printf ' COUNT('
+        printf 'DISTINCT `build_assignments`.`id`'
+      printf ') AS `pending_tasks_count`,'
+      printf ' COUNT('
+        printf 'DISTINCT `binary_packages`.`id`'
+      printf ') AS `pending_packages_count`,'
+      printf ' COUNT('
+        printf 'DISTINCT IF('
+          printf '`build_assignments`.`is_broken`,'
+          printf '`build_assignments`.`id`,'
+          printf 'NULL'
+        printf ')'
+      printf ') AS `broken_tasks_count`,'
+      printf ' COUNT('
+        printf 'DISTINCT `build_dependency_loops`.`loop`'
+      printf ') AS `dependency_loops_count`,'
+      printf ' COUNT('
+        printf 'DISTINCT `build_dependency_loops`.`build_assignment`'
+      printf ') AS `dependency_looped_tasks_count`,'
+      printf ' COUNT('
+        printf 'DISTINCT `build_slaves`.`currently_building`'
+      printf ') AS `locked_tasks_count`,'
+      printf ' COUNT('
+        printf 'DISTINCT IF('
+          printf '`build_assignments`.`is_blocked` IS NULL,'
+          printf 'NULL,'
+          printf '`build_assignments`.`id`'
+        printf ')'
+      printf ') AS `blocked_tasks_count`,'
+      printf ' COUNT('
+        printf 'DISTINCT IF('
+          mysql_query_has_pending_dependencies \
+            "$(
+              # shellcheck disable=SC2154
+              printf 'IF(`build_assignments`.`architecture`=%s,%s,`build_assignments`.`architecture`)' \
+                "${architecture_ids__any}" \
+                "${architecture_ids__i686}"
+            )" \
+            '`build_assignments`.`id`'
+          printf ','
+          printf '`build_assignments`.`id`,'
+          printf 'NULL'
+        printf ')'
+      printf ') AS `next_tasks_count`,'
+      printf ' `build_assignments`.`architecture`'
+      printf ' FROM `build_assignments`'
       mysql_join_build_assignments_binary_packages
       mysql_join_binary_packages_binary_packages_in_repositories
-      printf ' WHERE `binary_packages_in_repositories`.`repository`=%s' \
-        "${repository_ids__any_build_list}"
-      printf ' AND `build_assignments`.`architecture`=%s),' \
-        "${arch_id}"
-      # pending_packages_count
-      printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
-      printf ' `binary_packages`'
-      mysql_join_binary_packages_binary_packages_in_repositories
-      mysql_join_binary_packages_build_assignments
-      printf ' WHERE `binary_packages_in_repositories`.`repository`=%s' \
-        "${repository_ids__any_build_list}"
-      printf ' AND `build_assignments`.`architecture`=%s),' \
-        "${arch_id}"
-      # staging_packages_count
-      printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
-      printf ' `binary_packages`'
-      mysql_join_binary_packages_binary_packages_in_repositories
-      mysql_join_binary_packages_in_repositories_repositories
-      # shellcheck disable=SC2154
-      printf ' WHERE `repositories`.`stability`=%s' \
-        "${repository_stability_ids__staging}"
-      printf ' AND `repositories`.`architecture`=%s),' \
-        "${arch_id}"
-      # testing_packages_count
-      printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
-      printf ' `binary_packages`'
-      mysql_join_binary_packages_binary_packages_in_repositories
       mysql_join_binary_packages_in_repositories_repositories
-      # shellcheck disable=SC2154
-      printf ' WHERE `repositories`.`stability`=%s' \
-        "${repository_stability_ids__testing}"
-      printf ' AND NOT `binary_packages`.`is_tested`'
-      printf ' AND `repositories`.`architecture`=%s),' \
-        "${arch_id}"
-      # tested_packages_count
-      printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM'
-      printf ' `binary_packages`'
+      printf ' LEFT'
+      mysql_join_build_assignments_build_slaves
+      printf ' LEFT'
+      mysql_join_build_assignments_build_dependency_loops
+    printf ') AS `ba_q`'
+    printf ' ON `ba_q`.`architecture`=`architectures`.`id`'
+    printf ' JOIN ('
+      printf 'SELECT'
+      printf ' SUM('
+        printf 'IF('
+          # shellcheck disable=SC2154
+          printf '`repositories`.`stability`=%s,' \
+            "${repository_stability_ids__stable}"
+          printf '1,0'
+        printf ')'
+      printf ') AS `stable_packages_count`,'
+      printf ' SUM('
+        printf 'IF('
+          # shellcheck disable=SC2154
+          printf '`repositories`.`stability`=%s,' \
+            "${repository_stability_ids__staging}"
+          printf '1,0'
+        printf ')'
+      printf ') AS `staging_packages_count`,'
+      printf ' SUM('
+        printf 'IF('
+          # shellcheck disable=SC2154
+          printf '`repositories`.`stability`=%s' \
+            "${repository_stability_ids__testing}"
+          printf ' AND NOT `binary_packages`.`is_tested`,'
+          printf '1,0'
+        printf ')'
+      printf ') AS `testing_packages_count`,'
+      printf ' SUM('
+        printf 'IF('
+          printf '`repositories`.`stability`=%s' \
+            "${repository_stability_ids__testing}"
+          printf ' AND `binary_packages`.`is_tested`,'
+          printf '1,0'
+        printf ')'
+      printf ') AS `tested_packages_count`,'
+      printf ' `repositories`.`architecture`'
+      printf ' FROM `binary_packages`'
       mysql_join_binary_packages_binary_packages_in_repositories
       mysql_join_binary_packages_in_repositories_repositories
-      printf ' WHERE `repositories`.`stability`=%s' \
-        "${repository_stability_ids__testing}"
-      printf ' AND `binary_packages`.`is_tested`'
-      printf ' AND `repositories`.`architecture`=%s),' \
-        "${arch_id}"
-      # broken_tasks_count
-      printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM'
-      printf ' `build_assignments`'
-      mysql_join_build_assignments_binary_packages
-      mysql_join_binary_packages_binary_packages_in_repositories
-      printf ' WHERE `binary_packages_in_repositories`.`repository`=%s' \
-        "${repository_ids__any_build_list}"
-      printf ' AND `build_assignments`.`is_broken`'
-      printf ' AND `build_assignments`.`architecture`=%s),' \
-        "${arch_id}"
-      # dependency_loops_count
-      printf '(SELECT COUNT(DISTINCT `build_dependency_loops`.`loop`) FROM'
-      printf ' `build_dependency_loops`'
-      mysql_join_build_dependency_loops_build_assignments
-      printf ' WHERE `build_assignments`.`architecture`=%s),' \
-        "${arch_id}"
-      # dependency_looped_tasks_count
-      printf '(SELECT COUNT(DISTINCT `build_dependency_loops`.`build_assignment`) FROM'
-      printf ' `build_dependency_loops`'
-      mysql_join_build_dependency_loops_build_assignments
-      printf ' WHERE `build_assignments`.`architecture`=%s),' \
-        "${arch_id}"
-      # locked_tasks_count
-      printf '(SELECT COUNT(DISTINCT `build_slaves`.`currently_building`) FROM'
-      printf ' `build_slaves`'
-      mysql_join_build_slaves_build_assignments
-      mysql_join_build_assignments_binary_packages
-      mysql_join_binary_packages_binary_packages_in_repositories
-      printf ' WHERE `binary_packages_in_repositories`.`repository`=%s' \
-        "${repository_ids__any_build_list}"
-      printf ' AND `build_assignments`.`architecture`=%s),' \
-        "${arch_id}"
-      # blocked_tasks_count
-      printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM'
-      printf ' `build_assignments`'
-      mysql_join_build_assignments_binary_packages
-      mysql_join_binary_packages_binary_packages_in_repositories
-      printf ' WHERE `binary_packages_in_repositories`.`repository`=%s' \
-        "${repository_ids__any_build_list}"
-      printf ' AND `build_assignments`.`is_blocked` IS NOT NULL'
-      printf ' AND `build_assignments`.`architecture`=%s),' \
-        "${arch_id}"
-      # next_tasks_count
-      printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM'
-      printf ' `build_assignments`'
-      mysql_join_build_assignments_binary_packages
-      mysql_join_binary_packages_binary_packages_in_repositories
-      printf ' WHERE `binary_packages_in_repositories`.`repository`=%s' \
-        "${repository_ids__any_build_list}"
-      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_with_versions
-          mysql_join_install_target_providers_binary_packages '' 'prov_bp'
-          mysql_join_binary_packages_binary_packages_in_repositories 'prov_bp' 'prov_bpir'
-          printf ' WHERE `prov_bpir`.`repository`=%s' \
-            "${repository_ids__any_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 ' AND `build_assignments`.`architecture`=%s),' \
-        "${arch_id}"
-      # architecture
-      printf '%s,' \
-        "${arch_id}"
-      # date
-      printf 'from_base64("%s")' \
-        "${now}"
-      printf ');\n'
-    done | \
+      printf ' GROUP BY `repositories`.`architecture`'
+    printf ') AS `bp_q`'
+    printf ' ON `bp_q`.`architecture`=`architectures`.`id`;\n'
+  } | \
     mysql_run_query 'unimportant'
 fi