root/sql/schema.sql

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

Updated Schema Sql

  • 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 CREATE TABLE stratcon.loading_dock_metric_numeric_s_rullup_60m (
51     sid integer NOT NULL,
52     name text NOT NULL,
53     rollup_time timestamp with time zone NOT NULL,
54     count_rows integer,
55     avg_value numeric,
56     stddev_value numeric,
57     min_value numeric,
58     max_value numeric,
59     PRIMARY KEY (rollup_time,sid,name)
60 );
61
62 CREATE TABLE stratcon.map_uuid_to_sid (
63     id uuid NOT NULL,
64     sid integer NOT NULL,
65     PRIMARY KEY(id,sid)
66 );
67
68 CREATE TABLE stratcon.log_whence_s (
69     whence timestamp with time zone NOT NULL,
70     PRIMARY KEY(whence)
71 );
72
73 -- Schema Sequence
74
75 CREATE SEQUENCE stratcon.seq_sid
76     START WITH 50
77     INCREMENT BY 1
78     NO MAXVALUE
79     NO MINVALUE
80     CACHE 1;
81
82
83 -- Function To generate SID from ID
84
85 CREATE OR REPLACE FUNCTION stratcon.generate_sid_from_id(v_in_id uuid)
86 RETURNS integer
87 AS $$
88 DECLARE
89    v_ex_sid integer;
90    v_new_sid integer;
91    v_sql text;
92 BEGIN
93
94 SELECT sid FROM stratcon.map_uuid_to_sid WHERE id=v_in_id
95   INTO v_ex_sid;
96
97  IF NOT FOUND THEN
98     SELECT nextval('stratcon.seq_sid')
99     INTO v_new_sid;
100
101     v_sql:='INSERT INTO stratcon.map_uuid_to_sid(id,sid) VALUES ('||quote_literal(v_in_id)||','||v_new_sid||');';
102     EXECUTE v_sql;   
103    
104     RETURN v_new_sid;
105  ELSE
106       RETURN v_ex_sid;
107  END IF;
108
109 END
110 $$ LANGUAGE plpgsql;
111
112 -- Trigger Function to change Metrix Text Changes
113
114 CREATE TRIGGER loading_dock_metric_text_s_change_log
115     AFTER INSERT ON loading_dock_metric_text_s
116     FOR EACH ROW
117     EXECUTE PROCEDURE loading_dock_metric_text_s_change_log();
118
119 CREATE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger
120     AS $$
121 DECLARE
122     v_oldvalue TEXT;
123 BEGIN
124
125 IF TG_OP = 'INSERT' THEN
126     SELECT value FROM  stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name
127         AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log
128                         WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name )
129     INTO v_oldvalue;
130
131     IF v_oldvalue IS DISTINCT FROM NEW.value THEN
132
133         INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value)
134             VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value);
135
136     END IF;
137
138 ELSE
139         RAISE EXCEPTION 'Non-INSERT DML operation attempted on INSERT only table';
140 END IF;
141
142     RETURN NULL;
143
144 END
145 $$
146     LANGUAGE plpgsql;
147    
148 -- Trigger on Metrix Numeric to log last inserted timestamp
149
150 CREATE TRIGGER loading_dock_metric_numeric_s_whence_log
151     AFTER INSERT ON loading_dock_metric_numeric_s
152     FOR EACH ROW
153     EXECUTE PROCEDURE loading_dock_metric_numeric_s_whence_log();
154
155    
156 CREATE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() RETURNS trigger
157     AS $$
158 DECLARE
159 v_whence timestamptz;
160 BEGIN
161 IF TG_OP = 'INSERT' THEN
162     SELECT whence FROM stratcon.log_whence_s WHERE whence=NEW.whence
163      INTO v_whence;
164    IF NOT FOUND THEN
165        UPDATE stratcon.log_whence_s SET whence=NEW.whence;
166    END IF;
167 ELSE
168         RAISE EXCEPTION 'Non-INSERT DML operation attempted on INSERT only table';
169 END IF;
170
171     RETURN NULL;
172 END
173 $$
174     LANGUAGE plpgsql;
175
176
177
178 -- GRANTS
179
180  GRANT SELECT,INSERT ON stratcon.loading_dock_status_s TO stratcon;
181  GRANT SELECT,INSERT ON stratcon.loading_dock_check  TO stratcon;
182  GRANT SELECT,INSERT ON stratcon.loading_dock_status TO stratcon;
183  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric TO stratcon;
184  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text TO stratcon;
185  GRANT SELECT,INSERT ON stratcon.loading_dock_check_s TO stratcon;
186  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric_s TO stratcon;
187  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s_change_log TO stratcon;
188  GRANT SELECT,INSERT,UPDATE ON stratcon.log_whence_s TO stratcon;
189  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_text_s TO stratcon;
190  GRANT SELECT,INSERT ON stratcon.loading_dock_metric_numeric_s_rullup_60m TO stratcon;
191  GRANT SELECT,INSERT ON stratcon.map_uuid_to_sid TO stratcon;
192  ALTER TABLE stratcon.seq_sid OWNER TO stratcon;
193
194 COMMIT;
195
Note: See TracBrowser for help on using the browser.