1"""
2helper functions for cruft-report
4@contact: Debian FTPMaster <ftpmaster@debian.org>
5@copyright 2011 Torsten Werner <twerner@debian.org>
6"""
8# This program is free software; you can redistribute it and/or modify
9# it under the terms of the GNU General Public License as published by
10# the Free Software Foundation; either version 2 of the License, or
11# (at your option) any later version.
13# This program is distributed in the hope that it will be useful,
14# but WITHOUT ANY WARRANTY; without even the implied warranty of
15# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16# GNU General Public License for more details.
18# You should have received a copy of the GNU General Public License
19# along with this program; if not, write to the Free Software
20# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
22################################################################################
24from daklib.dbconn import *
26from sqlalchemy import func
27from sqlalchemy.orm import object_session, aliased
30def newer_version(lowersuite_name: str, highersuite_name: str, session, include_equal=False) -> list[tuple[str, str, str]]:
31 '''
32 Finds newer versions in lowersuite_name than in highersuite_name. Returns a
33 list of tuples (source, higherversion, lowerversion) where higherversion is
34 the newest version from highersuite_name and lowerversion is the newest
35 version from lowersuite_name.
36 '''
38 lowersuite = get_suite(lowersuite_name, session)
39 highersuite = get_suite(highersuite_name, session)
41 def get_suite_sources(suite):
42 q1 = session.query(DBSource.source,
43 func.max(DBSource.version).label('version')). \
44 with_parent(suite). \
45 group_by(DBSource.source). \
46 subquery()
48 return aliased(q1)
50 def get_suite_binaries(suite):
51 q1 = session.query(DBBinary.package,
52 DBSource.source,
53 func.max(DBSource.version).label('version'),
54 Architecture.arch_string,
55 func.max(DBBinary.version).label('binversion')). \
56 filter(DBBinary.suites.contains(suite)). \
57 join(DBBinary.source). \
58 join(DBBinary.architecture). \
59 group_by(
60 DBBinary.package,
61 DBSource.source,
62 Architecture.arch_string,
63 ). \
64 subquery()
66 return aliased(q1)
68 highq = get_suite_sources(highersuite)
69 lowq = get_suite_sources(lowersuite)
71 query = session.query(
72 highq.c.source,
73 highq.c.version.label('higherversion'),
74 lowq.c.version.label('lowerversion')
75 ). \
76 join(lowq, highq.c.source == lowq.c.source)
78 if include_equal:
79 query = query.filter(highq.c.version <= lowq.c.version)
80 else:
81 query = query.filter(highq.c.version < lowq.c.version)
83 list = []
84 # get all sources that have a higher version in lowersuite than in
85 # highersuite
86 for (source, higherversion, lowerversion) in query:
87 q1 = session.query(DBBinary.package,
88 DBSource.source,
89 DBSource.version,
90 Architecture.arch_string
91 ). \
92 filter(DBBinary.suites.contains(highersuite)). \
93 join(DBBinary.source). \
94 join(DBBinary.architecture). \
95 filter(DBSource.source == source). \
96 subquery()
97 q2 = session.query(q1.c.arch_string).group_by(q1.c.arch_string)
98 # all architectures for which source has binaries in highersuite
99 archs_high = set(x[0] for x in q2.all())
101 highq = get_suite_binaries(highersuite)
102 lowq = get_suite_binaries(lowersuite)
104 query = session.query(highq.c.arch_string). \
105 join(lowq, highq.c.source == lowq.c.source). \
106 filter(highq.c.arch_string == lowq.c.arch_string). \
107 filter(highq.c.package == lowq.c.package). \
108 filter(highq.c.source == source)
110 if include_equal:
111 query = query. \
112 filter(highq.c.binversion <= lowq.c.binversion). \
113 filter(highq.c.version <= lowq.c.version)
114 else:
115 query = query. \
116 filter(highq.c.binversion < lowq.c.binversion). \
117 filter(highq.c.version < lowq.c.version)
119 query = query.group_by(highq.c.arch_string)
121 # all architectures for which source has a newer binary in lowersuite
122 archs_newer = set(x[0] for x in query.all())
124 # if has at least one binary in lowersuite which is newer than the one
125 # in highersuite on each architecture for which source has binaries in
126 # highersuite, we know that the builds for all relevant architecture
127 # are done, so we can remove the old source with it's binaries
128 if (archs_newer >= archs_high):
129 list.append((source, higherversion, lowerversion))
131 list.sort()
132 return list
135def get_package_names(suite: Suite):
136 '''
137 Returns a query that selects all distinct package names from suite ordered
138 by package name.
139 '''
141 session = object_session(suite)
142 return session.query(DBBinary.package).with_parent(suite). \
143 group_by(DBBinary.package).order_by(DBBinary.package)
146class NamedSource:
147 '''
148 A source package identified by its name with all of its versions in a
149 suite.
150 '''
152 def __init__(self, suite: Suite, source: str):
153 self.source = source
154 query = suite.sources.filter_by(source=source). \
155 order_by(DBSource.version)
156 self.versions = [src.version for src in query]
158 def __str__(self):
159 return "%s(%s)" % (self.source, ", ".join(self.versions))
162class DejavuBinary:
163 '''
164 A binary package identified by its name which gets built by multiple source
165 packages in a suite. The architecture is ignored which leads to the
166 following corner case, e.g.:
168 If a source package 'foo-mips' that builds a binary package 'foo' on mips
169 and another source package 'foo-mipsel' builds a binary package with the
170 same name 'foo' on mipsel then the binary package 'foo' will be reported as
171 built from multiple source packages.
172 '''
174 def __init__(self, suite: Suite, package: str):
175 self.package = package
176 session = object_session(suite)
177 # We need a subquery to make sure that both binary and source packages
178 # are in the right suite.
179 bin_query = suite.binaries.filter_by(package=package).subquery()
180 src_query = session.query(DBSource.source).with_parent(suite). \
181 join(bin_query).order_by(DBSource.source).group_by(DBSource.source)
182 self.sources = []
183 if src_query.count() > 1:
184 for source, in src_query:
185 self.sources.append(str(NamedSource(suite, source)))
187 def has_multiple_sources(self) -> bool:
188 'Has the package been built by multiple sources?'
189 return len(self.sources) > 1
191 def __str__(self):
192 return "%s built by: %s" % (self.package, ", ".join(self.sources))
195def report_multiple_source(suite: Suite) -> None:
196 '''
197 Reports binary packages built from multiple source package with different
198 names.
199 '''
201 print("Built from multiple source packages")
202 print("-----------------------------------")
203 print()
204 for package, in get_package_names(suite):
205 binary = DejavuBinary(suite, package)
206 if binary.has_multiple_sources():
207 print(binary)
208 print()
211def query_without_source(suite_id: int, session):
212 """searches for arch: all packages from suite that do no longer
213 reference a source package in the same suite
215 subquery unique_binaries: selects all packages with only 1 version
216 in suite since 'dak rm' does not allow to specify version numbers"""
218 query = """
219 with unique_binaries as
220 (select package, max(version) as version, max(source) as source
221 from bin_associations_binaries
222 where architecture = 2 and suite = :suite_id
223 group by package having count(*) = 1)
224 select ub.package, ub.version
225 from unique_binaries ub
226 left join src_associations_src sas
227 on ub.source = sas.src and sas.suite = :suite_id
228 where sas.id is null
229 order by ub.package"""
230 return session.execute(query, {'suite_id': suite_id})
233def queryNBS(suite_id: int, session):
234 """This one is really complex. It searches arch != all packages that
235 are no longer built from current source packages in suite.
237 temp table unique_binaries: will be populated with packages that
238 have only one version in suite because 'dak rm' does not allow
239 specifying version numbers
241 temp table newest_binaries: will be populated with packages that are
242 built from current sources
244 subquery uptodate_arch: returns all architectures built from current
245 sources
247 subquery unique_binaries_uptodate_arch: returns all packages in
248 architectures from uptodate_arch
250 subquery unique_binaries_uptodate_arch_agg: same as
251 unique_binaries_uptodate_arch but with column architecture
252 aggregated to array
254 subquery uptodate_packages: similar to uptodate_arch but returns all
255 packages built from current sources
257 subquery outdated_packages: returns all packages with architectures
258 no longer built from current source
259 """
261 query = """
262with
263 unique_binaries as
264 (select
265 bab.package,
266 bab.architecture,
267 max(bab.source) as source
268 from bin_associations_binaries bab
269 where bab.suite = :suite_id and bab.architecture > 2
270 group by package, architecture having count(*) = 1),
271 newest_binaries as
272 (select ub.package, ub.architecture, nsa.source, nsa.version
273 from unique_binaries ub
274 join newest_src_association nsa
275 on ub.source = nsa.src and nsa.suite = :suite_id),
276 uptodate_arch as
277 (select architecture, source, version
278 from newest_binaries
279 group by architecture, source, version),
280 unique_binaries_uptodate_arch as
281 (select ub.package, (select a.arch_string from architecture a where a.id = ub.architecture) as arch_string, ua.source, ua.version
282 from unique_binaries ub
283 join source s
284 on ub.source = s.id
285 join uptodate_arch ua
286 on ub.architecture = ua.architecture and s.source = ua.source),
287 unique_binaries_uptodate_arch_agg as
288 (select ubua.package,
289 array_agg(ubua.arch_string order by ubua.arch_string) as arch_list,
290 ubua.source, ubua.version
291 from unique_binaries_uptodate_arch ubua
292 group by ubua.source, ubua.version, ubua.package),
293 uptodate_packages as
294 (select package, source, version
295 from newest_binaries
296 group by package, source, version),
297 outdated_packages as
298 (select array_agg(package order by package) as pkg_list,
299 arch_list, source, version
300 from unique_binaries_uptodate_arch_agg
301 where package not in
302 (select package from uptodate_packages)
303 group by arch_list, source, version)
304 select * from outdated_packages order by source"""
305 return session.execute(query, {'suite_id': suite_id})
308def queryNBS_metadata(suite_id: int, session):
309 """searches for NBS packages based on metadata extraction of the
310 newest source for a given suite"""
312 query = """
313 select string_agg(bin.package, ' ' order by bin.package), (
314 select arch_string
315 from architecture
316 where id = bin.architecture) as architecture, src.source, newsrc.version
317 from bin_associations_binaries bin
318 join src_associations_src src
319 on src.src = bin.source
320 and src.suite = bin.suite
321 join newest_src_association newsrc
322 on newsrc.source = src.source
323 and newsrc.version != src.version
324 and newsrc.suite = bin.suite
325 where bin.suite = :suite_id
326 and bin.package not in (
327 select trim(' \n' from unnest(string_to_array(meta.value, ',')))
328 from source_metadata meta
329 where meta.src_id = (
330 select newsrc.src
331 from newest_src_association newsrc
332 where newsrc.source = (
333 select s.source
334 from source s
335 where s.id = bin.source)
336 and newsrc.suite = bin.suite)
337 and key_id = (
338 select key_id
339 from metadata_keys
340 where key = 'Binary'))
341 group by src.source, newsrc.version, architecture
342 order by src.source, newsrc.version, bin.architecture"""
343 return session.execute(query, {'suite_id': suite_id})