#!/bin/sh # contains functions used to access mysql db # shellcheck disable=SC2016,SC2039,SC2119,SC2120 # TODO: remove hard-coded package suffixes if [ -z "${base_dir}" ]; then # just to make shellcheck happy . '../lib/load-configuration' fi # base64_encode_each encode each line of stdin with base64 base64_encode_each() { local line while read -r line; do printf '%s' \ "${line}" | \ base64 -w0 printf '\n' done } # mysql_run_query # wrapper function to query mysql mysql_run_query() { local query_stdin local query_stdout local query_stderr local file_name_extra local file local files local number file_name_extra='' if [ "x$1" = 'xunimportant' ]; then shift file_name_extra='unimportant_' elif [ -s "${work_dir}/build-master-sanity" ]; then file_name_extra='was_insane_' fi # we save the query in a file and delete that file if the query succeeded query_stdin=$(mktemp "${work_dir}/tmp.mysql-functions.${file_name_extra}query.$(date +'%Y-%m-%dT%T').XXXXXX.stdin") query_stdout="${query_stdin%.stdin}.stdout" query_stderr="${query_stdin%.stdin}.stderr" cat > "${query_stdin}" for _ in {1..10}; do if [ -f "${query_stdout}" ]; then wait_some_time 10 10 fi ${mysql_command} -N --raw --batch "$@" \ < "${query_stdin}" \ > "${query_stdout}" \ 2>> "${query_stderr}" \ && rm "${query_stdin}" "${query_stderr}" if ! [ -f "${query_stdin}" ]; then # success! break fi done # a present query_file means there was an error if [ -f "${query_stdin}" ]; then >&2 printf 'I (%s) could not complete a mysql query:\n' \ "$(hostname)" >&2 sed 's/^/mysql< /' "${query_stdin}" >&2 sed 's/^/mysql>2 /' "${query_stderr}" files="${query_stdin} ${query_stdout} ${query_stderr}$( sed -n ' s/^.*INFILE "\(\S\+\)".*$/\1/ T p ' "${query_stdin}" | \ sponge | \ cat -n | \ while read -r number file; do sed -i ' s@"'"$(str_to_regex "${file}")"'"@"'"${query_stdin%.stdin}.infile.${number}"'"@g ' "${query_stdin}" cp "${file}" "${query_stdin%.stdin}.infile.${number}" printf ' %s' "${query_stdin%.stdin}.infile.${number}" done )" if ${i_am_the_master}; then if [ ! -s "${work_dir}/build-master-sanity" ] && \ [ -z "${file_name_extra}" ]; then printf '\001ACTION failed to execute a mysql query - can you have a look at "%s"?.\001\n' \ "${query_stdin##*/}" \ | irc_say fi for file in ${files}; do cp "${file}" "${webserver_directory}/mysql-queries/${file##*/}.txt" chmod go+r "${webserver_directory}/mysql-queries/${file##*/}.txt" done fi if [ -z "${file_name_extra}" ]; then echo 'A mysql query failed.' > \ "${work_dir}/build-master-sanity" else # shellcheck disable=SC2086 rm -f ${files} fi return 2 fi # shellcheck disable=SC2094 { rm "${query_stdout}" cat } < \ "${query_stdout}" } # mysql_add_package_source $pkgbase $git_revision $mod_git_revision $upstream_package_repository $srcinfo_file # shellcheck disable=SC2086 mysql_add_package_source() { local pkgbase local git_revision local mod_git_revision local upstream_package_repository local srcinfo_file pkgbase="$1" git_revision="$2" mod_git_revision="$3" upstream_package_repository="$4" srcinfo_file="$5" local uses_upstream local uses_modification local repo local upstream_package_repository_id if grep -qx 'PKGBUILD_mod = \S\+' "${srcinfo_file}"; then uses_modification=1 else uses_modification=0 fi if grep -qx 'PKGBUILD = \S\+' "${srcinfo_file}"; then uses_upstream=1 else uses_upstream=0 fi upstream_package_repository_id=$( { printf 'SELECT `upstream_repositories`.`id`' printf ' FROM `upstream_repositories`' printf ' WHERE `upstream_repositories`.`name` = from_base64("%s")' \ "$( printf '%s' "${upstream_package_repository}" | \ base64 -w0 )" printf ' LIMIT 1;\n' } | \ mysql_run_query ) if [ -z "${upstream_package_repository_id}" ]; then >&2 printf 'Cannot find upstream package repository "%s" in the database.\n' \ "${upstream_package_repository}" exit 2 fi { printf 'INSERT INTO `package_sources`' printf ' (`pkgbase`,`git_revision`,`mod_git_revision`,`upstream_package_repository`,`uses_upstream`,`uses_modification`)' printf ' VALUES (' printf 'from_base64("%s"),' \ "$( printf '%s' "${pkgbase}" | \ base64 -w0 )" \ "$( printf '%s' "${git_revision}" | \ base64 -w0 )" \ "$( printf '%s' "${mod_git_revision}" | \ base64 -w0 )" printf '%s,' \ "${upstream_package_repository_id}" \ "${uses_upstream}" \ "${uses_modification}" | \ sed 's/,$/)/' printf ' ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`package_sources`.`id`);\n' printf 'SELECT LAST_INSERT_ID();\n' } | \ mysql_run_query } # mysql_generate_package_metadata $current_repository_id $package $git_revision $mod_git_revision $repository # generate the meta data of a package (dependencies, built packages, ...) in the database mysql_generate_package_metadata() { mysql_load_min_and_max_versions ( # new shell is intentional current_repository_id="$1" package="$2" git_revision="$3" mod_git_revision="$4" repository="$5" if [[ "${current_repository_id}" = *[!0-9]* ]]; then >&2 printf 'mysql_generate_package_metadata(): invalid current_repository_id="%s".\n' \ "${current_repository_id}" exit 2 fi temp_dir=$(mktemp -d 'tmp.mysql-functions.mysql_generate_package_metadata.XXXXXXXXXX' --tmpdir) trap 'rm -rf --one-file-system "${temp_dir}"' EXIT if ! make_source_info "${package}" "${repository}" "${git_revision}" "${mod_git_revision}" "${temp_dir}/SRCINFO"; then >&2 printf '"make_source_info %s %s %s %s %s" failed.\n' "${package}" "${repository}" "${git_revision}" "${mod_git_revision}" "${temp_dir}/SRCINFO" exit 2 fi # remove empty lines and unsupported architectures sed -i ' /^[^=]*=\s*$/d /^\s*arch = /{ / \(i[46]86\|pentium4\|any\)$/!d } ' "${temp_dir}/SRCINFO" if [ ! -s "${temp_dir}/SRCINFO" ]; then >&2 printf '"make_source_info" had empty output - eh, what?\n' exit 2 fi printf '\n\n' >> "${temp_dir}/SRCINFO" pkgbase=$( grep '^pkgbase = ' "${temp_dir}/SRCINFO" | \ cut -d' ' -f3 ) if [ -z "${pkgbase}" ]; then >&2 printf '"make_source_info" did not return a "pkgbase" - eh, what?\n' exit 2 fi # add the package source package_source_id=$( mysql_add_package_source "${pkgbase}" "${git_revision}" "${mod_git_revision}" "${repository}" "${temp_dir}/SRCINFO" ) { sed -n ' s/^\tarch = // T p ' "${temp_dir}/SRCINFO" | \ sort -u | \ grep -vxF 'any' || \ echo 'any' } > \ "${temp_dir}/architectures" # select any specific arch (which will be building the 'any' part of a split package) any_arch=$( tail -n1 "${temp_dir}/architectures" ) # iterate over all pkgnames grep '^pkgname = ' "${temp_dir}/SRCINFO" | \ cut -d' ' -f3 | \ while read -r pkgname; do # iff this pkgname defines some arch, ... if sed -n ' /^pkgname = '"$(str_to_regex "${pkgname}")"'$/,/^$/ p ' "${temp_dir}/SRCINFO" | \ grep -q '^\sarch = '; then # ... it will override all archs defined in pkgbase sed ' /^pkgbase = /,/^$/ { /^\sarch = /d } ' "${temp_dir}/SRCINFO" else cat "${temp_dir}/SRCINFO" fi | \ sed -n ' /^pkgbase = \|^pkgname = '"$(str_to_regex "${pkgname}")"'$/,/^$/ { s/^pkgname/\t\0/ /^\S/d /^\s*$/d s/^\s*// p } ' > \ "${temp_dir}/SRCINFO.tmp" epoch=$( { grep -m1 '^epoch = ' "${temp_dir}/SRCINFO.tmp" || \ echo 'epoch = 0' } | \ cut -d' ' -f3 ) pkgver=$( grep -m1 '^pkgver = ' "${temp_dir}/SRCINFO.tmp" | \ cut -d' ' -f3 ) pkgrel=$( grep -m1 '^pkgrel = ' "${temp_dir}/SRCINFO.tmp" | \ cut -d' ' -f3 ) # iterate over all archs grep '^arch = ' "${temp_dir}/SRCINFO.tmp" | \ cut -d' ' -f3 | \ while read -r arch; do build_arch=$( # this binary package is either built by the build_assignment # with the identical arch (if existent) or by the one # with arch=$any_arch grep -xF "${arch}" "${temp_dir}/architectures" || \ echo "${any_arch}" ) sed ' s/^\(\S\+\)_'"${arch}"' = /\1 = / s/^pkgname = \(\S\+\)$/\0\nprovides = \1='"${epoch}"':'"${pkgver}"'\ndepends = base\nmakedepends = base-devel/ ' "${temp_dir}/SRCINFO.tmp" | \ sed ' s/^arch = \S\+$/arch/ t s/^provides = /provides\t/ t mangle_version s/^groups = /groups\t/ t mangle_version s/^makedepends = /makedepends\t/ t mangle_version s/^checkdepends = /checkdepends\t/ t mangle_version s/^depends = /rundepends\t/ t mangle_version d :mangle_version # TODO: we might want to keep the pkgrel part and only remove the # sub_pkgrel part - but then we need to include pkgrels on the provide # side, too(?) s/\(=\|<\|<=\|>=\|>\)\([^[:space:]-]\+\)\(-\S*\)\?$/\t\1\t\2/ t split_epoch h s/$/\t>=\t'"${min_version}"'/ s/\(\s[0-9]\+\):\(\S\+\)$/\1\t\2/ p g s/$/\t<=\t'"${max_version}"'/ /^\S\+depends\s/d :split_epoch s/\(\s[0-9]\+\):\(\S\+\)$/\1\t\2/ t s/\s\S\+$/\t0\0/ ' | \ sed ' s/^/'"$( printf '%s\t' \ "${pkgname}" \ "${arch}" \ "${epoch}" \ "${pkgver}" \ "${pkgrel}" \ "${build_arch}" )"'/ ' done rm "${temp_dir}/SRCINFO.tmp" done | \ sort -u > \ "${temp_dir}/database-input" { printf 'CREATE TEMPORARY TABLE `links`(' printf '`pkgname` VARCHAR(64),' printf '`architecture` VARCHAR(16),' printf '`epoch` MEDIUMINT,' printf '`pkgver` VARCHAR(64),' printf '`pkgrel` MEDIUMINT,' printf '`build_architecture` VARCHAR(16),' printf '`type` VARCHAR(16),' printf '`install_target_name` VARCHAR(128),' printf '`version_relation` VARCHAR(2),' printf '`install_target_epoch` MEDIUMINT,' printf '`install_target_version` VARCHAR(64)' printf ');\n' printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `links`;\n' \ "${temp_dir}/database-input" printf 'SET @sub_pkgrel=(' printf 'SELECT IFNULL(' printf 'MAX(' printf '`binary_packages`.`sub_pkgrel`+' printf 'IF(`binary_packages_in_repositories`.`repository` IN (%s,%s),0,1)' \ "${repository_ids__any_build_list}" \ "${repository_ids__any_to_be_decided}" printf '),' printf '0' printf ')' printf ' FROM `links`' printf ' JOIN `architectures`' printf ' ON `architectures`.`name`=`links`.`architecture`' mysql_join_architectures_binary_packages printf ' AND `binary_packages`.`%s`=`links`.`%s`' \ 'pkgname' 'pkgname' \ 'epoch' 'epoch' \ 'pkgver' 'pkgver' \ 'pkgrel' 'pkgrel' printf ' LEFT' mysql_join_binary_packages_binary_packages_in_repositories printf ');\n' printf 'INSERT IGNORE INTO `build_assignments` (' printf '`package_source`,' printf '`architecture`' printf ') SELECT' printf ' %s,`architectures`.`id`' \ "${package_source_id}" printf ' FROM `links`' printf ' JOIN `architectures`' printf ' ON `architectures`.`name`=`links`.`build_architecture`' printf ' WHERE `links`.`type`="arch";\n' # we delete unbuilt binary_packages of identical version prior to # inserting, so we can _update_ a build list entry (e.g. because # the source changed, but the version stays the same) printf 'DELETE `binary_packages`' printf ' FROM `links`' printf ' JOIN `architectures`' printf ' ON `links`.`architecture`=`architectures`.`name`' printf ' JOIN `architectures` AS `ba_a`' printf ' ON `ba_a`.`name`=`links`.`build_architecture`' mysql_join_architectures_build_assignments 'ba_a' mysql_join_build_assignments_binary_packages printf ' AND `binary_packages`.`%s`=`links`.`%s`' \ 'pkgname' 'pkgname' \ 'epoch' 'epoch' \ 'pkgver' 'pkgver' \ 'pkgrel' 'pkgrel' printf ' AND `binary_packages`.`sub_pkgrel`=@sub_pkgrel' printf ' AND `binary_packages`.`architecture`=`architectures`.`id`' printf ' WHERE `links`.`type`="arch"' # the below test should always give "true", but it is a useful # safeguard to not delete built packages from the database printf ' AND NOT EXISTS(' printf 'SELECT 1' printf ' FROM `binary_packages_in_repositories`' mysql_join_binary_packages_in_repositories_repositories printf ' WHERE `repositories`.`is_on_master_mirror`' printf ' AND `binary_packages_in_repositories`.`package`=`binary_packages`.`id`' printf ');\n' printf 'INSERT IGNORE INTO `binary_packages` (' printf '`build_assignment`,' printf '`pkgname`,' printf '`epoch`,' printf '`pkgver`,' printf '`pkgrel`,' printf '`sub_pkgrel`,' printf '`architecture`,' printf '`compression`' printf ') SELECT' printf ' `build_assignments`.`id`,' printf '`links`.`%s`,' \ 'pkgname' \ 'epoch' \ 'pkgver' \ 'pkgrel' printf '@sub_pkgrel,' printf '`architectures`.`id`,' # TODO: somehow extract the compression from the PKGBUILD (?) printf '%s' \ "${compression_ids__tar_xz}" printf ' FROM `links`' printf ' JOIN `architectures`' printf ' ON `links`.`architecture`=`architectures`.`name`' printf ' JOIN `architectures` AS `ba_a`' printf ' ON `ba_a`.`name`=`links`.`build_architecture`' mysql_join_architectures_build_assignments 'ba_a' printf ' AND `build_assignments`.`package_source`=%s' \ "${package_source_id}" printf ' WHERE `links`.`type`="arch";\n' printf 'INSERT IGNORE INTO `binary_packages_in_repositories` (' printf ' `package`,' printf '`repository`' printf ') SELECT' printf ' `binary_packages`.`id`,' printf '%s' \ "${current_repository_id}" printf ' FROM `links`' printf ' JOIN `architectures`' printf ' ON `architectures`.`name`=`links`.`architecture`' mysql_join_architectures_binary_packages printf ' AND `binary_packages`.`sub_pkgrel`=@sub_pkgrel' printf ' AND `binary_packages`.`%s`=`links`.`%s`' \ 'pkgname' 'pkgname' \ 'epoch' 'epoch' \ 'pkgver' 'pkgver' \ 'pkgrel' 'pkgrel' printf ';\n' if [ "${current_repository_id}" -eq "${repository_ids__any_build_list}" ] || \ [ "${current_repository_id}" -eq "${repository_ids__any_to_be_decided}" ]; then printf 'DELETE `binary_packages_in_repositories`' printf ' FROM `links`' printf ' JOIN `architectures`' printf ' ON `architectures`.`name`=`links`.`architecture`' mysql_join_architectures_binary_packages printf ' AND `binary_packages`.`pkgname`=`links`.`pkgname`' printf ' AND (' printf '`binary_packages`.`sub_pkgrel`!=@sub_pkgrel' printf ' OR `binary_packages`.`%s`!=`links`.`%s`' \ 'epoch' 'epoch' \ 'pkgver' 'pkgver' \ 'pkgrel' 'pkgrel' printf ')' mysql_join_binary_packages_binary_packages_in_repositories printf ' AND `binary_packages_in_repositories`.`repository`=%s' \ "${current_repository_id}" printf ';\n' printf 'DELETE `binary_packages`' printf ' FROM `links`' printf ' JOIN `architectures`' printf ' ON `architectures`.`name`=`links`.`architecture`' mysql_join_architectures_binary_packages printf ' AND `binary_packages`.`pkgname`=`links`.`pkgname`' printf ' AND (' printf '`binary_packages`.`sub_pkgrel`!=@sub_pkgrel' printf ' OR `binary_packages`.`%s`!=`links`.`%s`' \ 'epoch' 'epoch' \ 'pkgver' 'pkgver' \ 'pkgrel' 'pkgrel' printf ') LEFT' mysql_join_binary_packages_binary_packages_in_repositories printf ' WHERE `binary_packages_in_repositories`.`id` IS NULL' printf ' AND `binary_packages`.`sha512sum` IS NULL;\n' fi printf 'INSERT IGNORE INTO `install_targets` (`name`)' printf ' SELECT `links`.`install_target_name`' printf ' FROM `links`;\n' printf 'INSERT IGNORE INTO `versions` (`epoch`,`version`)' printf ' SELECT' printf ' `links`.`install_target_epoch`,' printf '`links`.`install_target_version`' printf ' FROM `links`' printf ' WHERE `links`.`install_target_epoch` IS NOT NULL' printf ' AND `links`.`install_target_version` IS NOT NULL' printf ';\n' for link in 'groups' 'provides' 'makedepends' 'checkdepends' 'rundepends'; do case "${link}" in 'groups'|'provides') printf 'INSERT IGNORE INTO `install_target_providers` (' printf '`package`,' printf '`install_target`,' printf '`version`,' printf '`install_target_is_group`' printf ') SELECT' printf ' `binary_packages`.`id`,' printf '`install_targets`.`id`,' printf '`versions`.`id`,' if [ "${link}" = 'groups' ]; then printf '1' else printf '0' fi ;; 'makedepends'|'checkdepends'|'rundepends') printf 'INSERT IGNORE INTO `dependencies` (' printf '`dependent`,' printf '`depending_on`,' printf '`dependency_type`,' printf '`version`,' printf '`version_relation`' printf ') SELECT' printf ' `binary_packages`.`id`,' printf '`install_targets`.`id`,' printf '`dependency_types`.`id`,' printf '`versions`.`id`,' printf '`links`.`version_relation`' ;; esac printf ' FROM `links`' printf ' JOIN `architectures`' printf ' ON `architectures`.`name`=`links`.`architecture`' mysql_join_architectures_binary_packages printf ' AND `binary_packages`.`sub_pkgrel`=@sub_pkgrel' printf ' AND `binary_packages`.`%s`=`links`.`%s`' \ 'pkgname' 'pkgname' \ 'epoch' 'epoch' \ 'pkgver' 'pkgver' \ 'pkgrel' 'pkgrel' printf ' JOIN `install_targets`' printf ' ON `install_targets`.`name`=`links`.`install_target_name`' printf ' JOIN `versions`' printf ' ON `versions`.`epoch`=`links`.`install_target_epoch`' printf ' AND `versions`.`version`=`links`.`install_target_version`' if [ "${link}" = 'makedepends' ] || \ [ "${link}" = 'checkdepends' ] || \ [ "${link}" = 'rundepends' ]; then printf ' JOIN `dependency_types`' printf ' ON `dependency_types`.`name`="%s"' \ "${link%depends}" fi printf ' WHERE `links`.`type`="%s";\n' \ "${link}" done printf 'DROP TEMPORARY TABLE `links`;\n' } | \ mysql_run_query ) } # mysql_sanity_check # do a sanity check on the mysql database mysql_sanity_check() { { printf 'SELECT CONCAT(' printf '"\\"",' printf '`ba_arch`.`name`,' printf '"\\" build-assignment building \\"",' printf '`bp_arch`.`name`,' printf '"\\" binary package: ",' printf '`binary_packages`.`pkgname`' printf ')' printf ' FROM `binary_packages`' mysql_join_binary_packages_build_assignments mysql_join_binary_packages_architectures '' 'bp_arch' mysql_join_build_assignments_architectures '' 'ba_arch' printf ' LEFT JOIN `architecture_compatibilities`' printf ' ON `architecture_compatibilities`.`runs_on`=`build_assignments`.`architecture`' printf ' AND `architecture_compatibilities`.`built_for`=`binary_packages`.`architecture`' printf ' AND `architecture_compatibilities`.`fully_compatible`' printf ' WHERE `architecture_compatibilities`.`id` IS NULL;\n' printf 'SELECT DISTINCT CONCAT("package multiple times in equally stable repositories: ",`a_ra`.`name`,"/{",`a_r`.`name`,",",`b_r`.`name`,"}/",`a`.`pkgname`)' printf ' FROM `binary_packages` AS `a`' mysql_join_binary_packages_binary_packages_in_repositories 'a' 'a_bir' mysql_join_binary_packages_in_repositories_repositories 'a_bir' 'a_r' mysql_join_repositories_architectures 'a_r' 'a_ra' printf ' JOIN `repositories` AS `b_r`' printf ' ON `a_r`.`stability`=`b_r`.`stability`' printf ' AND `a_r`.`architecture`=`b_r`.`architecture`' mysql_join_repositories_binary_packages_in_repositories 'b_r' 'b_bir' mysql_join_binary_packages_in_repositories_binary_packages 'b_bir' 'b' printf ' AND `a`.`pkgname`=`b`.`pkgname`' printf ' AND `a`.`id`!=`b`.`id`' printf ' AND (' printf '`a_r`.`architecture`!=%s' \ "${architecture_ids__any}" printf ' OR `a`.`architecture`=`b`.`architecture`' printf ')' printf ' WHERE `a_r`.`stability` NOT IN (%s,%s)' \ "${repository_stability_ids__forbidden}" \ "${repository_stability_ids__virtual}" printf ' AND `b_r`.`stability` NOT IN (%s,%s);\n' \ "${repository_stability_ids__forbidden}" \ "${repository_stability_ids__virtual}" printf 'SELECT DISTINCT CONCAT("split-package with differing sub_pkgrels on the build-list: ",`a`.`pkgname`)' printf ' FROM `binary_packages` AS `a`' printf ' JOIN `binary_packages` AS `b`' printf ' ON `a`.`build_assignment`=`b`.`build_assignment`' mysql_join_binary_packages_binary_packages_in_repositories 'a' 'a_bir' mysql_join_binary_packages_binary_packages_in_repositories 'b' 'b_bir' printf ' WHERE `a`.`sub_pkgrel`!=`b`.`sub_pkgrel`' printf ' AND `%s_bir`.`repository`=%s' \ 'a' "${repository_ids__any_build_list}" \ 'b' "${repository_ids__any_build_list}" printf ';\n' printf 'SELECT DISTINCT CONCAT("non-virtual binary-package without checksum: ",' mysql_package_name_query printf ') FROM `binary_packages`' mysql_join_binary_packages_architectures printf ' LEFT' mysql_join_binary_packages_compressions mysql_join_binary_packages_binary_packages_in_repositories mysql_join_binary_packages_in_repositories_repositories printf ' WHERE `repositories`.`is_on_master_mirror`' printf ' AND `binary_packages`.`sha512sum` IS NULL;\n' } | \ mysql_run_query | \ sed ' s,^,, s,$,, ' ( # new shell is intentional temp_dir=$(mktemp -d 'tmp.mysql-functions.mysql_sanity_check.XXXXXXXXXX' --tmpdir) trap 'rm -rf --one-file-system "${temp_dir}"' EXIT { printf 'SELECT DISTINCT `architectures`.`name`' printf ' FROM `architectures`' printf ' WHERE `architectures`.`id`!=%s;\n' \ "${architecture_ids__any}" } | \ mysql_run_query | \ while read -r arch; do for dir in $(ls_master_mirror "${arch}"); do ls_master_mirror "${arch}/${dir}" | \ sed ' /\.pkg\.tar\.xz$/!d s|^|'"${arch}"'/'"${dir}"'/| ' done done | \ sort > \ "${temp_dir}/master-mirror-listing" { printf 'SELECT CONCAT(`r_a`.`name`,"/",`repositories`.`name`,"/",' mysql_package_name_query printf ') FROM `binary_packages_in_repositories`' mysql_join_binary_packages_in_repositories_binary_packages mysql_join_binary_packages_architectures printf ' LEFT' mysql_join_binary_packages_compressions mysql_join_binary_packages_in_repositories_repositories mysql_join_repositories_architectures '' 'r_a' printf ' WHERE `repositories`.`is_on_master_mirror`' } | \ mysql_run_query | \ sed ' s,\t,/, s,\t,/, s,\t,-, s,\t,:, s,\t,-, s,\t,., s,\t,-, ' | \ sort > \ "${temp_dir}/mysql-packages" diff -u \ "${temp_dir}/master-mirror-listing" \ "${temp_dir}/mysql-packages" # shellcheck disable=SC2041 ls_master_mirror 'pool' | \ sed ' /\.pkg\.tar\.xz\(\.sig\)\?$/ !d ' | \ sort -u > \ "${temp_dir}/master-mirror-pool" { printf 'SELECT ' mysql_package_name_query printf ' FROM `binary_packages`' mysql_join_binary_packages_architectures printf ' LEFT' mysql_join_binary_packages_compressions printf ' WHERE NOT EXISTS (' printf 'SELECT 1' printf ' FROM `binary_packages_in_repositories`' mysql_join_binary_packages_in_repositories_repositories printf ' WHERE NOT `repositories`.`is_on_master_mirror`' printf ' AND `binary_packages_in_repositories`.`package`=`binary_packages`.`id`' printf ') OR EXISTS (' printf 'SELECT 1' printf ' FROM `binary_packages_in_repositories`' mysql_join_binary_packages_in_repositories_repositories printf ' WHERE `repositories`.`is_on_master_mirror`' printf ' AND `binary_packages_in_repositories`.`package`=`binary_packages`.`id`' printf ')' } | \ mysql_run_query | \ sed ' s/^.*$/\0\n\0.sig/ ' | \ sort > \ "${temp_dir}/mysql-packages-pool" diff -u \ "${temp_dir}/master-mirror-pool" \ "${temp_dir}/mysql-packages-pool" ) } # mysql_find_build_assignment_loops # recreate the `build_dependency_loops` table mysql_find_build_assignment_loops() { ( # new shell is intentional tmp_file=$(mktemp 'tmp.mysql-functions.mysql_find_build_assignment_loops.XXXXXXXXXX' --tmpdir) trap 'rm "${tmp_file}"' EXIT { printf 'SELECT DISTINCT `architectures`.`id`' printf ' FROM `architectures`' printf ' WHERE `architectures`.`id`!=%s;\n' \ "${architecture_ids__any}" } | \ mysql_run_query | \ while read -r arch_id; do { printf 'SELECT DISTINCT' printf ' `ncy_ba`.`id`,' printf '`nt_ba`.`id`' printf ' FROM `dependencies`' mysql_join_dependencies_dependency_types mysql_join_dependencies_install_target_providers_with_versions mysql_join_install_target_providers_binary_packages '' 'ncy_bp' mysql_join_dependencies_binary_packages '' 'nt_bp' for which in 'ncy' 'nt'; do mysql_join_binary_packages_binary_packages_in_repositories "${which}"'_bp' "${which}"'_bpir' mysql_join_binary_packages_build_assignments "${which}"'_bp' "${which}"'_ba' printf ' JOIN `architecture_compatibilities` AS `%s_ac`' \ "${which}" printf ' ON `%s_ac`.`built_for`=`%s_ba`.`architecture`' \ "${which}" "${which}" printf ' AND `%s_ac`.`runs_on`=%s' \ "${which}" "${arch_id}" printf ' AND `%s_ac`.`fully_compatible`' \ "${which}" done printf ' WHERE `nt_bpir`.`repository`=%s' \ "${repository_ids__any_build_list}" printf ' AND `ncy_bpir`.`repository`=%s' \ "${repository_ids__any_build_list}" printf ' AND `dependency_types`.`relevant_for_building`' printf ' AND (' printf '`dependency_types`.`relevant_for_binary_packages`' printf ' OR `nt_bp`.`pkgname` LIKE "haskell-%%"' printf ' OR NOT EXISTS (' printf 'SELECT 1' printf ' FROM `binary_packages_in_repositories` AS `subst_bpir`' mysql_join_binary_packages_in_repositories_repositories 'subst_bpir' 'subst_r' printf ' AND `subst_r`.`is_on_master_mirror`' mysql_join_binary_packages_in_repositories_binary_packages 'subst_bpir' 'subst_bp' mysql_join_binary_packages_build_assignments 'subst_bp' 'subst_ba' printf ' JOIN `architecture_compatibilities` AS `subst_ac`' printf ' ON `subst_ac`.`built_for`=`subst_ba`.`architecture`' printf ' AND `subst_ac`.`runs_on`=%s' \ "${arch_id}" printf ' AND `subst_ac`.`fully_compatible`' mysql_join_binary_packages_in_repositories_install_target_providers 'subst_bpir' 'subst_itp' mysql_join_install_target_providers_versions 'subst_itp' 'subst_itp_v' printf ' WHERE `subst_itp`.`install_target`=`dependencies`.`depending_on`' printf ' AND ' mysql_query_ordering_correct \ '`dependencies_versions`.`order`' \ '`subst_itp_v`.`order`' \ '`dependencies`.`version_relation`' printf ')' printf ');\n' } | \ mysql_run_query | \ tr '\t' ' ' | \ tsort 2>&1 >/dev/null | \ sed 's/^tsort:\s*//' done | \ awk ' BEGIN { i=0 }; /^-: input contains a loop:$/ { i=i+1 }; !/^-: input contains a loop:$/ { print i "\t" $1 } ' > \ "${tmp_file}" { printf 'DELETE FROM `build_dependency_loops`;\n' printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `build_dependency_loops` (`loop`,`build_assignment`);\n' \ "${tmp_file}" } | \ mysql_run_query ) } # mysql_cleanup [dry] # clean up left overs from mysql database mysql_cleanup() { local operator if [ "$#" = '0' ]; then operator='DELETE' elif [ "$#" = '1' ] && [ "x$1" = 'xdry' ]; then operator='SELECT COUNT(1)' else >&2 echo 'Unknown parameter' >&2 echo 'Call "mysql_clean_up" or "mysql_clean_up dry".' exit 2 fi { # remove to-be-decided binary_packages_in_repositories printf '%s ' \ "${operator}" if [ "${operator}" = 'DELETE' ]; then printf '`binary_packages_in_repositories` ' fi printf 'FROM `binary_packages`' mysql_join_binary_packages_binary_packages_in_repositories printf ' WHERE `binary_packages_in_repositories`.`repository`=%s;\n' \ "${repository_ids__any_to_be_decided}" # remove virtual binary_packages w/o binary_packages_in_repositories printf '%s ' \ "${operator}" if [ "${operator}" = 'DELETE' ]; then printf '`binary_packages` ' fi printf 'FROM `binary_packages`' printf ' WHERE `binary_packages`.`sha512sum` IS NULL' printf ' AND NOT EXISTS (' printf 'SELECT 1' printf ' FROM `binary_packages_in_repositories` ' printf 'WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`' printf ');\n' # remove build_assignments w/o binary_package printf '%s FROM `build_assignments` ' \ "${operator}" printf 'WHERE NOT EXISTS ' printf '(' printf 'SELECT 1' printf ' FROM `binary_packages` ' printf 'WHERE `binary_packages`.`build_assignment`=`build_assignments`.`id`' printf ');\n' # remove failed_builds with unbroken build_assignments printf '%s ' \ "${operator}" if [ "${operator}" = 'DELETE' ]; then printf '`failed_builds` ' fi printf 'FROM `failed_builds` ' mysql_join_failed_builds_build_assignments printf 'WHERE NOT `build_assignments`.`is_broken`' printf ';\n' # remove package_sources w/o build_assignment printf '%s FROM `package_sources` ' \ "${operator}" printf 'WHERE NOT EXISTS ' printf '(' printf 'SELECT 1' printf ' FROM `build_assignments` ' printf 'WHERE `build_assignments`.`package_source`=`package_sources`.`id`' printf ');\n' # remove jobs from build slaves that are not on the build-list if [ "${operator}" = 'DELETE' ]; then printf 'UPDATE `binary_packages_in_repositories`' else printf 'SELECT COUNT(DISTINCT `build_slaves`.`id`)' printf ' FROM `binary_packages_in_repositories`' fi mysql_join_binary_packages_in_repositories_binary_packages printf ' AND `binary_packages_in_repositories`.`repository`=%s' \ "${repository_ids__any_build_list}" printf ' RIGHT' mysql_join_binary_packages_build_slaves if [ "${operator}" = 'DELETE' ]; then printf ' SET `build_slaves`.`currently_building`=NULL' fi printf ' WHERE `build_slaves`.`currently_building` IS NOT NULL' printf ' AND `binary_packages_in_repositories`.`id` IS NULL;\n' # remove build orders from build slaves which have not connected within 1h if [ "${operator}" = 'DELETE' ]; then printf 'UPDATE `build_slaves`' printf ' SET `build_slaves`.`currently_building`=NULL' else printf 'SELECT COUNT(1)' printf ' FROM `build_slaves`' fi printf ' WHERE `build_slaves`.`currently_building` IS NOT NULL' printf ' AND TIMEDIFF(NOW(),(' printf 'SELECT MAX(`ssh_log`.`date`)' printf ' FROM `ssh_log`' printf ' WHERE `ssh_log`.`build_slave`=`build_slaves`.`id`' printf ')) > "1:00:00";\n' # remove `last_action`, `trials` and `logged_lines` from buildslaves without an assignment if [ "${operator}" = 'DELETE' ]; then printf 'UPDATE `build_slaves`' printf ' SET `build_slaves`.`last_action`=NULL,' printf ' `build_slaves`.`logged_lines`=NULL,' printf ' `build_slaves`.`trials`=NULL' else printf 'SELECT COUNT(1)' printf ' FROM `build_slaves`' fi printf ' WHERE `build_slaves`.`currently_building` IS NULL' printf ' AND (`build_slaves`.`last_action` IS NOT NULL' printf ' OR `build_slaves`.`logged_lines` IS NOT NULL' printf ' OR `build_slaves`.`trials` IS NOT NULL);\n' # shrink the priorities printf 'CREATE TEMPORARY TABLE `ps%s`(`old_p` SMALLINT, `new_p` SMALLINT, UNIQUE KEY `old_p`(`old_p`));\n' \ '' '_2' '_3' printf 'INSERT INTO `ps`(`old_p`) VALUES (0);\n' printf 'INSERT IGNORE INTO `ps`(`old_p`)' printf ' SELECT `build_assignments`.`priority`' printf ' FROM `build_assignments`;\n' for copy in '2' '3'; do printf 'INSERT INTO `ps_%s`(`old_p`)' \ "${copy}" printf ' SELECT `ps`.`old_p`' printf ' FROM `ps`;\n' done printf 'UPDATE `ps`' printf ' LEFT JOIN (' printf 'SELECT' printf ' `ps_2`.`old_p` AS `limit_p`,' printf 'COUNT(`ps_3`.`old_p`) AS `count_p`' printf ' FROM `ps_2`' printf ' JOIN `ps_3`' printf ' ON `ps_3`.`old_p`<`ps_2`.`old_p`' printf ' GROUP BY `ps_2`.`old_p`' printf ') AS `ps_q`' printf ' ON `ps_q`.`limit_p`=`ps`.`old_p`' printf ' SET `ps`.`new_p`=IFNULL(`ps_q`.`count_p`,0)' printf ';\n' printf 'UPDATE `build_assignments`' printf ' JOIN `ps`' printf ' ON `build_assignments`.`priority`=`ps`.`old_p`' printf ' SET `build_assignments`.`priority`=`ps`.`new_p`;\n' printf 'DROP TEMPORARY TABLE `ps%s`;\n' \ '' '_2' '_3' } | \ mysql_run_query 'unimportant' } # mysql_query_has_pending_dependencies builder-architecture.id `build_assignment`.`id` # print a mysql query giving whether dependencies are pending (unsuccessfully built # dependencies do not count after 1 day) mysql_query_has_pending_dependencies() { # we have pending dependencies ... printf 'EXISTS (' printf 'SELECT 1' printf ' FROM `binary_packages` AS `todos`' mysql_join_binary_packages_binary_packages_in_repositories 'todos' 'todo_bpirs' printf ' AND `todo_bpirs`.`repository`=%s' \ "${repository_ids__any_build_list}" # ... if any dependency ... mysql_join_binary_packages_dependencies 'todos' 'l_deps' mysql_join_dependencies_versions 'l_deps' 'l_dep_vs' mysql_join_dependencies_dependency_types 'l_deps' 'l_dep_ts' mysql_join_dependencies_install_targets 'l_deps' # ... is relevant for building ... printf ' AND `l_dep_ts`.`relevant_for_building`' printf ' WHERE `todos`.`build_assignment`=%s' \ "$2" printf ' AND IF(' # ... and if (a) relevant for binary_packages ... printf '`l_dep_ts`.`relevant_for_binary_packages`' # (which includes makedependencies which are haskell packages!) printf ' OR `install_targets`.`name` LIKE "haskell-%%",' # ... has some unbuilt provider ... printf 'EXISTS (' printf 'SELECT 1' printf ' FROM `install_target_providers` AS `l_itps`' mysql_join_install_target_providers_versions 'l_itps' 'l_itp_vs' mysql_join_install_target_providers_binary_packages 'l_itps' 'dep_bp' mysql_join_binary_packages_binary_packages_in_repositories 'dep_bp' 'dep_bpir' printf ' AND `dep_bpir`.`repository`=%s' \ "${repository_ids__any_build_list}" mysql_join_binary_packages_build_assignments 'dep_bp' 'dep_ba' # ignore dependencies which are broken >=1 day printf ' AND (' printf 'NOT EXISTS (' printf 'SELECT 1' printf ' FROM `failed_builds`' printf ' WHERE `failed_builds`.`date`<=ADDTIME(NOW(),"-1 00:00:00")' printf ' AND `failed_builds`.`build_assignment`=`dep_ba`.`id`' printf ')' # haskell-* is a hopeless case in this regard printf ' OR `binary_packages`.`name` LIKE "haskell-%%"' printf ')' printf ' JOIN `architecture_compatibilities`' printf ' ON `architecture_compatibilities`.`built_for`=`dep_bp`.`architecture`' printf ' AND `architecture_compatibilities`.`fully_compatible`' printf ' WHERE `l_itps`.`install_target`=`l_deps`.`depending_on`' printf ' AND `dep_bp`.`build_assignment`!=%s' \ "$2" printf ' AND `architecture_compatibilities`.`runs_on`=%s' \ "$1" # version is irelevant: either the package is built and we don't # care or the package is not built and we don't know printf '),' # ... or (b) not relevant for binary_packages (e.g. a makedepends) has # no built provider printf 'NOT EXISTS (' printf 'SELECT 1' printf ' FROM `install_target_providers` AS `l_itps`' mysql_join_install_target_providers_versions 'l_itps' 'l_itp_vs' mysql_join_install_target_providers_binary_packages 'l_itps' 'dep_bp' mysql_join_binary_packages_binary_packages_in_repositories 'dep_bp' 'dep_bpir' mysql_join_binary_packages_in_repositories_repositories 'dep_bpir' 'dep_r' printf ' WHERE `l_itps`.`install_target`=`l_deps`.`depending_on`' printf ' AND (' printf '`dep_r`.`is_on_master_mirror`' printf ' OR `dep_bp`.`build_assignment`=%s' \ "$2" printf ') AND `dep_r`.`architecture`=%s' \ "$1" printf ' AND ' mysql_query_ordering_correct \ '`l_dep_vs`.`order`' \ '`l_itp_vs`.`order`' \ '`l_deps`.`version_relation`' printf ')' printf ')' printf ')' } # mysql_query_generate_packages_with_pending_dependencies # print the query to generate the temporary table # `packages_with_pending_dependencies` mysql_query_generate_packages_with_pending_dependencies() { printf 'CREATE TEMPORARY TABLE `install_target_statuses`(' printf '`id` BIGINT NOT NULL,' printf '`version_relation` VARCHAR(2) NOT NULL,' printf '`version_order` BIGINT NOT NULL,' printf '`builder_architecture` SMALLINT NOT NULL,' printf '`exists_built` BIT NOT NULL,' printf '`exists_unbuilt` BIT NOT NULL,' printf 'UNIQUE KEY `content`(`id`,`builder_architecture`),' printf 'KEY(`id`),' printf 'KEY(`builder_architecture`)' printf ');\n' printf 'INSERT IGNORE INTO `install_target_statuses`(' printf '`id`,' printf '`version_relation`,' printf '`version_order`,' printf '`builder_architecture`,' printf '`exists_built`,' printf '`exists_unbuilt`' printf ') SELECT ' printf '`dependencies`.`%s`,' \ 'depending_on' \ 'version_relation' printf '`versions`.`order`,' printf '`architectures`.`id`,0,0' printf ' FROM `dependencies`' mysql_join_dependencies_versions printf ' JOIN `architectures`;\n' printf 'UPDATE `install_target_statuses`' mysql_join_install_targets_install_target_providers 'install_target_statuses' mysql_join_install_target_providers_versions printf ' AND' mysql_query_ordering_correct \ '`install_target_statuses`.`version_order`' \ '`versions`.`order`' \ '`install_target_statuses`.`version_relation`' mysql_join_install_target_providers_binary_packages printf ' AND (' printf '`binary_packages`.`architecture`=`install_target_statuses`.`builder_architecture`' printf ' OR `binary_packages`.`architecture`=%s' \ "${architecture_ids__any}" printf ' OR `install_target_statuses`.`builder_architecture`=%s' \ "${architecture_ids__any}" printf ')' mysql_join_binary_packages_binary_packages_in_repositories printf ' SET `install_target_statuses`.`exists_unbuilt`=1' printf ' WHERE `binary_packages_in_repositories`.`repository`=%s;\n' \ "${repository_ids__any_build_list}" printf 'UPDATE `install_target_statuses`' mysql_join_install_targets_install_target_providers 'install_target_statuses' mysql_join_install_target_providers_versions printf ' AND' mysql_query_ordering_correct \ '`install_target_statuses`.`version_order`' \ '`versions`.`order`' \ '`install_target_statuses`.`version_relation`' mysql_join_install_target_providers_binary_packages printf ' AND (' printf '`binary_packages`.`architecture`=`install_target_statuses`.`builder_architecture`' printf ' OR `binary_packages`.`architecture`=%s' \ "${architecture_ids__any}" printf ' OR `install_target_statuses`.`builder_architecture`=%s' \ "${architecture_ids__any}" printf ')' mysql_join_binary_packages_binary_packages_in_repositories mysql_join_binary_packages_in_repositories_repositories printf ' SET `install_target_statuses`.`exists_built`=1' printf ' WHERE `repositories`.`is_on_master_mirror`;\n' printf 'CREATE TEMPORARY TABLE `packages_with_pending_dependencies`(' printf '`builder_architecture` SMALLINT NOT NULL,' printf '`build_assignment` BIGINT NOT NULL,' printf 'UNIQUE KEY `content`(`builder_architecture`,`build_assignment`),' printf 'KEY(`builder_architecture`),' printf 'KEY(`build_assignment`)' printf ');\n' printf 'INSERT IGNORE INTO `packages_with_pending_dependencies`(' printf '`builder_architecture`,' printf '`build_assignment`' printf ') SELECT' printf ' `install_target_statuses`.`builder_architecture`,' printf '`build_assignments`.`id`' printf ' FROM `install_target_statuses`' printf ' JOIN `install_targets`' printf ' ON `install_targets`.`id`=`install_target_statuses`.`id`' mysql_join_install_targets_dependencies printf ' AND `dependencies`.`version_relation`=`install_target_statuses`.`version_relation`' mysql_join_dependencies_versions printf ' AND `versions`.`order`=`install_target_statuses`.`version_order`' mysql_join_dependencies_dependency_types printf ' AND `dependency_types`.`relevant_for_building`' printf ' AND (' printf 'NOT `install_target_statuses`.`exists_built`' printf 'OR (' printf '`install_target_statuses`.`exists_unbuilt`' printf ' AND (' printf '`dependency_types`.`relevant_for_binary_packages`' printf ' OR `install_targets`.`name` LIKE "haskell-%%"' printf ')' printf ')' printf ')' mysql_join_dependencies_binary_packages mysql_join_binary_packages_binary_packages_in_repositories printf ' AND `binary_packages_in_repositories`.`repository`=%s' \ "${repository_ids__any_build_list}" mysql_join_binary_packages_build_assignments printf ' WHERE `build_assignments`.`architecture`=`install_target_statuses`.`builder_architecture`' printf ' OR `build_assignments`.`architecture`=%s' \ "${architecture_ids__any}" printf ' OR `install_target_statuses`.`builder_architecture`=%s' \ "${architecture_ids__any}" printf ';\n' printf 'DROP TEMPORARY TABLE `install_target_statuses`;\n' } # mysql_query_is_part_of_loop `build_assignment`.`id` # print a mysql query giving whether the package is part of a loop mysql_query_is_part_of_loop() { printf 'EXISTS (' printf 'SELECT 1' printf ' FROM `build_dependency_loops`' printf ' WHERE `build_dependency_loops`.`build_assignment`=%s' \ "$1" printf ')' } # mysql_query_select_pkgbase_and_revision # print the part of a mysql query giving: # pkgbase git_revision mod_git_revision upstream_package_repository mysql_query_select_pkgbase_and_revision() { printf '`package_sources`.`%s`,' \ 'pkgbase' \ 'git_revision' \ 'mod_git_revision' printf '`upstream_repositories`.`name`' printf ' FROM `build_assignments`' mysql_join_build_assignments_package_sources mysql_join_package_sources_upstream_repositories } # mysql_package_name_query [binary_packages] [architectures] [compressions] # print a mysql query of the full name of a package file mysql_package_name_query() { local bp_name="${1:-binary_packages}" local a_name="${2:-architectures}" local c_name="${3:-compressions}" printf 'CONCAT(' printf '`%s`.`pkgname`,"-",' "${bp_name}" printf 'IF(`%s`.`epoch`=0,"",CONCAT(`%s`.`epoch`,":")),' "${bp_name}" "${bp_name}" printf '`%s`.`pkgver`,"-",' "${bp_name}" printf '`%s`.`pkgrel`,' "${bp_name}" printf 'IF(`%s`.`sub_pkgrel_omitted`,"",CONCAT(".",`%s`.`sub_pkgrel`)),"-",' "${bp_name}" "${bp_name}" printf '`%s`.`name`,".pkg.",' "${a_name}" # TODO: do we really want to define the default compression this way? printf 'IF(' printf '`%s`.`suffix` IS NULL,' "${c_name}" printf '"tar.xz",' printf '`%s`.`suffix`' "${c_name}" printf ')' printf ')' } # mysql_join_*_* # print 'JOIN' part of mysql query to connect the respective tables # these functions take 2 optional arguments, acting as aliases for # the tables # mysql_join__generic $table_a $column_a $table_b $column_b # create mysql_join_${table_a}_${table_b}() function mysql_join__generic() { eval "$( printf 'mysql_join_%s_%s() {\n' "$1" "$3" printf ' printf '"'"' JOIN `%s`'"'"'\n' "$3" printf ' if [ -n "$2" ]; then\n' printf ' printf '"'"' AS `%%s`'"'"' "$2"\n' printf ' fi\n' printf ' if [ -n "$1" ]; then\n' printf ' printf '"'"' ON `%%s`.`%s`='"'"' "$1"\n' "$2" printf ' else\n' printf ' printf '"'"' ON `%s`.`%s`='"'"'\n' "$1" "$2" printf ' fi\n' printf ' if [ -n "$2" ]; then\n' printf ' printf '"'"'`%%s`.`%s`'"'"' "$2"\n' "$4" printf ' else\n' printf ' printf '"'"'`%s`.`%s`'"'"'\n' "$3" "$4" printf ' fi\n' printf '}\n' )" } for link in \ 'allowed_email_actions:action:email_actions' \ 'allowed_email_actions:gpg_key:gpg_keys' \ \ 'binary_packages:architecture:architectures' \ 'binary_packages:build_assignment:build_assignments' \ 'binary_packages:compression:compressions' \ \ 'binary_packages_in_repositories:package:binary_packages' \ 'binary_packages_in_repositories:repository:repositories' \ \ 'build_assignments:architecture:architectures' \ 'build_assignments:package_source:package_sources' \ \ 'build_dependency_loops:build_assignment:build_assignments' \ 'build_dependency_loops:build_assignment build_assignment:binary_packages' \ \ 'build_slaves:currently_building:build_assignments' \ 'build_slaves:currently_building build_assignment:binary_packages' \ 'build_slaves:ssh_key:ssh_keys' \ \ 'dependencies:depending_on:install_targets' \ 'dependencies:dependent:binary_packages' \ 'dependencies:dependent package:binary_packages_in_repositories' \ 'dependencies:dependency_type:dependency_types' \ 'dependencies:version:versions' \ \ 'email_log:action:email_actions' \ 'email_log:gpg_key:gpg_keys' \ \ 'failed_builds:reason:fail_reasons' \ 'failed_builds:build_assignment:build_assignments' \ 'failed_builds:build_slave:build_slaves' \ \ 'gpg_keys:owner:persons' \ \ 'install_target_providers:package:binary_packages' \ 'install_target_providers:package package:binary_packages_in_repositories' \ 'install_target_providers:install_target:install_targets' \ 'install_target_providers:install_target depending_on:dependencies' \ 'install_target_providers:version:versions' \ \ 'package_sources:upstream_package_repository:upstream_repositories' \ 'package_sources:pkgbase pkgbase:toolchain_order' \ \ 'repositories:stability:repository_stabilities' \ 'repositories:architecture:architectures' \ \ 'repository_moves:upstream_package_repository:upstream_repositories' \ 'repository_moves:upstream_package_repository upstream_package_repository:package_sources' \ \ 'ssh_keys:owner:persons' \ \ 'ssh_log:build_slave:build_slaves' \ \ 'upstream_repositories:git_repository:git_repositories'; do # A join for these cannot be done, because it's not clear on what to join: # 'repository_stability_relations:more_stable:repository_stabilities' # 'repository_stability_relations:less_stable:repository_stabilities' table_b="${link##*:}" table_a="${link%:*}" column_b="${table_a##*:}" table_a="${table_a%:*}" column_a="${column_b% *}" if [ "${column_a}" = "${column_b}" ]; then column_b='id' else column_b="${column_b##* }" fi mysql_join__generic "${table_a}" "${column_a}" "${table_b}" "${column_b}" mysql_join__generic "${table_b}" "${column_b}" "${table_a}" "${column_a}" done # mysql_join_install_target_providers_dependencies_with_versions() and # mysql_join_dependencies_install_target_providers_with_versions() also # join via `versions` mysql_join_install_target_providers_dependencies_with_versions() { mysql_join_install_target_providers_dependencies "$1" "$2" mysql_join_install_target_providers_versions "$1" "${1:-install_target_providers}_versions" mysql_join_dependencies_versions "$2" "${2:-dependencies}_versions" printf ' AND ' mysql_query_ordering_correct \ '`'"${2:-dependencies}_versions"'`.`order`' \ '`'"${1:-install_target_providers}_versions"'`.`order`' \ '`'"${2:-dependencies}"'`.`version_relation`' } mysql_join_dependencies_install_target_providers_with_versions() { local relation mysql_join_dependencies_install_target_providers "$1" "$2" mysql_join_install_target_providers_versions "$2" "${2:-install_target_providers}_versions" mysql_join_dependencies_versions "$1" "${1:-dependencies}_versions" printf ' AND ' mysql_query_ordering_correct \ '`'"${1:-dependencies}_versions"'`.`order`' \ '`'"${2:-install_target_providers}_versions"'`.`order`' \ '`'"${1:-dependencies}"'`.`version_relation`' } # mysql_retrieve_static_information # retrieve some static information from the database: # - ids of architectures -> $architecture_ids__$arch # - ids of non-any architectures -> $non_any_architecture_ids # - ids of package repositories -> $repository_ids__$arch_$repo # - names, paths and heads of git_repositories # - ids of repository_stabilities mysql_retrieve_static_information() { eval "$( { printf 'SELECT REPLACE(CONCAT(' printf '"architecture_ids__",' printf '`architectures`.`name`,"=",' printf '`architectures`.`id`' printf '),"-","_")' printf ' FROM `architectures`;\n' printf 'SELECT CONCAT(' printf '"non_any_architecture_ids=",' printf 'GROUP_CONCAT(' printf '`architectures`.`id`' printf ' ORDER BY `architectures`.`id`' printf '))' printf ' FROM `architectures`' printf ' WHERE `architectures`.`name`!="any";\n' printf 'SELECT REPLACE(CONCAT(' printf '"repository_ids__",' printf '`architectures`.`name`,"_",' printf '`repositories`.`name`,"=",' printf '`repositories`.`id`' printf '),"-","_")' printf ' FROM `repositories`' mysql_join_repositories_architectures printf ';\n' printf 'SELECT CONCAT(' printf '"repo_names=\\"",' printf 'REPLACE(GROUP_CONCAT(`git_repositories`.`name`),","," "),' printf '"\\"")' printf ' FROM `git_repositories`' printf ' GROUP BY "1";\n' printf 'SELECT CONCAT(' printf '"repo_paths__",' printf '`git_repositories`.`name`,"=",' printf '"\\"",`git_repositories`.`directory`,"\\";' printf 'repo_heads__",' printf '`git_repositories`.`name`,"=",' printf '"\\"",`git_repositories`.`head`,"\\"")' printf ' FROM `git_repositories`;\n' printf 'SELECT CONCAT(' printf '"repository_stability_ids__",' printf '`repository_stabilities`.`name`,"=",' printf '`repository_stabilities`.`id`)' printf ' FROM `repository_stabilities`;\n' printf 'SELECT REPLACE(CONCAT(' printf '"compression_ids__",' printf 'REPLACE(' printf '`compressions`.`suffix`,".","_"' printf '),"=",' printf '`compressions`.`id`' printf '),"-","_")' printf ' FROM `compressions`' } | \ mysql_run_query )" } # mysql_query_and_delete_unneeded_binary_packages # print a query which lists and deletes binary_packages which are not # linked from binary_packages_in_repositories mysql_query_and_delete_unneeded_binary_packages() { printf 'SELECT CONCAT("pool/",' mysql_package_name_query printf ') FROM `binary_packages`' mysql_join_binary_packages_architectures printf ' LEFT' mysql_join_binary_packages_compressions printf ' WHERE NOT EXISTS (' printf 'SELECT 1' printf ' FROM `binary_packages_in_repositories`' printf ' WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`' printf ');\n' printf 'DELETE `binary_packages`' printf ' FROM `binary_packages`' printf ' WHERE NOT EXISTS (' printf 'SELECT 1' printf ' FROM `binary_packages_in_repositories`' printf ' WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`' printf ');\n' } # mysql_sort_versions [-f] # sort the table of versions, iff versions without order exist or -f was # given mysql_sort_versions() { ( # new shell is intentional tmp_file=$(mktemp 'tmp.mysql_sort_versions.XXXXXXXXXX' --tmpdir) trap 'rm "${tmp_file}"' EXIT if [ "x$1" != 'x-f' ] && \ { printf 'SELECT' printf ' COUNT(1)' printf ' FROM `versions`' printf ' WHERE `versions`.`order` IS NULL' } | \ mysql_run_query | \ grep -qxF '0'; then # nothing to do exit 0 fi { printf 'SELECT ' printf '`versions`.`%s`,' \ 'id' \ 'epoch' \ 'version' | \ sed 's/,$//' printf ' FROM `versions`;\n' } | \ mysql_run_query | \ expand_version 3 | \ sort -k2n,2 -k3V,3 | \ cut -f1 | \ cat -n | \ awk '{print $1 " " $2}' | \ tr ' ' '\t' > \ "${tmp_file}" max_order=$( { printf 'SELECT' printf ' GREATEST(%s,' \ "$( wc -l < "${tmp_file}" )" printf 'MAX(`versions`.`order`))' printf ' FROM `versions`;\n' } | \ mysql_run_query ) { printf 'CREATE TEMPORARY TABLE `vs`(' printf '`id` BIGINT,' printf '`ord` BIGINT,' printf 'UNIQUE KEY(`id`),' printf 'UNIQUE KEY(`ord`)' printf ');\n' printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `vs`(`ord`,`id`);\n' \ "${tmp_file}" printf 'UPDATE `versions`' printf ' SET `versions`.`order`=`versions`.`order`+1+%s;\n' \ "${max_order}" printf 'COMMIT;\n' printf 'UPDATE `versions`' printf ' JOIN `vs`' printf ' ON `vs`.`id`=`versions`.`id`' printf ' SET `versions`.`order`=`vs`.`ord`;\n' } | \ mysql_run_query ) } # mysql_load_min_and_max_versions # load the minimal and maximal versions into min_version and max_version mysql_load_min_and_max_versions() { if [ -z "${min_version}" ]; then min_version=$( { printf 'SELECT CONCAT(`versions`.`epoch`,":",`versions`.`version`)' printf ' FROM `versions`' printf ' WHERE `versions`.`order` IS NOT NULL' printf ' ORDER BY `versions`.`order`' printf ' LIMIT 1' } | \ mysql_run_query ) fi if [ -z "${max_version}" ]; then max_version=$( { printf 'SELECT CONCAT(`versions`.`epoch`,":",`versions`.`version`)' printf ' FROM `versions`' printf ' WHERE `versions`.`order` IS NOT NULL' printf ' ORDER BY `versions`.`order` DESC' printf ' LIMIT 1' } | \ mysql_run_query ) fi if [ -z "${min_version_id}" ]; then min_version_id=$( { printf 'SELECT `versions`.`id`' printf ' FROM `versions`' printf ' WHERE `versions`.`order` IS NOT NULL' printf ' ORDER BY `versions`.`order`' printf ' LIMIT 1' } | \ mysql_run_query ) fi if [ -z "${max_version_id}" ]; then max_version_id=$( { printf 'SELECT `versions`.`id`' printf ' FROM `versions`' printf ' WHERE `versions`.`order` IS NOT NULL' printf ' ORDER BY `versions`.`order` DESC' printf ' LIMIT 1' } | \ mysql_run_query ) fi } # mysql_query_remove_old_binary_packages_from_build_list # print a query to remove old versions of packages from the build-list mysql_query_remove_old_binary_packages_from_build_list() { printf 'DELETE `d_bpir`' printf ' FROM `binary_packages_in_repositories` AS `d_bpir`' mysql_join_binary_packages_in_repositories_binary_packages 'd_bpir' 'd_bp' printf ' JOIN `versions` as `d_v`' printf ' ON `d_bp`.`epoch`=`d_v`.`epoch`' printf ' AND `d_bp`.`pkgver`=`d_v`.`version`' printf ' JOIN `binary_packages` AS `n_bp`' printf ' ON `d_bp`.`pkgname`=`n_bp`.`pkgname`' printf ' JOIN `versions` as `n_v`' printf ' ON `n_bp`.`epoch`=`n_v`.`epoch`' printf ' AND `n_bp`.`pkgver`=`n_v`.`version`' mysql_join_binary_packages_binary_packages_in_repositories 'n_bp' 'n_bpir' printf ' WHERE `d_bpir`.`repository`=%s' \ "${repository_ids__any_build_list}" printf ' AND `n_bpir`.`repository`=%s' \ "${repository_ids__any_build_list}" printf ' AND (' printf '`n_v`.`order`>`d_v`.`order`' printf ' OR (' printf '`n_v`.`order`=`d_v`.`order`' printf ' AND (' printf '`n_bp`.`pkgrel`>`d_bp`.`pkgrel`' printf ' OR (' printf '`n_bp`.`pkgrel`=`d_bp`.`pkgrel`' printf ' AND `n_bp`.`sub_pkgrel`>`d_bp`.`sub_pkgrel`' printf ')' printf ')' printf ')' printf ');\n' printf 'COMMIT;\n' mysql_query_and_delete_unneeded_binary_packages | \ grep -v '^SELECT ' } # mysql_query_ordering_correct dependency.version.order install_target_provider.version.order dependency.version_relation # print a query yielding wether the ordering is correct mysql_query_ordering_correct() { local relation printf '(' for relation in '<' '<=' '>' '>=' '='; do printf '(' printf '%s="%s"' \ "${3}" \ "${relation}" printf ' AND %s%s%s' \ "${2}" \ "${relation}" \ "${1}" printf ') OR ' done | \ sed ' s/ OR $// ' printf ')' } # mysql_determine_majority_build_slave_architecture_id # retreive the `architectures`.`id` of the majority of the currently # active build slaves mysql_determine_majority_build_slave_architecture_id() { { printf 'SELECT' printf ' COUNT(DISTINCT `ssh_log`.`id`) AS `count`,' printf '`architectures`.`id`' printf ' FROM `ssh_log`' printf ' JOIN `architectures`' printf ' ON `ssh_log`.`parameters`=CONCAT(`architectures`.`name`," ")' printf ' WHERE `action`="get-assignment"' printf ' AND `ssh_log`.`date`>ADDTIME(NOW(),"-1 00:00:00")' printf ' GROUP BY `ssh_log`.`parameters`' printf ' ORDER BY `count` DESC' printf ' LIMIT 1' } | \ mysql_run_query | \ cut -f2 }