From 32135c23d9e23707de6babd0c9e0783feeaca1d6 Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Mon, 25 Jun 2018 13:36:08 +0200 Subject: buildmaster/deletion-links.php: cleanup + aggregate similar knots --- buildmaster/deletion-links.php | 133 +++++++++++++++++++++++++++-------------- 1 file changed, 88 insertions(+), 45 deletions(-) (limited to 'buildmaster/deletion-links.php') diff --git a/buildmaster/deletion-links.php b/buildmaster/deletion-links.php index 3f9af30..6e12db8 100644 --- a/buildmaster/deletion-links.php +++ b/buildmaster/deletion-links.php @@ -8,71 +8,122 @@ $knots = ""; mysql_run_query( "CREATE TEMPORARY TABLE `d_bpir` (" . "`id` BIGINT, " . - "`arch` MEDIUMINT, " . + "`group` VARCHAR(256), " . + "`color` VARCHAR(7), " . "UNIQUE KEY `id` (`id`)" . ")" ); mysql_run_query( - "INSERT IGNORE INTO `d_bpir` (`id`,`arch`)" . - " SELECT `binary_packages_in_repositories`.`id`,`repositories`.`architecture`" . + "INSERT IGNORE INTO `d_bpir` (`id`,`color`)" . + " SELECT" . + " `binary_packages_in_repositories`.`id`," . + "IF(`build_assignments`.`is_black_listed` IS NULL,\"#800000\",\"#ff0000\") AS `color`" . " FROM `binary_packages_in_repositories`" . " JOIN `binary_packages` ON `binary_packages_in_repositories`.`package`=`binary_packages`.`id`" . - " JOIN `repositories` ON `binary_packages_in_repositories`.`repository`=`repositories`.`id`" . + " JOIN `build_assignments` ON `binary_packages`.`build_assignment`=`build_assignments`.`id`" . " WHERE `binary_packages_in_repositories`.`is_to_be_deleted`" . " AND `binary_packages`.`pkgname` NOT LIKE \"lib32-%\"" + . " LIMIT 25" // TODO: remove ); mysql_run_query( "CREATE TEMPORARY TABLE `d_bpir_copy` (" . "`id` BIGINT, " . - "`arch` MEDIUMINT, " . + "`group` VARCHAR(256), " . + "`color` VARCHAR(7), " . "UNIQUE KEY `id` (`id`)" . ")" ); mysql_run_query( - "INSERT IGNORE INTO `d_bpir_copy` (`id`,`arch`)" . - " SELECT `d_bpir`.`id`,`d_bpir`.`arch`" . + "INSERT IGNORE INTO `d_bpir_copy` (`id`,`color`)" . + " SELECT `d_bpir`.`id`,`d_bpir`.`color`" . " FROM `d_bpir`" ); mysql_run_query( - "CREATE TEMPORARY TABLE `d_it` (" . - "`id` BIGINT, " . - "`arch` MEDIUMINT, " . - "UNIQUE KEY `id` (`id`)" . + "CREATE TEMPORARY TABLE `d_bpir_links` (" . + "`dependent` BIGINT, " . + "`depending_on` BIGINT, " . + "`dep_type` SMALLINT, " . + "UNIQUE KEY `content` (`dependent`,`depending_on`,`dep_type`)" . ")" ); mysql_run_query( - "INSERT IGNORE INTO `d_it` (`id`,`arch`)" . - " SELECT `install_target_providers`.`install_target`,`d_bpir`.`arch`" . + "INSERT INTO `d_bpir_links` (`dependent`,`depending_on`,`dep_type`)" . + " SELECT `d_bpir`.`id`," . + " `itp_bpir`.`id`," . + " `dependencies`.`dependency_type`" . " FROM `d_bpir`" . - " JOIN `binary_packages_in_repositories` ON `binary_packages_in_repositories`.`id`=`d_bpir`.`id`" . - " JOIN `install_target_providers` ON `binary_packages_in_repositories`.`package`=`install_target_providers`.`package`" . - " WHERE NOT EXISTS (" . - "SELECT 1 FROM `install_target_providers` AS `subst_itp`" . - " JOIN `binary_packages_in_repositories` AS `subst_bpir` ON `subst_bpir`.`package`=`subst_itp`.`package`" . - " JOIN `repositories` ON `subst_bpir`.`repository`=`repositories`.`id`" . - " WHERE NOT `subst_bpir`.`is_to_be_deleted`" . - " AND `subst_itp`.`install_target`=`install_target_providers`.`install_target`" . - " AND `repositories`.`architecture`=`d_bpir`.`arch`" . + " JOIN `binary_packages_in_repositories` ON `d_bpir`.`id`=`binary_packages_in_repositories`.`id`" . + " JOIN `dependencies` ON `binary_packages_in_repositories`.`package`=`dependencies`.`dependent`" . + " JOIN `install_target_providers` ON `install_target_providers`.`install_target`=`dependencies`.`depending_on`" . + " JOIN `binary_packages_in_repositories` AS `itp_bpir` ON `itp_bpir`.`package`=`install_target_providers`.`package`" . + " JOIN `d_bpir_copy` ON `itp_bpir`.`id`=`d_bpir_copy`.`id`" . + " WHERE `dependencies`.`dependent`!=`install_target_providers`.`package`" +); + +mysql_run_query( + "CREATE TEMPORARY TABLE `d_bpir_links_copy` (" . + "`dependent` BIGINT, " . + "`depending_on` BIGINT, " . + "`dep_type` SMALLINT, " . + "UNIQUE KEY `content` (`dependent`,`depending_on`,`dep_type`)" . ")" ); +mysql_run_query( + "INSERT IGNORE INTO `d_bpir_links_copy` (`dependent`,`depending_on`,`dep_type`)" . + " SELECT `d_bpir_links`.`dependent`,`d_bpir_links`.`depending_on`,`d_bpir_links`.`dep_type`" . + " FROM `d_bpir_links`" +); + +mysql_run_query( + "UPDATE `d_bpir`" . + " JOIN (" . + "SELECT" . + " `d_bpir_copy`.`id`," . + "SHA2(" . + "GROUP_CONCAT(CONCAT(" . + "IFNULL(`d_bpir_copy`.`color`,\"0\"),\":\"," . + "IFNULL(`d_bpir_links`.`depending_on`,\"0\"),\":\"," . + "IFNULL(`d_bpir_links`.`dep_type`,\"0\"),\":\"," . + "IFNULL(`d_bpir_links_copy`.`dependent`,\"0\"),\":\"," . + "IFNULL(`d_bpir_links_copy`.`dep_type`,\"0\")" . + "))" . + ",256) AS `hash`" . + " FROM `d_bpir_copy`" . + " LEFT JOIN `d_bpir_links` ON `d_bpir_links`.`dependent`=`d_bpir_copy`.`id`" . + " LEFT JOIN `d_bpir_links_copy` ON `d_bpir_links_copy`.`depending_on`=`d_bpir_copy`.`id`" . + " GROUP BY `d_bpir_copy`.`id`" . + ") AS `grouped_d_bpir` ON `grouped_d_bpir`.`id`=`d_bpir`.`id`" . + " SET `d_bpir`.`group`=`grouped_d_bpir`.`hash`" +); + +mysql_run_query( + "UPDATE `d_bpir_copy`" . + " JOIN `d_bpir` ON `d_bpir`.`id`=`d_bpir_copy`.`id`" . + " SET `d_bpir_copy`.`group`=`d_bpir`.`group`" +); + +// TODO + $result = mysql_run_query( - "SELECT `binary_packages_in_repositories`.`id`," . - "`architectures`.`name` AS `arch`," . - "`binary_packages`.`pkgname`," . - "`repositories`.`name` AS `repo`," . - "IF(`build_assignments`.`is_black_listed` IS NULL,\"#800000\",\"#ff0000\") AS `color`" . + "SELECT MAX(`d_bpir`.`id`) AS `id`," . + "GROUP_CONCAT(CONCAT(" . + "`architectures`.`name`,\"/\"," . + "`repositories`.`name`,\"/\"," . + "`binary_packages`.`pkgname`" . + ") SEPARATOR \",\n\") AS `name`," . + "`d_bpir`.`color`" . " FROM `d_bpir`" . " JOIN `binary_packages_in_repositories` ON `d_bpir`.`id`=`binary_packages_in_repositories`.`id`" . " JOIN `binary_packages` ON `binary_packages`.`id`=`binary_packages_in_repositories`.`package`" . - " JOIN `build_assignments` ON `binary_packages`.`build_assignment`=`build_assignments`.`id`" . " JOIN `repositories` ON `binary_packages_in_repositories`.`repository`=`repositories`.`id`" . - " JOIN `architectures` ON `repositories`.`architecture`=`architectures`.`id`" + " JOIN `architectures` ON `repositories`.`architecture`=`architectures`.`id`" . + " GROUP BY `d_bpir`.`group`" ); while ($row = $result->fetch_assoc()) @@ -80,29 +131,21 @@ while ($row = $result->fetch_assoc()) "\"p" . $row["id"] . "\" [label = \"" . - $row["arch"] . - "/" . - $row["repo"] . - "/" . - $row["pkgname"] . + $row["name"] . "\", fontcolor = \"" . $row["color"] . "\"];\n"; $result = mysql_run_query( - "SELECT `d_bpir`.`id` AS `dependent`," . + "SELECT MAX(`d_bpir_links`.`dependent`) AS `dependent`," . "`dependency_types`.`name` AS `dep_type`," . - "`itp_bpir`.`id` AS `depending_on`" . - " FROM `d_bpir`" . - " JOIN `binary_packages_in_repositories` ON `d_bpir`.`id`=`binary_packages_in_repositories`.`id`" . - " JOIN `dependencies` ON `binary_packages_in_repositories`.`package`=`dependencies`.`dependent`" . - " JOIN `dependency_types` ON `dependencies`.`dependency_type`=`dependency_types`.`id`" . - " JOIN `install_target_providers` ON `install_target_providers`.`install_target`=`dependencies`.`depending_on`" . - " JOIN `d_it` ON `d_it`.`id`=`install_target_providers`.`install_target`" . - " JOIN `binary_packages_in_repositories` AS `itp_bpir` ON `itp_bpir`.`package`=`install_target_providers`.`package`" . - " JOIN `d_bpir_copy` ON `itp_bpir`.`id`=`d_bpir_copy`.`id`" . - " WHERE `dependencies`.`dependent`!=`install_target_providers`.`package`" + "MAX(`d_bpir_links`.`depending_on`) AS `depending_on`" . + " FROM `d_bpir_links`" . + " JOIN `dependency_types` ON `d_bpir_links`.`dep_type`=`dependency_types`.`id`" . + " JOIN `d_bpir` ON `d_bpir`.`id`=`d_bpir_links`.`dependent`" . + " JOIN `d_bpir_copy` ON `d_bpir_copy`.`id`=`d_bpir_links`.`depending_on`" . + " GROUP BY CONCAT(`d_bpir`.`group`,\"-\",`d_bpir_copy`.`group`)" ); while ($row = $result->fetch_assoc()) { -- cgit v1.2.3