why-dont-you - work in progress

3 files changed, 133 insertions, 168 deletions

diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql
index 267b4d3..b08afab 100755
--- a/bin/bootstrap-mysql
+++ b/bin/bootstrap-mysql
@@ -366,185 +366,132 @@ fi
   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 'DROP PROCEDURE IF EXISTS `recursively_find_the_culprit`//\n'
+  printf 'CREATE PROCEDURE `recursively_find_the_culprit`(IN `knot_id` BIGINT, IN `current_relevance_level` MEDIUMINT)\n'
+  printf 'recursively_find_the_culprit:BEGIN\n'
+  printf 'DECLARE row_count_saved INT DEFAULT 0;\n'
+  printf 'DECLARE next_knot_id INT DEFAULT 0;\n'
 
   printf 'REPEAT\n'
+    printf 'SET row_count_saved=0;\n'
+
+    printf 'UPDATE `knots` AS `c_k`'
+    printf ' JOIN `edges`'
+    printf ' ON `edges`.`cause`=`c_k`.`id`'
+    printf ' JOIN `knots_copy` AS `i_k`'
+    printf ' ON `edges`.`impact`=`i_k`.`id`'
+    printf ' SET `c_k`.`active`=(`i_k`.`active` XOR `edges`.`invert`),'
+    printf '`c_k`.`reason_length`=`i_k`.`reason_length`+1'
+    printf ' WHERE `c_k`.`reason_length` IS NULL'
+    printf ' AND `i_k`.`reason_length` IS NOT NULL'
+    printf ' AND (`i_k`.`and`=`i_k`.`active`);\n'
+
+    printf 'SET row_count_saved=row_count_saved+ROW_COUNT();\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 'SELECT 1 FROM `knots` AS `c_k`'
+    printf ' JOIN `edges`'
+    printf ' ON `edges`.`cause`=`c_k`.`id`'
+    printf ' JOIN `knots_copy` AS `i_k`'
+    printf ' ON `edges`.`impact`=`i_k`.`id`'
+    printf ' WHERE (`c_k`.`active` XOR `i_k`.`active` XOR `edges`.`invert`)'
+    printf ' AND `c_k`.`reason_length` IS NOT NULL'
+    printf ' AND `i_k`.`reason_length` IS NOT NULL'
     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 whether ",'
-      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'
+# TODO: the found EXISTS above triggers all knots in current_relevance_level:
+# - knot_id is proven wrong (to the relevance_level)
+# - all other knots of relevance_level should be reset to unknown
+# (this case might need to go _into_ the above repeat)
+
+# TODO: in the other case, we need to branch further :-/
+
+  printf 'IF row_count_saved=0 THEN'
+    printf 'SET next_knot_id = ('
+      printf 'SELECT `sub_q`.`id`'
+      printf ' FROM ('
+        printf 'SELECT `i_k`.`id`,'
+        printf 'MAX(IF(`c_k`.`reason_length` IS NULL,0,1)) AS `c_rl`'
+        printf ' FROM `knots` AS `c_k`'
+        printf ' JOIN `edges`'
+        printf ' ON `edges`.`cause`=`c_k`.`id`'
+        printf ' JOIN `knots_copy` AS `i_k`'
+        printf ' ON `edges`.`impact`=`i_k`.`id`'
+        printf ' WHERE `i_k`.`relevance_level`=`current_relevance_level`'
+        printf ' AND `i_k`.`reason_length` IS NOT NULL'
+        printf ' AND (`i_k`.`and` XOR `i_k`.`active`)'
+        printf ' GROUP BY `i_k`.`id`'
+      printf ') AS `sub_q`'
+      printf ' WHERE `sub_q`.`c_rl`=0'
+      printf ' LIMIT 1'
+    printf ');\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 '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 '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'
+  printf 'UNTIL (row_count_saved=0) OR EXISTS ('
+    printf 'SELECT 1 FROM `knots` AS `c_k`'
+    printf ' JOIN `edges`'
+    printf ' ON `edges`.`cause`=`c_k`.`id`'
+    printf ' JOIN `knots_copy` AS `i_k`'
+    printf ' ON `edges`.`impact`=`i_k`.`id`'
+    printf ' WHERE (`c_k`.`active` XOR `i_k`.`active` XOR `edges`.`invert`)'
+    printf ' AND `c_k`.`reason_length` IS NOT NULL'
+    printf ' AND `i_k`.`reason_length` IS NOT NULL'
+    printf ')\n'
+  printf 'END REPEAT;\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 'END\n'
+  printf '//\n'
+
+
+  printf 'DROP PROCEDURE IF EXISTS `find_the_culprit`//\n'
+  printf 'CREATE PROCEDURE `find_the_culprit`(IN `knot_id` BIGINT)\n'
+  printf 'find_the_culprit:BEGIN\n'
+  printf 'DECLARE row_count_saved INT DEFAULT 0;\n'
+
+  printf 'CREATE TEMPORARY TABLE `pending_knots` ('
+  printf '`knot_id` BIGINT,'
+  printf '`level` MEDIUMINT,'
+  printf '`group` MEDIUMINT,'
+  printf 'UNIQUE KEY `content` (`knot_id`,`level`,`group`)'
   printf ');\n'
 
-  printf 'REPEAT\n'
-    printf 'SET row_count_saved=0;\n'
+  printf 'UPDATE `knots`'
+  printf ' SET `knots`.`reason_length`=NULL,'
+  printf ' `knots`.`relevance_level`=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 '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 ' SET `knots`.`reason_length`=0,'
+  printf '`knots`.`active`=1'
+  printf ' WHERE `knots`.`id`=`knot_id`;\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 '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 = row_count_saved + ROW_COUNT();\n'
-  printf 'UNTIL row_count_saved=0\n'
-  printf 'END REPEAT;\n'
+  printf 'CALL `recursively_find_the_culprit(`knot_id`,0);\n'
 
   for copy in '' '_copy'; do
     printf 'CREATE TEMPORARY TABLE `knot_names%s` (' \
@@ -553,25 +500,23 @@ fi
     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 'INSERT INTO `knot_names` (`id`,`name`,`reason_length`,`and`,`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 'INSERT INTO `knot_names` (`id`,`name`,`reason_length`,`and`,`active`)'
   printf ' SELECT `knots`.`id`,'
   printf ' CONCAT('
     printf '`r_a`.`name`,"/",'
@@ -580,7 +525,6 @@ fi
   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`'
@@ -607,8 +551,8 @@ fi
   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 ' WHERE `cause`.`reason_length` IS NOT NULL'
+  printf ' AND `impact`.`reason_length` IS NOT NULL;\n'
 
   printf 'DROP TEMPORARY TABLE `knot_names`;\n'
 
diff --git a/bin/tmp-bootstrap b/bin/tmp-bootstrap
new file mode 100755
index 0000000..0ef436d
--- /dev/null
+++ b/bin/tmp-bootstrap
@@ -0,0 +1,16 @@
+#!/bin/sh
+
+# shellcheck source=../lib/load-configuration
+. "${0%/*}/../lib/load-configuration"
+
+eval "$(
+  # shellcheck disable=SC2016,SC2154
+  sed -n '
+    \,DELIMITER //, p
+    /DROP PROCEDURE IF EXISTS `recursively_find_the_culprit`/,/DELIMITER ;/ p
+  ' "${base_dir}/bin/bootstrap-mysql"
+)" | \
+  mysql -u root -p"$(
+    gpg -d -o - ~/Dokumente/pws.gpg 2>/dev/null | \
+      sed -n '/root@mysql/{N;N;s/.*\n//;p}'
+  )" buildmaster
diff --git a/bin/why-dont-you b/bin/why-dont-you
index 2ea27ab..78587a5 100755
--- a/bin/why-dont-you
+++ b/bin/why-dont-you
@@ -253,7 +253,7 @@ case "${action}" in
         printf ' `content_type` ENUM("bpir","it"),'
         printf ' `reason_length` MEDIUMINT,'
         printf ' `active` BIT,'
-        printf ' `relevant` BIT,'
+        printf ' `relevance_level` MEDIUMINT,'
         printf ' UNIQUE KEY (`id`),'
         printf ' UNIQUE KEY `content`(`content_id`,`content_type`));\n'
         printf 'CREATE TEMPORARY TABLE `edges%s` (' \
@@ -403,8 +403,13 @@ case "${action}" in
       printf ' AND `i`.`content_type`="bpir";\n'
 
       while read -r bpir pkgname; do
-        printf 'CALL `find_the_culprit`(%s);\n' \
-          "${bpir}"
+        printf 'CALL `find_the_culprit`('
+          printf '(SELECT `knots`.`id`'
+          printf ' FROM `knots`'
+          printf ' WHERE `knots`.`content_type`="bpir"'
+          printf ' AND `knots`.`content_id`=%s)' \
+            "${bpir}"
+        printf ');\n'
       done < \
         "${tmp_dir}/packages"
     } | \