root/sql/reconnoiter_ddl_dump.sql

Revision 06519e0d896bfca30746212670c342a1b84a74a5, 108.3 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 5 years ago)

Patch from Dan. fixes #153

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