X-Git-Url: https://git.exim.org/buildfarm-server.git/blobdiff_plain/0640671b1d4a0101ca1ca24cd368566586c70be8..d8818469c070065e44415ede75007d5f3b276ea3:/schema/bfwebdb.sql diff --git a/schema/bfwebdb.sql b/schema/bfwebdb.sql index c2c7042..2e575b0 100644 --- a/schema/bfwebdb.sql +++ b/schema/bfwebdb.sql @@ -144,6 +144,31 @@ $_$ ALTER FUNCTION public.prevstat(text, text, timestamp without time zone) OWNER TO pgbuildfarm; +-- +-- Name: set_latest(); Type: FUNCTION; Schema: public; Owner: pgbuildfarm +-- + +CREATE FUNCTION set_latest() RETURNS "trigger" + AS $$ + + begin + update latest_snapshot + set snapshot = + (case when snapshot > NEW.snapshot then snapshot else NEW.snapshot end) + where sysname = NEW.sysname and + branch = NEW.branch; + if not found then + insert into latest_snapshot + values(NEW.sysname, NEW.branch, NEW.snapshot); + end if; + return NEW; + end; +$$ + LANGUAGE plpgsql; + + +ALTER FUNCTION public.set_latest() OWNER TO pgbuildfarm; + -- -- Name: target(text); Type: FUNCTION; Schema: public; Owner: pgbuildfarm -- @@ -155,6 +180,41 @@ CREATE FUNCTION target(t text) RETURNS text ALTER FUNCTION public.target(t text) OWNER TO pgbuildfarm; +-- +-- Name: transitions(text, text, text, text, text, text); Type: FUNCTION; Schema: public; Owner: pgbuildfarm +-- + +CREATE FUNCTION transitions(text, text, text, text, text, text) RETURNS integer + AS $_$ + +my ($os,$osv,$comp,$compv,$arch,$owner) = @_; +# count transitions to and from upper case +my $trans = 1; +my $counttrans = 0; +foreach (split "" ,"$os$osv$comp$compv$arch$owner") +{ + if (/[A-Z]/) + { + next if $trans; + $trans = 1; + $counttrans++; + } + else + { + next unless $trans; + $trans = 0; + $counttrans++; + } +} + +return $counttrans; + +$_$ + LANGUAGE plperl; + + +ALTER FUNCTION public.transitions(text, text, text, text, text, text) OWNER TO pgbuildfarm; + SET default_tablespace = ''; SET default_with_oids = true; @@ -190,7 +250,9 @@ CREATE TABLE build_status ( log_archive bytea, log_archive_filenames text[], build_flags text[], - report_time timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone + report_time timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone, + scm text, + scmurl text ); @@ -237,7 +299,10 @@ CREATE TABLE buildsystems ( status text NOT NULL, sys_owner text NOT NULL, owner_email text NOT NULL, - status_ts timestamp without time zone DEFAULT (('now'::text)::timestamp(6) with time zone)::timestamp without time zone + status_ts timestamp without time zone DEFAULT (('now'::text)::timestamp(6) with time zone)::timestamp without time zone, + no_alerts boolean DEFAULT false, + sys_notes text, + sys_notes_ts timestamp with time zone ); @@ -253,6 +318,94 @@ CREATE VIEW buildsystems_export AS ALTER TABLE public.buildsystems_export OWNER TO pgbuildfarm; +-- +-- Name: latest_snapshot; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace: +-- + +CREATE TABLE latest_snapshot ( + sysname text NOT NULL, + branch text NOT NULL, + snapshot timestamp without time zone NOT NULL +); + + +ALTER TABLE public.latest_snapshot OWNER TO pgbuildfarm; + +-- +-- Name: personality; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace: +-- + +CREATE TABLE personality ( + name text NOT NULL, + os_version text NOT NULL, + compiler_version text NOT NULL, + effective_date timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL +); + + +ALTER TABLE public.personality OWNER TO pgbuildfarm; + +-- +-- Name: dashboard; Type: VIEW; Schema: public; Owner: pgbuildfarm +-- + +CREATE VIEW dashboard AS + SELECT ((timezone('GMT'::text, now()))::timestamp(0) without time zone - b.snapshot) AS when_ago, b.sysname, b.snapshot, b.status, b.stage, b.branch, b.build_flags, s.operating_system, COALESCE(b.os_version, s.os_version) AS os_version, s.compiler, COALESCE(b.compiler_version, s.compiler_version) AS compiler_version, s.architecture FROM buildsystems s, (SELECT DISTINCT ON (bs.sysname, bs.branch, bs.report_time) bs.sysname, bs.snapshot, bs.status, bs.stage, bs.branch, bs.build_flags, bs.report_time, p.compiler_version, p.os_version FROM ((build_status bs NATURAL JOIN latest_snapshot m) LEFT JOIN personality p ON (((p.name = bs.sysname) AND (p.effective_date <= bs.report_time)))) WHERE (m.snapshot > (now() - '30 days'::interval)) ORDER BY bs.sysname, bs.branch, bs.report_time, (p.effective_date IS NULL), p.effective_date DESC) b WHERE ((s.name = b.sysname) AND (s.status = 'approved'::text)); + + +ALTER TABLE public.dashboard OWNER TO pgbuildfarm; + +-- +-- Name: dashboard_ex; Type: VIEW; Schema: public; Owner: pgbuildfarm +-- + +CREATE VIEW dashboard_ex AS + SELECT ((timezone('GMT'::text, now()))::timestamp(0) without time zone - b.snapshot) AS when_ago, b.sysname, b.snapshot, b.status, b.stage, b.branch, b.build_flags, s.operating_system, COALESCE(b.os_version, s.os_version) AS os_version, s.compiler, COALESCE(b.compiler_version, s.compiler_version) AS compiler_version, s.architecture, s.sys_notes, (s.sys_notes_ts)::date AS sys_notes_date FROM buildsystems s, (SELECT DISTINCT ON (bs.sysname, bs.branch, bs.report_time) bs.sysname, bs.snapshot, bs.status, bs.stage, bs.branch, bs.build_flags, bs.report_time, p.compiler_version, p.os_version FROM ((build_status bs NATURAL JOIN latest_snapshot m) LEFT JOIN personality p ON (((p.name = bs.sysname) AND (p.effective_date <= bs.report_time)))) WHERE (m.snapshot > (now() - '30 days'::interval)) ORDER BY bs.sysname, bs.branch, bs.report_time, (p.effective_date IS NULL), p.effective_date DESC) b WHERE ((s.name = b.sysname) AND (s.status = 'approved'::text)); + + +ALTER TABLE public.dashboard_ex OWNER TO pgbuildfarm; + +-- +-- Name: dashboard_mat; Type: TABLE; Schema: public; Owner: pgbfweb; Tablespace: +-- + +CREATE TABLE dashboard_mat ( + sysname text, + snapshot timestamp without time zone, + status integer, + stage text, + branch text, + build_flags text[], + operating_system text, + os_version text, + compiler text, + compiler_version text, + architecture text +); + + +ALTER TABLE public.dashboard_mat OWNER TO pgbfweb; + +-- +-- Name: dashboard_mat_data; Type: VIEW; Schema: public; Owner: pgbuildfarm +-- + +CREATE VIEW dashboard_mat_data AS + SELECT b.sysname, b.snapshot, b.status, b.stage, b.branch, b.build_flags, s.operating_system, COALESCE(b.os_version, s.os_version) AS os_version, s.compiler, COALESCE(b.compiler_version, s.compiler_version) AS compiler_version, s.architecture FROM buildsystems s, (SELECT DISTINCT ON (bs.sysname, bs.branch, bs.report_time) bs.sysname, bs.snapshot, bs.status, bs.stage, bs.branch, bs.build_flags, bs.report_time, p.compiler_version, p.os_version FROM ((build_status bs NATURAL JOIN latest_snapshot m) LEFT JOIN personality p ON (((p.name = bs.sysname) AND (p.effective_date <= bs.report_time)))) WHERE (m.snapshot > (now() - '30 days'::interval)) ORDER BY bs.sysname, bs.branch, bs.report_time, (p.effective_date IS NULL), p.effective_date DESC) b WHERE ((s.name = b.sysname) AND (s.status = 'approved'::text)); + + +ALTER TABLE public.dashboard_mat_data OWNER TO pgbuildfarm; + +-- +-- Name: dashboard_mat_data2; Type: VIEW; Schema: public; Owner: pgbuildfarm +-- + +CREATE VIEW dashboard_mat_data2 AS + SELECT b.sysname, b.snapshot, b.status, b.stage, b.branch, CASE WHEN ((b.conf_sum ~ 'use_vpath'::text) AND (b.conf_sum !~ '''use_vpath'' => undef'::text)) THEN (b.build_flags || 'vpath'::text) ELSE b.build_flags END AS build_flags, s.operating_system, COALESCE(b.os_version, s.os_version) AS os_version, s.compiler, COALESCE(b.compiler_version, s.compiler_version) AS compiler_version, s.architecture FROM buildsystems s, (SELECT DISTINCT ON (bs.sysname, bs.branch, bs.report_time) bs.sysname, bs.snapshot, bs.status, bs.stage, bs.branch, bs.build_flags, bs.conf_sum, bs.report_time, p.compiler_version, p.os_version FROM ((build_status bs NATURAL JOIN latest_snapshot m) LEFT JOIN personality p ON (((p.name = bs.sysname) AND (p.effective_date <= bs.report_time)))) WHERE (m.snapshot > (now() - '30 days'::interval)) ORDER BY bs.sysname, bs.branch, bs.report_time, (p.effective_date IS NULL), p.effective_date DESC) b WHERE ((s.name = b.sysname) AND (s.status = 'approved'::text)); + + +ALTER TABLE public.dashboard_mat_data2 OWNER TO pgbuildfarm; + -- -- Name: failures; Type: VIEW; Schema: public; Owner: pgbuildfarm -- @@ -288,18 +441,14 @@ CREATE TABLE penguin_save ( ALTER TABLE public.penguin_save OWNER TO pgbuildfarm; -- --- Name: personality; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace: +-- Name: recent_failures; Type: VIEW; Schema: public; Owner: pgbuildfarm -- -CREATE TABLE personality ( - name text NOT NULL, - os_version text NOT NULL, - compiler_version text NOT NULL, - effective_date timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL -); +CREATE VIEW recent_failures AS + SELECT build_status.sysname, build_status.snapshot, build_status.stage, build_status.conf_sum, build_status.branch, build_status.changed_this_run, build_status.changed_since_success, build_status.log_archive_filenames, build_status.build_flags, build_status.report_time, build_status.log FROM build_status WHERE ((((build_status.stage <> 'OK'::text) AND (build_status.stage !~~ 'CVS%'::text)) AND (build_status.report_time IS NOT NULL)) AND ((build_status.snapshot + '3 mons'::interval) > ('now'::text)::timestamp(6) with time zone)); -ALTER TABLE public.personality OWNER TO pgbuildfarm; +ALTER TABLE public.recent_failures OWNER TO pgbuildfarm; -- -- Name: alerts_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace: @@ -341,6 +490,16 @@ ALTER TABLE ONLY buildsystems ALTER INDEX public.buildsystems_pkey OWNER TO pgbuildfarm; +-- +-- Name: latest_snapshot_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace: +-- + +ALTER TABLE ONLY latest_snapshot + ADD CONSTRAINT latest_snapshot_pkey PRIMARY KEY (sysname, branch); + + +ALTER INDEX public.latest_snapshot_pkey OWNER TO pgbuildfarm; + -- -- Name: personality_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace: -- @@ -388,11 +547,22 @@ CREATE INDEX bs_sysname_branch_report_idx ON build_status USING btree (sysname, ALTER INDEX public.bs_sysname_branch_report_idx OWNER TO pgbuildfarm; -- --- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: pgbuildfarm +-- Name: build_status_log_snapshot_idx; Type: INDEX; Schema: public; Owner: pgbuildfarm; Tablespace: -- -ALTER TABLE ONLY personality - ADD CONSTRAINT "$1" FOREIGN KEY (name) REFERENCES buildsystems(name) ON DELETE CASCADE; +CREATE INDEX build_status_log_snapshot_idx ON build_status_log USING btree (snapshot); + + +ALTER INDEX public.build_status_log_snapshot_idx OWNER TO pgbuildfarm; + +-- +-- Name: set_latest_snapshot; Type: TRIGGER; Schema: public; Owner: pgbuildfarm +-- + +CREATE TRIGGER set_latest_snapshot + AFTER INSERT ON build_status + FOR EACH ROW + EXECUTE PROCEDURE set_latest(); -- @@ -408,7 +578,15 @@ ALTER TABLE ONLY build_status -- ALTER TABLE ONLY build_status_log - ADD CONSTRAINT build_status_log_sysname_fkey FOREIGN KEY (sysname, snapshot) REFERENCES build_status(sysname, snapshot) ON DELETE CASCADE; + ADD CONSTRAINT build_status_log_sysname_fkey FOREIGN KEY (sysname, snapshot) REFERENCES build_status(sysname, snapshot) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: personality_build_systems_name_fk; Type: FK CONSTRAINT; Schema: public; Owner: pgbuildfarm +-- + +ALTER TABLE ONLY personality + ADD CONSTRAINT personality_build_systems_name_fk FOREIGN KEY (name) REFERENCES buildsystems(name) ON UPDATE CASCADE ON DELETE CASCADE; -- @@ -450,10 +628,20 @@ GRANT SELECT ON TABLE build_status_log TO rssfeed; REVOKE ALL ON TABLE buildsystems FROM PUBLIC; REVOKE ALL ON TABLE buildsystems FROM pgbuildfarm; GRANT ALL ON TABLE buildsystems TO pgbuildfarm; -GRANT INSERT,SELECT ON TABLE buildsystems TO pgbfweb; +GRANT INSERT,SELECT,UPDATE ON TABLE buildsystems TO pgbfweb; GRANT SELECT ON TABLE buildsystems TO rssfeed; +-- +-- Name: latest_snapshot; Type: ACL; Schema: public; Owner: pgbuildfarm +-- + +REVOKE ALL ON TABLE latest_snapshot FROM PUBLIC; +REVOKE ALL ON TABLE latest_snapshot FROM pgbuildfarm; +GRANT ALL ON TABLE latest_snapshot TO pgbuildfarm; +GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE latest_snapshot TO pgbfweb; + + -- -- Name: personality; Type: ACL; Schema: public; Owner: pgbuildfarm -- @@ -465,6 +653,55 @@ GRANT INSERT,SELECT ON TABLE personality TO pgbfweb; GRANT SELECT ON TABLE personality TO rssfeed; +-- +-- Name: dashboard; Type: ACL; Schema: public; Owner: pgbuildfarm +-- + +REVOKE ALL ON TABLE dashboard FROM PUBLIC; +REVOKE ALL ON TABLE dashboard FROM pgbuildfarm; +GRANT ALL ON TABLE dashboard TO pgbuildfarm; +GRANT SELECT ON TABLE dashboard TO pgbfweb; + + +-- +-- Name: dashboard_ex; Type: ACL; Schema: public; Owner: pgbuildfarm +-- + +REVOKE ALL ON TABLE dashboard_ex FROM PUBLIC; +REVOKE ALL ON TABLE dashboard_ex FROM pgbuildfarm; +GRANT ALL ON TABLE dashboard_ex TO pgbuildfarm; +GRANT SELECT ON TABLE dashboard_ex TO pgbfweb; + + +-- +-- Name: dashboard_mat; Type: ACL; Schema: public; Owner: pgbfweb +-- + +REVOKE ALL ON TABLE dashboard_mat FROM PUBLIC; +REVOKE ALL ON TABLE dashboard_mat FROM pgbfweb; +GRANT ALL ON TABLE dashboard_mat TO pgbfweb; + + +-- +-- Name: dashboard_mat_data; Type: ACL; Schema: public; Owner: pgbuildfarm +-- + +REVOKE ALL ON TABLE dashboard_mat_data FROM PUBLIC; +REVOKE ALL ON TABLE dashboard_mat_data FROM pgbuildfarm; +GRANT ALL ON TABLE dashboard_mat_data TO pgbuildfarm; +GRANT SELECT ON TABLE dashboard_mat_data TO pgbfweb; + + +-- +-- Name: dashboard_mat_data2; Type: ACL; Schema: public; Owner: pgbuildfarm +-- + +REVOKE ALL ON TABLE dashboard_mat_data2 FROM PUBLIC; +REVOKE ALL ON TABLE dashboard_mat_data2 FROM pgbuildfarm; +GRANT ALL ON TABLE dashboard_mat_data2 TO pgbuildfarm; +GRANT SELECT ON TABLE dashboard_mat_data2 TO pgbfweb; + + -- -- PostgreSQL database dump complete --