calculate_maximal_moveable_set STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `calculate_maximal_moveable_set`(IN `arch_id` MEDIUMINT,IN `from_stability` MEDIUMINT) BEGIN DECLARE row_count_saved INT DEFAULT 0; DROP TEMPORARY TABLE IF EXISTS `moveable_bpir`; DROP TEMPORARY TABLE IF EXISTS `replaced_bpir`; CREATE TEMPORARY TABLE `replaced_bpir` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`)); CREATE TEMPORARY TABLE `moveable_bpir` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`)); DROP TEMPORARY TABLE IF EXISTS `moveable_bpir_copy`; DROP TEMPORARY TABLE IF EXISTS `replaced_bpir_copy`; CREATE TEMPORARY TABLE `replaced_bpir_copy` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`)); CREATE TEMPORARY TABLE `moveable_bpir_copy` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`)); DROP TEMPORARY TABLE IF EXISTS `moveable_bpir_copy2`; DROP TEMPORARY TABLE IF EXISTS `replaced_bpir_copy2`; CREATE TEMPORARY TABLE `replaced_bpir_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 AND `a_ps`.`commit_time`!="0000-00-00 00:00:00" AND `b_ps`.`commit_time`!="0000-00-00 00:00:00" 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 `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 `binary_packages_in_repositories` AS `b_bpir` ON `b_bp`.`id`=`b_bpir`.`package` JOIN `repositories` AS `b_r` ON `b_bpir`.`repository`=`b_r`.`id` WHERE `a_r`.`stability` = `from_stability` AND `a_r`.`architecture` = `arch_id` AND `b_r`.`stability` = `from_stability` AND `b_r`.`architecture` = `arch_id`; INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`) SELECT `binary_packages_in_repositories`.`id`,`repository_moves`.`to_repository` FROM `binary_packages_in_repositories` JOIN `binary_packages` ON `binary_packages_in_repositories`.`package`=`binary_packages`.`id` JOIN `repositories` ON `binary_packages_in_repositories`.`repository`=`repositories`.`id` JOIN `repository_stabilities` ON `repositories`.`stability`=`repository_stabilities`.`id` JOIN `build_assignments` ON `binary_packages`.`build_assignment`=`build_assignments`.`id` JOIN `package_sources` ON `build_assignments`.`package_source`=`package_sources`.`id` JOIN `upstream_repositories` ON `package_sources`.`upstream_package_repository`=`upstream_repositories`.`id` JOIN `repository_moves` ON `upstream_repositories`.`id`=`repository_moves`.`upstream_package_repository` AND `repository_moves`.`from_repository`=`binary_packages_in_repositories`.`repository` WHERE `repository_stabilities`.`id`=`from_stability` AND `repositories`.`architecture`=`arch_id` AND (`repository_stabilities`.`name`="staging" OR `binary_packages`.`is_tested`) AND NOT `binary_packages`.`has_issues`; INSERT IGNORE INTO `package_blobs` (`ps_a`,`ps_b`) SELECT `package_sources`.`id`,`package_sources`.`id` FROM `package_sources` JOIN `build_assignments` ON `package_sources`.`id`=`build_assignments`.`package_source` JOIN `binary_packages` ON `build_assignments`.`id`=`binary_packages`.`build_assignment` JOIN `binary_packages_in_repositories` ON `binary_packages`.`id`=`binary_packages_in_repositories`.`package` JOIN `repositories` ON `binary_packages_in_repositories`.`repository`=`repositories`.`id` WHERE `repositories`.`stability`=`from_stability` AND `repositories`.`architecture`=`arch_id`; INSERT IGNORE INTO `replaced_bpir` (`id`,`replaced_by`) SELECT `r_bpir`.`id`,`m_bpir`.`id` FROM `moveable_bpir` JOIN `binary_packages_in_repositories` AS `m_bpir` ON `m_bpir`.`id`=`moveable_bpir`.`id` JOIN `binary_packages` AS `m_bp` ON `m_bpir`.`package`=`m_bp`.`id` JOIN `repositories` AS `m_to_r` ON `moveable_bpir`.`to_repository`=`m_to_r`.`id` JOIN `repository_stability_relations` AS `rsr` ON `rsr`.`less_stable`=`m_to_r`.`stability` JOIN `repositories` AS `r_r` ON `rsr`.`more_stable`=`r_r`.`stability` AND `r_r`.`architecture`=`m_to_r`.`architecture` JOIN `binary_packages_in_repositories` AS `r_bpir` ON `r_r`.`id`=`r_bpir`.`repository` JOIN `binary_packages` AS `r_bp` ON `r_bpir`.`package`=`r_bp`.`id` AND `r_bp`.`pkgname`=`m_bp`.`pkgname`; DELETE FROM `replaced_bpir_copy`; INSERT IGNORE INTO `replaced_bpir_copy` SELECT `replaced_bpir`.* FROM `replaced_bpir`; DELETE FROM `replaced_bpir_copy2`; INSERT IGNORE INTO `replaced_bpir_copy2` SELECT `replaced_bpir`.* FROM `replaced_bpir`; DELETE FROM `moveable_bpir_copy`; INSERT IGNORE INTO `moveable_bpir_copy` SELECT `moveable_bpir`.* FROM `moveable_bpir`; DELETE `replaced_bpir` FROM `replaced_bpir` JOIN `binary_packages_in_repositories` AS `not_r_bpir` ON `not_r_bpir`.`id`=`replaced_bpir`.`id` JOIN `binary_packages` AS `not_r_bp` ON `not_r_bpir`.`package`=`not_r_bp`.`id` JOIN `repositories` AS `not_r_r` ON `not_r_bpir`.`repository`=`not_r_r`.`id` JOIN `binary_packages` AS `r_bp` ON `r_bp`.`pkgname`=`not_r_bp`.`pkgname` AND `r_bp`.`id`!=`not_r_bp`.`id` JOIN `binary_packages_in_repositories` AS `r_bpir` ON `r_bp`.`id`=`r_bpir`.`package` JOIN `replaced_bpir_copy` ON `r_bpir`.`id`=`replaced_bpir_copy`.`id` JOIN `repositories` AS `r_r` ON `r_bpir`.`repository`=`r_r`.`id` AND `not_r_r`.`architecture`=`r_r`.`architecture` JOIN `repository_stability_relations` AS `rsr` ON `rsr`.`less_stable`=`r_r`.`stability` AND `rsr`.`more_stable`=`not_r_r`.`stability`; REPEAT SET row_count_saved = 0; DELETE FROM `replaced_bpir_copy`; INSERT IGNORE INTO `replaced_bpir_copy` SELECT `replaced_bpir`.* FROM `replaced_bpir`; DELETE FROM `replaced_bpir_copy2`; INSERT IGNORE INTO `replaced_bpir_copy2` SELECT `replaced_bpir`.* FROM `replaced_bpir`; DELETE FROM `moveable_bpir_copy`; INSERT IGNORE INTO `moveable_bpir_copy` SELECT `moveable_bpir`.* FROM `moveable_bpir`; DELETE `replaced_bpir`,`moveable_bpir` FROM `replaced_bpir` RIGHT JOIN `moveable_bpir` ON `moveable_bpir`.`id`=`replaced_bpir`.`replaced_by` JOIN `binary_packages_in_repositories` ON `binary_packages_in_repositories`.`id`=`moveable_bpir`.`id` JOIN `repositories` AS `target_repositories` ON `moveable_bpir`.`to_repository`=`target_repositories`.`id` JOIN `binary_packages` ON `binary_packages_in_repositories`.`package`=`binary_packages`.`id` JOIN `dependencies` ON `binary_packages`.`id`=`dependencies`.`dependent` JOIN `dependency_types` ON `dependencies`.`dependency_type`=`dependency_types`.`id` AND `dependency_types`.`relevant_for_binary_packages` JOIN `versions` ON `dependencies`.`version`=`versions`.`id` WHERE NOT EXISTS ( SELECT 1 FROM `install_target_providers` JOIN `versions` AS `prov_v` ON `install_target_providers`.`version`=`prov_v`.`id` JOIN `binary_packages` AS `prov_bp` ON `install_target_providers`.`package`=`prov_bp`.`id` JOIN `binary_packages_in_repositories` AS `prov_bpir` ON `prov_bp`.`id`=`prov_bpir`.`package` JOIN `repositories` AS `prov_r` ON `prov_bpir`.`repository`=`prov_r`.`id` 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 `target_repositories`.`architecture`=`prov_r`.`architecture` AND NOT EXISTS ( SELECT 1 FROM `replaced_bpir_copy` WHERE `replaced_bpir_copy`.`id`=`prov_bpir`.`id`) AND((`dependencies`.`version_relation`="<" AND `prov_v`.`order`<`versions`.`order`) OR (`dependencies`.`version_relation`="<=" AND `prov_v`.`order`<=`versions`.`order`) OR (`dependencies`.`version_relation`=">" AND `prov_v`.`order`>`versions`.`order`) OR (`dependencies`.`version_relation`=">=" AND `prov_v`.`order`>=`versions`.`order`) OR (`dependencies`.`version_relation`="=" AND `prov_v`.`order`=`versions`.`order`))) AND NOT EXISTS ( SELECT 1 FROM `install_target_providers` JOIN `versions` AS `prov_v` ON `install_target_providers`.`version`=`prov_v`.`id` JOIN `binary_packages_in_repositories` AS `itp_bpir` ON `install_target_providers`.`package`=`itp_bpir`.`package` JOIN `moveable_bpir_copy` ON `moveable_bpir_copy`.`id`=`itp_bpir`.`id` WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on` AND ((`dependencies`.`version_relation`="<" AND `prov_v`.`order`<`versions`.`order`) OR (`dependencies`.`version_relation`="<=" AND `prov_v`.`order`<=`versions`.`order`) OR (`dependencies`.`version_relation`=">" AND `prov_v`.`order`>`versions`.`order`) OR (`dependencies`.`version_relation`=">=" AND `prov_v`.`order`>=`versions`.`order`) OR (`dependencies`.`version_relation`="=" AND `prov_v`.`order`=`versions`.`order`))); SET row_count_saved = row_count_saved + ROW_COUNT(); DELETE FROM `replaced_bpir_copy`; INSERT IGNORE INTO `replaced_bpir_copy` SELECT `replaced_bpir`.* FROM `replaced_bpir`; DELETE FROM `replaced_bpir_copy2`; INSERT IGNORE INTO `replaced_bpir_copy2` SELECT `replaced_bpir`.* FROM `replaced_bpir`; DELETE FROM `moveable_bpir_copy`; INSERT IGNORE INTO `moveable_bpir_copy` SELECT `moveable_bpir`.* FROM `moveable_bpir`; DELETE `replaced_bpir`,`moveable_bpir` FROM `replaced_bpir` JOIN `moveable_bpir` ON `replaced_bpir`.`replaced_by`=`moveable_bpir`.`id` JOIN `binary_packages_in_repositories` AS `repl_bpir` ON `repl_bpir`.`id`=`replaced_bpir`.`id` AND NOT `repl_bpir`.`is_to_be_deleted` JOIN `binary_packages` AS `repl_bp` ON `repl_bpir`.`package`=`repl_bp`.`id` JOIN `build_assignments` AS `repl_ba` ON `repl_bp`.`build_assignment`=`repl_ba`.`id` AND NOT EXISTS ( SELECT 1 FROM `failed_builds` WHERE `failed_builds`.`date`<=ADDTIME(NOW(),"-1 00:00:00") AND `failed_builds`.`build_assignment`=`repl_ba`.`id`) JOIN `install_target_providers` ON `repl_bpir`.`package`=`install_target_providers`.`package` JOIN `repositories` AS `repl_r` ON `repl_bpir`.`repository`=`repl_r`.`id` JOIN `dependencies` ON `install_target_providers`.`install_target`=`dependencies`.`depending_on` JOIN `versions` AS `install_target_providers_versions` ON `install_target_providers`.`version`=`install_target_providers_versions`.`id` JOIN `versions` AS `dependencies_versions` ON `dependencies`.`version`=`dependencies_versions`.`id` AND ((`dependencies`.`version_relation`="<" AND `install_target_providers_versions`.`order`<`dependencies_versions`.`order`) OR (`dependencies`.`version_relation`="<=" AND `install_target_providers_versions`.`order`<=`dependencies_versions`.`order`) OR (`dependencies`.`version_relation`=">" AND `install_target_providers_versions`.`order`>`dependencies_versions`.`order`) OR (`dependencies`.`version_relation`=">=" AND `install_target_providers_versions`.`order`>=`dependencies_versions`.`order`) OR (`dependencies`.`version_relation`="=" AND `install_target_providers_versions`.`order`=`dependencies_versions`.`order`)) AND NOT EXISTS ( SELECT 1 FROM `replaced_bpir_copy` JOIN `binary_packages_in_repositories` AS `repl_bpir_copy` ON `repl_bpir_copy`.`id`=`replaced_bpir_copy`.`id` JOIN `repositories` AS `repl_r_copy` ON `repl_bpir_copy`.`repository`=`repl_r_copy`.`id` WHERE `repl_bpir_copy`.`package`=`dependencies`.`dependent` AND `repl_r_copy`.`architecture`=`repl_r`.`architecture`) JOIN `dependency_types` ON `dependencies`.`dependency_type`=`dependency_types`.`id` AND `dependency_types`.`relevant_for_binary_packages` JOIN `binary_packages` AS `req_bp` ON `dependencies`.`dependent`=`req_bp`.`id` JOIN `binary_packages_in_repositories` AS `req_bpir` ON `req_bp`.`id`=`req_bpir`.`package` JOIN `repositories` AS `req_r` ON `req_bpir`.`repository`=`req_r`.`id` AND `repl_r`.`architecture`=`req_r`.`architecture` 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 1 FROM `moveable_bpir_copy` JOIN `binary_packages_in_repositories` AS `subst_bpir` ON `subst_bpir`.`id`=`moveable_bpir_copy`.`id` JOIN `install_target_providers` AS `subst_itp` ON `subst_bpir`.`package`=`subst_itp`.`package` JOIN `repositories` AS `subst_r` ON `subst_bpir`.`repository`=`subst_r`.`id` WHERE `subst_itp`.`install_target`=`install_target_providers`.`install_target` AND `subst_r`.`architecture`=`repl_r`.`architecture`) AND NOT EXISTS ( SELECT 1 FROM `binary_packages_in_repositories` AS `subst_bpir` JOIN `install_target_providers` AS `subst_itp` ON `subst_bpir`.`package`=`subst_itp`.`package` JOIN `repositories` AS `subst_r` ON `subst_bpir`.`repository`=`subst_r`.`id` JOIN `repository_stability_relations` AS `subst_rr` ON `subst_rr`.`more_stable`=`subst_r`.`stability` WHERE `subst_rr`.`less_stable`=`repl_r`.`stability` AND `subst_r`.`architecture`=`repl_r`.`architecture` AND NOT EXISTS ( SELECT 1 FROM `replaced_bpir_copy2` WHERE `replaced_bpir_copy2`.`id`=`subst_bpir`.`id`) AND `subst_itp`.`install_target`=`install_target_providers`.`install_target`); SET row_count_saved = row_count_saved + ROW_COUNT(); DELETE FROM `replaced_bpir_copy`; INSERT IGNORE INTO `replaced_bpir_copy` SELECT `replaced_bpir`.* FROM `replaced_bpir`; DELETE FROM `replaced_bpir_copy2`; INSERT IGNORE INTO `replaced_bpir_copy2` SELECT `replaced_bpir`.* FROM `replaced_bpir`; DELETE FROM `moveable_bpir_copy`; INSERT IGNORE INTO `moveable_bpir_copy` SELECT `moveable_bpir`.* FROM `moveable_bpir`; DELETE `replaced_bpir`,`moveable_bpir` FROM `replaced_bpir` RIGHT JOIN `moveable_bpir` ON `replaced_bpir`.`replaced_by`=`moveable_bpir`.`id` JOIN `binary_packages_in_repositories` ON `binary_packages_in_repositories`.`id`=`moveable_bpir`.`id` JOIN `binary_packages` ON `binary_packages_in_repositories`.`package`=`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` AND `bl_ba`.`architecture`=`build_assignments`.`architecture` JOIN `binary_packages` AS `bl_bp` ON `bl_ba`.`id`=`bl_bp`.`build_assignment` JOIN `binary_packages_in_repositories` AS `bl_bpir` ON `bl_bp`.`id`=`bl_bpir`.`package` JOIN `repositories` ON `binary_packages_in_repositories`.`repository`=`repositories`.`id` JOIN `repositories` AS `bl_r` ON `bl_bpir`.`repository`=`bl_r`.`id` AND `repositories`.`architecture`=`bl_r`.`architecture` WHERE NOT EXISTS ( SELECT 1 FROM `moveable_bpir_copy` WHERE `moveable_bpir_copy`.`id`=`bl_bpir`.`id`); SET row_count_saved = row_count_saved + ROW_COUNT(); UNTIL row_count_saved=0 END REPEAT; DELETE FROM `replaced_bpir_copy`; INSERT IGNORE INTO `replaced_bpir_copy` SELECT `replaced_bpir`.* FROM `replaced_bpir`; DELETE FROM `replaced_bpir_copy2`; INSERT IGNORE INTO `replaced_bpir_copy2` SELECT `replaced_bpir`.* FROM `replaced_bpir`; DELETE FROM `moveable_bpir_copy`; INSERT IGNORE INTO `moveable_bpir_copy` SELECT `moveable_bpir`.* FROM `moveable_bpir`; DELETE `replaced_bpir` FROM `replaced_bpir` JOIN `moveable_bpir` ON `replaced_bpir`.`replaced_by`=`moveable_bpir`.`id` JOIN `binary_packages_in_repositories` AS `r_bpir` ON `replaced_bpir`.`id`=`r_bpir`.`id` JOIN `repositories` AS `r_r` ON `r_bpir`.`repository`=`r_r`.`id` JOIN `repositories` AS `m_to_r` ON `m_to_r`.`id`=`moveable_bpir`.`to_repository` WHERE `m_to_r`.`stability`!=`r_r`.`stability`; 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 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,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.",IF(`compressions`.`suffix` IS NULL,"tar.xz",`compressions`.`suffix`))) 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` LEFT JOIN `compressions` ON `binary_packages`.`compression`=`compressions`.`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 allowed_email_actions CREATE TABLE `allowed_email_actions` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `gpg_key` bigint(20) NOT NULL, `action` mediumint(9) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `content` (`gpg_key`,`action`), KEY `action` (`action`), CONSTRAINT `allowed_email_actions_ibfk_1` FOREIGN KEY (`action`) REFERENCES `email_actions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `allowed_email_actions_ibfk_2` FOREIGN KEY (`gpg_key`) REFERENCES `gpg_keys` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci architecture_compatibilities CREATE TABLE `architecture_compatibilities` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `built_for` smallint(6) NOT NULL, `runs_on` smallint(6) NOT NULL, `fully_compatible` bit(1) NOT NULL, `build_slave_compatible` bit(1) NOT NULL DEFAULT b'0', PRIMARY KEY (`id`), UNIQUE KEY `content` (`built_for`,`runs_on`), KEY `architecture_compatibilities_ibfk_2` (`runs_on`), CONSTRAINT `architecture_compatibilities_ibfk_1` FOREIGN KEY (`built_for`) REFERENCES `architectures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `architecture_compatibilities_ibfk_2` FOREIGN KEY (`runs_on`) REFERENCES `architectures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci architectures CREATE TABLE `architectures` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `name` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci binary_packages CREATE TABLE `binary_packages` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `build_assignment` bigint(20) NOT NULL, `epoch` mediumint(9) NOT NULL, `pkgver` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `pkgrel` mediumint(9) NOT NULL, `sub_pkgrel` mediumint(9) NOT NULL, `has_issues` bit(1) NOT NULL, `is_tested` bit(1) NOT NULL, `pkgname` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `architecture` smallint(6) NOT NULL, `sha512sum` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `sub_pkgrel_omitted` bit(1) NOT NULL DEFAULT b'0', `compression` smallint(6) DEFAULT NULL, `signing_key` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `file_name` (`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architecture`,`sub_pkgrel_omitted`), UNIQUE KEY `content` (`build_assignment`,`sub_pkgrel`,`pkgname`,`architecture`,`sub_pkgrel_omitted`), KEY `architecture` (`architecture`), KEY `epoch` (`epoch`), KEY `pkgver` (`pkgver`), KEY `pkgname` (`pkgname`), KEY `compression` (`compression`), KEY `signing_key` (`signing_key`), CONSTRAINT `binary_packages_ibfk_1` FOREIGN KEY (`architecture`) REFERENCES `architectures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `binary_packages_ibfk_2` FOREIGN KEY (`build_assignment`) REFERENCES `build_assignments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `binary_packages_ibfk_3` FOREIGN KEY (`compression`) REFERENCES `compressions` (`id`) ON DELETE SET NULL, CONSTRAINT `binary_packages_ibfk_4` FOREIGN KEY (`signing_key`) REFERENCES `gpg_keys` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci binary_packages_in_repositories CREATE TABLE `binary_packages_in_repositories` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `package` bigint(20) NOT NULL, `repository` mediumint(9) NOT NULL, `last_moved` timestamp NOT NULL DEFAULT current_timestamp(), `is_to_be_deleted` bit(1) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `content` (`package`,`repository`), KEY `binary_packages_in_repositories_ibfk_2` (`repository`), CONSTRAINT `binary_packages_in_repositories_ibfk_1` FOREIGN KEY (`package`) REFERENCES `binary_packages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `binary_packages_in_repositories_ibfk_2` FOREIGN KEY (`repository`) REFERENCES `repositories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci build_assignments CREATE TABLE `build_assignments` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `package_source` bigint(20) NOT NULL, `architecture` smallint(6) NOT NULL, `is_blocked` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `is_broken` bit(1) NOT NULL, `priority` smallint(6) NOT NULL, `is_black_listed` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, `return_date` timestamp NULL DEFAULT NULL, `currently_blocking` mediumint(9) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `content` (`package_source`,`architecture`), KEY `architecture` (`architecture`), CONSTRAINT `build_assignments_ibfk_1` FOREIGN KEY (`package_source`) REFERENCES `package_sources` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `build_assignments_ibfk_2` FOREIGN KEY (`architecture`) REFERENCES `architectures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci build_dependency_loops CREATE TABLE `build_dependency_loops` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `loop` mediumint(9) NOT NULL, `build_assignment` bigint(20) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `content` (`loop`,`build_assignment`), KEY `build_assignment` (`build_assignment`), CONSTRAINT `build_dependency_loops_ibfk_1` FOREIGN KEY (`build_assignment`) REFERENCES `build_assignments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci build_slaves CREATE TABLE `build_slaves` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, `currently_building` bigint(20) DEFAULT NULL, `logged_lines` bigint(20) DEFAULT NULL, `last_action` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `ssh_key` bigint(20) NOT NULL, `trials` mediumint(9) DEFAULT NULL, `is_sane` bit(1) NOT NULL, `access_allowed` bit(1) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `currently_building` (`currently_building`), KEY `ssh_key` (`ssh_key`), CONSTRAINT `build_slaves_ibfk_1` FOREIGN KEY (`currently_building`) REFERENCES `build_assignments` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `build_slaves_ibfk_2` FOREIGN KEY (`ssh_key`) REFERENCES `ssh_keys` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci command_log CREATE TABLE `command_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `date` timestamp NOT NULL DEFAULT current_timestamp(), `command` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `parameters` text COLLATE utf8mb4_unicode_ci NOT NULL, `shell` bit(1) NOT NULL, PRIMARY KEY (`id`), KEY `date` (`date`), KEY `command` (`command`), KEY `shell` (`shell`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci compressions CREATE TABLE `compressions` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `suffix` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `suffix` (`suffix`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci dependencies CREATE TABLE `dependencies` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `dependent` bigint(20) NOT NULL, `depending_on` bigint(20) NOT NULL, `dependency_type` smallint(6) NOT NULL, `version` bigint(20) NOT NULL, `version_relation` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `content` (`dependent`,`depending_on`,`dependency_type`,`version`,`version_relation`), KEY `dependencies_ibfk_2` (`depending_on`), KEY `dependencies_ibfk_3` (`dependency_type`), KEY `version` (`version`), CONSTRAINT `dependencies_ibfk_1` FOREIGN KEY (`dependent`) REFERENCES `binary_packages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `dependencies_ibfk_2` FOREIGN KEY (`depending_on`) REFERENCES `install_targets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `dependencies_ibfk_3` FOREIGN KEY (`dependency_type`) REFERENCES `dependency_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `dependencies_ibfk_4` FOREIGN KEY (`version`) REFERENCES `versions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci dependency_types CREATE TABLE `dependency_types` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, `relevant_for_building` bit(1) NOT NULL, `relevant_for_binary_packages` bit(1) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci email_actions CREATE TABLE `email_actions` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci email_log CREATE TABLE `email_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `date` timestamp NOT NULL DEFAULT current_timestamp(), `success` bit(1) NOT NULL, `action` mediumint(9) DEFAULT NULL, `count` mediumint(9) DEFAULT NULL, `gpg_key` bigint(20) DEFAULT NULL, `comment` text COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `action` (`action`), KEY `gpg_key` (`gpg_key`), CONSTRAINT `email_log_ibfk_1` FOREIGN KEY (`action`) REFERENCES `email_actions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `email_log_ibfk_2` FOREIGN KEY (`gpg_key`) REFERENCES `gpg_keys` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci fail_reasons CREATE TABLE `fail_reasons` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, `identifier` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL, `severity` smallint(6) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci failed_builds CREATE TABLE `failed_builds` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `build_slave` mediumint(9) NOT NULL, `build_assignment` bigint(20) NOT NULL, `date` timestamp NOT NULL DEFAULT current_timestamp(), `reason` smallint(6) NOT NULL, `log_file` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL, `log_file_exists` bit(1) NOT NULL, PRIMARY KEY (`id`), KEY `build_slave` (`build_slave`), KEY `build_assignment` (`build_assignment`), KEY `reason` (`reason`), CONSTRAINT `failed_builds_ibfk_1` FOREIGN KEY (`build_slave`) REFERENCES `build_slaves` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `failed_builds_ibfk_2` FOREIGN KEY (`build_assignment`) REFERENCES `build_assignments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `failed_builds_ibfk_3` FOREIGN KEY (`reason`) REFERENCES `fail_reasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci git_repositories CREATE TABLE `git_repositories` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `url` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL, `directory` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL, `head` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), UNIQUE KEY `url` (`url`), UNIQUE KEY `directory` (`directory`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci gpg_keys CREATE TABLE `gpg_keys` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `owner` mediumint(9) NOT NULL, `fingerprint` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL, `public_key` text COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `fingerprint` (`fingerprint`), KEY `owner` (`owner`), CONSTRAINT `gpg_keys_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `persons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci install_target_providers CREATE TABLE `install_target_providers` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `package` bigint(20) NOT NULL, `install_target` bigint(20) NOT NULL, `version` bigint(20) NOT NULL, `install_target_is_group` bit(1) NOT NULL DEFAULT b'0', PRIMARY KEY (`id`), UNIQUE KEY `content` (`package`,`install_target`,`version`,`install_target_is_group`), KEY `install_target_providers_ibfk_2` (`install_target`), KEY `version` (`version`), CONSTRAINT `install_target_providers_ibfk_1` FOREIGN KEY (`package`) REFERENCES `binary_packages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `install_target_providers_ibfk_2` FOREIGN KEY (`install_target`) REFERENCES `install_targets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `install_target_providers_ibfk_3` FOREIGN KEY (`version`) REFERENCES `versions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci install_targets CREATE TABLE `install_targets` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci mirror_statuses CREATE TABLE `mirror_statuses` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `protocol` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL, `url` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL, `country` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `country_code` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL, `last_sync` bigint(20) NOT NULL, `start` float NOT NULL, `stop` float NOT NULL, `isos` bit(1) NOT NULL, `ipv4` bit(1) NOT NULL, `ipv6` bit(1) NOT NULL, `active` bit(1) NOT NULL, PRIMARY KEY (`id`), KEY `url` (`url`), KEY `start` (`start`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci package_sources CREATE TABLE `package_sources` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `pkgbase` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `git_revision` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL, `mod_git_revision` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL, `upstream_package_repository` smallint(6) NOT NULL, `uses_upstream` bit(1) NOT NULL, `uses_modification` bit(1) NOT NULL, `commit_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `upstream_flag_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), UNIQUE KEY `content` (`pkgbase`,`git_revision`,`mod_git_revision`), KEY `upstream_package_repository` (`upstream_package_repository`), CONSTRAINT `package_sources_ibfk_1` FOREIGN KEY (`upstream_package_repository`) REFERENCES `upstream_repositories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci persons CREATE TABLE `persons` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci repositories CREATE TABLE `repositories` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `stability` mediumint(9) NOT NULL, `is_on_master_mirror` bit(1) NOT NULL, `architecture` smallint(6) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name_arch` (`name`,`architecture`), KEY `stability` (`stability`), KEY `architecture` (`architecture`), CONSTRAINT `repositories_ibfk_1` FOREIGN KEY (`stability`) REFERENCES `repository_stabilities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `repositories_ibfk_2` FOREIGN KEY (`architecture`) REFERENCES `architectures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci repository_moves CREATE TABLE `repository_moves` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `from_repository` mediumint(9) NOT NULL, `to_repository` mediumint(9) NOT NULL, `upstream_package_repository` smallint(6) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `content` (`from_repository`,`to_repository`,`upstream_package_repository`), KEY `to_repository` (`to_repository`), KEY `upstream_package_repository` (`upstream_package_repository`), CONSTRAINT `repository_moves_ibfk_1` FOREIGN KEY (`from_repository`) REFERENCES `repositories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `repository_moves_ibfk_2` FOREIGN KEY (`to_repository`) REFERENCES `repositories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `repository_moves_ibfk_3` FOREIGN KEY (`upstream_package_repository`) REFERENCES `upstream_repositories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci repository_stabilities CREATE TABLE `repository_stabilities` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, `bugtracker_category` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci repository_stability_relations CREATE TABLE `repository_stability_relations` ( `id` mediumint(9) NOT NULL AUTO_INCREMENT, `more_stable` mediumint(9) NOT NULL, `less_stable` mediumint(9) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `content` (`more_stable`,`less_stable`), KEY `less_stable` (`less_stable`), CONSTRAINT `repository_stability_relations_ibfk_1` FOREIGN KEY (`more_stable`) REFERENCES `repository_stabilities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `repository_stability_relations_ibfk_2` FOREIGN KEY (`less_stable`) REFERENCES `repository_stabilities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ssh_keys CREATE TABLE `ssh_keys` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `owner` mediumint(9) NOT NULL, `fingerprint` text COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `owner` (`owner`), CONSTRAINT `ssh_keys_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `persons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ssh_log CREATE TABLE `ssh_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `date` timestamp NOT NULL DEFAULT current_timestamp(), `build_slave` mediumint(9) DEFAULT NULL, `action` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL, `parameters` text COLLATE utf8mb4_unicode_ci NOT NULL, `exit_code` mediumint(9) DEFAULT NULL, `duration` mediumint(9) DEFAULT NULL, PRIMARY KEY (`id`), KEY `exit_code` (`exit_code`), KEY `ssh_log_build_slave_date_idx` (`build_slave`,`date`), KEY `ssh_log_date_idx` (`date`), CONSTRAINT `ssh_log_ibfk_1` FOREIGN KEY (`build_slave`) REFERENCES `build_slaves` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci statistics CREATE TABLE `statistics` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `date` timestamp NOT NULL DEFAULT current_timestamp(), `stable_packages_count` mediumint(9) NOT NULL, `pending_tasks_count` mediumint(9) NOT NULL, `pending_packages_count` mediumint(9) NOT NULL, `staging_packages_count` mediumint(9) NOT NULL, `testing_packages_count` mediumint(9) NOT NULL, `tested_packages_count` mediumint(9) NOT NULL, `broken_tasks_count` mediumint(9) NOT NULL, `dependency_loops_count` mediumint(9) NOT NULL, `dependency_looped_tasks_count` mediumint(9) NOT NULL, `locked_tasks_count` mediumint(9) NOT NULL, `blocked_tasks_count` mediumint(9) NOT NULL, `next_tasks_count` mediumint(9) NOT NULL, `architecture` smallint(6) NOT NULL DEFAULT 1, PRIMARY KEY (`id`), UNIQUE KEY `date_architecture` (`date`,`architecture`), KEY `architecture` (`architecture`), CONSTRAINT `statistics_ibfk_1` FOREIGN KEY (`architecture`) REFERENCES `architectures` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci todo_links CREATE TABLE `todo_links` ( `dependent` bigint(20) NOT NULL, `depending_on` bigint(20) NOT NULL, UNIQUE KEY `content` (`dependent`,`depending_on`), KEY `depending_on` (`depending_on`), CONSTRAINT `todo_links_ibfk_1` FOREIGN KEY (`dependent`) REFERENCES `todos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `todo_links_ibfk_2` FOREIGN KEY (`depending_on`) REFERENCES `todos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci todos CREATE TABLE `todos` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `file` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `line` mediumint(9) NOT NULL, `description` text COLLATE utf8mb4_unicode_ci NOT NULL, `importance` smallint(6) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci toolchain_order CREATE TABLE `toolchain_order` ( `number` smallint(6) NOT NULL, `pkgbase` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `requires_all_dependencies_built` bit(1) NOT NULL, PRIMARY KEY (`number`), UNIQUE KEY `content` (`pkgbase`,`requires_all_dependencies_built`), KEY `pkgbase` (`pkgbase`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci upstream_packages CREATE TABLE `upstream_packages` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `pkgname` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `epoch` mediumint(9) NOT NULL, `pkgver` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `pkgrel` mediumint(9) NOT NULL, `architecture` varchar(6) COLLATE utf8mb4_unicode_ci NOT NULL, `repository` smallint(6) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `content` (`pkgname`,`epoch`,`pkgver`,`pkgrel`), UNIQUE KEY `location` (`pkgname`,`repository`), KEY `architecture` (`architecture`), KEY `upstream_packages_ibfk_1` (`repository`), CONSTRAINT `upstream_packages_ibfk_1` FOREIGN KEY (`repository`) REFERENCES `upstream_repositories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci upstream_repositories CREATE TABLE `upstream_repositories` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, `git_repository` smallint(6) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `content` (`name`,`git_repository`), KEY `git_repository` (`git_repository`), CONSTRAINT `upstream_repositories_ibfk_1` FOREIGN KEY (`git_repository`) REFERENCES `git_repositories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci versions CREATE TABLE `versions` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `order` bigint(20) DEFAULT NULL, `epoch` mediumint(9) NOT NULL, `version` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `content` (`epoch`,`version`), KEY `order` (`order`), KEY `epoch` (`epoch`), KEY `pkgver` (`version`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci INSERT INTO `architectures` VALUES (1,'any'), (3,'i486'), (2,'i686'), (4,'pentium4'); INSERT INTO `architecture_compatibilities` VALUES (1,1,1,'1','1'), (2,2,2,'1','1'), (3,1,2,'1','1'), (4,3,3,'1','1'), (5,1,3,'1','\0'), (6,3,2,'\0','\0'), (7,1,4,'1','1'), (8,4,4,'1','1'), (9,2,4,'\0','1'), (10,3,4,'\0','\0'); INSERT INTO `compressions` VALUES (1,'tar'), (3,'tar.bz2'), (2,'tar.gz'), (5,'tar.lzo'), (6,'tar.lzr'), (4,'tar.xz'), (7,'tar.Z'), (8,'tar.zst'); INSERT INTO `dependency_types` VALUES (2,'run','1','1'), (3,'check','\0','\0'), (4,'link','\0','1'), (5,'make','1','\0'); INSERT INTO `email_actions` VALUES (2,'block'), (5,'copy-to-build-support'), (7,'delete'), (8,'delete-from-build-support'), (6,'prioritize'), (4,'schedule'), (1,'stabilize'), (3,'unblock'); INSERT INTO `fail_reasons` VALUES (1,'build()','==> ERROR: A failure occurred in build()\\.',1), (2,'source','==> ERROR: Could not download sources\\.\\|==> ERROR: Failure while downloading .*',2), (3,'package-cache','.*error: failed to commit transaction (invalid or corrupted package (PGP signature))',3), (4,'prepare()','==> ERROR: A failure occurred in prepare()\\.',3), (5,'package()','==> ERROR: A failure occurred in package\\(_\\S\\+\\)\\?()\\.',4), (6,'check()','==> ERROR: A failure occurred in check()\\.',5), (7,'dependencies','==> ERROR: \'pacman\' failed to install missing dependencies\\.',2), (8,'run-as-root','==> ERROR: Running makepkg as root is not allowed as it can cause permanent,',2), (9,'unknown','.*',101), (10,'pacman','==> ERROR: Failed to install all packages',6), (11,'unclean-chroot','==> ERROR: \'.*\' does not appear to be an Arch chroot\\.\\|==> ERROR: Working directory \'.*\' already exists',6), (12,'vcs-source','==> ERROR: Failure while creating working copy of .*',6), (14,'pacman-sync','error: failed to synchronize all databases',6), (15,'broken-slave:missing-programs','sudo: .*: command not found',7), (16,'broken-slave:systemd-hangup','Failed to attach [0-9]\\+ to compat systemd cgroup /\\S\\+: No such file or directory',7), (17,'build():broken-haskell-source','.*mismatched interface file versions (.*)',0), (18,'out-of-space','.*No space left on device',0), (19,'validity','==> ERROR: One or more files did not pass the validity check!',2), (20,'pgp-verify','==> ERROR: One or more PGP signatures could not be verified!',2), (21,'checksum-verify','==> ERROR: Integrity checks (.*) differ in size from the source array.',2), (22,'zstd-memory-exhaustion','zstd: error 11 : Allocation error : not enough memory',1), (23,'broken-devtools:sudo','sudo: error in event loop: Operation not permitted',2); INSERT INTO `repositories` VALUES (1,'core',1,'1',2), (2,'extra',1,'1',2), (3,'community',1,'1',2), (4,'build-support',4,'1',2), (5,'testing',2,'1',2), (6,'community-testing',2,'1',2), (7,'staging',3,'1',2), (8,'community-staging',3,'1',2), (9,'build-list',5,'\0',1), (10,'deletion-list',6,'\0',1), (11,'to-be-decided',7,'\0',1), (12,'gnome-unstable',8,'1',2), (13,'kde-unstable',8,'1',2), (14,'deletion-list',6,'\0',2), (15,'core',1,'1',3), (16,'extra',1,'1',3), (17,'community',1,'1',3), (18,'build-support',4,'1',3), (19,'testing',2,'1',3), (20,'community-testing',2,'1',3), (21,'staging',3,'1',3), (22,'community-staging',3,'1',3), (23,'gnome-unstable',8,'1',3), (24,'kde-unstable',8,'1',3), (25,'deletion-list',6,'\0',3), (26,'core',1,'1',4), (27,'extra',1,'1',4), (28,'community',1,'1',4), (29,'build-support',4,'1',4), (30,'testing',2,'1',4), (31,'community-testing',2,'1',4), (32,'staging',3,'1',4), (33,'community-staging',3,'1',4), (34,'gnome-unstable',8,'1',4), (35,'kde-unstable',8,'1',4), (36,'deletion-list',6,'\0',4); INSERT INTO `repository_moves` VALUES (5,5,1,1), (6,5,2,2), (1,6,3,3), (2,6,3,4), (8,7,5,1), (9,7,5,2), (3,8,6,3), (4,8,6,4), (51,9,4,5), (10,9,7,1), (11,9,7,2), (12,9,8,3), (13,9,8,4), (52,9,18,5), (29,9,21,1), (30,9,21,2), (31,9,22,3), (32,9,22,4), (53,9,29,5), (33,9,32,1), (34,9,32,2), (35,9,33,3), (36,9,33,4), (18,19,15,1), (19,19,16,2), (14,20,17,3), (15,20,17,4), (20,21,19,1), (21,21,19,2), (16,22,20,3), (17,22,20,4), (40,30,26,1), (41,30,27,2), (42,31,28,3), (43,31,28,4), (44,32,30,1), (45,32,30,2), (46,33,31,3), (47,33,31,4); INSERT INTO `repository_stabilities` VALUES (1,'stable','Packages: Stable'), (2,'testing','Packages: Testing'), (3,'staging',NULL), (4,'standalone',NULL), (5,'unbuilt','Packages: Build-list'), (6,'forbidden',NULL), (7,'virtual',NULL), (8,'unstable',NULL); INSERT INTO `repository_stability_relations` VALUES (16,1,1), (7,1,2), (5,1,3), (24,1,4), (8,1,5), (1,1,6), (17,2,2), (6,2,3), (26,2,4), (10,2,5), (3,2,6), (18,3,3), (25,3,4), (9,3,5), (2,3,6), (23,4,4), (4,5,6); INSERT INTO `toolchain_order` VALUES (2,'binutils','\0'), (4,'binutils','1'), (3,'gcc','1'), (1,'glibc','\0'), (5,'glibc','1'), (0,'linux-api-headers','\0'); INSERT INTO `upstream_repositories` VALUES (5,'build-support',3), (4,'community',2), (1,'core',1), (2,'extra',1), (3,'multilib',2);