[dak/master 2/2] do not create new db views just improve older ones
Signed-off-by: Torsten Werner <twerner@debian.org>
---
dak/cruft_report.py | 54 +++++++++++++++++++++++++++++++++---------------
dak/dakdb/update27.py | 51 ----------------------------------------------
2 files changed, 37 insertions(+), 68 deletions(-)
diff --git a/dak/cruft_report.py b/dak/cruft_report.py
index 384ad0e..ef9362f 100755
--- a/dak/cruft_report.py
+++ b/dak/cruft_report.py
@@ -273,38 +273,58 @@ def do_dubious_nbs(dubious_nbs):
################################################################################
def obsolete_source(suite_name, session):
- """returns obsolete source packages for suite_name sorted by
- install_date"""
+ """returns obsolete source packages for suite_name without binaries
+ in the same suite sorted by install_date; install_date should help
+ detecting source only (or binary throw away) uploads; duplicates in
+ the suite are skipped
+
+ subquery 'source_suite_unique' returns source package names from
+ suite without duplicates; the rationale behind is that neither
+ cruft-report nor rm cannot handle duplicates (yet)"""
query = """
-SELECT os.src, os.source, os.version, os.install_date
- FROM obsolete_source os
- JOIN suite s on s.id = os.suite
- WHERE s.suite_name = :suite_name
+WITH source_suite_unique AS
+ (SELECT source, suite
+ FROM source_suite GROUP BY source, suite HAVING count(*) = 1)
+SELECT ss.src, ss.source, ss.version,
+ to_char(ss.install_date, 'YYYY-MM-DD') AS install_date
+ FROM source_suite ss
+ JOIN source_suite_unique ssu
+ ON ss.source = ssu.source AND ss.suite = ssu.suite
+ JOIN suite s ON s.id = ss.suite
+ LEFT JOIN bin_associations_binaries bab
+ ON ss.src = bab.source AND ss.suite = bab.suite
+ WHERE s.suite_name = :suite_name AND bab.id IS NULL
ORDER BY install_date"""
args = { 'suite_name': suite_name }
return session.execute(query, args)
def source_bin(source, session):
- """returns binaries built by source for all or no suite"""
+ """returns binaries built by source for all or no suite grouped and
+ ordered by package name"""
query = """
-SELECT package
- FROM source_bin
- WHERE source = :source
- ORDER BY package"""
+SELECT b.package
+ FROM binaries b
+ JOIN src_associations_src sas ON b.source = sas.src
+ WHERE sas.source = :source
+ GROUP BY b.package
+ ORDER BY b.package"""
args = { 'source': source }
return session.execute(query, args)
def newest_source_bab(suite_name, package, session):
- """returns newest source that builds binary package in suite"""
+ """returns newest source that builds binary package in suite grouped
+ and sorted by source and package name"""
query = """
-SELECT source, srcver
- FROM newest_source_bab nsb
- JOIN suite s on s.id = nsb.suite
- WHERE s.suite_name = :suite_name AND nsb.package = :package
- ORDER BY source"""
+SELECT sas.source, MAX(sas.version) AS srcver
+ FROM src_associations_src sas
+ JOIN bin_associations_binaries bab ON sas.src = bab.source
+ JOIN suite s on s.id = bab.suite
+ WHERE s.suite_name = :suite_name AND bab.package = :package
+ GROUP BY sas.source, bab.package
+ ORDER BY sas.source, bab.package"""
args = { 'suite_name': suite_name, 'package': package }
return session.execute(query, args)
diff --git a/dak/dakdb/update27.py b/dak/dakdb/update27.py
index ee8f584..b06932b 100755
--- a/dak/dakdb/update27.py
+++ b/dak/dakdb/update27.py
@@ -50,53 +50,6 @@ CREATE OR REPLACE VIEW bin_associations_binaries AS
JOIN binaries ON bin_associations.bin = binaries.id;
""")
- print "Drop old views."
- c.execute("DROP VIEW IF EXISTS source_suite_unique CASCADE")
- c.execute("DROP VIEW IF EXISTS obsolete_source CASCADE")
- c.execute("DROP VIEW IF EXISTS source_bin CASCADE")
- c.execute("DROP VIEW IF EXISTS newest_source_bab CASCADE")
-
- print "Create new views."
- # returns source package names from suite without duplicates;
- # rationale: cruft-report and rm cannot handle duplicates (yet)
- c.execute("""
-CREATE VIEW source_suite_unique
- AS SELECT source, suite
- FROM source_suite GROUP BY source, suite HAVING count(*) = 1;
- """)
- # returns obsolete sources without binaries in the same suite;
- # outputs install_date to detect source only (or binary throw away)
- # uploads; duplicates are skipped
- c.execute("""
-CREATE VIEW obsolete_source
- AS SELECT ss.src, ss.source, ss.version, ss.suite,
- to_char(ss.install_date, 'YYYY-MM-DD') AS install_date
- FROM source_suite ss
- JOIN source_suite_unique ssu
- ON ss.source = ssu.source AND ss.suite = ssu.suite
- LEFT JOIN bin_associations_binaries bab
- ON ss.src = bab.source AND ss.suite = bab.suite
- WHERE bab.id IS NULL;
- """)
- # returns source package names and its binaries from any suite
- c.execute("""
-CREATE VIEW source_bin
- AS SELECT b.package, MAX(b.version) AS version, sas.source
- FROM binaries b
- JOIN src_associations_src sas
- ON b.source = sas.src
- GROUP BY b.package, sas.source
- """)
- # returns binaries from suite and their source with max(version)
- # grouped by source name, binary name, and suite
- c.execute("""
-CREATE VIEW newest_source_bab
- AS SELECT sas.source, MAX(sas.version) AS srcver, bab.package, bab.suite
- FROM src_associations_src sas
- JOIN bin_associations_binaries bab ON sas.src = bab.source
- GROUP BY sas.source, bab.package, bab.suite;
- """)
-
print "Grant permissions to views."
c.execute("GRANT SELECT ON binfiles_suite_component_arch TO PUBLIC;");
c.execute("GRANT SELECT ON srcfiles_suite_component TO PUBLIC;");
@@ -116,10 +69,6 @@ CREATE VIEW newest_source_bab
c.execute("GRANT SELECT ON src_associations_bin TO PUBLIC;");
c.execute("GRANT SELECT ON almost_obsolete_all_associations TO PUBLIC;");
c.execute("GRANT SELECT ON obsolete_all_associations TO PUBLIC;");
- c.execute("GRANT SELECT ON source_suite_unique TO PUBLIC;");
- c.execute("GRANT SELECT ON obsolete_source TO PUBLIC;");
- c.execute("GRANT SELECT ON source_bin TO PUBLIC;");
- c.execute("GRANT SELECT ON newest_source_bab TO PUBLIC;");
print "Committing"
c.execute("UPDATE config SET value = '27' WHERE name = 'db_revision'")
--
1.6.3.3
Reply to: