--
-- packages/acs-kernel/sql/test/rel-constraints-test.sql
--
-- @author oumi@arsdigita.com
-- @creation-date 2000-12-02
-- @cvs-id rel-constraints-test.sql,v 1.1.4.1 2001/01/12 23:06:33 oumi Exp
--

-- set serveroutput on
create table groups_test_groups (
       group_id      integer,
       sorder        integer,
       gname         varchar(100)
);

create table groups_test_users (
       user_id     integer,
       sorder      integer,
       uname       varchar(100)
);

CREATE OR REPLACE FUNCTION rel_constraint_dump_views() RETURNS integer AS $$
       r        record;
       str      varchar;
BEGIN

   raise NOTICE 'Contents of view 'rel_constraints_violated_one':';

   for r in select * from rel_constraints_violated_one 
   LOOP
      str := rpad(r.constraint_id, 10) ||
             rpad(r.rel_id, 10) ||
             rpad(acs_object__name(r.container_id), 20) ||
             rpad(acs_object__name(r.party_id), 20);

      raise NOTICE '%', str;
   end LOOP;


   raise NOTICE 'Contents of view 'rel_constraints_violated_two':';

   for r in select * from rel_constraints_violated_two 
   LOOP
      str := rpad(r.constraint_id, 10) ||
             rpad(r.rel_id, 10) ||
             rpad(acs_object__name(r.container_id), 20) ||
             rpad(acs_object__name(r.party_id), 20);

      raise NOTICE '%', str;
   end loop;

   return null;

END;
$$ LANGUAGE plpgsql;



-- added
select define_function_args('rel_constraint_test_check','rel_id,expect_violation_p');

--
-- procedure rel_constraint_test_check/2
--
CREATE OR REPLACE FUNCTION rel_constraint_test_check(
   rel_id integer,
   expect_violation_p boolean
) RETURNS integer AS $$
DECLARE
  v_violation_msg     varchar(4000);
  v_violation_p       boolean;
  v_object_id_one     integer;
  v_object_id_two     integer;
  v_rel_type          acs_rels.rel_type%TYPE;
  str                 varchar;
BEGIN

  v_violation_p := 'f';

  v_violation_msg := rel_constraint__violation(rel_id);

  if v_violation_msg is not null then
     v_violation_p := 't';
  end if;

  if v_violation_p != expect_violation_p then

      select object_id_one, object_id_two, rel_type
      into v_object_id_one, v_object_id_two, v_rel_type
      from acs_rels
      where rel_id = rel_constraint_test_check.rel_id;

      str :=        'Relation ' || acs_object_name(rel_id) || 
                    ' (' || rel_id || ')' ||
                    ' failed (violation_p = ' || v_violation_p || ').  ' ||
                    'Rel info: type = ' || v_rel_type || 
                    ', object one = ' ||
                    acs_object__name(v_object_id_one) || 
                    ' (' || v_object_id_one || ')' || 
                    ', object two = ' ||
                    acs_object__name(v_object_id_two) || 
                    ' (' || v_object_id_two || ').';

      PERFORM acs_log__error('rel_constraint_test_check', str);

      raise NOTICE '%', str;

      raise NOTICE 'Violation Message:';
      raise NOTICE '%', v_violation_msg;

  end if;

  return null;

END;
$$ LANGUAGE plpgsql; 

-- creates blah_member_rel and yippe_member_rel relationships

\i rel-segments-test-types-create.sql



--
-- procedure test_rel_constraints/0
--
CREATE OR REPLACE FUNCTION test_rel_constraints(

) RETURNS integer AS $$
DECLARE
  A      integer;
  B      integer;
  C      integer;
  D      integer;
  E      integer;
  F      integer;
  G      integer;

  joe    integer;
  jane   integer;
  bob    integer;
  betty  integer;
  jack	 integer;
  jill	 integer;
  sven	 integer;
  stacy	 integer;

  reg_users integer;

  rel_id integer;

  side_one_constraint integer;
  side_two_constraint integer;

  v_count integer;

  r       record;

BEGIN
  -- Create the test groups.
  A := acs_group__new('A');
  B := acs_group__new('B');
  C := acs_group__new('C');
  D := acs_group__new('D');
  E := acs_group__new('E');
  F := acs_group__new('F');
  G := acs_group__new('G');

  insert into groups_test_groups values (A,1,'A');
  insert into groups_test_groups values (B,2,'B');
  insert into groups_test_groups values (C,3,'C');
  insert into groups_test_groups values (D,4,'D');
  insert into groups_test_groups values (E,5,'E');
  insert into groups_test_groups values (F,6,'F');  
  insert into groups_test_groups values (G,7,'G');

  -- Create the test members.
  joe   := acs_user__new('joe@asdf.com','Joe',
                         'Smith','assword','p');
  jane  := acs_user__new('jane@asdf.com','Jane',
                         'Smith','assword','p');
  bob   := acs_user__new('bob@asdf.com','Bob',
                         'Smith','assword','p');
  betty := acs_user__new('betty@asdf.com','Betty',
                         'Smith','assword','p');
  jack  := acs_user__new('jack@asdf.com','Jack',
                         'Smith','assword','p');
  jill  := acs_user__new('jill@asdf.com','Jill',
                         'Smith','assword','p');
  sven  := acs_user__new('sven@asdf.com','Sven',
                         'Smith','assword','p');
  stacy := acs_user__new('stacy@asdf.com','Stacy',
                         'Smith','assword','p');

  insert into groups_test_users values (joe,1,'joe');
  insert into groups_test_users values (jane,2,'jane');
  insert into groups_test_users values (bob,3,'bob');
  insert into groups_test_users values (betty,4,'betty');
  insert into groups_test_users values (jack,5,'jack');
  insert into groups_test_users values (jill,6,'jill');  
  insert into groups_test_users values (sven,7,'sven');
  insert into groups_test_users values (stacy,8,'stacy');

  -- Make a couple of compositions.

  reg_users := acs__magic_object_id('registered_users');

  rel_id := composition_rel__new(A, B);
  rel_id := composition_rel__new(A, C);
  rel_id := composition_rel__new(A, D);

  rel_id := composition_rel__new(E, A);
  rel_id := composition_rel__new(E, F);

  rel_id := composition_rel__new(reg_users, E);
  rel_id := composition_rel__new(reg_users, G);
  -- define a few segments.

  -- define a few relational constraints.

 
  side_two_constraint := rel_constraint__new(
                                null,
                                'rel_constraint',
                                'Yippe: side 2 must be a blah of A',
                                rel_segment__get_or_new(reg_users, 
                                                        'yippe_member_rel', 
                                                        null),
                                'two',
                                rel_segment__get_or_new(A,
                                                        'blah_member_rel', 
                                                        null),
                                null,
                                null,
                                null
                                
  );


  side_one_constraint := rel_constraint__new(
                                null,
                                'rel_constraint',
                                'Yippe: side 1 must be a component of E',
                                rel_segment__get_or_new(reg_users, 
                                                        'yippe_member_rel', 
                                                        null),
                                'one',
                                rel_segment__get_or_new(E, 
                                                        'composition_rel', 
                                                        null),
                                null,
                                null,
                                null
  );


  delete from acs_logs;


  -- Make a couple of memberships.

  -- LEGAL MEMBERSHIPS:

  -- textbook case: 
  -- joe is a blah of A, and F is component of E, so its legal to make joe
  -- a yippe of F.

  rel_id := blah_member_rel__new(null, 'blah_member_rel', A, joe);

  PERFORM rel_constraint_test_check(rel_id, 'f');

  rel_id := yippe_member_rel__new(null, 'yippie_member_rel', F, joe);

  PERFORM rel_constraint_test_check(rel_id, 'f');

  -- do constraints respect group hierarchy? If so, this will be legal:
  rel_id := blah_member_rel__new(null, 'blah_member_rel', B, jane);

  PERFORM rel_constraint_test_check(rel_id, 'f');

  rel_id := yippe_member_rel__new(null, 'yippie_member_rel', F, jane);

  PERFORM rel_constraint_test_check(rel_id, 'f');

  -- ILLEGAL MEMBERSHIPS:
  
  -- G is not a component of F, therefore no one can be a yippe of G
  -- This should violated 2 constraints (object one and object two are both
  -- invalid).
  rel_id := yippe_member_rel__new(null, 'yippie_member_rel', G, bob);

  PERFORM rel_constraint_test_check(rel_id, 't');

  -- betty is not a blah of A, therefore she cannot be a yippe of F.
  rel_id := yippe_member_rel__new(null, 'yippie_member_rel', F, betty);

  PERFORM rel_constraint_test_check(rel_id, 't');

  -- make sven be a regular member of A.  Sven cannot be a yippe of F.
  rel_id := membership_rel__new(A, sven);
  rel_id := yippe_member_rel__new(null, 'yippie_member_rel', F, sven);

  PERFORM rel_constraint_test_check(rel_id, 't');

  -- TEST THE VIEWS (there should be 4 violated constraints,
  -- 1 side one violation and 3 side two violations.

  select count(*) into v_count
  from rel_constraints_violated_one;

  if v_count != 1 then
     raise NOTICE 'rel_constraints_violated_one should have 1 row. Found % rows.', 
                  v_count;
     PERFORM rel_constraint_dump_views();
  end if;

  select count(*) into v_count
  from rel_constraints_violated_two;

  if v_count != 3 then
     raise NOTICE 'rel_constraints_violated_two should have 2 rows. Found % rows.', 
                  v_count;
     PERFORM rel_constraint_dump_views();
  end if;


  -- Remove the constraints
  PERFORM rel_constraint__delete(side_one_constraint);
  PERFORM rel_constraint__delete(side_two_constraint);

  -- Remove the test memebership relations
  for r in select * from blah_member_rels LOOP
    PERFORM blah_member_rel__delete(r.rel_id);
  end loop;

  for r in select * from yippe_member_rels LOOP
    PERFORM yippe_member_rel__delete(r.rel_id);
  end loop;

  -- Remove the test segments.
  PERFORM rel_segment__delete(rel_segment__get(A,'blah_member_rel'));
  PERFORM rel_segment__delete(rel_segment__get(E,'composition_rel'));
  PERFORM rel_segment__delete(rel_segment__get(reg_users,'yippe_member_rel'));

  -- Remove the test groups.
  PERFORM acs_group__delete(G);
  PERFORM acs_group__delete(F);
  PERFORM acs_group__delete(E);
  PERFORM acs_group__delete(D);
  PERFORM acs_group__delete(C);
  PERFORM acs_group__delete(B);
  PERFORM acs_group__delete(A);

  -- Remove the test members.
  PERFORM acs_user__delete(joe);
  PERFORM acs_user__delete(jane);
  PERFORM acs_user__delete(bob);
  PERFORM acs_user__delete(betty);
  PERFORM acs_user__delete(jack);
  PERFORM acs_user__delete(jill);
  PERFORM acs_user__delete(sven);
  PERFORM acs_user__delete(stacy);

  return null;

END;
$$ LANGUAGE plpgsql; 


select test_rel_constraints();
drop function rel_constraint_dump_views();
drop function rel_constraint_test_check (integer, boolean);
drop function test_rel_constraints();
drop table groups_test_groups;
drop table groups_test_users;

\i rel-segments-test-types-drop.sql

select log_level, log_key, message
from acs_logs
where log_key = 'error';