-- -- packages/acs-kernel/sql/test/rel-constraints-test.sql -- -- @author oumi@arsdigita.com -- @creation-date 2000-12-02 -- @cvs-id $Id: rel-constraints-test.sql,v 1.1.1.1 2002/07/09 17:34:58 rmello Exp $ -- set serveroutput on create or replace procedure rel_constraint_dump_views is begin dbms_output.put_line(' '); dbms_output.put_line('Contents of view ''rel_constraints_violated_one'':'); for r in (select * from rel_constraints_violated_one) loop dbms_output.put_line(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)); end loop; dbms_output.put_line(' '); dbms_output.put_line('Contents of view ''rel_constraints_violated_two'':'); for r in (select * from rel_constraints_violated_two) loop dbms_output.put_line(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)); end loop; end rel_constraint_dump_views; / show errors create or replace procedure rel_constraint_test_check ( rel_id integer, expect_violation_p char ) is v_violation_msg varchar(4000); v_violation_p char; v_object_id_one integer; v_object_id_two integer; v_rel_type acs_rels.rel_type%TYPE; 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; acs_log.error('rel_constraint_test_check', '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 || ').'); dbms_output.put_line('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 || ').'); dbms_output.put_line('Violation Message:'); dbms_output.put_line(v_violation_msg); end if; end rel_constraint_test_check; / show errors -- creates blah_member_rel and yippe_member_rel relationships @rel-segments-test-types-create.sql 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; begin -- Create the test groups. A := acs_group.new(group_name => 'A'); B := acs_group.new(group_name => 'B'); C := acs_group.new(group_name => 'C'); D := acs_group.new(group_name => 'D'); E := acs_group.new(group_name => 'E'); F := acs_group.new(group_name => 'F'); G := acs_group.new(group_name => 'G'); -- Create the test members. joe := acs_user.new(email => 'joe@asdf.com', first_names => 'Joe', last_name => 'Smith', password => 'assword', salt => 'p'); jane := acs_user.new(email => 'jane@asdf.com', first_names => 'Jane', last_name => 'Smith', password => 'assword', salt => 'p'); bob := acs_user.new(email => 'bob@asdf.com', first_names => 'Bob', last_name => 'Smith', password => 'assword', salt => 'p'); betty := acs_user.new(email => 'betty@asdf.com', first_names => 'Betty', last_name => 'Smith', password => 'assword', salt => 'p'); jack := acs_user.new(email => 'jack@asdf.com', first_names => 'Jack', last_name => 'Smith', password => 'assword', salt => 'p'); jill := acs_user.new(email => 'jill@asdf.com', first_names => 'Jill', last_name => 'Smith', password => 'assword', salt => 'p'); sven := acs_user.new(email => 'sven@asdf.com', first_names => 'Sven', last_name => 'Smith', password => 'assword', salt => 'p'); stacy := acs_user.new(email => 'stacy@asdf.com', first_names => 'Stacy', last_name => 'Smith', password => 'assword', salt => 'p'); -- Make a couple of compositions. reg_users := acs.magic_object_id('registered_users'); rel_id := composition_rel.new(object_id_one => A, object_id_two => B); rel_id := composition_rel.new(object_id_one => A, object_id_two => C); rel_id := composition_rel.new(object_id_one => A, object_id_two => D); rel_id := composition_rel.new(object_id_one => E, object_id_two => A); rel_id := composition_rel.new(object_id_one => E, object_id_two => F); rel_id := composition_rel.new(object_id_one=>reg_users, object_id_two=>E); rel_id := composition_rel.new(object_id_one=>reg_users, object_id_two=>G); -- define a few segments. -- define a few relational constraints. side_two_constraint := rel_constraint.new( constraint_name => 'Yippe: side 2 must be a ''blah'' of A', rel_segment => acs_rel_segment.get_or_new(reg_users, 'yippe_member_rel'), rel_side => 'two', required_rel_segment => acs_rel_segment.get_or_new(A,'blah_member_rel') ); side_one_constraint := rel_constraint.new( constraint_name => 'Yippe: side 1 must be a component of E', rel_segment => acs_rel_segment.get_or_new(reg_users, 'yippe_member_rel'), rel_side => 'one', required_rel_segment => acs_rel_segment.get_or_new(E, 'composition_rel') ); 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(object_id_one => A, object_id_two => joe); rel_constraint_test_check(rel_id => rel_id, expect_violation_p => 'f'); rel_id := yippe_member_rel.new(object_id_one => F, object_id_two => joe); rel_constraint_test_check(rel_id => rel_id, expect_violation_p => 'f'); -- do constraints respect group hierarchy? If so, this will be legal: rel_id := blah_member_rel.new(object_id_one => B, object_id_two => jane); rel_constraint_test_check(rel_id => rel_id, expect_violation_p => 'f'); rel_id := yippe_member_rel.new(object_id_one => F, object_id_two => jane); rel_constraint_test_check(rel_id => rel_id, expect_violation_p => '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(object_id_one => G, object_id_two => bob); rel_constraint_test_check(rel_id => rel_id, expect_violation_p => 't'); -- betty is not a blah of A, therefore she cannot be a yippe of F. rel_id := yippe_member_rel.new(object_id_one => F, object_id_two => betty); rel_constraint_test_check(rel_id => rel_id, expect_violation_p => 't'); -- make sven be a regular member of A. Sven cannot be a yippe of F. rel_id := membership_rel.new(object_id_one => A, object_id_two => sven); rel_id := yippe_member_rel.new(object_id_one => F, object_id_two => sven); rel_constraint_test_check(rel_id => rel_id, expect_violation_p => '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 dbms_output.put_line ('rel_constraints_violated_one should have 1 row.' || ' Found ' || v_count || ' rows.'); rel_constraint_dump_views; end if; select count(*) into v_count from rel_constraints_violated_two; if v_count != 3 then dbms_output.put_line ('rel_constraints_violated_two should have 2 rows.' || ' Found ' || v_count || ' rows.'); rel_constraint_dump_views; end if; -- Remove the constraints rel_constraint.delete(side_one_constraint); rel_constraint.delete(side_two_constraint); -- Remove the test memebership relations for r in (select * from blah_member_rels) loop blah_member_rel.delete(r.rel_id); end loop; for r in (select * from yippe_member_rels) loop yippe_member_rel.delete(r.rel_id); end loop; -- Remove the test segments. acs_rel_segment.delete(acs_rel_segment.get(A,'blah_member_rel')); acs_rel_segment.delete(acs_rel_segment.get(E,'composition_rel')); acs_rel_segment.delete(acs_rel_segment.get(reg_users,'yippe_member_rel')); -- Remove the test groups. acs_group.delete(G); acs_group.delete(F); acs_group.delete(E); acs_group.delete(D); acs_group.delete(C); acs_group.delete(B); acs_group.delete(A); -- Remove the test members. acs_user.delete(joe); acs_user.delete(jane); acs_user.delete(bob); acs_user.delete(betty); acs_user.delete(jack); acs_user.delete(jill); acs_user.delete(sven); acs_user.delete(stacy); end; / show errors drop procedure rel_constraint_test_check; @rel-segments-test-types-drop.sql select log_level, log_key, message from acs_logs where log_key = 'error';