path: root/bin/db-update

#!/bin/sh

# move binary packages from staging to testing (if possible [1]) and
# additionally tested packages from testing to the respective stable
# repository (if possible [1])

# The condition [1] is explained in the stored function
# calculate_maximal_moveable_set which is created in bin/bootsrap-mysql

# TODO: separate locks for staging, testing (and stable)

# shellcheck disable=SC2039,SC2119,SC2120

# shellcheck source=../lib/load-configuration
. "${0%/*}/../lib/load-configuration"

# shellcheck disable=SC2016
usage() {
  >&2 echo ''
  >&2 echo 'db-update [options] [packages]:'
  >&2 echo ' move tested packages from testing to stable.'
  >&2 echo ' move possible packages from staging to testing.'
  >&2 echo ''
  >&2 echo 'possible options:'
  >&2 echo '  -f|--force $package-id:'
  >&2 echo '    Force movement of package with given id and move nothing else.'
  >&2 echo '  -h|--help:'
  >&2 echo '    Show this help and exit.'
  >&2 echo '  -n|--no-action:'
  >&2 echo '    Only print what would be moved.'
  >&2 echo '  -p|--progressive:'
  >&2 echo '    Move forward any package which replaces no package whose'
  >&2 echo '    dependencies are all available somewhere.'
  >&2 echo '    Note, that this _may_ move _less_ packages.'
  >&2 echo '  -w|--wait:'
  >&2 echo '    If necessary, wait for lock blocking.'
  [ -z "$1" ] && exit 1 || exit "$1"
}

eval set -- "$(
  getopt -o f:hnpw \
    --long force \
    --long help \
    --long no-action \
    --long progressive \
    --long wait \
    -n "$(basename "$0")" -- "$@" || \
  echo usage
)"

block_flag='-n'
no_action=false
progressive=false
force_ids=''

while true
do
  case "$1" in
    -f|--force)
      shift
      force_ids=$(
        printf '%s' "$1" | \
          base64 -w0
        printf '\n%s' "${force_ids}"
      )
    ;;
    -h|--help)
      usage 0
    ;;
    -n|--no-action)
      no_action=true
    ;;
    -p|--progressive)
      progressive=true
    ;;
    -w|--wait)
      block_flag=''
    ;;
    --)
      shift
      break
    ;;
    *)
      >&2 echo 'Whoops, forgot to implement option "'"$1"'" internally.'
      exit 42
    ;;
  esac
  shift
done

if [ $# -ne 0 ]; then
  >&2 echo 'db-update: too many arguments'
  usage
fi

if ${progressive} && \
  [ -n "${force_ids}" ]; then
  >&2 echo 'db-update: conflicting arguments'
  usage
fi

if [ -s "${work_dir}/build-master-sanity" ]; then
  >&2 echo 'Build master is not sane.'
  exit
fi

if ! ${no_action}; then
  # Create lock.
  exec 9> "${package_database_lock_file}"
  if ! verbose_flock ${block_flag} 9; then
    >&2 echo 'come back (shortly) later - I cannot lock package database.'
    exit 0
  fi

  exec 8> "${sanity_check_lock_file}"
  if ! verbose_flock -s ${block_flag} 8; then
    >&2 echo 'come back (shortly) later - sanity-check currently running.'
    exit 0
  fi

  # shellcheck disable=SC2016
  {
    printf 'UPDATE `binary_packages`'
    printf ' JOIN ('
      printf 'SELECT `binary_packages_in_repositories`.`package`,'
      printf 'MIN(`binary_packages_in_repositories`.`last_moved`) AS `first_last_moved`'
      printf ' FROM `binary_packages_in_repositories`'
      mysql_join_binary_packages_in_repositories_repositories
      # shellcheck disable=SC2154
      printf ' WHERE `repositories`.`stability`=%s' \
        "${repository_stability_ids__testing}"
      printf ' GROUP BY `binary_packages_in_repositories`.`package`'
    printf ') AS `binary_packages_in_repositories`'
    printf ' ON `binary_packages_in_repositories`.`package`=`binary_packages`.`id`'
    printf ' SET `binary_packages`.`is_tested`=1'
    printf ' WHERE NOT `binary_packages`.`has_issues`'
    printf ' AND NOT `binary_packages`.`is_tested`'
    printf ' AND `binary_packages_in_repositories`.`first_last_moved`<ADDDATE(NOW(),from_base64("%s"))' \
      "$(
        printf -- '-%s' "${max_testing_duration}" | \
          base64 -w0
      )"
  } | \
    mysql_run_query
fi

# Create tmp_dir and trap.
tmp_dir=$(mktemp -d "${work_dir}/tmp.db-update.XXXXXXXXXX")
trap 'rm -rf --one-file-system "${tmp_dir}"' EXIT

export TMPDIR="${tmp_dir}"

# shellcheck disable=SC2154
for source_stability in \
  "${repository_stability_ids__testing}" \
  "${repository_stability_ids__staging}"; do
  find "${tmp_dir}" -mindepth 1 -delete

  # shellcheck disable=SC2016
  {
    # Note, that we do not need to check that two repositories a and b
    # are for the same architecture if we joined them via
    # repository_moves, because only repositories with the same
    # architectures should be listed there.
    if [ -n "${force_ids}" ]; then
      printf 'DROP TEMPORARY TABLE IF EXISTS `%s_bpir`;\n' \
        'moveable' 'replaced'
      printf 'CREATE TEMPORARY TABLE `replaced_bpir` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n'
      printf 'CREATE TEMPORARY TABLE `moveable_bpir` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n'
      printf 'INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`)'
      printf ' VALUES'
      # shellcheck disable=SC2086
      printf '(from_base64("%s"),NULL),' \
        ${force_ids} | \
        sed 's/,$/;\n/'
      printf 'DELETE `moveable_bpir` FROM `moveable_bpir`'
      printf ' JOIN `binary_packages_in_repositories` ON `binary_packages_in_repositories`.`id`=`moveable_bpir`.`id`'
      mysql_join_binary_packages_in_repositories_binary_packages
      mysql_join_binary_packages_in_repositories_repositories
      printf ' WHERE `repositories`.`stability`!=%s;\n' \
        "${source_stability}"
      printf 'UPDATE `moveable_bpir`'
      printf ' JOIN `binary_packages_in_repositories` ON `binary_packages_in_repositories`.`id`=`moveable_bpir`.`id`'
      mysql_join_binary_packages_in_repositories_binary_packages
      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_in_repositories`.`repository`'
      printf ' SET `moveable_bpir`.`to_repository`=`repository_moves`.`to_repository`;\n'
      printf 'DELETE FROM `moveable_bpir` WHERE `moveable_bpir`.`to_repository` IS NULL;\n'
      printf 'INSERT IGNORE INTO `replaced_bpir` (`id`,`replaced_by`)'
      printf ' SELECT `binary_packages_in_repositories`.`id`,`moveable_bpir`.`id`'
      printf ' FROM `moveable_bpir`'
      printf ' JOIN `binary_packages_in_repositories` AS `subst_bpir` ON `moveable_bpir`.`id`=`subst_bpir`.`id`'
      mysql_join_binary_packages_in_repositories_binary_packages 'subst_bpir' 'subst_bp'
      printf ' JOIN `binary_packages` ON `binary_packages`.`pkgname`=`subst_bp`.`pkgname`'
      mysql_join_binary_packages_binary_packages_in_repositories
      mysql_join_binary_packages_in_repositories_repositories
      printf ' JOIN `repositories` AS `m_to_r` ON `moveable_bpir`.`to_repository`=`m_to_r`.`id`'
      printf ' AND `repositories`.`stability`=`m_to_r`.`stability`'
      printf ' AND `repositories`.`architecture`=`m_to_r`.`architecture`;\n'
    elif ${progressive}; then
      printf 'DROP TEMPORARY TABLE IF EXISTS `%s_bpir`;\n' \
        'moveable' 'replaced'
      printf 'CREATE TEMPORARY TABLE `replaced_bpir` (`id` BIGINT, `replaced_by` BIGINT, UNIQUE KEY (`id`));\n'
      printf 'CREATE TEMPORARY TABLE `moveable_bpir` (`id` BIGINT, `to_repository` MEDIUMINT, UNIQUE KEY (`id`));\n'

      printf 'INSERT IGNORE INTO `replaced_bpir` (`id`,`replaced_by`)'
      printf ' SELECT `old_bpir`.`id`,`new_bpir`.`id`'
      printf ' FROM `binary_packages_in_repositories` AS `new_bpir`'
      mysql_join_binary_packages_in_repositories_binary_packages 'new_bpir' 'new_bp'
      mysql_join_binary_packages_in_repositories_repositories 'new_bpir' 'from_r'
      printf ' AND `from_r`.`stability`=%s' \
        "${source_stability}"
      printf ' AND `from_r`.`is_on_master_mirror`'
      mysql_join_binary_packages_build_assignments 'new_bp'
      mysql_join_build_assignments_package_sources
      mysql_join_package_sources_upstream_repositories
      mysql_join_upstream_repositories_repository_moves
      printf ' AND `repository_moves`.`from_repository`=`from_r`.`id`'
      printf ' JOIN `repositories` AS `to_r`'
      printf ' ON `repository_moves`.`to_repository`=`to_r`.`id`'
      printf ' JOIN `binary_packages` AS `old_bp`'
      printf ' ON `new_bp`.`pkgname`=`old_bp`.`pkgname`'
      mysql_join_binary_packages_binary_packages_in_repositories 'old_bp' 'old_bpir'
      mysql_join_binary_packages_in_repositories_repositories 'old_bpir' 'old_r'
      printf ' AND `old_r`.`stability`=`to_r`.`stability`'
      printf ' AND `old_r`.`architecture`=`to_r`.`architecture`'
      mysql_join_binary_packages_dependencies 'old_bp'
      mysql_join_dependencies_dependency_types
      printf ' AND `dependency_types`.`relevant_for_binary_packages`'
      printf ' WHERE NOT EXISTS ('
        printf 'SELECT 1 FROM `install_target_providers`'
        mysql_join_install_target_providers_binary_packages_in_repositories
        mysql_join_binary_packages_in_repositories_repositories
        printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
        printf ' AND `repositories`.`architecture`=`old_r`.`architecture`'
      printf ');\n'

      printf 'INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`)'
      printf ' SELECT `replaced_bpir`.`replaced_by`,`binary_packages_in_repositories`.`repository`'
      printf ' FROM `replaced_bpir`'
      printf ' JOIN `binary_packages_in_repositories` ON `binary_packages_in_repositories`.`id`=`replaced_bpir`.`id`'
      printf ';\n'

      printf 'INSERT IGNORE INTO `moveable_bpir` (`id`,`to_repository`)'
      printf ' SELECT `binary_packages_in_repositories`.`id`,`repository_moves`.`to_repository`'
      printf ' FROM `binary_packages_in_repositories`'
      mysql_join_binary_packages_in_repositories_binary_packages
      mysql_join_binary_packages_in_repositories_repositories
      printf ' AND `repositories`.`is_on_master_mirror`'
      printf ' AND `repositories`.`stability`=%s' \
        "${source_stability}"
      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_in_repositories`.`repository`'
      printf ' WHERE NOT EXISTS ('
        printf 'SELECT 1 FROM `binary_packages_in_repositories` AS `repl_bpir`'
        mysql_join_binary_packages_in_repositories_binary_packages 'repl_bpir' 'repl_bp'
        printf ' WHERE `repl_bp`.`pkgname`=`binary_packages`.`pkgname`'
        printf ' AND `repl_bpir`.`repository`=`repository_moves`.`to_repository`'
      printf ');\n'
    else
      printf 'CALL calculate_maximal_moveable_set(%s);\n' \
        "${source_stability}"
    fi

    printf 'CREATE TEMPORARY TABLE `rps` (`id` MEDIUMINT, UNIQUE INDEX (`id`));\n'
    printf 'INSERT IGNORE INTO `rps` (`id`)'
    printf ' SELECT `moveable_bpir`.`to_repository`'
    printf ' FROM `moveable_bpir`;\n'
    printf 'INSERT IGNORE INTO `rps` (`id`)'
    printf ' SELECT `binary_packages_in_repositories`.`repository`'
    printf ' FROM `moveable_bpir`'
    printf ' JOIN `binary_packages_in_repositories` ON `moveable_bpir`.`id`=`binary_packages_in_repositories`.`id`;\n'
    printf 'INSERT IGNORE INTO `rps` (`id`)'
    printf ' SELECT `binary_packages_in_repositories`.`repository`'
    printf ' FROM `replaced_bpir`'
    printf ' JOIN `binary_packages_in_repositories` ON `replaced_bpir`.`id`=`binary_packages_in_repositories`.`id`;\n'

    printf 'SELECT "repositories",`architectures`.`name`,`repositories`.`name`'
    printf ' FROM `rps`'
    printf ' JOIN `repositories` ON `rps`.`id`=`repositories`.`id`'
    mysql_join_repositories_architectures
    printf ';\n'

    printf 'SELECT "mv.id",`moveable_bpir`.`id`,`moveable_bpir`.`to_repository`'
    printf ' FROM `moveable_bpir`;\n'

    printf 'SELECT "mv",'
    mysql_package_name_query
    printf ',`r_a`.`name`,`repositories`.`name`,`new_r_a`.`name`,`new_repo`.`name`'
    printf ' FROM `moveable_bpir`'
    printf ' JOIN `binary_packages_in_repositories` ON `moveable_bpir`.`id`=`binary_packages_in_repositories`.`id`'
    mysql_join_binary_packages_in_repositories_binary_packages
    mysql_join_binary_packages_in_repositories_repositories
    mysql_join_binary_packages_architectures
    mysql_join_repositories_architectures '' 'r_a'
    printf ' JOIN `repositories` AS `new_repo` ON `new_repo`.`id`=`moveable_bpir`.`to_repository`'
    mysql_join_repositories_architectures 'new_repo' 'new_r_a'
    printf ';\n'

    printf 'SELECT "rm.id",`replaced_bpir`.`id`'
    printf ' FROM `replaced_bpir`;\n'

    printf 'SELECT "rm",'
    mysql_package_name_query
    printf ',`r_a`.`name`,`repositories`.`name`'
    printf ' FROM `replaced_bpir`'
    printf ' JOIN `binary_packages_in_repositories` ON `replaced_bpir`.`id`=`binary_packages_in_repositories`.`id`'
    mysql_join_binary_packages_in_repositories_binary_packages
    mysql_join_binary_packages_in_repositories_repositories
    mysql_join_binary_packages_architectures
    mysql_join_repositories_architectures '' 'r_a'
    printf ';\n'
  } | \
    mysql_run_query | \
    tr '\t' ' ' | \
    grep '^\(repositories\|\(rm\|mv\)\(\.id\)\?\) ' | \
    while read -r what content; do
      printf '%s\n' "${content}" >> \
        "${tmp_dir}/${what}"
    done

  if [ ! -s "${tmp_dir}/repositories" ]; then
    >&2 printf 'Nothing to move here (%s).\n' "${source_stability}"
    continue
  fi

  touch \
    "${tmp_dir}/mv" \
    "${tmp_dir}/mv.id" \
    "${tmp_dir}/rm" \
    "${tmp_dir}/rm.id"

  # shellcheck disable=SC2086
  for s in "${tmp_dir}/"*; do
    sort -u "${s}" | \
      sponge "${s}"
  done

  # receive the repository databases from the master mirror
  mkdir "${tmp_dir}/dbs"
  while read -r arch repo; do
    mkdir -p "${tmp_dir}/dbs/${arch}/${repo}"
    # shellcheck disable=SC2086
    ${master_mirror_rsync_command} \
      "${master_mirror_rsync_directory}/${arch}/${repo}/${repo}.db."* \
      "${master_mirror_rsync_directory}/${arch}/${repo}/${repo}.files."* \
      "${tmp_dir}/dbs/${arch}/${repo}/"
  done < \
    "${tmp_dir}/repositories"

  # remove to-be-deleted packages
  # shellcheck disable=SC2094
  cut -d' ' -f2,3 < \
    "${tmp_dir}/rm" | \
    sort -u | \
    while read -r arch repo; do
      grep " $(str_to_regex "${arch} ${repo}")\$" "${tmp_dir}/rm" | \
        sed '
          s/\(-[^-]\+\)\{3\} \S\+ \S\+$//
        ' | \
        xargs -r repo-remove -q "${tmp_dir}/dbs/${arch}/${repo}/${repo}.db.tar.gz"
    done

  # copy and delete moved packages
  # shellcheck disable=SC2094
  cut -d' ' -f2,3,4,5 < \
    "${tmp_dir}/mv" | \
    sort -u | \
    while read -r from_arch from_repo to_arch to_repo; do
      grep " $(str_to_regex "${from_arch} ${from_repo} ${to_arch} ${to_repo}")\$" "${tmp_dir}/mv" | \
        sed '
          s/-[^-]\+\( \S\+\)\{4\}$//
        ' | \
        xargs -r "${base_dir}/bin/repo-copy" \
          "${tmp_dir}/dbs/${from_arch}/${from_repo}/${from_repo}.db.tar.gz" \
          "${tmp_dir}/dbs/${to_arch}/${to_repo}/${to_repo}.db.tar.gz"
      grep " $(str_to_regex "${from_arch} ${from_repo} ${to_arch} ${to_repo}")\$" "${tmp_dir}/mv" | \
        sed '
          s/\(-[^-]\+\)\{3\}\( \S\+\)\{4\}$//
        ' | \
        xargs -r repo-remove -q \
          "${tmp_dir}/dbs/${from_arch}/${from_repo}/${from_repo}.db.tar.gz"
    done

  # create real file names of packages, because
  # mysql_query_and_delete_unneeded_binary_packages does so, too
  sed -i '
    s,^\(\S\+\) \(\S\+\) \(\S\+\)$,\2/\3/\1,
  ' "${tmp_dir}/rm"

  # somewhat inaccurate
  if ! ${no_action}; then
    # shellcheck disable=SC2016
    {
      printf 'CREATE TEMPORARY TABLE `replaced_bpir` (`id` BIGINT, UNIQUE KEY (`id`));\n'
      printf 'CREATE TEMPORARY TABLE `moved_bpir` (`id` BIGINT, `new_repository` MEDIUMINT, UNIQUE KEY (`id`));\n'
      printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `%s` COLUMNS TERMINATED BY " ";\n' \
        "${tmp_dir}/mv.id" 'moved_bpir' \
        "${tmp_dir}/rm.id" 'replaced_bpir'
      printf 'DELETE `binary_packages_in_repositories` FROM `binary_packages_in_repositories`'
      printf ' JOIN `replaced_bpir` ON `binary_packages_in_repositories`.`id`=`replaced_bpir`.`id`;\n'
      mysql_query_and_delete_unneeded_binary_packages
      printf 'UPDATE `binary_packages_in_repositories`'
      printf ' JOIN `moved_bpir` ON `binary_packages_in_repositories`.`id`=`moved_bpir`.`id`'
      printf ' SET `binary_packages_in_repositories`.`repository`=`moved_bpir`.`new_repository`,'
      printf '`binary_packages_in_repositories`.`last_moved`=NOW()'
      printf ' WHERE `binary_packages_in_repositories`.`repository`!=`moved_bpir`.`new_repository`;\n'
    } | \
      mysql_run_query | \
      sort -u >> \
      "${tmp_dir}/rm"
  fi

  # move the packages remotely via sftp
  {
    sed '
      s/^/rm "/
      s/$/"/
      p
      s/"$/.sig"/
    ' "${tmp_dir}/rm"
    sed '
      s,^\(\S\+\) \(\S\+\) \(\S\+\) \(\S\+\) \(\S\+\)$,rename "\2/\3/\1" "\4/\5/\1"\nrename "\2/\3/\1.sig" "\4/\5/\1.sig",
    ' "${tmp_dir}/mv"
    echo 'quit'
  } | \
    if ${no_action}; then
      sed 's|^|sftp: |'
    else
      ${master_mirror_sftp_command}
    fi

  if ${no_action}; then
    continue
  fi

  # and push our local *.db.tar.gz via rsync
  while read -r arch repo; do
    recompress_gz \
      "${tmp_dir}" \
      "${tmp_dir}/dbs/${arch}/${repo}/${repo}."*".tar.gz" \
      "${tmp_dir}/dbs/${arch}/${repo}/${repo}."*".tar.gz.old"
    # shellcheck disable=SC2086
    ${master_mirror_rsync_command} \
      "${tmp_dir}/dbs/${arch}/${repo}/${repo}.db."* \
      "${tmp_dir}/dbs/${arch}/${repo}/${repo}.files."* \
      "${master_mirror_rsync_directory}/${arch}/${repo}/"
  done < \
    "${tmp_dir}/repositories"

done

trigger_mirror_refreshs