summaryrefslogtreecommitdiff
path: root/misc/database-layout.dump
diff options
context:
space:
mode:
authorErich Eckner <git@eckner.net>2018-07-27 12:00:03 +0200
committerErich Eckner <git@eckner.net>2018-07-27 12:00:03 +0200
commit1948e0ddf7ede863f299d2e220011513a2535dee (patch)
tree8941a9316aa58d4a6b83024b740e43289b4d8932 /misc/database-layout.dump
parenta3f8f41f231807860fb4d499bc088c5c6c069c8b (diff)
downloadbuilder-1948e0ddf7ede863f299d2e220011513a2535dee.tar.xz
misc/database-layout.dump: new stored functions: find_the_culprit and blacklist_packages
Diffstat (limited to 'misc/database-layout.dump')
-rw-r--r--misc/database-layout.dump194
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`));