root/sql/reconnoiter_ddl_dump.sql

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

updated pgdump ddl

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