+ query = """
+create temp table unique_binaries (
+ package text not null,
+ architecture integer not null,
+ source integer not null);
+
+insert into unique_binaries
+ select bab.package, bab.architecture, max(bab.source)
+ from bin_associations_binaries bab
+ where bab.suite = :suite_id and bab.architecture > 2
+ group by package, architecture having count(*) = 1;
+
+create temp table newest_binaries (
+ package text not null,
+ architecture integer not null,
+ source text not null,
+ version debversion not null);
+
+insert into newest_binaries
+ select ub.package, ub.architecture, nsa.source, nsa.version
+ from unique_binaries ub
+ join newest_src_association nsa
+ on ub.source = nsa.src and nsa.suite = :suite_id;
+
+with uptodate_arch as
+ (select architecture, source, version
+ from newest_binaries
+ group by architecture, source, version),
+ unique_binaries_uptodate_arch as
+ (select ub.package, ub.architecture, ua.source, ua.version
+ from unique_binaries ub
+ join source s
+ on ub.source = s.id
+ join uptodate_arch ua
+ on ub.architecture = ua.architecture and s.source = ua.source),
+ unique_binaries_uptodate_arch_agg as
+ (select ubua.package,
+ array(select unnest(array_agg(a.arch_string)) order by 1) as arch_list,
+ ubua.source, ubua.version
+ from unique_binaries_uptodate_arch ubua
+ join architecture a
+ on ubua.architecture = a.id
+ group by ubua.source, ubua.version, ubua.package),
+ uptodate_packages as
+ (select package, source, version
+ from newest_binaries
+ group by package, source, version),
+ outdated_packages as
+ (select array(select unnest(array_agg(package)) order by 1) as pkg_list,
+ arch_list, source, version
+ from unique_binaries_uptodate_arch_agg
+ where package not in
+ (select package from uptodate_packages)
+ group by arch_list, source, version)
+ select * from outdated_packages order by source"""
+ return session.execute(query, { 'suite_id': suite_id })
+
+def reportNBS(suite_name, suite_id):
+ session = DBConn().session()
+ nbsRows = queryNBS(suite_id, session)
+ title = 'NBS packages in suite %s' % suite_name
+ if nbsRows.rowcount > 0:
+ print '%s\n%s\n' % (title, '-' * len(title))
+ for row in nbsRows:
+ (pkg_list, arch_list, source, version) = row
+ pkg_string = ' '.join(pkg_list)
+ arch_string = ','.join(arch_list)
+ print "* source package %s version %s no longer builds" % \
+ (source, version)
+ print " binary package(s): %s" % pkg_string
+ print " on %s" % arch_string
+ print " - suggested command:"
+ message = '"[auto-cruft] NBS (no longer built by %s)"' % source
+ print " dak rm -m %s -s %s -a %s -p -R -b %s\n" % \
+ (message, suite_name, arch_string, pkg_string)
+ session.close()
+
+def reportAllNBS(suite_name, suite_id, session):
+ reportWithoutSource(suite_name, suite_id, session)
+ reportNewerAll(suite_name, session)
+ reportNBS(suite_name, suite_id)