#!/bin/sh # contains functions used to access mysql db # shellcheck disable=SC2016,SC2039,SC2119,SC2120 if [ -z "${base_dir}" ]; then # just to make shellcheck happy . '../lib/load-configuration' fi # TODO: replace most/all insert-select queries by separate select and insert # queries # TODO: normally operate on `binary_packages_in_repositories` instead of # `binary_packages` # TODO: consider `binary_packages` as cache of # `binary_packages_in_repositories` (e.g. delete, when no # `binary_packages_in_repositories` needs `binary_packages` anymore, # otherwise let them untouched) # 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 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.stdin.$(date +'%Y-%m-%dT%T').XXXXXX") query_stdout=$(mktemp "${work_dir}/tmp.mysql-functions.${file_name_extra}query.stdout.$(date +'%Y-%m-%dT%T').XXXXXX") query_stderr=$(mktemp "${work_dir}/tmp.mysql-functions.${file_name_extra}query.stderr.$(date +'%Y-%m-%dT%T').XXXXXX") cat > "${query_stdin}" for _ in {1..10}; do ${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 could not complete a mysql query!\n' 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 \ "${query_stdin}" \ "${query_stdout}" \ "${query_stderr}"; do cp "${file}" "${webserver_directory}/mysql-queries/${file##*/}.txt" chmod go+r "${webserver_directory}/mysql-queries/${file##*/}.txt" done if [ -z "${file_name_extra}" ]; then echo 'A mysql query failed.' > \ "${work_dir}/build-master-sanity" else rm -f \ "${query_stdin}" \ "${query_stdout}" \ "${query_stderr}" fi return 2 fi cat "${query_stdout}" rm "${query_stdout}" } # mysql_add_package_source $pkgbase $git_revision $old_git_revision $mod_git_revision $upstream_package_repository $srcinfo_file # $old_git_revision may be empty, in which case the whole history will be searched (slower) # shellcheck disable=SC2086 mysql_add_package_source() { local pkgbase local git_revision local old_git_revision local mod_git_revision local upstream_package_repository local srcinfo_file pkgbase="$1" git_revision="$2" old_git_revision="${3:+$3..}" mod_git_revision="$4" upstream_package_repository="$5" srcinfo_file="$6" local uses_upstream local uses_modification local repo local repo_path local commit_time local pkgbuild 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 eval 'repo_path="${repo_paths__'"$(sed -n 's/^upstream_git_repository = //;T;p' "${srcinfo_file}")"'}"' pkgbuild=$( sed -n ' s/^PKGBUILD = // T p ' "${srcinfo_file}" ) commit_time=$( git -C "${repo_path}" log -n 1 --pretty=format:%ct "${old_git_revision}${git_revision}" -- "${pkgbuild}" ) if [ -z "${commit_time}" ]; then # We may have modified git_mod_revision! commit_time=$( git -C "${repo_path}" log -n 1 --pretty=format:%ct "${git_revision}" -- "${pkgbuild}" ) fi else uses_upstream=0 commit_time=$(date '+%s') 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`,`commit_time`)' 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}" printf 'from_unixtime(%s))' \ "${commit_time}" printf ' ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`package_sources`.`id`);\n' printf 'SELECT LAST_INSERT_ID();\n' } | \ mysql_run_query } # TODO: return ids on INSERT queries and use those in subsequent queries # mysql_generate_package_metadata $current_repository_id $package $git_revision $old_git_revision $mod_git_revision $repository # generate the meta data of a package (dependencies, built packages, ...) in the database # $old_git_revision may be empty, in which case the whole history will be searched (slower) mysql_generate_package_metadata() { ( # new shell is intentional current_repository_id="$1" package="$2" git_revision="$3" old_git_revision="$4" mod_git_revision="$5" repository="$6" 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 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 # TODO: support more architectures sed -i ' /^[^=]*=\s*$/d /^\s*arch = /{ / \(i686\|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}" "${old_git_revision}" "${mod_git_revision}" "${repository}" "${temp_dir}/SRCINFO" ) # TODO: architectures should be identified by id, not "(SELECT ...)" # add the build assignment(s) { sed -n ' s/^\tarch = // T p ' "${temp_dir}/SRCINFO" | \ sort -u | \ grep -vxF 'any' || \ echo 'any' } | \ base64_encode_each | \ while read -r arch; do printf 'INSERT INTO `build_assignments` (`package_source`,`architecture`,`is_blocked`,`is_broken`,`priority`)' printf ' SELECT %s,`architectures`.`id`,NULL,0,0' \ "${package_source_id}" printf ' FROM `architectures`' printf ' WHERE `architectures`.`name` = from_base64("%s")' \ "${arch}" printf ' ON DUPLICATE KEY UPDATE `id`=LAST_INSERT_ID(`build_assignments`.`id`);\n' printf 'SELECT "%s",LAST_INSERT_ID();\n' \ "${arch}" done | \ mysql_run_query > \ "${temp_dir}/build-assignments.id" # select any specific arch (which will be building the 'any' part of a split package) any_arch=$( cut -f1 < \ "${temp_dir}/build-assignments.id" | \ sort | \ head -n1 | \ tr -d '\n' | \ base64 -w0 ) # iterate over all pkgnames grep '^pkgname = ' "${temp_dir}/SRCINFO" | \ cut -d' ' -f3 | \ while read -r pkgname; do pkgname64=$( printf '%s' "${pkgname}" | \ base64 -w0 ) sed -n ' /^pkgbase = \|^pkgname = '"$(str_to_regex "${pkgname}")"'$/,/^$/ { /^\S/d s/^\s*// p } ' "${temp_dir}/SRCINFO" > \ "${temp_dir}/BINARYINFO.${pkgname64}" # iterate over all archs grep '^arch = ' "${temp_dir}/BINARYINFO.${pkgname64}" | \ cut -d' ' -f3 | \ while read -r arch; do arch64=$( printf '%s' "${arch}" | \ base64 -w0 ) { { # 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 "^${arch64}"'\s' "${temp_dir}/build-assignments.id" || \ grep "^${any_arch}"'\s' "${temp_dir}/build-assignments.id" } | \ sed ' s/^.*\s/build-assignment-id = / ' sed ' s/^\(\S\+\)_'"${arch}"' = /\1 = / ' "${temp_dir}/BINARYINFO.${pkgname64}" } > \ "${temp_dir}/ARCHINFO ${pkgname64} ${arch64}" done done # TODO: This should become multiple mysql queries: # 1st inserting install_targets # 2nd inserting binary_packages (and binary_packages_in_repositories) # 3rd,4th inserting dependencies and install_target_providers # The links between the above should _solely_ be made by the ids of # the respective rows. # now each "ARCHINFO $pkgname $arch" file represents one binary package find "${temp_dir}" -mindepth 1 -maxdepth 1 -name 'ARCHINFO * *' -printf '%f\n' | \ while read -r _ pkgname arch; do build_assignment_id=$( grep '^build-assignment-id = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \ cut -d' ' -f3 ) pkgver=$( grep '^pkgver = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \ cut -d' ' -f3 | \ tr -d '\n' | \ base64 -w0 ) pkgrel=$( grep '^pkgrel = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \ cut -d' ' -f3 | \ sed 's/\.[0-9]\+$//' | \ tr -d '\n' | \ base64 -w0 ) epoch=$( { grep '^epoch = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" || \ echo 'epoch = 0' } | \ cut -d' ' -f3 | \ tr -d '\n' | \ base64 -w0 ) # TODO: allow versioned install_targets provides=$( grep '^\(groups\|provides\) = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \ cut -d' ' -f3 | \ sed 's/[<>=].*$//' | \ base64_encode_each ) makedepends=$( grep '^makedepends = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \ cut -d' ' -f3 | \ sed 's/[<>=].*$//' | \ base64_encode_each ) checkdepends=$( grep '^checkdepends = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \ cut -d' ' -f3 | \ sed 's/[<>=].*$//' | \ base64_encode_each ) rundepends=$( grep '^depends = ' "${temp_dir}/ARCHINFO ${pkgname} ${arch}" | \ cut -d' ' -f3 | \ sed 's/[<>=].*$//' | \ base64_encode_each ) sub_pkgrel=$( printf '(SELECT COALESCE(' # do not add binary packages which are currently on the # build-list or in $current_repository_id (beware of split # packages!) printf '(SELECT `sub_pkgrel` FROM `binary_packages`' mysql_join_binary_packages_architectures mysql_join_binary_packages_binary_packages_in_repositories printf ' WHERE' printf ' `binary_packages`.`%s`=from_base64("%s") AND' \ 'epoch' "${epoch}" \ 'pkgver' "${pkgver}" \ 'pkgrel' "${pkgrel}" \ 'pkgname' "${pkgname}" printf ' `architectures`.`name`=from_base64("%s")' \ "${arch}" printf ' AND `binary_packages_in_repositories`.`repository` IN (%s,%s)),' \ "${repository_ids__any_build_list}" \ "${current_repository_id}" # max(sub_pkgrel)+1 printf '(SELECT 1+MAX(`binary_packages`.`sub_pkgrel`) FROM `binary_packages`' mysql_join_binary_packages_architectures printf ' WHERE' printf ' `binary_packages`.`%s`=from_base64("%s") AND' \ 'epoch' "${epoch}" \ 'pkgver' "${pkgver}" \ 'pkgrel' "${pkgrel}" \ 'pkgname' "${pkgname}" if printf '%s' "${arch}" | base64 -d | grep -qxF 'any'; then # 'any' gets higher sub_pkgrel than any architecture printf ' 1' else # not-'any' gets higher sub_pkgrel than same or 'any' architecture printf ' (`architectures`.`name`=from_base64("%s") OR `architectures`.`name`="any")' \ "${arch}" fi printf ')' printf ',0))' ) { # TODO: identify arch by id printf 'INSERT IGNORE INTO `binary_packages` (' printf '`%s`,' \ 'build_assignment' \ 'architecture' \ 'epoch' \ 'pkgver' \ 'pkgrel' \ 'pkgname' \ 'sub_pkgrel' \ 'has_issues' \ 'is_tested' printf ') SELECT ' printf '%s,' \ "${build_assignment_id}" printf '`%s`.`id`,' \ 'architectures' printf 'from_base64("%s"),' \ "${epoch}" \ "${pkgver}" \ "${pkgrel}" \ "${pkgname}" printf '%s,0,0 FROM `architectures`' \ "${sub_pkgrel}" printf ' WHERE' printf ' `architectures`.`name` = from_base64("%s")' \ "${arch}" printf ';\n' printf 'INSERT IGNORE INTO `binary_packages_in_repositories` (`package`,`repository`,`is_to_be_deleted`)' printf ' SELECT LAST_INSERT_ID(),%s,0;\n' \ "${current_repository_id}" printf 'COMMIT;\n' } | \ sed ' s|,)|)|g s| JOIN WHERE | WHERE | s| AND;$|;| ' >> \ "${temp_dir}/add-binary-packages-command" { printf 'CREATE TEMPORARY TABLE `%s` (`name` VARCHAR(64));\n' \ 'provides' \ 'makedepends' \ 'checkdepends' \ 'rundepends' printf 'INSERT INTO `provides` VALUES\n' echo "${provides}" | \ sort -u | \ grep -vxF '' | \ sed ' s|^| (from_base64("| s|$|")),| ' printf ' (CONCAT(from_base64("%s"),"-",from_base64("%s"),":",from_base64("%s"),"-",from_base64("%s"))),\n' \ "${pkgname}" \ "${epoch}" \ "${pkgver}" \ "${pkgrel}" printf ' (CONCAT(from_base64("%s"),"-",from_base64("%s"),":",from_base64("%s"))),\n' \ "${pkgname}" \ "${epoch}" \ "${pkgver}" printf ' (from_base64("%s"));\n' \ "${pkgname}" printf 'INSERT INTO `rundepends` VALUES\n' echo "${rundepends}" | \ sort -u | \ grep -vxF '' | \ sed ' s|^| (from_base64("| s|$|")),| ' if printf '%s' "${pkgname}" | \ base64 -d | \ grep -q -- '-doc$'; then printf ' (CONCAT(from_base64("%s"),"-",from_base64("%s"),":",from_base64("%s"))),\n' \ "$(printf '%s' "${pkgname}" | base64 -d | sed 's/-doc$//' | base64 -w0)" \ "${epoch}" \ "${pkgver}" fi printf ' ("base");\n' echo "${checkdepends}" | \ sort -u | \ grep -vxF '' | \ sed ' s|^| (from_base64("| s|$|")),| 1 s/^/INSERT INTO `checkdepends` VALUES \n/ $ s/,$/;/ ' printf 'INSERT INTO `makedepends` VALUES\n' echo "${makedepends}" | \ sort -u | \ grep -vxF '' | \ sed ' s|^| (from_base64("| s|$|")),| ' printf ' ("base-devel");\n' printf 'INSERT IGNORE INTO `install_targets` (`name`)' printf ' SELECT (`name`) FROM `%s` UNION' \ 'provides' \ 'makedepends' \ 'checkdepends' \ 'rundepends' | \ sed 's| UNION$|;\n|' for link in 'provides' 'makedepends' 'checkdepends' 'rundepends'; do case "${link}" in 'provides') printf 'INSERT IGNORE INTO `install_target_providers` (`package`,`install_target`) SELECT' printf ' `binary_packages`.`id`,`install_targets`.`id` FROM' ;; 'makedepends'|'checkdepends'|'rundepends') printf 'INSERT IGNORE INTO `dependencies` (`dependent`,`depending_on`,`dependency_type`) SELECT' printf ' `binary_packages`.`id`,`install_targets`.`id`,`dependency_types`.`id` FROM' printf ' `dependency_types` JOIN' ;; esac printf ' `binary_packages`' mysql_join_binary_packages_architectures printf ' JOIN `install_targets`' printf ' JOIN `%s`' "${link}" printf ' ON `%s`.`name` = `install_targets`.`name`' "${link}" printf ' WHERE' if [ "${link}" = 'makedepends' ] || \ [ "${link}" = 'checkdepends' ] || \ [ "${link}" = 'rundepends' ]; then printf ' `dependency_types`.`name` = "%s" AND' \ "${link%depends}" fi # TODO: identify binary_packages by id printf ' `binary_packages`.`%s` = from_base64("%s") AND' \ 'epoch' "${epoch}" \ 'pkgver' "${pkgver}" \ 'pkgrel' "${pkgrel}" \ 'pkgname' "${pkgname}" # we do not want to match the sub_pkgrel: # a) it is tedious to do so (because it may be calculated # dynamically) # b) it is not necessary to do so: if only the sub_pkgrel # changed, the dependencies and provided install_targets # should not have changed printf ' `architectures`.`name` = from_base64("%s");\n' \ "${arch}" # the repository is of no relevance: it hardly matters for # the dependencies done printf 'DROP TABLE `%s`;\n' \ 'provides' \ 'makedepends' \ 'checkdepends' \ 'rundepends' } >> \ "${temp_dir}/add-install-targets-command" done { if [ -s "${temp_dir}/add-binary-packages-command" ]; then cat "${temp_dir}/add-binary-packages-command" fi if [ -s "${temp_dir}/add-install-targets-command" ]; then cat "${temp_dir}/add-install-targets-command" fi } | \ mysql_run_query ) } # mysql_sanity_check # do a sanity check on the mysql database mysql_sanity_check() { { printf 'SELECT CONCAT("\\"any\\" build-assignment building \\"",`bp_arch`.`name`,"\\" binary package: ",`binary_packages`.`pkgname`)' 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 ' WHERE `bp_arch`.`name`!="any"' printf ' AND `ba_arch`.`name`="any";\n' printf 'SELECT DISTINCT CONCAT("package multiple times in the same repository: ",`repositories`.`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' mysql_join_repositories_binary_packages_in_repositories '' 'b_bir' mysql_join_binary_packages_in_repositories_binary_packages 'b_bir' 'b' printf ' AND `a`.`pkgname`=`b`.`pkgname`' printf ' AND `a`.`architecture`=`b`.`architecture`' printf ' AND `a`.`id`!=`b`.`id`' printf ' WHERE `repositories`.`id` NOT IN (%s,%s);\n' \ "${repository_ids__any_deletion_list}" \ "${repository_ids__any_to_be_decided}" 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` 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 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 # shellcheck disable=SC2041 for arch in 'i686'; do for dir in $(ls_master_mirror "${arch}"); do ls_master_mirror "${arch}/${dir}" | \ sed -n ' s/\.pkg\.tar\.xz$// T s/-\([0-9]\+\)-\([^-]\+\)$/-\1.0-\2/ s/-\([^-:]\+-[^-]\+-[^-]\+\)$/-0:\1/ s|^|'"${arch}"'/'"${dir}"'/| p ' done done | \ sort > \ "${temp_dir}/master-mirror-listing" { printf 'SELECT `r_a`.`name`,`repositories`.`name`,' printf '`binary_packages`.`%s`,' \ 'pkgname' \ 'epoch' \ 'pkgver' \ 'pkgrel' \ 'sub_pkgrel' printf '`architectures`.`name`' printf ' FROM `binary_packages_in_repositories`' mysql_join_binary_packages_in_repositories_binary_packages mysql_join_binary_packages_architectures 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 -n ' s/\.pkg\.tar\.xz\(\.sig\)\?$// T s/-\([0-9]\+\)-\([^-]\+\)$/-\1.0-\2/ s/-\([^-:]\+-[^-]\+-[^-]\+\)$/-0:\1/ p ' | \ sort | \ uniq -c | \ grep '^\s*2\s' | \ awk '{print $2}' > \ "${temp_dir}/master-mirror-pool" { printf 'SELECT ' printf '`binary_packages`.`%s`,' \ 'pkgname' \ 'epoch' \ 'pkgver' \ 'pkgrel' \ 'sub_pkgrel' printf '`architectures`.`name`' printf ' FROM `binary_packages`' mysql_join_binary_packages_architectures printf ' WHERE NOT EXISTS (' printf 'SELECT 1 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 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,\t,-, s,\t,:, s,\t,-, s,\t,., s,\t,-, ' | \ sort > \ "${temp_dir}/mysql-packages-pool" diff -u \ "${temp_dir}/master-mirror-pool" \ "${temp_dir}/mysql-packages-pool" ) } mysql_find_build_assignment_loops() { new_loops=$( { printf 'SELECT DISTINCT `packages_dependency`.`build_assignment`,`packages_dependent`.`build_assignment`' printf ' FROM `dependencies`' mysql_join_dependencies_install_target_providers mysql_join_install_target_providers_binary_packages '' 'packages_dependency' mysql_join_dependencies_binary_packages '' 'packages_dependent' mysql_join_binary_packages_binary_packages_in_repositories 'packages_dependency' 'packages_in_repository_dependency' mysql_join_binary_packages_binary_packages_in_repositories 'packages_dependent' 'packages_in_repository_dependent' printf ' WHERE `packages_in_repositories_dependent`.`repository`=%s' \ "${repository_ids__any_build_list}" printf ' AND `packages_in_repositories_dependency`.`repository`=%s;\n' \ "${repository_ids__any_build_list}" } | \ mysql_run_query | \ tr '\t' ' ' | \ tsort 2>&1 >/dev/null | \ sed 's/^tsort:\s*//' | \ { loop=0 while read -r id; do if [ "x${id}" = 'x-: input contains a loop:' ]; then loop=$((loop+1)) continue fi if ! printf '%s' "${id}" | tr '\n' ' ' | grep -q '^[0-9]\+$'; then >&2 printf 'ERROR: non-numeric id "%s"\n' "${id}" continue fi printf '(%s,%s),' "${loop}" "${id}" done | \ sed 's/,$//' } ) { printf 'DELETE FROM `build_dependency_loops`;\n' if [ -n "${new_loops}" ]; then printf 'INSERT INTO `build_dependency_loops` (`loop`,`build_assignment`) VALUES %s;\n' \ "${new_loops}" fi } | \ 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 and binary_packages_in_repositories printf '%s ' \ "${operator}" if [ "${operator}" = 'DELETE' ]; then printf '`binary_packages`,`binary_packages_in_repositories` ' fi printf 'FROM `binary_packages`' mysql_join_binary_packages_binary_packages_in_repositories mysql_join_binary_packages_in_repositories_repositories printf ' WHERE `repositories`.`name`="to-be-decided";\n' # remove build_assignments w/o binary_package printf '%s FROM `build_assignments` ' \ "${operator}" printf 'WHERE NOT EXISTS ' printf '(' printf 'SELECT 1 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 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 `repositories`' else printf 'SELECT COUNT(DISTINCT `build_slaves`.`id`) FROM `repositories`' fi mysql_join_repositories_binary_packages_in_repositories mysql_join_binary_packages_in_repositories_binary_packages printf ' AND `repositories`.`name`="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 `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) FROM `build_slaves`' fi printf ' WHERE `build_slaves`.`currently_building` IS NOT NULL' printf ' AND TIMEDIFF(NOW(),(' printf 'SELECT MAX(`ssh_log`.`date`) 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) 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' } | \ mysql_run_query 'unimportant' } # mysql_query_has_pending_dependencies `build_assignment`.`id` # print a mysql query giving wether dependencies are pending mysql_query_has_pending_dependencies() { printf 'EXISTS (' printf 'SELECT 1 FROM `binary_packages_in_repositories` as `todos_bpir`' mysql_join_binary_packages_in_repositories_binary_packages 'todos_bpir' 'todos' mysql_join_binary_packages_dependencies 'todos' mysql_join_dependencies_install_target_providers mysql_join_install_target_providers_binary_packages '' 'deps' mysql_join_binary_packages_binary_packages_in_repositories 'deps' 'deps_bpir' printf ' WHERE' printf ' `%s`.`repository`=%s AND' \ 'deps_bpir' "${repository_ids__any_build_list}" \ 'todos_bpir' "${repository_ids__any_build_list}" printf ' `deps`.`build_assignment`!=`todos`.`build_assignment` AND' printf ' `todos`.`build_assignment`=%s' \ "$1" printf ')' } # mysql_query_is_part_of_loop `build_assignment`.`id` # print a mysql query giving wether the package is part of a loop mysql_query_is_part_of_loop() { printf 'EXISTS (' printf 'SELECT 1 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] # 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}" 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 '`%s`.`sub_pkgrel`,"-",' "${bp_name}" printf '`%s`.`name`,".pkg.tar.xz"' "${a_name}" 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_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:dependency_type:dependency_types' \ \ 'email_log:action:email_actions' \ 'email_log:gpg_key:gpg_keys' \ \ 'failed_builds:reason:fail_reason' \ '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' \ \ 'package_sources:upstream_package_repository:upstream_repositories' \ \ '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_retrieve_static_information # retrieve some static information from the database: # - ids of all known repositories -> $repository_ids__$arch_$repo mysql_retrieve_static_information() { eval "$( { printf 'SELECT REPLACE(CONCAT(' printf '"repository_ids__",' printf '`architectures`.`name`,"_",' printf '`repositories`.`name`,"=",' printf '`repositories`.`id`' 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 ' FROM `git_repositories`;\n' printf 'SELECT CONCAT(' printf '"repository_stability_ids__",' printf '`repository_stabilities`.`name`,"=",' printf '`repository_stabilities`.`id`)' printf ' FROM `repository_stabilities`;\n' } | \ 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 ' WHERE NOT EXISTS (' printf 'SELECT 1 FROM `binary_packages_in_repositories`' printf ' WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`' printf ');\n' printf 'DELETE `binary_packages` FROM `binary_packages`' printf ' WHERE NOT EXISTS (' printf 'SELECT 1 FROM `binary_packages_in_repositories`' printf ' WHERE `binary_packages_in_repositories`.`package`=`binary_packages`.`id`' printf ');\n' }