daily pastebin goal
27%
SHARE
TWEET

WASHINGTON UNIVERSITY DATABASE LEAKED! DATA OF UNIVERSITY

a guest Mar 17th, 2018 43 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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.  
  119. DROP SCHEMA IF EXISTS dtw CASCADE;
  120. create schema dtw;
  121.  
  122. /* Gender */
  123. drop domain if exists dtw.gender_type cascade;
  124. create domain dtw.gender_type CHAR(1)
  125.     check (value in (
  126.         'M',    /* Male */  
  127.         'F'     /* Female */
  128. ));
  129.  
  130. /* Status */
  131. drop domain if exists dtw.status_type cascade;
  132. create 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 */
  145. drop domain if exists dtw.ethnicity_type cascade;
  146. create 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 */
  159. drop domain if exists dtw.hispanic_type cascade;
  160. create 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.  */
  176. create sequence dtw.person_seq start with 100 no cycle;  
  177.  
  178.  
  179. DROP TABLE IF EXISTS dtw.youth;
  180. create 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.  */
  243. CREATE 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. )
  250. RETURNS int AS $PROC$  
  251. DECLARE
  252.     t   int;
  253.     f   int;
  254. BEGIN
  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;
  266. END;
  267. $PROC$ LANGUAGE plpgsql;
  268.  
  269. /*
  270.  * This function creates a new youth
  271.  */
  272. CREATE OR REPLACE FUNCTION dtw.New_youth (
  273.     p_fname         varchar(64),
  274.     p_mname         varchar(64),
  275.     p_lname         varchar(64)
  276. )
  277. RETURNS int AS $PROC$  
  278. DECLARE
  279.     t   int;
  280. BEGIN
  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;
  287. END;
  288. $PROC$ LANGUAGE plpgsql;
  289.  
  290.  
  291. /*
  292.  * This function updates the contents of a youth's field
  293.  */
  294. CREATE OR REPLACE FUNCTION dtw.Update_youth (
  295.     p_tid           int,
  296.     p_field         varchar(64),
  297.     p_value         anyelement
  298.     )
  299. RETURNS int AS $PROC$
  300. DECLARE
  301.     q       text;  
  302.     row1    dtw.youth%ROWTYPE;
  303. BEGIN
  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;
  319. END;
  320. $PROC$ LANGUAGE plpgsql;
  321.  
  322. /*
  323.  * This function returns a youth by its id
  324.  */
  325. CREATE OR REPLACE FUNCTION dtw.Get_youth_by_id (
  326.     p_tid           int
  327.     )
  328. RETURNS dtw.youth as $PROC$
  329. DECLARE
  330.     row1    dtw.youth%ROWTYPE;
  331. BEGIN
  332.     select into row1 * from dtw.youth where tid = p_tid;
  333.     return row1;
  334. END;
  335. $PROC$ LANGUAGE plpgsql;
  336.  
  337. /*
  338.  * This function returns a youth by its short name (aka initials)
  339.  */
  340. CREATE OR REPLACE FUNCTION dtw.Get_youth_by_short (
  341.     p_short         text
  342.     )
  343. RETURNS dtw.youth as $PROC$
  344. DECLARE
  345.     row1    dtw.youth%ROWTYPE;
  346. BEGIN
  347.     select into row1 * from dtw.youth where initials = p_short;
  348.     return row1;
  349. END;
  350. $PROC$ LANGUAGE plpgsql;
  351.  
  352. /*
  353.  * This function returns a youth by its email
  354.  */
  355. CREATE OR REPLACE FUNCTION dtw.Get_youth_by_email (
  356.     p_email         text
  357. )
  358. RETURNS dtw.youth as $PROC$
  359. DECLARE
  360.     row1    dtw.youth%ROWTYPE;
  361. BEGIN
  362.     select into row1 * from dtw.youth where email = p_email;
  363.     return row1;
  364. END;
  365. $PROC$ LANGUAGE plpgsql;
  366.  
  367. /*
  368.  * This function returns a youth record by user name
  369.  */
  370. CREATE OR REPLACE FUNCTION dtw.Get_youth_by_username (
  371.     p_username      text
  372.     )
  373. RETURNS dtw.youth as $PROC$
  374. DECLARE
  375.     row1    dtw.youth%ROWTYPE;
  376. BEGIN
  377.     select into row1 * from dtw.youth where username = p_username;
  378.     return row1;
  379. END;
  380. $PROC$ LANGUAGE plpgsql;
  381.  
  382. /*
  383.  * Returns TRUE if user name is okay
  384.  */
  385. CREATE OR REPLACE FUNCTION dtw.is_username_okay (
  386.     p_username      text
  387.     )
  388. RETURNS int as $PROC$
  389. DECLARE
  390.     row1    dtw.youth%ROWTYPE;
  391.     f1      boolean;
  392.     f2      boolean;
  393.     f3      boolean;
  394. BEGIN
  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;
  415. END;
  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.  */
  426. CREATE OR REPLACE FUNCTION dtw.Get_youth_check_password (
  427.     p_username      text,
  428.     p_password      text
  429.     )
  430. RETURNS integer as $PROC$
  431. DECLARE
  432.     row1    dtw.youth%ROWTYPE;
  433. BEGIN
  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;
  445. END;
  446. $PROC$ LANGUAGE plpgsql;
  447.  
  448.  
  449. /*
  450.  * This function returns a list youths by the most recently added
  451.  */
  452. CREATE OR REPLACE FUNCTION dtw.Get_recent_youth (
  453.     p_offset        int,
  454.     p_max           int
  455. )
  456. RETURNS SETOF dtw.youth as $PROC$
  457. DECLARE
  458.     row1    dtw.youth%ROWTYPE;
  459.     q       text;
  460. BEGIN
  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;
  476. END;
  477. $PROC$ LANGUAGE plpgsql;
  478.  
  479. /*
  480.  * Returns a list of youths by starting letters of first or last name
  481.  */
  482. CREATE OR REPLACE FUNCTION dtw.Get_youth_by_name (
  483.     p_begin         char,
  484.     p_offset        int,
  485.     p_limit         int
  486.     )
  487. RETURNS SETOF dtw.youth as $PROC$
  488. DECLARE
  489.     row1    dtw.youth%ROWTYPE;
  490.     q       text;
  491. BEGIN
  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;
  507. END;
  508. $PROC$ LANGUAGE plpgsql;
  509.  
  510. /*
  511.  * Returns a list of youths
  512.  */
  513. CREATE OR REPLACE FUNCTION dtw.Get_all_youth (
  514.     p_offset        int,
  515.     p_limit         int
  516.     )
  517. RETURNS SETOF dtw.youth as $PROC$
  518. DECLARE
  519.     row1    dtw.youth%ROWTYPE;
  520.     q       text;
  521. BEGIN
  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;
  537. END;
  538. $PROC$ LANGUAGE plpgsql;
  539.  
  540. /*
  541.  * This will delete all youth records from dtw.youth
  542.  */
  543. CREATE OR REPLACE FUNCTION dtw.Delete_all_youth ()
  544. RETURNS void as $PROC$
  545. BEGIN
  546.     delete from dtw.youth;
  547. END;
  548. $PROC$ LANGUAGE plpgsql;
  549.  
  550. /*
  551.  * This will delete all youth records from dtw.youth
  552.  */
  553. CREATE OR REPLACE FUNCTION dtw.Delete_youth_by_id (
  554.     p_id    int
  555. )
  556. RETURNS void as $PROC$
  557. BEGIN
  558.     delete from dtw.youth Y where Y.tid = p_id;
  559. END;
  560. $PROC$ LANGUAGE plpgsql;
  561.  
  562.  
  563. /*
  564.  * This will insert a set of youth into the system -- useful for testing
  565.  */
  566. CREATE OR REPLACE FUNCTION dtw.Create_test_youth (
  567.     p_num int
  568. )
  569. RETURNS void as $PROC$
  570. DECLARE
  571.     t       int;
  572.     k       int;
  573. BEGIN
  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;
  582. END;
  583. $PROC$ LANGUAGE plpgsql;
  584.  
  585.  
  586. /* -----------------------------------------------------------------------
  587.  * moderator functions  
  588.  * -----------------------------------------------------------------------
  589.  */
  590.  
  591. DROP TABLE IF EXISTS dtw.moderator;
  592. create 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.  */
  636. CREATE OR REPLACE FUNCTION dtw.New_moderator (
  637.     p_fname         varchar(64),
  638.     p_mname         varchar(64),
  639.     p_lname         varchar(64)
  640.     )
  641. RETURNS int AS $PROC$  
  642. DECLARE
  643.     t   int;
  644. BEGIN
  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;
  651. END;
  652. $PROC$ LANGUAGE plpgsql;
  653.  
  654. /*
  655.  * This function updates the contents of a moderators field
  656.  */
  657. CREATE OR REPLACE FUNCTION dtw.Update_moderator (
  658.     p_sid           int,
  659.     p_field         varchar(64),
  660.     p_value         anyelement
  661.     )
  662. RETURNS int AS $PROC$
  663. DECLARE
  664.     q       text;  
  665.     row1    dtw.moderator%ROWTYPE;
  666. BEGIN
  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;
  682. END;
  683. $PROC$ LANGUAGE plpgsql;
  684.  
  685. /*
  686.  * This function returns a moderator by its id
  687.  */
  688. CREATE OR REPLACE FUNCTION dtw.Get_moderator_by_id (
  689.     p_sid           int
  690.     )
  691. RETURNS dtw.moderator as $PROC$
  692. DECLARE
  693.     row1    dtw.moderator%ROWTYPE;
  694. BEGIN
  695.     select into row1 * from dtw.moderator where sid = p_sid;
  696.     return row1;
  697. END;
  698. $PROC$ LANGUAGE plpgsql;
  699.  
  700. /*
  701.  * This function returns a moderator by its short name (aka initials)
  702.  */
  703. CREATE OR REPLACE FUNCTION dtw.Get_moderator_by_short (
  704.     p_short         text
  705.     )
  706. RETURNS dtw.moderator as $PROC$
  707. DECLARE
  708.     row1    dtw.moderator%ROWTYPE;
  709. BEGIN
  710.     select into row1 * from dtw.moderator where initials = p_short;
  711.     return row1;
  712. END;
  713. $PROC$ LANGUAGE plpgsql;
  714.  
  715. /*
  716.  * This function returns a moderator record by its email
  717.  */
  718. CREATE OR REPLACE FUNCTION dtw.Get_moderator_by_email (
  719.     p_email         text
  720. )
  721. RETURNS dtw.moderator as $PROC$
  722. DECLARE
  723.     row1    dtw.moderator%ROWTYPE;
  724. BEGIN
  725.     select into row1 * from dtw.moderator where email = p_email;
  726.     return row1;
  727. END;
  728. $PROC$ LANGUAGE plpgsql;
  729.  
  730. /*
  731.  * This function returns a moderator record by user name
  732.  */
  733. CREATE OR REPLACE FUNCTION dtw.Get_moderator_by_username (
  734.     p_username      text
  735.     )
  736. RETURNS dtw.moderator as $PROC$
  737. DECLARE
  738.     row1    dtw.moderator%ROWTYPE;
  739. BEGIN
  740.     select into row1 * from dtw.moderator where username = p_username;
  741.     return row1;
  742. END;
  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.  */
  751. CREATE OR REPLACE FUNCTION dtw.Get_moderator_check_password (
  752.     p_username      text,
  753.     p_password      text
  754.     )
  755. RETURNS integer as $PROC$
  756. DECLARE
  757.     row1    dtw.moderator%ROWTYPE;
  758. BEGIN
  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;
  769. END;
  770. $PROC$ LANGUAGE plpgsql;
  771.  
  772. /*
  773.  * Returns a list of moderators by starting letters of first or last name
  774.  */
  775. CREATE OR REPLACE FUNCTION dtw.Get_all_moderator (
  776.     p_offset        int,
  777.     p_limit         int
  778.     )
  779. RETURNS SETOF dtw.moderator as $PROC$
  780. DECLARE
  781.     row1    dtw.moderator%ROWTYPE;
  782.     q       text;
  783. BEGIN
  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;
  799. END;
  800. $PROC$ LANGUAGE plpgsql;  
  801.  
  802. /*
  803. * Returns a list of youths by starting letters of first or last name
  804.  */
  805. CREATE OR REPLACE FUNCTION dtw.Get_all_moderator (
  806.     p_offset        int,
  807.     p_limit         int
  808.     )
  809. RETURNS SETOF dtw.moderator as $PROC$
  810. DECLARE
  811.     row1    dtw.moderator%ROWTYPE;
  812.     q       text;
  813. BEGIN
  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;
  829. END;
  830. $PROC$ LANGUAGE plpgsql;
  831.  
  832. /*
  833.  * This will delete all moderators records from dtw.moderator
  834.  */
  835. CREATE OR REPLACE FUNCTION dtw.Delete_all_moderator ()
  836. RETURNS void as $PROC$
  837. BEGIN
  838.     delete from dtw.moderator;
  839. END;
  840. $PROC$ LANGUAGE plpgsql;
  841.  
  842. CREATE OR REPLACE FUNCTION dtw.Delete_moderator_by_id (
  843.     p_sid       int
  844. )
  845. RETURNS void as $PROC$
  846. BEGIN
  847.     delete from dtw.moderator where sid = p_sid;
  848. END;
  849. $PROC$ LANGUAGE plpgsql;
  850.  
  851. CREATE OR REPLACE FUNCTION dtw.Delete_moderator_by_username (
  852.     p_username  text
  853. )
  854. RETURNS void as $PROC$
  855. BEGIN
  856.     delete from dtw.moderator where username = p_username;
  857. END;
  858. $PROC$ LANGUAGE plpgsql;
  859.  
  860. /* -----------------------------------------------------------------------
  861.  * Employer ...
  862.  * -----------------------------------------------------------------------
  863.  */
  864. DROP TABLE IF EXISTS dtw.emp;
  865. create 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.  */
  899. CREATE OR REPLACE FUNCTION dtw.New_employer (
  900.     p_contact_fname         text,
  901.     p_contact_lname         text,
  902.     p_contact_email         text
  903.     )
  904. RETURNS int AS $PROC$  
  905. DECLARE
  906.     t   int;
  907. BEGIN
  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;
  914. END;
  915. $PROC$ LANGUAGE plpgsql;
  916.  
  917. /*
  918.  * xxx
  919.  */
  920. CREATE OR REPLACE FUNCTION dtw.Update_employer (
  921.     p_eid           int,
  922.     p_field         varchar(64),
  923.     p_value         anyelement
  924.     )
  925. RETURNS int AS $PROC$
  926. DECLARE
  927.     q       text;
  928.     row1    dtw.emp%ROWTYPE;   
  929. BEGIN
  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;
  940. END;
  941. $PROC$ LANGUAGE plpgsql;
  942.  
  943. /*
  944.  *  Returns an employee record based on the ID
  945.  */
  946. CREATE OR REPLACE FUNCTION dtw.Get_emp_by_id (
  947.     p_eid           int
  948.     )
  949. RETURNS dtw.emp as $PROC$
  950. DECLARE
  951.     row1    dtw.emp%ROWTYPE;
  952. BEGIN
  953.     select into row1 * from dtw.emp where eid = p_eid;
  954.     return row1;
  955. END;
  956. $PROC$ LANGUAGE plpgsql;
  957.  
  958. /*
  959.  *  Returns an employee record based on the USER_ID
  960.  */
  961. CREATE OR REPLACE FUNCTION dtw.Get_emp_by_username (
  962.     p_username          text
  963.     )
  964. RETURNS dtw.emp as $PROC$
  965. DECLARE
  966.     row1    dtw.emp%ROWTYPE;
  967. BEGIN
  968.     select into row1 * from dtw.emp where username = p_username;
  969.     return row1;
  970. END;
  971. $PROC$ LANGUAGE plpgsql;
  972.  
  973. /*
  974.  *  Returns an employee record based on the email address
  975.  */
  976. CREATE OR REPLACE FUNCTION dtw.Get_emp_by_email (
  977.     p_email         text
  978.     )
  979. RETURNS dtw.emp as $PROC$
  980. DECLARE
  981.     row1    dtw.emp%ROWTYPE;
  982. BEGIN
  983.     select into row1 * from dtw.emp where contact_email = p_email;
  984.     return row1;
  985. END;
  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.  */
  994. CREATE OR REPLACE FUNCTION dtw.Get_emp_check_password (
  995.     p_username      text,
  996.     p_password      text
  997.     )
  998. RETURNS integer as $PROC$
  999. DECLARE
  1000.     row1    dtw.emp%ROWTYPE;
  1001. BEGIN
  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;
  1013. END;
  1014. $PROC$ LANGUAGE plpgsql;
  1015.  
  1016. /*
  1017.  * Xxxx
  1018.  */
  1019. CREATE OR REPLACE FUNCTION dtw.Get_all_employer (
  1020.     p_offset        int,
  1021.     p_limit         int
  1022.     )
  1023. RETURNS SETOF dtw.emp as $PROC$
  1024. DECLARE
  1025.     row1    dtw.emp%ROWTYPE;
  1026.     q       text;
  1027. BEGIN
  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;
  1043. END;
  1044. $PROC$ LANGUAGE plpgsql;
  1045.  
  1046. /*
  1047.  * Xxxx
  1048.  */
  1049. CREATE OR REPLACE FUNCTION dtw.Get_all_emp_by_contact (
  1050.     p_offset        int,
  1051.     p_limit         int
  1052.     )
  1053. RETURNS SETOF dtw.emp as $PROC$
  1054. DECLARE
  1055.     row1    dtw.emp%ROWTYPE;
  1056.     q       text;
  1057. BEGIN
  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;
  1073. END;
  1074. $PROC$ LANGUAGE plpgsql;
  1075.  
  1076. /*
  1077.  * This will delete all employer records from dtw.emp
  1078.  */
  1079. CREATE OR REPLACE FUNCTION dtw.Delete_all_emp ()
  1080. RETURNS void as $PROC$
  1081. BEGIN
  1082.     delete from dtw.emp;
  1083. END;
  1084. $PROC$ LANGUAGE plpgsql;
  1085.  
  1086. CREATE OR REPLACE FUNCTION dtw.Delete_emp_by_id (
  1087.     p_eid       int
  1088. )
  1089. RETURNS void as $PROC$
  1090. BEGIN
  1091.     delete from dtw.moderator where eid = p_eid;
  1092. END;
  1093. $PROC$ LANGUAGE plpgsql;
  1094.  
  1095. CREATE OR REPLACE FUNCTION dtw.Delete_emp_by_username (
  1096.     p_username  text
  1097. )
  1098. RETURNS void as $PROC$
  1099. BEGIN
  1100.     delete from dtw.moderator where username = p_username;
  1101. END;
  1102. $PROC$ LANGUAGE plpgsql;
  1103.  
  1104. /* -----------------------------------------------------------------------
  1105.  * Document ...
  1106.  * -----------------------------------------------------------------------
  1107.  */
  1108.  
  1109.   /* Status */
  1110. drop domain if exists dtw.doc_type_type cascade;
  1111. create 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.  
  1120. create sequence dtw.doc_seq start with 100 no cycle;   
  1121. DROP TABLE IF EXISTS dtw.doc;
  1122. create 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.  */
  1132. CREATE OR REPLACE FUNCTION dtw.New_doc(
  1133.     p_doc               text,
  1134.     p_doc_type          dtw.doc_type_type
  1135.     )
  1136. RETURNS int AS $PROC$  
  1137. DECLARE
  1138.     t   int;
  1139. BEGIN
  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;
  1146. END;
  1147. $PROC$ LANGUAGE plpgsql;
  1148.  
  1149. /*
  1150.  *  CHECK THIS -- NOT SURE IF ITS NEEDED ...
  1151.  */
  1152. CREATE 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.     )
  1158. RETURNS int AS $PROC$  
  1159. DECLARE
  1160.     sid_v       int;
  1161.     tid_v       int;
  1162.     t       int;
  1163. BEGIN
  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;
  1180. END;
  1181. $PROC$ LANGUAGE plpgsql;
  1182.  
  1183. /*
  1184.  * xxx
  1185.  */
  1186. CREATE OR REPLACE FUNCTION dtw.Find_docs (
  1187.     p_doc_type      dtw.doc_type_type,
  1188.     p_offset        int,
  1189.     p_limit         int
  1190.     )
  1191. RETURNS SETOF dtw.doc as $PROC$
  1192. DECLARE
  1193.     row1    dtw.doc%ROWTYPE;
  1194.     q       text;
  1195.     w       text;
  1196. BEGIN
  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;
  1218. END;
  1219. $PROC$ LANGUAGE plpgsql;
  1220.  
  1221. /* -----------------------------------------------------------------------
  1222.  * Properties  
  1223.  * -----------------------------------------------------------------------
  1224.  */
  1225.  
  1226. drop domain if exists dtw.prop_type_type cascade;
  1227. create 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.    
  1235. DROP TABLE IF EXISTS dtw.prop_type;
  1236. create 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.  
  1244. insert into dtw.prop_type(id,propname,proptype,shortl,descr) values (1,'tags', 'A', 'Tag list', 'Used for a list of tags');
  1245. insert into dtw.prop_type(id,propname,proptype,shortl,descr) values (2,'path', 'B', 'Path', 'Estimates of how youth are doing');
  1246.  
  1247. create sequence dtw.prop_seq start with 100 no cycle;  
  1248. DROP TABLE IF EXISTS dtw.prop;
  1249. create 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.  
  1261. CREATE 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. )
  1268. RETURNS int AS $PROC$  
  1269. DECLARE
  1270.     t   int;
  1271. BEGIN
  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;
  1287. END;
  1288. $PROC$ LANGUAGE plpgsql;
  1289.  
  1290.  
  1291. /* -----------------------------------------------------------------------
  1292.  * Job ...
  1293.  * -----------------------------------------------------------------------
  1294.  */
  1295.  
  1296.  /*
  1297.   * Jobs have a status indicator
  1298.   */
  1299. drop domain if exists dtw.job_status_type cascade;
  1300. create 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.  */
  1312. create sequence dtw.job_seq start with 100 no cycle;  
  1313. DROP TABLE IF EXISTS dtw.job;
  1314. create 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.  */
  1332. create sequence dtw.job_offer_seq start with 100 no cycle;  
  1333. DROP TABLE IF EXISTS dtw.job_offer;
  1334. create 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.  */
  1345. DROP TABLE IF EXISTS dtw.job_offer_note;
  1346. create 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.  */
  1355. CREATE OR REPLACE FUNCTION dtw.New_job_offer (
  1356.     p_jid                   int,
  1357.     p_tid                   int,
  1358.     p_sid                   int,
  1359.     p_note                  text
  1360.     )
  1361. RETURNS int AS $PROC$  
  1362. DECLARE
  1363.     t   int;
  1364.     d   int;
  1365. BEGIN
  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;
  1382. END;
  1383. $PROC$ LANGUAGE plpgsql;
  1384.  
  1385. /*
  1386.  *  This is used to add a new note to a job offer
  1387.  */
  1388. CREATE OR REPLACE FUNCTION dtw.New_note_for_job_offer (
  1389.     p_joid                  int,
  1390.     p_sid                   int,
  1391.     p_note                  text
  1392.     )
  1393. RETURNS int AS $PROC$  
  1394. DECLARE
  1395.     d   int;
  1396. BEGIN
  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;
  1403. END;
  1404. $PROC$ LANGUAGE plpgsql;  
  1405.  
  1406. /*
  1407.  *  This is used to create a new job
  1408.  */
  1409. CREATE OR REPLACE FUNCTION dtw.New_job (
  1410.     p_eid                   int,
  1411.     p_title                 text
  1412.     )
  1413. RETURNS int AS $PROC$  
  1414. DECLARE
  1415.     t   int;
  1416. BEGIN
  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;
  1423. END;
  1424. $PROC$ LANGUAGE plpgsql;
  1425.  
  1426. /*
  1427.  * This is used to update the attributes of a job
  1428.  */
  1429. CREATE OR REPLACE FUNCTION dtw.Update_job (
  1430.     p_jid           int,
  1431.     p_field         varchar(64),
  1432.     p_value         anyelement
  1433.     )
  1434. RETURNS int AS $PROC$
  1435. DECLARE
  1436.     q       text;  
  1437. BEGIN
  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;
  1441. END;
  1442. $PROC$ LANGUAGE plpgsql;
  1443.  
  1444. /*
  1445.  * This function returns information about a job
  1446.  */
  1447. CREATE OR REPLACE FUNCTION dtw.Get_job_info (
  1448.     p_job_id        int
  1449.     )
  1450. RETURNS dtw.job as $PROC$
  1451. DECLARE
  1452.     row1    dtw.job%ROWTYPE;
  1453. BEGIN
  1454.     select into row1 * from dtw.job where jid = p_job_id;
  1455.     return row1;
  1456. END;
  1457. $PROC$ LANGUAGE plpgsql;
  1458.  
  1459.  
  1460.  
  1461. /*
  1462.  * xxx
  1463.  */
  1464. /*
  1465.  * xxx
  1466.  */
  1467. CREATE 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.     )
  1473. RETURNS SETOF dtw.job as $PROC$
  1474. DECLARE
  1475.     row1                dtw.job%ROWTYPE;
  1476.     q                   text;
  1477.     status_clause       text;
  1478.     emp_clause          text;
  1479.     where_clause        text;
  1480. BEGIN
  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.  
  1529. END;
  1530. $PROC$ LANGUAGE plpgsql;
  1531.  
  1532. /*
  1533.  * This will delete all employer records from dtw.emp
  1534.  */
  1535. CREATE OR REPLACE FUNCTION dtw.Delete_all_job ()
  1536. RETURNS void as $PROC$
  1537. BEGIN
  1538.     delete from dtw.job;
  1539. END;
  1540. $PROC$ LANGUAGE plpgsql;
  1541.  
  1542. CREATE OR REPLACE FUNCTION dtw.Delete_job_by_id (
  1543.     p_jid       int
  1544. )
  1545. RETURNS void as $PROC$
  1546. BEGIN
  1547.     delete from dtw.job where jid = p_jid;
  1548. END;
  1549. $PROC$ LANGUAGE plpgsql;
  1550.  
  1551.  
  1552. /* -----------------------------------------------------------------------
  1553.  * Workflows tables  
  1554.  * -----------------------------------------------------------------------
  1555.  */
  1556.  
  1557. /* Status */
  1558. drop domain if exists dtw.node_type_domain cascade;
  1559. create 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.  
  1568. create sequence dtw.workflow_seq start with 1 no cycle;  
  1569. DROP TABLE IF EXISTS dtw.workflow;
  1570. create table dtw.workflow (
  1571.     wfid            int PRIMARY KEY DEFAULT nextval('dtw.workflow_seq'),
  1572.     name            varchar(64) UNIQUE,
  1573.     info            text
  1574. );
  1575.  
  1576. create sequence dtw.node_seq start with 1 no cycle;  
  1577. DROP TABLE IF EXISTS dtw.node;
  1578. create 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.  
  1586. create sequence dtw.edge_seq start with 1 no cycle;  
  1587. DROP TABLE IF EXISTS dtw.edge;
  1588. create 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.  */
  1603. CREATE OR REPLACE FUNCTION dtw.Create_workflow (
  1604.     p_name          varchar(64),   
  1605.     p_info          text   
  1606.     )
  1607. RETURNS int AS $PROC$  
  1608.  
  1609. DECLARE
  1610.     row1    RECORD;
  1611.     row2    RECORD;
  1612.  
  1613. BEGIN
  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;
  1636. END;
  1637. $PROC$ LANGUAGE plpgsql;
  1638.  
  1639. /*
  1640.  *  Delete workflow
  1641.  */
  1642. CREATE OR REPLACE FUNCTION dtw.Delete_workflow (
  1643.     p_name          varchar(64)
  1644.     )
  1645. RETURNS void AS $PROC$ 
  1646. BEGIN
  1647.     delete from dtw.workflow where name = p_name;
  1648. END;
  1649. $PROC$ LANGUAGE plpgsql;
  1650.  
  1651.  
  1652. /*
  1653.  * This function returns a list of all the workflows
  1654.  */
  1655. CREATE OR REPLACE FUNCTION dtw.Get_workflows ()
  1656. RETURNS SETOF dtw.workflow AS $PROC$
  1657.  
  1658. DECLARE
  1659.     row1        dtw.workflow%ROWTYPE;
  1660.  
  1661. BEGIN
  1662.     FOR row1 IN
  1663.         SELECT * FROM dtw.workflow
  1664.         LOOP
  1665.             RETURN NEXT row1;
  1666.         END LOOP;
  1667.     RETURN;
  1668. END;
  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.  */
  1681. CREATE 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.     )
  1687. RETURNS int AS $PROC$
  1688.  
  1689. DECLARE
  1690.     row1 RECORD;
  1691.     row2 RECORD;
  1692.  
  1693. BEGIN
  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;
  1709. END;
  1710. $PROC$ LANGUAGE plpgsql;
  1711.  
  1712.  
  1713. /*
  1714.  *  Return a node record by the node's short name.
  1715.  */
  1716. CREATE OR REPLACE FUNCTION dtw.Get_node (
  1717.     p_workflow_name         varchar(64),
  1718.     p_sname                 char (3)
  1719.     )
  1720. RETURNS RECORD AS $PROC$
  1721.  
  1722. DECLARE
  1723.     row1 RECORD;
  1724.  
  1725. BEGIN
  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;
  1731. END;
  1732. $PROC$ LANGUAGE plpgsql;
  1733.  
  1734. /*
  1735.  *  Return the node record by the node's id.
  1736.  */
  1737. CREATE OR REPLACE FUNCTION dtw.Get_node_by_id (
  1738.     p_nid                   int
  1739. )
  1740. RETURNS RECORD AS $PROC$
  1741.  
  1742. DECLARE
  1743.     row1 RECORD;
  1744.  
  1745. BEGIN
  1746.     select into row1 * from dtw.node where nid = p_nid;
  1747.     RETURN row1;
  1748. END;
  1749. $PROC$ LANGUAGE plpgsql;
  1750.  
  1751. /*
  1752.  * This function returns a list of all nodes for a workflow
  1753.  */
  1754. CREATE OR REPLACE FUNCTION dtw.Get_nodes (
  1755.     p_workflow_name         varchar(64)
  1756. )
  1757. RETURNS SETOF dtw.node AS $PROC$
  1758.  
  1759. DECLARE
  1760.     row1        dtw.node%ROWTYPE;
  1761.  
  1762. BEGIN
  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;
  1771. END;
  1772. $PROC$ LANGUAGE plpgsql;
  1773.  
  1774. /* -----------------------------------------------------------------------
  1775.  * Edges
  1776.  * -----------------------------------------------------------------------
  1777.  */
  1778. /*
  1779.  *  Connects the start node to a node
  1780.  */
  1781. CREATE OR REPLACE FUNCTION dtw.Link_from_start (
  1782.     p_workflow_name         varchar(64),   
  1783.     p_sname2                char(3),
  1784.     p_guard                 varchar(64)
  1785.     )
  1786. RETURNS int AS $PROC$  
  1787. BEGIN
  1788.     RETURN dtw.Link_between(p_workflow_name, '__S', p_sname2, p_guard);
  1789. END;
  1790. $PROC$ LANGUAGE plpgsql;
  1791.  
  1792. /*
  1793.  *  Connects a node to to the finish node
  1794.  */
  1795. CREATE OR REPLACE FUNCTION dtw.Link_to_finish (
  1796.     p_workflow_name         varchar(64),   
  1797.     p_sname1                char(3),
  1798.     p_guard                 varchar(64)
  1799.     )
  1800. RETURNS int AS $PROC$  
  1801. BEGIN
  1802.     RETURN dtw.Link_between(p_workflow_name, p_sname1, '__F', p_guard);
  1803. END;
  1804. $PROC$ LANGUAGE plpgsql;
  1805.  
  1806. /*
  1807.  *  This function adds an edge between two existing nodes in a workflow.
  1808.  */
  1809. CREATE 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.     )
  1815. RETURNS int AS $PROC$  
  1816.  
  1817. DECLARE
  1818.     row0 RECORD;
  1819.     row1 RECORD;
  1820.     row2 RECORD;
  1821.  
  1822. BEGIN
  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;
  1849. END;
  1850. $PROC$ LANGUAGE plpgsql;
  1851.  
  1852. /*
  1853.  * This table is used ONLY to send data about edges back to the client
  1854.  */
  1855. DROP TABLE IF EXISTS dtw.edge_info_rec;
  1856. create 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.  */
  1872. CREATE OR REPLACE FUNCTION dtw.Get_children (
  1873.     p_workflow_name         varchar(64),   
  1874.     p_sname                 char(3)
  1875.     )
  1876. RETURNS SETOF dtw.edge_info_rec AS $PROC$
  1877.  
  1878. DECLARE
  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.  
  1890. BEGIN
  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;
  1922. END;
  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.  
  1935. SPREAD THIS ..
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand