lib/mysql-functions: mysql_query_delete_packages() new

1 files changed, 81 insertions, 0 deletions

diff --git a/lib/mysql-functions b/lib/mysql-functions
index 48fb037..5d63cd5 100755
--- a/lib/mysql-functions
+++ b/lib/mysql-functions
@@ -1741,3 +1741,84 @@ mysql_determine_majority_build_slave_architecture_id() {
     mysql_run_query | \
     cut -f2
 }
+
+# mysql_query_delete_packages $package_sources_condition $repositories_condition
+
+# print a mysql query for deleting:
+#  - binary_packages_in_repositories matching $condition
+#  - binary_packages linked to *only* deleted
+#    binary_packages_in_repositories
+#  - build_assignments linked to *only* deleted binary_packages
+#  - package_sources linked to *only* deleted build_assignments
+# This is done in reverse order to not loose the handle on the
+# to-be-deleted binary_packages_in_repositories
+mysql_query_delete_packages() {
+  local depth package_sources_condition repositories_condition
+  package_sources_condition="$1"
+  repositories_condition="$2"
+  for depth in 4 3 2 1; do
+    printf 'DELETE '
+    if [ ${depth} -ge 4 ]; then
+      # try to delete package_sources, too
+      printf '`package_sources`,'
+    fi
+    if [ ${depth} -ge 3 ]; then
+      # try to delete build_assignments, too
+      printf '`build_assignments`,'
+    fi
+    if [ ${depth} -ge 2 ]; then
+      # try to delete binary_packages, too
+      printf '`binary_packages`,'
+    fi
+    printf '`binary_packages_in_repositories`'
+    printf ' FROM `repositories`'
+    mysql_join_repositories_binary_packages_in_repositories
+    printf ' AND %s' \
+      "${repositories_condition}"
+    mysql_join_binary_packages_in_repositories_binary_packages
+    mysql_join_binary_packages_build_assignments
+    mysql_join_build_assignments_package_sources
+    printf ' AND %s' \
+      "${package_sources_condition}"
+    case "${depth}" in
+      '2')
+        printf ' LEFT JOIN ('
+          printf '`binary_packages_in_repositories` AS `keep_bpir`'
+          mysql_join_binary_packages_in_repositories_repositories 'keep_bpir' 'keep_r'
+          printf ' AND %s' "${repositories_condition}" \
+          | sed '
+            s/\b`repositories`\b/`keep_r`/g
+          '
+        printf ') ON `keep_bpir`.`binary_package`=`binary_packages`.`id`'
+      ;;
+      '3')
+        printf ' LEFT JOIN ('
+          printf '`binary_packages` AS `keep_bp`'
+          mysql_join_binary_packages_binary_packages_in_repositories 'keep_bp' 'keep_bpir'
+          mysql_join_binary_packages_in_repositories_repositories 'keep_bpir' 'keep_r'
+          printf ' AND %s' "${repositories_condition}" \
+          | sed '
+            s/\b`repositories`\b/`keep_r`/g
+          '
+        printf ') ON `keep_bp`.`build_assignment`=`build_assignments`.`id`'
+      ;;
+      '4')
+        printf ' LEFT JOIN ('
+          printf '`build_assignments` AS `keep_ba`'
+          mysql_join_build_assignments_binary_packages 'keep_ba' 'keep_bp'
+          mysql_join_binary_packages_binary_packages_in_repositories 'keep_bp' 'keep_bpir'
+          mysql_join_binary_packages_in_repositories_repositories 'keep_bpir' 'keep_r'
+          printf ' AND %s' "${repositories_condition}" \
+          | sed '
+            s/\b`repositories`\b/`keep_r`/g
+          '
+        printf ') ON `keep_ba`.`package_source`=`package_sources`.`id`'
+      ;;
+    esac
+    if [ ${depth} -ge 2 ]; then
+      # only if all packages unbuilt or on the deletion-list
+      printf ' WHERE `keep_r`.`id` IS NULL'
+    fi
+    printf ';\n'
+  done
+}