root/sql/reconnoiter_ddl_dump.sql

Revision 9e9c974e402a479ebbc927ca69c82166708b2d80, 85.5 kB (checked in by Denish Patel <denish@omniti.com>, 6 years ago)

another fix for saved graph serach

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