From 1948e0ddf7ede863f299d2e220011513a2535dee Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Fri, 27 Jul 2018 12:00:03 +0200 Subject: misc/database-layout.dump: new stored functions: find_the_culprit and blacklist_packages --- misc/database-layout.dump | 194 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 194 insertions(+) (limited to 'misc/database-layout.dump') 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`)); -- cgit v1.2.3-54-g00ecf