From 49727af5d8015c356288c6cf5f062348768e7849 Mon Sep 17 00:00:00 2001
From: Erich Eckner <git@eckner.net>
Date: Wed, 25 Jul 2018 14:08:50 +0200
Subject: bin/get-package-updates: blacklisting is now done by a stored
 procedure

---
 bin/get-package-updates | 97 ++++++++-----------------------------------------
 1 file changed, 15 insertions(+), 82 deletions(-)

diff --git a/bin/get-package-updates b/bin/get-package-updates
index eb5e390..5fe6223 100755
--- a/bin/get-package-updates
+++ b/bin/get-package-updates
@@ -461,7 +461,6 @@ echo 'apply blacklisting'
 # ignore blacklisted packages and dependent packages
 # this is the first time when all the information is available and up to date
 
-touch "${tmp_dir}/black-listed"
 {
   git -C "${repo_paths__archlinux32}" archive "${new_repo_revisions__archlinux32}" -- 'blacklist' | \
     tar -Ox 'blacklist' | \
@@ -490,88 +489,22 @@ touch "${tmp_dir}/black-listed"
 } | \
   tr ' ' '\t' | \
   sort -u > \
-  "${tmp_dir}/black-listed-new"
-
-# TODO: this should be a stored procedure
-while [ -s "${tmp_dir}/black-listed-new" ]; do
-  cat \
-    "${tmp_dir}/black-listed-new" \
-    "${tmp_dir}/black-listed" | \
-    sort -u | \
-    sponge "${tmp_dir}/black-listed"
-  {
-    sed 'p' "${tmp_dir}/black-listed"
+  "${tmp_dir}/black-listed"
 
-    # shellcheck disable=SC2016
-    {
-      for suffix in '' '_copy'; do
-        printf 'CREATE TEMPORARY TABLE `bl%s` (' \
-          "${suffix}"
-        printf '`arch` VARCHAR(16),'
-        printf '`pkgbase` VARCHAR(64),'
-        printf 'UNIQUE KEY `content`(`arch`,`pkgbase`)'
-        printf ');\n'
-      done
-      printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `bl` (`arch`,`pkgbase`);\n' \
-        "${tmp_dir}/black-listed"
-      printf 'INSERT INTO `bl_copy` (`arch`,`pkgbase`)'
-      printf ' SELECT `bl`.`arch`,'
-      printf '`bl`.`pkgbase`'
-      printf ' FROM `bl`;\n'
-
-      printf 'INSERT IGNORE INTO `bl` (`arch`,`pkgbase`)'
-      printf ' SELECT `n_a`.`name`,'
-      printf '`bl_copy`.`pkgbase`'
-      printf ' FROM `bl_copy`'
-      printf ' JOIN `architectures` AS `o_a`'
-      printf ' ON `o_a`.`name`=`bl_copy`.`arch`'
-      printf ' JOIN `architecture_compatibilities`'
-      printf ' ON `architecture_compatibilities`.`runs_on`=`o_a`.`id`'
-      printf ' JOIN `architectures` AS `n_a`'
-      printf ' ON `architecture_compatibilities`.`built_for`=`n_a`.`id`'
-      printf ' OR `o_a`.`name`="any";\n'
-
-      printf 'DELETE FROM `bl`'
-      printf ' WHERE `bl`.`arch`="any";\n'
-
-      printf 'SELECT `a_ra`.`name`,`a_ps`.`pkgbase`'
-      printf ' FROM `package_sources` AS `a_ps`'
-      mysql_join_package_sources_build_assignments 'a_ps' 'a_ba'
-      mysql_join_build_assignments_binary_packages 'a_ba' 'a_bp'
-      mysql_join_binary_packages_binary_packages_in_repositories 'a_bp' 'a_bpir'
-      mysql_join_binary_packages_in_repositories_repositories 'a_bpir' 'a_r'
-      mysql_join_repositories_architectures 'a_r' 'a_ra'
-      mysql_join_binary_packages_dependencies 'a_bp'
-      mysql_join_dependencies_dependency_types
-      printf ' AND (`dependency_types`.`relevant_for_building`'
-      printf ' OR `dependency_types`.`relevant_for_binary_packages`)'
-      printf ' WHERE NOT EXISTS ('
-        printf ' SELECT 1'
-        printf ' FROM `install_target_providers`'
-        printf ' WHERE NOT EXISTS ('
-          printf ' SELECT 1'
-          printf ' FROM `bl`'
-          printf ' JOIN `package_sources` AS `b_ps` ON `bl`.`pkgbase`=`b_ps`.`pkgbase`'
-          mysql_join_package_sources_build_assignments 'b_ps' 'b_ba'
-          mysql_join_build_assignments_binary_packages 'b_ba' 'b_bp'
-          printf ' WHERE `install_target_providers`.`package`=`b_bp`.`id`'
-        printf ')'
-        printf ' AND `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
-      printf ') AND EXISTS ('
-      # TODO: This should be corrected at the root: automatic install targets, which are bogus should
-      #       not be added in the first place - but how do we detect that? -> add exceptions!
-        printf ' SELECT 1'
-        printf ' FROM `install_target_providers`'
-        printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
-      printf ');\n'
-    } | \
-      mysql_run_query | \
-      sort -u
-  } | \
-    sort | \
-    uniq -u | \
-    sponge "${tmp_dir}/black-listed-new"
-done
+# shellcheck disable=SC2016
+{
+  printf 'CREATE TEMPORARY TABLE `blacklist` ('
+  printf '`arch` VARCHAR(16),'
+  printf '`pkgbase` VARCHAR(64),'
+  printf 'UNIQUE KEY `content`(`arch`,`pkgbase`)'
+  printf ');\n'
+  printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `blacklist` (`arch`,`pkgbase`);\n' \
+    "${tmp_dir}/black-listed"
+  printf 'CALL `blacklist_packages`;\n'
+} | \
+  mysql_run_query | \
+  sort -u | \
+  sponge "${tmp_dir}/black-listed"
 
 deletion_list_count=$(
   wc -l < \
-- 
cgit v1.2.3-70-g09d2