Changeset 99564b3a9f761d3fb72e07eaa38809b4ff64c558

Show
Ignore:
Timestamp:
05/30/08 20:33:26 (5 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1212179606 +0000
git-parent:

[ee0ab939bfc68ba4c7c633c113f2a58b2ab443a7]

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

Updated Text change log trigger function to log older data changes too.

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • sql/schema.sql

    r6ea1dad r99564b3  
    254254    AS $$ 
    255255DECLARE 
    256     v_oldvalue TEXT; 
     256    v_oldvalue text; 
    257257    v_sid integer; 
    258258    v_name text; 
     259    v_value text; 
     260    v_whence timestamptz; 
     261    v_old_whence timestamptz; 
     262    v_old_name text; 
     263    v_old_sid integer; 
     264    v_old_value text; 
     265    v_max_whence timestamptz; 
    259266BEGIN 
    260267 
    261268IF 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; 
     269 
     270SELECT max(whence) FROM stratcon.loading_dock_metric_text_s WHERE whence <> NEW.whence and sid=NEW.sid and name = NEW.name  
     271        INTO v_max_whence; 
     272  
     273 IF NEW.whence < v_max_whence THEN             
     274  
     275    INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value) 
     276                 VALUES (NEW.sid,NEW.whence, NEW.name, NEW.value);  
     277                  
     278       SELECT  whence,name,value FROM  stratcon.loading_dock_metric_text_s_change_log WHERE whence > NEW.whence and sid=NEW.sid and name=NEW.name order by whence  limit 1  
     279        INTO v_whence,v_sid,v_name,v_value; 
     280          IF FOUND  THEN 
     281            IF v_value IS  DISTINCT FROM NEW.value THEN 
     282               NULL; 
     283            ELSE 
     284                DELETE from  stratcon.loading_dock_metric_text_s_change_log  WHERE whence=v_whence and sid=v_sid and name=v_name; 
     285            END IF; 
     286          END IF; 
     287        
     288          
     289       SELECT whence,sid,name,value from stratcon.loading_dock_metric_text_s where whence> NEW.whence and sid=NEW.sid and name=NEW.name and value!=NEW.value order by whence limit 1 
     290         INTO v_whence,v_sid,v_name,v_value; 
     291          IF FOUND  THEN 
     292             SELECT  whence,sid,name,value FROM  stratcon.loading_dock_metric_text_s_change_log WHERE whence =v_whence and sid=v_sid and name=v_name and value=v_value 
     293                 INTO v_old_whence,v_old_sid,v_old_name,v_old_value; 
     294              IF FOUND THEN 
     295                 NULL; 
     296              ELSE 
     297                INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value) 
     298                 VALUES (v_sid,v_whence, v_name, v_value);  
     299               END IF; 
     300        END IF; 
     301 
     302  ELSE 
     303             SELECT value FROM  stratcon.loading_dock_metric_text_s WHERE sid = NEW.sid AND name = NEW.name  
     304                 AND WHENCE = (SELECT max(whence) FROM stratcon.loading_dock_metric_text_s_change_log  
     305                                 WHERE WHENCE <> NEW.WHENCE and sid=NEW.sid and name=NEW.name ) 
     306                     INTO v_oldvalue; 
     307 
     308                    IF v_oldvalue IS DISTINCT FROM NEW.value THEN 
     309                 
     310                        INSERT INTO stratcon.loading_dock_metric_text_s_change_log (sid,whence,name,value) 
     311                            VALUES (NEW.sid, NEW.whence, NEW.name, NEW.value);  
     312                    END IF; 
     313  END IF;            
     314 
     315 
     316SELECT sid,metric_name FROM stratcon.metric_name_summary WHERE sid=NEW.sid  and metric_name=NEW.name 
     317        INTO v_sid,v_name; 
     318     IF NOT FOUND THEN 
     319          INSERT INTO  stratcon.metric_name_summary(sid,metric_name,metric_type)  VALUES(NEW.sid,NEW.name,'text'); 
     320     END IF; 
    278321 
    279322ELSE