root/sql/reconnoiter_ddl_dump.sql

Revision c256764571a55858b9a8db0f8905c297ec481926, 86.1 kB (checked in by Denish Patel <denish@omniti.com>, 9 years ago)

updated remove_metric function

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