[Gforge-commits] gforge/db 20040914.sql,NONE,1.1

[tperdue]

Update of /cvsroot/gforge/gforge/db
In directory db.perdue.net:/home/tperdue/share/dev.gforge.org/db

Added Files:
        20040914.sql 
Log Message:
initial commit of stored procs to add counters to taskman

--- NEW FILE: 20040914.sql ---
CREATE TABLE project_counts_agg (
        group_project_id integer NOT NULL,
        count integer DEFAULT 0 NOT NULL,
        open_count integer DEFAULT 0
);

INSERT INTO project_counts_agg 
        SELECT group_project_id, 
        (SELECT count(*) FROM project_task WHERE status_id != 3 AND 
                
project_task.group_project_id=project_group_list.group_project_id),
        (SELECT count(*) FROM project_task WHERE status_id = 2 AND 
                
project_task.group_project_id=project_group_list.group_project_id)
        FROM project_group_list;

CREATE FUNCTION projectgrouplist_insert_agg () RETURNS opaque AS '
BEGIN
    INSERT INTO project_counts_agg (group_project_id,count,open_count)
        VALUES (NEW.group_project_id,0,0);
        RETURN NEW;
END;
' LANGUAGE 'plpgsql';


CREATE FUNCTION projectgroup_update_agg () RETURNS opaque AS '
BEGIN
    --
    -- see if they are moving to a new subproject
    -- if so, its a more complex operation
    --
    IF NEW.group_project_id <> OLD.group_project_id THEN
        --
        -- transferred tasks always have a status of 1
        -- so we will increment the new subprojects sums
        --
        IF OLD.status_id=3 THEN
            -- No need to decrement counters on old tracker
        ELSE
            IF OLD.status_id=2 THEN
                UPDATE project_counts_agg SET count=count-1
                    WHERE group_project_id=OLD.group_project_id;
            ELSE
                IF OLD.status_id=1 THEN
                    UPDATE project_counts_agg SET 
count=count-1,open_count=open_count-1
                        WHERE group_project_id=OLD.group_project_id;
                END IF;
            END IF;
        END IF;

        IF NEW.status_id=3 THEN
            --DO NOTHING
        ELSE
            IF NEW.status_id=2 THEN
                    UPDATE project_counts_agg SET count=count+1
                        WHERE group_project_id=NEW.group_project_id;
            ELSE
                IF NEW.status_id=1 THEN
                    UPDATE project_counts_agg SET count=count+1, 
open_count=open_count+1
                        WHERE group_project_id=NEW.group_project_id;
                END IF;
            END IF;
        END IF;
    ELSE
        --
        -- just need to evaluate the status flag and
        -- increment/decrement the counter as necessary
        --
        IF NEW.status_id <> OLD.status_id THEN
            IF NEW.status_id = 1 THEN
                IF OLD.status_id=2 THEN
                    UPDATE project_counts_agg SET open_count=open_count+1
                        WHERE group_project_id=NEW.group_project_id;
                ELSE
                    IF OLD.status_id=3 THEN
                        UPDATE project_counts_agg SET open_count=open_count+1, 
count=count+1
                            WHERE group_project_id=NEW.group_project_id;
                    END IF;
                END IF;
            ELSE
                IF NEW.status_id = 2 THEN
                    IF OLD.status_id=1 THEN
                        UPDATE project_counts_agg SET open_count=open_count-1
                            WHERE group_project_id=NEW.group_project_id;
                    ELSE
                        IF OLD.status_id=3 THEN
                            UPDATE project_counts_agg SET count=count+1
                                WHERE group_project_id=NEW.group_project_id;
                        END IF;
                    END IF;
                ELSE
                    IF NEW.status_id = 3 THEN
                        IF OLD.status_id=2 THEN
                            UPDATE project_counts_agg SET count=count-1
                                WHERE group_project_id=NEW.group_project_id;
                        ELSE
                            IF OLD.status_id=1 THEN
                                UPDATE project_counts_agg SET 
open_count=open_count-1,count=count-1
                                    WHERE group_project_id=NEW.group_project_id;
                            END IF;
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
    END IF;
    RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER projectgrouplist_insert_trig AFTER INSERT ON artifact_group_list
FOR EACH ROW EXECUTE PROCEDURE projectgrouplist_insert_agg ();


CREATE TRIGGER projectgroup_update_trig AFTER UPDATE ON artifact
FOR EACH ROW EXECUTE PROCEDURE projectgroup_update_agg ();