bin/check-bugtracker: use temporary table

1 files changed, 32 insertions, 50 deletions

diff --git a/bin/check-bugtracker b/bin/check-bugtracker
index 0d28379..ad23089 100755
--- a/bin/check-bugtracker
+++ b/bin/check-bugtracker
@@ -9,58 +9,40 @@
 # shellcheck source=../lib/load-configuration
 . "${0%/*}/../lib/load-configuration"
 
-bug_list=$(
-  curl -LSs 'https://bugs.archlinux32.org/index.php?export_list=Export%20Tasklist' | \
-    sed -n '
-      1d
-      s/^[^,]\+,"//
-      T
-      s/^\([^"]\+\)"\(,[^,]\+\)\{2\},"\([^"]\+\)".*$/"\1" "\3"/
-      T
-      p
-    '
-)
+tmp_file=$(mktemp 'tmp.check-bugtracker.XXXXXXXX' --tmpdir)
+trap 'rm "${tmp_file}"' EXIT
+
+curl -LSs 'https://bugs.archlinux32.org/index.php?export_list=Export%20Tasklist' \
+| sed -n '
+  1d
+  s/^[^,]\+,"\([^"]\+\)"\(,[^,]\+\)\{2\},"\([^"]\+\)",.*$/\1\t\3/
+  T
+  :a
+    h
+    s/^\(.*\t\).*\[\([^]]\+\)].*$/\1\2/
+    T
+    p
+    g
+    s/^\(.*\t.*\)\[[^]]\+]\(.*\)$/\1\2/
+    ta
+' \
+> "${tmp_file}"
 
 # shellcheck disable=SC2016
 {
-  printf 'SELECT `repository_stabilities`.`id`,`repository_stabilities`.`bugtracker_category`'
-  printf ' FROM `repository_stabilities`'
-  printf ' WHERE NOT `repository_stabilities`.`bugtracker_category` IS NULL'
+  printf 'CREATE TEMPORARY TABLE `bugs`('
+    printf '`bugtracker_category` VARCHAR(32),'
+    printf '`pkgname` VARCHAR(64)'
+  printf ');\n';
+  printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `bugs`;\n' \
+    "${tmp_file}"
+  printf 'UPDATE `binary_packages`'
+  mysql_join_binary_packages_binary_packages_in_repositories
+  mysql_join_binary_packages_in_repositories_repositories
+  mysql_join_repositories_repository_stabilities
+  printf ' LEFT JOIN `bugs`'
+  printf ' ON `bugs`.`pkgname`=`binary_packages`.`pkgname`'
+  printf ' AND `bugs`.`bugtracker_category`=`repository_stabilities`.`bugtracker_category`'
+  printf ' SET `binary_packages`.`has_issues`=(`bugs`.`pkgname` IS NOT NULL);\n'
 } | \
-  mysql_run_query 'unimportant' | \
-  while read -r stability_id category; do
-    for has_issues in '1:' '0:NOT '; do
-      printf 'UPDATE `binary_packages`'
-      mysql_join_binary_packages_binary_packages_in_repositories
-      mysql_join_binary_packages_in_repositories_repositories
-      printf ' SET `has_issues`=%s' \
-        "${has_issues%:*}"
-      printf ' WHERE `repositories`.`stability`=%s' \
-        "${stability_id}"
-      printf ' AND `binary_packages`.`pkgname` %sIN (' \
-        "${has_issues#*:}"
-      printf '%s\n' "${bug_list}" | \
-        sed -n '
-          s/^"'"$(str_to_regex "${category}")"'" //
-          T
-          :a
-            /\[.*]/ {
-              s/^[^[]*\[//
-              T
-              h
-              s/].*$//
-              p
-              x
-              ba
-            }
-        ' | \
-        base64_encode_each | \
-        sed '
-          s/^/from_base64("/
-          s/$/")/
-        ' | \
-        tr '\n' ','
-      printf '"");\n'
-    done
-  done | \
   mysql_run_query 'unimportant'