udd/web/bapase.cgi

#!/usr/bin/ruby -w
require 'dbi'
require 'cgi'
require 'actions'

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

WAIT_RM_O = 50
WAIT_RM_RM = 100
WAIT_O_O = 50
WAIT_ORM_RM = 50

DATEZERO = Date::parse('0000-01-01')
CURDATE = Date::today

dbh = DBI::connect('DBI:Pg:dbname=udd;port=5441;host=localhost', 'guest')
cgi = CGI.new

type = cgi.params['t']
if not type.nil?
  type = type[0]
end

if type == 'o'
  orphaned = true
  query = "select * from bapase where type is not null order by orphaned_age desc"
elsif type == 'nmu'
  orphaned = true
  query = "select * from bapase where nmu and nmus > 1 order by nmus desc"
elsif type == 'testing'
  orphaned = true
  query = "select * from bapase where source not in (select source from sources where distribution='debian' and release='squeeze') order by testing_age desc, first_seen asc"
elsif type == 'nodd'
  orphaned = true
  query = <<EOF
select * from bapase where source in (
SELECT source
FROM sources
WHERE distribution = 'debian' AND release = 'sid'
AND sources.source NOT IN (
SELECT sources.source
FROM sources
LEFT OUTER JOIN uploaders ON (sources.source = uploaders.source AND sources.version = uploaders.version AND sources.distribution = uploaders.distribution AND sources.release = uploaders.release AND sources.component = uploaders.component)
WHERE sources.distribution = 'debian' AND sources.release = 'sid'
AND (maintainer_email in (SELECT email FROM carnivore_emails, active_dds WHERE active_dds.id = carnivore_emails.id)
OR email in (SELECT email FROM carnivore_emails, active_dds WHERE active_dds.id = carnivore_emails.id)
OR maintainer_email ~ '.*@lists.(alioth.)?debian.org'
OR email ~ '.*@lists.(alioth.)?debian.org'))
) order by upload_age desc
EOF
elsif type == 'maintnmu'
  orphaned = true
  query = <<EOF
select * from bapase where source in (
  select source from sources where distribution='debian' and release='squeeze' and maintainer_email in (
select nmus.email from
(select email, count(*) as tot from
(select maintainer_email as email, source from sources
where release = 'sid'
and distribution = 'debian'
and component = 'main'
union
select email, source from uploaders 
where release = 'sid'
and distribution = 'debian'
and component = 'main') as foo
group by email) as tot,
(select email, count(*) as nmus from
(select sources.maintainer_email as email, sources.source from sources, upload_history uh
where release = 'sid'
and distribution = 'debian'
and component = 'main'
and sources.source = uh.source and sources.version = uh.version
and uh.nmu
union
select email, uploaders.source from uploaders, upload_history uh
where release = 'sid'
and distribution = 'debian'
and component = 'main'
and uploaders.source = uh.source and uploaders.version = uh.version
and uh.nmu
) as foo
group by email) as nmus
where nmus * 100 / tot >= 100
and nmus.email = tot.email)
union (select source from uploaders where distribution='debian' and release='squeeze' and email in (
select nmus.email from
(select email, count(*) as tot from
(select maintainer_email as email, source from sources
where release = 'sid'
and distribution = 'debian'
and component = 'main'
union
select email, source from uploaders 
where release = 'sid'
and distribution = 'debian'
and component = 'main') as foo
group by email) as tot,
(select email, count(*) as nmus from
(select sources.maintainer_email as email, sources.source from sources, upload_history uh
where release = 'sid'
and distribution = 'debian'
and component = 'main'
and sources.source = uh.source and sources.version = uh.version
and uh.nmu
union
select email, uploaders.source from uploaders, upload_history uh
where release = 'sid'
and distribution = 'debian'
and component = 'main'
and uploaders.source = uh.source and uploaders.version = uh.version
and uh.nmu
) as foo
group by email) as nmus
where nmus * 100 / tot >= 100
and nmus.email = tot.email
))) order by nmus
EOF
else
  puts <<-EOF
  <a href="bapase.cgi?t=o">Orphaned packages</a><br/>
  <a href="bapase.cgi?t=nmu">Packages maintained with NMUs</a><br/>
  <a href="bapase.cgi?t=testing">Packages not in testing</a><br/>
  <a href="bapase.cgi?t=nodd">Packages not maintained by DDs</a><br/>
  <a href="bapase.cgi?t=maintnmu">Packages maintained or co-maintained by people with lots of NMUs</a><br/>
  </body></html>
  EOF
  exit(0)
end

# FIXME add case where type is uknown

$actions = Actions::fetch

puts <<-EOF
<html><head>
<style type="text/css">
  td, th {
    border: 1px solid gray;
    padding-left: 2px;
    padding-right: 2px;
  }
  th {
    font-size: 8pt;
  }
  tr:hover  {
    background-color: #ccc;
  }
  table {
    border-collapse: collapse;
  }
</style>
<title>Bapase</title>
</head><body>
<table border="1"><tr>
<th></th><th>Package</th><th>Action</th>
EOF
puts "<th>Orphaned</th>" if orphaned
puts <<-EOF
<th>Testing</th>
<th>Migrate</th>
<th>Popcon</th>
<th>Bugs</th>
<th>Last upload</th>
<th>NMUs</th>
<th>Comments</th>
</tr>
EOF
sth = dbh.prepare(query)
sth.execute
res = sth.fetch_all
n = 0
res.each do |r|
  n += 1
  pkg = r['source']
  puts "<tr><td>#{n}</td>"
  puts "<td><a href=\"http://packages.qa.debian.org/#{pkg}\">#{pkg}</a>"
  # FIXME removals
  if $actions[pkg]
    if $actions[pkg].act_todo
      puts "<td><b>#{$actions[pkg].act_status}</b></td>"
    else
      puts "<td>#{$actions[pkg].act_status}</td>"
    end
  else
    puts "<td></td>"
  end
  if orphaned
    if r['type']
      puts "<td><a href=\"http://bugs.debian.org/#{r['bug']}\">#{r['type']}</a>&nbsp;(#{r['orphaned_age']})</td>"
    else
      puts "<td></td>"
    end
  end
  if r['testing_age'] and r['testing_age'] > 1
    puts "<td>#{r['testing_age']}</td>"
  else
    puts "<td></td>"
  end
  if r['sync_age'] and r['sync_age'] > 1
    puts "<td>#{r['sync_age']}</td>"
  else
    puts "<td></td>"
  end
  puts "<td>#{r['insts']}&nbsp;/&nbsp;#{r['vote']}</td>"
  puts "<td><a href=\"http://bugs.debian.org/src:#{pkg}\">#{r['rc_bugs']}&nbsp;/&nbsp;#{r['all_bugs']}</a></td>"
  puts "<td>#{r['upload_age']}</td>"
  puts "<td>#{r['nmus']}</td>"
  puts "<td></td>"
  if $actions[pkg]
    comment = $actions[pkg].act_comment.gsub(/#\d+/) do |bug|
      bugn = bug.gsub(/^#/, '')
      "<a href=\"http://bugs.debian.org/#{bugn}\">#{bug}</a>"
    end
    puts "<td>#{comment}</td>"
  else
    puts "<td></td>"
  end
  puts "</tr>"
end
puts "</table>"

puts " -- #{res.length} packages listed."
puts "</body></html>"


#sth.finish
1	#!/usr/bin/ruby -w
2	require 'dbi'
3	require 'cgi'
4	require 'actions'
5
6	puts "Content-type: text/html\n\n"
7
8	WAIT_RM_O = 50
9	WAIT_RM_RM = 100
10	WAIT_O_O = 50
11	WAIT_ORM_RM = 50
12
13	DATEZERO = Date::parse('0000-01-01')
14	CURDATE = Date::today
15
16	dbh = DBI::connect('DBI:Pg:dbname=udd;port=5441;host=localhost', 'guest')
17	cgi = CGI.new
18
19	type = cgi.params['t']
20	if not type.nil?
21	type = type[0]
22	end
23
24	if type == 'o'
25	orphaned = true
26	query = "select * from bapase where type is not null order by orphaned_age desc"
27	elsif type == 'nmu'
28	orphaned = true
29	query = "select * from bapase where nmu and nmus > 1 order by nmus desc"
30	elsif type == 'testing'
31	orphaned = true
32	query = "select * from bapase where source not in (select source from sources where distribution='debian' and release='squeeze') order by testing_age desc, first_seen asc"
33	elsif type == 'nodd'
34	orphaned = true
35	query = <<EOF
36	select * from bapase where source in (
37	SELECT source
38	FROM sources
39	WHERE distribution = 'debian' AND release = 'sid'
40	AND sources.source NOT IN (
41	SELECT sources.source
42	FROM sources
43	LEFT OUTER JOIN uploaders ON (sources.source = uploaders.source AND sources.version = uploaders.version AND sources.distribution = uploaders.distribution AND sources.release = uploaders.release AND sources.component = uploaders.component)
44	WHERE sources.distribution = 'debian' AND sources.release = 'sid'
45	AND (maintainer_email in (SELECT email FROM carnivore_emails, active_dds WHERE active_dds.id = carnivore_emails.id)
46	OR email in (SELECT email FROM carnivore_emails, active_dds WHERE active_dds.id = carnivore_emails.id)
47	OR maintainer_email ~ '.*@lists.(alioth.)?debian.org'
48	OR email ~ '.*@lists.(alioth.)?debian.org'))
49	) order by upload_age desc
50	EOF
51	elsif type == 'maintnmu'
52	orphaned = true
53	query = <<EOF
54	select * from bapase where source in (
55	select source from sources where distribution='debian' and release='squeeze' and maintainer_email in (
56	select nmus.email from
57	(select email, count(*) as tot from
58	(select maintainer_email as email, source from sources
59	where release = 'sid'
60	and distribution = 'debian'
61	and component = 'main'
62	union
63	select email, source from uploaders
64	where release = 'sid'
65	and distribution = 'debian'
66	and component = 'main') as foo
67	group by email) as tot,
68	(select email, count(*) as nmus from
69	(select sources.maintainer_email as email, sources.source from sources, upload_history uh
70	where release = 'sid'
71	and distribution = 'debian'
72	and component = 'main'
73	and sources.source = uh.source and sources.version = uh.version
74	and uh.nmu
75	union
76	select email, uploaders.source from uploaders, upload_history uh
77	where release = 'sid'
78	and distribution = 'debian'
79	and component = 'main'
80	and uploaders.source = uh.source and uploaders.version = uh.version
81	and uh.nmu
82	) as foo
83	group by email) as nmus
84	where nmus * 100 / tot >= 100
85	and nmus.email = tot.email)
86	union (select source from uploaders where distribution='debian' and release='squeeze' and email in (
87	select nmus.email from
88	(select email, count(*) as tot from
89	(select maintainer_email as email, source from sources
90	where release = 'sid'
91	and distribution = 'debian'
92	and component = 'main'
93	union
94	select email, source from uploaders
95	where release = 'sid'
96	and distribution = 'debian'
97	and component = 'main') as foo
98	group by email) as tot,
99	(select email, count(*) as nmus from
100	(select sources.maintainer_email as email, sources.source from sources, upload_history uh
101	where release = 'sid'
102	and distribution = 'debian'
103	and component = 'main'
104	and sources.source = uh.source and sources.version = uh.version
105	and uh.nmu
106	union
107	select email, uploaders.source from uploaders, upload_history uh
108	where release = 'sid'
109	and distribution = 'debian'
110	and component = 'main'
111	and uploaders.source = uh.source and uploaders.version = uh.version
112	and uh.nmu
113	) as foo
114	group by email) as nmus
115	where nmus * 100 / tot >= 100
116	and nmus.email = tot.email
117	))) order by nmus
118	EOF
119	else
120	puts <<-EOF
121	<a href="bapase.cgi?t=o">Orphaned packages</a><br/>
122	<a href="bapase.cgi?t=nmu">Packages maintained with NMUs</a><br/>
123	<a href="bapase.cgi?t=testing">Packages not in testing</a><br/>
124	<a href="bapase.cgi?t=nodd">Packages not maintained by DDs</a><br/>
125	<a href="bapase.cgi?t=maintnmu">Packages maintained or co-maintained by people with lots of NMUs</a><br/>
126	</body></html>
127	EOF
128	exit(0)
129	end
130
131	# FIXME add case where type is uknown
132
133	$actions = Actions::fetch
134
135	puts <<-EOF
136	<html><head>
137	<style type="text/css">
138	td, th {
139	border: 1px solid gray;
140	padding-left: 2px;
141	padding-right: 2px;
142	}
143	th {
144	font-size: 8pt;
145	}
146	tr:hover {
147	background-color: #ccc;
148	}
149	table {
150	border-collapse: collapse;
151	}
152	</style>
153	<title>Bapase</title>
154	</head><body>
155	<table border="1"><tr>
156	<th></th><th>Package</th><th>Action</th>
157	EOF
158	puts "<th>Orphaned</th>" if orphaned
159	puts <<-EOF
160	<th>Testing</th>
161	<th>Migrate</th>
162	<th>Popcon</th>
163	<th>Bugs</th>
164	<th>Last upload</th>
165	<th>NMUs</th>
166	<th>Comments</th>
167	</tr>
168	EOF
169	sth = dbh.prepare(query)
170	sth.execute
171	res = sth.fetch_all
172	n = 0
173	res.each do \|r\|
174	n += 1
175	pkg = r['source']
176	puts "<tr><td>#{n}</td>"
177	puts "<td><a href=\"http://packages.qa.debian.org/#{pkg}\">#{pkg}</a>"
178	# FIXME removals
179	if $actions[pkg]
180	if $actions[pkg].act_todo
181	puts "<td><b>#{$actions[pkg].act_status}</b></td>"
182	else
183	puts "<td>#{$actions[pkg].act_status}</td>"
184	end
185	else
186	puts "<td></td>"
187	end
188	if orphaned
189	if r['type']
190	puts "<td><a href=\"http://bugs.debian.org/#{r['bug']}\">#{r['type']}</a> (#{r['orphaned_age']})</td>"
191	else
192	puts "<td></td>"
193	end
194	end
195	if r['testing_age'] and r['testing_age'] > 1
196	puts "<td>#{r['testing_age']}</td>"
197	else
198	puts "<td></td>"
199	end
200	if r['sync_age'] and r['sync_age'] > 1
201	puts "<td>#{r['sync_age']}</td>"
202	else
203	puts "<td></td>"
204	end
205	puts "<td>#{r['insts']} / #{r['vote']}</td>"
206	puts "<td><a href=\"http://bugs.debian.org/src:#{pkg}\">#{r['rc_bugs']} / #{r['all_bugs']}</a></td>"
207	puts "<td>#{r['upload_age']}</td>"
208	puts "<td>#{r['nmus']}</td>"
209	puts "<td></td>"
210	if $actions[pkg]
211	comment = $actions[pkg].act_comment.gsub(/#\d+/) do \|bug\|
212	bugn = bug.gsub(/^#/, '')
213	"<a href=\"http://bugs.debian.org/#{bugn}\">#{bug}</a>"
214	end
215	puts "<td>#{comment}</td>"
216	else
217	puts "<td></td>"
218	end
219	puts "</tr>"
220	end
221	puts "</table>"
222
223	puts " -- #{res.length} packages listed."
224	puts "</body></html>"
225
226
227
228
229	#sth.finish