Added a "bounceing ticket" view.

This gives us some visibility into tickets that bounce back and forth between
closed and wontfix.

3 files changed, 76 insertions, 0 deletions

diff --git a/tracdb/migrations/0002_bouncing_tickets_view.py b/tracdb/migrations/0002_bouncing_tickets_view.py
new file mode 100644
index 00000000..203e9577
--- /dev/null
+++ b/tracdb/migrations/0002_bouncing_tickets_view.py
@@ -0,0 +1,35 @@
+from south.db import dbs
+from south.v2 import SchemaMigration
+
+#
+# Create a database view for "bouncing" tickets - tickets that change their
+# state from closed back to open, possibly a number of times.
+#
+
+class Migration(SchemaMigration):
+    viewname = "bouncing_tickets"
+    viewquery = """
+        SELECT
+           ticket.id,
+           ticket.summary,
+           COUNT(*) AS times_reopened,
+           MAX(change.time) AS last_reopen_time
+        FROM ticket_change AS change
+        JOIN ticket ON change.ticket = ticket.id
+        WHERE
+           change.field = 'status'
+           AND change.oldvalue = 'closed'
+           AND change.newvalue != 'closed'
+           AND ticket.resolution = 'wontfix'
+        GROUP BY ticket.id;
+    """
+
+    def forwards(self, orm):
+        db = dbs['trac']
+        db.execute("CREATE VIEW %s AS %s" % (self.viewname, self.viewquery))
+        db.execute('COMMIT')
+
+    def backwards(self, orm):
+        db = dbs['trac']
+        db.execute("DROP VIEW %s" % self.viewname)
+        db.execute('COMMIT')
diff --git a/tracdb/urls.py b/tracdb/urls.py
new file mode 100644
index 00000000..e4f8aa84
--- /dev/null
+++ b/tracdb/urls.py
@@ -0,0 +1,10 @@
+from django.conf.urls import patterns, url
+from . import views
+
+urlpatterns = patterns('',
+    url(
+        r'^bouncing/$',
+        views.bouncing_tickets,
+        name='bouncing_tickets',
+    ),
+)
diff --git a/tracdb/views.py b/tracdb/views.py
new file mode 100644
index 00000000..c2fba15a
--- /dev/null
+++ b/tracdb/views.py
@@ -0,0 +1,31 @@
+import datetime
+from django.shortcuts import render
+from django import db
+from django.utils.tzinfo import FixedOffset
+
+def bouncing_tickets(request):
+    c = db.connections['trac'].cursor()
+    c.execute("""SELECT * FROM bouncing_tickets
+                 WHERE times_reopened >= 3
+                 ORDER BY last_reopen_time DESC""")
+    tickets = dictfetchall(c)
+
+    # Fix timestamps. LOLTrac.
+    for t in tickets:
+        t['last_reopen_time'] = ts2dt(t['last_reopen_time'])
+
+    return render(request,
+        'tracdb/bouncing_tickets.html',
+        {'tickets': tickets}
+    )
+
+def ts2dt(ts):
+    epoc = datetime.datetime(1970, 1, 1, tzinfo=FixedOffset(0))
+    return epoc + datetime.timedelta(microseconds=ts)
+
+def dictfetchall(cursor):
+    desc = cursor.description
+    return [
+        dict(zip([col[0] for col in desc], row))
+        for row in cursor.fetchall()
+    ]