root/sql/reconnoiter_ddl_dump.sql

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

fixed ownership of tables

  • Property mode set to 100644
Line 
1 --
2 -- PostgreSQL database dump
3 --
4
5 SET client_encoding = 'UTF8';
6 SET standard_conforming_strings = off;
7 SET check_function_bodies = false;
8 SET client_min_messages = warning;
9 SET escape_string_warning = off;
10
11 --
12 -- Name: prism; Type: SCHEMA; Schema: -; Owner: prism
13 --
14
15 CREATE SCHEMA prism;
16
17
18 ALTER SCHEMA prism OWNER TO prism;
19
20 --
21 -- Name: stratcon; Type: SCHEMA; Schema: -; Owner: stratcon
22 --
23
24 CREATE SCHEMA stratcon;
25
26
27 ALTER SCHEMA stratcon OWNER TO stratcon;
28
29 --
30 -- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: omniti
31 --
32
33 CREATE PROCEDURAL LANGUAGE plpgsql;
34
35
36 ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO omniti;
37
38 SET search_path = prism, pg_catalog;
39
40 SET default_tablespace = '';
41
42 SET default_with_oids = false;
43
44 --
45 -- Name: saved_graphs; Type: TABLE; Schema: prism; Owner: reconnoiter; Tablespace:
46 --
47
48 CREATE TABLE saved_graphs (
49     graphid uuid NOT NULL,
50     json text NOT NULL,
51     saved boolean DEFAULT false NOT NULL,
52     title text,
53     last_update timestamp without time zone NOT NULL,
54     ts_search_all tsvector,
55     graph_tags text[]
56 );
57
58
59 ALTER TABLE prism.saved_graphs OWNER TO reconnoiter;
60
61 --
62 -- Name: saved_graphs_dep; Type: TABLE; Schema: prism; Owner: reconnoiter; Tablespace:
63 --
64
65 CREATE TABLE saved_graphs_dep (
66     graphid uuid NOT NULL,
67     sid integer NOT NULL,
68     metric_name text NOT NULL,
69     metric_type character varying(22)
70 );
71
72
73 ALTER TABLE prism.saved_graphs_dep OWNER TO reconnoiter;
74
75 --
76 -- Name: saved_worksheets; Type: TABLE; Schema: prism; Owner: reconnoiter; Tablespace:
77 --
78
79 CREATE TABLE saved_worksheets (
80     sheetid uuid NOT NULL,
81     title text,
82     saved boolean DEFAULT false,
83     ts_search_all tsvector,
84     tags text[],
85     last_update timestamp with time zone DEFAULT now() NOT NULL
86 );
87
88
89 ALTER TABLE prism.saved_worksheets OWNER TO reconnoiter;
90
91 --
92 -- Name: saved_worksheets_dep; Type: TABLE; Schema: prism; Owner: reconnoiter; Tablespace:
93 --
94
95 CREATE TABLE saved_worksheets_dep (
96     sheetid uuid NOT NULL,
97     ordering integer NOT NULL,
98     graphid uuid NOT NULL
99 );
100
101
102 ALTER TABLE prism.saved_worksheets_dep OWNER TO reconnoiter;
103
104 SET search_path = public, pg_catalog;
105
106 --
107 -- Name: foo; Type: TABLE; Schema: public; Owner: omniti; Tablespace:
108 --
109
110 CREATE TABLE foo (
111     a timestamp with time zone
112 );
113
114
115 ALTER TABLE public.foo OWNER TO omniti;
116
117 SET default_with_oids = true;
118
119 --
120 -- Name: pga_diagrams; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
121 --
122
123 CREATE TABLE pga_diagrams (
124     diagramname character varying(64) NOT NULL,
125     diagramtables text,
126     diagramlinks text
127 );
128
129
130 ALTER TABLE public.pga_diagrams OWNER TO reconnoiter;
131
132 --
133 -- Name: pga_forms; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
134 --
135
136 CREATE TABLE pga_forms (
137     formname character varying(64) NOT NULL,
138     formsource text
139 );
140
141
142 ALTER TABLE public.pga_forms OWNER TO reconnoiter;
143
144 --
145 -- Name: pga_graphs; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
146 --
147
148 CREATE TABLE pga_graphs (
149     graphname character varying(64) NOT NULL,
150     graphsource text,
151     graphcode text
152 );
153
154
155 ALTER TABLE public.pga_graphs OWNER TO reconnoiter;
156
157 --
158 -- Name: pga_images; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
159 --
160
161 CREATE TABLE pga_images (
162     imagename character varying(64) NOT NULL,
163     imagesource text
164 );
165
166
167 ALTER TABLE public.pga_images OWNER TO reconnoiter;
168
169 --
170 -- Name: pga_layout; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
171 --
172
173 CREATE TABLE pga_layout (
174     tablename character varying(64) NOT NULL,
175     nrcols smallint,
176     colnames text,
177     colwidth text
178 );
179
180
181 ALTER TABLE public.pga_layout OWNER TO reconnoiter;
182
183 --
184 -- Name: pga_queries; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
185 --
186
187 CREATE TABLE pga_queries (
188     queryname character varying(64) NOT NULL,
189     querytype character(1),
190     querycommand text,
191     querytables text,
192     querylinks text,
193     queryresults text,
194     querycomments text
195 );
196
197
198 ALTER TABLE public.pga_queries OWNER TO reconnoiter;
199
200 --
201 -- Name: pga_reports; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
202 --
203
204 CREATE TABLE pga_reports (
205     reportname character varying(64) NOT NULL,
206     reportsource text,
207     reportbody text,
208     reportprocs text,
209     reportoptions text
210 );
211
212
213 ALTER TABLE public.pga_reports OWNER TO reconnoiter;
214
215 --
216 -- Name: pga_scripts; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
217 --
218
219 CREATE TABLE pga_scripts (
220     scriptname character varying(64) NOT NULL,
221     scriptsource text
222 );
223
224
225 ALTER TABLE public.pga_scripts OWNER TO reconnoiter;
226
227 SET default_with_oids = false;
228
229 --
230 -- Name: varnish_huh; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
231 --
232
233 CREATE TABLE varnish_huh (
234     sid integer,
235     whence timestamp with time zone,
236     name text,
237     value numeric
238 );
239
240
241 ALTER TABLE public.varnish_huh OWNER TO reconnoiter;
242
243 --
244 -- Name: varnish_huh2; Type: TABLE; Schema: public; Owner: reconnoiter; Tablespace:
245 --
246
247 CREATE TABLE varnish_huh2 (
248     sid integer,
249     whence timestamp with time zone,
250     name text,
251     value numeric
252 );
253
254
255 ALTER TABLE public.varnish_huh2 OWNER TO reconnoiter;
256
257 SET search_path = stratcon, pg_catalog;
258
259 --
260 -- Name: current_metric_text; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
261 --
262
263 CREATE TABLE current_metric_text (
264     sid integer NOT NULL,
265     whence timestamp with time zone NOT NULL,
266     name text NOT NULL,
267     value text
268 );
269
270
271 ALTER TABLE stratcon.current_metric_text OWNER TO reconnoiter;
272
273 --
274 -- Name: current_node_config; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
275 --
276
277 CREATE TABLE current_node_config (
278     remote_address inet NOT NULL,
279     node_type text NOT NULL,
280     whence timestamp with time zone NOT NULL,
281     config xml NOT NULL
282 );
283
284
285 ALTER TABLE stratcon.current_node_config OWNER TO reconnoiter;
286
287 --
288 -- Name: current_node_config_changelog; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
289 --
290
291 CREATE TABLE current_node_config_changelog (
292     remote_address inet NOT NULL,
293     node_type text NOT NULL,
294     whence timestamp with time zone NOT NULL,
295     config xml NOT NULL
296 );
297
298
299 ALTER TABLE stratcon.current_node_config_changelog OWNER TO reconnoiter;
300
301 --
302 -- Name: loading_dock_check_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
303 --
304
305 CREATE TABLE loading_dock_check_s (
306     sid integer NOT NULL,
307     remote_address inet,
308     whence timestamp with time zone NOT NULL,
309     id uuid NOT NULL,
310     target text NOT NULL,
311     module text NOT NULL,
312     name text NOT NULL
313 );
314
315
316 ALTER TABLE stratcon.loading_dock_check_s OWNER TO reconnoiter;
317
318 --
319 -- Name: loading_dock_metric_numeric_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
320 --
321
322 CREATE TABLE loading_dock_metric_numeric_s (
323     sid integer NOT NULL,
324     whence timestamp with time zone NOT NULL,
325     name text NOT NULL,
326     value numeric
327 );
328
329
330 ALTER TABLE stratcon.loading_dock_metric_numeric_s OWNER TO reconnoiter;
331
332 --
333 -- Name: loading_dock_metric_text_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
334 --
335
336 CREATE TABLE loading_dock_metric_text_s (
337     sid integer NOT NULL,
338     whence timestamp with time zone NOT NULL,
339     name text NOT NULL,
340     value text
341 );
342
343
344 ALTER TABLE stratcon.loading_dock_metric_text_s OWNER TO reconnoiter;
345
346 --
347 -- Name: loading_dock_metric_text_s_change_log; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
348 --
349
350 CREATE TABLE loading_dock_metric_text_s_change_log (
351     sid integer NOT NULL,
352     whence timestamp with time zone NOT NULL,
353     name text NOT NULL,
354     value text
355 );
356
357
358 ALTER TABLE stratcon.loading_dock_metric_text_s_change_log OWNER TO reconnoiter;
359
360 --
361 -- Name: loading_dock_status_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
362 --
363
364 CREATE TABLE loading_dock_status_s (
365     sid integer NOT NULL,
366     whence timestamp with time zone NOT NULL,
367     state character(1) NOT NULL,
368     availability character(1) NOT NULL,
369     duration integer NOT NULL,
370     status text
371 );
372
373
374 ALTER TABLE stratcon.loading_dock_status_s OWNER TO reconnoiter;
375
376 --
377 -- Name: loading_dock_status_s_change_log; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
378 --
379
380 CREATE TABLE loading_dock_status_s_change_log (
381     sid integer NOT NULL,
382     whence timestamp with time zone NOT NULL,
383     state character(1) NOT NULL,
384     availability character(1) NOT NULL,
385     duration integer NOT NULL,
386     status text
387 );
388
389
390 ALTER TABLE stratcon.loading_dock_status_s_change_log OWNER TO reconnoiter;
391
392 --
393 -- Name: log_whence_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
394 --
395
396 CREATE TABLE log_whence_s (
397     whence timestamp with time zone NOT NULL,
398     "interval" character varying(20) NOT NULL
399 );
400
401
402 ALTER TABLE stratcon.log_whence_s OWNER TO reconnoiter;
403
404 --
405 -- Name: map_uuid_to_sid; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
406 --
407
408 CREATE TABLE map_uuid_to_sid (
409     id uuid NOT NULL,
410     sid integer NOT NULL
411 );
412
413
414 ALTER TABLE stratcon.map_uuid_to_sid OWNER TO reconnoiter;
415
416 --
417 -- Name: metric_name_summary; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
418 --
419
420 CREATE TABLE metric_name_summary (
421     sid integer NOT NULL,
422     metric_name text NOT NULL,
423     metric_type character varying(22),
424     active boolean DEFAULT true,
425     ts_search_all tsvector
426 );
427
428
429 ALTER TABLE stratcon.metric_name_summary OWNER TO reconnoiter;
430
431 --
432 -- Name: metric_tags; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
433 --
434
435 CREATE TABLE metric_tags (
436     sid integer NOT NULL,
437     metric_name text NOT NULL,
438     metric_type character varying(22),
439     tags_array text[]
440 );
441
442
443 ALTER TABLE stratcon.metric_tags OWNER TO reconnoiter;
444
445 --
446 -- Name: mv_loading_dock_check_s; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
447 --
448
449 CREATE TABLE mv_loading_dock_check_s (
450     sid integer NOT NULL,
451     remote_address inet,
452     whence timestamp with time zone NOT NULL,
453     id uuid NOT NULL,
454     target text NOT NULL,
455     module text NOT NULL,
456     name text NOT NULL
457 );
458
459
460 ALTER TABLE stratcon.mv_loading_dock_check_s OWNER TO reconnoiter;
461
462 --
463 -- Name: rollup_matrix_numeric_12hours; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
464 --
465
466 CREATE TABLE rollup_matrix_numeric_12hours (
467     sid integer NOT NULL,
468     name text NOT NULL,
469     rollup_time timestamp with time zone NOT NULL,
470     count_rows integer,
471     avg_value numeric,
472     counter_dev numeric
473 );
474
475
476 ALTER TABLE stratcon.rollup_matrix_numeric_12hours OWNER TO reconnoiter;
477
478 --
479 -- Name: rollup_matrix_numeric_20m; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
480 --
481
482 CREATE TABLE rollup_matrix_numeric_20m (
483     sid integer NOT NULL,
484     name text NOT NULL,
485     rollup_time timestamp with time zone NOT NULL,
486     count_rows integer,
487     avg_value numeric,
488     counter_dev numeric
489 );
490
491
492 ALTER TABLE stratcon.rollup_matrix_numeric_20m OWNER TO reconnoiter;
493
494 --
495 -- Name: rollup_matrix_numeric_5m; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
496 --
497
498 CREATE TABLE rollup_matrix_numeric_5m (
499     sid integer NOT NULL,
500     name text NOT NULL,
501     rollup_time timestamp with time zone NOT NULL,
502     count_rows integer,
503     avg_value numeric,
504     counter_dev numeric
505 );
506
507
508 ALTER TABLE stratcon.rollup_matrix_numeric_5m OWNER TO reconnoiter;
509
510 --
511 -- Name: rollup_matrix_numeric_60m; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
512 --
513
514 CREATE TABLE rollup_matrix_numeric_60m (
515     sid integer NOT NULL,
516     name text NOT NULL,
517     rollup_time timestamp with time zone NOT NULL,
518     count_rows integer,
519     avg_value numeric,
520     counter_dev numeric
521 );
522
523
524 ALTER TABLE stratcon.rollup_matrix_numeric_60m OWNER TO reconnoiter;
525
526 --
527 -- Name: rollup_matrix_numeric_6hours; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
528 --
529
530 CREATE TABLE rollup_matrix_numeric_6hours (
531     sid integer NOT NULL,
532     name text NOT NULL,
533     rollup_time timestamp with time zone NOT NULL,
534     count_rows integer,
535     avg_value numeric,
536     counter_dev numeric
537 );
538
539
540 ALTER TABLE stratcon.rollup_matrix_numeric_6hours OWNER TO reconnoiter;
541
542 --
543 -- Name: rollup_runner; Type: TABLE; Schema: stratcon; Owner: reconnoiter; Tablespace:
544 --
545
546 CREATE TABLE rollup_runner (
547     rollup_table character varying(100),
548     runner character varying(22)
549 );
550
551
552 ALTER TABLE stratcon.rollup_runner OWNER TO reconnoiter;
553
554 SET search_path = prism, pg_catalog;
555
556 --
557 -- Name: add_graph_tags(uuid, text); Type: FUNCTION; Schema: prism; Owner: reconnoiter
558 --
559
560 CREATE FUNCTION add_graph_tags(in_graphid uuid, in_tags text) RETURNS void
561     AS $$
562   DECLARE
563    v_graphid uuid;
564    v_graph_tags text[];
565    new_tags_array text[];
566    BEGIN
567        SELECT graphid,graph_tags into v_graphid,v_graph_tags
568          FROM prism.saved_graphs
569            WHERE graphid =in_graphid;
570      IF NOT FOUND THEN
571                  RAISE EXCEPTION 'GraphID does not exist in saved graphs table.';
572             ELSE
573              new_tags_array:= array_append(v_graph_tags, in_tags);
574             UPDATE  prism.saved_graphs SET graph_tags = new_tags_array WHERE graphid=in_graphid;         
575       END IF;
576     RETURN;
577   END
578 $$
579     LANGUAGE plpgsql;
580
581
582 ALTER FUNCTION prism.add_graph_tags(in_graphid uuid, in_tags text) OWNER TO reconnoiter;
583
584 --
585 -- Name: add_tags(integer, text, text, text); Type: FUNCTION; Schema: prism; Owner: reconnoiter
586 --
587
588 CREATE FUNCTION add_tags(in_sid integer, in_metric_name text, in_metric_type text, in_tags text) RETURNS void
589     AS $$
590 DECLARE
591 v_sid integer;
592 v_metric_name text;
593 v_metric_typle varchar(20);
594 v_tags_array text[];
595 p_sid integer;
596 p_tags_array text[];
597 new_tags_array text[];
598  BEGIN
599      v_tags_array:= string_to_array(in_tags,'');
600      SELECT sid into p_sid
601       FROM stratcon.metric_tags
602       WHERE sid=in_sid AND metric_name=in_metric_name AND metric_type=in_metric_type;
603      IF NOT FOUND THEN
604           SELECT sid,metric_name,metric_type INTO v_sid, v_metric_name,v_metric_typle
605              FROM stratcon.metric_name_summary 
606              WHERE sid=in_sid AND metric_name=in_metric_name AND metric_type=in_metric_type;   
607           IF NOT FOUND THEN
608                RAISE EXCEPTION 'Metric does not exist in metric_name_summary table';
609           ELSE
610          INSERT INTO stratcon.metric_tags (sid,metric_name,metric_type,tags_array) values(v_sid, v_metric_name,v_metric_typle,v_tags_array);
611       END IF;
612      ELSE
613        SELECT tags_array INTO p_tags_array
614           FROM stratcon.metric_tags
615           WHERE sid=in_sid AND metric_name=in_metric_name AND metric_type=in_metric_type;
616              new_tags_array:= array_append(p_tags_array, in_tags);
617            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;         
618     END IF;
619   RETURN;
620 END
621 $$
622     LANGUAGE plpgsql;
623
624
625 ALTER FUNCTION prism.add_tags(in_sid integer, in_metric_name text, in_metric_type text, in_tags text) OWNER TO reconnoiter;
626
627 --
628 -- Name: check_name_saved_graphs(); Type: FUNCTION; Schema: prism; Owner: reconnoiter
629 --
630
631 CREATE FUNCTION check_name_saved_graphs() RETURNS trigger
632     AS $$
633 DECLARE
634 BEGIN
635     IF  NEW.saved IS true AND NEW.title IS null THEN
636     RAISE EXCEPTION 'You must name graph to save';
637     END IF;
638  RETURN NEW;
639 END
640 $$
641     LANGUAGE plpgsql;
642
643
644 ALTER FUNCTION prism.check_name_saved_graphs() OWNER TO reconnoiter;
645
646 --
647 -- Name: remove_graph_tags(uuid, text); Type: FUNCTION; Schema: prism; Owner: reconnoiter
648 --
649
650 CREATE FUNCTION remove_graph_tags(in_graphid uuid, in_tags text) RETURNS void
651     AS $$
652 DECLARE
653 v_graphid uuid;
654 v_graph_tags text[];
655 new_tags_array text[];
656 i int;
657  BEGIN
658     SELECT graphid,graph_tags into v_graphid,v_graph_tags
659             FROM prism.saved_graphs
660               WHERE graphid =in_graphid;
661      IF NOT FOUND THEN
662                     RAISE EXCEPTION 'GraphID does not exist in saved graphs table.';
663      ELSE
664         FOR i IN array_lower(v_graph_tags, 1)..array_upper(v_graph_tags, 1) LOOP
665            IF NOT v_graph_tags[i] =any(v_graph_tags) THEN
666               new_tags_array = array_append(new_tags_array, v_graph_tags[i]);
667            END IF;
668         END LOOP;
669         UPDATE  prism.saved_graphs SET graph_tags = new_tags_array WHERE graphid=in_graphid;           
670      END IF;
671   RETURN;
672 END
673 $$
674     LANGUAGE plpgsql;
675
676
677 ALTER FUNCTION prism.remove_graph_tags(in_graphid uuid, in_tags text) OWNER TO reconnoiter;
678
679 --
680 -- Name: remove_tags(integer, text, text, text); Type: FUNCTION; Schema: prism; Owner: reconnoiter
681 --
682
683 CREATE FUNCTION remove_tags(in_sid integer, in_metric_name text, in_metric_type text, in_tags text) RETURNS void
684     AS $$
685 DECLARE
686 v_tags_array text[];
687 p_sid integer;
688 p_tags_array text[];
689 new_tags_array text[];
690 i int;
691  BEGIN
692    v_tags_array:= string_to_array(in_tags,'');
693      SELECT sid,tags_array into p_sid ,p_tags_array
694       FROM stratcon.metric_tags
695       WHERE sid=in_sid AND metric_name=in_metric_name AND metric_type=in_metric_type;
696      IF NOT FOUND THEN
697          
698                RAISE EXCEPTION 'Metric tags does not found to be removed';
699          
700      ELSE
701          FOR i IN array_lower(p_tags_array, 1)..array_upper(p_tags_array, 1) LOOP
702          IF NOT p_tags_array[i] =any(v_tags_array) THEN
703             new_tags_array = array_append(new_tags_array, p_tags_array[i]);
704           END IF;
705          END LOOP;
706        
707            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;         
708     END IF;
709   RETURN;
710 END
711 $$
712     LANGUAGE plpgsql;
713
714
715 ALTER FUNCTION prism.remove_tags(in_sid integer, in_metric_name text, in_metric_type text, in_tags text) OWNER TO reconnoiter;
716
717 --
718 -- Name: saved_graphs_tsvector(uuid); Type: FUNCTION; Schema: prism; Owner: reconnoiter
719 --
720
721 CREATE FUNCTION saved_graphs_tsvector(in_graphid uuid) RETURNS tsvector
722     AS $$DECLARE
723 ref_graphid uuid;
724 ref_graph_tags text;
725 ref_title text;
726 v_ts_search_all tsvector;
727 BEGIN
728    SELECT graphid,COALESCE(array_to_string(graph_tags, ' '), ' '),title into ref_graphid,ref_graph_tags,ref_title
729                FROM prism.saved_graphs
730               WHERE graphid =in_graphid;
731     IF NOT FOUND THEN
732         RETURN NULL;
733     END IF;
734    
735     ref_title := coalesce(replace(ref_title, '.', ' '), ' ');
736     ref_graph_tags := regexp_replace(ref_graph_tags, E'[_\`/.\\134]', ' ', 'g');
737    
738     v_ts_search_all=to_tsvector(ref_title || ' ' ||ref_graph_tags);
739    
740     RETURN v_ts_search_all;
741 END$$
742     LANGUAGE plpgsql STRICT;
743
744
745 ALTER FUNCTION prism.saved_graphs_tsvector(in_graphid uuid) OWNER TO reconnoiter;
746
747 --
748 -- Name: trig_before_tsvector_saved_graphs(); Type: FUNCTION; Schema: prism; Owner: postgres
749 --
750
751 CREATE FUNCTION trig_before_tsvector_saved_graphs() RETURNS trigger
752     AS $$
753 DECLARE
754  BEGIN
755    NEW.ts_search_all:= to_tsvector(NEW.title);
756      RETURN NEW;
757  END
758 $$
759     LANGUAGE plpgsql;
760
761
762 ALTER FUNCTION prism.trig_before_tsvector_saved_graphs() OWNER TO postgres;
763
764 --
765 -- Name: trig_update_tsvector_saved_graphs(); Type: FUNCTION; Schema: prism; Owner: reconnoiter
766 --
767
768 CREATE FUNCTION trig_update_tsvector_saved_graphs() RETURNS trigger
769     AS $$
770 DECLARE
771  BEGIN
772    IF (NEW.graph_tags <> OLD.graph_tags OR NEW.title <> OLD.title) THEN
773            UPDATE prism.saved_graphs SET ts_search_all=prism.saved_graphs_tsvector(NEW.graphid) where graphid=NEW.graphid;
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: saved_graphs_dep_pkey; Type: CONSTRAINT; Schema: prism; Owner: reconnoiter; Tablespace:
2170 --
2171
2172 ALTER TABLE ONLY saved_graphs_dep
2173     ADD CONSTRAINT saved_graphs_dep_pkey PRIMARY KEY (graphid, sid, metric_name);
2174
2175
2176 --
2177 -- Name: saved_graphs_pkey; Type: CONSTRAINT; Schema: prism; Owner: reconnoiter; Tablespace:
2178 --
2179
2180 ALTER TABLE ONLY saved_graphs
2181     ADD CONSTRAINT saved_graphs_pkey PRIMARY KEY (graphid);
2182
2183
2184 --
2185 -- Name: saved_worksheets_pkey; Type: CONSTRAINT; Schema: prism; Owner: reconnoiter; Tablespace:
2186 --
2187
2188 ALTER TABLE ONLY saved_worksheets
2189     ADD CONSTRAINT saved_worksheets_pkey PRIMARY KEY (sheetid);
2190
2191
2192 SET search_path = public, pg_catalog;
2193
2194 --
2195 -- Name: pga_diagrams_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
2196 --
2197
2198 ALTER TABLE ONLY pga_diagrams
2199     ADD CONSTRAINT pga_diagrams_pkey PRIMARY KEY (diagramname);
2200
2201
2202 --
2203 -- Name: pga_forms_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
2204 --
2205
2206 ALTER TABLE ONLY pga_forms
2207     ADD CONSTRAINT pga_forms_pkey PRIMARY KEY (formname);
2208
2209
2210 --
2211 -- Name: pga_graphs_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
2212 --
2213
2214 ALTER TABLE ONLY pga_graphs
2215     ADD CONSTRAINT pga_graphs_pkey PRIMARY KEY (graphname);
2216
2217
2218 --
2219 -- Name: pga_images_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
2220 --
2221
2222 ALTER TABLE ONLY pga_images
2223     ADD CONSTRAINT pga_images_pkey PRIMARY KEY (imagename);
2224
2225
2226 --
2227 -- Name: pga_layout_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
2228 --
2229
2230 ALTER TABLE ONLY pga_layout
2231     ADD CONSTRAINT pga_layout_pkey PRIMARY KEY (tablename);
2232
2233
2234 --
2235 -- Name: pga_queries_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
2236 --
2237
2238 ALTER TABLE ONLY pga_queries
2239     ADD CONSTRAINT pga_queries_pkey PRIMARY KEY (queryname);
2240
2241
2242 --
2243 -- Name: pga_reports_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
2244 --
2245
2246 ALTER TABLE ONLY pga_reports
2247     ADD CONSTRAINT pga_reports_pkey PRIMARY KEY (reportname);
2248
2249
2250 --
2251 -- Name: pga_scripts_pkey; Type: CONSTRAINT; Schema: public; Owner: reconnoiter; Tablespace:
2252 --
2253
2254 ALTER TABLE ONLY pga_scripts
2255     ADD CONSTRAINT pga_scripts_pkey PRIMARY KEY (scriptname);
2256
2257
2258 SET search_path = stratcon, pg_catalog;
2259
2260 --
2261 -- Name: current_metric_text_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2262 --
2263
2264 ALTER TABLE ONLY current_metric_text
2265     ADD CONSTRAINT current_metric_text_pkey PRIMARY KEY (sid, name);
2266
2267
2268 --
2269 -- Name: current_node_config_changelog_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2270 --
2271
2272 ALTER TABLE ONLY current_node_config_changelog
2273     ADD CONSTRAINT current_node_config_changelog_pkey PRIMARY KEY (remote_address, node_type, whence);
2274
2275
2276 --
2277 -- Name: current_node_config_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2278 --
2279
2280 ALTER TABLE ONLY current_node_config
2281     ADD CONSTRAINT current_node_config_pkey PRIMARY KEY (remote_address, node_type);
2282
2283
2284 --
2285 -- Name: loading_dock_check_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2286 --
2287
2288 ALTER TABLE ONLY loading_dock_check_s
2289     ADD CONSTRAINT loading_dock_check_s_pkey PRIMARY KEY (sid, id, whence);
2290
2291
2292 --
2293 -- Name: loading_dock_metric_numeric_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2294 --
2295
2296 ALTER TABLE ONLY loading_dock_metric_numeric_s
2297     ADD CONSTRAINT loading_dock_metric_numeric_s_pkey PRIMARY KEY (whence, sid, name);
2298
2299 ALTER TABLE loading_dock_metric_numeric_s CLUSTER ON loading_dock_metric_numeric_s_pkey;
2300
2301
2302 --
2303 -- Name: loading_dock_metric_text_s_change_log_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2304 --
2305
2306 ALTER TABLE ONLY loading_dock_metric_text_s_change_log
2307     ADD CONSTRAINT loading_dock_metric_text_s_change_log_pkey PRIMARY KEY (whence, sid, name);
2308
2309
2310 --
2311 -- Name: loading_dock_metric_text_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2312 --
2313
2314 ALTER TABLE ONLY loading_dock_metric_text_s
2315     ADD CONSTRAINT loading_dock_metric_text_s_pkey PRIMARY KEY (whence, sid, name);
2316
2317
2318 --
2319 -- Name: loading_dock_status_s_change_log_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2320 --
2321
2322 ALTER TABLE ONLY loading_dock_status_s_change_log
2323     ADD CONSTRAINT loading_dock_status_s_change_log_pkey PRIMARY KEY (sid, whence);
2324
2325
2326 --
2327 -- Name: loading_dock_status_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2328 --
2329
2330 ALTER TABLE ONLY loading_dock_status_s
2331     ADD CONSTRAINT loading_dock_status_s_pkey PRIMARY KEY (sid, whence);
2332
2333
2334 --
2335 -- Name: log_whence_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2336 --
2337
2338 ALTER TABLE ONLY log_whence_s
2339     ADD CONSTRAINT log_whence_s_pkey PRIMARY KEY (whence, "interval");
2340
2341
2342 --
2343 -- Name: map_uuid_to_sid_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2344 --
2345
2346 ALTER TABLE ONLY map_uuid_to_sid
2347     ADD CONSTRAINT map_uuid_to_sid_pkey PRIMARY KEY (id, sid);
2348
2349
2350 --
2351 -- Name: metric_name_summary_pk; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2352 --
2353
2354 ALTER TABLE ONLY metric_name_summary
2355     ADD CONSTRAINT metric_name_summary_pk UNIQUE (sid, metric_name, metric_type);
2356
2357
2358 --
2359 -- Name: metric_tags_pk; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2360 --
2361
2362 ALTER TABLE ONLY metric_tags
2363     ADD CONSTRAINT metric_tags_pk UNIQUE (sid, metric_name, metric_type);
2364
2365
2366 --
2367 -- Name: mv_loading_dock_check_s_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2368 --
2369
2370 ALTER TABLE ONLY mv_loading_dock_check_s
2371     ADD CONSTRAINT mv_loading_dock_check_s_pkey PRIMARY KEY (sid);
2372
2373
2374 --
2375 -- Name: rollup_matrix_numeric_12hours_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2376 --
2377
2378 ALTER TABLE ONLY rollup_matrix_numeric_12hours
2379     ADD CONSTRAINT rollup_matrix_numeric_12hours_pkey PRIMARY KEY (rollup_time, sid, name);
2380
2381 ALTER TABLE rollup_matrix_numeric_12hours CLUSTER ON rollup_matrix_numeric_12hours_pkey;
2382
2383
2384 --
2385 -- Name: rollup_matrix_numeric_20m_new_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2386 --
2387
2388 ALTER TABLE ONLY rollup_matrix_numeric_20m
2389     ADD CONSTRAINT rollup_matrix_numeric_20m_new_pkey PRIMARY KEY (rollup_time, sid, name);
2390
2391 ALTER TABLE rollup_matrix_numeric_20m CLUSTER ON rollup_matrix_numeric_20m_new_pkey;
2392
2393
2394 --
2395 -- Name: rollup_matrix_numeric_5m_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2396 --
2397
2398 ALTER TABLE ONLY rollup_matrix_numeric_5m
2399     ADD CONSTRAINT rollup_matrix_numeric_5m_pkey PRIMARY KEY (rollup_time, sid, name);
2400
2401 ALTER TABLE rollup_matrix_numeric_5m CLUSTER ON rollup_matrix_numeric_5m_pkey;
2402
2403
2404 --
2405 -- Name: rollup_matrix_numeric_60m_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2406 --
2407
2408 ALTER TABLE ONLY rollup_matrix_numeric_60m
2409     ADD CONSTRAINT rollup_matrix_numeric_60m_pkey PRIMARY KEY (rollup_time, sid, name);
2410
2411 ALTER TABLE rollup_matrix_numeric_60m CLUSTER ON rollup_matrix_numeric_60m_pkey;
2412
2413
2414 --
2415 -- Name: rollup_matrix_numeric_6hours_pkey; Type: CONSTRAINT; Schema: stratcon; Owner: reconnoiter; Tablespace:
2416 --
2417
2418 ALTER TABLE ONLY rollup_matrix_numeric_6hours
2419     ADD CONSTRAINT rollup_matrix_numeric_6hours_pkey PRIMARY KEY (rollup_time, sid, name);
2420
2421 ALTER TABLE rollup_matrix_numeric_6hours CLUSTER ON rollup_matrix_numeric_6hours_pkey;
2422
2423
2424 SET search_path = prism, pg_catalog;
2425
2426 --
2427 -- Name: idx_saved_graphs_ts_search_all; Type: INDEX; Schema: prism; Owner: reconnoiter; Tablespace:
2428 --
2429
2430 CREATE INDEX idx_saved_graphs_ts_search_all ON saved_graphs USING btree (ts_search_all);
2431
2432
2433 SET search_path = stratcon, pg_catalog;
2434
2435 --
2436 -- Name: idx_metric_name_summary_ts_search_all; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace:
2437 --
2438
2439 CREATE INDEX idx_metric_name_summary_ts_search_all ON metric_name_summary USING btree (ts_search_all);
2440
2441
2442 --
2443 -- Name: idx_mv_loading_dock_check_s_module; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace:
2444 --
2445
2446 CREATE INDEX idx_mv_loading_dock_check_s_module ON mv_loading_dock_check_s USING btree (module);
2447
2448
2449 --
2450 -- Name: idx_mv_loading_dock_check_s_name; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace:
2451 --
2452
2453 CREATE INDEX idx_mv_loading_dock_check_s_name ON mv_loading_dock_check_s USING btree (name);
2454
2455
2456 --
2457 -- Name: idx_mv_loading_dock_check_s_target; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace:
2458 --
2459
2460 CREATE INDEX idx_mv_loading_dock_check_s_target ON mv_loading_dock_check_s USING btree (target);
2461
2462
2463 --
2464 -- Name: idx_rollup_matrix_numeric_20m_rollup_time; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace:
2465 --
2466
2467 CREATE INDEX idx_rollup_matrix_numeric_20m_rollup_time ON rollup_matrix_numeric_20m USING btree (date_hour(rollup_time));
2468
2469
2470 --
2471 -- Name: unq_mv_loading_dock_check_s_id; Type: INDEX; Schema: stratcon; Owner: reconnoiter; Tablespace:
2472 --
2473
2474 CREATE UNIQUE INDEX unq_mv_loading_dock_check_s_id ON mv_loading_dock_check_s USING btree (id);
2475
2476
2477 SET search_path = prism, pg_catalog;
2478
2479 --
2480 -- Name: check_name_saved_graphs; Type: TRIGGER; Schema: prism; Owner: reconnoiter
2481 --
2482
2483 CREATE TRIGGER check_name_saved_graphs
2484     BEFORE INSERT OR UPDATE ON saved_graphs
2485     FOR EACH ROW
2486     EXECUTE PROCEDURE check_name_saved_graphs();
2487
2488
2489 --
2490 -- Name: trig_before_tsvector_saved_graphs; Type: TRIGGER; Schema: prism; Owner: reconnoiter
2491 --
2492
2493 CREATE TRIGGER trig_before_tsvector_saved_graphs
2494     BEFORE INSERT ON saved_graphs
2495     FOR EACH ROW
2496     EXECUTE PROCEDURE trig_before_tsvector_saved_graphs();
2497
2498
2499 --
2500 -- Name: trig_update_tsvector_saved_graphs; Type: TRIGGER; Schema: prism; Owner: reconnoiter
2501 --
2502
2503 CREATE TRIGGER trig_update_tsvector_saved_graphs
2504     AFTER UPDATE ON saved_graphs
2505     FOR EACH ROW
2506     EXECUTE PROCEDURE trig_update_tsvector_saved_graphs();
2507
2508
2509 SET search_path = stratcon, pg_catalog;
2510
2511 --
2512 -- Name: loading_dock_metric_numeric_s_whence_log; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter
2513 --
2514
2515 CREATE TRIGGER loading_dock_metric_numeric_s_whence_log
2516     AFTER INSERT ON loading_dock_metric_numeric_s
2517     FOR EACH ROW
2518     EXECUTE PROCEDURE loading_dock_metric_numeric_s_whence_log();
2519
2520
2521 --
2522 -- Name: loading_dock_metric_text_s_change_log; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter
2523 --
2524
2525 CREATE TRIGGER loading_dock_metric_text_s_change_log
2526     AFTER INSERT ON loading_dock_metric_text_s
2527     FOR EACH ROW
2528     EXECUTE PROCEDURE loading_dock_metric_text_s_change_log();
2529
2530
2531 --
2532 -- Name: loading_dock_status_s_change_log; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter
2533 --
2534
2535 CREATE TRIGGER loading_dock_status_s_change_log
2536     AFTER INSERT ON loading_dock_status_s
2537     FOR EACH ROW
2538     EXECUTE PROCEDURE loading_dock_status_s_change_log();
2539
2540
2541 --
2542 -- Name: mv_loading_dock_check_s; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter
2543 --
2544
2545 CREATE TRIGGER mv_loading_dock_check_s
2546     AFTER INSERT ON loading_dock_check_s
2547     FOR EACH ROW
2548     EXECUTE PROCEDURE mv_loading_dock_check_s();
2549
2550
2551 --
2552 -- Name: trig_update_tsvector_from_metric_summary; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter
2553 --
2554
2555 CREATE TRIGGER trig_update_tsvector_from_metric_summary
2556     AFTER INSERT OR UPDATE ON metric_name_summary
2557     FOR EACH ROW
2558     EXECUTE PROCEDURE trig_update_tsvector_from_metric_summary();
2559
2560
2561 --
2562 -- Name: trig_update_tsvector_from_metric_tags; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter
2563 --
2564
2565 CREATE TRIGGER trig_update_tsvector_from_metric_tags
2566     AFTER INSERT OR UPDATE ON metric_tags
2567     FOR EACH ROW
2568     EXECUTE PROCEDURE trig_update_tsvector_from_metric_tags();
2569
2570
2571 --
2572 -- Name: trig_update_tsvector_from_mv_dock; Type: TRIGGER; Schema: stratcon; Owner: reconnoiter
2573 --
2574
2575 CREATE TRIGGER trig_update_tsvector_from_mv_dock
2576     AFTER INSERT OR UPDATE ON mv_loading_dock_check_s
2577     FOR EACH ROW
2578     EXECUTE PROCEDURE trig_update_tsvector_from_mv_dock();
2579
2580
2581 SET search_path = prism, pg_catalog;
2582
2583 --
2584 -- Name: graphid_fk; Type: FK CONSTRAINT; Schema: prism; Owner: reconnoiter
2585 --
2586
2587 ALTER TABLE ONLY saved_graphs_dep
2588     ADD CONSTRAINT graphid_fk FOREIGN KEY (graphid) REFERENCES saved_graphs(graphid);
2589
2590
2591 --
2592 -- Name: saved_graphs_dep_sid_fkey; Type: FK CONSTRAINT; Schema: prism; Owner: reconnoiter
2593 --
2594
2595 ALTER TABLE ONLY saved_graphs_dep
2596     ADD CONSTRAINT saved_graphs_dep_sid_fkey FOREIGN KEY (sid, metric_name, metric_type) REFERENCES stratcon.metric_name_summary(sid, metric_name, metric_type);
2597
2598
2599 --
2600 -- Name: saved_worksheets_dep_graphid_fkey; Type: FK CONSTRAINT; Schema: prism; Owner: reconnoiter
2601 --
2602
2603 ALTER TABLE ONLY saved_worksheets_dep
2604     ADD CONSTRAINT saved_worksheets_dep_graphid_fkey FOREIGN KEY (graphid) REFERENCES saved_graphs(graphid);
2605
2606
2607 --
2608 -- Name: saved_worksheets_dep_sheetid_fkey; Type: FK CONSTRAINT; Schema: prism; Owner: reconnoiter
2609 --
2610
2611 ALTER TABLE ONLY saved_worksheets_dep
2612     ADD CONSTRAINT saved_worksheets_dep_sheetid_fkey FOREIGN KEY (sheetid) REFERENCES saved_worksheets(sheetid);
2613
2614
2615 --
2616 -- Name: public; Type: ACL; Schema: -; Owner: postgres
2617 --
2618
2619 REVOKE ALL ON SCHEMA public FROM PUBLIC;
2620 REVOKE ALL ON SCHEMA public FROM postgres;
2621 GRANT ALL ON SCHEMA public TO postgres;
2622 GRANT ALL ON SCHEMA public TO PUBLIC;
2623
2624
2625 --
2626 -- Name: stratcon; Type: ACL; Schema: -; Owner: stratcon
2627 --
2628
2629 REVOKE ALL ON SCHEMA stratcon FROM PUBLIC;
2630 REVOKE ALL ON SCHEMA stratcon FROM stratcon;
2631 GRANT ALL ON SCHEMA stratcon TO stratcon;
2632 GRANT USAGE ON SCHEMA stratcon TO prism;
2633
2634
2635 --
2636 -- Name: saved_graphs; Type: ACL; Schema: prism; Owner: reconnoiter
2637 --
2638
2639 REVOKE ALL ON TABLE saved_graphs FROM PUBLIC;
2640 REVOKE ALL ON TABLE saved_graphs FROM reconnoiter;
2641 GRANT ALL ON TABLE saved_graphs TO reconnoiter;
2642 GRANT ALL ON TABLE saved_graphs TO prism;
2643
2644
2645 --
2646 -- Name: saved_graphs_dep; Type: ACL; Schema: prism; Owner: reconnoiter
2647 --
2648
2649 REVOKE ALL ON TABLE saved_graphs_dep FROM PUBLIC;
2650 REVOKE ALL ON TABLE saved_graphs_dep FROM reconnoiter;
2651 GRANT ALL ON TABLE saved_graphs_dep TO reconnoiter;
2652 GRANT ALL ON TABLE saved_graphs_dep TO prism;
2653
2654
2655 SET search_path = stratcon, pg_catalog;
2656
2657 --
2658 -- Name: current_metric_text; Type: ACL; Schema: stratcon; Owner: reconnoiter
2659 --
2660
2661 REVOKE ALL ON TABLE current_metric_text FROM PUBLIC;
2662 REVOKE ALL ON TABLE current_metric_text FROM reconnoiter;
2663 GRANT ALL ON TABLE current_metric_text TO reconnoiter;
2664 GRANT SELECT ON TABLE current_metric_text TO prism;
2665 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE current_metric_text TO stratcon;
2666
2667
2668 --
2669 -- Name: current_node_config; Type: ACL; Schema: stratcon; Owner: reconnoiter
2670 --
2671
2672 REVOKE ALL ON TABLE current_node_config FROM PUBLIC;
2673 REVOKE ALL ON TABLE current_node_config FROM reconnoiter;
2674 GRANT ALL ON TABLE current_node_config TO reconnoiter;
2675 GRANT SELECT ON TABLE current_node_config TO prism;
2676 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE current_node_config TO stratcon;
2677
2678
2679 --
2680 -- Name: current_node_config_changelog; Type: ACL; Schema: stratcon; Owner: reconnoiter
2681 --
2682
2683 REVOKE ALL ON TABLE current_node_config_changelog FROM PUBLIC;
2684 REVOKE ALL ON TABLE current_node_config_changelog FROM reconnoiter;
2685 GRANT ALL ON TABLE current_node_config_changelog TO reconnoiter;
2686 GRANT SELECT ON TABLE current_node_config_changelog TO prism;
2687 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE current_node_config_changelog TO stratcon;
2688
2689
2690 --
2691 -- Name: loading_dock_check_s; Type: ACL; Schema: stratcon; Owner: reconnoiter
2692 --
2693
2694 REVOKE ALL ON TABLE loading_dock_check_s FROM PUBLIC;
2695 REVOKE ALL ON TABLE loading_dock_check_s FROM reconnoiter;
2696 GRANT ALL ON TABLE loading_dock_check_s TO reconnoiter;
2697 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE loading_dock_check_s TO stratcon;
2698 GRANT SELECT ON TABLE loading_dock_check_s TO prism;
2699
2700
2701 --
2702 -- Name: loading_dock_metric_numeric_s; Type: ACL; Schema: stratcon; Owner: reconnoiter
2703 --
2704
2705 REVOKE ALL ON TABLE loading_dock_metric_numeric_s FROM PUBLIC;
2706 REVOKE ALL ON TABLE loading_dock_metric_numeric_s FROM reconnoiter;
2707 GRANT ALL ON TABLE loading_dock_metric_numeric_s TO reconnoiter;
2708 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE loading_dock_metric_numeric_s TO stratcon;
2709 GRANT SELECT ON TABLE loading_dock_metric_numeric_s TO prism;
2710
2711
2712 --
2713 -- Name: loading_dock_metric_text_s; Type: ACL; Schema: stratcon; Owner: reconnoiter
2714 --
2715
2716 REVOKE ALL ON TABLE loading_dock_metric_text_s FROM PUBLIC;
2717 REVOKE ALL ON TABLE loading_dock_metric_text_s FROM reconnoiter;
2718 GRANT ALL ON TABLE loading_dock_metric_text_s TO reconnoiter;
2719 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE loading_dock_metric_text_s TO stratcon;
2720 GRANT SELECT ON TABLE loading_dock_metric_text_s TO prism;
2721
2722
2723 --
2724 -- Name: loading_dock_metric_text_s_change_log; Type: ACL; Schema: stratcon; Owner: reconnoiter
2725 --
2726
2727 REVOKE ALL ON TABLE loading_dock_metric_text_s_change_log FROM PUBLIC;
2728 REVOKE ALL ON TABLE loading_dock_metric_text_s_change_log FROM reconnoiter;
2729 GRANT ALL ON TABLE loading_dock_metric_text_s_change_log TO reconnoiter;
2730 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE loading_dock_metric_text_s_change_log TO stratcon;
2731 GRANT SELECT ON TABLE loading_dock_metric_text_s_change_log TO prism;
2732
2733
2734 --
2735 -- Name: loading_dock_status_s; Type: ACL; Schema: stratcon; Owner: reconnoiter
2736 --
2737
2738 REVOKE ALL ON TABLE loading_dock_status_s FROM PUBLIC;
2739 REVOKE ALL ON TABLE loading_dock_status_s FROM reconnoiter;
2740 GRANT ALL ON TABLE loading_dock_status_s TO reconnoiter;
2741 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE loading_dock_status_s TO stratcon;
2742 GRANT SELECT ON TABLE loading_dock_status_s TO prism;
2743
2744
2745 --
2746 -- Name: loading_dock_status_s_change_log; Type: ACL; Schema: stratcon; Owner: reconnoiter
2747 --
2748
2749 REVOKE ALL ON TABLE loading_dock_status_s_change_log FROM PUBLIC;
2750 REVOKE ALL ON TABLE loading_dock_status_s_change_log FROM reconnoiter;
2751 GRANT ALL ON TABLE loading_dock_status_s_change_log TO reconnoiter;
2752 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE loading_dock_status_s_change_log TO stratcon;
2753 GRANT SELECT ON TABLE loading_dock_status_s_change_log TO prism;
2754
2755
2756 --
2757 -- Name: log_whence_s; Type: ACL; Schema: stratcon; Owner: reconnoiter
2758 --
2759
2760 REVOKE ALL ON TABLE log_whence_s FROM PUBLIC;
2761 REVOKE ALL ON TABLE log_whence_s FROM reconnoiter;
2762 GRANT ALL ON TABLE log_whence_s TO reconnoiter;
2763 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE log_whence_s TO stratcon;
2764 GRANT SELECT ON TABLE log_whence_s TO prism;
2765
2766
2767 --
2768 -- Name: map_uuid_to_sid; Type: ACL; Schema: stratcon; Owner: reconnoiter
2769 --
2770
2771 REVOKE ALL ON TABLE map_uuid_to_sid FROM PUBLIC;
2772 REVOKE ALL ON TABLE map_uuid_to_sid FROM reconnoiter;
2773 GRANT ALL ON TABLE map_uuid_to_sid TO reconnoiter;
2774 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE map_uuid_to_sid TO stratcon;
2775 GRANT SELECT ON TABLE map_uuid_to_sid TO prism;
2776
2777
2778 --
2779 -- Name: metric_name_summary; Type: ACL; Schema: stratcon; Owner: reconnoiter
2780 --
2781
2782 REVOKE ALL ON TABLE metric_name_summary FROM PUBLIC;
2783 REVOKE ALL ON TABLE metric_name_summary FROM reconnoiter;
2784 GRANT ALL ON TABLE metric_name_summary TO reconnoiter;
2785 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE metric_name_summary TO stratcon;
2786 GRANT SELECT,UPDATE ON TABLE metric_name_summary TO prism;
2787
2788
2789 --
2790 -- Name: metric_tags; Type: ACL; Schema: stratcon; Owner: reconnoiter
2791 --
2792
2793 REVOKE ALL ON TABLE metric_tags FROM PUBLIC;
2794 REVOKE ALL ON TABLE metric_tags FROM reconnoiter;
2795 GRANT ALL ON TABLE metric_tags TO reconnoiter;
2796 GRANT ALL ON TABLE metric_tags TO prism;
2797 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE metric_tags TO stratcon;
2798
2799
2800 --
2801 -- Name: mv_loading_dock_check_s; Type: ACL; Schema: stratcon; Owner: reconnoiter
2802 --
2803
2804 REVOKE ALL ON TABLE mv_loading_dock_check_s FROM PUBLIC;
2805 REVOKE ALL ON TABLE mv_loading_dock_check_s FROM reconnoiter;
2806 GRANT ALL ON TABLE mv_loading_dock_check_s TO reconnoiter;
2807 GRANT SELECT ON TABLE mv_loading_dock_check_s TO prism;
2808 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE mv_loading_dock_check_s TO stratcon;
2809
2810
2811 --
2812 -- Name: rollup_matrix_numeric_12hours; Type: ACL; Schema: stratcon; Owner: reconnoiter
2813 --
2814
2815 REVOKE ALL ON TABLE rollup_matrix_numeric_12hours FROM PUBLIC;
2816 REVOKE ALL ON TABLE rollup_matrix_numeric_12hours FROM reconnoiter;
2817 GRANT ALL ON TABLE rollup_matrix_numeric_12hours TO reconnoiter;
2818 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE rollup_matrix_numeric_12hours TO stratcon;
2819 GRANT SELECT ON TABLE rollup_matrix_numeric_12hours TO prism;
2820
2821
2822 --
2823 -- Name: rollup_matrix_numeric_20m; Type: ACL; Schema: stratcon; Owner: reconnoiter
2824 --
2825
2826 REVOKE ALL ON TABLE rollup_matrix_numeric_20m FROM PUBLIC;
2827 REVOKE ALL ON TABLE rollup_matrix_numeric_20m FROM reconnoiter;
2828 GRANT ALL ON TABLE rollup_matrix_numeric_20m TO reconnoiter;
2829 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE rollup_matrix_numeric_20m TO stratcon;
2830 GRANT SELECT ON TABLE rollup_matrix_numeric_20m TO prism;
2831
2832
2833 --
2834 -- Name: rollup_matrix_numeric_5m; Type: ACL; Schema: stratcon; Owner: reconnoiter
2835 --
2836
2837 REVOKE ALL ON TABLE rollup_matrix_numeric_5m FROM PUBLIC;
2838 REVOKE ALL ON TABLE rollup_matrix_numeric_5m FROM reconnoiter;
2839 GRANT ALL ON TABLE rollup_matrix_numeric_5m TO reconnoiter;
2840 GRANT SELECT,INSERT,DELETE ON TABLE rollup_matrix_numeric_5m TO stratcon;
2841 GRANT SELECT ON TABLE rollup_matrix_numeric_5m TO prism;
2842
2843
2844 --
2845 -- Name: rollup_matrix_numeric_60m; Type: ACL; Schema: stratcon; Owner: reconnoiter
2846 --
2847
2848 REVOKE ALL ON TABLE rollup_matrix_numeric_60m FROM PUBLIC;
2849 REVOKE ALL ON TABLE rollup_matrix_numeric_60m FROM reconnoiter;
2850 GRANT ALL ON TABLE rollup_matrix_numeric_60m TO reconnoiter;
2851 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE rollup_matrix_numeric_60m TO stratcon;
2852 GRANT SELECT ON TABLE rollup_matrix_numeric_60m TO prism;
2853
2854
2855 --
2856 -- Name: rollup_matrix_numeric_6hours; Type: ACL; Schema: stratcon; Owner: reconnoiter
2857 --
2858
2859 REVOKE ALL ON TABLE rollup_matrix_numeric_6hours FROM PUBLIC;
2860 REVOKE ALL ON TABLE rollup_matrix_numeric_6hours FROM reconnoiter;
2861 GRANT ALL ON TABLE rollup_matrix_numeric_6hours TO reconnoiter;
2862 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE rollup_matrix_numeric_6hours TO stratcon;
2863 GRANT SELECT ON TABLE rollup_matrix_numeric_6hours TO prism;
2864
2865
2866 --
2867 -- Name: rollup_runner; Type: ACL; Schema: stratcon; Owner: reconnoiter
2868 --
2869
2870 REVOKE ALL ON TABLE rollup_runner FROM PUBLIC;
2871 REVOKE ALL ON TABLE rollup_runner FROM reconnoiter;
2872 GRANT ALL ON TABLE rollup_runner TO reconnoiter;
2873 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE rollup_runner TO stratcon;
2874 GRANT SELECT ON TABLE rollup_runner TO prism;
2875
2876
2877 --
2878 -- Name: seq_sid; Type: ACL; Schema: stratcon; Owner: reconnoiter
2879 --
2880
2881 REVOKE ALL ON SEQUENCE seq_sid FROM PUBLIC;
2882 REVOKE ALL ON SEQUENCE seq_sid FROM reconnoiter;
2883 GRANT ALL ON SEQUENCE seq_sid TO reconnoiter;
2884 GRANT ALL ON SEQUENCE seq_sid TO stratcon;
2885
2886
2887 --
2888 -- PostgreSQL database dump complete
2889 --
2890
Note: See TracBrowser for help on using the browser.