root/sql/reconnoiter_ddl_dump.sql

Revision 848c999201996fa821ee897f8bc9e3c0979a575f, 85.1 kB (checked in by Denish Patel <denish@omniti.com>, 6 years ago)

pg ddl export

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