make `binary_packages`.`is_to_be_deleted` and `binary_packages`.`last_moved` obsolete; cleanup some queries

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 ')'