gforge-db-postgresql 5.1.1-2 / usr / share / gforge / db / 20050130.sql

ALTER TABLE artifact ADD COLUMN last_modified_date integer;
ALTER TABLE project_task ADD COLUMN last_modified_date integer;

update project_task SET last_modified_date=EXTRACT(EPOCH FROM now())::integer;
update artifact SET last_modified_date=EXTRACT(EPOCH FROM now())::integer;

CREATE FUNCTION "update_last_modified_date" () RETURNS OPAQUE AS '
BEGIN
NEW.last_modified_date = EXTRACT(EPOCH FROM now())::integer;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER artifact_update_last_modified_date BEFORE INSERT OR UPDATE ON artifact
FOR EACH ROW EXECUTE PROCEDURE update_last_modified_date();

CREATE TRIGGER project_task_update_last_modified_date BEFORE INSERT OR UPDATE ON project_task
FOR EACH ROW EXECUTE PROCEDURE update_last_modified_date();

DROP VIEW project_task_vw;

CREATE VIEW project_task_vw AS
SELECT project_task.*,project_category.category_name,project_status.status_name,users.user_name,users.realname
FROM project_task
FULL JOIN project_category ON (project_category.category_id=project_task.category_id)
FULL JOIN users ON (users.user_id=project_task.created_by)
NATURAL JOIN project_status;


DROP VIEW artifact_vw;

CREATE VIEW artifact_vw
AS
SELECT  artifact.artifact_id,
        artifact.group_artifact_id,
        artifact.status_id,
        artifact.category_id,
        artifact.artifact_group_id,
        artifact.resolution_id,
        artifact.priority,
        artifact.submitted_by,
        artifact.assigned_to,
        artifact.open_date,
        artifact.close_date,
        artifact.summary,
        artifact.details,
        u.user_name AS assigned_unixname,
        u.realname AS assigned_realname,
        u.email AS assigned_email,
        u2.user_name AS submitted_unixname,
        u2.realname AS submitted_realname,
        u2.email AS submitted_email,
        artifact_status.status_name,
        artifact_category.category_name,
        artifact_group.group_name,
        artifact_resolution.resolution_name,
        artifact.last_modified_date
FROM    users u,
        users u2,
        artifact_status,
        artifact_category,
        artifact_group,
        artifact_resolution,
        artifact
WHERE   artifact.assigned_to = u.user_id
AND     artifact.submitted_by = u2.user_id
AND     artifact.status_id = artifact_status.id
AND     artifact.category_id = artifact_category.id
AND     artifact.artifact_group_id = artifact_group.id
AND     artifact.resolution_id = artifact_resolution.id;