diff options
Diffstat (limited to 'bin/bootstrap-mysql')
| -rwxr-xr-x | bin/bootstrap-mysql | 274 |
1 files changed, 109 insertions, 165 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' |