From b46fb496ee7425fdee5e1b743e772184e27e71f7 Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Mon, 16 Apr 2018 11:54:15 +0200 Subject: remove `files` and `file_providers` - they're not practicable in the database --- bin/bootstrap-mysql | 12 +--- bin/return-assignment | 22 ------ conf/default.conf | 11 --- lib/mysql-functions | 107 ---------------------------- misc/database-layout.dmf | 182 +++++------------------------------------------ 5 files changed, 20 insertions(+), 314 deletions(-) diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql index ca84bcf..822d995 100755 --- a/bin/bootstrap-mysql +++ b/bin/bootstrap-mysql @@ -165,17 +165,7 @@ if [ ! "$1" = 'slim' ]; then ' locked_tasks_count MEDIUMINT' \ ' blocked_tasks_count MEDIUMINT' \ ' next_tasks_count MEDIUMINT' \ - ' UNIQUE date' \ - 'files BIGINT' \ - ' path TEXT' \ - ' name TEXT' \ - ' name_hash VARCHAR(128)' \ - ' absolute_name_hash VARCHAR(128)' \ - 'file_providers BIGINT' \ - ' package BIGINT :binary_packages' \ - ' file BIGINT :files' - # TODO: files and file_providers is currently not represented - # correctly, because they are not representable in this form + ' UNIQUE date' ) # shellcheck disable=SC2016 diff --git a/bin/return-assignment b/bin/return-assignment index 630349c..3c6e3d1 100755 --- a/bin/return-assignment +++ b/bin/return-assignment @@ -426,25 +426,6 @@ find . -maxdepth 1 -name '*.pkg.tar.xz' \ ' > \ "${tmp_dir}/sha512sums" -# generate file lists -find . -maxdepth 1 -name '*.pkg.tar.xz' \ - -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,/\([^\t/]\+\)$,/\t\1, - p - ' > \ - "${tmp_dir}/files" - # move namcap.logs find . -maxdepth 1 -name '*.pkg.tar.xz-namcap.log.gz' -execdir mv '{}' "${build_log_directory}/success/" \; @@ -470,9 +451,6 @@ trigger_mirror_refreshs # shellcheck disable=SC2016 { - # insert file lists into database -# 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' printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `pkg_hashes`;\n' \ diff --git a/conf/default.conf b/conf/default.conf index c06b16e..efbb5b4 100755 --- a/conf/default.conf +++ b/conf/default.conf @@ -156,14 +156,3 @@ if [ "${master_build_server_identity}" = "${work_dir}/.ssh/id_rsa" ] && \ mkdir -p "${master_build_server_identity%/*}" ssh-keygen -b4096 -f "${master_build_server_identity}" fi - -#mysql_files_table_partitions=$( -# # shellcheck disable=SC2016 -# printf 'SHOW CREATE TABLE `files`;\n' | \ -# mysql_run_query | \ -# sed -n ' -# s/^.*PARTITIONS \([0-9]\+\) .*$/\1/ -# T -# p -# ' -#) diff --git a/lib/mysql-functions b/lib/mysql-functions index 65df38a..c799877 100755 --- a/lib/mysql-functions +++ b/lib/mysql-functions @@ -783,7 +783,6 @@ mysql_find_build_assignment_loops() { # clean up left overs from mysql database mysql_cleanup() { local operator - local partition if [ "$#" = '0' ]; then operator='DELETE' elif [ "$#" = '1' ] && [ "x$1" = 'xdry' ]; then @@ -893,29 +892,6 @@ mysql_cleanup() { printf ' WHERE `build_slaves`.`currently_building` IS NULL' printf ' AND (`build_slaves`.`last_action` IS NOT NULL' printf ' OR `build_slaves`.`logged_lines` IS NOT NULL);\n' - for partition in $(seq 0 $((mysql_files_table_partitions-1))); do - # remove obsolete file_providers - printf '%s FROM `file_providers` PARTITION (p%s)' \ - "${operator}" "${partition}" - printf ' WHERE NOT EXISTS (' - printf 'SELECT * FROM `binary_packages`' - printf ' WHERE `binary_packages`.`id`=`file_providers`.`package`' - printf ') OR NOT EXISTS (' - printf 'SELECT * FROM `files` PARTITION (p%s)' \ - "${partition}" - printf ' WHERE `files`.`id`=`file_providers`.`file`' - printf ' AND `files`.`name_hash`=`file_providers`.`file_name_hash`' - printf ');\n' - # remove obsolete files - printf '%s FROM `files` PARTITION (p%s)' \ - "${operator}" "${partition}" - printf ' WHERE NOT EXISTS (' - printf 'SELECT * FROM `file_providers` PARTITION (p%s)' \ - "${partition}" - printf ' WHERE `files`.`id`=`file_providers`.`file`' - printf ' AND `files`.`name_hash`=`file_providers`.`file_name_hash`' - printf ');\n' - done } | \ mysql_run_query } @@ -1140,9 +1116,6 @@ for link in \ 'failed_builds:build_assignment:build_assignments' \ 'failed_builds:build_slave:build_slaves' \ \ - 'file_providers:package:binary_packages' \ - 'file_providers:file:files' \ - \ 'install_target_providers:package:binary_packages' \ 'install_target_providers:install_target:install_targets' \ 'install_target_providers:install_target depending_on:dependencies' \ @@ -1173,83 +1146,3 @@ 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 - - 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 $((mysql_files_table_partitions-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' \ - "${mysql_files_table_partitions}" "${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' \ - "${mysql_files_table_partitions}" "${partition}" - done - -} diff --git a/misc/database-layout.dmf b/misc/database-layout.dmf index ab9178d..e6196bc 100644 --- a/misc/database-layout.dmf +++ b/misc/database-layout.dmf @@ -152,84 +152,6 @@ - - - 796 - 131 - - - file_providers - #ffffff - - - id - BIGINT - True - True - - - package - BIGINT - True - False - - - file - BIGINT - True - False - - - file_name_hash - MEDIUMINT - True - False - - -
- - - - 785 - 6 - - - files - #ffffff - - - id - BIGINT - True - True - - - path - TEXT - True - False - - - name - TEXT - True - False - - - name_hash - MEDIUMINT - True - False - - - absolute_name_hash - VARCHAR(54) - True - False - - -
- 754 @@ -550,8 +472,8 @@ - 611 - 89 + 669 + 128 install_target_providers @@ -772,8 +694,8 @@ - 680 - 3 + 721 + 23
install_targets @@ -796,7 +718,7 @@ - 496 + 537 -6
@@ -1028,72 +950,6 @@ - - - - - - 796 - 169.906 - - 180 - - - - - 480.719 - 168.288 - - 0 - - - - - - OneToMany - - Mandatory - Mandatory - - - 1 - 0 - - - - - - - - - 864 - 131 - - 90 - - - - - 871.711 - 97.8125 - - 270 - - - - - - OneToMany - - Optional - Optional - - - 2 - 0 - - - @@ -1463,7 +1319,7 @@ 480.719 - 209.05 + 198.859 0 @@ -1528,8 +1384,8 @@ - 611 - 120.906 + 669 + 159.906 180 @@ -1537,7 +1393,7 @@ 480.719 - 127.525 + 147.906 0 @@ -1561,7 +1417,7 @@ - 571.836 + 612.836 71.8125 270 @@ -1570,7 +1426,7 @@ 480.719 - 86.7625 + 96.9531 0 @@ -1594,16 +1450,16 @@ - 689.109 - 89 + 747.109 + 128 90 - 730.016 - 52.8125 + 771.016 + 72.8125 270 @@ -1627,7 +1483,7 @@ - 647.672 + 688.672 32.9062 0 @@ -1635,8 +1491,8 @@ - 680 - 27.9062 + 721 + 47.9062 180 @@ -1693,7 +1549,7 @@ - 496 + 537 32.9062 180 -- cgit v1.2.3-54-g00ecf