root/sql/schema.sql

Revision b927b3bbf9eeab57dc26e56d2eddfea8cc199a49, 32.6 kB (checked in by Denish Patel <denish@omniti.com>, 10 years ago)

updated sequence ddl to start with 1.

  • Property mode set to 100644
Line 
1 BEGIN;
2
3 -- Tables
4
5 CREATE TABLE stratcon.loading_dock_check_s (
6     sid integer NOT NULL,
7     remote_address inet,
8     whence timestamptz NOT NULL,
9     id uuid NOT NULL,
10     target text NOT NULL,
11     module text NOT NULL,
12     name text NOT NULL,
13     PRIMARY KEY(sid,id,whence)
14 );
15
16 CREATE TABLE stratcon.loading_dock_status_s (
17     sid integer NOT NULL,
18     whence timestamptz NOT NULL,
19     state character(1) NOT NULL,
20     availability character(1) NOT NULL,
21     duration integer NOT NULL,
22     status text,
23     PRIMARY KEY(sid,whence)
24 );
25
26 CREATE TABLE stratcon.loading_dock_status_s_change_log (
27     sid integer NOT NULL,
28     whence timestamptz NOT NULL,
29     state character(1) NOT NULL,
30     availability character(1) NOT NULL,
31     duration integer NOT NULL,
32     status text,
33     PRIMARY KEY(sid,whence)
34 );
35
36
37 CREATE TABLE stratcon.loading_dock_metric_text_s (
38     sid integer NOT NULL,
39     whence timestamptz NOT NULL,
40     name text NOT NULL,
41     value text,
42     PRIMARY KEY(whence,sid,name)
43 );
44
45 CREATE TABLE stratcon.loading_dock_metric_text_s_change_log (
46     sid integer NOT NULL,
47     whence timestamptz NOT NULL,
48     name text NOT NULL,
49     value text,
50     PRIMARY KEY(whence,sid,name)
51 );
52
53 CREATE TABLE stratcon.loading_dock_metric_numeric_s (
54     sid integer NOT NULL,
55     whence timestamptz NOT NULL,
56     name text NOT NULL,
57     value numeric,
58     PRIMARY KEY(whence,sid,name)
59 );
60
61 CREATE TABLE stratcon.rollup_matrix_numeric_5m (
62     sid integer NOT NULL,
63     name text NOT NULL,
64     rollup_time timestamptz NOT NULL,
65     count_rows integer,
66     avg_value numeric,
67     min_value numeric,
68     max_value numeric,
69     PRIMARY KEY (rollup_time,sid,name)
70 );
71
72 CREATE TABLE stratcon.rollup_matrix_numeric_20m (
73     sid integer NOT NULL,
74     name text NOT NULL,
75     rollup_time timestamptz NOT NULL,
76     count_rows integer,
77     avg_value numeric,
78     min_value numeric,
79     max_value numeric,
80     PRIMARY KEY (rollup_time,sid,name)
81 );
82
83 CREATE OR REPLACE FUNCTION stratcon.date_hour(timestamptz)
84  RETURNS timestamptz as $BODY$
85  SELECT date_trunc('hour',$1);
86  $BODY$
87  language 'sql'
88  IMMUTABLE STRICT;
89
90 CREATE INDEX idx_rollup_matrix_numeric_20m_rollup_time
91               ON stratcon.rollup_matrix_numeric_20m(date_hour(rollup_time));
92
93 CREATE TABLE stratcon.rollup_matrix_numeric_60m(
94    sid integer not null,
95    name text not null,
96    rollup_time timestamptz not null,
97    count_rows integer,
98    avg_value numeric ,
99    min_value numeric ,
100    max_value numeric ,
101    PRIMARY KEY(rollup_time,sid,name));
102    
103 CREATE TABLE stratcon.rollup_matrix_numeric_6hours(
104    sid integer not null,
105    name text not null,
106    rollup_time timestamptz not null,
107    count_rows integer,
108    avg_value numeric ,
109    min_value numeric ,
110    max_value numeric ,
111    PRIMARY KEY(rollup_time6,sid,name));   
112
113 CREATE TABLE stratcon.rollup_matrix_numeric_12hours(
114    sid integer not null,
115    name text not null,
116    rollup_time timestamptz not null,
117    count_rows integer,
118    avg_value numeric ,
119    min_value numeric ,
120    max_value numeric ,
121    PRIMARY KEY(rollup_time12,sid,name));     
122  
123 CREATE TABLE stratcon.map_uuid_to_sid (
124     id uuid NOT NULL,
125     sid integer NOT NULL,
126     PRIMARY KEY(id,sid)
127 );
128
129 CREATE TABLE stratcon.log_whence_s (
130     whence timestamptz NOT NULL,
131     interval varchar2(20,
132     PRIMARY KEY(whence,interval)
133 );
134
135 CREATE TABLE stratcon.rollup_runner (
136   rollup_table character varying(100),
137   runner character varying(22)
138 );
139
140 CREATE TABLE stratcon.metric_name_summary (
141   sid integer NOT NULL,
142   metric_name text NOT NULL,
143   metric_type character varying(22),
144   active boolean default 'true',
145   PRIMARY KEY (sid,name)
146 );
147 -- Schema Sequence
148
149 CREATE SEQUENCE stratcon.seq_sid
150     START WITH 1
151     INCREMENT BY 1
152     NO MAXVALUE
153     NO MINVALUE
154     CACHE 1;
155
156
157
158 -- GRANTS
159
160  GRANT SELECT,INSERT ON stratcon.loading_dock_status_s TO stratcon;
161  GRANT SELECT,INSERT ON stratcon.loading_dock_status_s_change_log TO stratcon;
162  GRANT SELECT,INSERT ON stratcon.loading_dock_check_s TO stratcon;
163  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric_s TO stratcon;
164  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s_change_log TO stratcon;
165  GRANT SELECT,INSERT,DELETE ON stratcon.log_whence_s TO stratcon;
166  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s TO stratcon;
167  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_60m TO stratcon;
168  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_5m TO stratcon;
169  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_20m TO stratcon;
170  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_6hours TO stratcon;
171  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_12hours TO stratcon;
172  GRANT SELECT,INSERT ON stratcon.map_uuid_to_sid TO stratcon;
173  GRANT SELECT,INSERT,UPDATE,DELETE ON stratcon.rollup_runner TO stratcon;
174  GRANT SELECT,INSERT,UPDATE,DELETE ON stratcon.metric_name_summary TO stratcon;
175  ALTER TABLE stratcon.seq_sid OWNER TO stratcon;
176  
177  
178 -- Function To generate SID from ID
179
180 CREATE OR REPLACE FUNCTION stratcon.generate_sid_from_id(v_in_id uuid)
181 RETURNS integer
182 AS $$
183 DECLARE
184    v_ex_sid integer;
185    v_new_sid integer;
186  
187 BEGIN
188
189 SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id
190   INTO v_ex_sid;
191
192  IF NOT FOUND THEN
193     SELECT nextval('stratcon.seq_sid')
194     INTO v_new_sid;
195
196     INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid);
197    
198     RETURN v_new_sid;
199  ELSE
200       RETURN v_ex_sid;
201  END IF;
202
203 END
204 $$ LANGUAGE plpgsql;
205
206 -- Trigger Function to log dock status Changes
207
208 CREATE  TRIGGER loading_dock_status_s_change_log
209     AFTER INSERT ON stratcon.loading_dock_status_s
210     FOR EACH ROW
211     EXECUTE PROCEDURE stratcon.loading_dock_status_s_change_log();
212
213
214 CREATE OR REPLACE FUNCTION stratcon.loading_dock_status_s_change_log() RETURNS trigger
215     AS $$
216 DECLARE
217     v_state CHAR(1);
218     v_avail CHAR(1);
219 BEGIN
220
221 IF TG_OP = 'INSERT' THEN
222     SELECT state,availability FROM  stratcon.loading_dock_status_s WHERE sid = NEW.sid
223         AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log
224                         WHERE  SID=NEW.sid and  WHENCE <> NEW.whence )
225     INTO v_state,v_avail;
226
227     IF v_state IS DISTINCT FROM NEW.state OR v_avail IS DISTINCT FROM NEW.availability THEN
228
229         INSERT INTO stratcon.loading_dock_status_s_change_log (sid,whence,state,availability,duration,status)
230             VALUES (NEW.sid,NEW.whence,NEW.state,NEW.availability,NEW.duration,NEW.status);
231
232     END IF;
233
234 ELSE
235         RAISE EXCEPTION 'Something wrong with stratcon.loading_dock_status_s_change_log';
236 END IF;
237
238     RETURN NULL;
239
240 END
241 $$
242     LANGUAGE plpgsql;
243
244
245 -- Trigger Function to log Metrix Text Changes
246
247 CREATE TRIGGER loading_dock_metric_text_s_change_log
248     AFTER INSERT ON stratcon.loading_dock_metric_text_s
249     FOR EACH ROW
250     EXECUTE PROCEDURE stratcon.loading_dock_metric_text_s_change_log();
251
252
253 CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger
254     AS $$
255 DECLARE
256     v_oldvalue TEXT;
257     v_sid integer;
258     v_name text;
259 BEGIN
260
261 IF TG_OP = 'INSERT' THEN
262     SELECT value FROM  stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name
263         AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log
264                         WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name )
265     INTO v_oldvalue;
266
267     IF v_oldvalue IS DISTINCT FROM NEW.value THEN
268
269         INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value)
270             VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value);
271     END IF;
272    
273     SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid  and metric_name=NEW.name
274          INTO v_sid,v_name;
275        IF NOT FOUND THEN
276            INSERT INTO  stratcon.metric_name_summary(sid,metric_name,metric_type)  VALUES(NEW.sid,NEW.name,'text');
277     END IF;
278
279 ELSE
280         RAISE EXCEPTION 'something wrong with stratcon.loading_dock_metric_text_s_change_log ';
281 END IF;
282
283     RETURN NULL;
284
285 END
286 $$
287     LANGUAGE plpgsql;
288    
289 -- Trigger on Metrix Numeric to log last inserted timestamp
290
291 CREATE OR REPLACE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log()
292 RETURNS trigger
293 AS $$
294 DECLARE
295 v_whence timestamptz;
296 v_sid integer;
297 v_name text;
298 BEGIN
299 IF TG_OP = 'INSERT' THEN
300    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval and interval='5 minutes'
301      INTO v_whence;
302    IF NOT FOUND THEN
303        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',NEW.WHENCE) + (round(extract('minute' from NEW.WHENCE)/5)*5) * '1 minute'::interval,'5 minutes');
304     END IF;
305
306    SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid  and metric_name=NEW.name
307      INTO v_sid,v_name;
308    IF NOT FOUND THEN
309        INSERT INTO  stratcon.metric_name_summary VALUES(NEW.sid,NEW.name,'numeric');
310     END IF;
311
312 END IF;
313
314     RETURN NULL;
315 END
316 $$
317     LANGUAGE plpgsql;
318
319 -- 5 minutes rollup
320
321 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m()
322 RETURNS void
323 AS $$
324 DECLARE
325  
326  rec stratcon.rollup_matrix_numeric_5m%rowtype;
327  v_sql TEXT;
328  v_min_whence TIMESTAMPTZ;
329  v_max_rollup_5 TIMESTAMPTZ;
330  v_whence TIMESTAMPTZ;
331  rows INT;
332  v_nrunning INT;
333  v_self VARCHAR(22);
334  
335 BEGIN
336
337   SELECT COUNT(1) INTO v_nrunning
338     from stratcon.rollup_runner t, pg_stat_activity a
339    where rollup_table ='rollup_matrix_numeric_5m'
340      and runner = procpid || '.' || date_part('epoch',backend_start);
341
342   IF v_nrunning > 0 THEN
343     RAISE NOTICE 'stratcon.rollup_matrix_numeric_5m already running';
344     RETURN ;
345   END IF;
346
347   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
348     FROM pg_stat_activity
349    WHERE procpid = pg_backend_pid();
350
351   IF v_self IS NULL THEN
352     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_5m cannot self-identify';
353   END IF;
354
355   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_5m''';
356
357   EXECUTE v_sql;
358
359  SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='5 minutes'
360         INTO v_min_whence;
361        
362  SELECT MAX(rollup_time) FROM  stratcon.rollup_matrix_numeric_5m
363          INTO v_max_rollup_5;       
364  
365  -- Insert Log for 20 minutes rollup
366    
367    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'
368            INTO v_whence;
369       IF NOT FOUND THEN
370        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');
371    END IF;
372    
373  IF v_min_whence <= v_max_rollup_5 THEN
374
375    DELETE FROM stratcon.rollup_matrix_numeric_5m
376                 WHERE rollup_time = v_min_whence;
377  
378  END IF;
379
380  FOR rec IN
381                 SELECT sid , name,v_min_whence as rollup_time,
382                       COUNT(1) as count_rows ,AVG(value) as avg_value,MIN(value) as min_value ,MAX(value) as max_value
383                       FROM stratcon.loading_dock_metric_numeric_s
384                       WHERE WHENCE <= v_min_whence AND WHENCE > v_min_whence -'5 minutes'::interval
385                 GROUP BY rollup_time,sid,name
386  
387        LOOP
388    
389        
390         INSERT INTO stratcon.rollup_matrix_numeric_5m
391          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
392          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
393        
394    END LOOP;
395
396   -- Delete from whence log table
397  
398   DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='5 minutes';
399  
400   UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m';
401  
402 RETURN;
403
404 EXCEPTION
405     WHEN RAISE_EXCEPTION THEN
406        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m';
407        RAISE EXCEPTION '%', SQLERRM;
408     WHEN OTHERS THEN
409       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_5m';
410       RAISE NOTICE '%', SQLERRM;
411 END
412 $$ LANGUAGE plpgsql;
413
414
415 -- 20 minutes rollup
416
417 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_20m()
418 RETURNS void
419 AS $$
420 DECLARE
421  
422  rec stratcon.rollup_matrix_numeric_20m%rowtype;
423  v_sql TEXT;
424  v_min_whence TIMESTAMPTZ;
425  v_max_rollup_20 TIMESTAMPTZ;
426  v_whence TIMESTAMPTZ;
427  rows INT;
428  v_nrunning INT;
429  v_self VARCHAR(22);
430
431 BEGIN
432
433   SELECT COUNT(1) INTO v_nrunning
434     from stratcon.rollup_runner t, pg_stat_activity a
435    where rollup_table ='rollup_matrix_numeric_20m'
436      and runner = procpid || '.' || date_part('epoch',backend_start);
437
438   IF v_nrunning > 0 THEN
439     RAISE NOTICE 'stratcon.rollup_matrix_numeric_20m already running';
440     RETURN ;
441   END IF;
442
443   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
444     FROM pg_stat_activity
445    WHERE procpid = pg_backend_pid();
446
447   IF v_self IS NULL THEN
448     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_20m cannot self-identify';
449   END IF;
450
451   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_20m''';
452
453   EXECUTE v_sql;
454
455  SELECT MIN(whence) FROM stratcon.log_whence_s WHERE interval='20 minutes'
456         INTO v_min_whence;
457        
458  SELECT MAX(rollup_time) FROM  stratcon.rollup_matrix_numeric_20m
459          INTO v_max_rollup_20;       
460  
461  -- Insert Log for Hourly rollup
462    
463    SELECT whence FROM stratcon.log_whence_s WHERE whence=date_trunc('H',v_min_whence) and interval='1 hour'
464            INTO v_whence;
465       IF NOT FOUND THEN
466        INSERT INTO  stratcon.log_whence_s VALUES(date_trunc('H',v_min_whence),'1 hour');
467    END IF;
468    
469  IF v_min_whence <= v_max_rollup_20 THEN
470
471    DELETE FROM stratcon.rollup_matrix_numeric_20m
472                 WHERE rollup_time = v_min_whence;
473  
474  END IF;
475
476  FOR rec IN
477                 SELECT sid , name,v_min_whence as rollup_time,
478                        SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value,
479                        MIN(min_value) as min_value ,MAX(max_value) as max_value
480                        FROM stratcon.rollup_matrix_numeric_5m
481                       WHERE rollup_time<= v_min_whence AND rollup_time > v_min_whence -'20 minutes'::interval
482                 GROUP BY sid,name
483  
484        LOOP
485    
486        
487         INSERT INTO stratcon.rollup_matrix_numeric_20m
488          (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
489          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
490        
491    END LOOP;
492
493   -- Delete from whence log table
494  
495   DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='20 minutes';
496  
497   UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m';
498  
499 RETURN;
500
501 EXCEPTION
502     WHEN RAISE_EXCEPTION THEN
503        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m';
504        RAISE EXCEPTION '%', SQLERRM;
505     WHEN OTHERS THEN
506       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_20m';
507       RAISE NOTICE '%', SQLERRM;
508 END
509 $$ LANGUAGE plpgsql;
510
511
512 -- 1 hour rollup
513
514
515 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m()
516 RETURNS void
517 AS $$
518 DECLARE
519   rec stratcon.rollup_matrix_numeric_60m%rowtype;
520   v_sql TEXT;
521   v_min_whence TIMESTAMPTZ;
522   v_max_rollup_60 TIMESTAMPTZ;
523   v_whence TIMESTAMPTZ;
524   v_nrunning INT;
525   v_self VARCHAR(22);
526
527 BEGIN
528
529   SELECT COUNT(1) INTO v_nrunning
530     from stratcon.rollup_runner t, pg_stat_activity a
531    where rollup_table ='rollup_matrix_numeric_60m'
532      and runner = procpid || '.' || date_part('epoch',backend_start);
533
534   IF v_nrunning > 0 THEN
535     RAISE NOTICE 'stratcon.rollup_matrix_numeric_60m already running';
536     RETURN ;
537   END IF;
538
539   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
540     FROM pg_stat_activity
541    WHERE procpid = pg_backend_pid();
542
543   IF v_self IS NULL THEN
544     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_60m cannot self-identify';
545   END IF;
546
547   v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_60m''';
548
549   EXECUTE v_sql;
550
551   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='1 hour'
552          INTO v_min_whence;
553          
554   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_60m
555          INTO v_max_rollup_60;   
556
557 -- Insert Log for 6 Hour rollup
558    
559    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'
560            INTO v_whence;
561       IF NOT FOUND THEN
562        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');
563    END IF;
564    
565    
566   IF v_min_whence <= v_max_rollup_60 THEN
567  
568   DELETE FROM stratcon.rollup_matrix_numeric_60m
569        WHERE rollup_time= v_min_whence;
570
571   END IF;
572  
573     FOR rec IN
574                 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,
575                          MIN(min_value) as min_value ,MAX(max_value) as max_value
576                          FROM stratcon.rollup_matrix_numeric_20m
577                            WHERE date_hour(rollup_time)= v_min_whence
578                    GROUP BY date_hour(rollup_time),sid,name
579         LOOP
580      
581           INSERT INTO stratcon.rollup_matrix_numeric_60m
582           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
583           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
584          
585      END LOOP;
586
587
588 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='1 hour';
589
590 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m';
591
592 RETURN;
593
594 EXCEPTION
595     WHEN RAISE_EXCEPTION THEN
596        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m';
597        RAISE EXCEPTION '%', SQLERRM;
598     WHEN OTHERS THEN
599       UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_60m';
600       RAISE NOTICE '%', SQLERRM;
601 END
602 $$ LANGUAGE plpgsql;
603
604
605 -- 6 hours
606
607 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_6hours()
608 RETURNS void
609 AS $$
610 DECLARE
611   rec stratcon.rollup_matrix_numeric_6hours%rowtype;
612   v_sql TEXT;
613   v_min_whence TIMESTAMPTZ;
614   v_max_rollup_6 TIMESTAMPTZ;
615   v_whence TIMESTAMPTZ;
616   v_nrunning INT;
617   v_self VARCHAR(22);
618    
619 BEGIN
620
621   SELECT COUNT(1) INTO v_nrunning
622     from stratcon.rollup_runner t, pg_stat_activity a
623     where rollup_table ='rollup_matrix_numeric_6hours'
624      and runner = procpid || '.' || date_part('epoch',backend_start);
625
626   IF v_nrunning > 0 THEN
627     RAISE NOTICE 'stratcon.rollup_matrix_numeric_6hours already running';
628     RETURN ;
629   END IF;
630
631   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
632     FROM pg_stat_activity
633      WHERE procpid = pg_backend_pid();
634
635   IF v_self IS NULL THEN
636     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_6hours cannot self-identify';
637    END IF;
638
639    v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_6hours''';
640
641   EXECUTE v_sql;
642
643   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='6 hours'
644          INTO v_min_whence;
645          
646   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_6hours
647          INTO v_max_rollup_6;   
648
649 -- Insert Log for 12 Hours rollup
650    
651    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'
652            INTO v_whence;
653       IF NOT FOUND THEN
654        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');
655    END IF;
656    
657    
658   IF v_min_whence <= v_max_rollup_6 THEN
659  
660   DELETE FROM stratcon.rollup_matrix_numeric_6hours
661        WHERE rollup_time= v_min_whence;
662
663   END IF;
664  
665     FOR rec IN
666                 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,
667                          MIN(min_value) as min_value ,MAX(max_value) as max_value
668                          FROM stratcon.rollup_matrix_numeric_60m
669                            WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'6 hour'::interval
670                    GROUP BY sid,name
671         LOOP
672      
673        
674           INSERT INTO stratcon.rollup_matrix_numeric_6hours
675           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
676           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
677          
678      END LOOP;
679
680
681 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='6 hours';
682
683 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';
684
685 RETURN;
686
687 EXCEPTION
688     WHEN RAISE_EXCEPTION THEN
689        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';
690        RAISE EXCEPTION '%', SQLERRM;
691     WHEN OTHERS THEN
692        UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_6hours';
693        RAISE NOTICE '%', SQLERRM;
694 END
695 $$ LANGUAGE plpgsql;
696
697
698 -- 12 hours
699
700 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_12hours()
701 RETURNS void
702 AS $$
703 DECLARE
704   rec stratcon.rollup_matrix_numeric_12hours%rowtype;
705   v_sql TEXT;
706   v_min_whence TIMESTAMPTZ;
707   v_max_rollup_12 TIMESTAMPTZ;
708   v_whence TIMESTAMPTZ;
709   v_nrunning INT;
710   v_self VARCHAR(22);
711  
712  
713 BEGIN
714
715   SELECT COUNT(1) INTO v_nrunning
716     from stratcon.rollup_runner t, pg_stat_activity a
717     where rollup_table ='rollup_matrix_numeric_12hours'
718      and runner = procpid || '.' || date_part('epoch',backend_start);
719
720   IF v_nrunning > 0 THEN
721     RAISE NOTICE 'stratcon.rollup_matrix_numeric_12hours already running';
722     RETURN ;
723   END IF;
724
725   SELECT INTO v_self procpid || '.' || date_part('epoch',backend_start)
726     FROM pg_stat_activity
727      WHERE procpid = pg_backend_pid();
728
729   IF v_self IS NULL THEN
730     RAISE EXCEPTION 'stratcon.rollup_matrix_numeric_12hours cannot self-identify';
731    END IF;
732
733    v_sql = 'update stratcon.rollup_runner set runner = ''' || v_self || ''' where rollup_table = ''rollup_matrix_numeric_12hours''';
734
735   EXECUTE v_sql;
736
737   SELECT min(whence) FROM stratcon.log_whence_s WHERE interval='12 hours'
738          INTO v_min_whence;
739          
740   SELECT max(date_trunc('H',rollup_time)) FROM  stratcon.rollup_matrix_numeric_12hours
741          INTO v_max_rollup_12;   
742
743 /*-- Insert Log for 24 Hours rollup
744    
745    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'
746            INTO v_whence;
747       IF NOT FOUND THEN
748        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');
749    END IF;
750    */
751    
752   IF v_min_whence <= v_max_rollup_12 THEN
753  
754   DELETE FROM stratcon.rollup_matrix_numeric_12hours
755        WHERE rollup_time= v_min_whence;
756
757   END IF;
758  
759     FOR rec IN
760                 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,
761                          MIN(min_value) as min_value ,MAX(max_value) as max_value
762                          FROM stratcon.rollup_matrix_numeric_6hours
763                            WHERE rollup_time<= v_min_whence and rollup_time> v_min_whence-'12 hour'::interval
764                    GROUP BY sid,name
765         LOOP
766      
767        
768           INSERT INTO stratcon.rollup_matrix_numeric_12hours
769           (sid,name,rollup_time,count_rows,avg_value,min_value,max_value) VALUES
770           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.min_value,rec.max_value);
771          
772      END LOOP;
773
774
775 DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence AND INTERVAL='12 hours';
776
777 UPDATE stratcon.rollup_runner SET RUNNER = '' WHERE ROLLUP_TABLE= 'rollup_matrix_numeric_12hours';
778
779 RETURN;
780
781 EXCEPTION
782     WHEN RAISE_EXCEPTION THEN
783       UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours';
784       RAISE EXCEPTION '%', SQLERRM;
785     WHEN OTHERS THEN
786       UPDATE stratcon.rollup_runner set runner = '' where rollup_table = 'rollup_matrix_numeric_12hours';
787       RAISE NOTICE '%', SQLERRM;
788 END
789 $$ LANGUAGE plpgsql;
790
791
792 create or replace function
793 stratcon.fetch_varset(in_check uuid,
794                        in_name text,
795                        in_start_time timestamptz,
796                        in_end_time timestamptz,
797                        in_hopeful_nperiods int)
798 returns setof stratcon.loading_dock_metric_text_s_change_log as
799 $$
800 declare
801   v_sid int;
802   v_target record;
803   v_start_adj timestamptz;
804   v_start_text text;
805   v_next_text text;
806   v_end_adj timestamptz;
807   v_change_row stratcon.loading_dock_metric_text_s_change_log%rowtype;
808 begin
809   -- Map out uuid to an sid.
810   select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check;
811   if not found then
812     return;
813   end if;
814
815   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
816
817   select 'epoch'::timestamp +
818          ((floor(extract('epoch' from in_start_time) /
819                  extract('epoch' from v_target.period)) *
820            extract('epoch' from v_target.period)) || ' seconds') ::interval
821     into v_start_adj;
822
823   select 'epoch'::timestamp +
824          ((floor(extract('epoch' from in_end_time) /
825                  extract('epoch' from v_target.period)) *
826            extract('epoch' from v_target.period)) || ' seconds') ::interval
827     into v_end_adj;
828
829   for v_change_row in
830     select sid, 'epoch'::timestamp +
831          ((floor(extract('epoch' from whence) /
832                  extract('epoch' from v_target.period)) *
833            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
834            name, value
835       from stratcon.loading_dock_metric_text_s_change_log
836      where sid = v_sid
837        and name = in_name
838        and whence <= v_start_adj
839   order by 'epoch'::timestamp +
840          ((floor(extract('epoch' from whence) /
841                  extract('epoch' from v_target.period)) *
842            extract('epoch' from v_target.period)) || ' seconds') ::interval desc
843      limit 1
844   loop
845     v_start_text := coalesce(v_change_row.value, '[unset]');
846   end loop;
847
848   for v_change_row in
849     select v_sid as sid, whence, in_name as name, value from
850 --    (select v_start_adj::timestamp + t * v_target.period::interval as whence
851 --      from generate_series(1, v_target.nperiods) t) s
852 -- left join
853     (select 'epoch'::timestamp +
854          ((floor(extract('epoch' from whence) /
855                  extract('epoch' from v_target.period)) *
856            extract('epoch' from v_target.period)) || ' seconds') ::interval as whence,
857            coalesce(value, '[unset]') as value
858       from stratcon.loading_dock_metric_text_s_change_log
859      where sid = v_sid
860        and name = in_name
861        and whence > v_start_adj
862        and whence <= v_end_adj) d
863 --    using (whence)
864   order by whence asc
865   loop
866     v_next_text := v_change_row.value;
867     if v_change_row.value is not null and
868        v_start_text != v_change_row.value then
869       v_change_row.value := coalesce(v_start_text, '[unset]') || ' -> ' || coalesce(v_change_row.value, '[unset]');
870     else
871       v_change_row.value := v_start_text;
872     end if;
873     if v_next_text is not null then
874       v_start_text := v_next_text;
875     end if;
876     return next v_change_row;
877   end loop;
878
879   return;
880 end
881 $$ language 'plpgsql';
882
883
884 create or replace function
885 stratcon.choose_window(in_start_time timestamptz,
886                        in_end_time timestamptz,
887                        in_hopeful_nperiods int,
888                        out tablename text,
889                        out period interval,
890                        out nperiods int)
891 returns setof record as
892 $$
893 declare
894   window record;
895 begin
896   -- Figure out which table we should be looking in
897   for window in
898     select atablename, aperiod, anperiods
899     from (select aperiod, iv/isec as anperiods, atablename,
900                  abs(case when iv/isec - in_hopeful_nperiods < 0
901                           then 10 * (in_hopeful_nperiods - iv/isec)
902                           else iv/isec - in_hopeful_nperiods
903                            end) as badness
904             from (select extract('epoch' from in_end_time) -
905                          extract('epoch' from in_start_time) as iv
906                  ) i,
907                  (   select 5*60 as isec, '5 minutes'::interval as aperiod,
908                             'rollup_matrix_numeric_5m' as atablename
909                   union all
910                      select 20*60 as isec, '20 minutes'::interval as aperiod,
911                             'rollup_matrix_numeric_20m' as atablename
912                   union all
913                      select 60*60 as isec, '1 hour'::interval as aperiod,
914                             'rollup_matrix_numeric_60m' as atablename
915                   union all
916                      select 6*60*60 as isec, '6 hours'::interval as aaperiod,
917                             'rollup_matrix_numeric_6hours' as atablename
918                   union all
919                      select 12*60*60 as isec, '12 hours'::interval as aperiod,
920                             'rollup_matrix_numeric_12hours' as atablename
921                  ) ivs
922          ) b
923  order by badness asc
924   limit 1
925   loop
926     tablename := window.atablename;
927     period := window.aperiod;
928     nperiods := window.anperiods;
929     return next;
930   end loop;
931   return;
932 end
933 $$ language 'plpgsql';
934
935 create or replace function
936 stratcon.fetch_dataset(in_check uuid,
937                        in_name text,
938                        in_start_time timestamptz,
939                        in_end_time timestamptz,
940                        in_hopeful_nperiods int,
941                        derive boolean)
942 returns setof stratcon.rollup_matrix_numeric_5m as
943 $$
944 declare
945   v_sql text;
946   v_sid int;
947   v_target record;
948   v_interval numeric;
949   v_start_adj timestamptz;
950   v_end_adj timestamptz;
951   v_l_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
952   v_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
953   v_r_rollup_row stratcon.rollup_matrix_numeric_5m%rowtype;
954 begin
955
956   -- Map out uuid to an sid.
957   select sid into v_sid from stratcon.map_uuid_to_sid where id = in_check;
958   if not found then
959     return;
960   end if;
961
962   select * into v_target from stratcon.choose_window(in_start_time, in_end_time, in_hopeful_nperiods);
963
964   if not found then
965     raise exception 'no target table';
966     return;
967   end if;
968
969   select 'epoch'::timestamp +
970          ((floor(extract('epoch' from in_start_time) /
971                  extract('epoch' from v_target.period)) *
972            extract('epoch' from v_target.period)) || ' seconds') ::interval
973     into v_start_adj;
974
975   select 'epoch'::timestamp +
976          ((floor(extract('epoch' from in_end_time) /
977                  extract('epoch' from v_target.period)) *
978            extract('epoch' from v_target.period)) || ' seconds') ::interval
979     into v_end_adj;
980
981   v_sql := 'select ' || v_sid || ' as sid, ' || quote_literal(in_name) || ' as name, ' ||
982            's.rollup_time, d.count_rows, d.avg_value, ' ||
983            'd.min_value, d.max_value ' ||
984            ' from ' ||
985            '(select ' || quote_literal(v_start_adj) || '::timestamp' ||
986                   ' + t * ' || quote_literal(v_target.period) || '::interval' ||
987                        ' as rollup_time' ||
988              ' from generate_series(1,' || v_target.nperiods || ') t) s ' ||
989            'left join ' ||
990            '(select * from stratcon.' || v_target.tablename ||
991            ' where sid = ' || v_sid ||
992              ' and name = ' || quote_literal(in_name) ||
993              ' and rollup_time between ' || quote_literal(v_start_adj) || '::timestamp' ||
994                                  ' and ' || quote_literal(v_end_adj) || '::timestamp) d' ||
995            ' using(rollup_time)';
996
997   for v_rollup_row in execute v_sql loop
998     if derive is true then
999       v_r_rollup_row := v_rollup_row;
1000       if v_l_rollup_row.count_rows is not null and
1001          v_rollup_row.count_rows is not null then
1002         v_interval := extract('epoch' from v_rollup_row.rollup_time) - extract('epoch' from v_l_rollup_row.rollup_time);
1003         v_r_rollup_row.count_rows := (v_l_rollup_row.count_rows + v_rollup_row.count_rows) / 2;
1004         v_r_rollup_row.avg_value :=
1005           (v_rollup_row.avg_value - v_l_rollup_row.avg_value) / v_interval;
1006         v_r_rollup_row.min_value :=
1007           (v_rollup_row.min_value - v_l_rollup_row.min_value) / v_interval;
1008         v_r_rollup_row.max_value :=
1009           (v_rollup_row.max_value - v_l_rollup_row.max_value) / v_interval;
1010       else
1011         v_r_rollup_row.count_rows = NULL;
1012         v_r_rollup_row.avg_value = NULL;
1013         v_r_rollup_row.min_value = NULL;
1014         v_r_rollup_row.max_value = NULL;
1015       end if;
1016     else
1017       v_r_rollup_row := v_rollup_row;
1018     end if;
1019     return next v_r_rollup_row;
1020     v_l_rollup_row := v_rollup_row;
1021   end loop;
1022   return;
1023 end
1024 $$ language 'plpgsql';
1025
1026 COMMIT;
Note: See TracBrowser for help on using the browser.