lib/common-functions: rewrite remove_old_package_versions to use db and take package id as parameter

3 files changed, 134 insertions, 272 deletions

diff --git a/bin/copy-to-build-support b/bin/copy-to-build-support
index bd4412a..2de852a 100755
--- a/bin/copy-to-build-support
+++ b/bin/copy-to-build-support
@@ -109,8 +109,8 @@ sed -n '
     repository="${id#* }"
     id="${id%% *}"
 
-    printf '%s\n' "${package}" >> \
-      "${tmp_dir}/packages"
+    printf '%s\n' "${id}" >> \
+      "${tmp_dir}/package-ids"
     for suffix in '' '.sig'; do
       printf 'ln "i686/%s/%s%s" "i686/build-support/%s%s"\n' \
         "${repository}" \
@@ -180,14 +180,14 @@ if [ -s "${tmp_dir}/mysql-command" ]; then
     "${tmp_dir}/mysql-command"
 fi
 
-if [ -s "${tmp_dir}/packages" ]; then
-  while read -r package; do
-    remove_old_package_versions 'i686' 'build-support' "${package}"
+if [ -s "${tmp_dir}/package-ids" ]; then
+  while read -r package_id; do
+    remove_old_package_versions "${package_id}"
   done < \
-    "${tmp_dir}/packages"
+    "${tmp_dir}/package-ids"
 fi
 
 if [ -w "$1" ]; then
-  cat "${tmp_dir}/packages" > \
+  cat "${tmp_dir}/package-ids" > \
     "$1"
 fi
diff --git a/bin/return-assignment b/bin/return-assignment
index 1b5e991..28b01e7 100755
--- a/bin/return-assignment
+++ b/bin/return-assignment
@@ -576,6 +576,30 @@ trigger_mirror_refreshs
 } | \
   mysql_run_query
 
-for package in ${packages}; do
-  remove_old_package_versions 'i686' "${destination}" "${package}"
-done
+# shellcheck disable=SC2016
+{
+  printf 'SELECT'
+  printf ' `binary_packages`.`id`'
+  printf ' FROM `binary_packages`'
+  mysql_join_binary_packages_architectures
+  mysql_join_binary_packages_repositories
+  printf ' WHERE '
+  mysql_package_name_query
+  printf ' IN ('
+  # shellcheck disable=SC2086
+  printf '%s\n' ${packages} | \
+    base64_encode_each | \
+    sed '
+      s/^/from_base64("/
+      s/$/"),/
+    '
+  printf '"") AND `repositories`.`name`=from_base64("%s");\n' \
+    "$(
+      printf '%s' "${destination}" | \
+        base64 -w0
+    )"
+} | \
+  mysql_run_query | \
+  while read -r package_id; do
+    remove_old_package_versions "${package_id}"
+  done
diff --git a/lib/common-functions b/lib/common-functions
index 530b166..5949766 100755
--- a/lib/common-functions
+++ b/lib/common-functions
@@ -155,286 +155,124 @@ ls_master_mirror() {
 
 }
 
-# TODO: the actions of remove_old_package_versions should be done
-# on basis of the information in the database
+# TODO: once binary_packages_in_repositories is funcitonal, we should
+# use the id of that
 
-# remove_old_package_versions $arch $repository $package_file
+# remove_old_package_versions $binary_package_id
 
-# removes all older (not-newer) versions of $package_file
-# in all repositories not-older (newer) than $repository
+# removes all older versions of the given package from all repositories
+# less[1] stable than the current repository, as well as any different
+# version of the same package from equally[2] stable repositories
 
-# TODO: should remove all other version (also newer) from
-# some repositories :-/
-
-# A package is considered not newer if
-#  a) its version is not newer
-# A package is considered older if
-#  b) its version is older or
-#  c) if it's "not newer" and its architecture is 'any' and different or
-#  d) if it's "not newer" and the other architecture is 'any' and different
-
-# this ensures an any package may replace arch-specific packages of the same version and vice versa
+# 1] determined by `repository_stability_relations`
+# 2] identical `repository`.`stability`
 
 remove_old_package_versions() {
 
-  local arch="$1"
-  local repository="$2"
-  local package="$3"
-
-  local pkgname
-  local epoch
-  local pkgver
-  local pkgrel
-  local sub_pkgrel
-  pkgname="${package%-*}"
-  pkgrel="${pkgname##*-}"
-  sub_pkgrel="${pkgrel##*.}"
-  if [ "${sub_pkgrel}" = "${pkgrel}" ]; then
-    sub_pkgrel='0'
-  else
-    pkgrel="${pkgrel%.*}"
-  fi
-  pkgname="${pkgname%-*}"
-  pkgver="${pkgname##*-}"
-  epoch="${pkgver%%:*}"
-  if [ "${epoch}" = "${pkgver}" ]; then
-    epoch='0'
-  else
-    pkgver="${pkgver#*:}"
-  fi
-  pkgname="${pkgname%-*}"
-
-  # shellcheck disable=SC2016
-  {
-    printf 'SELECT "bogus",CONCAT(from_base64("%s"),"/",from_base64("%s")),1,from_base64("%s");\n' \
-      "$(
-        printf '%s' "${repository}" | \
-          base64 -w0
-      )" \
-      "$(
-        printf '%s' "${package}" | \
-          base64 -w0
-      )" \
-      "$(
-        printf '%s' "${package}" | \
-          sed '
-            s/^.*-\([^-]\+-[^-]\+\)-[^-]\+$/\1/
-          ' | \
-          base64 -w0
-      )"
-    printf 'SELECT '
-    printf '`binary_packages`.`id`,'
-    printf 'CONCAT(`repositories`.`name`,"/",'
-    mysql_package_name_query
-    printf '),'
-    # should we delete packages of identical version?
-    printf 'IF((`more_stable_repos`.`id`!=`repositories`.`id`) AND (`more_stable_repos`.`stability`=`repositories`.`stability`),2,0),'
-    printf 'CONCAT('
-      printf 'IF(`binary_packages`.`epoch`=0,"",CONCAT(`binary_packages`.`epoch`,":")),'
-      printf '`binary_packages`.`pkgver`,"-",'
-      printf '`binary_packages`.`pkgrel`,".",'
-      printf '`binary_packages`.`sub_pkgrel`'
-    printf ')'
-    printf ' FROM `binary_packages`'
-    mysql_join_binary_packages_repositories
-    mysql_join_binary_packages_architectures
-    printf ' JOIN `repository_stability_relations` ON `repository_stability_relations`.`less_stable`=`repositories`.`stability`'
-    printf ' JOIN `repositories` AS `more_stable_repos` ON `repository_stability_relations`.`more_stable`=`more_stable_repos`.`stability`'
-    # name must match
-    printf ' WHERE `binary_packages`.`pkgname`=from_base64("%s")' \
-      "$(printf '%s' "${package%-*-*-*}" | base64 -w0)"
-    # repository, where package should be deleted, should be less stable
-    printf ' AND `more_stable_repos`.`name`=from_base64("%s")' \
-      "$(printf '%s' "${repository}" | base64 -w0)"
-    printf ';\n'
-  } | \
-    mysql_run_query | \
-    tr '\t' ' ' | \
-    expand_version 4 | \
-    sort -k4V,4 -k3r,3 | \
-    shrink_version 4 | \
-    sed -n '
-      /^bogus /q
-      p
-    ' | \
-    cut -d' ' -f1,2 >&2
-
-  # repositories in which older packages should be deleted
-  local delete_older_repositories
-  # repositories in which not-newer packages should be deleted
-  local delete_not_newer_repositories
-
-  if echo "${standalone_package_repositories}" | \
-    grep -qxF "${repository}"; then
-
-    delete_older_repositories="${repository}"
-    delete_not_newer_repositories=''
-
-  elif echo "${staging_package_repositories}" | \
-    grep -qxF "${repository}"; then
-
-    delete_older_repositories="${repository}"
-    delete_not_newer_repositories=$(
-      echo "${staging_package_repositories}" | \
-        grep -vxF "${repository}"
-    ) || true
-
-  elif echo "${testing_package_repositories}" | \
-    grep -qxF "${repository}"; then
-
-    delete_older_repositories=$(
-      printf '%s\n' "${staging_package_repositories}" "${repository}"
-    )
-    delete_not_newer_repositories=$(
-      echo "${testing_package_repositories}" | \
-        grep -vxF "${repository}"
-    ) || true
-
-  elif echo "${stable_package_repositories}" | \
-    grep -qxF "${repository}"; then
-
-    delete_older_repositories=$(
-      printf '%s\n' "${staging_package_repositories}" "${testing_package_repositories}" "${repository}"
-    )
-    delete_not_newer_repositories=$(
-      echo "${stable_package_repositories}" | \
-        grep -vxF "${repository}"
-    ) || true
-
-  else
-
-    >&2 printf 'remove_old_package_versions: Unknown repository "%s".\n' "${repository}"
-    return 1
-
-  fi
-
   ( # the new shell is intentional
     tmp_dir=$(mktemp -d 'tmp.common-functions.remove_old_package_versions.XXXXXXXXXX' --tmpdir)
     trap 'rm -rf --one-file-system "${tmp_dir}"' EXIT
 
-    {
-      # the architecture of the package (any vs. i686)
-      package_arch="${package##*-}"
-      package_arch="${package_arch%%.*}"
-      if [ "${package_arch}" = 'any' ]; then
-        package_arch_regex_inverter='!'
-      else
-        unset package_arch_regex_inverter
-      fi
+    binary_package_id="$1"
 
-      for repo in ${delete_older_repositories}; do
-        ls_master_mirror "${arch}/${repo}" | \
-          sed -n '
-            /\.pkg\.tar\.xz$/!d
-            s|-\([^-]\+-[^-]\+\)-\([^-]\+\)$| \1 \2|
-            /^'"$(str_to_regex "${package%-*-*-*}")"' / {
-              s|^|2 '"${arch} ${repo}"' |
-              / any\.pkg\.tar\.xz$/'"${package_arch_regex_inverter}"'{
-                s|^2|0|
-              }
-              p
-            }
-          '
-      done
-      for repo in ${delete_not_newer_repositories}; do
-        ls_master_mirror "${arch}/${repo}" | \
-          sed -n '
-            /\.pkg\.tar\.xz$/!d
-            s|-\([^-]\+-[^-]\+\)-\([^-]\+\)$| \1 \2|
-            /^'"$(str_to_regex "${package%-*-*-*}")"' / {
-              s|^|0 '"${arch} ${repo}"' |
-              p
-            }
-          '
-      done
-      echo "${package%-*}" | \
-        sed 's|^.*-\([^-]\+-[^-]\+\)$|1 %cut% %it% %here% \1|'
-
-      # the generated list contains the following columns:
-      # $delete-if-newer-vs-not-older $arch-directory $repo-directory $pkgname $pkgver-$pkgrel $pkg-arch.pkg.tar.xz
+    # shellcheck disable=SC2016
+    {
+      printf 'SELECT '
+      printf '`d_bp`.`id`,'
+      printf 'IF(`d_r`.`stability`=`o_r`.`stability` AND `d_bp`.`id`!=`o_bp`.`id`,1,0),'
+      printf 'CONCAT('
+        printf 'IF(`d_bp`.`epoch`=0,"",CONCAT(`d_bp`.`epoch`,":")),'
+        printf '`d_bp`.`pkgver`,"-",'
+        printf '`d_bp`.`pkgrel`,".",'
+        printf '`d_bp`.`sub_pkgrel`'
+      printf '),'
+      printf 'IF(`d_r`.`id`=`o_r`.`id`,1,0),'
+      printf '`d_ra`.`name`,'
+      printf '`d_r`.`name`,'
+      printf '`d_bp`.`pkgname`,'
+      printf 'CONCAT(`d_ra`.`name`,"/",'
+      printf '`d_r`.`name`,"/",'
+      mysql_package_name_query 'd_bp' 'd_bpa'
+      printf ')'
+      printf ' FROM `binary_packages` AS `d_bp`'
+      mysql_join_binary_packages_repositories 'd_bp' 'd_r'
+      printf ' AND `d_r`.`is_on_master_mirror`'
+      mysql_join_repositories_architectures 'd_r' 'd_ra'
+      mysql_join_binary_packages_architectures 'd_bp' 'd_bpa'
+      printf ' JOIN `binary_packages` AS `o_bp` ON `d_bp`.`pkgname`=`o_bp`.`pkgname`'
+      mysql_join_binary_packages_repositories 'o_bp' 'o_r'
+      printf ' AND `o_r`.`is_on_master_mirror`'
+      printf ' AND `o_r`.`architecture`=`d_r`.`architecture`'
+      printf ' JOIN `repository_stability_relations`'
+      printf ' ON `repository_stability_relations`.`less_stable`=`d_r`.`stability`'
+      printf ' AND `repository_stability_relations`.`more_stable`=`o_r`.`stability`'
+      printf ' WHERE `o_bp`.`id`=from_base64("%s")' \
+        "$(
+          printf '%s' "${binary_package_id}" | \
+            base64 -w0
+        )"
+      printf ';\n'
     } | \
-      expand_version 5 | \
-      sort -k5V,5 -k1n,1 | \
-      shrink_version 5 | \
+      mysql_run_query | \
+      tr '\t' ' ' | \
+      expand_version 3 | \
+      sort -k3V,3 -k2r,2 | \
+      shrink_version 3 | \
       sed -n '
-        /^1 %cut% %it% %here% /q
-        s/^[02] //
-        s/ \(\S\+\)$/-\1/
-        p
-      ' | \
-      sort -u > \
-      "${tmp_dir}/packages-to-delete"
-    # this file contains a list of packages to be deleted, one on each line:
-    # $architecture-directory $repository-directory $package-name $pkgver-$pkgrel-$package-architecture.pkg.tar.xz
-
-    cut -d' ' -f1,2 < \
-      "${tmp_dir}/packages-to-delete" | \
-      grep -vxF "${arch} ${repository}" | \
-      sort -u > \
-      "${tmp_dir}/repositories-to-modify"
-
-    # fetch all databases being modified
-    while read -r del_arch del_repo; do
-      mkdir -p "${tmp_dir}/${del_arch}/${del_repo}"
-      ${master_mirror_rsync_command} \
-        "${master_mirror_rsync_directory}/${del_arch}/${del_repo}/${del_repo}.db."* \
-        "${master_mirror_rsync_directory}/${del_arch}/${del_repo}/${del_repo}.files."* \
-        "${tmp_dir}/${del_arch}/${del_repo}/"
-    done < \
-      "${tmp_dir}/repositories-to-modify"
-
-    while read -r del_arch del_repo del_package _; do
-      if [ "${del_arch}/${del_repo}" = "${arch}/${repository}" ]; then
-        # we do not repo-remove the package in the target repository
-        continue
-      fi
-      repo-remove -q "${tmp_dir}/${del_arch}/${del_repo}/${del_repo}.db.tar.gz" \
-        "${del_package}"
-    done < \
-      "${tmp_dir}/packages-to-delete"
+        /^'"${binary_package_id}"' /q
+        s/^\([0-9]\+ \)\(\S\+ \)\{2\}/\1/
+        h
+        /^[0-9]\+ 0 / {
+          s/^\(\S\+ \)\{2\}//
+          s/ \S\+$//
+          w'"${tmp_dir}"'/repo-removes
+          g
+        }
+        s/^\(\S\+ \)\{5\}//
+        w'"${tmp_dir}"'/sftp-removes
+        s/$/.sig/
+        w'"${tmp_dir}"'/sftp-removes
+        g
+        s/ .*$//
+        w'"${tmp_dir}"'/db-removes
+      '
 
-    # upload modified databases
-    while read -r del_arch del_repo; do
+    # repo-remove packages
+    while read -r arch repo pkgname; do
+      mkdir "${tmp_dir}/transit"
+      ${master_mirror_rsync_command} \
+        "${master_mirror_rsync_directory}/${arch}/${repo}/${repo}.db."* \
+        "${master_mirror_rsync_directory}/${arch}/${repo}/${repo}.files."* \
+        "${tmp_dir}/transit/"
+      repo-remove "${tmp_dir}/transit/${repo}.db.tar.gz" "${pkgname}"
       ${master_mirror_rsync_command} \
-        "${tmp_dir}/${del_arch}/${del_repo}/${del_repo}.db."* \
-        "${tmp_dir}/${del_arch}/${del_repo}/${del_repo}.files."* \
-        "${master_mirror_rsync_directory}/${del_arch}/${del_repo}/"
+        "${tmp_dir}/repos/${arch}/${repo}/${repo}.db."* \
+        "${tmp_dir}/repos/${arch}/${repo}/${repo}.files."* \
+        "${master_mirror_rsync_directory}/${arch}/${repo}/"
+      rm -rf --one-file-system "${tmp_dir}/transit"
     done < \
-      "${tmp_dir}/repositories-to-modify"
+      "${tmp_dir}/repo-removes"
 
-    # shellcheck disable=SC2016
-    sed '
-      s/\.pkg\.tar\.xz$//
-      s/^\S\+ //
-      s/-\([^-. ]\+\)\(-[^- ]\+\)$/-\1.0\2/
-      s/ \([^-: ]\+\(-[^- ]\+\)\{2\}\)$/ 0:\1/
-      s/ \([^-.]\+\):\([^-:]\+\)-\([^-.]\+\)\.\([^-.]\+\)-\([^-]\+\)$/ \1 \2 \3 \4 \5/
-    ' "${tmp_dir}/packages-to-delete" | \
-      while read -r repo pkgname epoch pkgver pkgrel sub_pkgrel arch; do
-        printf 'DELETE FROM `binary_packages` WHERE'
-        printf ' `binary_packages`.`%s`=(SELECT `%s`.`id` FROM `%s` WHERE `%s`.`name`=from_base64("%s")) AND' \
-          'architecture' 'architectures' 'architectures' 'architectures' "$(printf '%s' "${arch}" | base64 -w0)" \
-          'repository'   'repositories'  'repositories'  'repositories'  "$(printf '%s' "${repo}" | base64 -w0)"
-        printf ' `binary_packages`.`%s`=from_base64("%s") AND' \
-          'pkgname' "$(printf '%s' "${pkgname}" | base64 -w0)" \
-          'epoch' "$(printf '%s' "${epoch}" | base64 -w0)" \
-          'pkgver' "$(printf '%s' "${pkgver}" | base64 -w0)" \
-          'pkgrel' "$(printf '%s' "${pkgrel}" | base64 -w0)" \
-          'sub_pkgrel' "$(printf '%s' "${sub_pkgrel}" | base64 -w0)" | \
-          sed 's/ AND$//'
-        printf ';\n'
-      done | \
-      mysql_run_query
+    # sftp-remove packages
+    if [ -s "${tmp_dir}/sftp-removes" ]; then
+      sed '
+        s|^|rm "|
+        s|$|"|
+      ' "${tmp_dir}/sftp-removes" | \
+        ${master_mirror_sftp_command}
+    fi
+
+    # db-remove packages
+    if [ -s "${tmp_dir}/db-removes" ]; then
+      # shellcheck disable=SC2016
+      {
+        printf 'CREATE TEMPORARY TABLE `del` (`id` BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`));\n'
+        printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `del` (`id`);\n' "${tmp_dir}/db-removes"
+        printf 'DELETE `binary_packages` FROM `binary_packages` JOIN `del` ON `binary_packages`.`id`=`del`.`id`;\n'
+      } | \
+        mysql_run_query
+    fi
 
-    sed '
-      s| \(\S\+\)$|-\1|
-      y| |/|
-      s|^|rm "|
-      s|$|"|
-      p
-      s|"$|.sig"|
-    ' "${tmp_dir}/packages-to-delete" | \
-      ${master_mirror_sftp_command}
   )
 
 }