From 7e2276029aebdea3ca4836f46268fcce98812058 Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Fri, 20 Jul 2018 13:47:27 +0200 Subject: bin/bootstrap-mysql: some progress? --- bin/bootstrap-mysql | 221 +++++++++++++++++++++++++++++----------------------- 1 file 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` (' \ -- cgit v1.2.3-70-g09d2