summaryrefslogtreecommitdiff
path: root/bin/bootstrap-mysql
diff options
context:
space:
mode:
Diffstat (limited to 'bin/bootstrap-mysql')
-rwxr-xr-xbin/bootstrap-mysql221
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` (' \