bin/db-update, bin/bootstrap-mysql: move packages based on binary_packages_in_repositories

2 files changed, 168 insertions, 151 deletions

diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql
index a8bd1be..b8a966b 100755
--- a/bin/bootstrap-mysql
+++ b/bin/bootstrap-mysql
@@ -24,7 +24,7 @@ fi
 #shellcheck disable=SC2016
 {
   # calculate_maximal_moveable_set
-  # stores results in `moveable_binary_packages` and `replaced_binary_packages`
+  # stores results in `moveable_bpir` and `replaced_bpir`
   # Give a maximal list of packages to be moved, while implementing the
   # condition from db-update:
 
@@ -40,24 +40,22 @@ fi
   # a-1 in [core], then this will be "replaced" by a-2 on a system
   # running on [testing] repositories.
 
-  # TODO: operate on binary_packages_in_repositories instead of binary_packages
-
   printf 'DROP PROCEDURE IF EXISTS calculate_maximal_moveable_set;\n'
   printf 'DELIMITER //\n'
   printf 'CREATE PROCEDURE calculate_maximal_moveable_set(IN `from_stability` VARCHAR(32))\n'
   printf 'BEGIN\n'
-  # variables to store count of changed rows
+  # variable to store count of changed rows
   printf 'DECLARE row_count_saved INT DEFAULT 0;\n'
   for copy in '' '_copy' '_copy2'; do
-    printf 'DROP TEMPORARY TABLE IF EXISTS `%s_binary_packages%s`;\n' \
+    printf 'DROP TEMPORARY TABLE IF EXISTS `%s_bpir%s`;\n' \
       'moveable' "${copy}" \
       'replaced' "${copy}"
-    printf 'CREATE TEMPORARY TABLE `replaced_binary_packages%s` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n' \
+    printf 'CREATE TEMPORARY TABLE `replaced_bpir%s` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n' \
       "${copy}"
     if [ "${copy}" = '_copy2' ]; then
       continue
     fi
-    printf 'CREATE TEMPORARY TABLE `moveable_binary_packages%s` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n' \
+    printf 'CREATE TEMPORARY TABLE `moveable_bpir%s` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n' \
       "${copy}"
   done
   printf 'DROP TEMPORARY TABLE IF EXISTS `package_blobs`;\n'
@@ -82,10 +80,10 @@ fi
   printf ' WHERE `a_rs`.`name` = `from_stability`'
   printf ' AND `b_rs`.`name` = `from_stability`;\n'
   # these packages are considered for moving:
-  printf 'INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)'
-  printf ' SELECT `binary_packages`.`id`,`repository_moves`.`to_repository`'
-  printf ' FROM `binary_packages`'
-  mysql_join_binary_packages_binary_packages_in_repositories
+  printf 'INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`)'
+  printf ' SELECT `binary_packages_in_repositories`.`id`,`repository_moves`.`to_repository`'
+  printf ' FROM `binary_packages_in_repositories`'
+  mysql_join_binary_packages_in_repositories_binary_packages
   mysql_join_binary_packages_in_repositories_repositories
   mysql_join_repositories_repository_stabilities
   mysql_join_binary_packages_build_assignments
@@ -94,22 +92,15 @@ fi
   mysql_join_upstream_repositories_repository_moves
   printf ' AND `repository_moves`.`from_repository`=`binary_packages_in_repositories`.`repository`'
   # correct stability: "testing"/"staging" - as chosen
-  printf ' WHERE `repository_stabilities`.`name` = `from_stability`'
-  printf ' AND ('
-    printf '`from_stability`="staging"'
-    # "testing" packages must be tested
-    printf ' OR `binary_packages`.`is_tested`'
-  printf ')'
-  # no open issues
-  printf ' AND NOT `binary_packages`.`has_issues`'
+  printf ' WHERE `repository_stabilities`.`name`=`from_stability`'
   printf ';\n'
   # these packages are considered for being replaced:
   # for each moved package
-  printf 'INSERT IGNORE INTO `replaced_binary_packages` (`id`,`replaced_by`)'
-  printf ' SELECT `r_bp`.`id`,`m_bp`.`id`'
-  printf ' FROM `moveable_binary_packages`'
-  printf ' JOIN `binary_packages` AS `m_bp` ON `m_bp`.`id`=`moveable_binary_packages`.`id`'
-  mysql_join_binary_packages_binary_packages_in_repositories 'm_bp' 'm_bpir'
+  printf 'INSERT IGNORE INTO `replaced_bpir` (`id`,`replaced_by`)'
+  printf ' SELECT `r_bpir`.`id`,`m_bpir`.`id`'
+  printf ' FROM `moveable_bpir`'
+  printf ' JOIN `binary_packages_in_repositories` AS `m_bpir` ON `m_bpir`.`id`=`moveable_bpir`.`id`'
+  mysql_join_binary_packages_in_repositories_binary_packages 'm_bpir' 'm_bp'
   mysql_join_binary_packages_in_repositories_repositories 'm_bpir' 'm_r'
   mysql_join_binary_packages_build_assignments 'm_bp' 'm_ba'
   mysql_join_build_assignments_package_sources 'm_ba' 'm_ps'
@@ -134,19 +125,20 @@ fi
         [ "${copy}" = '2' ]; then
         continue
       fi
-      printf 'DELETE FROM `%s_binary_packages_copy%s`;\n' "${table}" "${copy}"
-      printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}"
-      printf ' SELECT `%s_binary_packages`.*' "${table}"
-      printf ' FROM `%s_binary_packages`;\n' "${table}"
+      printf 'DELETE FROM `%s_bpir_copy%s`;\n' "${table}" "${copy}"
+      printf 'INSERT IGNORE INTO `%s_bpir_copy%s`' "${table}" "${copy}"
+      printf ' SELECT `%s_bpir`.*' "${table}"
+      printf ' FROM `%s_bpir`;\n' "${table}"
     done
   done
   # a package is not moveable if its dependencies are not provided ...
-  printf 'DELETE `replaced_binary_packages`,`moveable_binary_packages`'
-  printf ' FROM `replaced_binary_packages`'
-  printf ' RIGHT JOIN `moveable_binary_packages`'
-  printf ' ON `moveable_binary_packages`.`id`=`replaced_binary_packages`.`replaced_by`'
-  printf ' JOIN `binary_packages` ON `binary_packages`.`id`=`moveable_binary_packages`.`id`'
-  printf ' JOIN `repositories` AS `target_repositories` ON `moveable_binary_packages`.`to_repository`=`target_repositories`.`id`'
+  printf 'DELETE `replaced_bpir`,`moveable_bpir`'
+  printf ' FROM `replaced_bpir`'
+  printf ' RIGHT JOIN `moveable_bpir`'
+  printf ' ON `moveable_bpir`.`id`=`replaced_bpir`.`replaced_by`'
+  printf ' JOIN `binary_packages_in_repositories` ON `binary_packages_in_repositories`.`id`=`moveable_bpir`.`id`'
+  printf ' JOIN `repositories` AS `target_repositories` ON `moveable_bpir`.`to_repository`=`target_repositories`.`id`'
+  mysql_join_binary_packages_in_repositories_binary_packages
   mysql_join_binary_packages_dependencies
   mysql_join_dependencies_dependency_types
   printf ' AND `dependency_types`.`relevant_for_binary_packages`'
@@ -160,13 +152,14 @@ fi
     printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
     printf ' AND `target_repositories`.`stability`=`repository_stability_relations`.`less_stable`'
     printf ' AND NOT EXISTS ('
-      printf 'SELECT 1 FROM `replaced_binary_packages_copy`'
-      printf ' WHERE `replaced_binary_packages_copy`.`id`=`prov_bp`.`id`'
+      printf 'SELECT 1 FROM `replaced_bpir_copy`'
+      printf ' WHERE `replaced_bpir_copy`.`id`=`prov_bpir`.`id`'
     printf ')'
   # ... by a moved package
   printf ') AND NOT EXISTS ('
     printf 'SELECT 1 FROM `install_target_providers`'
-    printf ' JOIN `moveable_binary_packages_copy` ON `moveable_binary_packages_copy`.`id`=`install_target_providers`.`package`'
+    mysql_join_install_target_providers_binary_packages_in_repositories '' 'itp_bpir'
+    printf ' JOIN `moveable_bpir_copy` ON `moveable_bpir_copy`.`id`=`itp_bpir`.`id`'
     printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
   printf ');\n'
   printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n'
@@ -178,10 +171,10 @@ fi
         [ "${copy}" = '2' ]; then
         continue
       fi
-      printf 'DELETE FROM `%s_binary_packages_copy%s`;\n' "${table}" "${copy}"
-      printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}"
-      printf ' SELECT `%s_binary_packages`.*' "${table}"
-      printf ' FROM `%s_binary_packages`;\n' "${table}"
+      printf 'DELETE FROM `%s_bpir_copy%s`;\n' "${table}" "${copy}"
+      printf 'INSERT IGNORE INTO `%s_bpir_copy%s`' "${table}" "${copy}"
+      printf ' SELECT `%s_bpir`.*' "${table}"
+      printf ' FROM `%s_bpir`;\n' "${table}"
     done
   done
 
@@ -198,23 +191,25 @@ fi
   #
   # However, in practice both should not happen.
   #
-  printf 'DELETE `replaced_binary_packages`,`moveable_binary_packages` FROM `replaced_binary_packages`'
-  printf ' JOIN `moveable_binary_packages` ON `replaced_binary_packages`.`replaced_by`=`moveable_binary_packages`.`id`'
-  printf ' JOIN `binary_packages` AS `repl_bp` ON `repl_bp`.`id`=`replaced_binary_packages`.`id`'
-  mysql_join_binary_packages_install_target_providers 'repl_bp'
+  printf 'DELETE `replaced_bpir`,`moveable_bpir` FROM `replaced_bpir`'
+  printf ' JOIN `moveable_bpir` ON `replaced_bpir`.`replaced_by`=`moveable_bpir`.`id`'
+  printf ' JOIN `binary_packages_in_repositories` AS `repl_bpir` ON `repl_bpir`.`id`=`replaced_bpir`.`id`'
+  mysql_join_binary_packages_in_repositories_install_target_providers 'repl_bpir'
+  mysql_join_binary_packages_in_repositories_repositories 'repl_bpir' 'repl_r'
   mysql_join_install_target_providers_dependencies
   printf ' AND NOT EXISTS ('
     # dependencies of replaced packages don't matter
-    printf 'SELECT 1 FROM `replaced_binary_packages_copy`'
-    printf ' WHERE `replaced_binary_packages_copy`.`id`=`dependencies`.`dependent`'
+    printf 'SELECT 1 FROM `replaced_bpir_copy`'
+    printf ' JOIN `binary_packages_in_repositories` AS `repl_bpir_copy` ON `repl_bpir_copy`.`id`=`replaced_bpir_copy`.`id`'
+    mysql_join_binary_packages_in_repositories_repositories 'repl_bpir_copy' 'repl_r_copy'
+    printf ' WHERE `repl_bpir_copy`.`package`=`dependencies`.`dependent`'
+    printf ' AND `repl_r_copy`.`architecture`=``repl_r`.`architecture`'
   printf ')'
   mysql_join_dependencies_dependency_types
   # consider only runtime dependencies
   printf ' AND `dependency_types`.`relevant_for_binary_packages`'
   mysql_join_dependencies_binary_packages '' 'req_bp'
   # we need to check wether req_bp's dependency is (un)critical
-  mysql_join_binary_packages_binary_packages_in_repositories 'repl_bp' 'repl_bpir'
-  mysql_join_binary_packages_in_repositories_repositories 'repl_bpir' 'repl_r'
   mysql_join_binary_packages_binary_packages_in_repositories 'req_bp' 'req_bpir'
   mysql_join_binary_packages_in_repositories_repositories 'req_bpir' 'req_r'
   # dependent package is "less stable" than dependency
@@ -225,28 +220,32 @@ fi
   printf ' WHERE NOT EXISTS ('
     # no moved package ...
     printf 'SELECT 1'
-    printf ' FROM `moveable_binary_packages_copy`'
-    mysql_join_binary_packages_install_target_providers 'moveable_binary_packages_copy' 'subst_itp'
+    printf ' FROM `moveable_bpir_copy`'
+    printf ' JOIN `binary_packages_in_repositories` AS `subst_bpir` ON `subst_bpir`.`id`=`moveable_bpir_copy`.`id`'
+    mysql_join_binary_packages_in_repositories_install_target_providers 'subst_bpir' 'subst_itp'
+    mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r'
     # ... provides the same
     printf ' WHERE `subst_itp`.`install_target`=`install_target_providers`.`install_target`'
+    printf ' AND `subst_r`.`architecture`=`repl_r`.`architecture`'
   # b) 1) ^
   printf ') AND NOT EXISTS ('
     # no current package ...
     printf 'SELECT 1'
-    printf ' FROM `binary_packages` AS `subst_bp`'
-    mysql_join_binary_packages_install_target_providers 'subst_bp' 'subst_itp'
+    printf ' FROM `binary_packages_in_repositories` AS `subst_bpir`'
+    mysql_join_binary_packages_in_repositories_install_target_providers 'subst_bpir' 'subst_itp'
     # ... in a repository ...
-    mysql_join_binary_packages_binary_packages_in_repositories 'subst_bp' 'subst_bpir'
     mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r'
     # ... more stable ...
     printf ' JOIN `repository_stability_relations` AS `subst_rr`'
     printf ' ON `subst_rr`.`more_stable`=`subst_r`.`stability`'
     # ... than x's repository ...
     printf ' WHERE `subst_rr`.`less_stable`=`repl_r`.`stability`'
+    # (architecture check is not necessary, as repositories of different
+    # architectures are not less or more stable than each other)
     printf ' AND NOT EXISTS ('
     # ... and which is not replaced ...
-      printf 'SELECT 1 FROM `replaced_binary_packages_copy2`'
-      printf ' WHERE `replaced_binary_packages_copy2`.`id`=`subst_bp`.`id`'
+      printf 'SELECT 1 FROM `replaced_bpir_copy2`'
+      printf ' WHERE `replaced_bpir_copy2`.`id`=`subst_bpir`.`id`'
     printf ')'
     # ... and provides the same
     printf ' AND `subst_itp`.`install_target`=`install_target_providers`.`install_target`'
@@ -261,27 +260,30 @@ fi
         [ "${copy}" = '2' ]; then
         continue
       fi
-      printf 'DELETE FROM `%s_binary_packages_copy%s`;\n' "${table}" "${copy}"
-      printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}"
-      printf ' SELECT `%s_binary_packages`.*' "${table}"
-      printf ' FROM `%s_binary_packages`;\n' "${table}"
+      printf 'DELETE FROM `%s_bpir_copy%s`;\n' "${table}" "${copy}"
+      printf 'INSERT IGNORE INTO `%s_bpir_copy%s`' "${table}" "${copy}"
+      printf ' SELECT `%s_bpir`.*' "${table}"
+      printf ' FROM `%s_bpir`;\n' "${table}"
     done
   done
 
-  printf 'DELETE `replaced_binary_packages`,`moveable_binary_packages` FROM `replaced_binary_packages`'
-  printf ' RIGHT JOIN `moveable_binary_packages`'
-  printf ' ON `replaced_binary_packages`.`replaced_by`=`moveable_binary_packages`.`id`'
-  printf ' JOIN `binary_packages`'
-  printf ' ON `binary_packages`.`id`=`moveable_binary_packages`.`id`'
+  # remove all packages of a blob, where some part cannot be moved
+  printf 'DELETE `replaced_bpir`,`moveable_bpir` FROM `replaced_bpir`'
+  printf ' RIGHT JOIN `moveable_bpir`'
+  printf ' ON `replaced_bpir`.`replaced_by`=`moveable_bpir`.`id`'
+  printf ' JOIN `binary_packages_in_repositories`'
+  printf ' ON `binary_packages_in_repositories`.`id`=`moveable_bpir`.`id`'
+  mysql_join_binary_packages_in_repositories_binary_packages
   mysql_join_binary_packages_build_assignments
   printf ' JOIN `package_blobs`'
   printf ' ON `build_assignments`.`package_source`=`package_blobs`.`ps_a`'
   printf ' JOIN `build_assignments` AS `bl_ba`'
   printf ' ON `bl_ba`.`package_source`=`package_blobs`.`ps_b`'
   mysql_join_build_assignments_binary_packages 'bl_ba' 'bl_bp'
+  mysql_join_binary_packages_binary_packages_in_repositories 'bl_bp' 'bl_bpir'
   printf ' WHERE NOT EXISTS ('
-    printf 'SELECT 1 FROM `moveable_binary_packages_copy`'
-    printf ' WHERE `moveable_binary_packages_copy`.`id`=`bl_bp`.`id`'
+    printf 'SELECT 1 FROM `moveable_bpir_copy`'
+    printf ' WHERE `moveable_bpir_copy`.`id`=`bl_bpir`.`id`'
   printf ');\n'
   printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n'
 
@@ -293,7 +295,7 @@ fi
         [ "${copy}" = '2' ]; then
         continue
       fi
-      printf 'DROP TEMPORARY TABLE `%s_binary_packages_copy%s`;\n' \
+      printf 'DROP TEMPORARY TABLE `%s_bpir_copy%s`;\n' \
         "${table}" "${copy}"
     done
   done
diff --git a/bin/db-update b/bin/db-update
index 2140880..0f15052 100755
--- a/bin/db-update
+++ b/bin/db-update
@@ -137,84 +137,88 @@ for source_stability in 'testing' 'staging'; do
   # shellcheck disable=SC2016
   {
     if [ -n "${force_ids}" ]; then
-      printf 'DROP TEMPORARY TABLE IF EXISTS `%s_binary_packages`;\n' \
+      printf 'DROP TEMPORARY TABLE IF EXISTS `%s_bpir`;\n' \
         'moveable' 'replaced'
-      printf 'CREATE TEMPORARY TABLE `replaced_binary_packages` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n'
-      printf 'CREATE TEMPORARY TABLE `moveable_binary_packages` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n'
-      printf 'INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)'
+      printf 'CREATE TEMPORARY TABLE `replaced_bpir` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n'
+      printf 'CREATE TEMPORARY TABLE `moveable_bpir` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n'
+      printf 'INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`)'
       printf ' VALUES'
       # shellcheck disable=SC2086
       printf '(from_base64("%s"),NULL),' \
         ${force_ids} | \
         sed 's/,$/;\n/'
-      printf 'DELETE `moveable_binary_packages` FROM `moveable_binary_packages`'
-      printf ' JOIN `binary_packages` ON `binary_packages`.`id`=`moveable_binary_packages`.`id`'
-      mysql_join_binary_packages_binary_packages_in_repositories
+      printf 'DELETE `moveable_bpir` FROM `moveable_bpir`'
+      printf ' JOIN `binary_packages_in_repositories` ON `binary_packages_in_repositories`.`id`=`moveable_bpir`.`id`'
+      mysql_join_binary_packages_in_repositories_binary_packages
       mysql_join_binary_packages_in_repositories_repositories
       mysql_join_repositories_repository_stabilities
       printf ' WHERE `repository_stabilities`.`name`!="%s";\n' \
         "${source_stability}"
-      printf 'UPDATE `moveable_binary_packages`'
-      printf ' JOIN `binary_packages` ON `binary_packages`.`id`=`moveable_binary_packages`.`id`'
+      printf 'UPDATE `moveable_bpir`'
+      printf ' JOIN `binary_packages_in_repositories` ON `binary_packages_in_repositories`.`id`=`moveable_bpir`.`id`'
+      mysql_join_binary_packages_in_repositories_binary_packages
       mysql_join_binary_packages_build_assignments
       mysql_join_build_assignments_package_sources
       mysql_join_package_sources_upstream_repositories
       mysql_join_upstream_repositories_repository_moves
-      mysql_join_binary_packages_binary_packages_in_repositories
       printf ' AND `repository_moves`.`from_repository`=`binary_packages_in_repositories`.`repository`'
       printf ' SET `moveable_binary_packages`.`to_repository`=`repository_moves`.`to_repository`;\n'
-      printf 'DELETE FROM `moveable_binary_packages` WHERE `moveable_binary_packages`.`to_repository` IS NULL;\n'
-      printf 'INSERT IGNORE INTO `replaced_binary_packages` (`id`,`replaced_by`)'
-      printf ' SELECT `binary_packages`.`id`,`moveable_binary_packages`.`id`'
-      printf ' FROM `moveable_binary_packages`'
-      printf ' JOIN `binary_packages` AS `subst_bp` ON `moveable_binary_packages`.`id`=`subst_bp`.`id`'
+      printf 'DELETE FROM `moveable_bpir` WHERE `moveable_bpir`.`to_repository` IS NULL;\n'
+      printf 'INSERT IGNORE INTO `replaced_bpir` (`id`,`replaced_by`)'
+      printf ' SELECT `binary_packages_in_repositories`.`id`,`moveable_bpir`.`id`'
+      printf ' FROM `moveable_bpir`'
+      printf ' JOIN `binary_packages_in_repositories` AS `subst_bpir` ON `moveable_bpir`.`id`=`subst_bpir`.`id`'
+      mysql_join_binary_packages_in_repositories_binary_packages 'subst_bpir' 'subst_bp'
       printf ' JOIN `binary_packages` ON `binary_packages`.`pkgname`=`subst_bp`.`pkgname`'
-      mysql_join_binary_packages_binary_packages_in_repositories
+      mysql_join_in_repositories_binary_packages_binary_packages
       printf ' AND `binary_packages_in_repositories`.`repository`=`moveable_binary_packages`.`to_repository`;\n'
     elif ${progressive}; then
-      printf 'DROP TEMPORARY TABLE IF EXISTS `%s_binary_packages`;\n' \
+      printf 'DROP TEMPORARY TABLE IF EXISTS `%s_bpir`;\n' \
         'moveable' 'replaced'
-      printf 'CREATE TEMPORARY TABLE `replaced_binary_packages` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n'
-      printf 'CREATE TEMPORARY TABLE `moveable_binary_packages` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n'
-
-      printf 'INSERT IGNORE INTO `replaced_binary_packages` (`id`,`replaced_by`)'
-      printf ' SELECT `binary_packages`.`id`,`subst_bp`.`id`'
-      printf ' FROM `binary_packages`'
-      mysql_join_binary_packages_binary_packages_in_repositories
-      mysql_join_binary_packages_in_repositories_repositories
-      printf ' AND `repositories`.`is_on_master_mirror`'
-      mysql_join_binary_packages_build_assignments
+      printf 'CREATE TEMPORARY TABLE `replaced_bpir` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n'
+      printf 'CREATE TEMPORARY TABLE `moveable_bpir` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n'
+
+      printf 'INSERT IGNORE INTO `replaced_bpir` (`id`,`replaced_by`)'
+      printf ' SELECT `old_bpir`.`id`,`new_bpir`.`id`'
+      printf ' FROM `binary_packages_in_repositories` AS `new_bpir`'
+      mysql_join_binary_packages_in_repositories_binary_packages 'new_bpir' 'new_bp'
+      # the new package is _currently_ in the old repository
+      mysql_join_binary_packages_in_repositories_repositories 'new_bpir' 'old_r'
+      printf ' AND `old_r`.`is_on_master_mirror`'
+      mysql_join_binary_packages_build_assignments 'new_bp'
       mysql_join_build_assignments_package_sources
       mysql_join_package_sources_upstream_repositories
       mysql_join_upstream_repositories_repository_moves
-      mysql_join_binary_packages_binary_packages_in_repositories
-      printf ' AND `repository_moves`.`to_repository`=`binary_packages_in_repositories`.`repository`'
-      printf ' JOIN `binary_packages` AS `subst_bp`'
-      printf ' ON `binary_packages`.`pkgname`=`subst_bp`.`pkgname`'
-      mysql_join_binary_packages_binary_packages_in_repositories 'subst_bp' 'subst_bpir'
-      printf ' AND `repository_moves`.`from_repository`=`subst_bpir`.`repository`'
-      mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r'
-      mysql_join_repositories_repository_stabilities 'subst_r' 'subst_rs'
-      printf ' AND `subst_rs`.`name`="%s"' \
+      printf ' AND `repository_moves`.`from_repository`=`old_r`.`id`'
+      printf ' JOIN `binary_packages` AS `old_bp`'
+      printf ' ON `new_bp`.`pkgname`=`old_bp`.`pkgname`'
+      mysql_join_binary_packages_binary_packages_in_repositories 'old_bp' 'old_bpir'
+      # the old package is currently in the new repository
+      printf ' AND `repository_moves`.`to_repository`=`old_bpir`.`repository`'
+      mysql_join_repositories_repository_stabilities 'old_r' 'old_rs'
+      printf ' AND `old_rs`.`name`="%s"' \
         "${source_stability}"
-      mysql_join_binary_packages_dependencies
+      mysql_join_binary_packages_dependencies 'new_bp'
       mysql_join_dependencies_dependency_types
       printf ' AND `dependency_types`.`relevant_for_binary_packages`'
       printf ' WHERE NOT EXISTS ('
         printf 'SELECT 1 FROM `install_target_providers`'
+        mysql_join_install_target_providers_binary_packages_in_repositories
+        mysql_join_binary_packages_in_repositories_repositories
         printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
+        printf ' AND `repositories`.`architecture`=`old_r`.`architecture`'
       printf ');\n'
 
-      printf 'INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)'
-      printf ' SELECT `replaced_binary_packages`.`replaced_by`,`binary_packages_in_repositories`.`repository`'
-      printf ' FROM `replaced_binary_packages`'
-      printf ' JOIN `binary_packages_in_repositories` ON `binary_packages_in_repositories`.`package`=`replaced_binary_packages`.`id`'
+      printf 'INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`)'
+      printf ' SELECT `replaced_bpir`.`replaced_by`,`binary_packages_in_repositories`.`repository`'
+      printf ' FROM `replaced_bpir`'
+      printf ' JOIN `binary_packages_in_repositories` ON `binary_packages_in_repositories`.`id`=`replaced_bpir`.`id`'
       printf ';\n'
 
-      printf 'INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)'
-      printf ' SELECT `binary_packages`.`id`,`repository_moves`.`to_repository`'
-      printf ' FROM `binary_packages`'
-      mysql_join_binary_packages_binary_packages_in_repositories
+      printf 'INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`)'
+      printf ' SELECT `binary_packages_in_repositories`.`id`,`repository_moves`.`to_repository`'
+      printf ' FROM `binary_packages_in_repositories`'
+      mysql_join_binary_packages_in_repositories_binary_packages
       mysql_join_binary_packages_in_repositories_repositories
       printf ' AND `repositories`.`is_on_master_mirror`'
       mysql_join_repositories_repository_stabilities
@@ -224,11 +228,10 @@ for source_stability in 'testing' 'staging'; do
       mysql_join_build_assignments_package_sources
       mysql_join_package_sources_upstream_repositories
       mysql_join_upstream_repositories_repository_moves
-      mysql_join_binary_packages_binary_packages_in_repositories
       printf ' AND `repository_moves`.`from_repository`=`binary_packages_in_repositories`.`repository`'
       printf ' WHERE NOT EXISTS ('
-        printf 'SELECT 1 FROM `binary_packages` AS `repl_bp`'
-        mysql_join_binary_packages_binary_packages_in_repositories 'repl_bp' 'repl_bpir'
+        printf 'SELECT 1 FROM `binary_packages_in_repositories` AS `repl_bpir`'
+        mysql_join_binary_packages_in_repositories_binary_packages 'repl_bpir' 'repl_bp'
         printf ' WHERE `repl_bp`.`pkgname`=`binary_packages`.`pkgname`'
         printf ' AND `repl_bpir`.`repository`=`repository_moves`.`to_repository`'
       printf ');\n'
@@ -239,44 +242,44 @@ for source_stability in 'testing' 'staging'; do
 
     printf 'CREATE TEMPORARY TABLE `rps` (`id` MEDIUMINT, UNIQUE INDEX (`id`));\n'
     printf 'INSERT IGNORE INTO `rps` (`id`)'
-    printf ' SELECT `moveable_binary_packages`.`to_repository`'
-    printf ' FROM `moveable_binary_packages`;\n'
+    printf ' SELECT `moveable_bpir`.`to_repository`'
+    printf ' FROM `moveable_bpir`;\n'
     printf 'INSERT IGNORE INTO `rps` (`id`)'
     printf ' SELECT `binary_packages_in_repositories`.`repository`'
-    printf ' FROM `moveable_binary_packages`'
-    printf ' JOIN `binary_packages_in_repositories` ON `moveable_binary_packages`.`id`=`binary_packages_in_repositories`.`package`;\n'
+    printf ' FROM `moveable_bpir`'
+    printf ' JOIN `binary_packages_in_repositories` ON `moveable_bpir`.`id`=`binary_packages_in_repositories`.`id`;\n'
     printf 'INSERT IGNORE INTO `rps` (`id`)'
     printf ' SELECT `binary_packages_in_repositories`.`repository`'
-    printf ' FROM `replaced_binary_packages`'
-    printf ' JOIN `binary_packages_in_repositories` ON `replaced_binary_packages`.`id`=`binary_packages_in_repositories`.`package`;\n'
+    printf ' FROM `replaced_bpir`'
+    printf ' JOIN `binary_packages_in_repositories` ON `replaced_bpir`.`id`=`binary_packages_in_repositories`.`id`;\n'
 
     printf 'SELECT "repositories",`repositories`.`name`'
     printf ' FROM `repositories`'
     printf ' JOIN `rps` ON `rps`.`id`=`repositories`.`id`;\n'
 
-    printf 'SELECT "mv.id",`moveable_binary_packages`.`id`,`moveable_binary_packages`.`to_repository`'
-    printf ' FROM `moveable_binary_packages`;\n'
+    printf 'SELECT "mv.id",`moveable_bpir`.`id`,`moveable_bpir`.`to_repository`'
+    printf ' FROM `moveable_bpir`;\n'
 
     printf 'SELECT "mv",'
     mysql_package_name_query
     printf ',`repositories`.`name`,`new_repo`.`name`'
-    printf ' FROM `moveable_binary_packages`'
-    printf ' JOIN `binary_packages` ON `moveable_binary_packages`.`id`=`binary_packages`.`id`'
-    mysql_join_binary_packages_binary_packages_in_repositories
+    printf ' FROM `moveable_bpir`'
+    printf ' JOIN `binary_packages_in_repositories` ON `moveable_bpir`.`id`=`binary_packages_in_repositories`.`id`'
+    mysql_join_binary_packages_in_repositories_binary_packages
     mysql_join_binary_packages_in_repositories_repositories
     mysql_join_binary_packages_architectures
-    printf ' JOIN `repositories` AS `new_repo` ON `new_repo`.`id`=`moveable_binary_packages`.`to_repository`'
+    printf ' JOIN `repositories` AS `new_repo` ON `new_repo`.`id`=`moveable_bpir`.`to_repository`'
     printf ';\n'
 
-    printf 'SELECT "rm.id",`replaced_binary_packages`.`id`'
-    printf ' FROM `replaced_binary_packages`;\n'
+    printf 'SELECT "rm.id",`replaced_bpir`.`id`'
+    printf ' FROM `replaced_bpir`;\n'
 
     printf 'SELECT "rm",'
     mysql_package_name_query
     printf ',`repositories`.`name`'
-    printf ' FROM `replaced_binary_packages`'
-    printf ' JOIN `binary_packages` ON `replaced_binary_packages`.`id`=`binary_packages`.`id`'
-    mysql_join_binary_packages_binary_packages_in_repositories
+    printf ' FROM `replaced_bpir`'
+    printf ' JOIN `binary_packages_in_repositories` ON `replaced_bpir`.`id`=`binary_packages_in_repositories`.`id`'
+    mysql_join_binary_packages_in_repositories_binary_packages
     mysql_join_binary_packages_in_repositories_repositories
     mysql_join_binary_packages_architectures
     printf ';\n'
@@ -368,10 +371,39 @@ for source_stability in 'testing' 'staging'; do
           "${tmp_dir}/dbs/${from_repo}/${from_repo}.db.tar.gz"
     done
 
+  # create real file names of packages, because
+  # mysql_query_and_delete_unneeded_binary_packages does so, too
+  sed -i '
+    s,^\(\S\+\) \(\S\+\)$,i686/\2/\1",
+  ' "${tmp_dir}/rm"
+
+  # shellcheck disable=SC2016
+  {
+    printf 'CREATE TEMPORARY TABLE `replaced_bpir` (`id` BIGINT, UNIQUE KEY (`id`));\n'
+    printf 'CREATE TEMPORARY TABLE `moved_bpir` (`id` BIGINT, `new_repository` MEDIUMINT, UNIQUE KEY (`id`));\n'
+    printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `%s` COLUMNS TERMINATED BY " ";\n' \
+      "${tmp_dir}/mv.id" 'moved_bpir' \
+      "${tmp_dir}/rm.id" 'replaced_bpir'
+    printf 'DELETE `binary_packages_in_repositories` FROM `binary_packages_in_repositories`'
+    printf ' JOIN `replaced_bpir` ON `binary_packages_in_repositories`.`id`=`replaced_binary_packages`.`id`;\n'
+    mysql_query_and_delete_unneeded_binary_packages
+    printf 'UPDATE `binary_packages_in_repositories`'
+    printf ' JOIN `moved_bpir` ON `binary_packages_in_repositories`.`id`=`moved_bpir`.`id`'
+    printf ' SET `binary_packages_in_repositories`.`repository`=`moved_bpir`.`new_repository`,'
+    printf '`binary_packages_in_repositories`.`last_moved`=NOW()'
+    printf ' WHERE `binary_packages_in_repositories`.`repository`!=`moved_bpir`.`new_repository`;\n'
+  } | \
+    mysql_run_query | \
+    sort -u >> \
+    "${tmp_dir}/rm"
+
   # move the packages remotely via sftp
   {
     sed '
-      s,^\(\S\+\) \(\S\+\)$,rm "i686/\2/\1"\nrm "i686/\2/\1.sig",
+      s/^/rm "/
+      s/$/"/
+      p
+      s/"$/.sig"/
     ' "${tmp_dir}/rm"
     sed '
       s,^\(\S\+\) \(\S\+\) \(\S\+\)$,rename "i686/\2/\1" "i686/\3/\1"\nrename "i686/\2/\1.sig" "i686/\3/\1.sig",
@@ -398,23 +430,6 @@ for source_stability in 'testing' 'staging'; do
   done < \
     "${tmp_dir}/repositories"
 
-  # shellcheck disable=SC2016
-  {
-    printf 'CREATE TEMPORARY TABLE `replaced_binary_packages` (`id` BIGINT, UNIQUE KEY (`id`));\n'
-    printf 'CREATE TEMPORARY TABLE `moved_binary_packages` (`id` BIGINT, `new_repository` MEDIUMINT, UNIQUE KEY (`id`));\n'
-    printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `%s` COLUMNS TERMINATED BY " ";\n' \
-      "${tmp_dir}/mv.id" 'moved_binary_packages' \
-      "${tmp_dir}/rm.id" 'replaced_binary_packages'
-    printf 'DELETE `binary_packages` FROM `binary_packages`'
-    printf ' JOIN `replaced_binary_packages` ON `binary_packages`.`id`=`replaced_binary_packages`.`id`;\n'
-    printf 'UPDATE `binary_packages`'
-    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_in_repositories`.`last_moved`=NOW()'
-    printf ' WHERE `binary_packages_in_repositories`.`repository`!=`moved_binary_packages`.`new_repository`;\n'
-  } | \
-    mysql_run_query
 done
 
 trigger_mirror_refreshs