root/sql/reconnoiter_ddl_dump.sql

Revision 6390014a64eb9ab5483bc88e66779b6c0ef7a20d, 53.7 kB (checked in by Denish Patel <denish@omniti.com>, 6 years ago)

Added DDL dump for reconnointer database

  • 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: stratcon; Type: SCHEMA; Schema: -; Owner: omniti
13 --
14
15 CREATE SCHEMA stratcon;
16
17
18 ALTER SCHEMA stratcon OWNER TO omniti;
19
20 SET search_path = stratcon, pg_catalog;
21
22 SET default_tablespace = '';
23
24 SET default_with_oids = false;
25
26 --
27 -- Name: loading_dock_check_s; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
28 --
29
30 CREATE TABLE loading_dock_check_s (
31     sid integer NOT NULL,
32     remote_address inet,
33     whence timestamp with time zone NOT NULL,
34     id uuid NOT NULL,
35     target text NOT NULL,
36     module text NOT NULL,
37     name text NOT NULL
38 );
39
40
41 ALTER TABLE stratcon.loading_dock_check_s OWNER TO omniti;
42
43 --
44 -- Name: loading_dock_metric_numeric_s; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
45 --
46
47 CREATE TABLE loading_dock_metric_numeric_s (
48     sid integer NOT NULL,
49     whence timestamp with time zone NOT NULL,
50     name text NOT NULL,
51     value numeric
52 );
53
54
55 ALTER TABLE stratcon.loading_dock_metric_numeric_s OWNER TO omniti;
56
57 --
58 -- Name: loading_dock_metric_text_s; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
59 --
60
61 CREATE TABLE loading_dock_metric_text_s (
62     sid integer NOT NULL,
63     whence timestamp with time zone NOT NULL,
64     name text NOT NULL,
65     value text
66 );
67
68
69 ALTER TABLE stratcon.loading_dock_metric_text_s OWNER TO omniti;
70
71 --
72 -- Name: loading_dock_metric_text_s_change_log; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
73 --
74
75 CREATE TABLE loading_dock_metric_text_s_change_log (
76     sid integer NOT NULL,
77     whence timestamp with time zone NOT NULL,
78     name text NOT NULL,
79     value text
80 );
81
82
83 ALTER TABLE stratcon.loading_dock_metric_text_s_change_log OWNER TO omniti;
84
85 --
86 -- Name: loading_dock_status_s; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
87 --
88
89 CREATE TABLE loading_dock_status_s (
90     sid integer NOT NULL,
91     whence timestamp with time zone NOT NULL,
92     state character(1) NOT NULL,
93     availability character(1) NOT NULL,
94     duration integer NOT NULL,
95     status text
96 );
97
98
99 ALTER TABLE stratcon.loading_dock_status_s OWNER TO omniti;
100
101 --
102 -- Name: loading_dock_status_s_change_log; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
103 --
104
105 CREATE TABLE loading_dock_status_s_change_log (
106     sid integer NOT NULL,
107     whence timestamp with time zone NOT NULL,
108     state character(1) NOT NULL,
109     availability character(1) NOT NULL,
110     duration integer NOT NULL,
111     status text
112 );
113
114
115 ALTER TABLE stratcon.loading_dock_status_s_change_log OWNER TO omniti;
116
117 --
118 -- Name: log_whence_s; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
119 --
120
121 CREATE TABLE log_whence_s (
122     whence timestamp with time zone NOT NULL,
123     "interval" character varying(20) NOT NULL
124 );
125
126
127 ALTER TABLE stratcon.log_whence_s OWNER TO omniti;
128
129 --
130 -- Name: map_uuid_to_sid; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
131 --
132
133 CREATE TABLE map_uuid_to_sid (
134     id uuid NOT NULL,
135     sid integer NOT NULL
136 );
137
138
139 ALTER TABLE stratcon.map_uuid_to_sid OWNER TO omniti;
140
141 --
142 -- Name: metric_name_summary; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
143 --
144
145 CREATE TABLE metric_name_summary (
146     sid integer NOT NULL,
147     metric_name text NOT NULL,
148     metric_type character varying(22),
149     active boolean DEFAULT true
150 );
151
152
153 ALTER TABLE stratcon.metric_name_summary OWNER TO omniti;
154
155 --
156 -- Name: rollup_matrix_numeric_12hours; Type: TABLE; Schema: stratcon; Owner: postgres; Tablespace:
157 --
158
159 CREATE TABLE rollup_matrix_numeric_12hours (
160     sid integer NOT NULL,
161     name text NOT NULL,
162     rollup_time timestamp with time zone NOT NULL,
163     count_rows integer,
164     avg_value numeric,
165     min_value numeric,
166     max_value numeric
167 );
168
169
170 ALTER TABLE stratcon.rollup_matrix_numeric_12hours OWNER TO postgres;
171
172 --
173 -- Name: rollup_matrix_numeric_20m; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
174 --
175
176 CREATE TABLE rollup_matrix_numeric_20m (
177     sid integer NOT NULL,
178     name text NOT NULL,
179     rollup_time timestamp with time zone NOT NULL,
180     count_rows integer,
181     avg_value numeric,
182     min_value numeric,
183     max_value numeric
184 );
185
186
187 ALTER TABLE stratcon.rollup_matrix_numeric_20m OWNER TO omniti;
188
189 --
190 -- Name: rollup_matrix_numeric_5m; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
191 --
192
193 CREATE TABLE rollup_matrix_numeric_5m (
194     sid integer NOT NULL,
195     name text NOT NULL,
196     rollup_time timestamp with time zone NOT NULL,
197     count_rows integer,
198     avg_value numeric,
199     min_value numeric,
200     max_value numeric
201 );
202
203
204 ALTER TABLE stratcon.rollup_matrix_numeric_5m OWNER TO omniti;
205
206 --
207 -- Name: rollup_matrix_numeric_60m; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
208 --
209
210 CREATE TABLE rollup_matrix_numeric_60m (
211     sid integer NOT NULL,
212     name text NOT NULL,
213     rollup_time timestamp with time zone NOT NULL,
214     count_rows integer,
215     avg_value numeric,
216     min_value numeric,
217     max_value numeric
218 );
219
220
221 ALTER TABLE stratcon.rollup_matrix_numeric_60m OWNER TO omniti;
222
223 --
224 -- Name: rollup_matrix_numeric_6hours; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
225 --
226
227 CREATE TABLE rollup_matrix_numeric_6hours (
228     sid integer NOT NULL,
229     name text NOT NULL,
230     rollup_time timestamp with time zone NOT NULL,
231     count_rows integer,
232     avg_value numeric,
233     min_value numeric,
234     max_value numeric
235 );
236
237
238 ALTER TABLE stratcon.rollup_matrix_numeric_6hours OWNER TO omniti;
239
240 --
241 -- Name: rollup_runner; Type: TABLE; Schema: stratcon; Owner: omniti; Tablespace:
242 --
243
244 CREATE TABLE rollup_runner (
245     rollup_table character varying(100),
246     runner character varying(22)
247 );
248
249
250 ALTER TABLE stratcon.rollup_runner OWNER TO omniti;
251
252 --
253 -- Name: choose_window(timestamp without time zone, timestamp without time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: omniti
254 --
255
256 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
257     AS $$
258 declare
259   window record;
260 begin
261   -- Figure out which table we should be looking in
262   for window in
263     select atablename, aperiod, anperiods
264     from (select aperiod, iv/isec as anperiods, atablename,
265                  abs(case when iv/isec - in_hopeful_nperiods < 0
266                           then 10 * (in_hopeful_nperiods - iv/isec)
267                           else iv/isec - in_hopeful_nperiods
268                            end) as badness
269             from (select extract('epoch' from in_end_time) -
270                          extract('epoch' from in_start_time) as iv
271                  ) i,
272                  (   select 5*60 as isec, '5 minutes'::interval as aperiod,
273                             'rollup_matrix_numeric_5m' as atablename
274                   union all
275                      select 20*60 as isec, '20 minutes'::interval as aperiod,
276                             'rollup_matrix_numeric_20m' as atablename
277                   union all
278                      select 60*60 as isec, '1 hour'::interval as aperiod,
279                             'rollup_matrix_numeric_60m' as atablename
280                   union all
281                      select 6*60*60 as isec, '6 hours'::interval as aaperiod,
282                             'rollup_matrix_numeric_6hours' as atablename
283                   union all
284                      select 12*60*60 as isec, '12 hours'::interval as aperiod,
285                             'rollup_matrix_numeric_12hours' as atablename
286                  ) ivs
287          ) b
288  order by badness asc
289   limit 1
290   loop
291     tablename := window.atablename;
292     period := window.aperiod;
293     nperiods := window.anperiods;
294     return next;
295   end loop;
296   return;
297 end
298 $$
299     LANGUAGE plpgsql;
300
301
302 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 omniti;
303
304 --
305 -- Name: choose_window(timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: omniti
306 --
307
308 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
309     AS $$
310 declare
311   window record;
312 begin
313   -- Figure out which table we should be looking in
314   for window in
315     select atablename, aperiod, anperiods
316     from (select aperiod, iv/isec as anperiods, atablename,
317                  abs(case when iv/isec - in_hopeful_nperiods < 0
318                           then 10 * (in_hopeful_nperiods - iv/isec)
319                           else iv/isec - in_hopeful_nperiods
320                            end) as badness
321             from (select extract('epoch' from in_end_time) -
322                          extract('epoch' from in_start_time) as iv
323                  ) i,
324                  (   select 5*60 as isec, '5 minutes'::interval as aperiod,
325                             'rollup_matrix_numeric_5m' as atablename
326                   union all
327                      select 20*60 as isec, '20 minutes'::interval as aperiod,
328                             'rollup_matrix_numeric_20m' as atablename
329                   union all
330                      select 60*60 as isec, '1 hour'::interval as aperiod,
331                             'rollup_matrix_numeric_60m' as atablename
332                   union all
333                      select 6*60*60 as isec, '6 hours'::interval as aaperiod,
334                             'rollup_matrix_numeric_6hours' as atablename
335                   union all
336                      select 12*60*60 as isec, '12 hours'::interval as aperiod,
337                             'rollup_matrix_numeric_12hours' as atablename
338                  ) ivs
339          ) b
340  order by badness asc
341   limit 1
342   loop
343     tablename := window.atablename;
344     period := window.aperiod;
345     nperiods := window.anperiods;
346     return next;
347   end loop;
348   return;
349 end
350 $$
351     LANGUAGE plpgsql;
352
353
354 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 omniti;
355
356 --
357 -- Name: date_hour(timestamp with time zone); Type: FUNCTION; Schema: stratcon; Owner: omniti
358 --
359
360 CREATE FUNCTION date_hour(timestamp with time zone) RETURNS timestamp with time zone
361     AS $_$
362  SELECT date_trunc('hour',$1);
363 $_$
364     LANGUAGE sql IMMUTABLE STRICT;
365
366
367 ALTER FUNCTION stratcon.date_hour(timestamp with time zone) OWNER TO omniti;
368
369 --
370 -- Name: fetch_dataset(uuid, text, timestamp with time zone, timestamp with time zone, integer, boolean); Type: FUNCTION; Schema: stratcon; Owner: omniti
371 --
372
373 CREATE FUNCTION fetch_dataset(in_check 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
374     AS $$
375 declare
376   v_sql text;
377   v_sid int;
378   v_target record;
379   v_interval numeric;
380   v_start_adj timestamptz;
381   v_end_adj timestamptz;
382   v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
383   v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
384   v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
385 begin
386
387   -- Map out uuid to an sid.
388   select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check;
389   if not found then
390     return;
391   end if;
392
393   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
394
395   if not found then
396     raise exception 'no target table';
397     return;
398   end if;
399
400   select 'epoch'::timestamp +
401          ((floor(extract('epoch' from in_start_time) /
402                  extract('epoch' from v_target.period)) *
403            extract('epoch' from v_target.period)) || ' seconds') ::interval
404     into v_start_adj;
405
406   select 'epoch'::timestamp +
407          ((floor(extract('epoch' from in_end_time) /
408                  extract('epoch' from v_target.period)) *
409            extract('epoch' from v_target.period)) || ' seconds') ::interval
410     into v_end_adj;
411
412   v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' ||
413            's.rollup_time, d.count_rows, d.avg_value, ' ||
414            'd.min_value, d.max_value ' ||
415            ' from ' ||
416            '(select ' || quote_literal(v_start_adj) || '::timestamp' ||
417                   ' + t * ' || quote_literal(v_target.period) || '::interval' ||
418                        ' as rollup_time' ||
419              ' from generate_series(1,' || v_target.nperiods || ') t) s ' ||
420            'left join ' ||
421            '(select * from stratcon.' || v_target.tablename ||
422            ' where sid = ' || v_sid ||
423              ' and name = ' || quote_literal(in_name) ||
424              ' and rollup_time between ' || quote_literal(v_start_adj) || '::timestamp' ||
425                                  ' and ' || quote_literal(v_end_adj) || '::timestamp) d' ||
426            ' using(rollup_time)';
427
428   for v_rollup_row in execute v_sql loop
429     if derive is true then
430       v_r_rollup_row := v_rollup_row;
431       if v_l_rollup_row.count_rows is not null and
432          v_rollup_row.count_rows is not null then
433         v_interval := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time);
434         v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2;
435         v_r_rollup_row.avg_value :=
436           (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval;
437         v_r_rollup_row.min_value :=
438           (v_rollup_row.min_value - v_l_rollup_row.min_value) / v_interval;
439         v_r_rollup_row.max_value :=
440           (v_rollup_row.max_value - v_l_rollup_row.max_value) / v_interval;
441       else
442         v_r_rollup_row.count_rows = NULL;
443         v_r_rollup_row.avg_value = NULL;
444         v_r_rollup_row.min_value = NULL;
445         v_r_rollup_row.max_value = NULL;
446       end if;
447     else
448       v_r_rollup_row := v_rollup_row;
449     end if;
450     return next v_r_rollup_row;
451     v_l_rollup_row := v_rollup_row;
452   end loop;
453   return;
454 end
455 $$
456     LANGUAGE plpgsql;
457
458
459 ALTER FUNCTION stratcon.fetch_dataset(in_check 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 omniti;
460
461 --
462 -- Name: fetch_varset(uuid, text, timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: stratcon; Owner: omniti
463 --
464
465 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
466     AS $$
467 declare
468   v_sid int;
469   v_target record;
470   v_start_adj timestamptz;
471   v_start_text text;
472   v_next_text text;
473   v_end_adj timestamptz;
474   v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype;
475 begin
476   -- Map out uuid to an sid.
477   select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check;
478   if not found then
479     return;
480   end if;
481
482   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
483
484   select 'epoch'::timestamp +
485          ((floor(extract('epoch' from in_start_time) /
486                  extract('epoch' from v_target.period)) *
487            extract('epoch' from v_target.period)) || ' seconds') ::interval
488     into v_start_adj;
489
490   select 'epoch'::timestamp +
491          ((floor(extract('epoch' from in_end_time) /
492                  extract('epoch' from v_target.period)) *
493            extract('epoch' from v_target.period)) || ' seconds') ::interval
494     into v_end_adj;
495
496   for v_change_row in
497     select sid, 'epoch'::timestamp +
498          ((floor(extract('epoch' from whence) /
499                  extract('epoch' from v_target.period)) *
500            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
501            name, value
502       from stratcon.loading_dock_metric_text_s_change_log
503      where sid = v_sid
504        and name = in_name
505        and whence <= v_start_adj
506   order by 'epoch'::timestamp +
507          ((floor(extract('epoch' from whence) /
508                  extract('epoch' from v_target.period)) *
509            extract('epoch' from v_target.period)) || ' seconds') ::interval desc
510      limit 1
511   loop
512     v_start_text := coalesce(v_change_row.value, '[unset]');
513   end loop;
514
515   for v_change_row in
516     select v_sid as sid, whence, in_name as name, value from
517 --    (select v_start_adj::timestamp + t * v_target.period::interval as whence
518 --      from generate_series(1, v_target.nperiods) t) s
519 -- left join
520     (select 'epoch'::timestamp +
521          ((floor(extract('epoch' from whence) /
522                  extract('epoch' from v_target.period)) *
523            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
524            coalesce(value, '[unset]') as value
525       from stratcon.loading_dock_metric_text_s_change_log
526      where sid = v_sid
527        and name = in_name
528        and whence > v_start_adj
529        and whence <= v_end_adj) d
530 --    using (whence)
531   order by whence asc
532   loop
533     v_next_text := v_change_row.value;
534     if v_change_row.value is not null and
535        v_start_text != v_change_row.value then
536       v_change_row.value := coalesce(v_start_text, '[unset]') || ' -> ' || coalesce(v_change_row.value, '[unset]');
537     else
538       v_change_row.value := v_start_text;
539     end if;
540     if v_next_text is not null then
541       v_start_text := v_next_text;
542     end if;
543     return next v_change_row;
544   end loop;
545
546   return;
547 end
548 $$
549     LANGUAGE plpgsql;
550
551
552 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 omniti;
553
554 --
555 -- Name: generate_sid_from_id(uuid); Type: FUNCTION; Schema: stratcon; Owner: omniti
556 --
557
558 CREATE FUNCTION generate_sid_from_id(v_in_id uuid) RETURNS integer
559     AS $$
560 DECLARE
561    v_ex_sid integer;
562    v_new_sid integer;
563  
564 BEGIN
565
566 SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id
567   INTO v_ex_sid;
568
569  IF NOT FOUND THEN
570     SELECT nextval('stratcon.seq_sid')
571     INTO v_new_sid;
572
573     INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid);
574        
575    
576     RETURN v_new_sid;
577  ELSE
578       RETURN v_ex_sid;
579  END IF;
580
581 END
582 $$
583     LANGUAGE plpgsql;
584
585
586 ALTER FUNCTION stratcon.generate_sid_from_id(v_in_id uuid) OWNER TO omniti;
587
588 --
589 -- Name: loading_dock_metric_numeric_s_whence_log(); Type: FUNCTION; Schema: stratcon; Owner: omniti
590 --
591
592 CREATE FUNCTION loading_dock_metric_numeric_s_whence_log() RETURNS trigger
593     AS $$
594 DECLARE
595 v_whence timestamptz;
596 v_sid integer;
597 v_name text;
598 BEGIN
599 IF TG_OP = 'INSERT' THEN
600    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval and interval='5 minutes'
601      INTO v_whence;
602    IF NOT FOUND THEN
603        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval,'5 minutes');
604     END IF;
605
606    SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid  and metric_name=NEW.name
607      INTO v_sid,v_name;
608    IF NOT FOUND THEN
609        INSERT INTO  stratcon.metric_name_summary VALUES(NEW.sid,NEW.name,'numeric');
610     END IF;
611
612 END IF;
613
614     RETURN NULL;
615 END
616 $$
617     LANGUAGE plpgsql;
618
619
620 ALTER FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() OWNER TO omniti;
621
622 --
623 -- Name: loading_dock_metric_text_s_change_log(); Type: FUNCTION; Schema: stratcon; Owner: omniti
624 --
625
626 CREATE FUNCTION loading_dock_metric_text_s_change_log() RETURNS trigger
627     AS $$
628 DECLARE
629     v_oldvalue text;
630     v_sid integer;
631     v_name text;
632     v_value text;
633     v_whence timestamptz;
634     v_old_whence timestamptz;
635     v_old_name text;
636     v_old_sid integer;
637     v_old_value text;
638     v_max_whence timestamptz;
639 BEGIN
640
641 IF TG_OP = 'INSERT' THEN
642
643 SELECT max(whence) FROM stratcon.loading_dock_metric_text_s WHERE whence <> NEW.whence and sid=NEW.sid and name = NEW.name
644         INTO v_max_whence;
645  
646  IF NEW.whence < v_max_whence THEN           
647  
648     INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value)
649                  VALUES (NEW.sid,NEW.whence, NEW.name, NEW.value);
650                  
651        SELECT  whence,name,value FROM  stratcon.loading_dock_metric_text_s_change_log WHERE whence > NEW.whence and sid=NEW.sid and name=NEW.name order by whence  limit 1
652         INTO v_whence,v_sid,v_name,v_value;
653           IF FOUND  THEN
654             IF v_value IS  DISTINCT FROM NEW.value THEN
655                NULL;
656             ELSE
657                 DELETE from  stratcon.loading_dock_metric_text_s_change_log  WHERE whence=v_whence and sid=v_sid and name=v_name;
658             END IF;
659           END IF;
660        
661          
662        SELECT whence,sid,name,value from stratcon.loading_dock_metric_text_s where whence> NEW.whence and sid=NEW.sid and name=NEW.name and value!=NEW.value order by whence limit 1
663          INTO v_whence,v_sid,v_name,v_value;
664           IF FOUND  THEN
665              SELECT  whence,sid,name,value FROM  stratcon.loading_dock_metric_text_s_change_log WHERE whence =v_whence and sid=v_sid and name=v_name and value=v_value
666                  INTO v_old_whence,v_old_sid,v_old_name,v_old_value;
667               IF FOUND THEN
668                  NULL;
669               ELSE
670                 INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value)
671                  VALUES (v_sid,v_whence, v_name, v_value);
672                END IF;
673         END IF;
674
675   ELSE
676      SELECT value FROM  stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name
677          AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log
678                          WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name )
679      INTO v_oldvalue;
680
681     IF v_oldvalue IS DISTINCT FROM NEW.value THEN
682
683         INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value)
684             VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value);
685     END IF;
686   END IF;   
687
688
689 SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid  and metric_name=NEW.name
690         INTO v_sid,v_name;
691      IF NOT FOUND THEN
692           INSERT INTO  stratcon.metric_name_summary(sid,metric_name,metric_type)  VALUES(NEW.sid,NEW.name,'text');
693      END IF;
694
695 ELSE
696         RAISE EXCEPTION 'something wrong with stratcon.loading_dock_metric_text_s_change_log ';
697 END IF;
698
699     RETURN NULL;
700
701 END
702 $$
703     LANGUAGE plpgsql;
704
705
706 ALTER FUNCTION stratcon.loading_dock_metric_text_s_change_log() OWNER TO omniti;
707
708 --
709 -- Name: loading_dock_status_s_change_log(); Type: FUNCTION; Schema: stratcon; Owner: omniti
710 --
711
712 CREATE FUNCTION loading_dock_status_s_change_log() RETURNS trigger
713     AS $$
714 DECLARE
715     v_state CHAR(1);
716     v_avail CHAR(1);
717 BEGIN
718
719 IF TG_OP = 'INSERT' THEN
720     SELECT state,availability FROM  stratcon.loading_dock_status_s WHERE sid = NEW.sid
721         AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_status_s_change_log
722                         WHERE  SID=NEW.sid and  WHENCE <> NEW.whence )
723     INTO v_state,v_avail;
724
725     IF v_state IS DISTINCT FROM NEW.state OR v_avail IS DISTINCT FROM NEW.availability THEN
726
727         INSERT INTO stratcon.loading_dock_status_s_change_log (sid,whence,state,availability,duration,status)
728             VALUES (NEW.sid,NEW.whence,NEW.state,NEW.availability,NEW.duration,NEW.status);
729
730     END IF;
731
732 ELSE
733         RAISE EXCEPTION 'Something wrong with stratcon.loading_dock_status_s_change_log';
734 END IF;
735
736     RETURN NULL;
737
738 END
739 $$
740     LANGUAGE plpgsql;
741
742
743 ALTER FUNCTION stratcon.loading_dock_status_s_change_log() OWNER TO omniti;
744
745 --
746 -- Name: remove_metric(uuid, text, text); Type: FUNCTION; Schema: stratcon; Owner: stratcon
747 --
748
749 CREATE FUNCTION remove_metric(in_uuid uuid, in_metric_name text, v_debug text, OUT v_out text) RETURNS text
750     AS $$
751 DECLARE
752 v_del_sid INT;
753 v_del_metric_name TEXT;
754 v_del_metric_type TEXT;
755 deleted_t INT;
756 deleted_tc INT;
757 deleted_n INT;
758 deleted_5 INT;
759 deleted_20 INT;
760 deleted_60 INT;
761 deleted_6h INT;
762 deleted_12h INT;
763 deleted_sum INT;
764
765 BEGIN
766   SELECT s.sid,m.metric_name,m.metric_type
767     FROM
768              stratcon.map_uuid_to_sid s,
769              stratcon.metric_name_summary m
770        WHERE s.id=in_uuid
771              and s.sid=m.sid
772              and m.metric_name=in_metric_name
773   INTO v_del_sid,v_del_metric_name,v_del_metric_type;
774 IF NOT FOUND THEN
775    IF v_debug = 'DEBUG' THEN
776      RAISE NOTICE 'Given UUID can not map to SID,Metric Name: %,%',in_uuid,in_metric_name;
777    END IF;
778    v_out:='Please Supply Valid UUID,Metric Name Combination :'||in_uuid||','||in_metric_name;
779  RETURN;
780 END IF;
781 IF v_debug = 'DEBUG' THEN
782         RAISE NOTICE 'Delete In Progress For: %,%,%',v_del_sid,v_del_metric_name,v_del_metric_type;
783 END IF;
784
785 -- Check of Text or Numeric Type
786 IF v_del_metric_type ='text' THEN
787  -- Delete from Metrix Tex table
788   DELETE FROM stratcon.loading_dock_metric_text_s WHERE sid=v_del_sid AND name=v_del_metric_name;
789      GET DIAGNOSTICS deleted_t = ROW_COUNT;
790      IF v_debug = 'DEBUG' THEN
791            RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_text_s : %',deleted;
792      END IF;
793  -- Delete from Metrix Change Log table
794   DELETE FROM stratcon.loading_dock_metric_text_s_change_log WHERE sid=v_del_sid AND name=v_del_metric_name;
795      GET DIAGNOSTICS deleted_tc = ROW_COUNT;
796      IF v_debug = 'DEBUG' THEN
797           RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_text_s_change_log : %',deleted;
798      END IF;
799  ELSE
800   -- Delete from Metrix Numeric table
801    DELETE FROM stratcon.loading_dock_metric_numeric_s WHERE sid=v_del_sid AND name=v_del_metric_name;
802    GET DIAGNOSTICS deleted_n = ROW_COUNT;
803      IF v_debug = 'DEBUG' THEN
804          RAISE NOTICE 'DELELTED ROWS FROM loading_dock_metric_numeric_s : %',deleted;
805      END IF;
806   -- Delete from Rollup tables
807    DELETE FROM stratcon.rollup_matrix_numeric_5m WHERE sid=v_del_sid AND name=v_del_metric_name;
808    GET DIAGNOSTICS deleted_5 = ROW_COUNT;   
809      IF v_debug = 'DEBUG' THEN
810          RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_5m : %',deleted;
811      END IF;
812    DELETE FROM stratcon.rollup_matrix_numeric_20m WHERE sid=v_del_sid AND name=v_del_metric_name;
813       GET DIAGNOSTICS deleted_20= ROW_COUNT;     
814         IF v_debug = 'DEBUG' THEN
815             RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_20m : %',deleted;
816         END IF;
817    DELETE FROM stratcon.rollup_matrix_numeric_60m WHERE sid=v_del_sid AND name=v_del_metric_name;
818       GET DIAGNOSTICS deleted_60 = ROW_COUNT;     
819         IF v_debug = 'DEBUG' THEN
820             RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_60m : %',deleted;
821         END IF;
822    DELETE FROM stratcon.rollup_matrix_numeric_6hours WHERE sid=v_del_sid AND name=v_del_metric_name;
823       GET DIAGNOSTICS deleted_6h = ROW_COUNT;     
824         IF v_debug = 'DEBUG' THEN
825             RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_6hours : %',deleted;
826         END IF;
827    DELETE FROM stratcon.rollup_matrix_numeric_12hours WHERE sid=v_del_sid AND name=v_del_metric_name;
828       GET DIAGNOSTICS deleted_12h = ROW_COUNT;     
829         IF v_debug = 'DEBUG' THEN
830             RAISE NOTICE 'DELELTED ROWS FROM rollup_matrix_numeric_12hours : %',deleted;
831         END IF;
832 END IF;
833   -- Delete from metrix summary table
834    DELETE FROM stratcon.metrix_name_summary WHERE sid=v_del_sid AND metric_name=v_del_metric_name;
835       GET DIAGNOSTICS deleted_sum= ROW_COUNT;     
836         IF v_debug = 'DEBUG' THEN
837             RAISE NOTICE 'DELELTED ROWS FROM metric_name_summary : %',deleted;
838         END IF;
839  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;
840 RETURN;
841    EXCEPTION
842     WHEN RAISE_EXCEPTION THEN
843             RAISE EXCEPTION '%', SQLERRM;
844     WHEN OTHERS THEN
845             RAISE NOTICE '%', SQLERRM;
846 END
847 $$
848     LANGUAGE plpgsql;
849
850
851 ALTER FUNCTION stratcon.remove_metric(in_uuid uuid, in_metric_name text, v_debug text, OUT v_out text) OWNER TO stratcon;
852
853 --
854 -- Name: rollup_matrix_numeric_12hours(); Type: FUNCTION; Schema: stratcon; Owner: postgres
855 --
856
857 CREATE FUNCTION rollup_matrix_numeric_12hours() RETURNS void
858     AS $$
859 DECLARE
860   rec stratcon.rollup_matrix_numeric_12hours%rowtype;
861   v_sql TEXT;
862   v_min_whence TIMESTAMPTZ;
863   v_max_rollup_12 TIMESTAMPTZ;
864   v_whence TIMESTAMPTZ;
865   v_nrunning INT;
866   v_self VARCHAR(22);
867   whenceint RECORD;
868  
869 BEGIN
870
871   SELECT COUNT(1) INTO v_nrunning
872     from stratcon.rollup_runner t, pg_stat_activity a
873     where rollup_table ='rollup_matrix_numeric_12hours'
874      and runner = procpid || '.' || date_part('epoch',backend_start);
875
876   IF v_nrunning > 0 THEN
877     RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running';
878     RETURN ;
879   END IF;
880
881   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
882     FROM pg_stat_activity
883      WHERE procpid = pg_backend_pid();
884
885   IF v_self IS NULL THEN
886     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot self-identify';
887    END IF;
888
889    v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_12hours''';
890
891   EXECUTE v_sql;
892
893  FOR whenceint IN  SELECT * FROM stratcon.log_whence_s WHERE interval='12 hours' LOOP
894  
895   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='12 hours'
896          INTO v_min_whence;
897          
898   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_12hours
899          INTO v_max_rollup_12;   
900
901 /*-- Insert Log for 24 Hours rollup
902    
903    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'
904            INTO v_whence;
905       IF NOT FOUND THEN
906        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');
907    END IF;
908    */
909    
910   IF v_min_whence <= v_max_rollup_12 THEN
911  
912   DELETE FROM stratcon.rollup_matrix_numeric_12hours
913        WHERE rollup_time= v_min_whence;
914
915   END IF;
916  
917     FOR rec IN
918                 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,
919          MIN(min_value) as min_value ,MAX(max_value) as max_value
920          FROM stratcon.rollup_matrix_numeric_6hours
921            WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'12 hour'::interval
922                    GROUP BY sid,name
923         LOOP
924      
925        
926           INSERT INTO stratcon.rollup_matrix_numeric_12hours
927           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
928           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
929          
930      END LOOP;
931
932
933 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='12 hours';
934
935 v_min_whence := NULL;
936 v_max_rollup_12 := NULL;
937
938 END LOOP;
939
940 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_12hours';
941
942 RETURN;
943
944 EXCEPTION
945     WHEN RAISE_EXCEPTION THEN
946       UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours';
947       RAISE EXCEPTION '%', SQLERRM;
948     WHEN OTHERS THEN
949       RAISE NOTICE '%', SQLERRM;
950 END
951 $$
952     LANGUAGE plpgsql;
953
954
955 ALTER FUNCTION stratcon.rollup_matrix_numeric_12hours() OWNER TO postgres;
956
957 --
958 -- Name: rollup_matrix_numeric_20m(); Type: FUNCTION; Schema: stratcon; Owner: omniti
959 --
960
961 CREATE FUNCTION rollup_matrix_numeric_20m() RETURNS void
962     AS $$
963 DECLARE
964  
965  rec stratcon.rollup_matrix_numeric_20m%rowtype;
966  v_sql TEXT;
967  v_min_whence TIMESTAMPTZ;
968  v_max_rollup_20 TIMESTAMPTZ;
969  v_whence TIMESTAMPTZ;
970  rows INT;
971  v_nrunning INT;
972  v_self VARCHAR(22);
973  whenceint RECORD;
974 BEGIN
975
976   SELECT COUNT(1) INTO v_nrunning
977     from stratcon.rollup_runner t, pg_stat_activity a
978    where rollup_table ='rollup_matrix_numeric_20m'
979      and runner = procpid || '.' || date_part('epoch',backend_start);
980
981   IF v_nrunning > 0 THEN
982     RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running';
983     RETURN ;
984   END IF;
985
986   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
987     FROM pg_stat_activity
988    WHERE procpid = pg_backend_pid();
989
990   IF v_self IS NULL THEN
991     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot self-identify';
992   END IF;
993
994   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_20m''';
995
996   EXECUTE v_sql;
997
998 FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='20 minutes' LOOP
999
1000  SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='20 minutes'
1001         INTO v_min_whence;
1002        
1003  SELECT MAX(rollup_time) FROM  stratcon.rollup_matrix_numeric_20m
1004          INTO v_max_rollup_20;       
1005  
1006  -- Insert Log for Hourly rollup
1007    
1008    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour'
1009            INTO v_whence;
1010       IF NOT FOUND THEN
1011        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour');
1012    END IF;
1013    
1014  IF v_min_whence <= v_max_rollup_20 THEN
1015
1016    DELETE FROM stratcon.rollup_matrix_numeric_20m
1017                 WHERE rollup_time = v_min_whence;
1018  
1019  END IF;
1020
1021  FOR rec IN
1022                 SELECT sid , name,v_min_whence as rollup_time,
1023                        SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value,
1024        MIN(min_value) as min_value ,MAX(max_value) as max_value
1025        FROM stratcon.rollup_matrix_numeric_5m
1026                       WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence -'20 minutes'::interval
1027                 GROUP BY sid,name
1028  
1029        LOOP
1030    
1031        
1032         INSERT INTO stratcon.rollup_matrix_numeric_20m
1033          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
1034          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
1035        
1036    END LOOP;
1037
1038   -- Delete from whence log table
1039  
1040   DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='20 minutes';
1041  
1042   v_min_whence:= NULL;
1043   v_max_rollup_20:= NULL;
1044
1045  END LOOP;
1046  
1047   UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m';
1048  
1049 RETURN;
1050
1051 EXCEPTION
1052     WHEN RAISE_EXCEPTION THEN
1053        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m';
1054        RAISE EXCEPTION '%', SQLERRM;
1055     WHEN OTHERS THEN
1056       RAISE NOTICE '%', SQLERRM;
1057 END
1058 $$
1059     LANGUAGE plpgsql;
1060
1061
1062 ALTER FUNCTION stratcon.rollup_matrix_numeric_20m() OWNER TO omniti;
1063
1064 --
1065 -- Name: rollup_matrix_numeric_5m(); Type: FUNCTION; Schema: stratcon; Owner: omniti
1066 --
1067
1068 CREATE FUNCTION rollup_matrix_numeric_5m() RETURNS void
1069     AS $$
1070 DECLARE
1071  
1072  rec stratcon.rollup_matrix_numeric_5m%rowtype;
1073  v_sql TEXT;
1074  v_min_whence TIMESTAMPTZ;
1075  v_max_rollup_5 TIMESTAMPTZ;
1076  v_whence TIMESTAMPTZ;
1077  rows INT;
1078  v_nrunning INT;
1079  v_self VARCHAR(22);
1080  whenceint RECORD;
1081 BEGIN
1082
1083   SELECT COUNT(1) INTO v_nrunning
1084     from stratcon.rollup_runner t, pg_stat_activity a
1085    where rollup_table ='rollup_matrix_numeric_5m'
1086      and runner = procpid || '.' || date_part('epoch',backend_start);
1087
1088   IF v_nrunning > 0 THEN
1089     RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running';
1090     RETURN ;
1091   END IF;
1092
1093   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
1094     FROM pg_stat_activity
1095    WHERE procpid = pg_backend_pid();
1096
1097   IF v_self IS NULL THEN
1098     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot self-identify';
1099   END IF;
1100
1101   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_5m''';
1102
1103   EXECUTE v_sql;
1104
1105 FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='5 minutes' LOOP
1106        
1107
1108  SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='5 minutes'
1109         INTO v_min_whence;
1110        
1111  SELECT MAX(rollup_time) FROM  stratcon.rollup_matrix_numeric_5m
1112          INTO v_max_rollup_5;       
1113  
1114  -- Insert Log for 20 minutes rollup
1115    
1116    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'
1117            INTO v_whence;
1118       IF NOT FOUND THEN
1119        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');
1120    END IF;
1121    
1122  IF v_min_whence <= v_max_rollup_5 THEN
1123
1124    DELETE FROM stratcon.rollup_matrix_numeric_5m
1125                 WHERE rollup_time = v_min_whence;
1126  
1127  END IF;
1128
1129  FOR rec IN
1130                 SELECT sid , name,v_min_whence as rollup_time,
1131                       COUNT(1) as count_rows ,AVG(value) as avg_value,MIN(value) as min_value ,MAX(value) as max_value
1132                       FROM stratcon.loading_dock_metric_numeric_s
1133                       WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval
1134                 GROUP BY rollup_time,sid,name
1135  
1136        LOOP
1137    
1138        
1139         INSERT INTO stratcon.rollup_matrix_numeric_5m
1140          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
1141          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
1142        
1143    END LOOP;
1144
1145   -- Delete from whence log table
1146  
1147   DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes';
1148  
1149  v_min_whence:= NULL;
1150  v_max_rollup_5:= NULL;
1151  
1152  END LOOP;
1153  
1154   UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m';
1155  
1156 RETURN;
1157
1158 EXCEPTION
1159     WHEN RAISE_EXCEPTION THEN
1160        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m';
1161        RAISE EXCEPTION '%', SQLERRM;
1162     WHEN OTHERS THEN
1163          RAISE NOTICE '%', SQLERRM;
1164 END
1165 $$
1166     LANGUAGE plpgsql;
1167
1168
1169 ALTER FUNCTION stratcon.rollup_matrix_numeric_5m() OWNER TO omniti;
1170
1171 --
1172 -- Name: rollup_matrix_numeric_60m(); Type: FUNCTION; Schema: stratcon; Owner: omniti
1173 --
1174
1175 CREATE FUNCTION rollup_matrix_numeric_60m() RETURNS void
1176     AS $$
1177 DECLARE
1178   rec stratcon.rollup_matrix_numeric_60m%rowtype;
1179   v_sql TEXT;
1180   v_min_whence TIMESTAMPTZ;
1181   v_max_rollup_60 TIMESTAMPTZ;
1182   v_whence TIMESTAMPTZ;
1183   v_nrunning INT;
1184   v_self VARCHAR(22);
1185   whenceint RECORD;
1186 BEGIN
1187
1188   SELECT COUNT(1) INTO v_nrunning
1189     from stratcon.rollup_runner t, pg_stat_activity a
1190    where rollup_table ='rollup_matrix_numeric_60m'
1191      and runner = procpid || '.' || date_part('epoch',backend_start);
1192
1193   IF v_nrunning > 0 THEN
1194     RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running';
1195     RETURN ;
1196   END IF;
1197
1198   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
1199     FROM pg_stat_activity
1200    WHERE procpid = pg_backend_pid();
1201
1202   IF v_self IS NULL THEN
1203     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot self-identify';
1204   END IF;
1205
1206   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_60m''';
1207
1208   EXECUTE v_sql;
1209
1210 FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='1 hour' LOOP
1211            
1212   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='1 hour'
1213          INTO v_min_whence;
1214          
1215   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_60m
1216          INTO v_max_rollup_60;   
1217
1218 -- Insert Log for 6 Hour rollup
1219    
1220    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'
1221            INTO v_whence;
1222       IF NOT FOUND THEN
1223        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');
1224    END IF;
1225    
1226    
1227   IF v_min_whence <= v_max_rollup_60 THEN
1228  
1229   DELETE FROM stratcon.rollup_matrix_numeric_60m
1230        WHERE rollup_time= v_min_whence;
1231
1232   END IF;
1233  
1234     FOR rec IN
1235                 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,
1236          MIN(min_value) as min_value ,MAX(max_value) as max_value
1237          FROM stratcon.rollup_matrix_numeric_20m
1238            WHERE date_hour(rollup_time)= v_min_whence
1239                    GROUP BY date_hour(rollup_time),sid,name
1240         LOOP
1241      
1242           INSERT INTO stratcon.rollup_matrix_numeric_60m
1243           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
1244           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
1245          
1246      END LOOP;
1247
1248
1249 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='1 hour';
1250
1251 v_min_whence := NULL;
1252 v_max_rollup_60 := NULL;
1253
1254 END LOOP;
1255
1256 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m';
1257
1258 RETURN;
1259
1260 EXCEPTION
1261     WHEN RAISE_EXCEPTION THEN
1262        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m';
1263        RAISE EXCEPTION '%', SQLERRM;
1264     WHEN OTHERS THEN
1265       RAISE NOTICE '%', SQLERRM;
1266 END
1267 $$
1268     LANGUAGE plpgsql;
1269
1270
1271 ALTER FUNCTION stratcon.rollup_matrix_numeric_60m() OWNER TO omniti;
1272
1273 --
1274 -- Name: rollup_matrix_numeric_6hours(); Type: FUNCTION; Schema: stratcon; Owner: omniti
1275 --
1276
1277 CREATE FUNCTION rollup_matrix_numeric_6hours() RETURNS void
1278     AS $$
1279 DECLARE
1280   rec stratcon.rollup_matrix_numeric_6hours%rowtype;
1281   v_sql TEXT;
1282   v_min_whence TIMESTAMPTZ;
1283   v_max_rollup_6 TIMESTAMPTZ;
1284   v_whence TIMESTAMPTZ;
1285   v_nrunning INT;
1286   v_self VARCHAR(22);
1287   whenceint RECORD; 
1288 BEGIN
1289
1290   SELECT COUNT(1) INTO v_nrunning
1291     from stratcon.rollup_runner t, pg_stat_activity a
1292     where rollup_table ='rollup_matrix_numeric_6hours'
1293      and runner = procpid || '.' || date_part('epoch',backend_start);
1294
1295   IF v_nrunning > 0 THEN
1296     RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running';
1297     RETURN ;
1298   END IF;
1299
1300   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
1301     FROM pg_stat_activity
1302      WHERE procpid = pg_backend_pid();
1303
1304   IF v_self IS NULL THEN
1305     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot self-identify';
1306    END IF;
1307
1308    v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_6hours''';
1309
1310   EXECUTE v_sql;
1311
1312 FOR whenceint IN SELECT * FROM stratcon.log_whence_s WHERE interval='6 hours' LOOP
1313
1314   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='6 hours'
1315          INTO v_min_whence;
1316          
1317   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_6hours
1318          INTO v_max_rollup_6;   
1319
1320 -- Insert Log for 12 Hours rollup
1321    
1322    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'
1323            INTO v_whence;
1324       IF NOT FOUND THEN
1325        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');
1326    END IF;
1327    
1328    
1329   IF v_min_whence <= v_max_rollup_6 THEN
1330  
1331   DELETE FROM stratcon.rollup_matrix_numeric_6hours
1332        WHERE rollup_time= v_min_whence;
1333
1334   END IF;
1335  
1336     FOR rec IN
1337                 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,
1338          MIN(min_value) as min_value ,MAX(max_value) as max_value
1339          FROM stratcon.rollup_matrix_numeric_60m
1340            WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval
1341                    GROUP BY sid,name
1342         LOOP
1343      
1344        
1345           INSERT INTO stratcon.rollup_matrix_numeric_6hours
1346           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
1347           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
1348      END LOOP;
1349
1350
1351 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='6 hours';
1352 v_min_whence := NULL;
1353 v_max_rollup_6 := NULL;
1354
1355 END LOOP;
1356
1357 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';
1358
1359 RETURN;
1360
1361 EXCEPTION
1362     WHEN RAISE_EXCEPTION THEN
1363        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';
1364        RAISE EXCEPTION '%', SQLERRM;
1365     WHEN OTHERS THEN
1366        RAISE NOTICE '%', SQLERRM;
1367 END
1368 $$
1369     LANGUAGE plpgsql;
1370
1371
1372 ALTER FUNCTION stratcon.rollup_matrix_numeric_6hours() OWNER TO omniti;
1373
1374 --
1375 -- Name: seq_sid; Type: SEQUENCE; Schema: stratcon; Owner: stratcon
1376 --
1377
1378 CREATE SEQUENCE seq_sid
1379     INCREMENT BY 1
1380     NO MAXVALUE
1381     NO MINVALUE
1382     CACHE 1;
1383
1384
1385 ALTER TABLE stratcon.seq_sid OWNER TO stratcon;
1386
1387 --
1388 -- Name: loading_dock_check_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1389 --
1390
1391 ALTER TABLE ONLY loading_dock_check_s
1392     ADD CONSTRAINT loading_dock_check_s_pkey PRIMARY KEY (sid, id, whence);
1393
1394
1395 --
1396 -- Name: loading_dock_metric_numeric_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1397 --
1398
1399 ALTER TABLE ONLY loading_dock_metric_numeric_s
1400     ADD CONSTRAINT loading_dock_metric_numeric_s_pkey PRIMARY KEY (whence, sid, name);
1401
1402
1403 --
1404 -- Name: loading_dock_metric_text_s_change_log_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1405 --
1406
1407 ALTER TABLE ONLY loading_dock_metric_text_s_change_log
1408     ADD CONSTRAINT loading_dock_metric_text_s_change_log_pkey PRIMARY KEY (whence, sid, name);
1409
1410
1411 --
1412 -- Name: loading_dock_metric_text_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1413 --
1414
1415 ALTER TABLE ONLY loading_dock_metric_text_s
1416     ADD CONSTRAINT loading_dock_metric_text_s_pkey PRIMARY KEY (whence, sid, name);
1417
1418
1419 --
1420 -- Name: loading_dock_status_s_change_log_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1421 --
1422
1423 ALTER TABLE ONLY loading_dock_status_s_change_log
1424     ADD CONSTRAINT loading_dock_status_s_change_log_pkey PRIMARY KEY (sid, whence);
1425
1426
1427 --
1428 -- Name: loading_dock_status_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1429 --
1430
1431 ALTER TABLE ONLY loading_dock_status_s
1432     ADD CONSTRAINT loading_dock_status_s_pkey PRIMARY KEY (sid, whence);
1433
1434
1435 --
1436 -- Name: log_whence_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1437 --
1438
1439 ALTER TABLE ONLY log_whence_s
1440     ADD CONSTRAINT log_whence_s_pkey PRIMARY KEY (whence, "interval");
1441
1442
1443 --
1444 -- Name: map_uuid_to_sid_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1445 --
1446
1447 ALTER TABLE ONLY map_uuid_to_sid
1448     ADD CONSTRAINT map_uuid_to_sid_pkey PRIMARY KEY (id, sid);
1449
1450
1451 --
1452 -- Name: metric_name_summary_pk; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1453 --
1454
1455 ALTER TABLE ONLY metric_name_summary
1456     ADD CONSTRAINT metric_name_summary_pk UNIQUE (sid, metric_name, metric_type);
1457
1458
1459 --
1460 -- Name: rollup_matrix_numeric_12hours_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: postgres; Tablespace:
1461 --
1462
1463 ALTER TABLE ONLY rollup_matrix_numeric_12hours
1464     ADD CONSTRAINT rollup_matrix_numeric_12hours_pkey PRIMARY KEY (rollup_time, sid, name);
1465
1466
1467 --
1468 -- Name: rollup_matrix_numeric_20m_new_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1469 --
1470
1471 ALTER TABLE ONLY rollup_matrix_numeric_20m
1472     ADD CONSTRAINT rollup_matrix_numeric_20m_new_pkey PRIMARY KEY (rollup_time, sid, name);
1473
1474
1475 --
1476 -- Name: rollup_matrix_numeric_5m_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1477 --
1478
1479 ALTER TABLE ONLY rollup_matrix_numeric_5m
1480     ADD CONSTRAINT rollup_matrix_numeric_5m_pkey PRIMARY KEY (rollup_time, sid, name);
1481
1482
1483 --
1484 -- Name: rollup_matrix_numeric_60m_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1485 --
1486
1487 ALTER TABLE ONLY rollup_matrix_numeric_60m
1488     ADD CONSTRAINT rollup_matrix_numeric_60m_pkey PRIMARY KEY (rollup_time, sid, name);
1489
1490
1491 --
1492 -- Name: rollup_matrix_numeric_6hours_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: omniti; Tablespace:
1493 --
1494
1495 ALTER TABLE ONLY rollup_matrix_numeric_6hours
1496     ADD CONSTRAINT rollup_matrix_numeric_6hours_pkey PRIMARY KEY (rollup_time, sid, name);
1497
1498
1499 --
1500 -- Name: idx_rollup_matrix_numeric_20m_rollup_time; Type: INDEX; Schema: stratcon; Owner: omniti; Tablespace:
1501 --
1502
1503 CREATE INDEX idx_rollup_matrix_numeric_20m_rollup_time ON rollup_matrix_numeric_20m USING btree (date_hour(rollup_time));
1504
1505
1506 --
1507 -- Name: loading_dock_metric_numeric_s_whence_log; Type: TRIGGER; Schema: stratcon; Owner: omniti
1508 --
1509
1510 CREATE TRIGGER loading_dock_metric_numeric_s_whence_log
1511     AFTER INSERT ON loading_dock_metric_numeric_s
1512     FOR EACH ROW
1513     EXECUTE PROCEDURE loading_dock_metric_numeric_s_whence_log();
1514
1515
1516 --
1517 -- Name: loading_dock_metric_text_s_change_log; Type: TRIGGER; Schema: stratcon; Owner: omniti
1518 --
1519
1520 CREATE TRIGGER loading_dock_metric_text_s_change_log
1521     AFTER INSERT ON loading_dock_metric_text_s
1522     FOR EACH ROW
1523     EXECUTE PROCEDURE loading_dock_metric_text_s_change_log();
1524
1525
1526 --
1527 -- Name: loading_dock_status_s_change_log; Type: TRIGGER; Schema: stratcon; Owner: omniti
1528 --
1529
1530 CREATE TRIGGER loading_dock_status_s_change_log
1531     AFTER INSERT ON loading_dock_status_s
1532     FOR EACH ROW
1533     EXECUTE PROCEDURE loading_dock_status_s_change_log();
1534
1535
1536 --
1537 -- Name: stratcon; Type: ACL; Schema: -; Owner: omniti
1538 --
1539
1540 REVOKE ALL ON SCHEMA stratcon FROM PUBLIC;
1541 REVOKE ALL ON SCHEMA stratcon FROM omniti;
1542 GRANT ALL ON SCHEMA stratcon TO omniti;
1543 GRANT USAGE ON SCHEMA stratcon TO stratcon;
1544
1545
1546 --
1547 -- Name: loading_dock_check_s; Type: ACL; Schema: stratcon; Owner: omniti
1548 --
1549
1550 REVOKE ALL ON TABLE loading_dock_check_s FROM PUBLIC;
1551 REVOKE ALL ON TABLE loading_dock_check_s FROM omniti;
1552 GRANT ALL ON TABLE loading_dock_check_s TO omniti;
1553 GRANT SELECT,INSERT ON TABLE loading_dock_check_s TO stratcon;
1554
1555
1556 --
1557 -- Name: loading_dock_metric_numeric_s; Type: ACL; Schema: stratcon; Owner: omniti
1558 --
1559
1560 REVOKE ALL ON TABLE loading_dock_metric_numeric_s FROM PUBLIC;
1561 REVOKE ALL ON TABLE loading_dock_metric_numeric_s FROM omniti;
1562 GRANT ALL ON TABLE loading_dock_metric_numeric_s TO omniti;
1563 GRANT SELECT,INSERT ON TABLE loading_dock_metric_numeric_s TO stratcon;
1564
1565
1566 --
1567 -- Name: loading_dock_metric_text_s; Type: ACL; Schema: stratcon; Owner: omniti
1568 --
1569
1570 REVOKE ALL ON TABLE loading_dock_metric_text_s FROM PUBLIC;
1571 REVOKE ALL ON TABLE loading_dock_metric_text_s FROM omniti;
1572 GRANT ALL ON TABLE loading_dock_metric_text_s TO omniti;
1573 GRANT SELECT,INSERT,DELETE ON TABLE loading_dock_metric_text_s TO stratcon;
1574
1575
1576 --
1577 -- Name: loading_dock_metric_text_s_change_log; Type: ACL; Schema: stratcon; Owner: omniti
1578 --
1579
1580 REVOKE ALL ON TABLE loading_dock_metric_text_s_change_log FROM PUBLIC;
1581 REVOKE ALL ON TABLE loading_dock_metric_text_s_change_log FROM omniti;
1582 GRANT ALL ON TABLE loading_dock_metric_text_s_change_log TO omniti;
1583 GRANT SELECT,INSERT ON TABLE loading_dock_metric_text_s_change_log TO stratcon;
1584
1585
1586 --
1587 -- Name: loading_dock_status_s; Type: ACL; Schema: stratcon; Owner: omniti
1588 --
1589
1590 REVOKE ALL ON TABLE loading_dock_status_s FROM PUBLIC;
1591 REVOKE ALL ON TABLE loading_dock_status_s FROM omniti;
1592 GRANT ALL ON TABLE loading_dock_status_s TO omniti;
1593 GRANT SELECT,INSERT ON TABLE loading_dock_status_s TO stratcon;
1594
1595
1596 --
1597 -- Name: loading_dock_status_s_change_log; Type: ACL; Schema: stratcon; Owner: omniti
1598 --
1599
1600 REVOKE ALL ON TABLE loading_dock_status_s_change_log FROM PUBLIC;
1601 REVOKE ALL ON TABLE loading_dock_status_s_change_log FROM omniti;
1602 GRANT ALL ON TABLE loading_dock_status_s_change_log TO omniti;
1603 GRANT SELECT,INSERT,DELETE ON TABLE loading_dock_status_s_change_log TO stratcon;
1604
1605
1606 --
1607 -- Name: log_whence_s; Type: ACL; Schema: stratcon; Owner: omniti
1608 --
1609
1610 REVOKE ALL ON TABLE log_whence_s FROM PUBLIC;
1611 REVOKE ALL ON TABLE log_whence_s FROM omniti;
1612 GRANT ALL ON TABLE log_whence_s TO omniti;
1613 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE log_whence_s TO stratcon;
1614
1615
1616 --
1617 -- Name: map_uuid_to_sid; Type: ACL; Schema: stratcon; Owner: omniti
1618 --
1619
1620 REVOKE ALL ON TABLE map_uuid_to_sid FROM PUBLIC;
1621 REVOKE ALL ON TABLE map_uuid_to_sid FROM omniti;
1622 GRANT ALL ON TABLE map_uuid_to_sid TO omniti;
1623 GRANT SELECT,INSERT ON TABLE map_uuid_to_sid TO stratcon;
1624
1625
1626 --
1627 -- Name: metric_name_summary; Type: ACL; Schema: stratcon; Owner: omniti
1628 --
1629
1630 REVOKE ALL ON TABLE metric_name_summary FROM PUBLIC;
1631 REVOKE ALL ON TABLE metric_name_summary FROM omniti;
1632 GRANT ALL ON TABLE metric_name_summary TO omniti;
1633 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE metric_name_summary TO stratcon;
1634
1635
1636 --
1637 -- Name: rollup_matrix_numeric_12hours; Type: ACL; Schema: stratcon; Owner: postgres
1638 --
1639
1640 REVOKE ALL ON TABLE rollup_matrix_numeric_12hours FROM PUBLIC;
1641 REVOKE ALL ON TABLE rollup_matrix_numeric_12hours FROM postgres;
1642 GRANT ALL ON TABLE rollup_matrix_numeric_12hours TO postgres;
1643 GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_12hours TO stratcon;
1644 GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_12hours TO omniti;
1645
1646
1647 --
1648 -- Name: rollup_matrix_numeric_20m; Type: ACL; Schema: stratcon; Owner: omniti
1649 --
1650
1651 REVOKE ALL ON TABLE rollup_matrix_numeric_20m FROM PUBLIC;
1652 REVOKE ALL ON TABLE rollup_matrix_numeric_20m FROM omniti;
1653 GRANT ALL ON TABLE rollup_matrix_numeric_20m TO omniti;
1654 GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_20m TO stratcon;
1655
1656
1657 --
1658 -- Name: rollup_matrix_numeric_5m; Type: ACL; Schema: stratcon; Owner: omniti
1659 --
1660
1661 REVOKE ALL ON TABLE rollup_matrix_numeric_5m FROM PUBLIC;
1662 REVOKE ALL ON TABLE rollup_matrix_numeric_5m FROM omniti;
1663 GRANT ALL ON TABLE rollup_matrix_numeric_5m TO omniti;
1664 GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_5m TO stratcon;
1665
1666
1667 --
1668 -- Name: rollup_matrix_numeric_60m; Type: ACL; Schema: stratcon; Owner: omniti
1669 --
1670
1671 REVOKE ALL ON TABLE rollup_matrix_numeric_60m FROM PUBLIC;
1672 REVOKE ALL ON TABLE rollup_matrix_numeric_60m FROM omniti;
1673 GRANT ALL ON TABLE rollup_matrix_numeric_60m TO omniti;
1674 GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_60m TO stratcon;
1675
1676
1677 --
1678 -- Name: rollup_matrix_numeric_6hours; Type: ACL; Schema: stratcon; Owner: omniti
1679 --
1680
1681 REVOKE ALL ON TABLE rollup_matrix_numeric_6hours FROM PUBLIC;
1682 REVOKE ALL ON TABLE rollup_matrix_numeric_6hours FROM omniti;
1683 GRANT ALL ON TABLE rollup_matrix_numeric_6hours TO omniti;
1684 GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_6hours TO stratcon;
1685
1686
1687 --
1688 -- Name: rollup_runner; Type: ACL; Schema: stratcon; Owner: omniti
1689 --
1690
1691 REVOKE ALL ON TABLE rollup_runner FROM PUBLIC;
1692 REVOKE ALL ON TABLE rollup_runner FROM omniti;
1693 GRANT ALL ON TABLE rollup_runner TO omniti;
1694 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE rollup_runner TO stratcon;
1695
1696
1697 --
1698 -- PostgreSQL database dump complete
1699 --
1700
Note: See TracBrowser for help on using the browser.