Show
Ignore:
Timestamp:
03/21/08 19:56:26 (5 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1206129386 +0000
git-parent:

[9ae46347d8ade4004022aa28bfe4dc8ed04689d7]

git-author:
Denish Patel <denish@omniti.com> 1206129386 +0000
Message:

Updated Schema Sql

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • sql/schema.sql

    r7de429d rb9b5a61  
    11BEGIN; 
    22 
    3 CREATE USER stratcon; 
     3-- Tables  
    44 
    5 CREATE SCHEMA stratcon; 
    6  
    7 CREATE TABLE stratcon.loading_dock_check ( 
    8   remote_address inet, 
    9   whence timestamptz not null, 
    10   id uuid not null, 
    11   target text not null, 
    12   module text not null, 
    13   name text not null, 
    14   PRIMARY KEY(id,whence) 
    15 ); 
    16 CREATE TABLE stratcon.loading_dock_status ( 
    17   remote_address inet, 
    18   whence timestamptz not null, 
    19   id uuid not null, 
    20   state char(1) not null, 
    21   availability char(1) not null, 
    22   duration integer not null, 
    23   status text, 
    24   PRIMARY KEY(id,whence) 
    25 ); 
    26 -- There's so much data in these tables, it would 
    27 -- be crazy not to specify a date range. So... 
    28 -- whence is first in the PK. 
    29 CREATE TABLE stratcon.loading_dock_metric_numeric ( 
    30   remote_address inet, 
    31   whence timestamptz not null, 
    32   id uuid not null, 
    33   name text not null, 
    34   type char(1) not null, 
    35   value numeric, 
    36   PRIMARY KEY(whence,id,name) 
    37 ); 
    38 CREATE TABLE stratcon.loading_dock_metric_text ( 
    39   remote_address inet, 
    40   whence timestamptz not null, 
    41   id uuid not null, 
    42   name text not null, 
    43   type char(1) not null, 
    44   value text, 
    45   PRIMARY KEY(whence,id,name) 
     5CREATE 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) 
    4614); 
    4715 
    48 GRANT USAGE ON SCHEMA stratcon TO stratcon; 
    49 GRANT INSERT ON stratcon.loading_dock_check TO stratcon; 
    50 GRANT INSERT ON stratcon.loading_dock_status TO stratcon; 
    51 GRANT INSERT ON stratcon.loading_dock_metric_numeric TO stratcon; 
    52 GRANT INSERT ON stratcon.loading_dock_metric_text TO stratcon; 
     16CREATE 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 
     26CREATE 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 
     34CREATE 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 
     42CREATE 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 
     50CREATE 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 
     62CREATE TABLE stratcon.map_uuid_to_sid ( 
     63    id uuid NOT NULL, 
     64    sid integer NOT NULL, 
     65    PRIMARY KEY(id,sid) 
     66); 
     67 
     68CREATE TABLE stratcon.log_whence_s ( 
     69    whence timestamp with time zone NOT NULL, 
     70    PRIMARY KEY(whence) 
     71); 
     72 
     73-- Schema Sequence  
     74 
     75CREATE 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 
     85CREATE OR REPLACE FUNCTION stratcon.generate_sid_from_id(v_in_id uuid) 
     86RETURNS integer 
     87AS $$ 
     88DECLARE 
     89   v_ex_sid integer; 
     90   v_new_sid integer; 
     91   v_sql text; 
     92BEGIN 
     93 
     94SELECT 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 
     109END 
     110$$ LANGUAGE plpgsql; 
     111 
     112-- Trigger Function to change Metrix Text Changes  
     113 
     114CREATE 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 
     119CREATE FUNCTION stratcon.loading_dock_metric_text_s_change_log() RETURNS trigger 
     120    AS $$ 
     121DECLARE 
     122    v_oldvalue TEXT; 
     123BEGIN 
     124 
     125IF 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 
     138ELSE 
     139        RAISE EXCEPTION 'Non-INSERT DML operation attempted on INSERT only table'; 
     140END IF; 
     141 
     142    RETURN NULL; 
     143 
     144END 
     145$$ 
     146    LANGUAGE plpgsql; 
     147     
     148-- Trigger on Metrix Numeric to log last inserted timestamp  
     149 
     150CREATE 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     
     156CREATE FUNCTION stratcon.loading_dock_metric_numeric_s_whence_log() RETURNS trigger 
     157    AS $$ 
     158DECLARE 
     159v_whence timestamptz; 
     160BEGIN 
     161IF 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; 
     167ELSE 
     168        RAISE EXCEPTION 'Non-INSERT DML operation attempted on INSERT only table'; 
     169END IF; 
     170 
     171    RETURN NULL; 
     172END 
     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; 
    53193 
    54194COMMIT; 
     195