Changeset 15df64bd213a7a14a521d3d2d4418ee9379272aa

Show
Ignore:
Timestamp:
09/25/08 16:07:48 (6 years ago)
Author:
Denish Patel <denish@omniti.com>
git-committer:
Denish Patel <denish@omniti.com> 1222358868 +0000
git-parent:

[51b29b5f6a456bd88627a2ab6ee6d7fa15e22657]

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

implemented add/remove tags functionality for graphs and created function to update ts_search_all vector based on title and graph_tags, applied trigger to call the function on saved_graphs

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • sql/reconnoiter_ddl_dump.sql

    r43ec19a r15df64b  
    5252    title text, 
    5353    last_update timestamp without time zone NOT NULL, 
    54     ts_search_all tsvector 
     54    ts_search_all tsvector, 
     55    graph_tags text[] 
    5556); 
    5657 
     
    507508 
    508509SET search_path = prism, pg_catalog; 
     510 
     511-- 
     512-- Name: add_graph_tags(uuid, text); Type: FUNCTION; Schema: prism; Owner: reconnoiter 
     513-- 
     514 
     515CREATE FUNCTION add_graph_tags(in_graphid uuid, in_tags text) RETURNS void 
     516    AS $$ 
     517  DECLARE 
     518   v_graphid uuid; 
     519   v_graph_tags text[]; 
     520   new_tags_array text[]; 
     521   BEGIN 
     522       SELECT graphid,graph_tags into v_graphid,v_graph_tags 
     523         FROM prism.saved_graphs  
     524           WHERE graphid =in_graphid;  
     525     IF NOT FOUND THEN 
     526                 RAISE EXCEPTION 'GraphID does not exist in saved graphs table.'; 
     527            ELSE  
     528             new_tags_array:= array_append(v_graph_tags, in_tags); 
     529            UPDATE  prism.saved_graphs SET graph_tags = new_tags_array WHERE graphid=in_graphid;           
     530      END IF; 
     531    RETURN; 
     532  END 
     533$$ 
     534    LANGUAGE plpgsql; 
     535 
     536 
     537ALTER FUNCTION prism.add_graph_tags(in_graphid uuid, in_tags text) OWNER TO reconnoiter; 
    509538 
    510539-- 
     
    571600 
    572601-- 
     602-- Name: remove_graph_tags(uuid, text); Type: FUNCTION; Schema: prism; Owner: reconnoiter 
     603-- 
     604 
     605CREATE FUNCTION remove_graph_tags(in_graphid uuid, in_tags text) RETURNS void 
     606    AS $$ 
     607DECLARE 
     608v_graphid uuid; 
     609v_graph_tags text[]; 
     610new_tags_array text[]; 
     611i int; 
     612 BEGIN 
     613    SELECT graphid,graph_tags into v_graphid,v_graph_tags 
     614            FROM prism.saved_graphs  
     615              WHERE graphid =in_graphid;  
     616     IF NOT FOUND THEN 
     617                    RAISE EXCEPTION 'GraphID does not exist in saved graphs table.'; 
     618     ELSE  
     619        FOR i IN array_lower(v_graph_tags, 1)..array_upper(v_graph_tags, 1) LOOP 
     620           IF NOT v_graph_tags[i] =any(v_graph_tags) THEN 
     621              new_tags_array = array_append(new_tags_array, v_graph_tags[i]); 
     622           END IF; 
     623        END LOOP; 
     624        UPDATE  prism.saved_graphs SET graph_tags = new_tags_array WHERE graphid=in_graphid;            
     625     END IF; 
     626  RETURN; 
     627END 
     628$$ 
     629    LANGUAGE plpgsql; 
     630 
     631 
     632ALTER FUNCTION prism.remove_graph_tags(in_graphid uuid, in_tags text) OWNER TO reconnoiter; 
     633 
     634-- 
    573635-- Name: remove_tags(integer, text, text, text); Type: FUNCTION; Schema: prism; Owner: reconnoiter 
    574636-- 
     
    607669 
    608670ALTER FUNCTION prism.remove_tags(in_sid integer, in_metric_name text, in_metric_type text, in_tags text) OWNER TO reconnoiter; 
     671 
     672-- 
     673-- Name: saved_graphs_tsvector(uuid); Type: FUNCTION; Schema: prism; Owner: reconnoiter 
     674-- 
     675 
     676CREATE FUNCTION saved_graphs_tsvector(in_graphid uuid) RETURNS tsvector 
     677    AS $$DECLARE 
     678ref_graphid uuid; 
     679ref_graph_tags text; 
     680ref_title text; 
     681v_ts_search_all tsvector; 
     682BEGIN 
     683   SELECT graphid,COALESCE(array_to_string(graph_tags, ' '), ' '),title into ref_graphid,ref_graph_tags,ref_title 
     684               FROM prism.saved_graphs  
     685              WHERE graphid =in_graphid; 
     686    IF NOT FOUND THEN 
     687        RETURN NULL; 
     688    END IF; 
     689     
     690    ref_title := coalesce(replace(ref_title, '.', ' '), ' '); 
     691    ref_graph_tags := regexp_replace(ref_graph_tags, E'[_\`/.\\134]', ' ', 'g'); 
     692     
     693    v_ts_search_all=to_tsvector(ref_title || ' ' ||ref_graph_tags); 
     694     
     695    RETURN v_ts_search_all; 
     696END$$ 
     697    LANGUAGE plpgsql STRICT; 
     698 
     699 
     700ALTER FUNCTION prism.saved_graphs_tsvector(in_graphid uuid) OWNER TO reconnoiter; 
     701 
     702-- 
     703-- Name: trig_update_tsvector_saved_graphs(); Type: FUNCTION; Schema: prism; Owner: reconnoiter 
     704-- 
     705 
     706CREATE FUNCTION trig_update_tsvector_saved_graphs() RETURNS trigger 
     707    AS $$ 
     708DECLARE 
     709 BEGIN 
     710 IF TG_OP != 'INSERT' THEN 
     711   IF (NEW.graphid <> OLD.graphid) THEN 
     712           UPDATE prism.saved_graphs SET ts_search_all=prism.saved_graphs_tsvector(NEW.graphid); 
     713   END IF;     
     714 ELSE  
     715    UPDATE prism.saved_graphs SET ts_search_all=prism.saved_graphs_tsvector(NEW.graphid); 
     716 END IF;   
     717   RETURN NEW; 
     718END 
     719$$ 
     720    LANGUAGE plpgsql; 
     721 
     722 
     723ALTER FUNCTION prism.trig_update_tsvector_saved_graphs() OWNER TO reconnoiter; 
    609724 
    610725SET search_path = public, pg_catalog; 
     
    881996 
    882997CREATE FUNCTION fetch_varset(in_sid integer, in_name text, in_start_time timestamp with time zone, in_end_time timestamp with time zone, in_hopeful_nperiods integer) RETURNS SETOF loading_dock_metric_text_s_change_log 
    883     AS $$ 
    884 declare 
     998    AS $$declare 
    885999  v_sid int; 
    8861000  v_target record; 
     
    9571071    return next v_change_row; 
    9581072  end loop; 
     1073 
     1074 
     1075  if v_next_text is null then 
     1076    -- No rows. 
     1077    for v_change_row in 
     1078      select v_sid as sid, v_start_adj as whence, in_name as name, value 
     1079        from stratcon.loading_dock_metric_text_s_change_log 
     1080       where sid = v_sid and name = in_name and whence <= v_start_adj 
     1081    order by whence desc 
     1082       limit 1 
     1083    loop 
     1084      return next v_change_row; 
     1085    end loop; 
     1086  end if; 
    9591087 
    9601088  return; 
     
    22632391 
    22642392 
     2393-- 
     2394-- Name: trig_update_tsvector_saved_graphs; Type: TRIGGER; Schema: prism; Owner: reconnoiter 
     2395-- 
     2396 
     2397CREATE TRIGGER trig_update_tsvector_saved_graphs 
     2398    AFTER INSERT OR UPDATE ON saved_graphs 
     2399    FOR EACH ROW 
     2400    EXECUTE PROCEDURE trig_update_tsvector_saved_graphs(); 
     2401 
     2402 
    22652403SET search_path = stratcon, pg_catalog; 
    22662404