packages/differences.php: show differences to upstream, too

2 files changed, 86 insertions, 9 deletions

diff --git a/lib/style.php b/lib/style.php
index 99bdce2..b1dcd31 100644
--- a/lib/style.php
+++ b/lib/style.php
@@ -98,6 +98,11 @@ $difflist_sorts = array(
     "label" => "i686 Version",
     "mysql" => "`i686_version`"
   ),
+  "x86_64_version" => array(
+    "title" => "x86_64 version",
+    "label" => "x86_64 Version",
+    "mysql" => "`x86_64_version`"
+  ),
   "repo" => array(
     "title" => "repository",
     "label" => "Repository",
@@ -139,6 +144,7 @@ $difflist_print_columns = array(
     '$i686_font_post',
     '"</a>'
   ),
+  'x86_64_version' => array('$x86_64_version'),
   'repository' => array('$repository'),
   'i486_move_date' => array('$i486_move_date'),
   'i686_move_date' => array('$i686_move_date')
diff --git a/packages/differences.php b/packages/differences.php
index f9b43af..f04ea67 100644
--- a/packages/differences.php
+++ b/packages/differences.php
@@ -2,6 +2,7 @@
 
 require_once "../init.php";
 
+require_once BASE . "/lib/helper.php";
 require_once BASE . "/lib/style.php";
 require_once BASE . "/lib/mysql.php";
 
@@ -46,25 +47,65 @@ if (array_key_exists('sort', $_GET)) {
   }
 }
 
+function check_a_older_than_b($a_v, $a_bp, $b_v, $b_bp) {
+  return
+    '`' . $a_v . '`.`order`<`' . $b_v . '`.`order`' .
+    ' OR (' .
+      '`' . $a_v . '`.`order`=`' . $b_v . '`.`order`' .
+      ' AND `' . $a_bp . '`.`pkgrel`<`' . $b_bp . '`.`pkgrel`' .
+    ')';
+}
+
 $result = mysql_run_query(
   'SELECT ' .
   '`bp_i486`.`pkgname`,' .
   mysql_query_package_version('bp_i486') . ' AS `i486_version`,' .
   mysql_query_package_version('bp_i686') . ' AS `i686_version`,' .
+  str_replace(
+    array(
+      '`upstream_packages`.`sub_pkgrel_omitted`',
+      '`upstream_packages`.`sub_pkgrel`'
+    ),
+    array(1, 0),
+    mysql_query_package_version('upstream_packages')
+  ) . ' AS `x86_64_version`,' .
   '`r_i486`.`name` AS `repository`,' .
   'MAX(`bpir_i486`.`last_moved`) AS `i486_last_moved`,' .
   'MAX(`bpir_i686`.`last_moved`) AS `i686_last_moved`,' .
   'IF(' .
-    '`v_i486`.`order`<`v_i686`.`order`' .
-    ' OR `v_i486`.`order`=`v_i686`.`order`' .
-    ' AND `bp_i486`.`pkgrel`<`bp_i686`.`pkgrel`' .
-    ',1,0) AS `i486_is_oldest`' .
+    check_a_older_than_b('v_i486','bp_i486','v_i686','bp_i686') .
+    ' OR ' . check_a_older_than_b('v_i486','bp_i486','v_x86_64','upstream_packages') .
+    ',1,0) AS `i486_is_old`,' .
+  'IF(' .
+    '`newer_i486`.`id` IS NULL,' .
+    '0,1' .
+  ') AS `i486_exists_newer`,' .
+  'IF(' .
+    check_a_older_than_b('v_i686','bp_i686','v_i486','bp_i486') .
+    ' OR ' . check_a_older_than_b('v_i686','bp_i686','v_x86_64','upstream_packages') .
+    ',1,0) AS `i686_is_old`,' .
+  'IF(' .
+    '`newer_i686`.`id` IS NULL,' .
+    '0,1' .
+  ') AS `i686_exists_newer`' .
   ' FROM `binary_packages` AS `bp_i486`' .
   mysql_join_binary_packages_binary_packages_in_repositories('bp_i486', 'bpir_i486') .
   mysql_join_binary_packages_in_repositories_repositories('bpir_i486', 'r_i486') .
   ' AND `r_i486`.`is_on_master_mirror`' .
   mysql_join_repositories_architectures('r_i486', 'ra_i486') .
   ' AND `ra_i486`.`name`="i486"' .
+  ' LEFT JOIN (' .
+    '`binary_packages` AS `newer_i486`' .
+    mysql_join_binary_packages_binary_packages_in_repositories('newer_i486','newer_i486_bpir') .
+    mysql_join_binary_packages_in_repositories_repositories('newer_i486_bpir','newer_i486_r') .
+    ' AND `newer_i486_r`.`is_on_master_mirror`' .
+    mysql_join_repositories_architectures('newer_i486_r','newer_i486_ra') .
+    ' AND `newer_i486_ra`.`name`="i486"' .
+    ' JOIN `repository_stability_relations` AS `i486_rsr`' .
+    ' ON `i486_rsr`.`less_stable`=`newer_i486_r`.`stability`' .
+  ') ON `newer_i486`.`pkgname`=`bp_i486`.`pkgname`' .
+  ' AND `i486_rsr`.`more_stable`=`r_i486`.`stability`' .
+  ' AND `r_i486`.`id`!=`newer_i486_r`.`id`' .
   ' JOIN `binary_packages` AS `bp_i686`' .
   ' ON `bp_i486`.`pkgname`=`bp_i686`.`pkgname`' .
   mysql_join_binary_packages_binary_packages_in_repositories('bp_i686', 'bpir_i686') .
@@ -73,15 +114,36 @@ $result = mysql_run_query(
   ' AND `r_i486`.`stability`=`r_i686`.`stability`' .
   mysql_join_repositories_architectures('r_i686', 'ra_i686') .
   ' AND `ra_i686`.`name`="i686"' .
+  ' LEFT JOIN (' .
+    '`binary_packages` AS `newer_i686`' .
+    mysql_join_binary_packages_binary_packages_in_repositories('newer_i686','newer_i686_bpir') .
+    mysql_join_binary_packages_in_repositories_repositories('newer_i686_bpir','newer_i686_r') .
+    ' AND `newer_i686_r`.`is_on_master_mirror`' .
+    mysql_join_repositories_architectures('newer_i686_r','newer_i686_ra') .
+    ' AND `newer_i686_ra`.`name`="i686"' .
+    ' JOIN `repository_stability_relations` AS `i686_rsr`' .
+    ' ON `i686_rsr`.`less_stable`=`newer_i686_r`.`stability`' .
+  ') ON `newer_i686`.`pkgname`=`bp_i686`.`pkgname`' .
+  ' AND `i686_rsr`.`more_stable`=`r_i686`.`stability`' .
+  ' AND `r_i686`.`id`!=`newer_i686_r`.`id`' .
   ' JOIN `versions` as `v_i486`' .
   ' ON `v_i486`.`epoch`=`bp_i486`.`epoch`' .
   ' AND `v_i486`.`version`=`bp_i486`.`pkgver`' .
   ' JOIN `versions` as `v_i686`' .
   ' ON `v_i686`.`epoch`=`bp_i686`.`epoch`' .
   ' AND `v_i686`.`version`=`bp_i686`.`pkgver`' .
+  ' LEFT JOIN (' .
+    '`upstream_packages`' .
+    ' JOIN `versions` AS `v_x86_64`' .
+    ' ON `v_x86_64`.`epoch`=`upstream_packages`.`epoch`' .
+    ' AND `v_x86_64`.`version`=`upstream_packages`.`pkgver`' .
+  ') ON `upstream_packages`.`pkgname`=`bp_i486`.`pkgname`' .
   ' WHERE `bp_i486`.`epoch`!=`bp_i686`.`epoch`' .
   ' OR `bp_i486`.`pkgver`!=`bp_i686`.`pkgver`' .
   ' OR `bp_i486`.`pkgrel`!=`bp_i686`.`pkgrel`' .
+  ' OR `bp_i486`.`epoch`!=`upstream_packages`.`epoch`' .
+  ' OR `bp_i486`.`pkgver`!=`upstream_packages`.`pkgver`' .
+  ' OR `bp_i486`.`pkgrel`!=`upstream_packages`.`pkgrel`' .
   ' GROUP BY CONCAT(`r_i486`.`stability`, "-", `bp_i486`.`pkgname`)' .
   ' ORDER BY ' . $sort . '`r_i486`.`stability`,`bp_i486`.`pkgname`'
 );
@@ -89,16 +151,25 @@ $differences = array();
 while ($row = $result -> fetch_assoc()) {
   $row['i486_move_date'] = substr($row['i486_last_moved'], 0, 10);
   $row['i686_move_date'] = substr($row['i686_last_moved'], 0, 10);
-  if ($row['i486_is_oldest']) {
-    $row['i486_font_pre'] = '<font color="#ff0000">';
+  if ($row['i486_is_old']) {
+    if ($row['i486_exists_newer'])
+      $row['i486_font_pre'] = '<font color="#800000">';
+    else
+      $row['i486_font_pre'] = '<font color="#ff0000">';
     $row['i486_font_post'] = '</font>';
-    $row['i686_font_pre'] = '';
-    $row['i686_font_post'] = '';
   } else {
     $row['i486_font_pre'] = '';
     $row['i486_font_post'] = '';
-    $row['i686_font_pre'] = '<font color="#ff0000">';
+  }
+  if ($row['i686_is_old']) {
+    if ($row['i686_exists_newer'])
+      $row['i686_font_pre'] = '<font color="#800000">';
+    else
+      $row['i686_font_pre'] = '<font color="#ff0000">';
     $row['i686_font_post'] = '</font>';
+  } else {
+    $row['i686_font_pre'] = '';
+    $row['i686_font_post'] = '';
   }
   $differences[] = $row;
 }