path: root/misc/database-layout.dump

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`
	WHERE NOT EXISTS (
SELECT 1
	FROM `install_target_providers`
	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 NOT EXISTS (
SELECT 1
	FROM `install_target_providers`
	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`);
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 `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`
	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.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
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,
	 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`),
	 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
) 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);
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),
  (10,9,7,1),
  (11,9,7,2),
  (12,9,8,3),
  (13,9,8,4),
  (29,9,21,1),
  (30,9,21,2),
  (31,9,22,3),
  (32,9,22,4),
  (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
  (4,'community',2),
  (1,'core',1),
  (2,'extra',1),
  (3,'multilib',2);