misc/database-layout.dump: new stored functions: find_the_culprit and blacklist_packages

1 files changed, 194 insertions, 0 deletions

diff --git a/misc/database-layout.dump b/misc/database-layout.dump
index 9cb1800..f067205 100644
--- a/misc/database-layout.dump
+++ b/misc/database-layout.dump
@@ -1,3 +1,65 @@
+blacklist_packages	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `blacklist_packages`()
+blacklist_packages:BEGIN
+DECLARE row_count_saved INT DEFAULT 0;
+CREATE TEMPORARY TABLE `bl` (`arch` SMALLINT,`pkgbase` VARCHAR(64),UNIQUE KEY `content`(`arch`,`pkgbase`));
+CREATE TEMPORARY TABLE `bl_copy` (`arch` SMALLINT,`pkgbase` VARCHAR(64),UNIQUE KEY `content`(`arch`,`pkgbase`));
+INSERT IGNORE INTO `bl`(`arch`,`pkgbase`)
+	SELECT `architectures`.`id`,`blacklist`.`pkgbase`
+	FROM `blacklist`
+	JOIN `architectures` AS `bl_a`
+	ON `bl_a`.`name`=`blacklist`.`arch`
+	JOIN `architecture_compatibilities`
+	ON `architecture_compatibilities`.`runs_on`=`bl_a`.`id` OR `bl_a`.`name`="any"
+	JOIN `architectures`
+	ON `architectures`.`id`=`architecture_compatibilities`.`built_for`
+	WHERE `architectures`.`name`!="any";
+REPEAT
+DELETE
+	FROM `bl_copy`;
+INSERT IGNORE INTO `bl_copy` (`arch`,`pkgbase`)
+	SELECT `bl`.`arch`, `bl`.`pkgbase`
+	FROM `bl`;
+INSERT IGNORE INTO `bl` (`arch`,`pkgbase`)
+	SELECT `a_r`.`architecture`,`a_ps`.`pkgbase`
+	FROM `package_sources` AS `a_ps`
+	JOIN `build_assignments` AS `a_ba`
+	ON `a_ps`.`id`=`a_ba`.`package_source`
+	JOIN `binary_packages` AS `a_bp`
+	ON `a_ba`.`id`=`a_bp`.`build_assignment`
+	JOIN `binary_packages_in_repositories` AS `a_bpir`
+	ON `a_bp`.`id`=`a_bpir`.`package`
+	JOIN `repositories` AS `a_r`
+	ON `a_bpir`.`repository`=`a_r`.`id`
+	JOIN `architectures` AS `a_ra`
+	ON `a_r`.`architecture`=`a_ra`.`id`
+	JOIN `dependencies`
+	ON `a_bp`.`id`=`dependencies`.`dependent`
+	JOIN `dependency_types`
+	ON `dependencies`.`dependency_type`=`dependency_types`.`id` AND (`dependency_types`.`relevant_for_building` OR `dependency_types`.`relevant_for_binary_packages`)
+	WHERE `a_ra`.`name`!="any" AND EXISTS (
+SELECT 1
+	FROM `install_target_providers`
+	WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`) AND NOT EXISTS (
+SELECT 1
+	FROM `install_target_providers`
+	WHERE NOT EXISTS (
+SELECT 1
+	FROM `bl_copy`
+	JOIN `package_sources` AS `b_ps`
+	ON `bl_copy`.`pkgbase`=`b_ps`.`pkgbase`
+	JOIN `build_assignments` AS `b_ba`
+	ON `b_ps`.`id`=`b_ba`.`package_source`
+	JOIN `binary_packages` AS `b_bp`
+	ON `b_ba`.`id`=`b_bp`.`build_assignment`
+	WHERE `install_target_providers`.`package`=`b_bp`.`id`) AND `install_target_providers`.`install_target`=`dependencies`.`depending_on`);
+UNTIL ROW_COUNT()=0
+END REPEAT;
+SELECT `architectures`.`name`,`bl`.`pkgbase`
+	FROM `bl`
+	JOIN `architectures`
+	ON `architectures`.`id`=`bl`.`arch`;
+DROP TEMPORARY TABLE `bl`;
+END	utf8mb4	utf8mb4_unicode_ci	utf8mb4_unicode_ci
 calculate_dependencies_of_package_upto_first_built_one	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `calculate_dependencies_of_package_upto_first_built_one`(IN `target_pkgbase` VARCHAR(64))
 BEGIN
 INSERT IGNORE INTO `relevant_binary_packages` (`id`)
@@ -331,6 +393,138 @@ DROP TEMPORARY TABLE `moveable_bpir_copy`;
 DROP TEMPORARY TABLE `replaced_bpir_copy`;
 DROP TEMPORARY TABLE `replaced_bpir_copy2`;
 END	utf8mb4	utf8mb4_unicode_ci	utf8mb4_unicode_ci
+find_the_culprit	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `find_the_culprit`(IN `bpir` BIGINT)
+find_the_culprit:BEGIN
+DECLARE row_count_saved INT DEFAULT 0;
+UPDATE `knots` SET `knots`.`reason_length`=NULL, `knots`.`relevant`=NULL, `knots`.`active`=1;
+DELETE
+	FROM `edges_copy`;
+INSERT INTO `edges_copy` (`cause`,`impact`,`invert`)
+	SELECT `edges`.`cause`,`edges`.`impact`,`edges`.`invert`
+	FROM `edges`;
+REPEAT
+UPDATE `knots_copy`
+	JOIN `knots`
+	ON `knots_copy`.`id`=`knots`.`id` SET `knots_copy`.`reason_length`=`knots`.`reason_length`, `knots_copy`.`active`=`knots`.`active`;
+SET row_count_saved=0;
+UPDATE `knots`
+	JOIN (
+SELECT `edges`.`impact`, MAX(IF(`knots_copy`.`reason_length` IS NULL,0,`edges`.`invert` XOR `knots_copy`.`active`)) AS `active`, MIN(`knots_copy`.`reason_length`) AS `reason_length`
+	FROM `edges`
+	JOIN `knots_copy`
+	ON `knots_copy`.`id`=`edges`.`cause` GROUP BY `edges`.`impact`) AS `edges_combined`
+	ON `edges_combined`.`impact`=`knots`.`id` SET `knots`.`active`=1, `knots`.`reason_length`=`edges_combined`.`reason_length`+1
+	WHERE NOT `knots`.`and` AND `edges_combined`.`active`=1;
+SET row_count_saved = row_count_saved + ROW_COUNT();
+UPDATE `knots`
+	LEFT JOIN (
+SELECT `edges`.`impact`, MAX(IF(`knots_copy`.`reason_length` IS NULL,1,`edges`.`invert` XOR `knots_copy`.`active`)) AS `active`, MAX(`knots_copy`.`reason_length`) AS `reason_length`
+	FROM `edges`
+	JOIN `knots_copy`
+	ON `knots_copy`.`id`=`edges`.`cause` GROUP BY `edges`.`impact`) AS `edges_combined`
+	ON `edges_combined`.`impact`=`knots`.`id` SET `knots`.`active`=0, `knots`.`reason_length`=IFNULL(`edges_combined`.`reason_length`+1,0)
+	WHERE NOT `knots`.`and` AND IFNULL(`edges_combined`.`active`,0)=0;
+SET row_count_saved = row_count_saved + ROW_COUNT();
+UPDATE `knots`
+	LEFT JOIN (
+SELECT `edges`.`impact`, MIN(IF(`knots_copy`.`reason_length` IS NULL,0,`edges`.`invert` XOR `knots_copy`.`active`)) AS `active`, MAX(`knots_copy`.`reason_length`) AS `reason_length`
+	FROM `edges`
+	JOIN `knots_copy`
+	ON `knots_copy`.`id`=`edges`.`cause` GROUP BY `edges`.`impact`) AS `edges_combined`
+	ON `edges_combined`.`impact`=`knots`.`id` SET `knots`.`active`=1, `knots`.`reason_length`=IFNULL(`edges_combined`.`reason_length`+1,0)
+	WHERE `knots`.`and` AND IFNULL(`edges_combined`.`active`,1)=1;
+SET row_count_saved = row_count_saved + ROW_COUNT();
+UPDATE `knots`
+	JOIN (
+SELECT `edges`.`impact`, MIN(IF(`knots_copy`.`reason_length` IS NULL,1,`edges`.`invert` XOR `knots_copy`.`active`)) AS `active`, MIN(`knots_copy`.`reason_length`) AS `reason_length`
+	FROM `edges`
+	JOIN `knots_copy`
+	ON `knots_copy`.`id`=`edges`.`cause` GROUP BY `edges`.`impact`) AS `edges_combined`
+	ON `edges_combined`.`impact`=`knots`.`id` SET `knots`.`active`=0, `knots`.`reason_length`=`edges_combined`.`reason_length`+1
+	WHERE `knots`.`and` AND `edges_combined`.`active`=0;
+SET row_count_saved = row_count_saved + ROW_COUNT();
+UNTIL (row_count_saved=0) OR EXISTS (
+SELECT 1
+	FROM `knots`
+	WHERE `knots`.`reason_length` IS NOT NULL AND `knots`.`content_id`=`bpir` AND `knots`.`content_type`="bpir")
+END REPEAT;
+UPDATE `knots_copy`
+	JOIN `knots`
+	ON `knots_copy`.`id`=`knots`.`id` SET `knots_copy`.`reason_length`=`knots`.`reason_length`, `knots_copy`.`active`=`knots`.`active`;
+IF NOT EXISTS (
+SELECT 1
+	FROM `knots`
+	WHERE `knots`.`reason_length` IS NOT NULL AND `knots`.`content_id`=`bpir` AND `knots`.`content_type`="bpir") THEN
+SELECT CONCAT("I cannot decide whether ",`binary_packages`.`pkgname`, " can be moved or not.")
+	FROM `binary_packages`
+	JOIN `binary_packages_in_repositories`
+	ON `binary_packages`.`id`=`binary_packages_in_repositories`.`package`
+	WHERE `binary_packages_in_repositories`.`id`=`bpir`;
+LEAVE find_the_culprit;
+END IF;
+IF (
+SELECT `knots`.`active`
+	FROM `knots`
+	WHERE `knots`.`reason_length` IS NOT NULL AND `knots`.`content_id`=`bpir` AND `knots`.`content_type`="bpir") THEN
+SELECT CONCAT(`binary_packages`.`pkgname`, " can be moved.")
+	FROM `binary_packages`
+	JOIN `binary_packages_in_repositories`
+	ON `binary_packages`.`id`=`binary_packages_in_repositories`.`package`
+	WHERE `binary_packages_in_repositories`.`id`=`bpir`;
+LEAVE find_the_culprit;
+END IF;
+SELECT CONCAT(`binary_packages`.`pkgname`, " cannot be moved:")
+	FROM `binary_packages`
+	JOIN `binary_packages_in_repositories`
+	ON `binary_packages`.`id`=`binary_packages_in_repositories`.`package`
+	WHERE `binary_packages_in_repositories`.`id`=`bpir`;
+UPDATE `knots` SET `knots`.`relevant`=(`knots`.`reason_length` IS NOT NULL AND `knots`.`content_id`=`bpir` AND `knots`.`content_type`="bpir");
+REPEAT
+SET row_count_saved=0;
+UPDATE `knots_copy`
+	JOIN `knots`
+	ON `knots_copy`.`id`=`knots`.`id` SET `knots_copy`.`relevant`=`knots`.`relevant`;
+UPDATE `knots`
+	JOIN `edges`
+	ON `knots`.`id`=`edges`.`cause`
+	JOIN `knots_copy`
+	ON `knots_copy`.`id`=`edges`.`impact` SET `knots`.`relevant`=1
+	WHERE `knots_copy`.`relevant` AND NOT `knots`.`relevant` AND NOT (`edges`.`invert` XOR `knots`.`active` XOR `knots_copy`.`active`) AND (`knots_copy`.`and`=`knots_copy`.`active` OR `knots_copy`.`reason_length`=`knots`.`reason_length`+1);
+SET row_count_saved = row_count_saved + ROW_COUNT();
+UNTIL row_count_saved=0
+END REPEAT;
+CREATE TEMPORARY TABLE `knot_names` (`id` BIGINT NOT NULL, `name` VARCHAR (128), `and` BIT, `active` BIT, `relevant` BIT, `reason_length` MEDIUMINT, UNIQUE KEY (`id`));
+CREATE TEMPORARY TABLE `knot_names_copy` (`id` BIGINT NOT NULL, `name` VARCHAR (128), `and` BIT, `active` BIT, `relevant` BIT, `reason_length` MEDIUMINT, UNIQUE KEY (`id`));
+INSERT INTO `knot_names` (`id`,`name`,`reason_length`,`and`,`relevant`,`active`)
+	SELECT `knots`.`id`, `install_targets`.`name`, `knots`.`reason_length`, `knots`.`and`, `knots`.`relevant`, `knots`.`active`
+	FROM `knots`
+	JOIN `install_targets`
+	ON `knots`.`content_id`=`install_targets`.`id` AND `knots`.`content_type`="it";
+INSERT INTO `knot_names` (`id`,`name`,`reason_length`,`and`,`relevant`,`active`)
+	SELECT `knots`.`id`, CONCAT(`r_a`.`name`,"/",`repositories`.`name`,"/",CONCAT(`binary_packages`.`pkgname`,"-",IF(`binary_packages`.`epoch`=0,"",CONCAT(`binary_packages`.`epoch`,":")),`binary_packages`.`pkgver`,"-",`binary_packages`.`pkgrel`,IF(`binary_packages`.`sub_pkgrel_omitted`,"",CONCAT(".",`binary_packages`.`sub_pkgrel`)),"-",`architectures`.`name`,".pkg.tar.xz")) AS `name`, `knots`.`reason_length`, `knots`.`and`, `knots`.`relevant`, `knots`.`active`
+	FROM `knots`
+	JOIN `binary_packages_in_repositories`
+	ON `knots`.`content_id`=`binary_packages_in_repositories`.`id` AND `knots`.`content_type`="bpir"
+	JOIN `binary_packages`
+	ON `binary_packages_in_repositories`.`package`=`binary_packages`.`id`
+	JOIN `repositories`
+	ON `binary_packages_in_repositories`.`repository`=`repositories`.`id`
+	JOIN `architectures` AS `r_a`
+	ON `repositories`.`architecture`=`r_a`.`id`
+	JOIN `architectures`
+	ON `binary_packages`.`architecture`=`architectures`.`id`;
+INSERT INTO `knot_names_copy`
+	SELECT *
+	FROM `knot_names`;
+SELECT CONCAT(IF(`impact`.`and`,"(and) ","(or) "),`impact`.`name`," ",IF(`impact`.`active`,"1","0"),IF(`edges`.`invert`," -NOT-> "," --> "),`cause`.`name`," ",IF(`cause`.`active`,"1","0"))
+	FROM `edges`
+	JOIN `knot_names` AS `cause`
+	ON `cause`.`id`=`edges`.`cause`
+	JOIN `knot_names_copy` AS `impact`
+	ON `impact`.`id`=`edges`.`impact`
+	WHERE `cause`.`relevant` AND `impact`.`relevant`;
+DROP TEMPORARY TABLE `knot_names`;
+END	utf8mb4	utf8mb4_unicode_ci	utf8mb4_unicode_ci
 show_broken_packages_and_dependencies	NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION	CREATE DEFINER=`root`@`localhost` PROCEDURE `show_broken_packages_and_dependencies`()
 BEGIN
 CREATE TEMPORARY TABLE `broken_packages_and_dependencies` (`id` BIGINT, UNIQUE KEY (`id`));