lib/mysql-functions, bin/return-assignment: mysql_insert_package_files_query() new and used

2 files changed, 97 insertions, 36 deletions

diff --git a/bin/return-assignment b/bin/return-assignment
index 96e7df2..db17e9b 100755
--- a/bin/return-assignment
+++ b/bin/return-assignment
@@ -426,11 +426,20 @@ find . -maxdepth 1 -name '*.pkg.tar.xz' \
 
 # generate file lists
 find . -maxdepth 1 -name '*.pkg.tar.xz' \
-  -exec pacman -Qlp {} \; | \
-  sed '
+  -printf '%f %p\n' | \
+  while read -r pkgfile path; do \
+    pacman -Qqlp "${path}" | \
+      sed '
+        s/^/'"${pkgfile}"'\t/
+      '
+  done | \
+  sed -n '
+    s/-\(\([^-:]\+\):\)\?\([^-:]\+\)-\([^-.]\+\)\(\.\([^-.]\+\)\)\?-\([^-]\+\)\.pkg\.tar\.xz\t/\t\2\t\3\t\4\t\6\t\7\t/
+    T
+    s/\t\t/\t0\t/g
     s,/$,,
-    s,\s\+,\t,
-    s,\t\(\S*/\)\([^/]\+\),\t\1\t\2,
+    s,/\([^\t/]\+\)$,/\t\1,
+    p
   ' > \
   "${tmp_dir}/files"
 
@@ -460,38 +469,7 @@ trigger_mirror_refreshs
 # shellcheck disable=SC2016
 {
   # insert file lists into database
-  printf 'CREATE TEMPORARY TABLE `pkg_files` ('
-  printf '`pkgname` VARCHAR(64),'
-  printf '`path` TEXT,'
-  printf '`name` TEXT,'
-  printf '`name_hash` VARCHAR(128),'
-  printf '`absolute_name_hash` VARCHAR(128)'
-  printf ');\n'
-  printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `pkg_files` (`pkgname`,`path`,`name`);\n' \
-    "${tmp_dir}/files"
-  printf 'UPDATE `pkg_files`'
-  printf ' SET `pkg_files`.`name_hash`=sha2(`pkg_files`.`name`,512),'
-  printf ' `pkg_files`.`absolute_name_hash`=sha2(concat(`pkg_files`.`path`,`pkg_files`.`name`),512);\n'
-  printf 'ALTER IGNORE TABLE `pkg_files` ADD UNIQUE INDEX `absolute_name_hash` (`absolute_name_hash`);\n'
-  printf 'INSERT IGNORE INTO `files` (`path`,`name`,`name_hash`,`absolute_name_hash`)'
-  printf ' SELECT '
-  printf '`pkg_files`.`%s`,' \
-    'path' 'name' 'name_hash' 'absolute_name_hash' | \
-    sed 's/,$//'
-  printf ' FROM `pkg_files`;\n'
-  printf 'INSERT IGNORE INTO `file_providers` (`package`,`file`)'
-  printf ' SELECT `binary_packages`.`id`,`files`.`id`'
-  printf ' FROM `pkg_files`'
-  printf ' JOIN `files` ON `files`.`absolute_name_hash`=`pkg_files`.`absolute_name_hash`'
-  printf ' JOIN `binary_packages` ON `binary_packages`.`pkgname`=`pkg_files`.`pkgname`'
-  mysql_join_binary_packages_build_slaves
-  mysql_join_binary_packages_repositories
-  printf ' WHERE `build_slaves`.`name`=from_base64("%s")' \
-    "$(
-      printf '%s' "${slave}" | \
-        base64 -w0
-    )"
-  printf ' AND `repositories`.`name`="build-list";\n'
+  mysql_insert_package_files_query "${tmp_dir}/files"
 
   # insert checksums into database
   printf 'CREATE TEMPORARY TABLE `pkg_hashes` (`sha512sum` VARCHAR(128), `pkgfile` VARCHAR(128));\n'
diff --git a/lib/mysql-functions b/lib/mysql-functions
index da58fe7..095f1cc 100755
--- a/lib/mysql-functions
+++ b/lib/mysql-functions
@@ -1166,3 +1166,86 @@ for link in \
   mysql_join__generic "${table_a}" "${column_a}" "${table_b}" "${column_b}"
   mysql_join__generic "${table_b}" "${column_b}" "${table_a}" "${column_a}"
 done
+
+# mysql_insert_package_files_query $file_list_file
+# Writes a query, inserting files listed in $file_list_file into database.
+# The file is expected to have the following \t separated columns:
+# pkgname epoch pkgver pkgrel sub_pkgrel arch path name
+mysql_insert_package_files_query() {
+
+  local partition
+  local partitions_count
+
+  partitions_count=16
+
+  if [ ! -r "$1" ]; then
+    >&2 printf 'mysql_insert_package_files: File "%s" is not readable.\n' \
+      "$1"
+    return 2
+  fi
+
+  printf 'DROP TEMPORARY TABLE IF EXISTS `pkg_files`;\n'
+  printf 'CREATE TEMPORARY TABLE `pkg_files` ('
+  printf '`pkgname` VARCHAR(64),'
+  printf '`epoch` MEDIUMINT,'
+  printf '`pkgver` VARCHAR(64),'
+  printf '`pkgrel` MEDIUMINT,'
+  printf '`sub_pkgrel` MEDIUMINT,'
+  printf '`arch` VARCHAR(16),'
+  printf '`path` TEXT,'
+  printf '`name` TEXT,'
+  printf '`absolute_name_hash` VARCHAR(56),'
+  printf '`name_hash` MEDIUMINT'
+  printf ');\n'
+  printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `pkg_files`' \
+    "${1}"
+  printf ' (`pkgname`, `epoch`, `pkgver`, `pkgrel`, `sub_pkgrel`, `arch`, `path`, `name`);\n'
+  printf 'UPDATE `pkg_files` SET'
+  printf ' `pkg_files`.`name_hash`=CAST(CONV(SUBSTR(SHA2(`pkg_files`.`name`,224),1,2),16,10) AS INT),'
+  printf ' `pkg_files`.`absolute_name_hash`=SHA2(CONCAT(`pkg_files`.`path`,`pkg_files`.`name`),224);\n'
+  for partition in $(seq 0 $((partitions_count-1))); do
+    printf 'INSERT IGNORE INTO `files` PARTITION (p%s) (`path`,`name`,`name_hash`,`absolute_name_hash`)' \
+      "${partition}"
+    printf ' SELECT '
+    printf '`pkg_files`.`%s`,' \
+      'path' 'name' 'name_hash' 'absolute_name_hash' | \
+      sed 's/,$//'
+    printf ' FROM `pkg_files`'
+    printf ' WHERE `pkg_files`.`name_hash` MOD %s = %s;\n' \
+      "${partitions_count}" "${partition}"
+    printf 'INSERT IGNORE INTO `file_providers` PARTITION (p%s) (`package`,`file`,`file_name_hash`)' \
+      "${partition}"
+    printf ' SELECT `binary_packages`.`id`,`files`.`id`,`files`.`name_hash` FROM `pkg_files`'
+    printf ' JOIN `binary_packages` ON'
+    printf ' `binary_packages`.`%s`=`pkg_files`.`%s` AND' \
+      'pkgname' 'pkgname' \
+      'epoch' 'epoch' \
+      'pkgver' 'pkgver' \
+      'pkgrel' 'pkgrel' \
+      'sub_pkgrel' 'sub_pkgrel' | \
+      sed 's/ AND$//'
+    if [ -n "${slave}" ]; then
+      mysql_join_binary_packages_build_slaves
+      printf ' AND `build_slaves`.`name`=from_base64("%s")' \
+        "$(
+          printf '%s' "${slave}" | \
+            base64 -w0
+        )"
+    fi
+    mysql_join_binary_packages_architectures
+    printf ' AND `pkg_files`.`arch`=`architectures`.`name`'
+    mysql_join_binary_packages_repositories
+    if [ -n "${slave}" ]; then
+      printf ' AND `repositories`.`name`="build-list"'
+    else
+      printf ' AND `repositories`.`is_on_master_mirror`'
+    fi
+    printf ' JOIN `files` PARTITION (p%s)' \
+      "${partition}"
+    printf ' ON `files`.`name_hash`=`pkg_files`.`name_hash`'
+    printf ' AND `files`.`absolute_name_hash`=`pkg_files`.`absolute_name_hash`'
+    printf ' WHERE `pkg_files`.`name_hash` MOD %s = %s;\n' \
+      "${partitions_count}" "${partition}"
+  done
+
+}