udd/web/bugs.cgi

#!/usr/bin/ruby -w

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

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

RELEASE_RESTRICT = [
  ['squeeze', 'squeeze', 'id in (select id from bugs_rt_affects_testing)'],
  ['sid', 'sid', 'id in (select id from bugs_rt_affects_unstable)'],
  ['squeeze_and_sid', 'squeeze and sid', 'id in (select id from bugs_rt_affects_testing_and_unstable)'],
  ['squeeze_or_sid', 'squeeze or sid', 'id in (select id from bugs_rt_affects_testing union select id from bugs_rt_affects_unstable)'],
  ['squeeze_not_sid', 'squeeze, not sid', 'id in (select id from bugs_rt_affects_testing) and id not in (select id from bugs_rt_affects_unstable)'],
  ['sid_not_squeeze', 'sid, not squeeze', 'id in (select id from bugs_rt_affects_unstable) and id not in (select id from bugs_rt_affects_testing)']
]

FILTERS = [
 ['fpatch', 'tagged patch', 'id in (select id from bugs_tags where tag=\'patch\')'],
 ['fsecurity', 'tagged security', 'id in (select id from bugs_tags where tag=\'security\')'],
 ['fdone', 'marked as done', 'status = \'done\''],
 ['fpending', 'tagged pending', 'id in (select id from bugs_tags where tag=\'pending\')'],
]

# ignore merged bugs ['fmerged', 'merged bugs', 'id in (select id from bugs_merged_with where id > merged_with)'],

# not in squeeze
# ['fnotmain', 'not in main', 'FIXME'],
# ['fnewer', 'newer than 7 days', 'FIXME'],
SORTS = [
  ['id', 'bug#'],
  ['source', 'source package'],
  ['package', 'binary package'],
  ['last_modified', 'last modified']
]

cgi = CGI::new
p cgi.params
# releases
if RELEASE_RESTRICT.map { |r| r[0] }.include?(cgi.params['release'][0])
  release = cgi.params['release'][0]
else
  release = 'squeeze_or_sid'
end
# sorts
if SORTS.map { |r| r[0] }.include?(cgi.params['sortby'][0])
  sortby = cgi.params['sortby'][0]
else
  sortby = 'id'
end
if ['asc', 'desc'].include?(cgi.params['sorto'][0])
  sorto = cgi.params['sorto'][0]
else
  sorto = 'asc'
end
# filters
filters = {}
FILTERS.map { |r| r[0] }.each do |e|
  if ['notconsidered', 'only', 'ign'].include?(cgi.params[e][0])
    filters[e] = cgi.params[e][0]
  else
    filters[e] = 'notconsidered'
  end
end

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>RC Bugs List @ UDD</title>
</head>
<body>
<h1>Release Critical Bugs List</h1>

<form action="bugs.cgi" method="get">
<p><b>Bugs affecting:</b>
EOF

RELEASE_RESTRICT.each do |r|
  checked = (release == r[0] ? 'CHECKED=\'1\'':'')
  puts "<input type='radio' name='release' value='#{r[0]}' #{checked}/>#{r[1]}&nbsp;&nbsp;"
end
puts <<-EOF
</p>
<table>
<tr><th colspan='4'>FILTERS</th></tr>
<tr><th>not considered</th><th>only</th><th>ignore</th><th>type</th></tr>
EOF
FILTERS.each do |r|
  puts <<-EOF
  <tr>
  <td style='text-align: center;'><input type='radio' name='#{r[0]}' value='' #{filters[r[0]]=='notconsidered'?'CHECKED=\'1\'':''}/></td>
  <td style='text-align: center;'><input type='radio' name='#{r[0]}' value='only' #{filters[r[0]]=='only'?'CHECKED=\'1\'':''}/></td>
  <td style='text-align: center;'><input type='radio' name='#{r[0]}' value='ign #{filters[r[0]]=='ign'?'CHECKED=\'1\'':''}'/></td>
  <td>#{r[1]}</td>
  </tr>
  EOF
end

puts "</table>"
puts "<p><b>Sort by:</b> "
SORTS.each do |r|
  checked = (sortby == r[0] ? 'CHECKED=\'1\'':'')
  puts "<input type='radio' name='sortby' value='#{r[0]}' #{checked}/>#{r[1]}&nbsp;&nbsp;"
end
puts "<b> -- </b>"
[['asc', 'increasing'],[ 'desc', 'decreasing']].each do |r|
  checked = (sorto == r[0] ? 'CHECKED=\'1\'':'')
  puts "<input type='radio' name='sorto' value='#{r[0]}' #{checked}/>#{r[1]}&nbsp;&nbsp;"
end
puts <<-EOF
</p><input type='submit'/>
</form>
EOF
if cgi.params != {}

tstart = Time::now

dbh = DBI::connect('DBI:Pg:dbname=udd;port=5441;host=localhost', 'guest')
q = "select id, bugs.package, bugs.source, title, last_modified from bugs \n"
q += "where #{RELEASE_RESTRICT.select { |r| r[0] == release }[0][2]} \n"
q += "and severity >= 'serious' \n" # hack hack hack
FILTERS.each do |f|
  if filters[f[0]] == 'only'
    q += "and #{f[2]} \n"
  elsif filters[f[0]] == 'ign'
    q += "and not (#{f[2]}) \n"
  end
end

q += "order by #{sortby} #{sorto}"
sth = dbh.prepare(q)
sth.execute
rows = sth.fetch_all

puts "<p><b>#{rows.length} bugs found.</b></p>"
puts <<-EOF
<table>
<tr><th>bug#</th><th>source pkg</th><th>binary pkg</th><th>title</th><th>last&nbsp;modified</th></tr>
EOF
rows.each do |r|
  puts <<-EOF
  <tr>
  <td style='text-align: center;'><a href="http://bugs.debian.org/#{r['id']}">##{r['id']}</a></td>
  <td style='text-align: center;'>#{r['source']}</td>
  <td style='text-align: center;'>#{r['package']}</td>
  <td>#{r['title']}</td>
  <td style='text-align: center;'>#{r['last_modified'].to_date}</td>
  </tr>
  EOF
end
=begin
release goals:
all
include / only

columns:
id
source
package
title

time
data last refreshed
EOF
=end

=begin
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

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>"
rows.each do |r|
   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>"
end
puts "</table>"
sth.finish

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>"

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|
   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>"
end
puts "</table>"
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

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>"
rows.each do |r|
   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>"
end
puts "</table>"
sth.finish

=end
puts "</table>"
sth2 = dbh.prepare("select max(start_time) from timestamps where source = 'bugs' and command = 'run'")
sth2.execute ; r2 = sth2.fetch_all
puts "<p><b>Generated in #{Time::now - tstart} seconds. Last data update: #{r2[0][0]}</b></p>"
puts "<pre>#{q}</pre>"
end # if cgi.params != {}
puts <<-EOF
</body>
</html>
EOF
1	lucas	1786	#!/usr/bin/ruby -w
2
3			require 'dbi'
4			require 'pp'
5			require 'cgi'
6
7			puts "Content-type: text/html\n\n"
8
9			RELEASE_RESTRICT = [
10			['squeeze', 'squeeze', 'id in (select id from bugs_rt_affects_testing)'],
11			['sid', 'sid', 'id in (select id from bugs_rt_affects_unstable)'],
12			['squeeze_and_sid', 'squeeze and sid', 'id in (select id from bugs_rt_affects_testing_and_unstable)'],
13			['squeeze_or_sid', 'squeeze or sid', 'id in (select id from bugs_rt_affects_testing union select id from bugs_rt_affects_unstable)'],
14			['squeeze_not_sid', 'squeeze, not sid', 'id in (select id from bugs_rt_affects_testing) and id not in (select id from bugs_rt_affects_unstable)'],
15			['sid_not_squeeze', 'sid, not squeeze', 'id in (select id from bugs_rt_affects_unstable) and id not in (select id from bugs_rt_affects_testing)']
16			]
17
18			FILTERS = [
19			['fpatch', 'tagged patch', 'id in (select id from bugs_tags where tag=\'patch\')'],
20			['fsecurity', 'tagged security', 'id in (select id from bugs_tags where tag=\'security\')'],
21			['fdone', 'marked as done', 'status = \'done\''],
22			['fpending', 'tagged pending', 'id in (select id from bugs_tags where tag=\'pending\')'],
23			]
24
25			# ignore merged bugs ['fmerged', 'merged bugs', 'id in (select id from bugs_merged_with where id > merged_with)'],
26
27			# not in squeeze
28			# ['fnotmain', 'not in main', 'FIXME'],
29			# ['fnewer', 'newer than 7 days', 'FIXME'],
30			SORTS = [
31			['id', 'bug#'],
32			['source', 'source package'],
33			['package', 'binary package'],
34			['last_modified', 'last modified']
35			]
36
37			cgi = CGI::new
38			p cgi.params
39			# releases
40			if RELEASE_RESTRICT.map { \|r\| r[0] }.include?(cgi.params['release'][0])
41			release = cgi.params['release'][0]
42			else
43			release = 'squeeze_or_sid'
44			end
45			# sorts
46			if SORTS.map { \|r\| r[0] }.include?(cgi.params['sortby'][0])
47			sortby = cgi.params['sortby'][0]
48			else
49			sortby = 'id'
50			end
51			if ['asc', 'desc'].include?(cgi.params['sorto'][0])
52			sorto = cgi.params['sorto'][0]
53			else
54			sorto = 'asc'
55			end
56			# filters
57			filters = {}
58			FILTERS.map { \|r\| r[0] }.each do \|e\|
59			if ['notconsidered', 'only', 'ign'].include?(cgi.params[e][0])
60			filters[e] = cgi.params[e][0]
61			else
62			filters[e] = 'notconsidered'
63			end
64			end
65
66			puts <<-EOF
67			<html>
68			<head>
69			<style type="text/css">
70			td, th {
71			border: 1px solid gray;
72			padding-left: 3px;
73			padding-right: 3px;
74			}
75			tr:hover {
76			background-color: #ccc;
77			}
78			table {
79			border-collapse: collapse;
80			}
81			</style>
82			<title>RC Bugs List @ UDD</title>
83			</head>
84			<body>
85			<h1>Release Critical Bugs List</h1>
86
87			<form action="bugs.cgi" method="get">
88			<p><b>Bugs affecting:</b>
89			EOF
90
91			RELEASE_RESTRICT.each do \|r\|
92			checked = (release == r[0] ? 'CHECKED=\'1\'':'')
93			puts "<input type='radio' name='release' value='#{r[0]}' #{checked}/>#{r[1]}  "
94			end
95			puts <<-EOF
96			</p>
97			<table>
98			<tr><th colspan='4'>FILTERS</th></tr>
99			<tr><th>not considered</th><th>only</th><th>ignore</th><th>type</th></tr>
100			EOF
101			FILTERS.each do \|r\|
102			puts <<-EOF
103			<tr>
104			<td style='text-align: center;'><input type='radio' name='#{r[0]}' value='' #{filters[r[0]]=='notconsidered'?'CHECKED=\'1\'':''}/></td>
105			<td style='text-align: center;'><input type='radio' name='#{r[0]}' value='only' #{filters[r[0]]=='only'?'CHECKED=\'1\'':''}/></td>
106			<td style='text-align: center;'><input type='radio' name='#{r[0]}' value='ign #{filters[r[0]]=='ign'?'CHECKED=\'1\'':''}'/></td>
107			<td>#{r[1]}</td>
108			</tr>
109			EOF
110			end
111
112			puts "</table>"
113			puts "<p><b>Sort by:</b> "
114			SORTS.each do \|r\|
115			checked = (sortby == r[0] ? 'CHECKED=\'1\'':'')
116			puts "<input type='radio' name='sortby' value='#{r[0]}' #{checked}/>#{r[1]}  "
117			end
118			puts "<b> -- </b>"
119			[['asc', 'increasing'],[ 'desc', 'decreasing']].each do \|r\|
120			checked = (sorto == r[0] ? 'CHECKED=\'1\'':'')
121			puts "<input type='radio' name='sorto' value='#{r[0]}' #{checked}/>#{r[1]}  "
122			end
123			puts <<-EOF
124			</p><input type='submit'/>
125			</form>
126			EOF
127			if cgi.params != {}
128
129			tstart = Time::now
130
131			dbh = DBI::connect('DBI:Pg:dbname=udd;port=5441;host=localhost', 'guest')
132			q = "select id, bugs.package, bugs.source, title, last_modified from bugs \n"
133			q += "where #{RELEASE_RESTRICT.select { \|r\| r[0] == release }[0][2]} \n"
134			q += "and severity >= 'serious' \n" # hack hack hack
135			FILTERS.each do \|f\|
136			if filters[f[0]] == 'only'
137			q += "and #{f[2]} \n"
138			elsif filters[f[0]] == 'ign'
139			q += "and not (#{f[2]}) \n"
140			end
141			end
142
143			q += "order by #{sortby} #{sorto}"
144			sth = dbh.prepare(q)
145			sth.execute
146			rows = sth.fetch_all
147
148			puts "<p><b>#{rows.length} bugs found.</b></p>"
149			puts <<-EOF
150			<table>
151			<tr><th>bug#</th><th>source pkg</th><th>binary pkg</th><th>title</th><th>last modified</th></tr>
152			EOF
153			rows.each do \|r\|
154			puts <<-EOF
155			<tr>
156			<td style='text-align: center;'><a href="http://bugs.debian.org/#{r['id']}">##{r['id']}</a></td>
157			<td style='text-align: center;'>#{r['source']}</td>
158			<td style='text-align: center;'>#{r['package']}</td>
159			<td>#{r['title']}</td>
160			<td style='text-align: center;'>#{r['last_modified'].to_date}</td>
161			</tr>
162			EOF
163			end
164			=begin
165			release goals:
166			all
167			include / only
168
169			columns:
170			id
171			source
172			package
173			title
174
175			time
176			data last refreshed
177			EOF
178			=end
179
180			=begin
181			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")
182			sth.execute ; rows = sth.fetch_all
183
184			puts "<h2>RC bugs tagged patch (and not pending)</h2>"
185			puts "<table>"
186			puts "<tr><th>bug</th><th>package</th><th>source</th><th>popcon</th><th>title</th></tr>"
187			rows.each do \|r\|
188			puts "<tr><td><a href=\"http://bugs.debian.org/#{r['id']}\">#{r['id']}</a></td>"
189			puts "<td>#{r['package']}</td>"
190			puts "<td><a href=\"http://packages.qa.debian.org/#{r['source']}\">#{r['source']}</a></td>"
191			puts "<td>#{r['insts']}</td>"
192			puts "<td>#{r['title']}</td>"
193			end
194			puts "</table>"
195			sth.finish
196
197			puts "<h2>RC bugs on packages with a newer version in Ubuntu (possible patches), not tagged patch nor pending</h2>"
198			puts "<table>"
199			puts "<tr><th>bug</th><th>package</th><th>source</th><th>versions (D/U)</th><th>popcon</th><th>title</th></tr>"
200
201			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)
202			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")
203			sth.execute ; rows = sth.fetch_all
204			rows.each do \|r\|
205			puts "<tr><td><a href=\"http://bugs.debian.org/#{r['id']}\">#{r['id']}</a></td>"
206			puts "<td>#{r['package']}</td>"
207			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>"
208			puts "<td>#{r['dversion']} / #{r['uversion']}</td>"
209			puts "<td>#{r['insts']}</td>"
210			puts "<td>#{r['title']}</td>"
211			end
212			puts "</table>"
213			sth.finish
214
215			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")
216			sth.execute ; rows = sth.fetch_all
217
218			puts "<h2>RC bugs affecting only testing (not unstable, and not pending)</h2>"
219			puts "<table>"
220			puts "<tr><th>bug</th><th>package</th><th>source</th><th>popcon</th><th>title</th></tr>"
221			rows.each do \|r\|
222			puts "<tr><td><a href=\"http://bugs.debian.org/#{r['id']}\">#{r['id']}</a></td>"
223			puts "<td>#{r['package']}</td>"
224			puts "<td><a href=\"http://packages.qa.debian.org/#{r['source']}\">#{r['source']}</a></td>"
225			puts "<td>#{r['insts']}</td>"
226			puts "<td>#{r['title']}</td>"
227			end
228			puts "</table>"
229			sth.finish
230
231			=end
232			puts "</table>"
233			sth2 = dbh.prepare("select max(start_time) from timestamps where source = 'bugs' and command = 'run'")
234			sth2.execute ; r2 = sth2.fetch_all
235			puts "<p><b>Generated in #{Time::now - tstart} seconds. Last data update: #{r2[0][0]}</b></p>"
236			puts "<pre>#{q}</pre>"
237			end # if cgi.params != {}
238			puts <<-EOF
239			</body>
240			</html>
241			EOF