Bug#720511: qa.debian.org: [UDD] Request to add attached patches to udd cgi-bin

To: Debian Bug Tracking System <submit@bugs.debian.org>
Subject: Bug#720511: qa.debian.org: [UDD] Request to add attached patches to udd cgi-bin
From: Pawel Sarbinowski <onexemailx@gmail.com>
Date: Thu, 22 Aug 2013 23:30:23 +0300
Message-id: <[🔎] 20130822203023.29246.20879.reportbug@venus.cs.aueb.gr>
Reply-to: Pawel Sarbinowski <onexemailx@gmail.com>, 720511@bugs.debian.org

Package: qa.debian.org
Severity: wishlist

Dear UDD maintainers,

I am making the debian android app for gsoc 2013 and i need to add the following 4 cgi 
scripts to the udd.debian.org/cgi-bin directory. 

rcbugs.cgi , new-maintainers.cgi and last-uploads.cgi are same as the previous scripts but 
return their output in csv format when called with the parameter ?out=csv. 

overlapping_interests.cgi Takes two arguments deva and devb which are the maintainers emails 
and returns a list of package names. This list consists of the packages 
of devb that are a dependency to some package maintained by deva.

It would be great if they could be uploaded before mid-september when gsoc ends so i can use
them in the first version.

Kind regards,
Pawel Sarbinowski

#!/usr/bin/env python
"""
Takes two arguments deva and devb which are the maintainers emails 
and returns a list of package names. This list consists of the packages 
of devb that are a dependency to some package maintained by deva.
"""

import cgi, re, sys
import pprint

import psycopg2

DATABASE = {'database': 'udd',
            'port': 5452,
            'host': 'localhost',
            'user': 'guest',
           }


def execute_query(query):
    conn = psycopg2.connect(**DATABASE)
    cursor = conn.cursor()
    cursor.execute(query)

    row = cursor.fetchone()
    while not row is None:
        yield row
        row = cursor.fetchone()

    cursor.close()
    conn.close()

def qet_maintainer_depends(maintainer):
    query="""SELECT DISTINCT depends 
                FROM all_packages 
            WHERE maintainer_email='{0}' AND depends is not NULL """.format(maintainer)

    depends = set()
    for row in execute_query(query):
        dep_line = row[0]
        dep_line = dep_line.replace(',', ' ').replace('|', ' ')
        # Remove versions from versioned depends
        dep_line = re.sub('\(.*\)', '', dep_line)
        
        for x in dep_line.split(' '):
            stripped = x.strip()

            if stripped :
                #add ' character so they can be imported 
                #into a where clause of another query
                depends.add("'"+stripped+"'")
        
    return list(depends)

def get_overlapping_pks(depends, maintainer):
    query = """SELECT DISTINCT package FROM all_packages 
            WHERE package in ({0}) AND maintainer_email='{1}'""".format(','.join(depends), maintainer)

    for row in execute_query(query):        
        yield row[0]

def main():
    print "Content-type: text/html\n\n"

    arguments = cgi.FieldStorage()
    
    if not "deva" in arguments or not "devb" in arguments:
        print "Not deva or devb argument was provided."
        sys.exit(-1)
     
    depends = qet_maintainer_depends(arguments["deva"].value)  
    pkgs = get_overlapping_pks(depends, arguments["devb"].value)
    
    for pkg in pkgs:
        print pkg

if __name__ == '__main__':
    main()

#!/usr/bin/ruby

require 'dbi'
require 'pp'
require 'yaml'
require 'cgi'

csvflag = false

cgi = CGI::new

if cgi.has_key?('out') and cgi.params['out'][0] == 'csv'
  csvflag = true
end

URELEASE=YAML::load(IO::read('../ubuntu-releases.yaml'))['devel']

puts "Content-type: text/html\n\n"

unless csvflag
  puts <<-EOF
  <html>
  <head>
  <style type="text/css">
    td, th {
      border: 1px solid gray;
      padding-left: 3px;
      padding-right: 3px;
    }
    tr:hover  {
      background-color: #ccc;
    }
    table {
      border-collapse: collapse;
    }
  </style>
  <title>Possibly easy targets for RC bug squashing</title>
  </head>
  <body>
  <h1>Possibly easy targets for RC bug squashing</h1>
  EOF
end

dbh = DBI::connect('DBI:Pg:dbname=udd;port=5452;host=localhost', 'guest')

sth = dbh.prepare("select id, bugs.package, bugs.source, insts, title from bugs, popcon_src where bugs.source = popcon_src.source and id in (select id from bugs_rt_affects_testing_and_unstable) and id in (select id from bugs_tags where tag='patch') and id not in (select id from bugs_tags where tag='pending') and severity >= 'serious' order by id")
sth.execute ; rows = sth.fetch_all

unless csvflag
  puts "<h2>RC bugs tagged patch (and not pending)</h2>"
  puts "<table>"
  puts "<tr><th>bug</th><th>package</th><th>source</th><th>popcon</th><th>title</th></tr>"
else
  puts "#RC bugs tagged patch (and not pending)"
end

rows.each do |r|
  unless csvflag
    puts "<tr><td><a href=\"http://bugs.debian.org/#{r['id']}\">#{r['id']}</a></td>"
    puts "<td>#{r['package']}</td>"
    puts "<td><a href=\"http://packages.qa.debian.org/#{r['source']}\">#{r['source']}</a></td>"
    puts "<td>#{r['insts']}</td>"
    puts "<td>#{r['title']}</td>"
  else
    puts "#{r['id']},#{r['source']},#{r['insts']},#{r['title']}"
    #puts "bug, source, popcorn, title"
  end

end

puts "</table>" unless csvflag

sth.finish

unless csvflag
  puts "<h2>RC bugs on packages with a newer version in Ubuntu (possible patches), not tagged patch nor pending</h2>"
  puts "<table>"
  puts "<tr><th>bug</th><th>package</th><th>source</th><th>versions (D/U)</th><th>popcon</th><th>title</th></tr>"
else
  puts "#RC bugs on packages with a newer version in Ubuntu (possible patches), not tagged patch nor pending"
  #puts "bug, source, versions, popcon, title"
end

sth = dbh.prepare("WITH ubudeb AS (select distinct on (d.source, u.source) d.source as dsource, u.source as usource, d.version as dversion, u.version as uversion from sources_uniq d, ubuntu_sources u where d.release = 'sid' and d.distribution = 'debian' and u.release = '#{URELEASE}' and u.distribution = 'ubuntu' and u.source = d.source and u.version > d.version order by d.source asc, u.source asc, d.version desc)
select id, bugs.package, bugs.source, title, dversion, uversion, insts from bugs, ubudeb, popcon_src where popcon_src.source = bugs.source and id in (select id from bugs_rt_affects_testing_and_unstable) and id not in (select id from bugs_tags where tag='patch') and id not in (select id from bugs_tags where tag='pending') and severity >= 'serious' and ubudeb.dsource = bugs.source order by id")
sth.execute ; rows = sth.fetch_all
rows.each do |r|
  unless csvflag
    puts "<tr><td><a href=\"http://bugs.debian.org/#{r['id']}\">#{r['id']}</a></td>"
    puts "<td>#{r['package']}</td>"
    puts "<td><a href=\"http://packages.qa.debian.org/#{r['source']}\">#{r['source']}</a> <a href=\"https://launchpad.net/ubuntu/#{URELEASE}/+source/#{r['source']}/+changelog\">UbCh</a></td>"
    puts "<td>#{r['dversion']} / #{r['uversion']}</td>"
    puts "<td>#{r['insts']}</td>"
    puts "<td>#{r['title']}</td>"
  else
    puts "#{r['id']},#{r['source']},#{r['dversion']} / #{r['uversion']},#{r['insts']},#{r['title']}"
  end

end

puts "</table>" unless csvflag

sth.finish

sth = dbh.prepare("select id, bugs.package, bugs.source, insts, title from bugs, popcon_src where bugs.source = popcon_src.source and id in (select id from bugs_rt_affects_testing) and id not in (select id from bugs_rt_affects_unstable) and severity >= 'serious' order by package")
sth.execute ; rows = sth.fetch_all

unless csvflag
  puts "<h2>RC bugs affecting only testing (not unstable, and not pending)</h2>"
  puts "<table>"
  puts "<tr><th>bug</th><th>package</th><th>source</th><th>popcon</th><th>title</th></tr>"
else
  puts "#RC bugs affecting only testing (not unstable, and not pending)"
  #puts "bug,source, popcon, title"
end
rows.each do |r|
  unless csvflag
    puts "<tr><td><a href=\"http://bugs.debian.org/#{r['id']}\">#{r['id']}</a></td>"
    puts "<td>#{r['package']}</td>"
    puts "<td><a href=\"http://packages.qa.debian.org/#{r['source']}\">#{r['source']}</a></td>"
    puts "<td>#{r['insts']}</td>"
    puts "<td>#{r['title']}</td>"
  else
    puts "#{r['id']},#{r['source']},#{r['insts']},#{r['title']}"
  end

end

puts "</table>" unless csvflag

sth.finish

#!/usr/bin/ruby

require 'cgi'
require 'dbi'
require 'pp'

csvflag = false

cgi = CGI::new

if cgi.has_key?('out') and cgi.params['out'][0] == 'csv'
  csvflag = true
end

puts "Content-type: text/html\n\n"

unless csvflag
  puts <<-EOF
  <html>
  <head>
  <meta http-equiv="Content-Type" content="text/html;charset=utf-8">
  <style type="text/css">
    td, th {
      border: 1px solid gray;
      padding-left: 3px;
      padding-right: 3px;
    }
    tr:hover  {
      background-color: #ccc;
    }
    table {
      border-collapse: collapse;
    }
  </style>
  <title>Latest uploads for Debian developers</title>
  </head>
  <body>
  EOF
end

dbh = DBI::connect('DBI:Pg:dbname=udd;port=5452;host=localhost', 'guest')

q = "
select changed_by_email, changed_by_name, date, source, version from upload_history
where (changed_by_name, changed_by_email, date) in (
select changed_by_name, changed_by_email, max(date)
from upload_history
group by changed_by_name, changed_by_email)
order by date desc
"

#in case of csv output just get 100 results
q << " LIMIT 100" if csvflag


sth = dbh.prepare(q)
sth.execute ; rows = sth.fetch_all
unless csvflag
  puts "<h2>Latest uploads for Debian developers</h2>"
  puts "(Looking at Changed-By: only, so developers can appear more than once if they changed the email they are using for Debian work)"

  puts "<table>"
  puts "<tr><th>date</th><th>uploader</th><th>package</th></tr>"
end

rows.each do |r|
  unless csvflag
    puts "<tr><td>#{r['date'].to_s.split(' ')[0]}</td><td>#{r['changed_by_name']} &lt;#{r['changed_by_email']}&gt;</td><td>#{r['source']} #{r['version']}</td></tr>"
  else
    puts "#{r['date'].to_s.split(' ')[0]},#{r['changed_by_name']},#{r['changed_by_email']},#{r['source']} #{r['version']}"
  end

end

unless csvflag
  puts "</table>"
  puts "Query:<br><pre>#{q}</pre>"
end

sth.finish

#!/usr/bin/ruby

require 'dbi'
require 'pp'
require 'cgi'

csvflag = false

cgi = CGI::new

if cgi.has_key?('out') and cgi.params['out'][0] == 'csv'
  csvflag = true
end

puts "Content-type: text/html\n\n"

unless csvflag
  puts <<-EOF
  <html>
  <head>
  <meta http-equiv="Content-Type" content="text/html;charset=utf-8">
  <style type="text/css">
    td, th {
      border: 1px solid gray;
      padding-left: 3px;
      padding-right: 3px;
    }
    tr:hover  {
      background-color: #ccc;
    }
    table {
      border-collapse: collapse;
    }
  </style>
  <title>New debian maintainers</title>
  </head>
  <body>
  EOF
end

dbh = DBI::connect('DBI:Pg:dbname=udd;port=5452;host=localhost', 'guest')

q = "
select changed_by_email, changed_by_name, date, source, version, login from
(select changed_by_email, changed_by_name, date, source, version, fingerprint from upload_history
where (changed_by_name, changed_by_email, date) in (
select changed_by_name, changed_by_email, min(date)
from upload_history
group by changed_by_name, changed_by_email)
) uploads
LEFT JOIN (select distinct key, login from carnivore_keys, carnivore_login where carnivore_keys.id = carnivore_login.id) carn ON uploads.fingerprint = carn.key
where (changed_by_email, login) not in (select email, login from carnivore_emails, carnivore_login where carnivore_emails.id = carnivore_login.id)
order by date desc
"

#in case of csv output just get 100 results
q << " LIMIT 100" if csvflag

sth = dbh.prepare(q)
sth.execute ; rows = sth.fetch_all

unless csvflag
  puts "<h2>New Debian maintainers</h2>"
  puts "This page lists the first upload of each maintainer (identified by his name and email), together with its sponsor."


  puts "<table>"
  puts "<tr><th>date</th><th>maintainer</th><th>package</th><th>sponsor</th></tr>"
end

rows.each do |r|
  unless csvflag
    puts "<tr><td>#{r['date'].to_s.split(' ')[0]}</td><td>#{r['changed_by_name']} &lt;#{r['changed_by_email']}&gt;</td><td>#{r['source']} #{r['version']}</td><td>#{r['login']}</td></tr>"
  else
    puts "#{r['date'].to_s.split(' ')[0]},#{r['changed_by_name']},#{r['changed_by_email']},#{r['source']} #{r['version']},#{r['login']}"
  end
end

unless csvflag
  puts "</table>"
  puts "Query:<br><pre>#{q}</pre>"
end

sth.finish

Reply to:

Follow-Ups:
- Bug#720511: marked as done (qa.debian.org: [UDD] Request to add attached patches to udd cgi-bin)
  - From: owner@bugs.debian.org (Debian Bug Tracking System)

Prev by Date: Re: [PTS] Unneeded upstream version mangling in separate watch files
Next by Date: Bug#720514: qa.debian.org: Link to source package in binary package page is missing
Previous by thread: Re: [PTS] Unneeded upstream version mangling in separate watch files
Next by thread: Bug#720511: marked as done (qa.debian.org: [UDD] Request to add attached patches to udd cgi-bin)
Index(es):
- Date
- Thread