X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=schema%2Fwanna-build.sql;h=ba6b0f58df0efa30467e5ed6136fa602614208dc;hb=c7027081dd29d75e2aaa237ed5ea7f1b42f15880;hp=e0facd697761acf3474f14302d577485f9c9505d;hpb=9aecc4a368553ae826bf36f75732b2f22e47ec3c;p=wannabuild.git

diff --git a/schema/wanna-build.sql b/schema/wanna-build.sql
index e0facd6..ba6b0f5 100644
--- a/schema/wanna-build.sql
+++ b/schema/wanna-build.sql
@@ -957,7 +957,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -1084,7 +1085,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -1211,7 +1213,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -1338,7 +1341,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -1465,7 +1469,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -1592,7 +1597,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -1719,7 +1725,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -1846,7 +1853,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -1973,7 +1981,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -2100,7 +2109,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -2227,7 +2237,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -2354,7 +2365,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -2481,7 +2493,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -2654,11 +2667,21 @@ 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) 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 ((packages.state_change < (now() - '1 day'::interval)) AND (((packages.state)::text = 'Built'::text) OR ((packages.state)::text = 'Uploaded'::text))) THEN 1 ELSE NULL::integer END) AS notinstalled FROM sparc.packages GROUP BY packages.distribution;
 
 
 ALTER TABLE public.distribution_architectures OWNER TO wbadm;
 
+--
+-- Name: distribution_architectures_statistics; Type: VIEW; Schema: public; Owner: wbadm
+--
+
+CREATE VIEW distribution_architectures_statistics AS
+    SELECT distribution_architectures.distribution, distribution_architectures.architecture, distribution_architectures.needsbuild, distribution_architectures.notinstalled FROM distribution_architectures;
+
+
+ALTER TABLE public.distribution_architectures_statistics OWNER TO wbadm;
+
 --
 -- Name: distributions; Type: TABLE; Schema: public; Owner: wbadm; Tablespace: 
 --
@@ -2689,7 +2712,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -2720,7 +2744,8 @@ CREATE TABLE pkg_history (
     "timestamp" timestamp without time zone NOT NULL,
     result character varying NOT NULL,
     build_time integer,
-    disk_space bigint
+    disk_space bigint,
+    builder character varying
 );
 
 
@@ -2760,16 +2785,6 @@ CREATE VIEW log AS
 
 ALTER TABLE public.log OWNER TO wbadm;
 
---
--- Name: log_v2; Type: VIEW; Schema: public; Owner: pkern
---
-
-CREATE VIEW log_v2 AS
-    (((((((((((((SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'alpha'::character varying AS architecture FROM alpha_public.pkg_history UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'amd64'::character varying AS architecture FROM amd64_public.pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'arm'::character varying AS architecture FROM arm_public.pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'armel'::character varying AS architecture FROM armel_public.pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'hppa'::character varying AS architecture FROM hppa_public.pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'hurd-i386'::character varying AS architecture FROM "hurd-i386_public".pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'i386'::character varying AS architecture FROM i386_public.pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'ia64'::character varying AS architecture FROM ia64_public.pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'kfreebsd-amd64'::character varying AS architecture FROM "kfreebsd-amd64_public".pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'kfreebsd-i386'::character varying AS architecture FROM "kfreebsd-i386_public".pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'mips'::character varying AS architecture FROM mips_public.pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'mipsel'::character varying AS architecture FROM mipsel_public.pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'powerpc'::character varying AS architecture FROM powerpc_public.pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 's390'::character varying AS architecture FROM s390_public.pkg_history) UNION ALL SELECT pkg_history.package, pkg_history.distribution, pkg_history.version, pkg_history."timestamp", pkg_history.result, 'sparc'::character varying AS architecture FROM sparc_public.pkg_history ORDER BY 4 DESC;
-
-
-ALTER TABLE public.log_v2 OWNER TO pkern;
-
 SET search_path = s390_public, pg_catalog;
 
 --
@@ -4463,6 +4478,7 @@ REVOKE ALL ON TABLE pkg_history FROM wbadm;
 GRANT ALL ON TABLE pkg_history TO wbadm;
 GRANT ALL ON TABLE pkg_history TO alpha;
 GRANT SELECT ON TABLE pkg_history TO wb_security;
+GRANT SELECT,INSERT,UPDATE ON TABLE pkg_history TO wb_all;
 
 
 --
@@ -5622,6 +5638,16 @@ GRANT ALL ON TABLE distribution_architectures TO wbadm;
 GRANT SELECT ON TABLE distribution_architectures TO PUBLIC;
 
 
+--
+-- Name: distribution_architectures_statistics; Type: ACL; Schema: public; Owner: wbadm
+--
+
+REVOKE ALL ON TABLE distribution_architectures_statistics FROM PUBLIC;
+REVOKE ALL ON TABLE distribution_architectures_statistics FROM wbadm;
+GRANT ALL ON TABLE distribution_architectures_statistics TO wbadm;
+GRANT SELECT ON TABLE distribution_architectures_statistics TO PUBLIC;
+
+
 --
 -- Name: distributions; Type: ACL; Schema: public; Owner: wbadm
 --
@@ -5704,16 +5730,6 @@ GRANT ALL ON TABLE log TO wbadm;
 GRANT SELECT ON TABLE log TO PUBLIC;
 
 
---
--- Name: log_v2; Type: ACL; Schema: public; Owner: pkern
---
-
-REVOKE ALL ON TABLE log_v2 FROM PUBLIC;
-REVOKE ALL ON TABLE log_v2 FROM pkern;
-GRANT ALL ON TABLE log_v2 TO pkern;
-GRANT SELECT ON TABLE log_v2 TO PUBLIC;
-
-
 SET search_path = s390_public, pg_catalog;
 
 --