bin/mysql-functions: mysql_repair_binary_packages_without_build_assignment started (and finished?)

1 files changed, 60 insertions, 0 deletions

diff --git a/bin/mysql-functions b/bin/mysql-functions
index d0861e7..1f2e882 100755
--- a/bin/mysql-functions
+++ b/bin/mysql-functions
@@ -771,3 +771,63 @@ mysql_query_select_pkgbase_and_revision() {
     'package_sources' 'package_sources' 'build_assignments' 'package_source' \
     'upstream_repositories' 'upstream_repositories' 'package_sources' 'upstream_package_repository'
 }
+
+# mysql_repair_binary_packages_without_build_assignment
+# try to generate valid build assignments to binary packages without
+# a valid one yet
+mysql_repair_binary_packages_without_build_assignment() {
+  {
+    printf 'SELECT '
+    printf '`binary_packages`.`id`'
+    printf ',replace(to_base64(%s),"\\n","")' \
+      '`binary_packages`.`pkgname`' \
+      '`architectures`.`name`'
+    printf ' FROM `binary_packages`'
+    printf ' JOIN `architectures` ON `binary_packages`.`architecture`=`architectures`.`id`'
+    printf ' WHERE `binary_packages`.`build_assignment`<0'
+  } | \
+    ${mysql_command} --raw --batch | \
+    sed '1d' | \
+    while read -r id pkgname arch; do
+      pkgname=$(
+        printf '%s' "${pkgname}" | \
+          base64 -d
+      )
+      pkgbase=$(
+        curl -Ss "$(
+          printf 'https://www.archlinux.org/packages/search/json/?name=%s' \
+            "${pkgname}"
+        )" | \
+          sed '
+            s/^.*"results":\s*\[//
+            s/}\s*,\s*{/\n/g
+          ' | \
+          grep '"pkgname":\s*"'"$(str_to_regex "${pkgname}")"'"' | \
+          tr ',' '\n' | \
+          grep '"pkgbase":' | \
+          cut -d'"' -f4
+      )
+      if [ -z "${pkgbase}" ]; then
+        >&2 printf 'Could not find "%s" upstream.\n' "${pkgname}"
+        continue
+      fi
+      pkgbase=$(
+        printf '%s' "${pkgbase}" | \
+          base64 -w0
+      )
+      printf 'INSERT IGNORE INTO `build_assignments` (`package_source`,`architecture`,`is_blocked`,`is_broken`,`priority`)'
+      printf ' SELECT `package_sources`.`id`,`architectures`.`id`,0,0,0'
+      printf ' FROM `package_sources`'
+      printf ' JOIN `architectures`'
+      printf ' WHERE `package_sources`.`pkgbase`=from_base64("%s")' "${pkgbase}"
+      printf ' AND `architectures`.`name`=from_base64("%s")' "${arch}"
+      printf ' LIMIT 1;\n'
+      printf 'UPDATE `binary_packages`'
+      printf ' JOIN `build_assignments`'
+      printf ' JOIN `package_sources` ON `build_assignments`.`package_source`=`package_sources`.`id`'
+      printf ' SET `binary_packages`.`build_assignment`=`build_assignments`.`id`'
+      printf ' WHERE `binary_packages`.`id`=%s' "${id}"
+      printf ' AND `package_sources`.`pkgbase`=from_base64("%s");\n' "${pkgbase}"
+    done | \
+    ${mysql_command}
+}