-- called by /home/master/builder/bin/sanity-check
DELETE `binary_packages_in_repositories` FROM `binary_packages` JOIN `binary_packages_in_repositories` ON `binary_packages`.`id`=`binary_packages_in_repositories`.`package` WHERE `binary_packages_in_repositories`.`repository`=11;
DELETE `binary_packages` FROM `binary_packages` WHERE `binary_packages`.`sha512sum` IS NULL AND NOT EXISTS (SELECT 1 FROM `binary_packages_in_repositories` WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`);
DELETE FROM `build_assignments` WHERE NOT EXISTS (SELECT 1 FROM `binary_packages` WHERE `binary_packages`.`build_assignment`=`build_assignments`.`id`);
DELETE `failed_builds` FROM `failed_builds`  JOIN `build_assignments` ON `failed_builds`.`build_assignment`=`build_assignments`.`id`WHERE NOT `build_assignments`.`is_broken`;
DELETE FROM `package_sources` WHERE NOT EXISTS (SELECT 1 FROM `build_assignments` WHERE `build_assignments`.`package_source`=`package_sources`.`id`);
UPDATE `binary_packages_in_repositories` JOIN `binary_packages` ON `binary_packages_in_repositories`.`package`=`binary_packages`.`id` AND `binary_packages_in_repositories`.`repository`=9 RIGHT JOIN `build_slaves` ON `binary_packages`.`build_assignment`=`build_slaves`.`currently_building` SET `build_slaves`.`currently_building`=NULL WHERE `build_slaves`.`currently_building` IS NOT NULL AND `binary_packages_in_repositories`.`id` IS NULL;
UPDATE `build_slaves` SET `build_slaves`.`currently_building`=NULL WHERE `build_slaves`.`currently_building` IS NOT NULL AND TIMEDIFF(NOW(),(SELECT MAX(`ssh_log`.`date`) FROM `ssh_log` WHERE `ssh_log`.`build_slave`=`build_slaves`.`id`)) > "1:00:00";
UPDATE `build_slaves` SET `build_slaves`.`last_action`=NULL, `build_slaves`.`logged_lines`=NULL, `build_slaves`.`trials`=NULL WHERE `build_slaves`.`currently_building` IS NULL AND (`build_slaves`.`last_action` IS NOT NULL OR `build_slaves`.`logged_lines` IS NOT NULL OR `build_slaves`.`trials` IS NOT NULL);
CREATE TEMPORARY TABLE `ps`(`old_p` SMALLINT, `new_p` SMALLINT, UNIQUE KEY `old_p`(`old_p`));
CREATE TEMPORARY TABLE `ps_2`(`old_p` SMALLINT, `new_p` SMALLINT, UNIQUE KEY `old_p`(`old_p`));
CREATE TEMPORARY TABLE `ps_3`(`old_p` SMALLINT, `new_p` SMALLINT, UNIQUE KEY `old_p`(`old_p`));
INSERT INTO `ps`(`old_p`) VALUES (0);
INSERT IGNORE INTO `ps`(`old_p`) SELECT `build_assignments`.`priority` FROM `build_assignments`;
INSERT INTO `ps_2`(`old_p`) SELECT `ps`.`old_p` FROM `ps`;
INSERT INTO `ps_3`(`old_p`) SELECT `ps`.`old_p` FROM `ps`;
UPDATE `ps` LEFT JOIN (SELECT `ps_2`.`old_p` AS `limit_p`,COUNT(`ps_3`.`old_p`) AS `count_p` FROM `ps_2` JOIN `ps_3` ON `ps_3`.`old_p`<`ps_2`.`old_p` GROUP BY `ps_2`.`old_p`) AS `ps_q` ON `ps_q`.`limit_p`=`ps`.`old_p` SET `ps`.`new_p`=IFNULL(`ps_q`.`count_p`,0);
UPDATE `build_assignments` JOIN `ps` ON `build_assignments`.`priority`=`ps`.`old_p` SET `build_assignments`.`priority`=`ps`.`new_p`;
DROP TEMPORARY TABLE `ps`;
DROP TEMPORARY TABLE `ps_2`;
DROP TEMPORARY TABLE `ps_3`;