From 9e8bc411e542a04d6015b5308a51f3362c292beb Mon Sep 17 00:00:00 2001
From: Erich Eckner <git@eckner.net>
Date: Thu, 5 Jul 2018 14:57:58 +0200
Subject: bin/bootstrap-mysql: `find_the_culprit(`bpir`)` new (maybe buggy)

---
 bin/bootstrap-mysql | 250 ++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 250 insertions(+)

diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql
index 81d5c82..98371f5 100755
--- a/bin/bootstrap-mysql
+++ b/bin/bootstrap-mysql
@@ -365,6 +365,256 @@ fi
   done
   printf 'END\n'
   printf '//\n'
+
+  printf 'DROP PROCEDURE IF EXISTS `find_the_culprit`//\n'
+  printf 'CREATE PROCEDURE `find_the_culprit`(IN `bpir` BIGINT)\n'
+  printf 'find_the_culprit:BEGIN\n'
+  printf 'DECLARE row_count_saved INT DEFAULT 0;\n'
+
+  printf 'UPDATE `knots`'
+  printf ' SET `knots`.`reason_length`=NULL,'
+  printf ' `knots`.`relevant`=NULL,'
+  printf ' `knots`.`active`=1;\n'
+  printf 'DELETE FROM `edges_copy`;\n'
+  printf 'INSERT INTO `edges_copy` (`cause`,`impact`,`invert`)'
+  printf ' SELECT `edges`.`cause`,'
+  printf '`edges`.`impact`,'
+  printf '`edges`.`invert`'
+  printf ' FROM `edges`;\n'
+
+  printf 'REPEAT\n'
+    printf 'UPDATE `knots_copy`'
+    printf ' JOIN `knots`'
+    printf ' ON `knots_copy`.`id`=`knots`.`id`'
+    printf ' SET `knots_copy`.`reason_length`=`knots`.`reason_length`,'
+    printf ' `knots_copy`.`active`=`knots`.`active`;\n'
+    printf 'SET row_count_saved=0;\n'
+    printf '%s\n' \
+      '0 0 1' \
+      '0 1 0' \
+      '1 1 1' \
+      '1 0 0' | \
+      while read -r and left value; do
+        printf 'UPDATE `knots`'
+        if [ "${left}" = '1' ]; then
+          printf ' LEFT'
+        fi
+        printf ' JOIN ('
+          printf 'SELECT `edges`.`impact`,'
+          if [ "${and}" = '1' ]; then
+            printf ' MIN('
+          else
+            printf ' MAX('
+          fi
+            printf 'IF('
+              printf '`knots_copy`.`reason_length` IS NULL,'
+              if [ "${value}" = '1' ]; then
+                printf '0,'
+              else
+                printf '1,'
+              fi
+              printf '`edges`.`invert` XOR `knots_copy`.`active`'
+            printf ')'
+          printf ') AS `active`,'
+          if [ "${left}" = '0' ]; then
+            printf ' MIN('
+          else
+            printf ' MAX('
+          fi
+            printf '`knots_copy`.`reason_length`'
+          printf ') AS `reason_length`'
+          printf ' FROM `edges`'
+          printf ' JOIN `knots_copy`'
+          printf ' ON `knots_copy`.`id`=`edges`.`cause`'
+          printf ' GROUP BY `edges`.`impact`'
+        printf ') AS `edges_combined`'
+        printf ' ON `edges_combined`.`impact`=`knots`.`id`'
+        printf ' SET `knots`.`active`=%s,' \
+          "${value}"
+        printf ' `knots`.`reason_length`='
+        if [ "${left}" = '1' ]; then
+          printf 'IFNULL('
+        fi
+        printf '`edges_combined`.`reason_length`+1'
+        if [ "${left}" = '1' ]; then
+          printf ',0)'
+        fi
+        printf ' WHERE'
+        if [ "${and}" = '0' ]; then
+          printf ' NOT'
+        fi
+        printf ' `knots`.`and`'
+        printf ' AND '
+        if [ "${left}" = '1' ]; then
+          printf 'IFNULL('
+        fi
+        printf '`edges_combined`.`active`'
+        if [ "${left}" = '1' ]; then
+          printf ',%s)' \
+            "${value}"
+        fi
+        printf '=%s;\n' \
+          "${value}"
+        printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n'
+      done
+  printf 'UNTIL (row_count_saved=0) OR EXISTS ('
+    printf 'SELECT 1 FROM `knots`'
+    printf ' WHERE `knots`.`reason_length` IS NOT NULL'
+    printf ' AND `knots`.`content_id`=`bpir`'
+    printf ' AND `knots`.`content_type`="bpir"'
+    printf ')\n'
+  printf 'END REPEAT;\n'
+
+  printf 'UPDATE `knots_copy`'
+  printf ' JOIN `knots`'
+  printf ' ON `knots_copy`.`id`=`knots`.`id`'
+  printf ' SET `knots_copy`.`reason_length`=`knots`.`reason_length`,'
+  printf ' `knots_copy`.`active`=`knots`.`active`;\n'
+
+  printf 'IF NOT EXISTS ('
+    printf 'SELECT 1 FROM `knots`'
+    printf ' WHERE `knots`.`reason_length` IS NOT NULL'
+    printf ' AND `knots`.`content_id`=`bpir`'
+    printf ' AND `knots`.`content_type`="bpir"'
+  printf ') THEN\n'
+    printf 'SELECT CONCAT('
+      printf '"I cannot decide wether ",'
+      printf '`binary_packages`.`pkgname`,'
+      printf ' " can be moved or not."'
+    printf ') FROM `binary_packages`'
+    mysql_join_binary_packages_binary_packages_in_repositories
+    printf ' WHERE `binary_packages_in_repositories`.`id`=`bpir`;\n'
+    printf 'LEAVE find_the_culprit;\n'
+  printf 'END IF;\n'
+
+  printf 'IF ('
+    printf 'SELECT `knots`.`active` FROM `knots`'
+    printf ' WHERE `knots`.`reason_length` IS NOT NULL'
+    printf ' AND `knots`.`content_id`=`bpir`'
+    printf ' AND `knots`.`content_type`="bpir"'
+  printf ') THEN\n'
+    printf 'SELECT CONCAT('
+      printf '`binary_packages`.`pkgname`,'
+      printf ' " can be moved."'
+    printf ') FROM `binary_packages`'
+    mysql_join_binary_packages_binary_packages_in_repositories
+    printf ' WHERE `binary_packages_in_repositories`.`id`=`bpir`;\n'
+    printf 'LEAVE find_the_culprit;\n'
+  printf 'END IF;\n'
+
+  printf 'SELECT CONCAT('
+    printf '`binary_packages`.`pkgname`,'
+    printf ' " cannot be moved:"'
+  printf ') FROM `binary_packages`'
+  mysql_join_binary_packages_binary_packages_in_repositories
+  printf ' WHERE `binary_packages_in_repositories`.`id`=`bpir`;\n'
+
+  # now we mark all relevant knots
+  printf 'UPDATE `knots`'
+  printf ' SET `knots`.`relevant`=('
+    printf '`knots`.`reason_length` IS NOT NULL'
+    printf ' AND `knots`.`content_id`=`bpir`'
+    printf ' AND `knots`.`content_type`="bpir"'
+  printf ');\n'
+
+  printf 'REPEAT\n'
+    printf 'SET row_count_saved=0;\n'
+
+    printf 'UPDATE `knots_copy`'
+    printf ' JOIN `knots`'
+    printf ' ON `knots_copy`.`id`=`knots`.`id`'
+    printf ' SET `knots_copy`.`relevant`=`knots`.`relevant`;\n'
+
+    printf 'UPDATE `knots`'
+    printf ' JOIN `edges`'
+    printf ' ON `knots`.`id`=`edges`.`cause`'
+    printf ' JOIN `knots_copy`'
+    printf ' ON `knots_copy`.`id`=`edges`.`impact`'
+    printf ' SET `knots`.`relevant`=1'
+    printf ' WHERE `knots_copy`.`relevant`'
+    printf ' AND NOT `knots`.`relevant`'
+    printf ' AND NOT ('
+      printf '`edges`.`invert`'
+      printf ' XOR `knots`.`active`'
+      printf ' XOR `knots_copy`.`active`'
+    printf ') AND ('
+      printf '`knots_copy`.`and`=`knots_copy`.`active`'
+      printf ' OR `knots_copy`.`reason_length`=`knots`.`reason_length`+1'
+    printf ');\n'
+
+    printf 'SET row_count_saved = row_count_saved + ROW_COUNT();\n'
+  printf 'UNTIL row_count_saved=0\n'
+  printf 'END REPEAT;\n'
+
+  for copy in '' '_copy'; do
+    printf 'CREATE TEMPORARY TABLE `knot_names%s` (' \
+      "${copy}"
+    printf '`id` BIGINT NOT NULL,'
+    printf ' `name` VARCHAR (128),'
+    printf ' `and` BIT,'
+    printf ' `active` BIT,'
+    printf ' `relevant` BIT,'
+    printf ' `reason_length` MEDIUMINT,'
+    printf ' UNIQUE KEY (`id`)'
+    printf ');\n'
+  done
+
+  printf 'INSERT INTO `knot_names` (`id`,`name`,`reason_length`,`and`,`relevant`,`active`)'
+  printf ' SELECT `knots`.`id`,'
+  printf ' `install_targets`.`name`,'
+  printf ' `knots`.`reason_length`,'
+  printf ' `knots`.`and`,'
+  printf ' `knots`.`relevant`,'
+  printf ' `knots`.`active`'
+  printf ' FROM `knots`'
+  printf ' JOIN `install_targets`'
+  printf ' ON `knots`.`content_id`=`install_targets`.`id`'
+  printf ' AND `knots`.`content_type`="it";\n'
+
+  printf 'INSERT INTO `knot_names` (`id`,`name`,`reason_length`,`and`,`relevant`,`active`)'
+  printf ' SELECT `knots`.`id`,'
+  printf ' CONCAT('
+    printf '`r_a`.`name`,"/",'
+    printf '`repositories`.`name`,"/",'
+    mysql_package_name_query
+  printf ') AS `name`,'
+  printf ' `knots`.`reason_length`,'
+  printf ' `knots`.`and`,'
+  printf ' `knots`.`relevant`,'
+  printf ' `knots`.`active`'
+  printf ' FROM `knots`'
+  printf ' JOIN `binary_packages_in_repositories`'
+  printf ' ON `knots`.`content_id`=`binary_packages_in_repositories`.`id`'
+  printf ' AND `knots`.`content_type`="bpir"'
+  mysql_join_binary_packages_in_repositories_binary_packages
+  mysql_join_binary_packages_in_repositories_repositories
+  mysql_join_repositories_architectures '' 'r_a'
+  mysql_join_binary_packages_architectures
+  printf ';\n'
+
+  printf 'INSERT INTO `knot_names_copy`'
+  printf ' SELECT * FROM `knot_names`;\n'
+
+  printf 'SELECT CONCAT('
+    printf 'IF(`impact`.`and`,"(and) ","(or) "),'
+    printf '`impact`.`name`,'
+    printf '" ",IF(`impact`.`active`,"1","0"),'
+    printf 'IF(`edges`.`invert`," -NOT-> "," --> "),'
+    printf '`cause`.`name`,'
+    printf '" ",IF(`cause`.`active`,"1","0")'
+  printf ') FROM `edges`'
+  printf ' JOIN `knot_names` AS `cause`'
+  printf ' ON `cause`.`id`=`edges`.`cause`'
+  printf ' JOIN `knot_names_copy` AS `impact`'
+  printf ' ON `impact`.`id`=`edges`.`impact`'
+  printf ' WHERE `cause`.`relevant`'
+  printf ' AND `impact`.`relevant`;\n'
+
+  printf 'DROP TEMPORARY TABLE `knot_names`;\n'
+
+  printf 'END\n'
+  printf '//\n'
+
   printf 'DELIMITER ;\n'
 
   printf 'GRANT %s ON %s TO '"'"'buildmaster'"'"'@'"'"'localhost'"'"';\n' \
-- 
cgit v1.2.3