lib/mysql-functions: mysql_create_build_environment_packages() new

1 files changed, 37 insertions, 0 deletions

diff --git a/lib/mysql-functions b/lib/mysql-functions
index ae3612b..f20133c 100755
--- a/lib/mysql-functions
+++ b/lib/mysql-functions
@@ -1829,3 +1829,40 @@ mysql_query_delete_packages() {
     printf ';\n'
   done
 }
+
+# mysql_create_build_environment_packages
+# create and populate the `build_environment_packages`(`binary_packages`.`id`,`architectures`.`id`) table
+# which lists all the packages, that can be installed in the build environments
+# (possibly ignoring dependencies)
+mysql_create_build_environment_packages() {
+  printf 'CREATE TEMPORARY TABLE `build_environment_packages`('
+    printf '`package` BIGINT NOT NULL,'
+    printf '`architecture` SMALLINT NOT NULL,'
+    printf 'KEY `package`(`package`),'
+    printf 'KEY `architecture`(`architecture`),'
+    printf 'UNIQUE KEY `content`(`package`,`architecture`)'
+  printf ');\n'
+  printf 'INSERT IGNORE INTO `build_environment_packages`('
+    printf '`package`,'
+    printf '`architecture`'
+  printf ') SELECT'
+  printf ' `binary_packages`.`id`,'
+  printf '`repositories`.`architecture`'
+  printf ' FROM `binary_packages`'
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
+  printf ' AND `repositories`.`is_on_master_mirror`'
+  printf ' LEFT JOIN ('
+    printf '`binary_packages` AS `newer_bp`'
+    mysql_join_binary_packages_binary_packages_in_repositories 'newer_bp' 'newer_bpir'
+    mysql_join_binary_packages_in_repositories_repositories 'newer_bpir' 'newer_r'
+    printf ' AND `newer_r`.`is_on_master_mirror`'
+    printf ' JOIN `repository_stability_relations`'
+    printf ' ON `repository_stability_relations`.`less_stable`=`newer_r`.`stability`'
+    printf ' AND `repository_stability_relations`.`less_stable`!=`repository_stability_relations`.`more_stable`'
+  printf ')'
+  printf ' ON `newer_bp`.`pkgname`=`binary_packages`.`pkgname`'
+  printf ' AND `newer_r`.`architecture`=`repositories`.`architecture`'
+  printf ' AND `repository_stability_relations`.`more_stable`=`repositories`.`stability`'
+  printf ' WHERE `newer_bp`.`id` IS NULL;\n'
+}