Welcome to Chris Johnsons' Portfolio

G'day. My name is Christopher Johnson and you've reached my humble portfolio which contains bits and pieces that I've created over the years. I've decided to post as much as I can in order to show where I've come from and in what ways I've improved. Thanks for stopping by. I hope you enjoy what you see. If you have any questions then feel free to contact me. Chris.

Twitter Update

Follow me on Twitter

Navigation

My Websites

Most Recent Additions

Browse by Month

Content Syndication

Meta

TimeTrack PL/pgsql and SQL Code Snippets

TimeTrackDB – mark_task_completed()

-- Function: 		taskcomplete
-- Author:     		Christopher Johnson
-- Description:		Updates the completed time(using DB Time) for a task when 
--             		it has been marked as complete
-- Passed in Varibles:   -
 
CREATE FUNCTION mark_task_completed() RETURNS TRIGGER AS $$
DECLARE
	getCurrentTime refcursor;
	currentTime TIMESTAMP WITHOUT TIME zone;
 
BEGIN
	IF NEW.task_status = 2 THEN
		-- get the current time
		OPEN getCurrentTime FOR EXECUTE 'SELECT now()';
		FETCH getCurrentTime INTO currentTime;
		CLOSE getCurrentTime;
 
		NEW.task_end_date := currentTime;
	END IF;
	RETURN NEW;
END;$$
    LANGUAGE plpgsql;

TimeTrackDB – milestone_trigger_func()

-- Function: 		milestone_trigger_func
-- Author:     		Christopher Johnson
-- Description:		Updates the completed time(using DB Time) for a task when it has been
-- 			marked as complete
-- Passed in Varibles:  -
-- Assumptions:		The start or end time is never assigned on insertion TimeTrack
--			on via updates.
--			The expected completion date is never changed on updates
 
CREATE FUNCTION milestone_trigger_func() RETURNS TRIGGER AS $$
DECLARE
	getPTime refcursor;
	setPTime refcursor;
 
	start_date TIMESTAMP WITHOUT TIME zone;
	end_date TIMESTAMP WITHOUT TIME zone;
	comp_date TIMESTAMP WITHOUT TIME zone;
 
	projectUpdateID INTEGER;
 
	updateOldDates BOOLEAN;
 
	attributes text;
	VALUES text;
BEGIN
	updateOldDates := FALSE;
 
	IF TG_OP = 'INSERT' THEN
		RETURN NULL;
	ELSIF TG_OP = 'UPDATE' THEN
	    -- Update the expected completion date in projects
	    OPEN getPTime FOR EXECUTE 'SELECT project_start_date, project_start_date
					FROM project WHERE project_id = ' || NEW.project_id;
	    FETCH getPTime INTO start_date, end_date;
	    CLOSE getPTime;
 
	    IF NEW.project_id != OLD.project_id THEN		
		updateOldDates := TRUE;
	    END IF;
 
	    -- check if we need to update project_start_date
            IF start_date < NEW.milestone_start_date OR start_date IS NULL THEN
	        UPDATE project SET project_start_date = NEW.milestone_start_date 
		        WHERE project_id = NEW.project_id;
            END IF;
 
            -- check if we need to update project_end_date
            IF end_date > NEW.milestone_end_date OR end_date IS NULL THEN
            	UPDATE project SET project_end_date = NEW.milestone_end_date 
			WHERE project_id = NEW.project_id;
            END IF;
	END IF;
 
	IF TG_OP = 'DELETE' OR updateOldDates = TRUE THEN
		-- Revert over all old values
		-- Find the new end_date
		OPEN setPTime FOR EXECUTE 
		'SELECT t.milestone_start_date 
		FROM milestone t 
		WHERE t.project_id = '|| OLD.project_id ||
		'ORDER BY t.milestone_start_date 
		DESC LIMIT 1';
 
		FETCH setPTime INTO start_date;
		UPDATE project SET project_start_date = start_date 
		WHERE project_id = OLD.project_id;
		CLOSE setPTime;
 
		-- Find the new end_date
		OPEN setPTime FOR EXECUTE 
		'SELECT t.milestone_end_date 
		FROM milestone t 
		WHERE t.project_id = '|| OLD.project_id ||
		'ORDER BY t.milestone_end_date 
		ASC LIMIT 1';
		FETCH setPTime INTO end_date;
		UPDATE project SET project_end_date = end_date WHERE project_id = OLD.project_id;
		CLOSE setPTime;
	END IF;
	RETURN NULL;
 
END;$$
    LANGUAGE plpgsql;

TimeTrackDB – Milestone Table

-- Ensure we are pointing to the correct schema
SET search_path = sep_schema, pg_catalog;
 
-- Milestone Table Creation
CREATE TABLE milestone (
    milestone_id INTEGER NOT NULL,
    milestone_name text,
    milestone_creation_date TIMESTAMP WITHOUT TIME zone NOT NULL,
    milestone_start_date TIMESTAMP WITHOUT TIME zone,
    milestone_end_date TIMESTAMP WITHOUT TIME zone,
    milestone_expected_completion TIMESTAMP WITHOUT TIME zone NOT NULL,
    project_id INTEGER,
    CONSTRAINT milestoneCreationBeforeEnd CHECK ((milestone_creation_date < milestone_end_date)),
    CONSTRAINT milestoneexpecdate CHECK ((milestone_creation_date < milestone_expected_completion))
);
ALTER TABLE milestone OWNER TO "TimeTrackAdmin";
 
-- Milestone Autoincrement Primary Key Sequence
CREATE SEQUENCE milestone_milestone_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
 
ALTER TABLE milestone_milestone_id_seq OWNER TO "TimeTrackAdmin";
 
-- Milestone Primary Key Default
ALTER TABLE milestone ALTER COLUMN milestone_id 
    SET DEFAULT NEXTVAL('milestone_milestone_id_seq'::regclass);
 
-- Milestone Foreign Key Constraints
ALTER TABLE ONLY milestone
    ADD CONSTRAINT milestone_project_id_fkey FOREIGN KEY (project_id) 
        REFERENCES project(project_id) ON DELETE CASCADE;
 
-- Milestone User Privileges
REVOKE ALL ON TABLE milestone FROM PUBLIC;
REVOKE ALL ON TABLE milestone FROM "TimeTrackAdmin";
GRANT ALL ON TABLE milestone TO "TimeTrackAdmin";
GRANT ALL ON TABLE milestone TO "TimeTrackProjectManager";
GRANT SELECT,REFERENCES,TRIGGER,UPDATE ON TABLE milestone TO "TimeTrackUser";
 
-- Milestone Sequence Privileges
REVOKE ALL ON SEQUENCE milestone_milestone_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE milestone_milestone_id_seq FROM "TimeTrackAdmin";
GRANT ALL ON SEQUENCE milestone_milestone_id_seq TO "TimeTrackAdmin";
GRANT ALL ON SEQUENCE milestone_milestone_id_seq TO "TimeTrackProjectManager";