Bug#825695: udd todo: List unsolved security issues in the TODO list?

[Petter Reinholdtsen <pere@hungry.com>]

I had a look at the code available from
<URL: ssh://git.debian.org/git/collab-qa/udd.git > and came up with this
drafth patch proposal.  Note, I do not really know ruby and have no way
to test if this is working as intended, so please take it as a early
draft to get some feedback.

Is this the way to get security issues into the todo list?  Note, I
decided to ignore issues with urgency unimportant.  I'm not sure if this
is a good idea, as some of those would be fit for an update via
stable-proposed-updates even if the security team do not want to release
a DSA for the issue.

diff --git a/web/inc/dmd-data.rb b/web/inc/dmd-data.rb
index 55a1ec3..ac78934 100755
--- a/web/inc/dmd-data.rb
+++ b/web/inc/dmd-data.rb
@@ -22,7 +22,7 @@ end
 
 class UDDData
   attr_accessor :debug
-  attr_reader :sources, :versions, :all_bugs, :bugs_tags, :bugs_count, :migration, :buildd, :dmd_todos, :ubuntu_bugs, :autoremovals, :qa, :ubuntu, :ustb, :udev, :dr, :doostb, :dostb, :dstb, :dtst, :lintian
+  attr_reader :sources, :versions, :all_bugs, :bugs_tags, :bugs_count, :migration, :buildd, :dmd_todos, :ubuntu_bugs, :autoremovals, :qa, :ubuntu, :ustb, :udev, :dr, :doostb, :dostb, :dstb, :dtst, :lintian, :security_issues
 
   def UDDData.parse_cgi_params(cgiparams)
     three = {'1' => '', '2' => '', '3' => ''}
@@ -119,6 +119,7 @@ class UDDData
     get_qa
     get_sources_dirty
     get_autoremovals
+    get_security_issues
     get_dmd_todos
     get_ubuntu_bugs
     get_ubuntu_dirty
@@ -729,6 +730,20 @@ and source not in (select source from upload_history where date > (current_date
       end
   end
 
+  def get_security_issues
+    @security_issues = {}
+    return if @sources.empty?
+    q = "SELECT sir.source, sir.release, sir.urgency, sir.issue
+           FROM security_issues_releases sir
+           WHERE sir.status != 'resolved' AND urgency != 'unimportant' AND
+                 sir.source IN (SELECT source FROM mysources) 
+           GROUP BY sir.source, sir.release, sir.urgency, sir.issue;"
+    rows = dbget(q)
+    rows.each do |r|
+      @autoremovals[r['source']] = r.to_h
+    end
+  end
+
   def get_dmd_todos
     @dmd_todos = []
     rc_bugs = @all_bugs.select { |b| ['serious', 'grave', 'critical'].include?(b['severity']) }
@@ -894,6 +909,19 @@ and source not in (select source from upload_history where date > (current_date
       }
     end
 
+    @security_issues.each_pair do |src, v|
+      # v: source, release, urgency, issue
+
+      @dmd_todos << {
+        :shortname => "security_issue_#{src}_#{v['release']}_#{v['issue']}",
+        :type => 'security issue',
+        :source => src,
+        :link => "http://security-tracker.debian.org/tracker/#{v['issue']}",
+        :description => "Open security issue",
+        :details => "#{v['issue']} for #{v['release']} (#{urgency} urgency)"
+      }
+    end
+
     @dmd_todos.map do |t|
       t[:reason] = @sources[t[:source]][:reason]
       t

I've tested the SQL statement, and it present me with this list when I
run it for myself:

    source    | release | urgency  |     issue     
--------------+---------+----------+---------------
 sssd         | wheezy  | low      | CVE-2014-0249
 vorbis-tools | jessie  | low      | CVE-2014-9639
 vorbis-tools | wheezy  | low      | CVE-2014-9639
 vorbis-tools | wheezy  | medium** | CVE-2014-9640
 sssd         | jessie  | medium** | CVE-2015-5292
 vorbis-tools | jessie  | medium** | CVE-2015-6749
 vorbis-tools | wheezy  | medium** | CVE-2015-6749

I am not quite sure what the ** part of the urgency mean.

-- 
Happy hacking
Petter Reinholdtsen