root/sql/reconnoiter_ddl_dump.sql

Revision 146a7f3bfbf2f113f1cbdba31a2ba3e575d3e2f8, 71.0 kB (checked in by Denish Patel <denish@omniti.com>, 6 years ago)

Full database dump

  • Property mode set to 100644
Line 
1 --
2 -- PostgreSQL database dump
3 --
4
5 SET client_encoding = 'UTF8';
6 SET standard_conforming_strings = off;
7 SET check_function_bodies = false;
8 SET client_min_messages = warning;
9 SET escape_string_warning = off;
10
11 --
12 -- Name: prism; Type: SCHEMA; Schema: -; Owner: prism
13 --
14
15 CREATE SCHEMA prism;
16
17
18 ALTER SCHEMA prism OWNER TO prism;
19
20 --
21 -- Name: stratcon; Type: SCHEMA; Schema: -; Owner: stratcon
22 --
23
24 CREATE SCHEMA stratcon;
25
26
27 ALTER SCHEMA stratcon OWNER TO stratcon;
28
29 --
30 -- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: omniti
31 --
32
33 CREATE PROCEDURAL LANGUAGE plpgsql;
34
35
36 ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO omniti;
37
38 SET search_path = prism, pg_catalog;
39
40 SET default_tablespace = '';
41
42 SET default_with_oids = false;
43
44 --
45 -- Name: saved_graphs; Type: TABLE; Schema: prism; Owner: reconnoiter; Tablespace:
46 --
47
48 CREATE TABLE saved_graphs (
49     graphid uuid NOT NULL,
50     json text NOT NULL,
51     saved boolean DEFAULT false NOT NULL,
52     title text,
53     last_update timestamp without time zone NOT NULL
54 );
55
56
57 ALTER TABLE prism.saved_graphs OWNER TO reconnoiter;
58
59 --
60 -- Name: saved_graphs_dep; Type: TABLE; Schema: prism; Owner: reconnoiter; Tablespace:
61 --
62
63 CREATE TABLE saved_graphs_dep (
64     graphid uuid NOT NULL,
65     sid integer NOT NULL,
66     metric_name text NOT NULL,
67     metric_type character varying(22)
68 );
69
70
71 ALTER TABLE prism.saved_graphs_dep OWNER TO reconnoiter;
72
73 SET search_path = public, pg_catalog;
74
75 SET default_with_oids = true;
76
77 --
78 -- Name: pga_diagrams; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
79 --
80
81 CREATE TABLE pga_diagrams (
82     diagramname character varying(64) NOT NULL,
83     diagramtables text,
84     diagramlinks text
85 );
86
87
88 ALTER TABLE public.pga_diagrams OWNER TO reconnoiter;
89
90 --
91 -- Name: pga_forms; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
92 --
93
94 CREATE TABLE pga_forms (
95     formname character varying(64) NOT NULL,
96     formsource text
97 );
98
99
100 ALTER TABLE public.pga_forms OWNER TO reconnoiter;
101
102 --
103 -- Name: pga_graphs; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
104 --
105
106 CREATE TABLE pga_graphs (
107     graphname character varying(64) NOT NULL,
108     graphsource text,
109     graphcode text
110 );
111
112
113 ALTER TABLE public.pga_graphs OWNER TO reconnoiter;
114
115 --
116 -- Name: pga_images; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
117 --
118
119 CREATE TABLE pga_images (
120     imagename character varying(64) NOT NULL,
121     imagesource text
122 );
123
124
125 ALTER TABLE public.pga_images OWNER TO reconnoiter;
126
127 --
128 -- Name: pga_layout; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
129 --
130
131 CREATE TABLE pga_layout (
132     tablename character varying(64) NOT NULL,
133     nrcols smallint,
134     colnames text,
135     colwidth text
136 );
137
138
139 ALTER TABLE public.pga_layout OWNER TO reconnoiter;
140
141 --
142 -- Name: pga_queries; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
143 --
144
145 CREATE TABLE pga_queries (
146     queryname character varying(64) NOT NULL,
147     querytype character(1),
148     querycommand text,
149     querytables text,
150     querylinks text,
151     queryresults text,
152     querycomments text
153 );
154
155
156 ALTER TABLE public.pga_queries OWNER TO reconnoiter;
157
158 --
159 -- Name: pga_reports; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
160 --
161
162 CREATE TABLE pga_reports (
163     reportname character varying(64) NOT NULL,
164     reportsource text,
165     reportbody text,
166     reportprocs text,
167     reportoptions text
168 );
169
170
171 ALTER TABLE public.pga_reports OWNER TO reconnoiter;
172
173 --
174 -- Name: pga_scripts; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
175 --
176
177 CREATE TABLE pga_scripts (
178     scriptname character varying(64) NOT NULL,
179     scriptsource text
180 );
181
182
183 ALTER TABLE public.pga_scripts OWNER TO reconnoiter;
184
185 SET default_with_oids = false;
186
187 --
188 -- Name: varnish_huh; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
189 --
190
191 CREATE TABLE varnish_huh (
192     sid integer,
193     whence timestamp with time zone,
194     name text,
195     value numeric
196 );
197
198
199 ALTER TABLE public.varnish_huh OWNER TO reconnoiter;
200
201 --
202 -- Name: varnish_huh2; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
203 --
204
205 CREATE TABLE varnish_huh2 (
206     sid integer,
207     whence timestamp with time zone,
208     name text,
209     value numeric
210 );
211
212
213 ALTER TABLE public.varnish_huh2 OWNER TO reconnoiter;
214
215 SET search_path = stratcon, pg_catalog;
216
217 --
218 -- Name: current_metric_text; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
219 --
220
221 CREATE TABLE current_metric_text (
222     sid integer NOT NULL,
223     whence timestamp with time zone NOT NULL,
224     name text NOT NULL,
225     value text
226 );
227
228
229 ALTER TABLE stratcon.current_metric_text OWNER TO reconnoiter;
230
231 --
232 -- Name: current_node_config; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
233 --
234
235 CREATE TABLE current_node_config (
236     remote_address inet NOT NULL,
237     node_type text NOT NULL,
238     whence timestamp with time zone NOT NULL,
239     config xml NOT NULL
240 );
241
242
243 ALTER TABLE stratcon.current_node_config OWNER TO reconnoiter;
244
245 --
246 -- Name: current_node_config_changelog; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
247 --
248
249 CREATE TABLE current_node_config_changelog (
250     remote_address inet NOT NULL,
251     node_type text NOT NULL,
252     whence timestamp with time zone NOT NULL,
253     config xml NOT NULL
254 );
255
256
257 ALTER TABLE stratcon.current_node_config_changelog OWNER TO reconnoiter;
258
259 --
260 -- Name: loading_dock_check_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
261 --
262
263 CREATE TABLE loading_dock_check_s (
264     sid integer NOT NULL,
265     remote_address inet,
266     whence timestamp with time zone NOT NULL,
267     id uuid NOT NULL,
268     target text NOT NULL,
269     module text NOT NULL,
270     name text NOT NULL
271 );
272
273
274 ALTER TABLE stratcon.loading_dock_check_s OWNER TO reconnoiter;
275
276 --
277 -- Name: loading_dock_metric_numeric_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
278 --
279
280 CREATE TABLE loading_dock_metric_numeric_s (
281     sid integer NOT NULL,
282     whence timestamp with time zone NOT NULL,
283     name text NOT NULL,
284     value numeric
285 );
286
287
288 ALTER TABLE stratcon.loading_dock_metric_numeric_s OWNER TO reconnoiter;
289
290 --
291 -- Name: loading_dock_metric_text_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
292 --
293
294 CREATE TABLE loading_dock_metric_text_s (
295     sid integer NOT NULL,
296     whence timestamp with time zone NOT NULL,
297     name text NOT NULL,
298     value text
299 );
300
301
302 ALTER TABLE stratcon.loading_dock_metric_text_s OWNER TO reconnoiter;
303
304 --
305 -- Name: loading_dock_metric_text_s_change_log; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
306 --
307
308 CREATE TABLE loading_dock_metric_text_s_change_log (
309     sid integer NOT NULL,
310     whence timestamp with time zone NOT NULL,
311     name text NOT NULL,
312     value text
313 );
314
315
316 ALTER TABLE stratcon.loading_dock_metric_text_s_change_log OWNER TO reconnoiter;
317
318 --
319 -- Name: loading_dock_status_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
320 --
321
322 CREATE TABLE loading_dock_status_s (
323     sid integer NOT NULL,
324     whence timestamp with time zone NOT NULL,
325     state character(1) NOT NULL,
326     availability character(1) NOT NULL,
327     duration integer NOT NULL,
328     status text
329 );
330
331
332 ALTER TABLE stratcon.loading_dock_status_s OWNER TO reconnoiter;
333
334 --
335 -- Name: loading_dock_status_s_change_log; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
336 --
337
338 CREATE TABLE loading_dock_status_s_change_log (
339     sid integer NOT NULL,
340     whence timestamp with time zone NOT NULL,
341     state character(1) NOT NULL,
342     availability character(1) NOT NULL,
343     duration integer NOT NULL,
344     status text
345 );
346
347
348 ALTER TABLE stratcon.loading_dock_status_s_change_log OWNER TO reconnoiter;
349
350 --
351 -- Name: log_whence_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
352 --
353
354 CREATE TABLE log_whence_s (
355     whence timestamp with time zone NOT NULL,
356     "interval" character varying(20) NOT NULL
357 );
358
359
360 ALTER TABLE stratcon.log_whence_s OWNER TO reconnoiter;
361
362 --
363 -- Name: map_uuid_to_sid; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
364 --
365
366 CREATE TABLE map_uuid_to_sid (
367     id uuid NOT NULL,
368     sid integer NOT NULL
369 );
370
371
372 ALTER TABLE stratcon.map_uuid_to_sid OWNER TO reconnoiter;
373
374 --
375 -- Name: metric_name_summary; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
376 --
377
378 CREATE TABLE metric_name_summary (
379     sid integer NOT NULL,
380     metric_name text NOT NULL,
381     metric_type character varying(22),
382     active boolean DEFAULT true
383 );
384
385
386 ALTER TABLE stratcon.metric_name_summary OWNER TO reconnoiter;
387
388 --
389 -- Name: mv_loading_dock_check_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
390 --
391
392 CREATE TABLE mv_loading_dock_check_s (
393     sid integer NOT NULL,
394     remote_address inet,
395     whence timestamp with time zone NOT NULL,
396     id uuid NOT NULL,
397     target text NOT NULL,
398     module text NOT NULL,
399     name text NOT NULL
400 );
401
402
403 ALTER TABLE stratcon.mv_loading_dock_check_s OWNER TO reconnoiter;
404
405 --
406 -- Name: rollup_matrix_numeric_12hours; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
407 --
408
409 CREATE TABLE rollup_matrix_numeric_12hours (
410     sid integer NOT NULL,
411     name text NOT NULL,
412     rollup_time timestamp with time zone NOT NULL,
413     count_rows integer,
414     avg_value numeric
415 );
416
417
418 ALTER TABLE stratcon.rollup_matrix_numeric_12hours OWNER TO reconnoiter;
419
420 --
421 -- Name: rollup_matrix_numeric_20m; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
422 --
423
424 CREATE TABLE rollup_matrix_numeric_20m (
425     sid integer NOT NULL,
426     name text NOT NULL,
427     rollup_time timestamp with time zone NOT NULL,
428     count_rows integer,
429     avg_value numeric
430 );
431
432
433 ALTER TABLE stratcon.rollup_matrix_numeric_20m OWNER TO reconnoiter;
434
435 --
436 -- Name: rollup_matrix_numeric_5m; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
437 --
438
439 CREATE TABLE rollup_matrix_numeric_5m (
440     sid integer NOT NULL,
441     name text NOT NULL,
442     rollup_time timestamp with time zone NOT NULL,
443     count_rows integer,
444     avg_value numeric
445 );
446
447
448 ALTER TABLE stratcon.rollup_matrix_numeric_5m OWNER TO reconnoiter;
449
450 --
451 -- Name: rollup_matrix_numeric_60m; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
452 --
453
454 CREATE TABLE rollup_matrix_numeric_60m (
455     sid integer NOT NULL,
456     name text NOT NULL,
457     rollup_time timestamp with time zone NOT NULL,
458     count_rows integer,
459     avg_value numeric
460 );
461
462
463 ALTER TABLE stratcon.rollup_matrix_numeric_60m OWNER TO reconnoiter;
464
465 --
466 -- Name: rollup_matrix_numeric_6hours; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
467 --
468
469 CREATE TABLE rollup_matrix_numeric_6hours (
470     sid integer NOT NULL,
471     name text NOT NULL,
472     rollup_time timestamp with time zone NOT NULL,
473     count_rows integer,
474     avg_value numeric
475 );
476
477
478 ALTER TABLE stratcon.rollup_matrix_numeric_6hours OWNER TO reconnoiter;
479
480 --
481 -- Name: rollup_runner; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
482 --
483
484 CREATE TABLE rollup_runner (
485     rollup_table character varying(100),
486     runner character varying(22)
487 );
488
489
490 ALTER TABLE stratcon.rollup_runner OWNER TO reconnoiter;
491
492 SET search_path = prism, pg_catalog;
493
494 --
495 -- Name: check_name_saved_graphs(); Type: FUNCTION; Schema: prism; Owner: reconnoiter
496 --
497
498 CREATE FUNCTION check_name_saved_graphs() RETURNS trigger
499     AS $$
500 DECLARE
501 BEGIN
502     IF  NEW.saved IS true AND NEW.title IS null THEN
503     RAISE EXCEPTION 'You must name graph to save';
504     END IF;
505  RETURN NEW;
506 END
507 $$
508     LANGUAGE plpgsql;
509
510
511 ALTER FUNCTION prism.check_name_saved_graphs() OWNER TO reconnoiter;
512
513 SET search_path = public, pg_catalog;
514
515 --
516 -- Name: date_hour(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: reconnoiter
517 --
518
519 CREATE FUNCTION date_hour(timestamp with time zone) RETURNS timestamp with time zone
520     AS $_$
521  SELECT date_trunc('hour',$1);
522 $_$
523     LANGUAGE sql IMMUTABLE STRICT;
524
525
526 ALTER FUNCTION public.date_hour(timestamp with time zone) OWNER TO reconnoiter;
527
528 SET search_path = stratcon, pg_catalog;
529
530 --
531 -- Name: choose_window(timestamp without time zone, timestamp without time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
532 --
533
534 CREATE FUNCTION choose_window(in_start_time timestamp without time zone, in_end_time timestamp without time zone, in_hopeful_nperiods integer, OUT tablename text, OUT period interval, OUT nperiods integer) RETURNS SETOF record
535     AS $$
536 declare
537   window record;
538 begin
539   -- Figure out which table we should be looking in
540   for window in
541     select atablename, aperiod, anperiods
542     from (select aperiod, iv/isec as anperiods, atablename,
543                  abs(case when iv/isec - in_hopeful_nperiods < 0
544                           then 10 * (in_hopeful_nperiods - iv/isec)
545                           else iv/isec - in_hopeful_nperiods
546                            end) as badness
547             from (select extract('epoch' from in_end_time) -
548                          extract('epoch' from in_start_time) as iv
549                  ) i,
550                  (   select 5*60 as isec, '5 minutes'::interval as aperiod,
551                             'rollup_matrix_numeric_5m' as atablename
552                   union all
553                      select 20*60 as isec, '20 minutes'::interval as aperiod,
554                             'rollup_matrix_numeric_20m' as atablename
555                   union all
556                      select 60*60 as isec, '1 hour'::interval as aperiod,
557                             'rollup_matrix_numeric_60m' as atablename
558                   union all
559                      select 6*60*60 as isec, '6 hours'::interval as aaperiod,
560                             'rollup_matrix_numeric_6hours' as atablename
561                   union all
562                      select 12*60*60 as isec, '12 hours'::interval as aperiod,
563                             'rollup_matrix_numeric_12hours' as atablename
564                  ) ivs
565          ) b
566  order by badness asc
567   limit 1
568   loop
569     tablename := window.atablename;
570     period := window.aperiod;
571     nperiods := window.anperiods;
572     return next;
573   end loop;
574   return;
575 end
576 $$
577     LANGUAGE plpgsql;
578
579
580 ALTER FUNCTION stratcon.choose_window(in_start_time timestamp without time zone, in_end_time timestamp without time zone, in_hopeful_nperiods integer, OUT tablename text, OUT period interval, OUT nperiods integer) OWNER TO reconnoiter;
581
582 --
583 -- Name: choose_window(timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
584 --
585
586 CREATE FUNCTION choose_window(in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, OUT tablename text, OUT period interval, OUT nperiods integer) RETURNS SETOF record
587     AS $$
588 declare
589   window record;
590 begin
591   -- Figure out which table we should be looking in
592   for window in
593     select atablename, aperiod, anperiods
594     from (select aperiod, round(iv/isec) ::integer as anperiods, atablename,
595                  abs(case when iv/isec - in_hopeful_nperiods < 0
596                           then 10 * (in_hopeful_nperiods - iv/isec)
597                           else iv/isec - in_hopeful_nperiods
598                            end) as badness
599             from (select extract('epoch' from in_end_time) -
600                          extract('epoch' from in_start_time) as iv
601                  ) i,
602                  (   select 5*60 as isec, '5 minutes'::interval as aperiod,
603                             'rollup_matrix_numeric_5m' as atablename
604                   union all
605                      select 20*60 as isec, '20 minutes'::interval as aperiod,
606                             'rollup_matrix_numeric_20m' as atablename
607                   union all
608                      select 60*60 as isec, '1 hour'::interval as aperiod,
609                             'rollup_matrix_numeric_60m' as atablename
610                   union all
611                      select 6*60*60 as isec, '6 hours'::interval as aaperiod,
612                             'rollup_matrix_numeric_6hours' as atablename
613                   union all
614                      select 12*60*60 as isec, '12 hours'::interval as aperiod,
615                             'rollup_matrix_numeric_12hours' as atablename
616                  ) ivs
617          ) b
618  order by badness asc
619   limit 1
620   loop
621     tablename := window.atablename;
622     period := window.aperiod;
623     nperiods := window.anperiods;
624     return next;
625   end loop;
626   return;
627 end
628 $$
629     LANGUAGE plpgsql;
630
631
632 ALTER FUNCTION stratcon.choose_window(in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, OUT tablename text, OUT period interval, OUT nperiods integer) OWNER TO reconnoiter;
633
634 --
635 -- Name: date_hour(timestamp with time zone); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
636 --
637
638 CREATE FUNCTION date_hour(timestamp with time zone) RETURNS timestamp with time zone
639     AS $_$
640  SELECT date_trunc('hour',$1);
641 $_$
642     LANGUAGE sql IMMUTABLE STRICT;
643
644
645 ALTER FUNCTION stratcon.date_hour(timestamp with time zone) OWNER TO reconnoiter;
646
647 --
648 -- Name: fetch_dataset(uuid, text, timestamp with time zone, timestamp with time zone, integer, boolean); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
649 --
650
651 CREATE FUNCTION fetch_dataset(in_uuid uuid, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean) RETURNS SETOF rollup_matrix_numeric_5m
652     AS $$
653 declare
654   v_sid int;
655   v_record stratcon.rollup_matrix_numeric_5m%rowtype;
656 begin
657   select sid into v_sid from stratcon.map_uuid_to_sid where id = in_uuid;
658   if not found then
659     return;
660   end if;
661
662     for v_record in  select sid, name, rollup_time, count_rows, avg_value from stratcon.fetch_dataset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods, derive) loop
663     return next v_record;
664     end loop;
665
666 --  return query select sid, name, rollup_time, count_rows, avg_value from stratcon.fetch_dataset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods, derive);
667   return;
668 end
669 $$
670     LANGUAGE plpgsql;
671
672
673 ALTER FUNCTION stratcon.fetch_dataset(in_uuid uuid, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean) OWNER TO reconnoiter;
674
675 --
676 -- Name: fetch_dataset(integer, text, timestamp with time zone, timestamp with time zone, integer, boolean); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
677 --
678
679 CREATE FUNCTION fetch_dataset(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean) RETURNS SETOF rollup_matrix_numeric_5m
680     AS $$
681 declare
682   v_sql text;
683   v_sid int;
684   v_target record;
685   v_interval numeric;
686   v_start_adj timestamptz;
687   v_end_adj timestamptz;
688   v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
689   v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
690   v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
691 begin
692
693   -- Map out uuid to an sid.
694   v_sid := in_sid;
695
696   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
697
698   if not found then
699     raise exception 'no target table';
700     return;
701   end if;
702
703   select 'epoch'::timestamp +
704          ((floor(extract('epoch' from in_start_time) /
705                  extract('epoch' from v_target.period)) *
706            extract('epoch' from v_target.period)) || ' seconds') ::interval
707     into v_start_adj;
708
709   select 'epoch'::timestamp +
710          ((floor(extract('epoch' from in_end_time) /
711                  extract('epoch' from v_target.period)) *
712            extract('epoch' from v_target.period)) || ' seconds') ::interval
713     into v_end_adj;
714
715   v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' ||
716            's.rollup_time, d.count_rows, d.avg_value ' ||
717            ' from ' ||
718            '(select ' || quote_literal(v_start_adj) || '::timestamp' ||
719                   ' + t * ' || quote_literal(v_target.period) || '::interval' ||
720                        ' as rollup_time' ||
721              ' from generate_series(1,' || v_target.nperiods || ') t) s ' ||
722            'left join ' ||
723            '(select * from stratcon.' || v_target.tablename ||
724            ' where sid = ' || v_sid ||
725              ' and name = ' || quote_literal(in_name) ||
726              ' and rollup_time between ' || quote_literal(v_start_adj) || '::timestamp' ||
727                                  ' and ' || quote_literal(v_end_adj) || '::timestamp) d' ||
728            ' using(rollup_time)';
729
730   for v_rollup_row in execute v_sql loop
731     if derive is true then
732       v_r_rollup_row := v_rollup_row;
733       if v_l_rollup_row.count_rows is not null and
734          v_rollup_row.count_rows is not null then
735         v_interval := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time);
736         v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2;
737         v_r_rollup_row.avg_value :=
738           (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval;
739       else
740         v_r_rollup_row.count_rows = NULL;
741         v_r_rollup_row.avg_value = NULL;
742        
743       end if;
744     else
745       v_r_rollup_row := v_rollup_row;
746     end if;
747     return next v_r_rollup_row;
748     v_l_rollup_row := v_rollup_row;
749   end loop;
750   return;
751 end
752 $$
753     LANGUAGE plpgsql;
754
755
756 ALTER FUNCTION stratcon.fetch_dataset(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer, derive boolean) OWNER TO reconnoiter;
757
758 --
759 -- Name: fetch_varset(uuid, text, timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
760 --
761
762 CREATE FUNCTION fetch_varset(in_check uuid, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer) RETURNS SETOF loading_dock_metric_text_s_change_log
763     AS $$
764 declare
765   v_sid int;
766 begin
767   -- Map out uuid to an sid.
768   select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check;
769   if not found then
770     return;
771   end if;
772
773   return query select * from stratcon.fetch_varset(v_sid::integer, in_name, in_start_time, in_end_time, in_hopeful_nperiods);
774 end
775 $$
776     LANGUAGE plpgsql;
777
778
779 ALTER FUNCTION stratcon.fetch_varset(in_check uuid, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer) OWNER TO reconnoiter;
780
781 --
782 -- Name: fetch_varset(integer, text, timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
783 --
784
785 CREATE FUNCTION fetch_varset(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer) RETURNS SETOF loading_dock_metric_text_s_change_log
786     AS $$
787 declare
788   v_sid int;
789   v_target record;
790   v_start_adj timestamptz;
791   v_start_text text;
792   v_next_text text;
793   v_end_adj timestamptz;
794   v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype;
795 begin
796   -- Map out uuid to an sid.
797   v_sid := in_sid;
798
799   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
800
801   select 'epoch'::timestamp +
802          ((floor(extract('epoch' from in_start_time) /
803                  extract('epoch' from v_target.period)) *
804            extract('epoch' from v_target.period)) || ' seconds') ::interval
805     into v_start_adj;
806
807   select 'epoch'::timestamp +
808          ((floor(extract('epoch' from in_end_time) /
809                  extract('epoch' from v_target.period)) *
810            extract('epoch' from v_target.period)) || ' seconds') ::interval
811     into v_end_adj;
812
813   for v_change_row in
814     select sid, 'epoch'::timestamp +
815          ((floor(extract('epoch' from whence) /
816                  extract('epoch' from v_target.period)) *
817            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
818            name, value
819       from stratcon.loading_dock_metric_text_s_change_log
820      where sid = v_sid
821        and name = in_name
822        and whence <= v_start_adj
823   order by 'epoch'::timestamp +
824          ((floor(extract('epoch' from whence) /
825                  extract('epoch' from v_target.period)) *
826            extract('epoch' from v_target.period)) || ' seconds') ::interval desc
827      limit 1
828   loop
829     v_start_text := coalesce(v_change_row.value, '[unset]');
830   end loop;
831
832   for v_change_row in
833     select v_sid as sid, whence, in_name as name, value from
834 --    (select v_start_adj::timestamp + t * v_target.period::interval as whence
835 --      from generate_series(1, v_target.nperiods) t) s
836 -- left join
837     (select 'epoch'::timestamp +
838          ((floor(extract('epoch' from whence) /
839                  extract('epoch' from v_target.period)) *
840            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
841            coalesce(value, '[unset]') as value
842       from stratcon.loading_dock_metric_text_s_change_log
843      where sid = v_sid
844        and name = in_name
845        and whence > v_start_adj
846        and whence <= v_end_adj) d
847 --    using (whence)
848   order by whence asc
849   loop
850     v_next_text := v_change_row.value;
851     if v_change_row.value is not null and
852        v_start_text != v_change_row.value then
853       v_change_row.value := coalesce(v_start_text, '[unset]') || ' -> ' || coalesce(v_change_row.value, '[unset]');
854     else
855       v_change_row.value := v_start_text;
856     end if;
857     if v_next_text is not null then
858       v_start_text := v_next_text;
859     end if;
860     return next v_change_row;
861   end loop;
862
863   return;
864 end
865 $$
866     LANGUAGE plpgsql;
867
868
869 ALTER FUNCTION stratcon.fetch_varset(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer) OWNER TO reconnoiter;
870
871 --
872 -- Name: generate_sid_from_id(uuid); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
873 --
874
875 CREATE FUNCTION generate_sid_from_id(v_in_id uuid) RETURNS integer
876     AS $$
877 DECLARE
878    v_ex_sid integer;
879    v_new_sid integer;
880  
881 BEGIN
882
883 SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id
884   INTO v_ex_sid;
885
886  IF NOT FOUND THEN
887     SELECT nextval('stratcon.seq_sid')
888     INTO v_new_sid;
889
890     INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid);
891        
892    
893     RETURN v_new_sid;
894  ELSE
895       RETURN v_ex_sid;
896  END IF;
897
898 END
899 $$
900     LANGUAGE plpgsql;
901
902
903 ALTER FUNCTION stratcon.generate_sid_from_id(v_in_id uuid) OWNER TO reconnoiter;
904
905 --
906 -- Name: loading_dock_metric_numeric_s_whence_log(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
907 --
908
909 CREATE FUNCTION loading_dock_metric_numeric_s_whence_log() RETURNS trigger
910     AS $$
911 DECLARE
912 v_whence timestamptz;
913 v_whence_5 timestamptz;
914 v_sid integer;
915 v_name text;
916 BEGIN
917 IF TG_OP = 'INSERT' THEN
918  
919  v_whence_5:=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval;
920  
921    SELECT whence FROM stratcon.log_whence_s WHERE whence=v_whence_5 and interval='5 minutes'
922      INTO v_whence;
923      
924    IF NOT FOUND THEN
925       BEGIN
926        INSERT INTO  stratcon.log_whence_s VALUES(v_whence_5,'5 minutes');
927        EXCEPTION
928         WHEN UNIQUE_VIOLATION THEN
929         -- do nothing
930       END;
931     END IF;
932
933    SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid  and metric_name=NEW.name
934      INTO v_sid,v_name;
935    IF NOT FOUND THEN
936        INSERT INTO  stratcon.metric_name_summary VALUES(NEW.sid,NEW.name,'numeric');
937     END IF;
938
939 END IF;
940     RETURN NULL;
941 END
942 $$
943     LANGUAGE plpgsql;
944
945
946 ALTER FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() OWNER TO reconnoiter;
947
948 --
949 -- Name: loading_dock_metric_text_s_change_log(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
950 --
951
952 CREATE FUNCTION loading_dock_metric_text_s_change_log() RETURNS trigger
953     AS $$
954 DECLARE
955     v_oldvalue text;
956     v_sid integer;
957     v_name text;
958     v_value text;
959     v_whence timestamptz;
960     v_old_whence timestamptz;
961     v_old_name text;
962     v_old_sid integer;
963     v_old_value text;
964     v_max_whence timestamptz;
965 BEGIN
966
967 IF TG_OP = 'INSERT' THEN
968
969              SELECT value FROM  stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name
970                  AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log
971                                  WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name )
972                      INTO v_oldvalue;
973
974                     IF v_oldvalue IS DISTINCT FROM NEW.value THEN
975
976                         INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value)
977                             VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value);
978                         DELETE FROM stratcon.current_metric_text
979                                 WHERE sid = NEW.sid and name = NEW.name;
980                         INSERT INTO stratcon.current_metric_text (sid,whence,name,value)
981                                 VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value);
982                     END IF;
983
984
985 SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid  and metric_name=NEW.name
986         INTO v_sid,v_name;
987      IF NOT FOUND THEN
988           INSERT INTO  stratcon.metric_name_summary(sid,metric_name,metric_type)  VALUES(NEW.sid,NEW.name,'text');
989      END IF;
990
991 ELSE
992         RAISE EXCEPTION 'something wrong with stratcon.loading_dock_metric_text_s_change_log ';
993 END IF;
994
995     RETURN NULL;
996
997 END
998 $$
999     LANGUAGE plpgsql;
1000
1001
1002 ALTER FUNCTION stratcon.loading_dock_metric_text_s_change_log() OWNER TO reconnoiter;
1003
1004 --
1005 -- Name: loading_dock_status_s_change_log(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
1006 --
1007
1008 CREATE FUNCTION loading_dock_status_s_change_log() RETURNS trigger
1009     AS $$
1010 DECLARE
1011     v_state CHAR(1);
1012     v_avail CHAR(1);
1013 BEGIN
1014
1015 IF TG_OP = 'INSERT' THEN
1016     SELECT state,availability FROM  stratcon.loading_dock_status_s WHERE sid = NEW.sid
1017         AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_status_s_change_log
1018                         WHERE  SID=NEW.sid and  WHENCE <> NEW.whence )
1019     INTO v_state,v_avail;
1020
1021     IF v_state IS DISTINCT FROM NEW.state OR v_avail IS DISTINCT FROM NEW.availability THEN
1022
1023         INSERT INTO stratcon.loading_dock_status_s_change_log (sid,whence,state,availability,duration,status)
1024             VALUES (NEW.sid,NEW.whence,NEW.state,NEW.availability,NEW.duration,NEW.status);
1025
1026     END IF;
1027
1028 ELSE
1029         RAISE EXCEPTION 'Something wrong with stratcon.loading_dock_status_s_change_log';
1030 END IF;
1031
1032     RETURN NULL;
1033
1034 END
1035 $$
1036     LANGUAGE plpgsql;
1037
1038
1039 ALTER FUNCTION stratcon.loading_dock_status_s_change_log() OWNER TO reconnoiter;
1040
1041 --
1042 -- Name: mv_loading_dock_check_s(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
1043 --
1044
1045 CREATE FUNCTION mv_loading_dock_check_s() RETURNS trigger
1046     AS $$
1047 DECLARE
1048     v_remote_address INET;
1049     v_target TEXT;
1050     v_module TEXT;
1051     v_name TEXT;
1052 BEGIN
1053
1054 IF TG_OP = 'INSERT' THEN
1055     SELECT remote_address,target,module,name FROM  stratcon.mv_loading_dock_check_s WHERE sid = NEW.sid AND id=NEW.id
1056         INTO v_remote_address,v_target,v_module,v_name;
1057
1058     IF v_remote_address IS DISTINCT FROM NEW.remote_address OR v_target IS DISTINCT FROM NEW.target OR v_name IS DISTINCT FROM NEW.name   THEN
1059        
1060         DELETE from stratcon.mv_loading_dock_check_s WHERE sid = NEW.sid AND id=NEW.id;
1061        
1062         INSERT INTO stratcon.mv_loading_dock_check_s (sid,remote_address,whence,id,target,module,name)
1063             VALUES (NEW.sid,NEW.remote_address,NEW.whence,NEW.id,NEW.target,NEW.module,NEW.name);
1064
1065     END IF;
1066
1067 ELSE
1068         RAISE EXCEPTION 'Something wrong with stratcon.mv_loading_dock_check_s';
1069 END IF;
1070
1071     RETURN NULL;
1072
1073 END
1074 $$
1075     LANGUAGE plpgsql;
1076
1077
1078 ALTER FUNCTION stratcon.mv_loading_dock_check_s() OWNER TO reconnoiter;
1079
1080 --
1081 -- Name: remove_metric(uuid, text, text); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
1082 --
1083
1084 CREATE FUNCTION remove_metric(in_uuid uuid, in_metric_name text, v_debug text, OUT v_out text) RETURNS text
1085     AS $$
1086 DECLARE
1087 v_del_sid INT;
1088 v_del_metric_name TEXT;
1089 v_del_metric_type TEXT;
1090 deleted_t INT;
1091 deleted_tc INT;
1092 deleted_n INT;
1093 deleted_5 INT;
1094 deleted_20 INT;
1095 deleted_60 INT;
1096 deleted_6h INT;
1097 deleted_12h INT;
1098 deleted_sum INT;
1099
1100 BEGIN
1101   SELECT s.sid,m.metric_name,m.metric_type
1102     FROM
1103              stratcon.map_uuid_to_sid s,
1104              stratcon.metric_name_summary m
1105        WHERE s.id=in_uuid
1106              and s.sid=m.sid
1107              and m.metric_name=in_metric_name
1108   INTO v_del_sid,v_del_metric_name,v_del_metric_type;
1109 IF NOT FOUND THEN
1110    IF v_debug = 'DEBUG' THEN
1111      RAISE NOTICE 'Given UUID can not map to SID,Metric Name: %,%',in_uuid,in_metric_name;
1112    END IF;
1113    v_out:='Please Supply Valid UUID,Metric Name Combination :'||in_uuid||','||in_metric_name;
1114  RETURN;
1115 END IF;
1116 IF v_debug = 'DEBUG' THEN
1117         RAISE NOTICE 'Delete In Progress For: %,%,%',v_del_sid,v_del_metric_name,v_del_metric_type;
1118 END IF;
1119
1120 -- Check of Text or Numeric Type
1121 IF v_del_metric_type ='text' THEN
1122  -- Delete from Metrix Tex table
1123   DELETE FROM stratcon.loading_dock_metric_text_s WHERE sid=v_del_sid AND name=v_del_metric_name;
1124      GET DIAGNOSTICS deleted_t = ROW_COUNT;
1125      IF v_debug = 'DEBUG' THEN
1126            RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_text_s : %',deleted;
1127      END IF;
1128  -- Delete from Metrix Change Log table
1129   DELETE FROM stratcon.loading_dock_metric_text_s_change_log WHERE sid=v_del_sid AND name=v_del_metric_name;
1130      GET DIAGNOSTICS deleted_tc = ROW_COUNT;
1131      IF v_debug = 'DEBUG' THEN
1132           RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_text_s_change_log : %',deleted;
1133      END IF;
1134  ELSE
1135   -- Delete from Metrix Numeric table
1136    DELETE FROM stratcon.loading_dock_metric_numeric_s WHERE sid=v_del_sid AND name=v_del_metric_name;
1137    GET DIAGNOSTICS deleted_n = ROW_COUNT;
1138      IF v_debug = 'DEBUG' THEN
1139          RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_numeric_s : %',deleted;
1140      END IF;
1141   -- Delete from Rollup tables
1142    DELETE FROM stratcon.rollup_matrix_numeric_5m WHERE sid=v_del_sid AND name=v_del_metric_name;
1143    GET DIAGNOSTICS deleted_5 = ROW_COUNT;   
1144      IF v_debug = 'DEBUG' THEN
1145          RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_5m : %',deleted;
1146      END IF;
1147    DELETE FROM stratcon.rollup_matrix_numeric_20m WHERE sid=v_del_sid AND name=v_del_metric_name;
1148       GET DIAGNOSTICS deleted_20= ROW_COUNT;     
1149         IF v_debug = 'DEBUG' THEN
1150             RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_20m : %',deleted;
1151         END IF;
1152    DELETE FROM stratcon.rollup_matrix_numeric_60m WHERE sid=v_del_sid AND name=v_del_metric_name;
1153       GET DIAGNOSTICS deleted_60 = ROW_COUNT;     
1154         IF v_debug = 'DEBUG' THEN
1155             RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_60m : %',deleted;
1156         END IF;
1157    DELETE FROM stratcon.rollup_matrix_numeric_6hours WHERE sid=v_del_sid AND name=v_del_metric_name;
1158       GET DIAGNOSTICS deleted_6h = ROW_COUNT;     
1159         IF v_debug = 'DEBUG' THEN
1160             RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_6hours : %',deleted;
1161         END IF;
1162    DELETE FROM stratcon.rollup_matrix_numeric_12hours WHERE sid=v_del_sid AND name=v_del_metric_name;
1163       GET DIAGNOSTICS deleted_12h = ROW_COUNT;     
1164         IF v_debug = 'DEBUG' THEN
1165             RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_12hours : %',deleted;
1166         END IF;
1167 END IF;
1168   -- Delete from metrix summary table
1169    DELETE FROM stratcon.metrix_name_summary WHERE sid=v_del_sid AND metric_name=v_del_metric_name;
1170       GET DIAGNOSTICS deleted_sum= ROW_COUNT;     
1171         IF v_debug = 'DEBUG' THEN
1172             RAISE NOTICE 'DELELTED ROWS FROM metric_name_summary : %',deleted;
1173         END IF;
1174  v_out:='Deleted Rows for Metric_Text, Metrix_Text_change_log,Metric_Numeric,Rollup_5m,Rollup_20m,Rollup_1hour,Rollup_6hours,Rollup_12hours,Metric_Summary:'||deleted_t||','||deleted_tc||','||deleted_n||','||deleted_5||','||deleted_20||','||deleted_60||','||deleted_6h||','||deleted_12h||','||deleted_sum;
1175 RETURN;
1176    EXCEPTION
1177     WHEN RAISE_EXCEPTION THEN
1178             RAISE EXCEPTION '%', SQLERRM;
1179     WHEN OTHERS THEN
1180             RAISE NOTICE '%', SQLERRM;
1181 END
1182 $$
1183     LANGUAGE plpgsql;
1184
1185
1186 ALTER FUNCTION stratcon.remove_metric(in_uuid uuid, in_metric_name text, v_debug text, OUT v_out text) OWNER TO reconnoiter;
1187
1188 --
1189 -- Name: rollup_matrix_numeric_12hours(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
1190 --
1191
1192 CREATE FUNCTION rollup_matrix_numeric_12hours() RETURNS void
1193     AS $$
1194 DECLARE
1195   rec stratcon.rollup_matrix_numeric_12hours%rowtype;
1196   v_sql TEXT;
1197   v_min_whence TIMESTAMPTZ;
1198   v_max_rollup_12 TIMESTAMPTZ;
1199   v_whence TIMESTAMPTZ;
1200   v_nrunning INT;
1201   v_self VARCHAR(22);
1202   whenceint RECORD;
1203  
1204 BEGIN
1205
1206   SELECT COUNT(1) INTO v_nrunning
1207     from stratcon.rollup_runner t, pg_stat_activity a
1208     where rollup_table ='rollup_matrix_numeric_12hours'
1209      and runner = procpid || '.' || date_part('epoch',backend_start);
1210
1211   IF v_nrunning > 0 THEN
1212     RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running';
1213     RETURN ;
1214   END IF;
1215
1216   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
1217     FROM pg_stat_activity
1218      WHERE procpid = pg_backend_pid();
1219
1220   IF v_self IS NULL THEN
1221     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot self-identify';
1222    END IF;
1223
1224    v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_12hours''';
1225
1226   EXECUTE v_sql;
1227
1228  FOR whenceint IN  SELECT * FROM stratcon.log_whence_s WHERE interval='12 hours' LOOP
1229  
1230   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='12 hours'
1231          INTO v_min_whence;
1232          
1233   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_12hours
1234          INTO v_max_rollup_12;   
1235
1236 /*-- Insert Log for 24 Hours rollup
1237    
1238    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/24)*24) * '1 hour'::interval and interval='24 hours'
1239            INTO v_whence;
1240       IF NOT FOUND THEN
1241        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/24)*24) * '1 hour'::interval,'24 hours');
1242    END IF;
1243    */
1244    
1245   IF v_min_whence <= v_max_rollup_12 THEN
1246  
1247   DELETE FROM stratcon.rollup_matrix_numeric_12hours
1248        WHERE rollup_time= v_min_whence;
1249
1250   END IF;
1251  
1252     FOR rec IN
1253                 SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value
1254          FROM stratcon.rollup_matrix_numeric_6hours
1255            WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'12 hour'::interval
1256                    GROUP BY sid,name
1257         LOOP
1258      
1259        
1260           INSERT INTO stratcon.rollup_matrix_numeric_12hours
1261           (sid,name,rollup_time,count_rows,avg_value) VALUES
1262           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value);
1263          
1264      END LOOP;
1265
1266
1267 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='12 hours';
1268
1269 v_min_whence := NULL;
1270 v_max_rollup_12 := NULL;
1271
1272 END LOOP;
1273
1274 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_12hours';
1275
1276 RETURN;
1277 EXCEPTION
1278     WHEN RAISE_EXCEPTION THEN
1279       UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours';
1280       RAISE EXCEPTION '%', SQLERRM;
1281     WHEN OTHERS THEN
1282       RAISE NOTICE '%', SQLERRM;
1283 END
1284 $$
1285     LANGUAGE plpgsql;
1286
1287
1288 ALTER FUNCTION stratcon.rollup_matrix_numeric_12hours() OWNER TO reconnoiter;
1289
1290 --
1291 -- Name: rollup_matrix_numeric_20m(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
1292 --
1293
1294 CREATE FUNCTION rollup_matrix_numeric_20m() RETURNS void
1295     AS $$
1296 DECLARE
1297  
1298  rec stratcon.rollup_matrix_numeric_20m%rowtype;
1299  v_sql TEXT;
1300  v_min_whence TIMESTAMPTZ;
1301  v_max_rollup_20 TIMESTAMPTZ;
1302  v_whence TIMESTAMPTZ;
1303  rows INT;
1304  v_nrunning INT;
1305  v_self VARCHAR(22);
1306  whenceint RECORD;
1307 BEGIN
1308
1309   SELECT COUNT(1) INTO v_nrunning
1310     from stratcon.rollup_runner t, pg_stat_activity a
1311    where rollup_table ='rollup_matrix_numeric_20m'
1312      and runner = procpid || '.' || date_part('epoch',backend_start);
1313
1314   IF v_nrunning > 0 THEN
1315     RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running';
1316     RETURN ;
1317   END IF;
1318
1319   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
1320     FROM pg_stat_activity
1321    WHERE procpid = pg_backend_pid();
1322
1323   IF v_self IS NULL THEN
1324     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot self-identify';
1325   END IF;
1326
1327   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_20m''';
1328
1329   EXECUTE v_sql;
1330
1331 FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='20 minutes' LOOP
1332
1333  SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='20 minutes'
1334         INTO v_min_whence;
1335        
1336  SELECT MAX(rollup_time) FROM  stratcon.rollup_matrix_numeric_20m
1337          INTO v_max_rollup_20;       
1338  
1339  -- Insert Log for Hourly rollup
1340    
1341    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour'
1342            INTO v_whence;
1343       IF NOT FOUND THEN
1344        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour');
1345    END IF;
1346    
1347  IF v_min_whence <= v_max_rollup_20 THEN
1348
1349    DELETE FROM stratcon.rollup_matrix_numeric_20m
1350                 WHERE rollup_time = v_min_whence;
1351  
1352  END IF;
1353
1354  FOR rec IN
1355                 SELECT sid , name,v_min_whence as rollup_time,
1356                        SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value
1357        FROM stratcon.rollup_matrix_numeric_5m
1358                       WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence -'20 minutes'::interval
1359                 GROUP BY sid,name
1360  
1361        LOOP
1362    
1363        
1364         INSERT INTO stratcon.rollup_matrix_numeric_20m
1365          (sid,name,rollup_time,count_rows,avg_value) VALUES
1366          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value);
1367        
1368    END LOOP;
1369
1370   -- Delete from whence log table
1371  
1372   DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='20 minutes';
1373  
1374   v_min_whence:= NULL;
1375   v_max_rollup_20:= NULL;
1376
1377  END LOOP;
1378  
1379   UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m';
1380  
1381 RETURN;
1382
1383 EXCEPTION
1384     WHEN RAISE_EXCEPTION THEN
1385        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m';
1386        RAISE EXCEPTION '%', SQLERRM;
1387     WHEN OTHERS THEN
1388       RAISE NOTICE '%', SQLERRM;
1389 END
1390 $$
1391     LANGUAGE plpgsql;
1392
1393
1394 ALTER FUNCTION stratcon.rollup_matrix_numeric_20m() OWNER TO reconnoiter;
1395
1396 --
1397 -- Name: rollup_matrix_numeric_5m(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
1398 --
1399
1400 CREATE FUNCTION rollup_matrix_numeric_5m() RETURNS void
1401     AS $$
1402 DECLARE
1403  
1404  rec stratcon.rollup_matrix_numeric_5m%rowtype;
1405  v_sql TEXT;
1406  v_min_whence TIMESTAMPTZ;
1407  v_max_rollup_5 TIMESTAMPTZ;
1408  v_whence TIMESTAMPTZ;
1409  rows INT;
1410  v_nrunning INT;
1411  v_self VARCHAR(22);
1412  whenceint RECORD;
1413 BEGIN
1414
1415   SELECT COUNT(1) INTO v_nrunning
1416     from stratcon.rollup_runner t, pg_stat_activity a
1417    where rollup_table ='rollup_matrix_numeric_5m'
1418      and runner = procpid || '.' || date_part('epoch',backend_start);
1419
1420   IF v_nrunning > 0 THEN
1421     RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running';
1422     RETURN ;
1423   END IF;
1424
1425   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
1426     FROM pg_stat_activity
1427    WHERE procpid = pg_backend_pid();
1428
1429   IF v_self IS NULL THEN
1430     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot self-identify';
1431   END IF;
1432
1433   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_5m''';
1434
1435   EXECUTE v_sql;
1436
1437 FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='5 minutes' LOOP
1438        
1439
1440  SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='5 minutes'
1441         INTO v_min_whence;
1442        
1443  SELECT MAX(rollup_time) FROM  stratcon.rollup_matrix_numeric_5m
1444          INTO v_max_rollup_5;       
1445  
1446  -- Insert Log for 20 minutes rollup
1447    
1448    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/20)*20) * '1 minute'::interval and interval='20 minutes'
1449            INTO v_whence;
1450       IF NOT FOUND THEN
1451        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence) + (round(extract('minute' from v_min_whence)/20)*20) * '1 minute'::interval,'20 minutes');
1452    END IF;
1453  IF v_min_whence <= v_max_rollup_5 THEN
1454
1455    DELETE FROM stratcon.rollup_matrix_numeric_5m
1456                 WHERE rollup_time = v_min_whence;
1457  
1458  END IF;
1459
1460  FOR rec IN
1461                 SELECT sid , name,v_min_whence as rollup_time,
1462                       COUNT(1) as count_rows ,AVG(value) as avg_value
1463                       FROM stratcon.loading_dock_metric_numeric_s
1464                       WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval
1465                 GROUP BY rollup_time,sid,name
1466  
1467        LOOP
1468    
1469        
1470         INSERT INTO stratcon.rollup_matrix_numeric_5m
1471          (sid,name,rollup_time,count_rows,avg_value) VALUES
1472          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value);
1473        
1474    END LOOP;
1475
1476   -- Delete from whence log table
1477  
1478   DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes';
1479  
1480  v_min_whence:= NULL;
1481  v_max_rollup_5:= NULL;
1482  
1483  END LOOP;
1484  
1485   UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m';
1486  
1487 RETURN;
1488
1489 EXCEPTION
1490     WHEN RAISE_EXCEPTION THEN
1491        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m';
1492        RAISE EXCEPTION '%', SQLERRM;
1493     WHEN OTHERS THEN
1494          RAISE NOTICE '%', SQLERRM;
1495 END
1496 $$
1497     LANGUAGE plpgsql;
1498
1499
1500 ALTER FUNCTION stratcon.rollup_matrix_numeric_5m() OWNER TO reconnoiter;
1501
1502 --
1503 -- Name: rollup_matrix_numeric_60m(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
1504 --
1505
1506 CREATE FUNCTION rollup_matrix_numeric_60m() RETURNS void
1507     AS $$
1508 DECLARE
1509   rec stratcon.rollup_matrix_numeric_60m%rowtype;
1510   v_sql TEXT;
1511   v_min_whence TIMESTAMPTZ;
1512   v_max_rollup_60 TIMESTAMPTZ;
1513   v_whence TIMESTAMPTZ;
1514   v_nrunning INT;
1515   v_self VARCHAR(22);
1516   whenceint RECORD;
1517 BEGIN
1518
1519   SELECT COUNT(1) INTO v_nrunning
1520     from stratcon.rollup_runner t, pg_stat_activity a
1521    where rollup_table ='rollup_matrix_numeric_60m'
1522      and runner = procpid || '.' || date_part('epoch',backend_start);
1523
1524   IF v_nrunning > 0 THEN
1525     RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running';
1526     RETURN ;
1527   END IF;
1528
1529   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
1530     FROM pg_stat_activity
1531    WHERE procpid = pg_backend_pid();
1532
1533   IF v_self IS NULL THEN
1534     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot self-identify';
1535   END IF;
1536
1537   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_60m''';
1538
1539   EXECUTE v_sql;
1540
1541 FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='1 hour' LOOP
1542            
1543   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='1 hour'
1544          INTO v_min_whence;
1545          
1546   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_60m
1547          INTO v_max_rollup_60;   
1548
1549 -- Insert Log for 6 Hour rollup
1550    
1551    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/6)*6) * '1 hour'::interval and interval='6 hours'
1552            INTO v_whence;
1553       IF NOT FOUND THEN
1554        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/6)*6) * '1 hour'::interval,'6 hours');
1555    END IF;
1556    
1557    
1558   IF v_min_whence <= v_max_rollup_60 THEN
1559  
1560   DELETE FROM stratcon.rollup_matrix_numeric_60m
1561        WHERE rollup_time= v_min_whence;
1562
1563   END IF;
1564  
1565     FOR rec IN
1566                 SELECT sid,name,date_hour(rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value
1567          FROM stratcon.rollup_matrix_numeric_20m
1568            WHERE date_hour(rollup_time)= v_min_whence
1569                    GROUP BY date_hour(rollup_time),sid,name
1570         LOOP
1571      
1572           INSERT INTO stratcon.rollup_matrix_numeric_60m
1573           (sid,name,rollup_time,count_rows,avg_value) VALUES
1574           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value);
1575          
1576      END LOOP;
1577
1578
1579 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='1 hour';
1580
1581 v_min_whence := NULL;
1582 v_max_rollup_60 := NULL;
1583
1584 END LOOP;
1585
1586 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m';
1587
1588 RETURN;
1589
1590 EXCEPTION
1591     WHEN RAISE_EXCEPTION THEN
1592        RAISE EXCEPTION '%', SQLERRM;
1593     WHEN OTHERS THEN
1594       RAISE NOTICE '%', SQLERRM;
1595 END
1596 $$
1597     LANGUAGE plpgsql;
1598
1599
1600 ALTER FUNCTION stratcon.rollup_matrix_numeric_60m() OWNER TO reconnoiter;
1601
1602 --
1603 -- Name: rollup_matrix_numeric_6hours(); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
1604 --
1605
1606 CREATE FUNCTION rollup_matrix_numeric_6hours() RETURNS void
1607     AS $$
1608 DECLARE
1609   rec stratcon.rollup_matrix_numeric_6hours%rowtype;
1610   v_sql TEXT;
1611   v_min_whence TIMESTAMPTZ;
1612   v_max_rollup_6 TIMESTAMPTZ;
1613   v_whence TIMESTAMPTZ;
1614   v_nrunning INT;
1615   v_self VARCHAR(22);
1616   whenceint RECORD; 
1617 BEGIN
1618
1619   SELECT COUNT(1) INTO v_nrunning
1620     from stratcon.rollup_runner t, pg_stat_activity a
1621     where rollup_table ='rollup_matrix_numeric_6hours'
1622      and runner = procpid || '.' || date_part('epoch',backend_start);
1623
1624   IF v_nrunning > 0 THEN
1625     RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running';
1626     RETURN ;
1627   END IF;
1628
1629   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
1630     FROM pg_stat_activity
1631      WHERE procpid = pg_backend_pid();
1632
1633   IF v_self IS NULL THEN
1634     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot self-identify';
1635    END IF;
1636
1637    v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_6hours''';
1638
1639   EXECUTE v_sql;
1640
1641 FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='6 hours' LOOP
1642
1643   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='6 hours'
1644          INTO v_min_whence;
1645          
1646   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_6hours
1647          INTO v_max_rollup_6;   
1648
1649 -- Insert Log for 12 Hours rollup
1650    
1651    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/12)*12) * '1 hour'::interval and interval='12 hours'
1652            INTO v_whence;
1653       IF NOT FOUND THEN
1654        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('day', v_min_whence) + (floor(extract('hour' from v_min_whence)/12)*12) * '1 hour'::interval,'12 hours');
1655    END IF;
1656    
1657    
1658   IF v_min_whence <= v_max_rollup_6 THEN
1659  
1660   DELETE FROM stratcon.rollup_matrix_numeric_6hours
1661        WHERE rollup_time= v_min_whence;
1662
1663   END IF;
1664  
1665     FOR rec IN
1666                 SELECT sid,name,v_min_whence as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value
1667          FROM stratcon.rollup_matrix_numeric_60m
1668            WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval
1669                    GROUP BY sid,name
1670         LOOP
1671      
1672        
1673           INSERT INTO stratcon.rollup_matrix_numeric_6hours
1674           (sid,name,rollup_time,count_rows,avg_value) VALUES
1675           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value);
1676          
1677      END LOOP;
1678
1679
1680 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='6 hours';
1681 v_min_whence := NULL;
1682 v_max_rollup_6 := NULL;
1683
1684 END LOOP;
1685
1686 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';
1687
1688 RETURN;
1689
1690 EXCEPTION
1691     WHEN RAISE_EXCEPTION THEN
1692        RAISE EXCEPTION '%', SQLERRM;
1693     WHEN OTHERS THEN
1694        RAISE NOTICE '%', SQLERRM;
1695 END
1696 $$
1697     LANGUAGE plpgsql;
1698
1699
1700 ALTER FUNCTION stratcon.rollup_matrix_numeric_6hours() OWNER TO reconnoiter;
1701
1702 --
1703 -- Name: update_config(inet, text, timestamp with time zone, xml); Type: FUNCTION; Schema: stratcon; Owner: reconnoiter
1704 --
1705
1706 CREATE FUNCTION update_config(v_remote_address_in inet, v_node_type_in text, v_whence_in timestamp with time zone, v_config_in xml) RETURNS void
1707     AS $$
1708 DECLARE
1709     v_config xml;
1710 BEGIN
1711     select config into v_config from stratcon.current_node_config
1712      where remote_address = v_remote_address_in
1713        and node_type = v_node_type_in;
1714     IF FOUND THEN
1715         IF v_config::text = v_config_in::text THEN
1716             RETURN;
1717         END IF;
1718         delete from stratcon.current_node_config
1719               where remote_address = v_remote_address_in
1720                 and node_type = v_node_type_in;
1721     END IF;
1722     insert into stratcon.current_node_config
1723                 (remote_address, node_type, whence, config)
1724          values (v_remote_address_in, v_node_type_in, v_whence_in, v_config_in);
1725     insert into stratcon.current_node_config_changelog
1726                 (remote_address, node_type, whence, config)
1727          values (v_remote_address_in, v_node_type_in, v_whence_in, v_config_in);
1728 END
1729 $$
1730     LANGUAGE plpgsql;
1731
1732
1733 ALTER FUNCTION stratcon.update_config(v_remote_address_in inet, v_node_type_in text, v_whence_in timestamp with time zone, v_config_in xml) OWNER TO reconnoiter;
1734
1735 --
1736 -- Name: seq_sid; Type: SEQUENCE; Schema: stratcon; Owner: reconnoiter
1737 --
1738
1739 CREATE SEQUENCE seq_sid
1740     INCREMENT BY 1
1741     NO MAXVALUE
1742     NO MINVALUE
1743     CACHE 1;
1744
1745
1746 ALTER TABLE stratcon.seq_sid OWNER TO reconnoiter;
1747
1748 SET search_path = prism, pg_catalog;
1749
1750 --
1751 -- Name: saved_graphs_dep_pkey; Type: CONSTRAINT; Schema: prism; Owner: reconnoiter; Tablespace:
1752 --
1753
1754 ALTER TABLE ONLY saved_graphs_dep
1755     ADD CONSTRAINT saved_graphs_dep_pkey PRIMARY KEY (graphid, sid, metric_name);
1756
1757
1758 --
1759 -- Name: saved_graphs_pkey; Type: CONSTRAINT; Schema: prism; Owner: reconnoiter; Tablespace:
1760 --
1761
1762 ALTER TABLE ONLY saved_graphs
1763     ADD CONSTRAINT saved_graphs_pkey PRIMARY KEY (graphid);
1764
1765
1766 SET search_path = public, pg_catalog;
1767
1768 --
1769 -- Name: pga_diagrams_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
1770 --
1771
1772 ALTER TABLE ONLY pga_diagrams
1773     ADD CONSTRAINT pga_diagrams_pkey PRIMARY KEY (diagramname);
1774
1775
1776 --
1777 -- Name: pga_forms_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
1778 --
1779
1780 ALTER TABLE ONLY pga_forms
1781     ADD CONSTRAINT pga_forms_pkey PRIMARY KEY (formname);
1782
1783
1784 --
1785 -- Name: pga_graphs_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
1786 --
1787
1788 ALTER TABLE ONLY pga_graphs
1789     ADD CONSTRAINT pga_graphs_pkey PRIMARY KEY (graphname);
1790
1791
1792 --
1793 -- Name: pga_images_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
1794 --
1795
1796 ALTER TABLE ONLY pga_images
1797     ADD CONSTRAINT pga_images_pkey PRIMARY KEY (imagename);
1798
1799
1800 --
1801 -- Name: pga_layout_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
1802 --
1803
1804 ALTER TABLE ONLY pga_layout
1805     ADD CONSTRAINT pga_layout_pkey PRIMARY KEY (tablename);
1806
1807
1808 --
1809 -- Name: pga_queries_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
1810 --
1811
1812 ALTER TABLE ONLY pga_queries
1813     ADD CONSTRAINT pga_queries_pkey PRIMARY KEY (queryname);
1814
1815
1816 --
1817 -- Name: pga_reports_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
1818 --
1819
1820 ALTER TABLE ONLY pga_reports
1821     ADD CONSTRAINT pga_reports_pkey PRIMARY KEY (reportname);
1822
1823
1824 --
1825 -- Name: pga_scripts_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
1826 --
1827
1828 ALTER TABLE ONLY pga_scripts
1829     ADD CONSTRAINT pga_scripts_pkey PRIMARY KEY (scriptname);
1830
1831
1832 SET search_path = stratcon, pg_catalog;
1833
1834 --
1835 -- Name: current_metric_text_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1836 --
1837
1838 ALTER TABLE ONLY current_metric_text
1839     ADD CONSTRAINT current_metric_text_pkey PRIMARY KEY (sid, name);
1840
1841
1842 --
1843 -- Name: current_node_config_changelog_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1844 --
1845
1846 ALTER TABLE ONLY current_node_config_changelog
1847     ADD CONSTRAINT current_node_config_changelog_pkey PRIMARY KEY (remote_address, node_type, whence);
1848
1849
1850 --
1851 -- Name: current_node_config_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1852 --
1853
1854 ALTER TABLE ONLY current_node_config
1855     ADD CONSTRAINT current_node_config_pkey PRIMARY KEY (remote_address, node_type);
1856
1857
1858 --
1859 -- Name: loading_dock_check_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1860 --
1861
1862 ALTER TABLE ONLY loading_dock_check_s
1863     ADD CONSTRAINT loading_dock_check_s_pkey PRIMARY KEY (sid, id, whence);
1864
1865
1866 --
1867 -- Name: loading_dock_metric_numeric_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1868 --
1869
1870 ALTER TABLE ONLY loading_dock_metric_numeric_s
1871     ADD CONSTRAINT loading_dock_metric_numeric_s_pkey PRIMARY KEY (whence, sid, name);
1872
1873 ALTER TABLE loading_dock_metric_numeric_s CLUSTER ON loading_dock_metric_numeric_s_pkey;
1874
1875
1876 --
1877 -- Name: loading_dock_metric_text_s_change_log_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1878 --
1879
1880 ALTER TABLE ONLY loading_dock_metric_text_s_change_log
1881     ADD CONSTRAINT loading_dock_metric_text_s_change_log_pkey PRIMARY KEY (whence, sid, name);
1882
1883
1884 --
1885 -- Name: loading_dock_metric_text_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1886 --
1887
1888 ALTER TABLE ONLY loading_dock_metric_text_s
1889     ADD CONSTRAINT loading_dock_metric_text_s_pkey PRIMARY KEY (whence, sid, name);
1890
1891
1892 --
1893 -- Name: loading_dock_status_s_change_log_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1894 --
1895
1896 ALTER TABLE ONLY loading_dock_status_s_change_log
1897     ADD CONSTRAINT loading_dock_status_s_change_log_pkey PRIMARY KEY (sid, whence);
1898
1899
1900 --
1901 -- Name: loading_dock_status_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1902 --
1903
1904 ALTER TABLE ONLY loading_dock_status_s
1905     ADD CONSTRAINT loading_dock_status_s_pkey PRIMARY KEY (sid, whence);
1906
1907
1908 --
1909 -- Name: log_whence_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1910 --
1911
1912 ALTER TABLE ONLY log_whence_s
1913     ADD CONSTRAINT log_whence_s_pkey PRIMARY KEY (whence, "interval");
1914
1915
1916 --
1917 -- Name: map_uuid_to_sid_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1918 --
1919
1920 ALTER TABLE ONLY map_uuid_to_sid
1921     ADD CONSTRAINT map_uuid_to_sid_pkey PRIMARY KEY (id, sid);
1922
1923
1924 --
1925 -- Name: metric_name_summary_pk; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1926 --
1927
1928 ALTER TABLE ONLY metric_name_summary
1929     ADD CONSTRAINT metric_name_summary_pk UNIQUE (sid, metric_name, metric_type);
1930
1931
1932 --
1933 -- Name: mv_loading_dock_check_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1934 --
1935
1936 ALTER TABLE ONLY mv_loading_dock_check_s
1937     ADD CONSTRAINT mv_loading_dock_check_s_pkey PRIMARY KEY (sid);
1938
1939
1940 --
1941 -- Name: rollup_matrix_numeric_12hours_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1942 --
1943
1944 ALTER TABLE ONLY rollup_matrix_numeric_12hours
1945     ADD CONSTRAINT rollup_matrix_numeric_12hours_pkey PRIMARY KEY (rollup_time, sid, name);
1946
1947 ALTER TABLE rollup_matrix_numeric_12hours CLUSTER ON rollup_matrix_numeric_12hours_pkey;
1948
1949
1950 --
1951 -- Name: rollup_matrix_numeric_20m_new_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1952 --
1953
1954 ALTER TABLE ONLY rollup_matrix_numeric_20m
1955     ADD CONSTRAINT rollup_matrix_numeric_20m_new_pkey PRIMARY KEY (rollup_time, sid, name);
1956
1957 ALTER TABLE rollup_matrix_numeric_20m CLUSTER ON rollup_matrix_numeric_20m_new_pkey;
1958
1959
1960 --
1961 -- Name: rollup_matrix_numeric_5m_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1962 --
1963
1964 ALTER TABLE ONLY rollup_matrix_numeric_5m
1965     ADD CONSTRAINT rollup_matrix_numeric_5m_pkey PRIMARY KEY (rollup_time, sid, name);
1966
1967 ALTER TABLE rollup_matrix_numeric_5m CLUSTER ON rollup_matrix_numeric_5m_pkey;
1968
1969
1970 --
1971 -- Name: rollup_matrix_numeric_60m_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1972 --
1973
1974 ALTER TABLE ONLY rollup_matrix_numeric_60m
1975     ADD CONSTRAINT rollup_matrix_numeric_60m_pkey PRIMARY KEY (rollup_time, sid, name);
1976
1977 ALTER TABLE rollup_matrix_numeric_60m CLUSTER ON rollup_matrix_numeric_60m_pkey;
1978
1979
1980 --
1981 -- Name: rollup_matrix_numeric_6hours_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
1982 --
1983
1984 ALTER TABLE ONLY rollup_matrix_numeric_6hours
1985     ADD CONSTRAINT rollup_matrix_numeric_6hours_pkey PRIMARY KEY (rollup_time, sid, name);
1986
1987 ALTER TABLE rollup_matrix_numeric_6hours CLUSTER ON rollup_matrix_numeric_6hours_pkey;
1988
1989
1990 --
1991 -- Name: idx_mv_loading_dock_check_s_module; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace:
1992 --
1993
1994 CREATE INDEX idx_mv_loading_dock_check_s_module ON mv_loading_dock_check_s USING btree (module);
1995
1996
1997 --
1998 -- Name: idx_mv_loading_dock_check_s_name; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace:
1999 --
2000
2001 CREATE INDEX idx_mv_loading_dock_check_s_name ON mv_loading_dock_check_s USING btree (name);
2002
2003
2004 --
2005 -- Name: idx_mv_loading_dock_check_s_target; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace:
2006 --
2007
2008 CREATE INDEX idx_mv_loading_dock_check_s_target ON mv_loading_dock_check_s USING btree (target);
2009
2010
2011 --
2012 -- Name: idx_rollup_matrix_numeric_20m_rollup_time; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace:
2013 --
2014
2015 CREATE INDEX idx_rollup_matrix_numeric_20m_rollup_time ON rollup_matrix_numeric_20m USING btree (date_hour(rollup_time));
2016
2017
2018 --
2019 -- Name: unq_mv_loading_dock_check_s_id; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace:
2020 --
2021
2022 CREATE UNIQUE INDEX unq_mv_loading_dock_check_s_id ON mv_loading_dock_check_s USING btree (id);
2023
2024
2025 SET search_path = prism, pg_catalog;
2026
2027 --
2028 -- Name: check_name_saved_graphs; Type: TRIGGER; Schema: prism; Owner: reconnoiter
2029 --
2030
2031 CREATE TRIGGER check_name_saved_graphs
2032     BEFORE INSERT OR UPDATE ON saved_graphs
2033     FOR EACH ROW
2034     EXECUTE PROCEDURE check_name_saved_graphs();
2035
2036
2037 SET search_path = stratcon, pg_catalog;
2038
2039 --
2040 -- Name: loading_dock_metric_numeric_s_whence_log; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter
2041 --
2042
2043 CREATE TRIGGER loading_dock_metric_numeric_s_whence_log
2044     AFTER INSERT ON loading_dock_metric_numeric_s
2045     FOR EACH ROW
2046     EXECUTE PROCEDURE loading_dock_metric_numeric_s_whence_log();
2047
2048
2049 --
2050 -- Name: loading_dock_metric_text_s_change_log; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter
2051 --
2052
2053 CREATE TRIGGER loading_dock_metric_text_s_change_log
2054     AFTER INSERT ON loading_dock_metric_text_s
2055     FOR EACH ROW
2056     EXECUTE PROCEDURE loading_dock_metric_text_s_change_log();
2057
2058
2059 --
2060 -- Name: loading_dock_status_s_change_log; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter
2061 --
2062
2063 CREATE TRIGGER loading_dock_status_s_change_log
2064     AFTER INSERT ON loading_dock_status_s
2065     FOR EACH ROW
2066     EXECUTE PROCEDURE loading_dock_status_s_change_log();
2067
2068
2069 --
2070 -- Name: mv_loading_dock_check_s; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter
2071 --
2072
2073 CREATE TRIGGER mv_loading_dock_check_s
2074     AFTER INSERT ON loading_dock_check_s
2075     FOR EACH ROW
2076     EXECUTE PROCEDURE mv_loading_dock_check_s();
2077
2078
2079 SET search_path = prism, pg_catalog;
2080
2081 --
2082 -- Name: graphid_fk; Type: FK CONSTRAINT; Schema: prism; Owner: reconnoiter
2083 --
2084
2085 ALTER TABLE ONLY saved_graphs_dep
2086     ADD CONSTRAINT graphid_fk FOREIGN KEY (graphid) REFERENCES saved_graphs(graphid);
2087
2088
2089 --
2090 -- Name: saved_graphs_dep_sid_fkey; Type: FK CONSTRAINT; Schema: prism; Owner: reconnoiter
2091 --
2092
2093 ALTER TABLE ONLY saved_graphs_dep
2094     ADD CONSTRAINT saved_graphs_dep_sid_fkey FOREIGN KEY (sid, metric_name, metric_type) REFERENCES stratcon.metric_name_summary(sid, metric_name, metric_type);
2095
2096
2097 --
2098 -- Name: public; Type: ACL; Schema: -; Owner: postgres
2099 --
2100
2101 REVOKE ALL ON SCHEMA public FROM PUBLIC;
2102 REVOKE ALL ON SCHEMA public FROM postgres;
2103 GRANT ALL ON SCHEMA public TO postgres;
2104 GRANT ALL ON SCHEMA public TO PUBLIC;
2105
2106
2107 --
2108 -- Name: stratcon; Type: ACL; Schema: -; Owner: stratcon
2109 --
2110
2111 REVOKE ALL ON SCHEMA stratcon FROM PUBLIC;
2112 REVOKE ALL ON SCHEMA stratcon FROM stratcon;
2113 GRANT ALL ON SCHEMA stratcon TO stratcon;
2114 GRANT USAGE ON SCHEMA stratcon TO prism;
2115
2116
2117 --
2118 -- Name: saved_graphs; Type: ACL; Schema: prism; Owner: reconnoiter
2119 --
2120
2121 REVOKE ALL ON TABLE saved_graphs FROM PUBLIC;
2122 REVOKE ALL ON TABLE saved_graphs FROM reconnoiter;
2123 GRANT ALL ON TABLE saved_graphs TO reconnoiter;
2124 GRANT ALL ON TABLE saved_graphs TO prism;
2125
2126
2127 --
2128 -- Name: saved_graphs_dep; Type: ACL; Schema: prism; Owner: reconnoiter
2129 --
2130
2131 REVOKE ALL ON TABLE saved_graphs_dep FROM PUBLIC;
2132 REVOKE ALL ON TABLE saved_graphs_dep FROM reconnoiter;
2133 GRANT ALL ON TABLE saved_graphs_dep TO reconnoiter;
2134 GRANT ALL ON TABLE saved_graphs_dep TO prism;
2135
2136
2137 SET search_path = stratcon, pg_catalog;
2138
2139 --
2140 -- Name: current_metric_text; Type: ACL; Schema: stratcon; Owner: reconnoiter
2141 --
2142
2143 REVOKE ALL ON TABLE current_metric_text FROM PUBLIC;
2144 REVOKE ALL ON TABLE current_metric_text FROM reconnoiter;
2145 GRANT ALL ON TABLE current_metric_text TO reconnoiter;
2146 GRANT SELECT ON TABLE current_metric_text TO prism;
2147 GRANT SELECT,INSERT,UPDATE ON TABLE current_metric_text TO stratcon;
2148
2149
2150 --
2151 -- Name: current_node_config; Type: ACL; Schema: stratcon; Owner: reconnoiter
2152 --
2153
2154 REVOKE ALL ON TABLE current_node_config FROM PUBLIC;
2155 REVOKE ALL ON TABLE current_node_config FROM reconnoiter;
2156 GRANT ALL ON TABLE current_node_config TO reconnoiter;
2157 GRANT SELECT ON TABLE current_node_config TO prism;
2158 GRANT SELECT,INSERT,UPDATE ON TABLE current_node_config TO stratcon;
2159
2160
2161 --
2162 -- Name: current_node_config_changelog; Type: ACL; Schema: stratcon; Owner: reconnoiter
2163 --
2164
2165 REVOKE ALL ON TABLE current_node_config_changelog FROM PUBLIC;
2166 REVOKE ALL ON TABLE current_node_config_changelog FROM reconnoiter;
2167 GRANT ALL ON TABLE current_node_config_changelog TO reconnoiter;
2168 GRANT SELECT ON TABLE current_node_config_changelog TO prism;
2169 GRANT SELECT,INSERT,UPDATE ON TABLE current_node_config_changelog TO stratcon;
2170
2171
2172 --
2173 -- Name: loading_dock_check_s; Type: ACL; Schema: stratcon; Owner: reconnoiter
2174 --
2175
2176 REVOKE ALL ON TABLE loading_dock_check_s FROM PUBLIC;
2177 REVOKE ALL ON TABLE loading_dock_check_s FROM reconnoiter;
2178 GRANT ALL ON TABLE loading_dock_check_s TO reconnoiter;
2179 GRANT SELECT,INSERT,UPDATE ON TABLE loading_dock_check_s TO stratcon;
2180 GRANT SELECT ON TABLE loading_dock_check_s TO prism;
2181
2182
2183 --
2184 -- Name: loading_dock_metric_numeric_s; Type: ACL; Schema: stratcon; Owner: reconnoiter
2185 --
2186
2187 REVOKE ALL ON TABLE loading_dock_metric_numeric_s FROM PUBLIC;
2188 REVOKE ALL ON TABLE loading_dock_metric_numeric_s FROM reconnoiter;
2189 GRANT ALL ON TABLE loading_dock_metric_numeric_s TO reconnoiter;
2190 GRANT SELECT,INSERT,UPDATE ON TABLE loading_dock_metric_numeric_s TO stratcon;
2191 GRANT SELECT ON TABLE loading_dock_metric_numeric_s TO prism;
2192
2193
2194 --
2195 -- Name: loading_dock_metric_text_s; Type: ACL; Schema: stratcon; Owner: reconnoiter
2196 --
2197
2198 REVOKE ALL ON TABLE loading_dock_metric_text_s FROM PUBLIC;
2199 REVOKE ALL ON TABLE loading_dock_metric_text_s FROM reconnoiter;
2200 GRANT ALL ON TABLE loading_dock_metric_text_s TO reconnoiter;
2201 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE loading_dock_metric_text_s TO stratcon;
2202 GRANT SELECT ON TABLE loading_dock_metric_text_s TO prism;
2203
2204
2205 --
2206 -- Name: loading_dock_metric_text_s_change_log; Type: ACL; Schema: stratcon; Owner: reconnoiter
2207 --
2208
2209 REVOKE ALL ON TABLE loading_dock_metric_text_s_change_log FROM PUBLIC;
2210 REVOKE ALL ON TABLE loading_dock_metric_text_s_change_log FROM reconnoiter;
2211 GRANT ALL ON TABLE loading_dock_metric_text_s_change_log TO reconnoiter;
2212 GRANT SELECT,INSERT,UPDATE ON TABLE loading_dock_metric_text_s_change_log TO stratcon;
2213 GRANT SELECT ON TABLE loading_dock_metric_text_s_change_log TO prism;
2214
2215
2216 --
2217 -- Name: loading_dock_status_s; Type: ACL; Schema: stratcon; Owner: reconnoiter
2218 --
2219
2220 REVOKE ALL ON TABLE loading_dock_status_s FROM PUBLIC;
2221 REVOKE ALL ON TABLE loading_dock_status_s FROM reconnoiter;
2222 GRANT ALL ON TABLE loading_dock_status_s TO reconnoiter;
2223 GRANT SELECT,INSERT,UPDATE ON TABLE loading_dock_status_s TO stratcon;
2224 GRANT SELECT ON TABLE loading_dock_status_s TO prism;
2225
2226
2227 --
2228 -- Name: loading_dock_status_s_change_log; Type: ACL; Schema: stratcon; Owner: reconnoiter
2229 --
2230
2231 REVOKE ALL ON TABLE loading_dock_status_s_change_log FROM PUBLIC;
2232 REVOKE ALL ON TABLE loading_dock_status_s_change_log FROM reconnoiter;
2233 GRANT ALL ON TABLE loading_dock_status_s_change_log TO reconnoiter;
2234 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE loading_dock_status_s_change_log TO stratcon;
2235 GRANT SELECT ON TABLE loading_dock_status_s_change_log TO prism;
2236
2237
2238 --
2239 -- Name: log_whence_s; Type: ACL; Schema: stratcon; Owner: reconnoiter
2240 --
2241
2242 REVOKE ALL ON TABLE log_whence_s FROM PUBLIC;
2243 REVOKE ALL ON TABLE log_whence_s FROM reconnoiter;
2244 GRANT ALL ON TABLE log_whence_s TO reconnoiter;
2245 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE log_whence_s TO stratcon;
2246 GRANT SELECT ON TABLE log_whence_s TO prism;
2247
2248
2249 --
2250 -- Name: map_uuid_to_sid; Type: ACL; Schema: stratcon; Owner: reconnoiter
2251 --
2252
2253 REVOKE ALL ON TABLE map_uuid_to_sid FROM PUBLIC;
2254 REVOKE ALL ON TABLE map_uuid_to_sid FROM reconnoiter;
2255 GRANT ALL ON TABLE map_uuid_to_sid TO reconnoiter;
2256 GRANT SELECT,INSERT,UPDATE ON TABLE map_uuid_to_sid TO stratcon;
2257 GRANT SELECT ON TABLE map_uuid_to_sid TO prism;
2258
2259
2260 --
2261 -- Name: metric_name_summary; Type: ACL; Schema: stratcon; Owner: reconnoiter
2262 --
2263
2264 REVOKE ALL ON TABLE metric_name_summary FROM PUBLIC;
2265 REVOKE ALL ON TABLE metric_name_summary FROM reconnoiter;
2266 GRANT ALL ON TABLE metric_name_summary TO reconnoiter;
2267 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE metric_name_summary TO stratcon;
2268 GRANT SELECT ON TABLE metric_name_summary TO prism;
2269
2270
2271 --
2272 -- Name: mv_loading_dock_check_s; Type: ACL; Schema: stratcon; Owner: reconnoiter
2273 --
2274
2275 REVOKE ALL ON TABLE mv_loading_dock_check_s FROM PUBLIC;
2276 REVOKE ALL ON TABLE mv_loading_dock_check_s FROM reconnoiter;
2277 GRANT ALL ON TABLE mv_loading_dock_check_s TO reconnoiter;
2278 GRANT SELECT ON TABLE mv_loading_dock_check_s TO prism;
2279 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE mv_loading_dock_check_s TO stratcon;
2280
2281
2282 --
2283 -- Name: rollup_matrix_numeric_12hours; Type: ACL; Schema: stratcon; Owner: reconnoiter
2284 --
2285
2286 REVOKE ALL ON TABLE rollup_matrix_numeric_12hours FROM PUBLIC;
2287 REVOKE ALL ON TABLE rollup_matrix_numeric_12hours FROM reconnoiter;
2288 GRANT ALL ON TABLE rollup_matrix_numeric_12hours TO reconnoiter;
2289 GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_12hours TO stratcon;
2290 GRANT SELECT ON TABLE rollup_matrix_numeric_12hours TO prism;
2291
2292
2293 --
2294 -- Name: rollup_matrix_numeric_20m; Type: ACL; Schema: stratcon; Owner: reconnoiter
2295 --
2296
2297 REVOKE ALL ON TABLE rollup_matrix_numeric_20m FROM PUBLIC;
2298 REVOKE ALL ON TABLE rollup_matrix_numeric_20m FROM reconnoiter;
2299 GRANT ALL ON TABLE rollup_matrix_numeric_20m TO reconnoiter;
2300 GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_20m TO stratcon;
2301 GRANT SELECT ON TABLE rollup_matrix_numeric_20m TO prism;
2302
2303
2304 --
2305 -- Name: rollup_matrix_numeric_5m; Type: ACL; Schema: stratcon; Owner: reconnoiter
2306 --
2307
2308 REVOKE ALL ON TABLE rollup_matrix_numeric_5m FROM PUBLIC;
2309 REVOKE ALL ON TABLE rollup_matrix_numeric_5m FROM reconnoiter;
2310 GRANT ALL ON TABLE rollup_matrix_numeric_5m TO reconnoiter;
2311 GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_5m TO stratcon;
2312 GRANT SELECT ON TABLE rollup_matrix_numeric_5m TO prism;
2313
2314
2315 --
2316 -- Name: rollup_matrix_numeric_60m; Type: ACL; Schema: stratcon; Owner: reconnoiter
2317 --
2318
2319 REVOKE ALL ON TABLE rollup_matrix_numeric_60m FROM PUBLIC;
2320 REVOKE ALL ON TABLE rollup_matrix_numeric_60m FROM reconnoiter;
2321 GRANT ALL ON TABLE rollup_matrix_numeric_60m TO reconnoiter;
2322 GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_60m TO stratcon;
2323 GRANT SELECT ON TABLE rollup_matrix_numeric_60m TO prism;
2324
2325
2326 --
2327 -- Name: rollup_matrix_numeric_6hours; Type: ACL; Schema: stratcon; Owner: reconnoiter
2328 --
2329
2330 REVOKE ALL ON TABLE rollup_matrix_numeric_6hours FROM PUBLIC;
2331 REVOKE ALL ON TABLE rollup_matrix_numeric_6hours FROM reconnoiter;
2332 GRANT ALL ON TABLE rollup_matrix_numeric_6hours TO reconnoiter;
2333 GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_6hours TO stratcon;
2334 GRANT SELECT ON TABLE rollup_matrix_numeric_6hours TO prism;
2335
2336
2337 --
2338 -- Name: rollup_runner; Type: ACL; Schema: stratcon; Owner: reconnoiter
2339 --
2340
2341 REVOKE ALL ON TABLE rollup_runner FROM PUBLIC;
2342 REVOKE ALL ON TABLE rollup_runner FROM reconnoiter;
2343 GRANT ALL ON TABLE rollup_runner TO reconnoiter;
2344 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE rollup_runner TO stratcon;
2345 GRANT SELECT ON TABLE rollup_runner TO prism;
2346
2347
2348 --
2349 -- Name: seq_sid; Type: ACL; Schema: stratcon; Owner: reconnoiter
2350 --
2351
2352 REVOKE ALL ON SEQUENCE seq_sid FROM PUBLIC;
2353 REVOKE ALL ON SEQUENCE seq_sid FROM reconnoiter;
2354 GRANT ALL ON SEQUENCE seq_sid TO reconnoiter;
2355 GRANT ALL ON SEQUENCE seq_sid TO stratcon;
2356
2357
2358 --
2359 -- PostgreSQL database dump complete
2360 --
2361
Note: See TracBrowser for help on using the browser.