From a3b1986a2e0f76a95435fcaa4e8ce172ae8e33e4 Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Thu, 13 Sep 2018 09:48:11 +0200 Subject: bin/get-assignment: use temporary table instead of sub-query --- bin/get-assignment | 268 +++++++++++++++++++++++++++-------------------------- 1 file changed, 138 insertions(+), 130 deletions(-) (limited to 'bin/get-assignment') 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 ) -- cgit v1.2.3