root/sql/reconnoiter_ddl_dump.sql

Revision 43ec19ac079e004379a4c8d8117756c8d622eff8, 79.5 kB (checked in by Denish Patel <denish@omniti.com>, 6 years ago)

added index

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