From 98d4655aba96a7d318a510e4609d943b82990426 Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Mon, 4 Jun 2018 12:13:42 +0200 Subject: bin/db-update, bin/bootstrap-mysql: move packages based on binary_packages_in_repositories --- bin/bootstrap-mysql | 138 +++++++++++++++++++-------------------- bin/db-update | 181 ++++++++++++++++++++++++++++------------------------ 2 files changed, 168 insertions(+), 151 deletions(-) (limited to 'bin') 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 -- cgit v1.2.3-54-g00ecf