From 08522d77db6c4df0f7241676d765cdcf9e2da5b3 Mon Sep 17 00:00:00 2001 From: Torsten Werner Date: Sat, 28 Nov 2009 12:44:23 +0100 Subject: [PATCH] do not create new db views just improve older ones Signed-off-by: Torsten Werner --- 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 384ad0e7..ef9362fd 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 ee8f5844..b06932b5 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'") -- 2.39.5