root/sql/reconnoiter_ddl_dump.sql

Revision 9295ab0de02cda482cfd2a5762f8a76727b480d1, 88.8 kB (checked in by Denish Patel <denish@omniti.com>, 5 years ago)

pg ddl update

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