bin/bootstrap-mysql: some progress?

1 files changed, 124 insertions, 97 deletions

diff --git a/bin/bootstrap-mysql b/bin/bootstrap-mysql
index b08afab..351971b 100755
--- a/bin/bootstrap-mysql
+++ b/bin/bootstrap-mysql
@@ -367,131 +367,158 @@ fi
   printf '//\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 '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 max_relevance_level INT DEFAULT 0;\n'
+  printf 'DECLARE min_edges_count INT DEFAULT 0;\n'
+
+  printf 'CREATE TEMPORARY TABLE `todo_knots` ('
+  printf '`knot_id` BIGINT NOT NULL,'
+  printf '`edges_count` BIGINT,'
+  printf '`irrelevant` BIT,'
+  printf 'UNIQUE KEY `knot_id` (`knot_id`)'
+  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` AS `c_k`'
+  printf 'UPDATE `knots`'
+  printf ' SET `knots`.`reason_length`=0,'
+  printf '`knots`.`active`=1'
+  printf ' WHERE `knots`.`id`=`knot_id`;\n'
+
+  printf 'trial_loop: REPEAT\n'
+
+
+# about which relevane_level should we care?
+
+    # test here for contradictions: "exists" & "foreach" conditions
+    printf 'SELECT max_relevance_level:=MIN(`i_k`.`relevance_level`)'
+    printf ' FROM `knots` AS `i_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 ' JOIN `knots_copy` AS `c_k`'
+    printf ' ON `edges`.`cause`=`c_k`.`id`'
+    printf ' WHERE `i_k`.`reason_length` IS NOT NULL'
+    printf ' AND `i_k`.'
+
+
+    printf ''
 
-    printf 'SET row_count_saved=row_count_saved+ROW_COUNT();\n'
+    # start again if something was done
+    printf 'IF (ROW_COUNT() != 0)'
+      printf ' ITERATE `trial_loop`\n'
+    printf 'END IF;\n'
+
+    # if the proof is done, all the steps below will find nothing which
+    # is still to be proven, and the loop will end
 
     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 '`knots_copy`.`active`=`knots`.`active`,'
+    printf '`knots_copy`.`relevance_level`=`knots`.`relevance_level`;\n'
+
+    # set cause-knots of and = 1 / or = 0 (which is unambiguous => fast)
+    printf 'SET row_count_saved = 0;\n'
 
-  printf 'UNTIL (row_count_saved=0) OR EXISTS ('
-    printf 'SELECT 1 FROM `knots` AS `c_k`'
+    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 ' WHERE (`c_k`.`active` XOR `i_k`.`active` XOR `edges`.`invert`)'
-    printf ' AND `c_k`.`reason_length` IS NOT NULL'
+    printf ' SET `c_k`.`active`=(`i_k`.`active` XOR `edges`.`invert`),'
+    printf '`c_k`.`reason_length`=`i_k`.`reason_length`+1,'
+    printf '`c_k`.`relevance_level`=`i_k`.`relevance_level`'
+    printf ' WHERE `c_k`.`reason_length` IS NULL'
     printf ' AND `i_k`.`reason_length` IS NOT NULL'
-    printf ')\n'
-  printf 'END REPEAT;\n'
+    printf ' AND (`i_k`.`and`=`i_k`.`active`);\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'
+    # start again if something was done
+    printf 'IF (ROW_COUNT() != 0)'
+      printf ' ITERATE `trial_loop`\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 max_relevance_level:=IFNULL(MAX(`knots`.`relevance_level`),0)'
+    printf ' FROM `knots`'
+    printf ' WHERE `knots`.`reason_length` IS NOT NULL;\n'
 
-  printf 'UNTIL (row_count_saved=0) OR EXISTS ('
-    printf 'SELECT 1 FROM `knots` AS `c_k`'
+    # try through ambiguous knots:
+    #   and = 0 with all reasoned causes = 1
+    #   or = 1 with all reasoned causes = 0
+    printf 'DELETE FROM `todo_knots`;\n'
+    printf 'INSERT IGNORE INTO `todo_knots` (`knot_id`,`edges_count`,`irrelevant`)'
+    printf ' SELECT'
+    printf ' MIN('
+      printf 'IF('
+        printf '`c_k`.`reason_length` IS NULL,'
+        printf '`c_k`.`id`,'
+        printf 'NULL'
+      printf ')'
+    printf '),'
+    printf 'SUM('
+      printf 'IF('
+        printf '`c_k`.`reason_length` IS NULL,'
+        printf '1,'
+        printf '0'
+      printf ')'
+    printf '),'
+    printf 'IFNULL('
+      printf 'MAX('
+        printf 'IF('
+          # is this knot already proven?
+          printf '`c_k`.`reason_length` IS NOT NULL'
+          printf ' AND (`c_k`.`active` XOR `edges`.`invert` = `i_k`.`active`)'
+          printf '1,'
+          printf '0'
+        printf ')'
+      printf '),'
+      printf '0'
+    printf ')'
+    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 (`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 ' WHERE `i_k`.`reason_length` IS NOT NULL'
+    printf ' AND (`i_k`.`and` XOR `i_k`.`active`)'
+    printf ' GROUP BY `i_k`.`id`;\n'
 
-  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 'SELECT min_edges_count:=MIN(`todo_knots`.`edges_count`)'
+    printf ' FROM `todo_knots`'
+    printf ' WHERE NOT `todo_knots`.`irrelevant`;\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 'DELETE FROM `todo_knots`'
+    printf ' WHERE `todo_knots`.`irrelevant`'
+    printf ' OR `todo_knots`.`edges_count`!=min_edges_count;\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`'
-  printf ' SET `knots`.`reason_length`=0,'
-  printf '`knots`.`active`=1'
-  printf ' WHERE `knots`.`id`=`knot_id`;\n'
+    printf 'UPDATE `knots` AS `c_k`'
+    printf ' JOIN `todo_knots`'
+    printf ' ON `todo_knots`.`knot_id`=`c_k`.`id`'
+    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 '`c_k`.`relevance_level`=max_relevance_level+1+`c_k`.`id`'
+    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'
+  # start again if something was done
+  printf 'UNTIL ROW_COUNT() = 0\n'
+  printf 'END REPEAT trial_loop;\n'
 
-  printf 'CALL `recursively_find_the_culprit(`knot_id`,0);\n'
+  printf 'DROP TEMPORARY TABLE `knots_copy`;\n'
 
   for copy in '' '_copy'; do
     printf 'CREATE TEMPORARY TABLE `knot_names%s` (' \