From 792664b9373ded19f242163b3f9cfe16c224d5db Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Thu, 29 Mar 2018 13:33:34 +0200 Subject: lib/mysql-functions, bin/return-assignment: mysql_insert_package_files_query() new and used --- bin/return-assignment | 50 +++++++++---------------------- lib/mysql-functions | 83 +++++++++++++++++++++++++++++++++++++++++++++++++++ 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 + +} -- cgit v1.2.3-54-g00ecf