remove `files` and `file_providers` - they're not practicable in the database

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 @@
         </column-list>
       </table>
     </item>
-    <item type="database-table" id="{457d2e44-2570-45c8-b93e-7b335efd0483}">
-      <position>
-        <x>796</x>
-        <y>131</y>
-      </position>
-      <table>
-        <name>file_providers</name>
-        <color>#ffffff</color>
-        <column-list>
-          <column>
-            <name>id</name>
-            <data-type>BIGINT</data-type>
-            <required>True</required>
-            <primary-key>True</primary-key>
-          </column>
-          <column>
-            <name>package</name>
-            <data-type>BIGINT</data-type>
-            <required>True</required>
-            <primary-key>False</primary-key>
-          </column>
-          <column>
-            <name>file</name>
-            <data-type>BIGINT</data-type>
-            <required>True</required>
-            <primary-key>False</primary-key>
-          </column>
-          <column>
-            <name>file_name_hash</name>
-            <data-type>MEDIUMINT</data-type>
-            <required>True</required>
-            <primary-key>False</primary-key>
-          </column>
-        </column-list>
-      </table>
-    </item>
-    <item type="database-table" id="{f24a3299-870d-4312-8dbe-50b3bf1953d3}">
-      <position>
-        <x>785</x>
-        <y>6</y>
-      </position>
-      <table>
-        <name>files</name>
-        <color>#ffffff</color>
-        <column-list>
-          <column>
-            <name>id</name>
-            <data-type>BIGINT</data-type>
-            <required>True</required>
-            <primary-key>True</primary-key>
-          </column>
-          <column>
-            <name>path</name>
-            <data-type>TEXT</data-type>
-            <required>True</required>
-            <primary-key>False</primary-key>
-          </column>
-          <column>
-            <name>name</name>
-            <data-type>TEXT</data-type>
-            <required>True</required>
-            <primary-key>False</primary-key>
-          </column>
-          <column>
-            <name>name_hash</name>
-            <data-type>MEDIUMINT</data-type>
-            <required>True</required>
-            <primary-key>False</primary-key>
-          </column>
-          <column>
-            <name>absolute_name_hash</name>
-            <data-type>VARCHAR(54)</data-type>
-            <required>True</required>
-            <primary-key>False</primary-key>
-          </column>
-        </column-list>
-      </table>
-    </item>
     <item type="database-table" id="{92f54126-a433-458e-80b7-67235e560334}">
       <position>
         <x>754</x>
@@ -550,8 +472,8 @@
     </item>
     <item type="database-table" id="{f8caa2d5-11ba-485c-b273-8183713896bf}">
       <position>
-        <x>611</x>
-        <y>89</y>
+        <x>669</x>
+        <y>128</y>
       </position>
       <table>
         <name>install_target_providers</name>
@@ -772,8 +694,8 @@
     </item>
     <item type="database-table" id="{e84f8e5f-28e0-4d8b-a7e5-0f80a896f74e}">
       <position>
-        <x>680</x>
-        <y>3</y>
+        <x>721</x>
+        <y>23</y>
       </position>
       <table>
         <name>install_targets</name>
@@ -796,7 +718,7 @@
     </item>
     <item type="database-table" id="{caab4a73-46dc-49dc-ac51-d8ae916827c5}">
       <position>
-        <x>496</x>
+        <x>537</x>
         <y>-6</y>
       </position>
       <table>
@@ -1028,72 +950,6 @@
         </columns>
       </relationship>
     </item>
-    <item type="database-relationship" id="{4a844898-e05d-4919-8c83-cf90f28edf42}">
-      <line>
-        <connector-list>
-          <connector>
-            <position>
-              <x>796</x>
-              <y>169.906</y>
-            </position>
-            <angle>180</angle>
-            <hub owner="{457d2e44-2570-45c8-b93e-7b335efd0483}"/>
-          </connector>
-          <connector>
-            <position>
-              <x>480.719</x>
-              <y>168.288</y>
-            </position>
-            <angle>0</angle>
-            <hub owner="{2e6c3b80-fef6-4d2b-8945-7ea6be1b646c}"/>
-          </connector>
-        </connector-list>
-      </line>
-      <relationship>
-        <cardinality>OneToMany</cardinality>
-        <modality>
-          <child>Mandatory</child>
-          <parent>Mandatory</parent>
-        </modality>
-        <columns>
-          <child>1</child>
-          <parent>0</parent>
-        </columns>
-      </relationship>
-    </item>
-    <item type="database-relationship" id="{e64f4d17-ff78-45f2-ab9b-281bf8fc5fb6}">
-      <line>
-        <connector-list>
-          <connector>
-            <position>
-              <x>864</x>
-              <y>131</y>
-            </position>
-            <angle>90</angle>
-            <hub owner="{457d2e44-2570-45c8-b93e-7b335efd0483}"/>
-          </connector>
-          <connector>
-            <position>
-              <x>871.711</x>
-              <y>97.8125</y>
-            </position>
-            <angle>270</angle>
-            <hub owner="{f24a3299-870d-4312-8dbe-50b3bf1953d3}"/>
-          </connector>
-        </connector-list>
-      </line>
-      <relationship>
-        <cardinality>OneToMany</cardinality>
-        <modality>
-          <child>Optional</child>
-          <parent>Optional</parent>
-        </modality>
-        <columns>
-          <child>2</child>
-          <parent>0</parent>
-        </columns>
-      </relationship>
-    </item>
     <item type="database-relationship" id="{fa899065-104a-466d-bc8c-16589a774a45}">
       <line>
         <connector-list>
@@ -1463,7 +1319,7 @@
           <connector>
             <position>
               <x>480.719</x>
-              <y>209.05</y>
+              <y>198.859</y>
             </position>
             <angle>0</angle>
             <hub owner="{2e6c3b80-fef6-4d2b-8945-7ea6be1b646c}"/>
@@ -1528,8 +1384,8 @@
         <connector-list>
           <connector>
             <position>
-              <x>611</x>
-              <y>120.906</y>
+              <x>669</x>
+              <y>159.906</y>
             </position>
             <angle>180</angle>
             <hub owner="{f8caa2d5-11ba-485c-b273-8183713896bf}"/>
@@ -1537,7 +1393,7 @@
           <connector>
             <position>
               <x>480.719</x>
-              <y>127.525</y>
+              <y>147.906</y>
             </position>
             <angle>0</angle>
             <hub owner="{2e6c3b80-fef6-4d2b-8945-7ea6be1b646c}"/>
@@ -1561,7 +1417,7 @@
         <connector-list>
           <connector>
             <position>
-              <x>571.836</x>
+              <x>612.836</x>
               <y>71.8125</y>
             </position>
             <angle>270</angle>
@@ -1570,7 +1426,7 @@
           <connector>
             <position>
               <x>480.719</x>
-              <y>86.7625</y>
+              <y>96.9531</y>
             </position>
             <angle>0</angle>
             <hub owner="{2e6c3b80-fef6-4d2b-8945-7ea6be1b646c}"/>
@@ -1594,16 +1450,16 @@
         <connector-list>
           <connector>
             <position>
-              <x>689.109</x>
-              <y>89</y>
+              <x>747.109</x>
+              <y>128</y>
             </position>
             <angle>90</angle>
             <hub owner="{f8caa2d5-11ba-485c-b273-8183713896bf}"/>
           </connector>
           <connector>
             <position>
-              <x>730.016</x>
-              <y>52.8125</y>
+              <x>771.016</x>
+              <y>72.8125</y>
             </position>
             <angle>270</angle>
             <hub owner="{e84f8e5f-28e0-4d8b-a7e5-0f80a896f74e}"/>
@@ -1627,7 +1483,7 @@
         <connector-list>
           <connector>
             <position>
-              <x>647.672</x>
+              <x>688.672</x>
               <y>32.9062</y>
             </position>
             <angle>0</angle>
@@ -1635,8 +1491,8 @@
           </connector>
           <connector>
             <position>
-              <x>680</x>
-              <y>27.9062</y>
+              <x>721</x>
+              <y>47.9062</y>
             </position>
             <angle>180</angle>
             <hub owner="{e84f8e5f-28e0-4d8b-a7e5-0f80a896f74e}"/>
@@ -1693,7 +1549,7 @@
         <connector-list>
           <connector>
             <position>
-              <x>496</x>
+              <x>537</x>
               <y>32.9062</y>
             </position>
             <angle>180</angle>