From 7673a80a19fa5c8cbd71afe9ecf93ee5ede223de Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Tue, 24 Apr 2018 13:28:26 +0200 Subject: bin/bootstrap-mysql,misc/database-layout.dump: small cleanup --- bin/bootstrap-mysql | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'bin/bootstrap-mysql') diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql index 822d995..8ccb2a2 100755 --- a/bin/bootstrap-mysql +++ b/bin/bootstrap-mysql @@ -408,6 +408,7 @@ if [ ! "$1" = 'slim' ]; then # now we delete all unreplaceable and unmoveable packages from the respective # list until no further changes are required printf 'REPEAT\n' + printf 'SET row_count_saved = 0;\n' # create copies of our temporary tables *yuck* for table in 'replaced' 'moveable'; do for copy in '' '2'; do @@ -449,7 +450,7 @@ if [ ! "$1" = 'slim' ]; then printf ' JOIN `moveable_binary_packages_copy` ON `moveable_binary_packages_copy`.`id`=`install_target_providers`.`package`' printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`' printf ');\n' - printf 'SET @row_count_saved = ROW_COUNT();\n' + printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n' # refresh copies of our temporary tables *yuck* for table in 'replaced' 'moveable'; do @@ -529,7 +530,8 @@ if [ ! "$1" = 'slim' ]; then printf ' AND `subst_itp`.`install_target`=`install_target_providers`.`install_target`' # b) 2) ^ printf ');\n' - printf 'UNTIL row_count_saved=0 AND ROW_COUNT()=0\n' + printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n' + printf 'UNTIL row_count_saved=0\n' printf 'END REPEAT;\n' for table in 'moveable' 'replaced'; do for copy in '' '2'; do -- cgit v1.2.3-70-g09d2 From ae36f67281ba7a8c2ca1baa3f51e922d27f93fe1 Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Tue, 24 Apr 2018 14:19:37 +0200 Subject: bin/bootstrap-mysql,misc/database-layout.dump: do not move packages commited to svn within 10 seconds separately --- bin/bootstrap-mysql | 52 ++++++++++++++++++++++++++++++++++++++++++ misc/database-layout.dump | 58 +++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 110 insertions(+) (limited to 'bin/bootstrap-mysql') diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql index 8ccb2a2..2bcd212 100755 --- a/bin/bootstrap-mysql +++ b/bin/bootstrap-mysql @@ -367,6 +367,25 @@ if [ ! "$1" = 'slim' ]; then printf 'CREATE TEMPORARY TABLE `moveable_binary_packages%s` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n' \ "${copy}" done + printf 'DROP TEMPORARY TABLE IF EXISTS `package_blobs`;\n' + printf 'CREATE TEMPORARY TABLE `package_blobs` (`ps_a` BIGINT, `ps_b` BIGINT, UNIQUE KEY `content` (`ps_a`,`ps_b`));\n' + # all packages being vaguely relevant enter `package_blobs`, because + # they might block any other package from moving + printf 'INSERT IGNORE INTO `package_blobs` (`ps_a`,`ps_b`)' + printf ' SELECT `a_ps`.`id`,`b_ps`.`id`' + printf ' FROM `package_sources` AS `a_ps`' + printf ' JOIN `package_sources` AS `b_ps`' + printf ' ON UNIX_TIMESTAMP(`a_ps`.`commit_time`) - UNIX_TIMESTAMP(`b_ps`.`commit_time`) BETWEEN -10 AND 10' + mysql_join_package_sources_build_assignments 'a_ps' 'a_ba' + mysql_join_package_sources_build_assignments 'b_ps' 'b_ba' + mysql_join_build_assignments_binary_packages 'a_ba' 'a_bp' + mysql_join_build_assignments_binary_packages 'b_ba' 'b_bp' + mysql_join_binary_packages_repositories 'a_bp' 'a_r' + mysql_join_binary_packages_repositories 'b_bp' 'b_r' + mysql_join_repositories_repository_stabilities 'a_r' 'a_rs' + mysql_join_repositories_repository_stabilities 'b_r' 'b_rs' + 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`' @@ -409,6 +428,7 @@ if [ ! "$1" = 'slim' ]; then # list until no further changes are required printf 'REPEAT\n' printf 'SET row_count_saved = 0;\n' + # create copies of our temporary tables *yuck* for table in 'replaced' 'moveable'; do for copy in '' '2'; do @@ -531,6 +551,38 @@ if [ ! "$1" = 'slim' ]; then # b) 2) ^ printf ');\n' printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n' + + # refresh copies of our temporary tables *yuck* + for table in 'replaced' 'moveable'; do + for copy in '' '2'; do + if [ "${table}" = 'moveable' ] && \ + [ "${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}" + 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`' + 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' + printf ' WHERE NOT EXISTS (' + printf 'SELECT 1 FROM `moveable_binary_packages_copy`' + printf ' WHERE `moveable_binary_packages_copy`.`id`=`bl_bp`.`id`' + printf ');\n' + printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n' + printf 'UNTIL row_count_saved=0\n' printf 'END REPEAT;\n' for table in 'moveable' 'replaced'; do diff --git a/misc/database-layout.dump b/misc/database-layout.dump index 33c4e4a..d35d100 100644 --- a/misc/database-layout.dump +++ b/misc/database-layout.dump @@ -44,6 +44,30 @@ CREATE TEMPORARY TABLE `moveable_binary_packages_copy` (`id` BIGINT, `to_reposit DROP TEMPORARY TABLE IF EXISTS `moveable_binary_packages_copy2`; DROP TEMPORARY TABLE IF EXISTS `replaced_binary_packages_copy2`; CREATE TEMPORARY TABLE `replaced_binary_packages_copy2` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`)); +DROP TEMPORARY TABLE IF EXISTS `package_blobs`; +CREATE TEMPORARY TABLE `package_blobs` (`ps_a` BIGINT, `ps_b` BIGINT, UNIQUE KEY `content` (`ps_a`,`ps_b`)); +INSERT IGNORE INTO `package_blobs` (`ps_a`,`ps_b`) + SELECT `a_ps`.`id`,`b_ps`.`id` + FROM `package_sources` AS `a_ps` + JOIN `package_sources` AS `b_ps` + ON UNIX_TIMESTAMP(`a_ps`.`commit_time`) - UNIX_TIMESTAMP(`b_ps`.`commit_time`) BETWEEN -10 AND 10 + JOIN `build_assignments` AS `a_ba` + ON `a_ps`.`id`=`a_ba`.`package_source` + JOIN `build_assignments` AS `b_ba` + ON `b_ps`.`id`=`b_ba`.`package_source` + JOIN `binary_packages` AS `a_bp` + ON `a_ba`.`id`=`a_bp`.`build_assignment` + JOIN `binary_packages` AS `b_bp` + ON `b_ba`.`id`=`b_bp`.`build_assignment` + JOIN `repositories` AS `a_r` + ON `a_bp`.`repository`=`a_r`.`id` + JOIN `repositories` AS `b_r` + ON `b_bp`.`repository`=`b_r`.`id` + JOIN `repository_stabilities` AS `a_rs` + ON `a_r`.`stability`=`a_rs`.`id` + JOIN `repository_stabilities` AS `b_rs` + ON `b_r`.`stability`=`b_rs`.`id` + WHERE `a_rs`.`name` = `from_stability` AND `b_rs`.`name` = `from_stability`; INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`) SELECT `binary_packages`.`id`,`repository_moves`.`to_repository` FROM `binary_packages` @@ -184,6 +208,40 @@ SELECT * FROM `replaced_binary_packages_copy2` WHERE `replaced_binary_packages_copy2`.`id`=`subst_bp`.`id`) AND `subst_itp`.`install_target`=`install_target_providers`.`install_target`); SET row_count_saved = row_count_saved + ROW_COUNT(); +DELETE + FROM `replaced_binary_packages_copy`; +INSERT IGNORE INTO `replaced_binary_packages_copy` + SELECT `replaced_binary_packages`.* + FROM `replaced_binary_packages`; +DELETE + FROM `replaced_binary_packages_copy2`; +INSERT IGNORE INTO `replaced_binary_packages_copy2` + SELECT `replaced_binary_packages`.* + FROM `replaced_binary_packages`; +DELETE + FROM `moveable_binary_packages_copy`; +INSERT IGNORE INTO `moveable_binary_packages_copy` + SELECT `moveable_binary_packages`.* + FROM `moveable_binary_packages`; +DELETE `replaced_binary_packages`,`moveable_binary_packages` + FROM `replaced_binary_packages` + RIGHT JOIN `moveable_binary_packages` + ON `replaced_binary_packages`.`replaced_by`=`moveable_binary_packages`.`id` + JOIN `binary_packages` + ON `binary_packages`.`id`=`moveable_binary_packages`.`id` + JOIN `build_assignments` + ON `binary_packages`.`build_assignment`=`build_assignments`.`id` + JOIN `package_blobs` + ON `build_assignments`.`package_source`=`package_blobs`.`ps_a` + JOIN `build_assignments` AS `bl_ba` + ON `bl_ba`.`package_source`=`package_blobs`.`ps_b` + JOIN `binary_packages` AS `bl_bp` + ON `bl_ba`.`id`=`bl_bp`.`build_assignment` + WHERE NOT EXISTS ( +SELECT 1 + FROM `moveable_binary_packages_copy` + WHERE `moveable_binary_packages_copy`.`id`=`bl_bp`.`id`); +SET row_count_saved = row_count_saved + ROW_COUNT(); UNTIL row_count_saved=0 END REPEAT; DROP TEMPORARY TABLE `moveable_binary_packages_copy`; -- cgit v1.2.3-70-g09d2 From bea2e56b0b86d478c0d5247b2c8ed8a939eac349 Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Tue, 24 Apr 2018 14:22:19 +0200 Subject: bin/bootstrap-mysql,misc/database-layout.dump: "EXISTS (SELECT * FROM ...)" -> "EXISTS (SELECT 1 FROM ...)" --- bin/bootstrap-mysql | 14 +++++++------- misc/database-layout.dump | 14 +++++++------- 2 files changed, 14 insertions(+), 14 deletions(-) (limited to 'bin/bootstrap-mysql') diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql index 2bcd212..35f5398 100755 --- a/bin/bootstrap-mysql +++ b/bin/bootstrap-mysql @@ -454,19 +454,19 @@ if [ ! "$1" = 'slim' ]; then printf ' AND `dependency_types`.`relevant_for_binary_packages`' # ... by a not-deleted, "more stable" package already in place or ... printf ' WHERE NOT EXISTS (' - printf 'SELECT * FROM `install_target_providers`' + printf 'SELECT 1 FROM `install_target_providers`' mysql_join_install_target_providers_binary_packages '' 'prov_bp' mysql_join_binary_packages_repositories 'prov_bp' 'prov_r' printf ' JOIN `repository_stability_relations` ON `prov_r`.`stability`=`repository_stability_relations`.`more_stable`' 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 * FROM `replaced_binary_packages_copy`' + printf 'SELECT 1 FROM `replaced_binary_packages_copy`' printf ' WHERE `replaced_binary_packages_copy`.`id`=`prov_bp`.`id`' printf ')' # ... by a moved package printf ') AND NOT EXISTS (' - printf 'SELECT * FROM `install_target_providers`' + printf 'SELECT 1 FROM `install_target_providers`' printf ' JOIN `moveable_binary_packages_copy` ON `moveable_binary_packages_copy`.`id`=`install_target_providers`.`package`' printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`' printf ');\n' @@ -506,7 +506,7 @@ if [ ! "$1" = 'slim' ]; then mysql_join_install_target_providers_dependencies printf ' AND NOT EXISTS (' # dependencies of replaced packages don't matter - printf 'SELECT * FROM `replaced_binary_packages_copy`' + printf 'SELECT 1 FROM `replaced_binary_packages_copy`' printf ' WHERE `replaced_binary_packages_copy`.`id`=`dependencies`.`dependent`' printf ')' mysql_join_dependencies_dependency_types @@ -523,7 +523,7 @@ if [ ! "$1" = 'slim' ]; then # a) ^ printf ' WHERE NOT EXISTS (' # no moved package ... - printf 'SELECT *' + printf 'SELECT 1' printf ' FROM `moveable_binary_packages_copy`' mysql_join_binary_packages_install_target_providers 'moveable_binary_packages_copy' 'subst_itp' # ... provides the same @@ -531,7 +531,7 @@ if [ ! "$1" = 'slim' ]; then # b) 1) ^ printf ') AND NOT EXISTS (' # no current package ... - printf 'SELECT *' + printf 'SELECT 1' printf ' FROM `binary_packages` AS `subst_bp`' mysql_join_binary_packages_install_target_providers 'subst_bp' 'subst_itp' # ... in a repository ... @@ -543,7 +543,7 @@ if [ ! "$1" = 'slim' ]; then printf ' WHERE `subst_rr`.`less_stable`=`repl_r`.`stability`' printf ' AND NOT EXISTS (' # ... and which is not replaced ... - printf 'SELECT * FROM `replaced_binary_packages_copy2`' + printf 'SELECT 1 FROM `replaced_binary_packages_copy2`' printf ' WHERE `replaced_binary_packages_copy2`.`id`=`subst_bp`.`id`' printf ')' # ... and provides the same diff --git a/misc/database-layout.dump b/misc/database-layout.dump index d35d100..e27afbd 100644 --- a/misc/database-layout.dump +++ b/misc/database-layout.dump @@ -133,7 +133,7 @@ DELETE `replaced_binary_packages`,`moveable_binary_packages` JOIN `dependency_types` ON `dependencies`.`dependency_type`=`dependency_types`.`id` AND `dependency_types`.`relevant_for_binary_packages` WHERE NOT EXISTS ( -SELECT * +SELECT 1 FROM `install_target_providers` JOIN `binary_packages` AS `prov_bp` ON `install_target_providers`.`package`=`prov_bp`.`id` @@ -142,10 +142,10 @@ SELECT * JOIN `repository_stability_relations` ON `prov_r`.`stability`=`repository_stability_relations`.`more_stable` WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on` AND `target_repositories`.`stability`=`repository_stability_relations`.`less_stable` AND NOT EXISTS ( -SELECT * +SELECT 1 FROM `replaced_binary_packages_copy` WHERE `replaced_binary_packages_copy`.`id`=`prov_bp`.`id`)) AND NOT EXISTS ( -SELECT * +SELECT 1 FROM `install_target_providers` JOIN `moveable_binary_packages_copy` ON `moveable_binary_packages_copy`.`id`=`install_target_providers`.`package` @@ -176,7 +176,7 @@ DELETE `replaced_binary_packages`,`moveable_binary_packages` ON `repl_bp`.`id`=`install_target_providers`.`package` JOIN `dependencies` ON `install_target_providers`.`install_target`=`dependencies`.`depending_on` AND NOT EXISTS ( -SELECT * +SELECT 1 FROM `replaced_binary_packages_copy` WHERE `replaced_binary_packages_copy`.`id`=`dependencies`.`dependent`) JOIN `dependency_types` @@ -190,12 +190,12 @@ SELECT * JOIN `repository_stability_relations` AS `repl_rr` ON `repl_rr`.`more_stable`=`repl_r`.`stability` AND `repl_rr`.`less_stable`=`req_r`.`stability` WHERE NOT EXISTS ( -SELECT * +SELECT 1 FROM `moveable_binary_packages_copy` JOIN `install_target_providers` AS `subst_itp` ON `moveable_binary_packages_copy`.`id`=`subst_itp`.`package` WHERE `subst_itp`.`install_target`=`install_target_providers`.`install_target`) AND NOT EXISTS ( -SELECT * +SELECT 1 FROM `binary_packages` AS `subst_bp` JOIN `install_target_providers` AS `subst_itp` ON `subst_bp`.`id`=`subst_itp`.`package` @@ -204,7 +204,7 @@ SELECT * JOIN `repository_stability_relations` AS `subst_rr` ON `subst_rr`.`more_stable`=`subst_r`.`stability` WHERE `subst_rr`.`less_stable`=`repl_r`.`stability` AND NOT EXISTS ( -SELECT * +SELECT 1 FROM `replaced_binary_packages_copy2` WHERE `replaced_binary_packages_copy2`.`id`=`subst_bp`.`id`) AND `subst_itp`.`install_target`=`install_target_providers`.`install_target`); SET row_count_saved = row_count_saved + ROW_COUNT(); -- cgit v1.2.3-70-g09d2