root/sql/reconnoiter_ddl_dump.sql

Revision 15df64bd213a7a14a521d3d2d4418ee9379272aa, 83.5 kB (checked in by Denish Patel <denish@omniti.com>, 6 years ago)

implemented add/remove tags functionality for graphs and created function to update ts_search_all vector based on title and graph_tags, applied trigger to call the function on saved_graphs

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