bin/bootstrap-mysql: stored procedure `blacklist_packages` new

1 files changed, 89 insertions, 0 deletions

diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql
index 176d407..267b4d3 100755
--- a/bin/bootstrap-mysql
+++ b/bin/bootstrap-mysql
@@ -615,6 +615,95 @@ fi
   printf 'END\n'
   printf '//\n'
 
+
+  printf 'DROP PROCEDURE IF EXISTS `blacklist_packages`//\n'
+  printf 'CREATE PROCEDURE `blacklist_packages`()\n'
+  printf 'blacklist_packages:BEGIN\n'
+  printf 'DECLARE row_count_saved INT DEFAULT 0;\n'
+
+  for suffix in '' '_copy'; do
+    printf 'CREATE TEMPORARY TABLE `bl%s` (' \
+      "${suffix}"
+      printf '`arch` SMALLINT,'
+      printf '`pkgbase` VARCHAR(64),'
+      printf 'UNIQUE KEY `content`(`arch`,`pkgbase`)'
+    printf ');\n'
+  done
+  printf 'INSERT IGNORE INTO `bl`(`arch`,`pkgbase`)'
+  printf ' SELECT'
+  printf ' `architectures`.`id`,'
+  printf '`blacklist`.`pkgbase`'
+  printf ' FROM `blacklist`'
+  printf ' JOIN `architectures` AS `bl_a`'
+  printf ' ON `bl_a`.`name`=`blacklist`.`arch`'
+  printf ' JOIN `architecture_compatibilities`'
+  printf ' ON `architecture_compatibilities`.`runs_on`=`bl_a`.`id`'
+  printf ' OR `bl_a`.`name`="any"'
+  printf ' JOIN `architectures`'
+  printf ' ON `architectures`.`id`=`architecture_compatibilities`.`built_for`'
+  printf ' WHERE `architectures`.`name`!="any";\n'
+
+  printf 'REPEAT\n'
+
+    printf 'DELETE FROM `bl_copy`;\n'
+    printf 'INSERT IGNORE INTO `bl_copy` (`arch`,`pkgbase`)'
+    printf ' SELECT'
+    printf ' `bl`.`arch`,'
+    printf ' `bl`.`pkgbase`'
+    printf ' FROM `bl`;\n'
+
+    printf 'INSERT IGNORE INTO `bl` (`arch`,`pkgbase`)'
+    printf ' SELECT'
+    printf ' `a_r`.`architecture`,'
+    printf '`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 `a_ra`.`name`!="any"'
+    # 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 ' AND EXISTS ('
+      printf 'SELECT 1'
+      printf ' FROM `install_target_providers`'
+      printf ' WHERE `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
+    printf ') AND NOT EXISTS ('
+      printf 'SELECT 1'
+      printf ' FROM `install_target_providers`'
+      printf ' WHERE NOT EXISTS ('
+        printf 'SELECT 1'
+        printf ' FROM `bl_copy`'
+        printf ' JOIN `package_sources` AS `b_ps`'
+        printf ' ON `bl_copy`.`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 ');\n'
+
+  printf 'UNTIL ROW_COUNT()=0\n'
+  printf 'END REPEAT;\n'
+
+  printf 'SELECT'
+  printf ' `architectures`.`name`,'
+  printf '`bl`.`pkgbase`'
+  printf ' FROM `bl`'
+  printf ' JOIN `architectures`'
+  printf ' ON `architectures`.`id`=`bl`.`arch`'
+  printf ';\n'
+
+  printf 'DROP TEMPORARY TABLE `bl`;\n'
+
+  printf 'END\n'
+  printf '//\n'
+
   printf 'DELIMITER ;\n'
 
   if [ "x$1" != 'x-no-privileges' ]; then