From ffabdf532ed759f78860aedfa6b0a2b7c6d9a0ac Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Fri, 4 May 2018 13:50:19 +0200 Subject: bin/bootstrap-mysql: remove unnecessary stuff --- bin/bootstrap-mysql | 1250 ++++++++++++++------------------------------------- 1 file changed, 345 insertions(+), 905 deletions(-) diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql index 35f5398..265bbe5 100755 --- a/bin/bootstrap-mysql +++ b/bin/bootstrap-mysql @@ -3,13 +3,6 @@ # shellcheck source=../conf/default.conf . "${0%/*}/../conf/default.conf" -# TODO: this should become some sort of mysql dump to bootstrap the -# tables and stored functions only - when the database is gone, we're -# pretty much screwed anyway :-D - -tmp_dir=$(mktemp -d 'tmp.bootstrap-mysql.XXXXXXXXXX' --tmpdir) -trap 'rm -rf --one-file-system "${tmp_dir}"' EXIT - exec 9> "${build_list_lock_file}" if ! flock -n 9; then >&2 echo 'Cannot get build-list lock.' @@ -26,914 +19,361 @@ if ! flock -s -n 7; then exit 1 fi -for dir in $(ls_master_mirror 'i686'); do - ls_master_mirror "i686/${dir}" | \ - sed ' - /\.pkg\.tar\.xz$/!d - s|^|'"${dir}"' | - ' -done | \ - sort -k2,2 > \ - "${tmp_dir}/master-mirror-listing" - -if [ ! "$1" = 'slim' ]; then - tables=$( - printf '%s\n' \ - 'dependency_types SMALLINT' \ - ' name VARCHAR(32)' \ - ' relevant_for_building BIT' \ - ' relevant_for_binary_packages BIT' \ - ' UNIQUE name' \ - 'dependencies BIGINT' \ - ' dependent BIGINT :binary_packages' \ - ' depending_on BIGINT :install_targets' \ - ' dependency_type SMALLINT :dependency_types' \ - ' UNIQUE content dependent depending_on dependency_type' \ - 'install_targets BIGINT' \ - ' name VARCHAR(64)' \ - ' UNIQUE name' \ - 'install_target_providers BIGINT' \ - ' package BIGINT :binary_packages' \ - ' install_target BIGINT :install_targets' \ - ' UNIQUE content package install_target' \ - 'binary_packages BIGINT' \ - ' build_assignment BIGINT :build_assignments' \ - ' repository MEDIUMINT :repositories' \ - ' epoch MEDIUMINT' \ - ' pkgver VARCHAR(64)' \ - ' pkgrel MEDIUMINT' \ - ' sub_pkgrel MEDIUMINT' \ - ' has_issues BIT' \ - ' is_tested BIT' \ - ' is_to_be_deleted BIT' \ - ' pkgname VARCHAR(64)' \ - ' architecture SMALLINT :architectures' \ - ' sha512sum NULL' \ - ' UNIQUE content build_assignment sub_pkgrel pkgname architecture repository' \ - ' UNIQUE file_name pkgname epoch pkgver pkgrel sub_pkgrel architecture repository' \ - 'repositories MEDIUMINT' \ - ' name VARCHAR(64)' \ - ' stability MEDIUMINT :repository_stabilities' \ - ' is_on_master_mirror BIT' \ - ' UNIQUE name' \ - 'repository_stabilities MEDIUMINT' \ - ' name VARCHAR(32)' \ - ' bugtracker_category VARCHAR(64) NULL' \ - ' UNIQUE name' \ - 'architectures SMALLINT' \ - ' name VARCHAR(16)' \ - ' UNIQUE name' \ - 'build_assignments BIGINT' \ - ' package_source BIGINT :package_sources' \ - ' architecture SMALLINT :architectures' \ - ' is_blocked VARCHAR(128) NULL' \ - ' is_black_listed TEXT NULL' \ - ' is_broken BIT' \ - ' priority SMALLINT' \ - ' UNIQUE content package_source architecture' \ - 'build_dependency_loops BIGINT' \ - ' loop MEDIUMINT' \ - ' build_assignment BIGINT :build_assignments' \ - ' UNIQUE content loop build_assignment' \ - 'build_slaves MEDIUMINT' \ - ' name VARCHAR(32)' \ - ' ssh_key VARCHAR(1024)' \ - ' operator VARCHAR(32)' \ - ' currently_building BIGINT NULL :build_assignments' \ - ' last_connection DATETIME' \ - ' logged_lines BIGINT NULL' \ - ' last_action VARCHAR(32) NULL' \ - ' UNIQUE name' \ - 'package_sources BIGINT' \ - ' pkgbase VARCHAR(64)' \ - ' git_revision VARCHAR(40)' \ - ' mod_git_revision VARCHAR(40)' \ - ' upstream_package_repository SMALLINT :upstream_repositories' \ - ' uses_upstream BIT' \ - ' uses_modification BIT' \ - ' commit_time DATETIME' \ - ' UNIQUE content pkgbase git_revision mod_git_revision' \ - 'upstream_repositories SMALLINT' \ - ' name VARCHAR(64)' \ - ' git_repository SMALLINT :git_repositories' \ - ' UNIQUE content name git_repository' \ - 'git_repositories SMALLINT' \ - ' name VARCHAR(64)' \ - ' url VARCHAR(128)' \ - ' directory VARCHAR(128)' \ - ' head VARCHAR(40)' \ - ' UNIQUE name' \ - ' UNIQUE url' \ - ' UNIQUE directory' \ - 'fail_reasons SMALLINT' \ - ' name VARCHAR(32)' \ - ' identifier VARCHAR(64)' \ - ' severity SMALLINT' \ - ' UNIQUE name' \ - 'failed_builds MEDIUMINT' \ - ' build_slave MEDIUMINT :build_slaves' \ - ' build_assignment BIGINT :build_assignments' \ - ' date DATETIME' \ - ' reason SMALLINT :fail_reasons' \ - ' log_file VARCHAR(512)' \ - 'todos MEDIUMINT' \ - ' file VARCHAR(64)' \ - ' line MEDIUMINT' \ - ' description VARCHAR(512)' \ - 'todo_links MEDIUMINT' \ - ' dependent MEDIUMINT' \ - ' depending_on MEDIUMINT' \ - 'repository_stability_relations MEDIUMINT' \ - ' more_stable MEDIUMINT :repository_stabilities' \ - ' less_stable MEDIUMINT :repository_stabilities' \ - 'repository_moves MEDIUMINT' \ - ' from_repository MEDIUMINT :repositories' \ - ' to_repository MEDIUMINT :repositories' \ - ' upstream_package_repository SMALLINT :upstream_repositories' \ - ' UNIQUE source from_repository upstream_package_repository' \ - 'statistics BIGINT' \ - ' date DATETIME' \ - ' stable_packages_count MEDIUMINT' \ - ' pending_tasks_count MEDIUMINT' \ - ' pending_packages_count MEDIUMINT' \ - ' staging_packages_count MEDIUMINT' \ - ' testing_packages_count MEDIUMINT' \ - ' tested_packages_count MEDIUMINT' \ - ' broken_tasks_count MEDIUMINT' \ - ' dependency_loops_count MEDIUMINT' \ - ' dependency_looped_tasks_count MEDIUMINT' \ - ' locked_tasks_count MEDIUMINT' \ - ' blocked_tasks_count MEDIUMINT' \ - ' next_tasks_count MEDIUMINT' \ - ' UNIQUE date' - ) - - # shellcheck disable=SC2016 - { - printf '%s\n' \ - '/*!40014 SET UNIQUE_CHECKS=0 */;' \ - '/*!40014 SET FOREIGN_KEY_CHECKS=0 */;' - for turn in 'drop' 'create' 'link'; do - echo "${tables}" | \ - sed -n ' - /^\S/p - ' | \ - while read -r table size; do - case "${turn}" in - 'drop') - printf 'DROP TABLE IF EXISTS `%s`;\n' "${table}" - ;; - 'create'|'link') - if [ "${turn}" = 'create' ]; then - printf 'CREATE TABLE `%s` (\n' "${table}" - printf ' `id` %s NOT NULL AUTO_INCREMENT,\n' "${size}" - fi - echo "${tables}" | \ - sed -n ' - s/^'"$(str_to_regex "${table} ${size}")"'$// - T - :a - $!N - s/\n\S.*$// - $!Ta - s/^\n// - p - ' | \ - while read -r column type rest; do - case "${column}" in - 'UNIQUE') - if [ ! "${turn}" = 'create' ]; then - continue - fi - if [ -z "${rest}" ]; then - rest="${type}" - fi - printf ' UNIQUE KEY `%s` (' "${type}" - # shellcheck disable=SC2086 - printf '`%s`,' ${rest} | \ - sed 's|,$||' - printf ')' - ;; - *) - if [ "${turn}" = 'create' ]; then - printf ' `%s` %s' "${column}" "${type}" - if ! echo "${rest}" | \ - grep -qwF 'NULL'; then - printf ' NOT NULL' - fi - else - echo "${rest}" | \ - tr ' ' '\n' | \ - sed -n 's/^://;T;p' | \ - while read -r link; do - printf 'ALTER TABLE `%s` ADD FOREIGN KEY (`%s`) REFERENCES `%s` (`id`) ON UPDATE CASCADE ON DELETE ' \ - "${table}" \ - "${column}" \ - "${link}" - if echo "${rest}" | \ - grep -qwF 'NULL'; then - printf 'SET NULL' - else - printf 'CASCADE' - fi - printf ';\n' - done - fi - ;; - esac - if [ "${turn}" = 'create' ]; then - printf ',\n' - fi - done - if [ "${turn}" = 'create' ]; then - printf ' PRIMARY KEY (`id`));\n' - fi - ;; - *) - >&2 printf 'unknown turn "%s"\n' "${turn}" - exit 2 - esac - done - done - printf '%s\n' \ - '/*!40014 SET UNIQUE_CHECKS=1 */;' \ - '/*!40014 SET FOREIGN_KEY_CHECKS=1 */;' - - # show_broken_packages_and_dependencies - # gives a list of broken packages and their dependencies if they're - # still on the build- or deletion-list - printf 'DROP PROCEDURE IF EXISTS show_broken_packages_and_dependencies;\n' - printf 'DELIMITER //\n' - printf 'CREATE PROCEDURE show_broken_packages_and_dependencies()\n' - printf 'BEGIN\n' - printf 'CREATE TEMPORARY TABLE `%s` (`id` BIGINT, UNIQUE KEY (`id`));\n' \ - 'broken_packages_and_dependencies' 'broken_packages_and_dependencies_old' - printf 'INSERT IGNORE INTO `broken_packages_and_dependencies` (`id`)' - printf ' SELECT `build_assignments`.`id`' - printf ' FROM `binary_packages`' - mysql_join_binary_packages_build_assignments - printf ' AND `build_assignments`.`is_broken`' - mysql_join_binary_packages_repositories - printf ' AND `repositories`.`name`="build-list";\n' - printf 'REPEAT\n' - printf 'INSERT IGNORE INTO `broken_packages_and_dependencies_old` (`id`)' - printf ' SELECT `broken_packages_and_dependencies`.`id` FROM `broken_packages_and_dependencies`;\n' - printf 'INSERT IGNORE INTO `broken_packages_and_dependencies` (`id`)' - printf ' SELECT `new_bp`.`build_assignment`' - printf ' FROM `broken_packages_and_dependencies_old`' - mysql_join_build_assignments_binary_packages 'broken_packages_and_dependencies_old' 'old_bp' - mysql_join_binary_packages_dependencies 'old_bp' - mysql_join_dependencies_dependency_types - printf ' AND `dependency_types`.`relevant_for_building`' - mysql_join_dependencies_install_target_providers - mysql_join_install_target_providers_binary_packages '' 'new_bp' - mysql_join_binary_packages_repositories 'new_bp' 'new_repo' - printf ' AND `new_repo`.`name` IN ("build-list","deletion-list");\n' - printf 'UNTIL ROW_COUNT()=0\n' - printf 'END REPEAT;\n' - printf 'SELECT ' - mysql_query_select_pkgbase_and_revision - printf ' JOIN `broken_packages_and_dependencies` ON `broken_packages_and_dependencies`.`id`=`build_assignments`.`id`;\n' - printf 'END\n' - printf '//\n' - printf 'DELIMITER ;\n' - - # calculate_dependencies_of_package_upto_first_built_one - # save binary packages (only their `id`) in `relevant_binary_packages` and `relevant_binary_packages_copy` - printf 'DROP PROCEDURE IF EXISTS calculate_dependencies_of_package_upto_first_built_one;\n' - printf 'DELIMITER //\n' - printf 'CREATE PROCEDURE calculate_dependencies_of_package_upto_first_built_one(IN `target_pkgbase` VARCHAR(64))\n' - printf 'BEGIN\n' - printf 'INSERT IGNORE INTO `relevant_binary_packages` (`id`)' - printf ' SELECT `binary_packages`.`id`' - printf ' FROM `binary_packages`' - mysql_join_binary_packages_repositories - printf ' AND `repositories`.`name`="build-list"' - mysql_join_binary_packages_build_assignments - mysql_join_build_assignments_package_sources - printf ' WHERE `package_sources`.`pkgbase`=`target_pkgbase`;\n' - printf 'REPEAT\n' - printf 'INSERT IGNORE INTO `relevant_binary_packages_copy` (`id`)' - printf ' SELECT `relevant_binary_packages`.`id` FROM `relevant_binary_packages`;\n' - printf 'INSERT IGNORE INTO `relevant_binary_packages` (`id`)' - printf ' SELECT `install_target_providers`.`package`' - printf ' FROM `relevant_binary_packages_copy`' - printf ' JOIN `binary_packages` ON `relevant_binary_packages_copy`.`id`=`binary_packages`.`id`' - mysql_join_binary_packages_repositories - printf ' AND `repositories`.`name`="build-list"' - mysql_join_binary_packages_dependencies - mysql_join_dependencies_dependency_types - printf ' AND `dependency_types`.`relevant_for_building`' - mysql_join_dependencies_install_target_providers - printf ';\n' - printf 'UNTIL ROW_COUNT()=0\n' - printf 'END REPEAT;\n' - printf 'END\n' - printf '//\n' - printf 'DELIMITER ;\n' - - # calculate_maximal_moveable_set - # stores results in `moveable_binary_packages` and `replaced_binary_packages` - # Give a maximal list of packages to be moved, while implementing the - # condition from db-update: - - # Every package which is replaced[1], must have its provided install_targets: - # a) provided by another moved or not-replaced package or - # b) not required by any not-replaced package. - - # Every package being moved needs to have all dependencies - # installable in the target repository. - - # TODO: [1] A "replaced" package may also be in a different repository - # e.g. if a-2 is moved from [staging] to [testing] and there is only - # a-1 in [core], then this will be "replaced" by a-2 on a system - # running on [testing] repositories. - - printf 'DROP PROCEDURE IF EXISTS calculate_maximal_moveable_set;\n' - printf 'DELIMITER //\n' - printf 'CREATE PROCEDURE calculate_maximal_moveable_set(IN `from_stability` VARCHAR(32))\n' - printf 'BEGIN\n' - # variables to store count of changed rows - printf 'DECLARE row_count_saved INT DEFAULT 0;\n' - for copy in '' '_copy' '_copy2'; do - printf 'DROP TEMPORARY TABLE IF EXISTS `%s_binary_packages%s`;\n' \ - 'moveable' "${copy}" \ - 'replaced' "${copy}" - printf 'CREATE TEMPORARY TABLE `replaced_binary_packages%s` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n' \ - "${copy}" - if [ "${copy}" = '_copy2' ]; then +#shellcheck disable=SC2016 +{ + # show_broken_packages_and_dependencies + # gives a list of broken packages and their dependencies if they're + # still on the build- or deletion-list + printf 'DROP PROCEDURE IF EXISTS show_broken_packages_and_dependencies;\n' + printf 'DELIMITER //\n' + printf 'CREATE PROCEDURE show_broken_packages_and_dependencies()\n' + printf 'BEGIN\n' + printf 'CREATE TEMPORARY TABLE `%s` (`id` BIGINT, UNIQUE KEY (`id`));\n' \ + 'broken_packages_and_dependencies' 'broken_packages_and_dependencies_old' + printf 'INSERT IGNORE INTO `broken_packages_and_dependencies` (`id`)' + printf ' SELECT `build_assignments`.`id`' + printf ' FROM `binary_packages`' + mysql_join_binary_packages_build_assignments + printf ' AND `build_assignments`.`is_broken`' + mysql_join_binary_packages_repositories + printf ' AND `repositories`.`name`="build-list";\n' + printf 'REPEAT\n' + printf 'INSERT IGNORE INTO `broken_packages_and_dependencies_old` (`id`)' + printf ' SELECT `broken_packages_and_dependencies`.`id` FROM `broken_packages_and_dependencies`;\n' + printf 'INSERT IGNORE INTO `broken_packages_and_dependencies` (`id`)' + printf ' SELECT `new_bp`.`build_assignment`' + printf ' FROM `broken_packages_and_dependencies_old`' + mysql_join_build_assignments_binary_packages 'broken_packages_and_dependencies_old' 'old_bp' + mysql_join_binary_packages_dependencies 'old_bp' + mysql_join_dependencies_dependency_types + printf ' AND `dependency_types`.`relevant_for_building`' + mysql_join_dependencies_install_target_providers + mysql_join_install_target_providers_binary_packages '' 'new_bp' + mysql_join_binary_packages_repositories 'new_bp' 'new_repo' + printf ' AND `new_repo`.`name` IN ("build-list","deletion-list");\n' + printf 'UNTIL ROW_COUNT()=0\n' + printf 'END REPEAT;\n' + printf 'SELECT ' + mysql_query_select_pkgbase_and_revision + printf ' JOIN `broken_packages_and_dependencies` ON `broken_packages_and_dependencies`.`id`=`build_assignments`.`id`;\n' + printf 'END\n' + printf '//\n' + printf 'DELIMITER ;\n' + + # calculate_dependencies_of_package_upto_first_built_one + # save binary packages (only their `id`) in `relevant_binary_packages` and `relevant_binary_packages_copy` + printf 'DROP PROCEDURE IF EXISTS calculate_dependencies_of_package_upto_first_built_one;\n' + printf 'DELIMITER //\n' + printf 'CREATE PROCEDURE calculate_dependencies_of_package_upto_first_built_one(IN `target_pkgbase` VARCHAR(64))\n' + printf 'BEGIN\n' + printf 'INSERT IGNORE INTO `relevant_binary_packages` (`id`)' + printf ' SELECT `binary_packages`.`id`' + printf ' FROM `binary_packages`' + mysql_join_binary_packages_repositories + printf ' AND `repositories`.`name`="build-list"' + mysql_join_binary_packages_build_assignments + mysql_join_build_assignments_package_sources + printf ' WHERE `package_sources`.`pkgbase`=`target_pkgbase`;\n' + printf 'REPEAT\n' + printf 'INSERT IGNORE INTO `relevant_binary_packages_copy` (`id`)' + printf ' SELECT `relevant_binary_packages`.`id` FROM `relevant_binary_packages`;\n' + printf 'INSERT IGNORE INTO `relevant_binary_packages` (`id`)' + printf ' SELECT `install_target_providers`.`package`' + printf ' FROM `relevant_binary_packages_copy`' + printf ' JOIN `binary_packages` ON `relevant_binary_packages_copy`.`id`=`binary_packages`.`id`' + mysql_join_binary_packages_repositories + printf ' AND `repositories`.`name`="build-list"' + mysql_join_binary_packages_dependencies + mysql_join_dependencies_dependency_types + printf ' AND `dependency_types`.`relevant_for_building`' + mysql_join_dependencies_install_target_providers + printf ';\n' + printf 'UNTIL ROW_COUNT()=0\n' + printf 'END REPEAT;\n' + printf 'END\n' + printf '//\n' + printf 'DELIMITER ;\n' + + # calculate_maximal_moveable_set + # stores results in `moveable_binary_packages` and `replaced_binary_packages` + # Give a maximal list of packages to be moved, while implementing the + # condition from db-update: + + # Every package which is replaced[1], must have its provided install_targets: + # a) provided by another moved or not-replaced package or + # b) not required by any not-replaced package. + + # Every package being moved needs to have all dependencies + # installable in the target repository. + + # TODO: [1] A "replaced" package may also be in a different repository + # e.g. if a-2 is moved from [staging] to [testing] and there is only + # a-1 in [core], then this will be "replaced" by a-2 on a system + # running on [testing] repositories. + + printf 'DROP PROCEDURE IF EXISTS calculate_maximal_moveable_set;\n' + printf 'DELIMITER //\n' + printf 'CREATE PROCEDURE calculate_maximal_moveable_set(IN `from_stability` VARCHAR(32))\n' + printf 'BEGIN\n' + # variables to store count of changed rows + printf 'DECLARE row_count_saved INT DEFAULT 0;\n' + for copy in '' '_copy' '_copy2'; do + printf 'DROP TEMPORARY TABLE IF EXISTS `%s_binary_packages%s`;\n' \ + 'moveable' "${copy}" \ + 'replaced' "${copy}" + printf 'CREATE TEMPORARY TABLE `replaced_binary_packages%s` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n' \ + "${copy}" + if [ "${copy}" = '_copy2' ]; then + continue + fi + printf 'CREATE TEMPORARY TABLE `moveable_binary_packages%s` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n' \ + "${copy}" + done + printf 'DROP TEMPORARY TABLE IF EXISTS `package_blobs`;\n' + printf 'CREATE TEMPORARY TABLE `package_blobs` (`ps_a` BIGINT, `ps_b` BIGINT, UNIQUE KEY `content` (`ps_a`,`ps_b`));\n' + # all packages being vaguely relevant enter `package_blobs`, because + # they might block any other package from moving + printf 'INSERT IGNORE INTO `package_blobs` (`ps_a`,`ps_b`)' + printf ' SELECT `a_ps`.`id`,`b_ps`.`id`' + printf ' FROM `package_sources` AS `a_ps`' + printf ' JOIN `package_sources` AS `b_ps`' + printf ' ON UNIX_TIMESTAMP(`a_ps`.`commit_time`) - UNIX_TIMESTAMP(`b_ps`.`commit_time`) BETWEEN -10 AND 10' + mysql_join_package_sources_build_assignments 'a_ps' 'a_ba' + mysql_join_package_sources_build_assignments 'b_ps' 'b_ba' + mysql_join_build_assignments_binary_packages 'a_ba' 'a_bp' + mysql_join_build_assignments_binary_packages 'b_ba' 'b_bp' + mysql_join_binary_packages_repositories 'a_bp' 'a_r' + mysql_join_binary_packages_repositories 'b_bp' 'b_r' + mysql_join_repositories_repository_stabilities 'a_r' 'a_rs' + mysql_join_repositories_repository_stabilities 'b_r' 'b_rs' + printf ' WHERE `a_rs`.`name` = `from_stability`' + printf ' AND `b_rs`.`name` = `from_stability`;\n' + # these packages are considered for moving: + printf 'INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)' + printf ' SELECT `binary_packages`.`id`,`repository_moves`.`to_repository`' + printf ' FROM `binary_packages`' + mysql_join_binary_packages_repositories + mysql_join_repositories_repository_stabilities + mysql_join_binary_packages_build_assignments + mysql_join_build_assignments_package_sources + mysql_join_package_sources_upstream_repositories + mysql_join_upstream_repositories_repository_moves + printf ' AND `repository_moves`.`from_repository`=`binary_packages`.`repository`' + # correct stability: "testing"/"staging" - as chosen + printf ' WHERE `repository_stabilities`.`name` = `from_stability`' + printf ' AND (' + printf '`from_stability`="staging"' + # "testing" packages must be tested + printf ' OR `binary_packages`.`is_tested`' + printf ')' + # no open issues + printf ' AND NOT `binary_packages`.`has_issues`' + printf ';\n' + # these packages are considered for being replaced: + # for each moved package + printf 'INSERT IGNORE INTO `replaced_binary_packages` (`id`,`replaced_by`)' + printf ' SELECT `r_bp`.`id`,`m_bp`.`id`' + printf ' FROM `moveable_binary_packages`' + printf ' JOIN `binary_packages` AS `m_bp` ON `m_bp`.`id`=`moveable_binary_packages`.`id`' + mysql_join_binary_packages_repositories 'm_bp' 'm_r' + mysql_join_binary_packages_build_assignments 'm_bp' 'm_ba' + mysql_join_build_assignments_package_sources 'm_ba' 'm_ps' + mysql_join_package_sources_upstream_repositories 'm_ps' 'm_ur' + mysql_join_upstream_repositories_repository_moves 'm_ur' + printf ' AND `repository_moves`.`from_repository`=`m_r`.`id`' + # in its target repository + printf ' JOIN `repositories` AS `r_r` ON `repository_moves`.`to_repository`=`r_r`.`id`' + mysql_join_repositories_binary_packages 'r_r' 'r_bp' + # all packages with identical names + printf ' AND `r_bp`.`pkgname`=`m_bp`.`pkgname`;\n' + # now we delete all unreplaceable and unmoveable packages from the respective + # list until no further changes are required + printf 'REPEAT\n' + printf 'SET row_count_saved = 0;\n' + + # create copies of our temporary tables *yuck* + for table in 'replaced' 'moveable'; do + for copy in '' '2'; do + if [ "${table}" = 'moveable' ] && \ + [ "${copy}" = '2' ]; then continue fi - printf 'CREATE TEMPORARY TABLE `moveable_binary_packages%s` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n' \ - "${copy}" + printf 'DELETE FROM `%s_binary_packages_copy%s`;\n' "${table}" "${copy}" + printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}" + printf ' SELECT `%s_binary_packages`.*' "${table}" + printf ' FROM `%s_binary_packages`;\n' "${table}" done - printf 'DROP TEMPORARY TABLE IF EXISTS `package_blobs`;\n' - printf 'CREATE TEMPORARY TABLE `package_blobs` (`ps_a` BIGINT, `ps_b` BIGINT, UNIQUE KEY `content` (`ps_a`,`ps_b`));\n' - # all packages being vaguely relevant enter `package_blobs`, because - # they might block any other package from moving - printf 'INSERT IGNORE INTO `package_blobs` (`ps_a`,`ps_b`)' - printf ' SELECT `a_ps`.`id`,`b_ps`.`id`' - printf ' FROM `package_sources` AS `a_ps`' - printf ' JOIN `package_sources` AS `b_ps`' - printf ' ON UNIX_TIMESTAMP(`a_ps`.`commit_time`) - UNIX_TIMESTAMP(`b_ps`.`commit_time`) BETWEEN -10 AND 10' - mysql_join_package_sources_build_assignments 'a_ps' 'a_ba' - mysql_join_package_sources_build_assignments 'b_ps' 'b_ba' - mysql_join_build_assignments_binary_packages 'a_ba' 'a_bp' - mysql_join_build_assignments_binary_packages 'b_ba' 'b_bp' - mysql_join_binary_packages_repositories 'a_bp' 'a_r' - mysql_join_binary_packages_repositories 'b_bp' 'b_r' - mysql_join_repositories_repository_stabilities 'a_r' 'a_rs' - mysql_join_repositories_repository_stabilities 'b_r' 'b_rs' - printf ' WHERE `a_rs`.`name` = `from_stability`' - printf ' AND `b_rs`.`name` = `from_stability`;\n' - # these packages are considered for moving: - printf 'INSERT IGNORE INTO `moveable_binary_packages` (`id`,`to_repository`)' - printf ' SELECT `binary_packages`.`id`,`repository_moves`.`to_repository`' - printf ' FROM `binary_packages`' - mysql_join_binary_packages_repositories - mysql_join_repositories_repository_stabilities - mysql_join_binary_packages_build_assignments - mysql_join_build_assignments_package_sources - mysql_join_package_sources_upstream_repositories - mysql_join_upstream_repositories_repository_moves - printf ' AND `repository_moves`.`from_repository`=`binary_packages`.`repository`' - # correct stability: "testing"/"staging" - as chosen - printf ' WHERE `repository_stabilities`.`name` = `from_stability`' - printf ' AND (' - printf '`from_stability`="staging"' - # "testing" packages must be tested - printf ' OR `binary_packages`.`is_tested`' - printf ')' - # no open issues - printf ' AND NOT `binary_packages`.`has_issues`' - printf ';\n' - # these packages are considered for being replaced: - # for each moved package - printf 'INSERT IGNORE INTO `replaced_binary_packages` (`id`,`replaced_by`)' - printf ' SELECT `r_bp`.`id`,`m_bp`.`id`' - printf ' FROM `moveable_binary_packages`' - printf ' JOIN `binary_packages` AS `m_bp` ON `m_bp`.`id`=`moveable_binary_packages`.`id`' - mysql_join_binary_packages_repositories 'm_bp' 'm_r' - mysql_join_binary_packages_build_assignments 'm_bp' 'm_ba' - mysql_join_build_assignments_package_sources 'm_ba' 'm_ps' - mysql_join_package_sources_upstream_repositories 'm_ps' 'm_ur' - mysql_join_upstream_repositories_repository_moves 'm_ur' - printf ' AND `repository_moves`.`from_repository`=`m_r`.`id`' - # in its target repository - printf ' JOIN `repositories` AS `r_r` ON `repository_moves`.`to_repository`=`r_r`.`id`' - mysql_join_repositories_binary_packages 'r_r' 'r_bp' - # all packages with identical names - printf ' AND `r_bp`.`pkgname`=`m_bp`.`pkgname`;\n' - # now we delete all unreplaceable and unmoveable packages from the respective - # list until no further changes are required - printf 'REPEAT\n' - printf 'SET row_count_saved = 0;\n' - - # create copies of our temporary tables *yuck* - for table in 'replaced' 'moveable'; do - for copy in '' '2'; do - if [ "${table}" = 'moveable' ] && \ - [ "${copy}" = '2' ]; then - continue - fi - printf 'DELETE FROM `%s_binary_packages_copy%s`;\n' "${table}" "${copy}" - printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}" - printf ' SELECT `%s_binary_packages`.*' "${table}" - printf ' FROM `%s_binary_packages`;\n' "${table}" - done - done - # a package is not moveable if its dependencies are not provided ... - printf 'DELETE `replaced_binary_packages`,`moveable_binary_packages`' - printf ' FROM `replaced_binary_packages`' - printf ' RIGHT JOIN `moveable_binary_packages`' - printf ' ON `moveable_binary_packages`.`id`=`replaced_binary_packages`.`replaced_by`' - printf ' JOIN `binary_packages` ON `binary_packages`.`id`=`moveable_binary_packages`.`id`' - printf ' JOIN `repositories` AS `target_repositories` ON `moveable_binary_packages`.`to_repository`=`target_repositories`.`id`' - mysql_join_binary_packages_dependencies - mysql_join_dependencies_dependency_types - printf ' AND `dependency_types`.`relevant_for_binary_packages`' - # ... by a not-deleted, "more stable" package already in place or ... - printf ' WHERE NOT EXISTS (' - printf 'SELECT 1 FROM `install_target_providers`' - mysql_join_install_target_providers_binary_packages '' 'prov_bp' - mysql_join_binary_packages_repositories 'prov_bp' 'prov_r' - printf ' JOIN `repository_stability_relations` ON `prov_r`.`stability`=`repository_stability_relations`.`more_stable`' - printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`' - printf ' AND `target_repositories`.`stability`=`repository_stability_relations`.`less_stable`' - printf ' AND NOT EXISTS (' - printf 'SELECT 1 FROM `replaced_binary_packages_copy`' - printf ' WHERE `replaced_binary_packages_copy`.`id`=`prov_bp`.`id`' - printf ')' - # ... by a moved package - printf ') AND NOT EXISTS (' - printf 'SELECT 1 FROM `install_target_providers`' - printf ' JOIN `moveable_binary_packages_copy` ON `moveable_binary_packages_copy`.`id`=`install_target_providers`.`package`' - printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`' - printf ');\n' - printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n' - - # refresh copies of our temporary tables *yuck* - for table in 'replaced' 'moveable'; do - for copy in '' '2'; do - if [ "${table}" = 'moveable' ] && \ - [ "${copy}" = '2' ]; then - continue - fi - printf 'DELETE FROM `%s_binary_packages_copy%s`;\n' "${table}" "${copy}" - printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}" - printf ' SELECT `%s_binary_packages`.*' "${table}" - printf ' FROM `%s_binary_packages`;\n' "${table}" - done - done - - # packages which should not be replaced: ones providing something that is: - # a) still needed by a not-replaced package x "less stable" than the target repository and - # b) not provided by: - # 1) a moved package or - # 2) any current, not-replaced package in a repository more stable than x's repository - # - # Note, that this is not 100% clean from an academic point of view: - # It might require _downgrading_ of a package to keep fulfilling a dependency - # and it might require to do so _without_ any chance for the user to notice, - # because there may be more "dependencies" in the database than in the package files. - # - # However, in practice both should not happen. - # - printf 'DELETE `replaced_binary_packages`,`moveable_binary_packages` FROM `replaced_binary_packages`' - printf ' JOIN `moveable_binary_packages` ON `replaced_binary_packages`.`replaced_by`=`moveable_binary_packages`.`id`' - printf ' JOIN `binary_packages` AS `repl_bp` ON `repl_bp`.`id`=`replaced_binary_packages`.`id`' - mysql_join_binary_packages_install_target_providers 'repl_bp' - mysql_join_install_target_providers_dependencies + done + # a package is not moveable if its dependencies are not provided ... + printf 'DELETE `replaced_binary_packages`,`moveable_binary_packages`' + printf ' FROM `replaced_binary_packages`' + printf ' RIGHT JOIN `moveable_binary_packages`' + printf ' ON `moveable_binary_packages`.`id`=`replaced_binary_packages`.`replaced_by`' + printf ' JOIN `binary_packages` ON `binary_packages`.`id`=`moveable_binary_packages`.`id`' + printf ' JOIN `repositories` AS `target_repositories` ON `moveable_binary_packages`.`to_repository`=`target_repositories`.`id`' + mysql_join_binary_packages_dependencies + mysql_join_dependencies_dependency_types + printf ' AND `dependency_types`.`relevant_for_binary_packages`' + # ... by a not-deleted, "more stable" package already in place or ... + printf ' WHERE NOT EXISTS (' + printf 'SELECT 1 FROM `install_target_providers`' + mysql_join_install_target_providers_binary_packages '' 'prov_bp' + mysql_join_binary_packages_repositories 'prov_bp' 'prov_r' + printf ' JOIN `repository_stability_relations` ON `prov_r`.`stability`=`repository_stability_relations`.`more_stable`' + printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`' + printf ' AND `target_repositories`.`stability`=`repository_stability_relations`.`less_stable`' printf ' AND NOT EXISTS (' - # dependencies of replaced packages don't matter printf 'SELECT 1 FROM `replaced_binary_packages_copy`' - printf ' WHERE `replaced_binary_packages_copy`.`id`=`dependencies`.`dependent`' + printf ' WHERE `replaced_binary_packages_copy`.`id`=`prov_bp`.`id`' printf ')' - mysql_join_dependencies_dependency_types - # consider only runtime dependencies - printf ' AND `dependency_types`.`relevant_for_binary_packages`' - mysql_join_dependencies_binary_packages '' 'req_bp' - # we need to check wether req_bp's dependency is (un)critical - mysql_join_binary_packages_repositories 'repl_bp' 'repl_r' - mysql_join_binary_packages_repositories 'req_bp' 'req_r' - # dependent package is "less stable" than dependency - printf ' JOIN `repository_stability_relations` AS `repl_rr`' - printf ' ON `repl_rr`.`more_stable`=`repl_r`.`stability`' - printf ' AND `repl_rr`.`less_stable`=`req_r`.`stability`' - # a) ^ - printf ' WHERE NOT EXISTS (' - # no moved package ... - printf 'SELECT 1' - printf ' FROM `moveable_binary_packages_copy`' - mysql_join_binary_packages_install_target_providers 'moveable_binary_packages_copy' 'subst_itp' - # ... provides the same - printf ' WHERE `subst_itp`.`install_target`=`install_target_providers`.`install_target`' - # b) 1) ^ - printf ') AND NOT EXISTS (' - # no current package ... - printf 'SELECT 1' - printf ' FROM `binary_packages` AS `subst_bp`' - mysql_join_binary_packages_install_target_providers 'subst_bp' 'subst_itp' - # ... in a repository ... - mysql_join_binary_packages_repositories 'subst_bp' 'subst_r' - # ... more stable ... - printf ' JOIN `repository_stability_relations` AS `subst_rr`' - printf ' ON `subst_rr`.`more_stable`=`subst_r`.`stability`' - # ... than x's repository ... - printf ' WHERE `subst_rr`.`less_stable`=`repl_r`.`stability`' - printf ' AND NOT EXISTS (' - # ... and which is not replaced ... - printf 'SELECT 1 FROM `replaced_binary_packages_copy2`' - printf ' WHERE `replaced_binary_packages_copy2`.`id`=`subst_bp`.`id`' - printf ')' - # ... and provides the same - printf ' AND `subst_itp`.`install_target`=`install_target_providers`.`install_target`' - # b) 2) ^ - printf ');\n' - printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n' - - # refresh copies of our temporary tables *yuck* - for table in 'replaced' 'moveable'; do - for copy in '' '2'; do - if [ "${table}" = 'moveable' ] && \ - [ "${copy}" = '2' ]; then - continue - fi - printf 'DELETE FROM `%s_binary_packages_copy%s`;\n' "${table}" "${copy}" - printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}" - printf ' SELECT `%s_binary_packages`.*' "${table}" - printf ' FROM `%s_binary_packages`;\n' "${table}" - done - done - - printf 'DELETE `replaced_binary_packages`,`moveable_binary_packages` FROM `replaced_binary_packages`' - printf ' RIGHT JOIN `moveable_binary_packages`' - printf ' ON `replaced_binary_packages`.`replaced_by`=`moveable_binary_packages`.`id`' - printf ' JOIN `binary_packages`' - printf ' ON `binary_packages`.`id`=`moveable_binary_packages`.`id`' - mysql_join_binary_packages_build_assignments - printf ' JOIN `package_blobs`' - printf ' ON `build_assignments`.`package_source`=`package_blobs`.`ps_a`' - printf ' JOIN `build_assignments` AS `bl_ba`' - printf ' ON `bl_ba`.`package_source`=`package_blobs`.`ps_b`' - mysql_join_build_assignments_binary_packages 'bl_ba' 'bl_bp' - printf ' WHERE NOT EXISTS (' - printf 'SELECT 1 FROM `moveable_binary_packages_copy`' - printf ' WHERE `moveable_binary_packages_copy`.`id`=`bl_bp`.`id`' - printf ');\n' - printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n' - - printf 'UNTIL row_count_saved=0\n' - printf 'END REPEAT;\n' - for table in 'moveable' 'replaced'; do - for copy in '' '2'; do - if [ "${table}" = 'moveable' ] && \ - [ "${copy}" = '2' ]; then - continue - fi - printf 'DROP TEMPORARY TABLE `%s_binary_packages_copy%s`;\n' \ - "${table}" "${copy}" - done + # ... by a moved package + printf ') AND NOT EXISTS (' + printf 'SELECT 1 FROM `install_target_providers`' + printf ' JOIN `moveable_binary_packages_copy` ON `moveable_binary_packages_copy`.`id`=`install_target_providers`.`package`' + printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`' + printf ');\n' + printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n' + + # refresh copies of our temporary tables *yuck* + for table in 'replaced' 'moveable'; do + for copy in '' '2'; do + if [ "${table}" = 'moveable' ] && \ + [ "${copy}" = '2' ]; then + continue + fi + printf 'DELETE FROM `%s_binary_packages_copy%s`;\n' "${table}" "${copy}" + printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}" + printf ' SELECT `%s_binary_packages`.*' "${table}" + printf ' FROM `%s_binary_packages`;\n' "${table}" done - printf 'END\n' - printf '//\n' - printf 'DELIMITER ;\n' - - printf 'GRANT %s ON %s TO '"'"'buildmaster'"'"'@'"'"'localhost'"'"';\n' \ - 'CREATE ROUTINE' 'buildmaster.*' \ - 'CREATE TEMPORARY TABLES' 'buildmaster.*' \ - 'EXECUTE' 'buildmaster.*' \ - 'RELOAD' '*.*' \ - 'SELECT' 'buildmaster.*' \ - 'SELECT' 'mysql.proc' \ - 'SHOW VIEW' 'buildmaster.*' \ - 'UPDATE' 'buildmaster.*' - printf 'GRANT %s ON %s TO '"'"'webserver'"'"'@'"'"'localhost'"'"';\n' \ - 'CREATE TEMPORARY TABLES' 'buildmaster.*' \ - 'SELECT' 'buildmaster.*' \ - 'SHOW VIEW' 'buildmaster.*' - printf 'FLUSH PRIVILEGES;\n' - } | \ - mysql_run_query -u root -p -fi + done -# shellcheck disable=SC2016 -{ - printf 'INSERT IGNORE INTO `architectures` (`name`) VALUES ' - printf '("%s"),' \ - 'any' 'i686' | \ - sed 's|,$||' - printf ';\n' - printf 'INSERT IGNORE INTO `fail_reasons` (`identifier`,`name`,`severity`) VALUES ' - printf '(from_base64("%s"),"%s",%s),' \ - "$( - printf '%s' '==> ERROR: A failure occurred in build()\.' | \ - base64 -w0 - )" 'build()' 0 \ - "$( - printf '%s' '==> ERROR: Could not download sources\.' | \ - base64 -w0 - )" 'source' 1 \ - "$( - printf '%s' '.*error: failed to commit transaction (invalid or corrupted package)' | \ - base64 -w0 - )" 'package-cache' 2 \ - "$( - printf '%s' '==> ERROR: A failure occurred in prepare()\.' | \ - base64 -w0 - )" 'prepare()' 2 \ - "$( - printf '%s' '==> ERROR: A failure occurred in package\(_\S\+\)\?()\.' | \ - base64 -w0 - )" 'package()' 3 \ - "$( - printf '%s' '==> ERROR: A failure occurred in check()\.' | \ - base64 -w0 - )" 'check()' 4 \ - "$( - printf '%s' '==> ERROR: '"'"'pacman'"'"' failed to install missing dependencies\.' | \ - base64 -w0 - )" 'dependencies' 1 \ - "$( - printf '%s' '==> ERROR: Running makepkg as root is not allowed as it can cause permanent,.*' | \ - base64 -w0 - )" 'run-as-root' 1 \ - "$( - printf '.*' | \ - base64 -w0 - )" 'unknown' 100 | \ - sed 's|,$||' - printf ';\n' - printf 'INSERT IGNORE INTO `git_repositories` (`name`,`url`,`directory`,`head`) VALUES' - { - for repo in ${repo_names}; do - eval 'repo_path="${repo_paths__'"${repo}"'}"' - printf '\n (' - printf 'from_base64("%s"),' \ - "$( - printf '%s' "${repo}" | \ - base64 -w0 - )" \ - "$( - git -C "${repo_path}" remote -v | \ - awk '{print $2}' | \ - tail -n1 | \ - base64_encode_each - )" \ - "$( - printf '%s' "${repo_path}" | \ - base64 -w0 - )" \ - "$( - git -C "${repo_path}" rev-parse HEAD | \ - base64_encode_each - )" | \ - sed 's|,$|),|' + # packages which should not be replaced: ones providing something that is: + # a) still needed by a not-replaced package x "less stable" than the target repository and + # b) not provided by: + # 1) a moved package or + # 2) any current, not-replaced package in a repository more stable than x's repository + # + # Note, that this is not 100% clean from an academic point of view: + # It might require _downgrading_ of a package to keep fulfilling a dependency + # and it might require to do so _without_ any chance for the user to notice, + # because there may be more "dependencies" in the database than in the package files. + # + # However, in practice both should not happen. + # + printf 'DELETE `replaced_binary_packages`,`moveable_binary_packages` FROM `replaced_binary_packages`' + printf ' JOIN `moveable_binary_packages` ON `replaced_binary_packages`.`replaced_by`=`moveable_binary_packages`.`id`' + printf ' JOIN `binary_packages` AS `repl_bp` ON `repl_bp`.`id`=`replaced_binary_packages`.`id`' + mysql_join_binary_packages_install_target_providers 'repl_bp' + mysql_join_install_target_providers_dependencies + printf ' AND NOT EXISTS (' + # dependencies of replaced packages don't matter + printf 'SELECT 1 FROM `replaced_binary_packages_copy`' + printf ' WHERE `replaced_binary_packages_copy`.`id`=`dependencies`.`dependent`' + printf ')' + mysql_join_dependencies_dependency_types + # consider only runtime dependencies + printf ' AND `dependency_types`.`relevant_for_binary_packages`' + mysql_join_dependencies_binary_packages '' 'req_bp' + # we need to check wether req_bp's dependency is (un)critical + mysql_join_binary_packages_repositories 'repl_bp' 'repl_r' + mysql_join_binary_packages_repositories 'req_bp' 'req_r' + # dependent package is "less stable" than dependency + printf ' JOIN `repository_stability_relations` AS `repl_rr`' + printf ' ON `repl_rr`.`more_stable`=`repl_r`.`stability`' + printf ' AND `repl_rr`.`less_stable`=`req_r`.`stability`' + # a) ^ + printf ' WHERE NOT EXISTS (' + # no moved package ... + printf 'SELECT 1' + printf ' FROM `moveable_binary_packages_copy`' + mysql_join_binary_packages_install_target_providers 'moveable_binary_packages_copy' 'subst_itp' + # ... provides the same + printf ' WHERE `subst_itp`.`install_target`=`install_target_providers`.`install_target`' + # b) 1) ^ + printf ') AND NOT EXISTS (' + # no current package ... + printf 'SELECT 1' + printf ' FROM `binary_packages` AS `subst_bp`' + mysql_join_binary_packages_install_target_providers 'subst_bp' 'subst_itp' + # ... in a repository ... + mysql_join_binary_packages_repositories 'subst_bp' 'subst_r' + # ... more stable ... + printf ' JOIN `repository_stability_relations` AS `subst_rr`' + printf ' ON `subst_rr`.`more_stable`=`subst_r`.`stability`' + # ... than x's repository ... + printf ' WHERE `subst_rr`.`less_stable`=`repl_r`.`stability`' + printf ' AND NOT EXISTS (' + # ... and which is not replaced ... + printf 'SELECT 1 FROM `replaced_binary_packages_copy2`' + printf ' WHERE `replaced_binary_packages_copy2`.`id`=`subst_bp`.`id`' + printf ')' + # ... and provides the same + printf ' AND `subst_itp`.`install_target`=`install_target_providers`.`install_target`' + # b) 2) ^ + printf ');\n' + printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n' + + # refresh copies of our temporary tables *yuck* + for table in 'replaced' 'moveable'; do + for copy in '' '2'; do + if [ "${table}" = 'moveable' ] && \ + [ "${copy}" = '2' ]; then + continue + fi + printf 'DELETE FROM `%s_binary_packages_copy%s`;\n' "${table}" "${copy}" + printf 'INSERT IGNORE INTO `%s_binary_packages_copy%s`' "${table}" "${copy}" + printf ' SELECT `%s_binary_packages`.*' "${table}" + printf ' FROM `%s_binary_packages`;\n' "${table}" done - printf ';\n' - } | \ - sed ' - s|,;|;| - ' - - for repo in \ - 'core:packages' \ - 'extra:packages' \ - 'multilib:packages' \ - 'community:community'; do - printf 'INSERT IGNORE INTO `upstream_repositories` (`name`,`git_repository`) SELECT\n' - printf ' from_base64("%s"),`id` FROM `git_repositories` WHERE `name` = from_base64("%s");\n' \ - "$( - printf '%s' "${repo%:*}" | \ - base64 -w0 - )" \ - "$( - printf '%s' "${repo#*:}" | \ - base64 -w0 - )" done - printf 'INSERT IGNORE INTO `build_slaves` (`name`,`ssh_key`,`operator`,`last_connection`) VALUES' - { - sed -n ' - s/^command="\S\+ \(\S\+\)" \S\+ \(\S\+\) \S\+$/\1 \2/ - T - p - ' ~/.ssh/authorized_keys | \ - while read -r name key; do - case "${name}" in - 'nlopc'*|'rechenknecht') - operator='deep42thought' - ;; - 'buildknecht'*) - operator='deep42thought/vollzornbrot' - ;; - 'eurobuild3') - operator='abaumann' - ;; - *) - operator="${name}" - ;; - esac - printf '\n (' - printf 'from_base64("%s"),' \ - "$( - printf '%s' "${name}" | \ - base64 -w0 - )" \ - "$( - printf '%s' "${key}" | \ - base64 -w0 - )" \ - "$( - printf '%s' "${operator}" | \ - base64 -w0 - )" - printf 'NOW()),' - done - printf ';\n' - } | \ - sed 's|,;|;|' - - printf 'INSERT IGNORE INTO `repository_stabilities` (`name`,`bugtracker_category`) VALUES' - { - printf '\n ("%s",%s),' \ - 'stable' '"Packages: Stable"' \ - 'testing' '"Packages: Testing"' \ - 'staging' 'NULL' \ - 'standalone' 'NULL' \ - 'unbuilt' '"Packages: Build-list"' \ - 'forbidden' 'NULL' - printf ';\n' - } | \ - sed 's|,;|;|' - - { - printf 'INSERT IGNORE INTO `repository_stability_relations` (`more_stable`,`less_stable`)' - printf ' SELECT `ms`.`id`,`ls`.`id`' - printf ' FROM `repository_stabilities` AS `ms` JOIN `repository_stabilities` AS `ls`' - printf ' WHERE ' - printf '(`ms`.`name`="%s" AND `ls`.`name`="%s") OR ' \ - 'stable' 'stable' \ - 'stable' 'testing' \ - 'stable' 'staging' \ - 'stable' 'standalone' \ - 'stable' 'unbuilt' \ - 'stable' 'forbidden' \ - 'testing' 'testing' \ - 'testing' 'staging' \ - 'testing' 'standalone' \ - 'testing' 'unbuilt' \ - 'testing' 'forbidden' \ - 'staging' 'staging' \ - 'staging' 'standalone' \ - 'staging' 'unbuilt' \ - 'staging' 'forbidden' \ - 'unbuilt' 'forbidden' \ - 'standalone' 'standalone' - printf ';\n' - } | \ - sed 's| OR ;|;|' - - { - printf 'INSERT IGNORE INTO `repository_moves` (`from_repository`,`to_repository`,`upstream_package_repository`)' - printf ' SELECT `f`.`id`,`t`.`id`,`u`.`id`' - printf ' FROM' - printf ' `repositories` AS `%s` JOIN' \ - 'f' 't' - printf ' `upstream_repositories` AS `u`' - printf ' WHERE ' - printf '(`f`.`name`="%s" AND `t`.`name`="%s" AND `u`.`name`="%s") OR ' \ - 'staging' 'testing' 'core' \ - 'staging' 'testing' 'extra' \ - 'staging' 'testing' 'multilib' \ - 'community-staging' 'community-testing' 'community' \ - 'community-staging' 'community-testing' 'multilib' \ - 'testing' 'core' 'core' \ - 'testing' 'extra' 'extra' \ - 'testing' 'extra' 'multilib' \ - 'community-testing' 'community' 'community' \ - 'community-testing' 'community' 'multilib' - printf ';\n' - } | \ - sed 's| OR ;|;|' - - for repo in \ - 'core:stable:AQ==' \ - 'extra:stable:AQ==' \ - 'community:stable:AQ==' \ - 'build-support:standalone:AQ==' \ - 'testing:testing:AQ==' \ - 'community-testing:testing:AQ==' \ - 'staging:staging:AQ==' \ - 'community-staging:staging:AQ==' \ - 'build-list:unbuilt:AA==' \ - 'deletion-list:forbidden:AA=='; do - printf 'INSERT IGNORE INTO `repositories` (`name`,`stability`,`is_on_master_mirror`) SELECT' - printf ' from_base64("%s"),`id`,from_base64("%s") FROM `repository_stabilities` WHERE `name`=from_base64("%s");\n' \ - "$( - printf '%s' "${repo}" | \ - cut -d: -f1 | \ - base64_encode_each - )" \ - "$( - printf '%s' "${repo}" | \ - cut -d: -f3 - )" \ - "$( - printf '%s' "${repo}" | \ - cut -d: -f2 | \ - base64_encode_each - )" + printf 'DELETE `replaced_binary_packages`,`moveable_binary_packages` FROM `replaced_binary_packages`' + printf ' RIGHT JOIN `moveable_binary_packages`' + printf ' ON `replaced_binary_packages`.`replaced_by`=`moveable_binary_packages`.`id`' + printf ' JOIN `binary_packages`' + printf ' ON `binary_packages`.`id`=`moveable_binary_packages`.`id`' + mysql_join_binary_packages_build_assignments + printf ' JOIN `package_blobs`' + printf ' ON `build_assignments`.`package_source`=`package_blobs`.`ps_a`' + printf ' JOIN `build_assignments` AS `bl_ba`' + printf ' ON `bl_ba`.`package_source`=`package_blobs`.`ps_b`' + mysql_join_build_assignments_binary_packages 'bl_ba' 'bl_bp' + printf ' WHERE NOT EXISTS (' + printf 'SELECT 1 FROM `moveable_binary_packages_copy`' + printf ' WHERE `moveable_binary_packages_copy`.`id`=`bl_bp`.`id`' + printf ');\n' + printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n' + + printf 'UNTIL row_count_saved=0\n' + printf 'END REPEAT;\n' + for table in 'moveable' 'replaced'; do + for copy in '' '2'; do + if [ "${table}" = 'moveable' ] && \ + [ "${copy}" = '2' ]; then + continue + fi + printf 'DROP TEMPORARY TABLE `%s_binary_packages_copy%s`;\n' \ + "${table}" "${copy}" + done done - - printf 'INSERT IGNORE INTO `dependency_types` (`name`,`relevant_for_building`,`relevant_for_binary_packages`) VALUES' - { - printf '\n ("%s",%s,%s),' \ - 'make' '1' '0' \ - 'check' '0' '0' \ - 'link' '0' '1' \ - 'run' '1' '1' - printf ';\n' - } | \ - sed 's|,;|;|' + printf 'END\n' + printf '//\n' + printf 'DELIMITER ;\n' + + printf 'GRANT %s ON %s TO '"'"'buildmaster'"'"'@'"'"'localhost'"'"';\n' \ + 'CREATE ROUTINE' 'buildmaster.*' \ + 'CREATE TEMPORARY TABLES' 'buildmaster.*' \ + 'EXECUTE' 'buildmaster.*' \ + 'RELOAD' '*.*' \ + 'SELECT' 'buildmaster.*' \ + 'SELECT' 'mysql.proc' \ + 'SHOW VIEW' 'buildmaster.*' \ + 'UPDATE' 'buildmaster.*' + printf 'GRANT %s ON %s TO '"'"'webserver'"'"'@'"'"'localhost'"'"';\n' \ + 'CREATE TEMPORARY TABLES' 'buildmaster.*' \ + 'SELECT' 'buildmaster.*' \ + 'SHOW VIEW' 'buildmaster.*' + printf 'FLUSH PRIVILEGES;\n' } | \ - mysql_run_query - -grep '^\('"$( - # shellcheck disable=SC2086 - printf '%s\\|' ${standalone_package_repositories} ${stable_package_repositories} -)"'\) ' "${tmp_dir}/master-mirror-listing" | \ - sed ' - s/\.pkg\.tar\.xz$// - s/-\([^-:]\+\)\(\(-[^-]\+\)\{2\}\)$/-0:\1\2/ - s/\(-[0-9]\+\)\(-[^-]\+\)$/\1.0\2/ - s/-\([^-:]\+\):\([^-:]\+\)-\([^-.]\+\)\.\([^-.]\+\)-\([^-]\+\)$/ \1 \2 \3 \4 \5/ - ' | \ - while read -r repo pkgname epoch pkgver pkgrel sub_pkgrel arch; do - # we don't care too much about those - they won't be moved anyways - printf '(' - # shellcheck disable=SC2046 - printf 'from_base64("%s"),' \ - $( - printf '%s\n' \ - "${pkgname}" \ - "${epoch}" \ - "${pkgver}" \ - "${pkgrel}" \ - "${sub_pkgrel}" \ - "${arch}" \ - "${repo}" | \ - base64_encode_each - ) | \ - sed 's/,$/),/' - printf '\n' - done > \ - "${tmp_dir}/new-stable-packages" - -if [ -s "${tmp_dir}/new-stable-packages" ]; then - # shellcheck disable=SC2016 - { - printf 'CREATE TEMPORARY TABLE `stable_packages` (' - printf '`%s` %s,' \ - 'pkgname' 'VARCHAR(64)' \ - 'epoch' 'MEDIUMINT' \ - 'pkgver' 'VARCHAR(64)' \ - 'pkgrel' 'MEDIUMINT' \ - 'sub_pkgrel' 'MEDIUMINT' \ - 'architecture' 'VARCHAR(16)' \ - 'repository' 'VARCHAR(64)' \ - 'build_assignment' 'BIGINT NOT NULL AUTO_INCREMENT' - printf 'PRIMARY KEY (`build_assignment`));\n' - sed ' - 1~10 ! b not_start - s/^/INSERT IGNORE INTO `stable_packages` (`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architecture`,`repository`) VALUES \n/ - :not_start - $ b end - 0~10 b end - b - :end - s/,$/;/ - ' "${tmp_dir}/new-stable-packages" - printf 'INSERT IGNORE INTO `binary_packages` (`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architecture`,`repository`,`build_assignment`,`has_issues`,`is_tested`,`is_to_be_deleted`)' - printf ' SELECT ' - printf '`stable_packages`.`%s`,' \ - 'pkgname' \ - 'epoch' \ - 'pkgver' \ - 'pkgrel' \ - 'sub_pkgrel' - printf '`%s`.`id`,' \ - 'architectures' \ - 'repositories' - printf -- '-`build_assignment`,0,1,0 FROM `stable_packages`' - printf ' JOIN `%s` ON `stable_packages`.`%s`=`%s`.`name`' \ - 'repositories' 'repository' 'repositories' \ - 'architectures' 'architecture' 'architectures' - printf ';\n' - printf 'DROP TABLE `stable_packages`;\n' - } | \ - mysql_run_query -fi - -mysql_repair_binary_packages_without_build_assignment + mysql_run_query -u root -p -- cgit v1.2.3-70-g09d2