root/sql/schema.sql

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

updated functions for rollups

  • 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 timestamp with time zone 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 timestamp with time zone 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_metric_numeric_s (
27     sid integer NOT NULL,
28     whence timestamp with time zone NOT NULL,
29     name text NOT NULL,
30     value numeric,
31     PRIMARY KEY(whence,sid,name)
32 );
33
34 CREATE TABLE stratcon.loading_dock_metric_text_s (
35     sid integer NOT NULL,
36     whence timestamp with time zone NOT NULL,
37     name text NOT NULL,
38     value text,
39     PRIMARY KEY(whence,sid,name)
40 );
41
42 CREATE TABLE stratcon.loading_dock_metric_text_s_change_log (
43     sid integer NOT NULL,
44     whence timestamp with time zone NOT NULL,
45     name text NOT NULL,
46     value text,
47     PRIMARY KEY(whence,sid,name)
48 );
49
50
51 CREATE TABLE stratcon.rollup_matrix_numeric_60m(
52    sid integer not null,
53    name text not null,
54    rollup_time timestamp not null,
55    count_rows integer,
56    avg_value numeric ,
57    stddev_value numeric,
58    min_value numeric ,
59    max_value numeric ,
60    PRIMARY KEY(rollup_time,sid,name));
61  
62 CREATE TABLE stratcon.rollup_matrix_numeric_5m (
63     sid integer NOT NULL,
64     name text NOT NULL,
65     rollup_time timestamp with time zone NOT NULL,
66     count_rows integer,
67     avg_value numeric,
68     stddev_value numeric,
69     min_value numeric,
70     max_value numeric,
71     PRIMARY KEY (rollup_time,sid,name)
72 );
73
74 CREATE TABLE stratcon.map_uuid_to_sid (
75     id uuid NOT NULL,
76     sid integer NOT NULL,
77     PRIMARY KEY(id,sid)
78 );
79
80 CREATE TABLE stratcon.log_whence_s (
81     whence timestamp with time zone NOT NULL,
82     PRIMARY KEY(whence)
83 );
84
85 -- Schema Sequence
86
87 CREATE SEQUENCE stratcon.seq_sid
88     START WITH 50
89     INCREMENT BY 1
90     NO MAXVALUE
91     NO MINVALUE
92     CACHE 1;
93
94
95 -- Function To generate SID from ID
96
97 CREATE OR REPLACE FUNCTION stratcon.generate_sid_from_id(v_in_id uuid)
98 RETURNS integer
99 AS $$
100 DECLARE
101    v_ex_sid integer;
102    v_new_sid integer;
103  
104 BEGIN
105
106 SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id
107   INTO v_ex_sid;
108
109  IF NOT FOUND THEN
110     SELECT nextval('stratcon.seq_sid')
111     INTO v_new_sid;
112
113     INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES (v_in_id,v_new_sid);
114    
115     RETURN v_new_sid;
116  ELSE
117       RETURN v_ex_sid;
118  END IF;
119
120 END
121 $$ LANGUAGE plpgsql;
122
123 -- Trigger Function to change Metrix Text Changes
124
125 CREATE TRIGGER loading_dock_metric_text_s_change_log
126     AFTER INSERT ON loading_dock_metric_text_s
127     FOR EACH ROW
128     EXECUTE PROCEDURE loading_dock_metric_text_s_change_log();
129
130 CREATE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger
131     AS $$
132 DECLARE
133     v_oldvalue TEXT;
134 BEGIN
135
136 IF TG_OP = 'INSERT' THEN
137     SELECT value FROM  stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name
138         AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log
139                         WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name )
140     INTO v_oldvalue;
141
142     IF v_oldvalue IS DISTINCT FROM NEW.value THEN
143
144         INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value)
145             VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value);
146
147     END IF;
148
149 ELSE
150         RAISE EXCEPTION 'Non-INSERT DML operation attempted on INSERT only table';
151 END IF;
152
153     RETURN NULL;
154
155 END
156 $$
157     LANGUAGE plpgsql;
158    
159 -- Trigger on Metrix Numeric to log last inserted timestamp
160
161 CREATE TRIGGER loading_dock_metric_numeric_s_whence_log
162     AFTER INSERT ON loading_dock_metric_numeric_s
163     FOR EACH ROW
164     EXECUTE PROCEDURE loading_dock_metric_numeric_s_whence_log();
165
166    
167 CREATE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() RETURNS trigger
168     AS $$
169 DECLARE
170 v_whence timestamptz;
171 BEGIN
172 IF TG_OP = 'INSERT' THEN
173     SELECT whence FROM stratcon.log_whence_s WHERE whence=NEW.whence
174      INTO v_whence;
175    IF NOT FOUND THEN
176        UPDATE stratcon.log_whence_s SET whence=NEW.whence;
177    END IF;
178 ELSE
179         RAISE EXCEPTION 'Non-INSERT DML operation attempted on INSERT only table';
180 END IF;
181
182     RETURN NULL;
183 END
184 $$
185     LANGUAGE plpgsql;
186
187
188
189
190 -- Generic rollup function (under progress)
191
192
193
194 CREATE OR REPLACE FUNCTION stratcon.generic_rollup_metrix_numeric()
195 RETURNS void
196 AS $$
197
198 DECLARE
199
200 v_min_whence TIMESTAMPTZ;
201 v_max_rollup_5 TIMESTAMPTZ;
202 v_max_rollup_60 TIMESTAMPTZ;
203
204 BEGIN
205
206   select min(whence) from stratcon.log_whence_s
207          INTO v_min_whence;
208          
209   select max(rollup_time) from  stratcon.rollup_matrix_numeric_5m
210          INTO v_max_rollup_5;         
211  
212   select max(rollup_time) from  stratcon.rollup_matrix_numeric_60m
213          INTO v_max_rollup_60;
214          
215  IF v_max_rollup_5 IS NULL  THEN
216    v_max_rollup_5:=timestamp '2008-01-01 00:00:00';
217  END IF;
218  
219  IF v_max_rollup_60 IS NULL  THEN
220     v_max_rollup_60:=timestamp '2008-01-01 00:00:00';
221   END IF;
222  
223          
224   IF v_min_whence >= v_max_rollup_5 THEN
225  
226   -- 5 MINUTES ROLLUP
227  
228      PERFORM stratcon.rollup_matrix_numeric_5m(v_min_whence);
229      
230      -- HOURLY ROLLUP
231      
232      IF  date_trunc('H',v_min_whence)!= date_trunc('H',v_max_rollup_60) THEN
233      
234        PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence);
235      
236      END IF;
237  
238   -- DELETE FROM LOG TABLE
239  
240    DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence;
241  
242   ELSIF v_min_whence < v_max_rollup_5 THEN
243  
244    -- 5 MINUTES ROLLUP
245
246 DELETE FROM stratcon.rollup_matrix_numeric_5m
247  WHERE rollup_time = date_trunc('minutes',v_min_whence);
248      
249       PERFORM stratcon.rollup_matrix_numeric_5m(v_min_whence ,v_max_rollup_5);
250      
251   -- HOURLY ROLLUP
252    
253    DELETE FROM stratcon.rollup_matrix_numeric_60m
254        WHERE date_trunc('hour',rollup_time) = date_trunc('hour',v_min_whence);
255        
256          PERFORM stratcon.rollup_matrix_numeric_60m(v_min_whence);
257          
258   -- DELETE FROM LOG TABLE
259
260       DELETE FROM stratcon.log_whence_s WHERE WHENCE=v_min_whence;
261      
262   ELSE
263  
264       RETURN;
265  
266   END IF;
267  
268 RETURN;
269
270 EXCEPTION
271     WHEN RAISE_EXCEPTION THEN
272        RAISE EXCEPTION '%', SQLERRM;
273     WHEN OTHERS THEN
274       RAISE NOTICE '%', SQLERRM;
275 END
276 $$ LANGUAGE plpgsql;
277
278 --- 5 minutes rollup
279
280 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_5m(v_min_whence timestamptz)
281 RETURNS void
282 AS $$
283 DECLARE
284  
285  rec stratcon.rollup_matrix_numeric_5m%rowtype;
286  v_sql TEXT;
287  
288 BEGIN
289
290  FOR rec IN
291                 SELECT sid , name, date_trunc('H',whence) + (round(extract('minute' from whence)/5)*5) * '1 minute'::interval as rollup_time,
292                       COUNT(1) as count_rows ,AVG(value) as avg_value,STDDEV(value) as stddev_value ,MIN(value) as min_value ,MAX(value) as max_value
293                       FROM stratcon.loading_dock_metric_numeric_s
294                       WHERE WHENCE < date_trunc('minutes',v_min_whence) AND WHENCE >= date_trunc('minutes',v_min_whence)-'5 minutes'::interval
295                 GROUP BY rollup_time,sid,name
296  
297        LOOP
298  
299         INSERT INTO stratcon.rollup_matrix_numeric_5m
300          (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES
301          (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value);
302        
303  
304  END LOOP;
305
306  
307 RETURN;
308
309 EXCEPTION
310     WHEN RAISE_EXCEPTION THEN
311        RAISE EXCEPTION '%', SQLERRM;
312     WHEN OTHERS THEN
313       RAISE NOTICE '%', SQLERRM;
314 END
315 $$ LANGUAGE plpgsql;
316
317 --- Hourly rollup
318
319
320 CREATE OR REPLACE FUNCTION stratcon.rollup_matrix_numeric_60m(v_min_whence timestamptz)
321 RETURNS void
322 AS $$
323 DECLARE
324   rec stratcon.rollup_matrix_numeric_60m%rowtype;
325  v_sql TEXT;
326  
327 BEGIN
328     FOR rec IN
329                 SELECT sid , name,date_trunc('hour',rollup_time) as rollup_time,SUM(count_rows) as count_rows ,(SUM(avg_value*count_rows)/SUM(count_rows)) as avg_value,
330                          SQRT((SUM((count_rows-1)*(POWER(stddev_value,2)+POWER(avg_value,2)))/(SUM(count_rows)-1)))-(power(SUM(avg_value*count_rows)/SUM(count_rows),2)) as stddev_value,
331                          MIN(min_value) as min_value ,MAX(max_value) as max_value
332                          FROM stratcon.rollup_matrix_numeric_5m
333                            WHERE date_trunc('hour',rollup_time)= date_trunc('hour',v_min_whence)
334                    GROUP BY date_trunc('hour',rollup_time),sid,name
335         LOOP
336      
337           INSERT INTO stratcon.rollup_matrix_numeric_60m
338           (sid,name,rollup_time,count_rows,avg_value,stddev_value,min_value,max_value) VALUES
339           (rec.sid,rec.name,rec.rollup_time,rec.count_rows,rec.avg_value,rec.stddev_value,rec.min_value,rec.max_value);
340          
341      END LOOP;
342 RETURN;
343
344 EXCEPTION
345     WHEN RAISE_EXCEPTION THEN
346        RAISE EXCEPTION '%', SQLERRM;
347     WHEN OTHERS THEN
348       RAISE NOTICE '%', SQLERRM;
349 END
350 $$ LANGUAGE plpgsql;
351
352
353 -- GRANTS
354
355  GRANT SELECT,INSERT ON stratcon.loading_dock_status_s TO stratcon;
356  GRANT SELECT,INSERT ON stratcon.loading_dock_check  TO stratcon;
357  GRANT SELECT,INSERT ON stratcon.loading_dock_status TO stratcon;
358  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric TO stratcon;
359  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text TO stratcon;
360  GRANT SELECT,INSERT ON stratcon.loading_dock_check_s TO stratcon;
361  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric_s TO stratcon;
362  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s_change_log TO stratcon;
363  GRANT SELECT,INSERT,DELETE ON stratcon.log_whence_s TO stratcon;
364  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s TO stratcon;
365  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_60m TO stratcon;
366  GRANT SELECT,INSERT,DELETE ON stratcon.rollup_matrix_numeric_5m TO stratcon;
367  GRANT SELECT,INSERT ON stratcon.map_uuid_to_sid TO stratcon;
368  ALTER TABLE stratcon.seq_sid OWNER TO stratcon;
369
370 COMMIT;
Note: See TracBrowser for help on using the browser.