WASHINGTON UNIVERSITY DATABASE LEAKED! DATA OF UNIVERSITY

/* filename: mk_job_db.sql */

/*
 *
 *
 */

 /* -----------------------------------------------------------------------
 *  Summary
 *
 *
 * youth
 * ------
 * New_youth					(p_fname varchar(64), p_mname varchar(64), p_lname varchar(64))
 * Update_youth 				(p_eid int, p_field varchar(64), p_value anyelement)
 * Get_youth_by_id				(p_id int)
 * Get_youth_by_short			(p_short text)
 * Get_youth_by_email			(p_email text)
 * Get_youth_by_username		(p_username text)
 * Get_youth_by_name			(p_begin char, p_offset int, p_limit int)
 * Get_youth_check_password		(p_user_name, p_user_password)
 * Get_recent_youth				(p_offset int, p_max int)
 * Get_all_youth				(p_offset int, p_max int)
 * Create_test_youth			(p_number)
 * Delete_all_youth				()
 * Delete_youth_by_id			(p_id int)
 *
 * Register_youth 				(p_user varchar(64), p_pass varchar(64), p_sim_name varchar(64), p_email varchar(64), p_cell varchar(64))
 * TODO: Check_youth_initials	(p_short char(4))
 *
 * moderator
 * ------
 * New_moderator			(p_fname varchar(64), p_mname varchar(64), p_lname varchar(64))
 * Update_moderator			(p_eid int, p_field varchar(64), p_value anyelement)
 * Get_all_moderator			(p_offset int, p_limit int)
 *
 * TODO: Check_moderator_initials	(p_short char(4))
 *       Update the last update fields in moderator, ect. ..
 *
 * Doc
 * ---
 * New_doc						(p_sid int, p_tid int, p_doc text, p_doc_type varchar(16))
 * New_doc_by_inititals			(p_sshort char(4), p_tshort char(4), p_doc text, doc_type dtw.doc_type_type)
 * Find_docs 					(p_doc_type dtw.doc_type_type, p_offset int, p_limit int)
 *
 * Employer
 * --------
 * New_employer 				(p_contact_fname text, p_contact_lname text, p_contact_email text)
 * Update_employer 				(p_eid int, p_field varchar(64), p_value anyelement)
 * Get_emp_by_id				(p_eid int)
 * Get_all_empoloyer			(p_offset int, p_limit int)
 * Get_all_emp_by_contact		(p_offset int, p_limit int)
 *
 * Job
 * ---
 * New_job						(p_eid, p_title)
 * Update_job					(p_eid int, p_field varchar(64), p_value anyelement)
 * Find_jobs 					(p_status job_status_type, p_offset int, p_limit int)
 *
 * Prop
 * ----
 * New_prop						(p_sid int, p_tid int, p_name text,	p_val anyelement, p_switch char(1))
 *
 Workflow
 *  --------
 *	Create_workflow ()
 *		Used to create an empty workflow object
 *	Delete_workflow ()
 *		Used to delete an empty workflow object
 *  Get_workflows ()
 * 		Used to get the list of workflows
 *
 *  Node
 *  ----
 *  Add_node ()
 *		Used to add a new node to a workflow
 *  Get_nodes ()
 * 		Used to get a list of all nodes for a workflow
 *	Get_node ()
 *		Used to get information about a node by the node short name
 *	Get_node_by_id ()
 *		Used to get information about a node by the node id
 *
 *	Edges
 *  -----
 *	Link_between ()
 *		Used to add an edge between two nodes
 *  Link_from_start ()
 *		Used to link from the special start node to the a node
 *  Link_to_finish ()
 *      Used to link from a node to a special finish node
 *	Get_children ()
 *		Used to get information on the children of a node
 *
 *  Exmaples
 *  --------
		select dtw.Create_workflow('work', 'Simple test of the workflow module');
		select dtw.Add_node('work', 'A', 'Document submitted', 'A');
		select dtw.Add_node('work', 'B', 'Document being reviewed', 'A');
		select dtw.Add_node('work', 'C', 'Document aapproved', 'A');
		select dtw.Link_from_start('work','A', '');
		select dtw.Link_between('work','A', 'B', 'Need to assign document');
		select dtw.Link_between('work','B','C','Need to approve document');
		select dtw.Link_to_finish('work','C', '');

		select dtw.Get_children('work','A');

		select dtw.Get_node_by_id(233);
		select dtw.Get_node('A');

		select dtw.Drop_workflow('work');

 * -----------------------------------------------------------------------
 */

/* CREATE OR REPLACE LANGUAGE  plpgsql; */


DROP SCHEMA IF EXISTS dtw CASCADE;
create schema dtw;

/* Gender */
drop domain if exists dtw.gender_type cascade;
create domain dtw.gender_type CHAR(1)
	check (value in (
		'M', 	/* Male */
		'F'		/* Female */
));

/* Status */
drop domain if exists dtw.status_type cascade;
create domain dtw.status_type CHAR(1)
	check (value in (
		'A', 		/* Active enrolled */
		'B',		/* Active engaged */
		'C',		/* Building */
		'D',		/* Living in doors */
		'E', 		/* On their journey  */
		'F', 		/* Diseased */
		'G',		/* Incarated */
		'Z'			/* Unknown */
));

/* Ethnicity */
drop domain if exists dtw.ethnicity_type cascade;
create domain dtw.ethnicity_type CHAR(1)
	check (value in (
		'A', 		/* US indian/Alaskan Native */
		'B',		/* White */
		'C',		/* Asian */
		'D',		/* Black/African American */
		'E', 		/* Hawaiian/Pacific Islander */
		'F', 		/* Don't know */
		'G',		/* Refused */
		'Z'			/* Not set */
));

/* Hispanic */
drop domain if exists dtw.hispanic_type cascade;
create domain dtw.hispanic_type CHAR(1)
	check (value in (
		'A', 		/* Hispanic */
		'B',		/* Non-Hispanic */
		'Z'			/* Not set */
));

/* -----------------------------------------------------------------------
 * Data model tables
 * -----------------------------------------------------------------------
 */

 /* -----------------------------------------------------------------------
 * youth tables and functions
 * -----------------------------------------------------------------------
 */
create sequence dtw.person_seq start with 100 no cycle;


DROP TABLE IF EXISTS dtw.youth;
create table dtw.youth (
	tid			int 	PRIMARY KEY DEFAULT nextval('dtw.person_seq'),

	initials 	char(4)	UNIQUE,

	username	text	UNIQUE,
	password	text,

/* Identity */
	sim_name	varchar(64),		/* simple name -- first and last */
	fname		varchar(64),		/* first */
	mname		varchar(64),		/* middle */
	lname		varchar(64),		/* last */
	snames 		varchar(64),		/* list of street names */
	dob			date, 				/* birth date */

	gender  	dtw.gender_type,
	transgender	boolean,

	ethnicity	char(8),			/* zero or more indicators */
	hispanic 	dtw.hispanic_type,

	status		dtw.status_type,						/* current status */
	last_signin	timestamp with time zone, 				/* last sign in */

	id_checked		boolean,
	id_checked_date	date,

/* Images and image captions */
	image01				text,
	image02				text,
	image03				text,

	image01_cap			text,
	image02_cap			text,
	image03_cap			text,

/* Application data */
	notes		text, 	/* open-end notes */
	data01		text, 	/* Available  */
	data02		text, 	/* Available  */
	data03		text,

/* Contact */
	cell		varchar(16),
	email		varchar(64),
	facebook	varchar(64),
	twitter		varchar(64),
	webpage     varchar(64),

	address01	text,
	address02	text,
	city 		varchar(64),
	state		char(2),
	zip			varchar(16),

	entered		timestamp with time zone, 	/* the date that this tuple was first created */
	last_update timestamp with time zone    /* the last date that this tuple was updated */
);

/*
 * This function creates a new youth
 */
CREATE OR REPLACE FUNCTION dtw.Register_youth (
	p_user	 		varchar(64),
	p_pass			varchar(64),
	p_sim_name		varchar(64),
	p_email			varchar(64),
	p_cell			varchar(64)
)
RETURNS int AS $PROC$
DECLARE
	t	int;
	f	int;
BEGIN
	select * from dtw.is_username_okay(p_user) into f;
	if (f = 0 ) THEN
		t := 0;
	else
		INSERT INTO dtw.youth (
			username,password,sim_name,email,cell, entered,last_update)
		VALUES (
			p_user, p_pass, p_sim_name, p_email, p_cell, now(), now())
		RETURNING tid into t;
	end if;
	return t;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This function creates a new youth
 */
CREATE OR REPLACE FUNCTION dtw.New_youth (
	p_fname	 		varchar(64),
	p_mname			varchar(64),
	p_lname			varchar(64)
)
RETURNS int AS $PROC$
DECLARE
	t	int;
BEGIN
	INSERT INTO dtw.youth (
	  	fname,mname,lname,entered,last_update)
	VALUES (
	  	p_fname, p_mname, p_lname, now(), now())
	RETURNING tid into t;
	return t;
END;
$PROC$ LANGUAGE plpgsql;


/*
 * This function updates the contents of a youth's field
 */
CREATE OR REPLACE FUNCTION dtw.Update_youth (
	p_tid	 		int,
	p_field			varchar(64),
	p_value			anyelement
	)
RETURNS int AS $PROC$
DECLARE
	q		text;
	row1	dtw.youth%ROWTYPE;
BEGIN
	if p_field = 'initials' then
		select into row1 * from dtw.youth where initials = p_value;
		if FOUND then
			return -1;
		end if;
	elsif p_field = 'username' then
		select into row1 * from dtw.youth where username = p_value;
		if FOUND then
			return -1;
		end if;
	end if;

	q := format('update dtw.youth SET %I = $1 WHERE tid = $2;', p_field);
	EXECUTE q USING p_value, p_tid;
	return p_tid;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This function returns a youth by its id
 */
CREATE OR REPLACE FUNCTION dtw.Get_youth_by_id (
	p_tid	 		int
	)
RETURNS dtw.youth as $PROC$
DECLARE
	row1	dtw.youth%ROWTYPE;
BEGIN
	select into row1 * from dtw.youth where tid = p_tid;
	return row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This function returns a youth by its short name (aka initials)
 */
CREATE OR REPLACE FUNCTION dtw.Get_youth_by_short (
	p_short	 		text
	)
RETURNS dtw.youth as $PROC$
DECLARE
	row1	dtw.youth%ROWTYPE;
BEGIN
	select into row1 * from dtw.youth where initials = p_short;
	return row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This function returns a youth by its email
 */
CREATE OR REPLACE FUNCTION dtw.Get_youth_by_email (
	p_email			text
)
RETURNS dtw.youth as $PROC$
DECLARE
	row1	dtw.youth%ROWTYPE;
BEGIN
	select into row1 * from dtw.youth where email = p_email;
	return row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This function returns a youth record by user name
 */
CREATE OR REPLACE FUNCTION dtw.Get_youth_by_username (
	p_username		text
	)
RETURNS dtw.youth as $PROC$
DECLARE
	row1	dtw.youth%ROWTYPE;
BEGIN
	select into row1 * from dtw.youth where username = p_username;
	return row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * Returns TRUE if user name is okay
 */
CREATE OR REPLACE FUNCTION dtw.is_username_okay (
	p_username		text
	)
RETURNS int as $PROC$
DECLARE
	row1	dtw.youth%ROWTYPE;
	f1		boolean;
	f2		boolean;
	f3		boolean;
BEGIN
	select into row1 * from dtw.youth where username = p_username;
	if NOT FOUND THEN
		f1 := true;
	END IF;

	select into row1 * from dtw.moderator where username = p_username;
	IF NOT FOUND THEN
		f2 := true;
	END IF;

	select into row1 * from dtw.emp where username = p_username;
	IF NOT FOUND THEN
		f3 := true;
	END IF;

	if (f1 = true and f2 = true and f3 = true) then
		return 1;
	else
		return 0;
	end if;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * Returns the internal ID if the user id and password match.
 *
 *    The User ID is not found:		return -2
 *    The password does not match:	return -1
 *	  The password does match: return the id.
 *
 */
CREATE OR REPLACE FUNCTION dtw.Get_youth_check_password (
	p_username 		text,
	p_password		text
	)
RETURNS integer as $PROC$
DECLARE
	row1	dtw.youth%ROWTYPE;
BEGIN
	select into row1 * from dtw.youth where username =  p_username;
	IF NOT FOUND THEN
		RETURN -2;
	END IF;

	select into row1 * from dtw.youth where username =  p_username AND password = p_password;
	IF NOT FOUND THEN
		RETURN -1;
	END IF;

	return row1.tid;
END;
$PROC$ LANGUAGE plpgsql;


/*
 * This function returns a list youths by the most recently added
 */
CREATE OR REPLACE FUNCTION dtw.Get_recent_youth (
	p_offset		int,
	p_max	 		int
)
RETURNS SETOF dtw.youth as $PROC$
DECLARE
	row1	dtw.youth%ROWTYPE;
	q		text;
BEGIN
	if p_max = -1 then
		q := 'select * from dtw.youth order by last_update desc limit ALL offset $1';
		FOR row1 IN EXECUTE q USING p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	else
		q := 'select * from dtw.youth order by last_update desc limit $1 offset $2';
		FOR row1 IN EXECUTE q USING p_max, p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	end if;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * Returns a list of youths by starting letters of first or last name
 */
CREATE OR REPLACE FUNCTION dtw.Get_youth_by_name (
	p_begin	 		char,
	p_offset		int,
	p_limit			int
	)
RETURNS SETOF dtw.youth as $PROC$
DECLARE
	row1	dtw.youth%ROWTYPE;
	q		text;
BEGIN
	if p_limit = -1 then
		q := format('select * from dtw.youth where fname ILIKE ''%s%%'' OR lname ILIKE ''%s%%'' order by lname limit ALL offset $1',p_begin,p_begin);
		FOR row1 IN EXECUTE q USING p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	else
		q := format('select * from dtw.youth where fname ILIKE ''%s%%'' OR lname ILIKE ''%s%%'' order by lname limit $1 offset $2',p_begin,p_begin);
		FOR row1 IN EXECUTE q USING p_limit, p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	end if;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * Returns a list of youths
 */
CREATE OR REPLACE FUNCTION dtw.Get_all_youth (
	p_offset	 	int,
	p_limit			int
	)
RETURNS SETOF dtw.youth as $PROC$
DECLARE
	row1	dtw.youth%ROWTYPE;
	q		text;
BEGIN
	if p_limit = -1 then
		q := 'select * from dtw.youth order by lname limit ALL offset $1';
		FOR row1 IN EXECUTE q USING p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	else
		q := 'select * from dtw.youth order by lname limit $1 offset $2';
		FOR row1 IN EXECUTE q USING p_limit, p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	end if;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This will delete all youth records from dtw.youth
 */
CREATE OR REPLACE FUNCTION dtw.Delete_all_youth ()
RETURNS void as $PROC$
BEGIN
	delete from dtw.youth;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This will delete all youth records from dtw.youth
 */
CREATE OR REPLACE FUNCTION dtw.Delete_youth_by_id (
	p_id	int
)
RETURNS void as $PROC$
BEGIN
	delete from dtw.youth Y where Y.tid = p_id;
END;
$PROC$ LANGUAGE plpgsql;


/*
 * This will insert a set of youth into the system -- useful for testing
 */
CREATE OR REPLACE FUNCTION dtw.Create_test_youth (
	p_num int
)
RETURNS void as $PROC$
DECLARE
	t		int;
	k		int;
BEGIN
	k := 1;
	LOOP
		select * into t from dtw.New_youth('Bob' || k, '', 'Lock' || k);
		k := k +1;
		IF k > (p_num-1) THEN
			EXIT;
		END IF;
	END LOOP;
END;
$PROC$ LANGUAGE plpgsql;


/* -----------------------------------------------------------------------
 * moderator functions
 * -----------------------------------------------------------------------
 */

DROP TABLE IF EXISTS dtw.moderator;
create table dtw.moderator (
	sid			int PRIMARY KEY DEFAULT nextval('dtw.person_seq'),

	initials 	char(4)	UNIQUE,

	username	text UNIQUE,
	password	text,

/* Identity */
	fname		varchar(64),
	mname		varchar(64),
	lname		varchar(64),

	dob			date,
	gender  	dtw.gender_type,

/* Images and image captions */
	image01				text,
	image02				text,
	image03				text,

	image01_cap			text,
	image02_cap			text,
	image03_cap			text,

/* Application data */
	notes		text, 	/* open-end notes */
	data01		text, 	/* Available  */
	data02		text, 	/* Available  */
	data03		text,

	cell		varchar(16),
	email		varchar(64),
	facebook	varchar(64),
	twitter		varchar(64),
	webpage		varchar(64),

	entered		timestamp with time zone, 	/* the date that this tuple was first created */
	last_update timestamp with time zone    /* the last date that this tuple was updated */
);

/*
 * This function creates a new moderator, returning the moderator id, sid
 */
CREATE OR REPLACE FUNCTION dtw.New_moderator (
	p_fname	 		varchar(64),
	p_mname			varchar(64),
	p_lname			varchar(64)
	)
RETURNS int AS $PROC$
DECLARE
	t	int;
BEGIN
	INSERT INTO dtw.moderator (
	  	fname,mname,lname,entered,last_update)
	VALUES (
	  	p_fname, p_mname, p_lname, now(), now())
	RETURNING sid into t;
	return t;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This function updates the contents of a moderators field
 */
CREATE OR REPLACE FUNCTION dtw.Update_moderator (
	p_sid	 		int,
	p_field			varchar(64),
	p_value			anyelement
	)
RETURNS int AS $PROC$
DECLARE
	q		text;
	row1	dtw.moderator%ROWTYPE;
BEGIN
	if p_field = 'initials' then
		select into row1 * from dtw.moderator where initials = p_value;
		if FOUND then
			return -1;
		end if;
	elsif p_field = 'username' then
		select into row1 * from dtw.moderator where username = p_value;
		if FOUND then
			return -1;
		end if;
	end if;

	q := format('update dtw.moderator SET %I = $1 WHERE sid = $2', p_field);
	EXECUTE q USING p_value, p_sid;
	return p_sid;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This function returns a moderator by its id
 */
CREATE OR REPLACE FUNCTION dtw.Get_moderator_by_id (
	p_sid	 		int
	)
RETURNS dtw.moderator as $PROC$
DECLARE
	row1	dtw.moderator%ROWTYPE;
BEGIN
	select into row1 * from dtw.moderator where sid = p_sid;
	return row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This function returns a moderator by its short name (aka initials)
 */
CREATE OR REPLACE FUNCTION dtw.Get_moderator_by_short (
	p_short	 		text
	)
RETURNS dtw.moderator as $PROC$
DECLARE
	row1	dtw.moderator%ROWTYPE;
BEGIN
	select into row1 * from dtw.moderator where initials = p_short;
	return row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This function returns a moderator record by its email
 */
CREATE OR REPLACE FUNCTION dtw.Get_moderator_by_email (
	p_email			text
)
RETURNS dtw.moderator as $PROC$
DECLARE
	row1	dtw.moderator%ROWTYPE;
BEGIN
	select into row1 * from dtw.moderator where email = p_email;
	return row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This function returns a moderator record by user name
 */
CREATE OR REPLACE FUNCTION dtw.Get_moderator_by_username (
	p_username		text
	)
RETURNS dtw.moderator as $PROC$
DECLARE
	row1	dtw.moderator%ROWTYPE;
BEGIN
	select into row1 * from dtw.moderator where username = p_username;
	return row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * Returns the internal ID if the user id and password match.
 *    The User ID is not found:		return -2
 *    The password does not match:	return -1
 *	  The password does match: 		return the id
 */
CREATE OR REPLACE FUNCTION dtw.Get_moderator_check_password (
	p_username 		text,
	p_password		text
	)
RETURNS integer as $PROC$
DECLARE
	row1	dtw.moderator%ROWTYPE;
BEGIN
	select into row1 * from dtw.moderator where username =  p_username;
	IF NOT FOUND THEN
		RETURN -2;
	END IF;

	select into row1 * from dtw.moderator where username =  p_username AND password = p_password;
	IF NOT FOUND THEN
		RETURN -1;
	END IF;
	return row1.sid;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * Returns a list of moderators by starting letters of first or last name
 */
CREATE OR REPLACE FUNCTION dtw.Get_all_moderator (
	p_offset		int,
	p_limit			int
	)
RETURNS SETOF dtw.moderator as $PROC$
DECLARE
	row1	dtw.moderator%ROWTYPE;
	q		text;
BEGIN
	if p_limit = -1 then
		q := 'select * from dtw.moderator order by lname limit ALL offset $1';
		FOR row1 IN EXECUTE q USING p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	else
		q := 'select * from dtw.moderator order by lname limit $1 offset $2';
		FOR row1 IN EXECUTE q USING p_limit, p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	end if;
END;
$PROC$ LANGUAGE plpgsql;

/*
* Returns a list of youths by starting letters of first or last name
 */
CREATE OR REPLACE FUNCTION dtw.Get_all_moderator (
	p_offset		int,
	p_limit			int
	)
RETURNS SETOF dtw.moderator as $PROC$
DECLARE
	row1	dtw.moderator%ROWTYPE;
	q		text;
BEGIN
	if p_limit = -1 then
		q := 'select * from dtw.moderator order by lname limit ALL offset $1';
		FOR row1 IN EXECUTE q USING p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	else
		q := 'select * from dtw.moderator order by lname limit $1 offset $2';
		FOR row1 IN EXECUTE q USING p_limit, p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	end if;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This will delete all moderators records from dtw.moderator
 */
CREATE OR REPLACE FUNCTION dtw.Delete_all_moderator ()
RETURNS void as $PROC$
BEGIN
	delete from dtw.moderator;
END;
$PROC$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dtw.Delete_moderator_by_id (
	p_sid		int
)
RETURNS void as $PROC$
BEGIN
	delete from dtw.moderator where sid = p_sid;
END;
$PROC$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dtw.Delete_moderator_by_username (
	p_username	text
)
RETURNS void as $PROC$
BEGIN
	delete from dtw.moderator where username = p_username;
END;
$PROC$ LANGUAGE plpgsql;

/* -----------------------------------------------------------------------
 * Employer ...
 * -----------------------------------------------------------------------
 */
DROP TABLE IF EXISTS dtw.emp;
create table dtw.emp (
	eid						int PRIMARY KEY DEFAULT nextval('dtw.person_seq'),
	contact_fname			text,
	contact_lname			text,
	contact_email			text,
	contact_tel_number		text,
	business 				text,
	location				text,		/* Informal description of location */
	neighborhood			text,		/* The neighborhood in which the job is located */
	mailing_address			text, 		/* The formal mailing address, including city and zip */
	city					text,
	zip						text,
	map_location			text, 		/* The location of the employer on a map */
	username				text UNIQUE,
	password				text,
	webpage					text,

/* Images and image captions */
	image01				text,
	image02				text,
	image03				text,

	image01_cap			text,
	image02_cap			text,
	image03_cap			text,

/* Dates */
	entered					timestamp with time zone, 	/* the date that this tuple was first created */
	last_update 			timestamp with time zone    /* the last date that this tuple was updated */
);

/*
 *  Xxx
 */
CREATE OR REPLACE FUNCTION dtw.New_employer (
	p_contact_fname	 		text,
	p_contact_lname	 		text,
	p_contact_email			text
	)
RETURNS int AS $PROC$
DECLARE
	t	int;
BEGIN
	INSERT INTO dtw.emp (
	  	contact_fname, contact_lname, contact_email, entered,last_update)
	VALUES (
	  	p_contact_fname, p_contact_fname, p_contact_email, now(), now())
	RETURNING eid into t;
	return t;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * xxx
 */
CREATE OR REPLACE FUNCTION dtw.Update_employer (
	p_eid	 		int,
	p_field			varchar(64),
	p_value			anyelement
	)
RETURNS int AS $PROC$
DECLARE
	q		text;
	row1	dtw.emp%ROWTYPE;
BEGIN
	if p_field = 'username' then
		select into row1 * from dtw.emp where username = p_value;
		if FOUND then
			return -1;
		end if;
	end if;

	q := format('update dtw.emp SET %I = $1 WHERE eid = $2', p_field);
	EXECUTE q USING p_value, p_eid;
	return p_eid;
END;
$PROC$ LANGUAGE plpgsql;

/*
 *  Returns an employee record based on the ID
 */
CREATE OR REPLACE FUNCTION dtw.Get_emp_by_id (
	p_eid	 		int
	)
RETURNS dtw.emp as $PROC$
DECLARE
	row1	dtw.emp%ROWTYPE;
BEGIN
	select into row1 * from dtw.emp where eid = p_eid;
	return row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 *  Returns an employee record based on the USER_ID
 */
CREATE OR REPLACE FUNCTION dtw.Get_emp_by_username (
	p_username	 		text
	)
RETURNS dtw.emp as $PROC$
DECLARE
	row1	dtw.emp%ROWTYPE;
BEGIN
	select into row1 * from dtw.emp where username = p_username;
	return row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 *  Returns an employee record based on the email address
 */
CREATE OR REPLACE FUNCTION dtw.Get_emp_by_email (
	p_email	 		text
	)
RETURNS dtw.emp as $PROC$
DECLARE
	row1	dtw.emp%ROWTYPE;
BEGIN
	select into row1 * from dtw.emp where contact_email = p_email;
	return row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * Returns the internal ID if the user id and password match.
 *    The User ID is not found:		return -2
 *    The password does not match:	return -1
 *	  The password does match: 		return the id
 */
CREATE OR REPLACE FUNCTION dtw.Get_emp_check_password (
	p_username 		text,
	p_password		text
	)
RETURNS integer as $PROC$
DECLARE
	row1	dtw.emp%ROWTYPE;
BEGIN
	select into row1 * from dtw.emp where username =  p_username;
	IF NOT FOUND THEN
		RETURN -2;
	END IF;

	select into row1 * from dtw.emp where username =  p_username AND password = p_password;
	IF NOT FOUND THEN
		RETURN -1;
	END IF;

	return row1.eid;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * Xxxx
 */
CREATE OR REPLACE FUNCTION dtw.Get_all_employer (
	p_offset		int,
	p_limit			int
	)
RETURNS SETOF dtw.emp as $PROC$
DECLARE
	row1	dtw.emp%ROWTYPE;
	q		text;
BEGIN
	if p_limit = -1 then
		q := 'select * from dtw.emp order by contact_lname limit ALL offset $1';
		FOR row1 IN EXECUTE q USING p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	else
		q := 'select * from dtw.emp order by contact_lname limit $1 offset $2';
		FOR row1 IN EXECUTE q USING p_limit, p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	end if;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * Xxxx
 */
CREATE OR REPLACE FUNCTION dtw.Get_all_emp_by_contact (
	p_offset		int,
	p_limit			int
	)
RETURNS SETOF dtw.emp as $PROC$
DECLARE
	row1	dtw.emp%ROWTYPE;
	q		text;
BEGIN
	if p_limit = -1 then
		q := 'select * from dtw.emp order by contact_lname limit ALL offset $1';
		FOR row1 IN EXECUTE q USING p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	else
		q := 'select * from dtw.emp order by contact_lname limit $1 offset $2';
		FOR row1 IN EXECUTE q USING p_limit, p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	end if;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This will delete all employer records from dtw.emp
 */
CREATE OR REPLACE FUNCTION dtw.Delete_all_emp ()
RETURNS void as $PROC$
BEGIN
	delete from dtw.emp;
END;
$PROC$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dtw.Delete_emp_by_id (
	p_eid		int
)
RETURNS void as $PROC$
BEGIN
	delete from dtw.moderator where eid = p_eid;
END;
$PROC$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dtw.Delete_emp_by_username (
	p_username	text
)
RETURNS void as $PROC$
BEGIN
	delete from dtw.moderator where username = p_username;
END;
$PROC$ LANGUAGE plpgsql;

/* -----------------------------------------------------------------------
 * Document ...
 * -----------------------------------------------------------------------
 */

  /* Status */
drop domain if exists dtw.doc_type_type cascade;
create domain dtw.doc_type_type CHAR(1)
	check (value in (
		'A', 		/* Plain note type */
		'B',		/* xxx */
		'C',		/* xxx */
		'D',  		/* xxx */
		'Z'			/* All */
));

create sequence dtw.doc_seq start with 100 no cycle;
DROP TABLE IF EXISTS dtw.doc;
create table dtw.doc (
	did			int PRIMARY KEY DEFAULT nextval('dtw.doc_seq'),
	doc			text,
	entered		timestamp with time zone,
	doc_type	dtw.doc_type_type DEFAULT 'A'
);

/*
 *  Creates a new document, returning the id
 */
CREATE OR REPLACE FUNCTION dtw.New_doc(
	p_doc				text,
	p_doc_type			dtw.doc_type_type
	)
RETURNS int AS $PROC$
DECLARE
	t	int;
BEGIN
	INSERT INTO dtw.doc (
	  	doc, entered, doc_type )
	VALUES (
		p_doc, now(), p_doc_type)
	RETURNING did into t;
	return t;
END;
$PROC$ LANGUAGE plpgsql;

/*
 *  CHECK THIS -- NOT SURE IF ITS NEEDED ...
 */
CREATE OR REPLACE FUNCTION dtw.New_doc_by_inititals(
	p_sshort			char(4),
	p_tshort			char(4),
	p_doc				text,
	doc_type			dtw.doc_type_type
	)
RETURNS int AS $PROC$
DECLARE
	sid_v		int;
	tid_v		int;
	t		int;
BEGIN
	select into sid_v sid from dtw.moderator where initials = p_sshort;
	IF NOT FOUND THEN
		RETURN 0;
	END IF;

	select into tid_v tid from dtw.youth where initials = p_tshort;
	IF NOT FOUND THEN
		RETURN 0;
	END IF;

	INSERT INTO dtw.doc (
	  	tid, sid, doc, entered, doc_type )
	VALUES (
		sid_v, tid_v, p_doc, now(), p_doc_type)
	RETURNING did into t;
	return t;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * xxx
 */
CREATE OR REPLACE FUNCTION dtw.Find_docs (
	p_doc_type		dtw.doc_type_type,
	p_offset		int,
	p_limit			int
	)
RETURNS SETOF dtw.doc as $PROC$
DECLARE
	row1	dtw.doc%ROWTYPE;
	q		text;
	w		text;
BEGIN
	if (p_doc_type = 'Z' or p_doc_type = NULL) then
		w := ' ';
	else
		w := 'where status = ' || '' || p_doc_type || '';
	end if;

	if (p_limit = -1) then
		q := 'select * from dtw.doc ' || w || ' order by entered desc limit ALL offset $1';
		FOR row1 IN EXECUTE q USING p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	else
		q := 'select * from dtw.doc ' || w || ' order by entered desc limit $1 offset $2';
		FOR row1 IN EXECUTE q USING p_limit, p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	end if;
END;
$PROC$ LANGUAGE plpgsql;

/* -----------------------------------------------------------------------
 * Properties
 * -----------------------------------------------------------------------
 */

drop domain if exists dtw.prop_type_type cascade;
create domain dtw.prop_type_type CHAR(1)
	check (value in (
		'A', 		/* String */
		'B',		/* Integer */
		'C',		/* Timestamp */
		'D'  		/* Number(8,2) */
));

DROP TABLE IF EXISTS dtw.prop_type;
create table dtw.prop_type (
	id				int PRIMARY KEY,
	propname			text,
	proptype			dtw.prop_type_type,
	shortl 				text,
	descr				text
);

insert into dtw.prop_type(id,propname,proptype,shortl,descr) values (1,'tags', 'A', 'Tag list', 'Used for a list of tags');
insert into dtw.prop_type(id,propname,proptype,shortl,descr) values (2,'path', 'B', 'Path', 'Estimates of how youth are doing');

create sequence dtw.prop_seq start with 100 no cycle;
DROP TABLE IF EXISTS dtw.prop;
create table dtw.prop (
	pid				int PRIMARY KEY DEFAULT nextval('dtw.prop_seq'),
	sid				int references dtw.moderator(sid),
	tid				int references dtw.youth(tid),
	switch			dtw.prop_type_type,
	propname		text,
	val_text		text,
	val_int			int,
	val_time		timestamp with time zone,
	entered			timestamp with time zone
);

CREATE OR REPLACE FUNCTION dtw.New_prop(
	p_sid				int,
	p_tid				int,
	p_name				text,
	p_val				anyelement,
	p_switch			char(1)
)
RETURNS int AS $PROC$
DECLARE
	t	int;
BEGIN
	t := 0;
	if (switch = 'A') then
		INSERT INTO dtw.doc (sid, tid, switch, propname, val_text, entered)
		VALUES (p_sid, p_tid, p_switch, p_name, p_val, now())
		RETURNING pid into t;
	elseif (switch = 'B') then
		INSERT INTO dtw.doc (sid, tid, switch, propname, val_int, entered)
		VALUES (p_sid, p_tid, p_switch, p_name, p_val, now())
		RETURNING pid into t;
	elseif (swtich = 'C') then
		INSERT INTO dtw.doc (sid, tid, switch, propname, val_time, entered)
		VALUES (p_sid, p_tid, p_switch, p_name, p_val, now())
		RETURNING pid into t;
	end if;
	return t;
END;
$PROC$ LANGUAGE plpgsql;


/* -----------------------------------------------------------------------
 * Job ...
 * -----------------------------------------------------------------------
 */

 /*
  * Jobs have a status indicator
  */
drop domain if exists dtw.job_status_type cascade;
create domain dtw.job_status_type CHAR(1)
	check (value in (
		'A', 		/* Proposed */
		'B',		/* Open */
		'C',		/* Assigned */
		'D',  		/* Closed */
		'Z'			/* All */
));

/*
 * Jobs are created by or on behalf of an employer
 */
create sequence dtw.job_seq start with 100 no cycle;
DROP TABLE IF EXISTS dtw.job;
create table dtw.job (
	jid						int PRIMARY KEY DEFAULT nextval('dtw.job_seq'),
	eid						int references dtw.emp(eid),
	title					text,
	job_date				timestamp with time zone,
	descr 					text,
	compensation 			text,
	notes		 			text,
	location				text,
	status					dtw.job_status_type DEFAULT 'A',
	entered					timestamp with time zone, 	/* the date that this tuple was first created */
	last_update 			timestamp with time zone    /* the last date that this tuple was updated */
);

/*
 * A job offer is actually a 'job assignment' -- that is, a job is assigned to a
 * youth actor by a moderator actor
 */
create sequence dtw.job_offer_seq start with 100 no cycle;
DROP TABLE IF EXISTS dtw.job_offer;
create table dtw.job_offer (
	joid					int PRIMARY KEY DEFAULT nextval('dtw.job_offer_seq'),
	jid						int references dtw.job(jid),
	tid						int references dtw.youth(tid),
	sid						int references dtw.moderator(sid),
	entered					timestamp with time zone 	/* the date that this tuple was first created */
);

/*
 * This is a bridge table for enabling notes to be associated with job offers
 */
DROP TABLE IF EXISTS dtw.job_offer_note;
create table dtw.job_offer_note (
	joid					int references dtw.job_offer(joid),
	sid						int references dtw.moderator(sid),
	did						int references dtw.doc(did)
);

/*
 *  This is used to create a new job offer. A job o
 */
CREATE OR REPLACE FUNCTION dtw.New_job_offer (
	p_jid					int,
	p_tid					int,
	p_sid					int,
	p_note 					text
	)
RETURNS int AS $PROC$
DECLARE
	t	int;
	d	int;
BEGIN
	INSERT INTO dtw.job_offer (
	  	jid, tid, sid, entered)
	VALUES (
		p_jid, p_tid, p_sid, now())
	RETURNING joid into t;

	update dtw.job SET status = 'C' WHERE jid = p_jid;

	if (p_note != '') then
		select * into d from dtw.New_doc(p_note,'A');
		INSERT INTO dtw.job_offer_note (
				joid, sid, did)
			VALUES(
				t,p_sid,d);
	end if;
	return t;
END;
$PROC$ LANGUAGE plpgsql;

/*
 *  This is used to add a new note to a job offer
 */
CREATE OR REPLACE FUNCTION dtw.New_note_for_job_offer (
	p_joid					int,
	p_sid					int,
	p_note 					text
	)
RETURNS int AS $PROC$
DECLARE
	d	int;
BEGIN
	select * into d from dtw.New_doc(p_note,'A');
	INSERT INTO dtw.job_offer_note (
		joid, sid, did)
	VALUES(
		p_joid,p_sid,d);
	return d;
END;
$PROC$ LANGUAGE plpgsql;

/*
 *  This is used to create a new job
 */
CREATE OR REPLACE FUNCTION dtw.New_job (
	p_eid					int,
	p_title	 				text
	)
RETURNS int AS $PROC$
DECLARE
	t	int;
BEGIN
	INSERT INTO dtw.job (
	  	eid, title, entered,last_update)
	VALUES (
		p_eid, p_title, now(), now())
	RETURNING jid into t;
	return t;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This is used to update the attributes of a job
 */
CREATE OR REPLACE FUNCTION dtw.Update_job (
	p_jid	 		int,
	p_field			varchar(64),
	p_value			anyelement
	)
RETURNS int AS $PROC$
DECLARE
	q		text;
BEGIN
	q := format('update dtw.job SET %I = $1 WHERE jid = $2', p_field);
	EXECUTE q USING p_value, p_jid;
	return p_jid;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This function returns information about a job
 */
CREATE OR REPLACE FUNCTION dtw.Get_job_info (
	p_job_id		int
	)
RETURNS dtw.job as $PROC$
DECLARE
	row1	dtw.job%ROWTYPE;
BEGIN
	select into row1 * from dtw.job where jid = p_job_id;
	return row1;
END;
$PROC$ LANGUAGE plpgsql;



/*
 * xxx
 */
/*
 * xxx
 */
CREATE OR REPLACE FUNCTION dtw.Find_jobs (
	p_emp_id		int,
	p_status		dtw.job_status_type,
	p_offset		int,
	p_limit			int
	)
RETURNS SETOF dtw.job as $PROC$
DECLARE
	row1				dtw.job%ROWTYPE;
	q					text;
	status_clause		text;
	emp_clause			text;
	where_clause		text;
BEGIN

	/*
	 * Check for employee id
	 */
	emp_clause := ' ';
	if (p_emp_id <> NULL) then
		emp_clause :=  ' eid = ' || '' || p_emp_id || '';
	end if;

	/*
	 * Check for status
	 */
	 status_clause := ' ';
	if (p_status = 'Z' or p_status = NULL) then
		status_clause := ' ';
	else
		status_clause := 'status = ' || '' || p_status || '';
	end if;

		/*
	 * Create where clause
	 */
	where_clause := ' ';
	if (emp_clause <> ' ') and (status_clause <> ' ') then
		where_clause := 'where ' || status_clause || ' and ' || emp_clause;
	elsif (emp_clause <> ' ') then
		where_clause := 'where ' || emp_clause;
	elsif (status_clause <> ' ') then
		where_clause := 'where ' || status_clause;
	end if;


	if (p_limit = -1) then
		q := 'select * from dtw.job ' || where_clause || ' order by entered desc limit ALL offset $1';
		FOR row1 IN EXECUTE q USING p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	else
		q := 'select * from dtw.job ' || where_clause || ' order by entered desc limit $1 offset $2';
		FOR row1 IN EXECUTE q USING p_limit, p_offset
			LOOP
				return next row1;
			END LOOP;
		RETURN;
	end if;

END;
$PROC$ LANGUAGE plpgsql;

/*
 * This will delete all employer records from dtw.emp
 */
CREATE OR REPLACE FUNCTION dtw.Delete_all_job ()
RETURNS void as $PROC$
BEGIN
	delete from dtw.job;
END;
$PROC$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dtw.Delete_job_by_id (
	p_jid		int
)
RETURNS void as $PROC$
BEGIN
	delete from dtw.job where jid = p_jid;
END;
$PROC$ LANGUAGE plpgsql;


/* -----------------------------------------------------------------------
 * Workflows tables
 * -----------------------------------------------------------------------
 */

/* Status */
drop domain if exists dtw.node_type_domain cascade;
create domain dtw.node_type_domain CHAR(1)
	check (value in (
		'S', 	/* Start node */
		'E', 	/* END node */
		'A',	/* Activity node */
		'F',	/* Fork node */
		'J' 	/* Joiner node */
));

create sequence dtw.workflow_seq start with 1 no cycle;
DROP TABLE IF EXISTS dtw.workflow;
create table dtw.workflow (
	wfid			int PRIMARY KEY DEFAULT nextval('dtw.workflow_seq'),
	name			varchar(64) UNIQUE,
	info			text
);

create sequence dtw.node_seq start with 1 no cycle;
DROP TABLE IF EXISTS dtw.node;
create table dtw.node (
	nid			int PRIMARY KEY DEFAULT nextval('dtw.node_seq'),
	wfid			int references dtw.workflow(wfid) on delete cascade,
	sname			char(3),
	name			varchar(64),
	ntype			dtw.node_type_domain
);

create sequence dtw.edge_seq start with 1 no cycle;
DROP TABLE IF EXISTS dtw.edge;
create table dtw.edge (
	eid			int PRIMARY KEY DEFAULT nextval('dtw.edge_seq'),
	node1			int references dtw.node(nid) on delete cascade,
	node2			int references dtw.node(nid) on delete cascade,
	guard			varchar(64)
);

/* -----------------------------------------------------------------------
 * Workflows
 * -----------------------------------------------------------------------
 */

/*
 *  Create_workflow
 */
CREATE OR REPLACE FUNCTION dtw.Create_workflow (
	p_name	 		varchar(64),
	p_info			text
	)
RETURNS int AS $PROC$

DECLARE
	row1 	RECORD;
	row2 	RECORD;

BEGIN
	select into row1 * from dtw.workflow where p_name=name;
	IF FOUND THEN
		RETURN 0;
	END IF;

	INSERT INTO dtw.workflow (
		name, info)
	VALUES (
		p_name, p_info);

	select into row2 * from dtw.workflow where p_name=name;
	IF NOT FOUND THEN
		RETURN 0;
	END IF;

	INSERT INTO dtw.node (wfid, sname, name, ntype)
		VALUES (row2.wfid, '__S', '__Start_node','S');

	INSERT INTO dtw.node (wfid, sname, name, ntype)
		VALUES (row2.wfid, '__F', '__Finish_node','F');

	RETURN 1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 *  Delete workflow
 */
CREATE OR REPLACE FUNCTION dtw.Delete_workflow (
	p_name	 		varchar(64)
	)
RETURNS void AS $PROC$
BEGIN
	delete from dtw.workflow where name = p_name;
END;
$PROC$ LANGUAGE plpgsql;


/*
 * This function returns a list of all the workflows
 */
CREATE OR REPLACE FUNCTION dtw.Get_workflows ()
RETURNS SETOF dtw.workflow AS $PROC$

DECLARE
	row1		dtw.workflow%ROWTYPE;

BEGIN
	FOR row1 IN
		SELECT * FROM dtw.workflow
		LOOP
			RETURN NEXT row1;
		END LOOP;
	RETURN;
END;
$PROC$ LANGUAGE plpgsql;

/* -----------------------------------------------------------------------
 * Nodes
 * -----------------------------------------------------------------------
 */

/*
 *  This function adds a node, identified by a short name, to an existing workflow.
 *  Returns 1 if the node is added or is already in the the workflow. Returns 0
 *  if the workflow is not found.
 */
CREATE OR REPLACE FUNCTION dtw.Add_node (
	p_workflow_name	 		varchar(64),
	p_sname					char (3),
	p_name					varchar(64),
	p_ntype					dtw.node_type_domain
	)
RETURNS int AS $PROC$

DECLARE
	row1 RECORD;
	row2 RECORD;

BEGIN
	select into row1 * from dtw.workflow where p_workflow_name=name;
	IF NOT FOUND THEN
		RETURN 0;
	END IF;

	select into row2 * from dtw.node where wfid=row1.wfid AND p_sname=sname;
	IF FOUND THEN
		RETURN 1;
	END IF;

	INSERT INTO dtw.node (
		wfid, sname, name, ntype)
	VALUES (
		row1.wfid, p_sname, p_name, p_ntype);
	RETURN 1;
END;
$PROC$ LANGUAGE plpgsql;


/*
 *	Return a node record by the node's short name.
 */
CREATE OR REPLACE FUNCTION dtw.Get_node (
	p_workflow_name	 		varchar(64),
	p_sname					char (3)
	)
RETURNS RECORD AS $PROC$

DECLARE
	row1 RECORD;

BEGIN
	select into row1 * from dtw.node N, dtw.workflow W
		where 	W.name = p_workflow_name
		AND		N.wif	= W.wid
		AND		N.sname = p_sname;
	RETURN row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 *	Return the node record by the node's id.
 */
CREATE OR REPLACE FUNCTION dtw.Get_node_by_id (
	p_nid					int
)
RETURNS RECORD AS $PROC$

DECLARE
	row1 RECORD;

BEGIN
	select into row1 * from dtw.node where nid = p_nid;
	RETURN row1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This function returns a list of all nodes for a workflow
 */
CREATE OR REPLACE FUNCTION dtw.Get_nodes (
	p_workflow_name			varchar(64)
)
RETURNS SETOF dtw.node AS $PROC$

DECLARE
	row1		dtw.node%ROWTYPE;

BEGIN
	FOR row1 IN
		SELECT N.nid, N.wfid, N.sname, N.name, N.ntype
		FROM dtw.node N, dtw.workflow W
		WHERE W.name = p_workflow_name AND W.wfid = N.wfid
		LOOP
			RETURN NEXT row1;
		END LOOP;
	RETURN;
END;
$PROC$ LANGUAGE plpgsql;

/* -----------------------------------------------------------------------
 * Edges
 * -----------------------------------------------------------------------
 */
/*
 *  Connects the start node to a node
 */
CREATE OR REPLACE FUNCTION dtw.Link_from_start (
	p_workflow_name	 		varchar(64),
	p_sname2				char(3),
	p_guard					varchar(64)
	)
RETURNS int AS $PROC$
BEGIN
	RETURN dtw.Link_between(p_workflow_name, '__S', p_sname2, p_guard);
END;
$PROC$ LANGUAGE plpgsql;

/*
 *  Connects a node to to the finish node
 */
CREATE OR REPLACE FUNCTION dtw.Link_to_finish (
	p_workflow_name	 		varchar(64),
	p_sname1				char(3),
	p_guard					varchar(64)
	)
RETURNS int AS $PROC$
BEGIN
	RETURN dtw.Link_between(p_workflow_name, p_sname1, '__F', p_guard);
END;
$PROC$ LANGUAGE plpgsql;

/*
 *  This function adds an edge between two existing nodes in a workflow.
 */
CREATE OR REPLACE FUNCTION dtw.Link_between (
	p_workflow_name	 		varchar(64),
	p_sname1				char(3),
	p_sname2				char(3),
	p_guard					varchar(64)
	)
RETURNS int AS $PROC$

DECLARE
	row0 RECORD;
	row1 RECORD;
	row2 RECORD;

BEGIN
	select into row0 * from dtw.workflow where p_workflow_name=name;
	IF NOT FOUND THEN
		RETURN 0;
	END IF;

	select into row1 * from dtw.node where sname=p_sname1;
	IF NOT FOUND THEN
		RETURN 0;
	END IF;

	select into row2 * from dtw.node where sname=p_sname2;
	IF NOT FOUND THEN
		RETURN 0;
	END IF;

	select into row0 * from dtw.edge where node1=row1.nid and node2=row2.nid;
	IF FOUND THEN
		RETURN 1;
	END IF;

	INSERT INTO dtw.edge (
		node1, node2, guard)
	VALUES (
		row1.nid, row2.nid, p_guard);

	RETURN 1;
END;
$PROC$ LANGUAGE plpgsql;

/*
 * This table is used ONLY to send data about edges back to the client
 */
DROP TABLE IF EXISTS dtw.edge_info_rec;
create table dtw.edge_info_rec (
	eid			int, 					/* Edge id */
	node1		int, 					/* moderator node id */
	node2		int, 					/* Child node id */
	guard		varchar(64), 			/* The lable of the edge */
	sname1		char(3), 				/* The short name for the moderator node */
	sname2		char(3), 				/* The short name for the child node */
	name2		varchar(64), 			/* The full name of the child node */
	ntype2		dtw.node_type_domain	/* The node type for the child node */
);

/*
 * This function returns a set of children for a given node.  The node children
 * records include the both edge information and children node information. See
 * the structure dtw.edge_info_rec.
 */
CREATE OR REPLACE FUNCTION dtw.Get_children (
	p_workflow_name	 		varchar(64),
	p_sname					char(3)
	)
RETURNS SETOF dtw.edge_info_rec AS $PROC$

DECLARE
	row1		RECORD;
	row2		dtw.node%ROWTYPE;
	row3		dtw.edge%ROWTYPE;
	row4		dtw.edge_info_rec%ROWTYPE;

	t_node1			int;
	t_sname1		char(3);
	t_sname2		char(3);
	t_name2			varchar(64);
	t_ntype2		dtw.node_type_domain;

BEGIN
	select into row1 * from dtw.workflow where name=p_workflow_name;
	IF NOT FOUND THEN
		RETURN;
	END IF;

	select into row2 * from dtw.node where sname = p_sname;
	IF NOT FOUND THEN
		RETURN;
	END IF;

	FOR row3 IN
		SELECT E.eid as eid, E.node1 as n1, E.node2 as n2, E.guard as guard
		FROM dtw.edge E
		WHERE E.node1 = row2.nid
		LOOP
			select into t_sname1 	sname from dtw.node where nid = row3.node1;
			select into t_sname2 	sname from dtw.node where nid = row3.node2;
			select into t_name2 	name from dtw.node where nid = row3.node2;
			select into t_ntype2 	ntype from dtw.node where nid = row3.node2;

			row4.eid		:= row3.eid;
			row4.node1		:= row3.node1;
			row4.node2		:= row3.node2;
			row4.guard		:= row3.guard;
			row4.sname1		:= t_sname1;
			row4.sname2		:= t_sname2;
			row4.name2		:= t_name2;
			row4.ntype2		:= t_ntype2;
			RETURN NEXT row4;
		END LOOP;
	RETURN;
END;
$PROC$ LANGUAGE plpgsql;



#EXPOSED
#BY DDJ HAXING GROUP
#WE ARE ANONYMOUS
#FUCKOFF
#USA
#LEAK
#DATABASE

SPREAD THIS ..