[dak/master] Move two queries from cruft_report to daklib/cruft
Signed-off-by: Niels Thykier <niels@thykier.net>
---
dak/cruft_report.py | 105 +-------------------------------------------------
daklib/cruft.py | 108 ++++++++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 109 insertions(+), 104 deletions(-)
diff --git a/dak/cruft_report.py b/dak/cruft_report.py
index a960a68..5c6ee8d 100755
--- a/dak/cruft_report.py
+++ b/dak/cruft_report.py
@@ -209,29 +209,9 @@ def do_newer_version(lowersuite_name, highersuite_name, code, session):
################################################################################
-def queryWithoutSource(suite_id, session):
- """searches for arch: all packages from suite that do no longer
- reference a source package in the same suite
-
- subquery unique_binaries: selects all packages with only 1 version
- in suite since 'dak rm' does not allow to specify version numbers"""
-
- query = """
- with unique_binaries as
- (select package, max(version) as version, max(source) as source
- from bin_associations_binaries
- where architecture = 2 and suite = :suite_id
- group by package having count(*) = 1)
- select ub.package, ub.version
- from unique_binaries ub
- left join src_associations_src sas
- on ub.source = sas.src and sas.suite = :suite_id
- where sas.id is null
- order by ub.package"""
- return session.execute(query, { 'suite_id': suite_id })
def reportWithoutSource(suite_name, suite_id, session, rdeps=False):
- rows = queryWithoutSource(suite_id, session)
+ rows = query_without_source(suite_id, session)
title = 'packages without source in suite %s' % suite_name
if rows.rowcount > 0:
print '%s\n%s\n' % (title, '-' * len(title))
@@ -284,90 +264,7 @@ def reportNewerAll(suite_name, session):
print " dak rm -m %s -s %s -a %s -p -b %s\n" % \
(message, suite_name, oldarch, package)
-def queryNBS(suite_id, session):
- """This one is really complex. It searches arch != all packages that
- are no longer built from current source packages in suite.
-
- temp table unique_binaries: will be populated with packages that
- have only one version in suite because 'dak rm' does not allow
- specifying version numbers
-
- temp table newest_binaries: will be populated with packages that are
- built from current sources
-
- subquery uptodate_arch: returns all architectures built from current
- sources
- subquery unique_binaries_uptodate_arch: returns all packages in
- architectures from uptodate_arch
-
- subquery unique_binaries_uptodate_arch_agg: same as
- unique_binaries_uptodate_arch but with column architecture
- aggregated to array
-
- subquery uptodate_packages: similar to uptodate_arch but returns all
- packages built from current sources
-
- subquery outdated_packages: returns all packages with architectures
- no longer built from current source
- """
-
- 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, rdeps=False):
session = DBConn().session()
diff --git a/daklib/cruft.py b/daklib/cruft.py
index a685bcb..05666ce 100644
--- a/daklib/cruft.py
+++ b/daklib/cruft.py
@@ -121,3 +121,111 @@ def report_multiple_source(suite):
if binary.has_multiple_sources():
print binary
print
+
+
+def query_without_source(suite_id, session):
+ """searches for arch: all packages from suite that do no longer
+ reference a source package in the same suite
+
+ subquery unique_binaries: selects all packages with only 1 version
+ in suite since 'dak rm' does not allow to specify version numbers"""
+
+ query = """
+ with unique_binaries as
+ (select package, max(version) as version, max(source) as source
+ from bin_associations_binaries
+ where architecture = 2 and suite = :suite_id
+ group by package having count(*) = 1)
+ select ub.package, ub.version
+ from unique_binaries ub
+ left join src_associations_src sas
+ on ub.source = sas.src and sas.suite = :suite_id
+ where sas.id is null
+ order by ub.package"""
+ return session.execute(query, {'suite_id': suite_id})
+
+
+def queryNBS(suite_id, session):
+ """This one is really complex. It searches arch != all packages that
+ are no longer built from current source packages in suite.
+
+ temp table unique_binaries: will be populated with packages that
+ have only one version in suite because 'dak rm' does not allow
+ specifying version numbers
+
+ temp table newest_binaries: will be populated with packages that are
+ built from current sources
+
+ subquery uptodate_arch: returns all architectures built from current
+ sources
+
+ subquery unique_binaries_uptodate_arch: returns all packages in
+ architectures from uptodate_arch
+
+ subquery unique_binaries_uptodate_arch_agg: same as
+ unique_binaries_uptodate_arch but with column architecture
+ aggregated to array
+
+ subquery uptodate_packages: similar to uptodate_arch but returns all
+ packages built from current sources
+
+ subquery outdated_packages: returns all packages with architectures
+ no longer built from current source
+ """
+
+ 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})
--
2.1.4
Reply to: