From e0c167a65916bf995add39aec94f5f524af834e4 Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Fri, 1 Jun 2018 11:06:41 +0200 Subject: make `binary_packages`.`is_to_be_deleted` and `binary_packages`.`last_moved` obsolete; cleanup some queries --- bin/build-master-status | 6 +++--- bin/db-update | 3 +-- bin/delete-packages | 10 +++++----- bin/find-obsolete-packages | 2 +- bin/seed-build-list | 13 +++++++------ lib/mysql-functions | 9 ++++----- 6 files changed, 21 insertions(+), 22 deletions(-) diff --git a/bin/build-master-status b/bin/build-master-status index f1f4ed9..9636722 100755 --- a/bin/build-master-status +++ b/bin/build-master-status @@ -79,7 +79,7 @@ if [ -s "${tmp_dir}/todos" ]; then printf ' WHERE `td`.`id` IS NULL;\n' printf 'DELETE FROM `todos` WHERE NOT EXISTS (' - printf 'SELECT * FROM `td`' + printf 'SELECT 1 FROM `td`' printf ' AND `td`.`%s`=`todos`.`%s`' \ 'file' 'file' \ 'line' 'line' \ @@ -88,10 +88,10 @@ if [ -s "${tmp_dir}/todos" ]; then printf ');\n' printf 'DROP TEMPORARY TABLE `td`;\n' printf 'DELETE FROM `todo_links` WHERE NOT EXISTS (' - printf 'SELECT * FROM `todos` ' + printf 'SELECT 1 FROM `todos` ' printf 'WHERE `todos`.`id`=`todo_links`.`depending_on`' printf ') OR NOT EXISTS (' - printf 'SELECT * FROM `todos` ' + printf 'SELECT 1 FROM `todos` ' printf 'WHERE `todos`.`id`=`todo_links`.`dependent`' printf ');\n' } | \ diff --git a/bin/db-update b/bin/db-update index 4834a1d..2140880 100755 --- a/bin/db-update +++ b/bin/db-update @@ -201,7 +201,7 @@ for source_stability in 'testing' 'staging'; do mysql_join_dependencies_dependency_types printf ' AND `dependency_types`.`relevant_for_binary_packages`' printf ' WHERE NOT EXISTS (' - printf 'SELECT * FROM `install_target_providers`' + printf 'SELECT 1 FROM `install_target_providers`' printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`' printf ');\n' @@ -411,7 +411,6 @@ for source_stability in 'testing' 'staging'; do printf ' JOIN `moved_binary_packages` ON `binary_packages`.`id`=`moved_binary_packages`.`id`' mysql_join_binary_packages_binary_packages_in_repositories printf ' SET `binary_packages_in_repositories`.`repository`=`moved_binary_packages`.`new_repository`,' - printf '`binary_packages`.`last_moved`=NOW(),' printf '`binary_packages_in_repositories`.`last_moved`=NOW()' printf ' WHERE `binary_packages_in_repositories`.`repository`!=`moved_binary_packages`.`new_repository`;\n' } | \ diff --git a/bin/delete-packages b/bin/delete-packages index 8ec7730..75fdc8e 100755 --- a/bin/delete-packages +++ b/bin/delete-packages @@ -113,7 +113,7 @@ export TMPDIR="${tmp_dir}" mysql_join_binary_packages_in_repositories_repositories printf ' LEFT' # should not be necessary, but is formally more correct mysql_join_binary_packages_install_target_providers - printf ' WHERE `binary_packages`.`is_to_be_deleted`' + printf ' WHERE `binary_packages_in_repositories`.`is_to_be_deleted`' printf ' AND `repositories`.`is_on_master_mirror`' printf ' AND NOT EXISTS (' # no packages depending on that one exist @@ -121,9 +121,9 @@ export TMPDIR="${tmp_dir}" mysql_join_dependencies_dependency_types printf ' AND `dependency_types`.`relevant_for_binary_packages`' mysql_join_dependencies_binary_packages '' 'd_bp' - # deliberately break dependencies of deletion-list packages - printf ' AND NOT `d_bp`.`is_to_be_deleted`' mysql_join_binary_packages_binary_packages_in_repositories 'd_bp' 'd_bpir' + # deliberately break dependencies of deletion-list packages + printf ' AND NOT `d_bpir`.`is_to_be_deleted`' mysql_join_binary_packages_in_repositories_repositories 'd_bpir' 'd_r' mysql_join_repositories_repository_stabilities 'd_r' 'd_rs' # this is deliberately less restrict than `d_r`.`is_on_master_mirror` @@ -131,9 +131,9 @@ export TMPDIR="${tmp_dir}" printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`' printf ' AND NOT EXISTS (' printf 'SELECT 1 FROM `binary_packages` AS `s_bp`' - mysql_join_binary_packages_install_target_providers 's_bp' 's_itp' - printf ' AND NOT `s_bp`.`is_to_be_deleted`' mysql_join_binary_packages_binary_packages_in_repositories 's_bp' 's_bpir' + printf ' AND NOT `s_bpir`.`is_to_be_deleted`' + mysql_join_binary_packages_install_target_providers 's_bp' 's_itp' mysql_join_binary_packages_in_repositories_repositories 's_bpir' 's_r' printf ' AND `s_r`.`is_on_master_mirror`' printf ' JOIN `repository_stability_relations`' diff --git a/bin/find-obsolete-packages b/bin/find-obsolete-packages index c6db3ce..db9f818 100755 --- a/bin/find-obsolete-packages +++ b/bin/find-obsolete-packages @@ -109,7 +109,7 @@ fi mysql_join_binary_packages_binary_packages_in_repositories mysql_join_binary_packages_in_repositories_repositories printf ' WHERE `repositories`.`is_on_master_mirror`' - printf ' AND NOT `binary_packages`.`is_to_be_deleted`' + printf ' AND NOT `binary_packages_in_repositories`.`is_to_be_deleted`' } | \ mysql_run_query } | \ diff --git a/bin/seed-build-list b/bin/seed-build-list index ce4239c..8bdc167 100755 --- a/bin/seed-build-list +++ b/bin/seed-build-list @@ -211,10 +211,11 @@ fi printf ' WHERE `repositories`.`name` IN ("build-list","deletion-list")' # packages with no not-to-be-deleted and at least on to-be-deleted version should be ignored printf ' OR (' - printf '`ignore_bin`.`is_to_be_deleted`' + printf '`binary_packages_in_repositories`.`is_to_be_deleted`' printf ' AND NOT EXISTS (' - printf 'SELECT * FROM `binary_packages` AS `other_bin`' - printf ' WHERE NOT `other_bin`.`is_to_be_deleted`' + printf 'SELECT 1 FROM `binary_packages` AS `other_bin`' + mysql_join_binary_packages_binary_packages_in_repositories 'other_bin' 'other_bpir' + printf ' WHERE NOT `other_bpir`.`is_to_be_deleted`' printf ' AND `other_bin`.`pkgname`=`ignore_bin`.`pkgname`' printf '));\n' } | \ @@ -245,10 +246,10 @@ printf 'CREATE TEMPORARY TABLE `pkgbases` (`pkgbase` VARCHAR(64), `repository` V cat "${tmp_dir}/must-haves" "${tmp_dir}/ignore-packages" printf 'SELECT `must_haves`.`pkgname` FROM `must_haves`' printf ' WHERE NOT EXISTS (' - printf 'SELECT * FROM `binary_packages`' + printf 'SELECT 1 FROM `binary_packages`' printf ' WHERE `binary_packages`.`pkgname`=`must_haves`.`pkgname`' printf ') AND NOT EXISTS (' - printf 'SELECT * FROM `ignore_packages`' + printf 'SELECT 1 FROM `ignore_packages`' printf ' WHERE `ignore_packages`.`pkgname`=`must_haves`.`pkgname`' printf ') AND NOT `must_haves`.`pkgname` LIKE "lib32-%%";\n' } | \ @@ -318,7 +319,7 @@ printf 'CREATE TEMPORARY TABLE `pkgbases` (`pkgbase` VARCHAR(64), `repository` V printf ' AND `subst_bp`.`id`!=`binary_packages`.`id`' printf ' AND `subst_bp`.`pkgname`=`binary_packages`.`pkgname`' printf ') AND NOT EXISTS (' - printf 'SELECT * FROM `install_target_providers`' + printf 'SELECT 1 FROM `install_target_providers`' mysql_join_install_target_providers_binary_packages '' 'subst_bp' mysql_join_binary_packages_binary_packages_in_repositories 'subst_bp' 'subst_bir' mysql_join_binary_packages_in_repositories_repositories 'subst_bir' 'subst_r' diff --git a/lib/mysql-functions b/lib/mysql-functions index 8dc37b1..cb76dac 100755 --- a/lib/mysql-functions +++ b/lib/mysql-functions @@ -455,8 +455,7 @@ mysql_generate_package_metadata() { 'pkgname' \ 'sub_pkgrel' \ 'has_issues' \ - 'is_tested' \ - 'is_to_be_deleted' + 'is_tested' printf ') SELECT ' printf '%s,' \ "${build_assignment_id}" @@ -467,7 +466,7 @@ mysql_generate_package_metadata() { "${pkgver}" \ "${pkgrel}" \ "${pkgname}" - printf '%s,0,0,0 FROM `architectures`' \ + printf '%s,0,0 FROM `architectures`' \ "${sub_pkgrel}" printf ' WHERE' printf ' `architectures`.`name` = from_base64("%s")' \ @@ -852,7 +851,7 @@ mysql_cleanup() { "${operator}" printf 'WHERE NOT EXISTS ' printf '(' - printf 'SELECT * FROM `binary_packages` ' + printf 'SELECT 1 FROM `binary_packages` ' printf 'WHERE `binary_packages`.`build_assignment`=`build_assignments`.`id`' printf ');\n' # remove failed_builds with unbroken build_assignments @@ -940,7 +939,7 @@ mysql_query_has_pending_dependencies() { # print a mysql query giving wether the package is part of a loop mysql_query_is_part_of_loop() { printf 'EXISTS (' - printf 'SELECT * FROM `build_dependency_loops`' + printf 'SELECT 1 FROM `build_dependency_loops`' printf ' WHERE `build_dependency_loops`.`build_assignment`=%s' \ "$1" printf ')' -- cgit v1.2.3-54-g00ecf