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 sqlalchemy import func
25from sqlalchemy.orm import aliased, object_session
27from daklib.dbconn import Architecture, DBBinary, DBSource, Suite, get_suite
30def newer_version(
31 lowersuite_name: str, highersuite_name: str, session, include_equal=False
32) -> list[tuple[str, str, str]]:
33 """
34 Finds newer versions in lowersuite_name than in highersuite_name. Returns a
35 list of tuples (source, higherversion, lowerversion) where higherversion is
36 the newest version from highersuite_name and lowerversion is the newest
37 version from lowersuite_name.
38 """
40 lowersuite = get_suite(lowersuite_name, session)
41 highersuite = get_suite(highersuite_name, session)
43 def get_suite_sources(suite):
44 q1 = (
45 session.query(DBSource.source, func.max(DBSource.version).label("version"))
46 .with_parent(suite)
47 .group_by(DBSource.source)
48 .subquery()
49 )
51 return aliased(q1)
53 def get_suite_binaries(suite):
54 q1 = (
55 session.query(
56 DBBinary.package,
57 DBSource.source,
58 func.max(DBSource.version).label("version"),
59 Architecture.arch_string,
60 func.max(DBBinary.version).label("binversion"),
61 )
62 .filter(DBBinary.suites.contains(suite))
63 .join(DBBinary.source)
64 .join(DBBinary.architecture)
65 .group_by(
66 DBBinary.package,
67 DBSource.source,
68 Architecture.arch_string,
69 )
70 .subquery()
71 )
73 return aliased(q1)
75 highq = get_suite_sources(highersuite)
76 lowq = get_suite_sources(lowersuite)
78 query = session.query(
79 highq.c.source,
80 highq.c.version.label("higherversion"),
81 lowq.c.version.label("lowerversion"),
82 ).join(lowq, highq.c.source == lowq.c.source)
84 if include_equal:
85 query = query.filter(highq.c.version <= lowq.c.version)
86 else:
87 query = query.filter(highq.c.version < lowq.c.version)
89 list = []
90 # get all sources that have a higher version in lowersuite than in
91 # highersuite
92 for source, higherversion, lowerversion in query:
93 q1 = (
94 session.query(
95 DBBinary.package,
96 DBSource.source,
97 DBSource.version,
98 Architecture.arch_string,
99 )
100 .filter(DBBinary.suites.contains(highersuite))
101 .join(DBBinary.source)
102 .join(DBBinary.architecture)
103 .filter(DBSource.source == source)
104 .subquery()
105 )
106 q2 = session.query(q1.c.arch_string).group_by(q1.c.arch_string)
107 # all architectures for which source has binaries in highersuite
108 archs_high = set(x[0] for x in q2.all())
110 highq = get_suite_binaries(highersuite)
111 lowq = get_suite_binaries(lowersuite)
113 query = (
114 session.query(highq.c.arch_string)
115 .join(lowq, highq.c.source == lowq.c.source)
116 .filter(highq.c.arch_string == lowq.c.arch_string)
117 .filter(highq.c.package == lowq.c.package)
118 .filter(highq.c.source == source)
119 )
121 if include_equal:
122 query = query.filter(highq.c.binversion <= lowq.c.binversion).filter(
123 highq.c.version <= lowq.c.version
124 )
125 else:
126 query = query.filter(highq.c.binversion < lowq.c.binversion).filter(
127 highq.c.version < lowq.c.version
128 )
130 query = query.group_by(highq.c.arch_string)
132 # all architectures for which source has a newer binary in lowersuite
133 archs_newer = set(x[0] for x in query.all())
135 # if has at least one binary in lowersuite which is newer than the one
136 # in highersuite on each architecture for which source has binaries in
137 # highersuite, we know that the builds for all relevant architecture
138 # are done, so we can remove the old source with it's binaries
139 if archs_newer >= archs_high:
140 list.append((source, higherversion, lowerversion))
142 list.sort()
143 return list
146def get_package_names(suite: Suite):
147 """
148 Returns a query that selects all distinct package names from suite ordered
149 by package name.
150 """
152 session = object_session(suite)
153 return (
154 session.query(DBBinary.package)
155 .with_parent(suite)
156 .group_by(DBBinary.package)
157 .order_by(DBBinary.package)
158 )
161class NamedSource:
162 """
163 A source package identified by its name with all of its versions in a
164 suite.
165 """
167 def __init__(self, suite: Suite, source: str):
168 self.source = source
169 query = suite.sources.filter_by(source=source).order_by(DBSource.version)
170 self.versions = [src.version for src in query]
172 def __str__(self):
173 return "%s(%s)" % (self.source, ", ".join(self.versions))
176class DejavuBinary:
177 """
178 A binary package identified by its name which gets built by multiple source
179 packages in a suite. The architecture is ignored which leads to the
180 following corner case, e.g.:
182 If a source package 'foo-mips' that builds a binary package 'foo' on mips
183 and another source package 'foo-mipsel' builds a binary package with the
184 same name 'foo' on mipsel then the binary package 'foo' will be reported as
185 built from multiple source packages.
186 """
188 def __init__(self, suite: Suite, package: str):
189 self.package = package
190 session = object_session(suite)
191 # We need a subquery to make sure that both binary and source packages
192 # are in the right suite.
193 bin_query = suite.binaries.filter_by(package=package).subquery()
194 src_query = (
195 session.query(DBSource.source)
196 .with_parent(suite)
197 .join(bin_query)
198 .order_by(DBSource.source)
199 .group_by(DBSource.source)
200 )
201 self.sources = []
202 if src_query.count() > 1:
203 for (source,) in src_query:
204 self.sources.append(str(NamedSource(suite, source)))
206 def has_multiple_sources(self) -> bool:
207 "Has the package been built by multiple sources?"
208 return len(self.sources) > 1
210 def __str__(self):
211 return "%s built by: %s" % (self.package, ", ".join(self.sources))
214def report_multiple_source(suite: Suite) -> None:
215 """
216 Reports binary packages built from multiple source package with different
217 names.
218 """
220 print("Built from multiple source packages")
221 print("-----------------------------------")
222 print()
223 for (package,) in get_package_names(suite):
224 binary = DejavuBinary(suite, package)
225 if binary.has_multiple_sources():
226 print(binary)
227 print()
230def query_without_source(suite_id: int, session):
231 """searches for arch: all packages from suite that do no longer
232 reference a source package in the same suite
234 subquery unique_binaries: selects all packages with only 1 version
235 in suite since 'dak rm' does not allow to specify version numbers"""
237 query = """
238 with unique_binaries as
239 (select package, max(version) as version, max(source) as source
240 from bin_associations_binaries
241 where architecture = 2 and suite = :suite_id
242 group by package having count(*) = 1)
243 select ub.package, ub.version
244 from unique_binaries ub
245 left join src_associations_src sas
246 on ub.source = sas.src and sas.suite = :suite_id
247 where sas.id is null
248 order by ub.package"""
249 return session.execute(query, {"suite_id": suite_id})
252def queryNBS(suite_id: int, session):
253 """This one is really complex. It searches arch != all packages that
254 are no longer built from current source packages in suite.
256 temp table unique_binaries: will be populated with packages that
257 have only one version in suite because 'dak rm' does not allow
258 specifying version numbers
260 temp table newest_binaries: will be populated with packages that are
261 built from current sources
263 subquery uptodate_arch: returns all architectures built from current
264 sources
266 subquery unique_binaries_uptodate_arch: returns all packages in
267 architectures from uptodate_arch
269 subquery unique_binaries_uptodate_arch_agg: same as
270 unique_binaries_uptodate_arch but with column architecture
271 aggregated to array
273 subquery uptodate_packages: similar to uptodate_arch but returns all
274 packages built from current sources
276 subquery outdated_packages: returns all packages with architectures
277 no longer built from current source
278 """
280 query = """
281with
282 unique_binaries as
283 (select
284 bab.package,
285 bab.architecture,
286 max(bab.source) as source
287 from bin_associations_binaries bab
288 where bab.suite = :suite_id and bab.architecture > 2
289 group by package, architecture having count(*) = 1),
290 newest_binaries as
291 (select ub.package, ub.architecture, nsa.source, nsa.version
292 from unique_binaries ub
293 join newest_src_association nsa
294 on ub.source = nsa.src and nsa.suite = :suite_id),
295 uptodate_arch as
296 (select architecture, source, version
297 from newest_binaries
298 group by architecture, source, version),
299 unique_binaries_uptodate_arch as
300 (select ub.package, (select a.arch_string from architecture a where a.id = ub.architecture) as arch_string, ua.source, ua.version
301 from unique_binaries ub
302 join source s
303 on ub.source = s.id
304 join uptodate_arch ua
305 on ub.architecture = ua.architecture and s.source = ua.source),
306 unique_binaries_uptodate_arch_agg as
307 (select ubua.package,
308 array_agg(ubua.arch_string order by ubua.arch_string) as arch_list,
309 ubua.source, ubua.version
310 from unique_binaries_uptodate_arch ubua
311 group by ubua.source, ubua.version, ubua.package),
312 uptodate_packages as
313 (select package, source, version
314 from newest_binaries
315 group by package, source, version),
316 outdated_packages as
317 (select array_agg(package order by package) as pkg_list,
318 arch_list, source, version
319 from unique_binaries_uptodate_arch_agg
320 where package not in
321 (select package from uptodate_packages)
322 group by arch_list, source, version)
323 select * from outdated_packages order by source"""
324 return session.execute(query, {"suite_id": suite_id})
327def queryNBS_metadata(suite_id: int, session):
328 """searches for NBS packages based on metadata extraction of the
329 newest source for a given suite"""
331 query = """
332 select string_agg(bin.package, ' ' order by bin.package), (
333 select arch_string
334 from architecture
335 where id = bin.architecture) as architecture, src.source, newsrc.version
336 from bin_associations_binaries bin
337 join src_associations_src src
338 on src.src = bin.source
339 and src.suite = bin.suite
340 join newest_src_association newsrc
341 on newsrc.source = src.source
342 and newsrc.version != src.version
343 and newsrc.suite = bin.suite
344 where bin.suite = :suite_id
345 and bin.package not in (
346 select trim(' \n' from unnest(string_to_array(meta.value, ',')))
347 from source_metadata meta
348 where meta.src_id = (
349 select newsrc.src
350 from newest_src_association newsrc
351 where newsrc.source = (
352 select s.source
353 from source s
354 where s.id = bin.source)
355 and newsrc.suite = bin.suite)
356 and key_id = (
357 select key_id
358 from metadata_keys
359 where key = 'Binary'))
360 group by src.source, newsrc.version, architecture
361 order by src.source, newsrc.version, bin.architecture"""
362 return session.execute(query, {"suite_id": suite_id})