Multilib differences report

This new tables shows multilib packages paired with their regular
counterparts in the normal repos if the pkgver differs. A few name hacks
are needed to trim lib32-, -multilib, etc. from the name to find the
matching package.

Signed-off-by: Dan McGee <dan@archlinux.org>

4 files changed, 113 insertions, 26 deletions

diff --git a/packages/utils.py b/packages/utils.py
index f8e1f2a1..5703db3b 100644
--- a/packages/utils.py
+++ b/packages/utils.py
@@ -1,4 +1,5 @@
 from collections import defaultdict
+from itertools import chain
 from operator import itemgetter
 
 from django.db import connection
@@ -63,6 +64,7 @@ def get_split_packages_info():
         split['repo'] = all_repos[split['repo']]
     return split_pkgs
 
+
 class Difference(object):
     def __init__(self, pkgname, repo, pkg_a, pkg_b):
         self.pkgname = pkgname
@@ -89,6 +91,7 @@ class Difference(object):
             return cmp(self.__dict__, other.__dict__)
         return False
 
+
 @cache_function(300)
 def get_differences_info(arch_a, arch_b):
     # This is a monster. Join packages against itself, looking for packages in
@@ -143,6 +146,46 @@ SELECT p.id, q.id
     return differences
 
 
+def multilib_differences():
+    # Query for checking multilib out of date-ness
+    sql = """
+SELECT ml.id, reg.id
+    FROM packages ml
+    JOIN packages reg
+    ON (
+        reg.pkgname = (
+            CASE WHEN ml.pkgname LIKE %s
+                THEN SUBSTRING(ml.pkgname, 7)
+            WHEN ml.pkgname LIKE %s
+                THEN SUBSTRING(ml.pkgname FROM 1 FOR CHAR_LENGTH(ml.pkgname) - 9)
+            ELSE
+                ml.pkgname
+            END
+        )
+        AND reg.pkgver != ml.pkgver
+    )
+    JOIN repos r ON reg.repo_id = r.id
+    WHERE ml.repo_id = %s
+    AND r.testing = %s
+    AND r.staging = %s
+    AND reg.arch_id = %s
+    ORDER BY ml.last_update
+"""
+    multilib = Repo.objects.get(name__iexact='multilib')
+    i686 = Arch.objects.get(name='i686')
+    params = ['lib32-%', '%-multilib', multilib.id, False, False, i686.id]
+
+    cursor = connection.cursor()
+    cursor.execute(sql, params)
+    results = cursor.fetchall()
+
+    # fetch all of the necessary packages
+    to_fetch = set(chain.from_iterable(results))
+    pkgs = Package.objects.normal().in_bulk(to_fetch)
+
+    return [(pkgs[ml], pkgs[reg]) for ml, reg in results]
+
+
 def get_wrong_permissions():
     sql = """
 SELECT DISTINCT id
diff --git a/packages/views/__init__.py b/packages/views/__init__.py
index 13ad0c71..aa7da262 100644
--- a/packages/views/__init__.py
+++ b/packages/views/__init__.py
@@ -16,7 +16,7 @@ from main.models import Package, PackageFile, Arch, Repo
 from mirrors.models import MirrorUrl
 from ..models import PackageRelation, PackageGroup
 from ..utils import (get_group_info, get_differences_info,
-        get_wrong_permissions)
+        multilib_differences, get_wrong_permissions)
 
 # make other views available from this same package
 from .flag import flaghelp, flag, flag_confirmed, unflag, unflag_all
@@ -233,10 +233,12 @@ def arch_differences(request):
     arch_a = Arch.objects.get(name='i686')
     arch_b = Arch.objects.get(name='x86_64')
     differences = get_differences_info(arch_a, arch_b)
+    multilib_diffs = multilib_differences()
     context = {
             'arch_a': arch_a,
             'arch_b': arch_b,
             'differences': differences,
+            'multilib_differences': multilib_diffs
     }
     return direct_to_template(request, 'packages/differences.html', context)
 
diff --git a/sitestatic/archweb.css b/sitestatic/archweb.css
index a354cb96..3407cb4b 100644
--- a/sitestatic/archweb.css
+++ b/sitestatic/archweb.css
@@ -668,9 +668,13 @@ div#pkglist-about {
 }
 
 /* search fields  and other filter selections */
+.filter-criteria {
+    margin-bottom: 1em;
+}
+
 .filter-criteria h3 {
     font-size: 1em;
-    margin-top:0;
+    margin-top: 0;
 }
 
 .filter-criteria div {
diff --git a/templates/packages/differences.html b/templates/packages/differences.html
index 33455d26..f5416d2b 100644
--- a/templates/packages/differences.html
+++ b/templates/packages/differences.html
@@ -4,31 +4,32 @@
 
 {% block content %}
 {% if differences %}
-<div id="differences-filter" class="box filter-criteria">
+<div class="box">
     <h2>Package Differences by Architecture</h2>
-    <h3>Filter Differences View</h3>
-    <form id="diff_filter" method="post" action=".">
-    <fieldset>
-        <legend>Select filter criteria</legend>
-        <div><label for="id_archonly" title="Limit packages to selected architecture">Architecture Limitation</label>
-            <select name="archonly" id="id_archonly">
-                <option value="all">Show All</option>
-                <option value="both" selected="selected">Only In Both</option>
-                <option value="{{ arch_a.name }}">In {{ arch_a.name }} Only</option>
-                <option value="{{ arch_b.name }}">In {{ arch_b.name }} Only</option>
-            </select>
-        </div>
-        <div><label for="id_multilib" title="Show multilib packages"><tt>[multilib]</tt> Visible</label>
-            <input type="checkbox" name="multilib" id="id_multilib" value="multilib"/></div>
-        <div><label for="id_minor" title="Show minor version mismatches">Minor Version Mismatches</label>
-            <input type="checkbox" name="minor" id="id_minor" value="minor"/></div>
-        <div ><label>&nbsp;</label><input title="Reset search criteria" type="button" id="criteria_reset" value="Reset"/></div>
-    </fieldset>
-    </form>
-</div>
+    <div id="differences-filter" class="filter-criteria">
+        <h3>Filter Differences View</h3>
+        <form id="diff_filter" method="post" action=".">
+        <fieldset>
+            <legend>Select filter criteria</legend>
+            <div><label for="id_archonly" title="Limit packages to selected architecture">Architecture Limitation</label>
+                <select name="archonly" id="id_archonly">
+                    <option value="all">Show All</option>
+                    <option value="both" selected="selected">Only In Both</option>
+                    <option value="{{ arch_a.name }}">In {{ arch_a.name }} Only</option>
+                    <option value="{{ arch_b.name }}">In {{ arch_b.name }} Only</option>
+                </select>
+            </div>
+            <div><label for="id_multilib" title="Show multilib packages"><tt>[multilib]</tt> Visible</label>
+                <input type="checkbox" name="multilib" id="id_multilib" value="multilib"/></div>
+            <div><label for="id_minor" title="Show minor version mismatches">Minor Version Mismatches</label>
+                <input type="checkbox" name="minor" id="id_minor" value="minor"/></div>
+            <div ><label>&nbsp;</label><input title="Reset search criteria" type="button" id="criteria_reset" value="Reset"/></div>
+        </fieldset>
+        </form>
+    </div>
+    {# TODO some sort of spacing here #}
 
-<div class="box">
-    <table class="results">
+    <table id="table_differences" class="results">
         <thead>
             <tr>
                 <th>Package Name</th>
@@ -57,17 +58,54 @@
         </tbody>
     </table>
 </div>
+
+<div class="box">
+    <h2>Multilib Differences to Main Packages</h2>
+
+    <table id="table_multilib_differences" class="results">
+        <thead>
+            <tr>
+                <th>Multilib Name</th>
+                <th>Multilib Version</th>
+                <th>i686 Version</th>
+                <th>i686 Name</th>
+                <th>i686 Repo</th>
+                <th>Multilib Last Updated</th>
+                <th>i686 Last Updated</th>
+            </tr>
+        </thead>
+        <tbody>
+            {% for pkg1, pkg2 in multilib_differences %}
+            <tr class="{% cycle 'odd' 'even' %}">
+                <td><a href="{{ pkg1.get_absolute_url }}"
+                        title="View package details for {{ pkg1.pkgname }}">{{ pkg1.pkgname }}</a></td>
+                <td><span{% if pkg1.flag_date %} class="flagged"{% endif %}>{{ pkg1.full_version }}</span></td>
+                <td><span{% if pkg2.flag_date %} class="flagged"{% endif %}>{{ pkg2.full_version }}</span></td>
+                <td><a href="{{ pkg2.get_absolute_url }}"
+                        title="View package details for {{ pkg2.pkgname }}">{{ pkg2.pkgname }}</a></td>
+                <td>{{ pkg2.repo }}</td>
+                <td>{{ pkg1.last_update|date }}</td>
+                <td>{{ pkg2.last_update|date }}</td>
+            </tr>
+            {% endfor %}
+        </tbody>
+    </table>
+
+</div>
+
 {% load cdn %}{% jquery %}
 <script type="text/javascript" src="{{ STATIC_URL }}jquery.tablesorter.min.js"></script>
 <script type="text/javascript" src="{{ STATIC_URL }}archweb.js"></script>
 <script type="text/javascript">
 $(document).ready(function() {
-    $('.results').tablesorter({widgets: ['zebra'], sortList: [[1,0], [0,0]]});
+    $('#table_differences').tablesorter({widgets: ['zebra'], sortList: [[1,0], [0,0]]});
     $('#diff_filter select').change(filter_packages);
     $('#diff_filter input').change(filter_packages);
     $('#criteria_reset').click(filter_packages_reset);
     // fire function on page load to ensure the current form selections take effect
     filter_packages();
+
+    $('#table_multilib_differences').tablesorter({widgets: ['zebra'], sortList: [[5, 0]]});
 });
 </script>
 {% endif %}