--- Begin Message ---
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']} <#{r['changed_by_email']}></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']} <#{r['changed_by_email']}></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
--- End Message ---