· 8 years ago · Mar 17, 2018, 01:28 PM
1/* filename: mk_job_db.sql */
2
3/*
4 *
5 *
6 */
7
8 /* -----------------------------------------------------------------------
9 * Summary
10 *
11 *
12 * youth
13 * ------
14 * New_youth (p_fname varchar(64), p_mname varchar(64), p_lname varchar(64))
15 * Update_youth (p_eid int, p_field varchar(64), p_value anyelement)
16 * Get_youth_by_id (p_id int)
17 * Get_youth_by_short (p_short text)
18 * Get_youth_by_email (p_email text)
19 * Get_youth_by_username (p_username text)
20 * Get_youth_by_name (p_begin char, p_offset int, p_limit int)
21 * Get_youth_check_password (p_user_name, p_user_password)
22 * Get_recent_youth (p_offset int, p_max int)
23 * Get_all_youth (p_offset int, p_max int)
24 * Create_test_youth (p_number)
25 * Delete_all_youth ()
26 * Delete_youth_by_id (p_id int)
27 *
28 * Register_youth (p_user varchar(64), p_pass varchar(64), p_sim_name varchar(64), p_email varchar(64), p_cell varchar(64))
29 * TODO: Check_youth_initials (p_short char(4))
30 *
31 * moderator
32 * ------
33 * New_moderator (p_fname varchar(64), p_mname varchar(64), p_lname varchar(64))
34 * Update_moderator (p_eid int, p_field varchar(64), p_value anyelement)
35 * Get_all_moderator (p_offset int, p_limit int)
36 *
37 * TODO: Check_moderator_initials (p_short char(4))
38 * Update the last update fields in moderator, ect. ..
39 *
40 * Doc
41 * ---
42 * New_doc (p_sid int, p_tid int, p_doc text, p_doc_type varchar(16))
43 * New_doc_by_inititals (p_sshort char(4), p_tshort char(4), p_doc text, doc_type dtw.doc_type_type)
44 * Find_docs (p_doc_type dtw.doc_type_type, p_offset int, p_limit int)
45 *
46 * Employer
47 * --------
48 * New_employer (p_contact_fname text, p_contact_lname text, p_contact_email text)
49 * Update_employer (p_eid int, p_field varchar(64), p_value anyelement)
50 * Get_emp_by_id (p_eid int)
51 * Get_all_empoloyer (p_offset int, p_limit int)
52 * Get_all_emp_by_contact (p_offset int, p_limit int)
53 *
54 * Job
55 * ---
56 * New_job (p_eid, p_title)
57 * Update_job (p_eid int, p_field varchar(64), p_value anyelement)
58 * Find_jobs (p_status job_status_type, p_offset int, p_limit int)
59 *
60 * Prop
61 * ----
62 * New_prop (p_sid int, p_tid int, p_name text, p_val anyelement, p_switch char(1))
63 *
64 Workflow
65 * --------
66 * Create_workflow ()
67 * Used to create an empty workflow object
68 * Delete_workflow ()
69 * Used to delete an empty workflow object
70 * Get_workflows ()
71 * Used to get the list of workflows
72 *
73 * Node
74 * ----
75 * Add_node ()
76 * Used to add a new node to a workflow
77 * Get_nodes ()
78 * Used to get a list of all nodes for a workflow
79 * Get_node ()
80 * Used to get information about a node by the node short name
81 * Get_node_by_id ()
82 * Used to get information about a node by the node id
83 *
84 * Edges
85 * -----
86 * Link_between ()
87 * Used to add an edge between two nodes
88 * Link_from_start ()
89 * Used to link from the special start node to the a node
90 * Link_to_finish ()
91 * Used to link from a node to a special finish node
92 * Get_children ()
93 * Used to get information on the children of a node
94 *
95 * Exmaples
96 * --------
97 select dtw.Create_workflow('work', 'Simple test of the workflow module');
98 select dtw.Add_node('work', 'A', 'Document submitted', 'A');
99 select dtw.Add_node('work', 'B', 'Document being reviewed', 'A');
100 select dtw.Add_node('work', 'C', 'Document aapproved', 'A');
101 select dtw.Link_from_start('work','A', '');
102 select dtw.Link_between('work','A', 'B', 'Need to assign document');
103 select dtw.Link_between('work','B','C','Need to approve document');
104 select dtw.Link_to_finish('work','C', '');
105
106 select dtw.Get_children('work','A');
107
108 select dtw.Get_node_by_id(233);
109 select dtw.Get_node('A');
110
111 select dtw.Drop_workflow('work');
112
113 * -----------------------------------------------------------------------
114 */
115
116/* CREATE OR REPLACE LANGUAGE plpgsql; */
117
118
119DROP SCHEMA IF EXISTS dtw CASCADE;
120create schema dtw;
121
122/* Gender */
123drop domain if exists dtw.gender_type cascade;
124create domain dtw.gender_type CHAR(1)
125 check (value in (
126 'M', /* Male */
127 'F' /* Female */
128));
129
130/* Status */
131drop domain if exists dtw.status_type cascade;
132create domain dtw.status_type CHAR(1)
133 check (value in (
134 'A', /* Active enrolled */
135 'B', /* Active engaged */
136 'C', /* Building */
137 'D', /* Living in doors */
138 'E', /* On their journey */
139 'F', /* Diseased */
140 'G', /* Incarated */
141 'Z' /* Unknown */
142));
143
144/* Ethnicity */
145drop domain if exists dtw.ethnicity_type cascade;
146create domain dtw.ethnicity_type CHAR(1)
147 check (value in (
148 'A', /* US indian/Alaskan Native */
149 'B', /* White */
150 'C', /* Asian */
151 'D', /* Black/African American */
152 'E', /* Hawaiian/Pacific Islander */
153 'F', /* Don't know */
154 'G', /* Refused */
155 'Z' /* Not set */
156));
157
158/* Hispanic */
159drop domain if exists dtw.hispanic_type cascade;
160create domain dtw.hispanic_type CHAR(1)
161 check (value in (
162 'A', /* Hispanic */
163 'B', /* Non-Hispanic */
164 'Z' /* Not set */
165));
166
167/* -----------------------------------------------------------------------
168 * Data model tables
169 * -----------------------------------------------------------------------
170 */
171
172 /* -----------------------------------------------------------------------
173 * youth tables and functions
174 * -----------------------------------------------------------------------
175 */
176create sequence dtw.person_seq start with 100 no cycle;
177
178
179DROP TABLE IF EXISTS dtw.youth;
180create table dtw.youth (
181 tid int PRIMARY KEY DEFAULT nextval('dtw.person_seq'),
182
183 initials char(4) UNIQUE,
184
185 username text UNIQUE,
186 password text,
187
188/* Identity */
189 sim_name varchar(64), /* simple name -- first and last */
190 fname varchar(64), /* first */
191 mname varchar(64), /* middle */
192 lname varchar(64), /* last */
193 snames varchar(64), /* list of street names */
194 dob date, /* birth date */
195
196 gender dtw.gender_type,
197 transgender boolean,
198
199 ethnicity char(8), /* zero or more indicators */
200 hispanic dtw.hispanic_type,
201
202 status dtw.status_type, /* current status */
203 last_signin timestamp with time zone, /* last sign in */
204
205 id_checked boolean,
206 id_checked_date date,
207
208/* Images and image captions */
209 image01 text,
210 image02 text,
211 image03 text,
212
213 image01_cap text,
214 image02_cap text,
215 image03_cap text,
216
217/* Application data */
218 notes text, /* open-end notes */
219 data01 text, /* Available */
220 data02 text, /* Available */
221 data03 text,
222
223/* Contact */
224 cell varchar(16),
225 email varchar(64),
226 facebook varchar(64),
227 twitter varchar(64),
228 webpage varchar(64),
229
230 address01 text,
231 address02 text,
232 city varchar(64),
233 state char(2),
234 zip varchar(16),
235
236 entered timestamp with time zone, /* the date that this tuple was first created */
237 last_update timestamp with time zone /* the last date that this tuple was updated */
238);
239
240/*
241 * This function creates a new youth
242 */
243CREATE OR REPLACE FUNCTION dtw.Register_youth (
244 p_user varchar(64),
245 p_pass varchar(64),
246 p_sim_name varchar(64),
247 p_email varchar(64),
248 p_cell varchar(64)
249)
250RETURNS int AS $PROC$
251DECLARE
252 t int;
253 f int;
254BEGIN
255 select * from dtw.is_username_okay(p_user) into f;
256 if (f = 0 ) THEN
257 t := 0;
258 else
259 INSERT INTO dtw.youth (
260 username,password,sim_name,email,cell, entered,last_update)
261 VALUES (
262 p_user, p_pass, p_sim_name, p_email, p_cell, now(), now())
263 RETURNING tid into t;
264 end if;
265 return t;
266END;
267$PROC$ LANGUAGE plpgsql;
268
269/*
270 * This function creates a new youth
271 */
272CREATE OR REPLACE FUNCTION dtw.New_youth (
273 p_fname varchar(64),
274 p_mname varchar(64),
275 p_lname varchar(64)
276)
277RETURNS int AS $PROC$
278DECLARE
279 t int;
280BEGIN
281 INSERT INTO dtw.youth (
282 fname,mname,lname,entered,last_update)
283 VALUES (
284 p_fname, p_mname, p_lname, now(), now())
285 RETURNING tid into t;
286 return t;
287END;
288$PROC$ LANGUAGE plpgsql;
289
290
291/*
292 * This function updates the contents of a youth's field
293 */
294CREATE OR REPLACE FUNCTION dtw.Update_youth (
295 p_tid int,
296 p_field varchar(64),
297 p_value anyelement
298 )
299RETURNS int AS $PROC$
300DECLARE
301 q text;
302 row1 dtw.youth%ROWTYPE;
303BEGIN
304 if p_field = 'initials' then
305 select into row1 * from dtw.youth where initials = p_value;
306 if FOUND then
307 return -1;
308 end if;
309 elsif p_field = 'username' then
310 select into row1 * from dtw.youth where username = p_value;
311 if FOUND then
312 return -1;
313 end if;
314 end if;
315
316 q := format('update dtw.youth SET %I = $1 WHERE tid = $2;', p_field);
317 EXECUTE q USING p_value, p_tid;
318 return p_tid;
319END;
320$PROC$ LANGUAGE plpgsql;
321
322/*
323 * This function returns a youth by its id
324 */
325CREATE OR REPLACE FUNCTION dtw.Get_youth_by_id (
326 p_tid int
327 )
328RETURNS dtw.youth as $PROC$
329DECLARE
330 row1 dtw.youth%ROWTYPE;
331BEGIN
332 select into row1 * from dtw.youth where tid = p_tid;
333 return row1;
334END;
335$PROC$ LANGUAGE plpgsql;
336
337/*
338 * This function returns a youth by its short name (aka initials)
339 */
340CREATE OR REPLACE FUNCTION dtw.Get_youth_by_short (
341 p_short text
342 )
343RETURNS dtw.youth as $PROC$
344DECLARE
345 row1 dtw.youth%ROWTYPE;
346BEGIN
347 select into row1 * from dtw.youth where initials = p_short;
348 return row1;
349END;
350$PROC$ LANGUAGE plpgsql;
351
352/*
353 * This function returns a youth by its email
354 */
355CREATE OR REPLACE FUNCTION dtw.Get_youth_by_email (
356 p_email text
357)
358RETURNS dtw.youth as $PROC$
359DECLARE
360 row1 dtw.youth%ROWTYPE;
361BEGIN
362 select into row1 * from dtw.youth where email = p_email;
363 return row1;
364END;
365$PROC$ LANGUAGE plpgsql;
366
367/*
368 * This function returns a youth record by user name
369 */
370CREATE OR REPLACE FUNCTION dtw.Get_youth_by_username (
371 p_username text
372 )
373RETURNS dtw.youth as $PROC$
374DECLARE
375 row1 dtw.youth%ROWTYPE;
376BEGIN
377 select into row1 * from dtw.youth where username = p_username;
378 return row1;
379END;
380$PROC$ LANGUAGE plpgsql;
381
382/*
383 * Returns TRUE if user name is okay
384 */
385CREATE OR REPLACE FUNCTION dtw.is_username_okay (
386 p_username text
387 )
388RETURNS int as $PROC$
389DECLARE
390 row1 dtw.youth%ROWTYPE;
391 f1 boolean;
392 f2 boolean;
393 f3 boolean;
394BEGIN
395 select into row1 * from dtw.youth where username = p_username;
396 if NOT FOUND THEN
397 f1 := true;
398 END IF;
399
400 select into row1 * from dtw.moderator where username = p_username;
401 IF NOT FOUND THEN
402 f2 := true;
403 END IF;
404
405 select into row1 * from dtw.emp where username = p_username;
406 IF NOT FOUND THEN
407 f3 := true;
408 END IF;
409
410 if (f1 = true and f2 = true and f3 = true) then
411 return 1;
412 else
413 return 0;
414 end if;
415END;
416$PROC$ LANGUAGE plpgsql;
417
418/*
419 * Returns the internal ID if the user id and password match.
420 *
421 * The User ID is not found: return -2
422 * The password does not match: return -1
423 * The password does match: return the id.
424 *
425 */
426CREATE OR REPLACE FUNCTION dtw.Get_youth_check_password (
427 p_username text,
428 p_password text
429 )
430RETURNS integer as $PROC$
431DECLARE
432 row1 dtw.youth%ROWTYPE;
433BEGIN
434 select into row1 * from dtw.youth where username = p_username;
435 IF NOT FOUND THEN
436 RETURN -2;
437 END IF;
438
439 select into row1 * from dtw.youth where username = p_username AND password = p_password;
440 IF NOT FOUND THEN
441 RETURN -1;
442 END IF;
443
444 return row1.tid;
445END;
446$PROC$ LANGUAGE plpgsql;
447
448
449/*
450 * This function returns a list youths by the most recently added
451 */
452CREATE OR REPLACE FUNCTION dtw.Get_recent_youth (
453 p_offset int,
454 p_max int
455)
456RETURNS SETOF dtw.youth as $PROC$
457DECLARE
458 row1 dtw.youth%ROWTYPE;
459 q text;
460BEGIN
461 if p_max = -1 then
462 q := 'select * from dtw.youth order by last_update desc limit ALL offset $1';
463 FOR row1 IN EXECUTE q USING p_offset
464 LOOP
465 return next row1;
466 END LOOP;
467 RETURN;
468 else
469 q := 'select * from dtw.youth order by last_update desc limit $1 offset $2';
470 FOR row1 IN EXECUTE q USING p_max, p_offset
471 LOOP
472 return next row1;
473 END LOOP;
474 RETURN;
475 end if;
476END;
477$PROC$ LANGUAGE plpgsql;
478
479/*
480 * Returns a list of youths by starting letters of first or last name
481 */
482CREATE OR REPLACE FUNCTION dtw.Get_youth_by_name (
483 p_begin char,
484 p_offset int,
485 p_limit int
486 )
487RETURNS SETOF dtw.youth as $PROC$
488DECLARE
489 row1 dtw.youth%ROWTYPE;
490 q text;
491BEGIN
492 if p_limit = -1 then
493 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);
494 FOR row1 IN EXECUTE q USING p_offset
495 LOOP
496 return next row1;
497 END LOOP;
498 RETURN;
499 else
500 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);
501 FOR row1 IN EXECUTE q USING p_limit, p_offset
502 LOOP
503 return next row1;
504 END LOOP;
505 RETURN;
506 end if;
507END;
508$PROC$ LANGUAGE plpgsql;
509
510/*
511 * Returns a list of youths
512 */
513CREATE OR REPLACE FUNCTION dtw.Get_all_youth (
514 p_offset int,
515 p_limit int
516 )
517RETURNS SETOF dtw.youth as $PROC$
518DECLARE
519 row1 dtw.youth%ROWTYPE;
520 q text;
521BEGIN
522 if p_limit = -1 then
523 q := 'select * from dtw.youth order by lname limit ALL offset $1';
524 FOR row1 IN EXECUTE q USING p_offset
525 LOOP
526 return next row1;
527 END LOOP;
528 RETURN;
529 else
530 q := 'select * from dtw.youth order by lname limit $1 offset $2';
531 FOR row1 IN EXECUTE q USING p_limit, p_offset
532 LOOP
533 return next row1;
534 END LOOP;
535 RETURN;
536 end if;
537END;
538$PROC$ LANGUAGE plpgsql;
539
540/*
541 * This will delete all youth records from dtw.youth
542 */
543CREATE OR REPLACE FUNCTION dtw.Delete_all_youth ()
544RETURNS void as $PROC$
545BEGIN
546 delete from dtw.youth;
547END;
548$PROC$ LANGUAGE plpgsql;
549
550/*
551 * This will delete all youth records from dtw.youth
552 */
553CREATE OR REPLACE FUNCTION dtw.Delete_youth_by_id (
554 p_id int
555)
556RETURNS void as $PROC$
557BEGIN
558 delete from dtw.youth Y where Y.tid = p_id;
559END;
560$PROC$ LANGUAGE plpgsql;
561
562
563/*
564 * This will insert a set of youth into the system -- useful for testing
565 */
566CREATE OR REPLACE FUNCTION dtw.Create_test_youth (
567 p_num int
568)
569RETURNS void as $PROC$
570DECLARE
571 t int;
572 k int;
573BEGIN
574 k := 1;
575 LOOP
576 select * into t from dtw.New_youth('Bob' || k, '', 'Lock' || k);
577 k := k +1;
578 IF k > (p_num-1) THEN
579 EXIT;
580 END IF;
581 END LOOP;
582END;
583$PROC$ LANGUAGE plpgsql;
584
585
586/* -----------------------------------------------------------------------
587 * moderator functions
588 * -----------------------------------------------------------------------
589 */
590
591DROP TABLE IF EXISTS dtw.moderator;
592create table dtw.moderator (
593 sid int PRIMARY KEY DEFAULT nextval('dtw.person_seq'),
594
595 initials char(4) UNIQUE,
596
597 username text UNIQUE,
598 password text,
599
600/* Identity */
601 fname varchar(64),
602 mname varchar(64),
603 lname varchar(64),
604
605 dob date,
606 gender dtw.gender_type,
607
608/* Images and image captions */
609 image01 text,
610 image02 text,
611 image03 text,
612
613 image01_cap text,
614 image02_cap text,
615 image03_cap text,
616
617/* Application data */
618 notes text, /* open-end notes */
619 data01 text, /* Available */
620 data02 text, /* Available */
621 data03 text,
622
623 cell varchar(16),
624 email varchar(64),
625 facebook varchar(64),
626 twitter varchar(64),
627 webpage varchar(64),
628
629 entered timestamp with time zone, /* the date that this tuple was first created */
630 last_update timestamp with time zone /* the last date that this tuple was updated */
631);
632
633/*
634 * This function creates a new moderator, returning the moderator id, sid
635 */
636CREATE OR REPLACE FUNCTION dtw.New_moderator (
637 p_fname varchar(64),
638 p_mname varchar(64),
639 p_lname varchar(64)
640 )
641RETURNS int AS $PROC$
642DECLARE
643 t int;
644BEGIN
645 INSERT INTO dtw.moderator (
646 fname,mname,lname,entered,last_update)
647 VALUES (
648 p_fname, p_mname, p_lname, now(), now())
649 RETURNING sid into t;
650 return t;
651END;
652$PROC$ LANGUAGE plpgsql;
653
654/*
655 * This function updates the contents of a moderators field
656 */
657CREATE OR REPLACE FUNCTION dtw.Update_moderator (
658 p_sid int,
659 p_field varchar(64),
660 p_value anyelement
661 )
662RETURNS int AS $PROC$
663DECLARE
664 q text;
665 row1 dtw.moderator%ROWTYPE;
666BEGIN
667 if p_field = 'initials' then
668 select into row1 * from dtw.moderator where initials = p_value;
669 if FOUND then
670 return -1;
671 end if;
672 elsif p_field = 'username' then
673 select into row1 * from dtw.moderator where username = p_value;
674 if FOUND then
675 return -1;
676 end if;
677 end if;
678
679 q := format('update dtw.moderator SET %I = $1 WHERE sid = $2', p_field);
680 EXECUTE q USING p_value, p_sid;
681 return p_sid;
682END;
683$PROC$ LANGUAGE plpgsql;
684
685/*
686 * This function returns a moderator by its id
687 */
688CREATE OR REPLACE FUNCTION dtw.Get_moderator_by_id (
689 p_sid int
690 )
691RETURNS dtw.moderator as $PROC$
692DECLARE
693 row1 dtw.moderator%ROWTYPE;
694BEGIN
695 select into row1 * from dtw.moderator where sid = p_sid;
696 return row1;
697END;
698$PROC$ LANGUAGE plpgsql;
699
700/*
701 * This function returns a moderator by its short name (aka initials)
702 */
703CREATE OR REPLACE FUNCTION dtw.Get_moderator_by_short (
704 p_short text
705 )
706RETURNS dtw.moderator as $PROC$
707DECLARE
708 row1 dtw.moderator%ROWTYPE;
709BEGIN
710 select into row1 * from dtw.moderator where initials = p_short;
711 return row1;
712END;
713$PROC$ LANGUAGE plpgsql;
714
715/*
716 * This function returns a moderator record by its email
717 */
718CREATE OR REPLACE FUNCTION dtw.Get_moderator_by_email (
719 p_email text
720)
721RETURNS dtw.moderator as $PROC$
722DECLARE
723 row1 dtw.moderator%ROWTYPE;
724BEGIN
725 select into row1 * from dtw.moderator where email = p_email;
726 return row1;
727END;
728$PROC$ LANGUAGE plpgsql;
729
730/*
731 * This function returns a moderator record by user name
732 */
733CREATE OR REPLACE FUNCTION dtw.Get_moderator_by_username (
734 p_username text
735 )
736RETURNS dtw.moderator as $PROC$
737DECLARE
738 row1 dtw.moderator%ROWTYPE;
739BEGIN
740 select into row1 * from dtw.moderator where username = p_username;
741 return row1;
742END;
743$PROC$ LANGUAGE plpgsql;
744
745/*
746 * Returns the internal ID if the user id and password match.
747 * The User ID is not found: return -2
748 * The password does not match: return -1
749 * The password does match: return the id
750 */
751CREATE OR REPLACE FUNCTION dtw.Get_moderator_check_password (
752 p_username text,
753 p_password text
754 )
755RETURNS integer as $PROC$
756DECLARE
757 row1 dtw.moderator%ROWTYPE;
758BEGIN
759 select into row1 * from dtw.moderator where username = p_username;
760 IF NOT FOUND THEN
761 RETURN -2;
762 END IF;
763
764 select into row1 * from dtw.moderator where username = p_username AND password = p_password;
765 IF NOT FOUND THEN
766 RETURN -1;
767 END IF;
768 return row1.sid;
769END;
770$PROC$ LANGUAGE plpgsql;
771
772/*
773 * Returns a list of moderators by starting letters of first or last name
774 */
775CREATE OR REPLACE FUNCTION dtw.Get_all_moderator (
776 p_offset int,
777 p_limit int
778 )
779RETURNS SETOF dtw.moderator as $PROC$
780DECLARE
781 row1 dtw.moderator%ROWTYPE;
782 q text;
783BEGIN
784 if p_limit = -1 then
785 q := 'select * from dtw.moderator order by lname limit ALL offset $1';
786 FOR row1 IN EXECUTE q USING p_offset
787 LOOP
788 return next row1;
789 END LOOP;
790 RETURN;
791 else
792 q := 'select * from dtw.moderator order by lname limit $1 offset $2';
793 FOR row1 IN EXECUTE q USING p_limit, p_offset
794 LOOP
795 return next row1;
796 END LOOP;
797 RETURN;
798 end if;
799END;
800$PROC$ LANGUAGE plpgsql;
801
802/*
803* Returns a list of youths by starting letters of first or last name
804 */
805CREATE OR REPLACE FUNCTION dtw.Get_all_moderator (
806 p_offset int,
807 p_limit int
808 )
809RETURNS SETOF dtw.moderator as $PROC$
810DECLARE
811 row1 dtw.moderator%ROWTYPE;
812 q text;
813BEGIN
814 if p_limit = -1 then
815 q := 'select * from dtw.moderator order by lname limit ALL offset $1';
816 FOR row1 IN EXECUTE q USING p_offset
817 LOOP
818 return next row1;
819 END LOOP;
820 RETURN;
821 else
822 q := 'select * from dtw.moderator order by lname limit $1 offset $2';
823 FOR row1 IN EXECUTE q USING p_limit, p_offset
824 LOOP
825 return next row1;
826 END LOOP;
827 RETURN;
828 end if;
829END;
830$PROC$ LANGUAGE plpgsql;
831
832/*
833 * This will delete all moderators records from dtw.moderator
834 */
835CREATE OR REPLACE FUNCTION dtw.Delete_all_moderator ()
836RETURNS void as $PROC$
837BEGIN
838 delete from dtw.moderator;
839END;
840$PROC$ LANGUAGE plpgsql;
841
842CREATE OR REPLACE FUNCTION dtw.Delete_moderator_by_id (
843 p_sid int
844)
845RETURNS void as $PROC$
846BEGIN
847 delete from dtw.moderator where sid = p_sid;
848END;
849$PROC$ LANGUAGE plpgsql;
850
851CREATE OR REPLACE FUNCTION dtw.Delete_moderator_by_username (
852 p_username text
853)
854RETURNS void as $PROC$
855BEGIN
856 delete from dtw.moderator where username = p_username;
857END;
858$PROC$ LANGUAGE plpgsql;
859
860/* -----------------------------------------------------------------------
861 * Employer ...
862 * -----------------------------------------------------------------------
863 */
864DROP TABLE IF EXISTS dtw.emp;
865create table dtw.emp (
866 eid int PRIMARY KEY DEFAULT nextval('dtw.person_seq'),
867 contact_fname text,
868 contact_lname text,
869 contact_email text,
870 contact_tel_number text,
871 business text,
872 location text, /* Informal description of location */
873 neighborhood text, /* The neighborhood in which the job is located */
874 mailing_address text, /* The formal mailing address, including city and zip */
875 city text,
876 zip text,
877 map_location text, /* The location of the employer on a map */
878 username text UNIQUE,
879 password text,
880 webpage text,
881
882/* Images and image captions */
883 image01 text,
884 image02 text,
885 image03 text,
886
887 image01_cap text,
888 image02_cap text,
889 image03_cap text,
890
891/* Dates */
892 entered timestamp with time zone, /* the date that this tuple was first created */
893 last_update timestamp with time zone /* the last date that this tuple was updated */
894);
895
896/*
897 * Xxx
898 */
899CREATE OR REPLACE FUNCTION dtw.New_employer (
900 p_contact_fname text,
901 p_contact_lname text,
902 p_contact_email text
903 )
904RETURNS int AS $PROC$
905DECLARE
906 t int;
907BEGIN
908 INSERT INTO dtw.emp (
909 contact_fname, contact_lname, contact_email, entered,last_update)
910 VALUES (
911 p_contact_fname, p_contact_fname, p_contact_email, now(), now())
912 RETURNING eid into t;
913 return t;
914END;
915$PROC$ LANGUAGE plpgsql;
916
917/*
918 * xxx
919 */
920CREATE OR REPLACE FUNCTION dtw.Update_employer (
921 p_eid int,
922 p_field varchar(64),
923 p_value anyelement
924 )
925RETURNS int AS $PROC$
926DECLARE
927 q text;
928 row1 dtw.emp%ROWTYPE;
929BEGIN
930 if p_field = 'username' then
931 select into row1 * from dtw.emp where username = p_value;
932 if FOUND then
933 return -1;
934 end if;
935 end if;
936
937 q := format('update dtw.emp SET %I = $1 WHERE eid = $2', p_field);
938 EXECUTE q USING p_value, p_eid;
939 return p_eid;
940END;
941$PROC$ LANGUAGE plpgsql;
942
943/*
944 * Returns an employee record based on the ID
945 */
946CREATE OR REPLACE FUNCTION dtw.Get_emp_by_id (
947 p_eid int
948 )
949RETURNS dtw.emp as $PROC$
950DECLARE
951 row1 dtw.emp%ROWTYPE;
952BEGIN
953 select into row1 * from dtw.emp where eid = p_eid;
954 return row1;
955END;
956$PROC$ LANGUAGE plpgsql;
957
958/*
959 * Returns an employee record based on the USER_ID
960 */
961CREATE OR REPLACE FUNCTION dtw.Get_emp_by_username (
962 p_username text
963 )
964RETURNS dtw.emp as $PROC$
965DECLARE
966 row1 dtw.emp%ROWTYPE;
967BEGIN
968 select into row1 * from dtw.emp where username = p_username;
969 return row1;
970END;
971$PROC$ LANGUAGE plpgsql;
972
973/*
974 * Returns an employee record based on the email address
975 */
976CREATE OR REPLACE FUNCTION dtw.Get_emp_by_email (
977 p_email text
978 )
979RETURNS dtw.emp as $PROC$
980DECLARE
981 row1 dtw.emp%ROWTYPE;
982BEGIN
983 select into row1 * from dtw.emp where contact_email = p_email;
984 return row1;
985END;
986$PROC$ LANGUAGE plpgsql;
987
988/*
989 * Returns the internal ID if the user id and password match.
990 * The User ID is not found: return -2
991 * The password does not match: return -1
992 * The password does match: return the id
993 */
994CREATE OR REPLACE FUNCTION dtw.Get_emp_check_password (
995 p_username text,
996 p_password text
997 )
998RETURNS integer as $PROC$
999DECLARE
1000 row1 dtw.emp%ROWTYPE;
1001BEGIN
1002 select into row1 * from dtw.emp where username = p_username;
1003 IF NOT FOUND THEN
1004 RETURN -2;
1005 END IF;
1006
1007 select into row1 * from dtw.emp where username = p_username AND password = p_password;
1008 IF NOT FOUND THEN
1009 RETURN -1;
1010 END IF;
1011
1012 return row1.eid;
1013END;
1014$PROC$ LANGUAGE plpgsql;
1015
1016/*
1017 * Xxxx
1018 */
1019CREATE OR REPLACE FUNCTION dtw.Get_all_employer (
1020 p_offset int,
1021 p_limit int
1022 )
1023RETURNS SETOF dtw.emp as $PROC$
1024DECLARE
1025 row1 dtw.emp%ROWTYPE;
1026 q text;
1027BEGIN
1028 if p_limit = -1 then
1029 q := 'select * from dtw.emp order by contact_lname limit ALL offset $1';
1030 FOR row1 IN EXECUTE q USING p_offset
1031 LOOP
1032 return next row1;
1033 END LOOP;
1034 RETURN;
1035 else
1036 q := 'select * from dtw.emp order by contact_lname limit $1 offset $2';
1037 FOR row1 IN EXECUTE q USING p_limit, p_offset
1038 LOOP
1039 return next row1;
1040 END LOOP;
1041 RETURN;
1042 end if;
1043END;
1044$PROC$ LANGUAGE plpgsql;
1045
1046/*
1047 * Xxxx
1048 */
1049CREATE OR REPLACE FUNCTION dtw.Get_all_emp_by_contact (
1050 p_offset int,
1051 p_limit int
1052 )
1053RETURNS SETOF dtw.emp as $PROC$
1054DECLARE
1055 row1 dtw.emp%ROWTYPE;
1056 q text;
1057BEGIN
1058 if p_limit = -1 then
1059 q := 'select * from dtw.emp order by contact_lname limit ALL offset $1';
1060 FOR row1 IN EXECUTE q USING p_offset
1061 LOOP
1062 return next row1;
1063 END LOOP;
1064 RETURN;
1065 else
1066 q := 'select * from dtw.emp order by contact_lname limit $1 offset $2';
1067 FOR row1 IN EXECUTE q USING p_limit, p_offset
1068 LOOP
1069 return next row1;
1070 END LOOP;
1071 RETURN;
1072 end if;
1073END;
1074$PROC$ LANGUAGE plpgsql;
1075
1076/*
1077 * This will delete all employer records from dtw.emp
1078 */
1079CREATE OR REPLACE FUNCTION dtw.Delete_all_emp ()
1080RETURNS void as $PROC$
1081BEGIN
1082 delete from dtw.emp;
1083END;
1084$PROC$ LANGUAGE plpgsql;
1085
1086CREATE OR REPLACE FUNCTION dtw.Delete_emp_by_id (
1087 p_eid int
1088)
1089RETURNS void as $PROC$
1090BEGIN
1091 delete from dtw.moderator where eid = p_eid;
1092END;
1093$PROC$ LANGUAGE plpgsql;
1094
1095CREATE OR REPLACE FUNCTION dtw.Delete_emp_by_username (
1096 p_username text
1097)
1098RETURNS void as $PROC$
1099BEGIN
1100 delete from dtw.moderator where username = p_username;
1101END;
1102$PROC$ LANGUAGE plpgsql;
1103
1104/* -----------------------------------------------------------------------
1105 * Document ...
1106 * -----------------------------------------------------------------------
1107 */
1108
1109 /* Status */
1110drop domain if exists dtw.doc_type_type cascade;
1111create domain dtw.doc_type_type CHAR(1)
1112 check (value in (
1113 'A', /* Plain note type */
1114 'B', /* xxx */
1115 'C', /* xxx */
1116 'D', /* xxx */
1117 'Z' /* All */
1118));
1119
1120create sequence dtw.doc_seq start with 100 no cycle;
1121DROP TABLE IF EXISTS dtw.doc;
1122create table dtw.doc (
1123 did int PRIMARY KEY DEFAULT nextval('dtw.doc_seq'),
1124 doc text,
1125 entered timestamp with time zone,
1126 doc_type dtw.doc_type_type DEFAULT 'A'
1127);
1128
1129/*
1130 * Creates a new document, returning the id
1131 */
1132CREATE OR REPLACE FUNCTION dtw.New_doc(
1133 p_doc text,
1134 p_doc_type dtw.doc_type_type
1135 )
1136RETURNS int AS $PROC$
1137DECLARE
1138 t int;
1139BEGIN
1140 INSERT INTO dtw.doc (
1141 doc, entered, doc_type )
1142 VALUES (
1143 p_doc, now(), p_doc_type)
1144 RETURNING did into t;
1145 return t;
1146END;
1147$PROC$ LANGUAGE plpgsql;
1148
1149/*
1150 * CHECK THIS -- NOT SURE IF ITS NEEDED ...
1151 */
1152CREATE OR REPLACE FUNCTION dtw.New_doc_by_inititals(
1153 p_sshort char(4),
1154 p_tshort char(4),
1155 p_doc text,
1156 doc_type dtw.doc_type_type
1157 )
1158RETURNS int AS $PROC$
1159DECLARE
1160 sid_v int;
1161 tid_v int;
1162 t int;
1163BEGIN
1164 select into sid_v sid from dtw.moderator where initials = p_sshort;
1165 IF NOT FOUND THEN
1166 RETURN 0;
1167 END IF;
1168
1169 select into tid_v tid from dtw.youth where initials = p_tshort;
1170 IF NOT FOUND THEN
1171 RETURN 0;
1172 END IF;
1173
1174 INSERT INTO dtw.doc (
1175 tid, sid, doc, entered, doc_type )
1176 VALUES (
1177 sid_v, tid_v, p_doc, now(), p_doc_type)
1178 RETURNING did into t;
1179 return t;
1180END;
1181$PROC$ LANGUAGE plpgsql;
1182
1183/*
1184 * xxx
1185 */
1186CREATE OR REPLACE FUNCTION dtw.Find_docs (
1187 p_doc_type dtw.doc_type_type,
1188 p_offset int,
1189 p_limit int
1190 )
1191RETURNS SETOF dtw.doc as $PROC$
1192DECLARE
1193 row1 dtw.doc%ROWTYPE;
1194 q text;
1195 w text;
1196BEGIN
1197 if (p_doc_type = 'Z' or p_doc_type = NULL) then
1198 w := ' ';
1199 else
1200 w := 'where status = ' || '' || p_doc_type || '';
1201 end if;
1202
1203 if (p_limit = -1) then
1204 q := 'select * from dtw.doc ' || w || ' order by entered desc limit ALL offset $1';
1205 FOR row1 IN EXECUTE q USING p_offset
1206 LOOP
1207 return next row1;
1208 END LOOP;
1209 RETURN;
1210 else
1211 q := 'select * from dtw.doc ' || w || ' order by entered desc limit $1 offset $2';
1212 FOR row1 IN EXECUTE q USING p_limit, p_offset
1213 LOOP
1214 return next row1;
1215 END LOOP;
1216 RETURN;
1217 end if;
1218END;
1219$PROC$ LANGUAGE plpgsql;
1220
1221/* -----------------------------------------------------------------------
1222 * Properties
1223 * -----------------------------------------------------------------------
1224 */
1225
1226drop domain if exists dtw.prop_type_type cascade;
1227create domain dtw.prop_type_type CHAR(1)
1228 check (value in (
1229 'A', /* String */
1230 'B', /* Integer */
1231 'C', /* Timestamp */
1232 'D' /* Number(8,2) */
1233));
1234
1235DROP TABLE IF EXISTS dtw.prop_type;
1236create table dtw.prop_type (
1237 id int PRIMARY KEY,
1238 propname text,
1239 proptype dtw.prop_type_type,
1240 shortl text,
1241 descr text
1242);
1243
1244insert into dtw.prop_type(id,propname,proptype,shortl,descr) values (1,'tags', 'A', 'Tag list', 'Used for a list of tags');
1245insert into dtw.prop_type(id,propname,proptype,shortl,descr) values (2,'path', 'B', 'Path', 'Estimates of how youth are doing');
1246
1247create sequence dtw.prop_seq start with 100 no cycle;
1248DROP TABLE IF EXISTS dtw.prop;
1249create table dtw.prop (
1250 pid int PRIMARY KEY DEFAULT nextval('dtw.prop_seq'),
1251 sid int references dtw.moderator(sid),
1252 tid int references dtw.youth(tid),
1253 switch dtw.prop_type_type,
1254 propname text,
1255 val_text text,
1256 val_int int,
1257 val_time timestamp with time zone,
1258 entered timestamp with time zone
1259);
1260
1261CREATE OR REPLACE FUNCTION dtw.New_prop(
1262 p_sid int,
1263 p_tid int,
1264 p_name text,
1265 p_val anyelement,
1266 p_switch char(1)
1267)
1268RETURNS int AS $PROC$
1269DECLARE
1270 t int;
1271BEGIN
1272 t := 0;
1273 if (switch = 'A') then
1274 INSERT INTO dtw.doc (sid, tid, switch, propname, val_text, entered)
1275 VALUES (p_sid, p_tid, p_switch, p_name, p_val, now())
1276 RETURNING pid into t;
1277 elseif (switch = 'B') then
1278 INSERT INTO dtw.doc (sid, tid, switch, propname, val_int, entered)
1279 VALUES (p_sid, p_tid, p_switch, p_name, p_val, now())
1280 RETURNING pid into t;
1281 elseif (swtich = 'C') then
1282 INSERT INTO dtw.doc (sid, tid, switch, propname, val_time, entered)
1283 VALUES (p_sid, p_tid, p_switch, p_name, p_val, now())
1284 RETURNING pid into t;
1285 end if;
1286 return t;
1287END;
1288$PROC$ LANGUAGE plpgsql;
1289
1290
1291/* -----------------------------------------------------------------------
1292 * Job ...
1293 * -----------------------------------------------------------------------
1294 */
1295
1296 /*
1297 * Jobs have a status indicator
1298 */
1299drop domain if exists dtw.job_status_type cascade;
1300create domain dtw.job_status_type CHAR(1)
1301 check (value in (
1302 'A', /* Proposed */
1303 'B', /* Open */
1304 'C', /* Assigned */
1305 'D', /* Closed */
1306 'Z' /* All */
1307));
1308
1309/*
1310 * Jobs are created by or on behalf of an employer
1311 */
1312create sequence dtw.job_seq start with 100 no cycle;
1313DROP TABLE IF EXISTS dtw.job;
1314create table dtw.job (
1315 jid int PRIMARY KEY DEFAULT nextval('dtw.job_seq'),
1316 eid int references dtw.emp(eid),
1317 title text,
1318 job_date timestamp with time zone,
1319 descr text,
1320 compensation text,
1321 notes text,
1322 location text,
1323 status dtw.job_status_type DEFAULT 'A',
1324 entered timestamp with time zone, /* the date that this tuple was first created */
1325 last_update timestamp with time zone /* the last date that this tuple was updated */
1326);
1327
1328/*
1329 * A job offer is actually a 'job assignment' -- that is, a job is assigned to a
1330 * youth actor by a moderator actor
1331 */
1332create sequence dtw.job_offer_seq start with 100 no cycle;
1333DROP TABLE IF EXISTS dtw.job_offer;
1334create table dtw.job_offer (
1335 joid int PRIMARY KEY DEFAULT nextval('dtw.job_offer_seq'),
1336 jid int references dtw.job(jid),
1337 tid int references dtw.youth(tid),
1338 sid int references dtw.moderator(sid),
1339 entered timestamp with time zone /* the date that this tuple was first created */
1340);
1341
1342/*
1343 * This is a bridge table for enabling notes to be associated with job offers
1344 */
1345DROP TABLE IF EXISTS dtw.job_offer_note;
1346create table dtw.job_offer_note (
1347 joid int references dtw.job_offer(joid),
1348 sid int references dtw.moderator(sid),
1349 did int references dtw.doc(did)
1350);
1351
1352/*
1353 * This is used to create a new job offer. A job o
1354 */
1355CREATE OR REPLACE FUNCTION dtw.New_job_offer (
1356 p_jid int,
1357 p_tid int,
1358 p_sid int,
1359 p_note text
1360 )
1361RETURNS int AS $PROC$
1362DECLARE
1363 t int;
1364 d int;
1365BEGIN
1366 INSERT INTO dtw.job_offer (
1367 jid, tid, sid, entered)
1368 VALUES (
1369 p_jid, p_tid, p_sid, now())
1370 RETURNING joid into t;
1371
1372 update dtw.job SET status = 'C' WHERE jid = p_jid;
1373
1374 if (p_note != '') then
1375 select * into d from dtw.New_doc(p_note,'A');
1376 INSERT INTO dtw.job_offer_note (
1377 joid, sid, did)
1378 VALUES(
1379 t,p_sid,d);
1380 end if;
1381 return t;
1382END;
1383$PROC$ LANGUAGE plpgsql;
1384
1385/*
1386 * This is used to add a new note to a job offer
1387 */
1388CREATE OR REPLACE FUNCTION dtw.New_note_for_job_offer (
1389 p_joid int,
1390 p_sid int,
1391 p_note text
1392 )
1393RETURNS int AS $PROC$
1394DECLARE
1395 d int;
1396BEGIN
1397 select * into d from dtw.New_doc(p_note,'A');
1398 INSERT INTO dtw.job_offer_note (
1399 joid, sid, did)
1400 VALUES(
1401 p_joid,p_sid,d);
1402 return d;
1403END;
1404$PROC$ LANGUAGE plpgsql;
1405
1406/*
1407 * This is used to create a new job
1408 */
1409CREATE OR REPLACE FUNCTION dtw.New_job (
1410 p_eid int,
1411 p_title text
1412 )
1413RETURNS int AS $PROC$
1414DECLARE
1415 t int;
1416BEGIN
1417 INSERT INTO dtw.job (
1418 eid, title, entered,last_update)
1419 VALUES (
1420 p_eid, p_title, now(), now())
1421 RETURNING jid into t;
1422 return t;
1423END;
1424$PROC$ LANGUAGE plpgsql;
1425
1426/*
1427 * This is used to update the attributes of a job
1428 */
1429CREATE OR REPLACE FUNCTION dtw.Update_job (
1430 p_jid int,
1431 p_field varchar(64),
1432 p_value anyelement
1433 )
1434RETURNS int AS $PROC$
1435DECLARE
1436 q text;
1437BEGIN
1438 q := format('update dtw.job SET %I = $1 WHERE jid = $2', p_field);
1439 EXECUTE q USING p_value, p_jid;
1440 return p_jid;
1441END;
1442$PROC$ LANGUAGE plpgsql;
1443
1444/*
1445 * This function returns information about a job
1446 */
1447CREATE OR REPLACE FUNCTION dtw.Get_job_info (
1448 p_job_id int
1449 )
1450RETURNS dtw.job as $PROC$
1451DECLARE
1452 row1 dtw.job%ROWTYPE;
1453BEGIN
1454 select into row1 * from dtw.job where jid = p_job_id;
1455 return row1;
1456END;
1457$PROC$ LANGUAGE plpgsql;
1458
1459
1460
1461/*
1462 * xxx
1463 */
1464/*
1465 * xxx
1466 */
1467CREATE OR REPLACE FUNCTION dtw.Find_jobs (
1468 p_emp_id int,
1469 p_status dtw.job_status_type,
1470 p_offset int,
1471 p_limit int
1472 )
1473RETURNS SETOF dtw.job as $PROC$
1474DECLARE
1475 row1 dtw.job%ROWTYPE;
1476 q text;
1477 status_clause text;
1478 emp_clause text;
1479 where_clause text;
1480BEGIN
1481
1482 /*
1483 * Check for employee id
1484 */
1485 emp_clause := ' ';
1486 if (p_emp_id <> NULL) then
1487 emp_clause := ' eid = ' || '' || p_emp_id || '';
1488 end if;
1489
1490 /*
1491 * Check for status
1492 */
1493 status_clause := ' ';
1494 if (p_status = 'Z' or p_status = NULL) then
1495 status_clause := ' ';
1496 else
1497 status_clause := 'status = ' || '' || p_status || '';
1498 end if;
1499
1500 /*
1501 * Create where clause
1502 */
1503 where_clause := ' ';
1504 if (emp_clause <> ' ') and (status_clause <> ' ') then
1505 where_clause := 'where ' || status_clause || ' and ' || emp_clause;
1506 elsif (emp_clause <> ' ') then
1507 where_clause := 'where ' || emp_clause;
1508 elsif (status_clause <> ' ') then
1509 where_clause := 'where ' || status_clause;
1510 end if;
1511
1512
1513 if (p_limit = -1) then
1514 q := 'select * from dtw.job ' || where_clause || ' order by entered desc limit ALL offset $1';
1515 FOR row1 IN EXECUTE q USING p_offset
1516 LOOP
1517 return next row1;
1518 END LOOP;
1519 RETURN;
1520 else
1521 q := 'select * from dtw.job ' || where_clause || ' order by entered desc limit $1 offset $2';
1522 FOR row1 IN EXECUTE q USING p_limit, p_offset
1523 LOOP
1524 return next row1;
1525 END LOOP;
1526 RETURN;
1527 end if;
1528
1529END;
1530$PROC$ LANGUAGE plpgsql;
1531
1532/*
1533 * This will delete all employer records from dtw.emp
1534 */
1535CREATE OR REPLACE FUNCTION dtw.Delete_all_job ()
1536RETURNS void as $PROC$
1537BEGIN
1538 delete from dtw.job;
1539END;
1540$PROC$ LANGUAGE plpgsql;
1541
1542CREATE OR REPLACE FUNCTION dtw.Delete_job_by_id (
1543 p_jid int
1544)
1545RETURNS void as $PROC$
1546BEGIN
1547 delete from dtw.job where jid = p_jid;
1548END;
1549$PROC$ LANGUAGE plpgsql;
1550
1551
1552/* -----------------------------------------------------------------------
1553 * Workflows tables
1554 * -----------------------------------------------------------------------
1555 */
1556
1557/* Status */
1558drop domain if exists dtw.node_type_domain cascade;
1559create domain dtw.node_type_domain CHAR(1)
1560 check (value in (
1561 'S', /* Start node */
1562 'E', /* END node */
1563 'A', /* Activity node */
1564 'F', /* Fork node */
1565 'J' /* Joiner node */
1566));
1567
1568create sequence dtw.workflow_seq start with 1 no cycle;
1569DROP TABLE IF EXISTS dtw.workflow;
1570create table dtw.workflow (
1571 wfid int PRIMARY KEY DEFAULT nextval('dtw.workflow_seq'),
1572 name varchar(64) UNIQUE,
1573 info text
1574);
1575
1576create sequence dtw.node_seq start with 1 no cycle;
1577DROP TABLE IF EXISTS dtw.node;
1578create table dtw.node (
1579 nid int PRIMARY KEY DEFAULT nextval('dtw.node_seq'),
1580 wfid int references dtw.workflow(wfid) on delete cascade,
1581 sname char(3),
1582 name varchar(64),
1583 ntype dtw.node_type_domain
1584);
1585
1586create sequence dtw.edge_seq start with 1 no cycle;
1587DROP TABLE IF EXISTS dtw.edge;
1588create table dtw.edge (
1589 eid int PRIMARY KEY DEFAULT nextval('dtw.edge_seq'),
1590 node1 int references dtw.node(nid) on delete cascade,
1591 node2 int references dtw.node(nid) on delete cascade,
1592 guard varchar(64)
1593);
1594
1595/* -----------------------------------------------------------------------
1596 * Workflows
1597 * -----------------------------------------------------------------------
1598 */
1599
1600/*
1601 * Create_workflow
1602 */
1603CREATE OR REPLACE FUNCTION dtw.Create_workflow (
1604 p_name varchar(64),
1605 p_info text
1606 )
1607RETURNS int AS $PROC$
1608
1609DECLARE
1610 row1 RECORD;
1611 row2 RECORD;
1612
1613BEGIN
1614 select into row1 * from dtw.workflow where p_name=name;
1615 IF FOUND THEN
1616 RETURN 0;
1617 END IF;
1618
1619 INSERT INTO dtw.workflow (
1620 name, info)
1621 VALUES (
1622 p_name, p_info);
1623
1624 select into row2 * from dtw.workflow where p_name=name;
1625 IF NOT FOUND THEN
1626 RETURN 0;
1627 END IF;
1628
1629 INSERT INTO dtw.node (wfid, sname, name, ntype)
1630 VALUES (row2.wfid, '__S', '__Start_node','S');
1631
1632 INSERT INTO dtw.node (wfid, sname, name, ntype)
1633 VALUES (row2.wfid, '__F', '__Finish_node','F');
1634
1635 RETURN 1;
1636END;
1637$PROC$ LANGUAGE plpgsql;
1638
1639/*
1640 * Delete workflow
1641 */
1642CREATE OR REPLACE FUNCTION dtw.Delete_workflow (
1643 p_name varchar(64)
1644 )
1645RETURNS void AS $PROC$
1646BEGIN
1647 delete from dtw.workflow where name = p_name;
1648END;
1649$PROC$ LANGUAGE plpgsql;
1650
1651
1652/*
1653 * This function returns a list of all the workflows
1654 */
1655CREATE OR REPLACE FUNCTION dtw.Get_workflows ()
1656RETURNS SETOF dtw.workflow AS $PROC$
1657
1658DECLARE
1659 row1 dtw.workflow%ROWTYPE;
1660
1661BEGIN
1662 FOR row1 IN
1663 SELECT * FROM dtw.workflow
1664 LOOP
1665 RETURN NEXT row1;
1666 END LOOP;
1667 RETURN;
1668END;
1669$PROC$ LANGUAGE plpgsql;
1670
1671/* -----------------------------------------------------------------------
1672 * Nodes
1673 * -----------------------------------------------------------------------
1674 */
1675
1676/*
1677 * This function adds a node, identified by a short name, to an existing workflow.
1678 * Returns 1 if the node is added or is already in the the workflow. Returns 0
1679 * if the workflow is not found.
1680 */
1681CREATE OR REPLACE FUNCTION dtw.Add_node (
1682 p_workflow_name varchar(64),
1683 p_sname char (3),
1684 p_name varchar(64),
1685 p_ntype dtw.node_type_domain
1686 )
1687RETURNS int AS $PROC$
1688
1689DECLARE
1690 row1 RECORD;
1691 row2 RECORD;
1692
1693BEGIN
1694 select into row1 * from dtw.workflow where p_workflow_name=name;
1695 IF NOT FOUND THEN
1696 RETURN 0;
1697 END IF;
1698
1699 select into row2 * from dtw.node where wfid=row1.wfid AND p_sname=sname;
1700 IF FOUND THEN
1701 RETURN 1;
1702 END IF;
1703
1704 INSERT INTO dtw.node (
1705 wfid, sname, name, ntype)
1706 VALUES (
1707 row1.wfid, p_sname, p_name, p_ntype);
1708 RETURN 1;
1709END;
1710$PROC$ LANGUAGE plpgsql;
1711
1712
1713/*
1714 * Return a node record by the node's short name.
1715 */
1716CREATE OR REPLACE FUNCTION dtw.Get_node (
1717 p_workflow_name varchar(64),
1718 p_sname char (3)
1719 )
1720RETURNS RECORD AS $PROC$
1721
1722DECLARE
1723 row1 RECORD;
1724
1725BEGIN
1726 select into row1 * from dtw.node N, dtw.workflow W
1727 where W.name = p_workflow_name
1728 AND N.wif = W.wid
1729 AND N.sname = p_sname;
1730 RETURN row1;
1731END;
1732$PROC$ LANGUAGE plpgsql;
1733
1734/*
1735 * Return the node record by the node's id.
1736 */
1737CREATE OR REPLACE FUNCTION dtw.Get_node_by_id (
1738 p_nid int
1739)
1740RETURNS RECORD AS $PROC$
1741
1742DECLARE
1743 row1 RECORD;
1744
1745BEGIN
1746 select into row1 * from dtw.node where nid = p_nid;
1747 RETURN row1;
1748END;
1749$PROC$ LANGUAGE plpgsql;
1750
1751/*
1752 * This function returns a list of all nodes for a workflow
1753 */
1754CREATE OR REPLACE FUNCTION dtw.Get_nodes (
1755 p_workflow_name varchar(64)
1756)
1757RETURNS SETOF dtw.node AS $PROC$
1758
1759DECLARE
1760 row1 dtw.node%ROWTYPE;
1761
1762BEGIN
1763 FOR row1 IN
1764 SELECT N.nid, N.wfid, N.sname, N.name, N.ntype
1765 FROM dtw.node N, dtw.workflow W
1766 WHERE W.name = p_workflow_name AND W.wfid = N.wfid
1767 LOOP
1768 RETURN NEXT row1;
1769 END LOOP;
1770 RETURN;
1771END;
1772$PROC$ LANGUAGE plpgsql;
1773
1774/* -----------------------------------------------------------------------
1775 * Edges
1776 * -----------------------------------------------------------------------
1777 */
1778/*
1779 * Connects the start node to a node
1780 */
1781CREATE OR REPLACE FUNCTION dtw.Link_from_start (
1782 p_workflow_name varchar(64),
1783 p_sname2 char(3),
1784 p_guard varchar(64)
1785 )
1786RETURNS int AS $PROC$
1787BEGIN
1788 RETURN dtw.Link_between(p_workflow_name, '__S', p_sname2, p_guard);
1789END;
1790$PROC$ LANGUAGE plpgsql;
1791
1792/*
1793 * Connects a node to to the finish node
1794 */
1795CREATE OR REPLACE FUNCTION dtw.Link_to_finish (
1796 p_workflow_name varchar(64),
1797 p_sname1 char(3),
1798 p_guard varchar(64)
1799 )
1800RETURNS int AS $PROC$
1801BEGIN
1802 RETURN dtw.Link_between(p_workflow_name, p_sname1, '__F', p_guard);
1803END;
1804$PROC$ LANGUAGE plpgsql;
1805
1806/*
1807 * This function adds an edge between two existing nodes in a workflow.
1808 */
1809CREATE OR REPLACE FUNCTION dtw.Link_between (
1810 p_workflow_name varchar(64),
1811 p_sname1 char(3),
1812 p_sname2 char(3),
1813 p_guard varchar(64)
1814 )
1815RETURNS int AS $PROC$
1816
1817DECLARE
1818 row0 RECORD;
1819 row1 RECORD;
1820 row2 RECORD;
1821
1822BEGIN
1823 select into row0 * from dtw.workflow where p_workflow_name=name;
1824 IF NOT FOUND THEN
1825 RETURN 0;
1826 END IF;
1827
1828 select into row1 * from dtw.node where sname=p_sname1;
1829 IF NOT FOUND THEN
1830 RETURN 0;
1831 END IF;
1832
1833 select into row2 * from dtw.node where sname=p_sname2;
1834 IF NOT FOUND THEN
1835 RETURN 0;
1836 END IF;
1837
1838 select into row0 * from dtw.edge where node1=row1.nid and node2=row2.nid;
1839 IF FOUND THEN
1840 RETURN 1;
1841 END IF;
1842
1843 INSERT INTO dtw.edge (
1844 node1, node2, guard)
1845 VALUES (
1846 row1.nid, row2.nid, p_guard);
1847
1848 RETURN 1;
1849END;
1850$PROC$ LANGUAGE plpgsql;
1851
1852/*
1853 * This table is used ONLY to send data about edges back to the client
1854 */
1855DROP TABLE IF EXISTS dtw.edge_info_rec;
1856create table dtw.edge_info_rec (
1857 eid int, /* Edge id */
1858 node1 int, /* moderator node id */
1859 node2 int, /* Child node id */
1860 guard varchar(64), /* The lable of the edge */
1861 sname1 char(3), /* The short name for the moderator node */
1862 sname2 char(3), /* The short name for the child node */
1863 name2 varchar(64), /* The full name of the child node */
1864 ntype2 dtw.node_type_domain /* The node type for the child node */
1865);
1866
1867/*
1868 * This function returns a set of children for a given node. The node children
1869 * records include the both edge information and children node information. See
1870 * the structure dtw.edge_info_rec.
1871 */
1872CREATE OR REPLACE FUNCTION dtw.Get_children (
1873 p_workflow_name varchar(64),
1874 p_sname char(3)
1875 )
1876RETURNS SETOF dtw.edge_info_rec AS $PROC$
1877
1878DECLARE
1879 row1 RECORD;
1880 row2 dtw.node%ROWTYPE;
1881 row3 dtw.edge%ROWTYPE;
1882 row4 dtw.edge_info_rec%ROWTYPE;
1883
1884 t_node1 int;
1885 t_sname1 char(3);
1886 t_sname2 char(3);
1887 t_name2 varchar(64);
1888 t_ntype2 dtw.node_type_domain;
1889
1890BEGIN
1891 select into row1 * from dtw.workflow where name=p_workflow_name;
1892 IF NOT FOUND THEN
1893 RETURN;
1894 END IF;
1895
1896 select into row2 * from dtw.node where sname = p_sname;
1897 IF NOT FOUND THEN
1898 RETURN;
1899 END IF;
1900
1901 FOR row3 IN
1902 SELECT E.eid as eid, E.node1 as n1, E.node2 as n2, E.guard as guard
1903 FROM dtw.edge E
1904 WHERE E.node1 = row2.nid
1905 LOOP
1906 select into t_sname1 sname from dtw.node where nid = row3.node1;
1907 select into t_sname2 sname from dtw.node where nid = row3.node2;
1908 select into t_name2 name from dtw.node where nid = row3.node2;
1909 select into t_ntype2 ntype from dtw.node where nid = row3.node2;
1910
1911 row4.eid := row3.eid;
1912 row4.node1 := row3.node1;
1913 row4.node2 := row3.node2;
1914 row4.guard := row3.guard;
1915 row4.sname1 := t_sname1;
1916 row4.sname2 := t_sname2;
1917 row4.name2 := t_name2;
1918 row4.ntype2 := t_ntype2;
1919 RETURN NEXT row4;
1920 END LOOP;
1921 RETURN;
1922END;
1923$PROC$ LANGUAGE plpgsql;
1924
1925
1926
1927#EXPOSED
1928#BY DDJ HAXING GROUP
1929#WE ARE ANONYMOUS
1930#FUCKOFF
1931#USA
1932#LEAK
1933#DATABASE
1934
1935SPREAD THIS ..