bin/mysql-functions: mysql_join_*_* new

15 files changed, 243 insertions, 219 deletions

diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql
index 322e0a1..30c260a 100755
--- a/bin/bootstrap-mysql
+++ b/bin/bootstrap-mysql
@@ -512,9 +512,8 @@ find "${work_dir}/package-states" -name '*.blocked' -printf '%p %f\n' | \
   ' | \
   while read -r state_file pkgbase git_revision mod_git_revision repository; do
     printf 'UPDATE `build_assignments`'
-    printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-      'package_sources'       'package_sources'       'build_assignments' 'package_source' \
-      'upstream_repositories' 'upstream_repositories' 'package_sources' 'upstream_package_repository'
+    mysql_join_build_assignments_package_sources
+    mysql_join_package_sources_upstream_repositories
     printf ' SET `build_assignments`.`is_blocked`=from_base64("%s")' \
       "$(
         tr -d '\n' < \
@@ -563,10 +562,9 @@ grep '^\('"$(
   "${tmp_dir}/new-stable-packages"
 
 if [ -s "${tmp_dir}/new-stable-packages" ]; then
+  # shellcheck disable=SC2016
   {
-    # shellcheck disable=SC2016
     printf 'CREATE TEMPORARY TABLE `stable_packages` ('
-    # shellcheck disable=SC2016
     printf '`%s` %s,' \
       'pkgname' 'VARCHAR(64)' \
       'epoch' 'MEDIUMINT' \
@@ -576,9 +574,7 @@ if [ -s "${tmp_dir}/new-stable-packages" ]; then
       'architecture' 'VARCHAR(16)' \
       'repository' 'VARCHAR(64)' \
       'build_assignment' 'BIGINT NOT NULL AUTO_INCREMENT'
-    # shellcheck disable=SC2016
     printf 'PRIMARY KEY (`build_assignment`));\n'
-    # shellcheck disable=SC2016
     sed '
       1~10 ! b not_start
       s/^/INSERT IGNORE INTO `stable_packages` (`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architecture`,`repository`) VALUES \n/
@@ -589,28 +585,22 @@ if [ -s "${tmp_dir}/new-stable-packages" ]; then
       :end
       s/,$/;/
     ' "${tmp_dir}/new-stable-packages"
-    # shellcheck disable=SC2016
     printf 'INSERT IGNORE INTO `binary_packages` (`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architecture`,`repository`,`build_assignment`,`has_issues`,`is_tested`,`is_to_be_deleted`)'
     printf ' SELECT '
-    # shellcheck disable=SC2016
     printf '`stable_packages`.`%s`,' \
       'pkgname' \
       'epoch' \
       'pkgver' \
       'pkgrel' \
       'sub_pkgrel'
-    # shellcheck disable=SC2016
     printf '`%s`.`id`,' \
       'architectures' \
       'repositories'
-    # shellcheck disable=SC2016
     printf -- '-`build_assignment`,0,1,0 FROM `stable_packages`'
-    # shellcheck disable=SC2016
     printf ' JOIN `%s` ON `stable_packages`.`%s`=`%s`.`name`' \
       'repositories' 'repository' 'repositories' \
       'architectures' 'architecture' 'architectures'
     printf ';\n'
-    # shellcheck disable=SC2016
     printf 'DROP TABLE `stable_packages`;\n'
   } | \
     ${mysql_command}
diff --git a/bin/build-master-status-from-mysql b/bin/build-master-status-from-mysql
index 7a6605a..ea8d0f1 100755
--- a/bin/build-master-status-from-mysql
+++ b/bin/build-master-status-from-mysql
@@ -21,11 +21,10 @@ fi
 {
   printf 'SELECT DISTINCT `package_sources`.`pkgbase`,`package_sources`.`git_revision`,`package_sources`.`mod_git_revision`,`upstream_repositories`.`name`'
   printf ' FROM `package_sources`'
-  printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
-    'upstream_repositories' 'package_sources' 'upstream_package_repository' 'upstream_repositories' \
-    'build_assignments' 'build_assignments' 'package_source' 'package_sources' \
-    'binary_packages' 'binary_packages' 'build_assignment' 'build_assignments' \
-    'repositories' 'binary_packages' 'repository' 'repositories'
+  mysql_join_package_sources_upstream_repositories
+  mysql_join_package_sources_build_assignments
+  mysql_join_build_assignments_binary_packages
+  mysql_join_binary_packages_repositories
   printf ' WHERE `repositories`.`name`="build-list"'
 } | \
   ${mysql_command} --batch | \
@@ -45,10 +44,9 @@ sort "${work_dir}/build-list" > \
     'pkgbase' 'git_revision' 'mod_git_revision'
   printf '`upstream_repositories`.`name`'
   printf ' FROM `build_slaves`'
-  printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-    'build_assignments' 'build_assignments' 'build_slaves' 'currently_building' \
-    'package_sources' 'package_sources' 'build_assignments' 'package_source' \
-    'upstream_repositories' 'upstream_repositories' 'package_sources' 'upstream_package_repository'
+  mysql_join_build_slaves_build_assignments
+  mysql_join_build_assignments_package_sources
+  mysql_join_package_sources_upstream_repositories
 } | \
   ${mysql_command} --raw --batch | \
   sed '
@@ -68,10 +66,9 @@ find "${work_dir}/package-states" -mindepth 1 -maxdepth 1 -name '*.locked' -prin
 {
   printf 'SELECT DISTINCT `package_sources`.`pkgbase`'
   printf ' FROM `package_sources`'
-  printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
-    'build_assignments' 'build_assignments' 'package_source' 'package_sources' \
-    'binary_packages' 'binary_packages' 'build_assignment' 'build_assignments' \
-    'repositories' 'binary_packages' 'repository' 'repositories'
+  mysql_join_package_sources_build_assignments
+  mysql_join_build_assignments_binary_packages
+  mysql_join_binary_packages_repositories
   printf ' WHERE `repositories`.`name`="deletion-list"'
   printf ' OR `binary_packages`.`is_to_be_deleted`;\n'
 } | \
@@ -89,9 +86,8 @@ sort "${work_dir}/deletion-list" > \
 {
   printf 'SELECT `package_sources`.`pkgbase`'
   printf ' FROM `package_sources`'
-  printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
-    'build_assignments' 'build_assignments' 'package_source' 'package_sources' \
-    'build_dependency_loops' 'build_dependency_loops' 'build_assignment' 'build_assignments'
+  mysql_join_package_sources_build_assignments
+  mysql_join_build_assignments_build_dependency_loops
   printf ';\n'
 } | \
   ${mysql_command} --batch | \
@@ -118,10 +114,9 @@ find "${work_dir}/build-list.loops" -regextype grep -mindepth 1 -maxdepth 1 \
     'sub_pkgrel'
   printf '`architectures`.`name`'
   printf ' FROM `binary_packages`'
-  printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-    'repositories'           'repositories'           'binary_packages' 'repository' \
-    'architectures'          'architectures'          'binary_packages' 'architecture' \
-    'repository_stabilities' 'repository_stabilities' 'repositories'    'stability'
+  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_architectures
+  mysql_join_repositories_repository_stabilities
   printf ' WHERE `binary_packages`.`is_tested`'
   printf ' AND NOT `binary_packages`.`has_issues`'
   printf ' AND `repository_stabilities`.`name`="testing"'
@@ -154,10 +149,9 @@ find "${work_dir}/package-states" -mindepth 1 -maxdepth 1 \
     'sub_pkgrel'
   printf '`architectures`.`name`'
   printf ' FROM `binary_packages`'
-  printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-    'repositories'           'repositories'           'binary_packages' 'repository' \
-    'architectures'          'architectures'          'binary_packages' 'architecture' \
-    'repository_stabilities' 'repository_stabilities' 'repositories'    'stability'
+  mysql_join_binary_packages_repositories
+  mysql_join_binary_packages_architectures
+  mysql_join_repositories_repository_stabilities
   printf ' WHERE NOT `binary_packages`.`is_tested`'
   printf ' AND NOT `binary_packages`.`has_issues`'
   printf ' AND `repository_stabilities`.`name`="testing"'
@@ -188,15 +182,13 @@ find "${work_dir}/package-states" -mindepth 1 -maxdepth 1 \
     'mod_git_revision'
   printf '`upstream_repositories`.`name`,`install_targets`.`name`'
   printf ' FROM `binary_packages`'
-  printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-    'dependencies'     'binary_packages'  'dependencies' 'dependent' \
-    'dependency_types' 'dependency_types' 'dependencies' 'dependency_type'
-  printf ' AND `dependency_types`.`relevant_for_building`'
-  printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-    'install_targets'       'install_targets'       'dependencies'      'depending_on' \
-    'build_assignments'     'build_assignments'     'binary_packages'   'build_assignment' \
-    'package_sources'       'package_sources'       'build_assignments' 'package_source' \
-    'upstream_repositories' 'upstream_repositories' 'package_sources'   'upstream_package_repository'
+  mysql_join_binary_packages_dependencies
+  mysql_join_dependencies_dependency_types
+  mysql_join_dependencies_install_targets
+  mysql_join_binary_packages_build_assignments
+  mysql_join_build_assignments_package_sources
+  mysql_join_package_sources_upstream_repositories
+  printf ' WHERE `dependency_types`.`relevant_for_building`'
 } | \
   ${mysql_command} --raw --batch | \
   sed '
@@ -214,10 +206,9 @@ find "${work_dir}/package-states" -mindepth 1 -maxdepth 1 \
       'pkgbase' 'git_revision' 'mod_git_revision'
     printf '`upstream_repositories`.`name`'
     printf ' FROM `upstream_repositories`'
-    printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
-      'package_sources'   'package_sources'   'upstream_package_repository' 'upstream_repositories' \
-      'build_assignments' 'build_assignments' 'package_source'              'package_sources' \
-      'binary_packages'   'binary_packages'   'build_assignment'            'build_assignments'
+    mysql_join_upstream_repositories_package_sources
+    mysql_join_package_sources_build_assignments
+    mysql_join_build_assignments_binary_packages
   } | \
     ${mysql_command} --raw --batch | \
     sed '
diff --git a/bin/check-bugtracker b/bin/check-bugtracker
index 72ab133..d6e92e5 100755
--- a/bin/check-bugtracker
+++ b/bin/check-bugtracker
@@ -31,7 +31,7 @@ bug_list=$(
   while read -r stability_id category; do
     for has_issues in '1:' '0:NOT '; do
       printf 'UPDATE `binary_packages`'
-      printf ' JOIN `repositories` ON `repositories`.`id`=`binary_packages`.`repository`'
+      mysql_join_binary_packages_repositories
       printf ' SET `has_issues`=%s' \
         "${has_issues%:*}"
       printf ' WHERE `repositories`.`stability`=%s' \
diff --git a/bin/common-functions b/bin/common-functions
index d7f3e41..a84271b 100755
--- a/bin/common-functions
+++ b/bin/common-functions
@@ -478,9 +478,8 @@ remove_old_package_versions() {
       printf '`binary_packages`.`sub_pkgrel`'
     printf ')'
     printf ' FROM `binary_packages`'
-    printf ' JOIN `%s` ON `%s`.`id`=`binary_packages`.`%s`' \
-      'repositories'  'repositories'  'repository' \
-      'architectures' 'architectures' 'architecture'
+    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_architectures
     printf ' JOIN `repository_stability_relations` ON `repository_stability_relations`.`less_stable`=`repositories`.`stability`'
     printf ' JOIN `repositories` AS `more_stable_repos` ON `repository_stability_relations`.`more_stable`=`more_stable_repos`.`stability`'
     # name must match
diff --git a/bin/copy-to-build-support b/bin/copy-to-build-support
index 4710012..808d041 100755
--- a/bin/copy-to-build-support
+++ b/bin/copy-to-build-support
@@ -31,8 +31,8 @@ sed -n '
       {
         printf 'SELECT `binary_packages`.`id`,`repositories`.`name`'
         printf ' FROM `binary_packages`'
-        printf ' JOIN `repositories` ON `binary_packages`.`repository`=`repositories`.`id`'
-        printf ' JOIN `architectures` ON `binary_packages`.`architecture`=`architectures`.`id`'
+        mysql_join_binary_packages_repositories
+        mysql_join_binary_packages_architectures
         printf ' WHERE'
         printf ' `binary_packages`.`%s`=from_base64("%s") AND' \
           'epoch' "$(printf '%s' "${epoch}" | base64 -w0)" \
@@ -89,7 +89,7 @@ sed -n '
       # shellcheck disable=SC2086
       printf ',`binary_packages`.`%s`' ${to_copy}
       printf ' FROM `binary_packages`'
-      printf ' JOIN `architectures` ON `binary_packages`.`architecture`=`architectures`.`id`'
+      mysql_join_binary_packages_architectures
       printf ' WHERE'
       printf ' `binary_packages`.`id`=%s;\n' \
         "${id}"
diff --git a/bin/db-update b/bin/db-update
index e5ea193..b830981 100755
--- a/bin/db-update
+++ b/bin/db-update
@@ -325,8 +325,8 @@ move_packages() {
   while read -r package; do
     while read -r part; do
       printf 'UPDATE `binary_packages`'
-      printf ' JOIN `repositories` AS `from_repo` ON `binary_packages`.`repository`=`from_repo`.`id`'
-      printf ' JOIN `architectures` ON `binary_packages`.`architecture`=`architectures`.`id`'
+      mysql_join_binary_packages_repositories '' 'from_repo'
+      mysql_join_binary_packages_architectures
       printf ' SET `binary_packages`.`repository`=('
         printf 'SELECT `to_repo`.`id`'
         printf ' FROM `repositories` as `to_repo`'
diff --git a/bin/get-assignment b/bin/get-assignment
index a7eec2d..1edcffb 100755
--- a/bin/get-assignment
+++ b/bin/get-assignment
@@ -45,11 +45,10 @@ hand_out_assignment() {
       'pkgbase' 'git_revision' 'mod_git_revision'
     printf '`upstream_repositories`.`name`,`binary_packages`.`sub_pkgrel`'
     printf ' FROM `upstream_repositories`'
-    printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
-      'package_sources'   'package_sources'   'upstream_package_repository' 'upstream_repositories' \
-      'build_assignments' 'build_assignments' 'package_source'              'package_sources' \
-      'binary_packages'   'binary_packages'   'build_assignment'            'build_assignments' \
-      'repositories'      'binary_packages'   'repository'                  'repositories'
+    mysql_join_upstream_repositories_package_sources
+    mysql_join_package_sources_build_assignments
+    mysql_join_build_assignments_binary_packages
+    mysql_join_binary_packages_repositories
     printf ' WHERE `repositories`.`name`="build-list"'
     printf ' AND `package_sources`.`%s`=from_base64("%s")' \
       'pkgbase' "$(printf '%s' "$1" | base64 -w0)" \
@@ -80,8 +79,8 @@ hand_out_assignment() {
     printf ' SET `currently_building` = ('
     printf ' SELECT `build_assignments`.`id`'
     printf ' FROM `build_assignments`'
-    printf ' JOIN `package_sources` ON `build_assignments`.`package_source`=`package_sources`.`id`'
-    printf ' JOIN `upstream_repositories` ON `package_sources`.`upstream_package_repository`=`upstream_repositories`.`id`'
+    mysql_join_build_assignments_package_sources
+    mysql_join_package_sources_upstream_repositories
     printf ' WHERE'
     printf ' `package_sources`.`%s` = from_base64("%s") AND' \
       'pkgbase'          "$(printf '%s' "$1" | base64 -w0)" \
@@ -94,8 +93,8 @@ hand_out_assignment() {
       "$(printf '%s' "${slave}" | base64 -w0)"
 
     printf 'UPDATE `build_assignments`'
-    printf ' JOIN `package_sources` ON `build_assignments`.`package_source`=`package_sources`.`id`'
-    printf ' JOIN `upstream_repositories` ON `package_sources`.`upstream_package_repository`=`upstream_repositories`.`id`'
+    mysql_join_build_assignments_package_sources
+    mysql_join_package_sources_upstream_repositories
     printf ' SET `build_assignments`.`priority`=0'
     printf ' WHERE'
     printf ' `package_sources`.`%s` = from_base64("%s") AND' \
@@ -150,10 +149,9 @@ currently_building=$(
   {
     printf 'SELECT '
     mysql_query_select_pkgbase_and_revision
-    printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
-      'build_slaves'    'build_slaves'    'currently_building' 'build_assignments' \
-      'binary_packages' 'binary_packages' 'build_assignment'   'build_assignments' \
-      'repositories'    'binary_packages' 'repository'         'repositories'
+    mysql_join_build_assignments_build_slaves
+    mysql_join_build_assignments_binary_packages
+    mysql_join_binary_packages_repositories
     printf ' WHERE `build_slaves`.`name`=from_base64("%s")' \
       "$(printf '%s' "${slave}" | base64 -w0)"
     printf ' AND `repositories`.`name`="build-list"'
@@ -189,9 +187,8 @@ next_building=$(
     mysql_query_is_part_of_loop '`build_assignments`.`id`'
     printf ' AS `part_of_loop`,'
     mysql_query_select_pkgbase_and_revision
-    printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-      'binary_packages' 'build_assignments' 'binary_packages' 'build_assignment' \
-      'repositories' 'repositories' 'binary_packages' 'repository'
+    mysql_join_build_assignments_binary_packages
+    mysql_join_binary_packages_repositories
     printf ' WHERE `repositories`.`name`="build-list"'
     printf ' AND NOT EXISTS ('
       printf ' SELECT *'
@@ -231,9 +228,8 @@ count_pending=$(
   {
     printf 'SELECT count(*)'
     printf ' FROM `build_assignments`'
-    printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-      'binary_packages' 'build_assignments' 'binary_packages' 'build_assignment' \
-      'repositories' 'repositories' 'binary_packages' 'repository'
+    mysql_join_build_assignments_binary_packages
+    mysql_join_binary_packages_repositories
     printf ' WHERE `repositories`.`name`="build-list"'
     printf ' AND `build_assignments`.`is_blocked` IS NULL'
     printf ';\n'
diff --git a/bin/get-package-updates b/bin/get-package-updates
index 89b09df..e81d128 100755
--- a/bin/get-package-updates
+++ b/bin/get-package-updates
@@ -110,22 +110,18 @@ delete_package() {
     {
       # packages from the build-list/to-be-decided go straight to the deletion-list
       printf 'UPDATE `binary_packages`'
-      printf ' JOIN `%s` ON `%s`.`id`=`binary_packages`.`%s`' \
-        'repositories' 'repositories' 'repository' \
-        'build_assignments' 'build_assignments' 'build_assignment'
-      printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-        'package_sources' 'package_sources' 'build_assignments' 'package_source'
+      mysql_join_binary_packages_repositories
+      mysql_join_binary_packages_build_assignments
+      mysql_join_build_assignments_package_sources
       printf ' SET `binary_packages`.`repository`=(SELECT `repositories`.`id` FROM `repositories` WHERE `repositories`.`name`="deletion-list")'
       printf ' WHERE `repositories`.`name` in ("build-list","to-be-decided")'
       printf ' AND `package_sources`.`pkgbase`=from_base64("%s");\n' \
         "$(printf '%s' "$1" | base64 -w0)"
       # other packages are marked as `is_to_be_deleted`
       printf 'UPDATE `binary_packages`'
-      printf ' JOIN `%s` ON `%s`.`id`=`binary_packages`.`%s`' \
-        'repositories' 'repositories' 'repository' \
-        'build_assignments' 'build_assignments' 'build_assignment'
-      printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-        'package_sources' 'package_sources' 'build_assignments' 'package_source'
+      mysql_join_binary_packages_repositories
+      mysql_join_binary_packages_build_assignments
+      mysql_join_build_assignments_package_sources
       printf ' SET `binary_packages`.`is_to_be_deleted`=1'
       printf ' WHERE `package_sources`.`pkgbase`=from_base64("%s");' \
         "$(printf '%s' "$1" | base64 -w0)"
@@ -341,10 +337,9 @@ done | \
         # which are not yet built or on the deletion list
         printf 'DELETE `build_assignments`,`binary_packages`'
         printf ' FROM `binary_packages`'
-        printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-          'build_assignments' 'build_assignments' 'binary_packages'   'build_assignment' \
-          'package_sources'   'package_sources'   'build_assignments' 'package_source' \
-          'repositories'      'repositories'      'binary_packages'   'repository'
+        mysql_join_binary_packages_build_assignments
+        mysql_join_build_assignments_package_sources
+        mysql_join_binary_packages_repositories
         printf ' WHERE `package_sources`.`pkgbase`=from_base64("%s")' \
           "$(
             printf '%s' "${package}" | \
@@ -353,10 +348,9 @@ done | \
         printf ' AND (`repositories`.`name`="build-list" OR `repositories`.`name`="deletion-list");\n'
         # remove is-to-be-deleted marker from old binary packages
         printf 'UPDATE `binary_packages`'
-        printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-          'build_assignments' 'build_assignments' 'binary_packages'   'build_assignment' \
-          'package_sources'   'package_sources'   'build_assignments' 'package_source' \
-          'repositories'      'repositories'      'binary_packages'   'repository'
+        mysql_join_binary_packages_build_assignments
+        mysql_join_build_assignments_package_sources
+        mysql_join_binary_packages_repositories
         printf ' SET `is_to_be_deleted`=0'
         printf ' WHERE `package_sources`.`pkgbase`=from_base64("%s");\n' \
           "$(
@@ -431,9 +425,9 @@ while [ -n "${black_listed_new}" ]; do
           '
         printf 'SELECT replace(to_base64(`a_ps`.`pkgbase`),"\\n","")'
         printf ' FROM `package_sources` AS `a_ps`'
-        printf ' JOIN `build_assignments` AS `a_ba` ON `a_ba`.`package_source`=`a_ps`.`id`'
-        printf ' JOIN `binary_packages` AS `a_bp` ON `a_bp`.`build_assignment`=`a_ba`.`id`'
-        printf ' JOIN `dependencies` ON `dependencies`.`dependent`=`a_bp`.`id`'
+        mysql_join_package_sources_build_assignments 'a_ps' 'a_ba'
+        mysql_join_build_assignments_binary_packages 'a_ba' 'a_bp'
+        mysql_join_binary_packages_dependencies 'a_bp'
         printf ' WHERE NOT EXISTS ('
           printf ' SELECT *'
           printf ' FROM `install_target_providers`'
@@ -441,8 +435,8 @@ while [ -n "${black_listed_new}" ]; do
             printf ' SELECT *'
             printf ' FROM `bl`'
             printf ' JOIN `package_sources` AS `b_ps` ON `bl`.`pkgbase`=`b_ps`.`pkgbase`'
-            printf ' JOIN `build_assignments` AS `b_ba` ON `b_ba`.`package_source`=`b_ps`.`id`'
-            printf ' JOIN `binary_packages` AS `b_bp` ON `b_bp`.`build_assignment`=`b_ba`.`id`'
+            mysql_join_package_sources_build_assignments 'b_ps' 'b_ba'
+            mysql_join_build_assignments_binary_packages 'b_ba' 'b_bp'
             printf ' WHERE `install_target_providers`.`package`=`b_bp`.`id`'
           printf ')'
           printf ' AND `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
@@ -607,8 +601,7 @@ fi
   done
   # move binary_packages from "to-be-decided" to "build-list"
   printf 'UPDATE `binary_packages`'
-  printf ' JOIN `repositories` AS `from_repo`'
-  printf ' ON `binary_packages`.`repository`=`from_repo`.`id`'
+  mysql_join_binary_packages_repositories '' 'from_repo'
   printf ' SET `repository`=('
     printf 'SELECT `to_repo`.`id`'
     printf ' FROM `repositories` AS `to_repo`'
diff --git a/bin/modify-package-state b/bin/modify-package-state
index b2d3133..b0ba53e 100755
--- a/bin/modify-package-state
+++ b/bin/modify-package-state
@@ -136,10 +136,9 @@ while read -r package reason; do
       # shellcheck disable=SC2016
       combiner=$(
         printf '`binary_packages`'
-        printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-          'repositories'           'repositories'           'binary_packages' 'repository' \
-          'repository_stabilities' 'repository_stabilities' 'repositories'    'stability' \
-          'architectures'          'architectures'          'binary_packages' 'architecture'
+        mysql_join_binary_packages_repositories
+        mysql_join_repositories_repository_stabilities
+        mysql_join_binary_packages_architectures
       )
       # shellcheck disable=SC2016,SC2031
       selector=$(
@@ -161,12 +160,11 @@ while read -r package reason; do
       # shellcheck disable=SC2016
       combiner=$(
         printf '`build_assignments`'
-        printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-          'package_sources'        'package_sources'        'build_assignments' 'package_source' \
-          'upstream_repositories'  'upstream_repositories'  'package_sources'   'upstream_package_repository' \
-          'binary_packages'        'build_assignments'      'binary_packages'   'build_assignment' \
-          'repositories'           'repositories'           'binary_packages'   'repository' \
-          'repository_stabilities' 'repository_stabilities' 'repositories'      'stability'
+        mysql_join_build_assignments_package_sources
+        mysql_join_package_sources_upstream_repositories
+        mysql_join_build_assignments_binary_packages
+        mysql_join_binary_packages_repositories
+        mysql_join_repositories_repository_stabilities
       )
       # shellcheck disable=SC2016
       selector=$(
@@ -238,8 +236,7 @@ while read -r package reason; do
       printf 'UPDATE `binary_packages` AS `a`'
       printf ' JOIN `binary_packages` AS `b`'
       printf ' ON `a`.`build_assignment`=`b`.`build_assignment`'
-      printf ' JOIN `repositories`'
-      printf ' ON `b`.`repository`=`repositories`.`id`'
+      mysql_join_binary_packages_repositories 'b'
       printf ' SET `b`.`is_tested`=1'
       printf ' WHERE `a`.`is_tested`'
       printf ' AND NOT `b`.`is_tested`'
diff --git a/bin/mysql-functions b/bin/mysql-functions
index 7f2cdab..6950ee8 100755
--- a/bin/mysql-functions
+++ b/bin/mysql-functions
@@ -131,7 +131,6 @@ mysql_show_binary_package() {
     shift
   done
 
-  local joint
   {
     printf 'SELECT'
     printf ' `%s`.`%s`,' \
@@ -146,17 +145,11 @@ mysql_show_binary_package() {
       'package_sources' 'mod_git_revision' \
       'upstream_repositories' 'name'
     printf ' FROM `binary_packages`'
-    for joint in \
-      'architectures:binary_packages:architecture' \
-      'package_sources:binary_packages:package_source' \
-      'repositories:binary_packages:repository' \
-      'upstream_repositories:package_sources:upstream_package_repository'; do
-      printf ' JOIN `%s` ON `%s`.`id` =' \
-        "${joint%%:*}" "${joint%%:*}"
-      joint="${joint#*:}"
-      printf ' `%s`.`%s`' \
-        "${joint%:*}" "${joint#*:}"
-    done
+    mysql_join_binary_packages_architectures
+    mysql_join_binary_packages_repositories
+    mysql_join_binary_packages_build_assignments
+    mysql_join_build_assignments_package_sources
+    mysql_join_package_sources_upstream_repositories
     printf ' WHERE'
     printf ' `%s`.`%s` = from_base64("%s") AND' \
       'binary_packages' 'pkgname' "${pkgname}" \
@@ -434,8 +427,8 @@ mysql_generate_package_metadata() {
             # build-list or in $current_repository (beware of split
             # packages!)
             printf '(SELECT `sub_pkgrel` FROM `binary_packages`'
-            printf ' JOIN `architectures` ON `binary_packages`.`architecture`=`architectures`.`id`'
-            printf ' JOIN `repositories` ON `binary_packages`.`repository`=`repositories`.`id`'
+            mysql_join_binary_packages_architectures
+            mysql_join_binary_packages_repositories
             printf ' WHERE'
             printf ' `binary_packages`.`%s`=from_base64("%s") AND' \
               'epoch' "${epoch}" \
@@ -448,7 +441,7 @@ mysql_generate_package_metadata() {
               "${current_repository}"
             # max(sub_pkgrel)+1
             printf '(SELECT 1+MAX(`binary_packages`.`sub_pkgrel`) FROM `binary_packages`'
-            printf ' JOIN `architectures` ON `binary_packages`.`architecture`=`architectures`.`id`'
+            mysql_join_binary_packages_architectures
             printf ' WHERE'
             printf ' `binary_packages`.`%s`=from_base64("%s") AND' \
               'epoch' "${epoch}" \
@@ -496,11 +489,9 @@ mysql_generate_package_metadata() {
           printf ' `%s` JOIN' \
             'repositories' \
             'architectures' \
-            'build_assignments' \
-            'package_sources'
-          printf ' ON `build_assignments`.`package_source` = `package_sources`.`id`'
-          printf ' JOIN `upstream_repositories`'
-          printf ' ON `package_sources`.`upstream_package_repository` = `upstream_repositories`.`id`'
+            'build_assignments'
+          mysql_join_build_assignments_package_sources
+          mysql_join_package_sources_upstream_repositories
           printf ' WHERE'
           printf ' `%s`.`%s` = from_base64("%s") AND' \
             'repositories' 'name' "${current_repository}" \
@@ -513,7 +504,6 @@ mysql_generate_package_metadata() {
         } | \
           sed '
             s|,)|)|g
-            s|JOIN ON|ON|g
             s| AND;$|;|
           ' >> \
           "${temp_dir}/add-binary-packages-command"
@@ -586,8 +576,7 @@ mysql_generate_package_metadata() {
               ;;
             esac
             printf ' `binary_packages`'
-            printf ' JOIN `architectures`'
-            printf ' ON `binary_packages`.`architecture` = `architectures`.`id`'
+            mysql_join_binary_packages_architectures
             printf ' JOIN `install_targets`'
             printf ' JOIN `%s`' "${link}"
             printf ' ON `%s`.`name` = `install_targets`.`name`' "${link}"
@@ -649,9 +638,9 @@ mysql_sanity_check() {
   {
     printf 'SELECT CONCAT("\\"any\\" build-assignment building \\"",`bp_arch`.`name`,"\\" binary package: ",`binary_packages`.`pkgname`)'
     printf ' FROM `binary_packages`'
-    printf ' JOIN `build_assignments` ON `binary_packages`.`build_assignment`=`build_assignments`.`id`'
-    printf ' JOIN `architectures` as `bp_arch` ON `binary_packages`.`architecture`=`bp_arch`.`id`'
-    printf ' JOIN `architectures` as `ba_arch` ON `build_assignments`.`architecture`=`ba_arch`.`id`'
+    mysql_join_binary_packages_build_assignments
+    mysql_join_binary_packages_architectures '' 'bp_arch'
+    mysql_join_build_assignments_architectures '' 'ba_arch'
     printf ' WHERE `bp_arch`.`name`!="any"'
     printf ' AND `ba_arch`.`name`="any";\n'
     printf 'SELECT DISTINCT CONCAT("package multiple times on build list: ",`a`.`pkgname`)'
@@ -660,14 +649,13 @@ mysql_sanity_check() {
     printf ' ON `a`.`pkgname`=`b`.`pkgname`'
     printf ' AND `a`.`repository`=`b`.`repository`'
     printf ' AND `a`.`id`!=`b`.`id`'
-    printf ' JOIN `repositories`'
-    printf ' ON `a`.`repository`=`repositories`.`id`'
+    mysql_join_binary_packages_repositories 'a'
     printf ' WHERE `repositories`.`name`="build-list";\n'
     printf 'SELECT DISTINCT CONCAT("\\"split-package with differing sub_pkgrels on the build-list: ",`a`.`pkgname`)'
     printf ' FROM `binary_packages` AS `a`'
     printf ' JOIN `binary_packages` AS `b` ON `a`.`build_assignment`=`b`.`build_assignment`'
-    printf ' JOIN `repositories` AS `arep` ON `a`.`repository`=`arep`.`id`'
-    printf ' JOIN `repositories` AS `brep` ON `b`.`repository`=`brep`.`id`'
+    mysql_join_binary_packages_repositories 'a' 'arep'
+    mysql_join_binary_packages_repositories 'b' 'brep'
     printf ' WHERE `a`.`sub_pkgrel`!=`b`.`sub_pkgrel`'
     printf ' AND `%srep`.`name`="build-list"' \
       'a' 'b'
@@ -700,8 +688,8 @@ mysql_sanity_check() {
     {
       printf 'SELECT `repositories`.`name`,`pkgname`,`epoch`,`pkgver`,`pkgrel`,`sub_pkgrel`,`architectures`.`name`'
       printf ' FROM `binary_packages`'
-      printf ' JOIN `architectures` ON `binary_packages`.`architecture`=`architectures`.`id`'
-      printf ' JOIN `repositories` ON `binary_packages`.`repository`=`repositories`.`id`'
+      mysql_join_binary_packages_architectures
+      mysql_join_binary_packages_repositories
       printf ' WHERE `repositories`.`is_on_master_mirror`'
     } | \
       ${mysql_command} --batch | \
@@ -728,12 +716,11 @@ mysql_find_build_assignment_loops() {
     {
       printf 'SELECT DISTINCT `packages_dependency`.`build_assignment`,`packages_dependent`.`build_assignment`'
       printf ' FROM `dependencies`'
-      printf ' JOIN `install_target_providers` ON `dependencies`.`depending_on`=`install_target_providers`.`install_target`'
-      printf ' JOIN `binary_packages` `packages_dependent` ON `dependencies`.`dependent`=`packages_dependent`.`id`'
-      printf ' JOIN `binary_packages` `packages_dependency` ON `install_target_providers`.`package`=`packages_dependency`.`id`'
-      printf ' JOIN `repositories` `repositories_%s` ON `packages_%s`.`repository`=`repositories_%s`.`id`' \
-        'dependent' 'dependent' 'dependent' \
-        'dependency' 'dependency' 'dependency'
+      mysql_join_dependencies_install_target_providers
+      mysql_join_install_target_providers_binary_packages '' 'packages_dependency'
+      mysql_join_dependencies_binary_packages '' 'packages_dependent'
+      mysql_join_binary_packages_repositories 'packages_dependency' 'repositories_dependency'
+      mysql_join_binary_packages_repositories 'packages_dependent' 'repositories_dependent'
       printf ' WHERE `repositories_dependent`.`name`="build-list" AND `repositories_dependency`.`name`="build-list"'
     } | \
       ${mysql_command} --raw --batch | \
@@ -836,8 +823,8 @@ mysql_cleanup() {
       printf 'WHERE `build_assignments`.`package_source`=`package_sources`.`id`'
     printf ');\n'
     printf 'UPDATE `build_slaves`'
-    printf ' JOIN `binary_packages` ON `build_slaves`.`currently_building`=`binary_packages`.`build_assignment`'
-    printf ' JOIN `repositories` ON `repositories`.`id`=`binary_packages`.`repository`'
+    mysql_join_build_slaves_binary_packages
+    mysql_join_binary_packages_repositories
     printf ' SET `currently_building`=NULL'
     printf ' WHERE `repositories`.`name`!="build-list";\n'
   } | \
@@ -849,12 +836,11 @@ mysql_cleanup() {
 mysql_query_has_pending_dependencies() {
   printf 'EXISTS ('
     printf 'SELECT * FROM `binary_packages` as `to_dos`'
-    printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`%s`' \
-      'repositories` AS `to_do_repos' 'to_do_repos'  'id'           'to_dos'                   'repository' \
-      'dependencies'                  'to_dos'       'id'           'dependencies'             'dependent' \
-      'install_target_providers'      'dependencies' 'depending_on' 'install_target_providers' 'install_target' \
-      'binary_packages` AS `bin_deps' 'bin_deps'     'id'           'install_target_providers' 'package' \
-      'repositories` AS `dep_repos'   'dep_repos'    'id'           'bin_deps'                 'repository'
+    mysql_join_binary_packages_repositories 'to_dos' 'to_do_repos'
+    mysql_join_binary_packages_dependencies 'to_dos'
+    mysql_join_dependencies_install_target_providers
+    mysql_join_install_target_providers_binary_packages '' 'bin_deps'
+    mysql_join_binary_packages_repositories 'bin_deps' 'dep_repos'
     printf ' WHERE'
     printf ' `%s`.`name`="build-list" AND' \
       'dep_repos' 'to_do_repos'
@@ -884,9 +870,8 @@ mysql_query_select_pkgbase_and_revision() {
     'mod_git_revision'
   printf '`upstream_repositories`.`name`'
   printf ' FROM `build_assignments`'
-  printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-    'package_sources' 'package_sources' 'build_assignments' 'package_source' \
-    'upstream_repositories' 'upstream_repositories' 'package_sources' 'upstream_package_repository'
+  mysql_join_build_assignments_package_sources
+  mysql_join_package_sources_upstream_repositories
 }
 
 # mysql_repair_binary_packages_without_build_assignment
@@ -900,7 +885,7 @@ mysql_repair_binary_packages_without_build_assignment() {
       '`binary_packages`.`pkgname`' \
       '`architectures`.`name`'
     printf ' FROM `binary_packages`'
-    printf ' JOIN `architectures` ON `binary_packages`.`architecture`=`architectures`.`id`'
+    mysql_join_binary_packages_architectures
     printf ' WHERE `binary_packages`.`build_assignment`<0'
   } | \
     ${mysql_command} --raw --batch | \
@@ -954,7 +939,7 @@ mysql_repair_binary_packages_without_build_assignment() {
       printf ' LIMIT 1;\n'
       printf 'UPDATE `binary_packages`'
       printf ' JOIN `build_assignments`'
-      printf ' JOIN `package_sources` ON `build_assignments`.`package_source`=`package_sources`.`id`'
+      mysql_join_binary_packages_build_assignments
       printf ' SET `binary_packages`.`build_assignment`=`build_assignments`.`id`'
       printf ' WHERE `binary_packages`.`id`=%s' "${id}"
       printf ' AND `package_sources`.`pkgbase`=from_base64("%s");\n' "${pkgbase}"
@@ -980,9 +965,8 @@ mysql_remove_duplicate_build_order() {
       'pkgver' 'pkgver' \
       'pkgrel' 'pkgrel'
     printf ' `old`.`sub_pkgrel`+1=`new`.`sub_pkgrel`'
-    printf ' JOIN `repositories` AS `%s` ON `%s`.`id`=`%s`.`repository`' \
-      'orep' 'orep' 'old' \
-      'nrep' 'nrep' 'new'
+    mysql_join_binary_packages_repositories 'old' 'orep'
+    mysql_join_binary_packages_repositories 'new' 'nrep'
     printf ' WHERE `orep`.`name`!="to-be-decided"'
     printf ' AND `nrep`.`name`="to-be-decided";\n'
     printf 'UPDATE IGNORE `dependencies`'
@@ -1004,6 +988,9 @@ mysql_remove_duplicate_build_order() {
     ${mysql_command}
 }
 
+# mysql_package_name_query
+# print a mysql query of the full name of a package file
+
 mysql_package_name_query() {
   printf 'CONCAT('
   printf '`binary_packages`.`pkgname`,"-",'
@@ -1014,3 +1001,82 @@ mysql_package_name_query() {
   printf '`architectures`.`name`,".pkg.tar.xz"'
   printf ')'
 }
+
+# mysql_join_*_*
+# print 'JOIN' part of mysql query to connect the respective tables
+# these functions take 2 optional arguments, acting as aliases for
+# the tables
+
+# mysql_join__generic $table_a $column_a $table_b $column_b
+# create mysql_join_${table_a}_${table_b}() function
+
+mysql_join__generic() {
+  eval "$(
+    printf 'mysql_join_%s_%s() {\n' "$1" "$3"
+    printf '  printf '"'"' JOIN `%s`'"'"'\n' "$3"
+    printf '  if [ -n "$2" ]; then\n'
+    printf '    printf '"'"' AS `%%s`'"'"' "$2"\n'
+    printf '  fi\n'
+    printf '  if [ -n "$1" ]; then\n'
+    printf '    printf '"'"' ON `%%s`.`%s`='"'"' "$1"\n' "$2"
+    printf '  else\n'
+    printf '    printf '"'"' ON `%s`.`%s`='"'"'\n' "$1" "$2"
+    printf '  fi\n'
+    printf '  if [ -n "$2" ]; then\n'
+    printf '    printf '"'"'`%%s`.`%s`'"'"' "$2"\n' "$4"
+    printf '  else\n'
+    printf '    printf '"'"'`%s`.`%s`'"'"'\n' "$3" "$4"
+    printf '  fi\n'
+    printf '}\n'
+  )"
+}
+
+for link in \
+  'binary_packages:architecture:architectures' \
+  'binary_packages:repository:repositories' \
+  'binary_packages:build_assignment:build_assignments' \
+ \
+  'build_assignments:architecture:architectures' \
+  'build_assignments:package_source:package_sources' \
+ \
+  'build_dependency_loops:build_assignment:build_assignments' \
+  'build_dependency_loops:build_assignment build_assignment:binary_packages' \
+ \
+  'build_slaves:currently_building:build_assignments' \
+  'build_slaves:currently_building build_assignment:binary_packages' \
+ \
+  'dependencies:depending_on:install_targets' \
+  'dependencies:dependent:binary_packages' \
+  'dependencies:dependency_type:dependency_types' \
+ \
+  'failed_builds:reason:fail_reason' \
+  'failed_builds:build_assignment:build_assignments' \
+  'failed_builds:build_slave:build_slaves' \
+ \
+  'install_target_providers:package:binary_packages' \
+  'install_target_providers:install_target:install_targets' \
+  'install_target_providers:install_target depending_on:dependencies' \
+ \
+  'package_sources:upstream_package_repository:upstream_repositories' \
+ \
+  'repositories:stability:repository_stabilities' \
+ \
+  'upstream_repositories:git_repository:git_repositories'; do
+# A join for these cannot be done, because it's not clear on what to join:
+#  'repository_stability_relations:more_stable:repository_stabilities'
+#  'repository_stability_relations:less_stable:repository_stabilities'
+
+  table_b="${link##*:}"
+  table_a="${link%:*}"
+  column_b="${table_a##*:}"
+  table_a="${table_a%:*}"
+  column_a="${column_b% *}"
+  if [ "${column_a}" = "${column_b}" ]; then
+    column_b='id'
+  else
+    column_b="${column_b##* }"
+  fi
+
+  mysql_join__generic "${table_a}" "${column_a}" "${table_b}" "${column_b}"
+  mysql_join__generic "${table_b}" "${column_b}" "${table_a}" "${column_a}"
+done
diff --git a/bin/prioritize-build-list b/bin/prioritize-build-list
index d611e79..e8dcf0a 100755
--- a/bin/prioritize-build-list
+++ b/bin/prioritize-build-list
@@ -32,10 +32,9 @@ fi
 # shellcheck disable=SC2016
 {
   printf 'UPDATE `build_assignments`'
-  printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
-    'package_sources' 'build_assignments' 'package_source'   'package_sources' \
-    'binary_packages' 'binary_packages'   'build_assignment' 'build_assignments' \
-    'repositories'    'binary_packages'   'repository'       'repositories'
+  mysql_join_build_assignments_package_sources
+  mysql_join_build_assignments_binary_packages
+  mysql_join_binary_packages_repositories
   printf ' SET `build_assignments`.`priority`=('
     printf 'SELECT COALESCE(MAX(`all_priorities`.`priority`),0)+1'
     printf ' FROM ('
diff --git a/bin/return-assignment b/bin/return-assignment
index 7b2dec3..995eae4 100755
--- a/bin/return-assignment
+++ b/bin/return-assignment
@@ -51,12 +51,11 @@ if [ "$5" = 'ERROR' ]; then
   infos=$(
     {
       printf 'SELECT DISTINCT `build_assignments`.`id`,IF(`build_assignments`.`is_broken`,"true","false") FROM `build_slaves`'
-      printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-        'build_assignments'     'build_assignments'     'build_slaves'      'currently_building' \
-        'package_sources'       'package_sources'       'build_assignments' 'package_source' \
-        'upstream_repositories' 'upstream_repositories' 'package_sources'   'upstream_package_repository' \
-        'binary_packages'       'build_assignments'     'binary_packages'   'build_assignment' \
-        'repositories'          'repositories'          'binary_packages'   'repository'
+      mysql_join_build_assignments_build_slaves
+      mysql_join_build_assignments_package_sources
+      mysql_join_package_sources_upstream_repositories
+      mysql_join_build_assignments_binary_packages
+      mysql_join_binary_packages_repositories
       printf ' WHERE `build_slaves`.`name`=from_base64("%s")' \
         "$(
           # shellcheck disable=SC2154
@@ -157,7 +156,7 @@ if [ "$5" = 'ERROR' ]; then
       printf 'DROP TABLE `failures`;\n'
     fi
     printf 'UPDATE `build_assignments`'
-    printf ' JOIN `build_slaves` ON `build_slaves`.`currently_building`=`build_assignments`.`id`'
+    mysql_join_build_assignments_build_slaves
     printf ' SET `build_assignments`.`is_broken`=1, `build_slaves`.`currently_building`=NULL'
     printf ' WHERE `build_slaves`.`name`=from_base64("%s");\n' \
       "$(
@@ -267,12 +266,11 @@ clean_up_lock_file() {
 if ! {
     printf 'SELECT count(*)'
     printf ' FROM `binary_packages`'
-    printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-      'build_assignments'     'build_assignments'     'binary_packages'   'build_assignment' \
-      'build_slaves'          'build_assignments'     'build_slaves'      'currently_building' \
-      'repositories'          'repositories'          'binary_packages'   'repository' \
-      'package_sources'       'package_sources'       'build_assignments' 'package_source' \
-      'upstream_repositories' 'upstream_repositories' 'package_sources'   'upstream_package_repository'
+    mysql_join_binary_packages_build_assignments
+    mysql_join_build_assignments_build_slaves
+    mysql_join_binary_packages_repositories
+    mysql_join_build_assignments_package_sources
+    mysql_join_package_sources_upstream_repositories
     printf ' WHERE `repositories`.`name`="build-list"'
     printf ' AND `build_slaves`.`name`=from_base64("%s")' \
       "$(printf '%s' "${slave}" | base64 -w0)"
@@ -396,9 +394,9 @@ package_errors=$(
         mysql_package_name_query
       printf ')'
       printf ' FROM `binary_packages`'
-      printf ' JOIN `architectures` ON `architectures`.`id`=`binary_packages`.`architecture`'
-      printf ' JOIN `build_slaves` ON `build_slaves`.`currently_building`=`binary_packages`.`build_assignment`'
-      printf ' JOIN `repositories` ON `binary_packages`.`repository`=`repositories`.`id`'
+      mysql_join_binary_packages_architectures
+      mysql_join_binary_packages_build_slaves
+      mysql_join_binary_packages_repositories
       printf ' WHERE `build_slaves`.`name`=from_base64("%s")' \
         "$(
           printf '%s' "${slave}" | \
@@ -476,9 +474,9 @@ fi
 # shellcheck disable=SC2016
 {
   printf 'UPDATE `build_assignments`'
-  printf ' JOIN `build_slaves` ON `build_slaves`.`currently_building`=`build_assignments`.`id`'
-  printf ' JOIN `binary_packages` ON `binary_packages`.`build_assignment`=`build_assignments`.`id`'
-  printf ' JOIN `repositories` AS `old_repo` ON `binary_packages`.`repository`=`old_repo`.`id`'
+  mysql_join_build_assignments_build_slaves
+  mysql_join_build_assignments_binary_packages
+  mysql_join_binary_packages_repositories '' 'old_repo'
   printf ' SET'
   printf ' `build_assignments`.`is_broken`=0,'
   printf ' `build_assignments`.`priority`=0,'
@@ -507,8 +505,8 @@ fi
   printf 'CREATE TEMPORARY TABLE `loops_to_delete` (`loop` MEDIUMINT);\n'
   printf 'INSERT INTO `loops_to_delete`'
   printf ' SELECT `build_dependency_loops`.`loop` FROM `build_dependency_loops`'
-  printf ' JOIN `binary_packages` ON `binary_packages`.`build_assignment`=`build_dependency_loops`.`build_assignment`'
-  printf ' JOIN `repositories` ON `binary_packages`.`repository`=`repositories`.`id`'
+  mysql_join_build_dependency_loops_binary_packages
+  mysql_join_binary_packages_repositories
   printf ' WHERE NOT `repositories`.`name` = "build-list";\n'
   printf 'DELETE FROM `build_dependency_loops` WHERE EXISTS ('
   printf 'SELECT * FROM `loops_to_delete` WHERE `loops_to_delete`.`loop`=`build_dependency_loops`.`loop`'
diff --git a/bin/seed-build-list b/bin/seed-build-list
index bfe975e..5ce9037 100755
--- a/bin/seed-build-list
+++ b/bin/seed-build-list
@@ -123,7 +123,7 @@ if [ -s "${tmp_dir}/mirrors" ]; then
       printf 'SELECT '
       mysql_package_name_query
       printf ' FROM `binary_packages`'
-      printf ' JOIN `architectures` ON `binary_packages`.`architecture`=`architectures`.`id`'
+      mysql_join_binary_packages_architectures
     } | \
       ${mysql_command} --raw --batch | \
       sed '
@@ -161,7 +161,7 @@ fi
   printf 'INSERT IGNORE INTO `ignore_packages` (`pkgname`)'
   printf ' SELECT DISTINCT `binary_packages`.`pkgname`'
   printf ' FROM `binary_packages`'
-  printf ' JOIN `repositories` ON `binary_packages`.`repository`=`repositories`.`id`'
+  mysql_join_binary_packages_repositories
   printf ' WHERE `repositories`.`name` IN ("build-list","deletion-list");\n'
 } | \
   sponge "${tmp_dir}/ignore-packages"
@@ -253,7 +253,7 @@ printf 'CREATE TEMPORARY TABLE `pkgbases` (`pkgbase` VARCHAR(64), `repository` V
   printf '`pkgbases`.`repository`'
   printf ' FROM `pkgbases`'
   printf ' JOIN `upstream_repositories` ON `upstream_repositories`.`name`=`pkgbases`.`repository`'
-  printf ' JOIN `git_repositories` ON `git_repositories`.`id`=`upstream_repositories`.`git_repository`'
+  mysql_join_upstream_repositories_git_repositories
   printf ';\n'
   printf 'SELECT '
   printf '`package_sources`.`pkgbase`,'
@@ -264,11 +264,10 @@ printf 'CREATE TEMPORARY TABLE `pkgbases` (`pkgbase` VARCHAR(64), `repository` V
   printf '),'
   printf '`upstream_repositories`.`name`'
   printf ' FROM `binary_packages`'
-  printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-    'build_assignments'     'build_assignments'     'binary_packages'       'build_assignment' \
-    'package_sources'       'package_sources'       'build_assignments'     'package_source' \
-    'upstream_repositories' 'upstream_repositories' 'package_sources'       'upstream_package_repository' \
-    'git_repositories'      'git_repositories'      'upstream_repositories' 'git_repository'
+  mysql_join_binary_packages_build_assignments
+  mysql_join_build_assignments_package_sources
+  mysql_join_package_sources_upstream_repositories
+  mysql_join_upstream_repositories_git_repositories
   printf ' WHERE ('
   if [ -s "${tmp_dir}/package-regexes" ]; then
     grep -vxF '' "${tmp_dir}/package-regexes" | \
diff --git a/bin/show-dependencies b/bin/show-dependencies
index 0a7a1fd..bb1ae37 100755
--- a/bin/show-dependencies
+++ b/bin/show-dependencies
@@ -13,7 +13,7 @@ if [ $# -eq 0 ]; then
     {
       printf 'SELECT `package_sources`.`pkgbase`'
       printf ' FROM `package_sources`'
-      printf ' JOIN `build_assignments` ON `build_assignments`.`package_source`=`package_sources`.`id`'
+      mysql_join_package_sources_build_assignments
       printf ' WHERE `build_assignments`.`is_broken`'
     } | \
       ${mysql_command} --raw --batch | \
@@ -28,9 +28,8 @@ if [ $# -eq 0 ]; then
   {
     printf 'SELECT DISTINCT `package_sources`.`pkgbase`,`binary_packages`.`pkgname`'
     printf ' FROM `binary_packages`'
-    printf ' JOIN `%s` ON `%s`.`id`=`%s`.`%s`' \
-      'build_assignments' 'build_assignments' 'binary_packages'   'build_assignment' \
-      'package_sources'   'package_sources'   'build_assignments' 'package_source'
+    mysql_join_binary_packages_build_assignments
+    mysql_join_build_assignments_package_sources
     printf ' ORDER BY `binary_packages`.`pkgname`'
     # Why can't mysql order in the same way "sort" does!
   } | \
diff --git a/bin/why-dont-you b/bin/why-dont-you
index e9922b7..d95f2a7 100755
--- a/bin/why-dont-you
+++ b/bin/why-dont-you
@@ -57,10 +57,9 @@ case "${action}" in
         # package_source, build_assignment, binary_package, repostory
         printf 'SELECT DISTINCT `tb_ps`.`pkgbase`,`tb_bin`.`id` AS `bin_id`,`tb_ba`.`id` AS `ba_id`,`tb_ba`.`is_blocked`,`tb_ba`.`is_broken`'
         printf ' FROM `package_sources` AS `tb_ps`'
-        printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
-          'build_assignments` AS `tb_ba' 'tb_ba'  'package_source'   'tb_ps' \
-          'binary_packages` AS `tb_bin'  'tb_bin' 'build_assignment' 'tb_ba' \
-          'repositories` AS `tb_rep'     'tb_bin' 'repository'       'tb_rep'
+        mysql_join_package_sources_build_assignments 'tb_ps' 'tb_ba'
+        mysql_join_build_assignments_binary_packages 'tb_ba' 'tb_bin'
+        mysql_join_binary_packages_repositories 'tb_bin' 'tb_rep'
         printf ' WHERE `tb_rep`.`name`="build-list"'
         printf ') AS `to_build`'
       printf ' ON `to_build`.`pkgbase`=`pkgbases`.`pkgbase`'
@@ -70,16 +69,14 @@ case "${action}" in
         # potential dependencies
         printf 'SELECT DISTINCT `dep_ps`.`pkgbase`,`dependencies`.`dependent`'
         printf ' FROM `package_sources` AS `dep_ps`'
-        printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
-          'build_assignments` AS `dep_ba' 'dep_ba'                   'package_source'   'dep_ps' \
-          'binary_packages` AS `dep_bin'  'dep_bin'                  'build_assignment' 'dep_ba' \
-          'repositories` AS `dep_rep'     'dep_bin'                  'repository'       'dep_rep' \
-          'install_target_providers'      'install_target_providers' 'package'          'dep_bin'
-        # starting from the line above, we have some additional joins,
+        mysql_join_package_sources_build_assignments 'dep_ps' 'dep_ba'
+        mysql_join_build_assignments_binary_packages 'dep_ba' 'dep_bin'
+        mysql_join_binary_packages_repositories 'dep_bin' 'dep_rep'
+        # now we have some (=3) additional joins,
         # because we are interested in dependency relations to `to_build`
-        printf ' JOIN `dependencies` ON `install_target_providers`.`install_target`=`dependencies`.`depending_on`'
-        printf ' JOIN `%s` ON `%s`.`%s`=`%s`.`id`' \
-          'dependency_types' 'dependencies' 'dependency_type' 'dependency_types'
+        mysql_join_binary_packages_install_target_providers 'dep_bin'
+        mysql_join_install_target_providers_dependencies
+        mysql_join_dependencies_dependency_types
         printf ' WHERE `dep_rep`.`name`="build-list"'
         printf ' AND `dependency_types`.`relevant_for_building`'
         printf ') AS `deps`'