2 -- PostgreSQL database dump
5 SET statement_timeout = 0;
6 SET client_encoding = 'SQL_ASCII';
7 SET standard_conforming_strings = off;
8 SET check_function_bodies = false;
9 SET client_min_messages = warning;
10 SET escape_string_warning = off;
13 -- Name: plperl; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: eximbuild
16 CREATE PROCEDURAL LANGUAGE plperl;
19 ALTER PROCEDURAL LANGUAGE plperl OWNER TO eximbuild;
22 -- Name: plperlu; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: eximbuild
25 CREATE PROCEDURAL LANGUAGE plperlu;
28 ALTER PROCEDURAL LANGUAGE plperlu OWNER TO eximbuild;
31 -- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: eximbuild
34 CREATE PROCEDURAL LANGUAGE plpgsql;
37 ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO eximbuild;
39 SET search_path = public, pg_catalog;
42 -- Name: pending; Type: TYPE; Schema: public; Owner: eximbuild
45 CREATE TYPE pending AS (
47 operating_system text,
50 compiler_version text,
56 ALTER TYPE public.pending OWNER TO eximbuild;
59 -- Name: pending2; Type: TYPE; Schema: public; Owner: eximbuild
62 CREATE TYPE pending2 AS (
64 operating_system text,
67 compiler_version text,
71 status_ts timestamp without time zone
75 ALTER TYPE public.pending2 OWNER TO eximbuild;
78 -- Name: approve(text, text); Type: FUNCTION; Schema: public; Owner: eximbuild
81 CREATE FUNCTION approve(text, text) RETURNS void
83 AS $_$update buildsystems set name = $2, status ='approved' where name = $1 and status = 'pending'$_$;
86 ALTER FUNCTION public.approve(text, text) OWNER TO eximbuild;
89 -- Name: approve2(text, text); Type: FUNCTION; Schema: public; Owner: eximbuild
92 CREATE FUNCTION approve2(text, text) RETURNS text
94 AS $_$ update buildsystems set name = $2, status = 'approved' where name = $1 and status = 'pending'; select owner_email || ':' || name || ':' || secret from buildsystems where name = $2;$_$;
97 ALTER FUNCTION public.approve2(text, text) OWNER TO eximbuild;
100 -- Name: pending(); Type: FUNCTION; Schema: public; Owner: eximbuild
103 CREATE FUNCTION pending() RETURNS SETOF pending2
105 AS $$select name,operating_system,os_version,compiler,compiler_version,architecture,owner_email, sys_owner, status_ts from buildsystems where status = 'pending' order by status_ts $$;
108 ALTER FUNCTION public.pending() OWNER TO eximbuild;
111 -- Name: plperl_call_handler(); Type: FUNCTION; Schema: public; Owner: eximbuild
114 CREATE FUNCTION plperl_call_handler() RETURNS language_handler
116 AS '$libdir/plperl', 'plperl_call_handler';
119 ALTER FUNCTION public.plperl_call_handler() OWNER TO eximbuild;
122 -- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: eximbuild
125 CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
127 AS '$libdir/plpgsql', 'plpgsql_call_handler';
130 ALTER FUNCTION public.plpgsql_call_handler() OWNER TO eximbuild;
133 -- Name: pregex(text, text, text); Type: FUNCTION; Schema: public; Owner: eximbuild
136 CREATE FUNCTION pregex(text, text, text) RETURNS text
138 AS $_$ my $source = shift; my $pattern = shift; my $repl = shift; my $regex = qr($pattern)i; $source =~ s/$regex/$repl/g; return $source; $_$;
141 ALTER FUNCTION public.pregex(text, text, text) OWNER TO eximbuild;
144 -- Name: prevstat(text, text, timestamp without time zone); Type: FUNCTION; Schema: public; Owner: eximbuild
147 CREATE FUNCTION prevstat(text, text, timestamp without time zone) RETURNS text
150 select coalesce((select distinct on (snapshot) stage
152 where sysname = $1 and branch = $2 and snapshot < $3
153 order by snapshot desc
154 limit 1), 'NEW') as prev_status
158 ALTER FUNCTION public.prevstat(text, text, timestamp without time zone) OWNER TO eximbuild;
161 -- Name: script_version(text); Type: FUNCTION; Schema: public; Owner: eximbuild
164 CREATE FUNCTION script_version(text) RETURNS text
169 if ($log =~ /'script_version' => '(REL_)?(\d+)\.(\d+)'/)
171 return sprintf("%.03d%.03d",$2,$3);
178 ALTER FUNCTION public.script_version(text) OWNER TO eximbuild;
181 -- Name: set_latest(); Type: FUNCTION; Schema: public; Owner: eximbuild
184 CREATE FUNCTION set_latest() RETURNS trigger
189 update latest_snapshot
191 (case when snapshot > NEW.snapshot then snapshot else NEW.snapshot end)
192 where sysname = NEW.sysname and
195 insert into latest_snapshot
196 values(NEW.sysname, NEW.branch, NEW.snapshot);
203 ALTER FUNCTION public.set_latest() OWNER TO eximbuild;
206 -- Name: set_local_error_terse(); Type: FUNCTION; Schema: public; Owner: eximbuild
209 CREATE FUNCTION set_local_error_terse() RETURNS void
210 LANGUAGE sql SECURITY DEFINER
211 AS $$ set local log_error_verbosity = terse $$;
214 -- ALTER FUNCTION public.set_local_error_terse() OWNER TO eximbuild;
217 -- Name: target(text); Type: FUNCTION; Schema: public; Owner: eximbuild
220 CREATE FUNCTION target(t text) RETURNS text
222 AS $_$ my $log = shift; $log =~ s/.*(Target:[^\n]*).*/$1/s; return $log; $_$;
225 ALTER FUNCTION public.target(t text) OWNER TO eximbuild;
228 -- Name: transitions(text, text, text, text, text, text); Type: FUNCTION; Schema: public; Owner: eximbuild
231 CREATE FUNCTION transitions(text, text, text, text, text, text) RETURNS integer
235 my ($os,$osv,$comp,$compv,$arch,$owner) = @_;
236 # count transitions to and from upper case
239 foreach (split "" ,"$os$osv$comp$compv$arch$owner")
260 ALTER FUNCTION public.transitions(text, text, text, text, text, text) OWNER TO eximbuild;
263 -- Name: web_script_version(text); Type: FUNCTION; Schema: public; Owner: eximbuild
266 CREATE FUNCTION web_script_version(text) RETURNS text
271 if ($log =~ /'web_script_version' => '(REL_)?(\d+)\.(\d+)'/)
273 return sprintf("%0.3d%0.3d",$2,$3);
280 ALTER FUNCTION public.web_script_version(text) OWNER TO eximbuild;
282 SET default_tablespace = '';
284 SET default_with_oids = true;
287 -- Name: alerts; Type: TABLE; Schema: public; Owner: eximbuild; Tablespace:
290 CREATE TABLE alerts (
291 sysname text NOT NULL,
292 branch text NOT NULL,
293 first_alert timestamp without time zone,
294 last_notification timestamp without time zone
298 ALTER TABLE public.alerts OWNER TO eximbuild;
301 -- Name: build_status; Type: TABLE; Schema: public; Owner: eximbuild; Tablespace:
304 CREATE TABLE build_status (
305 sysname text NOT NULL,
306 snapshot timestamp without time zone NOT NULL,
312 changed_this_run text,
313 changed_since_success text,
315 log_archive_filenames text[],
317 report_time timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone,
325 ALTER TABLE public.build_status OWNER TO eximbuild;
328 -- Name: build_status_export; Type: VIEW; Schema: public; Owner: eximbuild
331 CREATE VIEW build_status_export AS
332 SELECT build_status.sysname AS name, build_status.snapshot, build_status.stage, build_status.branch, build_status.build_flags FROM build_status;
335 ALTER TABLE public.build_status_export OWNER TO eximbuild;
338 -- Name: build_status_log; Type: TABLE; Schema: public; Owner: eximbuild; Tablespace:
341 CREATE TABLE build_status_log (
342 sysname text NOT NULL,
343 snapshot timestamp without time zone NOT NULL,
344 branch text NOT NULL,
345 log_stage text NOT NULL,
347 stage_duration interval
351 ALTER TABLE public.build_status_log OWNER TO eximbuild;
354 -- Name: buildsystems; Type: TABLE; Schema: public; Owner: eximbuild; Tablespace:
357 CREATE TABLE buildsystems (
359 secret text NOT NULL,
360 operating_system text NOT NULL,
361 os_version text NOT NULL,
362 compiler text NOT NULL,
363 compiler_version text NOT NULL,
364 architecture text NOT NULL,
365 status text NOT NULL,
366 sys_owner text NOT NULL,
367 owner_email text NOT NULL,
368 status_ts timestamp without time zone DEFAULT (('now'::text)::timestamp(6) with time zone)::timestamp without time zone,
369 no_alerts boolean DEFAULT false,
371 sys_notes_ts timestamp with time zone
375 ALTER TABLE public.buildsystems OWNER TO eximbuild;
378 -- Name: buildsystems_export; Type: VIEW; Schema: public; Owner: eximbuild
381 CREATE VIEW buildsystems_export AS
382 SELECT buildsystems.name, buildsystems.operating_system, buildsystems.os_version, buildsystems.compiler, buildsystems.compiler_version, buildsystems.architecture FROM buildsystems WHERE (buildsystems.status = 'approved'::text);
385 ALTER TABLE public.buildsystems_export OWNER TO eximbuild;
388 -- Name: dashboard_mat; Type: TABLE; Schema: public; Owner: eximbuild; Tablespace:
391 CREATE TABLE dashboard_mat (
392 sysname text NOT NULL,
393 snapshot timestamp without time zone NOT NULL,
396 branch text NOT NULL,
398 operating_system text,
401 compiler_version text,
403 sys_notes_ts timestamp with time zone,
408 ALTER TABLE public.dashboard_mat OWNER TO eximbuild;
411 -- Name: latest_snapshot; Type: TABLE; Schema: public; Owner: eximbuild; Tablespace:
414 CREATE TABLE latest_snapshot (
415 sysname text NOT NULL,
416 branch text NOT NULL,
417 snapshot timestamp without time zone NOT NULL
421 ALTER TABLE public.latest_snapshot OWNER TO eximbuild;
424 -- Name: personality; Type: TABLE; Schema: public; Owner: eximbuild; Tablespace:
427 CREATE TABLE personality (
429 os_version text NOT NULL,
430 compiler_version text NOT NULL,
431 effective_date timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL
435 ALTER TABLE public.personality OWNER TO eximbuild;
438 -- Name: dashboard_mat_data; Type: VIEW; Schema: public; Owner: eximbuild
441 CREATE VIEW dashboard_mat_data AS
442 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, s.sys_notes_ts, s.sys_notes 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));
445 ALTER TABLE public.dashboard_mat_data OWNER TO eximbuild;
448 -- Name: failures; Type: VIEW; Schema: public; Owner: eximbuild
451 CREATE VIEW failures AS
452 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 FROM build_status WHERE (((build_status.stage <> 'OK'::text) AND (build_status.stage !~~ 'CVS%'::text)) AND (build_status.report_time IS NOT NULL));
455 ALTER TABLE public.failures OWNER TO eximbuild;
458 -- Name: list_subscriptions; Type: TABLE; Schema: public; Owner: eximbuild; Tablespace:
461 CREATE TABLE list_subscriptions (
466 ALTER TABLE public.list_subscriptions OWNER TO eximbuild;
469 -- Name: penguin_save; Type: TABLE; Schema: public; Owner: eximbuild; Tablespace:
472 CREATE TABLE penguin_save (
474 snapshot timestamp without time zone,
479 ALTER TABLE public.penguin_save OWNER TO eximbuild;
482 -- Name: recent_failures; Type: VIEW; Schema: public; Owner: eximbuild
485 CREATE VIEW recent_failures AS
486 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));
489 ALTER TABLE public.recent_failures OWNER TO eximbuild;
492 -- Name: script_versions; Type: VIEW; Schema: public; Owner: eximbuild
495 CREATE VIEW script_versions AS
496 SELECT b.sysname, b.snapshot, b.branch, (script_version(b.conf_sum))::numeric AS script_version, (web_script_version(b.conf_sum))::numeric AS web_script_version FROM (build_status b JOIN dashboard_mat d ON (((b.sysname = d.sysname) AND (b.snapshot = d.snapshot))));
499 ALTER TABLE public.script_versions OWNER TO eximbuild;
502 -- Name: script_versions2; Type: VIEW; Schema: public; Owner: eximbuild
505 CREATE VIEW script_versions2 AS
506 SELECT b.sysname, b.snapshot, b.branch, script_version(b.conf_sum) AS script_version, web_script_version(b.conf_sum) AS web_script_version FROM (build_status b JOIN dashboard_mat d ON (((b.sysname = d.sysname) AND (b.snapshot = d.snapshot))));
509 ALTER TABLE public.script_versions2 OWNER TO eximbuild;
512 -- Name: alerts_pkey; Type: CONSTRAINT; Schema: public; Owner: eximbuild; Tablespace:
515 ALTER TABLE ONLY alerts
516 ADD CONSTRAINT alerts_pkey PRIMARY KEY (sysname, branch);
520 -- Name: build_status_log_pkey; Type: CONSTRAINT; Schema: public; Owner: eximbuild; Tablespace:
523 ALTER TABLE ONLY build_status_log
524 ADD CONSTRAINT build_status_log_pkey PRIMARY KEY (sysname, snapshot, log_stage);
528 -- Name: build_status_pkey; Type: CONSTRAINT; Schema: public; Owner: eximbuild; Tablespace:
531 ALTER TABLE ONLY build_status
532 ADD CONSTRAINT build_status_pkey PRIMARY KEY (sysname, snapshot);
536 -- Name: buildsystems_pkey; Type: CONSTRAINT; Schema: public; Owner: eximbuild; Tablespace:
539 ALTER TABLE ONLY buildsystems
540 ADD CONSTRAINT buildsystems_pkey PRIMARY KEY (name);
544 -- Name: dashboard_mat_pk; Type: CONSTRAINT; Schema: public; Owner: eximbuild; Tablespace:
547 ALTER TABLE ONLY dashboard_mat
548 ADD CONSTRAINT dashboard_mat_pk PRIMARY KEY (branch, sysname, snapshot);
550 ALTER TABLE dashboard_mat CLUSTER ON dashboard_mat_pk;
554 -- Name: latest_snapshot_pkey; Type: CONSTRAINT; Schema: public; Owner: eximbuild; Tablespace:
557 ALTER TABLE ONLY latest_snapshot
558 ADD CONSTRAINT latest_snapshot_pkey PRIMARY KEY (sysname, branch);
562 -- Name: personality_pkey; Type: CONSTRAINT; Schema: public; Owner: eximbuild; Tablespace:
565 ALTER TABLE ONLY personality
566 ADD CONSTRAINT personality_pkey PRIMARY KEY (name, effective_date);
570 -- Name: bs_branch_snapshot_idx; Type: INDEX; Schema: public; Owner: eximbuild; Tablespace:
573 CREATE INDEX bs_branch_snapshot_idx ON build_status USING btree (branch, snapshot);
577 -- Name: bs_status_idx; Type: INDEX; Schema: public; Owner: eximbuild; Tablespace:
580 CREATE INDEX bs_status_idx ON buildsystems USING btree (status);
584 -- Name: bs_sysname_branch_idx; Type: INDEX; Schema: public; Owner: eximbuild; Tablespace:
587 CREATE INDEX bs_sysname_branch_idx ON build_status USING btree (sysname, branch);
591 -- Name: bs_sysname_branch_report_idx; Type: INDEX; Schema: public; Owner: eximbuild; Tablespace:
594 CREATE INDEX bs_sysname_branch_report_idx ON build_status USING btree (sysname, branch, report_time);
598 -- Name: build_status_log_snapshot_idx; Type: INDEX; Schema: public; Owner: eximbuild; Tablespace:
601 CREATE INDEX build_status_log_snapshot_idx ON build_status_log USING btree (snapshot);
605 -- Name: set_latest_snapshot; Type: TRIGGER; Schema: public; Owner: eximbuild
608 CREATE TRIGGER set_latest_snapshot AFTER INSERT ON build_status FOR EACH ROW EXECUTE PROCEDURE set_latest();
612 -- Name: bs_fk; Type: FK CONSTRAINT; Schema: public; Owner: eximbuild
615 ALTER TABLE ONLY build_status
616 ADD CONSTRAINT bs_fk FOREIGN KEY (sysname) REFERENCES buildsystems(name) ON UPDATE CASCADE ON DELETE CASCADE;
620 -- Name: build_status_log_sysname_fkey; Type: FK CONSTRAINT; Schema: public; Owner: eximbuild
623 ALTER TABLE ONLY build_status_log
624 ADD CONSTRAINT build_status_log_sysname_fkey FOREIGN KEY (sysname, snapshot) REFERENCES build_status(sysname, snapshot) ON UPDATE CASCADE ON DELETE CASCADE;
628 -- Name: personality_build_systems_name_fk; Type: FK CONSTRAINT; Schema: public; Owner: eximbuild
631 ALTER TABLE ONLY personality
632 ADD CONSTRAINT personality_build_systems_name_fk FOREIGN KEY (name) REFERENCES buildsystems(name) ON UPDATE CASCADE ON DELETE CASCADE;
636 -- Name: public; Type: ACL; Schema: -; Owner: postgres
639 REVOKE ALL ON SCHEMA public FROM PUBLIC;
640 REVOKE ALL ON SCHEMA public FROM postgres;
641 GRANT ALL ON SCHEMA public TO postgres;
642 GRANT ALL ON SCHEMA public TO eximbuild;
643 GRANT ALL ON SCHEMA public TO PUBLIC;
647 -- Name: build_status; Type: ACL; Schema: public; Owner: eximbuild
650 REVOKE ALL ON TABLE build_status FROM PUBLIC;
651 REVOKE ALL ON TABLE build_status FROM eximbuild;
652 GRANT ALL ON TABLE build_status TO eximbuild;
653 GRANT SELECT,INSERT ON TABLE build_status TO apache;
654 GRANT SELECT ON TABLE build_status TO rssfeed;
658 -- Name: build_status_log; Type: ACL; Schema: public; Owner: eximbuild
661 REVOKE ALL ON TABLE build_status_log FROM PUBLIC;
662 REVOKE ALL ON TABLE build_status_log FROM eximbuild;
663 GRANT ALL ON TABLE build_status_log TO eximbuild;
664 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE build_status_log TO apache;
665 GRANT SELECT ON TABLE build_status_log TO rssfeed;
669 -- Name: buildsystems; Type: ACL; Schema: public; Owner: eximbuild
672 REVOKE ALL ON TABLE buildsystems FROM PUBLIC;
673 REVOKE ALL ON TABLE buildsystems FROM eximbuild;
674 GRANT ALL ON TABLE buildsystems TO eximbuild;
675 GRANT SELECT,INSERT,UPDATE ON TABLE buildsystems TO apache;
676 GRANT SELECT ON TABLE buildsystems TO rssfeed;
680 -- Name: dashboard_mat; Type: ACL; Schema: public; Owner: eximbuild
683 REVOKE ALL ON TABLE dashboard_mat FROM PUBLIC;
684 REVOKE ALL ON TABLE dashboard_mat FROM eximbuild;
685 GRANT ALL ON TABLE dashboard_mat TO eximbuild;
686 GRANT SELECT,INSERT,DELETE ON TABLE dashboard_mat TO apache;
690 -- Name: latest_snapshot; Type: ACL; Schema: public; Owner: eximbuild
693 REVOKE ALL ON TABLE latest_snapshot FROM PUBLIC;
694 REVOKE ALL ON TABLE latest_snapshot FROM eximbuild;
695 GRANT ALL ON TABLE latest_snapshot TO eximbuild;
696 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE latest_snapshot TO apache;
700 -- Name: personality; Type: ACL; Schema: public; Owner: eximbuild
703 REVOKE ALL ON TABLE personality FROM PUBLIC;
704 REVOKE ALL ON TABLE personality FROM eximbuild;
705 GRANT ALL ON TABLE personality TO eximbuild;
706 GRANT SELECT,INSERT ON TABLE personality TO apache;
707 GRANT SELECT ON TABLE personality TO rssfeed;
711 -- Name: dashboard_mat_data; Type: ACL; Schema: public; Owner: eximbuild
714 REVOKE ALL ON TABLE dashboard_mat_data FROM PUBLIC;
715 REVOKE ALL ON TABLE dashboard_mat_data FROM eximbuild;
716 GRANT ALL ON TABLE dashboard_mat_data TO eximbuild;
717 GRANT SELECT ON TABLE dashboard_mat_data TO apache;
721 -- PostgreSQL database dump complete