bin/put-upstream-packages-into-db new

1 files changed, 64 insertions, 0 deletions

diff --git a/bin/put-upstream-packages-into-db b/bin/put-upstream-packages-into-db
new file mode 100755
index 0000000..0989b8c
--- /dev/null
+++ b/bin/put-upstream-packages-into-db
@@ -0,0 +1,64 @@
+#!/bin/bash
+
+# shellcheck source=../lib/load-configuration
+. "${0%/*}/../lib/load-configuration"
+
+upstream_packages=$(mktemp "${work_dir}/tmp.put-upstream-packages-into-db.XXXXXXXXXX")
+trap 'rm "${upstream_packages}"' EXIT
+
+# shellcheck disable=SC2016
+{
+  printf 'SELECT `upstream_repositories`.`name`'
+  printf ' FROM `upstream_repositories`'
+} | \
+  mysql_run_query | \
+  while read -r repo; do
+    tar -Oxzf "/var/lib/pacman/sync/${repo}.db" | \
+      sed -n '
+        /^%FILENAME%$/ {
+          N
+          s/.*\n//
+          s/\.pkg\.tar\.xz$//
+          s/-\(\([^-:]\+\):\)\?\([^-:]\+\)-\([^-]\+\)-\([^-]\+\)$/\t\2\t\3\t\4\t\5/
+          s/\s\s/\t0\t/
+          s/^/'"${repo}"'\t/
+          p
+        }
+      '
+  done > \
+  "${upstream_packages}"
+
+# shellcheck disable=SC2016
+{
+  printf 'CREATE TEMPORARY TABLE `up`('
+  printf '`id` bigint(20) NOT NULL AUTO_INCREMENT,'
+  printf '`pkgname` VARCHAR(64) NOT NULL,'
+  printf '`epoch` MEDIUMINT(9) NOT NULL,'
+  printf '`pkgver` VARCHAR(64) COLLATE utf8mb4_unicode_ci NOT NULL,'
+  printf '`pkgrel` MEDIUMINT(9) NOT NULL,'
+  printf '`architecture` VARCHAR(6) COLLATE utf8mb4_unicode_ci NOT NULL,'
+  printf '`repository` VARCHAR(32) NOT NULL,'
+  printf 'PRIMARY KEY (`id`),'
+  printf 'UNIQUE KEY `content` (`pkgname`,`epoch`,`pkgver`,`pkgrel`),'
+  printf 'UNIQUE KEY `location` (`pkgname`,`repository`),'
+  printf 'KEY `repository` (`repository`)'
+  printf ');\n'
+  printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `up`(`repository`,`pkgname`,`epoch`,`pkgver`,`pkgrel`,`architecture`);\n' \
+    "${upstream_packages}"
+  printf 'DELETE `upstream_packages`'
+  printf ' FROM `upstream_packages`;\n'
+  printf 'INSERT IGNORE INTO `upstream_packages`(`id`,`pkgname`,`epoch`,`pkgver`,`pkgrel`,`architecture`,`repository`)'
+  printf ' SELECT '
+  printf '`up`.`%s`,' \
+    'id' \
+    'pkgname' \
+    'epoch' \
+    'pkgver' \
+    'pkgrel' \
+    'architecture'
+  printf '`upstream_repositories`.`id`'
+  printf ' FROM `up`'
+  printf ' JOIN `upstream_repositories`'
+  printf ' ON `upstream_repositories`.`name`=`up`.`repository`;\n'
+} | \
+  mysql_run_query