From 1a21a2040105b8f07bcbe85530e95588b2ca6709 Mon Sep 17 00:00:00 2001
From: Erich Eckner <git@eckner.net>
Date: Thu, 7 Mar 2019 13:41:05 +0100
Subject: bin/put-upstream-packages-into-db new

---
 bin/put-upstream-packages-into-db | 64 +++++++++++++++++++++++++++++++++++++++
 1 file changed, 64 insertions(+)
 create mode 100755 bin/put-upstream-packages-into-db

(limited to 'bin/put-upstream-packages-into-db')

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
-- 
cgit v1.2.3-70-g09d2