update to latest schema
[buildfarm-server.git] / schema / bfwebdb.sql
1 --
2 -- PostgreSQL database dump
3 --
4
5 SET client_encoding = 'SQL_ASCII';
6 SET check_function_bodies = false;
7 SET client_min_messages = warning;
8
9 --
10 -- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: pgbuildfarm
11 --
12
13 COMMENT ON SCHEMA public IS 'Standard public schema';
14
15
16 SET search_path = public, pg_catalog;
17
18 --
19 -- Name: plperl_call_handler(); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
20 --
21
22 CREATE FUNCTION plperl_call_handler() RETURNS language_handler
23     AS '$libdir/plperl', 'plperl_call_handler'
24     LANGUAGE c;
25
26
27 ALTER FUNCTION public.plperl_call_handler() OWNER TO pgbuildfarm;
28
29 --
30 -- Name: plperl; Type: PROCEDURAL LANGUAGE; Schema: public; Owner: 
31 --
32
33 CREATE TRUSTED PROCEDURAL LANGUAGE plperl HANDLER plperl_call_handler;
34
35
36 --
37 -- Name: plperlu; Type: PROCEDURAL LANGUAGE; Schema: public; Owner: 
38 --
39
40 CREATE PROCEDURAL LANGUAGE plperlu HANDLER plperl_call_handler;
41
42
43 --
44 -- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
45 --
46
47 CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
48     AS '$libdir/plpgsql', 'plpgsql_call_handler'
49     LANGUAGE c;
50
51
52 ALTER FUNCTION public.plpgsql_call_handler() OWNER TO pgbuildfarm;
53
54 --
55 -- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: public; Owner: 
56 --
57
58 CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;
59
60
61 --
62 -- Name: pending; Type: TYPE; Schema: public; Owner: pgbuildfarm
63 --
64
65 CREATE TYPE pending AS (
66         name text,
67         operating_system text,
68         os_version text,
69         compiler text,
70         compiler_version text,
71         architecture text,
72         owner_email text
73 );
74
75
76 ALTER TYPE public.pending OWNER TO pgbuildfarm;
77
78 --
79 -- Name: pending2; Type: TYPE; Schema: public; Owner: pgbuildfarm
80 --
81
82 CREATE TYPE pending2 AS (
83         name text,
84         operating_system text,
85         os_version text,
86         compiler text,
87         compiler_version text,
88         architecture text,
89         owner_email text,
90         "owner" text,
91         status_ts timestamp without time zone
92 );
93
94
95 ALTER TYPE public.pending2 OWNER TO pgbuildfarm;
96
97 --
98 -- Name: approve(text, text); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
99 --
100
101 CREATE FUNCTION approve(text, text) RETURNS void
102     AS $_$update buildsystems set name = $2, status ='approved' where name = $1 and status = 'pending'$_$
103     LANGUAGE sql;
104
105
106 ALTER FUNCTION public.approve(text, text) OWNER TO pgbuildfarm;
107
108 --
109 -- Name: approve2(text, text); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
110 --
111
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;$_$
114     LANGUAGE sql;
115
116
117 ALTER FUNCTION public.approve2(text, text) OWNER TO pgbuildfarm;
118
119 --
120 -- Name: pending(); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
121 --
122
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 $$
125     LANGUAGE sql;
126
127
128 ALTER FUNCTION public.pending() OWNER TO pgbuildfarm;
129
130 --
131 -- Name: prevstat(text, text, timestamp without time zone); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
132 --
133
134 CREATE FUNCTION prevstat(text, text, timestamp without time zone) RETURNS text
135     AS $_$
136    select coalesce((select distinct on (snapshot) stage
137                   from build_status
138                   where sysname = $1 and branch = $2 and snapshot < $3
139                   order by snapshot desc
140                   limit 1), 'NEW') as prev_status
141 $_$
142     LANGUAGE sql;
143
144
145 ALTER FUNCTION public.prevstat(text, text, timestamp without time zone) OWNER TO pgbuildfarm;
146
147 --
148 -- Name: set_latest(); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
149 --
150
151 CREATE FUNCTION set_latest() RETURNS "trigger"
152     AS $$
153
154         begin
155                 update latest_snapshot 
156                         set snapshot = 
157         (case when snapshot > NEW.snapshot then snapshot else NEW.snapshot end)
158                         where sysname = NEW.sysname and
159                                 branch = NEW.branch;
160                 if not found then
161                         insert into latest_snapshot
162                                 values(NEW.sysname, NEW.branch, NEW.snapshot);
163                 end if;
164                 return NEW;
165         end;
166 $$
167     LANGUAGE plpgsql;
168
169
170 ALTER FUNCTION public.set_latest() OWNER TO pgbuildfarm;
171
172 --
173 -- Name: target(text); Type: FUNCTION; Schema: public; Owner: pgbuildfarm
174 --
175
176 CREATE FUNCTION target(t text) RETURNS text
177     AS $_$ my $log = shift; $log =~ s/.*(Target:[^\n]*).*/$1/s; return $log; $_$
178     LANGUAGE plperl;
179
180
181 ALTER FUNCTION public.target(t text) OWNER TO pgbuildfarm;
182
183 SET default_tablespace = '';
184
185 SET default_with_oids = true;
186
187 --
188 -- Name: alerts; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace: 
189 --
190
191 CREATE TABLE alerts (
192     sysname text NOT NULL,
193     branch text NOT NULL,
194     first_alert timestamp without time zone,
195     last_notification timestamp without time zone
196 );
197
198
199 ALTER TABLE public.alerts OWNER TO pgbuildfarm;
200
201 --
202 -- Name: build_status; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace: 
203 --
204
205 CREATE TABLE build_status (
206     sysname text NOT NULL,
207     snapshot timestamp without time zone NOT NULL,
208     status integer,
209     stage text,
210     log text,
211     conf_sum text,
212     branch text,
213     changed_this_run text,
214     changed_since_success text,
215     log_archive bytea,
216     log_archive_filenames text[],
217     build_flags text[],
218     report_time timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone
219 );
220
221
222 ALTER TABLE public.build_status OWNER TO pgbuildfarm;
223
224 --
225 -- Name: build_status_export; Type: VIEW; Schema: public; Owner: pgbuildfarm
226 --
227
228 CREATE VIEW build_status_export AS
229     SELECT build_status.sysname AS name, build_status.snapshot, build_status.stage, build_status.branch, build_status.build_flags FROM build_status;
230
231
232 ALTER TABLE public.build_status_export OWNER TO pgbuildfarm;
233
234 --
235 -- Name: build_status_log; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace: 
236 --
237
238 CREATE TABLE build_status_log (
239     sysname text NOT NULL,
240     snapshot timestamp without time zone NOT NULL,
241     branch text NOT NULL,
242     log_stage text NOT NULL,
243     log_text text,
244     stage_duration interval
245 );
246
247
248 ALTER TABLE public.build_status_log OWNER TO pgbuildfarm;
249
250 --
251 -- Name: buildsystems; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace: 
252 --
253
254 CREATE TABLE buildsystems (
255     name text NOT NULL,
256     secret text NOT NULL,
257     operating_system text NOT NULL,
258     os_version text NOT NULL,
259     compiler text NOT NULL,
260     compiler_version text NOT NULL,
261     architecture text NOT NULL,
262     status text NOT NULL,
263     sys_owner text NOT NULL,
264     owner_email text NOT NULL,
265     status_ts timestamp without time zone DEFAULT (('now'::text)::timestamp(6) with time zone)::timestamp without time zone,
266     no_alerts boolean DEFAULT false
267 );
268
269
270 ALTER TABLE public.buildsystems OWNER TO pgbuildfarm;
271
272 --
273 -- Name: buildsystems_export; Type: VIEW; Schema: public; Owner: pgbuildfarm
274 --
275
276 CREATE VIEW buildsystems_export AS
277     SELECT buildsystems.name, buildsystems.operating_system, buildsystems.os_version, buildsystems.compiler, buildsystems.compiler_version, buildsystems.architecture FROM buildsystems WHERE (buildsystems.status = 'approved'::text);
278
279
280 ALTER TABLE public.buildsystems_export OWNER TO pgbuildfarm;
281
282 --
283 -- Name: latest_snapshot; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace: 
284 --
285
286 CREATE TABLE latest_snapshot (
287     sysname text NOT NULL,
288     branch text NOT NULL,
289     snapshot timestamp without time zone NOT NULL
290 );
291
292
293 ALTER TABLE public.latest_snapshot OWNER TO pgbuildfarm;
294
295 --
296 -- Name: personality; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace: 
297 --
298
299 CREATE TABLE personality (
300     name text NOT NULL,
301     os_version text NOT NULL,
302     compiler_version text NOT NULL,
303     effective_date timestamp with time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL
304 );
305
306
307 ALTER TABLE public.personality OWNER TO pgbuildfarm;
308
309 --
310 -- Name: dashboard; Type: VIEW; Schema: public; Owner: pgbuildfarm
311 --
312
313 CREATE VIEW dashboard AS
314     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));
315
316
317 ALTER TABLE public.dashboard OWNER TO pgbuildfarm;
318
319 --
320 -- Name: failures; Type: VIEW; Schema: public; Owner: pgbuildfarm
321 --
322
323 CREATE VIEW failures AS
324     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));
325
326
327 ALTER TABLE public.failures OWNER TO pgbuildfarm;
328
329 --
330 -- Name: list_subscriptions; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace: 
331 --
332
333 CREATE TABLE list_subscriptions (
334     addr text
335 );
336
337
338 ALTER TABLE public.list_subscriptions OWNER TO pgbuildfarm;
339
340 --
341 -- Name: penguin_save; Type: TABLE; Schema: public; Owner: pgbuildfarm; Tablespace: 
342 --
343
344 CREATE TABLE penguin_save (
345     branch text,
346     snapshot timestamp without time zone,
347     stage text
348 );
349
350
351 ALTER TABLE public.penguin_save OWNER TO pgbuildfarm;
352
353 --
354 -- Name: recent_failures; Type: VIEW; Schema: public; Owner: pgbuildfarm
355 --
356
357 CREATE VIEW recent_failures AS
358     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));
359
360
361 ALTER TABLE public.recent_failures OWNER TO pgbuildfarm;
362
363 --
364 -- Name: alerts_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace: 
365 --
366
367 ALTER TABLE ONLY alerts
368     ADD CONSTRAINT alerts_pkey PRIMARY KEY (sysname, branch);
369
370
371 ALTER INDEX public.alerts_pkey OWNER TO pgbuildfarm;
372
373 --
374 -- Name: build_status_log_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace: 
375 --
376
377 ALTER TABLE ONLY build_status_log
378     ADD CONSTRAINT build_status_log_pkey PRIMARY KEY (sysname, snapshot, log_stage);
379
380
381 ALTER INDEX public.build_status_log_pkey OWNER TO pgbuildfarm;
382
383 --
384 -- Name: build_status_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace: 
385 --
386
387 ALTER TABLE ONLY build_status
388     ADD CONSTRAINT build_status_pkey PRIMARY KEY (sysname, snapshot);
389
390
391 ALTER INDEX public.build_status_pkey OWNER TO pgbuildfarm;
392
393 --
394 -- Name: buildsystems_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace: 
395 --
396
397 ALTER TABLE ONLY buildsystems
398     ADD CONSTRAINT buildsystems_pkey PRIMARY KEY (name);
399
400
401 ALTER INDEX public.buildsystems_pkey OWNER TO pgbuildfarm;
402
403 --
404 -- Name: latest_snapshot_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace: 
405 --
406
407 ALTER TABLE ONLY latest_snapshot
408     ADD CONSTRAINT latest_snapshot_pkey PRIMARY KEY (sysname, branch);
409
410
411 ALTER INDEX public.latest_snapshot_pkey OWNER TO pgbuildfarm;
412
413 --
414 -- Name: personality_pkey; Type: CONSTRAINT; Schema: public; Owner: pgbuildfarm; Tablespace: 
415 --
416
417 ALTER TABLE ONLY personality
418     ADD CONSTRAINT personality_pkey PRIMARY KEY (name, effective_date);
419
420
421 ALTER INDEX public.personality_pkey OWNER TO pgbuildfarm;
422
423 --
424 -- Name: bs_branch_snapshot_idx; Type: INDEX; Schema: public; Owner: pgbuildfarm; Tablespace: 
425 --
426
427 CREATE INDEX bs_branch_snapshot_idx ON build_status USING btree (branch, snapshot);
428
429
430 ALTER INDEX public.bs_branch_snapshot_idx OWNER TO pgbuildfarm;
431
432 --
433 -- Name: bs_status_idx; Type: INDEX; Schema: public; Owner: pgbuildfarm; Tablespace: 
434 --
435
436 CREATE INDEX bs_status_idx ON buildsystems USING btree (status);
437
438
439 ALTER INDEX public.bs_status_idx OWNER TO pgbuildfarm;
440
441 --
442 -- Name: bs_sysname_branch_idx; Type: INDEX; Schema: public; Owner: pgbuildfarm; Tablespace: 
443 --
444
445 CREATE INDEX bs_sysname_branch_idx ON build_status USING btree (sysname, branch);
446
447
448 ALTER INDEX public.bs_sysname_branch_idx OWNER TO pgbuildfarm;
449
450 --
451 -- Name: bs_sysname_branch_report_idx; Type: INDEX; Schema: public; Owner: pgbuildfarm; Tablespace: 
452 --
453
454 CREATE INDEX bs_sysname_branch_report_idx ON build_status USING btree (sysname, branch, report_time);
455
456
457 ALTER INDEX public.bs_sysname_branch_report_idx OWNER TO pgbuildfarm;
458
459 --
460 -- Name: set_latest_snapshot; Type: TRIGGER; Schema: public; Owner: pgbuildfarm
461 --
462
463 CREATE TRIGGER set_latest_snapshot
464     AFTER INSERT ON build_status
465     FOR EACH ROW
466     EXECUTE PROCEDURE set_latest();
467
468
469 --
470 -- Name: bs_fk; Type: FK CONSTRAINT; Schema: public; Owner: pgbuildfarm
471 --
472
473 ALTER TABLE ONLY build_status
474     ADD CONSTRAINT bs_fk FOREIGN KEY (sysname) REFERENCES buildsystems(name) ON UPDATE CASCADE ON DELETE CASCADE;
475
476
477 --
478 -- Name: build_status_log_sysname_fkey; Type: FK CONSTRAINT; Schema: public; Owner: pgbuildfarm
479 --
480
481 ALTER TABLE ONLY build_status_log
482     ADD CONSTRAINT build_status_log_sysname_fkey FOREIGN KEY (sysname, snapshot) REFERENCES build_status(sysname, snapshot) ON UPDATE CASCADE ON DELETE CASCADE;
483
484
485 --
486 -- Name: personality_build_systems_name_fk; Type: FK CONSTRAINT; Schema: public; Owner: pgbuildfarm
487 --
488
489 ALTER TABLE ONLY personality
490     ADD CONSTRAINT personality_build_systems_name_fk FOREIGN KEY (name) REFERENCES buildsystems(name) ON UPDATE CASCADE ON DELETE CASCADE;
491
492
493 --
494 -- Name: public; Type: ACL; Schema: -; Owner: pgbuildfarm
495 --
496
497 REVOKE ALL ON SCHEMA public FROM PUBLIC;
498 REVOKE ALL ON SCHEMA public FROM pgbuildfarm;
499 GRANT ALL ON SCHEMA public TO pgbuildfarm;
500 GRANT ALL ON SCHEMA public TO PUBLIC;
501
502
503 --
504 -- Name: build_status; Type: ACL; Schema: public; Owner: pgbuildfarm
505 --
506
507 REVOKE ALL ON TABLE build_status FROM PUBLIC;
508 REVOKE ALL ON TABLE build_status FROM pgbuildfarm;
509 GRANT ALL ON TABLE build_status TO pgbuildfarm;
510 GRANT INSERT,SELECT ON TABLE build_status TO pgbfweb;
511 GRANT SELECT ON TABLE build_status TO rssfeed;
512
513
514 --
515 -- Name: build_status_log; Type: ACL; Schema: public; Owner: pgbuildfarm
516 --
517
518 REVOKE ALL ON TABLE build_status_log FROM PUBLIC;
519 REVOKE ALL ON TABLE build_status_log FROM pgbuildfarm;
520 GRANT ALL ON TABLE build_status_log TO pgbuildfarm;
521 GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE build_status_log TO pgbfweb;
522 GRANT SELECT ON TABLE build_status_log TO rssfeed;
523
524
525 --
526 -- Name: buildsystems; Type: ACL; Schema: public; Owner: pgbuildfarm
527 --
528
529 REVOKE ALL ON TABLE buildsystems FROM PUBLIC;
530 REVOKE ALL ON TABLE buildsystems FROM pgbuildfarm;
531 GRANT ALL ON TABLE buildsystems TO pgbuildfarm;
532 GRANT INSERT,SELECT ON TABLE buildsystems TO pgbfweb;
533 GRANT SELECT ON TABLE buildsystems TO rssfeed;
534
535
536 --
537 -- Name: latest_snapshot; Type: ACL; Schema: public; Owner: pgbuildfarm
538 --
539
540 REVOKE ALL ON TABLE latest_snapshot FROM PUBLIC;
541 REVOKE ALL ON TABLE latest_snapshot FROM pgbuildfarm;
542 GRANT ALL ON TABLE latest_snapshot TO pgbuildfarm;
543 GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE latest_snapshot TO pgbfweb;
544
545
546 --
547 -- Name: personality; Type: ACL; Schema: public; Owner: pgbuildfarm
548 --
549
550 REVOKE ALL ON TABLE personality FROM PUBLIC;
551 REVOKE ALL ON TABLE personality FROM pgbuildfarm;
552 GRANT ALL ON TABLE personality TO pgbuildfarm;
553 GRANT INSERT,SELECT ON TABLE personality TO pgbfweb;
554 GRANT SELECT ON TABLE personality TO rssfeed;
555
556
557 --
558 -- Name: dashboard; Type: ACL; Schema: public; Owner: pgbuildfarm
559 --
560
561 REVOKE ALL ON TABLE dashboard FROM PUBLIC;
562 REVOKE ALL ON TABLE dashboard FROM pgbuildfarm;
563 GRANT ALL ON TABLE dashboard TO pgbuildfarm;
564 GRANT SELECT ON TABLE dashboard TO pgbfweb;
565
566
567 --
568 -- PostgreSQL database dump complete
569 --
570