2 -- PostgreSQL database dump
5 SET client_encoding = 'SQL_ASCII';
6 SET check_function_bodies = false;
7 SET client_min_messages = warning;
10 -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: pgbuildfarm
13 COMMENT ON SCHEMA public IS 'Standard public schema';
16 SET search_path = public, pg_catalog;
19 -- Name: plperl_call_handler(); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
22 CREATE FUNCTION plperl_call_handler() RETURNS language_handler
23 AS '$libdir/plperl', 'plperl_call_handler'
27 ALTER FUNCTION public.plperl_call_handler() OWNER TO pgbuildfarm;
30 -- Name: plperl; Type: PROCEDURAL LANGUAGE; Schema: public; Owner:
33 CREATE TRUSTED PROCEDURAL LANGUAGE plperl HANDLER plperl_call_handler;
37 -- Name: plperlu; Type: PROCEDURAL LANGUAGE; Schema: public; Owner:
40 CREATE PROCEDURAL LANGUAGE plperlu HANDLER plperl_call_handler;
44 -- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
47 CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
48 AS '$libdir/plpgsql', 'plpgsql_call_handler'
52 ALTER FUNCTION public.plpgsql_call_handler() OWNER TO pgbuildfarm;
55 -- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner:
58 CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
62 -- Name: pending; Type: TYPE; Schema: public; Owner: pgbuildfarm
65 CREATE TYPE pending AS (
67 operating_system text,
70 compiler_version text,
76 ALTER TYPE public.pending OWNER TO pgbuildfarm;
79 -- Name: pending2; Type: TYPE; Schema: public; Owner: pgbuildfarm
82 CREATE TYPE pending2 AS (
84 operating_system text,
87 compiler_version text,
91 status_ts timestamp without time zone
95 ALTER TYPE public.pending2 OWNER TO pgbuildfarm;
98 -- Name: approve(text, text); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
101 CREATE FUNCTION approve(text, text) RETURNS void
102 AS $_$update buildsystems set name = $2, status ='approved' where name = $1 and status = 'pending'$_$
106 ALTER FUNCTION public.approve(text, text) OWNER TO pgbuildfarm;
109 -- Name: approve2(text, text); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
112 CREATE FUNCTION approve2(text, text) RETURNS text
113 AS $_$ update buildsystems set name = $2, status = 'approved' where name = $1 and status = 'pending'; select owner_email || ':' || name || ':' || secret from buildsystems where name = $2;$_$
117 ALTER FUNCTION public.approve2(text, text) OWNER TO pgbuildfarm;
120 -- Name: pending(); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
123 CREATE FUNCTION pending() RETURNS SETOF pending2
124 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 $$
128 ALTER FUNCTION public.pending() OWNER TO pgbuildfarm;
131 -- Name: prevstat(text, text, timestamp without time zone); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
134 CREATE FUNCTION prevstat(text, text, timestamp without time zone) RETURNS text
136 select coalesce((select distinct on (snapshot) stage
138 where sysname = $1 and branch = $2 and snapshot < $3
139 order by snapshot desc
140 limit 1), 'NEW') as prev_status
145 ALTER FUNCTION public.prevstat(text, text, timestamp without time zone) OWNER TO pgbuildfarm;
148 -- Name: target(text); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
151 CREATE FUNCTION target(t text) RETURNS text
152 AS $_$ my $log = shift; $log =~ s/.*(Target:[^\n]*).*/$1/s; return $log; $_$
156 ALTER FUNCTION public.target(t text) OWNER TO pgbuildfarm;
158 SET default_tablespace = '';
160 SET default_with_oids = true;
163 -- Name: alerts; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace:
166 CREATE TABLE alerts (
167 sysname text NOT NULL,
168 branch text NOT NULL,
169 first_alert timestamp without time zone,
170 last_notification timestamp without time zone
174 ALTER TABLE public.alerts OWNER TO pgbuildfarm;
177 -- Name: build_status; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace:
180 CREATE TABLE build_status (
181 sysname text NOT NULL,
182 snapshot timestamp without time zone NOT NULL,
188 changed_this_run text,
189 changed_since_success text,
191 log_archive_filenames text[],
193 report_time timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone
197 ALTER TABLE public.build_status OWNER TO pgbuildfarm;
200 -- Name: build_status_export; Type: VIEW; Schema: public; Owner: pgbuildfarm
203 CREATE VIEW build_status_export AS
204 SELECT build_status.sysname AS name, build_status.snapshot, build_status.stage, build_status.branch, build_status.build_flags FROM build_status;
207 ALTER TABLE public.build_status_export OWNER TO pgbuildfarm;
210 -- Name: build_status_log; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace:
213 CREATE TABLE build_status_log (
214 sysname text NOT NULL,
215 snapshot timestamp without time zone NOT NULL,
216 branch text NOT NULL,
217 log_stage text NOT NULL,
219 stage_duration interval
223 ALTER TABLE public.build_status_log OWNER TO pgbuildfarm;
226 -- Name: buildsystems; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace:
229 CREATE TABLE buildsystems (
231 secret text NOT NULL,
232 operating_system text NOT NULL,
233 os_version text NOT NULL,
234 compiler text NOT NULL,
235 compiler_version text NOT NULL,
236 architecture text NOT NULL,
237 status text NOT NULL,
238 sys_owner text NOT NULL,
239 owner_email text NOT NULL,
240 status_ts timestamp without time zone DEFAULT (('now'::text)::timestamp(6) with time zone)::timestamp without time zone
244 ALTER TABLE public.buildsystems OWNER TO pgbuildfarm;
247 -- Name: buildsystems_export; Type: VIEW; Schema: public; Owner: pgbuildfarm
250 CREATE VIEW buildsystems_export AS
251 SELECT buildsystems.name, buildsystems.operating_system, buildsystems.os_version, buildsystems.compiler, buildsystems.compiler_version, buildsystems.architecture FROM buildsystems WHERE (buildsystems.status = 'approved'::text);
254 ALTER TABLE public.buildsystems_export OWNER TO pgbuildfarm;
257 -- Name: failures; Type: VIEW; Schema: public; Owner: pgbuildfarm
260 CREATE VIEW failures AS
261 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));
264 ALTER TABLE public.failures OWNER TO pgbuildfarm;
267 -- Name: list_subscriptions; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace:
270 CREATE TABLE list_subscriptions (
275 ALTER TABLE public.list_subscriptions OWNER TO pgbuildfarm;
278 -- Name: penguin_save; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace:
281 CREATE TABLE penguin_save (
283 snapshot timestamp without time zone,
288 ALTER TABLE public.penguin_save OWNER TO pgbuildfarm;
291 -- Name: personality; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace:
294 CREATE TABLE personality (
296 os_version text NOT NULL,
297 compiler_version text NOT NULL,
298 effective_date timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL
302 ALTER TABLE public.personality OWNER TO pgbuildfarm;
305 -- Name: alerts_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace:
308 ALTER TABLE ONLY alerts
309 ADD CONSTRAINT alerts_pkey PRIMARY KEY (sysname, branch);
312 ALTER INDEX public.alerts_pkey OWNER TO pgbuildfarm;
315 -- Name: build_status_log_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace:
318 ALTER TABLE ONLY build_status_log
319 ADD CONSTRAINT build_status_log_pkey PRIMARY KEY (sysname, snapshot, log_stage);
322 ALTER INDEX public.build_status_log_pkey OWNER TO pgbuildfarm;
325 -- Name: build_status_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace:
328 ALTER TABLE ONLY build_status
329 ADD CONSTRAINT build_status_pkey PRIMARY KEY (sysname, snapshot);
332 ALTER INDEX public.build_status_pkey OWNER TO pgbuildfarm;
335 -- Name: buildsystems_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace:
338 ALTER TABLE ONLY buildsystems
339 ADD CONSTRAINT buildsystems_pkey PRIMARY KEY (name);
342 ALTER INDEX public.buildsystems_pkey OWNER TO pgbuildfarm;
345 -- Name: personality_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace:
348 ALTER TABLE ONLY personality
349 ADD CONSTRAINT personality_pkey PRIMARY KEY (name, effective_date);
352 ALTER INDEX public.personality_pkey OWNER TO pgbuildfarm;
355 -- Name: bs_branch_snapshot_idx; Type: INDEX; Schema: public; Owner: pgbuildfarm; Tablespace:
358 CREATE INDEX bs_branch_snapshot_idx ON build_status USING btree (branch, snapshot);
361 ALTER INDEX public.bs_branch_snapshot_idx OWNER TO pgbuildfarm;
364 -- Name: bs_status_idx; Type: INDEX; Schema: public; Owner: pgbuildfarm; Tablespace:
367 CREATE INDEX bs_status_idx ON buildsystems USING btree (status);
370 ALTER INDEX public.bs_status_idx OWNER TO pgbuildfarm;
373 -- Name: bs_sysname_branch_idx; Type: INDEX; Schema: public; Owner: pgbuildfarm; Tablespace:
376 CREATE INDEX bs_sysname_branch_idx ON build_status USING btree (sysname, branch);
379 ALTER INDEX public.bs_sysname_branch_idx OWNER TO pgbuildfarm;
382 -- Name: bs_sysname_branch_report_idx; Type: INDEX; Schema: public; Owner: pgbuildfarm; Tablespace:
385 CREATE INDEX bs_sysname_branch_report_idx ON build_status USING btree (sysname, branch, report_time);
388 ALTER INDEX public.bs_sysname_branch_report_idx OWNER TO pgbuildfarm;
391 -- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: pgbuildfarm
394 ALTER TABLE ONLY personality
395 ADD CONSTRAINT "$1" FOREIGN KEY (name) REFERENCES buildsystems(name) ON DELETE CASCADE;
399 -- Name: bs_fk; Type: FK CONSTRAINT; Schema: public; Owner: pgbuildfarm
402 ALTER TABLE ONLY build_status
403 ADD CONSTRAINT bs_fk FOREIGN KEY (sysname) REFERENCES buildsystems(name) ON UPDATE CASCADE ON DELETE CASCADE;
407 -- Name: build_status_log_sysname_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pgbuildfarm
410 ALTER TABLE ONLY build_status_log
411 ADD CONSTRAINT build_status_log_sysname_fkey FOREIGN KEY (sysname, snapshot) REFERENCES build_status(sysname, snapshot) ON DELETE CASCADE;
415 -- Name: public; Type: ACL; Schema: -; Owner: pgbuildfarm
418 REVOKE ALL ON SCHEMA public FROM PUBLIC;
419 REVOKE ALL ON SCHEMA public FROM pgbuildfarm;
420 GRANT ALL ON SCHEMA public TO pgbuildfarm;
421 GRANT ALL ON SCHEMA public TO PUBLIC;
425 -- Name: build_status; Type: ACL; Schema: public; Owner: pgbuildfarm
428 REVOKE ALL ON TABLE build_status FROM PUBLIC;
429 REVOKE ALL ON TABLE build_status FROM pgbuildfarm;
430 GRANT ALL ON TABLE build_status TO pgbuildfarm;
431 GRANT INSERT,SELECT ON TABLE build_status TO pgbfweb;
432 GRANT SELECT ON TABLE build_status TO rssfeed;
436 -- Name: build_status_log; Type: ACL; Schema: public; Owner: pgbuildfarm
439 REVOKE ALL ON TABLE build_status_log FROM PUBLIC;
440 REVOKE ALL ON TABLE build_status_log FROM pgbuildfarm;
441 GRANT ALL ON TABLE build_status_log TO pgbuildfarm;
442 GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE build_status_log TO pgbfweb;
443 GRANT SELECT ON TABLE build_status_log TO rssfeed;
447 -- Name: buildsystems; Type: ACL; Schema: public; Owner: pgbuildfarm
450 REVOKE ALL ON TABLE buildsystems FROM PUBLIC;
451 REVOKE ALL ON TABLE buildsystems FROM pgbuildfarm;
452 GRANT ALL ON TABLE buildsystems TO pgbuildfarm;
453 GRANT INSERT,SELECT ON TABLE buildsystems TO pgbfweb;
454 GRANT SELECT ON TABLE buildsystems TO rssfeed;
458 -- Name: personality; Type: ACL; Schema: public; Owner: pgbuildfarm
461 REVOKE ALL ON TABLE personality FROM PUBLIC;
462 REVOKE ALL ON TABLE personality FROM pgbuildfarm;
463 GRANT ALL ON TABLE personality TO pgbuildfarm;
464 GRANT INSERT,SELECT ON TABLE personality TO pgbfweb;
465 GRANT SELECT ON TABLE personality TO rssfeed;
469 -- PostgreSQL database dump complete