 --
-- acs-workflow/sql/workflow-package.sql
--
-- Creates the PL/SQL package that provides the API for defining and dropping
-- workflow cases.
--
-- @author Lars Pind (lars@pinds.com)
--
-- @creation-date 2000-05-18
--
-- @cvs-id $Id: workflow-package.sql,v 1.1 2001/04/11 04:47:19 danw Exp $
--


-- create or replace package workflow
-- as
-- 
--     /* Call this function after you have created a table */
--     function create_workflow (
--         workflow_key            in varchar2,
--         pretty_name             in varchar2, 
--         pretty_plural           in varchar2 default null,
--         description             in varchar2 default null,
--         table_name              in varchar2,
--         id_column               in varchar2 default 'case_id'
--     ) return varchar2;
-- 
--     procedure drop_workflow (
--         workflow_key            in varchar2
--     );
-- 
--     procedure delete_cases (
--         workflow_key            in varchar2
--     );
-- 
--     function create_attribute ( 
--         workflow_key            in varchar2,
--         attribute_name          in varchar2,
--         datatype                in varchar2,
--         pretty_name             in varchar2,
--         pretty_plural           in varchar2 default null,
--         table_name              in varchar2 default null,
--         column_name             in varchar2 default null,
--         default_value           in varchar2 default null,
--         min_n_values            in integer default 1,
--         max_n_values            in integer default 1,
--         sort_order              in integer default null,
--         storage                 in varchar2 default 'generic',
--         wf_datatype             in varchar2 default 'none'
--     ) return acs_attributes.attribute_id%TYPE;
-- 
--     procedure drop_attribute (
--         workflow_key            in varchar2,
--         attribute_name          in varchar2
--     );
-- 
--     procedure add_place (
--         workflow_key            in acs_object_types.object_type%TYPE,
--         place_key               in wf_places.place_key%TYPE,
--         place_name              in wf_places.place_name%TYPE,
--         sort_order              in wf_places.sort_order%TYPE
--     );
-- 
--     procedure delete_place (
--         workflow_key            in acs_object_types.object_type%TYPE,
--         place_key               in wf_places.place_key%TYPE
--     );
-- 
--     procedure add_transition (
--         workflow_key            in acs_object_types.object_type%TYPE,
--         transition_key          in wf_transitions.transition_key%TYPE,
--         transition_name         in wf_transitions.transition_name%TYPE,
--         sort_order              in wf_transitions.sort_order%TYPE,
--         trigger_type            in wf_transitions.trigger_type%TYPE default 'user'
--     );
-- 
--     procedure delete_transition (
--         workflow_key            in acs_object_types.object_type%TYPE,
--         transition_key          in wf_transitions.transition_key%TYPE
--     );
-- 
--     procedure add_arc (
--         workflow_key            in acs_object_types.object_type%TYPE,
--         transition_key          in wf_arcs.transition_key%TYPE,
--         place_key               in wf_arcs.place_key%TYPE,
--         direction               in wf_arcs.direction%TYPE,
--         guard_callback          in wf_arcs.guard_callback%TYPE default null,
--         guard_custom_arg        in wf_arcs.guard_custom_arg%TYPE default null,
--         guard_description       in wf_arcs.guard_description%TYPE default null
--     );
-- 
--     procedure delete_arc (
--         workflow_key            in acs_object_types.object_type%TYPE,
--         transition_key          in wf_arcs.transition_key%TYPE,
--         place_key               in wf_arcs.place_key%TYPE,
--         direction               in wf_arcs.direction%TYPE
--     );
-- 
--     /*
--      * A simple workflow is essentially one that we can display nicely using HTML tables.
--      * More specifically, it's a workflow containing only sequential routing and 
--      * simple iteration, where the choice is always between moving to the next task
--      * in the sequence or looping back to some prior task.
--      */
-- 
--     function simple_p (
--         workflow_key    in varchar2
--     ) return char;
-- 
-- end workflow;

-- show errors

-- create or replace package body workflow 
-- function create_workflow
create function workflow__create_workflow (varchar,varchar,varchar,varchar,varchar,varchar)
returns integer as '
declare
  create_workflow__workflow_key    alias for $1;  
  create_workflow__pretty_name     alias for $2;  
  create_workflow__pretty_plural   alias for $3;  
  create_workflow__description     alias for $4;  
  create_workflow__table_name      alias for $5;  
  create_workflow__id_column       alias for $6;  
  v_num_rows                       number;
  v_workflow_key                   varchar;
begin
        select count(*) into v_num_rows 
        from user_tables 
        where table_name = upper(create_workflow__table_name);

        if v_num_rows = 0 then
            raise EXCEPTION ''-20000: The table \\\'%\\\'must be created before calling workflow.create_workflow.'', create_workflow__table_name;
        end if;

        if substr(create_workflow__workflow_key, 
                  length(create_workflow__workflow_key) - 2, 3) != ''_wf'' then
            v_workflow_key := create_workflow__workflow_key || ''_wf'';
        else
            v_workflow_key := create_workflow__workflow_key;
        end if;

        PERFORM acs_object_type__create_type(
            v_workflow_key, 
            create_workflow__pretty_name, 
            create_workflow__pretty_plural,
            ''workflow'',
            create_workflow__table_name,
            create_workflow__id_column,
            null,
            ''f'',
            null,
            null
        );

        insert into wf_workflows 
            (workflow_key, description) 
        values 
            (v_workflow_key, create_workflow__description);

        return v_workflow_key;
     
end;' language 'plpgsql';


-- procedure drop_workflow
create function workflow__drop_workflow (varchar)
returns integer as '
declare
  drop_workflow__workflow_key      alias for $1;  
  v_table_name                     varchar;  
  v_num_rows                       number;        
  attribute_rec                    record;
begin
        select table_name into v_table_name 
        from   acs_object_types
        where  object_type = drop_workflow__workflow_key;

        select case when count(*) = 0 then 0 else 1 end into v_num_rows 
        from   user_tables
        where  table_name = upper(v_table_name);

        if v_num_rows > 0 then
            raise EXCEPTION ''-20000: The table \\\'%\\\' must be dropped before calling workflow.drop_workflow.'', v_table_name;
        end if;

        select case when count(*) = 0 then 0 else 1 end into v_num_rows 
        from   wf_cases
        where  workflow_key = drop_workflow__workflow_key;

        if v_num_rows > 0 then
            raise EXCEPTION ''-20000: You must delete all cases of workflow \\\'%\\\' before dropping the workflow definition.'', drop_workflow__workflow_key;
        end if;

        /* Delete all the auxillary stuff */
        delete from wf_context_task_panels 
        where workflow_key = drop_workflow__workflow_key;

        delete from wf_context_assignments 
        where workflow_key = drop_workflow__workflow_key;

        delete from wf_context_transition_info 
        where workflow_key = drop_workflow__workflow_key; 

        delete from wf_arcs 
        where workflow_key = drop_workflow__workflow_key;

        delete from wf_places 
        where workflow_key = drop_workflow__workflow_key;

        delete from wf_transition_attribute_map 
        where workflow_key = drop_workflow__workflow_key;

        delete from wf_transition_assignment_map 
        where workflow_key = drop_workflow__workflow_key;

        delete from wf_transitions 
        where workflow_key = drop_workflow__workflow_key;

        /* Drop all attributes */
        for attribute_rec in 
        select attribute_id, attribute_name 
        from acs_attributes 
        where object_type = drop_workflow__workflow_key
        LOOP
            /* there''s no on delete cascade, so we have to manually 
             * delete all the values 
             */

            delete from acs_attribute_values 
            where attribute_id = attribute_rec.attribute_id;

            PERFORM workflow__drop_attribute(
                drop_workflow__workflow_key,
                attribute_rec.attribute_name
            );
        end loop;

        /* Delete the workflow */
        delete from wf_workflows 
        where workflow_key = drop_workflow__workflow_key;
        
        PERFORM acs_object_type__drop_type(
            drop_workflow__workflow_key,
            ''f''
        );

        return 0; 
end;' language 'plpgsql';


-- procedure delete_cases
create function workflow__delete_cases (varchar)
returns integer as '
declare
  delete_cases__workflow_key       alias for $1; 
  case_rec                         record; 
begin
        for case_rec in 
        select case_id 
        from wf_cases 
        where workflow_key = delete_cases__workflow_key
        LOOP
            PERFORM workflow_case__delete(case_rec.case_id);
        end loop;

        return 0; 
end;' language 'plpgsql';


-- function create_attribute
create function workflow__create_attribute (varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,integer,varchar,varchar)
returns integer as '
declare
  create_attribute__workflow_key   alias for $1;  
  create_attribute__attribute_name alias for $2;  
  create_attribute__datatype       alias for $3;  
  create_attribute__pretty_name    alias for $4;  
  create_attribute__pretty_plural  alias for $5;  
  create_attribute__table_name     alias for $6;  
  create_attribute__column_name    alias for $7;  
  create_attribute__default_value  alias for $8;  
  create_attribute__min_n_values   alias for $9;  
  create_attribute__max_n_values   alias for $10; 
  create_attribute__sort_order     alias for $11; 
  create_attribute__storage        alias for $12; 
  create_attribute__wf_datatype    alias for $13; 
  v_attribute_id                   number;        
begin
        v_attribute_id := acs_attribute__create_attribute(
            create_attribute__workflow_key,
            create_attribute__attribute_name,
            create_attribute__datatype,
            create_attribute__pretty_name,
            create_attribute__pretty_plural,
            create_attribute__table_name,
            create_attribute__column_name,
            create_attribute__default_value,
            create_attribute__min_n_values,
            create_attribute__max_n_values,
            create_attribute__sort_order,
            create_attribute__storage,
            ''f''
        );
        
        insert into wf_attribute_info
            (attribute_id, wf_datatype)
        values
            (v_attribute_id, create_attribute__wf_datatype);

        return v_attribute_id;
     
end;' language 'plpgsql';


-- procedure drop_attribute
create function workflow__drop_attribute (varchar,varchar)
returns integer as '
declare
  drop_attribute__workflow_key     alias for $1;  
  drop_attribute__attribute_name   alias for $2;  
  v_attribute_id                   number;        
begin
        select attribute_id into v_attribute_id
        from   acs_attributes
        where  object_type = drop_attribute__workflow_key
        and    attribute_name = drop_attribute__attribute_name;
        
        delete from wf_attribute_info 
        where attribute_id = v_attribute_id;

        PERFORM acs_attribute__drop_attribute(
            drop_attribute__workflow_key,
            drop_attribute__attribute_name
        );

        return 0; 
end;' language 'plpgsql';


-- procedure add_place
create function workflow__add_place (varchar,varchar,varchar,integer)
returns integer as '
declare
  add_place__workflow_key          alias for $1;  
  add_place__place_key             alias for $2;  
  add_place__place_name            alias for $3;  
  add_place__sort_order            alias for $4;  
begin
        insert into wf_places (workflow_key, place_key, place_name, sort_order)
        values 
        (add_place__workflow_key, add_place__place_key,add_place__place_name, 
        add_place__sort_order);

        return 0; 
end;' language 'plpgsql';


-- procedure delete_place
create function workflow__delete_place (varchar,varchar)
returns integer as '
declare
  delete_place__workflow_key       alias for $1;  
  delete_place__place_key          alias for $2;  
begin
        delete from wf_places
        where  workflow_key = delete_place__workflow_key 
        and    place_key = delete_place__place_key;

        return 0; 
end;' language 'plpgsql';


-- procedure add_transition
create function workflow__add_transition (varchar,varchar,varchar,integer,varchar)
returns integer as '
declare
  add_transition__workflow_key     alias for $1;  
  add_transition__transition_key   alias for $2;  
  add_transition__transition_name   alias for $3;  
  add_transition__sort_order       alias for $4;  
  add_transition__trigger_type     alias for $5;  
begin
        insert into wf_transitions 
        (workflow_key, transition_key, transition_name, sort_order, trigger_type)
        values 
        (add_transition__workflow_key, add_transition__transition_key, 
        add_transition__transition_name,add_transition__sort_order, 
        add_transition__trigger_type);

        return 0; 
end;' language 'plpgsql';


-- procedure delete_transition
create function workflow__delete_transition (varchar,varchar)
returns integer as '
declare
  delete_transition__workflow_key       alias for $1;  
  delete_transition__transition_key     alias for $2;  
begin
        delete from wf_transitions
        where  workflow_key = delete_transition__workflow_key
        and    transition_key = delete_transition__transition_key;

        return 0; 
end;' language 'plpgsql';


-- procedure add_arc
create function workflow__add_arc (varchar,varchar,varchar,varchar,<=>,varchar,varchar)
returns integer as '
declare
  add_arc__workflow_key            alias for $1;  
  add_arc__transition_key          alias for $2;  
  add_arc__place_key               alias for $3;  
  add_arc__direction               alias for $4;  
  add_arc__guard_callback          alias for $5;  
  add_arc__guard_custom_arg        alias for $6;  
  add_arc__guard_description       alias for $7;  
begin
        insert into wf_arcs 
        (workflow_key, transition_key, place_key, direction,
        guard_callback, guard_custom_arg, guard_description)
        values 
        (add_arc__workflow_key, add_arc__transition_key, add_arc__place_key, 
        add_arc__direction,
        add_arc__guard_callback, add_arc__guard_custom_arg, add_arc__guard_description);

        return 0; 
end;' language 'plpgsql';


-- procedure delete_arc
create function workflow__delete_arc (varchar,varchar,varchar,varchar)
returns integer as '
declare
  delete_arc__workflow_key         alias for $1;  
  delete_arc__transition_key       alias for $2;  
  delete_arc__place_key            alias for $3;  
  delete_arc__direction            alias for $4;  
begin
        delete from wf_arcs
        where  workflow_key = delete_arc__workflow_key
        and    transition_key = delete_arc__transition_key
        and    place_key = delete_arc__place_key
        and    direction = delete_arc__direction;

        return 0; 
end;' language 'plpgsql';


-- function simple_p
create function workflow__simple_p (varchar)
returns boolean as '
declare
  simple_p__workflow_key           alias for $1;  
  -- previous_place_list              t_place_table; 
  -- target_place_list                t_place_table; 
  -- guard_list                       t_guard_table; 
  guard_list_1                     varchar;
  guard_list_2                     varchar;
  target_place_list_1              varchar;
  target_place_list_2              varchar;
  previous_place_list_i            varchar;
  row_count                        integer default 0;       
  v_count                          integer;       
  v_count2                         integer;       
  v_place_key                      wf_places.place_key%TYPE;
  v_end_place                      wf_places.place_key%TYPE;
  v_transition_key                 wf_transitions.transition_key%TYPE;
begin
        /* Let''s do some simple checks first */

        /* Places with more than one arc out */
        select count(*) into v_count
        from   wf_places p
        where  p.workflow_key = simple_p__workflow_key
        and    1 < (select count(*) 
                    from   wf_arcs a
                    where  a.workflow_key = p.workflow_key
                    and    a.place_key = p.place_key
                    and    direction = ''in'');

        if v_count > 0 then
            return ''f'';
        end if;

        /* Transitions with more than one arc in */
        select count(*) into v_count
        from   wf_transitions t
        where  t.workflow_key = simple_p__workflow_key
        and    1 < (select count(*)
                    from   wf_arcs a
                    where  a.workflow_key = t.workflow_key
                    and    a.transition_key = t.transition_key
                    and    direction = ''in'');

        if v_count > 0 then
            return ''f'';
        end if;

        /* Transitions with more than two arcs out */
        select count(*) into v_count
        from   wf_transitions t
        where  t.workflow_key = simple_p__workflow_key
        and    2 < (select count(*)
                    from   wf_arcs a
                    where  a.workflow_key = t.workflow_key
                    and    a.transition_key = t.transition_key
                    and    direction = ''out'');

        if v_count > 0 then
            return ''f'';
        end if;

        /* Now we do the more complicated checks.
         * We keep a list of visited places because I couldn''t think
         * of a nicer way that wasn''t susceptable to infinite loops.
         */


        v_place_key := ''start'';
        v_end_place := ''end'';

        create temporary table previous_place_list (
               rcnt integer primary key,
               ky   varchar(100)
               );

        create temporary table target_place_list (
               rcnt integer primary key,
               ky   varchar(100)
               );

        create temporary table guard_list (
               rcnt integer primary key,
               ky   varchar(100)
               );

        loop
            exit when v_place_key = v_end_place;

            -- previous_place_list(row_count) := v_place_key;
            insert into previous_place_list 
            (rcnt,ky) 
            values 
            (row_count,v_place_key);

            select distinct transition_key into v_transition_key
            from   wf_arcs
            where  workflow_key = simple_p__workflow_key
            and    place_key = v_place_key
            and    direction = ''in'';

            select count(*) into v_count
            from wf_arcs
            where workflow_key = simple_p__workflow_key
            and   transition_key = v_transition_key
            and   direction = ''out'';

            if v_count = 1 then
                select unique place_key into v_place_key
                from wf_arcs
                where workflow_key = simple_p__workflow_key
                and   transition_key = v_transition_key
                and   direction = ''out'';

            else if v_count = 0 then
                /* deadend! */
                return ''f'';

            else
                /* better be two based on our earlier test */

                for v_target in 
                select place_key,guard_callback,rownum
                from   wf_arcs
                where  workflow_key = simple_p.workflow_key
                and    transition_key = v_transition_key
                and    direction = ''out''
                LOOP
                    -- target_place_list(v_target.rownum) := v_target.place_key;
                    insert into target_place_list 
                    (rcnt,ky) 
                    values 
                    (v_target.rownum,v_target.place_key);

                    -- guard_list(v_target.rownum) := v_target.guard_callback; 
                    insert into guard_list 
                    (rcnt,ky) 
                    values 
                    (row_count,v_place_key);
                end loop;
    
                /* Check that the guard functions are the negation of each other 
                 * by looking for the magic entry "#" (exactly once)
                 */
                select ky into guard_list_1 from guard_list where rcnt = 1;
                select ky into guard_list_2 from guard_list where rcnt = 2;

                if ((guard_list_1 != ''#'' and guard_list_2 != ''#'') or
                    (guard_list_1 = ''#'' and guard_list_2 = ''#'')) then
                    return ''f'';
                end if;
    
                /* Check that exactly one of the targets is in the previous list */

                v_count2 := 0;
                select ky into target_place_list_1      
                from target_place_list where rcnt = 1;

                select ky into target_place_list_2 
                from target_place_list where rcnt = 2;                

                for i in 0..row_count LOOP
                    select ky into previous_place_list_i from previous_place_list
                    where rcont = i;
                    if target_place_list_1 = previous_place_list_i then
                        v_count2 := v_count2 + 1;
                        v_place_key := target_place_list_2;
                    end if;
                    if target_place_list_2 = previous_place_list_i then
                        v_count2 := v_count2 + 1;
                        v_place_key := target_place_list_1;
                    end if;
                end loop;

                if v_count2 != 1 then
                    return ''f'';
                end if;

            end if; end if;

            row_count := row_count + 1;

        end loop;

        /* if we got here, it must be okay */
        return ''t'';

     
end;' language 'plpgsql';



-- show errors
