From: Andreas Barth Date: Sun, 1 May 2011 16:19:50 +0000 (+0000) Subject: add view on built/uploaded packages for distribution_architectures X-Git-Url: https://git.donarmstrong.com/?p=wannabuild.git;a=commitdiff_plain;h=205007d2dff662e0f85c915f09c214973596b44f add view on built/uploaded packages for distribution_architectures --- diff --git a/schema/wanna-build.sql b/schema/wanna-build.sql index e0facd6..6722a32 100644 --- a/schema/wanna-build.sql +++ b/schema/wanna-build.sql @@ -2654,7 +2654,7 @@ SET search_path = public, pg_catalog; -- CREATE VIEW distribution_architectures AS - (((((((((((((SELECT DISTINCT packages.distribution, 'alpha'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM alpha.packages GROUP BY packages.distribution UNION SELECT DISTINCT packages.distribution, 'amd64'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM amd64.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'arm'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM arm.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'armel'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM armel.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'hppa'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM hppa.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'hurd-i386'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM "hurd-i386".packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'i386'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM i386.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'ia64'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM ia64.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'kfreebsd-amd64'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM "kfreebsd-amd64".packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'kfreebsd-i386'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM "kfreebsd-i386".packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'mips'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM mips.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'mipsel'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM mipsel.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'powerpc'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM powerpc.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 's390'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM s390.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'sparc'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild FROM sparc.packages GROUP BY packages.distribution; + (((((((((((((SELECT DISTINCT packages.distribution, 'alpha'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM alpha.packages GROUP BY packages.distribution UNION SELECT DISTINCT packages.distribution, 'amd64'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM amd64.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'arm'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM arm.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'armel'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM armel.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'hppa'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM hppa.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'hurd-i386'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM "hurd-i386".packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'i386'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM i386.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'ia64'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM ia64.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'kfreebsd-amd64'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM "kfreebsd-amd64".packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'kfreebsd-i386'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM "kfreebsd-i386".packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'mips'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM mips.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'mipsel'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM mipsel.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'powerpc'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM powerpc.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 's390'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM s390.packages GROUP BY packages.distribution) UNION SELECT DISTINCT packages.distribution, 'sparc'::character varying AS architecture, count(CASE WHEN ((packages.state)::text = 'Needs-Build'::text) THEN 1 ELSE NULL::integer END) AS needsbuild, count(CASE WHEN state_change < now() - interval '1 day' and ( packages.state::text = 'Built'::text or packages.state = 'Uploaded') THEN 1 ELSE NULL::integer END) AS notinstalled FROM sparc.packages GROUP BY packages.distribution; ALTER TABLE public.distribution_architectures OWNER TO wbadm;