root/sql/reconnoiter_ddl_dump.sql

Revision 17a8e8e4556d3b18451fd9531ef964be402a98d0, 103.1 kB (checked in by Theo Schlossnagle <jesus@omniti.com>, 5 years ago)

make the counter_dev stuff much more intelligent and accommodating of rolling counters if data is collected 'fast enough'. Fixes #110

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