Add stale package relations status screen

For now it is read only. Display a few tables of various ways of
detecting stale package relations. These include inactive users, pkgbase
values that no longer exist, and users that are listed as maintainers
that don't have the proper permissions for that package anymore.

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

5 files changed, 154 insertions, 3 deletions

diff --git a/packages/models.py b/packages/models.py
index 0afdee00..5dbdea45 100644
--- a/packages/models.py
+++ b/packages/models.py
@@ -19,6 +19,16 @@ class PackageRelation(models.Model):
     user = models.ForeignKey(User, related_name="package_relations")
     type = models.PositiveIntegerField(choices=TYPE_CHOICES, default=MAINTAINER)
 
+    def get_associated_packages(self):
+        # TODO: delayed import to avoid circular reference
+        from main.models import Package
+        return Package.objects.filter(pkgbase=self.pkgbase).select_related(
+                'arch', 'repo')
+
+    def repositories(self):
+        packages = self.get_associated_packages()
+        return sorted(set([p.repo for p in packages]))
+
     def __unicode__(self):
         return "%s: %s (%s)" % (
                 self.pkgbase, self.user, self.get_type_display())
diff --git a/packages/urls.py b/packages/urls.py
index b7ce5c74..37ce23c0 100644
--- a/packages/urls.py
+++ b/packages/urls.py
@@ -25,6 +25,7 @@ urlpatterns = patterns('packages.views',
     (r'^(?P<page>\d+)/$',        'search'),
 
     (r'^differences/$',          'arch_differences'),
+    (r'^stale_relations/$',      'stale_relations'),
 
     (r'^(?P<name>[A-z0-9\-+.]+)/$',
         'details'),
diff --git a/packages/utils.py b/packages/utils.py
index aaec0ec4..8d9f13ab 100644
--- a/packages/utils.py
+++ b/packages/utils.py
@@ -5,7 +5,7 @@ from operator import itemgetter
 
 from main.models import Package
 from main.utils import cache_function
-from .models import PackageGroup
+from .models import PackageGroup, PackageRelation
 
 @cache_function(300)
 def get_group_info(include_arches=None):
@@ -128,4 +128,26 @@ SELECT p.id, q.id
     differences.sort(key=lambda a: (a.repo.name, a.pkgname))
     return differences
 
+def get_wrong_permissions():
+    sql = """
+SELECT DISTINCT id
+    FROM (
+        SELECT pr.id, p.repo_id, pr.user_id
+        FROM packages p
+        JOIN packages_packagerelation pr ON p.pkgbase = pr.pkgbase
+        WHERE pr.type = %s
+        ) pkgs
+    WHERE pkgs.repo_id NOT IN (
+        SELECT repo_id FROM user_profiles_allowed_repos ar
+        INNER JOIN user_profiles up ON ar.userprofile_id = up.id
+        WHERE up.user_id = pkgs.user_id
+    )
+"""
+    cursor = connection.cursor()
+    cursor.execute(sql, [PackageRelation.MAINTAINER])
+    to_fetch = [row[0] for row in cursor.fetchall()]
+    relations = PackageRelation.objects.select_related('user').filter(
+            id__in=to_fetch)
+    return relations
+
 # vim: set ts=4 sw=4 et:
diff --git a/packages/views.py b/packages/views.py
index e792175b..9a2094aa 100644
--- a/packages/views.py
+++ b/packages/views.py
@@ -2,7 +2,7 @@ from django import forms
 from django.contrib import messages
 from django.contrib.admin.widgets import AdminDateWidget
 from django.contrib.auth.models import User
-from django.contrib.auth.decorators import permission_required
+from django.contrib.auth.decorators import login_required, permission_required
 from django.conf import settings
 from django.core.mail import send_mail
 from django.db.models import Q
@@ -23,7 +23,7 @@ from main.models import Arch, Repo, Signoff
 from main.utils import make_choice
 from mirrors.models import MirrorUrl
 from .models import PackageRelation
-from .utils import get_group_info, get_differences_info
+from .utils import get_group_info, get_differences_info, get_wrong_permissions
 
 def opensearch(request):
     if request.is_secure():
@@ -401,4 +401,21 @@ def arch_differences(request):
     }
     return direct_to_template(request, 'packages/differences.html', context)
 
+@login_required
+def stale_relations(request):
+    relations = PackageRelation.objects.select_related('user')
+    pkgbases = Package.objects.all().values('pkgbase')
+
+    inactive_user = relations.filter(user__is_active=False)
+    missing_pkgbase = relations.exclude(
+            pkgbase__in=pkgbases).order_by('pkgbase')
+    wrong_permissions = get_wrong_permissions()
+
+    context = {
+            'inactive_user': inactive_user,
+            'missing_pkgbase': missing_pkgbase,
+            'wrong_permissions': wrong_permissions,
+    }
+    return direct_to_template(request, 'packages/stale_relations.html', context)
+
 # vim: set ts=4 sw=4 et:
diff --git a/templates/packages/stale_relations.html b/templates/packages/stale_relations.html
new file mode 100644
index 00000000..975ef1b6
--- /dev/null
+++ b/templates/packages/stale_relations.html
@@ -0,0 +1,101 @@
+{% extends "base.html" %}
+{% block title %}Arch Linux - Stale Package Relations{% endblock %}
+{% block navbarclass %}anb-packages{% endblock %}
+
+{% block content %}
+<div class="box">
+    <h2>Stale Package Relations</h2>
+
+    <h3>Inactive User Relations ({{ inactive_user|length }})</h3>
+
+    <table class="results" id="inactive-user">
+        <thead>
+            <tr>
+                <th>Package Base</th>
+                <th>Packages</th>
+                <th>User</th>
+                <th>Type</th>
+            </tr>
+        </thead>
+        <tbody>
+            {% for relation in inactive_user %}
+            <tr class="{% cycle 'odd' 'even' %}">
+                <td>{{ relation.pkgbase }}</td>
+                <td class="wrap">{% for pkg in relation.get_associated_packages %}
+                    <a href="{{ pkg.get_absolute_url }}"
+                        title="View package details for {{ pkg.pkgname }}">{{ pkg.repo|lower }}/{{ pkg.pkgname }} ({{ pkg.arch }})</a>{% if not forloop.last %}, {% endif %}
+                {% endfor %}</td>
+                <td>{{ relation.user.get_full_name }}</td>
+                <td>{{ relation.get_type_display }}</td>
+            </tr>
+            {% empty %}
+                <tr class="empty"><td colspan="3"><em>No inactive user relations.</em></td></tr>
+            {% endfor %}
+        </tbody>
+    </table>
+
+    <h3>Relations with Non-existent <tt>pkgbase</tt> ({{ missing_pkgbase|length }})</h3>
+
+    <table class="results" id="missing-pkgbase">
+        <thead>
+            <tr>
+                <th>Package Base</th>
+                <th>User</th>
+                <th>Type</th>
+            </tr>
+        </thead>
+        <tbody>
+            {% for relation in missing_pkgbase %}
+            <tr class="{% cycle 'odd' 'even' %}">
+                <td>{{ relation.pkgbase }}</td>
+                <td>{{ relation.user.get_full_name }}</td>
+                <td>{{ relation.get_type_display }}</td>
+            </tr>
+            {% empty %}
+                <tr class="empty"><td colspan="3"><em>No non-existent pkgbase relations.</em></td></tr>
+            {% endfor %}
+        </tbody>
+    </table>
+
+    <h3>Maintainers with Wrong Permissions ({{ wrong_permissions|length }})</h3>
+
+    <table class="results" id="wrong-permissions">
+        <thead>
+            <tr>
+                <th>Package Base</th>
+                <th>Packages</th>
+                <th>User</th>
+                <th>Allowed Repos</th>
+                <th>Currently in Repos</th>
+            </tr>
+        </thead>
+        <tbody>
+            {% for relation in wrong_permissions %}
+            <tr class="{% cycle 'odd' 'even' %}">
+                <td>{{ relation.pkgbase }}</td>
+                <td class="wrap">{% for pkg in relation.get_associated_packages %}
+                    <a href="{{ pkg.get_absolute_url }}"
+                        title="View package details for {{ pkg.pkgname }}">{{ pkg.repo|lower }}/{{ pkg.pkgname }} ({{ pkg.arch }})</a>{% if not forloop.last %}, {% endif %}
+                {% endfor %}</td>
+                <td>{{ relation.user.get_full_name }}</td>
+                <td class="wrap">{{ relation.user.userprofile.allowed_repos.all|join:", " }}</td>
+                <td class="wrap">{{ relation.repositories|join:", " }}</td>
+            </tr>
+            {% empty %}
+                <tr class="empty"><td colspan="3"><em>No relations with wrong permissions.</em></td></tr>
+            {% endfor %}
+        </tbody>
+    </table>
+
+</div>
+{% load cdn %}{% jquery %}
+<script type="text/javascript" src="/media/jquery.tablesorter.min.js"></script>
+<script type="text/javascript" src="/media/archweb.js"></script>
+<script type="text/javascript">
+$(document).ready(function() {
+    $('#inactive-user:not(:has(tbody tr.empty))').tablesorter({widgets: ['zebra'], sortList: [[2,0]]});
+    $('#missing-pkgbase:not(:has(tbody tr.empty))').tablesorter({widgets: ['zebra'], sortList: [[0,0]]});
+});
+    $('#wrong-permissions:not(:has(tbody tr.empty))').tablesorter({widgets: ['zebra'], sortList: [[2,0]]});
+</script>
+{% endblock %}